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

Oracle Payroll Actions : Query to identify all payroll actions

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,
ppa.action_status,
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’
) TYPE,
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

Question and answer forum of iavinash.com

Check Also

CWB query to get employee successfully enrolled in the plan

CWB query to get employee successfully enrolled in the plan

CWB Reusable Query There comes scenario when you want to know the number of employee successfully …