BIP – My Public Info Extract

Each person can publish public info in HCM Cloud by navigating to Me -> Quick Actions -> Public Info. There are various sections under Public Info like Public Message, Contact Info, About Me etc.

You can use the below SQL queries to extract the data for relevant sections.

Public Message:

SQL Query:

SELECT papf.person_number
      ,ppnf.full_name
      ,ppmv.PORTRAIT_MESSAGE_CONTENT
      ,ppmv.TIME_FROM
      ,ppmv.TIME_TO
  FROM per_all_people_f papf
      ,per_person_names_f ppnf
      ,per_portrait_messages_vl ppmv
 WHERE papf.person_id = ppnf.person_id
   AND ppnf.name_type = 'GLOBAL'
   AND papf.person_id = ppmv.target_person_id
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

About Me:

SQL Query:

select papf.person_number
      ,ppnf.full_name
      ,hpv.summary about_me
      ,hpkaoe.keywords area_of_expertise
      ,regexp_replace(regexp_replace(regexp_replace(hpkaoe.KEYWORDS, '</p><br/>'), '</p>'),'<p>') as Area_Of_Expert
ise_wo_html
      ,hpkaoi.keywords area_of_interest
      ,regexp_replace(regexp_replace(regexp_replace(hpkaoi.KEYWORDS, '</p><br/>'), '</p>'),'<p>') as area_of_interest_wo_html
 from PER_ALL_PEOPLE_F papf,
      PER_PERSON_NAMES_F ppnf,
      HRT_PROFILES_VL hpv,
      HRT_PROFILE_KEYWORDS hpkaoe,
      HRT_PROFILE_KEYWORDS hpkaoi
where hpv.profile_id = hpkaoe.profile_id
  and hpkaoe.profile_id = hpkaoi.profile_id
  and hpkaoe.keyword_type = 'AOE'
  and hpkaoi. keyword_type = 'AOI'
  and papf.person_id = hpv.person_id
  and papf.person_id = ppnf.person_id
  and ppnf.name_type = 'GLOBAL'
  and TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  and TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date