viewATVAT_060.sql - Unknown column
Closed this issue · 6 comments
Hi - thanks for your extension to erpnext,
Problem Description
viewATVAT_060.sql references tabExpense Claim.taxes_and_charges, but this column does not exist on latest erpnext .
ERROR 1054 (42S22) at line 1: Unknown column 'tabExpense Claim.taxes_and_charges' in 'field list'
Do i even need to apply those sql files manually - this should be mentioned somewhere if so.
Version information
ERPNext: v12.10.1 (version-12)
ERPNextAustria: v0.6.3 (master)
Frappe Framework: v12.8.1 (version-12)
Can you please make sure that after the installation "bench migrate" is executed?
You can also adapt the sql queries - the provided files are only samples...
I installed successfully on v13 #8 (comment) but am getting when importing values:
pymysql.err.ProgrammingError: (1146, "Table '7777aeb205cb89f4.viewATVAT_065' doesn't exist")
and yes I was running bench --site erptest.organisation.net migrate and also did a bench clear-cache and even a bench restart.
Do we need to run install_VAT_views.sh manually or should that have been happened with bench migrate?
In frappe log I see
cmd': 'erpnextaustria.erpnextaustria.doctype.at_vat_declaration.at_vat_declaration.get_view_tax'}
frappe.log:Form Dict: {'start_date': '2021-07-01', 'end_date': '2021-07-31', 'view_name': 'viewATVAT_065', 'company': 'faircommerce TEST', 'cmd': 'erpnextaustria.erpnextaustria.doctype.at_vat_declaration.at_vat_declaration.get_view_tax'}
Apparently, in ERPnext v13 the tables referred in viewATVAT_060.sql are empty:
select * from `tabExpense Claim`;
Empty set (0.000 sec)
select * from `tabExpense Taxes and Charges`;
Empty set (0.000 sec)
So I removed that part in viewATVAT_060.sql to be able to successfully import all tables of this app:
CREATE VIEW `viewATVAT_060` AS
SELECT
`tabPurchase Invoice`.`posting_date` AS `posting_date`,
`tabPurchase Invoice`.`name` AS `name`,
`tabPurchase Invoice`.`base_grand_total` AS `base_grand_total`,
`tabPurchase Invoice`.`base_net_total` AS `base_net_total`,
`tabPurchase Invoice`.`taxes_and_charges` AS `taxes_and_charges`,
`tabPurchase Invoice`.`total_taxes_and_charges` AS `total_taxes_and_charges`
FROM `tabPurchase Invoice`
WHERE `docstatus` = 1 AND `taxes_and_charges` LIKE '%060%'
-- UNION SELECT
-- `tabExpense Claim`.`posting_date` AS `posting_date`,
-- `tabExpense Claim`.`name` AS `name`,
-- `tabExpense Claim`.`total_claimed_amount` AS `base_grand_total`,
-- (`tabExpense Claim`.`total_claimed_amount` - `tabExpense Claim`.`total_taxes_and_charges`) AS `base_net_total`,
-- `tabExpense Claim`.`taxes_and_charges` AS `taxes_and_charges`,
-- `tabExpense Claim`.`total_taxes_and_charges` AS `total_taxes_and_charges`
-- FROM `tabExpense Claim`
-- WHERE `docstatus` = 1 AND `taxes_and_charges` LIKE '%060%'
However, when issuing a Get values in an AT VAT declaration of a specific period I only get filled the 000 Revenue field. When comparing all viewATVAT_0NN views we see, that most are just templates and do not yet fetch the right records. So we need to dig into selecting the correct fields - unless somebody already did that job 😉
Where (as in which module/menu) would tabExpense Taxes and Charges and tabExpense Claim be filled, i.e. which type if expenses would have to be created?
Looking at the tabPurchase Invoice table it seems the field we are looking for is holding the name (or implicit foreign key reference) to the sales taxes and charges templates.
We presumably would need a field in tabExpense Claim that references tabExpense Taxes and Charges. But none of the varchar(140) fields seem to fit the bill.
+-------------------------+---------------+------+-----+-----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+---------------+------+-----+-----------------+-------+
| name | varchar(140) | NO | PRI | NULL | |
| creation | datetime(6) | YES | | NULL | |
| modified | datetime(6) | YES | | NULL | |
| modified_by | varchar(140) | YES | | NULL | |
| owner | varchar(140) | YES | | NULL | |
| docstatus | int(1) | NO | | 0 | |
| parent | varchar(140) | YES | MUL | NULL | |
| parentfield | varchar(140) | YES | | NULL | |
| parenttype | varchar(140) | YES | | NULL | |
| idx | int(8) | NO | | 0 | |
| total_claimed_amount | decimal(21,9) | NO | | 0.000000000 | |
| email_id | varchar(140) | YES | | NULL | |
| naming_series | varchar(140) | YES | | NULL | |
| fiscal_year | varchar(140) | YES | | NULL | |
| approval_status | varchar(140) | YES | MUL | Draft | |
| total_amount_reimbursed | decimal(21,9) | NO | | 0.000000000 | |
| _comments | text | YES | | NULL | |
| amended_from | varchar(140) | YES | | NULL | |
| total_sanctioned_amount | decimal(21,9) | NO | | 0.000000000 | |
| employee | varchar(140) | YES | MUL | NULL | |
| _liked_by | text | YES | | NULL | |
| company | varchar(140) | YES | | NULL | |
| _assign | text | YES | | NULL | |
| employee_name | varchar(140) | YES | | NULL | |
| exp_approver | varchar(140) | YES | | NULL | |
| _user_tags | text | YES | | NULL | |
| remark | text | YES | | NULL | |
| task | varchar(140) | YES | | NULL | |
| title | varchar(140) | YES | | {employee_name} | |
| project | varchar(140) | YES | | NULL | |
| posting_date | date | YES | | NULL | |
| vehicle_log | varchar(140) | YES | | NULL | |
| mode_of_payment | varchar(140) | YES | | NULL | |
| cost_center | varchar(140) | YES | | NULL | |
| status | varchar(140) | YES | | Draft | |
| payable_account | varchar(140) | YES | | NULL | |
| is_paid | int(1) | NO | | 0 | |
| total_advance_amount | decimal(21,9) | NO | | 0.000000000 | |
| department | varchar(140) | YES | | NULL | |
| expense_approver | varchar(140) | YES | | NULL | |
| clearance_date | date | YES | | NULL | |
| grand_total | decimal(21,9) | NO | | 0.000000000 | |
| total_taxes_and_charges | decimal(21,9) | NO | | 0.000000000 | |
| delivery_trip | varchar(140) | YES | | NULL | |
+-------------------------+---------------+------+-----+-----------------+-------+
You need the HRMS module in V14+