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