--ABS-119907 Service Event Charges for changes in non garment circulating inventory

--CONTRACTPRICELIST
ALTER TABLE CONTRACTPRICELIST
DROP CONSTRAINT CHK_CONTRACTPRICELIST_PRICETYPE;

ALTER TABLE CONTRACTPRICELIST
ADD CONSTRAINT CHK_CONTRACTPRICELIST_PRICETYPE CHECK  (PRICETYPE IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16));

COMMENT ON COLUMN CONTRACTPRICELIST.PRICETYPE IS '0 = Undefined / inherited
                                                  1 = Wash
                                                  2 = Rent
                                                  3 = Replacement
                                                  4 = Sales
                                                  5 = Weight
                                                  6 = Repair
                                                  7 = Emblem
                                                  8 = Emblem Template
                                                  9 = Garment Insurance
                                                  10 = Special Charge
                                                  11 = Wearer Function Special Type 
                                                  12 = Special Size Charge
                                                  13 = Container Rent
                                                  14 = Container Delivery
                                                  15 = Container Return
                                                  16 = Service Event';

--CUSTOMERGENERAL

ALTER TABLE CUSTOMERGENERAL
ADD (CHARGEINCREASEOFCIRCINV VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CUSTOMERGENERAL_CHARGEINC CHECK (CHARGEINCREASEOFCIRCINV IN('Y','N')),
     CHARGEDECREASEOFCIRCINV VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CUSTOMERGENERAL_CHARGEDEC CHECK (CHARGEDECREASEOFCIRCINV IN('Y','N'))
     );


--CUSTOMERSERVICEEVENTPRICE

CREATE SEQUENCE SEQ_CUSTOMERSERVICEEVENTPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERSERVICEEVENTPRICE(CUSTOMERSERVICEEVENTPRICE_ID NUMBER NOT NULL,
                                       CUSTOMER_ID NUMBER NOT NULL,
                                       TYPE NUMBER NOT NULL CONSTRAINT CHK_CUSTOMERSERVICEEVENTPRICE_TYPE CHECK (TYPE IN (1,2)),
                                       PRICE NUMBER(15,6) NOT NULL,
                                       STARTDATE DATE NOT NULL,
                                       TIMESTAMP DATE NOT NULL,
                                       SYSTEMUSER_ID NUMBER NOT NULL);
                                       

COMMENT ON COLUMN CUSTOMERSERVICEEVENTPRICE.TYPE IS '1 = Increase of Circulating Inventory Price
                                                     2 = Decrease of Circulating Inventory Price';

ALTER TABLE CUSTOMERSERVICEEVENTPRICE
ADD CONSTRAINT XPKCUSTOMERSERVICEEVENTPRICE PRIMARY KEY(CUSTOMERSERVICEEVENTPRICE_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERSERVICEEVENTPRICE ON CUSTOMERSERVICEEVENTPRICE
(CUSTOMER_ID ASC, TYPE ASC, STARTDATE ASC);

CREATE INDEX XIF1CUSTOMERSERVICEEVENTPRICE ON CUSTOMERSERVICEEVENTPRICE
(CUSTOMER_ID ASC);

ALTER TABLE CUSTOMERSERVICEEVENTPRICE
ADD CONSTRAINT CUSTOMERSERVICEEVENTPRICE_CUSTOMER FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE;



--GENERALPRICELIST

ALTER TABLE GENERALPRICELIST
DROP CONSTRAINT CHK_GENERALPRICELIST_PRICETYPE;

ALTER TABLE GENERALPRICELIST
ADD CONSTRAINT CHK_GENERALPRICELIST_PRICETYPE CHECK ( PRICETYPE IN (1,2,3,4,5,6,7,8,9,10,11,12, 13, 14, 15,16));

COMMENT ON COLUMN GENERALPRICELIST.PRICETYPE IS '1 = Wash
                                                 2 = Rent
                                                 3 = Replacement
                                                 4 = Sales
                                                 5 = Weight
                                                 6 = Repair
                                                 7 = Emblem
                                                 8 = Emblem Template
                                                 9 = Garment Insurance
                                                 10 = Special Charge
                                                 11 = Wearer Function Special Type
                                                 12 = Special Size Charge
                                                 13 = Container Rent
                                                 14 = Container Delivery
                                                 15 = Container Return
                                                 16 = Service Event';

--GENERALSERVICEEVENTPRICE

CREATE SEQUENCE SEQ_GENERALSERVICEEVENTPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE GENERALSERVICEEVENTPRICE(GENERALSERVICEEVENTPRICE_ID NUMBER NOT NULL,
                                       GENERALPRICELIST_ID NUMBER NOT NULL,
                                       TYPE NUMBER NOT NULL CONSTRAINT CHK_GENERALSERVICEEVENTPRICE_TYPE CHECK (TYPE IN (1,2)),
                                       PRICE NUMBER(15,6) NOT NULL,
                                       STARTDATE DATE NOT NULL,
                                       TIMESTAMP DATE NOT NULL,
                                       SYSTEMUSER_ID NUMBER NOT NULL);
                                       

COMMENT ON COLUMN GENERALSERVICEEVENTPRICE.TYPE IS '1 = Increase of Circulating Inventory Price
                                                     2 = Decrease of Circulating Inventory Price';

ALTER TABLE GENERALSERVICEEVENTPRICE
ADD CONSTRAINT XPKGENERALSERVICEEVENTPRICE PRIMARY KEY(GENERALSERVICEEVENTPRICE_ID);

CREATE UNIQUE INDEX XAK1GENERALSERVICEEVENTPRICE ON GENERALSERVICEEVENTPRICE
(GENERALPRICELIST_ID ASC, TYPE ASC, STARTDATE ASC);

CREATE INDEX XIF1GENERALSERVICEEVENTPRICE ON GENERALSERVICEEVENTPRICE
(GENERALPRICELIST_ID ASC);

ALTER TABLE GENERALSERVICEEVENTPRICE
ADD CONSTRAINT GENERALSERVICEEVENTPRICE_GPL FOREIGN KEY(GENERALPRICELIST_ID) REFERENCES GENERALPRICELIST(GENERALPRICELIST_ID) ON DELETE CASCADE;



--INVOICELINETYPE

ALTER TABLE INVOICELINETYPE
DROP CONSTRAINT CHK_INVLINETYPE_TYPE;

ALTER TABLE INVOICELINETYPE
ADD CONSTRAINT CHK_INVLINETYPE_TYPE CHECK ( TYPE BETWEEN 1 AND 37);

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
                                           34 = Custom Garment Charge
                                           35 = Unique Item Service 
                                           36 = Container Rent
                                           37 = Service Event Charge';
--PRICELISTPERCUSTOMER

ALTER TABLE PRICELISTPERCUSTOMER
DROP CONSTRAINT CHK_PRICELISTPERCUSTOMER_PRICETYPE;

ALTER TABLE PRICELISTPERCUSTOMER
ADD CONSTRAINT CHK_PRICELISTPERCUSTOMER_PRICETYPE CHECK (PRICETYPE IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16));


