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.
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 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
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:
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:
- A hard-coded Account Number for the checking account (since in our example we have only one checking account)
- Check Number
- Check Amount
- Vendor Name.
- 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).
- 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]))))
- Check Date
Here is the setup of the 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.
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.
We hope that helps!
Please give us a call if you’ve got questions that we can help with.