Search for:
Jun 26 2023
BIP – Extract Job Details

Once Jobs data is loaded/created, you can use below SQL to extract the setup data for Jobs:

Select pjft.name
      ,pjf.job_code
	  ,TO_CHAR(pjf.effective_start_date,'YYYY/MM/DD') start_date
      ,TO_CHAR(pjf.effective_end_date,'YYYY/MM/DD') end_Date
      ,(select add_set.set_code
          from fnd_setid_sets add_set
         where add_set.set_id = pjf.set_id
		   and add_set.language = USERENV('LANG')) set_code
      ,pjf.active_status
  from per_jobs_f pjf,
       per_jobs_f_tl pjft
 where 1=1
   and pjf.job_id = pjft.job_id
   and pjft.language = USERENV('LANG')
   and trunc(SYSDATE) between pjft.effective_start_date and pjft.effective_end_date
   and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
ORDER BY 2,4
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