-- ABS-73843 / ABS-83205

------------ STATUS -----------------

ALTER TABLE STATUS DROP CONSTRAINT STATUSTYPE_VALID;
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'));
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
CA - Cart
OR - OR Pack
SL - Shipment List
CO - Contract
CN - Consignment Note
';
INSERT INTO STATUS
          (STATUS_ID,
           STATUSTYPE,
           CODE,
           LEGACYSTATUSCODE,
           FACTORYDESCRIPTION,
           SYSTEMUSER_ID,
           TIMESTAMP)
        VALUES
          (SEQ_STATUS.NEXTVAL,
           'CN',
           '10',
           '',
           'New',
           GETMINSYSTEMUSER(),
           SYSDATE);
COMMIT;

INSERT INTO STATUS
          (STATUS_ID,
           STATUSTYPE,
           CODE,
           LEGACYSTATUSCODE,
           FACTORYDESCRIPTION,
           SYSTEMUSER_ID,
           TIMESTAMP)
        VALUES
          (SEQ_STATUS.NEXTVAL,
           'CN',
           '20',
           '',
           'In progress',
           GETMINSYSTEMUSER(),
           SYSDATE);
COMMIT;

INSERT INTO STATUS
          (STATUS_ID,
           STATUSTYPE,
           CODE,
           LEGACYSTATUSCODE,
           FACTORYDESCRIPTION,
           SYSTEMUSER_ID,
           TIMESTAMP)
        VALUES
          (SEQ_STATUS.NEXTVAL,
           'CN',
           '30',
           '',
           'Processed',
           GETMINSYSTEMUSER(),
           SYSDATE);
COMMIT;

INSERT INTO STATUS
          (STATUS_ID,
           STATUSTYPE,
           CODE,
           LEGACYSTATUSCODE,
           FACTORYDESCRIPTION,
           SYSTEMUSER_ID,
           TIMESTAMP)
        VALUES
          (SEQ_STATUS.NEXTVAL,
           'CN',
           '40',
           '',
           'Cancelled',
           GETMINSYSTEMUSER(),
           SYSDATE);
COMMIT;


-------------------- CONSIGNMENTNOTE -----------------

ALTER TABLE CONSIGNMENTNOTE ADD (STATUS_ID NUMBER NULL,
                                 REMARK CLOB NULL,
                                 EMPLOYEE_ID NUMBER NULL);

UPDATE CONSIGNMENTNOTE CN
SET CN.STATUS_ID = (SELECT STATUS_ID FROM STATUS WHERE STATUS.STATUSTYPE='CN' AND STATUS.CODE='30')
WHERE CN.STATUS=0;
COMMIT;

UPDATE CONSIGNMENTNOTE CN
SET CN.STATUS_ID= (SELECT STATUS_ID FROM STATUS WHERE STATUS.STATUSTYPE='CN' AND STATUS.CODE='40')
WHERE CN.STATUS=1;
COMMIT;

ALTER TABLE CONSIGNMENTNOTE
MODIFY STATUS_ID NUMBER NOT NULL;

ALTER TABLE CONSIGNMENTNOTE ADD CONSTRAINT FK_CONSIGNMENTNOTE_STATUS_ID
FOREIGN KEY(STATUS_ID) 
REFERENCES STATUS(STATUS_ID);

ALTER TABLE CONSIGNMENTNOTE ADD CONSTRAINT FK_CONSIGNMENTNOTE_EMPLOYEE_ID
FOREIGN KEY(EMPLOYEE_ID) 
REFERENCES EMPLOYEE(EMPLOYEE_ID)
ON DELETE SET NULL;

ALTER TABLE CONSIGNMENTNOTE SET UNUSED COLUMN STATUS;