Get roles required for nav collections exposed as tiles

get_roles_for_va.sql

WITH MY_TILE_NAV_COLL AS 
(
   /* This query provides the list of all tiles (CREF objects, CREF paths) and their
      corresponding nav collection CREF object. */
   SELECT A.PTPPB_GROUPLET_ID AS TILE_NAME
   ,A.DESCR AS TILE_DESCRIPTION
   ,A.PTPPB_ORIG_DISP_ID AS TILE_CREF_OBJECT
   ,'Root > Fluid Structure and Content > Fluid Pages > ' || D.PORTAL_LABEL AS TILE_PATH
   ,C.PTPPB_VALUE AS NAV_COLL_CREF_OBJECT
   FROM PS_PTPPB_GROUPLET A
   ,PSPRSMDEFN B
   ,PS_PTGP_DS_SETTGS C
   ,PSPRSMDEFN D
   WHERE A.PTPPB_GROUPLET_ID LIKE 'FSU%'--'%[NAMING_CONVENTION]%'
   AND A.PTPPB_DATATYPE_ID = 'NAVCOLL'
   AND B.PORTAL_NAME = 'EMPLOYEE'
   AND B.PORTAL_REFTYPE = 'C'
   AND B.PORTAL_OBJNAME = A.PTPPB_ORIG_DISP_ID
   AND A.PTPPB_GROUPLET_ID = C.PTPPB_GROUPLET_ID
   AND C.PTPPB_FIELDNAME = 'scname'
   AND D.PORTAL_NAME = B.PORTAL_NAME
   AND D.PORTAL_REFTYPE = 'F'
   AND D.PORTAL_OBJNAME = A.PTGPLT_TP_PARENT
)
SELECT DISTINCT X.*
,F.ROLENAME
FROM MY_TILE_NAV_COLL X
,PSPRSMDEFN Y
,PSPRSMPERM E
,PSROLECLASS F
WHERE ((Y.PORTAL_PRNTOBJNAME = X.NAV_COLL_CREF_OBJECT
     AND Y.PORTAL_REFTYPE = 'C'
        )
    OR (Y.PORTAL_PRNTOBJNAME IN (SELECT Z.PORTAL_OBJNAME
                                 FROM PSPRSMDEFN Z
                                 WHERE Z.PORTAL_PRNTOBJNAME = X.NAV_COLL_CREF_OBJECT
                                 AND Z.PORTAL_CREF_USGT <> 'LINK')
        )
    OR (Y.PORTAL_PRNTOBJNAME IN (SELECT Z.PORTAL_LINKOBJNAME
                                 FROM PSPRSMDEFN Z
                                 WHERE Z.PORTAL_PRNTOBJNAME = X.NAV_COLL_CREF_OBJECT
                                 AND Z.PORTAL_REFTYPE = 'F'
                                 AND Z.PORTAL_CREF_USGT = 'LINK')
        )
    )
AND Y.PORTAL_NAME = E.PORTAL_NAME
AND Y.PORTAL_REFTYPE = E.PORTAL_REFTYPE
AND Y.PORTAL_OBJNAME = E.PORTAL_OBJNAME
AND E.PORTAL_PERMNAME LIKE 'FSU%'--'[PLIST_NAMING_CONVENTION]%'
AND E.PORTAL_PERMNAME = F.CLASSID
ORDER BY X.TILE_CREF_OBJECT