VIEW: APPS.WIP_EAM_COST_DETAILS_V

Source

View Text - Preformatted

SELECT t1.maint_cost_category, m1.meaning, t1.wip_entity_id, t1.organization_id, t1.period_start_date, t1.period_set_name, t1.period_name, t1.Actual_Total_Cost, t1.Actual_Material_Cost, t1.Actual_Labor_Cost, t1.Actual_Equipment_Cost, t1.Estimated_Total_Cost, t1.Estimated_Material_Cost, t1.Estimated_Labor_Cost, t1.Estimated_Equipment_Cost, t1.Variance_Total_Cost, t1.Variance_Material_Cost, t1.Variance_Labor_Cost, t1.Variance_Equipment_Cost from (select e.maint_cost_category, e.wip_entity_id,e.organization_id, e.period_start_date, e.period_set_name, e.period_name, nvl((SUM(e.actual_mat_cost) + SUM(e.actual_lab_cost) + SUM(e.actual_eqp_cost)),0) as Actual_Total_Cost, nvl(SUM(e.actual_mat_cost),0) as Actual_Material_Cost, nvl(SUM(e.actual_lab_cost),0) as Actual_Labor_Cost, nvl(SUM(e.actual_eqp_cost),0) as Actual_Equipment_Cost, nvl((SUM(e.system_estimated_mat_cost) + SUM(e.system_estimated_lab_cost) + SUM(e.system_estimated_eqp_cost)),0) as Estimated_Total_Cost, nvl(SUM(e.system_estimated_mat_cost),0) as Estimated_Material_Cost, nvl(SUM(e.system_estimated_lab_cost),0) as Estimated_Labor_Cost, nvl(SUM(e.system_estimated_eqp_cost),0) as Estimated_Equipment_Cost, nvl(( (- SUM(e.actual_mat_cost) - SUM(e.actual_lab_cost) - SUM(e.actual_eqp_cost)) + (SUM(e.system_estimated_mat_cost) + SUM(e.system_estimated_lab_cost) + SUM(e.system_estimated_eqp_cost))),0) as Variance_Total_Cost, nvl(( - SUM(e.actual_mat_cost) + SUM(e.system_estimated_mat_cost)),0) as Variance_Material_Cost, nvl(( - SUM(e.actual_lab_cost) + SUM(e.system_estimated_lab_cost)),0) as Variance_Labor_Cost, nvl(( - SUM(e.actual_eqp_cost) + SUM(e.system_estimated_eqp_cost)),0) as Variance_Equipment_Cost from wip_eam_period_balances e group by e.maint_cost_category, e.wip_entity_id, e.organization_id, e.period_name, e.period_set_name, e.period_start_date) 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 T1.MAINT_COST_CATEGORY
, M1.MEANING
, T1.WIP_ENTITY_ID
, T1.ORGANIZATION_ID
, T1.PERIOD_START_DATE
, T1.PERIOD_SET_NAME
, T1.PERIOD_NAME
, T1.ACTUAL_TOTAL_COST
, T1.ACTUAL_MATERIAL_COST
, T1.ACTUAL_LABOR_COST
, T1.ACTUAL_EQUIPMENT_COST
, T1.ESTIMATED_TOTAL_COST
, T1.ESTIMATED_MATERIAL_COST
, T1.ESTIMATED_LABOR_COST
, T1.ESTIMATED_EQUIPMENT_COST
, T1.VARIANCE_TOTAL_COST
, T1.VARIANCE_MATERIAL_COST
, T1.VARIANCE_LABOR_COST
, T1.VARIANCE_EQUIPMENT_COST
FROM (SELECT E.MAINT_COST_CATEGORY
, E.WIP_ENTITY_ID
, E.ORGANIZATION_ID
, E.PERIOD_START_DATE
, E.PERIOD_SET_NAME
, E.PERIOD_NAME
, NVL((SUM(E.ACTUAL_MAT_COST) + SUM(E.ACTUAL_LAB_COST) + SUM(E.ACTUAL_EQP_COST))
, 0) AS ACTUAL_TOTAL_COST
, NVL(SUM(E.ACTUAL_MAT_COST)
, 0) AS ACTUAL_MATERIAL_COST
, NVL(SUM(E.ACTUAL_LAB_COST)
, 0) AS ACTUAL_LABOR_COST
, NVL(SUM(E.ACTUAL_EQP_COST)
, 0) AS ACTUAL_EQUIPMENT_COST
, NVL((SUM(E.SYSTEM_ESTIMATED_MAT_COST) + SUM(E.SYSTEM_ESTIMATED_LAB_COST) + SUM(E.SYSTEM_ESTIMATED_EQP_COST))
, 0) AS ESTIMATED_TOTAL_COST
, NVL(SUM(E.SYSTEM_ESTIMATED_MAT_COST)
, 0) AS ESTIMATED_MATERIAL_COST
, NVL(SUM(E.SYSTEM_ESTIMATED_LAB_COST)
, 0) AS ESTIMATED_LABOR_COST
, NVL(SUM(E.SYSTEM_ESTIMATED_EQP_COST)
, 0) AS ESTIMATED_EQUIPMENT_COST
, NVL(( (- SUM(E.ACTUAL_MAT_COST) - SUM(E.ACTUAL_LAB_COST) - SUM(E.ACTUAL_EQP_COST)) + (SUM(E.SYSTEM_ESTIMATED_MAT_COST) + SUM(E.SYSTEM_ESTIMATED_LAB_COST) + SUM(E.SYSTEM_ESTIMATED_EQP_COST)))
, 0) AS VARIANCE_TOTAL_COST
, NVL(( - SUM(E.ACTUAL_MAT_COST) + SUM(E.SYSTEM_ESTIMATED_MAT_COST))
, 0) AS VARIANCE_MATERIAL_COST
, NVL(( - SUM(E.ACTUAL_LAB_COST) + SUM(E.SYSTEM_ESTIMATED_LAB_COST))
, 0) AS VARIANCE_LABOR_COST
, NVL(( - SUM(E.ACTUAL_EQP_COST) + SUM(E.SYSTEM_ESTIMATED_EQP_COST))
, 0) AS VARIANCE_EQUIPMENT_COST
FROM WIP_EAM_PERIOD_BALANCES E GROUP BY E.MAINT_COST_CATEGORY
, E.WIP_ENTITY_ID
, E.ORGANIZATION_ID
, E.PERIOD_NAME
, E.PERIOD_SET_NAME
, E.PERIOD_START_DATE) T1
, MFG_LOOKUPS M1
WHERE M1.LOOKUP_TYPE = 'BOM_EAM_COST_CATEGORY'
AND T1.MAINT_COST_CATEGORY (+) = M1.LOOKUP_CODE