AX Retail 2012 R2: Query product availability in POS

Noted:  This customization is based on AX 2012 R2 + CU6 

Scenario: 

Retail POS still allow to sell unavailable items.  How to check item availability and prevent selling unavailable one.  

Inventory On-hand & Item Bar code

When I have 2 physical inventory quantity of Item No. 81137 (with variant): -      

I also create item bar codes matched all variants:- 

Calculate inventory 

After that we need to calculate inventory from Retail > Period > Calculate inventory.  And then run job "Products, prices, and bar codes"  

Sale item 

Scan bar code 81183736BR with 2 quantities and pay cash. 

But when I re-scan the same bar code, POS still allow me to sell it.  It shouldn't do, right? 

Creates SQL Views 

After inventory calculation, the table RetailInventAvailability manages available quantities based on inventory dimension. But because of I have various items (both product / product master subtype), so I have to create views that cover all products.  

There are 3 main SQL views: -

• PKARETAILSALESTYPE queries retail transaction ID for sales type
• PKARETAILINVENTVARIANT queries inventory dimension based on table RetailInventAvailability  
• PKARETAILINVENTSTOCK  queries all available items based on table RetailInventAvailability and quantity in stock  

CREATE VIEW [dbo].[PKARETAILSALESTYPE]
AS
SELECT        TRANSACTIONID, SALEISRETURNSALE
FROM            dbo.RETAILTRANSACTIONTABLE AS TRANS
WHERE        (TYPE = 2) AND (ENTRYSTATUS IN (0, 2))
AND EXISTS 
(SELECT 1 FROM dbo.RETAILTRANSACTIONSALESTRANS SALES 
WHERE TRANS.TRANSACTIONID = SALES.TRANSACTIONID 

AND   SALES.TRANSACTIONSTATUS IN (0, 2))


CREATE VIEW [dbo].[PKARETAILINVENTVARIANT]
AS
SELECT DISTINCT    
CASE 
WHEN ISNULL(SR.INVENTSERIALID, '') = '' THEN DIMV.INVENTDIMID
ELSE DIM.INVENTDIMID 
END
AS INVENTDIMAVAIL
, 
CASE 
WHEN ISNULL(DIMC.ITEMID, '') = '' THEN SR.ITEMID 
ELSE DIMC.ITEMID 
END 
AS ITEMID
, ISNULL(DIMC.RETAILVARIANTID,'') AS RETAILVARIANTID
, DIM.INVENTDIMID, DIM.INVENTBATCHID, DIM.WMSLOCATIONID, 
                         DIM.WMSPALLETID, DIM.INVENTSERIALID, DIM.INVENTLOCATIONID, DIM.CONFIGID, DIM.INVENTSIZEID, DIM.INVENTCOLORID, DIM.INVENTSTYLEID, 
                         DIM.INVENTSITEID, DIM.DATAAREAID, DIM.RECVERSION, DIM.RECID
FROM            dbo.INVENTDIM AS DIM INNER JOIN
                         dbo.INVENTDIMCOMBINATION AS DIMC ON DIM.INVENTDIMID = DIMC.INVENTDIMID 
INNER JOIN
                         dbo.INVENTDIM AS DIMV ON 
DIM.INVENTBATCHID = DIMV.INVENTBATCHID AND DIM.WMSLOCATIONID = DIMV.WMSLOCATIONID AND 
                         DIM.WMSPALLETID = DIMV.WMSPALLETID AND DIM.CONFIGID = DIMV.CONFIGID AND 
                         DIM.INVENTSIZEID = DIMV.INVENTSIZEID AND DIM.INVENTCOLORID = DIMV.INVENTCOLORID AND DIM.INVENTSTYLEID = DIMV.INVENTSTYLEID AND 
                         DIM.INVENTSITEID = DIMV.INVENTSITEID 
AND DIM.INVENTLOCATIONID <> DIMV.INVENTLOCATIONID
 
LEFT OUTER JOIN INVENTSERIAL SR ON 

   DIM.INVENTSERIALID = SR.INVENTSERIALID AND DIMC.ITEMID = SR.ITEMID


CREATE VIEW [dbo].[PKARETAILINVENTSTOCK]
AS
SELECT        CHANNEL.RECID AS STORERECID
, INVENT.ITEMID
, DIM.INVENTDIMID
, DIMC.RETAILVARIANTID
, DIM.INVENTLOCATIONID
,CASE 
WHEN ISNULL(SALES.INVENTSERIALID,'') <> '' THEN SALES.INVENTSERIALID
WHEN ISNULL(SR.INVENTSERIALID,'') <> '' THEN SR.INVENTSERIALID  
ELSE ISNULL(DIM.INVENTSERIALID,'') 
END AS INVENTSERIALID
, DIM.INVENTCOLORID
, DIM.CONFIGID
, DIM.INVENTSIZEID
, DIM.INVENTSTYLEID
, ISNULL(BAR.ITEMBARCODE, N'') AS ITEMBARCODE
, AVAIL.AVAILPHYSICAL
,ISNULL(SALES.QTY, 0) AS SALESQTY
,ISNULL((AVAIL.AVAILPHYSICAL + ISNULL(SALES.QTY, 0)),0) AS QTYINSTOCK

FROM            dbo.RETAILINVENTAVAILABILITY AS AVAIL INNER JOIN
                         dbo.INVENTDIM AS DIM ON AVAIL.INVENTDIMRECID = DIM.RECID INNER JOIN
                         dbo.RETAILCHANNELTABLE AS CHANNEL ON DIM.INVENTLOCATIONID = CHANNEL.INVENTLOCATION INNER JOIN
                         dbo.INVENTTABLE AS INVENT ON AVAIL.ITEMRECID = INVENT.RECID AND DIM.DATAAREAID = INVENT.DATAAREAID 
INNER JOIN
                         dbo.PKARETAILINVENTVARIANT AS DIMC ON DIM.INVENTDIMID = DIMC.INVENTDIMAVAIL AND DIMC.ITEMID = INVENT.ITEMID AND 
                         DIMC.DATAAREAID = INVENT.DATAAREAID 
LEFT OUTER JOIN
                         dbo.INVENTITEMBARCODE AS BAR ON DIMC.INVENTDIMID = BAR.INVENTDIMID
LEFT OUTER JOIN
dbo.INVENTSERIAL AS SR ON BAR.ITEMBARCODE = SR.INVENTSERIALID AND SR.ITEMID = INVENT.ITEMID
LEFT OUTER JOIN ( 
SELECT  SALESTRANS.ITEMID, SALESTRANS.VARIANTID, INVENTSERIALID, SUM(SALESTRANS.QTY) AS QTY
FROM dbo.RETAILTRANSACTIONSALESTRANS AS SALESTRANS INNER JOIN
dbo.PKARETAILSALESTYPE AS TRANS ON SALESTRANS.TRANSACTIONID = TRANS.TRANSACTIONID
GROUP BY SALESTRANS.ITEMID, SALESTRANS.VARIANTID, SALESTRANS.INVENTSERIALID, INVENTLOCATIONID
HAVING (SUM(SALESTRANS.QTY) <> 0)
) AS SALES 
ON INVENT.ITEMID = SALES.ITEMID AND DIMC.RETAILVARIANTID = SALES.VARIANTID AND 
(DIM.INVENTSERIALID = SALES.INVENTSERIALID OR SR.INVENTSERIALID = SALES.INVENTSERIALID)
WHERE AVAIL.AVAILPHYSICAL > 0
   

OK, now we can see available / unavailable items.  I will explain in next blog how to use them to prevent selling unavailable items.