-- CREATE TABLE EDI_O_CENTRALBILLING
CREATE TABLE EDI_O_CENTRALBILLING
(
TRANSFER_ID             NUMBER NOT NULL ,
TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_EDI_O_CENTRALBILLING CHECK (TRANSFER_STATUS IN (-1,0,1)),
BILTOCUSTOMERNUMBER     NUMBER NULL,
BILTOCUSTOMERNAME       VARCHAR2(80) NULL,
RVGCODE                 VARCHAR2(3) NULL,
BPMCODE                 VARCHAR2(2) NULL,
BILLEDQTY               NUMBER NULL,
DELIVEREDQTY            NUMBER NULL,
WEARERNUMBER            NUMBER NULL,
PRICE                   NUMBER(12,5) NULL,
AMOUNT                  NUMBER(9,2) NULL,
LINETAX                 NUMBER(7,3) NULL,
RVCEXTRACHARGECODE      VARCHAR2(4) NULL,
LINENUMBER              NUMBER NULL,
TOTALMINUSADJUSTMENTS   NUMBER(12,5) NULL,
PAYTHISAMOUNT           NUMBER(12,5) NULL,
TOTALPLUSADJUSTMENTS    NUMBER(12,5) NULL,
INVOICENUMBER           NUMBER NULL,
INVOICETYPE             VARCHAR2(1) NULL,
INVOICEDATE             DATE NULL,
INVOICEBUSINESSUNITCODE VARCHAR2(3) NULL,
STATUS                  VARCHAR2(1) NULL,
PREBILLAMOUNT           NUMBER(12,5) NULL,
CASHCOLLECTED           NUMBER(12,5) NULL,
ACCOUNTNUMBER           NUMBER NULL,
TURNOVERGROUPCODE       VARCHAR2(12) NULL,
TURNOVERGROUPDESCR      VARCHAR2(30) NULL,
SALESEMPLOYEENAME       VARCHAR2(40) NULL         
);
COMMENT ON COLUMN  EDI_O_CENTRALBILLING.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE EDI_O_CENTRALBILLING
  ADD CONSTRAINT PK_EDI_O_CENTRALBILLING PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_EDI_O_CENTRALBILLING ON EDI_O_CENTRALBILLING(TRANSFER_STATUS);
CREATE INDEX INDX02_EDI_O_CENTRALBILLING ON EDI_O_CENTRALBILLING(INVOICENUMBER);
CREATE INDEX INDX03_EDI_O_CENTRALBILLING ON EDI_O_CENTRALBILLING(BILTOCUSTOMERNUMBER);
ALTER TABLE EDI_O_CENTRALBILLING
  ADD CONSTRAINT FK_EDI_O_CENTRALBILLING FOREIGN KEY (BILTOCUSTOMERNUMBER)
  REFERENCES EDI_M_INVOICE_CUSTOMERS_U (CUSTOMERNUMBER) ON DELETE CASCADE;

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