-- ABS-95122 / ABS-96980
--- INVOICEHEADEREXTRAINFO ---
ALTER TABLE INVOICEHEADEREXTRAINFO ADD (ERROR VARCHAR2(100) NULL,
					TAXSTATUS NUMBER DEFAULT 0 NOT NULL
						CONSTRAINT CHK_INVHEADEXTRA_TAXSTATUS CHECK (TAXSTATUS IN (0,1,2,3,4,5,6)));

COMMENT ON COLUMN INVOICEHEADEREXTRAINFO.ERROR IS 'Error text returned. VERTEXTAX module only.';
COMMENT ON COLUMN INVOICEHEADEREXTRAINFO.TAXSTATUS IS 'Only used for module VERTEXTAX
0 = new (default)
1 = Taxation in progress
2 = Taxed
3 = Taxation Failed
4 = Tax Posting in Progress
5 = Tax Posted
6 = Tax Posting Failed';

--- INVOICELINETAX ---
ALTER TABLE INVOICELINETAX DROP CONSTRAINT CHK_INVLTAX_JURISDICTIONLAYER;

COMMENT ON COLUMN INVOICELINETAX.JURISDICTIONLAYER IS '0 = State (US and CA), Country (EU), 
1 = City
2 = County (Country for CA)
3 = District
For EU tax always 0';

ALTER TABLE INVOICELINETAX ADD CONSTRAINT CHK_INVLTAX_JURISDICTIONLAYER CHECK (JURISDICTIONLAYER IN (0,1,2,3));

--- INVOICELINEEXTRAINFO ---
CREATE TABLE INVOICELINEEXTRAINFO
(
INVOICELINE_ID NUMBER NOT NULL,
TOTALTAXPERCENTAGE NUMBER NULL,
TOTALTAX NUMBER NULL,
TAXAREAID VARCHAR2(10) NULL,
TAXAREAID2 VARCHAR2(10) NULL,
TIMESTAMP DATE NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL
);

COMMENT ON COLUMN INVOICELINEEXTRAINFO.TOTALTAXPERCENTAGE IS 'Total tax percentage returned, used for VERTEXTAX module only';
COMMENT ON COLUMN INVOICELINEEXTRAINFO.TOTALTAX IS 'Total Tax, used for VERTEXTAX module only';
COMMENT ON COLUMN INVOICELINEEXTRAINFO.TAXAREAID IS 'Tax area ID Returned. VERTEXTAX module only';
COMMENT ON COLUMN INVOICELINEEXTRAINFO.TAXAREAID2 IS 'Secondary Tax area ID Returned. VERTEXTAX module only';

ALTER TABLE INVOICELINEEXTRAINFO ADD CONSTRAINT XPKINVOICELINEEXTRAINFO PRIMARY KEY (INVOICELINE_ID);

ALTER TABLE INVOICELINEEXTRAINFO ADD CONSTRAINT FK_INVOICELINEEXTR_INVLINE FOREIGN KEY (INVOICELINE_ID)
REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;

--- VERTEXTRANSACTIONLOG ---
CREATE SEQUENCE SEQ_VERTEXTRANSACTIONLOG
MINVALUE 1
MAXVALUE 999999999
START WITH 1
INCREMENT BY 1;

CREATE TABLE VERTEXTRANSACTIONLOG
(
BATCHID NUMBER NOT NULL,
BATCHTYPE NUMBER DEFAULT 0 NOT NULL
	CONSTRAINT CHK_VERTEXLOG_BATCHTYPE CHECK (BATCHTYPE IN (0,1)),
EXPORTID VARCHAR2(50) NOT NULL,
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) NULL,
POSTINGDATE VARCHAR2(10) NULL,
ISTAXONLYADJUSTMENT NUMBER NOT NULL
	CONSTRAINT CHK_VERTEXLOG_ISTAX CHECK (ISTAXONLYADJUSTMENT IN (0,1)),
