Position Data Query

 with Position_FTE as (SELECT Positions.position_id position_id

      ,Positions.FTE Position_Current_FTE

      ,Positions.INCUMBENT_FTE      CurrentIncumbentFTE

      ,(Positions.FTE - Positions.INCUMBENT_FTE)      Difference_FTE

  FROM   

(SELECT HAPFT.NAME,

HAPFT.position_id,

        HAPF.FTE, 

    (select SUM(PAWMF.VALUE)

           from PER_ALL_ASSIGNMENTS_M PAAM,

                PER_ASSIGN_WORK_MEASURES_F PAWMF

  where 1=1

            AND PAAM.POSITION_ID = HAPF.POSITION_ID 

            AND SYSDATE  BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE

AND SYSDATE  BETWEEN PAWMF.EFFECTIVE_START_DATE AND PAWMF.EFFECTIVE_END_DATE

            AND PAAM.ASSIGNMENT_TYPE = 'E'

    AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

            AND PAAM.ASSIGNMENT_ID = PAWMF.ASSIGNMENT_ID

            AND PAWMF.UNIT = 'FTE') AS INCUMBENT_FTE

   FROM HR_ALL_POSITIONS_F HAPF, 

        HR_ALL_POSITIONS_F_TL HAPFT

  WHERE HAPF.POSITION_ID = HAPFT.POSITION_ID 

    AND USERENV('LANG') = HAPFT.LANGUAGE 

    AND TRUNC(SYSDATE)  BETWEEN HAPF.EFFECTIVE_START_DATE AND HAPF.EFFECTIVE_END_DATE

    AND TRUNC(SYSDATE)  BETWEEN HAPFT.EFFECTIVE_START_DATE AND HAPFT.EFFECTIVE_END_DATE

  ORDER BY HAPFT.NAME ) Positions)

SELECT 

  HLPF.position_id, 

  HLPF.position_code, 

  HLPF.effective_start_date, 

  HLPF.effective_end_date, 

  HLPF.location_id, 

  HLPF.active_status, 

  HLPF.supervisor_id, 

  HLPF.supervisor_assignment_id, 

  HLPF.permanent_temporary_flag, 

  HLPF.fte, 

  HLPF.hiring_status, 

  HLPF.full_part_time, 

  HLPF.standard_working_frequency AS FREQUENCY, 

  HLPF.position_type, 

  HLPF.working_hours, 

  HLPF.overlap_allowed, 

  HLPF.seasonal_flag AS SEASONAL, 

  HLPF.NAME, 

  HLPF.budgeted_position_flag AS BUDGETED_POSITION, 

  HLPF.assignment_category, 

  --HLPF.entry_grade_id AS ENTRY_GRADE, 

  HLPF.standard_working_frequency, 

  HLPF.standard_working_hours, 

  --HLPF.grade_ladder_id, 

  (

    SELECT 

      haou.NAME 

    FROM 

      hr_all_organization_units haou 

    WHERE 

      haou.organization_id = hlpf.business_unit_id

  ) BU_NAME, 

  hlpf.organization_id DEPT_ID, 

  (

    SELECT 

      haou.NAME 

    FROM 

      hr_all_organization_units haou 

    WHERE 

      haou.organization_id = hlpf.organization_id

  ) DEPT_NAME, 

  (

    SELECT 

      hla.LOCATION_NAME 

    FROM 

      HR_LOCATIONS_ALL_F_VL hla 

    WHERE 

      hla.location_id = HLPF.location_id

  and trunc(sysdate) between hla.effective_start_date and hla.effective_end_date 

  ) LOC_NAME, 

  hlpf.max_persons head_count, 

  (

    SELECT 

      par.action_reason 

    FROM 

      per_action_occurrences pao, 

      per_action_reasons_vl par 

    WHERE 

      pao.action_reason_id = par.action_reason_id 

      AND pao.action_occurrence_id = HLPF.action_occurrence_id

  ) ACTION_REASON, 

  hlpf.ATTRIBUTE2 Regular_Shift, 

  hlpf.ATTRIBUTE3 Payroll_location, 

  hlpf.ATTRIBUTE5 HR_Department, 

  pjfv.job_code,

  (select pg.name from per_grades_f_vl pg where pg.grade_id=hlpf.entry_grade_id

  and trunc(sysdate) between pg.effective_start_date and pg.effective_end_date )  Entry_Grade,

  (SELECT pglf.name

        FROM   per_grade_ladders_f_vl pglf

        WHERE  pglf.grade_ladder_id = pjfv.grade_ladder_id

               AND Trunc(SYSDATE) BETWEEN pglf.effective_start_date AND

                                          pglf.effective_end_date)

       grade_ladder,

hlpf.ATTRIBUTE1 Salary_Basis_name,

