-- Create table
create table INV.MTL_ONHAND_QUANTITIES_DETAIL
(
INVENTORY_ITEM_ID NUMBER not null,
ORGANIZATION_ID NUMBER not null,
DATE_RECEIVED DATE,
LAST_UPDATE_DATE DATE not null,
LAST_UPDATED_BY NUMBER not null,
CREATION_DATE DATE not null,
CREATED_BY NUMBER not null,
LAST_UPDATE_LOGIN NUMBER,
PRIMARY_TRANSACTION_QUANTITY NUMBER not null,
SUBINVENTORY_CODE VARCHAR2(10) not null,
REVISION VARCHAR2(3),
LOCATOR_ID NUMBER,
CREATE_TRANSACTION_ID NUMBER,
UPDATE_TRANSACTION_ID NUMBER,
LOT_NUMBER VARCHAR2(30),
ORIG_DATE_RECEIVED DATE,
COST_GROUP_ID NUMBER,
CONTAINERIZED_FLAG NUMBER,
PROJECT_ID NUMBER,
TASK_ID NUMBER,
ONHAND_QUANTITIES_ID NUMBER not null,
ORGANIZATION_TYPE NUMBER not null,
OWNING_ORGANIZATION_ID NUMBER not null,
OWNING_TP_TYPE NUMBER not null,
PLANNING_ORGANIZATION_ID NUMBER not null,
PLANNING_TP_TYPE NUMBER not null,
TRANSACTION_UOM_CODE VARCHAR2(3) not null,
TRANSACTION_QUANTITY NUMBER not null,
SECONDARY_UOM_CODE VARCHAR2(3),
SECONDARY_TRANSACTION_QUANTITY NUMBER,
IS_CONSIGNED NUMBER not null,
LPN_ID NUMBER
)
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 10
maxtrans 255
storage
(
initial 128
next 128
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create index INV.MTL_ONHAND_QUANTITIES_N4 on INV.MTL_ONHAND_QUANTITIES_DETAIL (INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE, PRIMARY_TRANSACTION_QUANTITY)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index INV.MTL_ONHAND_QUANTITIES_N5 on INV.MTL_ONHAND_QUANTITIES_DETAIL (ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID, LPN_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index INV.MTL_ONHAND_QUANTITIES_N6 on INV.MTL_ONHAND_QUANTITIES_DETAIL (ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_GROUP_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Grant/Revoke object privileges
grant select, insert, update, delete, references, alter, index on INV.MTL_ONHAND_QUANTITIES_DETAIL to APPS with grant option;