By Steve Endow
Imagine needing to import SOP Invoices into Dynamics GP using Integration Manager. You have a single data file containing the invoice lines, but you need to apply specific AR and Sales accounts to each invoice, differing from the customer’s defaults.
While a standard integration with separate Header and Line sources is simple, assigning these distributions adds complexity. You could create separate integrations for each account combination, but a more elegant solution exists.
One approach, used in the past, involved a VB script to generate a separate Distribution file. However, a simpler method leverages Dynamics GP’s default account functionality.
A “Before Document” script can be used to temporarily assign the desired AR and Sales accounts as defaults for the customer. This allows the import to utilize those defaults without directly managing distributions within Integration Manager.
After the invoice is imported, an “After Document” script clears these temporary default accounts, restoring the customer’s original setup. This approach simplifies the integration process.
Here’s the sample “Before Document” script, which sets the default accounts. Remember to adjust account numbers and the customer ID source field.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| '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
|
If your customer records have existing default accounts you want to retain, the script can be modified to store and restore these values using integration variables.
UPDATE: Testing revealed that the default Customer Sales Account doesn’t apply to SOP transactions, only RM. The script has been updated to set the sales account at the system level using SY01100 instead.
The following “After Document” script clears the temporarily set accounts:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| '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
|
This approach provides a streamlined solution for handling distributions during Integration Manager imports, simplifying a potentially complex process.
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
