Query to get the list of contexts for each formula type in Oracle Fusion HCM

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 'Performance Rating Calculation'--'Oracle%Payroll%'

and    c.context_id = u.context_id

Worker Self Evaluation - Calculated Rating Issue - Invalid Rating Level in Oracle Fusion HCM Performance Rating Calculation

One of the possible reason to fail is if the Length of the Goal Name is more than 255 Characters. Try to  reduce the Goal Name length to below 250 characters and use description for detailed information about the Goal.

Note: Please check with Oracle before you do any changes.

Query to get Employee Assignment Status in Oracle Fusion

SELECT papf.person_number

      ,pastt.user_status assignment_status

  FROM per_all_people_f papf

      ,per_all_assignments_m paam

  ,per_assignment_status_types past

  ,per_assignment_status_types_tl pastt

 WHERE papf.person_id = paam.person_id

   AND paam.assignment_status_type_id = past.assignment_status_type_id

   AND past.assignment_status_type_id = pastt.assignment_status_type_id

   AND pastt.source_lang = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

   AND paam.primary_assignment_flag = 'Y'

   AND paam.assignment_type = 'E'

   and paam.effective_latest_change = 'Y'

   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN past.start_date AND NVL(past.end_date,SYSDATE)

   AND papf.person_number = nvl(:p_person_number,papf.person_number)

ORDER BY papf.person_number asc

        ,pastt.user_status 

Query to get Element Entry details in Oracle Fusion HCM

SELECT peef.* 

  FROM per_all_people_f papf

      ,pay_element_entries_f peef

  ,pay_element_types_f pet

 WHERE peef.element_type_id = pet.element_type_id

   AND papf.person_id = peef.person_id

   AND papf.person_number = 'E-12345'

   AND peef.creator_type = 'BEN'

   AND pet.base_element_name= 'XX_ELEMENT_NAME'  

Mandatory Input value for HCM Elements in Oracle Fusion

SELECT petf.element_type_id

      ,pettl.element_name

      ,petf.processing_type Recurring_NonRecurring

      ,petf.effective_start_date

      ,petf.effective_end_date

      ,petf.multiple_entries_allowed_flag

      ,pivf.base_name

      ,pivf.mandatory_flag

  FROM pay_element_types_f petf

      ,pay_element_types_tl pettl

      ,pay_input_values_f pivf

 WHERE petf.element_type_id = pettl.element_type_id

   AND pivf.element_type_id=petf.element_type_id

   AND pettl.element_name = 'XX_ELEMENT_NAME'

   AND pettl.language=USERENV('LANG')

   AND pivf.user_enterable_flag='Y'

Query to get Employee Leave Balance for Specific Plan in Oracle Fusion

