Date Tracking

Date Tracking

Date tracking is a design / concept, which is used by Oracle E-Biz, in order to support the storage of historical data, along with the current ones. It is a mechanism to store data based on dates. Let’s try this with an example. There was Mr. Joe, who used to work as a Manager. He had been with the company since last 8 years. In this period of 8 years, he had been working in a set of different positions. Initially he joined as an Analyst, then he got promoted to senior analyst, then he became, the manager of a department.

If we were to know the position he was in, as of a date in 2008; how do we do that? Imagine, we are making a database table to store the employee related data, or rather let’s take the well known Employee table (that we all played with, while learning SQL), all it stores is the current position. Do we have a way to know the employee’s previous position? The answer is No.

So here is an innovative way, if we introduce, two more columns to the employee table, with names like “START_DATE” and “END_DATE”, and store the dates in there, it might solve my problem.

The table will look like this:

Now, if we ask the same question again, it tells me, oh yes, he was a Sr. Analyst in 2008. This is a nice table, which is capable of storing the historical data as well, however our data is repetitive. It’s not greatly normalized. But well, that’s the price we will have to pay, in order to get the advantage of storing historical data.

Hick ups:

  • Yes our data is not normalized.
  • We will have to use a Composite Primary key, so that means, anytime we are querying the table for current data, we will need a self join to say, "SYSDATE between START_DATE and END_DATE"

There are a lot of tables in Oracle E-biz that need to store Historical data. All those tables are date tracked. They hold two extra columns to store the start and end date of the record. And the columns are named EFFECTIVE_START_DATE and EFFECTIVE_END_DATE respectively. These columns do not accept null value. All Date Tracked table names end with “_F”.

Concept of EOT

But now, how do we manage the Till Date thing? We need to store a date there, it does not accept null. For that Oracle added another model, concept of EOT (End of Time). As per this concept, 31st December 4712 is the end of time. So at any place, if we were to show the record is the latest one, we would use, the “31-DEC-4712” in the EFFECTIVE_END_DATE column.

The date track also makes us capable of storing Future data. Let's say, we will promote Mr. Joe to Director as of 01-JAN-2014. So we will add another record in the table with Start Date as 1-JAN-2014 and end date as 31-DEC-4712. And will update the manager record's END_DATE column with 31-DEC-2013, right?

So having the EOT in the EFFECTIVE_END_DATE column does not always fetch us the currently active record. We should always use the condition (SYSDATE between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE).

Date Track Modes

Let’s talk about the application of date track concept? We will start with the modes. The modes represent the different ways a particular record can be updated in a date tracked table. For an example, we want to remove a record on a Date tracked table. We have two options:

  • Purge : This removes the entire record from the database
  • End Date: This updates the EFFECTIVE_END_DATE on the currently active row to today's date.

While inserting a new record, we will not be prompted for any modes. The EFFECTIVE_START_DATE is the today's date and EFFECTIVE_END_DATE is the EOT.

While updating a record; for an example, we want to make Mr. Joe a Sr. Manager. It prompts for these options:

  • Update: This will add another row to the table, with an EFFECTIVE_START_DATE of today, and EFFECTIVE_END_DATE as EOT; and it will update the currently active record's EFFECTIVE_END_DATE to yesterday's date. So Mr. Joe's manager row will get updated with the new EFFECTIVE_END_DATE as Yesterday's date; and a new record will get created with the EFFECTIVE_START_DATE as Today, and EFFECTIVE_END_DATE as EOT. Clear? Alright.
  • Correction: This is simple. It will simply go and update the column. It will not create a record. Our previous column value will be lost. So in this case, Mr. Joe's record of manager will be updated. The position field will get updated to Sr. Manager, and no one will ever know, that Mr. Joe was a manager at one point of time.

If UPDATE was selected, the system checks, whether the record being updated has already had future updates entered or not. If it has been updated in the future, we will further be prompted for the type of update. Those options are

  • UPDATE_CHANGE_INSERT (Insert) - The changes that the user makes remain in effect until the effective end date of the current record. At that point the future scheduled changes take effect.
  • UPDATE_OVERRIDE (Replace) - The user's changes take effect from now until the end date of the last record in the future. All future dated changes are deleted.

So for an example, we promoted Mr. Joe to Director as of 01-JAN-2014. Now, the currently active row has an EFFECTIVE_END_DATE of 31-DEC-2013. We get a request from my manager that Mr. Joe should get promoted to Asst Director First and then should get promoted to the director.

Here is a diagrammatic representation that will explain it better. See Figure 2.1 – Date Track Modes.

Figure 1 Date Track Modes

C:\Data\Biswajit\Imps\Book\Chapters - Updated\NEW\detailed\Chapter4_Oracle_Core_HR_files

(Figure 2.1 – Date Track Modes)

As we are updating a record, that has changes in future, It will ask if we want to do an Insert / Replace.

If we choose Insert, it will go ahead and insert the record from today to 31-DEC-2013. So a new record gets created with EFFECTIVE_START_DATE of today and EFFECTIVE_END_DATE of 31-DEC-2013, and the currently active record gets updated with an EFFECTIVE_END_DATE of yesterday.

