Hi friends,
We are facing below error when we click Include Payment (POS) checkobx while creating Sales Invoice
Error:
App Versions
{
"erpnext": "14.15.0",
"frappe": "14.25.0"
}
Route
Form/Sales Invoice/new-sales-invoice-1
Trackeback
Syntax error in query:
select
mpa.default_account, mpa.parent as mop, mp.type as type
from
`tabMode of Payment Account` mpa,`tabMode of Payment` mp
where
mpa.parent = mp.name and
mpa.company = %s and
mp.enabled = 1 and
mp.name in %s
group by
mp.name ('Muhammadi Enterprises', [])
Traceback (most recent call last):
File "apps/frappe/frappe/app.py", line 69, in application
response = frappe.api.handle()
File "apps/frappe/frappe/api.py", line 54, in handle
return frappe.handler.handle()
File "apps/frappe/frappe/handler.py", line 45, in handle
data = execute_cmd(cmd)
File "apps/frappe/frappe/handler.py", line 83, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File "apps/frappe/frappe/__init__.py", line 1607, in call
return fn(*args, **newargs)
File "apps/frappe/frappe/handler.py", line 307, in run_doc_method
response = doc.run_method(method, args)
File "apps/frappe/frappe/model/document.py", line 909, in run_method
out = Document.hook(fn)(self, *args, **kwargs)
File "apps/frappe/frappe/model/document.py", line 1259, in composer
return composed(self, method, *args, **kwargs)
File "apps/frappe/frappe/model/document.py", line 1241, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File "apps/frappe/frappe/model/document.py", line 906, in fn
return method_object(*args, **kwargs)
File "apps/erpnext/erpnext/accounts/doctype/sales_invoice/sales_invoice.py", line 469, in set_missing_values
pos = self.set_pos_fields(for_validate)
File "apps/erpnext/erpnext/accounts/doctype/sales_invoice/sales_invoice.py", line 558, in set_pos_fields
update_multi_mode_option(self, pos)
File "apps/erpnext/erpnext/accounts/doctype/sales_invoice/sales_invoice.py", line 2354, in update_multi_mode_option
mode_of_payments_info = get_mode_of_payments_info(mode_of_payments, doc.company)
File "apps/erpnext/erpnext/accounts/doctype/sales_invoice/sales_invoice.py", line 2385, in get_mode_of_payments_info
data = frappe.db.sql(
File "apps/frappe/frappe/database/database.py", line 218, in sql
self._cursor.execute(query, values)
File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
result = self._query(query)
File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
conn.query(q)
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
result.read()
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
first_packet = self.connection._read_packet()
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 725, in _read_packet
packet.raise_for_error()
File "env/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "env/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')\n\t\tgroup by\n\t\t\tmp.name' at line 9")
Request Data
{
"type": "POST",
"args": {
"docs": "{\"docstatus\":0,\"doctype\":\"Sales Invoice\",\"name\":\"new-sales-invoice-1\",\"__islocal\":1,\"__unsaved\":1,\"owner\":\"Administrator\",\"naming_series\":\"ACC-SINV-.YYYY.-\",\"company\":\"Muhammadi Enterprises\",\"posting_date\":\"2023-02-01\",\"set_posting_time\":0,\"is_pos\":1,\"is_consolidated\":0,\"is_return\":0,\"update_billed_amount_in_sales_order\":0,\"is_debit_note\":0,\"currency\":\"PKR\",\"selling_price_list\":\"Selling Bahawalpur Dec 2021\",\"price_list_currency\":\"PKR\",\"ignore_pricing_rule\":0,\"update_stock\":0,\"disable_rounded_total\":0,\"apply_discount_on\":\"Grand Total\",\"is_cash_or_non_trade_discount\":0,\"total_billing_amount\":0,\"allocate_advances_automatically\":0,\"write_off_outstanding_amount_automatically\":0,\"redeem_loyalty_points\":0,\"ignore_default_payment_terms_template\":0,\"party_account_currency\":\"PKR\",\"is_opening\":\"No\",\"letter_head\":\"Muhammadi Lubricants\",\"group_same_items\":0,\"status\":\"Draft\",\"is_internal_customer\":0,\"is_discounted\":0,\"items\":[{\"docstatus\":0,\"doctype\":\"Sales Invoice Item\",\"name\":\"new-sales-invoice-item-1\",\"__islocal\":1,\"__unsaved\":1,\"owner\":\"Administrator\",\"has_item_scanned\":0,\"item_group\":\"Automotive\",\"stock_uom\":\"Litre\",\"margin_type\":\"\",\"is_free_item\":0,\"grant_commission\":0,\"delivered_by_supplier\":0,\"is_fixed_asset\":0,\"enable_deferred_revenue\":0,\"allow_zero_valuation_rate\":0,\"cost_center\":\"Main - MEH\",\"page_break\":0,\"parent\":\"new-sales-invoice-1\",\"parentfield\":\"items\",\"parenttype\":\"Sales Invoice\",\"idx\":1,\"qty\":0,\"conversion_factor\":0,\"stock_qty\":0,\"price_list_rate\":0,\"base_price_list_rate\":0,\"margin_rate_or_amount\":0,\"rate_with_margin\":0,\"discount_amount\":0,\"base_rate_with_margin\":0,\"rate\":0,\"amount\":0,\"base_rate\":0,\"base_amount\":0,\"stock_uom_rate\":0,\"net_rate\":0,\"net_amount\":0,\"base_net_rate\":0,\"base_net_amount\":0,\"weight_per_unit\":0,\"total_weight\":0,\"incoming_rate\":0,\"actual_batch_qty\":0,\"actual_qty\":0,\"delivered_qty\":0}],\"posting_time\":\"18:27:15\",\"conversion_rate\":1,\"plc_conversion_rate\":1,\"company_tax_id\":null,\"company_address\":\"\",\"company_address_display\":\"\",\"debit_to\":\"Debtors - MEH\",\"base_net_total\":0,\"net_total\":0,\"base_total\":0,\"total\":0,\"total_qty\":0,\"rounding_adjustment\":0,\"grand_total\":0,\"base_grand_total\":0,\"total_taxes_and_charges\":0,\"base_total_taxes_and_charges\":0,\"base_rounding_adjustment\":0,\"rounded_total\":0,\"base_rounded_total\":0,\"in_words\":\"\",\"base_in_words\":\"\",\"base_discount_amount\":0,\"total_advance\":0,\"write_off_amount\":0,\"paid_amount\":0,\"base_paid_amount\":0,\"payments\":[],\"change_amount\":0,\"base_change_amount\":0,\"outstanding_amount\":0,\"amount_eligible_for_commission\":0,\"total_commission\":null}",
"method": "set_missing_values"
},
"headers": {},
"error_handlers": {},
"url": "/api/method/run_doc_method"
}
Response Data
{
"exception": "pymysql.err.ProgrammingError: (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')\\n\\t\\tgroup by\\n\\t\\t\\tmp.name' at line 9\")"
}