Oracle Organization_id Operating_Unit
SELECT hou.business_group_id,
gsob.set_of_books_id set_of_books_id,
gsob.short_name,
gsob.NAME,
gsob.chart_of_accounts_id chart_of_accounts_id,
hou.organization_id organization_id -- , hou.date_from user_definition_enable_date
--, hou.date_to disable_date
,
mp.organization_code organization_code,
hou.NAME organization_name -- , hoi1.org_information2 inventory_enabled_flag
,
DECODE(
fpg.multi_org_flag,
'Y', DECODE(hoi2.org_information_context, 'Accounting Information', TO_NUMBER(hoi2.org_information3), TO_NUMBER(NULL)),
TO_NUMBER(NULL)
) operating_unit,
hou2.NAME ou_name,
DECODE(hoi2.org_information_context, 'Accounting Information', TO_NUMBER(hoi2.org_information2), NULL) legal_entity,
hou3.NAME le_name
FROM hr_organization_units hou,
hr_organization_units hou2,
hr_organization_units hou3,
hr_organization_information hoi1,
hr_organization_information hoi2,
mtl_parameters mp,
gl_sets_of_books gsob,
fnd_product_groups fpg
WHERE hou.organization_id = hoi1.organization_id
AND hou.organization_id = hoi2.organization_id
AND hou.organization_id = mp.organization_id
AND hou2.organization_id =
DECODE(
fpg.multi_org_flag,
'Y', DECODE(hoi2.org_information_context, 'Accounting Information', TO_NUMBER(hoi2.org_information3), TO_NUMBER(NULL)),
TO_NUMBER(NULL)
)
AND hou3.organization_id = DECODE(hoi2.org_information_context, 'Accounting Information', TO_NUMBER(hoi2.org_information2), NULL)
AND hoi1.org_information1 = 'INV'
AND hoi1.org_information2 = 'Y'
AND (hoi1.org_information_context || '') = 'CLASS'
AND (hoi2.org_information_context || '') = 'Accounting Information'
AND hoi2.org_information1 = TO_CHAR(gsob.set_of_books_id)