All Scroll Levels & Records that are in a PeopleSoft Component.

This SQL returns all Scroll Levels and Records for a given PeopleSoft component. Handy.

DEFINE componentName = 'your_component';

SELECT DISTINCT B.PNLNAME, B.RECNAME, B.OCCURSLEVEL, B.FIELDNUM
FROM
  PSPNLGROUP A
, PSPNLFIELD B
, PSRECDEFN C
WHERE A.PNLGRPNAME = &componentName
AND B.PNLNAME = A.PNLNAME
AND B.RECNAME <> ' '
AND B.ASSOCFIELDNUM = 0
AND B.FIELDNUM =
(SELECT MIN(D.FIELDNUM)
FROM PSPNLFIELD D
WHERE D.PNLNAME = B.PNLNAME
AND D.RECNAME = B.RECNAME
AND D.OCCURSLEVEL = B.OCCURSLEVEL)
AND C.RECNAME = B.RECNAME
AND C.RECTYPE = 0
UNION
SELECT DISTINCT B.PNLNAME, B.RECNAME, B.OCCURSLEVEL + D.OCCURSLEVEL, B.FIELDNUM
FROM
  PSPNLFIELD B
, PSRECDEFN C
, (SELECT E.SUBPNLNAME, E.OCCURSLEVEL
FROM PSPNLFIELD E
WHERE E.SUBPNLNAME <> ' '
AND EXISTS
(SELECT 'X'
FROM PSPNLGROUP F
WHERE F.PNLGRPNAME = &componentName
AND F.PNLNAME = E.PNLNAME)) D
WHERE B.RECNAME <> ' '
AND B.ASSOCFIELDNUM = 0
AND B.FIELDNUM =
(SELECT MIN(D.FIELDNUM)
FROM PSPNLFIELD D
WHERE D.PNLNAME = B.PNLNAME
AND D.RECNAME = B.RECNAME
AND D.OCCURSLEVEL = B.OCCURSLEVEL)
AND C.RECNAME = B.RECNAME
AND C.RECTYPE = 0
AND D.SUBPNLNAME = B.PNLNAME ORDER BY 1,3,4

0 comments: