--ABS-5749
----------------------RESIDUALVALUESCHEME----------------------
ALTER TABLE RESIDUALVALUESCHEME ADD (REPLACEMENTPRICETYPE NUMBER DEFAULT 1 NOT NULL CONSTRAINT REPLPRICETYPE_VALID1_1 CHECK (REPLACEMENTPRICETYPE IN (1, 2, 3)));

COMMENT ON COLUMN RESIDUALVALUESCHEME.CALCULATIONBASIS
  IS '1 = weeks in circ, 2 = number of washes, 3 = actual weeks in use';

COMMENT ON COLUMN RESIDUALVALUESCHEME.REPLACEMENTPRICETYPE
  is 'Type of Replacement price used for residual value scheme. Possible values :
1 = actual residual value scheme and replacement price
2 = historic replacement price at moment of first issue
3 = historic replacement price at moment of last issue
';

UPDATE RESIDUALVALUESCHEME RVS
SET RVS.CALCULATIONBASIS = 1
WHERE RVS.CALCULATIONBASIS NOT IN(1,2,3);
COMMIT;

ALTER TABLE RESIDUALVALUESCHEME MODIFY (CALCULATIONBASIS DEFAULT 1);

ALTER TABLE RESIDUALVALUESCHEME
  ADD CONSTRAINT CALCBASIS_VALID1_1
  CHECK (CALCULATIONBASIS IN (1, 2, 3));

----------------------UNIQUEITEMFIXEDINFO----------------------
ALTER TABLE UNIQUEITEMFIXEDINFO ADD (HISTREPLACEMENTPRICEFIRSTISSUE NUMBER DEFAULT 0 NOT NULL,
									 HISTREPLACEMENTPRICELASTISSUE  NUMBER DEFAULT 0 NOT NULL);

----------------------RESIDUALVALUEHISTORY---------------------- 
CREATE SEQUENCE SEQ_RESIDUALVALUEHISTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE RESIDUALVALUEHISTORY
(
  RESIDUALVALUEHISTORY_ID   NUMBER NOT NULL,
  UNIQUEITEM_LINK_ID        NUMBER NOT NULL,
  CUSTOMER_LINK_ID          NUMBER NULL,
  DEPARTMENT_LINK_ID        NUMBER NULL,
  DISTRIBUTIONPOINT_LINK_ID NUMBER NULL,
  WEAREREMPLOYMENT_LINK_ID  NUMBER NULL,
  PRODUCT_LINK_ID           NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID    NUMBER NOT NULL,
  FINISHINGMETHOD_ID        NUMBER NULL,
  CALCULATIONDATE           DATE DEFAULT SYSDATE NOT NULL,
  TRANSACTIONTYPE           NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSACTIONTYPE_VALID1_1  CHECK (TRANSACTIONTYPE BETWEEN 0 AND 8),
  YEAR                      NUMBER NOT NULL,
  WEEK                      NUMBER NOT NULL,
  RESIDUALVALUESCHEME_ID    NUMBER NULL,
  RESIDUALVALUESCHEMECODE   VARCHAR2(6) NULL,
  CALCULATIONBASIS          NUMBER NULL CONSTRAINT CALCULATIONBASIS_VALID1_1  CHECK (CALCULATIONBASIS BETWEEN 1 AND 3),
  BASECALCULATIONWEEKS      NUMBER DEFAULT 0 NOT NULL,
  REPLACEMENTPRICETYPE      NUMBER DEFAULT 1 NOT NULL CONSTRAINT REPLACEMENTPRICETYPE_VALID1_1  CHECK (REPLACEMENTPRICETYPE BETWEEN 1 AND 3),
  RESVALSCHEMEDEFINITION_ID NUMBER NULL,
  DEFINITIONTYPE            NUMBER DEFAULT 1 NOT NULL CONSTRAINT DEFINITIONTYPE_VALID1_1 CHECK (DEFINITIONTYPE BETWEEN 1 AND 2),
  STARTPERCENTAGE           NUMBER(15,6) NULL,
  ENDPERCENTAGE             NUMBER(15,6) NULL,
  FIXEDPERCENTAGE           NUMBER(15,6) NULL,
  STARTRANGE                NUMBER NULL,
  ENDRANGE                  NUMBER NULL,
  REPLACEMENTPRICE          NUMBER(15,6) NOT NULL,
  RESIDUALVALUEPERCENTAGE   NUMBER(15,6) NOT NULL,
  RESIDUALVALUEAMOUNT       NUMBER(15,6) NOT NULL,
  CURRENCY_ID               NUMBER NULL,
  CALCULATIONWEEKS          NUMBER DEFAULT 0 NOT NULL,
  WEEKSINCIRC               NUMBER DEFAULT 0 NOT NULL,
  WEEKSONSTOCK              NUMBER DEFAULT 0 NOT NULL,
  WASHES                    NUMBER DEFAULT 0 NOT NULL,
  CUSTOMERRENTSTOP          NUMBER DEFAULT 0 NOT NULL CONSTRAINT CUSTOMERRENTSTOP_VALID1_1  CHECK (CUSTOMERRENTSTOP BETWEEN 0 AND 1),
  RENTSTOP                  NUMBER DEFAULT 0 NOT NULL CONSTRAINT RENTSTOP_VALID1_1  CHECK (RENTSTOP BETWEEN 0 AND 1),
  WEARERINACTIVE            NUMBER DEFAULT 0 NOT NULL CONSTRAINT WEARERINACTIVE_VALID1_1  CHECK (WEARERINACTIVE BETWEEN 0 AND 1),
  SEASONINACTIVE            NUMBER DEFAULT 0 NOT NULL CONSTRAINT SEASONINACTIVE_VALID1_1  CHECK (SEASONINACTIVE BETWEEN 0 AND 1),
  ONPASSIVESTOCK            NUMBER DEFAULT 0 NOT NULL CONSTRAINT ONPASSIVESTOCK_VALID1_1  CHECK (ONPASSIVESTOCK BETWEEN 0 AND 1),
  INDEPOSITSTOCK            NUMBER DEFAULT 0 NOT NULL CONSTRAINT INDEPOSITSTOCK_VALID1_1  CHECK (INDEPOSITSTOCK BETWEEN 0 AND 1),
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL,
  EXPORTED                  NUMBER DEFAULT 0 NOT NULL
);

