-- ABS-150539 Temp table for Customer selection Create Bundle

CREATE SEQUENCE SEQ_PRODUCTIONQUEUE
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRODUCTIONQUEUE (
  PRODUCTIONQUEUE_ID NUMBER NOT NULL,
  PLANTDEPARTMENTMACHINE_ID NUMBER NULL,
  PRODUCTIONCUSTOMER_ID NUMBER NOT NULL,
  PROCESSSTATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_PRODUCTIONQUEUE_PROCESSSTATUS CHECK (PROCESSSTATUS IN (0, 1, 2)),
  PREVPRODUCTIONQUEUE_ID NUMBER NULL,
  CREATIONUSER_ID NUMBER NOT NULL,
  CREATIONTIMESTAMP DATE NOT NULL,
  STARTPROCESSTIMESTAMP DATE NULL,
  ENDPROCESSTIMESTAMP DATE NULL,
  TIMESTAMP DATE NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL
);

COMMENT ON COLUMN PRODUCTIONQUEUE.PROCESSSTATUS IS
'0 = Created
 1 = In progress
 2 = Processed';

ALTER TABLE PRODUCTIONQUEUE 
ADD CONSTRAINT XPKPRODUCTIONQUEUE PRIMARY KEY (PRODUCTIONQUEUE_ID);

CREATE UNIQUE INDEX XAK1PRODUCTIONQUEUE ON PRODUCTIONQUEUE(PLANTDEPARTMENTMACHINE_ID, CREATIONTIMESTAMP);
CREATE INDEX XIE1PRODUCTIONQUEUE ON PRODUCTIONQUEUE(NVL(PREVPRODUCTIONQUEUE_ID,0));
CREATE INDEX XIF1PRODUCTIONQUEUE ON PRODUCTIONQUEUE(PLANTDEPARTMENTMACHINE_ID);
CREATE INDEX XIF2PRODUCTIONQUEUE ON PRODUCTIONQUEUE(PRODUCTIONCUSTOMER_ID);

ALTER TABLE PRODUCTIONQUEUE
ADD CONSTRAINT FK_PRODUCTIONQUEUE_PLANTDEPARTMENTMACHINEID FOREIGN KEY (PLANTDEPARTMENTMACHINE_ID) REFERENCES PLANTDEPARTMENTMACHINE (PLANTDEPARTMENTMACHINE_ID) ON DELETE CASCADE;

ALTER TABLE PRODUCTIONQUEUE
ADD CONSTRAINT FK_PRODUCTIONQUEUE_PRODUCTIONCUSTOMERID FOREIGN KEY (PRODUCTIONCUSTOMER_ID) REFERENCES PRODUCTIONCUSTOMER (PRODUCTIONCUSTOMER_ID) ON DELETE CASCADE;