Query Supplier’s phone, fax, and email in oracle R12

In Oracle EBS R12 supplier web page, there are Address Book and Contact Directory that contain phone, fax and email information.

A) To gather phone, fax, and email in Address Book we can use this query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
--email
SELECT HCP.EMAIL_ADDRESS
FROM HZ_PARTY_SITES HPS,
HZ_CONTACT_POINTS HCP,
AP_SUPPLIERS ASS
WHERE 1=1
AND ASS.VENDOR_NAME=:VENDOR_NAME
AND HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
AND HPS.PARTY_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID
AND HCP.CONTACT_POINT_TYPE='EMAIL';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--phone
SELECT ROWNUM NO, HCP.PHONE_AREA_CODE||' '||HCP.PHONE_NUMBER PHONE
FROM HZ_CONTACT_POINTS HCP,
HZ_PARTY_SITES HPS,
AP_SUPPLIERS ASS
WHERE 1=1
AND ASS.VENDOR_NAME=:VENDOR_NAME
AND HPS.PARTY_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID
AND HCP.PRIMARY_FLAG='Y'
AND HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
AND HCP.STATUS='A'
AND HCP.CONTACT_POINT_TYPE='PHONE'
AND HCP.PHONE_LINE_TYPE='GEN';

--fax
SELECT ROWNUM NO, HCP.PHONE_AREA_CODE||' '||HCP.PHONE_NUMBER PHONE
FROM HZ_CONTACT_POINTS HCP,
HZ_PARTY_SITES HPS,
AP_SUPPLIERS ASS
WHERE 1=1
AND ASS.VENDOR_NAME=:VENDOR_NAME
AND HPS.PARTY_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
AND HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID
AND HCP.STATUS='A'
AND HCP.CONTACT_POINT_TYPE='PHONE'
AND HCP.PHONE_LINE_TYPE='FAX';

B) To gather phone, fax, and email in Contact Directory we can use this query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
--email
SELECT HCP.EMAIL_ADDRESS
FROM HZ_RELATIONSHIPS HR
,HZ_CONTACT_POINTS HCP
,AP_SUPPLIERS ASS
WHERE 1=1
AND ASS.VENDOR_NAME=:VENDOR_NAME
AND HR.SUBJECT_TYPE='ORGANIZATION'
AND HR.RELATIONSHIP_CODE='CONTACT'
AND HR.STATUS='A'
AND HCP.OWNER_TABLE_NAME='HZ_PARTIES'
AND HR.SUBJECT_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HR.PARTY_ID
AND HCP.PRIMARY_FLAG='Y'
AND HCP.STATUS='A'
AND HCP.CONTACT_POINT_TYPE='EMAIL';

--phone
SELECT HCP.PHONE_AREA_CODE||HCP.PHONE_NUMBER
FROM HZ_RELATIONSHIPS HR
,HZ_CONTACT_POINTS HCP
,AP_SUPPLIERS ASS
WHERE 1=1
AND ASS.VENDOR_NAME=:VENDOR_NAME
AND HR.SUBJECT_TYPE='ORGANIZATION'
AND HR.RELATIONSHIP_CODE='CONTACT'
AND HR.STATUS='A'
AND HCP.OWNER_TABLE_NAME='HZ_PARTIES'
AND HR.SUBJECT_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HR.PARTY_ID
AND HCP.PRIMARY_FLAG='Y'
AND HCP.STATUS='A'
AND HCP.CONTACT_POINT_TYPE='PHONE'
AND HCP.PHONE_LINE_TYPE='GEN';

--fax
select hcp.phone_area_code||hcp.phone_number
from hz_relationships hr
,hz_contact_points hcp
,AP_SUPPLIERS ASS
where 1=1
AND ASS.VENDOR_NAME=:VENDOR_NAME
and hr.SUBJECT_TYPE='ORGANIZATION'
and hr.RELATIONSHIP_CODE='CONTACT'
and hr.STATUS='A'
AND hcp.owner_table_name='HZ_PARTIES'
and hr.SUBJECT_ID=ASS.PARTY_ID
and hcp.owner_table_id=hr.party_ID
and hcp.STATUS='A'
and hcp.CONTACT_POINT_TYPE='PHONE'
and hcp.PHONE_LINE_TYPE='FAX';

C) Following query fetches details on Supplier site contacts:-

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT DISTINCT asu.party_id, asu.segment1 Supp_Num
,asu.vendor_name
,hpc.party_name Contact_Name
,hpr.primary_phone_country_code cnt_cntry
,hpr.primary_phone_area_code cnt_area
,hpr.primary_phone_number cnt_phone
,assa.vendor_site_code
,assa.vendor_site_id
,asco.vendor_contact_id
FROM
hz_relationships hr
,ap_suppliers asu
,ap_supplier_sites_all assa
,ap_supplier_contacts asco
,hz_org_contacts hoc
,hz_parties hpc
,hz_parties hpr
,hz_contact_points hpcp
WHERE hoc.party_relationship_id = hr.relationship_id
AND hr.subject_id = asu.party_id
AND hr.relationship_code = 'CONTACT'
AND hr.object_table_name = 'HZ_PARTIES'
AND asu.vendor_id = assa.vendor_id
AND hr.object_id = hpc.party_id
AND hr.party_id = hpr.party_id
AND asco.relationship_id = hoc.party_relationship_id
AND assa.party_site_id = asco.org_party_site_id
AND hpr.party_type='PARTY_RELATIONSHIP'
AND hpr.party_id = hpcp.owner_table_id
AND hpcp.owner_table_name = 'HZ_PARTIES';

Thanks Yogesh

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