--ABS-154506

CREATE SEQUENCE SEQ_WORKSTATIONCONTAINER
START WITH 1
INCREMENT BY 1;

--WORKSTATIONCONTAINER TABLE
CREATE TABLE WORKSTATIONCONTAINER (
WORKSTATIONCONTAINER_ID NUMBER NOT NULL,
WORKSTATION_ID          NUMBER NOT NULL,
CONTAINER_ID            NUMBER NOT NULL,
SCANDATE                DATE   NOT NULL,
TRANSACTIONTYPE_ID      NUMBER NOT NULL,
SEQUENCENUMBER          NUMBER DEFAULT 1 NOT NULL,
BUSINESSUNIT_ID         NUMBER NOT NULL,
CREATIONTIMESTAMP       DATE   NOT NULL,
CREATIONUSER_ID         NUMBER NOT NULL,
TIMESTAMP               DATE   NOT NULL,
SYSTEMUSER_ID           NUMBER NOT NULL);

ALTER TABLE WORKSTATIONCONTAINER 
      ADD CONSTRAINT XPKWORKSTATIONCONTAINER PRIMARY KEY (WORKSTATIONCONTAINER_ID);

CREATE UNIQUE INDEX XAK1WORKSTATIONCONTAINER ON WORKSTATIONCONTAINER (WORKSTATION_ID ASC, CONTAINER_ID ASC, SCANDATE ASC, TRANSACTIONTYPE_ID ASC);
      
CREATE INDEX XIF1WORKSTATIONCONTAINER ON WORKSTATIONCONTAINER (WORKSTATION_ID ASC);      
CREATE INDEX XIF2WORKSTATIONCONTAINER ON WORKSTATIONCONTAINER (CONTAINER_ID ASC);      
CREATE INDEX XIF3WORKSTATIONCONTAINER ON WORKSTATIONCONTAINER (TRANSACTIONTYPE_ID ASC);      
CREATE INDEX XIF4WORKSTATIONCONTAINER ON WORKSTATIONCONTAINER (BUSINESSUNIT_ID ASC);      

ALTER TABLE WORKSTATIONCONTAINER 
      ADD CONSTRAINT FK_WORKSTATIONCONTAINER_WORKSTATION_ID FOREIGN KEY (WORKSTATION_ID)
      REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;

ALTER TABLE WORKSTATIONCONTAINER
      ADD CONSTRAINT FK_WORKSTATIONCONTAINER_CONTAINER_ID FOREIGN KEY (CONTAINER_ID)
      REFERENCES CONTAINER (CONTAINER_ID) ON DELETE CASCADE;

ALTER TABLE WORKSTATIONCONTAINER
      ADD CONSTRAINT FK_WORKSTATIONCONTAINER_TRANSACTIONTYPE_ID FOREIGN KEY (TRANSACTIONTYPE_ID)
      REFERENCES TRANSACTIONTYPE (TRANSACTIONTYPE_ID) ON DELETE CASCADE;

ALTER TABLE WORKSTATIONCONTAINER
      ADD CONSTRAINT FK_WORKSTATIONCONTAINER_BUSINESSUNIT_ID FOREIGN KEY (BUSINESSUNIT_ID)
      REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;

--PURGETABLE 

--ValidatedBundle
INSERT INTO PURGETABLE
  (PURGETABLE_ID,
   TABLENAME,
   DESCRIPTION,
   REMARK,
   WHERECLAUSE,
   DATEFIELDNAME,
   HINT,
   PLANTFIELDNAME,
   BUSINESSUNITFIELDNAME,
   COMMITSIZE,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_PURGETABLE.NEXTVAL,
   'ValidatedBundle',
   'Validate Bundle (v10)',
   'Clean up validated bundles that have been exported',
   'EXPORTED = ‘Y’',
   'EXPORTTIMESTAMP',
   NULL,
   NULL,
   'BUSINESSUNIT_ID',
   5000,
   1,
   SYSDATE);

--ProdReplenishAdjustment
INSERT INTO PURGETABLE
  (PURGETABLE_ID,
   TABLENAME,
   DESCRIPTION,
   REMARK,
   WHERECLAUSE,
   DATEFIELDNAME,
   HINT,
   PLANTFIELDNAME,
   BUSINESSUNITFIELDNAME,
   COMMITSIZE,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_PURGETABLE.NEXTVAL,
   'ProdReplenishAdjustment',
   'Bundle Replenishment Adjustments (v10)',
   'Clean up bundle replenishments adjustments within laundry',
   NULL,
   'CREATIONTIMESTAMP',
   NULL,
   NULL,
   'BUSINESSUNIT_ID',
   5000,
   1,
   SYSDATE);

--WorkstationContainer
INSERT INTO PURGETABLE
  (PURGETABLE_ID,
   TABLENAME,
   DESCRIPTION,
   REMARK,
   WHERECLAUSE,
   DATEFIELDNAME,
   HINT,
   PLANTFIELDNAME,
   BUSINESSUNITFIELDNAME,
   COMMITSIZE,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_PURGETABLE.NEXTVAL,
   'WorkstationContainer',
   'Pack Bundle (v10)',
   'Clean up any not processed containers for a workstation',
   NULL,
   'SCANDATE',
   NULL,
   NULL,
   'BUSINESSUNIT_ID',
   5000,
   1,
   SYSDATE);
      
COMMIT;
