How to Update Standard Location/Zone Automatically

It is possible to automatically update the standard location of an item with the help of an SQL Server Agent job.

In the SQL Server Management Studio expand the SQL Server Agent. (Make sure that the Server Agent is running) Right-click on the Jobs folder and select the ‘New Job…’ option.

Enter the name of the Job on the General page. Go to the Steps page to add a new step.

On the Job Step Properties page set the ‘Type’ as the Transact SQL script (T-SQL). Select the Database from the dropdown list.

Enter the update query into the Command field.

To run the job on a scheduled basis, configure a scheduling on the Schedules page.

Example: Update the standard location

To automatically update the standard location of an item to the first location where it is stored add the following query to the Command field of the Job:

UPDATE oitm SET oitm.U_PMX_DSLC = (SELECT top 1 PMX_invt.StorLocCode 
FROM pmx_invt 
JOIN pmx_ossl ON pmx_invt.StorLocCode = pmx_ossl.Code
JOIN pmx_osel ON pmx_invt.StorLocCode = pmx_osel.Code
WHERE pmx_invt.ItemCode = oitm.ItemCode AND pmx_ossl.SEQUENCE > 0
ORDER BY PMX_OSSL.SEQUENCE)

This job will update the standard location/zone (U_PMX_DSLC) of the items to a location where the item is currently stored.

If an item is stored on more than one location, the job will update the standard location to the first location the item is stored on. Locations are sorted by the lowest sequence number – alphabet.

Every location with a sequence number higher than 0 will be taken into account regardless of the location type and the warehouse.

With this job it is not possible to update the standard location to a zone.

Note: If there is no stock of the item, the Standard Location field will be emptied.