SQL stored procedure and Integration Manager 1

[GPUser05]

I have SQL stored procedure for expense invoices that I need to integrate in Great Plains. Can I call it through Integration Manager?

Any reply is highly appreciated.

Thanks in advance for your time.

 

[Luvsql]

First create a DSN within IM (Objects > DSN). Enter a name and select the sql driver. Click on Connection string and edit and paste this:

Provider=MSDASQL.1;Connect Timeout=15;Extended Properties="DRIVER=SQL Server;SERVER=MyServer;UID=UserID;PWD=Password;APP=Integration Manager;DATABASE=MyDB";WSID=NTWKSTN1; Locale Identifier=1033;Database=MyDB

Now create a new Integration, source choose Adv ODBC and define new. Choose the DSN created above. Enter Exec SPName in the query.

I can see my columns, but I'm having issues previewing my data, but it could just be the sp I picked to test.

 

[GPUser05]

Luvsql,

Thanks for your reply. I tried but got an error connection failed. For SERVER, do I need to give path to the database?
Do I need to change Locale Identifier? if yes how would I get that?

I appreciat your help.

Thanks,

 

[Luvsql]

The server is just the name of your SQL Server. The syntax was copied from a TK article, so I only edited the server, user ID, password and database fields.

 

[GPUser05]

I had problem with my userID, I used "sa" at this point just to check if I can connect and I did. I can see my columns. Thanks for the solution.
This integration is for expense invoices and I am giving Amount as Purchases and the Distribution account for debit. Do I need to give the credit amount and account also? I kept it at default but it did not take it from Vendor default.

I appreciate your help.

Thanks,

 

[Luvsql]

Here's what you'll need to do:

Create 2 sources based on the one sp. The first will be the header, grouping in the invoice#,voucher# or whatever field that will determine a unique number. The second source will be all the fields.

In the Distribution section of the integration, link the account number and the debit to the amount. Leave the credit as "use default" and then click on the options tab and make sure record source is "default non-imported" and then the correct source.

This will then update the AP Control Account from the vendor card and you won't need to import it.

 

[GPUser05]

Thanks a bunch.

I had created two sources and did the way you told for credit account. It did work and took AP control account from Vendor Card. but it just applies first debit amount to credit as I have only debit transaction amount and not invoice total.
Let me give you some information about my source file.
My source has same invoice# for all the transactions for one Vendor. it groups and totals the transactions by GL account. We might have to change our stored procedure to give invoice total.

I appreciate your help.

Thanks,

 

[GPUser05]

In integration I kept Voucher Number as default. When I run integration it assigns different voucher number for each transaction though my document number is same for all transctions. Do I need to give voucher number in integration?
I tried to use same source field as Document number, as we have done in one integration, used same source field for both but it failed with "already exists error" after first transaction.

I appreciate any help.

Thanks,

 

[GPUser05]

I had posted the problem about voucher number but it is fixed and integration is working now. Luvsql, thanks once again for your help.

This is a grate forum.

Thank you all.