set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[getitemsavailableqtywithdatets]
(@ItemNo VARCHAR(20),
@Location VARCHAR(10),
@Date DATETIME)
RETURNS TABLE
AS
-- Declare the return variable here
RETURN
SELECT *
FROM (SELECT Isnull(Sum(Quantity),0),
@ItemNo AS [Item No_]
FROM dbo.[Main-db$Item Ledger Entry] WITH (NOLOCK)
WHERE [Item No_] = @ItemNo
AND [Entry Type] = 0
UNION ALL
SELECT Isnull(Sum(tse.Quantity),0),
@ItemNo AS [Item No_]
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)) t(salesqty,[Item No_])