Here is the Sample SQL Query to find the HCM Locations related to data.
Please modify accordingly as per your requirements.
select
PLDFT.LOCATION_NAME as "Loc_Name",
PL.INTERNAL_LOCATION_CODE as "Loc_Code",
PLDFT.DESCRIPTION as "Loc_Description",
PLDF.ACTIVE_STATUS as "Loc_Status",
HAOUF.NAME as "Loc_Inventory Organization",
TO_CHAR(PLDF.EFFECTIVE_START_DATE,'DD-Mon-YYYY','nls_date_language=english') as "Loc_Effective Start Date",
PAR.ACTION_REASON as "Loc_Action Reason",
FSS.SET_NAME as "Loc_Location Set",
(select TERRITORY_SHORT_NAME from FND_TERRITORIES_VL where TERRITORY_CODE = PAF.COUNTRY) as "Loc_Country",
PAF.ADDRESS_LINE_1 as "Loc_Street Address/Address 1",
PAF.TOWN_OR_CITY as "Loc_City/Prefecture",
PAF.REGION_2 as "Loc_State/Province",
PAF.POSTAL_CODE as "Loc_Zip Code/Postal Code",
PAF.ADDL_ADDRESS_ATTRIBUTE4 as "Loc_Tax District",
PAF.REGION_1 as "Loc_County",
FTV.NAME as "Loc_Time Zone Code",
(select meaning from fnd_lookup_values_vl where lookup_type = 'PER_CORRESP_LANG' and lookup_code= PLDF.OFFICIAL_LANGUAGE_CODE) as "Loc_Official Language",
PLDF.EMAIL_ADDRESS as "Loc_E - Mail",
(select meaning from fnd_lookup_values_vl where lookup_type = 'ORA_PER_GEOGRAPHIC_TREE_NODES' and lookup_code= PLDF.GEO_HIERARCHY_NODE_VALUE) as "Loc_Geographic Hierarchy",
PLDF.TELEPHONE_NUMBER_1 as "Loc_Main Phone",
PLDF.TELEPHONE_NUMBER_2 as "Loc_Fax",
PLDF.TELEPHONE_NUMBER_3 as "Loc_Other Phone",
PLDF.SHIP_TO_SITE_FLAG as "Loc_Ship-to Site",
(
case when PLDF.SHIP_TO_SITE_FLAG = 'N'
then PLDFT2.LOCATION_NAME
end
) as "Loc_Ship-to Location",
PLDF.RECEIVING_SITE_FLAG as "Loc_Receiving Site",
PLDF.BILL_TO_SITE_FLAG as "Loc_Bill-to Site",
PLDF.OFFICE_SITE_FLAG as "Loc_Office Site",
PPNF.LIST_NAME as "Loc_Designated Receiver"
from
PER_LOCATIONS PL,
PER_LOCATION_DETAILS_F PLDF,
PER_LOCATION_DETAILS_F_TL PLDFT,
HR_ALL_ORGANIZATION_UNITS_F_VL HAOUF,
PER_ACTION_OCCURRENCES PAO,
PER_ACTION_REASONS_VL PAR,
FND_SETID_SETS FSS,
PER_ADDRESSES_F PAF,
FND_TIMEZONES_VL FTV,
PER_PERSON_NAMES_F PPNF,
PER_LOCATIONS PL2,
PER_LOCATION_DETAILS_F PLDF2,
PER_LOCATION_DETAILS_F_TL PLDFT2
WHERE
PL.LOCATION_ID = PLDF.LOCATION_ID
and PLDF.LOCATION_DETAILS_ID = PLDFT.LOCATION_DETAILS_ID(+)
and PLDFT.language(+) = 'US'
and PLDF.INVENTORY_ORGANIZATION_ID = HAOUF.ORGANIZATION_ID (+)
and PL.ACTION_OCCURRENCE_ID = PAO.ACTION_OCCURRENCE_ID (+)
and PAO.ACTION_REASON_ID = PAR.ACTION_REASON_ID (+)
and FSS.LANGUAGE = 'US'
and PL.SET_ID = FSS.SET_ID
and PLDF.MAIN_ADDRESS_ID = PAF.ADDRESS_ID
and PLDF.TIMEZONE_CODE = FTV.TIMEZONE_CODE (+)
and PLDF.DESIGNATED_RECEIVER_ID = PPNF.PERSON_ID (+)
and PPNF.NAME_TYPE (+) = 'GLOBAL'
and trunc(sysdate) between trunc(PPNF.EFFECTIVE_START_DATE(+)) and trunc(PPNF.EFFECTIVE_END_DATE(+))
and trunc(sysdate) between trunc(PLDF.EFFECTIVE_START_DATE(+)) and trunc(PLDF.EFFECTIVE_END_DATE(+))
and trunc(sysdate) between trunc(PLDFT.EFFECTIVE_START_DATE(+)) and trunc(PLDFT.EFFECTIVE_END_DATE(+))
and trunc(sysdate) between trunc(HAOUF.EFFECTIVE_START_DATE(+)) and trunc(HAOUF.EFFECTIVE_END_DATE(+))
and trunc(sysdate) between trunc(PAF.EFFECTIVE_START_DATE(+)) and trunc(PAF.EFFECTIVE_END_DATE(+))
and trunc(sysdate) between trunc(PLDF2.EFFECTIVE_START_DATE(+)) and trunc(PLDF2.EFFECTIVE_END_DATE(+))
and trunc(sysdate) between trunc(PLDFT2.EFFECTIVE_START_DATE(+)) and trunc(PLDFT2.EFFECTIVE_END_DATE(+))
and PLDF.SHIP_TO_LOCATION_ID = PL2.LOCATION_ID (+)
and PL2.LOCATION_ID = PLDF2.LOCATION_ID (+)
and PLDF2.LOCATION_DETAILS_ID = PLDFT2.LOCATION_DETAILS_ID (+)
and PLDFT2.language (+) = 'US'
order by PLDFT.LOCATION_NAME
