SELECT M1.LOOKUP_CODE AS MAINT_COST_CATEGORY , T1.ACTIVITY_ESTIMATE_RECORD_ID , M1.MEANING , NVL(EQUIPMENT , 0) EQUIPMENT , NVL(LABOR , 0) LABOR , NVL(MATERIAL , 0) MATERIAL , NVL(T1.TOTAL , 0) TOTAL FROM ( SELECT MAINT_COST_CATEGORY , ACTIVITY_ESTIMATE_RECORD_ID , SUM(EQUIPMENT) AS EQUIPMENT , SUM(LABOR) AS LABOR , SUM(MATERIAL) AS MATERIAL , NVL(SUM(EQUIPMENT) + SUM(LABOR) + SUM(MATERIAL) , 0) AS TOTAL FROM (SELECT MAINT_COST_CATEGORY , ACTIVITY_ESTIMATE_RECORD_ID , COST_VALUE AS EQUIPMENT , 0 AS LABOR , 0 AS MATERIAL FROM CST_EAM_ACTIVITY_ESTIMATES WHERE RECORD_TYPE = 'S' AND EAM_COST_ELEMENT = 1 UNION SELECT MAINT_COST_CATEGORY , ACTIVITY_ESTIMATE_RECORD_ID , 0 , COST_VALUE , 0 FROM CST_EAM_ACTIVITY_ESTIMATES WHERE RECORD_TYPE = 'S' AND EAM_COST_ELEMENT = 2 UNION SELECT MAINT_COST_CATEGORY , ACTIVITY_ESTIMATE_RECORD_ID , 0 , 0 , COST_VALUE FROM CST_EAM_ACTIVITY_ESTIMATES WHERE RECORD_TYPE = 'S' AND EAM_COST_ELEMENT = 3 ) GROUP BY MAINT_COST_CATEGORY , ACTIVITY_ESTIMATE_RECORD_ID ) T1 , MFG_LOOKUPS M1 WHERE M1.LOOKUP_TYPE = 'BOM_EAM_COST_CATEGORY' AND T1.MAINT_COST_CATEGORY (+) = M1.LOOKUP_CODE