ECC Service, Service Request
Description
Categories: Enterprise Command Center
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
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
Run
ECC Service, Service Request and other Oracle EBS reports with Blitz Report™ on our demo environment
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, 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,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,SR_CLOSED_FLAG_CODE , SR_OPEN_FLAG_CODE ,PARTY_TYPE,NOT_ESCALATED,SR_NOT_CLOSED,INSTANCE_COMPONENT,INSTANCE_SUBCOMPONENT,ITEM_COMPONENT,ITEM_SUBCOMPONENT 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 ( select ROWID "ROW_ID",INCIDENT_CONTEXT "CONTEXT_VALUE",INCIDENT_ATTRIBUTE_1 "ADDITIONAL_INFO",INCIDENT_ATTRIBUTE_1 "CONCATENATED_SEGMENTS" from CS_INCIDENTS_ALL_B )) 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'",fnd_ecc_dff_util.get_vset_values_dff('CS_INCIDENTS_ALL_B_EXT','CI_NAME',"CI_NAME",170,"CONTEXT_VALUE") "'SRXT_CI_NAME'",fnd_ecc_dff_util.get_vset_values_dff('CS_INCIDENTS_ALL_B_EXT','CI_TAG',"CI_TAG",170,"CONTEXT_VALUE") "'SRXT_CI_TAG'",fnd_ecc_dff_util.get_vset_values_dff('CS_INCIDENTS_ALL_B_EXT','CI_ENVIRONMENT',"CI_ENVIRONMENT",170,"CONTEXT_VALUE") "'SRXT_CI_ENVIRONMENT'",fnd_ecc_dff_util.get_vset_values_dff('CS_INCIDENTS_ALL_B_EXT','CI_USER_COUNT',"CI_USER_COUNT",170,"CONTEXT_VALUE") "'SRXT_CI_USER_COUNT'",fnd_ecc_dff_util.get_vset_values_dff('CS_INCIDENTS_ALL_B_EXT','CI_IMPACT',"CI_IMPACT",170,"CONTEXT_VALUE") "'SRXT_CI_IMPACT'","IMPACT" "'SRXT_IMPACT'","SPECIAL_INSTRUCTIONS" "'SRXT_SPECIAL_INSTRUCTIONS'","CONCATENATED_SEGMENTS" "'SRXT_CONCATENATED_SEGMENTS'","CI_NAME" "'SRXT_CI_NAME_EDOC'","CI_TAG" "'SRXT_CI_TAG_EDOC'","CI_ENVIRONMENT" "'SRXT_CI_ENVIRONMENT_EDOC'","CI_USER_COUNT" "'SRXT_CI_USER_COUNT_EDOC'","CI_IMPACT" "'SRXT_CI_IMPACT_EDOC'" from ( select ROWID "ROW_ID",EXTERNAL_CONTEXT "CONTEXT_VALUE",EXTERNAL_ATTRIBUTE_11 "ITEM",EXTERNAL_ATTRIBUTE_12 "SERIAL_NUMBER",EXTERNAL_ATTRIBUTE_13 "CUSTOMER_NAME",EXTERNAL_ATTRIBUTE_14 "CUSTOMER_ADDRESS",(DECODE(EXTERNAL_CONTEXT,'Request for Change',EXTERNAL_ATTRIBUTE_2,'Problem',EXTERNAL_ATTRIBUTE_2,'Incident',EXTERNAL_ATTRIBUTE_2,NULL)) "CI_NAME",(DECODE(EXTERNAL_CONTEXT,'Request for Change',EXTERNAL_ATTRIBUTE_7,'Problem',EXTERNAL_ATTRIBUTE_7,'Incident',EXTERNAL_ATTRIBUTE_7,NULL)) "CI_TAG",(DECODE(EXTERNAL_CONTEXT,'Request for Change',EXTERNAL_ATTRIBUTE_3,'Problem',EXTERNAL_ATTRIBUTE_3,'Incident',EXTERNAL_ATTRIBUTE_3,NULL)) "CI_ENVIRONMENT",(DECODE(EXTERNAL_CONTEXT,'Request for Change',EXTERNAL_ATTRIBUTE_4,'Problem',EXTERNAL_ATTRIBUTE_4,'Incident',EXTERNAL_ATTRIBUTE_4,NULL)) "CI_USER_COUNT",(DECODE(EXTERNAL_CONTEXT,'Request for Change',EXTERNAL_ATTRIBUTE_5,'Problem',EXTERNAL_ATTRIBUTE_5,'Incident',EXTERNAL_ATTRIBUTE_5,NULL)) "CI_IMPACT",(DECODE(EXTERNAL_CONTEXT,'Request for Change',EXTERNAL_ATTRIBUTE_1,'Problem',EXTERNAL_ATTRIBUTE_1,'Incident',EXTERNAL_ATTRIBUTE_1,NULL)) "IMPACT",(DECODE(EXTERNAL_CONTEXT,'Technical Problem',EXTERNAL_ATTRIBUTE_10,NULL)) "SPECIAL_INSTRUCTIONS",EXTERNAL_ATTRIBUTE_11||'.'||EXTERNAL_ATTRIBUTE_12||'.'||EXTERNAL_ATTRIBUTE_13||'.'||EXTERNAL_ATTRIBUTE_14||'.'||(DECODE(EXTERNAL_CONTEXT,'Incident',EXTERNAL_CONTEXT||'.'||EXTERNAL_ATTRIBUTE_2||'.'||EXTERNAL_ATTRIBUTE_7||'.'||EXTERNAL_ATTRIBUTE_3||'.'||EXTERNAL_ATTRIBUTE_4||'.'||EXTERNAL_ATTRIBUTE_5||'.'||EXTERNAL_ATTRIBUTE_1,'Problem',EXTERNAL_CONTEXT||'.'||EXTERNAL_ATTRIBUTE_2||'.'||EXTERNAL_ATTRIBUTE_7||'.'||EXTERNAL_ATTRIBUTE_3||'.'||EXTERNAL_ATTRIBUTE_4||'.'||EXTERNAL_ATTRIBUTE_5||'.'||EXTERNAL_ATTRIBUTE_1,'Request for Change',EXTERNAL_CONTEXT||'.'||EXTERNAL_ATTRIBUTE_2||'.'||EXTERNAL_ATTRIBUTE_7||'.'||EXTERNAL_ATTRIBUTE_3||'.'||EXTERNAL_ATTRIBUTE_4||'.'||EXTERNAL_ATTRIBUTE_5||'.'||EXTERNAL_ATTRIBUTE_1,'Technical Problem',EXTERNAL_CONTEXT||'.'||EXTERNAL_ATTRIBUTE_10,NULL)) "CONCATENATED_SEGMENTS" from CS_INCIDENTS_ALL_B )) 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, MAX(INSTANCE_COMPONENT) AS INSTANCE_COMPONENT, MAX(INSTANCE_SUBCOMPONENT) AS INSTANCE_SUBCOMPONENT, MAX(ITEM_COMPONENT) AS ITEM_COMPONENT, MAX(ITEM_SUBCOMPONENT) AS ITEM_SUBCOMPONENT for INCIDENT_LANG in ('US' "US")) ) x where 2=2 |