--ABS-5747

------------------------------------------------CONTRACTATTRIBUTE---------------------------------
CREATE SEQUENCE SEQ_CONTRACTATTRIBUTE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTATTRIBUTE
(
CONTRACTATTRIBUTE_ID NUMBER NOT NULL,
GROUPNUMBER NUMBER DEFAULT 1 NOT NULL CONSTRAINT GROUPNUMBER_VALID CHECK (GROUPNUMBER IN (1, 2, 3, 4, 5)),
SEQUENCENUMBER NUMBER DEFAULT 0 NOT NULL CONSTRAINT SEQUENCENUMBER_VALID6 CHECK (SEQUENCENUMBER BETWEEN 0 AND 999),
FACTORYNAME VARCHAR2(50) NOT NULL,
INTERNALNAMECONTRACT VARCHAR2(30) NULL,
INTERNALNAMECUSTOMER VARCHAR2(30) NULL,
INTERNALNAME VARCHAR2(30) NULL,
SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN CONTRACTATTRIBUTE.GROUPNUMBER IS '1 = Contract Billing - General
2 = Contract Billing - Garments
3 = Contract Billing - Invoicing
4 = Contract Billing - Residential
5 = Contract Price Lists
';

COMMENT ON COLUMN CONTRACTATTRIBUTE.SEQUENCENUMBER IS '0 – 999
Sequence number can be used to specify the sequence within the group, so the attributes can be displayed in a logical order, and attributes that belong together are shown behind each other.
';

ALTER TABLE CONTRACTATTRIBUTE ADD CONSTRAINT XPKCONTRACTATTRIBUTE PRIMARY KEY (CONTRACTATTRIBUTE_ID);

CREATE UNIQUE INDEX XAK1CONTRACTATTRIBUTE ON CONTRACTATTRIBUTE (FACTORYNAME ASC);


INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (80, 3, 31, 'PaymentMethod', NULL, 'rbPaymentMethod', 0, sysdate);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (81, 3, 32, 'PaymentType' ,NULL , 'cmbPaymentType',0, sysdate);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (82, 3, 33, 'PayByCustomerNumber', NULL, 'cbPayByCustomer', 0, sysdate);
COMMIT;

 
INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (83, 3, 34, 'PaymentTermsCode',NULL , 'cmbPaymentTerms', 0, sysdate);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (84, 3, 35, 'CreditSqueezePerc',NULL , 'txtCreditSqueeze', 0, sysdate);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (85, 3, 36, 'CreditLimit',NULL , 'txtCreditLimit', 0, sysdate);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (86, 3, 37, 'DelinquentMarkAfter',NULL , 'sfDelinquentAfter', 0, sysdate);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (87, 3, 38, 'CollectionResponsibleEmployeeNumber', NULL, 'srfCollectionResponsible', 0, sysdate);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (88, 3, 39, 'FinanceChargeDays', NULL, 'sfFinancialChargeDays', 0, sysdate);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAMECONTRACT, INTERNALNAMECUSTOMER, SYSTEMUSER_ID, TIMESTAMP) VALUES (89, 3, 40, 'PutFinanceChargeOn', NULL, 'cbFinancialCharge', 0, sysdate);
COMMIT;



------------------------------------------------COMPLIANCYTEMPLATE---------------------------------
CREATE SEQUENCE SEQ_COMPLIANCYTEMPLATE
START WITH 1
INCREMENT BY 1;

CREATE TABLE COMPLIANCYTEMPLATE
(
COMPLIANCYTEMPLATE_ID NUMBER NOT NULL,
CODE VARCHAR2(6) NOT NULL,
DESCRIPTION VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE COMPLIANCYTEMPLATE ADD CONSTRAINT XPKCOMPLIANCYTEMPLATE PRIMARY KEY (COMPLIANCYTEMPLATE_ID);

CREATE UNIQUE INDEX XAK1COMPLIANCYTEMPLATE ON COMPLIANCYTEMPLATE (CODE ASC);


------------------------------------------------COMPLIANCYTEMPLATELINE---------------------------------
CREATE SEQUENCE SEQ_COMPLIANCYTEMPLATELINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE COMPLIANCYTEMPLATELINE
(
COMPLIANCYTEMPLATELINE_ID NUMBER NOT NULL,
COMPLIANCYTEMPLATE_ID NUMBER NOT NULL,
CONTRACTATTRIBUTE_ID NUMBER NOT NULL,
COMPLIANCYRULE NUMBER DEFAULT 0 NOT NULL CONSTRAINT COMPLIANCYRULE_VALID CHECK (COMPLIANCYRULE IN (0, 1, 2)),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN COMPLIANCYTEMPLATELINE.COMPLIANCYRULE IS '0 = Contract suggested, can be overruled 
1 = Restricted to qualified user
2 = Contract enforced
';
ALTER TABLE COMPLIANCYTEMPLATELINE ADD CONSTRAINT XPKCOMPLIANCYTEMPLATELINE PRIMARY KEY (COMPLIANCYTEMPLATELINE_ID);

CREATE UNIQUE INDEX XAK1COMPLIANCYTEMPLATELINE ON COMPLIANCYTEMPLATELINE(COMPLIANCYTEMPLATE_ID ASC, CONTRACTATTRIBUTE_ID ASC);

CREATE INDEX XIF1COMPLIANCYTEMPLATELINE ON COMPLIANCYTEMPLATELINE(COMPLIANCYTEMPLATE_ID ASC);

CREATE INDEX XIF2COMPLIANCYTEMPLATELINE ON COMPLIANCYTEMPLATELINE(CONTRACTATTRIBUTE_ID ASC);

ALTER TABLE COMPLIANCYTEMPLATELINE ADD CONSTRAINT COMPLTEMPLINE_COMPLIANCYTEMP  FOREIGN KEY (COMPLIANCYTEMPLATE_ID) REFERENCES COMPLIANCYTEMPLATE(COMPLIANCYTEMPLATE_ID) ON DELETE CASCADE;

ALTER TABLE COMPLIANCYTEMPLATELINE ADD CONSTRAINT COMPLEMPLINE_CONTRACTATT FOREIGN KEY (CONTRACTATTRIBUTE_ID)
REFERENCES CONTRACTATTRIBUTE (CONTRACTATTRIBUTE_ID);


