Search for:
Jun 23 2024
BIP – Extract Job details with Job Family and Job Function details
SELECT pjf.job_id
     , pjf.job_code
     , pjft.name
     , pjffv.job_family_name
     , pjf.effective_start_date
     , pjf.job_function_code
     , hikm.source_system_id
     , hikm.source_system_owner
  FROM per_jobs_f pjf
     , per_jobs_f_tl pjft
     , per_job_family_f_vl pjffv
     , hrc_integration_key_map hikm
 WHERE pjf.job_id = hikm.surrogate_id
   AND pjft.job_id = pjf.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 trunc(sysdate) between pjffv.effective_start_date and pjffv.effective_end_date
   AND pjft.language = 'US'
   AND pjffv.job_family_id = pjf.job_family_id

List of all job families not associated with a Job:

SELECT *
  FROM per_job_family_f_vl pjffv
 WHERE 1=1
   AND NOT EXISTS (SELECT 1
                     FROM per_jobs_f pjf
		    WHERE pjffv.job_family_id = pjf.job_family_id)
Mandeep Gupta 0 Comments
Dec 20 2021
HDL – Sample file to load Job Model profile data
METADATA|TalentProfile|ProfileId|ProfileCode|Description|PersonId|PersonNumber|Summary|ProfileTypeCode|ProfileTypeId|ProfileStatusCode|ProfileUsageCode|SourceSystemId|SourceSystemOwner
MERGE|TalentProfile||JOB_PROFILE_CODE1|Job Profile for CODE1||||JOB||A|M|TP_JOB_CODE1|HRC_SQLLOADER

METADATA|ProfileRelation|ProfileRelationId|BusinessUnitName|JobCode|JobFamilyName|JobSetCode|LocationCode|LocationSetCode|OrganizationName|PositionCode|ProfileCode|RelationCode|ProfileId|LearningItemNumber|ObjectEffectiveEndDate|ObjectEffectiveStartDate|JobFamilyId|JobId|LearningItemId|LocationId|OrganizationId|PositionId|SourceSystemId|SourceSystemOwner
MERGE|ProfileRelation|||CODE1||COMMON|||||JOB_PROFILE_CODE1|JOB||||2021/01/01|||||||PR_JOB_CODE1|HRC_SQLLOADER

METADATA|ProfileItem|ProfileCode|ContentItem|ContentItemId|ContentType|ContentTypeId|CountryCountryCode|CountryGeographyCode|DateFrom|Importance|InterestLevel|ItemDate1|ItemDate2|RatingModelCode1|RatingModelId1|SectionId|SectionName|DateTo|SourceSystemId|SourceSystemOwner
MERGE|ProfileItem|JOB_PROFILE_CODE1|Process Oriented||COMPETENCY||||2021/01/01|2||||PROFICIENCY|||||PI_JOB_CODE1|HRC_SQLLOADER
Mandeep Gupta 0 Comments
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