Setting distributions for a Integration Manager single file SOP Invoice import using VB Script

By Steve Endow

Let's say you want to import some SOP Invoices into Dynamics GP using Integration Manager.  And let's say that you have a single data file that contains the invoice lines.

Setting up a standard integration with separate Header and Line data sources is fairly straightforward.

But then you realize that you need to set the distributions for each invoice.  You need to assign different AR accounts and different Sales accounts to the invoices.  The customer does not have any default accounts for their GP customers, and you need to setup 3 different integrations, each of which will post to a different AR and sales account.  Fortunately, all invoices for each integration will be assigned to the same AR and sales account--so that was some consolation.

I'm actually a little rusty with this type of IM challenge.  Maybe there is a better way to accomplish this, but the last time I had to do this, about 9 or 10  years ago, I wrote a Before Integration VB Script that read the invoice data file and generated a Distribution file.

Not wanting to dig through 10 year old client files to find my ancient script, and not really wanting to have to add a Distribution data source to this integration, I thought of a hack.

What if we were able to use the customer default accounts so that IM would use those accounts for the invoice distributions?  That would allow us to avoid touching the distributions in the IM integration.  How would we do that?

Well, what if we had a Before Document script that set the default AR and Sales distributions for the customer?  We know which AR and Sales account should be used for each integration, and all invoices in that integration will use the same accounts, so we should be able to set the default account for each customer right before each invoice is imported.

But after we set the default AR and Sales account for the customer, and after the invoice is imported, then that customer has default accounts that we actually don't want as the defaults.  So why not create a similar After Document script to clear the default accounts?  Since this customer does not have any default customer accounts, we just wipe out the AR and Sales accounts for the customer.

So here is the script that I came up with.  I pulled it together and handed it off to a partner to test, so there might be a few bugs or rough edges, but you should get the drift.

Here is the Before Document script, which sets the default account for the customer.  You would change the account numbers and the source field for the Customer ID.

If your customer records have default accounts that you want to preserve, you could query those, store them in integration variables, and then set them back in the After Document script rather than clearing them.


UPDATE & CORRECTION:  So after some testing, the partner found that the default Customer Sales Account is not used for SOP--only for RM.  This makes sense, but was annoying to discover.  So instead of setting the sales account at the customer level, it can be set at the system level.  I've modified the scripts to reflect the update to SY01100 for the sales account.


'9/10/2014
'IM Before Document Script to set AR and Sales accounts for customer prior to IM invoice import

strIntercoID = GPConnection.GPConnInterCompanyID

'Get a connection object.
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "database=" & strIntercoID
GPConnection.Open(objConn)

'Create an ADO command object.
Set objCmd= CreateObject("ADODB.Command")
objConn.CursorLocation = 3

'set the database to the currently logged in db.
objConn.DefaultDatabase = strIntercoID
objCmd.ActiveConnection = objConn

objCmd.CommandType = 1

strARAccount = "000-1200-00"
strSalesAccount = "000-4100-00"

'Read customer ID
strCustomerID = Trim(SourceFields("InvoiceData.Customer ID"))

strSQL1 = "UPDATE RM00101 SET RMARACC = (SELECT ACTINDX FROM GL00105 WHERE ACTNUMST = '" & strARAccount & "') WHERE CUSTNMBR = '" & strCustomerID & "'"

strSQL2 = "UPDATE SY01100 SET ACTINDX = (SELECT ACTINDX FROM GL00105 WHERE ACTNUMST = '" & strSalesAccount & "') WHERE SERIES = 5 AND SEQNUMBR = 400"

'Set and execute the queries
objCmd.CommandType = 1 '1 = Text

objCmd.CommandText = strSQL1
objCmd.Execute

objCmd.CommandText = strSQL2
objCmd.Execute



And here is the After Document script which clears the accounts for the customer.


'9/10/2014
'IM Aftwer Document Script to clear AR and Sales accounts for customer after IM invoice import

strIntercoID = GPConnection.GPConnInterCompanyID

'Get a connection object.
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "database=" & strIntercoID
GPConnection.Open(objConn)

'Create an ADO command object.
Set objCmd= CreateObject("ADODB.Command")
objConn.CursorLocation = 3

'set the database to the currently logged in db.
objConn.DefaultDatabase = strIntercoID
objCmd.ActiveConnection = objConn

objCmd.CommandType = 1

'Read customer ID
strCustomerID = Trim(SourceFields("InvoiceData.Customer ID"))

strSQL1 = "UPDATE RM00101 SET RMARACC = 0 WHERE CUSTNMBR = '" & strCustomerID & "'"

strSQL2 = "UPDATE SY01100 SET ACTINDX = 0 WHERE SERIES = 5 AND SEQNUMBR = 400"

'Set and execute the queries
objCmd.CommandType = 1 '1 = Text

objCmd.CommandText = strSQL1
objCmd.Execute

objCmd.CommandText = strSQL2
objCmd.Execute

The partner is going to test it to confirm that it works for the customer, but if it does the trick, I think it's a pretty clever workaround to bothering with distributions for an Integration Manager import.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com