Oracle Applications Architecture

Introduction

Ever since the inception of mankind, continuous improvement has been a key factor. Innovative thinking and the attitude of experiment has guided us to what we are today. It’s the willingness to find something new, and doing things in a better way that has led Homo sapiens thus far. The path of technical advancement had been similar.

Information Technology had been the answer to a lot of complicated and repetitive issues lately. With the advent of software engineering, we have been trying to eliminate the human intervention from the complex processes and automate them, and so far we have been very successful. If we reopen the history of Information technology, we will find a lot of pioneering products that has changed the way things worked before its introduction. ERP is one such product.

ERP stands for Enterprise Resource Planning. This is a software application that enables the flow if information between different streams of business, like Finance, Manufacturing, Sales, Human resources etc. An enterprise uses ERP systems to support its daily business transactional needs and reporting capabilities based on the transactions made.

Architecture of E-Biz

This section describes the basics of E-Biz Architecture. Although there are a lot attached to the Oracle apps architecture, we will discuss the concepts that will help us in understanding the functionality better. Oracle E-Biz runs with three tier architecture.

Desktop Tier

This is the client facing or user facing interface. It runs HTML based Java applets to pop up forms and web based applications, for the user access. This tier accepts our log in authentication credentials and keeps them for further usage. So once logged in, we can use oracle applications as well as other tools embedded within. The Oracle forms are brought in with a Forms client applet which in turn is a collection of Java Archive files (JAR files). When we log in for the first time, the Forms client applet and frequently used JAR files are downloaded to our machine and cached. Later less frequently used JARs get downloaded based on necessity.

Application Tier

This tier beholds all the servers that are responsible for the services we avail on the desktop tier. For an example, if something is queried from the database, then apart from the data, the appearance, the business logic etc are needed; these extra things other than the data are called Services. This is the tier where the servers will reside, and provide us the services. A basic application tier will comprise of:

Web Server: Manages the web services, like HTTP requests, Java Controller, Servlet engines etc. In short it provides end to end handle on application appearance (except form based applications) and request-response management.

Form Server: Manages the form based application requests and the Form listener Servlets.

Concurrent Server: This is an innovative piece of architecture, where the application allows us to run programs that can process in the background without putting any pressure on the other transactional processes we are working on. For an example, we can just put in a request for a report and keep working on the application, without any interventions; as the report is being run by the concurrent manager, which is independent of the application server.

Admin Server: This one manages the applications data and patches. It records the patches installed in the system, along with the administration of application data.

Database Tier

The database tier contains the Oracle database server, which stores all the data maintained by Oracle Applications. In short, it is the database of the applications.

Technology Layer

There are a set of technology that are used across modules. These provide common features to all Oracle application products. These technologies are:

Oracle Applications DBA

Oracle Applications Object Library

Oracle Applications Utilities

Oracle Common Modules

Oracle Workflow

Oracle Alert

Oracle Applications Framework

Oracle XML Publisher

These are all standalone modules. We will learn about these in details, as and when we emanate through the chapters.

File Systems

There is a given pattern in which the files are stored in the servers. The pattern is nothing but a defined folder/directory structure, which is commonly known as the File System. The file systems have evolved along with the Oracle E-Biz. There had been significant changes to the File systems with the advent of R12 and we will discuss the file system based on R12.

Database server

Talking about the Database server first, it contains stacks like Application stack and Technical stack. See Figure 1.2 – Database Server. And the stacks further contain different directories.

DATA_TOP: This directory resides in ./apps_st/data. Here apps_st represents the application stack directory in the database server. It contains the database level data like, the system table space, data table space, Index Table space, database files, redo-log files etc.

ORACLE_HOME: This directory resides in ./apps_st/10.2.0. It is the Oracle Home for the 10g database being used in the application backend.

Application server

The application server holds the application elated data, and in the vein of database Server, it holds the application stack and the Technical stack. See Figure 1.3 – Application Server. The stacks further hold the sub directories.

APPL_TOP: This is considered the Mother directory, as it stores the product directories within. Each product holds its own directory and this one lists them all.

If we go further down to the granular level in to APPL_TOP, we will find the directory containing the following subdirectories:

The product files and directories

The core technology files and directories

application environment files <*.env / *.cmd>

consolidated environment files <*.env / *.cmd>

