[Home] [Help]
PACKAGE BODY: APPS.POS_SUPPLIER_ADDRESS_PKG
Source
1 PACKAGE BODY pos_supplier_address_pkg AS
2 /* $Header: POSSAB.pls 120.26 2011/09/21 06:43:34 ramkandu ship $ */
3
4 g_module VARCHAR2(30) := 'POS_SUPPLIER_ADDRESS_PKG';
5
6 PROCEDURE assign_address_type
7 ( p_party_site_id IN NUMBER,
8 p_address_type IN VARCHAR2,
9 x_party_site_use_id OUT nocopy NUMBER,
10 x_return_status OUT nocopy VARCHAR2,
11 x_msg_count OUT nocopy NUMBER,
12 x_msg_data OUT nocopy VARCHAR2
13 )
14 IS
15 CURSOR l_cur IS
16 SELECT party_site_use_id
17 FROM hz_party_site_uses
18 WHERE party_site_id = p_party_site_id
19 AND site_use_type = p_address_type
20 AND status = 'A';
21
22 l_rec l_cur%ROWTYPE;
23 l_found BOOLEAN;
24
25 l_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type;
26
27 l_method VARCHAR(30);
28 l_step VARCHAR2(100);
29 BEGIN
30
31 l_method := 'assign_address_type';
32 -- log_values p_party_site_id p_address_type
33 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
34 fnd_log.string( fnd_log.level_statement
35 , g_module || '.' || l_method
36 , ' p_party_site_id = ' || p_party_site_id
37 || ' p_address_type = ' || p_address_type
38 );
39 END IF;
40
41 l_step := 'check existing address type';
42 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
43 fnd_log.string
44 (fnd_log.level_statement
45 , g_module || '.' || l_method
46 , l_step
47 );
48 END IF;
49
50 OPEN l_cur;
51 FETCH l_cur INTO l_rec;
52 l_found := l_cur%found;
53 CLOSE l_cur;
54
55 IF l_found THEN
56 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
57 fnd_log.string
58 (fnd_log.level_statement
59 , g_module || '.' || l_method
60 , l_step || ' found an existing record'
61 );
62 END IF;
63
64 x_return_status := fnd_api.g_ret_sts_success;
65 RETURN;
66 ELSE
67 l_step := 'call hz_party_site_v2pub.create_party_site_use';
68
69 l_party_site_use_rec.party_site_id := p_party_site_id;
70 l_party_site_use_rec.application_id := 177;
71 l_party_site_use_rec.created_by_module := 'POS_SUPPLIER_MGMT';
72 l_party_site_use_rec.status := 'A';
73 l_party_site_use_rec.site_use_type := p_address_type;
74
75 hz_party_site_v2pub.create_party_site_use
76 ( p_init_msg_list => FND_API.G_FALSE,
77 p_party_site_use_rec => l_party_site_use_rec,
78 x_party_site_use_id => x_party_site_use_id,
79 x_return_status => x_return_status,
80 x_msg_count => x_msg_count,
81 x_msg_data => x_msg_data
82 );
83
84 -- log_callresult x_party_site_use_id
85 IF x_return_status = FND_API.g_ret_sts_success THEN
86 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
87 fnd_log.string( fnd_log.level_statement
88 , g_module || '.' || l_method
89 , l_step || ' x_party_site_use_id = ' || x_party_site_use_id
90 );
91 END IF;
92 ELSE
93 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
94 fnd_log.string( fnd_log.level_error
95 , g_module || '.' || l_method
96 , l_step || ': x_return_status = ' || x_return_status
97 || ' x_msg_count = ' || x_msg_count
98 || ', x_msg_data = ' || x_msg_data
99 );
100 END IF;
101 END IF;
102 END IF;
103 END assign_address_type;
104
105 PROCEDURE remove_address_type
106 ( p_party_site_id IN NUMBER,
107 p_address_type IN VARCHAR2,
108 x_return_status OUT nocopy VARCHAR2,
109 x_msg_count OUT nocopy NUMBER,
110 x_msg_data OUT nocopy VARCHAR2
111 )
112 IS
113 CURSOR l_cur IS
114 SELECT party_site_use_id, object_version_number
115 FROM hz_party_site_uses
116 WHERE party_site_id = p_party_site_id
117 AND site_use_type = p_address_type
118 AND status = 'A';
119
120 l_rec l_cur%ROWTYPE;
121 l_found BOOLEAN;
122
123 l_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type;
124
125 l_method VARCHAR(30);
126 l_step VARCHAR2(100);
127 BEGIN
128
129 l_method := 'remove_address_type';
130 -- log_values p_party_site_id p_address_type
131 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
132 fnd_log.string( fnd_log.level_statement
133 , g_module || '.' || l_method
134 , ' p_party_site_id = ' || p_party_site_id
135 || ' p_address_type = ' || p_address_type
136 );
137 END IF;
138
139 l_step := 'check existing address type';
140 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
141 fnd_log.string
142 (fnd_log.level_statement
143 , g_module || '.' || l_method
144 , l_step
145 );
146 END IF;
147
148 OPEN l_cur;
149 FETCH l_cur INTO l_rec;
150 l_found := l_cur%found;
151 CLOSE l_cur;
152
153 IF NOT l_found THEN
154 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
155 fnd_log.string
156 (fnd_log.level_statement
157 , g_module || '.' || l_method
158 , l_step || ' existing record not found so no record to end-date.'
159 );
160 END IF;
161
162 x_return_status := fnd_api.g_ret_sts_success;
163 RETURN;
164 ELSE
165 l_step := 'call hz_party_site_v2pub.update_party_site_use';
166 l_party_site_use_rec.party_site_use_id := l_rec.party_site_use_id;
167 l_party_site_use_rec.status := 'I';
168
169 hz_party_site_v2pub.update_party_site_use
170 ( p_init_msg_list => FND_API.G_FALSE,
171 p_party_site_use_rec => l_party_site_use_rec,
172 p_object_version_number => l_rec.object_version_number,
173 x_return_status => x_return_status,
174 x_msg_count => x_msg_count,
175 x_msg_data => x_msg_data
176 );
177
178 IF x_return_status = FND_API.g_ret_sts_success THEN
179 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
180 fnd_log.string( fnd_log.level_statement
181 , g_module || '.' || l_method
182 , l_step || ' succeeded'
183 );
184 END IF;
185 ELSE
186 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
187 fnd_log.string( fnd_log.level_error
188 , g_module || '.' || l_method
189 , l_step || ': x_return_status = ' || x_return_status
190 || ' x_msg_count = ' || x_msg_count
191 || ', x_msg_data = ' || x_msg_data
192 );
193 END IF;
194 END IF;
195 END IF;
196 END remove_address_type;
197
198 PROCEDURE handle_address_type
199 (p_party_site_id IN NUMBER,
200 p_address_type IN VARCHAR2,
201 p_value IN VARCHAR2,
202 x_return_status OUT nocopy VARCHAR2,
203 x_msg_count OUT nocopy NUMBER,
204 x_msg_data OUT nocopy VARCHAR2
205 )
206 IS
207 l_method VARCHAR2(30);
208 l_step VARCHAR2(100);
209 l_party_site_use_id NUMBER;
210 BEGIN
211
212 IF p_value = 'Y' THEN
213 l_step := 'call assign_address_type ';
214 ELSE
215 l_step := 'call remove_address_type ';
216 END IF;
217
218 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
219 fnd_log.string
220 (fnd_log.level_statement
221 , g_module || '.' || l_method
222 , l_step || ' p_address_type = ' || p_address_type
223 || ' p_party_site_id = ' || p_party_site_id
224 );
225 END IF;
226
227 IF p_value = 'Y' THEN
228 assign_address_type
229 (p_party_site_id => p_party_site_id,
230 p_address_type => p_address_type,
231 x_party_site_use_id => l_party_site_use_id,
232 x_return_status => x_return_status,
233 x_msg_count => x_msg_count,
234 x_msg_data => x_msg_data
235 );
236 ELSE
237 remove_address_type
238 (p_party_site_id => p_party_site_id,
239 p_address_type => p_address_type,
240 x_return_status => x_return_status,
241 x_msg_count => x_msg_count,
242 x_msg_data => x_msg_data
243 );
244 END IF;
245
246 END handle_address_type;
247
248 PROCEDURE check_payables_options
249 (
250 p_vendor_id IN NUMBER,
251 x_return_status OUT nocopy VARCHAR2,
252 x_msg_count OUT nocopy NUMBER,
253 x_msg_data OUT nocopy VARCHAR2
254 )
255 IS
256 l_orgs VARCHAR2(3000);
257 l_found BOOLEAN;
258 l_reg_ou_id NUMBER := null;
259 BEGIN
260 l_found := FALSE;
261 /* bug9505563: Use OU ID from the Registration Request to create the sites */
262
263 IF (FND_PROFILE.VALUE('POS_SM_SITE_ENABLE_OPTION') = 'REQOU') THEN
264
265 begin
266
267 SELECT ou_id
268 INTO l_reg_ou_id
269 FROM pos_supplier_registrations
270 WHERE po_vendor_id = p_vendor_id;
271
272 exception
273 when others then
274 l_reg_ou_id := null;
275 end;
276 END IF;
277
278 FOR x IN (SELECT name
279 FROM hr_operating_units o
280 WHERE mo_global.check_access(organization_id) = 'Y'
281 AND o.organization_id = nvl(l_reg_ou_id,o.organization_id)
282 AND NOT exists
283 (SELECT 1
284 FROM ap_system_parameters_all
285 WHERE o.organization_id = org_id
286 )
287 )
288 LOOP
289 l_found := TRUE;
290 IF l_orgs IS NULL THEN
291 l_orgs := x.name;
292 ELSE
293 l_orgs := l_orgs || ', ' || x.name;
294 END IF;
295 END LOOP;
296
297 IF l_found THEN
298 x_return_status := 'E';
299 fnd_message.set_name('POS','POS_ORG_PAY_PARAM_MISS');
300 fnd_message.set_token('OPERATING_UNITS', l_orgs);
301 fnd_msg_pub.add;
302 fnd_msg_pub.count_and_get
303 (p_count => x_msg_count,
304 p_data => x_msg_data
305 );
306 ELSE
307 x_return_status := 'S';
308 END IF;
309
310 END check_payables_options;
311
312 PROCEDURE create_supplier_address
313 (p_vendor_id IN NUMBER,
314 p_vendor_party_id IN NUMBER,
315 p_party_site_name IN VARCHAR2,
316 p_address_line1 IN VARCHAR2,
317 p_address_line2 IN VARCHAR2,
318 p_address_line3 IN VARCHAR2,
319 p_address_line4 IN VARCHAR2,
320 p_country IN VARCHAR2,
321 p_city IN VARCHAR2,
322 p_state IN VARCHAR2,
323 p_province IN VARCHAR2,
324 p_postal_code IN VARCHAR2,
325 p_county IN VARCHAR2,
326 p_rfq_flag IN VARCHAR2,
327 p_pur_flag IN VARCHAR2,
328 p_pay_flag IN VARCHAR2,
329 p_primary_pay_flag IN VARCHAR2,
330 p_phone_area_code IN VARCHAR2,
331 p_phone_number IN VARCHAR2,
332 p_phone_extension IN VARCHAR2,
333 p_fax_area_code IN VARCHAR2,
334 p_fax_number IN VARCHAR2,
335 p_email_address IN VARCHAR2,
336 x_return_status OUT nocopy VARCHAR2,
337 x_msg_count OUT nocopy NUMBER,
338 x_msg_data OUT nocopy VARCHAR2,
339 x_party_site_id OUT nocopy NUMBER
340 )
341 IS
342 l_party_site_number hz_party_sites.party_site_number%TYPE;
343 l_party_site_id NUMBER;
344 l_location_id NUMBER;
345 l_party_site_use_id NUMBER;
346 l_ou_ids pos_security_profile_utl_pkg.number_table;
347 l_ou_count NUMBER;
348 l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
349 l_vendor_site_id NUMBER;
350 l_dummy_location_id NUMBER;
351 l_dummy_party_site_id NUMBER;
352
353 /* bug9505563: Use OU ID from the Registration Request to create the sites */
354 l_reg_ou_id NUMBER;
355
356 BEGIN
357 SAVEPOINT create_supplier_address_sp;
358
359 check_payables_options(
360 p_vendor_id => p_vendor_id,
361 x_return_status => x_return_status,
362 x_msg_count => x_msg_count,
363 x_msg_data => x_msg_data);
364
365 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
366 ROLLBACK TO create_supplier_address_sp;
367 RETURN;
368 END IF;
369
370 pos_security_profile_utl_pkg.get_current_ous (l_ou_ids, l_ou_count);
371
372 pos_hz_util_pkg.pos_create_hz_location
373 (p_country_code => p_country,
374 p_address1 => p_address_line1,
375 p_address2 => p_address_line2,
376 p_address3 => p_address_line3,
377 p_address4 => p_address_line4,
378 p_city => p_city,
379 p_postal_code => p_postal_code,
380 p_county => p_county,
381 p_state => p_state ,
382 p_province => p_province,
383 x_location_id => l_location_id,
384 x_return_status => x_return_status,
385 x_msg_count => x_msg_count,
386 x_msg_data => x_msg_data
387 );
388
389 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
390 ROLLBACK TO create_supplier_address_sp;
391 RETURN;
392 END IF;
393
394 pos_hz_util_pkg.pos_create_party_site
395 (p_party_id => p_vendor_party_id,
396 p_location_id => l_location_id,
397 p_party_site_name => p_party_site_name,
398 x_party_site_id => l_party_site_id,
399 x_party_site_number => l_party_site_number,
400 x_return_status => x_return_status,
401 x_msg_count => x_msg_count,
402 x_msg_data => x_msg_data
403 );
404
405 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
406 ROLLBACK TO create_supplier_address_sp;
407 RETURN;
408 END IF;
409
410 x_party_site_id := l_party_site_id;
411
412 IF p_rfq_flag IS NOT NULL AND p_rfq_flag = 'Y' THEN
413 assign_address_type
414 (p_party_site_id => l_party_site_id,
415 p_address_type => 'RFQ', -- 'RFQ',
416 x_party_site_use_id => l_party_site_use_id,
417 x_return_status => x_return_status,
418 x_msg_count => x_msg_count,
419 x_msg_data => x_msg_data
420 );
421 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
422 ROLLBACK TO create_supplier_address_sp;
423 RETURN;
424 END IF;
425 END IF;
426
427 IF p_pur_flag IS NOT NULL AND p_pur_flag = 'Y' THEN
428 assign_address_type
429 (p_party_site_id => l_party_site_id,
430 p_address_type => 'PURCHASING', -- 'PURCHASING',
431 x_party_site_use_id => l_party_site_use_id,
432 x_return_status => x_return_status,
433 x_msg_count => x_msg_count,
434 x_msg_data => x_msg_data
435 );
436 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
437 ROLLBACK TO create_supplier_address_sp;
438 RETURN;
439 END IF;
440 END IF;
441
442 IF p_pay_flag IS NOT NULL AND p_pay_flag = 'Y' THEN
443 assign_address_type
444 (p_party_site_id => l_party_site_id,
445 p_address_type => 'PAY', --'PAY',
446 x_party_site_use_id => l_party_site_use_id,
447 x_return_status => x_return_status,
448 x_msg_count => x_msg_count,
449 x_msg_data => x_msg_data
450 );
451 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
452 ROLLBACK TO create_supplier_address_sp;
453 RETURN;
454 END IF;
455 END IF;
456
457 IF p_primary_pay_flag IS NOT NULL AND p_primary_pay_flag = 'Y' THEN
458 -- assign_address_type
459 -- (p_party_site_id => l_party_site_id,
460 -- p_address_type => 'PRIMARY_PAY', -- ap has not seeded this
461 -- x_party_site_use_id => l_party_site_use_id,
462 -- x_return_status => x_return_status,
463 -- x_msg_count => x_msg_count,
464 -- x_msg_data => x_msg_data
465 -- );
466 -- IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
467 -- RETURN;
468 -- END IF;
469 NULL;
470 END IF;
471
472 pos_hz_contact_point_pkg.update_party_site_phone
473 (
474 p_party_site_id => l_party_site_id,
475 p_country_code => NULL,
476 p_area_code => p_phone_area_code ,
477 p_number => p_phone_number,
478 p_extension => NULL,
479 x_return_status => x_return_status,
480 x_msg_count => x_msg_count,
481 x_msg_data => x_msg_data
482 );
483
484 IF x_return_status IS NULL OR
485 x_return_status <> fnd_api.g_ret_sts_success THEN
486 ROLLBACK TO create_supplier_address_sp;
487 RETURN;
488 END IF;
489
490 pos_hz_contact_point_pkg.update_party_site_fax
491 (
492 p_party_site_id => l_party_site_id,
493 p_country_code => NULL,
494 p_area_code => p_fax_area_code ,
495 p_number => p_fax_number,
496 p_extension => NULL,
497 x_return_status => x_return_status,
498 x_msg_count => x_msg_count,
499 x_msg_data => x_msg_data
500 );
501
502 IF x_return_status IS NULL OR
503 x_return_status <> fnd_api.g_ret_sts_success THEN
504 ROLLBACK TO create_supplier_address_sp;
505 RETURN;
506 END IF;
507
508 pos_hz_contact_point_pkg.update_party_site_email
509 (
510 p_party_site_id => l_party_site_id,
511 p_email => p_email_address,
512 x_return_status => x_return_status,
513 x_msg_count => x_msg_count,
514 x_msg_data => x_msg_data
515 );
516
517 IF x_return_status IS NULL OR
518 x_return_status <> fnd_api.g_ret_sts_success THEN
519 ROLLBACK TO create_supplier_address_sp;
520 RETURN;
521 END IF;
522
523 -- create vendor sites
524
525 /* bug 9505563 - Supplier Hub Changes
526 create vendor site only in supplier registration OU. */
527
528 IF (FND_PROFILE.VALUE('POS_SM_SITE_ENABLE_OPTION') = 'REQOU') THEN
529
530 begin
531
532 SELECT ou_id
533 INTO l_reg_ou_id
534 FROM pos_supplier_registrations
535 WHERE po_vendor_id = p_vendor_id;
536
537 exception
538 when others then
539 l_reg_ou_id := null;
540 end;
541
542
543 IF l_reg_ou_id IS NOT NULL THEN
544
545 l_vendor_site_rec := NULL;
546 l_vendor_site_rec.org_id := l_reg_ou_id;
547 l_vendor_site_rec.vendor_id := p_vendor_id;
548 l_vendor_site_rec.location_id := l_location_id;
549 l_vendor_site_rec.party_site_id := l_party_site_id;
550 l_vendor_site_rec.vendor_site_code := substrb(p_party_site_name,
551 1,15);
552 l_vendor_site_rec.purchasing_site_flag := p_pur_flag;
553 l_vendor_site_rec.rfq_only_site_flag := p_rfq_flag;
554 l_vendor_site_rec.pay_site_flag := p_pay_flag;
555 l_vendor_site_rec.primary_pay_site_flag := p_primary_pay_flag;
556 l_vendor_site_rec.email_address := p_email_address;
557 l_vendor_site_rec.area_code := p_phone_area_code;
558 l_vendor_site_rec.phone := p_phone_number;
559 l_vendor_site_rec.fax_area_code := p_fax_area_code;
560 l_vendor_site_rec.fax := p_fax_number;
561
562 pos_vendor_pub_pkg.create_vendor_site(
563 p_vendor_site_rec => l_vendor_site_rec,
564 x_return_status => x_return_status,
565 x_msg_count => x_msg_count,
566 x_msg_data => x_msg_data,
567 x_vendor_site_id => l_vendor_site_id,
568 x_party_site_id => l_dummy_party_site_id,
569 x_location_id => l_dummy_location_id);
570
571 IF (x_return_status IS NULL OR
572 x_return_status <> fnd_api.g_ret_sts_success) THEN
573 ROLLBACK TO create_supplier_address_sp;
574 RETURN;
575 END IF;
576 END IF;
577 ELSIF (FND_PROFILE.VALUE('POS_SM_SITE_ENABLE_OPTION') = 'MOACSP') THEN
578 -- Create Sites in all OUs as per MO:Security profile option
579
580 FOR l_index IN 1..l_ou_count LOOP
581
582 l_vendor_site_rec := NULL;
583
584 l_vendor_site_rec.org_id := l_ou_ids(l_index);
585 l_vendor_site_rec.vendor_id := p_vendor_id;
586 l_vendor_site_rec.location_id := l_location_id;
587 l_vendor_site_rec.party_site_id := l_party_site_id;
588 l_vendor_site_rec.vendor_site_code := substrb(p_party_site_name, 1, 15);
589 l_vendor_site_rec.purchasing_site_flag := p_pur_flag;
590 l_vendor_site_rec.rfq_only_site_flag := p_rfq_flag;
591 l_vendor_site_rec.pay_site_flag := p_pay_flag;
592 l_vendor_site_rec.primary_pay_site_flag := p_primary_pay_flag;
593 l_vendor_site_rec.email_address := p_email_address;
594 l_vendor_site_rec.area_code := p_phone_area_code;
595 l_vendor_site_rec.phone := p_phone_number;
596 l_vendor_site_rec.fax_area_code := p_fax_area_code;
597 l_vendor_site_rec.fax := p_fax_number;
598
599 pos_vendor_pub_pkg.create_vendor_site
600 ( p_vendor_site_rec => l_vendor_site_rec,
601 x_return_status => x_return_status,
602 x_msg_count => x_msg_count,
603 x_msg_data => x_msg_data,
604 x_vendor_site_id => l_vendor_site_id,
605 x_party_site_id => l_dummy_party_site_id,
606 x_location_id => l_dummy_location_id
607 );
608
609 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
610 ROLLBACK TO create_supplier_address_sp;
611 RETURN;
612 END IF;
613
614 END LOOP;
615 END IF;
616
617 x_return_status := fnd_api.g_ret_sts_success;
618
619 EXCEPTION
620 WHEN OTHERS THEN
621 ROLLBACK TO create_supplier_address_sp;
622 x_return_status := fnd_api.g_ret_sts_unexp_error;
623 x_msg_data := Sqlerrm;
624 x_msg_count := 1;
625 pos_log.log_sqlerrm('POSSAB','create_supplier_address');
626 END create_supplier_address;
627
628 PROCEDURE update_supplier_address
629 (p_vendor_id IN NUMBER,
630 p_vendor_party_id IN NUMBER,
631 p_party_site_id IN NUMBER,
632 p_party_site_name IN VARCHAR2,
633 p_address_line1 IN VARCHAR2,
634 p_address_line2 IN VARCHAR2,
635 p_address_line3 IN VARCHAR2,
636 p_address_line4 IN VARCHAR2,
637 p_country IN VARCHAR2,
638 p_city IN VARCHAR2,
639 p_state IN VARCHAR2,
640 p_province IN VARCHAR2,
641 p_postal_code IN VARCHAR2,
642 p_county IN VARCHAR2,
643 p_rfq_flag IN VARCHAR2,
644 p_pur_flag IN VARCHAR2,
645 p_pay_flag IN VARCHAR2,
646 p_primary_pay_flag IN VARCHAR2,
647 p_phone_area_code IN VARCHAR2,
648 p_phone_number IN VARCHAR2,
649 p_phone_extension IN VARCHAR2,
650 p_fax_area_code IN VARCHAR2,
651 p_fax_number IN VARCHAR2,
652 p_email_address IN VARCHAR2,
653 x_return_status OUT nocopy VARCHAR2,
654 x_msg_count OUT nocopy NUMBER,
655 x_msg_data OUT nocopy VARCHAR2
656 )
657 IS
658 l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
659 l_location_rec hz_location_v2pub.location_rec_type;
660 l_obj_ver hz_locations.object_version_number%TYPE;
661
662 CURSOR l_cur IS
663 SELECT object_version_number,location_id
664 from hz_locations
665 where location_id =
666 (SELECT location_id
667 FROM hz_party_sites
668 WHERE party_site_id = p_party_site_id
669 ) FOR UPDATE;
670
671 l_rec l_cur%ROWTYPE;
672
673 CURSOR l_cur2 IS
674 select object_version_number, party_site_name
675 from hz_party_sites
676 where party_site_id = p_party_site_id FOR UPDATE;
677
678 l_rec2 l_cur2%ROWTYPE;
679
680 BEGIN
681 SAVEPOINT update_supplier_address_sp;
682 OPEN l_cur;
683 FETCH l_cur INTO l_rec;
684 IF l_cur%notfound THEN
685 CLOSE l_cur;
686 -- prepare err msg
687 RETURN;
688 END IF;
689 CLOSE l_cur;
690
691 OPEN l_cur2;
692 FETCH l_cur2 INTO l_rec2;
693 IF l_cur2%notfound THEN
694 CLOSE l_cur2;
695 -- prepare err msg
696 RETURN;
697 END IF;
698 CLOSE l_cur2;
699
700 l_location_rec.location_id := l_rec.location_id;
701 l_location_rec.address1 := Nvl(p_address_line1, fnd_api.g_miss_char);
702 l_location_rec.address2 := Nvl(p_address_line2, fnd_api.g_miss_char);
703 l_location_rec.address3 := Nvl(p_address_line3, fnd_api.g_miss_char);
704 l_location_rec.address4 := Nvl(p_address_line4, fnd_api.g_miss_char);
705 l_location_rec.city := Nvl(p_city , fnd_api.g_miss_char);
706 l_location_rec.postal_code := Nvl(p_postal_code , fnd_api.g_miss_char);
707 l_location_rec.state := Nvl(p_state , fnd_api.g_miss_char);
708 l_location_rec.province := Nvl(p_province , fnd_api.g_miss_char);
709 l_location_rec.county := Nvl(p_county , fnd_api.g_miss_char);
710 l_location_rec.country := Nvl(p_country , fnd_api.g_miss_char);
711
712 l_obj_ver := l_rec.object_version_number;
713
714 hz_location_v2pub.update_location
715 ( p_init_msg_list => fnd_api.g_true,
716 p_location_rec => l_location_rec,
717 p_object_version_number => l_obj_ver,
718 x_return_status => x_return_status,
719 x_msg_count => x_msg_count,
720 x_msg_data => x_msg_data
721 );
722
723 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
724 ROLLBACK TO update_supplier_address_sp;
725 RETURN;
726 END IF;
727
728 IF l_rec2.party_site_name IS NULL AND p_party_site_name IS NOT NULL OR
729 l_rec2.party_site_name IS NOT NULL AND p_party_site_name IS NULL OR
730 l_rec2.party_site_name <> p_party_site_name THEN
731
732 l_party_site_rec.party_site_id := p_party_site_id;
733 l_party_site_rec.party_site_name := p_party_site_name;
734
735 hz_party_site_v2pub.update_party_site
736 (p_init_msg_list => fnd_api.g_false,
737 p_party_site_rec => l_party_site_rec,
738 p_object_version_number => l_rec2.object_version_number,
739 x_return_status => x_return_status,
740 x_msg_count => x_msg_count,
741 x_msg_data => x_msg_data
742 );
743
744 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
745 ROLLBACK TO update_supplier_address_sp;
746 RETURN;
747 END IF;
748 END IF;
749
750 -- set phone for the address
751 pos_hz_contact_point_pkg.update_party_site_phone
752 (
753 p_party_site_id => p_party_site_id,
754 p_country_code => NULL,
755 p_area_code => p_phone_area_code ,
756 p_number => p_phone_number,
757 p_extension => NULL,
758 x_return_status => x_return_status,
759 x_msg_count => x_msg_count,
760 x_msg_data => x_msg_data
761 );
762
763 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
764 ROLLBACK TO update_supplier_address_sp;
765 RETURN;
766 END IF;
767
768 -- set fax for the address
769 pos_hz_contact_point_pkg.update_party_site_fax
770 (
771 p_party_site_id => p_party_site_id,
772 p_country_code => NULL,
773 p_area_code => p_fax_area_code ,
774 p_number => p_fax_number,
775 p_extension => NULL,
776 x_return_status => x_return_status,
777 x_msg_count => x_msg_count,
778 x_msg_data => x_msg_data
779 );
780
781 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
782 ROLLBACK TO update_supplier_address_sp;
783 RETURN;
784 END IF;
785
786 -- set email for the address
787 pos_hz_contact_point_pkg.update_party_site_email
788 (
789 p_party_site_id => p_party_site_id,
790 p_email => p_email_address,
791 x_return_status => x_return_status,
792 x_msg_count => x_msg_count,
793 x_msg_data => x_msg_data
794 );
795
796 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
797 ROLLBACK TO update_supplier_address_sp;
798 RETURN;
799 END IF;
800
801 handle_address_type
802 (p_party_site_id => p_party_site_id,
803 p_address_type => 'PAY',
804 p_value => p_pay_flag,
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 x_return_status <> fnd_api.g_ret_sts_success THEN
811 ROLLBACK TO update_supplier_address_sp;
812 RETURN;
813 END IF;
814
815 handle_address_type
816 (p_party_site_id => p_party_site_id,
817 p_address_type => 'PURCHASING',
818 p_value => p_pur_flag,
819 x_return_status => x_return_status,
820 x_msg_count => x_msg_count,
821 x_msg_data => x_msg_data
822 );
823
824 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
825 ROLLBACK TO update_supplier_address_sp;
826 RETURN;
827 END IF;
828
829 handle_address_type
830 (p_party_site_id => p_party_site_id,
831 p_address_type => 'RFQ',
832 p_value => p_rfq_flag,
833 x_return_status => x_return_status,
834 x_msg_count => x_msg_count,
835 x_msg_data => x_msg_data
836 );
837
838 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
839 ROLLBACK TO update_supplier_address_sp;
840 RETURN;
841 END IF;
842
843 -- logic for primary pay still need to be worked out
844 x_return_status := fnd_api.g_ret_sts_success;
845
846 END update_supplier_address;
847
848 PROCEDURE unassign_address_to_contact
849 (p_contact_party_id IN NUMBER,
850 p_org_party_site_id IN NUMBER,
851 p_vendor_id IN NUMBER,
852 x_return_status OUT nocopy VARCHAR2,
853 x_msg_count OUT nocopy NUMBER,
854 x_msg_data OUT nocopy VARCHAR2
855 )
856 IS
857
858 CURSOR l_cur IS
859 select ASCS.vendor_site_id, ASCS.relationship_id, ASCS.org_contact_id,
860 ASCS.rel_party_id, ASCS.party_site_id, ASCS.vendor_contact_id
861 from ap_supplier_contacts ASCS
862 where (ASCS.inactive_date is null OR ASCS.inactive_date > sysdate)
863 AND ASCS.org_party_site_id = p_org_party_site_id
864 AND ASCS.per_party_id = p_contact_party_id;
865
866 l_rec l_cur%ROWTYPE;
867 l_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
868
869 BEGIN
870
871 for l_rec in l_cur loop
872
873 l_vendor_contact_rec.vendor_site_id := l_rec.vendor_site_id;
874 l_vendor_contact_rec.per_party_id := p_contact_party_id;
875 l_vendor_contact_rec.relationship_id := l_rec.relationship_id;
876 l_vendor_contact_rec.rel_party_id := l_rec.rel_party_id;
877 l_vendor_contact_rec.org_party_site_id := p_org_party_site_id;
878 l_vendor_contact_rec.inactive_date := sysdate;
879 l_vendor_contact_rec.vendor_contact_id := l_rec.vendor_contact_id;
880 l_vendor_contact_rec.org_contact_id := l_rec.org_contact_id;
881 l_vendor_contact_rec.party_site_id := l_rec.party_site_id;
882
883 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
884 FND_LOG.string(fnd_log.level_statement, g_module,
885 ' Before Calling pos_vendor_pub_pkg.update_vendor_contact');
886 FND_LOG.string(fnd_log.level_statement, g_module,
887 ' per_party_id ' || p_contact_party_id);
888 FND_LOG.string(fnd_log.level_statement, g_module,
889 ' org_party_site_id ' || p_org_party_site_id);
890 FND_LOG.string(fnd_log.level_statement, g_module,
891 ' rel_party_id ' || l_rec.rel_party_id);
892 FND_LOG.string(fnd_log.level_statement, g_module,
893 ' relationship_id ' || l_rec.relationship_id);
894 FND_LOG.string(fnd_log.level_statement, g_module,
895 ' vendor_site_id ' || l_rec.vendor_site_id);
896 FND_LOG.string(fnd_log.level_statement, g_module,
897 ' org_contact_id ' || l_rec.org_contact_id);
898
899 END IF;
900
901 AP_VENDOR_PUB_PKG.Update_Vendor_Contact
902 (
903 p_api_version => 1.0,
904 p_init_msg_list => FND_API.G_TRUE,
905 p_commit => FND_API.G_FALSE,
906 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
907 p_vendor_contact_rec => l_vendor_contact_rec,
908 x_return_status => x_return_status,
909 x_msg_count => x_msg_count,
910 x_msg_data => x_msg_data
911 );
912
913 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
914 FND_LOG.string(fnd_log.level_statement, g_module,
915 ' After Calling pos_vendor_pub_pkg.update_vendor_contact');
916 FND_LOG.string(fnd_log.level_statement, g_module,
917 ' x_return_status ' || x_return_status);
918 FND_LOG.string(fnd_log.level_statement, g_module,
919 ' x_msg_count ' || x_msg_count);
920 FND_LOG.string(fnd_log.level_statement, g_module,
921 ' x_msg_data ' || x_msg_data);
922 END IF;
923
924 end loop;
925
926 x_return_status := 'S';
927 x_msg_data := null;
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 raise_application_error(-20020, 'Failure error status ' || x_return_status || x_msg_data || Sqlerrm, true);
932
933 END unassign_address_to_contact;
934
935 -- code added for bug 8237063
936 /* updating address details to ap_supplier_contacts
937 when only the contact related data is changed and the
938 address assignments are not added or deleted */
939 PROCEDURE update_address_to_contact
940 (p_contact_party_id IN NUMBER,
941 p_org_party_site_id IN NUMBER,
942 p_vendor_id IN NUMBER,
943 x_return_status OUT nocopy VARCHAR2,
944 x_msg_count OUT nocopy NUMBER,
945 x_msg_data OUT nocopy VARCHAR2
946 )
947 IS
948
949 CURSOR l_cur IS
950 select ASCS.vendor_site_id, ASCS.relationship_id, ASCS.org_contact_id,
951 ASCS.rel_party_id, ASCS.party_site_id, ASCS.vendor_contact_id, ASCS.inactive_date
952 from ap_supplier_contacts ASCS
953 where (ASCS.inactive_date is null OR ASCS.inactive_date > sysdate)
954 AND ASCS.org_party_site_id = p_org_party_site_id
955 AND ASCS.per_party_id = p_contact_party_id;
956
957 l_rec l_cur%ROWTYPE;
958 l_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
959
960 BEGIN
961
962 for l_rec in l_cur loop
963
964 l_vendor_contact_rec.vendor_site_id := l_rec.vendor_site_id;
965 l_vendor_contact_rec.per_party_id := p_contact_party_id;
966 l_vendor_contact_rec.relationship_id := l_rec.relationship_id;
967 l_vendor_contact_rec.rel_party_id := l_rec.rel_party_id;
968 l_vendor_contact_rec.org_party_site_id := p_org_party_site_id;
969 l_vendor_contact_rec.inactive_date := l_rec.inactive_date;
970 l_vendor_contact_rec.vendor_contact_id := l_rec.vendor_contact_id;
971 l_vendor_contact_rec.org_contact_id := l_rec.org_contact_id;
972 l_vendor_contact_rec.party_site_id := l_rec.party_site_id;
973
974 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
975 FND_LOG.string(fnd_log.level_statement, g_module,
976 ' Before Calling pos_vendor_pub_pkg.update_vendor_contact from update_address_to_contact');
977 FND_LOG.string(fnd_log.level_statement, g_module,
978 ' per_party_id ' || p_contact_party_id);
979 FND_LOG.string(fnd_log.level_statement, g_module,
980 ' org_party_site_id ' || p_org_party_site_id);
981 FND_LOG.string(fnd_log.level_statement, g_module,
982 ' rel_party_id ' || l_rec.rel_party_id);
983 FND_LOG.string(fnd_log.level_statement, g_module,
984 ' relationship_id ' || l_rec.relationship_id);
985 FND_LOG.string(fnd_log.level_statement, g_module,
986 ' vendor_site_id ' || l_rec.vendor_site_id);
987 FND_LOG.string(fnd_log.level_statement, g_module,
988 ' org_contact_id ' || l_rec.org_contact_id);
989
990 END IF;
991
992 AP_VENDOR_PUB_PKG.Update_Vendor_Contact
993 (
994 p_api_version => 1.0,
995 p_init_msg_list => FND_API.G_TRUE,
996 p_commit => FND_API.G_FALSE,
997 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
998 p_vendor_contact_rec => l_vendor_contact_rec,
999 x_return_status => x_return_status,
1000 x_msg_count => x_msg_count,
1001 x_msg_data => x_msg_data
1002 );
1003
1004 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1005 FND_LOG.string(fnd_log.level_statement, g_module,
1006 ' After Calling pos_vendor_pub_pkg.update_vendor_contact from update_address_to_contact');
1007 FND_LOG.string(fnd_log.level_statement, g_module,
1008 ' x_return_status ' || x_return_status);
1009 FND_LOG.string(fnd_log.level_statement, g_module,
1010 ' x_msg_count ' || x_msg_count);
1011 FND_LOG.string(fnd_log.level_statement, g_module,
1012 ' x_msg_data ' || x_msg_data);
1013 END IF;
1014
1015 end loop;
1016
1017 x_return_status := 'S';
1018 x_msg_data := null;
1019
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 raise_application_error(-20020, 'Failure error status ' || x_return_status || x_msg_data || Sqlerrm, true);
1023
1024 END update_address_to_contact;
1025 -- code added for bug 8237063
1026
1027 PROCEDURE assign_address_to_contact
1028 (p_contact_party_id IN NUMBER,
1029 p_org_party_site_id IN NUMBER,
1030 p_vendor_id IN NUMBER,
1031 x_attribute_category IN VARCHAR2 default null,
1032 x_attribute1 IN VARCHAR2 default null,
1033 x_attribute2 IN VARCHAR2 default null,
1034 x_attribute3 IN VARCHAR2 default null,
1035 x_attribute4 IN VARCHAR2 default null,
1036 x_attribute5 IN VARCHAR2 default null,
1037 x_attribute6 IN VARCHAR2 default null,
1038 x_attribute7 IN VARCHAR2 default null,
1039 x_attribute8 IN VARCHAR2 default null,
1040 x_attribute9 IN VARCHAR2 default null,
1041 x_attribute10 IN VARCHAR2 default null,
1042 x_attribute11 IN VARCHAR2 default null,
1043 x_attribute12 IN VARCHAR2 default null,
1044 x_attribute13 IN VARCHAR2 default null,
1045 x_attribute14 IN VARCHAR2 default null,
1046 x_attribute15 IN VARCHAR2 default null,
1047 x_return_status OUT nocopy VARCHAR2,
1048 x_msg_count OUT nocopy NUMBER,
1049 x_msg_data OUT nocopy VARCHAR2
1050 )
1051 IS
1052
1053 CURSOR l_cur IS
1054 SELECT 1
1055 FROM ap_supplier_contacts
1056 WHERE org_party_site_id = p_org_party_site_id
1057 AND per_party_id = p_contact_party_id
1058 AND (inactive_date is null OR inactive_date >= sysdate)
1059 AND rownum = 1;
1060
1061 l_number NUMBER;
1062
1063 l_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
1064
1065 /* Bug 6610366 , Checking the Status from hz_relationships as status column
1066 in hz_org_contacts is obsoleted in R12 ,Also checking for */
1067
1068 CURSOR l_cur3 IS
1069 SELECT hzr.relationship_id, hzr.party_id rel_party_id, hoc.org_contact_id
1070 FROM ap_suppliers pv, hz_relationships hzr, hz_org_contacts hoc
1071 WHERE pv.vendor_id = p_vendor_id
1072 AND hzr.relationship_type = 'CONTACT'
1073 AND hzr.relationship_code = 'CONTACT_OF'
1074 AND hzr.subject_id = p_contact_party_id
1075 AND hzr.subject_type = 'PERSON'
1076 AND hzr.subject_table_name = 'HZ_PARTIES'
1077 AND hzr.object_type = 'ORGANIZATION'
1078 AND hzr.object_table_name = 'HZ_PARTIES'
1079 AND hzr.object_id = pv.party_id
1080 AND hzr.status = 'A'
1081 AND trunc(SYSDATE) between Trunc(hzr.START_DATE) AND
1082 NVL(Trunc(hzr.END_DATE),trunc(SYSDATE + 1))
1083 AND hzr.relationship_id = hoc.party_relationship_id;
1084
1085 l_rec3 l_cur3%ROWTYPE;
1086 l_step NUMBER;
1087 l_vendor_contact_id NUMBER;
1088 l_per_party_id NUMBER;
1089 l_rel_party_id NUMBER;
1090 l_rel_id NUMBER;
1091 l_org_contact_id NUMBER;
1092 l_person_party_site_id NUMBER;
1093 BEGIN
1094
1095 l_step := 0;
1096
1097 OPEN l_cur;
1098 FETCH l_cur INTO l_number;
1099 IF l_cur%found THEN
1100 -- already has such assignment
1101 CLOSE l_cur;
1102 x_return_status := fnd_api.g_ret_sts_success;
1103 x_msg_count := 1;
1104 x_msg_data := NULL;
1105 RETURN;
1106 END IF;
1107
1108 l_step := 1;
1109
1110 OPEN l_cur3;
1111 FETCH l_cur3 INTO l_rec3;
1112 IF l_cur3%notfound THEN
1113 CLOSE l_cur3;
1114 x_return_status := fnd_api.g_ret_sts_error;
1115 x_msg_count := 1;
1116 x_msg_data := 'invalid supplier contact info';
1117 RETURN;
1118 END IF;
1119 CLOSE l_cur3;
1120
1121 l_step := 2;
1122
1123 l_vendor_contact_rec.vendor_site_id := null;
1124 l_vendor_contact_rec.per_party_id := p_contact_party_id;
1125 l_vendor_contact_rec.relationship_id := l_rec3.relationship_id;
1126 l_vendor_contact_rec.rel_party_id := l_rec3.rel_party_id;
1127 l_vendor_contact_rec.org_party_site_id := p_org_party_site_id;
1128 l_vendor_contact_rec.org_contact_id := l_rec3.org_contact_id;
1129
1130 /* Bug 12983048 - Start */
1131 l_vendor_contact_rec.vendor_id := p_vendor_id;
1132
1133 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1134 FND_LOG.string(fnd_log.level_statement, g_module,
1135 'Before Call to pos_vendor_pub_pkg.create_vendor_contact, vendor_id is : ' || l_vendor_contact_rec.vendor_id);
1136 END IF;
1137 /* Bug 12983048 - End */
1138
1139 /* Bug 6599374 Start */
1140
1141 l_vendor_contact_rec.attribute_category:=x_attribute_category;
1142 l_vendor_contact_rec.attribute1:=x_attribute1;
1143 l_vendor_contact_rec.attribute2:=x_attribute2;
1144 l_vendor_contact_rec.attribute3:=x_attribute3;
1145 l_vendor_contact_rec.attribute4:=x_attribute4;
1146 l_vendor_contact_rec.attribute5:=x_attribute5;
1147 l_vendor_contact_rec.attribute6:=x_attribute6;
1148 l_vendor_contact_rec.attribute7:=x_attribute7;
1149 l_vendor_contact_rec.attribute8:=x_attribute8;
1150 l_vendor_contact_rec.attribute9:=x_attribute9;
1151 l_vendor_contact_rec.attribute10:=x_attribute10;
1152 l_vendor_contact_rec.attribute11:=x_attribute11;
1153 l_vendor_contact_rec.attribute12:=x_attribute12;
1154 l_vendor_contact_rec.attribute13:=x_attribute13;
1155 l_vendor_contact_rec.attribute14:=x_attribute14;
1156 l_vendor_contact_rec.attribute15:=x_attribute15;
1157
1158 /* Bug 6599374 End */
1159
1160 l_step := 3;
1161
1162 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1163 FND_LOG.string(fnd_log.level_statement, g_module,
1164 ' Before Calling pos_vendor_pub_pkg.create_vendor_contact');
1165 FND_LOG.string(fnd_log.level_statement, g_module,
1166 ' per_party_id ' || p_contact_party_id);
1167 FND_LOG.string(fnd_log.level_statement, g_module,
1168 ' org_party_site_id ' || p_org_party_site_id);
1169 FND_LOG.string(fnd_log.level_statement, g_module,
1170 ' rel_party_id ' || l_rec3.rel_party_id);
1171 FND_LOG.string(fnd_log.level_statement, g_module,
1172 ' relationship_id ' || l_rec3.relationship_id);
1173 END IF;
1174
1175 pos_vendor_pub_pkg.create_vendor_contact
1176 ( p_vendor_contact_rec => l_vendor_contact_rec,
1177 x_return_status => x_return_status,
1178 x_msg_count => x_msg_count,
1179 x_msg_data => x_msg_data,
1180 x_vendor_contact_id => l_vendor_contact_id,
1181 x_per_party_id => l_per_party_id,
1182 x_rel_party_id => l_rel_party_id,
1183 x_rel_id => l_rel_id,
1184 x_org_contact_id => l_org_contact_id,
1185 x_party_site_id => l_person_party_site_id
1186 );
1187
1188 l_step := 4;
1189
1190 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1191 FND_LOG.string(fnd_log.level_statement, g_module,
1192 ' After Calling pos_vendor_pub_pkg.create_vendor_contact');
1193 FND_LOG.string(fnd_log.level_statement, g_module,
1194 ' x_return_status ' || x_return_status);
1195 FND_LOG.string(fnd_log.level_statement, g_module,
1196 ' x_msg_count ' || x_msg_count);
1197 FND_LOG.string(fnd_log.level_statement, g_module,
1198 ' x_msg_data ' || x_msg_data);
1199 END IF;
1200
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 raise_application_error(-20020, 'Failure at step ' || l_step || Sqlerrm, true);
1204 END assign_address_to_contact;
1205
1206 /* Bug 6599374 Start */
1207
1208 PROCEDURE update_address_assignment_dff
1209 (x_contact_party_id IN NUMBER,
1210 x_org_party_site_id IN NUMBER,
1211 x_vendor_id IN NUMBER,
1212 x_attribute_category IN VARCHAR2 default null,
1213 x_attribute1 IN VARCHAR2 default null,
1214 x_attribute2 IN VARCHAR2 default null,
1215 x_attribute3 IN VARCHAR2 default null,
1216 x_attribute4 IN VARCHAR2 default null,
1217 x_attribute5 IN VARCHAR2 default null,
1218 x_attribute6 IN VARCHAR2 default null,
1219 x_attribute7 IN VARCHAR2 default null,
1220 x_attribute8 IN VARCHAR2 default null,
1221 x_attribute9 IN VARCHAR2 default null,
1222 x_attribute10 IN VARCHAR2 default null,
1223 x_attribute11 IN VARCHAR2 default null,
1224 x_attribute12 IN VARCHAR2 default null,
1225 x_attribute13 IN VARCHAR2 default null,
1226 x_attribute14 IN VARCHAR2 default null,
1227 x_attribute15 IN VARCHAR2 default null,
1228 x_return_status OUT nocopy VARCHAR2,
1229 x_msg_count OUT nocopy NUMBER,
1230 x_msg_data OUT nocopy VARCHAR2
1231 ) IS
1232 BEGIN
1233
1234 /* Here We Need To Call AP Package For Updating The Address Assignments */
1235
1236 AP_VENDOR_PUB_PKG.Update_Address_Assignments_DFF
1237 (
1238 p_api_version => 1,
1239 p_init_msg_list => FND_API.G_FALSE,
1240 p_commit => FND_API.G_FALSE,
1241 p_contact_party_id => x_contact_party_id,
1242 p_org_party_site_id => x_org_party_site_id,
1243 p_attribute_category=> x_attribute_category,
1244 p_attribute1 => x_attribute1,
1245 p_attribute2 => x_attribute2,
1246 p_attribute3 => x_attribute3,
1247 p_attribute4 => x_attribute4,
1248 p_attribute5 => x_attribute5,
1249 p_attribute6 => x_attribute6,
1250 p_attribute7 => x_attribute7,
1251 p_attribute8 => x_attribute8,
1252 p_attribute9 => x_attribute9,
1253 p_attribute10 => x_attribute10,
1254 p_attribute11 => x_attribute11,
1255 p_attribute12 => x_attribute12,
1256 p_attribute13 => x_attribute13,
1257 p_attribute14 => x_attribute14,
1258 p_attribute15 => x_attribute15,
1259 x_return_status => x_return_status,
1260 x_msg_count => x_msg_count,
1261 x_msg_data => x_msg_data
1262 );
1263
1264 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1265 FND_LOG.string(fnd_log.level_statement, g_module,
1266 ' After Calling AP_VENDOR_PUB_PKG.Update_Address_Assignments_DFF ');
1267 FND_LOG.string(fnd_log.level_statement, g_module,
1268 ' x_return_status ' || x_return_status);
1269 FND_LOG.string(fnd_log.level_statement, g_module,
1270 ' x_msg_count ' || x_msg_count);
1271 FND_LOG.string(fnd_log.level_statement, g_module,
1272 ' x_msg_data ' || x_msg_data);
1273 END IF;
1274
1275 EXCEPTION
1276 WHEN OTHERS
1277 THEN
1278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1279
1280 END update_address_assignment_dff;
1281
1282 /* Bug 6599374 End */
1283
1284 -- This procedure is used by the new supplier UI in r12
1285 -- to update details such as site use flags, phone,
1286 -- fax, email, notes for a supplier address
1287 PROCEDURE buyer_update_address_details
1288 (p_party_site_id IN NUMBER,
1289 p_rfqFlag IN VARCHAR2,
1290 p_purFlag IN VARCHAR2,
1291 p_payFlag IN VARCHAR2,
1292 p_primaryPayFlag IN VARCHAR2,
1293 p_note IN VARCHAR2,
1294 p_phone_area_code IN VARCHAR2 DEFAULT NULL,
1295 p_phone IN VARCHAR2 DEFAULT NULL,
1296 p_phone_contact_id IN NUMBER DEFAULT NULL,
1297 p_phone_obj_ver_num IN NUMBER DEFAULT NULL,
1298 p_fax_area_code IN VARCHAR2 DEFAULT NULL,
1299 p_fax IN VARCHAR2 DEFAULT NULL,
1300 p_fax_contact_id IN NUMBER DEFAULT NULL,
1301 p_fax_obj_ver_num IN NUMBER DEFAULT NULL,
1302 p_email IN VARCHAR2 DEFAULT NULL,
1303 p_email_contact_id IN NUMBER DEFAULT NULL,
1304 p_email_obj_ver_num IN NUMBER DEFAULT NULL,
1305 x_return_status OUT NOCOPY VARCHAR2,
1306 x_msg_count OUT NOCOPY NUMBER,
1307 x_msg_data OUT NOCOPY VARCHAR2
1308 )
1309 IS
1310 l_status VARCHAR2(1);
1311 l_msg VARCHAR2(2000);
1312 BEGIN
1313 SAVEPOINT update_supplier_address_sp;
1314
1315 -- set phone for the address
1316 pos_hz_contact_point_pkg.update_party_site_phone
1317 (
1318 p_party_site_id => p_party_site_id,
1319 p_country_code => NULL,
1320 p_area_code => p_phone_area_code ,
1321 p_number => p_phone,
1322 p_extension => NULL,
1323 x_return_status => x_return_status,
1324 x_msg_count => x_msg_count,
1325 x_msg_data => x_msg_data
1326 );
1327
1328 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1329 ROLLBACK TO update_supplier_address_sp;
1330 RETURN;
1331 END IF;
1332
1333 -- set fax for the address
1334 pos_hz_contact_point_pkg.update_party_site_fax
1335 (
1336 p_party_site_id => p_party_site_id,
1337 p_country_code => NULL,
1338 p_area_code => p_fax_area_code ,
1339 p_number => p_fax,
1340 p_extension => NULL,
1341 x_return_status => x_return_status,
1342 x_msg_count => x_msg_count,
1343 x_msg_data => x_msg_data
1344 );
1345
1346 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1347 ROLLBACK TO update_supplier_address_sp;
1348 RETURN;
1349 END IF;
1350
1351 -- set email for the address
1352 pos_hz_contact_point_pkg.update_party_site_email
1353 (
1354 p_party_site_id => p_party_site_id,
1355 p_email => p_email,
1356 x_return_status => x_return_status,
1357 x_msg_count => x_msg_count,
1358 x_msg_data => x_msg_data
1359 );
1360
1361 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1362 ROLLBACK TO update_supplier_address_sp;
1363 RETURN;
1364 END IF;
1365
1366 handle_address_type
1367 (p_party_site_id => p_party_site_id,
1368 p_address_type => 'PAY',
1369 p_value => p_payflag,
1370 x_return_status => x_return_status,
1371 x_msg_count => x_msg_count,
1372 x_msg_data => x_msg_data
1373 );
1374
1375 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1376 ROLLBACK TO update_supplier_address_sp;
1377 RETURN;
1378 END IF;
1379
1380 handle_address_type
1381 (p_party_site_id => p_party_site_id,
1382 p_address_type => 'PURCHASING',
1383 p_value => p_purflag,
1384 x_return_status => x_return_status,
1385 x_msg_count => x_msg_count,
1386 x_msg_data => x_msg_data
1387 );
1388
1389 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1390 ROLLBACK TO update_supplier_address_sp;
1391 RETURN;
1392 END IF;
1393
1394 handle_address_type
1395 (p_party_site_id => p_party_site_id,
1396 p_address_type => 'RFQ',
1397 p_value => p_rfqflag,
1398 x_return_status => x_return_status,
1399 x_msg_count => x_msg_count,
1400 x_msg_data => x_msg_data
1401 );
1402
1403 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1404 ROLLBACK TO update_supplier_address_sp;
1405 RETURN;
1406 END IF;
1407
1408 -- logic for primary pay still need to be worked out
1409 pos_address_notes_pkg.update_note
1410 ( p_party_site_id => p_party_site_id,
1411 p_note => p_note,
1412 x_status => l_status,
1413 x_exception_msg => l_msg
1414 );
1415
1416 IF l_status IS NULL OR l_status <> 'S' THEN
1417 x_return_status := fnd_api.g_ret_sts_error;
1418 x_msg_data := l_msg;
1419 IF l_msg IS NOT NULL THEN
1420 x_msg_count := 1;
1421 ELSE
1422 x_msg_count := 0;
1423 END IF;
1424 ELSE
1425 x_return_status := fnd_api.g_ret_sts_success;
1426 END IF;
1427
1428 END buyer_update_address_details;
1429
1430
1431
1432 END pos_supplier_address_pkg;