Inv_org_parameters table is one of the most important table if you have implemented inventory module in Oracle Fusion. Anywhere there has to be a linkage with inventory org, this table will come into the game. Example is querying egp_system_items_b table for a particular item_number.
Point to remember is that Unique index is on Organization_code but with upper case i.e. write the query like this:
SELECT *
FROM inv_org_parameters
WHERE UPPER(Organization_code) = ‘ABC’
Since you are querying inv_org_parameters table, you might have to create the LoVs for few related entities. Check out this post Fusion LoV Queries: Legal Entity, Business Unit, Inventory, Sub Inventory
Am pretty sure that you know about the subinventories. Need the query to get the subinventory details? This post will help you.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
So let’s discuss the table structure and constraints. Below are the important columns of the table. For complete list, click here.
| COLUMN NAME | DATA TYPE | DATA LENGTH | NULLABLE |
| ORGANIZATION_ID | NUMBER | 22 | N |
| ORGANIZATION_CODE | VARCHAR2 | 72 | Y |
| MASTER_ORGANIZATION_ID | NUMBER | 22 | Y |
| BUSINESS_UNIT_ID | NUMBER | 22 | Y |
| LEGAL_ENTITY_ID | NUMBER | 22 | Y |
| INVENTORY_FLAG | VARCHAR2 | 4 | N |
| ITEM_DEFINITION_ORG_ID | NUMBER | 22 | Y |
Indexes:
| INDEX NAME | UNIQUENESS | COLUMN NAME |
| INV_ORG_PARAMETERS_N1 | NONUNIQUE | MASTER_ORGANIZATION_ID |
| INV_ORG_PARAMETERS_U1 | UNIQUE | ORGANIZATION_ID |
| INV_ORG_PARAMETERS_U2 | UNIQUE | SYS_NC00150$ |
Constraints:
| CONSTRAINT NAME | CONSTRAINT TYPE | SEARCH CONDITION VC |
| SYS_C0093718 | C | “LAST_UPDATE_DATE” IS NOT NULL |
| SYS_C0093719 | C | “ORGANIZATION_ID” IS NOT NULL |
| SYS_C0093720 | C | “LAST_UPDATED_BY” IS NOT NULL |
| SYS_C0093721 | C | “CREATION_DATE” IS NOT NULL |
| SYS_C0093722 | C | “CREATED_BY” IS NOT NULL |
| SYS_C0093723 | C | “INVENTORY_FLAG” IS NOT NULL |
| SYS_C0093724 | C | “OBJECT_VERSION_NUMBER” IS NOT NULL |
Related Posts:
Fusion LoV Queries: Legal Entity, Business Unit, Inventory, Sub Inventory
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com