Query to get ship to address and bill to address in Oracle Apps

Query to get ship to address and bill to address in Oracle Apps

In this post , We will be discuss about Query to get ship to and bill to address in oracle Apps. Customer ship to address and bill to address  stores in Customer HZ_Locations table 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 get ship to address and bill to address in Oracle Apps.

 

 

Important Tables of Customer ship to address and bill to address in Oracle Apps

    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_PURPOSE_CODE,

    ,hl.COUNTRY Country

    ,hl.ADDRESS1 BILL&SHIP_TO_ADDRESS1

    ,hl.ADDRESS2 BILL&SHIP_TO_ADDRESS2

    ,hl.ADDRESS3 BILL&SHIP_TO_ADDRESS3

    ,hl.ADDRESS4 BILL&SHIP_TO_ADDRESS4

    ,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;