Issues with Sage 500 latitude and longitude on sales orders

WE have a custom Sage 500 task that creates sales orders from external data - I am seeing an issue where I get  an error in my SQL profiler when creating a sales order and calling the stored proc   spsoGetSOLineDist      That does an insert into #tsoSOLineDist with the values for   @_iShipToAddrLatitude,         @_iShipToAddrLongitude.   The issue is, that seems to fail.    I have found the different procs that create that temp table and I noticed that one of them  specCreateSalesOrder   does not create the columns for the latitude and longitude

(does not have  ShipToAddrLatitude DECIMAL(9,6) NULL,
ShipToAddrLongitude DECIMAL(9,6) NULL)

where all the other ones seem to.    Because of that I have issues because the insert can't succeed cleanly.    I am wondering it this was an oversite or something with that proc.    

  • 0

    Looking at Sage 500 2023, stored procedure specCreateSalesOrder used to belong to Sage's old eCommerce process which would explain why it wasn't updated to include the new columns for Latitude and Longitude.  Sage no longer maintains that code as they no longer offer the eCommerce module.  If your custom task is using this stored procedure, you can easily modify the table definition in it to include the two new columns.   Now why Sage added those columns I cannot answer as I haven't found anything yet that actually uses them in the client.  Maybe someone at Sage could give more info on that.

  • 0 in reply to LouDavis

    I believe that Sage added the Latitude and Longitude to be used in tax calculations with Avalara.  So, the only use of them would be if you are using that as your tax provider for Sage 500 ERP.

  • 0

    I typically wrap my code around a call to spSOapiSalesOrdIns, which is being updated and contains the longitude and latitude columns and variables. You could also create a new stored procedure to import the orders based on updates to specCreateSalesOrder that correspond to your data requirements.

    The coordinates can be updated manually and I believe they are maintained by Avalara. They also can be populated with a call to the Google maps API using a front-end customization or back-end through SSIS. The newer USPS address validation does not populate this data.

  • 0

    As Russ pointed out, it was likely done for external applications and is an attribute of an address, which is not module or document specific. 

    Having geocoded information can be very valuable depending upon the application.  I worked at a company that contained "mapping" information in the database that housed every address in the state of California.  Prior to geocoding, it was mapped by Thomas Brothers' map page (I think they still actually print a couple of them).  Moving to geocoding allowed us to then implement a "centroid" search defined by the user's desired distance to find properties within x miles of a target address.  The real estate appraiser application then gave better answers and allowed us to no longer maintain Thomas Brothers map pages relationships.  This is assumed today, but back in the 80's/90's not so much.

    In 500, I could picture something like using the information (if it was populated) to help determine delivery routes or vendor distances.  But as far as I know, there is no 500 native usage of the data.  I was not originally involved with its design or implementation so I do not have the history knowledge of the whys or whens.

  • 0 in reply to Ramon M.

    That's exactly what I did for a client that had a requirement to schedule their trucks of cement from various facilities to client sites using the Google APIs.