Fusion HCM Cloud OTBI - analysis failes to return data/blank column, for SA Workforce Management - Worker Assignment Real Time, where assignment type is 'ET' (Employment Terms)

(Doc ID 1941712.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

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

Symptoms

Fusion HCM Cloud OTBI - custom analysis failes to return data where assignment type is 'ET' (Employment Terms)

OTBI Report using

subject area 'Workforce Management - Worker Assignment Real Time' with
column: 'Projected Assignment End' in Worker Assignment Details

Unexpected behavior
--------------------------------

The Person Number column is populated but the Projected Assignment End column is blank

Steps:
-----------
In the Manage Employment screen, for the Person number 10000122, can see the Projected End Date set to 30/10/2014

Execute the following simple analysis:

set variable LOGLEVEL = 2;SELECT
   0 s_0,
   "Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."Projected Assignment End" s_1,    <<<<<<<<<<<<<<<<
   "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" s_2
FROM "Workforce Management - Worker Assignment Real Time"
WHERE
("Worker"."Person Number" = '10000122')
ORDER BY 1, 3 ASC NULLS LAST, 2 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

is not pulling through the projected end date for an FTC employee.

Other findings:

In the database the value is stored in PROJECTED_ASSIGNMENT_END of PER_ALL_ASSIGNMENTS_M where the assignment type is 'ET' (Employment Terms) however the SQL issued is only pulling back the row in the assignment table where the type is 'E'.

review nqquery.log shows the values that are being selected and ET is not one of them:

.....

AND ( ( ((  (
(AssignmentPEO.ASSIGNMENT_TYPE = 'E' ) )  OR (
(AssignmentPEO.ASSIGNMENT_TYPE = 'C' ) )  OR (
(AssignmentPEO.ASSIGNMENT_TYPE = 'N' ) )  OR (
(AssignmentPEO.ASSIGNMENT_TYPE = 'P' ) )  ))  AND (
(AssignmentPEO.EFFECTIVE_LATEST_CHANGE = 'Y' ) ) )  AND ((( 1 = 2 ) OR
(1=1))....

does not check for 'ET'

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