Query to get the contact who have raised an Service Request in Oracle Service CRM. The person who raised an SR may be
1. Customer of
2. Employee of
3. Person
4. Organization -- In this case there will be no entry in cs_hz_sr_contact_points
5. Party Relationship
In cs_hz_sr_contact_points There will be an entry for contact point_type phone and contact_point_type email. It will also have contact_TYPE as PERSON OR PARTY_RELATIONSIP OR EMPLOYEE.
========================Query Begins==================
select inc.incident_number,
(Case when cont.CONTACT_TYPE in ('PERSON','PARTY_RELATIONSHIP') then per.person_first_name
when cont.contact_type='EMPLOYEE' then res.first_name end) as first_name,
(Case when cont.CONTACT_TYPE in ('PERSON','PARTY_RELATIONSHIP') then per.person_last_name
when cont.contact_type='EMPLOYEE' then res.last_name end) as last_name
from cs_incidents_all_b inc
join (select distinct incident_id,party_id,CONTACT_TYPE from cs_hz_sr_contact_points) cont
on inc.incident_id=cont.incident_id
left JOIN HZ_PARTy_relationships rel on rel.party_id=cont.party_id
left JOIN HZ_PARTIES per on per.party_id=
(case when CONTACT_TYPE='PERSON' then cont.party_id else rel.subject_id end)
left join per_all_people_f res on res.person_id=cont.party_id
where incident_number in ('10016729','10016730','10016731','10016732','10016733','10016734')
order by inc.incident_id
========================Query Ends====================
