SQL Query to Fetch Global Transfer records in Fusion HCM

SQL Query to Fetch Global Transfer records in Fusion HCM 

select

papf.person_number, ppnf.full_name,

to_char(ppos.date_start,'mm/dd/yyyy') Hire_date

,(select hou.name from hr_organization_units hou where organization_id = ppos.LEGAL_ENTITY_ID)  Current_LE

from per_All_assignments_m paam

,per_all_people_F papf

,per_person_names_f ppnf

,PER_PERIODS_OF_SERVICE ppos

where paam.person_id = papf.person_id

and papf.person_id = ppnf.person_id

and ppos.period_of_service_id = paam.period_of_service_id

and ppnf.name_type = 'GLOBAL'

and sysdate between papf.effective_start_date and papf.effective_end_date

and sysdate between ppnf.effective_start_date and ppnf.effective_end_date

and  sysdate  between paam.effective_start_date and paam.effective_end_date

and paam.person_id = papf.person_id

and paam.assignment_status_type = 'ACTIVE' 

and paam.assignment_type = 'E'

and paam.action_code = 'GLB_TRANSFER'

Note :- Do let me know in case of any issue in query.

SQL Query to Fetch Inactive Employees in Fusion HCM

SQL Query to Fetch Inactive Employees in Fusion HCM 

select 

paam.*

from

 per_all_assignments_m paam ,

per_all_people_f  papf 

where 1=1

and papf.person_id = paam.person_id

and paam.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'

and paam.effective_latest_change = 'Y'

and paam.primary_flag = 'Y'

and paam.assignment_type= 'E'

and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

and exists ( select '1' from per_all_assignments_m paam1 where paam1 .assignment_id = paam.assignment_id

             and trunc(sysdate) between paam1 .effective_start_date and paam1 .effective_end_date

and t1.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'

)