-- ABS-81878/ ABS-99452

-------------- FLAG -----------
ALTER TABLE FLAG ADD (SERVICETYPE NUMBER DEFAULT 0 NOT NULL
                      CONSTRAINT CHK_FLAG_SERVICETYPE CHECK (SERVICETYPE IN (0, 1)));

COMMENT ON COLUMN FLAG.SERVICETYPE
  IS 'Only used for module UNIQUEITEMSERVICE
0 = No Action
1 = Remove
';

--If constraint below fails to be added then wrong records must be manually checked 
--FLAGTYPE cannot have a DEFAULT value, so we cannot update wrong records to a DEFAULT value
ALTER TABLE FLAG
  ADD CONSTRAINT CHK_FLAG_FLAGTYPE
  CHECK (FLAGTYPE IN (1, 2, 3, 4, 5));

COMMENT ON COLUMN FLAG.FLAGTYPE
  IS 'Possible flag types:
1=Garment/wearer flags
2=OR-pack flags
3=Container flags
4=Residential/domestic
5=Unique Item Service
';

-------------- PRODUCTCABINETSERVICE -----------
RENAME PRODUCTCABINETSERVICE TO PRODUCTUNIQUEITEMSERVICE;

ALTER INDEX XPKPRODUCTCABINETSERVICE RENAME TO XPKPRODUCTUNIQUEITEMSERVICE;

ALTER TABLE PRODUCTUNIQUEITEMSERVICE
 RENAME CONSTRAINT XPKPRODUCTCABINETSERVICE TO XPKPRODUCTUNIQUEITEMSERVICE;
ALTER TABLE PRODUCTUNIQUEITEMSERVICE
 RENAME CONSTRAINT FK_PRODUCTCABINETSERVICE_PROD TO FK_PRODUCTUISERVICE_PROD;

ALTER TABLE PRODUCTUNIQUEITEMSERVICE
 RENAME CONSTRAINT CHK_PRODUCTCABINETSERVICE_TY TO CHK_PRODUCTUISERVICE_TY;


-------------- STATUS --------------
COMMENT ON COLUMN STATUS.STATUSTYPE
  IS 'IN - Invoice
PN - Packing Note / Delivery Note
WN - Weighing Note
ST - Statement
RB - Remit batch
WO - Work Order
UI - Unique item
SR - Straight Even Return
GD - Garment Dispenser
PO - Purchase Order
BN - Unique Bundle
UR - Unique Residential Item
PL - Pick List
MO - Mounting Order
CA - Cart
OR - OR Pack
SL - Shipment List
CO - Contract
CN - Consignment Note
US - Unique Item Service
';

ALTER TABLE STATUS DROP CONSTRAINT CHK_STATUS_STATUSTYPE;

ALTER TABLE STATUS
  ADD CONSTRAINT CHK_STATUS_STATUSTYPE
  CHECK (STATUSTYPE IN ('IN', 'MO', 'PN', 'WN', 'ST', 'RB', 'WO', 'UI', 'SR', 'GD', 'PO', 'BN', 'UR', 'PL', 'CA', 'OR', 'SL', 'CO', 'CN', 'US'));