Introduction:
This blog has the SQL query that can be used to pull the data Organization contact details.
Cause of the issue:
Business wants to reach the organization contact details like person,Email,Fax, phone number, contact person and address etc., we can use this query.
How do we solve:
Create a report in BI Publisher using below SQL query to extract the details.
SELECT hp.party_name customer_name, hp.party_number customer_number,
hca.account_number, hca.cust_account_id, hp.party_id,
hps.party_site_id, hcsu.site_use_code, hps.location_id,
hl.address1, hl.address2, hl.address3, hl.city, hl.state, hl.country,
hl.postal_code, hcsu.site_use_id, hcsa.bill_to_flag,
(select hcsu.LOCATION from hz_cust_site_uses_all a where
hcsu.cust_acct_site_id= a.cust_acct_site_id
AND site_use_code = ‘SHIP_TO’) ship_location,
(select hcsu.LOCATION from hz_cust_site_uses_all a where
hcsu.cust_acct_site_id= a.cust_acct_site_id
AND site_use_code = ‘BILL_TO’) bill_location,(select sub.party_name
from apps.hz_cust_accounts hca1
, apps.hz_parties obj
, apps.hz_relationships rel
, apps.hz_contact_points hcp
, apps.hz_parties sub
where hca1.party_id = rel.object_id
and hca1.party_id = obj.party_id
and rel.subject_id = sub.party_id
and rel.relationship_type = ‘CONTACT’
and rel.directional_flag = ‘F’
and rel.party_id = hcp.owner_table_id
and hcp.owner_table_name = ‘HZ_PARTIES’
and hca1.party_id = hca.party_id
and sub.party_name is not null
and rownum = 1
) contact_Person
FROM hz_parties hp,
hz_party_sites hps,
hz_locations hl,
hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
and hcsa.BILL_TO_FLAG=’P’
and hcsa.status=’A’
and hcsu.status=’A’
and hp.status=’A’
and hca.status=’A’ ;
