--- ABS-117907 / ABS-122897

-------------- CONTAINERBILLPERCUSTOMER ------------

ALTER TABLE CONTAINERBILLPERCUSTOMER 
      ADD (CHARGERENT VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTAINERBILLPERCUST_CHRENT CHECK (CHARGERENT IN ('Y','N')),
      CHARGEDELIVERY VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTAINERBILLPERCUST_CHDEL CHECK (CHARGEDELIVERY IN ('Y','N')),
      CREDITRETURN VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTAINERBILLPERCUST_CRETURN CHECK (CREDITRETURN IN ('Y','N')));

UPDATE CONTAINERBILLPERCUSTOMER C
       SET C.CHARGERENT = 'Y';
COMMIT;

----------------- 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));
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';


------------- CUSTOMERCONTAINERDELPRICE -------------
CREATE SEQUENCE SEQ_CUSTOMERCONTAINERDELPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERCONTAINERDELPRICE
(
  CUSTOMERCONTAINERDELPRICE_ID NUMBER NOT NULL,
  CONTAINERBILLPERCUSTOMER_ID  NUMBER NOT NULL,
  PRICE                        NUMBER(15,6) NOT NULL,
  STARTDATE                    DATE NOT NULL,
  TIMESTAMP                    DATE NOT NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL
);

ALTER TABLE CUSTOMERCONTAINERDELPRICE 
      ADD CONSTRAINT XPKCUSTCONTAINERDELPRICE PRIMARY KEY (CUSTOMERCONTAINERDELPRICE_ID);
      
CREATE UNIQUE INDEX XAK1CUSTOMERCONTAINERDELPRICE ON CUSTOMERCONTAINERDELPRICE (CONTAINERBILLPERCUSTOMER_ID ASC, STARTDATE ASC);
CREATE INDEX XIF1CUSTOMERCONTAINERDELPRICE ON CUSTOMERCONTAINERDELPRICE (CONTAINERBILLPERCUSTOMER_ID ASC);

ALTER TABLE CUSTOMERCONTAINERDELPRICE 
      ADD CONSTRAINT FK_CUSTCONTDELPRICE_CONTBILLCUST FOREIGN KEY (CONTAINERBILLPERCUSTOMER_ID)
      REFERENCES CONTAINERBILLPERCUSTOMER (CONTAINERBILLPERCUSTOMER_ID) ON DELETE CASCADE;


------------- CUSTOMERCONTAINERRETCREDIT -------------
CREATE SEQUENCE SEQ_CUSTOMERCONTAINERRETCREDIT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERCONTAINERRETCREDIT
(
  CUSTOMERCONTAINERRETCREDIT_ID   NUMBER NOT NULL,
  CONTAINERBILLPERCUSTOMER_ID     NUMBER NOT NULL,
  PRICE                           NUMBER(15,6) NOT NULL,
  STARTDATE                       DATE NOT NULL,
  TIMESTAMP                       DATE NOT NULL,
  SYSTEMUSER_ID                   NUMBER NOT NULL
);

ALTER TABLE CUSTOMERCONTAINERRETCREDIT 
      ADD CONSTRAINT XPKCUSTCONTAINERRETCR PRIMARY KEY (CUSTOMERCONTAINERRETCREDIT_ID);
      
CREATE UNIQUE INDEX XAK1CUSTOMERCONTAINERRETCREDIT ON CUSTOMERCONTAINERRETCREDIT (CONTAINERBILLPERCUSTOMER_ID ASC, STARTDATE ASC);
CREATE INDEX XIF1CUSTOMERCONTAINERRETCREDIT ON CUSTOMERCONTAINERRETCREDIT (CONTAINERBILLPERCUSTOMER_ID ASC);

ALTER TABLE CUSTOMERCONTAINERRETCREDIT 
      ADD CONSTRAINT FK_CUSTCONTRETCR_CONTBILLCUST FOREIGN KEY (CONTAINERBILLPERCUSTOMER_ID)
      REFERENCES CONTAINERBILLPERCUSTOMER (CONTAINERBILLPERCUSTOMER_ID) ON DELETE CASCADE;

------------- GENERALCONTAINERDELPRICE -------------
CREATE SEQUENCE SEQ_GENERALCONTAINERDELPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE GENERALCONTAINERDELPRICE
(
  GENERALCONTAINERDELPRICE_ID   NUMBER NOT NULL,
  GENERALPRICELIST_ID           NUMBER NOT NULL,
  CONTAINERTYPE_ID              NUMBER NOT NULL,
  PRICE                         NUMBER(15,6) NOT NULL,
  STARTDATE                     DATE NOT NULL,
  TIMESTAMP                     DATE NOT NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL
);

