--Order 20015705.60

---PRODUCT table---
ALTER TABLE PRODUCT ADD (TOTALPACKWEIGHT NUMBER(15,6) NULL);
ALTER TABLE PRODUCT ADD (PACKVOLUMEUNITS NUMBER(15,6) NULL);

COMMENT ON COLUMN PRODUCT.PACKTYPE
  IS '0 = No pack
1 = Non-OR Pack
2 = OR Pack 3 = Clean room product';

ALTER TABLE PRODUCT
  DROP CONSTRAINT PACKTYPE_VALID;
ALTER TABLE PRODUCT
  ADD CONSTRAINT PACKTYPE_VALID
  CHECK (PACKTYPE IN (0, 1, 2, 3));


---PACKCONTENTS table---
ALTER TABLE PACKCONTENTS ADD (ROWNUMBER NUMBER NULL);
ALTER TABLE PACKCONTENTS ADD (POSITIONNUMBER NUMBER NULL);

COMMENT ON COLUMN PACKCONTENTS.ROWNUMBER
  IS 'Only used in ORS module for OR packs';
COMMENT ON COLUMN PACKCONTENTS.POSITIONNUMBER
  IS 'Only used in ORS module for OR packs';


ALTER TABLE PACKCONTENTS MODIFY (PACKSEQUENCE NULL);

DROP INDEX XAK3PACKCONTENTS;
CREATE UNIQUE INDEX XAK3PACKCONTENTS ON PACKCONTENTS (PACK_ID, PACKSEQUENCE, ROWNUMBER, POSITIONNUMBER, DATEACTIVE);
 
ALTER TABLE PACKCONTENTS
  ADD CONSTRAINT DEFINITION_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)));
