1 Copyright (c) 2023, Oracle. All rights reserved. Oracle Confidential.
Fusion Global Payroll: Query To Link PAY_PAYROLL_ACTIONS, PAY_COSTS And
GL_JE_BATCHES (Doc ID 2730923.1) Modified: Dec 4, 2020 Type: HOWTO
In this Document
Goal Solution References
APPLIES TO:
Oracle Fusion Global Payroll Cloud Service - Version 11.13.20.07.0 and later Information in this document applies to any platform.
GOAL
1. How do we extract Payroll Costing Results from General Ledger tables. That is, how to drill back from GL_JE_BATCHES TO PAY_COSTS table via SQL query. 2. Also, we want an ability to drill down from SLA with the new GL Account code combination back to Payroll tables to identify the employee / Pay element to which the cost in GL belongs to.
SOLUTION
Please find below queries which provide the link between payroll and SLA tables:
--join of pay costs and payroll_actions
SELECT pc.* FROM pay_costs pc, pay_run_results prr, pay_payroll_rel_actions pra, pay_payroll_actions ppa where pc.run_result_id = prr.run_result_id and prr.payroll_rel_action_id = pra.payroll_rel_action_id and pra.payroll_action_id = ppa.payroll_action_id and ppa.action_type in ('Q','R');
--find transfer to SLA rel action
select pra.PAYROLL_REL_ACTION_ID,ppa.* from PAY_PAYROLL_REL_ACTIONS pra, pay_payroll_actions ppa, pay_action_interlocks pai where ppa.payroll_action_id = pra.payroll_action_id and action_type = 'T' and pai.locking_action_id = pra.PAYROLL_REL_ACTION_ID and pai.locked_action_id = <<cost_rel_action_id_from_query1>>;
--pay_xla_events payroll and xla bridge table
select * from pay_xla_events where PAYROLL_REL_ACTION_ID = <<xla_rel_action_id_from_query2>>;
--xla_events table select * from xla_events where EVENT_ID = <<event_id_from_query3>>;
--xla_ae_headers table select * from xla_ae_headers where EVENT_ID = <<event_id_from_query3>>;
--xla_ae_lines table select * from xla_ae_lines where AE_HEADER_ID = <<ae_header_id_from_query5>>; https://support.oracle.com/epmos/faces/DocContentDisplay?_adf.ctrl-state=1cv0u1b5ag_618&id=2730923.1 1/2 15/05/2023, 21:00 Document 2730923.1 -- pay_xla_lines and GL code combinations select xal.*, gcc.* from fusion.xla_ae_lines xal, fusion.xla_ae_headers xah, fusion.gl_code_combinations gcc where xal.ae_header_id = xah.ae_header_id and xal.code_combination_id = gcc.code_combination_id and xah.event_id = <<event_id_from_query3>>;
Note: xla_ae_headers and xla_ae_lines would be populated only when Create accounting is run in final mode Cost Rel action id means payroll_rel_action_id of costed runs (Payroll, quickpay). It would be payroll_rel_action_id from 1st query SLA Rel action id means payroll_rel_action_id of transfer to sla run. It would be payroll_rel_action_id from 2nd query.
===============================================
Below is the query used to link SLA and GL tables:
select a.status, a.* from gl_je_batches a where a.je_batch_id in (
select je_batch_id from where gl_sl_link_id in (select gl_sl_link_id from xla_ae_lines where ae_header_id in (select ae_header_id from xla_ae_headers where event_id=1108997)) )
REFERENCES BUG:29609091 - HOW TO DRILL BACK USING SQL QUERY FROM GL_JE_LINES TO PAY_COSTS TABLE ?
NOTE:2169506.1 - Fusion Global Payroll: How to Obtain List of Employee Payroll Actions and What Are ACTION_TYPE Meanings in PAY_PAYROLL_ACTIONS and PAY_PAYROLL_REL_ACTIONS Tables?
