ADempiere and iDempiere – Tree Management

Hi Everyone,

The purpose of this post is to help you understand some of the mystery behind ADempiere and iDempiere tree management. Here are some quick bullets:

1. All trees except Product are held in the AD_TreeNode. Products are held in AD_TreeNodePR table.
2. The ‘Tree’ window defines AD_Tree_ID.
3. Inside the AD_TreeNode Table
   1. The Node_ID is the record ID of the tree’s child record.
   2. The Parent_ID is the record ID of the tree’s parent record. Parent records must have the ‘summary’ flag is set to ‘Y’

Trees are used extensively in ADempiere and iDempiere. They are used to manage and organize business partners, charts of accounts, products, roles, menus, financial entities, projects, campaigns, and departments.

Here are some examples of how you can manipulate records at the database using the tree structure:

Example #1: Move fertilizer org inside the store summary org.

UPDATE AD_TreeNode SET Parent_ID=50007, SeqNo=0, Updated=CURRENT_TIMESTAMP WHERE AD_Tree_ID=104 AND Node_ID=50001;

Example #2: Move all not needed GL Accounts (in the Account Element window) to a folder called ‘DeleteMe’; then, create a SQL statement to delete the not needed accounts.

delete from C_ElementValue 
where C_ElementValue_ID in 
(
 select node_id
 from ad_treenode 
 where parent_id = 1000489 -- record_id of parent folder
 and ad_tree_id = 1000008 -- name of the Account Element tree
) or C_ElementValue_ID=1000489 -- record_id of the parent folder
;