SELECT *
FROM
(SELECT S1.*
,
PG.PNLGRPNAME
FROM
(SELECT ROWNUM AS JBSEQ,
'LEVEL '
||LEVEL,
LPAD(' ',5*LEVEL,'*')
||PARENTJOBNAME AS PARENT1 ,
PARENTJOBNAME ,
S.PRCSTYPE ,
S.PRCSNAME,
S.RUN_CNTL_ID ,
S.PRCSJOBSEQ,
D.DESCR
FROM PS_SCHDLITEM S ,
(SELECT B.PRCSJOBNAME AS PRCSNAME, B.PRCSTYPE, B.DESCR FROM PS_PRCSJOBDEFN B
UNION
SELECT A.PRCSNAME,A.PRCSTYPE, A.DESCR FROM PS_PRCSDEFN A
) D
WHERE SCHEDULENAME ='IND_PAM_JS_AM_D'
AND D.PRCSTYPE =S.PRCSTYPE
AND D.PRCSNAME = S.PRCSNAME
START WITH PARENT_ITEM_NODE= ' '
CONNECT BY PRIOR ITEM_NODE = PARENT_ITEM_NODE
ORDER SIBLINGS BY PARENT_ITEM_NODE,
PRCSJOBSEQ,
PRCSNAME,
RUN_CNTL_ID
) S1 ,
(SELECT B.PRCSJOBNAME AS PRCSNAME,
'PSJOB' AS PRCSTYPE ,
B.PNLGRPNAME
FROM PS_PRCSJOBPNL B
UNION
SELECT A.PRCSNAME,A.PRCSTYPE, A.PNLGRPNAME FROM PS_PRCSDEFNPNL A
) PG
WHERE S1.PRCSTYPE = PG.PRCSTYPE(+)
AND S1.PRCSNAME =PG.PRCSNAME(+)
—AND N.PNLGRPNAME =PG.PNLGRPNAME(+)
ORDER BY JBSEQ
) P2