[Home] [Help]
PACKAGE BODY: APPS.CAC_SYNC_CONTACT_DQM_PVT
Source
1 PACKAGE BODY CAC_SYNC_CONTACT_DQM_PVT as
2 /* $Header: cacvscqb.pls 120.3 2006/01/13 17:09:16 twan noship $ */
3
4 /* -- Private methods -- */
5
6 -- This is used to get the translated user profile option name
7 FUNCTION GET_USER_PROFILE_NAME(p_profile_option_name IN VARCHAR2)
8 RETURN VARCHAR2
9 IS
10 CURSOR c_profile IS
11 SELECT user_profile_option_name
12 FROM fnd_profile_options_vl
13 WHERE profile_option_name = p_profile_option_name;
14
15 l_user_profile_option_name fnd_profile_options_tl.user_profile_option_name%TYPE;
16 BEGIN
17 OPEN c_profile;
18 FETCH c_profile INTO l_user_profile_option_name;
19 CLOSE c_profile;
20
21 RETURN l_user_profile_option_name;
22 END GET_USER_PROFILE_NAME;
23
24 -- This is used to get an organization party id after DQM search
25 FUNCTION GET_ORGANIZATION_PARTY_ID(p_search_ctx_id IN NUMBER)
26 RETURN NUMBER
27 IS
28 CURSOR c_party IS
29 SELECT party_id
30 FROM hz_matched_parties_gt
31 WHERE search_context_id = p_search_ctx_id;
32
33 l_org_party_id NUMBER;
34 BEGIN
35 OPEN c_party;
36 FETCH c_party INTO l_org_party_id;
37 CLOSE c_party;
38
39 RETURN l_org_party_id;
40 END GET_ORGANIZATION_PARTY_ID;
41
42 FUNCTION filter_ph_num(p_inval IN VARCHAR2)
43 RETURN VARCHAR2 IS
44 BEGIN
45 IF p_inval IS NULL THEN
46 RETURN NULL;
47 END IF;
48 RETURN translate(
49 p_inval,
50 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ',
51 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ');
52 END;
53
54 -- This is used to get a record for contact_search_rec_type
55 FUNCTION GET_CONTACT_SEARCH_REC
56 ( p_contact_name IN VARCHAR2
57 , p_person_name IN VARCHAR2
58 , p_person_first_name IN VARCHAR2
59 , p_person_last_name IN VARCHAR2
60 , p_person_middle_name IN VARCHAR2
61 , p_person_name_suffix IN VARCHAR2
62 , p_person_title IN VARCHAR2
63 , p_job_title IN VARCHAR2
64 )
65 RETURN hz_party_search.contact_search_rec_type
66 IS
67 l_contact_search_rec hz_party_search.contact_search_rec_type;
68 BEGIN
69 l_contact_search_rec.contact_name := p_contact_name;
70 l_contact_search_rec.person_name := p_person_name;
71 l_contact_search_rec.person_first_name := p_person_first_name;
72 l_contact_search_rec.person_last_name := p_person_last_name;
73 l_contact_search_rec.person_middle_name := p_person_middle_name;
74 l_contact_search_rec.person_name_suffix := p_person_name_suffix;
75 l_contact_search_rec.person_title := p_person_title;
76 l_contact_search_rec.job_title := p_job_title;
77
78 RETURN l_contact_search_rec;
79 END GET_CONTACT_SEARCH_REC;
80
81 -- This is used to get a record for contact_point_search_rec_type for PHONE
82 PROCEDURE GET_CONTACT_POINT_SEARCH_REC
83 ( p_contact_point_type IN VARCHAR2
84 , p_phone_line_type IN VARCHAR2
85 , p_phone_country_code IN VARCHAR2
86 , p_phone_area_code IN VARCHAR2
87 , p_phone_number IN VARCHAR2
88 , p_contact_point_purpose IN VARCHAR2
89 , p_status IN VARCHAR2
90 , x_contact_point_list IN OUT NOCOPY hz_party_search.contact_point_list
91 )
92 IS
93 l_index NUMBER;
94 BEGIN
95 IF p_phone_number IS NOT NULL THEN
96 l_index := NVL(x_contact_point_list.LAST,0) + 1;
97 x_contact_point_list(l_index).contact_point_type := p_contact_point_type;
98 x_contact_point_list(l_index).phone_line_type := p_phone_line_type;
99 x_contact_point_list(l_index).phone_country_code := p_phone_country_code;
100 x_contact_point_list(l_index).phone_area_code := p_phone_area_code;
101 x_contact_point_list(l_index).phone_number := p_phone_number;
102 x_contact_point_list(l_index).contact_point_purpose := p_contact_point_purpose;
103 x_contact_point_list(l_index).status := p_status;
104 x_contact_point_list(l_index).flex_format_phone_number := filter_ph_num(p_phone_country_code || p_phone_area_code || p_phone_number);
105 x_contact_point_list(l_index).raw_phone_number := filter_ph_num(p_phone_country_code || p_phone_area_code || p_phone_number);
106 END IF;
107 END GET_CONTACT_POINT_SEARCH_REC;
108
109 -- This is used to get a record for contact_point_search_rec_type for EMAIL
110 PROCEDURE GET_CONTACT_POINT_SEARCH_REC(
111 p_contact_point_type IN VARCHAR2
112 ,p_email_format IN VARCHAR2
113 ,p_email_address IN VARCHAR2
114 ,p_status IN VARCHAR2
115 ,x_contact_point_list IN OUT NOCOPY hz_party_search.contact_point_list
116 )
117 IS
118 l_index NUMBER;
119 BEGIN
120 IF p_email_address IS NOT NULL THEN
121 l_index := NVL(x_contact_point_list.LAST,0) + 1;
122 x_contact_point_list(l_index).contact_point_type := p_contact_point_type;
123 x_contact_point_list(l_index).email_format := p_email_format;
124 x_contact_point_list(l_index).email_address := p_email_address;
125 x_contact_point_list(l_index).status := p_status;
126 END IF;
127 END GET_CONTACT_POINT_SEARCH_REC;
128
129 PROCEDURE GET_CONTACT_POINT_LIST
130 ( p_work_phone_country_code IN VARCHAR2
131 , p_work_phone_area_code IN VARCHAR2
132 , p_work_phone_number IN VARCHAR2
133 , p_home_phone_country_code IN VARCHAR2
134 , p_home_phone_area_code IN VARCHAR2
135 , p_home_phone_number IN VARCHAR2
136 , p_fax_phone_country_code IN VARCHAR2
137 , p_fax_phone_area_code IN VARCHAR2
138 , p_fax_phone_number IN VARCHAR2
139 , p_pager_phone_country_code IN VARCHAR2
140 , p_pager_phone_area_code IN VARCHAR2
141 , p_pager_phone_number IN VARCHAR2
142 , p_cell_phone_country_code IN VARCHAR2
143 , p_cell_phone_area_code IN VARCHAR2
144 , p_cell_phone_number IN VARCHAR2
145 , p_text_email_address IN VARCHAR2
146 , p_html_email_address IN VARCHAR2
147 , x_contact_point_list IN OUT NOCOPY hz_party_search.contact_point_list
148 )
149 IS
150 BEGIN
151 get_contact_point_search_rec
152 (p_contact_point_type => 'PHONE'
153 ,p_phone_line_type => 'GEN'
154 ,p_phone_country_code => p_work_phone_country_code
155 ,p_phone_area_code => p_work_phone_area_code
156 ,p_phone_number => p_work_phone_number
157 ,p_contact_point_purpose => 'BUSINESS'
158 ,p_status => 'A'
159 ,x_contact_point_list => x_contact_point_list
160 );
161
162 get_contact_point_search_rec
163 (p_contact_point_type => 'PHONE'
164 ,p_phone_line_type => 'GEN'
165 ,p_phone_country_code => p_home_phone_country_code
166 ,p_phone_area_code => p_home_phone_area_code
167 ,p_phone_number => p_home_phone_number
168 ,p_contact_point_purpose => 'PERSONAL'
169 ,p_status => 'A'
170 ,x_contact_point_list => x_contact_point_list
171 );
172
173 get_contact_point_search_rec
174 (p_contact_point_type => 'PHONE'
175 ,p_phone_line_type => 'FAX'
176 ,p_phone_country_code => p_fax_phone_country_code
177 ,p_phone_area_code => p_fax_phone_area_code
178 ,p_phone_number => p_fax_phone_number
179 ,p_contact_point_purpose => NULL
180 ,p_status => 'A'
181 ,x_contact_point_list => x_contact_point_list
182 );
183
184 get_contact_point_search_rec
185 (p_contact_point_type => 'PHONE'
186 ,p_phone_line_type => 'MOBILE'
187 ,p_phone_country_code => p_cell_phone_country_code
188 ,p_phone_area_code => p_cell_phone_area_code
189 ,p_phone_number => p_cell_phone_number
190 ,p_contact_point_purpose => NULL
191 ,p_status => 'A'
192 ,x_contact_point_list => x_contact_point_list
193 );
194
195 get_contact_point_search_rec
196 (p_contact_point_type => 'PHONE'
197 ,p_phone_line_type => 'PAGER'
198 ,p_phone_country_code => p_pager_phone_country_code
199 ,p_phone_area_code => p_pager_phone_area_code
200 ,p_phone_number => p_pager_phone_number
201 ,p_contact_point_purpose => NULL
202 ,p_status => 'A'
203 ,x_contact_point_list => x_contact_point_list
204 );
205
206 get_contact_point_search_rec
207 (p_contact_point_type => 'EMAIL'
208 ,p_email_format => 'MAILTEXT'
209 ,p_email_address => p_text_email_address
210 ,p_status => 'A'
211 ,x_contact_point_list => x_contact_point_list
212 );
213
214 get_contact_point_search_rec
215 (p_contact_point_type => 'EMAIL'
216 ,p_email_format => 'MAILHTML'
217 ,p_email_address => p_html_email_address
218 ,p_status => 'A'
219 ,x_contact_point_list => x_contact_point_list
220 );
221
222 END GET_CONTACT_POINT_LIST;
223
224 FUNCTION GET_PARTY_ID (p_search_context_id IN NUMBER)
225 RETURN NUMBER
226 IS
227 CURSOR c_ctx (b_search_context_id NUMBER) IS
228 SELECT REL.PARTY_ID
229 FROM HZ_RELATIONSHIPS REL
230 , HZ_ORG_CONTACTS ORCT
231 , HZ_MATCHED_CONTACTS_GT GT
232 WHERE REL.RELATIONSHIP_ID = ORCT.PARTY_RELATIONSHIP_ID
233 AND REL.DIRECTIONAL_FLAG = 'F'
234 AND ORCT.ORG_CONTACT_ID = GT.ORG_CONTACT_ID
235 AND GT.SEARCH_CONTEXT_ID = b_search_context_id;
236
237 l_party_id NUMBER;
238 BEGIN
239 OPEN c_ctx(p_search_context_id);
240 FETCH c_ctx INTO l_party_id;
241 CLOSE c_ctx;
242
243 RETURN l_party_id;
244 END GET_PARTY_ID;
245
246 /* -- Public methods -- */
247
248 PROCEDURE FIND_ORGANIZATION
249 ( p_init_msg_list IN VARCHAR2
250 , p_organization_name IN VARCHAR2
251 , p_contact_name IN VARCHAR2
252 , x_organization_id OUT NOCOPY NUMBER
253 , x_return_status OUT NOCOPY VARCHAR2
254 , x_msg_count OUT NOCOPY NUMBER
255 , x_msg_data OUT NOCOPY VARCHAR2
256 )
257 IS
258 l_party_search_rec hz_party_search.party_search_rec_type;
259 l_party_site_list hz_party_search.party_site_list;
260 l_contact_list hz_party_search.contact_list;
261 l_contact_point_list hz_party_search.contact_point_list;
262 l_search_ctx_id NUMBER;
263 l_num_matches NUMBER;
264 l_rule_id NUMBER;
265 BEGIN
266 IF p_init_msg_list IS NULL OR
267 fnd_api.to_boolean (p_init_msg_list)
268 THEN
269 fnd_msg_pub.initialize;
270 END IF;
271
272 l_rule_id := fnd_profile.value('HZ_ORG_DUP_PREV_MATCHRULE');
273
274 IF l_rule_id IS NULL THEN
275 fnd_message.set_name ('JTF', 'CAC_SYNC_CONTACT_MATCH_RULE_NF');
276 fnd_message.set_token ('P_PROFILE', get_user_profile_name('HZ_ORG_DUP_PREV_MATCHRULE'));
277 fnd_msg_pub.add;
278 RAISE fnd_api.g_exc_unexpected_error;
279 END IF;
280
281 l_party_search_rec.organization_name := p_organization_name;
282 l_party_search_rec.party_all_names := p_organization_name;
283 l_party_search_rec.party_name := p_organization_name;
284 l_party_search_rec.party_type := 'ORGANIZATION';
285 l_party_search_rec.status := 'A';
286
287 hz_party_search.find_parties(
288 p_init_msg_list => fnd_api.g_false,
289 x_rule_id => l_rule_id,
290 p_party_search_rec => l_party_search_rec,
291 p_party_site_list => l_party_site_list,
292 p_contact_list => l_contact_list,
293 p_contact_point_list => l_contact_point_list,
294 p_restrict_sql => NULL,
295 p_search_merged => 'N',
296 x_search_ctx_id => l_search_ctx_id,
297 x_num_matches => l_num_matches,
298 x_return_status => x_return_status,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data
301 );
302
303 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
304 THEN
305 x_return_status := fnd_api.g_ret_sts_unexp_error;
306 RAISE fnd_api.g_exc_unexpected_error;
307 ELSE
308 IF l_num_matches = 1 THEN
309 x_organization_id := get_organization_party_id(l_search_ctx_id);
310 ELSE
311 IF l_num_matches = 0 THEN
312 -- Error message: <Contact Name>: Organization does not exist, contact not synchronized.
313 fnd_message.set_name ('JTF', 'CAC_SYNC_DQM_ORG_NOTFOUND');
314 fnd_message.set_token ('P_CONTACT_NAME', p_contact_name);
315 ELSE
316 -- Error message: <Contact Name>: Multiple matches for the organization were found, contact not synchronized.
317 fnd_message.set_name ('JTF', 'CAC_SYNC_DQM_ORG_TOOMANY');
318 fnd_message.set_token ('P_CONTACT_NAME', p_contact_name);
319 END IF;
320 fnd_msg_pub.add;
321 RAISE fnd_api.g_exc_unexpected_error;
322 END IF;
323 END IF;
324
325 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
326
327 EXCEPTION
328 WHEN fnd_api.g_exc_unexpected_error THEN
332 WHEN OTHERS THEN
329 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
330 x_return_status := fnd_api.g_ret_sts_unexp_error;
331
333 fnd_message.set_name ('JTF', 'CAC_SYNC_CONTACT_UNEXPECTED_ER');
334 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
335 fnd_msg_pub.add;
336 x_return_status := fnd_api.g_ret_sts_unexp_error;
337 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
338 END FIND_ORGANIZATION;
339
340 PROCEDURE CHECK_CONTACT
341 ( p_init_msg_list IN VARCHAR2
342 , p_org_party_id IN NUMBER
343 , p_organization_name IN VARCHAR2
344 , p_person_full_name IN VARCHAR2
345 , p_person_first_name IN VARCHAR2
346 , p_person_last_name IN VARCHAR2
347 , p_person_middle_name IN VARCHAR2
348 , p_person_name_suffix IN VARCHAR2
349 , p_person_title IN VARCHAR2
350 , p_job_title IN VARCHAR2
351 , p_work_phone_country_code IN VARCHAR2
352 , p_work_phone_area_code IN VARCHAR2
353 , p_work_phone_number IN VARCHAR2
354 , p_home_phone_country_code IN VARCHAR2
355 , p_home_phone_area_code IN VARCHAR2
356 , p_home_phone_number IN VARCHAR2
357 , p_fax_phone_country_code IN VARCHAR2
358 , p_fax_phone_area_code IN VARCHAR2
359 , p_fax_phone_number IN VARCHAR2
360 , p_pager_phone_country_code IN VARCHAR2
361 , p_pager_phone_area_code IN VARCHAR2
362 , p_pager_phone_number IN VARCHAR2
363 , p_cell_phone_country_code IN VARCHAR2
364 , p_cell_phone_area_code IN VARCHAR2
365 , p_cell_phone_number IN VARCHAR2
366 , p_text_email_address IN VARCHAR2
367 , p_html_email_address IN VARCHAR2
368 , p_match_type IN VARCHAR2
369 , x_return_status OUT NOCOPY VARCHAR2
370 , x_msg_count OUT NOCOPY NUMBER
371 , x_msg_data OUT NOCOPY VARCHAR2
372 , x_num_of_matches OUT NOCOPY NUMBER
373 , x_party_id OUT NOCOPY NUMBER
374 )
375 IS
376 l_contact_list hz_party_search.contact_list;
377 l_contact_point_list hz_party_search.contact_point_list;
378 l_search_ctx_id NUMBER;
379 l_rule_id NUMBER;
380 l_num_matches NUMBER;
381 l_index NUMBER;
382 BEGIN
383 IF p_init_msg_list IS NULL OR
384 fnd_api.to_boolean (p_init_msg_list)
385 THEN
386 fnd_msg_pub.initialize;
387 END IF;
388
389 x_return_status := fnd_api.g_ret_sts_success;
390
391 l_rule_id := fnd_profile.value('HZ_CON_DUP_PREV_MATCHRULE');
392 IF l_rule_id IS NULL THEN
393 fnd_message.set_name ('JTF', 'CAC_SYNC_CONTACT_MATCH_RULE_NF');
394 fnd_message.set_token ('P_PROFILE', get_user_profile_name('HZ_CON_DUP_PREV_MATCHRULE'));
395 fnd_msg_pub.add;
396 RAISE fnd_api.g_exc_unexpected_error;
397 END IF;
398
399 -- Prepare contact list
400 l_contact_list(1) := get_contact_search_rec
401 (p_contact_name => p_person_full_name
402 ,p_person_name => p_person_full_name
403 ,p_person_first_name => p_person_first_name
404 ,p_person_last_name => p_person_last_name
405 ,p_person_middle_name => p_person_middle_name
406 ,p_person_name_suffix => p_person_name_suffix
407 ,p_person_title => p_person_title
408 ,p_job_title => p_job_title
409 );
410
411 -- Prepare contact point list
412 get_contact_point_list
413 ( p_work_phone_country_code => p_work_phone_country_code
414 , p_work_phone_area_code => p_work_phone_area_code
415 , p_work_phone_number => p_work_phone_number
416 , p_home_phone_country_code => p_home_phone_country_code
417 , p_home_phone_area_code => p_home_phone_area_code
418 , p_home_phone_number => p_home_phone_number
419 , p_fax_phone_country_code => p_fax_phone_country_code
420 , p_fax_phone_area_code => p_fax_phone_area_code
421 , p_fax_phone_number => p_fax_phone_number
422 , p_pager_phone_country_code => p_pager_phone_country_code
423 , p_pager_phone_area_code => p_pager_phone_area_code
424 , p_pager_phone_number => p_pager_phone_number
425 , p_cell_phone_country_code => p_cell_phone_country_code
426 , p_cell_phone_area_code => p_cell_phone_area_code
427 , p_cell_phone_number => p_cell_phone_number
428 , p_text_email_address => p_text_email_address
429 , p_html_email_address => p_html_email_address
430 , x_contact_point_list => l_contact_point_list
431 );
432
433 -- Perform DQM check for contacts
434 hz_party_search.get_matching_contacts(
435 p_init_msg_list => fnd_api.g_false,
436 p_rule_id => l_rule_id,
437 p_party_id => p_org_party_id,
438 p_contact_list => l_contact_list,
439 p_contact_point_list => l_contact_point_list,
440 p_restrict_sql => null,
441 p_match_type => p_match_type,
442 x_search_ctx_id => l_search_ctx_id,
443 x_num_matches => l_num_matches,
444 x_return_status => x_return_status,
445 x_msg_count => x_msg_count,
449 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
446 x_msg_data => x_msg_data
447 );
448
450 THEN
451 x_return_status := fnd_api.g_ret_sts_unexp_error;
452 RAISE fnd_api.g_exc_unexpected_error;
453 ELSE
454 IF l_num_matches > 1 THEN
455 -- Error message: <Contact Name>: Multiple matches for the contact were found, contact not synchronized.
456 fnd_message.set_name ('JTF', 'CAC_SYNC_DQM_CONTACT_EXISTS');
457 fnd_message.set_token ('P_CONTACT_NAME', p_person_full_name);
458 fnd_msg_pub.add;
459 RAISE fnd_api.g_exc_unexpected_error;
460 ELSE
461 x_num_of_matches := l_num_matches;
462 x_party_id := get_party_id(l_search_ctx_id);
463 END IF;
464 END IF;
465
466 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
467
468 EXCEPTION
469 WHEN fnd_api.g_exc_unexpected_error THEN
470 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
471 x_return_status := fnd_api.g_ret_sts_unexp_error;
472
473 WHEN OTHERS THEN
474 fnd_message.set_name ('JTF', 'CAC_SYNC_CONTACT_UNEXPECTED_ER');
475 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
476 fnd_msg_pub.add;
477 x_return_status := fnd_api.g_ret_sts_unexp_error;
478 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
479 END CHECK_CONTACT;
480
481 END CAC_SYNC_CONTACT_DQM_PVT;