Search for:
Jun 23 2024
BIP – Query to get Job Profile Details
SELECT pjf.job_code
      ,hpb.profile_code
      ,hpt.description profile_desc
      ,hpt.summary
      ,hpeiv.DESCRIPTION
      ,hpeiv.RESPONSIBILITIES
      ,hpeiv.QUALIFICATIONS
  FROM HRT_PROFILE_ITEMS hpi
      ,HRT_PROFILES_B hpb
      ,HRT_PROFILES_TL hpt
      ,HRT_PROFILE_RELATIONS hpr
      ,HRT_PROFILE_EXTRA_INFO_VL hpeiv
      ,PER_JOBS_F pjf
 WHERE hpi.profile_id = hpb.profile_id
   AND hpb.profile_usage_code = 'M'
   AND hpi.profile_id = hpr.profile_id
   AND hpi.profile_id = hpt.profile_id
   AND hpi.profile_id = hpeiv.profile_id
   AND pjf.job_id = hpr.object_id
   AND trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_Date
   AND pjf.job_code= '1099'
   AND hpt.language = 'US'
Mandeep Gupta 0 Comments
May 28 2023
BIP – Query to find list of jobs without job profiles

There is no easy way in OTBI to find list of jobs which don’t have a model profile associated with them. This can be achieved easily using BIP query.

Sample Query:

SELECT * FROM PER_JOBS_F pjf
 WHERE trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_Date
   AND NOT EXISTS ( SELECT 1
                      FROM HRT_PROFILE_ITEMS hpi
                          ,HRT_PROFILES_B hpb
                          ,HRT_PROFILE_RELATIONS hpr
                     WHERE hpi.profile_id = hpb.profile_id
                       AND hpb.profile_usage_code = 'M'
                       AND hpi.profile_id = hpr.profile_id
                       AND pjf.job_id = hpr.object_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