hlpf.ATTRIBUTE4 Job_Car_Eligibility ,

  pjfv.name Job,

  (select ppnf.full_name from per_person_names_f ppnf where ppnf.name_type='GLOBAL' and ppnf.person_id=hlpf.supervisor_id

  and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date 

  ) Manager,

  (select  HLPF_par.name from PER_POSITION_HIERARCHY_F phr,hr_all_positions_f_vl HLPF_par

  where phr.parent_position_id = hlpf_par.position_id

  and trunc(sysdate) between phr.effective_start_date 

  and phr.effective_end_date 

  and trunc(sysdate) between HLPF_par.effective_start_date 

  and HLPF_par.effective_end_date 

  and phr.position_id = HLPF.position_id) parent_position,

  hlpf.ATTRIBUTE10 Local_position_name,

  hlpf.ATTRIBUTE13 Local_position_Desc,

  pfte.Position_Current_FTE current_position_fte, 

  pfte.CurrentIncumbentFTE,

  pfte.Difference_FTE,

  --DELEGATE_POSITION_ID,

  (select  HLPF_del.name from hr_all_positions_f_vl HLPF_del

  where HLPF_del.position_id = HLPF.DELEGATE_POSITION_ID

  and trunc(sysdate) between HLPF_del.effective_start_date and HLPF_del.effective_end_date) Delegate_position,

  (select ppleg.INFORMATION1 from PER_POSITION_LEG_F ppleg where ppleg.legislation_code='BR'

  and trunc(sysdate) between ppleg.effective_start_date and ppleg.effective_end_date

  and ppleg.position_id=hlpf.position_id)  CBO_Occupation,

  hlpf.FUNDED_BY_EXISTING_POSITION,

  hlpf.COST_CENTER,

  hlpf.ENTRY_STEP_ID,

  hlpf.ATTRIBUTE8 Officer_Code,

  hlpf.ATTRIBUTE9 EBA,

  hlpf.last_updated_by,

  hlpf.last_update_date,

  (select pg.name from per_grades_f_vl pg,PER_VALID_GRADES_F pvgf where 1=1 

  and pvgf.position_id= hlpf.position_id

  and pvgf.grade_id = pg.grade_id

  and trunc(sysdate) between pg.effective_start_date and pg.effective_end_date  

  and trunc(sysdate) between pvgf.effective_start_date and pvgf.effective_end_date and rownum=1) grade,

  (select pg.grade_code from per_grades_f_vl pg,PER_VALID_GRADES_F pvgf where 1=1 

  and pvgf.position_id= hlpf.position_id

  and pvgf.grade_id = pg.grade_id

  and trunc(sysdate) between pg.effective_start_date and pg.effective_end_date  

  and trunc(sysdate) between pvgf.effective_start_date and pvgf.effective_end_date and rownum=1) grade_code,

  (select p.name from PER_GRADE_STEPS_F_VL p where p.GRADE_STEP_ID = hlpf.ENTRY_STEP_ID

  and trunc(sysdate) between p.effective_start_date and p.effective_end_date  ) Entry_Step,

  (select fss.SET_CODE from per_grades_f_vl pg,PER_VALID_GRADES_F pvgf,FND_SETID_SETS fss where 1=1 

  and pvgf.position_id= hlpf.position_id

  and pvgf.grade_id = pg.grade_id

  and pg.set_id = fss.set_id

  and trunc(sysdate) between pg.effective_start_date and pg.effective_end_date  

  and trunc(sysdate) between pvgf.effective_start_date and pvgf.effective_end_date and rownum=1) set_code

FROM 

  hr_all_positions_f_vl HLPF, 

  per_jobs_f_vl pjfv ,

  Position_FTE pfte

WHERE 

  1 = 1 

  and HLPF.job_id = pjfv.job_id 

  and HLPF.position_id = pfte.position_id(+)

  and trunc(sysdate) between hlpf.effective_start_date 

  and hlpf.effective_end_date 

  AND Trunc(SYSDATE) BETWEEN pjfv.effective_start_date 

  AND pjfv.effective_end_date

  and hlpf.position_code in ( '123')

Business Unit ID query

 select BU.name, BU.organization_id BU_ID from hr_all_organization_units_vl BU,
HR_ORG_UNIT_CLASSIFICATIONS_F cl
where 1=1
and BU.organization_id=cl.organization_id
and CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT' 
and BU.name ='BU_NAME'

REGEXP_SUBSTR use cases

Split the string

 

SELECT  

REGEXP_SUBSTR(' 123, 234, 345,','[0-9]+',1,level) "REGEXPR_SUBSTR2"  FROM DUAL
connect by REGEXP_SUBSTR(' 123, 234, 345,','[0-9]+',1,level) is not null

 SELECT  

