To Find Navigation of a process which has a Field in Temp Table in A.E.

To Find Navigation of a process which has a Field in Temp Table in A.E.

 SELECT fld.FIELDNAME,fld.DESCRLONG, CASE fld.FIELDTYPE

                WHEN 0 THEN 'Character'

                WHEN 1 THEN 'Long Character'

                WHEN 2 THEN 'Number'

                WHEN 3 THEN 'Signed Number'

                WHEN 4 THEN 'Date'

                WHEN 5 THEN 'Time'

                WHEN 6 THEN 'DateTime'

                WHEN 8 THEN 'Image'

                WHEN 9 THEN 'Image Reference'

                ELSE TO_CHAR(fld.FIELDTYPE)

        END  fld_FIELDTYPE, recFld.RECNAME, recFld.FIELDNUM, recFld.EDITTABLE, recFld.SUBRECORD, recDefn.RECTYPE, recDefn.RECDESCR, recDefn.DESCRLONG,CASE recDefn.RECTYPE

        WHEN 0 THEN 'Table'

        WHEN 1 THEN 'View'

        WHEN 2 THEN 'Derived'

        WHEN 3 THEN 'Sub Record'

        WHEN 5 THEN 'Dynamic View'

        WHEN 6 THEN 'Query View'

        WHEN 7 THEN 'Temporary Table'

        ELSE TO_CHAR(recDefn.RECTYPE)

END  rec_RECTYPE,

  

  

   -- stRec.AE_APPLID, stRec.AE_STATE_RECNAME, stRec.AE_DEFAULT_STATE,  

   temp.AE_APPLID, temp.RECNAME, '',

   aeDef.AE_APPLID, aeDef.DESCR, aeDef.AE_APPLLIBRARY, aeDef.AE_DISABLE_RESTART, aeDef.AEPROGTYPE, aeDef.DESCRLONG, 

   aeRqst.OPRID, aeRqst.RUN_CNTL_ID, aeRqst.AE_PROCESS_STATUS, aeRqst.PROCESS_INSTANCE, 

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

   prcsDefn.PRCSTYPE, prcsDefn.PNLGRPNAME, prcsNav.PORTAL_NAME, prcsNav.DESCR254, prcsNav.PORTAL_URI_SEG1, prcsNav.PORTAL_URI_SEG2, prcsNav.PORTAL_URI_SEG3, 

   prcsNav.PORTAL_URI_SEG4, prcsNav.FLUIDMODE, prcsNav.SMALLFFOPT, prcsNav.PORTAL_URLTEXT, prcsRun.PRCSINSTANCE, prcsRun.OPRID, 

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

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

  FROM (((((((((PSDBFIELD fld 

  LEFT OUTER JOIN  PSRECFIELD B ON  fld.FIELDNAME = recFld.FIELDNAME )

  LEFT OUTER JOIN  PSRECDEFN C ON  recFld.RECNAME = recDefn.RECNAME )                                               

  LEFT OUTER JOIN  PSAEAPPLTEMPTBL temp ON  temp.RECNAME = recDefn.RECNAME )                                        

  LEFT OUTER JOIN  PSAEAPPLDEFN aeDef ON  temp.AE_APPLID = aeDef.AE_APPLID )                                        

  LEFT OUTER JOIN  PS_AEREQUESTTBL aeRqst ON  aeDef.AE_APPLID = aeRqst.AE_APPLID )                                  

  LEFT OUTER JOIN  PS_PRCSDEFNPNL prcsDefn ON  prcsDefn.PRCSNAME = aeDef.AE_APPLID )                                

  LEFT OUTER JOIN  PSPRSMDEFN prcsNav ON  prcsNav.PORTAL_URI_SEG2 = prcsDefn.PNLGRPNAME )                           

  LEFT OUTER JOIN  PSPRCSRQST prcsRun ON  prcsDefn.PRCSTYPE = prcsRun.PRCSTYPE AND prcsDefn.PRCSNAME = prcsRun.PRCSNAME AND prcsRun.OPRID = 'U' )     PSPRCSRQST prcsRun

  

  )

  WHERE ( fld.FIELDNAME in ('SEX','ETHNIC_GRP_CD','MILITARY_STATUS','DISABILITY_STS_USA'))

