--ABS-25834/ ABS-48322

---SOUNDCONFIGURATION table---
INSERT INTO SOUNDCONFIGURATION
  (SELECT SEQ_SOUNDCONFIGURATION.NEXTVAL,
         'Miscellaneous',
         'Right Rail Button pressed',
         GETMINSYSTEMUSER(),
         SYSDATE,
         NULL
    FROM DUAL );
COMMIT;

INSERT INTO SOUNDCONFIGURATION
  (SELECT SEQ_SOUNDCONFIGURATION.NEXTVAL,
         'Miscellaneous',
         'Wrong Rail Button pressed',
         GETMINSYSTEMUSER(),
         SYSDATE,
         NULL
    FROM DUAL );
COMMIT;

---TRANSACTIONTYPE table---
INSERT INTO TRANSACTIONTYPE
  (TRANSACTIONTYPE_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)  
VALUES
  (38, 'Stockroom Shipping Scan', GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

---TRANSACTIONTYPE_DESC table---
INSERT INTO TRANSACTIONTYPE_DESC
  (TRANSACTIONTYPE_ID, LANGUAGE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT T.TRANSACTIONTYPE_ID,
         L.LANGUAGE_ID,
         T.FACTORYDESCRIPTION,
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM TRANSACTIONTYPE T, LANGUAGES L
   WHERE L.TRANSLATEDESCRIPTION = 'Y'
     AND T.TRANSACTIONTYPE_ID = 38;
COMMIT;

---WORKSTATION table---
ALTER TABLE WORKSTATION ADD (PLCIPADDRESS VARCHAR2(20) NULL,
                             PLCPORTNUMBER NUMBER NULL);

COMMENT ON COLUMN WORKSTATION.PLCIPADDRESS
  IS 'IP Address from PLC (used for StockroomShipping)';
COMMENT ON COLUMN WORKSTATION.PLCPORTNUMBER
  IS 'Port number from PLC (used for StockroomShipping)';

---WORKSTATIONPLCCONFIG table---
CREATE TABLE WORKSTATIONPLCCONFIG
(
  WORKSTATION_ID  NUMBER NOT NULL,
  RELAISNUMBER    NUMBER(2) NOT NULL CONSTRAINT RELAISNUMBER_VALID1_1
  			CHECK (RELAISNUMBER BETWEEN 1 AND 99),
  BUSINESSUNIT_ID NUMBER NULL,
  DEPOT_ID        NUMBER NULL,
  SOUND_ID        NUMBER NULL,
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP       DATE NOT NULL
);

COMMENT ON COLUMN WORKSTATIONPLCCONFIG.RELAISNUMBER
  IS 'Internal relais number from the PLC (associated with a rail)';
COMMENT ON COLUMN WORKSTATIONPLCCONFIG.BUSINESSUNIT_ID
  IS 'BusinessUnit that is associated with the rail.';
COMMENT ON COLUMN WORKSTATIONPLCCONFIG.DEPOT_ID
  IS 'A rail should be associated with either a Business Unit or a depot, but not both.';
COMMENT ON COLUMN WORKSTATIONPLCCONFIG.SOUND_ID
  IS 'Sound associated with this rail.';

ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT XPKWORKSTATIONPLCCONFIG PRIMARY KEY (WORKSTATION_ID, RELAISNUMBER);

CREATE UNIQUE INDEX XAK1WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (WORKSTATION_ID ASC, BUSINESSUNIT_ID ASC, DEPOT_ID ASC);

CREATE INDEX XIF1WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (WORKSTATION_ID ASC);
CREATE INDEX XIF2WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF3WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (DEPOT_ID ASC);
CREATE INDEX XIF4WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (SOUND_ID ASC);

ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT WORKSTATIONPLCCONFIG_WS FOREIGN KEY (WORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;
  
ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT WORKSTATIONPLCCONFIG_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;
  
ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT WORKSTATIONPLCCONFIG_DEP FOREIGN KEY (DEPOT_ID)
  REFERENCES DEPOT (DEPOT_ID) ON DELETE SET NULL;
  
ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT WORKSTATIONPLCCONFIG_SOUND FOREIGN KEY (SOUND_ID)
  REFERENCES SOUND (SOUND_ID) ON DELETE SET NULL;

---STOCKROOMSHIPPINGSCAN table---
CREATE TABLE STOCKROOMSHIPPINGSCAN
(
  WORKSTATION_ID   NUMBER NOT NULL,
  WORKORDERLINE_ID NUMBER NOT NULL,
  UNIQUEITEM_ID    NUMBER NOT NULL,
  SYSTEMUSER_ID    NUMBER NOT NULL,
  TIMESTAMP        DATE NOT NULL
)
;

ALTER TABLE STOCKROOMSHIPPINGSCAN
  ADD CONSTRAINT XPKSTOCKROOMSHIPPINGSCAN PRIMARY KEY (WORKSTATION_ID, WORKORDERLINE_ID, UNIQUEITEM_ID);
  
CREATE INDEX XIF1STOCKROOMSHIPPINGSCAN ON STOCKROOMSHIPPINGSCAN (WORKSTATION_ID ASC);
CREATE INDEX XIF2STOCKROOMSHIPPINGSCAN ON STOCKROOMSHIPPINGSCAN (WORKORDERLINE_ID ASC);
CREATE INDEX XIF3STOCKROOMSHIPPINGSCAN ON STOCKROOMSHIPPINGSCAN (UNIQUEITEM_ID ASC);

ALTER TABLE STOCKROOMSHIPPINGSCAN
  ADD CONSTRAINT STOCKROOMSHIPPINGSCAN_WS FOREIGN KEY (WORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;
  
ALTER TABLE STOCKROOMSHIPPINGSCAN
  ADD CONSTRAINT STOCKROOMSHIPPINGSCAN_WOL FOREIGN KEY (WORKORDERLINE_ID)
  REFERENCES WORKORDERLINE (WORKORDERLINE_ID) ON DELETE CASCADE;
  
ALTER TABLE STOCKROOMSHIPPINGSCAN
  ADD CONSTRAINT STOCKROOMSHIPPINGSCAN_UI FOREIGN KEY (UNIQUEITEM_ID)
  REFERENCES UNIQUEITEM (UNIQUEITEM_ID) ON DELETE CASCADE;
