Header Ads

  • Recent Posts

    SQL Query to Retrieve all Records associated with a PeopleSoft Component

    Sometimes, I have had the necessity to find all the records that form part of a PeopleSoft component. I had to pass these records into other SQL queries to get certain information. For components, with multiple pages and records chances are that I would overlook upon one or two records. Hence, I wrote this SQL query to fetch all the records associated with a component.

    SELECT R.RECNAME AS RECORD_NAME,
           ( CASE
               WHEN R.RECTYPE = 0 THEN 'Table'
               WHEN R.RECTYPE = 1 THEN 'View'
               WHEN R.RECTYPE = 2 THEN 'Derived'
               WHEN R.RECTYPE = 3 THEN 'Sub Record'
               WHEN R.RECTYPE = 5 THEN 'Dynamic View'
               WHEN R.RECTYPE = 6 THEN 'Query View'
               WHEN R.RECTYPE = 7 THEN 'Temporary Table'
               ELSE 'Unknown'
             END )   AS RECORD_TYPE
    FROM   PSRECDEFN R
    WHERE  R.RECNAME IN (SELECT DISTINCT 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=:1 --Comp Name
                                               AND RECNAME <> ' ')
                         UNION
                         SELECT DISTINCT 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=:1--Comp Name))
       AND R.RECNAME <> ' '
    ORDER  BY R.RECTYPE ;



    Another SQL query is below which gives the component name, market, record name with its record type and description:
    SELECT DISTINCT p.pnlgrpname,  
                    cp.market,
                    cp.descr,
                    pgf.recname,
                    r.recdescr,
                    r.rectype
    FROM 
    pspnlgrpdefn cp,
           pspnlgroup cpg,
           pspnldefn pg,
           pspnlfield pgf,
           psrecdefn r

    WHERE cp.pnlgrpname = :1 -- Comp Name       AND
    cp.market = 'GBL'
           AND cp.pnlgrpname = cpg.pnlgrpname
           AND cp.market = cp.market
           AND pg.pnlname = cpg.pnlname
           AND pgf.pnlname = pg.pnlname
           AND pgf.recname = r.recname;


    3 comments:

    1. THANK YOU! This query saved me tremendous amount of manual effort.

      ReplyDelete
    2. Thanks a lot for helping with the Query...

      ReplyDelete
    3. Thanks much for this. But if there are nested subpages/secondary pages, this won't fetch all Records

      ReplyDelete

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad