Query to get Organization detail in Fusion

Overview :

Below query to give the organization information in Fusion

Query:

SELECT hra.organization_id as bu_id
,hro.name organization_name
,hra.business_group_id, hro.*
FROM   HR_ALL_ORGANIZATION_UNITS_F hra,
HR_ORGANIZATION_UNITS_F_TL hro
WHERE 1=1
hra.ORGANIZATION_ID = hro.ORGANIZATION_ID
AND hra.EFFECTIVE_START_DATE = hro.EFFECTIVE_START_DATE
AND hra.EFFECTIVE_END_DATE = hro.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN hra.EFFECTIVE_START_DATE AND hra.EFFECTIVE_END_DATE
AND hro.LANGUAGE=’US’