CREATE PROCEDURE 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) 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
    WHERE SupplierOPEntryUpdated.SupplierCode = @id

    ORDER BY EntryDatefrmtd ASC;
END