Payment Details report - query report

1

I’m trying to generate a Payment Details report that includes the payment mode. I’ve created a query report, but it’s not working as expected. Can someone please assist me?

SELECT
pe.name AS “Payment Entry”,
pe.payment_type,
pe.paid_amount AS “Amount”, – Use the correct column for amount (e.g., paid_amount or total_amount)
pe.paid_from AS “Paid From”,
pe.paid_to AS “Paid To”,
pe.party_type,
pe.party,
pe.reference_no AS “Reference No”,
pe.posting_date AS “Payment Date”, – Replace date with posting_date (or the correct date column)
pe.status AS “Payment Status”,
je.name AS “Journal Entry”,
je.voucher_type AS “Voucher Type”,
je.voucher_no AS “Voucher No”,
je.posting_date AS “Posting Date”,
je.status AS “Journal Entry Status”,
acc.account_name AS “Account”,
ser.name AS “Sales Invoice”
FROM
tabPayment Entry pe
LEFT JOIN
tabJournal Entry je ON pe.journal_entry = je.name
LEFT JOIN
tabAccount acc ON pe.account = acc.name
LEFT JOIN
tabPayment Entry Reference per ON pe.name = per.payment_entry
LEFT JOIN
tabSales Invoice ser ON per.reference_name = ser.name
WHERE
pe.docstatus = 1 – Only submitted entries
AND pe.payment_type = ‘Pay’ – Or ‘Receive’ as needed
AND pe.posting_date BETWEEN %(from_date)s AND %(to_date)s – Date filter
ORDER BY
pe.posting_date DESC;

2

There are several issues with your query. First, there is no column named voucher_no or status in the selected columns. Additionally, there is no column called journal_entry in the join condition with the alias “pe”.

If these are custom columns, please verify that they exist in the relevant tables. Otherwise, you may need to adjust the column names accordingly.

3

Can I get sample query if you can?

4

As I mentioned in my previous response, your query has some missing columns, and your join conditions are not valid. It is hard to share a sample query without knowing the actual requirements. If you explain what you want and why you are joining those tables, then maybe I or someone from the community can help you.

5

I need to get details sales invoice amount , payment entry amount and outstanding . Those details invoice wise

6

Something like this?

SELECT
    si.name AS invoice_number,
    si.posting_date AS invoice_date,
    si.customer AS customer,
    si.grand_total AS invoice_amount,
    IFNULL(
        (SELECT SUM(per.allocated_amount)
         FROM `tabPayment Entry Reference` per
         JOIN `tabPayment Entry` pe ON per.parent = pe.name
         WHERE per.reference_name = si.name
           AND pe.docstatus = 1),
        0
    ) AS payment_amount,
    (si.grand_total - IFNULL(
        (SELECT SUM(per.allocated_amount)
         FROM `tabPayment Entry Reference` per
         JOIN `tabPayment Entry` pe ON per.parent = pe.name
         WHERE per.reference_name = si.name
           AND pe.docstatus = 1),
        0
    )) AS outstanding_amount
FROM
    `tabSales Invoice` si
WHERE
    si.docstatus = 1
ORDER BY
    si.posting_date DESC;
7

thank you very much

actually i need like this

Customer Invoice Amount Return Invoice Amount Paid Amount Outstanding Amount
Customer A 10000.00 2000.00 5000.00 5000.00
Customer B 15000.00 0.00 10000.00 5000.00
8

Please check the simple query, and then set it according to the scenario.

SELECT 
    si.customer AS "Customer:Link/Customer:150",
    SUM(CASE WHEN si.is_return = 0 THEN si.grand_total ELSE 0 END) AS "Invoice Amount:Currency:120",
    SUM(CASE WHEN si.is_return = 1 THEN si.grand_total ELSE 0 END) AS "Return Invoice Amount:Currency:150",
    SUM(CASE WHEN si.is_return = 0 THEN si.grand_total ELSE 0 END) - SUM(si.outstanding_amount) AS "Paid Amount:Currency:120",
    SUM(si.outstanding_amount) AS "Outstanding Amount:Currency:150"
FROM 
    `tabSales Invoice` si
WHERE 
    si.docstatus = 1
GROUP BY 
    si.customer
ORDER BY 
    si.customer;