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