fix: last purchase rate in item prices report (#23506)
* fix: last purchase rate in item prices report
* fix: last purchase rate in item prices report
* fix: last purchase rate in item prices report
* chore: fetch last purchase rate from update stock purchase invoices
diff --git a/erpnext/stock/report/item_prices/item_prices.py b/erpnext/stock/report/item_prices/item_prices.py
index aa3ed92..12f3297 100644
--- a/erpnext/stock/report/item_prices/item_prices.py
+++ b/erpnext/stock/report/item_prices/item_prices.py
@@ -77,38 +77,33 @@
return item_rate_map
def get_last_purchase_rate():
-
item_last_purchase_rate_map = {}
- query = """select * from (select
- result.item_code,
- result.base_rate
- from (
- (select
- po_item.item_code,
- po_item.item_name,
- po.transaction_date as posting_date,
- po_item.base_price_list_rate,
- po_item.discount_percentage,
- po_item.base_rate
- from `tabPurchase Order` po, `tabPurchase Order Item` po_item
- where po.name = po_item.parent and po.docstatus = 1)
- union
- (select
- pr_item.item_code,
- pr_item.item_name,
- pr.posting_date,
- pr_item.base_price_list_rate,
- pr_item.discount_percentage,
- pr_item.base_rate
- from `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pr_item
- where pr.name = pr_item.parent and pr.docstatus = 1)
- ) result
- order by result.item_code asc, result.posting_date desc) result_wrapper
- group by item_code"""
+ query = """select * from (
+ (select
+ po_item.item_code,
+ po.transaction_date as posting_date,
+ po_item.base_rate
+ from `tabPurchase Order` po, `tabPurchase Order Item` po_item
+ where po.name = po_item.parent and po.docstatus = 1)
+ union
+ (select
+ pr_item.item_code,
+ pr.posting_date,
+ pr_item.base_rate
+ from `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pr_item
+ where pr.name = pr_item.parent and pr.docstatus = 1)
+ union
+ (select
+ pi_item.item_code,
+ pi.posting_date,
+ pi_item.base_rate
+ from `tabPurchase Invoice` pi, `tabPurchase Invoice Item` pi_item
+ where pi.name = pi_item.parent and pi.docstatus = 1 and pi.update_stock = 1)
+ ) result order by result.item_code asc, result.posting_date asc"""
for d in frappe.db.sql(query, as_dict=1):
- item_last_purchase_rate_map.setdefault(d.item_code, d.base_rate)
+ item_last_purchase_rate_map[d.item_code] = d.base_rate
return item_last_purchase_rate_map