[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_MASS_NOTIFN_PKG
Source
1 PACKAGE BODY BEN_CWB_MASS_NOTIFN_PKG as
2 /* $Header: bencwbnf.pkb 120.10.12010000.3 2008/09/18 07:31:00 sgnanama ship $ */
3
4 TYPE pid IS RECORD (
5 person_id ben_per_in_ler.person_id%type);
6
7 TYPE person_info IS RECORD (
8 full_name ben_cwb_person_info.full_name%type
9 ,name per_business_groups_perf.name%type
10 ,employee_number ben_cwb_person_info.employee_number%type
11 ,person_id ben_per_in_ler.person_id%type
12 ,per_in_ler_id ben_per_in_ler.per_in_ler_id%type);
13
14 TYPE the_users IS REF CURSOR;
15
16 TYPE g_person_sel_table_type IS TABLE OF pid;
17
18 TYPE g_users_t IS TABLE OF fnd_user.user_name%type;
19
20 g_actn VARCHAR2 (2000);
21 g_proc VARCHAR2 (80);
22 g_package VARCHAR2 (80) := 'BEN_CWB_MASS_NOTIFN_PKG';
23 g_selected_persons g_person_sel_table_type := g_person_sel_table_type();
24 g_sent_total NUMBER;
25 g_unsent_total NUMBER;
26
27 CURSOR c_found(
28 v_user_name IN VARCHAR2
29 ,v_plan_id IN NUMBER
30 ,v_lf_evt_orcd_date IN DATE
31 ,v_messg_txt_body IN VARCHAR2
32 )
33 IS
34 SELECT txn.transaction_id
35 FROM ben_transaction txn
36 WHERE txn.transaction_type = 'CWBMASSNOTIF'
37 AND txn.attribute1 = v_user_name
38 AND txn.attribute2 = v_plan_id||','||to_char(v_lf_evt_orcd_date,'yyyy/mm/dd')
39 AND txn.attribute3 = v_messg_txt_body;
40
41 CURSOR c_person_all
42 IS
43 SELECT ppf.full_name
44 ,bg.name
45 ,ppf.employee_number
46 ,ppf.person_id
47 ,null
48 ,ppf.business_group_id
49 FROM per_all_people_f ppf
50 , per_person_types ppt
51 , per_assignments_f paf
52 , per_business_groups_perf bg
53 WHERE ppf.person_id=paf.person_id
54 AND sysdate BETWEEN ppf.effective_start_date AND ppf.effective_end_date
55 AND paf.primary_flag = 'Y'
56 AND paf.business_group_id = ppf.business_group_id
57 AND paf.person_id = ppf.person_id
58 AND sysdate BETWEEN paf.effective_start_date AND paf.effective_end_date
59 AND ppt.person_type_id=ppf.person_type_id
60 AND ppt.system_person_type = 'EMP'
61 AND bg.business_group_id = ppf.business_group_id;
62 /*
63 SELECT ppf.full_name
64 ,bg.name
65 ,ppf.employee_number
66 ,ppf.person_id
67 ,null
68 ,ppf.business_group_id
69 ,cursor ( select user_name from fnd_user usr
70 where usr.employee_id = ppf.person_id )
71 FROM per_all_people_f ppf
72 ,per_business_groups_perf bg
73 WHERE sysdate BETWEEN NVL(EFFECTIVE_START_DATE,sysdate)
74 AND NVL(EFFECTIVE_END_DATE,sysdate)
75 AND ppf.business_group_id = bg.business_group_id;
76 */
77 CURSOR c_user_selection (
78 v_person_id IN NUMBER
79 )
80 IS
81 SELECT user_name
82 FROM fnd_user usr
83 WHERE usr.employee_id = v_person_id;
84
85 CURSOR c_person_selection (
86 v_group_pl_id IN NUMBER
87 , v_lf_evt_orcd_date IN DATE
88 , v_target_pop IN VARCHAR2
89 , v_req_acc_lvl IN VARCHAR2
90 )
91 IS
92 SELECT DISTINCT max(DECODE (ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
93 'BN', per.brief_name,
94 'CN', per.custom_name,
95 per.full_name)) full_name
96 ,max(bg.name)
97 ,max(per.employee_number)
98 ,max(pil.person_id)
99 ,max(pil.per_in_ler_id)
100 FROM ben_cwb_person_info per
101 ,ben_per_in_ler pil
102 ,ben_cwb_group_hrchy hrchy
103 ,hr_all_organization_units bg
104 WHERE per.group_pl_id = v_group_pl_id
105 AND per.lf_evt_ocrd_dt = v_lf_evt_orcd_date
106 AND pil.per_in_ler_id = per.group_per_in_ler_id
107 AND bg.organization_id (+) = per.business_group_id
108 AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
109 AND (((v_target_pop IS NOT NULL)
110 AND (
111 ((v_target_pop = 'ELI')
112 AND EXISTS( select person_rates.elig_flag from ben_cwb_person_rates person_rates
113 where person_rates.group_per_in_ler_id = pil.per_in_ler_id
114 and person_rates.group_pl_id = pil.group_pl_id
115 AND person_rates.elig_flag = 'Y')
116 AND (hrchy.lvl_num = (SELECT MAX (lvl_num)
117 FROM ben_cwb_group_hrchy hr
118 WHERE hr.emp_per_in_ler_id = hrchy.emp_per_in_ler_id)))
119 OR
120 ((v_target_pop = 'EPO')
121 AND EXISTS( select * from ben_cwb_person_rates person_rates
122 where person_rates.group_per_in_ler_id = pil.per_in_ler_id
123 and person_rates.group_pl_id = pil.group_pl_id
124 and ((person_rates.pay_proposal_id is not null)
125 or (person_rates.element_entry_value_id is not null)))
126 AND (hrchy.lvl_num = (SELECT MAX (lvl_num)
127 FROM ben_cwb_group_hrchy hr
128 WHERE hr.emp_per_in_ler_id = hrchy.emp_per_in_ler_id)))
129 OR
130 ((v_target_pop = 'MAN')
131 AND (hrchy.lvl_num = 0))
132 OR
133 ((v_target_pop = 'MFU')
134 AND (hrchy.lvl_num = 0)
135 AND (pil.per_in_ler_stat_cd = 'PROCD'))
136 OR
137 ((v_target_pop = 'MNS')
138 AND (hrchy.lvl_num = 0)
139 AND EXISTS(select groups.submit_cd
140 from ben_cwb_person_groups groups
141 where groups.group_per_in_ler_id = pil.per_in_ler_id
142 and groups.group_pl_id = pil.group_pl_id
143 and groups.group_oipl_id = -1
144 and groups.submit_cd = 'NS'))
145 OR
146 ((v_target_pop = 'MOB')
147 AND (hrchy.lvl_num = 0)
148 AND EXISTS(select groups.submit_cd
149 from ben_cwb_person_groups groups
150 where groups.group_per_in_ler_id = pil.per_in_ler_id
151 and groups.group_pl_id = pil.group_pl_id
152 and groups.group_oipl_id = -1
153 and groups.submit_cd = 'SU'
154 and groups.approval_cd = 'AP'))
155 OR
156 ((v_target_pop = 'MWA')
157 AND (hrchy.lvl_num = 0)
158 AND EXISTS(select groups.approval_cd
159 from ben_cwb_person_groups groups
160 where groups.group_per_in_ler_id = pil.per_in_ler_id
161 and groups.group_pl_id = pil.group_pl_id
162 and groups.group_oipl_id = -1
163 and nvl(groups.approval_cd,'NULL') <> 'AP'))
164 OR
165 ((v_target_pop = 'MWB')
166 AND (hrchy.lvl_num = 0)
167 AND EXISTS(select groups.submit_cd
168 from ben_cwb_person_groups groups
169 where groups.group_per_in_ler_id = pil.per_in_ler_id
170 and groups.group_pl_id = pil.group_pl_id
171 and groups.group_oipl_id = -1
172 and (groups.dist_bdgt_val is not null
173 or groups.ws_bdgt_val is not null)))
174 OR
175 ((v_target_pop = 'MFX')
176 AND (hrchy.lvl_num = 0)
177 AND EXISTS (select ler.per_in_ler_stat_cd
178 from ben_per_in_ler ler
179 ,ben_cwb_group_hrchy hier
180 where hier.mgr_per_in_ler_id = pil.per_in_ler_id
181 and ler.per_in_ler_id = hier.emp_per_in_ler_id
182 and ler.per_in_ler_stat_cd = 'PROCD'))
183 OR
184 ((v_target_pop = 'MEL')
185 AND (hrchy.lvl_num = 0)
186 AND EXISTS (select rts.elig_flag
187 from ben_cwb_person_rates rts
188 ,ben_cwb_group_hrchy hier
189 where hier.mgr_per_in_ler_id = hrchy.mgr_per_in_ler_id
190 and rts.group_per_in_ler_id = hier.emp_per_in_ler_id
191 and rts.elig_flag = 'Y'
192 and hier.lvl_num <> 0))
193 OR
194 ((v_target_pop = 'MWT')
195 AND (hrchy.lvl_num = 0)
196 AND NOT EXISTS (select tsk.task_id
197 from ben_cwb_person_tasks tsk
198 where tsk.group_per_in_ler_id = hrchy.mgr_per_in_ler_id
199 and tsk.status_cd in ('IP','NA','CO'))
200 )
201 )
202 )
203 OR
204 ((v_target_pop IS NULL)
205 AND (hrchy.lvl_num = (SELECT MAX (lvl_num)
206 FROM ben_cwb_group_hrchy hr
207 WHERE hr.emp_per_in_ler_id = hrchy.emp_per_in_ler_id)))
208 )
209 AND (((v_req_acc_lvl IS NOT NULL)
210 AND
211 (
212 ((v_req_acc_lvl = 'AL')
213 AND EXISTS (select gr.access_cd
214 from ben_cwb_person_groups gr
215 , ben_cwb_group_hrchy hr
216 where hr.emp_per_in_ler_id = pil.per_in_ler_id
217 and hr.lvl_num = 0
218 and gr.group_per_in_ler_id = hr.emp_per_in_ler_id
219 and gr.group_pl_id = pil.group_pl_id
220 and gr.group_oipl_id = -1
221 and gr.access_cd IN ('UP','RO','NA')))
222 OR
223 ((v_req_acc_lvl = 'FR')
224 AND EXISTS (select gr.access_cd
225 from ben_cwb_person_groups gr
226 , ben_cwb_group_hrchy hr
227 where hr.emp_per_in_ler_id = pil.per_in_ler_id
228 and hr.lvl_num = 0
229 and gr.group_per_in_ler_id = pil.per_in_ler_id
230 and gr.group_pl_id = pil.group_pl_id
231 and gr.group_oipl_id = -1
232 and gr.access_cd IN ('UP','RO')))
233 OR
234 ((v_req_acc_lvl = 'FU')
235 AND EXISTS (select gr.access_cd
236 from ben_cwb_person_groups gr
237 , ben_cwb_group_hrchy hr
238 where hr.emp_per_in_ler_id = pil.per_in_ler_id
239 and hr.lvl_num = 0
240 and gr.group_per_in_ler_id = hrchy.emp_per_in_ler_id
241 and gr.group_pl_id = pil.group_pl_id
242 and gr.group_oipl_id = -1
243 and gr.access_cd IN ('UP')))
244 OR
245 ((v_req_acc_lvl = 'NA')
246 AND EXISTS (select gr.access_cd
247 from ben_cwb_person_groups gr
248 , ben_cwb_group_hrchy hr
249 where hr.emp_per_in_ler_id = pil.per_in_ler_id
250 and hr.lvl_num = 0
251 and gr.group_per_in_ler_id = hrchy.emp_per_in_ler_id
252 and gr.group_pl_id = pil.group_pl_id
253 and gr.group_oipl_id = -1
254 and gr.access_cd IN ('NA')))
255 OR
256 ((v_req_acc_lvl = 'RO')
257 AND EXISTS (select gr.access_cd
258 from ben_cwb_person_groups gr
259 , ben_cwb_group_hrchy hr
260 where hr.emp_per_in_ler_id = pil.per_in_ler_id
261 and hr.lvl_num = 0
262 and gr.group_per_in_ler_id = hrchy.emp_per_in_ler_id
263 and gr.group_pl_id = pil.group_pl_id
264 and gr.group_oipl_id = -1
265 and gr.access_cd IN ('RO')))
266 )
267 )
268 OR (v_req_acc_lvl IS NULL))
269 group by per.person_id;
270
271 CURSOR c_check_termination (
272 v_person_id IN NUMBER
273 )
274 IS
275 SELECT ppt.system_person_type
276 FROM per_person_types ppt
277 ,per_all_people_f ppf
278 WHERE ppf.person_id = v_person_id
279 AND sysdate BETWEEN ppf.effective_start_date AND ppf.effective_end_date
280 AND ppt.person_type_id = ppf.person_type_id;
281
282 CURSOR c_check_assignment (
283 v_group_per_in_ler_id IN NUMBER
284 )
285 IS
286 SELECT info.assignment_id
287 FROM ben_cwb_person_info info
288 ,per_assignments_f paf
289 WHERE info.group_per_in_ler_id = v_group_per_in_ler_id
290 AND paf.assignment_id = info.assignment_id
291 AND sysdate BETWEEN paf.effective_start_date AND paf.effective_end_date;
292
293 -- --------------------------------------------------------------------------
294 -- |-----------------------------< WRITE >----------------------------------|
295 -- --------------------------------------------------------------------------
296 --
297 PROCEDURE WRITE (p_string IN VARCHAR2)
298 IS
299 BEGIN
300 ben_batch_utils.WRITE (p_string);
301 END;
302
303 -- --------------------------------------------------------------------------
304 -- |---------------------< check_selection_rule >----------------------------|
305 -- --------------------------------------------------------------------------
306 --
307 -- Description
308 -- This procedure checks for person selection rule.
309 --
310
311 FUNCTION check_selection_rule(
312 p_person_selection_rule_id IN NUMBER
313 ,p_person_id IN NUMBER
314 ,p_business_group_id IN NUMBER
315 ,p_effective_date IN DATE
316 ,p_input1 in varchar2 default null -- Bug 5331889
317 ,p_input1_value in varchar2 default null)
318 RETURN BOOLEAN IS
319 --
320 l_outputs ff_exec.outputs_t;
321 l_assignment_id NUMBER;
322 l_package VARCHAR2(80) := g_package || '.check_selection_rule';
323 value_exception exception ;
324 --
325 BEGIN
326 --
327 IF p_person_selection_rule_id IS NULL THEN
328 --
329 RETURN TRUE;
330 --
331 ELSE
332 --
333 l_assignment_id :=
334 benutils.get_assignment_id(p_person_id => p_person_id
335 ,p_business_group_id => p_business_group_id
336 ,p_effective_date => p_effective_date);
337 --
338 if l_assignment_id is null
339 then
340 raise ben_batch_utils.g_record_error;
341 end if ;
342 --
343 l_outputs :=
344 benutils.formula(p_formula_id => p_person_selection_rule_id
345 ,p_effective_date => p_effective_date
346 ,p_business_group_id => p_business_group_id
347 ,p_assignment_id => l_assignment_id
348 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
349 ,p_param1_value => to_char(p_person_id)
350 ,p_param2 => p_input1
351 ,p_param2_value => p_input1_value);
352 --
353 IF l_outputs(l_outputs.FIRST).VALUE = 'Y' THEN
354 --
355 RETURN TRUE;
356 --
357 ELSIF l_outputs(l_outputs.FIRST).VALUE = 'N' THEN
358 --
359 RETURN FALSE;
360 --
361 ELSIF upper(l_outputs(l_outputs.FIRST).VALUE) not in ('Y', 'N') THEN
362 --
363 RAISE value_exception;
364 --
365 END IF;
366 --
367 END IF;
368 --
369 EXCEPTION
370 --
371 When ben_batch_utils.g_record_error then
372 hr_utility.set_location(l_package ,10);
373 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
374 fnd_message.set_token('ID' ,to_char(p_person_id) );
375 fnd_message.set_token('PROC',l_package ) ;
376 Ben_batch_utils.write(p_text => '<< Person id : '||to_char(p_person_id)||' failed.'||
377 ' Reason : '|| fnd_message.get ||' >>' );
378 RETURN FALSE;
379 When value_exception then
380 hr_utility.set_location(l_package ,20);
381 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
382 fnd_message.set_token('RL','person_selection_rule_id :'||p_person_selection_rule_id);
383 fnd_message.set_token('PROC',l_package ) ;
384 Ben_batch_utils.write(p_text => '<< Person id : '||to_char(p_person_id)||' failed.'||
385 ' Reason : '|| fnd_message.get ||' >>' );
386 RETURN FALSE;
387 WHEN OTHERS THEN
388 hr_utility.set_location(l_package ,30);
389 Ben_batch_utils.write(p_text => '<< Person id : '||to_char(p_person_id)||' failed.'||
390 ' Reason : '|| SQLERRM ||' >>' );
391 RETURN FALSE;
392 --
393 END check_selection_rule;
394 -- --------------------------------------------------------------------------
395 -- |---------------------< inc_home_link >--------------------------------|
396 -- --------------------------------------------------------------------------
397 --
398 -- Description
399 -- This procedure checks if link for Compensation Workbench has to be
400 -- included in the Notification. This preference is set in the concurrent
401 -- process.
402 --
403 procedure inc_home_link
404 (itemtype in varchar2
405 , itemkey in varchar2
406 , actid in number
407 , funcmode in varchar2
408 , result out nocopy varchar2)
409 is
410 l_include_link varchar2(240);
411 l_package varchar2(80) := g_package||'.inc_home_link';
412 l_error varchar2(5000);
413 begin
414 hr_utility.set_location('Entering '||l_package ,30);
415 l_include_link := wf_engine.GetItemAttrText(itemtype => itemtype,
416 itemkey => itemkey,
417 aname => 'MASS_NTF_LINK');
418 if ( l_include_link = 'Y') then
419 result:= 'COMPLETE:' ||'Y';
420 else
421 result:= 'COMPLETE:' ||'N';
422 end if;
423 hr_utility.set_location('Leaving '||l_package ,30);
424 EXCEPTION
425 when others then
426 l_error:=sqlerrm;
427 hr_utility.set_location ('exception is'||l_error , 300);
428 Wf_Core.Context('BEN_CWB_MASS_NOTIFN_PKG' , 'inc_home_link',l_error);
429 raise;
430
431 end inc_home_link;
432 -- --------------------------------------------------------------------------
433 -- |---------------------< mass_ntf_cleanup >--------------------------------|
434 -- --------------------------------------------------------------------------
435 --
436 -- Description
437 -- This procedure has cleanup code for the workflow engine.
438 --
439 procedure mass_ntf_cleanup
440 (itemtype in varchar2
441 , itemkey in varchar2
442 , actid in number
443 , funcmode in varchar2
444 , result out nocopy varchar2)
445 is
446 --users WF_DIRECTORY.UserTable;
447 l_package varchar2(80) := g_package||'.mass_ntf_cleanup';
448 l_error varchar2(5000);
449
450 begin
451 hr_utility.set_location('Entering '||l_package ,30);
452 /* removed dynamic role based messaging
453 WF_DIRECTORY.GetRoleUsers('CWB_MASS_NTF_ROLE_TEMP1',users);
454 if users.first is not null then
455 for i in users.first..users.last
456 loop
457 null;
458 WF_DIRECTORY.RemoveUsersFromAdHocRole('CWB_MASS_NTF_ROLE_TEMP1',null);
459 end loop;
460 end if;
461 */
462 result:= 'COMPLETE:';
463 hr_utility.set_location('Leaving '||l_package ,40);
464 EXCEPTION
465 when others then
466 l_error:=sqlerrm;
467 hr_utility.set_location ('exception is'||l_error , 300);
468 Wf_Core.Context('BEN_CWB_MASS_NOTIFN_PKG' , 'mass_ntf_cleanup',l_error);
469 raise;
470
471 end mass_ntf_cleanup;
472
473 -- --------------------------------------------------------------------------
474 -- |---------------------< get_item_attribute >--------------------------------|
475 -- --------------------------------------------------------------------------
476 --
477 -- Description
478 -- This procedure returns the attribute text
479 --
480 function get_item_attribute
481 (itemtype in varchar2
482 , itemkey in varchar2
483 , aname in varchar2)
484 return varchar2
485 is
486 l_package varchar2(80) := g_package||'.get_item_attribute';
487 l_error varchar2(5000);
488 l_value varchar2(1000);
489 begin
490 hr_utility.set_location('Entering '||l_package ,30);
491 l_value := WF_ENGINE.GetItemAttrText(itemtype => itemtype
492 ,itemkey => itemkey
493 ,aname => aname);
494 hr_utility.set_location('Leaving '||l_package ,40);
495 return l_value;
496 EXCEPTION
497 when others then
498 l_error:=sqlerrm;
499 hr_utility.set_location ('exception is'||l_error , 300);
500 raise;
501
502 end get_item_attribute;
503
504
505 -- --------------------------------------------------------------------------
506 -- |---------------------< create_workflow >--------------------------------|
507 -- --------------------------------------------------------------------------
508 --
509 --
510 PROCEDURE create_workflow(
511 v_users IN g_users_t
512 ,v_people IN person_info
513 ,p_pl_id in number
514 ,p_lf_evt_ocrd_dt in date
515 ,p_messg_txt_title in varchar2 default null
516 ,p_messg_txt_body in varchar2 default null
517 ,p_include_cwb_link in varchar2 default 'N'
518 ,p_resend_if_prev_sent in varchar2 default 'N'
519 ,p_mail_to_user in varchar2 default null
520 ,p_withhold_notifn in varchar2 default 'N'
521 )
522 IS
523 l_plan_name varchar2(240);
524 l_yr_perd_start_dt date;
525 l_yr_perd_end_dt date;
526 l_wthn_yr_start_dt date;
527 l_wthn_yr_end_dt date;
528 l_notify boolean;
529 l_transaction_id number;
530 l_wf_key number;
531 l_transaction_key number;
532 l_trans_rec ben_transaction%rowtype;
533 l_user_name varchar2(100);
534 l_reject_reason varchar2(30);
535 l_system_person_type per_person_types.system_person_type%type;
536 l_assignment_id ben_cwb_person_info.assignment_id%type;
537 BEGIN
538
539 if(v_people.per_in_ler_id is not null) then
540
541 OPEN c_check_termination(v_people.person_id);
542 FETCH c_check_termination
543 INTO l_system_person_type;
544 CLOSE c_check_termination;
545
546 OPEN c_check_assignment(v_people.per_in_ler_id);
547 FETCH c_check_assignment
548 INTO l_assignment_id;
549 CLOSE c_check_assignment;
550
551 if(l_assignment_id is null) then
552 l_reject_reason := 'ASSIGNMENT ENDED';
553 end if;
554
555 if(l_system_person_type = 'EX_EMP') then
556 l_reject_reason := 'EMPLOYEE TERMINATED';
557 end if;
558
559 else
560 l_system_person_type := 'DEFAULT';
561 l_assignment_id := -1;
562 end if;
563
564 if((l_system_person_type <> 'EX_EMP')and(l_assignment_id is not null)) then
565
566 SELECT user_name
567 INTO l_user_name
568 FROM fnd_user
569 WHERE user_id = fnd_global.user_id;
570 SELECT name
571 ,yr_perd_start_dt
572 ,yr_perd_end_dt
573 ,wthn_yr_start_dt
574 ,wthn_yr_end_dt
575 INTO l_plan_name
576 ,l_yr_perd_start_dt
577 ,l_yr_perd_end_dt
578 ,l_wthn_yr_start_dt
579 ,l_wthn_yr_end_dt
580 FROM ben_cwb_pl_dsgn
581 WHERE pl_id = p_pl_id
582 AND oipl_id = -1
583 AND lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
584
585 if(v_users.COUNT=0) then
586 WRITE('NOT SENT' ||' - '|| 'NO USER ATTACHED' ||' - '|| v_people.full_name
587 ||' - '|| v_people.name ||' - '|| v_people.employee_number
588 ||' - '|| v_people.person_id||' - '|| v_people.per_in_ler_id);
589 g_unsent_total := g_unsent_total + 1;
590 end if;
591
592 FOR element IN 1..v_users.COUNT
593 LOOP
594 if(v_users.exists(element)) then
595 --testing phase omission
596 l_notify := TRUE;
597 if(p_resend_if_prev_sent='N') then
598 OPEN c_found(v_users(element)
599 ,p_pl_id
600 ,p_lf_evt_ocrd_dt
601 ,p_messg_txt_body
602 );
603 FETCH c_found
604 INTO l_transaction_id;
605 if(l_transaction_id is not null) then
606 l_notify := FALSE;
607 end if;
608 CLOSE c_found;
609 end if;
610 if(l_notify = TRUE) then
611 if(p_withhold_notifn = 'N') then
612
613 select BEN_CWB_WF_NTF_S.NEXTVAL into l_wf_key from dual;
614 wf_engine.createProcess (ItemType => 'BENCWBFY',
615 ItemKey => l_wf_key,
616 process => 'MASS_NOTIFICATION_PROC');
617 -----------------------------------------------------------------
618
619 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
620 itemkey => l_wf_key,
621 aname => 'MASS_NTF_KEY',
622 avalue => l_wf_key);
623
624 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
625 itemkey => l_wf_key,
626 aname => 'MASS_NTF_TITLE',
627 avalue => p_messg_txt_title);
628 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
629 itemkey => l_wf_key,
630 aname => 'MASS_NTF_MSG',
631 avalue => p_messg_txt_body);
632 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
633 itemkey => l_wf_key,
634 aname => 'MASS_NTF_SUB',
635 avalue => '');
636 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
637 itemkey => l_wf_key,
638 aname => 'MASS_NTF_LINK',
639 avalue => p_include_cwb_link);
640 ----------------------------------------------------------------
641 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
642 itemkey => l_wf_key,
643 aname => 'FROM_ROLE',
644 avalue => l_user_name);
645 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
646 itemkey => l_wf_key,
647 aname => 'MASS_NTF_HDR_PLAN',
648 avalue => l_plan_name);
649 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
650 itemkey => l_wf_key,
651 aname => 'MASS_NTF_HDR_PERIOD',
652 avalue => l_yr_perd_start_dt
653 ||' - '||l_yr_perd_end_dt);
654 ------------------------------------------------------------
655 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
656 itemkey => l_wf_key,
657 aname => 'MASS_NTF_RCVR',
658 avalue => v_users(element));
659 wf_engine.StartProcess (ItemType => 'BENCWBFY',
660 ItemKey => l_wf_key);
661 ------------------------------------------------------------
662 /* commenting out - sending indiv threads
663 WF_DIRECTORY.AddUsersToAdHocRole(adhocRole,v_users(element));
664 */
665 --insert into nt values (v_users(element));
666
667 select BEN_TRANSACTION_S.NEXTVAL into l_transaction_key from dual;
668 l_trans_rec.attribute1 := v_users(element);
669 l_trans_rec.attribute2 := p_pl_id ||','|| to_char(p_lf_evt_ocrd_dt,'yyyy/mm/dd');
670 l_trans_rec.attribute3 := p_messg_txt_body;
671 l_trans_rec.attribute4 := sysdate;
672 insert into ben_transaction
673 (transaction_id ,transaction_type
674 ,attribute1 ,attribute2
675 ,attribute3 ,attribute4
676 ,attribute5 ,attribute6
677 ,attribute7 ,attribute8
678 ,attribute9 ,attribute10
679 ,attribute11 ,attribute12
680 ,attribute13 ,attribute14
681 ,attribute15 ,attribute16
682 ,attribute17 ,attribute18
683 ,attribute19 ,attribute20
684 ,attribute21 ,attribute22
685 ,attribute23 ,attribute24
686 ,attribute25 ,attribute26
687 ,attribute27 ,attribute28
688 ,attribute29 ,attribute30
689 ,attribute31 ,attribute32
690 ,attribute33 ,attribute34
691 ,attribute35 ,attribute36
692 ,attribute37 ,attribute38
693 ,attribute39 ,attribute40)
694 values
695 (l_transaction_key ,'CWBMASSNOTIF'
696 ,l_trans_rec.attribute1 ,l_trans_rec.attribute2
697 ,l_trans_rec.attribute3 ,l_trans_rec.attribute4
698 ,l_trans_rec.attribute5 ,l_trans_rec.attribute6
699 ,l_trans_rec.attribute7 ,l_trans_rec.attribute8
700 ,l_trans_rec.attribute9 ,l_trans_rec.attribute10
701 ,l_trans_rec.attribute11 ,l_trans_rec.attribute12
702 ,l_trans_rec.attribute13 ,l_trans_rec.attribute14
703 ,l_trans_rec.attribute15 ,l_trans_rec.attribute16
704 ,l_trans_rec.attribute17 ,l_trans_rec.attribute18
705 ,l_trans_rec.attribute19 ,l_trans_rec.attribute20
706 ,l_trans_rec.attribute21 ,l_trans_rec.attribute22
707 ,l_trans_rec.attribute23 ,l_trans_rec.attribute24
708 ,l_trans_rec.attribute25 ,l_trans_rec.attribute26
709 ,l_trans_rec.attribute27 ,l_trans_rec.attribute28
710 ,l_trans_rec.attribute29 ,l_trans_rec.attribute30
711 ,l_trans_rec.attribute31 ,l_trans_rec.attribute32
712 ,l_trans_rec.attribute33 ,l_trans_rec.attribute34
713 ,l_trans_rec.attribute35 ,l_trans_rec.attribute36
714 ,l_trans_rec.attribute37 ,l_trans_rec.attribute38
715 ,l_trans_rec.attribute39 ,l_trans_rec.attribute40);
716 end if;
717
718 /* WRITE('Notification issued to : '||v_users(element)); */
719 WRITE('SENT' ||' - '|| v_users(element) ||' - '|| v_people.full_name
720 ||' - '|| v_people.name ||' - '|| v_people.employee_number
721 ||' - '|| v_people.person_id||' - '|| v_people.per_in_ler_id);
722 g_sent_total := g_sent_total + 1;
723 else if(l_notify <> TRUE) then
724 WRITE('NOT RESENT' ||' - '|| v_users(element) ||' - '|| v_people.full_name
725 ||' - '|| v_people.name ||' - '|| v_people.employee_number
726 ||' - '|| v_people.person_id||' - '|| v_people.per_in_ler_id);
727 g_unsent_total := g_unsent_total + 1;
728 end if;
729 end if;
730 end if;
731
732 END LOOP;
733 else
734 WRITE('NOT SENT' ||' - '|| l_reject_reason ||' - '|| v_people.full_name
735 ||' - '|| v_people.name ||' - '|| v_people.employee_number
736 ||' - '|| v_people.person_id||' - '|| v_people.per_in_ler_id);
737 g_unsent_total := g_unsent_total + 1;
738 end if;
739 END create_workflow;
740 -- --------------------------------------------------------------------------
741 -- |------------------------------< notify >--------------------------------|
742 -- --------------------------------------------------------------------------
743 -- Description
744 -- This procedure contains calls for sending mass notifications via
745 -- Oracle Workflow. This will be called by a concurent process.
746 --
747 procedure notify( errbuf out nocopy varchar2
748 ,retcode out nocopy number
749 ,p_pl_id in number
750 ,p_lf_evt_ocrd_dt in varchar2
751 ,p_messg_txt_title in varchar2 default null
752 ,p_messg_txt_body in varchar2 default null
753 ,p_target_pop in varchar2 default null
754 ,p_req_acc_lvl in varchar2 default null
755 ,p_person_selection_rule_id in number default null
756 ,p_include_cwb_link in varchar2 default 'N'
757 ,p_resend_if_prev_sent in varchar2 default 'N'
758 ,p_mail_to_user in varchar2 default null
759 ,p_withhold_notifn in varchar2 default 'N'
760 )
761 is
762
763 ps_rec person_info;
764 l_pid pid;
765 users the_users;
766 v_users g_users_t := g_users_t();
767 --adhocRole varchar2(50):='CWB_MASS_NTF_ROLE_TEMP1';
768 --adhocDRole varchar2(50):='Placeholder Role';
769 display_name varchar2(240);
770 email_id varchar2(240);
771 notif_pref varchar2(240);
772 lang varchar2(240);
773 terr varchar2(240);
774 --adHocUsers WF_DIRECTORY.UserTable;
775 l_error varchar2(5000);
776 l_business_group_id number;
777 l_emp_processed number;
778 l_emp_with_user number;
779 l_emp_with_no_user number;
780 l_effective_date date;
781 l_commit number;
782 l_can_date date;
783 l_var_date varchar2(50);
784 l_lf_evt_ocrd_dt date;
785 l_benefit_action_id number;
786 l_object_version_number number;
787 BEGIN
788
789 begin
790 l_can_date := fnd_date.canonical_to_date(p_lf_evt_ocrd_dt);
791 l_var_date := fnd_date.date_to_canonical(l_can_date);
792 if(l_var_date = p_lf_evt_ocrd_dt) then
793 l_lf_evt_ocrd_dt := l_can_date;
794 else
795 l_lf_evt_ocrd_dt := fnd_date.canonical_to_date(p_lf_evt_ocrd_dt);
796 end if;
797 exception
798 when others then
799 l_lf_evt_ocrd_dt := fnd_date.canonical_to_date(p_lf_evt_ocrd_dt);
800 end;
801
802 g_sent_total := 0;
803 g_unsent_total := 0;
804 if(p_withhold_notifn = 'Y') then
805 WRITE('==========================++ !NOTE! ++====================================');
806 WRITE('NOTIFICATIONS ARE WITHHELD AS THIS IS JUST A TEST RUN.');
807 WRITE('ONLY LOG FILE IS GENERATED!');
808 WRITE ('===========================================================================');
809 end if;
810 WRITE('==============================START========================================');
811 g_actn := 'Compensation Workbench Mass Notification initialised...';
812 WRITE ('Time '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
813 WRITE (g_actn);
814 g_proc := g_package || '.notify';
815 WRITE ('=============================NOTIFY========================================');
816 WRITE ('|Parameter Value ');
817 --WRITE ('||p_effective_dates - ' || p_effective_date);
818 --WRITE ('||p_validate - ' || p_validate);
819 WRITE ('|p_pl_id - ' || p_pl_id);
820 WRITE ('|p_lf_evt_ocrd_dt - ' || l_lf_evt_ocrd_dt);
821 WRITE ('|p_messg_txt_title - ' || p_messg_txt_title);
822 WRITE ('|p_messg_txt_body - ' || p_messg_txt_body);
823 WRITE ('|p_target_pop - ' || p_target_pop);
824 WRITE ('|p_req_acc_lvl - ' || p_req_acc_lvl);
825 WRITE ('p_person_selection_rule_id - ' || p_person_selection_rule_id);
826 WRITE ('|p_include_cwb_link - ' || p_include_cwb_link);
827 WRITE ('|p_resend_if_prev_sent - ' || p_resend_if_prev_sent);
828 WRITE ('|p_mail_to_user - ' || p_mail_to_user);
829 WRITE ('|p_withhold_notifn - ' || p_withhold_notifn);
830 --WRITE ('||p_is_multi_thread - ' || p_is_multi_thread);
831 WRITE ('===========================================================================');
832
833 l_effective_date := trunc(fnd_date.canonical_to_date(sysdate));
834 --
835 -- Put row in fnd_sessions
836 --
837 dt_fndate.change_ses_date
838 (p_ses_date => l_effective_date,
839 p_commit => l_commit);
840 WRITE ('Changing Session Date: '||l_effective_date);
841 WRITE ('Commit on date : '||l_commit);
842 WRITE ('===========================================================================');
843
844 g_actn := 'Calling ben_batch_utils.ini...';
845 WRITE (g_actn);
846 write ('ben_batch_utils.ini with PROC_INFO');
847 ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');/*
848 g_actn := 'Calling benutils.get_parameter...';
849 WRITE (g_actn);
850 write_h ('benutils.get_parameter with ' || p_bg_id || ' ' || 'BENCWBMN' || ' '
851 || g_max_errors_allowed
852 );
853 benutils.get_parameter (p_business_group_id => p_bg_id
854 , p_batch_exe_cd => 'BENCWBPP'
855 , p_threads => l_threads
856 , p_chunk_size => l_chunk_size
857 , p_max_errors => g_max_errors_allowed
858 );
859 write_h ('Values of l_threads is ' || l_threads || ' and l_chunk_size is ' || l_chunk_size);*/
860 benutils.g_thread_id := 99; -- need to investigate why this is needed
861 g_actn := 'Creating benefit actions...';
862 WRITE (g_actn);
863 WRITE ('Time'||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
864 write ('=====================Benefit Actions=======================');
865 write ('||Parameter value ');
866 write ('||p_request_id- ' || fnd_global.conc_request_id);
867 write ('||p_program_application_id- ' || fnd_global.prog_appl_id);
868 write ('||p_program_id- ' || fnd_global.conc_program_id);
869 write ('==========================================================');
870 ben_benefit_actions_api.create_perf_benefit_actions
871 (p_benefit_action_id => l_benefit_action_id
872 , p_process_date => l_effective_date
873 , p_mode_cd => 'W'
874 , p_derivable_factors_flag => 'NONE'
875 , p_validate_flag => 'N'
876 , p_debug_messages_flag => 'N'
877 , p_business_group_id => benutils.get_profile('PER_BUSINESS_GROUP_ID')
878 , p_no_programs_flag => 'N'
879 , p_no_plans_flag => 'N'
880 , p_audit_log_flag => 'N'
881 , p_pl_id => p_pl_id
882 , p_pgm_id => -9999
883 , p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
884 --, p_person_id => p_person_id
885 --, p_grant_price_val => p_grant_price_val
886 , p_object_version_number => l_object_version_number
887 , p_effective_date => l_effective_date
888 , p_request_id => fnd_global.conc_request_id
889 , p_program_application_id => fnd_global.prog_appl_id
890 , p_program_id => fnd_global.conc_program_id
891 , p_program_update_date => SYSDATE/*
892 , p_bft_attribute1 => l_process_compents
893 , p_bft_attribute3 => p_employees_in_bg
894 , p_bft_attribute4 => p_manager_id*/
895 , p_bft_attribute30 => 'N'
896 );
897 write ('Benefit Action Id is ' || l_benefit_action_id);
898 benutils.g_benefit_action_id := l_benefit_action_id;
899
900
901 l_emp_processed := 0;
902 l_emp_with_user := 0;
903 l_emp_with_no_user := 0;
904
905 /*WF_DIRECTORY.GetRoleUsers('CWB_MASS_NTF_ROLE_TEMP1',adHocUsers);
906 if adHocUsers.first is not null then
907 for i in adHocUsers.first..adHocUsers.last
908 loop
909 WF_DIRECTORY.RemoveUsersFromAdHocRole('CWB_MASS_NTF_ROLE_TEMP1',adHocUsers(i));
910 end loop;
911 end if;*/
912 /*
913 wf_directory.GetRoleInfo(adhocRole
914 ,display_name
915 ,email_id
916 ,notif_pref
917 ,lang
918 ,terr
919 );
920 if(display_name is null) then
921 wf_directory.CreateAdHocRole(adhocRole,adhocDRole);
922 end if;
923 */
924
925 if(p_mail_to_user is not null) then
926 WRITE('Mailing Notification');
927 WRITE('STATUS - USERNAME - - - - -');
928 v_users.extend;
929 v_users(v_users.last) := p_mail_to_user;
930 /*WRITE(v_users.COUNT||' users attached');*/
931 create_workflow ( v_users => v_users
932 ,v_people => ps_rec
933 ,p_pl_id => p_pl_id
934 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
935 ,p_messg_txt_title => p_messg_txt_title
936 ,p_messg_txt_body => p_messg_txt_body
937 ,p_include_cwb_link => p_include_cwb_link
938 ,p_resend_if_prev_sent => p_resend_if_prev_sent
939 ,p_mail_to_user => p_mail_to_user
940 ,p_withhold_notifn => p_withhold_notifn);
941 l_emp_processed := l_emp_processed + 1;
942 l_emp_with_user := l_emp_with_user + 1;
943 elsif (p_person_selection_rule_id is not null) then
944 WRITE('STATUS - USERNAME - NAME - BUSINESS GROUP - EMP.NO. - PERSON ID - PER IN LER ID');
945 OPEN c_person_all;
946 LOOP
947 FETCH c_person_all
948 INTO ps_rec.full_name,
949 ps_rec.name,
950 ps_rec.employee_number,
951 ps_rec.person_id,
952 ps_rec.per_in_ler_id,
953 l_business_group_id;
954 /*
955 ,
956 users;
957 */
958 EXIT WHEN c_person_all%NOTFOUND;
959
960 BEGIN
961 IF (check_selection_rule(p_person_selection_rule_id => p_person_selection_rule_id
962 ,p_person_id => ps_rec.person_id
963 ,p_business_group_id => l_business_group_id
964 ,p_effective_date => sysdate)) then
965
966 /*WRITE('===========================================================================');
967 WRITE(ps_rec.full_name||' - '||ps_rec.name||' - '||ps_rec.employee_number
968 ||' - '||ps_rec.person_id||' - '||ps_rec.per_in_ler_id);*/
969 l_emp_processed := l_emp_processed + 1;
970 OPEN c_user_selection (ps_rec.person_id);
971 FETCH c_user_selection bulk collect into v_users;
972 CLOSE c_user_selection;
973 /*FETCH users bulk collect into v_users;*/
974
975 create_workflow ( v_users => v_users
976 ,v_people => ps_rec
977 ,p_pl_id => p_pl_id
978 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
979 ,p_messg_txt_title => p_messg_txt_title
980 ,p_messg_txt_body => p_messg_txt_body
981 ,p_include_cwb_link => p_include_cwb_link
982 ,p_resend_if_prev_sent => p_resend_if_prev_sent
983 ,p_mail_to_user => p_mail_to_user
984 ,p_withhold_notifn => p_withhold_notifn
985 );
986
987 if(v_users.COUNT > 0) then
988 l_emp_with_user := l_emp_with_user + 1;
989 else
990 l_emp_with_no_user := l_emp_with_no_user +1;
991 end if;
992 /*WRITE(v_users.COUNT||' users attached');*/
993 ELSE
994 v_users.delete;
995 END IF;
996 EXCEPTION
997 WHEN others THEN
998 WRITE('PERSON ID ERRORED :'||ps_rec.person_id);
999 END;
1000 END LOOP;
1001 CLOSE c_person_all;
1002 else
1003 WRITE('STATUS - USERNAME - NAME - BUSINESS GROUP - EMP.NO. - PERSON ID - PER IN LER ID');
1004 OPEN c_person_selection (p_pl_id
1005 ,l_lf_evt_ocrd_dt
1006 ,p_target_pop
1007 ,p_req_acc_lvl
1008 );
1009
1010 LOOP
1011 FETCH c_person_selection
1012 INTO ps_rec.full_name,
1013 ps_rec.name,
1014 ps_rec.employee_number,
1015 ps_rec.person_id,
1016 ps_rec.per_in_ler_id;
1017 /*
1018 ,
1019 users;
1020 */
1021
1022 EXIT WHEN c_person_selection%NOTFOUND;
1023
1024 --l_pid.person_id := ps_rec.person_id;
1025 --g_selected_persons.extend;
1026 --g_selected_persons(g_selected_persons.last) := l_pid;
1027
1028 --WRITE('Person identified');
1029 /*WRITE('===========================================================================');
1030 WRITE(ps_rec.full_name||' - '||ps_rec.name||' - '||ps_rec.employee_number
1031 ||' - '||ps_rec.person_id||' - '||ps_rec.per_in_ler_id);*/
1032 l_emp_processed := l_emp_processed + 1;
1033 OPEN c_user_selection (ps_rec.person_id);
1034 FETCH c_user_selection bulk collect into v_users;
1035
1036 --if v_users is not null then
1037 /*
1038 for indx in v_users.first .. v_users.last
1039 loop
1040 insert into nt values (v_users(indx));
1041 end loop;
1042 */
1043 --end if;
1044 /*WRITE(v_users.COUNT||' users attached');*/
1045 if(v_users.COUNT > 0) then
1046 l_emp_with_user := l_emp_with_user + 1;
1047 else
1048 l_emp_with_no_user := l_emp_with_no_user +1;
1049 end if;
1050 --end if;
1051 create_workflow ( v_users => v_users
1052 ,v_people => ps_rec
1053 ,p_pl_id => p_pl_id
1054 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1055 ,p_messg_txt_title => p_messg_txt_title
1056 ,p_messg_txt_body => p_messg_txt_body
1057 ,p_include_cwb_link => p_include_cwb_link
1058 ,p_resend_if_prev_sent => p_resend_if_prev_sent
1059 ,p_mail_to_user => p_mail_to_user
1060 ,p_withhold_notifn => p_withhold_notifn
1061 );
1062 CLOSE c_user_selection;
1063 END LOOP;
1064 CLOSE c_person_selection;
1065 end if;
1066 /* commenting out - sending indiv processes
1067 ------------------------------------------------------------
1068 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
1069 itemkey => l_wf_key,
1070 aname => 'MASS_NTF_RCVR',
1071 avalue => adhocRole);
1072 wf_engine.StartProcess (ItemType => 'BENCWBFY',
1073 ItemKey => l_wf_key);
1074 ------------------------------------------------------------
1075 */
1076 WRITE ('=============================================================================');
1077 WRITE ('Employees processed : '|| l_emp_processed);
1078 WRITE ('Employees with users defined in FND_USER : '|| l_emp_with_user);
1079 WRITE ('Employees with no users defined in FND_USER : '|| l_emp_with_no_user);
1080 WRITE ('Note: Notifications issued only to employee with defined user(s)');
1081 WRITE (' and satisfying criteria as provided in the concurrent request.');
1082 WRITE ('=============================================================================');
1083 WRITE ('Notifications sent : '|| g_sent_total);
1084 WRITE ('Notifications not sent : '|| g_unsent_total);
1085 WRITE ('Note: Sum of sent and unsent notifications is the number of defined users,');
1086 WRITE (' which may be greater than the employees processed, if there are multiple');
1087 WRITE (' users defined for employees.');
1088 WRITE ('=============================================================================');
1089 if(p_withhold_notifn = 'Y') then
1090 WRITE('=========================++ !NOTE! ++========================================');
1091 WRITE('NOTIFICATIONS ARE WITHHELD AS THIS IS JUST A TEST RUN.');
1092 WRITE('ONLY LOG FILE IS GENERATED!');
1093 WRITE('=============================================================================');
1094 end if;
1095
1096 WRITE('=============================================================================');
1097 WRITE ('Time '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1098 WRITE('===============================END===========================================');
1099 EXCEPTION
1100 when others then
1101 l_error:=sqlerrm;
1102 hr_utility.set_location ('exception is'||l_error , 300);
1103 Wf_Core.Context('BEN_CWB_MASS_NOTIFN_PKG', 'notify',l_error);
1104 WRITE('ERROR! '||l_error);
1105 --raise;
1106 END;
1107
1108 end BEN_CWB_MASS_NOTIFN_PKG;