REGEXP_SUBSTR(' 123, abc, 345,','[0-9]+|[a-z]+',1,level) "REGEXPR_SUBSTR2"  FROM DUAL

connect by REGEXP_SUBSTR(' 123, abc, 345,','[0-9]+|[a-z]+',1,level) is not null

Pull 4th word

SELECT

  regexp_substr( 'This is a regexp123_substr demo', '[[:alpha:]]+', 1, 4

  ) the_4th_word

FROM

  dual

 

  

Pull 4th alphanumeric word

  

  SELECT

  regexp_substr( 'This is a regexp123_substr demo', '[[:alnum:]]+', 1, 4

  ) the_4th_word

FROM

  dual

Fetching Questionaire responses within Oracle checklist or Journey

select ans.LONG_TEXT from

HRQ_QSTN_ANSWERS_VL ans

where ans.ANSWER_CODE in

(SELECT trim(COLUMN_VALUE) ANSWER_LIST

FROM ( (select qstn.ANSWER_LIST

from Per_allocated_tasks task,

HRQ_QSTNR_PCPT_RESPONSES_V qstn

where 1=1

and task.ACTION_TYPE = 'ORA_CHK_QUESTIONNAIRE'

and task.ALLOCATED_TASK_ID = qstn.PARTICIPANT_ID

and task.QUESTIONNAIRE_ID=qstn.QUESTIONNAIRE_ID

and task.PERFORMER_ORIG_SYSTEM = 'PERSON_NUMBER')), xmltable(('"' || REPLACE(ANSWER_LIST, ',', '","') || '"')))

Primary Email Query Oracle HCM

select papf.person_number, papf.primary_email_id,pea.email_address_id,EMAIL_TYPE,DATE_FROM,DATE_TO,EMAIL_ADDRESS 

from 

per_all_people_f papf, PER_EMAIL_ADDRESSES  pea

where papf.person_id=pea.person_id

and papf.primary_email_id=pea.email_address_id

and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

Benefits Enrollment Query - Oracle Cloud HCM

SELECT 

  papf.person_number,

  PEN.CONFIG_NUM_1, 

  PEN.assignment_id, 

  PEN.benefit_relation_id, 

  BRN.benefit_relation_name, 

  BRN.BENEFIT_REL_SYSTEM_CD, 

  PEN.TYPE_ID, 

  PEN.CONFIG_CHAR_1, 

  PEN.bnft_amt, 

  PEN.bnft_nnmntry_uom, 

  PEN.bnft_ordr_num, 

  PEN.bnft_typ_cd, 

  BRN.STATUS, 

  PEN.business_group_id, 

  phf.cvrd_in_anthr_pl, 

  phf.disability_status, 

  PEN.enrt_cvg_strt_dt, 

  PEN.enrt_cvg_thru_dt, 

  PEN.enrt_mthd_cd, 

  PEN.enrt_ovridn_flag, 

  PEN.enrt_ovrid_rsn_cd, 

  PEN.enrt_ovrid_thru_dt, 

  PEN.erlst_deenrt_dt, 

  BRN.legal_entity_id, 

  LER.DESC_TXT, 

  LER.GLOBAL_FLAG LER_GLOBAL_FLAG, 

  PEN.ler_id, 

  LER.INSTRUCTION_TEXT, 

  LER.name LER_NAME, 

  LER.QUALG_EVT_FLAG, 

  LER.SELF_ASSIGNED_EVENT_FLAG, 

  LER.SHORT_CODE LER_SHORT_CODE, 

  LER.SHORT_NAME LER_SHORT_NAME, 

  LER.SLCTBL_SLF_SVC_CD, 

  LER.TYP_CD, 

  PEN.no_lngr_elig_flag, 

  PEN.oipl_id, 

  OIPL.CONFIG_CHAR_1 OIPL_CONFIG_CHAR_1, 

  OPT.name OPT_name, 

  PEN.orgnl_enrt_dt, 

  BRN.ORIGINAL_SOURCE, 

  PEN.person_id, 

  PGM.CONFIG_CHAR_1 PGM_CONFIG_CHAR_1, 

  PL.CONFIG_CHAR_3, 

  PEN.per_in_ler_id, 

  pgm.alws_unrstrctd_enrt_flag PGM_alws_unrstrctd_enrt_flag, 

  pgm.global_flag PGM_GLOBAL_FLAG, 

  PEN.pgm_id, 

  PGM.name PGM_name, 

  pgm.short_code PGM_SHORT_CODE, 

  pgm.short_name PGM_SHORT_NAME, 

  pgm.pgm_stat_cd, 

  pl.alws_unrstrctd_enrt_flag PL_alws_unrstrctd_enrt_flag, 

  PL.FUNCTION_CODE, 

  PL.GLOBAL_flag PL_GLOBAL_FLAG, 

  PEN.pl_id, 

  PL.SUBJ_TO_IMPTD_INCM_CD, 

  PL.FRFS_APLY_FLAG, 

  PL.name PL_name, 

  PL.short_code PL_SHORT_CODE, 

  PL.short_name PL_SHORT_NAME, 

  pl.pl_stat_cd, 

  PEN.pl_typ_id, 

  BRN.PRIMARY_REL, 

  PEN.prtt_enrt_rslt_id, 

  PEN.prtt_enrt_rslt_stat_cd, 

  PEN.prtt_is_cvrd_flag, 

  PEN.ptip_id, 

  ptp.ADMIN_CATEGORY_CD, 

  ptp.CARRIER_PLAN_TYPE_NAME, 

  ptp.global_flag PTP_GLOBAL_FLAG, 

  ptp.name PTP_name, 

  ptp.SS_CATEGORY_CD, 

  phf.receipt_of_death_cert_date, 

  phf.registered_disabled_flag, 

  BRN.rel_prmry_asg_id, 

  PEN.rplcs_sspndd_rslt_id, 

  PEN.sspndd_flag, 

  phf.student_status, 

  PHF.tobacco_type_usage, 

  PEN.uom, 

  BRN.UPDATED_SOURCE, 

  PEN.ELECTION_DATE, 

  PEN.INTERIM_FLAG, 

  PIL.LF_EVT_OCRD_DT, 

  PIL.PER_IN_LER_STAT_CD, 

  PEN.CREATED_BY, 

  PEN.CREATION_DATE, 

  PEN.LAST_UPDATED_BY, 

  PEN.LAST_UPDATE_DATE, 

  PEN.LAST_UPDATE_LOGIN, 

  OPT.SHORT_CODE OPT_SHORT_CODE, 

  OPT.SHORT_NAME OPT_SHORT_NAME

