[Home] [Help]
PACKAGE BODY: APPS.POS_SPM_WF_PKG1
Source
1 PACKAGE BODY pos_spm_wf_pkg1 AS
2 /* $Header: POSSPM1B.pls 120.30.12010000.8 2008/11/17 15:19:38 suyjoshi ship $ */
3
4 TYPE g_refcur IS ref CURSOR;
5
6 g_package_name CONSTANT VARCHAR2(30) := 'POS_SPM_WF_PKG1';
7
8 g_log_module CONSTANT VARCHAR2(30) := 'POSSPM1B';
9
10 g_supplier_function_name CONSTANT fnd_form_functions.function_name%TYPE := 'POS_HT_SP_S_SUP_DET';
11
12 g_new_line CONSTANT VARCHAR2(1) := '
13 ';
14
15 FUNCTION get_function_id (p_function_name IN VARCHAR2) RETURN NUMBER IS
16 CURSOR l_cur IS
17 SELECT function_id
18 FROM fnd_form_functions
19 WHERE function_name = p_function_name;
20 l_function_id NUMBER;
21 BEGIN
22 OPEN l_cur;
23 FETCH l_cur INTO l_function_id;
24 IF l_cur%notfound THEN
25 CLOSE l_cur;
26 RAISE no_data_found;
27 END IF;
28 CLOSE l_cur;
29 RETURN l_function_id;
30 END get_function_id;
31
32 -- utility to add user to a workflow adhoc role
33 -- using the new procedure wf_directory.addUserToAdHocRle2
34 PROCEDURE addusertoadhocrole
35 (p_role_name IN VARCHAR2,
36 p_user_name IN VARCHAR2)
37 IS
38 l_user_table wf_directory.usertable;
39 BEGIN
40 l_user_table(1) := p_user_name;
41 wf_directory.adduserstoadhocrole2(p_role_name, l_user_table);
42 END addusertoadhocrole;
43
44 PROCEDURE get_enterprise_name(x_name OUT nocopy VARCHAR2)
45 IS
46 l_status VARCHAR2(2);
47 l_msg VARCHAR2(1000);
48 BEGIN
49 pos_enterprise_util_pkg.get_enterprise_party_name
50 (x_party_name => x_name,
51 x_exception_msg => l_msg,
52 x_status => l_status);
53 IF l_status IS NULL OR l_status <> 'S' THEN
54 RAISE no_data_found;
55 END IF;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 raise_application_error(-20001, 'error in get_enterprise_name', TRUE);
60 END get_enterprise_name;
61
62 PROCEDURE get_supplier_name
63 (p_vendor_id IN NUMBER,
64 x_vendor_name OUT nocopy VARCHAR2)
65 IS
66 CURSOR l_cur IS
67 SELECT vendor_name
68 FROM ap_suppliers
69 WHERE vendor_id = p_vendor_id;
70 BEGIN
71 OPEN l_cur;
72 FETCH l_cur INTO x_vendor_name;
73 IF l_cur%notfound THEN
74 CLOSE l_cur;
75 RAISE no_data_found;
76 END IF;
77 CLOSE l_cur;
78 END get_supplier_name;
79
80 PROCEDURE get_wf_item_type
81 (x_itemtype OUT NOCOPY VARCHAR2)
82 IS
83 BEGIN
84 x_itemtype := 'POSSPM1';
85 END get_wf_item_type;
86
87 -- generate a workflow item key
88 PROCEDURE get_wf_item_key
89 (p_process IN VARCHAR2,
90 p_idstr IN VARCHAR2,
91 x_itemkey OUT NOCOPY VARCHAR2)
92 IS
93 BEGIN
94 x_itemkey := 'POSSPM1_' || p_process || '_' || p_idstr || '_' || fnd_crypto.smallrandomnumber;
95 END get_wf_item_key;
96
97 PROCEDURE get_adhoc_role_name
98 (p_process IN VARCHAR2,
99 x_name OUT nocopy VARCHAR2) IS
100 --l_name wf_roles.name%TYPE;
101 --l_index NUMBER := 1;
102 BEGIN
103 x_name := 'POSSPM1_' || p_process || '_' ||
104 To_char(Sysdate, 'MMDDYYYY_HH24MISS') || '_' || fnd_crypto.smallrandomnumber;
105 -- bug 3569374
106 --l_name := x_name;
107 --WHILE(wf_directory.getroledisplayname(l_name) IS NOT NULL) LOOP
108 -- l_name := x_name || '_' || l_index;
109 -- l_index := l_index + 1;
110 --END LOOP;
111 --x_name := l_name;
112 END get_adhoc_role_name;
113
114 PROCEDURE create_adhoc_role
115 (p_process IN VARCHAR2,
116 x_name OUT nocopy VARCHAR2) IS
117 BEGIN
118 get_adhoc_role_name(p_process,x_name);
119 wf_directory.CreateAdHocRole
120 (role_name => x_name,
121 role_display_name => x_name);
122 END create_adhoc_role;
123
124 PROCEDURE add_user_to_role_from_cur
125 (p_refcur IN g_refcur,
126 p_role IN VARCHAR2,
127 x_count OUT nocopy NUMBER
128 ) IS
129 l_count NUMBER;
130 l_name wf_roles.name%TYPE;
131 BEGIN
132 l_count := 0;
133 WHILE TRUE LOOP
134 FETCH p_refcur INTO l_name;
135 IF p_refcur%notfound THEN
136 EXIT;
137 END IF;
138
139 BEGIN
140 -- It is possible for the next call to fail
141 -- under some cases. For example, if you
142 -- just create a new user and the system
143 -- has not sync that to the workflow directory,
144 -- you would get an exception for name not valid.
145 --
146 -- In such case, we do not want to stop sending
147 -- notification. We would just log the error.
148 --
149 AddUserToAdHocRole(p_role, l_name);
150 l_count := l_count + 1;
151 EXCEPTION
152 WHEN OTHERS THEN
153 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
154 fnd_log.string(fnd_log.level_exception,
155 g_package_name || '.add_user_to_role_from_cur',
156 'AddUserToAdHocRole failed with error '
157 || sqlerrm);
158 END IF;
159 END;
160 END LOOP;
161 CLOSE p_refcur;
162 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
163 fnd_log.string(fnd_log.level_statement,
164 g_log_module || '.add_user_to_role_from_cur',
165 'count is ' || l_count);
166 END IF;
167 x_count := l_count;
168 END add_user_to_role_from_cur;
169
170 PROCEDURE get_user_name
171 (p_user_id IN NUMBER,
172 x_first_name OUT nocopy VARCHAR2,
173 x_last_name OUT nocopy VARCHAR2)
174 IS
175 CURSOR l_per_cur IS
176 SELECT ppf.first_name, ppf.last_name
177 FROM per_people_f ppf, fnd_user fu
178 WHERE fu.employee_id = ppf.person_id
179 AND fu.user_id = p_user_id;
180 CURSOR l_tca_cur IS
181 SELECT hp.person_first_name, hp.person_last_name
182 FROM hz_parties hp, fnd_user fu
183 WHERE fu.person_party_id = hp.party_id
184 AND fu.user_id = p_user_id;
185 CURSOR l_fnd_cur IS
186 SELECT user_name FROM fnd_user WHERE user_id = p_user_id;
187 l_found BOOLEAN;
188 BEGIN
189 l_found := FALSE;
190 -- query the user's employee name.
191 OPEN l_per_cur;
192 FETCH l_per_cur INTO x_first_name, x_last_name;
193 l_found := l_per_cur%found;
194 CLOSE l_per_cur;
195 IF l_found THEN
196 RETURN;
197 END IF;
198
199 -- query the user's party name.
200 OPEN l_tca_cur;
201 FETCH l_tca_cur INTO x_first_name, x_last_name;
202 l_found := l_tca_cur%found;
203 CLOSE l_tca_cur;
204 IF l_found THEN
205 RETURN;
206 END IF;
207
208 -- query the user's username
209 OPEN l_fnd_cur;
210 FETCH l_fnd_cur INTO x_first_name;
211 x_last_name := NULL;
212 l_found := l_fnd_cur%found;
213 CLOSE l_fnd_cur;
214
215 IF l_found = FALSE THEN
216 RAISE no_data_found;
217 END IF;
218 END get_user_name;
219
220 PROCEDURE get_current_user_name
221 (x_first_name OUT nocopy VARCHAR2,
222 x_last_name OUT nocopy VARCHAR2)
223 IS
224 BEGIN
225 get_user_name(fnd_global.user_id, x_first_name, x_last_name);
226 END get_current_user_name;
227
228 PROCEDURE get_user_company_name
229 (p_username IN VARCHAR2,
230 x_company_name OUT nocopy VARCHAR2)
231 IS
232 CURSOR l_per_cur IS
233 SELECT employee_id
234 FROM fnd_user
235 WHERE user_name = p_username;
236 l_employee_id NUMBER;
237 l_vendor_id NUMBER;
238 BEGIN
239 OPEN l_per_cur;
240 FETCH l_per_cur INTO l_employee_id;
241 IF l_per_cur%notfound THEN
242 l_employee_id := NULL;
243 END IF;
244 CLOSE l_per_cur;
245
246 IF l_employee_id IS NOT NULL THEN
247 get_enterprise_name (x_company_name);
248 ELSE
249 l_vendor_id := pos_vendor_util_pkg.get_po_vendor_id_for_user(p_username);
250 IF l_vendor_id IS NOT NULL THEN
251 get_supplier_name(l_vendor_id, x_company_name);
252 ELSE
253 x_company_name := NULL;
254 END IF;
255 END IF;
256 END get_user_company_name;
257
258 PROCEDURE get_current_user_company_name
259 (x_company_name OUT nocopy VARCHAR2)
260 IS
261 BEGIN
262 get_user_company_name(fnd_global.user_name, x_company_name);
263 END get_current_user_company_name;
264
265 -- This is a private procedure for setting up the
266 -- value for the <first name>, <last name> of <company name>
267 -- item attributes for some of the notifications that have
268 -- that in the message body
269 --
270 -- Note: this is not a generic procedure, and should
271 -- not be included in the package spec.
272 PROCEDURE setup_actioner_private
273 (itemtype IN VARCHAR2,
274 itemkey IN VARCHAR2
275 )
276 IS
277 l_company_name ap_suppliers.vendor_name%TYPE;
278 l_first_name hz_parties.person_first_name%TYPE;
279 l_last_name hz_parties.person_last_name%TYPE;
280 BEGIN
281 get_current_user_name(l_first_name, l_last_name);
282 get_current_user_company_name(l_company_name);
283
284 wf_engine.SetItemAttrText (itemtype => itemtype,
285 itemkey => itemkey,
286 aname => 'COMPANY_NAME',
287 avalue => l_company_name);
288
289
290 wf_engine.SetItemAttrText (itemtype => itemtype,
291 itemkey => itemkey,
292 aname => 'FIRST_NAME',
293 avalue => l_first_name);
294
295 wf_engine.SetItemAttrText (itemtype => itemtype,
296 itemkey => itemkey,
297 aname => 'LAST_NAME',
298 avalue => l_last_name);
299
300 END setup_actioner_private;
301
302 PROCEDURE get_buyers
303 (p_event_type IN VARCHAR2,
304 x_cur OUT nocopy g_refcur
305 )
306 IS
307 l_sql VARCHAR2(1000);
308 BEGIN
309 OPEN x_cur FOR
310 SELECT user_name
311 FROM fnd_user fu, pos_spmntf_subscription sub
312 WHERE fu.user_id = sub.user_id
313 AND sub.event_type = p_event_type;
314 RETURN;
315 END get_buyers;
316
317 FUNCTION get_address_name_in_req(p_address_request_id IN NUMBER) RETURN VARCHAR2
318 IS
319 CURSOR l_cur IS
320 SELECT Decode(par.party_site_id, NULL, par.party_site_name,
321 (SELECT hps.party_site_name
322 FROM hz_party_sites hps
323 WHERE hps.party_site_id = par.party_site_id
324 )) address_name
325 FROM pos_address_requests par
326 WHERE par.address_request_id = p_address_request_id;
327
328 l_rec l_cur%ROWTYPE;
329
330 BEGIN
331 OPEN l_cur;
332 FETCH l_cur INTO l_rec;
333 IF l_cur%notfound THEN
334 CLOSE l_cur;
335 RAISE no_data_found;
336 END IF;
337 CLOSE l_cur;
338 RETURN l_rec.address_name;
339
340 END get_address_name_in_req;
341
342 -- this is a private procedure. not intended to be public
343 PROCEDURE notify_addr_events
344 (p_vendor_id IN NUMBER,
345 p_address_request_id IN NUMBER,
346 p_wf_process IN VARCHAR2,
347 x_itemtype OUT nocopy VARCHAR2,
348 x_itemkey OUT nocopy VARCHAR2,
349 x_receiver OUT nocopy VARCHAR2
350 )
351 IS
352 l_supplier_name ap_suppliers.vendor_name%TYPE;
353 l_address_name hz_party_sites.party_site_name%TYPE;
354 l_itemtype wf_items.item_type%TYPE;
355 l_itemkey wf_items.item_key%TYPE;
356 l_receiver wf_roles.name%TYPE;
357 l_cur g_refcur;
358 l_count NUMBER;
359 l_process wf_process_activities.process_name%TYPE;
360 l_step NUMBER;
361 BEGIN
362 l_step := 0;
363 get_supplier_name(p_vendor_id, l_supplier_name);
364
365 l_step := 1;
366 l_address_name := get_address_name_in_req(p_address_request_id);
367
368 l_process := p_wf_process;
369
370 -- setup receiver
371 create_adhoc_role(l_process || '_' || p_address_request_id, l_receiver);
372
373 l_step := 3;
374 get_buyers('SUPP_ADDR_CHANGE_REQ',l_cur);
375
376 l_step := 4;
377 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
378 IF l_count < 1 THEN
379 -- there is no one to notify, so we just return
380 x_itemtype := NULL;
381 x_itemkey := NULL;
382 x_receiver := NULL;
383 RETURN;
384 END IF;
385
386 l_step := 5;
387 -- create workflow process
388 get_wf_item_type (l_itemtype);
389 get_wf_item_key (l_process,
390 To_char(p_vendor_id) || '_' || p_address_request_id,
391 l_itemkey);
392
393 wf_engine.CreateProcess(itemtype => l_itemtype,
394 itemkey => l_itemkey,
395 process => l_process);
396 l_step := 6;
397 wf_engine.SetItemAttrText (itemtype => l_itemtype,
398 itemkey => l_itemkey,
399 aname => 'RECEIVER',
400 avalue => l_receiver);
401
402 wf_engine.SetItemAttrText (itemtype => l_itemtype,
403 itemkey => l_itemkey,
404 aname => 'SUPPLIER_NAME',
405 avalue => l_supplier_name);
406
407 wf_engine.SetItemAttrText (itemtype => l_itemtype,
408 itemkey => l_itemkey,
409 aname => 'ADDRESS_NAME',
410 avalue => l_address_name);
411 wf_engine.SetItemAttrText (itemtype => l_itemtype,
412 itemkey => l_itemkey,
413 aname => 'VENDOR_ID',
414 avalue => p_vendor_id);
415
416 wf_engine.SetItemAttrText (itemtype => l_itemtype,
417 itemkey => l_itemkey,
418 aname => 'URL',
419 avalue => pos_url_pkg.get_dest_page_url('POS_HT_SP_B_ADDR_BK', 'BUYER'));
420 l_step := 7;
421 setup_actioner_private(l_itemtype, l_itemkey);
422
423 l_step := 8;
424 wf_engine.StartProcess(itemtype => l_itemtype,
425 itemkey => l_itemkey );
426 l_step := 9;
427 x_itemtype := l_itemtype;
428 x_itemkey := l_itemkey;
429 x_receiver := l_receiver;
430
431 EXCEPTION
432 WHEN OTHERS THEN
433 wf_core.context(g_package_name,'NOTIFY_ADDR_EVENTS',l_itemtype,l_itemkey);
434 raise_application_error(-20041, 'Failure at step ' || l_step, true);
435 END notify_addr_events;
436
437 -- notify buyer admins that an address is created
438 -- in the supplier's address book
439 PROCEDURE notify_addr_created
440 (p_vendor_id IN NUMBER,
441 p_address_request_id IN NUMBER,
442 x_itemtype OUT nocopy VARCHAR2,
443 x_itemkey OUT nocopy VARCHAR2,
444 x_receiver OUT nocopy VARCHAR2
445 )
446 IS
447 BEGIN
448 notify_addr_events
449 (p_vendor_id => p_vendor_id,
450 p_address_request_id => p_address_request_id,
451 p_wf_process => 'PADDR_CREATED',
452 x_itemtype => x_itemtype,
453 x_itemkey => x_itemkey,
454 x_receiver => x_receiver
455 );
456 END notify_addr_created;
457
458 -- notify buyer admins that an address is removed
459 -- in the supplier's address book
460 PROCEDURE notify_addr_removed
461 (p_vendor_id IN NUMBER,
462 p_address_request_id IN NUMBER,
463 x_itemtype OUT nocopy VARCHAR2,
464 x_itemkey OUT nocopy VARCHAR2,
465 x_receiver OUT nocopy VARCHAR2
466 )
467 IS
468 BEGIN
469 notify_addr_events
470 (p_vendor_id => p_vendor_id,
471 p_address_request_id => p_address_request_id,
472 p_wf_process => 'PADDR_REMOVED',
473 x_itemtype => x_itemtype,
474 x_itemkey => x_itemkey,
475 x_receiver => x_receiver
476 );
477 END notify_addr_removed;
478
479 -- notify buyer admins that an address is updated in
480 -- the supplier's address book
481 PROCEDURE notify_addr_updated
482 (p_vendor_id IN NUMBER,
483 p_address_request_id IN NUMBER,
484 x_itemtype OUT nocopy VARCHAR2,
485 x_itemkey OUT nocopy VARCHAR2,
486 x_receiver OUT nocopy VARCHAR2
487 )
488 IS
489 BEGIN
490 notify_addr_events
491 (p_vendor_id => p_vendor_id,
492 p_address_request_id => p_address_request_id,
493 p_wf_process => 'PADDR_UPDATED',
494 x_itemtype => x_itemtype,
495 x_itemkey => x_itemkey,
496 x_receiver => x_receiver
497 );
498 END notify_addr_updated;
499
500 FUNCTION get_bus_class_name_in_req (p_bus_class_request_id IN NUMBER) RETURN VARCHAR2
501 IS
502 CURSOR l_cur IS
503 SELECT flv.meaning
504 FROM fnd_lookup_values flv, pos_bus_class_reqs pbcr
505 WHERE flv.lookup_type = pbcr.lookup_type
506 AND flv.lookup_code = pbcr.lookup_code
507 AND flv.language = userenv('LANG')
508 AND flv.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
509 AND pbcr.bus_class_request_id = p_bus_class_request_id;
510
511 l_rec l_cur%ROWTYPE;
512 BEGIN
513 OPEN l_cur;
514 FETCH l_cur INTO l_rec;
515 IF l_cur%notfound THEN
516 CLOSE l_cur;
517 RAISE no_data_found;
518 END IF;
519 CLOSE l_cur;
520 RETURN l_rec.meaning;
521
522 END get_bus_class_name_in_req;
523
524 -- this is a utility private procedure that deals with
525 -- supplier business classification related change notifications
526 PROCEDURE notify_bus_class_changed
527 (p_process IN VARCHAR2,
528 p_vendor_id IN NUMBER,
529 p_bus_class_request_id IN NUMBER,
530 x_itemtype OUT nocopy VARCHAR2,
531 x_itemkey OUT nocopy VARCHAR2,
532 x_receiver OUT nocopy VARCHAR2
536 l_itemtype wf_items.item_type%TYPE;
533 )
534 IS
535 l_supplier_name ap_suppliers.vendor_name%TYPE;
537 l_itemkey wf_items.item_key%TYPE;
538 l_receiver wf_roles.name%TYPE;
539 l_cur g_refcur;
540 l_count NUMBER;
541 l_step NUMBER;
542 l_bus_class_name fnd_lookup_values.meaning%TYPE;
543 BEGIN
544 l_step := 0;
545 get_supplier_name(p_vendor_id, l_supplier_name);
546
547 l_step := 1;
548 -- setup receiver
549 create_adhoc_role(p_process||'_'||p_bus_class_request_id, l_receiver);
550
551 l_step := 2;
552 get_buyers('SUPP_BUS_CLASS_CHANGE_REQ',l_cur);
553
554 l_step := 3;
555 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
556 IF l_count < 1 THEN
557 -- there is no one to notify, so we just return
558 x_itemtype := NULL;
559 x_itemkey := NULL;
560 x_receiver := NULL;
561 RETURN;
562 END IF;
563
564 l_step := 4;
565 l_bus_class_name := get_bus_class_name_in_req(p_bus_class_request_id);
566
567 l_step := 5;
568 -- create workflow process
569 get_wf_item_type (l_itemtype);
570
571 l_step := 6;
572 get_wf_item_key (p_process,
573 To_char(p_vendor_id) || '_' || p_bus_class_request_id,
574 l_itemkey);
575
576 l_step := 7;
577 wf_engine.CreateProcess(itemtype => l_itemtype,
578 itemkey => l_itemkey,
579 process => p_process);
580 l_step := 8;
581
582 wf_engine.SetItemAttrText (itemtype => l_itemtype,
583 itemkey => l_itemkey,
584 aname => 'RECEIVER',
585 avalue => l_receiver);
586
587 wf_engine.SetItemAttrText (itemtype => l_itemtype,
588 itemkey => l_itemkey,
589 aname => 'SUPPLIER_NAME',
590 avalue => l_supplier_name);
591
592 wf_engine.SetItemAttrText (itemtype => l_itemtype,
593 itemkey => l_itemkey,
594 aname => 'BUS_CLASS_NAME',
595 avalue => l_bus_class_name);
596
597 wf_engine.SetItemAttrText (itemtype => l_itemtype,
598 itemkey => l_itemkey,
599 aname => 'VENDOR_ID',
600 avalue => p_vendor_id);
601
602 wf_engine.SetItemAttrText (itemtype => l_itemtype,
603 itemkey => l_itemkey,
604 aname => 'URL',
605 avalue => pos_url_pkg.get_dest_page_url ('POS_HT_SP_B_BUS_CLSS', 'BUYER'));
606 l_step := 9;
607
608 setup_actioner_private(l_itemtype, l_itemkey);
609
610 l_step := 10;
611
612 wf_engine.StartProcess(itemtype => l_itemtype,
613 itemkey => l_itemkey );
614
615 l_step := 11;
616 x_itemtype := l_itemtype;
617 x_itemkey := l_itemkey;
618 x_receiver := l_receiver;
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 wf_core.context(g_package_name,'NOTIFY_BUS_CLASS_CRT_REMOVED',l_itemtype,l_itemkey);
623 raise_application_error(-20043,'Failure at step ' || l_step , true);
624 END notify_bus_class_changed;
625
626 -- notify buyer admins that a business classification is added to the
627 -- supplier's list
628 PROCEDURE notify_bus_class_created
629 (p_vendor_id IN NUMBER,
630 p_bus_class_request_id IN NUMBER,
631 x_itemtype OUT nocopy VARCHAR2,
632 x_itemkey OUT nocopy VARCHAR2,
633 x_receiver OUT nocopy VARCHAR2
634 )
635 IS
636 l_class_id number;
637 BEGIN
638 select classification_id
639 into l_class_id
640 from pos_bus_class_reqs
641 where bus_class_request_id = p_bus_class_request_id;
642
643 -- The java API makes the call to this method even if the
644 -- classification is changed. This is where we can catch
645 -- this
646 if ( l_class_id is not null and l_class_id > 0 ) then
647 notify_bus_class_updated (p_vendor_id, p_bus_class_request_id,
648 x_itemtype, x_itemkey, x_receiver);
649 return;
650 end if;
651
652 notify_bus_class_changed
653 ('PBUS_CLASS_CREATED', p_vendor_id, p_bus_class_request_id, x_itemtype, x_itemkey, x_receiver);
654 END notify_bus_class_created;
655
656 -- notify buyer admins that a business classification is removed from the
657 -- supplier's list
658 PROCEDURE notify_bus_class_removed
659 (p_vendor_id IN NUMBER,
660 p_bus_class_request_id IN NUMBER,
661 x_itemtype OUT nocopy VARCHAR2,
662 x_itemkey OUT nocopy VARCHAR2,
663 x_receiver OUT nocopy VARCHAR2
664 )
665 IS
666 BEGIN
667
668 notify_bus_class_changed
669 ('PBUS_CLASS_REMOVED', p_vendor_id, p_bus_class_request_id, x_itemtype, x_itemkey, x_receiver);
670 END notify_bus_class_removed;
671
675 (p_vendor_id IN NUMBER,
672 -- notify buyer admins that a business classification is removed from the
673 -- supplier's list
674 PROCEDURE notify_bus_class_updated
676 p_bus_class_request_id IN NUMBER,
677 x_itemtype OUT nocopy VARCHAR2,
678 x_itemkey OUT nocopy VARCHAR2,
679 x_receiver OUT nocopy VARCHAR2
680 )
681 IS
682 BEGIN
683 notify_bus_class_changed
684 ('PBUS_CLASS_UPDATED', p_vendor_id, p_bus_class_request_id, x_itemtype, x_itemkey, x_receiver);
685 END notify_bus_class_updated;
686
687 FUNCTION get_contact_name_in_req (p_contact_request_id IN NUMBER) RETURN VARCHAR2
688 IS
689 CURSOR l_cur IS
690 SELECT Decode(pcr.contact_party_id, NULL, pcr.first_name || ' ' || pcr.last_name,
691 (SELECT hp.party_name
692 FROM hz_parties hp
693 WHERE hp.party_id = pcr.contact_party_id
694 )) contact_name
695 FROM pos_contact_requests pcr
696 WHERE pcr.contact_request_id = p_contact_request_id;
697
698 l_rec l_cur%ROWTYPE;
699
700 BEGIN
701 OPEN l_cur;
702 FETCH l_cur INTO l_rec;
703 IF l_cur%notfound THEN
704 CLOSE l_cur;
705 RAISE no_data_found;
706 END IF;
707 CLOSE l_cur;
708 RETURN l_rec.contact_name;
709
710 END get_contact_name_in_req;
711
712
713 -- this is a private method used for sending notification for
714 -- contact creation, update, removal events
715 PROCEDURE notify_contact_events
716 (p_wf_process IN VARCHAR2,
717 p_vendor_id IN NUMBER,
718 p_contact_request_id IN NUMBER,
719 x_itemtype OUT nocopy VARCHAR2,
720 x_itemkey OUT nocopy VARCHAR2,
721 x_receiver OUT nocopy VARCHAR2
722 )
723 IS
724 l_supplier_name ap_suppliers.vendor_name%TYPE;
725 l_contact_name hz_parties.party_name%TYPE;
726 l_itemtype wf_items.item_type%TYPE;
727 l_itemkey wf_items.item_key%TYPE;
728 l_receiver wf_roles.name%TYPE;
729 l_cur g_refcur;
730 l_count NUMBER;
731 l_process wf_process_activities.process_name%TYPE;
732 l_step NUMBER;
733 BEGIN
734 l_step := 0;
735 get_supplier_name(p_vendor_id, l_supplier_name);
736 l_step := 1;
737
738 l_contact_name := get_contact_name_in_req(p_contact_request_id);
739 l_step := 2;
740
741 -- setup receiver
742 l_process := p_wf_process;
743 create_adhoc_role(l_process||'_'||p_contact_request_id, l_receiver);
744
745 l_step := 3;
746 get_buyers('SUPP_CONT_CHANGE_REQ', l_cur);
747
748 l_step := 4;
749 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
750 IF l_count < 1 THEN
751 -- there is no one to notify, so we just return
752 x_itemtype := NULL;
753 x_itemkey := NULL;
754 x_receiver := NULL;
755 RETURN;
756 END IF;
757
758 l_step := 5;
759 -- create workflow process
760 get_wf_item_type (l_itemtype);
761
762 l_step := 6;
763 get_wf_item_key (l_process,
764 To_char(p_vendor_id) || '_' || p_contact_request_id,
765 l_itemkey);
766
767 l_step := 7;
768 wf_engine.CreateProcess(itemtype => l_itemtype,
769 itemkey => l_itemkey,
770 process => l_process);
771
772 l_step := 8;
773 wf_engine.SetItemAttrText (itemtype => l_itemtype,
774 itemkey => l_itemkey,
775 aname => 'RECEIVER',
776 avalue => l_receiver);
777
778 wf_engine.SetItemAttrText (itemtype => l_itemtype,
779 itemkey => l_itemkey,
780 aname => 'SUPPLIER_NAME',
781 avalue => l_supplier_name);
782
783 wf_engine.SetItemAttrText (itemtype => l_itemtype,
784 itemkey => l_itemkey,
785 aname => 'CONTACT_NAME',
786 avalue => l_contact_name);
787
788 wf_engine.SetItemAttrText (itemtype => l_itemtype,
789 itemkey => l_itemkey,
790 aname => 'VENDOR_ID',
791 avalue => p_vendor_id);
792
793 wf_engine.SetItemAttrText (itemtype => l_itemtype,
794 itemkey => l_itemkey,
795 aname => 'URL',
796 avalue => pos_url_pkg.get_dest_page_url ('POS_HT_SP_B_CONT_DIR', 'BUYER'));
797
798 l_step := 9;
799 setup_actioner_private(l_itemtype, l_itemkey);
800
801 l_step := 10;
802 wf_engine.StartProcess(itemtype => l_itemtype,
803 itemkey => l_itemkey );
804
805 l_step := 11;
806 x_itemtype := l_itemtype;
807 x_itemkey := l_itemkey;
808 x_receiver := l_receiver;
809
810 EXCEPTION
811 WHEN OTHERS THEN
812 wf_core.context(g_package_name,'NOTIFY_CONTACT_CREATED',l_itemtype,l_itemkey);
816 -- notify buyer admins that an contact is removed
813 raise_application_error(-20044,'Failure at step ' || l_step , true);
814 END notify_contact_events;
815
817 -- in the supplier's contact directory
818 PROCEDURE notify_contact_created
819 (p_vendor_id IN NUMBER,
820 p_contact_request_id IN NUMBER,
821 x_itemtype OUT nocopy VARCHAR2,
822 x_itemkey OUT nocopy VARCHAR2,
823 x_receiver OUT nocopy VARCHAR2
824 )
825 IS
826 BEGIN
827 notify_contact_events
828 ('PCONTACT_CREATED', p_vendor_id, p_contact_request_id, x_itemtype, x_itemkey, x_receiver);
829 END notify_contact_created;
830
831 -- notify buyer admins that an contact is removed
832 -- in the supplier's contact directory
833 PROCEDURE notify_contact_removed
834 (p_vendor_id IN NUMBER,
835 p_contact_request_id IN NUMBER,
836 x_itemtype OUT nocopy VARCHAR2,
837 x_itemkey OUT nocopy VARCHAR2,
838 x_receiver OUT nocopy VARCHAR2
839 )
840 IS
841 BEGIN
842 notify_contact_events
843 ('PCONTACT_REMOVED', p_vendor_id, p_contact_request_id, x_itemtype, x_itemkey, x_receiver);
844 END notify_contact_removed;
845
846 -- notify buyer admins that an contact is updated in
847 -- the supplier's contact directory
848 PROCEDURE notify_contact_updated
849 (p_vendor_id IN NUMBER,
850 p_contact_request_id IN NUMBER,
851 x_itemtype OUT nocopy VARCHAR2,
852 x_itemkey OUT nocopy VARCHAR2,
853 x_receiver OUT nocopy VARCHAR2
854 )
855 IS
856 BEGIN
857 notify_contact_events
858 ('PCONTACT_UPDATED', p_vendor_id, p_contact_request_id, x_itemtype, x_itemkey, x_receiver);
859 END notify_contact_updated;
860
861 -- in r12, the contact change request approval ui and the contact address change request approval ui
862 -- is the same. So for link events, the notification should be also the same (which means using the contact request).
863
864 /*PROCEDURE notify_contact_link_events
865 (p_vendor_id IN NUMBER,
866 p_contact_request_id IN NUMBER,
867 p_wf_process IN VARCHAR2,
868 x_itemtype OUT nocopy VARCHAR2,
869 x_itemkey OUT nocopy VARCHAR2,
870 x_receiver OUT nocopy VARCHAR2
871 )
872 IS
873 l_supplier_name ap_suppliers.vendor_name%TYPE;
874 l_contact_name hz_parties.party_name%TYPE;
875 l_itemtype wf_items.item_type%TYPE;
876 l_itemkey wf_items.item_key%TYPE;
877 l_receiver wf_roles.name%TYPE;
878 l_cur g_refcur;
879 l_count NUMBER;
880 l_process wf_process_activities.process_name%TYPE;
881 l_step NUMBER;
882 BEGIN
883 l_step := 0;
884 get_supplier_name(p_vendor_id, l_supplier_name);
885
886 l_step := 1;
887 get_contact_name(p_contact_party_id, l_contact_name);
888
889 l_step := 2;
890 -- setup receiver
891 l_process := p_wf_process;
892 create_adhoc_role(l_process||'_'||p_party_site_id||'_'||p_contact_party_id, l_receiver);
893 get_buyers('SUPP_CONT_CHANGE_REQ', l_cur);
894 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
895 IF l_count < 1 THEN
896 -- there is no one to notify, so we just return
897 x_itemtype := NULL;
898 x_itemkey := NULL;
899 RETURN;
900 END IF;
901
902 l_step := 3;
903 get_wf_item_type (l_itemtype);
904
905 l_step := 4;
906 get_wf_item_key (l_process,
907 To_char(p_vendor_id) || '_' || p_contact_party_id,
908 l_itemkey);
909
910 l_step := 5;
911 wf_engine.CreateProcess(itemtype => l_itemtype,
912 itemkey => l_itemkey,
913 process => l_process);
914
915 l_step := 6;
916 wf_engine.SetItemAttrText (itemtype => l_itemtype,
917 itemkey => l_itemkey,
918 aname => 'RECEIVER',
919 avalue => l_receiver);
920
921 wf_engine.SetItemAttrText (itemtype => l_itemtype,
922 itemkey => l_itemkey,
923 aname => 'SUPPLIER_NAME',
924 avalue => l_supplier_name);
925
926 wf_engine.SetItemAttrText (itemtype => l_itemtype,
927 itemkey => l_itemkey,
928 aname => 'CONTACT_NAME',
929 avalue => l_contact_name);
930
931 wf_engine.SetItemAttrText (itemtype => l_itemtype,
932 itemkey => l_itemkey,
933 aname => 'VENDOR_ID',
934 avalue => p_vendor_id);
935
936 wf_engine.SetItemAttrText (itemtype => l_itemtype,
937 itemkey => l_itemkey,
938 aname => 'URL',
939 avalue => pos_url_pkg.get_dest_page_url ('POS_HT_SP_B_CONT_DIR', 'BUYER'));
940 l_step := 7;
941 setup_actioner_private(l_itemtype, l_itemkey);
942 l_step := 8;
943
944 wf_engine.StartProcess(itemtype => l_itemtype,
948 x_itemtype := l_itemtype;
945 itemkey => l_itemkey );
946
947 l_step := 9;
949 x_itemkey := l_itemkey;
950 x_receiver := l_receiver;
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 wf_core.context(g_package_name,'NOTIFY_CONTACT_LINK_CREATED',l_itemtype,l_itemkey);
955 raise_application_error(-20046, 'Failure at step ' || l_step , true);
956 END notify_contact_link_events;
957
958 PROCEDURE notify_contact_link_created
959 (p_vendor_id IN NUMBER,
960 p_party_site_id IN NUMBER,
961 p_contact_party_id IN NUMBER,
962 x_itemtype OUT nocopy VARCHAR2,
963 x_itemkey OUT nocopy VARCHAR2,
964 x_receiver OUT nocopy VARCHAR2
965 )
966 IS
967 BEGIN
968 notify_contact_link_events
969 (p_vendor_id => p_vendor_id,
970 p_party_site_id => p_party_site_id,
971 p_contact_party_id => p_contact_party_id,
972 p_wf_process => 'PCONTACT_LINK_CREATED',
973 x_itemtype => x_itemtype,
974 x_itemkey => x_itemkey,
975 x_receiver => x_receiver
976 );
977 END notify_contact_link_created;
978
979 PROCEDURE notify_contact_link_removed
980 (p_vendor_id IN NUMBER,
981 p_party_site_id IN NUMBER,
982 p_contact_party_id IN NUMBER,
983 x_itemtype OUT nocopy VARCHAR2,
984 x_itemkey OUT nocopy VARCHAR2,
985 x_receiver OUT nocopy VARCHAR2
986 )
987 IS
988 BEGIN
989 notify_contact_link_events
990 (p_vendor_id => p_vendor_id,
991 p_party_site_id => p_party_site_id,
992 p_contact_party_id => p_contact_party_id,
993 p_wf_process => 'PCONTACT_LINK_REMOVED',
994 x_itemtype => x_itemtype,
995 x_itemkey => x_itemkey,
996 x_receiver => x_receiver
997 );
998 END notify_contact_link_removed;
999 */
1000
1001 -- this is a utility private procedure that deals with
1002 -- supplier product and service related change notifications
1003 PROCEDURE notify_product_crt_removed
1004 (p_process IN VARCHAR2,
1005 p_vendor_id IN NUMBER,
1006 x_itemtype OUT nocopy VARCHAR2,
1007 x_itemkey OUT nocopy VARCHAR2,
1008 x_receiver OUT nocopy VARCHAR2
1009 )
1010 IS
1011 l_supplier_name ap_suppliers.vendor_name%TYPE;
1012 l_itemtype wf_items.item_type%TYPE;
1013 l_itemkey wf_items.item_key%TYPE;
1014 l_receiver wf_roles.name%TYPE;
1015 l_cur g_refcur;
1016 l_count NUMBER;
1017 l_step NUMBER;
1018 BEGIN
1019 l_step := 0;
1020 get_supplier_name(p_vendor_id, l_supplier_name);
1021
1022 l_step := 1;
1023 -- setup receiver
1024 create_adhoc_role(p_process||'_'||p_vendor_id, l_receiver);
1025
1026 l_step := 2;
1027 get_buyers('SUPP_PS_CHANGE_REQ',l_cur);
1028
1029 l_step := 3;
1030 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
1031
1032 l_step := 4;
1033 IF l_count < 1 THEN
1034 -- there is no one to notify, so we just return
1035 x_itemtype := NULL;
1036 x_itemkey := NULL;
1037 x_receiver := NULL;
1038 RETURN;
1039 END IF;
1040
1041 l_step := 5;
1042 get_wf_item_type (l_itemtype);
1043
1044 l_step := 6;
1045 get_wf_item_key (p_process,
1046 To_char(p_vendor_id),
1047 l_itemkey);
1048
1049 l_step := 7;
1050 wf_engine.CreateProcess(itemtype => l_itemtype,
1051 itemkey => l_itemkey,
1052 process => p_process);
1053
1054 l_step := 8;
1055 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1056 itemkey => l_itemkey,
1057 aname => 'RECEIVER',
1058 avalue => l_receiver);
1059
1060 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1061 itemkey => l_itemkey,
1062 aname => 'SUPPLIER_NAME',
1063 avalue => l_supplier_name);
1064
1065 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1066 itemkey => l_itemkey,
1067 aname => 'VENDOR_ID',
1068 avalue => p_vendor_id);
1069
1070 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1071 itemkey => l_itemkey,
1072 aname => 'URL',
1073 avalue => pos_url_pkg.get_dest_page_url ('POS_HT_SP_B_PS', 'BUYER'));
1074
1075 l_step := 9;
1076 setup_actioner_private(l_itemtype, l_itemkey);
1077
1078 l_step := 10;
1079 wf_engine.StartProcess(itemtype => l_itemtype,
1080 itemkey => l_itemkey );
1081
1082 l_step := 11;
1083 x_itemtype := l_itemtype;
1084 x_itemkey := l_itemkey;
1085 x_receiver := l_receiver;
1086
1087 EXCEPTION
1088 WHEN OTHERS THEN
1092
1089 wf_core.context(g_package_name,'NOTIFY_PRODUCT_CRT_REMOVED',l_itemtype,l_itemkey);
1090 raise_application_error(-20048, 'Failure at step ' || l_step, true);
1091 END notify_product_crt_removed;
1093 -- notify buyer admins that a business classification is added to the
1094 -- supplier's list
1095 PROCEDURE notify_product_created
1096 (p_vendor_id IN NUMBER,
1097 x_itemtype OUT nocopy VARCHAR2,
1098 x_itemkey OUT nocopy VARCHAR2,
1099 x_receiver OUT nocopy VARCHAR2
1100 )
1101 IS
1102 BEGIN
1103 notify_product_crt_removed
1104 ('PPRODUCT_CREATED', p_vendor_id, x_itemtype, x_itemkey, x_receiver);
1105 END notify_product_created;
1106
1107 -- notify buyer admins that a product and service is removed from the
1108 -- supplier's list
1109 PROCEDURE notify_product_removed
1110 (p_vendor_id IN NUMBER,
1111 x_itemtype OUT nocopy VARCHAR2,
1112 x_itemkey OUT nocopy VARCHAR2,
1113 x_receiver OUT nocopy VARCHAR2
1114 )
1115 IS
1116 BEGIN
1117 notify_product_crt_removed
1118 ('PPRODUCT_REMOVED', p_vendor_id, x_itemtype, x_itemkey, x_receiver);
1119 END notify_product_removed;
1120
1121 FUNCTION get_1st_supplier_user(p_vendor_id IN NUMBER) RETURN VARCHAR2
1122 IS
1123 l_supplier_party_id NUMBER;
1124 CURSOR l_user_name_cur (p_supplier_party_id IN NUMBER) IS
1125 SELECT fu.user_name
1126 FROM hz_relationships hzr, hz_parties hp, fnd_user fu
1127 WHERE
1128 fu.person_party_id = hp.party_id
1129 AND fu.email_address IS NOT NULL
1130 AND fu.end_date IS NULL
1131 AND hzr.object_id = p_supplier_party_id
1132 AND hzr.subject_type = 'PERSON'
1133 AND hzr.object_type = 'ORGANIZATION'
1134 AND hzr.relationship_type = 'POS_EMPLOYMENT'
1135 AND hzr.relationship_code = 'EMPLOYEE_OF'
1136 AND hzr.status = 'A'
1137 AND (hzr.start_date IS NULL OR
1138 hzr.start_date <= Sysdate)
1139 AND (hzr.end_date IS NULL OR
1140 hzr.end_date >= Sysdate)
1141 AND hzr.subject_id = hp.party_id
1142 ORDER BY hp.creation_date asc;
1143
1144 BEGIN
1145 l_supplier_party_id := pos_vendor_util_pkg.get_party_id_for_vendor(p_vendor_id);
1146 IF l_supplier_party_id IS NULL THEN
1147 RETURN NULL;
1148 END IF;
1149
1150 FOR l_rec IN l_user_name_cur(l_supplier_party_id) LOOP
1151 RETURN l_rec.user_name; -- only need the first one
1152 END LOOP;
1153
1154 RETURN NULL;
1155 END get_1st_supplier_user;
1156
1157 PROCEDURE setup_dup_reg_receiver
1158 (p_process IN VARCHAR2,
1159 p_vendor_id IN NUMBER,
1160 x_receiver OUT nocopy VARCHAR2
1161 )
1162 IS
1163 l_receiver wf_roles.name%TYPE;
1164 l_adhoc_user wf_roles.name%TYPE;
1165
1166 CURSOR l_user_name_cur IS
1167 SELECT fu.user_name
1168 FROM hz_relationships hzr, hz_parties hp, fnd_user fu, ap_suppliers ap, hz_party_usg_assignments hpua
1169 WHERE fu.person_party_id = hp.party_id
1170 AND fu.email_address IS NOT NULL
1171 AND fu.end_date IS NULL
1172 AND ap.vendor_id = p_vendor_id
1173 AND hzr.object_id = ap.party_id
1174 AND hzr.subject_type = 'PERSON'
1175 AND hzr.object_type = 'ORGANIZATION'
1176 AND hzr.relationship_type = 'CONTACT'
1177 AND hzr.relationship_code = 'CONTACT_OF'
1178 AND hzr.status = 'A'
1179 AND (hzr.start_date IS NULL OR
1180 hzr.start_date <= Sysdate)
1181 AND (hzr.end_date IS NULL OR
1182 hzr.end_date >= Sysdate)
1183 AND hzr.subject_id = hp.party_id
1184 and hpua.party_id = hp.party_id
1185 and hpua.status_flag = 'A'
1186 and hpua.party_usage_code = 'SUPPLIER_CONTACT'
1187 and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate);
1188
1189 l_user_name_rec l_user_name_cur%ROWTYPE;
1190
1191 CURSOR l_cur IS
1192 SELECT pvsa.email_address, ft.nls_territory, pvsa.language
1193 FROM ap_supplier_sites_all pvsa, fnd_territories ft
1194 WHERE pvsa.email_address IS NOT NULL
1195 AND pvsa.vendor_id = p_vendor_id
1196 AND (pvsa.inactive_date IS NULL OR pvsa.inactive_date IS NOT NULL AND pvsa.inactive_date > Sysdate)
1197 AND pvsa.country = ft.territory_code (+);
1198
1199 l_rec l_cur%ROWTYPE;
1200 l_count NUMBER;
1201 l_user_name fnd_user.user_name%TYPE;
1202
1203 CURSOR l_contact_cur IS
1204 select hp.person_first_name, hp.person_last_name, hzr_hp.email_address
1205 from hz_parties hp, hz_relationships hzr, hz_parties hzr_hp, hz_party_usg_assignments hpua, ap_suppliers apsupp
1206 where hp.party_id = hzr.subject_id
1207 and hzr.object_id = apsupp.party_id
1208 and apsupp.vendor_id = p_vendor_id
1209 and hzr.relationship_type = 'CONTACT'
1210 and hzr.relationship_code = 'CONTACT_OF'
1211 and hzr.subject_type ='PERSON'
1212 and hzr.object_type = 'ORGANIZATION'
1213 and (hzr.end_date is null or hzr.end_date > sysdate)
1214 and hzr.status = 'A'
1215 and hzr_hp.party_id = hzr.party_id
1216 and hpua.party_id = hp.party_id
1220 and hp.party_id not in ( select pcr.contact_party_id
1217 and hpua.status_flag = 'A'
1218 and hpua.party_usage_code = 'SUPPLIER_CONTACT'
1219 and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate)
1221 from pos_contact_requests pcr, pos_supplier_mappings psm
1222 where pcr.request_status='PENDING'
1223 and psm.mapping_id = pcr.mapping_id
1224 and psm.PARTY_ID = apsupp.party_id
1225 and pcr.contact_party_id is not null )
1226 and hzr_hp.email_address is not null;
1227
1228 BEGIN
1229 create_adhoc_role(p_process||'_'||p_vendor_id, l_receiver);
1230 l_count := 0;
1231
1232 FOR l_user_name_rec in l_user_name_cur loop
1233 l_count := l_count + 1;
1234 AddUserToAdHocRole
1235 (l_receiver, l_user_name_rec.user_name);
1236 END LOOP;
1237
1238 IF l_count = 0 then
1239 FOR l_rec IN l_cur LOOP
1240 l_count := l_count + 1;
1241 l_adhoc_user := l_receiver || '_' || l_count;
1242 wf_directory.CreateAdHocUser
1243 (name => l_adhoc_user,
1244 display_name => l_rec.email_address,
1245 language => l_rec.language,
1246 territory => l_rec.nls_territory ,
1247 email_address => l_rec.email_address
1248 );
1249 AddUserToAdHocRole
1250 (l_receiver, l_adhoc_user);
1251 END LOOP;
1252 END IF;
1253
1254 IF l_count = 0 THEN
1255 FOR l_contact_rec IN l_contact_cur LOOP
1256 l_count := l_count + 1;
1257 l_adhoc_user := l_receiver || '_' || l_count;
1258 wf_directory.CreateAdHocUser
1259 (name => l_adhoc_user,
1260 display_name => l_contact_rec.email_address,
1261 email_address => l_contact_rec.email_address
1262 );
1263 AddUserToAdHocRole
1264 (l_receiver, l_adhoc_user);
1265 END LOOP;
1266 END IF;
1267 x_receiver := l_receiver;
1268 END setup_dup_reg_receiver;
1269
1270 -- return Y if the role has at least one active user; otherwise N
1271 FUNCTION wfrole_has_active_user
1272 (p_role IN VARCHAR2) RETURN VARCHAR2 IS
1273 l_users wf_directory.usertable;
1274 l_username wf_users.name%TYPE;
1275 BEGIN
1276 wf_directory.GetRoleUsers(p_role, l_users);
1277 FOR l_index IN 1..l_users.COUNT LOOP
1278 IF l_users(l_index) IS NOT NULL
1279 AND wf_directory.UserActive(l_users(l_index)) THEN
1280 RETURN 'Y';
1281 END IF;
1282 END LOOP;
1283 RETURN 'N';
1284 END wfrole_has_active_user;
1285
1286 PROCEDURE notify_dup_supplier_reg
1287 (p_vendor_id IN NUMBER,
1288 p_first_name IN VARCHAR2,
1289 p_last_name IN VARCHAR2,
1290 p_sup_reg_email IN VARCHAR2,
1291 x_itemtype OUT nocopy VARCHAR2,
1292 x_itemkey OUT nocopy VARCHAR2,
1293 x_receiver OUT nocopy VARCHAR2
1294 )
1295 IS
1296 PRAGMA autonomous_transaction;
1297 l_supplier_name ap_suppliers.vendor_name%TYPE;
1298 l_enterprise_name hz_parties.party_name%TYPE;
1299 l_itemtype wf_items.item_type%TYPE;
1300 l_itemkey wf_items.item_key%TYPE;
1301 l_receiver wf_roles.name%TYPE;
1302 l_cur g_refcur;
1303 l_count NUMBER;
1304 l_process wf_process_activities.process_name%TYPE;
1305 l_step NUMBER;
1306 BEGIN
1307 l_step := 0;
1308 get_supplier_name(p_vendor_id, l_supplier_name);
1309
1310 l_step := 1;
1311 get_enterprise_name(l_enterprise_name);
1312
1313 l_step := 2;
1314 -- setup receiver
1315 l_process := 'PDUP_SUPPLIER_REG';
1316 setup_dup_reg_receiver(l_process, p_vendor_id, l_receiver);
1317
1318 IF wfrole_has_active_user(l_receiver) = 'N' THEN
1319 -- there is no one to notify, so we just return
1320 x_itemtype := NULL;
1321 x_itemkey := NULL;
1322 x_receiver := NULL;
1323 -- bug 2809368, need to rollback before return as this procedure uses
1324 -- autonomous_transaction
1325 ROLLBACK;
1326 RETURN;
1327 END IF;
1328
1329 l_step := 3;
1330 get_wf_item_type (l_itemtype);
1331
1332 l_step := 4;
1333 get_wf_item_key (l_process,
1334 To_char(p_vendor_id) || '_' || p_sup_reg_email,
1335 l_itemkey);
1336
1337 l_step := 5;
1338 wf_engine.CreateProcess(itemtype => l_itemtype,
1339 itemkey => l_itemkey,
1340 process => l_process);
1341
1342 l_step := 6;
1343 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1344 itemkey => l_itemkey,
1345 aname => 'RECEIVER',
1346 avalue => l_receiver);
1347
1348 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1349 itemkey => l_itemkey,
1350 aname => 'SUPPLIER_NAME',
1351 avalue => l_supplier_name);
1352
1353 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1354 itemkey => l_itemkey,
1358 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1355 aname => 'ENTERPRISE_NAME',
1356 avalue => l_enterprise_name);
1357
1359 itemkey => l_itemkey,
1360 aname => 'FIRST_NAME',
1361 avalue => p_first_name);
1362
1363 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1364 itemkey => l_itemkey,
1365 aname => 'LAST_NAME',
1366 avalue => p_last_name);
1367
1368 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1369 itemkey => l_itemkey,
1370 aname => 'SUP_REG_EMAIL',
1371 avalue => p_sup_reg_email);
1372
1373 l_step := 7;
1374 wf_engine.StartProcess(itemtype => l_itemtype,
1375 itemkey => l_itemkey );
1376
1377 l_step := 8;
1378 x_itemtype := l_itemtype;
1379 x_itemkey := l_itemkey;
1380 x_receiver := l_receiver;
1381
1382 COMMIT;
1383
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386 -- bug 2809368, need to rollback before return as this procedure uses
1387 -- autonomous_transaction
1388 ROLLBACK;
1389 wf_core.context(g_package_name,'NOTIFY_DUP_SUPPLIER_REG',l_itemtype,l_itemkey);
1390 raise_application_error(-20049, 'Failure at step ' || l_step, true);
1391 END notify_dup_supplier_reg;
1392
1393 PROCEDURE get_reg_ou_id
1394 (p_supplier_reg_id IN NUMBER,
1395 x_ou_id OUT nocopy NUMBER)
1396 IS
1397 CURSOR l_cur IS
1398 SELECT ou_id
1399 FROM pos_supplier_registrations
1400 WHERE supplier_reg_id = p_supplier_reg_id;
1401 BEGIN
1402 OPEN l_cur;
1403 FETCH l_cur INTO x_ou_id;
1404 IF l_cur%notfound THEN
1405 CLOSE l_cur;
1406 RAISE no_data_found;
1407 END IF;
1408 CLOSE l_cur;
1409 END get_reg_ou_id;
1410
1411 PROCEDURE get_reg_supplier_name
1412 (p_supplier_reg_id IN NUMBER,
1413 x_name OUT nocopy VARCHAR2)
1414 IS
1415 CURSOR l_cur IS
1416 SELECT supplier_name
1417 FROM pos_supplier_registrations
1418 WHERE supplier_reg_id = p_supplier_reg_id;
1419 BEGIN
1420 OPEN l_cur;
1421 FETCH l_cur INTO x_name;
1422 IF l_cur%notfound THEN
1423 CLOSE l_cur;
1424 RAISE no_data_found;
1425 END IF;
1426 CLOSE l_cur;
1427 END get_reg_supplier_name;
1428
1429 -- notify buyer admins that a supplier has registered
1430 PROCEDURE notify_supplier_registered
1431 (p_supplier_reg_id IN NUMBER,
1432 x_itemtype OUT nocopy VARCHAR2,
1433 x_itemkey OUT nocopy VARCHAR2,
1434 x_receiver OUT nocopy VARCHAR2
1435 )
1436 IS
1437 l_supplier_name ap_suppliers.vendor_name%TYPE;
1438 l_itemtype wf_items.item_type%TYPE;
1439 l_itemkey wf_items.item_key%TYPE;
1440 l_receiver wf_roles.name%TYPE;
1441 l_cur g_refcur;
1442 l_count NUMBER;
1443 l_process wf_process_activities.process_name%TYPE;
1444 l_ou_id NUMBER;
1445 l_step NUMBER;
1446 BEGIN
1447
1448 l_step := 0;
1449 get_reg_supplier_name(p_supplier_reg_id, l_supplier_name);
1450
1451 l_step := 1;
1452 get_reg_ou_id(p_supplier_reg_id, l_ou_id);
1453
1454 l_step := 2;
1455 l_process := 'PSUPPLIER_REGISTERED';
1456 create_adhoc_role(l_process||'_'||p_supplier_reg_id, l_receiver);
1457
1458 l_step := 3;
1459 get_buyers('SUPPLIER_REGISTERED',l_cur);
1460
1461 l_step := 4;
1462 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
1463
1464 l_step := 5;
1465 IF l_count < 1 THEN
1466 -- there is no one to notify, so we just return
1467 x_itemtype := NULL;
1468 x_itemkey := NULL;
1469 x_receiver := NULL;
1470 RETURN;
1471 END IF;
1472
1473 l_step := 6;
1474 get_wf_item_type (l_itemtype);
1475
1476 l_step := 7;
1477 get_wf_item_key (l_process,
1478 To_char(p_supplier_reg_id),
1479 l_itemkey);
1480
1481 l_step := 8;
1482 wf_engine.CreateProcess(itemtype => l_itemtype,
1483 itemkey => l_itemkey,
1484 process => l_process);
1485
1486 l_step := 9;
1487 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1488 itemkey => l_itemkey,
1489 aname => 'RECEIVER',
1490 avalue => l_receiver);
1491
1492 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1493 itemkey => l_itemkey,
1494 aname => 'SUPPLIER_NAME',
1495 avalue => l_supplier_name);
1496
1497 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1498 itemkey => l_itemkey,
1499 aname => 'URL',
1500 avalue => pos_url_pkg.get_internal_login_url);
1501
1505
1502 l_step := 10;
1503 wf_engine.StartProcess(itemtype => l_itemtype,
1504 itemkey => l_itemkey );
1506 l_step := 11;
1507 x_itemtype := l_itemtype;
1508 x_itemkey := l_itemkey;
1509 x_receiver := l_receiver;
1510
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 wf_core.context(g_package_name,'NOTIFY_SUPPLIER_REGISTERED',l_itemtype,l_itemkey);
1514 raise_application_error(-20052, 'Failure at step ' || l_step, true);
1515 END notify_supplier_registered;
1516
1517 FUNCTION get_admin_email RETURN VARCHAR2
1518 IS
1519 l_user_id NUMBER;
1520
1521 CURSOR l_email_cur IS
1522 SELECT fu.email_address
1523 FROM fnd_user fu
1524 WHERE fu.user_id = l_user_id;
1525
1526 CURSOR l_name_cur IS
1527 SELECT ppf.first_name, ppf.last_name
1528 FROM fnd_user fu, per_people_f ppf
1529 WHERE fu.user_id = l_user_id AND
1530 ppf.person_id = fu.employee_id;
1531
1532 l_email_rec l_email_cur%ROWTYPE;
1533 l_name_rec l_name_cur%ROWTYPE;
1534 l_found_email BOOLEAN;
1535 l_found_name BOOLEAN;
1536 BEGIN
1537 l_user_id := fnd_global.user_id;
1538 l_found_email := FALSE;
1539 l_found_name := FALSE;
1540 OPEN l_email_cur;
1541 FETCH l_email_cur INTO l_email_rec;
1542 IF l_email_cur%found THEN
1543 l_found_email := TRUE;
1544 END IF;
1545 CLOSE l_email_cur;
1546 OPEN l_name_cur;
1547 FETCH l_name_cur INTO l_name_rec;
1548 IF l_name_cur%found THEN
1549 l_found_name := TRUE;
1550 END IF;
1551 CLOSE l_name_cur;
1552
1553 IF l_found_name AND l_found_email THEN
1554 RETURN l_name_rec.first_name || ' ' || l_name_rec.last_name || '(' ||
1555 l_email_rec.email_address || ')';
1556 ELSIF l_found_name THEN
1557 RETURN l_name_rec.first_name || ' ' || l_name_rec.last_name;
1558 ELSE
1559 RETURN l_email_rec.email_address;
1560 END IF;
1561 END get_admin_email;
1562
1563 -- notify the supplier that his/her supplier registration is
1564 -- approved
1565 PROCEDURE notify_supplier_approved
1566 (p_supplier_reg_id IN NUMBER,
1567 p_username IN VARCHAR2,
1568 p_password IN VARCHAR2,
1569 x_itemtype OUT nocopy VARCHAR2,
1570 x_itemkey OUT nocopy VARCHAR2
1571 )
1572 IS
1573 l_itemtype wf_items.item_type%TYPE;
1574 l_itemkey wf_items.item_key%TYPE;
1575 l_process wf_process_activities.process_name%TYPE;
1576 l_enterprise_name hz_parties.party_name%TYPE;
1577 l_step NUMBER;
1578 BEGIN
1579 l_step := 0;
1580 get_enterprise_name(l_enterprise_name);
1581
1582 l_step := 1;
1583 l_process := 'PSUPPLIER_APPROVED';
1584 get_wf_item_type (l_itemtype);
1585
1586 l_step := 2;
1587 get_wf_item_key (l_process,
1588 To_char(p_supplier_reg_id),
1589 l_itemkey);
1590
1591 l_step := 3;
1592 wf_engine.CreateProcess(itemtype => l_itemtype,
1593 itemkey => l_itemkey,
1594 process => l_process);
1595
1596 l_step := 4;
1597 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1598 itemkey => l_itemkey,
1599 aname => 'RECEIVER',
1600 avalue => p_username);
1601
1602 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1603 itemkey => l_itemkey,
1604 aname => 'ENTERPRISE_NAME',
1605 avalue => l_enterprise_name);
1606
1607 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1608 itemkey => l_itemkey,
1609 aname => 'USERNAME',
1610 avalue => p_username);
1611
1612 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1613 itemkey => l_itemkey,
1614 aname => 'PASSWORD',
1615 avalue => p_password);
1616
1617 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1618 itemkey => l_itemkey,
1619 aname => 'URL',
1620 avalue => pos_url_pkg.get_external_login_url);
1621
1622 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1623 itemkey => l_itemkey,
1624 aname => 'ADMIN_EMAIL',
1625 avalue => get_admin_email);
1626
1627 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1628 itemkey => l_itemkey,
1629 aname => 'BUYER_NOTE',
1630 avalue => 'PLSQL:POS_SPM_WF_PKG1.BUYER_NOTE/'||To_char(p_supplier_reg_id));
1631 l_step := 5;
1632 wf_engine.StartProcess(itemtype => l_itemtype,
1633 itemkey => l_itemkey );
1634 l_step := 6;
1635 x_itemtype := l_itemtype;
1636 x_itemkey := l_itemkey;
1637
1638 EXCEPTION
1639 WHEN OTHERS THEN
1643
1640 wf_core.context(g_package_name,'NOTIFY_SUPPLIER_APPROVED',l_itemtype,l_itemkey);
1641 raise_application_error(-20053, 'Failure at step ' || l_step, true);
1642 END notify_supplier_approved;
1644 -- Notify supplie user of login info.
1645 -- The supplier user here is not the primary contact who submitted the
1646 -- registration. The notification for the primary contact should be
1647 -- sent using notify_supplier_approved method above.
1648 PROCEDURE notify_supplier_user_approved
1649 (p_supplier_reg_id IN NUMBER,
1650 p_username IN VARCHAR2,
1651 p_password IN VARCHAR2,
1652 x_itemtype OUT nocopy VARCHAR2,
1653 x_itemkey OUT nocopy VARCHAR2
1654 )
1655 IS
1656 l_itemtype wf_items.item_type%TYPE;
1657 l_itemkey wf_items.item_key%TYPE;
1658 l_process wf_process_activities.process_name%TYPE;
1659 l_enterprise_name hz_parties.party_name%TYPE;
1660 l_step VARCHAR2(100);
1661 BEGIN
1662 l_step := 1;
1663
1664 get_enterprise_name(l_enterprise_name);
1665
1666 l_process := 'SUPPLIER_REG_USER_CREATED';
1667
1668 l_step := 2;
1669 get_wf_item_type (l_itemtype);
1670
1671 l_step := 3;
1672 get_wf_item_key (l_process,
1673 To_char(p_supplier_reg_id) || '_' || p_username,
1674 l_itemkey);
1675
1676 l_step := 4;
1677 wf_engine.CreateProcess(itemtype => l_itemtype,
1678 itemkey => l_itemkey,
1679 process => l_process);
1680
1681 l_step := 5;
1682 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1683 itemkey => l_itemkey,
1684 aname => 'RECEIVER',
1685 avalue => p_username);
1686
1687 l_step := 6;
1688 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1689 itemkey => l_itemkey,
1690 aname => 'ENTERPRISE_NAME',
1691 avalue => l_enterprise_name);
1692
1693 l_step := 7;
1694 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1695 itemkey => l_itemkey,
1696 aname => 'USERNAME',
1697 avalue => p_username);
1698
1699 l_step := 8;
1700 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1701 itemkey => l_itemkey,
1702 aname => 'PASSWORD',
1703 avalue => p_password);
1704
1705 l_step := 9;
1706 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1707 itemkey => l_itemkey,
1708 aname => 'URL',
1709 avalue => pos_url_pkg.get_external_login_url);
1710
1711 l_step := 10;
1712 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1713 itemkey => l_itemkey,
1714 aname => 'ADMIN_EMAIL',
1715 avalue => get_admin_email);
1716
1717 l_step := 11;
1718
1719 wf_engine.StartProcess(itemtype => l_itemtype,
1720 itemkey => l_itemkey );
1721 x_itemtype := l_itemtype;
1722 x_itemkey := l_itemkey;
1723
1724 EXCEPTION
1725 WHEN OTHERS THEN
1726 wf_core.context(g_package_name,'NOTIFY_SUPPLIER_USER_APPROVED',l_itemtype,l_itemkey);
1727 raise_application_error(-20053, 'Failure at step ' || l_step, true);
1728 END notify_supplier_user_approved;
1729
1730 -- notify the supplier that his/her supplier registration is
1731 -- rejected
1732 PROCEDURE notify_supplier_rejected
1733 (p_supplier_reg_id IN NUMBER,
1734 x_itemtype OUT nocopy VARCHAR2,
1735 x_itemkey OUT nocopy VARCHAR2,
1736 x_receiver OUT nocopy VARCHAR2
1737 )
1738 IS
1739 l_itemtype wf_items.item_type%TYPE;
1740 l_itemkey wf_items.item_key%TYPE;
1741 l_process wf_process_activities.process_name%TYPE;
1742
1743 CURSOR l_cur IS
1744 SELECT email_address, first_name, last_name
1745 FROM pos_contact_requests
1746 WHERE mapping_id IN (SELECT mapping_id FROM pos_supplier_mappings WHERE supplier_reg_id = p_supplier_reg_id)
1747 AND do_not_delete = 'Y';
1748
1749 l_rec l_cur%ROWTYPE;
1750 l_receiver wf_roles.name%TYPE;
1751 l_enterprise_name hz_parties.party_name%TYPE;
1752 l_display_name wf_roles.display_name%TYPE;
1753 l_step NUMBER;
1754 BEGIN
1755 l_step := 0;
1756 get_enterprise_name(l_enterprise_name);
1757
1758 l_step := 1;
1759 OPEN l_cur;
1760 FETCH l_cur INTO l_rec;
1761 IF l_cur%notfound THEN
1762 CLOSE l_cur;
1763 RAISE no_data_found;
1764 END IF;
1765 CLOSE l_cur;
1766
1767 l_step := 2;
1768 l_display_name := l_rec.first_name || ' ' || l_rec.last_name;
1769 l_process := 'PSUPPLIER_REJECTED';
1770 get_adhoc_role_name(l_process, l_receiver);
1771
1772 l_step := 3;
1773 wf_directory.CreateAdHocUser
1774 (name => l_receiver,
1775 display_name => l_display_name,
1776 email_address => l_rec.email_address
1777 );
1781
1778
1779 l_step := 4;
1780 get_wf_item_type (l_itemtype);
1782 l_step := 7;
1783 get_wf_item_key (l_process,
1784 To_char(p_supplier_reg_id),
1785 l_itemkey);
1786
1787 l_step := 8;
1788 wf_engine.CreateProcess(itemtype => l_itemtype,
1789 itemkey => l_itemkey,
1790 process => l_process);
1791
1792 l_step := 9;
1793 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1794 itemkey => l_itemkey,
1795 aname => 'RECEIVER',
1796 avalue => l_receiver);
1797
1798 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1799 itemkey => l_itemkey,
1800 aname => 'ENTERPRISE_NAME',
1801 avalue => l_enterprise_name);
1802
1803 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1804 itemkey => l_itemkey,
1805 aname => 'BUYER_NOTE',
1806 avalue => 'PLSQL:POS_SPM_WF_PKG1.BUYER_NOTE/'||To_char(p_supplier_reg_id));
1807
1808 l_step := 10;
1809 wf_engine.StartProcess(itemtype => l_itemtype,
1810 itemkey => l_itemkey );
1811
1812 l_step := 11;
1813 x_itemtype := l_itemtype;
1814 x_itemkey := l_itemkey;
1815 x_receiver := l_receiver;
1816
1817 EXCEPTION
1818 WHEN OTHERS THEN
1819 wf_core.context(g_package_name,'NOTIFY_SUPPLIER_REJECTED',l_itemtype,l_itemkey);
1820 raise_application_error(-20050, 'Failure at step ' || l_step, true);
1821 END notify_supplier_rejected;
1822
1823 -- This procedure is used by workflow to generate the buyer note with proper heading
1824 -- in the notification to supplier when the supplier registration is approved or rejected.
1825 -- It should not be used for other purpose.
1826 --
1827 -- Logic of the procedure: if notes_to_supplier is not null, returns a fnd message
1828 -- POS_SUPPREG_BUYER_NOTE_HEADING for heading and the note; otherwise, null.
1829 -- (bug 2725468).
1830 --
1831 PROCEDURE buyer_note
1832 (document_id IN VARCHAR2,
1833 display_type IN VARCHAR2,
1834 document IN OUT nocopy VARCHAR2,
1835 document_type IN OUT nocopy VARCHAR2)
1836 IS
1837 l_supplier_reg_id NUMBER;
1838
1839 CURSOR l_cur IS
1840 SELECT note_to_supplier
1841 FROM pos_supplier_registrations
1842 WHERE supplier_reg_id = l_supplier_reg_id;
1843
1844 l_rec l_cur%ROWTYPE;
1845 l_enterprise_name hz_parties.party_name%TYPE;
1846 l_step NUMBER;
1847 BEGIN
1848 l_step := 0;
1849 -- the document id should be the supplier_reg_id for the registration in pos_supplier_registrations
1850 l_supplier_reg_id := To_number(document_id);
1851
1852 l_step := 1;
1853 OPEN l_cur;
1854 FETCH l_cur INTO l_rec;
1855 CLOSE l_cur;
1856 IF l_rec.note_to_supplier IS NULL THEN
1857 document := NULL;
1858 ELSE
1859 get_enterprise_name(l_enterprise_name);
1860 fnd_message.set_name('POS','POS_SUPPREG_BUYER_NOTE_HEADING');
1861 fnd_message.set_token('ENTERPRISE_NAME', l_enterprise_name);
1862 IF display_type = 'text/html' THEN
1863 document_type := 'text/html';
1864 document := fnd_message.get || '<br>' || l_rec.note_to_supplier || '<p>';
1865 ELSE
1866 document := g_new_line || fnd_message.get || g_new_line || l_rec.note_to_supplier || g_new_line;
1867 END IF;
1868 END IF;
1869
1870 EXCEPTION
1871 WHEN OTHERS THEN
1872 wf_core.context(g_package_name,'BUYER_NOTE',Sqlerrm);
1873 raise_application_error(-20051, 'Failure at step ' || l_step, true);
1874 END buyer_note;
1875
1876 -- generates the buyer's note document for notifications to suppliers on
1877 -- bank account approvals
1878 PROCEDURE bank_acct_buyer_note
1879 (document_id IN VARCHAR2,
1880 display_type IN VARCHAR2,
1881 document IN OUT nocopy VARCHAR2,
1882 document_type IN OUT nocopy VARCHAR2)
1883 IS
1884 l_step NUMBER;
1885 BEGIN
1886 l_step := 0;
1887
1888 IF document_id IS NULL THEN
1889 document := NULL;
1890 ELSE
1891 fnd_message.set_name('POS','POS_SBD_BUYER_NOTE_HEADER');
1892 IF display_type = 'text/html' THEN
1893 document_type := 'text/html';
1894 document := fnd_message.get || '<br>' || document_id;
1895 ELSE
1896 document := fnd_message.get || g_new_line || document_id || g_new_line;
1897 END IF;
1898 END IF;
1899
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902 wf_core.context(g_package_name,'BANK_ACCT_BUYER_NOTE',Sqlerrm);
1903 raise_application_error(-20051, 'Failure at step ' || l_step, true);
1904 END bank_acct_buyer_note;
1905
1906 --
1907 --
1908 --
1909 -- The following section are related to supplier bank account project
1910 --
1911 --
1912 -- wf function activity to setup receiver (suppliers) for account actions
1913 PROCEDURE setup_acct_action_receiver
1914 (itemtype IN VARCHAR2,
1915 itemkey IN VARCHAR2,
1916 actid IN NUMBER,
1920 l_receiver wf_roles.name%TYPE;
1917 funcmode IN VARCHAR2,
1918 resultout OUT nocopy VARCHAR2)
1919 IS
1921 l_vendor_id NUMBER;
1922 l_function_id NUMBER;
1923
1924 CURSOR l_receiver_cur IS
1925 select DISTINCT fu.user_name
1926 from fnd_user fu,
1927 fnd_responsibility fr,
1928 fnd_user_resp_groups_direct furg,
1929 hz_relationships hr1, ap_suppliers ap_sup, hz_party_usg_assignments hpua
1930 where fr.menu_id IN
1931 (SELECT fme.menu_id
1932 FROM fnd_menu_entries fme
1933 START WITH fme.function_id = l_function_id
1934 CONNECT BY PRIOR menu_id = sub_menu_id
1935 )
1936 AND ( furg.end_date is null or furg.end_date > sysdate )
1937 AND furg.security_group_id = 0
1938 AND fr.responsibility_id = furg.responsibility_id
1939 AND fr.application_id = furg.responsibility_application_id
1940 AND fu.user_id = furg.user_id
1941 and fu.person_party_id = hr1.subject_id
1942 and hr1.subject_type = 'PERSON'
1943 and hr1.relationship_type = 'CONTACT'
1944 and hr1.relationship_code = 'CONTACT_OF'
1945 and hr1.object_type = 'ORGANIZATION'
1946 and hr1.status = 'A'
1947 and hr1.start_date <= sysdate
1948 and ( hr1.end_date IS NULL OR hr1.end_date > sysdate)
1949 and hr1.object_id = ap_sup.party_id
1950 and ap_sup.vendor_id = l_vendor_id
1951 and hpua.party_id = hr1.subject_id
1952 and hpua.status_flag = 'A'
1953 and hpua.party_usage_code = 'SUPPLIER_CONTACT'
1954 and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate);
1955
1956 BEGIN
1957
1958 IF ( funcmode = 'RUN' ) THEN
1959 l_vendor_id :=
1960 wf_engine.GetItemAttrNumber(itemtype, itemkey, 'VENDOR_ID');
1961 create_adhoc_role('PACCOUNT_ACTION_' || l_vendor_id, l_receiver);
1962
1963 wf_engine.SetItemAttrText (itemtype => itemtype,
1964 itemkey => itemkey,
1965 aname => 'RECEIVER',
1966 avalue => l_receiver);
1967
1968 l_function_id := get_function_id(g_supplier_function_name);
1969
1970 FOR l_user_rec IN l_receiver_cur LOOP
1971 AddUserToAdHocRole(l_receiver, l_user_rec.user_name);
1972
1973 END LOOP;
1974 END IF;
1975 resultout := 'COMPLETE';
1976
1977 EXCEPTION
1978 WHEN OTHERS THEN
1979 WF_CORE.CONTEXT ('POS_SPM_WF_PKG1', 'setup_acct_action_receiver', itemtype, itemkey, to_char(actid), funcmode);
1980 END setup_acct_action_receiver;
1981
1982 -- wf function activity to setup buyer receivers for supplier account update
1983 PROCEDURE setup_acct_upd_buyer_rcvr
1984 (itemtype IN VARCHAR2,
1985 itemkey IN VARCHAR2,
1986 actid IN NUMBER,
1987 funcmode IN VARCHAR2,
1988 resultout OUT nocopy VARCHAR2)
1989 IS
1990 l_receiver wf_roles.name%TYPE;
1991 l_cur g_refcur;
1992 l_count NUMBER;
1993 l_vendor_id NUMBER;
1994 l_bank_account_name iby_ext_bank_accounts_v.bank_account_name%TYPE;
1995 l_bank_account_number iby_ext_bank_accounts_v.bank_account_number%TYPE;
1996 l_currency_code iby_ext_bank_accounts_v.currency_code%TYPE;
1997 BEGIN
1998 IF ( funcmode = 'RUN' ) THEN
1999
2000 l_vendor_id :=
2001 wf_engine.GetItemAttrNumber(itemtype, itemkey, 'VENDOR_ID');
2002
2003 l_bank_account_number :=
2004 wf_engine.GetItemAttrText(itemtype, itemkey, 'BANK_ACCOUNT_NUMBER');
2005
2006 l_currency_code :=
2007 wf_engine.GetItemAttrText(itemtype, itemkey, 'CURRENCY_CODE');
2008
2009 l_bank_account_name :=
2010 wf_engine.GetItemAttrText(itemtype, itemkey, 'BANK_ACCOUNT_NAME');
2011
2012 create_adhoc_role('PACCOUNT_UPDATED_'||l_bank_account_number, l_receiver);
2013
2014 wf_engine.SetItemAttrText (itemtype => itemtype,
2015 itemkey => itemkey,
2016 aname => 'RECEIVER',
2017 avalue => l_receiver);
2018
2019 get_buyers('SUPP_BANK_ACCT_CHANGE_REQ',l_cur);
2020 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
2021
2022 IF l_count > 0 THEN
2023 resultout := 'COMPLETE:Y';
2024 ELSE
2025 resultout := 'COMPLETE:N';
2026 END IF;
2027 RETURN;
2028 END IF;
2029
2030 resultout := ' ';
2031 RETURN;
2032
2033 EXCEPTION
2034 WHEN OTHERS THEN
2035 WF_CORE.CONTEXT ('POS_SPM_WF_PKG1', 'setup_acct_upd_buyer_rcvr', itemtype,
2036 itemkey, to_char(actid), funcmode);
2037 RAISE;
2038 END setup_acct_upd_buyer_rcvr;
2039
2040 -- return a cursor of usernames of suppliers
2041 -- whose have profile management function
2042 PROCEDURE get_spm_supplier_for_vendor
2043 (p_vendor_id IN NUMBER,
2044 x_refcur OUT nocopy g_refcur)
2045 IS
2046 l_function_id NUMBER;
2047 BEGIN
2048 l_function_id := get_function_id (g_supplier_function_name);
2049
2050 -- Specifically, this query returns
2051 -- supplier admins who have a responsibility that has a menu that includes
2055 select DISTINCT psuv.user_name
2052 -- the specific supplier function;
2053 --
2054 OPEN x_refcur FOR
2056 from fnd_responsibility fr,
2057 fnd_user_resp_groups_direct furg,
2058 pos_supplier_users_v psuv
2059 where fr.menu_id IN
2060 (SELECT fme.menu_id
2061 FROM fnd_menu_entries fme
2062 START WITH fme.function_id = l_function_id
2063 CONNECT BY PRIOR menu_id = sub_menu_id
2064 )
2065 AND ( furg.end_date is null or furg.end_date > sysdate )
2066 AND furg.security_group_id = 0
2067 AND fr.responsibility_id = furg.responsibility_id
2068 AND fr.application_id = furg.responsibility_application_id
2069 AND psuv.user_id = furg.user_id
2070 AND psuv.vendor_id = p_vendor_id;
2071 END get_spm_supplier_for_vendor;
2072
2073 -- wf function activity to setup supplier receivers for buyer account update
2074 PROCEDURE setup_acct_upd_supp_rcvr
2075 (itemtype IN VARCHAR2,
2076 itemkey IN VARCHAR2,
2077 actid IN NUMBER,
2078 funcmode IN VARCHAR2,
2079 resultout OUT nocopy VARCHAR2)
2080 IS
2081 BEGIN
2082 -- This method is no longer required since IBY now controls the bank account creation
2083 -- and update flows.
2084 null;
2085
2086 END setup_acct_upd_supp_rcvr;
2087
2088 -- wf function activity to setup buyer receiver for account creation
2089 PROCEDURE setup_acct_crt_buyer_rcvr
2090 (itemtype IN VARCHAR2,
2091 itemkey IN VARCHAR2,
2092 actid IN NUMBER,
2093 funcmode IN VARCHAR2,
2094 resultout OUT nocopy VARCHAR2)
2095 IS
2096 l_receiver wf_roles.name%TYPE;
2097 l_cur g_refcur;
2098 l_count NUMBER;
2099 l_vendor_id NUMBER;
2100 l_bank_account_name iby_ext_bank_accounts_v.bank_account_name%TYPE;
2101 l_bank_account_number iby_ext_bank_accounts_v.bank_account_number%TYPE;
2102 l_currency_code iby_ext_bank_accounts_v.currency_code%TYPE;
2103
2104 BEGIN
2105 IF ( funcmode = 'RUN' ) THEN
2106
2107 l_vendor_id :=
2108 wf_engine.GetItemAttrNumber(itemtype, itemkey, 'VENDOR_ID');
2109
2110 l_bank_account_number :=
2111 wf_engine.GetItemAttrText(itemtype, itemkey, 'BANK_ACCOUNT_NUMBER');
2112
2113 l_currency_code :=
2114 wf_engine.GetItemAttrText(itemtype, itemkey, 'CURRENCY_CODE');
2115
2116 l_bank_account_name :=
2117 wf_engine.GetItemAttrText(itemtype, itemkey, 'BANK_ACCOUNT_NAME');
2118
2119 create_adhoc_role('PACCOUNT_CREATED_'|| l_vendor_id, l_receiver);
2120
2121 wf_engine.SetItemAttrText (itemtype => itemtype,
2122 itemkey => itemkey,
2123 aname => 'RECEIVER',
2124 avalue => l_receiver);
2125
2126 get_buyers('SUPP_BANK_ACCT_CHANGE_REQ',l_cur);
2127 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
2128
2129 IF l_count > 0 THEN
2130 resultout := 'COMPLETE:Y';
2131 ELSE
2132 resultout := 'COMPLETE:N';
2133 END IF;
2134 RETURN;
2135 END IF;
2136
2137 resultout := ' ';
2138 RETURN;
2139
2140 EXCEPTION
2141 WHEN OTHERS THEN
2142 WF_CORE.CONTEXT ('POS_SPM_WF_PKG1', 'setup_acct_crt_buyer_rcvr', itemtype,itemkey, to_char(actid), funcmode);
2143 END setup_acct_crt_buyer_rcvr;
2144
2145 -- wf function activity to setup supplier receiver for account creation
2146 PROCEDURE setup_acct_crt_supp_rcvr
2147 (itemtype IN VARCHAR2,
2148 itemkey IN VARCHAR2,
2149 actid IN NUMBER,
2150 funcmode IN VARCHAR2,
2151 resultout OUT nocopy VARCHAR2)
2152 IS
2153 BEGIN
2154 -- This method is no longer required since IBY now controls the bank account creation
2155 -- and update flows.
2156 null;
2157 END setup_acct_crt_supp_rcvr;
2158
2159 PROCEDURE notify_account_create
2160 (p_vendor_id IN NUMBER,
2161 p_bank_name IN VARCHAR2,
2162 p_bank_account_number IN VARCHAR2,
2163 x_itemtype OUT nocopy VARCHAR2,
2164 x_itemkey OUT nocopy VARCHAR2)
2165 IS
2166 l_itemtype wf_items.item_type%TYPE;
2167 l_itemkey wf_items.item_key%TYPE;
2168 l_process wf_process_activities.process_name%TYPE;
2169 l_step NUMBER;
2170 l_supplier_name ap_suppliers.vendor_name%TYPE;
2171 l_first_name hz_parties.person_first_name%TYPE;
2172 l_last_name hz_parties.person_last_name%TYPE;
2173
2174 BEGIN
2175 l_step := 0;
2176 get_supplier_name(p_vendor_id, l_supplier_name);
2177 get_current_user_name(l_first_name, l_last_name);
2178
2179 l_step := 1;
2180 get_wf_item_type (l_itemtype);
2181
2182 l_step := 2;
2183 l_process := 'PACCOUNT_CREATED';
2184
2185 get_wf_item_key (l_process,
2186 To_char(p_vendor_id),
2187 l_itemkey);
2188
2189 l_step := 3;
2190 wf_engine.CreateProcess(itemtype => l_itemtype,
2191 itemkey => l_itemkey,
2192 process => l_process);
2193
2194 l_step := 4;
2195 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2196 itemkey => l_itemkey,
2197 aname => 'URL',
2198 avalue => pos_url_pkg.get_dest_page_url ('POS_SBD_BUYER_MAIN', 'BUYER'));
2199
2203 avalue => l_supplier_name);
2200 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2201 itemkey => l_itemkey,
2202 aname => 'SUPPLIER_NAME',
2204
2205 wf_engine.SetItemAttrNumber (itemtype => l_itemtype,
2206 itemkey => l_itemkey,
2207 aname => 'VENDOR_ID',
2208 avalue => p_vendor_id);
2209
2210 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2211 itemkey => l_itemkey,
2212 aname => 'BANK_NAME',
2213 avalue => p_bank_name);
2214
2215 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2216 itemkey => l_itemkey,
2217 aname => 'BANK_ACCOUNT_NUMBER',
2218 avalue => IBY_EXT_BANKACCT_PUB.Mask_Bank_Number(p_bank_account_number));
2219
2220 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2221 itemkey => l_itemkey,
2222 aname => 'FIRST_NAME',
2223 avalue => l_first_name);
2224
2225 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2226 itemkey => l_itemkey,
2227 aname => 'LAST_NAME',
2228 avalue => l_last_name);
2229
2230 l_step := 5;
2231 wf_engine.StartProcess(itemtype => l_itemtype,
2232 itemkey => l_itemkey );
2233
2234 x_itemtype := l_itemtype;
2235 x_itemkey := l_itemkey;
2236
2237 EXCEPTION
2238 WHEN OTHERS THEN
2239 wf_core.context(g_package_name,'NOTIFY_ACCOUNT_CREATE',l_itemtype,l_itemkey);
2240 raise_application_error(-20050, 'Failure at step ' || l_step, true);
2241
2242 END notify_account_create;
2243
2244 PROCEDURE notify_buyer_create_account
2245 (p_vendor_id IN NUMBER,
2246 p_bank_name IN VARCHAR2,
2247 p_bank_account_number IN VARCHAR2,
2248 x_itemtype OUT nocopy VARCHAR2,
2249 x_itemkey OUT nocopy VARCHAR2)
2250 IS
2251 l_itemtype wf_items.item_type%TYPE;
2252 l_itemkey wf_items.item_key%TYPE;
2253 l_process wf_process_activities.process_name%TYPE;
2254 l_step NUMBER;
2255 l_supplier_name ap_suppliers.vendor_name%TYPE;
2256 l_enterprise_name hz_parties.party_name%TYPE;
2257 l_first_name hz_parties.person_first_name%TYPE;
2258 l_last_name hz_parties.person_last_name%TYPE;
2259
2260 BEGIN
2261 l_step := 0;
2262
2263 get_supplier_name(p_vendor_id, l_supplier_name);
2264 get_enterprise_name(l_enterprise_name);
2265 get_current_user_name(l_first_name, l_last_name);
2266
2267 l_step := 1;
2268
2269 get_wf_item_type (l_itemtype);
2270
2271 l_step := 2;
2272
2273 l_process := 'PACCT_BUYER_CREATE';
2274
2275 get_wf_item_key (l_process,
2276 To_char(p_vendor_id),
2277 l_itemkey);
2278
2279 l_step := 3;
2280
2281 wf_engine.CreateProcess(itemtype => l_itemtype,
2282 itemkey => l_itemkey,
2283 process => l_process);
2284
2285 l_step := 4;
2286
2287 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2288 itemkey => l_itemkey,
2289 aname => 'URL',
2290 avalue => pos_url_pkg.get_external_login_url);
2291
2292 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2293 itemkey => l_itemkey,
2294 aname => 'SUPPLIER_NAME',
2295 avalue => l_supplier_name);
2296
2297 wf_engine.SetItemAttrNumber (itemtype => l_itemtype,
2298 itemkey => l_itemkey,
2299 aname => 'VENDOR_ID',
2300 avalue => p_vendor_id);
2301
2302 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2303 itemkey => l_itemkey,
2304 aname => 'BANK_NAME',
2305 avalue => p_bank_name);
2306
2307 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2308 itemkey => l_itemkey,
2309 aname => 'BANK_ACCOUNT_NUMBER',
2310 avalue => IBY_EXT_BANKACCT_PUB.Mask_Bank_Number(p_bank_account_number));
2311
2312 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2313 itemkey => l_itemkey,
2314 aname => 'ENTERPRISE_NAME',
2315 avalue => l_enterprise_name);
2316
2317 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2318 itemkey => l_itemkey,
2319 aname => 'FIRST_NAME',
2320 avalue => l_first_name);
2321
2322 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2323 itemkey => l_itemkey,
2324 aname => 'LAST_NAME',
2328 wf_engine.StartProcess(itemtype => l_itemtype,
2325 avalue => l_last_name);
2326 l_step := 5;
2327
2329 itemkey => l_itemkey );
2330
2331 x_itemtype := l_itemtype;
2332 x_itemkey := l_itemkey;
2333
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 wf_core.context(g_package_name,'NOTIFY_BUYER_CREATE_ACCOUNT',l_itemtype,l_itemkey);
2337 raise_application_error(-20050, 'Failure at step ' || l_step, true);
2338 end notify_buyer_create_account;
2339
2340 PROCEDURE notify_account_update
2341 (p_vendor_id IN NUMBER,
2342 p_bank_name IN VARCHAR2,
2343 p_bank_account_number IN VARCHAR2,
2344 p_currency_code IN VARCHAR2,
2345 p_bank_account_name IN VARCHAR2,
2346 x_itemtype OUT nocopy VARCHAR2,
2347 x_itemkey OUT nocopy VARCHAR2)
2348 IS
2349 l_itemtype wf_items.item_type%TYPE;
2350 l_itemkey wf_items.item_key%TYPE;
2351 l_process wf_process_activities.process_name%TYPE;
2352 l_step NUMBER;
2353 l_supplier_name ap_suppliers.vendor_name%TYPE;
2354 l_first_name hz_parties.person_first_name%TYPE;
2355 l_last_name hz_parties.person_last_name%TYPE;
2356
2357 BEGIN
2358 l_step := 0;
2359
2360 get_supplier_name(p_vendor_id, l_supplier_name);
2361 get_current_user_name(l_first_name, l_last_name);
2362
2363 l_step := 1;
2364
2365 get_wf_item_type (l_itemtype);
2366
2367 l_step := 2;
2368
2369 l_process := 'PACCOUNT_UPDATED';
2370
2371 get_wf_item_key (l_process,
2372 To_char(p_vendor_id),
2373 l_itemkey);
2374
2375 l_step := 3;
2376
2377 wf_engine.CreateProcess(itemtype => l_itemtype,
2378 itemkey => l_itemkey,
2379 process => l_process);
2380
2381 l_step := 4;
2382 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2383 itemkey => l_itemkey,
2384 aname => 'URL',
2385 avalue => pos_url_pkg.get_dest_page_url ('POS_SBD_BUYER_MAIN', 'BUYER'));
2386
2387 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2388 itemkey => l_itemkey,
2389 aname => 'SUPPLIER_NAME',
2390 avalue => l_supplier_name);
2391
2392 wf_engine.SetItemAttrNumber (itemtype => l_itemtype,
2393 itemkey => l_itemkey,
2394 aname => 'VENDOR_ID',
2395 avalue => p_vendor_id);
2396
2397 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2398 itemkey => l_itemkey,
2399 aname => 'BANK_NAME',
2400 avalue => p_bank_name);
2401
2402 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2403 itemkey => l_itemkey,
2404 aname => 'BANK_ACCOUNT_NUMBER',
2405 avalue => IBY_EXT_BANKACCT_PUB.Mask_Bank_Number(p_bank_account_number));
2406
2407 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2408 itemkey => l_itemkey,
2409 aname => 'CURRENCY_CODE',
2410 avalue => p_currency_code);
2411
2412 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2413 itemkey => l_itemkey,
2414 aname => 'BANK_ACCOUNT_NAME',
2415 avalue => p_bank_account_name);
2416
2417 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2418 itemkey => l_itemkey,
2419 aname => 'FIRST_NAME',
2420 avalue => l_first_name);
2421
2422 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2423 itemkey => l_itemkey,
2424 aname => 'LAST_NAME',
2425 avalue => l_last_name);
2426
2427 l_step := 5;
2428
2429 wf_engine.StartProcess(itemtype => l_itemtype,
2430 itemkey => l_itemkey );
2431
2432 x_itemtype := l_itemtype;
2433 x_itemkey := l_itemkey;
2434
2435 EXCEPTION
2436 WHEN OTHERS THEN
2437 wf_core.context(g_package_name,'NOTIFY_ACCOUNT_UPDATE',l_itemtype,l_itemkey);
2438 raise_application_error(-20050, 'Failure at step ' || l_step, true);
2439
2440 END notify_account_update;
2441
2442 PROCEDURE notify_buyer_update_account
2443 (p_vendor_id IN NUMBER,
2444 p_bank_name IN VARCHAR2,
2445 p_bank_account_number IN VARCHAR2,
2446 p_currency_code IN VARCHAR2,
2447 p_bank_account_name IN VARCHAR2,
2448 x_itemtype OUT nocopy VARCHAR2,
2449 x_itemkey OUT nocopy VARCHAR2)
2450 IS
2451 l_itemtype wf_items.item_type%TYPE;
2452 l_itemkey wf_items.item_key%TYPE;
2453 l_process wf_process_activities.process_name%TYPE;
2454 l_step NUMBER;
2455 l_supplier_name ap_suppliers.vendor_name%TYPE;
2456 l_first_name hz_parties.person_first_name%TYPE;
2460 l_step := 0;
2457 l_last_name hz_parties.person_last_name%TYPE;
2458
2459 BEGIN
2461
2462 get_supplier_name(p_vendor_id, l_supplier_name);
2463 get_current_user_name(l_first_name, l_last_name);
2464
2465 l_step := 1;
2466
2467 get_wf_item_type (l_itemtype);
2468
2469 l_step := 2;
2470
2471 l_process := 'PACCT_BUYER_UPDATE';
2472
2473 get_wf_item_key (l_process,
2474 To_char(p_vendor_id),
2475 l_itemkey);
2476
2477 l_step := 3;
2478
2479 wf_engine.CreateProcess(itemtype => l_itemtype,
2480 itemkey => l_itemkey,
2481 process => l_process);
2482
2483 l_step := 4;
2484
2485 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2486 itemkey => l_itemkey,
2487 aname => 'URL',
2488 avalue => pos_url_pkg.get_external_login_url);
2489
2490 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2491 itemkey => l_itemkey,
2492 aname => 'SUPPLIER_NAME',
2493 avalue => l_supplier_name);
2494
2495 wf_engine.SetItemAttrNumber (itemtype => l_itemtype,
2496 itemkey => l_itemkey,
2497 aname => 'VENDOR_ID',
2498 avalue => p_vendor_id);
2499
2500 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2501 itemkey => l_itemkey,
2502 aname => 'BANK_NAME',
2503 avalue => p_bank_name);
2504
2505 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2506 itemkey => l_itemkey,
2507 aname => 'BANK_ACCOUNT_NUMBER',
2508 avalue => IBY_EXT_BANKACCT_PUB.Mask_Bank_Number(p_bank_account_number));
2509
2510 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2511 itemkey => l_itemkey,
2512 aname => 'CURRENCY_CODE',
2513 avalue => p_currency_code);
2514
2515 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2516 itemkey => l_itemkey,
2517 aname => 'BANK_ACCOUNT_NAME',
2518 avalue => p_bank_account_name);
2519
2520 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2521 itemkey => l_itemkey,
2522 aname => 'FIRST_NAME',
2523 avalue => l_first_name);
2524
2525 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2526 itemkey => l_itemkey,
2527 aname => 'LAST_NAME',
2528 avalue => l_last_name);
2529
2530 l_step := 5;
2531
2532 wf_engine.StartProcess(itemtype => l_itemtype,
2533 itemkey => l_itemkey );
2534
2535 x_itemtype := l_itemtype;
2536 x_itemkey := l_itemkey;
2537
2538 EXCEPTION
2539 WHEN OTHERS THEN
2540 wf_core.context(g_package_name,'NOTIFY_BUYER_UPDATE_ACCOUNT',l_itemtype,l_itemkey);
2541 raise_application_error(-20050, 'Failure at step ' || l_step, true);
2542
2543 END notify_buyer_update_account;
2544
2545 PROCEDURE notify_sup_on_acct_action
2546 (p_bank_account_number IN VARCHAR2,
2547 p_vendor_id IN NUMBER,
2548 p_bank_name IN VARCHAR2,
2549 p_request_status IN VARCHAR2,
2550 p_note IN VARCHAR2,
2551 x_itemtype OUT nocopy VARCHAR2,
2552 x_itemkey OUT nocopy VARCHAR2
2553 ) IS
2554
2555 l_itemtype wf_items.item_type%TYPE;
2556 l_itemkey wf_items.item_key%TYPE;
2557 l_process wf_process_activities.process_name%TYPE;
2558 l_enterprise_name hz_parties.party_name%TYPE;
2559 l_step NUMBER;
2560
2561 BEGIN
2562 l_step := 0;
2563
2564 IF p_request_status = 'APPROVED' THEN
2565 l_process := 'PACCOUNT_APPROVED';
2566 ELSIF p_request_status = 'REJECTED' THEN
2567 l_process := 'PACCOUNT_REJECTED';
2568 ELSIF p_request_status = 'IN_VERIFICATION' THEN
2569 l_process := 'PACCOUNT_VERIFY';
2570 ELSIF p_request_status = 'VERIFICATION_FAILED' THEN
2571 l_process := 'PACCOUNT_VERIFY_FAILED';
2572 END IF;
2573
2574 get_wf_item_type (l_itemtype);
2575 get_wf_item_key (l_process,To_char(p_vendor_id),l_itemkey);
2576
2577 l_step := 3;
2578
2579 wf_engine.CreateProcess(itemtype => l_itemtype,
2580 itemkey => l_itemkey,
2581 process => l_process);
2582
2583 l_step := 4;
2584 wf_engine.SetItemAttrNumber (itemtype => l_itemtype,
2585 itemkey => l_itemkey,
2586 aname => 'VENDOR_ID',
2587 avalue => p_vendor_id);
2588
2589 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2590 itemkey => l_itemkey,
2591 aname => 'BANK_NAME',
2595 itemkey => l_itemkey,
2592 avalue => p_bank_name);
2593
2594 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2596 aname => 'BANK_ACCOUNT_NUMBER',
2597 avalue => IBY_EXT_BANKACCT_PUB.Mask_Bank_Number(p_bank_account_number));
2598
2599 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2600 itemkey => l_itemkey,
2601 aname => 'BUYER_NOTE',
2602 avalue => 'PLSQL:POS_SPM_WF_PKG1.BANK_ACCT_BUYER_NOTE/'||p_note);
2603
2604 IF l_process = 'PACCOUNT_VERIFY' OR l_process = 'PACCOUNT_VERIFY_FAILED'
2605 THEN
2606 get_enterprise_name(l_enterprise_name);
2607 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2608 itemkey => l_itemkey,
2609 aname => 'ENTERPRISE_NAME',
2610 avalue => l_enterprise_name);
2611 END IF;
2612
2613 l_step := 5;
2614
2615 wf_engine.StartProcess(itemtype => l_itemtype,
2616 itemkey => l_itemkey );
2617
2618 x_itemtype := l_itemtype;
2619 x_itemkey := l_itemkey;
2620
2621 END notify_sup_on_acct_action;
2622
2623 -- wf function activity to setup receiver for account address change or remove
2624 PROCEDURE setup_acct_addr_receiver
2625 (itemtype IN VARCHAR2,
2626 itemkey IN VARCHAR2,
2627 actid IN NUMBER,
2628 funcmode IN VARCHAR2,
2629 resultout OUT nocopy VARCHAR2)
2630 IS
2631 l_receiver wf_roles.name%TYPE;
2632 l_cur g_refcur;
2633 l_count NUMBER;
2634 l_vendor_id NUMBER;
2635 l_bank_branch_id NUMBER;
2636 l_currency_code iby_ext_bank_accounts_v.currency_code%TYPE;
2637 l_bank_account_number iby_ext_bank_accounts_v.bank_account_number%TYPE;
2638 l_bank_account_name iby_ext_bank_accounts_v.bank_account_name%TYPE;
2639 l_party_site_id NUMBER;
2640 BEGIN
2641 IF ( funcmode = 'RUN' ) THEN
2642
2643 l_vendor_id :=
2644 wf_engine.GetItemAttrNumber(itemtype, itemkey, 'VENDOR_ID');
2645
2646 l_bank_account_number :=
2647 wf_engine.GetItemAttrText(itemtype, itemkey, 'BANK_ACCOUNT_NUMBER');
2648
2649 l_currency_code :=
2650 wf_engine.GetItemAttrText(itemtype, itemkey, 'CURRENCY_CODE');
2651
2652 l_bank_branch_id :=
2653 wf_engine.GetItemAttrNumber(itemtype, itemkey, 'BANK_BRANCH_ID');
2654
2655 l_bank_account_name :=
2656 wf_engine.GetItemAttrText(itemtype, itemkey, 'BANK_ACCOUNT_NAME');
2657
2658 create_adhoc_role('PACCT_ADDR_'|| l_vendor_id, l_receiver);
2659
2660 wf_engine.SetItemAttrText (itemtype => itemtype,
2661 itemkey => itemkey,
2662 aname => 'RECEIVER',
2663 avalue => l_receiver);
2664
2665 get_buyers('SUPP_BANK_ACCT_CHANGE_REQ',l_cur);
2666 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
2667
2668 IF l_count > 0 THEN
2669 resultout := 'COMPLETE:Y';
2670 ELSE
2671 resultout := 'COMPLETE:N';
2672 END IF;
2673 RETURN;
2674 END IF;
2675
2676 resultout := ' ';
2677 RETURN;
2678
2679 EXCEPTION
2680 WHEN OTHERS THEN
2681 WF_CORE.CONTEXT ('POS_SPM_WF_PKG1', 'setup_acct_addr_receiver', itemtype,
2682 itemkey, to_char(actid), funcmode);
2683 RAISE;
2684 END setup_acct_addr_receiver;
2685
2686 PROCEDURE notify_acct_addr_crtchgrmv
2687 (p_vendor_id IN NUMBER,
2688 p_bank_name IN VARCHAR2,
2689 p_bank_account_number IN VARCHAR2,
2690 p_currency_code IN VARCHAR2,
2691 p_bank_account_name IN VARCHAR2,
2692 p_party_site_name IN VARCHAR2,
2693 p_action IN VARCHAR2,
2694 x_itemtype OUT nocopy VARCHAR2,
2695 x_itemkey OUT nocopy VARCHAR2
2696 ) IS
2697 l_itemtype wf_items.item_type%TYPE;
2698 l_itemkey wf_items.item_key%TYPE;
2699 l_process wf_process_activities.process_name%TYPE;
2700 l_step NUMBER;
2701 l_count NUMBER;
2702
2703 l_supplier_name ap_suppliers.vendor_name%TYPE;
2704 l_first_name hz_parties.person_first_name%TYPE;
2705 l_last_name hz_parties.person_last_name%TYPE;
2706
2707 BEGIN
2708 l_step := 0;
2709
2710 get_supplier_name(p_vendor_id, l_supplier_name);
2711 get_current_user_name(l_first_name, l_last_name);
2712
2713 l_step := 1;
2714
2715 get_wf_item_type (l_itemtype);
2716
2717 l_step := 2;
2718
2719 l_process := p_action;
2720
2721 get_wf_item_key (l_process,
2722 To_char(p_vendor_id),
2723 l_itemkey);
2724
2725 l_step := 3;
2726
2727 wf_engine.CreateProcess(itemtype => l_itemtype,
2728 itemkey => l_itemkey,
2729 process => l_process);
2730
2731 l_step := 4;
2732
2733 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2734 itemkey => l_itemkey,
2735 aname => 'URL',
2739 itemkey => l_itemkey,
2736 avalue => pos_url_pkg.get_dest_page_url ('POS_SBD_BUYER_MAIN', 'BUYER'));
2737
2738 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2740 aname => 'SUPPLIER_NAME',
2741 avalue => l_supplier_name);
2742
2743 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2744 itemkey => l_itemkey,
2745 aname => 'ADDRESS_NAME',
2746 avalue => p_party_site_name);
2747
2748 wf_engine.SetItemAttrNumber (itemtype => l_itemtype,
2749 itemkey => l_itemkey,
2750 aname => 'VENDOR_ID',
2751 avalue => p_vendor_id);
2752
2753 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2754 itemkey => l_itemkey,
2755 aname => 'BANK_NAME',
2756 avalue => p_bank_name);
2757
2758 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2759 itemkey => l_itemkey,
2760 aname => 'BANK_ACCOUNT_NUMBER',
2761 avalue => IBY_EXT_BANKACCT_PUB.Mask_Bank_Number(p_bank_account_number));
2762
2763 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2764 itemkey => l_itemkey,
2765 aname => 'CURRENCY_CODE',
2766 avalue => p_currency_code);
2767
2768 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2769 itemkey => l_itemkey,
2770 aname => 'BANK_ACCOUNT_NAME',
2771 avalue => p_bank_account_name);
2772
2773 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2774 itemkey => l_itemkey,
2775 aname => 'FIRST_NAME',
2776 avalue => l_first_name);
2777
2778 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2779 itemkey => l_itemkey,
2780 aname => 'LAST_NAME',
2781 avalue => l_last_name);
2782
2783 l_step := 5;
2784
2785 wf_engine.StartProcess(itemtype => l_itemtype,
2786 itemkey => l_itemkey );
2787
2788 x_itemtype := l_itemtype;
2789 x_itemkey := l_itemkey;
2790
2791 EXCEPTION
2792 WHEN OTHERS THEN
2793 wf_core.context(g_package_name,'NOTIFY_ACCT_ADDR_CRTCHGRMV',l_itemtype,l_itemkey);
2794 raise_application_error(-20050, 'Failure at step ' || l_step, true);
2795 END notify_acct_addr_crtchgrmv;
2796
2797 PROCEDURE notify_acct_addr_created
2798 (p_vendor_id IN NUMBER,
2799 p_bank_name IN VARCHAR2,
2800 p_bank_account_number IN VARCHAR2,
2801 p_currency_code IN VARCHAR2,
2802 p_bank_account_name IN VARCHAR2,
2803 p_party_site_name IN VARCHAR2,
2804 x_itemtype OUT nocopy VARCHAR2,
2805 x_itemkey OUT nocopy VARCHAR2)
2806 IS
2807 BEGIN
2808 notify_acct_addr_crtchgrmv(p_vendor_id,
2809 p_bank_name,
2810 p_bank_account_number,
2811 p_currency_code,
2812 p_bank_account_name,
2813 p_party_site_name,
2814 'PACCT_ADDR_CREATED',
2815 x_itemtype,
2816 x_itemkey);
2817
2818 END notify_acct_addr_created;
2819
2820 PROCEDURE notify_acct_addr_changed
2821 (p_vendor_id IN NUMBER,
2822 p_bank_name IN VARCHAR2,
2823 p_bank_account_number IN VARCHAR2,
2824 p_currency_code IN VARCHAR2,
2825 p_bank_account_name IN VARCHAR2,
2826 p_party_site_name IN VARCHAR2,
2827 x_itemtype OUT nocopy VARCHAR2,
2828 x_itemkey OUT nocopy VARCHAR2)
2829 IS
2830 BEGIN
2831 notify_acct_addr_crtchgrmv(p_vendor_id,
2832 p_bank_name,
2833 p_bank_account_number,
2834 p_currency_code,
2835 p_bank_account_name,
2836 p_party_site_name,
2837 'PACCT_ADDR_CHANGED',
2838 x_itemtype,
2839 x_itemkey);
2840
2841 END notify_acct_addr_changed;
2842
2843 PROCEDURE notify_acct_addr_removed
2844 (p_vendor_id IN NUMBER,
2845 p_bank_name IN VARCHAR2,
2846 p_bank_account_number IN VARCHAR2,
2847 p_currency_code IN VARCHAR2,
2848 p_bank_account_name IN VARCHAR2,
2849 p_party_site_name IN VARCHAR2,
2850 x_itemtype OUT nocopy VARCHAR2,
2851 x_itemkey OUT nocopy VARCHAR2)
2852 IS
2853 BEGIN
2854 notify_acct_addr_crtchgrmv(p_vendor_id,
2855 p_bank_name,
2856 p_bank_account_number,
2857 p_currency_code,
2858 p_bank_account_name,
2859 p_party_site_name,
2860 'PACCT_ADDR_REMOVED',
2861 x_itemtype,
2862 x_itemkey);
2863
2864 END notify_acct_addr_removed;
2865
2866 FUNCTION get_supplier_reg_url
2867 (p_reg_key IN VARCHAR2
2868 )
2869 RETURN VARCHAR2 IS
2870 BEGIN
2871 RETURN pos_url_pkg.get_external_url ||
2875
2872 'OA_HTML/jsp/pos/suppreg/SupplierRegister.jsp?regkey=' ||
2873 p_reg_key;
2874 END get_supplier_reg_url;
2876 PROCEDURE send_supplier_invite_reg_ntf
2877 (p_supplier_reg_id IN NUMBER
2878 )
2879 IS
2880 l_itemtype wf_items.item_type%TYPE;
2881 l_itemkey wf_items.item_key%TYPE;
2882 l_receiver wf_roles.name%TYPE;
2883 l_process wf_process_activities.process_name%TYPE;
2884
2885 CURSOR l_cur IS
2886 SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address
2887 FROM pos_supplier_registrations psr,
2888 pos_contact_requests pcr,
2889 pos_supplier_mappings psm
2890 WHERE psr.supplier_reg_id = psm.supplier_reg_id
2891 AND psr.supplier_reg_id = p_supplier_reg_id
2892 AND pcr.mapping_id = psm.mapping_id
2893 AND pcr.do_not_delete = 'Y';
2894
2895 l_rec l_cur%ROWTYPE;
2896
2897 l_enterprise_name hz_parties.party_name%TYPE;
2898 BEGIN
2899 OPEN l_cur;
2900 FETCH l_cur INTO l_rec;
2901 IF l_cur%notfound THEN
2902 CLOSE l_cur;
2903 RAISE no_data_found;
2904 END IF;
2905 CLOSE l_cur;
2906
2907 get_wf_item_type(l_itemtype);
2908 l_process := 'SEND_SUPPLIER_INVITE_REG_NTF';
2909 get_wf_item_key(l_process, p_supplier_reg_id, l_itemkey);
2910 l_receiver := l_process || '_' || p_supplier_reg_id;
2911
2912 wf_directory.createadhocuser
2913 ( name => l_receiver,
2914 display_name => l_receiver,
2915 email_address => l_rec.email_address
2916 );
2917
2918 wf_engine.CreateProcess(itemtype => l_itemtype,
2919 itemkey => l_itemkey,
2920 process => l_process
2921 );
2922
2923 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2924 itemkey => l_itemkey,
2925 aname => 'RECEIVER',
2926 avalue => l_receiver
2927 );
2928
2929 get_enterprise_name(l_enterprise_name);
2930
2931 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2932 itemkey => l_itemkey,
2933 aname => 'ENTERPRISE_NAME',
2934 avalue => l_enterprise_name
2935 );
2936
2937 wf_engine.SetItemAttrText (itemtype => l_itemtype,
2938 itemkey => l_itemkey,
2939 aname => 'URL',
2940 avalue => get_supplier_reg_url(l_rec.reg_key)
2941 );
2942
2943 wf_engine.StartProcess (itemtype => l_itemtype,
2944 itemkey => l_itemkey
2945 );
2946
2947 END send_supplier_invite_reg_ntf;
2948
2949 PROCEDURE send_supplier_reg_saved_ntf
2950 (p_supplier_reg_id IN NUMBER
2951 )
2952 IS
2953 l_itemtype wf_items.item_type%TYPE;
2954 l_itemkey wf_items.item_key%TYPE;
2955 l_receiver wf_roles.name%TYPE;
2956 l_process wf_process_activities.process_name%TYPE;
2957
2958 CURSOR l_cur IS
2959 SELECT psr.reg_key, pcr.first_name, pcr.last_name, pcr.email_address
2960 FROM pos_supplier_registrations psr,
2961 pos_contact_requests pcr,
2962 pos_supplier_mappings psm
2963 WHERE psr.supplier_reg_id = psm.supplier_reg_id
2964 AND psr.supplier_reg_id = p_supplier_reg_id
2965 AND pcr.mapping_id = psm.mapping_id
2966 AND pcr.do_not_delete = 'Y';
2967
2968 l_rec l_cur%ROWTYPE;
2969
2970 l_enterprise_name hz_parties.party_name%TYPE;
2971
2972 l_wf_role_rec wf_directory.wf_local_roles_rec_type;
2973
2974 BEGIN
2975 OPEN l_cur;
2976 FETCH l_cur INTO l_rec;
2977 IF l_cur%notfound THEN
2978 CLOSE l_cur;
2979 RAISE no_data_found;
2980 END IF;
2981 CLOSE l_cur;
2982
2983 get_wf_item_type(l_itemtype);
2984 l_process := 'SEND_SUPPLIER_REG_SAVED_NTF';
2985 get_wf_item_key(l_process, p_supplier_reg_id, l_itemkey);
2986 l_receiver := l_process || '_' || p_supplier_reg_id;
2987
2988 wf_directory.GetRoleInfo
2989 ( role => l_receiver,
2990 display_name => l_wf_role_rec.display_name,
2991 email_address => l_wf_role_rec.email_address,
2992 notification_preference => l_wf_role_rec.notification_preference,
2993 language => l_wf_role_rec.language,
2994 territory => l_wf_role_rec.territory
2995 );
2996
2997 if (l_wf_role_rec.email_address is null) then
2998 --ad hoc role doesn't exist, create a new one
2999 wf_directory.CreateAdHocRole
3000 ( role_name => l_receiver,
3001 role_display_name => l_receiver,
3002 email_address => l_rec.email_address
3003 );
3004 else
3005 --ad hoc role already exist, check for email address
3006 if (l_wf_role_rec.email_address <> l_rec.email_address) then
3007 --if the contact email address has been changed, modify the role email
3008 wf_directory.SetAdHocRoleAttr
3009 ( role_name => l_receiver,
3010 email_address => l_rec.email_address
3014
3011 );
3012 end if;
3013 end if;
3015 wf_engine.CreateProcess(itemtype => l_itemtype,
3016 itemkey => l_itemkey,
3017 process => l_process
3018 );
3019
3020 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3021 itemkey => l_itemkey,
3022 aname => 'RECEIVER',
3023 avalue => l_receiver
3024 );
3025
3026 get_enterprise_name(l_enterprise_name);
3027
3028 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3029 itemkey => l_itemkey,
3030 aname => 'ENTERPRISE_NAME',
3031 avalue => l_enterprise_name
3032 );
3033
3034 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3035 itemkey => l_itemkey,
3036 aname => 'URL',
3037 avalue => get_supplier_reg_url(l_rec.reg_key)
3038 );
3039
3040 wf_engine.StartProcess (itemtype => l_itemtype,
3041 itemkey => l_itemkey
3042 );
3043
3044 END send_supplier_reg_saved_ntf;
3045
3046 -- this is a private procedure. not intended to be public
3047 PROCEDURE notify_bank_aprv_supp_aprv
3048 (p_vendor_id IN NUMBER,
3049 x_itemtype OUT nocopy VARCHAR2,
3050 x_itemkey OUT nocopy VARCHAR2,
3051 x_receiver OUT nocopy VARCHAR2
3052 )
3053 IS
3054 l_supplier_name ap_suppliers.vendor_name%TYPE;
3055 l_itemtype wf_items.item_type%TYPE;
3056 l_itemkey wf_items.item_key%TYPE;
3057 l_receiver wf_roles.name%TYPE;
3058 l_cur g_refcur;
3059 l_count NUMBER;
3060 l_process wf_process_activities.process_name%TYPE;
3061 l_step NUMBER;
3062 BEGIN
3063 l_step := 0;
3064 get_supplier_name(p_vendor_id, l_supplier_name);
3065
3066 l_process := 'PSUPPLIER_ACCOUNT_APRV';
3067
3068 l_step := 1;
3069 -- setup receiver
3070 create_adhoc_role(l_process || '_' || p_vendor_id, l_receiver);
3071
3072 l_step := 3;
3073 get_buyers('SUPP_BANK_ACCT_CHANGE_REQ',l_cur);
3074
3075 l_step := 4;
3076 add_user_to_role_from_cur(l_cur, l_receiver, l_count);
3077 IF l_count < 1 THEN
3078 -- there is no one to notify, so we just return
3079 x_itemtype := NULL;
3080 x_itemkey := NULL;
3081 x_receiver := NULL;
3082 RETURN;
3083 END IF;
3084
3085 l_step := 5;
3086
3087 -- create workflow process
3088 get_wf_item_type (l_itemtype);
3089 get_wf_item_key (l_process, p_vendor_id, l_itemkey);
3090
3091 wf_engine.CreateProcess(itemtype => l_itemtype,
3092 itemkey => l_itemkey,
3093 process => l_process);
3094 l_step := 6;
3095 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3096 itemkey => l_itemkey,
3097 aname => 'RECEIVER',
3098 avalue => l_receiver);
3099
3100 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3101 itemkey => l_itemkey,
3102 aname => 'SUPPLIER_NAME',
3103 avalue => l_supplier_name);
3104
3105 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3106 itemkey => l_itemkey,
3107 aname => 'VENDOR_ID',
3108 avalue => p_vendor_id);
3109
3110 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3111 itemkey => l_itemkey,
3112 aname => 'URL',
3113 avalue => pos_url_pkg.get_dest_page_url('POS_SBD_BUYER_MAIN', 'BUYER'));
3114 l_step := 7;
3115 setup_actioner_private(l_itemtype, l_itemkey);
3116
3117 l_step := 8;
3118 wf_engine.StartProcess(itemtype => l_itemtype,
3119 itemkey => l_itemkey );
3120 l_step := 9;
3121 x_itemtype := l_itemtype;
3122 x_itemkey := l_itemkey;
3123 x_receiver := l_receiver;
3124
3125 EXCEPTION
3126 WHEN OTHERS THEN
3127 wf_core.context(g_package_name,'notify_bank_aprv_supp_aprv',l_itemtype,l_itemkey);
3128 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3129 FND_LOG.string(fnd_log.level_error, 'pos_spm_wf_pkg1' , 'Call to notify_bank_aprv_supp_aprv failed');
3130 END IF;
3131 END notify_bank_aprv_supp_aprv;
3132 -- notify the supplier that his/her supplier registration is approved
3133 -- when the user (primary contact) already exists in OID and auto-link of username is enabled
3134 PROCEDURE notify_supplier_apprv_ssosync
3135 (p_supplier_reg_id IN NUMBER,
3136 p_username IN VARCHAR2,
3137 x_itemtype OUT nocopy VARCHAR2,
3138 x_itemkey OUT nocopy VARCHAR2
3139 )
3140 IS
3144 l_enterprise_name hz_parties.party_name%TYPE;
3141 l_itemtype wf_items.item_type%TYPE;
3142 l_itemkey wf_items.item_key%TYPE;
3143 l_process wf_process_activities.process_name%TYPE;
3145 l_step NUMBER;
3146 BEGIN
3147 l_step := 0;
3148 get_enterprise_name(l_enterprise_name);
3149
3150 l_step := 1;
3151 l_process := 'PSUPPLIER_APPROVED_SSOSYNC';
3152 get_wf_item_type (l_itemtype);
3153
3154 l_step := 2;
3155 get_wf_item_key (l_process,
3156 To_char(p_supplier_reg_id),
3157 l_itemkey);
3158
3159 l_step := 3;
3160 wf_engine.CreateProcess(itemtype => l_itemtype,
3161 itemkey => l_itemkey,
3162 process => l_process);
3163
3164 l_step := 4;
3165 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3166 itemkey => l_itemkey,
3167 aname => 'RECEIVER',
3168 avalue => p_username);
3169
3170 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3171 itemkey => l_itemkey,
3172 aname => 'ENTERPRISE_NAME',
3173 avalue => l_enterprise_name);
3174
3175 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3176 itemkey => l_itemkey,
3177 aname => 'USERNAME',
3178 avalue => p_username);
3179
3180 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3181 itemkey => l_itemkey,
3182 aname => 'URL',
3183 avalue => pos_url_pkg.get_external_login_url);
3184
3185 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3186 itemkey => l_itemkey,
3187 aname => 'ADMIN_EMAIL',
3188 avalue => get_admin_email);
3189
3190 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3191 itemkey => l_itemkey,
3192 aname => 'BUYER_NOTE',
3193 avalue => 'PLSQL:POS_SPM_WF_PKG1.BUYER_NOTE/'||To_char(p_supplier_reg_id));
3194 l_step := 5;
3195 wf_engine.StartProcess(itemtype => l_itemtype,
3196 itemkey => l_itemkey );
3197 l_step := 6;
3198 x_itemtype := l_itemtype;
3199 x_itemkey := l_itemkey;
3200
3201 EXCEPTION
3202 WHEN OTHERS THEN
3203 wf_core.context(g_package_name,'NOTIFY_SUPPLIER_APPROVED_SSO_SYNC',l_itemtype,l_itemkey);
3204 raise_application_error(-20053, 'Failure at step ' || l_step, true);
3205 END notify_supplier_apprv_ssosync;
3206
3207 -- send email to non-primary contact of user registration
3208 -- when the user already exists in OID and auto-link of username is enabled
3209 PROCEDURE notify_user_approved_sso_sync
3210 (p_supplier_reg_id IN NUMBER,
3211 p_username IN VARCHAR2,
3212 x_itemtype OUT nocopy VARCHAR2,
3213 x_itemkey OUT nocopy VARCHAR2
3214 )
3215 IS
3216 l_itemtype wf_items.item_type%TYPE;
3217 l_itemkey wf_items.item_key%TYPE;
3218 l_process wf_process_activities.process_name%TYPE;
3219 l_enterprise_name hz_parties.party_name%TYPE;
3220 l_step VARCHAR2(100);
3221 BEGIN
3222 l_step := 1;
3223
3224 get_enterprise_name(l_enterprise_name);
3225
3226 l_process := 'SUPPLIER_USER_CREATED_SSOSYNC';
3227
3228 l_step := 2;
3229 get_wf_item_type (l_itemtype);
3230
3231 l_step := 3;
3232 get_wf_item_key (l_process,
3233 To_char(p_supplier_reg_id) || '_' || p_username,
3234 l_itemkey);
3235
3236 l_step := 4;
3237 wf_engine.CreateProcess(itemtype => l_itemtype,
3238 itemkey => l_itemkey,
3239 process => l_process);
3240
3241 l_step := 5;
3242 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3243 itemkey => l_itemkey,
3244 aname => 'RECEIVER',
3245 avalue => p_username);
3246
3247 l_step := 6;
3248 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3249 itemkey => l_itemkey,
3250 aname => 'ENTERPRISE_NAME',
3251 avalue => l_enterprise_name);
3252
3253 l_step := 7;
3254 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3255 itemkey => l_itemkey,
3256 aname => 'USERNAME',
3257 avalue => p_username);
3258
3259 l_step := 8;
3260
3261 l_step := 9;
3262 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3263 itemkey => l_itemkey,
3264 aname => 'URL',
3265 avalue => pos_url_pkg.get_external_login_url);
3266
3267 l_step := 10;
3268 wf_engine.SetItemAttrText (itemtype => l_itemtype,
3269 itemkey => l_itemkey,
3270 aname => 'ADMIN_EMAIL',
3271 avalue => get_admin_email);
3272
3273 l_step := 11;
3274
3275 wf_engine.StartProcess(itemtype => l_itemtype,
3276 itemkey => l_itemkey );
3277 x_itemtype := l_itemtype;
3278 x_itemkey := l_itemkey;
3279
3280 EXCEPTION
3281 WHEN OTHERS THEN
3282 wf_core.context(g_package_name,'NOTIFY_USER_APPROVED_SSO_SYNC',l_itemtype,l_itemkey);
3283 raise_application_error(-20053, 'Failure at step ' || l_step, true);
3284 END notify_user_approved_sso_sync;
3285
3286 -- CODE ADDED FOR BUSINESS CLASSIFICATION RE-CERTIFICATION ER
3287
3288 PROCEDURE bc_recert_workflow
3289 (
3290 ERRBUF OUT nocopy VARCHAR2,
3291 RETCODE OUT nocopy VARCHAR2
3292 )
3293 IS
3294
3295 l_supplier_count NUMBER;
3296 l_vendor_id NUMBER;
3297 ul NUMBER;
3298
3299 l_user_name VARCHAR2(100);
3300 l_email_address VARCHAR2(100);
3301 l_enterprise_name VARCHAR2(100);
3302 l_role_name VARCHAR2(200);
3303 l_role_display_name VARCHAR2(100);
3304 l_vendor_id_char VARCHAR2(20);
3305
3306 l_last_certification_date DATE;
3307 l_due_date DATE;
3308 l_expiration_date DATE;
3309
3310 l_itemtype wf_items.item_type%TYPE;
3311 l_purge_itemkey wf_items.item_key%TYPE;
3312 l_itemkey wf_items.item_key%TYPE;
3313 l_purge_item_key_type wf_items.item_key%TYPE;
3314 l_process wf_process_activities.process_name%TYPE;
3315 l_users WF_DIRECTORY.UserTable;
3316
3317 CURSOR l_vendor_id_cur IS
3318 SELECT APS.VENDOR_ID
3319 FROM AP_SUPPLIERS APS
3320 WHERE
3321 Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE) + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD')) = Trunc(SYSDATE) + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
3322 OR
3323 (
3324 Trunc(SYSDATE) > Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD')))
3325 AND
3326 Mod ( To_Number(Trunc(SYSDATE) - Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE)) - To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD'))
3327 ,To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
3328 ) = 0
3329 )
3330 OR APS.BUS_CLASS_LAST_CERTIFIED_DATE IS NULL;
3331
3332 CURSOR l_supplier_users_cur IS
3333 SELECT fu.user_name, fu.email_address
3334 FROM hz_relationships hzr, hz_parties hp, fnd_user fu, ap_suppliers ap, hz_party_usg_assignments hpua
3335 WHERE
3336 fu.user_id in (select spm.user_id from pos_spmntf_subscription spm
3337 where spm.event_type = 'SUPP_BUS_CLASS_RECERT_NTF')
3338 AND fu.person_party_id = hp.party_id
3339 AND fu.email_address IS NOT NULL
3340 AND fu.end_date IS NULL
3341 AND ap.vendor_id = l_vendor_id
3342 AND hzr.object_id = ap.party_id
3343 AND hzr.subject_type = 'PERSON'
3344 AND hzr.object_type = 'ORGANIZATION'
3345 AND hzr.relationship_type = 'CONTACT'
3346 AND hzr.relationship_code = 'CONTACT_OF'
3347 AND hzr.status = 'A'
3348 AND (hzr.start_date IS NULL OR hzr.start_date <= Sysdate)
3349 AND (hzr.end_date IS NULL OR hzr.end_date >= Sysdate)
3350 AND hzr.subject_id = hp.party_id
3351 AND hpua.party_id = hp.party_id
3352 AND hpua.status_flag = 'A'
3353 AND hpua.party_usage_code = 'SUPPLIER_CONTACT'
3354 AND (hpua.effective_end_date IS NULL OR hpua.effective_end_date > Sysdate);
3355
3356 CURSOR l_purge_wf_items_cur(l_purge_item_key_type VARCHAR2) IS
3357 SELECT item_key
3358 FROM wf_items
3359 WHERE item_key LIKE l_purge_item_key_type;
3360
3361 BEGIN
3362 l_supplier_count := 0;
3363
3364 SELECT COUNT(APS.VENDOR_ID)
3365 into l_supplier_count
3366 FROM AP_SUPPLIERS APS
3367 WHERE
3368 Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE) + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD')) = Trunc(SYSDATE) + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
3369 OR
3370 ( Trunc(SYSDATE) > Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD')))
3371 AND
3372 Mod ( To_Number(Trunc(SYSDATE) - Trunc(APS.BUS_CLASS_LAST_CERTIFIED_DATE)) - To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD'))
3373 ,To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
3374 ) = 0
3375 )
3376 OR APS.BUS_CLASS_LAST_CERTIFIED_DATE IS NULL;
3377
3378 IF l_supplier_count > 0 THEN
3379
3380 OPEN l_vendor_id_cur;
3381
3382 LOOP
3383
3384 FETCH l_vendor_id_cur INTO l_vendor_id;
3385 EXIT WHEN l_vendor_id_cur%NOTFOUND;
3386
3387 l_process := 'POS_BC_RECERT_REMIND_NOTIFY';
3388
3389 get_wf_item_type (l_itemtype);
3390
3391 l_purge_item_key_type := l_itemtype||'_'||l_process||'_%_VENDOR_ID_'||to_char(l_vendor_id);
3392
3393 OPEN l_purge_wf_items_cur(l_purge_item_key_type);
3394 LOOP
3395
3396 FETCH l_purge_wf_items_cur INTO l_purge_itemkey;
3397 EXIT WHEN l_purge_wf_items_cur%NOTFOUND;
3398
3399 WF_PURGE.ITEMS (itemtype => l_itemtype,
3400 itemkey => l_purge_itemkey,
3401 enddate => SYSDATE,
3402 docommit => true,
3403 force => TRUE
3404 );
3405 END LOOP;
3406 CLOSE l_purge_wf_items_cur;
3407
3408 ul := 0;
3409
3410 OPEN l_supplier_users_cur;
3411
3412 LOOP
3413
3414 FETCH l_supplier_users_cur INTO l_user_name, l_email_address;
3415 EXIT WHEN l_supplier_users_cur%NOTFOUND;
3416
3417 l_users(ul) := l_user_name;
3418
3419 ul := ul + 1;
3420
3421 END LOOP;
3422
3423 CLOSE l_supplier_users_cur;
3424
3425 IF ul > 0 THEN
3426
3427 l_itemkey := l_itemtype||'_'||l_process||'_'||to_char(sysdate, 'JSSSSS')||'_'||'VENDOR_ID'||'_'||to_char(l_vendor_id);
3428
3429 wf_engine.CreateProcess(itemtype => l_itemtype,
3430 itemkey => l_itemkey,
3431 process => l_process
3432 );
3433
3434 get_enterprise_name(l_enterprise_name);
3435
3436 wf_engine.SetItemAttrText(itemtype => l_itemtype,
3437 itemkey => l_itemkey,
3438 aname => 'ENTERPRISE_NAME',
3439 avalue => l_enterprise_name
3440 );
3441
3442 wf_engine.SetItemAttrNumber (itemtype => l_itemtype,
3443 itemkey => l_itemkey,
3444 aname => 'VENDOR_ID',
3445 avalue => l_vendor_id
3446 );
3447
3448 SELECT bus_class_last_certified_date
3449 INTO l_last_certification_date
3450 FROM ap_suppliers
3451 WHERE vendor_id = l_vendor_id;
3452
3453 IF l_last_certification_date IS NOT NULL then
3454 l_due_date := l_last_certification_date + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_PERIOD'));
3455 ELSE
3456 l_due_date := SYSDATE + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'));
3457 END IF;
3458
3459 wf_engine.SetItemAttrText(itemtype => l_itemtype,
3460 itemkey => l_itemkey,
3461 aname => 'BC_RECERT_DUE_DATE',
3462 avalue => l_due_date
3463 );
3464
3465 l_role_name := l_process||' '||To_Char(l_vendor_id)||' '||to_char(sysdate, 'JSSSSS')||To_Char(fnd_crypto.smallrandomnumber);
3466
3467 select vendor_name
3468 into l_role_display_name
3469 from ap_suppliers
3470 where vendor_id = l_vendor_id;
3471
3472 select SYSDATE + To_Number(FND_PROFILE.Value('POS_BUS_CLASS_RECERT_REMIND_DAYS'))
3473 into l_expiration_date
3474 from ap_suppliers
3475 where vendor_id = l_vendor_id;
3476
3477
3478 WF_DIRECTORY.CreateAdHocRole2(role_name => l_role_name,
3479 role_display_name => l_role_display_name,
3480 notification_preference => 'MAILHTML',
3481 role_users => l_users,
3482 expiration_date => l_expiration_date
3483 );
3484
3485 wf_engine.SetItemAttrText( itemtype => l_itemtype,
3486 itemkey => l_itemkey,
3487 aname => 'BUS_CLASS_RECERT_RECEIVERS',
3488 avalue => l_role_name
3489 );
3490
3491 wf_engine.StartProcess(itemtype => l_itemtype,
3492 itemkey => l_itemkey
3493 );
3494
3495 END IF;
3496
3497 END LOOP;
3498
3499 CLOSE l_vendor_id_cur;
3500
3501 END IF;
3502 EXCEPTION
3503
3504 WHEN OTHERS THEN
3505
3506 wf_core.context(g_package_name,'bc_recert_workflow',l_itemtype,l_itemkey);
3507
3508 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3509 FND_LOG.string(fnd_log.level_error, 'pos_spm_wf_pkg1' , 'Call to the workflow process for sending reminder notifications for Business Classification Re-Certification failed.');
3510 END IF;
3511
3512 END bc_recert_workflow;
3513
3514 -- END OF CODE ADDED FOR BUSINESS CLASSIFICATION RE-CERTIFICATION ER
3515
3516
3517 END pos_spm_wf_pkg1;