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 |