Introduction:
This SQL query is used to fetch the data of Employees Salary details (CTC)like Annual salary, Fixed CTC, Employee number, DOJ, Base element name etc ..
Cause of the issue:
Business wants a report that contains Annual salary details of an employee from Fusion HCM module. So, business wants to create a custom report to pull the required data.
How do we solve:
Create a report in BI Publisher using below SQL query to extract the CTC details.
SQL Query:
SELECT distinct
papf.person_number emp_number,
name_g.display_name em_name,
papf.start_date doj,
cs.date_from,
cs.salary_amount fixed_ctc_pm,
cs.annual_salary,
cs.annual_ft_salary total_ctc_pa,
petv.base_element_name,
peev.screen_entry_value
FROM
cmp_salary cs,
per_all_people_f papf,
per_person_names_f name_g,
per_all_assignments_f paaf,
pay_element_entries_f peef,
pay_element_types_vl petv,
pay_element_entry_values_f peev,
pay_input_values_vl piv
WHERE
cs.person_id = papf.person_id
AND cs.assignment_type = ‘E’
AND trunc(sysdate) BETWEEN nvl(papf.effective_start_date, trunc(sysdate)) AND nvl(papf.effective_end_date, trunc(sysdate) + 1
)
AND name_g.person_id = papf.person_id
AND trunc(sysdate) BETWEEN nvl(name_g.effective_start_date, trunc(sysdate)) AND nvl(name_g.effective_end_date, trunc(sysdate)
+ 1)
AND name_g.name_type = ‘GLOBAL’
AND papf.person_id = paaf.person_id
AND paaf.primary_flag = ‘Y’
AND paaf.assignment_type = ‘E’
AND trunc(sysdate) BETWEEN nvl(paaf.effective_start_date, trunc(sysdate)) AND nvl(paaf.effective_end_date, trunc(sysdate) + 1
)
AND papf.person_id = peef.person_id
AND peef.element_type_id = petv.element_type_id
AND trunc(sysdate) BETWEEN nvl(peef.effective_start_date, trunc(sysdate)) AND nvl(peef.effective_end_date, trunc(sysdate) + 1
)
AND peef.element_entry_id = peev.element_entry_id (+)
AND peef.object_version_number = peev.object_version_number (+)
AND peev.input_value_id = piv.input_value_id
AND piv.uom = ‘M’
AND piv.base_name = ‘Amount’
