- OracleApps Epicenter - http://www.oracleappshub.com -

Bespoke Application & Utilizing HR/Payroll APIs

Before start of the topic , let me tell you intresting fact about the word "bespoke application".

Long time back I heard this term while working with a UK based client, in a telecon its sound like "They need a bespoke application with Oracle to cater Billing with Front end” ..What? The only time I had heard the words ‘spoke’ and ‘application’ in the same sentence was when someone told me about an application built on top of a hub and spoke architecture .Well after that , I realized that ‘bespoke application’ means the same ‘custom built application’ or simply ‘custom application’ that we normally used to called as. I really donot know why UK English don't use word custom here. Its sound Interesting localization tidbit.

Bespoke (pronounced bee-SPOHK)

Therefore , "Bespoke [1]" was most commonly used for suits made specifically for a customer without the use of a pattern. In effect, the length of fabric was "spoken for".

The Need

IT, Health care based industry are Manpower intensive, therefore Human Capital management system is really is need to cater today growing need. Though Integrated system like Oracle or people soft or SAP there is capability of maintaining each function, but still companies prefer to have a front end application for maintaining some of the key information for employee management system.Therefore Custom web based application possibility can not be ruled out.

If this is the case inhouse development team would like to access the HRMS/Payroll via API's so that they can have there own presentation layer.

1. Is this possible ? Then How?
2. Where do I find the API documentation?

So answer of these questions are

1. Yes, it possible. You need to identify the tables and API's

2. There are enough documentation available for this.

Just Follow these post, you will get answer of most of your query.

Its all about Table and API

Such kind of Bespoke/custom application requires these Oracle tables access which are divided into three major tracks.

1.Reference Tables : What you really required

  • Business groups, payrolls related information : In this set you requires company and payroll reference information , which is required to validate employee information for your company
    • You can map with these payroll table
      • hr_organization_units
      • hr_locations
      • pay_payrolls
      • fnd_common_lookups
  • Counties, cities, states, zips : Contains the address info for employee address and tax validation. Tax validation is required if there is seperate rule for taxation or may have multiorg installation.
    • You can map with these payroll table
      • pay_us_states
      • pay_us_counties
      • pay_us_city_names
      • pay_us_zip_codes
  • Deductions and levies : These are all the deductions and levies whatever configured in Oracle Apps.
    • You can map with these payroll table
      • pay_element_types_f
      • pay_element_links_f
      • pay_element_values

2. Employee Tables : What you really required

  • Persons and address tables :These are employee personal and employment information
    • You can map with these payroll table
      • per_people_f
      • pay_people_groups
      • per_assignments_f
      • per_addresses
  • Deductions and levies
    • You can map with these payroll table
      • pay_element_entries_f
      • pay_element_entry_values_f
  • Direct Deposits
    • You can map with these payroll table
      • pay_external_accounts
      • pay_org_payment_methods_f
      • pay_personal_payment_methods_f

3. Payroll Tables : What you really required

  • Payroll summary :Contains all the payroll details such as earnings, deductions, bonuses, and hours in a few tables
    • You can map with these payroll table
      • pay_payroll_actions
      • pay_assignment_actions
      • pay_run_results
      • pay_run_result_values
      • pay_element_classifications
      • pay_element_types_f
      • pay_input_values_f
  • Payment summary :Contains check and direct deposit details such as amount and account numbers
    • You can map with these payroll table
      • pay_payroll_actions
      • pay_assignment_actions
      • pay_pre_payments
      • pay_org_payment_methods_f
      • pay_personal_payment_methods_f

API Avability of HRMS

So here are few Business events required for your bespoken application and there underline API's details

Add a New Employee

  1. Insert person :hr_entry_api.insert_element_entry
  2. Update people group :hr_assignment_api.update_emp_asg_criteria
  3. Update assignment :hr_assignment_api.update_us_emp_asg
  4. Insert base salary :insert into per_pay_proposals
  5. Insert check as the payment method :hr_entry_api. insert_element_entry

Update Employee Information

  1. Update personal information if it changed: per_per_upd.upd
  2. Update assignment and people group information if it exists, set mode to correct or update :hr_assignment_api.update_emp_asg_criteria

Add terminating employees

In Oracle On Terminate screen, select Actual Process Date and/or Final Process Date and click on Terminate button. Optionally, enter 'Leaving Reason,' 'Notified,' 'Projected,' 'Last Standard Proces' values.

These are API's are available for termination

  2. Hr_ex_employee_api (File name: peexeapi.pkb)
  3. This file contains three (3) procedures.....
  4. hr_ex_employee_api.actual_termination_emp
  5. hr_ex_employee_api.update_term_details_emp
  6. hr_ex_employee_api.final_process_emp
  7. Note: The API's have to be called in that order.


  1. Insert or Update Addresses :hr_person_address_api.create_person_address
  2. Update existing address records :hr_person_address_api.update_person_address


  1. Insert or create a phone :hr_phone_api.create_phone
  2. update existing phone records :hr_person_address_api.update_person_address

