eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CS_SR_UWQ_HTML_EMPLOYEE_V

Source

View Text - Preformatted


SELECT inc_b.incident_id, inc_b.incident_number, sev.name serverity, stat.name status, inc_tl.summary, decode(hzp.party_type, 'PERSON', hzp.person_last_name || ' ' || hzp.person_first_name, 'ORGANIZATION', hzp.party_name) person_org, inc_b.incident_owner_id owner_id, res_dt.resource_name owner, typ.name sr_type, lkp1.meaning escalated, NULL coverage_name, NULL coverage_level, inc_b.incident_severity_id, inc_b.incident_status_id, inc_b.incident_type_id, inc_b.resource_type, inc_b.incident_owner_id resource_id, sev.name severity, obj.object_function ieu_object_function, obj.object_parameters ieu_object_parameters, NULL ieu_media_type_uuid, 'Incident_id' ieu_param_pk_col, to_char(inc_b.incident_id) ieu_param_pk_value, inc_b.expected_resolution_date, inc_b.obligation_date, inc_b.creation_date, decode(cshz.contact_type, 'EMPLOYEE', emp.full_name, 'PERSON', hzp1.person_last_name || ' ' || hzp1.person_first_name, hzp1.party_name) contact_name, st_b.sort_order, kfv.concatenated_segments product_name, lkp.meaning sr_creation_channel, inc_b.last_update_date modified_date, usr.user_name modified_by, NULL owner_name, hzp1.email_address, decode(cshz.contact_type, 'EMPLOYEE', per_add.address_line_1 || decode(per_add.address_line_2, NULL, NULL, ';' || per_add.address_line_2) || decode(per_add.address_line_3, NULL, NULL, ';' || per_add.address_line_3) || decode(per_add.town_or_city, NULL, NULL, ' ' || per_add.town_or_city), hzp1.address1 || decode(hzp1.address2, NULL, NULL, ';' || hzp1.address2) || decode(hzp1.address3, NULL, NULL, ';' || hzp1.address3) || decode(hzp1.address4, NULL, NULL, ';' || hzp1.address4) || decode(hzp1.city, NULL, NULL, ' ' || hzp1.city) || decode(hzp1.state, NULL, NULL, ' ' || hzp1.state)) contact_address, decode(cshz.contact_type, 'EMPLOYEE', per_add.country, hzp1.country) contact_country, inc_b.project_number, inc_b.customer_ticket_number, inc_b.current_serial_number, inc_b.problem_code, inc_b.customer_po_number, inc_b.resolution_code, csl.meaning contact_type, decode(cshz.contact_type, 'EMPLOYEE', emp.employee_number, hzp1.party_number) contact_number, csi.serial_number, csi.external_reference tag, inc_b.incident_country, fnd_u.user_name created_by, inc_b.cust_pref_lang_code language_code, urg.name urgency, oks.meaning coverage_type, oks.importance_level FROM cs_incidents_all_tl inc_tl, cs_incidents_b_sec inc_b, cs_incident_statuses_b st_b, cs_incident_statuses_tl stat, cs_incident_severities_tl sev, cs_incident_types_tl typ, per_all_people_f emp, hz_parties hzp, hz_parties hzp1, cs_hz_sr_contact_points cshz, jtf_rs_resource_extns_tl res_dt, jtf_objects_b obj, mtl_system_items_kfv kfv, fnd_lookup_values lkp, fnd_user usr, cs_lookups csl, csi_item_instances csi, fnd_user fnd_u, hr_locations per_add, per_assignments_x asg, jtf_task_references_b tsk_ref, jtf_tasks_b tsk, fnd_lookup_values lkp1, cs_incident_urgencies_tl urg, oks_cov_types_v oks WHERE inc_tl.incident_id = inc_b.incident_id AND inc_tl.LANGUAGE = userenv('LANG') AND inc_b.resource_type = 'RS_EMPLOYEE' AND inc_b.status_flag = 'O' AND inc_b.incident_severity_id = sev.incident_severity_id AND sev.LANGUAGE = userenv('LANG') AND inc_b.incident_status_id = st_b.incident_status_id AND st_b.incident_status_id = stat.incident_status_id AND stat.LANGUAGE = userenv('LANG') AND inc_b.customer_id = hzp.party_id(+) AND inc_b.incident_type_id = typ.incident_type_id AND typ.LANGUAGE = userenv('LANG') AND res_dt.resource_id = inc_b.incident_owner_id AND res_dt.LANGUAGE = userenv('LANG') AND res_dt.category = SUBSTR(inc_b.resource_type, 4, LENGTH(inc_b.resource_type) -3) AND obj.object_code = 'SR_HTML' AND cshz.incident_id(+) = inc_b.incident_id AND cshz.primary_flag(+) = 'Y' AND hzp1.party_id(+) = cshz.party_id AND emp.person_id(+) = cshz.party_id AND sysdate BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+) AND csl.lookup_type(+) = 'CS_SR_CONTACT_TYPE' AND csl.lookup_code(+) = cshz.contact_type AND kfv.inventory_item_id(+) = inc_b.inventory_item_id AND kfv.organization_id(+) = inc_b.inv_organization_id AND lkp.lookup_type = 'CS_SR_CREATION_CHANNEL' AND lkp.lookup_code = nvl(UPPER(inc_b.sr_creation_channel), 'PHONE') AND lkp.LANGUAGE = userenv('LANG') AND lkp.view_application_id = 170 AND lkp.security_group_id = fnd_global.lookup_security_group('CS_SR_CREATION_CHANNEL', 170) AND usr.user_id = inc_b.last_updated_by AND inc_b.customer_product_id = csi.instance_id(+) AND fnd_u.user_id = inc_b.created_by AND asg.person_id(+) = emp.person_id AND asg.primary_flag(+) = 'Y' AND asg.assignment_type(+) = 'E' AND asg.effective_end_date(+) > TRUNC(sysdate) AND asg.location_id = per_add.location_id(+) AND tsk_ref.object_id(+) = inc_b.incident_id AND tsk_ref.object_type_code(+) = 'SR' AND tsk_ref.reference_code(+) = 'ESC' AND tsk.task_id(+) = tsk_ref.task_id AND lkp1.lookup_type(+) = 'JTF_TASK_ESC_LEVEL' AND lkp1.lookup_code(+) = tsk.escalation_level AND lkp1.LANGUAGE(+) = userenv('LANG') AND lkp1.view_application_id(+) = 0 AND lkp1.security_group_id(+) = fnd_global.lookup_security_group('JTF_TASK_ESC_LEVEL', 170) AND inc_b.incident_urgency_id = urg.incident_urgency_id(+) AND urg.LANGUAGE(+) = userenv('LANG') AND oks.code(+) = inc_b.coverage_type AND(tsk.last_update_date = (SELECT MAX(a.last_update_date) FROM jtf_tasks_b a, jtf_task_references_b b WHERE b.object_id = inc_b.incident_id AND a.task_id = b.task_id AND b.object_type_code = 'SR' AND b.reference_code = 'ESC' AND a.entity = 'ESCALATION') OR tsk.last_update_date IS NULL)

