assignment status in oracle fusion hcm table

Assignment Category and Worker Category Table in Oracle Fusion HCM

• Post author: Oracle Cloud Hub
• Post last modified: 26 February 2024

Below Query will help us fetch the Assignment Category and Worker Category for a given assignment.

Above code will return the below output

Navigate to the assignment details screen to view data from UI

You Might Also Like

Fast Formula Types based on modules in Oracle Fusion HCM Cloud

Oracle Fusion HCM Technical Consultant Skills

Query to Fetch the Payroll Assigned to a Person in Oracle Fusion HCM

• 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

Assignment Type and its importance in HCM Cloud

• Post category: Core HR
• Post comments: 0 Comments
• Post last modified: July 1, 2021
• Reading time: 4 mins read

In this article, we will look into the Assignment Types available in HCM Cloud and how to use them in the reports.

Each worker type is denoted by its alphabet value in the ASSIGNMENT_TYPE and PERIOD_TYPE columns of the PER_ALL_ASSIGNMENTS_M and PER_PERIODS_OF_SERVICE tables respectively. Both ASSIGNMENT_TYPE and PERIOD_TYPE should have the same values for the given work terms record.

For example, pending worker is denoted by P, employee by E, contingent worker by C, nonworker by N, and offer by O.

Both Assignment and Work Terms information will be stored in PER_ALL_ASSIGNMENTS_M table with a different ASSIGNMENT_TYPE.

Now, if you are building reports based on the Assignment table, we should use the right Assignment types.

If we have to pull Employees only, we need to use ASSIGNMENT_TYPE = ‘E’. If you use ASSIGNMENT_TYPE <>’ET’ then this query might work if you ONLY have Employees in the client POD without any contingent workers and pending workers, else it will pull additional employees on the report.

I have seen many cases where the developers used ASSIGNMENT_TYPE NOT IN (‘ET’,’CT’).. In this case, it will pull Employees, Contingent Workers, Non-Workers, and Offers. Instead of writing like this, it is better to rewrite it as ASSIGNMENT_TYPE IN (‘E’,’C’) if there is a need for pulling Employees and Contingent Workers.

You Might Also Like

How to Mask Work Email Address in Dev or Test instance post P2T Refresh

How to store Public Documents on Content Server and share a hyperlink on the application

How to Mass Download Document of Records from 21D?

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.

Oracle Analytics Cloud and Server

• All Categories
• 12.7K Forums
• User Groups
• 1 Announcements
• Find Partners
• For Partners
• Hall of Fame: Spotlights
• Hall of Fame: Monthly Highlights
• Hall of Fame: Leaderboard

HCM | SQL query to get details of the employees belonging to departments below the selected departme

Requirement :

1) Report should display employees belonging to the departments of the selected organization type. 2) Report should also display employees belonging to departments below the selected departments as per organization hierarchy.

Scenario : Suppose the department of the employee is DEPARTMENT4.3 then the report should fetch the data of the employee whose department is DEPARTMENT4.3 along with the employees who belong to  DEPARTMENT4.3.1,  DEPARTMENT4.3.2,  DEPARTMENT4.3.3,  DEPARTMENT4.3.3.1.

Organization tree hierarchy :

NODE1:LEGAL EMPLOYER(ROOT)

NODE2: DEPARTMENT1

NODE3:          DEPARTMENT1.1

NODE4:                          DEPARTMENT1.1.1

                                      DEPARTMENT1.1.2

NODE3:           DEPARTMENT1.2

NODE2: DEPARTMENT2

NODE2: DEPARTMENT3

NODE3:           DEPARTMENT3.1

                        DEPARTMENT3.2

NODE2: DEPARTMENT4

NODE3:           DEPARTMENT4.1

                        DEPARTMENT4.2

                        DEPARTMENT4.3

NODE4:                         DEPARTMENT4.3.1

                                      DEPARTMENT4.3.2

                                      DEPARTMENT4.3.3

NODE5:                                          DEPARTMENT4.3.3.1

PS : I am able to achieve the first requirement by using attached SQL code but I need help to achieve the second requirement. I am not able to get any lead how should I accomplish the second requirement like does it require code modification or can be handled in template( currently using RTF template of excel type).

Code Snippet

• Oracle Fusion Analytics

