--Order 20013178.80 Changes at rent for loans

-------------------------DELIVERYVARIATIONS TABLE-------------------------

BEGIN
EXECUTE IMMEDIATE ('ALTER TABLE DELIVERYVARIATIONS
  DROP CONSTRAINT SOILCOUNTSUBTYPE_VALID');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
ALTER TABLE DELIVERYVARIATIONS
  ADD CONSTRAINT SOILCOUNTSUBTYPE_VALID
  CHECK (SOILCOUNTSUBTYPE IN (0,1,2,3,4,5,6));


COMMENT ON COLUMN DELIVERYVARIATIONS.SOILCOUNTSUBTYPE IS '0 - Not applicable
1 - Regular soil count
2 - Shortage
3 - Rejected
4 = Heavy Soil
5 = Clean Return
6 = Loss
Default value 0
Note: option 6 is used in combination with Origin = 7 (Replacement). Others are used in combination with Origin = 1 (soil count).
';

ALTER TABLE DELIVERYVARIATIONS
	ADD (REASONCODE_ID NUMBER NULL,
	     CHARGESTARTDATE DATE NULL);

DROP INDEX XAK2DELIVERYVARIATIONS;

CREATE UNIQUE INDEX XAK2DELIVERYVARIATIONS ON DELIVERYVARIATIONS
(DISTRIBUTIONPOINT_ID   ASC,PRODUCT_ID   ASC,SIZEDEFINITION_ID   ASC,FINISHINGMETHOD_ID   ASC,PACKINGNOTE_ID   ASC,PROCESSED   ASC,DELIVERYDATE   ASC,ORIGIN   ASC,CAUSENOTE_ID   ASC,UPDATERENTQTY   ASC,SOILCOUNTSUBTYPE   ASC,WEBORDERLINE_ID   ASC,REFERENCE   ASC);
	

COMMENT ON COLUMN DELIVERYVARIATIONS.CHARGESTARTDATE IS 'Only used for module RELATEDLOANBILLING.';

CREATE  INDEX XIF12DELIVERYVARIATIONS ON DELIVERYVARIATIONS
(REASONCODE_ID   ASC);

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


-----------------------------RENTALQTYADJUSTMENT TABLE------------------------

ALTER TABLE RENTALQTYADJUSTMENT
	ADD CONSTRAINT Origin_valid5  CHECK (ORIGIN IN (0, 1, 2));

COMMENT ON COLUMN RENTALQTYADJUSTMENT.ORIGIN IS '0 – Manual entry 
1 – Conversion
2 - Loan
';


DROP INDEX XAK1RENTALQTYADJUSTMENT;


CREATE UNIQUE INDEX XAK1RENTALQTYADJUSTMENT ON RENTALQTYADJUSTMENT
(PRODUCTLISTPERDISTRIBPOINT_ID   ASC,ADJUSTMENTDATE   ASC,REASONCODE_ID   ASC,CANCELLATIONTIMESTAMP   ASC,DELIVERYVARIATIONS_ID   ASC);


-----------------------------CUSTOMERRENTPRICE TABLE---------------------------
ALTER TABLE CUSTOMERRENTPRICE
	ADD CONSTRAINT TYPEOFRENT_valid1  CHECK (TYPEOFRENT IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10));


ALTER TABLE CUSTOMERRENTPRICE MODIFY(TYPEOFRENT NUMBER(2));

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


------------------------------GENERALRENTPRICE TABLE-------------------------
ALTER TABLE GENERALRENTPRICE MODIFY(TYPEOFRENT NUMBER(2));

ALTER TABLE GENERALRENTPRICE
	ADD CONSTRAINT TYPEOFRENT_valid2  CHECK (TYPEOFRENT IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10));

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
10 = Loan';


-----------------------------PRODUCTGROUP TABLE--------------------------
ALTER TABLE PRODUCTGROUP
	ADD (LOANTURNOVERGROUP_ID NUMBER NULL);

CREATE  INDEX XIF12PRODUCTGROUP ON PRODUCTGROUP
(LOANTURNOVERGROUP_ID   ASC);

ALTER TABLE PRODUCTGROUP
ADD CONSTRAINT PRODUCTGROUP_TURNOVERGROUP FOREIGN KEY (LOANTURNOVERGROUP_ID) REFERENCES TURNOVERGROUP (TURNOVERGROUP_ID);

------------------------------PRODUCT TABLE-------------------------------

ALTER TABLE PRODUCT
	ADD (LOANTURNOVERGROUP_ID NUMBER NULL);

CREATE  INDEX XIF32PRODUCT ON PRODUCT
(LOANTURNOVERGROUP_ID   ASC);

ALTER TABLE PRODUCT
ADD CONSTRAINT PRODUCT_TURNOVERGROUP FOREIGN KEY (LOANTURNOVERGROUP_ID) REFERENCES TURNOVERGROUP (TURNOVERGROUP_ID);


------------------------------INVOICELINESUBTYPE TABLE---------------------------

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             41,
             1,
             'New Loan',
             1,
             sysdate
        from dual;
commit;

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             42,
             1,
             'Outstanding Loan',
             1,
             sysdate
        from dual;
commit;

-----------------------------INVOICELINESUBTYPE_DESC-----------------------

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 = 41 and i.invoicelinetype_id = 1;
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 = 42 and i.invoicelinetype_id = 1;
commit;	