Customer information Queries

Ship To Customer Address
Use below query to fetch ship_to address for a Order or Invoice
For Order join hcsu.site_use_id = order.ship_to_org_id
For Invoice join  hcsu.site_use_id = invoice.ship_to_site_use_id
       SELECT hcsu.site_use_id
            ,hcsu.location
            ,hcas.cust_acct_site_id "address_id"
            ,hps.party_site_number
            , hl.address1 || '  ' || hl.address2
            ,hl.city
            ,hl.state
            ,hl.country
            ,hl.postal_code
            ,hcsu.bill_to_site_use_id
            ,hcsu.contact_id
        FROM hz_cust_accounts_all hca
            ,hz_cust_acct_sites hcas
            ,hz_cust_site_uses hcsu
            ,hz_party_sites hps
            ,hz_locations hl
       WHERE 1 = 1
         AND hca.cust_account_id = hcas.cust_account_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND hps.location_id = hl.location_id
         AND hps.party_site_id = hcas.party_site_id
          --AND hcsu.primary_flag = 'Y'
         AND hcsu.status = 'A'
         AND hcsu.site_use_code = 'SHIP_TO'
         AND hca.cust_account_id = <customer_id>
         AND hcsu.org_id =<org_id>;

Bill To Customer Address
Use below query to fetch bill_to address for a Order or Invoice
For Order join hcsu.site_use_id = order.invoice_to_org_id
For Invoice join  hcsu.site_use_id = invoice.bill_to_site_use_id
       SELECT hcsu.site_use_id
            ,hcsu.location
            ,hcas.cust_acct_site_id "address_id"
            ,hps.party_site_number
            ,hl.address1 || '  ' || hl.address2
            ,hl.city
            ,hl.state
            ,hl.country
            ,hl.postal_code
            ,hcsu.contact_id
        FROM hz_cust_accounts_all hca
            ,hz_cust_acct_sites hcas
            ,hz_cust_site_uses hcsu
            ,hz_party_sites hps
            ,hz_locations hl
       WHERE 1 = 1
         AND hca.cust_account_id = hcas.cust_account_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND hps.location_id = hl.location_id
         AND hps.party_site_id = hcas.party_site_id
         AND hcsu.primary_flag = 'Y'
         AND hcsu.status = 'A'
         AND hcsu.site_use_code = 'BILL_TO'
         AND hca.cust_account_id = <customer_id>
         AND hcsu.org_id =<org_id>;

Cusomer Contact's by Party/SiteUses & Customer Contact information
Use below query to get the customer contact by party/site uses.
You can use the same query to get contacts contact type by joining with  hz_contact_points hcp
using the conditions hcp.owner_table_name = 'HZ_PARTIES' and hcp.owner_table_id = hcar.party_id (from below query)

SELECT  hcar.cust_account_id
    , hcar.cust_acct_site_id
    , hcar.party_id rel_party_id
    , hr.object_id org_party_id
    , hr.subject_id person_party_id
    , hr.relationship_type
    , hp.party_name contact_name
    FROM hz_cust_account_roles hcar
    , hz_relationships hr
    , hz_parties hp
    WHERE 1                   =1
        AND hcar.role_type       = 'CONTACT'
        AND hcar.party_id        = hr.party_id
        AND hr.relationship_code = 'CONTACT_OF'
        AND hr.subject_id        = hp.party_id
        AND hcar.cust_account_id = <customer_id>

 Above query with hz_contact_points join to get site uses contact details

SELECT  hcar.cust_account_id
    , hcar.cust_acct_site_id
    , hcar.party_id rel_party_id
    , hr.object_id org_party_id
    , hr.subject_id person_party_id
    , hr.relationship_type
    , hp.party_name contact_name
    , hcp.email_address
    FROM hz_cust_account_roles hcar
    , hz_relationships hr
    , hz_parties hp
    , hz_contact_points hcp
    WHERE 1                   =1
        AND hcar.role_type       = 'CONTACT'
        AND hcar.party_id        = hr.party_id
        AND hr.relationship_code = 'CONTACT_OF'
        AND hr.subject_id        = hp.party_id
        AND hcp.owner_table_id = hcar.party_id
        --AND hcp.contact_point_type = 'EMAIL'
        AND hcar.cust_account_id = <customer_id>
        and hcar.cust_acct_site_id = (select cust_acct_site_id from
        hz_cust_site_uses_all where site_use_id = <site_use_id>);

Customer Contact Phone/Fax/Email
Customer contact can be defined at party or party site level.

Contact details from Party level. Modify PHONE_LINE_TYPE to FAX or EMAIL to get related data.
       SELECT hcp.raw_phone_number
          FROM hz_contact_points hcp, hz_relationships hr, hz_parties hzp
         WHERE hcp.owner_table_id = hr.party_id
           AND hr.object_id = nvl(&party_id,hr.object_id)
           AND hr.status = 'A'
           AND hcp.status = 'A'
           AND hcp.primary_flag = 'Y'
           AND hzp.party_id = hr.party_id
           AND hcp.phone_line_type = 'GEN'
           AND hcp.owner_table_name = 'HZ_PARTIES'

Contact details from Party Site level.
         SELECT hcp.raw_phone_number
           FROM hz_contact_points hcp
          WHERE hcp.owner_table_id = nvl(&party_site_id,hcp.owner_table_id)
            AND phone_line_type = 'GEN'
            AND primary_flag = 'Y'
            AND owner_table_name = 'HZ_PARTY_SITES';

Customer Profile
Customer
   SELECT description
     FROM ar_customer_profiles_v a, ra_terms b
    WHERE customer_id = nvl(&customer_id,customer_id)
      AND status = 'A'
      AND site_use_id IS NULL
      AND a.standard_terms = b.term_id;

Customer Site
         SELECT description
           FROM ar_customer_profiles_v a, ra_terms b
          WHERE customer_id = nvl(&customer_id,customer_id)
            AND status = 'A'
            AND site_use_id = nvl(&bill_site_use_id,site_use_id)
            AND a.standard_terms = b.term_id;

Customer Credit Limits
Customer
   SELECT nvl(overall_credit_limit,0)
                 ,nvl(trx_credit_limit,0)
     FROM hz_cust_profile_amts
    WHERE cust_account_id = nvl(&customer_id,customer_id)
      --AND status = 'A'
      AND currency_code = &CURRENCY_CODE -- For Multi Org
      AND site_use_id IS NULL;

Customer Site
         SELECT nvl(overall_credit_limit,0)
               ,nvl(trx_credit_limit,0)
           FROM hz_cust_profile_amts
          WHERE cust_account_id = nvl(&customer_id,customer_id)
            --AND status = 'A'
            AND site_use_id =  nvl(&bill_site_use_id,site_use_id);