Organization SQL Query in Oracle Cloud ERP
The main table for inventory organizations and parameters is called INV_ORG_PARAMETERS. The MASTER_ORGANIZATION_ID relates back to the same table listing the parent of the organization. Other organization information is stored in Human Resource (HR) tables like HR_ORGANIZATION_UNITS_F_TL.
Note that the _TL table is stripped by language so the example query just looks at the US language.
You can change it as required. The meaning of the code for each parameter is found in FND_LOOKUPS. Here is a sample query looking at organizations’ parameters.
There are many other interesting columns but this query focuses on a few related to the main controls of the organization:
SELECT
a.ORGANIZATION_ID,
a.ORGANIZATION_CODE,
g.NAME ORGANIZATION_NAME,
a.MASTER_ORGANIZATION_ID,
b.ORGANIZATION_CODE MASTER_ORGANIZATION_CODE,
a.BUSINESS_UNIT_ID,
a.LEGAL_ENTITY_ID,
a.CALENDAR_CODE,
a.NEGATIVE_INV_RECEIPT_CODE,
e.MEANING NEG_MEANING,
a.STOCK_LOCATOR_CONTROL_CODE,
c.meaning STOCK_LOCATOR_MEANING,
a.SERIAL_NUMBER_TYPE,
d.meaning SERIAL_NUMBER_TYPE_MEANING,
a.LOT_NUMBER_UNIQUENESS,
f.meaning LOT_UNIQ_MEANING
FROM
INV_ORG_PARAMETERS a,
INV_ORG_PARAMETERS b,
FND_LOOKUPS c,
FND_LOOKUPS d,
FND_LOOKUPS e,
FND_LOOKUPS f,
HR_ORGANIZATION_UNITS_F_TL g
WHERE
a.MASTER_ORGANIZATION_ID = b.ORGANIZATION_ID
AND c.LOOKUP_TYPE = 'INV_LOCATION_CONTROL'
AND a.STOCK_LOCATOR_CONTROL_CODE = c.LOOKUP_CODE (+)
AND d.LOOKUP_TYPE = 'INV_SERIAL_NUMBER'
AND a.SERIAL_NUMBER_TYPE = d.LOOKUP_CODE (+)
AND e.LOOKUP_TYPE = 'INV_YES_NO_NUMERIC'
AND a.NEGATIVE_INV_RECEIPT_CODE = e.LOOKUP_CODE (+)
AND f.LOOKUP_TYPE = 'INV_LOT_UNIQUENESS'
AND a.LOT_NUMBER_UNIQUENESS = f.LOOKUP_CODE (+)
AND g.ORGANIZATION_ID = a.ORGANIZATION_ID
AND g.LANGUAGE = 'US'
ORDER BY
a.ORGANIZATION_ID;
