Find Drop Zones References in a Project

Find-Project-DZ-References.sql

SELECT DISTINCT a.pnlname         AS PAGE, 
                d.itemlabel       AS PAGE_DESCR, 
                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, 
                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,
       psprojectitem f
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'
       AND f.projectname in ('SV_COMPANY_PROJECT') /* <<<<<< Insert Project List Here */ 
       AND f.objecttype = 5
       AND a.pnlname = f.objectvalue1;