-- ABS-32035 - changes after analysis

-----------------------------------------------CONTRACTCOMPONENT------------------------
ALTER TABLE CONTRACTCOMPONENT DROP CONSTRAINT GARMENTSCONTRACT_VALID1_1;

-----------------------------------------------CONTRACT ----------------------------------
ALTER TABLE CONTRACT ADD (GARMENTSCONTRACT NUMBER DEFAULT 0 NOT NULL CONSTRAINT GARMENTSCONTRACT_VALID1_1 CHECK (GARMENTSCONTRACT IN (0, 1)));

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

-----------------------------------------------CONTRACTCOMPONENT------------------------
DROP TABLE CONTRACTCOMPONENT CASCADE CONSTRAINTS;

-----------------------------------------------CONTRACTCOMPONENTGRMCHANGES------------------------
CREATE TABLE CONTRACTCOMPONENTGRMCHANGES
(
  CONTRACT_ID    NUMBER NOT NULL,
  CHARGEPREPCHARGE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD295 CHECK (CHARGEPREPCHARGE IN ('Y','N')),
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL);

CREATE INDEX XIF1CONTRACTCOMPGRMCHANGES ON CONTRACTCOMPONENTGRMCHANGES (CONTRACT_ID ASC);

ALTER TABLE CONTRACTCOMPONENTGRMCHANGES
  ADD CONSTRAINT CONTRCOMPGRMCH_CONTRACT FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

-----------------------------------------------CONTRACTCOMPONENTBILLING------------------------
CREATE TABLE CONTRACTCOMPONENTBILLING
(
  CONTRACT_ID    NUMBER NOT NULL,
  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')),
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL);


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

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

CREATE INDEX XIF1CONTRACTCOMPBILLING ON CONTRACTCOMPONENTBILLING (CONTRACT_ID ASC);

CREATE INDEX XIF2CONTRACTCOMPBILLING ON CONTRACTCOMPONENTBILLING (INVOICEFREQUENCY_ID ASC);

CREATE INDEX XIF3CONTRACTCOMPBILLING ON CONTRACTCOMPONENTBILLING (STATEMENTFREQUENCY_ID ASC);

ALTER TABLE CONTRACTCOMPONENTBILLING
  ADD CONSTRAINT CONTRCOMPBILL_CONTRACT FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTCOMPONENTBILLING
  ADD CONSTRAINT CONTRCOMPBILL_INVFREQ FOREIGN KEY (INVOICEFREQUENCY_ID)
  REFERENCES INVOICEFREQUENCY (INVOICEFREQUENCY_ID);

ALTER TABLE CONTRACTCOMPONENTBILLING
  ADD CONSTRAINT CONTRCOMPBILL_STATEMENTFREQ FOREIGN KEY (STATEMENTFREQUENCY_ID)
  REFERENCES INVOICEFREQUENCY (INVOICEFREQUENCY_ID);

-----------------------------------------------CONTRACTCOMPONENTPAYMENT------------------------

CREATE TABLE CONTRACTCOMPONENTPAYMENT
(
  CONTRACT_ID    NUMBER NOT NULL,
  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 CONTRACTCOMPONENTPAYMENT.PUTFINANCECHARGEON IS '1 = Invoice
2 = Statement
';

CREATE INDEX XIF1CONTRACTCOMPPAY ON CONTRACTCOMPONENTPAYMENT (CONTRACT_ID ASC);

CREATE INDEX XIF2CONTRACTCOMPPAY ON CONTRACTCOMPONENTPAYMENT (PAYMENTTERMS_ID ASC);

ALTER TABLE CONTRACTCOMPONENTPAYMENT
  ADD CONSTRAINT CONTRCOMPPAY_CONTRACT FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTCOMPONENTPAYMENT
  ADD CONSTRAINT CONTRCOMPPAY_PAYMENTTERM FOREIGN KEY (PAYMENTTERMS_ID)
  REFERENCES PAYMENTTERMS (PAYMENTTERMS_ID);

