AX Retail 2012 R2: Query product assortment in POS

An assortment is a collection of retail categories and, or products that are allocated to stores.  That means each stores can have different products.

Set up the assortment, process assortment and run product job

Do the following steps respectively: -

• Set up: Retail > Common > Assortments
• Process assortment: Retail > Periodic > Process assortments
• Run Job: Retail > Periodic > Data distribution > Distribution schedule > run job "Product, prices, and bar codes"

Query assorted items in POS

In Store DB, the assortments makes us not able to query items directly from inventory table.  There are 3 views for getting assorted items (search items of each store by STORERECID, DATAAREAID): -

• ASSORTEDINVENTITEMS results all assorted items including Product / Product master subtype.  

• ASSORTEDINVENTDIMCOMBINATION results assorted items for only Product master subtype which consists of product variants.  

• VARIANTSVIEW selects view ASSORTEDINVENTDIMCOMBINATION and show all inventory dimension details.  

Query assorted items for both Product/Product Master

Reference to my previous blog; AX Retail 2012 R2: Create New POS Form with Editable Quantity. I had to show items with both Product and Product Master subtype together with theirs inventory dimension details.  So, the view VARIANTSVIEW cannot serve what I need.  

So, I create a new view similar to ASSORTEDINVENTDIMCOMBINATION but union with items with no variant by set its InventDimId as "AllBlank".

ALTER VIEW [dbo].[PKAASSORTEDINVENTDIMCOMBINATION]
AS
(
SELECT RCT.RECID AS STORERECID, RAE.ITEMID, 
'AllBlank' AS INVENTDIMID, 
0 AS DISTINCTPRODUCTVARIANT, 
''AS RETAILVARIANTID, IVT.DATAAREAID
FROM            dbo.RETAILASSORTMENTEXPLODED AS RAE INNER JOIN
                         dbo.RETAILCHANNELTABLE AS RCT ON RAE.OMOPERATINGUNITID = RCT.OMOPERATINGUNITID
                         INNER JOIN
                         dbo.INVENTTABLE AS IVT ON RAE.ITEMID = IVT.ITEMID 
AND RAE.CHANNELDATAAREAID = IVT.DATAAREAID
WHERE (RAE.VALIDFROM <= GETUTCDATE()) AND (RAE.VALIDTO >= GETUTCDATE()) 
AND NOT EXISTS 
(SELECT 1 FROM dbo.INVENTDIMCOMBINATION AS IDC
WHERE RAE.ITEMID = IDC.ITEMID AND RAE.CHANNELDATAAREAID = IDC.DATAAREAID)
)
UNION 
(
SELECT STORERECID, ITEMID, INVENTDIMID, DISTINCTPRODUCTVARIANT, RETAILVARIANTID, DATAAREAID 
FROM dbo.ASSORTEDINVENTDIMCOMBINATION AIDC

)

And then, create a new view similar to VARIANTSVIEW but selects above view.  "AllBlank" will help to get blank dimension details itself.