Customer address table in oracle apps r12

Customer address table in oracle apps r12

In this post , We will be discuss about Customer address table in oracle apps r12. Customer Addresses stores in different - different tables in oracle apps r12. We have two types of Customer Addresses in Oracle Apps r12 which is Bill-To and Ship-To. In this Post I will help to share the Tables and the SQL query to Extract the Customer address table in oracle apps r12.

 

 

Important Tables of Customer address in oracle apps r12

    1.hz_parties hp
    2.ar_customers ac
    3.hz_cust_accounts hca
    4.hz_cust_acct_sites_all hcas
    5.hz_cust_site_uses_all hcsua
    6.hz_party_sites hps
    7.hz_locations hl
    8.hz_party_usg_assignments

 

 

Sql Query Using Customer address table in oracle apps r12

SELECT DISTINCT customer_name,hcsua.SITE_USE_CODE SITE_USE_CODE,

    ,hl.COUNTRY Country

    ,hl.ADDRESS1 Address_Line_1

    ,hl.ADDRESS2 Address_Line_2

    ,hl.ADDRESS3 Address_Line_3

    ,hl.ADDRESS4 Address_Line_4

    ,hl.CITY City

    ,hl.STATE STATE

    ,hl.PROVINCE Province

    ,hl.COUNTY County

    ,hl.POSTAL_CODE Postal_Code

    ,hl.POSTAL_PLUS4_CODE Postal_Code_Extension

    ,hl.LANGUAGE Location_Language

    ,hl.DESCRIPTION Description

    ,hl.SHORT_DESCRIPTION Short_Description

    ,hl.SALES_TAX_GEOCODE Sales_Tax_Geocode

    ,hl.SALES_TAX_INSIDE_CITY_LIMITS Sales_Tax_Inside_City_Limits

    ,hl.TIMEZONE_ID Timezone_Code_Identifier

    ,NULL Address_Line_1

    ,NULL Address_Validation_Source

    ,NULL Return_Address_Valid_Code

    ,hl.DATE_VALIDATED Address_Valid_Date

    ,hl.ADDRESS_EFFECTIVE_DATE Address_Eff_Date

    ,hl.ADDRESS_EXPIRATION_DATE Address_Exp_Date

    ,hl.VALIDATION_STATUS_CODE Valid_Indicator

    ,hl.DO_NOT_VALIDATE_FLAG Valid_Ineligible_Indicator

    ,NULL Interface_Status

    ,NULL Error_Identifier

FROM hz_parties hp,

    ar_customers ac,

     hz_cust_accounts hca,

     z_cust_acct_sites_all hcas,

     hz_cust_site_uses_all hcsua,

    ,hz_party_sites hps

    ,hz_locations hl

    ,hz_party_usg_assignments hpua

WHERE hp.party_id = hps.party_id

and hca.cust_account_id = hcas.cust_account_id

and hcas.cust_account_id=ac.customer_id

AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id

and hcas.party_site_id=hps.party_site_id

    AND hps.location_id = hl.location_id

    AND hp.party_id = hpua.party_id

    AND hpua.PARTY_USAGE_CODE = 'CUSTOMER'

    AND hp.STATUS = 'A'

    AND TRUNC(NVL(hl.ADDRESS_EXPIRATION_DATE,SYSDATE)) >= TRUNC(SYSDATE)

ORDER BY hl.ORIG_SYSTEM_REFERENCE;