Product: | WIP - Work in Process |
---|---|
Description: | Detailed cost information for EAM work orders |
Implementation/DBA Data: | APPS.WIP_EAM_COST_DETAILS_V |
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