-- Drop i-table
drop table I_STANDARDINVENTORY;
-- Recreate i-table
create table I_STANDARDINVENTORY
(
  CUSTOMERNUMBER      NUMBER not null,
  PRODUCTCODE         VARCHAR2(12) not null,
  FUNCTIONCODE        VARCHAR2(6) not null,
  FUNCTIONDESCRIPTION VARCHAR2(30) not null,
  TEMPLATECODE        VARCHAR2(6),
  MAXINVENTORY        NUMBER default 1 not null,
  CHANGESPERWEEK      NUMBER default 1 not null,
  TYPEOFMERCHANDISE   NUMBER default 2 not null
);
-- Add comments to the columns 
comment on column I_STANDARDINVENTORY.MAXINVENTORY
  is 'MAXINVENTORY = PUTINUSE >= 1 ';
comment on column I_STANDARDINVENTORY.CHANGESPERWEEK
  is 'CHANGESPERWEEK >= 1';
comment on column I_STANDARDINVENTORY.TYPEOFMERCHANDISE
  is 'Always TOM; 2=Garments';
-- Create/Recreate primary, unique and foreign key constraints 
alter table I_STANDARDINVENTORY
  add constraint PK_I_STANDARDINVENTORY primary key (CUSTOMERNUMBER, FUNCTIONCODE, PRODUCTCODE);
alter table I_STANDARDINVENTORY
  add constraint FK_I_STANDARDINVENTORY_CUST foreign key (CUSTOMERNUMBER)
  references I_CUSTOMER (CUSTOMERNUMBER) on delete cascade;
alter table I_STANDARDINVENTORY
  add constraint FK_I_STANDARDINVENTORY_PROD foreign key (PRODUCTCODE, TYPEOFMERCHANDISE)
  references I_PRODUCT (CODE, TYPEOFMERCHANDISE) on delete cascade;
-- Create/Recreate check constraints 
alter table I_STANDARDINVENTORY
  add constraint CHK_I_STANDARDINV_CHANGESPERWK
  check (CHANGESPERWEEK >= 1);
alter table I_STANDARDINVENTORY
  add constraint CHK_I_STANDARDINV_MAXINVENTORY
  check (MAXINVENTORY >= 1);
-- Create/Recreate indexes 
create index INDX_I_STANDARDINVENTORY on I_STANDARDINVENTORY (CUSTOMERNUMBER, TEMPLATECODE, PRODUCTCODE);
create index TIDX2DELIVERYHISTORY on DELIVERYHISTORY (deliverydate, product_link_id, sizedefinition_link_id, finishingmethod_id);
drop index XIF272PACKINGNOTE;
create index XIF272PACKINGNOTE on PACKINGNOTE (processbusinessunit_id, notenumber);

