oracle per_assignment_supervisors_f

• Manage VIP Account
• Register for VIP Plan
• VIP Member-Only Content
• HCM Data Loader
• HCM Extract
• BI Publisher
• Fast Formula
• OTBI Analytics
• Personalizations
• Scheduled Processes
• Absence Management
• Performance Management
• Talent Management
• Time & Labor
• HCM Extracts Questions
• HCM Data Loader Questions
• BI Reports Questions
• Report Issues/suggestions
• Your Feedback counts
• Write for Us
• Privacy Policy
• Join Telegram Group
• Join LinkedIn Group
• Join Facebook Page

Most frequently used Tables list in Fusion HCM

• Post category: Others
• Post comments: 1 Comment
• Post last modified: January 30, 2021
• Reading time: 12 mins read

There is no easy way to find out the underlying tables from the application pages. This post serves as a handy reference to the most frequently used business objects and tables list.

Table of Contents

Table Naming Convention in Cloud Applications

If you are new to the table naming convention, please check this post to understand the tables and views naming convention:

Table Naming Convention in Fusion Applications

Most Important Tables list across modules

Core hr work structures entity relationship diagrams.

Core HR Workforce Structures Table Relationships

Core HR Transactional Tables Entity Relationship Diagrams

Core HR Entity Relationship Diagram (ERD) for HCM Cloud

Payroll Element Entry Entity Relationship Diagram

Payroll element entry table relationships erd, sql developer like tool for hcm cloud for free.

If you are looking for a SQL Developer like tool for HCM Cloud , please check out the FREE tool available on cloud from DataFusing team.

FREE Cloud based SQL Developer like tool for Oracle Cloud

FREE HCM Cloud DEMO Vision Instance

If you are looking for a DEMO VISION instance access, please check out this post:

FREE Oracle Fusion Demo Instance (VISION) Access

Note: This list is not the exhaustive list of tables. If you want the complete list of tables, you can refer the below links: Tables and Views for Human Resources 20A Tables and Views for Common Features 20B

You Might Also Like

How to pull UK Calculation Card Info?

Difference between PER_ALL_ASSIGNMENTS_M and _F tables

Session expired

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.

404 Not found

• Skip to content
• Accessibility Policy
• Oracle blogs
• Lorem ipsum dolor
• Oracle Fusion Analytics ,

Using Oracle Fusion HCM Analytics to Help Manage Data Integrity for Oracle Fusion Cloud HCM Records

Oracle Fusion Analytics is an analytics application that helps you answer key business questions and make better-informed decisions. Cultivating data literacy in your organization and maintaining accurate data in your underlying transaction system are two of the most important elements in becoming data-driven. Fusion HCM Analytics not only helps transform data into an easily consumable format, but it also identifies erroneous data, which helps you clean data in the source transaction system via reject records generated every time the data pipeline runs. This article provides guidance on how you can use this metadata to investigate data anomalies and collaborate with Oracle Fusion Cloud HCM support for data cleanup at the source.

This blog describes how you can

• Check the Rejected Report in Fusion HCM Analytics.
• Execute investigation queries to analyze the anomalies in Oracle Fusion Cloud HCM .

Check the Rejected Report in Fusion Analytics

Proceed as follows:

• Log in to Fusion HCM Analytics
• Navigate Home à Projects.
• Further navigate to data visualization Shared Folders à Common à Warehouse Refresh Statistics.

This is a ready-to-use report with various tabs that provide different levels of detail. Alternatively, you can create your report by using the Common – Warehouse Refresh Statistics subject area, as shown in this image:

You can see the  reject record codes  on the report. you are also provided primary keys (identifiers or ids) to identify exactly the records that had issues. the next section describes how you can query these reject codes further., reject codes and corresponding investigation queries,  this section lists the most common error codes you might encounter, along with sql queries you can use for further analysis and investigation..

**Fusion HCM Analytics Prerequisite Steps for Manager Hierarchy

