How Costing works in Oracle Payroll?

 What is costing?

Costing in Oracle Payroll R12 helps track payroll expenses. Debits record costs, Credits allocate payments, and balancing ensures they match. This keeps financial records accurate.

Levels of Costing (from lowest to highest priority):

Payroll

Element Link

Organization

Assignment

Element Entry

Types of Costing:

Costed:

· Uses all 5 levels of the costing hierarchy.

Fixed Costed:

· Fix costed works in the same way as costed but it does not use the assignment or organization level costing.

Distributed:

· Used when we want to distribute overhead costs over other elements.

· Requires creation of distribution set of elements

· Assign the distribution set to an element at the element link level.

What is costing/balancing and debit/credit?

Costing: Cost borne by the company

Balancing: Balance the effect to the company

Debit: Costed to company. Money is going out from company account. For e.g. basic salary is paid to an employee, it will be a debited from the company account.

Credit: Reverse of Debit. Money is coming into the company account. For e.g. rent deduction from employee, it will be credit to the company account.

Below table shows the debit/credit based on element classification:

How to enable an input value for Costing?

By default, the input value with name “Pay Value” is enabled for costing, but if the input value name is different then we need to enable the costing at element link level as shown below:

Tables effected in payroll costing:

PAY_PAYROLL_ACTIONS

PAY_ASSIGNMENT_ACTIONS

PAY_RUN_RESULTS

PAY_COSTS

Finally, if we want to fetch the costing details for an element from the run results, below is the query to do so:

SELECT

papf.employee_number,

petf.element_name,

pivf.name,

prrv.result_value,

(

SELECT

decode(

pc.debit_or_credit, ‘D’, ‘Debit’, ‘Credit’

)

|| ‘ : ‘

|| concatenated_segments

FROM

pay_cost_allocation_keyflex pcak,

pay_costs pc

WHERE

pcak.cost_allocation_keyflex_id = pc.cost_allocation_keyflex_id

AND pc.run_result_id = prrv.run_result_id

AND pc.input_value_id = prrv.input_value_id

AND pc.balance_or_cost = ‘C’

) costing_segment,

(

SELECT

decode(

pc.debit_or_credit, ‘D’, ‘Debit’, ‘Credit’

)

|| ‘ : ‘

|| pcak.concatenated_segments

FROM

pay_cost_allocation_keyflex pcak,

pay_costs pc

WHERE

pcak.cost_allocation_keyflex_id = pc.cost_allocation_keyflex_id

AND pc.run_result_id = prrv.run_result_id

AND pc.input_value_id = prrv.input_value_id

AND pc.balance_or_cost = ‘B’

) balancing_segment

FROM

pay_payroll_actions ppa,

pay_assignment_actions paa,

pay_run_results prr,

pay_element_types_f petf,

pay_run_result_values prrv,

per_all_assignments_f paaf,

pay_input_values_f pivf,

per_all_people_f papf

WHERE

ppa.payroll_action_id = paa.payroll_action_id

AND paa.assignment_action_id = prr.assignment_action_id

AND prr.element_type_id = petf.element_type_id

AND paaf.assignment_id = paa.assignment_id

AND papf.person_id = paaf.person_id

AND prr.run_result_id = prrv.run_result_id

AND papf.employee_number =:p_employee_number

AND ppa.effective_date =:p_effective_date

AND prrv.input_value_id = pivf.input_value_id

AND petf.element_name = nvl(petf.element_name,:p_element_name)

AND ppa.effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date

AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date

AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date

AND ppa.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date

AND EXISTS (

SELECT

1

FROM

pay_costs pc

WHERE

pc.input_value_id = prrv.input_value_id

AND pc.run_result_id = prrv.run_result_id

);

SQL query to get SIT values for employees in Oracle Apps HRMS

