Query : Finding Subordinates and their subordinates

SELECT per.EMPLOYEE_NUMBER,per.FULL_NAME
FROM hr.per_all_people_f per
,hr.per_person_type_usages_f usg
,hr.per_person_types ppt

Oracle HRMS Payroll Interview Questions

Q1) What is the difference between a Job and a Position?

Job is general one, whereas Position is specific term to its role and responsibilities.

 JOB:            MANAGER (generic term), SOFTWARE DEVELOPER

 POSITION:  Finance Manager, HR Manager, Junior SOFTWARE ENGINEER, (this is position which is                               specific to the role to be played.

Query : Find employee count

select ppg.group_name, count(*)
from per_all_people_f ppl, per_all_assignments_f paf, pay_people_groups ppg
where ppl.person_id = paf.person_id
and ppl.current_employee_flag = 'Y'
and paf.assignment_type = 'E'

Query : Job Change Date

select papf.employee_number,max(oldpaaf.effective_end_date +1) "Job Change Date"
from per_all_people_f papf,
per_all_assignments_f oldpaaf,
per_all_assignments_f newpaaf

User Hooks

Oracle has provided user hooks to implement custom logic or validation on standard processes.

For example: 

1. Creating a element entry when creating an absence
2. validating the DFF segments in Absence before creating a absence.
3. Validation on creating EITs, Element entries, absence, termination of employee etc

How to create a report for various payroll runs

SELECT < Columns>
from pay_payroll_actions ppa,
pay_all_payrolls_f papf,
per_time_periods ptp

Important Tables in HRMS

Per_all_people_F

per_person_types

per_person_type_usages

per_addresses

Mass Employee Image Upload in Oracle HRMS

There is no Standard API to do mass upload of images into the system. Following is the work around to achieve the same.

Below steps are followed from:
http://vjcoracleapps.blogspot.ae/2013/07/oracle-hrms-api-photo-upload-api.html

IMPORTANT BASE TABLES FOR ORACLE HRMS R12

 Important Base tables for ORACLE HRMS r12

 Some of the very important base tables in HRMS are as follows:

1. per_all_people_f
This table will provide us the very basic information about the employee. The very data from the first screen we see when we open the ‘People -> Enter and Maintain’ form goes into this table.
2. per_all_assignments_f
This table will store all the information which is been entered in the employee assignment form.

Query : To get Active Employees and Terminated Employees

SELECT
PAAF.ASSIGNMENT_ID,
PAPF.PERSON_ID AS PERSON_ID2,
PAPF.FIRST_NAME AS FIRST_NAME,
PAPF.LAST_NAME AS LAST_NAME,

How to Register/Delete Procedure in User Hook

1)     Create API Hook Call (Register Procedure in User Hook)

• Package Name: hr_api_hook_call_api.create_api_hook_call

declare

l_api_hook_call_id      number;

l_object_version_number number;

begin

Basic API to create employee

The below API is used to create a new employee and loads all the basic data required to create a employee.
emp_details_staging_tbl contains the data of employees to be loaded to per_all_people_f table. When p_validateis TRUE the API execution does not copy the data into per_all_people_f table. So it should be validated to FALSE. It is validated to TRUE only when testing the API to check it satisfies all the validations.

How to Purge Employee Record

In two ways this can be done.

1. US HRMS Manager -> People -> Delete Personal Records. Query the person and click delete (red cross in the main menu).

Query :Employee Contact Information

SELECT papf.person_id employee_id, 
papf.full_name employee_name,
papf.effective_start_date employee_start_date,

How to get Net Entitlement of Leaves in Oracle HRMS Payroll

How to get net entitlement of leaves of an employee on a specific date in oracle hrms??

Please refer to the function code below.

CREATE OR REPLACE FUNCTION CUST_GET_NET_ENTITLEMENT (

   P_ASSG_ID            IN   NUMBER,

   P_PAYROLL_ID         IN   NUMBER,

Creating Cost_allocation_keyflex_id

This Procedure will create cost_allocation_keyflex combinations if the passed cost_allocation_keyflex_id does not not exist. I did this because standard API FND_FLEX_EXT.GET_COMBINATION_ID for creating combinations was not populating concatenated segment column. May be because of the bug in the API.

Mass Employee Image Upload in Oracle HRMS

There is no Standard API to do mass upload of images into the system. Following is the work around to achieve the same.

Below steps are followed from:
http://vjcoracleapps.blogspot.ae/2013/07/oracle-hrms-api-photo-upload-api.html

Mass Employee Image Upload in Oracle HRMS

There is no Standard API to do mass upload of images into the system. Following is the work around to achieve the same.

Below steps are followed from:
http://vjcoracleapps.blogspot.ae/2013/07/oracle-hrms-api-photo-upload-api.html

Key Flexfields (KFFs) in HRMS

• Job KFF,
• Grade KFF,
• People Group KFF,
• Position KFF,

Interface from OTL to 3rd party payroll provider

You can use and find the details using following tables

HXC_TIMECARD_SUMMARY
HXC_TIME_BUILDING_BLOCKS
HXC_TIME_ATTRIBUTES