VIEW: APPS.CS_SR_SEARCH_V

Source

View Text - Preformatted

SELECT inc.rowid row_id, inc.incident_id, inc.org_id, inc.last_update_date, inc.last_updated_by, inc.creation_date, inc.created_by, inc.last_update_login, inc.incident_number, inc.incident_number incident_number_n, inc.incident_status_id, inc.incident_type_id, inc.incident_urgency_id, inc.incident_severity_id, tl.summary, inc.incident_owner_id, inc.resource_type, inc.resource_subtype_id, inc.inventory_item_id, inc.ship_to_site_use_id, inc.ship_to_contact_id, inc.bill_to_site_use_id, inc.bill_to_contact_id, inc.install_site_use_id, (SELECT meaning problem_code FROM fnd_lookup_values WHERE lookup_type = 'REQUEST_PROBLEM_CODE' AND lookup_code = inc.problem_code AND LANGUAGE = userenv('LANG')) problem_code, inc.expected_resolution_date, inc.incident_date, inc.incident_resolved_date, inc.customer_product_id, inc.incident_attribute_1, inc.incident_attribute_2, inc.incident_attribute_3, inc.incident_attribute_4, inc.incident_attribute_5, inc.incident_attribute_6, inc.incident_attribute_7, inc.incident_attribute_8, inc.incident_attribute_9, inc.incident_attribute_10, inc.incident_attribute_11, inc.incident_attribute_12, inc.incident_attribute_13, inc.incident_attribute_14, inc.incident_attribute_15, inc.incident_context, inc.record_is_valid_flag, inc.resolution_code, inc.interfaced_to_depot_flag, (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.filed_by_employee_flag, inc.customer_id, inc.account_id, inc.employee_id, NULL employee_name, NULL employee_number, inc.current_serial_number current_serial_number_nv, inc.purchase_order_num, inc.original_order_number, inc.workflow_process_id, inc.close_date date_closed, inc.publish_flag, inc.qa_collection_id, inc.contract_service_id, inc.kb_solution_id, inc.time_zone_id, inc.customer_po_number, inc.customer_ticket_number, inc.obligation_date, inc.site_id, inc.customer_site_id, inc.sr_creation_channel, inc.platform_version_id, party.party_number customer_number, inc.caller_type, inc.cp_component_id, inc.cp_component_version_id, inc.cp_subcomponent_id, inc.cp_subcomponent_version_id, inc.cp_revision_id, inc.inv_item_revision, inc.inv_component_id, inc.inv_component_version, inc.inv_subcomponent_id, inc.inv_subcomponent_version, inc.platform_id, inc.language_id, inc.territory_id, inc.time_difference, inc.inv_organization_id, 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, sr_party.person_first_name || ' ' || sr_party.person_last_name contact_name, party_cont.phone_country_code country_code, party_cont.phone_area_code area_code, party_cont.phone_number, decode(party_cont.phone_country_code, NULL, NULL, phone_country_code || '-') || decode(party_cont.phone_area_code, NULL, NULL, phone_area_code || '-') || party_cont.phone_number phone, party_cont.phone_extension ext, party_cont.email_address email, party_cont.telephone_type phone_type, sr_cont.party_id contact_party_id, sr_cont.contact_point_id, (SELECT account.account_number FROM hz_cust_accounts account WHERE account.cust_account_id = inc.account_id) account, inc.object_version_number, sr_party.person_pre_name_adjunct person_title, (SELECT look5.meaning FROM cs_lookups look5 WHERE look5.lookup_type = 'CS_SR_CALLER_TYPE' AND look5.lookup_code = inc.caller_type) caller_type_name, (SELECT look5.description FROM cs_lookups look5 WHERE look5.lookup_type = 'CS_SR_CALLER_TYPE' AND look5.lookup_code = inc.caller_type) caller_type_description, (SELECT hz_time.global_timezone_name FROM hz_timezones hz_time WHERE hz_time.timezone_id = inc.time_zone_id) time_zone_name, kfv.organization_id organization_id, party.country, inc.contract_number, inc.contract_id, inc.project_number, sr_cont.contact_type, sr_party.party_number contact_number, inc.group_type, inc.owner_group_id, inc.error_code, inc.system_id, inc.external_reference, inc.incident_occurred_date, grp.group_name group_owner, inc.category_id, inc.incident_address, inc.incident_location_id, inc.incident_country, inc.incident_postal_code, inc.incident_state, inc.incident_location_type, sr_cont.primary_flag, inc.status_flag, inc.item_serial_number, inc.incident_address2, inc.incident_address3, inc.incident_address4, party.known_as customer_alias, sr_cont.party_role_code, tl.text_index FROM cs_incidents_b_sec inc, cs_incidents_all_tl tl, mtl_system_items_kfv kfv, hz_parties party, jtf_rs_groups_vl grp, 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 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.party_role_code(+) = 'CONTACT' AND inc.owner_group_id = grp.group_id(+)

View Text - HTML Formatted

SELECT INC.ROWID ROW_ID
, INC.INCIDENT_ID
, INC.ORG_ID
, INC.LAST_UPDATE_DATE
, INC.LAST_UPDATED_BY
, INC.CREATION_DATE
, INC.CREATED_BY
, INC.LAST_UPDATE_LOGIN
, INC.INCIDENT_NUMBER
, INC.INCIDENT_NUMBER INCIDENT_NUMBER_N
, INC.INCIDENT_STATUS_ID
, INC.INCIDENT_TYPE_ID
, INC.INCIDENT_URGENCY_ID
, INC.INCIDENT_SEVERITY_ID
, TL.SUMMARY
, INC.INCIDENT_OWNER_ID
, INC.RESOURCE_TYPE
, INC.RESOURCE_SUBTYPE_ID
, INC.INVENTORY_ITEM_ID
, INC.SHIP_TO_SITE_USE_ID
, INC.SHIP_TO_CONTACT_ID
, INC.BILL_TO_SITE_USE_ID
, INC.BILL_TO_CONTACT_ID
, INC.INSTALL_SITE_USE_ID
, (SELECT MEANING PROBLEM_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'REQUEST_PROBLEM_CODE'
AND LOOKUP_CODE = INC.PROBLEM_CODE
AND LANGUAGE = USERENV('LANG')) PROBLEM_CODE
, INC.EXPECTED_RESOLUTION_DATE
, INC.INCIDENT_DATE
, INC.INCIDENT_RESOLVED_DATE
, INC.CUSTOMER_PRODUCT_ID
, INC.INCIDENT_ATTRIBUTE_1
, INC.INCIDENT_ATTRIBUTE_2
, INC.INCIDENT_ATTRIBUTE_3
, INC.INCIDENT_ATTRIBUTE_4
, INC.INCIDENT_ATTRIBUTE_5
, INC.INCIDENT_ATTRIBUTE_6
, INC.INCIDENT_ATTRIBUTE_7
, INC.INCIDENT_ATTRIBUTE_8
, INC.INCIDENT_ATTRIBUTE_9
, INC.INCIDENT_ATTRIBUTE_10
, INC.INCIDENT_ATTRIBUTE_11
, INC.INCIDENT_ATTRIBUTE_12
, INC.INCIDENT_ATTRIBUTE_13
, INC.INCIDENT_ATTRIBUTE_14
, INC.INCIDENT_ATTRIBUTE_15
, INC.INCIDENT_CONTEXT
, INC.RECORD_IS_VALID_FLAG
, INC.RESOLUTION_CODE
, INC.INTERFACED_TO_DEPOT_FLAG
, (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.FILED_BY_EMPLOYEE_FLAG
, INC.CUSTOMER_ID
, INC.ACCOUNT_ID
, INC.EMPLOYEE_ID
, NULL EMPLOYEE_NAME
, NULL EMPLOYEE_NUMBER
, INC.CURRENT_SERIAL_NUMBER CURRENT_SERIAL_NUMBER_NV
, INC.PURCHASE_ORDER_NUM
, INC.ORIGINAL_ORDER_NUMBER
, INC.WORKFLOW_PROCESS_ID
, INC.CLOSE_DATE DATE_CLOSED
, INC.PUBLISH_FLAG
, INC.QA_COLLECTION_ID
, INC.CONTRACT_SERVICE_ID
, INC.KB_SOLUTION_ID
, INC.TIME_ZONE_ID
, INC.CUSTOMER_PO_NUMBER
, INC.CUSTOMER_TICKET_NUMBER
, INC.OBLIGATION_DATE
, INC.SITE_ID
, INC.CUSTOMER_SITE_ID
, INC.SR_CREATION_CHANNEL
, INC.PLATFORM_VERSION_ID
, PARTY.PARTY_NUMBER CUSTOMER_NUMBER
, INC.CALLER_TYPE
, INC.CP_COMPONENT_ID
, INC.CP_COMPONENT_VERSION_ID
, INC.CP_SUBCOMPONENT_ID
, INC.CP_SUBCOMPONENT_VERSION_ID
, INC.CP_REVISION_ID
, INC.INV_ITEM_REVISION
, INC.INV_COMPONENT_ID
, INC.INV_COMPONENT_VERSION
, INC.INV_SUBCOMPONENT_ID
, INC.INV_SUBCOMPONENT_VERSION
, INC.PLATFORM_ID
, INC.LANGUAGE_ID
, INC.TERRITORY_ID
, INC.TIME_DIFFERENCE
, INC.INV_ORGANIZATION_ID
, 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
, SR_PARTY.PERSON_FIRST_NAME || ' ' || SR_PARTY.PERSON_LAST_NAME CONTACT_NAME
, PARTY_CONT.PHONE_COUNTRY_CODE COUNTRY_CODE
, PARTY_CONT.PHONE_AREA_CODE AREA_CODE
, PARTY_CONT.PHONE_NUMBER
, DECODE(PARTY_CONT.PHONE_COUNTRY_CODE
, NULL
, NULL
, PHONE_COUNTRY_CODE || '-') || DECODE(PARTY_CONT.PHONE_AREA_CODE
, NULL
, NULL
, PHONE_AREA_CODE || '-') || PARTY_CONT.PHONE_NUMBER PHONE
, PARTY_CONT.PHONE_EXTENSION EXT
, PARTY_CONT.EMAIL_ADDRESS EMAIL
, PARTY_CONT.TELEPHONE_TYPE PHONE_TYPE
, SR_CONT.PARTY_ID CONTACT_PARTY_ID
, SR_CONT.CONTACT_POINT_ID
, (SELECT ACCOUNT.ACCOUNT_NUMBER
FROM HZ_CUST_ACCOUNTS ACCOUNT
WHERE ACCOUNT.CUST_ACCOUNT_ID = INC.ACCOUNT_ID) ACCOUNT
, INC.OBJECT_VERSION_NUMBER
, SR_PARTY.PERSON_PRE_NAME_ADJUNCT PERSON_TITLE
, (SELECT LOOK5.MEANING
FROM CS_LOOKUPS LOOK5
WHERE LOOK5.LOOKUP_TYPE = 'CS_SR_CALLER_TYPE'
AND LOOK5.LOOKUP_CODE = INC.CALLER_TYPE) CALLER_TYPE_NAME
, (SELECT LOOK5.DESCRIPTION
FROM CS_LOOKUPS LOOK5
WHERE LOOK5.LOOKUP_TYPE = 'CS_SR_CALLER_TYPE'
AND LOOK5.LOOKUP_CODE = INC.CALLER_TYPE) CALLER_TYPE_DESCRIPTION
, (SELECT HZ_TIME.GLOBAL_TIMEZONE_NAME
FROM HZ_TIMEZONES HZ_TIME
WHERE HZ_TIME.TIMEZONE_ID = INC.TIME_ZONE_ID) TIME_ZONE_NAME
, KFV.ORGANIZATION_ID ORGANIZATION_ID
, PARTY.COUNTRY
, INC.CONTRACT_NUMBER
, INC.CONTRACT_ID
, INC.PROJECT_NUMBER
, SR_CONT.CONTACT_TYPE
, SR_PARTY.PARTY_NUMBER CONTACT_NUMBER
, INC.GROUP_TYPE
, INC.OWNER_GROUP_ID
, INC.ERROR_CODE
, INC.SYSTEM_ID
, INC.EXTERNAL_REFERENCE
, INC.INCIDENT_OCCURRED_DATE
, GRP.GROUP_NAME GROUP_OWNER
, INC.CATEGORY_ID
, INC.INCIDENT_ADDRESS
, INC.INCIDENT_LOCATION_ID
, INC.INCIDENT_COUNTRY
, INC.INCIDENT_POSTAL_CODE
, INC.INCIDENT_STATE
, INC.INCIDENT_LOCATION_TYPE
, SR_CONT.PRIMARY_FLAG
, INC.STATUS_FLAG
, INC.ITEM_SERIAL_NUMBER
, INC.INCIDENT_ADDRESS2
, INC.INCIDENT_ADDRESS3
, INC.INCIDENT_ADDRESS4
, PARTY.KNOWN_AS CUSTOMER_ALIAS
, SR_CONT.PARTY_ROLE_CODE
, TL.TEXT_INDEX
FROM CS_INCIDENTS_B_SEC INC
, CS_INCIDENTS_ALL_TL TL
, MTL_SYSTEM_ITEMS_KFV KFV
, HZ_PARTIES PARTY
, JTF_RS_GROUPS_VL GRP
, 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 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.PARTY_ROLE_CODE(+) = 'CONTACT'
AND INC.OWNER_GROUP_ID = GRP.GROUP_ID(+)