Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Bespoke Application & Utilizing HR/Payroll APIs

Posted on December 16th, 2008 by Sanjit Anand ||Email This Post Email This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today

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” 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”.

dgreybarrowThe 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.

dgreybarrowIts all about Table and API

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

  • Reference tables : The details for driving basic Information.
  • Employee tables : The details for driving employee vital details
  • Payroll tables : Payroll details for employee or any other elements.

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

dgreybarrowAPI Avability of HRMS

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

dgreybarrowAdd 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

dgreybarrowUpdate 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

dgreybarrowAdd 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

Hr_ex_employee_api (File name: peexeapi.pkb)
This file contains three (3) procedures.....    
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

dgreybarrowSchools and Colleges

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

dgreybarrowInsert 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

dgreybarrowEnd 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

dgreybarrowInsert 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

SELECT text 
FROM all_source
WHERE name like '%your_api_name%' 
AND text like '%Header%';

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

select object_type,object_name 
from user_objects 
where object_name 
like '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.

dgreybarrowConsidertaion 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

dgreybarrowAdditional 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.

dgreybarrowSuggested Reading

Related Posts

Posted in HRMS | 2 Comments »Email This Post Email This Post |

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today
2 Responses
  1. Donald Ferreira Says:

    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. Alok Sinha Says:

    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

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.