Query to get organization structure details in Oracle Fusion General Ledger

The Oracle Fusion Application allows to create the Legal Entity, Business Unit to define the association and the balancing segments for the legal entity as per the enterprise structure of the organization

The blog provides the SQL Query to retrieve the Organization Structure details in Oracle Fusion with the association of legal entities, business unit , legal entity registration and general ledger mapping for the defined organization structure

Query to get organization structure details in Oracle Fusion General Ledger

SELECT
HR_ORGN_UNITS_TBL.NAME ORGANIZATION
, HR_ORGN_UNITS_TBL.ORGANIZATION_ID ORGANIZATION_ID
, XLE_REGN_TBL.REGISTERED_NAME
, XLE_REGN_TBL.ALTERNATE_REGISTERED_NAME
, XLE_REGN_TBL.REGISTRATION_NUMBER
, XLE_REGN_TBL.PLACE_OF_REGISTRATION
, XLE_REGN_TBL.EFFECTIVE_FROM
, XLE_REGN_TBL.EFFECTIVE_TO
, XLE_ENTITY_PROF_TBL.NAME "LEGAL_ENTITY_NAME"
, XLE_ENTITY_PROF_TBL.LEGAL_ENTITY_IDENTIFIER
, HR_OPER_UNITS_TBL.BUSINESS_GROUP_ID
, HR_OPER_UNITS_TBL.ORGANIZATION_ID "BU_ID"
, HR_OPER_UNITS_TBL.NAME "BU_NAME"
, HR_OPER_UNITS_TBL.DATE_FROM "BU_FROM_DATE"
, HR_OPER_UNITS_TBL.DATE_TO "BU_TO_DATE"
, HR_OPER_UNITS_TBL.SHORT_CODE "BU_SHORT_CODE"
, HR_OPER_UNITS_TBL.SET_OF_BOOKS_ID
, GL_LEDGERS_TBL.NAME LEDGER_NAME
, GL_LEDGERS_TBL.LEDGER_ID
FROM
XLE_ENTITY_PROFILES XLE_ENTITY_PROF_TBL
, XLE_REGISTRATIONS XLE_REGN_TBL
, HR_OPERATING_UNITS HR_OPER_UNITS_TBL
, HR_ORGANIZATION_UNITS HR_ORGN_UNITS_TBL
, GL_LEDGER_NORM_SEG_VALS GLNSV_TBL
, GL_LEDGERS GL_LEDGERS_TBL
WHERE
XLE_ENTITY_PROF_TBL.LEGAL_ENTITY_ID = XLE_REGN_TBL.SOURCE_ID
AND XLE_REGN_TBL.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND XLE_ENTITY_PROF_TBL.LEGAL_ENTITY_ID = HR_OPER_UNITS_TBL.DEFAULT_LEGAL_CONTEXT_ID(+)
AND HR_ORGN_UNITS_TBL.ORGANIZATION_ID = HR_OPER_UNITS_TBL.BUSINESS_GROUP_ID
AND GLNSV_TBL.LEGAL_ENTITY_ID = XLE_ENTITY_PROF_TBL.LEGAL_ENTITY_ID
AND GL_LEDGERS_TBL.LEDGER_ID = GLNSV_TBL.LEDGER_ID;