Note about prorating a recurring element with a negative amount

Just a note, when a recurring element is fed a negative amount and has a proration formula on it, the element gets processed in the payroll only when the eff. start date of it is the start of pay period, otherwise it wont be processed. But in case of positive amount, proration works fine. Not sure if its a functionality or a bug.

OTBI report to show person Images

Hello All. I recently came across a cool report to show person Images in OTBI.

Go to the subject area Workforce Management - Person Real Time. Add Person Number, Person ID and Name columns(can be any column). Click on Settings- Edit Formula on one of the columns and add the below code.

Limitation is that report with images cannot be exported.

'<img src="/hcmUI/personImage?personId='||CAST("Worker"."Person ID" AS char)||'">'
or
'<img src=/hcmUI/personImage?personId='||CAST("Worker"."Person ID" AS char)||'>'

Click OK.

Click on Settings - Column Properties - Data Format - Treat Text as HTML.




Click OK.

View the results. You can view the employee images.

Query to fetch Payroll Balance details in Oracle HCM - Cloud

The below query can be used to fetch a Payroll Balance(SIT Withheld) on the Separate Run run type(Relationship State Run dimension). This Separate Run run type will generally be used for Bonus elements.

select papf.person_number,pprd.payroll_relationship_number,
ppnf.full_name,BASE_RUN_TYPE_NAME,RUN_METHOD,SHORTNAME,
to_char(ppa.effective_date,'rrrr/mm/dd')  process_date,
to_char(ppa.date_earned,'rrrr/mm/dd') date_earned,
bal.balance_value "REL ASG RUN BALANCE",
bal.ctx_user_string "Reference"
from
per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_f paaf,
PER_PERIODS_OF_SERVICE ppos,
pay_payroll_assignments ppasg,
PAY_ASSIGNED_PAYROLLS_DN papd,
pay_all_payrolls_f pap,
pay_pay_relationships_dn pprd,
pay_payroll_actions ppa,
pay_payroll_rel_actions ppra,
pay_run_types_f prt,
pay_dimension_usages_vl pdu,
pay_balance_types_vl pbt,
table(pay_balance_view_pkg.get_balance_dimensions
(p_balance_type_id => pbt.balance_type_id
,p_payroll_rel_action_id => ppra.payroll_rel_action_id
,p_payroll_term_id => null
,p_payroll_assignment_id => null
)) bal
where 1=1
and papf.person_id = paaf.person_id
and papf.person_id = ppnf.person_id
and papf.person_id = ppos.person_id
and paaf.period_of_service_id = ppos.period_of_service_id
and paaf.assignment_id = ppasg.hr_assignment_id
and ppasg.payroll_term_id = papd.payroll_term_id
and papd.payroll_id = pap.payroll_id
and ppasg.payroll_relationship_id = pprd.payroll_relationship_id
and pprd.payroll_relationship_id = ppra.payroll_relationship_id
and ppra.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_id = pap.payroll_id
and ppra.run_type_id = prt.run_type_id
and paaf.PRIMARY_FLAG ='Y'
and paaf.ASSIGNMENT_TYPE = 'E'
AND PPOS.date_start = (SELECT max(date_start) FROM PER_PERIODS_OF_SERVICE PPOS_IN WHERE PPOS_IN.person_id = PAPF.person_id)
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 prt.effective_start_date and prt.effective_end_date
and trunc(sysdate) between ppasg.start_date and ppasg.end_date
and trunc(sysdate) between papd.start_date and papd.end_date
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and ppnf.name_type='GLOBAL'--'US'
and paaf.assignment_type IN ('E','C','P','N')
and ppa.action_type in ('R','Q')
and ppra.run_type_id is not null
and ppa.effective_date = to_date('30-04-2020','dd-mm-rrrr')
and prt.BASE_RUN_TYPE_NAME = 'Separate Payment'
and pdu.balance_dimension_id = bal.balance_dimension_id
and pbt.balance_name='SIT Withheld'
and pdu.dimension_name = 'Relationship State Run'
and bal.balance_value <> 0
and papf.person_number in ('12345')

Cloud Report for Inactive Users, Locked users and User Password changes audit report

Oracle has provided the below jobs out of the box for various purposes in Cloud, which can be run from Scheduled Processes.

• Inactive Users
• Locked Users
• User Password Changes Audit Report

The prerequisite to run these reports is to run another process which should ideally be scheduled, is Import User Login History before running any of these.

A zip file of an xml file is generated which can be opened in Excel for a report view.

SQL query to check the background SQL query for a Database Item - DBI

 SELECT 'SELECT ' || definition_text || ' FROM ' AS select_clause_text,

      to_char(TEXT) AS from_clause_text

    FROM ff_database_items fdi, ff_user_entities fue, ff_routes fr

   WHERE 1=1

     AND fue.user_entity_id = fdi.user_entity_id

     AND fr.route_id = fue.route_id

     and user_name='CMP_ASSIGNMENT_SALARY_AMOUNT'

SQL Query to fetch loaded HDL dat file records using content id

select hdfl.text hdl_DAT_FILE
from fusion.hrc_dl_data_sets hdds,
fusion.hrc_dl_data_set_bus_objs hddsbo,
fusion.hrc_dl_file_lines hdfl
where 1=1
and hdds.data_set_id = hddsbo.data_set_id
and hddsbo.data_set_bus_obj_id = hdfl.data_set_bus_obj_id
and hdds.ucm_content_id='UCMFA02392156'

Sample fast formula - Total Compensation Item

/*******************************************************************
FORMULA NAME : XYZ_MTI_TARGET
FORMULA TYPE : Total Compensation Item
Requirement: The formula will fetch an attribute from Assignment EIT
*******************************************************************/

/*=========== INPUT VALUES DEFAULTS BEGIN =====================*/
INPUTS ARE CMP_IV_PERSON_ID (text), CMP_IV_PERIOD_START_DATE (date), CMP_IV_PERIOD_END_DATE (date)
DEFAULT FOR CMP_IV_PERSON_ID IS '-1'
DEFAULT FOR CMP_IV_PERIOD_START_DATE IS '1951/01/01' (date)
DEFAULT FOR PER_ASG_LEGAL_ENTITY_ID is -1
DEFAULT_DATA_VALUE FOR PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_MTI_TARGET IS -1
DEFAULT_DATA_VALUE FOR PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_PERIOD_START IS '4712/12/31 00:00:00' (DATE)
DEFAULT_DATA_VALUE FOR PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_PERIOD_END IS '4712/12/31 00:00:00' (DATE)
DEFAULT_DATA_VALUE FOR PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_ACTIVE IS 'NA'
DEFAULT FOR PER_ASG_ASSIGNMENT_NUMBER IS 'NA'

/*============ INPUT VALUES DEFAULTS ENDS =====================*/

/*=================== FORMULA SECTION BEGIN =================*/

Formula_Name = '[XYZ_MTI_TARGET]'
L_PD_START_DATE = CMP_IV_PERIOD_START_DATE

COMPENSATION_DATES = to_char(CMP_IV_PERIOD_START_DATE,'YYYY/MM/DD')
ASSIGNMENTS = to_char(GET_CONTEXT(HR_ASSIGNMENT_ID,-1))
LEGALEMPLOYERS = to_char(PER_ASG_LEGAL_ENTITY_ID)
CHANGE_CONTEXTS(EFFECTIVE_DATE = L_PD_START_DATE)
(
l_asg_number=PER_ASG_ASSIGNMENT_NUMBER
I = PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_MTI_TARGET.FIRST(-1)
WHILE PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_MTI_TARGET.EXISTS(I) LOOP
(
IF L_PD_START_DATE >= PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_PERIOD_START[I]
AND L_PD_START_DATE <= PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_PERIOD_END[I]
AND PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_ACTIVE[I]='Y'  THEN
(
VALUES = PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_MTI_TARGET[I]
RETURN COMPENSATION_DATES, VALUES, ASSIGNMENTS, LEGALEMPLOYERS
)
I = PER_ASSIGNMENT_EIT_EFF_USER_MTI_PLAN_MTI_TARGET.NEXT(I,-1)
)
)


RETURN ASSIGNMENTS, LEGALEMPLOYERS

/*================ FORMULA SECTION END =======================*/

Sample Fast Formula - Participation and Rate Eligibility formula

Participation and Rate eligibility formula can be used to determine if an employee is eligible for a particular plan(ex: compensation plan). Below fast formula is used to check eligibility of an employee as per the job tagged to his/her assignment.

/*******************************************************************
FORMULA NAME: XYZ_EMP_ELIG_JOB_LEVEL_1_3_5
FORMULA TYPE: Participation and Rate Eligibility
DESCRIPTION: This formula determines if a person is eligible by checking if the person's job level is in 1,3 or 5
*********************************************************************************/
DEFAULT FOR PER_ASG_JOB_APPROVAL_AUTHORITY IS -1
DEFAULT FOR PER_ASG_ASSIGNMENT_NUMBER IS 'NA'

Formula_Name = '[XYZ_EMP_ELIG_JOB_LEVEL_1_3_5]'

l_eff_date  = GET_CONTEXT(EFFECTIVE_DATE, TO_DATE('1951/01/01 12:00:00'))

CHANGE_CONTEXTS(EFFECTIVE_DATE = l_eff_date)
(
IF ((PER_ASG_JOB_APPROVAL_AUTHORITY = 1)
OR (PER_ASG_JOB_APPROVAL_AUTHORITY = 3) OR (PER_ASG_JOB_APPROVAL_AUTHORITY = 5)) THEN
(
ELIGIBLE = 'Y'
RETURN ELIGIBLE
)
)
ELIGIBLE = 'N'
RETURN ELIGIBLE

Sample Compensation Default and Override formula - Compensation related

The Compensation Default and Override formula type can be used to assign default values to cells in compensation worksheet cells. In below example we are pulling out an EIT attribute as of plan start date.


/* ---------------------------------------------------------------------
NAME : XYZ_CMP_DEFAULT_TESTATTR
TYPE : Compensation Default and Override
Requirement: The formula will fetch data from an Assignment EIT(MTI Information) for a compensation worksheet cell
---------------------------------------------------------------------*/

DEFAULT_DATA_VALUE FOR PER_ASSIGNMENT_EIT_EFF_USER_MTI_INFORMATION_TESTATTR IS 'NA'
DEFAULT_DATA_VALUE FOR PER_ASSIGNMENT_EIT_EFF_USER_MTI_INFORMATION_PERIOD_START IS '4712/12/31 00:00:00' (DATE)
DEFAULT_DATA_VALUE FOR PER_ASSIGNMENT_EIT_EFF_USER_MTI_INFORMATION_PERIOD_END IS '4712/12/31 00:00:00' (DATE)
DEFAULT FOR PER_ASG_START_DATE IS '1900/01/01 00:00:00' (date)
DEFAULT FOR PER_ASG_ASSIGNMENT_NUMBER IS 'NA'

INPUTS ARE CMP_IV_PLAN_START_DATE (text) ,CMP_IV_PLAN_END_DATE (text)

DEFAULT FOR CMP_IV_PLAN_START_DATE   IS  '1951/01/01'
DEFAULT FOR CMP_IV_PLAN_END_DATE     IS  '4712/12/31'

L_DATA_TYPE = 'CHAR'
Formula_Name = '[XYZ_CMP_DEFAULT_TESTATTR]'
L_PL_START_DATE      = TO_DATE(CMP_IV_PLAN_START_DATE,'YYYY/MM/DD')

CHANGE_CONTEXTS(EFFECTIVE_DATE = L_PL_START_DATE)
(
 l_asg_number=PER_ASG_ASSIGNMENT_NUMBER
 I = PER_ASSIGNMENT_EIT_EFF_USER_MTI_INFORMATION_TESTATTR.FIRST(-1)
 WHILE PER_ASSIGNMENT_EIT_EFF_USER_MTI_INFORMATION_TESTATTR.EXISTS(I) LOOP
 (
  IF L_PL_START_DATE >= PER_ASSIGNMENT_EIT_EFF_USER_MTI_INFORMATION_PERIOD_START[I]
   AND L_PL_START_DATE <= PER_ASSIGNMENT_EIT_EFF_USER_MTI_INFORMATION_PERIOD_END[I]  THEN
  (
   L_DEFAULT_VALUE = PER_ASSIGNMENT_EIT_EFF_USER_MTI_INFORMATION_TESTATTR[I]
   RETURN L_DEFAULT_VALUE,L_DATA_TYPE
  )
  I = PER_ASSIGNMENT_EIT_EFF_USER_MTI_INFORMATION_TESTATTR.NEXT(I,-1) 
 )
)
RETURN L_DEFAULT_VALUE,L_DATA_TYPE