GO
create 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