joining hr_organization_information and mtl_parameters in Oracle apps

Question

I have seen many joining condition using hr_all_organization_units and mtl_parameters but is it possible to join hr_organization_information with mtl_parameters?

In documentation I could not get difference on hr_all_organization_units and hr_organization_information

select * from hr_organization_information hou, mtl_parameters mp where
mp.organization_id=hou.organization_id;

Is the above query logically correct in Oracle EBS?

Answer 1

hr_all_organization_units holds all organizations, regardless of their classification, e.g. Operating Units, HR Organizations, Inventory Organizations, etc..

mtl_parameters has records only for Inventory Organizations, to store additional inventory related information.

hr_organization_information is a generic table that stores attributes for each organization, e.g. org_information_context='CLASS' to define the type of organization. You can link this table directly with mtl_parameters as you did in the example, but you would:

1. only find records for inventory organizations
2. have duplicate records as you have more than one type of org_information_context for each organization in the hr_organization_information table.

Please note that one organization in hr_all_organization_units can have different classifications at the same time, e.g. Operating Unit and Inventory Organization. Here is an example dataset from an Oracle Vision environment, which has one record per organization and shows their classifications in columns G to Q: https://www.enginatics.com/example/per-organizations/

The PER Organzations Blitz Report shows the link between the organization and the org information table.