ALTER TABLE RESIDUALVALUEHISTORY
  ADD CONSTRAINT XPKRESIDUALVALUEHISTORY PRIMARY KEY (RESIDUALVALUEHISTORY_ID);

CREATE UNIQUE INDEX XAK1RESIDUALVALUEHISTORY ON RESIDUALVALUEHISTORY (UNIQUEITEM_LINK_ID, CALCULATIONDATE, TRANSACTIONTYPE);

CREATE INDEX XIE1RESIDUALVALUEHISTORY ON RESIDUALVALUEHISTORY (UNIQUEITEM_LINK_ID, YEAR, WEEK, TRANSACTIONTYPE);

CREATE INDEX XIE2RESIDUALVALUEHISTORY ON RESIDUALVALUEHISTORY (CUSTOMER_LINK_ID, DEPARTMENT_LINK_ID, DISTRIBUTIONPOINT_LINK_ID, WEAREREMPLOYMENT_LINK_ID);

CREATE INDEX XIE3RESIDUALVALUEHISTORY ON RESIDUALVALUEHISTORY (PRODUCT_LINK_ID, SIZEDEFINITION_LINK_ID, FINISHINGMETHOD_ID);

CREATE INDEX XIE4RESIDUALVALUEHISTORY ON RESIDUALVALUEHISTORY (EXPORTED);

COMMENT ON COLUMN RESIDUALVALUEHISTORY.TRANSACTIONTYPE
  IS '0 = SQ process,
1 = Issue,
2 = Return to Stock,
3 = Rag from circulation,
4 = Rag from stock, 
5 = Make customer owned, 
6 = Make laundry owned, 
7 = Undo Rag and Return to Stock, 
8 = Undo rag in circulation';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.CALCULATIONBASIS 
IS 'Possible values:
1 = weeks in circ,
2 = number of washes, 
3 = actual weeks in use';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.REPLACEMENTPRICETYPE
IS 'Possible values:
1 = actual residual value scheme and replacement price,
2 = historic replacement price at moment of first issue,
3 = historic replacement price at moment of last issue';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.DEFINITIONTYPE
IS 'Possible values:
1 = Fixed, 
2 = Linear';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.FIXEDPERCENTAGE
IS 'Null when DefinitionType = 2 (Linear)';
  
COMMENT ON COLUMN RESIDUALVALUEHISTORY.WASHES
IS 'Only relevant when CalculationBasis = 2 ';
  
COMMENT ON COLUMN RESIDUALVALUEHISTORY.CUSTOMERRENTSTOP
IS 'Possible values:
0 = No rentstop, 
1 = Rentstop';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.RENTSTOP
 IS 'Possible values:
0 = No rentstop, 
1 = Rentstop';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.WEARERINACTIVE
IS 'Possible values:
0 = Wearer active, 
1 = Wearer inactive';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.SEASONINACTIVE
IS 'Possible values:
0 = Season active, 
1 = Season inactive';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.ONPASSIVESTOCK
IS 'Possible values:
0 = Not on stock, 
1 = On stock';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.INDEPOSITSTOCK
IS 'Possible values:
0 = Not on stock, 
1 = On deposit stock';

