--Order 20011527.80

---CUSTOMERRESIDENTIALSETTINGS---

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
	ADD (RENTFORCLIENTS NUMBER  DEFAULT 0 NOT NULL CONSTRAINT RentForClients_valid1_1 CHECK (RENTFORCLIENTS IN (0, 1)));

COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.RENTFORCLIENTS IS '0 = No rent for clients is charged for this customer
1 = Allow Rent to be charged per client for this customer.';


---CLIENTRENTADJUSTMENT---

CREATE SEQUENCE SEQ_CLIENTRENTADJUSTMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLIENTRENTADJUSTMENT
(
	CLIENTRENTADJUSTMENT_ID NUMBER NOT NULL ,
	CLIENT_ID            NUMBER NOT NULL ,
	PRODUCT_ID           NUMBER NOT NULL ,
	SIZEDEFINITION_ID    NUMBER NOT NULL ,
	FINISHINGMETHOD_ID   NUMBER NOT NULL ,
	ADJUSTMENTDATE       DATE NOT NULL ,
	QUANTITY             NUMBER(4) NOT NULL  CONSTRAINT  Quantity_valid1_1 CHECK (QUANTITY BETWEEN -9999 AND 9999),
	REASONCODE_ID        NUMBER NULL ,
	REMARK               VARCHAR2(80) NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


ALTER TABLE CLIENTRENTADJUSTMENT
ADD CONSTRAINT  XPKCLIENTRENTADJUSTMENT PRIMARY KEY (CLIENTRENTADJUSTMENT_ID);

CREATE UNIQUE INDEX XAK1CLIENTRENTADJUSTMENT ON CLIENTRENTADJUSTMENT
(CLIENT_ID   ASC,PRODUCT_ID   ASC,SIZEDEFINITION_ID   ASC,FINISHINGMETHOD_ID   ASC,ADJUSTMENTDATE   ASC,REASONCODE_ID   ASC);

CREATE  INDEX XIF1CLIENTRENTADJUSTMENT ON CLIENTRENTADJUSTMENT
(CLIENT_ID   ASC);

CREATE  INDEX XIF2CLIENTRENTADJUSTMENT ON CLIENTRENTADJUSTMENT
(PRODUCT_ID   ASC);

CREATE  INDEX XIF3CLIENTRENTADJUSTMENT ON CLIENTRENTADJUSTMENT
(SIZEDEFINITION_ID   ASC);

CREATE  INDEX XIF4CLIENTRENTADJUSTMENT ON CLIENTRENTADJUSTMENT
(FINISHINGMETHOD_ID   ASC);

CREATE  INDEX XIF5CLIENTRENTADJUSTMENT ON CLIENTRENTADJUSTMENT
(REASONCODE_ID   ASC);

ALTER TABLE CLIENTRENTADJUSTMENT
ADD CONSTRAINT CLIENTRENTADJUSTMENT_CLIENT FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT (CLIENT_ID) ON DELETE CASCADE;

ALTER TABLE CLIENTRENTADJUSTMENT
ADD CONSTRAINT CLIENTRENTADJUSTMENT_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);

ALTER TABLE CLIENTRENTADJUSTMENT
ADD CONSTRAINT CLIENTRENTADJUSTMENT_SIZEDEF FOREIGN KEY (SIZEDEFINITION_ID) REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

ALTER TABLE CLIENTRENTADJUSTMENT
ADD CONSTRAINT CLIENTRENTADJUSTMENT_FINISHING FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);

ALTER TABLE CLIENTRENTADJUSTMENT
ADD CONSTRAINT CLIENTRENTADJUSTMENT FOREIGN KEY (REASONCODE_ID) REFERENCES REASONCODE (REASONCODE_ID);


---CUSTOMERRESIDENTIALRENTPRICE---

CREATE SEQUENCE SEQ_CUSTOMERRESIDENTRENTPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERRESIDENTIALRENTPRICE
(
	CUSTOMERRESIDENTALRENTPRICE_ID NUMBER NOT NULL ,
	CUSTOMER_ID          NUMBER NOT NULL ,
	PRODUCT_ID           NUMBER NOT NULL ,
	SIZEDEFINITION_ID    NUMBER NOT NULL ,
	FINISHINGMETHOD_ID   NUMBER NOT NULL ,
	STARTDATE            DATE NOT NULL ,
	PRICE                NUMBER(15,6) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE CUSTOMERRESIDENTIALRENTPRICE
ADD CONSTRAINT  XPKCUSTOMERRESIDENTALRENTPRICE PRIMARY KEY (CUSTOMERRESIDENTALRENTPRICE_ID);

CREATE  INDEX XIF1CUSTOMERRESIDENTIALRENTPRI ON CUSTOMERRESIDENTIALRENTPRICE
(CUSTOMER_ID   ASC);

CREATE  INDEX XIF2CUSTOMERRESIDENTIALRENTPRI ON CUSTOMERRESIDENTIALRENTPRICE
(PRODUCT_ID   ASC);

CREATE  INDEX XIF3CUSTOMERRESIDENTIALRENTPRI ON CUSTOMERRESIDENTIALRENTPRICE
(SIZEDEFINITION_ID   ASC);

CREATE  INDEX XIF4CUSTOMERRESIDENTIALRENTPRI ON CUSTOMERRESIDENTIALRENTPRICE
(FINISHINGMETHOD_ID   ASC);

ALTER TABLE CUSTOMERRESIDENTIALRENTPRICE
ADD CONSTRAINT CUSTRESRENTPRICE_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);