SELECT papf.person_number employee_number

      ,ppn.full_name employee_name

  ,TO_CHAR(acc.procd_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') transaction_date

  ,SUM(acc.value) balance

  FROM per_all_people_f papf

  ,per_person_names_f ppn

  ,anc_per_acrl_entry_dtls acc

  ,anc_absence_plans_vl abpv

WHERE papf.person_id = ppn.person_id

  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

  AND TRUNC(SYSDATE) BETWEEN ppn.effective_start_date AND ppn.effective_end_date

  AND ppn.name_type = 'GLOBAL'

  AND acc.person_id = papf.person_id

  AND abpv.absence_plan_id = acc.pl_id

  AND abpv.name = 'XX Plan'

  AND papf.person_number = '123456789'--:p_person_number

GROUP BY papf.person_number 

      ,ppn.full_name 

Functional Setup Manager in Oracle Fusion

• Functional Setup Manager is a web application that enables you to manage and perform functional configuration tasks for offerings. 
• FSM is installed and deployed as part of OTBI Enterprise for HCM Cloud Service. 
• FSM generates a list of configuration tasks specific to the offering and functional areas that were selected. These tasks can be assigned to different functional developers and the status of the implementation project can be monitored in FSM.
• Functional Setup Manager user interfaces guide functional developers through the performance of each task. 

Configuration Manager in Oracle Fusion

• Configuration Manager is a web application for setting up and maintaining an OTBI Enterprise for HCM Cloud Service environment. 
• It also provides a quick review of setup values, and is the tool for monitoring and troubleshooting load plan executions. 
• Configuration Manager works in conjunction with Functional Setup Manager to provide guided tasks to configure offerings and functional areas.
• Using Configuration Manager, migrate configuration data across environments, using the import and export options.
• Use Configuration Manager to Monitor and manage setup data also monitor and manage load plans.

Function Security Privileges in Oracle Fusion HCM

• Each Function Security Privilege secures the code resources that make up the relevant page, such as the Promote Worker page.

• If the predefined security reference implementation doesn't fully represent your enterprise, then you can make changes. For example, the predefined Line Manager abstract role includes compensation management duties. 

• If some of your line managers don't handle compensation, then you can create a custom line manager role without those duties. 

• Alternatively, if a predefined job role is too narrowly defined, then you can create a job role with a greater range of duties than its predefined equivalent.

Data Security Policies in Oracle Fusion HCM

Data Security Policy combines:

• A Duty role. Ex:- Worker Promotion Duty.

• A business object being accessed. Ex:- Person Assignment.

• The condition that must be met for access to be granted. Ex:- Human Resource Specialists can promote workers whose person and assignment records are identified in their person and assignment security profiles. 

• A Data Security Privilege that defines the action being performed. Ex:- Promote Worker.

Duty Roles in Oracle Fusion HCM

• Duty Roles are associated with Function Security Privileges(FSV) and Data Security Policies(DSP).

• In the above screenshot Worker Promotion Duty Role is associated with one Function Security Privilege and two Data Security Policies.

• The FSP Promote Worker secures access to the Promote Worker page. 

• The DSP Promote Worker Data determines the workers whom users with this duty role can promote.

• The DSP Choose Position Data determines the positions into which users with this duty role can promote workers.

Role Inheritance in Oracle Fusion HCM

• Each Role is a Hierarchy of other Roles.
• HCM Data Roles inherit Job or Abstract Roles.
• Job and Abstract Roles inherit Duty Roles.
• Duty Roles inherit other Duty Roles.
• When you assign both Data and Abstract roles to users, they inherit the data and function security associated with those roles.

In above screenshot User ABC has 2 Roles.

1. HR Specialist Vision Corporation which is a data role.
2. Employee which is an abstract role.

• Role HR Specialist Vision Corporation inherits the job role Human Resource Specialist which in turn inherits the duty roles that provide access to the tasks and functions that a human resource specialist performs. The security profile assigned to the data role provides the data access for the role.

• Role Employee inherits the duty roles that provide access to all tasks and functions, unrelated to a specific job, that every employee performs. The security profile assigned to the abstract role provides the data access for the role.

Different Types of roles in Oracle Fusion Human Capital Management(HCM)

There are 4 different types of roles in Oracle Fusion HCM.

• Data roles
• Abstract roles
• Job roles
• Duty roles

Data Roles:- Data Roles combine a worker's job and the data that users with the job must access. For example, the HCM data role Payroll Administrator Payroll US combines a job i.e. Payroll Administrator with a data scope i.e. Payroll US. You define the data scope of a data role in one or more HCM security profiles.

Abstract Roles:- Abstract roles represent a worker's role in the enterprise independently of the job  that you hire the worker to do. There are 3 abstract roles which are predefined in Oracle Fusion HCM:

1. Employee
2. Contingent worker
3. Line manager

You can also create custom abstract roles. All workers are likely to have at least one abstract role through which they access standard functions, such as managing their own information and searching the worker directory. You assign abstract roles directly to users.

Job Roles:- Job roles represent the job that you hire a worker to perform. Human Resource Analyst and Payroll Manager are examples of predefined job roles. You can also create custom job roles in Oracle Fusion HCM. Typically, you include job roles in data roles and assign those data roles to users. The IT Security Manager and Application Implementation Consultant predefined job roles are exceptions to this general rule because they're not considered HCM job roles. Also, you don't define their data scope in HCM security profiles.

Duty Roles:- Duty roles represent the individual duties that users perform as part of their job. They grant access to work areas, dashboards, task flows, application pages, reports, batch programs, ..etc.  Both Job roles and abstract roles inherit duty roles. Duty roles can also inherit other duty roles. They're part of the security reference implementation, and are the building blocks of custom job and abstract roles. You can also create custom duty roles. You don't assign duty roles directly to users.

Few Predefined HCM Roles in Oracle Fusion

In Oracle Fusion Human Capital Management many Job and Abstract roles are predefined . Below are the few of main predefined HCM Roles

• Benefits Administrator
• Benefits Manager
• Benefits Specialist
• Compensation Administrator
• Compensation Analyst
• Compensation Manager
• Compensation Specialist
• Contingent Worker
• Employee
• Human Capital Management Application Administrator
• Human Resource Analyst
• Human Resource Manager
• Human Resource Specialist
• Human Resource VP
• Line Manager
• Payroll Administrator
• Payroll Manager