"Unknown column 'item_code' in 'where clause'"

dkatubulla · August 4, 2015, 11:32am

Hello All,

I have created a DocType “Customer Category”. It is applied for customers on creation. A pricing rule was defined based on the “Customer Category”. Pricing rule works fine in “Sales Invoice”. But when the invoice is being saved an error occurs. “Unknown column ‘item_code’ in ‘where clause’”. Error doesn’t occur when the same item is used without applying the pricing rule.

Pricing Rule
Apply On : Item Code
Item Code : Body Wash
Selling
Applicable For : Customer Category
Customer Category : Member
Price or Discount : Price
Price : 0.00

Traceback (innermost last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/form/save.py”, line 18, in savedocs
doc.save()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 216, in save
self.insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 192, in insert
d.db_insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/base_document.py”, line 254, in db_insert
), d.values())
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 135, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘item_code’ in ‘where clause’”)

Traceback (innermost last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 51, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 69, in handle
execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 92, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 751, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/form/save.py”, line 18, in savedocs
doc.save()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 216, in save
self.insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 192, in insert
d.db_insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/base_document.py”, line 254, in db_insert
), d.values())
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 135, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘item_code’ in ‘where clause’”)

nabinhait · August 4, 2015, 12:56pm

Please click on “Report this Issue”, when you get the error. That will give some more information to debug the issue.

dkatubulla · August 5, 2015, 4:44am

Did it before posting here. Since i’m using erpnext on a local machine i’m not sure about the mail’s being sent.

dkatubulla · August 5, 2015, 5:10am

App Versions
{
“erpnext”: “5.1.4”,
“frappe”: “5.0.34”
}

Route
Form/Sales Invoice/New Sales Invoice 1

Error Report
Traceback (innermost last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/form/save.py”, line 18, in savedocs
doc.save()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 216, in save
self.insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 192, in insert
d.db_insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/base_document.py”, line 254, in db_insert
), d.values())
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 135, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘item_code’ in ‘where clause’”)

Traceback (innermost last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 51, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 69, in handle
execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 92, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 751, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/form/save.py”, line 18, in savedocs
doc.save()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 216, in save
self.insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 192, in insert
d.db_insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/base_document.py”, line 254, in db_insert
), d.values())
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 135, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘item_code’ in ‘where clause’”)

Request Data
{
“type”: “POST”,
“args”: {
“doc”: “{"docstatus":0,"doctype":"Sales Invoice","name":"New Sales Invoice 1","__islocal":1,"__unsaved":1,"owner":"Administrator","naming_series":"SINV-","company":"Car Service","posting_date":"2015-08-05","currency":"LKR","selling_price_list":"Standard Selling","apply_discount_on":"Grand Total","source":"","is_opening":"No","c_form_applicable":"No","posting_time":"09:41:30","fiscal_year":"2015","recurring_type":"","due_date":"2015-08-05","price_list_currency":"LKR","plc_conversion_rate":1,"conversion_rate":1,"category":"Member","customer":"UP BBS 0728","address_display":null,"customer_group":"Commercial","debit_to":"Debtors - CS","contact_mobile":null,"customer_address":null,"contact_email":null,"shipping_address":null,"contact_display":"Yasitha","contact_person":"Yasitha-UP BBS 0728","territory":"Sri Lanka","shipping_address_name":null,"sales_team":[],"customer_name":"UP BBS 0728","mode_of_payment":"Cash","items":[{"docstatus":0,"doctype":"Sales Invoice Item","name":"New Sales Invoice Item 1","__islocal":1,"__unsaved":1,"owner":"Administrator","stock_uom":"Nos","cost_center":"Main - CS","parent":"New Sales Invoice 1","parentfield":"items","parenttype":"Sales Invoice","idx":1,"item_code":"Body Wash","item_group":"Services","description":"Body Wash","stock_qty":1,"base_price_list_rate":0,"image":"","barcode":null,"base_amount":0,"expense_account":null,"item_tax_rate":"{}","rate":0,"qty":1,"price_list_rate":0,"pricing_rule":"PRULE00002","income_account":"Sales - CS","item_name":"Body Wash","amount":0,"discount_percentage":0,"min_order_qty":"","net_rate":0,"conversion_factor":1,"customer_item_code":null,"batch_no":null,"warehouse":null,"net_amount":0,"base_rate":0,"brand":null,"uom":"Nos","base_net_rate":0,"base_net_amount":0,"actual_batch_qty":0,"actual_qty":0,"delivered_qty":0}],"base_net_total":0,"net_total":0,"base_total":0,"total":0,"grand_total":0,"base_grand_total":0,"total_taxes_and_charges":0,"base_total_taxes_and_charges":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,"outstanding_amount":0,"total_commission":null}”,
“action”: “Save”,
“cmd”: “frappe.desk.form.save.savedocs”,
“_type”: “POST”
},
“btn”: {},
“freeze”: true,
“freeze_message”: “Saving”,
“url”: “/”
}

Response JSON
{
“_server_messages”: “["Did not save"]”
}

nabinhait · August 5, 2015, 9:36am

Seems like, somehow you are assigning item_code value in parent document (sales invoice).