[Home] [Help]
PACKAGE BODY: APPS.BEN_MANAGE_DEFAULT_ENRT
Source
1 package body ben_manage_default_enrt as
2 /* $Header: beneadeb.pkb 120.17.12010000.5 2008/08/05 14:43:05 ubhat ship $ */
3 --
4 /* ============================================================================
5 * Name
6 * Manage Default Enrollment Process
7 *
8 * Purpose
9 * This package is used to check validity of parameters passed in via SRS
10 * or via a PL/SQL function or procedure. This package will make a call to
11 * a PL/SQL package procedure will process default enrollment for
12 * a particular person or person's.
13 *
14 * History
15 * Date Who Version What?
16 * ----------- ---------- ------- ---------------------------------------
17 * 28 Mar 1998 Hugh Dang 110.0 Created.
18 * 16 Jun 1998 Hugh Dang 110.1 Message functions
19 * 23 Jun 1998 Hugh Dang 110.2 Change Per_in_ler where clause from
20 * effective_start_date to stat_cd.
21 * 29 Jun 1998 lmcdonal 110.4 Exclude programs from processing if
22 * there are results for that program and
23 * this per-in-ler in the result table.
24 * ditto for plans not in program.
25 * 21 Jul 1998 Hugh Dang 110.5 Add print parameters procedure to
26 * print out nocopy parameters list to the log
27 * file.
28 * 22 Sep 1998 Hugh Dang 115.6 Modify the where clause for dflt enrt
29 * date from >= effect date to <=
30 * effective date in g_dflt_mn_epe
31 * cursor.
32 * 13 Oct 1998 Hugh Dang 115.7 Modify the population source from
33 * Elctbl Choice table to Pil Electbl chc
34 * popl and add default process for only
35 * one comp. object.
36 * 31 Oct 1998 S Tee 115.8 Added a person_id parameter to process_
37 * post_enrollment procedure. Add
38 * per_in_ler_id to delete_enrollment.
39 * 05 Nov 1998 Hugh Dang 115.9 Change call to get_parameters.
40 * 01 Dec 1998 Hugh Dang 115.10 Remove p_mode_cd check.
41 * 02 Dec 1998 Hugh Dang 115.11 Change logic how to handle report/log,
42 * and common procedures/Function into
43 * ben_batch_utils package.
44 * 11 Dec 1998 S Tee 115.12 Added the per_in_ler_id to process
45 * common enrollment result procedure.
46 * 20-Dec-1998 Hdang 115.13 Added audit_log to turn audit log report
47 * on and off.
48 * 22-Dec-1998 Hdang 115.14 Turn Audit log report on.
49 *
50 * 28-Dec-1998 Jlamoureux 115.15 Removed dflt_enrt_dt, enrt_typ_cycl_cd,
51 * enrt_perd_strt_dt, and enrt_perd_end_dt
52 * from c_dflt_mn_epe cursor. These columns
53 * have moved to pil_elctbl_chc_popl.
54 * 28-Dec-1998 Hdang 115.16 Add Prtt_enrt_rslt_id in batch_rate tbl.
55 * 29-Dec-1998 Hdang 115.17 Call ini_proc_info from process.
56 * 01-Jan-1999 Stee 115.18 Changed post enrollment to pass a process
57 * code.
58 * 12-Jan-1999 Hdang 115.19 Added commit to force report info got
59 * commit.
60 * 03-Feb-1999 Hdang 115.20 Add logic to default comp obj procedure to
61 * handle mandatory flag.
62 * 22-Feb-1999 Hdang 115.21 Chagne p_effective_date data type from date
63 * to varchar2
64 *
65 * 22-Feb-1999 Hdang 115.22 Change multitrhead p_eefective_date.
66 * 03-MAr-1999 Stee 115.23 Removed dbms_output.put_line.
67 * 22-MAR-1999 TMathers 115.27 CHanged -MON- to /MM/
68 * 05-Apr-1999 mhoyes 115.28 - Un-datetrack of per_in_ler_f changes.
69 * 10-May-1999 jcarpent 115.29 Check ('VOIDD','BCKDT') for pil stt cd
70 * 20-JUL-1999 Gperry 115.30 genutils -> benutils package rename.
71 * 12-Aug-1999 lmcdonal 115.31 Start support for enterable cvg amounts.
72 * To complete, need task 280 to be done
73 * (changes to benelinf).
74 * 25-Aug-1999 Gperry 115.32 Added ben_env_object call to multithread.
75 * 25-AUG-1999 Gperry 115.33 Leapfrog of 115.30 with ben_env_object fix.
76 * 25-AUG-1999 Gperry 115.34 Leapfrog of 115.32 with ben_env_object fix.
77 * 14-SEP-1999 shdas 115.35 added bnft_val to election_information
78 * 06-Oct-1999 tguy 115.36 added call to dt_fndate
79 * 19-Oct-1999 maagrawa 115.37 Call to write_table_and_file changed to
80 * log the messages in the log file.
81 * 10-Nov-1999 jcarpent 115.38 Switched order of post_enrollment and
82 * post_results calls so that pil is not
83 * closed too soon.
84 * 14-Dec-1999 jcarpent 115.39 Moved close enrollment to end of process.
85 * 28-Dec-1999 stee 115.40 Added per_in_ler_id to multi_rows_edit
86 * call and removed delete enrollment as it
87 * will be deleted in the multi_rows_edit
88 * logic and dependents will be recycled
89 * properly.
90 * 01-Feb-2000 gperry 115.41 Fixed WWBUG 1176104. Multithreading not
91 * working.
92 * 10-Feb-2000 jcarpent 115.42 Bleeding benefits fixed. bnft vars set
93 * null within default loop.
94 * 04-Apr-2000 gperry 115.43 Fixed WWBUG 1217194.
95 * 11-Apr-2000 mmogel 115.45 Added tokens to messages to make them
96 * more meaningful to the user
97 * 18-May-2000 gperry 115.46 No persons selected errors silently now.
98 * WWBUG 1097159
99 * 18-May-2000 gperry 115.47 Initial performance fixes.
100 * 19-May-2000 gperry 115.48 Removed ben_timing stuff.
101 * 30-May-2000 gperry 115.49 More performance tuning.
102 * 29-Jun-2000 shdas 115.50 Added call to reinstate_dpnt
103 * 23-jan-2001 jcarpent 115.51 Bug 1609055. Set ler info when c_pel
104 * returns no rows.
105 * 01-Jul-2001 kmahendr 115.52 Unrestricted changes
106 * 13-Jul-2001 ikasire 115.53 Bug 1834566 changed the cursor c_rt
107 * where clause to remove
108 * ecr.prtt_rt_val_id is null condition.
109 * 18-dec-2001 tjesumic 115.54 cwb changes
110 * 20-dec-2001 ikasire 115.55 added dbdrv lines
111 * 30-Apr-2002 kmahendr 115.56 Added write calls to capture error messages.
112 *
113 * 08-Jun-02 pabodla 115.57 Do not select the contingent worker
114 * assignment when assignment data is
115 * fetched.
116 * 14-Nov-02 vsethi 115.58 Bug 2370264 In Default_Comp_obj changed exception
117 * handling for forms (p_batch_flag is false)
118 * 19-dec-02 pabodla 115.59 NOCOPY Changes
119 03-Jan-03 tjesumic 115.60 after the enhncemnt # 2685018 cryfwd_elig_dpnt_cd value is
120 concated with result id from where the dpnt carry forwarded ,
121 this fix will seprate the code from result id
122 * 05-Mar-03 hnarayan 115.62 Bug 2828045 - In c_person cursor of Process,
123 * uncommented BG id check and added check
124 * to exclude PILs of type COMP and ABS. Also fixed
125 * p_ler_id parameter check condition in the query.
126 * 07-Mar-03 tjesumic 115.63 2944657 whne the enrollment is closed from LE form
127 * min max restriction is nat validated. the enrollment can be
128 * made without enrolling in a plan, Min reqment of the plan is 1 though
129 * this is fixed by calling ben_proc_common_enrt_rslt.set_elcn_made_or_asnd_dt
130 * before multi_rows_edit
131 * 15-MAY-03 glingapp 115.64 2961251 Passed the pl_id parameter in
132 * ben_proc_common_enrt_rslt.set_elcn_made_or_asnd_dt.
133 * 16-Sep-03 kmahendr 115.65 GSP changes
134 * 22-Sep-03 mmudigon 115.66 Bug 3121395. Changes to c_rt cursor
135 * 6-oct-03 nhunur 115.67 made changes to exception handling in default_comp_obj procedure.
136 * 16-Oct-03 tjesumic 115.68 l_prev_enrt_rslt_id resintialized
137 * 30-Oct-03 ikasire 115.69 Bug 3192923 OVERRIDE thru date needs to be handled
138 * 13-sep-04 vvprabhu 115.70 Bug 3876613 Procedure Default_Comp_obj_w added
139 * 05-nov-04 vvprabhu 115.71 Bug 3978573 parameter p_called_from_ss added to
140 * Default_Comp_obj to pypass multi_row_edit when called from SSBEN.
141 * 03-Dec-04 ikasire 115.72 Bug 4046914
142 * 10-Jan-05 lakrish 115.73 3989075, call dt_fndate.change_ses_date to insert a row into
143 * fnd_sessions while running default enrt process from SS
144 * 26-Jan-05 ikasire 115.74 BUG 4064635 CF Suspended Interim Changes
145 * 26-May-05 vborkar 115.75 Bug 4387247 : In wrapper method exception
146 * handlers changes made to avoid null errors
147 * on SS pages
148 * 01-Jun-05 vborkar 115.76 Bug 4387247 : Modifications to take care of
149 * application exceptions.
150 * 22-Jun-05 vvprabhu 115.77 Bug 4421813 ben_env_obj.init added to default_comp_obj
151 * in cases where it is called from self-service.
152 * 20-Jul-05 ikasire 115.78 Bug 4463836 passing the p_called_from_ss to multi_row_edit call
153 * 26-Oct-05 bmanyam 115.79 4684991 - Use lf_evt_ocrd_dt instead of p_effective_date
154 * when checking for overide-thru-date
155 * 13-Dec-05 vborkar 115.80 Bug 4695708 : Made changes to Default_Comp_obj
156 * and Default_Comp_obj_w exception handlers
157 * so that error messages are correctly shown
158 * in SS.
159 * 11-Apr-06 swjain 115.82 Bug 4951065 - Updated cursor c_dflt_mn_epe_mndtry
160 * in procedure Default_Comp_obj
161 * 03-May-06 abparekh 115.83 Bug 5158204 - use minimum (enrt perd start date, defaults assnd date )
162 * for default enrollment date
163 * 14-Sep-06 abparekh 115.84 Bug 5407755 - Modified fix of version 115.83
164 * 115.85 use NVL(defaults assnd date, enrt perd end date)
165 * for close enrollment date
166 * 16-Nov-06 vvprabhu 115.86 Bug 5664300 - added p_called_frm_ss parameter to
167 * 115.87 process_post_results
168 * 30-Nov-06 rtagarra 115.88 Bug 5662220 - Added check so that when there is no condition for a person
169 * in person_selection_rule then skip the person.
170 * 22-Jan-07 kmahendr 115.89 Bug#5768880 - changed cursor c_pel to filter
171 out unrestricted pels
172 * 27-apr-07 nhunur 115.90 changed incorrect join condition introduced above
173
174 * 24-May-07 sjilla 115.91 Bug 6027345 - Additional more specific Exception hadler used.
175 * 06-aug-07 swjain 115.92 Bug 6319484 - Updated cursor c_dflt_mn_epe
176 * 09-Aug-07 vvprabhu 115.93 Bug 5857493 - added g_audit_flag to
177 * control person selection rule error logging
178 * 12-jun-08 bachakra 115.97 Bug 7166971 - added clause in c_pel to apply defaults
179 for those programs whose default assigned date is less
180 than effective date. Also removed the fix for 6992857
181 as that is not the expected functioanlity.
182 * -----------------------------------------------------------------------------------
183 */
184 --
185 -- Global cursor and variables declaration
186 --
187 g_package varchar2(80) := 'Ben_manage_default_Enrt';
188 g_max_errors_allowed number(9) := 200;
189 g_persons_errored number(9) := 0;
190 g_persons_procd number(9) := 0;
191 g_rec benutils.g_batch_ler_rec;
192 --
193 -- ===========================================================================
194 -- << Procedure: Write_person_category >>
195 -- ===========================================================================
196 --
197 Procedure write_person_category
198 (p_audit_log varchar2 default 'N'
199 ,p_error Boolean default FALSE
200 ,p_business_group_id number
201 ,P_person_id number
202 ,p_effective_date date
203 ) is
204 --
205 Cursor c1 (c_prtt_enrt_rslt_id number) is
206 Select ecd.dpnt_person_id, ecd.cvg_strt_dt, ecd.cvg_thru_dt
207 From ben_elig_cvrd_dpnt_f ecd,
208 ben_per_in_ler pil
209 Where ecd.prtt_enrt_rslt_id is not NULL
210 and ecd.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
211 and ecd.business_group_id = p_business_group_id
212 and p_effective_date between
213 ecd.effective_start_date and ecd.effective_end_date
214 and pil.per_in_ler_id=ecd.per_in_ler_id
215 --and pil.business_group_id=ecd.business_group_id
216 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
217
218
219
220 l_proc varchar2(80) := g_package||'.write_person_category';
221 l_actn varchar2(80);
222 l_cache ben_batch_utils.g_comp_obj_table := ben_batch_utils.g_cache_comp;
223 l_cache_cnt binary_integer := ben_batch_utils.g_cache_comp_cnt;
224 l_category varchar2(30):= 'DEFNOCHG';
225 l_detail varchar2(132) := 'Default election asigned -- no current elections changed' ;
226 l_OVN Number;
227 l_id Number;
228 l_OVN1 varchar2(240);
229 l_actn_cd varchar2(30);
230 Begin
231 hr_utility.set_location ('Entering '||l_proc,05);
232 l_actn := 'Starting...';
233 If (p_error) then
234 If (p_audit_log = 'Y') then
235 l_category := 'ERROR_C';
236 l_detail := 'Error occur while defaulting enrollment';
237 --
238 l_actn := 'Calling ben_batch_utils.write_rec (ERROR)...';
239 Ben_batch_utils.write_rec(p_typ_cd => l_category ,p_text => l_detail);
240 End if;
241 Else
242 For i in 1..l_cache_cnt Loop
243 If (l_cache(i).upd_flag or l_cache(i).ins_flag or l_cache(i).del_flag) then
244 l_category := 'DEFWCHG';
245 l_detail := 'Default elections assigned -- some or all current election changed';
246 exit;
247 End if;
248 End loop;
249 --
250 l_actn := 'Calling ben_batch_utils.write_rec (DEFAULT)...';
251 Ben_batch_utils.write_rec(p_typ_cd => l_category,p_text => l_detail);
252 End if;
253 If (p_audit_log = 'Y') then
254 For i in 1..l_cache_cnt Loop
255 If (l_cache(i).del_flag) then
256 l_actn_cd := 'DEL';
257 Elsif (l_cache(i).ins_flag) then
258 l_actn_cd := 'INS';
259 Elsif (l_cache(i).upd_flag) then
260 l_actn_cd := 'UPD';
261 Elsif (l_cache(i).def_flag) then
262 l_actn_cd := 'DEF';
263 End if;
264 --
265 l_actn := 'Calling ben_batch_rate_info_api.create_batch_rate_info...';
266 insert into ben_batch_rate_info
267 (batch_rt_id,
268 benefit_action_id,
269 person_id,
270 pgm_id,
271 pl_id,
272 oipl_id,
273 dflt_val,
274 val,
275 actn_cd,
276 dflt_flag,
277 business_group_id,
278 object_version_number)
279 values
280 (ben_batch_rate_info_s.nextval,
281 benutils.g_benefit_action_id,
282 p_person_id,
283 l_cache(i).pgm_id,
284 l_cache(i).pl_id,
285 l_cache(i).oipl_id,
286 l_cache(i).bnft_amt,
287 l_cache(i).prtt_enrt_rslt_id,
288 l_actn_cd,
289 'Y',
290 p_business_group_id,
291 1);
292 --
293 If (l_cache(i).prtt_enrt_rslt_id is not NULL) then
294 For l_rec in c1(l_cache(i).prtt_enrt_rslt_id) loop
295 --
296 l_actn := 'Calling ben_batch_dpnt_info_api.create_batch_dpnt_info...';
297 insert into ben_batch_dpnt_info
298 (batch_dpnt_id,
299 person_id,
300 benefit_action_id,
301 business_group_id,
302 pgm_id,
303 pl_id,
304 oipl_id,
305 enrt_cvg_strt_dt,
306 enrt_cvg_thru_dt,
307 actn_cd,
308 object_version_number,
309 dpnt_person_id)
310 values
311 (ben_batch_dpnt_info_s.nextval,
312 p_person_id,
313 benutils.g_benefit_action_id,
314 p_business_group_id,
315 l_cache(i).pgm_id,
316 l_cache(i).pl_id,
317 l_cache(i).oipl_id,
318 l_rec.cvg_strt_dt,
319 l_rec.cvg_thru_dt,
320 l_actn_cd,
321 1,
322 l_rec.dpnt_person_id);
323 --
324 End loop;
325 End if;
326 End loop;
327 End if;
328 hr_utility.set_location ('Leaving '||l_proc,10);
329 Exception
330 When others then
331 ben_batch_utils.rpt_error(p_proc => l_proc
332 ,p_last_actn => l_actn
333 ,p_rpt_flag => TRUE
334 );
335 raise;
336 End write_person_category;
337 --
338 -- ===========================================================================
339 -- << Procedure: ben_batch_utils.batch_report >>
340 -- ===========================================================================
341 --
342 Procedure Submit_all_reports
343 (p_rpt_flag in Boolean default FALSE
344 ,p_audit_log in varchar2 default 'N'
345 ) is
346 l_proc varchar2(80) := g_package||'.submit_all_reports';
347 l_actn varchar2(80);
348 l_request_id number;
349 Begin
350 hr_utility.set_location ('Entering '||l_proc,05);
351 If fnd_global.conc_request_id <> -1 then
352 l_actn := 'Calling ben_batch_utils.batch_report (BENDFAUD)...';
353 If (p_audit_log = 'Y') then
354 ben_batch_utils.batch_report
355 (p_concurrent_request_id => fnd_global.conc_request_id
356 ,p_program_name => 'BENDFAUD'
357 ,p_request_id => l_request_id
358 );
359 End if;
360 l_actn := 'Calling ben_batch_utils.batch_report (BENDFSUM)...';
361 ben_batch_utils.batch_report
362 (p_concurrent_request_id => fnd_global.conc_request_id
363 ,p_program_name => 'BENDFSUM'
364 ,p_request_id => l_request_id
365 );
366 l_actn := 'Calling ben_batch_utils.batch_report (BENERTYP)...';
367 ben_batch_utils.batch_report
368 (p_concurrent_request_id => fnd_global.conc_request_id
369 ,p_program_name => 'BENERTYP'
370 ,p_request_id => l_request_id
371 ,p_subtitle =>
372 'ERROR DETAIL BY ERROR TYPE'
373 );
374 l_actn := 'Calling ben_batch_utils.batch_report (BENERPER)...';
375 ben_batch_utils.batch_report
376 (p_concurrent_request_id => fnd_global.conc_request_id
377 ,p_program_name => 'BENERPER'
378 ,p_request_id => l_request_id
379 ,p_subtitle =>
380 'ERROR DETAIL BY PERSON'
381 );
382 End if;
383 hr_utility.set_location ('Leaving '||l_proc,10);
384 Exception
385 When others then
386 ben_batch_utils.rpt_error(p_proc => l_proc
387 ,p_last_actn => l_actn
388 ,p_rpt_flag => p_rpt_flag
389 );
390 raise;
391 End Submit_all_reports;
392 --
393 -- ============================================================================
394 -- << Procedure: Do_Multithread >>
395 -- Description:
396 -- this is a main procedure to invoke the Default enrollment process.
397 -- ============================================================================
398 procedure do_multithread
399 (errbuf out nocopy varchar2
400 ,retcode out nocopy number
401 ,p_validate in varchar2 default 'N'
402 ,p_benefit_action_id in number
403 ,p_thread_id in number
404 ,p_effective_date in varchar2
405 ,p_business_group_id in number
406 ,p_audit_log in varchar2 default 'N'
407 ) is
408 --
409 -- Local variable declaration
410 --
411 l_proc varchar2(80) := g_package||'.do_multithread';
412 l_person_id ben_person_actions.person_id%type;
413 l_person_action_id ben_person_actions.person_action_id%type;
414 l_object_version_number ben_person_actions.object_version_number%type;
415 l_ler_id ben_person_actions.ler_id%type;
416 l_range_id ben_batch_ranges.range_id%type;
417 l_record_number number := 0;
418 l_start_person_action_id number := 0;
419 l_end_person_action_id number := 0;
420 l_actn varchar2(80);
421 l_cnt number(5):= 0;
422 l_chunk_size number(15);
423 l_threads number(15);
424 l_effective_date date;
425 --
426 -- Cursors declaration
427 --
428 Cursor c_range_thread is
429 Select ran.range_id
430 ,ran.starting_person_action_id
431 ,ran.ending_person_action_id
432 From ben_batch_ranges ran
433 Where ran.range_status_cd = 'U'
434 And ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
435 And rownum < 2
436 For update of ran.range_status_cd
437 ;
438 Cursor c_person_thread is
439 Select ben.person_id
440 ,ben.person_action_id
441 ,ben.object_version_number
442 ,ben.ler_id
443 From ben_person_actions ben
444 Where ben.benefit_action_id = p_benefit_action_id
445 And ben.action_status_cd <> 'P'
446 And ben.person_action_id between
447 l_start_person_action_id and l_end_person_action_id
448 Order by ben.person_action_id
449 ;
450 Cursor c_parameter is
451 Select *
452 From ben_benefit_actions ben
453 Where ben.benefit_action_id = p_benefit_action_id
454 ;
455 l_parm c_parameter%rowtype;
456 l_commit number;
457 --
458 Begin
459 --
460 hr_utility.set_location ('Entering '||l_proc,05);
461 --
462 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
463 --
464 -- Put row in fnd_sessions
465 --
466 dt_fndate.change_ses_date
467 (p_ses_date => l_effective_date,
468 p_commit => l_commit);
469 --
470 l_actn := 'Calling benutils.get_parameter...';
471 benutils.get_parameter(p_business_group_id => p_business_group_Id
472 ,p_batch_exe_cd => 'BENEADEB'
473 ,p_threads => l_threads
474 ,p_chunk_size => l_chunk_size
475 ,p_max_errors => g_max_errors_allowed );
476 --
477 -- Set up benefits environment
478 --
479 ben_env_object.init(p_business_group_id => p_business_group_id,
480 p_effective_date => l_effective_date,
481 p_thread_id => p_thread_id,
482 p_chunk_size => l_chunk_size,
483 p_threads => l_threads,
484 p_max_errors => g_max_errors_allowed,
485 p_benefit_action_id => p_benefit_action_id);
486 --
487 l_actn := 'Calling ben_batch_utils.ini...';
488 ben_batch_utils.ini;
489 --
490 -- Copy benefit action id to global in benutils package
491 --
492 benutils.g_benefit_action_id := p_benefit_action_id;
493 benutils.g_thread_id := p_thread_id;
494 g_persons_errored := 0;
495 g_persons_procd := 0;
496 open c_parameter;
497 fetch c_parameter into l_parm;
498 close c_parameter;
499 --
500 l_actn := 'Calling ben_batch_utils.print_parameters...';
501 --
502 ben_batch_utils.print_parameters
503 (p_thread_id => p_thread_id
504 ,p_benefit_action_id => p_benefit_action_id
505 ,p_validate => p_validate
506 ,p_business_group_id => p_business_group_id
507 ,p_effective_date => l_effective_date
508 ,p_popl_enrt_typ_cycl_id => l_parm.popl_enrt_typ_cycl_id
509 ,p_person_id => l_parm.person_id
510 ,p_person_selection_rule_id => l_parm.person_selection_rl
511 ,p_person_type_id => l_parm.person_type_id
512 ,p_ler_id => l_parm.ler_id
513 ,p_organization_id => l_parm.organization_id
514 ,p_benfts_grp_id => l_parm.benfts_grp_id
515 ,p_location_id => l_parm.location_id
516 ,p_legal_entity_id => l_parm.legal_entity_id
517 ,p_payroll_id => l_parm.payroll_id
518 ,p_audit_log => l_parm.audit_log_flag
519 );
520 --
521 -- While loop to only try and fetch records while they exist
522 -- we always try and fetch the size of the chunk, if we get less
523 -- then we know that the process is finished so we end the while loop.
524 -- The process is as follows :
525 -- 1) Lock the rows that are not processed
526 -- 2) Grab as many rows as we can upto the chunk size
527 -- 3) Put each row into the person cache.
528 -- 4) Process the person cache
529 -- 5) Go to number 1 again.
530 --
531 Loop
532 l_actn := 'Opening c_range thread and fetch range...';
533 open c_range_thread;
534 fetch c_range_thread into l_range_id
535 ,l_start_person_action_id
536 ,l_end_person_action_id;
537 exit when c_range_thread%notfound;
538 close c_range_thread;
539 If(l_range_id is not NULL) then
540 --
541 l_actn := 'Updating ben_batch_ranges row...';
542 --
543 update ben_batch_ranges ran set ran.range_status_cd = 'P'
544 where ran.range_id = l_range_id;
545 commit;
546 End if;
547 --
548 -- Remove all records from cache
549 --
550 l_actn := 'Clearing g_cache_person_process cache...';
551 g_cache_person_process.delete;
552 open c_person_thread;
553 l_record_number := 0;
554 Loop
555 --
556 l_actn := 'Loading person data into g_cache_person_process cache...';
557 --
558 fetch c_person_thread
559 into g_cache_person_process(l_record_number+1).person_id
560 ,g_cache_person_process(l_record_number+1).person_action_id
561 ,g_cache_person_process(l_record_number+1).object_version_number
562 ,g_cache_person_process(l_record_number+1).ler_id;
563 exit when c_person_thread%notfound;
564 l_record_number := l_record_number + 1;
565 End loop;
566 close c_person_thread;
567 --
568 l_actn := 'Preparing to default each participant from cache...' ;
569 --
570 If l_record_number > 0 then
571 --
572 -- Process the rows from the person process cache
573 --
574 For l_cnt in 1..l_record_number loop
575 Begin
576 ben_manage_default_enrt.process_default_enrt
577 (p_validate => p_validate
578 ,p_person_id => g_cache_person_process(l_cnt).person_id
579 ,p_business_group_id => p_business_group_id
580 ,p_effective_date => l_effective_date
581 ,p_person_action_id => g_cache_person_process(l_cnt).person_action_id
582 ,p_object_version_number => g_cache_person_process(l_cnt).object_version_number
583 ,p_audit_log => p_audit_log
584 );
585 --
586 Exception
587 When others then
588 If (g_persons_errored > g_max_errors_allowed) then
589 fnd_message.raise_error;
590 End if;
591 End;
592 End loop;
593 Else
594 --
595 l_actn := 'Erroring out nocopy since not person is found in range...' ;
596 --
597 fnd_message.set_name('BEN','BEN_91709_PER_NOT_FND_IN_RNG');
598 fnd_message.set_token('PROCEDURE',l_proc);
599 fnd_message.raise_error;
600 End if;
601 commit;
602 End loop;
603 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
604 commit;
605 --
606 l_actn := 'Calling Log_beneadeb_statistics...';
607 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
608 ,p_num_pers_errored => g_persons_errored
609 );
610 hr_utility.set_location ('Leaving '||l_proc,70);
611 Exception
612 When others then
613 ben_batch_utils.rpt_error(p_proc => l_proc
614 ,p_last_actn => l_actn
615 ,p_rpt_flag => TRUE
616 );
617 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
618 ,p_num_pers_errored => g_persons_errored
619 );
620 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
621 commit;
622 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
623 fnd_message.set_token('PROCEDURE', l_proc);
624 fnd_message.set_token('STEP',l_actn );
625 fnd_message.raise_error;
626 --
627 end do_multithread;
628 --
629 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
630 -- << Procedure: Restart >>
631 -- *****************************************************************
632 --
633 procedure restart (errbuf out nocopy varchar2
634 ,retcode out nocopy number
635 ,p_benefit_action_id in number
636 ) is
637 --
638 -- Cursor Declaration
639 --
640 cursor c_parameters is
641 Select -- to_char(process_date,'YYYY/MM/DD HH24:MI:SS') process_date
642 fnd_date.date_to_canonical(process_date) process_date
643 ,validate_flag
644 ,person_id
645 ,person_type_id
646 ,pgm_id
647 ,business_group_id
648 ,pl_id
649 ,popl_enrt_typ_cycl_id
650 ,person_selection_rl
651 ,ler_id
652 ,organization_id
653 ,benfts_grp_id
654 ,location_id
655 ,legal_entity_id
656 ,payroll_id
657 ,debug_messages_flag
658 ,audit_log_flag
659 From ben_benefit_actions ben
660 Where ben.benefit_action_id = p_benefit_action_id;
661 --
662 -- Local Variable declaration.
663 --
664 l_proc varchar2(80) := g_package||'.restart';
665 l_parameters c_parameters%rowtype;
666 l_errbuf varchar2(80);
667 l_retcode number;
668 l_actn varchar2(80);
669 Begin
670 hr_utility.set_location ('Entering '||l_proc,10);
671 --
672 -- get the parameters for a previous run and do a restart
673 --
674 l_actn := 'Getting parameter data...';
675 open c_parameters;
676 fetch c_parameters into l_parameters;
677 If c_parameters%notfound then
678 ben_batch_utils.rpt_error(p_proc => l_proc
679 ,p_last_actn => l_actn
680 ,p_rpt_flag => TRUE
681 );
682 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
683 fnd_message.set_token('PROC',l_proc);
684 fnd_message.raise_error;
685 End if;
686 close c_parameters;
687 --
688 -- Call process procedure with parameters for restart
689 --
690 l_actn := 'Calling process...';
691 Process (errbuf => l_errbuf
692 ,retcode => l_retcode
693 ,p_benefit_action_id => p_benefit_action_id
694 ,p_effective_date => l_parameters.process_date
695 ,p_validate => l_parameters.validate_flag
696 ,p_person_id => l_parameters.person_id
697 ,p_person_type_id => l_parameters.person_type_id
698 ,p_business_group_id => l_parameters.business_group_id
699 ,p_popl_enrt_typ_cycl_id => l_parameters.popl_enrt_typ_cycl_id
700 ,p_ler_id => l_parameters.ler_id
701 ,p_organization_id => l_parameters.organization_id
702 ,p_benfts_grp_id => l_parameters.benfts_grp_id
703 ,p_location_id => l_parameters.location_id
704 ,p_legal_entity_id => l_parameters.legal_entity_id
705 ,p_payroll_id => l_parameters.payroll_id
706 ,p_debug_messages => l_parameters.debug_messages_flag
707 ,p_audit_log => l_parameters.audit_log_flag
708 );
709 hr_utility.set_location ('Leaving '||l_proc,70);
710 Exception
711 when others then
712 ben_batch_utils.rpt_error(p_proc => l_proc
713 ,p_last_actn => l_actn
714 ,p_rpt_flag => TRUE
715 );
716 raise;
717 end restart;
718 --
719 -- *************************************************************************
720 -- * << Procedure: Process >>
721 -- *************************************************************************
722 --
723 procedure process(errbuf out nocopy varchar2
724 ,retcode out nocopy number
725 ,p_benefit_action_id in number
726 ,p_effective_date in varchar2
727 ,p_validate in varchar2 default 'N'
728 ,p_person_id in number default null
729 ,p_person_type_id in number default null
730 ,p_business_group_id in number
731 ,p_popl_enrt_typ_cycl_id in number default null
732 ,p_person_selection_rule_id in number default null
733 ,p_ler_id in number default null
734 ,p_organization_id in number default null
735 ,p_benfts_grp_id in number default null
736 ,p_location_id in number default null
737 ,p_legal_entity_id in number default null
738 ,p_payroll_id in number default null
739 ,p_debug_messages in varchar2 default 'N'
740 ,p_audit_log in varchar2 default 'N') is
741 --
742 -- local variable declaration.
743 --
744 l_effective_date date;
745 l_request_id number;
746 l_proc varchar2(80) := g_package||'.process';
747 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
748 l_object_version_number ben_benefit_actions.object_version_number%type;
749 l_person_id_out per_people_f.person_id%type;
750 l_range_id ben_batch_ranges.range_id%type;
751 l_chunk_size number;
752 l_threads number;
753 l_start_person_action_id number := 0;
754 l_end_person_action_id number := 0;
755 l_rule_value varchar2(30);
756 l_skip boolean;
757 l_actn varchar2(80);
758 l_num_ranges number := 0;
759 l_num_rows number := 0;
760 l_num_persons number := 0;
761 l_commit number;
762 --
763 -- Cursors declaration.
764 --
765 --
766 -- Bug fix 2828045 - uncommented the BG id check =>
767 -- pil.business_group_id = p_business_group_id, and added
768 -- check for filtering out PILs of type COMP and ABS, since these
769 -- PILs should not be considered for Default Enrt Process.
770 -- Also, fixed the p_ler_id comparison clause along with this.
771 -- The p_ler_id param is compared with pil.per_in_ler_id instead
772 -- of pil.ler_id
773 --
774 cursor c_person is
775 select pil.person_id
776 from ben_per_in_ler pil
777 where
778 pil.business_group_id = p_business_group_id -- 2828045
779 and pil.per_in_ler_id in
780 (select pel.per_in_ler_id
781 from ben_pil_elctbl_chc_popl pel
782 where pel.business_group_id = pil.business_group_id
783 and pel.per_in_ler_id = pil.per_in_ler_id
784 and pel.dflt_enrt_dt <= l_effective_date
785 and pel.dflt_asnd_dt is NULL
786 and pel.ELCNS_MADE_DT is NULL)
787 and pil.per_in_ler_stat_cd = 'STRTD'
788 and (p_person_id is NULL or pil.person_id = p_person_id)
789 -- and (p_ler_id is NULL or pil.per_in_ler_id = p_ler_id) -- 2828045
790 and (p_ler_id is NULL or pil.ler_id = p_ler_id)
791 and exists (select null -- 2828045
792 from ben_ler_f ler
793 where ler.ler_id = pil.ler_id
794 and ler.typ_cd not in ('COMP','ABS','GSP')
795 and l_effective_date
796 between ler.effective_start_date
797 and ler.effective_end_date
798 )
799 and (p_person_type_id is null
800 or exists (select null
801 from per_person_type_usages ptu
802 where ptu.person_id = pil.person_id
803 and ptu.person_type_id = p_person_type_id))
804 -- Bug : 2170794 Commented the code below and added the new clauses .
805 /* and ((p_location_id is null
806 and p_organization_id is null
807 and p_legal_entity_id is null
808 and p_payroll_id is null)
809 or exists (select null
810 from per_all_assignments_f asg
811 where nvl(asg.location_id,hr_api.g_number) =
812 nvl(p_location_id,hr_api.g_number)
813 and nvl(asg.organization_id,hr_api.g_number) =
814 nvl(p_organization_id,hr_api.g_number)
815 and nvl(asg.soft_coding_keyflex_id,hr_api.g_number) =
816 nvl(p_legal_entity_id,hr_api.g_number)
817 and nvl(asg.payroll_id, hr_api.g_number)=
818 nvl(p_payroll_id,hr_api.g_number)
819 and asg.person_id = pil.person_id
820 and asg.primary_flag = 'Y'
821 and asg.business_group_id = pil.business_group_id
822 and l_effective_date
823 between asg.effective_start_date
824 and asg.effective_end_date))
825 */
826 and ((p_location_id is null )
827 or exists (select null
828 from per_all_assignments_f asg
829 where asg.location_id = p_location_id
830 and asg.person_id = pil.person_id
831 and asg.assignment_type <> 'C'
832 and asg.primary_flag = 'Y'
833 and asg.business_group_id = pil.business_group_id
834 and l_effective_date
835 between asg.effective_start_date and asg.effective_end_date))
836 and (( p_organization_id is null )
837 or exists (select null
838 from hr_organization_units org,
839 per_all_assignments_f asg
840 where asg.organization_id = org.organization_id
841 and org.organization_id = p_organization_id
842 and l_effective_date
843 between org.date_from and nvl(org.date_to,l_effective_date )
844 and asg.person_id = pil.person_id
845 and asg.assignment_type <> 'C'
846 and asg.primary_flag = 'Y'
847 and asg.business_group_id = pil.business_group_id
848 and l_effective_date
849 between asg.effective_start_date and asg.effective_end_date))
850 and (( p_legal_entity_id is null )
851 or exists (select null
852 from hr_soft_coding_keyflex hsc,
853 per_all_assignments_f asg
854 where asg.person_id = pil.person_id
855 and asg.assignment_type <> 'C'
856 and asg.primary_flag = 'Y'
857 and asg.business_group_id = pil.business_group_id
858 and l_effective_date
859 between asg.effective_start_date and asg.effective_end_date
860 and asg.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
861 and hsc.segment1 = to_char(p_legal_entity_id)))
862 and (( p_payroll_id is null)
863 or exists (select null
864 from pay_payrolls_f pay,
865 per_all_assignments_f asg
866 where asg.person_id = pil.person_id
867 and asg.assignment_type <> 'C'
868 and asg.primary_flag = 'Y'
869 and asg.business_group_id = pil.business_group_id
870 and l_effective_date
871 between asg.effective_start_date and asg.effective_end_date
872 and pay.payroll_id = p_payroll_id
873 and pay.payroll_id = asg.payroll_id
874 and l_effective_date
875 between pay.effective_start_date and pay.effective_end_date ))
876 -- Bug : 2170794
877 and (p_benfts_grp_id is null
878 or exists (select null
879 from ben_benfts_grp bng,
880 per_all_people_f ppf
881 where bng.benfts_grp_id = p_benfts_grp_id
882 And bng.business_group_id = pil.business_group_id
883 And ppf.person_id = pil.person_id
884 And ppf.benefit_group_id = bng.benfts_grp_id
885 And l_effective_date
886 between ppf.effective_start_date
887 and ppf.effective_end_date));
888 --
889 l_person_action_id l_number_type := l_number_type();
890 l_person_id l_number_type := l_number_type();
891 l_silent_error exception;
892 --
893 Begin
894 --
895 hr_utility.set_location ('Entering '||l_proc,10);
896 -- Bug 5857493
897 if p_audit_log ='Y' then
898 ben_batch_utils.g_audit_flag := true;
899 else
900 ben_batch_utils.g_audit_flag := false;
901 end if;
902 --
903 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
904 --
905 -- Put row in fnd_sessions
906 --
907 dt_fndate.change_ses_date
908 (p_ses_date => l_effective_date,
909 p_commit => l_commit);
910 --
911 l_actn := 'Initialize the ben_batch_utils cache...';
912 --
913 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
914 --
915 -- Get chunk_size and Thread values for multi-thread process, and check to
916 --
917 l_actn := 'Calling benutils.get_parameter...';
918 benutils.get_parameter(p_business_group_id => p_business_group_Id
919 ,p_batch_exe_cd => 'BENEADEB'
920 ,p_threads => l_threads
921 ,p_chunk_size => l_chunk_size
922 ,p_max_errors => g_max_errors_allowed);
923 --
924 benutils.g_benefit_action_id := p_benefit_action_id;
925 benutils.g_thread_id := 99;
926 --
927 -- Create benefit actions parameters in the benefit action table.
928 -- Do not create if a benefit action already exists, in other words
929 -- we are doing a restart.
930 --
931 if p_benefit_action_id is null then
932 --
933 ben_benefit_actions_api.create_perf_benefit_actions
934 (p_validate => false
935 ,p_benefit_action_id => l_benefit_action_id
936 ,p_process_date => l_effective_date
937 ,p_mode_cd => 'S'
938 ,p_derivable_factors_flag => 'N'
939 ,p_validate_flag => p_validate
940 ,p_person_id => p_person_id
941 ,p_person_type_id => p_person_type_id
942 ,p_pgm_id => NULL
943 ,p_business_group_id => p_business_group_id
944 ,p_pl_id => NULL
945 ,p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id
946 ,p_no_programs_flag => 'N'
947 ,p_no_plans_flag => 'N'
948 ,p_comp_selection_rl => NULL
949 ,p_person_selection_rl => p_person_selection_rule_id
950 ,p_ler_id => p_ler_id
951 ,p_organization_id => p_organization_id
952 ,p_benfts_grp_id => p_benfts_grp_id
953 ,p_location_id => p_location_id
954 ,p_pstl_zip_rng_id => NULL
955 ,p_rptg_grp_id => NULL
956 ,p_pl_typ_id => NULL
957 ,p_opt_id => NULL
958 ,p_eligy_prfl_id => NULL
959 ,p_vrbl_rt_prfl_id => NULL
960 ,p_legal_entity_id => p_legal_entity_id
961 ,p_payroll_id => p_payroll_id
962 ,p_audit_log_flag => p_audit_log
963 ,p_debug_messages_flag => p_debug_messages
964 ,p_object_version_number => l_object_version_number
965 ,p_effective_date => l_effective_date
966 ,p_request_id => fnd_global.conc_request_id
967 ,p_program_application_id => fnd_global.prog_appl_id
968 ,p_program_id => fnd_global.conc_program_id
969 ,p_program_update_date => sysdate);
970 --
971 benutils.g_benefit_action_id := l_benefit_action_id;
972 --
973 -- Now lets create person actions for all the people we are going to
974 -- process in the BENEADEB run.
975 --
976 open c_person;
977 --
978 l_actn := 'Loading person_actions table..';
979 --
980 loop
981 --
982 fetch c_person into l_person_id_out;
983 exit when c_person%notfound;
984 --
985 l_skip := FALSE;
986 --
987 if p_person_selection_rule_id is not null then
988 --
989 l_actn := 'Calling Ben_batch_utils.person_selection_rule...';
990 l_rule_value :=
991 ben_batch_utils.person_selection_rule
992 (p_person_id => l_person_id_out
993 ,p_business_group_id => p_business_group_id
994 ,p_person_selection_rule_id=> p_person_selection_rule_id
995 ,p_effective_date => l_effective_date);
996 --
997 if l_rule_value = 'N' then
998 --
999 l_skip := TRUE;
1000 --
1001 elsif l_rule_value = 'Y' then -- Bug 5662220
1002 --
1003 l_skip := FALSE;
1004 --
1005 else
1006 --
1007 l_skip := TRUE; -- Bug 5662220
1008 --
1009 end if;
1010 --
1011 end if;
1012 --
1013 -- Store person_id into person actions table.
1014 --
1015 if not l_skip then
1016 --
1017 l_num_persons := l_num_persons + 1;
1018 l_num_rows := l_num_rows + 1;
1019 --
1020 -- Extend person_action_id type
1021 --
1022 l_person_action_id.extend(1);
1023 --
1024 -- Get Primary Key value
1025 --
1026 select ben_person_actions_s.nextval
1027 into l_person_action_id(l_num_rows)
1028 from sys.dual;
1029 --
1030 -- Extend person_id type
1031 --
1032 l_person_id.extend(1);
1033 l_person_id(l_num_rows) := l_person_id_out;
1034 --
1035 if l_num_rows = l_chunk_size then
1036 --
1037 -- Bulk bind in person actions
1038 --
1039 forall l_count in 1..l_num_rows
1040 --
1041 insert into ben_person_actions
1042 (person_action_id,
1043 person_id,
1044 ler_id,
1045 benefit_action_id,
1046 action_status_cd,
1047 object_version_number)
1048 values
1049 (l_person_action_id(l_count),
1050 l_person_id(l_count),
1051 0,
1052 l_benefit_action_id,
1053 'U',
1054 1);
1055 --
1056 l_num_ranges := l_num_ranges + 1;
1057 --
1058 -- Select next sequence number for the range
1059 --
1060 select ben_batch_ranges_s.nextval
1061 into l_range_id
1062 from sys.dual;
1063 --
1064 -- Calculate start and end points of the range
1065 --
1066 l_start_person_action_id := l_person_action_id(1);
1067 l_end_person_action_id := l_person_action_id(l_num_rows);
1068 --
1069 insert into ben_batch_ranges
1070 (range_id,
1071 benefit_action_id,
1072 range_status_cd,
1073 starting_person_action_id,
1074 ending_person_action_id,
1075 object_version_number)
1076 values
1077 (l_range_id,
1078 l_benefit_action_id,
1079 'U',
1080 l_start_person_action_id,
1081 l_end_person_action_id,
1082 1);
1083 --
1084 l_num_rows := 0;
1085 --
1086 -- Dispose of varray
1087 --
1088 l_person_action_id.delete;
1089 l_person_id.delete;
1090 --
1091 commit;
1092 --
1093 end if;
1094 --
1095 end if;
1096 --
1097 end loop;
1098 --
1099 close c_person;
1100 --
1101 if l_num_rows <> 0 then
1102 --
1103 forall l_count in 1..l_num_rows
1104 --
1105 -- Bulk bind in person actions
1106 --
1107 insert into ben_person_actions
1108 (person_action_id,
1109 person_id,
1110 ler_id,
1111 benefit_action_id,
1112 action_status_cd,
1113 object_version_number)
1114 values
1115 (l_person_action_id(l_count),
1116 l_person_id(l_count),
1117 0,
1118 l_benefit_action_id,
1119 'U',
1120 1);
1121 --
1122 l_num_ranges := l_num_ranges + 1;
1123 --
1124 -- Get next sequence for the range
1125 --
1126 select ben_batch_ranges_s.nextval
1127 into l_range_id
1128 from sys.dual;
1129 --
1130 l_start_person_action_id := l_person_action_id(1);
1131 l_end_person_action_id := l_person_action_id(l_num_rows);
1132 --
1133 insert into ben_batch_ranges
1134 (range_id,
1135 benefit_action_id,
1136 range_status_cd,
1137 starting_person_action_id,
1138 ending_person_action_id,
1139 object_version_number)
1140 values
1141 (l_range_id,
1142 l_benefit_action_id,
1143 'U',
1144 l_start_person_action_id,
1145 l_end_person_action_id,
1146 1);
1147 --
1148 l_num_rows := 0;
1149 --
1150 -- Dispose of data in varrays
1151 --
1152 l_person_action_id.delete;
1153 l_person_id.delete;
1154 --
1155 commit;
1156 --
1157 end if;
1158 --
1159 Else
1160 --
1161 l_benefit_action_id := p_benefit_action_id;
1162 l_actn := 'Calling Ben_batch_utils.create_restart_person_actions...';
1163 --
1164 Ben_batch_utils.create_restart_person_actions
1165 (p_benefit_action_id => p_benefit_action_id
1166 ,p_effective_date => l_effective_date
1167 ,p_chunk_size => l_chunk_size
1168 ,p_threads => l_threads
1169 ,p_num_ranges => l_num_ranges
1170 ,p_num_persons => l_num_persons);
1171 --
1172 End if;
1173 --
1174 commit;
1175 --
1176 -- Now to multithread the code.
1177 --
1178 If l_num_ranges > 1 then
1179 --
1180 For l_count in 1..least(l_threads,l_num_ranges)-1 loop
1181 --
1182 l_actn := 'Submitting job to con-current manager...';
1183 l_request_id := fnd_request.submit_request
1184 (application => 'BEN'
1185 ,program => 'BENDFLT'
1186 ,description => NULL
1187 ,sub_request => FALSE
1188 ,argument1 => p_validate
1189 ,argument2 => l_benefit_action_id
1190 ,argument3 => l_count
1191 ,argument4 => p_effective_date
1192 ,argument5 => p_business_group_id
1193 ,argument6 => p_audit_log);
1194 --
1195 -- Store the request id of the concurrent request
1196 --
1197 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
1198 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
1199 := l_request_id;
1200 --
1201 commit;
1202 --
1203 End loop;
1204 --
1205 Elsif (l_num_ranges = 0 ) then
1206 --
1207 l_actn := 'Calling Ben_batch_utils.print_parameters...';
1208 --
1209 Ben_batch_utils.print_parameters
1210 (p_thread_id => 99
1211 ,p_benefit_action_id => l_benefit_action_id
1212 ,p_validate => p_validate
1213 ,p_business_group_id => p_business_group_id
1214 ,p_effective_date => l_effective_date
1215 ,p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id
1216 ,p_person_id => p_person_id
1217 ,p_person_selection_rule_id => p_person_selection_rule_id
1218 ,p_person_type_id => p_person_type_id
1219 ,p_ler_id => p_ler_id
1220 ,p_organization_id => p_organization_id
1221 ,p_benfts_grp_id => p_benfts_grp_id
1222 ,p_location_id => p_location_id
1223 ,p_legal_entity_id => p_legal_entity_id
1224 ,p_payroll_id => p_payroll_id
1225 ,p_audit_log => p_audit_log);
1226 --
1227 Ben_batch_utils.write(p_text =>
1228 '<< No Person got selected with above selection criteria >>' );
1229 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
1230 fnd_message.set_token('PROC',l_proc);
1231 raise l_silent_error;
1232 --
1233 End if;
1234 --
1235 l_actn := 'Calling do_multithread...';
1236 --
1237 do_multithread(errbuf => errbuf
1238 ,retcode => retcode
1239 ,p_validate => p_validate
1240 ,p_benefit_action_id => l_benefit_action_id
1241 ,p_thread_id => l_threads+1
1242 ,p_effective_date => p_effective_date
1243 ,p_business_group_id => p_business_group_id
1244 ,p_audit_log => p_audit_log);
1245 --
1246 l_actn := 'Calling ben_batch_utils.check_all_slaves_finished...';
1247 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1248 --
1249 l_actn := 'Calling ben_batch_utils.End_process...';
1250 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1251 ,p_person_selected => l_num_persons
1252 ,p_business_group_id => p_business_group_id);
1253 --
1254 l_actn := 'Calling submit_all_reports...';
1255 submit_all_reports(p_audit_log => p_audit_log);
1256 --
1257 hr_utility.set_location ('Leaving '||l_proc,70);
1258 --
1259 Exception
1260 --
1261 when l_silent_error then
1262 ben_batch_utils.write(p_text => fnd_message.get);
1263 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1264 If (l_num_ranges > 0) then
1265 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1266 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1267 ,p_person_selected => l_num_persons
1268 ,p_business_group_id => p_business_group_id);
1269 submit_all_reports(p_audit_log => p_audit_log);
1270 End if;
1271 --
1272 when others then
1273 ben_batch_utils.rpt_error(p_proc => l_proc
1274 ,p_last_actn => l_actn
1275 ,p_rpt_flag => TRUE );
1276 ben_batch_utils.write(p_text => fnd_message.get);
1277 ben_batch_utils.write(p_text => sqlerrm);
1278 ben_batch_utils.write(p_text => 'Big Error Occured');
1279 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1280 If (l_num_ranges > 0) then
1281 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1282 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1283 ,p_person_selected => l_num_persons
1284 ,p_business_group_id => p_business_group_id);
1285 submit_all_reports(p_audit_log => p_audit_log);
1286 End if;
1287 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1288 fnd_message.set_token('PROCEDURE', l_proc);
1289 fnd_message.set_token('STEP', l_actn );
1290 fnd_message.raise_error;
1291 End process;
1292 --
1293 -- ============================================================================
1294 -- << Procedure: Default_comp_obj>>
1295 -- ============================================================================
1296 Procedure Default_Comp_obj
1297 (p_validate in Boolean default FALSE
1298 ,p_per_in_ler_id in Number
1299 ,p_person_id in Number
1300 ,p_business_group_id in Number
1301 ,p_effective_date in Date
1302 ,p_pgm_id in Number
1303 ,p_pl_nip_id in Number
1304 ,p_susp_flag out nocopy Boolean
1305 ,p_batch_flag in Boolean default FALSE
1306 ,p_cls_enrt_flag in Boolean default TRUE
1307 ,p_called_frm_ss in Boolean default FALSE
1308 ) is
1309 -- in_pndg_wkflow_flag added to block electable choice in pending workflow not to be assigned
1310 Cursor c_dflt_mn_epe is
1311 Select epe.ELIG_PER_ELCTBL_CHC_ID
1312 ,epe.PGM_ID
1313 ,epe.PL_ID
1314 ,epe.OIPL_ID
1315 ,epe.DPNT_CVG_STRT_DT_CD
1316 ,epe.DPNT_CVG_STRT_DT_RL
1317 ,epe.ENRT_CVG_STRT_DT
1318 ,epe.CRYFWD_ELIG_DPNT_CD
1319 ,epe.CRNTLY_ENRD_FLAG
1320 ,epe.DFLT_FLAG
1321 ,epe.ELCTBL_FLAG
1322 ,epe.MNDTRY_FLAG
1323 ,epe.AUTO_ENRT_FLAG
1324 ,epe.PRTT_ENRT_RSLT_ID
1325 ,epe.BUSINESS_GROUP_ID
1326 ,'DEF' actn_cd
1327 ,'N' Suspended
1328 ,epe.in_pndg_wkflow_flag
1329 From ben_elig_per_elctbl_chc epe
1330 Where epe.Auto_enrt_flag = 'N'
1331 and epe.per_in_ler_id = p_per_in_ler_id
1332 and epe.Business_group_id = p_business_group_id
1333 and (epe.elctbl_flag = 'Y' or epe.mndtry_flag = 'Y')
1334 and (p_pgm_id is NULL or epe.pgm_id = p_pgm_id)
1335 and (p_pl_nip_id is null
1336 or (p_pl_nip_id = epe.pl_id and epe.pgm_id is NULL) )
1337 and (epe.dflt_flag = 'Y' or epe.crntly_enrd_flag = 'Y')
1338 and not exists (select null from ben_prtt_enrt_rslt_f pen
1339 where pen.per_in_ler_id = epe.per_in_ler_id
1340 -- Bug 6319484 Instead of checking for same pen_id,
1341 -- check if not already enrolled in same plan
1342 and nvl(pen.pgm_id,hr_api.g_number) = nvl(epe.pgm_id,hr_api.g_number)
1343 and pen.pl_id = epe.pl_id
1344 -- and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
1345 and pen.prtt_enrt_rslt_stat_cd IS NULL
1346 and pen.effective_end_date = hr_api.g_eot
1347 and pen.enrt_cvg_thru_dt = hr_api.g_eot )
1348 order by epe.pgm_id, epe.pl_id;
1349
1350 Cursor c_dflt_mn_epe_mndtry (c_pgm_id number, c_pl_id number) is
1351 Select epe.ELIG_PER_ELCTBL_CHC_ID
1352 ,epe.PGM_ID
1353 ,epe.PL_ID
1354 ,epe.OIPL_ID
1355 ,epe.DPNT_CVG_STRT_DT_CD
1356 ,epe.DPNT_CVG_STRT_DT_RL
1357 ,epe.ENRT_CVG_STRT_DT
1358 ,epe.CRYFWD_ELIG_DPNT_CD
1359 ,epe.CRNTLY_ENRD_FLAG
1360 ,epe.DFLT_FLAG
1361 ,epe.ELCTBL_FLAG
1362 ,epe.MNDTRY_FLAG
1363 ,epe.AUTO_ENRT_FLAG
1364 ,epe.PRTT_ENRT_RSLT_ID
1365 ,epe.BUSINESS_GROUP_ID
1366 ,'DEF' actn_cd
1367 ,'N' Suspended
1368 ,epe.in_pndg_wkflow_flag
1369 From ben_elig_per_elctbl_chc epe
1370 Where epe.dflt_flag = 'N'
1371 and epe.crntly_enrd_flag = 'N'
1372 and epe.mndtry_flag = 'Y'
1373 and epe.Auto_enrt_flag = 'N'
1374 and epe.per_in_ler_id = p_per_in_ler_id
1375 and epe.Business_group_id = p_business_group_id
1376 and nvl(epe.pgm_id,hr_api.g_number) = nvl(c_pgm_id, hr_api.g_number)
1377 and epe.pl_id = c_pl_id
1378 and comp_lvl_cd = 'OIPL'; -- Bug 4951065
1379 --
1380 Cursor c_pen (lc_prtt_enrt_rslt_id number) is
1381 Select prtt_enrt_rslt_id
1382 ,effective_start_date
1383 ,effective_end_date
1384 ,object_version_number
1385 ,bnft_amt
1386 ,uom
1387 ,enrt_mthd_cd
1388 ,business_group_id
1389 ,enrt_cvg_strt_dt
1390 ,enrt_cvg_thru_dt
1391 ,ERLST_DEENRT_DT
1392 ,enrt_ovrid_thru_dt
1393 ,enrt_ovridn_flag
1394 From ben_prtt_enrt_rslt_f
1395 Where prtt_enrt_rslt_id = lc_prtt_enrt_rslt_id
1396 and p_effective_date between
1397 effective_start_date and effective_end_date
1398 and prtt_enrt_rslt_stat_cd IS NULL
1399 and business_group_id = p_business_group_id
1400 ;
1401 l_pen c_pen%ROWTYPE;
1402 --
1403 Cursor c_rt (v_elig_per_elctbl_chc_id number) is
1404 Select ecr.enrt_rt_id
1405 ,nvl(ecr.val,ecr.dflt_val) default_val
1406 ,ecr.ANN_DFLT_VAL
1407 From ben_enrt_rt ecr
1408 Where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
1409 and ecr.business_group_id = p_business_group_id
1410 and ecr.entr_val_at_enrt_flag = 'Y'
1411 -- and ecr.prtt_rt_val_id is null -- ikasire bug 1834655
1412 Union
1413 Select ecr.enrt_rt_id
1414 ,nvl(ecr.val,ecr.dflt_val) default_val
1415 ,ecr.ANN_DFLT_VAL
1416 From ben_enrt_rt ecr
1417 ,ben_enrt_bnft enb
1418 Where enb.enrt_bnft_id = ecr.enrt_bnft_id
1419 and ecr.business_group_id = p_business_group_id
1420 and enb.business_group_id = p_business_group_id
1421 and enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
1422 and ecr.entr_val_at_enrt_flag = 'Y'
1423 -- and ecr.prtt_rt_val_id is null -- ikasire bug 1834655
1424 ;
1425 --
1426 Cursor c_bnft (l_elig_per_elctbl_chc_id number) is
1427 Select enrt_bnft_id, val, dflt_val, entr_val_at_enrt_flag,cvg_mlt_cd
1428 From ben_enrt_bnft
1429 Where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
1430 and dflt_flag = 'Y'
1431 ;
1432 Type enrt_id_table is table of c_rt%rowtype index by binary_integer;
1433 Type epe_table is table of c_dflt_mn_epe%rowtype index by binary_integer;
1434 --
1435 -- Local Variables
1436 --
1437 l_proc varchar2(80) := g_package || '.Default_comp_obj';
1438 l_output_string varchar2(80);
1439 l_validate boolean;
1440 l_actn varchar2(80);
1441 l_rt enrt_id_table;
1442 l_tot_rt number(5) := 0;
1443 l_bnft_amt ben_enrt_bnft.val%type;
1444 l_dflt_bnft_amt ben_enrt_bnft.val%type;
1445 l_entr_flag varchar2(1);
1446 l_bnft_id ben_enrt_bnft.enrt_bnft_id%type;
1447 l_suspend_flag varchar2(30);
1448 l_prtt_enrt_interim_id number(15);
1449 l_datetrack_mode varchar2(30);
1450 l_effective_start_date date;
1451 l_effective_end_date date;
1452 l_dump_num number(15);
1453 l_dump_boolean boolean;
1454 l_epe epe_table;
1455 l_epe_cnt binary_integer := 0;
1456 l_prev_pgm_id number := -999999;
1457 l_prev_pl_id number := -999999;
1458 l_actn_cd varchar2(30);
1459 l_cvg_mlt_cd varchar2(30) := null ;
1460 l_cryfwd_elig_dpnt_cd varchar2(30);
1461 l_prev_rslt_id_at number := 0 ;
1462 l_prev_prtt_enrt_rslt_id number ;
1463 l_not_ovridn boolean := true ;
1464 l_global_pil_rec ben_global_enrt.g_global_pil_rec_type; -- 4684991
1465
1466 Begin
1467 hr_utility.set_location ('Entering '|| l_proc , 5);
1468
1469 l_actn := 'Openning c_dflt_mn_epe cursor...';
1470 hr_utility.set_location (l_actn , 10);
1471 p_susp_flag := FALSE;
1472
1473 -- Bug - 4684991 - Fetch pil details
1474 ben_global_enrt.get_pil
1475 (p_per_in_ler_id => p_per_in_ler_id
1476 ,p_global_pil_rec => l_global_pil_rec);
1477 --
1478 --
1479 -- Retreive Records from Elig_per_elctbl_chc Table.
1480 --
1481 --
1482
1483 For l_rec in c_dflt_mn_epe loop
1484 --hr_utility.set_location (l_actn , 11);
1485 If (l_prev_pgm_id = -999999) then
1486 NULL;
1487 Elsif (l_prev_pgm_id <> l_rec.pgm_id) then
1488 For l_rec1 in c_dflt_mn_epe_mndtry(c_pgm_id => l_prev_pgm_id
1489 ,c_pl_id => l_prev_pl_id ) loop
1490 l_epe_cnt := l_epe_cnt + 1;
1491 l_epe(l_epe_cnt) := l_rec1;
1492 --hr_utility.set_location (l_actn||' First ', 12 );
1493 End loop;
1494 Elsif (l_prev_pl_id <> l_rec.pl_id) then
1495 For l_rec1 in c_dflt_mn_epe_mndtry(c_pgm_id => l_prev_pgm_id
1496 ,c_pl_id => l_prev_pl_id ) loop
1497 l_epe_cnt := l_epe_cnt + 1;
1498 l_epe(l_epe_cnt) := l_rec1;
1499 --hr_utility.set_location (l_actn||' Second ', 12 );
1500 End loop;
1501 End if;
1502 --hr_utility.set_location (l_actn||'Outside ', 13);
1503 l_epe_cnt := l_epe_cnt + 1;
1504 l_epe(l_epe_cnt) := l_rec;
1505 l_prev_pgm_id := l_rec.pgm_id;
1506 l_prev_pl_id := l_rec.pl_id;
1507 End loop;
1508 --
1509 --hr_utility.set_location ('Last loop '||l_actn , 15);
1510 -- Last loop
1511 --
1512 If l_epe_cnt > 0 then
1513 For l_rec1 in c_dflt_mn_epe_mndtry(c_pgm_id => l_prev_pgm_id
1514 ,c_pl_id => l_prev_pl_id ) loop
1515 l_epe_cnt := l_epe_cnt + 1;
1516 l_epe(l_epe_cnt) := l_rec1;
1517 --hr_utility.set_location ('l_rec1 ' , 16);
1518 End loop;
1519 End if;
1520 --
1521 --hr_utility.set_location ('Before '||l_actn , 17);
1522 For i in 1..l_epe_cnt Loop
1523 --
1524 l_not_ovridn := true ;
1525 --
1526 If (l_epe(i).prtt_enrt_rslt_id is not NULL) then
1527 l_actn := 'Getting enrollment data from c_pen cursor...';
1528 hr_utility.set_location (l_actn , 18);
1529 open c_pen(l_epe(i).prtt_enrt_rslt_id);
1530 fetch c_pen into l_pen;
1531 If (c_pen%notfound) then
1532 Close c_pen;
1533 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FND');
1534 fnd_message.set_token('PROC',l_proc);
1535 fnd_message.set_token('ID',to_char(l_epe(i).prtt_enrt_rslt_id));
1536 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1537 fnd_message.set_token('LER_ID',null);
1538 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
1539 fnd_message.raise_error;
1540 End if;
1541 close c_pen;
1542 --
1543 hr_utility.set_location (' lf_evt_ocrd_dt '|| to_char(l_global_pil_rec.lf_evt_ocrd_dt) , 5);
1544 --
1545 -- 4684991 - Use lf_evt_ocrd_dt instead of p_effective_date
1546 if l_pen.enrt_ovridn_flag = 'Y'
1547 AND nvl(l_pen.enrt_ovrid_thru_dt,hr_api.g_eot) >
1548 NVL(l_global_pil_rec.lf_evt_ocrd_dt, p_effective_date) then
1549 --
1550 l_not_ovridn := false ;
1551 --
1552 end if ;
1553 --
1554 If (l_pen.effective_start_date = p_effective_date) then
1555 l_datetrack_mode := hr_api.g_correction;
1556 Else
1557 l_datetrack_mode := hr_api.g_update;
1558 End if;
1559 l_epe(i).actn_cd := 'UPD';
1560 Else
1561 l_datetrack_mode := hr_api.g_insert;
1562 l_epe(i).actn_cd := 'INS';
1563 End if;
1564
1565 If(nvl(l_epe(i).dflt_flag,'X') = 'Y'
1566 or nvl(l_epe(i).mndtry_flag, 'X') = 'Y') and
1567 nvl(l_epe(i).in_pndg_wkflow_flag,'N') <> 'Y' and
1568 l_not_ovridn then -- added flag condition so that if pending flag=Y
1569 -- election is not made
1570
1571 l_actn := 'Openning c_bnft cursor for benefit...';
1572 hr_utility.set_location(l_actn , 20);
1573 l_bnft_id:=null;
1574 l_bnft_amt:=null;
1575 l_dflt_bnft_amt:=null;
1576 l_entr_flag:=null;
1577 open c_bnft(l_epe(i).elig_per_elctbl_chc_id);
1578
1579 fetch c_bnft into l_bnft_id, l_bnft_amt, l_dflt_bnft_amt, l_entr_flag,l_cvg_mlt_cd ;
1580 --hr_utility.set_location(' l_bnft_amt '||l_bnft_amt ,21 );
1581 --hr_utility.set_location(' l_dflt_bnft_amt '||l_dflt_bnft_amt , 22);
1582 --hr_utility.set_location(' l_entr_flag '||l_entr_flag , 23);
1583 close c_bnft;
1584 -- if the benefit amount is entered at enrollment, use the default
1585 -- benefit value instead of the val field.
1586 if l_entr_flag = 'Y' or l_cvg_mlt_cd = 'SAAEAR' then
1587 l_bnft_amt := l_dflt_bnft_amt;
1588 end if;
1589
1590 l_actn := 'Initializing rate cache and load...';
1591
1592 For j in 1..10 loop
1593 l_rt(j).enrt_rt_id := NULL;
1594 l_rt(j).default_val := 0;
1595 l_rt(j).ann_dflt_val := 0;
1596 End loop;
1597 l_tot_rt := 0;
1598 For Crec in c_rt(l_epe(i).elig_per_elctbl_chc_id) loop
1599 l_tot_rt := l_tot_rt + 1;
1600 l_rt(l_tot_rt).enrt_rt_id := Crec.enrt_rt_id;
1601 l_rt(l_tot_rt).default_val := Crec.default_val;
1602 l_rt(l_tot_rt).ann_dflt_val := Crec.ann_dflt_val;
1603 --hr_utility.set_location('l_rt(l_tot_rt).ann_dflt_val '||l_rt(l_tot_rt).ann_dflt_val ,26);
1604 --hr_utility.set_location('Crec.dflt_val '||l_rt(l_tot_rt).dflt_val , 27);
1605 End loop;
1606 l_suspend_flag := 'N';
1607 If (nvl(l_epe(i).actn_cd,'XXX') = 'UPD') then
1608 If nvl(l_bnft_amt,0) = nvl(l_pen.bnft_amt,0) then
1609 l_epe(i).actn_cd := 'DEF';
1610 End if;
1611 End if;
1612 --
1613 l_actn := 'Calling ben_election_information.election_information...';
1614 --
1615 /*
1616 hr_utility.set_location(l_actn , 30);
1617 hr_utility.set_location(' l_bnft_id '||l_bnft_id ,30);
1618 hr_utility.set_location(' l_bnft_amt '||l_bnft_amt ,30);
1619 hr_utility.set_location(' p_enrt_rt_id1 '||l_rt(1).enrt_rt_id ,30);
1620 hr_utility.set_location(' l_rt(1).dflt_val '||l_rt(1).dflt_val ,31);
1621 hr_utility.set_location(' p_Ann_rt_val1 '|| l_rt(1).ann_dflt_val , 31);
1622 */
1623 --
1624 Ben_election_information.election_information
1625 (p_elig_per_elctbl_chc_id => l_epe(i).elig_per_elctbl_chc_id
1626 ,p_prtt_enrt_rslt_id => l_epe(i).prtt_enrt_rslt_id
1627 ,p_effective_date => p_effective_date
1628 ,p_enrt_mthd_cd => 'D'
1629 ,p_business_group_id => p_business_group_id
1630 ,p_enrt_bnft_id => l_bnft_id
1631 ,p_bnft_val => l_bnft_amt
1632 ,p_enrt_rt_id1 => l_rt(1).enrt_rt_id
1633 ,p_enrt_rt_id2 => l_rt(2).enrt_rt_id
1634 ,p_enrt_rt_id3 => l_rt(3).enrt_rt_id
1635 ,p_enrt_rt_id4 => l_rt(4).enrt_rt_id
1636 ,p_enrt_rt_id5 => l_rt(5).enrt_rt_id
1637 ,p_enrt_rt_id6 => l_rt(6).enrt_rt_id
1638 ,p_enrt_rt_id7 => l_rt(7).enrt_rt_id
1639 ,p_enrt_rt_id8 => l_rt(8).enrt_rt_id
1640 ,p_enrt_rt_id9 => l_rt(9).enrt_rt_id
1641 ,p_enrt_rt_id10 => l_rt(10).enrt_rt_id
1642 ,p_rt_val1 => l_rt(1).default_val
1643 ,p_rt_val2 => l_rt(2).default_val
1644 ,p_rt_val3 => l_rt(3).default_val
1645 ,p_rt_val4 => l_rt(4).default_val
1646 ,p_rt_val5 => l_rt(5).default_val
1647 ,p_rt_val6 => l_rt(6).default_val
1648 ,p_rt_val7 => l_rt(7).default_val
1649 ,p_rt_val8 => l_rt(8).default_val
1650 ,p_rt_val9 => l_rt(9).default_val
1651 ,p_rt_val10 => l_rt(10).default_val
1652 ,p_Ann_rt_val1 => l_rt(1).ann_dflt_val
1653 ,p_Ann_rt_val2 => l_rt(2).ann_dflt_val
1654 ,p_Ann_rt_val3 => l_rt(3).ann_dflt_val
1655 ,p_Ann_rt_val4 => l_rt(4).ann_dflt_val
1656 ,p_Ann_rt_val5 => l_rt(5).ann_dflt_val
1657 ,p_Ann_rt_val6 => l_rt(6).ann_dflt_val
1658 ,p_Ann_rt_val7 => l_rt(7).ann_dflt_val
1659 ,p_Ann_rt_val8 => l_rt(8).ann_dflt_val
1660 ,p_Ann_rt_val9 => l_rt(9).ann_dflt_val
1661 ,p_Ann_rt_val10 => l_rt(10).ann_dflt_val
1662 ,p_datetrack_mode => l_datetrack_mode
1663 ,p_suspend_flag => l_suspend_flag
1664 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
1665 ,P_PRTT_RT_VAL_ID1 => l_dump_num
1666 ,P_PRTT_RT_VAL_ID2 => l_dump_num
1667 ,P_PRTT_RT_VAL_ID3 => l_dump_num
1668 ,P_PRTT_RT_VAL_ID4 => l_dump_num
1669 ,P_PRTT_RT_VAL_ID5 => l_dump_num
1670 ,P_PRTT_RT_VAL_ID6 => l_dump_num
1671 ,P_PRTT_RT_VAL_ID7 => l_dump_num
1672 ,P_PRTT_RT_VAL_ID8 => l_dump_num
1673 ,P_PRTT_RT_VAL_ID9 => l_dump_num
1674 ,P_PRTT_RT_VAL_ID10 => l_dump_num
1675 ,P_OBJECT_VERSION_NUMBER => l_pen.object_version_number
1676 ,p_effective_start_date => l_effective_start_date
1677 ,p_effective_end_date => l_effective_end_date
1678 ,P_DPNT_ACTN_WARNING => l_dump_boolean
1679 ,P_BNF_ACTN_WARNING => l_dump_boolean
1680 ,P_CTFN_ACTN_WARNING => l_dump_boolean
1681 );
1682 -- after the enhncemnt # 2685018 cryfwd_elig_dpnt_cd value is concated with
1683 -- result id from where the dpnt carry forwarded , this will seprate the code from
1684 --- result id
1685
1686 l_prev_prtt_enrt_rslt_id := null; -- Reintializing the previous enrt result id
1687 l_cryfwd_elig_dpnt_cd := l_epe(i).cryfwd_elig_dpnt_cd ;
1688 l_prev_rslt_id_at := instr(l_cryfwd_elig_dpnt_cd, '^') ;
1689 --- if the result id concated with the code, then the caht '^' must be aprt of the
1690 --- the code
1691
1692 if l_prev_rslt_id_at > 0 then
1693 --- if the to_number errors , catch the exception
1694 Begin
1695 l_prev_prtt_enrt_rslt_id := to_number(substr(l_cryfwd_elig_dpnt_cd,l_prev_rslt_id_at+1) );
1696 Exception
1697 when value_error then
1698 l_prev_prtt_enrt_rslt_id := null;
1699 End ;
1700 l_cryfwd_elig_dpnt_cd := substr(l_cryfwd_elig_dpnt_cd,1,l_prev_rslt_id_at-1) ;
1701 end if ;
1702
1703
1704 if l_datetrack_mode = hr_api.g_insert and l_cryfwd_elig_dpnt_cd = 'CFRRWP' then
1705
1706 ben_automatic_enrollments.reinstate_dpnt
1707 (p_pgm_id => l_epe(i).pgm_id,
1708 p_pl_id => l_epe(i).pl_id,
1709 p_oipl_id => l_epe(i).oipl_id,
1710 p_business_group_id => p_business_group_id,
1711 p_person_id => p_person_id,
1712 p_per_in_ler_id => p_per_in_ler_id,
1713 p_elig_per_elctbl_chc_id => l_epe(i).elig_per_elctbl_chc_id,
1714 p_dpnt_cvg_strt_dt_cd => l_epe(i).dpnt_cvg_strt_dt_cd,
1715 p_dpnt_cvg_strt_dt_rl => l_epe(i).dpnt_cvg_strt_dt_rl,
1716 p_enrt_cvg_strt_dt => l_epe(i).enrt_cvg_strt_dt,
1717 p_effective_date => p_effective_date,
1718 p_prev_prtt_enrt_rslt_id => l_prev_prtt_enrt_rslt_id
1719 );
1720 end if;
1721 l_actn := 'Getting suspend status...';
1722 --
1723 l_epe(i).suspended := l_suspend_flag;
1724 If (l_suspend_flag = 'Y') then
1725 p_susp_flag := TRUE;
1726 End if;
1727 Elsif(nvl(l_epe(i).dflt_flag,'X') <> 'Y'
1728 and nvl(l_epe(i).crntly_enrd_flag, 'X') = 'Y'
1729 and p_effective_date >= nvl(l_pen.ERLST_DEENRT_DT, hr_api.g_sot)
1730 and l_epe(i).AUTO_ENRT_FLAG = 'N'
1731 ) then
1732 --
1733 -- The enrollment result is ended in the multi_rows_edit. This
1734 -- is information for the batch reports.
1735 --
1736 l_epe(i).actn_cd := 'DEL';
1737 --
1738 End if;
1739 If (p_batch_flag) then
1740 --
1741 l_actn := 'Calling Ben_batch_utils.cache_comp_obj...';
1742 Ben_batch_utils.cache_comp_obj
1743 (p_prtt_enrt_rslt_id => l_epe(i).prtt_enrt_rslt_id
1744 ,p_effective_date => p_effective_date
1745 ,p_actn_cd => l_epe(i).actn_cd
1746 ,p_suspended => l_epe(i).suspended);
1747 End if;
1748 End loop;
1749
1750 -- Bug 4421813 Call init so that the person details are available for later procedures
1751
1752 if l_epe_cnt = 0 and fnd_global.conc_request_id = -1 and p_called_frm_ss then
1753 --
1754 ben_env_object.init(p_business_group_id => p_business_group_id,
1755 p_effective_date => p_effective_date,
1756 p_thread_id => 1,
1757 p_chunk_size => 1,
1758 p_threads => 1,
1759 p_max_errors => 1,
1760 p_benefit_action_id => null);
1761 --
1762 end if;
1763
1764
1765 --
1766 ben_proc_common_enrt_rslt.set_elcn_made_or_asnd_dt(
1767 p_per_in_ler_id => p_per_in_ler_id
1768 ,p_pgm_id => p_pgm_id
1769 ,p_pl_id => p_pl_nip_id --null Bug 2961251 passed pl_id parameter instead of null
1770 ,p_enrt_mthd_cd => 'D' -- Explicit
1771 ,p_business_group_id => p_business_group_id
1772 ,p_effective_date => p_effective_date
1773 ,p_validate => FALSE
1774 );
1775
1776 -- Check data to make sure multi-rows adit passed. This section is used
1777 -- for the last program ID.
1778 --
1779
1780 -- if (not p_called_frm_ss) then
1781 l_actn := 'Calling Ben_PRTT_ENRT_RESULT_api.multi_rows_edit...';
1782 Ben_PRTT_ENRT_RESULT_api.multi_rows_edit
1783 (p_person_id => p_person_id
1784 ,p_effective_date => p_effective_date
1785 ,p_business_group_id => p_business_group_id
1786 ,p_pgm_id => p_pgm_id
1787 ,p_per_in_ler_id => p_per_in_ler_id
1788 ,p_called_frm_ss => p_called_frm_ss
1789 );
1790 -- End if;
1791 --
1792 -- Invoke post result process.
1793 --
1794 l_actn := 'Calling Ben_proc_common_enrt_rslt.process_post_results...';
1795 Ben_proc_common_enrt_rslt.process_post_results
1796 (p_person_id => p_person_id
1797 ,p_enrt_mthd_cd => 'D'
1798 ,p_effective_date => p_effective_date
1799 ,p_business_group_id => p_business_group_id
1800 ,p_validate => FALSE
1801 ,p_per_in_ler_id => p_per_in_ler_id
1802 ,p_called_frm_ss =>p_called_frm_ss
1803 );
1804 --
1805 -- Invoke process_post_enrollment.
1806 --
1807 l_actn := 'Calling Ben_proc_common_enrt_rslt.process_post_enrollment...';
1808 Ben_proc_common_enrt_rslt.process_post_enrollment
1809 (p_per_in_ler_id => p_per_in_ler_id
1810 ,p_pgm_id => p_pgm_id
1811 ,p_pl_id => p_pl_nip_id
1812 ,p_enrt_mthd_cd => 'D'
1813 ,p_proc_cd => 'DFLTENRT'
1814 ,p_person_id => p_person_id
1815 ,p_business_group_id => p_business_group_id
1816 ,p_effective_date => p_effective_date
1817 ,p_validate => FALSE
1818 ,p_cls_enrt_flag => p_cls_enrt_flag
1819 );
1820 hr_utility.set_location ('Leaving '|| l_proc,10);
1821 Exception
1822 when app_exception.application_exception then -- 6027345
1823 fnd_message.raise_error; -- 6027345
1824 When others then
1825 if p_batch_flag then
1826 --
1827 -- Update person action to errored as record has an error
1828 --
1829 -- ben_batch_utils.write(p_text => fnd_message.get);
1830 ben_batch_utils.write(p_text => sqlerrm);
1831 ben_batch_utils.rpt_error(p_proc => l_proc
1832 ,p_last_actn => l_actn
1833 ,p_rpt_flag => p_batch_flag
1834 );
1835 raise ben_batch_utils.g_record_error ;
1836 -- Added for Bug 2370264
1837 else
1838 hr_utility.set_location ('Error in Default_Comp_obj : '|| sqlerrm , 87);
1839 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
1840 fnd_message.set_token('2',substr(sqlerrm,1,500)); -- 4695708
1841 fnd_message.raise_error;
1842 end if;
1843 -- End 2370264
1844 End Default_Comp_obj;
1845 --
1846 -- ============================================================================
1847 -- << Procedure: process_default_enrt >>
1848 -- ============================================================================
1849 --
1850 Procedure Process_default_enrt
1851 (p_validate in varchar2 default 'N'
1852 ,p_person_id in number default null
1853 ,p_person_action_id in number default null
1854 ,p_object_version_number in out nocopy number
1855 ,p_business_group_id in number
1856 ,p_effective_date in date
1857 ,p_batch_flag in Boolean default FALSE
1858 ,p_audit_log in varchar2 default 'N'
1859 ) is
1860 --
1861 -- Local Cursor
1862 --
1863 -- See bug 1960 : this cursor is not retrieving any pil_popl's due to :
1864 -- and a.ELCNS_MADE_DT is not NULL
1865 -- so above part of where clause is chaged to
1866 -- and a.ELCNS_MADE_DT is NULL
1867 --
1868 Cursor C_pel is
1869 Select a.PER_IN_LER_ID
1870 ,a.PGM_ID
1871 ,a.PL_ID
1872 ,b.lf_evt_ocrd_dt
1873 ,b.ler_id
1874 ,a.dflt_enrt_dt
1875 ,a.enrt_perd_strt_dt
1876 ,a.enrt_perd_end_dt
1877 From Ben_pil_elctbl_chc_popl a
1878 ,ben_per_in_ler b
1879 ,ben_ler_f ler
1880 Where a.PIL_ELCTBL_POPL_STAT_CD = 'STRTD'
1881 --and a.business_group_id = b.business_group_id
1882 and a.business_group_id = p_business_group_id
1883 and a.per_in_ler_id = b.per_in_ler_id
1884 and a.dflt_enrt_dt <= p_effective_date -- 7166971
1885 and b.per_in_ler_stat_cd = 'STRTD'
1886 and a.ELCNS_MADE_DT is NULL
1887 and ler.ler_id = b.ler_id
1888 and ler.typ_cd not in ('SCHEDDU') -- bug5768880
1889 and p_effective_date between ler.effective_start_date
1890 and ler.effective_End_date
1891 and b.person_id = p_person_id ;
1892 --
1893 Cursor C_pil is
1894 Select b.PER_IN_LER_ID
1895 ,b.lf_evt_ocrd_dt
1896 ,b.ler_id
1897 From ben_per_in_ler b,
1898 ben_ler_f ler
1899 Where b.per_in_ler_stat_cd = 'STRTD'
1900 and b.person_id = p_person_id
1901 and b.ler_id = ler.ler_id
1902 and ler.typ_cd not in ('COMP','GSP')
1903 and p_effective_date between
1904 ler.effective_start_date and
1905 ler.effective_end_date
1906 ;
1907 --
1908 -- Local Variables
1909 --
1910 l_proc Varchar2(80) := g_package || '.process_default enrollment';
1911 l_output_string Varchar2(80);
1912 l_validate boolean;
1913 l_actn varchar2(80);
1914 l_bnft_amt ben_enrt_bnft.val%type;
1915 l_bnft_id ben_enrt_bnft.enrt_bnft_id%type;
1916 l_datetrack_mode varchar2(30);
1917 l_pel_cnt binary_integer := 0;
1918 l_susp_flag boolean;
1919 l_output varchar2(2000);
1920 l_per_in_ler_id number;
1921 l_lf_evt_ocrd_dt date;
1922 l_ler_id number;
1923 l_dflt_enrt_date date;
1924 --
1925 begin
1926 -- hr_utility.trace_on(NULL,'TRC');
1927 hr_utility.set_location ('Entering '|| l_proc,10);
1928 l_actn := 'Initializing...';
1929 Savepoint process_default_enrt_savepoint;
1930 --
1931 -- Cache person data and write personal data into cache.
1932 --
1933 l_actn := 'Calling ben_batch_utils.person_header...';
1934 ben_batch_utils.person_header
1935 (p_person_id => p_person_id
1936 ,p_business_group_id => p_business_group_id
1937 ,p_effective_date => p_effective_date
1938 );
1939 --
1940 l_actn := 'Calling ben_batch_utils.ini(COMP_OBJ)...';
1941 ben_batch_utils.ini('COMP_OBJ');
1942 For l_rec in c_pel loop
1943 l_pel_cnt := l_pel_cnt + 1;
1944 l_per_in_ler_id:=l_rec.per_in_ler_id;
1945 l_ler_id := l_rec.ler_id;
1946 l_lf_evt_ocrd_dt := l_rec.lf_evt_ocrd_dt;
1947 --
1948 l_actn := 'Calling Default_comp_obj(pgm:' || to_char(l_rec.pgm_id) ||
1949 ' Pl_no_Pgm:' || to_char(l_rec.pl_id) || ')';
1950 --
1951 --
1952 -- Bug 5407755
1953 -- Default enrollment date = nvl ( ( 'Defaults will be assigned on',
1954 -- OR 'Days after Enrollment Period to Apply Defaults'
1955 -- ),
1956 -- Enrollment Period End Date
1957 -- )
1958 --
1959 l_dflt_enrt_date := NVL (l_rec.dflt_enrt_dt, l_rec.enrt_perd_end_dt);
1960 --
1961 IF l_dflt_enrt_date IS NULL
1962 THEN
1963 l_dflt_enrt_date := p_effective_date;
1964 END IF;
1965 --
1966 hr_utility.set_location ('l_Dflt_enrt_Date = ' || l_dflt_enrt_date, 9999);
1967 --
1968 Default_comp_obj
1969 (p_validate => FALSE
1970 ,p_per_in_ler_id => l_rec.per_in_ler_id
1971 ,p_person_id => p_person_id
1972 ,p_business_group_id => p_business_group_id
1973 ,p_effective_date => l_Dflt_enrt_Date /* Bug 5158204 */
1974 ,p_pgm_id => l_rec.pgm_id
1975 ,p_pl_nip_id => l_rec.pl_id
1976 ,p_susp_flag => l_susp_flag
1977 ,p_batch_flag => TRUE
1978 ,p_cls_enrt_flag => FALSE
1979 );
1980 --
1981 End loop;
1982 --
1983 -- jcarpent
1984 -- Bug 1609055. If this is null then you get an error inserting
1985 -- the log rows. Instead just fetch the per_in_ler_info for the
1986 -- started event.
1987 -- Tilak
1988 -- now can be multiple per_in_ler_id is started status
1989 if l_ler_id is null then
1990 open c_pil;
1991 fetch c_pil into
1992 l_per_in_ler_id,
1993 l_lf_evt_ocrd_dt,
1994 l_ler_id;
1995 close c_pil;
1996 end if;
1997 --
1998 -- Close enrollment i.e. update the per_in_ler to processed.
1999 --
2000 if l_pel_cnt>0 then
2001 ben_close_enrollment.close_single_enrollment
2002 (p_per_in_ler_id => l_per_in_ler_id
2003 ,p_effective_date => p_effective_date
2004 ,p_business_group_id => p_business_group_id
2005 ,p_validate => false
2006 ,p_close_uneai_flag => 'Y'
2007 ,p_uneai_effective_date => p_effective_date
2008 );
2009 end if;
2010 --
2011 l_actn := 'Calling Ben_batch_utils.write_comp...';
2012 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
2013 ,p_effective_date => p_effective_date
2014 );
2015 If (p_validate = 'Y') then
2016 Rollback to process_default_enrt_savepoint;
2017 End if;
2018 --
2019 l_actn := 'Calling write_person_category...';
2020 write_person_category (p_audit_log => p_audit_log
2021 ,p_person_id => p_person_id
2022 ,p_business_group_id => p_business_group_id
2023 ,p_effective_date => p_effective_date
2024 );
2025 --
2026 If p_person_action_id is not null then
2027 --
2028 l_actn := 'Calling ben_person_actions_api.update_person_actions...';
2029 --
2030 update ben_person_actions
2031 set action_status_cd = 'P'
2032 where person_action_id = p_person_action_id;
2033 --
2034 End if;
2035 --
2036 g_rec.person_id := p_person_id;
2037 g_rec.ler_id := l_ler_id;
2038 g_rec.per_in_ler_id := l_per_in_ler_id;
2039 g_rec.lf_evt_ocrd_dt := l_lf_evt_ocrd_dt;
2040 g_rec.replcd_flag := 'N';
2041 g_rec.crtd_flag := 'N';
2042 g_rec.tmprl_flag := 'N';
2043 g_rec.dltd_flag := 'N';
2044 g_rec.open_and_clsd_flag := 'N';
2045 g_rec.not_crtd_flag := 'N';
2046 g_rec.clsd_flag := 'Y';
2047 g_rec.stl_actv_flag := 'N';
2048 g_rec.clpsd_flag := 'N';
2049 g_rec.clsn_flag := 'N';
2050 g_rec.no_effect_flag := 'N';
2051 g_rec.cvrge_rt_prem_flag := 'N';
2052 g_rec.business_group_id := p_business_group_id;
2053 g_rec.effective_date := p_effective_date;
2054 --
2055 benutils.write(p_rec => g_rec);
2056 --
2057 g_persons_procd := g_persons_procd + 1;
2058 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
2059 hr_utility.set_location ('Leaving '|| l_proc,10);
2060 Exception
2061 When others then
2062 rollback to process_default_enrt_savepoint;
2063 g_persons_errored := g_persons_errored + 1;
2064 ben_batch_utils.write_error_rec;
2065 ben_batch_utils.write(p_text => fnd_message.get);
2066 ben_batch_utils.write(p_text => sqlerrm);
2067 ben_batch_utils.rpt_error(p_proc => l_proc
2068 ,p_last_actn => l_actn
2069 ,p_rpt_flag => TRUE);
2070 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
2071 ,p_effective_date => p_effective_date
2072 );
2073 If p_person_action_id is not null then
2074 ben_person_actions_api.update_person_actions
2075 (p_person_action_id => p_person_action_id
2076 ,p_action_status_cd => 'E'
2077 ,p_object_version_number => p_object_version_number
2078 ,p_effective_date => p_effective_date
2079 );
2080 End if;
2081 write_person_category (p_audit_log => p_audit_log
2082 ,p_error => TRUE
2083 ,p_person_id => p_person_id
2084 ,p_business_group_id => p_business_group_id
2085 ,p_effective_date => p_effective_date
2086 );
2087 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
2088 raise ben_batch_utils.g_record_error;
2089 end process_default_enrt;
2090
2091 -- ============================================================================
2092 -- << Procedure: Default_comp_obj_w >>
2093 -- ============================================================================
2094 Procedure Default_Comp_obj_w
2095 (p_validate in varchar2 default 'TRUE'
2096 ,p_per_in_ler_id in Number
2097 ,p_person_id in Number
2098 ,p_business_group_id in Number
2099 ,p_effective_date in Date
2100 ,p_pgm_id in Number
2101 ,p_pl_nip_id in Number default null
2102 ,p_susp_flag out nocopy varchar2
2103 ,p_batch_flag in varchar2 default 'FALSE'
2104 ,p_cls_enrt_flag in varchar2 default 'FALSE'
2105 ) is
2106
2107 l_proc Varchar2(80) := g_package || '.Default_Comp_obj_w';
2108 l_suspend_flag boolean;
2109 l_validate boolean;
2110 l_batch_flag boolean;
2111 l_cls_enrt_flag boolean;
2112 l_commit number;
2113 begin
2114 --
2115 fnd_msg_pub.initialize;
2116 hr_utility.set_location ('Entering '|| l_proc,10);
2117
2118 if UPPER(p_validate) = 'TRUE' then
2119 l_validate := true;
2120 else
2121 l_validate := false;
2122 end if;
2123 --
2124 if UPPER(p_batch_flag) = 'FALSE' then
2125 l_batch_flag := false;
2126 else
2127 l_batch_flag := true;
2128 end if;
2129 --
2130 if UPPER(p_cls_enrt_flag) = 'FALSE' then
2131 l_cls_enrt_flag := false;
2132 else
2133 l_cls_enrt_flag := true;
2134 end if;
2135 --
2136
2137 -- Bug 3989075, Put row in fnd_sessions for SS processing
2138 dt_fndate.change_ses_date
2139 (p_ses_date => p_effective_date,
2140 p_commit => l_commit);
2141
2142 Default_Comp_obj
2143 (p_validate => l_validate
2144 ,p_per_in_ler_id => p_per_in_ler_id
2145 ,p_person_id => p_person_id
2146 ,p_business_group_id => p_business_group_id
2147 ,p_effective_date => p_effective_date
2148 ,p_pgm_id => p_pgm_id
2149 ,p_pl_nip_id => p_pl_nip_id
2150 ,p_susp_flag => l_suspend_flag
2151 ,p_batch_flag => l_batch_flag
2152 ,p_cls_enrt_flag => l_cls_enrt_flag
2153 ,p_called_frm_ss => TRUE
2154 );
2155 --
2156 if l_suspend_flag = true then
2157 p_susp_flag := 'TRUE';
2158 else
2159 p_susp_flag := 'FALSE';
2160 end if;
2161 hr_utility.set_location ('Leaving '|| l_proc,20);
2162
2163 exception
2164 --
2165 when app_exception.application_exception then --Bug 4387247
2166 hr_utility.set_location ('Application Error in Default_Comp_obj_w.', 88);
2167 fnd_msg_pub.add;
2168 when others then
2169 hr_utility.set_location ('Other Error in Default_Comp_obj_w : '|| sqlerrm , 89);
2170 --Bug 4387247
2171 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2172 fnd_message.set_token('2',substr(sqlerrm,1,200));
2173 fnd_msg_pub.add;
2174 end Default_Comp_obj_w;
2175 --
2176 end ben_manage_default_enrt; -- End of Package.