Query to get Customer Information in R12
April 21, 2011 2 Comments
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;
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′;
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’