SQL Query for Extracting Role, Duty Role, and Effective Date Navigation Path in Oracle Fusion BI Report

select “ASE_USER_ROLE_MBR”.”USER_ID” as “USER_ID”,

(select “ASE_USER_VL”.”USER_DISPLAY_NAME” from “FUSION”.”ASE_USER_VL” “ASE_USER_VL” where “ASE_USER_VL”.”USER_ID” = “ASE_USER_ROLE_MBR”.”USER_ID”) as “USER_DISPLAY_NAME”,

“ASE_USER_ROLE_MBR”.”ROLE_ID” as “JOB_ROLE_ID”,

(select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “ASE_USER_ROLE_MBR”.”ROLE_ID”) as “JOB_ROLE_NAME”,

“ASE_USER_ROLE_MBR”.”EFFECTIVE_START_DATE” as “EFFECTIVE_START_DATE”,

“ASE_USER_ROLE_MBR”.”EFFECTIVE_END_DATE” as “EFFECTIVE_END_DATE”

from “FUSION”.”ASE_USER_ROLE_MBR” “ASE_USER_ROLE_MBR”

where (“ASE_USER_ROLE_MBR”.”EFFECTIVE_END_DATE” >= sysdate OR “ASE_USER_ROLE_MBR”.”EFFECTIVE_END_DATE” IS NULL)

AND EXISTS (SELECT ‘1’ FROM “FUSION”.”ASE_USER_VL” “ASE_USER_VL”

WHERE (“ASE_USER_VL”.”EFFECTIVE_END_DATE” >= sysdate OR “ASE_USER_VL”.”EFFECTIVE_END_DATE” IS NULL)

AND “ASE_USER_VL”.”USER_ID” = “ASE_USER_ROLE_MBR”.”USER_ID”)