ECC Depot Repair, Depot Resolved Service Orders

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: csd-repairs-closed
Query Procedure: CSD_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: csd_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
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 CS_INCIDENTS_ALL_B_DFV) 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('CS_INCIDENTS_ALL_B_EXT','CI_NAME',CI_NAME, NULL ,NULL,170) "'SRXT_CI_NAME'",FND_ECC_DFF_UTIL.get_vset_values('CS_INCIDENTS_ALL_B_EXT','CI_TAG',CI_TAG, NULL ,NULL,170) "'SRXT_CI_TAG'",FND_ECC_DFF_UTIL.get_vset_values('CS_INCIDENTS_ALL_B_EXT','CI_ENVIRONMENT',CI_ENVIRONMENT, NULL ,NULL,170) "'SRXT_CI_ENVIRONMENT'",FND_ECC_DFF_UTIL.get_vset_values('CS_INCIDENTS_ALL_B_EXT','CI_USER_COUNT',CI_USER_COUNT, NULL ,NULL,170) "'SRXT_CI_USER_COUNT'",FND_ECC_DFF_UTIL.get_vset_values('CS_INCIDENTS_ALL_B_EXT','CI_IMPACT',CI_IMPACT, NULL ,NULL,170) "'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 CS_INCIDENTS_ALL_B1_DFV) 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