Thursday 22 May 2014

Oracle HRMS Organizations Technical Details

Tables:-

HR_ALL_ORGANIZATION_UNITS
The basic details of an organization are stored here.
There is also a translation table HR_ALL_ORGANIZATION_UNITS_TL for the organization
name in each language installed.

HR_ORG_INFORMATION_TYPES
This holds the different types of organization information.
Each type is defined in the Org Developer DF as a context value. eg Work Day Information,
Business Group Information, etc.
When using the Organizations screen you would see the information types relevant to the
org classification.

HR_ORG_INFO_TYPES_BY_CLASS
This table holds the org information types that are available for each classification.
This data is seeded and there is no screen that displays these groupings.
When you select a classification on the Org screen and press the Other button, this table is
referenced to display the relevant org information types.

HR_ORGANIZATION_INFORMATION
This table is more complicated as it stores two distinct sets of information.
To know which type of information is stored you need to check the value in column

ORG_INFORMATION_CONTEXT.
When the value is 'CLASS' the row is used to link an organization to a classification. There
will be one row for every classification used by an organization. The classification name is
held in column ORG_INFORMATION1.
If you create an organization, a row will be added here for every classification you save
against that org.
When the value is set to an information type (from HR_ORG_INFORMATION_TYPES) the
columns ORG_INFORMATION1-20 are used to hold the values for that information type.
So when you are in the Define Organizations screen and you press the others button and
select an information type, the values you see in the fields on the screen come from this
table.
The information type is a dff context with some segments defined. Each segment is mapped
to one of the ORG_INFORMATIONx columns in this table. When you open the information
type field you see the individual segments and the value for each segment is held in the
column in table HR_ORGANIZATION_INFORMATION that matches the column specified in
the segment definition.
The lookup ORG_TYPE holds values for the 'Type' field on the Define Organization screen.
The lookup ORG_CLASS holds values for the Classifications Name field on the Define
Organization screen.

SQL> select i.organization_id, o.name, l.meaning
from hr_all_organization_units o
, hr_organization_information i
, fnd_lookup_values l
where o.organization_id = i.organization_id
and o.name = '&Organization_Name'
and i.org_information1 = l.lookup_code
and l.lookup_type = 'ORG_CLASS'
and i.org_information_context = 'CLASS';

This sql will prompt you for an organization information values

SQL> select o.name
, i.org_information1 Normal_Start_Time
, i.org_information2 Normal_End_Time
, i.org_information3 Working_Hours
, i.org_information4 Frequency
from hr_organization_information i
, hr_all_organization_units o
where o.name = '&Organization_Name'
and o.organization_id = i.organization_id


and i.org_information_context = 'Work Day Information';

No comments:

Post a Comment