Reusable Queries

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,
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

Show More
Back to top button

Adblock Detected

Please consider supporting us by disabling your ad blocker