--order 502421
-- Add/modify columns 
alter table I_PRODUCT modify MAXWASHES null;
alter table I_PRODUCT add TREATMENTCHECKSCHEMECODE varchar2(6);
alter table I_PRODUCT add LOTNUMBERREQUIREDYN varchar2(1) default 'N' not null;
alter table I_PRODUCT add PACKTYPE number default 0 not null;
alter table I_PRODUCT add STERILE number default 0 not null;
alter table I_PRODUCT add TREATMENTTYPE varchar2(12);
alter table I_PRODUCT add EXPIRATIONCODE varchar2(6);
alter table I_PRODUCT add ASSEMBLYLAYOUT varchar2(6);
-- Add comments to the columns 
comment on column I_PRODUCT.TREATMENTCHECKSCHEMECODE
  is 'Makes field maxwashes obsolete';
comment on column I_PRODUCT.PACKTYPE
  is '0 = No pack (default)
1 = Non-OR Pack
2 = OR Pack';
comment on column I_PRODUCT.STERILE
  is '0 = Not sterile (default)
1 = Sterile';
comment on column I_PRODUCT.TREATMENTTYPE
  is 'TreatmentType (sterilization process). 
Must be defined if Sterile <> 0, and empty if sterile = 0';
comment on column I_PRODUCT.EXPIRATIONCODE
  is 'ExpirationCode must be defined if Sterile <> 0, and empty if sterile = 0';
comment on column I_PRODUCT.ASSEMBLYLAYOUT
  is 'Labellayout of labeltype=4';
-- Create/Recreate check constraints 
alter table I_PRODUCT
  add constraint I_PROD_LOTNUMBERREQUIREDYN
  check (LOTNUMBERREQUIREDYN IN ('Y', 'N'));
alter table I_PRODUCT
  add constraint I_PROD_PACKTYPE
  check (PACKTYPE IN (0,1,2));
alter table I_PRODUCT
  add constraint I_PROD_STERILE
  check (STERILE IN (0,1));
alter table I_PRODUCT
  add constraint I_PROD_EXPIRATIONCODE
  check (EXPIRATIONCODE IS NULL OR (EXPIRATIONCODE<>0 AND STERILE=0));
  
  
-- Add/modify columns 
alter table I_PRODUCTLISTPERCUSTOMER add TREATMENTCHECKSCHEMECODE varchar2(6);
alter table I_PRODUCTLISTPERCUSTOMER add DELIVERYLABELLAYOUT varchar2(6);
-- Add comments to the columns 
comment on column I_PRODUCTLISTPERCUSTOMER.DELIVERYLABELLAYOUT
  is 'FK to labellayout table, module ORS, labeltype=5';  
  
-- Add/modify columns 
alter table I_UNIQUEITEMFLAGS add TREATMENTCHECKSCHEMECODE varchar2(6);  

-- Create/Recreate primary, unique and foreign key constraints 
alter table I_UNIQUEPOOL
  add constraint PK_I_UNIQUEPOOL primary key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER);
  
-- Add/modify columns 
alter table I_UNIQUEITEM modify IDCODESEQUENCENUMBER default 1;
alter table I_UNIQUEPOOL modify IDCODESEQUENCENUMBER default 1;  

-- Create/Recreate primary, unique and foreign key constraints 
alter table I_UNIQUEITEM
  add constraint FK_I_UNIQUEITEM_BU foreign key (BUSINESSUNIT)
  references businessunit (CODE);
  
-- Create/Recreate primary, unique and foreign key constraints 
alter table I_UNIQUEPOOL
  add constraint FK_I_UNIQUEPOOL_BU foreign key (BUSINESSUNIT)
  references businessunit (CODE);  

-- Create table
create table I_PACKCONTENT
(
  PACKCODE            VARCHAR2(12) not null,
  PACKSEQUENCE        NUMBER default 1 not null,
  PRODUCTCODE         VARCHAR2(12) not null,
  SIZECODE            VARCHAR2(12) default '-' not null,
  FINISHINGMETHODCODE VARCHAR2(6) default '-' not null,
  AMOUNT              NUMBER default 1 not null,
  REMARK              VARCHAR2(80),
  LOCATION            VARCHAR2(6),
  DATEACTIVE          DATE default sysdate-1 not null,
  DATEINACTIVE        DATE default to_date('31-12-2099','dd-mm-yyyy') not null
);
-- Add comments to the columns 
comment on column I_PACKCONTENT.PACKCODE
  is 'FK to I_PRODUCT';
comment on column I_PACKCONTENT.PRODUCTCODE
  is 'FK to I_PRODUCT';
comment on column I_PACKCONTENT.AMOUNT
  is 'Pack amount';
-- Create/Recreate primary, unique and foreign key constraints 
alter table I_PACKCONTENT
  add constraint PK_I_PACKCONTENT primary key (PACKCODE, PACKSEQUENCE, PRODUCTCODE, SIZECODE, FINISHINGMETHODCODE);
alter table I_PACKCONTENT
  add constraint FK_I_PACKCONTENT_PACKCODE foreign key (PACKCODE)
  references I_PRODUCT (CODE) on delete cascade;
alter table I_PACKCONTENT
  add constraint FK_I_PACKCONTENT_PRODUCT foreign key (PRODUCTCODE)
  references I_PRODUCT (CODE) on delete cascade;
-- Create/Recreate check constraints 
alter table I_PACKCONTENT
  add constraint CHK_I_PACKCONTENT_DATECHECK
  check (DATEACTIVE <= DATEINACTIVE);
alter table I_PACKCONTENT
  add constraint CHK_I_PACKCONTENT_FIN
  check (FINISHINGMETHODCODE=UPPER(TRIM(FINISHINGMETHODCODE)));
alter table I_PACKCONTENT
  add constraint CHK_I_PACKCONTENT_PRODUCT
  check (PRODUCTCODE=UPPER(TRIM(PRODUCTCODE)));
alter table I_PACKCONTENT
  add constraint CHK_I_PACKCONTENT_SIZE
  check (SIZECODE=UPPER(TRIM(SIZECODE)));

-- Create table
create table I_PACKCONTENTSALTERNATIVE
(
  PACKCODE               VARCHAR2(12) not null,
  PACKSEQUENCE           NUMBER not null,
  PRODUCTCODE            VARCHAR2(12) not null,
  SIZECODE               VARCHAR2(12) default '-' not null,
  FINISHINGMETHODCODE    VARCHAR2(6) default '-' not null,
  SEQUENCENUMBER         NUMBER(1) default 1 not null,
  ALTERNATIVEPRODUCTCODE VARCHAR2(12) not null,
  ALTERNATIVESIZECODE    VARCHAR2(12) default '-' not null
);
-- Add comments to the columns 
comment on column I_PACKCONTENTSALTERNATIVE.PACKCODE
  is 'FK to I_PRODUCT';
comment on column I_PACKCONTENTSALTERNATIVE.PRODUCTCODE
  is 'FK to I_PRODUCT';
comment on column I_PACKCONTENTSALTERNATIVE.SEQUENCENUMBER
  is 'Priority 1 is first, max is 9';
-- Create/Recreate primary, unique and foreign key constraints 
alter table I_PACKCONTENTSALTERNATIVE
  add constraint PK_I_PACKCONTENTALTERNATVE primary key (PACKCODE, PACKSEQUENCE, PRODUCTCODE, SIZECODE, FINISHINGMETHODCODE, SEQUENCENUMBER);
alter table I_PACKCONTENTSALTERNATIVE
  add constraint FK_I_PACKCONTENTALTERNATVE_PCK foreign key (PACKCODE, PACKSEQUENCE, PRODUCTCODE, SIZECODE, FINISHINGMETHODCODE)
  references I_PACKCONTENT (PACKCODE, PACKSEQUENCE, PRODUCTCODE, SIZECODE, FINISHINGMETHODCODE) on delete cascade;
alter table I_PACKCONTENTSALTERNATIVE
  add constraint FK_I_PACKCONTENTALTERNATVE_PRD foreign key (ALTERNATIVEPRODUCTCODE)
  references I_PRODUCT (CODE);
-- Create/Recreate check constraints 
alter table I_PACKCONTENTSALTERNATIVE
  add constraint CHK_I_PACKCONTENTALTERNAT_ALSZ
  check (ALTERNATIVESIZECODE=UPPER(TRIM(ALTERNATIVESIZECODE)));
alter table I_PACKCONTENTSALTERNATIVE
  add constraint CHK_I_PACKCONTENTALTERNAT_ALT
  check (PRODUCTCODE || SIZECODE <> ALTERNATIVEPRODUCTCODE || ALTERNATIVESIZECODE);
alter table I_PACKCONTENTSALTERNATIVE
  add constraint CHK_I_PACKCONTENTALTERNAT_FIN
  check (FINISHINGMETHODCODE=UPPER(TRIM(FINISHINGMETHODCODE)));
alter table I_PACKCONTENTSALTERNATIVE
  add constraint CHK_I_PACKCONTENTALTERNAT_PROD
  check (PRODUCTCODE=UPPER(TRIM(PRODUCTCODE)));
alter table I_PACKCONTENTSALTERNATIVE
  add constraint CHK_I_PACKCONTENTALTERNAT_SEQ
  check (SEQUENCENUMBER between 1 and 9);
alter table I_PACKCONTENTSALTERNATIVE
  add constraint CHK_I_PACKCONTENTALTERNAT_SIZE
  check (SIZECODE=UPPER(TRIM(SIZECODE)));

