Oracle Fusion (Cloud) Reusable SQL Queries

01

of 06

SQL Query to Get Employees Absence Plan Balance

Below SQL query will help you to get accrual balance for any absence plan, just change the plan name for which you need to get the accrual balance.

select papf.person_number,
ppnf.DISPLAY_NAME Person_Name,
houft.NAME LegalEmployer,
aapft.name as Balance_Name,
to_char(appe.enrt_st_dt,'DD/MM/YYYY') Enrollment_Start_Date,
to_char(appe.last_accrual_run,'DD/MM/YYYY') Balance_Calculation_Date,
apae.end_bal Absence_Plan_Balance

From per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_m paam,
per_periods_of_service ppos,
HR_ORGANIZATION_UNITS_F_TL houft,
anc_per_plan_enrollment appe,
anc_absence_plans_f_tl aapft,
anc_per_accrual_entries apae

Where 1=1
and ppnf.person_id = papf.person_id
and ppnf.name_type (+)= 'GLOBAL'
and papf.person_id = paam.person_id
and paam.primary_flag = 'Y'
and paam.assignment_type = 'E'
and paam.effective_latest_change ='Y'
and ppos.period_of_service_id = paam.period_of_service_id
AND ppos.date_start = (select max(date_start)
from per_periods_of_service ppos1
where 1=1
and ppos1.person_id = papf.person_id
and period_type = 'E')
and houft.organization_id = paam.legal_entity_id
and houft.LANGUAGE = 'US'
and appe.Person_id = papf.Person_id
and aapft.absence_plan_id = appe.plan_id
and aapft.LANGUAGE = 'US'
and apae.per_plan_enrt_id = appe.per_plan_enrt_id
and apae.accrual_period = appe.last_accrual_run

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 SYSDATE BETWEEN houft.effective_start_date AND houft.effective_end_date
and SYSDATE between appe.enrt_st_dt and appe.enrt_end_dt

02

of 06

Query to Get Employee’s Default Expense Account

Below is the query to get employees default expense account available on Person management screen.

Employees Default Expense Account Query

select papf.person_number 	"Employee Number",
	   ppnf.display_name	"Employee Name",
	   gl.segment1			"Company", -- Description could be different for you
	   gl.segment2			"Department", -- Description could be different for you,
	   gl.segment3			"Account", -- Description could be different for you,
	   gl.segment4			"Intercompany", -- Description could be different for you,
	   gl.segment5			"Product", -- Description could be different for you,
	   gl.segment1||'.'||gl.segment2||'.'||gl.segment3||'.'||gl.segment4||'.'||gl.segment5 "Default Expense Account" -- You can add more segments also
from per_all_people_f papf,
	per_all_assignments_m paam,
	per_person_names_f  ppnf,
	gl_code_combinations gl              
where 1 = 1
and papf.person_id = paam.person_id
and paam.default_code_comb_id = gl.code_combination_id (+) 
and papf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL' 
and paam.assignment_type = 'E'
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
Order by papf.person_number

Employees expense account from OTBI

You can also get these details from OTBI. Subject area that hold these information is “Expenses – Employee Expense Overview Real Time” and then you can use following columns

• Employee » Employee Number
• Employee » Employee Name
• Employee » Employee Default Expense Account
• Employee » Employee Default Expense Concatenated Segments

03

of 06

Query to Get Employees Area of Responsibility (AOR)

SELECT
pn.DISPLAY_NAME,
pall.PERSON_NUMBER,
asg.ASSIGNMENT_NUMBER,
aor.*
FROM PER_ASG_RESPONSIBILITIES aor
JOIN PER_PERSON_NAMES_F pn ON aor.PERSON_ID=pn.PERSON_ID
JOIN PER_ALL_PEOPLE_F pall ON aor.PERSON_ID=pall.PERSON_ID
JOIN PER_ALL_ASSIGNMENTS_M asg ON aor.PERSON_ID=asg.PERSON_ID
WHERE TRUNC(sysdate) BETWEEN pn.effective_start_date AND pn.effective_end_date
AND pn.NAME_TYPE='GLOBAL'
AND asg.ASSIGNMENT_TYPE IN ('E','C','N','P')
AND TRUNC(sysdate) BETWEEN asg.effective_start_date AND asg.effective_end_date

04

of 06

Query to Get Context Supported by Any Fast Formula

select fftb.base_formula_type_name,
	   fcbc.base_context_name
from   ff_formula_types_b fftb
,      ff_formula_types_tl ffttl
,      ff_ftype_context_usages fftcu
,      ff_contexts_b fcbc
where  fftb.formula_type_id = fftcu.formula_type_id
and    ffttl.formula_type_id = fftb.formula_type_id
and    ffttl.language = userenv('lang')
and    fcbc.context_id = fftcu.context_id
and    upper(fftb.base_formula_type_name) = upper('Global Absence Plan Enrollment Start') -- Change your formula name here

05

of 06

Query to Get Users Account and Role Details

Note this query will fetch detail about user accounts where person record is linked.

select papf.person_number "Person Number",
           ppnf.Display_name "Employee Name",
           pjft.name "Job Name",
           houft.name "Department Name",
           ppos.actual_termination_date "Employee Termination Date",
           pu.username "User Name",
           prdv.role_name "Role Name",
           pur.active_flag "Is Role Active?",
           pur.terminated_flag "Is User Account Terminated?",
           pur.start_date "Role Assigned Date",
           pur.end_date "Role Removed Date",
           pur.created_by "Role Assigned By"
from per_person_names_f ppnf
,per_all_people_f papf
,per_all_assignments_m paam
,per_jobs_f pjf
,per_jobs_f_tl pjft
,hr_all_organization_units_f haouf
,hr_org_unit_classifications_f houcf
,hr_organization_units_f_tl houft
,per_periods_of_service ppos
,per_users pu
,per_user_roles pur
,per_roles_dn_vl prdv
where 1 = 1
and papf.person_id = ppnf.person_id
and papf.person_id = paam.person_id
and paam.job_id = pjf.job_id
and pjf.job_id = pjft.job_id
and paam.primary_assignment_flag = 'Y'
and paam.organization_id = haouf.organization_id
and haouf.organization_id = houcf.organization_id
and haouf.organization_id = houft.organization_id
and papf.person_id = ppos.person_id
and papf.person_id = pu.person_id
and pu.user_id = pur.user_id
and pur.role_id = prdv.role_id
AND houft.language = 'US'
and pjft.language = 'US'
and ppnf.name_type = 'GLOBAL'
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
and trunc(sysdate) between pjft.effective_start_date and pjft.effective_end_date
and trunc(sysdate) between haouf.effective_start_date and haouf.effective_end_date
and trunc(sysdate) between houcf.effective_start_date and houcf.effective_end_date
and trunc(sysdate) between houft.effective_start_date and houft.effective_end_date
and  pu.username = 'Enter Your User Name or Comment This Line'

06

of 06

Query to Get Address of Employee

SELECT 
person_number,          
ppauf.address_type,          
paf.address_line_1,          
paf.address_line_2,          
paf.address_line_3,          
paf.address_line_4,          
paf.building,          
paf.floor_number,          
paf.town_or_city,          
paf.region_1,          
paf.region_2,          
paf.region_3, 		 
per_extract_utility.get_territory_name(paf.country)COUNTRY,          
paf.postal_code     
FROM 
per_all_people_f papf,          
per_person_addr_usages_f ppauf,          
per_addresses_f paf    
WHERE 
papf.person_id = ppauf.person_id(+)          
AND ppauf.address_id = paf.address_id(+)          
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date  AND TRUNC (SYSDATE) BETWEEN ppauf.effective_start_date AND ppauf.effective_end_date          
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date