-- ABS-85049 / ABS-88700

-------------- BATCHFINISHINGMETHOD -----------

CREATE SEQUENCE SEQ_BATCHFINISHINGMETHOD
START WITH 1
INCREMENT BY 1;

CREATE TABLE BATCHFINISHINGMETHOD (BATCHFINISHINGMETHOD_ID NUMBER NOT NULL,
                                   BATCH_ID NUMBER NOT NULL,
                                   FINISHINGMETHOD_ID NUMBER NOT NULL,
                                   OUTSCANSTATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_BATCHFINMETHOD_OUTSCANSTAT CHECK (OUTSCANSTATUS IN (0,1,2)),
                                   WORKSTATION_ID NUMBER NULL,
                                   SYSTEMUSER_ID NUMBER NOT NULL,
                                   TIMESTAMP DATE NOT NULL);
             
COMMENT ON COLUMN BATCHFINISHINGMETHOD.OUTSCANSTATUS 
IS '0 = New / Not started
1 = In progress
2 = Completed
Default 0';

ALTER TABLE BATCHFINISHINGMETHOD ADD CONSTRAINT XPKBATCHFINISHINGMETHOD PRIMARY KEY (BATCHFINISHINGMETHOD_ID);

CREATE UNIQUE INDEX XAK1BATCHFINISHINGMETHOD ON BATCHFINISHINGMETHOD(BATCH_ID ASC, FINISHINGMETHOD_ID ASC);

CREATE INDEX XIF1BATCHFINISHINGMETHOD ON BATCHFINISHINGMETHOD (BATCH_ID ASC);
CREATE INDEX XIF2BATCHFINISHINGMETHOD ON BATCHFINISHINGMETHOD (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF3BATCHFINISHINGMETHOD ON BATCHFINISHINGMETHOD (WORKSTATION_ID ASC);

ALTER TABLE BATCHFINISHINGMETHOD ADD CONSTRAINT FK_BATCHFINMETHOD_BATCH_ID FOREIGN KEY(BATCH_ID)
REFERENCES BATCH(BATCH_ID) ON DELETE CASCADE;
ALTER TABLE BATCHFINISHINGMETHOD ADD CONSTRAINT FK_BATCHFINMETHOD_FINMETHODID FOREIGN KEY(FINISHINGMETHOD_ID)
REFERENCES FINISHINGMETHOD(FINISHINGMETHOD_ID) ON DELETE CASCADE;
ALTER TABLE BATCHFINISHINGMETHOD ADD CONSTRAINT FK_BATCHFINMETHOD_WORKSTATION FOREIGN KEY(WORKSTATION_ID)
REFERENCES WORKSTATION(WORKSTATION_ID) ON DELETE SET NULL;