ECC Service, Service Request, SQL1

Description
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 ECC
select * from (select 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 'SR_ROW_ID','SR_CONTEXT_VALUE','SR_ADDITIONAL_INFO','SR_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT_VALUE,ADDITIONAL_INFO,CONCATENATED_SEGMENTS from CS_INCIDENTS_ALL_B_DFV) dfv , (select 'SRXT_ROW_ID','SRXT_CONTEXT_VALUE','SRXT_ITEM','SRXT_SERIAL_NUMBER','SRXT_CUSTOMER_NAME','SRXT_CUSTOMER_ADDRESS','SRXT_CI_NAME','SRXT_CI_TAG','SRXT_CI_ENVIRONMENT','SRXT_CI_USER_COUNT','SRXT_CI_IMPACT','SRXT_IMPACT','SRXT_SPECIAL_INSTRUCTIONS','SRXT_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT_VALUE,ITEM,SERIAL_NUMBER,CUSTOMER_NAME,CUSTOMER_ADDRESS,CI_NAME,CI_TAG,CI_ENVIRONMENT,CI_USER_COUNT,CI_IMPACT,IMPACT,SPECIAL_INSTRUCTIONS,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"))