Here is the Sample Query for the Core HR - Legal Entity Details.
Please use this as a reference and modify as per the requirements accordingly.
select
HG.GEOGRAPHY_NAME as "Country",
XEP.NAME as "Name",
to_char(' '||to_char(XEP.LEGAL_ENTITY_IDENTIFIER)||' ') as "Legal Entity Identifier",
TO_CHAR(XEP.EFFECTIVE_FROM,'DD-Mon-YYYY','nls_date_language=english') as "Start Date",
TO_CHAR(XEP.EFFECTIVE_TO,'DD-Mon-YYYY','nls_date_language=english') as "End Date",
XEP.PSU_FLAG as "Payroll Statutory Unit Flag",
XEP.LEGAL_EMPLOYER_FLAG as "Legal Employer",
XEP_PSU.NAME as "Payroll Statutory Unit",
HPS.OVERALL_PRIMARY_FLAG as "Primary",
(case when sysdate between HPS.START_DATE_ACTIVE and HPS.END_DATE_ACTIVE
then 'Current'
else 'Past Date'
end ) as "Current Status",
(HZ.ADDRESS1||','||HZ.CITY||','||HZ.STATE||' '||HZ.POSTAL_CODE||HZ.COUNTY) as "Address",
(select meaning from fnd_lookup_values_vl where lookup_type = 'PARTY_SITE_USE_CODE' and lookup_code= HPSU.SITE_USE_TYPE) as "Purpose",
HPS.PARTY_SITE_NUMBER as "Site Number",
HZ.TIMEZONE_CODE as "Timezone"
from
XLE_ENTITY_PROFILES XEP,
XLE_ENTITY_PROFILES XEP_PSU,
HZ_GEOGRAPHIES HG,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HZ,
HZ_PARTY_SITE_USES HPSU
where
XEP.PARENT_PSU_ID = XEP_PSU.LEGAL_ENTITY_ID(+)
and XEP.GEOGRAPHY_ID = HG.GEOGRAPHY_ID
and HG.GEOGRAPHY_TYPE = 'COUNTRY'
and sysdate between HG.START_DATE and HG.END_DATE
and XEP.PARTY_ID = HPS.PARTY_ID(+)
and sysdate between HPS.START_DATE_ACTIVE(+) and HPS.END_DATE_ACTIVE(+)
and HPS.LOCATION_ID = HZ.LOCATION_ID(+)
and HPS.PARTY_SITE_ID = HPSU.PARTY_SITE_ID(+)
AND 'Legal Entity' IN (:Data_Heads)
order by XEP.NAME
