HRHD – Query to get Service Request Details

Use below query to get service request details from Fusion BIP:

SELECT ssr.sr_id,
ssr.title,
ssr.problem_desc,
to_char(ssr.open_date,’RRRR/MM/DD’) sr_opening_date,
ssr.primary_contact_party_id,
ssr.sr_number,
ssr.status_cd,
(SELECT flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_code = ssr.status_cd
AND flv.lookup_type = ‘ORA_SVC_SR_STATUS_CD’
AND flv.LANGUAGE = ‘US’) sr_status_meaning,
ssr.severity_cd,
(SELECT meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_code = ssr.severity_cd
AND flv.lookup_type = ‘ORA_SVC_SR_SEVERITY_CD’
AND flv.LANGUAGE = ‘US’) sr_severity_meaning,
sc.category_name category,
sq.queue_name queue,
to_char(ssr.last_resource_assign_date,’RRRR/MM/DD’) sr_last_resource_assigned_on
FROM svc_service_requests ssr,
svc_queues sq,
svc_categories sc
WHERE ssr.queue_id = sq.queue_id
AND ssr.category_id = sc.category_id
–AND ssr.status_cd in (‘ORA_SVC_NEW’,’ORA_SVC_INPROGRESS’)