-- Create table
create table I_UNIQUEORPACK
(
  BUSINESSUNIT           VARCHAR2(6) not null,
  PRIMARYID              VARCHAR2(24) not null,
  IDCODESEQUENCENUMBER   NUMBER default 1 not null,
  EXPIRATIONDATE         DATE default to_date('31-12-2099','dd-mm-yyyy') not null,
  TREATMENTTYPE          VARCHAR2(12),
  STOCKEXPIRATIONDATE    DATE,
  CREATIONTIMESTAMP      DATE default sysdate-1 not null,
  CREATIONEMPLOYEENUMBER NUMBER default 1 not null,
  ASSEMBLYLAYOUT         VARCHAR2(6),
  ASSEMBLETIMESTAMP      DATE,
  ASSEMBLEEMPLOYEENUMBER NUMBER,
  LOADTIMESTAMP          DATE,
  LOADEMPLOYEENUMBER     NUMBER,
  DELIVERYTIMESTAMP      DATE,
  DELIVERYEMPLOYEENUMBER NUMBER,
  CANCELTIMESTAMP        DATE,
  CANCELEMPLOYEENUMBER   NUMBER,
  RECALLTIMESTAMP        DATE,
  RECALLEMPLOYEENUMBER   NUMBER,
  REASONCODE             VARCHAR2(6)
);
-- Add comments to the columns 
comment on column I_UNIQUEORPACK.BUSINESSUNIT
  is 'Must exist in the I_UNIQUEPOOL';
comment on column I_UNIQUEORPACK.PRIMARYID
  is 'Must exist in the I_UNIQUEPOOL';
comment on column I_UNIQUEORPACK.IDCODESEQUENCENUMBER
  is 'Must exist in the I_UNIQUEPOOL';
comment on column I_UNIQUEORPACK.CREATIONTIMESTAMP
  is 'Date / time of creation';
comment on column I_UNIQUEORPACK.ASSEMBLETIMESTAMP
  is 'Date / time of assembly';
comment on column I_UNIQUEORPACK.LOADTIMESTAMP
  is 'Date / time of loading pack on cart';
comment on column I_UNIQUEORPACK.DELIVERYTIMESTAMP
  is 'Date / time of deliverynote';
comment on column I_UNIQUEORPACK.CANCELTIMESTAMP
  is 'Date / time of canceling the pack';
comment on column I_UNIQUEORPACK.RECALLTIMESTAMP
  is 'Date / time of recalling the pack';
comment on column I_UNIQUEORPACK.REASONCODE
  is 'Reason code for canceling or recall';
-- Create/Recreate primary, unique and foreign key constraints 
alter table I_UNIQUEORPACK
  add constraint PK_I_UNIQUEORPACK primary key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER);
alter table I_UNIQUEORPACK
  add constraint FK_I_UNIQUEORPACK foreign key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER)
  references I_UNIQUEPOOL (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER);
alter table I_UNIQUEORPACK
  add constraint FK_I_UNIQUEORPACK_BU foreign key (BUSINESSUNIT)
  references businessunit (CODE);

-- Create table
create table I_UNIQUEORSTERILE
(
  BUSINESSUNIT                VARCHAR2(6) not null,
  PRIMARYID                   VARCHAR2(24) not null,
  IDCODESEQUENCENUMBER        NUMBER default 1 not null,
  CHARGENUMBER                VARCHAR2(15) not null,
  STERILIZERCODE              VARCHAR2(6) not null,
  STERILIZATIONTIMESTAMP      DATE not null,
  STERILIZATIONEMPLOYEENUMBER NUMBER not null,
  REJECTIONREASONCODE         VARCHAR2(6)
);
-- Add comments to the columns 
comment on column I_UNIQUEORSTERILE.BUSINESSUNIT
  is 'Must exist in I_UNIQUEORPACK';
comment on column I_UNIQUEORSTERILE.PRIMARYID
  is 'Must exist in I_UNIQUEORPACK';
comment on column I_UNIQUEORSTERILE.IDCODESEQUENCENUMBER
  is 'Must exist in I_UNIQUEORPACK';
comment on column I_UNIQUEORSTERILE.CHARGENUMBER
  is 'Must be uppercase, and no spaces allowed CHK_I_UNIQUEORSTERILE_CHARGE';
comment on column I_UNIQUEORSTERILE.STERILIZATIONTIMESTAMP
  is 'Date / time of sterilization';
comment on column I_UNIQUEORSTERILE.REJECTIONREASONCODE
  is 'Optional rejection reason code';
-- Create/Recreate primary, unique and foreign key constraints 
alter table I_UNIQUEORSTERILE
  add constraint PK_I_UNIQUEORSTERILE primary key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER, CHARGENUMBER);
alter table I_UNIQUEORSTERILE
  add constraint FK_I_UNIQUEORSTERILE foreign key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER)
  references I_UNIQUEORPACK (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER) on delete cascade;
alter table I_UNIQUEORSTERILE
  add constraint FK_I_UNIQUEORSTERILE_BU foreign key (BUSINESSUNIT)
  references businessunit (CODE);  
-- Create/Recreate check constraints 
alter table I_UNIQUEORSTERILE
  add constraint CHK_I_UNIQUEORSTERILE_CHARGE
  check (CHARGENUMBER = UPPER(TRIM(CHARGENUMBER)));
