[Home] [Help]
36: l_ptype VARCHAR2(30);
37: BEGIN
38: x_return_status := 'S';
39: select owner_table_id into p_party_id
40: from hz_contact_points
41: where owner_table_name='HZ_PARTIES'
42: and contact_point_type='EMAIL'
43: and status='A'
44: and upper(email_address)=upper(p_email)
42: and contact_point_type='EMAIL'
43: and status='A'
44: and upper(email_address)=upper(p_email)
45: and contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
46: where contact_level_table='HZ_CONTACT_POINTS' and status='A');
47: EXCEPTION
48: WHEN NO_DATA_FOUND THEN
49: p_party_id:=-1;
50: when too_many_rows then
81: l_counter1 number:=1;
82: l_counter2 number:=1;
83: cursor c1 is
84: select a.owner_table_id,b.party_type,a.email_address
85: from hz_contact_points a,hz_parties b
86: where a.owner_table_name='HZ_PARTIES'
87: and upper(a.email_address)=upper(p_email)
88: and a.contact_point_type='EMAIL'
89: and a.status='A'
88: and a.contact_point_type='EMAIL'
89: and a.status='A'
90: and b.party_id=a.owner_table_id
91: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
92: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
93: order by 2,1 DESC;
94: BEGIN
95: -- Standard call to check for call compatibility.
96: IF NOT FND_API.Compatible_API_Call (l_api_version_number,