[Home] [Help]
PACKAGE BODY: APPS.PON_EVAL_TEAM_UTIL_PVT
Source
1 PACKAGE BODY PON_EVAL_TEAM_UTIL_PVT AS
2 /* $Header: PONVETUB.pls 120.1 2011/12/17 02:01:56 atjen noship $ */
3
4
5 PROCEDURE send_mng_eval_teams_notif(p_auction_header_id IN NUMBER);
6
7 PROCEDURE send_eval_team_update_notif(p_auction_header_id IN NUMBER,
8 p_member_user_id IN NUMBER);
9
10
11 FUNCTION get_display_name_for_user(p_user_id IN NUMBER)
12 RETURN VARCHAR2
13 IS
14
15 l_party_id NUMBER;
16
17 BEGIN
18
19 SELECT person_party_id
20 INTO l_party_id
21 FROM fnd_user
22 WHERE user_id = p_user_id;
23
24 RETURN pon_locale_pkg.get_party_display_name(l_party_id);
25
26 END get_display_name_for_user;
27
28
29 -- Procedure to store the current setting of evaluation teams
30 PROCEDURE init_mng_eval_teams(p_auction_header_id IN NUMBER)
31 IS
32 BEGIN
33
34 DELETE FROM pon_mng_eval_team_members
35 WHERE auction_header_id = p_auction_header_id;
36
37 DELETE FROM pon_mng_eval_team_sections
38 WHERE auction_header_id = p_auction_header_id;
39
40
41 INSERT INTO pon_mng_eval_team_members
42 (
43 auction_header_id,
44 team_id,
45 user_id,
46 status_code,
47 created_by,
48 creation_date,
49 last_updated_by,
50 last_update_date,
51 last_update_login
52 )
53 SELECT auction_header_id,
54 team_id,
55 user_id,
56 'C',
57 fnd_global.user_id,
58 SYSDATE,
59 fnd_global.user_id,
60 SYSDATE,
61 fnd_global.login_id
62 FROM pon_evaluation_team_members
63 WHERE auction_header_id = p_auction_header_id;
64
65 INSERT INTO pon_mng_eval_team_sections
66 (
67 auction_header_id,
68 team_id,
69 team_name,
70 section_id,
71 status_code,
72 created_by,
73 creation_date,
74 last_updated_by,
75 last_update_date,
76 last_update_login
77 )
78 SELECT pets.auction_header_id,
79 pets.team_id,
80 pet.team_name,
81 pets.section_id,
82 'C',
83 fnd_global.user_id,
84 SYSDATE,
85 fnd_global.user_id,
86 SYSDATE,
87 fnd_global.login_id
88 FROM pon_evaluation_team_sections pets,
89 pon_evaluation_teams pet
90 WHERE pets.auction_header_id = p_auction_header_id
91 AND pet.auction_header_id = p_auction_header_id
92 AND pet.team_id = pets.team_id;
93
94 END init_mng_eval_teams;
95
96
97 -- Procedure to track changes after Manage Evaluation Teams
98 PROCEDURE process_mng_eval_teams(p_auction_header_id IN NUMBER)
99 IS
100 BEGIN
101
102 -- 1. Track sections to be excluded from evaluations
103
104 MERGE INTO pon_mng_eval_bid_sections pmebs
105 USING (SELECT pbh.bid_number,
106 pmets.section_id
107 FROM pon_mng_eval_team_members pmetm,
108 pon_mng_eval_team_sections pmets,
109 pon_bid_headers pbh,
110 fnd_user fu
111 WHERE pmetm.auction_header_id = p_auction_header_id
112 AND pmets.auction_header_id = p_auction_header_id
113 AND pmetm.team_id = pmets.team_id
114 AND (pmetm.user_id, pmets.section_id) NOT IN
115 (SELECT petm.user_id,
116 pets.section_id
117 FROM pon_evaluation_team_members petm,
118 pon_evaluation_team_sections pets
119 WHERE petm.auction_header_id = p_auction_header_id
120 AND pets.auction_header_id = p_auction_header_id
121 AND petm.team_id = pets.team_id
122 )
123 AND pbh.auction_header_id = p_auction_header_id
124 AND pbh.bid_status IN ('ACTIVE', 'DRAFT')
125 AND pbh.evaluation_flag = 'Y'
126 AND pbh.evaluator_id = fu.person_party_id
127 AND fu.user_id = pmetm.user_id
128 ) bs_deleted
129 ON (pmebs.auction_header_id = p_auction_header_id AND
130 pmebs.bid_number = bs_deleted.bid_number AND
131 pmebs.section_id = bs_deleted.section_id
132 )
133 WHEN MATCHED THEN
134 UPDATE SET status_code = 'X' WHERE status_code = 'A'
135 DELETE WHERE status_code = 'X'
136 WHEN NOT MATCHED THEN
137 INSERT (auction_header_id,
138 bid_number,
139 section_id,
140 status_code,
141 created_by,
142 creation_date,
143 last_updated_by,
144 last_update_date,
145 last_update_login
146 )
147 VALUES (p_auction_header_id,
148 bs_deleted.bid_number,
149 bs_deleted.section_id,
150 'D',
151 fnd_global.user_id,
152 SYSDATE,
153 fnd_global.user_id,
154 SYSDATE,
155 fnd_global.login_id
156 );
157
158
159 -- 2. Track new sections for the evaluations
160
161 MERGE INTO pon_mng_eval_bid_sections pmebs
162 USING (SELECT pbh.bid_number,
163 pets.section_id
164 FROM pon_evaluation_team_members petm,
165 pon_evaluation_team_sections pets,
166 pon_bid_headers pbh,
167 fnd_user fu
168 WHERE petm.auction_header_id = p_auction_header_id
169 AND pets.auction_header_id = p_auction_header_id
170 AND petm.team_id = pets.team_id
171 AND (petm.user_id, pets.section_id) NOT IN
172 (SELECT pmetm.user_id,
173 pmets.section_id
174 FROM pon_mng_eval_team_members pmetm,
175 pon_mng_eval_team_sections pmets
176 WHERE pmetm.auction_header_id = p_auction_header_id
177 AND pmets.auction_header_id = p_auction_header_id
178 AND pmetm.team_id = pmets.team_id
179 )
180 AND pbh.auction_header_id = p_auction_header_id
181 AND bid_status IN ('ACTIVE', 'DRAFT')
182 AND pbh.evaluation_flag = 'Y'
183 AND pbh.evaluator_id = fu.person_party_id
184 AND fu.user_id = petm.user_id
185 ) bs_added
186 ON (pmebs.auction_header_id = p_auction_header_id AND
187 pmebs.bid_number = bs_added.bid_number AND
188 pmebs.section_id = bs_added.section_id
189 )
190 WHEN MATCHED THEN
191 UPDATE SET status_code = 'A' WHERE status_code = 'D'
192 WHEN NOT MATCHED THEN
193 INSERT (auction_header_id,
194 bid_number,
195 section_id,
196 status_code,
197 created_by,
198 creation_date,
199 last_updated_by,
200 last_update_date,
201 last_update_login
202 )
203 VALUES (p_auction_header_id,
204 bs_added.bid_number,
205 bs_added.section_id,
206 'A',
207 fnd_global.user_id,
208 SYSDATE,
209 fnd_global.user_id,
210 SYSDATE,
211 fnd_global.login_id
212 );
213
214
215 -- 3. Track changes in team members
216
217 UPDATE pon_mng_eval_team_members
218 SET status_code = 'D'
219 WHERE auction_header_id = p_auction_header_id
220 AND (team_id, user_id) NOT IN
221 (SELECT team_id,
222 user_id
223 FROM pon_evaluation_team_members
224 WHERE auction_header_id = p_auction_header_id
225 );
226
227 INSERT INTO pon_mng_eval_team_members
228 (
229 auction_header_id,
230 team_id,
231 user_id,
232 status_code,
233 created_by,
234 creation_date,
235 last_updated_by,
236 last_update_date,
237 last_update_login
238 )
239 SELECT auction_header_id,
240 team_id,
241 user_id,
242 'A',
243 fnd_global.user_id,
244 SYSDATE,
245 fnd_global.user_id,
246 SYSDATE,
247 fnd_global.login_id
248 FROM pon_evaluation_team_members
249 WHERE auction_header_id = p_auction_header_id
250 AND (team_id, user_id) NOT IN
251 (SELECT team_id,
252 user_id
253 FROM pon_mng_eval_team_members
254 WHERE auction_header_id = p_auction_header_id
255 );
256
257
258 -- 4. Track changes in team sections assignment
259
260 UPDATE pon_mng_eval_team_sections
261 SET status_code = 'D'
262 WHERE auction_header_id = p_auction_header_id
263 AND (team_id, section_id) NOT IN
264 (SELECT team_id,
265 section_id
266 FROM pon_evaluation_team_sections
267 WHERE auction_header_id = p_auction_header_id
268 );
269
270 INSERT INTO pon_mng_eval_team_sections
271 (
272 auction_header_id,
273 team_id,
274 team_name,
275 section_id,
276 status_code,
277 created_by,
278 creation_date,
279 last_updated_by,
280 last_update_date,
281 last_update_login
282 )
283 SELECT pets.auction_header_id,
284 pets.team_id,
285 pet.team_name,
286 pets.section_id,
287 'A',
288 fnd_global.user_id,
289 SYSDATE,
290 fnd_global.user_id,
291 SYSDATE,
292 fnd_global.login_id
293 FROM pon_evaluation_team_sections pets,
294 pon_evaluation_teams pet
295 WHERE pets.auction_header_id = p_auction_header_id
296 AND pet.auction_header_id = p_auction_header_id
297 AND pet.team_id = pets.team_id
298 AND (pets.team_id, pets.section_id) NOT IN
299 (SELECT team_id,
300 section_id
301 FROM pon_mng_eval_team_sections
302 WHERE auction_header_id = p_auction_header_id
303 );
304
305
306 -- 5. Clear the evaluation scores
307
308 UPDATE pon_team_member_attr_scores
309 SET score = NULL
310 WHERE auction_header_id = p_auction_header_id
311 AND (bid_number, section_id) IN
312 (SELECT bid_number,
313 section_id
314 FROM pon_mng_eval_bid_sections
315 WHERE auction_header_id = p_auction_header_id
316 AND status_code = 'D'
317 );
318
319 UPDATE pon_bid_attribute_values
320 SET score = NULL,
321 weighted_score = NULL
322 WHERE auction_header_id = p_auction_header_id
323 AND auction_line_number = -1
324 AND (bid_number, attr_group_seq_number) IN
325 (SELECT pmebs.bid_number,
326 pas.attr_group_seq_number
327 FROM pon_mng_eval_bid_sections pmebs,
328 pon_auction_sections pas
329 WHERE pmebs.auction_header_id = p_auction_header_id
330 AND pmebs.status_code = 'D'
331 AND pas.auction_header_id = p_auction_header_id
332 AND pas.section_id = pmebs.section_id
333 );
334
335
336 -- 6. Send notifications
337
338 send_mng_eval_teams_notif(p_auction_header_id);
339
340 END process_mng_eval_teams;
341
342 -- Procedure to send notifications after Manage Evaluation Teams
343 PROCEDURE send_mng_eval_teams_notif(p_auction_header_id IN NUMBER)
344 IS
345
346 CURSOR c_notify_members
347 IS
348 SELECT user_id
349 FROM pon_neg_team_members
350 WHERE auction_header_id = p_auction_header_id
351 AND (user_id IN (SELECT pmetm.user_id
352 FROM pon_mng_eval_team_members pmetm,
353 pon_mng_eval_team_sections pmets
354 WHERE pmetm.auction_header_id = p_auction_header_id
355 AND pmets.auction_header_id = p_auction_header_id
356 AND pmets.team_id = pmetm.team_id
357 AND ((pmetm.status_code = 'A' AND
358 pmets.status_code IN ('A', 'C'))
359 OR
360 (pmetm.status_code = 'D' AND
361 pmets.status_code IN ('C', 'D'))
362 OR
363 (pmetm.status_code = 'C' AND
364 pmets.status_code IN ('A', 'D'))
365 )
366 )
367 OR
368 menu_name = 'PON_SOURCING_EDITNEG'
369 );
370
371 TYPE notify_member_tbl_type IS TABLE OF c_notify_members%ROWTYPE;
372
373 l_notify_members_tbl notify_member_tbl_type;
374
375 BEGIN
376
377 OPEN c_notify_members;
378 FETCH c_notify_members BULK COLLECT INTO l_notify_members_tbl;
379 CLOSE c_notify_members;
380
381 FOR i IN 1..l_notify_members_tbl.COUNT LOOP
382 send_eval_team_update_notif(p_auction_header_id,
383 l_notify_members_tbl(i).user_id);
384 END LOOP;
385
386 END send_mng_eval_teams_notif;
387
388 -- Procedure to notify member of evaluation team updates
389 PROCEDURE send_eval_team_update_notif(p_auction_header_id IN NUMBER,
390 p_member_user_id IN NUMBER)
391 IS
392
393 CURSOR c_auction_details
394 IS
395 SELECT pah.document_number,
396 pah.auction_title,
397 hz.party_name preparer_tp_name,
398 pad.message_suffix
399 FROM pon_auction_headers_all pah,
400 pon_auc_doctypes pad,
401 hz_parties hz
402 WHERE pah.auction_header_id = p_auction_header_id
403 AND pad.doctype_id = pah.doctype_id
404 AND hz.party_id = pah.trading_partner_id;
405
406 l_language_code VARCHAR2(3);
407 l_doc_number pon_auction_headers_all.document_number%TYPE;
408 l_auction_title pon_auction_headers_all.auction_title%TYPE;
409 l_preparer_tp_name hz_parties.party_name%TYPE;
410 l_msg_suffix pon_auc_doctypes.message_suffix%TYPE;
411 l_doc_type VARCHAR2(50);
412 l_member_user_name fnd_user.user_name%TYPE;
413 l_et_update_subject VARCHAR2(2000);
414 l_neg_summary_url VARCHAR2(2000);
415
416 l_sequence NUMBER;
417 l_itemtype VARCHAR2(8) := 'PONAUCT';
418 l_itemkey VARCHAR2(240);
419
420 BEGIN
421
422 pon_profile_util_pkg.get_wf_language(p_member_user_id, l_language_code);
423 pon_auction_pkg.set_session_language(NULL, l_language_code);
424
425
426 OPEN c_auction_details;
427 FETCH c_auction_details
428 INTO l_doc_number, l_auction_title, l_preparer_tp_name, l_msg_suffix;
429 CLOSE c_auction_details;
430
431 l_doc_type := pon_auction_pkg.getMessage('PON_AUCTION', '_' || l_msg_suffix);
432
433 SELECT user_name
434 INTO l_member_user_name
435 FROM fnd_user
436 WHERE user_id = p_member_user_id;
437
438 fnd_message.set_name('PON', 'PON_SM_ET_UPDATE_SUB');
439 fnd_message.set_token('DOC_TYPE', l_doc_type);
440 fnd_message.set_token('DOC_NUMBER', l_doc_number);
441 fnd_message.set_token('AUCTION_TITLE', l_auction_title);
442 l_et_update_subject := fnd_message.get;
443
444 l_neg_summary_url := pon_wf_utl_pkg.get_dest_page_url
445 ( p_dest_func => 'PON_NEG_SUMMARY',
446 p_notif_performer => 'BUYER'
447 );
448
449
450 SELECT pon_auction_wf_s.nextval
451 INTO l_sequence
452 FROM dual;
453
454 l_itemkey := p_auction_header_id || '-' || l_sequence;
455
456 wf_engine.CreateProcess(itemtype => l_itemtype,
457 itemkey => l_itemkey,
458 process => 'MNG_EVAL_TEAM_UPDATE');
459
460
461 wf_engine.SetItemAttrText(itemtype => l_itemtype,
462 itemkey => l_itemkey,
463 aname => 'AUCTION_ID',
464 avalue => p_auction_header_id);
465
466 wf_engine.SetItemAttrText(itemtype => l_itemtype,
467 itemkey => l_itemkey,
468 aname => 'MEMBER_USER_ID',
469 avalue => p_member_user_id);
470
471 wf_engine.SetItemAttrText(itemtype => l_itemtype,
472 itemkey => l_itemkey,
473 aname => 'DOC_TYPE',
474 avalue => l_doc_type);
475
476 wf_engine.SetItemAttrText(itemtype => l_itemtype,
477 itemkey => l_itemkey,
478 aname => 'DOC_NUMBER',
479 avalue => l_doc_number);
480
481 wf_engine.SetItemAttrText(itemtype => l_itemtype,
482 itemkey => l_itemkey,
483 aname => 'AUCTION_TITLE',
484 avalue => l_auction_title);
485
486 wf_engine.SetItemAttrText(itemtype => l_itemtype,
487 itemkey => l_itemkey,
488 aname => 'PREPARER_TP_NAME',
489 avalue => l_preparer_tp_name);
490
491 wf_engine.SetItemAttrText(itemtype => l_itemtype,
492 itemkey => l_itemkey,
493 aname => 'ORIGIN_USER_NAME',
494 avalue => fnd_global.user_name);
495
496 wf_engine.SetItemAttrText(itemtype => l_itemtype,
497 itemkey => l_itemkey,
498 aname => 'RECIPIENT_ROLE',
499 avalue => l_member_user_name);
500
501 wf_engine.SetItemAttrText(itemtype => l_itemtype,
502 itemkey => l_itemkey,
503 aname => 'MNG_EVAL_TEAM_UPDATE_SUBJECT',
504 avalue => l_et_update_subject);
505
506 wf_engine.SetItemAttrText(itemtype => l_itemtype,
507 itemkey => l_itemkey,
508 aname => 'MNG_EVAL_TEAM_UPDATE_BODY',
509 avalue => 'plsqlclob:' ||
510 'pon_eval_team_util_pvt.' ||
511 'gen_eval_team_update_body/' ||
512 l_itemtype || ':' ||
513 l_itemkey);
514
515 wf_engine.SetItemAttrText(itemtype => l_itemtype,
516 itemkey => l_itemkey,
517 aname => 'NEG_SUMMARY_URL',
518 avalue => l_neg_summary_url);
519
520
521 wf_engine.SetItemOwner(itemtype => l_itemtype,
522 itemkey => l_itemkey,
523 owner => fnd_global.user_name);
524
525 wf_engine.StartProcess(itemtype => l_itemtype,
526 itemkey => l_itemkey);
527
528
529 pon_auction_pkg.unset_session_language;
530
531 END send_eval_team_update_notif;
532
533
534 PROCEDURE gen_eval_team_update_body(p_document_id IN VARCHAR2,
535 p_display_type IN VARCHAR2,
536 x_document IN OUT NOCOPY CLOB,
537 x_document_type IN OUT NOCOPY VARCHAR2)
538 IS
539
540 CURSOR c_changed_team_members(p_auction_header_id IN NUMBER,
541 p_member_user_id IN NUMBER)
542 IS
543 SELECT pmetm.user_id,
544 pmets.team_name,
545 pas.section_name,
546 pmetm.status_code
547 FROM pon_mng_eval_team_members pmetm,
548 pon_mng_eval_team_sections pmets,
549 pon_auction_sections pas
550 WHERE pmetm.auction_header_id = p_auction_header_id
551 AND pmets.auction_header_id = p_auction_header_id
552 AND pas.auction_header_id = p_auction_header_id
553 AND pmets.team_id = pmetm.team_id
554 AND pas.section_id = pmets.section_id
555 AND ((pmetm.status_code = 'A' AND
556 pmets.status_code IN ('A', 'C'))
557 OR
558 (pmetm.status_code = 'D' AND
559 pmets.status_code IN ('C', 'D'))
560 )
561 AND pmetm.user_id = NVL(p_member_user_id, pmetm.user_id)
562 ORDER BY pmetm.status_code,
563 pmetm.user_id,
564 pmets.team_name,
565 pas.attr_group_seq_number;
566
567 CURSOR c_changed_team_sections(p_auction_header_id IN NUMBER,
568 p_member_user_id IN NUMBER)
569 IS
570 SELECT pmets.team_name,
571 pas.section_name,
572 pmets.status_code
573 FROM pon_mng_eval_team_members pmetm,
574 pon_mng_eval_team_sections pmets,
575 pon_auction_sections pas
576 WHERE pmetm.auction_header_id = p_auction_header_id
577 AND pmets.auction_header_id = p_auction_header_id
578 AND pas.auction_header_id = p_auction_header_id
579 AND pmets.team_id = pmetm.team_id
580 AND pas.section_id = pmets.section_id
581 AND pmetm.status_code = 'C'
582 AND pmets.status_code IN ('A', 'D')
583 AND pmetm.user_id = p_member_user_id
584 ORDER BY pmets.status_code,
585 pmets.team_name,
586 pas.attr_group_seq_number;
587
588 CURSOR c_changed_team_sections_all(p_auction_header_id IN NUMBER)
589 IS
590 SELECT pmets.team_name,
591 pas.section_name,
592 pmets.status_code
593 FROM pon_mng_eval_team_sections pmets,
594 pon_auction_sections pas
595 WHERE pmets.auction_header_id = p_auction_header_id
596 AND pas.auction_header_id = p_auction_header_id
597 AND pas.section_id = pmets.section_id
598 AND pmets.status_code IN ('A', 'D')
599 ORDER BY pmets.status_code,
600 pmets.team_name,
601 pas.attr_group_seq_number;
602
603 CURSOR c_notification_id(p_itemtype IN VARCHAR2,
604 p_itemkey IN VARCHAR2)
605 IS
606 SELECT notification_id
607 FROM wf_item_activity_statuses
608 WHERE item_type = p_itemtype
609 AND item_key = p_itemkey
610 AND assigned_user IS NOT NULL;
611
612 CURSOR c_has_full_access(p_auction_header_id IN NUMBER,
613 p_member_user_id IN NUMBER)
614 IS
615 SELECT 'Y'
616 FROM pon_neg_team_members
617 WHERE auction_header_id = p_auction_header_id
618 AND user_id = p_member_user_id
619 AND menu_name = 'PON_SOURCING_EDITNEG';
620
621 TYPE team_members_rec_type IS RECORD
622 ( user_id NUMBER,
623 team_name VARCHAR2(80),
624 sections VARCHAR2(4000),
625 status_code VARCHAR2(1)
626 );
627
628 TYPE team_sections_rec_type IS RECORD
629 ( team_name VARCHAR2(80),
630 sections VARCHAR2(4000),
631 status_code VARCHAR2(1)
632 );
633
634 TYPE team_members_tbl_type IS TABLE OF team_members_rec_type;
635 TYPE team_sections_tbl_type IS TABLE OF team_sections_rec_type;
636
637 l_team_members_tbl team_members_tbl_type := team_members_tbl_type();
638 l_team_sections_tbl team_sections_tbl_type := team_sections_tbl_type();
639
640 l_team_members_rec team_members_rec_type;
641 l_team_sections_rec team_sections_rec_type;
642
643 l_user_id NUMBER;
644 l_team_name pon_evaluation_teams.team_name%TYPE;
645 l_section_name pon_auction_sections.section_name%TYPE;
646 l_status_code VARCHAR2(1);
647
648 l_itemtype VARCHAR2(8);
649 l_itemkey VARCHAR2(240);
650
651 l_auction_header_id pon_auction_headers_all.auction_header_id%TYPE;
652 l_member_user_id fnd_user.user_id%TYPE;
653 l_doc_type VARCHAR2(50);
654 l_doc_number pon_auction_headers_all.document_number%TYPE;
655 l_neg_summary_url VARCHAR2(2000);
656 l_notification_id NUMBER;
657 l_has_full_access VARCHAR2(1);
658
659 l_language_code VARCHAR2(3);
660 l_msg_name VARCHAR2(30);
661 l_document VARCHAR2(32000) := fnd_global.newline;
662
663 BEGIN
664
665 l_itemtype := SUBSTR(p_document_id, 1, INSTR(p_document_id, ':') - 1);
666 l_itemkey := SUBSTR(p_document_id, INSTR(p_document_id, ':') + 1,
667 LENGTH(p_document_id));
668
669 l_auction_header_id := wf_engine.GetItemAttrText(itemtype => l_itemtype,
670 itemkey => l_itemkey,
671 aname => 'AUCTION_ID');
672
673 l_member_user_id := wf_engine.GetItemAttrText(itemtype => l_itemtype,
674 itemkey => l_itemkey,
675 aname => 'MEMBER_USER_ID');
676
677 l_doc_type := wf_engine.GetItemAttrText(itemtype => l_itemtype,
678 itemkey => l_itemkey,
679 aname => 'DOC_TYPE');
680
681 l_doc_number := wf_engine.GetItemAttrText(itemtype => l_itemtype,
682 itemkey => l_itemkey,
683 aname => 'DOC_NUMBER');
684
685 l_neg_summary_url := wf_engine.GetItemAttrText
686 ( itemtype => l_itemtype,
687 itemkey => l_itemkey,
688 aname => 'NEG_SUMMARY_URL'
689 );
690
691 OPEN c_notification_id(l_itemtype, l_itemkey);
692 FETCH c_notification_id INTO l_notification_id;
693 CLOSE c_notification_id;
694
695 IF (l_notification_id IS NOT NULL) THEN
696 l_neg_summary_url := REPLACE(l_neg_summary_url,
697 '&#NID', l_notification_id);
698 END IF;
699
700 OPEN c_has_full_access(l_auction_header_id, l_member_user_id);
701 FETCH c_has_full_access INTO l_has_full_access;
702 CLOSE c_has_full_access;
703
704
705 -- 1. Get list of changed team members and their section assigments
706
707 IF (l_has_full_access = 'Y') THEN
708 OPEN c_changed_team_members(l_auction_header_id, NULL);
709 ELSE
710 OPEN c_changed_team_members(l_auction_header_id, l_member_user_id);
711 END IF;
712
713 LOOP
714 FETCH c_changed_team_members
715 INTO l_user_id, l_team_name, l_section_name, l_status_code;
716
717 EXIT WHEN c_changed_team_members%NOTFOUND;
718
719 IF (l_team_members_rec.user_id = l_user_id AND
720 l_team_members_rec.team_name = l_team_name AND
721 l_team_members_rec.status_code = l_status_code)
722 THEN
723 l_team_members_rec.sections := l_team_members_rec.sections || ', ' ||
724 l_section_name;
725 ELSE
726 IF (l_team_members_rec.user_id IS NOT NULL) THEN
727 l_team_members_tbl.EXTEND();
728 l_team_members_tbl(l_team_members_tbl.LAST) := l_team_members_rec;
729 END IF;
730
731 l_team_members_rec.user_id := l_user_id;
732 l_team_members_rec.team_name := l_team_name;
733 l_team_members_rec.sections := l_section_name;
734 l_team_members_rec.status_code := l_status_code;
735 END IF;
736
737 END LOOP;
738
739 IF (l_team_members_rec.user_id IS NOT NULL) THEN
740 l_team_members_tbl.EXTEND();
741 l_team_members_tbl(l_team_members_tbl.LAST) := l_team_members_rec;
742 END IF;
743
744 CLOSE c_changed_team_members;
745
746
747 -- 2. Get list of changed team sections assignment
748
749 IF (l_has_full_access = 'Y') THEN
750
751 OPEN c_changed_team_sections_all(l_auction_header_id);
752
753 LOOP
754 FETCH c_changed_team_sections_all
755 INTO l_team_name, l_section_name, l_status_code;
756
757 EXIT WHEN c_changed_team_sections_all%NOTFOUND;
758
759 IF (l_team_sections_rec.team_name = l_team_name AND
760 l_team_sections_rec.status_code = l_status_code)
761 THEN
762 l_team_sections_rec.sections := l_team_sections_rec.sections || ', ' ||
763 l_section_name;
764 ELSE
765 IF (l_team_sections_rec.team_name IS NOT NULL) THEN
766 l_team_sections_tbl.EXTEND();
767 l_team_sections_tbl(l_team_sections_tbl.LAST) := l_team_sections_rec;
768 END IF;
769
770 l_team_sections_rec.team_name := l_team_name;
771 l_team_sections_rec.sections := l_section_name;
772 l_team_sections_rec.status_code := l_status_code;
773 END IF;
774
775 END LOOP;
776
777 IF (l_team_sections_rec.team_name IS NOT NULL) THEN
778 l_team_sections_tbl.EXTEND();
779 l_team_sections_tbl(l_team_sections_tbl.LAST) := l_team_sections_rec;
780 END IF;
781
782 CLOSE c_changed_team_sections_all;
783
784 ELSE
785
786 OPEN c_changed_team_sections(l_auction_header_id, l_member_user_id);
787
788 LOOP
789 FETCH c_changed_team_sections
790 INTO l_team_name, l_section_name, l_status_code;
791
792 EXIT WHEN c_changed_team_sections%NOTFOUND;
793
794 IF (l_team_sections_rec.team_name = l_team_name AND
795 l_team_sections_rec.status_code = l_status_code)
796 THEN
797 l_team_sections_rec.sections := l_team_sections_rec.sections || ', ' ||
798 l_section_name;
799 ELSE
800 IF (l_team_sections_rec.team_name IS NOT NULL) THEN
801 l_team_sections_tbl.EXTEND();
802 l_team_sections_tbl(l_team_sections_tbl.LAST) := l_team_sections_rec;
803 END IF;
804
805 l_team_sections_rec.team_name := l_team_name;
806 l_team_sections_rec.sections := l_section_name;
807 l_team_sections_rec.status_code := l_status_code;
808 END IF;
809
810 END LOOP;
811
812 IF (l_team_sections_rec.team_name IS NOT NULL) THEN
813 l_team_sections_tbl.EXTEND();
814 l_team_sections_tbl(l_team_sections_tbl.LAST) := l_team_sections_rec;
815 END IF;
816
817 CLOSE c_changed_team_sections;
818
819 END IF;
820
821
822 -- 3. Construct the message body based on the above 2 lists
823
824 pon_profile_util_pkg.get_wf_language(l_member_user_id, l_language_code);
825 pon_auction_pkg.set_session_language(NULL, l_language_code);
826
827 FOR i IN 1..l_team_members_tbl.COUNT LOOP
828
829 l_team_members_rec := l_team_members_tbl(i);
830
831 IF (l_team_members_rec.status_code = 'A') THEN
832 l_msg_name := 'PON_SM_ET_MEMBER_ADDED';
833 ELSE
834 l_msg_name := 'PON_SM_ET_MEMBER_REMOVED';
835 END IF;
836
837 IF (p_display_type = 'text/html') THEN
838 l_msg_name := l_msg_name || '_HB';
839 ELSE
840 l_msg_name := l_msg_name || '_TB';
841 END IF;
842
843 fnd_message.set_name('PON', l_msg_name);
844 fnd_message.set_token
845 ( 'TEAM_MEMBER',
846 get_display_name_for_user(l_team_members_rec.user_id)
847 );
848 fnd_message.set_token('DOC_TYPE', l_doc_type);
849 fnd_message.set_token('DOC_NUMBER', l_doc_number);
850 fnd_message.set_token('TEAM_NAME', l_team_members_rec.team_name);
851 fnd_message.set_token('SECTIONS', l_team_members_rec.sections);
852
853 IF (p_display_type = 'text/html') THEN
854 fnd_message.set_token('NEG_SUMMARY_URL', l_neg_summary_url);
855 END IF;
856
857 l_document := l_document || fnd_message.get;
858
859 END LOOP;
860
861 FOR i IN 1..l_team_sections_tbl.COUNT LOOP
862
863 l_team_sections_rec := l_team_sections_tbl(i);
864
865 IF (l_team_sections_rec.status_code = 'A') THEN
866 l_msg_name := 'PON_SM_ET_SECTION_ADDED';
867 ELSE
868 l_msg_name := 'PON_SM_ET_SECTION_REMOVED';
869 END IF;
870
871 IF (p_display_type = 'text/html') THEN
872 l_msg_name := l_msg_name || '_HB';
873 ELSE
874 l_msg_name := l_msg_name || '_TB';
875 END IF;
876
877 fnd_message.set_name('PON', l_msg_name);
878
879 fnd_message.set_token('TEAM_NAME', l_team_sections_rec.team_name);
880 fnd_message.set_token('SECTIONS', l_team_sections_rec.sections);
881 fnd_message.set_token('DOC_TYPE', l_doc_type);
882 fnd_message.set_token('DOC_NUMBER', l_doc_number);
883
884 IF (p_display_type = 'text/html') THEN
885 fnd_message.set_token('NEG_SUMMARY_URL', l_neg_summary_url);
886 END IF;
887
888 l_document := l_document || fnd_message.get;
889
890 END LOOP;
891
892 wf_notification.WriteToClob(x_document, l_document);
893
894 pon_auction_pkg.unset_session_language;
895
896 END gen_eval_team_update_body;
897
898
899 PROCEDURE send_eval_update_scorer_notif(p_bid_number IN NUMBER)
900 IS
901
902 CURSOR c_auction_details
903 IS
904 SELECT pah.document_number,
905 pah.auction_header_id,
906 pah.auction_title,
907 hz.party_name preparer_tp_name,
908 pad.message_suffix
909 FROM pon_bid_headers pbh,
910 pon_auction_headers_all pah,
911 pon_auc_doctypes pad,
912 hz_parties hz
913 WHERE pbh.bid_number = p_bid_number
914 AND pah.auction_header_id = pbh.auction_header_id
915 AND pad.doctype_id = pah.doctype_id
916 AND hz.party_id = pah.trading_partner_id;
917
918 CURSOR c_notify_scorer
919 IS
920 SELECT DISTINCT
921 fus.user_name scorer_user_name,
922 pon_locale_pkg.get_party_display_name(pbh.evaluator_id)
923 AS evaluator_name
924 FROM pon_bid_headers pbh,
925 fnd_user fue,
926 pon_evaluation_team_members petm,
927 pon_evaluation_team_sections pets,
928 pon_scoring_team_members pstm,
929 pon_scoring_team_sections psts,
930 fnd_user fus
931 WHERE pbh.bid_number = p_bid_number
932 AND pbh.evaluation_flag = 'Y'
933 AND pbh.evaluator_id = fue.person_party_id
934 AND petm.user_id = fue.user_id
935 AND petm.auction_header_id = pbh.auction_header_id
936 AND pets.auction_header_id = pbh.auction_header_id
937 AND pets.team_id = petm.team_id
938 AND fus.user_id = pstm.user_id
939 AND pstm.auction_header_id = pbh.auction_header_id
940 AND psts.auction_header_id = pbh.auction_header_id
941 AND psts.team_id = pstm.team_id
942 AND psts.section_id = pets.section_id
943 AND psts.section_id IN
944 (SELECT pas.section_id
945 FROM pon_auction_sections pas,
946 pon_auction_attributes paa
947 WHERE pas.auction_header_id = pbh.auction_header_id
948 AND paa.auction_header_id = pbh.auction_header_id
949 AND paa.attr_group_seq_number = pas.attr_group_seq_number
950 AND paa.line_number= -1
951 AND paa.internal_attr_flag = 'Y'
952 AND paa.scoring_method = 'MANUAL'
953 );
954
955 TYPE notify_tbl_type IS TABLE OF c_notify_scorer%ROWTYPE;
956
957 l_notify_tbl notify_tbl_type;
958
959 l_doc_number pon_auction_headers_all.document_number%TYPE;
960 l_auction_header_id pon_auction_headers_all.auction_header_id%TYPE;
961 l_auction_title pon_auction_headers_all.auction_title%TYPE;
962 l_preparer_tp_name hz_parties.party_name%TYPE;
963 l_msg_suffix pon_auc_doctypes.message_suffix%TYPE;
964 l_language_code VARCHAR2(3);
965 l_doc_type VARCHAR2(50);
966 l_eval_update_sub VARCHAR2(2000);
967 l_neg_summary_url VARCHAR2(2000);
968
969 l_sequence NUMBER;
970 l_itemtype VARCHAR2(8) := 'PONAUCT';
971 l_itemkey VARCHAR2(240);
972
973 BEGIN
974
975 OPEN c_notify_scorer;
976 FETCH c_notify_scorer BULK COLLECT INTO l_notify_tbl;
977 CLOSE c_notify_scorer;
978
979 IF (l_notify_tbl.COUNT = 0) THEN
980 RETURN;
981 END IF;
982
983
984 OPEN c_auction_details;
985 FETCH c_auction_details INTO l_doc_number,
986 l_auction_header_id,
987 l_auction_title,
988 l_preparer_tp_name,
989 l_msg_suffix;
990 CLOSE c_auction_details;
991
992
993 FOR i IN 1..l_notify_tbl.COUNT LOOP
994
995 pon_profile_util_pkg.get_wf_language(l_notify_tbl(i).scorer_user_name,
996 l_language_code);
997 pon_auction_pkg.set_session_language(NULL, l_language_code);
998
999
1000 l_doc_type := pon_auction_pkg.getMessage('PON_AUCTION', '_'||l_msg_suffix);
1001
1002 fnd_message.set_name('PON', 'PON_SM_EVAL_UPDATE_SCORER_SUB');
1003 fnd_message.set_token('DOC_TYPE', l_doc_type);
1004 fnd_message.set_token('DOC_NUMBER', l_doc_number);
1005 fnd_message.set_token('AUCTION_TITLE', l_auction_title);
1006 l_eval_update_sub := fnd_message.get;
1007
1008 l_neg_summary_url := pon_wf_utl_pkg.get_dest_page_url
1009 ( p_dest_func => 'PON_NEG_SUMMARY',
1010 p_notif_performer => 'BUYER'
1011 );
1012
1013
1014 SELECT pon_auction_wf_s.nextval
1015 INTO l_sequence
1016 FROM dual;
1017
1018 l_itemkey := l_auction_header_id || '-' || l_sequence;
1019
1020 wf_engine.CreateProcess(itemtype => l_itemtype,
1021 itemkey => l_itemkey,
1022 process => 'NOTIFY_SCORER_EVAL_UPDATE');
1023
1024 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1025 itemkey => l_itemkey,
1026 aname => 'AUCTION_ID',
1027 avalue => l_auction_header_id);
1028
1029 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1030 itemkey => l_itemkey,
1031 aname => 'DOC_TYPE',
1032 avalue => l_doc_type);
1033
1034 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1035 itemkey => l_itemkey,
1036 aname => 'DOC_NUMBER',
1037 avalue => l_doc_number);
1038
1039 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1040 itemkey => l_itemkey,
1041 aname => 'AUCTION_TITLE',
1042 avalue => l_auction_title);
1043
1044 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1045 itemkey => l_itemkey,
1046 aname => 'PREPARER_TP_NAME',
1047 avalue => l_preparer_tp_name);
1048
1049 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1050 itemkey => l_itemkey,
1051 aname => 'ORIGIN_USER_NAME',
1052 avalue => fnd_global.user_name);
1053
1054 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1055 itemkey => l_itemkey,
1056 aname => 'RECIPIENT_ROLE',
1057 avalue => l_notify_tbl(i).scorer_user_name);
1058
1059 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1060 itemkey => l_itemkey,
1061 aname => 'TEAM_MEMBER',
1062 avalue => l_notify_tbl(i).evaluator_name);
1063
1064 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1065 itemkey => l_itemkey,
1066 aname => 'EVAL_UPDATE_SCORER_SUBJECT',
1067 avalue => l_eval_update_sub);
1068
1069 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1070 itemkey => l_itemkey,
1071 aname => 'EVAL_UPDATE_SCORER_BODY',
1072 avalue => 'plsqlclob:' ||
1073 'pon_eval_team_util_pvt.' ||
1074 'gen_eval_update_scorer_body/' ||
1075 l_itemtype || ':' ||
1076 l_itemkey);
1077
1078 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1079 itemkey => l_itemkey,
1080 aname => 'NEG_SUMMARY_URL',
1081 avalue => l_neg_summary_url);
1082
1083
1084 wf_engine.SetItemOwner(itemtype => l_itemtype,
1085 itemkey => l_itemkey,
1086 owner => fnd_global.user_name);
1087
1088 wf_engine.StartProcess(itemtype => l_itemtype,
1089 itemkey => l_itemkey);
1090
1091
1092 pon_auction_pkg.unset_session_language;
1093
1094 END LOOP;
1095
1096 END send_eval_update_scorer_notif;
1097
1098
1099 PROCEDURE gen_eval_update_scorer_body(p_document_id IN VARCHAR2,
1100 p_display_type IN VARCHAR2,
1101 x_document IN OUT NOCOPY CLOB,
1102 x_document_type IN OUT NOCOPY VARCHAR2)
1103 IS
1104
1105 CURSOR c_notification_id(p_itemtype IN VARCHAR2,
1106 p_itemkey IN VARCHAR2)
1107 IS
1108 SELECT notification_id
1109 FROM wf_item_activity_statuses
1110 WHERE item_type = p_itemtype
1111 AND item_key = p_itemkey
1112 AND assigned_user IS NOT NULL;
1113
1114 l_itemtype VARCHAR2(8);
1115 l_itemkey VARCHAR2(240);
1116
1117 l_team_member VARCHAR2(240);
1118 l_neg_summary_url VARCHAR2(2000);
1119 l_doc_type VARCHAR2(50);
1120 l_doc_number pon_auction_headers_all.document_number%TYPE;
1121 l_auction_title pon_auction_headers_all.auction_title%TYPE;
1122 l_scorer_user_name fnd_user.user_name%TYPE;
1123 l_notification_id NUMBER;
1124
1125 l_language_code VARCHAR2(3);
1126 l_document VARCHAR2(32000);
1127
1128 BEGIN
1129
1130 l_itemtype := SUBSTR(p_document_id, 1, INSTR(p_document_id, ':') - 1);
1131 l_itemkey := SUBSTR(p_document_id, INSTR(p_document_id, ':') + 1,
1132 LENGTH(p_document_id));
1133
1134 l_team_member := wf_engine.GetItemAttrText(itemtype => l_itemtype,
1135 itemkey => l_itemkey,
1136 aname => 'TEAM_MEMBER');
1137
1138 l_doc_type := wf_engine.GetItemAttrText(itemtype => l_itemtype,
1139 itemkey => l_itemkey,
1140 aname => 'DOC_TYPE');
1141
1142 l_doc_number := wf_engine.GetItemAttrText(itemtype => l_itemtype,
1143 itemkey => l_itemkey,
1144 aname => 'DOC_NUMBER');
1145
1146 l_auction_title := wf_engine.GetItemAttrText(itemtype => l_itemtype,
1147 itemkey => l_itemkey,
1148 aname => 'AUCTION_TITLE');
1149
1150 l_scorer_user_name := wf_engine.GetItemAttrText
1151 ( itemtype => l_itemtype,
1152 itemkey => l_itemkey,
1153 aname => 'RECIPIENT_ROLE'
1154 );
1155
1156 l_neg_summary_url := wf_engine.GetItemAttrText
1157 ( itemtype => l_itemtype,
1158 itemkey => l_itemkey,
1159 aname => 'NEG_SUMMARY_URL'
1160 );
1161
1162 OPEN c_notification_id(l_itemtype, l_itemkey);
1163 FETCH c_notification_id INTO l_notification_id;
1164 CLOSE c_notification_id;
1165
1166 IF (l_notification_id IS NOT NULL) THEN
1167 l_neg_summary_url := REPLACE(l_neg_summary_url,
1168 '&#NID', l_notification_id);
1169 END IF;
1170
1171
1172 pon_profile_util_pkg.get_wf_language(l_scorer_user_name, l_language_code);
1173 pon_auction_pkg.set_session_language(NULL, l_language_code);
1174
1175 IF (p_display_type = 'text/html') THEN
1176 fnd_message.set_name('PON', 'PON_SM_EVAL_UPDATE_SCORER_HB');
1177 ELSE
1178 fnd_message.set_name('PON', 'PON_SM_EVAL_UPDATE_SCORER_TB');
1179 END IF;
1180
1181 fnd_message.set_token('TEAM_MEMBER', l_team_member);
1182 fnd_message.set_token('DOC_TYPE', l_doc_type);
1183 fnd_message.set_token('DOC_NUMBER', l_doc_number);
1184 fnd_message.set_token('AUCTION_TITLE', l_auction_title);
1185 fnd_message.set_token('NEG_SUMMARY_URL', l_neg_summary_url);
1186 l_document := fnd_message.get;
1187
1188 wf_notification.WriteToClob(x_document, l_document);
1189
1190 pon_auction_pkg.unset_session_language;
1191
1192 END gen_eval_update_scorer_body;
1193
1194
1195 END PON_EVAL_TEAM_UTIL_PVT;