Introduction

The title of this article might surprise a lot of experienced consultants but it does continues to puzzle new starters to understand how to identify whether an employee is a new hire or is some one who has undergone assignment changes.

We would try to explain this with a very simple example covering both the technical and functional sides and try to explain the same both from an database as well as application perspective.

To start with we would begin with the Technical Aspect ( generally people tend to start with this aspect as its considered that most people would have little idea about SQL queries, Relational Database etc and it becomes easier to understand).

In context of Oracle HCM Cloud the Assignment Related details are captured in a Base Table named PER_ALL_ASSIGNMENTS_M. Also important detail like Person Number is stored in the PERSON_NUMBER database field of PER_ALL_PEOPLE_F table.

We would use the below query to get the assignment details from the database:

When we execute this query creating a Data Model ( Read thisarticle to learn how to create a sql query based BI Data Model in Oracle Cloud Application) we would be able to view the following results:

From the above diagram we can find the below things

Couple of points to note from above table are:

1. There is just one Assignment Record for Person# 2

2. There are multiple assignment records for Person#3

3. Each New Assignment Record (For Person#3) starts one day after the previous assignment record ends

4. The most recent record has an Assignment End Date of 31-Dec-4712

So from these details we may conclude that Person# 2 has not undergone any assignment changes and there should be only one record (one created during new hire action) in case we verify from UI.

For Person# 3 on the other hand there are 3 assignment records out of which the first one would be new hire record and the other two would be assignment changes.

Verification From Application UI

In order to perform a verification from application we would need to follow the below navigation:

Navigator->Person Management-> Search for Person Number and then go Quick Actions -> Personal and Employment -> Manage Employment

We would see the following details on the Manage Employment we could see the Action is Hire, the effective start date and hire date are both same (12/29/04 which corresponds to 29-Dec-2004) and also the assignment history shows all the actions were performed on the hire date.

Similarly, if we check for the Person# 3 we could infer that the Hire Date and the Start Date of First Assignment (2/14/03 corresponds to 14-Feb-2003) , the most current Assignment Record starts on 1/24/15 for Promotion Action. Also in the View History we could see that there are three assignment records with assignment actions Hire, Manager Change and Promotion.

Inference / Summary

From the above example we could clearly see that for a New Hire there would be just one assignment record with Assignment Action as Hire and the Hire Date and Assignment Start Date would be same. In case of an employee record where multiple assignment changes have been made there would be multiple assignment records each starting on a new date such that the New Assignment Start Date begins one day after the previous assignment record ends.

While in this example, I have picked up already existing person records for this demonstration but one can create two new worker records one which will only have Hire Action performed and the other would have multiple assignment changes.

Ashish Harbhajanka

Add comment