Report shows purchase invoices paid without a purchase receive

ERPNext Reports
,
1

Hi,

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

Thanks

2

Hi @Ahmed_Moustafa2,

Please apply the Query report and check it.

SELECT

    `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`
    
    WHERE
    
    `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!

3

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”: [

{
	"fieldname":"from_date",
	"label": __("From Date"),
	"fieldtype": "Date",
	"width": "80",
	"default": frappe.datetime.month_start()
},
{
	"fieldname":"to_date",
	"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 ?

4

is date_time a field of purchase receipt?

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

Thanks.