-- SO20015705 OR Product update 
ALTER TABLE ERP_I_PRODUCT
  DROP CONSTRAINT CHK_ERP_I_PRODUCT_PACKTYPE;
ALTER TABLE ERP_I_PRODUCT
  ADD CONSTRAINT CHK_ERP_I_PRODUCT_PACKTYPE
  CHECK (PACKTYPE IN (0, 1, 2, 3));
COMMENT ON COLUMN PRODUCT.PACKTYPE
  IS '0 = No pack
1 = Non-OR Pack
2 = OR Pack 
3 = Clean room product';
ALTER TABLE ERP_I_PRODUCT
  DROP CONSTRAINT CHK_ERP_I_PRODUCT_ORPCKCNTAL;
ALTER TABLE ERP_I_PRODUCT
  ADD CONSTRAINT CHK_ERP_I_PRODUCT_ORPCKCNTAL
  CHECK (ORPACKCONTENTSALLOWED IN (0, 1, 2, 3));  
  
-- Drop check constraints, another CHK_ERP_I_PRODYCT_TOM exists 
alter table ERP_I_PRODUCT
  drop constraint CHK_ERP_I_PRODUCT_TYPEOMERCH;  
  
--Add columns ERP_I_PRODUCT
ALTER TABLE ERP_I_PRODUCT
	ADD (CONFIRMPRODUCTONPDA NUMBER DEFAULT 1 NOT NULL 
  CONSTRAINT ERPCONFIRMPRODUCTONPDA_VALID1 CHECK (CONFIRMPRODUCTONPDA IN (1, 2)));
COMMENT ON COLUMN ERP_I_PRODUCT.CONFIRMPRODUCTONPDA IS '1 = Yes
2 = No'; 
ALTER TABLE ERP_I_PRODUCT ADD (TOTALPACKWEIGHT NUMBER(15,6) NULL);
ALTER TABLE ERP_I_PRODUCT ADD (PACKVOLUMEUNITS NUMBER(15,6) NULL);

-- Create table ERP_I_PACKCONTENTS
CREATE TABLE ERP_I_PACKCONTENTS
(
  TRANSFER_ID        NUMBER not null,
  TRANSFER_STATUS    NUMBER default 0 not null,
  PARENTPRODUCT      VARCHAR2(12) NOT NULL,
  PACKSEQUENCE       NUMBER,
  ROWNUMBER          NUMBER,
  POSITIONNUMBER     NUMBER,  
  CONTENT_PRODUCTCODE VARCHAR2(12) NOT NULL,
  CONTENT_SIZECODE   VARCHAR2(12) NOT NULL,
  CONTENT_FINISHINGMETHOD VARCHAR2(6) NOT NULL,
  QUANTITY           NUMBER DEFAULT 1 NOT NULL,
  REMARK             VARCHAR2(80),
  LOCATION           VARCHAR2(6),
  UNIQUELYCODED      VARCHAR2(1) DEFAULT 'N' NOT NULL,
  DATEACTIVE         DATE DEFAULT TO_DATE('01-01-2015','DD-MM-YYYY') NOT NULL,
  DATEINACTIVE       DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  ALLOWPACKCONTENTS  NUMBER(1) DEFAULT 0 NOT NULL
);
-- Add comments to the columns 
comment on column ERP_I_PACKCONTENTS.PACKSEQUENCE
  is 'Products will be packed in this order';
comment on column ERP_I_PACKCONTENTS.ALLOWPACKCONTENTS
  is '0 = Allow general pool only
1 = Allow customer related pool
2 = Allow wearer related items
3 = Allow customer related pool and wearer related items';
comment on column ERP_I_PACKCONTENTS.ROWNUMBER
  is 'Only used in ORS module for OR packs';
comment on column ERP_I_PACKCONTENTS.POSITIONNUMBER
  is 'Only used in ORS module for OR packs';
comment on column ERP_I_PACKCONTENTS.QUANTITY
  is 'remark this is the AMOUNT in the packcontent table'; 
comment on column ERP_I_PACKCONTENTS.TRANSFER_ID
  is 'Link to ERP_I_PRODUCT.TRANSFER_ID';
comment on column ERP_I_PACKCONTENTS.PARENTPRODUCT
  is 'Link to the product code of ERP_I_PRODUCT.CODE';  

-- Create/Recreate primary, unique and foreign key constraints 
create unique index PK_ERP_I_PACKCONTENTS on ERP_I_PACKCONTENTS (TRANSFER_ID, PARENTPRODUCT, PACKSEQUENCE, ROWNUMBER, POSITIONNUMBER, DATEACTIVE);

alter table ERP_I_PACKCONTENTS
  add constraint FK_ERP_I_PACKCONTENTS foreign key (TRANSFER_ID, PARENTPRODUCT)
  references ERP_I_PRODUCT (TRANSFER_ID, CODE) on delete cascade;

-- Create/Recreate check constraints 
alter table ERP_I_PACKCONTENTS
  add constraint ERPALLOWPACKCONTENTS_VALID
  check (ALLOWPACKCONTENTS IN (0, 1, 2, 3));
alter table ERP_I_PACKCONTENTS
  add constraint ERPDEFINITION_VALID
  check (( (PACKSEQUENCE IS NULL AND ROWNUMBER IS NOT NULL AND POSITIONNUMBER IS NOT NULL) OR (PACKSEQUENCE IS NOT NULL AND ROWNUMBER IS NULL AND POSITIONNUMBER IS NULL) ));
alter table ERP_I_PACKCONTENTS
  add constraint ERP_YN_VALIDATIONNEW181
  check (UNIQUELYCODED IN ('Y', 'N'));
