Bug in budget check at Material Request level

pioneerpathan · November 15, 2023, 11:40am

So far, we are using the budget module of ERPNExt and it is working fine.

Recently, we came across a case where the system was throwing an error message,
" Annual Budget for Account ACC-1 against department DEPT-1 is 40,26,132.00. It will exceed by 5,67,50,472.50"
as per the configuration for the annual budget check.

As per common memory of our users, it was never expected to have the budget to be crossed at half of the FY. Then after digging into the issue, a bug was found.

It occurs when you order an item with a UOM other than the default/stock UOM of the item and its UOM conversion is not 1:1. Please refer following configuration and transaction details to understand the bug.

Item to Purchase
Item Name: IT-1
Default UOM / Stock UOM: Packet
Alternate UOM Conversion for SKU: Box
Alternate UOM Conversion factor: 1 Box = 150 Packet

Material Request where bug occurred
Raising a material request (i.e. an Indent):
Item: IT-1
Qty: 50
UOM: Box
Rate per Unit: 7,928.31 (This is a rate per box)
Amount / Indent Total: 3,96,415.50 (Qty * Rate)

Stock UOM: Packet
UOM Conversion Factor: 150
Stock Qty: 7500

Now, refer to the budget check logic at Material Request:

github.com

frappe/erpnext/blob/develop/erpnext/accounts/doctype/budget/budget.py#L282


      
          		amount = (
          			get_requested_amount(args, budget) + get_ordered_amount(args, budget) + get_actual_expense(args)
          		)
          
          	elif args.get("doctype") == "Purchase Order" and budget.for_purchase_order:
          		amount = get_ordered_amount(args, budget) + get_actual_expense(args)
          
          	return amount
          
          
          def get_requested_amount(args, budget):
          	item_code = args.get("item_code")
          	condition = get_other_condition(args, budget, "Material Request")
          
          	data = frappe.db.sql(
          		""" select ifnull((sum(child.stock_qty - child.ordered_qty) * rate), 0) as amount
          		from `tabMaterial Request Item` child, `tabMaterial Request` parent where parent.name = child.parent and
          		child.item_code = %s and parent.docstatus = 1 and child.stock_qty > child.ordered_qty and {0} and
          		parent.material_request_type = 'Purchase' and parent.status != 'Stopped'""".format(
          			condition
          		),

Carefully read the code as given above through a GitHub link. As per the logic at line #287, the system finds the pending quantity and amount that to be blocked against it through the equation–> (sum(child.stock_qty - child.ordered_qty) * rate).

We have not yet raised any purchase order, so the pending quantity as per the equation is 7500-0 = 7500. The pending amount (to be blocked against the budget) is: 7500 * 7,928.31 = 59,462,325.00. This is very important, here 7500 stock quantity (Packets) is directly multiplied by the original rate per BOX which is wrong. Hence, the budget blocking amount (Rs. 59,462,325,00) is many times more than the total indent value (Rs. 3,96,415.50).

Possible fixes for the above issue:

Proportionate the “Rate per Unit” in the budget check logic as mentioned above. In my case, instead of considering the per-unit (Box) rate Rs. 7,928.31, it should be proportionated to: 7,928.31 / 150 = 52.8554 (Rate per Packet) and then it should be multiplied by the pending quantity of 7500. It gives (52.8554 * 7500 = 3,96,415,50) which is perfect to consider for budget blocking.
Use “qty” instead of “stock_qty” in the query for budget checking.

Please share your views on this, if my understanding is wrong.