[Home] [Help]
PACKAGE BODY: APPS.POS_PROFILE_PKG
Source
1 PACKAGE BODY POS_PROFILE_PKG as
2 /*$Header: POSPRUTB.pls 120.9.12000000.2 2007/09/07 19:52:17 pkapoor ship $ */
3
4 g_log_module_name VARCHAR2(30) := 'pos.plsql.POSPRUTB';
5
6 /* This procedure gets the vendor information from the notification id.
7 *
8 */
9
10 PROCEDURE get_vendor_data (
11 p_ntf_id IN NUMBER
12 , x_vendor_id out nocopy NUMBER
13 , x_party_id out nocopy NUMBER
14 , x_vendor_name out nocopy VARCHAR2
15 , x_vendor_number out nocopy VARCHAR2
16 , x_status out nocopy VARCHAR2
17 , x_exception_msg out nocopy VARCHAR2
18 )
19 IS
20
21 l_vendor_id number;
22
23 cursor l_vendor_cur is
24 select vendor_id, vendor_name, party_id, segment1 from ap_suppliers
25 where vendor_id = l_vendor_id;
26 l_vendor_rec l_vendor_cur%ROWTYPE;
27
28 BEGIN
29
30 l_vendor_id := POS_URL_PKG.get_ntf_vendor_id (p_ntf_id);
31
32 for l_vendor_rec in l_vendor_cur loop
33
34 x_vendor_id := l_vendor_rec.vendor_id;
35 x_vendor_name :=l_vendor_rec.vendor_name;
36 x_vendor_number := l_vendor_rec.segment1;
37 x_party_id := l_vendor_rec.party_id;
38
39 end loop;
40
41 if x_vendor_id is null then
42 x_status := 'E';
43 x_vendor_id := -1;
44 return;
45 end if;
46
47 x_status := 'S';
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 x_vendor_id := -1;
52 x_party_id := -1;
53 X_STATUS :='E';
54 END get_vendor_data;
55
56 PROCEDURE buyer_boot_strap
57 ( p_user_id IN NUMBER
58 , x_status OUT nocopy VARCHAR2
59 , x_exception_msg OUT nocopy VARCHAR2
60 )
61 IS
62
63 l_employee_id FND_USER.EMPLOYEE_ID%TYPE;
64 l_user_party_id HZ_PARTIES.PARTY_ID%TYPE;
65 l_enterprise_id HZ_PARTIES.PARTY_ID%TYPE;
66 l_relationship_id HZ_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
67 l_step NUMBER;
68 l_username FND_USER.USER_NAME%TYPE;
69 l_email_address FND_USER.EMAIL_ADDRESS%TYPE;
70
71 BEGIN
72 x_status := 'E';
73 l_step := 0;
74
75 select employee_id, user_name,email_address into
76 l_employee_id, l_username, l_email_address
77 from fnd_user
78 where user_id = p_user_id;
79
80 l_step := 1;
81
82 if l_employee_id is not null then
83
84 l_step := 2;
85
86 l_enterprise_id := POS_PARTY_MANAGEMENT_PKG.check_for_enterprise_user(l_username);
87
88 l_step := 3;
89
90 if l_enterprise_id <> -1 then
91 l_step := 4;
92 x_status := 'S';
93 else
94 l_step := 5;
95 POS_ENTERPRISE_UTIL_PKG.pos_create_enterprise_user
96 (l_username
97 ,'First'
98 ,'Last'
99 ,l_email_address
100 ,l_user_party_id
101 ,l_relationship_id
102 ,x_exception_msg
103 ,x_status);
104 x_status := 'S';
105 l_step := 6;
106 end if;
107 else
108 l_step := 7;
109 x_status := 'E';
110 end if;
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 X_STATUS :='E';
115 x_exception_msg := 'Failure at step ' || l_step;
116 raise_application_error(-20029, x_exception_msg, true);
117
118 END buyer_boot_strap;
119
120 FUNCTION get_update_date_from_contact (
121 p_contact_id IN NUMBER
122 ) RETURN DATE
123 IS
124 l_date DATE;
125 BEGIN
126 SELECT last_update_date
127 INTO l_date
128 FROM hz_contact_points
129 WHERE contact_point_id = p_contact_id;
130 return l_date;
131 END get_update_date_from_contact;
132
133 FUNCTION phone_exist(
134 p_party_id IN NUMBER
135 , p_owner_table_name IN VARCHAR2
136 , p_contact_point_type IN VARCHAR2
137 , p_phone_line_type IN VARCHAR2
138 ) RETURN BOOLEAN
139 IS
140 l_count NUMBER;
141 BEGIN
142 SELECT count(contact_point_id)
143 INTO l_count
144 FROM hz_contact_points hcp
145 WHERE hcp.owner_table_name = owner_table_name
146 AND hcp.owner_table_id = p_party_id
147 AND hcp.contact_point_type = p_contact_point_type
148 AND hcp.phone_line_type = p_phone_line_type
149 AND hcp.status = 'A' ;
150 IF l_count = 1 then
151 return TRUE;
152 ELSE
153 return FALSE;
154 END IF;
155 END phone_exist;
156
157 FUNCTION web_exist(
158 p_party_id IN NUMBER
159 , p_owner_table_name IN VARCHAR2
160 , p_contact_point_type IN VARCHAR2
161 , p_web_type IN VARCHAR2
162 ) RETURN BOOLEAN
163 IS
164 l_count NUMBER;
165 BEGIN
166 IF (p_contact_point_type='EMAIL') then
167 SELECT count(contact_point_id)
168 INTO l_count
169 FROM hz_contact_points hcp
170 WHERE hcp.owner_table_name = p_owner_table_name
171 AND hcp.owner_table_id = p_party_id
172 AND hcp.contact_point_type = p_contact_point_type
173 AND hcp.primary_flag = 'Y'
174 AND hcp.status = 'A';
175 ELSE
176 SELECT count(contact_point_id)
177 INTO l_count
178 FROM hz_contact_points hcp
179 WHERE hcp.owner_table_name = p_owner_table_name
180 AND hcp.owner_table_id = p_party_id
181 AND hcp.contact_point_type = p_contact_point_type
182 AND hcp.web_type = p_web_type
183 AND hcp.primary_flag = 'Y'
184 AND hcp.status = 'A';
185 END IF;
186 IF l_count=1 then
187 return TRUE;
188 ELSE
189 return FALSE;
190 END IF;
191 END web_exist;
192
193 PROCEDURE update_address_note (
194 p_party_site_id IN NUMBER
195 , p_note IN VARCHAR2
196 , x_status out nocopy VARCHAR2
197 , x_exception_msg out nocopy VARCHAR2
198 )
199 IS
200 l_step NUMBER;
201 BEGIN
202
203 l_step := 0;
204
205 -- Update the address note with the provided note
206 POS_ADDRESS_NOTES_PKG.update_note(
207 p_party_site_id
208 ,p_note
209 ,x_status
210 ,x_exception_msg
211 );
212
213 l_step := 1;
214
215 EXCEPTION
216 WHEN OTHERS THEN
217 X_STATUS :='E';
218 x_exception_msg := 'Failure at step ' || l_step;
219 raise_application_error(-20017, x_exception_msg, true);
220 END update_address_note;
221
222 PROCEDURE assign_address_type (
223 p_party_site_id IN NUMBER
224 , p_address_type IN VARCHAR2
225 , x_party_site_use_id out nocopy NUMBER
226 , x_status out nocopy VARCHAR2
227 , x_exception_msg out nocopy VARCHAR2
228 )
229 IS
230
231 l_msg_count NUMBER;
232 l_step NUMBER;
233 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
234
235 BEGIN
236
237 l_party_site_use_rec.party_site_id := p_party_site_id;
238 l_party_site_use_rec.application_id := 177;
239 l_party_site_use_rec.created_by_module := 'POS_PROFILE_PKG';
240 l_party_site_use_rec.status := 'A';
241 l_party_site_use_rec.site_use_type := p_address_type;
242
243 hz_party_site_v2pub.create_party_site_use
244 ( p_init_msg_list => FND_API.G_FALSE,
245 p_party_site_use_rec => l_party_site_use_rec,
246 x_party_site_use_id => x_party_site_use_id,
247 x_return_status => x_status,
248 x_msg_count => l_msg_count,
249 x_msg_data => x_exception_msg
250
251 );
252
253 EXCEPTION
254 WHEN OTHERS THEN
255 X_STATUS :='E';
256 x_exception_msg := 'Failure at step ' || l_step;
257 raise_application_error(-20017, x_exception_msg, true);
258 END assign_address_type;
259
260
261 PROCEDURE update_address_type (
262 p_party_site_use_id IN NUMBER
263 , p_status IN VARCHAR2
264 , p_object_version_number IN NUMBER
265 , x_status out nocopy VARCHAR2
266 , x_exception_msg out nocopy VARCHAR2
267 )
268 IS
269
270 l_msg_count NUMBER;
271 l_step NUMBER;
272 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
273 l_obj_no NUMBER;
274 BEGIN
275
276 --dbms_output.put_line (' In update address type. Start ');
277 l_party_site_use_rec.party_site_use_id := p_party_site_use_id;
278 l_party_site_use_rec.application_id := 177;
279 l_party_site_use_rec.created_by_module := 'POS_PROFILE_PKG';
280 l_party_site_use_rec.status := p_status;
281 l_obj_no := p_object_version_number;
282
283 if p_status = 'I' then
284 l_party_site_use_rec.primary_per_type := 'N';
285 end if;
286
287 hz_party_site_v2pub.update_party_site_use
288 ( p_init_msg_list => FND_API.G_FALSE,
289 p_party_site_use_rec => l_party_site_use_rec,
290 p_object_version_number => l_obj_no,
291 x_return_status => x_status,
292 x_msg_count => l_msg_count,
293 x_msg_data => x_exception_msg
294
295 );
296
297 --dbms_output.put_line (' In update address type. End');
298 EXCEPTION
299 WHEN OTHERS THEN
300 X_STATUS :='E';
301 x_exception_msg := 'Failure at step ' || l_step;
302 raise_application_error(-20019, x_exception_msg, true);
303 END update_address_type;
304
305 PROCEDURE update_all_address_type (
306 p_party_site_id IN NUMBER
307 , p_rfq IN VARCHAR2
308 , p_pur IN VARCHAR2
309 , p_pay IN VARCHAR2
310 , p_primaryPay IN VARCHAR2
311 , x_status out nocopy VARCHAR2
312 , x_exception_msg out nocopy VARCHAR2
313 )
314 IS
315
316 l_step NUMBER;
317 l_status hz_party_site_uses.status%TYPE;
318 l_rfq_obj_no hz_party_site_uses.object_version_number%TYPE;
319 l_pay_obj_no hz_party_site_uses.object_version_number%TYPE;
320 l_pur_obj_no hz_party_site_uses.object_version_number%TYPE;
321
322 l_rfq_status hz_party_site_uses.status%TYPE;
323 l_pur_status hz_party_site_uses.status%TYPE;
324 l_pay_status hz_party_site_uses.status%TYPE;
325
326 l_rfq_use_id hz_party_site_uses.party_site_use_id%TYPE;
327 l_pur_use_id hz_party_site_uses.party_site_use_id%TYPE;
328 l_pay_use_id hz_party_site_uses.party_site_use_id%TYPE;
329
330 l_party_site_name hz_party_sites.party_site_name%type;
331 BEGIN
332
333 --dbms_output.put_line (' In update all address types. Start');
334 l_step := 1;
335 x_status := 'S';
336 x_exception_msg := null;
337
338 --dbms_output.put_line (' In update all address types. Step: '||l_step);
339 select pay.party_site_use_id, pur.party_site_use_id, rfq.party_site_use_id,
340 pay.object_version_number, pur.object_version_number, rfq.object_version_number,
341 pay.status, pur.status, rfq.status, hps.party_site_name
342 into l_pay_use_id, l_pur_use_id, l_rfq_use_id, l_pay_obj_no, l_pur_obj_no, l_rfq_obj_no,
343 l_pay_status, l_pur_status, l_rfq_status , l_party_site_name
344 from hz_party_sites hps ,hz_party_site_uses pay, hz_party_site_uses pur, hz_party_site_uses rfq
345 where hps.party_site_id = p_party_site_id
346 --and hps.created_by_module like 'POS%'
347 and pay.party_site_id(+) = hps.party_site_id
348 and pur.party_site_id(+) = hps.party_site_id
349 and rfq.party_site_id(+) = hps.party_site_id
350 and pay.status(+) = 'A'
351 and pur.status(+) = 'A'
352 and rfq.status(+) = 'A'
353 and nvl(pay.end_date(+), sysdate) >= sysdate
354 and nvl(pur.end_date(+), sysdate) >= sysdate
355 and nvl(rfq.end_date(+), sysdate) >= sysdate
356 and nvl(pay.begin_date(+), sysdate) <= sysdate
357 and nvl(pur.begin_date(+), sysdate) <= sysdate
358 and nvl(rfq.begin_date(+), sysdate) <= sysdate
359 and pay.site_use_type(+) = 'PAY'
360 and pur.site_use_type(+) = 'PURCHASING'
361 and rfq.site_use_type(+) = 'RFQ';
362
363 l_step := 2;
364 --dbms_output.put_line (' In update all address types. Step: '||l_step);
365
366 if p_rfq = 'Y' then
367 l_status := 'A';
368 else
369 l_status := 'I';
370 end if;
371
372 l_step := 3;
373 --dbms_output.put_line (' In update all address types. Step: '||l_step);
374 if l_rfq_use_id is not null and l_status = 'I' then
375
376 l_step := 4;
377 --dbms_output.put_line (' In update all address types. Step: '||l_step);
378 POS_PROFILE_PKG.update_address_type (
379 p_party_site_use_id => l_rfq_use_id
380 , p_status => l_status
381 , p_object_version_number => l_rfq_obj_no
382 , x_status => x_status
383 , x_exception_msg => x_exception_msg
384 );
385 else
386 l_step := 5;
387 --dbms_output.put_line (' In update all address types. Step: '||l_step);
388 if l_status = 'A' then
389 assign_address_type (
390 p_party_site_id => p_party_site_id
391 , p_address_type => 'RFQ'
392 , x_party_site_use_id => l_rfq_use_id
393 , x_status => x_status
394 , x_exception_msg => x_exception_msg
395 );
396 end if;
397
398 end if;
399
400 l_step := 6;
401 --dbms_output.put_line (' In update all address types. Step: '||l_step);
402 if p_pur = 'Y' then
403 l_status := 'A';
404 else
405 l_status := 'I';
406 end if;
407
408 l_step := 7;
409 --dbms_output.put_line (' In update all address types. Step: '||l_step);
410 if l_pur_use_id is not null and l_status = 'I' then
411
412 l_step := 8;
413 --dbms_output.put_line (' In update all address types. Step: '||l_step);
414 POS_PROFILE_PKG.update_address_type (
415 p_party_site_use_id => l_pur_use_id
416 , p_status => l_status
417 , p_object_version_number => l_pur_obj_no
418 , x_status => x_status
419 , x_exception_msg => x_exception_msg
420 );
421 else
422 l_step := 9;
423 --dbms_output.put_line (' In update all address types. Step: '||l_step);
424 if l_status = 'A' then
425 l_step := 10;
426 --dbms_output.put_line (' In update all address types. Step: '||l_step);
427 assign_address_type (
428 p_party_site_id => p_party_site_id
429 , p_address_type => 'PURCHASING'
430 , x_party_site_use_id => l_rfq_use_id
431 , x_status => x_status
432 , x_exception_msg => x_exception_msg
433 );
434 end if;
435
436 end if;
437
438 l_step := 11;
439 --dbms_output.put_line (' In update all address types. Step: '||l_step);
440 if p_pay = 'Y' then
441 l_status := 'A';
442 else
443 l_status := 'I';
444 end if;
445
446 l_step := 12;
447 --dbms_output.put_line (' In update all address types. Step: '||l_step);
448 if l_pay_use_id is not null and l_status = 'I' then
449
450 l_step := 13;
451 --dbms_output.put_line (' In update all address types. Step: '||l_step);
452 POS_PROFILE_PKG.update_address_type (
453 p_party_site_use_id => l_pay_use_id
454 , p_status => l_status
455 , p_object_version_number => l_pay_obj_no
456 , x_status => x_status
457 , x_exception_msg => x_exception_msg
458 );
459 else
460 l_step := 14;
461 --dbms_output.put_line (' In update all address types. Step: '||l_step);
462 if l_status = 'A' then
463 assign_address_type (
464 p_party_site_id => p_party_site_id
465 , p_address_type => 'PAY'
466 , x_party_site_use_id => l_rfq_use_id
467 , x_status => x_status
468 , x_exception_msg => x_exception_msg
469 );
470 end if;
471
472 end if;
473
474 l_step := 15;
475 --dbms_output.put_line (' In update all address types. End ');
476 EXCEPTION
477 WHEN OTHERS THEN
478 X_STATUS :='E';
479 x_exception_msg := 'Failure at step ' || l_step;
480 raise_application_error(-20016, x_exception_msg, true);
481 END update_all_address_type;
482
483 PROCEDURE update_party_email(
484 p_party_id IN NUMBER
485 , p_party_type IN VARCHAR2
486 , p_email IN VARCHAR2
487 , x_status OUT NOCOPY VARCHAR2
488 , x_exception_msg OUT NOCOPY VARCHAR2
489 )
490 IS
491 l_contact_point_id NUMBER;
492 l_contact_points_rec hz_contact_point_v2pub.contact_point_rec_type;
493 l_email_rec hz_contact_point_v2pub.email_rec_type;
494 l_exception_msg varchar2(100);
495 return_status VARCHAR2(100);
496 msg_count NUMBER;
497 msg_data VARCHAR2(100);
498 profile_id NUMBER;
499 l_email VARCHAR2(2000);
500 l_update_date DATE;
501 l_object_version_number number;
502 l_old_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
503 l_email_status varchar2(10);
504 BEGIN
505 --dbms_output.put_line (' In update party email .Start');
506 x_exception_msg := 'BEGIN: update_party_email';
507 x_status := 'S';
508
509 --fnd_client_info.set_org_context('-3113');
510
511 IF (web_exist(p_party_id,p_party_type,'EMAIL',NULL)) then
512 SELECT hcp.contact_point_id, nvl(hcp.object_version_number,0), hcp.email_address
513 INTO l_contact_point_id, l_object_version_number,l_old_email
514 FROM hz_contact_points hcp
515 WHERE hcp.owner_table_name = p_party_type
516 AND hcp.owner_table_id = p_party_id
517 AND hcp.contact_point_type = 'EMAIL'
518 AND hcp.primary_flag = 'Y'
519 AND HCP.STATUS = 'A';
520
521 --to prevent a NULL value to be passed to update_contact_points.
522 --TCA fails if NULL is passed to the API
523 IF p_email is NULL or trim(p_email) = '' THEN
524 l_email :=l_old_email ;
525 l_email_status := 'I';
526
527 ELSE
528 l_email :=p_email;
529 l_email_status := 'A';
530 END IF;
531
532 l_contact_points_rec.contact_point_id := l_contact_point_id;
533 l_contact_points_rec.status := l_email_status;
534 l_email_rec.email_format := 'MAILTEXT';
535 l_email_rec.email_address := l_email;
536 l_update_date := get_update_date_from_contact(l_contact_point_id);
537
538 HZ_CONTACT_POINT_V2PUB.update_contact_point(
539 --p_api_version => 1.0,
540 --p_commit => fnd_api.g_false,
541 p_contact_point_rec => l_contact_points_rec,
542 p_email_rec => l_email_rec,
543 p_object_version_number => l_object_version_number,
544 --p_last_update_date => l_update_date, --get_update_date_from_contact(l_contact_point_id),
545 x_return_status => return_status,
546 x_msg_count => msg_count,
547 x_msg_data => msg_data);
548 if (return_status <> 'S') THEN
549 x_exception_msg :=msg_data;
550 raise_application_error(-20001, x_exception_msg, true);
551 END IF;
552 ELSIF (p_email is NOT NULL) THEN
553 l_email_rec.email_format := 'MAILTEXT';
554 l_email_rec.email_address := p_email;
555 l_contact_points_rec.contact_point_type := 'EMAIL';
556 l_contact_points_rec.status := 'A';
557 l_contact_points_rec.owner_table_name := p_party_type;
558 l_contact_points_rec.owner_table_id := p_party_id;
559 l_contact_points_rec.created_by_module := 'POS:PLS:ADM';
560 hz_contact_point_v2pub.create_contact_point
561 (
562 --1.0,fnd_api.g_true,
563 fnd_api.g_false,l_contact_points_rec,null,
564 l_email_rec,null,null,null,profile_id,return_status,
565 msg_count,msg_data);
566 if (return_status <> 'S') THEN
567 x_exception_msg := msg_data;
568 raise_application_error(-20002, x_exception_msg, true);
569 end if;
570 ELSE
571 NULL; --This is the case when the field was null and remains null
572 END IF;
573
574 --dbms_output.put_line (' In update party email .End');
575 x_exception_msg :=NULL;
576 --x_status ='S';
577 EXCEPTION
578 WHEN OTHERS THEN
579 raise_application_error(-20001, x_exception_msg, true);
580 END update_party_email;
581
582 PROCEDURE update_party_phone(
583 p_party_id IN NUMBER
584 , p_party_type IN VARCHAR2
585 , p_country_code IN VARCHAR2
586 , p_area_code IN VARCHAR2
587 , p_number IN VARCHAR2
588 , p_extension IN VARCHAR2
589 , x_status OUT NOCOPY VARCHAR2
590 , x_exception_msg OUT NOCOPY VARCHAR2
591 )
592 IS
593 l_contact_point_id NUMBER;
594 l_contact_points_rec hz_contact_point_v2pub.contact_point_rec_type;
595 l_phone_rec hz_contact_point_v2pub.phone_rec_type;
596 l_exception_msg varchar2(100);
597 return_status VARCHAR2(100);
598 msg_count NUMBER;
599 msg_data VARCHAR2(100);
600 profile_id NUMBER;
601 l_number VARCHAR2(40);
602 l_update_date DATE;
603 l_object_version_number number;
604 l_old_number HZ_CONTACT_POINTS.PHONE_NUMBER%TYPE;
605 BEGIN
606 --dbms_output.put_line (' In update party phone.Start');
607 x_status := 'S';
608 x_exception_msg := 'BEGIN: update_party_phone';
609 x_exception_msg := 'SELECT: contact_point_id';
610 --fnd_client_info.set_org_context('-3113');
611 IF (phone_exist(p_party_id,p_party_type,'PHONE', 'GEN')) THEN
612 SELECT hcp.contact_point_id, nvl(hcp.object_version_number,0), hcp.phone_number
613 INTO l_contact_point_id, l_object_version_number, l_old_number
614 FROM hz_contact_points hcp
615 WHERE hcp.owner_table_name = p_party_type
616 AND hcp.owner_table_id = p_party_id
617 AND hcp.contact_point_type = 'PHONE'
618 AND hcp.phone_line_type = 'GEN'
619 AND hcp.primary_flag = 'Y'
620 AND hcp.status = 'A';
621
622 IF ( p_number is NULL or trim(p_number) = '' ) THEN
623 l_number := l_old_number ;
624 l_contact_points_rec.status := 'I';
625 --l_contact_points_rec.status := 'A';
626 ELSE
627 l_number :=p_number;
628 l_contact_points_rec.status := 'A';
629 END IF;
630
631 l_contact_points_rec.contact_point_id := l_contact_point_id;
632 l_phone_rec.phone_country_code := p_country_code;
633 l_phone_rec.phone_area_code := p_area_code;
634 l_phone_rec.phone_number := l_number;
635 l_phone_rec.phone_extension := p_extension;
636 l_phone_rec.phone_line_type := 'GEN';
637 l_update_date := get_update_date_from_contact(l_contact_point_id);
638
639 x_exception_msg := 'CALL: HZ_CONTACT_POINT_V2PUB.update_contact_point';
640
641 HZ_CONTACT_POINT_V2PUB.update_contact_point(
642 --p_api_version => 1.0,
643 --p_commit => fnd_api.g_false,
644 p_contact_point_rec => l_contact_points_rec,
645 p_phone_rec => l_phone_rec,
646 p_object_version_number => l_object_version_number,
647 --p_last_update_date => l_update_date, --get_update_date_from_contact(l_contact_point_id),
648 x_return_status => return_status,
649 x_msg_count => msg_count, x_msg_data => msg_data);
650 if (return_status <> 'S') THEN
651 x_status := 'E';
652 x_exception_msg :=msg_data;
653 raise_application_error(-20004, x_exception_msg, true);
654 END IF;
655
656 x_status := 'S';
657
658 ELSIF p_number IS NOT NULL THEN
659 l_phone_rec.phone_country_code := p_country_code;
660 l_phone_rec.phone_area_code := p_area_code;
661 l_phone_rec.phone_number := p_number;
662 l_phone_rec.phone_extension := p_extension;
663 l_phone_rec.phone_line_type := 'GEN';
664 l_contact_points_rec.contact_point_type := 'PHONE';
665 l_contact_points_rec.status := 'A';
666 l_contact_points_rec.owner_table_name := p_party_type;
667 l_contact_points_rec.owner_table_id := p_party_id;
668 l_contact_points_rec.created_by_module := 'POS:PLS:ADM';
669 l_contact_points_rec.primary_flag := 'Y';
670 hz_contact_point_v2pub.create_contact_point(
671 --1.0,fnd_api.g_true,
672 fnd_api.g_false,l_contact_points_rec,null,null,l_phone_rec,null,
673 null,profile_id,return_status,msg_count,msg_data);
674 if (return_status <> 'S') THEN
675 x_status := 'E';
676 x_exception_msg := msg_data;
677 raise_application_error(-20005, x_exception_msg, true);
678 end if;
679 x_status := 'S';
680 ELSE
681 NULL; --This is the case when the field was null and remains null
682 x_status := 'S';
683 END IF;
684
685 x_status := 'S';
686 x_exception_msg :=NULL;
687 --dbms_output.put_line (' In update party phone.end');
688 EXCEPTION
689 WHEN OTHERS THEN
690 raise_application_error(-20005,'Update phone number failed: '||x_exception_msg, true);
691 END update_party_phone;
692
693 PROCEDURE update_party_fax(
694 p_party_id IN NUMBER
695 , p_party_type IN VARCHAR2
696 , p_country_code IN VARCHAR2
697 , p_area_code IN VARCHAR2
698 , p_number IN VARCHAR2
699 , p_extension IN VARCHAR2
700 , x_status OUT NOCOPY VARCHAR2
701 , x_exception_msg OUT NOCOPY VARCHAR2
702 )
703 IS
704 l_contact_point_id NUMBER;
705 l_contact_points_rec hz_contact_point_v2pub.contact_point_rec_type;
706 l_phone_rec hz_contact_point_v2pub.phone_rec_type;
707 l_exception_msg varchar2(100);
708 return_status VARCHAR2(100);
709 msg_count NUMBER;
710 msg_data VARCHAR2(100);
711 profile_id NUMBER;
712 l_number VARCHAR2(40);
713 l_update_date DATE;
714 l_object_version_number number;
715 l_old_number HZ_CONTACT_POINTS.PHONE_NUMBER%TYPE;
716 BEGIN
717 --dbms_output.put_line (' In update party fax.start');
718 x_exception_msg := 'BEGIN: update_party_fax';
719 x_status := 'S';
720
721 --fnd_client_info.set_org_context('-3113');
722 IF (phone_exist(p_party_id,p_party_type,'PHONE', 'FAX')) THEN
723 SELECT hcp.contact_point_id, nvl( hcp.object_version_number,0), hcp.phone_number
724 INTO l_contact_point_id, l_object_version_number, l_old_number
725 FROM hz_contact_points hcp
726 WHERE hcp.owner_table_name = p_party_type
727 AND hcp.owner_table_id = p_party_id
728 AND hcp.contact_point_type = 'PHONE'
729 AND hcp.phone_line_type = 'FAX'
730 AND hcp.status = 'A' ;
731
732 IF ( p_number is NULL or trim(p_number) = '' ) THEN
733 l_number :=l_old_number;
734 l_contact_points_rec.status := 'I';
735 --l_contact_points_rec.status := 'A';
736 ELSE
737 l_number :=p_number;
738 l_contact_points_rec.status := 'A';
739 END IF;
740
741 l_contact_points_rec.contact_point_id := l_contact_point_id;
742 l_phone_rec.phone_country_code := p_country_code;
743 l_phone_rec.phone_area_code := p_area_code;
744 l_phone_rec.phone_number := l_number;
745 l_phone_rec.phone_extension := p_extension;
746 l_phone_rec.phone_line_type := 'FAX';
747 l_update_date := get_update_date_from_contact(l_contact_point_id);
748
749 HZ_CONTACT_POINT_V2PUB.update_contact_point(
750 --p_api_version => 1.0,
751 --p_commit => fnd_api.g_false,
752 p_contact_point_rec => l_contact_points_rec,
753 p_phone_rec => l_phone_rec,
754 p_object_version_number =>l_object_version_number,
755 x_return_status => return_status,
756 x_msg_count => msg_count, x_msg_data => msg_data);
757 if (return_status <> 'S') THEN
758 x_exception_msg :=msg_data;
759 raise_application_error(-20005, x_exception_msg, true);
760 END IF;
761 ELSIF p_number IS NOT NULL THEN
762 l_phone_rec.phone_country_code := p_country_code;
763 l_phone_rec.phone_area_code := p_area_code;
764 l_phone_rec.phone_number := p_number;
765 l_phone_rec.phone_extension := p_extension;
766 l_phone_rec.phone_line_type := 'FAX';
767 l_contact_points_rec.contact_point_type := 'PHONE';
768 l_contact_points_rec.status := 'A';
769 l_contact_points_rec.owner_table_name := p_party_type;
770 l_contact_points_rec.owner_table_id := p_party_id;
771 l_contact_points_rec.created_by_module := 'POS:PLS:ADM';
772 l_contact_points_rec.primary_flag := 'N';
773
774 hz_contact_point_v2pub.create_contact_point(
775 --1.0,fnd_api.g_true,
776 fnd_api.g_false,l_contact_points_rec,null,null,l_phone_rec,null,
777 null,profile_id,return_status,msg_count,msg_data);
778 if (return_status <> 'S') THEN
779 x_exception_msg := msg_data;
780 raise_application_error(-20005, x_exception_msg, true);
781 end if;
782 ELSE
783 NULL; --This is the case when the field was null and remains null
784 END IF;
785
786 x_exception_msg :=NULL;
787 --dbms_output.put_line (' In update party fax.end');
788 EXCEPTION
789 WHEN OTHERS THEN
790 ----dbms_output.put_line('Other failure -- '||x_exception_msg);
791 raise;
792
793 END update_party_fax;
794
795 PROCEDURE buyer_update_address_details
796 (
797 p_party_site_id IN NUMBER,
798 p_rfqFlag IN VARCHAR2,
799 p_purFlag IN VARCHAR2,
800 p_payFlag IN VARCHAR2,
801 p_primaryPayFlag IN VARCHAR2,
802 p_note IN VARCHAR2,
803 p_phone_area_code IN VARCHAR2 DEFAULT NULL,
804 p_phone IN VARCHAR2 DEFAULT NULL,
805 p_phone_contact_id IN NUMBER default null,
806 p_phone_obj_ver_num IN NUMBER default null,
807 p_fax_area_code IN VARCHAR2 DEFAULT NULL,
808 p_fax IN VARCHAR2 DEFAULT NULL,
809 p_fax_contact_id IN NUMBER default null,
810 p_fax_obj_ver_num IN NUMBER default null,
811 p_email IN VARCHAR2 DEFAULT NULL,
812 p_email_contact_id IN NUMBER default null,
813 p_email_obj_ver_num IN NUMBER default null,
814 x_status out nocopy VARCHAR2,
815 x_exception_msg out nocopy VARCHAR2
816 )
817 IS
818 l_step NUMBER;
819 lv_proc_name VARCHAR2(30) := 'buyer_update_address_site';
820 BEGIN
821
822 l_step := 0;
823
824 --dbms_output.put_line (lv_proc_name || ' : '||l_step);
825 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
826 FND_LOG.string(fnd_log.level_statement, g_log_module_name
827 || '.' || lv_proc_name, 'Start');
828 END IF;
829
830 l_step := 1;
831 --dbms_output.put_line (lv_proc_name || ' : '||l_step);
832 -- Assign all address types.
833 POS_PROFILE_PKG.update_all_address_type(
834 p_party_site_id => p_party_site_id
835 , p_rfq => p_rfqFlag
836 , p_pur => p_purFlag
837 , p_pay => p_payFlag
838 , p_primaryPay => p_primaryPayFlag
839 , x_status => x_status
840 , x_exception_msg => x_exception_msg
841 );
842
843 --dbms_output.put_line ('Status:'|| x_status ||' : msg '|| x_exception_msg);
844 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
845 FND_LOG.string(fnd_log.level_statement, g_log_module_name
846 || '.' || lv_proc_name, 'Assigned all address types: Status:'
847 || x_status || ': msg: '|| x_exception_msg);
848 END IF;
849
850 l_step := 2;
851 --dbms_output.put_line (lv_proc_name || ' : '||l_step);
852 -- Add the note
853 POS_PROFILE_PKG.update_address_note(
854 p_party_site_id => p_party_site_id
855 , p_note => p_note
856 , x_status => x_status
857 , x_exception_msg => x_exception_msg
858 );
859 --dbms_output.put_line ('Status:'|| x_status ||' : msg '|| x_exception_msg);
860
861 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
862 FND_LOG.string(fnd_log.level_statement, g_log_module_name
863 || '.' || lv_proc_name, 'Assigned address note: Status:'
864 || x_status || ': msg: '|| x_exception_msg);
865 END IF;
866
867 l_step:= 3;
868 --dbms_output.put_line (lv_proc_name || ' : '||l_step);
869
870 -- set phone for the address
871 update_party_phone(
872 p_party_id => p_party_site_id,
873 p_party_type => 'HZ_PARTY_SITES',
874 p_country_code => null,
875 p_area_code => p_phone_area_code ,
876 p_number => p_phone,
877 p_extension => null,
878 x_status => x_status,
879 x_exception_msg => x_exception_msg
880 );
881
882 --dbms_output.put_line ('Status:'|| x_status ||' : msg '|| x_exception_msg);
883 if(x_status <> 'S' ) THEN
884 raise_application_error(-20006,'Update Address: Failed to add phone for party site:'||p_party_site_id, true);
885 END IF;
886
887 -- set fax for the address
888 update_party_fax(
889 p_party_id => p_party_site_id,
890 p_party_type => 'HZ_PARTY_SITES',
891 p_country_code => null,
892 p_area_code => p_fax_area_code ,
893 p_number => p_fax,
894 p_extension => null,
895 x_status => x_status,
896 x_exception_msg => x_exception_msg
897 );
898 --dbms_output.put_line (lv_proc_name || ' : '||l_step);
899 --dbms_output.put_line ('Status:'|| x_status ||' : msg '|| x_exception_msg);
900
901 if(x_status <> 'S' ) THEN
902 raise_application_error(-20006,'Update Address: Failed to add fax for party site:'||p_party_site_id, true);
903 END IF;
904
905 update_party_email(
906 p_party_id => p_party_site_id,
907 p_party_type => 'HZ_PARTY_SITES',
908 p_email => p_email,
909 x_status => x_status,
910 x_exception_msg => x_exception_msg
911 );
912 --dbms_output.put_line (lv_proc_name || ' : '||l_step);
913 --dbms_output.put_line ('Status:'|| x_status ||' : msg '|| x_exception_msg);
914
915 if(x_status <> 'S' ) THEN
916 raise_application_error(-20006,'Update Address: Failed to add phone for party site:'||p_party_site_id, true);
917 END IF;
918
919 END buyer_update_address_details;
920
921 PROCEDURE remove_address (
922 p_party_site_id IN NUMBER
923 , x_status out nocopy VARCHAR2
924 , x_exception_msg out nocopy VARCHAR2
925 )
926 IS
927 l_step NUMBER;
928 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
929 l_msg_count NUMBER;
930 l_obj_ver HZ_PARTY_SITES.object_version_number%TYPE;
931 l_created_by_module HZ_PARTY_SITES.created_by_module%TYPE;
932
933 cursor l_site_cur is
934 select vendor_site_id, party_site_id, vendor_id from ap_supplier_sites_all where party_site_id = p_party_site_id;
935 l_site_rec l_site_cur%ROWTYPE;
936 l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
937 l_return_status varchar2(1);
938 l_msg_data varchar2(4000);
939
940 cursor l_contact_cur is
941 select distinct ASCS.per_party_id
942 from ap_supplier_contacts ASCS
943 where (ASCS.inactive_date is null OR ASCS.inactive_date > sysdate)
944 AND ASCS.org_party_site_id = p_party_site_id;
945 l_vendor_id number;
946 l_contact_rec l_contact_cur%ROWTYPE;
947
948 l_result_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
949 l_payee_rec IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_Rec_Type;
950 l_pay_instr_rec IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
951 l_pay_assign_rec IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
952 l_payee_assignment_id number;
953
954 cursor l_bank_cur is
955 select uses.instrument_id, uses.order_of_preference, uses.start_date,
956 payee.supplier_site_id, payee.org_id, payee.org_type,
957 hps.party_id, uses.instrument_payment_use_id
958 from iby_pmt_instr_uses_all uses, iby_external_payees_all payee, hz_party_sites hps
959 where uses.instrument_type = 'BANKACCOUNT'
960 and payee.ext_payee_id = uses.ext_pmt_party_id
961 and payee.payee_party_id = hps.party_id
962 and payee.payment_function = 'PAYABLES_DISB'
963 and payee.party_site_id = hps.party_site_id
964 and hps.party_site_id = p_party_site_id
965 and (uses.end_date is null OR uses.end_date > sysdate)
966 order by uses.order_of_preference;
967
968 l_bank_rec l_bank_cur%ROWTYPE;
969
970 BEGIN
971 l_step := 0;
972
973 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
974 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
975 ' Begin remove_address ');
976 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
977 ' p_party_site_id ' || p_party_site_id);
978 END IF;
979
980 -- End date the party_site in hz_party_sites with party_site_id = p_party_site_id
981 select object_version_number, created_by_module
982 into l_obj_ver, l_created_by_module
983 from hz_party_sites
984 where party_site_id = p_party_site_id;
985
986 l_party_site_rec.party_site_id := p_party_site_id;
987 l_party_site_rec.status := 'I';
988 l_party_site_rec.created_by_module := l_created_by_module;
989
990 hz_party_site_v2pub.update_party_site(FND_API.G_FALSE,
991 l_party_site_rec,
992 l_obj_ver,
993 x_status,
994 l_msg_count,
995 x_exception_msg);
996
997 l_step := 1;
998
999 -- Inactivate all the vendor sites.
1000 for l_site_rec in l_site_cur loop
1001 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1002 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1003 ' l_vendor_site_id ' || l_site_rec.vendor_site_id);
1004 END IF;
1005
1006 l_vendor_site_rec.vendor_site_id := l_site_rec.vendor_site_id;
1007 l_vendor_site_rec.party_site_id := l_site_rec.party_site_id;
1008 l_vendor_site_rec.vendor_id := l_site_rec.vendor_id;
1009 l_vendor_site_rec.inactive_date := sysdate;
1010
1011 POS_VENDOR_PUB_PKG.Update_Vendor_Site
1012 (
1013 l_vendor_site_rec,
1014 l_return_status,
1015 l_msg_count,
1016 l_msg_data
1017 );
1018
1019 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1020 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1021 ' AP Update Vendor Site Status ' || l_return_status);
1022 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1023 ' AP Update Vendor Site Count ' || l_msg_count);
1024 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1025 ' AP Update Vendor Site exception msg ' || l_msg_data);
1026 END IF;
1027 end loop;
1028
1029 l_step := 2;
1030
1031 -- Inactivate all the vendor contacts address linkages.
1032 for l_contact_rec in l_contact_cur loop
1033 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1034 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1035 ' l_contact_party_id ' || l_contact_rec.per_party_id);
1036 END IF;
1037
1038 pos_supplier_address_pkg.unassign_address_to_contact
1039 (p_contact_party_id => l_contact_rec.per_party_id,
1040 p_org_party_site_id => p_party_site_id,
1041 p_vendor_id => l_vendor_id,
1042 x_return_status => l_return_status,
1043 x_msg_count => l_msg_count,
1044 x_msg_data => l_msg_data);
1045
1046 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1047 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1048 ' AP Update Vendor Contact Status ' || l_return_status);
1049 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1050 ' AP Update Vendor Contact Count ' || l_msg_count);
1051 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1052 ' AP Update Vendor Contact exception msg ' || l_msg_data);
1053 END IF;
1054
1055 end loop;
1056
1057 l_step := 3;
1058 -- Inactivate all the bank accounts linked to this address.
1059 for l_bank_rec in l_bank_cur loop
1060 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1061 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1062 ' l_instrument_id ' || l_bank_rec.instrument_id);
1063 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1064 ' l_assignment_id ' || l_bank_rec.instrument_payment_use_id);
1065 END IF;
1066 -- Payee Record
1067 l_payee_rec.Payment_Function := 'PAYABLES_DISB';
1068 l_payee_rec.Party_id := l_bank_rec.party_id;
1069 l_payee_rec.Party_Site_id := p_party_site_id;
1070 l_payee_rec.org_Id := l_bank_rec.org_id;
1071 l_payee_rec.Supplier_Site_id := l_bank_rec.supplier_site_id;
1072 l_payee_rec.Org_Type := l_bank_rec.org_type;
1073
1074 -- Instrument Record.
1075 l_pay_instr_rec.Instrument_Type := 'BANKACCOUNT';
1076 l_pay_instr_rec.Instrument_Id := l_bank_rec.instrument_id;
1077
1078 -- Assignment Record.
1079 l_pay_assign_rec.Instrument := l_pay_instr_rec;
1080 l_pay_assign_rec.assignment_id := l_bank_rec.instrument_payment_use_id;
1081 l_pay_assign_rec.Priority := l_bank_rec.order_of_preference;
1082 l_pay_assign_rec.Start_Date := l_bank_rec.start_date;
1083 l_pay_assign_rec.End_Date := sysdate;
1084
1085 IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment(
1086 p_api_version => 1.0,
1087 p_init_msg_list => FND_API.G_FALSE,
1088 p_commit => FND_API.G_FALSE,
1089 x_return_status => x_status,
1090 x_msg_count => l_msg_count,
1091 x_msg_data => x_exception_msg,
1092 p_payee => l_payee_rec,
1093 p_assignment_attribs => l_pay_assign_rec,
1094 x_assign_id => l_payee_assignment_id,
1095 x_response => l_result_rec
1096 );
1097
1098 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1099 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1100 ' After Set_Payee_Instr_Assignment ');
1101 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1102 ' x_return_status ' || x_status);
1103 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1104 ' x_msg_count ' || l_msg_count);
1105 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1106 ' x_msg_data ' || x_exception_msg);
1107 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1108 ' x_assign_id ' || l_payee_assignment_id);
1109 END IF;
1110
1111 end loop;
1112
1113 x_status :='S';
1114 x_exception_msg :=NULL;
1115
1116 EXCEPTION
1117 WHEN OTHERS THEN
1118 X_STATUS :='E';
1119 x_exception_msg := 'Failure at step ' || l_step;
1120 raise_application_error(-20015, x_exception_msg || sqlerrm, true);
1121 END remove_address;
1122
1123 END POS_PROFILE_PKG;