-- ABS-7332

-----------------  PRODUCTCONVERSION   -------------------------------------
ALTER TABLE PRODUCTCONVERSION ADD (PRODUCTCATEGORY NUMBER DEFAULT 0 NOT NULL CONSTRAINT PRODUCTCATEGORY_VALID CHECK (PRODUCTCATEGORY IN (0,1,2)));

COMMENT ON COLUMN PRODUCTCONVERSION.PRODUCTCATEGORY
IS '0 = Garments 
1 = OR 
2 = Linen';


-----------------  PRODUCTCONVERSIONFM   -------------------------------------
CREATE SEQUENCE SEQ_PRODUCTCONVERSIONFM
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRODUCTCONVERSIONFM
(
  PRODUCTCONVERSIONFM_ID  NUMBER NOT NULL,
  PRODUCTCONVERSION_ID    NUMBER NOT NULL,
  SOURCEFINMETHOD_ID      NUMBER NOT NULL,
  DESTINATIONFINMETHOD_ID NUMBER NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
);

CREATE UNIQUE INDEX XAKPRODUCTCONVERSIONFM ON PRODUCTCONVERSIONFM (PRODUCTCONVERSION_ID,SOURCEFINMETHOD_ID);

CREATE INDEX XIF1PRODUCTCONVERSIONFM ON PRODUCTCONVERSIONFM (PRODUCTCONVERSION_ID);

CREATE INDEX XIF2PRODUCTCONVERSIONFM ON PRODUCTCONVERSIONFM (SOURCEFINMETHOD_ID);

CREATE INDEX XIF3PRODUCTCONVERSIONFM ON PRODUCTCONVERSIONFM (DESTINATIONFINMETHOD_ID);


ALTER TABLE PRODUCTCONVERSIONFM
  ADD CONSTRAINT XPKPRODUCTCONVERSIONFM PRIMARY KEY (PRODUCTCONVERSIONFM_ID);
  
ALTER TABLE PRODUCTCONVERSIONFM
  ADD CONSTRAINT PROD_CONV_PRODUCTCONVERSION_ID FOREIGN KEY (PRODUCTCONVERSION_ID)
  REFERENCES PRODUCTCONVERSION (PRODUCTCONVERSION_ID) ON DELETE CASCADE;
  
ALTER TABLE PRODUCTCONVERSIONFM
  ADD CONSTRAINT PROD_CONV_FINISHINGMETHOD_ID FOREIGN KEY (SOURCEFINMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID) ON DELETE CASCADE;
  
ALTER TABLE PRODUCTCONVERSIONFM
  ADD CONSTRAINT PROD_CONV_DESTIN_FINMETHOD_ID FOREIGN KEY (DESTINATIONFINMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
  
 
-----------------  PRODUCTCONVERSIONCPL   ------------------------------------- 
CREATE SEQUENCE SEQ_PRODUCTCONVERSIONCPL
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRODUCTCONVERSIONCPL
(
  PRODUCTCONVERSIONCPL_ID       NUMBER NOT NULL,
  PRODUCTCONVERSION_ID          NUMBER NOT NULL,
  PRODUCTLISTPERDISTRIBPOINT_ID NUMBER NOT NULL,
  NEWRENTALQUANTITY             NUMBER NOT NULL,
  REMARK                        VARCHAR2(500) NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
);

CREATE UNIQUE INDEX XAKPRODUCTCONVERSIONCPL ON PRODUCTCONVERSIONCPL (PRODUCTCONVERSION_ID, PRODUCTLISTPERDISTRIBPOINT_ID);

CREATE INDEX XIF1PRODUCTCONVERSIONCPL ON PRODUCTCONVERSIONCPL (PRODUCTCONVERSION_ID);

CREATE INDEX XIF2PRODUCTCONVERSIONCPL ON PRODUCTCONVERSIONCPL (PRODUCTLISTPERDISTRIBPOINT_ID);

ALTER TABLE PRODUCTCONVERSIONCPL
  ADD CONSTRAINT XPKPRODUCTCONVERSIONCPL PRIMARY KEY (PRODUCTCONVERSIONCPL_ID);
  
ALTER TABLE PRODUCTCONVERSIONCPL
  ADD CONSTRAINT PROD_CONV_CPL_PROD_CONV_ID FOREIGN KEY (PRODUCTCONVERSION_ID)
  REFERENCES PRODUCTCONVERSION (PRODUCTCONVERSION_ID) ON DELETE CASCADE;
  
ALTER TABLE PRODUCTCONVERSIONCPL
  ADD CONSTRAINT PROD_CONV_CPL_PLPD_ID FOREIGN KEY (PRODUCTLISTPERDISTRIBPOINT_ID)
  REFERENCES PRODUCTLISTPERDISTRIBPOINT (PRODUCTLISTPERDISTRIBPOINT_ID) ON DELETE CASCADE;
  
  

-----------------  PHASEOUTCPL   ------------------------------
CREATE TABLE PHASEOUTCPL
(
  PHASEOUTPLDP_ID NUMBER NOT NULL,
  PHASEINPLDP_ID  NUMBER NOT NULL,
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP       DATE NOT NULL
);

CREATE INDEX XIF1PHASEOUTCPL ON PHASEOUTCPL (PHASEINPLDP_ID);

ALTER TABLE PHASEOUTCPL
  ADD CONSTRAINT XPKPHASEOUTCPL PRIMARY KEY (PHASEOUTPLDP_ID);
  
ALTER TABLE PHASEOUTCPL
  ADD CONSTRAINT PHASEOUTCPL_PHASEOUTPLDP_ID FOREIGN KEY (PHASEOUTPLDP_ID)
  REFERENCES PRODUCTLISTPERDISTRIBPOINT (PRODUCTLISTPERDISTRIBPOINT_ID) ON DELETE CASCADE;
  
ALTER TABLE PHASEOUTCPL
  ADD CONSTRAINT PHASEOUTCPL_PHASEINPLDP_ID FOREIGN KEY (PHASEINPLDP_ID)
  REFERENCES PRODUCTLISTPERDISTRIBPOINT (PRODUCTLISTPERDISTRIBPOINT_ID) ON DELETE CASCADE;


-----------------  REASONCODE   ------------------------------
ALTER TABLE REASONCODE ADD (PHASEINOUTPRODUCTS VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT PHASEINOUTPRODUCTS_VALID  CHECK (PHASEINOUTPRODUCTS IN ('Y','N')));

COMMENT ON COLUMN REASONCODE.PHASEINOUTPRODUCTS
  IS 'Possible values : Y / N ';


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



-----------------  DELIVERYVARIATIONS   ------------------------------
ALTER TABLE DELIVERYVARIATIONS ADD (PRODUCTCONVERSIONCPL_ID NUMBER NULL);

CREATE INDEX XIF13DELIVERYVARIATIONS ON DELIVERYVARIATIONS (PRODUCTCONVERSIONCPL_ID);

ALTER TABLE DELIVERYVARIATIONS
  ADD CONSTRAINT DELVAR_PRODUCTCONVERSIONCPL_ID FOREIGN KEY (PRODUCTCONVERSIONCPL_ID)
  REFERENCES PRODUCTCONVERSIONCPL (PRODUCTCONVERSIONCPL_ID) ON DELETE CASCADE;








