VIEW: APPS.CST_EAM_ACTIVITY_ESTIMATES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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