HRMS

Some Fast Formula Tables:

SQL Query to select tables:-

Select * from all_objects where object_type='table' and object_name like 'ff%';

ff_functions
ff_function_parameters
ff_formulas_f
ff_formula_types
ff_database_items
ff_globals_ff

Fast formulas are used for validations under applied rules for calculations.

Its Uses in Payroll:

validate element inputs
Calculate element Pay values &run results during payroll processing.
Specify the rules for skipping an element during payroll processing
perform legislative checks during a payroll run.

Components of a formula:

-Assignment statements
-different types of input including database items,functions,nested expressions and conditions.

1.Assignment & return statements:

 E.g Pay value for element wage:

 wage=hours_worked*Hourly_rate return wage;

2.Constants and variables:-

 E.g Wage=200;

3.Data type:

 -numeric,text,data

4.Functions: Greatest,initcap,instr,lower,rteim,translate,round,etc..

 -convert variables from number to text(to_text),dates to text
  text to date(to_date),text to number(to_num)

5.Compile Fastformula:

 -choose verify button after wiriting the fast formula to compile it.
 -to compile to many formulas together,run the concurrent program
  "Bulk compile formula process"

Fast formula errors:

 -verify time errors(syntax),runtime(data problem),uninitialized variables,divide by zero.

IMPORTANT TABLES IN HRMS:
per_all_people_f:

Tracks Employee Records.
Date track table with primary key person_id.
Can get personal ,professional,taxable location and other informations of the person(employee).

per_all_assignments:

Central table of oracle payroll.
Element entries are stored against assignment record.
Assignment_id is the primary key.
Keeps track of employee position,grade etc.

per_person_type:

Master table of person types(casual,applicant,employees).
person_type_id is the primary key.

Per_periods_of_service:

Period of service details for an employee.

----------------------------------------------------------------------------------------
PAYROLL

pay_element_types_f:

list payroll elements
element_type_id with date track columns are the primary keys.

pay_element_links_f:

Payroll linked elements.
After linking payroll elements to the payroll it gets into employee payroll.
element_link_id with date track columns are the primary keys.

pay_element_entries_f:

Elements attached to an assignment has an entry in this table
.
pay_payroll_actions:

Type of pay and costing sements can be driven out.

BEE(BATCH ELEMENT ENTRIES)

BEE(Batch Element Entries)

Bulk of data added for many exisiting employees.

May be accounting,DFF,KFF elements added to numerous employees at a same time is referred to BEE
Process is as follows:

Example:To add new bonus element for exisiting employees
1.Create a control file with the columns required eg:name of the element,value given to the element
2.Create an table to store the vales to updload the data .
3.Use FNDLOAD for file upload

Example:
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter

**Refer Metalink or Oracle Applications Systems Administrator Guide

4.Create the Batch Header and Batch lines for its respective tables using API's(pay_batch_header.create_batch_header).

Tables to be Populated:Pay_batch_headers,pay_batch_lines

Batch header consist of an identical name for the batch to be uploaded(batch_name,batch_id).

Batch lines consist of values to bonus elements(batch_id).

5.Check the Status of file uploaded in the batch Status.
it will be U-unprocesses,V-Valid,P-processed.

CONVERSTIONS USING API:

