GO
/****** Object:  StoredProcedure [dbo].[Sp_GetCustomerReceiptDetailEdit]    Script Date: 09-01-2025 19:53:32 ******/
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,
        Invoice.NetAmount 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 Invoice.NetAmount
            ELSE (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)
        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,
        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 
    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((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo), 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 CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        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,
        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 
    LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo 
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND CustomerOPEntryUpdated.InvoiceAmt IS NOT NULL
END






GO
/****** Object:  StoredProcedure [dbo].[Sp_GetInvoiceDetailForReceipt]    Script Date: 09-01-2025 19:54:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_GetInvoiceDetailForReceipt]
    @CustomerId NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- First SELECT statement
    SELECT 
        Invoice.InvNo AS InvoiceNo, 
        (Invoice.NetAmount - COALESCE(InvoiceReturn.NetAmount, 0) - ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(CustomerReceiptDetail.Discount), 0) AS DiscountAmt, 
        ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) AS ReceivedAmt,
        Invoice.NetAmount AS InvoiceAmount, 
        FORMAT(Invoice.InvoiceDate, 'dd-MM-yyyy') AS EntryDatefrmtd
    FROM 
        Invoice
        LEFT JOIN CustomerReceiptDetail 
            ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo
        LEFT JOIN InvoiceReturn 
            ON InvoiceReturn.InvNo = Invoice.InvNo 
            AND InvoiceReturn.InvType = 'Credit'
    WHERE 
        Invoice.InvType = 'Credit' 
        AND Invoice.CustomerCode = @CustomerId
    GROUP BY 
        Invoice.InvNo, 
        Invoice.NetAmount, 
        Invoice.InvoiceDate, 
        InvoiceReturn.NetAmount,
        InvoiceReturn.InvType
    HAVING 
        (Invoice.NetAmount - COALESCE(InvoiceReturn.NetAmount, 0) - ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) > 0
    
    UNION ALL

    -- Second SELECT statement
    SELECT 
        InvoiceNo AS InvoiceNo,
        Balance AS BalanceAmount,
        Discount AS DiscountAmt,
        ReceivedAmt AS ReceivedAmt,
        InvoiceAmt AS InvoiceAmount,
        FORMAT(InvoiceDate, 'dd-MM-yyyy') AS EntryDatefrmtd
    FROM 
        CustomerOPEntryUpdated
    WHERE 
        CustomerCode = @CustomerId

    ORDER BY EntryDatefrmtd ASC;
END








GO
/****** Object:  StoredProcedure [dbo].[SP_GetPurchaseDetailsForSuppPayment]    Script Date: 09-01-2025 19:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetPurchaseDetailsForSuppPayment]
    @id NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        Purchase.DocumentNo AS PurchaseNo, 
        (Purchase.NetAmount 
         - COALESCE(PurchaseReturn.NetAmount, 0) 
         - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
         - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(SupplierPaymentDetails.Discount), 0) AS DiscountAmt,
        ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) AS ReceivedAmt,
        Purchase.NetAmount AS PurchaseAmount, 
        FORMAT(Purchase.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        'P' AS type
    FROM Purchase
    LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo AND type = 'P'
    LEFT JOIN PurchaseReturn
        ON PurchaseReturn.PurchaseNo = Purchase.DocumentNo AND PurchaseReturn.BillType = 'Credit'
    WHERE Purchase.BillType = 'Credit' AND Purchase.SupplierCode = @id
    GROUP BY Purchase.DocumentNo, Purchase.NetAmount, Purchase.EntryDate, PurchaseReturn.NetAmount, PurchaseReturn.BillType
    HAVING (Purchase.NetAmount 
            - COALESCE(PurchaseReturn.NetAmount, 0) 
            - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
            - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) > 0

    UNION ALL

    SELECT 
        PurchaseExpenseDetails.purexpno AS PurchaseNo, 
        (PurchaseExpenseDetails.RATE 
         - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
         - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(SupplierPaymentDetails.Discount), 0) AS DiscountAmt,
        ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) AS ReceivedAmt,
        PurchaseExpenseDetails.RATE AS PurchaseAmount,  
        FORMAT(PurchaseExpenseDetails.EXPDATE, 'dd-MM-yyyy') AS EntryDatefrmtd,
        'E' AS type
    FROM PurchaseExpenseDetails
    LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.purexpno AND type = 'E'

    WHERE PurchaseExpenseDetails.EXTYPE = 'Credit' AND PurchaseExpenseDetails.suppcode = @id
    GROUP BY PurchaseExpenseDetails.purexpno, PurchaseExpenseDetails.RATE, PurchaseExpenseDetails.EXPDATE, PurchaseExpenseDetails.EXTYPE
    HAVING (PurchaseExpenseDetails.RATE 
            - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0)) > 0

    UNION ALL

    SELECT 
        SupplierOPEntryUpdated.PurchaseNo AS PurchaseNo, 
		
        (ISNULL(SupplierOPEntryUpdated.Balance, 0)-ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SupplierOPEntryUpdated.Discount, 0) AS DiscountAmt,
        ISNULL(SupplierOPEntryUpdated.ReceivedAmt, 0) AS ReceivedAmt,
        SupplierOPEntryUpdated.PurchaseAmt AS PurchaseAmount, 
        FORMAT(SupplierOPEntryUpdated.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        '' AS type
    FROM SupplierOPEntryUpdated
	 LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo 
    WHERE SupplierOPEntryUpdated.SupplierCode = @id
	group by
	SupplierOPEntryUpdated.PurchaseNo, 
        SupplierOPEntryUpdated.PurchaseAmt, 
        SupplierOPEntryUpdated.EntryDate,
        SupplierOPEntryUpdated.Balance,
		SupplierOPEntryUpdated.Discount,
		SupplierOPEntryUpdated.ReceivedAmt
    ORDER BY EntryDatefrmtd ASC;
END






GO
/****** Object:  StoredProcedure [dbo].[SP_GetSupplierPaymentDetailEdit]    Script Date: 09-01-2025 19:55:45 ******/
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  
        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,
        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 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND 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  
        SupplierOPEntryUpdated.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((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 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 (SupplierOPEntryUpdated.PurchaseAmt)
            ELSE (SELECT (SupplierOPEntryUpdated.PurchaseAmt -  
                          COALESCE(SUM(ReceivedAmt), 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,
        '' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN SupplierOPEntryUpdated ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type IS NULL group by SupplierOPEntryUpdated.PurchaseNo,SupplierOPEntryUpdated.ReceivedAmt,SupplierPaymentDetails.Narration,SupplierOPEntryUpdated.PurchaseAmt,SupplierPaymentDetails.PurchaseDate,
SupplierPaymentDetails.PurchaseNo,SupplierPaymentDetails.EntryDate,SupplierPaymentDetails.ReceivedAmount;
END