-- ABS-39489/ ABS-47533

---INVOICELINETYPE table---
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
33= Statement fee';

ALTER TABLE INVOICELINETYPE DROP CONSTRAINT TYPE_valid;

ALTER TABLE INVOICELINETYPE
  ADD CONSTRAINT TYPE_valid CHECK (TYPE BETWEEN 1 AND 33);

insert into invoicelinetype
            (invoicelinetype_id,
             type,
             factorydescription,
             includeinminimumcalc,
             taxtype,
             defineturnovergroup,
             definespecialcharge,
             supressprinting,
             systemuser_id,
             timestamp)
          values
            (33,
             33,
             'Statement fee',
             'Y',
             3,
             'Y',
             'N',
             0,
             getminsystemuser(),
             sysdate);
commit;

insert into invoicelinetype_desc
          (invoicelinetype_id,
           language_id,
           description,
           systemuser_id,
           timestamp)
          select x.invoicelinetype_id,
                 l.language_id,
                 x.factorydescription,
                 x.systemuser_id,
                 x.timestamp
            from invoicelinetype x, languages l
            where x.invoicelinetype_id = 33;
commit;

---INVOICELINESUBTYPE table---
INSERT INTO INVOICELINESUBTYPE I
      (I.INVOICELINESUBTYPE_ID,
       SUBTYPE,
       INVOICELINETYPE_ID,
       FACTORYDESCRIPTION,
       SYSTEMUSER_ID,
       TIMESTAMP)
      SELECT SEQ_INVOICELINESUBTYPE.NEXTVAL,
             0,
             33,
             'Statement Fee',
             GETMINSYSTEMUSER(),
             SYSDATE
        FROM DUAL;
COMMIT;
        
INSERT INTO INVOICELINESUBTYPE_DESC
      (INVOICELINESUBTYPE_ID,
       LANGUAGE_ID,
       DESCRIPTION,
       SYSTEMUSER_ID,
       TIMESTAMP)
      SELECT I.INVOICELINESUBTYPE_ID,
             L.LANGUAGE_ID,
             I.FACTORYDESCRIPTION,
             GETMINSYSTEMUSER(),
             SYSDATE
        FROM INVOICELINESUBTYPE I, LANGUAGES L
       WHERE L.TRANSLATEDESCRIPTION = 'Y' AND I.SUBTYPE = 0 AND I.INVOICELINETYPE_ID = 33;
COMMIT;


---FORMLAYOUTSTATEMENT table---
CREATE TABLE FORMLAYOUTSTATEMENT
(
  FORMLAYOUT_ID           NUMBER NOT NULL,
  USAGE                   NUMBER DEFAULT 0 NOT NULL CONSTRAINT USAGE_VALID2_1
                          CHECK (USAGE IN (0, 1, 2)),
  CHARGESTATEMENTHANDLING VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD360
                          CHECK (CHARGESTATEMENTHANDLING IN ('Y','N')),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;
 
COMMENT ON COLUMN FORMLAYOUTSTATEMENT.USAGE
  IS '0 = General
1 = Regular Mail Only
2 = E-mail Only
. Only used for FormType = ''ST''.
';

ALTER TABLE FORMLAYOUTSTATEMENT
  ADD CONSTRAINT XPKFORMLAYOUTSTATEMENT PRIMARY KEY (FORMLAYOUT_ID);
  
ALTER TABLE FORMLAYOUTSTATEMENT
  ADD CONSTRAINT FORMLAYOUTSTATEMENT_FL FOREIGN KEY (FORMLAYOUT_ID)
  REFERENCES FORMLAYOUT (FORMLAYOUT_ID) ON DELETE CASCADE;

---PLANTSTMTHANDLINGPRICE table---
CREATE SEQUENCE SEQ_PLANTSTMTTHANDLINGPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLANTSTMTHANDLINGPRICE
(
  PLANTSTMTHANDLINGPRICE_ID      NUMBER NOT NULL,
  PLANT_ID                       NUMBER NOT NULL,
  STARTDATE                      DATE NOT NULL,
  PRICE                          NUMBER NOT NULL,
  SYSTEMUSER_ID                  NUMBER NOT NULL,
  TIMESTAMP                      DATE NOT NULL
)
;

ALTER TABLE PLANTSTMTHANDLINGPRICE
  ADD CONSTRAINT XPKPLANTSTMTHANDLINGPRICE PRIMARY KEY (PLANTSTMTHANDLINGPRICE_ID);
  
CREATE UNIQUE INDEX XAK1PLANTSTMTHANDLINGPRICE ON PLANTSTMTHANDLINGPRICE (PLANT_ID ASC, STARTDATE ASC);

CREATE INDEX XIF1PLANTSTMTHANDLINGPRICE ON PLANTSTMTHANDLINGPRICE (PLANT_ID ASC);

ALTER TABLE PLANTSTMTHANDLINGPRICE
  ADD CONSTRAINT PLANTSTMTHANDLINGPRICE_PL FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

---CUSTOMERSTMTHANDLINGPRICE table---
CREATE SEQUENCE SEQ_CUSTOMERSTMTHANDLINGPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERSTMTHANDLINGPRICE
(
  CUSTOMERSTMTHANDLINGPRICE_ID   NUMBER NOT NULL,
  CUSTOMER_ID                    NUMBER NOT NULL,
  STARTDATE                      DATE NOT NULL,
  PRICE                          NUMBER NOT NULL,
  SYSTEMUSER_ID                  NUMBER NOT NULL,
  TIMESTAMP                      DATE NOT NULL
)
;

ALTER TABLE CUSTOMERSTMTHANDLINGPRICE
  ADD CONSTRAINT XPKCUSTOMERSTMTHANDLINGPRICE PRIMARY KEY (CUSTOMERSTMTHANDLINGPRICE_ID);
  
CREATE UNIQUE INDEX XAK1CUSTOMERSTMTHANDLINGPRICE ON CUSTOMERSTMTHANDLINGPRICE (CUSTOMER_ID ASC, STARTDATE ASC);

CREATE INDEX XIF1CUSTOMERSTMTHANDLINGPRICE ON CUSTOMERSTMTHANDLINGPRICE (CUSTOMER_ID ASC);

ALTER TABLE CUSTOMERSTMTHANDLINGPRICE
  ADD CONSTRAINT CUSTOMERSTMTHANDLINGPRICE_CU FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;


---CUSTOMERBILLINGINFO table---
ALTER TABLE CUSTOMERBILLINGINFO ADD (SPECSTATEMENTHANDLINGPRICE VARCHAR2(1) DEFAULT 'N' NOT NULL
      				     CONSTRAINT YN_VALIDATIONFIELD361 CHECK (SPECSTATEMENTHANDLINGPRICE IN ('Y', 'N')));