-- ABS-11734 / ABS-47584

--------------- SOILCOUNT ---------------

ALTER TABLE SOILCOUNT ADD (REFERENCENUMBER NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN SOILCOUNT.REFERENCENUMBER
  IS 'Referencenumber can be used to split soil counts on the same day and business unit and distribution points. when not used always set to 0.
Used in clean return exchange carts.';

DROP INDEX XAK1SOILCOUNT;
CREATE UNIQUE INDEX XAK1SOILCOUNT ON SOILCOUNT (DISTRIBUTIONPOINT_LINK_ID ASC, COUNTDATE ASC, PROCESSBUSINESSUNIT_ID ASC, BAGTAG_ID ASC, REFERENCENUMBER ASC);
  
CREATE INDEX XIE2SOILCOUNT ON SOILCOUNT (REFERENCENUMBER ASC);

--------------- UNIQUEITEM ---------------

ALTER TABLE UNIQUEITEM ADD (EXCHANGECART_ID NUMBER NULL);

CREATE INDEX XIF21UNIQUEITEM ON UNIQUEITEM (EXCHANGECART_ID ASC);

ALTER TABLE UNIQUEITEM
  ADD CONSTRAINT UNIQUEITEM_EXCHANGECART FOREIGN KEY (EXCHANGECART_ID)
  REFERENCES EXCHANGECART (EXCHANGECART_ID) ON DELETE SET NULL;
  
--------------- WEIGHINGTICKET ---------------

COMMENT ON COLUMN WEIGHINGTICKET.ORIGIN
  IS '1= Standard Weighing Ticket
2= Weight Correction Ticket
3 = Clean Return Ticket.';

ALTER TABLE WEIGHINGTICKET
  ADD CONSTRAINT ORIGIN_VALID10_1
  CHECK (ORIGIN IN (1,2,3));

--------------- CHIPPINGRATE ---------------

CREATE SEQUENCE SEQ_CHIPPINGRATE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CHIPPINGRATE
(
  CHIPPINGRATE_ID        NUMBER NOT NULL,
  BUSINESSUNIT_ID        NUMBER NOT NULL,
  YEAR                   NUMBER NOT NULL,
  WEEK                   NUMBER NOT NULL CONSTRAINT WEEK_VALID1_2
                                CHECK (WEEK BETWEEN 1 AND 53),
  PRODUCT_LINK_ID        NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
  FINISHINGMETHOD_ID     NUMBER NOT NULL,
  CHIPPINGRATE           NUMBER NOT NULL CONSTRAINT CHIPPINGRATE_VALID1_2
                                CHECK (CHIPPINGRATE BETWEEN 0 AND 100),
  TOTALDELIVERED         NUMBER NOT NULL,
  TOTALSCANNED           NUMBER NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
);

ALTER TABLE CHIPPINGRATE
  ADD CONSTRAINT XPKCHIPPINGRATE PRIMARY KEY (CHIPPINGRATE_ID);

CREATE UNIQUE INDEX XAK1CHIPPINGRATE ON CHIPPINGRATE (BUSINESSUNIT_ID ASC, YEAR ASC, WEEK ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF1CHIPPINGRATE ON CHIPPINGRATE (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2CHIPPINGRATE ON CHIPPINGRATE (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF3CHIPPINGRATE ON CHIPPINGRATE (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF4CHIPPINGRATE ON CHIPPINGRATE (FINISHINGMETHOD_ID ASC);


ALTER TABLE CHIPPINGRATE
  ADD CONSTRAINT CHIPPINGRATE_BUSINESSUNIT FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;
ALTER TABLE CHIPPINGRATE
  ADD CONSTRAINT CHIPPINGRATE_PRODUCT_LINK FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
ALTER TABLE CHIPPINGRATE
  ADD CONSTRAINT CHIPPINGRATE_SIZEDEF FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
ALTER TABLE CHIPPINGRATE
  ADD CONSTRAINT CHIPPINGRATE_FINISHINGMETHOD FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
