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

User_H69RU

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

Vijay Ramani VJ

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