ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 9A31E6B07C2D4F0E8B5A1C3D7E9F2A64Y select (select haouv.name from hr_all_organization_units_vl haouv where cibs.org_id=haouv.organization_id) operating_unit, cibs.incident_number request_number, citv.name request_type, cisv.name status, (select cisv2.name from cs_incident_severities_vl cisv2 where cibs.incident_severity_id=cisv2.incident_severity_id) severity, (select ciuv.name from cs_incident_urgencies_vl ciuv where cibs.incident_urgency_id=ciuv.incident_urgency_id) urgency, ciat.summary, xxen_util.meaning(cibs.problem_code,'REQUEST_PROBLEM_CODE',170) problem_code, xxen_util.meaning(cibs.resolution_code,'REQUEST_RESOLUTION_CODE',170) resolution_code, ciat.resolution_summary, hp.party_name customer, hp.party_number customer_number, (select hca.account_number from hz_cust_accounts hca where cibs.account_id=hca.cust_account_id) account_number, case when cpc.contact_type='EMPLOYEE' then (select papf.full_name from per_all_people_f papf where cpc.party_id=papf.person_id and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date) when cpc.contact_type='PARTY_RELATIONSHIP' then (select hp2.party_name from hz_relationships hr, hz_parties hp2 where cpc.party_id=hr.party_id and hr.subject_type='PERSON' and hr.directional_flag='F' and hr.subject_id=hp2.party_id) else (select hp2.party_name from hz_parties hp2 where cpc.party_id=hp2.party_id) end primary_contact, msiv.concatenated_segments item, msiv.description item_description, (select cii.instance_number from csi_item_instances cii where cibs.customer_product_id=cii.instance_id) instance_number, cibs.current_serial_number serial_number, (select jrrev.resource_name from jtf_rs_resource_extns_vl jrrev where cibs.incident_owner_id=jrrev.resource_id) owner, (select jrgv.group_name from jtf_rs_groups_vl jrgv where cibs.owner_group_id=jrgv.group_id and cibs.group_type='RS_GROUP') owner_group, nvl(xxen_util.meaning(cibs.sr_creation_channel,'CS_SR_CREATION_CHANNEL',170),cibs.sr_creation_channel) channel, cibs.contract_number, cibs.incident_date reported_date, cibs.obligation_date respond_by_date, cibs.inc_responded_by_date responded_date, cibs.expected_resolution_date resolve_by_date, cibs.incident_resolved_date resolved_date, cibs.close_date, round((cibs.inc_responded_by_date-cibs.incident_date)*24,2) response_hours, round((cibs.incident_resolved_date-cibs.incident_date)*24,2) resolution_hours, round(nvl(cibs.close_date,sysdate)-cibs.incident_date,1) days_open, xxen_util.yes(case when coalesce(cibs.inc_responded_by_date,cibs.close_date,sysdate)>cibs.obligation_date then 'Y' end) response_overdue, xxen_util.yes(case when coalesce(cibs.incident_resolved_date,cibs.close_date,sysdate)>cibs.expected_resolution_date then 'Y' end) resolution_overdue, &detail_columns xxen_util.user_name(cibs.created_by) created_by, cibs.creation_date from cs_incidents_b_sec cibs, cs_incidents_all_tl ciat, cs_incident_types_vl citv, cs_incident_statuses_vl cisv, hz_parties hp, mtl_system_items_vl msiv, (select chscp.incident_id, chscp.contact_type, chscp.party_id from cs_hz_sr_contact_points chscp where chscp.primary_flag='Y') cpc &detail_from where 1=1 and cibs.incident_id=ciat.incident_id and ciat.language=userenv('lang') and cibs.incident_type_id=citv.incident_type_id and cibs.incident_status_id=cisv.incident_status_id and cibs.customer_id=hp.party_id(+) and cibs.inventory_item_id=msiv.inventory_item_id(+) and cibs.inv_organization_id=msiv.organization_id(+) and cibs.incident_id=cpc.incident_id(+) &detail_where order by cibs.incident_date desc, cibs.incident_number &detail_order New report. US CS Service Requests Service requests with customer, product, owner, status and response and resolution times, one row per request. Response and resolution hours are measured from the reported date to the responded and resolved dates. A request is overdue when it was responded to or resolved after its respond by or resolve by date. Without a responded or resolved date, its close date is used, or the current date while it is open. Detail Level adds the request's tasks or notes, one row per task or note; private notes are listed only for the user who entered them. Enginatics &detail_columns &detail_from &detail_order &detail_where 1=1 1 10 1=1 cibs.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit) LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name US Operating Unit 2 20 1=1 cibs.incident_date>=:reported_date_from Date US Reported Date From 3 30 1=1 cibs.incident_date<:reported_date_to+1 Date :$flex$.reported_date_from US Reported Date To 4 40 1=1 cibs.incident_number=:request_number Char US Request Number 5 50 1=1 citv.name=:request_type LOV custom Y select citv.name value, citv.description from cs_incident_types_vl citv where citv.incident_subtype='INC' order by citv.name US Request Type 6 60 1=1 cisv.name=:status LOV custom Y select cisv.name value, cisv.description from cs_incident_statuses_vl cisv where cisv.incident_subtype='INC' order by cisv.name US Status 7 70 1=1 cibs.status_flag='O' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Open Only 8 80 1=1 cibs.incident_severity_id in (select cisv2.incident_severity_id from cs_incident_severities_vl cisv2 where cisv2.name=:severity) LOV custom Y select cisv.name value, cisv.description from cs_incident_severities_vl cisv where cisv.incident_subtype='INC' order by cisv.importance_level, cisv.name US Severity 9 90 1=1 hp.party_name=:customer_name LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Customer Name 10 100 1=1 cibs.owner_group_id in (select jrgv.group_id from jtf_rs_groups_vl jrgv where jrgv.group_name=:owner_group) and cibs.group_type='RS_GROUP' LOV custom Y select jrgv.group_name value, jrgv.group_desc description from jtf_rs_groups_vl jrgv where jrgv.group_id in (select cibs.owner_group_id from cs_incidents_b_sec cibs where cibs.group_type='RS_GROUP') order by jrgv.group_name US Owner Group 11 110 1=1 cibs.incident_owner_id in (select jrrev.resource_id from jtf_rs_resource_extns_vl jrrev where jrrev.resource_name=:owner) LOV custom Y select jrrev.resource_name value, xxen_util.meaning(jrrev.category,'JTF_RS_RESOURCE_CATEGORY',0) description from jtf_rs_resource_extns_vl jrrev where jrrev.resource_id in (select cibs.incident_owner_id from cs_incidents_b_sec cibs) order by jrrev.resource_name US Owner 12 120 1=1 msiv.concatenated_segments=:item LOV INV Item (master) 8E2FF36EDEF379D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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) ) order by msiv.concatenated_segments US Item 13 130 1=1 cibs.current_serial_number=:serial_number Char US Serial Number 14 140 &detail_columns jtv.task_number, jtv.task_name, (select jttv.name from jtf_task_types_vl jttv where jtv.task_type_id=jttv.task_type_id) task_type, (select jtsv.name from jtf_task_statuses_vl jtsv where jtv.task_status_id=jtsv.task_status_id) task_status, case when jtv.owner_type_code='RS_GROUP' then (select jrgv2.group_name from jtf_rs_groups_vl jrgv2 where jtv.owner_id=jrgv2.group_id) else (select jrrev2.resource_name from jtf_rs_resource_extns_vl jrrev2 where jtv.owner_id=jrrev2.resource_id) end task_owner, jtv.planned_start_date, jtv.planned_end_date, jtv.scheduled_start_date, jtv.scheduled_end_date, jtv.actual_start_date, jtv.actual_end_date, jtv.description task_description, LOV custom Y select 'T' id, 'Tasks' value, 'One row per service request task' description from dual union all select 'N' id, 'Notes' value, 'One row per service request note' description from dual T US Detail Level Adds the tasks or the notes of each service request, one row per task or note 15 &detail_columns xxen_util.meaning(jnv.note_type,'JTF_NOTE_TYPE',0) note_type, xxen_util.meaning(jnv.note_status,'JTF_NOTE_STATUS',0) note_status, jnv.notes, xxen_util.user_name(jnv.entered_by) note_entered_by, jnv.entered_date note_date, N US Detail Level 16 &detail_from , (select jnv.* from jtf_notes_vl jnv where jnv.source_object_code='SR' and (jnv.note_status<>'P' or jnv.entered_by=fnd_global.user_id)) jnv N US Detail Level 17 &detail_from , jtf_tasks_vl jtv T US Detail Level 18 &detail_order ,jnv.entered_date N US Detail Level 19 &detail_order ,jtv.planned_start_date, jtv.task_number T US Detail Level 20 &detail_where and cibs.incident_id=jnv.source_object_id(+) N US Detail Level 21 &detail_where and cibs.incident_id=jtv.source_object_id(+) and jtv.source_object_type_code(+)='SR' T US Detail Level