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;

Other Sample Queries

Hello there
Request A Quote
Ready to Upgrade Your SQL Workflow?
Hello there
Looking for a partner to streamline your data tasks? SQL Connect is here to pull data from multiple Oracle ERP/SCM/HCM Pods. Run ad hoc queries, extract data to CSV or Excel, and make the most of your data. Discover the ease and efficiency of SQL Connect today!
Hello there
Select your version
Hello there
Would you like to view the installation guide?
Hello there
Step 1. Run the downloaded file to install SQL Connect on your computer.

Step 2: Register for Access
If you don’t have a username and password:
  • Click the “SIGN-UP” button on the login screen.
  • Complete the registration form.
  • Check your email for your login credentials.
installation guide
Step 3. First Time Launch
Hello there
Open SQL Connect:
  • For Windows: Go to Start Menu -> Programs -> SQL Connect -> SQL Connect.
  • For Mac: Open Applications and find SQL Connect.
    Logging In:
    • Enter the username and password provided by your administrator or received via email.
    • Note: Trial credentials expire after 15 days.
      Step 4. Creating a New Oracle Connection
      Hello there
      1. Open the Create Connection window in the application.
      2. Enter the following details:
        • Oracle Connection Name: Choose a name for this connection.
        • Oracle Cloud Username: Your user ID for Oracle Cloud.
        • Password: Your Oracle Cloud password. Use the EYE icon to show/hide the password.
        • URL: The web address for your Oracle pod, e.g., 'http://EEEE.us6.oraclecloud.oracle.com'
      3. Click "NEXT" to test and save the connection. If there's an error, check the details or contact your system administrator.
      Step 5. Using SQL connect
      Hello there
      To connect to a database, select “Connect To” and pick your pod from the dropdown list.

      For any issues, refer to the FAQ or contact your administrator. If you are using an enterprise license, please get assistance from your company’s administrator.
      Hello there
      Step 1. Run the downloaded file to install SQL Connect on your computer.

      Step 2: Register for Access
      If you don’t have a username and password:
      • Click the “SIGN-UP” button on the login screen.
      • Complete the registration form.
      • Check your email for your login credentials.
      installation guide
      Step 2. First Time Launch
      Hello there
      Open SQL Connect:
      • For Windows: Go to Start Menu -> Programs -> SQL Connect -> SQL Connect.
      • For Mac: Open Applications and find SQL Connect.
        Logging In:
        • Enter the username and password provided by your administrator or received via email.
        • Note: Trial credentials expire after 15 days.
          Step 3. Creating a New Oracle Connection
          Hello there
          1. Open the Create Connection window in the application.
          2. Enter the following details:
            • Oracle Connection Name: Choose a name for this connection.
            • Oracle Cloud Username: Your user ID for Oracle Cloud.
            • Password: Your Oracle Cloud password. Use the EYE icon to show/hide the password.
            • URL: The web address for your Oracle pod, e.g., 'http://EEEE.us6.oraclecloud.oracle.com'
          3. Click "NEXT" to test and save the connection. If there's an error, check the details or contact your system administrator.
          Step 4. Using SQL connect
          Hello there
          To connect to a database, select “Connect To” and pick your pod from the dropdown list.

          For any issues, refer to the FAQ or contact your administrator. If you are using an enterprise license, please get assistance from your company’s administrator.