dyzx6jjcmp62y |
WITH ROLE_VERS (ROLE_TYPEID, ROLE_ID, ROLEVERSION_ID, ROOT_TYPEID) AS ( SELECT 'P'||ROLEVERSION_ID ROLE_TYPEID, ROLE_ID, ROLEVERSION_ID, 'P'||ROLEVERSION_ID ROOT_TYPEID FROM T_AV_ROLEVERSIONS WHERE ROLEVERSION_ID = :B1 UNION ALL SELECT 'C'||D.ENT_ID, D.ENT_ID, TO_NUMBER(NULL) ROLEVERSION_ID, R.ROOT_TYPEID FROM ROLE_VERS R JOIN T_AV_ROLEVERDEFINITIONS D ON D.ROLEVERSION_ID = R.ROLEVERSION_ID WHERE D.ENT_TYPE = 'global-role' ), ROLES (ROLE_TYPEID, ROLE_ID, ROLEVERSION_ID, ROOT_TYPEID) AS ( SELECT ROLE_TYPEID, ROLE_ID, ROLEVERSION_ID, ROOT_TYPEID FROM ROLE_VERS UNION ALL SELECT 'C'||D.ENT_ID, D.ENT_ID, TO_NUMBER(NULL) ROLEVERSION_ID, R.ROOT_TYPEID FROM ROLES R JOIN T_AV_ROLEDEFINITIONS D ON D.ROLE_ID = R.ROLE_ID WHERE R.ROLEVERSION_ID IS NULL AND D.DELETION_DATE IS NULL AND D.ENT_TYPE = 'global-role' ) SELECT ROLE_TYPEID_TYPE(ROLE_TYPEID , ROLE_ID, ROLEVERSION_ID, ROOT_TYPEID) FROM (SELECT DISTINCT ROLE_ID, ROLEVERSION_ID, ROOT_TYPEID, ROLE_TYPEID FROM ROLES)
|