Locations Hierarchy Generalized

Locations Hierarchy ISO14224

Maximo Locations Hierarchy

We managed to build a complete Location Hierarchy for a specific site in Locations Hierarchy Part 1. In this post we will generalize the script even further.

Generalizing the Scope of the Hierarchy

Our target will be to generalize the script so that it can be run against:

1. Single Site

2. Multiple Sites

3. Single Organization

4. This will include the full list of Sites

5. Multiple Organizations

6. This will include the full list of Organizations and related Sites

7. No restrictions at all

Performance Considerations

This is no-light query, you must ensure that your database is able to execute this query and get back with some results.

… and the code?

Have fun …

WITH LOCATIONS_SET AS 
(
 SELECT LOCHIERARCHY.SITEID
        ,LOCHIERARCHY.ORGID
        ,LOCHIERARCHY.SYSTEMID
        ,LOCHIERARCHY.LOCATION
        ,LOCATIONS.DESCRIPTION
        ,LOCATIONS.TYPE 
        ,LOCATIONS.GLACCOUNT 
        ,LOCHIERARCHY.PARENT
        ,LOCATIONS.STATUS
        ,CLASSSTRUCTURE.CLASSIFICATIONID
        ,CLASSSTRUCTURE.DESCRIPTION AS CLASSIFICATIONDESC 
    FROM LOCHIERARCHY
        ,LOCATIONS
        ,LOCOPER
        ,CLASSSTRUCTURE
   WHERE LOCHIERARCHY.LOCATION=LOCATIONS.LOCATION
     AND LOCHIERARCHY.SITEID=LOCATIONS.SITEID
     AND LOCHIERARCHY.LOCATION=LOCOPER.LOCATION
     AND LOCHIERARCHY.SITEID=LOCOPER.SITEID
     AND LOCATIONS.CLASSSTRUCTUREID=CLASSSTRUCTURE.CLASSSTRUCTUREID(+)
     AND LOCHIERARCHY.SYSTEMID='PRIMARY'
     -- OPTIONAL --
     -- AND LOCHIERARCHY.SITEID IN ('SITE1','SITE2')
     -- AND LOCHIERARCHY.ORGID IN ('ORG1','ORG2')
)
  SELECT LS.*
         ,LEVEL AS HIERARCHY_LEVEL
         ,SUBSTR(SYS_CONNECT_BY_PATH(LS.LOCATION, ' -> '), 4) AS HIERARCHY_PATH
    FROM LOCATIONS_SET LS
   START WITH LS.PARENT IS NULL
 CONNECT BY NOCYCLE PRIOR LS.LOCATION=LS.PARENT
     AND PRIOR LS.SITEID=LS.SITEID
     AND PRIOR LS.ORGID=LS.ORGID 

Links

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

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