Skip to main content
Mitratech Success Center

Client Support Center

Need help? Click a product group below to select your application and get access to knowledge articles, webinars, training content, and release notes or to contact our support team.

Authorized users - log in to create a ticket, view tickets status and check your success plan details.

 

AP Link Implementation Guide

This document covers the configuration and implementation of the TeamConnect AP Link tool.  This is a configurable tool that allows invoice information to be sent to Accounts Payable and payment information to be returned from Accounts Payable through a batch process.  The tool allows for two way integration and currently only supports file based integrations.

 

The diagram below illustrates the connectivity:

 

clipboard_e049e626d03bf4a2c9018dc00d3ab0b12.png

Files can either be placed on a network drive or placed on a server leveraging FTP or SFTP protocols.  They landing zone can be Mitratech provided or client provided depending on the client’s requirements.

 

AP Link allows for either submitting a flat, delimited file or submit a structured file using the “template generated file” method.  This document will cover templates in detail.

1.   Output Tab of AP Link

The output tab of the AP Link tool provides the details about importing data received from AP system and updating invoices with payment information.  The information below shows the settings.

1.1.                AP Extract Settings

 

Schedule: <schedule is generally once per day>

Output Interface Type:  File

Output File Location: FTP / SFTP  - SFTP will be used in most instances

To save locally, use /var/tmp

Output File Path: <Client to Provide>

Use Secure FTP: Checked

URL: <Client to Provide>

Username: <Client to Provide>

Password: <Client to Provide>

Known Hosts Location: blank

File Name:  This is the name of the file and can either be static or can be based on attributes.  Typically there will be a static part and then a date part. To include todays date at the end of the file use ${.now?date}.

File Extension: <This is generally static like txt or csv>

File Format: <The options are Delimited File, Fixed Width File, and Template Generated File.>

Delimited and Fixed Width files will produce one line per invoice.  Template Generated files will allow for multiple lines per invoices to handle split allocations.

If Delimited File is selected, the following information is needed.

Delimiter:

 Tab

 Semicolon

 Comma

 Space

 Other

 

Row Delimiter:

 Carriage return-line feed combination

 Carriage   return

 Line feed

 Tab

 Other

             

 

Text Qualifier:

 

Date Field Format:

 

File contains header row

If Fixed Width is selected, the following information is needed.

 

Padding:

Justification:

Text Fields:

 Space

 Zero

 Other

Numeric Fields:

 Space

 Zero

 Other

Date Fields:

 Space

 Zero

 Other

 

Date Field Format:

 

File contains header row

 

 

If Template Generated File is selected, then the following information is needed.

Template File:

 

 

File Contains Header Row: <Yes or No depending on whether or not one is provided.  Header Rows will only be used for Fixed Width or Delimited Files>

Invoices for Collection:  Ready to Send

The AP Link tool uses a search view to determine which invoices will be extracted.  This search view will look for invoices that are ready to be sent.  Typically the search view will be

ReadytoSendtoAPIN =”Yes”

And

DateSentToAPIN = null

And

Posting Status = “Posted”

 

Generally the client will have some business rule to determine setting ReadytoSendtoAPIN equal to yes.  Many clients have rules that only US Dollar invoices can be sent.

 

Store File in Documents: <This option will save a copy of the exported file in the documents folder of the AP Settings object.  The best practice is to set this to Yes.>

Retention: <Number of days to keep the file>

Sent to AP Date:  SentToAPIN (Custom field used to track when an invoice is sent)

 

1.1.                Mapping

This is the section which allows specific fields on the invoice to be mapped and used in the output file.  Most commonly, the following fields will be sent to an accounts payable system.

·       Invoice Number

·       Invoice Date

·       Vendor Number

·       Net Invoice Total

·       Currency

·       Invoice Key (see note below)

·       Some accounting information like GL Code or Cost Center. 

 

Because AP Link is mostly an output tool, it is important that all calculations done in advance and the data saved where it is accessible to the invoice.

 

 

Each of the fields to be used in the output is built into the mapping.

 

 

 

In order to map a field, the following process is used within the tool.

 

First, in the mapping section, click the mapping button circled below.

 

 

 

Select the desired field in the invoice by drilling down to the field.

 

 

 

Click off the field list box.  Give the Field a Name, Order for where it appears in the output and length.  Length is only used in Fixed Width outputs, but must be included.

 

 

This process is completed for each file in the output. 

 

It is important to convince the client that they should include the invoice primary key in the output file as well as the invoice number.  The invoice primary key can be used for the return file of the invoice payments for invoice matching.  Otherwise the system tries to use Invoice Number, Invoice Date and Vendor Name.  Since vendor names can change, this can be a problem.

 

