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