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;