-- ABS-70582 EDI INTERFACE INVOICES
-- CREATE MAPPING TABLE EDI_M_INVOICE_CUSTOMERS_U
CREATE TABLE EDI_M_INVOICE_CUSTOMERS_U
(
  CUSTOMERNUMBER  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
ALTER TABLE EDI_M_INVOICE_CUSTOMERS_U
  ADD CONSTRAINT PK_EDI_M_INVOICE_CUSTOMERS_U PRIMARY KEY(CUSTOMERNUMBER);

-- CREATE TABLE EDI_O_INVOICEHEADER_U
CREATE TABLE EDI_O_INVOICEHEADER_U
(
  TRANSFER_ID             NUMBER NOT NULL ,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_EDI_O_INVOICEHEADER_U CHECK (TRANSFER_STATUS IN (-1,0,1)),
  INVBU_INVNR             VARCHAR2(22) NULL,
  INVOICEBUSINESSUNITCODE VARCHAR2(3) NULL,
  INVOICENUMBER           NUMBER NULL,
  ROUTECODE               VARCHAR2(6) NULL,
  INVOICETYPE             VARCHAR2(1) NULL,
  CHARGEINDICATOR         VARCHAR2(1) NULL,
  INVOICEDATE             DATE NULL,
  XREF_PARTNERCODE        VARCHAR2(35) NULL,
  XREF_PARTNERQUAL        VARCHAR2(4) NULL,
  CONTRACTNUMBER          NUMBER NULL,
  PO_NUMBER               VARCHAR2(22) NULL,
  PO_DATE                 DATE NULL,
  INVBU_INVNR_LINENR      VARCHAR2(30) NULL,
  DELTOCUSTOMERNUMBER     NUMBER NULL,
  DELTOCUSTOMERNAME       VARCHAR2(80) NULL,
  DELTOSTREET             VARCHAR2(55) NULL,
  DELTOSTREET2            VARCHAR2(55) NULL,
  DELTOCITY               VARCHAR2(30) NULL,
  DELTOSTATE              VARCHAR2(20) NULL,
  DELTOZIPCODE            VARCHAR2(15) NULL,
  DELTOCOUNTRY            VARCHAR2(3) NULL,
  DEPOTNAME               VARCHAR2(60) NULL,
  DEPOTSTREET             VARCHAR2(55) NULL,
  DEPOTSTREET2            VARCHAR2(55) NULL,
  DEPOTCITY               VARCHAR2(30) NULL,
  DEPOTSTATE              VARCHAR2(20) NULL,
  DEPOTZIPCODE            VARCHAR2(15) NULL,
  BILTOCUSTOMERNAME       VARCHAR2(80) NULL,
  BILTOCUSTOMERNUMBER     NUMBER NULL,
  BILTOSTREET             VARCHAR2(55) NULL,
  BILTOSTREET2            VARCHAR2(55) NULL,
  BILTOCITY               VARCHAR2(30) NULL,
  BILTOSTATE              VARCHAR2(20) NULL,
  BILTOZIPCODE            VARCHAR2(15) NULL,
  BILTOCOUNTRY            VARCHAR2(3) NULL,
  PAYTHISAMOUNT           NUMBER(15,2) NULL,
  TOTALTAXAMOUNT          NUMBER(15,2) NULL,
  GST_TAXAMOUNT           NUMBER(7,3) NULL,
  TOTFREIGHTAMOUNT        NUMBER(15,2) NULL,
  GL_NUMBERFREIGHT        VARCHAR2(30) NULL,
  GL_NUMBERTAX            VARCHAR2(30) NULL,
  CREDITSQUEEZEPERC       NUMBER(5,3) NULL,
  DISCOUNTDAYS            NUMBER(3) NULL,
  NETPAYMENTDAYS          NUMBER(3) NULL,
  PARTNERDELVLOCCODE      VARCHAR2(20) NULL,
  PARTNERDELVLOCGL#       VARCHAR2(20) NULL,
  PARTNERREMITLOCCOD      VARCHAR2(20) NULL,
  CONTACTPERSONNAME       VARCHAR2(25) NULL,
  CONTACTPHONE            VARCHAR2(25) NULL,
  MOZART_XREF_ORDINV      VARCHAR2(25) NULL,
  DROPTO_NAME             VARCHAR2(60) NULL,
  DROPTO_ADDRESS1         VARCHAR2(55) NULL,
  DROPTO_ADDRESS2         VARCHAR2(55) NULL,
  DROPTO_CITY             VARCHAR2(30) NULL,
  DROPTO_STATE            VARCHAR2(20) NULL,
  DROPTO_POSTCDE          VARCHAR2(15) NULL,
  DROPTO_COUNTRYCDE       VARCHAR2(3) NULL,
  UNUSED_TEXT_4           VARCHAR2(25) NULL,
  UNUSED_TEXT_5           VARCHAR2(25) NULL,
  UNUSED_CODE_1           VARCHAR2(6) NULL,
  UNUSED_CODE_2           VARCHAR2(6) NULL,
  UNUSED_CODE_3           VARCHAR2(6) NULL,
  UNUSED_DATE_1           NUMBER(8) NULL,
  UNUSED_DATE_2           NUMBER(8) NULL,
  UNUSED_DATE_3           NUMBER(8) NULL,
  UNUSED_VALUE_1          NUMBER(11,2) NULL,
  UNUSED_VALUE_2          NUMBER(11,2) NULL,
  UNUSED_VALUE_3          NUMBER(11,2) NULL,
  PARTNER_DEPT_NBR        NUMBER(5) NULL,
  UNUSED_NUMBER_2         NUMBER(5) NULL,
  UNUSED_NUMBER_3         NUMBER(5) NULL,
  RECORD_STATUS           VARCHAR2(1) NULL,
  UNUSED_STATUS_2         VARCHAR2(1) NULL,
  UNUSED_STATUS_3         VARCHAR2(1) NULL,
  EMAIL_ADDRESS           VARCHAR2(60) NULL,
  RECORDSTATUS            VARCHAR2(1) NULL,
  CREATINGUSER            VARCHAR2(10) NULL,
  CREATINGPROGRAM         VARCHAR2(10) NULL,
  CREATIONDATE            DATE NULL,
  CREATIONTIME            DATE NULL,
  CHANGEUSER              VARCHAR2(10) NULL,
  CHANGEPROGRAM           VARCHAR2(10) NULL,
  CHANGEDATE              DATE NULL,
  CHANGETIME              DATE NULL,
  EXT_TRANSFER_ID         VARCHAR2(20) NULL,
  EXT_INST_ID             VARCHAR2(40) NULL,
  EXT_PROCESSED_STATUS    VARCHAR2(1) NULL,
  EXT_ERROR_CODE          VARCHAR2(20) NULL,
  EXT_ERROR_DESC          VARCHAR2(500)
);
COMMENT ON COLUMN  EDI_O_INVOICEHEADER_U.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE EDI_O_INVOICEHEADER_U
  ADD CONSTRAINT EDI_O_INVOICEHEADER_U PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_EDI_O_INVOICEHEADER_U ON EDI_O_INVOICEHEADER_U(TRANSFER_STATUS);
CREATE INDEX INDX02_EDI_O_INVOICEHEADER_U ON EDI_O_INVOICEHEADER_U(INVOICENUMBER);
CREATE INDEX INDX03_EDI_O_INVOICEHEADER_U ON EDI_O_INVOICEHEADER_U(DELTOCUSTOMERNUMBER);
ALTER TABLE EDI_O_INVOICEHEADER_U
  ADD CONSTRAINT FK_EDI_O_INVOICEHEADER_U FOREIGN KEY (DELTOCUSTOMERNUMBER)
  REFERENCES EDI_M_INVOICE_CUSTOMERS_U (CUSTOMERNUMBER) ON DELETE CASCADE;

-- CREATE TABLE EDI_O_INVOICELINE_U
CREATE TABLE EDI_O_INVOICELINE_U
(
  TRANSFER_ID             NUMBER NOT  NULL,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_EDI_O_INVOICELINE_U CHECK (TRANSFER_STATUS IN (-1,0,1)),
  INVBU_INVNR             VARCHAR2(22) NULL,
  LINENUMBER              VARCHAR2(5) NULL,
  INVOICEBUSINESSUNITCODE VARCHAR2(3) NULL,
  INVOICENUMBER           NUMBER NULL,
  XREF_PARTNERCODE        VARCHAR2(35) NULL,
  XREF_PARTNERQUAL        VARCHAR2(4) NULL,
  INVOICETYPE             VARCHAR2(1) NULL,
  SORTCODE                VARCHAR2(4) NULL,
  CODEINCLSIZE            VARCHAR2(48) NULL,
  DESCRIPTION             VARCHAR2(12) NULL,
  LONGDESCRIPTION         VARCHAR2(80) NULL,
  PRODUCTCODE             VARCHAR2(12) NULL,
  COLORCODE               VARCHAR2(12) NULL,
  SIZECODE1               VARCHAR2(12) NULL,
  SIZECODE2               VARCHAR2(12) NULL,
  RVGCODE                 VARCHAR2(3) NULL,
  BPMCODE                 VARCHAR2(2) NULL,
  RVCEXTRACHARGECODE      VARCHAR2(4) NULL,
  WEARERNUMBER            NUMBER NULL,
  FIRSTNAME               VARCHAR2(25) NULL,
  WEARERNAME              VARCHAR2(25) NULL,
  BILLEDQTY               NUMBER NULL,
  ORDEREDQTY              NUMBER NULL,
  DELIVEREDQTY            NUMBER NULL,
  PICKUPQTY               NUMBER NULL,
  AMOUNT                  NUMBER(9,2) NULL,
  PRICE                   NUMBER(12,5) NULL,
  REGISTRATIONUNIT        VARCHAR2(20) NULL,
  LINETAX                 NUMBER(7,3) NULL,
  UNITLINETAX             NUMBER(7,3) NULL,
  LINETAXPERCENTAGE       NUMBER(6,6) NULL,
  LINTAXTYPE              VARCHAR2(1) NULL,
  REFERENCEID             VARCHAR2(30) NULL,
  TEXT1                   VARCHAR2(25) NULL,
  CODE1                   VARCHAR2(6) NULL,
  DATE1                   NUMBER(8) NULL,
  VALUE1                  NUMBER(11,2) NULL,
  POLINENR                NUMBER(5) NULL,
  WKSBTWDEL               VARCHAR2(1) NULL,
  RECORDSTATUS            VARCHAR2(1) NULL,
  CREATINGUSER            VARCHAR2(10) NULL,
  CREATINGPROGRAM         VARCHAR2(10) NULL,
  CREATIONDATE            DATE NULL,
  CREATIONTIME            DATE NULL,
  CHANGEUSER              VARCHAR2(10) NULL,
  CHANGEPROGRAM           VARCHAR2(10) NULL,
  CHANGEDATE              DATE NULL,
  CHANGETIME              DATE NULL,
  EXT_TRANSFER_ID         VARCHAR2(20) NULL,
  EXT_INST_ID             VARCHAR2(40) NULL,
  EXT_PROCESSED_STATUS    VARCHAR2(1) NULL,
  EXT_ERROR_CODE          VARCHAR2(20) NULL,
  EXT_ERROR_DESC          VARCHAR2(500) NULL
);
COMMENT ON COLUMN  EDI_O_INVOICELINE_U.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE EDI_O_INVOICELINE_U
  ADD CONSTRAINT FK_EDI_O_INVOICELINE_U FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICEHEADER_U (TRANSFER_ID) ON DELETE CASCADE; 
CREATE INDEX INDX01_EDI_O_INVOICELINE_U ON EDI_O_INVOICELINE_U(TRANSFER_STATUS);  
CREATE INDEX INDX02_EDI_O_INVOICELINE_U ON EDI_O_INVOICELINE_U(INVOICENUMBER, LINENUMBER);

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