Search for:
Sep 26 2021
BIP – Query to extract business unit attached to a Job

In multi country HCM Implementations, work structures data can be stored in a COMMON set or a country specific set. In such cases, it becomes important to show the correct business unit name against the jobs in reports.

Use the below report to extract the Job and the business unit:

SELECT BU_ID
      ,BU_NAME
	  ,DEFAULT_SET_ID
	  ,SHORT_CODE
	  ,pjft.name
FROM  PER_JOBS_F pjf
     ,PER_JOBS_F_TL pjft
     ,FUN_ALL_BUSINESS_UNITS_V fabuv
WHERE pjf.SET_ID = fabuv.DEFAULT_SET_ID
and pjf.job_id = pjft.job_id
and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
and trunc(sysdate) between pjft.effective_start_date and pjft.effective_end_date
and pjft.language = 'US'
order by pjft.name

To get more details on SET Name, Set assignments, you can make use of following tables:

FND_SETID_ASSIGNMENTS

FND_SETID_SETS_VL

Mandeep Gupta 0 Comments
Jun 11 2021
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
Mandeep Gupta 0 Comments
Mar 24 2021
Configuration – Extract DFF Setup

Use below query to extract the DFF setups defined in Fusion:

SELECT
 fdsv.descriptive_flexfield_code
      ,fdsv.context_code
      ,fdsv.segment_code
      ,fdsv.name
      ,fdsv.column_name
      ,fvvs.value_set_code
      ,fdsv.display_type
      ,fdsv.prompt
      ,fdsv.short_prompt
      ,fdsv.enabled_flag
      ,fdsv.required_flag
      ,fdsv.read_only_flag
      ,fdsv.description
      ,fdsv.sequence_number
      ,fdsv.derivation_value
      ,fdsv.bi_enabled_flag
  FROM
 fnd_df_segments_vl fdsv,
       fnd_vs_value_sets fvvs
 WHERE fdsv.value_set_id = fvvs.value_set_id(+)
   AND fdsv.descriptive_flexfield_code = 'PER_ASG_DF'
Mandeep Gupta 0 Comments