ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 3AA58841D9E6462BB7C471D3EA65640CY select wdj.organization_code, wdj.wip_entity_name work_order, msn.serial_number asset_number, msn.descriptive_text instance_description, msibk.concatenated_segments asset_group, xxen_util.meaning(msibk.eam_item_type,'MTL_EAM_ASSET_TYPE',700) asset_type, mcbk.concatenated_segments item_category, bd.department_code department, bd.description department_description, mel.location_codes eam_location, --cii.serial_number, wdj.description work_order_description, xxen_util.client_time(wdj.scheduled_start_date) scheduled_start_date, xxen_util.client_time(wdj.scheduled_completion_date) scheduled_completion_date, round((wdj.scheduled_completion_date-wdj.scheduled_start_date)*24,3) duration, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) work_order_status, &planner_maintenance --xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) status, xxen_util.user_name(wdj.created_by) created_by, xxen_util.client_time(wdj.creation_date) creation_date, xxen_util.user_name(wdj.last_updated_by) last_updated_by, xxen_util.client_time(wdj.last_update_date) last_update_date, xxen_util.meaning(wdj.shutdown_type,'BOM_EAM_SHUTDOWN_TYPE',700) shutdown_type, xxen_util.meaning(wdj.priority,'WIP_EAM_ACTIVITY_PRIORITY',700) priority, decode(wdj.status_type,3,'Complete',4,'Uncomplete','Disabled') action_code, ppa.segment1 project_number, pt.task_number, wdj.class_code, eps.name pm_schedule_name, xxen_util.meaning(wdj.work_order_type,'WIP_EAM_WORK_ORDER_TYPE',700) work_order_type, xxen_util.meaning(wdj.activity_type,'MTL_EAM_ACTIVITY_TYPE',700) activity_type, xxen_util.meaning(wdj.activity_cause,'MTL_EAM_ACTIVITY_CAUSE',700) activity_cause, xxen_util.meaning(wdj.activity_source,'MTL_EAM_ACTIVITY_SOURCE',700) activity_source, (select msibk.concatenated_segments from mtl_system_items_b_kfv msibk where wdj.primary_item_id=msibk.inventory_item_id and rownum=1) activity_name, null failure_code_required, null failure, null cause, null resolution, to_date(null) failure_date, null comments, null failure_set, nvl(wepb.actual_cost,0) actual_cost, nvl(wepb.estimated_cost,0) estimated_cost, nvl(wepb.variance_cost,0) variance_cost, nvl(wepb.cumulative_actual_cost,0) cumulative_actual_cost, nvl(wepb.cumulative_estimated_cost,0) cumulative_estimated_cost, nvl(wepb.cumulative_variance_cost,0) cumulative_variance_cost, xxen_util.meaning(nvl(wdj.estimation_status,1),'CST_EAM_ESTIMATION_STATUS',700) estimation_status, to_number(null) estimate_number, &columns nvl(sum(wecdv.actual_total_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) total_cost, nvl(sum(wecdv.actual_material_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) material_cost, nvl(sum(wecdv.actual_labor_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) labor_cost, nvl(sum(wecdv.actual_equipment_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) equipment_cost, nvl(sum(wecdv.estimated_total_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) estimated_total_cost, nvl(sum(wecdv.estimated_material_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) estimated_material_cost, nvl(sum(wecdv.estimated_labor_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) estimated_labor_cost, nvl(sum(wecdv.estimated_equipment_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) estimated_equipment_cost, nvl(sum(wecdv.variance_total_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) variance_total_cost, nvl(sum(wecdv.variance_material_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) variance_material_cost, nvl(sum(wecdv.variance_labor_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) variance_labor_cost, nvl(sum(wecdv.variance_equipment_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) variance_equipment_cost, wdj.wip_entity_id --ewod.cycle_id, --ewod.seq_id, from ( select mp.organization_code, we.wip_entity_name, mp.maint_organization_id, wdj.* from mtl_parameters mp, wip_entities we, wip_discrete_jobs wdj where 1=1 and wdj.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and wdj.status_type not in (7,5,12,14,15) and mp.organization_id=we.organization_id and we.entity_type in (6,7) and wdj.maintenance_object_source<>2 and we.wip_entity_id=wdj.wip_entity_id and we.organization_id=wdj.organization_id ) wdj, mtl_system_items_b_kfv msibk, bom_departments bd, pa_projects_all ppa, pa_tasks pt, eam_pm_schedulings eps, mtl_serial_numbers msn, mtl_eam_locations mel, mtl_categories_b_kfv mcbk, ( select wepb.organization_id, wepb.wip_entity_id, nvl(sum(wepb.actual_mat_cost)+sum(wepb.actual_lab_cost)+sum(wepb.actual_eqp_cost),0) actual_cost, nvl(sum(wepb.system_estimated_mat_cost)+sum(wepb.system_estimated_lab_cost)+sum(wepb.system_estimated_eqp_cost),0) estimated_cost, nvl(-sum(wepb.actual_mat_cost)-sum(wepb.actual_lab_cost)-sum(wepb.actual_eqp_cost)+sum(wepb.system_estimated_mat_cost)+sum(wepb.system_estimated_lab_cost)+sum(wepb.system_estimated_eqp_cost),0) variance_cost, nvl(sum(wepb.actual_mat_cost )+sum(wepb.actual_lab_cost)+sum(wepb.actual_eqp_cost),nvl(sum(wepb.actual_mat_cost)+sum(wepb.actual_lab_cost)+sum(wepb.actual_eqp_cost),0)) cumulative_actual_cost, nvl(sum(wepb.system_estimated_mat_cost)+sum(wepb.system_estimated_lab_cost)+sum(wepb.system_estimated_eqp_cost),nvl(sum(wepb.system_estimated_mat_cost)+sum(wepb.system_estimated_lab_cost)+sum(wepb.system_estimated_eqp_cost),0)) cumulative_estimated_cost, nvl(-sum(wepb.actual_mat_cost)-sum(wepb.actual_lab_cost)-sum(wepb.actual_eqp_cost)+sum(wepb.system_estimated_mat_cost)+sum(wepb.system_estimated_lab_cost)+sum(wepb.system_estimated_eqp_cost),nvl(-sum(wepb.actual_mat_cost)-sum(wepb.actual_lab_cost)-sum(wepb.actual_eqp_cost)+sum(wepb.system_estimated_mat_cost)+sum(wepb.system_estimated_lab_cost)+sum(wepb.system_estimated_eqp_cost),0)) cumulative_variance_cost from wip_eam_period_balances wepb where (wepb.period_name,wepb.period_set_name) in ( select period_name, to_char(null) period_set_name from org_acct_periods oap where 2=2 union all select gp.period_name, gp.period_set_name from gl_periods gp where 3=3 and gp.adjustment_period_flag='N' ) group by wepb.organization_id, wepb.wip_entity_id ) wepb, ( select distinct wecdv.organization_id, wecdv.wip_entity_id, &columns nvl(sum(wecdv.actual_total_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) actual_total_cost, nvl(sum(wecdv.actual_material_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) actual_material_cost, nvl(sum(wecdv.actual_labor_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) actual_labor_cost, nvl(sum(wecdv.actual_equipment_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) actual_equipment_cost, nvl(sum(wecdv.estimated_total_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) estimated_total_cost, nvl(sum(wecdv.estimated_material_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) estimated_material_cost, nvl(sum(wecdv.estimated_labor_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) estimated_labor_cost, nvl(sum(wecdv.estimated_equipment_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) estimated_equipment_cost, nvl(sum(wecdv.variance_total_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) variance_total_cost, nvl(sum(wecdv.variance_material_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) variance_material_cost, nvl(sum(wecdv.variance_labor_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) variance_labor_cost, nvl(sum(wecdv.variance_equipment_cost) over (partition by wecdv.organization_id, wecdv.wip_entity_id &partition_by),0) variance_equipment_cost from wip_eam_cost_details_v wecdv where (wecdv.period_set_name, wecdv.period_name) in ( select to_char(null) period_set_name, period_name from org_acct_periods oap where 2=2 union all select gp.period_set_name, gp.period_name from gl_periods gp where 3=3 and gp.adjustment_period_flag='N' ) ) wecdv where 4=4 and nvl(wdj.asset_group_id,wdj.rebuild_item_id)=msibk.inventory_item_id(+) and wdj.organization_id=msibk.organization_id(+) and wdj.owning_department=bd.department_id(+) and wdj.project_id=ppa.project_id(+) and wdj.task_id=pt.task_id(+) and wdj.pm_schedule_id=eps.pm_schedule_id(+) and decode(wdj.maintenance_object_type,1,wdj.maintenance_object_id)=msn.gen_object_id(+) and msn.eam_location_id=mel.location_id(+) and msn.category_id=mcbk.category_id(+) and wdj.organization_id=wepb.organization_id(+) and wdj.wip_entity_id=wepb.wip_entity_id(+) and wdj.organization_id=wecdv.organization_id(+) and wdj.wip_entity_id=wecdv.wip_entity_id(+) order by wdj.organization_code, wdj.scheduled_start_date desc, wdj.wip_entity_name Fixed the report returning no rows (null outer join keys to mtl_eam_locations and mtl_categories_b_kfv). Asset Number, Instance Description, Item Category and Eam Location, and the Asset Number and Asset Group parameters, now read the 11i asset from mtl_serial_numbers. #,##0.00;[Red]-#,##0.00 US EAM Work Orders 11i Enterprise asset management work orders including asset and cost information Enginatics &columns &partition_by &planner_maintenance 1=1 2=2 3=3 4=4 1 -10 &planner_maintenance select 'xxen_util.meaning(ewod.planner_maintenance,''EAM_PLANNER'',700) planner,' text from all_tab_columns atc where atc.column_name='PLANNER_MAINTENANCE' and atc.table_name='EAM_WORK_ORDER_DETAILS' and atc.owner='EAM' Char 1 US Planner Maintenance Column 2 10 1=1 mp.organization_code=:organization_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.eam_enabled_flag='Y' and mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) US Organization Code 3 20 4=4 bd.department_code=:department_code LOV BOM Department 8E2FF36EDEDC79D2E0530100007F1FF2 Y select bd.department_code value, nvl2(bd.department_class_code,bd.department_class_code||': ',null)||bd.description description from bom_departments bd where :$flex$.organization_code is null and bd.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or bd.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') order by bd.department_code US Department 4 30 1=1 wdj.maintenance_object_type=1 and wdj.maintenance_object_id in ( select msn0.gen_object_id from mtl_system_items_b_kfv msibk0, mtl_serial_numbers msn0 where msibk0.concatenated_segments=:asset_group and msibk0.organization_id=msn0.current_organization_id and msibk0.inventory_item_id=msn0.inventory_item_id ) LOV custom Y select msiv.concatenated_segments value, mp.organization_code||': '||msiv.description description from mtl_parameters mp, mtl_system_items_vl msiv where ( :$flex$.organization_code is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and mp.organization_id=msiv.organization_id and msiv.eam_item_type in (1,3) and msiv.serial_number_control_code<>1 and (msiv.organization_id, msiv.inventory_item_id) in (select meanv.current_organization_id, meanv.inventory_item_id from mtl_eam_asset_numbers_all_v meanv) order by msiv.concatenated_segments, mp.organization_code US Asset Group 5 40 4=4 wdj.maintenance_object_type=1 and wdj.maintenance_object_id in (select msn0.gen_object_id from mtl_serial_numbers msn0 where msn0.serial_number like :asset_number) LOV custom Y Y select meanv.serial_number value, mp.organization_code||': '||meanv.concatenated_segments description from mtl_parameters mp, mtl_eam_asset_numbers_all_v meanv where ( :$flex$.organization_code is null and meanv.current_organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and mp.organization_id=meanv.current_organization_id and meanv.eam_item_type in (1,3) order by meanv.serial_number US Asset Number 6 50 1=1 wdj.scheduled_start_date>=:sched_start_date_from Date US Scheduled Start Date From 7 60 1=1 wdj.scheduled_start_date<:sched_start_date_to+1 Date US Scheduled Start Date To 8 70 1=1 wdj.creation_date>=:creation_date_from Date US Work Order Creation Date From 9 80 1=1 wdj.creation_date<:creation_date_to+1 Date US Work Order Creation Date To 10 90 4=4 wdj.status_type=to_number(xxen_util.lookup_code(:work_order_status,'WIP_JOB_STATUS',700)) LOV custom Y select ml.meaning value, null description from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' and ml.lookup_code in (1,3,4,6,17) order by ml.lookup_code US Work Order Status 11 100 4=4 ewod.planner_maintenance=xxen_util.lookup_code(:planner,'EAM_PLANNER',700) LOV custom Y select ml.meaning value, null description from mfg_lookups ml where ml.lookup_type='EAM_PLANNER' order by ml.meaning US Planner 12 110 1=1 wdj.created_by=xxen_util.user_id(:created_by) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US Work Order Created By 13 120 2=2 oap.period_start_date>=:period_date_from Date US Cost Period from Date 14 3=3 gp.start_date>=:period_date_from US Cost Period from Date 15 130 2=2 oap.schedule_close_date<=:period_date_to Date US Cost Period to Date 16 3=3 gp.end_date<=:period_date_to US Cost Period to Date 17 140 &partition_by , wecdv.period_set_name, wecdv.period_name, wecdv.cost_category_meaning LOV custom Y select 'Period' value, '' description from dual union all select 'Cost Category' value, '' description from dual union all select 'Period and Cost Category' value, '' description from dual Period and Cost Category Cost Category US Cost Detail Level 18 &partition_by , wecdv.cost_category_meaning Cost Category US Cost Detail Level 19 &partition_by , wecdv.period_set_name, wecdv.period_name Period US Cost Detail Level 20 &columns wecdv.cost_category_meaning, Cost Category US Cost Detail Level 21 &columns wecdv.period_set_name, wecdv.period_name, Period US Cost Detail Level 22 &columns wecdv.period_set_name, wecdv.period_name, wecdv.cost_category_meaning, Period and Cost Category US Cost Detail Level 68553EDE615F47B28208B29F41387AA6 EAM Work Order Cost by Category Estimated, actual and variance cost of each work order by maintenance cost category, split into material, labor and equipment, with a pivot of the totals by cost category. ENGINATICS 4 ASSET_GROUP 3 ASSET_NUMBER Y 8 COST_CATEGORY_MEANING 3 16 EQUIPMENT_COST 15 ESTIMATED_EQUIPMENT_COST 12 ESTIMATED_LABOR_COST 9 ESTIMATED_MATERIAL_COST 18 ESTIMATED_TOTAL_COST 13 LABOR_COST 10 MATERIAL_COST 1 ORGANIZATION_CODE 1 7 SCHEDULED_START_DATE 19 TOTAL_COST 17 VARIANCE_EQUIPMENT_COST 14 VARIANCE_LABOR_COST 11 VARIANCE_MATERIAL_COST 20 VARIANCE_TOTAL_COST 2 WORK_ORDER 2 5 WORK_ORDER_DESCRIPTION 6 WORK_ORDER_STATUS COST_CATEGORY_MEANING ROW 1 ESTIMATED_TOTAL_COST VALUE 2 SUM TOTAL_COST VALUE 3 SUM VARIANCE_TOTAL_COST VALUE 4 SUM Cost Detail Level Cost Category S Site