Besides listing the products released by Legal entity (see previous post), it's useful to know whether they were blocked for Inventory, Sales or Procurement. This is quite easy when doing it over the data entity:
-- check status via the data entity SELECT PSO.ItemNumber , PSO.DataAreaId , PSO.IsSalesProcessingStopped , PSO.IsInventoryProcessingStopped , PSO.IsProcurementProcessingStopped FROM dbo.InventProductSpecificOrderSettingsV3Entity PSO ORDER BY PSO.ItemNumber , PSO.DataAreaId
However, when the data entity is not available, the logic gets a bit more complex because the data are stored in 3 different tables: InventItemInventSetup, InventItemPurchSetup, respectively InventItemSalesSetup. Here's the piece of logic used to get the various statuses in AX 2009 (of course, without the JOINs on Partition) and it works also in Dynamics 365 F&O:
/* Product Specific Order Settings via JOINs */ SELECT ITM.DataAreaId , ITM.ItemId , IPS.Stopped IsProcurementProcessingStopped , ILS.Stopped IsInventoryProcessingStopped , ISS.Stopped IsSalesProcessingStopped FROM dbo.InventTable ITM LEFT JOIN dbo.InventItemPurchSetup IPS
ON ITM.ItemID = IPS.ItemId AND ITM.DataAreaId = IPS.DataAreaId AND ITM.Partition = IPS.Partition AND IPS.InventDimId = 'AllBlank' LEFT JOIN dbo.InventItemSalesSetup ISS ON ITM.ItemID = ISS.ItemId AND ITM.DataAreaId = ISS.DataAreaId AND ITM.Partition = ISS.Partition AND ISS.InventDimId = 'AllBlank' LEFT JOIN dbo.InventItemInventSetup ILS ON ITM.ItemID = ILS.ItemId AND ITM.DataAreaId = ILS.DataAreaId AND ITM.Partition = ILS.Partition AND ILS.InventDimId = 'AllBlank' ORDER BY ITM.ItemId , ITM.DataAreaId
The constraint on InventDimId is necessary because there can be multiple records for the Product and Legal entity combination.
Alternatively, one can use UNIONs instead of JOINs and include the logic into a view to simplify the final query (the query was written to test the behavior in a distributed environment like serverless SQL pool):
-- create the view CREATE OR ALTER VIEW TDM.vProductSpecificOrderSettings AS /* Product Specific Order Settings via UNIONs */ SELECT IST.DataAreaId , IST.ItemId , IST.Partition , max(IST.IsInventoryProcessingStopped) IsInventoryProcessingStopped , max(IST.IsSalesProcessingStopped) IsSalesProcessingStopped , max(IST.IsProcurementProcessingStopped) IsProcurementProcessingStopped FROM ( -- inventory SELECT IIS.DataAreaId , IIS.ItemId , IIS.Partition , IIS.Stopped IsInventoryProcessingStopped , 0 IsSalesProcessingStopped , 0 IsProcurementProcessingStopped FROM dbo.InventItemInventSetup IIS WHERE IIS.InventDimId = 'AllBlank' UNION ALL -- purchasing SELECT IPS.DataAreaId , IPS.ItemId , IPS.Partition , 0 , 0 , IPS.Stopped FROM dbo.InventItemPurchSetup IPS WHERE IPS.InventDimId = 'AllBlank' UNION ALL -- sales SELECT ISS.DataAreaId , ISS.ItemId , ISS.Partition , 0 , ISS.Stopped , 0 FROM dbo.InventItemSalesSetup ISS WHERE ISS.InventDimId = 'AllBlank' ) IST GROUP BY IST.DataAreaId , IST.ItemId , IST.Partition /* Product Specific Order Settings via UNIONs */ SELECT ITM.DataAreaId , ITM.ItemId , PSO.IsInventoryProcessingStopped , PSO.IsSalesProcessingStopped , PSO.IsProcurementProcessingStopped FROM dbo.InventTable ITM LEFT JOIN TDM.vProductSpecificOrderSettings PSO ON ITM.ItemId = PSO.ItemId AND ITM.DataAreaId = PSO.DataAreaId AND ITM.Partition = PSO.Partition ORDER BY ITM.ItemId , ITM.DataAreaId
At least for the database used for testing on an SQL Server 2022 instance, the last query has slightly lower estimated subtree cost and memory grant than the previous one. It's also interesting that the data entity-based query outperforms the other two queries.
And here's the combined query from this and previous post:
-- Legal Entities vs Products incl. product order settings SELECT DAT.Id DataAreaId , PRD.DisplayProductNumber ItemId , CASE WHEN ITM.DataAreaId IS NOT NULL THEN 1 ELSE 0 END IsReleased , CASE WHEN ITM.CreatedDatetime <> ITM.ModifiedDateTime THEN 1 ELSE 0 END IsModified , PSO.IsInventoryProcessingStopped , PSO.IsSalesProcessingStopped , PSO.IsProcurementProcessingStopped FROM dbo.DataArea DAT CROSS JOIN dbo.EcoResProduct PRD LEFT JOIN dbo.InventTable ITM ON DAT.Id = ITM.DataAreaId AND PRD.DisplayProductNumber = ITM.ItemId AND PRD.Partition = ITM.Partition LEFT JOIN TDM.vProductSpecificOrderSettings PSO ON ITM.ItemId = PSO.ItemId AND ITM.DataAreaId = PSO.DataAreaId AND ITM.Partition = PSO.Partition WHERE DAT.Id <> 'DAT' AND PRD.DisplayProductNumber = 'D0001' ORDER BY PRD.DisplayProductNumber , DAT.Id
Happy coding!
No comments:
Post a Comment