ECC Service, Service Request

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, 'Sr Row Id', 'Sr Context Value', 'Sr Additional Info', 'Sr Concatenated Segments', 'Srxt Row Id', 'Srxt Context Value', 'Srxt Item', 'Srxt Serial Number', 'Srxt Customer Name' ...
Imported from Enterprise Command Center
Description: Data set for service and associate contact information
Dataset Key: cs-service
Query Procedure: CS_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: cs_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select
x.*
from
(
select * from (select /*+ leading(srd.PROF srd.inc ) cardinality(srd.inc 1000) */  ECC_SPEC_ID, dfv.*,xdfv.*, INCIDENT_ID, SERVICE_REQUEST_NUMBER,
                INCIDENT_TYPE_ID, INCIDENT_DATE, YEAR_CREATED, QUARTER_CREATED, MONTH_CREATED, WEEK_CREATED,
                OWNER_JOB_TITLE,OWNER_EMAIL, OWNER_PHONE, OWNER_MANAGER_NAME,INCIDENT_OWNER_ID,ASSIGNED_GROUP_EMAIL,UNASSIGNED_FLAG,
                UNASSIGNED_OWNER_FLAG,OWNER_GROUP_ID,ERROR_CODE,INCIDENT_SEVERITY_ID,INCIDENT_LANG,ESCALATED_CODE,CUSTOMER_ID,CUSTOMER,
                CUSTOMER_NUMBER,CUSTOMER_EMAIL_ADDRESS,CUSTOMER_PHONE,CUSTOMER_ADDRESS,ACCOUNT,ACTUAL_RESOLUTION_DATE,DATE_CLOSED,
                INCIDENT_OCCURRED_DATE,RESOLVED_ON,RESPONDED_ON,RESOLUTION_TIME, DAYS_OPEN,HOURS_SINCE_LAST_UPDATE,
                CLOSED_FLAG_CODE,OPEN_FLAG_CODE,YEAR_CLOSED,QUARTER_CLOSED,MONTH_CLOSED,WEEK_CLOSED,RESPONSE_AT_RISK_FLAG,RESOLUTION_AT_RISK_FLAG,
                RESOLUTION_PAST_DUE_FLAG,RESPONSE_PAST_DUE_FLAG,DAYS_SINCE_RESPONSE_PAST_DUE,DAYS_SINCE_RESOLUTION_PAST_DUE,AGE,
                LAST_UPDATE_DATE,DAYS_SINCE_LAST_UPDATE,ESCALATION_NUMBER,ORG_ID,DATE_CREATED,INCIDENT_ADDRESS,PRIMARY_CONTACT_NAME,
                PRIMARY_CONTACT_PHONE,PRIMARY_CONTACT_EMAIL,PRIMARY_CONTACT_PARTY_ID,PRIM_CONTACT_REL_ID,INVENTORY_ITEM_ID,CUSTOMER_PRODUCT_ID,
                ITEM,ITEM_CATEGORY,ITEM_REVISION,ITEM_INSTANCE,SERIAL_NUMBER,TAG_NUMBER,RESPOND_BY,RESOLVE_BY,CONTRACT,
                CONTRACT_SERVICE_ID,COVERAGE_START_DATE,COVERAGE_END_DATE,AT_RISK_FLAG,PAST_DUE_FLAG,ALERT,
                REQUEST_TYPE,SUMMARY, STATUS, INDIVIDUAL_OWNER, ASSIGNED_GROUP, UNASSIGNED, UNASSIGNED_OWNER,CUSTOMER_URGENCY,SEVERITY, ESCALATED,CUSTOMER_TYPE,CUSTOMER_COUNTRY,
                CLOSED_FLAG,OPEN_FLAG,RESPONSE_AT_RISK,RESOLUTION_AT_RISK,RESOLUTION_PAST_DUE,RESPONSE_PAST_DUE,ESCALATION_LEVEL,ESCALATED_STATUS,
                OPERATING_UNIT,ITEM_DESCRIPTION,SYSTEM_NAME,RESOLUTION_TYPE,PROBLEM_TYPE,CONTRACT_STATUS,COVERAGE_NAME,COVERAGE_DESCRIPTION,
                COVERAGE_TYPE,AT_RISK,PAST_DUE,ALERT_STATUS,CONTACT_NAMES,CONTACT_PHONE_NUMBERS,CONTACT_EMAIL_ADDRESSES,NON_PRIMARY_ITEMS,NON_PRIM_ITEM_INSTANCES,
                NON_PRIM_SERIAL_NUMBERS FROM CS_ECC_SERVICE_DTLS_V srd
                 , (select ROW_ID "'SR_ROW_ID'",CONTEXT_VALUE "'SR_CONTEXT_VALUE'",ADDITIONAL_INFO "'SR_ADDITIONAL_INFO'",CONCATENATED_SEGMENTS "'SR_CONCATENATED_SEGMENTS'" from CS_INCIDENTS_ALL_B_DFV) dfv , (select ROW_ID "'SRXT_ROW_ID'",CONTEXT_VALUE "'SRXT_CONTEXT_VALUE'",ITEM "'SRXT_ITEM'",SERIAL_NUMBER "'SRXT_SERIAL_NUMBER'",CUSTOMER_NAME "'SRXT_CUSTOMER_NAME'",CUSTOMER_ADDRESS "'SRXT_CUSTOMER_ADDRESS'",CI_NAME "'SRXT_CI_NAME'",CI_TAG "'SRXT_CI_TAG'",CI_ENVIRONMENT "'SRXT_CI_ENVIRONMENT'",CI_USER_COUNT "'SRXT_CI_USER_COUNT'",CI_IMPACT "'SRXT_CI_IMPACT'",IMPACT "'SRXT_IMPACT'",SPECIAL_INSTRUCTIONS "'SRXT_SPECIAL_INSTRUCTIONS'",CONCATENATED_SEGMENTS "'SRXT_CONCATENATED_SEGMENTS'" from CS_INCIDENTS_ALL_B1_DFV) xdfv  WHERE srd.sr_rowid = dfv."'SR_ROW_ID'"(+) AND srd.sr_rowid = xdfv."'SRXT_ROW_ID'"(+)  AND  INCIDENT_LANG in ('US'))
                PIVOT (
                 MAX(REQUEST_TYPE) AS REQUEST_TYPE,
                 MAX(SUMMARY) AS SUMMARY,
                 MAX(STATUS) AS STATUS,
                 MAX(INDIVIDUAL_OWNER) AS INDIVIDUAL_OWNER,
                 MAX(ASSIGNED_GROUP) AS ASSIGNED_GROUP,
                 MAX(UNASSIGNED) AS UNASSIGNED,
                 MAX(UNASSIGNED_OWNER) AS UNASSIGNED_OWNER,
                 MAX(CUSTOMER_URGENCY) AS CUSTOMER_URGENCY,
                 MAX(SEVERITY) AS SEVERITY,
                 MAX(ESCALATED) AS ESCALATED,
                 MAX(CUSTOMER_TYPE) AS CUSTOMER_TYPE,
                 MAX(CUSTOMER_COUNTRY) AS CUSTOMER_COUNTRY,
                 MAX(CLOSED_FLAG) AS CLOSED_FLAG,
                 MAX(OPEN_FLAG) AS OPEN_FLAG,
                 MAX(RESPONSE_AT_RISK) AS RESPONSE_AT_RISK,
                 MAX(RESOLUTION_AT_RISK) AS RESOLUTION_AT_RISK,
                 MAX(RESOLUTION_PAST_DUE) AS RESOLUTION_PAST_DUE,
                 MAX(RESPONSE_PAST_DUE) AS RESPONSE_PAST_DUE,
                 MAX(ESCALATION_LEVEL) AS ESCALATION_LEVEL,
                 MAX(ESCALATED_STATUS) AS ESCALATED_STATUS,
                 MAX(OPERATING_UNIT) AS OPERATING_UNIT,
                 MAX(ITEM_DESCRIPTION) AS ITEM_DESCRIPTION,
                 MAX(SYSTEM_NAME) AS SYSTEM_NAME,
                 MAX(RESOLUTION_TYPE) AS RESOLUTION_TYPE,
                 MAX(PROBLEM_TYPE) AS PROBLEM_TYPE,
                 MAX(CONTRACT_STATUS) AS CONTRACT_STATUS,
                 MAX(COVERAGE_NAME) AS COVERAGE_NAME,
                 MAX(COVERAGE_DESCRIPTION) AS COVERAGE_DESCRIPTION,
                 MAX(COVERAGE_TYPE) AS COVERAGE_TYPE,
                 MAX(AT_RISK) AS AT_RISK,
                 MAX(PAST_DUE) AS PAST_DUE,
                 MAX(ALERT_STATUS) AS ALERT_STATUS
                 for INCIDENT_LANG in ('US' "US"))
) x
where
2=2