Applying Payables Documents in SmartConnect Integrations
One of the most common requests we receive at eOne is how someone can apply payments checks or documents using SmartConnect. After all, you can do it with Receivables, so why not Payables? SmartConnect uses Microsoft’s eConnect API to send data to Dynamics GP, which means the data available for update from SmartConnect are based on what is provided through eConnect.
We can certainly create new data points to be used within SmartConnect by using our Node Builder product to define the import tables, replicate the business logic used within the product, and publish the new nodes to create and update data in Dynamics GP. The hard part of that process is replicating the business logic however.
Thankfully, my colleague Steve Endow, owner of Precipio Services, was kind enough to figure out the business logic for applying Payables documents and wrap it all in a nice little DLL. This means we can now use a .NET Script Task within SmartConnect to call a DLL that will apply Payables Documents.
The following steps illustrate the process of configuring SmartConnect to work with the DLL.Setup
There are a few setup items required for SmartConnect to be able to use the AP Payment API from Precipio Services which will only work with SmartConnect 2014 or higher.
These steps will have to be completed for each location where SmartConnect is running. Any machine running the User Interface, Web Service or Windows Service will need to go through this setup.
Precipio DLLCopy the Precipio.APApply.dll to the SmartConnect folder – by default this path is C:\Program Files (x86)\eOne Solutions\SmartConnect. To ensure the process runs properly the DLL will need to be located on any machine running SmartConnect.
You will also need to copy the Precipion.APApply.dll to:
SmartConnect 21+
- Copy the Precipion.APApply.dll to the C:\Program Files\eOne Solutions\SmartConnect\ folder (include any location where SmartConnect has been installed)
- Copy the Precipion.APApply.dll to the C:\Program Files\eOne Solutions\SmartConnect API\bin\ folder (wherever the SmartConnect WCF Web Service is installed)
- Copy the Precipion.APApply.dll to the C:\Windows\SysWOW64\ folder (on machine where the SmartConnect eOne Windows Service has been installed)
- Copy the Precipion.APApply.dll to the C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ folder
SmartConnect 2018 and older
Items 2 and 3 will only be needed if the maps will be executed from the SmartConnect web services.
- Copy the Precipion.APApply.dll to the C:\Program Files (x86)\eOne Solutions\SmartConnect\ folder (include any location where SmartConnect has been installed)
- Copy the Precipion.APApply.dll to the C:\Program Files (x86)\eOne Solutions\wcf\bin\ folder (wherever the SmartConnect WCF Web Service is installed)
- Copy the Precipion.APApply.dll to the C:\Program Files (x86)\eOne Solutions\www\bin\ folder (wherever the SmartConnect Web Service is installed)
- Copy the Precipion.APApply.dll to the C:\Windows\SysWOW64\ folder (on machine where the SmartConnect eOne Windows Service has been installed)
- Copy the Precipion.APApply.dll to the C:\Windows\Microsoft.NET\Framework\v4.0.30319 folder
Create a new file in the SmartConnect directory of any machine running SmartConnect and name it, eOne.SmartConnect.UI.External.exe.config. Add the following lines to the file. If SmartConnect is running, you will need to restart it.
If you are using the eOne SmartConnect Windows Service to schedule integrations, you would also need to create an eOne.SmartConnect.WindowsService.exe.config as well using the same lines below and then restart the service.
Lastly, you use the eOne.SmartConnect.RunMapConsole.exe to run integrations, you’ll need to create an eOne.SmartConnect.RunMapConsole.exe.config using the same lines as below.
<?xml version="1.0"?> <configuration> <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0"/> </startup> </configuration>
SQL
Add a new row to the SmartConnect.ScriptNamespace table to create a reference to the Library. If we are logged into SmartConnect we need to exit and log back in.
INSERT INTO SmartConnect..ScriptNamespace SELECT 'Precipio.APApply', 'Precipio.APApply', 0
Map
In this example, we are creating Payables Manual Payments that are being applied to previously created and posted Vouchers. This same process could be used after importing Vouchers or Credit Memos and applying the Payments.
Data SourceWe are using a simple data source from a CSV (comma separated values) file with the basic information required.
We are mapping the VendorId, DocDate, PaymentNumber, ApplyDocumentNumber and the ApplyAmount.
We will use the PaymentNumber as my Key Field for each new document to create.
The ApplyDocumentNumber is the vendor document number used to find the payables document in the open table.
The assumption in this integration is the Apply Amount will be the Payment Amount.
DestinationOur destination is Microsoft Dynamics GP with the Group of Payables and Node Type of Manual Checks. We are only mapping the Create manual check node.
To get the next Payables Check Number, we will create a Dynamics GP Rolling Column.
We are mapping only the required fields for the destination. For required parameters that aren’t part of my data source, we use a combination of local constants and List Options to hardcode those values.
Since we can only use this to apply one document at a time, the task we will create is a Run Script Task that will execute when the Document Succeeds.
The script task will initialize the apply process. If that is successful, it will make the call to the Apply Payment method.
VB.NETdim success as boolean dim errorResult as boolean dim errorCode as integer dim responseMessage as string dim licenseKey as string dim serverInstance as string dim userConnectType as string dim userID as string dim userPassword as string dim companyDatabase as string dim documentType as string dim apApplyTest as new Precipio.APApply.Import() licenseKey = "mykey" 'Retrieved from Precipio Services serverInstance = "eone-2014\eone" userConnectType = "SQL" 'SQL or GP userID = "sa" userPassword = "pass@word1" companyDatabase = "TWO" 'Initialize the license success = apApplyTest.Initialize(licenseKey, serverInstance, userConnectType, userID, userPassword, responseMessage) 'Display a message if the initialize fails. Only use this if running manually if not success then MessageBox.Show("Initialize: " & responseMessage) return false end if 'Apply the Payment that was just integrated to a voucher documentType = "PAYMENT" 'PAYMENT or CREDIT success = apApplyTest.ApplyPayment(companyDatabase, _VENDORID, Convert.ToDateTime(_DOCDATE), documentType, _PAYMENTNUMBER, _APPLYDOCUMENTNUMBER, _APPLYAMOUNT, errorResult, errorCode, responseMessage) if not success then MessageBox.Show("Apply: " & responseMessage) return false else if errorResult then MessageBox.Show("Apply: " & responseMessage) return false end if apApplyTest = nothing return true
C#
bool success = false; bool errorResult = false; int errorCode = 0; string responseMessage = null; string licenseKey = null; string serverInstance = null; string userConnectType = null; string userID = null; string userPassword = null; string companyDatabase = null; string documentType = null; Precipio.APApply.Import apApplyTest = new Precipio.APApply.Import(); licenseKey = "mykey"; //Retrieved from Precipio Services serverInstance = "eone-2014\\eone"; userConnectType = "SQL"; //SQL or GP userID = "sa"; userPassword = "pass@word1"; companyDatabase = "TWO"; //Initialize the license success = apApplyTest.Initialize(licenseKey, serverInstance, userConnectType, userID, userPassword, responseMessage); //Display a message if the initialize fails. Only use this if running manually if (!success) { MessageBox.Show("Initialize: " + responseMessage); return false; } //Apply the Payment that was just integrated to a voucher documentType = "PAYMENT"; //PAYMENT or CREDIT success = apApplyTest.ApplyPayment(companyDatabase, _VENDORID, Convert.ToDateTime(_DOCDATE), documentType, _PAYMENTNUMBER, _APPLYDOCUMENTNUMBER, _APPLYAMOUNT, errorResult, errorCode, responseMessage); if (!success) { MessageBox.Show("Apply: " + responseMessage); return false; } else if (errorResult) { MessageBox.Show("Apply: " + responseMessage); return false; } apApplyTest = null; return true;
When the map executes, as each Payables Manual Check is created, the post document success task will apply the Check to the Voucher. If the document fails to be created, the apply task will not be attempted.
CaveatsThis is a list of items to consider when using this method to apply payables documents
– Documents being applied to must be posted and in the Payables Open table.
– This process does not handle Multi Currency transactions.
– The import of the document may be successful while the apply task fails, in that case it can then be applied manually.
The AP Apply Library can be purchased from Precipio Services and currently supports Dynamics GP 2010 and Dynamics GP 2013. Precipio will send the license key along with the API document for the values to pass to the methods. http://precipioservices.com/
Lorren,
Thank you for a very helpful post. I am sure this will be relief for many people trying to overcome exactly this problem. One question:
If my understanding is correct, the example above assumes that we are applying one Manual Check to one Invoice. How we should modify the source data and script to apply a single Manual Check to multiple Documents?
Thanks in advance for your answer!
Peretz, You are correct, the example assumes there is one source record applied to one invoice. If you need to apply that one source record to multiple documents, then, in the script, you need to be able to loop through your apply to records.
Lorren, Thanks for your response! When I loop through to apply to multiple invoices, which data source should I be using for the applications? Specifically, am I able to include one row for each application in the source document, then group on Check Number for the sake of the Manual Check creation, but then access the expanded group within the script? Am I thinking about it the right way? Do you have any documentation on how I would do this? A simple nudge would go a long way, and I’d be happy to share the results with the community, once I figure it out.
Lorren, I don’t see a reply to Peretz’s question. If we are grouping our source data by check number to create the manual check, can we then access the original, ungrouped individual lines within the script to create the applies?
Pam,
With document success task, there is not a way to access the individual ungrouped lines.
The only option is the ungrouped lines have to be accessed again in some manner. To get around this in one case, I had to get the data out of a SQL table using .NET and looping through those records.
I just reviewed Steve Endow’s DLL documentation (as we’re trying to resolve this issue right now.) He noted this in the documentation:
“If you need to apply a payment to multiple invoices as part of a manual payment import, you will need to write an additional script that accesses the payment application data source (separate from the manual payment data source), retrieves the apply records, and loops through the multiple payment applications for each imported payment.”
“Alternately, a separate Apply Payments map could be created that reads the payment application data source and uses a script to apply payments to multiple invoices, independent of a payment import. “
Hi Lorren,
I need to know, how can I do to get Precipio.APApply.dll file?
I mean, is there available for download somewhere. I’m trying to contact with Precipio people, but I have not answer yet.
is it include with SmartConnect now?
Regards
Luis,
The DLL was written by Precipio and they are the only one’s who you can purchase it from. I will contact them to see if maybe they are out of the office.
Lorren
I noticed that in the selection “Manual Checks” was selected. Is this also available for mixed batches using “Manual Payments” where some of the payments are EFT and some are checks?