Locations Hierarchy

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.

Maximo Locations

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

Hierarchical Structure

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 

Links

https://adijaradat.com/post/classification-hierarchy/

https://docs.oracle.com/cd/E18283_01/server.112/e17118/queries003.htm