PHYSICALORIGINTAXAREAID VARCHAR2(10) NOT NULL,
PHYSICALORIGINSTREETADD1 VARCHAR2(100) NOT NULL,
PHYSICALORIGINCITY VARCHAR2(60) NOT NULL,
PHYSICALORIGINMAINDIVISION VARCHAR2(60) NOT NULL,
PHYSICALORIGINSUBDIVISION VARCHAR2(60) NULL,
PHYSICALORIGINPOSTALCODE VARCHAR2(20) NOT NULL,
PHYSICALORIGINCOUNTRY VARCHAR2(60) NOT NULL,
ADMINTAXAREAID VARCHAR2(10) NOT NULL,
ADMINSTREETADDRESS1 VARCHAR2(100) NOT NULL,
ADMINCITY VARCHAR2(60) NOT NULL,
ADMINMAINDIVISION VARCHAR2(60) NOT NULL,
ADMINSUBDIVISION VARCHAR2(60) NULL,
ADMINPOSTALCODE VARCHAR2(20) NOT NULL,
ADMINCOUNTRY VARCHAR2(60) NOT NULL,
CUSTOMERCODE VARCHAR2(40) NOT NULL,
CUSTOMERCLASS VARCHAR2(40) NULL,
LINEITEMNUMBER NUMBER NOT NULL,
DESTINATIONTAXAREAID VARCHAR2(10) NOT NULL,
DESTINATIONSTREETADD1 VARCHAR2(100) NOT NULL,
DESTINATIONCITY VARCHAR2(60) NOT NULL,
DESTINATIONMAINDIVISION VARCHAR2(60)  NOT NULL,
DESTINATIONSUBDIVISION VARCHAR2(60) NULL,
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 NULL,
PRODUCT VARCHAR2(40) NOT NULL,
PRODUCTCLASS VARCHAR2(40) NOT NULL,
MATERIALCODE VARCHAR2(40) NULL,
FLEXIBLECODEFIELD1 VARCHAR2(250) NOT NULL,
FLEXIBLECODEFIELD2 VARCHAR2(250) NOT NULL,
FLEXIBLECODEFIELD3 VARCHAR2(250) NOT NULL,
FLEXIBLECODEFIELD4 VARCHAR2(250) NULL,
FLEXIBLECODEFIELD5 VARCHAR2(250) NOT NULL,
FLEXIBLECODEFIELD6 VARCHAR2(250) NOT NULL,
FLEXIBLECODEFIELD7 VARCHAR2(250) NOT NULL,
FLEXIBLECODEFIELD8 VARCHAR2(250) NULL,
FLEXIBLECODEFIELD9 VARCHAR2(250) NULL,
FLEXIBLECODEFIELD10 VARCHAR2(250) NULL,
FLEXIBLECODEFIELD11 VARCHAR2(250) NULL,
FLEXIBLECODEFIELD12 VARCHAR2(250) NOT NULL,
FLEXIBLECODEFIELD13 VARCHAR2(250) NOT NULL,
FLEXIBLECODEFIELD14 VARCHAR2(250) NULL,
FLEXIBLECODEFIELD15 VARCHAR2(250) NULL,
FLEXIBLENUMERICFIELD1 NUMBER NULL,
FLEXIBLEDATEFIELD1 VARCHAR2(10) NOT NULL,
FLEXIBLEDATEFIELD2 VARCHAR2(10) NULL,
FLEXIBLEDATEFIELD3 VARCHAR2(10) NULL,
FLEXIBLEDATEFIELD4 VARCHAR2(10) NOT NULL,
STATUS NUMBER DEFAULT 0 NOT NULL
	CONSTRAINT CHK_VERTEXLOG_STATUS CHECK (STATUS IN (0,1,2,3,4)),
CHARGEDTAX NUMBER NOT NULL,
CREATEDTIMESTAMP DATE NOT NULL,
RESPONSETIMESTAMP DATE NULL,
TAXAREAID VARCHAR2(10) NULL,
TAXAREAID2 VARCHAR2(10) NULL,
TOTALLINEITEMTAX NUMBER NULL,
COMBINEDRATE NUMBER NULL,
ERRORTEXT VARCHAR2(100) NULL,
ROOTEXCEPTION VARCHAR2(100) NULL,
TOTALCOUNTRYTAX NUMBER NULL,
TOTALMAINDIVISIONTAX NUMBER NULL,
TOTALSUBDIVISIONTAX NUMBER NULL,
TOTALCITYTAX NUMBER NULL,
TOTALDISTRICTTAX NUMBER NULL
);

