Oracle Hcm Supervisor Querybaldcirclekidz

• Oracle Hcm Supervisor Querybaldcirclekidz Login
• Oracle Hcm Supervisor Querybaldcirclekidz Interview
• Oracle Hcm Supervisor Querybaldcirclekidz Responsibilities
• Oracle Hcm Supervisor Querybaldcirclekidz Salary

When routing by supervisory hierarchy, the application determines the current user's supervisor (the supervisor listed on the user's primary assignment) and routes to that person. You can choose a. How can we print entire employee hierarchy for each employee using oracle SQL hierarchial query. Sample Employee Table EmpID, ENAME, MGR 1, A, 1 2, B, 1 3, C, 2 4, C, 2 5, C, 3.

Oracle Hcm Supervisor Querybaldcirclekidz Login

In any typical ERP Implementation, there are requirements to prepare multiple reports. Most of the Reports try to display the Assignment related details like Job, Grade, Location, Position, Department, BusinessUnit, Assignment Status along with other fields from different Tables. I am responsible for the sale and account management of Oracle's portfolio of Human Capital Management Software for Eastern Canada. As a consultant/advisor to existing and new Oracle clients, I assist organizations in achieving outstanding business performance through the optimization of their HR systems, processes and technologies.

Organization through Organization Hierarchy
Find the Parent Organization of a Particular Type through Organization Hierarchy on the basis of any organization. For Example I need the Branch Name on the basis of any organization (Department, Sub Department or Section under it.

Query to Get Parent Organization of a Particular Type

Find Organization Manager Query
There is one screen which hold the Organization and Manager relationship. If you want to get manager for any organization, then use following query to get the Manager ID.

Find the List Users that have a particular Responsibility

select rg.user_id,fu.user_name,ppf.employee_number,ppf.full_name,rt.responsibility_name
from fnd_responsibility_tl rt,FND_USER_RESP_GROUPS_DIRECT rg,fnd_user fu, per_all_people_f ppf
where upper(RESPONSIBILITY_NAME) like ‘%APPROVALS MANAGEMENT BUSINESS ANALYST'
and rt.RESPONSIBILITY_ID = rg.RESPONSIBILITY_ID
and rg.user_id = fu.user_id
and fu.employee_id = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
Core HR Queries

Find total Active employee in the company

select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where ppf.current_employee_flag = ‘Y'
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number is not null
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = ‘Employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)
Get Month Wise Hired Employees Head Count

select to_char(ORIGINAL_DATE_OF_HIRE,'MON') MM, COUNT(PERSON_ID) TOTAL from per_people_x
WHERE to_char(ORIGINAL_DATE_OF_HIRE,'RRRR') = ‘2014'
group by to_char(ORIGINAL_DATE_OF_HIRE,'MON')

Provide a List of Active Employees along with their Supervisor's Name and email address
select ppf.employee_number,ppf.full_name,ppf.email_address ,paaf.supervisor_id,sup.full_name,sup.email_address
from per_all_people_f ppf,per_all_assignments_f paaf,per_people_x sup
where ppf.current_employee_flag = ‘Y'
and paaf.supervisor_id (+) = sup.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppf.person_id = paaf.person_id
and ppf.employee_number is not null
and ppf.employee_number = ‘539988'
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = ‘Employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)

Get the Employee Salary Increase Summary

select ppf.employee_number,ppf.full_name,ppp.proposed_salary_n,ppp.change_date
from per_pay_proposals ppp,per_all_people_f ppf,per_all_assignments_f paaf
where ppf.person_id = paaf.person_id
and paaf.assignment_id = ppp.assignment_id
and ppf.employee_number = ‘603167'
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppp.approved = ‘Y'
and change_date = (select min(change_date) from per_pay_proposals where assignment_id = ppp.assignment_id and proposed_salary_n = ppp.proposed_salary_n)
order by ppp.change_date desc

Get the History for An Employee's Transfers in the company

Get the list of employee who were terminated and have been rehired

select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and person_id in
( select person_id
from (
select count(1),person_id
from per_periods_of_service
group by person_id
having count(1) > 1
)
)
Get the List of Terminated Employees

select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and current_employee_flag is null
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = ‘Ex-employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)

Get an Employees Leave History