COMMENT ON COLUMN RESIDUALVALUEHISTORY.EXPORTED
IS 'Possible values:
0 = Not exported
1 = Prepared for export
2 = Exported';

--insert into process
INSERT INTO PROCESS(PROCESS_ID,DESCRIPTION,ACTIVE) VALUES ('124','Status Quo Historic Residual value','Y');
COMMIT;


--ABS-7447
----------------------INTERNETORDERLINE---------------------- 
ALTER TABLE INTERNETORDERLINE MODIFY PRODUCTCODE VARCHAR2(16);


----------------------INVOICELINE---------------------- 
ALTER TABLE INVOICELINE MODIFY CODE VARCHAR2(16);


----------------------PACKINGNOTELINE---------------------- 
ALTER TABLE PACKINGNOTELINE MODIFY CUSTOMCODE VARCHAR2(16);

----------------------ERP_O_INVOICELINE--------------------
ALTER TABLE ERP_O_INVOICELINE MODIFY CODE VARCHAR2(16);
----------------------ERP_O_PACKINGNOTELINE--------------------
ALTER TABLE ERP_O_PACKINGNOTELINE MODIFY CUSTOMCODE VARCHAR2(16);

--ABS-5786
----------------------DEPARTMENT---------------------- 
ALTER TABLE DEPARTMENT ADD (ONLYRENTORSALESITEMS NUMBER DEFAULT 0 NOT NULL CONSTRAINT ONLYRENTORSALESITEMS_VALID1_1  CHECK (ONLYRENTORSALESITEMS BETWEEN 0 AND 3));

COMMENT ON COLUMN DEPARTMENT.ONLYRENTORSALESITEMS
IS 'Possible values:
0 = Not applicable 
1 = Both rented garments and sales items are allowed
2 = Only rented garments are allowed
3 = Only sales items are allowed;'
  
--ABS-9498
----------------------SORTINGDEFINITION---------------------
COMMENT ON COLUMN SORTINGDEFINITION.SORTFIELD  IS 
'1 = Department Number 
2 = Wearer Number 
3 = Wearer Name 
4 = Customer Employee Number 
5 = Bank/Locker 
6 = Gender 
7 = Product Code 
8 = Size Code 
9 = Delivery Point 
10 = Garment Dispenser Code 
11 = Customer/Laundry Owned 
12 = Ordernumber
13 = Bank 
14 = ProductSortGroup 
15 = LockerSortGroup 
16 = DistributionPointNumber 
17 = Size sequence
18 = Season Active';

ALTER TABLE SORTINGDEFINITION
  DROP CONSTRAINT SORTFIELD_VALID;
  
ALTER TABLE SORTINGDEFINITION
  ADD CONSTRAINT SORTFIELD_VALID
  CHECK (SORTFIELD BETWEEN 1 AND 18);

--ABS-5791
----------------------DELIVERYSCAN---------------------
COMMENT ON COLUMN DELIVERYSCAN.OUTSCANNODELIVERY
IS '0 = Normal delivery scan (item is scanned out and delivered to customer) 
1 = Rejected outscan, item is not delivered to the customer. Only used for modules OUTSCANNODELIVERY and BATCHJOB. 
2 = Repaired and delivered 
3 = Repaired, not delivered 
4 = Outline, not delivered 
5 = Withdrawal, not delivered 
6 = Replacement, not delivered
7 = Treated, not delivered
8 = Taken out of circulation, not delivered';

ALTER TABLE DELIVERYSCAN
  DROP CONSTRAINT OUTSCANNODELIVERY_VALID;
  
ALTER TABLE DELIVERYSCAN
  ADD CONSTRAINT OUTSCANNODELIVERY_VALID
  CHECK (OUTSCANNODELIVERY IN (0, 1, 2, 3, 4, 5, 6, 7, 8));


----------------------PACKINGNOTELINE---------------------
--ALTER TABLE PACKINGNOTELINE ADD (CHARGERETURNEDFROMCIRC NUMBER DEFAULT 0 NOT NULL);



--ABS-5766
----------------------WEARERINVENTORY---------------------
ALTER TABLE WEARERINVENTORY ADD (RENTSTOPDEACTIVATEDBYITEM_ID NUMBER NULL);



