Oracle Fusion Data extration SQL query issue

[sksuresh2k30]

HI Team,

We are working with the customer to export the Oracle Fusion SOD data extraction. It seems like the customer is unable to create a Data Model with the provided query. What object is ARRM referring to in the below query? 

 https://docs.saviyntcloud.com/bundle/AAG-Guide/page/Content/AAG_for_Oracle_Enterprise_Resource_Plann...

WITH HIERARCHY_TABLE AS

(

SELECT Level as "HIERARCHY_LEVEL",

Connect_by_root ARV1.code "TOP_ROLE_CODE",

ARV1.ROLE_NAME "Child1_Role_Name",

ARV1.CODE "Child1 Role Code",

ARV1.ROLE_ID "CHILD1_ROLE_ID",

ARV1.IS_SEEDED "Seeded Child1 Role",

ARV1.Role_type_code "Child1 Role Type",

ARV1.Effective_end_date "Child1 End Date",

ARV2.ROLE_NAME "Child2_Role_Name",

ARV2.CODE "Child2 Role Code",

ARV2.ROLE_ID "CHILD2_ROLE_ID",

ARV2.IS_SEEDED "Seeded Child2 Role",

ARV2.Role_type_code "Child2 Role Type",

'/'

|| Connect_by_root ARV1.code

|| SYS_CONNECT_BY_PATH(ARV2.CODE, '/') "Path",

ARV2.Effective_end_date "Child2 Role End Date",

ARRM.effective_end_date "Role membership end date",

ARV1.last_update_date "LAST_UPDATE_DATE"

FROM ASE_ROLE_VL ARV1,

ASE_ROLE_VL ARV2,

WHERE ARV1.ROLE_ID = ARRM.CHILD_ROLE_ID (+)

AND ARV2.ROLE_ID = ARRM.PARENT_ROLE_ID

CONNECT BY prior ARRM.parent_role_id = ARRM.Child_Role_id

UNION

SELECT cast(1 as float) "HIERARCHY_LEVEL",

ARV1.ROLE_COMMON_NAME "TOP_ROLE_CODE",

ARV1.ROLE_NAME "Child1_Role_Name",

null "Child1 Role Code",

null "CHILD1_ROLE_ID",

null "Seeded Child1 Role",

null "Child1 Role Type",

null "Child1 End Date",

null "Child2_Role_Name",

null "Child2 Role Code",

null "CHILD2_ROLE_ID",

null "Seeded Child2 Role",

null "Child2 Role Type",

'/' || ARV1.ROLE_COMMON_NAME "Path",

null "Child2 Role End Date",

null "Role membership end date",

ARV1.last_update_date "LAST_UPDATE_DATE"

FROM per_roles_dn_vl ARV1

)

SELECT * FROM

(

SELECT

H."HIERARCHY_LEVEL" ROLE_LEVEL,

prdv_top_role.ROLE_NAME TOPROLE_NAME,

H."TOP_ROLE_CODE" TOP_ROLE_CODE,

prdv_top_role.ROLE_ID TOPROLE_ID,

prdv_top_role.role_guid TOPROLE_GUID,

H."Child1_Role_Name" CHILD1_ROLE_NAME,

H."Child1 Role Code" CHILD1_ROLE_CODE,

H."CHILD1_ROLE_ID" CHILD1_ROLE_ID,

H."Seeded Child1 Role" SEEDED_CHILD1_ROLE,

H."Child1 Role Type" CHILD1_ROLE_TYPE,

H."Child1 End Date" CHILD1_END_DATE,

H."Child2_Role_Name"CHILD2_ROLE_NAME,

H."Child2 Role Code" CHILD2_ROLE_CODE,

H."CHILD2_ROLE_ID" CHILD2_ROLE_ID,

H."Seeded Child2 Role" SEEDED_CHILD2_ROLE,

H."Child2 Role Type" CHILD2_ROLE_TYPE,

H."Path" PATH,

H."Child2 Role End Date" CHILD2_ROLE_END_DATE,

H."Role membership end date" ROLE_MEMBERSHIP_END_DATE,

H."LAST_UPDATE_DATE" HIERARCHY_TABLE_LAST_UPDT_DATE,

'OracleFusion' INSTANCE_NAME

FROM

HIERARCHY_TABLE H,

per_roles_dn_vl prdv_top_role --,

WHERE 1=1

and H.TOP_ROLE_CODE = prdv_top_role.role_common_name (+)

) R

[rushikeshvartak]

The object `ARRM` in the query provided refers to a table or view in the Oracle Fusion ERP system that contains role hierarchy information, specifically linking parent roles to child roles. This is often part of the data structure that represents Segregation of Duties (SOD) rules or role hierarchy in Oracle Fusion.

In this query:

- `ARRM.PARENT_ROLE_ID` and `ARRM.CHILD_ROLE_ID` are columns from the `ARRM` object that store IDs linking parent and child roles in a hierarchical structure.
- The query is using these columns to traverse and build a hierarchy of roles using the `CONNECT BY` clause, which is an Oracle SQL feature for handling hierarchical data.

Given the context and typical naming conventions, `ARRM` likely stands for something like "Application Role Relationship Mapping" or a similar concept, but the exact name and structure could vary depending on the specific Oracle Fusion ERP setup.

If the customer is having trouble creating a Data Model with the provided query, it might be due to a mismatch or an issue with accessing the `ARRM` object in their Oracle Fusion environment. They may need to verify that the `ARRM` object (table or view) exists, is correctly populated, and that the columns referenced in the query are available and properly defined.

[sksuresh2k30]

Thank you. Let me check with the customer and update you on the status.