Schools and Colleges

  1. Insert or create a schol and college information :per_esa_ins.ins
  2. update existing records :per_esa_upd.upd

Insert or Update Deductions

  1. If deduction is ended, use the delete API to set end date :hr_entry_api.delete_element_entry
  2. Update deduction if exists, else insert it :hr_entry_api.insert_element_entry


  1. Insert or create a qualifications information :per_qualifications_api.create_qualification
  2. update qualifications records :per_qualifications_api.update_qualification

End Date /delete Element Enties

  1. End Date /delete Element Enties :py_element_entry_api.delete_element_entry

There are 4 date track delete modes available (variable p_datetrack_delete_mode)

  • DELETE end date element entry
  • DELETE_NEXT_CHANGE delete next changes
  • FUTURE_CHANGE delete future changes
  • ZAP completely remove element entry from the database

Insert or Update Direct Deposits

  1. If direct deposit is ended, use the delete API to set end date :hr_personal_pay_method_api.delete_personal_pay_method
  2. Insert bank account if it does not exist :insert into pay_external_accounts
  3. Update direct deposit if exists, else insert it :hr_personal_pay_method_api.create_personal_pay_method

Inform Manager of Inactive Employee Credit card process

  1. Use this if required inactivate employee credit card process : hr_ex_employee_api.actual_termination_emp


  1. Create new Job :hr_job_api.create_job
  2. Updating the job details :hr_job_api.update_job


  1. Create new position :hr_position_api.create_position
  2. Updating the position details :hr_position_api.update_position

Where I can get the details for these API's

For Oracle release 11 ,11i release 12, the APIs are located in the operating system directories:

$PER_TOP/patch/xxx/sql and $PAY_TOP/patch/xxx/sql,

Refer to filenames like pe****api.pkh, py****api.pkh, and hr****api.pkh, where **** represents wildcard characters.

You can identify the package name and version of the API.

Run this query

  3. SELECT [2] text
  4. FROM [3] all_source
  5. WHERE [4] name LIKE [5] '%your_api_name%'
  6. AND [6] text LIKE [5] '%Header%';

This query will return the header line of the pac kage and package body showing the package name and current version number.

  3. SELECT [2] object_type,object_name
  4. FROM [3] user_objects
  5. WHERE [4] object_name
  6. LIKE [5] 'HR%API';

Publicly Callable Business Process APIs in Oracle HRMS

Oracle APIs are a logical grouping of all external process routines. The Oracle HRMS API sets delivers a set of PL/SQL packages procedures and functions that provide an open interface to the database.

There are three options for you to find the details:

  1. Oracle Documents
    To find a current list of publicly callable business process APIs in Oracle HRMS; you need to go into your On Line Help from within applications and use the search field to find 'Publicly Callable Business'. In the Search Results frame you will see a link to 'Publicly Callable Business Process APIs in Oracle HRMS (Oracle HRMS)' listed.
  2. Irep
    To find correct information on APIs you check Oracle Integration Repository (irep) which can accessed via MetaLink by selecting the Knowledge tab - Knowledge Browser sub-tab, then in the Online Documentation box select - Oracle Integration Repository.
  3. Search the database
    You may find official documentation on Publicaly Callable APIs as in options 1.
    You can search in the HR Schema for the stored procedures with name 'HR%API'. Open the Package spec and then read the inline comment about the parameters. This is very useful to understand what is meant by each paramater and what the API does.
  4. Etrm via metalink
    TRM helpful in selecting the appropriate API call.

Considertaion for using API

Before using any API's of payroll and HRMS you must have clear understanding of

  1. Data Tracking
  2. Validation paramater
  3. Correction, insert mode of API's

Additional Note for API's documents

You can download "Configuring, Reporting and System Administration in Oracle HRMS" document from the Oracle documentation library. It has a list of api's.

Suggested Reading

2 Comments (Open | Close)

2 Comments To "Bespoke Application & Utilizing HR/Payroll APIs"

#1 Comment By Donald Ferreira On December 20, 2008 @ 5:44 am

Man this guy(Sanjit) really rocks.

Wonder the amount of knowledge you have about EBS and your sharing to the community, is just amazinggggg.

Thanks for the good work. All the best and success

How can I becoming a memeber of you blog?

Kind regards

#2 Comment By Alok Sinha On September 3, 2009 @ 5:49 pm

I new nothing of HRMS looks like I will be the Shark soon.

There was only one guy whom I think of as GURU Ajay Bluemars Kuma.

I have been requesting him to have a site or blog like you have. He is also amazing zing zing zing. Trust me Sanjit you & Kuma Sir both have my very sincere admiration.

I sincerely appreciate that guys like him and you exists.

Great again
Alok Sinha