--Order 20015680.40


--CUSTOMER table
ALTER TABLE CUSTOMER ADD (INSURANCESTARTDATE DATE NULL, 
                          INVWEARERBREAKDOWN NUMBER(1) NULL CONSTRAINT INVWEARERBREAKDOWN_VALID CHECK (INVWEARERBREAKDOWN IN (0, 1)));
     
 
 
-- GENERALPRICELIST table
ALTER TABLE GENERALPRICELIST ADD (DEFAULTPRICELIST VARCHAR2(1) NULL CONSTRAINT DEFAULTPRICELIST_VALID CHECK (DEFAULTPRICELIST IN ('Y', 'N')));



-- CUSTOMEREASYCAREPRICE table
CREATE SEQUENCE SEQ_CUSTOMEREASYCAREPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMEREASYCAREPRICE
(
  CUSTOMEREASYCAREPRICE_ID  NUMBER NOT NULL,
  PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
  PRICE                     NUMBER(15,6) NOT NULL,
  STARTDATE                 DATE NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
);

ALTER TABLE CUSTOMEREASYCAREPRICE
ADD CONSTRAINT  XPKCUSTOMEREASYCAREPRICE PRIMARY KEY (CUSTOMEREASYCAREPRICE_ID);

CREATE INDEX XIF1CUSTOMEREASYCAREPRICE ON CUSTOMEREASYCAREPRICE
(PRODUCTLISTPERCUSTOMER_ID   ASC);


CREATE UNIQUE INDEX XAK1CUSTOMEREASYCAREPRICE ON CUSTOMEREASYCAREPRICE
(PRODUCTLISTPERCUSTOMER_ID   ASC, STARTDATE ASC);


ALTER TABLE CUSTOMEREASYCAREPRICE
ADD CONSTRAINT CUSTOMEREASYCAREPRICE_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID) REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;



-- REASONCODE table
ALTER TABLE REASONCODE ADD (CHARGERESIDUALINSURANCE NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHARGERESIDUALINSURANCE_valid CHECK (CHARGERESIDUALINSURANCE IN (0,1)));



-- RAGREASON table
ALTER TABLE RAGREASON ADD (CHARGERESIDUALINSURANCE NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHARGERESIDUALINSRAG_valid CHECK (CHARGERESIDUALINSURANCE IN (0,1)));


CREATE TABLE RAGREASONDIALOGS
(
  RAGREASON_ID        NUMBER NOT NULL ,
  DIALOG_ID            NUMBER NOT NULL ,
  SELECTABLE           VARCHAR2(1) DEFAULT  'Y'  NOT NULL  CONSTRAINT  YN_VALIDATIONFIELD_6 CHECK (SELECTABLE IN ('Y', 'N')),
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

CREATE SEQUENCE SEQ_RAGREASONDIALOGS
START WITH 1
INCREMENT BY 1;

CREATE  INDEX XIF1RAGREASONDIALOGS ON RAGREASONDIALOGS
(DIALOG_ID   ASC);

CREATE  INDEX XIF2RAGREASONDIALOGS ON RAGREASONDIALOGS
(RAGREASON_ID   ASC);

ALTER TABLE RAGREASONDIALOGS
ADD CONSTRAINT  XPKRAGREASONDIALOGS PRIMARY KEY (RAGREASON_ID,DIALOG_ID);

ALTER TABLE RAGREASONDIALOGS
ADD CONSTRAINT RAGREASONDIALOGS_DIALOGS FOREIGN KEY (DIALOG_ID) REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;

ALTER TABLE RAGREASONDIALOGS
ADD CONSTRAINT RAGREASONDIALOGS_RAGREASON FOREIGN KEY (RAGREASON_ID) REFERENCES RAGREASON (RAGREASON_ID) ON DELETE CASCADE;