Accounts Payable (AP) Integration

Last updated
Save as PDF

$images\icn_goldstar.jpg$ Premium Feature – Additional Purchase Required

Generic XML File Import/Export for Accounts Payable

The Accounts Payable (AP) Integration task that automatically imports invoice payment information and exports invoice information in an eXtensible Markup Language (XML) format for use with a separate accounting program. When an invoice is exported in this method, all information is included; filtering is done by the accounting program. This task can be configured to exclude internal invoices.

Requirements

Vendors are matched with invoices in one of two ways. If both are present, the Invoice ID takes precedence.

Invoice ID
Vendor Number and Internal Invoice Number

This information comes from the Entity Number field on the Base Info tab of the Entities module and the Transaction # field on the Base Info tab of the Invoices module.

When entering information on the Base Info tab in the Invoices module, be aware of the following:

Do not use commas in the Payment Amount field.
The Payment Method field on an invoice’s Base Info tab cannot be blank; a selection must be made from the dropdown list.

Exporting Invoice Information Using the AP Integration Task

When the AP Integration task is run, it searches for invoices that have been posted, and exports all available information to separate XML files called YYYYMMDDHHMMSSmmm_n_Invoice_Export.xml, where YYYY is the year, MM is the two-digit month, DD is the two-digit day, HH is the two-digit hour, MM is the minutes, SS is the seconds, mmm is the milliseconds, and n is incremented by one for each invoice exported. For example, a file created on November 15, 2003, at 8:58:23 p.m. might be called 20031115205823788_0_Invoice_Export.xml. All fields from the Invoices module are exported; filtering is done through a third-party program.

For the AP Integration tasks, all fields can be included in the XML file whether data exists for the fields. Contact Mitratech Support to enable this feature.

Although all other values in the export filename are padded to a set number of digits, the milliseconds (mmm) value reflects the actual value. Thus, if milliseconds are 10 in the example above, the filename will be 2003111520582310_0_Invoice_Export.xml and will have one less digit than a filename with a three-digit millisecond value.

The same statement is true for the alternate AP Integration task below.

When using the alternate AP Integration (Single Export File) task, all invoices that have been posted will be exported into a single export file called YYYYMMDDHHMMSSmmm_Invoice_Export.xml.

The AP Integration (Single Export File) task may cause memory-related issues in certain environments depending on the number of invoices being generated to the file. If memory issues occur, Mitratech recommends using the standard AP Integration task and modifying your import code to accept multiple export files.

If special characters, such as an ampersand (&) or double-quotation mark (“) are used in any field that is being exported, these characters will be converted using standard XML special character conversions.

User fields from the Invoices module will be included in the export file automatically.

After an invoice has been exported, a button on the Base Info tab will flag the invoice to be re- exported if any information has changed.

Sample XML Export File Format

Correlation between XML Tags and the eCounsel Database

XML		eCounsel
Node Set	Tag	Table	Field	Type
Invoice	Invoice_ID	Invoice	Invoice_ID	int
BillingEntity	Name	Entity	Name	varchar(150)
	FederalNumber	Entity	FederalNumber	varchar(50)
	VendorNumber	Entity	EntityNumber	varchar(40)
	AdministrationNumber	Company	AdministrationNumber	varchar(20)
	RemitAddressn	Address	Addressn	varchar(100)

XML		eCounsel
Node Set	Tag	Table	Field	Type
	RemitCity	Address	City	varchar(30)
	RemitState	Address	State	varchar(30)
	RemitZip	Address	Zip	varchar(20)
	RemitCountry	Address	Country	varchar(30)
	RemitRegion	Address	Region	varchar(30)
