[Home] [Help]
PACKAGE BODY: APPS.POS_VENDOR_REG_PKG
Source
1 PACKAGE BODY POS_VENDOR_REG_PKG AS
2 /* $Header: POSVREGB.pls 120.30.12010000.3 2008/11/11 14:27:13 suyjoshi ship $ */
3
4 g_module VARCHAR2(30) := 'POS_VENDOR_REG_PKG';
5
6 TYPE username_pwd_rec IS RECORD
7 (user_name fnd_user.user_name%TYPE,
8 password VARCHAR2(200),
9 exist_in_oid VARCHAR2(1)
10 );
11
12 TYPE username_pwd_tbl IS TABLE OF username_pwd_rec INDEX BY BINARY_INTEGER;
13
14 -- Note: this procedure will lock the supplier reg row
15 --
16 PROCEDURE lock_supplier_reg_row
17 (p_supplier_reg_id IN NUMBER,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 x_supplier_reg_rec OUT NOCOPY pos_supplier_registrations%ROWTYPE
22 )
23 IS
24 CURSOR l_cur IS
25 SELECT *
26 FROM pos_supplier_registrations
27 WHERE supplier_reg_id = p_supplier_reg_id FOR UPDATE;
28 BEGIN
29 OPEN l_cur;
30 FETCH l_cur INTO x_supplier_reg_rec;
31 IF l_cur%notfound THEN
32 CLOSE l_cur;
33 x_return_status := fnd_api.g_ret_sts_error;
34 fnd_message.set_name('POS','POS_SUPPLIER_REG_INVALID_ID');
35 fnd_message.set_token('SUPPLIER_REG_ID', p_supplier_reg_id);
36 fnd_msg_pub.ADD;
37
38 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
39 fnd_log.string
40 (fnd_log.level_error
41 , g_module || '.lock_supplier_reg_row'
42 , 'can not lock supplier reg row with id ' || p_supplier_reg_id);
43 END IF;
44 ELSE
45 FETCH l_cur INTO x_supplier_reg_rec;
46 CLOSE l_cur;
47 x_return_status := fnd_api.g_ret_sts_success;
48 END IF;
49
50 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
51
52 END lock_supplier_reg_row;
53
54 PROCEDURE check_bus_class
55 (p_supplier_reg_id IN NUMBER,
56 p_bus_class_code IN VARCHAR2,
57 x_found OUT nocopy VARCHAR2,
58 x_ext_attr_1 OUT nocopy VARCHAR2
59 )
60 IS
61 CURSOR l_cur IS
62 SELECT ext_attr_1
63 FROM pos_bus_class_reqs pbcr
64 , pos_supplier_mappings psm
65 , pos_supplier_registrations psr
66 WHERE psm.supplier_reg_id = psr.supplier_reg_id
67 AND psr.supplier_reg_id = p_supplier_reg_id
68 AND pbcr.mapping_id = psm.mapping_id
69 AND pbcr.request_type = 'ADD'
70 AND pbcr.request_status = 'PENDING'
71 AND pbcr.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
72 AND pbcr.lookup_code = p_bus_class_code;
73 BEGIN
74 x_found := 'N';
75
76 OPEN l_cur;
77 FETCH l_cur INTO x_ext_attr_1;
78 IF l_cur%found THEN
79 x_found := 'Y';
80 ELSE
81 x_found := 'N';
82 END IF;
83 CLOSE l_cur;
84 END check_bus_class;
85
86 PROCEDURE save_duns_number
87 (p_vendor_id IN NUMBER,
88 p_duns_number IN VARCHAR2,
89 x_return_status OUT nocopy VARCHAR2,
90 x_msg_count OUT nocopy NUMBER,
91 x_msg_data OUT nocopy VARCHAR2
92 )
93 IS
94 l_org_rec hz_party_v2pub.organization_rec_type;
95 l_party_rec hz_party_v2pub.party_rec_type;
96
97 CURSOR l_cur IS
98 SELECT party_id, object_version_number
99 FROM hz_parties
100 WHERE party_id =
101 (SELECT party_id
102 FROM ap_suppliers
103 WHERE vendor_id = p_vendor_id);
104
105 l_rec l_cur%ROWTYPE;
106 l_profile_id NUMBER;
107 BEGIN
108 IF p_duns_number IS NULL THEN
109 x_return_status := fnd_api.g_ret_sts_success;
110 RETURN;
111 END IF;
112
113 OPEN l_cur;
114 FETCH l_cur INTO l_rec;
115 IF l_cur%notfound THEN
116 CLOSE l_cur;
117 x_return_status := fnd_api.g_ret_sts_unexp_error;
118 x_msg_count := 1;
119 x_msg_data := 'can not find party for vendor ' || p_vendor_id;
120 RETURN;
121 END IF;
122 CLOSE l_cur;
123
124 l_party_rec.party_id := l_rec.party_id;
125 l_org_rec.party_rec := l_party_rec;
126 l_org_rec.duns_number_c := p_duns_number;
127
128 hz_party_v2pub.update_organization
129 (p_init_msg_list => fnd_api.g_false,
130 p_organization_rec => l_org_rec,
131 p_party_object_version_number => l_rec.object_version_number,
132 x_profile_id => l_profile_id,
133 x_return_status => x_return_status,
134 x_msg_count => x_msg_count,
135 x_msg_data => x_msg_data
136 );
137 END save_duns_number;
138
139 PROCEDURE create_vendor_and_party
140 (p_supplier_reg_rec IN pos_supplier_registrations%ROWTYPE,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2,
144 x_vendor_id OUT NOCOPY NUMBER,
145 x_party_id OUT NOCOPY NUMBER
146 )
147 IS
148 l_step VARCHAR2(100);
149 l_method VARCHAR2(100);
150 l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
151 l_found VARCHAR2(1);
152 l_ext_attr_1 pos_bus_class_reqs.ext_attr_1%TYPE;
153 CURSOR l_vendor_cur IS
154 SELECT vendor_id
155 FROM ap_suppliers
156 WHERE vendor_id = x_vendor_id;
157 l_number NUMBER;
158 /* Added for bug 7366321 */
159 l_hzprofile_value varchar2(20);
160 l_hzprofile_changed varchar2(1) := 'N';
161 /* End */
162
163 BEGIN
164 l_method := 'create_vendor_and_party';
165 x_return_status := fnd_api.g_ret_sts_error;
166 x_msg_count := 0;
167 x_msg_data := NULL;
168
169 FND_MSG_PUB.Count_And_Get(
170 p_count => x_msg_count,
171 p_data => x_msg_data
172 );
173
174 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
175 fnd_log.string
176 (fnd_log.level_procedure
177 , g_module || '.' || l_method
178 , 'start');
179 END IF;
180
181 l_step := 'set HZ_GENERATE_PARTY_NUMBER profile to Y';
182 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
183 fnd_log.string
184 (fnd_log.level_statement
185 , g_module || '.' || l_method
186 , l_step);
187 END IF;
188 /* Added for bug 7366321 */
189 l_hzprofile_value := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
190 if nvl(l_hzprofile_value, 'Y') = 'N' then
191 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'Y');
192 l_hzprofile_changed := 'Y';
193 end if;
194 /* End */
195
196 -- not sure if the next line is still necessary
197 -- generate party numbers from a sequence,
198 -- rather than having to supply them.
199 /* commented for bug 7366321
200 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER','Y');
201 */
202
203 l_step := 'fnd_msg_pub.initialize';
204 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
205 fnd_log.string
206 (fnd_log.level_statement
207 , g_module || '.' || l_method
208 , l_step);
209 END IF;
210
211 l_step := 'prepare l_vendor_rec';
212 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
213 fnd_log.string
214 (fnd_log.level_statement
215 , g_module || '.' || l_method
216 , l_step);
217 END IF;
218
219 l_vendor_rec.vendor_name := p_supplier_reg_rec.supplier_name;
220 l_vendor_rec.segment1 := p_supplier_reg_rec.supplier_number;
221 l_vendor_rec.jgzz_fiscal_code := p_supplier_reg_rec.taxpayer_id;
222 l_vendor_rec.tax_reference := p_supplier_reg_rec.tax_registration_number;
223 l_vendor_rec.start_date_active := Sysdate;
224
225 -- The following is commented as we are not sure if this is correct
226 -- IF p_supplier_reg_rec.taxpayer_id IS NOT NULL THEN
227 -- l_vendor_rec.federal_reportable_flag := 'Y';
228 -- END IF;
229
230 l_step := 'check minority group lookup code';
231 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
232 fnd_log.string
233 (fnd_log.level_statement
234 , g_module || '.' || l_method
235 , l_step);
236 END IF;
237
238 check_bus_class
239 ( p_supplier_reg_id => p_supplier_reg_rec.supplier_reg_id,
240 p_bus_class_code => 'MINORITY_OWNED',
241 x_found => l_found,
242 x_ext_attr_1 => l_ext_attr_1
243 );
244
245 IF l_found = 'Y' THEN
246 l_vendor_rec.minority_group_lookup_code := l_ext_attr_1;
247 END IF;
248
249 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
250 fnd_log.string
251 (fnd_log.level_statement
252 , g_module || '.' || l_method
253 , l_step || ' l_found is ' || l_found);
254 END IF;
255
256 l_step := 'check women owned';
257 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
258 fnd_log.string
259 (fnd_log.level_statement
260 , g_module || '.' || l_method
261 , l_step);
262 END IF;
263
264 check_bus_class
265 ( p_supplier_reg_id => p_supplier_reg_rec.supplier_reg_id,
266 p_bus_class_code => 'WOMEN_OWNED',
267 x_found => l_found,
268 x_ext_attr_1 => l_ext_attr_1
269 );
270
271 IF l_found = 'Y' THEN
272 l_vendor_rec.women_owned_flag := 'Y';
273 END IF;
274
275 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
276 fnd_log.string
277 (fnd_log.level_statement
278 , g_module || '.' || l_method
279 , l_step || ' l_found is ' || l_found);
280 END IF;
281
282 l_step := 'check small business';
283
284 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
285 fnd_log.string
286 (fnd_log.level_statement
287 , g_module || '.' || l_method
288 , l_step);
289 END IF;
290
291 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
292 fnd_log.string
293 (fnd_log.level_statement
294 , g_module || '.' || l_method
295 , l_step || ' l_found is ' || l_found);
296 END IF;
297
298 check_bus_class
299 ( p_supplier_reg_id => p_supplier_reg_rec.supplier_reg_id,
300 p_bus_class_code => 'SMALL_BUSINESS',
301 x_found => l_found,
302 x_ext_attr_1 => l_ext_attr_1
303 );
304
305 IF l_found = 'Y' THEN
306 l_vendor_rec.small_business_flag := 'Y';
307 END IF;
308
309 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
310 fnd_log.string
311 (fnd_log.level_statement
312 , g_module || '.' || l_method
313 , l_step || ' l_found is ' || l_found);
314 END IF;
315
316 l_step := 'call pos_vendor_pub_pkg.create_vendor';
317
318 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
319 fnd_log.string
320 (fnd_log.level_statement
321 , g_module || '.' || l_method
322 , l_step);
323 END IF;
324
325 pos_vendor_pub_pkg.create_vendor
326 ( p_vendor_rec => l_vendor_rec,
327 x_return_status => x_return_status,
328 x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data,
330 x_vendor_id => x_vendor_id,
331 x_party_id => x_party_id
332 );
333 /* Added for bug 7366321 */
334 if nvl(l_hzprofile_changed,'N') = 'Y' then
335 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
336 l_hzprofile_changed := 'N';
337 end if;
338 /* End */
339 IF x_vendor_id IS NULL THEN
340 raise_application_error(-20001, 'create_vendor returns NULL vendor_id, error msg: ' || x_msg_data, true);
341 END IF;
342
343 IF x_party_id IS NULL THEN
344 raise_application_error(-20001, 'create_vendor returns NULL party_id, error msg: ' || x_msg_data, true);
345 END IF;
346
347 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
348 fnd_log.string
349 (fnd_log.level_statement
350 , g_module || '.' || l_method
351 , 'create_vendor call result: x_return_status ' || x_return_status
352 || ' x_msg_count ' || x_msg_count
353 || ' x_msg_data ' || x_msg_data
354 );
355 END IF;
356
357 IF x_return_status = fnd_api.g_ret_sts_success THEN
358
359 OPEN l_vendor_cur;
360 FETCH l_vendor_cur INTO l_number;
361 IF l_vendor_cur%notfound THEN
362 CLOSE l_vendor_cur;
363 RAISE no_data_found;
364 END IF;
365 CLOSE l_vendor_cur;
366
367 -- save duns number collected during registration
368 -- since right now the vendor creation api does not take
369 -- duns number for vendor as input parameter
370 save_duns_number
371 (p_vendor_id => l_number,
372 p_duns_number => p_supplier_reg_rec.duns_number,
373 x_return_status => x_return_status,
374 x_msg_count => x_msg_count,
375 x_msg_data => x_msg_data
376 );
377
378 IF x_return_status IS NULL OR
379 x_return_status <> fnd_api.g_ret_sts_success THEN
380 RETURN;
381 END IF;
382
383 l_step := 'update pos_supplier_mappings with ids';
384 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
385 fnd_log.string
386 (fnd_log.level_statement
387 , g_module || '.' || l_method
388 , l_step || ' x_return_status ' || x_return_status
389 || ' x_msg_count ' || x_msg_count
390 || ' x_msg_data ' || x_msg_data
391 );
392 END IF;
393
394 UPDATE pos_supplier_mappings
395 SET vendor_id = x_vendor_id,
396 party_id = x_party_id,
397 last_updated_by = fnd_global.user_id,
398 last_update_date = Sysdate,
399 last_update_login = fnd_global.login_id
400 WHERE supplier_reg_id = p_supplier_reg_rec.supplier_reg_id;
401
402 l_step := 'update pos_supplier_registrations with ids';
403
404 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
405 fnd_log.string
406 (fnd_log.level_statement
407 , g_module || '.' || l_method
408 , l_step);
409 END IF;
410
411 UPDATE pos_supplier_registrations
412 SET registration_status = 'APPROVED',
413 po_vendor_id = x_vendor_id,
414 vendor_party_id = x_party_id,
415 last_updated_by = fnd_global.user_id,
416 last_update_date = Sysdate,
417 last_update_login = fnd_global.login_id
418 WHERE supplier_reg_id = p_supplier_reg_rec.supplier_reg_id;
419
420 x_return_status := fnd_api.g_ret_sts_success;
421
422 END IF;
423
424 EXCEPTION
425 WHEN OTHERS THEN
426
427 /* Added for bug 7366321 */
428 if nvl(l_hzprofile_changed,'N') = 'Y' then
429 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
430 l_hzprofile_changed := 'N';
431 end if;
432 /* End */
433
434 x_return_status := fnd_api.g_ret_sts_error;
435 raise_application_error (-20003, sqlerrm, true);
436
437 END create_vendor_and_party;
438
439 PROCEDURE create_supplier_addrs_sites
440 (p_supplier_reg_id IN NUMBER,
441 x_return_status OUT nocopy VARCHAR2,
442 x_msg_count OUT nocopy NUMBER,
443 x_msg_data OUT nocopy VARCHAR2
444 ) IS
445 CURSOR l_cur IS
446 SELECT par.address_request_id
447 FROM pos_address_requests par
448 , pos_supplier_mappings psm
449 WHERE par.request_type = 'ADD'
450 AND par.request_status = 'PENDING'
451 AND par.mapping_id = psm.mapping_id
452 AND psm.supplier_reg_id = p_supplier_reg_id;
453
454 BEGIN
455 FOR l_rec IN l_cur LOOP
456 pos_profile_change_request_pkg.approve_address_req
457 (p_request_id => l_rec.address_request_id,
458 x_return_status => x_return_status,
459 x_msg_count => x_msg_count,
460 x_msg_data => x_msg_data
461 );
462 IF x_return_status IS NULL OR
463 x_return_status <> fnd_api.g_ret_sts_success THEN
464 RETURN;
465 END IF;
466 END LOOP;
467
468 x_return_status := fnd_api.g_ret_sts_success;
469
470 END create_supplier_addrs_sites;
471
472 PROCEDURE create_supplier_contacts
473 (p_supplier_reg_id IN NUMBER,
474 x_return_status OUT nocopy VARCHAR2,
475 x_msg_count OUT nocopy NUMBER,
476 x_msg_data OUT nocopy VARCHAR2,
477 x_username_pwds OUT nocopy username_pwd_tbl
478 )
479 IS
480 CURSOR l_cur IS
481 SELECT pcr.contact_request_id, pcr.email_address, psm.vendor_id, create_user_account
482 FROM pos_contact_requests pcr
483 , pos_supplier_mappings psm
484 WHERE pcr.request_type = 'ADD'
485 AND pcr.request_status = 'PENDING'
486 AND pcr.mapping_id = psm.mapping_id
487 AND psm.supplier_reg_id = p_supplier_reg_id;
488
489 l_counter NUMBER;
490
491 CURSOR l_user_cur (p_user_name IN VARCHAR2) IS
492 SELECT user_id
493 FROM fnd_user
494 WHERE user_name = Upper(p_user_name);
495
496 l_user_id NUMBER;
497
498 CURSOR l_reg_type_cur IS
499 SELECT registration_type
500 FROM pos_supplier_registrations
501 WHERE supplier_reg_id = p_supplier_reg_id;
502
503 l_reg_type_rec l_reg_type_cur%ROWTYPE;
504
505 l_pon_def_also VARCHAR2(1);
506
507 l_temp_password VARCHAR2(200);
508
509 l_user_in_oid VARCHAR2(1);
510
511 BEGIN
512 l_counter := 0;
513
514 FOR l_rec IN l_cur LOOP
515
516 if (l_rec.create_user_account = 'Y' and
517 FND_USER_PKG.TestUserName(Upper(l_rec.email_address)) = FND_USER_PKG.USER_SYNCHED) then
518 l_user_in_oid := 'Y';
519 else
520 l_user_in_oid := 'N';
521 end if;
522
523 l_temp_password := NULL;
524
525 pos_profile_change_request_pkg.approve_contact_req
526 (p_request_id => l_rec.contact_request_id,
527 x_return_status => x_return_status,
528 x_msg_count => x_msg_count,
529 x_msg_data => x_msg_data,
530 x_password => l_temp_password
531 );
532
533 IF x_return_status IS NULL OR
534 x_return_status <> fnd_api.g_ret_sts_success THEN
535 RETURN;
536 END IF;
537
538 IF l_rec.create_user_account = 'Y' THEN
539 -- save the username and password to the return table
540 l_counter := l_counter + 1;
541 x_username_pwds(l_counter).user_name := Upper(l_rec.email_address);
542 x_username_pwds(l_counter).password := l_temp_password;
543 x_username_pwds(l_counter).exist_in_oid := l_user_in_oid;
544
545 -- assign default responsibilities for the new user account
546 OPEN l_user_cur (l_rec.email_address);
547 FETCH l_user_cur INTO l_user_id;
548
549 IF l_user_cur%notfound THEN
550 CLOSE l_user_cur;
551 ELSE
552 CLOSE l_user_cur;
553
554 l_pon_def_also := 'N';
555
556 OPEN l_reg_type_cur;
557 FETCH l_reg_type_cur INTO l_reg_type_rec;
558 IF l_reg_type_cur%found AND
559 l_reg_type_rec.registration_type = 'ONBOARD_SRC' THEN
560 l_pon_def_also := 'Y';
561 END IF;
562 CLOSE l_reg_type_cur;
563
564 pos_user_admin_pkg.assign_vendor_reg_def_resp
565 (p_user_id => l_user_id,
566 p_vendor_id => l_rec.vendor_id,
567 p_pon_def_also => l_pon_def_also,
568 x_return_status => x_return_status,
569 x_msg_count => x_msg_count,
570 x_msg_data => x_msg_data
571 );
572
573 IF x_return_status IS NULL OR
574 x_return_status <> fnd_api.g_ret_sts_success THEN
575 RETURN;
576 END IF;
577 END IF;
578 END IF;
579 END LOOP;
580
581 x_return_status := fnd_api.g_ret_sts_success;
582
583 END create_supplier_contacts;
584
585 PROCEDURE create_bus_class
586 (p_supplier_reg_id IN NUMBER,
587 x_return_status OUT nocopy VARCHAR2,
588 x_msg_count OUT nocopy NUMBER,
589 x_msg_data OUT nocopy VARCHAR2
590 )
591 IS
592 CURSOR l_cur IS
593 SELECT pbcr.bus_class_request_id
594 FROM pos_bus_class_reqs pbcr
595 , pos_supplier_mappings psm
596 WHERE pbcr.request_type = 'ADD'
597 AND pbcr.request_status = 'PENDING'
598 AND pbcr.mapping_id = psm.mapping_id
599 AND psm.supplier_reg_id = p_supplier_reg_id;
600
601 BEGIN
602
603 FOR l_rec IN l_cur LOOP
604 pos_profile_change_request_pkg.approve_bus_class_req
605 (p_request_id => l_rec.bus_class_request_id,
606 x_return_status => x_return_status,
607 x_msg_count => x_msg_count,
608 x_msg_data => x_msg_data
609 );
610 IF x_return_status IS NULL OR
611 x_return_status <> fnd_api.g_ret_sts_success THEN
612 RETURN;
613 END IF;
614 END LOOP;
615
616 x_return_status := fnd_api.g_ret_sts_success;
617
618 END create_bus_class;
619
620 PROCEDURE create_product_service
621 (p_supplier_reg_id IN NUMBER,
622 x_return_status OUT nocopy VARCHAR2,
623 x_msg_count OUT nocopy NUMBER,
624 x_msg_data OUT nocopy VARCHAR2
625 )
626 IS
627 CURSOR l_cur IS
628 SELECT ppsr.ps_request_id
629 FROM pos_product_service_requests ppsr
630 , pos_supplier_mappings psm
631 WHERE ppsr.request_type = 'ADD'
632 AND ppsr.request_status = 'PENDING'
633 AND ppsr.mapping_id = psm.mapping_id
634 AND psm.supplier_reg_id = p_supplier_reg_id;
635
636 BEGIN
637
638 FOR l_rec IN l_cur LOOP
639 pos_profile_change_request_pkg.approve_ps_req
640 (p_request_id => l_rec.ps_request_id,
641 x_return_status => x_return_status,
642 x_msg_count => x_msg_count,
643 x_msg_data => x_msg_data
644 );
645 IF x_return_status IS NULL OR
646 x_return_status <> fnd_api.g_ret_sts_success THEN
647 RETURN;
648 END IF;
649 END LOOP;
650
651 x_return_status := fnd_api.g_ret_sts_success;
652
653 END create_product_service;
654
655 PROCEDURE get_reg_primary_user
656 (p_supplier_reg_id IN NUMBER,
657 x_user_name OUT nocopy VARCHAR2,
658 x_user_id OUT nocopy NUMBER
659 )
660 IS
661 CURSOR l_cur IS
662 SELECT fu.user_name, fu.user_id
663 FROM pos_contact_requests pcr, fnd_user fu
664 WHERE pcr.mapping_id =
665 (SELECT mapping_id
666 FROM pos_supplier_mappings
667 WHERE supplier_reg_id = p_supplier_reg_id
668 )
669 AND pcr.request_status = 'APPROVED'
670 AND pcr.do_not_delete = 'Y'
671 AND fu.user_name = Upper(pcr.email_address);
672
673 BEGIN
674 OPEN l_cur;
675 FETCH l_cur INTO x_user_name, x_user_id;
676 CLOSE l_cur;
677 END get_reg_primary_user;
678
679
680 PROCEDURE notify_banking_approver
681 (p_vendor_id IN NUMBER,
682 x_return_status OUT nocopy VARCHAR2,
683 x_msg_count OUT nocopy NUMBER,
684 x_msg_data OUT nocopy VARCHAR2)
685 IS
686 l_itemtype wf_items.item_type%TYPE;
687 l_itemkey wf_items.item_key%TYPE;
688 l_receiver wf_roles.name%TYPE;
689 BEGIN
690
691 pos_spm_wf_pkg1.notify_bank_aprv_supp_aprv
692 (p_vendor_id => p_vendor_id,
693 x_itemtype => l_itemtype,
694 x_itemkey => l_itemkey,
695 x_receiver => l_receiver
696 ) ;
697 x_return_status := fnd_api.g_ret_sts_success;
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 x_return_status := fnd_api.g_ret_sts_unexp_error;
702 x_msg_count := 1;
703 x_msg_data := Sqlerrm;
704
705 END notify_banking_approver;
706
707 PROCEDURE notify_supplier_approved
708 (p_supplier_reg_id IN NUMBER,
709 p_username_pwds IN username_pwd_tbl,
710 x_return_status OUT nocopy VARCHAR2,
711 x_msg_count OUT nocopy NUMBER,
712 x_msg_data OUT nocopy VARCHAR2
713 )
714 IS
715 l_itemtype wf_items.item_type%TYPE;
716 l_itemkey wf_items.item_key%TYPE;
717 l_count NUMBER;
718 l_user_name fnd_user.user_name%TYPE;
719 l_user_id NUMBER;
720 BEGIN
721 get_reg_primary_user
722 (p_supplier_reg_id => p_supplier_reg_id,
723 x_user_name => l_user_name,
724 x_user_id => l_user_id
725 );
726 IF l_user_name IS NULL THEN
727 x_return_status := fnd_api.g_ret_sts_error;
728 x_msg_count := 1;
729 x_msg_data := 'can not find the primary supplier user';
730 RETURN;
731 END IF;
732
733 l_count := p_username_pwds.COUNT;
734 FOR l_index IN 1..l_count LOOP
735 IF p_username_pwds(l_index).user_name <> l_user_name THEN
736 if (p_username_pwds(l_index).exist_in_oid = 'Y') then
737 pos_spm_wf_pkg1.notify_user_approved_sso_sync
738 (p_supplier_reg_id => p_supplier_reg_id,
739 p_username => p_username_pwds(l_index).user_name,
740 x_itemtype => l_itemtype,
741 x_itemkey => l_itemkey
742 ) ;
743 else
744 pos_spm_wf_pkg1.notify_supplier_user_approved
745 (p_supplier_reg_id => p_supplier_reg_id,
746 p_username => p_username_pwds(l_index).user_name,
747 p_password => p_username_pwds(l_index).password,
748 x_itemtype => l_itemtype,
749 x_itemkey => l_itemkey
750 ) ;
751 end if;
752 ELSE
753 if (p_username_pwds(l_index).exist_in_oid = 'Y') then
754 pos_spm_wf_pkg1.notify_supplier_apprv_ssosync
755 (p_supplier_reg_id => p_supplier_reg_id,
756 p_username => p_username_pwds(l_index).user_name,
757 x_itemtype => l_itemtype,
758 x_itemkey => l_itemkey
759 ) ;
760 else
761 pos_spm_wf_pkg1.notify_supplier_approved
762 (p_supplier_reg_id => p_supplier_reg_id,
763 p_username => p_username_pwds(l_index).user_name,
764 p_password => p_username_pwds(l_index).password,
765 x_itemtype => l_itemtype,
766 x_itemkey => l_itemkey
767 ) ;
768 end if;
769 END IF;
770 END LOOP;
771
772 x_return_status := fnd_api.g_ret_sts_success;
773
774 EXCEPTION
775 WHEN OTHERS THEN
776 x_return_status := fnd_api.g_ret_sts_unexp_error;
777 x_msg_count := 1;
778 x_msg_data := Sqlerrm;
779
780 END notify_supplier_approved;
781
782 PROCEDURE approve_supplier_reg
783 (p_supplier_reg_id IN NUMBER,
784 x_return_status OUT NOCOPY VARCHAR2,
785 x_msg_count OUT NOCOPY NUMBER,
786 x_msg_data OUT NOCOPY VARCHAR2
787 )
788 IS
789 l_supplier_reg_rec pos_supplier_registrations%ROWTYPE;
790 l_step VARCHAR2(100);
791 l_method VARCHAR2(30);
792 l_vendor_id NUMBER;
793 l_vendor_party_id NUMBER;
794 l_username_pwds username_pwd_tbl;
795 l_user_name fnd_user.user_name%TYPE;
796 l_user_id NUMBER;
797
798 CURSOR l_ptp_cur(p_party_id IN NUMBER) IS
799 SELECT party_tax_profile_id
800 FROM zx_party_tax_profile
801 WHERE party_id = p_party_id
802 AND party_type_code = 'THIRD_PARTY'
803 AND ROWNUM = 1;
804
805 l_party_tax_profile_id NUMBER;
806 BEGIN
807
808 SAVEPOINT approve_supplier_reg;
809
810 l_method := 'approve_supplier';
811
812 x_return_status := fnd_api.g_ret_sts_error;
813
814 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
815 fnd_log.string
816 (fnd_log.level_procedure
817 , g_module || '.' || l_method
818 , 'start');
819 END IF;
820
821 l_step := 'lock supplier reg row';
822 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
823 fnd_log.string
824 (fnd_log.level_statement
825 , g_module || '.' || l_method
826 , l_step);
827 END IF;
828
829 lock_supplier_reg_row
830 (p_supplier_reg_id => p_supplier_reg_id,
831 x_return_status => x_return_status,
832 x_msg_count => x_msg_count,
833 x_msg_data => x_msg_data,
834 x_supplier_reg_rec => l_supplier_reg_rec
835 );
836
837 IF NOT (x_return_status IS NOT NULL
838 AND x_return_status = fnd_api.g_ret_sts_success) THEN
839 ROLLBACK TO approve_supplier_reg;
840 RETURN;
841 END IF;
842
843 l_step := 'check reg status';
844 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
845 fnd_log.string
846 (fnd_log.level_statement
847 , g_module || '.' || l_method
848 , l_step);
849 END IF;
850
851 IF l_supplier_reg_rec.registration_status IS NULL OR
852 l_supplier_reg_rec.registration_status NOT IN ('PENDING_APPROVAL','RIF_SUPPLIER')
853 THEN
854 x_return_status := fnd_api.g_ret_sts_error;
855 fnd_message.set_name('POS','POS_SUPPLIER_REG_NOT_PENDING');
856 fnd_msg_pub.ADD;
857 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
858
859 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
860 fnd_log.string
861 (fnd_log.level_error
862 , g_module || '.' || l_method
863 , 'status is not PENDING_APPROVAL for reg id ' || p_supplier_reg_id);
864 END IF;
865
866 ROLLBACK TO approve_supplier_reg;
867 RETURN;
868 END IF;
869
870 l_step := 'create vendor and party';
871 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
872 fnd_log.string
873 (fnd_log.level_statement
874 , g_module || '.' || l_method
875 , l_step);
876 END IF;
877
878 create_vendor_and_party
879 (p_supplier_reg_rec => l_supplier_reg_rec,
880 x_return_status => x_return_status,
881 x_msg_count => x_msg_count,
882 x_msg_data => x_msg_data,
883 x_vendor_id => l_vendor_id,
884 x_party_id => l_vendor_party_id
885 );
886
887 IF NOT (x_return_status IS NOT NULL
888 AND x_return_status = fnd_api.g_ret_sts_success) THEN
889 ROLLBACK TO approve_supplier_reg;
890 RETURN;
891 END IF;
892
893 l_supplier_reg_rec.po_vendor_id := l_vendor_id;
894 l_supplier_reg_rec.vendor_party_id := l_vendor_party_id;
895
896 l_step := 'create supplier addresses and vendor sites';
897 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
898 fnd_log.string
899 (fnd_log.level_statement
900 , g_module || '.' || l_method
901 , l_step);
902 END IF;
903
904 -- Call eTax API to save tax reg type and tax reg country to party tax profile
905 -- if they are collected during registration.
906 -- We are doing this because even though TCA party creation API
907 -- does create an party tax profile record for a new party,
908 -- it does not store tax reg country code and tax reg type
909 -- in the record as of 11/29/05 in r12 code line.
910
911 IF l_supplier_reg_rec.tax_reg_country_code IS NOT NULL OR
912 l_supplier_reg_rec.tax_reg_type IS NOT NULL OR
913 l_supplier_reg_rec.tax_registration_number IS NOT NULL THEN
914
915 OPEN l_ptp_cur(l_vendor_party_id);
916 FETCH l_ptp_cur INTO l_party_tax_profile_id;
917 IF l_ptp_cur%found THEN
918 CLOSE l_ptp_cur;
919
920 zx_party_tax_profile_pkg.update_row
921 (p_party_tax_profile_id => l_party_tax_profile_id,
922 p_collecting_authority_flag => NULL,
923 p_provider_type_code => NULL,
924 p_create_awt_dists_type_code => NULL,
925 p_create_awt_invoices_type_cod => NULL,
926 p_tax_classification_code => NULL,
927 p_self_assess_flag => NULL,
928 p_allow_offset_tax_flag => NULL,
929 p_rep_registration_number => l_supplier_reg_rec.tax_registration_number,
930 p_effective_from_use_le => NULL,
931 p_record_type_code => NULL,
932 p_request_id => NULL,
933 p_attribute1 => NULL,
934 p_attribute2 => NULL,
935 p_attribute3 => NULL,
936 p_attribute4 => NULL,
937 p_attribute5 => NULL,
938 p_attribute6 => NULL,
939 p_attribute7 => NULL,
940 p_attribute8 => NULL,
941 p_attribute9 => NULL,
942 p_attribute10 => NULL,
943 p_attribute11 => NULL,
944 p_attribute12 => NULL,
945 p_attribute13 => NULL,
946 p_attribute14 => NULL,
947 p_attribute15 => NULL,
948 p_attribute_category => NULL,
949 p_party_id => NULL,
950 p_program_login_id => NULL,
951 p_party_type_code => NULL,
952 p_supplier_flag => NULL,
953 p_customer_flag => NULL,
954 p_site_flag => NULL,
955 p_process_for_applicability_fl => NULL,
956 p_rounding_level_code => NULL,
957 p_rounding_rule_code => NULL,
958 p_withholding_start_date => NULL,
959 p_inclusive_tax_flag => NULL,
960 p_allow_awt_flag => NULL,
961 p_use_le_as_subscriber_flag => NULL,
962 p_legal_establishment_flag => NULL,
963 p_first_party_le_flag => NULL,
964 p_reporting_authority_flag => NULL,
965 x_return_status => x_return_status,
966 p_registration_type_code => l_supplier_reg_rec.tax_reg_type,
967 p_country_code => l_supplier_reg_rec.tax_reg_country_code
968 );
969 IF x_return_status IS NULL
970 OR x_return_status <> fnd_api.g_ret_sts_success THEN
971 ROLLBACK TO approve_supplier_reg;
972 x_msg_count := 1;
973 x_msg_data := 'call to zx_party_tax_profile_pkg.update_row failed';
974 RETURN;
975 END IF;
976 ELSE
977 CLOSE l_ptp_cur;
978 END IF;
979 END IF;
980
981 create_supplier_addrs_sites
982 (p_supplier_reg_id => l_supplier_reg_rec.supplier_reg_id,
983 x_return_status => x_return_status,
984 x_msg_count => x_msg_count,
985 x_msg_data => x_msg_data
986 );
987
988 IF x_return_status IS NULL
989 OR x_return_status <> fnd_api.g_ret_sts_success THEN
990 ROLLBACK TO approve_supplier_reg;
991 RETURN;
992 END IF;
993
994 l_step := 'create supplier contacts';
995 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
996 fnd_log.string
997 (fnd_log.level_statement
998 , g_module || '.' || l_method
999 , l_step);
1000 END IF;
1001
1002 create_supplier_contacts
1003 (p_supplier_reg_id => l_supplier_reg_rec.supplier_reg_id,
1004 x_return_status => x_return_status,
1005 x_msg_count => x_msg_count,
1006 x_msg_data => x_msg_data,
1007 x_username_pwds => l_username_pwds
1008 );
1009
1010 IF NOT (x_return_status IS NOT NULL
1011 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1012 ROLLBACK TO approve_supplier_reg;
1013 RETURN;
1014 END IF;
1015
1016 l_step := 'create supplier business classification';
1017 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1018 fnd_log.string
1019 (fnd_log.level_statement
1020 , g_module || '.' || l_method
1021 , l_step);
1022 END IF;
1023
1024 create_bus_class
1025 (p_supplier_reg_id => l_supplier_reg_rec.supplier_reg_id,
1026 x_return_status => x_return_status,
1027 x_msg_count => x_msg_count,
1028 x_msg_data => x_msg_data
1029 );
1030
1031 IF NOT (x_return_status IS NOT NULL
1032 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1033 ROLLBACK TO approve_supplier_reg;
1034 RETURN;
1035 END IF;
1036
1037 l_step := 'create product and services';
1038 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1039 fnd_log.string
1040 (fnd_log.level_statement
1041 , g_module || '.' || l_method
1042 , l_step);
1043 END IF;
1044
1045 create_product_service
1046 (p_supplier_reg_id => l_supplier_reg_rec.supplier_reg_id,
1047 x_return_status => x_return_status,
1048 x_msg_count => x_msg_count,
1049 x_msg_data => x_msg_data
1050 );
1051
1052 IF NOT (x_return_status IS NOT NULL
1053 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1054 ROLLBACK TO approve_supplier_reg;
1055 RETURN;
1056 END IF;
1057
1058 l_step := 'handle supplier survey';
1059 -- to be coded as part of supplier profile survey project
1060 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1061 fnd_log.string
1062 (fnd_log.level_statement
1063 , g_module || '.' || l_method
1064 , l_step);
1065 END IF;
1066
1067 l_step := 'notify supplier';
1068
1069 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1070 fnd_log.string
1071 (fnd_log.level_statement
1072 , g_module || '.' || l_method
1073 , l_step);
1074 END IF;
1075
1076 notify_supplier_approved
1077 (p_supplier_reg_id => p_supplier_reg_id,
1078 p_username_pwds => l_username_pwds,
1079 x_return_status => x_return_status,
1080 x_msg_count => x_msg_count,
1081 x_msg_data => x_msg_data
1082 );
1083
1084 IF NOT (x_return_status IS NOT NULL
1085 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1086 ROLLBACK TO approve_supplier_reg;
1087 RETURN;
1088 END IF;
1089
1090 -- Notify the banking approvers about the supplier approval
1091 -- so that they can review the supplier bank accounts. Bug 5299682
1092 notify_banking_approver
1093 (p_vendor_id => l_vendor_id,
1094 x_return_status => x_return_status,
1095 x_msg_count => x_msg_count,
1096 x_msg_data => x_msg_data
1097 );
1098
1099 get_reg_primary_user
1100 (p_supplier_reg_id => p_supplier_reg_id,
1101 x_user_name => l_user_name,
1102 x_user_id => l_user_id
1103 );
1104
1105 -- Code Added for Business Classification Re-Certification ER to update ap_suppliers table
1106 -- with the last certification date and last certified by values at the time of supplier approval.
1107
1108 update ap_suppliers
1109 set bus_class_last_certified_by = l_user_id,
1110 bus_class_last_certified_date = (select creation_date
1111 from pos_supplier_registrations
1112 where supplier_reg_id = p_supplier_reg_id ),
1113 last_updated_by = l_user_id,
1114 last_update_date = sysdate
1115
1116 where vendor_id=l_vendor_id;
1117
1118 -- End of Code added for Business Classification Re-Certification ER
1119
1120 pon_new_supplier_reg_pkg.src_pos_reg_supplier_callback
1121 ( x_return_status => x_return_status,
1122 x_msg_count => x_msg_count,
1123 x_msg_data => x_msg_data,
1124 p_requested_supplier_id => p_supplier_reg_id,
1125 p_po_vendor_id => l_vendor_id,
1126 p_supplier_hz_party_id => l_vendor_party_id,
1127 p_user_id => l_user_id
1128 );
1129
1130 IF NOT (x_return_status IS NOT NULL
1131 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1132 ROLLBACK TO approve_supplier_reg;
1133 RETURN;
1134 END IF;
1135
1136 x_return_status := fnd_api.g_ret_sts_success;
1137 x_msg_count := 0;
1138 x_msg_data := NULL;
1139
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 ROLLBACK TO approve_supplier_reg;
1143 raise_application_error (-20004, 'error in step ' || l_step ||
1144 ': ' || Sqlerrm, true);
1145
1146 END approve_supplier_reg;
1147
1148 --- note: when creating rfq only supplier, we will skip business
1149 --- classification and product and service
1150
1151 PROCEDURE reject_supplier_reg
1152 (p_supplier_reg_id IN NUMBER,
1153 x_return_status OUT NOCOPY VARCHAR2,
1154 x_msg_count OUT NOCOPY NUMBER,
1155 x_msg_data OUT NOCOPY VARCHAR2
1156 ) IS
1157 l_step VARCHAR2(100);
1158 l_method VARCHAR2(30);
1159 l_itemtype wf_items.item_type%TYPE;
1160 l_itemkey wf_items.item_key%TYPE;
1161 l_receiver fnd_user.user_name%TYPE;
1162
1163 l_supplier_reg_rec pos_supplier_registrations%ROWTYPE;
1164
1165 BEGIN
1166 l_method := 'reject_supplier_reg';
1167
1168 l_step := 'lock supplier reg row';
1169 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1170 fnd_log.string
1171 (fnd_log.level_statement
1172 , g_module || '.' || l_method
1173 , l_step);
1174 END IF;
1175
1176 lock_supplier_reg_row
1177 (p_supplier_reg_id => p_supplier_reg_id,
1178 x_return_status => x_return_status,
1179 x_msg_count => x_msg_count,
1180 x_msg_data => x_msg_data,
1181 x_supplier_reg_rec => l_supplier_reg_rec
1182 );
1183
1184 IF NOT (x_return_status IS NOT NULL
1185 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1186 RETURN;
1187 END IF;
1188
1189 l_step := 'check reg status';
1190 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1191 fnd_log.string
1192 (fnd_log.level_statement
1193 , g_module || '.' || l_method
1194 , l_step);
1195 END IF;
1196
1197 IF l_supplier_reg_rec.registration_status IS NULL OR
1198 l_supplier_reg_rec.registration_status <> 'PENDING_APPROVAL' THEN
1199 x_return_status := fnd_api.g_ret_sts_error;
1200 fnd_message.set_name('POS','POS_SUPPLIER_REG_NOT_PENDING');
1201 fnd_msg_pub.ADD;
1202 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1203
1204 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1205 fnd_log.string
1206 (fnd_log.level_error
1207 , g_module || '.' || l_method
1208 , 'status is not PENDING_APPROVAL for reg id ' || p_supplier_reg_id);
1209 END IF;
1210
1211 RETURN;
1212 END IF;
1213
1214 UPDATE pos_supplier_registrations
1215 SET registration_status = 'REJECTED',
1216 last_update_date = Sysdate,
1217 last_updated_by = fnd_global.user_id,
1218 last_update_login = fnd_global.login_id
1219 WHERE supplier_reg_id = p_supplier_reg_id;
1220
1221 UPDATE pos_address_requests
1222 SET request_status = 'REJECTED',
1223 last_update_date = Sysdate,
1224 last_updated_by = fnd_global.user_id,
1225 last_update_login = fnd_global.login_id
1226 WHERE mapping_id =
1227 (SELECT mapping_id FROM pos_supplier_mappings
1228 WHERE supplier_reg_id = p_supplier_reg_id)
1229 AND request_status = 'PENDING';
1230
1231 UPDATE pos_contact_requests
1232 SET request_status = 'REJECTED',
1233 last_update_date = Sysdate,
1234 last_updated_by = fnd_global.user_id,
1235 last_update_login = fnd_global.login_id
1236 WHERE mapping_id =
1237 (SELECT mapping_id FROM pos_supplier_mappings
1238 WHERE supplier_reg_id = p_supplier_reg_id)
1239 AND request_status = 'PENDING';
1240
1241 UPDATE pos_cont_addr_requests
1242 SET request_status = 'REJECTED',
1243 last_update_date = Sysdate,
1244 last_updated_by = fnd_global.user_id,
1245 last_update_login = fnd_global.login_id
1246 WHERE mapping_id =
1247 (SELECT mapping_id FROM pos_supplier_mappings
1248 WHERE supplier_reg_id = p_supplier_reg_id)
1249 AND request_status = 'PENDING';
1250
1251 pos_spm_wf_pkg1.notify_supplier_rejected
1252 (p_supplier_reg_id => p_supplier_reg_id,
1253 x_itemtype => l_itemtype,
1254 x_itemkey => l_itemkey,
1255 x_receiver => l_receiver
1256 );
1257
1258 x_return_status := fnd_api.g_ret_sts_success;
1259 x_msg_count := 0;
1260 x_msg_data := NULL;
1261
1262 END reject_supplier_reg;
1263
1264 PROCEDURE submit_supplier_reg
1265 (p_supplier_reg_id IN NUMBER,
1266 x_return_status OUT NOCOPY VARCHAR2,
1267 x_msg_count OUT NOCOPY NUMBER,
1268 x_msg_data OUT NOCOPY VARCHAR2
1269 ) IS
1270
1271 l_step VARCHAR2(100);
1272 l_method VARCHAR2(30);
1273 l_supplier_reg_rec pos_supplier_registrations%ROWTYPE;
1274 BEGIN
1275 l_method := 'submit_supplier_reg';
1276
1277 l_step := 'lock supplier reg row';
1278 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1279 fnd_log.string
1280 (fnd_log.level_statement
1281 , g_module || '.' || l_method
1282 , l_step);
1283 END IF;
1284
1285 lock_supplier_reg_row
1286 (p_supplier_reg_id => p_supplier_reg_id,
1287 x_return_status => x_return_status,
1288 x_msg_count => x_msg_count,
1289 x_msg_data => x_msg_data,
1290 x_supplier_reg_rec => l_supplier_reg_rec
1291 );
1292
1293 IF x_return_status IS NULL
1294 OR x_return_status <> fnd_api.g_ret_sts_success THEN
1295 RETURN;
1296 END IF;
1297
1298 l_step := 'check reg status';
1299 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1300 fnd_log.string
1301 (fnd_log.level_statement
1302 , g_module || '.' || l_method
1303 , l_step);
1304 END IF;
1305
1306 IF l_supplier_reg_rec.registration_status IS NULL OR
1307 (l_supplier_reg_rec.registration_status <> 'DRAFT' AND
1308 l_supplier_reg_rec.registration_status <> 'RIF_SUPPLIER')
1309 THEN
1310 x_return_status := fnd_api.g_ret_sts_error;
1311 fnd_message.set_name('POS','POS_SUPPLIER_REG_NOT_DRAFT');
1312 fnd_msg_pub.ADD;
1313 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1314
1315 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1316 fnd_log.string
1317 (fnd_log.level_error
1318 , g_module || '.' || l_method
1319 , 'status is not DRAFT for reg id ' || p_supplier_reg_id);
1320 END IF;
1321
1322 RETURN;
1323 END IF;
1324
1325 UPDATE pos_supplier_registrations
1326 SET registration_status = 'PENDING_APPROVAL',
1327 last_update_date = Sysdate,
1328 last_updated_by = fnd_global.user_id,
1329 last_update_login = fnd_global.login_id
1330 WHERE supplier_reg_id = p_supplier_reg_id;
1331
1332 x_return_status := fnd_api.g_ret_sts_success;
1333 x_msg_count := 0;
1334 x_msg_data := NULL;
1335
1336 END submit_supplier_reg;
1337
1338 PROCEDURE send_save_for_later_ntf
1339 (p_supplier_reg_id IN NUMBER,
1340 p_email_address IN VARCHAR2,
1341 x_return_status OUT NOCOPY VARCHAR2,
1342 x_msg_count OUT NOCOPY NUMBER,
1343 x_msg_data OUT NOCOPY VARCHAR2
1344 ) IS
1345 BEGIN
1346
1347 pos_spm_wf_pkg1.send_supplier_reg_saved_ntf
1348 (p_supplier_reg_id => p_supplier_reg_id);
1349
1350 x_return_status := fnd_api.g_ret_sts_success;
1351 x_msg_count := 0;
1352 x_msg_data := NULL;
1353
1354 END send_save_for_later_ntf;
1355
1356 FUNCTION is_ou_id_valid
1357 (p_ou_id IN NUMBER
1358 ) RETURN VARCHAR2
1359 IS
1360
1361 ln_dup hr_operating_units.organization_id%TYPE;
1362
1363 CURSOR hou_cur IS
1364 SELECT organization_id
1365 FROM hr_operating_units
1366 WHERE organization_id = p_ou_id
1367 AND ( date_to IS NULL OR
1368 ( date_to > sysdate AND date_to > date_from ) );
1369
1370 BEGIN
1371
1372 OPEN hou_cur;
1373 FETCH hou_cur INTO ln_dup;
1374 IF hou_cur%FOUND THEN
1375 CLOSE hou_cur;
1376 RETURN 'Y';
1377 END IF;
1378 CLOSE hou_cur;
1379
1380 RETURN 'N';
1381
1382 END;
1383
1384 FUNCTION is_supplier_number_unique
1385 (p_supp_regid IN NUMBER,
1386 p_supp_number IN VARCHAR2
1387 ) RETURN VARCHAR2
1388 IS
1389 BEGIN
1390
1391 IF p_supp_number IS NULL THEN
1392 RETURN 'N';
1393 END IF;
1394
1395 FOR x IN (SELECT supplier_number
1396 FROM pos_supplier_registrations
1397 WHERE supplier_reg_id <> p_supp_regid
1398 --AND registration_status <> 'REJECTED'
1399 -- the unique key POS_SUPPLIER_REG_U2 is on supplier_number only
1400 -- without considering the registration_status
1401 AND p_supp_number = supplier_number
1402 AND ROWNUM < 2
1403 )
1404 LOOP
1405 RETURN 'N';
1406 END LOOP;
1407
1408 FOR x IN (SELECT segment1
1409 FROM ap_suppliers
1410 WHERE segment1 = p_supp_number
1411 AND ROWNUM < 2
1412 )
1413 LOOP
1414 RETURN 'N';
1415 END LOOP;
1416
1417 RETURN 'Y';
1418
1419 END is_supplier_number_unique;
1420
1421
1422 PROCEDURE is_taxpayer_id_unique(
1423 p_supp_regid IN NUMBER
1424 , p_taxpayer_id IN VARCHAR2
1425 , p_country IN VARCHAR2
1426 , x_is_unique OUT NOCOPY VARCHAR2
1427 , x_vendor_id OUT NOCOPY NUMBER
1428 )
1429 IS
1430
1431 CURSOR supp_reg_cur IS
1432 SELECT -1
1433 FROM pos_supplier_registrations psr
1434 WHERE psr.supplier_reg_id <> p_supp_regid
1435 AND psr.taxpayer_id = p_taxpayer_id
1436 AND ((psr.tax_reg_country_code is not null and p_country is not null and psr.tax_reg_country_code = p_country) OR
1437 (p_country is null))
1438 AND psr.registration_status = 'PENDING_APPROVAL';
1439
1440 CURSOR po_vendors_cur IS
1441 SELECT pv.vendor_id
1442 FROM ap_suppliers pv, zx_party_tax_profile zxpr
1443 WHERE pv.num_1099 = p_taxpayer_id
1444 AND zxpr.party_id = pv.party_id
1445 AND ((zxpr.country_code is not null and p_country is not null and zxpr.country_code = p_country) OR
1446 (p_country is null));
1447 BEGIN
1448
1449 IF p_taxpayer_id IS NULL THEN
1450 x_is_unique := 'Y';
1451 x_vendor_id := -1;
1452 RETURN;
1453 END IF;
1454
1455 OPEN supp_reg_cur;
1456 FETCH supp_reg_cur INTO x_vendor_id;
1457 IF supp_reg_cur%FOUND THEN
1458 CLOSE supp_reg_cur;
1459 x_is_unique := 'N';
1460 RETURN;
1461 END IF;
1462 CLOSE supp_reg_cur;
1463
1464 OPEN po_vendors_cur;
1465 FETCH po_vendors_cur INTO x_vendor_id;
1466 IF po_vendors_cur%FOUND THEN
1467 CLOSE po_vendors_cur;
1468 x_is_unique := 'N';
1469 RETURN;
1470 END IF;
1471 CLOSE po_vendors_cur;
1472
1473 x_is_unique := 'Y';
1474 x_vendor_id := -1;
1475 END is_taxpayer_id_unique;
1476
1477 PROCEDURE is_duns_num_unique(
1478 p_supp_regid IN NUMBER
1479 , p_duns_num IN VARCHAR2
1480 , x_is_unique OUT NOCOPY VARCHAR2
1481 , x_vendor_id OUT NOCOPY NUMBER
1482 )
1483 IS
1484
1485 l_party_id hz_parties.party_id%TYPE := -1;
1486
1487 CURSOR supp_reg_cur IS
1488 SELECT -1
1489 FROM pos_supplier_registrations psr
1490 WHERE psr.supplier_reg_id <> p_supp_regid
1491 AND psr.duns_number = p_duns_num
1492 AND psr.registration_status = 'PENDING_APPROVAL';
1493
1494 CURSOR hz_cur IS
1495 SELECT party_id
1496 FROM hz_parties
1497 WHERE duns_number_c = p_duns_num
1498 AND party_type = 'ORGANIZATION';
1499
1500 CURSOR po_vendors_cur ( p_party_id hz_parties.party_id%TYPE ) IS
1501 SELECT vendor_id
1502 FROM ap_suppliers
1503 WHERE party_id = p_party_id;
1504
1505 BEGIN
1506
1507 IF p_duns_num IS NULL THEN
1508 x_is_unique := 'Y';
1509 x_vendor_id := -1;
1510 RETURN;
1511 END IF;
1512
1513 OPEN supp_reg_cur;
1514 FETCH supp_reg_cur INTO x_vendor_id;
1515 IF supp_reg_cur%FOUND THEN
1516 CLOSE supp_reg_cur;
1517 x_is_unique := 'N';
1518 RETURN;
1519 END IF;
1520 CLOSE supp_reg_cur;
1521
1522 OPEN hz_cur;
1523 FETCH hz_cur INTO l_party_id;
1524 IF hz_cur%NOTFOUND THEN
1525 CLOSE hz_cur;
1526 x_is_unique := 'Y';
1527 x_vendor_id := -1;
1528 RETURN;
1529 END IF;
1530 CLOSE hz_cur;
1531
1532 OPEN po_vendors_cur(l_party_id);
1533 FETCH po_vendors_cur INTO x_vendor_id;
1534 IF po_vendors_cur%NOTFOUND THEN
1535 x_vendor_id := -1;
1536 END IF;
1537 CLOSE po_vendors_cur;
1538 x_is_unique := 'N';
1539 RETURN;
1540
1541 END is_duns_num_unique;
1542
1543 PROCEDURE is_taxregnum_unique(
1544 p_supp_regid IN NUMBER
1545 , p_taxreg_num IN VARCHAR2
1546 , p_country IN VARCHAR2
1547 , x_is_unique OUT NOCOPY VARCHAR2
1548 , x_vendor_id OUT NOCOPY NUMBER
1549 )
1550 IS
1551
1552 CURSOR supp_reg_cur IS
1553 SELECT -1
1554 FROM pos_supplier_registrations psr
1555 WHERE psr.supplier_reg_id <> p_supp_regid
1556 AND psr.tax_registration_number = p_taxreg_num
1557 AND ((psr.tax_reg_country_code is not null and p_country is not null and psr.tax_reg_country_code = p_country) OR
1558 (p_country is null))
1559 AND psr.registration_status = 'PENDING_APPROVAL';
1560
1561 CURSOR po_vendors_cur IS
1562 SELECT pv.vendor_id
1563 FROM ap_suppliers pv, zx_party_tax_profile zxpr
1564 WHERE zxpr.party_id = pv.party_id
1565 AND zxpr.rep_registration_number = p_taxreg_num
1566 AND ((zxpr.country_code is not null and p_country is not null and zxpr.country_code = p_country) OR
1567 (p_country is null));
1568
1569 CURSOR po_vendor_sites_cur IS
1570 SELECT pvsa.vendor_id
1571 FROM ap_supplier_sites_all pvsa, zx_party_tax_profile zxpr
1572 WHERE zxpr.rep_registration_number = p_taxreg_num
1573 AND zxpr.site_flag = 'Y'
1574 AND zxpr.party_id = pvsa.party_site_id
1575 AND ((zxpr.country_code is not null and p_country is not null and zxpr.country_code = p_country) OR
1576 (p_country is null));
1577 BEGIN
1578
1579 IF p_taxreg_num IS NULL THEN
1580 x_is_unique := 'Y';
1581 x_vendor_id := -1;
1582 RETURN;
1583 END IF;
1584
1585 OPEN supp_reg_cur;
1586 FETCH supp_reg_cur INTO x_vendor_id;
1587 IF supp_reg_cur%FOUND THEN
1588 CLOSE supp_reg_cur;
1589 x_is_unique := 'N';
1590 RETURN;
1591 END IF;
1592 CLOSE supp_reg_cur;
1593
1594 OPEN po_vendors_cur;
1595 FETCH po_vendors_cur INTO x_vendor_id;
1596 IF po_vendors_cur%FOUND THEN
1597 CLOSE po_vendors_cur;
1598 x_is_unique := 'N';
1599 RETURN;
1600 END IF;
1601 CLOSE po_vendors_cur;
1602
1603 OPEN po_vendor_sites_cur;
1604 FETCH po_vendor_sites_cur INTO x_vendor_id;
1605 IF po_vendor_sites_cur%FOUND THEN
1606 CLOSE po_vendor_sites_cur;
1607 x_is_unique := 'N';
1608 RETURN;
1609 END IF;
1610 CLOSE po_vendor_sites_cur;
1611
1612 x_is_unique := 'Y';
1613 x_vendor_id := -1;
1614 END is_taxregnum_unique;
1615
1616 END POS_VENDOR_REG_PKG;