-- ABS-5747

-----------------------STATUS-------------------------
ALTER TABLE STATUS MODIFY (STATUSTYPE VARCHAR2(30) CONSTRAINT STATUSTYPE_valid CHECK (STATUSTYPE IN ('IN', 'MO', 'PN', 'WN', 'ST', 'RB', 'WO', 'UI', 'SR', 'GD', 'PO', 'BN', 'UR', 'PL', 'CA', 'OR', 'SL', 'CO')));

COMMENT ON COLUMN STATUS.STATUSTYPE IS 'IN – Invoice
MO – Mounting Order
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
';

INSERT INTO STATUS
  (STATUS_ID, STATUSTYPE, CODE, LEGACYSTATUSCODE, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  select SEQ_STATUS.NEXTVAL,
            'CO',
             10,
             NULL,
             'New / Not yet approved',
             getminsystemuser(),
             sysdate
    from dual;
commit;

INSERT INTO STATUS
  (STATUS_ID, STATUSTYPE, CODE, LEGACYSTATUSCODE, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  select SEQ_STATUS.NEXTVAL,
            'CO',
             20,
             NULL,
             'Approved',
             getminsystemuser(),
             sysdate
    from dual;
commit;

INSERT INTO STATUS
  (STATUS_ID, STATUSTYPE, CODE, LEGACYSTATUSCODE, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  select SEQ_STATUS.NEXTVAL,
            'CO',
             30,
             NULL,
             'Extended',
             getminsystemuser(),
             sysdate
    from dual;
commit;

INSERT INTO STATUS
  (STATUS_ID, STATUSTYPE, CODE, LEGACYSTATUSCODE, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  select SEQ_STATUS.NEXTVAL,
            'CO',
             40,
             NULL,
             'Expired',
             getminsystemuser(),
             sysdate
    from dual;
commit;

INSERT INTO STATUS
  (STATUS_ID, STATUSTYPE, CODE, LEGACYSTATUSCODE, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  select SEQ_STATUS.NEXTVAL,
            'CO',
             50,
             NULL,
             'Terminated',
             getminsystemuser(),
             sysdate
    from dual;
commit;

-----------------------CONTRACT-------------------------

ALTER TABLE CONTRACT
	ADD (STATUS_ID NUMBER NULL,
	          CATEGORY NUMBER DEFAULT 1 NOT NULL CONSTRAINT CATEGORY_VALID1 CHECK (CATEGORY IN (1, 2, 3)),
	          MASTERAGREEMENT_ID NUMBER NULL,
	          CURRENCY_ID NUMBER NULL,
	          EMAIL VARCHAR2(300) NULL,
	          TRANSACTIONTYPE_ID NUMBER NULL);

COMMENT ON COLUMN CONTRACT.CATEGORY IS '1 = Service Contract
2 = Billing Contract
3 = All Inclusive Contract
';

CREATE INDEX XIF15CONTRACT ON CONTRACT
(STATUS_ID ASC);

CREATE INDEX XIF16CONTRACT ON CONTRACT
(MASTERAGREEMENT_ID ASC);

CREATE INDEX XIF17CONTRACT ON CONTRACT
(CURRENCY_ID ASC);

CREATE INDEX XIF18CONTRACT ON CONTRACT
(TRANSACTIONTYPE_ID ASC);

ALTER TABLE CONTRACT
ADD CONSTRAINT CONTRACT_STATUS FOREIGN KEY (STATUS_ID) REFERENCES STATUS(STATUS_ID);

ALTER TABLE CONTRACT
ADD CONSTRAINT CONTRACT_MASTERAGREEMENT FOREIGN KEY (MASTERAGREEMENT_ID) REFERENCES MASTERAGREEMENT(MASTERAGREEMENT_ID);

ALTER TABLE CONTRACT
ADD CONSTRAINT CONTRACT_CURRENCY FOREIGN KEY (CURRENCY_ID) REFERENCES CURRENCY(CURRENCY_ID);

ALTER TABLE CONTRACT
ADD CONSTRAINT CONTRACT_TRANSACTIONTYPE FOREIGN KEY (TRANSACTIONTYPE_ID) REFERENCES TRANSACTIONTYPE(TRANSACTIONTYPE_ID);

ALTER TABLE CONTRACT DROP CONSTRAINT CONTRACT_OWNERCUST_VALIDATION;

ALTER TABLE CONTRACT ADD CONSTRAINT CONTRACT_OWNERCUST_VALIDATION CHECK ((OWNER IS NULL AND CUSTOMER_LINK_ID IS NOT NULL AND MASTERAGREEMENT_ID IS NULL) OR (OWNER IS NOT NULL AND CUSTOMER_LINK_ID IS NULL AND MASTERAGREEMENT_ID IS NULL) OR (OWNER IS NULL AND CUSTOMER_LINK_ID IS  NULL AND MASTERAGREEMENT_ID IS NOT NULL));

UPDATE CONTRACT
SET STATUS_ID = (SELECT S.STATUS_ID FROM STATUS S WHERE S.CODE = '20' AND S.STATUSTYPE = 'CO');

COMMIT;

ALTER TABLE CONTRACT
	MODIFY (STATUS_ID NUMBER NOT NULL);