CREATE VIEW [dbo].[PMX_REPLENISH_PICKLOC_DEMAND_BASED_DEFAULT_LOCATION] AS

SELECT dbo.PMX_ITWL.ItemCode
, dbo.PMX_OSSL.Code AS StorLocCode
, ISNULL(Inventory.Quantity, 0) AS QtyOnStorLoc
, ISNULL(MoveOrder.OpenQty, 0) AS QtyOnRepOrder
, '1' AS MaxNumberOfItems
, dbo.OITM.U_PMX_RQPL AS MinNumberOfItems
, '1' AS ReplenishQty
, dbo.PMX_OSSL.Sequence AS LocSequence
, dbo.PMX_OSEL.PmxWhsCode
, '1' AS OpenQuantity --QtyThatCanBeReplenished
, '1' AS QtyToMoveRAW
, '1' AS QtyToMove
FROM dbo.PMX_OSSL
INNER JOIN dbo.PMX_OSEL ON dbo.PMX_OSEL.Code = dbo.PMX_OSSL.Code 
INNER JOIN dbo.PMX_ITWL ON dbo.PMX_OSSL.Code = dbo.PMX_ITWL.DefaultLocationZone  AND dbo.PMX_OSEL.PmxWhsCode = dbo.PMX_ITWL.PmxWhsCode
INNER JOIN dbo.OITM ON dbo.OITM.ItemCode = dbo.PMX_ITWL.ItemCode 
LEFT OUTER JOIN dbo.PMX_OSBI ON dbo.PMX_OSBI.Code = dbo.PMX_OSSL.Code 
--QTY ON STORAGE LOCATION
LEFT OUTER JOIN (SELECT StorLocCode, ItemCode, SUM(Quantity) AS Quantity
                 FROM dbo.PMX_INVT
                 GROUP BY StorLocCode, ItemCode) 
				 AS Inventory ON dbo.PMX_ITWL.ItemCode = Inventory.ItemCode AND dbo.PMX_ITWL.DefaultLocationZone = Inventory.StorLocCode
--QTY ON OPEN REPLENISHMENT ORDER
LEFT OUTER JOIN (SELECT dbo.PMX_MOLI.ItemCode, dbo.PMX_MOLI.DestStorLocCode, SUM(dbo.PMX_MOLI.OpenQty) AS OpenQty
                FROM dbo.PMX_MOLI 
				INNER JOIN dbo.PMX_MOHE ON dbo.PMX_MOLI.DocEntry = dbo.PMX_MOHE.DocEntry
                WHERE (dbo.PMX_MOHE.MoveOrderType = 'R') AND (dbo.PMX_MOHE.MoveOrderStatus IN ('N', 'P'))
                GROUP BY dbo.PMX_MOLI.ItemCode, dbo.PMX_MOLI.DestStorLocCode)
				AS MoveOrder ON dbo.PMX_ITWL.ItemCode = MoveOrder.ItemCode AND dbo.PMX_ITWL.DefaultLocationZone = MoveOrder.DestStorLocCode 
--QTY ON OPEN PICKLISTS
LEFT OUTER JOIN (SELECT dbo.PMX_PLLI.ItemCode, SUM(dbo.PMX_PLLI.Quantity) AS Quantity
                FROM dbo.PMX_PLLI
				INNER JOIN dbo.PMX_PLHE ON dbo.PMX_PLHE.DocEntry = dbo.PMX_PLLI.DocEntry
                WHERE (dbo.PMX_PLLI.PickListLineStatus IN ('N', 'R'))
                GROUP BY dbo.PMX_PLLI.ItemCode) 
				AS Picklist ON dbo.PMX_ITWL.ItemCode = Picklist.ItemCode
WHERE 1=1
AND (dbo.PMX_OSSL.IsActive = 'Y') 
AND (dbo.PMX_OSSL.IsPickLoc = 'Y')
AND (dbo.PMX_OSEL.TypeCode = 'BIN')
--Calculate If Replishement is possible with open MoveOrders
--AND (ISNULL(dbo.PMX_ITWL.MaxNumberOfItems, 0) - ISNULL(MoveOrder.OpenQty, 0) - ISNULL(Inventory.Quantity, 0) >= 0) 
--Calculate if Replenishement is needed with open MoveOrders
AND (ISNULL(dbo.OITM.U_PMX_RQPL, 0) - ISNULL(Inventory.Quantity, 0) - ISNULL(MoveOrder.OpenQty, 0) > 0) 
AND (dbo.PMX_ITWL.ItemCode IS NOT NULL)