Retrieving Role, Duty Role, and Privileges Navigation Path in Oracle Fusion BI Report with SQL Query

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)