Integration Manager VBScript (need help)

(1) Share

Report

Posted on by Community Member

Hello,

I am integrating invoices in the Sales Order processing module in GP.

I need help with entering some scripting code after a document is inserted. (The script needs to run once after each document is inserted)

I want it to copy the distribution reference from the sales distribution account (that I'm integrating) and copy it to the default non-imported accounts receivable distribution reference.

I want the distribution reference for each record to mirror each other basically.

Any help would be appreciated.

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

L Vail 65,271 on at

Like (1)

Report

Hi,

I think you'll need to make an external connection to the database in your VB script and then copy the DistRef field value from the 16384 SEQNUMBR to the 32768 SEQNUMBR for the same SOPNUMBE. You can do it for each document or you could get fancy and do it at the end. Does this sound like it will work for you?

Kind regards,

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

If you have the code I can use I can give it a shot.

I only use tab delimited file i create from excel to use with integration manager to upload those invoices into GP.

Was this reply helpful? Yes No
Suggested answer

MG-16101311-0 26,225 on at

Like (1)

Report
Sorry for the delay in getting back to you, but in essence, Leslie is correct. You will need to create a connection to the database to update the distribution reference. Here's what I have in mind.

1) You will first need to get the invoice number from the source query and store this in a global variable. You can do this in the Before Document Commit event script, as follows:

SetVariable "gblSopNumber", SourceFields("SomeSourceQuery.DocNumberField")

2) Then you will need to add a script to the After Document event script, as follows:

InvoiceNumber = GetVariable("gblSopNumber")
'Create a new connection object Set MyCon = CreateObject("ADODB.Connection") 'Open the connection to the database MyCon.ConnectionString = "database=" & GPConnection.GPConnInterCompanyID GPConnection.Open(MyCon) 'Execute script MyScript = "declare @DistRef varchar(40);" & _ "select @DistRef = DistRef from SOP10102 WHERE SOPTYPE = 3 AND SOPNUMBE = '" & InvoiceNumber & "' AND DistRef <> '';" &_ "update SOP10102 set DistRef = @DistRef WHERE SOPTYPE = 3 AND SOPNUMBE = '" & InvoiceNumber & "' AND DistRef = '';" Call MyCon.Execute(MyScript) 'Close the connection Call MyCon.Close 'Release the object Set MyCon = nothing

If you are not importing the invoice numbers, this is, you are letting GP assign the invoice numbers, then the above script will need to change slightly. I have not tested the script above, but I see no reason for it not to work. If you have any difficulties let me know.

Was this reply helpful? Yes No