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
[su_highlight background=”#cd2122″]WHERE paf.assignment_id = 14142–‘&assignment_id'[/su_highlight]
AND paf.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
ORDER BY ppa.effective_date
