SELECT PGDRS.DATA_ROLE_DISPLAY_NAME DISPLAY_ROLE_NAME,
PGDRS.DATA_ROLE_NAME ROLE_CODE,
PRODN.ABSTRACT_ROLE,
PRODN.JOB_ROLE,
PRODN.DATA_ROLE,
PRODN.DELEGATION_ALLOWED,
(SELECT ORG.NAME FROM PER_ORG_SECURITY_PROFILES ORG WHERE ORG.ORG_SECURITY_PROFILE_ID =
(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID =
PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'ORGANIZATION')) AS ORGANIZATION_SEC_PROFILE,
(SELECT POS.NAME FROM PER_POSITION_SECURITY_PROFILES POS WHERE POS.POSITION_SECURITY_PROFILE_ID =
(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE
DP.GENERATED_DATA_ROLE_ID = PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'POSITION')) AS POSITION_SEC_PROFILE,
(SELECT CNT.NAME FROM PER_COUNTRY_SECURITY_PROFILES CNT WHERE CNT.COUNTRY_SECURITY_PROFILE_ID =
(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID =
PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'COUNTRY')) AS COUNTRY_SEC_PROFILE,
(SELECT LDG.NAME FROM PER_LDG_SECURITY_PROFILES LDG WHERE LDG.LDG_SECURITY_PROFILE_ID =
(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID =
PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'LDG')) AS LDG_SEC_PROFILE,
(SELECT PER.NAME FROM PER_PERSON_SECURITY_PROFILES PER WHERE PER.PERSON_SECURITY_PROFILE_ID =
(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE
DP.GENERATED_DATA_ROLE_ID = PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'PERSON')) AS PERSON_SEC_PROFILE,
(SELECT PUB.NAME FROM PER_PERSON_SECURITY_PROFILES PUB WHERE PUB.PERSON_SECURITY_PROFILE_ID =
(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID =
PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'PUBLIC_PERSON')) AS PUBLIC_SEC_PROFILE,
(SELECT DOR.NAME FROM PER_DOC_TYPE_SECURITY_PROFILES DOR WHERE DOR.DOC_TYPE_SECURITY_PROFILE_ID =
(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID =
PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'DOR')) AS DOCUMENT_SEC_PROFILE,
(SELECT PAY.NAME FROM PAY_PAY_SECURITY_PROFILES PAY WHERE PAY.PAY_SECURITY_PROFILE_ID =
(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE DP.GENERATED_DATA_ROLE_ID = PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'PAYROLL')) AS PAYROLL_SEC_PROFILE,
(SELECT FLO.NAME FROM PAY_FLW_SECURITY_PROFILES FLO WHERE FLO.FLW_SECURITY_PROFILE_ID =
(SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE
DP.GENERATED_DATA_ROLE_ID = PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'FLOWPATTERN')) AS FLOWPATTERN_SEC_PROFILE,
(SELECT TRA.NAME FROM HRC_TXN_SEC_PROFILE TRA WHERE TRA.TXN_SECURITY_PROFILE_ID = (SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE
DP.GENERATED_DATA_ROLE_ID = PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT = 'TRANSACTION')) AS TRANSACTION_SEC_PROFILE,
(SELECT REQ.NAME FROM IRC_REQ_SEC_PROFILES REQ WHERE REQ.REQ_SECURITY_PROFILE_ID = (SELECT DP.SECURITY_PROFILE_ID FROM PER_GEN_DATA_ROLE_PROFILES DP WHERE
DP.GENERATED_DATA_ROLE_ID = PGDRS.GENERATED_DATA_ROLE_ID AND DP.HR_SECURING_OBJECT =
'REQUISITION')) AS REQUISITION_SEC_PROFILE
FROM PER_GENERATED_DATA_ROLES PGDRS,
PER_ROLES_DN PRODN
WHERE PGDRS.DATA_ROLE_ID = PRODN.ROLE_ID
ORDER BY PRODN.ABSTRACT_ROLE DESC, PRODN.JOB_ROLE DESC, PRODN.DATA_ROLE DESC
