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!
