How to export CoA (Chart of Account) using SQL Query?

From InfiniteERP Wiki
Revision as of 20:54, 25 June 2024 by Wikiadmin (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

This article will share how to export Openbravo chart of account (CoA) using sql query. Exporting CoA is helpful, especially when we need to reconfigure CoA using Openbravo user interface, then we need to deploy configured CoA to another instance. Openbravo require specific CSV format when you upload COA and you can use this SQL query to produce a table which is suitable with Openbravo requirement.

select
	ev.value as "SearchKey",
	ev.name as "Name",
	ev.description as "Description",
	ev.accounttype as "Type",
	ev.accountsign as "Sign",
	ev.isdoccontrolled as "Document",
	ev.issummary as "Summary",
	ev2.value as "Parent",
	ev.elementlevel as "Level",
	'' as "Operands"
from
	c_elementvalue ev
left join ad_treenode tn on
	tn.node_id = ev.c_elementvalue_id
left join c_elementvalue ev2 on
	ev2.c_elementvalue_id = tn.parent_id
where
	ev.ad_client_id = 'AE56E7FBDE964A198A5CD83CEB9EC51D'

You can change ad_client_id with your desired client to be exported. This SQL query does not include operand (customized element sub tab inside account tree window). Future update on this blog post will explain it.