--ABS-7451
----------------------RAGGEDITEM---------------------
ALTER TABLE RAGGEDITEM ADD (RESVALUECHARGED NUMBER DEFAULT 0 NOT NULL CONSTRAINT RESVALUECHARGED_VALID1_1  CHECK (RESVALUECHARGED IN (0, 1)),
							RESIDUALVALUEPERCENTAGE NUMBER DEFAULT 0 NOT NULL,
							HISTREPLACEMENTPRICEFIRSTISSUE NUMBER DEFAULT 0 NOT NULL,
							HISTREPLACEMENTPRICELASTISSUE NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN RAGGEDITEM.RESVALUECHARGED
IS 'Field indicates if residual value was intended to be charged for unique item at the moment it was ragged. Possible values:
0 = Not Charged
1 = Charged';

---------------------------
update raggeditem ri
   set ri.resvaluecharged = 1
 where ri.reasoncode_id = (select rc.reasoncode_id from reasoncode rc where rc.reasoncode_id=ri.reasoncode_id and rc.chargeresidualvalue = 'Y')
   and ri.poolitem = 2;
COMMIT;

   

--ABS-5771
----------------------PRODUCTLISTPERCUSTOMER---------------------
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (RESETRVUPONREISSUE NUMBER DEFAULT 0 NOT NULL CONSTRAINT RESETRVUPONREISSUE_VALID1_1  CHECK (RESETRVUPONREISSUE IN (0 ,1)));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.RESETRVUPONREISSUE
IS 'Possible values:
0 = No
1 = Yes';

----------------------CUSTOMERREISSUEPRICE---------------------
CREATE SEQUENCE SEQ_CUSTOMERREISSUEPRICE
START WITH 1
INCREMENT BY 1;

 CREATE TABLE CUSTOMERREISSUEPRICE
(
  CUSTOMERREISSUEPRICE_ID NUMBER NOT NULL,
  PRODUCT_ID              NUMBER NOT NULL,
  SIZEDEFINITION_ID       NUMBER NOT NULL,
  CUSTOMER_ID             NUMBER NOT NULL,
  STARTDATE               DATE DEFAULT SYSDATE NOT NULL,
  PRICE                   NUMBER(15,6) DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               TIMESTAMP NOT NULL);

CREATE UNIQUE INDEX XAK1CUSTOMERREISSUEPRICE ON CUSTOMERREISSUEPRICE (CUSTOMER_ID, PRODUCT_ID, SIZEDEFINITION_ID, STARTDATE);

CREATE INDEX XIE1CUSTOMERREISSUEPRICE ON CUSTOMERREISSUEPRICE (PRODUCT_ID, SIZEDEFINITION_ID);

CREATE INDEX XIF1CUSTOMERREISSUEPRICE ON CUSTOMERREISSUEPRICE (PRODUCT_ID);

CREATE INDEX XIF2CUSTOMERREISSUEPRICE ON CUSTOMERREISSUEPRICE (SIZEDEFINITION_ID);

CREATE INDEX XIF3CUSTOMERREISSUEPRICE ON CUSTOMERREISSUEPRICE (CUSTOMER_ID);

ALTER TABLE CUSTOMERREISSUEPRICE
  ADD CONSTRAINT XPKCUSTOMERREISSUEPRICE PRIMARY KEY (CUSTOMERREISSUEPRICE_ID);
  
ALTER TABLE CUSTOMERREISSUEPRICE
  ADD CONSTRAINT CUST_REISSUEPRICE_PRODUCT_ID FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID);
  
ALTER TABLE CUSTOMERREISSUEPRICE
  ADD CONSTRAINT CUST_REISSUEPRICE_SIZEDEF_ID FOREIGN KEY (SIZEDEFINITION_ID)
  REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);
  
ALTER TABLE CUSTOMERREISSUEPRICE
  ADD CONSTRAINT CUST_REISSUEPRICE_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID);

--ABS-5792
----------------------PRODUCTLISTPERCUSTOMER---------------------
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (ENDRENTDATEMETHOD NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.ENDRENTDATEMETHOD
IS 'Possible values:
0 = Do not take into account weeks in use prev. issues,
1 = Do take into account weeks in use prev. issues';  
  

  
--ABS-5785
----------------------PRODUCTLISTPERCUSTOMER---------------------
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (SEASONALRENT NUMBER DEFAULT 0 NOT NULL CONSTRAINT SEASONALRENT_VALID1_1  CHECK (SEASONALRENT IN (0, 1, 2)));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.SEASONALRENT
IS 'Allowed values:
0 = Not applicable
1 = Only charge rent during active season
2 = Charge rent for both active and inactive season)';

  
-----------------------------------------------
update productlistpercustomer plpc
   set plpc.seasonalrent = 1
 where plpc.seasontype_id is not null
   and plpc.product_id =
       (select p.product_id
          from product p
         where p.product_id = plpc.product_id
           and p.typeofmerchandise in (1, 2));
COMMIT;
