Home » Reusable Queries » Oracle Payroll Actions : Query to identify all payroll actions
Oracle Payroll Actions

Oracle Payroll Actions : Query to identify all payroll actions

⚡ Must Read Fusion Article : Oracle Fusion Trees Explained With Example

Oracle Payroll Actions

Following query can be used to retrieve all payroll actions against any assignment. Do not forget to Change the assignment number. “pay_payroll_actions” table holds the value but only the code. Decode function needs to be used to get the meaning.

 Code  Meaning Code Meaning Code Meaning Code Meaning
 R  Payroll Run  B  Balance Adj  I  Balance Init  T  Trasfer to GL
 Q  Quickpay  E  Manual Pay  N  Non Tracked  P  Prepay
 C  Costing  F  Advance Pay  M  BACs  O  Retropay
 D  Void  G  Action Retropay  X  End of YEAR  U  Q Prepay
 A  Cash  H  Cheque  V  Reversal  Z  Purge

SQL query to retrieve oracle payroll actions for any assignments

SELECT paf.person_id, ppa.effective_date, ppa.action_type,
DECODE (ppa.action_type,
‘R’, ‘Payroll Run’,
‘Q’, ‘Quickpay’,
‘C’, ‘Costing’,
‘D’, ‘Void’,
‘A’, ‘Cash’,
‘B’, ‘Balance Adj’,
‘E’, ‘Manual Pay’,
‘F’, ‘Advance Pay’,
‘G’, ‘Action Retropay’,
‘H’, ‘Cheque’,
‘I’, ‘Balance Init’,
‘N’, ‘Non Tracked’,
‘M’, ‘BACs’,
‘X’, ‘End of YEAR’,
‘V’, ‘Reversal’,
‘T’, ‘Trasfer to GL’,
‘P’, ‘Prepay’,
‘O’, ‘Retropay’,
‘U’, ‘Q Prepay’,
‘Z’, ‘Purge’
DECODE (ppa.action_status,
‘C’, ‘Complete’,
‘E’, ‘Error’,
‘I’, ‘Incomplete’,
‘M’, ‘Retry’,
‘P’, ‘Processing’,
‘U’, ‘Unprocessed’
) status,
ppa.payroll_action_id, ppa.action_type, ppa.business_group_id,
ppa.payroll_id, ppa.org_payment_method_id, ppa.payment_type_id,
ppa.action_status, ppa.request_id, ppa.start_date, ppa.date_earned,
ppa.display_run_number, ppa.pay_advice_date, ppa.run_type_id,
ppa.end_date, ppa.future_process_mode
FROM apps.pay_payroll_actions ppa,
apps.pay_assignment_actions paa,
apps.per_assignments_f paf
 WHERE paf.assignment_id = 14142–‘&assignment_id’  AND paf.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
ORDER BY ppa.effective_date

☑ What to read next :
Question and answer forum of
☑ Related Job : Excellent Opportunity for Oracle Applications HRMS Techno functional Consultant at Hyderabad 

Check Also

OTL timecardtimesheet query to fetch submittedapproved hours

OTL timecard/timesheet query to fetch submitted/approved hours

OTL Query Here is the query to get time submitted by any resource (employee as …