SQL Query for job and job plan list of workflow

[Murali krishna]

Hi Team,

In oracle DB, Have requirement to get multiple jobs and multiple job plan list along with flag Generate task at "Activation time or Run time".

For example JOBPLAN_A contain JOB_A1 AND JOBPLAN_B, JOBPLAN_C .

JOBPLAN_B contains  JOB_B1 and JOB_B2. 

JOBPLAN_C contains JOB_C1 and JOB_C2.

Need single sql query to list like below for given JOBPLAN_A.

JOB_A1 ,  "Activation time or Run time"
JOBPLAN_B,"Activation time or Run time"
JOB_B1, "Activation time or Run time"
JOB_B2, "Activation time or Run time"
JOBPLAN_C , "Activation time or Run time"
JOB_C1 , "Activation time or Run time"
JOB_C2, ""Activation time or Run time"

we can get by using JPP and OH table only for workflow but not if workflow contains nested job plans.

Please share me query if anyone have sql query.

-------------------------------------------

[Christine Bauder]

Try this one.....you'll need to add your main workflow name and your client...it will probably return more than you are looking for. It basically takes the main workflow and returns all nested workflows and all of the jobs inside and in the order they run. The Generate At Runtime column is the last column and defined as a 1 or a 0. A value of 1 is Generate at Runtime and a value of 0 is Generate at Activation time. 

WITH Parent (Top,Parent,oh_idnr,jpp_oh_idnr,jpp_lnr,Task,Alias,Otype,Path,Active,ActivePath,Ord) AS (
    select
        s.oh_name,
        s.oh_name AS parent,
        s.oh_idnr,
        0 AS jpp_oh_idnr,
        0 AS jpp_lnr,
        null,
        null,
        s.oh_otype,
        s.oh_name AS Path,
        '1',
        '1',
        '000'
    from oh s
    where s.oh_client = 1000
    and s.oh_deleteflag = 0
    and s.oh_otype = 'JOBP'
    and s.oh_name = ('enter your workflow name here')
    UNION ALL
   SELECT
        Parent.Top,
        th.oh_name,
        th.oh_idnr,
        th.jpp_oh_idnr,
        th.jpp_lnr,
        TH.jpp_object,
        nvl(th.jpp_alias, TH.jpp_object),
        th.jpp_otype,
        Parent.Path || '/' || TH.jpp_object AS Path,
        DECODE(BITAND(th.jpp_active, 1), 1, '1', '0'),
        Parent.Active||DECODE(BITAND(th.jpp_active, 1), 1, '1', '0'),
        Parent.Ord || LPAD(TH.jpp_lnr, 3, '0')
    FROM
      (select o.oh_idnr, w.oh_name, p.jpp_object, p.jpp_otype, p.jpp_oh_idnr, p.jpp_lnr, p.jpp_active, p.jpp_alias
      from jpp p, oh o, oh w
      WHERE o.oh_name = p.jpp_object
      and p.jpp_oh_idnr = w.oh_idnr
      and o.oh_client = 1000
      AND o.oh_deleteflag = 0
      and w.oh_client = enter your client number here
      AND w.oh_deleteflag = 0) TH
    INNER JOIN
      Parent
    ON
      Parent.oh_idnr = TH.jpp_oh_idnr
)
SELECT p.*, JBA_ActAtRun AS GeneratateAtRuntime
FROM Parent p
left outer JOIN JBA 
ON P.OH_IDNR = JBA.JBA_OH_IDNR 
order by Top, Ord

------------------------------
Christine Bauder
Assistant Director of Production Services
University of Colorado
Colorado
------------------------------