VIEW: APPS.CS_SR_SEARCH_NO_CONT_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, inc.problem_code, inc.expected_resolution_date, inc.incident_resolved_date, inc.incident_date, inc.customer_product_id, 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.account_id, decode(inc.customer_product_id, NULL, current_serial_number, (SELECT serial_number FROM csi_item_instances WHERE instance_id = inc.customer_product_id)) 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.contract_service_id, inc.time_zone_id, inc.customer_po_number, inc.customer_ticket_number, inc.obligation_date, inc.site_id, inc.customer_site_id, (SELECT meaning FROM cs_lookups WHERE lookup_type = 'CS_SR_CREATION_CHANNEL' AND lookup_code(+) = inc.sr_creation_channel) sr_creation_channel_meaning, 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, party.party_name company_name, (SELECT account.account_number FROM hz_cust_accounts account WHERE account.cust_account_id = inc.account_id) account, inc.object_version_number, (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, inc.group_type, inc.owner_group_id, (SELECT grp.group_name FROM jtf_rs_groups_vl grp, jtf_rs_group_usages usg WHERE grp.group_id = inc.owner_group_id AND grp.group_id = usg.group_id AND usg.usage = 'SUPPORT' ) GROUP_OWNER, inc.error_code, inc.system_id, inc.external_reference, inc.incident_occurred_date, inc.category_id, address_v.address incident_address , inc.incident_location_id, address_v.country incident_country , address_v.postal_code incident_postal_code , address_v.state incident_state , inc.incident_location_type, inc.status_flag, inc.item_serial_number, party.known_as customer_alias, inc.maint_organization_id, tl.text_index, address_v.city incident_city , address_v.county incident_county , address_v.province incident_province , inc.project_id , inc.project_task_id FROM cs_incidents_b_sec inc, cs_incidents_all_tl tl, mtl_system_items_kfv kfv, hz_parties party, (Select inc.incident_id, l.address1|| DECODE(l.address2,NULL,NULL,';'||l.address2) || DECODE(l.address3,NULL,NULL,';'||l.address3) || DECODE(l.address4,NULL,NULL,';'||l.address4) address, l.city,l.state,l.country,l.province,l.county,l.postal_code from hz_party_sites s ,hz_locations l, cs_incidents_b_sec inc where s.location_id = l.location_id and inc.incident_location_type = 'HZ_PARTY_SITE' and inc.incident_location_id = s.party_site_id union Select inc.incident_id, l.address1|| DECODE(l.address2,NULL,NULL,';'||l.address2) || DECODE(l.address3,NULL,NULL,';'||l.address3) || DECODE(l.address4,NULL,NULL,';'||l.address4) address, l.city,l.state,l.country,l.province,l.county,l.postal_code from hz_locations l, cs_incidents_b_sec inc where inc.incident_location_type = 'HZ_LOCATION' and inc.incident_location_id = l.location_id) address_v 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 inc.incident_id = address_v.incident_id(+) AND tl.LANGUAGE = userenv('LANG') AND inc.customer_id = party.party_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
, INC.PROBLEM_CODE
, INC.EXPECTED_RESOLUTION_DATE
, INC.INCIDENT_RESOLVED_DATE
, INC.INCIDENT_DATE
, INC.CUSTOMER_PRODUCT_ID
, 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.ACCOUNT_ID
, DECODE(INC.CUSTOMER_PRODUCT_ID
, NULL
, CURRENT_SERIAL_NUMBER
, (SELECT SERIAL_NUMBER
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = INC.CUSTOMER_PRODUCT_ID)) 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.CONTRACT_SERVICE_ID
, INC.TIME_ZONE_ID
, INC.CUSTOMER_PO_NUMBER
, INC.CUSTOMER_TICKET_NUMBER
, INC.OBLIGATION_DATE
, INC.SITE_ID
, INC.CUSTOMER_SITE_ID
, (SELECT MEANING
FROM CS_LOOKUPS
WHERE LOOKUP_TYPE = 'CS_SR_CREATION_CHANNEL'
AND LOOKUP_CODE(+) = INC.SR_CREATION_CHANNEL) SR_CREATION_CHANNEL_MEANING
, 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
, PARTY.PARTY_NAME COMPANY_NAME
, (SELECT ACCOUNT.ACCOUNT_NUMBER
FROM HZ_CUST_ACCOUNTS ACCOUNT
WHERE ACCOUNT.CUST_ACCOUNT_ID = INC.ACCOUNT_ID) ACCOUNT
, INC.OBJECT_VERSION_NUMBER
, (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
, INC.GROUP_TYPE
, INC.OWNER_GROUP_ID
, (SELECT GRP.GROUP_NAME
FROM JTF_RS_GROUPS_VL GRP
, JTF_RS_GROUP_USAGES USG
WHERE GRP.GROUP_ID = INC.OWNER_GROUP_ID
AND GRP.GROUP_ID = USG.GROUP_ID
AND USG.USAGE = 'SUPPORT' ) GROUP_OWNER
, INC.ERROR_CODE
, INC.SYSTEM_ID
, INC.EXTERNAL_REFERENCE
, INC.INCIDENT_OCCURRED_DATE
, INC.CATEGORY_ID
, ADDRESS_V.ADDRESS INCIDENT_ADDRESS
, INC.INCIDENT_LOCATION_ID
, ADDRESS_V.COUNTRY INCIDENT_COUNTRY
, ADDRESS_V.POSTAL_CODE INCIDENT_POSTAL_CODE
, ADDRESS_V.STATE INCIDENT_STATE
, INC.INCIDENT_LOCATION_TYPE
, INC.STATUS_FLAG
, INC.ITEM_SERIAL_NUMBER
, PARTY.KNOWN_AS CUSTOMER_ALIAS
, INC.MAINT_ORGANIZATION_ID
, TL.TEXT_INDEX
, ADDRESS_V.CITY INCIDENT_CITY
, ADDRESS_V.COUNTY INCIDENT_COUNTY
, ADDRESS_V.PROVINCE INCIDENT_PROVINCE
, INC.PROJECT_ID
, INC.PROJECT_TASK_ID
FROM CS_INCIDENTS_B_SEC INC
, CS_INCIDENTS_ALL_TL TL
, MTL_SYSTEM_ITEMS_KFV KFV
, HZ_PARTIES PARTY
, (SELECT INC.INCIDENT_ID
, L.ADDRESS1|| DECODE(L.ADDRESS2
, NULL
, NULL
, ';'||L.ADDRESS2) || DECODE(L.ADDRESS3
, NULL
, NULL
, ';'||L.ADDRESS3) || DECODE(L.ADDRESS4
, NULL
, NULL
, ';'||L.ADDRESS4) ADDRESS
, L.CITY
, L.STATE
, L.COUNTRY
, L.PROVINCE
, L.COUNTY
, L.POSTAL_CODE
FROM HZ_PARTY_SITES S
, HZ_LOCATIONS L
, CS_INCIDENTS_B_SEC INC
WHERE S.LOCATION_ID = L.LOCATION_ID
AND INC.INCIDENT_LOCATION_TYPE = 'HZ_PARTY_SITE'
AND INC.INCIDENT_LOCATION_ID = S.PARTY_SITE_ID UNION SELECT INC.INCIDENT_ID
, L.ADDRESS1|| DECODE(L.ADDRESS2
, NULL
, NULL
, ';'||L.ADDRESS2) || DECODE(L.ADDRESS3
, NULL
, NULL
, ';'||L.ADDRESS3) || DECODE(L.ADDRESS4
, NULL
, NULL
, ';'||L.ADDRESS4) ADDRESS
, L.CITY
, L.STATE
, L.COUNTRY
, L.PROVINCE
, L.COUNTY
, L.POSTAL_CODE
FROM HZ_LOCATIONS L
, CS_INCIDENTS_B_SEC INC
WHERE INC.INCIDENT_LOCATION_TYPE = 'HZ_LOCATION'
AND INC.INCIDENT_LOCATION_ID = L.LOCATION_ID) ADDRESS_V
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 INC.INCIDENT_ID = ADDRESS_V.INCIDENT_ID(+)
AND TL.LANGUAGE = USERENV('LANG')
AND INC.CUSTOMER_ID = PARTY.PARTY_ID(+)