Query to get Customer Information in R12


Query to get Customer Information in R12

This query is tested in R12.1.3 instance. The query may take few minutes to execute depending upon the size of the data that are present in various HZ Tables.

select  distinct
        hp.party_name "Customer Name",
        hca.account_number,
        hca.status,
        hcsu.location,
        hcsu.site_use_code,
        hcsu.status loc_stat,
        ps.class,
        hcsu.site_use_id,
        hcpc.name profile_name,
        hl.address1,
        hl.address2,
        hl.address3,
        hl.city,
        hl.state,
        hl.postal_code,
        ps.customer_id,
        ps.customer_site_use_id,
        hps.identifying_address_flag,
        ps.trx_date,
        HOU.NAME "Operating Unit"
from    apps.hz_parties hp,
        apps.hz_party_sites hps,
        apps.hz_locations hl,
        apps.hz_cust_accounts hca,
        apps.hz_cust_acct_sites hcas,
        apps.hz_cust_site_uses hcsu,
        apps.hz_customer_profiles hcp,
        apps.hz_cust_profile_classes hcpc,
        apps.ar_payment_schedules_all ps,
        apps.hr_operating_units hou
where   hp.party_id = hca.party_id(+)
        and hp.party_id = hcp.party_id
        and hp.party_id = hps.party_id
        and hps.party_site_id = hcas.party_site_id
        and hps.location_id = hl.location_id
        and hca.cust_account_id = hcas.cust_account_id
        and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
        and hca.cust_account_id = hcp.cust_account_id
        and hca.cust_account_id = ps.customer_id
        and hcp.profile_class_id = hcpc.profile_class_id
        and ps.customer_site_use_id = hcsu.site_use_id
        and hcsu.org_id = hou.organization_id;

2 Responses to Query to get Customer Information in R12

  1. kishan says:

    please tell me the error in this code
    ship to location and bill to location are not getting displayed in this code

    SELECT hca.account_number customer_number,
    hp.party_name customer_name,
    hps.party_site_number site_number, hl.address1 address1,
    hl.address2 address2, hl.address3 address3,
    hl.address4 address4, hl.city city,
    hl.postal_code postal_code, hl.state state,
    ftt.territory_short_name country,
    hcsua1.LOCATION bill_to_location,
    hcsua2.LOCATION ship_to_location
    FROM hz_parties hp,
    hz_party_sites hps,
    hz_cust_accounts hca,
    hz_cust_acct_sites_all hcasa1,
    hz_cust_site_uses_all hcsua1,
    hz_locations hl,
    fnd_territories_tl ftt,
    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 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 hcsua1.site_use_code(+) = ‘bill_to’
    AND hcsua2.site_use_code(+) = ‘ship_to’
    AND hcasa1.org_id(+) = fnd_profile.VALUE (‘org_id’)
    AND hcasa2.org_id(+) = fnd_profile.VALUE (‘org_id’)
    AND hps.location_id = hl.location_id
    AND hl.country = ftt.territory_code
    AND ftt.LANGUAGE = USERENV (‘lang’)
    and hca.account_number =’2000′;

  2. qurram says:

    Try now

    SELECT hca.account_number customer_number,
    hp.party_name customer_name,
    hps.party_site_number site_number, hl.address1 address1,
    hl.address2 address2, hl.address3 address3,
    hl.address4 address4, hl.city city,
    hl.postal_code postal_code, hl.state state,
    ftt.territory_short_name country,
    hcsua1.LOCATION bill_to_location,
    hcsua2.LOCATION ship_to_location
    FROM hz_parties hp,
    hz_party_sites hps,
    hz_cust_accounts hca,
    hz_cust_acct_sites_all hcasa1,
    hz_cust_site_uses_all hcsua1,
    hz_locations hl,
    fnd_territories_tl ftt,
    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 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 hcsua1.site_use_code(+) = ‘BILL_TO’
    AND hcsua2.site_use_code(+) = ‘SHIP_TO’
    AND hcasa1.org_id(+) = fnd_profile.value (‘org_id’)
    AND hcasa2.org_id(+) = FND_PROFILE.VALUE (‘org_id’)
    AND hps.location_id = hl.location_id
    AND hl.country = ftt.territory_code
    AND ftt.LANGUAGE = USERENV (‘lang’)
    and hca.account_number = ‘2000’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: