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
Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports. 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