select “A”.”Job_Role_id”,
(select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “A”.”Job_Role_id”) as “Job_Role_Name”,
“A”.”Duty_Role_id”,
“A”.”Duty_Role_Name”,
“A”.”ROLE_TYPE_NAME”,
“A”.”Navigation_path”
from (select distinct connect_by_root “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID” as “Job_Role_id”,
NVL(“ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID”,”ASE_ROLE_VL”.”ROLE_ID”) as “Duty_Role_id”,
NVL((select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID”),
“ASE_ROLE_VL”.”ROLE_NAME”) as “Duty_Role_Name”,
(select “ASE_ROLE_TYPE_VL”.”ROLE_TYPE_NAME” from “FUSION”.”ASE_ROLE_TYPE_VL” “ASE_ROLE_TYPE_VL” where “ASE_ROLE_TYPE_VL”.”ROLE_TYPE_CODE” = “ASE_ROLE_VL”.”ROLE_TYPE_CODE”) as “ROLE_TYPE_NAME”,
SYS_CONNECT_BY_PATH(“ASE_ROLE_VL”.”ROLE_NAME”, ‘ –> ‘) as “Navigation_path”,
“ASE_ROLE_VL”.”EFFECTIVE_END_DATE” as “EED1”,
“ASE_ROLE_ROLE_MBR”.”EFFECTIVE_END_DATE” as “EED2”
FROM “FUSION”.”ASE_ROLE_ROLE_MBR” “ASE_ROLE_ROLE_MBR”,
“FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL”
WHERE “ASE_ROLE_VL”.”ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID”(+)
CONNECT BY PRIOR “ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID” OR PRIOR “ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID” = “ASE_ROLE_VL”.”ROLE_ID”) “A”
where “A”.”ROLE_TYPE_NAME” LIKE ‘%Duty Roles%’
AND (“A”.”EED1″ >= sysdate OR “A”.”EED1″ IS NULL)
AND (“A”.”EED1″ >= sysdate OR “A”.”EED1″ IS NULL)
Leave a Reply