search

8848digital / erpnext

Free and Open Source Enterprise Resource Planning (ERP)
https://erpnext.com
GNU General Public License v3.0
0 stars 0 forks source link

Material Request: While new material request creation through fetch Items from Bill of Materials getting an error #24

Closed 8848swa closed 2 months ago

8848swa commented 2 months ago

Information about bug

While new material request creation through fetch Items from Bill of Materials getting an error image

Module

stock

Version

ERPNext: v15.34.0 (version-15)

Frappe Framework: v15.41.0 (version-15)

Payments: v0.0.1 (version-15)

Installation method

manual install

Relevant log output / Stack trace / Full Error Message.

### App Versions

{
    "erpnext": "15.34.0",
    "frappe": "15.41.0",
    "payments": "0.0.1"
}

Route

Form/Material Request/new-material-request-aiadvskrqa

Traceback

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 114, in application
    response = frappe.api.handle(request)
  File "apps/frappe/frappe/api/__init__.py", line 49, in handle
    data = endpoint(**arguments)
  File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 49, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 85, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1775, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
    return func(*args, **kwargs)
  File "apps/frappe/frappe/desk/form/save.py", line 37, in savedocs
    doc.submit()
  File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
    return func(*args, **kwargs)
  File "apps/frappe/frappe/model/document.py", line 1048, in submit
    return self._submit()
  File "apps/frappe/frappe/model/document.py", line 1031, in _submit
    return self.save()
  File "apps/frappe/frappe/model/document.py", line 337, in save
    return self._save(*args, **kwargs)
  File "apps/frappe/frappe/model/document.py", line 390, in _save
    self.run_post_save_methods()
  File "apps/frappe/frappe/model/document.py", line 1131, in run_post_save_methods
    self.run_method("on_submit")
  File "apps/frappe/frappe/model/document.py", line 962, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "apps/frappe/frappe/model/document.py", line 1322, in composer
    return composed(self, method, *args, **kwargs)
  File "apps/frappe/frappe/model/document.py", line 1304, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "apps/frappe/frappe/model/document.py", line 959, in fn
    return method_object(*args, **kwargs)
  File "apps/erpnext/erpnext/selling/doctype/sales_order/sales_order.py", line 404, in on_submit
    self.update_reserved_qty()
  File "apps/erpnext/erpnext/selling/doctype/sales_order/sales_order.py", line 530, in update_reserved_qty
    update_bin_qty(item_code, warehouse, {"reserved_qty": get_reserved_qty(item_code, warehouse)})
  File "apps/erpnext/erpnext/stock/stock_balance.py", line 95, in get_reserved_qty
    reserved_qty = frappe.db.sql(
  File "apps/frappe/frappe/database/postgres/database.py", line 218, in sql
    return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
  File "apps/frappe/frappe/database/database.py", line 227, in sql
    self._cursor.execute(query, values)
psycopg2.errors.UndefinedFunction: function if(integer, numeric, integer) does not exist
LINE 2: ...npi_qty * ((so_item_qty - so_item_delivered_qty - if(dont_re...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Request Data

{
    "type": "POST",
    "args": {
        "doc": "{\"name\":\"SAL-ORD-2024-00002\",\"owner\":\"Administrator\",\"creation\":\"2024-09-25 11:54:50.557950\",\"modified\":\"2024-09-25 11:54:50.557950\",\"modified_by\":\"Administrator\",\"docstatus\":0,\"idx\":0,\"title\":\"{customer_name}\",\"naming_series\":\"SAL-ORD-.YYYY.-\",\"customer\":\"Test\",\"customer_name\":\"Test\",\"tax_id\":null,\"order_type\":\"Sales\",\"transaction_date\":\"2024-09-25\",\"delivery_date\":\"2024-09-30\",\"po_no\":null,\"po_date\":null,\"company\":\"8848 Digital\",\"skip_delivery_note\":0,\"amended_from\":null,\"cost_center\":null,\"project\":null,\"currency\":\"INR\",\"conversion_rate\":1,\"selling_price_list\":\"Standard Selling\",\"price_list_currency\":\"INR\",\"plc_conversion_rate\":1,\"ignore_pricing_rule\":0,\"scan_barcode\":null,\"set_warehouse\":null,\"reserve_stock\":0,\"total_qty\":1,\"total_net_weight\":0,\"base_total\":120,\"base_net_total\":120,\"total\":120,\"net_total\":120,\"tax_category\":\"\",\"taxes_and_charges\":null,\"exempt_from_sales_tax\":0,\"shipping_rule\":null,\"incoterm\":null,\"named_place\":null,\"base_total_taxes_and_charges\":0,\"total_taxes_and_charges\":0,\"base_grand_total\":120,\"base_rounding_adjustment\":0,\"base_rounded_total\":120,\"base_in_words\":\"\",\"grand_total\":120,\"rounding_adjustment\":0,\"rounded_total\":120,\"in_words\":\"\",\"advance_paid\":0,\"disable_rounded_total\":0,\"apply_discount_on\":\"Grand Total\",\"base_discount_amount\":0,\"coupon_code\":null,\"additional_discount_percentage\":0,\"discount_amount\":0,\"other_charges_calculation\":null,\"customer_address\":null,\"address_display\":null,\"customer_group\":\"All Customer Groups\",\"territory\":\"All Territories\",\"contact_person\":null,\"contact_display\":null,\"contact_phone\":null,\"contact_mobile\":null,\"contact_email\":null,\"shipping_address_name\":null,\"shipping_address\":null,\"dispatch_address_name\":null,\"dispatch_address\":null,\"company_address\":\"\",\"company_address_display\":\"\",\"payment_terms_template\":null,\"tc_name\":null,\"terms\":null,\"status\":\"Draft\",\"delivery_status\":\"Not Delivered\",\"per_delivered\":0,\"per_billed\":0,\"per_picked\":0,\"billing_status\":\"Not Billed\",\"sales_partner\":null,\"amount_eligible_for_commission\":120,\"commission_rate\":0,\"total_commission\":0,\"loyalty_points\":0,\"loyalty_amount\":0,\"from_date\":null,\"to_date\":null,\"auto_repeat\":null,\"letter_head\":null,\"group_same_items\":0,\"select_print_heading\":null,\"language\":\"en\",\"is_internal_customer\":0,\"represents_company\":null,\"source\":null,\"inter_company_order_reference\":null,\"campaign\":null,\"party_account_currency\":\"INR\",\"doctype\":\"Sales Order\",\"sales_team\":[],\"items\":[{\"name\":\"9fnp16ngvi\",\"owner\":\"Administrator\",\"creation\":\"2024-09-25 11:54:50.557950\",\"modified\":\"2024-09-25 11:54:50.557950\",\"modified_by\":\"Administrator\",\"docstatus\":0,\"idx\":1,\"item_code\":\"Test1\",\"customer_item_code\":null,\"ensure_delivery_based_on_produced_serial_no\":0,\"is_stock_item\":1,\"reserve_stock\":0,\"delivery_date\":\"2024-09-30\",\"item_name\":\"Test1\",\"description\":\"Test1\",\"item_group\":\"Consumable\",\"brand\":null,\"image\":\"\",\"qty\":1,\"stock_uom\":\"Nos\",\"uom\":\"Nos\",\"conversion_factor\":1,\"stock_qty\":1,\"stock_reserved_qty\":0,\"price_list_rate\":0,\"base_price_list_rate\":0,\"margin_type\":\"\",\"margin_rate_or_amount\":0,\"rate_with_margin\":0,\"discount_percentage\":0,\"discount_amount\":0,\"base_rate_with_margin\":0,\"rate\":120,\"amount\":120,\"item_tax_template\":null,\"base_rate\":120,\"base_amount\":120,\"pricing_rules\":\"\",\"stock_uom_rate\":120,\"is_free_item\":0,\"grant_commission\":1,\"net_rate\":120,\"net_amount\":120,\"base_net_rate\":120,\"base_net_amount\":120,\"billed_amt\":0,\"valuation_rate\":0,\"gross_profit\":120,\"delivered_by_supplier\":0,\"supplier\":null,\"weight_per_unit\":0,\"total_weight\":0,\"weight_uom\":null,\"warehouse\":\"Stores - 8D\",\"target_warehouse\":null,\"prevdoc_docname\":null,\"quotation_item\":null,\"against_blanket_order\":0,\"blanket_order\":null,\"blanket_order_rate\":0,\"bom_no\":null,\"projected_qty\":10,\"actual_qty\":0,\"ordered_qty\":0,\"planned_qty\":0,\"production_plan_qty\":0,\"work_order_qty\":0,\"delivered_qty\":0,\"produced_qty\":0,\"returned_qty\":0,\"picked_qty\":0,\"additional_notes\":null,\"page_break\":0,\"item_tax_rate\":\"{}\",\"transaction_date\":\"2024-09-25\",\"material_request\":null,\"purchase_order\":null,\"material_request_item\":null,\"purchase_order_item\":null,\"parent\":\"SAL-ORD-2024-00002\",\"parentfield\":\"items\",\"parenttype\":\"Sales Order\",\"doctype\":\"Sales Order Item\",\"__unsaved\":1}],\"pricing_rules\":[],\"packed_items\":[],\"taxes\":[],\"payment_schedule\":[{\"name\":\"9fnq6akskp\",\"owner\":\"Administrator\",\"creation\":\"2024-09-25 11:54:50.678488\",\"modified\":\"2024-09-25 11:54:50.678488\",\"modified_by\":\"Administrator\",\"docstatus\":0,\"idx\":1,\"payment_term\":null,\"description\":null,\"due_date\":\"2024-09-25\",\"mode_of_payment\":null,\"invoice_portion\":100,\"discount_type\":null,\"discount_date\":null,\"discount\":0,\"payment_amount\":120,\"outstanding\":120,\"paid_amount\":0,\"discounted_amount\":0,\"base_payment_amount\":120,\"parent\":\"SAL-ORD-2024-00002\",\"parentfield\":\"payment_schedule\",\"parenttype\":\"Sales Order\",\"doctype\":\"Payment Schedule\"}],\"__last_sync_on\":\"2024-09-25T06:24:43.480Z\"}",
        "action": "Submit"
    },
    "btn": {
        "jQuery370080973804964452571": {
            "events": {
                "click": [
                    {
                        "type": "click",
                        "origType": "click",
                        "guid": 5019,
                        "namespace": ""
                    }
                ]
            }
        }
    },
    "freeze": true,
    "headers": {},
    "error_handlers": {},
    "url": "/api/method/frappe.desk.form.save.savedocs",
    "request_id": null
}

Response Data

{
    "exception": "",
    "exc_type": "UndefinedFunction",
    "_exc_source": "erpnext (app)",
    "_debug_messages": "[\"Error in query:\\nfunction if(integer, numeric, integer) does not exist\\nLINE 2: ...npi_qty * ((so_item_qty - so_item_delivered_qty - if(dont_re...\\n                                                             ^\\nHINT:  No function matches the given name and argument types. You might need to add explicit type casts.\\n\"]"
}
shubham-DT commented 2 months ago

Issue is resolved PR -33

aasif-patel commented 2 months ago

@shubham-DT same issue occurring if fetching data without "Fetch exploded BOM (including sub-assemblies)" checked

App Versions

{
    "erpnext": "15.34.0",
    "frappe": "15.41.0",
    "payments": "0.0.1"
}

Route

Form/Material Request/MAT-MR-2024-00004

Traceback

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 114, in application
    response = frappe.api.handle(request)
  File "apps/frappe/frappe/api/__init__.py", line 49, in handle
    data = endpoint(**arguments)
  File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 49, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 85, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1775, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
    return func(*args, **kwargs)
  File "apps/erpnext/erpnext/manufacturing/doctype/bom/bom.py", line 1204, in get_bom_items
    items = get_bom_items_as_dict(bom, company, qty, fetch_exploded, include_non_stock_items=True).values()
  File "apps/erpnext/erpnext/manufacturing/doctype/bom/bom.py", line 1181, in get_bom_items_as_dict
    items = frappe.db.sql(query, {"qty": qty, "bom": bom, "company": company}, as_dict=True)
  File "apps/frappe/frappe/database/postgres/database.py", line 218, in sql
    return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
  File "apps/frappe/frappe/database/database.py", line 227, in sql
    self._cursor.execute(query, values)
psycopg2.errors.UndefinedColumn: column "bom_item_idx" does not exist
LINE 48:                 bom_item_idx
                         ^

Request Data

{
    "type": "POST",
    "args": {
        "bom": "BOM-_Test FG Item 2-001",
        "warehouse": "_Test Rejected Warehouse - _TC",
        "qty": 1,
        "fetch_exploded": 0,
        "company": "8848 Digital"
    },
    "headers": {},
    "error_handlers": {},
    "url": "/api/method/erpnext.manufacturing.doctype.bom.bom.get_bom_items",
    "request_id": null
}

Response Data

{
    "exception": "",
    "exc_type": "UndefinedColumn",
    "_exc_source": "erpnext (app)",
    "_debug_messages": "[\"Error in query:\\ncolumn \\\"bom_item_idx\\\" does not exist\\nLINE 48:                 bom_item_idx\\n                         ^\\n\"]"
}
shubham-DT commented 2 months ago

fixed the issue of creation MR from BOM when fetch_explode_bom is unchecked PR - 45

aasif-patel commented 2 months ago

Resolved, Hence closing...