Share with others

Banks use Positive Pay tools to reconcile the checks that a company issues with those it presents for payment. Here is how we use the powerful Generic Inquiry function of Acumatica to design a query that pulls the Positive Pay records.

Use these steps if you need to create a simple, PostivePay record set from your Acumatica check run where you can export the results to Excel, then upload that file to your bank’s portal.

Archival footage of information delivery systems that pre-date Positive Pay portals

Instructions

Positive Pay tables

We must first understand the tables involved:

  • PX.Objects.AP.APPayment (to pull a list of payment records)
  • PX.Objects.AP.APContact (to pull Remit-To Name and in this example, the ATTN field because it contains the name of a factor for the original payee)
  •  PX.Objects.AP.APAddress (to pull and address fields, in case the bank requires)

Positive Pay tables

Positive Pay Table Relations

Because remit-to information is needed in this case, we are linking to APAddress and APContact from APPayment. The behavior in Acumatica’s tables is that the Default Remittance information (as stored on the Payment Settings tab of the vendor master) is stored in the APAddress and APContact tables.

However, if on a specific payment the remittance is overridden, then a new record gets added to APAddress and/or APContact depending on whether you overrode one or both.

Your table links should be configured as such:
from APPayment, link to APAddress where remitAddressID = addressID
from APPayment, link to APContact where remitConactID = contactID

Define Parameters

In our case, the customer has a single checking account and wants to upload a Postive Pay file once a day. So, we defined these parameters as shown in the screenshot:

  • PaymentDateBegin
  • PaymentDateEnd

Positive Pay parameters

Define Conditions

As you can see above, we also defined how the data should be filtered:

APPayment.DocDate >= PaymentDateBegin
APPayment.DocDate <= PaymentDateEnd
APPayemnt.DocType must not be a Debit Adj, Voided Refund or Voided Check

Define Results Grid Output

“Results Grid Output” refers to the columns of the Excel file that will be uploaded as a Positive Pay file on the Bank’s portal. Here is what we defined for the results grid output:

  1. A hard-coded Account Number for the checking account (since in our example we have only one checking account)
  2. Check Number
  3. Check Amount
  4. Vendor Name.
    1. In our example, remittance goes to a factor rather than the original payee. The bank required that we concatenate the original payee (stored in the Remittance Contact Full Name field) and factor (stored in the Remittance Attention field).
    2. We must test for whether the Attention field is in use. If it is NULL, then concatenation of the two fields returns a NULL even when Full Name has a value. This formula tests for that: =Concat( [APContact.FullName], (IIf((IsNull([APContact.Attention], ‘x’))=’x’,’ ‘,Concat(‘ – ‘,[APContact.Attention]))))
  5. Check Date

Here is the setup of the Results Grid:

Positive Pay Results Grid

Check Positive Pay Inquiry Results

Generic Inquiry results show a few records so we can have a sanity check on our assumptions. Once the “from” and “to” payment dates are entered, results are shown. Here’s what we’re seeing:

  • Check 1004 shows an example of where the Remittance Fullname and Attention were overridden on the individual payment transaction.
  • Check 1005 is an example of where the Remittance was not overridden on the payment, so the “Fullname” is coming from the Vendor Master remittance, but there was no value in the Attention field on the Vendor Master.

Positive Pay Grid Results

Export Positive Pay Results

Finally, by clicking the Export to Excel button on the toolbar, an Excel file is created. Now, you are ready to upload the file to the bank portal.

Positive Pay Export

We hope that helps!
Please give us a call if you’ve got questions that we can help with.

Share with others