[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_COMMUNICATIONS
Source
1 package body ben_determine_communications as
2 /*$Header: bentmpcm.pkb 120.9 2011/05/10 02:52:54 pvelvano ship $*/
3 --
4 /*
5 Name
6 Determine Communications
7 Purpose
8 This is a batch process that creates person communication for triggers
9 that are not automatically generated by an OAB process.
10 History
11 Version Date Author Comment
12 -------+----------+----------+------------------------------------------------
13 115.0 18-DEC-98 bbulusu Created
14 115.1 31-Dec-98 bbulusu Added more functionality.
15 115.2 04-Feb-98 bbulusu Removed p_min_cmbn and p_max_cmbn. Added more
16 functionality.
17 115.3 25-Feb-99 bbulusu Changed dates in process and do_multithread to
18 varchar2.
19 115.5 15 Apr 99 mhoyes Un-datetrack of per_in_ler_f changes.
20 115.6 29 Apr 99 maagrawa - prtt_enrt_rslt now has a status code.
21 - Corrected cursors and calls to
22 ben_generate_communications.
23 - Removed parameter comp_object_selection_rule.
24 - Added procedures for comm. reports.
25 - Removed bugs.
26 115.7 09-May-99 maagrawa Commit after submitting each request.
27 115.8 10-May-99 maagrawa Corrected the header position.
28 115.9 10-May-99 jcarpent Check ('VOIDD', 'BCKDT') for pil stat cd
29 115.10 13-May-99 jcarpent Fixed ben_prtt_enrt_actn query to handle arc.
30 115.11 14-May-99 maagrawa Changed chr() with fnd_global.local_chr()
31 115.12 09-Jul-99 jcarpent Added checks for backed out pil
32 115.13 20-JUL-99 Gperry genutils -> benutils package rename.
33 115.14 27-JUL-1999 mhoyes - Changed g_report_rec to ref ben_type.
34 115.15 30-JUL-1999 mhoyes - Changed g_batch_proc_rec to ref ben_type.
35 115.16 10-SEP-1999 maagrawa -For emerging event, trap the date the life
36 event occurs.
37 -For emerging event, restrict the process to
38 comp. objects where the derived factor in
39 question is attached when the parameter
40 use_fctr_to_sel_flag is ON.
41 -Added 3 parameters comp_selection_rule,
42 los_det_to_use_cd, use_fctr_to_sel_flag.
43 115.17 06-Oct-1999 tguy added call to dt_fndate
44 115.18 11-Oct-1999 maagrawa los_dt_to_use_cd supported to get the start
45 date. Support available for DOH and ASD only.
46 115.19 13-Oct-1999 stee Change elig_enrol_cd to not be required for
47 mss mailing unless a comp object is specified.
48 115.20 13-Oct-1999 maagrawa Ordered the parameter list in procedure process
49 as in seeded concurrent procedure.
50 115.21 19-Oct-1999 maagrawa Moved function get_message_name to benutils.
51 115.22 09-Feb-2000 maagrawa Generate enrollment reminder letter when the
52 effective date is within the enrollment
53 window (1187184, 1183934).
54 Pass the ler_id of the active life event to
55 the communications process for enrollment
56 reminder letters (1187482).
57 115.23 16-Feb-2000 maagrawa Create emerging event comm. for dependents also
58 when they cross age boundaries.(1198557).
59 115.24 23-Feb-2000 maagrawa Forgot to close c_dpnt_enrt in previous version.
60 115.25 02-Mar-2000 maagrawa In standard_header procedure use application_id
61 (805) also to get conc.program name.(1167925)
62 115.26 13-Mar-2000 maagrawa Removed effective_start_date check from
63 procedure chk_per_cm.
64 115.27 04-Apr-2000 mmogel Added tokens to the message calls so that
65 they are more meaningful to the user
66 115.28 11-Apr-2000 maagrawa Use the global variable g_comm_generated to
67 identify communications generated in the
68 procedure chk_per_cm.(4507,4859,4883)
69 115.29 08-May-2000 maagrawa Added parameter p_status in procedure
70 standard_header.
71 115.30 17-Jul-2000 gperry Fixed WWBUG 1351039.
72 Dependent communications now created correctly.
73 Added in check for person_type_id parameter
74 and commented out verify_person_type check.
75 115.31 05-Sep-2000 pbodla - Bug 5422 : Allow different enrollment periods
76 for programs for scheduled a enrollment.
77 115.32 07-Dec-2000 rchase - Bug 1518211. p_dob is in/out now for
78 determine_age calls.
79 115.33 11-Jun-2002 pabodla - Added dbdrv command
80
81 115.34 14-Jun-2002 pabodla Do not select the contingent worker
82 assignment when assignment data is
83 fetched.
84 115.35 18-Jun-2002 ikasire Bug 2394141 NLS Fix
85 115.36 27-Sep-2002 rpgupta Fixed bug 2595834
86 Made changes to standard_header procedure
87 to take a substring of 80 characters
88 115.38 11-dec-2002 hmani NoCopy changes
89 115.39 30-dec-2003 mmudigon Bug 3232205. Modified cursors on
90 ben_person_actions to drive by
91 benefit_action_id
92 115.40 17-Sep-2004 pabodla iRec - c_pil_enrtrmdr : modified not to consider
93 GSP/irec/comp events.
94 115.41 27-Dec-2004 tjesumic person_type_id is validated for the communications
95 115.42 30-Jun-2006 swjain Passing ler_id in call to chk_person_selection
96 115.43 01-Aug-2006 swjain Bug 5435002 - Updated cursor c_per_actnrmdr, c_per_emrgevt
97 c_pil_enrtrmdr, c_per_mssmlg in procedure create_actnrmdr_ranges,
98 create_emrgevt_ranges, create_enrtrmdr_ranges, create_mssmlg_ranges
99 respectively.
100 115.44 07-Aug-2006 rtagarra Bug#5444208 Modified the cursor c_per_mssmlg to pick up the person when the assignment
101 is active on the effective date.
102 115.45 07-Aug-2006 gsehgal Bug 5446127 Changed the location description to location code
103 115.46 20-apr-2007 nhunur Bug 5942699 Perf fixes with logging and sql changes
104 115.47 20-apr-2007 nhunur Bug 6008383 Perf fixes with mass mailing cursors
105 115.48 06-Apr-2009 velvanop Bug8364821 - Communication is sent to the Employee and not to the Spouse when
106 Communication is triggered on the Spouse.Spouse is also an Employee.
107 115.49 10-May-2011 velvanop Bug 12414987: To check whether Participant is enrolled in a comp object,
108 check the enrollment based on cvg dates and not as effective dates
109 --------------------------------------------------------------------------------------------------
110 */
111 --
112 -- GLOBAL VARIABLES
113 --
114 g_package varchar2(80) := 'ben_determine_communications';
115 --
116 -- Process Information
117 --
118 type g_processes_table is table of number index by binary_integer;
119 g_processes_rec g_processes_table;
120 --
121 -- Caching variables and their types.
122 --
123 type g_cache_pl_fctr_rec_type is record(pl_id number,
124 belongs_to varchar2(1));
125 type g_cache_plip_fctr_rec_type is record(plip_id number,
126 belongs_to varchar2(1));
127 type g_cache_pl_fctr_table is table of g_cache_pl_fctr_rec_type index
128 by binary_integer;
129 type g_cache_plip_fctr_table is table of g_cache_plip_fctr_rec_type index
130 by binary_integer;
131 g_cache_pl_fctr_rec g_cache_pl_fctr_table;
132 g_cache_plip_fctr_rec g_cache_plip_fctr_table;
133 g_cache_last_pl_fctr_rec g_cache_pl_fctr_rec_type;
134 g_cache_last_plip_fctr_rec g_cache_plip_fctr_rec_type;
135 --
136 g_hash_key number := ben_hash_utility.get_hash_key;
137 g_hash_jump number := ben_hash_utility.get_hash_jump;
138 --
139 g_num_processes number;
140 g_threads number;
141 g_chunk_size number;
142 g_max_errors number;
143 g_num_ranges number;
144 --
145 g_per_slctd number := 0;
146 g_person_actn_cnt number := 0;
147 g_person_cnt number := 0;
148 g_error_person_cnt number := 0;
149 g_current_thread number := 0;
150 --
151 g_rec ben_type.g_report_rec;
152 g_proc_rec ben_type.g_batch_proc_rec;
153 g_action_rec ben_type.g_batch_action_rec;
154 g_strt_tm_numeric number;
155 g_end_tm_numeric number;
156 --
157 -- Global structure to hold the parameters that are passed into the master
158 -- process
159 --
160 type g_parm_list is record
161 (benefit_action_id number(15)
162 ,validate_flag varchar2(30)
163 ,effective_date date
164 ,business_group_id number(15)
165 ,mode_cd varchar2(30)
166 ,cm_trgr_typ_cd varchar2(30)
167 ,cm_typ_id number(15)
168 ,person_id number(15)
169 ,person_type_id number(15)
170 ,person_sel_rl number(15)
171 ,organization_id number(15)
172 ,location_id number(15)
173 ,ler_id number(15)
174 ,pgm_id number(15)
175 ,pl_nip_id number(15)
176 ,plan_in_pgm_flag varchar2(30)
177 ,comp_selection_rl number(15)
178 ,actn_typ_id number(15)
179 ,elig_enrol_cd varchar2(30)
180 ,use_fctr_to_sel_flag varchar2(30)
181 ,age_fctr_id number(15)
182 ,min_age number(15)
183 ,max_age number(15)
184 ,los_fctr_id number(15)
185 ,min_los number(15)
186 ,max_los number(15)
187 ,los_det_to_use_cd varchar2(30)
188 ,cmbn_age_los_fctr_id number(15)
189 ,date_from date
190 -- PB : 5422 :
191 --
192 ,lf_evt_ocrd_dt date
193 -- ,popl_enrt_typ_cycl_id number(15)
194 ,audit_log_flag varchar2(30));
195 --
196 g_parm g_parm_list;
197 --
198 --
199 -- ----------------------------------------------------------------------------
200 -- --------------------------< seconds_to_hours >------------------------------
201 -- ----------------------------------------------------------------------------
202 --
203 function seconds_to_hours(p_seconds in number) return varchar2 is
204 --
205 l_hours varchar2(30);
206 l_minutes varchar2(30);
207 l_seconds varchar2(30);
208 --
209 l_proc varchar2(80) := g_package || '.seconds_to_hours';
210 --
211 begin
212 --
213 hr_utility.set_location('Entering : ' || l_proc, 10);
214 --
215 l_hours := to_char(floor(p_seconds/3600));
216 l_minutes := to_char(floor(mod(p_seconds, 3600)/60));
217 l_seconds := to_char(mod(p_seconds, 60));
218 --
219 if length(l_hours) = 1 then
220 l_hours := '0' || l_hours;
221 end if;
222 --
223 if length(l_minutes) = 1 then
224 l_minutes := '0' || l_minutes;
225 end if;
226 --
227 if length(l_seconds) = 1 then
228 l_seconds := '0' || l_seconds;
229 end if;
230 --
231 hr_utility.set_location('Leaving : ' || l_proc, 10);
232 --
233 return l_hours || 'h : ' || l_minutes || 'm : ' || l_seconds || 's';
234 --
235 end seconds_to_hours;
236 --
237 -- ----------------------------------------------------------------------------
238 -- -----------------------< initialize_globals >-------------------------------
239 -- ----------------------------------------------------------------------------
240 --
241 procedure initialize_globals is
242 begin
243 --
244 g_processes_rec.delete;
245 --
246 benutils.g_report_table_object.delete;
247 benutils.g_batch_action_table_object.delete;
248 benutils.g_batch_proc_table_object.delete;
249 benutils.g_batch_commu_table_object.delete;
250 --
251 g_num_processes := 0;
252 g_threads := 0;
253 g_chunk_size := 0;
254 g_max_errors := 0;
255 g_num_ranges := 0;
256 --
257 g_per_slctd := 0;
258 g_person_actn_cnt := 0;
259 g_person_cnt := 0;
260 g_error_person_cnt := 0;
261 g_current_thread := 0;
262 --
263 end initialize_globals;
264 --
265 -- ----------------------------------------------------------------------------
266 -- -----------------------< submit_all_reports >-------------------------------
267 -- ----------------------------------------------------------------------------
268 --
269 procedure submit_all_reports is
270 l_proc varchar2(80) := g_package||'.submit_all_reports';
271 l_actn varchar2(80);
272 l_request_id number;
273 begin
274 --
275 hr_utility.set_location ('Entering '||l_proc,05);
276 --
277 -- Fire off the following Reports for BENTMPCM run.
278 -- * Audit Log
279 -- * Activity Summary
280 -- * Error by Error Type
281 -- * Error by Person
282 --
283 if (g_parm.audit_log_flag = 'Y') then
284 --
285 ben_batch_utils.batch_report
286 (p_concurrent_request_id => fnd_global.conc_request_id
287 ,p_program_name => 'BENCMAUD'
288 ,p_request_id => l_request_id);
289 --
290 end if;
291 --
292 if fnd_global.conc_request_id <> - 1 then
293 ben_batch_utils.batch_report
294 (p_concurrent_request_id => fnd_global.conc_request_id
295 ,p_program_name => 'BENCMSUM'
296 ,p_request_id => l_request_id);
297 --
298 ben_batch_utils.batch_report
299 (p_concurrent_request_id => fnd_global.conc_request_id
300 ,p_program_name => 'BENCMEPE'
301 ,p_request_id => l_request_id);
302 --
303 ben_batch_utils.batch_report
304 (p_concurrent_request_id => fnd_global.conc_request_id
305 ,p_program_name => 'BENCMETY'
306 ,p_request_id => l_request_id);
307 end if;
308 --
309 exception
310 when others then
311 raise;
312 end submit_all_reports;
313 --
314 -- ----------------------------------------------------------------------------
315 -- -------------------< check_all_slaves_finished >----------------------------
316 -- ----------------------------------------------------------------------------
317 --
318 procedure check_all_slaves_finished is
319 --
320 l_proc varchar2(80) := g_package || '.check_all_slaves_finished';
321 --
322 l_no_slaves boolean := true;
323 l_dummy varchar2(1);
324 l_master varchar2(1) := 'N';
325 --
326 cursor c_slaves(v_request_id number) is
327 select null
328 from fnd_concurrent_requests fnd
329 where fnd.phase_code <> 'C'
330 and fnd.request_id = v_request_id;
331 --
332 cursor c_master is
333 select 'Y'
334 from ben_benefit_actions bft
335 where bft.benefit_action_id = g_parm.benefit_action_id
336 and bft.request_id = fnd_global.conc_request_id;
337 --
338 cursor c_person_actions(p_status_cd varchar2) is
339 select count(*)
340 from ben_person_actions pac
341 where pac.benefit_action_id = g_parm.benefit_action_id
342 and pac.action_status_cd = nvl(p_status_cd,pac.action_status_cd);
343 --
344 begin
345 --
346 hr_utility.set_location('Entering : ' || l_proc, 10);
347 --
348 -- Check if process is master
349 --
350 open c_master;
351 fetch c_master into l_master;
352 close c_master;
353 --
354 if g_num_processes <> 0 and l_master = 'Y' then
355 --
356 while l_no_slaves loop
357 --
358 l_no_slaves := false;
359 --
360 for l_count in 1..g_num_processes loop
361 --
362 open c_slaves(g_processes_rec(l_count));
363 --
364 fetch c_slaves into l_dummy;
365 if c_slaves%found then
366 --
367 -- Slave is still running
368 --
369 l_no_slaves := true;
370 close c_slaves;
371 exit;
372 --
373 end if;
374 --
375 close c_slaves;
376 --
377 end loop;
378 --
379 -- To prevent over polling of fnd_concurrent_requests
380 -- sleep for a while.
381 --
382 If (l_no_slaves) then
383 dbms_lock.sleep(4);
384 End if;
385 --
386 end loop;
387 --
388 end if;
389 --
390 -- Log process information
391 -- This is master specific only
392 --
393 if l_master = 'Y' then
394 --
395 g_proc_rec.end_dt := sysdate;
396 g_proc_rec.end_tm := to_char(sysdate,'HH24:MI:SS');
397 g_end_tm_numeric := dbms_utility.get_time;
398 g_proc_rec.elpsd_tm := seconds_to_hours
399 ((g_end_tm_numeric - g_strt_tm_numeric)/100);
400 --
401 open c_person_actions(null);
402 fetch c_person_actions into g_proc_rec.per_slctd;
403 close c_person_actions;
404 --
405 open c_person_actions('E');
406 fetch c_person_actions into g_proc_rec.per_err;
407 close c_person_actions;
408 --
409 open c_person_actions('P');
410 fetch c_person_actions into g_proc_rec.per_proc_succ;
411 close c_person_actions;
412 --
413 open c_person_actions('U');
414 fetch c_person_actions into g_proc_rec.per_unproc;
415 close c_person_actions;
416 --
417 g_proc_rec.per_proc := nvl(g_proc_rec.per_proc_succ,0)+
418 nvl(g_proc_rec.per_err,0);
419 --
420 benutils.write(p_rec => g_proc_rec);
421 --
422 end if;
423 --
424 benutils.write_table_and_file(p_table => true
425 ,p_file => false);
426 commit;
427 --
428 if l_master = 'Y' then
429 --
430 submit_all_reports;
431 commit;
432 --
433 end if;
434 --
435 hr_utility.set_location('Leaving : ' || l_proc, 10);
436 --
437 end check_all_slaves_finished;
438 --
439 -- ----------------------------------------------------------------------------
440 -- -------------------------< person_error_cnt >-----------------------------------
441 -- ----------------------------------------------------------------------------
442 --
443 procedure person_error_cnt is
444 --
445 error_limit exception;
446 --
447 l_proc varchar2(80) := g_package || '.person_error_cnt';
448 --
449 begin
450 --
451 hr_utility.set_location('Entering : ' || l_proc, 10);
452 --
453 -- Increment the error count
454 --
455 g_error_person_cnt := g_error_person_cnt + 1;
456 --
457 -- Check if the error count excedes the maximum allowed errors
458 --
459 if g_error_person_cnt > g_max_errors then
460 raise error_limit;
461 end if;
462 --
463 hr_utility.set_location('Leaving : ' || l_proc, 10);
464 --
465 exception
466 --
467 when error_limit then
468 fnd_message.set_name('BEN', 'BEN_92163_BENTMPCM_ERR_LMT');
469 fnd_message.set_token('PROC',l_proc);
470 benutils.write(p_text => fnd_message.get);
471 raise;
472 --
473 end person_error_cnt;
474 --
475 -- ----------------------------------------------------------------------------
476 -- -----------------------< write_logfile >------------------------------------
477 -- ----------------------------------------------------------------------------
478 --
479 procedure write_logfile is
480 --
481 l_proc varchar2(80) := g_package || '.write_logfile';
482 --
483 begin
484 --
485 hr_utility.set_location('Entering : ' || l_proc, 10);
486 --
487 benutils.write(p_text => benutils.g_banner_minus);
488 benutils.write(p_text => 'Batch Process Statistical Information');
489 benutils.write(p_text => benutils.g_banner_minus);
490 benutils.write(p_text => 'People processed : ' || g_person_cnt);
491 benutils.write(p_text => 'People errored : ' || g_error_person_cnt);
492 benutils.write(p_text => benutils.g_banner_minus);
493 --
494 benutils.write_table_and_file(p_table => false
495 ,p_file => true);
496 commit;
497 --
498 hr_utility.set_location('Leaving : ' || l_proc, 10);
499 --
500 exception
501 --
502 when others then
503 --
504 benutils.write(p_text => sqlerrm);
505 fnd_message.set_name('BEN','BEN_91663_BENMNGLE_LOGGING');
506 fnd_message.set_token('PROC',l_proc);
507 benutils.write(p_text => fnd_message.get);
508 fnd_message.raise_error;
509 --
510 end write_logfile;
511 --
512 -- ----------------------------------------------------------------------------
513 -- -----------------------< print_parameters >---------------------------------
514 -- ----------------------------------------------------------------------------
515 --
516 procedure print_parameters is
517 --
518 l_proc varchar2(80) := g_package || '.print_parameters';
519 --
520 l_rec benutils.g_batch_param_rec;
521 begin
522 --
523 hr_utility.set_location('Entering : ' || l_proc, 10);
524 --
525 if fnd_global.conc_request_id = -1 then
526 return;
527 end if;
528 --
529 -- added to prevent logging
530 --
531 benutils.get_batch_parameters
532 (p_benefit_action_id => g_parm.benefit_action_id,
533 p_rec => l_rec);
534 --
535 fnd_file.put_line(which => fnd_file.log
536 ,buff => 'Runtime Parameters');
537 --
538 fnd_file.put_line(which => fnd_file.log
539 ,buff => '------------------');
540 --
541 fnd_file.put_line(which => fnd_file.log
542 ,buff => 'Benefit Action ID : '||
543 benutils.iftrue
544 (p_expression => g_parm.benefit_action_id is null
545 ,p_true => 'NONE'
546 ,p_false => g_parm.benefit_action_id));
547 --
548 fnd_file.put_line(which => fnd_file.log
549 ,buff => 'Validation Mode : '||
550 hr_general.decode_lookup('YES_NO',g_parm.validate_flag));
551 --
552 fnd_file.put_line(which => fnd_file.log
553 ,buff => 'Effective Date : '||
554 to_char(g_parm.effective_date,'DD-MON-YYYY'));
555 --
556 fnd_file.put_line(which => fnd_file.log
557 ,buff => 'Business Group ID : '||
558 g_parm.business_group_id);
559 --
560 fnd_file.put_line(which => fnd_file.log
561 ,buff => 'Run Mode : ' ||
562 hr_general.decode_lookup('BEN_BENTMPCM_MD',g_parm.mode_cd));
563 --
564 fnd_file.put_line(which => fnd_file.log
565 ,buff => 'Comm Trigger Type Code : ' ||
566 benutils.iftrue
567 (p_expression => g_parm.cm_trgr_typ_cd is null
568 ,p_true => 'All'
569 ,p_false => g_parm.cm_trgr_typ_cd));
570 --
571 fnd_file.put_line(which => fnd_file.log
572 ,buff => 'Communication Type ID : ' ||
573 benutils.iftrue
574 (p_expression => g_parm.cm_typ_id is null
575 ,p_true => 'All'
576 ,p_false => g_parm.cm_typ_id));
577 --
578 fnd_file.put_line(which => fnd_file.log
579 ,buff => 'Person ID : ' ||
580 benutils.iftrue
581 (p_expression => g_parm.person_id is null
582 ,p_true => 'All'
583 ,p_false => g_parm.person_id));
584 --
585 fnd_file.put_line(which => fnd_file.log
586 ,buff => 'Person Type ID : ' ||
587 benutils.iftrue
588 (p_expression => g_parm.person_type_id is null
589 ,p_true => 'All'
590 ,p_false => g_parm.person_type_id));
591 --
592 fnd_file.put_line(which => fnd_file.log
593 ,buff => 'Person Selection Rule : ' ||
594 benutils.iftrue
595 (p_expression => g_parm.person_sel_rl is null
596 ,p_true => 'All'
597 ,p_false => g_parm.person_sel_rl));
598 --
599 fnd_file.put_line(which => fnd_file.log
600 ,buff => 'Organization ID : ' ||
601 benutils.iftrue
602 (p_expression => g_parm.organization_id is null
603 ,p_true => 'All'
604 ,p_false => g_parm.organization_id));
605 --
606 fnd_file.put_line(which => fnd_file.log
607 ,buff => 'Location ID : ' ||
608 benutils.iftrue
609 (p_expression => g_parm.location_id is null
610 ,p_true => 'All'
611 ,p_false => g_parm.location_id));
612 --
613 fnd_file.put_line(which => fnd_file.log
614 ,buff => 'Life Event Reason ID : ' ||
615 benutils.iftrue
616 (p_expression => g_parm.ler_id is null
617 ,p_true => 'All'
618 ,p_false => g_parm.ler_id));
619 --
620 fnd_file.put_line(which => fnd_file.log
621 ,buff => 'Program ID : '||
622 benutils.iftrue
623 (p_expression => g_parm.pgm_id is null
624 ,p_true => 'All'
625 ,p_false => g_parm.pgm_id));
626 --
627 fnd_file.put_line(which => fnd_file.log,
628 buff => 'Plan ID : '||
629 benutils.iftrue
630 (p_expression => g_parm.pl_nip_id is null
631 ,p_true => 'All'
632 ,p_false => g_parm.pl_nip_id));
633 --
634 fnd_file.put_line(which => fnd_file.log,
635 buff => 'Is plan in program? : '||
636 g_parm.plan_in_pgm_flag);
637 --
638 fnd_file.put_line(which => fnd_file.log
639 ,buff => 'Action Type ID : ' ||
640 benutils.iftrue
641 (p_expression => g_parm.actn_typ_id is null
642 ,p_true => 'All'
643 ,p_false => g_parm.actn_typ_id));
644 --
645 fnd_file.put_line(which => fnd_file.log
646 ,buff => 'Elig Enrol Code : ' ||
647 benutils.iftrue
648 (p_expression => g_parm.elig_enrol_cd is null
649 ,p_true => 'NONE'
650 ,p_false => g_parm.elig_enrol_cd));
651 --
652 fnd_file.put_line(which => fnd_file.log
653 ,buff => 'Age Factor ID : ' ||
654 benutils.iftrue
655 (p_expression => g_parm.age_fctr_id is null
656 ,p_true => 'NONE'
657 ,p_false => g_parm.age_fctr_id));
658 --
659 fnd_file.put_line(which => fnd_file.log
660 ,buff => 'Minimum Age : ' ||
661 benutils.iftrue
662 (p_expression => g_parm.min_age is null
663 ,p_true => 'NONE'
664 ,p_false => g_parm.min_age));
665 --
666 fnd_file.put_line(which => fnd_file.log
667 ,buff => 'Maximum Age : ' ||
668 benutils.iftrue
669 (p_expression => g_parm.max_age is null
670 ,p_true => 'NONE'
671 ,p_false => g_parm.max_age));
672 --
673 fnd_file.put_line(which => fnd_file.log
674 ,buff => 'Length of Srvce Factor Id : ' ||
675 benutils.iftrue
676 (p_expression => g_parm.los_fctr_id is null
677 ,p_true => 'NONE'
678 ,p_false => g_parm.los_fctr_id));
679 --
680 fnd_file.put_line(which => fnd_file.log
681 ,buff => 'Minimum LOS : ' ||
682 benutils.iftrue
683 (p_expression => g_parm.min_los is null
684 ,p_true => 'NONE'
685 ,p_false => g_parm.min_los));
686 --
687 fnd_file.put_line(which => fnd_file.log
688 ,buff => 'Maximum LOS : ' ||
689 benutils.iftrue
690 (p_expression => g_parm.max_los is null
691 ,p_true => 'NONE'
692 ,p_false => g_parm.max_los));
693 --
694 fnd_file.put_line(which => fnd_file.log
695 ,buff => 'Cmbn Age and LOS Factor Id : ' ||
696 benutils.iftrue
697 (p_expression => g_parm.cmbn_age_los_fctr_id is null
698 ,p_true => 'NONE'
699 ,p_false => g_parm.cmbn_age_los_fctr_id));
700 --
701 fnd_file.put_line(which => fnd_file.log
702 ,buff => 'Date From : ' ||
703 benutils.iftrue
704 (p_expression => g_parm.date_from is null
705 ,p_true => 'NONE'
706 ,p_false => g_parm.date_from));
707 --
708 /*
709 fnd_file.put_line(which => fnd_file.log,
710 buff => 'Enrollment Period : '||
711 benutils.iftrue
712 (p_expression => g_parm.popl_enrt_typ_cycl_id is null
713 ,p_true => 'All'
714 ,p_false => g_parm.popl_enrt_typ_cycl_id));
715 */
716 --
717 fnd_file.put_line(which => fnd_file.log
718 ,buff => 'Audit Log Flag : ' ||
719 hr_general.decode_lookup('YES_NO',g_parm.audit_log_flag));
720 --
721 hr_utility.set_location('Leaving : ' || l_proc, 10);
722 --
723 end print_parameters;
724 --
725 procedure set_object(p_rec in g_cache_pl_fctr_rec_type) is
726 --
727 l_proc varchar2(80) := g_package||'set_object pl_fctr';
728 l_index binary_integer;
729 l_not_hash_found boolean;
730 --
731 begin
732 --
733 hr_utility.set_location('Entering '||l_proc,10);
734 --
735 -- 1) get hash index
736 -- 2) If hash index is not used use hash index
737 -- 3) If hash index is used and correct then do nothing
738 -- 4) If hash index is used and not correct then try next hash index
739 --
740 -- Get hashed index value
741 --
742 l_index := mod(p_rec.pl_id,g_hash_key);
743 --
744 if not g_cache_pl_fctr_rec.exists(l_index) then
745 --
746 -- Lets store the hash value in this index
747 --
748 g_cache_pl_fctr_rec(l_index) := p_rec;
749 --
750 else
751 --
752 -- If it does exist check if its the right one
753 --
754 if g_cache_pl_fctr_rec(l_index).pl_id <> p_rec.pl_id then
755 --
756 -- Loop through the hash using the jump routine to check further
757 -- indexes
758 --
759 l_not_hash_found := false;
760 --
761 while not l_not_hash_found loop
762 --
763 l_index := l_index+g_hash_jump;
764 --
765 -- Check if the hash index exists, if not we can use it
766 --
767 if not g_cache_pl_fctr_rec.exists(l_index) then
768 --
769 -- Lets store the hash value in the index
770 --
771 g_cache_pl_fctr_rec(l_index) := p_rec;
772 l_not_hash_found := true;
773 --
774 else
775 --
776 -- Make sure the index is the correct one
777 --
778 if g_cache_pl_fctr_rec(l_index).pl_id = p_rec.pl_id then
779 --
780 -- We have a match so the hashed value has been stored before
781 --
782 l_not_hash_found := true;
783 --
784 end if;
785 --
786 end if;
787 --
788 end loop;
789 --
790 end if;
791 --
792 end if;
793 --
794 hr_utility.set_location('Leaving '||l_proc,10);
795 --
796 end set_object;
797
798
799 procedure set_object(p_rec in g_cache_plip_fctr_rec_type) is
800 --
801 l_proc varchar2(80) := g_package||'set_object plip_fctr';
802 l_index binary_integer;
803 l_not_hash_found boolean;
804 --
805 begin
806 --
807 hr_utility.set_location('Entering '||l_proc,10);
808 --
809 -- 1) get hash index
810 -- 2) If hash index is not used use hash index
811 -- 3) If hash index is used and correct then do nothing
812 -- 4) If hash index is used and not correct then try next hash index
813 --
814 -- Get hashed index value
815 --
816 l_index := mod(p_rec.plip_id,g_hash_key);
817 --
818 if not g_cache_plip_fctr_rec.exists(l_index) then
819 --
820 -- Lets store the hash value in this index
821 --
822 g_cache_plip_fctr_rec(l_index) := p_rec;
823 --
824 else
825 --
826 -- If it does exist check if its the right one
827 --
828 if g_cache_plip_fctr_rec(l_index).plip_id <> p_rec.plip_id then
829 --
830 -- Loop through the hash using the jump routine to check further
831 -- indexes
832 --
833 l_not_hash_found := false;
834 --
835 while not l_not_hash_found loop
836 --
837 l_index := l_index+g_hash_jump;
838 --
839 -- Check if the hash index exists, if not we can use it
840 --
841 if not g_cache_plip_fctr_rec.exists(l_index) then
842 --
843 -- Lets store the hash value in the index
844 --
845 g_cache_plip_fctr_rec(l_index) := p_rec;
846 l_not_hash_found := true;
847 --
848 else
849 --
850 -- Make sure the index is the correct one
851 --
852 if g_cache_plip_fctr_rec(l_index).plip_id = p_rec.plip_id then
853 --
854 -- We have a match so the hashed value has been stored before
855 --
856 l_not_hash_found := true;
857 --
858 end if;
859 --
860 end if;
861 --
862 end loop;
863 --
864 end if;
865 --
866 end if;
867 --
868 hr_utility.set_location('Leaving '||l_proc,10);
869 --
870 end set_object;
871 --
872 --
873 procedure set_fctr_object(
874 p_pl_id in number,
875 p_pgm_id in number default null,
876 p_plip_id in number default null,
877 p_ptip_id in number default null,
878 p_business_group_id in number,
879 p_effective_date in date,
880 p_pl_fctr_rec out nocopy g_cache_pl_fctr_rec_type,
881 p_plip_fctr_rec out nocopy g_cache_plip_fctr_rec_type
882 ) is
883 --
884 l_proc varchar2(80) := g_package||'set_fctr_object';
885 --
886 cursor c_los_fctr is
887 select 'Y'
888 from ben_prtn_elig_f epa,
889 ben_prtn_elig_prfl_f cep,
890 ben_eligy_prfl_f elp,
891 ben_elig_los_prte_f els
892 where (epa.pl_id = p_pl_id or
893 epa.pgm_id = p_pgm_id or
894 epa.plip_id = p_plip_id or
895 epa.ptip_id = p_ptip_id )
896 and epa.prtn_elig_id = cep.prtn_elig_id
897 and cep.eligy_prfl_id = elp.eligy_prfl_id
898 and elp.eligy_prfl_id = els.eligy_prfl_id
899 and els.los_fctr_id = g_parm.los_fctr_id
900 and g_parm.effective_date between
901 epa.effective_start_date and epa.effective_end_date
902 and g_parm.effective_date between
903 cep.effective_start_date and cep.effective_end_date
904 and g_parm.effective_date between
905 elp.effective_start_date and elp.effective_end_date
906 and g_parm.effective_date between
907 els.effective_start_date and els.effective_end_date;
908 --
909 cursor c_age_fctr is
910 select 'Y'
911 from ben_prtn_elig_f epa,
912 ben_prtn_elig_prfl_f cep,
913 ben_eligy_prfl_f elp,
914 ben_elig_age_prte_f eap
915 where (epa.pl_id = p_pl_id or
916 epa.pgm_id = p_pgm_id or
917 epa.plip_id = p_plip_id or
918 epa.ptip_id = p_ptip_id )
919 and epa.prtn_elig_id = cep.prtn_elig_id
920 and cep.eligy_prfl_id = elp.eligy_prfl_id
921 and elp.eligy_prfl_id = eap.eligy_prfl_id
922 and eap.age_fctr_id = g_parm.age_fctr_id
923 and g_parm.effective_date between
924 epa.effective_start_date and epa.effective_end_date
925 and g_parm.effective_date between
926 cep.effective_start_date and cep.effective_end_date
927 and g_parm.effective_date between
928 elp.effective_start_date and elp.effective_end_date
929 and g_parm.effective_date between
930 eap.effective_start_date and eap.effective_end_date;
931 --
932 cursor c_cmbn_age_los_fctr is
933 select 'Y'
934 from ben_prtn_elig_f epa,
935 ben_prtn_elig_prfl_f cep,
936 ben_eligy_prfl_f elp,
937 ben_elig_cmbn_age_los_prte_f ecp
938 where (epa.pl_id = p_pl_id or
939 epa.pgm_id = p_pgm_id or
940 epa.plip_id = p_plip_id or
941 epa.ptip_id = p_ptip_id )
942 and epa.prtn_elig_id = cep.prtn_elig_id
943 and cep.eligy_prfl_id = elp.eligy_prfl_id
944 and elp.eligy_prfl_id = ecp.eligy_prfl_id
945 and ecp.cmbn_age_los_fctr_id = g_parm.cmbn_age_los_fctr_id
946 and g_parm.effective_date between
947 epa.effective_start_date and epa.effective_end_date
948 and g_parm.effective_date between
949 cep.effective_start_date and cep.effective_end_date
950 and g_parm.effective_date between
951 elp.effective_start_date and elp.effective_end_date
952 and g_parm.effective_date between
953 ecp.effective_start_date and ecp.effective_end_date;
954 --
955 l_belongs varchar2(1);
956 l_pl_fctr_rec g_cache_pl_fctr_rec_type;
957 l_plip_fctr_rec g_cache_plip_fctr_rec_type;
958 --
959 begin
960 --
961 hr_utility.set_location('Entering '||l_proc,10);
962 --
963 -- 1) Get record from database.
964 -- 2) If record not found then raise error.
965 -- 3) Pass record to set_object routine.
966 --
967 if g_parm.los_fctr_id is not null then
968 --
969 open c_los_fctr;
970 --
971 fetch c_los_fctr into l_belongs;
972 if c_los_fctr%notfound then
973 --
974 l_pl_fctr_rec.belongs_to := 'N';
975 l_plip_fctr_rec.belongs_to := 'N';
976 --
977 else
978 --
979 l_pl_fctr_rec.belongs_to := 'Y';
980 l_plip_fctr_rec.belongs_to := 'Y';
981 --
982 end if;
983 --
984 close c_los_fctr;
985 --
986 elsif g_parm.age_fctr_id is not null then
987 --
988 open c_age_fctr;
989 --
990 fetch c_age_fctr into l_belongs;
991 if c_age_fctr%notfound then
992 --
993 l_pl_fctr_rec.belongs_to := 'N';
994 l_plip_fctr_rec.belongs_to := 'N';
995 --
996 else
997 --
998 l_pl_fctr_rec.belongs_to := 'Y';
999 l_plip_fctr_rec.belongs_to := 'Y';
1000 --
1001 end if;
1002 --
1003 close c_age_fctr;
1004 --
1005 elsif g_parm.cmbn_age_los_fctr_id is not null then
1006 --
1007 open c_cmbn_age_los_fctr;
1008 --
1009 fetch c_cmbn_age_los_fctr into l_belongs;
1010 if c_cmbn_age_los_fctr%notfound then
1011 --
1012 l_pl_fctr_rec.belongs_to := 'N';
1013 l_plip_fctr_rec.belongs_to := 'N';
1014 --
1015 else
1016 --
1017 l_pl_fctr_rec.belongs_to := 'Y';
1018 l_plip_fctr_rec.belongs_to := 'Y';
1019 --
1020 end if;
1021 --
1022 close c_cmbn_age_los_fctr;
1023 --
1024 end if;
1025 --
1026 l_pl_fctr_rec.pl_id := p_pl_id;
1027 l_plip_fctr_rec.plip_id := p_plip_id;
1028 --
1029 if p_plip_id is not null then
1030 set_object(p_rec => l_plip_fctr_rec);
1031 else
1032 set_object(p_rec => l_pl_fctr_rec);
1033 end if;
1034 --
1035 p_pl_fctr_rec := l_pl_fctr_rec;
1036 p_plip_fctr_rec := l_plip_fctr_rec;
1037 --
1038 hr_utility.set_location('Leaving '||l_proc,10);
1039 --
1040 end set_fctr_object;
1041
1042 --
1043 procedure get_object(p_pl_id in number,
1044 p_rec out nocopy g_cache_pl_fctr_rec_type) is
1045 --
1046 l_proc varchar2(80) := g_package||'get_object pl_fctr';
1047 l_index binary_integer;
1048 l_not_hash_found boolean;
1049 l_rec g_cache_pl_fctr_rec_type;
1050 l_plip_fctr_rec g_cache_plip_fctr_rec_type;
1051 --
1052 begin
1053 --
1054 hr_utility.set_location('Entering '||l_proc,10);
1055 --
1056 if g_cache_last_pl_fctr_rec.pl_id = p_pl_id then
1057 --
1058 p_rec := g_cache_last_pl_fctr_rec;
1059 return;
1060 --
1061 end if;
1062 --
1063 -- 1) Get hashed index
1064 -- 2) If hashed index is correct person_id then return person_id
1065 -- 3) If hashed index is not correct person_id then check next index
1066 -- 4) Repeat 3 until correct person_id found, if not found raise error.
1067 --
1068 -- Get hashed index value
1069 --
1070 l_index := mod(p_pl_id,g_hash_key);
1071 --
1072 if g_cache_pl_fctr_rec.exists(l_index) then
1073 --
1074 -- Lets get the hashed record.
1075 --
1076 if g_cache_pl_fctr_rec(l_index).pl_id = p_pl_id then
1077 --
1078 g_cache_last_pl_fctr_rec := g_cache_pl_fctr_rec(l_index);
1079 p_rec := g_cache_pl_fctr_rec(l_index);
1080 --
1081 else
1082 --
1083 -- We need to loop through all the hashed indexes
1084 --
1085 l_not_hash_found := false;
1086 --
1087 while not l_not_hash_found loop
1088 --
1089 l_index := l_index+g_hash_jump;
1090 --
1091 -- Check if the hash index exists, if not error
1092 --
1093 if not g_cache_pl_fctr_rec.exists(l_index) then
1094 --
1095 -- Raise an error as we are trying to retrieve a non cached object
1096 --
1097 raise no_data_found;
1098 --
1099 else
1100 --
1101 -- Make sure the index is the correct one
1102 --
1103 if g_cache_pl_fctr_rec(l_index).pl_id = p_pl_id then
1104 --
1105 -- We have a match
1106 --
1107 g_cache_last_pl_fctr_rec := g_cache_pl_fctr_rec(l_index);
1108 p_rec := g_cache_pl_fctr_rec(l_index);
1109 l_not_hash_found := true;
1110 --
1111 end if;
1112 --
1113 end if;
1114 --
1115 end loop;
1116 --
1117 end if;
1118 --
1119 else
1120 --
1121 raise no_data_found;
1122 --
1123 end if;
1124 --
1125 hr_utility.set_location('Leaving '||l_proc,10);
1126 --
1127 exception
1128 --
1129 when no_data_found then
1130 --
1131 set_fctr_object(p_pl_id => p_pl_id,
1132 p_business_group_id => g_parm.business_group_id,
1133 p_effective_date => g_parm.effective_date,
1134 p_pl_fctr_rec => l_rec,
1135 p_plip_fctr_rec => l_plip_fctr_rec);
1136 --
1137 g_cache_last_pl_fctr_rec := l_rec;
1138 p_rec := l_rec;
1139 --
1140 end get_object;
1141 --
1142 --
1143 procedure get_object(p_plip_id in number,
1144 p_pgm_id in number,
1145 p_pl_id in number,
1146 p_ptip_id in number,
1147 p_rec out nocopy g_cache_plip_fctr_rec_type) is
1148 --
1149 l_proc varchar2(80) := g_package||'get_object plip_fctr';
1150 l_index binary_integer;
1151 l_not_hash_found boolean;
1152 l_rec g_cache_plip_fctr_rec_type;
1153 l_pl_fctr_rec g_cache_pl_fctr_rec_type;
1154 --
1155 begin
1156 --
1157 hr_utility.set_location('Entering '||l_proc,10);
1158 --
1159 if g_cache_last_plip_fctr_rec.plip_id = p_plip_id then
1160 --
1161 p_rec := g_cache_last_plip_fctr_rec;
1162 return;
1163 --
1164 end if;
1165 --
1166 -- 1) Get hashed index
1167 -- 2) If hashed index is correct person_id then return person_id
1168 -- 3) If hashed index is not correct person_id then check next index
1169 -- 4) Repeat 3 until correct person_id found, if not found raise error.
1170 --
1171 -- Get hashed index value
1172 --
1173 l_index := mod(p_plip_id,g_hash_key);
1174 --
1175 if g_cache_plip_fctr_rec.exists(l_index) then
1176 --
1177 -- Lets get the hashed record.
1178 --
1179 if g_cache_plip_fctr_rec(l_index).plip_id = p_plip_id then
1180 --
1181 g_cache_last_plip_fctr_rec := g_cache_plip_fctr_rec(l_index);
1182 p_rec := g_cache_plip_fctr_rec(l_index);
1183 --
1184 else
1185 --
1186 -- We need to loop through all the hashed indexes
1187 --
1188 l_not_hash_found := false;
1189 --
1190 while not l_not_hash_found loop
1191 --
1192 l_index := l_index+g_hash_jump;
1193 --
1194 -- Check if the hash index exists, if not error
1195 --
1196 if not g_cache_plip_fctr_rec.exists(l_index) then
1197 --
1198 -- Raise an error as we are trying to retrieve a non cached object
1199 --
1200 raise no_data_found;
1201 --
1202 else
1203 --
1204 -- Make sure the index is the correct one
1205 --
1206 if g_cache_plip_fctr_rec(l_index).plip_id = p_plip_id then
1207 --
1208 -- We have a match
1209 --
1210 g_cache_last_plip_fctr_rec := g_cache_plip_fctr_rec(l_index);
1211 p_rec := g_cache_plip_fctr_rec(l_index);
1212 l_not_hash_found := true;
1213 --
1214 end if;
1215 --
1216 end if;
1217 --
1218 end loop;
1219 --
1220 end if;
1221 --
1222 else
1223 --
1224 raise no_data_found;
1225 --
1226 end if;
1227 --
1228 hr_utility.set_location('Leaving '||l_proc,10);
1229 --
1230 exception
1231 --
1232 when no_data_found then
1233 --
1234 set_fctr_object(p_plip_id => p_plip_id,
1235 p_pl_id => p_pl_id,
1236 p_pgm_id => p_pgm_id,
1237 p_ptip_id => p_ptip_id,
1238 p_business_group_id => g_parm.business_group_id,
1239 p_effective_date => g_parm.effective_date,
1240 p_plip_fctr_rec => l_rec,
1241 p_pl_fctr_rec => l_pl_fctr_rec);
1242 --
1243 g_cache_last_plip_fctr_rec := l_rec;
1244 p_rec := l_rec;
1245 --
1246 end get_object;
1247 --
1248 --
1249 function fctr_belongs_to_comp_object(
1250 p_pgm_id in number default null,
1251 p_pl_id in number default null)
1252 return boolean is
1253 --
1254 cursor c_get_pl_details is
1255 select ptip.ptip_id,
1256 plip.plip_id
1257 from ben_pl_f pl,
1258 ben_ptip_f ptip,
1259 ben_plip_f plip
1260 where pl.pl_id = p_pl_id
1261 and pl.pl_typ_id = ptip.pl_typ_id
1262 and ptip.pgm_id = p_pgm_id
1263 and plip.pl_id = pl.pl_id
1264 and plip.pgm_id = ptip.pgm_id
1265 and g_parm.effective_date between
1266 pl.effective_start_date and pl.effective_end_date
1267 and g_parm.effective_date between
1268 plip.effective_start_date and plip.effective_end_date
1269 and g_parm.effective_date between
1270 ptip.effective_start_date and ptip.effective_end_date;
1271 --
1272 l_plip_id ben_plip_f.plip_id%type := null;
1273 l_ptip_id ben_ptip_f.ptip_id%type := null;
1274 l_pl_fctr_rec g_cache_pl_fctr_rec_type;
1275 l_plip_fctr_rec g_cache_plip_fctr_rec_type;
1276 l_return boolean := false;
1277 l_proc varchar2(80) := g_package || '.fctr_belongs_to_comp_object';
1278 --
1279 begin
1280 --
1281 hr_utility.set_location('Entering '||l_proc,10);
1282 --
1283 if p_pgm_id is not null then
1284 --
1285 open c_get_pl_details;
1286 fetch c_get_pl_details into l_ptip_id, l_plip_id;
1287 if c_get_pl_details%found then
1288 get_object(p_pl_id => p_pl_id,
1289 p_pgm_id => p_pgm_id,
1290 p_plip_id => l_plip_id,
1291 p_ptip_id => l_ptip_id,
1292 p_rec => l_plip_fctr_rec);
1293 if l_plip_fctr_rec.belongs_to = 'Y' then
1294 l_return := true;
1295 end if;
1296 end if;
1297 close c_get_pl_details;
1298 --
1299 elsif p_pl_id is not null then
1300 --
1301 get_object(p_pl_id => p_pl_id,
1302 p_rec => l_pl_fctr_rec);
1303 if l_pl_fctr_rec.belongs_to = 'Y' then
1304 l_return := true;
1305 end if;
1306 --
1307 end if;
1308 --
1309 hr_utility.set_location('Leaving '||l_proc,10);
1310 --
1311 return l_return;
1312 --
1313 end fctr_belongs_to_comp_object;
1314 --
1315 -- ----------------------------------------------------------------------------
1316 -- ----------------------------< chk_per_cm >----------------------------------
1317 -- ----------------------------------------------------------------------------
1318 --
1319 procedure chk_per_cm(p_person_id in number) is
1320 --
1321 l_proc varchar2(80) := g_package || '.chk_per_cm';
1322 --
1323 begin
1324 --
1325 hr_utility.set_location('Entering : ' || l_proc, 10);
1326 --
1327 g_rec.person_id := p_person_id;
1328 --
1329 if ben_generate_communications.g_comm_generated then
1330 --
1331 g_rec.rep_typ_cd := 'COMMU';
1332 fnd_message.set_name('BEN','BEN_92182_COMM_GNRTD');
1333 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1334 g_rec.text := fnd_message.get;
1335 --
1336 else
1337 --
1338 g_rec.rep_typ_cd := 'NOCOMMU';
1339 fnd_message.set_name('BEN','BEN_92183_NO_COMM_GNRTD');
1340 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1341 g_rec.text := fnd_message.get;
1342 --
1343 end if;
1344 --
1345 benutils.write(p_rec => g_rec);
1346 --
1347 hr_utility.set_location('Leaving : ' || l_proc, 10);
1348 --
1349 end chk_per_cm;
1350 --
1351 -- ----------------------------------------------------------------------------
1352 -- ----------------------< get_main_person_id >--------------------------------
1353 -- ----------------------------------------------------------------------------
1354 --
1355 function get_main_person_id(p_dpnt_person_id in number)
1356 return number is
1357 --
1358 cursor c_per is
1359 select ppf.person_id
1360 from per_all_people_f ppf,
1361 per_contact_relationships pcr
1362 where pcr.contact_person_id = p_dpnt_person_id
1363 and pcr.business_group_id = g_parm.business_group_id
1364 and ppf.person_id = pcr.person_id
1365 and ppf.business_group_id = g_parm.business_group_id
1366 and g_parm.effective_date between ppf.effective_start_date
1367 and ppf.effective_end_date;
1368 --
1369 l_person_id number;
1370 --
1371 l_proc varchar2(80) := g_package || '.get_main_person_id';
1372 --
1373 begin
1374 --
1375 hr_utility.set_location('Entering : ' || l_proc, 10);
1376 --
1377 open c_per;
1378 fetch c_per into l_person_id;
1379 close c_per;
1380 --
1381 hr_utility.set_location('Leaving : ' || l_proc, 10);
1382 --
1383 return l_person_id;
1384 --
1385 end get_main_person_id;
1386 --
1387 -- ----------------------------------------------------------------------------
1388 -- -------------------------< get_person_type >--------------------------------
1389 -- ----------------------------------------------------------------------------
1390 --
1391 function get_person_type(p_person_type_id in number)
1392 return varchar2 is
1393 --
1394 cursor c_person_type is
1395 select ppt.system_person_type
1396 from per_person_types ppt
1397 where ppt.person_type_id = p_person_type_id
1398 and ppt.business_group_id = g_parm.business_group_id;
1399 --
1400 l_type varchar2(30) := null;
1401 l_proc varchar2(80) := g_package || '.get_person_type';
1402 --
1403 begin
1404 --
1405 hr_utility.set_location('Entering : ' || l_proc, 10);
1406 --
1407 open c_person_type;
1408 fetch c_person_type into l_type;
1409 close c_person_type;
1410 --
1411 hr_utility.set_location('Leaving : ' || l_proc, 10);
1412 --
1413 return(l_type);
1414 --
1415 exception
1416 when others then
1417 raise;
1418 --
1419 end get_person_type;
1420 --
1421 --
1422 --
1423 function verify_person_type_id(p_person_id in number,
1424 p_person_type_id in number)
1425 return boolean is
1426 --
1427 cursor c1 is
1428 select 'Y'
1429 from per_all_people_f ppf, per_person_types ppt
1430 where ppf.person_id = p_person_id
1431 and ppf.person_type_id = p_person_type_id
1432 and ppf.business_group_id = g_parm.business_group_id
1433 and g_parm.effective_date between ppf.effective_start_date
1434 and ppf.effective_end_date
1435 and ppf.person_type_id = ppt.person_type_id
1436 and ppt.business_group_id = g_parm.business_group_id
1437 and ppt.active_flag = 'Y';
1438 --
1439 l_success varchar2(30) := null;
1440 --
1441 begin
1442 --
1443 if p_person_type_id is null then
1444 --
1445 return(true);
1446 --
1447 end if;
1448 --
1449 open c1;
1450 fetch c1 into l_success;
1451 close c1;
1452 --
1453 if l_success = 'Y' then
1454 --
1455 return(true);
1456 --
1457 else
1458 --
1459 return(false);
1460 --
1461 end if;
1462 --
1463 end verify_person_type_id;
1464 --
1465 function is_dependent(p_person_id in number)
1466 return boolean is
1467 --
1468 cursor c_dpnt is
1469 select 'Y'
1470 from per_person_type_usages_f ptu,
1471 per_person_types ppt
1472 where ptu.person_id = p_person_id
1473 and ptu.person_type_id = ppt.person_type_id
1474 and ppt.system_person_type = 'DPNT'
1475 and g_parm.effective_date between
1476 ptu.effective_start_date and ptu.effective_end_date
1477 and ppt.active_flag = 'Y'
1478 and ppt.business_group_id = g_parm.business_group_id;
1479 --
1480 l_exists varchar2(30) := null;
1481 l_return boolean := false;
1482 l_proc varchar2(80) := g_package || '.is_dependent';
1483 --
1484 begin
1485 --
1486 hr_utility.set_location('Entering : ' || l_proc, 10);
1487 --
1488 open c_dpnt;
1489 fetch c_dpnt into l_exists;
1490 close c_dpnt;
1491 --
1492 if l_exists = 'Y' then
1493 --
1494 l_return := true;
1495 --
1496 hr_utility.set_location('Person id DPNT',10);
1497 end if;
1498 --
1499 hr_utility.set_location('Leaving : ' || l_proc, 10);
1500 --
1501 return(l_return);
1502 --
1503 exception
1504 when others then
1505 raise;
1506 --
1507 end is_dependent;
1508 --
1509
1510 /* Bug 8364821: Added function to determine whether Dependent is also an Employee */
1511
1512 function is_dependent_emp(p_person_id in number)
1513 return boolean is
1514 --
1515 cursor c_dpnt is
1516 select 'Y'
1517 from per_person_type_usages_f ptu,
1518 per_person_types ppt
1519 where ptu.person_id = p_person_id
1520 and ptu.person_type_id = ppt.person_type_id
1521 and ppt.system_person_type = 'EMP'
1522 and g_parm.effective_date between
1523 ptu.effective_start_date and ptu.effective_end_date
1524 and ppt.active_flag = 'Y'
1525 and ppt.business_group_id = g_parm.business_group_id;
1526 --
1527 l_exists varchar2(30) := null;
1528 l_return boolean := false;
1529 l_proc varchar2(80) := g_package || '.is_dependent_emp';
1530 --
1531 begin
1532 --
1533 hr_utility.set_location('Entering : ' || l_proc, 10);
1534 --
1535 open c_dpnt;
1536 fetch c_dpnt into l_exists;
1537 close c_dpnt;
1538 --
1539 if l_exists = 'Y' then
1540 --
1541 l_return := true;
1542 --
1543 hr_utility.set_location('Person id EMP',10);
1544 end if;
1545 --
1546 hr_utility.set_location('Leaving : ' || l_proc, 10);
1547 --
1548 return(l_return);
1549 --
1550 exception
1551 when others then
1552 raise;
1553 --
1554 end is_dependent_emp;
1555
1556 /* End Bug 8364821*/
1557
1558
1559 -- ----------------------------------------------------------------------------
1560 -- -------------------------< check_business_rules >---------------------------
1561 -- ----------------------------------------------------------------------------
1562 --
1563 procedure check_business_rules is
1564 --
1565 cursor c1 is
1566 select null
1567 from per_all_people_f ppf, per_person_types ppt
1568 where ppf.person_id = g_parm.person_id
1569 and ppf.person_type_id = g_parm.person_type_id
1570 and ppf.business_group_id = g_parm.business_group_id
1571 and g_parm.effective_date between ppf.effective_start_date
1572 and ppf.effective_end_date
1573 and ppf.person_type_id = ppt.person_type_id
1574 and ppt.business_group_id = g_parm.business_group_id
1575 and ppt.active_flag = 'Y';
1576 --
1577 l_person_type varchar2(30);
1578 l_dummy varchar2(30);
1579 --
1580 l_proc varchar2(80) := g_package || '.check_business_rules';
1581 --
1582 begin
1583 --
1584 hr_utility.set_location ('Entering '||l_proc,10);
1585 --
1586 -- This procedure checks validity of parameters that have been passed to the
1587 -- BENTMPCM process.
1588 --
1589 -- Check if mandatory arguments have been stipulated
1590 --
1591 hr_api.mandatory_arg_error(p_api_name => l_proc,
1592 p_argument => 'p_business_group_id',
1593 p_argument_value => g_parm.business_group_id);
1594 --
1595 hr_api.mandatory_arg_error(p_api_name => l_proc,
1596 p_argument => 'p_effective_date',
1597 p_argument_value => g_parm.effective_date);
1598 --
1599 hr_api.mandatory_arg_error(p_api_name => l_proc,
1600 p_argument => 'p_validate',
1601 p_argument_value => g_parm.validate_flag);
1602 --
1603 hr_api.mandatory_arg_error(p_api_name => l_proc,
1604 p_argument => 'p_cm_trgr_typ_cd',
1605 p_argument_value => g_parm.cm_trgr_typ_cd);
1606 --
1607 -- Business Rule Checks
1608 --
1609 -- p_person_selection_rule_id and p_person_id are mutually exclusive
1610 --
1611 if g_parm.person_id is not null and
1612 g_parm.person_sel_rl is not null then
1613 fnd_message.set_name('BEN','BEN_91745_RULE_AND_PERSON');
1614 fnd_message.set_token('PROC',l_proc);
1615 fnd_message.set_token('PERSON_ID',to_char(g_parm.person_id));
1616 fnd_message.set_token('PER_SELECT_RL',
1617 'person_selection_rule :'||g_parm.person_sel_rl);
1618 fnd_message.raise_error;
1619 end if;
1620 --
1621 -- p_person_id must be of p_person_type_id specified
1622 --
1623 if g_parm.person_id is not null and
1624 g_parm.person_type_id is not null then
1625 --
1626 -- Make sure person is of the person type specified
1627 --
1628 if not(verify_person_type_id(p_person_id => g_parm.person_id,
1629 p_person_type_id => g_parm.person_type_id)) then
1630 --
1631 fnd_message.set_name('BEN','BEN_91748_PERSON_TYPE');
1632 fnd_message.set_token('PROC',l_proc);
1633 fnd_message.set_token('PERSON_ID',to_char(g_parm.person_id));
1634 fnd_message.set_token('PER_TYPE_ID',to_char(g_parm.person_type_id));
1635 fnd_message.raise_error;
1636 --
1637 end if;
1638 --
1639 end if;
1640 --
1641 -- If a plan is specified as not in a program then the pgm_id should be null
1642 --
1643 if g_parm.plan_in_pgm_flag = 'N' and
1644 g_parm.pgm_id is not null then
1645 --
1646 fnd_message.set_name('BEN', 'BEN_92164_PLN_NIP_PGM_NULL');
1647 -- If you specify a plan as not in a program then the program should be blank.
1648 fnd_message.set_token('PROC',l_proc);
1649 fnd_message.raise_error;
1650 --
1651 end if;
1652 --
1653 -- Business rule for the emerging event trigger type:
1654 --
1655 if g_parm.cm_trgr_typ_cd = 'EMRGEVT' then
1656 --
1657 -- Atleast one of the derivable factors column must be specified.
1658 --
1659 if g_parm.age_fctr_id is null and
1660 g_parm.min_age is null and
1661 g_parm.max_age is null and
1662 g_parm.los_fctr_id is null and
1663 g_parm.min_los is null and
1664 g_parm.max_los is null and
1665 g_parm.cmbn_age_los_fctr_id is null then
1666 fnd_message.set_name('BEN','BEN_92165_ATLEAST_ONE_FCTR');
1667 fnd_message.set_token('PROC',l_proc);
1668 fnd_message.raise_error;
1669 end if;
1670 --
1671 -- If evaluating LOS then check if the person type is compatible.
1672 --
1673 if (g_parm.los_fctr_id is not null or
1674 g_parm.min_los is not null or
1675 g_parm.max_los is not null or
1676 g_parm.cmbn_age_los_fctr_id is not null) then
1677 --
1678 l_person_type := get_person_type
1679 (p_person_type_id => g_parm.person_type_id);
1680 --
1681 if l_person_type = 'DPNT' -- Dependent
1682 or l_person_type = 'BNF' -- Beneficiary
1683 or l_person_type = 'OTHER' -- External, Contact
1684 or l_person_type = 'APL' -- Applicant
1685 then
1686 --
1687 fnd_message.set_name('BEN','BEN_92166_INVLD_PER_TYPE');
1688 fnd_message.set_token('PROC',l_proc);
1689 fnd_message.set_token('PER_TYP',l_person_type);
1690 fnd_message.raise_error;
1691 --
1692 end if;
1693 --
1694 end if;
1695 --
1696 -- Only ONE derivable factor id should be specified. All other parameters
1697 -- related to derivable factors should be null.
1698 --
1699 if g_parm.age_fctr_id is not null then
1700 --
1701 -- If the factor is specified, then the min and max values should be null
1702 --
1703 if g_parm.min_age is not null or
1704 g_parm.max_age is not null then
1705 --
1706 fnd_message.set_name('BEN', 'BEN_92167_NO_MINMAX_WHEN_FCTR');
1707 fnd_message.set_token('PROC',l_proc);
1708 fnd_message.set_token('MIN','Min Age : '||g_parm.min_age);
1709 fnd_message.set_token('MAX','Max Age : '||g_parm.max_age);
1710 --
1711 -- Do not specify the min and max values when selecting a predefined
1712 -- FCTR factor.
1713 --
1714 fnd_message.raise_error;
1715 --
1716 end if;
1717 --
1718 if g_parm.los_fctr_id is not null or
1719 g_parm.min_los is not null or
1720 g_parm.max_los is not null or
1721 g_parm.cmbn_age_los_fctr_id is not null then
1722 fnd_message.set_name('BEN', 'BEN_92168_ONLY_ONE_FCTR');
1723 fnd_message.set_token('PROC',l_proc);
1724 fnd_message.set_token('LOS_FCTR_ID',to_char(g_parm.los_fctr_id));
1725 fnd_message.set_token('AGE_FCTR_ID',to_char(g_parm.age_fctr_id));
1726 fnd_message.set_token('MIN_LOS',to_char(g_parm.min_los));
1727 fnd_message.set_token('MAX_LOS',to_char(g_parm.max_los));
1728 fnd_message.set_token('MIN_AGE',to_char(g_parm.min_age));
1729 fnd_message.set_token('MAX_AGE',to_char(g_parm.max_age));
1730 fnd_message.set_token('CMBN_AGE_LOS_FCTR_ID',
1731 to_char(g_parm.cmbn_age_los_fctr_id));
1732 fnd_message.raise_error;
1733 end if;
1734 --
1735 end if;
1736 --
1737 if g_parm.los_fctr_id is not null then
1738 --
1739 -- If the factor is specified, then the min and max values should be null
1740 --
1741 if g_parm.min_los is not null or
1742 g_parm.max_los is not null then
1743 --
1744 fnd_message.set_name('BEN', 'BEN_92167_NO_MINMAX_WHEN_FCTR');
1745 fnd_message.set_token('PROC',l_proc);
1746 fnd_message.set_token('MIN','Min Length of Service : '||g_parm.min_los);
1747 fnd_message.set_token('MAX','Max Length of Service : '||g_parm.max_los);
1748 --
1749 -- Do not specify the min and max values when selecting a predefined
1750 -- FCTR factor.
1751 --
1752 fnd_message.raise_error;
1753 --
1754 end if;
1755 --
1756 if g_parm.age_fctr_id is not null or
1757 g_parm.min_age is not null or
1758 g_parm.max_age is not null or
1759 g_parm.cmbn_age_los_fctr_id is not null then
1760 fnd_message.set_name('BEN', 'BEN_92168_ONLY_ONE_FCTR');
1761 fnd_message.set_token('PROC',l_proc);
1762 fnd_message.set_token('LOS_FCTR_ID',to_char(g_parm.los_fctr_id));
1763 fnd_message.set_token('AGE_FCTR_ID',to_char(g_parm.age_fctr_id));
1764 fnd_message.set_token('MIN_LOS',to_char(g_parm.min_los));
1765 fnd_message.set_token('MAX_LOS',to_char(g_parm.max_los));
1766 fnd_message.set_token('MIN_AGE',to_char(g_parm.min_age));
1767 fnd_message.set_token('MAX_AGE',to_char(g_parm.max_age));
1768 fnd_message.set_token('CMBN_AGE_LOS_FCTR_ID',
1769 to_char(g_parm.cmbn_age_los_fctr_id));
1770 fnd_message.raise_error;
1771 end if;
1772 --
1773 end if;
1774 --
1775 if g_parm.cmbn_age_los_fctr_id is not null then
1776 --
1777 if g_parm.age_fctr_id is not null or
1778 g_parm.los_fctr_id is not null then
1779 fnd_message.set_name('BEN', 'BEN_92168_ONLY_ONE_FCTR');
1780 fnd_message.set_token('PROC',l_proc);
1781 fnd_message.set_token('LOS_FCTR_ID',to_char(g_parm.los_fctr_id));
1782 fnd_message.set_token('AGE_FCTR_ID',to_char(g_parm.age_fctr_id));
1783 fnd_message.set_token('MIN_LOS',to_char(g_parm.min_los));
1784 fnd_message.set_token('MAX_LOS',to_char(g_parm.max_los));
1785 fnd_message.set_token('MIN_AGE',to_char(g_parm.min_age));
1786 fnd_message.set_token('MAX_AGE',to_char(g_parm.max_age));
1787 fnd_message.set_token('CMBN_AGE_LOS_FCTR_ID',
1788 to_char(g_parm.cmbn_age_los_fctr_id));
1789 fnd_message.raise_error;
1790 end if;
1791 --
1792 end if;
1793 --
1794 -- When a min or a max value is being supplied for a derivable factor:
1795 --
1796 if g_parm.min_age is not null or
1797 g_parm.max_age is not null then
1798 --
1799 -- Check if the min is greater than then max
1800 --
1801 if g_parm.min_age > g_parm.max_age then
1802 fnd_message.set_name('BEN', 'BEN_92534_MIN_MAX');
1803 fnd_message.set_token('PROC',l_proc);
1804 fnd_message.set_token('MIN','Minimum Age : '||g_parm.min_age);
1805 fnd_message.set_token('MAX','Maximum Age : '||g_parm.max_age);
1806 fnd_message.raise_error;
1807 end if;
1808 --
1809 -- If only a max value is being provided, error out because if the user
1810 -- wants to evaluate a threshold, the min can be used for that purpose
1811 --
1812 if g_parm.min_age is null and
1813 g_parm.max_age is not null then
1814 fnd_message.set_name('BEN', 'BEN_92169_ONLY_MIN_OR_BOTH');
1815 fnd_message.set_token('PROC',l_proc);
1816 fnd_message.set_token('MIN','Minimum Age : '||g_parm.min_age);
1817 fnd_message.set_token('MAX','Maximum Age : '||g_parm.max_age);
1818 -- Only max value specified. Provide only the min value or both min and
1819 -- max values.
1820 fnd_message.raise_error;
1821 end if;
1822 --
1823 -- When evaluating this derived factor, all other parameters relating to
1824 -- other derived factors should be null.
1825 --
1826 if g_parm.los_fctr_id is not null or
1827 g_parm.min_los is not null or
1828 g_parm.max_los is not null or
1829 g_parm.cmbn_age_los_fctr_id is not null then
1830 fnd_message.set_name('BEN', 'BEN_92168_ONLY_ONE_FCTR');
1831 fnd_message.set_token('PROC',l_proc);
1832 fnd_message.set_token('LOS_FCTR_ID',to_char(g_parm.los_fctr_id));
1833 fnd_message.set_token('AGE_FCTR_ID',to_char(g_parm.age_fctr_id));
1834 fnd_message.set_token('MIN_LOS',to_char(g_parm.min_los));
1835 fnd_message.set_token('MAX_LOS',to_char(g_parm.max_los));
1836 fnd_message.set_token('MIN_AGE',to_char(g_parm.min_age));
1837 fnd_message.set_token('MAX_AGE',to_char(g_parm.max_age));
1838 fnd_message.set_token('CMBN_AGE_LOS_FCTR_ID',
1839 to_char(g_parm.cmbn_age_los_fctr_id));
1840 --
1841 fnd_message.raise_error;
1842 end if;
1843 --
1844 elsif g_parm.min_los is not null or
1845 g_parm.max_los is not null then
1846 --
1847 -- Check if the min is greater than then max
1848 --
1849 if g_parm.min_los > g_parm.max_los then
1850 fnd_message.set_name('BEN', 'BEN_92534_MIN_MAX');
1851 fnd_message.set_token('PROC',l_proc);
1852 fnd_message.set_token('MIN',
1853 'Minimum Length of Service : '||g_parm.min_los);
1854 fnd_message.set_token('MAX',
1855 'Maximum Length of Service : '||g_parm.max_los);
1856 fnd_message.raise_error;
1857 end if;
1858 --
1859 -- If only a max value is being provided, error out because if the user
1860 -- wants to evaluate a threshold, the min can be used for that purpose
1861 --
1862 if g_parm.min_los is null and
1863 g_parm.max_los is not null then
1864 fnd_message.set_name('BEN', 'BEN_92169_ONLY_MIN_OR_BOTH');
1865 fnd_message.set_token('PROC',l_proc);
1866 fnd_message.set_token('MIN',
1867 'Minimum Length of Service : '||g_parm.min_los);
1868 fnd_message.set_token('MAX',
1869 'Maximum Length of Service : '||g_parm.max_los);
1870 fnd_message.raise_error;
1871 end if;
1872 --
1873 -- When evaluating this derived factor, all other parameters relating to
1874 -- other derived factors should be null.
1875 --
1876 if g_parm.age_fctr_id is not null or
1877 g_parm.min_age is not null or
1878 g_parm.max_age is not null or
1879 g_parm.cmbn_age_los_fctr_id is not null then
1880 fnd_message.set_name('BEN', 'BEN_92168_ONLY_ONE_FCTR');
1881 fnd_message.set_token('PROC',l_proc);
1882 fnd_message.set_token('LOS_FCTR_ID',to_char(g_parm.los_fctr_id));
1883 fnd_message.set_token('AGE_FCTR_ID',to_char(g_parm.age_fctr_id));
1884 fnd_message.set_token('MIN_LOS',to_char(g_parm.min_los));
1885 fnd_message.set_token('MAX_LOS',to_char(g_parm.max_los));
1886 fnd_message.set_token('MIN_AGE',to_char(g_parm.min_age));
1887 fnd_message.set_token('MAX_AGE',to_char(g_parm.max_age));
1888 fnd_message.set_token('CMBN_AGE_LOS_FCTR_ID',
1889 to_char(g_parm.cmbn_age_los_fctr_id));
1890 fnd_message.raise_error;
1891 end if;
1892 --
1893 end if;
1894 --
1895 end if; -- cm_trgr_typ_cd = EMRGEVT
1896 --
1897 -- Business Rules for the Targeted Mass Mailing Trigger
1898 --
1899 if g_parm.cm_trgr_typ_cd = 'MSSMLG' then
1900 --
1901 if g_parm.elig_enrol_cd is not null
1902 and g_parm.pgm_id is null and
1903 g_parm.pl_nip_id is null then
1904 --
1905 -- If the elig_enrol_cd is being specified, then a comp object must be
1906 -- specified.
1907 --
1908 fnd_message.set_name('BEN', 'BEN_92171_PLAN_OR_PGM');
1909 fnd_message.set_token('PROC',l_proc);
1910 -- A program or a plan must be provided for the mass mailing trigger.
1911 fnd_message.raise_error;
1912 --
1913 end if;
1914 --
1915 end if; -- g_parm.cm_trgr_typ_cd = MSSMLG
1916 --
1917 hr_utility.set_location ('Leaving '||l_proc,10);
1918 --
1919 end check_business_rules;
1920 --
1921 -- ----------------------------------------------------------------------------
1922 -- ---------------------------< process_age >----------------------------------
1923 -- ----------------------------------------------------------------------------
1924 --
1925 procedure process_age
1926 (p_person_id in number
1927 ,p_lf_evt_ocrd_dt out nocopy date
1928 ,p_breach out nocopy boolean) is
1929 --
1930 -- This procedure processes a person's age. This calculation is different from
1931 -- age calculations in other modules because of a date range. The user can
1932 -- specify a range of dates in which to evaluate if the person(s) will cross an
1933 -- age boundary.
1934 --
1935 cursor c_person is
1936 select ppf.date_of_birth
1937 from per_all_people_f ppf
1938 where ppf.person_id = p_person_id
1939 and ppf.business_group_id = g_parm.business_group_id
1940 and g_parm.effective_date between ppf.effective_start_date
1941 and ppf.effective_end_date;
1942 --
1943 l_dob date;
1944 --
1945 cursor c_agf is
1946 select *
1947 from ben_age_fctr agf
1948 where agf.age_fctr_id = g_parm.age_fctr_id
1949 and agf.business_group_id = g_parm.business_group_id;
1950 --
1951 l_agf c_agf%rowtype;
1952 --
1953 dob_null exception;
1954 agf_pgm_or_pl exception;
1955 --
1956 l_age_before number;
1957 l_age_after number;
1958 l_adj_date_from date;
1959 l_adj_date_to date;
1960 l_effective_date date;
1961 l_uom varchar2(30) := null;
1962 l_min_value number;
1963 l_max_value number;
1964 l_break varchar2(30);
1965 --RCHASE
1966 l_dob_null date:=NULL;
1967 --
1968 l_proc varchar2(80) := g_package || '.process_age';
1969 --
1970 begin
1971 --
1972 hr_utility.set_location('Entering : ' || l_proc, 10);
1973 --
1974 hr_utility.set_location('Processing age for person ID : ' || p_person_id, 10);
1975 --
1976 -- Get the person's date of birth
1977 --
1978 open c_person;
1979 fetch c_person into l_dob;
1980 close c_person;
1981 --
1982 if l_dob is null then
1983 raise dob_null;
1984 end if;
1985 --
1986 if g_parm.date_from is not null then
1987 --
1988 l_effective_date := g_parm.date_from;
1989 --
1990 else
1991 --
1992 l_effective_date := g_parm.effective_date;
1993 --
1994 end if;
1995 --
1996 if g_parm.age_fctr_id is not null then
1997 --
1998 -- An age factor is specified. Get the details from the table.
1999 --
2000 open c_agf;
2001 fetch c_agf into l_agf;
2002 close c_agf;
2003 --
2004 l_uom := l_agf.age_uom;
2005 l_min_value := l_agf.mn_age_num;
2006 l_max_value := l_agf.mx_age_num;
2007 --
2008 if l_agf.age_det_cd in ('AFDCPPY', 'ALDCPPY', 'AFDFPPY') and
2009 g_parm.pgm_id is null and
2010 g_parm.pl_nip_id is null then
2011 raise agf_pgm_or_pl;
2012 end if;
2013 --
2014 ben_derive_factors.determine_age
2015 (p_person_id => p_person_id
2016 --RCHASE
2017 ,p_per_dob => l_dob_null
2018 ,p_age_fctr_id => l_agf.age_fctr_id
2019 ,p_per_in_ler_id => null
2020 ,p_pgm_id => g_parm.pgm_id
2021 ,p_pl_id => g_parm.pl_nip_id
2022 ,p_effective_date => l_effective_date - 1
2023 ,p_business_group_id => g_parm.business_group_id
2024 ,p_value => l_age_before
2025 ,p_change_date => l_adj_date_from);
2026 --
2027 ben_derive_factors.determine_age
2028 (p_person_id => p_person_id
2029 --RCHASE
2030 ,p_per_dob => l_dob_null
2031 ,p_age_fctr_id => l_agf.age_fctr_id
2032 ,p_per_in_ler_id => null
2033 ,p_pgm_id => g_parm.pgm_id
2034 ,p_pl_id => g_parm.pl_nip_id
2035 ,p_effective_date => g_parm.effective_date
2036 ,p_business_group_id => g_parm.business_group_id
2037 ,p_value => l_age_after
2038 ,p_change_date => l_adj_date_to);
2039 --
2040 elsif g_parm.age_fctr_id is null and
2041 (g_parm.min_age is not null or
2042 g_parm.max_age is not null) then
2043 --
2044 -- An age factor not specified. But a min age or a max age was specified.
2045 -- Evaluate if the person's age will cross either the min or max boundary
2046 -- in the date range (date_from to effective_date) specified.
2047 --
2048 hr_utility.set_location('Calculating user specified age ' || l_proc, 10);
2049 --
2050 l_age_before := months_between(l_effective_date - 1, l_dob )/12;
2051 l_age_after := months_between(g_parm.effective_date, l_dob)/12;
2052 l_uom := 'YR';
2053 l_min_value := g_parm.min_age;
2054 l_max_value := g_parm.max_age;
2055 --
2056 hr_utility.set_location('Age Before'||l_age_before,10);
2057 hr_utility.set_location('Age After'||l_age_after,10);
2058 hr_utility.set_location('Min Value'||l_min_value,10);
2059 hr_utility.set_location('Max Value'||l_max_value,10);
2060 --
2061 end if; -- g_parm.age_fctr_id
2062 --
2063 -- Check if a breach occures.
2064 --
2065 --
2066 if benutils.min_max_breach(
2067 p_min_value => l_min_value,
2068 p_max_value => l_max_value,
2069 p_old_value => l_age_before,
2070 p_new_value => l_age_after,
2071 p_break => l_break) then
2072 --
2073 p_lf_evt_ocrd_dt := benutils.derive_date(
2074 p_date => l_dob,
2075 p_uom => l_uom,
2076 p_min => l_min_value,
2077 p_max => l_max_value,
2078 p_value => l_break);
2079 p_breach := true;
2080 --
2081 else
2082 --
2083 p_lf_evt_ocrd_dt := null;
2084 p_breach := false;
2085 --
2086 end if;
2087 --
2088 hr_utility.set_location('Leaving : ' || l_proc, 10);
2089 --
2090 exception
2091 --
2092 when dob_null then
2093 hr_utility.set_location('Exception raised in ' || l_proc, 10);
2094 fnd_message.set_name('BEN', 'BEN_91337_DOB');
2095 fnd_message.set_token('PROC',l_proc);
2096 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2097 -- The person being processed does not have a date of birth.
2098 raise ben_manage_life_events.g_record_error;
2099 --
2100 when agf_pgm_or_pl then
2101 hr_utility.set_location('Exception raised in ' || l_proc, 10);
2102 fnd_message.set_name('BEN','BEN_92181_FCTR_RQR_PGM_OR_PL');
2103 fnd_message.set_token('PROC',l_proc);
2104 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2105 --
2106 -- The derived factor chosen uses a program or a plan year for calculation.
2107 -- Please select a program or plan.
2108 --
2109 raise ben_manage_life_events.g_record_error;
2110 --
2111 when others then
2112 hr_utility.set_location('Exception raised in ' || l_proc, 10);
2113 -- For nocopy changes
2114 p_lf_evt_ocrd_dt := null;
2115 p_breach := false;
2116 --
2117 raise;
2118 --
2119 end process_age;
2120 --
2121 -- ----------------------------------------------------------------------------
2122 -- ---------------------------< process_los >----------------------------------
2123 -- ----------------------------------------------------------------------------
2124 --
2125 procedure process_los
2126 (p_person_id in number
2127 ,p_lf_evt_ocrd_dt out nocopy date
2128 ,p_breach out nocopy boolean) is
2129 --
2130 -- This procedure processes the person's length of service.
2131 --
2132 cursor c_lsf is
2133 select *
2134 from ben_los_fctr los
2135 where los.los_fctr_id = g_parm.los_fctr_id
2136 and los.business_group_id = g_parm.business_group_id;
2137 --
2138 l_lsf c_lsf%rowtype;
2139 --
2140 cursor c_person is
2141 select pps.date_start,
2142 pps.adjusted_svc_date
2143 from per_periods_of_service pps
2144 where pps.person_id = p_person_id
2145 and pps.business_group_id = g_parm.business_group_id;
2146 --
2147 l_start_date date;
2148 l_adj_svc_date date;
2149 l_los_before number;
2150 l_los_after number;
2151 l_min_value number;
2152 l_max_value number;
2153 l_effective_date date;
2154 l_break varchar2(30);
2155 l_uom varchar2(30);
2156 --
2157 lsf_pgm_or_pl exception;
2158 --
2159 l_proc varchar2(80) := g_package || '.calculate_los';
2160 --
2161 begin
2162 --
2163 hr_utility.set_location('Entering : ' || l_proc, 10);
2164 --
2165 hr_utility.set_location('Person ID : ' || p_person_id, 10);
2166 --
2167 open c_person;
2168 fetch c_person into l_start_date,
2169 l_adj_svc_date;
2170 close c_person;
2171 --
2172 -- If the LOS Date to use Code is to use Adjusted Service date,
2173 -- override the start date by adjusted service date, if it is not null.
2174 --
2175 if g_parm.los_det_to_use_cd = 'ASD' then
2176 --
2177 l_start_date := nvl(l_adj_svc_date, l_start_date);
2178 --
2179 end if;
2180 --
2181 if g_parm.date_from is not null then
2182 --
2183 l_effective_date := g_parm.date_from;
2184 --
2185 else
2186 --
2187 l_effective_date := g_parm.effective_date;
2188 --
2189 end if;
2190 --
2191 if g_parm.los_fctr_id is not null then
2192 --
2193 -- An LOS factor is specified. Get the details from the table.
2194 --
2195 open c_lsf;
2196 fetch c_lsf into l_lsf;
2197 close c_lsf;
2198 --
2199 if l_lsf.los_det_cd in ('AFDCPPY', 'ALDCPPY', 'AFDFPPY') and
2200 g_parm.pgm_id is null and
2201 g_parm.pl_nip_id is null then
2202 raise lsf_pgm_or_pl;
2203 end if;
2204 --
2205 l_uom := l_lsf.los_uom;
2206 l_min_value := l_lsf.mn_los_num;
2207 l_max_value := l_lsf.mx_los_num;
2208 --
2209 ben_derive_factors.determine_los
2210 (p_person_id => p_person_id
2211 ,p_los_fctr_id => l_lsf.los_fctr_id
2212 ,p_per_in_ler_id => null
2213 ,p_pgm_id => g_parm.pgm_id
2214 ,p_pl_id => g_parm.pl_nip_id
2215 ,p_effective_date => l_effective_date - 1
2216 ,p_lf_evt_ocrd_dt => null
2217 ,p_business_group_id => g_parm.business_group_id
2218 ,p_perform_rounding_flg => TRUE
2219 ,p_value => l_los_before
2220 ,p_start_date => l_start_date);
2221 --
2222 ben_derive_factors.determine_los
2223 (p_person_id => p_person_id
2224 ,p_los_fctr_id => l_lsf.los_fctr_id
2225 ,p_per_in_ler_id => null
2226 ,p_pgm_id => g_parm.pgm_id
2227 ,p_pl_id => g_parm.pl_nip_id
2228 ,p_effective_date => g_parm.effective_date
2229 ,p_lf_evt_ocrd_dt => null
2230 ,p_business_group_id => g_parm.business_group_id
2231 ,p_perform_rounding_flg => TRUE
2232 ,p_value => l_los_after
2233 ,p_start_date => l_start_date);
2234 --
2235 elsif g_parm.los_fctr_id is null and
2236 (g_parm.min_los is not null or
2237 g_parm.max_los is not null) then
2238 --
2239 -- An LOS factor not specified. But a min LOS or a max LOS was specified.
2240 -- Evaluate if the person's LOS will cross either the min or max boundary
2241 -- in the date range (date_from to effective_date) specified.
2242 --
2243 hr_utility.set_location('Calculating user specified LOS ' || l_proc, 10);
2244 --
2245 l_los_before := months_between(l_effective_date - 1, l_start_date )/12;
2246 l_los_after := months_between(g_parm.effective_date, l_start_date)/12;
2247 l_uom := 'YR';
2248 l_min_value := g_parm.min_los;
2249 l_max_value := g_parm.max_los;
2250 --
2251 end if; -- g_parm.los_fctr_id
2252 --
2253 -- Check if a breach occures.
2254 --
2255 if benutils.min_max_breach(
2256 p_min_value => l_min_value,
2257 p_max_value => l_max_value,
2258 p_old_value => l_los_before,
2259 p_new_value => l_los_after,
2260 p_break => l_break) then
2261 --
2262 p_lf_evt_ocrd_dt := benutils.derive_date(
2263 p_date => l_start_date,
2264 p_uom => l_uom,
2265 p_min => l_min_value,
2266 p_max => l_max_value,
2267 p_value => l_break);
2268 p_breach := true;
2269 --
2270 else
2271 --
2272 p_lf_evt_ocrd_dt := null;
2273 p_breach := false;
2274 --
2275 end if;
2276 --
2277 hr_utility.set_location('Leaving : ' || l_proc, 10);
2278 --
2279 exception
2280 --
2281 when lsf_pgm_or_pl then
2282 --
2283 hr_utility.set_location('Leaving : ' || l_proc, 10);
2284 fnd_message.set_name('BEN','BEN_92181_FCTR_RQR_PGM_OR_PL');
2285 fnd_message.set_token('PROC',l_proc);
2286 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2287 --
2288 -- The derived factor chosen uses a program or a plan year for calculation.
2289 -- Please select a program or plan.
2290 --
2291 raise ben_manage_life_events.g_record_error;
2292 --
2293 when others then
2294 hr_utility.set_location('Exception raised in ' || l_proc, 10);
2295 -- For nocopy changes
2296 p_lf_evt_ocrd_dt := null;
2297 p_breach := false;
2298 --
2299 raise;
2300 --
2301 end process_los;
2302 --
2303 -- ----------------------------------------------------------------------------
2304 -- ------------------------< process_cmbn_age_los >----------------------------
2305 -- ----------------------------------------------------------------------------
2306 --
2307 procedure process_cmbn_age_los
2308 (p_person_id in number
2309 ,p_lf_evt_ocrd_dt out nocopy date
2310 ,p_breach out nocopy boolean) is
2311 --
2312 cursor c_cmbn is
2313 select cal.cmbn_age_los_fctr_id,
2314 cal.age_fctr_id,
2315 cal.los_fctr_id,
2316 cal.cmbnd_min_val,
2317 cal.cmbnd_max_val,
2318 agf.mn_age_num,
2319 agf.mx_age_num,
2320 agf.age_det_cd,
2321 lsf.mn_los_num,
2322 lsf.mx_los_num,
2323 lsf.los_det_cd
2324 from ben_cmbn_age_los_fctr cal,
2325 ben_age_fctr agf,
2326 ben_los_fctr lsf
2327 where cal.cmbn_age_los_fctr_id = g_parm.cmbn_age_los_fctr_id
2328 and cal.business_group_id = g_parm.business_group_id
2329 and cal.age_fctr_id = agf.age_fctr_id
2330 and agf.business_group_id = g_parm.business_group_id
2331 and cal.los_fctr_id = lsf.los_fctr_id
2332 and lsf.business_group_id = g_parm.business_group_id;
2333 --
2334 l_cmbn c_cmbn%rowtype;
2335 --
2336 l_age_before number;
2337 l_age_after number;
2338 l_los_before number;
2339 l_los_after number;
2340 l_cmbn_before number;
2341 l_cmbn_after number;
2342 l_min_value number;
2343 l_max_value number;
2344 l_adj_date date;
2345 l_effective_date date;
2346 l_start_date date;
2347 l_break varchar2(30);
2348 l_dob_null date:=NULL;
2349 --
2350 cmbn_pgm_or_pl exception;
2351 --
2352 l_proc varchar2(80) := g_package || '.process_cmbn_age_los';
2353 --
2354 begin
2355 --
2356 hr_utility.set_location('Entering : ' || l_proc, 10);
2357 --
2358 if g_parm.date_from is not null then
2359 --
2360 l_effective_date := g_parm.date_from;
2361 --
2362 else
2363 --
2364 l_effective_date := g_parm.effective_date;
2365 --
2366 end if;
2367 --
2368 open c_cmbn;
2369 fetch c_cmbn into l_cmbn;
2370 close c_cmbn;
2371 --
2372 if (l_cmbn.age_det_cd in ('AFDCPPY','ALDCPPY','AFDCPPY') or
2373 l_cmbn.los_det_cd in ('AFDCPPY','ALDCPPY','AFDCPPY')) and
2374 g_parm.pgm_id is null and
2375 g_parm.pl_nip_id is null then
2376 raise cmbn_pgm_or_pl;
2377 end if;
2378 --
2379 ben_derive_factors.determine_age
2380 (p_person_id => p_person_id
2381 --RCHASE
2382 ,p_per_dob => l_dob_null
2383 ,p_age_fctr_id => l_cmbn.age_fctr_id
2384 ,p_per_in_ler_id => null
2385 ,p_pgm_id => g_parm.pgm_id
2386 ,p_pl_id => g_parm.pl_nip_id
2387 ,p_effective_date => l_effective_date - 1
2388 ,p_business_group_id => g_parm.business_group_id
2389 ,p_value => l_age_before
2390 ,p_change_date => l_adj_date);
2391 --
2392 ben_derive_factors.determine_age
2393 (p_person_id => p_person_id
2394 --RCHASE
2395 ,p_per_dob => l_dob_null
2396 ,p_age_fctr_id => l_cmbn.age_fctr_id
2397 ,p_per_in_ler_id => null
2398 ,p_pgm_id => g_parm.pgm_id
2399 ,p_pl_id => g_parm.pl_nip_id
2400 ,p_effective_date => g_parm.effective_date
2401 ,p_business_group_id => g_parm.business_group_id
2402 ,p_value => l_age_after
2403 ,p_change_date => l_adj_date);
2404 --
2405 ben_derive_factors.determine_los
2406 (p_person_id => p_person_id
2407 ,p_los_fctr_id => l_cmbn.los_fctr_id
2408 ,p_per_in_ler_id => null
2409 ,p_pgm_id => g_parm.pgm_id
2410 ,p_pl_id => g_parm.pl_nip_id
2411 ,p_effective_date => l_effective_date - 1
2412 ,p_lf_evt_ocrd_dt => null
2413 ,p_business_group_id => g_parm.business_group_id
2414 ,p_perform_rounding_flg => TRUE
2415 ,p_value => l_los_before
2416 ,p_start_date => l_start_date);
2417 --
2418 ben_derive_factors.determine_los
2419 (p_person_id => p_person_id
2420 ,p_los_fctr_id => l_cmbn.los_fctr_id
2421 ,p_per_in_ler_id => null
2422 ,p_pgm_id => g_parm.pgm_id
2423 ,p_pl_id => g_parm.pl_nip_id
2424 ,p_effective_date => g_parm.effective_date
2425 ,p_lf_evt_ocrd_dt => null
2426 ,p_business_group_id => g_parm.business_group_id
2427 ,p_perform_rounding_flg => TRUE
2428 ,p_value => l_los_after
2429 ,p_start_date => l_start_date);
2430 --
2431 l_cmbn_before := l_age_before + l_los_before;
2432 l_cmbn_after := l_age_after + l_los_after;
2433 l_min_value := nvl(l_cmbn.cmbnd_min_val,
2434 l_cmbn.mn_age_num+l_cmbn.mn_los_num);
2435 l_max_value := nvl(l_cmbn.cmbnd_max_val,
2436 l_cmbn.mx_age_num+l_cmbn.mx_los_num);
2437 --
2438 if benutils.min_max_breach(
2439 p_min_value => l_min_value,
2440 p_max_value => l_max_value,
2441 p_old_value => l_cmbn_before,
2442 p_new_value => l_cmbn_after,
2443 p_break => l_break) then
2444 --
2445 p_lf_evt_ocrd_dt := g_parm.effective_date;
2446 p_breach := true;
2447 --
2448 else
2449 --
2450 p_lf_evt_ocrd_dt := null;
2451 p_breach := false;
2452 --
2453 end if;
2454 --
2455 hr_utility.set_location('Leaving : ' || l_proc, 10);
2456 --
2457 exception
2458 --
2459 when cmbn_pgm_or_pl then
2460 hr_utility.set_location('Exception raised in ' || l_proc, 10);
2461 fnd_message.set_name('BEN','BEN_92181_FCTR_RQR_PGM_OR_PL');
2462 fnd_message.set_token('PROC',l_proc);
2463 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2464 --
2465 -- The derived factor chosen uses a program or plan year for calculation.
2466 -- Please select a program or plan.
2467 --
2468 raise ben_manage_life_events.g_record_error;
2469 --
2470 when others then
2471 hr_utility.set_location('Exception raised in ' || l_proc, 10);
2472 -- For nocopy changes
2473 p_lf_evt_ocrd_dt := null;
2474 p_breach := false;
2475 --
2476 raise;
2477 end;
2478 --
2479 -- ----------------------------------------------------------------------------
2480 -- ------------------------< process_emrg_events >-----------------------------
2481 -- ----------------------------------------------------------------------------
2482 --
2483 procedure process_emrg_events
2484 (p_start_person_action_id in number
2485 ,p_end_person_action_id in number) is
2486 --
2487 cursor c_per_actn is
2488 select *
2489 from ben_person_actions bpa
2490 where bpa.benefit_action_id = g_parm.benefit_action_id
2491 and bpa.person_action_id between p_start_person_action_id
2492 and p_end_person_action_id
2493 and bpa.action_status_cd <> 'P'
2494 order by bpa.person_action_id;
2495 --
2496 l_breach boolean;
2497 l_lf_evt_ocrd_dt date;
2498 l_person_id number;
2499 l_dpnt_person_id number := null;
2500 l_cm_sent boolean := TRUE;
2501 --
2502 l_proc varchar2(80) := g_package || '.process_emrg_events';
2503 --
2504 begin
2505 --
2506 hr_utility.set_location('Entering ' || l_proc, 10);
2507 --
2508 for l_per_rec in c_per_actn loop
2509 --
2510 g_person_cnt := g_person_cnt + 1;
2511 l_breach := false;
2512 l_lf_evt_ocrd_dt := null;
2513 l_person_id := l_per_rec.person_id;
2514 --
2515 begin
2516 --
2517 -- Set a savepoint to rollback errors
2518 --
2519 savepoint before_communications;
2520 --
2521 if g_parm.age_fctr_id is not null or
2522 g_parm.min_age is not null or
2523 g_parm.max_age is not null then
2524 --
2525 hr_utility.set_location('Processing age ' || l_proc, 15);
2526 --
2527 -- If we are processing emerging events for dependents, then get the
2528 -- main person id since the communication will be sent to participants.
2529 --
2530 /* Bug 8364821: Added If else condition. If Dependent is an Employee, do not create communication on the
2531 Employee. Communication should be sent to the Dependent. If not send the Communication to the Employee */
2532 if is_dependent_emp(p_person_id => l_per_rec.person_id) then
2533 hr_utility.set_location('Dependent is Employee ', 15);
2534 l_person_id := l_per_rec.person_id;
2535 else
2536 hr_utility.set_location('Dependent is Dependent ', 15);
2537 l_person_id := get_main_person_id
2538 (p_dpnt_person_id => l_per_rec.person_id);
2539 --
2540 l_dpnt_person_id := l_per_rec.person_id;
2541 end if;
2542 --
2543 -- Process the person's age to check if there is a breach.
2544 --
2545 process_age(p_person_id => l_per_rec.person_id
2546 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2547 ,p_breach => l_breach);
2548 --
2549 elsif g_parm.los_fctr_id is not null or
2550 g_parm.min_los is not null or
2551 g_parm.max_los is not null then
2552 --
2553 hr_utility.set_location('Processing LOS ' || l_proc, 15);
2554 --
2555 l_person_id := l_per_rec.person_id;
2556 --
2557 -- Process the person's length of service to check if there is a breach.
2558 --
2559 process_los
2560 (p_person_id => l_per_rec.person_id
2561 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2562 ,p_breach => l_breach);
2563 --
2564 elsif g_parm.cmbn_age_los_fctr_id is not null then
2565 --
2566 hr_utility.set_location('Processing cmbn_age_los ' || l_proc, 15);
2567 --
2568 l_person_id := l_per_rec.person_id;
2569 --
2570 -- Process the person's combination of age and length of service.
2571 --
2572 process_cmbn_age_los
2573 (p_person_id => l_per_rec.person_id
2574 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2575 ,p_breach => l_breach);
2576 --
2577 else
2578 hr_utility.set_location('Not calculating factors ' || l_proc, 15);
2579 end if;
2580 --
2581 hr_utility.set_location('After processing...', 15);
2582 --
2583 ben_generate_communications.g_comm_generated := false;
2584 --
2585 if l_breach then
2586 --
2587 hr_utility.set_location(l_person_id,10);
2588 hr_utility.set_location(g_parm.cm_trgr_typ_cd,10);
2589 hr_utility.set_location(g_parm.cm_typ_id,10);
2590 hr_utility.set_location(g_parm.pgm_id,10);
2591 hr_utility.set_location(g_parm.pl_nip_id,10);
2592 hr_utility.set_location(l_dpnt_person_id,10);
2593 hr_utility.set_location(g_parm.business_group_id,10);
2594 hr_utility.set_location(g_parm.effective_date,10);
2595 hr_utility.set_location(l_lf_evt_ocrd_dt,10);
2596 hr_utility.set_location(g_parm.mode_cd,10);
2597 ben_generate_communications.main
2598 (p_person_id => l_person_id
2599 ,p_cm_trgr_typ_cd => g_parm.cm_trgr_typ_cd
2600 ,p_cm_typ_id => g_parm.cm_typ_id
2601 ,p_pgm_id => g_parm.pgm_id
2602 ,p_pl_id => g_parm.pl_nip_id
2603 ,p_dpnt_person_id => l_dpnt_person_id
2604 ,p_business_group_id => g_parm.business_group_id
2605 ,p_proc_cd1 => 'MSSMLGEE'
2606 ,p_effective_date => g_parm.effective_date
2607 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2608 ,p_mode => g_parm.mode_cd);
2609 --
2610 end if;
2611 --
2612 hr_utility.set_location('Here',10);
2613 if (g_parm.audit_log_flag = 'Y') then
2614 chk_per_cm(p_person_id => l_person_id);
2615 end if;
2616 --
2617 g_action_rec.person_action_id := l_per_rec.person_action_id;
2618 g_action_rec.action_status_cd := 'P';
2619 g_action_rec.object_version_number := l_per_rec.object_version_number;
2620 g_action_rec.effective_date := g_parm.effective_date;
2621 --
2622 hr_utility.set_location('Nearly Leaving Now',10);
2623 benutils.write(p_rec => g_action_rec);
2624 --
2625 hr_utility.set_location('Leaving Now',10);
2626 exception
2627 --
2628 when ben_manage_life_events.g_record_error then
2629 --
2630 hr_utility.set_location ('PERSON ERROR '|| l_proc,10);
2631 --
2632 rollback to before_communications;
2633 --
2634 g_rec.person_id := l_per_rec.person_id;
2635 g_rec.rep_typ_cd := 'ERRCM';
2636 g_rec.error_message_code := benutils.get_message_name;
2637 g_rec.text := fnd_message.get;
2638 --
2639 benutils.write(p_rec => g_rec);
2640 --
2641 -- Update person action to errored as record has an error
2642 --
2643 g_action_rec.person_action_id := l_per_rec.person_action_id;
2644 g_action_rec.action_status_cd := 'E';
2645 g_action_rec.object_version_number := l_per_rec.object_version_number;
2646 g_action_rec.effective_date := g_parm.effective_date;
2647 --
2648 benutils.write(p_rec => g_action_rec);
2649 --
2650 -- Increment the person error count.
2651 --
2652 person_error_cnt;
2653 --
2654 when others then
2655 --
2656 hr_utility.set_location ('PERSON ERROR '|| l_proc,10);
2657 --
2658 rollback to before_communications;
2659 --
2660 g_rec.person_id := l_per_rec.person_id;
2661 g_rec.rep_typ_cd := 'FATAL';
2662 g_rec.error_message_code := sqlcode;
2663 g_rec.text := sqlerrm;
2664 --
2665 benutils.write(p_rec => g_rec);
2666 --
2667 -- Update person action to errored as record has an error
2668 --
2669 g_action_rec.person_action_id := l_per_rec.person_action_id;
2670 g_action_rec.action_status_cd := 'E';
2671 g_action_rec.object_version_number := l_per_rec.object_version_number;
2672 g_action_rec.effective_date := g_parm.effective_date;
2673 --
2674 benutils.write(p_rec => g_action_rec);
2675 --
2676 -- Increment the person error count.
2677 --
2678 person_error_cnt;
2679 --
2680 end;
2681 --
2682 end loop;
2683 --
2684 if g_parm.validate_flag = 'Y' then
2685 rollback;
2686 end if;
2687 --
2688 benutils.write_table_and_file(p_table => true
2689 ,p_file => true);
2690 --
2691 commit;
2692 --
2693 hr_utility.set_location('Leaving ' || l_proc, 10);
2694 --
2695 end process_emrg_events;
2696 --
2697 -- ----------------------------------------------------------------------------
2698 -- ----------------------< process_trgt_mass_mail >----------------------------
2699 -- ----------------------------------------------------------------------------
2700 --
2701 procedure process_trgt_mass_mail
2702 (p_start_person_action_id in number
2703 ,p_end_person_action_id in number) is
2704 --
2705 cursor c_per_actn is
2706 select *
2707 from ben_person_actions bpa
2708 where bpa.benefit_action_id = g_parm.benefit_action_id
2709 and bpa.person_action_id between p_start_person_action_id
2710 and p_end_person_action_id
2711 and bpa.action_status_cd <> 'P'
2712 order by bpa.person_action_id;
2713 --
2714 l_object_version_number number;
2715 l_cm_sent boolean := TRUE;
2716 --
2717 l_proc varchar2(80) := g_package || '.process_trgt_mass_mail';
2718 --
2719 begin
2720 --
2721 hr_utility.set_location('Entering ' || l_proc, 10);
2722 --
2723 for l_per_rec in c_per_actn loop
2724 --
2725 g_person_cnt := g_person_cnt + 1;
2726 --
2727 begin
2728 -- Issue a savepoint to rollback when errors occur.
2729 --
2730 savepoint before_communications;
2731 --
2732 -- generate communications the selected people
2733 --
2734 ben_generate_communications.g_comm_generated := false;
2735 --
2736 ben_generate_communications.main
2737 (p_person_id => l_per_rec.person_id
2738 ,p_cm_trgr_typ_cd => g_parm.cm_trgr_typ_cd
2739 ,p_cm_typ_id => g_parm.cm_typ_id
2740 ,p_pgm_id => g_parm.pgm_id
2741 ,p_pl_id => g_parm.pl_nip_id
2742 ,p_business_group_id => g_parm.business_group_id
2743 ,p_proc_cd1 => 'MSSMLG'
2744 ,p_effective_date => g_parm.effective_date
2745 ,p_mode => g_parm.mode_cd);
2746 --
2747 if (g_parm.audit_log_flag = 'Y') then
2748 chk_per_cm(p_person_id => l_per_rec.person_id);
2749 end if;
2750 --
2751 g_action_rec.person_action_id := l_per_rec.person_action_id;
2752 g_action_rec.action_status_cd := 'P';
2753 g_action_rec.object_version_number := l_per_rec.object_version_number;
2754 g_action_rec.effective_date := g_parm.effective_date;
2755 --
2756 benutils.write(p_rec => g_action_rec);
2757 --
2758 exception
2759 --
2760 when ben_manage_life_events.g_record_error then
2761 --
2762 hr_utility.set_location ('PERSON ERROR '|| l_proc,10);
2763 --
2764 rollback to before_communications;
2765 --
2766 g_rec.rep_typ_cd := 'ERRCM';
2767 g_rec.error_message_code := benutils.get_message_name;
2768 g_rec.text := fnd_message.get;
2769 g_rec.person_id := l_per_rec.person_id;
2770 --
2771 benutils.write(p_rec => g_rec);
2772 --
2773 -- Update person action to errored as record has an error
2774 --
2775 g_action_rec.person_action_id := l_per_rec.person_action_id;
2776 g_action_rec.action_status_cd := 'E';
2777 g_action_rec.object_version_number := l_per_rec.object_version_number;
2778 g_action_rec.effective_date := g_parm.effective_date;
2779 --
2780 benutils.write(p_rec => g_action_rec);
2781 --
2782 -- Increment the person error count.
2783 --
2784 person_error_cnt;
2785 --
2786 when others then
2787 --
2788 hr_utility.set_location ('PERSON ERROR '|| l_proc,10);
2789 --
2790 rollback to before_communications;
2791 --
2792 g_rec.rep_typ_cd := 'FATAL';
2793 g_rec.error_message_code := sqlcode;
2794 g_rec.text := sqlerrm;
2795 g_rec.person_id := l_per_rec.person_id;
2796 --
2797 benutils.write(p_rec => g_rec);
2798 --
2799 -- Update person action to errored as record has an error
2800 --
2801 g_action_rec.person_action_id := l_per_rec.person_action_id;
2802 g_action_rec.action_status_cd := 'E';
2803 g_action_rec.object_version_number := l_per_rec.object_version_number;
2804 g_action_rec.effective_date := g_parm.effective_date;
2805 --
2806 benutils.write(p_rec => g_action_rec);
2807 --
2808 -- Increment the person error count.
2809 --
2810 person_error_cnt;
2811 --
2812 end;
2813 --
2814 end loop;
2815 --
2816 if g_parm.validate_flag = 'Y' then
2817 rollback;
2818 end if;
2819 --
2820 benutils.write_table_and_file(p_table => true
2821 ,p_file => true);
2822 --
2823 commit;
2824 --
2825 hr_utility.set_location('Leaving ' || l_proc, 10);
2826 --
2827 end process_trgt_mass_mail;
2828 --
2829 -- ----------------------------------------------------------------------------
2830 -- --------------------< process_actn_item_reminders >-------------------------
2831 -- ----------------------------------------------------------------------------
2832 --
2833 procedure process_actn_item_reminders
2834 (p_start_person_action_id in number
2835 ,p_end_person_action_id in number) is
2836 --
2837 cursor c_per_actn is
2838 select *
2839 from ben_person_actions bpa
2840 where bpa.benefit_action_id = g_parm.benefit_action_id
2841 and bpa.person_action_id between p_start_person_action_id
2842 and p_end_person_action_id
2843 and bpa.benefit_action_id = g_parm.benefit_action_id
2844 order by bpa.person_action_id;
2845 --
2846 l_cm_sent boolean := TRUE;
2847 --
2848 l_proc varchar2(80) := g_package || '.process_actn_item_reminders';
2849 --
2850 begin
2851 --
2852 hr_utility.set_location('Entering : ' || l_proc, 10);
2853 --
2854 for l_per_rec in c_per_actn loop
2855 --
2856 hr_utility.set_location('Processing person ID : ' ||l_per_rec.person_id,10);
2857 --
2858 begin
2859 --
2860 -- Set a savepoint to rollback errors
2861 --
2862 savepoint before_communications;
2863 --
2864 g_person_cnt := g_person_cnt + 1;
2865 --
2866 -- generate communications for the selected people
2867 --
2868 ben_generate_communications.g_comm_generated := false;
2869 --
2870 ben_generate_communications.main
2871 (p_person_id => l_per_rec.person_id
2872 ,p_cm_trgr_typ_cd => g_parm.cm_trgr_typ_cd
2873 ,p_cm_typ_id => g_parm.cm_typ_id
2874 ,p_ler_id => l_per_rec.ler_id
2875 ,p_actn_typ_id => g_parm.actn_typ_id
2876 ,p_pgm_id => g_parm.pgm_id
2877 ,p_pl_id => g_parm.pl_nip_id
2878 ,p_business_group_id => g_parm.business_group_id
2879 ,p_proc_cd1 => 'MSSMLGAR'
2880 ,p_effective_date => g_parm.effective_date
2881 ,p_mode => g_parm.mode_cd);
2882 --
2883 if (g_parm.audit_log_flag = 'Y') then
2884 chk_per_cm(p_person_id => l_per_rec.person_id);
2885 end if;
2886 --
2887 g_action_rec.person_action_id := l_per_rec.person_action_id;
2888 g_action_rec.action_status_cd := 'P';
2889 g_action_rec.object_version_number := l_per_rec.object_version_number;
2890 g_action_rec.effective_date := g_parm.effective_date;
2891 --
2892 benutils.write(p_rec => g_action_rec);
2893 --
2894 exception
2895 --
2896 when ben_manage_life_events.g_record_error then
2897 --
2898 hr_utility.set_location ('PERSON ERROR '|| l_proc,10);
2899 --
2900 rollback to before_communications;
2901 --
2902 g_rec.rep_typ_cd := 'ERRCM';
2903 g_rec.error_message_code := benutils.get_message_name;
2904 g_rec.text := fnd_message.get;
2905 g_rec.person_id := l_per_rec.person_id;
2906 --
2907 benutils.write(p_rec => g_rec);
2908 --
2909 -- Update the g_action_rec to record an error
2910 --
2911 g_action_rec.person_action_id := l_per_rec.person_action_id;
2912 g_action_rec.action_status_cd := 'E';
2913 g_action_rec.object_version_number := l_per_rec.object_version_number;
2914 g_action_rec.effective_date := g_parm.effective_date;
2915 --
2916 benutils.write(p_rec => g_action_rec);
2917 --
2918 -- Increment the person error count.
2919 --
2920 person_error_cnt;
2921 --
2922 when others then
2923 --
2924 hr_utility.set_location ('PERSON ERROR '|| l_proc,10);
2925 --
2926 rollback to before_communications;
2927 --
2928 g_rec.rep_typ_cd := 'FATAL';
2929 g_rec.error_message_code := sqlcode;
2930 g_rec.text := sqlerrm;
2931 g_rec.person_id := l_per_rec.person_id;
2932 --
2933 benutils.write(p_rec => g_rec);
2934 --
2935 -- Update person action to errored as record has an error
2936 --
2937 g_action_rec.person_action_id := l_per_rec.person_action_id;
2938 g_action_rec.action_status_cd := 'E';
2939 g_action_rec.object_version_number := l_per_rec.object_version_number;
2940 g_action_rec.effective_date := g_parm.effective_date;
2941 --
2942 benutils.write(p_rec => g_action_rec);
2943 --
2944 -- Increment the person error count.
2945 --
2946 person_error_cnt;
2947 --
2948 end;
2949 --
2950 end loop;
2951 --
2952 if g_parm.validate_flag = 'Y' then
2953 rollback;
2954 end if;
2955 --
2956 benutils.write_table_and_file(p_table => true
2957 ,p_file => true);
2958 --
2959 commit;
2960 --
2961 hr_utility.set_location('Leaving : ' || l_proc, 10);
2962 --
2963 end process_actn_item_reminders;
2964 --
2965 -- ----------------------------------------------------------------------------
2966 -- ------------------------< process_enrt_reminders >--------------------------
2967 -- ----------------------------------------------------------------------------
2968 --
2969 procedure process_enrt_reminders
2970 (p_start_person_action_id in number
2971 ,p_end_person_action_id in number) is
2972 --
2973 cursor c_per_actn is
2974 select *
2975 from ben_person_actions bpa
2976 where bpa.benefit_action_id = g_parm.benefit_action_id
2977 and bpa.person_action_id between p_start_person_action_id
2978 and p_end_person_action_id
2979 and bpa.action_status_cd <> 'P'
2980 and bpa.benefit_action_id = g_parm.benefit_action_id
2981 order by bpa.person_action_id;
2982 --
2983 l_cm_sent boolean := TRUE;
2984 --
2985 l_proc varchar2(80) := g_package || '.process_enrt_reminders';
2986 l_asnd_lf_evt_dt date;
2987 --
2988 begin
2989 --
2990 hr_utility.set_location('Entering ' || l_proc, 10);
2991 --
2992 for l_per_rec in c_per_actn loop
2993 --
2994 g_person_cnt := g_person_cnt + 1;
2995 --
2996 begin
2997 --
2998 -- Set a savepoint to rollback errors.
2999 --
3000 savepoint before_communications;
3001 --
3002 -- generate communications for the selected people
3003 --
3004 ben_generate_communications.g_comm_generated := false;
3005 --
3006 l_asnd_lf_evt_dt := fnd_date.canonical_to_date(g_parm.lf_evt_ocrd_dt);
3007 ben_generate_communications.main
3008 (p_person_id => l_per_rec.person_id
3009 ,p_cm_trgr_typ_cd => g_parm.cm_trgr_typ_cd
3010 ,p_cm_typ_id => g_parm.cm_typ_id
3011 ,p_ler_id => l_per_rec.ler_id
3012 ,p_pgm_id => g_parm.pgm_id
3013 ,p_pl_id => g_parm.pl_nip_id
3014 -- PB : 5422 :
3015 -- ,p_enrt_perd_id => g_parm.popl_enrt_typ_cycl_id
3016 ,p_asnd_lf_evt_dt => l_asnd_lf_evt_dt -- fnd_date.canonical_to_date(g_parm.lf_evt_ocrd_dt)
3017 ,p_business_group_id => g_parm.business_group_id
3018 ,p_proc_cd1 => 'MSSMLGER'
3019 ,p_effective_date => g_parm.effective_date
3020 ,p_mode => g_parm.mode_cd);
3021 --
3022 if (g_parm.audit_log_flag = 'Y') then
3023 chk_per_cm(p_person_id => l_per_rec.person_id);
3024 end if;
3025 --
3026 g_action_rec.person_action_id := l_per_rec.person_action_id;
3027 g_action_rec.action_status_cd := 'P';
3028 g_action_rec.object_version_number := l_per_rec.object_version_number;
3029 g_action_rec.effective_date := g_parm.effective_date;
3030 --
3031 benutils.write(p_rec => g_action_rec);
3032 --
3033 exception
3034 --
3035 when ben_manage_life_events.g_record_error then
3036 --
3037 hr_utility.set_location ('PERSON ERROR '|| l_proc,10);
3038 --
3039 rollback to before_communications;
3040 --
3041 benutils.write(p_text => fnd_message.get);
3042 --
3043 g_rec.rep_typ_cd := 'ERRCM';
3044 g_rec.error_message_code := benutils.get_message_name;
3045 g_rec.text := fnd_message.get;
3046 g_rec.person_id := l_per_rec.person_id;
3047 --
3048 benutils.write(p_rec => g_rec);
3049 --
3050 -- Update person action to errored as record has an error
3051 --
3052 g_action_rec.person_action_id := l_per_rec.person_action_id;
3053 g_action_rec.action_status_cd := 'E';
3054 g_action_rec.object_version_number := l_per_rec.object_version_number;
3055 g_action_rec.effective_date := g_parm.effective_date;
3056 --
3057 benutils.write(p_rec => g_action_rec);
3058 --
3059 -- Increment the person error count.
3060 --
3061 person_error_cnt;
3062 --
3063 when others then
3064 --
3065 hr_utility.set_location ('PERSON ERROR '|| l_proc,10);
3066 --
3067 rollback to before_communications;
3068 --
3069 g_rec.rep_typ_cd := 'FATAL';
3070 g_rec.error_message_code := sqlcode;
3071 g_rec.text := sqlerrm;
3072 g_rec.person_id := l_per_rec.person_id;
3073 --
3074 benutils.write(p_rec => g_rec);
3075 --
3076 -- Update person action to errored as record has an error
3077 --
3078 g_action_rec.person_action_id := l_per_rec.person_action_id;
3079 g_action_rec.action_status_cd := 'E';
3080 g_action_rec.object_version_number := l_per_rec.object_version_number;
3081 g_action_rec.effective_date := g_parm.effective_date;
3082 --
3083 benutils.write(p_rec => g_action_rec);
3084 --
3085 -- Increment the person error count.
3086 --
3087 person_error_cnt;
3088 --
3089 end;
3090 --
3091 end loop;
3092 --
3093 if g_parm.validate_flag = 'Y' then
3094 rollback;
3095 end if;
3096 --
3097 benutils.write_table_and_file(p_table => true
3098 ,p_file => true);
3099 --
3100 commit;
3101 --
3102 hr_utility.set_location('Leaving ' || l_proc, 10);
3103 --
3104 end process_enrt_reminders;
3105 --
3106 -- ----------------------------------------------------------------------------
3107 -- ---------------------------------< restart >--------------------------------
3108 -- ----------------------------------------------------------------------------
3109 --
3110 procedure restart
3111 (errbuf out nocopy varchar2
3112 ,retcode out nocopy number
3113 ,p_benefit_action_id in number) is
3114 --
3115 -- Cursor Declaration
3116 --
3117 cursor c_parm is
3118 select *
3119 from ben_benefit_actions ben
3120 where ben.benefit_action_id = p_benefit_action_id;
3121 --
3122 l_parm c_parm%rowtype;
3123 --
3124 l_errbuf varchar2(80);
3125 l_retcode number;
3126 l_proc varchar2(80) := g_package || '.restart';
3127 --
3128 begin
3129 --
3130 --
3131 hr_utility.set_location('Entering : ' || l_proc, 10);
3132 --
3133 -- get the parameters for a previous run and do a restart
3134 --
3135 open c_parm;
3136 fetch c_parm into l_parm;
3137 --
3138 if c_parm%notfound then
3139 close c_parm;
3140 fnd_message.set_name('BEN', 'BEN_91753_BENEFIT_ACTION_ERR');
3141 fnd_message.set_token('PROC',l_proc);
3142 fnd_message.raise_error;
3143 end if;
3144 --
3145 close c_parm;
3146 --
3147 process
3148 (errbuf => l_errbuf
3149 ,retcode => l_retcode
3150 ,p_benefit_action_id => p_benefit_action_id
3151 ,p_validate => l_parm.validate_flag
3152 ,p_effective_date => l_parm.process_date
3153 ,p_business_group_id => l_parm.business_group_id
3154 ,p_mode => l_parm.mode_cd
3155 ,p_cm_trgr_typ_cd => l_parm.cm_trgr_typ_cd
3156 ,p_cm_typ_id => l_parm.cm_typ_id
3157 ,p_person_id => l_parm.person_id
3158 ,p_person_type_id => l_parm.person_type_id
3159 ,p_person_sel_rl => l_parm.person_selection_rl
3160 ,p_organization_id => l_parm.organization_id
3161 ,p_location_id => l_parm.location_id
3162 ,p_ler_id => l_parm.ler_id
3163 ,p_pgm_id => l_parm.pgm_id
3164 ,p_pl_nip_id => l_parm.pl_id
3165 ,p_plan_in_pgm_flag => l_parm.no_programs_flag
3166 ,p_comp_selection_rl => l_parm.comp_selection_rl
3167 ,p_actn_typ_id => l_parm.actn_typ_id
3168 ,p_elig_enrol_cd => l_parm.elig_enrol_cd
3169 ,p_use_fctr_to_sel_flag => l_parm.use_fctr_to_sel_flag
3170 ,p_age_fctr_id => l_parm.age_fctr_id
3171 ,p_min_age => l_parm.min_age
3172 ,p_max_age => l_parm.max_age
3173 ,p_los_fctr_id => l_parm.los_fctr_id
3174 ,p_min_los => l_parm.min_los
3175 ,p_max_los => l_parm.max_los
3176 ,p_los_det_to_use_cd => l_parm.los_det_to_use_cd
3177 ,p_cmbn_age_los_fctr_id => l_parm.cmbn_age_los_fctr_id
3178 ,p_date_from => l_parm.date_from
3179 --
3180 -- PB : 5422 :
3181 -- ,p_popl_enrt_typ_cycl_id => l_parm.popl_enrt_typ_cycl_id
3182 ,p_lf_evt_ocrd_dt => fnd_date.date_to_canonical(l_parm.lf_evt_ocrd_dt)
3183 ,p_audit_log_flag => l_parm.audit_log_flag);
3184 --
3185 hr_utility.set_location('Leaving : ' || l_proc, 10);
3186 --
3187 end restart;
3188 --
3189 -- ----------------------------------------------------------------------------
3190 -- ----------------------------< do_multithread >------------------------------
3191 -- ----------------------------------------------------------------------------
3192 --
3193 procedure do_multithread
3194 (errbuf out nocopy varchar2
3195 ,retcode out nocopy number
3196 ,p_validate in varchar2 default 'N'
3197 ,p_benefit_action_id in number
3198 ,p_thread_id in number
3199 ,p_effective_date in varchar2
3200 ,p_business_group_id in number) is
3201 --
3202 cursor c_range_thread is
3203 select ran.rowid,
3204 ran.starting_person_action_id,
3205 ran.ending_person_action_id
3206 from ben_batch_ranges ran
3207 where ran.range_status_cd = 'U'
3208 and ran.benefit_action_id = p_benefit_action_id
3209 and rownum < 2
3210 for update of ran.range_status_cd;
3211 --
3212 l_range_rec c_range_thread%rowtype;
3213 --
3214 cursor c_parm is
3215 select ben.benefit_action_id
3216 ,ben.validate_flag
3217 ,ben.process_date
3218 ,ben.business_group_id
3219 ,ben.mode_cd
3220 ,ben.cm_trgr_typ_cd
3221 ,ben.cm_typ_id
3222 ,ben.person_id
3223 ,ben.person_type_id
3224 ,ben.person_selection_rl
3225 ,ben.organization_id
3226 ,ben.location_id
3227 ,ben.ler_id
3228 ,ben.pgm_id
3229 ,ben.pl_id
3230 ,ben.no_programs_flag
3231 ,ben.comp_selection_rl
3232 ,ben.actn_typ_id
3233 ,ben.elig_enrol_cd
3234 ,ben.use_fctr_to_sel_flag
3235 ,ben.age_fctr_id
3236 ,ben.min_age
3237 ,ben.max_age
3238 ,ben.los_fctr_id
3239 ,ben.min_los
3240 ,ben.max_los
3241 ,ben.los_det_to_use_cd
3242 ,ben.cmbn_age_los_fctr_id
3243 ,ben.date_from
3244 -- PB : 5422 :
3245 -- ,ben.popl_enrt_typ_cycl_id
3246 ,ben.lf_evt_ocrd_dt
3247 ,ben.audit_log_flag
3248 from ben_benefit_actions ben
3249 where ben.benefit_action_id = p_benefit_action_id;
3250 --
3251 l_effective_date date;
3252 l_proc varchar2(80) := g_package || '.do_multithread';
3253 l_commit number;
3254 --
3255 begin
3256 --
3257 hr_utility.set_location('Entering : ' || l_proc, 10);
3258 --
3259 -- Convert varchar2 dates to real dates
3260 -- 1) First remove time component
3261 -- 2) Next convert format
3262 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
3263 l_effective_date := to_date(to_char(trunc(l_effective_date),'DD-MON-RRRR')
3264 ,'DD-MON-RRRR');
3265 --
3266 -- Put row in fnd_sessions
3267 --
3268 dt_fndate.change_ses_date
3269 (p_ses_date => l_effective_date,
3270 p_commit => l_commit);
3271 --
3272 -- This process could be either a continuation of the master process or could
3273 -- be a spawned request from the master process in which case the globals will
3274 -- be empty. Get all the parameters just in case
3275 --
3276 -- Get the chunk size and threads for the batch process.
3277 --
3278 benutils.get_parameter
3279 (p_business_group_id => p_business_group_id
3280 ,p_batch_exe_cd => 'BENTMPCM'
3281 ,p_threads => g_threads
3282 ,p_chunk_size => g_chunk_size
3283 ,p_max_errors => g_max_errors);
3284 --
3285 -- Load the global parameter list.
3286 --
3287 open c_parm;
3288 fetch c_parm into g_parm;
3289 close c_parm;
3290 --
3291 hr_utility.set_location(g_parm.business_group_id,100000);
3292 -- Set up benefits environment
3293 --
3294 ben_env_object.init(p_business_group_id => p_business_group_id,
3295 p_effective_date => l_effective_date,
3296 p_thread_id => p_thread_id,
3297 p_chunk_size => g_chunk_size,
3298 p_threads => g_threads,
3299 p_max_errors => g_max_errors,
3300 p_benefit_action_id => p_benefit_action_id);
3301 --
3302 -- Set the global for the current thread being processed.
3303 --
3304 g_current_thread := p_thread_id;
3305 benutils.g_benefit_action_id := p_benefit_action_id;
3306 benutils.g_thread_id := p_thread_id;
3307 --
3308 -- Print parameters
3309 --
3310 print_parameters;
3311 --
3312 -- The processing for this thread is as follows:
3313 -- 1) Lock the rows in ben_batch_ranges that are not processed.
3314 -- 2) Fetch the start and ending person action id for the range.
3315 -- 3) Process the rows in ben_person_action that fall in the range based on
3316 -- the trigger type.
3317 -- 4) Go to number 1 again and repeat until all ranges are processed.
3318 --
3319 loop
3320 --
3321 open c_range_thread;
3322 fetch c_range_thread into l_range_rec;
3323 --
3324 if c_range_thread%notfound then
3325 close c_range_thread;
3326 exit;
3327 end if;
3328 --
3329 close c_range_thread;
3330 --
3331 update ben_batch_ranges
3332 set range_status_cd = 'P'
3333 where rowid = l_range_rec.rowid;
3334 --
3335 commit;
3336 --
3337 if g_parm.cm_trgr_typ_cd = 'ENRTRMDR' then
3338 --
3339 process_enrt_reminders
3340 (p_start_person_action_id => l_range_rec.starting_person_action_id
3341 ,p_end_person_action_id => l_range_rec.ending_person_action_id);
3342 --
3343 elsif g_parm.cm_trgr_typ_cd = 'ACTNRMDR' then
3344 --
3345 process_actn_item_reminders
3346 (p_start_person_action_id => l_range_rec.starting_person_action_id
3347 ,p_end_person_action_id => l_range_rec.ending_person_action_id);
3348 --
3349 elsif g_parm.cm_trgr_typ_cd = 'EMRGEVT' then
3350 --
3351 process_emrg_events
3352 (p_start_person_action_id => l_range_rec.starting_person_action_id
3353 ,p_end_person_action_id => l_range_rec.ending_person_action_id);
3354 --
3355 elsif g_parm.cm_trgr_typ_cd = 'MSSMLG' then
3356 --
3357 process_trgt_mass_mail
3358 (p_start_person_action_id => l_range_rec.starting_person_action_id
3359 ,p_end_person_action_id => l_range_rec.ending_person_action_id);
3360 --
3361 end if;
3362 --
3363 end loop;
3364 --
3365 check_all_slaves_finished;
3366 --
3367 -- Write statistics of the process to a log file
3368 --
3369 write_logfile;
3370 --
3371 hr_utility.set_location('Leaving : ' || l_proc, 10);
3372 --
3373 exception
3374 --
3375 when others then
3376 --
3377 rollback;
3378 benutils.write(p_text => sqlerrm);
3379 hr_utility.set_location ('BENTMPCM Error '|| l_proc,10);
3380 g_rec.rep_typ_cd := 'FATAL';
3381 g_rec.error_message_code := sqlcode;
3382 g_rec.text := fnd_message.get;
3383 benutils.write(p_rec => g_rec);
3384 --
3385 -- Output log information to log file
3386 --
3387 write_logfile;
3388 --
3389 check_all_slaves_finished;
3390 --
3391 fnd_message.set_name('BEN','BEN_92172_BENTMPCM_ERRORED');
3392 fnd_message.set_token('PROC',l_proc);
3393 benutils.write(p_text => fnd_message.get);
3394 benutils.write_table_and_file(p_table => true,
3395 p_file => true);
3396 commit;
3397 --
3398 fnd_message.raise_error;
3399 --
3400 end do_multithread;
3401 --
3402 -- ----------------------------------------------------------------------------
3403 -- ---------------------------< create_actions_ranges >------------------------
3404 -- ----------------------------------------------------------------------------
3405 --
3406 -- This procedure creates person actions and batch ranges based on the chunk
3407 -- size. The in-out parameters keep track of the person action ids created.
3408 --
3409 procedure create_actions_ranges
3410 (p_person_id in number default null
3411 ,p_ler_id in number default null
3412 ,p_start_person_action_id in out nocopy number
3413 ,p_ending_person_action_id in out nocopy number) is
3414 --
3415 l_person_ok varchar2(1) := 'Y';
3416 l_person_action_id number;
3417 l_object_version_number number;
3418 l_range_id number;
3419
3420 -- For Nocopy changes
3421 l_start_person_action_id number := p_start_person_action_id;
3422 l_ending_person_action_id number := p_ending_person_action_id;
3423 --
3424 l_proc varchar2(80) := g_package || '.create_actions_ranges';
3425 --
3426 begin
3427 --
3428 hr_utility.set_location('Entering : ' || l_proc, 10);
3429 --
3430 if g_parm.person_sel_rl is not null then
3431 --
3432 hr_utility.set_location('Verifying rul',10);
3433 if not ben_rules.chk_person_selection
3434 (p_person_id => p_person_id
3435 ,p_business_group_id => g_parm.business_group_id
3436 ,p_person_selection_rule_id=> g_parm.person_sel_rl
3437 ,p_effective_date => g_parm.effective_date
3438 ,p_ler_id => p_ler_id) then
3439 --
3440 l_person_ok := 'N';
3441 --
3442 end if;
3443 --
3444 end if;
3445 --
3446 --if not(verify_person_type_id(p_person_id => p_person_id,
3447 -- p_person_type_id => g_parm.person_type_id)) then
3448 -- --
3449 -- hr_utility.set_location('Verifying person type',10);
3450 -- l_person_ok := 'N';
3451 -- --
3452 --end if;
3453 --
3454 -- Create a person action only if the person passes the person selection rule
3455 -- above. The default (if no person selecion rule was passed in) is 'Y'.
3456 --
3457 if l_person_ok = 'Y' then
3458 --
3459 ben_person_actions_api.create_person_actions
3460 (p_validate => FALSE
3461 ,p_person_action_id => l_person_action_id
3462 ,p_person_id => p_person_id
3463 ,p_ler_id => p_ler_id
3464 ,p_benefit_action_id => g_parm.benefit_action_id
3465 ,p_action_status_cd => 'U'
3466 ,p_chunk_number => null
3467 ,p_object_version_number => l_object_version_number
3468 ,p_effective_date => g_parm.effective_date);
3469 --
3470 g_person_actn_cnt := g_person_actn_cnt + 1;
3471 --
3472 -- We have to create batch ranges based on the number of person actions
3473 -- inserted and the chunk size. Keep track of the starting person action id
3474 -- by setting the out parameter.
3475 --
3476 if mod(g_person_actn_cnt, g_chunk_size) = 1 or g_chunk_size = 1 then
3477 p_start_person_action_id := l_person_action_id;
3478 end if;
3479 --
3480 p_ending_person_action_id := l_person_action_id;
3481 --
3482 if mod(g_person_actn_cnt, g_chunk_size) = 0 or g_chunk_size = 1 then
3483 --
3484 ben_batch_ranges_api.create_batch_ranges
3485 (p_validate => FALSE
3486 ,p_effective_date => g_parm.effective_date
3487 ,p_benefit_action_id => g_parm.benefit_action_id
3488 ,p_range_id => l_range_id
3489 ,p_range_status_cd => 'U'
3490 ,p_starting_person_action_id => p_start_person_action_id
3491 ,p_ending_person_action_id => p_ending_person_action_id
3492 ,p_object_version_number => l_object_version_number);
3493 --
3494 g_num_ranges := g_num_ranges + 1;
3495 --
3496 end if;
3497 --
3498 end if;
3499 --
3500 hr_utility.set_location('Leaving : ' || l_proc, 10);
3501 --
3502 exception
3503 when others then
3504 -- For nocopy changes
3505 p_start_person_action_id := l_start_person_action_id;
3506 p_ending_person_action_id := l_ending_person_action_id;
3507
3508 raise;
3509 --
3510 end create_actions_ranges;
3511 --
3512 -- ----------------------------------------------------------------------------
3513 -- ------------------------< create_enrtrmdr_ranges >--------------------------
3514 -- ----------------------------------------------------------------------------
3515 --
3516 procedure create_enrtrmdr_ranges is
3517 --
3518 -- Cursor to select rows from ben_per_in_ler for people that haven't enrolled
3519 -- in a plan or a program as of the effective date
3520 --
3521 cursor c_pil_enrtrmdr is
3522 select distinct pil.person_id, pil.ler_id
3523 from ben_per_in_ler pil,
3524 ben_pil_elctbl_chc_popl pel,
3525 ben_ler_f ler
3526 where (g_parm.person_id is null or
3527 pil.person_id = g_parm.person_id)
3528 and pil.per_in_ler_stat_cd = 'STRTD'
3529 and pil.business_group_id = g_parm.business_group_id
3530 and pil.per_in_ler_id = pel.per_in_ler_id
3531 and pil.ler_id = ler.ler_id
3532 and g_parm.effective_date between
3533 ler.effective_start_date and ler.effective_end_date
3534 and ler.typ_cd not in ('COMP', 'GSP', 'ABS')
3535 and pel.elcns_made_dt is null
3536 and g_parm.effective_date between
3537 nvl(pel.enrt_perd_strt_dt, g_parm.effective_date) and
3538 nvl(pel.enrt_perd_end_dt, g_parm.effective_date)
3539 --
3540 and (g_parm.person_type_id is null
3541 or
3542 exists (select null
3543 from per_person_type_usages_f ppu
3544 where pil.person_id = ppu.person_id
3545 and ppu.person_type_id = g_parm.person_type_id
3546 and g_parm.effective_date
3547 between ppu.effective_start_date
3548 and ppu.effective_end_date))
3549 --
3550 and (g_parm.pgm_id is null or
3551 pel.pgm_id = g_parm.pgm_id)
3552 and ((g_parm.pl_nip_id is null
3553 and g_parm.plan_in_pgm_flag = 'Y'
3554 and pel.pgm_id is not null)
3555 or
3556 (g_parm.pl_nip_id is null
3557 and g_parm.plan_in_pgm_flag = 'N'
3558 and pel.pgm_id is null)
3559 or
3560 (g_parm.pl_nip_id = pel.pl_id
3561 and g_parm.plan_in_pgm_flag = 'N'))
3562 and (g_parm.ler_id is null or
3563 pil.ler_id = g_parm.ler_id)
3564 and ((g_parm.organization_id is null and
3565 g_parm.location_id is null)
3566 or
3567 /* check if the person belongs to the org or location specified */
3568 /* Bug 5435002 */
3569 exists (select 's'
3570 FROM per_all_assignments_f asg, per_assignment_status_types ast
3571 WHERE asg.person_id = pil.person_id
3572 AND asg.primary_flag = 'Y'
3573 and (g_parm.organization_id is null or
3574 asg.organization_id = g_parm.organization_id)
3575 and (g_parm.location_id is null or
3576 asg.location_id = g_parm.location_id)
3577 AND g_parm.effective_date
3578 BETWEEN asg.effective_start_date AND asg.effective_end_date
3579 AND asg.assignment_status_type_id = ast.assignment_status_type_id
3580 and asg.business_group_id = g_parm.business_group_id
3581 AND ( ( assignment_type = 'E'
3582 AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
3583 OR ( ast.per_system_status = 'TERM_ASSIGN'
3584 AND NOT EXISTS (
3585 SELECT assignment_id
3586 FROM per_all_assignments_f asg1, per_assignment_status_types ast1
3587 WHERE asg1.assignment_type = 'B'
3588 AND asg1.primary_flag = 'Y'
3589 AND asg1.person_id = pil.person_id
3590 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
3591 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
3592 AND g_parm.effective_date
3593 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3594 )
3595 )
3596 )
3597 )
3598 OR ( assignment_type = 'B'
3599 AND NOT EXISTS (
3600 SELECT assignment_id
3601 FROM per_all_assignments_f asg2, per_assignment_status_types ast2
3602 WHERE asg2.assignment_type = 'E'
3603 AND asg2.primary_flag = 'Y'
3604 AND asg2.person_id = pil.person_id
3605 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
3606 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
3607 AND g_parm.effective_date
3608 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
3609 )
3610 )
3611 )
3612 )
3613 );
3614 --
3615 l_start_person_action_id number;
3616 l_ending_person_action_id number;
3617 l_range_id number;
3618 l_object_version_number number;
3619 --
3620 l_proc varchar2(80) := g_package || '.create_enrtrmdr_ranges';
3621 --
3622 begin
3623 --
3624 hr_utility.set_location('Entering : ' || l_proc, 10);
3625 --
3626 hr_utility.set_location('Creating actions and ranges ' || l_proc, 15);
3627 --
3628 for l_per_rec in c_pil_enrtrmdr loop
3629 --
3630 create_actions_ranges
3631 (p_person_id => l_per_rec.person_id
3632 ,p_ler_id => l_per_rec.ler_id
3633 ,p_start_person_action_id => l_start_person_action_id
3634 ,p_ending_person_action_id => l_ending_person_action_id);
3635 --
3636 end loop;
3637 --
3638 -- There could be a few person actions left over from the call in the for
3639 -- loop above. Create a batch range for them.
3640 --
3641 If g_person_actn_cnt > 0 and
3642 mod(g_person_actn_cnt, g_chunk_size) <> 0 then
3643 --
3644 hr_utility.set_location('Ranges for remaining people ' || l_proc, 25);
3645 --
3646 ben_batch_ranges_api.create_batch_ranges
3647 (p_validate => FALSE
3648 ,p_effective_date => g_parm.effective_date
3649 ,p_benefit_action_id => g_parm.benefit_action_id
3650 ,p_range_id => l_range_id
3651 ,p_range_status_cd => 'U'
3652 ,p_starting_person_action_id => l_start_person_action_id
3653 ,p_ending_person_action_id => l_ending_person_action_id
3654 ,p_object_version_number => l_object_version_number);
3655 --
3656 g_num_ranges := g_num_ranges + 1;
3657 --
3658 end if;
3659 --
3660 hr_utility.set_location('Leaving : ' || l_proc, 10);
3661 --
3662 end create_enrtrmdr_ranges;
3663 --
3664 -- ----------------------------------------------------------------------------
3665 -- ------------------------< create_actnrmdr_ranges >--------------------------
3666 -- ----------------------------------------------------------------------------
3667 --
3668 procedure create_actnrmdr_ranges is
3669 --
3670 cursor c_per_actnrmdr is
3671 select distinct ppf.person_id
3672 from per_all_people_f ppf,
3673 ben_prtt_enrt_rslt_f pen,
3674 ben_per_in_ler pil,
3675 ben_prtt_enrt_actn_f pea
3676 where (g_parm.person_id is null or
3677 ppf.person_id = g_parm.person_id)
3678 and g_parm.effective_date between ppf.effective_start_date
3679 and ppf.effective_end_date
3680 and ppf.business_group_id = g_parm.business_group_id
3681 and pen.person_id = ppf.person_id
3682 and pen.ler_id = nvl(g_parm.ler_id, pen.ler_id)
3683 and pen.business_group_id = g_parm.business_group_id
3684 and g_parm.effective_date between pen.effective_start_date
3685 and pen.effective_end_date
3686 and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
3687 and pen.prtt_enrt_rslt_stat_cd is null
3688 and pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
3689 and (g_parm.actn_typ_id is null or
3690 pea.actn_typ_id = g_parm.actn_typ_id)
3691 and g_parm.effective_date between pea.effective_start_date
3692 and pea.effective_end_date
3693 and pea.business_group_id = g_parm.business_group_id
3694 --
3695 and (g_parm.person_type_id is null
3696 or
3697 exists (select null
3698 from per_person_type_usages_f ppu
3699 where ppf.person_id = ppu.person_id
3700 and ppu.person_type_id = g_parm.person_type_id
3701 and g_parm.effective_date
3702 between ppu.effective_start_date
3703 and ppu.effective_end_date))
3704 --
3705 and pea.cmpltd_dt is null
3706 and (g_parm.pgm_id is null or
3707 pen.pgm_id = g_parm.pgm_id)
3708 and ((g_parm.pl_nip_id is null
3709 and g_parm.plan_in_pgm_flag = 'Y'
3710 and pen.pgm_id is not null)
3711 or
3712 (g_parm.pl_nip_id is null
3713 and g_parm.plan_in_pgm_flag = 'N'
3714 and pen.pgm_id is null)
3715 or
3716 (g_parm.pl_nip_id = pen.pl_id))
3717 and ((g_parm.organization_id is null and
3718 g_parm.location_id is null)
3719 or
3720 /* check if the person belongs to the org or location specified */
3721 /* Bug 5435002 */
3722 exists (select 's'
3723 FROM per_all_assignments_f asg, per_assignment_status_types ast
3724 WHERE asg.person_id = ppf.person_id
3725 AND asg.primary_flag = 'Y'
3726 and (g_parm.organization_id is null or
3727 asg.organization_id = g_parm.organization_id)
3728 and (g_parm.location_id is null or
3729 asg.location_id = g_parm.location_id)
3730 AND g_parm.effective_date
3731 BETWEEN asg.effective_start_date AND asg.effective_end_date
3732 AND asg.assignment_status_type_id = ast.assignment_status_type_id
3733 and asg.business_group_id = g_parm.business_group_id
3734 AND ( ( assignment_type = 'E'
3735 AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
3736 OR ( ast.per_system_status = 'TERM_ASSIGN'
3737 AND NOT EXISTS (
3738 SELECT assignment_id
3739 FROM per_all_assignments_f asg1, per_assignment_status_types ast1
3740 WHERE asg1.assignment_type = 'B'
3741 AND asg1.primary_flag = 'Y'
3742 AND asg1.person_id = ppf.person_id
3743 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
3744 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
3745 AND g_parm.effective_date
3746 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3747 )
3748 )
3749 )
3750 )
3751 OR ( assignment_type = 'B'
3752 AND NOT EXISTS (
3753 SELECT assignment_id
3754 FROM per_all_assignments_f asg2, per_assignment_status_types ast2
3755 WHERE asg2.assignment_type = 'E'
3756 AND asg2.primary_flag = 'Y'
3757 AND asg2.person_id = ppf.person_id
3758 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
3759 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
3760 AND g_parm.effective_date
3761 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
3762 )
3763 )
3764 )
3765 )
3766 )
3767 and pil.per_in_ler_id(+)=pea.per_in_ler_id
3768 and pil.business_group_id(+)=pea.business_group_id
3769 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
3770 -- found row condition
3771 or pil.per_in_ler_stat_cd is null
3772 -- outer join condition
3773 )
3774 ;
3775 --
3776 l_start_person_action_id number;
3777 l_ending_person_action_id number;
3778 l_range_id number;
3779 l_object_version_number number;
3780 --
3781 l_proc varchar2(80) := g_package || '.create_actnrmdr_ranges';
3782 --
3783 begin
3784 --
3785 hr_utility.set_location('Entering : ' || l_proc, 10);
3786 --
3787 for l_per_rec in c_per_actnrmdr loop
3788 --
3789 -- Create person actions and batch ranges.
3790 --
3791 create_actions_ranges
3792 (p_person_id => l_per_rec.person_id
3793 ,p_start_person_action_id => l_start_person_action_id
3794 ,p_ending_person_action_id => l_ending_person_action_id);
3795 --
3796 end loop;
3797 --
3798 -- There could be a few person actions left over from the call in the for
3799 -- loop above. Create a batch range for them.
3800 --
3801 If g_person_actn_cnt > 0 and
3802 mod(g_person_actn_cnt, g_chunk_size) <> 0 then
3803 --
3804 hr_utility.set_location('Ranges for remaining people ' || l_proc, 25);
3805 --
3806 ben_batch_ranges_api.create_batch_ranges
3807 (p_validate => FALSE
3808 ,p_effective_date => g_parm.effective_date
3809 ,p_benefit_action_id => g_parm.benefit_action_id
3810 ,p_range_id => l_range_id
3811 ,p_range_status_cd => 'U'
3812 ,p_starting_person_action_id => l_start_person_action_id
3813 ,p_ending_person_action_id => l_ending_person_action_id
3814 ,p_object_version_number => l_object_version_number);
3815 --
3816 g_num_ranges := g_num_ranges + 1;
3817 --
3818 end if;
3819 --
3820 hr_utility.set_location('Leaving : ' || l_proc, 10);
3821 --
3822 end create_actnrmdr_ranges;
3823 --
3824 -- ----------------------------------------------------------------------------
3825 -- ------------------------< create_emrgevt_ranges >---------------------------
3826 -- ----------------------------------------------------------------------------
3827 --
3828 procedure create_emrgevt_ranges is
3829 --
3830 -- Cursor to select people from per_people_f for the emerging event literature
3831 --
3832 cursor c_per_emrgevt is
3833 select distinct ppf.person_id
3834 from per_people_f ppf
3835 where (g_parm.person_id is null or
3836 ppf.person_id = g_parm.person_id)
3837 and ppf.business_group_id = g_parm.business_group_id
3838 and (g_parm.date_from is null or
3839 g_parm.date_from between ppf.effective_start_date
3840 and ppf.effective_end_date)
3841 and g_parm.effective_date between ppf.effective_start_date
3842 and ppf.effective_end_date
3843 and (g_parm.person_type_id is null
3844 or
3845 exists (select null
3846 from per_person_type_usages_f ppu
3847 where ppf.person_id = ppu.person_id
3848 and ppu.person_type_id = g_parm.person_type_id
3849 and g_parm.effective_date
3850 between ppu.effective_start_date
3851 and ppu.effective_end_date))
3852 and ((g_parm.organization_id is null and
3853 g_parm.location_id is null)
3854 or
3855 /* check if the person belongs to the org or location specified */
3856 /* Bug 5435002 */
3857 exists (select 's'
3858 FROM per_all_assignments_f asg, per_assignment_status_types ast
3859 WHERE asg.person_id = ppf.person_id
3860 AND asg.primary_flag = 'Y'
3861 and (g_parm.organization_id is null or
3862 asg.organization_id = g_parm.organization_id)
3863 and (g_parm.location_id is null or
3864 asg.location_id = g_parm.location_id)
3865 AND g_parm.effective_date
3866 BETWEEN asg.effective_start_date AND asg.effective_end_date
3867 AND asg.assignment_status_type_id = ast.assignment_status_type_id
3868 and asg.business_group_id = g_parm.business_group_id
3869 AND ( ( assignment_type = 'E'
3870 AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
3871 OR ( ast.per_system_status = 'TERM_ASSIGN'
3872 AND NOT EXISTS (
3873 SELECT assignment_id
3874 FROM per_all_assignments_f asg1, per_assignment_status_types ast1
3875 WHERE asg1.assignment_type = 'B'
3876 AND asg1.primary_flag = 'Y'
3877 AND asg1.person_id = ppf.person_id
3878 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
3879 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
3880 AND g_parm.effective_date
3881 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3882 )
3883 )
3884 )
3885 )
3886 OR ( assignment_type = 'B'
3887 AND NOT EXISTS (
3888 SELECT assignment_id
3889 FROM per_all_assignments_f asg2, per_assignment_status_types ast2
3890 WHERE asg2.assignment_type = 'E'
3891 AND asg2.primary_flag = 'Y'
3892 AND asg2.person_id = ppf.person_id
3893 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
3894 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
3895 AND g_parm.effective_date
3896 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
3897 )
3898 )
3899 )
3900 )
3901 );
3902 --
3903 cursor c_elig(v_person_id number) is
3904 select elig.pgm_id,
3905 elig.pl_id
3906 from ben_elig_per_f elig,
3907 ben_per_in_ler pil
3908 where elig.person_id = v_person_id
3909 and (g_parm.pgm_id is null or
3910 elig.pgm_id = g_parm.pgm_id)
3911 and ((g_parm.pl_nip_id is null
3912 and g_parm.plan_in_pgm_flag = 'Y'
3913 and elig.pgm_id is not null)
3914 or
3915 (g_parm.pl_nip_id is null
3916 and g_parm.plan_in_pgm_flag = 'N'
3917 and elig.pgm_id is null)
3918 or
3919 (g_parm.pl_nip_id = elig.pl_id))
3920 and elig.elig_flag = 'Y'
3921 and g_parm.effective_date between elig.effective_start_date
3922 and elig.effective_end_date
3923 and pil.per_in_ler_id(+)=elig.per_in_ler_id
3924 and pil.business_group_id(+)=elig.business_group_id
3925 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
3926 -- found row condition
3927 or pil.per_in_ler_stat_cd is null
3928 -- outer join condition
3929 );
3930 --
3931 cursor c_enrt(v_person_id number) is
3932 select pen.pgm_id,
3933 pen.pl_id
3934 from ben_prtt_enrt_rslt_f pen
3935 where pen.person_id = v_person_id
3936 and (g_parm.pgm_id is null or
3937 pen.pgm_id = g_parm.pgm_id)
3938 and ((g_parm.pl_nip_id is null
3939 and g_parm.plan_in_pgm_flag = 'Y'
3940 and pen.pgm_id is not null)
3941 or
3942 (g_parm.pl_nip_id is null
3943 and g_parm.plan_in_pgm_flag = 'N'
3944 and pen.pgm_id is null)
3945 or
3946 (g_parm.pl_nip_id = pen.pl_id))
3947 and pen.sspndd_flag = 'N'
3948 and pen.prtt_enrt_rslt_stat_cd is null
3949 and pen.business_group_id = g_parm.business_group_id
3950 /* Bug 12414987: Check whether Participant is covered or not as of g_parm.effective_date */
3951 /* and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
3952 and g_parm.effective_date between pen.effective_start_date
3953 and pen.effective_end_date
3954 and pen.effective_end_date = hr_api.g_eot*/
3955 and g_parm.effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
3956 and pen.enrt_cvg_thru_dt <= pen.effective_end_date;
3957 --
3958 cursor c_dpnt_enrt(v_person_id number) is
3959 select pen.pgm_id,
3960 pen.pl_id
3961 from ben_prtt_enrt_rslt_f pen,
3962 ben_elig_cvrd_dpnt_f pdp
3963 where pdp.dpnt_person_id = v_person_id
3964 and pdp.cvg_thru_dt = hr_api.g_eot
3965 and g_parm.effective_date between
3966 pdp.effective_start_date and pdp.effective_end_date
3967 and pdp.effective_end_date = hr_api.g_eot
3968 and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
3969 and (g_parm.pgm_id is null or
3970 pen.pgm_id = g_parm.pgm_id)
3971 and ((g_parm.pl_nip_id is null
3972 and g_parm.plan_in_pgm_flag = 'Y'
3973 and pen.pgm_id is not null)
3974 or
3975 (g_parm.pl_nip_id is null
3976 and g_parm.plan_in_pgm_flag = 'N'
3977 and pen.pgm_id is null)
3978 or
3979 (g_parm.pl_nip_id = pen.pl_id))
3980 and pen.sspndd_flag = 'N'
3981 and pen.prtt_enrt_rslt_stat_cd is null
3982 and pen.business_group_id = g_parm.business_group_id
3983 and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
3984 and g_parm.effective_date between
3985 pen.effective_start_date and pen.effective_end_date
3986 and pen.effective_end_date = hr_api.g_eot;
3987 --
3988 l_start_person_action_id number;
3989 l_ending_person_action_id number;
3990 l_range_id number;
3991 l_object_version_number number;
3992 l_create_action boolean := false;
3993 l_pgm_id number;
3994 l_pl_id number;
3995 --
3996 l_proc varchar2(80) := g_package || '.create_emrtevt_ranges';
3997 --
3998 begin
3999 --
4000 hr_utility.set_location('Entering : ' || l_proc, 10);
4001 --
4002 --
4003 for l_per_rec in c_per_emrgevt loop
4004 --
4005 l_create_action := false;
4006 --
4007 if is_dependent(p_person_id => l_per_rec.person_id) then
4008 --
4009 -- For Dependents.
4010 --
4011 if g_parm.elig_enrol_cd = 'ENROL' then
4012 --
4013 hr_utility.set_location('Person is dependent',10);
4014 hr_utility.set_location('Person ID '||l_per_rec.person_id,10);
4015 hr_utility.set_location('BG ID '||g_parm.business_group_id,10);
4016 hr_utility.set_location('EFF DATE '||g_parm.effective_date,10);
4017 hr_utility.set_location('PGM ID '||g_parm.pgm_id,10);
4018 hr_utility.set_location('PL_NIP ID '||g_parm.pl_nip_id,10);
4019 hr_utility.set_location('PL_IN PROG FLAG '||g_parm.plan_in_pgm_flag,10);
4020 open c_dpnt_enrt(l_per_rec.person_id);
4021 fetch c_dpnt_enrt into l_pgm_id, l_pl_id;
4022 --
4023 if c_dpnt_enrt%found then
4024 --
4025 hr_utility.set_location('Definately Found record',10);
4026 l_create_action := true;
4027 --
4028 if g_parm.use_fctr_to_sel_flag = 'Y' then
4029 --
4030 hr_utility.set_location('Found record',10);
4031 l_create_action := fctr_belongs_to_comp_object(
4032 p_pgm_id => l_pgm_id,
4033 p_pl_id => l_pl_id);
4034 --
4035 end if;
4036 --
4037 end if;
4038 --
4039 close c_dpnt_enrt;
4040 --
4041 end if;
4042 --
4043 else
4044 --
4045 -- Participants.
4046 --
4047 if g_parm.elig_enrol_cd = 'ELIG' then
4048 --
4049 open c_elig(l_per_rec.person_id);
4050 fetch c_elig into l_pgm_id, l_pl_id;
4051 --
4052 if c_elig%found then
4053 --
4054 l_create_action := true;
4055 --
4056 if g_parm.use_fctr_to_sel_flag = 'Y' then
4057 --
4058 l_create_action := fctr_belongs_to_comp_object(
4059 p_pgm_id => l_pgm_id,
4060 p_pl_id => l_pl_id);
4061 --
4062 end if;
4063 --
4064 end if;
4065 --
4066 close c_elig;
4067 --
4068 elsif g_parm.elig_enrol_cd = 'ENROL' then
4069 --
4070 hr_utility.set_location('Person is participant',10);
4071 open c_enrt(l_per_rec.person_id);
4072 fetch c_enrt into l_pgm_id, l_pl_id;
4073 --
4074 if c_enrt%found then
4075 --
4076 l_create_action := true;
4077 --
4078 if g_parm.use_fctr_to_sel_flag = 'Y' then
4079 --
4080 l_create_action := fctr_belongs_to_comp_object(
4081 p_pgm_id => l_pgm_id,
4082 p_pl_id => l_pl_id);
4083 --
4084 end if;
4085 --
4086 end if;
4087 --
4088 close c_enrt;
4089 --
4090 end if;
4091 --
4092 end if;
4093 --
4094 if l_create_action AND
4095 ben_rules.chk_comp_object_selection(
4096 p_oipl_id => null,
4097 p_pl_id => l_pl_id,
4098 p_pgm_id => l_pgm_id,
4099 p_pl_typ_id => null,
4100 p_opt_id => null,
4101 p_business_group_id => g_parm.business_group_id,
4102 p_comp_selection_rule_id => g_parm.comp_selection_rl,
4103 p_effective_date => g_parm.effective_date) then
4104 --
4105 -- Create person actions and batch ranges
4106 --
4107 g_per_slctd := g_per_slctd + 1;
4108 --
4109 hr_utility.set_location('Range created',10);
4110 --
4111 create_actions_ranges
4112 (p_person_id => l_per_rec.person_id
4113 ,p_start_person_action_id => l_start_person_action_id
4114 ,p_ending_person_action_id => l_ending_person_action_id);
4115 --
4116 end if;
4117 --
4118 end loop;
4119 --
4120 hr_utility.set_location('People selected from database : ' || g_per_slctd,10);
4121 --
4122 -- There could be a few person actions left over from the call in the for
4123 -- loop above. Create a batch range for them.
4124 --
4125 If g_person_actn_cnt > 0 and
4126 mod(g_person_actn_cnt, g_chunk_size) <> 0 then
4127 --
4128 hr_utility.set_location('Ranges for remaining people ' || l_proc, 25);
4129 --
4130 ben_batch_ranges_api.create_batch_ranges
4131 (p_validate => FALSE
4132 ,p_effective_date => g_parm.effective_date
4133 ,p_benefit_action_id => g_parm.benefit_action_id
4134 ,p_range_id => l_range_id
4135 ,p_range_status_cd => 'U'
4136 ,p_starting_person_action_id => l_start_person_action_id
4137 ,p_ending_person_action_id => l_ending_person_action_id
4138 ,p_object_version_number => l_object_version_number);
4139 --
4140 g_num_ranges := g_num_ranges + 1;
4141 --
4142 end if;
4143 --
4144 hr_utility.set_location('Leaving : ' || l_proc, 10);
4145 --
4146 end create_emrgevt_ranges;
4147 --
4148 -- ----------------------------------------------------------------------------
4149 -- -------------------------< create_mssmlg_ranges >---------------------------
4150 -- ----------------------------------------------------------------------------
4151 --
4152 procedure create_mssmlg_ranges is
4153 --
4154 -- if g_parm.elig_enrol_cd is not null and g_parm.pgm_id is null and g_parm.pl_nip_id is null
4155 -- this is error condition
4156
4157 cursor c_per_mssmlg_elig_pgm is
4158 select person_id
4159 from per_all_people_f ppf
4160 where (g_parm.person_id is null or
4161 ppf.person_id = g_parm.person_id)
4162 and g_parm.effective_date between ppf.effective_start_date
4163 and ppf.effective_end_date
4164 and ppf.business_group_id = g_parm.business_group_id
4165 and (g_parm.person_type_id is null
4166 or
4167 exists (select null
4168 from per_person_type_usages_f ppu
4169 where ppf.person_id = ppu.person_id
4170 and ppu.person_type_id = g_parm.person_type_id
4171 and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
4172 --
4173 and ((g_parm.elig_enrol_cd = 'ELIG' -- begin ELIG block
4174 and
4175 -- check if the person is eligible for the comp object
4176 exists (select 's'
4177 from ben_elig_per_f elig,
4178 ben_per_in_ler pil
4179 where elig.person_id = ppf.person_id
4180 and elig.pgm_id = g_parm.pgm_id
4181 and (g_parm.pl_nip_id is null and g_parm.plan_in_pgm_flag = 'Y' and elig.pgm_id is not null)
4182 and elig.elig_flag = 'Y'
4183 and g_parm.effective_date between elig.effective_start_date and elig.effective_end_date
4184 and pil.per_in_ler_id(+)=elig.per_in_ler_id
4185 and pil.business_group_id(+)=elig.business_group_id
4186 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
4187 -- found row condition
4188 or pil.per_in_ler_stat_cd is null
4189 -- outer join condition
4190 )
4191 ))
4192 )
4193 and ((g_parm.organization_id is null and
4194 g_parm.location_id is null)
4195 or
4196 exists (select 's'
4197 FROM per_all_assignments_f asg, per_assignment_status_types ast
4198 WHERE asg.person_id = ppf.person_id
4199 AND asg.primary_flag = 'Y'
4200 and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
4201 and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
4202 AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4203 AND asg.assignment_status_type_id = ast.assignment_status_type_id
4204 and asg.business_group_id = g_parm.business_group_id
4205 AND ( ( assignment_type = 'E'
4206 AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
4207 OR ( ast.per_system_status = 'TERM_ASSIGN'
4208 AND NOT EXISTS (
4209 SELECT assignment_id
4210 FROM per_all_assignments_f asg1, per_assignment_status_types ast1
4211 WHERE asg1.assignment_type = 'B'
4212 AND asg1.primary_flag = 'Y'
4213 AND asg1.person_id = ppf.person_id
4214 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
4215 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
4216 AND g_parm.effective_date
4217 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
4218 )
4219 )
4220 )
4221 )
4222 OR ( assignment_type = 'B'
4223 AND NOT EXISTS (
4224 SELECT assignment_id
4225 FROM per_all_assignments_f asg2, per_assignment_status_types ast2
4226 WHERE asg2.assignment_type = 'E'
4227 AND asg2.primary_flag = 'Y'
4228 AND asg2.person_id = ppf.person_id
4229 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
4230 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
4231 AND g_parm.effective_date
4232 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
4233 )
4234 )
4235 )
4236 )
4237 );
4238 --
4239 cursor c_per_mssmlg_elig_plnip is
4240 select person_id
4241 from per_all_people_f ppf
4242 where (g_parm.person_id is null or
4243 ppf.person_id = g_parm.person_id)
4244 and g_parm.effective_date between ppf.effective_start_date
4245 and ppf.effective_end_date
4246 and ppf.business_group_id = g_parm.business_group_id
4247 and (g_parm.person_type_id is null
4248 or
4249 exists (select null
4250 from per_person_type_usages_f ppu
4251 where ppf.person_id = ppu.person_id
4252 and ppu.person_type_id = g_parm.person_type_id
4253 and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
4254 --
4255 and ((g_parm.elig_enrol_cd = 'ELIG' -- begin ELIG block
4256 and
4257 -- check if the person is eligible for the comp object
4258 exists (select 's'
4259 from ben_elig_per_f elig,
4260 ben_per_in_ler pil
4261 where elig.person_id = ppf.person_id
4262 and g_parm.pgm_id is null
4263 and (g_parm.pl_nip_id is not null and g_parm.plan_in_pgm_flag = 'N' and elig.pgm_id is null)
4264 and g_parm.pl_nip_id = elig.pl_id
4265 and elig.elig_flag = 'Y'
4266 and g_parm.effective_date between elig.effective_start_date and elig.effective_end_date
4267 and pil.per_in_ler_id(+)=elig.per_in_ler_id
4268 and pil.business_group_id(+)=elig.business_group_id
4269 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
4270 -- found row condition
4271 or pil.per_in_ler_stat_cd is null
4272 -- outer join condition
4273 )
4274 ))
4275 )
4276 and ((g_parm.organization_id is null and
4277 g_parm.location_id is null)
4278 or
4279 exists (select 's'
4280 FROM per_all_assignments_f asg, per_assignment_status_types ast
4281 WHERE asg.person_id = ppf.person_id
4282 AND asg.primary_flag = 'Y'
4283 and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
4284 and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
4285 AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4286 AND asg.assignment_status_type_id = ast.assignment_status_type_id
4287 and asg.business_group_id = g_parm.business_group_id
4288 AND ( ( assignment_type = 'E'
4289 AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
4290 OR ( ast.per_system_status = 'TERM_ASSIGN'
4291 AND NOT EXISTS (
4292 SELECT assignment_id
4293 FROM per_all_assignments_f asg1, per_assignment_status_types ast1
4294 WHERE asg1.assignment_type = 'B'
4295 AND asg1.primary_flag = 'Y'
4296 AND asg1.person_id = ppf.person_id
4297 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
4298 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
4299 AND g_parm.effective_date
4300 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
4301 )
4302 )
4303 )
4304 )
4305 OR ( assignment_type = 'B'
4306 AND NOT EXISTS (
4307 SELECT assignment_id
4308 FROM per_all_assignments_f asg2, per_assignment_status_types ast2
4309 WHERE asg2.assignment_type = 'E'
4310 AND asg2.primary_flag = 'Y'
4311 AND asg2.person_id = ppf.person_id
4312 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
4313 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
4314 AND g_parm.effective_date
4315 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
4316 )
4317 )
4318 )
4319 )
4320 );
4321 --
4322 cursor c_per_mssmlg_enrol_pgm is
4323 select person_id
4324 from per_all_people_f ppf
4325 where (g_parm.person_id is null or ppf.person_id = g_parm.person_id)
4326 and g_parm.effective_date between ppf.effective_start_date and ppf.effective_end_date
4327 and ppf.business_group_id = g_parm.business_group_id
4328 and (g_parm.person_type_id is null
4329 or
4330 exists (select null
4331 from per_person_type_usages_f ppu
4332 where ppf.person_id = ppu.person_id
4333 and ppu.person_type_id = g_parm.person_type_id
4334 and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
4335 --
4336 and ( (g_parm.elig_enrol_cd = 'ENROL' -- begin ENROL block
4337 and exists (select 's'
4338 from ben_prtt_enrt_rslt_f pen
4339 where pen.person_id = ppf.person_id
4340 and (pen.pgm_id = g_parm.pgm_id)
4341 and (g_parm.pl_nip_id is null and g_parm.plan_in_pgm_flag = 'Y' and pen.pgm_id is not null)
4342 and pen.sspndd_flag = 'N'
4343 and pen.prtt_enrt_rslt_stat_cd is null
4344 and pen.business_group_id = g_parm.business_group_id
4345 /* Bug 12414987: Check whether Participant is covered or not as of g_parm.effective_date */
4346 --and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
4347 --and g_parm.effective_date between pen.effective_start_date and pen.effective_end_date
4348 and g_parm.effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
4349 and pen.enrt_cvg_thru_dt <= pen.effective_end_date
4350
4351 ))
4352 )
4353 and ((g_parm.organization_id is null and
4354 g_parm.location_id is null)
4355 or
4356 exists (select 's'
4357 FROM per_all_assignments_f asg, per_assignment_status_types ast
4358 WHERE asg.person_id = ppf.person_id
4359 AND asg.primary_flag = 'Y'
4360 and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
4361 and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
4362 AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4363 AND asg.assignment_status_type_id = ast.assignment_status_type_id
4364 and asg.business_group_id = g_parm.business_group_id
4365 AND ( ( assignment_type = 'E'
4366 AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
4367 OR ( ast.per_system_status = 'TERM_ASSIGN'
4368 AND NOT EXISTS (
4369 SELECT assignment_id
4370 FROM per_all_assignments_f asg1, per_assignment_status_types ast1
4371 WHERE asg1.assignment_type = 'B'
4372 AND asg1.primary_flag = 'Y'
4373 AND asg1.person_id = ppf.person_id
4374 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
4375 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
4376 AND g_parm.effective_date
4377 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
4378 ))
4379 ))
4380 OR ( assignment_type = 'B'
4381 AND NOT EXISTS (
4382 SELECT assignment_id
4383 FROM per_all_assignments_f asg2, per_assignment_status_types ast2
4384 WHERE asg2.assignment_type = 'E'
4385 AND asg2.primary_flag = 'Y'
4386 AND asg2.person_id = ppf.person_id
4387 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
4388 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
4389 AND g_parm.effective_date
4390 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
4391 ))
4392 ))
4393 );
4394 --
4395 cursor c_per_mssmlg_enrol_plnip is
4396 select person_id
4397 from per_all_people_f ppf
4398 where (g_parm.person_id is null or ppf.person_id = g_parm.person_id)
4399 and g_parm.effective_date between ppf.effective_start_date and ppf.effective_end_date
4400 and ppf.business_group_id = g_parm.business_group_id
4401 and (g_parm.person_type_id is null
4402 or
4403 exists (select null
4404 from per_person_type_usages_f ppu
4405 where ppf.person_id = ppu.person_id
4406 and ppu.person_type_id = g_parm.person_type_id
4407 and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
4408 --
4409 and ( (g_parm.elig_enrol_cd = 'ENROL' -- begin ENROL block
4410 and exists (select 's'
4411 from ben_prtt_enrt_rslt_f pen
4412 where pen.person_id = ppf.person_id
4413 and (g_parm.pgm_id is null)
4414 and (g_parm.pl_nip_id is not null and g_parm.plan_in_pgm_flag = 'N' and pen.pgm_id is null)
4415 and (g_parm.pl_nip_id = pen.pl_id)
4416 and pen.sspndd_flag = 'N'
4417 and pen.prtt_enrt_rslt_stat_cd is null
4418 and pen.business_group_id = g_parm.business_group_id
4419 and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
4420 and g_parm.effective_date between pen.effective_start_date and pen.effective_end_date
4421 ))
4422 )
4423 and ((g_parm.organization_id is null and
4424 g_parm.location_id is null)
4425 or
4426 exists (select 's'
4427 FROM per_all_assignments_f asg, per_assignment_status_types ast
4428 WHERE asg.person_id = ppf.person_id
4429 AND asg.primary_flag = 'Y'
4430 and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
4431 and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
4432 AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4433 AND asg.assignment_status_type_id = ast.assignment_status_type_id
4434 and asg.business_group_id = g_parm.business_group_id
4435 AND ( ( assignment_type = 'E'
4436 AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
4437 OR ( ast.per_system_status = 'TERM_ASSIGN'
4438 AND NOT EXISTS (
4439 SELECT assignment_id
4440 FROM per_all_assignments_f asg1, per_assignment_status_types ast1
4441 WHERE asg1.assignment_type = 'B'
4442 AND asg1.primary_flag = 'Y'
4443 AND asg1.person_id = ppf.person_id
4444 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
4445 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
4446 AND g_parm.effective_date
4447 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
4448 ))
4449 ))
4450 OR ( assignment_type = 'B'
4451 AND NOT EXISTS (
4452 SELECT assignment_id
4453 FROM per_all_assignments_f asg2, per_assignment_status_types ast2
4454 WHERE asg2.assignment_type = 'E'
4455 AND asg2.primary_flag = 'Y'
4456 AND asg2.person_id = ppf.person_id
4457 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
4458 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
4459 AND g_parm.effective_date
4460 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
4461 ))
4462 ))
4463 );
4464 --
4465 cursor c_per_mssmlg is
4466 select person_id
4467 from per_all_people_f ppf
4468 where (g_parm.person_id is null or ppf.person_id = g_parm.person_id)
4469 and g_parm.effective_date between ppf.effective_start_date and ppf.effective_end_date
4470 and ppf.business_group_id = g_parm.business_group_id
4471 and (g_parm.person_type_id is null
4472 or
4473 exists (select null
4474 from per_person_type_usages_f ppu
4475 where ppf.person_id = ppu.person_id
4476 and ppu.person_type_id = g_parm.person_type_id
4477 and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
4478 --
4479 and (g_parm.elig_enrol_cd is null )
4480 and ((g_parm.organization_id is null and
4481 g_parm.location_id is null)
4482 or
4483 exists (select 's'
4484 FROM per_all_assignments_f asg, per_assignment_status_types ast
4485 WHERE asg.person_id = ppf.person_id
4486 AND asg.primary_flag = 'Y'
4487 and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
4488 and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
4489 AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4490 AND asg.assignment_status_type_id = ast.assignment_status_type_id
4491 and asg.business_group_id = g_parm.business_group_id
4492 AND ( ( assignment_type = 'E'
4493 AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
4494 OR ( ast.per_system_status = 'TERM_ASSIGN'
4495 AND NOT EXISTS (
4496 SELECT assignment_id
4497 FROM per_all_assignments_f asg1, per_assignment_status_types ast1
4498 WHERE asg1.assignment_type = 'B'
4499 AND asg1.primary_flag = 'Y'
4500 AND asg1.person_id = ppf.person_id
4501 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
4502 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
4503 AND g_parm.effective_date
4504 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
4505 ))
4506 ))
4507 OR ( assignment_type = 'B'
4508 AND NOT EXISTS (
4509 SELECT assignment_id
4510 FROM per_all_assignments_f asg2, per_assignment_status_types ast2
4511 WHERE asg2.assignment_type = 'E'
4512 AND asg2.primary_flag = 'Y'
4513 AND asg2.person_id = ppf.person_id
4514 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
4515 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
4516 AND g_parm.effective_date
4517 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
4518 ))
4519 ))
4520 );
4521 --
4522 l_start_person_action_id number;
4523 l_ending_person_action_id number;
4524 l_range_id number;
4525 l_object_version_number number;
4526 --
4527 l_proc varchar2(80) := g_package || '.create_mssmlg_ranges';
4528 --
4529 begin
4530 --
4531 hr_utility.set_location('Entering : ' || l_proc, 10);
4532 --
4533 if (g_parm.elig_enrol_cd = 'ELIG' and g_parm.pgm_id is not null and g_parm.pl_nip_id is null)
4534 then
4535 for l_per_rec in c_per_mssmlg_elig_pgm loop
4536 --
4537 g_per_slctd := g_per_slctd + 1;
4538 --
4539 create_actions_ranges
4540 (p_person_id => l_per_rec.person_id
4541 ,p_start_person_action_id => l_start_person_action_id
4542 ,p_ending_person_action_id => l_ending_person_action_id);
4543 --
4544 end loop;
4545 elsif (g_parm.elig_enrol_cd = 'ELIG' and g_parm.pgm_id is null and g_parm.pl_nip_id is not null)
4546 then
4547 for l_per_rec in c_per_mssmlg_elig_plnip loop
4548 --
4549 g_per_slctd := g_per_slctd + 1;
4550 --
4551 create_actions_ranges
4552 (p_person_id => l_per_rec.person_id
4553 ,p_start_person_action_id => l_start_person_action_id
4554 ,p_ending_person_action_id => l_ending_person_action_id);
4555 --
4556 end loop;
4557 elsif (g_parm.elig_enrol_cd = 'ENROL' and g_parm.pgm_id is not null and g_parm.pl_nip_id is null)
4558 then
4559 for l_per_rec in c_per_mssmlg_enrol_pgm loop
4560 --
4561 g_per_slctd := g_per_slctd + 1;
4562 --
4563 create_actions_ranges
4564 (p_person_id => l_per_rec.person_id
4565 ,p_start_person_action_id => l_start_person_action_id
4566 ,p_ending_person_action_id => l_ending_person_action_id);
4567 --
4568 end loop;
4569 elsif (g_parm.elig_enrol_cd = 'ENROL' and g_parm.pgm_id is null and g_parm.pl_nip_id is not null)
4570 then
4571 for l_per_rec in c_per_mssmlg_enrol_plnip loop
4572 --
4573 g_per_slctd := g_per_slctd + 1;
4574 --
4575 create_actions_ranges
4576 (p_person_id => l_per_rec.person_id
4577 ,p_start_person_action_id => l_start_person_action_id
4578 ,p_ending_person_action_id => l_ending_person_action_id);
4579 --
4580 end loop;
4581 else
4582 for l_per_rec in c_per_mssmlg loop
4583 --
4584 g_per_slctd := g_per_slctd + 1;
4585 --
4586 create_actions_ranges
4587 (p_person_id => l_per_rec.person_id
4588 ,p_start_person_action_id => l_start_person_action_id
4589 ,p_ending_person_action_id => l_ending_person_action_id);
4590 --
4591 end loop;
4592 end if;
4593 --
4594 hr_utility.set_location('People selected from database : ' || g_per_slctd,10);
4595 --
4596 -- There could be a few person actions left over from the call in the for
4597 -- loop above. Create a batch range for them.
4598 --
4599 If g_person_actn_cnt > 0 and
4600 mod(g_person_actn_cnt, g_chunk_size) <> 0 then
4601 --
4602 hr_utility.set_location('Ranges for remaining people ' || l_proc, 25);
4603 --
4604 ben_batch_ranges_api.create_batch_ranges
4605 (p_validate => FALSE
4606 ,p_effective_date => g_parm.effective_date
4607 ,p_benefit_action_id => g_parm.benefit_action_id
4608 ,p_range_id => l_range_id
4609 ,p_range_status_cd => 'U'
4610 ,p_starting_person_action_id => l_start_person_action_id
4611 ,p_ending_person_action_id => l_ending_person_action_id
4612 ,p_object_version_number => l_object_version_number);
4613 --
4614 g_num_ranges := g_num_ranges + 1;
4615 --
4616 end if;
4617 --
4618 hr_utility.set_location('Leaving : ' || l_proc, 10);
4619 --
4620 end create_mssmlg_ranges;
4621 --
4622 -- ----------------------------------------------------------------------------
4623 -- -------------------------------< process >----------------------------------
4624 -- ----------------------------------------------------------------------------
4625 --
4626 procedure process
4627 (errbuf out nocopy varchar2
4628 ,retcode out nocopy number
4629 ,p_benefit_action_id in number default null
4630 ,p_validate in varchar2 default 'N'
4631 ,p_effective_date in varchar2
4632 ,p_business_group_id in number
4633 ,p_mode in varchar2 default 'I'
4634 ,p_cm_trgr_typ_cd in varchar2
4635 ,p_cm_typ_id in number default null
4636 ,p_person_id in number default null
4637 ,p_person_type_id in number default null
4638 ,p_person_sel_rl in number default null
4639 ,p_organization_id in number default null
4640 ,p_location_id in number default null
4641 ,p_ler_id in number default null
4642 ,p_pgm_id in number default null
4643 ,p_pl_nip_id in number default null
4644 ,p_plan_in_pgm_flag in varchar2 default 'Y'
4645 ,p_actn_typ_id in number default null
4646 ,p_elig_enrol_cd in varchar2 default null
4647 ,p_age_fctr_id in number default null
4648 ,p_min_age in number default null
4649 ,p_max_age in number default null
4650 ,p_los_fctr_id in number default null
4651 ,p_min_los in number default null
4652 ,p_max_los in number default null
4653 ,p_cmbn_age_los_fctr_id in number default null
4654 ,p_date_from in varchar2 default null
4655 -- PB : 5422 :
4656 -- ,p_popl_enrt_typ_cycl_id in number default null
4657 ,p_lf_evt_ocrd_dt in varchar2 default null
4658 ,p_audit_log_flag in varchar2 default 'N'
4659 ,p_comp_selection_rl in number default null
4660 ,p_use_fctr_to_sel_flag in varchar2 default 'N'
4661 ,p_los_det_to_use_cd in varchar2 default null
4662 ) is
4663 --
4664 l_effective_date date;
4665 l_date_from date;
4666 l_request_id number;
4667 l_errbuf varchar2(1000);
4668 l_retcode number;
4669 l_object_version_number number;
4670 l_commit number;
4671 --
4672 l_proc varchar2(80) := g_package || '.process';
4673 l_lf_evt_ocrd_dt date;
4674 --
4675 begin
4676 --
4677 hr_utility.set_location('Entering : ' || l_proc, 10);
4678 --
4679 -- Convert varchar2 dates to real dates
4680 -- 1) First remove time component
4681 -- 2) Next convert format
4682 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
4683 l_effective_date := to_date(to_char(trunc(l_effective_date),'DD-MON-RRRR')
4684 ,'DD-MON-RRRR');
4685 l_date_from := to_date(p_date_from,'YYYY/MM/DD HH24:MI:SS');
4686 l_date_from := to_date(to_char(trunc(l_date_from),'DD-MON-RRRR')
4687 ,'DD-MON-RRRR');
4688 l_lf_evt_ocrd_dt := to_date(p_lf_evt_ocrd_dt,'YYYY/MM/DD HH24:MI:SS');
4689 l_lf_evt_ocrd_dt := to_date(to_char(trunc(l_lf_evt_ocrd_dt),'DD/MM/RRRR'),'DD/MM/RRRR');
4690 --
4691 -- Put row in fnd_sessions
4692 --
4693 dt_fndate.change_ses_date
4694 (p_ses_date => l_effective_date,
4695 p_commit => l_commit);
4696 --
4697 -- Initialize all the global record structures
4698 --
4699 initialize_globals;
4700 --
4701 -- Log start time of process
4702 --
4703 g_proc_rec.business_group_id := p_business_group_id;
4704 g_proc_rec.strt_dt := sysdate;
4705 g_proc_rec.strt_tm := to_char(sysdate,'HH24:MI:SS');
4706 g_strt_tm_numeric := dbms_utility.get_time;
4707 --
4708 -- Flush the global-parameter-list and load all the passed parameters into it.
4709 -- All the sub procedures in the main process will be able to access this list
4710 -- and this will keep the procedure calls simple.
4711 --
4712 g_parm.benefit_action_id := p_benefit_action_id;
4713 g_parm.validate_flag := p_validate;
4714 g_parm.effective_date := l_effective_date;
4715 g_parm.business_group_id := p_business_group_id;
4716 g_parm.mode_cd := p_mode;
4717 g_parm.cm_trgr_typ_cd := p_cm_trgr_typ_cd;
4718 g_parm.cm_typ_id := p_cm_typ_id;
4719 g_parm.person_id := p_person_id;
4720 g_parm.person_type_id := p_person_type_id;
4721 g_parm.person_sel_rl := p_person_sel_rl;
4722 g_parm.organization_id := p_organization_id;
4723 g_parm.location_id := p_location_id;
4724 g_parm.ler_id := p_ler_id;
4725 g_parm.pgm_id := p_pgm_id;
4726 g_parm.pl_nip_id := p_pl_nip_id;
4727 g_parm.plan_in_pgm_flag := p_plan_in_pgm_flag;
4728 g_parm.comp_selection_rl := p_comp_selection_rl;
4729 g_parm.actn_typ_id := p_actn_typ_id;
4730 g_parm.elig_enrol_cd := p_elig_enrol_cd;
4731 g_parm.use_fctr_to_sel_flag := p_use_fctr_to_sel_flag;
4732 g_parm.age_fctr_id := p_age_fctr_id;
4733 g_parm.min_age := p_min_age;
4734 g_parm.max_age := p_max_age;
4735 g_parm.los_fctr_id := p_los_fctr_id;
4736 g_parm.min_los := p_min_los;
4737 g_parm.max_los := p_max_los;
4738 g_parm.los_det_to_use_cd := p_los_det_to_use_cd;
4739 g_parm.cmbn_age_los_fctr_id := p_cmbn_age_los_fctr_id;
4740 g_parm.date_from := l_date_from;
4741 -- PB : 5422 :
4742 -- g_parm.popl_enrt_typ_cycl_id := p_popl_enrt_typ_cycl_id;
4743 g_parm.lf_evt_ocrd_dt := l_lf_evt_ocrd_dt;
4744 g_parm.audit_log_flag := p_audit_log_flag;
4745 --
4746 -- Check the parameters for validity and incompatibilities.
4747 --
4748 check_business_rules;
4749 --
4750 -- Get the parameters for the batch process so we know how many slaves to
4751 -- start and what size the chunk size is. Store them in globals.
4752 --
4753 benutils.get_parameter
4754 (p_business_group_id => p_business_group_id
4755 ,p_batch_exe_cd => 'BENTMPCM'
4756 ,p_threads => g_threads
4757 ,p_chunk_size => g_chunk_size
4758 ,p_max_errors => g_max_errors);
4759 --
4760 hr_utility.set_location('Num Threads = ' || g_threads, 10);
4761 hr_utility.set_location('Chunk Size = ' || g_chunk_size, 10);
4762 hr_utility.set_location('Max Errors = ' || g_max_errors, 10);
4763 --
4764 -- Create benefit actions parameters in the benefit action table.
4765 -- If a benefit action already exists, do not create - in other words
4766 -- we are doing a restart.
4767 --
4768 if p_benefit_action_id is null then
4769 --
4770 -- A new request was submitted. Create a new benefit action.
4771 --
4772 ben_benefit_actions_api.create_benefit_actions
4773 (p_validate => FALSE
4774 ,p_benefit_action_id => g_parm.benefit_action_id
4775 ,p_process_date => l_effective_date
4776 ,p_mode_cd => p_mode
4777 ,p_derivable_factors_flag => 'N'
4778 ,p_validate_flag => p_validate
4779 ,p_person_id => p_person_id
4780 ,p_person_type_id => p_person_type_id
4781 ,p_pgm_id => p_pgm_id
4782 ,p_business_group_id => p_business_group_id
4783 ,p_pl_id => p_pl_nip_id
4784 -- PB : 5422 :
4785 -- ,p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id
4786 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
4787 ,p_no_programs_flag => p_plan_in_pgm_flag
4788 ,p_no_plans_flag => 'N'
4789 ,p_comp_selection_rl => p_comp_selection_rl
4790 ,p_person_selection_rl => p_person_sel_rl
4791 ,p_ler_id => p_ler_id
4792 ,p_organization_id => p_organization_id
4793 ,p_location_id => p_location_id
4794 ,p_debug_messages_flag => 'Y'
4795 ,p_cm_trgr_typ_cd => p_cm_trgr_typ_cd
4796 ,p_cm_typ_id => p_cm_typ_id
4797 ,p_use_fctr_to_sel_flag => p_use_fctr_to_sel_flag
4798 ,p_age_fctr_id => p_age_fctr_id
4799 ,p_min_age => p_min_age
4800 ,p_max_age => p_max_age
4801 ,p_los_fctr_id => p_los_fctr_id
4802 ,p_min_los => p_min_los
4803 ,p_max_los => p_max_los
4804 ,p_los_det_to_use_cd => p_los_det_to_use_cd
4805 ,p_cmbn_age_los_fctr_id => p_cmbn_age_los_fctr_id
4806 ,p_date_from => l_date_from
4807 ,p_elig_enrol_cd => p_elig_enrol_cd
4808 ,p_actn_typ_id => p_actn_typ_id
4809 ,p_audit_log_flag => p_audit_log_flag
4810 ,p_request_id => fnd_global.conc_request_id
4811 ,p_program_application_id => fnd_global.prog_appl_id
4812 ,p_program_id => fnd_global.conc_program_id
4813 ,p_program_update_date => sysdate
4814 ,p_object_version_number => l_object_version_number
4815 ,p_effective_date => l_effective_date);
4816 --
4817 commit;
4818 --
4819 -- Delete batch ranges for the benefit action id
4820 --
4821 delete from ben_batch_ranges
4822 where benefit_action_id = g_parm.benefit_action_id;
4823 --
4824 commit;
4825 --
4826 -- Create person-actions and batch-ranges for the process. The following
4827 -- if-statement calls the appropriate procedure based on the trigger-type.
4828 --
4829 if p_cm_trgr_typ_cd = 'ENRTRMDR' then
4830 --
4831 create_enrtrmdr_ranges;
4832 --
4833 elsif p_cm_trgr_typ_cd = 'ACTNRMDR' then
4834 --
4835 create_actnrmdr_ranges;
4836 --
4837 elsif p_cm_trgr_typ_cd = 'EMRGEVT' then
4838 --
4839 create_emrgevt_ranges;
4840 --
4841 elsif p_cm_trgr_typ_cd = 'MSSMLG' then
4842 --
4843 create_mssmlg_ranges;
4844 --
4845 end if;
4846 --
4847 g_proc_rec.benefit_action_id := g_parm.benefit_action_id;
4848 --
4849 hr_utility.set_location('Number of person actions : ' ||
4850 to_char(g_person_actn_cnt), 10);
4851 --
4852 else -- p_benefit_action_id
4853 --
4854 -- Benefit action id is not null i.e. the batch process is being restarted
4855 -- for a certain benefit action id. Create batch ranges and person actions
4856 -- for restarting.
4857 --
4858 hr_utility.set_location('restart batch process ' || l_proc, 20);
4859 --
4860 ben_batch_utils.create_restart_person_actions
4861 (p_benefit_action_id => p_benefit_action_id
4862 ,p_effective_date => l_effective_date
4863 ,p_chunk_size => g_chunk_size
4864 ,p_threads => g_threads
4865 ,p_num_ranges => g_num_ranges
4866 ,p_num_persons => g_person_cnt
4867 ,p_commit_data => 'Y');
4868 --
4869 g_parm.benefit_action_id := p_benefit_action_id;
4870 g_proc_rec.benefit_action_id := g_parm.benefit_action_id;
4871 --
4872 end if;
4873 --
4874 -- If there were no people selected with the criteria provided, the number of
4875 -- ranges created would have been zero. Raise an error if so.
4876 --
4877 if g_num_ranges = 0 then
4878 --
4879 fnd_message.set_name('BEN', 'BEN_91769_NOONE_TO_PROCESS');
4880 fnd_message.set_token('PROC',l_proc);
4881 fnd_message.raise_error;
4882 --
4883 end if;
4884 --
4885 -- Set the number of threads to the lesser of the defined number of threads
4886 -- and the number of ranges created above.
4887 --
4888 g_threads := least(g_threads, g_num_ranges);
4889 --
4890 hr_utility.set_location('Number of Threads : ' || g_threads, 20);
4891 --
4892 -- Submit requests
4893 --
4894 for l_count in 1..(g_threads -1) loop
4895 --
4896 -- We are subtracting one from the number of threads because the main
4897 -- process will act as the last thread and will be able to keep track of
4898 -- the child processes. If only one range got created this loop will never
4899 -- be executed and no additional concurrent requests will be submitted.
4900 --
4901 hr_utility.set_location('Sumitting thread : ' || l_count, 25);
4902 --
4903 l_request_id := fnd_request.submit_request
4904 (application => 'BEN'
4905 ,program => 'BENTMPCMT'
4906 ,description => NULL
4907 ,sub_request => FALSE
4908 ,argument1 => p_validate
4909 ,argument2 => g_parm.benefit_action_id
4910 ,argument3 => l_count
4911 ,argument4 => p_effective_date
4912 ,argument5 => p_business_group_id);
4913 --
4914 commit;
4915 --
4916 g_num_processes := ben_batch_utils.g_num_processes + 1;
4917 g_processes_rec(g_num_processes) := l_request_id;
4918 --
4919 end loop;
4920 --
4921 -- Carry on with the master. This will ensure that the master finishes last.
4922 --
4923 do_multithread
4924 (errbuf => l_errbuf
4925 ,retcode => l_retcode
4926 ,p_validate => p_validate
4927 ,p_benefit_action_id => g_parm.benefit_action_id
4928 ,p_thread_id => g_threads
4929 ,p_effective_date => p_effective_date
4930 ,p_business_group_id => p_business_group_id);
4931 --
4932 hr_utility.set_location('Leaving : ' || l_proc, 10);
4933 --
4934 end process;
4935 --
4936 procedure summary_by_action(p_concurrent_request_id in number,
4937 p_rcv_comm_count out nocopy number,
4938 p_rcv_1_comm_count out nocopy number,
4939 p_rcv_mlt_comm_count out nocopy number)
4940 is
4941 --
4942 cursor c_tot_person_cm is
4943 select count(distinct bmi.person_id)
4944 from ben_batch_commu_info bmi,
4945 ben_benefit_actions bft
4946 where bft.request_id = p_concurrent_request_id
4947 and bft.benefit_action_id = bmi.benefit_action_id;
4948 --
4949 cursor c_tot_person_1_cm is
4950 select count(*)
4951 from ben_batch_commu_info bmi1,
4952 ben_benefit_actions bft
4953 where bft.request_id = p_concurrent_request_id
4954 and bft.benefit_action_id = bmi1.benefit_action_id
4955 and not exists ( select 1
4956 from ben_batch_commu_info bmi2
4957 where bmi2.benefit_action_id = bft.benefit_action_id
4958 and bmi2.person_id = bmi1.person_id
4959 and bmi2.batch_commu_id <> bmi1.batch_commu_id);
4960 --
4961 begin
4962 --
4963 open c_tot_person_cm;
4964 fetch c_tot_person_cm into p_rcv_comm_count;
4965 close c_tot_person_cm;
4966 --
4967 open c_tot_person_1_cm;
4968 fetch c_tot_person_1_cm into p_rcv_1_comm_count;
4969 close c_tot_person_1_cm;
4970 --
4971 if p_rcv_comm_count is null then
4972 --
4973 p_rcv_comm_count := 0;
4974 --
4975 end if;
4976 --
4977 if p_rcv_1_comm_count is null then
4978 --
4979 p_rcv_1_comm_count := 0;
4980 --
4981 end if;
4982 --
4983 p_rcv_mlt_comm_count := p_rcv_comm_count - p_rcv_1_comm_count;
4984 --
4985 end summary_by_action;
4986 --
4987 --
4988 procedure standard_header(p_concurrent_request_id in number,
4989 p_concurrent_program_name out nocopy varchar2,
4990 p_process_date out nocopy date,
4991 p_validate out nocopy varchar2,
4992 p_business_group out nocopy varchar2,
4993 p_mode out nocopy varchar2,
4994 p_cm_trgr_typ out nocopy varchar2,
4995 p_cm_typ out nocopy varchar2,
4996 p_person out nocopy varchar2,
4997 p_person_type out nocopy varchar2,
4998 p_person_selection_rule out nocopy varchar2,
4999 p_organization out nocopy varchar2,
5000 p_location out nocopy varchar2,
5001 p_ler out nocopy varchar2,
5002 p_program out nocopy varchar2,
5003 p_plan out nocopy varchar2,
5004 p_plan_in_program out nocopy varchar2,
5005 p_actn_typ out nocopy varchar2,
5006 p_elig_enrol out nocopy varchar2,
5007 p_age_fctr out nocopy varchar2,
5008 p_min_age out nocopy number,
5009 p_max_age out nocopy number,
5010 p_los_fctr out nocopy varchar2,
5011 p_min_los out nocopy number,
5012 p_max_los out nocopy number,
5013 p_cmbn_age_los_fctr out nocopy varchar2,
5014 p_date_from out nocopy date,
5015 p_enrollment_period out nocopy varchar2,
5016 p_audit_log out nocopy varchar2,
5017 p_status out nocopy varchar2) is
5018 --
5019 l_all varchar2(80);
5020 l_none varchar2(80);
5021 l_proc varchar2(80) := g_package||'.standard_header';
5022 --
5023 cursor c_benefit_actions is
5024 select bft.process_date process_date,
5025 hr1.meaning mode_meaning,
5026 hr2.meaning validate_meaning,
5027 nvl(ppf.full_name,l_all) person_name,
5028 nvl(ppt.user_person_type,l_all) person_type,
5029 nvl(pgm1.name,l_all) pgm_name,
5030 pbg.name business_group_name,
5031 nvl(pln1.name,l_all) pln_name,
5032 decode(hr4.meaning,
5033 null,
5034 l_all,
5035 hr4.meaning||
5036 ' '||
5037 pln2.name||
5038 ' '||
5039 pgm2.name||
5040 ' '||
5041 epo.strt_dt||
5042 ' '||
5043 epo.end_dt) enrt_perd,
5044 hr3.meaning plan_in_program,
5045 hr5.meaning elig_enrol,
5046 hr6.meaning audit_log,
5047 hr7.meaning cm_trgr_typ,
5048 nvl(cct.name, l_all) cm_typ_name,
5049 decode(loc.location_code,
5050 null, l_all,
5051 -- changed bug: 5446127
5052 -- loc.description) location_desc,
5053 loc.location_code) location_desc,
5054 -- change end
5055 nvl(ff.formula_name,l_none) person_selection_rl,
5056 nvl(ler.name,l_all) ler_name,
5057 nvl(org.name,l_all) org_name,
5058 actn.name actn_typ_name,
5059 agf.name agf_fctr,
5060 bft.min_age min_age,
5061 bft.max_age max_age,
5062 los.name los_fctr,
5063 bft.min_los min_los,
5064 bft.max_los max_los,
5065 cla.name cmbn_age_los_fctr,
5066 bft.date_from date_from,
5067 conc.user_concurrent_program_name conc_prog_name,
5068 fnd1.meaning
5069 from ben_benefit_actions bft,
5070 hr_lookups hr1,
5071 hr_lookups hr2,
5072 hr_lookups hr3,
5073 hr_lookups hr4,
5074 hr_lookups hr5,
5075 hr_lookups hr6,
5076 hr_lookups hr7,
5077 fnd_lookups fnd1,
5078 ben_age_fctr agf,
5079 ben_los_fctr los,
5080 ben_cmbn_age_los_fctr cla,
5081 ben_cm_typ_f cct,
5082 ben_actn_typ actn,
5083 per_people_f ppf,
5084 per_person_types ppt,
5085 ben_pgm_f pgm1,
5086 per_business_groups pbg,
5087 ben_pl_f pln1,
5088 ff_formulas_f ff,
5089 ben_ler_f ler,
5090 hr_all_organization_units org,
5091 ben_popl_enrt_typ_cycl_f pop,
5092 ben_enrt_perd epo,
5093 ben_pl_f pln2,
5094 ben_pgm_f pgm2,
5095 hr_locations_all loc,
5096 fnd_concurrent_requests fnd,
5097 fnd_concurrent_programs_tl conc
5098 where fnd.request_id = p_concurrent_request_id
5099 and conc.concurrent_program_id = fnd.concurrent_program_id
5100 and conc.application_id = 805
5101 and conc.language = userenv('LANG') --NLS Fix
5102 and bft.request_id = fnd.request_id
5103 and hr1.lookup_code = bft.mode_cd
5104 and hr1.lookup_type = 'BEN_BENTMPCM_MD'
5105 and hr2.lookup_code = bft.validate_flag
5106 and hr2.lookup_type = 'YES_NO'
5107 and hr3.lookup_code = bft.no_programs_flag
5108 and hr3.lookup_type = 'YES_NO'
5109 and hr4.lookup_code(+) = pop.enrt_typ_cycl_cd
5110 and hr4.lookup_type(+) = 'BEN_ENRT_TYP_CYCL'
5111 and hr5.lookup_code(+) = bft.elig_enrol_cd
5112 and hr5.lookup_type(+) = 'BEN_ELIG_ENROL'
5113 and hr6.lookup_code(+) = bft.audit_log_flag
5114 and hr6.lookup_type(+) = 'YES_NO'
5115 and hr7.lookup_code = bft.cm_trgr_typ_cd
5116 and hr7.lookup_type = 'BEN_CM_TRGR_TYP'
5117 and fnd.status_code = fnd1.lookup_code
5118 and fnd1.lookup_type= 'CP_STATUS_CODE'
5119 and cct.cm_typ_id(+) = bft.cm_typ_id
5120 and bft.process_date
5121 between nvl(cct.effective_start_date,bft.process_date)
5122 and nvl(cct.effective_end_date,bft.process_date)
5123 and loc.location_id(+) = bft.location_id
5124 and actn.actn_typ_id(+) = bft.actn_typ_id
5125 and agf.age_fctr_id(+) = bft.age_fctr_id
5126 and los.los_fctr_id(+) = bft.los_fctr_id
5127 and cla.cmbn_age_los_fctr_id(+) = bft.cmbn_age_los_fctr_id
5128 and pop.popl_enrt_typ_cycl_id(+) = epo.popl_enrt_typ_cycl_id
5129 and bft.process_date
5130 between nvl(pop.effective_start_date,bft.process_date)
5131 and nvl(pop.effective_end_date,bft.process_date)
5132 -- PB : 5422 :
5133 -- and epo.enrt_perd_id(+) = bft.popl_enrt_typ_cycl_id
5134 -- and epo.asnd_lf_evt_dt(+) = bft.lf_evt_ocrd_dt
5135 and pln2.pl_id(+) = pop.pl_id
5136 and bft.process_date
5137 between nvl(pln2.effective_start_date,bft.process_date)
5138 and nvl(pln2.effective_end_date,bft.process_date)
5139 and pgm2.pgm_id(+) = pop.pgm_id
5140 and bft.process_date
5141 between nvl(pgm2.effective_start_date,bft.process_date)
5142 and nvl(pgm2.effective_end_date,bft.process_date)
5143 and ppf.person_id(+) = bft.person_id
5144 and bft.process_date
5145 between nvl(ppf.effective_start_date,bft.process_date)
5146 and nvl(ppf.effective_end_date,bft.process_date)
5147 and ppt.person_type_id(+) = bft.person_type_id
5148 and pgm1.pgm_id(+) = bft.pgm_id
5149 and bft.process_date
5150 between nvl(pgm1.effective_start_date,bft.process_date)
5151 and nvl(pgm1.effective_end_date,bft.process_date)
5152 and pbg.business_group_id = bft.business_group_id
5153 and pln1.pl_id(+) = bft.pl_id
5154 and bft.process_date
5155 between nvl(pln1.effective_start_date,bft.process_date)
5156 and nvl(pln1.effective_end_date,bft.process_date)
5157 and ler.ler_id(+) = bft.ler_id
5158 and bft.process_date
5159 between nvl(ler.effective_start_date,bft.process_date)
5160 and nvl(ler.effective_end_date,bft.process_date)
5161 and ff.formula_id(+) = bft.person_selection_rl
5162 and bft.process_date
5163 between nvl(ff.effective_start_date,bft.process_date)
5164 and nvl(ff.effective_end_date,bft.process_date)
5165 and org.organization_id(+) = bft.organization_id
5166 and bft.process_date
5167 between nvl(org.date_from,bft.process_date)
5168 and nvl(org.date_to,bft.process_date);
5169 --
5170 begin
5171 --
5172 hr_utility.set_location('Entering :'||l_proc,10);
5173 --
5174 -- Default return values for nulls
5175 --
5176 fnd_message.set_name('BEN','BEN_91792_ALL_PROMPT');
5177 --l_all := fnd_message.get;
5178 l_all := substrb(fnd_message.get,1,80); -- bug 2595834
5179 fnd_message.set_name('BEN','BEN_91793_NONE_PROMPT');
5180 l_none := substrb(fnd_message.get,1,80); -- bug 2595834
5181 --l_none := fnd_message.get;
5182 --
5183 open c_benefit_actions;
5184 fetch c_benefit_actions into p_process_date,
5185 p_mode,
5186 p_validate,
5187 p_person,
5188 p_person_type,
5189 p_program,
5190 p_business_group,
5191 p_plan,
5192 p_enrollment_period,
5193 p_plan_in_program,
5194 p_elig_enrol,
5195 p_audit_log,
5196 p_cm_trgr_typ,
5197 p_cm_typ,
5198 p_location,
5199 p_person_selection_rule,
5200 p_ler,
5201 p_organization,
5202 p_actn_typ,
5203 p_age_fctr,
5204 p_min_age,
5205 p_max_age,
5206 p_los_fctr,
5207 p_min_los,
5208 p_max_los,
5209 p_cmbn_age_los_fctr,
5210 p_date_from,
5211 p_concurrent_program_name,
5212 p_status;
5213 close c_benefit_actions;
5214 --
5215 hr_utility.set_location('Leaving :'||l_proc,10);
5216 --
5217 end;
5218 --
5219 --
5220 end ben_determine_communications;