The blog provides the Oracle EBS R12 AR SQL to get to get Customer Address details
SELECT HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HP.PARTY_NAME CUSTOMER_NAME,
HPS.PARTY_SITE_NUMBER CUST_SITE_NUMBER,
HL.ADDRESS1 ADDRESS_LINE1,
HL.ADDRESS2 ADDRESS_LINE2,
HL.ADDRESS3 ADDRESS_LINE3,
HL.ADDRESS4 ADDRESS_LINE4,
HL.CITY CITY,
HL.POSTAL_CODE POSTAL_CODE,
HL.STATE STATE,
FND_TL.TERRITORY_SHORT_NAME COUNTRY,
HCSUA1.LOCATION BILL_TO_LOCATION,
HCSUA2.LOCATION SHIP_TO_LOCATION,
HCASA1.ORG_ID ORG_ID
FROM
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCT_SITES_ALL HCASA1,
HZ_CUST_SITE_USES_ALL HCSUA1,
HZ_LOCATIONS HL,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
FND_TERRITORIES_TL FND_TL,
HZ_CUST_ACCT_SITES_ALL HCASA2,
HZ_CUST_SITE_USES_ALL HCSUA2
WHERE HP.PARTY_ID = HPS.PARTY_ID(+)
AND HP.PARTY_ID = HCA.PARTY_ID(+)
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HL.COUNTRY = FND_TL.TERRITORY_CODE
AND HCASA1.PARTY_SITE_ID(+) = HPS.PARTY_SITE_ID
AND HCASA2.PARTY_SITE_ID(+) = HPS.PARTY_SITE_ID
AND HCSUA1.CUST_ACCT_SITE_ID(+) = HCASA1.CUST_ACCT_SITE_ID
AND HCSUA2.CUST_ACCT_SITE_ID(+) = HCASA2.CUST_ACCT_SITE_ID
AND HCASA1.ORG_ID(+) = FND_PROFILE.VALUE ('ORG_ID')
AND HCASA2.ORG_ID(+) = FND_PROFILE.VALUE ('ORG_ID')
AND HCSUA1.SITE_USE_CODE(+) = 'BILL_TO'
AND HCSUA2.SITE_USE_CODE(+) = 'SHIP_TO'
AND FND_TL.LANGUAGE = USERENV ('LANG')
--AND HCA.ACCOUNT_NUMBER IN ('320454', '314146')
ORDER BY HCA.ACCOUNT_NUMBER ;