Process to be run to enable DFF fields for BI Reporting

Before running the process ensure the fields created as DFF segments is BI Enabled by selecting the BI Enabled checkbox while creating the Global segment.

Process Name - Import Oracle Fusion Data Extensions for Transactional Business Intelligence

When is Absence Plan required

 An absence plan is required if you need the absence to be integrated to payroll, there are some adjustments, or it needs to be sent across to third party.

An absence plan is not required if absence type just needs an approval from Manager and there is no further processing of the absence anywhere in the system.

Sample HDL for Assignment Data Load

METADATA|WorkTerms|AssignmentNumber|ActionCode|ReasonCode|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId
MERGE|WorkTerms|ET5087851|ASG_CHANGE|DATAMIGRATION|2021/10/01|4712/12/31|Y|2|300000619781821

METADATA|Assignment|AssignmentNumber|ActionCode|ReasonCode|WorkTermsAssignmentId|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId|FLEX:PER_ASG_DF|pensionPlanCode(PER_ASG_DF=CA)|payrollDivision(PER_ASG_DF=CA)
MERGE|Assignment|E5087851|ASG_CHANGE|DATAMIGRATION|300000619847597|2021/10/01|4712/12/31|Y|2|300000619781821|CA|08|REG

Query for WorkTerms
SELECT   
PAPF.PERSON_NUMBER,PAAF.assignment_type,
paaf.effective_start_date,
'MERGE' METADATA,
'WorkTerms' WorkTerms,
PAAF.assignment_number,
'ASG_CHANGE' ActionCode,
'DATAMIGRATION' ReasonCode,
'2021/10/01' EffectiveStartDate,
'4712/12/31' EffectiveEndDate,
'Y' EffectiveLatestChange,
decode(paaf.effective_start_date,to_date('10/01/2021','mm/dd/rrrr'),PAAF.EFFECTIVE_SEQUENCE+1,1) EffectiveSequence,
PAAF.PERIOD_OF_SERVICE_ID PeriodOfServiceId
FROM     per_all_people_f PAPF
,        per_person_names_f PPNF
,        per_all_assignments_f PAAF
,        PER_PERIODS_OF_SERVICE PPOS_MAIN
WHERE    PAPF.person_id = PPNF.person_id
AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PPNF.effective_start_date AND PPNF.effective_end_date
AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date
AND      PPNF.name_type = 'GLOBAL'
AND      PPOS_MAIN.date_start = (SELECT max(date_start) FROM PER_PERIODS_OF_SERVICE PPOS WHERE PPOS.person_id = PAPF.person_id)
AND      PPOS_MAIN.person_id = PAPF.person_id
AND      PAAF.person_id = PAPF.person_id
AND      PAAF.assignment_type ='ET'--IN ('E','C','P','N')
AND      PPOS_MAIN.period_of_service_id = PAAF.period_of_service_id
and paaf.legislation_code='CA'
AND      PAPF.person_number IN ('5087851')    
ORDER BY to_number( PAPF.person_number )

Assignment Line Query

SELECT   

PAPF.PERSON_NUMBER,PAAF.assignment_type,

paaf.effective_start_date,

'MERGE' METADATA,

'Assignment' Assignment,

PAAF.assignment_number,

'ASG_CHANGE' ActionCode,

'DATAMIGRATION' ReasonCode,

PAAF.WORK_TERMS_ASSIGNMENT_ID WorkTermsAssignmentId,

'2021/10/01' EffectiveStartDate,

'4712/12/31' EffectiveEndDate,

'Y' EffectiveLatestChange,

decode(paaf.effective_start_date,to_date('10/01/2021','mm/dd/rrrr'),PAAF.EFFECTIVE_SEQUENCE+1,1) EffectiveSequence,

PAAF.PERIOD_OF_SERVICE_ID PeriodOfServiceId,

'CA' "FLEX:PER_ASG_DF",

'' "pensionPlanCode(PER_ASG_DF=CA)",

