Oracle HRMS – E-Mail Address or any particular field needs to be Changed For All Employees


You can use our public API to update Email address (hr_person_api ) use procedure (update_person)
Note: take care when you pass Object_version_number parameter
As per these people are existing employees so you have to get the latest Object_version_number by
running this Select statement.

Here’s the piece of code which demonstrates how to call the API:

l_employee_number per_people_f.employee_number%TYPE;
l_person_id per_people_f.person_id%TYPE := 373;
l_per_object_version_number per_people_f.object_version_number%TYPE;
v_effective_start_date date;
v_effective_end_date date;
v_full_name varchar2(100);
v_comment_id number;
v_name_combination_warning boolean;
v_assign_payroll_warning boolean;
v_orig_hire_warning boolean;
Select object_version_number ,employee_number
into l_per_object_version_number ,l_employee_number
from per_people_f
where person_id = l_person_id ;
p_validate => FALSE
,p_effective_date => to_date(’29-JAN-2006′,’DD-MON-YYYY’)
,p_datetrack_update_mode => ‘CORRECTION’
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_employee_number
,p_email_address => ‘’
,p_effective_start_date => v_effective_start_date
,p_effective_end_date => v_effective_end_date
,p_full_name => v_full_name
,p_comment_id => v_comment_id
,p_name_combination_warning => v_name_combination_warning
,p_assign_payroll_warning => v_assign_payroll_warning
,p_orig_hire_warning => v_orig_hire_warning

Alternatively, you can Use ADI by creating an integrator that use this API, that i will give more explanation in article for “Building ADI for HRMS”

Salary form in Oracle HR

I have been asked this question by many clients and consultants recently, and the solution is pretty straight forward. So, i thought i’ll share it here with y’all.

Oracle released a Family pack RUP 2 for HRMS last year, and it brought in a new feature – The web based Salary Administration form.

For many users, it was a thrill. But for a lot others, it was a challenge to get it to work. They are used to the old form based salary administration interface. Some of them even have custom hooks on the form that can not be implemented on the webpage so easily.

So, the Question is, how to revert to the salary form after applying this RUP, so you can still continue using the form based salary administration.

Here’s the answer. And as i said, its pretty straightforward.

Taskflows come to rescue.

Here are the steps to replace “New Salary Framework Page” with “Old Salary Form”:

  • Select any HRMS or Super HRMS Manager Responsibility (Example: US HRMS Manager)
  • Open the “Define Taskflow” form. (Security->Task Flow Definitions)
  • Query the Taskflow that is used by the Person form function (For Example: US HRMS PERSON TASKFLOW). Normally, this is the custom Taskflow.
  • Go to “Node section – Name field” and add “SALFORMNODE” by inserting a new row and selecting “SALFORMNODE” from Lov in the Name field.
  • Then Query “NASSIGN” node in “Node Section”.
  • In “Navigation Options Section”, delete record that has “SALREVIEW” for Node. Select “Yes” in the delete warning window.
  • In Navigation Options, insert new record with the following details:
    Seq: 1
    Button: Checked
    Label: &Salary (leave the default &Salary as is)
  • Save and Exit the “Define Taskflow” form.
  • Logout the current applications and re-login to check that the “Old Salary form” is launched instead of “New Salary Page” when “Salary” button is selected in Assignment Window.

Try this, and let us know if you still have any issues. Most probably, it’s because you are using Oracle 😉


Using Images in Oracle HRMS

Hello Friends, Lets learn something in Oracle HRMS. Oracle Human resources has a table called PER_IMAGES. This table stores employee pictures in RAW format. If you want to mass upload the pictures of all employees in this table, you can have a hard time. we were facing a similar issue, and here is the approach i used to bulk upload (or mass upload) employee pictures to PER_IMAGES table in Oracle Human resources system using SQL Loader.

First, let’s see how the table looks:

SQL> desc per_images

Name                            Null?    Type
——————————- ——– —-
IMAGE_ID                        NOT NULL NUMBER(15)
IMAGE                           NOT NULL LONG RAW
PARENT_ID                       NOT NULL NUMBER(15)
TABLE_NAME                      NOT NULL VARCHAR2(30)
IMAGE_ID   – Next value in sequence
IMAGE      – Binary Image

To load a binary image in this table, we have to first create a control file, which will look something like this:

options (bindsize 400000)
load data
infile photo.bmp “fix 60999”
concatenate 3
into table PER_IMAGES
(image_id constant 1, image raw(33532), parent_id constant 1253,
table_name constant “PER_PEOPLE_F”)
Some important facts now: Size of picture in this example is 33532 bytes. Bindsize must always be larger than the filesize. And, most importantly, only 64k can be loaded at one time, so we have divided the file size into equal portions < 64k. Here are the shell scripts, and ctl files i am using:

   echo enter file name
   read fname
 while read line
  value=`head -n $x $fname | tail -n 1`
           echo $x $value;
                sh $x $value
                x=`expr $x + 1`
   echo “****$value”;
What the above script called mainscript does is that it read the file that contain employee id and picture file name.For each employee, it than calls another shell script called, and increments the id. This id we will be using in the ctl file.

filesize=`ls -l $3 | awk ‘{print $5}’`
fixsize=`expr $filesize`

if [ $filesize -gt 65536 ]; then
   echo “Filesize more than 6 k. breaking up…”
    if [ $filesize -gt 131072 ]; then
    if [ $filesize -gt 196608 ]; then
    if [ $filesize -gt 262144 ]; then
   echo “Breaking into parts :”$concno
   fixsize=`expr $filesize / $concno`  

echo $1 $2 $3 $4 “fixsize” $fixsize “filesize” $filesize “conc” $concno >> fileload.log

echo “Creating ctl file for dataload now”
echo “options (bindsize=400000)” >> loadpics.ctl
echo “load data” >> loadpics.ctl
echo “infile $3″ \”fix $fixsize\”>> loadpics.ctl
echo “append” >> loadpics.ctl
echo “concatenate “$concno >> loadpics.ctl
echo “into table PER_IMAGES” >> loadpics.ctl
echo “(image_id constant $1,” >> loadpics.ctl
echo “image raw($filesize),” >> loadpics.ctl
echo “parent_id constant $2,” >> loadpics.ctl
echo “table_name constant \”PER_PEOPLE_F\”)” >> loadpics.ctl
echo “” >> loadpics.ctl

sqlldr apps/<appspwd> control=loadpics.ctl log=picsload.log

mv loadpics.ctl loadpicslast.ctl


Above script is pretty simple, isn’t it? Well what we are doing is reading the file size, and creating
a ctl file with the information gathered. Then we are calling SQLLDR to upload the picture using this CTL file.

Hope this helps you! Thanks you …. Shivmohan Purohit

Article Courtesy: