-- ABS-76585
---ERP_O_ACCT_MDSE_REVENUE---
CREATE TABLE ERP_O_ACCT_MDSE_REVENUE
(
TRANSFER_ID           NUMBER NOT NULL,
TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_ACCT_MDSE_REVE_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
BUSINESSUNITCODE      VARCHAR2(6) NULL,
ROUTECODE             VARCHAR2(6) NULL,
INVOICEDATE           DATE NULL,
SORTSEQUENCE          NUMBER NULL,
CUSTOMERNUMBER        NUMBER NULL,
INVOICENUMBER         NUMBER NULL,
NEWACCOUNTFLAG        VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_ERP_O_ACCT_MDSE_REVE_V2 CHECK (NEWACCOUNTFLAG IN ('Y','N')),
CHT_ACCTNUMBER        NUMBER NULL,
MCD_TRANSACTIONTYPE   VARCHAR2(1) NULL,
MCD_SOURCE            VARCHAR2(5) NULL,
MCD_TRANSACTIONDATE   DATE NULL,
MCD_WEEKEND           DATE NULL,
MCD_RTEDAY            VARCHAR2(3) NULL,
MCD_BILLEDQTY         NUMBER NULL,
MCD_REVISEDQTY        NUMBER NULL,
MCD_BILLEDAMT         NUMBER NULL,
MCD_RTESEQUENCE       NUMBER NULL,
MCD_RTEFREQUENCY      NUMBER NULL,
MCD_JOURNALENTRYAMT   NUMBER NULL,
MCD_JOURNALENTRYNUM   VARCHAR2(10) DEFAULT 'JEABS' NULL,
MCD_JOURNALENTRYDESC  VARCHAR2(20) DEFAULT 'cash' NULL,
ESB_ERR_CODE          VARCHAR2(20) NULL,
ESB_ERR_DESC          VARCHAR2(500) NULL,
INST_ID               VARCHAR2(40) NULL,
STATUS                VARCHAR2(1) DEFAULT 'C' NULL CONSTRAINT CHK_ERP_O_ACCT_MDSE_REVE_V3 CHECK (STATUS IN ('C','P','E'))
);
COMMENT ON COLUMN  ERP_O_ACCT_MDSE_REVENUE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_ACCT_MDSE_REVENUE
  ADD CONSTRAINT ERP_O_ACCT_MDSE_REVENUE PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_ERP_O_ACCT_MDSE_REV ON ERP_O_ACCT_MDSE_REVENUE(ROUTECODE);  

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