VIEW: APPS.CS_SR_UWQ_UNOWNED_SEC_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, null 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, hzp1.address1||' '||hzp1.address2||' '||hzp1.address3||' '||hzp1.address4||' '||hzp1.city||' '||hzp1.state contact_address, hzp1.country contact_country, inc_b.project_number, inc_b.customer_ticket_number, inc_b.current_serial_number, 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_objects_vl obj, mtl_system_items_kfv kfv, fnd_lookup_values lkp, fnd_user usr, 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.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 inc_b.incident_status_id = stat.incident_status_id and stat.language = userenv('LANG') and inc_b.customer_id = hzp.party_id(+) and inc_b.owner_assigned_flag = 'N' 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 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 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) and inc_b.incident_urgency_id = urg.incident_urgency_id(+) and urg.language(+) = userenv('LANG') and oks.code(+) = inc_b.coverage_type

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
, NULL 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
, HZP1.ADDRESS1||' '||HZP1.ADDRESS2||' '||HZP1.ADDRESS3||' '||HZP1.ADDRESS4||' '||HZP1.CITY||' '||HZP1.STATE CONTACT_ADDRESS
, HZP1.COUNTRY CONTACT_COUNTRY
, INC_B.PROJECT_NUMBER
, INC_B.CUSTOMER_TICKET_NUMBER
, INC_B.CURRENT_SERIAL_NUMBER
, 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_OBJECTS_VL OBJ
, MTL_SYSTEM_ITEMS_KFV KFV
, FND_LOOKUP_VALUES LKP
, FND_USER USR
, 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.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 INC_B.INCIDENT_STATUS_ID = STAT.INCIDENT_STATUS_ID
AND STAT.LANGUAGE = USERENV('LANG')
AND INC_B.CUSTOMER_ID = HZP.PARTY_ID(+)
AND INC_B.OWNER_ASSIGNED_FLAG = 'N'
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 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 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)
AND INC_B.INCIDENT_URGENCY_ID = URG.INCIDENT_URGENCY_ID(+)
AND URG.LANGUAGE(+) = USERENV('LANG')
AND OKS.CODE(+) = INC_B.COVERAGE_TYPE