Are there any stored procedure to create/update customer and customer contact?

Question

I recently started working with sage 500 api which is really a set of stored procedures. At this stage, I want to create/update a customer as well as the corresponding contact. Does anyone have any idea of what stored procedures to use for this purpose or please point me in a good direction to kick-start this process? 
 Will appreciate any kind of help because I can't find any documentation or useful stored procedures yet!

LouDavis · Accepted Answer

You are correct, if you are not using Data Migrator, you will need to manually populate the staging tables yourself and then execute the stored procedure to create the customer records. There is no stored procedure to do this as the data could be from any source (website/webservice, delimited file, Excel, etc.). If you are using delimited files or Excel, you might find it easier to use Data Migrator from the Sage 500 client to process your data as it will populate the staging tables for you and execute the stored procedure. 
 
If your requirements are different, you can use either temporary or the actual staging tables themselves to process data. There is a parameter @iUseStageTable that you can pass to the stored procedure to indicate if you are using temp tables or not. We do a lot of work with these APIs using SSIS and the first stage of the process is always populating the staging tables with the appropriate data with the second stage executing the API stored procedure.

JohnHanrahan · Answer

In the past (when I worked for a reseller) I directly updated the tables. I am not aware of an API to do updates. Sage sort of frowns on this though. Just an FYI.

LouDavis · Answer

Sage highly frowns upon direct updates to their tables. Your best bet is to use the API as includes validations necessary for creating new customers. As I mentioned before review the stored procedure to see what it actually does and what limitations it has. If necessary you can create your own modified version of this procedure to fill any gaps you find in what you need to accomplish. Simply script the stored procedure and add some identifier to the stored procedure name to identify it as yours. Do not modify the original as any updates from Sage will overwrite your modifications to it.

LouDavis · Answer

As John points out, Sage 500 does have the Data Migrator tool for this purpose. The stored procedure it uses is spARapiCustomerIns. There isn't any "formal" documentation on this stored procedure, but if you review it and read the programmer notes you should get a pretty good idea of what is required to use it, its capabilities, and its limitations.

Sage 500

Are there any stored procedure to create/update customer and customer contact?

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING