Dynamics GP Integration Tip: Retrieving payment details on an SOP Invoice

In this blog we are looking at how to retrieve the payment details for an SOP Invoice payment via the Dynamics GP API (eConnect layer).
In this example we have already built an integration that synchronizes Invoices from Dynamics GP to a CRM system, into a custom object.
We now want to retrieve any payment information on the Invoices and update the CRM Invoice with that information, giving visibility to the sales team of any outstanding accounts receivable issues for their customer.

Making payments in Dynamics GP

If we look at the Dynamics GP UI, to make a payment to a posted SOP Invoice in Dynamics GP, a Payment type transaction is created and then applied to the invoice(s) via the “Apply Sales Documents” functionality.

 

Retrieving Payment information from the Dynamics GP company file

We can query both the posted SOP Invoice and the Payment through the Receivables Posted Transaction operation and the RM20101 table of the Dynamics GP company database file.

The challenge is that there is no direct link between the two records queried on the same table:
To solve the problem, we can look into the RM30201/RM30202 table.  This table tracks the historical record of the applied transaction and includes information such as the target Document Number (APTODCNM), the amount to apply (APPTOAMT) and the source Document Number (APFRDCNM):
With this additional table, we can then manipulate the records by creating a Report View or a Stored Procedure to enhance our eConnect operation and/or Database query and retrieve the required payment information.