-- ABS-76585
---ERP_O_ACCT_MDSE_CONTRACT---
CREATE TABLE ERP_O_ACCT_MDSE_CONTRACT
(
TRANSFER_ID           NUMBER NOT NULL,
TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_ACCT_MDSE_CONTR_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
BUSINESSUNITCODE      VARCHAR2(6) NULL,
CONTRACTCODE          NUMBER NULL,
CONTRACTTYPE          VARCHAR2(6) NULL,
CUSTCONTRACTENDDATE   DATE NULL,
CONTRACTRENEWALDATE   DATE NULL,
CUSTOMERNUMBER        NUMBER NULL,
ROUTECODE             VARCHAR2(6) NULL,
ROUTEDELIVERYDAY      NUMBER(1) NULL,
ROUTESEQUENCENUMBER   NUMBER NULL,
ROUTEFREQUENCY        NUMBER 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_CONTR_V2 CHECK (STATUS IN ('C','P','E')),
INSTALL_DATE          DATE NULL,
ACTIVE                VARCHAR2(1) DEFAULT 'Y' NULL CONSTRAINT CHK_ERP_O_ACCT_MDSE_CONTR_V3 CHECK (ACTIVE IN ('Y','N')),
COEAPPLYGMPWCHARGES   VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT CHK_ERP_O_ACCT_MDSE_CONTR_V4 CHECK (COEAPPLYGMPWCHARGES IN ('Y','N'))
);
COMMENT ON COLUMN  ERP_O_ACCT_MDSE_CONTRACT.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_ACCT_MDSE_CONTRACT
  ADD CONSTRAINT ERP_O_ACCT_MDSE_CONTRACT PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_ERP_O_ACCT_MDSE_CONTR ON ERP_O_ACCT_MDSE_CONTRACT(CONTRACTCODE);  

-- ERP_O_O_ACCT_MDSE_CONTRACT_LOG --
CREATE TABLE ERP_O_ACCT_MDSE_CONTRACT_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_O_ACCT_MDSE_CON_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_CONTRACT_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN  ERP_O_ACCT_MDSE_CONTRACT_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_O_ACCT_MDSE_CONTRACT_LOG
  ADD CONSTRAINT PK_ERP_O_ACCT_MDSE_CONTR_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_ACCT_MDSE_CONTRACT_LOG
  ADD CONSTRAINT FK_ERP_O_ACCT_MDSE_CONTR_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_ACCT_MDSE_CONTRACT(TRANSFER_ID) ON DELETE CASCADE;
