INV_SECONDARY_INVENTORIES is the base table to hold the subinventory data. In this blog, we will discuss the Table structure, Indexes & Constraints of table INV_SECONDARY_INVENTORIES. In my experience, Oracle most often uses subinventory name, and not the id. Here are a few related posts that can help you:
Subinventory Query in Oracle Fusion
Fusion Inventory Table – inv_org_parameters
Fusion LoV Queries: Legal Entity, Business Unit, Inventory, Sub Inventory
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...
Subinventory Table Structure
| COLUMN NAME | DATA TYPE | DATA LENGTH | NULLABLE |
| SECONDARY_INVENTORY_NAME | VARCHAR2 | 40 | N |
| STANDARD_PACK_TYPE | VARCHAR2 | 12 | Y |
| STRUCTURE_INSTANCE_NUMBER | NUMBER | 22 | Y |
| ORGANIZATION_ID | NUMBER | 22 | N |
| OBJECT_VERSION_NUMBER | NUMBER | 22 | N |
| LAST_UPDATE_DATE | TIMESTAMP(6) | 11 | N |
| LAST_UPDATED_BY | VARCHAR2 | 256 | N |
| CREATION_DATE | TIMESTAMP(6) | 11 | N |
| CREATED_BY | VARCHAR2 | 256 | N |
| LAST_UPDATE_LOGIN | VARCHAR2 | 128 | Y |
| DESCRIPTION | VARCHAR2 | 200 | Y |
| DISABLE_DATE | DATE | 7 | Y |
| INVENTORY_ATP_CODE | NUMBER | 22 | N |
| AVAILABILITY_TYPE | NUMBER | 22 | N |
| RESERVABLE_TYPE | NUMBER | 22 | N |
| LOCATOR_TYPE | VARCHAR2 | 120 | Y |
| PICKING_ORDER | NUMBER | 22 | Y |
| QUANTITY_TRACKED | NUMBER | 22 | N |
| SUBINVENTORY_ID | NUMBER | 22 | N |
| ASSET_INVENTORY | NUMBER | 22 | N |
| SOURCE_TYPE | VARCHAR2 | 120 | Y |
| SOURCE_SUBINVENTORY | VARCHAR2 | 40 | Y |
| SOURCE_ORGANIZATION_ID | NUMBER | 22 | Y |
| REQUISITION_APPROVAL_TYPE | NUMBER | 22 | Y |
| FILL_KILL_TO_LOOKUP | VARCHAR2 | 120 | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2 | 120 | Y |
| ATTRIBUTE1 – 15 | VARCHAR2 | 600 | Y |
| REQUEST_ID | NUMBER | 22 | Y |
| PREPROCESSING_LEAD_TIME | NUMBER | 22 | Y |
| PROCESSING_LEAD_TIME | NUMBER | 22 | Y |
| POSTPROCESSING_LEAD_TIME | NUMBER | 22 | Y |
| DEMAND_CLASS | VARCHAR2 | 120 | Y |
| PROJECT_ID | NUMBER | 22 | Y |
| TASK_ID | NUMBER | 22 | Y |
| SUBINVENTORY_USAGE | NUMBER | 22 | Y |
| LOCATION_ID | NUMBER | 22 | Y |
| PICK_UOM_CODE | VARCHAR2 | 12 | Y |
| DEPRECIABLE_FLAG | NUMBER | 22 | Y |
| STATUS_ID | NUMBER | 22 | Y |
| DEFAULT_LOC_STATUS_ID | NUMBER | 22 | Y |
| LPN_CONTROLLED_FLAG | NUMBER | 22 | Y |
| PICK_METHODOLOGY | NUMBER | 22 | Y |
| CARTONIZATION_FLAG | NUMBER | 22 | Y |
| DROPPING_ORDER | NUMBER | 22 | Y |
| SUBINVENTORY_TYPE | VARCHAR2 | 120 | Y |
| PLANNING_LEVEL | NUMBER | 22 | Y |
| DEFAULT_COUNT_TYPE_CODE | VARCHAR2 | 120 | Y |
| ENABLE_BULK_PICK | VARCHAR2 | 4 | Y |
| ENABLE_LOCATOR_ALIAS | VARCHAR2 | 4 | Y |
| ENFORCE_ALIAS_UNIQUENESS | VARCHAR2 | 4 | Y |
| JOB_DEFINITION_NAME | VARCHAR2 | 400 | Y |
| JOB_DEFINITION_PACKAGE | VARCHAR2 | 3600 | Y |
| COUNT_METHOD | VARCHAR2 | 120 | Y |
| SUBINVENTORY_GROUP | VARCHAR2 | 400 | Y |
Indexes
| INDEX NAME | INDEX TYPE | UNIQUENESS | COLUMN NAME |
| INV_SECONDARY_INVENTORIES_N2 | NORMAL | NONUNIQUE | LAST_UPDATE_DATE |
| INV_SECONDARY_INVENTORIES_N3 | FUNCTION-BASED NORMAL | NONUNIQUE | ORGANIZATION_ID |
| INV_SECONDARY_INVENTORIES_N3 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00067$ |
| INV_SECONDARY_INVENTORIES_N4 | NORMAL | NONUNIQUE | SUBINVENTORY_GROUP |
| INV_SECONDARY_INVENTORIES_N4 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| INV_SECONDARY_INVS_N1 | NORMAL | NONUNIQUE | SUBINVENTORY_TYPE |
| INV_SECONDARY_INVS_N1 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| INV_SECONDARY_INVS_U1 | NORMAL | UNIQUE | SECONDARY_INVENTORY_NAME |
| INV_SECONDARY_INVS_U1 | NORMAL | UNIQUE | ORGANIZATION_ID |
Constraints
| CONSTRAINT_NAME | CONSTRAINT TYPE | SEARCH_CONDITION_VC |
| SYS_C0045704 | C | “LAST_UPDATE_DATE” IS NOT NULL |
| SYS_C0045701 | C | “SECONDARY_INVENTORY_NAME” IS NOT NULL |
| SYS_C0045703 | C | “OBJECT_VERSION_NUMBER” IS NOT NULL |
| SYS_C0045713 | C | “ASSET_INVENTORY” IS NOT NULL |
| SYS_C0045711 | C | “QUANTITY_TRACKED” IS NOT NULL |
| SYS_C0045709 | C | “AVAILABILITY_TYPE” IS NOT NULL |
| INV_SECONDARY_INVS_PK | P | |
| SYS_C0045712 | C | “SUBINVENTORY_ID” IS NOT NULL |
| SYS_C0045705 | C | “LAST_UPDATED_BY” IS NOT NULL |
| SYS_C0045707 | C | “CREATED_BY” IS NOT NULL |
| SYS_C0045702 | C | “ORGANIZATION_ID” IS NOT NULL |
| SYS_C0045706 | C | “CREATION_DATE” IS NOT NULL |
| SYS_C0045710 | C | “RESERVABLE_TYPE” IS NOT NULL |
| SYS_C0045708 | C | “INVENTORY_ATP_CODE” IS NOT NULL |
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