COMMENT ON COLUMN PRICELISTPERCUSTOMER.PRICETYPE IS '1 = Wash
                                                     2 = Rent
                                                     3 = Replacement
                                                     4 = Sales
                                                     5 = Weight
                                                     6 = Repair
                                                     7 = Emblem
                                                     8 = Emblem Template
                                                     9 = Garment Insurance
                                                     10 = Special Charge
                                                     13 = Container Rent
                                                     14 = Container Delivery
                                                     15 = Container Return
                                                     16 = Service Event';
                                                     


--SERVICEEVENTCHARGE
CREATE SEQUENCE SEQ_SERVICEEVENTCHARGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SERVICEEVENTCHARGE(SERVICEEVENTCHARGE_ID NUMBER NOT NULL,
                                       INVOICELINE_ID NUMBER NULL,
                                       STATUS NUMBER NOT NULL CONSTRAINT CHK_SERVICEEVENTCHARGE_STATUS CHECK (STATUS IN (0,1,2,3)),
                                       SERVICEEVENTCHARGE_TYPE NUMBER NOT NULL,
                                       SYSTEMUSER_ID NUMBER NOT NULL,
                                       TIMESTAMP DATE NOT NULL);
                                       
                                       
COMMENT ON COLUMN SERVICEEVENTCHARGE.STATUS IS '0 = To be charged
                                                1 = Charged
                                                2 = Free of charge
                                                3 = Cancelled';
COMMENT ON COLUMN SERVICEEVENTCHARGE.SERVICEEVENTCHARGE_TYPE IS '1 = Rental Quantity Adjustment';

CREATE INDEX XIF1SERVICEEVENTCHARGE ON SERVICEEVENTCHARGE
(INVOICELINE_ID ASC);

ALTER TABLE SERVICEEVENTCHARGE
ADD CONSTRAINT XPKSERVICEEVENTCHARGE PRIMARY KEY(SERVICEEVENTCHARGE_ID);

ALTER TABLE SERVICEEVENTCHARGE
ADD CONSTRAINT SERVICEEVENTCHARGE_IL FOREIGN KEY(INVOICELINE_ID) REFERENCES INVOICELINE(INVOICELINE_ID);

--SECRENTALQTYADJUSTMENT
CREATE TABLE SECRENTALQTYADJUSTMENT(SERVICEEVENTCHARGE_ID NUMBER NOT NULL,
                                    RENTALQTYADJUSTMENT_ID NUMBER NOT NULL,
                                    SYSTEMUSER_ID NUMBER NOT NULL,
                                    TIMESTAMP DATE NOT NULL);


ALTER TABLE SECRENTALQTYADJUSTMENT
ADD CONSTRAINT XPKSECRENTALQTYADJUSTMENT PRIMARY KEY(SERVICEEVENTCHARGE_ID);

CREATE INDEX XIF1SECRENTALQTYADJUSTMENT ON SECRENTALQTYADJUSTMENT
(RENTALQTYADJUSTMENT_ID ASC);

ALTER TABLE SECRENTALQTYADJUSTMENT
ADD CONSTRAINT SECRENTALQTYADJUSTMENT_SEC FOREIGN KEY(SERVICEEVENTCHARGE_ID) REFERENCES SERVICEEVENTCHARGE(SERVICEEVENTCHARGE_ID) ON DELETE CASCADE;

ALTER TABLE SECRENTALQTYADJUSTMENT
ADD CONSTRAINT SECRENTALQTYADJUSTMENT_RQA FOREIGN KEY(RENTALQTYADJUSTMENT_ID) REFERENCES RENTALQTYADJUSTMENT(RENTALQTYADJUSTMENT_ID) ON DELETE CASCADE;