ALTER TABLE GENERALCONTAINERDELPRICE 
      ADD CONSTRAINT XPKGENERALCONTDELPRICE PRIMARY KEY (GENERALCONTAINERDELPRICE_ID);
      
CREATE UNIQUE INDEX XAK1GENERALCONTAINERDELPRICE ON GENERALCONTAINERDELPRICE (GENERALPRICELIST_ID ASC, CONTAINERTYPE_ID ASC, STARTDATE ASC);
CREATE INDEX XIF1GENERALCONTAINERDELPRICE ON GENERALCONTAINERDELPRICE (GENERALPRICELIST_ID ASC);
CREATE INDEX XIF2GENERALCONTAINERDELPRICE ON GENERALCONTAINERDELPRICE (CONTAINERTYPE_ID ASC);
     
ALTER TABLE GENERALCONTAINERDELPRICE 
      ADD CONSTRAINT FK_GENCONTDELPRICE_GENPRLIST FOREIGN KEY (GENERALPRICELIST_ID)
      REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID) ON DELETE CASCADE;
ALTER TABLE GENERALCONTAINERDELPRICE 
      ADD CONSTRAINT FK_GENCONTDELPRICE_CONTYPE FOREIGN KEY (CONTAINERTYPE_ID)
      REFERENCES CONTAINERTYPE (CONTAINERTYPE_ID) ON DELETE CASCADE;

      
   
------------- GENERALCONTAINERRETCREDIT -------------
CREATE SEQUENCE SEQ_GENERALCONTAINERRETCREDIT
START WITH 1
INCREMENT BY 1;

CREATE TABLE GENERALCONTAINERRETCREDIT
(
  GENERALCONTAINERRETCREDIT_ID   NUMBER NOT NULL,
  GENERALPRICELIST_ID            NUMBER NOT NULL,
  CONTAINERTYPE_ID               NUMBER NOT NULL,
  PRICE                          NUMBER(15,6) NOT NULL,
  STARTDATE                      DATE NOT NULL,
  TIMESTAMP                      DATE NOT NULL,
  SYSTEMUSER_ID                  NUMBER NOT NULL
);

ALTER TABLE GENERALCONTAINERRETCREDIT 
      ADD CONSTRAINT XPKGENERALCONTRETCREDIT PRIMARY KEY (GENERALCONTAINERRETCREDIT_ID);
      
CREATE UNIQUE INDEX XAK1GENERALCONTAINERRETCREDIT ON GENERALCONTAINERRETCREDIT (GENERALPRICELIST_ID ASC, CONTAINERTYPE_ID ASC, STARTDATE ASC);
CREATE INDEX XIF1GENERALCONTAINERRETCREDIT ON GENERALCONTAINERRETCREDIT (GENERALPRICELIST_ID ASC);
CREATE INDEX XIF2GENERALCONTAINERRETCREDIT ON GENERALCONTAINERRETCREDIT (CONTAINERTYPE_ID ASC);
      
ALTER TABLE GENERALCONTAINERRETCREDIT 
      ADD CONSTRAINT FK_GENCONTRETCREDIT_GENPRLIST FOREIGN KEY (GENERALPRICELIST_ID)
      REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID) ON DELETE CASCADE;
ALTER TABLE GENERALCONTAINERRETCREDIT 
      ADD CONSTRAINT FK_GENCONTRETCREDIT_CONTYPE FOREIGN KEY (CONTAINERTYPE_ID)
      REFERENCES CONTAINERTYPE (CONTAINERTYPE_ID) ON DELETE CASCADE;
      
      
-----------------  GENERALPRICELIST  ----------------

ALTER TABLE GENERALPRICELIST DROP CONSTRAINT PRICETYPE_VALID4;
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));
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';


-----------------  PRICELISTPERCUSTOMER   ----------------

ALTER TABLE PRICELISTPERCUSTOMER
  ADD CONSTRAINT CHK_PRICELISTPERCUSTOMER_PRICETYPE CHECK (PRICETYPE IN (1,2,3,4,5,6,7,8,9,10,11,12, 13, 14, 15));
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 
      11 = Wearer Function Special Type 
      12 = Special Size Charge 
      13 = Container Rent 
      14 = Container Delivery 
      15 = Container Return';
      
