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