Report shows purchase invoices paid without a purchase receive


I need a SQL query report to show purchase invoices paid without a purchase receive.


Hi @Ahmed_Moustafa2,

Please apply the Query report and check it.


    `tabPurchase Invoice`.name as `Purchase Invoice:Link/Purchase Invoice:150`,
    `tabPurchase Invoice`.supplier as `Supplier:Link/Supplier:150`,
    `tabPurchase Invoice`.grand_total as `Grand Total:Float:120`
    FROM `tabPurchase Invoice`, `tabPurchase Invoice Item`
    `tabPurchase Invoice Item`.`parent` = `tabPurchase Invoice`.`name`
    AND `tabPurchase Invoice Item`.purchase_receipt IS NULL
    AND `tabPurchase Invoice`.docstatus = 1
    AND `tabPurchase Invoice`.status = "Paid"
    GROUP BY `tabPurchase Invoice`.name DESC

Please set columns or values as your according.

Thank You!

Thank you very much!

just one more thing, I add this line to the WHERE cluse " date_time >= %(from_date)s and date_time <= %(to_date)s " and I created a .js file with the following :

frappe.query_reports[“Purchase invoices paid without a purchase receipt”] = {
“filters”: [

	"label": __("From Date"),
	"fieldtype": "Date",
	"width": "80",
	"default": frappe.datetime.month_start()
	"label": __("To Date"),
	"fieldtype": "Date",
	"width": "80",
	"default": frappe.datetime.month_end()


However, error message KeyError: ‘from_date’ showed up.
How can I fix the from date to date filter ?

is date_time a field of purchase receipt?

if date_time is not a field then put posting_date.
Then check it.