'' "payrollDivision(PER_ASG_DF=CA)"

FROM     per_all_people_f PAPF

,        per_person_names_f PPNF

,        per_all_assignments_f PAAF

,        PER_PERIODS_OF_SERVICE PPOS_MAIN

WHERE    PAPF.person_id = PPNF.person_id

AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date

AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PPNF.effective_start_date AND PPNF.effective_end_date

AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date

AND      PPNF.name_type = 'GLOBAL'

AND      PPOS_MAIN.date_start = (SELECT max(date_start) FROM PER_PERIODS_OF_SERVICE PPOS WHERE PPOS.person_id = PAPF.person_id)

AND      PPOS_MAIN.person_id = PAPF.person_id

AND      PAAF.person_id = PAPF.person_id

AND      PAAF.assignment_type ='E'--IN ('E','C','P','N')

AND      PPOS_MAIN.period_of_service_id = PAAF.period_of_service_id

and paaf.legislation_code='CA'

AND      PAPF.person_number IN ('5087851')    

ORDER BY to_number( PAPF.person_number )

Triggering HCM Extract Using OIC

We need to use the HCM Cloud Adapter

Under Services, select FlowActionsService and select submitFlow Operation

In the Mapper, pass the parameters as below. Make sure Recurring Flag is also passed as it is mandatory.

Save the integration and execute the same.

HCM Data Loader Transformation formula

Sample Data - Job.txt

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

Test Job3,TJ3,COMMON,2021/01/01,4712/12/31,A

Test Job4,TJ4,COMMON,2021/01/01,4712/12/31,A

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

* FORMULA NAME: GENERATE_JOB_HDL_FROM_TXT

* FORMULA TYPE: HCM Data Loader

* DESCRIPTION: This formula will create Job HDL File from TXT File

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

/* Inputs  */

INPUTS ARE  OPERATION (text), LINENO (number),  LINEREPEATNO (number),POSITION1 (text), POSITION2 (text), POSITION3 (text), POSITION4 (text), POSITION5 (text), POSITION6 (text)

DEFAULT FOR POSITION1 IS 'NO DATA'

DEFAULT FOR POSITION2 IS 'NO DATA'

DEFAULT FOR POSITION3 IS 'NO DATA'

DEFAULT FOR POSITION4 IS 'NO DATA'

DEFAULT FOR POSITION5 IS 'NO DATA'

DEFAULT FOR POSITION6 IS 'NO DATA'

DEFAULT FOR LINEREPEATNO IS 1

IF OPERATION='FILETYPE' THEN 

   OUTPUTVALUE='DELIMITED' 

   

ELSE IF OPERATION='DELIMITER' THEN 

   OUTPUTVALUE=',' 

   

ELSE IF OPERATION='READ' THEN

   OUTPUTVALUE='NONE'

ELSE IF OPERATION = 'NUMBEROFBUSINESSOBJECTS' THEN

(

OUTPUTVALUE = '1'

RETURN OUTPUTVALUE

)

ELSE IF OPERATION = 'METADATALINEINFORMATION' THEN

(   

METADATA1[1]  = 'Job' /*FileName*/ /*Reserved*/

METADATA1[2]  = 'Job' /*FileDiscriminator*/ /*Reserved*/

METADATA1[3]  = 'Name'

METADATA1[4]  = 'JobCode'

METADATA1[5]  = 'SetCode'

METADATA1[6]  = 'EffectiveStartDate'

METADATA1[7]  = 'EffectiveEndDate'

METADATA1[8]  = 'ActiveStatus'

RETURN METADATA1

)

ELSE IF OPERATION='MAP' THEN 

(

IF LINEREPEATNO = 1 THEN

(

LINEREPEAT = 'Y'

FileName = 'Job'

BusinessOperation = 'MERGE'

FileDiscriminator = 'Job'

Name = trim(POSITION1)

JobCode = trim(POSITION2)

SetCode = trim(POSITION3)

EffectiveStartDate = trim(POSITION4)

EffectiveEndDate = trim(POSITION5)

ActiveStatus = trim(POSITION6)

RETURN BusinessOperation,FileName,FileDiscriminator,Name,JobCode,SetCode,EffectiveStartDate,EffectiveEndDate,ActiveStatus,LINEREPEAT,LINEREPEATNO

)

)