However, the Invoice Primary Key is not a field that is available in the list of system fields in the mapping.  Therefore, you will need to do the following to make the primary key available.  First, create a custom field for InvoiceKey.  Second, create a prepopulation rule to populate this with the Primary Key.  Third, use this custom field in your mapping.

 

 

2.   Input Tab of AP Link

2.1.                AP Extract Settings

Schedule: Generally once per day

Input Interface Type:  File

Incoming File Location: FTP / SFTP (generally SFTP will be used)

Use Secure FTP: Checked

URL: <provided by the client if hosted>

Username: <provided by the client>

Password: <provided by the client>

Known Hosts Location: blank

File Name:  The system can look for a file that either begins with a constant or contains a constant. 

File Extension: generally will be txt or csv

File Format: Delimited Text or Fixed width

If Delimited Text is selected then the following inputs are required.

Delimiter:

 Tab

 Semicolon

 Comma

 Space

 Other

 

Row Delimiter:

 Carriage return-line feed combination

 Carriage return

 Line feed

 Tab

 Other

 

Text Qualifier: 

 

Date Field Format:

 

File contains header row

If Fixed Width is selected, then the following inputs are required.

 

Store File in Documents: checked

Retention Period: 30 Days

Upon Success:  Delete file in Current Directory

 

2.1.                Mapping

 

The Mapping section is where available fields are mapped and made available for output.

 

 

 

 

3.   Template Generated Files

One of the biggest features in AP Link is the ability to create a “Templated Generated File” that will allow for creating structured files that will support cases that are beyond just having a flat file on one line per invoice.  Typically, clients will want to have a structure where there is a header line for each invoice and then a detail line for each cost center associated with the invoice.  Each client can have a unique AP file format that can contain any possible set of fields.  Frequently these file formats will contain a lot of fixed values or unused values because they were developed to handle all possible cases of accounts payable scenarios including those not used by legal.

 

By way of illustration, we will work through a basic example that does not have a lot of extraneous details.  The actual clients files will be different.

 

Consider the following sample invoice file:

HDR|LGL|20170418

INV|28271|882719|20170307|42000|LGL|22222

DTL|1|25200|A2918

DTL|2|16800|B2817

INV|34200019|789200|20170309|50000|LGL|22223

DTL|1|50000|A2918

TRL|92000|92000|7

 

This is a pipe delimited file that is broken up into invoice headers, invoice details, and a trailer.  Pipe Delimited files are generally the easiest to deal with, but fixed width or XML could be created.

The file header line has the following fields.

Line Type

Fixed Value HDR

Source

Fixed Value LGL

Current Date

Today’s date

 

The invoice line has the following fields with mapping to TeamConnect

Line Type

Fixed Value HDR

Invoice Number

numberString field of Invoice

Vendor Number

Custom Field on Vendor Category of the contact

Invoice Date

invoiceDate field of the invoice

Invoice Amount

Net Invoice Total of the invoice

Source

Fixed value LGL

Invoice Key

Primary Key of the invoice

 

The Detail line has the following fields

Line Type

Fixed Value DTL

Line Number

Sequential number of the invoice line

Line Amount

Amount field from the invoice matter cost center object

Cost Center

Cost Center field from the invoice matter cost center object

 

The Trailer line has the following fields

Line Type

Fixed Value TRL

Total Amount

Total of all invoice header lines

Total Amount Lines

Total of all invoice detail lines

Line Count

Count of the number of lines in the file

 

 

The Template Generated Files function uses a template mapping language called Free Marker for generating the templates.  You can find additional details about it at http://freemarker.org/.   It is a tool that allows for taking data and mapping it into structured output.

 

The general process for creating a template is to use the sample file as your guide and then replace elements of the file with the appropriate mapping elements.  Any fixed items just stay fixed.  The result will be the template that you will upload into the AP settings object.  It is pretty much an XML file with tags for the various elements.

 

First, you want to use the mapping to in the application to map any available fields.

 

 

Second, there are some required tags that are needed to define the structure of the template:

<@apoutput></@apoutput> [These go at the start and end of the template]

<@fileheader></@fileheader> [These are used if you have a header line at the beginning of the file]

<@filetrailer></@filetrailer> [These are used if you have a trailer line at the end of the file]

<@invoiceheader></@invoiceheader> [This defines an individual invoice record]

<@invoicedetail></@invoicedetail> [This will be placed within the header tags for the detail lines]

 

 

