SQL against staging table to validate org hierarchy
/*Un-comment where clause to narrow list down to orgs set at level 7*/
with cte as
(
select
fin_coa_cd,
org_cd,
RPTS_TO_FIN_COA_CD,
RPTS_TO_ORG_CD,
1 as count,
cast ( RPTS_TO_ORG_CD as varchar(100)) as [path]
from ca_org_t
where org_cd = 'UNIV'
union all
select
t.fin_coa_cd,
t.org_cd,
t.RPTS_TO_FIN_COA_CD,
t.rpts_to_org_cd,
cte.count + 1,
cast ([path] as varchar(100))
from
cte
join ca_org_t t on cte.org_cd = t.RPTS_TO_ORG_CD and cte.fin_coa_cd = t.RPTS_TO_FIN_COA_CD
where t.org_cd <> 'UNIV' and t.RPTS_TO_ORG_CD = 'UNIV'
union all
select
t.fin_coa_cd,
t.org_cd,
t.RPTS_TO_FIN_COA_CD,
t.rpts_to_org_cd,
cte.count + 1,
cast ([path] + char(9) + t.rpts_to_org_cd as varchar(100))
from
cte
join ca_org_t t on cte.org_cd = t.RPTS_TO_ORG_CD and cte.fin_coa_cd = t.RPTS_TO_FIN_COA_CD
where t.org_cd <> 'UNIV' and t.RPTS_TO_ORG_CD <> 'UNIV'
)
select
FIN_COA_CD,
ORG_CD,
RPTS_TO_FIN_COA_CD,
RPTS_TO_ORG_CD,
count,
[path] + char(9) + ORG_CD
from cte
/*use to only show orgs at level 7
where count = 7
*/
order by path;