To Find the Navigation of recently run processes.

To Find the Navigation of recently run processes - just the url. 

The PORTAL_URLTEXT can be used to paste in the browswer address bar and we can directly go to the component. We can get PORTAL_URI_SEG1 Menu, PORTAL_URI_SEG2 Component from the 

PSPRSMDEFN table.

SELECT prcsCmp.PNLGRPNAME,PORTAL_URLTEXT,prcsRun.prcstype,cmpNav.descr254,

prcsRun.* 

FROM psprcsrqst prcsRun,   

ps_PRCSDEFNPNL prcsCmp,    -- Process Components... can be more than one.

PSPRSMDEFN cmpNav             -- Component Path... navigation.

WHERE 

prcsRun.prcsname in 

('xxx')

AND 

(begindttm =(SELECT MAX(begindttm) FROM psprcsrqst a1 WHERE prcsRun.prcsname = a1.prcsname AND prcsRun.oprid = a1.oprid)

OR  begindttm =(SELECT MIN(begindttm) FROM psprcsrqst a1 WHERE prcsRun.prcsname = a1.prcsname AND prcsRun.oprid = a1.oprid)

)

AND prcsRun.prcsname = prcsCmp.prcsname 

and PORTAL_URI_SEG2 = prcsCmp.PNLGRPNAME;

With Navigation

This below query gives navigation as well as the url. The text in purple can be used to get navigation of any component. Since its not recursive, it can be used to fetch more than one component at a time.

SELECT distinct A.PRCSTYPE, A.PRCSNAME, B.PNLGRPNAME, c.oprid,

TO_CHAR(CAST((C.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 

TO_CHAR(CAST((C.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), C.RUNCNTLID, 

A.DESCR, dbms_lob.substr ( A.DESCRLONG,2000,1 ), A.RESTARTENABLED, D.DESCR254, D.PORTAL_URI_SEG1 Menu, 

D.PORTAL_URI_SEG2 Component, 

dbms_lob.substr ( d.PORTAL_URLTEXT,2000,1 )   -- without this we cant apply distinct as its a clob

,  C.PRCSINSTANCE

,h.PORTAL_LABEL || ' > ' || g.PORTAL_LABEL || ' > ' || f.PORTAL_LABEL || ' > '  

|| e.PORTAL_LABEL || ' > '  ||  d.PORTAL_LABEL PATH_TO_COMPONENT

  FROM (((

  ((((

  PS_PRCSDEFN A  -- gets the process

  LEFT OUTER JOIN  PS_PRCSDEFNPNL B     ON  A.PRCSTYPE = B.PRCSTYPE AND A.PRCSNAME = B.PRCSNAME )  -- gets the component

  LEFT OUTER JOIN  PSPRSMDEFN D     ON  D.PORTAL_URI_SEG2    = B.PNLGRPNAME -- component name

    

    LEFT OUTER JOIN  PSPRSMDEFN E ON  D.PORTAL_NAME = E.PORTAL_NAME AND D.PORTAL_PRNTOBJNAME  = E.PORTAL_OBJNAME ) 

    LEFT OUTER JOIN  PSPRSMDEFN F ON  E.PORTAL_NAME = F.PORTAL_NAME AND  E.PORTAL_PRNTOBJNAME = F.PORTAL_OBJNAME ) 

    LEFT OUTER JOIN  PSPRSMDEFN G ON  F.PORTAL_NAME = G.PORTAL_NAME AND  F.PORTAL_PRNTOBJNAME = G.PORTAL_OBJNAME ) 

    LEFT OUTER JOIN  PSPRSMDEFN H ON  G.PORTAL_NAME = H.PORTAL_NAME AND  G.PORTAL_PRNTOBJNAME = H.PORTAL_OBJNAME )  -- gets the navigation

        ) 

  LEFT OUTER JOIN  PSPRCSRQST C     ON  A.PRCSTYPE = C.PRCSTYPE AND A.PRCSNAME = C.PRCSNAME 

       and  C.PRCSINSTANCE =     (select MAX( C1.PRCSINSTANCE) from PSPRCSRQST C1 where C1.prcsname = c.prcsname) -- gets one per type

    )

where 

a.prcsname in ( :prcsname

)      

    

    order by 3,1,2

    ;

Get all processes run by a user id and the approx. navigation for the latest run.

SELECT distinct prcsRun.prcsname,prcsCmp.PNLGRPNAME,prcsRun.prcstype  ,to_char(cmpNav.PORTAL_URLTEXT) -- navigation url

--,cmpNav.descr254

 ,prcsRun.* 

FROM psprcsrqst prcsRun,   

ps_PRCSDEFNPNL prcsCmp    -- Process Components... can be more than one.

 ,PSPRSMDEFN cmpNav             -- Component Path... navigation.

WHERE 

prcsRun.oprid = '%user%' and

-- prcsRun.prcsname in ('xxx') -- if required by process

-- AND 

 (begindttm =(SELECT MAX(begindttm) FROM psprcsrqst a1 WHERE prcsRun.prcsname = a1.prcsname AND prcsRun.oprid = a1.oprid)  -- for the latest run

-- OR  begindttm =(SELECT MIN(begindttm) FROM psprcsrqst a1 WHERE prcsRun.prcsname = a1.prcsname AND prcsRun.oprid = a1.oprid) -- for the earliest run

 )

  AND

prcsRun.prcsname = prcsCmp.prcsname 

 and cmpNav.PORTAL_URI_SEG2 = prcsCmp.PNLGRPNAME

order by prcsRun.prcsname;