Product

Organization SQL Query in Oracle Cloud ERP

By September 14, 2020 April 29th, 2021 No Comments

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;