If we choose Replace, it will discard the future change. So a new record gets created with EFFECTIVE_START_DATE of today and EFFECTIVE_END_DATE of 31-DEC-4712, and the currently active record gets updated with an EFFECTIVE_END_DATE of yesterday. The Record with Director as the position gets purged.

End Dating

Usually, we do not delete any data from system in HRMS. Although we should purge the data that was never relevant to the enterprise or any given employee or assignment, however we should just populate the end date in case of data, which was used earlier and not being used anymore.

For an example, there is a date tracked table that stores the car hire details. In that table, we are storing the data related to the options available to choose a car for hire. We are giving 4 options to the employees to hire a car; for say, a Chevy, a Dodge, a Hyundai and a Lamborghini. However from year 2010, due to low budget, we are not going to be giving Lamborghini as an option anymore. In this case, we are going to populate an end date (EFFECTIVE_END_DATE) on the Lamborghini record with a date of 31-DEC-2009. So that it will tell me, the car was available in past, but is not available now (01-JAN-2010). This feature is known as End Dating.


Usually in a date track table, if we opt for a delete in forms; it will prompt us to enter, whether it’s an End Date or a purge.

DATED Tables

Now, we know what a date tracked table is. Let’s talk about DATED Tables. These are more or less similar to the Date Tracked enabled tables; however these tables do not use the composite primary key like the former. These tables use only one Primary key, but with two date fields - DATE_FROM and DATE_TO.

So what’s the use of these tables? Although they serve the same purpose of storing historical and future records, unlike the Date Tracked tables, the consistency of data is not maintained. So we can consider these to be partially date tracked. To make it simpler, let’s try Mr. Joe's example again. As we would need the position column to be maintained without any hassle of dates, we created two new fields and then tried identifying individual rows with the combination of EMP_ID and the date columns. So that enabled me with features like, Update, Correction, Insert and Replace.

However imagine a case where, we do not need that much data consistency, so that whenever we do some updates to a column, it adds a new row to the table. Like address. So if we were to store Mr. Joe's address, we will keep it in a table, that can just tell me, since when, till when did he live in a given address, we do not want any complexity of Insert and replace. All we want to do is to be capable of updating the address (that’s a new record), and correct the address (Updating the same record). So in this case every time we update an address, it creates a new ADDRESS_ID.

These are like a level lower than Date Track enabled tables. These tables do not have any indicator in their names, unlike the date track enabled tables.

We will discuss more about these tables later, when we discuss about the technical aspect of Core-HR.

Keeping Person Records

Talking about the person records, the indicative details of all persons are stored in a table named “PER_ALL_PEOPLE_F”. This is considered the base table to store the basic information of any given person, associated with the enterprise, be it an employee or spouse / child of an employee. However there are a lot of other tables that store additional information related to the persons.

You must have guessed that, this is a date track enabled table, as it ends with _F. Hence the table has a composite primary key, PERSON_ID along with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. The table also contains foreign keys to a lot of related tables. Along with that, fields like name, gender, date of birth, and all basic details are present in this table. As per E-Biz design, this table is considered to be the pivot for all employee and employee's contact records.

Question: What is a contact?

Anybody with any specific relationship with a person is its contact. If Jill is married to Joe, Jill is Joe’s contact. The relationship can be of any type, spouse, children, domestic partner, grand children, ex-spouse etc.

The other related tables to store person related details are:

  • PER_ADDRESSES: stores the address of a person. It’s a DATED table.
  • PER_PHONES: stores the Phone numbers of a person.
  • PER_CONTACT_RELATIONSHIPS: stores the contacts of a person.
  • PER_DISABILITIES_F: stores the disability information of a person.
  • PER_PERSON_TYPE_USAGES_F: stores the person type of a person (example: Employee, Ex-employee, Beneficiary etc.)
  • PER_QUALIFICATIONS: stores the qualification of a person.

These are some of the basic and frequently used tables, to store the Person level records, however there are a lot of tables, and views that can be used to store any specific information about a person. We will learn about those, as and when we come across them.

Again, there are a set of related tables/ views, that store similar information, but in a different fashion. Let’s jump on to examples.

  • PER_ALL_PEOPLE_F: Stores the Person data with Date Track
  • PER_PEOPLE_F: a view over PER_ALL_PEOPLE_F with additional security on records. Like, which user can see what all records?
  • PER_PEOPLE_X: shows up only the currently active record as of SYSDATE.
  • PER_PEOPLE_V: a view used by E-Biz forms to show the data with additional security using security profiles.
  • PER_ALL_PEOPLE_D: a view that shows the date track history.

Now we know, even though the data stored is same, various tables/ views are designed to store the data in different fashions. The reason may be data abstraction or security or in few cases just history.

Keeping Employment Records

The Employment records are very important to the enterprise, as these are going to be the details about our employees and ex-employees. The way the data is stored in the application is much normalized. When we talk about the employment, what are the details that we need to take care of?

  • His Assignment
  • His Service with the Firm
  • His Salary

Let’s discus these details one by one.

Assignment: This is the unit of an employment period. It starts with a Hire, and ends with a termination / New Assignment. For an example, Mr. Joe works for three years in the firm, and then gets terminated and then gets rehired in to the firm after 1 year, and continues for another 5 years. In this case, Mr. Joe had two assignments with the firm. So every time Mr. Joe got hired, he had a new assignment. These assignments related details are stored in PER_ALL_ASSIGNMENTS_F. This table stores all the data related to the employment, like, the Job, his Location, the Organization he is working for, his supervisor etc. It’s a date track enabled table and ASSIGNMENT_ID is the primary key.

Oracle E-Biz also creates assignments for the ones who are retired, sometimes for the contacts as well. Those are called Benefit assignments; we will learn more about them later. E-Biz also has something called Applicant assignment. It’s the assignment details of an applicant, who might become an employee in future. We can even have more than one assignment for an employee in a given period. It’s like; the employee is working for two different roles / Jobs. An employee must have at least one and only one primary assignment. All others are considered Secondary.

Talking about secondary assignments; these get created when an employee is assigned more than one roles in an enterprise, provided the roles are governed by two different Organizations / GRE or they use different Jobs and positions. The secondary assignment helps the system to track time entered / salary / payroll etc.

Service: Every Hire created in the firm, will result is a period of Service record. The table that is used for that is PER_PERIODS_OF_SERVICE. Its primary key is PERIOD_OF_SERVICE_ID. This table stores the Hire date, term date and the Term reasons, along with other details related to service. If a Person has multiple assignments but within a single service (without being rehired), he will have multiple ASSIGNMENT_ID, however just one PERIOD_OF_SERVICE_ID. A hire drives the period of service, but a new employment instance / a change in role drives the assignment, along with the termination.

Salary: Now let's talk about the salary. This is the amount that a Person gets paid. Although Oracle E-biz considers Annual Salary as the calculation standard; the defined salary gets calculated based on the frequency of pay and the amount per pay period. The pay frequencies are specific to pay basis and in turn depends on payrolls. These are some very popular pay frequencies:

  • Monthly: Once a Month
  • Semi Monthly: Twice a Month
  • Bi-Weekly: Once in Two weeks
  • Weekly: Once in a week

To determine the Annual salary of any employee, Oracle uses something called as Annualization Factor. It’s a number, which is multiplied to the salary to get the Annual Salary; so for Monthly, the Annualization factor will be 12 and for Biweekly, it will be 26.

How does the Salary get calculated?

  • It takes the PROPOSED_SALARY_N column from PER_PAY_PROPOSALS where APPROVED_FLAG = Y with Employee's ASSIGNMENT_ID.
  • It gets the PAY_BASIS_ID from the PER_ALL_ASSIGNMENTS_F for the Employee using its ASSIGNMENT_ID.
  • It then multiplies the amount with the Annualization factor stored in PER_PAY_BASES.PAY_ANNUALIZATION_FACTOR based on the Employee's PAY_BASIS_ID.
    • Then the Multiplication resultant is the Annual Salary.

Person Types

Person Type is a very powerful functionality through which we can identify and group the persons we have in our system. First of all, what are the different types of persons we store in our system? Many actually; we store the Employees, applicants, contingent workers, Ex-Employees, Contacts and beneficiaries of the Employees etc. Now, we should have some way to identify these different groups. Although we can identify an Ex-employee as someone who used to work with the firm, and does not work anymore, it becomes a tedious task to do the same number of checks every time, isn’t it? So what’s better? A Single attribute that can tell us, on this person is an Ex-Employee. How nice would that be, that when a person is currently working the attribute should say “Employee”, and soon after the termination happens, the attribute should automatically change to “Ex-Employee”. Wouldn’t that be awesome? This functionality is there. The attributes are nothing but “Person Types”. Let’s see how to use it.

Oracle application comes with a seeded set of Person types that can be used to identify the population. However we can further add new person types as and when we require them. Like we can have Fixed-Term employee as a person type, which is different than Employee. We can have Retirees different than Ex-Employees etc. the one that are seeded are called the system person types; and the one that the user creates is called the user person type. There are eight system person types in R12. And we can create as many user person types as we want based on the requirement. Let’s see how to.

Responsibility: Super HRMS Manager

Navigation: Other Definition -> Person Types

Did the Default flag make confusion? Ok let’s try this. We have three types of Employees in our system, and we want to make different person types for each of them.

So what we should do is, go to the Person Types Screen and add three records with the System name as “Employee”. One for each type of user name “Night Shift Staff”, “Mid-Shift Staff” and “General Shift Staff”. Now, we can make any one of these three as Default; for example let’s set “General Shift Staff” as default. Now whenever there is a hire, the system will identify, oh, it’s an Employee, then what is the Default Person Type? Oh, it’s “General Shift Staff”. So it will make the person type of new hire as “General Shift Staff”. But if later he changes his shifts, we can just go and add a new person type usage in his record and make him a “Night Shift Staff” from “General Shift Staff” manually. Simple, isn’t it?

But how do we change it manually? Let’s see.

Responsibility: Super HRMS Manager

Navigation: Fast Path -> Person Type Usages


  • · Query for the employee
  • · Add a new Person Type usage / End date the old one.