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

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!

  • Sage has a tool called Data Migrator which can import customers. It loads a tarCustomer record(s) from a staging table called stgCustomer. Do a quick search for stgCustomer. One of the stored procs will be the one you are looking for. There will be no documentation but it should be pretty easy to use.
  • 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.
  • Thank you for replying!

    I have been through the suggested tables and stored procedures.

    However, the stored procedures are there to migrate records from "staging" or "temporary" tables and the migration is where the validations exist. I cannot find any procedures to create records in staging tables. So does that mean that I have to make stored procedure to insert records in all the staging tables that stores information about customer(eg. StgCustomer, StgCustAdd and may be more) and then migrate them or "am I missing something"?
  • 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.
  • Now that I get a clear idea about how to "create" a record in sage 500 through API, I'll appreciate your help in exploring the process of "updating" any record. Is it that I have to make my own stored procedure to validate data(using sage 500 stored procedures) and directly update the main tables like "tarCustomer, tarCustAddr, etc." or is this something that sage 500 already provides in the form of stored procedure api?