-- ABS-116534/ ABS-117803

--- CUSTOMERRENTPRICE table --- 
ALTER TABLE CUSTOMERRENTPRICE ADD (MAXINVENTORY NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN CUSTOMERRENTPRICE.MAXINVENTORY
  IS 'Only used for TypeOfRent = 11. For all other must be filled with 0. For TypeOfRent = 11 must be >= 0.';

DROP INDEX XAK1CUSTOMERRENTPRICE;
CREATE UNIQUE INDEX XAK1CUSTOMERRENTPRICE ON CUSTOMERRENTPRICE (PRODUCTLISTPERCUSTOMER_ID ASC, STARTDATE ASC, CHANGESPERWEEK ASC, TYPEOFRENT ASC, INVOICEFREQUENCYNUMBER ASC, SIZEBILLINGGROUP_ID ASC, MAXINVENTORY ASC);

ALTER TABLE CUSTOMERRENTPRICE
  DROP CONSTRAINT TYPEOFRENT_VALID1;
ALTER TABLE CUSTOMERRENTPRICE
  ADD CONSTRAINT CHK_CUSTRENTPRICE_TYPEOFRENT
  CHECK (TYPEOFRENT IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11));
  
COMMENT ON COLUMN CUSTOMERRENTPRICE.TYPEOFRENT
  IS '1 = Piece price 2 = Changes/Wk 3 = Overuse price 4 = Per Frequency 5 = Underuse Price 6 = Lease 7 = Split price 8 = Per Invoice Frequency 9 = Residential rent (not used) 10 = Loan 11 = Changes per week per max inventory';

ALTER TABLE CUSTOMERRENTPRICE
  ADD CONSTRAINT CHK_CUSTRENTPRICE_RENTMAXINV
  CHECK ((TYPEOFRENT = 11 AND MAXINVENTORY >= 0) OR (TYPEOFRENT <> 11 AND MAXINVENTORY = 0));


--- GENERALRENTPRICE table --- 
ALTER TABLE GENERALRENTPRICE ADD (MAXINVENTORY NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN GENERALRENTPRICE.MAXINVENTORY
  IS 'Only used for TypeOfRent = 11. For all other must be filled with 0. For TypeOfRent = 11 must be >= 0.';

DROP INDEX XAK1GENERALRENTPRICE;
CREATE UNIQUE INDEX XAK1GENERALRENTPRICE ON GENERALRENTPRICE (GENERALPRICELIST_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, FINISHINGMETHOD_ID ASC, TYPEOFRENT ASC, STARTDATE ASC, CHANGESPERWEEK ASC, INVOICEFREQUENCYNUMBER ASC, SIZEBILLINGGROUP_ID ASC, MAXINVENTORY ASC);

ALTER TABLE GENERALRENTPRICE
  DROP CONSTRAINT TYPEOFRENT_VALID2;
ALTER TABLE GENERALRENTPRICE
  ADD CONSTRAINT CHK_GENRENTPRICE_TYPEOFRENT
  CHECK (TYPEOFRENT IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11));
  
COMMENT ON COLUMN GENERALRENTPRICE.TYPEOFRENT
  IS '1 = Piece price 2 = Changes/Wk 3 = Overuse price 4 = Per Frequency 5 = Underuse Price 6 = Lease 7 = Split price 8 = Per Invoice Frequency 9 = Residential rent (not used) 10 = Loan 11 = Changes per week per max inventory';

ALTER TABLE GENERALRENTPRICE
  ADD CONSTRAINT CHK_GENRENTPRICE_RENTMAXINV
  CHECK ((TYPEOFRENT = 11 AND MAXINVENTORY >= 0) OR (TYPEOFRENT <> 11 AND MAXINVENTORY = 0));

--- PRODUCTLISTPERCUSTOMER TABLE ---
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (BM1RENTCPWMAX NUMBER DEFAULT 0 NOT NULL
                                                      CONSTRAINT CHK_PLPC_BM1RENTCPWMAX CHECK (BM1RENTCPWMAX IN (0, 1, 2)));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.BM1RENTCPWMAX
  IS '0 = Not applicable
1 = Rent Price based on charges per week and Max Inventory, rent based on real quantity in circulation
2 = Rent Price based on charges per week and Max Inventory, rent based on max quantity
Option 1 and 2 are only available for the module RENTBM1CPWMAX and can only be set if BillingMethod = 1 and ChangesPerWeek = ‘Y’. In all other cases it should be set to 0. 
';

--- CUSTOMERGARMENTCHARGES TABLE ---
ALTER TABLE CUSTOMERGARMENTCHARGES ADD (DEFAULTBM1RENTCPWMAX NUMBER DEFAULT 0 NOT NULL
                                                             CONSTRAINT CHK_CUSTGRMCH_DEFBM1RENTCPW CHECK (DEFAULTBM1RENTCPWMAX IN (0, 1, 2)));

COMMENT ON COLUMN CUSTOMERGARMENTCHARGES.DEFAULTBM1RENTCPWMAX
  IS '0 = Not applicable
1 = Rent Price based on charges per week and Max Inventory, rent based on real quantity in circulation
2 = Rent Price based on charges per week and Max Inventory, rent based on max quantity
Option 1 and 2 are only available for the module RENTBM1CPWMAX. This field is only a default for the setting on PLPC.
';

--- STATUSQUOHEADER TABLE ---
COMMENT ON COLUMN STATUSQUOHEADER.STATUSQUOTYPE
  IS '1= Items In Circ
2= Sets
3= Wearers
4= Stock Quantities
5 = Lease
6 = stock charge
7 = mats in circulation
8 = on site inventory
9 = costs PBA
10 = compress
11 = clean up
12 = commission
13 = max items in circ
14 = AR Aging
15 = Items In Circ Residential
16 = Wearer Inventories';

ALTER TABLE STATUSQUOHEADER
  DROP CONSTRAINT STATUSQUOTYPE_VALID;
ALTER TABLE STATUSQUOHEADER
  ADD CONSTRAINT CHK_SQH_STATUSQUOTYPE
  CHECK (STATUSQUOTYPE BETWEEN 1 AND 16);

--- SQWEARERINVENTORY TABLE ---
CREATE SEQUENCE SEQ_SQWEARERINVENTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE SQWEARERINVENTORY
(
  SQWEARERINVENTORY_ID      NUMBER NOT NULL,
  STATUSQUOHEADER_ID        NUMBER NOT NULL,
  CUSTOMER_LINK_ID          NUMBER NOT NULL,
  WEAREREMPLOYMENT_LINK_ID  NUMBER NOT NULL,
  PRODUCT_LINK_ID           NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID    NUMBER NOT NULL,
  FINISHINGMETHOD_LINK_ID   NUMBER NOT NULL,
  CHANGESPERWEEK            NUMBER NOT NULL,
  MAXINVENTORY              NUMBER NOT NULL,
  CIRCINVENTORY             NUMBER NOT NULL,
  EXPIREDYN                 VARCHAR2(1) DEFAULT 'N' NOT NULL 
                            CONSTRAINT CHK_SQWEARERINV_EXPIREDYN CHECK (EXPIREDYN IN ('Y', 'N')),
  DISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
)
;

COMMENT ON COLUMN SQWEARERINVENTORY.EXPIREDYN
  IS 'Set to Yes if the wearer inventory or wearer is not active anymore but there are still items in circulation (CircInventory > 0)';

ALTER TABLE SQWEARERINVENTORY
  ADD CONSTRAINT XPKSQWEARERINVENTORY PRIMARY KEY (SQWEARERINVENTORY_ID);
  
