--ABS-120094
CREATE TABLE ERP_I_INVOICEMESSAGES
(
  TRANSFER_ID          NUMBER NOT NULL,
  TRANSFER_STATUS      NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_INVOICEMESSAGES_TS CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CUSTOMERNUMBER       NUMBER NOT NULL,
  PRICEINCREASEDATE    DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
  MESSAGE              VARCHAR2(300) NOT NULL,
  TIMESTAMP            DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_INVOICEMESSAGES.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_INVOICEMESSAGES.CUSTOMERNUMBER
 IS 'Must be an existing customer number';
ALTER TABLE ERP_I_INVOICEMESSAGES
  ADD CONSTRAINT PK_ERP_I_INVOICEMESSAGES PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1_ERP_I_INVOICEMES ON ERP_I_INVOICEMESSAGES (CUSTOMERNUMBER, PRICEINCREASEDATE);
CREATE INDEX INDX_ERP_I_INVOICEMES_TRANS ON ERP_I_INVOICEMESSAGES (TRANSFER_STATUS); 

CREATE TABLE ERP_I_INVOICEMESSAGES_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_INVOICEMESSLOG_TS CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);
COMMENT ON COLUMN ERP_I_INVOICEMESSAGES_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_INVOICEMESSAGES_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_INVOICEMESSAGES_LOG
  ADD CONSTRAINT PK_ERP_I_INVOICEMESSAGES_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_INVOICEMESSAGES_LOG
  ADD CONSTRAINT FK_ERP_I_INVOICEMESSAGES_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_INVOICEMESSAGES (TRANSFER_ID) ON DELETE CASCADE;
