VIEW: APPS.CSF_SR_HIST_V

Source

View Text - Preformatted

SELECT inc.rowid row_id , inc.incident_id , inc.org_id , inc.creation_date , inc.incident_number , inc.incident_number incident_number_n , tl.summary , inc.inventory_item_id , inc.install_site_use_id , inc.problem_code , inc.expected_resolution_date , inc.incident_date , inc.customer_product_id , cp.instance_number , inc.resolution_code , (select type.name from cs_incident_types_tl type where type.incident_type_id=inc.incident_type_id and type.language=userenv('LANG')) incident_type , (select sev.name from cs_incident_severities_tl sev where sev.incident_severity_id =inc.incident_severity_id and sev.language=USERENV('LANG')) severity , (select status.name from cs_incident_statuses_tl status where status.incident_status_id =inc.incident_status_id and status.language=USERENV('LANG')) status_code , (select urgency.name from cs_incident_urgencies_tl urgency where urgency.incident_urgency_id =inc.incident_urgency_id and urgency.language(+)=USERENV('LANG')) urgency , inc.customer_id , inc.employee_id , null employee_name , inc.contract_Service_id , party.party_number customer_number , inc.caller_type , kfv.concatenated_segments product_name , kfv.description product_description , tl.owner owner , party.party_name company_name , sr_party.person_first_name||' '||sr_party.person_last_name contact_name , party_cont.phone_number phone , party_cont.email_address email , (select hz_time.global_timezone_name from hz_timezones hz_time where hz_time.timezone_id = inc.time_zone_id) time_zone_name , party.country ,inc.contract_number ,sr_cont.contact_type ,sr_party.party_number contact_number , inc.group_type ,tl.text_index from cs_incidents_all_b inc, cs_incidents_all_tl tl, csi_item_instances cp, mtl_system_items_kfv kfv, hz_parties party, hz_parties sr_party, hz_contact_points party_cont, cs_hz_sr_contact_points sr_cont WHERE inc.inventory_item_id=kfv.inventory_item_id(+) and inc.inv_organization_id = kfv.organization_id(+) and cp.instance_id(+)=inc.customer_product_id and inc.incident_id=tl.incident_id and tl.language=userenv('LANG') and inc.customer_id=party.party_id and sr_cont.incident_id(+)=inc.incident_id and sr_cont.contact_point_id=party_cont.contact_point_id(+) and sr_cont.party_id=sr_party.party_id(+) and sr_cont.primary_flag(+)='Y'

View Text - HTML Formatted

SELECT INC.ROWID ROW_ID
, INC.INCIDENT_ID
, INC.ORG_ID
, INC.CREATION_DATE
, INC.INCIDENT_NUMBER
, INC.INCIDENT_NUMBER INCIDENT_NUMBER_N
, TL.SUMMARY
, INC.INVENTORY_ITEM_ID
, INC.INSTALL_SITE_USE_ID
, INC.PROBLEM_CODE
, INC.EXPECTED_RESOLUTION_DATE
, INC.INCIDENT_DATE
, INC.CUSTOMER_PRODUCT_ID
, CP.INSTANCE_NUMBER
, INC.RESOLUTION_CODE
, (SELECT TYPE.NAME
FROM CS_INCIDENT_TYPES_TL TYPE
WHERE TYPE.INCIDENT_TYPE_ID=INC.INCIDENT_TYPE_ID
AND TYPE.LANGUAGE=USERENV('LANG')) INCIDENT_TYPE
, (SELECT SEV.NAME
FROM CS_INCIDENT_SEVERITIES_TL SEV
WHERE SEV.INCIDENT_SEVERITY_ID =INC.INCIDENT_SEVERITY_ID
AND SEV.LANGUAGE=USERENV('LANG')) SEVERITY
, (SELECT STATUS.NAME
FROM CS_INCIDENT_STATUSES_TL STATUS
WHERE STATUS.INCIDENT_STATUS_ID =INC.INCIDENT_STATUS_ID
AND STATUS.LANGUAGE=USERENV('LANG')) STATUS_CODE
, (SELECT URGENCY.NAME
FROM CS_INCIDENT_URGENCIES_TL URGENCY
WHERE URGENCY.INCIDENT_URGENCY_ID =INC.INCIDENT_URGENCY_ID
AND URGENCY.LANGUAGE(+)=USERENV('LANG')) URGENCY
, INC.CUSTOMER_ID
, INC.EMPLOYEE_ID
, NULL EMPLOYEE_NAME
, INC.CONTRACT_SERVICE_ID
, PARTY.PARTY_NUMBER CUSTOMER_NUMBER
, INC.CALLER_TYPE
, KFV.CONCATENATED_SEGMENTS PRODUCT_NAME
, KFV.DESCRIPTION PRODUCT_DESCRIPTION
, TL.OWNER OWNER
, PARTY.PARTY_NAME COMPANY_NAME
, SR_PARTY.PERSON_FIRST_NAME||' '||SR_PARTY.PERSON_LAST_NAME CONTACT_NAME
, PARTY_CONT.PHONE_NUMBER PHONE
, PARTY_CONT.EMAIL_ADDRESS EMAIL
, (SELECT HZ_TIME.GLOBAL_TIMEZONE_NAME
FROM HZ_TIMEZONES HZ_TIME
WHERE HZ_TIME.TIMEZONE_ID = INC.TIME_ZONE_ID) TIME_ZONE_NAME
, PARTY.COUNTRY
, INC.CONTRACT_NUMBER
, SR_CONT.CONTACT_TYPE
, SR_PARTY.PARTY_NUMBER CONTACT_NUMBER
, INC.GROUP_TYPE
, TL.TEXT_INDEX
FROM CS_INCIDENTS_ALL_B INC
, CS_INCIDENTS_ALL_TL TL
, CSI_ITEM_INSTANCES CP
, MTL_SYSTEM_ITEMS_KFV KFV
, HZ_PARTIES PARTY
, HZ_PARTIES SR_PARTY
, HZ_CONTACT_POINTS PARTY_CONT
, CS_HZ_SR_CONTACT_POINTS SR_CONT
WHERE INC.INVENTORY_ITEM_ID=KFV.INVENTORY_ITEM_ID(+)
AND INC.INV_ORGANIZATION_ID = KFV.ORGANIZATION_ID(+)
AND CP.INSTANCE_ID(+)=INC.CUSTOMER_PRODUCT_ID
AND INC.INCIDENT_ID=TL.INCIDENT_ID
AND TL.LANGUAGE=USERENV('LANG')
AND INC.CUSTOMER_ID=PARTY.PARTY_ID
AND SR_CONT.INCIDENT_ID(+)=INC.INCIDENT_ID
AND SR_CONT.CONTACT_POINT_ID=PARTY_CONT.CONTACT_POINT_ID(+)
AND SR_CONT.PARTY_ID=SR_PARTY.PARTY_ID(+)
AND SR_CONT.PRIMARY_FLAG(+)='Y'