Oracle Apps HRMS - Query to get employee details
SELECT ppf.employee_number,
ppf.npw_number,
DECODE('G','G',ppf.global_name,ppf.local_name) full_name,
ppf.email_address,
bg.name business_group,
org.name organization,
assignment_number,
DECODE(ppf.current_employee_flag,'Y',1,0) is_employee,
DECODE(ppf.current_npw_flag,'Y',1,0) is_cwk,
paf.primary_flag,
hr_util_misc_ss.getobjectname('JOB',j.job_id,ppf.business_group_id,j.name) job,
hr_util_misc_ss.getobjectname('POSITION',p.position_id,ppf.business_group_id,p.name) position,
hr_util_misc_ss.getobjectname('GRADE', g.grade_id,ppf.business_group_id,g.name) grade,
ltl.location_code,
l.derived_locale,
DECODE(paf.assignment_type, 'C', hr_general.decode_lookup('CWK_ASG_CATEGORY', paf.employment_category), hr_general.decode_lookup('EMP_CAT', paf.employment_category)) assignment_category,
ppp.proposed_salary_n * DECODE(pay_annualization_factor, NULL, 1, 0, 1, pay_annualization_factor) annual_salary,
NULL currency,
pb.name pay_basis,
hr_general.decode_lookup('PAY_BASIS',pb.pay_basis) salary_frequency,
payroll.payroll_name,
DECODE('G','G',sup.global_name,sup.local_name) supervisor,
ppf.person_id person_id,
paf.assignment_id assignment_id,
ppp.pay_proposal_id pay_proposal_id,
(SELECT hr_util_misc_ss.get_in_preferred_currency_str(ppp.proposed_salary_n * DECODE(pay_annualization_factor, NULL, 1, 0, 1, pay_annualization_factor),currency_code,TRUNC(sysdate))
FROM pay_input_values_f ivf,
pay_element_types_f petf,
fnd_currencies_tl c
WHERE pb.input_value_id = ivf.input_value_id
AND ppp.change_date BETWEEN ivf.effective_start_date AND ivf.effective_end_date
AND ivf.element_type_id = petf.element_type_id
AND petf.input_currency_code = c.currency_code
AND c.language = userenv('LANG')
AND ppp.change_date BETWEEN petf.effective_start_date AND petf.effective_end_date
) multi_currency
FROM per_people_f ppf,
per_all_assignments_f paf,
hr_all_organization_units_tl bg,
hr_all_organization_units_tl org,
per_jobs_tl j,
per_grades_tl g,
hr_all_positions_f_tl p,
hr_locations_all l,
hr_locations_all_tl ltl,
per_all_people_f sup,
per_pay_proposals ppp,
per_pay_bases pb,
pay_all_payrolls_f payroll
WHERE 1 = 1
AND paf.person_id(+) = ppf.person_id
AND DECODE(paf.assignment_type(+),'E' ,'T','C','T',NULL, 'T','F') ='T'
AND paf.pay_basis_id = pb.pay_basis_id(+)
AND paf.assignment_id = ppp.assignment_id(+)
AND ppp.pay_proposal_id(+) = hr_mee_views_gen.getAsgProposalId(paf.assignment_id)
AND ppp.approved(+) = 'Y'
AND paf.job_id = j.job_id(+)
AND j.language(+) = userenv('LANG')
AND ppf.business_group_id = bg.organization_id
AND bg.language = userenv('LANG')
AND paf.organization_id = org.organization_id(+)
AND org.language(+) = userenv('LANG')
AND paf.grade_id = g.grade_id(+)
AND g.language(+) = userenv('LANG')
AND paf.position_id = p.position_id(+)
AND p.language(+) = userenv('LANG')
AND paf.location_id = l.location_id(+)
AND paf.location_id = ltl.location_id(+)
AND ltl.language(+) = userenv('LANG')
AND paf.supervisor_id = sup.person_id(+)
AND paf.payroll_id = payroll.payroll_id(+)
AND TRUNC(sysdate) BETWEEN payroll.effective_start_date(+)
AND payroll.effective_end_date(+)
AND trunc(sysdate) BETWEEN paf.effective_start_date(+) AND paf.effective_end_date(+)
AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND trunc(sysdate) BETWEEN sup.effective_start_date(+) AND sup.effective_end_date(+)
AND PPF.PERSON_ID = :1
