CST Activity Cause

select
                bd.department_code Dept,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('p_maint_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') Maintained_Item,
                'Asset' Item_Type,
                nvl(ml1.meaning, 'Not Defined') Act_Type,
                nvl(ml2.meaning, 'Not Defined') Act_Cause,
                cii.instance_number Asset_number,
                we.wip_entity_name Work_Order,
                we.description Description,
                round(sum(nvl(wepb.actual_mat_cost,0)) * (:P_EXCHANGE_RATE) / (:P_ROUND_UNIT)) * (:P_ROUND_UNIT) Material,
                round(sum(nvl(wepb.actual_lab_cost,0)) * (:P_EXCHANGE_RATE) / (:P_ROUND_UNIT)) * (:P_ROUND_UNIT) Labor,
                round(sum(nvl(wepb.actual_eqp_cost,0)) * (:P_EXCHANGE_RATE) / (:P_ROUND_UNIT)) * (:P_ROUND_UNIT) Equip,
                round(sum(nvl(wepb.actual_mat_cost,0) + nvl(wepb.actual_lab_cost,0) + nvl(wepb.actual_eqp_cost,0)) * (:P_EXCHANGE_RATE) / (:P_ROUND_UNIT)) * (:P_ROUND_UNIT) Actual_Total,
                cii.serial_number Asset_Serial_number
            from
                mfg_lookups ml1,
                mfg_lookups ml2,
                mtl_system_items msi,
                wip_eam_period_balances wepb,
                bom_departments bd,
                wip_discrete_jobs wdj,
                wip_entities we,
                mtl_parameters mp,
                csi_item_instances cii
            where
            &P_MAINT_ITEM_WHERE
            and mp.maint_organization_id = :P_ORG_ID
            and wdj.organization_id = mp.organization_id
            and wdj.maintenance_object_id = cii.instance_id
            and wdj.maintenance_object_type = 3
            and msi.inventory_item_id = cii.inventory_item_id
            and msi.organization_id = mp.organization_id
            and msi.eam_item_type = 1
            and ml1.lookup_type(+) = 'MTL_EAM_ACTIVITY_TYPE'
            and ml1.lookup_code(+) = wdj.activity_type
            and ml2.lookup_type(+) = 'MTL_EAM_ACTIVITY_CAUSE'
            and ml2.lookup_code(+) = wdj.activity_cause
            and wdj.rebuild_item_id is null
            &P_MAINT_UNIT_AI_WHERE
            and wdj.wip_entity_id = we.wip_entity_id
            and we.entity_type in (6,7)
            and wdj.status_type in (3,4,5,12)
            and bd.department_id = wdj.owning_department
            &P_DEPT_WHERE
            and wdj.wip_entity_id = wepb.wip_entity_id
            and wdj.organization_id = wepb.organization_id
            and :P_ITEM_TYPE in (1,3)
            &P_PERIOD_WHERE
            group by
                bd.department_code,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('p_maint_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),
                ml1.meaning,
                ml2.meaning,
                cii.instance_number,
                cii.serial_number,
                we.description,
                we.wip_entity_name
            union
            select
                bd.department_code Dept,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('p_maint_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') Maintained_Item,
                'Rebuild Item' Item_Type,
                nvl(ml1.meaning, 'Not Defined') Act_Type,
                nvl(ml2.meaning, 'Not Defined') Act_Cause,
                nvl(cii.instance_number, 'Not Applicable') Asset_Number,
                we.wip_entity_name Work_Order,
                we.description Description,
                round(sum(nvl(wepb.actual_mat_cost,0)) * (:P_EXCHANGE_RATE) / (:P_ROUND_UNIT)) * (:P_ROUND_UNIT) Material,
                round(sum(nvl(wepb.actual_lab_cost,0)) * (:P_EXCHANGE_RATE) / (:P_ROUND_UNIT)) * (:P_ROUND_UNIT) Labor,
                round(sum(nvl(wepb.actual_eqp_cost,0)) * (:P_EXCHANGE_RATE) / (:P_ROUND_UNIT)) * (:P_ROUND_UNIT) Equip,
                round(sum(nvl(wepb.actual_mat_cost,0) + nvl(wepb.actual_lab_cost,0) + nvl(wepb.actual_eqp_cost,0)) * (:P_EXCHANGE_RATE) / (:P_ROUND_UNIT)) * (:P_ROUND_UNIT) Actual_Total,
                nvl(cii.serial_number, 'Not Applicable') Asset_Serial_Number
            from
                mfg_lookups ml1,
                mfg_lookups ml2,
                mtl_system_items msi,
                wip_eam_period_balances wepb,
                bom_departments bd,
                wip_discrete_jobs wdj,
                wip_entities we,
                mtl_parameters mp,
                csi_item_instances cii
            where
            &P_MAINT_ITEM_WHERE
            and mp.maint_organization_id = :P_ORG_ID
            and wdj.organization_id = mp.organization_id
            and wdj.rebuild_item_id = cii.inventory_item_id (+)
            and nvl(wdj.rebuild_serial_number,-1)  = cii.serial_number (+)
            and msi.organization_id = mp.organization_id
            and wdj.rebuild_item_id = msi.inventory_item_id
            and msi.eam_item_type = 3
            and ml1.lookup_type(+) = 'MTL_EAM_ACTIVITY_TYPE'
            and ml1.lookup_code(+) = wdj.activity_type
            and ml2.lookup_type(+) = 'MTL_EAM_ACTIVITY_CAUSE'
            and ml2.lookup_code(+) = wdj.activity_cause
            &P_MAINT_UNIT_RI_WHERE
            and wdj.wip_entity_id = we.wip_entity_id
            and we.entity_type in (6,7)
            and wdj.status_type in (3,4,5,12)
            and bd.department_id = wdj.owning_department
            &P_DEPT_WHERE
            and wdj.wip_entity_id = wepb.wip_entity_id
            and wdj.organization_id = wepb.organization_id
            and :P_ITEM_TYPE in (2,3)
            &P_PERIOD_WHERE
            group by
                bd.department_code,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('p_maint_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),
                ml1.meaning,
                ml2.meaning,
                cii.serial_number,
                cii.instance_number,
                we.description,
                we.wip_entity_name
            order by Dept, Maintained_Item, Act_Type, Asset_Number, Work_Order