GO
/****** Object:  StoredProcedure [dbo].[SP_GetSupplierPaymentDetailEdit]    Script Date: 16-01-2025 14:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetSupplierPaymentDetailEdit]
    @id NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- Query to fetch payment details for purchases
    SELECT  
        SupplierPaymentDetails.PurchaseNo,
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        Purchase.NetAmount AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (Purchase.NetAmount)
            ELSE (SELECT (Purchase.NetAmount -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
        --          GROUP BY PurchaseNo) = 1
        --    THEN '0'
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
        --            AND CRD2.VoucherNo != @id 
        --          GROUP BY PurchaseNo) 
        --END AS ReceivedWithoutInv,
		isnull([dbo].[udf_SelectReceivedAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'P' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN Purchase ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo 
	LEFT JOIN SupplierPayment ON SupplierPayment.VoucherNo = SupplierPaymentDetails.VoucherNo 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND SupplierPaymentDetails.Type = 'P'

    UNION ALL

    -- Query to fetch payment details for purchase expenses
    SELECT  
        PurchaseNo,
        ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        PurchaseExpenseDetails.RATE AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (PurchaseExpenseDetails.RATE)
            ELSE (SELECT (PurchaseExpenseDetails.RATE -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'E' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN PurchaseExpenseDetails ON SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.purexpno 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type = 'E'

    UNION ALL

    -- Query to fetch supplier opening balance payment details
     SELECT  
        SupplierPaymentDetails.PurchaseNo,
        
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        SupplierOPEntryUpdated.PurchaseAmt AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 0) AS DiscountAmt,
	   --isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](SupplierPaymentDetails.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode), 0) AS ReceivedAmt,
        
		FORMAT(SupplierPaymentDetails.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
       
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN SupplierOPEntryUpdated.InvoiceAmt
        --    ELSE (SELECT (SupplierOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo  and   SupplierOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforSupppayment](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno,SupplierOPEntryUpdated.PurchaseAmt),0)AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS ReceivedWithoutInv,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(Discount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		 isnull([dbo].[udf_SelectDiscountAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS DiscountWithoutInv,'' as Type 
    FROM   
        SupplierPaymentDetails 
		inner join SupplierPayment  on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo
    LEFT JOIN  
        SupplierOPEntryUpdated ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo  and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode
    WHERE  
        SupplierPaymentDetails.VoucherNo = @id AND SupplierOPEntryUpdated.PurchaseAmt IS NOT NULL



		union all 

		 SELECT  
        SupplierPaymentDetails.PurchaseNo,
        
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        0 AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 0) AS DiscountAmt,
	   --isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](SupplierPaymentDetails.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode), 0) AS ReceivedAmt,
        
		FORMAT(SupplierPaymentDetails.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
       
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN SupplierOPEntryUpdated.InvoiceAmt
        --    ELSE (SELECT (SupplierOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo  and   SupplierOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforSupppayment](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno,SupplierOPEntryUpdated.PurchaseAmt),0)AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS ReceivedWithoutInv,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(Discount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		 isnull([dbo].[udf_SelectDiscountAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS DiscountWithoutInv,'' as Type 
    FROM   
        SupplierPaymentDetails 
		left join SupplierPayment  on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo
    LEFT JOIN  
        SupplierOPEntryUpdated ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo  and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode
	left join	Purchase ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo 
    WHERE  
        SupplierPaymentDetails.VoucherNo = @id AND SupplierOPEntryUpdated.PurchaseAmt is NULL and purchase.NetAmount is null and SupplierPayment.Advance in(1,3) and SupplierPaymentDetails.Type is null
END







GO
/****** Object:  StoredProcedure [dbo].[Sp_GetCustomerReceiptDetailEdit]    Script Date: 16-01-2025 11:05:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_GetCustomerReceiptDetailEdit]
    @VoucherNo NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- First SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        ISNULL(Invoice.NetAmount,0) AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS ReceivedAmt,
		
        FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS DiscountAmt,

        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN COALESCE(Invoice.NetAmount, 0)
            ELSE COALESCE((SELECT (Invoice.NetAmount - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo),0)
        END AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo
    LEFT JOIN  
        Invoice ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo 
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND Invoice.NetAmount IS NOT NULL

    UNION ALL

    -- Second SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        CustomerOPEntryUpdated.InvoiceAmt AS InvoiceAmount,
       
	   --isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode), 0) AS ReceivedAmt,
        
		FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo), 0) AS DiscountAmt,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN CustomerOPEntryUpdated.InvoiceAmt
        --    ELSE (SELECT (CustomerOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo  and   CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforcustrecpt](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno,CustomerOPEntryUpdated.InvoiceAmt),0)AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS ReceivedWithoutInv,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(Discount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		  isnull([dbo].[udf_SelectDiscountAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo
    LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo  and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND CustomerOPEntryUpdated.InvoiceAmt IS NOT NULL

		union all 

		 SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        ISNULL(Invoice.NetAmount,0) AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS ReceivedAmt,
		
        FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS DiscountAmt,

        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN COALESCE(Invoice.NetAmount, 0)
            ELSE COALESCE((SELECT (Invoice.NetAmount - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo),0)
        END AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo

    LEFT JOIN  
        Invoice ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo 
		LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo  and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND Invoice.NetAmount IS NULL AND CustomerOPEntryUpdated.InvoiceAmt IS NULL and CustomerReceipt.Advance in(1,3)
END


alter table item
add ModelNo nvarchar(500)