Fusion Apps OTBI - Termination Analysis based on "Workforce Management - Worker Assignment Event Real Time" SA is pulling all the termination lines for employees. (Doc ID 1586040.1)

Last updated on SEPTEMBER 25, 2013

Applies to:

Oracle Fusion Global Human Resources - Version 11.1.5.0.0 and later
Information in this document applies to any platform.

Symptoms

On : 11.1.5.0.0 version, Technology Management - BI & Reporting

You created a Termination Report,  based on "Workforce Management - Worker Assignment Event Real Time", in order to reflect the terminated employees.
The report is returning all of the employee's termination lines.
For instance, an employee could be terminated more than once. Hence, there will be more than one termination line.
How can you limit the results to show only the latest termination line.

Here is a sample of the logical sql used in the report:

SELECT
   0 s_0,
   "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Type" s_1,
   "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Effective End Date" s_2,
   "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Effective Start Date" s_3,
   "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Primary Assignment Flag" s_4,
   "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Primary Flag" s_5,
   "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Reason Code" s_6,
   "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."System Person Type" s_7,
   "Workforce Management - Worker Assignment Event Real Time"."Department"."Department Name" s_8,
   "Workforce Management - Worker Assignment Event Real Time"."HR Action"."Action Code" s_9,
   "Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Name" s_10,
   "Workforce Management - Worker Assignment Event Real Time"."Worker"."Person Number" s_11
FROM "Workforce Management - Work Relationship Real Time"
    WHERE
(("Workforce Management - Worker Assignment Event Real Time"."HR Action"."Action Code" <> 'GLB_TRANSFER')
    AND ("Workforce Management - Worker Assignment Event Real Time"."HR Action"."Action Code" <> 'REHIRE')
    AND ("Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Status Type" = 'Inactive')
    AND ("Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Primary Flag" = 'Yes')
    AND ("Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Primary Assignment Flag" = 'Yes')
    AND ("Workforce Management - Worker Assignment Event Real Time"."HR Action"."Action Code" <> 'ASG_CHANGE'))
ORDER BY 1,
     12 ASC NULLS LAST,
     11 ASC NULLS LAST,
     9 ASC NULLS LAST,
     10 ASC NULLS LAST,
     6 ASC NULLS LAST,
     8 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms