-- ABS-113075/ ABS-119158

--- CUSTOMERPRODUCTCONVERSION TABLE ---
CREATE SEQUENCE SEQ_CUSTOMERPRODUCTCONVERSION
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERPRODUCTCONVERSION
(
  CUSTOMERPRODUCTCONVERSION_ID NUMBER NOT NULL,
  CUSTOMER_ID   NUMBER NOT NULL,
  FILTERON      NUMBER NOT NULL CONSTRAINT CHK_CUSTPRODUCTCONV_FILTER
                CHECK (FILTERON IN (1, 2, 3)),
  PROCESSED     VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CUSTPRODUCTCONV_PROC
                CHECK (PROCESSED IN ('Y', 'N')),
  JOBRUN_ID	NUMBER NOT NULL,
  SOURCEPRODUCT_ID NUMBER NOT NULL,
  SOURCESIZE_ID NUMBER NULL,
  DESTINATIONPRODUCT_ID NUMBER NOT NULL,
  DESTINATIONSIZE_ID NUMBER NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
)
;

COMMENT ON COLUMN CUSTOMERPRODUCTCONVERSION.FILTERON
  IS '1 = Customer
2 = Contract
3 = National Account
';

COMMENT ON COLUMN CUSTOMERPRODUCTCONVERSION.JOBRUN_ID
  IS 'All records selected at one run of the procedure will be connected to same JobRun_ID.';
	
CREATE UNIQUE INDEX XAK1CUSTOMERPRODUCTCONVERSION ON CUSTOMERPRODUCTCONVERSION(CUSTOMER_ID, SOURCEPRODUCT_ID, SOURCESIZE_ID, DESTINATIONPRODUCT_ID, DESTINATIONSIZE_ID);
CREATE INDEX XIF1CUSTOMERPRODUCTCONVERSION ON CUSTOMERPRODUCTCONVERSION(CUSTOMER_ID);
CREATE INDEX XIF2CUSTOMERPRODUCTCONVERSION ON CUSTOMERPRODUCTCONVERSION(SOURCEPRODUCT_ID);
CREATE INDEX XIF3CUSTOMERPRODUCTCONVERSION ON CUSTOMERPRODUCTCONVERSION(SOURCESIZE_ID);
CREATE INDEX XIF4CUSTOMERPRODUCTCONVERSION ON CUSTOMERPRODUCTCONVERSION(DESTINATIONPRODUCT_ID);
CREATE INDEX XIF5CUSTOMERPRODUCTCONVERSION ON CUSTOMERPRODUCTCONVERSION(DESTINATIONSIZE_ID);


ALTER TABLE CUSTOMERPRODUCTCONVERSION
  ADD CONSTRAINT XPKCUSTOMERPRODUCTCONVERSION PRIMARY KEY (CUSTOMERPRODUCTCONVERSION_ID);
  
ALTER TABLE CUSTOMERPRODUCTCONVERSION
  ADD CONSTRAINT FK_CUSTPRODUCTCONV_CUST FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
ALTER TABLE CUSTOMERPRODUCTCONVERSION
  ADD CONSTRAINT FK_CUSTPRODUCTCONV_PROD1 FOREIGN KEY (SOURCEPRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;
ALTER TABLE CUSTOMERPRODUCTCONVERSION
  ADD CONSTRAINT FK_CUSTPRODUCTCONV_SIZE1 FOREIGN KEY (SOURCESIZE_ID) REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID) ON DELETE CASCADE;
ALTER TABLE CUSTOMERPRODUCTCONVERSION
  ADD CONSTRAINT FK_CUSTPRODUCTCONV_PROD2 FOREIGN KEY (DESTINATIONPRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;
ALTER TABLE CUSTOMERPRODUCTCONVERSION
  ADD CONSTRAINT FK_CUSTPRODUCTCONV_SIZE2 FOREIGN KEY (DESTINATIONSIZE_ID) REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID) ON DELETE CASCADE;