VIEW: APPS.CS_SR_UWQ_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_first_name || ' ' ||hzp.person_last_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_line1 || decode(per_add.address_line2,null,null,';'||per_add.address_line2) || decode(per_add.address_line3,null,null,';'||per_add.address_line3) || 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 FROM cs_incidents_all_tl inc_tl, cs_incidents_all_b inc_b, cs_incident_statuses_b st_b, cs_incident_statuses_tl stat, cs_incident_severities_tl sev, cs_incident_types_tl typ, jtf_rs_resource_extns_tl res_dt, per_all_people_f emp, hz_parties hzp, hz_parties hzp1, cs_hz_sr_contact_points cshz, 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, per_addresses per_add, jtf_task_references_b tsk_ref, jtf_tasks_b tsk, fnd_lookup_values lkp1 WHERE inc_tl.incident_id = inc_b.incident_id and inc_tl.language = userenv('LANG') 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.close_flag='N' or st_b.close_flag is null ) and st_b.incident_status_id = stat.incident_status_id and stat.language = userenv('LANG') and inc_b.customer_id = hzp.party_id(+) 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 inc_b.incident_type_id = typ.incident_type_id and typ.language = userenv('LANG') and obj.object_code='SR' 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 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 csl.lookup_type = 'CS_SR_CONTACT_TYPE' and csl.lookup_code = cshz.contact_type and inc_b.customer_product_id = csi.instance_id(+) 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 fnd_u.user_id = inc_b.created_by and per_add.person_id(+) = emp.person_id and per_add.primary_flag(+) = 'Y' and per_add.date_to(+) is null 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', 0) UNION 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.owner_group_id Owner_id, jtfg.group_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.group_type, inc_b.owner_group_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_line1 || decode(per_add.address_line2,null,null,';'||per_add.address_line2) || decode(per_add.address_line3,null,null,';'||per_add.address_line3) || 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 FROM cs_incidents_all_tl inc_tl, cs_incidents_all_b 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_groups_tl jtfg, 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, per_addresses per_add, jtf_task_references_b tsk_ref, jtf_tasks_b tsk, fnd_lookup_values lkp1 WHERE inc_tl.incident_id = inc_b.incident_id and inc_tl.language = userenv('LANG') 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.close_flag='N' or st_b.close_flag is null ) 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.group_type = 'RS_GROUP' and inc_b.incident_type_id = typ.incident_type_id and typ.language = userenv('LANG') and jtfg.group_id = inc_b.owner_group_id and jtfg.language = userenv('LANG') and obj.object_code='SR' 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 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 csl.lookup_type = 'CS_SR_CONTACT_TYPE' and csl.lookup_code = cshz.contact_type and inc_b.customer_product_id = csi.instance_id(+) 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', 0) and usr.user_id = inc_b.last_updated_by and fnd_u.user_id = inc_b.created_by and per_add.person_id(+) = emp.person_id and per_add.primary_flag(+) = 'Y' and per_add.date_to(+) is null 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', 0) UNION 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.owner_group_id Owner_id, jtft.team_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.group_type, inc_b.owner_group_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_line1 || decode(per_add.address_line2,null,null,';'||per_add.address_line2) || decode(per_add.address_line3,null,null,';'||per_add.address_line3) || 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 FROM cs_incidents_all_tl inc_tl, cs_incidents_all_b 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_teams_tl jtft, 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, per_addresses per_add, jtf_task_references_b tsk_ref, jtf_tasks_b tsk, fnd_lookup_values lkp1 WHERE inc_tl.incident_id = inc_b.incident_id and inc_tl.language = userenv('LANG') 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.close_flag='N' or st_b.close_flag is null ) 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.group_type = 'RS_TEAM' and inc_b.incident_type_id = typ.incident_type_id and typ.language = userenv('LANG') and jtft.team_id = inc_b.owner_group_id and jtft.language = userenv('LANG') and obj.object_code='SR' 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 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 csl.lookup_type = 'CS_SR_CONTACT_TYPE' and csl.lookup_code = cshz.contact_type and inc_b.customer_product_id = csi.instance_id(+) 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', 0) and usr.user_id = inc_b.last_updated_by and fnd_u.user_id = inc_b.created_by and per_add.person_id(+) = emp.person_id and per_add.primary_flag(+) = 'Y' and per_add.date_to(+) is null 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', 0)

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_FIRST_NAME || ' ' ||HZP.PERSON_LAST_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_LINE1 || DECODE(PER_ADD.ADDRESS_LINE2
, NULL
, NULL
, ';'||PER_ADD.ADDRESS_LINE2) || DECODE(PER_ADD.ADDRESS_LINE3
, NULL
, NULL
, ';'||PER_ADD.ADDRESS_LINE3) || 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
FROM CS_INCIDENTS_ALL_TL INC_TL
, CS_INCIDENTS_ALL_B INC_B
, CS_INCIDENT_STATUSES_B ST_B
, CS_INCIDENT_STATUSES_TL STAT
, CS_INCIDENT_SEVERITIES_TL SEV
, CS_INCIDENT_TYPES_TL TYP
, JTF_RS_RESOURCE_EXTNS_TL RES_DT
, PER_ALL_PEOPLE_F EMP
, HZ_PARTIES HZP
, HZ_PARTIES HZP1
, CS_HZ_SR_CONTACT_POINTS CSHZ
, 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
, PER_ADDRESSES PER_ADD
, JTF_TASK_REFERENCES_B TSK_REF
, JTF_TASKS_B TSK
, FND_LOOKUP_VALUES LKP1
WHERE INC_TL.INCIDENT_ID = INC_B.INCIDENT_ID
AND INC_TL.LANGUAGE = USERENV('LANG')
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.CLOSE_FLAG='N' OR ST_B.CLOSE_FLAG IS NULL )
AND ST_B.INCIDENT_STATUS_ID = STAT.INCIDENT_STATUS_ID
AND STAT.LANGUAGE = USERENV('LANG')
AND INC_B.CUSTOMER_ID = HZP.PARTY_ID(+)
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 INC_B.INCIDENT_TYPE_ID = TYP.INCIDENT_TYPE_ID
AND TYP.LANGUAGE = USERENV('LANG')
AND OBJ.OBJECT_CODE='SR'
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 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 CSL.LOOKUP_TYPE = 'CS_SR_CONTACT_TYPE'
AND CSL.LOOKUP_CODE = CSHZ.CONTACT_TYPE
AND INC_B.CUSTOMER_PRODUCT_ID = CSI.INSTANCE_ID(+)
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 FND_U.USER_ID = INC_B.CREATED_BY
AND PER_ADD.PERSON_ID(+) = EMP.PERSON_ID
AND PER_ADD.PRIMARY_FLAG(+) = 'Y'
AND PER_ADD.DATE_TO(+) IS NULL
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'
, 0) UNION 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.OWNER_GROUP_ID OWNER_ID
, JTFG.GROUP_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.GROUP_TYPE
, INC_B.OWNER_GROUP_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_LINE1 || DECODE(PER_ADD.ADDRESS_LINE2
, NULL
, NULL
, ';'||PER_ADD.ADDRESS_LINE2) || DECODE(PER_ADD.ADDRESS_LINE3
, NULL
, NULL
, ';'||PER_ADD.ADDRESS_LINE3) || 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
FROM CS_INCIDENTS_ALL_TL INC_TL
, CS_INCIDENTS_ALL_B 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_GROUPS_TL JTFG
, 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
, PER_ADDRESSES PER_ADD
, JTF_TASK_REFERENCES_B TSK_REF
, JTF_TASKS_B TSK
, FND_LOOKUP_VALUES LKP1
WHERE INC_TL.INCIDENT_ID = INC_B.INCIDENT_ID
AND INC_TL.LANGUAGE = USERENV('LANG')
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.CLOSE_FLAG='N' OR ST_B.CLOSE_FLAG IS NULL )
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.GROUP_TYPE = 'RS_GROUP'
AND INC_B.INCIDENT_TYPE_ID = TYP.INCIDENT_TYPE_ID
AND TYP.LANGUAGE = USERENV('LANG')
AND JTFG.GROUP_ID = INC_B.OWNER_GROUP_ID
AND JTFG.LANGUAGE = USERENV('LANG')
AND OBJ.OBJECT_CODE='SR'
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 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 CSL.LOOKUP_TYPE = 'CS_SR_CONTACT_TYPE'
AND CSL.LOOKUP_CODE = CSHZ.CONTACT_TYPE
AND INC_B.CUSTOMER_PRODUCT_ID = CSI.INSTANCE_ID(+)
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'
, 0)
AND USR.USER_ID = INC_B.LAST_UPDATED_BY
AND FND_U.USER_ID = INC_B.CREATED_BY
AND PER_ADD.PERSON_ID(+) = EMP.PERSON_ID
AND PER_ADD.PRIMARY_FLAG(+) = 'Y'
AND PER_ADD.DATE_TO(+) IS NULL
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'
, 0) UNION 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.OWNER_GROUP_ID OWNER_ID
, JTFT.TEAM_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.GROUP_TYPE
, INC_B.OWNER_GROUP_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_LINE1 || DECODE(PER_ADD.ADDRESS_LINE2
, NULL
, NULL
, ';'||PER_ADD.ADDRESS_LINE2) || DECODE(PER_ADD.ADDRESS_LINE3
, NULL
, NULL
, ';'||PER_ADD.ADDRESS_LINE3) || 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
FROM CS_INCIDENTS_ALL_TL INC_TL
, CS_INCIDENTS_ALL_B 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_TEAMS_TL JTFT
, 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
, PER_ADDRESSES PER_ADD
, JTF_TASK_REFERENCES_B TSK_REF
, JTF_TASKS_B TSK
, FND_LOOKUP_VALUES LKP1
WHERE INC_TL.INCIDENT_ID = INC_B.INCIDENT_ID
AND INC_TL.LANGUAGE = USERENV('LANG')
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.CLOSE_FLAG='N' OR ST_B.CLOSE_FLAG IS NULL )
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.GROUP_TYPE = 'RS_TEAM'
AND INC_B.INCIDENT_TYPE_ID = TYP.INCIDENT_TYPE_ID
AND TYP.LANGUAGE = USERENV('LANG')
AND JTFT.TEAM_ID = INC_B.OWNER_GROUP_ID
AND JTFT.LANGUAGE = USERENV('LANG')
AND OBJ.OBJECT_CODE='SR'
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 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 CSL.LOOKUP_TYPE = 'CS_SR_CONTACT_TYPE'
AND CSL.LOOKUP_CODE = CSHZ.CONTACT_TYPE
AND INC_B.CUSTOMER_PRODUCT_ID = CSI.INSTANCE_ID(+)
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'
, 0)
AND USR.USER_ID = INC_B.LAST_UPDATED_BY
AND FND_U.USER_ID = INC_B.CREATED_BY
AND PER_ADD.PERSON_ID(+) = EMP.PERSON_ID
AND PER_ADD.PRIMARY_FLAG(+) = 'Y'
AND PER_ADD.DATE_TO(+) IS NULL
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'
, 0)