Query to get ABsence Types and Absence reasons

 select typ.name absence_type, rea.name absence_reason from 
ANC_ABSENCE_TYPES_VL typ,
ANC_ABSENCE_REASONS_VL rea,
ANC_ABSENCE_TYPE_REASONS_F typ_rea
where 1=1
and typ_rea.ABSENCE_TYPE_ID = typ.ABSENCE_TYPE_ID
and typ_rea.ABSENCE_REASON_ID = rea.ABSENCE_REASON_ID
and trunc(sysdate) between typ.effective_start_date and typ.effective_end_date
and trunc(sysdate) between rea.effective_start_date and rea.effective_end_date
and trunc(sysdate) between typ_rea.effective_start_date and typ_rea.effective_end_date
and typ.LEGISLATION_CODE='IN'
order by 1,2

ORC Report - Requisition phase, state and Selection Process

 select 
pr.name process_name,
req.requisition_number, 
pha.name phase_name ,
sta.name state_name,
cand.CANDIDATE_NUMBER,
pha_cand.name Subm_phase_name,
sta_cand.name Subm_state_name
from 
irc_requisitions_vl req,
IRC_PHASES_VL pha,
IRC_STATES_VL sta,
IRC_PROCESSES_B proc,
IRC_PROCESSES_B proc_tmp,
IRC_PROCESSES_VL pr,
IRC_SUBMISSIONS subm,
IRC_CANDIDATES cand,
IRC_PHASES_VL pha_cand,
IRC_STATES_VL sta_cand
where 1=1
and req.CURRENT_PHASE_ID = pha.phase_id
and req.CURRENT_STATE_ID = sta.state_id
and req.requisition_id = subm.requisition_id
and subm.PROCESS_ID = proc.process_id
and proc.PROCESS_TEMPLATE_ID = proc_tmp.process_id
and proc_tmp.process_id = pr.process_id
and subm.person_id = cand.person_id
and subm.CURRENT_PHASE_ID = pha_cand.phase_id
and subm.CURRENT_STATE_ID = sta_cand.state_id
and req.requisition_number='1234'
--and pr.name='Test Candidate selection process'

HCM Extracts - How to read parameter in Data Group filter

Calling a date parameter in filter - pay_report_utils.get_parameter_value_date('EFFECTIVE_DATE')

Calling a number parameter in filter - pay_report_utils.get_parameter_value_number('LEGAL_EMPLOYER_ID')

Calling a text parameter in filter - pay_report_utils.get_parameter_value('PERSON_NUMBER')

Important points to remember

How can I create a payroll relationship?

Oracle HCM Cloud automatically creates a payroll relationship when you hire an employee or create other types of person records such as candidate and pending workers.

What happens if I don't use Cloud Payroll, do I need a payroll relationship?

If you want to capture payroll related information in Oracle HCM Cloud, such as salary, compensation, and time cards, you need a payroll relationship.

How can I end a payroll relationship?

Enter a Final Close Date for a terminated employee after you have completed all payroll processing including any remaining payments and year-end balance adjustments. The Final Close Date ends the payroll relationship record and discontinues the employee to be included in payroll processes.

Enter the Final Close Date on the assignment of the terminated employee. If the employee has no other active assignments, the Final Close Date that you have entered on the assignment shows up on the payroll relationship record.

Correct Termination Date HDL

METADATA|WorkRelationship|PersonNumber|DateStart|WorkerType|LegalEmployerName|CorrectTerminationFlag|ActualTerminationDate
MERGE|WorkRelationship|12345|2021/08/12|E|US1 LE|Y|2022/09/01

CorrectTerminationFlag is passed as Y

BIP report output encoding format change to handle special character display issue(? mark issue)

 While reading data generated using a BIP report in OIC to filter data using certain business requirement, I was not able to read the special characters in OIC from the BIP generated file even though data was coming file in Notepad++ if I checked the BIP generated file. 

