SQL Reloaded: Released Products by Legal Entity in Dynamics 365 F&O

To check in which Legal Entity (LE) a Product was released using the standard UI, the user needs to go through each LE and search for the respective Product, action which can be time-consuming and error-prone if done for multiple Products. An alternative would be to use the Excel add-in or the Table Browser and export the data for each LE, respectively aggregate the data in one file and build a power pivot on top of it. If this kind of checks need to be done on a regular basis, then a better solution is to build a simple report.

When a Product is released for a Legal Entity (LE) a record is created in the InventTable table and is accessible over the dbo.EcoResReleasedProductV2Entity data entity as well. One can build the logic as a GROUP BY with an entry for each LE in scope. 

-- Released Products by Legal Entity
SELECT ItemId
, Max(CASE WHEN DataAreaId ='cnmf' THEN 1 ELSE 0 END) [cnmf]
, Max(CASE WHEN DataAreaId ='demf' THEN 1 ELSE 0 END) [demf]
, Max(CASE WHEN DataAreaId ='inmf' THEN 1 ELSE 0 END) [inmf]
, Max(CASE WHEN DataAreaId ='jpmf' THEN 1 ELSE 0 END) [jpmf]
, Max(CASE WHEN DataAreaId ='mymf' THEN 1 ELSE 0 END) [mymf]
, Max(CASE WHEN DataAreaId ='rumf' THEN 1 ELSE 0 END) [rumf]
, Max(CASE WHEN DataAreaId ='samf' THEN 1 ELSE 0 END) [samf]
, Max(CASE WHEN DataAreaId ='thmf' THEN 1 ELSE 0 END) [thmf]
, Max(CASE WHEN DataAreaId ='usmf' THEN 1 ELSE 0 END) [usmf]
, count(*) NoRecords
FROM dbo.InventTable
WHERE DataAreaId <> 'DAT'
-- AND ItemId = 'D0001'
GROUP BY ItemId
ORDER BY NoRecords DESC
-- HAVING count(*)>1 -- 

Unfortunately, the query is static and needs to be adjusted each time a new LE is added or removed. Unless the query is build and executed dynamically, there is apparently no way in SQL to include/exclude the LEs dynamically. However, one can use a cartesian product between LEs and Products, and retrieve the corresponding entry from the Released products table:

-- Legal Entities vs Products 
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
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
WHERE DAT.Id <> 'DAT'
   AND PRD.DisplayProductNumber = 'D0001'
ORDER BY PRD.DisplayProductNumber
, DAT.Id 

Further on, the data can be aggregated in a matrix display in a paginated report, given that the respective functionality can dynamically display the LEs in scope. 

Notes:
1) The first query will run also in AX 2009, while the second will run only in D365 F&O.
2) Besides checking whether a Product was released (see IsReleased column) it's useful to check whether the Product was modified (see IsModified). If the Created and Modified timestamps are the same, then the Product was not touched after its release. 
3) The two queries can be extended to retrieve further Product-specific data (e.g. Unit of Measure, Base Prices, Default vendor, etc.).
4) It's safer to use the corresponding data entities instead of the tables.
5) It's also useful to check whether the Products are blocked for Sales, Purchasing or Inventory (see next post). 

Happy coding!