Some Nice to have SQL Scripts in PeopleSoft - Part III

13)      SQL Query to find out the records exposed by a Component Interface

SELECT distinct RECNAME
FROM PSBCITEM
WHERE BCNAME = 'XXXX_CI';

 

14)       SQL Query to find search records in a Component Interface

SELECT SEARCHRECNAME, ADDSRCHRECNAME
FROM PSBCDEFN
WHERE BCNAME = 'XXXX_CI';

 

15)       SQL to find All Records under a specified component

SELECT DISTINCT RECNAME FROM PSRECDEFN
WHERE ( RECNAME IN (SELECT RECNAME FROM PSPNLFIELD WHERE PNLNAME IN (SELECT DISTINCT B.PNLNAME
FROM PSPNLGROUP A, PSPNLFIELD B WHERE (A.PNLNAME = B.PNLNAME OR A.PNLNAME = B.SUBPNLNAME)
AND A.PNLGRPNAME = 'JOB_DATA'))
OR RECNAME IN (SELECT RECNAME FROM PSPNLFIELD WHERE PNLNAME IN (SELECT DISTINCT B.SUBPNLNAME
FROM PSPNLGROUP A, PSPNLFIELD B WHERE (A.PNLNAME = B.PNLNAME OR A.PNLNAME = B.SUBPNLNAME)
AND A.PNLGRPNAME = 'JOB_DATA'))

 

16)       SQL to find the Navigation of a PeopleSoft Process

       SQL to find the navigation to the run control page by Process Name in PeopleSoft.

SELECT E.PRCSTYPE, E.PRCSNAME, F.DESCR, DECODE( H.PORTAL_LABEL,' ','','','','Root','Root ','Root > ' || H.PORTAL_LABEL) || '> ' || G.PORTAL_LABEL || ' > ' || C.PORTAL_LABEL || ' > ' || B.PORTAL_LABEL || ' > ' || A.PORTAL_LABEL || ' > ' || D.PORTAL_LABEL , D.DESCR254, E.PNLGRPNAME,D.PORTAL_NAME,D.PORTAL_REFTYPE,D.PORTAL_OBJNAME,F.PRCSTYPE,F.PRCSNAME
FROM PSPRSMDEFN D, PS_PRCSDEFNPNL E, PS_PRCSDEFN F, PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN G, PSPRSMDEFN H
WHERE E.PNLGRPNAME = D.PORTAL_URI_SEG2
AND E.PRCSTYPE = F.PRCSTYPE
AND E.PRCSNAME = F.PRCSNAME
AND D.PORTAL_NAME = 'EMPLOYEE'
AND D.PORTAL_REFTYPE = 'C'
AND E.PNLGRPNAME > ' '
AND A.PORTAL_NAME = 'EMPLOYEE'
AND A.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
AND B.PORTAL_NAME = 'EMPLOYEE'
AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME
AND C.PORTAL_NAME (+) = 'EMPLOYEE'
AND C.PORTAL_OBJNAME(+) = B.PORTAL_PRNTOBJNAME
AND G.PORTAL_NAME(+) = 'EMPLOYEE'
AND G.PORTAL_OBJNAME(+) = C.PORTAL_PRNTOBJNAME
AND H.PORTAL_NAME(+) = 'EMPLOYEE'
AND H.PORTAL_OBJNAME(+) = G.PORTAL_PRNTOBJNAME
AND E.PRCSNAME ='Process Name'
ORDER BY 1, 2, 4