Custom account payables summary report with aging

ERPNext Accounting
1

Hi, anyone know how to customer account reports by supplier wise with aging for account payables and account receivebles.

2

Hi,

What kind of customization do you require?

Thanks,

Divyesh M.

3

Hi mangroliya,

In the erp next accounts payables report already is there but i want to customize that one or create the new report. For that report I developed a sql query but data is not matching to the application.

SELECT
name “Reference”,
due_date “Date”,
base_grand_total “Invoice Amount”,
paid_amount “Paid Amount”,
outstanding_amount,
is_paid,
CASE
WHEN due_date >= CURRENT_DATE THEN Round(base_grand_total - paid_amount)
ELSE 0
END AS “Current”,
CASE
WHEN due_date < CURRENT_DATE AND due_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN Round(base_grand_total - paid_amount)
ELSE 0
END AS “30 Days”,
CASE
WHEN due_date < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) AND due_date >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) THEN Round(base_grand_total - paid_amount)
ELSE 0
END AS “60 Days”,
CASE
WHEN due_date < DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) AND due_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) THEN Round(base_grand_total - paid_amount)
ELSE 0
END AS “90 Days”,
CASE
WHEN due_date < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) AND due_date >= DATE_SUB(CURRENT_DATE, INTERVAL 120 DAY) THEN Round(base_grand_total - paid_amount)
ELSE 0
END AS “120 Days”,
CASE
WHEN due_date < DATE_SUB(CURRENT_DATE, INTERVAL 120 DAY) AND due_date >= DATE_SUB(CURRENT_DATE, INTERVAL 150 DAY) THEN Round(base_grand_total - paid_amount)
ELSE 0
END AS “150 Days”,
CASE
WHEN due_date < DATE_SUB(CURRENT_DATE, INTERVAL 150 DAY) THEN Round(base_grand_total - paid_amount)
ELSE 0
END AS “180 Days Above”
FROM
tabPurchase Invoice;

Please help me on this i want only these fields for the report.

Thanks & Regards,
Harsha