How to identify Calendars objects assigned to a Process Flow's Components' Eligibility field

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

Below is an example of a site's requirement:

I have a need to identify Calendars (not schedules) used by a Process Flow that contains many Sub Process Flows. Is there a database query I can use to recursively list calendars used for Sub Process Flows and Components?

Environment

Release : 9.3

Component : APPLICATIONS MANAGER

Resolution

The following sql query can be used. Note that aside from the Calendar information, the sql also queries some addition information from components and Sub Process Flows. The sql can be edited by your DBA to include more or less information:

select distinct level, a.* from

(SELECT jtc.so_module chain, jt.so_module as job, cal.SO_HOLIDAY_NAME calendar, sv.aw_subvar SUBVAR, cd.so_task_name,  cd.so_predecessors,

    ca.so_det_arg as prompt_arg, oc.so_soc_order, oc.so_cond_timing, oc.so_condition_1, oc.so_qualifier, oc.so_condition_2, oc.so_action, oc.so_act_arg

FROM so_job_table jt 

INNER JOIN so_chain_detail cd

    ON cd.so_job_seq = jt.so_job_seq   

INNER JOIN so_job_table jtc

    ON jtc.so_job_seq = cd.so_chain_seq

INNER JOIN aw_subvar_refs sv

    ON jt.SO_MODULE = sv.AW_REF_OBJ

LEFT OUTER JOIN so_object_cond oc

    ON (oc.so_object_seq = cd.so_det_seq

    and cd.so_chain_seq = jtc.so_job_seq)

LEFT OUTER JOIN so_chain_args ca

    ON (ca.so_det_seq = cd.so_det_seq)

LEFT OUTER JOIN aw_module_sched sch

    ON sch.AW_JOB_SEQ = jt.SO_JOB_SEQ

LEFT OUTER JOIN so_holiday_grps cal   

    ON cal.SO_HOLIDAY_GRP =  cd.SO_HOLIDAY_GRP       

WHERE cd.so_act_flag = 'Y') a -- assuming we want active flows

start with a.chain = 'PARENT PROCESS FLOW NAME' -- The top level chain to recurse through

connect by CHAIN = prior JOB 

order siblings by job, so_task_name;