When you are referencing a mapped field in the tool, you define it as ${field name} as you have it in the application.

 

If you are mapping to cost centers then you need to reference your cost centers through a custom field on the invoice.  There is a more detailed discussion of this topic later in the document.

 

The example below shows our sample file from above with the section tags in place and the fields mapped for the available fields from the mapping page.

 

<@apoutput>

<@fileheader>

HDR|LGL|${.now?date}

</@fileheader>

<@invoiceheader>

<@set var="matterAlloc" record=invoice fieldName="MatterInvoiceAllocationIN"/>

<#assign costCenterFieldName = "CostCenterINAL">

<#assign AmountFieldName = "AmountINAL">

<#assign ccTP = "INAL">

HDR|${INVOICE_NUMBER}|${VENDOR_NUMBER}|${INVOICE_DATE?string["MMddyyyy"]}|${INVOICE_AMOUNT}|LGL|${INVOICE_KEY}

<@invoicedetail>

detailList=matterAlloc.getChildListForObjectDefinition(ccTP)>

<@set var="costCenter" record=invoiceDetail fieldName=costCenterFieldName/>

<@set var="INVOICE_DETAIL_AMOUNT" record=invoiceDetail fieldName=AmountFieldName/>

 

DTL|${invoiceDetailIndex?c}|${INVOICE_DETAIL_AMOUNT}|${CostCenter}

</@invoicedetail>

</@invoiceheader>

<@filetrailer>

TRL|${TOTAL_INVOICE_AMOUNT}|${TOTAL_INVOICE_DETAIL_AMOUNT}|${RECORD_COUNT?c}

</@filetrailer>

</@apoutput>

 

 

 

4.   Example File – XML

A client has an xml file output format.  They are also using the Financial Management Module with invoice cost centers.  Each invoice has a header record and then a line record for the allocation information

 

 

<@apoutput>

<@fileheader>

<?xml version="1.0" ?>

<VCHR_INFO>

</@fileheader>

<@invoiceheader>

<@set var="matterAlloc" record=invoice fieldName="InvoiceOrganization"/>

<#assign AmountFieldName = "AmountChargedCO">

<#assign AccountFieldName = "ACCOUNT">

<#assign BusinessUnitFieldName = "BUSINESS_UNIT_GL">

<#assign Chart2FieldName = "CHARTFIELD2">

<#assign DeptIDFieldName = "DEPTID">

<#assign OperatingUnitFieldName = "OPERATING_UNIT">

<#assign ProductFieldName = "PRODUCT">

<#assign VatFieldName = "TOTAL_VAT_AMOUNT">

<#assign ccTP = "ICC$">

  <VOUCHER_HEADER>

    <BUSINESS_UNIT>${AP_BU}</BUSINESS_UNIT>

    <INVOICE_ID>${INVOICE_NUMBER}</INVOICE_ID>

    <INVOICE_DT>${INVOICE_DATE?string["MMddyyyy"]}</INVOICE_DT>

    <GROSS_AMT>${GROSS_AMT}</GROSS_AMT>

    <VENDOR_ID>${VENDOR_ID}</VENDOR_ID>

    <ORIGIN>LTC</ORIGIN>

    <PYMNT_MESSAGE>Thank you for your support</PYMNT_MESSAGE>

    <DESCR254_MIXED>${PRIMARY_KEY}</DESCR254_MIXED>

    <VAT_ENTRD_AMT>${TOTAL_VAT_AMOUNT}</VAT_ENTRD_AMT>

    <TOTAL_OTHER_TAX_AMOUNT>${OTHER_TAX_AMOUNT}</TOTAL_OTHER_TAX_AMOUNT>

    <CURRENCY_ID>${CURRENCY_ID}</CURRENCY_ID>

<@invoicedetail detailList=matterAlloc.getChildListForObjectDefinition(ccTP)>

<@set var="AllocDEPTID" record=invoiceDetail fieldName=DeptIDFieldName/>

<@set var="AllocACCOUNT" record=invoiceDetail fieldName=AccountFieldName/>

<@set var="AllocBUSINESS_UNIT_GL" record=invoiceDetail fieldName=BusinessUnitFieldName/>

<@set var="AllocOPERATING_UNIT" record=invoiceDetail fieldName=OperatingUnitFieldName/>

<@set var="AllocAMOUNT" record=invoiceDetail fieldName=AmountFieldName/>

<@set var="AllocCHARTFIELD2" record=invoiceDetail fieldName=Chart2FieldName/>

<@set var="AllocPRODUCT" record=invoiceDetail fieldName=ProductFieldName/>