The BIP template was eText and the issue was in that where I was using the character set as iso-8859-1 instead of utf-8 which was causing the output file to be generated in ANSI encoding format and in OIC I was trying to read the file using UTF-8.

So to resolve this issue, we need to read the file as ISO8859_1 in OIC and keep the etext character set as iso-8859-1 or else we need to change the character set to utf-8 in etext template and read it as UTF-8 in OIC. This is a simple thing which was overlooked and took a lot of my time.

Global Transfer HDL

 METADATA|WorkRelationship|LegalEmployerName|PersonNumber|DateStart|WorkerType|ActualTerminationDate|EnterpriseSeniorityDate|LastWorkingDate|OnMilitaryServiceFlag|PrimaryFlag|GlobalTransferFlag|ActionCode|RehireRecommendationFlag

MERGE|WorkRelationship|AL Legal Employer Name|20000001|2021/12/01|E||2019/06/01||N|Y|Y|GLB_TRANSFER|Y

METADATA|WorkTerms|ActionCode|AssignmentStatusTypeCode|AssignmentNumber|AssignmentType|WorkerType|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|GradeCode|PeriodOfServiceId(SourceSystemId)|PersonNumber|DateStart|LegalEmployerName|BusinessUnitId|PersonTypeCode

MERGE|WorkTerms|GLB_TRANSFER|ACTIVE_PROCESS|ET20000001-1|ET|E|4712/12/31|Y|1|2021/12/01|Grad1||20000001|2021/12/01|AL Legal Employer Name|300000045830045|Regular

METADATA|Assignment|ActionCode|PersonNumber|EffectiveStartDate|EffectiveEndDate|AssignmentNumber|WorkTermsNumber|AssignmentType|EffectiveSequence|EffectiveLatestChange|BusinessUnitId|DateProbationEnd|WorkerCategory|AssignmentCategory|GradeCode|HourlySalariedCode|PositionCode|JobCode|LocationId|ManagerFlag|OrganizationId|DateStart|LegalEmployerName|PersonTypeCode|PrimaryAssignmentFlag|AssignmentStatusTypeCode|WorkerType

MERGE|Assignment|GLB_TRANSFER|20000001|2021/12/01|4712/12/31|E20000001-1|ET20000001-1|E|1|Y|300000045830045||Senior Management|Full-time Regular|Grad1|S|123134||300000002378027||300000001871927|2021/12/01|AL Legal Employer Name|Regular|Y|ACTIVE_PROCESS|E

Roll Back Global Transfer HDL

METADATA|WorkRelationship|PersonNumber|DateStart|WorkerType|LegalEmployerName|CancelWorkRelationshipFlag

DELETE|WorkRelationship|100240|2022/04/01|E|US1 LE|Y

All we need to do is cancel new work relationship, then employee becomes active on old work relationship

In case old work relationship is a pending worker, pending worker wont become active and will be terminated

Sample Fast Fomula - Global Absence Partial Period Accrual Rate Formula

  /***************************************************************************

FORMULA NAME: XYZ_ANNUAL_LEAVE_PRORATION_FF

FORMULA TYPE: Global Absence Partial Period Accrual Rate Formula

DESCRIPTION: This formula returns the accrual for mid-period enrollments and un-enrollments for Annual Leave absence plan

Change History:

Name Date Version  Comments

-------------------------------------------------------------------------------

Aditya DRAFT 1A Initial Version

*******************************************************************************/

DEFAULT FOR IV_ACCRUAL IS 0

DEFAULT FOR ACP_HIRE_DATE IS '1901/01/01 00:00:00' (date)

DEFAULT FOR ACP_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_ACCRUALPERIODSTARTDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_ACCRUALPERIODENDDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_PLANENROLLMENTSTARTDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_PLANENROLLMENTENDDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_CALEDARSTARTDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR IV_CALEDARENDDATE IS '4712/12/31 00:00:00' (date)

DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE is '47121231'

