Oracle Fusion Organization Classifications (with organization SQL queries)

Organization classifications define the purpose of the organization, 

whether it’s a department, a division, or a legal entity. 

In some enterprises, an organization can have 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.

Below are the few valid classifications available in oracle fusion for given organization id:

CST
DEPARTMENT
ENTERPRISE
FUN_BUSINESS_UNIT
HCM_LEMP (Legal Entities)
HCM_LRU  (Legal Reporting Unit)
HCM_PSU  (Payroll Statutory Unit)

HCM_TRU  (Tax Reporting Unit)
HCM_REPORTING_ESTABLISHMENT
INV
PA_EXPENDITURE_ORG
PA_PROJECT_ORG
PRJ_PROJECT_UNIT
SALES_RESOURCE_ORG

--SQL Query to get Organization details with Organization Classification      
SELECT hao.organization_id
    ,haot.NAME
    ,hao.internal_external_flag
    ,hao.type
    ,haoc.classification_code
    ,hao.effective_start_date
    ,hao.effective_end_date
    ,haot.LANGUAGE
FROM hr_all_organization_units_f    hao     --Table
    ,hr_organization_units_f_tl     haot    --Table
    ,hr_org_unit_classifications_x  haoc    --View
WHERE 1=1
AND hao.organization_id         = haot.organization_id
AND hao.organization_id         = haoc.organization_id
AND hao.organization_id         = haot.organization_id
AND hao.effective_start_date    = haot.effective_start_date 
AND Trunc(sysdate) BETWEEN hao.effective_start_date AND hao.effective_end_date
;

--SQL Query to get Inventory Organization Details
SELECT HOU.organization_id ORGANIZATION_ID
	,HOU.name ORGANIZATION_NAME
	,MP.organization_code ORGANIZATION_CODE
	,LGR.ledger_id SET_OF_BOOKS_ID
	,LGR.chart_of_accounts_id CHART_OF_ACCOUNTS_ID
	,LGR.currency_code
	,LGR.period_set_name
	,DECODE(HOI1.STATUS, 'A', 'Y', 'N') INVENTORY_ENABLED_FLAG
	,BU.bu_name BUSINESS_UNIT_NAME
	,MP.business_unit_id BUSINESS_UNIT_ID
	,MP.legal_entity_id LEGAL_ENTITY
	,HOU.TYPE ORGANIZATION_TYPE
FROM hr_all_organization_units_x HOU
    ,hr_org_unit_classifications_x HOI1
    ,inv_org_parameters MP
    ,gl_ledgers LGR
    ,fun_all_business_units_v BU
WHERE 1=1
AND HOU.organization_id         = HOI1.organization_id
AND HOU.organization_id         = MP.organization_id
AND HOI1.classification_code    = 'INV'
AND BU.primary_ledger_id        = LGR.ledger_id(+)
AND LGR.object_type_code(+)     = 'L'
AND NVL(LGR.complete_flag, 'Y') = 'Y'
AND BU.bu_id(+)                 = MP.business_unit_id