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.