INPUTS are IV_ACCRUAL,IV_ACCRUALPERIODSTARTDATE,IV_ACCRUALPERIODENDDATE,IV_PLANENROLLMENTSTARTDATE,IV_PLANENROLLMENTENDDATE,IV_CALEDARSTARTDATE,IV_CALEDARENDDATE

ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)

ld_effective_date= GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00'(date))

ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)

LN_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)

ld_hire_date=ACP_HIRE_DATE

ld_term_date=ACP_TERMINATION_DATE

/*to check if hire date is on or before 15th of the month*/

ld_st_date= GREATEST(GREATEST(ld_hire_date,IV_ACCRUALPERIODSTARTDATE),IV_PLANENROLLMENTSTARTDATE)

ln_st_day = to_number(to_char(ld_st_date,'DD'))

if ln_st_day > 15 THEN

(

ld_accrual_period_st_date = ADD_DAYS(LAST_DAY(ld_st_date),1)/*accrual start date is from next month*/

)

else

(

ld_accrual_period_st_date = ADD_MONTHS(ADD_DAYS(LAST_DAY(ld_st_date),1),-1)/*accrual start date should be from 1st of current month*/

)

/*to check if end date is on or after 15th of the month*/

ld_end_date= LEAST(LEAST(ld_term_date,IV_ACCRUALPERIODENDDATE),IV_PLANENROLLMENTENDDATE)

ln_end_day= to_number(to_char(ld_end_date,'DD'))

if ln_end_day <= 15 THEN

(

ld_accrual_period_end_date = ADD_MONTHS(LAST_DAY(ld_end_date),-1)

)

else

(

ld_accrual_period_end_date = LAST_DAY(ld_end_date)

)

ln_term_duration = 1 + (TO_NUMBER(TO_CHAR(IV_CALEDARENDDATE, 'yy')) - TO_NUMBER(TO_CHAR(IV_CALEDARSTARTDATE, 'yy'))) * 12 + (TO_NUMBER(TO_CHAR(IV_CALEDARENDDATE, 'mm')) - TO_NUMBER(TO_CHAR(IV_CALEDARSTARTDATE, 'mm')))

/*prorationFactor = months_between(ADD_DAYS(ld_accrual_period_end_date,1),ld_accrual_period_st_date)/12*/

ln_participation_duration = months_between(ADD_DAYS(ld_accrual_period_end_date,1),ld_accrual_period_st_date)

ln_prorated_accrual = IV_ACCRUAL * (ln_participation_duration / ln_term_duration)

accrual = round(ln_prorated_accrual)

RETURN accrual

Query to fetch different Accrual Types codes to be passed in HSDL or HDL templates

Query to fetch different Accrual Types codes to be passed in HSDL or HDL templates

select lookup_code,meaning,ENABLED_FLAG
 from fnd_lookup_values where language='US'
and lookup_type='ANC_ACCRUAL_ENTRY_TYPE'

REST API call and payload - Reassign a transaction in Oracle Cloud HCM

The below payload is not available in documentation.

Method : PUT

API : bpm/api/4.0/tasks/{task_number}

Payload :

{
  "identities":[
     {
        "id":"finuser2",
        "type":"user"
     }
  ],
  "action":{
     "id":"REASSIGN",
     "type":"system"    
  },
  "comment":{"commentStr":"Second comment","commentScope":"BPM"}
}

The party identified by the specified party ID does not exist

If you are trying to create a personal payment method for an employee but you are getting below error.

 Error: The party identified by the specified party ID does not exist. (PER-1530186)

To Resolve this issue we need to run the "Synchronize Person Records" process so that a party id will be created for the employee, who is basically a new hire

The from date and to date passed as parameters to this process should be in such a way that the last update date of per_persons record for the employee in question falls between these 2 dates.

select PERSON_ID,LAST_UPDATE_DATE from

per_all_people_f where PERSON_ID in (<Enter Person ID>);

