GO
/****** Object:  UserDefinedFunction [dbo].[udf_deliveryqty]    Script Date: 05-04-2025 12:35:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[udf_deliveryqty](@CustomerCode VARCHAR(50),@itemcode varchar(250),@dnno varchar(250))
--ALTER FUNCTION [dbo].[udf_deliveryqty](@CustomerCode VARCHAR(50),@InvoiceNo varchar(100),@itemcode varchar(250),@dnno varchar(250))
RETURNS FLOAT
AS
BEGIN
RETURN(
select sum (qty) from (
select sum(qty)as qty from invoiceitem inner join invoice on invoice.invno=invoiceitem.invno and invoice.sr=invoiceitem.sr
where itemcode=@itemcode
and  invoice.sr='sales' and customercode=@CustomerCode and  invoiceitem.DelNoteNo=@dnno and isnull(invoiceitem.deleted,'N')='N' 
--and invoiceitem.invno=@InvoiceNo

union all
select -sum(qty) as qty from invoiceitem --inner join invoice on invoice.invno=invoiceitem.invno and invoice.sr=invoiceitem.sr
where itemcode=@itemcode
and  SR<>'sales'  and isnull(invoiceitem.deleted,'N')='N' AND invoiceitem.DelNoteNo=@dnno
--and invoiceitem.invno=@InvoiceNo
)a)

END