Hi @Arti P , did you get a solution for this?

Dear All, Pls give me the complete (employee/person/worker) query to get all the details for these employees load through HDL worker.dat (worker,person name,person email,work relationship,work terms,assignments) please help me. thank you

Hi @User_Z86UI ,

Seems this is related to HCM product. Actually this forum is for Oracle Analytics Cloud and Server product, so please post your question/request in the proper forum. Try below forum and see if that helps.

https://community.oracle.com/customerconnect/categories/hcm-reporting-and-analytics-for-hcm

Digital Engineering & Experience

Data, automation & ai, oracle cloud.

• Cloud Enhancement & Managed Services

Mastek "iConniX" - Generative AI Portfolio

• Products Platforms & Innovation icxPro - Intelligent Part Assistant iLeaseFinPro Vehicle Leasing Software Warehouse 360 Enterprise Workforce Scheduler Connected Enterprise

Oracle Cloud & Enterprise Apps

Salesforce cloud & industry solutions, platforms & innovation.

• INDUSTRIES Public Sector & Government Healthcare & Life Sciences Retail & Consumer Manufacturing & Technology Financial Services Higher Education Construction & Engineering Hospitality Transportation & Logistics High Tech Media & Entertainment
• PARTNERS About Partnerships Oracle Partner Salesforce Partner AWS Partner Microsoft Partner Snowflake Premier Partner UiPath Partner ServiceNow Partner
• Insights News Press Releases Blog Resources Webinars & Events
• MST SOLUTIONS
• BIZANALYTICA
• Digital Engineering & Experience Cloud Migration Cloud Engineering Low Code DevSecOps Quality Engineering MACH Platform Engineering
• Data, Automation & AI Cloud Data Modernization Data Management Data Governance Business Intelligence & Analytics Intelligent Automation Decision Sciences & ML, AI Basecamp
• Oracle Cloud Oracle Cloud Applications Glide 4.0 Oracle E-Business Suite Oracle Consulting AdvantEdge IaaS SAP to Cloud Migration PeopleSoft to Cloud Migration Oracle Analytics Cloud Oracle Cloud Infrastructure Oracle Autonomous Data Warehouse
• Salesforce MuleSoft Experience Cloud Sales Cloud Servide Cloud Marketing Cloud Industry Cloud Commerce Cloud Data Cloud CRM
• Platforms & Innovation
• icxPro – Intelligent Part Assistant
• iLeaseFinPro Vehicle Leasing Software
• Warehouse 360
• Enterprise Workforce Scheduler
• Connected Enterprise
• Public Sector & Government
• Healthcare & Life Sciences
• Retail & Consumer
• Manufacturing & Technology
• Financial Services
• Higher Education
• Construction & Engineering
• Hospitality
• Transportation & Logistics
• Media & Entertainment
• About Partnerships
• Oracle Partner
• Salesforce Partner
• AWS Partner
• Microsoft Partner
• Snowflake Premier Partner
• UiPath Partner
• ServiceNow Partner
• Press Releases
• Webinars & Events

Mastek Blog

Understanding common lookups in oracle hcm.

04-Oct-2021 02:33:00 / by Nilesh Pawar

The HR department has to maintain personal, legislation-specific, and organization-related data for all employees in their HR applications. It is crucial to properly manage and store this data as it will be used for reporting. Furthermore, this data will also be used by other applications like payroll and outbound interfaces.

Oracle HCM Cloud   offers all such data fields and their values out of the box, which helps customers adhere to the legislative rules and properly maintain the data. Oracle offers its customers the flexibility to manage values for these data fields by using the functionality of lookups.

Lookups hold a list of values that appear for various fields within an application.

There are three categories of lookups:

1. Standard lookups

2. Common lookups

3. Set-enabled lookups

What are the Common Lookups in Oracle HCM?

Here are some essential details about common lookups.

Common lookups are predefined lookups that allow users to add new values or enable/disable existing values. These are generally maintained by admin users.

To enter a value for the fields within the application, a user can select any one of the values from the available list. There are predefined lookup types available for various fields in the application.

For example, 'Assignment Category' is one of the fields on the employee's assignment screen that contains a list of values. The list of values that appear for this field comes from lookup. The lookup type for this is 'EMP_CAT'.

