Updated: Feb 25
Check out Locations Hierarchy Generalized (Part 2)
Maximo Locations Hierarchy are organized under Sites allowing a high level segregation of locations and authorization. This is especially important note to keep in mind which working with Maximo Locations data as it indicates that Locations are Site-level object.
One of the assumptions we can make, knowing that Locations is a site-level object, is that the Location name may exist under a different site. So the location name is not unique, however, the location name and site combination is unique.
Maximo Locations Hierarchy
Locations are mostly useful when organized in hierarchical structure. When organizing data in hierarchical format we expect additional details to describe the hierarchy. The following list are data that can be obtained when constructing locations hierarchy query and they primarily gives a great details about the hierarchy levels:
LEVEL: The tree branch level, this should start from “1” and increment as far as the trees depth. This is especially useful if you are to implement a logic that depends on the hierarchy level.
PATH: Just like the site Bread Crumbs component, this piece of information is able to describe all the tree nodes starting from the Root (the very first node) all the down to the current node. The number of node comprising the PATH should be equal to the LEVEL value.
CONNECT_BY_ROOT: The root node which the current note belongs to. You can safely assume that you could possible have multiple ROOTs because it depends on your START WITH clause.
Maximo maintains the Location hierarchy information in a DB-agnostic approach using, mainly, LOCATIONS, LOCHIERARCHY, and LOCANCESTOR tables.
Constructing the Hierarchy
To reconstruct the Location hierarchy in Oracle DB, you can utilize Hierarchical Queries:
WITH LOCATIONS_SET AS ( SELECT LOCHIERARCHY.SITEID ,LOCHIERARCHY.SYSTEMID ,LOCHIERARCHY.LOCATION ,LOCATIONS.DESCRIPTION ,LOCATIONS.GLACCOUNT ,LOCHIERARCHY.PARENT ,LOCATIONS.STATUS ,CLASSSTRUCTURE.CLASSIFICATIONID ,CLASSSTRUCTURE.DESCRIPTION AS CLASSIFICATIONDESC FROM LOCHIERARCHY ,LOCATIONS ,CLASSSTRUCTURE WHERE LOCHIERARCHY.LOCATION=LOCATIONS.LOCATION AND LOCHIERARCHY.SITEID=LOCATIONS.SITEID AND LOCATIONS.CLASSSTRUCTUREID=CLASSSTRUCTURE.CLASSSTRUCTUREID(+) AND LOCHIERARCHY.SITEID='MYSITE' AND LOCHIERARCHY.SYSTEMID='PRIMARY' ) SELECT LS.* ,LEVEL AS HIERARCHY_LEVEL ,SUBSTR(SYS_CONNECT_BY_PATH(LS.LOCATION, '->'), 3) AS HIERARCHY_PATH FROM LOCATIONS_SET LS START WITH LS.PARENT IS NULL CONNECT BY NOCYCLE PRIOR LS.LOCATION=LS.PARENT