FROM 

  per_all_people_f papf, 

  per_all_assignments_f ASSIGN, 

  ben_prtt_enrt_rslt pen, 

  ben_ler_f ler, 

  ben_per_in_ler pil, 

  ben_pl_f PL, 

  ben_pl_typ_f ptp, 

  ben_pgm_f PGM, 

  ben_oipl_f OIPL, 

  ben_opt_f OPT, 

  ben_benefit_relations_f brn, 

  ben_per_le_habits_cov_f phf 

WHERE 

  trunc(sysdate) between pen.enrt_cvg_strt_dt AND pen.enrt_cvg_thru_dt 

  AND pen.enrt_cvg_strt_dt <= pen.enrt_cvg_thru_dt 

  AND pen.enrt_cvg_strt_dt BETWEEN papf.effective_start_date 

  AND papf.effective_end_date 

  AND pen.enrt_cvg_strt_dt BETWEEN ASSIGN.effective_start_date 

  AND ASSIGN.effective_end_date 

  AND pen.enrt_cvg_strt_dt BETWEEN LER.effective_start_date 

  AND LER.effective_end_date 

  AND pen.enrt_cvg_strt_dt BETWEEN BRN.effective_start_date 

  AND BRN.effective_end_date 

  AND pen.enrt_cvg_strt_dt BETWEEN PGM.effective_start_date(+) 

  AND PGM.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN PL.effective_start_date(+) 

  AND PL.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN OIPL.effective_start_date(+) 

  AND OIPL.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN OPT.effective_start_date(+) 

  AND OPT.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN ptp.effective_start_date(+) 

  AND ptp.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN phf.effective_start_date(+) 

  AND phf.effective_end_date(+) 

  AND PEN.prtt_enrt_rslt_stat_cd IS NULL 

  AND PEN.ler_id = LER.ler_id 

  AND PEN.per_in_ler_id = pil.per_in_ler_id 

  AND pil.per_in_ler_stat_cd in ('STRTD', 'PROCD') 

  AND PEN.benefit_relation_id = BRN.benefit_relation_id 

  AND PEN.business_group_id = ASSIGN.business_group_id 

  AND BRN.rel_prmry_asg_id = ASSIGN.assignment_id 

  AND ASSIGN.person_id = PEN.person_id 

  AND papf.person_id = ASSIGN.person_id

  AND PEN.person_id = phf.person_id(+) 

  AND PEN.pgm_id = PGM.pgm_id(+) 

  AND PEN.pl_id = PL.pl_id (+) 

  AND PEN.pl_typ_id = ptp.PL_TYP_ID (+) 

  AND PEN.oipl_id = OIPL.oipl_id(+) 

  AND PEN.opt_id = OPT.opt_id(+)

  AND papf.person_number = '12345'