Below query can be used to fetch value for any Special Information Type (SIT) in Oracle Apps HRMS.

 SELECT

    papf.employee_number,

    papf.full_name,

    ppos.actual_termination_date,

    ppa.date_from,

    ppa.date_to,

    fifsv.id_flex_num,

    (

        SELECT

            fifsv1.segment_name

            || ' : '

            || pac.segment1

        FROM

            per_analysis_criteria        pac1,

            apps.fnd_id_flex_segments_vl fifsv1

        WHERE

            pac1.analysis_criteria_id = pac.analysis_criteria_id

            AND fifsv1.id_flex_num = pac.id_flex_num

            AND fifsv1.application_column_name = 'SEGMENT1'

    ) segment1_value,

    (

        SELECT

            fifsv1.segment_name

            || ' : '

            || pac.segment2

        FROM

            per_analysis_criteria        pac1,

            apps.fnd_id_flex_segments_vl fifsv1

        WHERE

            pac1.analysis_criteria_id = pac.analysis_criteria_id

            AND fifsv1.id_flex_num = pac.id_flex_num

            AND fifsv1.application_column_name = 'SEGMENT2'

    ) segment2_value,

    (

        SELECT

            fifsv1.segment_name

            || ' : '

            || pac.segment3

        FROM

            per_analysis_criteria        pac1,

            apps.fnd_id_flex_segments_vl fifsv1

        WHERE

            pac1.analysis_criteria_id = pac.analysis_criteria_id

            AND fifsv1.id_flex_num = pac.id_flex_num

            AND fifsv1.application_column_name = 'SEGMENT3'

    ) segment3_value,

    (

        SELECT

            fifsv1.segment_name

            || ' : '

            || pac.segment4

        FROM

            per_analysis_criteria        pac1,

            apps.fnd_id_flex_segments_vl fifsv1

        WHERE

            pac1.analysis_criteria_id = pac.analysis_criteria_id

            AND fifsv1.id_flex_num = pac.id_flex_num

            AND fifsv1.application_column_name = 'SEGMENT4'

    ) segment4_value

FROM

    fnd_id_flex_structures_vl fifsv,

    per_analysis_criteria     pac,

    per_person_analyses       ppa,

    per_all_people_f          papf,

    per_periods_of_service    ppos

    where fifsv.ID_FLEX_STRUCTURE_NAME = <SIT Title from the above screenshot>

AND fifsv.id_flex_num = pac.id_flex_num

    AND ppa.person_id = papf.person_id

        AND pac.analysis_criteria_id = ppa.analysis_criteria_id

            AND ppos.person_id = papf.person_id

                AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date

    ; 

The above query is designed to get values for 4 segments. The query can be modified by just adding the highlighted select column to fetch any number of segments.

For e.g. if you need to fetch value for the 5th segment, add below section in the select clause.

   (

        SELECT

            fifsv1.segment_name

            || ' : '

            || pac.segment5

        FROM

            per_analysis_criteria        pac1,

            apps.fnd_id_flex_segments_vl fifsv1

        WHERE

            pac1.analysis_criteria_id = pac.analysis_criteria_id

            AND fifsv1.id_flex_num = pac.id_flex_num

            AND fifsv1.application_column_name = 'SEGMENT5'

    ) segment5_value

For more such queries, kindly drop a comment in the comment section.

Query to fetch detail for Oracle DB Item used in a fast formula in Oracle Payroll

Database Items (FF_DATABASE_ITEMS) query

SELECT

    fdi.user_name       db_item_name,

    (

        SELECT

            frp.parameter_name

            || ' : '

            || frpv.value

        FROM

            ff_route_parameter_values frpv,

            ff_route_parameters       frp

        WHERE

            frpv.user_entity_id = fue.user_entity_id

            AND frp.route_parameter_id = frpv.route_parameter_id

            AND frp.sequence_no = 1

    )                   parameter_value_1,

    (

        SELECT

            frp.parameter_name

            || ' : '

            || frpv.value

        FROM

            ff_route_parameter_values frpv,

            ff_route_parameters       frp

        WHERE

            frpv.user_entity_id = fue.user_entity_id

            AND frp.route_parameter_id = frpv.route_parameter_id

            AND frp.sequence_no = 2

    )                   parameter_value_2,

    fdi.definition_text select_clause,

    fr.text             from_where_clause

FROM

    ff_database_items fdi,

    ff_user_entities  fue,

    ff_routes         fr

where fdi.user_name  = <DB_ITEM_NAME>

AND fue.user_entity_id = fdi.user_entity_id

    AND fr.route_id = fue.route_id;

For more such queries, please drop a comment in the comment section. Thanks !!