Duplicate lookup codes are not allowed in the lookup type.

Lookup Customization

Oracle Fusion contains certain predefined lookups that you cannot disable values for or add new values to. However, there are a few lookups that allow you to disable values and also add some new values. So, it all depends on the lookup configuration level, as it helps you check what level of customization is possible for a lookup type.

There are three different types of common lookups available in Oracle Fusion that decide the customization for the lookup:

1. User lookup   – This is a flexible type of lookup; you can add new values and enable/disable the existing values to it.

2. Extensible lookup   – In this type of lookup, you can add new values but cannot disable the existing values.

3. System lookup   – System lookup does not allow you to add or disable the existing values.

Lookup Type and Its Components

Lookup Type   – Here, you can add values to the lookup. Note that this field name will appear on the setup screen and not on the application screen, where users usually make an entry into the application.

Meaning   – This is the name of the field that users will see on the screen, where they will add the value.

Module   – This is the name of the module to which this lookup belongs. It might be named as HR or Absence.

Lookup Configuration level   – As explained in the above section, lookup configuration has three values:

We can add values, known as lookup codes, in each lookup type.

The lookup codes consist of:

Lookup Code   – This signifies the value of each code that we have added. It is not visible to the users but it is used to uniquely identify values added to the lookup type.

Display Sequence   – This is used to mention the sequence of values they should appear in, for the users to select. It can be 1, 2, 3, etc.

Enabled   – The Checkbox helps you to enable or disable any particular lookup code.

Start Date and End Date   – You can specify a date range for each lookup code to make it available only within that range for users to select. If this is left blank, then it is always available for users.

Meaning   – This term will appear in the list of values on the UI for a specific field, which the user can see and select from the drop-down menu. It is associated with the lookup code.

Tag   – Tag is used for localizing the lookup code. For example, if we put +GB as Tag then this value will appear only for Great Britain localization; and if we put –GB, then it will not appear for Great Britain. If we leave this blank, it will appear for all areas.

You can create a new lookup type or enable/disable values by using the task under Setup and Maintenance -> Manage Common Lookups

Here is an example for lookup type 'EMP_CAT':

Business Case

If any customer based in GB wants to enable the 'Transgender' value for 'Gender', which is already present in the lookup type, they can navigate to the Manage Common lookups task. Since the lookup type for Gender is 'Sex', select the value 'Transgender' in the lookup codes section, and in the tag column enter '+GB'. Click on Save and Close.

To get all the details of lookups in the application, simply use the below query:

Select * from hcm_lookups;

So, this is how common lookups in Oracle HCM capture information for employees and provide the flexibility to manage value in these data fields.

Topics: Oracle , hcm

Written by Nilesh Pawar

Oracle Fusion HCM Consultant

Subscribe to Email Updates

Lists by topic.