To check if party id exists for any person, we can check with below query

SELECT h.party_id

FROM hz_parties h

,hz_orig_sys_references hosp

,per_all_people_f papf

WHERE hosp.owner_table_id = h.party_id

AND hosp.orig_system_reference = to_char (papf.person_id)

AND hosp.owner_table_name = 'HZ_PARTIES'

AND hosp.orig_system = 'FUSION_HCM'

AND papf.person_id = <Enter Person ID>

Query to fetch Union Code on Employment page

 Hi All,

I was trying to pull the union code from employment page for an employee. There is a union_id column in per_all_assignments_m. This needs to be joined with organization_id from hr_organization_units_f_tl and pull the name from this table for union code.

select name from fusion.hr_organization_units_f_tl where organization_id=<PER_ALL_ASSIGNMENTS_M.UNION_ID> and language='US' and trunc(sysdate) between effective_start_date and effective_end_date

Absence Reason validation in Absence Entry Validation formula

 /******************************************************************************
FORMULA NAME: GSE_PRGUS_BEREAVEMENT_VALIDATION
FORMULA TYPE: Global Absence Entry Validation
DESCRIPTION: This formula validates the bereavement absence entry.  Allow
             a certain number of days for immediate family and fewer days
             for non-immediate family.
*******************************************************************************/
DEFAULT FOR IV_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ABSENCE_REASON IS ' '
DEFAULT FOR IV_TOTALDURATION  IS 0
DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0
DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_STATUS_CD IS ' '
DEFAULT FOR ANC_ABS_TYP_NAME IS ' '
DEFAULT FOR ANC_ABS_ENTRS_END_DATE IS '4712/12/31 00:00:00' (date)
INPUTS ARE IV_END_DATE (date), IV_START_DATE (date), IV_ABSENCE_REASON, IV_TOTALDURATION
lc_absence_reason1 = 'Immediate Family'
lc_absence_reason2 = 'Other Relative/Friend'
lc_valid           = 'N'
if IV_ABSENCE_REASON = lc_absence_reason1 then
  (if IV_TOTALDURATION > 40 then
     (lc_valid = 'N'
      ERROR_MESSAGE = 'GSE_PRGUS_ANC_BEREAVEMENT_IF')
   else
      (lc_valid = 'Y')
  )
if IV_ABSENCE_REASON = lc_absence_reason2 then
  (if IV_TOTALDURATION > 24 then
     (lc_valid = 'N'
      ERROR_MESSAGE = 'GSE_PRGUS_ANC_BEREAVEMENT_OT')
   else
      (lc_valid = 'Y')
  )

VALID = lc_valid

if lc_valid = 'N' then
(ERROR_CODE    = 'E'
 RETURN VALID, ERROR_MESSAGE, ERROR_CODE)
ELSE
(RETURN VALID)

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'

Roll Back Global Transfer HDL

 METADATA|WorkRelationship|PersonNumber|DateStart|WorkerType|LegalEmployerName|CancelWorkRelationshipFlag
DELETE|WorkRelationship|100240|2022/04/01|E|US1 LE|Y

All we need to do is cancel new work relationship, then employee becomes active on old work relationship

In case old work relationship is a pending worker, pending worker wont become active and will be terminated

Period Type in Per_periods_of_service

 Reference : Oracle DOC ID 2781368.1

Query to get content_item_id for HDL

 select hci.name,
hci.content_item_code,
hci.content_item_id
from hrt_content_tp_valuesets_vl hctv,
hrt_content_items_vl hci
where hctv.value_set_name like 'Degrees'
and hctv.content_type_id = (select content_type_id from HRT_CONTENT_TYPES_TL CT where 1=1 and CONTENT_TYPE_NAME ='Degrees')
and hci.content_value_set_id = hctv.content_value_set_id
order by hci.content_item_code, hci.name

Talent Profile Item HDL

 When loading Talent Profile Data, first go thru doc id - Load Talent Profile Data For Persons Using HDL (Doc ID 2798626.1)

