Oracle Apps Organization Structure Query Explained with SQL Examples

1.

SELECT GL.LEDGER_ID SOB_ID,GL.NAME SOB_NAME,GL.CHART_OF_ACCOUNTS_ID
FROM GL_LEDGERS GL

2.

SELECT HO.BUSINESS_GROUP_ID,HO.ORGANIZATION_ID,HO.NAME OPERATING_UNIT_NAME
FROM HR_OPERATING_UNITS HO

3.

SELECT HOU.BUSINESS_GROUP_ID,HOU.ORGANIZATION_ID,HOU.NAME ORGANIZATION_UNIT_NAME,HOU.LOCATION_ID
FROM HR_ORGANIZATION_UNITS HOU
WHERE HOU.BUSINESS_GROUP_ID<>0


4.
SELECT MP.MASTER_ORGANIZATION_ID,MP.ORGANIZATION_CODE,MP.*
FROM  MTL_PARAMETERS MP

/*****QUERY************/

SELECT GL.LEDGER_ID SOB_ID,GL.NAME SOB_NAME,GL.CHART_OF_ACCOUNTS_ID,
HO.BUSINESS_GROUP_ID,HO.ORGANIZATION_ID OPERATING_UNIT_ID,HO.NAME OPERATING_UNIT_NAME,
HOU.ORGANIZATION_ID,HOU.NAME ORGANIZATION_UNIT_NAME,HOU.LOCATION_ID,
MP.MASTER_ORGANIZATION_ID,MP.ORGANIZATION_CODE
FROM GL_LEDGERS GL,
     HR_OPERATING_UNITS HO,
     HR_ORGANIZATION_UNITS HOU,
     MTL_PARAMETERS MP
WHERE GL.LEDGER_ID=HO.SET_OF_BOOKS_ID
  AND HO.BUSINESS_GROUP_ID=HOU.BUSINESS_GROUP_ID
  AND HOU.ORGANIZATION_ID=MP.ORGANIZATION_ID(+)