--ABS-153625

CREATE SEQUENCE SEQ_PRODREPLENISHADJUSTMENT
START WITH 1
INCREMENT BY 1; 

--PRODREPLENISHADJUSTMENT TABLE
CREATE TABLE PRODREPLENISHADJUSTMENT(
PRODREPLENISHADJUSTMENT_ID NUMBER NOT NULL,
BUSINESSUNIT_ID            NUMBER NOT NULL,
DISTRIBUTIONPOINT_LINK_ID  NUMBER NOT NULL,
CREATIONTIMESTAMP          DATE   NOT NULL,
ORIGIN                     NUMBER DEFAULT 1 NOT NULL,
PRODUCT_LINK_ID            NUMBER NOT NULL,
SIZEDEFINITION_LINK_ID     NUMBER NOT NULL,
FINISHINGMETHOD_ID         NUMBER,
RELATEDCUSTOMER_ID         NUMBER DEFAULT NULL,
CUSTOMEROWNED              VARCHAR2(1) DEFAULT 'N' NOT NULL,
QUANTITY                   NUMBER DEFAULT 0 NOT NULL,
BUNDLEPRIMARYID            VARCHAR2(24) DEFAULT NULL,
CREATIONUSER_ID            NUMBER NOT NULL,
TIMESTAMP                  DATE NOT NULL,
SYSTEMUSER_ID              NUMBER NOT NULL);

ALTER TABLE PRODREPLENISHADJUSTMENT 
      ADD CONSTRAINT XPKPRODREPLENISHADJUSTMENT PRIMARY KEY (PRODREPLENISHADJUSTMENT_ID);
      
COMMENT ON COLUMN PRODREPLENISHADJUSTMENT.ORIGIN IS '1 = Rejected bundle, 2 = Validated bundle, 3 = Replacement bundle';

CREATE UNIQUE INDEX XAK1PRODREPLENISHADJUSTMENT ON PRODREPLENISHADJUSTMENT (BUSINESSUNIT_ID ASC, DISTRIBUTIONPOINT_LINK_ID ASC, CREATIONTIMESTAMP ASC, ORIGIN ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC, RELATEDCUSTOMER_ID ASC, CUSTOMEROWNED ASC);
CREATE UNIQUE INDEX XAK2PRODREPLENISHADJUSTMENT ON PRODREPLENISHADJUSTMENT (BUSINESSUNIT_ID ASC, DISTRIBUTIONPOINT_LINK_ID ASC, TRUNC(CREATIONTIMESTAMP) ASC, ORIGIN ASC, BUNDLEPRIMARYID ASC);

CREATE INDEX XIF1PRODREPLENISHADJUSTMENT ON PRODREPLENISHADJUSTMENT (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2PRODREPLENISHADJUSTMENT ON PRODREPLENISHADJUSTMENT (DISTRIBUTIONPOINT_LINK_ID ASC);
CREATE INDEX XIF3PRODREPLENISHADJUSTMENT ON PRODREPLENISHADJUSTMENT (PRODUCT_LINK_ID ASC );
CREATE INDEX XIF4PRODREPLENISHADJUSTMENT ON PRODREPLENISHADJUSTMENT (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF5PRODREPLENISHADJUSTMENT ON PRODREPLENISHADJUSTMENT (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF6PRODREPLENISHADJUSTMENT ON PRODREPLENISHADJUSTMENT (RELATEDCUSTOMER_ID ASC);

ALTER TABLE PRODREPLENISHADJUSTMENT
      ADD CONSTRAINT FK_PRODREPLADJ_BUSINESSUNIT_ID FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
      
ALTER TABLE PRODREPLENISHADJUSTMENT
      ADD CONSTRAINT FK_PRODREPLADJ_DISTRIBUTIONPOINT_LINK_ID FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID) REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
      
ALTER TABLE PRODREPLENISHADJUSTMENT
      ADD CONSTRAINT FK_PRODREPLADJ_PRODUCT_LINK_ID FOREIGN KEY (PRODUCT_LINK_ID) REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);

ALTER TABLE PRODREPLENISHADJUSTMENT
      ADD CONSTRAINT FK_PRODREPLADJ_SIZEDEFINITION_LINK_ID FOREIGN KEY (SIZEDEFINITION_LINK_ID) REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);

ALTER TABLE PRODREPLENISHADJUSTMENT
      ADD CONSTRAINT FK_PRODREPLADJ_FINISHINGMETHOD_ID FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
      
ALTER TABLE PRODREPLENISHADJUSTMENT
      ADD CONSTRAINT FK_PRODREPLADJ_RELATEDCUSTOMER_ID FOREIGN KEY (RELATEDCUSTOMER_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID) ON DELETE CASCADE;
      
ALTER TABLE PRODREPLENISHADJUSTMENT
      ADD CONSTRAINT YN_VALIDCUSTOMEROWNED CHECK (CUSTOMEROWNED IN ('Y', 'N'));

ALTER TABLE PRODREPLENISHADJUSTMENT
      ADD CONSTRAINT CHK_ORIGIN CHECK (ORIGIN IN (1, 2, 3));
