web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Update Site Specific O...
Finance | Project Operations, Human Resources, ...
Answered

Update Site Specific Order Settings (AX2009)

(0) ShareShare
ReportReport
Posted on by ChrisTackleberry 10

Hopefully this is not too much unnecessary background...

Items are set up by our Engineering Department. Recently, it was discovered that someone had been setting up the Site Specific Order settings incorrectly (correct site, but incorrect warehouse)

My team must occasionally deal with "split orders" where all lines of a sales order do not go to the same manufacturing site.

This requires us to:
1. Delete production orders.
2. Delete picking lists.
3. Manually change the site/warehouse on the appropriate Sales Lines.
4. Regenerate the production order.

The problem arises on step 3. Because the items have the incorrect warehouse specified in Site Specific Order Settings, we must perform an additional step for each line (and edit the SSOS in the inventtable)

My question is: Can the SSOS (with certain parameters) be edited en masse via an SQL update query or some other method?

i have been in contact with our support team, but they have other priorities at this time and have continually disregarded the issue.

I have the same question (0)
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,035 Super User 2025 Season 2 on at

    Hi Chris,

    It is possible to use an SQL script, but this is not recommended as data can be cached in Dynamics AX. You can use the import options to load a file with the changes. You can also write an x++ job to update the warehouse values in the Site Specific Order Settings. Whatever method you will choose, ensure you will do it first on a test environment and test carefully.

  • Guy Terry Profile Picture
    Guy Terry 28,924 Moderator on at

    Hi Chris,

    Don't worry, personally I would rather too much information than too little!

    I think what you are asking would be difficult for an end user to do, without help from someone with technical abilities or tools (and permissions).

    I can't remember how AX 2009 worked in this regard, but it might be possible for you to position the windows on your screen so you have the list of Items on the left, and the SSOS screen on the right. If you are lucky, as you select an Item on the left, the SSOS screen on the right might change to show the SSOS of the item you just selected. If this is the case, this would be the easiest way for a user to update the SSOS for multiple items.

  • ChrisTackleberry Profile Picture
    ChrisTackleberry 10 on at

    Hello André,

    Thank you for your reply. The first issue I need to solve is being able to find EVERY item with the incorrect warehouse specified on SSOS.

    I would like to write a SELECT query in SQL to do this. So far, I have been unable to link the appropriate tables.

  • ChrisTackleberry Profile Picture
    ChrisTackleberry 10 on at

    Hello Guy,

    Thank you for your reply. I have the necessary permissions and decent SQL skills, but I am having trouble linking the two necessary tables. (inventtable and inventdim)

    As for your suggestion, you are correct. However, the user who has an incorrect autofill saved has done THOUSANDS of new item creation. So going one-by-one is not a feasible solution.

  • Verified answer
    Guy Terry Profile Picture
    Guy Terry 28,924 Moderator on at

    Site Specific Order Settings are split across three tables (one each for the Purchase, Inventory and Sales tabs), and these tables also contain the Default Order Settings.

    I hope the SQL below will be useful. It will show the default Inventory Warehouse(s) of each Item in a single Company. You can change the table in the second SELECT to show the Purchase and Sales warehouses. (And I have set it to filter out Default order settings).

    To update the default Warehouse, you need to change the INVENTDIMIDDEFAULT on the correct record. The easiest way to find the correct INVENTDIMIDDEFAULT value to use is to find a record for an Item / Site which has the correct default Warehouse.

    Note - I wrote this query using an AX 2012 database. I expect the SELECT on line four will fail because AX 2009 does not have all of these fields. Please remove fields which do not exist in your database.

    DECLARE @Company nvarchar(4)

DECLARE @AllBlank nvarchar(20)

SET @Company='USMF' -- Set the AX Company Id here

SELECT @AllBlank = ID.INVENTDIMID FROM dbo.INVENTDIM AS ID WHERE ID.DATAAREAID = @Company AND ID.INVENTBATCHID = '' AND ID.WMSLOCATIONID = '' AND ID.WMSPALLETID = '' AND ID.INVENTSERIALID = '' AND ID.INVENTLOCATIONID = '' AND ID.CONFIGID = '' AND ID.INVENTSIZEID = '' AND ID.INVENTCOLORID = '' AND ID.INVENTSITEID = '' AND ID.INVENTSTYLEID = ''

SELECT SSOS.ItemId AS 'Item', IDS.INVENTSITEID AS 'Site', IDW.INVENTLOCATIONID AS 'Warehouse', SSOS.INVENTDIMIDDEFAULT AS 'Warehouse InventDimId'

FROM INVENTITEMINVENTSETUP AS SSOS --INVENTITEMPURCHSETUP INVENTITEMINVENTSETUP INVENTITEMSALESSETUP

INNER JOIN INVENTDIM AS IDS ON SSOS.DATAAREAID = IDS.DATAAREAID AND SSOS.INVENTDIMID = IDS.INVENTDIMID

INNER JOIN INVENTDIM AS IDW ON SSOS.DATAAREAID = IDW.DATAAREAID AND SSOS.INVENTDIMIDDEFAULT = IDW.INVENTDIMID

WHERE SSOS.DATAAREAID = @Company AND SSOS.INVENTDIMID <> @AllBlank

  • ChrisTackleberry Profile Picture
    ChrisTackleberry 10 on at

    THANK YOU!!!

    This gave me the missing piece to find what I needed.

    I was able to identify 84,997 records with the incorrect SSOS that I can now communicate to our DEV team so they can identify which member of our engineering team has the incorrect default set and they can import the correct information by whatever means they deem appropriate for our database.

    Here was what I ended up using based on your suggestion:

    SELECT SSOS.ItemId AS 'Item', IDS.INVENTSITEID AS 'Site', IDW.INVENTLOCATIONID AS 'Warehouse', SSOS.INVENTDIMIDDEFAULT AS 'Warehouse InventDimId'

FROM 
--INVENTITEMINVENTSETUP AS SSOS
INVENTITEMPURCHSETUP AS SSOS 
--INVENTITEMINVENTSETUP AS SSOS 
--INVENTITEMSALESSETUP AS SSOS 

INNER JOIN INVENTDIM AS IDS ON SSOS.DATAAREAID = IDS.DATAAREAID AND SSOS.INVENTDIMID = IDS.INVENTDIMID

INNER JOIN INVENTDIM AS IDW ON SSOS.DATAAREAID = IDW.DATAAREAID AND SSOS.INVENTDIMIDDEFAULT = IDW.INVENTDIMID

WHERE idw.inventlocationid = 'XXXX'

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 565 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 450 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 250 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans