select distinct loc_dataset.location, 'https://elogin.ads.swc/maximo/ui/?event=loadapp'||'&'||'value=location'||'&'||'additionalevent=useqb... MXE_LINK, loc_dataset.location_desc , loc_dataset.updated_level, loc_dataset.system, loc_dataset.loc_sort, loc_dataset.swclevel, case when loc_dataset.updated_level=1 then loc_dataset.location end as level1, case when loc_dataset.updated_level=2 then loc_dataset.location end as level2, case when loc_dataset.updated_level=3 then loc_dataset.location end as level3, case when loc_dataset.updated_level=4 then loc_dataset.location end as level4, case when loc_dataset.updated_level=5 then loc_dataset.location end as level5, case when loc_dataset.updated_level=6 then loc_dataset.location end as level6, case when loc_dataset.updated_level=7 then loc_dataset.location end as level7, case when loc_dataset.updated_level=8 then loc_dataset.location end as level8, case when loc_dataset.updated_level=9 then loc_dataset.location end as level9, loc_dataset.location_type, loc_dataset.loc_status, loc_dataset.SWCPRODUCT, loc_dataset.SWCOPAREA, (select DESCRIPTION from MAXIMO.SWCHIERARCHY WHERE SWCCODE = SWCOPAREA) OPERAREA_DESC, loc_dataset.SWCOPGROUP, loc_dataset.SWCFACILITY, loc_dataset.Process, loc_dataset.PROCESS_DESC, loc_dataset.FACILITY_DESC, loc_dataset.IS_MTU, (select locationspec.numvalue from maximo.locationspec where loc_dataset.location = locationspec.location and loc_dataset.loc_CLASSSTRUCTUREID = locationspec.classstructureid and locationspec.assetattrid= 'COF_SCORE') COF_SCORE, ESTREPLCOST, LOCPRIORITY, loc_CLASSSTRUCTUREID, classstructure.classificationid, classification.description CLASS_DESC from ( select level as updated_level, lh.systemid as system, SYS_CONNECT_BY_PATH (lh.location, ' \ ') as loc_sort, lpad ('.', (level - 1) * 5,'.') || lh.location as loc_hierarchy, lh.location, l.description as location_desc , lo.swclevel, l.TYPE location_type, l.GLACCOUNT, l.STATUS loc_status, l.STATUSDATE, l.SWCOWNER, l.CLASSSTRUCTUREID loc_CLASSSTRUCTUREID, lo.LOCPRIORITY, lo.SWCPRODUCT, lo.SWCMAINTSTGY , lo.SWCOPAREA, lo.SWCOPGROUP, CASE WHEN lo.SWCFACILITY is null then SUBSTR(lh.location,1,6) else lo.SWCFACILITY end as SWCFACILITY, LEVEL83.LEVEL_83 as Process, LEVEL83.PROCESS_DESC, (SELECT DESCRIPTION FROM MAXIMO.LOCATIONS WHERE lo.SWCFACILITY = LOCATIONS.LOCATION )FACILITY_DESC, CASE WHEN (l.TYPE='FUNCLOC' AND instr(l.LOCATION,'-') <> 7) THEN 1 ELSE 0 END AS IS_MTU, lo.SWCINHERITPARENT , (select LASTREADING from MAXIMO.LOCATIONMETER where l.location=LOCATIONMETER.location and METERNAME= 'ESTREPLCOST')ESTREPLCOST, l.locationsid from maximo.lochierarchy lh inner join maximo.locations l on lh.location = l.location and lh.siteid = l.siteid and lh.SYSTEMID = 'PRIMARY' inner join maximo.locoper lo on lh.location = lo.location and lh.siteid = lo.siteid left outer join (Select PRO.*,LOC.DESCRIPTION as PROCESS_DESC from (select MAXIMO.LOCANCESTOR.LOCATION, MIN(MAXIMO.LOCANCESTOR.ANCESTOR) LEVEL_83 from MAXIMO.LOCANCESTOR, MAXIMO.LOCOPER WHERE MAXIMO.LOCANCESTOR.ANCESTOR=MAXIMO.LOCOPER.LOCATION AND MAXIMO.LOCOPER.SWCLEVEL=83 GROUP BY MAXIMO.LOCANCESTOR.LOCATION) PRO, MAXIMO.LOCATIONS LOC WHERE PRO.LEVEL_83=LOC.LOCATION) LEVEL83 on l.location=LEVEL83.location where l.type in ('FACILITY','FUNCLOC','SYSAREA') AND (l.LOCATION LIKE 'ST%' OR l.LOCATION LIKE 'RT%' OR l.LOCATION LIKE 'WT%' OR l.LOCATION LIKE 'WP%' OR l.LOCATION LIKE 'SP%') AND l.STATUS IN ('EXISTING','PENDINGDECOM','NONMAINTAINED','OPERATING') start with (lo.swclevel = 80 and l.TYPE = 'FACILITY') connect by prior lh.location = lh.parent )loc_dataset Left join maximo.classstructure on loc_dataset.loc_CLASSSTRUCTUREID = classstructure.classstructureid left join maximo.classification on classstructure.classificationid = classification.classificationid order by loc_dataset.location
