-- ABS-97809
CREATE TABLE ERP_O_MIROKU
(
  TRANSFER_ID                  NUMBER NOT NULL,
  TRANSFER_STATUS              NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK1_ERP_O_MIROKU CHECK (TRANSFER_STATUS IN (-1,0,1)),
  ARTRANSACTIONDATE            DATE NOT NULL,
  SLIP_NUMBER                  NUMBER DEFAULT 9991 NULL,
  JOURNAL_INPUT_FORMAT         VARCHAR2(4) NULL,
  DEBIT_SUBJECT_CODE           VARCHAR2(8) DEFAULT '1250' NULL,
  DEBIT_SUBJECT_NAME           VARCHAR2(30) NULL,
  DEBIT_DIVISION_CODE          VARCHAR2(6) NULL,
  DEBIT_DIVISION_NAME          VARCHAR2(60) NULL,
  SUBJECT_CODE                 VARCHAR2(4) NULL,
  SUBJECT_NAME                 VARCHAR2(30) NULL,
  DEBIT_BUSINESS_PARTNER_CODE  VARCHAR2(6) NULL,
  DEBIT_BUSINESS_PARTNER_NAME  VARCHAR2(60) NULL,
  DEBIT_AUXILIARY_CODE         VARCHAR2(8) NULL,
  DEBIT_AUXILIARY_NAME         VARCHAR2(24) NULL,
  CREDIT_ITEM_CODE             VARCHAR2(8) NULL,
  CREDIT_ITEM_NAME             VARCHAR2(24) NULL,
  CREDIT_DEPARTMENT_CODE       VARCHAR2(8) NULL,
  CREDIT_DEPARTMENT_NAME       VARCHAR2(60) NULL,
  CREDIT_SUBJECT_CODE          VARCHAR2(8) NULL,
  CREDIT_SUBJECT_NAME          VARCHAR2(30) NULL,
  CREDIT_BUSINESS_PARTNER_CODE VARCHAR2(6) NULL,
  CREDIT_BUSINESS_PARTNER_NAME VARCHAR2(60) NULL,
  CREDIT_AUXILIARY_CODE        VARCHAR2(3) NULL,
  CREDIT_AUXILIARY_NAME        VARCHAR2(60) NULL,
  CONSUMPTION_TAX_CODE         VARCHAR2(6) NULL,
  CONSUMPTION_TAX_RATE         NUMBER NULL,
  AMOUNT                       NUMBER NOT NULL,
  DESCRIPTION                  VARCHAR2(128) NULL,
  DUE_DATE                     DATE NULL,
  RECORD_CATEGORY              VARCHAR2(3) DEFAULT '199' NULL,
  TIMESTAMP                    DATE DEFAULT SYSDATE NOT NULL
);
ALTER TABLE ERP_O_MIROKU
  ADD CONSTRAINT PK_ERP_O_MIROKU PRIMARY KEY(TRANSFER_ID);
CREATE INDEX XIE1ERP_O_MIROKU ON ERP_O_MIROKU(ARTRANSACTIONDATE);
CREATE INDEX XIE2ERP_O_MIROKU ON ERP_O_MIROKU(TIMESTAMP);

CREATE TABLE ERP_O_MIROKU_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK1_ERP_O_MIROKU_LOG 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_MIROKU_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_MIROKU_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
CREATE INDEX XIE1ERP_O_MIROKU_LOG ON ERP_O_MIROKU_LOG(TIMESTAMP);
ALTER TABLE ERP_O_MIROKU_LOG
  ADD CONSTRAINT PK_ERP_O_MIROKU_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_MIROKU_LOG
  ADD CONSTRAINT FK_ERP_O_MIROKU_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_MIROKU (TRANSFER_ID) ON DELETE CASCADE;