Get Organisation hierarchies through X++/SQL

In AX 2012

//Project XPO Download link

Organisation Hierarchies

XPO Project Contains,

1) Query - OrganisationHierarchies
2) View - OrganisationHierarchies
3) Job - GetOrgansationalHierarchy

static void  GetOrgansationalHierarchy(Args _args)
{
    //Reference Query : OMHierarchy
    OMHierarchyType OMHierarchyType1;
    OMHierarchyRelationship OMHierarchyRelationship1;
    OMRevisionEdit OMRevisionEdit1; //check this too
    OMInternalOrganization  omInternalOrganizationP,omInternalOrganizationC;
    OMOperatingUnit  omOperatingUntInt;
 
    //Function to Clear
    void clearBuffer()
    {
        OMHierarchyType1.clear();
        omInternalOrganizationP.clear();
        omInternalOrganizationC.clear();
        omOperatingUntInt.clear();
        OMRevisionEdit1.clear();
        OMHierarchyRelationship1.clear();
    }
    ;
    clearBuffer();
    while select Name,RecId
                from OMHierarchyType1
    {
        while select HierarchyType,ParentOrganization,ChildOrganization
                from  OMHierarchyRelationship1
            where OMHierarchyRelationship1.HierarchyType == OMHierarchyType1.RecId
                    && OMHierarchyRelationship1.ParentOrganization != 0
               outer join  omInternalOrganizationP //Parent Organization
                     where omInternalOrganizationP.RecId == OMHierarchyRelationship1.ParentOrganization
                outer join omInternalOrganizationC //Child Organization
                    where omInternalOrganizationC.RecId == OMHierarchyRelationship1.ChildOrganization
                outer join omOperatingUntInt // Operating unit Type
                    where omOperatingUntInt.PartyNumber == omInternalOrganizationC.PartyNumber
                 
        {
            info(strFmt("Org,%1,%2,%3,%4",OMHierarchyType1.Name,
                                   omInternalOrganizationP.Name,
                                   omInternalOrganizationC.Name,
                                   omOperatingUntInt.OMOperatingUnitType
                                   ));
        }
    }
}


In SQL

SQL Script Query

CREATE VIEW vORGANISATIONHIERARCHIES
AS
SELECT
T1.NAME AS HIERARCHYNAME,
T2.CHILDORGANIZATION AS CHILDORGANIZATION,
T2.PARENTORGANIZATION AS PARENTORGANIZATION,
T2.HIERARCHYTYPE AS HIERARCHYTYPE,
T3.NAME AS PARENT,
T4.NAME AS CHILD,
T5.OMOPERATINGUNITTYPE AS ChildOMOPERATINGUNITTYPE,
T5.OMOPERATINGUNITNUMBER AS ChildOMOPERATINGUNITNUMBER,
T6.OMOPERATINGUNITNUMBER AS ParentOMOPERATINGUNITNUMBER,
T6.OMOPERATINGUNITTYPE AS ParentOMOPERATINGUNITTYPE
FROM OMHIERARCHYTYPE T1
CROSS JOIN OMHIERARCHYRELATIONSHIP T2
LEFT OUTER JOIN DIRPARTYTABLE T3
ON (T2.PARENTORGANIZATION=T3.RECID)
LEFT OUTER JOIN DIRPARTYTABLE T4
ON (T2.CHILDORGANIZATION=T4.RECID)
LEFT OUTER JOIN DIRPARTYTABLE T5
ON ((T4.RECID=T5.RECID)
AND (T5.INSTANCERELATIONTYPE IN (2377) ))
LEFT OUTER JOIN DIRPARTYTABLE T6
ON ((T3.RECID=T6.RECID)
AND (T6.INSTANCERELATIONTYPE IN (2377) ))
 WHERE ( NOT ((T2.PARENTORGANIZATION=0))
AND (T1.RECID=T2.HIERARCHYTYPE))