[Home] [Help]
PACKAGE BODY: APPS.POS_SUPP_CONTACT_PKG
Source
1 PACKAGE BODY POS_SUPP_CONTACT_PKG AS
2 /*$Header: POSCONTB.pls 120.31.12020000.2 2013/03/25 07:27:12 ppotnuru ship $ */
3
4 g_module CONSTANT VARCHAR2(30) := 'POS_SUPP_CONTACT_PKG';
5 /*Added one argument p_department for BUG 7938942 */
6 PROCEDURE create_org_contact_private
7 (p_vendor_party_id IN NUMBER,
8 p_person_party_id IN NUMBER,
9 p_job_title IN VARCHAR2,
10 p_inactive_date IN DATE DEFAULT NULL,
11 x_return_status OUT nocopy VARCHAR2,
12 x_msg_count OUT nocopy NUMBER,
13 x_msg_data OUT nocopy VARCHAR2,
14 x_rel_party_id OUT nocopy NUMBER,
15 p_department IN VARCHAR2 DEFAULT NULL
16 )
17 IS
18 l_rel_rec hz_relationship_v2pub.relationship_rec_type;
19 l_rel_party_rec hz_party_v2pub.party_rec_type;
20 l_rel_party_number hz_parties.party_number%TYPE;
21 l_rel_id NUMBER;
22 l_rel_party_id NUMBER;
23 l_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
24 l_org_contact_id NUMBER;
25 l_method VARCHAR2(30);
26 l_step VARCHAR2(100);
27 BEGIN
28 l_method := 'create_org_contact_private';
29
30 l_rel_rec := NULL;
31 l_rel_party_rec := NULL;
32
33 l_rel_rec.object_id := p_vendor_party_id;
34 l_rel_rec.object_type := 'ORGANIZATION';
35 l_rel_rec.object_table_name := 'HZ_PARTIES';
36 l_rel_rec.subject_id := p_person_party_id;
37 l_rel_rec.subject_type := 'PERSON';
38 l_rel_rec.subject_table_name := 'HZ_PARTIES';
39 l_rel_rec.relationship_code := 'CONTACT_OF';
40 l_rel_rec.relationship_type := 'CONTACT';
41 l_rel_rec.start_date := Sysdate;
42 l_rel_rec.end_date := p_inactive_date;
43 l_rel_rec.created_by_module := 'POS_SUPPLIER_MGMT';
44 l_rel_rec.application_id := 177;
45 l_rel_rec.party_rec := l_rel_party_rec;
46 l_rel_rec.status := 'A';
47
48 l_org_contact_rec.created_by_module := 'POS_SUPPLIER_MGMT';
49 l_org_contact_rec.application_id := 177;
50 l_org_contact_rec.job_title := p_job_title;
51 l_org_contact_rec.department := p_department;
52 l_org_contact_rec.party_rel_rec := l_rel_rec;
53
54 l_step := 'call hz_party_contact_v2pub.create_org_contact';
55 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
56 fnd_log.string
57 ( fnd_log.level_statement
58 , g_module || '.' || l_method
59 , l_step
60 || ' with subject_id ' || l_rel_rec.subject_id
61 || ' object_id ' || l_rel_rec.object_id
62 || ' job_title ' || l_org_contact_rec.job_title
63 );
64 END IF;
65
66 hz_party_contact_v2pub.create_org_contact
67 (p_init_msg_list => fnd_api.g_false,
68 p_org_contact_rec => l_org_contact_rec,
69 x_return_status => x_return_status,
70 x_msg_count => x_msg_count,
71 x_msg_data => x_msg_data,
72 x_org_contact_id => l_org_contact_id,
73 x_party_rel_id => l_rel_id,
74 x_party_id => l_rel_party_id,
75 x_party_number => l_rel_party_number
76 );
77
78 pos_log.log_call_result
79 (p_module => 'POSCONTB',
80 p_prefix => 'call hz_party_v2pub.create_person',
81 p_return_status => x_return_status,
82 p_msg_count => x_msg_count,
83 p_msg_data => x_msg_data
84 );
85
86 IF x_return_status = fnd_api.g_ret_sts_success THEN
87 x_rel_party_id := l_rel_party_id;
88 END IF;
89
90 END create_org_contact_private;
91 /*Added one argument p_department for BUG 7938942
92 Added four args for bug 9316284 - Alt Contact Name, Alt. Phone Area Code, Alt. Phone number, URL*/
93 PROCEDURE create_supplier_contact
94 (p_vendor_party_id IN NUMBER,
95 p_first_name IN VARCHAR2,
96 p_last_name IN VARCHAR2,
97 p_middle_name IN VARCHAR2 DEFAULT NULL,
98 p_contact_title IN VARCHAR2 DEFAULT NULL,
99 p_job_title IN VARCHAR2 DEFAULT NULL,
100 p_phone_area_code IN VARCHAR2 DEFAULT NULL,
101 p_phone_number IN VARCHAR2 DEFAULT NULL,
102 p_phone_extension IN VARCHAR2 DEFAULT NULL,
103 p_fax_area_code IN VARCHAR2 DEFAULT NULL,
104 p_fax_number IN VARCHAR2 DEFAULT NULL,
105 p_email_address IN VARCHAR2 DEFAULT NULL,
106 p_inactive_date IN DATE DEFAULT NULL,
107 x_return_status OUT nocopy VARCHAR2,
108 x_msg_count OUT nocopy NUMBER,
109 x_msg_data OUT nocopy VARCHAR2,
110 x_person_party_id OUT nocopy NUMBER,
111 p_department IN VARCHAR2 DEFAULT NULL,
112 p_alt_contact_name IN VARCHAR2 DEFAULT NULL,
113 p_alt_area_code IN VARCHAR2 DEFAULT NULL,
114 p_alt_phone_number IN VARCHAR2 DEFAULT NULL,
115 p_url IN VARCHAR2 DEFAULT NULL
116 )
117 IS
118 l_person_party_rec hz_party_v2pub.party_rec_type;
119 l_person_party_number hz_parties.party_number%TYPE;
120 l_person_rec hz_party_v2pub.person_rec_type;
121 l_person_profile_id NUMBER;
122 l_method VARCHAR2(30);
123 l_step VARCHAR2(100);
124 l_rel_party_id NUMBER;
125
126 /* Added for bug 7366321 */
127 l_hzprofile_value varchar2(20);
128 l_hzprofile_changed varchar2(1) := 'N';
129 /* End */
130
131
132 BEGIN
133 SAVEPOINT create_supplier_contact_sp;
134
135 l_method := 'create_supplier_contact';
136
137 /* Added for bug 7366321 */
138 l_hzprofile_value := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
139 if nvl(l_hzprofile_value, 'Y') = 'N' then
140 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'Y');
141 l_hzprofile_changed := 'Y';
142 end if;
143 /* End */
144
145 /* commented for bug 7366321
146 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER','Y');
147 */
148
149 -- create contact party
150 l_person_party_rec := NULL;
151 l_person_rec := NULL;
152
153 l_person_rec.person_first_name := p_first_name;
154 l_person_rec.person_middle_name := p_middle_name;
155 l_person_rec.person_last_name := p_last_name;
156 l_person_rec.person_title := p_job_title;
157 l_person_rec.created_by_module := 'POS_SUPPLIER_MGMT';
158 l_person_rec.application_id := 177;
159 l_person_rec.party_rec := l_person_party_rec;
160 l_person_rec.person_pre_name_adjunct := p_contact_title;
161 l_person_rec.known_as := p_alt_contact_name;
162
163 l_step := 'create contact party';
164
165 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
166 fnd_log.string
167 ( fnd_log.level_statement
168 , g_module || '.' || l_method
169 , l_step
170 || ' with person_first_name ' || l_person_rec.person_first_name
171 || ' person_middle_name ' || l_person_rec.person_middle_name
172 || ' person_last_name ' || l_person_rec.person_last_name
173 || ' person_title ' || l_person_rec.person_title
174 || ' person_pre_name_adjunct ' || l_person_rec.person_pre_name_adjunct
175 );
176 END IF;
177
178 hz_party_v2pub.create_person
179 (p_init_msg_list => fnd_api.g_false,
180 p_person_rec => l_person_rec,
181 p_party_usage_code => 'SUPPLIER_CONTACT',
182 x_return_status => x_return_status,
183 x_msg_count => x_msg_count,
184 x_msg_data => x_msg_data,
185 x_party_id => x_person_party_id,
186 x_party_number => l_person_party_number,
187 x_profile_id => l_person_profile_id
188 );
189
190 /* Added for bug 7366321 */
191 if nvl(l_hzprofile_changed,'N') = 'Y' then
192 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
193 l_hzprofile_changed := 'N';
194 end if;
195 /* End */
196
197 pos_log.log_call_result
198 (p_module => 'POSCONTB',
199 p_prefix => 'call hz_party_v2pub.create_person',
200 p_return_status => x_return_status,
201 p_msg_count => x_msg_count,
202 p_msg_data => x_msg_data
203 );
204
205 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
206 /* Added for bug 7366321 */
207 if nvl(l_hzprofile_changed,'N') = 'Y' then
208 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
209 l_hzprofile_changed := 'N';
210 end if;
211 /* End */
212
213 ROLLBACK TO create_supplier_contact_sp;
214 RETURN;
215 END IF;
216 /*Passing one more argument department */
217 create_org_contact_private
218 (p_vendor_party_id => p_vendor_party_id,
219 p_person_party_id => x_person_party_id,
220 p_job_title => p_job_title,
221 p_inactive_date => p_inactive_date,
222 x_return_status => x_return_status,
223 x_msg_count => x_msg_count,
224 x_msg_data => x_msg_data,
225 x_rel_party_id => l_rel_party_id,
226 p_department => p_department
227 );
228
229 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
230 ROLLBACK TO create_supplier_contact_sp;
231 RETURN;
232 END IF;
233
234 l_step := 'store phone for supplier contact';
235 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
236 fnd_log.string
237 (fnd_log.level_statement
238 , g_module || '.' || l_method
239 , l_step
240 );
241 END IF;
242
243 pos_hz_contact_point_pkg.update_party_phone
244 (
245 p_party_id => l_rel_party_id,
246 p_country_code => NULL,
247 p_area_code => p_phone_area_code ,
248 p_number => p_phone_number,
249 p_extension => p_phone_extension,
250 x_return_status => x_return_status,
251 x_msg_count => x_msg_count,
252 x_msg_data => x_msg_data
253 );
254
255 IF x_return_status IS NULL OR
256 x_return_status <> fnd_api.g_ret_sts_success THEN
257 ROLLBACK TO create_supplier_contact_sp;
258 RETURN;
259 END IF;
260
261 l_step := 'store fax for supplier contact';
262 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
263 fnd_log.string
264 (fnd_log.level_statement
265 , g_module || '.' || l_method
266 , l_step
267 );
268 END IF;
269
270 pos_hz_contact_point_pkg.update_party_fax
271 (
272 p_party_id => l_rel_party_id,
273 p_country_code => NULL,
274 p_area_code => p_fax_area_code ,
275 p_number => p_fax_number,
276 p_extension => NULL,
277 x_return_status => x_return_status,
278 x_msg_count => x_msg_count,
279 x_msg_data => x_msg_data
280 );
281
282 IF x_return_status IS NULL OR
283 x_return_status <> fnd_api.g_ret_sts_success THEN
284 ROLLBACK TO create_supplier_contact_sp;
285 RETURN;
286 END IF;
287
288 l_step := 'store email for supplier contact';
289 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
290 fnd_log.string
291 (fnd_log.level_statement
292 , g_module || '.' || l_method
293 , l_step
294 );
295 END IF;
296
297 pos_hz_contact_point_pkg.update_party_email
298 (
299 p_party_id => l_rel_party_id,
300 p_email => p_email_address,
301 x_return_status => x_return_status,
302 x_msg_count => x_msg_count,
303 x_msg_data => x_msg_data
304 );
305
306 IF x_return_status IS NULL OR
307 x_return_status <> fnd_api.g_ret_sts_success THEN
308 ROLLBACK TO create_supplier_contact_sp;
309 RETURN;
310 END IF;
311
312 l_step := 'store alt phone for supplier contact';
313 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
314 fnd_log.string
315 (fnd_log.level_statement
316 , g_module || '.' || l_method
317 , l_step
318 );
319 END IF;
320
321 pos_hz_contact_point_pkg.update_party_alt_phone
322 (
323 p_party_id => l_rel_party_id,
324 p_country_code => NULL,
325 p_area_code => p_alt_area_code,
326 p_number => p_alt_phone_number,
327 p_extension => NULL,
328 x_return_status => x_return_status,
329 x_msg_count => x_msg_count,
330 x_msg_data => x_msg_data
331 );
332
333 IF x_return_status IS NULL OR
334 x_return_status <> fnd_api.g_ret_sts_success THEN
335 ROLLBACK TO create_supplier_contact_sp;
336 RETURN;
337 END IF;
338
339 l_step := 'store URL for supplier contact';
340 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
341 fnd_log.string
342 (fnd_log.level_statement
343 , g_module || '.' || l_method
344 , l_step
345 );
346 END IF;
347
348 pos_hz_contact_point_pkg.update_party_url
349 (
350 p_party_id => l_rel_party_id,
351 p_url => p_url,
352 x_return_status => x_return_status,
353 x_msg_count => x_msg_count,
354 x_msg_data => x_msg_data
355 );
356
357 IF x_return_status IS NULL OR
358 x_return_status <> fnd_api.g_ret_sts_success THEN
359 ROLLBACK TO create_supplier_contact_sp;
360 RETURN;
361 END IF;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 /* Added for bug 7366321 */
366 if nvl(l_hzprofile_changed,'N') = 'Y' then
367 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
368 l_hzprofile_changed := 'N';
369 end if;
370 /* End */
371 ROLLBACK TO create_supplier_contact_sp;
372 x_return_status := fnd_api.g_ret_sts_unexp_error;
373 x_msg_count := 1;
374 x_msg_data := Sqlerrm;
375 pos_log.log_sqlerrm('POSCONTB', 'in create_supplier_contact');
376
377 END create_supplier_contact;
378
379 /**
380 Name : update_supplier_contact
381 Description : This procedure is used to update the contact details
382 like name,email,phone number,fax etc.
383 Parameters :
384
385 IN :
386 p_contact_party_id - holds party id of the 'PERSON' party record in hz_parties.
387 p_vendor_party_id - holds party id of the 'ORGANIZATION' party record in hz_parties.
388 p_first_name - first name of the contact
389 p_last_name - last name of the contact
390 p_middle_name - middle name of the contact
391 p_contact_title - contact title of the contact
392 p_job_title - job title of the contact
393 p_phone_number - phone number of the contact
394 p_fax_number - fax number of the contact
395 p_email_address - email address of the contact
396 p_inactive_date - inactive date of the contact
397 p_party_object_version_number - object version number of the relationship record in hz_parties
398 p_email_object_version_number - object version number of the email contact in hz_contact_points
399 p_phone_object_version_number - object version number of the phone contact in hz_contact_points
400 p_fax_object_version_number - object version number of the fax contact in hz_contact_points
401 p_rel_object_version_number - object version number of the relationship record in hz_relationships
402 p_cont_object_version_number - object version number of the hz_org_contacts record
403 p_person_party_obversion_num - object version number of the 'PERSON' party record in hz_parties
404
405 OUT :
406 x_return_status - returns either success/failure
407 x_msg_count - returns the number of error messages
408 x_msg_data - returns error messages
409 **/
410 /*Added one argument p_department for BUG 7938942 */
411 PROCEDURE update_supplier_contact
412 (p_contact_party_id IN NUMBER,
413 p_vendor_party_id IN NUMBER,
414 p_first_name IN VARCHAR2 DEFAULT NULL,
415 p_last_name IN VARCHAR2 DEFAULT NULL,
416 p_middle_name IN VARCHAR2 DEFAULT NULL,
417 p_contact_title IN VARCHAR2 DEFAULT NULL,
418 p_job_title IN VARCHAR2 DEFAULT NULL,
419 p_phone_area_code IN VARCHAR2 DEFAULT NULL,
420 p_phone_number IN VARCHAR2 DEFAULT NULL,
421 p_phone_extension IN VARCHAR2 DEFAULT NULL,
422 p_fax_area_code IN VARCHAR2 DEFAULT NULL,
423 p_fax_number IN VARCHAR2 DEFAULT NULL,
424 p_email_address IN VARCHAR2 DEFAULT NULL,
425 p_inactive_date IN DATE DEFAULT NULL,
426 --Start Bug 6620664 - Handling Concurrent Updates on ContactDirectory, BusinessClassifications ans Accounting pages
427 p_party_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
428 p_email_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
429 p_phone_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
430 p_fax_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
431 p_rel_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
432 p_cont_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
433 --End Bug 6620664 - Handling Concurrent Updates on ContactDirectory, BusinessClassifications ans Accounting pages
434 p_person_party_obversion_num IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
435 x_return_status OUT nocopy VARCHAR2,
436 x_msg_count OUT nocopy NUMBER,
437 x_msg_data OUT nocopy VARCHAR2,
438 p_department IN VARCHAR2 DEFAULT NULL,
439 p_alt_contact_name IN VARCHAR2 DEFAULT NULL,
440 p_alt_area_code IN VARCHAR2 DEFAULT NULL,
441 p_alt_phone_number IN VARCHAR2 DEFAULT NULL,
442 p_url IN VARCHAR2 DEFAULT NULL,
443 p_url_object_version_number IN NUMBER DEFAULT fnd_api.G_NULL_NUM,
444 p_altphone_obj_version_num IN NUMBER DEFAULT fnd_api.G_NULL_NUM
445 )
446 IS
447 CURSOR l_contact_party_cur IS
448 SELECT person_first_name,
449 person_last_name,
450 person_middle_name,
451 person_pre_name_adjunct,
452 person_title,
453 object_version_number,
454 known_as
455 FROM hz_parties
456 WHERE party_id = p_contact_party_id;
457
458 l_contact_party_rec l_contact_party_cur%ROWTYPE;
459 l_person_rec hz_party_v2pub.person_rec_type;
460 l_party_rec hz_party_v2pub.party_rec_type;
461 l_profile_id NUMBER;
462 /*For BUG 7938942 as part of l_cur2 adding department field also*/
463 CURSOR l_cur2 IS
464 SELECT hoc.org_contact_id,
465 hoc.job_title,
466 hoc.department,
467 hoc.object_version_number cont_object_version_number,
468 hzr.object_version_number rel_object_version_number,
469 hzr.party_id,
470 hzr.relationship_id,
471 hp.object_version_number rel_party_obj_ver_num
472 FROM hz_org_contacts hoc, hz_relationships hzr, hz_parties hp
473 WHERE hoc.party_relationship_id = hzr.relationship_id
474 AND hzr.object_table_name = 'HZ_PARTIES'
475 AND hzr.object_id = p_vendor_party_id
476 AND hzr.subject_table_name = 'HZ_PARTIES'
477 AND hzr.subject_id = p_contact_party_id
478 AND hzr.relationship_type = 'CONTACT'
479 AND hzr.relationship_code = 'CONTACT_OF'
480 AND hzr.party_id = hp.party_id;
481
482 l_rec2 l_cur2%ROWTYPE;
483
484 l_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
485 l_rel_rec hz_relationship_v2pub.relationship_rec_type;
486 l_rel_party_id NUMBER;
487 l_found_org_contact BOOLEAN;
488 l_enddate_changed BOOLEAN;
489
490 /* Start for Bug 6620664 */
491 l_party_object_version_number NUMBER;
492 l_phone_object_version_number NUMBER;
493 l_fax_object_version_number NUMBER;
494 l_email_object_version_number NUMBER;
495 l_rel_object_version_number NUMBER;
496 l_cont_object_version_number NUMBER;
497 l_url_object_version_number NUMBER;
498 l_altphone_obj_version_num NUMBER;
499 /* End for Bug 6620664 */
500
501 /* Bug 7027825 Start */
502 l_person_party_obversion_num NUMBER;
503 /* Bug 7027825 End */
504
505
506 cursor l_user_cur is
507 select user_name
508 from fnd_user
509 WHERE person_party_id = p_contact_party_id;
510 l_user_rec l_user_cur%ROWTYPE;
511
512 BEGIN
513 SAVEPOINT update_supplier_contact_sp;
514
515 /* Start for Bug 6620664 */
516 l_phone_object_version_number := p_phone_object_version_number;
517 l_fax_object_version_number := p_fax_object_version_number;
518 l_email_object_version_number := p_email_object_version_number;
519 l_url_object_version_number := p_url_object_version_number;
520 l_altphone_obj_version_num := p_altphone_obj_version_num;
521 /* End for Bug 6620664 */
522
523 OPEN l_contact_party_cur;
524
525 FETCH l_contact_party_cur INTO l_contact_party_rec;
526
527 IF l_contact_party_cur%notfound THEN
528 CLOSE l_contact_party_cur;
529 x_return_status := fnd_api.g_ret_sts_error;
530 x_msg_count := 1;
531 x_msg_data := 'invalid contact party_id ' || p_contact_party_id;
532 RETURN;
533 END IF;
534 /* Bug 7027825 Start */
535 IF(p_person_party_obversion_num=fnd_api.G_NULL_NUM) THEN
536 l_person_party_obversion_num := l_contact_party_rec.object_version_number;
537 ELSE
538 l_person_party_obversion_num := p_person_party_obversion_num;
539 END IF ;
540 /* Bug 7027825 End */
541 CLOSE l_contact_party_cur;
542
543 OPEN l_cur2;
544 FETCH l_cur2 INTO l_rec2;
545 l_found_org_contact := l_cur2%found;
546 CLOSE l_cur2;
547
548 IF l_found_org_contact THEN
549 -- retrieve the relation record
550 HZ_RELATIONSHIP_V2PUB.get_relationship_rec (
551 p_relationship_id => l_rec2.relationship_id,
552 p_directional_flag => 'F',
553 x_rel_rec => l_rel_rec,
554 x_return_status => x_return_status,
555 x_msg_count => x_msg_count,
556 x_msg_data => x_msg_data
557 );
558 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
559 ROLLBACK TO update_supplier_contact_sp;
560 RETURN;
561 END IF;
562
563 if ((l_rel_rec.end_date is null and p_inactive_date is null) or
564 (l_rel_rec.end_date is not null and l_rel_rec.end_date = p_inactive_date)) then
565 l_enddate_changed := false;
566 else
567 l_enddate_changed := true;
568 l_rel_rec.end_date := p_inactive_date;
569 end if;
570
571 /* Start Bug 6620664 */
572 IF(p_party_object_version_number = fnd_api.G_NULL_NUM) THEN
573 l_party_object_version_number := l_rec2.rel_party_obj_ver_num;
574 ELSE
575 l_party_object_version_number := p_party_object_version_number;
576 END IF;
577
578 IF(p_rel_object_version_number = fnd_api.G_NULL_NUM) THEN
579 l_rel_object_version_number := l_rec2.rel_object_version_number;
580 ELSE
581 l_rel_object_version_number := p_rel_object_version_number;
582 END IF;
583
584 IF(p_cont_object_version_number = fnd_api.G_NULL_NUM) THEN
585 l_cont_object_version_number := l_rec2.cont_object_version_number;
586 ELSE
587 l_cont_object_version_number := p_cont_object_version_number;
588 END IF;
589 /* End Bug 6620664 */
590
591 -- update org contact if needed
592 /*BUG 7938942: Added code to update department field along with job title if need to update*/
593 IF ((l_rec2.job_title IS NULL AND p_job_title IS NULL) OR
594 (l_rec2.job_title IS NOT NULL AND l_rec2.job_title = p_job_title))
595 AND ((l_rec2.department IS NULL AND p_department IS NULL) OR
596 (l_rec2.department IS NOT NULL AND l_rec2.department = p_department))
597
598 AND (not l_enddate_changed)
599
600 THEN
601 NULL;
602 ELSE
603 IF ((l_rec2.job_title IS NULL AND p_job_title IS NULL) OR
604 (l_rec2.job_title IS NOT NULL AND l_rec2.job_title = p_job_title))
605 THEN
606 NULL;
607 ELSE
608 l_org_contact_rec.job_title := p_job_title;
609 /* Bug 9850943 Start */
610 IF p_job_title IS NULL THEN
611 l_org_contact_rec.job_title:=FND_API.G_MISS_CHAR;
612 END IF;
613 /* Bug 9850943 End */
614 END IF;
615 IF ((l_rec2.department IS NULL AND p_department IS NULL) OR
616 (l_rec2.department IS NOT NULL AND l_rec2.department = p_department))
617 THEN
618 NULL;
619 ELSE
620 l_org_contact_rec.department := p_department;
621 /* Bug 9850943 Start */
622 IF p_department IS NULL THEN
623 l_org_contact_rec.department:=FND_API.G_MISS_CHAR;
624 END IF;
625 /* Bug 9850943 End */
626 END IF;
627 l_org_contact_rec.party_rel_rec := l_rel_rec;
628 l_org_contact_rec.org_contact_id := l_rec2.org_contact_id;
629
630 hz_party_contact_v2pub.update_org_contact
631 (p_init_msg_list => fnd_api.g_true,
632 p_org_contact_rec => l_org_contact_rec,
633 --Start Bug 6620664
634 p_cont_object_version_number => l_cont_object_version_number, --l_rec2.cont_object_version_number,
635 p_rel_object_version_number => l_rel_object_version_number, --l_rec2.rel_object_version_number,
636 p_party_object_version_number => l_party_object_version_number, --l_rec2.rel_party_obj_ver_num,
637 --End Bug 6620664
638 x_return_status => x_return_status,
639 x_msg_count => x_msg_count,
640 x_msg_data => x_msg_data
641 );
642 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
643 ROLLBACK TO update_supplier_contact_sp;
644 RETURN;
645 END IF;
646 END IF;
647 ELSE
648 -- create org contact record as we did not find it
649 create_org_contact_private
650 (p_vendor_party_id => p_vendor_party_id,
651 p_person_party_id => p_contact_party_id,
652 p_job_title => p_job_title,
653 p_inactive_date => p_inactive_date,
654 x_return_status => x_return_status,
655 x_msg_count => x_msg_count,
656 x_msg_data => x_msg_data,
657 x_rel_party_id => l_rel_party_id,
658 p_department => p_department
659 );
660
661 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
662 --ROLLBACK TO create_supplier_contact_sp;
663 ROLLBACK TO update_supplier_contact_sp;
664 RETURN;
665 END IF;
666 END IF;
667
668 -- update party if needed
669 IF (p_first_name IS NULL AND l_contact_party_rec.person_first_name IS NULL OR
670 p_first_name IS NOT NULL AND l_contact_party_rec.person_first_name IS NOT NULL AND
671 p_first_name = l_contact_party_rec.person_first_name
672 ) AND
673 (p_last_name IS NULL AND l_contact_party_rec.person_last_name IS NULL OR
674 p_last_name IS NOT NULL AND l_contact_party_rec.person_last_name IS NOT NULL AND
675 p_last_name = l_contact_party_rec.person_last_name
676 ) AND
677 (p_middle_name IS NULL AND l_contact_party_rec.person_middle_name IS NULL OR
678 p_middle_name IS NOT NULL AND l_contact_party_rec.person_middle_name IS NOT NULL AND
679 p_middle_name = l_contact_party_rec.person_middle_name
680 ) AND
681 (p_contact_title IS NULL AND l_contact_party_rec.person_pre_name_adjunct IS NULL OR
682 p_contact_title IS NOT NULL AND l_contact_party_rec.person_pre_name_adjunct IS NOT NULL AND
683 p_contact_title = l_contact_party_rec.person_pre_name_adjunct
684 ) AND
685 (p_alt_contact_name IS NULL AND l_contact_party_rec.known_as IS NULL OR
686 p_alt_contact_name IS NOT NULL AND l_contact_party_rec.known_as IS NOT NULL AND
687 p_alt_contact_name = l_contact_party_rec.known_as
688 )AND
689 (p_job_title IS NULL AND l_contact_party_rec.person_title IS NULL OR
690 p_job_title IS NOT NULL AND l_contact_party_rec.person_title IS NOT NULL AND
691 p_job_title = l_contact_party_rec.person_title
692 ) THEN
693
694 NULL; -- no change for party
695
696 ELSE
697 l_person_rec.person_first_name := nvl(p_first_name, fnd_api.g_miss_char);
698 l_person_rec.person_last_name := p_last_name;
699 l_person_rec.person_middle_name := nvl(p_middle_name, fnd_api.g_miss_char);
700 l_person_rec.person_pre_name_adjunct := nvl(p_contact_title, fnd_api.g_miss_char);
701 l_party_rec.party_id := p_contact_party_id;
702 l_person_rec.party_rec := l_party_rec;
703 l_person_rec.known_as := nvl(p_alt_contact_name, fnd_api.g_miss_char);
704 l_person_rec.person_title := p_job_title;
705
706 /* Bug 7027825 , For Updating A Person Party Type We need to pass the object version number of the
707 PERSON record ,So changed the below object version number parameter to l_person_party_obversion_num */
708
709 hz_party_v2pub.update_person
710 (p_init_msg_list => fnd_api.g_true,
711 p_person_rec => l_person_rec,
712 p_party_object_version_number => l_person_party_obversion_num,
713 x_profile_id => l_profile_id,
714 x_return_status => x_return_status,
715 x_msg_count => x_msg_count,
716 x_msg_data => x_msg_data
717 );
718
719 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
720 ROLLBACK TO update_supplier_contact_sp;
721 RETURN;
722 END IF;
723 END IF;
724
725 pos_hz_contact_point_pkg.update_party_phone
726 (
727 p_party_id => l_rec2.party_id,
728 p_country_code => NULL,
729 p_area_code => p_phone_area_code ,
730 p_number => p_phone_number,
731 p_extension => p_phone_extension,
732 --Start Bug 6620664
733 p_phone_object_version_number => l_phone_object_version_number,
734 --End Bug 6620664
735 x_return_status => x_return_status,
736 x_msg_count => x_msg_count,
737 x_msg_data => x_msg_data
738 );
739
740 IF x_return_status IS NULL OR
741 x_return_status <> fnd_api.g_ret_sts_success THEN
742 --ROLLBACK TO create_supplier_contact_sp;
743 ROLLBACK TO update_supplier_contact_sp;
744 RETURN;
745 END IF;
746
747 pos_hz_contact_point_pkg.update_party_fax
748 (
749 p_party_id => l_rec2.party_id,
750 p_country_code => NULL,
751 p_area_code => p_fax_area_code ,
752 p_number => p_fax_number,
753 p_extension => NULL,
754 --Start Bug 6620664
755 p_fax_object_version_number => l_fax_object_version_number,
756 --End Bug 6620664
757 x_return_status => x_return_status,
758 x_msg_count => x_msg_count,
759 x_msg_data => x_msg_data
760 );
761
762 IF x_return_status IS NULL OR
763 x_return_status <> fnd_api.g_ret_sts_success THEN
764 --ROLLBACK TO create_supplier_contact_sp;
765 ROLLBACK TO update_supplier_contact_sp;
766 RETURN;
767 END IF;
768
769 pos_hz_contact_point_pkg.update_party_email
770 (
771 p_party_id => l_rec2.party_id,
772 p_email => p_email_address,
773 --Start Bug 6620664
774 p_email_object_version_number => l_email_object_version_number,
775 --End Bug 6620664
776 x_return_status => x_return_status,
777 x_msg_count => x_msg_count,
778 x_msg_data => x_msg_data
779 );
780
781 pos_hz_contact_point_pkg.update_party_alt_phone
782 (
783 p_party_id => l_rec2.party_id,
784 p_country_code => NULL,
785 p_area_code => p_alt_area_code ,
786 p_number => p_alt_phone_number,
787 p_extension => NULL,
788 p_phone_object_version_number => l_altphone_obj_version_num,
789 x_return_status => x_return_status,
790 x_msg_count => x_msg_count,
791 x_msg_data => x_msg_data
792 );
793
794 IF x_return_status IS NULL OR
795 x_return_status <> fnd_api.g_ret_sts_success THEN
796 ROLLBACK TO update_supplier_contact_sp;
797 RETURN;
798 END IF;
799
800 pos_hz_contact_point_pkg.update_party_url
801 (
802 p_party_id => l_rec2.party_id,
803 p_url => p_url,
804 p_url_object_version_number => l_url_object_version_number,
805 x_return_status => x_return_status,
806 x_msg_count => x_msg_count,
807 x_msg_data => x_msg_data
808 );
809
810 IF x_return_status IS NULL OR
811 x_return_status <> fnd_api.g_ret_sts_success THEN
812 ROLLBACK TO update_supplier_contact_sp;
813 RETURN;
814 END IF;
815
816 for l_user_rec in l_user_cur loop
817
818 fnd_user_pkg.updateuser
819 (
820 x_user_name => l_user_rec.user_name,
821 x_email_address => p_email_address,
822 x_owner => NULL,
823 x_end_date => p_inactive_date
824 );
825
826 END LOOP;
827
828 /* Bug 9576302 Start
829 We need to update status flag of HZ_RELATIONSHIPS table for this person party to 'A' irrespective
830 of whether contact is active or not as in R12 we are making use of end_date to check whether contact
831 is active or not.
832
833 We need to update the status flag in HZ_ORG_CONTACTS also to 'A' for the relationship_id
834 */
835
836 UPDATE HZ_RELATIONSHIPS
837 SET
838 STATUS='A'
839 WHERE
840 RELATIONSHIP_ID=(SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS WHERE
841 SUBJECT_TABLE_NAME='HZ_PARTIES'
842 AND
843 SUBJECT_ID=p_contact_party_id
844 AND
845 SUBJECT_TYPE='PERSON'
846 AND
847 OBJECT_TABLE_NAME='HZ_PARTIES'
848 AND
849 OBJECT_ID=p_vendor_party_id
850 AND
851 OBJECT_TYPE='ORGANIZATION'
852 AND
853 RELATIONSHIP_TYPE='CONTACT'
854 AND
855 RELATIONSHIP_CODE='CONTACT_OF'
856 );
857
858 UPDATE HZ_RELATIONSHIPS
859 SET
860 STATUS='A'
861 WHERE
862 RELATIONSHIP_ID=(SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS WHERE
863 OBJECT_TABLE_NAME='HZ_PARTIES'
864 AND
865 OBJECT_ID=p_contact_party_id
866 AND
867 OBJECT_TYPE='PERSON'
868 AND
869 SUBJECT_TABLE_NAME='HZ_PARTIES'
870 AND
871 SUBJECT_ID=p_vendor_party_id
872 AND
873 SUBJECT_TYPE='ORGANIZATION'
874 AND
875 RELATIONSHIP_TYPE='CONTACT'
876 AND
877 RELATIONSHIP_CODE='CONTACT'
878 );
879
880 UPDATE HZ_ORG_CONTACTS
881 SET
882 STATUS='A'
883 WHERE
884 PARTY_RELATIONSHIP_ID=(SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS WHERE
885 SUBJECT_TABLE_NAME='HZ_PARTIES'
886 AND
887 SUBJECT_ID=p_contact_party_id
888 AND
889 SUBJECT_TYPE='PERSON'
890 AND
891 OBJECT_TABLE_NAME='HZ_PARTIES'
892 AND
893 OBJECT_ID=p_vendor_party_id
894 AND
895 OBJECT_TYPE='ORGANIZATION'
896 AND
897 RELATIONSHIP_TYPE='CONTACT'
898 AND
899 RELATIONSHIP_CODE='CONTACT_OF');
900
901 UPDATE HZ_ORG_CONTACTS
902 SET
903 STATUS='A'
904 WHERE
905 PARTY_RELATIONSHIP_ID=(SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS WHERE
906 OBJECT_TABLE_NAME='HZ_PARTIES'
907 AND
908 OBJECT_ID=p_contact_party_id
909 AND
910 OBJECT_TYPE='PERSON'
911 AND
912 SUBJECT_TABLE_NAME='HZ_PARTIES'
913 AND
914 SUBJECT_ID=p_vendor_party_id
915 AND
916 SUBJECT_TYPE='ORGANIZATION'
917 AND
918 RELATIONSHIP_TYPE='CONTACT'
919 AND
920 RELATIONSHIP_CODE='CONTACT'
921 );
922
923
924 /* Bug 9576302 End */
925 IF x_return_status IS NULL OR
926 x_return_status <> fnd_api.g_ret_sts_success THEN
927 --ROLLBACK TO create_supplier_contact_sp;
928 ROLLBACK TO update_supplier_contact_sp;
929 RETURN;
930 END IF;
931
932 EXCEPTION
933 WHEN OTHERS THEN
934 ROLLBACK TO update_supplier_contact_sp;
935 x_return_status := fnd_api.g_ret_sts_unexp_error;
936 x_msg_count := 1;
937 x_msg_data := Sqlerrm;
938 pos_log.log_sqlerrm('POSCONTB', 'in update_supplier_contact');
939
940 END update_supplier_contact;
941
942 END POS_SUPP_CONTACT_PKG ;