-- ABS-100318/ ABS-109838

---------- PLANTPGSETTINGSBM6 TABLE ----------------
CREATE SEQUENCE SEQ_PLANTPGSETTINGSBM6
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLANTPGSETTINGSBM6
(
  PLANTPGSETTINGSBM6_ID NUMBER NOT NULL,
  PLANT_ID              NUMBER NOT NULL,
  PRODUCTGROUP_ID       NUMBER NOT NULL,
  ONEOFFPRICEMULTIPLIER NUMBER(5,2) NULL CONSTRAINT CHK_PLANTPGSETTBM6_ONEOFFPRICE
                        CHECK (ONEOFFPRICEMULTIPLIER > 0),
  CHANGEDONEOFFPRICEMULTIPLIER VARCHAR2(1) DEFAULT 'N' NOT NULL
                        CONSTRAINT CHK_PLANTPGSETTBM6_CHGONEOFFPRICE CHECK (CHANGEDONEOFFPRICEMULTIPLIER IN ('Y', 'N')),
  INVENTORYMAINTPERC    NUMBER(5,2) NULL,
  CHANGEDINVENTORYMAINTPERC VARCHAR2(1) DEFAULT 'N' NOT NULL
                        CONSTRAINT CHK_PLANTPGSETTBM6_CHGINVMAINTPER CHECK (CHANGEDINVENTORYMAINTPERC IN ('Y', 'N')),
  SYSTEMUSER_ID         NUMBER NOT NULL,
  TIMESTAMP             DATE NOT NULL
)
;

ALTER TABLE PLANTPGSETTINGSBM6
  ADD CONSTRAINT XPKPLANTPGSETTINGSBM6 PRIMARY KEY (PLANTPGSETTINGSBM6_ID);
  
CREATE INDEX XIF1PLANTPGSETTINGSBM6 ON PLANTPGSETTINGSBM6 (PLANT_ID ASC);
CREATE INDEX XIF2PLANTPGSETTINGSBM6 ON PLANTPGSETTINGSBM6 (PRODUCTGROUP_ID ASC);

ALTER TABLE PLANTPGSETTINGSBM6
  ADD CONSTRAINT FK_PPGBM6_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;
  
ALTER TABLE PLANTPGSETTINGSBM6
  ADD CONSTRAINT FK_PPGBM6_PRODUCTG FOREIGN KEY (PRODUCTGROUP_ID)
  REFERENCES PRODUCTGROUP (PRODUCTGROUP_ID) ON DELETE CASCADE;