ELSE 

   OUTPUTVALUE='NONE'

RETURN OUTPUTVALUE

/* End Formula Text */

Query to Fetch Business Unit in Cloud

select haou.name as business_unit,
paam.person_id
from hr_org_unit_classifications_f houcf
, hr_all_organization_units haou
,per_all_assignments_m paam
,per_periods_of_service ppos
where
1=1
and houcf.organization_id = haou.organization_id
and paam.organization_id= haou.organization_id
and PPOS.PERIOD_OF_SERVICE_ID= PAAM.PERIOD_OF_SERVICE_ID
and houcf.classification_code= 'DEPARTMENT'
and houcf.status = 'A' 
AND paam.assignment_type IN ('E','C')
AND paam.effective_latest_change = 'Y' 
and paam.assignment_status_type IN ('ACTIVE','SUSPENDED')
and ppos.ACTUAL_TERMINATION_DATE is null
and :EFFECTIVE_DATE BETWEEN Paam.EFFECTIVE_START_DATE AND Paam.EFFECTIVE_END_DATE
and :EFFECTIVE_DATE BETWEEN haou.EFFECTIVE_START_DATE AND haou.EFFECTIVE_END_DATE
and :EFFECTIVE_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE

Font Mapping in BI Publisher

We were facing an issue where a particular font(Kunstler Regular) which was added in the RTF template. Though the font was visible in the RTF template, it was not visible in the final PDF report generated. To resolve this issue, the below steps need to be done.

