ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 6C1D3E5F7A9B4C2D8E0F1A3B5C7D9E1FY select mp.organization_code, wewr.work_request_number, wewr.description, xxen_util.meaning(wewr.work_request_status_id,'WIP_EAM_WORK_REQ_STATUS',700) status, xxen_util.meaning(wewr.work_request_priority_id,'WIP_EAM_ACTIVITY_PRIORITY',700) priority, xxen_util.meaning(wewr.work_request_type_id,'WIP_EAM_WORK_REQ_TYPE',700) request_type, wewr.asset_number, msiv.concatenated_segments asset_group, msiv.description asset_group_description, bd.department_code owning_department, wewr.creation_date, wewr.expected_resolution_date, case when wewr.work_request_status_id not in (5,6) then round(sysdate-wewr.creation_date,1) end days_open, xxen_util.yes(case when wewr.work_request_status_id not in (5,6) and wewr.expected_resolution_date<sysdate then 'Y' end) overdue, xxen_util.user_name(wewr.work_request_created_by) requested_by, xxen_util.user_name(wewr.created_for) created_for, xxen_util.meaning(wewr.contact_preference,'WIP_EAM_CONTACT_PREFERENCE',700) contact_preference, wewr.phone_number, wewr.e_mail email, we.wip_entity_name work_order, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) work_order_status, wdj.scheduled_start_date, wdj.scheduled_completion_date, wdj.date_completed, wdj.date_closed, xxen_util.user_name(wewr.created_by) created_by, wewr.last_update_date, xxen_util.user_name(wewr.last_updated_by) last_updated_by from wip_eam_work_requests wewr, mtl_parameters mp, mtl_system_items_vl msiv, bom_departments bd, wip_entities we, wip_discrete_jobs wdj where 1=1 and wewr.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 wewr.organization_id=mp.organization_id and wewr.asset_group=msiv.inventory_item_id(+) and wewr.organization_id=msiv.organization_id(+) and wewr.work_request_owning_dept=bd.department_id(+) and wewr.wip_entity_id=we.wip_entity_id(+) and wewr.wip_entity_id=wdj.wip_entity_id(+) order by mp.organization_code, wewr.creation_date desc, wewr.work_request_number New report. US EAM Work Requests Maintenance work requests with their asset, priority, requester and the work order created for them, one row per work request. Days Open and Overdue apply to requests that are neither rejected nor complete. Overdue means the expected resolution date has passed. Work order status is the system status of the linked work order. Enginatics 1=1 1 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 2 20 1=1 wewr.creation_date>=:creation_date_from Date US Creation Date From 3 30 1=1 wewr.creation_date<:creation_date_to+1 Date :$flex$.creation_date_from US Creation Date To 4 40 1=1 wewr.work_request_status_id=to_number(xxen_util.lookup_code(:status,'WIP_EAM_WORK_REQ_STATUS',700)) LOV custom Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='WIP_EAM_WORK_REQ_STATUS' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Status 5 50 1=1 wewr.work_request_status_id not in (5,6) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Open Only Excludes rejected and completed work requests 6 60 1=1 wewr.wip_entity_id is null LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Without Work Order 7 70 1=1 wewr.work_request_priority_id=to_number(xxen_util.lookup_code(:priority,'WIP_EAM_ACTIVITY_PRIORITY',700)) LOV custom Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='WIP_EAM_ACTIVITY_PRIORITY' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Priority 8 80 1=1 wewr.work_request_type_id=to_number(xxen_util.lookup_code(:request_type,'WIP_EAM_WORK_REQ_TYPE',700)) LOV custom Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='WIP_EAM_WORK_REQ_TYPE' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Request Type 9 90 1=1 bd.department_code=:owning_department 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 Owning Department 10 100 1=1 msiv.concatenated_segments=:asset_group LOV EAM Asset Group 8E2FF36EDEEC79D2E0530100007F1FF2 Y 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 cii.last_vld_organization_id, cii.inventory_item_id from csi_item_instances cii where sysdate between nvl(cii.active_start_date,sysdate) and nvl(cii.active_end_date,sysdate)) order by msiv.concatenated_segments, mp.organization_code US Asset Group 11 110 1=1 wewr.asset_number=:asset_number LOV EAM Asset Number 8E2FF36EDEEB79D2E0530100007F1FF2 Y Y select cii.instance_number value, mp.organization_code||': '||msik.concatenated_segments description from mtl_parameters mp, csi_item_instances cii, mtl_system_items_kfv msik where ( :$flex$.organization_code is null and cii.last_vld_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=cii.last_vld_organization_id and sysdate between nvl(cii.active_start_date,sysdate) and nvl(cii.active_end_date,sysdate) and cii.last_vld_organization_id=msik.organization_id and cii.inventory_item_id=msik.inventory_item_id and msik.eam_item_type in (1,3) and msik.serial_number_control_code<>1 order by cii.instance_number US Asset Number