CSD Depot Repair Traveler

SELECT    
   ro.item,
   ro.item_desc,
   ro.serial_number Serial_Num,
   ro.inventory_item_id,
   ro.repair_number Repair_Num,
   rgtl.group_name Repair_Org,
   rstl.resource_name Repair_Owner,
   ro.approval_status_meaning Approval,
   to_char(ro.promise_date, 'DD-MON-YYYY HH24:MI:SS') Promise_Date,
   ro.repair_type_name Repair_Type,
   ro.status_meaning Repair_Status,
   ro.quantity,
   ro.unit_of_measure_tl UOM,
   ro.ro_priority_meaning Priority,
   ro.problem_description Problem_Desc,
   cov.contract_number Contract_Num,
   ro.default_po_num Po_Num,
   inc.incident_number Request_Num,
   decode(INC.CALLER_TYPE, 'ORGANIZATION', PARTY.PARTY_NAME , 'PERSON', PARTY.PERSON_FIRST_NAME || ' ' || PARTY.PERSON_LAST_NAME , FND2.FULL_NAME) 
"Customer",
   decode(SR_CONT.CONTACT_TYPE, 'EMPLOYEE',FND2.FULL_NAME ,'PERSON','','PARTY_RELATIONSHIP',SR_CONT_PARTY.PARTY_NAME ) "Contact",
   ACCOUNT.ACCOUNT_NUMBER Account_Num,
   decode(SR_CONT.CONTACT_TYPE,'PARTY_RELATIONSHIP', PARTY_CONT.PHONE_COUNTRY_CODE|| decode(PARTY_CONT.PHONE_AREA_CODE, '', '',      
        decode(PARTY_CONT.PHONE_COUNTRY_CODE, '', PARTY_CONT.PHONE_AREA_CODE, '-' ||PARTY_CONT.PHONE_AREA_CODE)) || decode(PARTY_CONT.PHONE_NUMBER, '',      
        '',decode(PARTY_CONT.PHONE_AREA_CODE, '', decode(PARTY_CONT.PHONE_COUNTRY_CODE, '', PARTY_CONT.PHONE_NUMBER, '-' || PARTY_CONT.PHONE_NUMBER), '-' ||      
        PARTY_CONT.PHONE_NUMBER)), 'PERSON', PARTY_CONT.PHONE_COUNTRY_CODE || decode(PARTY_CONT.PHONE_AREA_CODE, '', '', decode(PARTY_CONT.PHONE_COUNTRY_CODE,      
        '',PARTY_CONT.PHONE_AREA_CODE, '-' || PARTY_CONT.PHONE_AREA_CODE)) || decode(PARTY_CONT.PHONE_NUMBER, '', '', decode(PARTY_CONT.PHONE_AREA_CODE, '',      
        decode(PARTY_CONT.PHONE_COUNTRY_CODE , '', PARTY_CONT.PHONE_NUMBER , '-' || PARTY_CONT.PHONE_NUMBER ), '-' || PARTY_CONT.PHONE_NUMBER )), 'EMPLOYEE',      
        PERP.PHONE_NUMBER, NULL ) "Contact_Phone",
   decode(SR_CONT.CONTACT_TYPE, 'PARTY_RELATIONSHIP', PARTY_CONT2.email_address, 'PERSON', PARTY_CONT2.email_address, 'EMPLOYEE',PEREMPS.email_address ,NULL) 
"Contact_Email",
   HZLOC3.ADDRESS1 S_ADDR1, 
   HZLOC3.ADDRESS2 S_ADDR2, 
   HZLOC3.ADDRESS3 S_ADDR3, 
   HZLOC3.ADDRESS4 S_ADDR4, 
   HZLOC3.CITY S_CITY,
   HZLOC3.COUNTY S_COUNTY,
   HZLOC3.POSTAL_CODE S_P_CODE,
   HZLOC3.PROVINCE S_PROVINCE,
   HZLOC3.STATE S_STATE,
   HZLOC3.COUNTRY S_COUNTRY,
   HZLOC2.ADDRESS1 B_ADDR1, 
   HZLOC2.ADDRESS2 B_ADDR2, 
   HZLOC2.ADDRESS3 B_ADDR3, 
   HZLOC2.ADDRESS4 B_ADDR4, 
   HZLOC2.CITY B_CITY,
   HZLOC2.COUNTY B_COUNTY,
   HZLOC2.POSTAL_CODE B_P_CODE,
   HZLOC2.PROVINCE B_PROVINCE,
   HZLOC2.STATE B_STATE,
   HZLOC2.COUNTRY B_COUNTRY,
   fnd2.user_name User_Name,
   ro.attribute1,
   ro.attribute2,
   ro.attribute3,
   ro.attribute4,
   ro.attribute5,
   ro.attribute6,
   ro.attribute7,
   ro.attribute8,
   ro.attribute9,
   ro.attribute10,
   ro.attribute11,
   ro.attribute12,
   ro.attribute13,
   ro.attribute14,
   ro.attribute15   
FROM
   FND_USER FND,
   FND_USER FND2,
   CS_INCIDENT_TYPES_VL_SEC TYPE,
   CS_INCIDENT_SEVERITIES_VL SEV,
   CS_INCIDENT_STATUSES_VL STATUS,
   CS_INCIDENT_URGENCIES_B URGENCY_B,
   CS_INCIDENT_URGENCIES_TL URGENCY_T,
   CSI_INSTANCE_STATUSES CPS,
   PER_ALL_PEOPLE_F FND2,
   CSI_ITEM_INSTANCES CII,
   CS_INCIDENTS_VL_SEC INC,
   MTL_SYSTEM_ITEMS_KFV KFV,
   HZ_PARTY_SITES HZSITE1,
   HZ_LOCATIONS HZLOC1,
   HZ_PARTY_SITES HZSITE2,
   HZ_PARTY_SITE_USES HZUSES2,
   HZ_LOCATIONS HZLOC2,
   HZ_PARTY_SITES HZSITE3,
   HZ_PARTY_SITE_USES HZUSES3,
   HZ_LOCATIONS HZLOC3,
   JTF_PARTIES_ALL_V PARTY,
   HZ_PARTIES SR_CONT_PARTY,
   HZ_RELATIONSHIPS HZ_REL,
   HZ_CONTACT_POINTS PARTY_CONT,
   HZ_CONTACT_POINTS PARTY_CONT1,
   HZ_CONTACT_POINTS PARTY_CONT2,
   PER_EMPLOYEES_CURRENT_X PEREMPS,
   JTF_CUST_ACCOUNTS_ALL_V ACCOUNT,
   CS_HZ_SR_CONTACT_POINTS SR_CONT,
   HZ_TIMEZONES HZ_TIME,
   PER_PHONES PERP,
   HR_LOOKUPS HRL,
   AR_LOOKUPS ARL,
   AR_LOOKUPS ARL2,
   AR_LOOKUPS ARL3,
   jtf_rs_groups_tl rgtl,
   jtf_rs_resource_extns_tl rstl,
   oks_ent_coverages_v cov,
   csd_repairs_v ro
WHERE 
    INC.INCIDENT_TYPE_ID = TYPE.INCIDENT_TYPE_ID