• Run the Refresh Manager Hierarchy process in Fusion Cloud HCM with the following parameters :

Execute investigation queries to analyze the anomalies in Fusion Cloud HCM.

To execute the queries provided above:

• Log in to Oracle Analaytics Publisher and select the data model:
• On the data model screen, select the SQL Query option:
• Enter the  SQL Query anc change the data source to ApplicationDB_HCM:
• After your data model is created, select View Data:

Check the results.

Further Action

You can file a service request with fusion hcm cloud to fix any records that are identified via the sql queries suggested..

To help ensure data integrity, Fusion HCM Analytics blocks anomalous data from being loaded and logs it as rejected. This blog describes the 12 most common data mismatch scenarios you might encounter. Using this information can help you understand and analyze the nature of the data rejects and collaborate with the Fusion Cloud HCM teams on the best approach for reconciling the data. 

For more details on reject records, please see  Tables with Rejection Details .  And to be sure you're set up for success with your Fusion HCM implementation, see    Prerequisites for Implementing Fusion HCM Analytics .

Nupur Joshi

Senior principal product manager, analytics apps for hcm, abhishek bajpai, principal solution architect, oracle analytics.

Abhishek has more than two decades of Architect, Product Pre-Sales, Software design, implementation, and  Consulting experience spread across several Client countries spanning 5 continents encompassing all the major industry verticals. He has extensive experience with various databases, data integration, Analytics, and ML technologies on OCI, Azure, and AWS cloud platforms.

Anirban Majumdar

Director, faw engineering, ranjith annadi, principal member of technical staff.

Abhishek has more than two decades of Architect, Product Pre-Sales, Software design, implementation, and  Consulting experience spread across several Client countries spanning 5 continents encompassing all the major industry verticals. He has extensive experience with various databases, data integration, Analytics, and ML technologies on OCI, Azure, and AWS cloud platforms.

Oracle Fusion Analytics is an analytics application that helps you answer key business questions and make better-informed decisions. Cultivating data literacy in your organization and maintaining accurate data in your underlying transaction system are two of the most important elements in becoming data-driven. Fusion HCM Analytics not only helps transform data into an easily consumable format, but it also identifies erroneous data, which helps you clean data in the source transaction system via reject records generated every time the data pipeline runs. This article provides guidance on how you can use this metadata to investigate data anomalies and collaborate with Oracle Fusion Cloud HCM support for data cleanup at the source.

• Navigate Home à Projects.
• Further navigate to data visualization Shared Folders à Common à Warehouse Refresh Statistics.

This is a ready-to-use report with various tabs that provide different levels of detail. Alternatively, you can create your report by using the Common – Warehouse Refresh Statistics subject area, as shown in this image: \n

You can see the  reject record codes  on the report. you are also provided primary keys (identifiers or ids) to identify exactly the records that had issues. the next section describes how you can query these reject codes further.,  this section lists the most common error codes you might encounter, along with sql queries you can use for further analysis and investigation..

To execute the queries provided above:

• Enter the  SQL Query anc change the data source to ApplicationDB_HCM:

To help ensure data integrity, Fusion HCM Analytics blocks anomalous data from being loaded and logs it as rejected. This blog describes the 12 most common data mismatch scenarios you might encounter. Using this information can help you understand and analyze the nature of the data rejects and collaborate with the Fusion Cloud HCM teams on the best approach for reconciling the data. 

For more details on reject records, please see  Tables with Rejection Details .  And to be sure you're set up for success with your Fusion HCM implementation, see    Prerequisites for Implementing Fusion HCM Analytics .

Previous Post

Access Oracle Analytics Cloud logs through integration with Oracle Cloud Infrastructure Logging

Leverage parameters for dynamic data manipulations, resources for.