<@set var="AllocVAT" record=invoiceDetail fieldName=VatFieldName/>

    <VOUCHER_DETAIL>

      <AMOUNT>${AllocAMOUNT - AllocVAT}</AMOUNT>

      <DEPTID><#if AllocDEPTID??>${AllocDEPTID}</#if></DEPTID>

      <ACCOUNT>${AllocACCOUNT}</ACCOUNT>

      <PRODUCT><#if PRODUCT??>${PRODUCT}</#if></PRODUCT>

      <BUSINESS_UNIT_GL>${AllocBUSINESS_UNIT_GL}</BUSINESS_UNIT_GL>

      <BUSINESS_UNIT_PC><#if AllocBUSINESS_UNIT_PC??>${AllocBUSINESS_UNIT_PC}</#if></BUSINESS_UNIT_PC>

      <PROJECT_ID><#if PROJECT_ID??>${PROJECT_ID}</#if></PROJECT_ID>

      <ACTIVITY_ID><#if ACTIVITY_ID??>${ACTIVITY_ID}</#if></ACTIVITY_ID>

      <RESOURCE_TYPE><#if RESOURCE_TYPE??>${RESOURCE_TYPE}</#if></RESOURCE_TYPE>

      <RESOURCE_CATEGORY><#if RESOURCE_CATEGORY??>${RESOURCE_CATEGORY}</#if></RESOURCE_CATEGORY>

      <RESOURCE_SUB_CAT><#if RESOURCE_SUB_CAT??>${RESOURCE_SUB_CAT}</#if></RESOURCE_SUB_CAT>

      <OPERATING_UNIT>${AllocOPERATING_UNIT}</OPERATING_UNIT>

      <CHARTFIELD2><#if AllocCHARTFIELD2??>${AllocCHARTFIELD2}</#if></CHARTFIELD2>

      <VAT_LN_ENT_AMT>${AllocVAT}</VAT_LN_ENT_AMT>

    </VOUCHER_DETAIL>

</@invoicedetail>

  </VOUCHER_HEADER>

</@invoiceheader>

<@filetrailer>

</VCHR_INFO>

</@filetrailer>

</@apoutput>

5.   Example File – Pipe Delimited

 

A client has a structured file with file header, invoice header per invoice, invoice detail per allocation and file trailer.  They have a custom invoice cost allocation module. A lot of the data in the file is “fixed values” that do not change.

 

<@apoutput>

<@fileheader>

0|PFI|LAW|TEAMCONNECT|${.now?date}|01

</@fileheader>

<@invoiceheader>

<@set var="matter" record=invoice fieldName="MatterIN"/>

<@set var="matterAlloc" record=invoice fieldName="MatterInvoiceAllocationIN"/>

<#assign percentFieldName = "AllocationPctINAL">

<#assign costCenterFieldName = "CostCenterINAL">

<#assign ClientOrgCodeName = "OrgCodeINAL">

<#assign EntityFieldName = "EntityINAL">

<#assign AmountFieldName = "AmountINAL">

<#assign ccTP = "INAL">

<@set var="vendorNumber" record=VENDOR fieldName="VendorNumberCONT" category="CONT_EXTE_SUVE"/>

<@set var="vendorLocation" record=VENDOR fieldName="VendorLocationCONT" category="CONT_EXTE_SUVE"/>

1|${invoiceIndex?c}|${INVOICE_NUMBER}|${TRANSACTION_TYPE}|${INVOICE_DATE?date}|${vendorNumber}|${vendorLocation}|${INVOICE_AMOUNT}| |${VENDOR_NAME}|PFI_DOMESTIC_STANDARD|${INVOICE_NUMBER}| |N| | | | | | | | | | | | | |N| | |

<@invoicedetail detailList=matterAlloc.getChildListForObjectDefinition(ccTP)>

<@set var="percent" record=invoiceDetail fieldName=percentFieldName/>

<@set var="costCenter" record=invoiceDetail fieldName=costCenterFieldName/>

<@set var="ClientOrgCode" record=invoiceDetail fieldName=ClientOrgCodeName/>

<@set var="Entity" record=invoiceDetail fieldName=EntityFieldName/>

<@set var="INVOICE_DETAIL_AMOUNT" record=invoiceDetail fieldName=AmountFieldName/>

2|${invoiceIndex?c}|${invoiceDetailIndex?c}|ITEM|${INVOICE_DETAIL_AMOUNT}|${AP_DESC}|${Entity}|${account(ACCOUNT)}|99999|11|99999|${ClientOrgCode}|99999|9999|999999|PFI_DOMESTIC_SHIP_TO|NJ|07102| | | | | | | | | | | | |

</@invoicedetail>

</@invoiceheader>

<@filetrailer>

9|${TOTAL_INVOICE_AMOUNT}|${TOTAL_INVOICE_DETAIL_AMOUNT}|${RECORD_COUNT?c}

</@filetrailer>

</@apoutput>

<#function account treePosition>

        <#if treePosition == 'ROOT_1111'>

               <#return '76441'>

        <#elseif treePosition == 'ROOT_2222'>

               <#return '76440'>

        <#elseif treePosition == 'ROOT_3333'>

               <#return '70020'>

        <#elseif treePosition == 'ROOT_4444'>

               <#return '76205'>

        <#elseif treePosition == 'ROOT_5555'>

               <#return '77005'>

        <#else>

               <#-- Should never reach here -->

               <#return ''>

        </#if>

</#function>

 

6.   Working with Cost Allocations

One of the key requirements that clients have is the ability to have cost allocations broken out in their AP Feed.  If a client is using the Financial Management module, at rule is needed that ties the Matter Invoice Cost allocation record to the invoice.  Out of the box, it does not do this.  You need a custom object field on the invoice referencing the Matter Invoice Cost allocation record.  A custom rule on creating of the cost allocation sets the value of this field on the corresponding invoice.

 

In the template, you need to refer to this field.  In the case below, it is MatterInvoiceAllocationIN

 

<@set var="matterAlloc" record=invoice fieldName="MatterInvoiceAllocationIN"/>

<#assign percentFieldName = "AllocationPctINAL">

<#assign costCenterFieldName = "CostCenterINAL">

<#assign ClientOrgCodeName = "OrgCodeINAL">

<#assign EntityFieldName = "EntityINAL">

<#assign AmountFieldName = "AmountINAL">

<#assign ccTP = "INAL">

<@set var="vendorNumber" record=VENDOR fieldName="VendorNumberCONT" category="CONT_EXTE_SUVE"/>

<@set var="vendorLocation" record=VENDOR fieldName="VendorLocationCONT" category="CONT_EXTE_SUVE"/>

1|${invoiceIndex?c}|${INVOICE_NUMBER}|${TRANSACTION_TYPE}|${INVOICE_DATE?date}|${vendorNumber}|${vendorLocation}|${INVOICE_AMOUNT}| |${VENDOR_NAME}|PFI_DOMESTIC_STANDARD|${INVOICE_NUMBER}| |N| | | | | | | | | | | | | |N| | |

<@invoicedetail detailList=matterAlloc.getChildListForObjectDefinition(ccTP)>

<@set var="percent" record=invoiceDetail fieldName=percentFieldName/>

<@set var="costCenter" record=invoiceDetail fieldName=costCenterFieldName/>

<@set var="ClientOrgCode" record=invoiceDetail fieldName=ClientOrgCodeName/>

<@set var="Entity" record=invoiceDetail fieldName=EntityFieldName/>

<@set var="INVOICE_DETAIL_AMOUNT" record=invoiceDetail fieldName=AmountFieldName/>

2|${invoiceIndex?c}|${invoiceDetailIndex?c}|ITEM|${INVOICE_DETAIL_AMOUNT}|${AP_DESC}|${Entity}|${account(ACCOUNT)}|99999|11|99999|${ClientOrgCode}|99999|9999|999999|PFI_DOMESTIC_SHIP_TO|NJ|07102| | | | | | | | | | | | |

</@invoicedetail>

 

7.   Useful Functions

Some clients want a trailer line that summarizes the file.  Here are the usual contents and how to get them

 

Total amount of the invoices: ${TOTAL_INVOICE_AMOUNT}

Total Amount of the Invoice Lines: ${TOTAL_INVOICE_DETAIL_AMOUNT}

Total Number of Lines in the file: ${RECORD_COUNT?c}

 

Here are examples of how to get the line item count an invoice count.

Count of Invoice ${invoiceIndex?c}

Count of Invoice Lines ${invoiceDetailIndex?c}

8.   Formatting Dates

When adding a date field to the template, you need to make sure it is formatted properly. This example shows how to format dates. ${INVOICE_DATE?string["MMddyyyy"]}

9.   Dealing with Null Values

The templates are very fussy about null values.  If a field is called out, then the tool assumes it must be populated.  In order to allow for a field to have a null value you need to explicitly allow for it.  The example below shows the field PROJECT_ID within the if statement.  The ?? says “is Null”

 

<#if PROJECT_ID??>${PROJECT_ID}</#if>

 

 

  • Was this article helpful?