Custom Report based on Purchase Order Items to be Received

Someone might find this useful so I thought I’d post it here.

This is a report that I call Purchased Items to be Received. It’s based on the Purchase Order Items to be Received report that is default in ERPNext. It drops a few fields that I don’t particularly need like Item Description and Project and adds the Supplier Part Number as well as accounts for the purchase UOM and conversion factor. It also links to the Supplier website when you click on the Supplier Part field value.

Note: This report is dependent on a custom field of type LONGTEXT called part_no_url added to the Item Supplier document, in which I save the URL pointing to the Supplier’s part on their ordering web page. This is really a benefit to another custom report (that I will post as well) that replaces the Requested Items to be Ordered report.

select 
	tpo.name as "PO:Link/Purchase Order:75",
	tpo.transaction_date as "Date:Date:90",
	tpoi.schedule_date as "Reqd by Date:Date:90",
	tpo.supplier as "Supplier:Link/Supplier:120",
	IF(ISNULL(tis.part_no_url),tis.supplier_part_no,CONCAT("<a href='", tis.part_no_url, "' target='",tis.supplier,"'>",tis.supplier_part_no,"</a>")) as "Supplier Part::120",
	tpoi.item_code as "Our Part:Link/Item:75",
	CONCAT("<a href=\"","/desk#Form/Item/",tpoi.item_code,"\">",tpoi.item_name,"</a>") as "Item Name:Link/Item:160",
	(tpoi.qty * tpoi.conversion_factor) as "Qty:Int:70",
	(tpoi.received_qty * tpoi.conversion_factor) as "Received:Int:70", 
	((tpoi.qty * tpoi.conversion_factor) - ifnull((tpoi.received_qty * tpoi.conversion_factor), 0)) as "To Receive:Int:70",
    tpoi.warehouse as "Warehouse:Link/Warehouse:150"
	
from
	`tabPurchase Order` tpo, 
	`tabPurchase Order Item` tpoi
	LEFT JOIN `tabItem Supplier` tis on (tis.parent = tpoi.item_code AND tis.supplier_part_no = tpoi.supplier_part_no)
where
	tpoi.parent = tpo.name
	and tpo.docstatus = 1
	and tpo.status not in ("Stopped", "Closed")
	and ifnull(tpoi.received_qty, 0) < ifnull(tpoi.qty, 0)

order by tpo.transaction_date asc

I have a few more ideas for this report, such as adding Expected Delivery and Tracking Number fields, but need to figure out a good way to add values to these fields (probably on the Purchase Order) after the PO has been Submitted.

I hope someone might find this useful. It’s worked well for me. :wink:

1 Like

Hi @jvantslot
its actually more useful can you please provide us the way and within which DocTypes we must add your script and if you can upload some .png images will be good
thank you