--ABS-6146

CREATE SEQUENCE SEQ_INCIDENTALINVENTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE INCIDENTALINVENTORY
(
INCIDENTALINVENTORY_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
INVENTORYDATE DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
DISTRIBUTIONPOINT_ID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
PRODUCTLISTPERDISTRIBPOINT_ID NUMBER NULL,
STANDARDDELIVERYLINE_ID NUMBER NULL,
AVERAGE NUMBER DEFAULT 0 NOT NULL,
COUNTED NUMBER DEFAULT 0 NOT NULL,
AVERAGEWITHFACTOR NUMBER DEFAULT 0 NOT NULL,
NUMBEROFDELIVERIES NUMBER DEFAULT 0 NOT NULL,
NUMBEROFDELIVERIESSTOPPED NUMBER DEFAULT 0 NOT NULL,
MINSTOCKQTY NUMBER DEFAULT 0 NOT NULL,
STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT INCIDENTALINVSTATUS_VALID1_1 CHECK (STATUS BETWEEN 0 AND 2),
ONSITEADJUSTMENT NUMBER DEFAULT 0 NOT NULL,
STARTDATE DATE NULL,
ENDDATE DATE NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN INCIDENTALINVENTORY.STATUS
  IS '0 = New
1 = Processed
2 = Error';
  
ALTER TABLE INCIDENTALINVENTORY ADD CONSTRAINT XPKINCIDENTALINVENTORY PRIMARY KEY (INCIDENTALINVENTORY_ID);

CREATE UNIQUE INDEX XAK1INCIDENTALINVENTORY ON INCIDENTALINVENTORY (CUSTOMER_ID ASC, INVENTORYDATE ASC, DISTRIBUTIONPOINT_ID ASC, PRODUCT_ID ASC);

CREATE INDEX XIE1INCIDENTALINVENTORY ON INCIDENTALINVENTORY (CUSTOMER_ID ASC, STATUS ASC);

CREATE INDEX XIF1INCIDENTALINVENTORY ON INCIDENTALINVENTORY (CUSTOMER_ID ASC);

CREATE INDEX XIF2INCIDENTALINVENTORY ON INCIDENTALINVENTORY (DISTRIBUTIONPOINT_ID ASC);

CREATE INDEX XIF3INCIDENTALINVENTORY ON INCIDENTALINVENTORY (PRODUCT_ID ASC);

CREATE INDEX XIF4INCIDENTALINVENTORY ON INCIDENTALINVENTORY (PRODUCTLISTPERDISTRIBPOINT_ID ASC);

CREATE INDEX XIF5INCIDENTALINVENTORY ON INCIDENTALINVENTORY (STANDARDDELIVERYLINE_ID ASC);

ALTER TABLE INCIDENTALINVENTORY ADD CONSTRAINT INCIDENTALINVENTORY_CUSTID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE INCIDENTALINVENTORY ADD CONSTRAINT INCIDENTALINVENTORY_DP FOREIGN KEY (DISTRIBUTIONPOINT_ID)
REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID) ON DELETE CASCADE;

ALTER TABLE INCIDENTALINVENTORY ADD CONSTRAINT INCIDENTALINVENTORY_PROD FOREIGN KEY (PRODUCT_ID)
REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;

ALTER TABLE INCIDENTALINVENTORY ADD CONSTRAINT INCIDENTALINVENTORY_PLPD FOREIGN KEY (PRODUCTLISTPERDISTRIBPOINT_ID)
REFERENCES PRODUCTLISTPERDISTRIBPOINT (PRODUCTLISTPERDISTRIBPOINT_ID) ON DELETE SET NULL;

ALTER TABLE INCIDENTALINVENTORY ADD CONSTRAINT INCIDENTALINVENTORY_SDL FOREIGN KEY (STANDARDDELIVERYLINE_ID)
REFERENCES STANDARDDELIVERYLINE (STANDARDDELIVERYLINE_ID) ON DELETE SET NULL;

