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'))