[Home] [Help]
PACKAGE BODY: APPS.POS_VENDOR_REG_PKG
Source
1 PACKAGE BODY POS_VENDOR_REG_PKG AS
2 /* $Header: POSVREGB.pls 120.47.12020000.3 2013/03/20 00:14:23 atjen 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 /* Begin Supplier Hub: Supplier Management
226 Added code to pass the party id from Supplier reg table
227 to Vendor Record. This is to avoid creation of party
228 again during the Reg approval time.
229 As per the Supplier hub changes, a party will be created by
230 Buyer Admin during Registration review */
231
232 l_vendor_rec.party_id := p_supplier_reg_rec.vendor_party_id;
233
234 /* End Supplier Hub: Supplier Management */
235
236 /* Begin Supplier Hub: Bug 11071248
237 * The following 4 fields are introduced for supplier registration in
238 * Supplier Hub, and need to be copied to suppliers table during approval.
239 */
240
241 l_vendor_rec.vendor_name_alt := p_supplier_reg_rec.supplier_name_alt;
242 l_vendor_rec.vendor_type_lookup_code := p_supplier_reg_rec.supplier_type;
243 l_vendor_rec.sic_code := p_supplier_reg_rec.standard_industry_class;
244 l_vendor_rec.ni_number := p_supplier_reg_rec.ni_number;
245
246 /* End Supplier Hub: Bug 11071248 */
247
248 -- The following is commented as we are not sure if this is correct
249 -- IF p_supplier_reg_rec.taxpayer_id IS NOT NULL THEN
250 -- l_vendor_rec.federal_reportable_flag := 'Y';
251 -- END IF;
252
253 l_step := 'check minority group lookup code';
254 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
255 fnd_log.string
256 (fnd_log.level_statement
257 , g_module || '.' || l_method
258 , l_step);
259 END IF;
260
261 check_bus_class
262 ( p_supplier_reg_id => p_supplier_reg_rec.supplier_reg_id,
263 p_bus_class_code => 'MINORITY_OWNED',
264 x_found => l_found,
265 x_ext_attr_1 => l_ext_attr_1
266 );
267
268 IF l_found = 'Y' THEN
269 l_vendor_rec.minority_group_lookup_code := l_ext_attr_1;
270 END IF;
271
272 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
273 fnd_log.string
274 (fnd_log.level_statement
275 , g_module || '.' || l_method
276 , l_step || ' l_found is ' || l_found);
277 END IF;
278
279 l_step := 'check women owned';
280 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
281 fnd_log.string
282 (fnd_log.level_statement
283 , g_module || '.' || l_method
284 , l_step);
285 END IF;
286
287 check_bus_class
288 ( p_supplier_reg_id => p_supplier_reg_rec.supplier_reg_id,
289 p_bus_class_code => 'WOMEN_OWNED',
290 x_found => l_found,
291 x_ext_attr_1 => l_ext_attr_1
292 );
293
294 IF l_found = 'Y' THEN
295 l_vendor_rec.women_owned_flag := 'Y';
296 END IF;
297
298 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
299 fnd_log.string
300 (fnd_log.level_statement
301 , g_module || '.' || l_method
302 , l_step || ' l_found is ' || l_found);
303 END IF;
304
305 l_step := 'check small business';
306
307 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
308 fnd_log.string
309 (fnd_log.level_statement
310 , g_module || '.' || l_method
311 , l_step);
312 END IF;
313
314 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
315 fnd_log.string
316 (fnd_log.level_statement
317 , g_module || '.' || l_method
318 , l_step || ' l_found is ' || l_found);
319 END IF;
320
321 check_bus_class
322 ( p_supplier_reg_id => p_supplier_reg_rec.supplier_reg_id,
323 p_bus_class_code => 'SMALL_BUSINESS',
324 x_found => l_found,
325 x_ext_attr_1 => l_ext_attr_1
326 );
327
328 IF l_found = 'Y' THEN
329 l_vendor_rec.small_business_flag := 'Y';
330 END IF;
331
332 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
333 fnd_log.string
334 (fnd_log.level_statement
335 , g_module || '.' || l_method
336 , l_step || ' l_found is ' || l_found);
337 END IF;
338
339 l_step := 'call pos_vendor_pub_pkg.create_vendor';
340
341 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
342 fnd_log.string
343 (fnd_log.level_statement
344 , g_module || '.' || l_method
345 , l_step);
346 END IF;
347
348 pos_vendor_pub_pkg.create_vendor
349 ( p_vendor_rec => l_vendor_rec,
350 x_return_status => x_return_status,
351 x_msg_count => x_msg_count,
352 x_msg_data => x_msg_data,
353 x_vendor_id => x_vendor_id,
354 x_party_id => x_party_id
355 );
356 /* Added for bug 7366321 */
357 if nvl(l_hzprofile_changed,'N') = 'Y' then
358 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
359 l_hzprofile_changed := 'N';
360 end if;
361 /* End */
362
363 IF x_return_status IS NULL OR
364 x_return_status <> fnd_api.g_ret_sts_success THEN
365 RETURN;
366 END IF;
367
368 IF x_vendor_id IS NULL THEN
369 raise_application_error(-20001, 'create_vendor returns NULL vendor_id, error msg: ' || x_msg_data, true);
370 END IF;
371
372 IF x_party_id IS NULL THEN
373 raise_application_error(-20001, 'create_vendor returns NULL party_id, error msg: ' || x_msg_data, true);
374 END IF;
375
376 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
377 fnd_log.string
378 (fnd_log.level_statement
379 , g_module || '.' || l_method
380 , 'create_vendor call result: x_return_status ' || x_return_status
381 || ' x_msg_count ' || x_msg_count
382 || ' x_msg_data ' || x_msg_data
383 );
384 END IF;
385
386 IF x_return_status = fnd_api.g_ret_sts_success THEN
387
388 OPEN l_vendor_cur;
389 FETCH l_vendor_cur INTO l_number;
390 IF l_vendor_cur%notfound THEN
391 CLOSE l_vendor_cur;
392 RAISE no_data_found;
393 END IF;
394 CLOSE l_vendor_cur;
395
396 -- save duns number collected during registration
397 -- since right now the vendor creation api does not take
398 -- duns number for vendor as input parameter
399 save_duns_number
400 (p_vendor_id => l_number,
401 p_duns_number => p_supplier_reg_rec.duns_number,
402 x_return_status => x_return_status,
403 x_msg_count => x_msg_count,
404 x_msg_data => x_msg_data
405 );
406
407 IF x_return_status IS NULL OR
408 x_return_status <> fnd_api.g_ret_sts_success THEN
409 RETURN;
410 END IF;
411
412 l_step := 'update pos_supplier_mappings with ids';
413 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
414 fnd_log.string
415 (fnd_log.level_statement
416 , g_module || '.' || l_method
417 , l_step || ' x_return_status ' || x_return_status
418 || ' x_msg_count ' || x_msg_count
419 || ' x_msg_data ' || x_msg_data
420 );
421 END IF;
422
423 UPDATE pos_supplier_mappings
424 SET vendor_id = x_vendor_id,
425 party_id = x_party_id,
426 last_updated_by = fnd_global.user_id,
427 last_update_date = Sysdate,
428 last_update_login = fnd_global.login_id
429 WHERE supplier_reg_id = p_supplier_reg_rec.supplier_reg_id;
430
431
432 /* Begin Supplier Hub: Supplier Management
433 Following update will transfer the attachments
434 entered by supplier during registration time
435 to supplier entity upon approval
436 */
437 l_step := 'assign registered supplier attachments to approved supplier';
438
439 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
440 fnd_log.string
441 (fnd_log.level_statement
442 , g_module || '.' || l_method
443 , l_step);
444 END IF;
445
446 UPDATE fnd_attached_documents
447 SET entity_name = 'PO_VENDORS',
448 pk1_value = x_vendor_id,
449 last_updated_by = fnd_global.user_id,
450 last_update_date = Sysdate,
451 last_update_login = fnd_global.login_id
452 WHERE entity_name = 'POS_SUPP_REG' and
453 pk1_value = p_supplier_reg_rec.supplier_reg_id;
454
455
456 l_step := 'set party_usage_code SUPPLIER_PROSPECT as inactive';
457
458 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
459 fnd_log.string
460 (fnd_log.level_statement
461 , g_module || '.' || l_method
462 , l_step);
463 END IF;
464
465 UPDATE hz_party_usg_assignments
466 SET effective_end_date=sysdate,
467 status_flag = 'I',
468 last_updated_by = fnd_global.user_id,
469 last_update_date = Sysdate,
470 last_update_login = fnd_global.login_id
471 WHERE party_id= x_party_id
472 and party_usage_code='SUPPLIER_PROSPECT';
473
474
475 /* End Supplier Hub: Supplier Management */
476
477 l_step := 'update pos_supplier_registrations with ids';
478
479 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
480 fnd_log.string
481 (fnd_log.level_statement
482 , g_module || '.' || l_method
483 , l_step);
484 END IF;
485
486 UPDATE pos_supplier_registrations
487 SET registration_status = 'APPROVED',
488 po_vendor_id = x_vendor_id,
489 vendor_party_id = x_party_id,
490 last_updated_by = fnd_global.user_id,
491 last_update_date = Sysdate,
492 last_update_login = fnd_global.login_id
493 WHERE supplier_reg_id = p_supplier_reg_rec.supplier_reg_id;
494
495 x_return_status := fnd_api.g_ret_sts_success;
496
497 END IF;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501
502 /* Added for bug 7366321 */
503 if nvl(l_hzprofile_changed,'N') = 'Y' then
504 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', l_hzprofile_value);
505 l_hzprofile_changed := 'N';
506 end if;
507 /* End */
508
509 x_return_status := fnd_api.g_ret_sts_error;
510 raise_application_error (-20003, sqlerrm, true);
511
512 END create_vendor_and_party;
513
514 PROCEDURE create_supplier_addrs_sites
515 (p_supplier_reg_id IN NUMBER,
516 x_return_status OUT nocopy VARCHAR2,
517 x_msg_count OUT nocopy NUMBER,
518 x_msg_data OUT nocopy VARCHAR2
519 ) IS
520 CURSOR l_cur IS
521 SELECT par.address_request_id
522 FROM pos_address_requests par
523 , pos_supplier_mappings psm
524 WHERE par.request_type = 'ADD'
525 AND par.request_status = 'PENDING'
526 AND par.mapping_id = psm.mapping_id
527 AND psm.supplier_reg_id = p_supplier_reg_id;
528
529 BEGIN
530 FOR l_rec IN l_cur LOOP
531 pos_profile_change_request_pkg.approve_address_req
532 (p_request_id => l_rec.address_request_id,
533 x_return_status => x_return_status,
534 x_msg_count => x_msg_count,
535 x_msg_data => x_msg_data
536 );
537 IF x_return_status IS NULL OR
538 x_return_status <> fnd_api.g_ret_sts_success THEN
539 RETURN;
540 END IF;
541 END LOOP;
542
543 x_return_status := fnd_api.g_ret_sts_success;
544
545 END create_supplier_addrs_sites;
546
547 PROCEDURE create_supplier_contacts
548 (p_supplier_reg_id IN NUMBER,
549 x_return_status OUT nocopy VARCHAR2,
550 x_msg_count OUT nocopy NUMBER,
551 x_msg_data OUT nocopy VARCHAR2,
552 x_username_pwds OUT nocopy username_pwd_tbl
553 )
554 IS
555
556
557 /*
558 Supplier Hub : Supplier Managment Changes :
559 Modified where clause to pick up request_type 'ADD_PARTY_CONTACT also
560 */
561 CURSOR l_cur IS
562 SELECT pcr.contact_request_id, pcr.email_address,
563 psm.vendor_id, create_user_account
564 FROM pos_contact_requests pcr
565 , pos_supplier_mappings psm
566 WHERE (pcr.request_type = 'ADD'
567 OR pcr.request_type = 'ADD_PARTY_CONTACT')
568 AND pcr.request_status = 'PENDING'
569 AND pcr.mapping_id = psm.mapping_id
570 AND psm.supplier_reg_id = p_supplier_reg_id;
571
572 l_counter NUMBER;
573
574 CURSOR l_user_cur (p_user_name IN VARCHAR2) IS
575 SELECT user_id
576 FROM fnd_user
577 WHERE user_name = Upper(p_user_name);
578
579 l_user_id NUMBER;
580
581 CURSOR l_reg_type_cur IS
582 SELECT registration_type
583 FROM pos_supplier_registrations
584 WHERE supplier_reg_id = p_supplier_reg_id;
585
586 l_reg_type_rec l_reg_type_cur%ROWTYPE;
587
588 l_pon_def_also VARCHAR2(1);
589
590 l_temp_password VARCHAR2(200);
591
592 l_user_in_oid VARCHAR2(1);
593
594 BEGIN
595 l_counter := 0;
596
597 FOR l_rec IN l_cur LOOP
598
599 if (l_rec.create_user_account = 'Y' and
600 FND_USER_PKG.TestUserName(Upper(l_rec.email_address)) = FND_USER_PKG.USER_SYNCHED) then
601 l_user_in_oid := 'Y';
602 else
603 l_user_in_oid := 'N';
604 end if;
605
606 l_temp_password := NULL;
607
608 pos_profile_change_request_pkg.approve_contact_req
609 (p_request_id => l_rec.contact_request_id,
610 x_return_status => x_return_status,
611 x_msg_count => x_msg_count,
612 x_msg_data => x_msg_data,
613 x_password => l_temp_password
614 );
615
616 IF x_return_status IS NULL OR
617 x_return_status <> fnd_api.g_ret_sts_success THEN
618 RETURN;
619 END IF;
620
621 IF l_rec.create_user_account = 'Y' THEN
622 -- save the username and password to the return table
623 l_counter := l_counter + 1;
624 x_username_pwds(l_counter).user_name := Upper(l_rec.email_address);
625 x_username_pwds(l_counter).password := l_temp_password;
626 x_username_pwds(l_counter).exist_in_oid := l_user_in_oid;
627
628 -- assign default responsibilities for the new user account
629 OPEN l_user_cur (l_rec.email_address);
630 FETCH l_user_cur INTO l_user_id;
631
632 IF l_user_cur%notfound THEN
633 CLOSE l_user_cur;
634 ELSE
635 CLOSE l_user_cur;
636
637 l_pon_def_also := 'N';
638
639 OPEN l_reg_type_cur;
640 FETCH l_reg_type_cur INTO l_reg_type_rec;
641 IF l_reg_type_cur%found AND
642 l_reg_type_rec.registration_type = 'ONBOARD_SRC' THEN
643 l_pon_def_also := 'Y';
644 END IF;
645 CLOSE l_reg_type_cur;
646
647 pos_user_admin_pkg.assign_vendor_reg_def_resp
648 (p_user_id => l_user_id,
649 p_vendor_id => l_rec.vendor_id,
650 p_pon_def_also => l_pon_def_also,
651 x_return_status => x_return_status,
652 x_msg_count => x_msg_count,
653 x_msg_data => x_msg_data
654 );
655
656 IF x_return_status IS NULL OR
657 x_return_status <> fnd_api.g_ret_sts_success THEN
658 RETURN;
659 END IF;
660 END IF;
661 END IF;
662 END LOOP;
663
664 x_return_status := fnd_api.g_ret_sts_success;
665
666 END create_supplier_contacts;
667
668 PROCEDURE create_bus_class
669 (p_supplier_reg_id IN NUMBER,
670 x_return_status OUT nocopy VARCHAR2,
671 x_msg_count OUT nocopy NUMBER,
672 x_msg_data OUT nocopy VARCHAR2
673 )
674 IS
675 CURSOR l_cur IS
676 SELECT pbcr.bus_class_request_id
677 FROM pos_bus_class_reqs pbcr
678 , pos_supplier_mappings psm
679 WHERE pbcr.request_type = 'ADD'
680 AND pbcr.request_status = 'PENDING'
681 AND pbcr.mapping_id = psm.mapping_id
682 AND psm.supplier_reg_id = p_supplier_reg_id;
683
684 BEGIN
685
686 FOR l_rec IN l_cur LOOP
687 pos_profile_change_request_pkg.approve_bus_class_req
688 (p_request_id => l_rec.bus_class_request_id,
689 x_return_status => x_return_status,
690 x_msg_count => x_msg_count,
691 x_msg_data => x_msg_data
692 );
693 IF x_return_status IS NULL OR
694 x_return_status <> fnd_api.g_ret_sts_success THEN
695 RETURN;
696 END IF;
697 END LOOP;
698
699 x_return_status := fnd_api.g_ret_sts_success;
700
701 END create_bus_class;
702
703 PROCEDURE create_product_service
704 (p_supplier_reg_id IN NUMBER,
705 x_return_status OUT nocopy VARCHAR2,
706 x_msg_count OUT nocopy NUMBER,
707 x_msg_data OUT nocopy VARCHAR2
708 )
709 IS
710 CURSOR l_cur IS
711 SELECT ppsr.ps_request_id
712 FROM pos_product_service_requests ppsr
713 , pos_supplier_mappings psm
714 WHERE ppsr.request_type = 'ADD'
715 AND ppsr.request_status = 'PENDING'
716 AND ppsr.mapping_id = psm.mapping_id
717 AND psm.supplier_reg_id = p_supplier_reg_id;
718
719 BEGIN
720
721 FOR l_rec IN l_cur LOOP
722 pos_profile_change_request_pkg.approve_ps_req
723 (p_request_id => l_rec.ps_request_id,
724 x_return_status => x_return_status,
725 x_msg_count => x_msg_count,
726 x_msg_data => x_msg_data
727 );
728 IF x_return_status IS NULL OR
729 x_return_status <> fnd_api.g_ret_sts_success THEN
730 RETURN;
731 END IF;
732 END LOOP;
733
734 x_return_status := fnd_api.g_ret_sts_success;
735
736 END create_product_service;
737
738 PROCEDURE get_reg_primary_user
739 (p_supplier_reg_id IN NUMBER,
740 x_user_name OUT nocopy VARCHAR2,
741 x_user_id OUT nocopy NUMBER
742 )
743 IS
744 CURSOR l_cur IS
745 SELECT fu.user_name, fu.user_id
746 FROM pos_contact_requests pcr, fnd_user fu
747 WHERE pcr.mapping_id =
748 (SELECT mapping_id
749 FROM pos_supplier_mappings
750 WHERE supplier_reg_id = p_supplier_reg_id
751 )
752 AND pcr.request_status = 'APPROVED'
753 AND pcr.do_not_delete = 'Y'
754 AND fu.user_name = Upper(pcr.email_address);
755
756 BEGIN
757 OPEN l_cur;
758 FETCH l_cur INTO x_user_name, x_user_id;
759 CLOSE l_cur;
760 END get_reg_primary_user;
761
762
763 PROCEDURE notify_banking_approver
764 (p_vendor_id IN NUMBER,
765 x_return_status OUT nocopy VARCHAR2,
766 x_msg_count OUT nocopy NUMBER,
767 x_msg_data OUT nocopy VARCHAR2)
768 IS
769 l_itemtype wf_items.item_type%TYPE;
770 l_itemkey wf_items.item_key%TYPE;
771 l_receiver wf_roles.name%TYPE;
772 l_count NUMBER;
773
774 BEGIN
775 SELECT Count(pagr.ACCOUNT_REQUEST_ID)
776 INTO l_count
777 FROM POS_ACNT_GEN_REQ pagr,pos_supplier_mappings psm
778 WHERE pagr.mapping_id = psm.mapping_id
779 AND psm.vendor_id = p_vendor_id;
780
781
782 IF l_count>0 THEN
783 pos_spm_wf_pkg1.notify_bank_aprv_supp_aprv
784 (p_vendor_id => p_vendor_id,
785 x_itemtype => l_itemtype,
786 x_itemkey => l_itemkey,
787 x_receiver => l_receiver
788 ) ;
789 IF x_return_status IS NULL OR
790 x_return_status <> fnd_api.g_ret_sts_success THEN
791 RETURN;
792 END IF;
793 END IF;
794 x_return_status := fnd_api.g_ret_sts_success;
795
796 EXCEPTION
797 WHEN OTHERS THEN
798 x_return_status := fnd_api.g_ret_sts_unexp_error;
799 x_msg_count := 1;
800 x_msg_data := Sqlerrm;
801
802 END notify_banking_approver;
803
804 PROCEDURE notify_supplier_approved
805 (p_supplier_reg_id IN NUMBER,
806 p_username_pwds IN username_pwd_tbl,
807 x_return_status OUT nocopy VARCHAR2,
808 x_msg_count OUT nocopy NUMBER,
809 x_msg_data OUT nocopy VARCHAR2
810 )
811 IS
812 l_itemtype wf_items.item_type%TYPE;
813 l_itemkey wf_items.item_key%TYPE;
814 l_count NUMBER;
815 l_user_name fnd_user.user_name%TYPE;
816 l_user_id NUMBER;
817 BEGIN
818 get_reg_primary_user
819 (p_supplier_reg_id => p_supplier_reg_id,
820 x_user_name => l_user_name,
821 x_user_id => l_user_id
822 );
823 IF l_user_name IS NULL THEN
824 x_return_status := fnd_api.g_ret_sts_error;
825 x_msg_count := 1;
826 x_msg_data := 'can not find the primary supplier user';
827 RETURN;
828 END IF;
829
830 l_count := p_username_pwds.COUNT;
831 FOR l_index IN 1..l_count LOOP
832 IF p_username_pwds(l_index).user_name <> l_user_name THEN
833 if (p_username_pwds(l_index).exist_in_oid = 'Y') then
834 pos_spm_wf_pkg1.notify_user_approved_sso_sync
835 (p_supplier_reg_id => p_supplier_reg_id,
836 p_username => p_username_pwds(l_index).user_name,
837 x_itemtype => l_itemtype,
838 x_itemkey => l_itemkey
839 ) ;
840 else
841 pos_spm_wf_pkg1.notify_supplier_user_approved
842 (p_supplier_reg_id => p_supplier_reg_id,
843 p_username => p_username_pwds(l_index).user_name,
844 p_password => p_username_pwds(l_index).password,
845 x_itemtype => l_itemtype,
846 x_itemkey => l_itemkey
847 ) ;
848 end if;
849 ELSE
850 if (p_username_pwds(l_index).exist_in_oid = 'Y') then
851 pos_spm_wf_pkg1.notify_supplier_apprv_ssosync
852 (p_supplier_reg_id => p_supplier_reg_id,
853 p_username => p_username_pwds(l_index).user_name,
854 x_itemtype => l_itemtype,
855 x_itemkey => l_itemkey
856 ) ;
857 else
858 pos_spm_wf_pkg1.notify_supplier_approved
859 (p_supplier_reg_id => p_supplier_reg_id,
860 p_username => p_username_pwds(l_index).user_name,
861 p_password => p_username_pwds(l_index).password,
862 x_itemtype => l_itemtype,
863 x_itemkey => l_itemkey
864 ) ;
865 end if;
866 END IF;
867 END LOOP;
868
869 x_return_status := fnd_api.g_ret_sts_success;
870
871 EXCEPTION
872 WHEN OTHERS THEN
873 x_return_status := fnd_api.g_ret_sts_unexp_error;
874 x_msg_count := 1;
875 x_msg_data := Sqlerrm;
876
877 END notify_supplier_approved;
878
879 -- Begin Supplier Hub: Supplier Management
880 -- Procedure to update from prospective supplier UDAs to registered supplier UDAs
881 PROCEDURE update_supplier_reg_uda
882 (p_supplier_reg_id IN NUMBER,
883 p_party_id IN NUMBER,
884 x_return_status OUT nocopy VARCHAR2,
885 x_msg_count OUT nocopy NUMBER,
886 x_msg_data OUT nocopy VARCHAR2
887 )
888 IS
889
890 CURSOR l_cur IS
891 SELECT par.address_request_id, par.party_site_id
892 FROM pos_address_requests par,
893 pos_supplier_mappings psm
894 WHERE par.request_type = 'ADD'
895 AND par.mapping_id = psm.mapping_id
896 AND par.party_site_id IS NOT NULL
897 AND psm.supplier_reg_id = p_supplier_reg_id;
898
899 l_supp_level_id NUMBER;
900 l_supp_addr_level_id NUMBER;
901
902 BEGIN
903
904 -- get the data level id's
905
906 SELECT data_level_id
907 INTO l_supp_level_id
908 FROM ego_data_level_b
909 WHERE application_id = 177
910 AND attr_group_type = 'POS_SUPP_PROFMGMT_GROUP'
911 AND data_level_name = 'SUPP_LEVEL';
912
913 SELECT data_level_id
914 INTO l_supp_addr_level_id
915 FROM ego_data_level_b
916 WHERE application_id = 177
917 AND attr_group_type = 'POS_SUPP_PROFMGMT_GROUP'
918 AND data_level_name = 'SUPP_ADDR_LEVEL';
919
920 -- update party level UDAs
921
922 UPDATE pos_supp_prof_ext_b
923 SET is_prospect = 'N',
924 party_id = p_party_id
925 WHERE is_prospect = 'Y'
926 AND party_id = p_supplier_reg_id
927 AND data_level_id = l_supp_level_id;
928
929 UPDATE pos_supp_prof_ext_tl
930 SET is_prospect = 'N',
931 party_id = p_party_id
932 WHERE is_prospect = 'Y'
933 AND party_id = p_supplier_reg_id
934 AND data_level_id = l_supp_level_id;
935
936 -- update party site UDAs
937
938 FOR l_rec IN l_cur LOOP
939
940 UPDATE pos_supp_prof_ext_b
941 SET is_prospect = 'N',
942 party_id = p_party_id,
943 pk1_value = l_rec.party_site_id
944 WHERE is_prospect = 'Y'
945 AND party_id = p_supplier_reg_id
946 AND pk1_value = l_rec.address_request_id
947 AND data_level_id = l_supp_addr_level_id;
948
949 UPDATE pos_supp_prof_ext_tl
950 SET is_prospect = 'N',
951 party_id = p_party_id,
952 pk1_value = l_rec.party_site_id
953 WHERE is_prospect = 'Y'
954 AND party_id = p_supplier_reg_id
955 AND pk1_value = l_rec.address_request_id
956 AND data_level_id = l_supp_addr_level_id;
957
958 END LOOP;
959
960 x_return_status := fnd_api.g_ret_sts_success;
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 x_return_status := fnd_api.g_ret_sts_unexp_error;
965 x_msg_count := 1;
966 x_msg_data := SQLERRM;
967
968 END update_supplier_reg_uda;
969 -- End Supplier Hub: Supplier Management
970
971 PROCEDURE approve_supplier_reg
972 (p_supplier_reg_id IN NUMBER,
973 x_return_status OUT NOCOPY VARCHAR2,
974 x_msg_count OUT NOCOPY NUMBER,
975 x_msg_data OUT NOCOPY VARCHAR2
976 )
977 IS
978 l_supplier_reg_rec pos_supplier_registrations%ROWTYPE;
979 l_step VARCHAR2(100);
980 l_method VARCHAR2(30);
981 l_vendor_id NUMBER;
982 l_vendor_party_id NUMBER;
983 l_username_pwds username_pwd_tbl;
984 l_user_name fnd_user.user_name%TYPE;
985 l_user_id NUMBER;
986
987 CURSOR l_ptp_cur(p_party_id IN NUMBER) IS
988 SELECT party_tax_profile_id
989 FROM zx_party_tax_profile
990 WHERE party_id = p_party_id
991 AND party_type_code = 'THIRD_PARTY'
992 AND ROWNUM = 1;
993
994 l_party_tax_profile_id NUMBER;
995 BEGIN
996
997 SAVEPOINT approve_supplier_reg;
998
999 l_method := 'approve_supplier';
1000
1001 x_return_status := fnd_api.g_ret_sts_error;
1002
1003 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1004 fnd_log.string
1005 (fnd_log.level_procedure
1006 , g_module || '.' || l_method
1007 , 'start');
1008 END IF;
1009
1010 l_step := 'lock supplier reg row';
1011 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1012 fnd_log.string
1013 (fnd_log.level_statement
1014 , g_module || '.' || l_method
1015 , l_step);
1016 END IF;
1017
1018 lock_supplier_reg_row
1019 (p_supplier_reg_id => p_supplier_reg_id,
1020 x_return_status => x_return_status,
1021 x_msg_count => x_msg_count,
1022 x_msg_data => x_msg_data,
1023 x_supplier_reg_rec => l_supplier_reg_rec
1024 );
1025
1026 IF NOT (x_return_status IS NOT NULL
1027 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1028 ROLLBACK TO approve_supplier_reg;
1029 RETURN;
1030 END IF;
1031
1032 l_step := 'check reg status';
1033 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1034 fnd_log.string
1035 (fnd_log.level_statement
1036 , g_module || '.' || l_method
1037 , l_step);
1038 END IF;
1039
1040 IF l_supplier_reg_rec.registration_status IS NULL OR
1041 l_supplier_reg_rec.registration_status NOT IN ('PENDING_APPROVAL','RIF_SUPPLIER')
1042 THEN
1043 x_return_status := fnd_api.g_ret_sts_error;
1044 fnd_message.set_name('POS','POS_SUPPLIER_REG_NOT_PENDING');
1045 fnd_msg_pub.ADD;
1046 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1047
1048 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1049 fnd_log.string
1050 (fnd_log.level_error
1051 , g_module || '.' || l_method
1052 , 'status is not PENDING_APPROVAL for reg id ' || p_supplier_reg_id);
1053 END IF;
1054
1055 ROLLBACK TO approve_supplier_reg;
1056 RETURN;
1057 END IF;
1058
1059 l_step := 'create vendor and party';
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 create_vendor_and_party
1068 (p_supplier_reg_rec => l_supplier_reg_rec,
1069 x_return_status => x_return_status,
1070 x_msg_count => x_msg_count,
1071 x_msg_data => x_msg_data,
1072 x_vendor_id => l_vendor_id,
1073 x_party_id => l_vendor_party_id
1074 );
1075
1076 IF NOT (x_return_status IS NOT NULL
1077 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1078 ROLLBACK TO approve_supplier_reg;
1079 RETURN;
1080 END IF;
1081
1082 l_supplier_reg_rec.po_vendor_id := l_vendor_id;
1083 l_supplier_reg_rec.vendor_party_id := l_vendor_party_id;
1084
1085 l_step := 'create supplier addresses and vendor sites';
1086 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1087 fnd_log.string
1088 (fnd_log.level_statement
1089 , g_module || '.' || l_method
1090 , l_step);
1091 END IF;
1092
1093 -- Call eTax API to save tax reg type and tax reg country to party tax profile
1094 -- if they are collected during registration.
1095 -- We are doing this because even though TCA party creation API
1096 -- does create an party tax profile record for a new party,
1097 -- it does not store tax reg country code and tax reg type
1098 -- in the record as of 11/29/05 in r12 code line.
1099
1100 IF l_supplier_reg_rec.tax_reg_country_code IS NOT NULL OR
1101 l_supplier_reg_rec.tax_reg_type IS NOT NULL OR
1102 l_supplier_reg_rec.tax_registration_number IS NOT NULL THEN
1103
1104 OPEN l_ptp_cur(l_vendor_party_id);
1105 FETCH l_ptp_cur INTO l_party_tax_profile_id;
1106 IF l_ptp_cur%found THEN
1107 CLOSE l_ptp_cur;
1108
1109 zx_party_tax_profile_pkg.update_row
1110 (p_party_tax_profile_id => l_party_tax_profile_id,
1111 p_collecting_authority_flag => NULL,
1112 p_provider_type_code => NULL,
1113 p_create_awt_dists_type_code => NULL,
1114 p_create_awt_invoices_type_cod => NULL,
1115 p_tax_classification_code => NULL,
1116 p_self_assess_flag => NULL,
1117 p_allow_offset_tax_flag => NULL,
1118 p_rep_registration_number => l_supplier_reg_rec.tax_registration_number,
1119 p_effective_from_use_le => NULL,
1120 p_record_type_code => NULL,
1121 p_request_id => NULL,
1122 p_attribute1 => NULL,
1123 p_attribute2 => NULL,
1124 p_attribute3 => NULL,
1125 p_attribute4 => NULL,
1126 p_attribute5 => NULL,
1127 p_attribute6 => NULL,
1128 p_attribute7 => NULL,
1129 p_attribute8 => NULL,
1130 p_attribute9 => NULL,
1131 p_attribute10 => NULL,
1132 p_attribute11 => NULL,
1133 p_attribute12 => NULL,
1134 p_attribute13 => NULL,
1135 p_attribute14 => NULL,
1136 p_attribute15 => NULL,
1137 p_attribute_category => NULL,
1138 p_party_id => NULL,
1139 p_program_login_id => NULL,
1140 p_party_type_code => NULL,
1141 p_supplier_flag => NULL,
1142 p_customer_flag => NULL,
1143 p_site_flag => NULL,
1144 p_process_for_applicability_fl => NULL,
1145 p_rounding_level_code => NULL,
1146 p_rounding_rule_code => NULL,
1147 p_withholding_start_date => NULL,
1148 p_inclusive_tax_flag => NULL,
1149 p_allow_awt_flag => NULL,
1150 p_use_le_as_subscriber_flag => NULL,
1151 p_legal_establishment_flag => NULL,
1152 p_first_party_le_flag => NULL,
1153 p_reporting_authority_flag => NULL,
1154 x_return_status => x_return_status,
1155 p_registration_type_code => l_supplier_reg_rec.tax_reg_type,
1156 p_country_code => l_supplier_reg_rec.tax_reg_country_code
1157 );
1158 IF x_return_status IS NULL
1159 OR x_return_status <> fnd_api.g_ret_sts_success THEN
1160 ROLLBACK TO approve_supplier_reg;
1161 x_msg_count := 1;
1162 x_msg_data := 'call to zx_party_tax_profile_pkg.update_row failed';
1163 RETURN;
1164 END IF;
1165 ELSE
1166 CLOSE l_ptp_cur;
1167 END IF;
1168 END IF;
1169
1170 create_supplier_addrs_sites
1171 (p_supplier_reg_id => l_supplier_reg_rec.supplier_reg_id,
1172 x_return_status => x_return_status,
1173 x_msg_count => x_msg_count,
1174 x_msg_data => x_msg_data
1175 );
1176
1177 IF x_return_status IS NULL
1178 OR x_return_status <> fnd_api.g_ret_sts_success THEN
1179 ROLLBACK TO approve_supplier_reg;
1180 RETURN;
1181 END IF;
1182
1183 l_step := 'create supplier contacts';
1184 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1185 fnd_log.string
1186 (fnd_log.level_statement
1187 , g_module || '.' || l_method
1188 , l_step);
1189 END IF;
1190
1191 create_supplier_contacts
1192 (p_supplier_reg_id => l_supplier_reg_rec.supplier_reg_id,
1193 x_return_status => x_return_status,
1194 x_msg_count => x_msg_count,
1195 x_msg_data => x_msg_data,
1196 x_username_pwds => l_username_pwds
1197 );
1198
1199 IF NOT (x_return_status IS NOT NULL
1200 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1201 ROLLBACK TO approve_supplier_reg;
1202 RETURN;
1203 END IF;
1204
1205 l_step := 'create supplier business classification';
1206 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1207 fnd_log.string
1208 (fnd_log.level_statement
1209 , g_module || '.' || l_method
1210 , l_step);
1211 END IF;
1212
1213 create_bus_class
1214 (p_supplier_reg_id => l_supplier_reg_rec.supplier_reg_id,
1215 x_return_status => x_return_status,
1216 x_msg_count => x_msg_count,
1217 x_msg_data => x_msg_data
1218 );
1219
1220 IF NOT (x_return_status IS NOT NULL
1221 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1222 ROLLBACK TO approve_supplier_reg;
1223 RETURN;
1224 END IF;
1225
1226 l_step := 'create product and services';
1227 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1228 fnd_log.string
1229 (fnd_log.level_statement
1230 , g_module || '.' || l_method
1231 , l_step);
1232 END IF;
1233
1234 create_product_service
1235 (p_supplier_reg_id => l_supplier_reg_rec.supplier_reg_id,
1236 x_return_status => x_return_status,
1237 x_msg_count => x_msg_count,
1238 x_msg_data => x_msg_data
1239 );
1240
1241 IF NOT (x_return_status IS NOT NULL
1242 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1243 ROLLBACK TO approve_supplier_reg;
1244 RETURN;
1245 END IF;
1246
1247 l_step := 'handle supplier survey';
1248 -- to be coded as part of supplier profile survey project
1249 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1250 fnd_log.string
1251 (fnd_log.level_statement
1252 , g_module || '.' || l_method
1253 , l_step);
1254 END IF;
1255
1256 l_step := 'notify supplier';
1257
1258 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1259 fnd_log.string
1260 (fnd_log.level_statement
1261 , g_module || '.' || l_method
1262 , l_step);
1263 END IF;
1264
1265 notify_supplier_approved
1266 (p_supplier_reg_id => p_supplier_reg_id,
1267 p_username_pwds => l_username_pwds,
1268 x_return_status => x_return_status,
1269 x_msg_count => x_msg_count,
1270 x_msg_data => x_msg_data
1271 );
1272
1273 IF NOT (x_return_status IS NOT NULL
1274 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1275 ROLLBACK TO approve_supplier_reg;
1276 RETURN;
1277 END IF;
1278
1279 -- Notify the banking approvers about the supplier approval
1280 -- so that they can review the supplier bank accounts. Bug 5299682
1281 notify_banking_approver
1282 (p_vendor_id => l_vendor_id,
1283 x_return_status => x_return_status,
1284 x_msg_count => x_msg_count,
1285 x_msg_data => x_msg_data
1286 );
1287
1288 get_reg_primary_user
1289 (p_supplier_reg_id => p_supplier_reg_id,
1290 x_user_name => l_user_name,
1291 x_user_id => l_user_id
1292 );
1293
1294 -- Code Added for Business Classification Re-Certification ER to update ap_suppliers table
1295 -- with the last certification date and last certified by values at the time of supplier approval.
1296
1297 update ap_suppliers
1298 set bus_class_last_certified_by = l_user_id,
1299 bus_class_last_certified_date = (select creation_date
1300 from pos_supplier_registrations
1301 where supplier_reg_id = p_supplier_reg_id ),
1302 last_updated_by = l_user_id,
1303 last_update_date = sysdate
1304
1305 where vendor_id=l_vendor_id;
1306
1307 -- End of Code added for Business Classification Re-Certification ER
1308
1309 pon_new_supplier_reg_pkg.src_pos_reg_supplier_callback
1310 ( x_return_status => x_return_status,
1311 x_msg_count => x_msg_count,
1312 x_msg_data => x_msg_data,
1313 p_requested_supplier_id => p_supplier_reg_id,
1314 p_po_vendor_id => l_vendor_id,
1315 p_supplier_hz_party_id => l_vendor_party_id,
1316 p_user_id => l_user_id
1317 );
1318
1319 IF NOT (x_return_status IS NOT NULL
1320 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1321 ROLLBACK TO approve_supplier_reg;
1322 RETURN;
1323 END IF;
1324
1325 -- Begin Supplier Hub: Supplier Management
1326 update_supplier_reg_uda
1327 (p_supplier_reg_id => p_supplier_reg_id,
1328 p_party_id => l_vendor_party_id,
1329 x_return_status => x_return_status,
1330 x_msg_count => x_msg_count,
1331 x_msg_data => x_msg_data
1332 );
1333
1334 IF NOT (x_return_status IS NOT NULL
1335 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1336 ROLLBACK TO approve_supplier_reg;
1337 RETURN;
1338 END IF;
1339
1340 IF (FND_PROFILE.VALUE('POS_SM_ENABLE_SPM_EXTENSION') = 'Y') THEN
1341 PON_ATTR_MAPPING.Sync_User_Attrs_Data(NULL, l_vendor_id, x_return_status, x_msg_data);
1342 END IF;
1343
1344 IF NOT (x_return_status IS NOT NULL
1345 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1346 ROLLBACK TO approve_supplier_reg;
1347 RETURN;
1348 END IF;
1349 -- End Supplier Hub: Supplier Management
1350
1351 x_return_status := fnd_api.g_ret_sts_success;
1352 x_msg_count := 0;
1353 x_msg_data := NULL;
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 ROLLBACK TO approve_supplier_reg;
1358 raise_application_error (-20004, 'error in step ' || l_step ||
1359 ': ' || Sqlerrm, true);
1360
1361 END approve_supplier_reg;
1362
1363 --- note: when creating rfq only supplier, we will skip business
1364 --- classification and product and service
1365
1366 PROCEDURE reject_supplier_reg
1367 (p_supplier_reg_id IN NUMBER,
1368 x_return_status OUT NOCOPY VARCHAR2,
1369 x_msg_count OUT NOCOPY NUMBER,
1370 x_msg_data OUT NOCOPY VARCHAR2
1371 ) IS
1372 l_step VARCHAR2(100);
1373 l_method VARCHAR2(30);
1374 l_itemtype wf_items.item_type%TYPE;
1375 l_itemkey wf_items.item_key%TYPE;
1376 l_receiver fnd_user.user_name%TYPE;
1377
1378 l_party_usages number := 0 ;
1379
1380 l_supplier_reg_rec pos_supplier_registrations%ROWTYPE;
1381 event_id Number;
1382 BEGIN
1383 l_method := 'reject_supplier_reg';
1384
1385 l_step := 'lock supplier reg row';
1386 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1387 fnd_log.string
1388 (fnd_log.level_statement
1389 , g_module || '.' || l_method
1390 , l_step);
1391 END IF;
1392
1393 lock_supplier_reg_row
1394 (p_supplier_reg_id => p_supplier_reg_id,
1395 x_return_status => x_return_status,
1396 x_msg_count => x_msg_count,
1397 x_msg_data => x_msg_data,
1398 x_supplier_reg_rec => l_supplier_reg_rec
1399 );
1400
1401 IF NOT (x_return_status IS NOT NULL
1402 AND x_return_status = fnd_api.g_ret_sts_success) THEN
1403 RETURN;
1404 END IF;
1405
1406 l_step := 'check reg status';
1407 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1408 fnd_log.string
1409 (fnd_log.level_statement
1410 , g_module || '.' || l_method
1411 , l_step);
1412 END IF;
1413
1414 IF l_supplier_reg_rec.registration_status IS NULL OR
1415 l_supplier_reg_rec.registration_status <> 'PENDING_APPROVAL' THEN
1416 x_return_status := fnd_api.g_ret_sts_error;
1417 fnd_message.set_name('POS','POS_SUPPLIER_REG_NOT_PENDING');
1418 fnd_msg_pub.ADD;
1419 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1420
1421 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1422 fnd_log.string
1423 (fnd_log.level_error
1424 , g_module || '.' || l_method
1425 , 'status is not PENDING_APPROVAL for reg id ' || p_supplier_reg_id);
1426 END IF;
1427
1428 RETURN;
1429 END IF;
1430
1431 UPDATE pos_supplier_registrations
1432 SET registration_status = 'REJECTED',
1433 last_update_date = Sysdate,
1434 last_updated_by = fnd_global.user_id,
1435 last_update_login = fnd_global.login_id
1436 WHERE supplier_reg_id = p_supplier_reg_id;
1437
1438 UPDATE pos_address_requests
1439 SET request_status = 'REJECTED',
1440 last_update_date = Sysdate,
1441 last_updated_by = fnd_global.user_id,
1442 last_update_login = fnd_global.login_id
1443 WHERE mapping_id =
1444 (SELECT mapping_id FROM pos_supplier_mappings
1445 WHERE supplier_reg_id = p_supplier_reg_id)
1446 AND request_status = 'PENDING';
1447
1448 UPDATE pos_contact_requests
1449 SET request_status = 'REJECTED',
1450 last_update_date = Sysdate,
1451 last_updated_by = fnd_global.user_id,
1452 last_update_login = fnd_global.login_id
1453 WHERE mapping_id =
1454 (SELECT mapping_id FROM pos_supplier_mappings
1455 WHERE supplier_reg_id = p_supplier_reg_id)
1456 AND request_status = 'PENDING';
1457
1458 UPDATE pos_cont_addr_requests
1459 SET request_status = 'REJECTED',
1460 last_update_date = Sysdate,
1461 last_updated_by = fnd_global.user_id,
1462 last_update_login = fnd_global.login_id
1463 WHERE mapping_id =
1464 (SELECT mapping_id FROM pos_supplier_mappings
1465 WHERE supplier_reg_id = p_supplier_reg_id)
1466 AND request_status = 'PENDING';
1467
1468
1469 /* Begin Supplier Hub: Supplier Management */
1470 /* set the status of the party created to 'I' */
1471
1472 select count(*)
1473 into l_party_usages
1474 from hz_party_usg_assignments
1475 where party_id = (select vendor_party_id
1476 from pos_supplier_registrations
1477 where supplier_reg_id = p_supplier_reg_id);
1478
1479 if (l_party_usages = 0 ) then
1480
1481 update hz_parties
1482 set status = 'I',
1483 last_update_date = Sysdate,
1484 last_updated_by = fnd_global.user_id,
1485 last_update_login = fnd_global.login_id
1486 where status = 'A' and
1487 created_by_module = 'POS_SUPPLIER_MGMT' and
1488 party_id = (select vendor_party_id
1489 from pos_supplier_registrations
1490 where supplier_reg_id = p_supplier_reg_id);
1491 end if;
1492
1493 /* set party_usage_code SUPPLIER_PROSPECT as inactive */
1494
1495 UPDATE hz_party_usg_assignments
1496 SET effective_end_date=sysdate,
1497 status_flag = 'I',
1498 last_update_date = Sysdate,
1499 last_updated_by = fnd_global.user_id,
1500 last_update_login = fnd_global.login_id
1501 WHERE party_id= (select vendor_party_id
1502 from pos_supplier_registrations
1503 where supplier_reg_id = p_supplier_reg_id)
1504 and party_usage_code='SUPPLIER_PROSPECT';
1505
1506 /* End Supplier Hub: Supplier Management */
1507
1508 pos_spm_wf_pkg1.notify_supplier_rejected
1509 (p_supplier_reg_id => p_supplier_reg_id,
1510 x_itemtype => l_itemtype,
1511 x_itemkey => l_itemkey,
1512 x_receiver => l_receiver
1513 );
1514
1515 x_return_status := fnd_api.g_ret_sts_success;
1516 x_msg_count := 0;
1517 x_msg_data := NULL;
1518 /* Begin Supplier Hub - Supplier Data Publication */
1519 /* Raise Supplier User Creation event */
1520 event_id:= pos_appr_rej_supp_event_raise.raise_appr_rej_supp_event('oracle.apps.pos.supplier.rejectsupplier', p_supplier_reg_id, '');
1521
1522 /* End Supplier Hub - Supplier Data Publication */
1523 END reject_supplier_reg;
1524
1525 PROCEDURE submit_supplier_reg
1526 (p_supplier_reg_id IN NUMBER,
1527 x_return_status OUT NOCOPY VARCHAR2,
1528 x_msg_count OUT NOCOPY NUMBER,
1529 x_msg_data OUT NOCOPY VARCHAR2
1530 ) IS
1531
1532 l_step VARCHAR2(100);
1533 l_method VARCHAR2(30);
1534 l_supplier_reg_rec pos_supplier_registrations%ROWTYPE;
1535 BEGIN
1536 l_method := 'submit_supplier_reg';
1537
1538 l_step := 'lock supplier reg row';
1539 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1540 fnd_log.string
1541 (fnd_log.level_statement
1542 , g_module || '.' || l_method
1543 , l_step);
1544 END IF;
1545
1546 lock_supplier_reg_row
1547 (p_supplier_reg_id => p_supplier_reg_id,
1548 x_return_status => x_return_status,
1549 x_msg_count => x_msg_count,
1550 x_msg_data => x_msg_data,
1551 x_supplier_reg_rec => l_supplier_reg_rec
1552 );
1553
1554 IF x_return_status IS NULL
1555 OR x_return_status <> fnd_api.g_ret_sts_success THEN
1556 RETURN;
1557 END IF;
1558
1559 l_step := 'check reg status';
1560 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1561 fnd_log.string
1562 (fnd_log.level_statement
1563 , g_module || '.' || l_method
1564 , l_step);
1565 END IF;
1566
1567 IF l_supplier_reg_rec.registration_status IS NULL OR
1568 (l_supplier_reg_rec.registration_status <> 'DRAFT' AND
1569 l_supplier_reg_rec.registration_status <> 'RIF_SUPPLIER')
1570 THEN
1571 x_return_status := fnd_api.g_ret_sts_error;
1572 fnd_message.set_name('POS','POS_SUPPLIER_REG_NOT_DRAFT');
1573 fnd_msg_pub.ADD;
1574 fnd_msg_pub.count_and_get(fnd_api.g_false, x_msg_count, x_msg_data);
1575
1576 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1577 fnd_log.string
1578 (fnd_log.level_error
1579 , g_module || '.' || l_method
1580 , 'status is not DRAFT for reg id ' || p_supplier_reg_id);
1581 END IF;
1582
1583 RETURN;
1584 END IF;
1585
1586 UPDATE pos_supplier_registrations
1587 SET registration_status = 'PENDING_APPROVAL',
1588 last_update_date = Sysdate,
1589 last_updated_by = fnd_global.user_id,
1590 last_update_login = fnd_global.login_id
1591 WHERE supplier_reg_id = p_supplier_reg_id;
1592
1593 x_return_status := fnd_api.g_ret_sts_success;
1594 x_msg_count := 0;
1595 x_msg_data := NULL;
1596
1597 END submit_supplier_reg;
1598
1599 PROCEDURE send_save_for_later_ntf
1600 (p_supplier_reg_id IN NUMBER,
1601 p_email_address IN VARCHAR2,
1602 x_return_status OUT NOCOPY VARCHAR2,
1603 x_msg_count OUT NOCOPY NUMBER,
1604 x_msg_data OUT NOCOPY VARCHAR2
1605 ) IS
1606 BEGIN
1607
1608 pos_spm_wf_pkg1.send_supplier_reg_saved_ntf
1609 (p_supplier_reg_id => p_supplier_reg_id);
1610
1611 x_return_status := fnd_api.g_ret_sts_success;
1612 x_msg_count := 0;
1613 x_msg_data := NULL;
1614
1615 END send_save_for_later_ntf;
1616
1617 FUNCTION is_ou_id_valid
1618 (p_ou_id IN NUMBER
1619 ) RETURN VARCHAR2
1620 IS
1621
1622 ln_dup hr_operating_units.organization_id%TYPE;
1623
1624 CURSOR hou_cur IS
1625 SELECT organization_id
1626 FROM hr_operating_units
1627 WHERE organization_id = p_ou_id
1628 AND ( date_to IS NULL OR
1629 ( date_to > sysdate AND date_to > date_from ) );
1630
1631 BEGIN
1632
1633 OPEN hou_cur;
1634 FETCH hou_cur INTO ln_dup;
1635 IF hou_cur%FOUND THEN
1636 CLOSE hou_cur;
1637 RETURN 'Y';
1638 END IF;
1639 CLOSE hou_cur;
1640
1641 RETURN 'N';
1642
1643 END;
1644
1645 FUNCTION is_supplier_number_unique
1646 (p_supp_regid IN NUMBER,
1647 p_supp_number IN VARCHAR2
1648 ) RETURN VARCHAR2
1649 IS
1650 BEGIN
1651
1652 IF p_supp_number IS NULL THEN
1653 RETURN 'N';
1654 END IF;
1655
1656 FOR x IN (SELECT supplier_number
1657 FROM pos_supplier_registrations
1658 WHERE supplier_reg_id <> p_supp_regid
1659 --AND registration_status <> 'REJECTED'
1660 -- the unique key POS_SUPPLIER_REG_U2 is on supplier_number only
1661 -- without considering the registration_status
1662 AND p_supp_number = supplier_number
1663 AND ROWNUM < 2
1664 )
1665 LOOP
1666 RETURN 'N';
1667 END LOOP;
1668
1669 FOR x IN (SELECT segment1
1670 FROM ap_suppliers
1671 WHERE segment1 = p_supp_number
1672 AND ROWNUM < 2
1673 )
1674 LOOP
1675 RETURN 'N';
1676 END LOOP;
1677
1678 RETURN 'Y';
1679
1680 END is_supplier_number_unique;
1681
1682 /*Bug 8819829
1683 Modified cursors to check duplicates only based on tax payer id.
1684 Country shoud not be considered for checking duplicates. */
1685
1686 PROCEDURE is_taxpayer_id_unique(
1687 p_supp_regid IN NUMBER
1688 , p_taxpayer_id IN VARCHAR2
1689 , p_country IN VARCHAR2
1690 , x_is_unique OUT NOCOPY VARCHAR2
1691 , x_vendor_id OUT NOCOPY NUMBER
1692 )
1693 IS
1694
1695 CURSOR supp_reg_cur IS
1696 SELECT -1
1697 FROM pos_supplier_registrations psr
1698 WHERE psr.supplier_reg_id <> p_supp_regid
1699 AND psr.taxpayer_id = p_taxpayer_id
1700 AND psr.registration_status = 'PENDING_APPROVAL';
1701
1702 CURSOR po_vendors_cur IS
1703 SELECT pv.vendor_id
1704 FROM ap_suppliers pv
1705 WHERE pv.num_1099 = p_taxpayer_id;
1706
1707 BEGIN
1708
1709 IF p_taxpayer_id IS NULL THEN
1710 x_is_unique := 'Y';
1711 x_vendor_id := -1;
1712 RETURN;
1713 END IF;
1714
1715 /* Code Change For Bug 9569389 - Start
1716 Swapping the position of the below cursors inorder to check the po_vendors first */
1717
1718 OPEN po_vendors_cur;
1719 FETCH po_vendors_cur INTO x_vendor_id;
1720 IF po_vendors_cur%FOUND THEN
1721 CLOSE po_vendors_cur;
1722 x_is_unique := 'N';
1723 RETURN;
1724 END IF;
1725 CLOSE po_vendors_cur;
1726
1727 OPEN supp_reg_cur;
1728 FETCH supp_reg_cur INTO x_vendor_id;
1729 IF supp_reg_cur%FOUND THEN
1730 CLOSE supp_reg_cur;
1731 x_is_unique := 'N';
1732 RETURN;
1733 END IF;
1734 CLOSE supp_reg_cur;
1735
1736 /* Code Change For Bug 9569389 - End */
1737
1738 x_is_unique := 'Y';
1739 x_vendor_id := -1;
1740 END is_taxpayer_id_unique;
1741
1742 PROCEDURE is_duns_num_unique(
1743 p_supp_regid IN NUMBER
1744 , p_duns_num IN VARCHAR2
1745 , x_is_unique OUT NOCOPY VARCHAR2
1746 , x_vendor_id OUT NOCOPY NUMBER
1747 )
1748 IS
1749
1750 l_party_id hz_parties.party_id%TYPE := -1;
1751
1752 CURSOR supp_reg_cur IS
1753 SELECT -1
1754 FROM pos_supplier_registrations psr
1755 WHERE psr.supplier_reg_id <> p_supp_regid
1756 AND psr.duns_number = p_duns_num
1757 AND psr.registration_status = 'PENDING_APPROVAL';
1758
1759 CURSOR hz_cur IS
1760 SELECT party_id
1761 FROM hz_parties
1762 WHERE duns_number_c = p_duns_num
1763 AND party_type = 'ORGANIZATION';
1764
1765 CURSOR po_vendors_cur ( p_party_id hz_parties.party_id%TYPE ) IS
1766 SELECT vendor_id
1767 FROM ap_suppliers
1768 WHERE party_id = p_party_id;
1769
1770 /*Begin Supplier Data Hub - Supplier Management*/
1771
1772 CURSOR supp_reg_org_cur ( p_party_id hz_parties.party_id%TYPE ) IS
1773 SELECT -1
1774 FROM pos_supplier_registrations psr
1775 WHERE psr.supplier_reg_id = p_supp_regid
1776 AND psr.vendor_party_id = p_party_id
1777 AND psr.registration_status = 'PENDING_APPROVAL';
1778
1779 /*End Supplier Data Hub - Supplier Management*/
1780
1781 BEGIN
1782
1783 IF p_duns_num IS NULL THEN
1784 x_is_unique := 'Y';
1785 x_vendor_id := -1;
1786 RETURN;
1787 END IF;
1788
1789 OPEN supp_reg_cur;
1790 FETCH supp_reg_cur INTO x_vendor_id;
1791 IF supp_reg_cur%FOUND THEN
1792 CLOSE supp_reg_cur;
1793 x_is_unique := 'N';
1794 RETURN;
1795 END IF;
1796 CLOSE supp_reg_cur;
1797
1798 OPEN hz_cur;
1799 FETCH hz_cur INTO l_party_id;
1800 IF hz_cur%NOTFOUND THEN
1801 CLOSE hz_cur;
1802 x_is_unique := 'Y';
1803 x_vendor_id := -1;
1804 RETURN;
1805 END IF;
1806 CLOSE hz_cur;
1807
1808 /*Begin Supplier Data Hub - Supplier Management*/
1809
1810 OPEN supp_reg_org_cur(l_party_id);
1811 FETCH supp_reg_org_cur INTO x_vendor_id;
1812 IF supp_reg_org_cur%FOUND THEN
1813 CLOSE supp_reg_org_cur;
1814 x_is_unique := 'Y';
1815 x_vendor_id := -1;
1816 RETURN;
1817 END IF;
1818 CLOSE supp_reg_org_cur;
1819
1820 /*End Supplier Data Hub - Supplier Management*/
1821
1822 OPEN po_vendors_cur(l_party_id);
1823 FETCH po_vendors_cur INTO x_vendor_id;
1824 IF po_vendors_cur%NOTFOUND THEN
1825 CLOSE po_vendors_cur;
1826 x_is_unique := 'Y';
1827 x_vendor_id := -1;
1828 RETURN;
1829 END IF;
1830 CLOSE po_vendors_cur;
1831 x_is_unique := 'N';
1832 RETURN;
1833
1834 END is_duns_num_unique;
1835
1836 PROCEDURE is_taxregnum_unique(
1837 p_supp_regid IN NUMBER
1838 , p_taxreg_num IN VARCHAR2
1839 , p_country IN VARCHAR2
1840 , x_is_unique OUT NOCOPY VARCHAR2
1841 , x_vendor_id OUT NOCOPY NUMBER
1842 )
1843 IS
1844
1845 CURSOR supp_reg_cur IS
1846 SELECT -1
1847 FROM pos_supplier_registrations psr
1848 WHERE psr.supplier_reg_id <> p_supp_regid
1849 AND psr.tax_registration_number = p_taxreg_num
1850 AND ((psr.tax_reg_country_code is not null and p_country is not null and psr.tax_reg_country_code = p_country) OR
1851 (p_country is null))
1852 AND psr.registration_status = 'PENDING_APPROVAL';
1853
1854 CURSOR po_vendors_cur IS
1855 SELECT pv.vendor_id
1856 FROM ap_suppliers pv, zx_party_tax_profile zxpr
1857 WHERE zxpr.party_id = pv.party_id
1858 AND zxpr.rep_registration_number = p_taxreg_num
1859 AND ((zxpr.country_code is not null and p_country is not null and zxpr.country_code = p_country) OR
1860 (p_country is null));
1861
1862 CURSOR po_vendor_sites_cur IS
1863 SELECT pvsa.vendor_id
1864 FROM ap_supplier_sites_all pvsa, zx_party_tax_profile zxpr
1865 WHERE zxpr.rep_registration_number = p_taxreg_num
1866 AND zxpr.site_flag = 'Y'
1867 AND zxpr.party_id = pvsa.party_site_id
1868 AND ((zxpr.country_code is not null and p_country is not null and zxpr.country_code = p_country) OR
1869 (p_country is null));
1870 BEGIN
1871
1872 IF p_taxreg_num IS NULL THEN
1873 x_is_unique := 'Y';
1874 x_vendor_id := -1;
1875 RETURN;
1876 END IF;
1877
1878 OPEN supp_reg_cur;
1879 FETCH supp_reg_cur INTO x_vendor_id;
1880 IF supp_reg_cur%FOUND THEN
1881 CLOSE supp_reg_cur;
1882 x_is_unique := 'N';
1883 RETURN;
1884 END IF;
1885 CLOSE supp_reg_cur;
1886
1887 OPEN po_vendors_cur;
1888 FETCH po_vendors_cur INTO x_vendor_id;
1889 IF po_vendors_cur%FOUND THEN
1890 CLOSE po_vendors_cur;
1891 x_is_unique := 'N';
1892 RETURN;
1893 END IF;
1894 CLOSE po_vendors_cur;
1895
1896 OPEN po_vendor_sites_cur;
1897 FETCH po_vendor_sites_cur INTO x_vendor_id;
1898 IF po_vendor_sites_cur%FOUND THEN
1899 CLOSE po_vendor_sites_cur;
1900 x_is_unique := 'N';
1901 RETURN;
1902 END IF;
1903 CLOSE po_vendor_sites_cur;
1904
1905 x_is_unique := 'Y';
1906 x_vendor_id := -1;
1907 END is_taxregnum_unique;
1908
1909 -- Begin Supplier Management: Bug 12849540
1910 /*
1911 * Return a list of classification codes for a prospective supplier
1912 */
1913 PROCEDURE get_prospect_class_codes
1914 ( p_supp_reg_id IN NUMBER,
1915 x_class_codes_tbl OUT NOCOPY EGO_VARCHAR_TBL_TYPE
1916 )
1917 IS
1918
1919 l_mapping_id pos_supplier_mappings.mapping_id%TYPE;
1920 l_party_id pos_supplier_registrations.vendor_party_id%TYPE;
1921 l_supplier_type pos_supplier_registrations.supplier_type%TYPE;
1922 l_ps_rec pos_product_service_requests%ROWTYPE;
1923 l_segment pos_product_service_requests.segment1%TYPE;
1924
1925 l_ps_segment_def fnd_profile_option_values.profile_option_value%TYPE;
1926 l_ps_segment_count NUMBER;
1927 l_category_set_id NUMBER;
1928 l_ps_delimiter VARCHAR2(1);
1929 l_ps_code VARCHAR2(1000);
1930 l_start NUMBER;
1931 l_end NUMBER;
1932
1933 TYPE ps_segments_tbl_type IS TABLE OF NUMBER;
1934 l_ps_segments_tbl ps_segments_tbl_type;
1935
1936 TYPE ps_code_tbl_type IS TABLE OF NUMBER INDEX BY VARCHAR2(1000);
1937 l_ps_code_tbl ps_code_tbl_type;
1938
1939
1940 CURSOR c_mapping_id(p_supp_reg_id IN NUMBER) IS
1941 SELECT mapping_id
1942 FROM pos_supplier_mappings
1943 WHERE supplier_reg_id = p_supp_reg_id;
1944
1945 CURSOR c_party_id(p_supp_reg_id IN NUMBER) IS
1946 SELECT vendor_party_id
1947 FROM pos_supplier_registrations
1948 WHERE supplier_reg_id = p_supp_reg_id;
1949
1950 CURSOR c_supplier_type(p_supp_reg_id IN NUMBER) IS
1951 SELECT supplier_type
1952 FROM pos_supplier_registrations
1953 WHERE supplier_reg_id = p_supp_reg_id;
1954
1955 CURSOR c_bus_class(p_mapping_id IN NUMBER) IS
1956 SELECT 'BC:' || lookup_code AS code
1957 FROM pos_bus_class_reqs
1958 WHERE mapping_id = p_mapping_id;
1959
1960 CURSOR c_product_service(p_mapping_id IN NUMBER) IS
1961 SELECT *
1962 FROM pos_product_service_requests
1963 WHERE mapping_id = p_mapping_id;
1964
1965 CURSOR c_tca_class(p_party_id IN NUMBER) IS
1966 SELECT 'HZ:' || REPLACE(hccr.class_category, ' ', '$')
1967 || ':'
1968 || hccr.class_code AS code
1969 FROM hz_class_code_relations hccr,
1970 (SELECT class_category, class_code, owner_table_id
1971 FROM hz_code_assignments
1972 WHERE owner_table_name = 'HZ_PARTIES'
1973 AND owner_table_id = p_party_id
1974 AND start_date_active <= SYSDATE
1975 AND NVL(end_date_active, SYSDATE) >= SYSDATE
1976 AND status = 'A'
1977 ) v
1978 WHERE hccr.class_category = v.class_category
1979 START WITH hccr.class_code = v.class_code
1980 CONNECT BY PRIOR hccr.class_code = hccr.sub_class_code
1981 UNION
1982 SELECT 'HZ:' || REPLACE(fnd.lookup_type, ' ', '$')
1983 || ':'
1984 || fnd.lookup_code AS code
1985 FROM fnd_lookup_values_vl fnd,
1986 (SELECT class_category, class_code, owner_table_id
1987 FROM hz_code_assignments
1988 WHERE owner_table_name = 'HZ_PARTIES'
1989 AND owner_table_id = p_party_id
1990 AND start_date_active <= SYSDATE
1991 AND NVL(end_date_active, SYSDATE) >= SYSDATE
1992 AND status = 'A'
1993 ) v
1994 WHERE fnd.lookup_type = v.class_category
1995 AND fnd.lookup_code = v.class_code;
1996
1997
1998 BEGIN
1999
2000 x_class_codes_tbl := EGO_VARCHAR_TBL_TYPE();
2001
2002 -- Mapping Id
2003 OPEN c_mapping_id(p_supp_reg_id);
2004 FETCH c_mapping_id INTO l_mapping_id;
2005 CLOSE c_mapping_id;
2006
2007 IF (l_mapping_id IS NULL) THEN
2008 RETURN;
2009 END IF;
2010
2011
2012 -- Common
2013 x_class_codes_tbl.EXTEND();
2014 x_class_codes_tbl(x_class_codes_tbl.LAST) := 'BS:BASE';
2015
2016
2017 -- Supplier Type
2018 OPEN c_supplier_type(p_supp_reg_id);
2019 FETCH c_supplier_type INTO l_supplier_type;
2020 CLOSE c_supplier_type;
2021
2022 IF (l_supplier_type IS NOT NULL) THEN
2023 x_class_codes_tbl.EXTEND();
2024 x_class_codes_tbl(x_class_codes_tbl.LAST) := 'ST:' || l_supplier_type;
2025 END IF;
2026
2027
2028 -- Business Classifications
2029 FOR l_bus_class_rec IN c_bus_class(l_mapping_id) LOOP
2030 x_class_codes_tbl.EXTEND();
2031 x_class_codes_tbl(x_class_codes_tbl.LAST) := l_bus_class_rec.code;
2032 END LOOP;
2033
2034
2035 -- Products and Services
2036 pos_product_service_utl_pkg.get_product_meta_data(l_ps_segment_def,
2037 l_ps_segment_count,
2038 l_category_set_id,
2039 l_ps_delimiter);
2040
2041 -- split l_ps_segment_def to l_ps_segments_tbl
2042 -- e.g., from 1.2.3.4 to (1, 2, 3, 4)
2043 l_ps_segments_tbl := ps_segments_tbl_type();
2044 l_start := 1;
2045 FOR i IN 1..l_ps_segment_count LOOP
2046 l_end := INSTR(l_ps_segment_def, '.', 1, i);
2047 IF (l_end = 0) THEN
2048 l_end := LENGTH(l_ps_segment_def) + 1;
2049 END IF;
2050
2051 l_ps_segments_tbl.EXTEND();
2052 l_ps_segments_tbl(l_ps_segments_tbl.LAST) :=
2053 SUBSTR(l_ps_segment_def, l_start, l_end - l_start);
2054
2055 l_start := l_end + 1;
2056 END LOOP;
2057
2058 OPEN c_product_service(l_mapping_id);
2059 LOOP
2060 FETCH c_product_service INTO l_ps_rec;
2061 EXIT WHEN c_product_service%NOTFOUND;
2062
2063 -- Construct the Products and Services codes, with parent codes
2064 -- Using the associative array l_ps_code_tbl as a hashset, so no
2065 -- duplicate is added
2066
2067 l_ps_code := 'PS:';
2068 FOR i IN 1..l_ps_segments_tbl.COUNT LOOP
2069
2070 l_segment := NULL;
2071 CASE l_ps_segments_tbl(i)
2072 WHEN 1 THEN l_segment := l_ps_rec.segment1;
2073 WHEN 2 THEN l_segment := l_ps_rec.segment2;
2074 WHEN 3 THEN l_segment := l_ps_rec.segment3;
2075 WHEN 4 THEN l_segment := l_ps_rec.segment4;
2076 WHEN 5 THEN l_segment := l_ps_rec.segment5;
2077 WHEN 6 THEN l_segment := l_ps_rec.segment6;
2078 WHEN 7 THEN l_segment := l_ps_rec.segment7;
2079 WHEN 8 THEN l_segment := l_ps_rec.segment8;
2080 WHEN 9 THEN l_segment := l_ps_rec.segment9;
2081 WHEN 10 THEN l_segment := l_ps_rec.segment10;
2082 WHEN 11 THEN l_segment := l_ps_rec.segment11;
2083 WHEN 12 THEN l_segment := l_ps_rec.segment12;
2084 WHEN 13 THEN l_segment := l_ps_rec.segment13;
2085 WHEN 14 THEN l_segment := l_ps_rec.segment14;
2086 WHEN 15 THEN l_segment := l_ps_rec.segment15;
2087 WHEN 16 THEN l_segment := l_ps_rec.segment16;
2088 WHEN 17 THEN l_segment := l_ps_rec.segment17;
2089 WHEN 18 THEN l_segment := l_ps_rec.segment18;
2090 WHEN 19 THEN l_segment := l_ps_rec.segment19;
2091 WHEN 20 THEN l_segment := l_ps_rec.segment20;
2092 END CASE;
2093
2094 IF (l_segment IS NOT NULL) THEN
2095 IF (i > 1) THEN
2096 l_ps_code := l_ps_code || l_ps_delimiter;
2097 END IF;
2098 l_ps_code := l_ps_code || l_segment;
2099 l_ps_code_tbl(l_ps_code) := 1;
2100 END IF;
2101
2102 END LOOP; -- l_ps_segments_tbl
2103
2104 END LOOP;
2105 CLOSE c_product_service;
2106
2107 l_ps_code := l_ps_code_tbl.FIRST;
2108 WHILE l_ps_code IS NOT NULL LOOP
2109 x_class_codes_tbl.EXTEND();
2110 x_class_codes_tbl(x_class_codes_tbl.LAST) := l_ps_code;
2111 l_ps_code := l_ps_code_tbl.NEXT(l_ps_code);
2112 END LOOP;
2113
2114
2115 -- General and Industrial Classifications
2116 OPEN c_party_id(p_supp_reg_id);
2117 FETCH c_party_id INTO l_party_id;
2118 CLOSE c_party_id;
2119
2120 IF (l_party_id IS NOT NULL) THEN
2121 FOR l_tca_class_rec IN c_tca_class(l_party_id) LOOP
2122 x_class_codes_tbl.EXTEND();
2123 x_class_codes_tbl(x_class_codes_tbl.LAST) := l_tca_class_rec.code;
2124 END LOOP;
2125 END IF;
2126
2127 END get_prospect_class_codes;
2128
2129 /*
2130 * Return a list of prospective supplier's required UDA that does not have
2131 * value, in the following format:
2132 *
2133 * x_attr_req_tbl := EGO_VARCHAR_TBL_TYPE(<page_display_name 1>,
2134 * <attribute_group_display_name 1>,
2135 * <attribute_display_name 1>,
2136 * <page_display_name 2>,
2137 * <attribute_group_display_name 2>,
2138 * <attribute_display_name 2>,
2139 * ...etc...
2140 * <page_display_name n>,
2141 * <attribute_group_display_name n>,
2142 * <attribute_display_name n>);
2143 */
2144 PROCEDURE validate_required_user_attrs
2145 ( p_supp_reg_id IN NUMBER,
2146 p_buyer_user IN VARCHAR2,
2147 x_attr_req_tbl OUT NOCOPY EGO_VARCHAR_TBL_TYPE,
2148 x_return_status OUT NOCOPY VARCHAR2,
2149 x_msg_count OUT NOCOPY NUMBER,
2150 x_msg_data OUT NOCOPY VARCHAR2
2151 )
2152 IS
2153
2154 TYPE ext_attr_csr_type IS REF CURSOR;
2155 TYPE ext_attr_rec_type IS RECORD
2156 ( page_disp_name ego_pages_tl.display_name%TYPE,
2157 attr_group_id ego_attr_groups_v.attr_group_id%TYPE,
2158 attr_group_disp_name ego_attr_groups_v.attr_group_disp_name%TYPE,
2159 attr_name ego_attrs_v.attr_name%TYPE,
2160 attr_disp_name ego_attrs_v.attr_display_name%TYPE
2161 );
2162 TYPE ext_attr_tbl_type IS TABLE OF ext_attr_rec_type;
2163
2164 TYPE attr_name_list_tbl_type IS TABLE OF VARCHAR2(3000)
2165 INDEX BY BINARY_INTEGER;
2166
2167 TYPE has_value_tbl_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
2168
2169 l_object_id NUMBER;
2170 l_pages_list VARCHAR2(4000);
2171 l_class_codes_tbl EGO_VARCHAR_TBL_TYPE;
2172 l_class_codes_list VARCHAR2(4000);
2173
2174 l_return_status VARCHAR2(1);
2175 l_privileges VARCHAR2(32767);
2176
2177 l_ext_attr_query VARCHAR2(32767);
2178 l_ext_attr_csr ext_attr_csr_type;
2179 l_ext_attr_tbl ext_attr_tbl_type;
2180 l_attr_group_id ego_attr_groups_v.attr_group_id%TYPE;
2181 l_attr_name ego_attrs_v.attr_name%TYPE;
2182
2183 l_attr_name_list_tbl attr_name_list_tbl_type;
2184 l_attr_name_list VARCHAR2(3000);
2185 l_attr_group_request_tbl EGO_ATTR_GROUP_REQUEST_TABLE;
2186 l_attr_group_request_obj EGO_ATTR_GROUP_REQUEST_OBJ;
2187 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
2188 l_attributes_row_tbl EGO_USER_ATTR_ROW_TABLE;
2189 l_attributes_row_obj EGO_USER_ATTR_ROW_OBJ;
2190 l_attributes_data_tbl EGO_USER_ATTR_DATA_TABLE;
2191 l_errorcode NUMBER;
2192
2193 l_has_value_tbl has_value_tbl_type;
2194
2195
2196 CURSOR c_pages_list(p_supp_reg_id IN NUMBER, p_buyer_user IN VARCHAR2) IS
2197 SELECT pac.page_id
2198 FROM pos_attrpg_config pac,
2199 pos_supplier_registrations psr
2200 WHERE psr.supplier_reg_id = p_supp_reg_id
2201 AND pac.org_id IN (-999, psr.ou_id)
2202 AND DECODE(p_buyer_user, 'Y', pac.internal_update_flag,
2203 'N', pac.supplier_update_flag) = 'Y'
2204 AND pac.page_id NOT IN
2205 (SELECT pac2.page_id
2206 FROM pos_attrpg_config pac2
2207 WHERE pac2.org_id = psr.ou_id
2208 AND DECODE(p_buyer_user, 'Y', pac2.internal_update_flag,
2209 'N', pac2.supplier_update_flag) = 'N'
2210 );
2211
2212 BEGIN
2213
2214 x_return_status := FND_API.G_RET_STS_SUCCESS;
2215
2216
2217 l_object_id := EGO_EXT_FWK_PUB.Get_Object_Id_From_Name('HZ_PARTIES');
2218
2219
2220 -- Pages List
2221 FOR l_page_rec IN c_pages_list(p_supp_reg_id, p_buyer_user) LOOP
2222 l_pages_list := l_pages_list || l_page_rec.page_id || ',';
2223 END LOOP;
2224
2225 l_pages_list := SUBSTR(l_pages_list, 1, LENGTH(l_pages_list) - 1);
2226
2227 -- Bug 13956982
2228 -- If there are no pages, then there is no required UDA to validate.
2229 IF (l_pages_list IS NULL) THEN
2230 RETURN;
2231 END IF;
2232
2233
2234 -- Classification Codes
2235 get_prospect_class_codes(p_supp_reg_id, l_class_codes_tbl);
2236
2237 FOR i IN 1..l_class_codes_tbl.COUNT LOOP
2238 l_class_codes_list := l_class_codes_list || '''' ||
2239 l_class_codes_tbl(i) || ''',';
2240 END LOOP;
2241
2242 l_class_codes_list := SUBSTR(l_class_codes_list, 1,
2243 LENGTH(l_class_codes_list) - 1);
2244
2245 -- Bug 15982192
2246 -- Added support for UDA security
2247 pos_data_security.get_privileges_prosp(p_supp_reg_id,
2248 fnd_global.user_id,
2249 l_return_status,
2250 l_privileges);
2251 l_privileges := '''' || REPLACE(l_privileges, ',', ''',''') || '''';
2252
2253 -- Bug 13953145
2254 -- Removed the following condition from the below query:
2255 -- ' AND ag.multi_row_code = ''N'' ' ||
2256
2257 -- Required Attributes List
2258 l_ext_attr_query :=
2259 'SELECT ptl.display_name, ' ||
2260 ' ag.attr_group_id, ' ||
2261 ' ag.attr_group_disp_name, ' ||
2262 ' attr.attr_name, ' ||
2263 ' attr.attr_display_name ' ||
2264 'FROM ego_pages_b pb, ' ||
2265 ' ego_pages_tl ptl, ' ||
2266 ' ego_page_entries_b pe, ' ||
2267 ' ego_obj_ag_assocs_b a, ' ||
2268 ' ego_attr_groups_v ag, ' ||
2269 ' ego_attrs_v attr, ' ||
2270 ' ego_attr_group_dl agdl ' ||
2271 'WHERE ptl.page_id = pb.page_id ' ||
2272 ' AND ptl.language = USERENV(''LANG'') ' ||
2273 ' AND pe.page_id = pb.page_id ' ||
2274 ' AND a.association_id = pe.association_id ' ||
2275 ' AND a.enabled_flag = ''Y'' ' ||
2276 ' AND ag.attr_group_id = a.attr_group_id ' ||
2277 ' AND attr.application_id = ag.application_id ' ||
2278 ' AND attr.attr_group_type = ag.attr_group_type ' ||
2279 ' AND attr.attr_group_name = ag.attr_group_name ' ||
2280 ' AND attr.enabled_flag = ''Y'' ' ||
2281 ' AND attr.required_flag = ''Y'' ' ||
2282 ' AND agdl.attr_group_id = ag.attr_group_id ' ||
2283 ' AND (agdl.edit_privilege_id IS NULL OR ' ||
2284 ' agdl.edit_privilege_id IN ( ' ||
2285 ' SELECT function_id ' ||
2286 ' FROM fnd_form_functions ' ||
2287 ' WHERE function_name IN (' || l_privileges || ')) ' ||
2288 ' ) ' ||
2289 ' AND pb.object_id = ' || l_object_id ||
2290 ' AND pb.data_level = ''SUPP_LEVEL'' ' ||
2291 ' AND pb.page_id IN (' || l_pages_list || ') ' ||
2292 ' AND pb.classification_code IN (' || l_class_codes_list || ') ' ||
2293 'ORDER BY pb.sequence, pe.sequence, attr.sequence';
2294
2295 OPEN l_ext_attr_csr FOR l_ext_attr_query;
2296 FETCH l_ext_attr_csr BULK COLLECT INTO l_ext_attr_tbl;
2297 CLOSE l_ext_attr_csr;
2298
2299
2300 -- Build Request Table
2301 FOR i IN 1..l_ext_attr_tbl.COUNT LOOP
2302 l_attr_group_id := l_ext_attr_tbl(i).attr_group_id;
2303 l_attr_name := l_ext_attr_tbl(i).attr_name;
2304
2305 -- Bug 15982192
2306 -- Removed the ELSIF condition
2307 IF (NOT l_attr_name_list_tbl.EXISTS(l_attr_group_id)) THEN
2308 l_attr_name_list_tbl(l_attr_group_id) := l_attr_name;
2309 ELSE
2310 l_attr_name_list_tbl(l_attr_group_id) :=
2311 l_attr_name_list_tbl(l_attr_group_id) || ',' || l_attr_name;
2312 END IF;
2313 END LOOP;
2314
2315 l_attr_group_request_tbl := EGO_ATTR_GROUP_REQUEST_TABLE();
2316
2317 l_attr_group_id := l_attr_name_list_tbl.FIRST;
2318 WHILE l_attr_group_id IS NOT NULL LOOP
2319 l_attr_name_list := l_attr_name_list_tbl(l_attr_group_id);
2320
2321 -- Bug 13956982
2322 -- Due to changes from EGOPEFDB.pls version 120.65.12010000.44 onwards,
2323 -- need to pass 'Y' instead of '''Y''' for DATA_LEVEL_1 field.
2324 l_attr_group_request_tbl.EXTEND();
2325 l_attr_group_request_tbl(l_attr_group_request_tbl.LAST) :=
2326 EGO_ATTR_GROUP_REQUEST_OBJ(
2327 l_attr_group_id, -- ATTR_GROUP_ID
2328 NULL, -- APPLICATION_ID
2329 NULL, -- ATTR_GROUP_TYPE
2330 NULL, -- ATTR_GROUP_NAME
2331 'SUPP_LEVEL', -- DATA_LEVEL
2332 'Y', -- DATA_LEVEL_1
2333 NULL, -- DATA_LEVEL_2
2334 NULL, -- DATA_LEVEL_3
2335 NULL, -- DATA_LEVEL_4
2336 NULL, -- DATA_LEVEL_5
2337 l_attr_name_list -- ATTR_NAME_LIST
2338 );
2339
2340 l_attr_group_id := l_attr_name_list_tbl.NEXT(l_attr_group_id);
2341 END LOOP;
2342
2343
2344 -- Get Attribute Values
2345 l_pk_column_values :=
2346 EGO_COL_NAME_VALUE_PAIR_ARRAY(
2347 EGO_COL_NAME_VALUE_PAIR_OBJ('PARTY_ID', TO_CHAR(p_supp_reg_id))
2348 );
2349
2350 EGO_USER_ATTRS_DATA_PVT.Get_User_Attrs_Data
2351 ( p_api_version => 1.0,
2352 p_object_name => 'HZ_PARTIES',
2353 p_pk_column_name_value_pairs => l_pk_column_values,
2354 p_attr_group_request_table => l_attr_group_request_tbl,
2355 x_attributes_row_table => l_attributes_row_tbl,
2356 x_attributes_data_table => l_attributes_data_tbl,
2357 x_return_status => x_return_status,
2358 x_errorcode => l_errorcode,
2359 x_msg_count => x_msg_count,
2360 x_msg_data => x_msg_data
2361 );
2362
2363 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2364 RETURN;
2365 END IF;
2366
2367 -- If attribute has value, add an entry in l_has_value_tbl
2368 IF (l_attributes_row_tbl IS NOT NULL AND l_attributes_data_tbl IS NOT NULL)
2369 THEN
2370 FOR i IN 1..l_attributes_data_tbl.COUNT LOOP
2371
2372 IF (l_attributes_data_tbl(i).attr_value_str IS NOT NULL OR
2373 l_attributes_data_tbl(i).attr_value_num IS NOT NULL OR
2374 l_attributes_data_tbl(i).attr_value_date IS NOT NULL OR
2375 l_attributes_data_tbl(i).attr_disp_value IS NOT NULL)
2376 THEN
2377
2378 FOR j IN 1..l_attributes_row_tbl.COUNT LOOP
2379
2380 IF (l_attributes_row_tbl(j).row_identifier =
2381 l_attributes_data_tbl(i).row_identifier)
2382 THEN
2383 l_attr_group_id := l_attributes_row_tbl(j).attr_group_id;
2384 l_attr_name := l_attributes_data_tbl(i).attr_name;
2385 l_has_value_tbl(l_attr_group_id || '|' || l_attr_name) := 1;
2386 EXIT;
2387 END IF;
2388
2389 END LOOP; -- l_attributes_row_tbl
2390
2391 END IF;
2392
2393 END LOOP; -- l_attributes_data_tbl
2394 END IF;
2395
2396
2397 -- Validate Required Attributes
2398 x_attr_req_tbl := EGO_VARCHAR_TBL_TYPE();
2399
2400 FOR i IN 1..l_ext_attr_tbl.COUNT LOOP
2401 l_attr_group_id := l_ext_attr_tbl(i).attr_group_id;
2402 l_attr_name := l_ext_attr_tbl(i).attr_name;
2403
2404 IF (NOT l_has_value_tbl.EXISTS(l_attr_group_id || '|' || l_attr_name))
2405 THEN
2406 x_attr_req_tbl.EXTEND(3);
2407
2408 x_attr_req_tbl(x_attr_req_tbl.LAST - 2) :=
2409 l_ext_attr_tbl(i).page_disp_name;
2410
2411 x_attr_req_tbl(x_attr_req_tbl.LAST - 1) :=
2412 l_ext_attr_tbl(i).attr_group_disp_name;
2413
2414 x_attr_req_tbl(x_attr_req_tbl.LAST) :=
2415 l_ext_attr_tbl(i).attr_disp_name;
2416 END IF;
2417 END LOOP;
2418
2419 EXCEPTION
2420 WHEN OTHERS THEN
2421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2422 x_msg_data := SQLERRM;
2423
2424 END validate_required_user_attrs;
2425 -- End Supplier Management: Bug 12849540
2426
2427 END POS_VENDOR_REG_PKG;