Query to find employee salary details in Oracle Fusion

Query to find employee salary details in Oracle Fusion

Hi friends, we are going to discuss about the Query to find employee salary details in Oracle Fusion. We will share the detail sql query which helps to extract the complete employee details with its salary information's in oracle fusion . In oracle Fusion HCM employee salary is divided into multiple elements. So if we are thinking to find out the complete employee salary details information's then we need to add multiple salary components or elements to create the complete salary amount for the employee. We will also try to share the most important tables too which holds the employee salary related details in oracle fusion. This query is very very important if you are thinking to develop the employee salary report in oracle fusion. This is one of the most important employee query in oracle fusion HCM. Please find below the complete detail about the Query to find employee salary details in Oracle Fusion.

Query to find employee salary details in Oracle Fusion

Top 5 employee salary tables in oracle fusion

Here below is some of the important employee salary tables in oracle fusion.

1.CMP_ASG_SALARY_V 

2.CMP_ASG_SALARY_COMPONENTS_V

3.CMP_BASIS_COMPONENTS

4.CMP_BASIS_PAY_RATES

5.CMP_BASIS_SIMPLE_COMPONENTS

Detail SQL Query to find employee salary details in Oracle Fusion

Here below is the detail sql query which helps to extract the complete salary details in oracle fusion.

If you want to query the CMP_SALARY table directly, please note that the Salary is tied to the person's assignment, so it should be joined by ASSIGNMENT_ID to the assignments table and from there to the persons table.

Query 1:- 

select person_id from per_all_people_f where person_number = '[PERSON_NUMBER]'

Query 2:-

select assignment_id, assignment_number from per_all_assignments_m where person_id in ([PERSON_ID from Q1])

Query 3:-

select * from cmp_salary where assignment_id in ([ASSIGNMENT_ID from Q2])

Query 4:- 

select csc.salary_id,

csc.salary_component_id,

csa.salary_basis_id,

csa.currency_code,

csb.name "SALARY_BASIS_NAME",

csa.assignment_id,

csa.date_from,

csa.date_to,

hlk.display_sequence,

csc.component_reason_code,

hlk.meaning as component_name,

csc.change_amount as component_amount,

csc.change_percentage as component_percentage

from cmp_salary_components csc,

cmp_salary csa,

hcm_lookups hlk,

cmp_salary_bases csb

where csc.salary_id = csa.salary_id

and hlk.lookup_type ='CMP_SALARY_COMPONENTS'

and csa.salary_basis_id = csb.salary_basis_id

and hlk.lookup_code = csc.component_reason_code

Query to find employee salary details in Oracle Fusion