-- ABS-56106
CREATE SEQUENCE SEQ_PRICEUPDATEHISTORY 
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRICEUPDATEHISTORY
( PRICEUPDATEHISTORY_ID   NUMBER NOT NULL,
  ABSLOGPROCESSJOBLINE_ID NUMBER NOT NULL,
  CHAIN_ID                NUMBER NULL,
  CUSTOMER_LINK_ID        NUMBER NULL,
  PRODUCT_LINK_ID         NUMBER NULL,
  SIZEDEFINITION_LINK_ID  NUMBER NULL,
  WEIGHINGCATEGORY_ID     NUMBER NULL,
  SPECIALCHARGE_ID        NUMBER NULL,
  REPAIRTYPE_ID           NUMBER NULL,
  OLDPRICE                NUMBER NULL,
  NEWPRICE                NUMBER NULL,
  PERCENTAGE              NUMBER NULL,
  ADJUSTMENTTYPE          NUMBER DEFAULT 1 NOT NULL CONSTRAINT ADJUSTMENTTYPE_VALID1_2
                                                    CHECK (ADJUSTMENTTYPE IN (1,2,3,4)),
  RUNTYPE                 NUMBER DEFAULT 0 NOT NULL CONSTRAINT RUNTYPE_VALID1_1
                                                    CHECK (RUNTYPE IN (0,1)),
  STARTDATEPRICEUPDATE    DATE NULL,
  OLDPRICEINDEXCODE       VARCHAR2(6) NULL,
  NEWPRICEINDEXCODE       VARCHAR2(6) NULL,
  OLDLOCKDATE             DATE NULL,
  NEWLOCKDATE             DATE NULL,
  UNDOPRICEUPDATEFORDATE  DATE NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL);

COMMENT ON COLUMN PRICEUPDATEHISTORY.ADJUSTMENTTYPE
  IS 'Possible values:
1 = Price update
2 = Undo price update
3 = Related task
4 = All';

COMMENT ON COLUMN PRICEUPDATEHISTORY.RUNTYPE
  IS 'Possible values:
0 = Test run
1 = Final run';

ALTER TABLE PRICEUPDATEHISTORY
  ADD CONSTRAINT XPKPRICEUPDATEHISTORY PRIMARY KEY (PRICEUPDATEHISTORY_ID);

CREATE UNIQUE INDEX XAK1PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (ABSLOGPROCESSJOBLINE_ID ASC);

CREATE INDEX XIE1PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (ADJUSTMENTTYPE ASC);
CREATE INDEX XIE2PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (RUNTYPE ASC);
CREATE INDEX XIE3PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (OLDPRICEINDEXCODE ASC);
CREATE INDEX XIE4PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (NEWPRICEINDEXCODE ASC);
CREATE INDEX XIE5PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (PERCENTAGE ASC);
CREATE INDEX XIE6PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (STARTDATEPRICEUPDATE ASC);
CREATE INDEX XIE7PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (UNDOPRICEUPDATEFORDATE ASC);

CREATE INDEX XIF1PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (CHAIN_ID ASC);
CREATE INDEX XIF2PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (CUSTOMER_LINK_ID ASC);
CREATE INDEX XIF3PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF4PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF5PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (WEIGHINGCATEGORY_ID ASC);
CREATE INDEX XIF6PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (SPECIALCHARGE_ID ASC);
CREATE INDEX XIF7PRICEUPDATEHISTORY ON PRICEUPDATEHISTORY (REPAIRTYPE_ID ASC);

ALTER TABLE PRICEUPDATEHISTORY
  ADD CONSTRAINT PRICEUPDATEHISTORY_ABSLOGPROC FOREIGN KEY (ABSLOGPROCESSJOBLINE_ID)
  REFERENCES ABSLOGPROCESSJOBLINE (ABSLOGPROCESSJOBLINE_ID) ON DELETE CASCADE;
ALTER TABLE PRICEUPDATEHISTORY
  ADD CONSTRAINT PRICEUPDATEHISTORY_CHAIN FOREIGN KEY (CHAIN_ID)
  REFERENCES CHAIN (CHAIN_ID) ON DELETE SET NULL;
ALTER TABLE PRICEUPDATEHISTORY
  ADD CONSTRAINT PRICEUPDATEHISTORY_CUSTLINK FOREIGN KEY (CUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID) ON DELETE SET NULL;
ALTER TABLE PRICEUPDATEHISTORY
  ADD CONSTRAINT PRICEUPDATEHISTORY_PRODLINK FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID) ON DELETE SET NULL;
ALTER TABLE PRICEUPDATEHISTORY
  ADD CONSTRAINT PRICEUPDATEHISTORY_SIZELINK FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID) ON DELETE SET NULL;
ALTER TABLE PRICEUPDATEHISTORY
  ADD CONSTRAINT PRICEUPDATEHISTORY_WEIGHCAT FOREIGN KEY (WEIGHINGCATEGORY_ID)
  REFERENCES WEIGHINGCATEGORY (WEIGHINGCATEGORY_ID) ON DELETE SET NULL;
ALTER TABLE PRICEUPDATEHISTORY
  ADD CONSTRAINT PRICEUPDATEHISTORY_SPECIALCH FOREIGN KEY (SPECIALCHARGE_ID)
  REFERENCES SPECIALCHARGE (SPECIALCHARGE_ID) ON DELETE SET NULL;
ALTER TABLE PRICEUPDATEHISTORY
  ADD CONSTRAINT PRICEUPDATEHISTORY_REPAIRTYPE FOREIGN KEY (REPAIRTYPE_ID)
  REFERENCES REPAIRTYPE (REPAIRTYPE_ID) ON DELETE SET NULL;
