Blogs

SQL query to identify Drop Zone configurations in PeopleSoft

By Chandra Rawat posted 11-09-2023 03:27 PM

  

Sharing a Drop Zone configurations SQL query here...

I encountered a problem during our HR PUM 47 upgrade that was related to drop zones. The following query proved to be quite valuable in identifying all the dropzones in the system. It also comes in handy if you need to remove dropzone data from various Peopletool tables that may be there due to improper migrations.

------------------------------------------

SELECT  distinct b.portal_uri_seg2 AS COMPONENT, 
                c.descr           AS COMPONENT_DESCR, 
                CASE 
                  WHEN c.fluidmode = 0 THEN 'Classic' 
                  WHEN c.fluidmode = 1 THEN 'Fluid' 
                  ELSE 'N/A' 
                END               AS TYPE, 
                a.pnlname         AS PAGE, 
                d.itemlabel       AS PAGE_DESCR, 
                SUBSTR(e.ptcs_pnlfldname, ( INSTR(e.ptcs_pnlfldname, '.', 1, 1) 
                                            + 1 ), 
                ( INSTR(e.ptcs_pnlfldname, '.', 1, 3) - 
                  INSTR(e.ptcs_pnlfldname, '.', 1, 1) ) 
                - 1)              AS DROP_ZONE, 
                a.ptcs_serviceid  AS CONFIGURED_SUBPAGE 
FROM   psptcssrvconf a, 
       psprsmdefn b, 
       pspnlgrpdefn c, 
       pspnlgroup d, 
       psptcs_mapflds e       
WHERE  a.ptcs_embeddable = 'Y' 
       AND a.ptcs_suowserv = 'Y' 
       AND a.version <> 0 
       AND a.portal_objname = b.portal_objname 
       AND b.portal_uri_seg2 = c.pnlgrpname 
       AND b.portal_uri_seg3 = c.market 
       AND c.pnlgrpname = d.pnlgrpname 
       AND c.market = d.market 
       AND a.pnlname = d.pnlname 
       AND a.portal_name = e.portal_name 
       AND a.portal_objname = e.portal_objname 
       AND a.ptcs_serviceid = e.ptcs_serviceid 
       AND a.ptcs_instanceid = e.ptcs_instanceid 
       AND e.ptcs_parametername = 'PTCS_MENUFIELD';

1 comment
31 views

Permalink

Comments

11-13-2023 08:35 AM

Thank you! Should come in handy.