ECC Service, Service Request

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