————————————————————————————–
Oracle EBS – Query to get MultiOrg Info
————————————————————————————–
————– Execute the below query into EBS database.—————————————-
SELECT mp.organization_code org_code, org.organization_id org_id,
org.NAME org_name, hl.location_id, hl.location_code, hl.address_line_1,
hl.address_line_2, hl.address_line_3, hl.town_or_city, hl.country,
hl.postal_code, ou.organization_id ou_id, ou.NAME ou,
le.legal_entity_id le_id, le.NAME le, gl.ledger_id,
gl.NAME primary_ledger, gl.currency_code, bg.NAME bg,
(SELECT organization_code
FROM apps.mtl_parameters
WHERE organization_id =
(SELECT parameter_value
FROM apps.oe_sys_parameters_all
WHERE parameter_code = ‘MASTER_ORGANIZATION_ID’
AND org_id = ou.organization_id)) ivo,
(SELECT organization_code
FROM apps.mtl_parameters
WHERE organization_id = (SELECT inventory_organization_id
FROM ap.financials_system_params_all
WHERE org_id = ou.organization_id)) pvo,
(SELECT period_name || ‘ : ‘ || open_flag
FROM apps.org_acct_periods
WHERE period_start_date = TRUNC (SYSDATE)
AND organization_id = mp.organization_id) inv_period,
(SELECT period_name
–|| ‘ : ‘ || show_status
FROM apps.gl_period_statuses_v
WHERE start_date = TRUNC (SYSDATE)
AND ledger_id = gl.ledger_id
AND application_id = 101) gl_ledger_period,
(SELECT period_name
|| ‘ : ‘ || show_status
FROM apps.gl_period_statuses_v
WHERE start_date = TRUNC (SYSDATE)
AND ledger_id = gl.ledger_id
AND application_id = 200) ap_period,
(SELECT period_name
|| ‘ : ‘ || show_status
FROM apps.gl_period_statuses_v
WHERE start_date = TRUNC (SYSDATE)
AND ledger_id = gl.ledger_id
AND application_id = 222) ar_period,
(SELECT period_name
|| ‘ : ‘ || show_status
FROM apps.gl_period_statuses_v
WHERE start_date = TRUNC (SYSDATE)
AND ledger_id = gl.ledger_id
AND application_id = 201) po_period
FROM apps.xle_entity_profiles le,
apps.hr_all_organization_units ou,
apps.hr_all_organization_units org,
apps.hr_all_organization_units bg,
apps.mtl_parameters mp,
apps.gl_ledgers gl,
apps.hr_organization_information ouinfo,
apps.hr_organization_information orginfo,
apps.hr_locations hl
WHERE mp.organization_id = org.organization_id
AND org.organization_id = orginfo.organization_id
AND org.location_id = hl.location_id
AND orginfo.org_information_context = ‘Accounting Information’
AND orginfo.org_information3 = ou.organization_id
AND orginfo.org_information1 = gl.ledger_id
AND orginfo.org_information2 = le.legal_entity_id
AND ou.organization_id = ouinfo.organization_id
AND ouinfo.org_information_context = ‘Operating Unit Information’
AND ouinfo.org_information2 = le.legal_entity_id
AND ouinfo.org_information3 = gl.ledger_id
AND bg.organization_id = ou.business_group_id
