Employee Primary Assignment Line Manager Details

This query returns the current line manager for an employee’s primary assignment in Oracle Fusion HCM. It uses current effective-dated assignment, supervisor, and person-name records, and keeps only primary employee assignments. Oracle documents that assignments include manager information, that supervisor relationships are stored in PER_ASSIGNMENT_SUPERVISORS_F, and that an assignment must have at least one line manager.
categories  HCM Core HR
back Back to Vault
xls Download Sample Report for Employee Primary Assignment Line Manager Details
SQL Download SQL Query for Employee Primary Assignment Line Manager Details

CSV SQL Query for Employee Primary Assignment Line Manager Details

Copy 
SELECT
    PAPF.PERSON_NUMBER           AS Person_Number,
    PPNF.FULL_NAME               AS Supervisor_Name
FROM
    PER_ALL_PEOPLE_F             PAPF,
    PER_ALL_ASSIGNMENTS_M        PAAM,
    PER_ASSIGNMENT_SUPERVISORS_F PASF,
    PER_PERSON_NAMES_F           PPNF
WHERE
        PAPF.PERSON_ID           = PAAM.PERSON_ID
    AND PAAM.PRIMARY_ASSIGNMENT_FLAG = 'Y'
    AND PAAM.ASSIGNMENT_TYPE     = 'E'
    AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
    AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
    AND PAPF.PERSON_ID           = PASF.PERSON_ID
    AND PASF.MANAGER_TYPE        = 'LINE_MANAGER'
    AND PPNF.PERSON_ID           = PASF.MANAGER_ID
    AND PPNF.NAME_TYPE           = 'GLOBAL'
    AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE AND PASF.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
ORDER BY
    PAPF.PERSON_NUMBER ASC,
    PPNF.FULL_NAME ASC NULLS FIRST

Employee Primary Assignment Line Manager Details in BI Connector

Try BI Connector  back
back Back to Vault

Can’t find the report you need?

Want to tweak one to fit your business logic?

Contact Sales
Banner