CREATE VIEW [dbo].[PMX_REPLENISH_BULK_STOCK] as

SELECT PMX_INVT."ItemCode"
, PMX_INVT."StorLocCode"
, PMX_INVT."Quantity"
, PMX_INVT."ItemTransactionalInfoKey"
, PMX_ITRI."BestBeforeDate"
, PMX_INVT."LogUnitIdentKey"
, PMX_INVT."QualityStatusCode"
FROM PMX_INVT
INNER JOIN PMX_OSSL ON PMX_OSSL."Code" = PMX_INVT."StorLocCode" AND PMX_OSSL."IsPickLoc" = 'N'
INNER JOIN PMX_OSEL ON PMX_OSEL."Code" = PMX_INVT."StorLocCode"
LEFT OUTER JOIN PMX_ITRI ON PMX_ITRI.InternalKey = PMX_INVT.ItemTransactionalInfoKey
WHERE 1=1
AND PMX_OSEL."TypeCode" = 'BIN'
AND PMX_INVT.QualityStatusCode='RELEASED'
AND ISNULL(PMX_ITRI."BestBeforeDate",GETDATE()) >= GETDATE()
-- Make sure its not used in other open replenishment move order
AND "LogUnitIdentKey" NOT IN (
							SELECT ISNULL(PMX_MOLI."SrcLogUnitIdentKey",1)
							FROM PMX_MOLI 
							INNER JOIN PMX_MOHE ON PMX_MOLI."DocEntry" = PMX_MOHE."DocEntry" 
							WHERE PMX_MOHE."MoveOrderType" = 'R' AND PMX_MOLI.LineStatus = 'O'
							)
-- Make sure its not used in open pick list
AND "LogUnitIdentKey" NOT IN (
							SELECT ISNULL(PMX_PLLI."LogUnitIdentKey",1)
							FROM PMX_PLLI  
							WHERE PMX_PLLI."PickListLineStatus" IN ( 'R', 'P', 'T', 'K', 'L')
							)

GROUP BY PMX_INVT."ItemCode" , "StorLocCode" , PMX_INVT."Quantity", "ItemTransactionalInfoKey",PMX_ITRI."BestBeforeDate", "LogUnitIdentKey", PMX_INVT."QualityStatusCode"