SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SelectPendingInvoice]  
@Flag integer,  
@CustomerCode as varchar(50),  
@FromDate datetime,  
@ToDate datetime, 
@salesmanname as varchar(500),
@BranchCode as varchar(500)
  
AS  
begin  
if @Flag=1  
begin  
select * from (
select InvNo,CustomerCode,CustomerName,EntryDate,PONO,NetAmount,isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvNo,@BranchCode),0) as ReceivedAmt,[dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvNo,@BranchCode)AS ReturnAmt,  
  isnull(dbo.udf_CustomerReceiptDiscountDtls(CustomerCode,InvNo,@BranchCode),0) as DiscountAmt,
(NetAmount-(isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvNo,@BranchCode),0)+isnull(dbo.udf_CustomerReceiptDiscountDtls(CustomerCode,InvNo,@BranchCode),0)+ISNULL([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvNo,@BranchCode),0))) as BalAmt,isnull(dbo.udf_CustomerReceiptDiscountDtls(CustomerCode,InvNo,@BranchCode),0) as DisAmt from Invoice  
where Billtype='Credit' and SR='Sales'  
AND (@salesmanname IS NULL OR @salesmanname = '' OR ISNULL(Invoice.Attended, '') = @salesmanname) 
and Invoice.EntryDate between @FromDate and @ToDate  
and ((NetAmount-(isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvNo,@BranchCode),0)+isnull(dbo.udf_CustomerReceiptDiscountDtls(CustomerCode,InvNo,@BranchCode),0)+ISNULL([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvNo,@BranchCode),0))))>0  
AND ISNULL(Deleted,'N')='N'  
AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(Invoice.BranchCode, '') = @BranchCode) 
union all
select InvoiceNo as InvNo,CustomerCode as CustomerCode,customer.name as customername,InvoiceDate as entrydate,'' as pono,isnull(InvoiceAmt,0) as netamount,
isnull(ReceivedAmt,0)+isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvoiceNo,@BranchCode),0) as receivedamt
,isnull(ReturnedAmt,0) +isnull([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvoiceNo,@BranchCode),0)AS ReturnAmt ,isnull(Discount,0) as DiscountAmt,  
isnull(Balance,0)-(isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvoiceNo,@BranchCode),0) + isnull(Discount,0)+isnull([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvoiceNo,@BranchCode),0)) as BalAmt,isnull(Discount,0) as DisAmt  from CustomerOPEntryUpdated
inner join customer on customer.code=CustomerOPEntryUpdated.CustomerCode

WHERE isnull(Balance,0)-(isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvoiceNo,@BranchCode),0) + isnull(Discount,0)+isnull([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvoiceNo,@BranchCode),0)) >0
and InvoiceDate between @FromDate and @ToDate  AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(CustomerOPEntryUpdated.BranchCode, '') = @BranchCode) 
)a
order by EntryDate asc,InvNo asc  
select InvoiceNo as InvNo,PaidDate,InvoiceAmount,ReceivedAmount,VoucherNo from CustomerReceiptDetail where isnull(deleted,'N')='N'
end  
if @Flag=2  
begin  
select * from (
select InvNo,CustomerCode,CustomerName,EntryDate,PONO,NetAmount,isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvNo,@BranchCode),0) as ReceivedAmt,[dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvNo,@BranchCode)AS ReturnAmt,
isnull(dbo.udf_CustomerReceiptDiscountDtls(CustomerCode,InvNo,@BranchCode),0) as DiscountAmt,  
(NetAmount-(isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvNo,@BranchCode),0)+ isnull(dbo.udf_CustomerReceiptDiscountDtls(CustomerCode,InvNo,@BranchCode),0)+ISNULL([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvNo,@BranchCode),0))) as BalAmt ,isnull(dbo.udf_CustomerReceiptDiscountDtls(CustomerCode,InvNo,@BranchCode),0) as DisAmt from Invoice  
where Billtype='Credit' and SR='Sales'  
and CustomerCode=@CustomerCode  
AND (@salesmanname IS NULL OR @salesmanname = '' OR ISNULL(Invoice.Attended, '') = @salesmanname)   
and Invoice.EntryDate between @FromDate and @ToDate  
and ((NetAmount-(isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvNo,@BranchCode),0)+ isnull(dbo.udf_CustomerReceiptDiscountDtls(CustomerCode,InvNo,@BranchCode),0)+ISNULL([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvNo,@BranchCode),0))))>0  
AND ISNULL(Deleted,'N')='N'   
AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(Invoice.BranchCode, '') = @BranchCode) 
union all
select InvoiceNo as Invno,CustomerCode as CustomerCode,customer.name as customername,InvoiceDate as entrydate,'' as pono,isnull(InvoiceAmt,0) as netamount,
isnull(ReceivedAmt,0)+isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvoiceNo,@BranchCode),0) as receivedamt
,isnull(ReturnedAmt,0) +isnull([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvoiceNo,@BranchCode),0)AS ReturnAmt,isnull(Discount,0) as DiscountAmt,  
isnull(Balance,0)-(isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvoiceNo,@BranchCode),0) + isnull(Discount,0)+isnull([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvoiceNo,@BranchCode),0))  as BalAmt,
isnull(Discount,0) as DisAmt  from CustomerOPEntryUpdated
inner join customer on customer.code=CustomerOPEntryUpdated.CustomerCode
WHERE  CustomerCode=@CustomerCode  AND  isnull(Balance,0)-(isnull(dbo.udf_CustomerReceiptDtls(CustomerCode,InvoiceNo,@BranchCode),0) + isnull(Discount,0)+isnull([dbo].udf_SelectReturnAmount(CustomerCode,@FromDate,@ToDate,InvoiceNo,@BranchCode),0)) >0
and InvoiceDate between @FromDate and @ToDate  AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(CustomerOPEntryUpdated.BranchCode, '') = @BranchCode) 
)a 
order by EntryDate asc,InvNo asc  
  
select InvoiceNo as InvNo,PaidDate,InvoiceAmount,ReceivedAmount,VoucherNo from CustomerReceiptDetail   where isnull(deleted,'N')='N'
end  
end