COMMENT ON COLUMN VERTEXTRANSACTIONLOG.BATCHID IS 'Batch code to identify a group of transaction records within the input database.';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.BATCHTYPE IS '0 = BCI (default)
1 = WebService';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.EXPORTID IS 'Same value as batchID';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.MESSAGETYPE IS 'QUOTATION for prebilling (non settled invoice)
DISTRIBUTETAX for tax distribution (settlement)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.COMPANYCODE IS 'Value of system setting VERTEXTAXCOMPANY ';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DIVISIONCODE IS 'Legal Entity of the business unit';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DOCUMENTDATE IS 'This field identifies the date in CCYY-MM-DD or MM/DD/CCYY format. ABS Invoice date';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DOCUMENTNUMBER IS 'ABS Invoice Number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DOCUMENTTYPE IS 'ORDER for prebilling (non settled invoice)
INVOICE for tax distribution (settlement)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.BILLINGTYPE IS 'DELINV, CREDITMEMO, ONDEMAND, ONDEMANDTAX';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.POSTINGDATE IS 'ABS Invoice Date NULL for prebilling (quotation)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.ISTAXONLYADJUSTMENT IS '0 for prebilling (non settled invoice)
1 for tax distribution (settlement)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.PHYSICALORIGINTAXAREAID IS 'Plant / Depot tax area ID';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.PHYSICALORIGINSTREETADD1 IS 'Plant / Depot Address 1';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.PHYSICALORIGINCITY IS 'Plant / Depot Address City';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.PHYSICALORIGINMAINDIVISION IS 'Plant / Depot Address State';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.PHYSICALORIGINSUBDIVISION IS 'Plant / Depot Address County';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.PHYSICALORIGINPOSTALCODE IS 'Plant / Depot Address Zip Code';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.PHYSICALORIGINCOUNTRY IS 'Plant Country';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.ADMINTAXAREAID IS 'Bill To Tax area ID';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.ADMINSTREETADDRESS1 IS 'Bill To Customer Street';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.ADMINCITY IS 'Bill To Customer City';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.ADMINMAINDIVISION IS 'Bill To Customer State';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.ADMINSUBDIVISION IS 'Bill To Customer County';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.ADMINPOSTALCODE IS 'Bill To Customer Zip Code';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.ADMINCOUNTRY IS 'Bill To Customer Country';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.CUSTOMERCODE IS 'Bill To Customer Number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.CUSTOMERCLASS IS 'Master Account Number of Bill To Customer';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.LINEITEMNUMBER IS 'Invoice line number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DESTINATIONTAXAREAID IS 'Del To Tax area ID';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DESTINATIONSTREETADD1 IS 'Del To address 1';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DESTINATIONCITY IS 'Del to city';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DESTINATIONMAINDIVISION IS 'Del to State';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DESTINATIONSUBDIVISION IS 'Del to County';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DESTINATIONPOSTALCODE IS 'Del to Zipcode';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.DESTINATIONCOUNTRY IS 'Del to Customer country';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.QUANTITY IS 'Line Quantity';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.UNITOFMEASURE IS 'Unit of Measure, e.g. EA, BX, etc. Default EA';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.EXTENDEDPRICE IS 'Line Amount, equals Quantity x Unit Price.';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.INPUTTOTALTAX IS 'NULL for prebilling (non settled invoice)
Total tax on invoice line for tax distribution (settlement)';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.PRODUCT IS 'InvoiceLine.description';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.PRODUCTCLASS IS 'ABS Tax Group';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.MATERIALCODE IS 'Product (Style+Color, 6 digits)
Can be null for miscellaneous lines';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD1 IS 'Source system name, e.g. ABS.';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD2 IS 'Source system instance number – SBX, DEV, TST, and PRD. – last 3 chars of trusted ID system setting';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD3 IS 'Plant name';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD4 IS 'Depot Name if the route of the invoice has a depot, else null.';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD5 IS 'Ship-to Customer Number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD6 IS 'Invoice Line Type';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD7 IS 'Sub Type';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD8 IS 'Product Group';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD9 IS 'Merchandise Type';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD10 IS 'Contract number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD11 IS 'Route number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD12 IS 'Bill-to Customer Name';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD13 IS 'Del-to Customer Name';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD14 IS 'Invoice Remarks';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD15 IS 'Invoice source, fixed as ABS';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLENUMERICFIELD1 IS 'Wearer number';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLEDATEFIELD1 IS 'Document Creation Date';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLEDATEFIELD2 IS 'Delivery Date';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLEDATEFIELD3 IS 'Original Invoice Date if a credit';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLEDATEFIELD4 IS 'System date of record creation';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.STATUS IS '0 = New: 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.';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.CHARGEDTAX IS 'This field identifies tax already paid on a line item (also called Previous Tax Paid).';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.CREATEDTIMESTAMP IS 'Date/time the record was created';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.RESPONSETIMESTAMP IS 'Date/time the record was updated from vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.TAXAREAID IS 'This field is the primary tax area in which tax was calculated. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.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.TOTALLINEITEMTAX IS 'This field contains the sum of all taxes for a given line item. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.COMBINEDRATE IS 'This field contains the sum of the effective rates for each tax. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.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.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.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.TOTALMAINDIVISIONTAX IS 'Sum of all transaction taxes at state or province level. Field set by Vertex';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.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.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.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';

ALTER TABLE VERTEXTRANSACTIONLOG ADD CONSTRAINT XPKVERTEXTRANSACTIONLOG PRIMARY KEY (BATCHID, DOCUMENTNUMBER, LINEITEMNUMBER);

CREATE INDEX XIE1VERTEXTRANSACTIONLOG ON VERTEXTRANSACTIONLOG (DOCUMENTNUMBER ASC, PRODUCT ASC);

--- VERTEXBATCHCONTROL ---
CREATE TABLE VERTEXBATCHCONTROL
(
BATCHID NUMBER NOT NULL,
BATCHSTATUS NUMBER DEFAULT 0 NOT NULL
	CONSTRAINT CHK_VERTEXBATCH_STATUS CHECK (BATCHSTATUS IN (0,1,2,3,4,5))
);

COMMENT ON COLUMN VERTEXBATCHCONTROL.BATCHSTATUS IS '0 = new (default)
1 = ready for processing
2 = Processing
3 = Complete
4 = Failed
5 = WebService';

ALTER TABLE VERTEXBATCHCONTROL ADD CONSTRAINT XPKVERTEXBATCHCONTROL PRIMARY KEY (BATCHID);