SQL query to identify Drop Zone configurations in PeopleSoft
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';
No comments:
Post a Comment