1 PACKAGE BODY qa_ss_import_wf AS
2 /* $Header: qltsswfb.plb 120.1.12010000.2 2008/10/30 09:57:24 ntungare ship $ */
3
4
5 --
6 --This is a complete package that is used for the workflow notifications
7 --sent from Self Service. This is used for both Self Service Transaction
8 --Notification (which is launched at the time of Collection Import when
9 --rows entered through self service error out)and Buyer Notification
10 --(which is launched when the user invokes the "Send Notification"
11 --button from the Self Service Enter Quality Results Page.
12 --
13 --The following procedures will be used for the QA Self Service Transaction
14 --Notification Workflow
15 -- Procedure unblock
16 -- Procedure Check_Completion
17 -- Procedure Dispatch_Notification.
18 -- Procedure Set_Message_Attr
19 --
20 --The following procedures will be used for the QA Self Service Buyer
21 --Notification Workflow
22 -- Procedure Start_Buyer_Notification
23 -- Procedure Send
24 -- Function Get_Buyer_Name
25 -- Function Get_User_Name
26 -- Function Get_Plan_Name
27 -- Function Get_Item
28 -- Procedure Set_Supplier_Info
29 -- Function Get_PO_Number
30 --
31 --Author: Revathy Narasimhan (rnarasim)
32 --Created on: 99/07/23
33 --
34
35 --
36 --These global variables are set for the transaction notification.
37 --They are set only once and are accessed later
38 --
39
40 x_plan_name VARCHAR2(30);
41 x_user_name VARCHAR2(100);
42 x_organization_code VARCHAR2(3);
43 x_error_rows NUMBER;
44 x_creation_date DATE;
45 x_last_updated_name VARCHAR2(80);
46 x_buyer_name VARCHAR2(80);
47
48 --
49 --The Transaction Notification Process is started before the Collection
50 --Manager spawns the workers.
51 --Package: QLTTRAMB; Function: launch_workflow;
52 --
53 --After the workflow is launched, the process immediately goes
54 --to a block state.
55 --As and when each worker is completed this procedure is called.
56 --This unblocks the BLOCK activity. Once unblocked, the process moves to
57 --the activity CHECK_COMPLETION.
58 --Called from package: QLTTRAWB; Procedure WRAPPER; File: qlttrawb.plb
59 --
60
61 PROCEDURE unblock (item_type IN VARCHAR2, key IN NUMBER) IS
62 x_num_workers NUMBER;
63 BEGIN
64 x_num_workers := wf_engine.getitemattrnumber(
65 itemtype => item_type,
66 itemkey => key,
67 aname => 'NUM_WORKERS');
68 x_num_workers := x_num_workers - 1;
69 wf_engine.setitemattrnumber(
70 itemtype => item_type,
71 itemkey => key,
72 aname => 'NUM_WORKERS',
73 avalue => x_num_workers);
74 wf_engine.completeactivity(item_type, key, 'BLOCK', NULL);
75 RETURN;
76 END unblock;
77
78 --
79 --This Procedure will be called to check and see if all workers have finished
80 --Item attribute Num_Workers has the total number of workers launched.
81 --The Collection manager sets this item attribute value when it launches
82 --the workflow process.
83 --The process will go back to the UNBLOCK state from CHECK_COMPLETION stage
84 --till Num_Workers = 0(which implies that all workers have finished). It then
85 --proceeds to the Dispatch Notification activity.
86 --
87
88 PROCEDURE check_completion (itemtype IN VARCHAR2,
89 itemkey IN VARCHAR2,
90 actid IN NUMBER,
91 funcmode IN VARCHAR2,
92 result OUT NOCOPY VARCHAR2) IS
93 x_num_workers NUMBER;
94 x_comp_workers NUMBER;
95 BEGIN
96 IF (funcmode = 'RUN') THEN
97 x_num_workers := wf_engine.getitemattrnumber(
98 itemtype => itemtype,
99 itemkey => itemkey,
100 aname => 'NUM_WORKERS');
101
102 IF x_num_workers = 0 THEN
103 result := 'COMPLETE:Y';
104 RETURN;
105 ELSE
106 result := 'COMPLETE:N';
107 RETURN;
108 END IF;
109 END IF;
110 END check_completion;
111
112
113 --
114 --Dispatch_Notification uses a cursor to find the right set of records.
115 --For every row of the record, a notification will be sent,to the user
116 --and the Supervisor.
117 --If the buyer exists then a notification is sent to the buyer as well.
118 --
119
120 PROCEDURE dispatch_notification (itemtype IN VARCHAR2,
121 itemkey IN VARCHAR2,
122 actid IN NUMBER,
123 funcmode IN VARCHAR2,
124 result OUT NOCOPY VARCHAR2) IS
125 x_request_id NUMBER;
126 x_nid_user NUMBER;
127 x_nid_supervisor NUMBER;
128 x_nid_buyer NUMBER;
129
130 --
131 --This cursor gets the buyer name from po_agent_id in fnd_user
132 --This cursor also gets the last_update_login from fnd_user
133 --This also gets the name for source_line_id.
134 --It fetches only the error rows from QRI.
135 --
136
137 cursor qri(x_request_id NUMBER) IS
138 SELECT
139 qr.plan_name, fu1.user_name, qr.organization_code,
140 count(*), qr.creation_date,
141 fu2.user_name, fu3.user_name
142 FROM
143 qa_results_interface qr, fnd_user fu1,
144 fnd_user fu2, fnd_user fu3
145 WHERE
146 qr.source_line_id IS NOT NULL AND
147 qr.process_status = 3 AND
148 qr.request_id = x_request_id AND
149 qr.source_line_id = fu1.user_id AND
150 qr.last_update_login = fu2.user_id AND
151 qr.po_agent_id = fu3.employee_id (+)
152 GROUP BY
153 qr.plan_name, fu1.user_name,
154 qr.organization_code ,qr.creation_date,
155 fu2.user_name, fu3.user_name;
156
157 BEGIN
158 IF (funcmode = 'RUN') THEN
159 x_request_id := wf_engine.getitemattrnumber(
160 itemtype => 'QASSIMP',
161 itemkey => itemkey,
162 aname => 'REQUEST_ID');
163
164 open qri(x_request_id);
165 fetch qri INTO x_plan_name,
166 x_user_name,
167 x_organization_code,
168 x_error_rows,
169 x_creation_date,
170 x_last_updated_name,
171 x_buyer_name;
172
173 WHILE qri%found LOOP
174
175 x_nid_user := wf_notification.send
176 (x_user_name,
177 itemtype ,
178 'FAILURE_DETECTED_TO_USER');
179 set_message_attr(x_nid_user);
180 x_nid_supervisor := wf_notification.send
181 (x_last_updated_name,
182 itemtype,
183 'FAILURE_DETECTED_TO_SUPERVISOR');
184 set_message_attr(x_nid_supervisor);
185
186 IF x_buyer_name IS NOT NULL THEN
187 x_nid_buyer := wf_notification.send
188 (x_buyer_name,
189 itemtype,
190 'FAILURE_DETECTED_TO_BUYER');
191 set_message_attr(x_nid_buyer);
192 END IF;
193
194 fetch qri INTO x_plan_name,
195 x_user_name,
196 x_organization_code,
197 x_error_rows,
198 x_creation_date,
199 x_last_updated_name,
200 x_buyer_name;
201
202 END LOOP;
203 close qri;
204 result := 'COMPLETE:NO_MORE_ROWS';
205 END IF; -- funcmode = 'Run'
206
207 END dispatch_notification;
208
209 --
210 --This procedure is called for a particular import notification,
211 --to set all the message attributes.
212 --
213
214 PROCEDURE set_message_attr(id IN NUMBER) IS
215 BEGIN
216 wf_notification.setattrdate(id, 'CREATION_DATE', x_creation_date);
217 wf_notification.setattrtext(id, 'PLAN_NAME', x_plan_name);
218 wf_notification.setattrnumber(id, 'ERROR_ROWS', x_error_rows);
219 wf_notification.setattrtext(id, 'USER_NAME', x_user_name);
220 wf_notification.setattrtext(id, 'BUYER_NAME', x_buyer_name);
221 wf_notification.setattrtext(id, 'ORGANIZATION_CODE', x_organization_code);
222 wf_notification.setattrtext(id, 'LAST_UPDATE_LOGIN', x_last_updated_name);
223 END set_message_attr;
224
225
226 --
227 --This is used for starting Buyer notifications. This procedure starts
228 --the process and sets the attributes that are required for sending the
229 --notification. Called from package: QA_SS_CORE; Procedure Enter_Results
230 --File: qltsscob.plb
231 --
232
233 PROCEDURE start_buyer_notification
234 (x_buyer_id IN NUMBER DEFAULT NULL,
235 x_source_id IN NUMBER DEFAULT NULL,
236 x_plan_id IN NUMBER DEFAULT NULL ,
237 x_item_id IN NUMBER DEFAULT NULL,
238 x_po_header_id IN NUMBER DEFAULT NULL) IS
239
240 x_itemkey NUMBER;
241 x_buyer_name VARCHAR2(60) := 'Buyer Information Not Available';
242 x_plan_name VARCHAR2(60):= 'Plan Name Not Available';
243 x_user_name VARCHAR2(60) := 'User Name Not Available';
244 x_item VARCHAR2(60) := 'Item Information Not Available';
245 x_po_number VARCHAR2(30) := 'PO Number not available';
246 x_itemtype VARCHAR2(8);
247 x_org_id NUMBER;
248 x_org_code VARCHAR2(3);
249 x_org_name hr_all_organization_units.name%type;
250
251 BEGIN
252
253
254
255 --Get the name of the itemtype to use, depending on the
256 --profile option.
257 --
258
259 x_itemtype := get_itemtype_profile;
260
261 --
262 --Create Buyer Notification Process.
263 --Return the itemkey.
264 --
265
266 x_itemkey := create_buyer_process(x_itemtype);
267
268 --
269 --
270 --Now get the various attributes and set them. Then start
271 --the process.
272 --Get Buyer name and set it.
273 --
274
275 IF x_buyer_id IS NOT NULL THEN
276 x_buyer_name := get_buyer_name(x_buyer_id);
277 END IF;
278 wf_engine.setitemattrtext(
279 itemtype => x_itemtype,
280 itemkey => x_itemkey,
281 aname => 'BUYER_NAME',
282 avalue => x_buyer_name);
283
284 --
285 --Get User Name, set it and also set the supplier information.
286 --
287
288 IF x_source_id IS NOT NULL THEN
289 x_user_name := get_user_name(x_source_id);
290 set_supplier_info(x_source_id, x_itemkey, x_itemtype);
291 END IF;
292 wf_engine.setitemattrtext(
293 itemtype => x_itemtype,
294 itemkey => x_itemkey,
295 aname => 'USER_NAME',
296 avalue => x_user_name);
297
298 --
299 --Get Plan Name and set it.
300 --
301
302 IF x_plan_id IS NOT NULL THEN
303 x_plan_name := get_plan_name(x_plan_id);
304 END IF;
305 wf_engine.setitemattrtext(
306 itemtype => x_itemtype,
307 itemkey => x_itemkey,
308 aname => 'PLAN_NAME',
309 avalue => x_plan_name);
310
311 --
312 --Get OrgId and set it.
313 --
314
315 x_org_id := get_org_id(x_plan_id);
316 wf_engine.setitemattrnumber(
317 itemtype => x_itemtype,
318 itemkey => x_itemkey,
319 aname => 'ORGANIZATION_ID',
320 avalue => x_org_id);
321
322
323
324 -- The two get and sets below are added to resolve the bug 1052466
325 --
326 -- orashid
327
328
329 --
330 --Get OrgCode and set it.
331 --
332
333 x_org_code := get_org_code(x_org_id);
334
335 wf_engine.setitemattrtext(
336 itemtype => x_itemtype,
337 itemkey => x_itemkey,
338 aname => 'ORGANIZATION_CODE',
339 avalue => x_org_code);
340
341 --
342 --Get OrgName and set it.
343 --
344
345 x_org_name := get_org_name(x_org_id);
346 wf_engine.setitemattrtext(
347 itemtype => x_itemtype,
348 itemkey => x_itemkey,
349 aname => 'ORGANIZATION_NAME',
350 avalue => x_org_name);
351
352 --
353 --Get Item and set it.
354 --
355
356 IF x_item_id IS NOT NULL THEN
357 x_item := get_item(x_item_id);
358 END IF;
359 wf_engine.setitemattrtext(
360 itemtype => x_itemtype,
361 itemkey => x_itemkey,
362 aname => 'ITEM',
363 avalue => x_item);
364
365 --
366 --Get PO Number and set it.
367 --
368
369 IF x_po_header_id IS NOT NULL THEN
370 x_po_number := get_po_number(x_po_header_id);
371 END IF;
372 wf_engine.setitemattrtext(
373 itemtype => x_itemtype,
374 itemkey => x_itemkey,
375 aname => 'PO_NUMBER',
376 avalue => x_po_number);
377
378 --
379 --Start WF Process
380 --
381
382 wf_engine.startprocess(
383 itemtype => x_itemtype,
384 itemkey => x_itemkey);
385 END start_buyer_notification;
386
387
388 --
389 --Used for sending the notification. We need to fetch the item level
390 --attributes and set each of them, for the message level attributes.
391 --If Workflow comes up with better mechanism, where you donot need to
392 --set the message level attributes, please rewrite this procedure : you would
393 --not need these gets and sets !
394 --Revathy
395 --
396
397 PROCEDURE send (itemtype IN VARCHAR2,
398 itemkey IN VARCHAR2,
399 actid IN NUMBER,
400 funcmode IN VARCHAR2,
401 result OUT NOCOPY VARCHAR2) IS
402 x_nid NUMBER;
403 x_buyer_name VARCHAR2(30);
404 x_plan_name VARCHAR2(30);
405 x_user_name VARCHAR2(30);
406 x_item VARCHAR2(60);
407 x_supplier_name VARCHAR2(240);
408 x_contact_name VARCHAR2(30);
409 x_contact_phone VARCHAR2(30);
410 x_po_number VARCHAR2(30);
411 x_rsinf VARCHAR2(80);
412 x_cimdf VARCHAR2(200);
413 x_cursor_plan_name VARCHAR2(60);
414 x_org_id NUMBER;
415 x_org_code VARCHAR2(3);
416 x_org_name hr_all_organization_units.name%type;
417
418
419 BEGIN
420 IF (funcmode='RUN') THEN
421
422 x_buyer_name := wf_engine.getitemattrtext(
423 itemtype => itemtype,
424 itemkey => itemkey,
425 aname => 'BUYER_NAME');
426 x_plan_name := wf_engine.getitemattrtext(
427 itemtype => itemtype,
428 itemkey => itemkey,
429 aname => 'PLAN_NAME');
430 x_user_name := wf_engine.getitemattrtext(
431 itemtype => itemtype,
432 itemkey => itemkey,
433 aname => 'USER_NAME');
434 x_item := wf_engine.getitemattrtext(
435 itemtype => itemtype,
436 itemkey => itemkey,
437 aname => 'ITEM');
438 x_supplier_name := wf_engine.getitemattrtext(
439 itemtype => itemtype,
440 itemkey => itemkey,
441 aname => 'SUPPLIER_NAME');
442 x_contact_name := wf_engine.getitemattrtext(
443 itemtype => itemtype,
444 itemkey => itemkey,
445 aname => 'CONTACT_NAME');
446 x_contact_phone := wf_engine.getitemattrtext(
447 itemtype => itemtype,
448 itemkey => itemkey,
449 aname => 'CONTACT_PHONE');
450 x_po_number := wf_engine.getitemattrtext(
454
451 itemtype => itemtype,
452 itemkey => itemkey,
453 aname => 'PO_NUMBER');
455 x_org_id := wf_engine.getitemattrnumber(
456 itemtype => itemtype,
457 itemkey => itemkey,
458 aname => 'ORGANIZATION_ID');
459
460 -- The next two gets are added to resolve the bug 1052466
461 --
462 -- orashid
463
464 x_org_code := wf_engine.getitemattrtext(
465 itemtype => itemtype,
466 itemkey => itemkey,
467 aname => 'ORGANIZATION_CODE');
468
469 x_org_name := wf_engine.getitemattrtext(
470 itemtype => itemtype,
471 itemkey => itemkey,
472 aname => 'ORGANIZATION_NAME');
473
474
475 --
476 --Formulate the command for launching forms.
477 --
478
479 x_cimdf := 'QACIMDF:ORG_ID="'|| '&' || 'ORGANIZATION_ID"'||
480 ' SELF_SERVICE="YES"' ||
481 ' SELF_SERVICE_PLAN_NAME="' || '&' || 'PLAN_NAME"';
482
483 x_rsinf := 'QARSINF:ORG_ID="'|| '&' || 'ORGANIZATION_ID"'||
484 ' SELF_SERVICE_PLAN_NAME="' || '&' || 'PLAN_NAME"';
485
486 --
487 --Its not enough if the item level attributes are set.
488 --We also have to set the message attributes, for each message
489 --sent using wf_notification.send
490 --
491
492 x_nid := wf_notification.send(x_buyer_name, itemtype, 'RESULTS_SAVED_TO_BUYER');
493 wf_notification.setattrtext(x_nid, 'BUYER_NAME', x_buyer_name);
494 wf_notification.setattrtext(x_nid, 'PLAN_NAME', x_plan_name);
495 wf_notification.setattrtext(x_nid, 'USER_NAME', x_user_name);
496 wf_notification.setattrtext(x_nid, 'ITEM', x_item);
497 wf_notification.setattrtext(x_nid, 'SUPPLIER_NAME', x_supplier_name);
498 wf_notification.setattrtext(x_nid, 'CONTACT_NAME', x_contact_name);
499 wf_notification.setattrtext(x_nid, 'CONTACT_PHONE', x_contact_phone);
500 wf_notification.setattrtext(x_nid, 'PO_NUMBER', x_po_number);
501 wf_notification.setattrnumber(x_nid, 'ORGANIZATION_ID', x_org_id);
502 wf_notification.setattrtext(x_nid, 'ORGANIZATION_CODE', x_org_code);
503 wf_notification.setattrtext(x_nid, 'ORGANIZATION_NAME', x_org_name);
504 wf_notification.setattrtext(x_nid, 'OPEN_CIMDF_COMMAND', x_cimdf);
505 wf_notification.setattrtext(x_nid, 'OPEN_RSINF_COMMAND', x_rsinf);
506
507 END IF;
508
509 END send;
510
511 --
512 --This function starts the Buyer Notification Process
513 --
514
515 FUNCTION create_buyer_process(x_type IN VARCHAR2) RETURN NUMBER IS
516 x_itemkey NUMBER;
517 cursor c IS
518 SELECT qa_ss_notify_workflow_s.nextval FROM dual;
519 BEGIN
520 open c;
521 fetch c INTO x_itemkey;
522 close c;
523 wf_engine.createprocess(
524 itemtype => x_type,
525 itemkey => x_itemkey,
526 process => 'BUYER_NOTIFICATION');
527 RETURN x_itemkey;
528 END create_buyer_process;
529
530
531 --
532 --This function returns the itemtype as defined in the profile option.
533 --
534
535 FUNCTION get_itemtype_profile RETURN VARCHAR2 IS
536 x_profile_val NUMBER;
537 x_type VARCHAR2(20);
538 BEGIN
539 x_profile_val := fnd_profile.VALUE('QA_SS_NOTIFY_WORKFLOW');
540 IF x_profile_val = 1 THEN
541 x_type := 'QASSNOT';
542 ELSE
543 x_type := 'QASSUNOT';
544 END IF;
545 RETURN x_type;
546 END get_itemtype_profile;
547
548 --
549 --This function returns the Buyer name for given Buyer.
550 --Uses the directory service provided by Workflow to get the name
551 --For more information, look at Worflow APIs.
552
553 FUNCTION get_buyer_name(s_id IN NUMBER) RETURN VARCHAR2 IS
554 b_name VARCHAR2(30);
555 x_disp_name VARCHAR2(60);
556 BEGIN
557 wf_directory.getusername('PER', s_id, b_name, x_disp_name);
558 RETURN b_name;
559 END get_buyer_name;
560
561 --
562 --Function to retrieve user name.
563 --
564
565 FUNCTION get_user_name(u_id IN NUMBER) RETURN VARCHAR2 IS
566 u_name VARCHAR2(30);
567 cursor c IS
568 SELECT DISTINCT fu.user_name FROM
569 fnd_user fu
570 WHERE
571 fu.user_id = u_id;
572 BEGIN
573
574 open c;
575 fetch c INTO u_name;
576 IF c%notfound THEN
577 close c;
578 RETURN NULL;
579 ELSE
580 close c;
581 RETURN u_name;
582 END IF;
583 END get_user_name;
584
585 --
586 --This function returns the plan name.
587 --
588
589 FUNCTION get_plan_name(p_id IN NUMBER) RETURN VARCHAR2 IS
590 p_name VARCHAR2(30);
591 cursor c IS
592 SELECT name FROM
593 qa_plans qp
594 WHERE
595 qp.plan_id = p_id;
596 BEGIN
597 open c;
598 fetch c INTO p_name;
599 IF c%notfound THEN
600 close c;
601 RETURN NULL;
602 ELSE
603 close c;
604 RETURN p_name;
605 END IF;
606 END get_plan_name;
607
608 --
612
609 --This function finds the Org Id given the plan name
610 --
611
613 FUNCTION get_org_id(p_id IN NUMBER) RETURN NUMBER IS
614 x_id NUMBER;
615 x_cursor_plan_id NUMBER;
616 cursor c(x_cursor_plan_id NUMBER) IS
617 SELECT organization_id FROM qa_plans
618 WHERE plan_id = x_cursor_plan_id;
619 BEGIN
620 open c(p_id);
621 fetch c INTO x_id;
622 close c;
623 RETURN x_id;
624 END get_org_id;
625
626
627 -- The two functions below were added to resolve the bug # 1052466
628 --
629 -- orashid
630
631
632 --
633 --This function finds the Org Code given the org id
634 --
635
636
637 FUNCTION get_org_code (org_id IN NUMBER) RETURN VARCHAR2 IS
638
639 x_org_code varchar2(3);
640 x_cursor_org_id NUMBER;
641
642 -- Bug 4958774. SQL Repository Fix SQL ID: 15008535
643 cursor c(x_cursor_org_id NUMBER) IS
644 SELECT organization_code
645 FROM mtl_parameters -- org_organization_definitions
646 WHERE organization_id = x_cursor_org_id;
647
648 BEGIN
649
650 open c(org_id);
651 fetch c INTO x_org_code;
652 close c;
653 RETURN x_org_code;
654
655 END get_org_code;
656
657
658 --
659 --This function finds the Org Name given the org id
660 --
661
662 FUNCTION get_org_name (org_id IN NUMBER) RETURN VARCHAR2 IS
663
664 x_org_name hr_all_organization_units.name%type;
665 x_cursor_org_id NUMBER;
666
667 -- Bug 4958774. SQL Repository Fix SQL ID: 15008551
668 cursor c(x_cursor_org_id NUMBER) IS
669 SELECT organization_name
670 FROM inv_organization_name_v -- org_organization_definitions
671 WHERE organization_id = x_cursor_org_id;
672
673 BEGIN
674
675 open c(org_id);
676 fetch c INTO x_org_name;
677 close c;
678 RETURN x_org_name;
679
680 END get_org_name;
681
682
683
684
685
686 --
687 --Function to retrieve item for an item id
688 --
689
690 FUNCTION get_item (i_id IN NUMBER) RETURN VARCHAR2 IS
691 i_name VARCHAR2(30);
692 cursor c IS
693 SELECT DISTINCT msiv.concatenated_segments FROM
694 mtl_system_items_kfv msiv
695 WHERE msiv.inventory_item_id = i_id;
696 BEGIN
697 open c;
698 fetch c INTO i_name;
699 IF c%notfound THEN
700 close c;
701 RETURN NULL;
702 ELSE
703 close c;
704 RETURN i_name;
705 END IF;
706 END get_item;
707
708 --
709 --Function to retrieve po_number for a po_header_id
710 --
711
712 FUNCTION get_po_number (p_id IN NUMBER) RETURN VARCHAR2 IS
713 po_number VARCHAR2(20);
714 cursor c IS
715 SELECT DISTINCT po.segment1 FROM
716 po_headers po
717 WHERE po.po_header_id = p_id;
718 BEGIN
719 open c;
720 fetch c INTO po_number;
721 IF c%notfound THEN
722 close c;
723 RETURN NULL;
724 ELSE
725 close c;
726 RETURN po_number;
727 END IF;
728 END get_po_number;
729
730 --
731 --Sets Supplier Information.
732 --
733
734 PROCEDURE set_supplier_info(s_id IN NUMBER,
735 x_itemkey IN NUMBER, x_itemtype IN VARCHAR2) IS
736 x_supplier_name VARCHAR2(240);
737 x_contact_name VARCHAR2(300);
738 x_contact_phone VARCHAR2(60);
739 --
740 -- bug 7383622
741 -- Modified the cursor definiton to make use of the
742 -- supplier id instaed of the user_id for performance
743 -- ntungare
744 --
745 cursor c (p_supplier_id IN NUMBER) IS
746 SELECT pv.vendor_name,
747 pvc.first_name || ' ' || pvc.last_name contact_name,
748 pvc.area_code || ' ' || pvc.phone contact_phone
749 FROM po_vendors pv,
750 po_vendor_sites_all pvs,
751 po_vendor_contacts pvc
752 --, fnd_user fu
753 WHERE pvc.vendor_site_id = pvs.vendor_site_id AND
754 pvs.vendor_id = pv.vendor_id AND
755 --pvc.vendor_contact_id = fu.supplier_id AND
756 --fu.user_id = s_id
757 pvc.vendor_contact_id = p_supplier_id
758 ORDER BY pvc.phone; -- list the ones that have telephone first.
759 --
760 -- bug 7383622
761 -- Added a new variable to capture the supplier id
762 -- ntungare
763 --
764 l_supplier_id NUMBER;
765
766 CURSOR get_supplier_id IS
767 SELECT supplier_id from fnd_user where user_id = s_id;
768 BEGIN
769 --
770 -- bug 7383622
771 -- get the supplier id from the user id
772 -- ntungare
773 --
774 open get_supplier_id;
775 fetch get_supplier_id into l_supplier_id;
776 close get_supplier_id;
777
778 open c(l_supplier_id);
779 fetch c INTO x_supplier_name, x_contact_name, x_contact_phone;
780 IF c%found THEN
781 wf_engine.setitemattrtext(
782 itemtype => x_itemtype,
783 itemkey => x_itemkey,
784 aname => 'SUPPLIER_NAME',
785 avalue => x_supplier_name);
786 wf_engine.setitemattrtext(
787 itemtype => x_itemtype,
788 itemkey => x_itemkey,
789 aname => 'CONTACT_NAME',
790 avalue => x_contact_name);
791 wf_engine.setitemattrtext(
792 itemtype => x_itemtype,
793 itemkey => x_itemkey,
794 aname => 'CONTACT_PHONE',
795 avalue => x_contact_phone);
796 close c;
797 RETURN;
798 END IF;
799 close c;
800 RETURN;
801 END set_supplier_info;
802
803 END qa_ss_import_wf;
804
[Home] [Help]