PAY Hire to Retire Tracker – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Payroll · all Payroll reports

Overview

One row per employee period of service that overlaps the period from Date From to Date To: new hires, leavers and everyone employed in between, with the date of each step from hire to the first payment, the termination dates, and the payroll processing exceptions of the period.

Assignment columns are those of the primary assignment on Date To, or on the termination date for a leaver. New Hire and Leaver mark a hire or termination date within the period.

Payroll Ready Date is the later of the hire date and the dates the payroll and the first approved salary were entered. First Period Cut Off and First Period Pay Date are the cut-off and the regular payment date of the first pay period whose cut-off is on or after the hire date. Payment dates are those of completed cheque, magnetic transfer, cash or manual payments.

Missed Run Date is the date of the payroll’s last run in the period when the assignment was processable on that payroll but was not processed in that pay period or later. Unpaid Prepayment Date and Unpaid Amount are prepayments of the period that no completed payment pays. Process Errors counts payroll processes in error or marked for retry in the period; Error Date, Error Process and Error Message describe the latest.

Retro Pending Date is the earliest reprocess date of a retropay request not yet processed. Run After Termination is the last run date when that run was for a pay period starting after the last standard process date, or after the termination date when there is none.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Business Group, Payroll, Date From, Date To

Oracle EBS Tables Used

per_all_assignments_f, per_assignment_status_types, hr_all_organization_units_vl, per_jobs_vl, hr_locations_all_vl, per_all_people_f, pay_all_payrolls_f, per_pay_bases, per_pay_proposals, per_assignment_status_types_v, hr_soft_coding_keyflex, pay_personal_payment_methods_f, pay_org_payment_methods_f_tl, per_time_periods, pay_message_lines, pay_retro_assignments, per_periods_of_service, pay_pre_payments, pay_assignment_actions, pay_payroll_actions

Report Categories

Enginatics, R12 only

PER Person Assignment Upload, PER Employee Assignments, PAY Gross to Net Summary, PER New Hires, GL Account Distribution Analysis

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import PAY_Hire_to_Retire_Tracker.xml
SQL Source PAY Hire to Retire Tracker.sql
Full Report on Enginatics www.enginatics.com/reports/pay-hire-to-retire-tracker/

Case Study & Technical Analysis: PAY Hire to Retire Tracker

Executive Summary

PAY Hire to Retire Tracker follows each employee period of service that overlaps a chosen date range, from the hire date through the first payroll run and payment, and to the termination and any payroll processing exceptions. It returns one row per employee, with the primary assignment details and the dates of each step in the payroll journey. HR and payroll administrators use it to confirm that new hires were paid on time, to find leavers who were processed after termination, and to investigate payroll errors that affect a particular period of service.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. Periods of service in PER_PERIODS_OF_SERVICE that overlap the date range are joined to their person in PER_ALL_PEOPLE_F and to their business group.
  2. The primary employee assignment in PER_ALL_ASSIGNMENTS_F is joined to each period of service on the period identifier.
  3. Payroll actions and assignment actions in the range are aggregated per assignment into first and last run dates, first and last payment dates, unpaid prepayments and errors.
  4. Each payroll’s last run in the range is aggregated per payroll identifier.
  5. Pay period cut-off and payment dates are found from the time periods of the assignment’s payroll for the first period on or after the hire date.
  6. Error message lines are read from PAY_MESSAGE_LINES for the latest error action.

Parameters & Filtering

Performance & Optimization

FAQ

Q: What does Missed Run Date show? A: It is the date of the payroll’s last run in the period when the assignment was processable on that payroll but was not processed in that pay period or later.

Q: How is Payroll Ready Date calculated? A: For a new hire it is the latest of the hire date, the date the payroll was entered, and the date the approved salary was entered when a salary basis exists.

Q: Which assignment does the report show for a leaver? A: It shows the primary assignment on the termination date, as stated in the report description.

© Enginatics GmbH