--ABSN-2357
--fields are here as selection criteria for export to bookkeeping system via view
CREATE TABLE ERP_O_BOOKKEEPING
(
  TRANSFER_ID           NUMBER NOT NULL,
  TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL,
  ARTRANSACTION_ID      NUMBER NOT NULL,
  BUSINESSUNITCODE      VARCHAR2(6),
  EXPORT_FOR_PERIOD     NUMBER,
  ARTRANSACTIONDATE     DATE,
  ARTRANSACTIONTYPE_ID  NUMBER,
  ARTRANSTYPECODE       VARCHAR2(3),
  ARTRANSTYPELEGACYCODE VARCHAR2(3),
  FACTORYDESCRIPTION    VARCHAR2(30),  
  INVOICENUMBER         NUMBER,
  INVOICEDATE           DATE,
  CUSTOMERNUMBER        NUMBER NOT NULL,
  LEGACYCUSTOMERNUMBER  VARCHAR2(12),
  CLIENTNUMBER          NUMBER,
  CURRENCYCODE          VARCHAR2(3),
  TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_O_BOOKKEEPING.EXPORT_FOR_PERIOD IS 'Parameter fp_export_for_period between 1 and 12, max 2 long';
COMMENT ON COLUMN ERP_O_BOOKKEEPING.TRANSFER_STATUS IS '0=ready to be read, 1=read correctly';
ALTER TABLE ERP_O_BOOKKEEPING ADD CONSTRAINT PK_ERP_O_BOOKKEEPING PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_BOOKKEEPING ADD CONSTRAINT CHK_ERP_O_BOOKKEEPING_V1 CHECK (TRANSFER_STATUS IN (-1,0,1));
CREATE INDEX XIF1ERP_O_BOOKKEEPING ON ERP_O_BOOKKEEPING (TRANSFER_STATUS);
CREATE INDEX XIF2ERP_O_BOOKKEEPING ON ERP_O_BOOKKEEPING (ARTRANSACTION_ID, TRANSFER_ID);

CREATE TABLE ERP_O_BOOKKEEPING_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_O_BOOKKEEPING_LOG.TRANSFER_STATUS IS '0=ready to be read, 1=read correctly';
COMMENT ON COLUMN ERP_O_BOOKKEEPING_LOG.PROCESS_STATUS IS '0=processed correctly, 1=process stopped with errors';
ALTER TABLE ERP_O_BOOKKEEPING_LOG ADD CONSTRAINT PK_ERP_O_BOOKKEEPING_LOG PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF2ERP_O_BOOKKEEPING_LOG ON ERP_O_BOOKKEEPING_LOG(PROCESS_STATUS);
ALTER TABLE ERP_O_BOOKKEEPING_LOG ADD CONSTRAINT FK_ERP_O_BOOKKEEPING_LOG FOREIGN KEY (TRANSFER_ID) REFERENCES ERP_O_BOOKKEEPING (TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE ERP_O_BOOKKEEPING_LOG ADD CONSTRAINT CHK_ERP_O_BOOKKEEPING_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1));