• Digital Transformation (74)
• Retail (34)
• Salesforce (29)
• Oracle (22)
• Healthcare (21)
• Retail Tech (19)
• Government (17)
• Financial Services (14)
• Testing (14)
• Vertical Vibe (13)
• Artificial Intelligence (12)
• Banking (11)
• Cloud migration (10)
• Health Cloud (10)
• Cloud Technology (9)
• Customer Experience (9)
• Public Sector (9)
• CEO Insight (8)
• Machine Learning (8)
• UK Public Sector (8)
• Digital Service Design (7)
• data analysis (7)
• Business Process Automation (6)
• Cloud Enhancement Services (6)
• Data Virtualisation (6)
• Future Tech (6)
• TechNovation (6)
• customer data platforms (6)
• retail operations (6)
• Data Security (5)
• Managed Services (5)
• Supply Chain (5)
• UK Central Government (5)
• data modernisation (5)
• research (5)
• Blockchain (4)
• Digital Workforce (4)
• Digital commerce (4)
• Intelligent Automation (4)
• Test Automation (4)
• Transformation (4)
• cloud implementation (4)
• data governance (4)
• edge computing (4)
• leadership (4)
• mortgages (4)
• mulesoft (4)
• thoughtleadership (4)
• AI Strategy (3)
• AI technologies (3)
• Data management (3)
• Fintech (3)
• Government Digital services (3)
• Government Tech (3)
• Graduate Programme (3)
• Manufacturing (3)
• Oracle ERP Cloud (3)
• Oracle Partner (3)
• RPA Implementation (3)
• Robotic Automation (3)
• business acceleration (3)
• business outcomes (3)
• consumer market (3)
• metaverse (3)
• oracle HCM (3)
• strategy (3)
• work management (3)
• workplace (3)
• Automation Testing (2)
• Business Intelligence (2)
• Cloud Adoption (2)
• Cloud-based AI (2)
• Customer Engagement (2)
• Customer Insight (2)
• Data Science (2)
• Data Visualisation (2)
• DataOps (2)
• DevOps culture (2)
• Digital Banking (2)
• Global Highlights (2)
• IT service management (ITSM) (2)
• Identity & Access Management (2)
• Intelligent Automation tools (2)
• Intelligent Automation vs RPA (2)
• Intelligent Process Automation (2)
• Mobile Testing (2)
• Operational Resilience (2)
• Oracle Cloud Application (2)
• Security (2)
• Spend Analytics (2)
• Technology (2)
• analytics (2)
• automating systems and processes (2)
• business analysis (2)
• business uncertainty (2)
• cybersecurity (2)
• data integration (2)
• data migration (2)
• data protection (2)
• data quality (2)
• data-driven intelligence (2)
• diversity and inclusion (2)
• ecommmerce (2)
• erp cloud implementation (2)
• erp consultant (2)
• governance (2)
• human resources (2)
• post pandemic (2)
• work from home (2)
• #DevSecOps #SecOps #IAST #DAST #SAST #RASP (1)
• AI Algorithms (1)
• AI in the social sector (1)
• Agile BI (1)
• Agile Testing (1)
• Agile with GDS (1)
• Automated Testing (1)
• Automation (1)
• BizAnalytica (1)
• CRM transformation (1)
• Chat Bots (1)
• Cognitive Computing (1)
• Cognitive RPA (1)
• Cognitive Technologies (1)
• Compatibility Testing (1)
• Continous Delivery (1)
• DART Framework (1)
• Data Storage (1)
• Data availability (1)
• Deep Learning (1)
• DevSecOps (1)
• EPM Cloud (1)
• Education (1)
• Engineering colleges in Mumbai (1)
• Financial Consolidation and Close (1)
• IT consulting (1)
• Innovation (1)
• Low-Code (1)
• MOT Framework (1)
• Mobile Content (1)
• Natural Language Processing (1)
• Oracle EPBCS (1)
• Oracle ERP (1)
• Oracle Field Service Cloud (1)
• Project Deep Blue (1)
• Project Management (1)
• Recovery Testing (1)
• SAP ECC (1)
• Salesforce Lightning (1)
• Scalability (1)
• Scaling AI (1)
• Semiconductor (1)
• Serverless Cloud Architectures (1)
• Service Management (1)
• Smart Cities (1)
• Smart Contracts (1)
• Solution Showcase (1)
• Tech consultant (1)
• Test Maturity (1)
• Trends in Testing (1)
• Workers Compensation (1)
• analytics practice (1)
• biometrics (1)
• branding (1)
• careers decomplexed (1)
• cloud modernization (1)
• connected enterprise (1)
• data modelling (1)
• data table (1)
• decomplexify (1)
• development (1)
• employee engagement (1)
• employee experience (1)
• financial crime (1)
• healthcare data (1)
• higher education (1)
• industrial iot (1)
• logistics (1)
• magento (1)
• marketing (1)
• outsystems (1)
• planning and budgeting (1)
• predective analytics (1)
• productivity (1)
• provider (1)
• public safety (1)
• talent acquisition (1)
• testing partnership (1)
• time management (1)

Posts by Topic

Recent posts.

Oracle Fusion HCM Tables

Oracle Fusion HCM uses a wide range of database tables to store various types of information related to human capital management processes. These tables capture employee data, organizational structures, compensation details, performance records, and much more. Here are some key categories of tables within Oracle Fusion HCM:

• Person Management Tables:
• PER_PERSON_NAMES_F: Stores basic personal information about individuals.
• PER_PERSON_TYPES: Defines different types of people (e.g., employees, contingent workers).
• PER_PEOPLE_F: Contains additional details about individuals, such as contact information and employment status.
• Workforce Management Tables:
• PER_ASSIGNMENT_STATUS_TYPES: Defines various assignment statuses (e.g., active, terminated).
• PER_PERIODS_OF_SERVICE: Stores information about an individual’s periods of service within the organization.
• PER_ASSIGNMENTS_F: Contains assignment-related details, such as job, position, and location.
• Compensation Management Tables:
• PAY_PAYROLL_REL_GROUPS: Defines payroll relationships and groups.
• PAY_ELEMENT_ENTRIES_F: Stores information about compensation elements associated with an individual’s pay.
• PAY_RUN_RESULTS: Contains payroll results, including earnings and deductions.
• Performance Management Tables:
• PER_PERFORMANCE_RATINGS: Stores performance ratings and associated details.
• PER_OBJECTIVES: Contains performance objectives and goals for employees.
• PER_PERFORMANCE_DOCUMENTS: Stores performance documents and reviews.
• Talent Management Tables:
• PER_GOAL_DEFINITIONS: Stores predefined goal definitions for talent management.
• PER_GOAL_LADDERS: Contains goal ladder information to track progress.
• PER_CAREER_PLAN_STEPS: Stores steps within a career development plan.
• Organizational Management Tables:
• HR_ALL_ORGANIZATION_UNITS: Contains details about organizational units and their hierarchies.
• HR_LOCATIONS: Stores information about physical locations where employees work.
• Training and Learning Management Tables:
• TLE_OFFERING_ATTENDANCES: Stores attendance and completion details for training offerings.
• TLE_ENROLLMENTS: Contains enrollment information for training courses.
• Recruitment Management Tables:
• PER_JOB_OPENINGS: Stores job openings and associated information.
• PER_RECRUITMENT_AGENCY_CONTACTS: Contains contact details of recruitment agencies.
• Benefits Management Tables:
• BEN_OPTIONS: Stores available benefits options.
• BEN_PARTICIPANTS: Contains information about participants in benefits programs.
• Security and Access Control Tables:
• FND_USER: Stores user information, including usernames and email addresses.
• FND_USER_RESP_GROUPS: Links users to roles and responsibilities.

These are just a selection of tables within Oracle Fusion HCM. The application uses numerous other tables to manage different aspects of HR processes and data. Understanding these tables and their relationships is crucial for efficient data management and reporting within Oracle Fusion HCM.

Oracle Fusion HCM Training Demo Day 1 Video:

Conclusion:.

Unogeeks is the No.1 Training Institute for Fusion HCM Training. Anyone Disagree? Please drop in a comment

You can check out our Oracle Fusion HCM Training details here Oracle Fusion HCM Training

You can check out our other latest blogs on Oracle Fusion HCM Training in this Oracle Fusion HCM Blogs

———————————-

For Training inquiries:

Call/Whatsapp: +91 73960 33555

Mail us at: [email protected]

Our Website ➜  https://unogeeks.com

Instagram:  https://www.instagram.com/unogeeks

Facebook:  https://www.facebook.com/UnogeeksSoftwareTrainingInstitute

Twitter:  https://twitter.com/unogeeks

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

Learn to Grow

HDL – Updating an attribute on Assignment

Technical consultants encounter many scenario’s where they have to update one or two attributes on the assignment data post initial data load. This can happen during any phase of the new implementation or post go-live. For example, there is a need to update the assignment category for a set of workers. In such scenario’s, the approach is to extract the already uploaded assignment data with minimum required attributes and append the attributes for assignment category and upload it back to Fusion using HDL.

The key thing here is to extract the minimum required data for Assignment and WorkTerms. The mandatory fields required for an update using HDL can be checked from:

My Client Groups -> Data Exchange -> View Business Objects -> Worker -> WorkTerms -> Assignment

Validated Version :- 20D

Sample Query to extract the minimum required attributes for Assignments and Workterms in order to update assignment category (AssignmentCategory):

You can modify the above query to include any attributes which you want to update. For example, sample file to update Notice Period will look like:

Sample RTF File:

• Tables and Views for SCM

DOO_STATUS_RULESET_ASSIGNMENTS

Defines the mapping between a status rule set and the category of fulfillment lines to apply it to.

Schema: FUSION

Object owner: DOO

Object type: TABLE

Tablespace: REFERENCE

Primary Key

Foreign keys.