CREATE UNIQUE INDEX XAK1SQWEARERINVENTORY ON SQWEARERINVENTORY (STATUSQUOHEADER_ID ASC, WEAREREMPLOYMENT_LINK_ID ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF1SQWEARERINVENTORY ON SQWEARERINVENTORY (STATUSQUOHEADER_ID ASC);
CREATE INDEX XIF2SQWEARERINVENTORY ON SQWEARERINVENTORY (CUSTOMER_LINK_ID ASC);
CREATE INDEX XIF3SQWEARERINVENTORY ON SQWEARERINVENTORY (WEAREREMPLOYMENT_LINK_ID ASC);
CREATE INDEX XIF4SQWEARERINVENTORY ON SQWEARERINVENTORY (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF5SQWEARERINVENTORY ON SQWEARERINVENTORY (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF6SQWEARERINVENTORY ON SQWEARERINVENTORY (FINISHINGMETHOD_LINK_ID ASC);
CREATE INDEX XIF7SQWEARERINVENTORY ON SQWEARERINVENTORY (DISTRIBUTIONPOINT_LINK_ID ASC);

ALTER TABLE SQWEARERINVENTORY
  ADD CONSTRAINT FK_SQWEARERINV_SQH FOREIGN KEY (STATUSQUOHEADER_ID)
  REFERENCES STATUSQUOHEADER (STATUSQUOHEADER_ID) ON DELETE CASCADE;
  
ALTER TABLE SQWEARERINVENTORY
  ADD CONSTRAINT FK_SQWEARERINV_CUST FOREIGN KEY (CUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);
  
ALTER TABLE SQWEARERINVENTORY
  ADD CONSTRAINT FK_SQWEARERINV_WEARER FOREIGN KEY (WEAREREMPLOYMENT_LINK_ID)
  REFERENCES WEAREREMPLOYMENT_LINK (WEAREREMPLOYMENT_LINK_ID);
  
ALTER TABLE SQWEARERINVENTORY
  ADD CONSTRAINT FK_SQWEARERINV_PROD FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
  
ALTER TABLE SQWEARERINVENTORY
  ADD CONSTRAINT FK_SQWEARERINV_SIZE FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
  
ALTER TABLE SQWEARERINVENTORY
  ADD CONSTRAINT FK_SQWEARERINV_FM FOREIGN KEY (FINISHINGMETHOD_LINK_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
  
ALTER TABLE SQWEARERINVENTORY
  ADD CONSTRAINT FK_SQWEARERINV_DP FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
  REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);

--- INVOICELINERENT TABLE ---
CREATE TABLE INVOICELINERENT
(
  INVOICELINE_ID            NUMBER NOT NULL,
  CHANGESPERWEEK            NUMBER DEFAULT 0 NOT NULL,
  MAXINVENTORY              NUMBER DEFAULT 0 NOT NULL,
  NUMBEROFWEARERINVENTORIES NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
)
;

COMMENT ON COLUMN INVOICELINERENT.CHANGESPERWEEK
  IS 'Used for Billing Method 1 Rent with Price defined per Changes per Week and per Max Inventory (module RENTBM1CPWMAX).';
COMMENT ON COLUMN INVOICELINERENT.MAXINVENTORY
  IS 'Used for Billing Method 1 Rent with Price defined per Changes per Week and per Max Inventory (module RENTBM1CPWMAX).';
COMMENT ON COLUMN INVOICELINERENT.NUMBEROFWEARERINVENTORIES
  IS 'Used for Billing Method 1 Rent with Price defined per Changes per Week and per Max Inventory (module RENTBM1CPWMAX).';
  
ALTER TABLE INVOICELINERENT
  ADD CONSTRAINT XPKINVOICELINERENT PRIMARY KEY (INVOICELINE_ID);
  
ALTER TABLE INVOICELINERENT
  ADD CONSTRAINT FK_INVOICELINERENT_ILP FOREIGN KEY (INVOICELINE_ID)
  REFERENCES INVOICELINEPRODUCT (INVOICELINE_ID) ON DELETE CASCADE;

