CREATE PROCEDURE 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