-- ABS-32035
-----------------------------------------------CONTRACT ----------------------------------
ALTER TABLE CONTRACT ADD (COMPLIANCYTEMPLATE_ID NUMBER NULL);


CREATE INDEX XIF19CONTRACT  ON CONTRACT (COMPLIANCYTEMPLATE_ID ASC);

ALTER TABLE CONTRACT ADD CONSTRAINT CONTRACT_COMPLIANCYTEMP_ID FOREIGN KEY (COMPLIANCYTEMPLATE_ID) REFERENCES COMPLIANCYTEMPLATE (COMPLIANCYTEMPLATE_ID) ON DELETE SET NULL;

-----------------------------------------------CONTRACTCOMPONENT------------------------

CREATE TABLE CONTRACTCOMPONENT
(
  CONTRACT_ID    NUMBER NOT NULL,
  GARMENTSCONTRACT NUMBER DEFAULT 0 NOT NULL CONSTRAINT GARMENTSCONTRACT_VALID1_1 CHECK (GARMENTSCONTRACT IN (0, 1)) ,
  CHARGEPREPCHARGE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD295 CHECK (CHARGEPREPCHARGE IN ('Y','N')),
  INVOICEFREQUENCY_ID NUMBER NOT NULL,
  MINIMUMINVOICEAMOUNT NUMBER DEFAULT 0 NOT NULL,
  SENDSTATEMENT NUMBER DEFAULT 3 NOT NULL CONSTRAINT SENDSTATEMENT_VALID1_1 CHECK (SENDSTATEMENT IN (1, 2, 3)),
  STATEMENTFREQUENCY_ID NUMBER NULL,
  SENDSTATEMENTBY NUMBER DEFAULT 0 NOT NULL CONSTRAINT SENDSTATEMENTBY_VALID1_1 CHECK (SENDSTATEMENTBY IN (0, 1, 2, 3)),
  ATTACHINVOICECOPIES VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD296 CHECK (ATTACHINVOICECOPIES IN ('Y','N')),
  STATEMENTPERCUSTOMER VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD297 CHECK (STATEMENTPERCUSTOMER IN ('Y','N')),
  PAYMENTTERMS_ID NUMBER NULL,
  FINANCECHARGEDAYS NUMBER DEFAULT 0 NOT NULL CONSTRAINT FINANCECHARGEDAYS_VALID1_1 CHECK (FINANCECHARGEDAYS BETWEEN 0 AND 999),
  PUTFINANCECHARGEON NUMBER DEFAULT 1 NOT NULL CONSTRAINT PUTFINANCECHARGEON_VALID1_1 CHECK (PUTFINANCECHARGEON IN (1, 2)),
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL
)
;

COMMENT ON COLUMN CONTRACTCOMPONENT.GARMENTSCONTRACT IS '0 = No Garments
1 = Include Garments in Contract
';

COMMENT ON COLUMN CONTRACTCOMPONENT.SENDSTATEMENT IS '1 = Always, with frequency
2 = Only if invoices are Past Due
3 = Never
';

COMMENT ON COLUMN CONTRACTCOMPONENT.SENDSTATEMENTBY IS '0 = Mail
1 = Fax
2 = Email
3 = EDI
';

COMMENT ON COLUMN CONTRACTCOMPONENT.PUTFINANCECHARGEON IS '1 = Invoice
2 = Statement
';

CREATE INDEX XIF1CONTRACTCOMPONENT ON CONTRACTCOMPONENT (CONTRACT_ID ASC);

CREATE INDEX XIF2CONTRACTCOMPONENT ON CONTRACTCOMPONENT (INVOICEFREQUENCY_ID ASC);

CREATE INDEX XIF3CONTRACTCOMPONENT ON CONTRACTCOMPONENT (STATEMENTFREQUENCY_ID ASC);

CREATE INDEX XIF4CONTRACTCOMPONENT ON CONTRACTCOMPONENT (PAYMENTTERMS_ID ASC);


