NOTE: This thread is for those who know Oracle EBS (developers or programmers)
I have been trying to get the contact information that is stored in HZ_PARTIES BUT for some reason it shows NULL emails and thats not correct (my query works just fine if i put HZ_PARTY_SITES instead of HZ_PARTIES, but it is not showing the information that i want)
Everytime that a customers is created, this PROCEDURE is executed:
Based on the information shown above, i made this query:
SELECT hp.party_name , hca.account_number , hcp.phone_number , hcp.email_address FROM apps.hz_cust_accounts hca INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id INNER JOIN apps.hz_party_site hps ON hcas.party_site_id = hps.party_site_id INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id LEFT JOIN ( SELECT owner_table_id , max(case when contact_point_type = 'PHONE' then phone_number end) phone_number , max(case when contact_point_type = 'EMAIL' then email_address end) email_address FROM hz_contact_points WHERE status = 'A' AND primary_flag = 'Y' AND owner_table_name = 'HZ_PARTIES' AND contact_point_type IN ('EMAIL','PHONE') GROUP BY owner_table_id ) hcp ON hcas.party_site_id = hcp.owner_table_id WHERE hcas.status = 'A' AND hps.status = 'A' AND hca.status = 'A' --AND hca.account_number = 'account_number' ; Take a look at this image (i cannot upload images yet):
Could you please help me to solve this?
EDIT: I am connecting to an Oracle Database (11g)
72 Answers
This is all about working with the Trading Community Architecture
See how the hz_relationships table is used to bring this information in. The below query works for an EBS R12.2 environment (12.1 database) where the contacts are created at the account level (EBS R12 allows both at the account level and the site level,Customer Form, Contacts/Communication: Troubleshooting, How To, Known Issues and Patches (Doc ID 1456319.1).
SELECT hca.account_number cust_account_number, obj.party_name customer_name, sub.party_name contact_name, hps.party_site_number, hcp.contact_point_type, hcp.phone_number, hcp.email_address FROM apps.hz_cust_accounts hca JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id JOIN apps.hz_parties obj ON hca.party_id = obj.party_id AND obj.party_type = 'ORGANIZATION' JOIN apps.hz_relationships rel ON hca.party_id = rel.object_id AND rel.relationship_type = 'CONTACT' AND rel.directional_flag = 'F' JOIN apps.hz_parties sub ON rel.subject_id = sub.party_id AND sub.party_type = 'PERSON' JOIN apps.hz_contact_points hcp ON rel.party_id = hcp.owner_table_id AND hcp.owner_table_name = 'HZ_PARTIES' JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id JOIN apps.hz_locations hl ON hps.location_id = hl.location_id WHERE hcas.status = 'A' AND hps.status = 'A' AND hca.status = 'A' AND hcp.contact_point_type IN ( 'PHONE', 'EMAIL' ) ORDER BY 2, 3 ; This query looks at contacts which are set-up at the site level (customer contacts):
SELECT hca.account_number cust_account_number, obj.party_name customer_name, sub.party_name contact_name, hps.party_site_number, hcp.contact_point_type, hcp.phone_number, hcp.email_address FROM apps.hz_cust_accounts hca JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id JOIN apps.hz_locations hl ON hps.location_id = hl.location_id JOIN apps.hz_parties obj ON hps.party_id = obj.party_id AND obj.party_type = 'ORGANIZATION' JOIN apps.hz_relationships rel ON rel.object_id = obj.party_id AND rel.relationship_type = 'CONTACT' AND rel.directional_flag = 'F' JOIN apps.hz_parties sub ON rel.subject_id = sub.party_id AND sub.party_type = 'PERSON' JOIN apps.hz_contact_points hcp ON rel.party_id = hcp.owner_table_id AND hcp.owner_table_name = 'HZ_PARTIES' WHERE hcas.status = 'A' AND hps.status = 'A' AND hca.status = 'A' AND hcp.contact_point_type IN ( 'PHONE', 'EMAIL' ) ORDER BY 2, Boiling down the query a little further, one could just focus on the contact information without focussing on the type of contact:
SELECT obj.party_name, sub.party_name contact_name, hcp.contact_point_type, hcp.phone_number, hcp.email_address FROM apps.hz_parties obj JOIN apps.hz_relationships rel ON rel.object_id = obj.party_id AND rel.relationship_type = 'CONTACT' AND rel.directional_flag = 'F' JOIN apps.hz_parties sub ON rel.subject_id = sub.party_id JOIN apps.hz_contact_points hcp ON rel.party_id = hcp.owner_table_id AND hcp.owner_table_name = 'HZ_PARTIES' WHERE 1 = 1 AND hcp.contact_point_type IN ( 'PHONE', 'EMAIL' ) ORDER BY 2, 3; The list of contact points in Party and Party Site level with type as Email and Phone number.
SELECT DISTINCT hp.party_id, hp.party_name, hca.cust_account_id, hca.account_number, party_site_id, party_site_number, hcp.phone_number, NVL (hcp.email_address, hcp.url) email_or_url, hcp.contact_point_type communication_type, hcp.status active, hcp.contact_point_purpose purpose FROM apps.hz_contact_points hcp, apps.hz_party_sites hps, apps.hz_cust_accounts hca, apps.hz_parties hp WHERE hps.party_id = hca.party_id AND hp.party_id = hca.party_id AND hcp.contact_point_type IN ('PHONE','EMAIL') AND hcp.owner_table_name = 'HZ_PARTIES' AND hcp.owner_table_id = hps.party_id AND hcp.status = 'A' AND hps.status = 'A' AND hca.status = 'A' AND hp.status = 'A' UNION SELECT DISTINCT hp.party_id, hp.party_name, hca.cust_account_id, hca.account_number, party_site_id, party_site_number, hcp.phone_number, NVL (hcp.email_address, hcp.url) email_or_url, hcp.contact_point_type communication_type, hcp.status active, hcp.contact_point_purpose purpose FROM apps.hz_contact_points hcp, apps.hz_party_sites hps, apps.hz_cust_accounts hca, apps.hz_parties hp WHERE hps.party_id = hca.party_id AND hp.party_id = hca.party_id AND hcp.contact_point_type IN ('PHONE','EMAIL') AND hcp.owner_table_name = 'HZ_PARTY_SITES' AND hcp.owner_table_id = hps.party_site_id AND hcp.status = 'A' AND hps.status = 'A' AND hp.status = 'A' AND hca.status = 'A'; 
