CST Activity Cause

Description
Categories: BI Publisher
Application: Bills of Material
Source: Activity Cause Report (XML)
Short Name: CSTRACTC_XML
DB package: BOM_CSTRACTC_XMLP_PKG
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
Parameter Name SQL text Validation
Report Type
 
LOV Oracle
Period From
 
LOV Oracle
Period To
 
LOV Oracle
Currency
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Department From
 
LOV Oracle
Department To
 
LOV Oracle
Item Type
 
LOV Oracle
Asset Group From
 
Asset Group To
 
Asset Number From
 
LOV Oracle
Asset Number To
 
LOV Oracle
Organization ID
 
Number
CST_SRS_INVERSE_RATE
 
CST_SRS_RATE_TYPE
 
Number
CST_SRS_DEPT_DUMMY
 
Number