Query to Fetch the Role, Duty Role, Privileges Navigation Path for Oracle Fusion BI Report

select DISTINCT “C”.”Job Role id”,
“C”.”Job Role Name”,
“C”.”Duty Role id”,
(select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “C”.”Duty Role id”) as “Duty Role Name”,
“ASE_PRIV_ROLE_MBR”.”PRIVILEGE_ID” as “PRIVILEGE_ID”,
(select “ASE_PRIVILEGE_VL”.”NAME” from “FUSION”.”ASE_PRIVILEGE_VL” “ASE_PRIVILEGE_VL” where “ASE_PRIVILEGE_VL”.”PRIVILEGE_ID” = “ASE_PRIV_ROLE_MBR”.”PRIVILEGE_ID”) as “Privilege_name”,
“C”.”Navigation path”
from (select “B”.”Job_Role_id1″ as “Job Role id”,
(select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “B”.”Job_Role_id1″) as “Job Role Name”,
“B”.”Duty_Role_id1″ as “Duty Role id”,
“B”.”Navigation_path1″ as “Navigation path”
from (select “A”.”Job_Role_id” as “Job_Role_id1”,
“A”.”Duty_Role_id” as “Duty_Role_id1”,
(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” =
(select “ASE_ROLE_VL”.”ROLE_TYPE_CODE” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “A”.”Job_Role_id”)) as “JOB_ROLE_TYPE_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” =
(select “ASE_ROLE_VL”.”ROLE_TYPE_CODE” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “A”.”Duty_Role_id”)) as “DUTY_ROLE_TYPE_NAME”,”A”.”Navigation_path” as “Navigation_path1”
from ((select distinct connect_by_root “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID” as “Job_Role_id”,
“ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID” as “Duty_Role_id”,
SYS_CONNECT_BY_PATH((select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID”), ‘ –> ‘) as “Navigation_path”
FROM “FUSION”.”ASE_ROLE_ROLE_MBR” “ASE_ROLE_ROLE_MBR”
WHERE (“ASE_ROLE_ROLE_MBR”.”EFFECTIVE_END_DATE” >= sysdate OR “ASE_ROLE_ROLE_MBR”.”EFFECTIVE_END_DATE” IS NULL)
CONNECT BY PRIOR “ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID”)
UNION
(select “ASE_ROLE_VL”.”ROLE_ID” as “Job_Role_id”,
“ASE_ROLE_VL”.”ROLE_ID” as “Duty_Role_id”,
‘ –> ‘||”ASE_ROLE_VL”.”ROLE_NAME” as “Navigation_path”
FROM “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL”
WHERE (“ASE_ROLE_VL”.”EFFECTIVE_END_DATE” >= sysdate OR “ASE_ROLE_VL”.”EFFECTIVE_END_DATE” IS NULL))) “A”) “B”
where (“B”.”JOB_ROLE_TYPE_NAME” LIKE ‘%Duty Roles%’ OR “B”.”DUTY_ROLE_TYPE_NAME” LIKE ‘%Duty Roles%’)) “C”,
“FUSION”.”ASE_PRIV_ROLE_MBR” “ASE_PRIV_ROLE_MBR”
where “ASE_PRIV_ROLE_MBR”.”ROLE_ID” = “C”.”Duty Role id”
AND UPPER(“C”.”Job Role Name”) LIKE UPPER(:P_NAME)

Leave a Reply

Your email address will not be published.