set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[GetItemsAvailableQtyWithDate]
(
	-- Add the parameters for the function here
	@ItemNo			VARCHAR(20),
	@Location		VARCHAR(10),
	@Date			DATETIME
)
RETURNS INT
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Qty			INT,
			@ILEQty			INT,
			@Ret_Qty		INT
IF(@Location = 'NULL')
	SET @Location = NULL

IF(@Date IS NULL)
BEGIN
	IF(@Location IS NULL)
	BEGIN
		SELECT @ILEQty = ISNULL(SUM(Quantity),0)
		FROM dbo.[Main-db$Item Ledger Entry] WITH (NOLOCK)
		WHERE [Item No_] = @ItemNo AND [Entry Type] = 0

		SELECT @Qty = ISNULL(SUM(tse.Quantity),0)
		FROM [dbo].[Main-db$Trans_ Sales Entry] tse WITH (NOLOCK)
		LEFT OUTER JOIN dbo.[Main-db$Trans_ Sales Entry Status] tses WITH (NOLOCK) ON
			tses.[Store No_] = tse.[Store No_] AND 
			tses.[POS Terminal No_] = tse.[POS Terminal No_] AND
			tses.[Transaction No_] = tse.[Transaction No_] AND
			tses.[Line No_] = tse.[Line No_]
		WHERE tse.[Item No_] = @ItemNo AND 
			(tses.[Item No_] IS NULL OR tses.Status = 0)
		SET @Ret_Qty = @ILEQty - @Qty
	END
	ELSE
	BEGIN
		SELECT @ILEQty = ISNULL(SUM(Quantity),0)
		FROM dbo.[Main-db$Item Ledger Entry] WITH (NOLOCK)
		WHERE [Item No_] = @ItemNo AND [Location Code] = @Location 	
				AND [Entry Type] = 0

		SELECT @Qty = ISNULL(SUM(tse.Quantity),0)
		FROM [dbo].[Main-db$Trans_ Sales Entry] tse WITH (NOLOCK)
		LEFT OUTER JOIN dbo.[Main-db$Trans_ Sales Entry Status] tses WITH (NOLOCK) ON
			tses.[Store No_] = tse.[Store No_] AND 
			tses.[POS Terminal No_] = tse.[POS Terminal No_] AND
			tses.[Transaction No_] = tse.[Transaction No_] AND
			tses.[Line No_] = tse.[Line No_]
		WHERE tse.[Item No_] = @ItemNo AND tse.[Location Code] = @Location 
			AND (tses.[Item No_] IS NULL OR tses.Status = 0)
		SET @Ret_Qty = @ILEQty - @Qty
	END
END
ELSE
BEGIN
	IF(@Location IS NULL)
	BEGIN
		SELECT @ILEQty = ISNULL(SUM(Quantity),0)
		FROM dbo.[Main-db$Item Ledger Entry] WITH (NOLOCK)
		WHERE [Item No_] = @ItemNo AND [Posting Date] <= @Date
			AND [Entry Type] = 0

		SELECT @Qty = ISNULL(SUM(tse.Quantity),0)
		FROM [dbo].[Main-db$Trans_ Sales Entry] tse WITH (NOLOCK)
		LEFT OUTER JOIN dbo.[Main-db$Trans_ Sales Entry Status] tses WITH (NOLOCK) ON
			tses.[Store No_] = tse.[Store No_] AND 
			tses.[POS Terminal No_] = tse.[POS Terminal No_] AND
			tses.[Transaction No_] = tse.[Transaction No_] AND
			tses.[Line No_] = tse.[Line No_]
		WHERE tse.[Item No_] = @ItemNo AND  tse.[Date] <= @Date AND
			(tses.[Item No_] IS NULL OR tses.Status = 0)
		SET @Ret_Qty = @ILEQty - @Qty
	END
	ELSE
	BEGIN
		SELECT @ILEQty = ISNULL(SUM(Quantity),0)
		FROM dbo.[Main-db$Item Ledger Entry] WITH (NOLOCK)
		WHERE [Item No_] = @ItemNo AND [Location Code] = @Location 	
			AND [Posting Date] <= @Date AND [Entry Type] = 0

		SELECT @Qty = ISNULL(SUM(tse.Quantity),0)
		FROM [dbo].[Main-db$Trans_ Sales Entry] tse WITH (NOLOCK)
		LEFT OUTER JOIN dbo.[Main-db$Trans_ Sales Entry Status] tses WITH (NOLOCK) ON
			tses.[Store No_] = tse.[Store No_] AND 
			tses.[POS Terminal No_] = tse.[POS Terminal No_] AND
			tses.[Transaction No_] = tse.[Transaction No_] AND
			tses.[Line No_] = tse.[Line No_]
		WHERE tse.[Item No_] = @ItemNo AND tse.[Date] <= @Date AND
			tse.[Location Code] = @Location 
			AND (tses.[Item No_] IS NULL OR tses.Status = 0)
		SET @Ret_Qty = @ILEQty - @Qty
	END
END
	-- Return the result of the function
	RETURN @ILEQty
END

select I.[Item No_], I.[Location Code],I.[Posting Date], F.TotalQty
from [dbo].[main-db$Item Ledger Entry] I 
CROSS APPLY dbo.GetItemInventoryQuantitytestt(I.[Item No_], I.[Location Code], GetDate())F
order by I.[Posting Date] desc