select a.date_start,a.date_end,a.absence_days,
(select name from per_ABSENCE_ATTENDANCE_types
where ABSENCE_ATTENDANCE_TYPE_ID = a.ABSENCE_ATTENDANCE_TYPE_ID
and business_group_id =a.business_group_id) leave_type
from per_absence_attendances a
where person_id = (select person_id from per_all_people_f where employee_number = ‘47457' and employee_number is not null)
order by date_start desc

Get EIT Based information for employees

select ppf.employee_number,ppf.full_name, ppei.* from per_all_people_f ppf, per_people_extra_info ppei
where ppf.person_id = ppei.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number = ‘603167'
and ppei.information_type = ‘XX_OTHER_DETAILS'

Get SIT Based information for employees

select ppf.employee_number,ppf.full_name,pac.segment10 Type,SEGMENT1 ID_Number,SEGMENT20 IssueDt,SEGMENT30 ExpDt,SEGMENT11 IssuePlace,SEGMENT17 IssuingCountry,SEGMENT12 Issuing_Authority,SEGMENT18 PrimaryFlag
from per_person_analyses ppa,per_analysis_criteria pac,FND_ID_FLEXS fif, FND_ID_FLEX_STRUCTURES fis,FND_ID_FLEX_STRUCTURES_TL stl, per_all_people_f ppf
where ppa.ANALYSIS_CRITERIA_ID = pac.ANALYSIS_CRITERIA_ID
and fif.ID_FLEX_CODE = fis.ID_FLEX_CODE
and ppa.person_id = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number = ‘602201'
–and pac.ID_FLEX_NUM = 50408
and pac.ID_FLEX_NUM = fis.ID_FLEX_NUM
and stl.ID_FLEX_NUM = fis.ID_FLEX_NUM
and stl.ID_FLEX_STRUCTURE_NAME = ‘ID Card for Employee'

Get the List of all Organization which are below an organization in Org Hierarchy

Get the List of all Organization which are Above an organization in Org Hierarchy

Parent and Child Organizations List

select hp.name parent_org, hc.name child_organization
from PER_ORG_STRUCTURE_ELEMENTS pose, PER_ORG_STRUCTURE_VERSIONS posv, hr_all_organization_units hc, hr_all_organization_units hp
where 1=1
and pose.BUSINESS_GROUP_ID = posv.BUSINESS_GROUP_ID
and pose.ORGANIZATION_ID_CHILD = hc.organization_id
and pose.ORGANIZATION_ID_PARENT = hp.organization_id

Get the Oracle Balance Values

SELECT pdb.defined_balance_id,dim.dimension_name
FROM pay_defined_balances pdb,
pay_balance_types typ,
pay_balance_dimensions dim
WHERE pdb.balance_type_id = typ.balance_type_id
AND pdb.balance_dimension_id = dim.balance_dimension_id
AND dim.legislation_code = ( SELECT legislation_code FROM per_business_groups WHERE business_group_id = 83)
AND UPPER (typ.balance_name) = UPPER (‘Indemnity Days')
— AND UPPER (dim.dimension_name) = UPPER (‘Assignment Run');

select pay_balance_pkg.get_value (P_Balance_ID, P_Assignment_Action_ID, trunc(sysdate))
from dual

Oracle Hcm Supervisor Querybaldcirclekidz Interview

Trunc(Sysdate) will return you balance value as of Date, you can use any old date as per your requirement.

Disable Responsibilities for Users

Normally such requirement occurs when you want that no user should be able to logged in during any particular time like some upgrade project or any critical migration activity. You can disable the users by following query. Al though direct updates are not allowed but since this table does not contain object version id column so we used queries mentioned below. Please consult Oracle Support if you want to double check the impact of using this update statement.

update fnd_responsibility a
set a.END_DATE = to_date('05-APR-2016′)
where a.RESPONSIBILITY_KEY in
( SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL OR A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%')
and C.EMPLOYEE_ID is not null
)

In order to reset the responsibilities back to original state

update fnd_responsibility a
set a.END_DATE = NULL
where a.RESPONSIBILITY_KEY in
( SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL OR A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%')
and C.EMPLOYEE_ID is not null
)

Get the Employee Salary Increase Summary

select ppf.employee_number,ppf.full_name,ppp.proposed_salary_n,ppp.change_date
from per_pay_proposals ppp,per_all_people_f ppf,per_all_assignments_f paaf
where ppf.person_id = paaf.person_id
and paaf.assignment_id = ppp.assignment_id
and ppf.employee_number = ‘603167'
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppp.approved = ‘Y'
and change_date = (select min(change_date) from per_pay_proposals where assignment_id = ppp.assignment_id and proposed_salary_n = ppp.proposed_salary_n)
order by ppp.change_date desc

Get the History for An Employee's Transfers in the company

Get the list of employee who were terminated and have been rehired

select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and person_id in
( select person_id
from (
select count(1),person_id
from per_periods_of_service
group by person_id
having count(1) > 1
)
)
Get the List of Terminated Employees

select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and current_employee_flag is null
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = ‘Ex-employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)

Get an Employees Leave History

select a.date_start,a.date_end,a.absence_days,
(select name from per_ABSENCE_ATTENDANCE_types
where ABSENCE_ATTENDANCE_TYPE_ID = a.ABSENCE_ATTENDANCE_TYPE_ID
and business_group_id =a.business_group_id) leave_type
from per_absence_attendances a
where person_id = (select person_id from per_all_people_f where employee_number = ‘47457' and employee_number is not null)
order by date_start desc

Get EIT Based information for employees

select ppf.employee_number,ppf.full_name, ppei.* from per_all_people_f ppf, per_people_extra_info ppei
where ppf.person_id = ppei.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number = ‘603167'
and ppei.information_type = ‘XX_OTHER_DETAILS'

Get SIT Based information for employees

select ppf.employee_number,ppf.full_name,pac.segment10 Type,SEGMENT1 ID_Number,SEGMENT20 IssueDt,SEGMENT30 ExpDt,SEGMENT11 IssuePlace,SEGMENT17 IssuingCountry,SEGMENT12 Issuing_Authority,SEGMENT18 PrimaryFlag
from per_person_analyses ppa,per_analysis_criteria pac,FND_ID_FLEXS fif, FND_ID_FLEX_STRUCTURES fis,FND_ID_FLEX_STRUCTURES_TL stl, per_all_people_f ppf
where ppa.ANALYSIS_CRITERIA_ID = pac.ANALYSIS_CRITERIA_ID
and fif.ID_FLEX_CODE = fis.ID_FLEX_CODE
and ppa.person_id = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number = ‘602201'
–and pac.ID_FLEX_NUM = 50408
and pac.ID_FLEX_NUM = fis.ID_FLEX_NUM
and stl.ID_FLEX_NUM = fis.ID_FLEX_NUM
and stl.ID_FLEX_STRUCTURE_NAME = ‘ID Card for Employee'

Get the List of all Organization which are below an organization in Org Hierarchy

Get the List of all Organization which are Above an organization in Org Hierarchy

Parent and Child Organizations List

select hp.name parent_org, hc.name child_organization
from PER_ORG_STRUCTURE_ELEMENTS pose, PER_ORG_STRUCTURE_VERSIONS posv, hr_all_organization_units hc, hr_all_organization_units hp
where 1=1
and pose.BUSINESS_GROUP_ID = posv.BUSINESS_GROUP_ID
and pose.ORGANIZATION_ID_CHILD = hc.organization_id
and pose.ORGANIZATION_ID_PARENT = hp.organization_id

Get the Oracle Balance Values

SELECT pdb.defined_balance_id,dim.dimension_name
FROM pay_defined_balances pdb,
pay_balance_types typ,
pay_balance_dimensions dim
WHERE pdb.balance_type_id = typ.balance_type_id
AND pdb.balance_dimension_id = dim.balance_dimension_id
AND dim.legislation_code = ( SELECT legislation_code FROM per_business_groups WHERE business_group_id = 83)
AND UPPER (typ.balance_name) = UPPER (‘Indemnity Days')
— AND UPPER (dim.dimension_name) = UPPER (‘Assignment Run');

select pay_balance_pkg.get_value (P_Balance_ID, P_Assignment_Action_ID, trunc(sysdate))
from dual

Oracle Hcm Supervisor Querybaldcirclekidz Interview

Trunc(Sysdate) will return you balance value as of Date, you can use any old date as per your requirement.

Disable Responsibilities for Users

Normally such requirement occurs when you want that no user should be able to logged in during any particular time like some upgrade project or any critical migration activity. You can disable the users by following query. Al though direct updates are not allowed but since this table does not contain object version id column so we used queries mentioned below. Please consult Oracle Support if you want to double check the impact of using this update statement.

update fnd_responsibility a
set a.END_DATE = to_date('05-APR-2016′)
where a.RESPONSIBILITY_KEY in
( SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL OR A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%')
and C.EMPLOYEE_ID is not null
)

In order to reset the responsibilities back to original state

update fnd_responsibility a
set a.END_DATE = NULL
where a.RESPONSIBILITY_KEY in
( SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL OR A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%')
and C.EMPLOYEE_ID is not null
)

Oracle Hcm Supervisor Querybaldcirclekidz Responsibilities

and end_date IS not NULL
and end_date = '05-APR-2016′

Provide the List of Employees current and Previous Assignment Details

(shows Employee Organization Change history. Like wise we can change the query to show the history of Grade, Payroll, Organization, Location, Position, Job change summary)

select
ppf.employee_number
,paaf.assignment_number
,ppf.full_name
,paaf.organization_id current_org_id
,paaf.effective_start_date Curr_org_start_date
,(select name from hr_all_organization_units where organization_id = paaf.organization_id) current_org_name
,paaf_prev.effective_start_date prev_org_start_date
,paaf_prev.organization_id prev_org_id
,(select name from hr_all_organization_units where organization_id = paaf_prev.organization_id) prev_org
from per_all_assignments_f paaf,per_all_assignments_f paaf_prev,pay_people_groups ppg,pay_people_groups ppg_prev,per_all_people_f ppf
where paaf_prev.effective_end_date + 1= paaf.effective_start_date
and paaf_prev.assignment_id = paaf.assignment_id
and paaf_prev.assignment_type = ‘E'
and ppf.employee_number = ‘557331'
and paaf.assignment_type = ‘E'
and paaf.organization_id <> paaf_prev.organization_id
and paaf.PEOPLE_GROUP_ID = ppg.people_group_id
and paaf_prev.PEOPLE_GROUP_ID = ppg_prev.people_group_id
and paaf.effective_start_date between ppf.effective_start_date and ppf.effective_end_date
and paaf.person_id = ppf.person_id
order by paaf_prev.effective_start_date desc

Employee Short Leave Details and Hours Calculation

select paa.person_id,paa.date_start, paa.date_end,time_start,time_end
,round ( ( ((substr(time_end,1,2) -substr(time_start,1,2)) * 60
+ (substr(time_end,4,2) -substr(time_start,4,2))) / 60 ) ,2) Hours
from per_absence_attendances paa,per_absence_Attendance_types paat
where paa.person_id = 68567
and paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paat.name = ‘Short Leave'
and paa.date_start between :P_Period_Start_date and :P_Period_End_date
and paa.date_end between :P_Period_Start_date and :P_Period_End_date

Payroll
——-

OLM

Find the Tests, Applicants and Their Number of Attempt for each Test

select
tests.parent_category,tests.category,tests.catalog_course_obj_code,tests.catalog_course_obj
,folder,tests.test_name,tests.offering_name,
tests.Test_Instance_Name
,mv.employee_number
,mv.full_name
,odb.DATE_BOOKING_PLACED Enrolled_Date
,obst.name test_status
,(select count(1) from ota_attempts where event_id = oe.event_id and user_id = odb.DELEGATE_PERSON_ID and attempt_status in (‘F','P','C') and RAW_STATUS in (‘I', ‘C')
and suspend_data is null) no_of_attempts
,mv.person_id,oe.event_id
fromota_delegate_bookings odb,ota_events oe,OTA_BOOKING_STATUS_TYPES obst,XXALB_CATL_OBJ_TESTS_V tests,per_all_people_f mv
where 1=1
and odb.event_id=oe.event_id
and obst.BOOKING_STATUS_TYPE_ID = odb.BOOKING_STATUS_TYPE_ID
and obst.business_group_id = odb.business_group_id
and oe.event_id = tests.event_id
and DELEGATE_PERSON_ID = mv.person_id
and trunc(sysdate) between mv.effective_start_date and effective_end_date
–and mv.employee_number = ‘603167'
order by oe.event_id,odb.DATE_BOOKING_PLACED

PMS – API to Create Competency Requirement

It create the record in Competency Requirement screen.

Competency requirement can be created either of the following criterias

a. Job

b. Position

c. Business Group

d. Organization

DECLARE

v_competence_element_id number;
v_object_version_number number;
v_type varchar2(50):='REQUIREMENT';
v_business_group_id number:=83;
v_competence_id number:=1002;
v_proficiency_level_id number:=1102;
v_high_proficiency_level_id number:=1106;
v_effective_date_from date:=sysdate;
v_job_id number:=1504;
v_effective_date date:=sysdate;

Oracle Hcm Supervisor Querybaldcirclekidz Salary

BEGIN

HR_COMPETENCE_ELEMENT_API.CREATE_COMPETENCE_ELEMENT( p_validate=>FALSE,
p_competence_element_id=>v_competence_element_id,
p_object_version_number=>v_object_version_number,
p_type=>'REQUIREMENT',
p_business_group_id=>v_business_group_id,
p_competence_id=>v_competence_id,
p_effective_date_from=>v_effective_date_from,
p_job_id=>v_job_id,
p_effective_date=> v_effective_date,
p_mandatory =>'N');

dbms_output.put_line(‘v_competence_element_id='||v_competence_element_id);

exception
when others then
dbms_output.put_line(‘error : ‘ || sqlerrm);
END;

If have like contents in this post and you think it can be helpful to others, please share it at least once in your circle, in this way, you will join me in my cause to Learn Share and Grow.Come on,Lets Grow Together.

Other Related Posts

Useful Self Service Queries
WISTU – What I Suggest To U
Free Online Test
Solved Issued of Empty Email Notification
Steps for creating custom form in Oracle Apps
How to create backend logging mechanism
Useful Apps SQL Queries Pool
Top 10 Apps Technical Interview Questions