Query to find inventory organization in Oracle fusion

Query to find inventory organization in Oracle fusion

In this post , we will be discuss about the Query to find inventory organization in Oracle fusion. This query will help to extract the important inventory organization informations in oracle fusion. we will get the different organization parameters , with accounting , Costing , Inventory and other receiving related informations using this query oracle fusion. Here below is the detail sql query to find inventory organization in Oracle fusion.

Important Tables used by Query to find inventory organization in Oracle fusion

1.HR_ALL_ORGANIZATION_UNITS_X

2.HR_ORG_UNIT_CLASSIFICATIONS_X

3. INV_ORG_PARAMETERS

4.FUN_ALL_BUSINESS_UNITS_V

5. GL_LEDGERS 

Detail SQL Query to find inventory organization in Oracle fusion

Here below is the query to extract the inventory organization information in oracle fusion.

SELECT HOU.ORGANIZATION_ID ORGANIZATION_ID,

HOU.NAME ORGANIZATION_NAME,

MP.ORGANIZATION_CODE ORGANIZATION_CODE,

LGR.LEDGER_ID SET_OF_BOOKS_ID,

LGR.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID,

LGR.CURRENCY_CODE,

LGR.PERIOD_SET_NAME,

DECODE(HOI1.STATUS, 'A', 'Y', 'N') INVENTORY_ENABLED_FLAG,

BU.BU_NAME BUSINESS_UNIT_NAME,

MP.BUSINESS_UNIT_ID BUSINESS_UNIT_ID,

MP.LEGAL_ENTITY_ID LEGAL_ENTITY,

HOU.TYPE ORGANIZATION_TYPE

FROM HR_ALL_ORGANIZATION_UNITS_X HOU,

HR_ORG_UNIT_CLASSIFICATIONS_X HOI1,

INV_ORG_PARAMETERS MP,

GL_LEDGERS LGR,

FUN_ALL_BUSINESS_UNITS_V BU

WHERE HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID

AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID

AND HOI1.CLASSIFICATION_CODE = 'INV'

AND BU.PRIMARY_LEDGER_ID = LGR.LEDGER_ID(+)

AND LGR.OBJECT_TYPE_CODE(+) = 'L'

AND NVL(LGR.COMPLETE_FLAG, 'Y') = 'Y'

AND BU.BU_ID(+) = MP.BUSINESS_UNIT_ID