Human Capital Management
- Jan 8, 2022
SQL Query to Get Department, Grade, Job details
This article will provide SQL query snippet to pull up Department, Grade, Job details. There are two scenarios :
Latest Department, Grade, Job with their name, effective date and other basic details.
Department, Grade, Job change complete history.
This article will cover both the scenarios.
Scenario 1 - Latest Department, Grade, Job with their name, effective date and other basic details.
Scenario 2 - Department, Grade, Job change complete history.
Note - The above two SQL queries are pulling up the Department, Grade, Job information but the same logic can be extended to fetch any of the other relevant information with similar criteria.
Try this out, and play around!
Recent Posts
Commonly Used Administrator Profile Values in HCM
How to Create a BIP Report in Oracle Fusion Hcm
Enabling Environmental Health and safety (EHS) module in 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 tables PER_ALL_ASSIGNMENTS_M and _F differences
- Post category: BI Publisher
- Post comments: 3 Comments
- Post last modified: August 7, 2020
- Reading time: 2 mins read
In this article we will look into the difference between the PER_ALL_ASSIGNMENTS_M and _F tables which is the most confusing part for developers when they want to build queries based on the assignment information tables.
There are multiple tables for Assignment Information and we will look into details on which table stores what information:
PER_ALL_ASSIGNMENTS_M – This is the core table which stores all assignment related information.
PER_ALL_ASSIGNMENTS_F – This is a view based our of PER_ALL_ASSIGNMENTS_M table and picks up only the latest row for each effective date. If multiple transactions are not there for a day, both _M and _F fetches same data for that day. If there are MCPD (Multiple Changes Per Day) then _F will fetch only one row whereas _M will fetch multiple rows. EFFECTIVE_LATEST_CHANGE = ‘Y’ is the only condition added to the PER_ALL_ASSIGNMENTS_M to get _F view.
PER_ALL_ASSIGNMENTS_M_ – This is the audit table for core assignment table. If Auditing for Assignment business object is enabled, then data will get stored in this table for every update/delete/correction on assignment information. All Audit tables end with underscore ( _ ) character.
Hope you are now clear on the difference between these tables and ready to use then as per your requirement.
You Might Also Like
How to fetch Payroll Run Results when employees have multiple assignments?
How to generate pipe delimited output from BI Report using a dummy template?
Variables and its usage in RTF template
Hi Sricharan, If there is any changes done in correction mode the AUDIT_ACTION_TYPE_ column value will be ‘UPDATE’ in per_all_assignment_M_ table. How to differentiate/find between update and correction in table.
Thanks, Pavan.
You have raised an interesting question and here is the answer for it..
,case when audit_action_type_ = ‘UPDATE’ then ‘Update’ when audit_action_type_ = ‘HISTORY’ and creation_date = last_update_date then ‘Creation’ when audit_action_type_ = ‘HISTORY’ and creation_date <> last_update_date then ‘History’ when audit_action_type_ = ‘INSERT’ then ‘New’ when audit_action_type_ = ‘DELETE’ then ‘Deleted’ end Audit_Action
I will do another post on Audit Query to make it clear. Thanks, sricharan
Hi Sricharan,
Thanks for your replay. May question is how to find correction records. Because even if you do correction that record is storing as UPDATE in Audit table.
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.
To view an employee's assignment history:
IMAGES
COMMENTS
This guide contains the information about tables within Oracle HCM Cloud and their columns, primary keys, and indexes. The guide also includes the information about views within Oracle HCM Cloud along with the columns and queries associated with each view.
Table to get Assignment change history. Content (required): Hi All, We got a requirement to show the assignment changes (Like Transfer). From which tables we can get assignment changes history? Kindly help on this. Thanks, Venkatesan R. Version (include the version you are using, if applicable):
Describes Tables and Views to integrate with or extend Oracle HCM Cloud.
This article will provide SQL query snippet to pull up Department, Grade, Job details. There are two scenarios : Latest Department, Grade, Job with their name, effective date and other basic details. Department, Grade, Job change complete history. This article will cover both the scenarios.
Do you know which table to use while building query on assignment information? PER_ALL_ASSIGNMENTS_M or PER_ALL_ASSIGNMENTS_F or PER_ALL_ASSIGNMENTS_M_? Read on to know more details.
Oracle Fusion Human Capital Management (HCM), the Assignment Table, is essential in storing information related to employee assignments. This includes data like position, department,...
For example, the Extract Assignment Basic History user entity sets the assignment effective start date as effective date first and then retrieves the data, unlike other user entities which use the effective date only.
Assignment History. Use the Assignment History window to view the history of all an employee's assignments, both current and in previous periods of service. To view an employee's assignment history: 1. In the Current field, select Yes, No or All. Yes selects the list of current employees.
There is a requirement to get the historical data of assignment from 'PER_EXT_ASSIGNMENT_BASIC_HISTORY_UE'. Scenario: When multiple updates are done on assignment with same effective date, there is a sequence of update to identify them.
From which table we can get Change Attribute Value displayed in assignment history page? Solution. In this Document. My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.