ECC Depot Repair, Depot Resolved Service Orders (1)

select
x.*
from
(
select * from (SELECT /*+ use_nl(csdro.temp.cr csdro.temp.cra csdro.temp.cii csdro.temp.actparty
  csdro.temp.party csdro.temp.sr csdro.temp.okh csdro.temp.srcreatedby csdro.temp.rocreatedby) use_nl(csdro.temp.cr csdro.temp.srcp) */
                              srdfv.*,xdfv.*, csdro.* FROM CSD_ECC_REPAIRS_CLOSED_V csdro
                               , (select "ROW_ID" "'SRD_ROW_ID'","CONTEXT_VALUE" "'SRD_CONTEXT_VALUE'","ADDITIONAL_INFO" "'SRD_ADDITIONAL_INFO'","CONCATENATED_SEGMENTS" "'SRD_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 )) srdfv , (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 csdro.language in ('US')  AND csdro.sr_rowid = srdfv."'SRD_ROW_ID'"(+) AND csdro.sr_rowid = xdfv."'SRXT_ROW_ID'"(+) )
                              PIVOT(max(repair_type_name) as repair_type_name,
                                  max(ro_status_meaning) as ro_status_meaning,
                                  max(ro_status_reason_meaning) as ro_status_reason_meaning,
                                  max(approval_status) as approval_status,
                                  max(external_display_status) as external_display_status,
                                  max(flow_status) as flow_status,
                                  max(rework) as rework,
                                  max(ro_escalation) as ro_escalation,
                                  max(repair_item_desc) as repair_item_desc,
                                  max(repair_inventory_org) as repair_inventory_org,
                                  max(repair_owner) as repair_owner,
                                  max(repair_org_name) as repair_org_name,
                                  max(repair_priority) as repair_priority,
                                  max(warranty_status) as warranty_status,
                                  max(warranty_violation) as warranty_violation,
                                  max(instance_usage) as instance_usage,
                                  max(estimate_status) as estimate_status,
                                  max(sr_owner) as sr_owner,
                                  max(disposition) as disposition ,
                                  max(sr_item_description) as sr_item_description,
                                  max(diagnostic_code) as diagnostic_code,
                                  max(diagnostic_code_desc) as diagnostic_code_desc,
                                  max(service_code) as service_code,
                                  max(service_code_desc) as service_code_desc,
                                  max(defect_code_desc) as defect_code_desc,
                                  max(incident_type) as incident_type,
                                  max(incident_severity) as incident_severity,
                                  max(incident_urgency) as incident_urgency,
                                  max(incident_status) as incident_status,
                                  max(sr_summary) as sr_summary,
                                  max(sr_creation_channel) as sr_creation_channel,
                                  max(return_type) as return_type,
                                  max(carrier_name) as carrier_name,
                                  max(processor_name) as processor_name,
                                  max(ship_to_country) as ship_to_country,
                                  max(returned_to_country) as returned_to_country,
                                  max(returned_from_country) as returned_from_country,
                                  max(operating_unit_name) as operating_unit_name,
                                  max(tis_flow_status_meaning) as tis_flow_status_meaning,
                                  max(rma_return_reason_desc) as rma_return_reason_desc,
                                  max(job_status) as job_status
                              for LANGUAGE in ('US' "US"))
) x
where
2=2