----------- PLANTPGSETTINGSBM6DELDAYS TABLE ----------------
CREATE TABLE PLANTPGSETTINGSBM6DELDAYS
(
  PLANTPGSETTINGSBM6_ID    NUMBER NOT NULL,
  DELIVERIESPERWEEK        NUMBER NOT NULL
                           CONSTRAINT CHK_PLPGSETTBM6DELDAYS_DELPERWEEK CHECK (DELIVERIESPERWEEK BETWEEN 1 AND 7),
  CIRCINVENTORYMULTIPLIER  NUMBER(5,2) NULL
                           CONSTRAINT CHK_PLPGSETTBM6DELDAYS_CIRCINVM CHECK (CIRCINVENTORYMULTIPLIER > 0),
  CHANGEDCIRCINVMULTIPLIER VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PLPGSETTBM6DELDAYS_CHANGEDCIRC CHECK (CHANGEDCIRCINVMULTIPLIER IN ('Y','N')),
  LOADPERCENTPERDAY        NUMBER(5,2) NULL
                           CONSTRAINT CHK_PLPGSETTBM6DELDAYS_LOADPERC CHECK (LOADPERCENTPERDAY > 0),
  CHANGEDLOADPERCPERDAY    VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PLPGSETTBM6DELDAYS_CHANGEDLOAD CHECK (CHANGEDLOADPERCPERDAY IN ('Y','N')),
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

ALTER TABLE PLANTPGSETTINGSBM6DELDAYS
  ADD CONSTRAINT XPKPLANTPGSETTINGSBM6DELDAYS PRIMARY KEY (PLANTPGSETTINGSBM6_ID, DELIVERIESPERWEEK);

CREATE INDEX XIF1PLANTPGSETTINGSBM6DELDAYS ON PLANTPGSETTINGSBM6DELDAYS(PLANTPGSETTINGSBM6_ID ASC);

ALTER TABLE PLANTPGSETTINGSBM6DELDAYS
  ADD CONSTRAINT FK_PLPGSETTBM6DELDAYS_PLANT FOREIGN KEY (PLANTPGSETTINGSBM6_ID)
  REFERENCES PLANTPGSETTINGSBM6 (PLANTPGSETTINGSBM6_ID) ON DELETE CASCADE;


---------- PLANTPRODSETTINGSBM6 TABLE ----------------
CREATE SEQUENCE SEQ_PLANTPRODSETTINGSBM6
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLANTPRODSETTINGSBM6
(
  PLANTPRODSETTINGSBM6_ID  NUMBER NOT NULL,
  PLANT_ID                 NUMBER NOT NULL,
  PRODUCT_ID               NUMBER NOT NULL,
  ONEOFFPRICEMULTIPLIER    NUMBER(5,2) NULL CONSTRAINT CHK_PLANTPSETTBM6_ONEOFFPRICE
                           CHECK (ONEOFFPRICEMULTIPLIER > 0),
  CHANGEDONEOFFPRICEMULTIPLIER VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PLANTPSETTBM6_CHGONEOFFPRICE CHECK (CHANGEDONEOFFPRICEMULTIPLIER IN ('Y', 'N')),
  INVENTORYMAINTPERC       NUMBER(5,2) NULL,
  CHANGEDINVENTORYMAINTPERC VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PLANTPSETTBM6_CHGINVMAINTPER CHECK (CHANGEDINVENTORYMAINTPERC IN ('Y', 'N')),
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

ALTER TABLE PLANTPRODSETTINGSBM6
  ADD CONSTRAINT XPKPLANTPRODSETTINGSBM6 PRIMARY KEY (PLANTPRODSETTINGSBM6_ID);
  
CREATE INDEX XIF1PLANTPRODSETTINGSBM6 ON PLANTPRODSETTINGSBM6 (PLANT_ID ASC);
CREATE INDEX XIF2PLANTPRODSETTINGSBM6 ON PLANTPRODSETTINGSBM6 (PRODUCT_ID ASC);
  
ALTER TABLE PLANTPRODSETTINGSBM6
  ADD CONSTRAINT FK_PPSBM6_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;
  
ALTER TABLE PLANTPRODSETTINGSBM6
  ADD CONSTRAINT FK_PPSBM6_PRODUCT FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;

----------- PLANTPRODSETTINGSBM6DELDAYS TABLE ----------------
CREATE TABLE PLANTPRODSETTINGSBM6DELDAYS
(
  PLANTPRODSETTINGSBM6_ID  NUMBER NOT NULL,
  DELIVERIESPERWEEK        NUMBER NOT NULL
                           CONSTRAINT CHK_PLPSETTBM6DELDAYS_DELPERWEEK CHECK (DELIVERIESPERWEEK BETWEEN 1 AND 7),
  CIRCINVENTORYMULTIPLIER  NUMBER(5,2) NULL
                           CONSTRAINT CHK_PLPSETTBM6DELDAYS_CIRCINVM CHECK (CIRCINVENTORYMULTIPLIER > 0),
  CHANGEDCIRCINVMULTIPLIER VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PLPSETTBM6DELDAYS_CHANGEDCIRC CHECK (CHANGEDCIRCINVMULTIPLIER IN ('Y','N')),
  LOADPERCENTPERDAY        NUMBER(5,2) NULL
                           CONSTRAINT CHK_PLPSETTBM6DELDAYS_LOADPERC CHECK (LOADPERCENTPERDAY > 0),
  CHANGEDLOADPERCPERDAY    VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PLPSETTBM6DELDAYS_CHANGEDLOAD CHECK (CHANGEDLOADPERCPERDAY IN ('Y','N')),
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

ALTER TABLE PLANTPRODSETTINGSBM6DELDAYS
  ADD CONSTRAINT XPKPLANTPRODSETTINGSBM6DELDAYS PRIMARY KEY (PLANTPRODSETTINGSBM6_ID, DELIVERIESPERWEEK);

CREATE INDEX XIF1PLANTPRODSETTINGSBM6DELDAYS ON PLANTPRODSETTINGSBM6DELDAYS(PLANTPRODSETTINGSBM6_ID ASC);

ALTER TABLE PLANTPRODSETTINGSBM6DELDAYS
  ADD CONSTRAINT FK_PLPSETTBM6DELDAYS_PLANT FOREIGN KEY (PLANTPRODSETTINGSBM6_ID)
  REFERENCES PLANTPRODSETTINGSBM6 (PLANTPRODSETTINGSBM6_ID) ON DELETE CASCADE;

------- PRODUCTGROUPBM6 TABLE ---------
CREATE TABLE PRODUCTGROUPBM6
(
  PRODUCTGROUP_ID       NUMBER NOT NULL,
  ONEOFFPRICEMULTIPLIER NUMBER(5,2) DEFAULT 1 NULL,
  CHANGEDONEOFFPRICEMULTIPLIER VARCHAR2(1) DEFAULT 'N' NOT NULL
                        CONSTRAINT CHK_PGBM6_CHANGEDONEOFF CHECK (CHANGEDONEOFFPRICEMULTIPLIER IN ('Y','N')),
  INVENTORYMAINTPERC    NUMBER(5,2) DEFAULT 0 NULL,
  CHANGEDINVENTORYMAINTPERC VARCHAR2(1) DEFAULT 'N' NOT NULL
                        CONSTRAINT CHK_PGBM6_CHGINVMAINTPER CHECK (CHANGEDINVENTORYMAINTPERC IN ('Y', 'N')),
  SYSTEMUSER_ID         NUMBER NOT NULL,
  TIMESTAMP             DATE NOT NULL
)
;

ALTER TABLE PRODUCTGROUPBM6
  ADD CONSTRAINT XPKPRODUCTGROUPBM6 PRIMARY KEY (PRODUCTGROUP_ID);
  
ALTER TABLE PRODUCTGROUPBM6
  ADD CONSTRAINT FK_PRODUCTGROUPBM6_PG FOREIGN KEY (PRODUCTGROUP_ID)
  REFERENCES PRODUCTGROUP (PRODUCTGROUP_ID) ON DELETE CASCADE;


-------- PRODUCTGROUPBM6DELDAYS TABLE -----------
CREATE TABLE PRODUCTGROUPBM6DELDAYS
(
  PRODUCTGROUP_ID         NUMBER NOT NULL,
  DELIVERIESPERWEEK       NUMBER NOT NULL
                          CONSTRAINT CHK_PGBM6DELDAYS_DELPERWEEK CHECK (DELIVERIESPERWEEK BETWEEN 1 AND 7),
  CIRCINVENTORYMULTIPLIER NUMBER(5,2) NULL
                          CONSTRAINT CHK_PGBM6DELDAYS_CIRCINV CHECK (CIRCINVENTORYMULTIPLIER > 0),
  CHANGEDCIRCINVMULTIPLIER VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PGBM6DELDAYS_CHANGEDCIRC CHECK (CHANGEDCIRCINVMULTIPLIER IN ('Y','N')),
  LOADPERCENTPERDAY       NUMBER(5,2) NULL
                          CONSTRAINT CHK_PGBM6DELDAYS_LOADPERC CHECK (LOADPERCENTPERDAY > 0),
  CHANGEDLOADPERCPERDAY   VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PGBM6DELDAYS_CHANGEDLOAD CHECK (CHANGEDLOADPERCPERDAY IN ('Y','N')),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;

ALTER TABLE PRODUCTGROUPBM6DELDAYS
  ADD CONSTRAINT XPKPRODUCTGROUPBM6DELDAYS PRIMARY KEY (PRODUCTGROUP_ID, DELIVERIESPERWEEK);

CREATE INDEX XIF1PRODUCTGROUPBM6DELDAYS ON PRODUCTGROUPBM6DELDAYS (PRODUCTGROUP_ID ASC);

ALTER TABLE PRODUCTGROUPBM6DELDAYS
  ADD CONSTRAINT FK_PGBM6DELDAYS_PG FOREIGN KEY (PRODUCTGROUP_ID)
  REFERENCES PRODUCTGROUPBM6 (PRODUCTGROUP_ID) ON DELETE CASCADE;


----------PRODUCTBM6 TABLE ---------------
CREATE TABLE PRODUCTBM6
(
  PRODUCT_ID            NUMBER NOT NULL,
  ONEOFFPRICEMULTIPLIER NUMBER(5,2) NULL
                        CONSTRAINT CHK_PRODUCTBM6_ONEOFFPRICE CHECK (ONEOFFPRICEMULTIPLIER > 0),
  CHANGEDONEOFFPRICEMULTIPLIER VARCHAR2(1) DEFAULT 'N' NOT NULL
                        CONSTRAINT CHK_PRODUCTBM6_CHGONEOFFPRICE CHECK (CHANGEDONEOFFPRICEMULTIPLIER IN ('Y', 'N')),
  INVENTORYMAINTPERC    NUMBER(5,2) NULL,
  CHANGEDINVENTORYMAINTPERC VARCHAR2(1) DEFAULT 'N' NOT NULL
                        CONSTRAINT CHK_PRODUCTBM6_CHGINVMAINTPER CHECK (CHANGEDINVENTORYMAINTPERC IN ('Y', 'N')),
  SYSTEMUSER_ID         NUMBER NOT NULL,
  TIMESTAMP             DATE NOT NULL
)
;

ALTER TABLE PRODUCTBM6
  ADD CONSTRAINT XPKPRODUCTBM6 PRIMARY KEY (PRODUCT_ID);

ALTER TABLE PRODUCTBM6
  ADD CONSTRAINT FK_PRODUCTBM6_PRODUCT FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;

----------PRODUCTBM6DELDAYS TABLE ---------------
CREATE TABLE PRODUCTBM6DELDAYS
(
  PRODUCT_ID              NUMBER NOT NULL,
  DELIVERIESPERWEEK       NUMBER NOT NULL
                          CONSTRAINT CHK_PBM6DELDAYS_DELPERWEEK CHECK (DELIVERIESPERWEEK BETWEEN 1 AND 7),
  CIRCINVENTORYMULTIPLIER NUMBER(5,2) NULL
                          CONSTRAINT CHK_PBM6DELDAYS_CIRCINV CHECK (CIRCINVENTORYMULTIPLIER > 0),
  CHANGEDCIRCINVMULTIPLIER VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PBM6DELDAYS_CHANGEDCIRC CHECK (CHANGEDCIRCINVMULTIPLIER IN ('Y','N')),
  LOADPERCENTPERDAY       NUMBER(5,2) NULL
                          CONSTRAINT CHK_PBM6DELDAYS_LOADPERC CHECK (LOADPERCENTPERDAY > 0),
  CHANGEDLOADPERCPERDAY   VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_PBM6DELDAYS_CHANGEDLOAD CHECK (CHANGEDLOADPERCPERDAY IN ('Y','N')),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;

ALTER TABLE PRODUCTBM6DELDAYS
  ADD CONSTRAINT XPKPRODUCTBM6DELDAYS PRIMARY KEY (PRODUCT_ID, DELIVERIESPERWEEK);

CREATE INDEX XIF1PRODUCTBM6DELDAYS ON PRODUCTBM6DELDAYS (PRODUCT_ID ASC);
  
ALTER TABLE PRODUCTBM6DELDAYS
  ADD CONSTRAINT FK_PBM6DELDAYS_PROD FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCTBM6 (PRODUCT_ID) ON DELETE CASCADE;

----------PLPCBM6 TABLE ---------------
CREATE TABLE PLPCBM6
(
  PRODUCTLISTPERCUSTOMER_ID  NUMBER NOT NULL,
  ONEOFFPRICEMULTIPLIER      NUMBER(5,2) NULL
                             CONSTRAINT CHK_PLPCBM6_ONEOFFPRICE CHECK (ONEOFFPRICEMULTIPLIER > 0),
  WEEKLYUSAGE                NUMBER DEFAULT 0 NOT NULL,
  INVENTORYMAINTPERC         NUMBER(5,2) NULL,
  VARIABLEDELIVERYQUANTITY   VARCHAR2(1) DEFAULT 'N' NOT NULL
			     CONSTRAINT CHK_PLPCBM6_VARDELQTY CHECK (VARIABLEDELIVERYQUANTITY IN ('Y', 'N')),
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
)
;

ALTER TABLE PLPCBM6
  ADD CONSTRAINT XPKPLPCBM6 PRIMARY KEY (PRODUCTLISTPERCUSTOMER_ID);
  
ALTER TABLE PLPCBM6
  ADD CONSTRAINT FK_PLPCBM6_PRODUCT FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
  REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;


----------PLPCBM6DELDAYS TABLE ---------------
CREATE TABLE PLPCBM6DELDAYS
(
  PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
  DELIVERIESPERWEEK         NUMBER NOT NULL
                            CONSTRAINT CHK_PLPCBM6DELDAYS_DELPERWEEK CHECK (DELIVERIESPERWEEK BETWEEN 1 AND 7),
  CIRCINVENTORYMULTIPLIER   NUMBER(5,2) NULL
                            CONSTRAINT CHK_PLPCBM6DELDAYS_CIRCINV CHECK (CIRCINVENTORYMULTIPLIER > 0),
  LOADPERCENTPERDAY         NUMBER(5,2) NULL
                            CONSTRAINT CHK_PLPCBM6DELDAYS_LOADPERC CHECK (LOADPERCENTPERDAY > 0),
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
)
;

ALTER TABLE PLPCBM6DELDAYS
  ADD CONSTRAINT XPKPLPCBM6DELDAYS PRIMARY KEY (PRODUCTLISTPERCUSTOMER_ID, DELIVERIESPERWEEK);

CREATE INDEX XIF1PLPCBM6DELDAYS ON PLPCBM6DELDAYS (PRODUCTLISTPERCUSTOMER_ID ASC);

ALTER TABLE PLPCBM6DELDAYS
  ADD CONSTRAINT FK_PLPCBM6DELDAYS_PROD FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
  REFERENCES PLPCBM6 (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;

----------- PLPCBM6HISTORY TABLE ---------------
CREATE SEQUENCE SEQ_PLPCBM6HISTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLPCBM6HISTORY
(
  PLPCBM6HISTORY_ID           NUMBER NOT NULL,
  PRODUCTLISTPERCUSTOMER_ID   NUMBER NOT NULL,
  PREVWEEKLYUSAGE             NUMBER DEFAULT 0 NOT NULL,
  NEWWEEKLYUSAGE              NUMBER DEFAULT 0 NOT NULL,
  PREVCIRCINVENTORYMULTIPLIER NUMBER(5,2) NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_PREVCIRCINVM CHECK (PREVCIRCINVENTORYMULTIPLIER > 0),
  NEWCIRCINVENTORYMULTIPLIER  NUMBER(5,2) NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_NEWCIRCINVM CHECK (NEWCIRCINVENTORYMULTIPLIER > 0),
  PREVCIRCINVENTORY           NUMBER NULL,
  NEWCIRCINVENTORY            NUMBER NULL,
  PREVDELIVERIESPERWEEK       NUMBER NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_PREVDELPWEEK CHECK (PREVDELIVERIESPERWEEK BETWEEN 1 AND 7),
  NEWDELIVERIESPERWEEK        NUMBER NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_NEWDELPWEEK CHECK (NEWDELIVERIESPERWEEK BETWEEN 1 AND 7),
  PREVLOADPERCENTPERDAY       NUMBER(5,2) NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_PREVLOAD CHECK (PREVLOADPERCENTPERDAY > 0),
  NEWLOADPERCENTPERDAY        NUMBER(5,2) NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_NEWLOAD CHECK (NEWLOADPERCENTPERDAY > 0),
  PREVDELIVERYQTY             NUMBER NULL,
  NEWDELIVERYQTY              NUMBER NULL,
  PREVBILLINGMULTIPLIER       NUMBER(5,2) NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_PREVBILLM CHECK (PREVBILLINGMULTIPLIER > 0),
  NEWBILLINGMULTIPLIER        NUMBER(5,2) NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_NEWBILLM CHECK (NEWBILLINGMULTIPLIER > 0),
  PREVBILLINGQTY              NUMBER NULL,
  NEWBILLINGQTY               NUMBER NULL,
  PREVONEOFFPRICEMULTIPLIER   NUMBER(5,2) NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_PREVONEOFFP CHECK (PREVONEOFFPRICEMULTIPLIER > 0),
  NEWONEOFFPRICEMULTIPLIER    NUMBER(5,2) NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_NEWONEOFFP CHECK (NEWONEOFFPRICEMULTIPLIER > 0),
  PREVINVENTORYMAINTPERC      NUMBER(5,2) NULL,
  NEWINVENTORYMAINTPERC       NUMBER(5,2) NULL,
  CHANGECAUSE                 NUMBER NOT NULL
                              CONSTRAINT CHK_PLPCBM6HISTORY_CHANGEC CHECK (CHANGECAUSE IN (0, 1, 2, 3, 4, 5, 6, 7)),
  DATEOFCHANGE                DATE NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL
)
;

COMMENT ON COLUMN PLPCBM6HISTORY.PREVBILLINGMULTIPLIER
  IS 'Billing multiplier is the CircInventoryMultiplier taken for once per week deliveries. ';
COMMENT ON COLUMN PLPCBM6HISTORY.NEWBILLINGMULTIPLIER
  IS 'Billing multiplier is the CircInventoryMultiplier taken for once per week deliveries. ';
COMMENT ON COLUMN PLPCBM6HISTORY.CHANGECAUSE
  IS '0 = No change 
1 = Weekly Usage change
2 = PLPC factor change
3 = Plant Product factor change
4 = Plant Product Group factor change
5 = Product factor change
6 = Product Group factor change
7  = Deliveries Per Week change
';

ALTER TABLE PLPCBM6HISTORY
  ADD CONSTRAINT XPKPLPCBM6HISTORY PRIMARY KEY (PLPCBM6HISTORY_ID);

CREATE INDEX XIF1PLPCBM6HISTORY ON PLPCBM6HISTORY (PRODUCTLISTPERCUSTOMER_ID ASC);

ALTER TABLE PLPCBM6HISTORY
  ADD CONSTRAINT FK_PLPCBM6HISTORY_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
  REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;

--------- PLPCBM6RECALCULATION TABLE -----------------
CREATE TABLE PLPCBM6RECALCULATION
(
  PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
  PROCESSED                 VARCHAR2(1) DEFAULT 'N' NOT NULL
                            CONSTRAINT CHK_PLPCBM6RECALC_PROCESSED CHECK (PROCESSED IN ('Y', 'N')),
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
)
;

ALTER TABLE PLPCBM6RECALCULATION
  ADD CONSTRAINT XPKPLPCBM6RECALCULATION PRIMARY KEY (PRODUCTLISTPERCUSTOMER_ID);
  
ALTER TABLE PLPCBM6RECALCULATION
  ADD CONSTRAINT FK_PLPCBM6RECALC_PLPCBM6 FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
  REFERENCES PLPCBM6 (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;

---------- USERGROUP table --------------
ALTER TABLE USERGROUP ADD (BILLINGMETHOD6ADVANCED VARCHAR2(1) DEFAULT 'N' NOT NULL
                           CONSTRAINT CHK_USERGROUP_BM6ADV CHECK (BILLINGMETHOD6ADVANCED IN ('Y', 'N')));

COMMENT ON COLUMN USERGROUP.BILLINGMETHOD6ADVANCED
  IS 'only used for module BILLINGMETHOD6';

---------- PRODUCTLISTPERCUSTOMER table --------------
COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.BILLINGMETHOD
  IS '1 = Flat rated rent
2 = Rent based on minimum
3 = Circulating inventory
4 = Piece rate only
5 = Lease
6 = Rent based on Usage';

ALTER TABLE PRODUCTLISTPERCUSTOMER
  DROP CONSTRAINT BILLINGMETHOD_VALIDATION2;

ALTER TABLE PRODUCTLISTPERCUSTOMER
  ADD CONSTRAINT CHK_PLPC_BILLINGMETHOD
  CHECK (BILLINGMETHOD IN (1, 2, 3, 4, 5, 6));
