--- ABS-13966
-------------------------   BUNDLESORTBOX -----------------------------------
CREATE SEQUENCE SEQ_BUNDLESORTBOX
START WITH 1
INCREMENT BY 1;

CREATE TABLE BUNDLESORTBOX (BUNDLESORTBOX_ID NUMBER NOT NULL,
			              WORKSTATION_ID NUMBER NOT NULL,
			              BOXNUMBER NUMBER NOT NULL CONSTRAINT BOXNUMBER_VALID1_2 CHECK (BOXNUMBER BETWEEN 1 AND 999),
			              PRODUCT_ID NUMBER NOT NULL,
			              SIZEDEFINITION_ID NUMBER NOT NULL,
			              CUSTOMER_ID NUMBER NULL,
			              SYSTEMUSER_ID NUMBER NOT NULL,
			             TIMESTAMP DATE NOT NULL
				);

ALTER TABLE BUNDLESORTBOX ADD CONSTRAINT XPKBUNDLESORTBOX PRIMARY KEY (BUNDLESORTBOX_ID);

CREATE UNIQUE INDEX XAK1BUNDLESORTBOX ON BUNDLESORTBOX (WORKSTATION_ID ASC, BOXNUMBER ASC);

CREATE INDEX XIE1BUNDLESORTBOX ON BUNDLESORTBOX (WORKSTATION_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, CUSTOMER_ID ASC);

CREATE INDEX XIF1BUNDLESORTBOX ON BUNDLESORTBOX (WORKSTATION_ID ASC);

CREATE INDEX XIF2BUNDLESORTBOX ON BUNDLESORTBOX (PRODUCT_ID ASC);

CREATE INDEX XIF3BUNDLESORTBOX ON BUNDLESORTBOX (SIZEDEFINITION_ID ASC);

CREATE INDEX XIF4BUNDLESORTBOX ON BUNDLESORTBOX (CUSTOMER_ID ASC);

ALTER TABLE BUNDLESORTBOX ADD CONSTRAINT BUNDLESORTBOX_WS FOREIGN KEY (WORKSTATION_ID)
REFERENCES WORKSTATION (WORKSTATION_ID);

ALTER TABLE BUNDLESORTBOX ADD CONSTRAINT BUNDLESORTBOX_PRODUCT FOREIGN KEY (PRODUCT_ID)
REFERENCES PRODUCT (PRODUCT_ID);

ALTER TABLE BUNDLESORTBOX ADD CONSTRAINT BUNDLESORTBOX_SIZEDEF FOREIGN KEY (SIZEDEFINITION_ID)
REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

ALTER TABLE BUNDLESORTBOX ADD CONSTRAINT BUNDLESORTBOX_CUSTOMER FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID);

-------------------------   BUNDLESORTBOXCONTENTS -----------------------------------
CREATE TABLE BUNDLESORTBOXCONTENTS (BUNDLESORTBOX_ID NUMBER NOT NULL,
			              UNIQUEITEM_ID NUMBER NOT NULL,
			              SEQUENCENUMBER NUMBER NOT NULL CONSTRAINT SEQUENCENUMBER_VALID1_1 CHECK (SEQUENCENUMBER BETWEEN 1 AND 9999),
			             SYSTEMUSER_ID NUMBER NOT NULL,
			             TIMESTAMP DATE NOT NULL
				);

ALTER TABLE BUNDLESORTBOXCONTENTS ADD CONSTRAINT XPKBUNDLESORTBOXCONTENTS PRIMARY KEY (BUNDLESORTBOX_ID, UNIQUEITEM_ID);

CREATE UNIQUE INDEX XAK1BUNDLESORTBOXCONTENTS ON BUNDLESORTBOXCONTENTS(BUNDLESORTBOX_ID ASC, SEQUENCENUMBER ASC);

CREATE INDEX XIF1BUNDLESORTBOXCONTENTS ON BUNDLESORTBOXCONTENTS (BUNDLESORTBOX_ID ASC);

CREATE INDEX XIF2BUNDLESORTBOXCONTENTS ON BUNDLESORTBOXCONTENTS (UNIQUEITEM_ID ASC);

ALTER TABLE BUNDLESORTBOXCONTENTS ADD CONSTRAINT BUNDLESORTBOXCONT_BUNDLESORTBX FOREIGN KEY (BUNDLESORTBOX_ID)
REFERENCES BUNDLESORTBOX(BUNDLESORTBOX_ID) ON DELETE CASCADE;

ALTER TABLE BUNDLESORTBOXCONTENTS ADD CONSTRAINT BUNDLESORTBOXCONT_UNIQUEITEM FOREIGN KEY (UNIQUEITEM_ID)
REFERENCES UNIQUEITEM (UNIQUEITEM_ID) ON DELETE CASCADE;

-------------------------   PRODUCTGROUP-----------------------------------
ALTER TABLE PRODUCTGROUP ADD (ALLOWBUNDLESORT VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD272 CHECK (ALLOWBUNDLESORT IN ('Y','N')));


-------------------------   BUSINESSUNIT-----------------------------------
ALTER TABLE BUSINESSUNIT ADD (BUNDLESORTLABELLAYOUT_ID NUMBER NULL);


CREATE INDEX XIF12BUSINESSUNIT ON BUSINESSUNIT (BUNDLESORTLABELLAYOUT_ID ASC);

ALTER TABLE BUSINESSUNIT ADD CONSTRAINT BUSINESSUNIT_LABELLAYOUT FOREIGN KEY (BUNDLESORTLABELLAYOUT_ID)
REFERENCES LABELLAYOUT(LABELLAYOUT_ID);

-------------------------   TRANSACTIONTYPE-----------------------------------
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 37,
         'In Circ via Bundle Sort Scan',
         getminsystemuser(),
         sysdate
    from dual;
commit;

-------------------------   REASONCODEDIALOGS-----------------------------------
INSERT INTO REASONCODEDIALOGS
  (REASONCODE_ID, DIALOG_ID, SELECTABLE, SYSTEMUSER_ID, TIMESTAMP)
  SELECT R.REASONCODE_ID,
         D.DIALOG_ID,
         'Y',
         GETMINSYSTEMUSER(),
         GETCURRENTDATETIME()
    FROM REASONCODE R,
         DIALOGS    D WHERE D.CODE = 'BundleSortScan';
commit;