How to Retrieve Unique Resignation Actions and Reasons
SELECT ACTIONSB.ACTION_CODE AS A_CODE, ACTIONS.ACTION_NAME AS A_NAME, USAGES.ACTION_REASON_CODE AS U_REASON_CD, REASONS.ACTION_REASON AS ACTION_REASON
SQL Query to identified content section name and content section id
In this blog post, I will show you how to query the Fusion.hrt_profile_typ_sections_vl view to get the names and IDs of the template-based sections. This view contains information about the sections that are defined in the profile-type templates. Template-based sections are those that have the template_based_section_flag set to 'Y'. To get the list of these sections, we can use the following SQL statement:
SQL to find the manager and their direct reportees
select papf_Reportee.person_number, to_char(pasf.EFFECTIVE_START_DATE,'yyyy/dd/mm') start_dt_spr from per_all_people_f papf, per_All_assignments_m paam, PER_ASSIGNMENT_SUPERVISORS_F pasf,
SQL to get the document type, document type id, category code, sub-category code, and legislation code
Each document type has a unique ID, a category code, a sub-category code, a legislation code, and a creation date The following query will select all the columns from the table and sort them by the creation date in descending order
How to identified how many user has assigned the data role with required document record security profile
1. Firstly, we need to run the below query to get the assigned security profile to the data roles. 2. Secondly, we need to create the OTBI report to get the users who have been assigned the data roles.
SQL Query to get the profile id or profile code for an employee
select "PER_ALL_PEOPLE_F"."PERSON_NUMBER" as "PERSON_NUMBER", "HRT_PROFILES_B"."PROFILE_CODE" as "PROFILE_CODE"
Get the list of area of responsibility assigned to the user
select papf.person_number, ppnf.display_name, paaf.assignment_number,
Get the list of work email address to be flagged as primary email
Select pea.email_address_id, papf.person_id, pea.email_type,pea.email_address, case when pea.email_address_id = papf.primary_email_id then 'Y'
SQL Query to get the list of failed approval transactions
SELECT d.*-- h.transaction_id, d.state,d.status,h.module_identifier, h.CREATION_DATE FROM HRC_TXN_HEADER h,HRC_TXN_DATA d WHERE h.Transaction_id = d.Transaction_id and h.transaction_id = 000000000000000
Employee cannot add the specific absence type in the future date
A client wants the employee cannot add the absence type in the future date, it should be today’s day only. We have written the fast formula. DEFAULT FOR IV_START_DATE is '0001/01/01 00:00:00' (Date) INPUTS ARE IV_START_DATE (Date)
SQL Query to get the employee person id with person number
select * from per_all_people_f where person_number= '1734'
SQL Query to get the absence entry id, absence type id, absence start date and absence end date
select per.person_number,PER_ABSENCE_ENTRY_ID ,abs.absence_type_id, abs.name absence_type, to_char(apae.start_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') start_date, to_char(apae.end_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') end_date, duration from ANC_PER_ABS_ENTRIES apae, PER_PERIODS_OF_SERVICE pps, PER_ALL_PEOPLE_F per,
SQL Query to get the content id for license and certification
Select ITEM.CONTENT_ITEM_ID, ITEM.CONTENT_TYPE_ID, ITEM.CONTENT_ITEM_CODE,ITEM.DATE_FROM, ITEM.BUSINESS_GROUP_ID, TL.NAME, TL.ITEM_DESCRIPTION, TYPE.CONTEXT_NAME, ITEM.CREATED_BY, ITEM.CREATION_DATE, ITEM.LAST_UPDATED_BY, ITEM.LAST_UPDATE_DATE
SQL Query to get Goal Plan Assignment id, Goal Plan Id, Assignment Id, Goal Plan Name, Person Number
select "HRG_GOAL_PLN_ASSIGNMENTS"."GOAL_PLAN_ASSIGNMENT_ID" as "GOAL_PLAN_ASSIGNMENT_ID", "HRG_GOAL_PLN_ASSIGNMENTS"."GOAL_PLAN_ID" as "GOAL_PLAN_ID", "HRG_GOAL_PLN_ASSIGNMENTS"."ASSIGNMENT_ID" as "ASSIGNMENT_ID",
SQL Query to get the approval transaction id details
Select Txnd.Status As Txn_Status ,Txnh.Module_Identifier ,p.person_number ,txnh.CREATION_DATE ,Txnh.Transaction_Id
SQL Query to get the list of terminated employees
select "PER_PERIODS_OF_SERVICE"."LAST_UPDATE_DATE" as "LAST_UPDATE_DATE", "PER_PERIODS_OF_SERVICE"."LAST_UPDATED_BY" as "LAST_UPDATED_BY", "PER_ALL_PEOPLE_F"."PERSON_NUMBER" as "PERSON_NUMBER"
SQL Query to get the list of seniority date version
SELECT SeniorityDateSetUp.seniority_date_code, HcmLookup.MEANING,SeniorityDateSetUp.SENIORITY_VERSION FROM fusion.per_seniority_dates_setup SeniorityDateSetUp,
SQL Query to extract those data that have no profile code
Select distinct papf.person_number,profile_code from PER_ALL_PEOPLE_F PAPF, HRT_PROFILES_B HPB where PAPF.PERSON_ID = HPB.PERSON_ID AND Profile_Code is NULL
SQL Query to get only active employees
EXISTS (SELECT 1 FROM PER_ALL_ASSIGNMENTS_M ASG,PER_PERIODS_OF_SERVICE PS WHERE ASG.ASSIGNMENT_TYPE IN('E','C','N','P') AND ASG.EFFECTIVE_LATEST_CHANGE='Y' AND SYSDATE BETWEEN LEAST(SYSDATE,ASG.EFFECTIVE_START_DATE) AND ASG.EFFECTIVE_END_DATE AND
SQL Query to get the parent departments
SELECT DISTINCT dhou.ATTRIBUTE2 as Project_Code, dhou.ATTRIBUTE3 as Expense_Code, dhou.name AS DEPT_NAME, dhou1.name AS PARENT_DEPARTMENT_NAME
Categories
- All Posts
- Absence Management
- Absence Management - Issues
- Benefit Management
- Common for all modules
- Compensation Management
- Compensation Management - Issues
- Global Human Resource
- Global Human Resource - Issues
- Human Resource Helpdesk
- Oracle Learning Cloud
- Oracle Learning Cloud - Issues
- Oracle Recruiting Cloud
- Oracle Recruiting Cloud - Issues
- Talent Management
- Talent Management - Issues
- Technical Contents
- Technical Contents - Fast Formula
- Technical Contents - HDL Templates
- Technical Contents - OTBI Reports
- Technical Contents - Spreadsheet Loader
- Technical Contents - SQL Query
- Technical Contents - SQL-HDL
- Time and Labor
- Time and Labor - Issues