• Pull out the font's .TTF file from C:/Windows/Fonts into a separate folder. If the font family has multiple fonts, then many files will be copied into your destination folder. In my case it was just font(KUNSTLER.ttf file)
• In BIP navigate to Administration-Manage BI Publisher-Font Mappings
• Choose the above file under Manage Custom Fonts and click on upload
• Then under RTF templates click on Add Font Mapping
• Give Base Font as the name of the font, it should be same as the name which is visible in MS word(else it didn't work for me)
• Select target font, which will the above uploaded file name and click on Apply
• Once this is done, in BIP navigate to Administration-Manage BI Publisher-Manage Cache and click on Clear Object Cache.

Once these steps are done and the report was re-run, the PDF output also had displayed the Kunstler Regular font.

GET_PAY_AVAILABILITY function parameters

 

Sample Formula to calculate absence duration in Oracle Cloud HCM

 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_START_TIME IS '00:00'
DEFAULT FOR IV_END_TIME IS '23:59'
DEFAULT FOR IV_START_DURATION IS 1
DEFAULT FOR IV_END_DURATION IS 1
DEFAULT FOR PER_ASG_FTE_VALUE IS 1
        DEFAULT FOR PER_ASG_EMPLOYMENT_CATEGORY IS 'UNKNOWN'

INPUTS ARE IV_START_DATE (date), IV_END_DATE (date), IV_START_TIME(text),IV_END_TIME(text), IV_START_DURATION(number), IV_END_DURATION(number)

ln_duration = 0
ln_unrounded_duration_d = 0

lc_asg_resource_type = 'ASSIGN'
ld_period_start_date = to_date(to_char(IV_START_DATE,'DD/MM/RRRR')||' '||IV_START_TIME,'DD/MM/RRRR HH24:MI:SS')
ld_period_end_date = to_date(to_char(IV_END_DATE,'DD/MM/RRRR')||' '||IV_END_TIME,'DD/MM/RRRR HH24:MI:SS')
lc_use_sch_asg_y = 'Y'
lc_use_sch_inh_y = 'Y'
lc_include_noshift_y = 'Y'
lc_include_calevents_y = 'Y'
lc_calc_units_d = 'D'

/* Retrieve the absence duration based on the work schedule */
ln_unrounded_duration_d = GET_PAY_AVAILABILITY (lc_asg_resource_type,
    ld_period_start_date,
    ld_period_end_date,
    lc_use_sch_asg_y,
    lc_use_sch_inh_y,
    lc_include_noshift_y,
    lc_include_calevents_y,
    lc_calc_units_d)
/* test if (vertical) Part time apply the FTE */
       IF PER_ASG_EMPLOYMENT_CATEGORY ='FR_20' THEN 
   ln_duration = ln_unrounded_duration_d * (1/PER_ASG_FTE_VALUE)
       ELSE ln_duration = ln_unrounded_duration_d
  /*duration = ROUND(ln_duration,2)*/
       duration = ln_duration

       RETURN duration

Getting Sysdate in a particular Date format in OIC

Sysdate can be got in a particular Date format using the below code in OIC.

string( xp20:format-dateTime(  string ( fn:current-dateTime() ) ,'[Y0001]-[M01]-[D01][H01]:[m01]:[s01] ' ) )

Reading a lookup value in OIC

The below code can be used to read lookup value in OIC.

dvm:lookupValue('oramds:/apps/ICS/DVM/Test1.dvm','Column1','Path','Column2','Default Value for Column2')

dvm:lookupValue('oramds:/apps/ICS/DVM/Test1.dvm','Column1','Size','Column2','Default Value for Column2')

Sample Fast Formula - Eligibility Profile Fast Formula based on US LDG

Came across this useful post. Re-posting.

 /* --------------------------------------------------------------------------------------------------------------------------
   ********************************************************************************************
   *          NAME : US Legislation                                                                                                 
   *          TYPE : Participation and Rate Eligiblity
   *          Requirement: Formula to get the eligible employees for US
   ********************************************************************************************
------------------------------------------------------------------------------------------------------------------------*/ 
DEFAULT FOR PER_ASG_LEGISLATION_CODE IS 'ABC'
ELIGIBLE = 'N'
IF (PER_ASG_LEGISLATION_CODE = 'US') THEN
(
ELIGIBLE = 'Y'
)
RETURN ELIGIBLE

Customizing HCM seeded notifications

If you want to change a notification format in Cloud Oracle HCM, go to the BIP path using the admin login(use xmlpserver in URL), Shared folders/Human Capital Management/ Workflow Notifications and click customize, so that the copy gets created in Shared folders/Custom/Human Capital Management/ Workflow Notifications. But before doing this, there doesnt exist a copy of the report being customized.

/Shared folders/Human Capital Management/ Workflow Notifications

OIC iCal scheduling syntax

source: rfc5545 (ietf.org)  

 recur = recur-rule-part *( ";" recur-rule-part )

                       ;
                       ; The rule parts are not ordered in any
                       ; particular sequence.
                       ;
                       ; The FREQ rule part is REQUIRED,
                       ; but MUST NOT occur more than once.
                       ;
                       ; The UNTIL or COUNT rule parts are OPTIONAL,
                       ; but they MUST NOT occur in the same 'recur'.
                       ;



Desruisseaux                Standards Track                    [Page 38]

---

RFC 5545                       iCalendar                  September 2009


                       ; The other rule parts are OPTIONAL,
                       ; but MUST NOT occur more than once.

       recur-rule-part = ( "FREQ" "=" freq )
                       / ( "UNTIL" "=" enddate )
                       / ( "COUNT" "=" 1*DIGIT )
                       / ( "INTERVAL" "=" 1*DIGIT )
                       / ( "BYSECOND" "=" byseclist )
                       / ( "BYMINUTE" "=" byminlist )
                       / ( "BYHOUR" "=" byhrlist )
                       / ( "BYDAY" "=" bywdaylist )
                       / ( "BYMONTHDAY" "=" bymodaylist )
                       / ( "BYYEARDAY" "=" byyrdaylist )
                       / ( "BYWEEKNO" "=" bywknolist )
                       / ( "BYMONTH" "=" bymolist )
                       / ( "BYSETPOS" "=" bysplist )
                       / ( "WKST" "=" weekday )

       freq        = "SECONDLY" / "MINUTELY" / "HOURLY" / "DAILY"
                   / "WEEKLY" / "MONTHLY" / "YEARLY"

       enddate     = date / date-time

       byseclist   = ( seconds *("," seconds) )

       seconds     = 1*2DIGIT       ;0 to 60

       byminlist   = ( minutes *("," minutes) )

       minutes     = 1*2DIGIT       ;0 to 59

       byhrlist    = ( hour *("," hour) )

       hour        = 1*2DIGIT       ;0 to 23

       bywdaylist  = ( weekdaynum *("," weekdaynum) )

       weekdaynum  = [[plus / minus] ordwk] weekday

       plus        = "+"

       minus       = "-"

       ordwk       = 1*2DIGIT       ;1 to 53

       weekday     = "SU" / "MO" / "TU" / "WE" / "TH" / "FR" / "SA"
       ;Corresponding to SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,
       ;FRIDAY, and SATURDAY days of the week.



Desruisseaux                Standards Track                    [Page 39]

---

RFC 5545                       iCalendar                  September 2009


       bymodaylist = ( monthdaynum *("," monthdaynum) )

       monthdaynum = [plus / minus] ordmoday

       ordmoday    = 1*2DIGIT       ;1 to 31

       byyrdaylist = ( yeardaynum *("," yeardaynum) )

       yeardaynum  = [plus / minus] ordyrday

       ordyrday    = 1*3DIGIT      ;1 to 366

       bywknolist  = ( weeknum *("," weeknum) )

       weeknum     = [plus / minus] ordwk

       bymolist    = ( monthnum *("," monthnum) )

       monthnum    = 1*2DIGIT       ;1 to 12

       bysplist    = ( setposday *("," setposday) )

       setposday   = yeardaynum

example:

FREQ=MONTHLY;BYMONTH=1,3,5,7,8,10,12;BYMONTHDAY=15,31;

&FREQ=MONTHLY;BYMONTH=4,6,9,11;BYMONTHDAY=15,30;

&FREQ=MONTHLY;BYMONTH=2;BYMONTHDAY=15,28,29;

OIC iCal scheduling example

Schedule an integration from sunday to thursday 8 AM to 8PM for every 30 minutes

Use iCal expression as below.

FREQ=WEEKLY;BYDAY=SU,MO,TU,WE,TH;BYHOUR=8,9,10,11,12,13,14,15,16,17,18,19,20;BYMINUTE=0,30;

Query to get contexts for a DBI

 select * from FF_CONTEXTS_TL where LANGUAGE='US' AND context_id in
   (select CONTEXT_ID from FF_CONTEXTS_B where CONTEXT_ID in
      (select CONTEXT_ID from ff_route_context_usages where route_id in
         (select route_id from ff_user_entities_b where user_entity_id in
            (select user_entity_id from ff_database_items_b where base_user_name like 'ANC_ABS_ENTRS_ATTRIBUTE1')
         )
      )
   )

EBS Bank accounts query

 select
ppr.payroll_relationship_id payrollrelationshipid,
ppr.person_id personid,
h.party_id partyid,
eba.bank_account_id bankaccountid,
eba.bank_account_num bankaccountnumber,
eba.bank_id bankid,
eba.bank_name bankname,
eba.bank_number banknumber,
eba.branch_number branchnumber,
eba.branch_id branchid,
eba.bank_branch_name branchname,
eba.eft_swift_code eftswiftcode,
eba.bank_home_country homecountry,
eba.bank_account_type bankaccounttype
FROM
pay_bank_accounts eba,
iby_account_owners ebao,
hz_parties h,
pay_pay_relationships_dn ppr,
per_persons p,
hz_orig_sys_references hosp
WHERE
eba.bank_account_id = ebao.ext_bank_account_id
AND ebao.account_owner_party_id = h.party_id
AND hosp.owner_table_id = h.party_id
AND hosp.orig_system_reference = TO_CHAR(ppr.person_id)
AND hosp.owner_table_name = 'HZ_PARTIES'
AND hosp.orig_system = 'FUSION_HCM'
AND ppr.person_id = p.person_id
AND h.status = 'A'
--and eba.bank_account_id= fill_value
and ppr.payroll_relationship_number='123'

EBS Run Results Query

 SELECT papf.employee_number,
       papf.full_name,
       ppa.effective_date,
       pp.payroll_name,
       pet.element_name,
       piv.name input_value,
       prrv.result_value
FROM pay_payroll_actions ppa,
     pay_assignment_actions paa,
     pay_payrolls_f pp,
     pay_run_results prr,
     pay_run_result_values prrv,
     pay_input_values_f piv,
     pay_element_types_f pet,
     per_all_assignments_f paaf,
     per_all_people_f papf
WHERE 1=1 and ppa.payroll_action_id = :payroll_action_id
  AND ppa.payroll_action_id = paa.payroll_action_id
  AND ppa.payroll_id = pp.payroll_id
  AND paa.assignment_action_id = prr.assignment_action_id
  AND prr.run_result_id= prrv.run_result_id
  AND prrv.input_value_id = piv.input_value_id
  AND piv.element_type_id = pet.element_type_id
  AND paaf.assignment_id = paa.assignment_id
  AND paaf.person_id = papf.person_id
  and paaf.business_group_id=3444
  and pet.element_name='Regular Salary'
  AND trunc(sysdate) BETWEEN pp.effective_start_date AND pp.effective_end_date
  AND trunc(sysdate) BETWEEN pet.effective_start_date AND pet.effective_end_date
  AND trunc(sysdate) BETWEEN piv.effective_start_date AND piv.effective_end_date
  AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
  AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
  --and ppa.effective_date=trunc(sysdate-11)
  and piv.name='Pay Value'
ORDER BY employee_number;

Personal Payment Method and Bank account details Query in EBS

 SELECT
    papf.employee_number,
    acc.segment1,
    acc.segment2,
    acc.segment3,
    acc.segment4,
    acc.segment5,
    pppmf.last_update_date
FROM
    pay_personal_payment_methods_f  pppmf,
    per_all_assignments_f           paaf,
    per_all_people_f                papf,
    pay_external_accounts           acc
WHERE
        1 = 1
    AND pppmf.assignment_id = paaf.assignment_id
    AND paaf.person_id = papf.person_id
    AND acc.external_account_id = pppmf.external_account_id
    AND paaf.assignment_type = 'E'
    AND paaf.primary_flag = 'Y'
    AND paaf.business_group_id = 3444
    AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND trunc(sysdate) BETWEEN pppmf.effective_start_date AND pppmf.effective_end_date
    AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
    AND papf.employee_number IN ( '')

Query to fetch Absence Plan Accrual balance

SELECT
    papf.person_number,
    aapft.name,
    (
        SELECT
            a.end_bal
        FROM
            anc_per_accrual_entries  a,
            anc_per_plan_enrollment  b
        WHERE
                a.per_plan_enrt_id = b.per_plan_enrt_id
            AND a.accrual_period = b.last_accrual_run
            AND b.work_term_asg_id = paaf.work_terms_assignment_id
            AND b.plan_id = appe.plan_id
            AND b.person_id = paaf.person_id
            AND trunc(sysdate) BETWEEN b.enrt_st_dt AND b.enrt_end_dt
    ) accrual_balance
FROM
    per_all_assignments_m    paaf,
    per_all_people_f         papf,
    anc_per_plan_enrollment  appe,
    anc_absence_plans_f_tl   aapft
WHERE
        paaf.person_id = papf.person_id
    AND appe.person_id = papf.person_id
    AND aapft.absence_plan_id = appe.plan_id
    AND aapft.language = 'US'
    AND aapft.name = 'Holiday - Days'
    AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND trunc(sysdate) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
    AND papf.person_number = '11111'
    AND paaf.primary_flag = 'Y'
    AND paaf.assignment_type = 'E'    

Oracle Payroll Payment Distribution Flow

 

Retry Payroll Example

 Let us say, we have run the payroll and later make some changes to an employee and retry the payroll. The process is as follows.

First Mark the process for retry.

Then run the process Retry Payroll or Retroactive Calculation

Select the correct process name and Submit it.

Once this process is complete the process which was marked for Retry earlier will now show it is completed.

HDL to delete payroll relationship number from Payroll Relationship type Object Group

 File Name: ObjectGroupAmend.dat

METADATA|ObjectGroupAmend|LegislativeDataGroupName|ObjectCode|ObjectGroupCode|ObjectGroupLevelName

DELETE|ObjectGroupAmend|US Legislative Data Group|12345|Test_PRO_Group|Payroll Relationship

Padding Negative numbers in SQL and Etext Templates

 I had a requirement to display a negative number left padded with 0's in etext template. This is how you do it.

FORMAT_NUMBER(-25,'00000000') will give -00002500

In SQL this can be achieved using to_char('-25','000000000')

Sample HDL for Closing Payroll

METADATA|AssignedPayroll|AssignmentNumber|LegislativeDataGroupName|PayrollDefinitionCode|StartDate|CloseDate|EffectiveStartDate|EffectiveEndDate

MERGE|AssignedPayroll|E1001|US Legislative Data Group|Semi Monthly|2021/01/05|2021/03/31 00:00:00|2021/01/05|2021/03/31

MERGE|AssignedPayroll|E1002|US Legislative Data Group|Semi Monthly|2021/01/05|2021/03/31 00:00:00|2021/01/05|

Close Date should be passed in the format - yyyy/mm/dd hh24:mi:ss. Otherwise error will be thrown.

Effective End Date if left blank also works the same way in above case as I did for the second employee above.

Query to fetch Payroll Relationship and Person Numbers from Object Group

 Query to fetch Payroll Relationship and Person Numbers from Object Group which are added as static.

select
papf.person_number,ppnf.full_name,pprd.PAYROLL_RELATIONSHIP_NUMBER,poga.*
from 
per_all_people_f papf,
per_person_names_f ppnf,

per_all_assignments_f paaf,
pay_payroll_assignments ppasg,
pay_pay_relationships_dn pprd,
pay_object_group_amends poga,
pay_object_groups pog
where 1=1
and papf.person_id = paaf.person_id
and papf.person_id = ppnf.person_id
and paaf.assignment_id = ppasg.hr_assignment_id
--and paaf.person_id = ppasg.person_id
and ppasg.payroll_relationship_id = pprd.payroll_relationship_id
and pog.OBJECT_GROUP_ID=poga.OBJECT_GROUP_ID
and poga.object_id=pprd.payroll_relationship_id
and paaf.PRIMARY_FLAG ='Y'
--and paaf.ASSIGNMENT_TYPE = 'E'
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and trunc(sysdate) between ppasg.start_date and ppasg.end_date
and ppnf.name_type='GLOBAL'
and base_object_group_name like 'Object_Group_Name'

Query to check USER level Profile Option Value in Oracle Cloud HCM

I needed a query to fetch the profile option values at USER level for Language Preference set by the employee. So here is what I had written, let me know if any change is required.

SELECT
    fnd.language_code
FROM
    fnd_profile_options_b      b,
    fnd_profile_option_values  v,
    per_users                  per,
    fnd_languages_vl           fnd
WHERE
        v.level_value = per.user_guid
    AND b.profile_option_id = v.profile_option_id
    AND per.person_id IS NOT NULL
    AND b.profile_option_name = 'FND_LANGUAGE'
    AND v.level_name = 'USER'
    AND v.profile_option_value = fnd.language_tag
    AND fnd.activation_status = 'ACTIVE'
    AND per.person_id = (select distinct person_id from per_all_people_f where person_number='123456')