-- ABS-133193
CREATE TABLE VERTEXBATCHCONTROL_ARCH
(
  BATCHID     NUMBER NOT NULL,
  BATCHSTATUS NUMBER DEFAULT 0 NOT NULL,
  PLANT_ID    NUMBER
);
COMMENT ON COLUMN VERTEXBATCHCONTROL_ARCH.BATCHSTATUS
  IS '0 = new (default) 1 = ready for processing 2 = Processing 3 = Complete 4 = Failed 5 = WebService 6 = Cancelled 7 = Finalized';

ALTER TABLE VERTEXBATCHCONTROL_ARCH
  ADD CONSTRAINT XPKVERTEXBATCHCONTROL_ARCH PRIMARY KEY (BATCHID);
  

CREATE TABLE VERTEXTRANSACTIONLOG_ARCH
(
  BATCHID                      NUMBER NOT NULL,
  BATCHTYPE                    NUMBER DEFAULT 0 NOT NULL,
  EXPORTID                     VARCHAR2(50),
  MESSAGETYPE                  VARCHAR2(50) NOT NULL,
  COMPANYCODE                  VARCHAR2(40) NOT NULL,
  DIVISIONCODE                 VARCHAR2(40) NOT NULL,
  DOCUMENTDATE                 VARCHAR2(10) NOT NULL,
  DOCUMENTNUMBER               VARCHAR2(80) NOT NULL,
  DOCUMENTTYPE                 VARCHAR2(20) NOT NULL,
  BILLINGTYPE                  VARCHAR2(20),
  POSTINGDATE                  VARCHAR2(10),
  TAXONLYADJUSTMENTIND         NUMBER NOT NULL,
  PHYSICALORIGINTAXAREAID      VARCHAR2(10) NOT NULL,
  PHYSICALORIGINSTREET         VARCHAR2(100) NOT NULL,
  PHYSICALORIGINCITY           VARCHAR2(60) NOT NULL,
  PHYSICALORIGINMAINDIVISION   VARCHAR2(60) NOT NULL,
  PHYSICALORIGINSUBDIVISION    VARCHAR2(60),
  PHYSICALORIGINPOSTALCODE     VARCHAR2(20) NOT NULL,
  PHYSICALORIGINCOUNTRY        VARCHAR2(60) NOT NULL,
  ADMINDESTINATIONTAXAREAID    VARCHAR2(10) NOT NULL,
  ADMINDESTINATIONSTREET       VARCHAR2(100) NOT NULL,
  ADMINDESTINATIONCITY         VARCHAR2(60) NOT NULL,
  ADMINDESTINATIONMAINDIVISION VARCHAR2(60) NOT NULL,
  ADMINDESTINATIONSUBDIVISION  VARCHAR2(60),
  ADMINDESTINATIONPOSTALCODE   VARCHAR2(20) NOT NULL,
  ADMINDESTINATIONCOUNTRY      VARCHAR2(60) NOT NULL,
  CUSTOMERCODE                 VARCHAR2(40) NOT NULL,
  CUSTOMERCLASS                VARCHAR2(40),
  LINEITEMNUMBER               NUMBER NOT NULL,
  DESTINATIONTAXAREAID         VARCHAR2(10) NOT NULL,
  DESTINATIONSTREET            VARCHAR2(100) NOT NULL,
  DESTINATIONCITY              VARCHAR2(60) NOT NULL,
  DESTINATIONMAINDIVISION      VARCHAR2(60) NOT NULL,
  DESTINATIONSUBDIVISION       VARCHAR2(60),
  DESTINATIONPOSTALCODE        VARCHAR2(20) NOT NULL,
  DESTINATIONCOUNTRY           VARCHAR2(60) NOT NULL,
  QUANTITY                     NUMBER NOT NULL,
  UNITOFMEASURE                VARCHAR2(3) DEFAULT 'EA' NOT NULL,
  EXTENDEDPRICE                NUMBER NOT NULL,
  INPUTTOTALTAX                NUMBER,
  PRODUCTCODE                  VARCHAR2(40) NOT NULL,
  PRODUCTCLASSCODE             VARCHAR2(40) NOT NULL,
  MATERIALCODE                 VARCHAR2(40),
  FLEXIBLECODEFIELD1           VARCHAR2(250) NOT NULL,
  FLEXIBLECODEFIELD2           VARCHAR2(250) NOT NULL,
  FLEXIBLECODEFIELD3           VARCHAR2(250) NOT NULL,
  FLEXIBLECODEFIELD4           VARCHAR2(250),
  FLEXIBLECODEFIELD5           VARCHAR2(250) NOT NULL,
  FLEXIBLECODEFIELD6           VARCHAR2(250) NOT NULL,
  FLEXIBLECODEFIELD7           VARCHAR2(250),
  FLEXIBLECODEFIELD8           VARCHAR2(250),
  FLEXIBLECODEFIELD9           VARCHAR2(250),
  FLEXIBLECODEFIELD10          VARCHAR2(250),
  FLEXIBLECODEFIELD11          VARCHAR2(250),
  FLEXIBLECODEFIELD12          VARCHAR2(250) NOT NULL,
  FLEXIBLECODEFIELD13          VARCHAR2(250) NOT NULL,
  FLEXIBLECODEFIELD14          VARCHAR2(250),
  FLEXIBLECODEFIELD15          VARCHAR2(250),
  FLEXIBLENUMERICFIELD1        NUMBER,
  FLEXIBLEDATEFIELD1           VARCHAR2(10) NOT NULL,
  FLEXIBLEDATEFIELD2           VARCHAR2(10),
  FLEXIBLEDATEFIELD3           VARCHAR2(10),
  FLEXIBLEDATEFIELD4           VARCHAR2(10) NOT NULL,
  STATUS                       NUMBER DEFAULT 0 NOT NULL,
  CHARGEDTAX                   NUMBER NOT NULL,
  CREATEDTIMESTAMP             DATE NOT NULL,
  RESPONSETIMESTAMP            DATE,
  TAXAREAID                    VARCHAR2(10),
  TAXAREAID2                   VARCHAR2(10),
  TOTALLINEITEMTAX             NUMBER,
  COMBINEDRATE                 NUMBER,
  ERRORTEXT                    VARCHAR2(200),
  ROOTEXCEPTION                VARCHAR2(100),
  TOTALCOUNTRYTAX              NUMBER,
  TOTALMAINDIVISIONTAX         NUMBER,
  TOTALSUBDIVISIONTAX          NUMBER,
  TOTALCITYTAX                 NUMBER,
  TOTALDISTRICTTAX             NUMBER,
  FLEXIBLECODEFIELD16          VARCHAR2(250) NOT NULL,
  FLEXIBLECODEFIELD17          VARCHAR2(250),
  TRANSACTIONTYPE              VARCHAR2(15) DEFAULT 'SALE' NOT NULL,
  FLEXIBLENUMERICFIELD2        NUMBER
);
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.BATCHID
  IS 'Batch code to identify a group of transaction records within the input database.';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.BATCHTYPE
  IS '0 = BCI (default) 1 = WebService 2 = BCI (Immediate Credit tax distribution)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.EXPORTID
  IS 'Same value as batchID NOTE: this is updated by the BCI process to NULL when complete';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.MESSAGETYPE
  IS 'QUOTATION for prebilling (non settled invoice) DISTRIBUTETAX for tax distribution (settlement)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.COMPANYCODE
  IS 'Value of system setting VERTEXTAXCOMPANY';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DIVISIONCODE
  IS 'Legal Entity of the business unit';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DOCUMENTDATE
  IS 'This field identifies the date in CCYY-MM-DD or MM/DD/CCYY format. ABS Invoice date';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DOCUMENTNUMBER
  IS 'ABS Invoice Number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DOCUMENTTYPE
  IS 'ORDER for prebilling (non settled invoice) INVOICE for tax distribution (settlement)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.BILLINGTYPE
  IS 'DELINV, CREDITMEMO, ONDEMAND, ONDEMANDTAX';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.POSTINGDATE
  IS 'ABS Invoice Date NULL for prebilling (quotation)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TAXONLYADJUSTMENTIND
  IS '0 for prebilling (non settled invoice) 1 for tax distribution (settlement)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.PHYSICALORIGINTAXAREAID
  IS 'Plant / Depot tax area ID';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.PHYSICALORIGINSTREET
  IS 'Plant / Depot Address 1';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.PHYSICALORIGINCITY
  IS 'Plant / Depot Address City';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.PHYSICALORIGINMAINDIVISION
  IS 'Plant / Depot Address State';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.PHYSICALORIGINSUBDIVISION
  IS 'Plant / Depot Address County';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.PHYSICALORIGINPOSTALCODE
  IS 'Plant / Depot Address Zip Code';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.PHYSICALORIGINCOUNTRY
  IS 'Plant Country';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.ADMINDESTINATIONTAXAREAID
  IS 'Bill To Tax area ID';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.ADMINDESTINATIONSTREET
  IS 'Bill To Customer Street';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.ADMINDESTINATIONCITY
  IS 'Bill To Customer City';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.ADMINDESTINATIONMAINDIVISION
  IS 'Bill To Customer State';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.ADMINDESTINATIONSUBDIVISION
  IS 'Bill To Customer County';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.ADMINDESTINATIONPOSTALCODE
  IS 'Bill To Customer Zip Code';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.ADMINDESTINATIONCOUNTRY
  IS 'Bill To Customer Country';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.CUSTOMERCODE
  IS 'Bill To Customer Number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.CUSTOMERCLASS
  IS 'Master Account Number of Bill To Customer';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.LINEITEMNUMBER
  IS 'Invoice line number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DESTINATIONTAXAREAID
  IS 'Del To Tax area ID';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DESTINATIONSTREET
  IS 'Del To address 1';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DESTINATIONCITY
  IS 'Del to city';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DESTINATIONMAINDIVISION
  IS 'Del to State';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DESTINATIONSUBDIVISION
  IS 'Del to County';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DESTINATIONPOSTALCODE
  IS 'Del to Zipcode';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.DESTINATIONCOUNTRY
  IS 'Del to Customer country';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.QUANTITY
  IS 'Line Quantity';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.UNITOFMEASURE
  IS 'Unit of Measure, e.g. EA, BX, etc. Default EA';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.EXTENDEDPRICE
  IS 'Line Amount, equals Quantity x Unit Price.';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.INPUTTOTALTAX
  IS 'NULL for prebilling (non settled invoice) Total tax on invoice line for tax distribution (settlement)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.PRODUCTCODE
  IS 'InvoiceLine.description';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.PRODUCTCLASSCODE
  IS 'ABS Tax Group';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.MATERIALCODE
  IS 'Product (Style+Color, 6 digits) Can be null for miscellaneous lines';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD1
  IS 'Source system name, e.g. ABS.';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD2
  IS 'Value of system setting VERTEXTAXINSTANCECODE';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD3
  IS 'Plant code';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD4
  IS 'Depot Code if the route of the invoice has a depot, else NULL';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD5
  IS 'Ship-to Customer Number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD6
  IS 'Invoice Line Type Number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD7
  IS 'Invoice Line Sub Type Number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD8
  IS 'Product Group';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD9
  IS 'Merchandise Type';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD10
  IS 'Contract number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD11
  IS 'Route number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD12
  IS 'Bill-to Customer Name';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD13
  IS 'Del-to Customer Name';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD14
  IS 'Invoice Remarks';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD15
  IS 'Invoice source, fixed as ABS';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLENUMERICFIELD1
  IS 'Wearer number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLEDATEFIELD1
  IS 'Document Creation Date';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLEDATEFIELD2
  IS 'Delivery Date';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLEDATEFIELD3
  IS 'Original Invoice Date if a credit';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLEDATEFIELD4
  IS 'System date of record creation';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.STATUS
  IS '0 = New (default): The Batch Client Interface has not processed this new line item. 1 = Processing: The Batch Client Interface is processing this line item. 2 = OK: The Batch Client Interface processed this line item successfully. 3 = Skipped: The Batch Client Interface skipped the line item because it already exists in the Tax Journal. 4 = Failed: The Batch Client Interface did not process this line item because a field associated with this line item or another line item within the same transaction was invalid. 5 = WebService generated line';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.CHARGEDTAX
  IS 'This field identifies tax already paid on a line item (also called Previous Tax Paid).';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.CREATEDTIMESTAMP
  IS 'Date/time the record was created';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.RESPONSETIMESTAMP
  IS 'Date/time the record was updated from vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TAXAREAID
  IS 'This field is the primary tax area in which tax was calculated. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TAXAREAID2
  IS 'When tax is calculated in more than one tax area, this field contains the second Tax Area ID. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TOTALLINEITEMTAX
  IS 'This field contains the sum of all taxes for a given line item. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.COMBINEDRATE
  IS 'This field contains the sum of the effective rates for each tax. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.ERRORTEXT
  IS 'This field contains explanatory text that describes the reason for the failed status (3) for a given line item. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.ROOTEXCEPTION
  IS 'This field contains the rootExceptionClass that the CalcEngine throws if there is an error. This information helps when troubleshooting failed BCI requests. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TOTALCOUNTRYTAX
  IS 'Sum of all transaction taxes at country/federal level. In US, there is no federal level sales and use tax thus this field will always be zero. In Canada, GST and HST are on the federal level.Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TOTALMAINDIVISIONTAX
  IS 'Sum of all transaction taxes at state or province level. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TOTALSUBDIVISIONTAX
  IS 'Sum of all transaction taxes at county level. In Canada, there is no county level VAT thus this field will always be zero.Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TOTALCITYTAX
  IS 'Sum of all transaction taxes at city level. In Canada, there is no city level VAT thus this field will always be zero. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TOTALDISTRICTTAX
  IS 'Sum of all transaction taxes at district level. In Canada, there is no district level VAT thus this field will always be zero. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD16
  IS 'Invoice Line Type Description';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLECODEFIELD17
  IS 'Invoice Line Sub Type Description';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.TRANSACTIONTYPE
  IS 'Tax type of the invoice line (RENTAL or SALE)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG_ARCH.FLEXIBLENUMERICFIELD2
  IS 'BatchID';

ALTER TABLE VERTEXTRANSACTIONLOG_ARCH
  ADD CONSTRAINT XPKVERTEXTRANSACTIONLOG_ARCH PRIMARY KEY (BATCHID, DOCUMENTNUMBER, LINEITEMNUMBER);
ALTER TABLE VERTEXTRANSACTIONLOG_ARCH
  ADD CONSTRAINT FK_VERTEXTRANSLOG_ARCH_BATCH FOREIGN KEY (BATCHID)
  REFERENCES VERTEXBATCHCONTROL_ARCH (BATCHID);
