Differences between per_all_people_f.person_type_id and
per_person_type_usages_f.person_type_id
References
~~~~~~~~
When you need to identify the person_type_id of a person, which one do you
use:
per_all_people_f.person_type_id or per_person_type_usages_f.person_type_id?
The answer is you should always use the per_person_type_usages_f.
person_type_id. Though on the surface the two fields seem to be the same but
they are different and that can mislead you. 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. Confusing??? Please read on the example below:
BG_ID PER_TYP_ID SYS_PER_TYPE USER_PER_TYPE DEFAULT_FLAG
=========================================================
1001 20 EMP Employee Y
1001 30 EMP Temp N
1001 40 EMP Contractor N
1001 50 EMP Placeholder N
1001 60 EMP Student N
For each system_person_type in each business group, there can only be one
USER_PERSON_TYPE with a default_flag = 'Y'. In the above example, the
default user_person_type is Employee with Person Type ID = 20.
However, an employee can have a user_person_type = Contractor, in this case is
person_type_id 40. 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: 20
PER_PERSON_TYPE_USAGES_F: 40
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".
Post a Comment