SQL Experts Only: Tips & Tricks

I recently assisted a customer in adding a user-defined-field (UDF) to the CI_Item table to add a custom ship code.  The addition of the UDF is a routine process we do frequently for customers.  What made the request unique was they wanted the open Sales Orders, Sales Order History and AR Invoice History tables to be updated with the new custom values for the line items.  Given a period of time for the customer to populate the newly created field with data using Inventory Item Maintenance function in Sage 100 Premium, we created the following scripts to update the information:

To run the queries open the SQL Management Studio and click the “New Query” button.  The provided scripts can be copied and pasted in to the new query screen or the management studio; only needing to change the user-defined to your unique field name.

To update open sales order lines:

UPDATE S

SET S.UDF_SHIP_REGION = I.UDF_SHIP_REGION

FROM SO_SalesOrderDetail S inner join CI_Item I

ON S.ItemCode = I.ItemCode

 

To update open sales order history lines:

UPDATE S

SET S.UDF_SHIP_REGION = I.UDF_SHIP_REGION

FROM SO_SalesOrderHistoryDetail S inner join CI_Item I

ON S.ItemCode = I.ItemCode

 

To update open sales order history lines:

UPDATE A

SET A.UDF_SHIP_REGION = I.UDF_SHIP_REGION

FROM AR_InvoiceHistoryDetail A inner join CI_Item I

ON A.ItemCode = I.ItemCode

 

To update uposted Sales Order Invoice lines:

UPDATE S

SET S.UDF_SHIP_REGION = I.UDF_SHIP_REGION

FROM SO_InvoiceDetail S inner join CI_Item I

ON S.ItemCode = I.ItemCode

 

Do you need EXPERT help for you Sage 100 ERP software? Oasis Solutions Group can help!

 

 

You may also like

Louisville, KY – July 1, 2024 – Oasis Solutions, an award-winning provider of ERP and accounting software solutions, is proud ...

5 Advantages to choosing Oasis Solutions as your NetSuite Solution Provider  NetSuite is considered the #1 cloud ERP in the ...

Stay tuned on our ERP path!

Subscribe to our newsletter and get the latest news