• Analyst Reports
• Cloud Economics
• Corporate Responsibility
• Diversity and Inclusion
• Security Practices
• What is Customer Service?
• What is ERP?
• What is Marketing Automation?
• What is Procurement?
• What is Talent Management?
• What is VM?
• Try Oracle Cloud Free Tier
• Oracle Sustainability
• Oracle COVID-19 Response
• Oracle and SailGP
• Oracle and Premier League
• Oracle and Red Bull Racing Honda
• US Sales 1.800.633.0738
• How can we help?
• Subscribe to Oracle Content
• © 2022 Oracle
• Privacy / Do Not Sell My Info

• Skip to content
• Accessibility Policy
• Oracle blogs
• Lorem ipsum dolor
• Integration ,
• SaaS Extension and Customization ,

Enforcing role-based data security over HCM cloud REST API - a worked example.

Introduction

Customers of Oracle HCM Cloud extend it by adding custom user interfaces (UI) in web pages or mobile applications.  HCM REST API is vital to enable this ability to extend, by providing fast and simple access to resources in HCM Cloud. An important requirement for access over REST is for employees to get no more access to information over REST API than they are allowed over HCM cloud UI pages.

There can also be uses cases where customers integrate HCM cloud with other 3rd party and Oracle applications using REST API and for this purpose might use users who are not associated with employees. Such system users might be allowed to access all data.

Let's look at how to control access to REST API using a practical example, after a quick review of essential security concepts in HCM cloud application. 

Role-based security in HCM cloud

Role based security is a fundamental concept in fusion cloud applications. It allows enterprises to secure access to data and workflows by the role of an user in the organization and by user's position in a organizational hierarchy, through set of pre-defined or custom roles those are reusable. This a well-documented concept, so I'll not elaborate here further. The graphic below provides and overview and a reference to documentation is provided below.

Figure-Overview of role based security in Oracle HCM Cloud application

Job roles, by inhering privileges and duty roles, control what type of data and workflows that a user can access. The next task is to refine control of data access further - for example:

• A HR analyst for a department can only view or update employee information for employees in that department
• A manager can only view information about his or her direct and indirect reports

Data roles, by combing a job role and a security profile, allow restricting access to a slice of a type of information, based on predefined security profiles, or through custom criteria defined by SQL statements.  Data roles can also be enforced over REST API.  This comes handy for use cases such as mobile extensions that depend on access of REST API.

Data roles are not pre-defined out of box, as they are specific to organizations. So they are defined during implementations, under “Assign Security Profiles to Role” task.  

Steps to enable access to HCM REST services

HCM users will not be able to access data over HCM REST API, until a pre-defined REST API Duty role or security privileges granted to such a role are assigned. For example, “Use REST Service - Worker Details Read Only” is a pre-defined HCM Duty role that allows access to view worker details.  In summary, here are the steps to enable data security over HCM REST API.

• Create a Custom job role or modify existing job role with a pre-defined HCM REST Duty role
• Create a custom security profile, if necessary, to define custom data access (or pick a pre-defined security profile)
• Using the job role and security profile from previous steps, create a data role. 
• Assign data role to a user to enable data access

At this point, the user is ready to access the desired information over REST API.

Data security example

Figure - a sample employee hierarchy 

In the sample hierarchy of employees shown above, let's look at 3 REST API use cases.

• Use case 1.Lindsey Jacona can only view her own information
• Use case 2.Kerry Lane can only view his and all of his direct and indirect reports' information
• Use case 3.Raymond Harazin can view information about employees under two managers, Sharon Colby and Matthew Schnieder.

The common task for all 3 use cases is to define a job role (or modify an existing job role) that includes duty role "Use REST Service - Worker Details Read Only". Once the job role is available, next step is to define data role and grant the data role to the user.

For use case 1, define a data role that includes the job role and the person security profile "View Own Record". This data role restricts the access to one's own information.

For use case 2, define a data role that includes the job role and the person security profile "View Manager Hierarchy".  This data role restricts access to one's direct and indirect reports in the hierarchy. 

