AP Integration Guide
Premium Feature –Additional PurchaseRequired
TheAPIntegration task importsinvoicepaymentinformationandexportsinvoiceinformationinaneXtensible Markup Language (XML) format. All fields from the Invoices module are exported to one XML fileforeach invoice; filtering is done through the third-party program. Vendors are matched with invoices in one of two ways:
- Invoice ID
- VendorNumberand Internal Invoice Number
This information comes from the EntityNumberfieldontheBase Info tab of the Entities module andtheTransaction #fieldfrom the Base Info tab of and Invoices module.
Whenentering information ontheBase Info tab in the Invoices module be aware of the following:
- Do not use commas in the Payment Amount field.
- The Payment Method field cannot Info blank; a selection must be made from on the dropdown field.
To configure the AP Integration task, first you must import the task template provided by Mitratech, then schedule the task and configure the parameters.
In addition, if your company requires that all invoices be exported to a single XML file (opposedto one file per invoice), can provide an alternate taskcalled theAP Integration (Single Export File) task. This task functions exactly as the APIntegration task (must be imported using ascript), butwillcreateasingle XML file all invoices exported each time the task is executed. See “Exporting Invoices Using the APIntegrationTask”onpage 8 for more information.
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.
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.
TheAP Integration task is configuredusing the Scheduler component of Suite Manager.
Configuring AP Integration
Importing the AP Integration Task
The AP Integration can be imported into the Scheduler using the instructions below. You must first purchase the functionality and obtain the task from Mitratech Support To Import the AP Integration Task:
- Open Suite Manager.
- On the Suite Manager control panel, click on the Scheduler component in the Tools & Utilities area.
- On the Scheduler window, click on code Templates tab.
- Click Import.
- On the Select the XML or Script file for import dialog box, select an XML or BSI file containing the task definition, then click Open.
Scheduling the AP Integration Task
The Scheduler component has several options for automating tasks at time intervals specified by the System Administrator. To run a task outside of its schedule, delete the task, edit the schedule, or edit any parameters, click the task name on the Schedule tab of the Scheduler window and click the appropriate button.
To Schedule a Task:
- Open Suite Manager.
- On the Suite Manager control panel, click the Scheduler component in the Tools & Utilities area.
- On the Scheduler window, click New.
- On the Schedule Information dialog box, select the name of the appropriate task from the Task dropdown field.
- Type a description for the schedule in the text box.
- Select the appropriate radio option button in the Schedule area. Tasks may be scheduled as follows:
- Daily – Specify the time each day the task should be run and if any days should be skipped in between runs (for example, setting the field to 2 day(s) at 1:00 AM would mean that the task would be run every other day at 1:00 a.m.).
- Weekly – Specify the day(s) of the week on which the task should be run (for example, selecting the Monday, Wednesday, and Friday checkboxes would mean that the task would be run on those days at the time specified).
- Monthly – Specify the day (such as 15 for the 15th of each month) of the month to run the task or specify the time of the month (such as the first Sunday of each month) to run the task at the time specified.
- Custom (Advanced) – See below.
- Click on the End Date checkbox and select a date for which the schedule will be terminated for the task.
- Enter any parameters for the task as needed.
- Click Save and Close.
To Schedule a Task Using Custom Time Parameters:
- Follow Steps 1-5 above.
- Select the Custom radio option button.
The string in the Expression text box is a cron (command run on) statement used to schedule the task. Suite Manager will build a statement for you according to the values in the text boxes as described below.
Editing the value in the Expression text box is advanced functionality and should only be performed by an administrator who is knowledgeable concerning valid cron statements. - In the Schedule area, specify the following:
- the number of the minute in the hour at which the task will run. Valid entries are 0-59 (and the , - * / special characters). For example, entering “30” means that the task will execute at x:30 minutes, where x is the hour.
- the number of the hour at which the task will run. Valid entries are 0-23 (and the , - * / special characters). For example, entering “2” means that the task will execute at 2:00 a.m.; entering “23” means that the task will execute at 11:00 p.m.
- the number of the day-of-the month for execution. Valid entries are 1-31 (and the , - * / ? L W special characters). For example, entering “15” means the task will execute on the 15th of every month.
- the number of the day of the week on which the task will run. Valid entries are 1-7 or SUN-SAT (and the , - * ? / L # special characters). For example, entering FRI means that the task will execute every Friday.
- the number of month for execution. Valid entries include 1-12 or JAN-DEC (and the , - * ?/ L # special characters). For example, entering “3” means the task will execute in March. where special characters are defined as follows:
* |
used to specify all values. For example, an asterisk in the minute field means every minute. |
? |
Specifies no value (day-of-month and day-of-week only). For example, a question mark in the day-of-month text box and a “3” in the day-of-week text box indicates the task will execute every Tuesday regardless of the day of the month. |
- |
Specifies a range of values. For example, entering “10-12” in the Hours text box means the task will execute at 10 p.m., 11 p.m., and 12 p.m. |
, |
Specifies additional values. For example, entering “MON,WED,FRI” in the day-of- week field means the task will execute on Monday, Wednesday, and Friday. |
/ |
Specifies increments. For example, entering “0/15” in the Minutes text box means the task will execute every 15 minutes starting at 0 minutes (0, 15, 30, 45). Entering “5/15” in the Minutes text box means the task will execute every 15 minutes starting at 5 minutes (5, 20, 35, 50). |
L |
Specifies the last day-of-month or day-of-week. |
W |
Specifies the weekday (Monday-Friday) closest to the given day. For example, entering “15W” means that the task will execute on the weekday closest to the 15th of the month (if the 15th falls on a Sunday for a month, the task will execute on Monday the 16th). |
# |
Specifies the nth X day of the month. For example, entering “6#3” in the day-of week text box means the third Friday of the month (day 6 = Friday and “#3” = the 3rd one of the month). |
Combinations of the various components may be configured. For example, entering 30 minutes and 1 hour means that the task will execute every 1.5 hours. Or, entering “LW” for the day-of-month translates to “last weekday of the month.”
- Click on the End Date checkbox and select a date for which the schedule will be terminated for the task.
- Enter any parameters for the task as needed.
- Click Save and Close.
The AP Integration task imports invoice payment information and exports invoice information in an XML format.
To Configure Task Parameters:
- Open Suite Manager.
- On the Suite Manager control panel, click the Scheduler component in the Tools & Utilities area.
- On the Scheduler window, click New.
- On the Schedule Information dialog box, select the AP Integration Task from the Task dropdown field.
- In the Schedule area, create a schedule for the task.
- In the Parameters area on the Schedule Information dialog box, type the appropriate directory paths. The import and export folders must be different; attempting to use the same folder will cause the task to fail.
- In the Exclude Details field, enter the appropriate option to exclude invoice line item comments from invoices being included in the export process.
- In the Exclude Internal field, enter the appropriate option to include or exclude internal invoices from the export process.
- Click Save and Close.
Exporting Invoices 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 Payment Information
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
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) |