Purchase Order Items To Be Received - (AMOUNT TO RECEIVE)

ERPNext Stock/Inventory
1

Hello Everyone,

Im new to ERPNExt, there is a Report on STOCK > Purchase Order Items To Be Received,
can anyone help on how to show the Amount to Received?

Does anyone done a Query Report for this?

Thank you for you help

.

Purchase%20Order%20Items%20to%20Be%20Received
Purchase%20Order%20Items%20to%20Be%20Received1356×282 68.9 KB

2

refer this tutorial -
ERPNext - Purchase Cycle (Purchase Order, Purchase Receipt, Purchase Invoice, Payment)

documentation -
https://erpnext.org/docs/user/manual/en/buying/purchase-order
thanks.

3

it’s self hosted or you are using ERPNext Cloud hosting?

4

it’s a query report. if you have self hosted version.

login with Administrator and on this report go to menu and edit.
and paste below query. it’ll add Net amount per item in this report.

select
tabPurchase Order.name as “Purchase Order:Link/Purchase Order:120”,
tabPurchase Order.transaction_date as “Date:Date:100”,
tabPurchase Order Item.schedule_date as “Reqd by Date:Date:110”,
tabPurchase Order.supplier as “Supplier:Link/Supplier:120”,
tabPurchase Order.supplier_name as “Supplier Name::150”,
tabPurchase Order Item.project as “Project”,
tabPurchase Order Item.item_code as “Item Code:Link/Item:120”,
tabPurchase Order Item.qty as “Qty:Float:100”,
tabPurchase Order Item.received_qty as “Received Qty:Float:100”,
tabPurchase Order Item.net_amount as “Net Amount:Float:100”,
(tabPurchase Order Item.qty - ifnull(tabPurchase Order Item.received_qty, 0)) as “Qty to Receive:Float:100”,
tabPurchase Order Item.warehouse as “Warehouse:Link/Warehouse:150”,
tabPurchase Order Item.item_name as “Item Name::150”,
tabPurchase Order Item.description as “Description::200”,
tabPurchase Order Item.brand as “Brand::100”,
tabPurchase Order.company as “Company:Link/Company:”
from
tabPurchase Order, tabPurchase Order Item
where
tabPurchase Order Item.parent = tabPurchase Order.name
and tabPurchase Order.docstatus = 1
and tabPurchase Order.status not in (“Stopped”, “Closed”)
and ifnull(tabPurchase Order Item.received_qty, 0) < ifnull(tabPurchase Order Item.qty, 0)
order by tabPurchase Order.transaction_date asc

5

Hello Everyone,

Thank you for your responses. I appreciate them all.

I have modified your QUERY Adnan, and set the formula like

“Amount to Receive” = ((Qty - Received) * rate)

QUERY:

select 
    `tabPurchase Order`.`name` as "Purchase Order:Link/Purchase Order:120",
	`tabPurchase Order`.`transaction_date` as "Date:Date:100",
	`tabPurchase Order Item`.`schedule_date` as "Reqd by Date:Date:110",
	`tabPurchase Order`.`supplier` as "Supplier:Link/Supplier:120",
	`tabPurchase Order`.`supplier_name` as "Supplier Name::150",
	`tabPurchase Order Item`.`project` as "Project",
	`tabPurchase Order Item`.item_code as "Item Code:Link/Item:120",
	`tabPurchase Order Item`.qty as "Qty:Float:100",
	`tabPurchase Order Item`.received_qty as "Received Qty:Float:100", 
	(`tabPurchase Order Item`.qty - ifnull(`tabPurchase Order Item`.received_qty, 0)) as "Qty to Receive:Float:100",
	(`tabPurchase Order Item`.rate * (`tabPurchase Order Item`.qty - ifnull(`tabPurchase Order Item`.received_qty, 0))) as "Amount to Receive:Float:100",
    `tabPurchase Order Item`.warehouse as "Warehouse:Link/Warehouse:150",
	`tabPurchase Order Item`.item_name as "Item Name::150",
	`tabPurchase Order Item`.description as "Description::200",
    `tabPurchase Order Item`.brand as "Brand::100",
	`tabPurchase Order`.`company` as "Company:Link/Company:"
from
	`tabPurchase Order`, `tabPurchase Order Item`
where
	`tabPurchase Order Item`.`parent` = `tabPurchase Order`.`name`
	and `tabPurchase Order`.docstatus = 1
	and `tabPurchase Order`.status not in ("Stopped", "Closed")
	and ifnull(`tabPurchase Order Item`.received_qty, 0) < ifnull(`tabPurchase Order Item`.qty, 0)
order by `tabPurchase Order`.transaction_date asc