For use case 3, a custom security profile is required. This security profile includes the "View All Workers" person security profile and a custom SQL snippet that restricts the list of workers to those report to managers Sharon Colby and Matthew Schnieder. Here is one working example SQL:

EXISTS( SELECT 1    FROM "FUSION"."PER_ASSIGNMENT_SUPERVISORS_F" "PER_ASSIGNMENT_SUPERVISORS_F"   WHERE  "PER_ASSIGNMENT_SUPERVISORS_F"."MANAGER_ID" IN ( {insert SQL to select person ids} )   AND "PER_ASSIGNMENT_SUPERVISORS_F"."PERSON_ID"=&table_alias.person_id  )

Most use cases are covered by one of the pre-defined security profiles. Use caution while defining custom profiles as the SQL queries can adversely affect application performance. 

HCM Cloud security:  https://docs.oracle.com/en/cloud/saas/applications-common/18b/ochus/an-introduction-to-hcm-security-in-the-cloud.html

Mani Krishnan

Customers of Oracle HCM Cloud extend it by adding custom user interfaces (UI) in web pages or mobile applications.  HCM REST API is vital to enable this ability to extend, by providing fast and simple access to resources in HCM Cloud. An important requirement for access over REST is for employees to get no more access to information over REST API than they are allowed over HCM cloud UI pages.

Let's look at how to control access to REST API using a practical example, after a quick review of essential security concepts in HCM cloud application. 

Role based security is a fundamental concept in fusion cloud applications. It allows enterprises to secure access to data and workflows by the role of an user in the organization and by user's position in a organizational hierarchy, through set of pre-defined or custom roles those are reusable. This a well-documented concept, so I'll not elaborate here further. The graphic below provides and overview and a reference to documentation is provided below.

• A HR analyst for a department can only view or update employee information for employees in that department

Data roles, by combing a job role and a security profile, allow restricting access to a slice of a type of information, based on predefined security profiles, or through custom criteria defined by SQL statements.  Data roles can also be enforced over REST API.  This comes handy for use cases such as mobile extensions that depend on access of REST API.

Data roles are not pre-defined out of box, as they are specific to organizations. So they are defined during implementations, under “Assign Security Profiles to Role” task.  

HCM users will not be able to access data over HCM REST API, until a pre-defined REST API Duty role or security privileges granted to such a role are assigned. For example, “Use REST Service - Worker Details Read Only” is a pre-defined HCM Duty role that allows access to view worker details.  In summary, here are the steps to enable data security over HCM REST API.

• Create a custom security profile, if necessary, to define custom data access (or pick a pre-defined security profile)
• Using the job role and security profile from previous steps, create a data role. 

Figure - a sample employee hierarchy 

In the sample hierarchy of employees shown above, let's look at 3 REST API use cases.

• Use case 2.Kerry Lane can only view his and all of his direct and indirect reports' information

The common task for all 3 use cases is to define a job role (or modify an existing job role) that includes duty role "Use REST Service - Worker Details Read Only". Once the job role is available, next step is to define data role and grant the data role to the user.

For use case 1, define a data role that includes the job role and the person security profile "View Own Record". This data role restricts the access to one's own information.

For use case 2, define a data role that includes the job role and the person security profile "View Manager Hierarchy".  This data role restricts access to one's direct and indirect reports in the hierarchy. 

For use case 3, a custom security profile is required. This security profile includes the "View All Workers" person security profile and a custom SQL snippet that restricts the list of workers to those report to managers Sharon Colby and Matthew Schnieder. Here is one working example SQL:

EXISTS( SELECT 1  \n  FROM "FUSION"."PER_ASSIGNMENT_SUPERVISORS_F" "PER_ASSIGNMENT_SUPERVISORS_F" \n  WHERE  "PER_ASSIGNMENT_SUPERVISORS_F"."MANAGER_ID" IN ( {insert SQL to select person ids} ) \n  AND "PER_ASSIGNMENT_SUPERVISORS_F"."PERSON_ID"=&table_alias.person_id  )

