--ABS-153411

CREATE SEQUENCE SEQ_VALIDATEDBUNDLE
START WITH 1
INCREMENT BY 1;

--VALIDATEDBUNDLE TABLE
CREATE TABLE VALIDATEDBUNDLE(
VALIDATEDBUNDLE_ID               NUMBER NOT NULL,
BUSINESSUNIT_ID                  NUMBER NOT NULL,
PRIMARYID                        VARCHAR2(24) NOT NULL,
DISTRIBUTIONPOINT_LINK_ID        NUMBER NOT NULL,
RELATEDCUSTOMER_ID               NUMBER DEFAULT NULL NULL,
CUSTOMEROWNED                    VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_VALIDATEDBUNDLE_CUSTOMEROWNED CHECK (CUSTOMEROWNED IN ('Y', 'N')),
PRODUCT_LINK_ID                  NUMBER NOT NULL,
SIZEDEFINITION_LINK_ID           NUMBER NOT NULL,
FINISHINGMETHOD_ID               NUMBER DEFAULT NULL NULL,
QTYREJECTED                      NUMBER NOT NULL,
ORIGINALBUNDLEQTY                NUMBER NOT NULL,
ORIGINALREJECTIONSTATUS_ID       NUMBER NOT NULL,
EXPORTED                         VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_VALIDATEDBUNDLE_EXPORTED CHECK (EXPORTED IN ('Y', 'N')),
EXPORTTIMESTAMP                  DATE DEFAULT NULL NULL,
PLANTDEPARTMENTMACHINE_ID        NUMBER DEFAULT NULL NULL,
MACHINEOPERATOR_ID               NUMBER DEFAULT NULL NULL,
CREATIONTIMESTAMP                DATE NOT NULL,
CREATIONUSER_ID                  NUMBER NOT NULL,
TIMESTAMP                        DATE NOT NULL,
SYSTEMUSER_ID                    NUMBER NOT NULL);

ALTER TABLE VALIDATEDBUNDLE
      ADD CONSTRAINT XPKVALIDATEDBUNDLE PRIMARY KEY (VALIDATEDBUNDLE_ID);

CREATE UNIQUE INDEX XAK1VALIDATEDBUNDLE ON VALIDATEDBUNDLE (BUSINESSUNIT_ID ASC, PRIMARYID ASC);

CREATE INDEX XIF1VALIDATEDBUNDLE ON VALIDATEDBUNDLE (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2VALIDATEDBUNDLE ON VALIDATEDBUNDLE (DISTRIBUTIONPOINT_LINK_ID ASC);
CREATE INDEX XIF3VALIDATEDBUNDLE ON VALIDATEDBUNDLE (RELATEDCUSTOMER_ID ASC);
CREATE INDEX XIF4VALIDATEDBUNDLE ON VALIDATEDBUNDLE (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF5VALIDATEDBUNDLE ON VALIDATEDBUNDLE (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF6VALIDATEDBUNDLE ON VALIDATEDBUNDLE (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF7VALIDATEDBUNDLE ON VALIDATEDBUNDLE (ORIGINALREJECTIONSTATUS_ID ASC);
CREATE INDEX XIF8VALIDATEDBUNDLE ON VALIDATEDBUNDLE (PLANTDEPARTMENTMACHINE_ID ASC);

CREATE INDEX XIE1VALIDATEDBUNDLE ON VALIDATEDBUNDLE (BUSINESSUNIT_ID ASC, DISTRIBUTIONPOINT_LINK_ID ASC);
CREATE INDEX XIE2VALIDATEDBUNDLE ON VALIDATEDBUNDLE (BUSINESSUNIT_ID ASC, EXPORTED ASC);

ALTER TABLE VALIDATEDBUNDLE
      ADD CONSTRAINT FK_VALBNDL_BUSINESSUNITID FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
      
ALTER TABLE VALIDATEDBUNDLE
      ADD CONSTRAINT FK_VALBNDL_DISTRIBUTIONPOINTLINK FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID) REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
      
ALTER TABLE VALIDATEDBUNDLE
      ADD CONSTRAINT FK_VALBNDL_RELATEDCUSTOMERID FOREIGN KEY (RELATEDCUSTOMER_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID) ON DELETE CASCADE;
      
ALTER TABLE VALIDATEDBUNDLE
      ADD CONSTRAINT FK_VALBNDL_PRODUCTLINK FOREIGN KEY (PRODUCT_LINK_ID) REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
      
ALTER TABLE VALIDATEDBUNDLE
      ADD CONSTRAINT FK_VALBNDL_SIZEDEFINITIONLINK FOREIGN KEY (SIZEDEFINITION_LINK_ID) REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
      
ALTER TABLE VALIDATEDBUNDLE
      ADD CONSTRAINT FK_VALBNDL_FINISHINGMETHODID FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
      
ALTER TABLE VALIDATEDBUNDLE
      ADD CONSTRAINT FK_VALBNDL_ORIGINALREJECTIONSTATUSID FOREIGN KEY (ORIGINALREJECTIONSTATUS_ID) REFERENCES STATUS (STATUS_ID);
            
ALTER TABLE VALIDATEDBUNDLE
      ADD CONSTRAINT FK_VALBNDL_PLANTDEPARTMENTMACHINEID FOREIGN KEY (PLANTDEPARTMENTMACHINE_ID) REFERENCES PLANTDEPARTMENTMACHINE (PLANTDEPARTMENTMACHINE_ID);      
      
            