ALTER TABLE CONTRACTCOMPONENT
  ADD CONSTRAINT CONTRACTCOMPONENT_CONTRACT FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTCOMPONENT
  ADD CONSTRAINT CONTRACTCOMPONENT_INVFREQ FOREIGN KEY (INVOICEFREQUENCY_ID)
  REFERENCES INVOICEFREQUENCY (INVOICEFREQUENCY_ID);

ALTER TABLE CONTRACTCOMPONENT
  ADD CONSTRAINT CONTRACTCOMP_STATEMENTFREQ FOREIGN KEY (STATEMENTFREQUENCY_ID)
  REFERENCES INVOICEFREQUENCY (INVOICEFREQUENCY_ID);

ALTER TABLE CONTRACTCOMPONENT
  ADD CONSTRAINT CONTRACTCOMPONENT_PAYMENTTERM FOREIGN KEY (PAYMENTTERMS_ID)
  REFERENCES PAYMENTTERMS (PAYMENTTERMS_ID);

------------------------------------------CONTRACTPRICE-------------------------------------

CREATE SEQUENCE SEQ_CONTRACTPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTPRICE
(
  CONTRACTPRICE_ID    NUMBER NOT NULL,
  CONTRACT_ID NUMBER NOT NULL,
  CATEGORY NUMBER DEFAULT 0 NOT NULL CONSTRAINT CATEGORY_VALID1_2 CHECK (CATEGORY IN (0, 1, 2, 3, 4, 5, 6, 7, 8)),
  PRICETYPE NUMBER NOT NULL,
  REASONCODE_ID NUMBER NULL,
  STARTDATE DATE NOT NULL,
  PRICE NUMBER(15,6) NOT NULL,
  RATIFICATIONSTARTDATE DATE NULL,
  RATIFICATIONUSER_ID NUMBER NULL,
  RATIFICATIONTIMESTAMP DATE NULL,
  CREATIONUSER_ID NUMBER NOT NULL,
  CREATIONTIMESTAMP DATE NOT NULL,
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL
);


COMMENT ON COLUMN CONTRACTPRICE.CATEGORY IS '0 = Not applicable
1 = Service Costs LOG
2 = Insurance - Weekly fee per piece
3 = Restocking Fee
4 = Stock Charge
5 = Locker
6 = General Prep Charge
7 = Surcharge Price
8 = Label Price
';

COMMENT ON COLUMN CONTRACTPRICE.PRICETYPE IS '0 = Not applicable
For Category = 1 (Service Costs)
1 = Service Costs LOG
2 = Service Costs COG
For Category = 5 (Locker)
1 = Rent price per week
2 = Service Price
For Category = 7 (Surcharge price)
1 = Non Standard Size Surcharge
2 = Special Cut Surcharge
For Category = 8 (Label Price)
1 = Code Label Price
2 = Name Label Price
3 = Residential Label
5 = Patch Price
Note : option 4 is not used to keep the numbering the same as in CustomerLabelLayoutPrice table.
';

COMMENT ON COLUMN CONTRACTPRICE.REASONCODE_ID IS 'Can only be used in combination with Category = 6, set to Null for all other categories.
';
ALTER TABLE CONTRACTPRICE ADD CONSTRAINT XPKCONTRACTPRICE PRIMARY KEY (CONTRACTPRICE_ID);


CREATE UNIQUE INDEX XAK1CONTRACTPRICE  ON 
CONTRACTPRICE(CONTRACT_ID ASC, CATEGORY ASC, PRICETYPE ASC, REASONCODE_ID ASC, STARTDATE ASC);

CREATE INDEX XIF1CONTRACTPRICE ON CONTRACTPRICE (CONTRACT_ID ASC);

CREATE INDEX XIF2CONTRACTPRICE ON CONTRACTPRICE (REASONCODE_ID ASC);