InvoiceBaseInfo	InvoiceDate	Invoice	InvoiceDate	datetime
	InvoiceStatus_CD	Invoice	InvoiceStatus_CD	varchar(40)
	BillingEntityInvoiceNumber	Invoice	BillingEntityInvoiceNumber	varchar(20)
	InternalInvoiceNumber	Invoice	OurInvoiceNumber	varchar(20)
	InternalInvoice	Invoice	InternalInvoice	char(1)
	ServiceStartDate	Invoice	ServiceStartDate	datetime
	ServiceEndDate	Invoice	ServiceEndDate	datetime
	ReceivedDate	Invoice	ReceivedDate	datetime
	PurchaseOrderNumber	Invoice	PurchaseOrderNumber	varchar(20)
	TaxPointDate	Invoice	TaxPointDate	datetime
	TaxInvoiceNote	Invoice	TaxInvoice	varchar(30)
	TaxCreditNote	Invoice	TaxCreditNote	char(1)
	InvoiceReference	Invoice	InvoiceReference	varchar(20)
	Currency_CD	Invoice	Currency_CD	varchar(40)
	ConversionRate	Invoice	ConversionRate	numeric(13,7)
	OriginalAmount	Invoice	OriginalAmount	numeric(17,2)
	DiscountBilledAmount	Invoice	DiscountAmount	numeric(17,2)
	AdjustmentAmount	Invoice	AdjustmentAmount	numeric(17,2)
	AdjustedBilledAmountPreTa x	Invoice	AdjustedNativeAmountPreTax	numeric(17,2)
	TaxBilledAmount	Invoice	TaxNativeAmount	numeric(17,2)
	AdjustedBilledAmount	Invoice	AdjustedNativeAmount	numeric(17,2)
	InvoiceAmount	Invoice	InvoiceAmount	numeric(17,2)
	AdjustmentDate	Invoice	AdjustmentDate	datetime
	HoldDate	Invoice	HoldDate	datetime
	PostedDate	Invoice	PostedDate	datetime
	ExportedDate	Invoice	ExportedDate	datetime
	InvoiceComments	LongComments	Comments	text
InvoiceMatter	MatterNumber	MatterInvoice	MatterNumber	int
	MatterName	Matter	MatterName	varchar(60)
	Priority_CD	Matter	Priority_CD	varchar(40)
	OriginalAmount	MatterInvoice	OriginalAmount	numeric(17,2)
	DiscountAmount	MatterInvoice	DiscountAmount	numeric(17,2)
	AdjustmentAmount	MatterInvoice	AdjustmentAmount	numeric(17,2)
	AdjustedBilledAmountPreTa x	MatterInvoice	AdjustedNativeAmountPreTax	numeric(17,4)
	TaxBilledAmount	MatterInvoice	TaxNativeAmount	numeric(17,4)
	AdjustedBilledAmount	MatterInvoice	AdjustedNativeAmount	numeric(17,4)
	MatterInvoiceAmount	MatterInvoice	MatterInvoiceAmount	numeric(17,2)

XML		eCounsel
Node Set	Tag	Table	Field	Type
	AdjustmentDate	MatterInvoice	AdjustmentDate	datetime
InvoiceMatterTax	VendorTaxID	MatterInvoice TaxSummary	VendorTaxID	varchar(20)
	TaxType	MatterInvoice TaxSummary	TaxType_CD	varchar(40)
	TaxRate	MatterInvoice TaxSummary	TaxRate	numeric(13,7)
	TaxCountry	MatterInvoice TaxSummary	TaxCountry_CD	varchar(40)
	TaxLocality	MatterInvoice TaxSummary	TaxLocality	varchar(40)
	TaxableBilledFees	MatterInvoice TaxSummary	TaxableNativeFees	numeric(17,2)
	TotalTaxFees	MatterInvoice TaxSummary	TotalTaxFees	numeric(17,2)
	TaxableBilledExpenses	MatterInvoice TaxSummary	TaxableNativeExpenses	numeric(17,2)
	TotalTaxExpenses	MatterInvoice TaxSummary	TotalTaxExpenses	numeric(17,2)
