Custom Report based on Requested Items to be Ordered

ERPNext Reports
1

Here’s another report that someone might find useful.

I created a custom field for the Item Supplier Document that I called _part_no_url labelled simply URL. This field is of type LongText and I customized the Item Supplier Form to show the field “In List View” so that it appears on the Item Form in the Supplier Details section. In this field I paste the URL that points to the Supplier’s website for purchasing the item. If the part_no_url field is NULL then the report just show s the Supplier Part Number as regular text.

When you click on the Supplier Part on the report, the URL is opened in a new tab with a name referring to the Supplier. That way if you have multiple items to be ordered for a given supplier, it only opens one browser tab for the Supplier and any subsequent URLS for the same supplier replace the existing tab. This way you don’t end up with multiple tabs for each supplier. Just one. You can order your items one-by-one in the same tab this way.

select 
    mr.name as "Material Request:Link/Material Request:120",
    mr.transaction_date as "Date:Date:100",
    mr_item.item_code as "Item Code:Link/Item:75",
    CONCAT("<a href=\"","/desk#Form/Item/",mr_item.item_code,"\">",mr_item.item_name,"</a>") as "Item Name::160",
    sum(ifnull(mr_item.qty, 0)) as "Qty:Float:100",
    sum(ifnull(mr_item.ordered_qty, 0)) as "Ordered Qty:Float:100", 
    (sum(mr_item.qty) - sum(ifnull(mr_item.ordered_qty, 0))) as "Qty to Order:Float:100",
    i.default_supplier as "Supplier:Link/Supplier:200",
    IF(ISNULL(i_supplier.part_no_url),i_supplier.supplier_part_no,CONCAT("<a href='", i_supplier.part_no_url, "' target='",i_supplier.supplier,"'>",i_supplier.supplier_part_no,"</a>")) as "Part::160"
	    	   	
from
    `tabMaterial Request` mr, `tabMaterial Request Item` mr_item, `tabItem` i
    left join `tabItem Supplier` i_supplier on (i_supplier.parent = i.name AND i_supplier.supplier = i.default_supplier)

where
    mr_item.parent = mr.name
    and mr.material_request_type = "Purchase"
    and mr.docstatus = 1
    and mr.status != "Stopped"
    and i.name = mr_item.item_code

group by mr.name, mr_item.item_code

having
    sum(ifnull(mr_item.ordered_qty, 0)) < sum(ifnull(mr_item.qty, 0))

order by i.default_supplier asc

Hope this report is useful for someone.

2 Likes