Customer site details query in oracle apps r12

Customer site details query in oracle apps r12

In this post , We will be discuss about Customer site details query in oracle apps r12. This query helps to extract the complete Customer Site Detail information. I am also sharing the Important Tables for this Customer Site Details SQL Query. Please find below the complete Customer site details query in oracle apps r12.

Most Important Tables of Customer site details query 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

Complete Customer site details query 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;