InvoiceMatterDetail	ProfessionalVendorName	Entity	Name	varchar(150)
	ProfessionalName	Entity	Name	varchar(150)
	LineItemDate	InvoiceDetail	LineItemDate	datetime
	Phase	Phase	PhaseName	varchar(60)
	Task	Task	TaskName	varchar(60)
	CostType_CD	InvoiceDetail	CostType_CD	varchar(40)
	Activity_CD	InvoiceDetail	Activity_CD	varchar(40)
	Expense_CD	InvoiceDetail	Expense_CD	varchar(40)
	HoursOrUnits	InvoiceDetail	HoursOrUnits	numeric(10,2)
	Rate	InvoiceDetail	Rate	numeric(10,4)
	OriginalAmount	InvoiceDetail	OriginalAmount	numeric(17,2)
	DiscountAmount	InvoiceDetail	DiscountAmount	numeric(17,2)
	AdjustmentAmount	InvoiceDetail	AdjustmentAmount	numeric(17,2)
	AdjustedBilledAmountPreTa x	InvoiceDetail	AdjustedNativeAmountPretax	numeric(17,2)
	LineItemAmount	InvoiceDetail	LineItemAmount	numeric(10,2)
	Adjustment_CD	InvoiceDetail	Adjustment_CD	varchar(40)
	AdjustmentDate	InvoiceDetail	AdjustmentDate	datetime
	IsTaxable	InvoiceDetail	IsTaxable	char(1)
	ImportWarning	InvoiceDetail	ImportWarning	varchar(1000)
	Comments	LongComments	Comments	text
InvoiceMatterDetailTaxable	VendorTaxID	InvoiceDetailTaxable	VendorTaxID	varchar(20)
InvoiceMatterAllocation	AllocationEntityName	InvoiceAllocation	Allocation_EID	int
	AllocationPercent	InvoiceAllocation	AllocationPercent	numeric(10,4)
	AllocationAmount	InvoiceAllocation	AllocationAmount	numeric(17,2)
	AllocationNativeAmount	InvoiceAllocation	AllocationNativeAmount	numeric(17,2)
	GeneralLedgerCode	InvoiceAllocation	GeneralLedgerCode	varchar(40)
	ChargeBackAccount1	InvoiceAllocation	ChargebackAccount1	varchar(40)

XML		eCounsel
Node Set	Tag	Table	Field	Type
	ChargeBackAccount2	InvoiceAllocation	ChargebackAccount2	varchar(40)
	ChargeBackAccount3	InvoiceAllocation	ChargebackAccount3	varchar(40)
	Comments	LongComments	Comments	text
InvoiceMatterApproval	EmployeeName	Entity	Name	varchar(150)
	Approved	InvoiceApproval	Approved	char(1)
	ApproveOrder	InvoiceApproval	ApproveOrder	int
	ApprovalDate	InvoiceApproval	ApprovalDate	datetime
	ApprovalReceivedDate	InvoiceApproval	ApprovalReceivedDate	datetime
	ApproverTimedOut	InvoiceApproval	ApproverTimedOut	char(1)
	Comments	LongComments	Comments	text

Importing Invoice Information Using the AP Integration Task

When a .xml file is successfully imported, the filename extension will be appended with

.imported_YYYYMMDDHHMMSSmmm, where YYYY is the year, MM is the two-digit month, DD is the two-digit day, HH is the two-digit hour, MM is the minutes, SS is the seconds, and mmm is the milliseconds. For example, a file imported on November 15, 2007, at 8:58:23 p.m. might be called payment.xml.imported_20071115205823788.

If any errors were encountered, the extension will be changed to

.ERROR_IMPORTING_YYYYMMDDHHMMSSmmm. The task will not process any other invoices in the file with the error. Ensure that the import directory is checked regularly for files with errors.

Sample XML Import File

file_Invoice_Import

Correlation between XML Tags and the eCounsel Database

XML		eCounsel
Node Set	Tag	Table	Field	Type
Invoice	Invoice_ID	Invoice	Invoice_ID	int
	VendorNumber	Invoice	EntityNumber	varchar(40)
	InternalInvoiceNumber	Invoice	OurInvoiceNumber	varchar(20)
	PaymentDate	Invoice	PaymentDate	datetime
	PaymentAmount	Invoice	PaymentAmount	numeric(17,2)
	PaymentMethod	Invoice	PaymentMethod_CD	varchar(40)
	CheckNumber	Invoice	CheckNumber	varchar(20)