Joining Organization and CFGOrgCodes dynamically

If you have ever worked with the organization and cfgorgcodes table, you've probably had not-so-straight forward experiences in joining these two tables together. If you firm is running multi-company, then you have definitely experienced the fun in marrying to the two tables together!

In Short, the organization table lists out the full name of the given lowest level in your organization along with the full org code, project number mappings, ranges and posting rules.

The CFGOrgcodes table provides the hierarchy and the name of each level by each individual code.

For example, your organization table might have something like this in the name and org fields

Name=Enterprise:Midwest:SpringField:Engineering Dept.
Org=1:MW:SPF:ENG

Your CFGOrgCode table might have something like this based on the entry from the above example.

OrgLevel, Code, Label
1, 1, Enterprise
2, MW, Midwest
3, SPF, SpringField
4, ENG, Engineering Dept.

I put the query below together to dynamically parse out the each Org Level based on the Org Code.

SELECT o.name, o.org, ISNULL(c1.label,'') as OrgLevel1,
ISNULL(OrgLabels.org1Label,'') as OrgLevel1Label,
ISNULL(c1.label,'') as OrgLevel2,
ISNULL(OrgLabels.org2Label,'') as OrgLevel2Label,
ISNULL(c1.label,'') as OrgLevel3,
ISNULL(OrgLabels.org3Label,'') as OrgLevel3Label,
ISNULL(c1.label,'') as OrgLevel4,
ISNULL(OrgLabels.org4Label,'') as OrgLevel4Label,
ISNULL(c1.label,'') as OrgLevel5,
ISNULL(OrgLabels.org5Label,'') as OrgLevel5Label

from Organization o
inner join (Select OrgLevels, OrgDelimiter,
Org1Start, Org1Length, Org2Start, Org2Length,
Org3Start, Org3Length, Org4Start, Org4Length,
Org5Start, Org5Length from CFGFormat) as OrgFormat on 1=1

inner join
(Select orgLabel, org1Label, org2Label, org3Label, org4Label, org5Label
from CFGLabels) as OrgLabels on 1=1

left join cfgOrgCodes c1 on SUBSTRING(o.org,OrgFormat.Org1Start,OrgFormat.Org1Length) = c1.code and c1.orglevel = 1
left join cfgOrgCodes c2 on SUBSTRING(o.org,OrgFormat.Org2Start,OrgFormat.Org2Length) = c2.code and c2.orglevel = 2
left join cfgOrgCodes c3 on SUBSTRING(o.org,OrgFormat.Org3Start,OrgFormat.Org3Length) = c3.code and c3.orglevel = 3
left join cfgOrgCodes c4 on SUBSTRING(o.org,OrgFormat.Org4Start,OrgFormat.Org4Length) = c4.code and c4.orglevel = 4
left join cfgOrgCodes c5 on SUBSTRING(o.org,OrgFormat.Org5Start,OrgFormat.Org5Length) = c5.code and c5.orglevel = 5

You'll notice that I've joined on a few tables that I haven't yet mentioned.

CFGFormat
The CFGFormat table allows us to understand the start position and the length of each level in the Org Code so that we can dynamically parse it out. If you change the lengths or rules of your particular levels in your org structure, you will not need to change the above code.

CFGLabels
This CFGLabels table allows us to appropriate define what the generic term is for each level in your Organization. These levels show up on the reports.

If the above query does not work for your environment, please feel free to drop me an email...
Enjoy...