How Does Oracle Payroll Run Process works ?

 

What are Payroll Action Types?

Payroll action types are steps in Oracle used during the payroll process. These steps are divided into three parts: before, during, and after the payroll run. Before the payroll, actions like Batch Element Entry and Retro Pay are done. After the payroll, actions like Prepayments and Costing are completed.

The type of actions is stored in a lookup called ACTION_TYPE

In this article, we will focus on the Payroll Run action types that directly process an employee’s pay. These are the main steps that handle all earnings, deductions, and calculate the final salary to be paid to the employee.

What is the Payroll Run process and how is it used in Oracle Payroll? 

The Payroll Run process calculates the gross to net payment for the employees. There are two action types used for payroll run processes.

• Quick Pay
• Run

Quick Pay is used to process payroll run for a single employee whereas Run is used to process payroll run as a batch for multiple employees.

What happens when the payroll process is submitted?

As soon as a payroll run (quick pay or run) process is submitted in Oracle, a record is created in PAY_PAYROLL_ACTIONS table for that specific action type. The action types for Quick Pay and Run are Q and R respectively. For each assignment, a child record is created in PAY_ASSIGNMENT_ACTIONS table.

For e.g. if a Quick Pay is submitted, 1 record will be inserted in PAY_PAYROLL_ACTIONS table with action_type as Q and 1 child record will be inserted in PAY_ASSIGNMENT_ACTIONS table with the assignment_id. These two tables are joined with column payroll_action_id.

 

For Run (multiple employees), there will be multiple records in PAY_ASSIGNMENT_ACTIONS table (1 for each employee) as shown below.

The highlighted number in the Assignment Process Results form is the assignment_action_id

Navigation:
HRMS Manager > View > Assign. Process Results > Provide the assignment number > Find

 Below is the query to find a payroll action details from an assignment_action_id

select ppa.* from
pay_payroll_actions ppa
,pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = 3014246246;

What is a run result?

Each eligible element entry associated with the assignment will be processed during the payroll run (whether Quick Pay or Regular Run) according to the logic and calculations specified in the fast formula. The run result represents the outcome or output of the logical calculation for an earning or deduction.
Like how elements have input values, the run result values store the output corresponding to each specific input value for that element entry.

Navigation:

HRMS Manager > View > Assign. Process Results > HRMS Manager > View > Assign. Process Results > Provide the assignment number > Find > View Results > Run Result Values 

For the previous example of the assignment_action_id, lets say the element getting processed is Basic Salary with two input value namely Monthly Basic Salary and Pay Value. The Monthly Basic Salary input value is the input to the fast formula and the Pay Value stores the output after the calculation.

For each assignment_action_id from PAY_ASSIGNMENT_ACTIONS, multiple run_result_id will be inserted into PAY_RUN_RESULTS table. The number of records inserted will depend on the number of elements processed in the payroll run. For e.g. if the payroll run (Quick Pay or Run) is processing only 1 element (Basic Salary), then only 1 record will be inserted in PAY_RUN_RESULTS table.

Below is the query to find a run result details from an assignment_action_id. This query can be modified to filter based on assignment_number, effective_date or element_name.

SELECT
paaf.assignment_number,
ppa.effective_date,
petf.element_name,
pivf.name,
prrv.result_value
FROM
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_types_f petf,
pay_run_result_values prrv,
per_all_assignments_f paaf,
pay_input_values_f pivf
WHERE
ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id
AND paaf.assignment_id = paa.assignment_id
AND prr.run_result_id = prrv.run_result_id
AND pivf.input_value_id = prrv.input_value_id
— and paaf.assignment_number = :assignment_number
AND paa.assignment_action_id = 3014246246
— and pivf.name = ‘Basic Salary’
— and ppa.effective_date = :effective_date
AND petf.element_name = ‘Basic Salary’
AND ppa.effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
— order by 2 desc
;

Conclusion

When a payroll run process (Quick Pay or Run) is submitted, below are the tables inserted :

PAY_PAYROLL_ACTIONS
PAY_ASSIGNMENT_ACTIONS
PAY_RUN_RESULTS
PAY_RUN_RESULT_VALUES

 

For more such articles, please drop a comment in the comment section.