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

 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';

 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';

 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';