Wednesday, November 14, 2012

Menu navigation from Process name

  Select distinct RTRIM(LTRIM(c.PORTAL_LABEL)) LBL1, '>' L1,RTRIM(LTRIM(b.PORTAL_LABEL)) LBL2, '>' L2,RTRIM(LTRIM(a.PORTAL_LABEL)) LBL3,e.DESCR,d.prcsname  from
    (SELECT MA.PORTAL_PRNTOBJNAME,PORTAL_URI_SEG2,   MA.PORTAL_LABEL FROM SYSADM.PSPRSMDEFN MA  WHERE MA.PORTAL_REFTYPE = 'C' ) a,
    (SELECT MA.PORTAL_OBJNAME,MA.PORTAL_PRNTOBJNAME, MA.PORTAL_LABEL FROM SYSADM.PSPRSMDEFN MA  WHERE MA.PORTAL_REFTYPE = 'F') b,
    (SELECT MA.PORTAL_OBJNAME,MA.PORTAL_PRNTOBJNAME, MA.PORTAL_LABEL FROM SYSADM.PSPRSMDEFN MA  WHERE MA.PORTAL_REFTYPE = 'F') c,
      ps_prcsdefnpnl d,
      ps_prcsdefn e
           Where a.PORTAL_PRNTOBJNAME=b.PORTAL_OBJNAME
           and b.PORTAL_PRNTOBJNAME=c.PORTAL_OBJNAME
           and a.PORTAL_URI_SEG2=d.pnlgrpname
           and d.prcsname = 'ABFI111'
           and d.prcsname=e.prcsname; ---Give Component Name to find path

Thursday, September 27, 2012

Component Path Finder SQL-Simplified

Select RTRIM(LTRIM(c.PORTAL_LABEL)) LBL1, '>' L1,RTRIM(LTRIM(b.PORTAL_LABEL)) LBL2, '>' L2,RTRIM(LTRIM(a.PORTAL_LABEL)) LBL3  from
    (SELECT MA.PORTAL_PRNTOBJNAME,PORTAL_URI_SEG2,   MA.PORTAL_LABEL FROM SYSADM.PSPRSMDEFN MA  WHERE MA.PORTAL_REFTYPE = 'C' ) a,
    (SELECT MA.PORTAL_OBJNAME,MA.PORTAL_PRNTOBJNAME, MA.PORTAL_LABEL FROM SYSADM.PSPRSMDEFN MA  WHERE MA.PORTAL_REFTYPE = 'F') b,
    (SELECT MA.PORTAL_OBJNAME,MA.PORTAL_PRNTOBJNAME, MA.PORTAL_LABEL FROM SYSADM.PSPRSMDEFN MA  WHERE MA.PORTAL_REFTYPE = 'F') c
           Where a.PORTAL_PRNTOBJNAME=b.PORTAL_OBJNAME
           and b.PORTAL_PRNTOBJNAME=c.PORTAL_OBJNAME
           and a.PORTAL_URI_SEG2='ITEM_TYP_FISCAL' ---Give Component Name to find path
           ;

Component Path Finder SQL-Simplified

Select RTRIM(LTRIM(c.PORTAL_LABEL)) LBL1, '>' L1,RTRIM(LTRIM(b.PORTAL_LABEL)) LBL2, '>' L2,RTRIM(LTRIM(a.PORTAL_LABEL)) LBL3  from
    (SELECT MA.PORTAL_PRNTOBJNAME,PORTAL_URI_SEG2,   MA.PORTAL_LABEL FROM SYSADM.PSPRSMDEFN MA  WHERE MA.PORTAL_REFTYPE = 'C' ) a,
    (SELECT MA.PORTAL_OBJNAME,MA.PORTAL_PRNTOBJNAME, MA.PORTAL_LABEL FROM SYSADM.PSPRSMDEFN MA  WHERE MA.PORTAL_REFTYPE = 'F') b,
    (SELECT MA.PORTAL_OBJNAME,MA.PORTAL_PRNTOBJNAME, MA.PORTAL_LABEL FROM SYSADM.PSPRSMDEFN MA  WHERE MA.PORTAL_REFTYPE = 'F') c
           Where a.PORTAL_PRNTOBJNAME=b.PORTAL_OBJNAME
           and b.PORTAL_PRNTOBJNAME=c.PORTAL_OBJNAME
           and a.PORTAL_URI_SEG2='ITEM_TYP_FISCAL' ---Give Component Name to find path
           ;