fix: create entries for only PR items present in LCV (#36852)
* fix: check if item code exists in lcv before creating gle
* refactor: use qb to fetch lcv items
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 9f1224d..be19bca 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -759,21 +759,22 @@
# Amount added through landed-cost-voucher
if landed_cost_entries:
- for account, amount in landed_cost_entries[(item.item_code, item.name)].items():
- gl_entries.append(
- self.get_gl_dict(
- {
- "account": account,
- "against": item.expense_account,
- "cost_center": item.cost_center,
- "remarks": self.get("remarks") or _("Accounting Entry for Stock"),
- "credit": flt(amount["base_amount"]),
- "credit_in_account_currency": flt(amount["amount"]),
- "project": item.project or self.project,
- },
- item=item,
+ if (item.item_code, item.name) in landed_cost_entries:
+ for account, amount in landed_cost_entries[(item.item_code, item.name)].items():
+ gl_entries.append(
+ self.get_gl_dict(
+ {
+ "account": account,
+ "against": item.expense_account,
+ "cost_center": item.cost_center,
+ "remarks": self.get("remarks") or _("Accounting Entry for Stock"),
+ "credit": flt(amount["base_amount"]),
+ "credit_in_account_currency": flt(amount["amount"]),
+ "project": item.project or self.project,
+ },
+ item=item,
+ )
)
- )
# sub-contracting warehouse
if flt(item.rm_supp_cost):
diff --git a/erpnext/stock/doctype/landed_cost_voucher/landed_cost_voucher.py b/erpnext/stock/doctype/landed_cost_voucher/landed_cost_voucher.py
index 111a0861..7f0dc2d 100644
--- a/erpnext/stock/doctype/landed_cost_voucher/landed_cost_voucher.py
+++ b/erpnext/stock/doctype/landed_cost_voucher/landed_cost_voucher.py
@@ -6,6 +6,7 @@
from frappe import _
from frappe.model.document import Document
from frappe.model.meta import get_field_precision
+from frappe.query_builder.custom import ConstantColumn
from frappe.utils import flt
import erpnext
@@ -19,19 +20,7 @@
self.set("items", [])
for pr in self.get("purchase_receipts"):
if pr.receipt_document_type and pr.receipt_document:
- pr_items = frappe.db.sql(
- """select pr_item.item_code, pr_item.description,
- pr_item.qty, pr_item.base_rate, pr_item.base_amount, pr_item.name,
- pr_item.cost_center, pr_item.is_fixed_asset
- from `tab{doctype} Item` pr_item where parent = %s
- and exists(select name from tabItem
- where name = pr_item.item_code and (is_stock_item = 1 or is_fixed_asset=1))
- """.format(
- doctype=pr.receipt_document_type
- ),
- pr.receipt_document,
- as_dict=True,
- )
+ pr_items = get_pr_items(pr)
for d in pr_items:
item = self.append("items")
@@ -247,3 +236,30 @@
),
tuple([item.valuation_rate] + serial_nos),
)
+
+
+def get_pr_items(purchase_receipt):
+ item = frappe.qb.DocType("Item")
+ pr_item = frappe.qb.DocType(purchase_receipt.receipt_document_type + " Item")
+ return (
+ frappe.qb.from_(pr_item)
+ .inner_join(item)
+ .on(item.name == pr_item.item_code)
+ .select(
+ pr_item.item_code,
+ pr_item.description,
+ pr_item.qty,
+ pr_item.base_rate,
+ pr_item.base_amount,
+ pr_item.name,
+ pr_item.cost_center,
+ pr_item.is_fixed_asset,
+ ConstantColumn(purchase_receipt.receipt_document_type).as_("receipt_document_type"),
+ ConstantColumn(purchase_receipt.receipt_document).as_("receipt_document"),
+ )
+ .where(
+ (pr_item.parent == purchase_receipt.receipt_document)
+ & ((item.is_stock_item == 1) | (item.is_fixed_asset == 1))
+ )
+ .run(as_dict=True)
+ )
diff --git a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
index 0b5dc05..60aefdd 100644
--- a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
+++ b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
@@ -470,27 +470,28 @@
# Amount added through landed-cos-voucher
if d.landed_cost_voucher_amount and landed_cost_entries:
- for account, amount in landed_cost_entries[(d.item_code, d.name)].items():
- account_currency = get_account_currency(account)
- credit_amount = (
- flt(amount["base_amount"])
- if (amount["base_amount"] or account_currency != self.company_currency)
- else flt(amount["amount"])
- )
+ if (d.item_code, d.name) in landed_cost_entries:
+ for account, amount in landed_cost_entries[(d.item_code, d.name)].items():
+ account_currency = get_account_currency(account)
+ credit_amount = (
+ flt(amount["base_amount"])
+ if (amount["base_amount"] or account_currency != self.company_currency)
+ else flt(amount["amount"])
+ )
- self.add_gl_entry(
- gl_entries=gl_entries,
- account=account,
- cost_center=d.cost_center,
- debit=0.0,
- credit=credit_amount,
- remarks=remarks,
- against_account=warehouse_account_name,
- credit_in_account_currency=flt(amount["amount"]),
- account_currency=account_currency,
- project=d.project,
- item=d,
- )
+ self.add_gl_entry(
+ gl_entries=gl_entries,
+ account=account,
+ cost_center=d.cost_center,
+ debit=0.0,
+ credit=credit_amount,
+ remarks=remarks,
+ against_account=warehouse_account_name,
+ credit_in_account_currency=flt(amount["amount"]),
+ account_currency=account_currency,
+ project=d.project,
+ item=d,
+ )
if d.rate_difference_with_purchase_invoice and stock_rbnb:
account_currency = get_account_currency(stock_rbnb)