Sample Bursting query for FTP file in BI Publisher

SELECT 'JOB' as "KEY",
     'eText_Job' TEMPLATE,
     'ETEXT' TEMPLATE_FORMAT,
     'en-US' LOCALE,
     'TEXT' OUTPUT_FORMAT,
     'JOB_FTP' output_name,
     'FTP' DEL_CHANNEL,
     'INTERFACE_TEST' PARAMETER1,--server name set on xmlpserver as admin
     'userid' PARAMETER2,--userid
     'password#1234' PARAMETER3,--password
     '/DIR_TEST/FOLDER' PARAMETER4,--file path
     'JOB_FTP.txt'  PARAMETER5,--file name
     'true' PARAMETER6--secure ftp or not
FROM dual

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

How to get the list of contexts for each formula type?

The below query can be used to find out the different contexts that are available to be used within a fast formula. Change the formula type as required.

select t.base_formula_type_name
,      ttl.formula_type_name
,      ttl.description
,      c.base_context_name
from   ff_formula_types_b t
,      ff_formula_types_tl ttl
,      ff_ftype_context_usages u
,      ff_contexts_b c
where  t.formula_type_id = u.formula_type_id
and    ttl.formula_type_id = t.formula_type_id
and    ttl.language = userenv('LANG')
and    ttl.formula_type_name like 'Oracle%Payroll%'
and    c.context_id = u.context_id

Payroll Batch Loader(PBL) for Payroll Relationship Group related Data Load

Task Name: Object Groups

Task Action Name: Add Payroll Relationship Inclusions and Exclusions

Name : Object Group Name

Employment Level : Payroll Relationship

Employment Number: Payroll Relationship Number

Inclusion Status: Include/Exclude

Data Migration Flow

Difference between Abstract role and Job Role

Abstract Roles
Abstract roles represent a worker's functions in the enterprise independently of the job they do.
Abstract roles permit users to perform functions that span across the different jobs in the enterprise. For example, users who are employees must be provisioned with the Employee abstract role, so they can update their employee profiles and pictures.

Job Role
Job roles provide users with the permissions they need to perform activities specific to their jobs. You can assign job roles directly to users. You can also create custom job roles.
So Job Roles are the one that actually let a user perform actions inside Oracle Cloud.

Termination Date database item for oracle payroll fast formula (For element defined at payroll relationship level)

PER_EXT_WORK_REL_MAX_TERMINATION_DATE - is used 

Termination Date database item for oracle payroll fast formula (For element defined at payroll relationship level)

Query to find out ESS job history for a report

When a report is run throough ESS or when we run it by clicking Schedule option, then below query can be used to find out the job history.


SELECT (CASE
              WHEN state = 1 THEN 'Wait'
              WHEN state = 2 THEN 'Ready'
              WHEN state = 3 THEN 'Running'
              WHEN state = 4 THEN 'Completed'
              WHEN state = 9 THEN 'Cancelled'
              WHEN state = 10 THEN 'Error'
              WHEN state = 12 THEN 'Succeeded'
              WHEN state = 13 THEN 'Paused'
              ELSE TO_CHAR (state)
          END)
             request_state,
         erh.*
    FROM fusion.ess_request_history erh, fusion.ess_request_property erp
   WHERE     1 = 1
         AND erh.requestid = erp.requestid
         AND erp.name = 'report_url'
         AND erp.VALUE LIKE '/Custom/Financials/XX AP Payment Information Email Notification Report.xdo'
ORDER BY erh.requestid DESC

To check GET_VALUE_SET results use the below query

To debug value sets, create a BI report with the following query as a data model to return the required data: SELECT pay_ff_functions.gvs ('SAMPLE_GET_ASG_NUM','|=PERSON_ID=100000012092216') value FROM dual

Context PAYROLL_ASSIGNMENT_ID wasn't set when used at line ABC of formula XYZ_PRESTAT_DEDN_CALCULATOR.

AFDICT_APP_PREFIX-PAY-1635399: An error occurred during formula execution. (ORACLE_ERROR_TEXT=) (FORMULA_ERROR_TEXT=Context PAYROLL_ASSIGNMENT_ID wasn't set when used at line ABC of formula XYZ_PRESTAT_DEDN_CALCULATOR.)

I faced this error when using DBI ASG_HR_ASG_ID in the fast formula (in this particular case, a oracle payroll formula was written on pre stat deduction element).

The above error is got during payroll execution. This error is got because the element, on which this formula is tagged to, is defined at payroll relationship level and not at assignment level.

This DBI is using the context payroll_assignment_id but this context is not available at the payroll relationship level (the element is setup to be processed at payroll relationship level). So this cannot be used in a fast formula of an element which is defined at payroll relationship level.

Refer Doc ID 2542604.1 for more information.

Null value check in Fast formula in cloud

if isnull(VALUE_SET_VALUE) = 'N' then

  (

  VALUE_SET_VALUE = 0

  )


However, it does the exact opposite of what its name suggests: it returns 'Y' if the value is not null, and it returns 'N' if the value is null.

Manage Payroll Process Configuration task navigation

With the 19A release the "Manage Payroll Process Configuration task has moved to a different location:

1. Start on the landing page and click on the “You have a new home page!”
2. Click on “My Client Groups”
3. On the left, click on “Show More”
4. Scroll down to the section titled ‘Payroll’ and you will see “Manage Payroll Process Configuration”
  a. Refer to the below knowledge document for step by step instructions.
  b. https://mosemp.us.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=467288969587748&id=2503131.1&displayIndex=2&_afrWindowMode=0&_adf.ctrl-state=6ba0z82ik_114#FIX
5. By design only user's account with person ID attached is able to access Payroll Process Configuration Task.