• Post category:Core HR
  • Post comments:0 Comments
  • Post last modified:February 11, 2021
  • Reading time:11 mins read
You are currently viewing Organization Classifications, their importance and queries to get the info
Organization Classifications, their importance and queries to get the info

In this article we will look into Organization Classifications and their purpose in the overall enterprise structure and finally handy queries to get the entities information.

In Fusion, we use the same business object Organization to load multiple entities like Legal Entity, Business Unit, Department, Division, Payroll Statutory Unit and Legal Reporting Unit. We load these entities as Organizations with a different classification.

Table of Contents

What is Organization Classification?

Organization classifications define the purpose of the organization, whether it’s a department, a division, or a legal entity. In some enterprises, organization classifications overlap, which means that the same organization can be assigned multiple classifications.

For example, one organization within an enterprise might be both a project organization and a department. The classifications of organizations vary according to business objectives, legal structure, industry, company culture, size and type of growth. You can create organizations in Oracle Fusion with one or more classifications to reflect your enterprise structure.

Defining an Organization with One Classification

Define each organization in your enterprise as a separate organization with a single classification to reflect your enterprise structure and provide flexibility for expansion. The advantage of setting up separate organizations is the ability to add further organizations to expand the enterprise easily. For example, if your enterprise acquires another company which has a different line of business in a country in which you employ people, you can create a division, a legal entity, and additional departments. Classify the new legal entity as a legal employer and payroll statutory unit for the company’s payroll tax and social insurance.

Defining an Organization with Multiple Classifications

Define an organization with multiple classifications if the organization has multiple purposes. For example, use an organization within the sales applications as a department that employs salespeople and classify it as a department and a sales organization. Or, if your enterprise operates and employs people in multiple countries, create a legal entity for each country using the Manage Legal Entity task. Then use the Manage Departments task to classify the legal entity as a department.

SQL Query to get all Organization Classifications:

select distinct classification_code from HR_ORG_UNIT_CLASSIFICATIONS_F

Output:

DEPARTMENT (For Departments)
HCM_PSU (For Payroll Statutory Units)
HCM_LRU (For Legal Reporting Units)
FUN_BUSINESS_UNIT (For Business Units)
HCM_LEMP (For Legal Entities)
HCM_TRU (For Tax Reporting Units)
HCM_DIVISION (For Divisions)

ORA_PER_UNION (For Unions)

Sample Enterprise Configuration requires an enterprise with multiple divisions, ledgers, legal employers, payroll statutory units, tax reporting units, legislative data groups, departments, cost centers, and business units as shown below:

image 35 - Organization Classifications, their importance and queries to get the info

SQL Queries to fetch all entities based on Organization Classification

SQL Queries to fetch Business Unit:

SELECT hauft.NAME
FROM   HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE   haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
AND     SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

SELECT BU_NAME  
FROM FUN_ALL_BUSINESS_UNITS_V
WHERE SYSDATE BETWEEN DATE_FROM AND DATE_TO 
order by BU_NAME

SQL Query to fetch Division:

SELECT hauft.NAME
FROM   HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE   haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_DIVISION'
AND     SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

SELECT hauft.NAME
FROM   HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE   haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_LRU'
AND     SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

SQL Query to find UNION:

SELECT
haotl.name               ,
hao.organization_id      ,
hao.effective_start_date ,
hao.effective_end_date
FROM
hr_org_unit_classifications_f hac ,
hr_all_organization_units_f   hao ,
hr_organization_units_f_tl    haotl
WHERE
hao.organization_id = hac.organization_id
AND     hao.organization_id = haotl.organization_id
AND     hao.effective_start_date BETWEEN hac.effective_start_date AND     hac.effective_end_date
AND     haotl.language             = userenv('LANG')
AND     haotl.effective_start_date = hao.effective_start_date
AND     haotl.effective_end_date   = hao.effective_end_date
AND     hac.classification_code    = 'ORA_PER_UNION'

SQL Query for Tax Reporting Unit:

SELECT hauft.NAME
FROM   HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE   haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_TRU'
AND     SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

SQL Queries to find Departments:

SELECT hauft.NAME
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT'
AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date
order by hauft.NAME
SELECT DISTINCT NAME  
FROM  PER_DEPARTMENTS
 WHERE (SYSDATE BETWEEN EFFECTIVE_START_DATE(+) AND EFFECTIVE_END_DATE(+))
order by NAME

SELECT hauft.NAME
FROM   HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE   haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP'
AND     SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

SQL Query to find Payroll Statutory Units:

SELECT hauft.NAME
FROM   HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE   haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_PSU'
AND     SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date