Person Types in Oracle HRMS

There are two tables (per_all_people_f,per_person_type_usages_f) where you can find the person_types for an employee. Developers usually gets misguided to which person_type_id we need to consider.

The answer is we should always need to use the table per_person_type_usages_f to retrieve the correct person_id. Though the person_type_id in the two tables appear to be same but they are different and that can perty much mislead us. Here are the reasons below:

1. The per_all_people_f.person_type_id holds the default user_person_type for a given system_person_type. It is not maintained to reflect the true user person type. For each system_person_type in each business group, there can only be one USER_PERSON_TYPE with a default_flag = 'Y'. However, an employee can have a user_person_type = Contractor, What will show in person_type_id of the two tables with a CONTRACTOR user_person_type will be as follows:

PER_ALL_PEOPLE_F : 2
PER_PERSON_TYPE_USAGES_F: 4

where 'Contractor' is the subgroup of the person type 'Employee' and 2 is the person_type_id for the employee and 4 is the person_type_id of contractor..

So, only the person_type_usages_f table will give us a true picture of user_person_type and should be used in ALL HR transactions. The per_all_people_f.person_type_id is for use by non HR applications, such as Purchasing which doesn't care about the various user flavors but the system_person_type of 'EMP'. When HR Development made the change to use per_person_type_usages_f table, they retain the person_type_id column in per_all_people_f table for compatibility with other applications. This scheme will enable other non-HR applications still work.

NOTE: When accessing per_person_type_usages_f table for a given person at a given time, there can be multiple rows returned because per_person_type_usages_f table will have a row for each person_type. When an employee is an 'EMP_APL', there'll be two rows returned: one for a system_person_type = 'EMP'with user_person_type = whatever, and another row for system_person_type = 'APL' and user_person_type = whatever. Any SQL statement or cursor needs to handle the multiple rows returned condition. Otherwise, one will get an error something like "Exact fetch returns more than 1 row".