The Product directories store information related to the Product. And are named based on the Product short name. For an example, the Prod directory for General Ledger will be called GL_TOP, as GL is the short name for General Ledger. Each product has a two-three lettered short code. The Product Top directory stores directories like admin, bin, forms, html, Java etc.

Under each product directory, there will be a directory for the version, like 12.0.0, 12.0.1, etc. For each version directory, there will be directories for each and every language the application is installed in. Like US, FR, GB etc. And under each language directory there will the following folders:

· Bin: Stores the Control files and data Load files.

· Data: Stores the data files used in the product.

· Forms: Stores the fmb files.

· Java: stores the Java class files and XML files.

· Patch: Holds the different downloaded patches.

· Publisher: Stores the XML Publisher template files

· Reports: Stores the Oracle Report Files.

· SQL: Stores all the database objects related to the Product.

COMN_TOP: This directory holds the technology layer products. The ones that are used across products (e.g. FND, WF)

ORACLE_HOME: Holds application technology stack tools components in application server.

JAVA_HOME: Holds application technology stacks Java components in application server.

Using Oracle E-Biz

Before getting into Oracle applications, we must know how to use Oracle applications. So let’s discuss a little bit about that. We will start with the different GUIs (Graphical User Interfaces) available with the application.

GUI is commonly known as the Graphical User Interface. These are the screens that open up on the user’s window, so that a user can enter, query and update data with ease. There are two types of GUIs available in Oracle E-Biz R12.

Form Based

HTML Based

 

Form Based GUI

The Forms based applications are the most widely used GUI in E-Biz. These are developed by the Forms Builder in Developer 2000 (D2K) or Developer suite; and are launched to the client machine by Java using the JAR files.

Forms GUI, the forms based GUI looks similar to the image given here. This is the first screen that opens up, when we open any responsibility. This is called the navigator. There are two distinct sections.

• Menu Functions (On the left side of the figure 1.4)
• Top Ten List

Menu Functions:

The Menu function section opens up the different menus attached to the responsibility. When we double click on the menu, it either opens another sub menu or simply calls a function, which in turn opens up a form / a web based GUI. There are two ways to open a menu, either double click on it, or single click it and then click on the Open Button on the fourth co-ordinate. The ‘+’ sign tells us, that the menu has one or more menus / functions attached to it. If we click on the ‘+‘sign, it expands with the sub menus/ functions, and the sign is then changed to ‘-’; clicking on which causes the menu to collapse. The text on the top, tells the Responsibility name and the description of the highlighted menu.

Let’s have a look at the extreme left of the figure 1.4. We will find small icons with ‘+’ and ‘-’ Signs. They help us navigate the screen better.

• The ‘+’ sign expands the highlighted menu.
• The ‘-’ sign collapses the menu.
• The third sign ‘+ ->’ expands the entire menu, including all submenus in it
• The ‘++’ sign expands all the menus in the screen to the granular level, expanding all menus and submenus
• The ‘--’ sign collapses all the menus and submenus

Top Ten List:

The Top Ten lists are the most frequently used screens, put on the other side for ease of access, because we do not have to go on finding the function from the menus. However system does not determine what to put on the list. The list will be determined by the user, and system remembers the list and brings it up once same user logs in again. The two arrow buttons on the middle of the navigator are the ones that are used to move a function to the top ten lists and vice versa. As the name suggests, we can have ten functions listed out there.

Another amazing thing about top ten list is the shortcut, once the function is added to the list, it gets added with a number as a Prefix. Next time we wish to open the function, we will just key that number from the number pad of the keyboard, and that opens up the related form.

Let’s have a look at the tools available to the Form Based GUIs. See Figure 1.5 – The Toolbar.

           

A	New	Creates a New Record
B	Find	Opens the find record window
C	Navigator	Shows the navigator, putting all other active windows in the back
D	Save	Saves the record
E	Next Step	Takes us to the next step in the process
F	Switch Responsibility	Opens up a window listing all available responsibilities for the user that a user can switch to.
G	Print	Prints the current page
H	Close	Closes all windows in the navigator
I	Cut	Cuts the selection
J	Copy	Copies the selection
K	Paste	Pastes the cut or copied portion
L	Clear	Clears the record
M	Delete	Deletes the Record, with a prompt
N	Edit Field	Opens a Editor to edit the text in the selected field
O	Zoom	Zooms the view based on users preferences
P	Translate	Opens the translation window; translates the text,
Q	Attachments	Shows the added attachments
R	Folders	Opens up the Folder tools, it helps users to add / remove columns from some basic windows.
S	Help	Opens the window help button; however we should have the Profile options set for this.