hr_person_address_api is used for example purpose.
DECLARE
x_business_group_id NUMBER;
x_person_id NUMBER;
x_address_id NUMBER;
x_obj_no NUMBER;
x_errm VARCHAR2(100);
CURSOR legacy_address_cursor IS
/*replace the hard-coding with your source data*/
Lets say you wish to migrate a person address record of following data in Oracle HRMS
Address Line 1 : Martket Stree
Address Line 2 : London
Date FROM : SYSDATE - 1
Employee number : 90909090
Person Id : 134593
SELECT '90909090' AS employee_number
,'Woodlands Street' AS addr_line1
,'London' AS addr_line2
,'SW1 1DB' AS post_code
,'07968875963' AS tel_no
,134593 AS person_id
/*as you have already migrated this person*/
,trunc(SYSDATE) - 1 date_from
/*you can make this to be the same as start date of person*/
FROM dual
--replace this with your actual source table
;
BEGIN
SELECT business_group_id
INTO x_business_group_id
FROM per_business_groups
WHERE NAME = 'Your Buss Group Name here or Setup Business Group';
FOR p_record IN legacy_address_cursor
LOOP
BEGIN
hr_person_address_api.create_gb_person_address(p_validate => FALSE
,p_effective_date => SYSDATE
,p_pradd_ovlapval_override => FALSE
,p_person_id => p_record.person_id
,p_primary_flag => 'Y'
,p_date_from => p_record.date_from
,p_address_line1 => initcap(p_record.addr_line1)
,p_address_line2 => initcap(p_record.addr_line2)
,p_postcode => p_record.post_code
,p_country => 'GB'
,p_telephone_number => p_record.tel_no
,p_address_id => x_address_id
,p_object_version_number => x_obj_no
,p_date_to => NULL
,p_address_type => NULL
,p_comments => NULL);
dbms_output.put_line('Address for person_id=>'p_record.person_id' has been loaded');
--update the legacy source address table to change migration status of record
EXCEPTION
WHEN OTHERS THEN
x_errm := SQLERRM;
dbms_output.put_line('Error when migrating Address for person_id=>' p_record.person_id ' 'x_errm);
/* log_error(p_record.person_id,x_errm);*/
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
x_errm := SQLERRM;
dbms_output.put_line('Fatal Error ' x_errm);
/* log_error(-1,'Fatal Error ' x_errm);*/
END;
/
staging table is a table where data is held temporarily for transformation, enrichment and validation before it is moved to the final destination. Staging tables are typically used for conversion and interfaces.

 Transformation includes activities such§ as changing formats and data types, e.g. the character string "23-SEP-2006" into the date September 23 2006; mapping one value to another, e.g. from 1 to "M".

 Enrichment is adding new data to the records that were not available§ in the source.

 Validation is checking that the values in fields in a§ record are valid and that the record is internally consistent.

How an Application Life Cycle goes:

1. Evaluate the Essential information’s to make up the core work.
2. Install EBS
3. Start configuring the functional you are going to develop and deploy
4 .Convert the Legacy system and transfer data to EBS
5. Test the system and data flow.
6. Migrate according the client environment.
7.Sustain the configuration and update it.

CRP’s (Conference Room Pilots):-
-Configures Oracle HR & Payroll Applications
-Generates the default settings and loads configuration.
-Loads the reference tables.

Configuration models:-

 1.Single operating company in one country.
 2.Single operating unit with many countries.
 3.Multiple operating unit in one country.
 4.Multiple operating unit in multiple country.
 

Important API's:

HR_ASSIGNMENT_API

CREATE_SECONDARY_EMP_ASG To Create a secondary Assignments for Employee

CREATE_GB_SECONDARY_EMP_ASG To Create a secondary Assignments for UK Employee

CREATE_US_SECONDARY_EMP_ASG To Create a secondary Assignments for US Employee

SUSPEND_EMP_ASG To Suspend The Employee assignments(Temparary)

ACTUAL_TERMINATION_EMP_ASG To Remove All Assignements for employee(Permanent)

UPDATE_EMP_ASG Update The Existing assignment for an Employee

UPDATE_GB_EMP_ASG Update The Existing assignment for an UK Employee

UPDATE_US_EMP_ASG Update The Existing assignment for an US Employee

UPDATE_EMP_ASG_CRITERIA To Update Assignment Criteria for an employee

ACTIVATE_EMP_ASG Use to Activate Suspend Assignment for an Employee

UPDATE_APL_ASG To Update Assignment for an Applicant

TERMINATE_APL_ASG To Terminate Assignment for an Applicant

SET_NEW_PRIMARY_ASG Use to set The purticular assignment as a Primary.

HR_EMPLOYEE_API

