Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

MCPD on Assignment - get top of stack

Received Response
65
Views
2
Comments

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.

Tagged:

Answers

  • Rank 7 - Analytics Coach
    edited Jul 20, 2023 4:43PM

    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.

  • Rank 7 - Analytics Coach

    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
    
    
    

Welcome!

It looks like you're new here. Sign in or register to get started.