Most use cases are covered by one of the pre-defined security profiles. Use caution while defining custom profiles as the SQL queries can adversely affect application performance. 

HCM Cloud security:  https://docs.oracle.com/en/cloud/saas/applications-common/18b/ochus/an-introduction-to-hcm-security-in-the-cloud.html

Previous Post

OCI VCN and SD-WAN connectivity (Silver Peak)

Architecture approaches for dns on oci: linux vs windows, resources for.

• Analyst Reports
• Cloud Economics
• Corporate Responsibility
• Diversity and Inclusion
• Security Practices
• What is Customer Service?
• What is ERP?
• What is Marketing Automation?
• What is Procurement?
• What is Talent Management?
• What is VM?
• Try Oracle Cloud Free Tier
• Oracle Sustainability
• Oracle COVID-19 Response
• Oracle and SailGP
• Oracle and Premier League
• Oracle and Red Bull Racing Honda
• US Sales 1.800.633.0738
• How can we help?
• Subscribe to Oracle Content
• © 2022 Oracle
• Privacy / Do Not Sell My Info

eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

TABLE: HR.PER_ALL_ASSIGNMENTS_F

Object details.

PER_ALL_ASSIGNMENTS_F is the DateTracked table that holds information about employee assignments. It also holds details of assignments for which an applicant has applied. The ASSIGNMENT_TYPE is E for an employee assignment, and A for an applicant assignment. Employees must have at least one employee assignment at all times in a period of service, and each assignment must have a unique number. Employees can have multiple assignments at any time, but there must always be a designated primary assignment. Applicants must have at least one applicant assignment throughout their application. Each assignment has an assignment status that can change over time.

Storage Details

Primary key: per_assignments_f_pk, foreign keys.

Cut, paste (and edit) the following text to query this object:

Dependencies

[top of page]

• Setting up SQL Connect
• Connecting to Oracle Fusion Cloud
• Running Your First SQL Query
• Intellisense
• Database Browser
• Multi-Line Editing
• Setting Up Advance Table Security
• Exporting Data to CSV/Excel

• SQL IN ACTION
• Frequently Asked Questions (FAQ)
• Software License Agreement
• Installation Instructions
• Release Notes
• Privacy Policy

Direct and Indirect Reports for a Manager

In this query, you can get the details of Managers and their direct/indirect reports from Oracle HCM Cloud.

This query uses following tables from ORacle HCM CLoud –

PER_ASSIGNMENT_SUPERVISORS_F ,

PER_ALL_PEOPLE_F,

PER_ALL_ASSIGNMENTS_M and

PER_ASSIGNMENT_SUPERVISORS_F

tables to get the manager information of an employee.

And here is the output as it looks in HCM Demo environment, after running the query using SQLConnect.

Previous Post HCM Cloud - Time in Job, Time in Position Query

Next post sqlconnect shortcuts.

• Quick Start
• SOFTWARE LICENSE AGREEMENT
• PRIVACY POLICY

© 2024 All Rights Reserved | Splash Business Intelligence Inc

Tables and Views for HCM

Oracle Fusion Cloud HCM

Oracle Fusion Cloud HCM Tables and Views for HCM

Copyright © 2016,2024, Oracle and/or its affiliates.

Author: Shine Mathew

Supervisor Loads using HDL

I have been loading supervisor records for a client with all active supervisors correctly showing up in HCM Cloud. However, when i begin to load the historical supervisor records for inactive employees I have successful loads but the information in cloud does not show appear in any tables. (such as the per_assignment_supervisors_f table that the client is using to store supervisor relationships)

Has anyone else had this problem? and do you think it is an HDL issue or a cloud issue?

Howdy, Stranger!

To view full details, sign in.

Don't have an account? Click here to get started!

• Install App

Oracle Database Discussions

For appeals, questions and feedback, please email [email protected]

Differences between per_all_assignments_f table & per_assignments_f table