The Menu in the application is almost the same as we have in Toolbar, and are pretty self explanatory. So we are not going to discuss more about that.

Querying in the Forms GUI is very simple. The function key ‘F11’ is used for the same. Pressing F11 turns the entire form grey. Then we can enter the string that we are looking for in the grey fields and press CTRL + F 11. The system then tries finding out a match for the query we have entered and puts it on to the form. For example, if the query returns 5 records, the form will initially show up the first record it had fetched, and then we can navigate through the other four by using the up and down arrow keys.

If we know just a portion of the string and not the whole String we want to query for, then we can do a pattern matching using a Wild Card Character. For an Example, we want to look for an employee name; and all we know that the last name starts with a ‘XXAA’ then we can query like this: ‘XXAA%’

With this query in place, the system will look for all records that start with ‘XXAA’ and will return all records that match the requirement. That is called Pattern Matching mechanism. The ‘%’ is called a wild card character; there is another one, the underscore ‘_’.

When we enter the ‘%’, it searches for the string that can have any number of characters in place of the ‘%’ sign. Similarly the ‘_’ replaces just one character. Let’s take an example. If there are five records in my database like:

1. XXAAAPPS

And we will enter a query for ‘XXAA%’; the system will return all the rows, except the last one; as the % sign will replace all other characters, and every row starts with a ‘XXAA’, except the last one.

If we enter a query for ‘XXAA_’ then the system will return me just one row, as it will look strings that has just one character after XXAA, and the result is ‘XXAAA’; all other strings will not be pulled because either the string does not match, or they have more than one characters after XXAA.

There is another way to query the database. We can just use the Find window. Almost all forms have a Find window associated with it. We can open the window just by clicking the Find button from the toolbar. The Find button opens up a window with the prompt of data fields that are unique to the records. We can key in the strings with the wild card characters if we wish to, and then press find to fetch the records.

Let’s discuss the keyboard shortcuts that are available in forms.                    

CTRL + K	Open shortcuts	Opens a window with all available shortcuts.
CTRL + L	List of Values	Opens a window with all available values. If pressed on navigator, opens up all possible Functions to choose from.
F7	Clear Block	Clears the Entire Block.
F5	Clear Field	Clears the field.
F8	Clear Form	Clears the entire form.
F11	Query mode	Enters in the query mode.
CTRL + F11	Execute Query	Queries based on the data.
F12	Count	Counts the number of records to be returned by the query.
CTRL + U	Update	Updates the Record.
CTRL + P	Print	Prints the page.
CTRL + H	Help	Opens help window.
Tab	Next Field	Takes us to the next field.
Down	Next row	Takes us to the next record.
Up	Previous Row	Takes us to the Previous row.
Shift + CTRL + E	Show Error	Shows the error occurred in the form.
Shift + F5	Duplicate Field	Copies the field with the value available from the previous record.
Shift + F6	Duplicate Record	Copies the entire record, as it is in the Previous record.
Shift + page down	Next Block	Moves the cursor to next Block

We have discussed the most widely used keyboard shortcuts here. However if we want to learn more shortcuts then we need to use one, (CTRL + K) on the navigator.

HTML Based GUI

HTML based GUIs, as the name suggests, are pages popped up on client window in HTML. See Figure 1.6 – HTML Based GUI. The pages are usually OAF pages / Java pages that are translated onto HTML at the desktop tier. The query standards and the basics remain the same; however there will be no application specific menu/ toolbar in it. The HTML pages are executed on a web browser, like the Forms GUI.

The HTML based pages in R12 are made mainly for the end user interactions, as the end user might not be able to browse through the Forms. Hence the web based GUI are very self explanatory. The only other difference that we might find from the Forms GUI is there will be buttons and small icons that represent different actions. The buttons, and the icons, as shown here, have text labels that help the users to understand the underlying actions. Self Service Human resource is a complete web based GUI module that helps the end users to enter and maintain, data for their day to day activities.

Using Self Service

Self service Human resource or SSHR is a set of web based GUI, specially created for the end users. Users like employees, applicants, ex-applicants need not have to log in to forms and browse for data, they can simply log in to the SSHR pages and query and update data from there. There are two major reasons to do so, firstly, the end user need not know the flow in which Oracle works or the basics of Forms GUI; secondly the user need not have to be exposed to the enormous data we store in the applications. All it needs is to see data related to it. With these things in mind, Oracle gives the users a wonderful Web Based GUI Interface called Self Service Human Resource.

Although we can do wonders using SSHR, and it is a complete module by itself, we are not going to discuss the techno-functional aspects of SSHR; because of the vastness of the subject matter. However what we certainly plan to do is to discuss, how to use SSHR with respect to the different modules and application utilities we have learnt so far.

As we already discussed, it is a module by itself and there is a different responsibility to be attached to the user to be able to use it. Actually two:

• Employee Self Service
• Manager Self Service

The first one is used for the Employees, where they can enter their personal details, manage their competencies, self appraisals etc. and the second one is for managers, where they can manage their subordinates, Approve applications, Conduct appraisals etc.

If we look at the key functionality, we can divide it in three distinct types:

• People Management
• Talent Management
• Compensation and Benefits Management

People Management

These are functionalities that enable the user to maintain their personal, professional and employment details.

Maintaining personal details will include functionality like:

• · Store the Personal details
• · Managing Addresses and phone numbers
• · Managing Contacts, dependents and beneficiaries
• · Managing Emergency Contacts
• · Managing Documents of Records to store the Electronic documents

For Professional details:

• · Store details on Educational Qualifications and Resume
• · Manage competencies
• · Manage work preferences

And lastly for employment details, the functionalities are:

• · Store data in EIT and SITs
• · Manage the assignments of subordinates.
• · Manage Locations and supervisors of directs.
• · Manage the pay rates for Contingent workers and other employees.
• · Manage the work schedule and other employment information of the subordinates
• · Terminate Employees and End the placements for contingent worker

Talent Management

Talent management set of functionalities are more related to the appraisal process, employee reviews and all. Some of the major functionalities are:

• · Users can do self appraisal and submit it to the Supervisors
• · Managers can complete employee appraisals, and submit reviews
• · Users can create and manage events and bookings like presentations, trainings etc.
• · Managers can enrol directs in to specific bookings.
• · Managers can do a suitability matching to find out if there are any suitable profiles.
• · Managers can also do succession planning for the positions that are going to be vacant in future, and identify the possible successors for the positions.

Compensation and Benefits Management

These set of functionality enable the users to do the following:

• · Users can opt for Online or paper pay slips.
• · Users can view their online pay slips.
• · Users can set up their preferred payment methods, along with the bank account details if necessary.
• · Users can see their absences, apply for absences.
• · Managers can approve/ reject absences.
• · Users can manage their Benefits online.

These are most widely used functionalities that a user or a Manager can do with SSHR. However as we had discussed earlier, the possibilities are huge. It is advised to log into SSHR and start exploring the usages, to learn more about the module. Using SSHR should not be a problem, as it is a user friendly GUI for end users.                 

Oracle Core HR

Human resource management system, as the name suggests, is a system that accounts for the human resource / the human capital / asset. This is very nice to count Employees to be an asset to the enterprise. However like all other assets of the Firm, human resource also needs renewal and maintenance. This module in Oracle E-Biz helps us manage our Human capital and lets us embed the HR related data with other modules.

HRMS as a module is needed in almost all other modules in the enterprise. For an example, if we are entering any data related to procurement, we might want to record the person details who wanted it. Similarly for a Project request, we might want to record who the Project manager for the project is. For an Order to be approved, we might need the manager's name of the person who punched that particular order. So HRMS is everywhere. Progressively when we keep reading about the various functionalities, we will be able to relate the concepts to the real world.

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:

EMP_ID	EMP_NAME	POSITION	START_DATE	END_DATE
1234	Joe	Analyst	01-JAN-2002	31-DEC-2006
1234	Joe	Sr. Analyst	01-JAN-2007	31-DEC-2009
1234	Joe	Manager	01-JAN-2010	Till Date

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

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

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

User Name	The name of the Person Type; choose a meaningful name.
System Name	The seeded Person type, of which we are creating a sub class; choose a most appropriate type from here.
Active	To say if the Person type is active as of today.
Default	Each System Person Type will have one and only one default User Person Type. So when the system finds a person to be falling in the System Person type criteria, it will change it to the Default one.

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

Steps:

·         · Query for the employee

·         · Add a new Person Type usage / End date the old one.