Thursday, February 26, 2015

Applying Payables Documents in SmartConnect Integrations

Today we're presenting our first ever "Tech Thursday" (since we know you can't get enough of our Tech Tuesdays) from our Director of Professional Services, Lorren Zemke.

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 DLL
Copy 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. 


SmartConnect Config File
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.


<?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 Source
We 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.
 

Destination
Our 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.



Task
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.NET


dim 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;
}


Map Execution
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.

Caveats
This is a list of items to consider when using this method to apply payables documents

  • The Manual Payments or Credits being applied must be unposted. 
  • The unposted Manual Payments or Credits can only be applied to an open (posted) document, such as an invoice or debit memo.
  • 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.
Precipio Services
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/


Questions? Feel free to comment below or send an email to sales@eonesolutions.com!