Wants to get latest record of the person instead of all records in the OTBI report

Question

Summary:

Content (please ensure you mask any confidential information):

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

Nathan CCC · Answer

Anup,

Which subject area is your query? What is the query? Please post the logical sql from the advanced tab as plain text not attachment. Make it the simplest query with least columns you can to provide an example of the problem.

You said person. Not the assignments of the persons who are workers with work relationships between that person and legal entities of type legal employer. So I will assume you are using "Workforce Management - Person Real Time".

https://docs.oracle.com/en/cloud/saas/human-resources/24c/faohb/Workforce-Management--Person-Real-Time-SA-6.html#Workforce_Management__Person_Real_Time_SA_6_d1e18299

The user guide states "This subject area only includes the workers latest personal information as of today."

And in this example query I only get 1 row per person

select all 0 s_0

, "Workforce Management - Person Real Time"."Person"."Person Count" as measure

, cast("Worker"."Person ID" as character) as person_unique_identifier

, "Worker"."Person Number" as person_number
from "Workforce Management - Person Real Time"

order by 1 desc nulls last, 2 desc nulls last, 3 asc nulls last

fetch first 7 rows only

as you can see from your session log "View Log" on page /analytics/saw.dll?issuerawsql

the query gets only the person effective today

(also with the worker assignment effective today with the latest version of that assignment if multiple versions in that day but restricted only to workers of type Employee Worker, Contingent Worker, Pending Worker, Non Worker)

…FROM PER_PERSONS PersonPEO

, PER_ALL_PEOPLE_F PersonDetailsPEO

, PER_ALL_ASSIGNMENTS_M AssignmentPEO

WHERE (PersonPEO.PERSON_ID = PersonDetailsPEO.PERSON_ID

AND PersonPEO.PERSON_ID = AssignmentPEO.PERSON_ID

AND ( DATE'2024-07-25'  BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE AND PersonDetailsPEO.EFFECTIVE_END_DATE)

AND ( DATE'2024-07-25'  BETWEEN AssignmentPEO.EFFECTIVE_START_DATE AND AssignmentPEO.EFFECTIVE_END_DATE))

AND ( ( (AssignmentPEO.EFFECTIVE_LATEST_CHANGE = 'Y' )

)AND ((  ( (AssignmentPEO.ASSIGNMENT_TYPE = 'E' ) )  OR ( (AssignmentPEO.ASSIGNMENT_TYPE = 'C' ) )  OR ( (AssignmentPEO.ASSIGNMENT_TYPE = 'N' ) )  OR ( (AssignmentPEO.ASSIGNMENT_TYPE = 'P' ) )  )) )

SO why do you think you are getting the multiple versions over time? Do your persons have multiple worker assignments effective at the same point in time as at today? Maybe they do 50% of time on assignment A on a contract with legal employer X and 50% of their time on a contract with with legal employer Y on assignment B.