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