--ABS-6127

-----------------------------------CUSTOMERDOWNPAYMENT--------------------------

CREATE SEQUENCE SEQ_CUSTOMERDOWNPAYMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERDOWNPAYMENT
(
CUSTOMERDOWNPAYMENT_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
STARTDATE DATE DEFAULT SYSDATE NOT NULL,
ENDDATE DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
COSTCENTER_ID NUMBER NOT NULL,
AMOUNT NUMBER NOT NULL,
INVOICEFREQUENCY_ID NUMBER NOT NULL,
PAYMENTTERMS_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN CUSTOMERDOWNPAYMENT.INVOICEFREQUENCY_ID IS 'Code 0 (Direct) means one time down payment
';
ALTER TABLE CUSTOMERDOWNPAYMENT ADD CONSTRAINT XPKCUSTOMERDOWNPAYMENT PRIMARY KEY (CUSTOMERDOWNPAYMENT_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERDOWNPAYMENT ON CUSTOMERDOWNPAYMENT (CUSTOMER_ID ASC, STARTDATE ASC, COSTCENTER_ID ASC);

CREATE INDEX XIF1CUSTOMERDOWNPAYMENT ON CUSTOMERDOWNPAYMENT(CUSTOMER_ID ASC);

CREATE INDEX XIF2CUSTOMERDOWNPAYMENT ON CUSTOMERDOWNPAYMENT(COSTCENTER_ID ASC);

CREATE INDEX XIF3CUSTOMERDOWNPAYMENT ON CUSTOMERDOWNPAYMENT(INVOICEFREQUENCY_ID ASC);

CREATE INDEX XIF4CUSTOMERDOWNPAYMENT ON CUSTOMERDOWNPAYMENT(PAYMENTTERMS_ID ASC);

ALTER TABLE CUSTOMERDOWNPAYMENT ADD CONSTRAINT CUSTOMERDOWNPAY_CUSTID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERDOWNPAYMENT ADD CONSTRAINT CUSTOMERDOWNPAY_CC FOREIGN KEY (COSTCENTER_ID)
REFERENCES COSTCENTER (COSTCENTER_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERDOWNPAYMENT ADD CONSTRAINT CUSTOMERDOWNPAY_INVFREQ FOREIGN KEY (INVOICEFREQUENCY_ID)
REFERENCES INVOICEFREQUENCY (INVOICEFREQUENCY_ID);

ALTER TABLE CUSTOMERDOWNPAYMENT ADD CONSTRAINT CUSTOMERDOWNPAY_PAYTERMS FOREIGN KEY (PAYMENTTERMS_ID)
REFERENCES PAYMENTTERMS (PAYMENTTERMS_ID);

------------------------------------------------CUSTOMERDOWNPAYMENTINVLINE---------------------------------

CREATE TABLE CUSTOMERDOWNPAYMENTINVLINE
(
CUSTOMERDOWNPAYMENT_ID NUMBER NOT NULL,
INVOICELINE_ID NUMBER NOT NULL,
CREDITINVOICELINE_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);


ALTER TABLE CUSTOMERDOWNPAYMENTINVLINE ADD CONSTRAINT XPKCUSTOMERDOWNPAYMENTINVLINE PRIMARY KEY (CUSTOMERDOWNPAYMENT_ID, INVOICELINE_ID);

CREATE INDEX XIE1CUSTOMERDOWNPAYMENTINVLINE ON CUSTOMERDOWNPAYMENTINVLINE (CUSTOMERDOWNPAYMENT_ID ASC, CREDITINVOICELINE_ID ASC);

CREATE INDEX XIF1CUSTOMERDOWNPAYMENTINVLINE ON CUSTOMERDOWNPAYMENTINVLINE(CUSTOMERDOWNPAYMENT_ID ASC);

CREATE INDEX XIF2CUSTOMERDOWNPAYMENTINVLINE ON CUSTOMERDOWNPAYMENTINVLINE(INVOICELINE_ID ASC);

CREATE INDEX XIF3CUSTOMERDOWNPAYMENTINVLINE ON CUSTOMERDOWNPAYMENTINVLINE(CREDITINVOICELINE_ID ASC);

ALTER TABLE CUSTOMERDOWNPAYMENTINVLINE ADD CONSTRAINT CUSTDOWNPAYINVLINE_CUSTDOWNPAY  FOREIGN KEY (CUSTOMERDOWNPAYMENT_ID) REFERENCES CUSTOMERDOWNPAYMENT (CUSTOMERDOWNPAYMENT_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERDOWNPAYMENTINVLINE ADD CONSTRAINT CUSTDOWNPAYINVLINE_INVLINE FOREIGN KEY (INVOICELINE_ID)
REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERDOWNPAYMENTINVLINE ADD CONSTRAINT CUSTDOWNPAYINVLINE_CREDINVLINE FOREIGN KEY (CREDITINVOICELINE_ID)
REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE SET NULL;

------------------------------------------------PLANT---------------------------------
ALTER TABLE PLANT
	ADD (DEFAULTDOWNPAYMENTTERMS_ID NUMBER NULL);

CREATE INDEX XIF7PLANT ON PLANT(DEFAULTDOWNPAYMENTTERMS_ID ASC);

ALTER TABLE PLANT ADD CONSTRAINT PLANT_PAYMENTTERMS  FOREIGN KEY (DEFAULTDOWNPAYMENTTERMS_ID) REFERENCES PAYMENTTERMS (PAYMENTTERMS_ID) ON DELETE SET NULL;


------------------------------------------------INVOICEHEADER---------------------------------
ALTER TABLE INVOICEHEADER MODIFY (INVOICETYPE NUMBER CONSTRAINT INVOICETYPE_valid CHECK (INVOICETYPE IN (1, 2, 4, 5, 6, 7, 8)));

COMMENT ON COLUMN INVOICEHEADER.INVOICETYPE IS '1=Generated
2=Manual
4=Additional Generated
5=Lost Customer
6=Corrections Invoice
7 = Excess Amounts / insurance company invoice
8 = Down Payment Invoice
';

------------------------------------------------INVOICELINETYPE---------------------------------
insert into invoicelinetype
            (invoicelinetype_id,
             type,
             factorydescription,
             includeinminimumcalc,
             taxtype,
             defineturnovergroup,
             definespecialcharge,
             supressprinting,
             systemuser_id,
             timestamp)
          values
            (32,
             32,
             'Down Payment',
             'N',
             3,
             'Y',
             'N',
             0,
             getminsystemuser(),
             sysdate);
commit;

ALTER TABLE INVOICELINETYPE MODIFY (TYPE NUMBER CONSTRAINT TYPE_valid CHECK (TYPE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32)));

COMMENT ON COLUMN INVOICELINETYPE.TYPE IS '01= Rent
02= Delivery (wash)
03= Soil Weight
04= Clean Weight
05= Sales
06= Repair
07= Replacement charge
08= Automatic Replacement (non-garm)
09= Loss charge
10= Remaining value charge
11= Overuse charge
12= Prep / Emblem charge
13= Finance charge
14= Cash Delay line
15= Miscellaneous Invoice line
16= Special Charge
17= Minimum invoice amount
18= Tax
19=Emblem Rent
20=Locker Rent
21=Locker service
22=Lease
23=Stock Charge
24=Lost Customer Residual Value Charge
25=Lost Revenue Charge
26 = Residential wash
27 = Residential labels
28 = Service costs
29 = Capped Excess Amounts
30 = Client rent
31 = Budget
32 = Down Payment
';


------------------------------------------------INVOICELINESUBTYPE---------------------------------

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             0,
             32,
             'Down Payment Debit',
             1,
             sysdate
        from dual;
commit;

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             1,
             32,
             'Down Payment Credit',
             1,
             sysdate
        from dual;
commit;