ALTER TABLE CUSTOMERRESIDENTIALRENTPRICE
ADD CONSTRAINT CUSTRESRENTPRICE_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);

ALTER TABLE CUSTOMERRESIDENTIALRENTPRICE
ADD CONSTRAINT CUSTRESRENTPRICE_SIZEDEF FOREIGN KEY (SIZEDEFINITION_ID) REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

ALTER TABLE CUSTOMERRESIDENTIALRENTPRICE
ADD CONSTRAINT CUSTRESRENTPRICE_FINISHINGM FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERRESIDENTIALRENTPRI ON CUSTOMERRESIDENTIALRENTPRICE
(CUSTOMER_ID   ASC,PRODUCT_ID   ASC,SIZEDEFINITION_ID   ASC,FINISHINGMETHOD_ID   ASC,STARTDATE   ASC);


---INVOICELINECLIENTRENT---

CREATE TABLE INVOICELINECLIENTRENT
(
	INVOICELINE_ID       NUMBER NOT NULL ,
	CLIENT_LINK_ID       NUMBER NOT NULL ,
	QUANTITY             NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE INVOICELINECLIENTRENT
ADD CONSTRAINT  XPKINVOICELINECLIENTRENT PRIMARY KEY (INVOICELINE_ID,CLIENT_LINK_ID);

CREATE  INDEX XIF1INVOICELINECLIENTRENT ON INVOICELINECLIENTRENT
(INVOICELINE_ID   ASC);

CREATE  INDEX XIF2INVOICELINECLIENTRENT ON INVOICELINECLIENTRENT
(CLIENT_LINK_ID   ASC);

ALTER TABLE INVOICELINECLIENTRENT
ADD CONSTRAINT INVOICELINECLIENTRENT_INVOICEL FOREIGN KEY (INVOICELINE_ID) REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;

ALTER TABLE INVOICELINECLIENTRENT
ADD CONSTRAINT INVOICELINECLIENTRENT_CLIENT_L FOREIGN KEY (CLIENT_LINK_ID) REFERENCES CLIENT_LINK (CLIENT_LINK_ID);


---RESIDENTIALRENT---

CREATE SEQUENCE SEQ_RESIDENTIALRENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE RESIDENTIALRENT
(
	RESIDENTIALRENT_ID   NUMBER NOT NULL ,
	CUSTOMER_ID          NUMBER NOT NULL ,
	PRODUCT_ID           NUMBER NOT NULL ,
	SIZEDEFINITION_ID    NUMBER NOT NULL ,
	FINISHINGMETHOD_ID   NUMBER NOT NULL ,
	STARTDATE            DATE NOT NULL ,
	ENDDATE              DATE NOT NULL ,
	AUTOADDQUANTITY      NUMBER(4) DEFAULT  0  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE RESIDENTIALRENT
ADD CONSTRAINT  XPKRESIDENTIALRENT PRIMARY KEY (RESIDENTIALRENT_ID);

CREATE  INDEX XIF1RESIDENTIALRENT ON RESIDENTIALRENT
(CUSTOMER_ID   ASC);

CREATE  INDEX XIF2RESIDENTIALRENT ON RESIDENTIALRENT
(PRODUCT_ID   ASC);

CREATE  INDEX XIF3RESIDENTIALRENT ON RESIDENTIALRENT
(SIZEDEFINITION_ID   ASC);

CREATE  INDEX XIF4RESIDENTIALRENT ON RESIDENTIALRENT
(FINISHINGMETHOD_ID   ASC);

CREATE UNIQUE INDEX XAK1RESIDENTIALRENT ON RESIDENTIALRENT
(CUSTOMER_ID   ASC,PRODUCT_ID   ASC,SIZEDEFINITION_ID   ASC,FINISHINGMETHOD_ID   ASC,STARTDATE   ASC);

ALTER TABLE RESIDENTIALRENT
ADD CONSTRAINT RESIDENTIALRENT_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);

ALTER TABLE RESIDENTIALRENT
ADD CONSTRAINT RESIDENTIALRENT_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);

ALTER TABLE RESIDENTIALRENT
ADD CONSTRAINT RESIDENTIALRENT_SIZEDEF FOREIGN KEY (SIZEDEFINITION_ID) REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

ALTER TABLE RESIDENTIALRENT
ADD CONSTRAINT RESIDENTIALRENT_FINISHINGMETHO FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);


---GENERALRENTPRICE---

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


---invoicelinetype---

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

insert into invoicelinetype
						(invoicelinetype_id, 
						 timestamp, 
						 systemuser_id,
						 includeinminimumcalc,
						 factorydescription,
						 type,
						 turnovergroup_id,
						 defineturnovergroup,
						 definespecialcharge,
						 taxtype,
						 eannumber,
						 supressprinting) 
  select 30,
         sysdate,
         getminsystemuser(),
         'N',
         'Client Rent',
         30,
         null,
         'N',
         'Y',
         2,
         null,
         0
    from dual;

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 = 30;

commit;

---invoicelinesubtype---

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             0,
             30,
             'Client Rent',
             1,
             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,
             1,
             sysdate
        from invoicelinesubtype i, languages l
       where l.translatedescription = 'Y' and  subtype = 0 and i.invoicelinetype_id = 30;
commit;