If you have access to query Visual, you might want to see your account tree for XWALKING to your GL in excel.
This sql will get you such a spreadsheet.
I did not drop accounts based on EFFECTIVE DATE.
Our tree goes 9 levels deep - yours may go deeper, in which case you need to edit the case statements and add a join or two.
select distinct
case when b.id is null then a.id else (case when c.id is null then b.id else (case when d.id is null then c.id else (case when e.id is null then d.id else (case when f.id is null then e.id else (case when g.id is null then f.id else (case when h.id is null then g.id else (case when i.id is null then h.id else i.id end) end) end) end) end) end) end) end as "ACCOUNT",
case when b.id is null then '1' else (case when c.id is null then '2' else (case when d.id is null then '3' else (case when e.id is null then '4' else (case when f.id is null then '5' else (case when g.id is null then '6' else (case when h.id is null then '7' else (case when i.id is null then '8' else '9' end) end) end) end) end) end) end) end as "LEVEL",
a.type as "acct_lvl_1_type",
a.id as "acct_lvl_1",
a.description as "acct_lvl_1_descr",
b.type as "acct_lvl_2_type",
b.id as "acct_lvl_2",
b.description as "acct_lvl_2_descr",
c.type as "acct_lvl_3_type",
c.id as "acct_lvl_3",
c.description as "acct_lvl_3_descr",
d.type as "acct_lvl_4_type",
d.id as "acct_lvl_4",
d.description as "acct_lvl_4_descr",
e.type as "acct_lvl_5_type",
e.id as "acct_lvl_5",
e.description as "acct_lvl_5_descr",
f.type as "acct_lvl_6_type",
f.id as "acct_lvl_6",
f.description as "acct_lvl_6_descr",
g.type as "acct_lvl_7_type",
g.id as "acct_lvl_7",
g.description as "acct_lvl_7_descr",
h.type as "acct_lvl_8_type",
h.id as "acct_lvl_8",
h.description as "acct_lvl_8_descr",
i.type as "acct_lvl_9_type",
i.id as "acct_lvl_9",
i.description as "acct_lvl_9_descr"
from account a
left outer join account b
on b.parent_acct_id = a.id
left outer join account c
on c.parent_acct_id = b.id
left outer join account d
on d.parent_acct_id = c.id
left outer join account e
on e.parent_acct_id = d.id
left outer join account f
on f.parent_acct_id = e.id
left outer join account g
on g.parent_acct_id = f.id
left outer join account h
on h.parent_acct_id = g.id
left outer join account i
on i.parent_acct_id = h.id
where a.parent_acct_id is null
;