To Find Navigation of a process which has a Field in State Record in A.E.

SELECT fld.FIELDNAME,fld.DESCRLONG, CASE fld.FIELDTYPE

                WHEN 0 THEN 'Character'

                WHEN 1 THEN 'Long Character'

                WHEN 2 THEN 'Number'

                WHEN 3 THEN 'Signed Number'

                WHEN 4 THEN 'Date'

                WHEN 5 THEN 'Time'

                WHEN 6 THEN 'DateTime'

                WHEN 8 THEN 'Image'

                WHEN 9 THEN 'Image Reference'

                ELSE TO_CHAR(fld.FIELDTYPE)

        END  fld_FIELDTYPE, recFld.RECNAME, recFld.FIELDNUM, recFld.EDITTABLE, recFld.SUBRECORD, recDefn.RECTYPE, recDefn.RECDESCR, recDefn.DESCRLONG,CASE recDefn.RECTYPE

        WHEN 0 THEN 'Table'

        WHEN 1 THEN 'View'

        WHEN 2 THEN 'Derived'

        WHEN 3 THEN 'Sub Record'

        WHEN 5 THEN 'Dynamic View'

        WHEN 6 THEN 'Query View'

        WHEN 7 THEN 'Temporary Table'

        ELSE TO_CHAR(recDefn.RECTYPE)

END  rec_RECTYPE,

  

  

   stRec.AE_APPLID, stRec.AE_STATE_RECNAME, stRec.AE_DEFAULT_STATE, 

   aeDef.AE_APPLID, aeDef.DESCR, aeDef.AE_APPLLIBRARY, aeDef.AE_DISABLE_RESTART, aeDef.AEPROGTYPE, aeDef.DESCRLONG, 

   aeRqst.OPRID, aeRqst.RUN_CNTL_ID, aeRqst.AE_PROCESS_STATUS, aeRqst.PROCESS_INSTANCE, 

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

   prcsDefn.PRCSTYPE, prcsDefn.PNLGRPNAME, prcsNav.PORTAL_NAME, prcsNav.DESCR254, prcsNav.PORTAL_URI_SEG1, prcsNav.PORTAL_URI_SEG2, prcsNav.PORTAL_URI_SEG3, 

   prcsNav.PORTAL_URI_SEG4, prcsNav.FLUIDMODE, prcsNav.SMALLFFOPT, prcsNav.PORTAL_URLTEXT, prcsRun.PRCSINSTANCE, prcsRun.OPRID, 

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

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

  FROM (((((((((PSDBFIELD fld 

  LEFT OUTER JOIN  PSRECFIELD B ON  fld.FIELDNAME = recFld.FIELDNAME )

  LEFT OUTER JOIN  PSRECDEFN C ON  recFld.RECNAME = recDefn.RECNAME )                                           

  LEFT OUTER JOIN  PSAEAPPLSTATE stRec ON  stRec.AE_STATE_RECNAME = recDefn.RECNAME )                           

  LEFT OUTER JOIN  PSAEAPPLDEFN aeDef ON  stRec.AE_APPLID = aeDef.AE_APPLID )                                   

  LEFT OUTER JOIN  PS_AEREQUESTTBL aeRqst ON  aeDef.AE_APPLID = aeRqst.AE_APPLID )                              

  LEFT OUTER JOIN  PS_PRCSDEFNPNL prcsDefn ON  prcsDefn.PRCSNAME = aeDef.AE_APPLID )                            

  LEFT OUTER JOIN  PSPRSMDEFN prcsNav ON  prcsNav.PORTAL_URI_SEG2 = prcsDefn.PNLGRPNAME )                       

  LEFT OUTER JOIN  PSPRCSRQST prcsRun ON  prcsDefn.PRCSTYPE = prcsRun.PRCSTYPE AND prcsDefn.PRCSNAME = prcsRun.PRCSNAME AND prcsRun.OPRID = 'U' )     

  

  )

  WHERE ( fld.FIELDNAME in ('SEX','ETHNIC_GRP_CD','MILITARY_STATUS','DISABILITY_STS_USA'))

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;