--ABS-152157
CREATE TABLE ERP_I_SCAN
(
 TRANSFER_ID     NUMBER NOT NULL,
 TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
 PRIMARYID	     VARCHAR2(24) NOT NULL,
 PLANTCODE	     VARCHAR2(6) NOT NULL,
 SCANDATE	       NUMBER(8) NOT NULL,
 SCANTIME	       VARCHAR2(6) NOT NULL,
 LOCATIONCODE	   VARCHAR2(6) NOT NULL,
 NEXTLOCATION	   VARCHAR2(6) NULL,
 TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
CREATE INDEX INDX_ERP_I_SCAN_STATUS ON ERP_I_SCAN (TRANSFER_STATUS);
ALTER TABLE ERP_I_SCAN  ADD CONSTRAINT PK_ERP_I_SCAN PRIMARY KEY (TRANSFER_ID);
COMMENT ON COLUMN ERP_I_SCAN.SCANDATE IS 'YYYYMMDD e.g. 20110509';
COMMENT ON COLUMN ERP_I_SCAN.SCANTIME IS 'HH(24)MISS e.g. 142359 for 14:23:59, 24 hour notation';

CREATE TABLE ERP_I_SCAN_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_SCAN_LOG.TRANSFER_STATUS IS '0=ready to be read, 1=read correctly';
COMMENT ON COLUMN ERP_I_SCAN_LOG.PROCESS_STATUS IS '0=processed correctly, 1=process stopped with errors';
ALTER TABLE ERP_I_SCAN_LOG ADD CONSTRAINT PK_ERP_I_SCAN_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_SCAN_LOG ADD CONSTRAINT FK_ERP_I_SCAN_LOG FOREIGN KEY (TRANSFER_ID)
 REFERENCES ERP_I_SCAN (TRANSFER_ID) ON DELETE CASCADE;
