Query to find SR's assigned to specific user in Oracle Apps R12

Query:

select * from
(SELECT cia.incident_number       SR_NUMBER
,       papf.full_name             SR_Person
,       fu.user_name               SR_user
,       'OWNER'                    Record_Type
,       cia.INCIDENT_DATE          SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       NULL                       old_status
,       cidv.NEW_STATUS_NAME       current_status
,       cidv.NEW_TYPE_NAME         SR_type
,       'NULL'   Notes
,       cidv.INCIDENT_DATE        Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all          cia
,       jtf_rs_resource_extns     jrre
,       per_all_people_f          papf
,       fnd_user                  fu
,       CS_INCIDENTS_DIARY_V      cidv
WHERE   cia.INCIDENT_OWNER_ID = jrre.RESOURCE_ID
AND     jrre.SOURCE_ID        = papf.person_id
AND     papf.person_id        = fu.employee_id
AND     cia.incident_id       = cidv.incident_id
AND     cidv.INCIDENT_OWNER_FLAG = 'Y'
AND     cidv.INCIDENT_TYPE_FLAG  ='Y'
UNION
select  cia.incident_number SR_NUMBER
,       papf.full_name     SR_Person
,       cidv.owner         SR_Owner
,       'STATUS UPDATE'       Record_Type
,       cia.INCIDENT_DATE  SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       cidv.OLD_STATUS_NAME  old_status
,       cidv.NEW_STATUS_NAME  current_status
,       cidv.NEW_TYPE_NAME   SR_type
,       'NULL'   Notes
,       cidv.INCIDENT_DATE  Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all          cia
,       CS_INCIDENTS_DIARY_V      cidv
,       CS_INCIDENT_STATUSES_tL   cstt
,       CS_INCIDENT_SEVERITIES_tl cist
,       fnd_user                  fu
,       per_all_people_f          papf
WHERE   cia.incident_id            = cidv.incident_id
AND     cidv.INCIDENT_OWNER_FLAG  != 'Y'
AND     cidv.OLD_STATUS_NAME      != cidv.NEW_STATUS_NAME
AND     cia.INCIDENT_STATUS_ID     = cstt.INCIDENT_STATUS_ID
AND     cstt.language              = 'US'
AND     cia.INCIDENT_SEVERITY_ID   = cist.INCIDENT_SEVERITY_ID
AND     cist.language              = 'US'
AND     cidv.owner                = fu.USER_NAME
AND     fu.employee_id            = papf.person_id
UNION
select  cia.incident_number SR_NUMBER
,       papf.full_name     SR_Person
,       fu.user_name       SR_Owner
,       'NOTE'             Record_Type
,       cia.INCIDENT_DATE  SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       NULL  old_stus
,       cist.name  current_status
,       citt.name   SR_type
,       jnv.NOTES Notes
,       jnv.ENTERED_DATE  Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all cia
,       JTF_NOTES_VL  jnv
,       cs_incident_types_tl citt
,       cs_incident_statuses_tl cist
,       fnd_user      fu
,       per_all_people_f   papf
WHERE   cia.incident_id     = jnv.source_object_id
AND     jnv.SOURCE_OBJECT_CODE ='SR'
and     cia.INCIDENT_TYPE_ID = citt.INCIDENT_TYPE_ID
AND     citt.language              = 'US'
AND     cia.INCIDENT_STATUS_ID = cist.INCIDENT_STATUS_ID
AND     cist.language              = 'US'
AND     jnv.created_by      = fu.user_id
AND     fu.employee_id =papf.person_id
UNION
SELECT  incident_number            SR_NUMBER
,       papf.full_name             SR_Person
,       fu.user_name               SR_user
,       'RE ASSIGN'                Record_Type
,       cia.INCIDENT_DATE          SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       cidv.OLD_STATUS_NAME       old_status
,       cidv.NEW_STATUS_NAME       current_status
,       cidv.NEW_TYPE_NAME         SR_type
,       NULL                Notes
,       cidv.INCIDENT_DATE         Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all          cia
,       jtf_rs_resource_extns     jrre
,       per_all_people_f          papf
,       fnd_user                  fu
,       CS_INCIDENTS_DIARY_V      cidv
,       jtf_rs_resource_extns     old_res
,       per_all_people_f          old_per
,       fnd_user                  old_user
WHERE   cia.INCIDENT_OWNER_ID = jrre.RESOURCE_ID
AND     jrre.SOURCE_ID        = papf.person_id
AND     papf.person_id        = fu.employee_id
AND     cia.incident_id       = cidv.incident_id
AND     cidv.OWNER_OLD        != OWNER_NEW
AND     cidv.OWNER_OLD        = old_res.RESOURCE_ID
AND     old_res.SOURCE_ID     = old_per.person_id
AND     old_per.person_id     = old_user.employee_id
AND     cidv.INCIDENT_OWNER_FLAG = 'Y'
)
where SR_user = 'SATISHV'
--AND   trunc(Date1) > trunc(sysdate-365)
order by SR_NUMBER
;







Query Credits : Siva Gollapalle