CREATE_EMPLOYEE Use to create a New Employee

CREATE_GB_EMPLOYEE Use to create a New UK Employee

CREATE_US_EMPLOYEE Use to create a New US Employee

RE_HIRE_EX_EMPLOYEE Use to rehire ex-employee who got termination Already

GET_PERSON_DETAILS Display The Person details

HIRE_INTO_JOB Hire a perticular person to purticular Job

HR_ENTRY_API

INSERT_ELEMENT_ENTRY Use to create a new element entry

CHECK_SALARY_ADMIN_UPDATES

UPDATE_ELEMENT_ENTRY Use to Update a existing element entry

DELETE_ELEMENT_ENTRY Use to delete a new element entry

HR_GRADE_API

CREATE_GRADE_RATE_VALUE Use to Create a grades

UPDATE_GRADE_RATE_VALUE Use to Update a grades

DELETE_GRADE_RATE_VALUE Use to Delete a grades

HR_JOB_API

CREATE_JOB Use to create a new jos for a puticular job group

UPDATE_JOB Use to Update a jos for a puticular job group

DELETE_JOB Use to Delete a jos for a puticular job group

HR_ORGANIZATION_API

CREATE_ORGANIZATION It's used to create.update and delete Organizations

UPDATE_ORGANIZATION

DELETE_ORGANIZATION

CREATE_ORG_CLASSIFICATION It's used to create.update and delete Org.Classification

ENABLE_ORG_CLASSIFICATION

DISABLE_ORG_CLASSIFICATION

CREATE_ORG_INFORMATION It's used to create.update Organizations

UPDATE_ORG_INFORMATION

CREATE_BUSINESS_GROUP Used to create Business Group

CREATE_OPERATING_UNIT Used to create Operating units

CREATE_LEGAL_ENTITY Used to create legal entity

CREATE_COMPANY_COST_CENTER

HR_PERSON_API

UPDATE_PERSON Used to Update the persons records

UPDATE_GB_PERSON

UPDATE_US_PERSON 　

Reports in HRMS:

Absence report
advance pay listing
advance pay listing
assignment status report
assignment unavilable for processing report
aduit report
cost breakdown report(budget over a period)
Calculate commitment(costing)
Element result lisiting report
element link details report
Employee payroll movements report
full person details report set
pay advice report and more..

Data Pump:

Data Pump:

Allowing to load existing data from HRMS system into single Batch interface table
-uses appropriate API
-Data Pump engine used for data load process
-HR_API_modules Contains all HRMS API.

Advantages:

-can map same interface table to map data into different Business API's
-e.g. Create_employee and create_peron_address API's can be called by data pump int he same batch.
-Also rum multithreaded, restarted after error fixed.

Key components of data pump:

1. Meta Mapper:- Generates specific procedures & update data in interface table

pl/sql code: HR_PUMP_META_MAPPER.GENERATEALL;

or

HR_PUMP_META_MAPPER.GENERATE ('HR_EMPLOYEE_API','CREATE_EMPLOYEE');

Meta mapper view will be HRDPV_GREATE_EMPLOYEE

2. Batch Tables:-

Used to hold data to be uploaded

HR_PUMP_BATCH_HEADERS
HR_PUMP_Batch_LINES



HR_PUMP_BATCH_HEADERS: Has

batch_id
batch_name
batch status
business_group_name

HR_PUMP_Batch_LINES: Has

batch_line_id
batch_id
api_module_id

Batch Line table has 230 columns mapped to API parameters


3. Data Pump Procedure:
Meta mapper creates API Procedures to be used to insert records into batch line tables.

E.g.  HRDPP_CREATE_EMPLOYEE.INSERT_BATCH_LINES

Generated views are procedure can be seen undar HRDPV_view & HRDPP_Procedure.

Execute HR_PUMP_META_MAPPER.HELP ('HR_EMPLOYEE_API','CREATE_EMPLOYEE');

4. Data pump Engine:-Standard concurrent program that performs upload of data with two essential parameters batch name and validate.