MCPD on Assignment - get top of stack

Question

We are using the following qualifiers for getting a recent assignment.

paam.primary_flag='Y', paam.assignment_type='E', paam.effective_latest_change='Y', paam.assignment_status_type='ACTIVE', trunc(sysdate) between paam.effective_start_date and paam.effective_end_date

How do you get the top of stack when there are also MCPD on the assignment? What column can I either qualify or do a max statement against?

Thanks.

Nathan CCC · Answer

For example in this query your persons with multiple assignments will float to the top... do you have any with multiple primary active employee assignments at the same point in time?

select all 
count(*) over (partition by null) as n
, count(distinct assignment.assignment_id) over (partition by null, assignment.person_id) as n_assignment_by_person
, count(*) over (partition by null, assignment.person_id) as n_instance_by_person
, row_number() over 
    (
    partition by null
    , assignment.person_id
    order by null
    , assignment.primary_flag desc nulls last -- Y, N
    , assignment.assignment_status_type asc nulls last --Active, Inactive
    , assignment.assignment_number desc nulls last --Employee worker, Contingent worker
    , assignment.assignment_id asc nulls last
    , assignment.effective_start_date desc nulls last
    , assignment.effective_end_date asc nulls last
    , assignment.effective_latest_change desc nulls last --Y,N
    , assignment.effective_sequence desc nulls last
    ) as r_instance_by_person
, to_char(assignment.person_id) as person_id
, to_char(assignment.assignment_id) as assignment_id
, to_char(assignment.effective_start_date,upper('yyyy-mm-dd"t"hh24:mi:ss')) as assignment_effective_start_date
, to_char(assignment.effective_end_date,upper('yyyy-mm-dd"t"hh24:mi:ss')) as assignment_effective_end_date
, assignment.effective_latest_change as assignment_effective_latest_change
, to_char(assignment.effective_sequence) as assignment_effective_sequence
, assignment.primary_flag as primary_flag
, assignment.primary_assignment_flag as primary_assignment
, assignment.primary_work_relation_flag as primary_work_relationship 
, assignment.primary_work_terms_flag as primary_work_terms
, assignment.assignment_status_type as assignment_status_code
, assignment.assignment_type as assignment_type_code --'E', 'C', 'N', 'P' --employee worker, contingent worker, non worker, pending worker
, assignment.assignment_number as assignment_number  
from
per_all_assignments_m assignment 
order by null
, 1 desc nulls last
, 2 desc nulls last
, 3 desc nulls last
, 4 asc nulls last

Nathan CCC · Answer

Hi, Yes a person may have many assignments at the same point it time

and for each assignment id many instances (updates) of that assignment id over time including multiple update in the same day.

Peron assignment is unique on 5 cols;

PER_ALL_ASSIGNMENTS_M_PK

ASSIGNMENT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, EFFECTIVE_LATEST_CHANGE, EFFECTIVE_SEQUENCE

https://docs.oracle.com/en/cloud/saas/human-resources/23a/oedmh/perallassignmentsm-25185.html#perallassignmentsm-25185

but  you have effective_latest_change=y so you do not need the last one EFFECTIVE_SEQUENCE (becasue only 1 sequence exist as the last sequence each day)

and you have sysdate between start and end date

so you have only one instance of each assignment id

so all you need to do is choose between multiple assignment ids for each person id

It is technically possible based on the unique indexes on the tables to have multiple assignment id for a person at the same time that are all primary true and active and the worker type is employee but I think the system has validation rules at data entry to try to prevent this happening (do a query to have a look if you can find any?)

My favourite method to guarantee uniquness is to rank them using aggregate over partition by function in my inner selct 1,2,3,4... then in the outer select add where rank = 1. But using max with a group by will do it too.