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.

  • ChrisTackleberry Profile Picture
    ChrisTackleberry 10 on at
    RE: Update Site Specific Order Settings (AX2009)

    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'

  • Verified answer
    Guy Terry Profile Picture
    Guy Terry 28,691 Super User 2025 Season 1 on at
    RE: Update Site Specific Order Settings (AX2009)

    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
    RE: Update Site Specific Order Settings (AX2009)

    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.

  • ChrisTackleberry Profile Picture
    ChrisTackleberry 10 on at
    RE: Update Site Specific Order Settings (AX2009)

    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.

  • Guy Terry Profile Picture
    Guy Terry 28,691 Super User 2025 Season 1 on at
    RE: Update Site Specific Order Settings (AX2009)

    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.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 293,276 Super User 2025 Season 1 on at
    RE: Update Site Specific Order Settings (AX2009)

    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.

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,276 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,973 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans