HR Organization Information

HR_ORGANIZATION_INFORMATION is used to hold two distinct sets
of organization information. When ORG_INFORMATION_CONTEXT

is set to CLASS this table acts as an intersection table between organizations and
organization classifications.
ORGANIZATION_ID identifies the organization and ORG_INFORMATION1 holds the
organization classification name.
This information is maintained by the organization classifications block of the
Organization form.
When ORG_INFORMATION_CONTEXT is set to an information type held in
HR_ORG_INFORMATION_TYPES the ORG_INFORMATION1–20 columns hold details for the
specific information type.
This information is maintained by a predefined protected descriptive flexfield.
(Org Developer DF.)
select * from HR_LOCATIONS where trunc(sysdate) = trunc(creation_date) and

location_id = 30982
select * from HR_ORGANIZATION_UNITS where trunc(sysdate) = trunc(creation_date)
select * from HR_ALL_ORGANIZATION_UNITS where trunc(sysdate) = trunc(creation_date)
select ORGANIZATION_ID,
NAME,
BUSINESS_GROUP_ID,
LOCATION_ID,
DATE_FROM,
INTERNAL_EXTERNAL_FLAG,
INTERNAL_ADDRESS_LINE,
TYPE from HR_ALL_ORGANIZATION_UNITS where trunc(sysdate) = trunc(creation_date)
select * from HR_ORGANIZATION_INFORMATION where trunc(sysdate) =

trunc(creation_date)
select ORG_INFORMATION_ID,
ORGANIZATION_ID,
ORG_INFORMATION_CONTEXT,
ORG_INFORMATION1 from HR_ORGANIZATION_INFORMATION where trunc(sysdate) =
All organizations are maintained in HR_ALL_ORGANIZATION_UNITS table and

organization classifications like Legal entity or operating
unit information are stored in HR_ORGANIZATION_INFORMATION table. There are 3 views
provided based on these 2 tables to easily find out
the Legal entity, Operating unit and Inventory organization viz:
BUSINESS_GROUP_ID ,
NAME ,
DATE_FROM ,
DATE_TO
from apps.HR_LEGAL_ENTITIES
BUSINESS_GROUP_ID,
NAME,
DATE_FROM,
DATE_TO,
SET_OF_BOOKS_ID from HR_OPERATING_UNITS
BUSINESS_GROUP_ID,
USER_DEFINITION_ENABLE_DATE,
DISABLE_DATE,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
SET_OF_BOOKS_ID,
CHART_OF_ACCOUNTS_ID,
INVENTORY_ENABLED_FLAG,
OPERATING_UNIT,
LEGAL_ENTITY from ORG_ORGANIZATION_DEFINITIONS
Query to find few business groups set up in the instance :
select
business_group_id,name
from per_business_groups
where lower(name) like '%vision corporation%'
order by name
Query to find SOBs set up in the instance :
select
set_of_books_id,
name sob_name,
chart_of_accounts_id,
chart_of_accounts_name,
period_set_name calendar_period,
accounted_period_type,
user_period_type,
currency_code
from gl_sets_of_books_v
where set_of_books_id=1
A very important query to find out inventory organizations for an operating unit :
select
organization_id,
organization_code,
organization_name,
(select location_id from hr_all_organization_units ou
where od.organization_id=ou.organization_id) location_id,
user_definition_enable_date,
disable_date,
chart_of_accounts_id,
inventory_enabled_flag,
operating_unit,
legal_entity,
set_of_books_id,
business_group_id
from org_organization_definitions od
where operating_unit=204 and ORGANIZATION_ID = 9073
order by organization_code
select * from org_organization_definitions where ORGANIZATION_NAME like 'M12%'--
trunc(sysdate) = trunc(creation_date)
select *from mtl_parameters where ORGANIZATION_ID = 9073
select * from MTL_SECONDARY_INVENTORIES_FK_V where trunc(sysdate) =

select * from MTL_SECONDARY_INVENTORIES where trunc(sysdate) = trunc(creation_date)
select * from MTL_UOM_CLASSES where trunc(sysdate) = trunc(creation_date)
select * from MTL_ITEM_LOCATIONS where trunc(sysdate) = trunc(creation_date)
Query to fetch Business group Details - 1
SELECT business_group_id,
organization_id,
NAME,
date_from,
date_to,
internal_address_line,
location_id,
comments,
default_start_time,
default_end_time,
working_hours,
frequency,
short_name,
method_of_generation_emp_num,
method_of_generation_apl_num,
grade_structure,
people_group_structure,
job_structure,
cost_allocation_structure,
position_structure,
legislation_code,
currency_code,
security_group_id,
enabled_flag,
competence_structure,
method_of_generation_cwk_num
FROM per_business_groups;
Query to fetch Business group Details - 2