ALTER TABLE CONTRACTPRICE
  ADD CONSTRAINT CONTRACTPRICE_CONTRACT FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTPRICE
  ADD CONSTRAINT CONTRACTPRICE_REASONCODE FOREIGN KEY (REASONCODE_ID)
  REFERENCES REASONCODE (REASONCODE_ID);


-----------------------------------CONTRACTBILLINGCOMMENTS----------------------------------
CREATE SEQUENCE SEQ_CONTRACTBILLINGCOMMENTS
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTBILLINGCOMMENTS
(
  CONTRACTBILLINGCOMMENTS_ID NUMBER NOT NULL,
  CONTRACT_ID 	NUMBER NOT NULL,
  TYPE NUMBER NOT NULL CONSTRAINT TYPE_VALID1_2 CHECK (TYPE IN (1, 2)),  
  STARTDATE DATE NOT NULL,
  ENDDATE DATE NOT NULL,
  DESCRIPTION VARCHAR2(300) NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
);

COMMENT ON COLUMN CONTRACTBILLINGCOMMENTS.TYPE IS '1 = Invoice
2 = Statement
';

ALTER TABLE CONTRACTBILLINGCOMMENTS ADD CONSTRAINT XPKCONTRACTBILLINGCOMMENTS PRIMARY KEY (CONTRACTBILLINGCOMMENTS_ID);

CREATE UNIQUE INDEX XAK1CONTRACTBILLINGCOMMENTS ON 
CONTRACTBILLINGCOMMENTS(CONTRACT_ID ASC, TYPE ASC, STARTDATE ASC);

ALTER TABLE CONTRACTBILLINGCOMMENTS
ADD CONSTRAINT CONTRACTBILCOMMENTS_CONTRACT FOREIGN KEY (CONTRACT_ID)
REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

-----------------------------------CONTRACTATTRIBUTE--------------------------
ALTER TABLE CONTRACTATTRIBUTE DROP CONSTRAINT GROUPNUMBER_VALID ;

ALTER TABLE CONTRACTATTRIBUTE ADD CONSTRAINT GROUPNUMBER_VALID CHECK 
(GROUPNUMBER IN (1, 2, 3, 4, 5, 6, 7, 8, 9));

COMMENT ON COLUMN CONTRACTATTRIBUTE.GROUPNUMBER IS '1 = Miscellaneous
2 = Garments - Settings
3 = Garments – Locker Management
4 = Garments - Charges
5 = Garments – Wearer Functions
6 = Garments – Emblem Templates
7 = Invoicing
8 = Payment
9 = Residential
';
ALTER TABLE CONTRACTATTRIBUTE DROP COLUMN INTERNALNAMECONTRACT;

ALTER TABLE CONTRACTATTRIBUTE DROP COLUMN INTERNALNAMECUSTOMER;

ALTER TABLE CONTRACTATTRIBUTE ADD (COMBINEDWITH_ID NUMBER NULL);

CREATE INDEX XIF1CONTRACTATTRIBUTE ON CONTRACTATTRIBUTE(COMBINEDWITH_ID ASC);

ALTER TABLE CONTRACTATTRIBUTE
ADD CONSTRAINT CONTRATT_CONTRACTATT FOREIGN KEY (COMBINEDWITH_ID)
REFERENCES CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID) ON DELETE SET NULL;


INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (39, 4, 1, 'ChargePrepCharge', 'cbGeneralPrepCharge', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (40, 4, 2, 'PrepChargePrice', 'txtPrepChargePrice', GETMINSYSTEMUSER(), sysdate, 39);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (51, 7, 1, 'InvoiceFrequencyCode', 'cmbInvoiceFrequency', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (53, 7, 3, 'MinimumInvoiceAmount', 'txtMinInvoiceAmount', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (73, 7, 23, 'SendStatement', 'rbSendStatement', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (74, 7, 24, 'StatementFrequencyCode', 'cmbFrequencyStatement', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (75, 7, 26, 'AttachInvoiceCopies', 'cbAttachInvoice', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (76, 7, 27, 'StatementPerCustomer', 'cbStatementPerCustomer', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (77, 7, 28, 'InvoiceComments', 'btnCommentOnInvoice', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (79, 7, 30, 'StatementComments', 'btnCommentOnStatement', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (96, 7, 25, 'SendStatementByE-mail', 'cbSendByEmail', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, COMBINEDWITH_ID) VALUES (97, 7, 25, 'SendStatementByFax', 'cbSendByFax', GETMINSYSTEMUSER(), sysdate, NULL);
COMMIT;

UPDATE CONTRACTATTRIBUTE C
SET C.INTERNALNAME = 'cmbPaymentTerms'
WHERE C.FACTORYNAME = 'PaymentTermsCode';
COMMIT;

UPDATE CONTRACTATTRIBUTE C
SET C.INTERNALNAME = 'sfFinancialChargeDays'
WHERE C.FACTORYNAME = 'FinanceChargeDays';
COMMIT;

UPDATE CONTRACTATTRIBUTE C
SET C.INTERNALNAME = 'cbFinancialCharge'
WHERE C.FACTORYNAME = 'PutFinanceChargeOn';
COMMIT;
-----------------------------------CONTRACTCOMPLIANCY----------------------------------
CREATE SEQUENCE SEQ_CONTRACTCOMPLIANCY
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTCOMPLIANCY
(
  CONTRACTCOMPLIANCY_ID 	NUMBER NOT NULL,
  CONTRACT_ID 				NUMBER NOT NULL,
  CONTRACTATTRIBUTE_ID 		NUMBER NOT NULL,
  COMPLIANCYRULE 			NUMBER DEFAULT 0 NOT NULL CONSTRAINT COMPLIANCYRULE_VALID1_1 CHECK (COMPLIANCYRULE IN (0, 1, 2)),
  COMBINEDWITH_ID	 		NUMBER NULL,
  SYSTEMUSER_ID 			NUMBER NOT NULL,
  TIMESTAMP     			DATE NOT NULL
);

COMMENT ON COLUMN CONTRACTCOMPLIANCY.COMPLIANCYRULE IS '0 = Contract suggested, can be overruled 
1 = Contract enforced, can only be overruled by maintenance user
2 = Contract enforced
';

ALTER TABLE CONTRACTCOMPLIANCY ADD CONSTRAINT XPKCONTRACTCOMPLIANCY PRIMARY KEY (CONTRACTCOMPLIANCY_ID);

CREATE UNIQUE INDEX XAK1CONTRACTCOMPLIANCY ON 
CONTRACTCOMPLIANCY(CONTRACT_ID ASC, CONTRACTATTRIBUTE_ID ASC);

CREATE INDEX XIF1CONTRACTCOMPLIANCY ON CONTRACTCOMPLIANCY(CONTRACT_ID ASC);

CREATE INDEX XIF2CONTRACTCOMPLIANCY ON CONTRACTCOMPLIANCY(CONTRACTATTRIBUTE_ID ASC);

CREATE INDEX XIF3CONTRACTCOMPLIANCY ON CONTRACTCOMPLIANCY(COMBINEDWITH_ID ASC);

ALTER TABLE CONTRACTCOMPLIANCY
ADD CONSTRAINT CONTRCOMPL_CONTRACT FOREIGN KEY (CONTRACT_ID)
REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTCOMPLIANCY
ADD CONSTRAINT CONTRCOMPL_CONTRACTATTRIBUTE FOREIGN KEY (CONTRACTATTRIBUTE_ID)
REFERENCES CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID);

ALTER TABLE CONTRACTCOMPLIANCY
ADD CONSTRAINT CONTRCOMPL_CONTRACTCOMPLIANCY FOREIGN KEY (COMBINEDWITH_ID)
REFERENCES CONTRACTCOMPLIANCY (CONTRACTCOMPLIANCY_ID) ON DELETE SET NULL;