View Text - HTML Formatted

SELECT INC_B.INCIDENT_ID
, INC_B.INCIDENT_NUMBER
, SEV.NAME SERVERITY
, STAT.NAME STATUS
, INC_TL.SUMMARY
, DECODE(HZP.PARTY_TYPE
, 'PERSON'
, HZP.PERSON_LAST_NAME || ' ' || HZP.PERSON_FIRST_NAME
, 'ORGANIZATION'
, HZP.PARTY_NAME) PERSON_ORG
, INC_B.INCIDENT_OWNER_ID OWNER_ID
, RES_DT.RESOURCE_NAME OWNER
, TYP.NAME SR_TYPE
, LKP1.MEANING ESCALATED
, NULL COVERAGE_NAME
, NULL COVERAGE_LEVEL
, INC_B.INCIDENT_SEVERITY_ID
, INC_B.INCIDENT_STATUS_ID
, INC_B.INCIDENT_TYPE_ID
, INC_B.RESOURCE_TYPE
, INC_B.INCIDENT_OWNER_ID RESOURCE_ID
, SEV.NAME SEVERITY
, OBJ.OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, OBJ.OBJECT_PARAMETERS IEU_OBJECT_PARAMETERS
, NULL IEU_MEDIA_TYPE_UUID
, 'INCIDENT_ID' IEU_PARAM_PK_COL
, TO_CHAR(INC_B.INCIDENT_ID) IEU_PARAM_PK_VALUE
, INC_B.EXPECTED_RESOLUTION_DATE
, INC_B.OBLIGATION_DATE
, INC_B.CREATION_DATE
, DECODE(CSHZ.CONTACT_TYPE
, 'EMPLOYEE'
, EMP.FULL_NAME
, 'PERSON'
, HZP1.PERSON_LAST_NAME || ' ' || HZP1.PERSON_FIRST_NAME
, HZP1.PARTY_NAME) CONTACT_NAME
, ST_B.SORT_ORDER
, KFV.CONCATENATED_SEGMENTS PRODUCT_NAME
, LKP.MEANING SR_CREATION_CHANNEL
, INC_B.LAST_UPDATE_DATE MODIFIED_DATE
, USR.USER_NAME MODIFIED_BY
, NULL OWNER_NAME
, HZP1.EMAIL_ADDRESS
, DECODE(CSHZ.CONTACT_TYPE
, 'EMPLOYEE'
, PER_ADD.ADDRESS_LINE_1 || DECODE(PER_ADD.ADDRESS_LINE_2
, NULL
, NULL
, ';' || PER_ADD.ADDRESS_LINE_2) || DECODE(PER_ADD.ADDRESS_LINE_3
, NULL
, NULL
, ';' || PER_ADD.ADDRESS_LINE_3) || DECODE(PER_ADD.TOWN_OR_CITY
, NULL
, NULL
, ' ' || PER_ADD.TOWN_OR_CITY)
, HZP1.ADDRESS1 || DECODE(HZP1.ADDRESS2
, NULL
, NULL
, ';' || HZP1.ADDRESS2) || DECODE(HZP1.ADDRESS3
, NULL
, NULL
, ';' || HZP1.ADDRESS3) || DECODE(HZP1.ADDRESS4
, NULL
, NULL
, ';' || HZP1.ADDRESS4) || DECODE(HZP1.CITY
, NULL
, NULL
, ' ' || HZP1.CITY) || DECODE(HZP1.STATE
, NULL
, NULL
, ' ' || HZP1.STATE)) CONTACT_ADDRESS
, DECODE(CSHZ.CONTACT_TYPE
, 'EMPLOYEE'
, PER_ADD.COUNTRY
, HZP1.COUNTRY) CONTACT_COUNTRY
, INC_B.PROJECT_NUMBER
, INC_B.CUSTOMER_TICKET_NUMBER
, INC_B.CURRENT_SERIAL_NUMBER
, INC_B.PROBLEM_CODE
, INC_B.CUSTOMER_PO_NUMBER
, INC_B.RESOLUTION_CODE
, CSL.MEANING CONTACT_TYPE
, DECODE(CSHZ.CONTACT_TYPE
, 'EMPLOYEE'
, EMP.EMPLOYEE_NUMBER
, HZP1.PARTY_NUMBER) CONTACT_NUMBER
, CSI.SERIAL_NUMBER
, CSI.EXTERNAL_REFERENCE TAG
, INC_B.INCIDENT_COUNTRY
, FND_U.USER_NAME CREATED_BY
, INC_B.CUST_PREF_LANG_CODE LANGUAGE_CODE
, URG.NAME URGENCY
, OKS.MEANING COVERAGE_TYPE
, OKS.IMPORTANCE_LEVEL
FROM CS_INCIDENTS_ALL_TL INC_TL
, CS_INCIDENTS_B_SEC INC_B
, CS_INCIDENT_STATUSES_B ST_B
, CS_INCIDENT_STATUSES_TL STAT
, CS_INCIDENT_SEVERITIES_TL SEV
, CS_INCIDENT_TYPES_TL TYP
, PER_ALL_PEOPLE_F EMP
, HZ_PARTIES HZP
, HZ_PARTIES HZP1
, CS_HZ_SR_CONTACT_POINTS CSHZ
, JTF_RS_RESOURCE_EXTNS_TL RES_DT
, JTF_OBJECTS_B OBJ
, MTL_SYSTEM_ITEMS_KFV KFV
, FND_LOOKUP_VALUES LKP
, FND_USER USR
, CS_LOOKUPS CSL
, CSI_ITEM_INSTANCES CSI
, FND_USER FND_U
, HR_LOCATIONS PER_ADD
, PER_ASSIGNMENTS_X ASG
, JTF_TASK_REFERENCES_B TSK_REF
, JTF_TASKS_B TSK
, FND_LOOKUP_VALUES LKP1
, CS_INCIDENT_URGENCIES_TL URG
, OKS_COV_TYPES_V OKS
WHERE INC_TL.INCIDENT_ID = INC_B.INCIDENT_ID
AND INC_TL.LANGUAGE = USERENV('LANG')
AND INC_B.RESOURCE_TYPE = 'RS_EMPLOYEE'
AND INC_B.STATUS_FLAG = 'O'
AND INC_B.INCIDENT_SEVERITY_ID = SEV.INCIDENT_SEVERITY_ID
AND SEV.LANGUAGE = USERENV('LANG')
AND INC_B.INCIDENT_STATUS_ID = ST_B.INCIDENT_STATUS_ID
AND ST_B.INCIDENT_STATUS_ID = STAT.INCIDENT_STATUS_ID
AND STAT.LANGUAGE = USERENV('LANG')
AND INC_B.CUSTOMER_ID = HZP.PARTY_ID(+)
AND INC_B.INCIDENT_TYPE_ID = TYP.INCIDENT_TYPE_ID
AND TYP.LANGUAGE = USERENV('LANG')
AND RES_DT.RESOURCE_ID = INC_B.INCIDENT_OWNER_ID
AND RES_DT.LANGUAGE = USERENV('LANG')
AND RES_DT.CATEGORY = SUBSTR(INC_B.RESOURCE_TYPE
, 4
, LENGTH(INC_B.RESOURCE_TYPE) -3)
AND OBJ.OBJECT_CODE = 'SR_HTML'
AND CSHZ.INCIDENT_ID(+) = INC_B.INCIDENT_ID
AND CSHZ.PRIMARY_FLAG(+) = 'Y'
AND HZP1.PARTY_ID(+) = CSHZ.PARTY_ID
AND EMP.PERSON_ID(+) = CSHZ.PARTY_ID
AND SYSDATE BETWEEN EMP.EFFECTIVE_START_DATE(+)
AND EMP.EFFECTIVE_END_DATE(+)
AND CSL.LOOKUP_TYPE(+) = 'CS_SR_CONTACT_TYPE'
AND CSL.LOOKUP_CODE(+) = CSHZ.CONTACT_TYPE
AND KFV.INVENTORY_ITEM_ID(+) = INC_B.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID(+) = INC_B.INV_ORGANIZATION_ID
AND LKP.LOOKUP_TYPE = 'CS_SR_CREATION_CHANNEL'
AND LKP.LOOKUP_CODE = NVL(UPPER(INC_B.SR_CREATION_CHANNEL)
, 'PHONE')
AND LKP.LANGUAGE = USERENV('LANG')
AND LKP.VIEW_APPLICATION_ID = 170
AND LKP.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP('CS_SR_CREATION_CHANNEL'
, 170)
AND USR.USER_ID = INC_B.LAST_UPDATED_BY
AND INC_B.CUSTOMER_PRODUCT_ID = CSI.INSTANCE_ID(+)
AND FND_U.USER_ID = INC_B.CREATED_BY
AND ASG.PERSON_ID(+) = EMP.PERSON_ID
AND ASG.PRIMARY_FLAG(+) = 'Y'
AND ASG.ASSIGNMENT_TYPE(+) = 'E'
AND ASG.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE)
AND ASG.LOCATION_ID = PER_ADD.LOCATION_ID(+)
AND TSK_REF.OBJECT_ID(+) = INC_B.INCIDENT_ID
AND TSK_REF.OBJECT_TYPE_CODE(+) = 'SR'
AND TSK_REF.REFERENCE_CODE(+) = 'ESC'
AND TSK.TASK_ID(+) = TSK_REF.TASK_ID
AND LKP1.LOOKUP_TYPE(+) = 'JTF_TASK_ESC_LEVEL'
AND LKP1.LOOKUP_CODE(+) = TSK.ESCALATION_LEVEL
AND LKP1.LANGUAGE(+) = USERENV('LANG')
AND LKP1.VIEW_APPLICATION_ID(+) = 0
AND LKP1.SECURITY_GROUP_ID(+) = FND_GLOBAL.LOOKUP_SECURITY_GROUP('JTF_TASK_ESC_LEVEL'
, 170)
AND INC_B.INCIDENT_URGENCY_ID = URG.INCIDENT_URGENCY_ID(+)
AND URG.LANGUAGE(+) = USERENV('LANG')
AND OKS.CODE(+) = INC_B.COVERAGE_TYPE AND(TSK.LAST_UPDATE_DATE = (SELECT MAX(A.LAST_UPDATE_DATE)
FROM JTF_TASKS_B A
, JTF_TASK_REFERENCES_B B
WHERE B.OBJECT_ID = INC_B.INCIDENT_ID
AND A.TASK_ID = B.TASK_ID
AND B.OBJECT_TYPE_CODE = 'SR'
AND B.REFERENCE_CODE = 'ESC'
AND A.ENTITY = 'ESCALATION') OR TSK.LAST_UPDATE_DATE IS NULL)