Use the queries to get relevant data for HDL from above DOC id.

Sample HDL 

METADATA|ProfileItem|ProfileCode|SourceSystemOwner|SourceSystemId|ContentTypeId|SectionId|DateFrom|ItemText2401|ItemText2409|ItemDate1|ItemDate2

MERGE|ProfileItem|PERS_1234|SSOWNER|1234_PREVEMP2|129|300000001994419|2021/11/22|MS Industries|Manager|2011/01/01|2011/01/01

METADATA|ProfileItem|ProfileItemId|ProfileId|ItemDate2

MERGE|ProfileItem|300000040539722|300000040539720|2017/12/31

METADATA|ProfileItem|ProfileItemId|ProfileId

DELETE|ProfileItem|300000056889089|300000044616273

Query to pull Legal Entity Registration Number

 SELECT XREG.registered_name,XJUR.name,XREG.REGISTRATION_NUMBER
     FROM XLE_JURISDICTIONS_TL XJUR , XLE_REGISTRATIONS XREG 
where 1=1
and XJUR.jurisdiction_id = XREG.jurisdiction_id
and XJUR.language='US'
and XJUR.name='Quebec Provincial Tax'--Jurisdiction Name, which you will find on Manage Legal Entity Registrations Page    
and XREG.source_table='XLE_ENTITY_PROFILES'
and XREG.registered_name='XYZ'--LE Name

Global Transfer HDL

METADATA|WorkRelationship|LegalEmployerName|PersonNumber|DateStart|WorkerType|ActualTerminationDate|EnterpriseSeniorityDate|LastWorkingDate|OnMilitaryServiceFlag|PrimaryFlag|GlobalTransferFlag|ActionCode|RehireRecommendationFlag

MERGE|WorkRelationship|AL Legal Employer Name|20000001|2021/12/01|E||2019/06/01||N|Y|Y|GLB_TRANSFER|Y

METADATA|WorkTerms|ActionCode|AssignmentStatusTypeCode|AssignmentNumber|AssignmentType|WorkerType|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|GradeCode|PeriodOfServiceId(SourceSystemId)|PersonNumber|DateStart|LegalEmployerName|BusinessUnitId|PersonTypeCode

MERGE|WorkTerms|GLB_TRANSFER|ACTIVE_PROCESS|ET20000001-1|ET|E|4712/12/31|Y|1|2021/12/01|Grad1||20000001|2021/12/01|AL Legal Employer Name|300000045830045|Regular

METADATA|Assignment|ActionCode|PersonNumber|EffectiveStartDate|EffectiveEndDate|AssignmentNumber|WorkTermsNumber|AssignmentType|EffectiveSequence|EffectiveLatestChange|BusinessUnitId|DateProbationEnd|WorkerCategory|AssignmentCategory|GradeCode|HourlySalariedCode|PositionCode|JobCode|LocationId|ManagerFlag|OrganizationId|DateStart|LegalEmployerName|PersonTypeCode|PrimaryAssignmentFlag|AssignmentStatusTypeCode|WorkerType

MERGE|Assignment|GLB_TRANSFER|20000001|2021/12/01|4712/12/31|E20000001-1|ET20000001-1|E|1|Y|300000045830045||Senior Management|Full-time Regular|Grad1|S|123134||300000002378027||300000001871927|2021/12/01|AL Legal Employer Name|Regular|Y|ACTIVE_PROCESS|E

Cancel Work Relationship HDL

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId|CancelWorkRelationshipFlag
DELETE|WorkRelationship|FUSION|300000012545898|300000012549658|Y
select hik.source_system_owner, hik.source_system_id, ppos.person_id
 from per_periods_of_service ppos, hrc_integration_key_map hik
 where ppos.period_of_service_id = hik.surrogate_id
  and ppos.person_id in (select person_id from per_all_people_f
where person_number = '12345678')