/*order 502470*/

ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (IMAGECARE NUMBER(1) DEFAULT 0 NOT NULL);

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.IMAGECARE IS '0= Not charged / not applicable
1= For Repairs
2= For Loss charges
3= For Repairs and Loss charges
4= For Residual Value
5= For Repair and Residual Value
6= For Residual Value and Loss Charge
7= For Repair Loss Charge and Residual Value';

CREATE SEQUENCE SEQ_CUSTOMERIMAGECAREPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERIMAGECAREPRICE (
       CUSTOMERIMAGECAREPRICE_ID  NUMBER NOT NULL,
       CUSTOMER_ID                NUMBER NOT NULL,
       PRODUCT_ID                 NUMBER NOT NULL,
       SIZEDEFINITION_ID          NUMBER NOT NULL,
       STARTDATE                  DATE NOT NULL,
       PRICE			  NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1CUSTOMERIMAGECAREPRICE ON CUSTOMERIMAGECAREPRICE
(
       CUSTOMER_ID                     ASC
);

CREATE INDEX XIF2CUSTOMERIMAGECAREPRICE ON CUSTOMERIMAGECAREPRICE
(
       PRODUCT_ID                       ASC
);

CREATE INDEX XIF3CUSTOMERIMAGECAREPRICE ON CUSTOMERIMAGECAREPRICE
(
       SIZEDEFINITION_ID                ASC
);

CREATE UNIQUE INDEX XPKCUSTOMERIMAGECAREPRICE ON CUSTOMERIMAGECAREPRICE 
(
       CUSTOMERIMAGECAREPRICE_ID        ASC
);

CREATE UNIQUE INDEX XAK1CUSTOMERIMAGECAREPRICE ON CUSTOMERIMAGECAREPRICE 
(
        CUSTOMER_ID        ASC,
        PRODUCT_ID	   ASC,
	SIZEDEFINITION_ID   ASC,
	STARTDATE	   ASC
);

ALTER TABLE CUSTOMERIMAGECAREPRICE
       ADD  ( CONSTRAINT XPKCUSTOMERIMAGECAREPRICE PRIMARY KEY (
              CUSTOMERIMAGECAREPRICE_ID) ) ;

ALTER TABLE CUSTOMERIMAGECAREPRICE
       ADD  ( CONSTRAINT CUSTOMERIMAGECAREPRICE_SIZEDEF
              FOREIGN KEY (SIZEDEFINITION_ID)
                             REFERENCES SIZEDEFINITION ) ;

ALTER TABLE CUSTOMERIMAGECAREPRICE
       ADD  ( CONSTRAINT CUSTOMERIMAGECAREPRICE_PRODUCT
              FOREIGN KEY (PRODUCT_ID)
                             REFERENCES PRODUCT ) ;

ALTER TABLE CUSTOMERIMAGECAREPRICE
       ADD  ( CONSTRAINT CUSTOMERIMAGECAREPRICE_CUSTOME
              FOREIGN KEY (CUSTOMER_ID)
                             REFERENCES CUSTOMER 
			     ON DELETE CASCADE  ) ;