AND INC.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
AND INC.CREATED_BY = FND.USER_ID
AND decode(SR_CONT.CONTACT_TYPE, 'EMPLOYEE' , SR_CONT.PARTY_ID, NULL) = FND2.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(FND2.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) 
AND NVL(FND2.EFFECTIVE_END_DATE, TRUNC(SYSDATE+1)) 
AND INC.INCIDENT_URGENCY_ID = URGENCY_B.INCIDENT_URGENCY_ID (+) 
AND URGENCY_T.INCIDENT_URGENCY_ID (+) = URGENCY_B.INCIDENT_URGENCY_ID 
AND URGENCY_T.LANGUAGE (+) = USERENV('LANG') 
AND INC.INCIDENT_SEVERITY_ID = SEV.INCIDENT_SEVERITY_ID 
AND INC.CUSTOMER_PRODUCT_ID = CII.INSTANCE_ID (+) 
AND CII.INSTANCE_STATUS_ID = CPS.INSTANCE_STATUS_ID (+) 
AND INC.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID (+) 
AND KFV.ORGANIZATION_ID (+) = CS_STD.Get_Item_Valdn_Orgzn_Id 
AND INC.CUSTOMER_ID = PARTY.PARTY_ID (+) 
AND INC.ACCOUNT_ID = ACCOUNT.CUST_ACCOUNT_ID (+) 
AND SR_CONT.INCIDENT_ID (+) = INC.INCIDENT_ID 
AND SR_CONT.CONTACT_POINT_ID = PARTY_CONT.CONTACT_POINT_ID (+) 
AND SR_CONT.PARTY_ID = SR_CONT_PARTY.PARTY_ID (+) 
AND SR_CONT.PRIMARY_FLAG (+) = 'Y' 
AND INC.TIME_ZONE_ID = HZ_TIME.TIMEZONE_ID (+) 
AND INC.INSTALL_SITE_USE_ID = HZSITE1.PARTY_SITE_ID(+) 
AND HZSITE1.LOCATION_ID = HZLOC1.LOCATION_ID(+) 
AND INC.BILL_TO_SITE_USE_ID = HZUSES2.PARTY_SITE_USE_ID(+) 
AND HZUSES2.PARTY_SITE_ID = HZSITE2.PARTY_SITE_ID(+) 
AND HZSITE2.LOCATION_ID = HZLOC2.LOCATION_ID(+) 
AND INC.SHIP_TO_SITE_USE_ID = HZUSES3.PARTY_SITE_USE_ID(+) 
AND HZUSES3.PARTY_SITE_ID = HZSITE3.PARTY_SITE_ID(+) 
AND HZSITE3.LOCATION_ID = HZLOC3.LOCATION_ID(+) 
AND PERP.PARENT_ID(+) = DECODE(SR_CONT.contact_type,'EMPLOYEE',SR_CONT.party_id,NULL) 
AND PERP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F' 
AND PERP.PHONE_ID(+) = DECODE(SR_CONT.contact_type,'EMPLOYEE',SR_CONT.contact_point_id,NULL) 
AND ARL.LOOKUP_TYPE (+) = 'PARTY_RELATIONS_TYPE' 
AND ARL.LOOKUP_CODE (+) = HZ_REL.RELATIONSHIP_CODE 
AND ARL2.LOOKUP_TYPE (+) = 'PHONE_LINE_TYPE' 
AND ARL2.LOOKUP_CODE (+) = PARTY_CONT1.PHONE_LINE_TYPE 
AND ARL3.LOOKUP_TYPE (+) = 'PHONE_LINE_TYPE' 
AND ARL3.LOOKUP_CODE (+) = PARTY_CONT.PHONE_LINE_TYPE 
AND HRL.LOOKUP_TYPE (+) = 'PHONE_TYPE' 
AND HRL.LOOKUP_CODE (+) = PERP.PHONE_TYPE 
AND SR_CONT_PARTY.PARTY_ID = HZ_REL.PARTY_ID (+) 
AND HZ_REL.SUBJECT_TABLE_NAME (+)= 'HZ_PARTIES' 
AND HZ_REL.OBJECT_TABLE_NAME (+)= 'HZ_PARTIES' 
AND HZ_REL.DIRECTIONAL_FLAG(+) = 'F' 
AND PARTY_CONT1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' 
AND PARTY_CONT1.OWNER_TABLE_ID(+) = INC.customer_id 
AND PARTY_CONT1.PRIMARY_FLAG (+) = 'Y' 
AND PARTY_CONT1.CONTACT_POINT_TYPE (+) = 'PHONE' 
AND PARTY_CONT1.STATUS (+) = 'A' 
AND PARTY_CONT2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' 
AND PARTY_CONT2.OWNER_TABLE_ID(+) = party_cont.owner_table_id 
AND PARTY_CONT2.PRIMARY_FLAG (+) = 'Y' 
AND PARTY_CONT2.CONTACT_POINT_TYPE (+) = 'EMAIL' 
AND PARTY_CONT2.STATUS (+) = 'A' 
AND PEREMPS.EMPLOYEE_ID(+) = DECODE(SR_CONT.contact_type,'EMPLOYEE',SR_CONT.party_id,NULL)
AND ro.incident_id = inc.incident_id
AND ro.resource_id = rstl.resource_id (+)
AND rstl.category (+) = 'EMPLOYEE'
AND rstl.language (+) = userenv('LANG')
AND ro.owning_organization_id = rgtl.group_id (+)
AND rgtl.language (+) = userenv('LANG')
AND fnd2.user_id = FND_GLOBAL.USER_ID
AND cov.contract_line_id (+) = ro.contract_line_id
AND ro.repair_line_id = :p_repair_line_id