Oracle Transactional Business Intelligence

Products Banner

Query to find the HRBP Representative of an Employee in HCM Database

Received Response
1343
Views
4
Comments

Summary:

Find a 'HR Business Partner' of an Employee in HCM Database - to create a report on Employee and his/her 'HR Business Partner Representative' details.

Content (required):

I want to find the HRBP and Line Manager of an Employee and trigger them E-mail on his Last Working day. For this requirement, I am looking for a query to find the HRBP of an Employee. I have found the Line Manager but I couldn't able to find the HRBP of an employee. Please help. I'm leaving my incomplete query here.

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

21D

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

SELECT DISTINCT PAPF.PERSON_NUMBER,

        PPNF.DISPLAY_NAME           "EMPLOYEE NAME",

        HAOU_DEPT.NAME             "DEPARTMENT",

        PPNF_MGR.DISPLAY_NAME         "LINE_MANAGER",

        PEA_MGR.EMAIL_ADDRESS AS "LINE MANAGER EMAIL ADDRESS",

        PAAF.ASSIGNMENT_STATUS_TYPE,

        PAAF.ACTION_CODE,

        PAAF.REASON_CODE,

TO_CHAR(POS.ACTUAL_TERMINATION_DATE, 'MM/DD/YYYY') AS Termination_Date,

    TO_CHAR(PAPF.EFFECTIVE_START_DATE, 'MM/DD/YYYY')  AS START_DATE,

PPNF_HR.DISPLAY_NAME AS "HRBP NAME",

PSR.RESPONSIBILITY_TYPE,

PEA_HR.EMAIL_ADDRESS AS "HRBP EMAIL ADDRESS"


FROM  


PER_ALL_PEOPLE_F PAPF

    LEFT OUTER JOIN PER_EMAIL_ADDRESSES PEA

      ON PEA.PERSON_ID = PAPF.PERSON_ID,

PER_PERSON_NAMES_F PPNF,

PER_PERIODS_OF_SERVICE PPOS,


PER_ALL_ASSIGNMENTS_M PAAF 

    LEFT OUTER JOIN PER_PERIODS_OF_SERVICE POS 

      ON POS.PERIOD_OF_SERVICE_ID = PAAF.PERIOD_OF_SERVICE_ID,


HR_ALL_ORGANIZATION_UNITS_F_VL HAOU_DEPT,

PER_ASSIGNMENT_SUPERVISORS_F PASF,


PER_ALL_PEOPLE_F PAPF_MGR

   LEFT OUTER JOIN PER_EMAIL_ADDRESSES PEA_MGR

      ON PEA_MGR.PERSON_ID = PAPF_MGR.PERSON_ID,

PER_PERSON_NAMES_F PPNF_MGR,


PER_ALL_PEOPLE_F PAPF_HR

   LEFT OUTER JOIN PER_EMAIL_ADDRESSES PEA_HR

      ON PEA_HR.PERSON_ID = PAPF_HR.PERSON_ID,

PER_PERSON_NAMES_F PPNF_HR,

PER_ASG_RESPONSIBILITIES PSR


WHERE  

    PAPF.PERSON_ID = PPNF.PERSON_ID

    AND PAPF.PERSON_ID = PPOS.PERSON_ID

    AND PAPF.PERSON_ID = PAAF.PERSON_ID

    AND PPNF.NAME_TYPE = 'GLOBAL'

    AND PPNF.LEGISLATION_CODE = 'IN'

    AND PAAF.ASSIGNMENT_TYPE IN ('E','C')

    AND PAAF.ORGANIZATION_ID = HAOU_DEPT.ORGANIZATION_ID

    AND PAAF.ASSIGNMENT_ID = PASF.ASSIGNMENT_ID

    AND PASF.MANAGER_TYPE = 'LINE_MANAGER'

    AND PASF.MANAGER_ID = PAPF_MGR.PERSON_ID

    AND PAPF_MGR.PERSON_ID = PPNF_MGR.PERSON_ID

    AND PPOS.PERSON_ID = PAPF.PERSON_ID

    AND (PAAF.ACTION_CODE = 'RESIGNATION' OR PAAF.ACTION_CODE = 'RETIREMENT') 

    AND TO_CHAR(PPOS.ACTUAL_TERMINATION_DATE) <= TO_CHAR(SYSDATE)

AND PAPF_HR.PERSON_ID = PPNF_HR.PERSON_ID

    AND PSR.PERSON_ID = PAPF_HR.PERSON_ID



ORDER BY PAPF.PERSON_NUMBER

Answers


  • Try the below:

    SELECT distinct per.person_number "Employee-Person",

           papfc.person_id,

      par.responsibility_type,

      par.responsibility_name,

      papfc.person_number,

      pea.EMAIL_ADDRESS,

      ppnfv.full_name full_name

    FROM

     (SELECT papf.person_id,

     papf.person_number,

     paam.LEGISLATION_CODE,

    (SELECT DISTINCT hlaf.country

     FROM HR_LOCATIONS_ALL_F hlaf

     WHERE hlaf.location_id=paam.location_id

    AND sysdate BETWEEN hlaf.effective_start_date AND hlaf.effective_end_Date) country,

     paam.business_unit_id,

     paam.LEGAL_ENTITY_ID,

     paam.ORGANIZATION_ID,

     paam.location_id,

     paam.POSITION_ID,

     paam.job_id

      FROM per_all_people_f papf,

    per_all_assignments_m paam

      WHERE 1=1 

    AND paam.person_id=papf.person_id

    AND sysdate BETWEEN paam.effective_start_date AND paam.effective_end_date

    AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_Date

    AND paam.EFFECTIVE_LATEST_CHANGE='Y'

    AND paam.ASSIGNMENT_TYPE IN ('E') ) per,

    PER_PERSON_NAMES_F_V ppnfv,

    PER_ASG_RESPONSIBILITIES par,

    per_all_people_f papfc,

    per_email_addresses pea

    WHERE 1=1

     AND papfc.person_id=par.person_id

     --AND per.person_number='76006'

     and sysdate between ppnfv.effective_Start_date and ppnfv.effective_end_Date

     and ppnfv.person_id=papfc.person_id

     AND sysdate BETWEEN papfc.effective_Start_date AND papfc.effective_end_Date

     AND papfc.primary_email_id=pea.email_address_id(+)

     AND (nvl(par.country,NVL(per.country,1))=NVL(per.country,1) OR nvl(par.country,NVL(per.LEGISLATION_CODE,1))=NVL(per.LEGISLATION_CODE,1)) 

     AND nvl(par.business_unit_id,NVL(per.business_unit_id,1))=NVL(per.business_unit_id,1)

     AND nvl(par.LEGAL_ENTITY_ID,NVL(per.LEGAL_ENTITY_ID,1))=NVL(per.LEGAL_ENTITY_ID,1)

     AND nvl(par.ORGANIZATION_ID,NVL(per.ORGANIZATION_ID,1))=NVL(per.ORGANIZATION_ID,1)

     AND nvl(par.location_id,NVL(per.location_id,1))=NVL(per.location_id,1)

     AND nvl(par.POSITION_ID,NVL(per.POSITION_ID,1))=NVL(per.POSITION_ID,1)

     AND nvl(par.job_id,NVL(per.job_id,1))=NVL(per.job_id,1)

     AND par.responsibility_type IN ('HR_REP','NBTY_HRIS_MANAGER','NBTY_SVP_HR','PAY_REP')

     AND par.END_DATE IS NULL

    ORDER BY par.responsibility_type,

    papfc.person_number

  • Go to a specific user's employment record and on the right side, you will see a small headphone icon and click on it.