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
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
| --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' ; --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' ; |
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' ; |
Hope it help!
No comments:
Post a Comment