Organizations in HRMS

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.


This sql will prompt you for an organization name

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';