SELECT o.organization_id,
o.organization_id,
otl.NAME,
o.date_from,
o.date_to,
o.internal_address_line,
o.location_id,
o.comments
FROM hr_all_organization_units o,
hr_all_organization_units_tl otl,
hr_organization_information o2,
hr_organization_information o3,
hr_organization_information o4
WHERE o.organization_id = otl.organization_id
AND o.organization_id = o2.organization_id(+)
AND o.organization_id = o3.organization_id
AND o.organization_id = o4.organization_id
AND o3.org_information_context = 'Business Group Information'
AND o2.org_information_context(+) = 'Work Day Information'
AND o4.org_information_context = 'CLASS'
AND o4.org_information1 = 'HR_BG'
AND o4.org_information2 = 'Y';
Query to fetch Legal Entity Details
SELECT xle_firstpty.NAME,
xle_firstpty.activity_code,
xle_firstpty.sub_activity_code,
xle_firstpty.registration_number,
xle_firstpty.effective_from,
xle_firstpty.location_id,
xle_firstpty.address_line_1,
xle_firstpty.town_or_city,
xle_firstpty.region_1,
xle_firstpty.postal_code,
xle_firstpty.country,
xle_firstpty.address_style,
xle_cont.contact_name,
xle_cont.contact_legal_id,
xle_cont.title,
xle_cont.job_title,
xle_cont.role
FROM xle_firstparty_information_v xle_firstpty,
xle_legal_contacts_v xle_cont
WHERE 1 = 1
AND XLE_FIRSTPTY.LEGAL_ENTITY_ID = C_REP_ENTITY_ID
AND xle_firstpty.legal_entity_id = xle_cont.entity_id(+);
Query to fetch legal entity details along with OU details
SELECT DISTINCT hrl.country,

hroutl_bg.NAME bg,
hroutl_bg.organization_id,
lep.legal_entity_id,
lep.NAME legal_entity,
hroutl_ou.NAME ou_name,
hroutl_ou.organization_id org_id,
hrl.location_id,
hrl.location_code,
glev.flex_segment_value
FROM xle_entity_profiles lep,
xle_registrations reg,
hr_locations_all hrl,
hz_parties hzp,
fnd_territories_vl ter,
hr_operating_units hro,
hr_all_organization_units_tl hroutl_bg,
hr_all_organization_units_tl hroutl_ou,
hr_organization_units gloperatingunitseo,
gl_legal_entities_bsvs glev
WHERE lep.transacting_entity_flag = 'Y'
AND lep.party_id = hzp.party_id
AND lep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND hrl.location_id = reg.location_id
AND reg.identifying_flag = 'Y'
AND ter.territory_code = hrl.country
AND lep.legal_entity_id = hro.default_legal_context_id
AND gloperatingunitseo.organization_id = hro.organization_id
AND hroutl_bg.organization_id = hro.business_group_id
AND hroutl_ou.organization_id = hro.organization_id
AND glev.legal_entity_id = lep.legal_entity_id;

HR Organization Information

Uploaded by

Copyright:

Available Formats

HR Organization Information

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

HR Organization Information

Uploaded by

Copyright:

Available Formats

What tables store organization and classification information?

What tables store organization and classification information?

What views provide information on legal entities, operating units, and inventory organizations?

What views provide information on legal entities, operating units, and inventory organizations?

HR_ORGANIZATION_INFORMATION is used to hold two distinct sets

of organization information. When ORG_INFORMATION_CONTEXT

select * from HR_LOCATIONS where trunc(sysdate) = trunc(creation_date) and

select * from HR_ORGANIZATION_UNITS where trunc(sysdate) = trunc(creation_date)

select * from HR_ALL_ORGANIZATION_UNITS where trunc(sysdate) = trunc(creation_date)

select * from HR_ORGANIZATION_INFORMATION where trunc(sysdate) =

All organizations are maintained in HR_ALL_ORGANIZATION_UNITS table and

Query to find few business groups set up in the instance :

Query to find SOBs set up in the instance :

select *from mtl_parameters where ORGANIZATION_ID = 9073

select * from MTL_SECONDARY_INVENTORIES_FK_V where trunc(sysdate) =

select * from MTL_SECONDARY_INVENTORIES where trunc(sysdate) = trunc(creation_date)

select * from MTL_UOM_CLASSES where trunc(sysdate) = trunc(creation_date)

select * from MTL_ITEM_LOCATIONS where trunc(sysdate) = trunc(creation_date)

Query to fetch Business group Details - 1

Query to fetch Business group Details - 2

Query to fetch Legal Entity Details

Query to fetch legal entity details along with OU details

SELECT DISTINCT hrl.country,

You might also like