[Home] [Help]
PACKAGE BODY: APPS.BEN_MANAGE_DEFAULT_ENRT
Source
1 package body ben_manage_default_enrt as
2 /* $Header: beneadeb.pkb 120.25.12020000.3 2013/01/30 10:06:41 amnaraya 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 * 28-Jul-09 velvanop 115.99 Fidelity Enhancement Bug No: 8716679
183 * The enhancement request is to reinstate elections from an intervening event
184 * with a life event that is backed out and reprocessed. The objective is to allow
185 * customers to have the ability to determine whether elections made for
186 * intervening events should be brought forward for a backed out life events.
187 * 18-Jan-11 stee 115.98 Bug 10377891 - added enrt_mthd_cd check to
188 * c_dflt_mn_epe.
189 * 11-Jul-11 velvanop 115.99 Bug 12688164 - Multi row edit validation in default enrollment process should happen after
190 * the enrollments of backed out LE are reinstated. If no enrollments are
191 * reinstated multi row edit should be called after applying the defaults
192 * 30-Apr-12 amnaraya 120.17.12010000.10 Bug: Default elections has to be made on the Enrollment period start
193 * date and not on the effective date. Reference bug 7623319.
194 * 28-Jan-13 amnaraya 120.17.12010000.11 Enh 16043887: When the Beneficiaries are carry forwared and the
195 * Exclude Participant flag is set, show a note.
196 * -----------------------------------------------------------------------------------
197 */
198 --
199 -- Global cursor and variables declaration
200 --
201 g_package varchar2(80) := 'Ben_manage_default_Enrt';
202 g_max_errors_allowed number(9) := 200;
203 g_persons_errored number(9) := 0;
204 g_persons_procd number(9) := 0;
205 g_rec benutils.g_batch_ler_rec;
206 --
207 -- ===========================================================================
208 -- << Procedure: Write_person_category >>
209 -- ===========================================================================
210 --
211 Procedure write_person_category
212 (p_audit_log varchar2 default 'N'
213 ,p_error Boolean default FALSE
214 ,p_business_group_id number
215 ,P_person_id number
216 ,p_effective_date date
217 ) is
218 --
219 Cursor c1 (c_prtt_enrt_rslt_id number) is
220 Select ecd.dpnt_person_id, ecd.cvg_strt_dt, ecd.cvg_thru_dt
221 From ben_elig_cvrd_dpnt_f ecd,
222 ben_per_in_ler pil
223 Where ecd.prtt_enrt_rslt_id is not NULL
224 and ecd.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
225 and ecd.business_group_id = p_business_group_id
226 and p_effective_date between
227 ecd.effective_start_date and ecd.effective_end_date
228 and pil.per_in_ler_id=ecd.per_in_ler_id
229 --and pil.business_group_id=ecd.business_group_id
230 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
231
232
233
234 l_proc varchar2(80) := g_package||'.write_person_category';
235 l_actn varchar2(80);
236 l_cache ben_batch_utils.g_comp_obj_table := ben_batch_utils.g_cache_comp;
237 l_cache_cnt binary_integer := ben_batch_utils.g_cache_comp_cnt;
238 l_category varchar2(30):= 'DEFNOCHG';
239 l_detail varchar2(132) := 'Default election asigned -- no current elections changed' ;
240 l_OVN Number;
241 l_id Number;
242 l_OVN1 varchar2(240);
243 l_actn_cd varchar2(30);
244 Begin
245 hr_utility.set_location ('Entering '||l_proc,05);
246 l_actn := 'Starting...';
247 If (p_error) then
248 If (p_audit_log = 'Y') then
249 l_category := 'ERROR_C';
250 l_detail := 'Error occur while defaulting enrollment';
251 --
252 l_actn := 'Calling ben_batch_utils.write_rec (ERROR)...';
253 Ben_batch_utils.write_rec(p_typ_cd => l_category ,p_text => l_detail);
254 End if;
255 Else
256 For i in 1..l_cache_cnt Loop
257 If (l_cache(i).upd_flag or l_cache(i).ins_flag or l_cache(i).del_flag) then
258 l_category := 'DEFWCHG';
259 l_detail := 'Default elections assigned -- some or all current election changed';
260 exit;
261 End if;
262 End loop;
263 --
264 l_actn := 'Calling ben_batch_utils.write_rec (DEFAULT)...';
265 Ben_batch_utils.write_rec(p_typ_cd => l_category,p_text => l_detail);
266 End if;
267 If (p_audit_log = 'Y') then
268 For i in 1..l_cache_cnt Loop
269 If (l_cache(i).del_flag) then
270 l_actn_cd := 'DEL';
271 Elsif (l_cache(i).ins_flag) then
272 l_actn_cd := 'INS';
273 Elsif (l_cache(i).upd_flag) then
274 l_actn_cd := 'UPD';
275 Elsif (l_cache(i).def_flag) then
276 l_actn_cd := 'DEF';
277 End if;
278 --
279 l_actn := 'Calling ben_batch_rate_info_api.create_batch_rate_info...';
280 insert into ben_batch_rate_info
281 (batch_rt_id,
282 benefit_action_id,
283 person_id,
284 pgm_id,
285 pl_id,
286 oipl_id,
287 dflt_val,
288 val,
289 actn_cd,
290 dflt_flag,
291 business_group_id,
292 object_version_number)
293 values
294 (ben_batch_rate_info_s.nextval,
295 benutils.g_benefit_action_id,
296 p_person_id,
297 l_cache(i).pgm_id,
298 l_cache(i).pl_id,
299 l_cache(i).oipl_id,
300 l_cache(i).bnft_amt,
301 l_cache(i).prtt_enrt_rslt_id,
302 l_actn_cd,
303 'Y',
304 p_business_group_id,
305 1);
306 --
307 If (l_cache(i).prtt_enrt_rslt_id is not NULL) then
308 For l_rec in c1(l_cache(i).prtt_enrt_rslt_id) loop
309 --
310 l_actn := 'Calling ben_batch_dpnt_info_api.create_batch_dpnt_info...';
311 insert into ben_batch_dpnt_info
312 (batch_dpnt_id,
313 person_id,
314 benefit_action_id,
315 business_group_id,
316 pgm_id,
317 pl_id,
318 oipl_id,
319 enrt_cvg_strt_dt,
320 enrt_cvg_thru_dt,
321 actn_cd,
322 object_version_number,
323 dpnt_person_id)
324 values
325 (ben_batch_dpnt_info_s.nextval,
326 p_person_id,
327 benutils.g_benefit_action_id,
328 p_business_group_id,
329 l_cache(i).pgm_id,
330 l_cache(i).pl_id,
331 l_cache(i).oipl_id,
332 l_rec.cvg_strt_dt,
333 l_rec.cvg_thru_dt,
334 l_actn_cd,
335 1,
336 l_rec.dpnt_person_id);
337 --
338 End loop;
339 End if;
340 End loop;
341 End if;
342 hr_utility.set_location ('Leaving '||l_proc,10);
343 Exception
344 When others then
345 ben_batch_utils.rpt_error(p_proc => l_proc
346 ,p_last_actn => l_actn
347 ,p_rpt_flag => TRUE
348 );
349 raise;
350 End write_person_category;
351 --
352 -- ===========================================================================
353 -- << Procedure: ben_batch_utils.batch_report >>
354 -- ===========================================================================
355 --
356 Procedure Submit_all_reports
357 (p_rpt_flag in Boolean default FALSE
358 ,p_audit_log in varchar2 default 'N'
359 ) is
360 l_proc varchar2(80) := g_package||'.submit_all_reports';
361 l_actn varchar2(80);
362 l_request_id number;
363 Begin
364 hr_utility.set_location ('Entering '||l_proc,05);
365 If fnd_global.conc_request_id <> -1 then
366 l_actn := 'Calling ben_batch_utils.batch_report (BENDFAUD)...';
367 If (p_audit_log = 'Y') then
368 ben_batch_utils.batch_report
369 (p_concurrent_request_id => fnd_global.conc_request_id
370 ,p_program_name => 'BENDFAUD'
371 ,p_request_id => l_request_id
372 );
373 End if;
374 l_actn := 'Calling ben_batch_utils.batch_report (BENDFSUM)...';
375 ben_batch_utils.batch_report
376 (p_concurrent_request_id => fnd_global.conc_request_id
377 ,p_program_name => 'BENDFSUM'
378 ,p_request_id => l_request_id
379 );
380 l_actn := 'Calling ben_batch_utils.batch_report (BENERTYP)...';
381 ben_batch_utils.batch_report
382 (p_concurrent_request_id => fnd_global.conc_request_id
383 ,p_program_name => 'BENERTYP'
384 ,p_request_id => l_request_id
385 ,p_subtitle =>
386 'ERROR DETAIL BY ERROR TYPE'
387 );
388 l_actn := 'Calling ben_batch_utils.batch_report (BENERPER)...';
389 ben_batch_utils.batch_report
390 (p_concurrent_request_id => fnd_global.conc_request_id
391 ,p_program_name => 'BENERPER'
392 ,p_request_id => l_request_id
393 ,p_subtitle =>
394 'ERROR DETAIL BY PERSON'
395 );
396 End if;
397 hr_utility.set_location ('Leaving '||l_proc,10);
398 Exception
399 When others then
400 ben_batch_utils.rpt_error(p_proc => l_proc
401 ,p_last_actn => l_actn
402 ,p_rpt_flag => p_rpt_flag
403 );
404 raise;
405 End Submit_all_reports;
406 --
407 -- ============================================================================
408 -- << Procedure: Do_Multithread >>
409 -- Description:
410 -- this is a main procedure to invoke the Default enrollment process.
411 -- ============================================================================
412 procedure do_multithread
413 (errbuf out nocopy varchar2
414 ,retcode out nocopy number
415 ,p_validate in varchar2 default 'N'
416 ,p_benefit_action_id in number
417 ,p_thread_id in number
418 ,p_effective_date in varchar2
419 ,p_business_group_id in number
420 ,p_audit_log in varchar2 default 'N'
421 ) is
422 --
423 -- Local variable declaration
424 --
425 l_proc varchar2(80) := g_package||'.do_multithread';
426 l_person_id ben_person_actions.person_id%type;
427 l_person_action_id ben_person_actions.person_action_id%type;
428 l_object_version_number ben_person_actions.object_version_number%type;
429 l_ler_id ben_person_actions.ler_id%type;
430 l_range_id ben_batch_ranges.range_id%type;
431 l_record_number number := 0;
432 l_start_person_action_id number := 0;
433 l_end_person_action_id number := 0;
434 l_actn varchar2(80);
435 l_cnt number(5):= 0;
436 l_chunk_size number(15);
437 l_threads number(15);
438 l_effective_date date;
439 --
440 -- Cursors declaration
441 --
442 Cursor c_range_thread is
443 Select ran.range_id
444 ,ran.starting_person_action_id
445 ,ran.ending_person_action_id
446 From ben_batch_ranges ran
447 Where ran.range_status_cd = 'U'
448 And ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
449 And rownum < 2
450 For update of ran.range_status_cd
451 ;
452 Cursor c_person_thread is
453 Select ben.person_id
454 ,ben.person_action_id
455 ,ben.object_version_number
456 ,ben.ler_id
457 From ben_person_actions ben
458 Where ben.benefit_action_id = p_benefit_action_id
459 And ben.action_status_cd <> 'P'
460 And ben.person_action_id between
461 l_start_person_action_id and l_end_person_action_id
462 Order by ben.person_action_id
463 ;
464 Cursor c_parameter is
465 Select *
466 From ben_benefit_actions ben
467 Where ben.benefit_action_id = p_benefit_action_id
468 ;
469 l_parm c_parameter%rowtype;
470 l_commit number;
471 --
472 Begin
473 --
474 hr_utility.set_location ('Entering '||l_proc,05);
475 --
476 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
477 --
478 -- Put row in fnd_sessions
479 --
480 dt_fndate.change_ses_date
481 (p_ses_date => l_effective_date,
482 p_commit => l_commit);
483 --
484 l_actn := 'Calling benutils.get_parameter...';
485 benutils.get_parameter(p_business_group_id => p_business_group_Id
486 ,p_batch_exe_cd => 'BENEADEB'
487 ,p_threads => l_threads
488 ,p_chunk_size => l_chunk_size
489 ,p_max_errors => g_max_errors_allowed );
490 --
491 -- Set up benefits environment
492 --
493 ben_env_object.init(p_business_group_id => p_business_group_id,
494 p_effective_date => l_effective_date,
495 p_thread_id => p_thread_id,
496 p_chunk_size => l_chunk_size,
497 p_threads => l_threads,
498 p_max_errors => g_max_errors_allowed,
499 p_benefit_action_id => p_benefit_action_id);
500 --
501 l_actn := 'Calling ben_batch_utils.ini...';
502 ben_batch_utils.ini;
503 --
504 -- Copy benefit action id to global in benutils package
505 --
506 benutils.g_benefit_action_id := p_benefit_action_id;
507 benutils.g_thread_id := p_thread_id;
508 g_persons_errored := 0;
509 g_persons_procd := 0;
510 open c_parameter;
511 fetch c_parameter into l_parm;
512 close c_parameter;
513 --
514 l_actn := 'Calling ben_batch_utils.print_parameters...';
515 --
516 ben_batch_utils.print_parameters
517 (p_thread_id => p_thread_id
518 ,p_benefit_action_id => p_benefit_action_id
519 ,p_validate => p_validate
520 ,p_business_group_id => p_business_group_id
521 ,p_effective_date => l_effective_date
522 ,p_popl_enrt_typ_cycl_id => l_parm.popl_enrt_typ_cycl_id
523 ,p_person_id => l_parm.person_id
524 ,p_person_selection_rule_id => l_parm.person_selection_rl
525 ,p_person_type_id => l_parm.person_type_id
526 ,p_ler_id => l_parm.ler_id
527 ,p_organization_id => l_parm.organization_id
528 ,p_benfts_grp_id => l_parm.benfts_grp_id
529 ,p_location_id => l_parm.location_id
530 ,p_legal_entity_id => l_parm.legal_entity_id
531 ,p_payroll_id => l_parm.payroll_id
532 ,p_audit_log => l_parm.audit_log_flag
533 );
534 --
535 -- While loop to only try and fetch records while they exist
536 -- we always try and fetch the size of the chunk, if we get less
537 -- then we know that the process is finished so we end the while loop.
538 -- The process is as follows :
539 -- 1) Lock the rows that are not processed
540 -- 2) Grab as many rows as we can upto the chunk size
541 -- 3) Put each row into the person cache.
542 -- 4) Process the person cache
543 -- 5) Go to number 1 again.
544 --
545 Loop
546 l_actn := 'Opening c_range thread and fetch range...';
547 open c_range_thread;
548 fetch c_range_thread into l_range_id
549 ,l_start_person_action_id
550 ,l_end_person_action_id;
551 exit when c_range_thread%notfound;
552 close c_range_thread;
553 If(l_range_id is not NULL) then
554 --
555 l_actn := 'Updating ben_batch_ranges row...';
556 --
557 update ben_batch_ranges ran set ran.range_status_cd = 'P'
558 where ran.range_id = l_range_id;
559 commit;
560 End if;
561 --
562 -- Remove all records from cache
563 --
564 l_actn := 'Clearing g_cache_person_process cache...';
565 g_cache_person_process.delete;
566 open c_person_thread;
567 l_record_number := 0;
568 Loop
569 --
570 l_actn := 'Loading person data into g_cache_person_process cache...';
571 --
572 fetch c_person_thread
573 into g_cache_person_process(l_record_number+1).person_id
574 ,g_cache_person_process(l_record_number+1).person_action_id
575 ,g_cache_person_process(l_record_number+1).object_version_number
576 ,g_cache_person_process(l_record_number+1).ler_id;
577 exit when c_person_thread%notfound;
578 l_record_number := l_record_number + 1;
579 End loop;
580 close c_person_thread;
581 --
582 l_actn := 'Preparing to default each participant from cache...' ;
583 --
584 If l_record_number > 0 then
585 --
586 -- Process the rows from the person process cache
587 --
588 For l_cnt in 1..l_record_number loop
589 Begin
590 ben_manage_default_enrt.process_default_enrt
591 (p_validate => p_validate
592 ,p_person_id => g_cache_person_process(l_cnt).person_id
593 ,p_business_group_id => p_business_group_id
594 ,p_effective_date => l_effective_date
595 ,p_person_action_id => g_cache_person_process(l_cnt).person_action_id
596 ,p_object_version_number => g_cache_person_process(l_cnt).object_version_number
597 ,p_audit_log => p_audit_log
598 );
599 --
600 Exception
601 When others then
602 If (g_persons_errored > g_max_errors_allowed) then
603 fnd_message.raise_error;
604 End if;
605 End;
606 End loop;
607 Else
608 --
609 l_actn := 'Erroring out nocopy since not person is found in range...' ;
610 --
611 fnd_message.set_name('BEN','BEN_91709_PER_NOT_FND_IN_RNG');
612 fnd_message.set_token('PROCEDURE',l_proc);
613 fnd_message.raise_error;
614 End if;
615 commit;
616 End loop;
617 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
618 commit;
619 --
620 l_actn := 'Calling Log_beneadeb_statistics...';
621 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
622 ,p_num_pers_errored => g_persons_errored
623 );
624 hr_utility.set_location ('Leaving '||l_proc,70);
625 Exception
626 When others then
627 ben_batch_utils.rpt_error(p_proc => l_proc
628 ,p_last_actn => l_actn
629 ,p_rpt_flag => TRUE
630 );
631 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
632 ,p_num_pers_errored => g_persons_errored
633 );
634 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
635 commit;
636 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
637 fnd_message.set_token('PROCEDURE', l_proc);
638 fnd_message.set_token('STEP',l_actn );
639 fnd_message.raise_error;
640 --
641 end do_multithread;
642 --
643 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
644 -- << Procedure: Restart >>
645 -- *****************************************************************
646 --
647 procedure restart (errbuf out nocopy varchar2
648 ,retcode out nocopy number
649 ,p_benefit_action_id in number
650 ) is
651 --
652 -- Cursor Declaration
653 --
654 cursor c_parameters is
655 Select -- to_char(process_date,'YYYY/MM/DD HH24:MI:SS') process_date
656 fnd_date.date_to_canonical(process_date) process_date
657 ,validate_flag
658 ,person_id
659 ,person_type_id
660 ,pgm_id
661 ,business_group_id
662 ,pl_id
663 ,popl_enrt_typ_cycl_id
664 ,person_selection_rl
665 ,ler_id
666 ,organization_id
667 ,benfts_grp_id
668 ,location_id
669 ,legal_entity_id
670 ,payroll_id
671 ,debug_messages_flag
672 ,audit_log_flag
673 From ben_benefit_actions ben
674 Where ben.benefit_action_id = p_benefit_action_id;
675 --
676 -- Local Variable declaration.
677 --
678 l_proc varchar2(80) := g_package||'.restart';
679 l_parameters c_parameters%rowtype;
680 l_errbuf varchar2(80);
681 l_retcode number;
682 l_actn varchar2(80);
683 Begin
684 hr_utility.set_location ('Entering '||l_proc,10);
685 --
686 -- get the parameters for a previous run and do a restart
687 --
688 l_actn := 'Getting parameter data...';
689 open c_parameters;
690 fetch c_parameters into l_parameters;
691 If c_parameters%notfound then
692 ben_batch_utils.rpt_error(p_proc => l_proc
693 ,p_last_actn => l_actn
694 ,p_rpt_flag => TRUE
695 );
696 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
697 fnd_message.set_token('PROC',l_proc);
698 fnd_message.raise_error;
699 End if;
700 close c_parameters;
701 --
702 -- Call process procedure with parameters for restart
703 --
704 l_actn := 'Calling process...';
705 Process (errbuf => l_errbuf
706 ,retcode => l_retcode
707 ,p_benefit_action_id => p_benefit_action_id
708 ,p_effective_date => l_parameters.process_date
709 ,p_validate => l_parameters.validate_flag
710 ,p_person_id => l_parameters.person_id
711 ,p_person_type_id => l_parameters.person_type_id
712 ,p_business_group_id => l_parameters.business_group_id
713 ,p_popl_enrt_typ_cycl_id => l_parameters.popl_enrt_typ_cycl_id
714 ,p_ler_id => l_parameters.ler_id
715 ,p_organization_id => l_parameters.organization_id
716 ,p_benfts_grp_id => l_parameters.benfts_grp_id
717 ,p_location_id => l_parameters.location_id
718 ,p_legal_entity_id => l_parameters.legal_entity_id
719 ,p_payroll_id => l_parameters.payroll_id
720 ,p_debug_messages => l_parameters.debug_messages_flag
721 ,p_audit_log => l_parameters.audit_log_flag
722 );
723 hr_utility.set_location ('Leaving '||l_proc,70);
724 Exception
725 when others then
726 ben_batch_utils.rpt_error(p_proc => l_proc
727 ,p_last_actn => l_actn
728 ,p_rpt_flag => TRUE
729 );
730 raise;
731 end restart;
732 --
733 -- *************************************************************************
734 -- * << Procedure: Process >>
735 -- *************************************************************************
736 --
737 procedure process(errbuf out nocopy varchar2
738 ,retcode out nocopy number
739 ,p_benefit_action_id in number
740 ,p_effective_date in varchar2
741 ,p_validate in varchar2 default 'N'
742 ,p_person_id in number default null
743 ,p_person_type_id in number default null
744 ,p_business_group_id in number
745 ,p_popl_enrt_typ_cycl_id in number default null
746 ,p_person_selection_rule_id in number default null
747 ,p_ler_id in number default null
748 ,p_organization_id in number default null
749 ,p_benfts_grp_id in number default null
750 ,p_location_id in number default null
751 ,p_legal_entity_id in number default null
752 ,p_payroll_id in number default null
753 ,p_debug_messages in varchar2 default 'N'
754 ,p_audit_log in varchar2 default 'N') is
755 --
756 -- local variable declaration.
757 --
758 l_effective_date date;
759 l_request_id number;
760 l_proc varchar2(80) := g_package||'.process';
761 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
762 l_object_version_number ben_benefit_actions.object_version_number%type;
763 l_person_id_out per_people_f.person_id%type;
764 l_range_id ben_batch_ranges.range_id%type;
765 l_chunk_size number;
766 l_threads number;
767 l_start_person_action_id number := 0;
768 l_end_person_action_id number := 0;
769 l_rule_value varchar2(30);
770 l_skip boolean;
771 l_actn varchar2(80);
772 l_num_ranges number := 0;
773 l_num_rows number := 0;
774 l_num_persons number := 0;
775 l_commit number;
776 --
777 -- Cursors declaration.
778 --
779 --
780 -- Bug fix 2828045 - uncommented the BG id check =>
781 -- pil.business_group_id = p_business_group_id, and added
782 -- check for filtering out PILs of type COMP and ABS, since these
783 -- PILs should not be considered for Default Enrt Process.
784 -- Also, fixed the p_ler_id comparison clause along with this.
785 -- The p_ler_id param is compared with pil.per_in_ler_id instead
786 -- of pil.ler_id
787 --
788 cursor c_person is
789 select pil.person_id
790 from ben_per_in_ler pil
791 where
792 pil.business_group_id = p_business_group_id -- 2828045
793 and pil.per_in_ler_id in
794 (select pel.per_in_ler_id
795 from ben_pil_elctbl_chc_popl pel
796 where pel.business_group_id = pil.business_group_id
797 and pel.per_in_ler_id = pil.per_in_ler_id
798 and pel.dflt_enrt_dt <= l_effective_date
799 and pel.dflt_asnd_dt is NULL
800 and pel.ELCNS_MADE_DT is NULL)
801 and pil.per_in_ler_stat_cd = 'STRTD'
802 and (p_person_id is NULL or pil.person_id = p_person_id)
803 -- and (p_ler_id is NULL or pil.per_in_ler_id = p_ler_id) -- 2828045
804 and (p_ler_id is NULL or pil.ler_id = p_ler_id)
805 and exists (select null -- 2828045
806 from ben_ler_f ler
807 where ler.ler_id = pil.ler_id
808 and ler.typ_cd not in ('COMP','ABS','GSP')
809 and l_effective_date
810 between ler.effective_start_date
811 and ler.effective_end_date
812 )
813 and (p_person_type_id is null
814 or exists (select null
815 from per_person_type_usages ptu
816 where ptu.person_id = pil.person_id
817 and ptu.person_type_id = p_person_type_id))
818 -- Bug : 2170794 Commented the code below and added the new clauses .
819 /* and ((p_location_id is null
820 and p_organization_id is null
821 and p_legal_entity_id is null
822 and p_payroll_id is null)
823 or exists (select null
824 from per_all_assignments_f asg
825 where nvl(asg.location_id,hr_api.g_number) =
826 nvl(p_location_id,hr_api.g_number)
827 and nvl(asg.organization_id,hr_api.g_number) =
828 nvl(p_organization_id,hr_api.g_number)
829 and nvl(asg.soft_coding_keyflex_id,hr_api.g_number) =
830 nvl(p_legal_entity_id,hr_api.g_number)
831 and nvl(asg.payroll_id, hr_api.g_number)=
832 nvl(p_payroll_id,hr_api.g_number)
833 and asg.person_id = pil.person_id
834 and asg.primary_flag = 'Y'
835 and asg.business_group_id = pil.business_group_id
836 and l_effective_date
837 between asg.effective_start_date
838 and asg.effective_end_date))
839 */
840 and ((p_location_id is null )
841 or exists (select null
842 from per_all_assignments_f asg
843 where asg.location_id = p_location_id
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_organization_id is null )
851 or exists (select null
852 from hr_organization_units org,
853 per_all_assignments_f asg
854 where asg.organization_id = org.organization_id
855 and org.organization_id = p_organization_id
856 and l_effective_date
857 between org.date_from and nvl(org.date_to,l_effective_date )
858 and asg.person_id = pil.person_id
859 and asg.assignment_type <> 'C'
860 and asg.primary_flag = 'Y'
861 and asg.business_group_id = pil.business_group_id
862 and l_effective_date
863 between asg.effective_start_date and asg.effective_end_date))
864 and (( p_legal_entity_id is null )
865 or exists (select null
866 from hr_soft_coding_keyflex hsc,
867 per_all_assignments_f asg
868 where asg.person_id = pil.person_id
869 and asg.assignment_type <> 'C'
870 and asg.primary_flag = 'Y'
871 and asg.business_group_id = pil.business_group_id
872 and l_effective_date
873 between asg.effective_start_date and asg.effective_end_date
874 and asg.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
875 and hsc.segment1 = to_char(p_legal_entity_id)))
876 and (( p_payroll_id is null)
877 or exists (select null
878 from pay_payrolls_f pay,
879 per_all_assignments_f asg
880 where asg.person_id = pil.person_id
881 and asg.assignment_type <> 'C'
882 and asg.primary_flag = 'Y'
883 and asg.business_group_id = pil.business_group_id
884 and l_effective_date
885 between asg.effective_start_date and asg.effective_end_date
886 and pay.payroll_id = p_payroll_id
887 and pay.payroll_id = asg.payroll_id
888 and l_effective_date
889 between pay.effective_start_date and pay.effective_end_date ))
890 -- Bug : 2170794
891 and (p_benfts_grp_id is null
892 or exists (select null
893 from ben_benfts_grp bng,
894 per_all_people_f ppf
895 where bng.benfts_grp_id = p_benfts_grp_id
896 And bng.business_group_id = pil.business_group_id
897 And ppf.person_id = pil.person_id
898 And ppf.benefit_group_id = bng.benfts_grp_id
899 And l_effective_date
900 between ppf.effective_start_date
901 and ppf.effective_end_date));
902 --
903 l_person_action_id l_number_type := l_number_type();
904 l_person_id l_number_type := l_number_type();
905 l_silent_error exception;
906 --
907 Begin
908 --
909 hr_utility.set_location ('Entering '||l_proc,10);
910 -- Bug 5857493
911 if p_audit_log ='Y' then
912 ben_batch_utils.g_audit_flag := true;
913 else
914 ben_batch_utils.g_audit_flag := false;
915 end if;
916 --
917 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
918 --
919 -- Put row in fnd_sessions
920 --
921 dt_fndate.change_ses_date
922 (p_ses_date => l_effective_date,
923 p_commit => l_commit);
924 --
925 l_actn := 'Initialize the ben_batch_utils cache...';
926 --
927 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
928 --
929 -- Get chunk_size and Thread values for multi-thread process, and check to
930 --
931 l_actn := 'Calling benutils.get_parameter...';
932 benutils.get_parameter(p_business_group_id => p_business_group_Id
933 ,p_batch_exe_cd => 'BENEADEB'
934 ,p_threads => l_threads
935 ,p_chunk_size => l_chunk_size
936 ,p_max_errors => g_max_errors_allowed);
937 --
938 benutils.g_benefit_action_id := p_benefit_action_id;
939 benutils.g_thread_id := 99;
940 --
941 -- Create benefit actions parameters in the benefit action table.
942 -- Do not create if a benefit action already exists, in other words
943 -- we are doing a restart.
944 --
945 if p_benefit_action_id is null then
946 --
947 ben_benefit_actions_api.create_perf_benefit_actions
948 (p_validate => false
949 ,p_benefit_action_id => l_benefit_action_id
950 ,p_process_date => l_effective_date
951 ,p_mode_cd => 'S'
952 ,p_derivable_factors_flag => 'N'
953 ,p_validate_flag => p_validate
954 ,p_person_id => p_person_id
955 ,p_person_type_id => p_person_type_id
956 ,p_pgm_id => NULL
957 ,p_business_group_id => p_business_group_id
958 ,p_pl_id => NULL
959 ,p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id
960 ,p_no_programs_flag => 'N'
961 ,p_no_plans_flag => 'N'
962 ,p_comp_selection_rl => NULL
963 ,p_person_selection_rl => p_person_selection_rule_id
964 ,p_ler_id => p_ler_id
965 ,p_organization_id => p_organization_id
966 ,p_benfts_grp_id => p_benfts_grp_id
967 ,p_location_id => p_location_id
968 ,p_pstl_zip_rng_id => NULL
969 ,p_rptg_grp_id => NULL
970 ,p_pl_typ_id => NULL
971 ,p_opt_id => NULL
972 ,p_eligy_prfl_id => NULL
973 ,p_vrbl_rt_prfl_id => NULL
974 ,p_legal_entity_id => p_legal_entity_id
975 ,p_payroll_id => p_payroll_id
976 ,p_audit_log_flag => p_audit_log
977 ,p_debug_messages_flag => p_debug_messages
978 ,p_object_version_number => l_object_version_number
979 ,p_effective_date => l_effective_date
980 ,p_request_id => fnd_global.conc_request_id
981 ,p_program_application_id => fnd_global.prog_appl_id
982 ,p_program_id => fnd_global.conc_program_id
983 ,p_program_update_date => sysdate);
984 --
985 benutils.g_benefit_action_id := l_benefit_action_id;
986 --
987 -- Now lets create person actions for all the people we are going to
988 -- process in the BENEADEB run.
989 --
990 open c_person;
991 --
992 l_actn := 'Loading person_actions table..';
993 --
994 loop
995 --
996 fetch c_person into l_person_id_out;
997 exit when c_person%notfound;
998 --
999 l_skip := FALSE;
1000 --
1001 if p_person_selection_rule_id is not null then
1002 --
1003 l_actn := 'Calling Ben_batch_utils.person_selection_rule...';
1004 l_rule_value :=
1005 ben_batch_utils.person_selection_rule
1006 (p_person_id => l_person_id_out
1007 ,p_business_group_id => p_business_group_id
1008 ,p_person_selection_rule_id=> p_person_selection_rule_id
1009 ,p_effective_date => l_effective_date);
1010 --
1011 if l_rule_value = 'N' then
1012 --
1013 l_skip := TRUE;
1014 --
1015 elsif l_rule_value = 'Y' then -- Bug 5662220
1016 --
1017 l_skip := FALSE;
1018 --
1019 else
1020 --
1021 l_skip := TRUE; -- Bug 5662220
1022 --
1023 end if;
1024 --
1025 end if;
1026 --
1027 -- Store person_id into person actions table.
1028 --
1029 if not l_skip then
1030 --
1031 l_num_persons := l_num_persons + 1;
1032 l_num_rows := l_num_rows + 1;
1033 --
1034 -- Extend person_action_id type
1035 --
1036 l_person_action_id.extend(1);
1037 --
1038 -- Get Primary Key value
1039 --
1040 select ben_person_actions_s.nextval
1041 into l_person_action_id(l_num_rows)
1042 from sys.dual;
1043 --
1044 -- Extend person_id type
1045 --
1046 l_person_id.extend(1);
1047 l_person_id(l_num_rows) := l_person_id_out;
1048 --
1049 if l_num_rows = l_chunk_size then
1050 --
1051 -- Bulk bind in person actions
1052 --
1053 forall l_count in 1..l_num_rows
1054 --
1055 insert into ben_person_actions
1056 (person_action_id,
1057 person_id,
1058 ler_id,
1059 benefit_action_id,
1060 action_status_cd,
1061 object_version_number)
1062 values
1063 (l_person_action_id(l_count),
1064 l_person_id(l_count),
1065 0,
1066 l_benefit_action_id,
1067 'U',
1068 1);
1069 --
1070 l_num_ranges := l_num_ranges + 1;
1071 --
1072 -- Select next sequence number for the range
1073 --
1074 select ben_batch_ranges_s.nextval
1075 into l_range_id
1076 from sys.dual;
1077 --
1078 -- Calculate start and end points of the range
1079 --
1080 l_start_person_action_id := l_person_action_id(1);
1081 l_end_person_action_id := l_person_action_id(l_num_rows);
1082 --
1083 insert into ben_batch_ranges
1084 (range_id,
1085 benefit_action_id,
1086 range_status_cd,
1087 starting_person_action_id,
1088 ending_person_action_id,
1089 object_version_number)
1090 values
1091 (l_range_id,
1092 l_benefit_action_id,
1093 'U',
1094 l_start_person_action_id,
1095 l_end_person_action_id,
1096 1);
1097 --
1098 l_num_rows := 0;
1099 --
1100 -- Dispose of varray
1101 --
1102 l_person_action_id.delete;
1103 l_person_id.delete;
1104 --
1105 commit;
1106 --
1107 end if;
1108 --
1109 end if;
1110 --
1111 end loop;
1112 --
1113 close c_person;
1114 --
1115 if l_num_rows <> 0 then
1116 --
1117 forall l_count in 1..l_num_rows
1118 --
1119 -- Bulk bind in person actions
1120 --
1121 insert into ben_person_actions
1122 (person_action_id,
1123 person_id,
1124 ler_id,
1125 benefit_action_id,
1126 action_status_cd,
1127 object_version_number)
1128 values
1129 (l_person_action_id(l_count),
1130 l_person_id(l_count),
1131 0,
1132 l_benefit_action_id,
1133 'U',
1134 1);
1135 --
1136 l_num_ranges := l_num_ranges + 1;
1137 --
1138 -- Get next sequence for the range
1139 --
1140 select ben_batch_ranges_s.nextval
1141 into l_range_id
1142 from sys.dual;
1143 --
1144 l_start_person_action_id := l_person_action_id(1);
1145 l_end_person_action_id := l_person_action_id(l_num_rows);
1146 --
1147 insert into ben_batch_ranges
1148 (range_id,
1149 benefit_action_id,
1150 range_status_cd,
1151 starting_person_action_id,
1152 ending_person_action_id,
1153 object_version_number)
1154 values
1155 (l_range_id,
1156 l_benefit_action_id,
1157 'U',
1158 l_start_person_action_id,
1159 l_end_person_action_id,
1160 1);
1161 --
1162 l_num_rows := 0;
1163 --
1164 -- Dispose of data in varrays
1165 --
1166 l_person_action_id.delete;
1167 l_person_id.delete;
1168 --
1169 commit;
1170 --
1171 end if;
1172 --
1173 Else
1174 --
1175 l_benefit_action_id := p_benefit_action_id;
1176 l_actn := 'Calling Ben_batch_utils.create_restart_person_actions...';
1177 --
1178 Ben_batch_utils.create_restart_person_actions
1179 (p_benefit_action_id => p_benefit_action_id
1180 ,p_effective_date => l_effective_date
1181 ,p_chunk_size => l_chunk_size
1182 ,p_threads => l_threads
1183 ,p_num_ranges => l_num_ranges
1184 ,p_num_persons => l_num_persons);
1185 --
1186 End if;
1187 --
1188 commit;
1189 --
1190 -- Now to multithread the code.
1191 --
1192 If l_num_ranges > 1 then
1193 --
1194 For l_count in 1..least(l_threads,l_num_ranges)-1 loop
1195 --
1196 l_actn := 'Submitting job to con-current manager...';
1197 l_request_id := fnd_request.submit_request
1198 (application => 'BEN'
1199 ,program => 'BENDFLT'
1200 ,description => NULL
1201 ,sub_request => FALSE
1202 ,argument1 => p_validate
1203 ,argument2 => l_benefit_action_id
1204 ,argument3 => l_count
1205 ,argument4 => p_effective_date
1206 ,argument5 => p_business_group_id
1207 ,argument6 => p_audit_log);
1208 --
1209 -- Store the request id of the concurrent request
1210 --
1211 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
1212 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
1213 := l_request_id;
1214 --
1215 commit;
1216 --
1217 End loop;
1218 --
1219 Elsif (l_num_ranges = 0 ) then
1220 --
1221 l_actn := 'Calling Ben_batch_utils.print_parameters...';
1222 --
1223 Ben_batch_utils.print_parameters
1224 (p_thread_id => 99
1225 ,p_benefit_action_id => l_benefit_action_id
1226 ,p_validate => p_validate
1227 ,p_business_group_id => p_business_group_id
1228 ,p_effective_date => l_effective_date
1229 ,p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id
1230 ,p_person_id => p_person_id
1231 ,p_person_selection_rule_id => p_person_selection_rule_id
1232 ,p_person_type_id => p_person_type_id
1233 ,p_ler_id => p_ler_id
1234 ,p_organization_id => p_organization_id
1235 ,p_benfts_grp_id => p_benfts_grp_id
1236 ,p_location_id => p_location_id
1237 ,p_legal_entity_id => p_legal_entity_id
1238 ,p_payroll_id => p_payroll_id
1239 ,p_audit_log => p_audit_log);
1240 --
1241 Ben_batch_utils.write(p_text =>
1242 '<< No Person got selected with above selection criteria >>' );
1243 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
1244 fnd_message.set_token('PROC',l_proc);
1245 raise l_silent_error;
1246 --
1247 End if;
1248 --
1249 l_actn := 'Calling do_multithread...';
1250 --
1251 do_multithread(errbuf => errbuf
1252 ,retcode => retcode
1253 ,p_validate => p_validate
1254 ,p_benefit_action_id => l_benefit_action_id
1255 ,p_thread_id => l_threads+1
1256 ,p_effective_date => p_effective_date
1257 ,p_business_group_id => p_business_group_id
1258 ,p_audit_log => p_audit_log);
1259 --
1260 l_actn := 'Calling ben_batch_utils.check_all_slaves_finished...';
1261 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1262 --
1263 l_actn := 'Calling ben_batch_utils.End_process...';
1264 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1265 ,p_person_selected => l_num_persons
1266 ,p_business_group_id => p_business_group_id);
1267 --
1268 l_actn := 'Calling submit_all_reports...';
1269 submit_all_reports(p_audit_log => p_audit_log);
1270 --
1271 hr_utility.set_location ('Leaving '||l_proc,70);
1272 --
1273 Exception
1274 --
1275 when l_silent_error then
1276 ben_batch_utils.write(p_text => fnd_message.get);
1277 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1278 If (l_num_ranges > 0) then
1279 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1280 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1281 ,p_person_selected => l_num_persons
1282 ,p_business_group_id => p_business_group_id);
1283 submit_all_reports(p_audit_log => p_audit_log);
1284 End if;
1285 --
1286 when others then
1287 ben_batch_utils.rpt_error(p_proc => l_proc
1288 ,p_last_actn => l_actn
1289 ,p_rpt_flag => TRUE );
1290 ben_batch_utils.write(p_text => fnd_message.get);
1291 ben_batch_utils.write(p_text => sqlerrm);
1292 ben_batch_utils.write(p_text => 'Big Error Occured');
1293 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1294 If (l_num_ranges > 0) then
1295 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1296 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1297 ,p_person_selected => l_num_persons
1298 ,p_business_group_id => p_business_group_id);
1299 submit_all_reports(p_audit_log => p_audit_log);
1300 End if;
1301 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1302 fnd_message.set_token('PROCEDURE', l_proc);
1303 fnd_message.set_token('STEP', l_actn );
1304 fnd_message.raise_error;
1305 End process;
1306 --
1307 -- ============================================================================
1308 -- << Procedure: Default_comp_obj>>
1309 -- ============================================================================
1310 Procedure Default_Comp_obj
1311 (p_validate in Boolean default FALSE
1312 ,p_per_in_ler_id in Number
1313 ,p_person_id in Number
1314 ,p_business_group_id in Number
1315 ,p_effective_date in Date
1316 ,p_pgm_id in Number
1317 ,p_pl_nip_id in Number
1318 ,p_susp_flag out nocopy Boolean
1319 ,p_batch_flag in Boolean default FALSE
1320 ,p_cls_enrt_flag in Boolean default TRUE
1321 ,p_called_frm_ss in Boolean default FALSE
1322 ,p_reinstate_dflts_flag in varchar2 default 'N' -- Enhancement Bug :8716679
1323 ,p_prev_per_in_ler_id in Number default null -- -- Enhancement Bug :8716679
1324 ) is
1325
1326 l_prev_pil_id number;
1327
1328 -- in_pndg_wkflow_flag added to block electable choice in pending workflow not to be assigned
1329 Cursor c_dflt_mn_epe is
1330 Select epe.ELIG_PER_ELCTBL_CHC_ID
1331 ,epe.PGM_ID
1332 ,epe.PL_ID
1333 ,epe.OIPL_ID
1334 ,epe.DPNT_CVG_STRT_DT_CD
1335 ,epe.DPNT_CVG_STRT_DT_RL
1336 ,epe.ENRT_CVG_STRT_DT
1337 ,epe.CRYFWD_ELIG_DPNT_CD
1338 ,epe.CRNTLY_ENRD_FLAG
1339 ,epe.DFLT_FLAG
1340 ,epe.ELCTBL_FLAG
1341 ,epe.MNDTRY_FLAG
1342 ,epe.AUTO_ENRT_FLAG
1343 ,epe.PRTT_ENRT_RSLT_ID
1344 ,epe.BUSINESS_GROUP_ID
1345 ,'DEF' actn_cd
1346 ,'N' Suspended
1347 ,epe.in_pndg_wkflow_flag
1348 From ben_elig_per_elctbl_chc epe
1349 Where epe.Auto_enrt_flag = 'N'
1350 and epe.per_in_ler_id = p_per_in_ler_id
1351 and epe.Business_group_id = p_business_group_id
1352 and (epe.elctbl_flag = 'Y' or epe.mndtry_flag = 'Y')
1353 and (p_pgm_id is NULL or epe.pgm_id = p_pgm_id)
1354 and (p_pl_nip_id is null
1355 or (p_pl_nip_id = epe.pl_id and epe.pgm_id is NULL) )
1356 /* Modified the condition for Enhancement Bug :8716679. Defaulting the explicit elections
1357 will only be called if p_reinstate_dflts_flag = 'Y' or else normal defaulting logic will work.*/
1358 and ( (p_reinstate_dflts_flag = 'N' and (epe.dflt_flag = 'Y' or epe.crntly_enrd_flag = 'Y') ) or
1359 (p_reinstate_dflts_flag = 'Y' and epe.crntly_enrd_flag = 'Y' and prtt_enrt_rslt_id is not null
1360 and 'Y' = ( select 'Y' from ben_prtt_enrt_rslt_f pen
1361 where pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
1362 and pen.per_in_ler_id = p_prev_per_in_ler_id
1363 and pen.prtt_enrt_rslt_stat_cd is null
1364 and pen.enrt_mthd_cd = 'E'
1365 and pen.sspndd_flag = 'N'
1366 and not exists
1367 (select 'Y' from ben_prtt_enrt_rslt_f pen2
1368 where pen.prtt_enrt_rslt_id = pen2.rplcs_sspndd_rslt_id
1369 and pen2.prtt_enrt_rslt_stat_cd is null
1370 and pen2.per_in_ler_id = p_prev_per_in_ler_id) )
1371 ) )
1372 /* End of change for Enhancement Bug :8716679 */
1373 and not exists (select null from ben_prtt_enrt_rslt_f pen
1374 where pen.per_in_ler_id = epe.per_in_ler_id
1375 -- Bug 6319484 Instead of checking for same pen_id,
1376 -- check if not already enrolled in same plan
1377 and nvl(pen.pgm_id,hr_api.g_number) = nvl(epe.pgm_id,hr_api.g_number)
1378 --
1379 -- bug 10377891. If there are multiple options for a plan and one is
1380 -- a rollover that was automatically enrolled.
1381 --
1382 and (pen.pl_id = epe.pl_id
1383 and pen.enrt_mthd_cd <> 'A') -- bug 10377891
1384 -- and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
1385 and pen.prtt_enrt_rslt_stat_cd IS NULL
1386 and pen.effective_end_date = hr_api.g_eot
1387 and pen.enrt_cvg_thru_dt = hr_api.g_eot )
1388 order by epe.pgm_id, epe.pl_id;
1389
1390 cursor c_test(c_pen_id number) is
1391 select pen.prtt_enrt_rslt_id||'Y' from ben_prtt_enrt_rslt_f pen
1392 where pen.prtt_enrt_rslt_id = c_pen_id
1393 and pen.per_in_ler_id = l_prev_pil_id
1394 and pen.prtt_enrt_rslt_stat_cd is null
1395 and pen.enrt_mthd_cd = 'E'
1396 and pen.sspndd_flag = 'N'
1397 and not exists
1398 (select 'Y' from ben_prtt_enrt_rslt_f pen2
1399 where pen.prtt_enrt_rslt_id = pen2.rplcs_sspndd_rslt_id
1400 and pen2.prtt_enrt_rslt_stat_cd is null
1401 and pen2.per_in_ler_id = l_prev_pil_id);
1402 l_test varchar2(100);
1403
1404 Cursor c_dflt_mn_epe_mndtry (c_pgm_id number, c_pl_id number) is
1405 Select epe.ELIG_PER_ELCTBL_CHC_ID
1406 ,epe.PGM_ID
1407 ,epe.PL_ID
1408 ,epe.OIPL_ID
1409 ,epe.DPNT_CVG_STRT_DT_CD
1410 ,epe.DPNT_CVG_STRT_DT_RL
1411 ,epe.ENRT_CVG_STRT_DT
1412 ,epe.CRYFWD_ELIG_DPNT_CD
1413 ,epe.CRNTLY_ENRD_FLAG
1414 ,epe.DFLT_FLAG
1415 ,epe.ELCTBL_FLAG
1416 ,epe.MNDTRY_FLAG
1417 ,epe.AUTO_ENRT_FLAG
1418 ,epe.PRTT_ENRT_RSLT_ID
1419 ,epe.BUSINESS_GROUP_ID
1420 ,'DEF' actn_cd
1421 ,'N' Suspended
1422 ,epe.in_pndg_wkflow_flag
1423 From ben_elig_per_elctbl_chc epe
1424 Where epe.dflt_flag = 'N'
1425 and epe.crntly_enrd_flag = 'N'
1426 and epe.mndtry_flag = 'Y'
1427 and epe.Auto_enrt_flag = 'N'
1428 and epe.per_in_ler_id = p_per_in_ler_id
1429 and epe.Business_group_id = p_business_group_id
1430 and nvl(epe.pgm_id,hr_api.g_number) = nvl(c_pgm_id, hr_api.g_number)
1431 and epe.pl_id = c_pl_id
1432 and comp_lvl_cd = 'OIPL'; -- Bug 4951065
1433 --
1434 Cursor c_pen (lc_prtt_enrt_rslt_id number) is
1435 Select prtt_enrt_rslt_id
1436 ,effective_start_date
1437 ,effective_end_date
1438 ,object_version_number
1439 ,bnft_amt
1440 ,uom
1441 ,enrt_mthd_cd
1442 ,business_group_id
1443 ,enrt_cvg_strt_dt
1444 ,enrt_cvg_thru_dt
1445 ,ERLST_DEENRT_DT
1446 ,enrt_ovrid_thru_dt
1447 ,enrt_ovridn_flag
1448 From ben_prtt_enrt_rslt_f
1449 Where prtt_enrt_rslt_id = lc_prtt_enrt_rslt_id
1450 and p_effective_date between
1451 effective_start_date and effective_end_date
1452 and prtt_enrt_rslt_stat_cd IS NULL
1453 and business_group_id = p_business_group_id
1454 ;
1455 l_pen c_pen%ROWTYPE;
1456 --
1457 Cursor c_rt (v_elig_per_elctbl_chc_id number) is
1458 Select ecr.enrt_rt_id
1459 ,nvl(ecr.val,ecr.dflt_val) default_val
1460 ,ecr.ANN_DFLT_VAL
1461 From ben_enrt_rt ecr
1462 Where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
1463 and ecr.business_group_id = p_business_group_id
1464 and ecr.entr_val_at_enrt_flag = 'Y'
1465 -- and ecr.prtt_rt_val_id is null -- ikasire bug 1834655
1466 Union
1467 Select ecr.enrt_rt_id
1468 ,nvl(ecr.val,ecr.dflt_val) default_val
1469 ,ecr.ANN_DFLT_VAL
1470 From ben_enrt_rt ecr
1471 ,ben_enrt_bnft enb
1472 Where enb.enrt_bnft_id = ecr.enrt_bnft_id
1473 and ecr.business_group_id = p_business_group_id
1474 and enb.business_group_id = p_business_group_id
1475 and enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
1476 and ecr.entr_val_at_enrt_flag = 'Y'
1477 -- and ecr.prtt_rt_val_id is null -- ikasire bug 1834655
1478 ;
1479 --
1480 Cursor c_bnft (l_elig_per_elctbl_chc_id number) is
1481 Select enrt_bnft_id, val, dflt_val, entr_val_at_enrt_flag,cvg_mlt_cd
1482 From ben_enrt_bnft
1483 Where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
1484 and dflt_flag = 'Y'
1485 ;
1486 Type enrt_id_table is table of c_rt%rowtype index by binary_integer;
1487 Type epe_table is table of c_dflt_mn_epe%rowtype index by binary_integer;
1488
1489 --Start AMN Bug 14006027
1490 cursor c_pl_enrt_dt(v_elig_per_elctbl_chc_id number) is
1491 SELECT pel.enrt_perd_strt_dt
1492 FROM ben_pil_elctbl_chc_popl pel
1493 , ben_elig_per_elctbl_chc epe
1494 WHERE pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1495 AND epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id;
1496
1497 l_pl_enrt_dt c_pl_enrt_dt%rowtype;
1498 l_effective_dt date;
1499 --End AMN Bug 14006027
1500 --Start AMN Enh 16043887
1501 CURSOR c_bnf_exl_participant(l_pl_id number) IS
1502 SELECT pl.bnf_exl_participant_flag
1503 ,pl.name
1504 FROM ben_pl_f pl
1505 WHERE pl.pl_id = l_pl_id
1506 AND p_effective_date
1507 BETWEEN pl.effective_start_date
1508 AND pl.effective_end_date;
1509 l_bnf_exl_participant c_bnf_exl_participant%rowtype;
1510 CURSOR c_pl_bnf(l_pen_id number) IS
1511 SELECT 1
1512 FROM ben_pl_bnf_f
1513 WHERE prtt_enrt_rslt_id = l_pen_id
1514 AND bnf_person_id = p_person_id
1515 AND p_effective_date
1516 BETWEEN effective_start_date
1517 AND effective_end_date;
1518 l_pl_bnf c_pl_bnf%rowtype;
1519 --End AMN Enh 16043887
1520 --
1521 -- Local Variables
1522 --
1523 l_proc varchar2(80) := g_package || '.Default_comp_obj';
1524 l_output_string varchar2(80);
1525 l_validate boolean;
1526 l_actn varchar2(80);
1527 l_rt enrt_id_table;
1528 l_tot_rt number(5) := 0;
1529 l_bnft_amt ben_enrt_bnft.val%type;
1530 l_dflt_bnft_amt ben_enrt_bnft.val%type;
1531 l_entr_flag varchar2(1);
1532 l_bnft_id ben_enrt_bnft.enrt_bnft_id%type;
1533 l_suspend_flag varchar2(30);
1534 l_prtt_enrt_interim_id number(15);
1535 l_datetrack_mode varchar2(30);
1536 l_effective_start_date date;
1537 l_effective_end_date date;
1538 l_dump_num number(15);
1539 l_dump_boolean boolean;
1540 l_epe epe_table;
1541 l_epe_cnt binary_integer := 0;
1542 l_prev_pgm_id number := -999999;
1543 l_prev_pl_id number := -999999;
1544 l_actn_cd varchar2(30);
1545 l_cvg_mlt_cd varchar2(30) := null ;
1546 l_cryfwd_elig_dpnt_cd varchar2(30);
1547 l_prev_rslt_id_at number := 0 ;
1548 l_prev_prtt_enrt_rslt_id number ;
1549 l_not_ovridn boolean := true ;
1550 l_global_pil_rec ben_global_enrt.g_global_pil_rec_type; -- 4684991
1551 l_rdefault_table_cnt number;
1552
1553 Begin
1554 hr_utility.set_location ('Entering '|| l_proc , 5);
1555
1556 l_actn := 'Openning c_dflt_mn_epe cursor...';
1557 hr_utility.set_location (l_actn , 10);
1558 p_susp_flag := FALSE;
1559
1560 -- Bug - 4684991 - Fetch pil details
1561 ben_global_enrt.get_pil
1562 (p_per_in_ler_id => p_per_in_ler_id
1563 ,p_global_pil_rec => l_global_pil_rec);
1564 --
1565 --
1566 -- Retreive Records from Elig_per_elctbl_chc Table.
1567 --
1568 --
1569
1570 For l_rec in c_dflt_mn_epe loop
1571 --hr_utility.set_location (l_actn , 11);
1572 hr_utility.set_location ('l_rec.prtt_enrt_rslt_id '|| l_rec.prtt_enrt_rslt_id , 5);
1573 open c_test(l_rec.prtt_enrt_rslt_id);
1574 fetch c_test into l_test;
1575 hr_utility.set_location ('l_test '|| l_test , 5);
1576 close c_test;
1577
1578 If (l_prev_pgm_id = -999999) then
1579 NULL;
1580 Elsif (l_prev_pgm_id <> l_rec.pgm_id) then
1581 For l_rec1 in c_dflt_mn_epe_mndtry(c_pgm_id => l_prev_pgm_id
1582 ,c_pl_id => l_prev_pl_id ) loop
1583 l_epe_cnt := l_epe_cnt + 1;
1584 l_epe(l_epe_cnt) := l_rec1;
1585 --hr_utility.set_location (l_actn||' First ', 12 );
1586 End loop;
1587 Elsif (l_prev_pl_id <> l_rec.pl_id) then
1588 For l_rec1 in c_dflt_mn_epe_mndtry(c_pgm_id => l_prev_pgm_id
1589 ,c_pl_id => l_prev_pl_id ) loop
1590 l_epe_cnt := l_epe_cnt + 1;
1591 l_epe(l_epe_cnt) := l_rec1;
1592 --hr_utility.set_location (l_actn||' Second ', 12 );
1593 End loop;
1594 End if;
1595 --hr_utility.set_location (l_actn||'Outside ', 13);
1596 l_epe_cnt := l_epe_cnt + 1;
1597 l_epe(l_epe_cnt) := l_rec;
1598 l_prev_pgm_id := l_rec.pgm_id;
1599 l_prev_pl_id := l_rec.pl_id;
1600 End loop;
1601 --
1602 --hr_utility.set_location ('Last loop '||l_actn , 15);
1603 -- Last loop
1604 --
1605 If l_epe_cnt > 0 then
1606 For l_rec1 in c_dflt_mn_epe_mndtry(c_pgm_id => l_prev_pgm_id
1607 ,c_pl_id => l_prev_pl_id ) loop
1608 l_epe_cnt := l_epe_cnt + 1;
1609 l_epe(l_epe_cnt) := l_rec1;
1610 --hr_utility.set_location ('l_rec1 ' , 16);
1611 End loop;
1612 End if;
1613 --
1614 --hr_utility.set_location ('Before '||l_actn , 17);
1615 For i in 1..l_epe_cnt Loop
1616 --
1617 l_not_ovridn := true ;
1618 --
1619 If (l_epe(i).prtt_enrt_rslt_id is not NULL) then
1620 l_actn := 'Getting enrollment data from c_pen cursor...';
1621 hr_utility.set_location (l_actn , 18);
1622 open c_pen(l_epe(i).prtt_enrt_rslt_id);
1623 fetch c_pen into l_pen;
1624 If (c_pen%notfound) then
1625 Close c_pen;
1626 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FND');
1627 fnd_message.set_token('PROC',l_proc);
1628 fnd_message.set_token('ID',to_char(l_epe(i).prtt_enrt_rslt_id));
1629 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1630 fnd_message.set_token('LER_ID',null);
1631 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
1632 fnd_message.raise_error;
1633 End if;
1634 close c_pen;
1635 --
1636 hr_utility.set_location (' lf_evt_ocrd_dt '|| to_char(l_global_pil_rec.lf_evt_ocrd_dt) , 5);
1637 --
1638 -- 4684991 - Use lf_evt_ocrd_dt instead of p_effective_date
1639 if l_pen.enrt_ovridn_flag = 'Y'
1640 AND nvl(l_pen.enrt_ovrid_thru_dt,hr_api.g_eot) >
1641 NVL(l_global_pil_rec.lf_evt_ocrd_dt, p_effective_date) then
1642 --
1643 l_not_ovridn := false ;
1644 --
1645 end if ;
1646 --
1647 If (l_pen.effective_start_date = p_effective_date) then
1648 l_datetrack_mode := hr_api.g_correction;
1649 Else
1650 l_datetrack_mode := hr_api.g_update;
1651 End if;
1652 l_epe(i).actn_cd := 'UPD';
1653 Else
1654 l_datetrack_mode := hr_api.g_insert;
1655 l_epe(i).actn_cd := 'INS';
1656 End if;
1657
1658 If(nvl(l_epe(i).dflt_flag,'X') = 'Y'
1659 or nvl(l_epe(i).mndtry_flag, 'X') = 'Y') and
1660 nvl(l_epe(i).in_pndg_wkflow_flag,'N') <> 'Y' and
1661 l_not_ovridn then -- added flag condition so that if pending flag=Y
1662 -- election is not made
1663
1664 l_actn := 'Openning c_bnft cursor for benefit...';
1665 hr_utility.set_location(l_actn , 20);
1666 l_bnft_id:=null;
1667 l_bnft_amt:=null;
1668 l_dflt_bnft_amt:=null;
1669 l_entr_flag:=null;
1670 open c_bnft(l_epe(i).elig_per_elctbl_chc_id);
1671
1672 fetch c_bnft into l_bnft_id, l_bnft_amt, l_dflt_bnft_amt, l_entr_flag,l_cvg_mlt_cd ;
1673 --hr_utility.set_location(' l_bnft_amt '||l_bnft_amt ,21 );
1674 --hr_utility.set_location(' l_dflt_bnft_amt '||l_dflt_bnft_amt , 22);
1675 --hr_utility.set_location(' l_entr_flag '||l_entr_flag , 23);
1676 close c_bnft;
1677 -- if the benefit amount is entered at enrollment, use the default
1678 -- benefit value instead of the val field.
1679 if l_entr_flag = 'Y' or l_cvg_mlt_cd = 'SAAEAR' then
1680 l_bnft_amt := l_dflt_bnft_amt;
1681 end if;
1682
1683 l_actn := 'Initializing rate cache and load...';
1684
1685 For j in 1..10 loop
1686 l_rt(j).enrt_rt_id := NULL;
1687 l_rt(j).default_val := 0;
1688 l_rt(j).ann_dflt_val := 0;
1689 End loop;
1690 l_tot_rt := 0;
1691 For Crec in c_rt(l_epe(i).elig_per_elctbl_chc_id) loop
1692 l_tot_rt := l_tot_rt + 1;
1693 l_rt(l_tot_rt).enrt_rt_id := Crec.enrt_rt_id;
1694 l_rt(l_tot_rt).default_val := Crec.default_val;
1695 l_rt(l_tot_rt).ann_dflt_val := Crec.ann_dflt_val;
1696 --hr_utility.set_location('l_rt(l_tot_rt).ann_dflt_val '||l_rt(l_tot_rt).ann_dflt_val ,26);
1697 --hr_utility.set_location('Crec.dflt_val '||l_rt(l_tot_rt).dflt_val , 27);
1698 End loop;
1699 l_suspend_flag := 'N';
1700 If (nvl(l_epe(i).actn_cd,'XXX') = 'UPD') then
1701 If nvl(l_bnft_amt,0) = nvl(l_pen.bnft_amt,0) then
1702 l_epe(i).actn_cd := 'DEF';
1703 End if;
1704 End if;
1705 --
1706 l_actn := 'Calling ben_election_information.election_information...';
1707 --
1708 /*
1709 hr_utility.set_location(l_actn , 30);
1710 hr_utility.set_location(' l_bnft_id '||l_bnft_id ,30);
1711 hr_utility.set_location(' l_bnft_amt '||l_bnft_amt ,30);
1712 hr_utility.set_location(' p_enrt_rt_id1 '||l_rt(1).enrt_rt_id ,30);
1713 hr_utility.set_location(' l_rt(1).dflt_val '||l_rt(1).dflt_val ,31);
1714 hr_utility.set_location(' p_Ann_rt_val1 '|| l_rt(1).ann_dflt_val , 31);
1715 */
1716 --
1717 /* Added for Enhancement Bug 8716679
1718 To add the electable choices to pl/sql table that are defaulted. This pl/sql
1719 is scanned to check whether the enrollment record is already defaulted. If the
1720 enrollment is defaulted then the enrollment that is to be reinstated will
1721 not be reinstated*/
1722 hr_utility.set_location ('Defaulted epe '||l_epe(i).elig_per_elctbl_chc_id,199);
1723 hr_utility.set_location ('p_prev_per_in_ler_id '|| p_prev_per_in_ler_id , 5);
1724 if(p_reinstate_dflts_flag = 'Y') then
1725 l_rdefault_table_cnt := nvl( ben_lf_evt_clps_restore.g_reinstated_defaults.LAST, 0) + 1;
1726 ben_lf_evt_clps_restore.g_reinstated_defaults(l_rdefault_table_cnt) := l_epe(i).elig_per_elctbl_chc_id;
1727 end if;
1728 /* End of Enhancement Bug 8716679*/
1729 --Start AMN Bug 14006027 : Default elections has to be made on the
1730 --Enrollment period start date and not on the effective date.
1731 open c_pl_enrt_dt(l_epe(i).elig_per_elctbl_chc_id);
1732 fetch c_pl_enrt_dt into l_pl_enrt_dt;
1733 if c_pl_enrt_dt%found then
1734 l_effective_dt := l_pl_enrt_dt.enrt_perd_strt_dt;
1735 else
1736 l_effective_dt := p_effective_date;
1737 end if;
1738 close c_pl_enrt_dt;
1739 hr_utility.set_location ('p_effective_date '|| p_effective_date , 5);
1740 hr_utility.set_location ('l_effective_dt '|| l_effective_dt , 5);
1741 --End AMN Bug 14006027
1742
1743 Ben_election_information.election_information
1744 (p_elig_per_elctbl_chc_id => l_epe(i).elig_per_elctbl_chc_id
1745 ,p_prtt_enrt_rslt_id => l_epe(i).prtt_enrt_rslt_id
1746 ,p_effective_date => l_effective_dt --AMN Bug 14006027 p_effective_date
1747 ,p_enrt_mthd_cd => 'D'
1748 ,p_business_group_id => p_business_group_id
1749 ,p_enrt_bnft_id => l_bnft_id
1750 ,p_bnft_val => l_bnft_amt
1751 ,p_enrt_rt_id1 => l_rt(1).enrt_rt_id
1752 ,p_enrt_rt_id2 => l_rt(2).enrt_rt_id
1753 ,p_enrt_rt_id3 => l_rt(3).enrt_rt_id
1754 ,p_enrt_rt_id4 => l_rt(4).enrt_rt_id
1755 ,p_enrt_rt_id5 => l_rt(5).enrt_rt_id
1756 ,p_enrt_rt_id6 => l_rt(6).enrt_rt_id
1757 ,p_enrt_rt_id7 => l_rt(7).enrt_rt_id
1758 ,p_enrt_rt_id8 => l_rt(8).enrt_rt_id
1759 ,p_enrt_rt_id9 => l_rt(9).enrt_rt_id
1760 ,p_enrt_rt_id10 => l_rt(10).enrt_rt_id
1761 ,p_rt_val1 => l_rt(1).default_val
1762 ,p_rt_val2 => l_rt(2).default_val
1763 ,p_rt_val3 => l_rt(3).default_val
1764 ,p_rt_val4 => l_rt(4).default_val
1765 ,p_rt_val5 => l_rt(5).default_val
1766 ,p_rt_val6 => l_rt(6).default_val
1767 ,p_rt_val7 => l_rt(7).default_val
1768 ,p_rt_val8 => l_rt(8).default_val
1769 ,p_rt_val9 => l_rt(9).default_val
1770 ,p_rt_val10 => l_rt(10).default_val
1771 ,p_Ann_rt_val1 => l_rt(1).ann_dflt_val
1772 ,p_Ann_rt_val2 => l_rt(2).ann_dflt_val
1773 ,p_Ann_rt_val3 => l_rt(3).ann_dflt_val
1774 ,p_Ann_rt_val4 => l_rt(4).ann_dflt_val
1775 ,p_Ann_rt_val5 => l_rt(5).ann_dflt_val
1776 ,p_Ann_rt_val6 => l_rt(6).ann_dflt_val
1777 ,p_Ann_rt_val7 => l_rt(7).ann_dflt_val
1778 ,p_Ann_rt_val8 => l_rt(8).ann_dflt_val
1779 ,p_Ann_rt_val9 => l_rt(9).ann_dflt_val
1780 ,p_Ann_rt_val10 => l_rt(10).ann_dflt_val
1781 ,p_datetrack_mode => l_datetrack_mode
1782 ,p_suspend_flag => l_suspend_flag
1783 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
1784 ,P_PRTT_RT_VAL_ID1 => l_dump_num
1785 ,P_PRTT_RT_VAL_ID2 => l_dump_num
1786 ,P_PRTT_RT_VAL_ID3 => l_dump_num
1787 ,P_PRTT_RT_VAL_ID4 => l_dump_num
1788 ,P_PRTT_RT_VAL_ID5 => l_dump_num
1789 ,P_PRTT_RT_VAL_ID6 => l_dump_num
1790 ,P_PRTT_RT_VAL_ID7 => l_dump_num
1791 ,P_PRTT_RT_VAL_ID8 => l_dump_num
1792 ,P_PRTT_RT_VAL_ID9 => l_dump_num
1793 ,P_PRTT_RT_VAL_ID10 => l_dump_num
1794 ,P_OBJECT_VERSION_NUMBER => l_pen.object_version_number
1795 ,p_effective_start_date => l_effective_start_date
1796 ,p_effective_end_date => l_effective_end_date
1797 ,P_DPNT_ACTN_WARNING => l_dump_boolean
1798 ,P_BNF_ACTN_WARNING => l_dump_boolean
1799 ,P_CTFN_ACTN_WARNING => l_dump_boolean
1800 );
1801 -- after the enhncemnt # 2685018 cryfwd_elig_dpnt_cd value is concated with
1802 -- result id from where the dpnt carry forwarded , this will seprate the code from
1803 --- result id
1804
1805 l_prev_prtt_enrt_rslt_id := null; -- Reintializing the previous enrt result id
1806 l_cryfwd_elig_dpnt_cd := l_epe(i).cryfwd_elig_dpnt_cd ;
1807 l_prev_rslt_id_at := instr(l_cryfwd_elig_dpnt_cd, '^') ;
1808 --- if the result id concated with the code, then the caht '^' must be aprt of the
1809 --- the code
1810
1811 if l_prev_rslt_id_at > 0 then
1812 --- if the to_number errors , catch the exception
1813 Begin
1814 l_prev_prtt_enrt_rslt_id := to_number(substr(l_cryfwd_elig_dpnt_cd,l_prev_rslt_id_at+1) );
1815 Exception
1816 when value_error then
1817 l_prev_prtt_enrt_rslt_id := null;
1818 End ;
1819 l_cryfwd_elig_dpnt_cd := substr(l_cryfwd_elig_dpnt_cd,1,l_prev_rslt_id_at-1) ;
1820 end if ;
1821
1822
1823 if l_datetrack_mode = hr_api.g_insert and l_cryfwd_elig_dpnt_cd = 'CFRRWP' then
1824
1825 ben_automatic_enrollments.reinstate_dpnt
1826 (p_pgm_id => l_epe(i).pgm_id,
1827 p_pl_id => l_epe(i).pl_id,
1828 p_oipl_id => l_epe(i).oipl_id,
1829 p_business_group_id => p_business_group_id,
1830 p_person_id => p_person_id,
1831 p_per_in_ler_id => p_per_in_ler_id,
1832 p_elig_per_elctbl_chc_id => l_epe(i).elig_per_elctbl_chc_id,
1833 p_dpnt_cvg_strt_dt_cd => l_epe(i).dpnt_cvg_strt_dt_cd,
1834 p_dpnt_cvg_strt_dt_rl => l_epe(i).dpnt_cvg_strt_dt_rl,
1835 p_enrt_cvg_strt_dt => l_epe(i).enrt_cvg_strt_dt,
1836 p_effective_date => p_effective_date,
1837 p_prev_prtt_enrt_rslt_id => l_prev_prtt_enrt_rslt_id
1838 );
1839 end if;
1840 l_actn := 'Getting suspend status...';
1841 --
1842 l_epe(i).suspended := l_suspend_flag;
1843 If (l_suspend_flag = 'Y') then
1844 p_susp_flag := TRUE;
1845 End if;
1846 Elsif(nvl(l_epe(i).dflt_flag,'X') <> 'Y'
1847 and nvl(l_epe(i).crntly_enrd_flag, 'X') = 'Y'
1848 and p_effective_date >= nvl(l_pen.ERLST_DEENRT_DT, hr_api.g_sot)
1849 and l_epe(i).AUTO_ENRT_FLAG = 'N'
1850 ) then
1851 --
1852 -- The enrollment result is ended in the multi_rows_edit. This
1853 -- is information for the batch reports.
1854 --
1855 l_epe(i).actn_cd := 'DEL';
1856 --
1857 End if;
1858 If (p_batch_flag) then
1859 --
1860 l_actn := 'Calling Ben_batch_utils.cache_comp_obj...';
1861 Ben_batch_utils.cache_comp_obj
1862 (p_prtt_enrt_rslt_id => l_epe(i).prtt_enrt_rslt_id
1863 ,p_effective_date => p_effective_date
1864 ,p_actn_cd => l_epe(i).actn_cd
1865 ,p_suspended => l_epe(i).suspended);
1866 End if;
1867 End loop;
1868
1869 -- Bug 4421813 Call init so that the person details are available for later procedures
1870
1871 if l_epe_cnt = 0 and fnd_global.conc_request_id = -1 and p_called_frm_ss then
1872 --
1873 ben_env_object.init(p_business_group_id => p_business_group_id,
1874 p_effective_date => p_effective_date,
1875 p_thread_id => 1,
1876 p_chunk_size => 1,
1877 p_threads => 1,
1878 p_max_errors => 1,
1879 p_benefit_action_id => null);
1880 --
1881 end if;
1882
1883
1884 --
1885 ben_proc_common_enrt_rslt.set_elcn_made_or_asnd_dt(
1886 p_per_in_ler_id => p_per_in_ler_id
1887 ,p_pgm_id => p_pgm_id
1888 ,p_pl_id => p_pl_nip_id --null Bug 2961251 passed pl_id parameter instead of null
1889 ,p_enrt_mthd_cd => 'D' -- Explicit
1890 ,p_business_group_id => p_business_group_id
1891 ,p_effective_date => p_effective_date
1892 ,p_validate => FALSE
1893 );
1894
1895 -- Check data to make sure multi-rows adit passed. This section is used
1896 -- for the last program ID.
1897 --
1898 /* Bug 12688164: Added 'if' condition. Multi row edit validation in default enrollment process should happen after the
1899 enrollments of backed out LE are reinstated. If no enrollments are
1900 reinstated multi row edit should be called after applying the defaults */
1901 if(p_reinstate_dflts_flag = 'N') then
1902 -- if (not p_called_frm_ss) then
1903 l_actn := 'Calling Ben_PRTT_ENRT_RESULT_api.multi_rows_edit...';
1904 Ben_PRTT_ENRT_RESULT_api.multi_rows_edit
1905 (p_person_id => p_person_id
1906 ,p_effective_date => p_effective_date
1907 ,p_business_group_id => p_business_group_id
1908 ,p_pgm_id => p_pgm_id
1909 ,p_per_in_ler_id => p_per_in_ler_id
1910 ,p_called_frm_ss => p_called_frm_ss
1911 );
1912 -- End if;
1913 --
1914 /*Start Enh 16043887: When the Beneficiaries are carry forwared and the
1915 Exclude Participant flag is set, show a note.
1916 */
1917
1918 For i in 1..l_epe_cnt Loop
1919 If(nvl(l_epe(i).dflt_flag,'X') = 'Y'
1920 or nvl(l_epe(i).mndtry_flag, 'X') = 'Y')
1921 and nvl(l_epe(i).in_pndg_wkflow_flag,'N') <> 'Y'
1922 and l_not_ovridn then
1923 open c_bnf_exl_participant(l_epe(i).pl_id);
1924 fetch c_bnf_exl_participant into l_bnf_exl_participant;
1925 close c_bnf_exl_participant;
1926 if 'Y' = l_bnf_exl_participant.bnf_exl_participant_flag then
1927 open c_pl_bnf(l_epe(i).prtt_enrt_rslt_id);
1928 fetch c_pl_bnf into l_pl_bnf;
1929 if c_pl_bnf%found then
1930 fnd_message.set_name('BEN','BEN_94757_BNF_SLF_DSGN_NOTE');
1931 fnd_message.set_token('PLAN',l_bnf_exl_participant.name);
1932 benutils.write(p_text => fnd_message.get);
1933 end if;
1934 close c_pl_bnf;
1935 end if;
1936 end if;
1937 end loop;
1938 --End Enh 16043887
1939
1940 -- Invoke post result process.
1941 --
1942 l_actn := 'Calling Ben_proc_common_enrt_rslt.process_post_results...';
1943 Ben_proc_common_enrt_rslt.process_post_results
1944 (p_person_id => p_person_id
1945 ,p_enrt_mthd_cd => 'D'
1946 ,p_effective_date => p_effective_date
1947 ,p_business_group_id => p_business_group_id
1948 ,p_validate => FALSE
1949 ,p_per_in_ler_id => p_per_in_ler_id
1950 ,p_called_frm_ss =>p_called_frm_ss
1951 );
1952 --
1953 -- Invoke process_post_enrollment.
1954 --
1955 l_actn := 'Calling Ben_proc_common_enrt_rslt.process_post_enrollment...';
1956 Ben_proc_common_enrt_rslt.process_post_enrollment
1957 (p_per_in_ler_id => p_per_in_ler_id
1958 ,p_pgm_id => p_pgm_id
1959 ,p_pl_id => p_pl_nip_id
1960 ,p_enrt_mthd_cd => 'D'
1961 ,p_proc_cd => 'DFLTENRT'
1962 ,p_person_id => p_person_id
1963 ,p_business_group_id => p_business_group_id
1964 ,p_effective_date => p_effective_date
1965 ,p_validate => FALSE
1966 ,p_cls_enrt_flag => p_cls_enrt_flag
1967 );
1968 end if;
1969 hr_utility.set_location ('Leaving '|| l_proc,10);
1970 Exception
1971 when app_exception.application_exception then -- 6027345
1972 fnd_message.raise_error; -- 6027345
1973 When others then
1974 if p_batch_flag then
1975 --
1976 -- Update person action to errored as record has an error
1977 --
1978 -- ben_batch_utils.write(p_text => fnd_message.get);
1979 ben_batch_utils.write(p_text => sqlerrm);
1980 ben_batch_utils.rpt_error(p_proc => l_proc
1981 ,p_last_actn => l_actn
1982 ,p_rpt_flag => p_batch_flag
1983 );
1984 raise ben_batch_utils.g_record_error ;
1985 -- Added for Bug 2370264
1986 else
1987 hr_utility.set_location ('Error in Default_Comp_obj : '|| sqlerrm , 87);
1988 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
1989 fnd_message.set_token('2',substr(sqlerrm,1,500)); -- 4695708
1990 fnd_message.raise_error;
1991 end if;
1992 -- End 2370264
1993 End Default_Comp_obj;
1994 --
1995 -- ============================================================================
1996 -- << Procedure: process_default_enrt >>
1997 -- ============================================================================
1998 --
1999 Procedure Process_default_enrt
2000 (p_validate in varchar2 default 'N'
2001 ,p_person_id in number default null
2002 ,p_person_action_id in number default null
2003 ,p_object_version_number in out nocopy number
2004 ,p_business_group_id in number
2005 ,p_effective_date in date
2006 ,p_batch_flag in Boolean default FALSE
2007 ,p_audit_log in varchar2 default 'N'
2008 ) is
2009 --
2010 -- Local Cursor
2011 --
2012 -- See bug 1960 : this cursor is not retrieving any pil_popl's due to :
2013 -- and a.ELCNS_MADE_DT is not NULL
2014 -- so above part of where clause is chaged to
2015 -- and a.ELCNS_MADE_DT is NULL
2016 --
2017 Cursor C_pel is
2018 Select a.PER_IN_LER_ID
2019 ,a.PGM_ID
2020 ,a.PL_ID
2021 ,b.lf_evt_ocrd_dt
2022 ,b.ler_id
2023 ,a.dflt_enrt_dt
2024 ,a.enrt_perd_strt_dt
2025 ,a.enrt_perd_end_dt
2026 From Ben_pil_elctbl_chc_popl a
2027 ,ben_per_in_ler b
2028 ,ben_ler_f ler
2029 Where a.PIL_ELCTBL_POPL_STAT_CD = 'STRTD'
2030 --and a.business_group_id = b.business_group_id
2031 and a.business_group_id = p_business_group_id
2032 and a.per_in_ler_id = b.per_in_ler_id
2033 and a.dflt_enrt_dt <= p_effective_date -- 7166971
2034 and b.per_in_ler_stat_cd = 'STRTD'
2035 and a.ELCNS_MADE_DT is NULL
2036 and ler.ler_id = b.ler_id
2037 and ler.typ_cd not in ('SCHEDDU') -- bug5768880
2038 and p_effective_date between ler.effective_start_date
2039 and ler.effective_End_date
2040 and b.person_id = p_person_id ;
2041 --
2042 Cursor C_pil is
2043 Select b.PER_IN_LER_ID
2044 ,b.lf_evt_ocrd_dt
2045 ,b.ler_id
2046 From ben_per_in_ler b,
2047 ben_ler_f ler
2048 Where b.per_in_ler_stat_cd = 'STRTD'
2049 and b.person_id = p_person_id
2050 and b.ler_id = ler.ler_id
2051 and ler.typ_cd not in ('COMP','GSP')
2052 and p_effective_date between
2053 ler.effective_start_date and
2054 ler.effective_end_date
2055 ;
2056 --
2057 -- Local Variables
2058 --
2059 l_proc Varchar2(80) := g_package || '.process_default enrollment';
2060 l_output_string Varchar2(80);
2061 l_validate boolean;
2062 l_actn varchar2(80);
2063 l_bnft_amt ben_enrt_bnft.val%type;
2064 l_bnft_id ben_enrt_bnft.enrt_bnft_id%type;
2065 l_datetrack_mode varchar2(30);
2066 l_pel_cnt binary_integer := 0;
2067 l_susp_flag boolean;
2068 l_output varchar2(2000);
2069 l_per_in_ler_id number;
2070 l_lf_evt_ocrd_dt date;
2071 l_ler_id number;
2072 l_dflt_enrt_date date;
2073 --
2074 begin
2075 -- hr_utility.trace_on(NULL,'TRC');
2076 hr_utility.set_location ('Entering '|| l_proc,10);
2077 l_actn := 'Initializing...';
2078 Savepoint process_default_enrt_savepoint;
2079 --
2080 -- Cache person data and write personal data into cache.
2081 --
2082 l_actn := 'Calling ben_batch_utils.person_header...';
2083 ben_batch_utils.person_header
2084 (p_person_id => p_person_id
2085 ,p_business_group_id => p_business_group_id
2086 ,p_effective_date => p_effective_date
2087 );
2088 --
2089 l_actn := 'Calling ben_batch_utils.ini(COMP_OBJ)...';
2090 ben_batch_utils.ini('COMP_OBJ');
2091 For l_rec in c_pel loop
2092 l_pel_cnt := l_pel_cnt + 1;
2093 l_per_in_ler_id:=l_rec.per_in_ler_id;
2094 l_ler_id := l_rec.ler_id;
2095 l_lf_evt_ocrd_dt := l_rec.lf_evt_ocrd_dt;
2096 --
2097 l_actn := 'Calling Default_comp_obj(pgm:' || to_char(l_rec.pgm_id) ||
2098 ' Pl_no_Pgm:' || to_char(l_rec.pl_id) || ')';
2099 --
2100 --
2101 -- Bug 5407755
2102 -- Default enrollment date = nvl ( ( 'Defaults will be assigned on',
2103 -- OR 'Days after Enrollment Period to Apply Defaults'
2104 -- ),
2105 -- Enrollment Period End Date
2106 -- )
2107 --
2108 l_dflt_enrt_date := NVL (l_rec.dflt_enrt_dt, l_rec.enrt_perd_end_dt);
2109 --
2110 IF l_dflt_enrt_date IS NULL
2111 THEN
2112 l_dflt_enrt_date := p_effective_date;
2113 END IF;
2114 --
2115 hr_utility.set_location ('l_Dflt_enrt_Date = ' || l_dflt_enrt_date, 9999);
2116 --
2117 Default_comp_obj
2118 (p_validate => FALSE
2119 ,p_per_in_ler_id => l_rec.per_in_ler_id
2120 ,p_person_id => p_person_id
2121 ,p_business_group_id => p_business_group_id
2122 ,p_effective_date => l_Dflt_enrt_Date /* Bug 5158204 */
2123 ,p_pgm_id => l_rec.pgm_id
2124 ,p_pl_nip_id => l_rec.pl_id
2125 ,p_susp_flag => l_susp_flag
2126 ,p_batch_flag => TRUE
2127 ,p_cls_enrt_flag => FALSE
2128 );
2129 --
2130 End loop;
2131 --
2132 -- jcarpent
2133 -- Bug 1609055. If this is null then you get an error inserting
2134 -- the log rows. Instead just fetch the per_in_ler_info for the
2135 -- started event.
2136 -- Tilak
2137 -- now can be multiple per_in_ler_id is started status
2138 if l_ler_id is null then
2139 open c_pil;
2140 fetch c_pil into
2141 l_per_in_ler_id,
2142 l_lf_evt_ocrd_dt,
2143 l_ler_id;
2144 close c_pil;
2145 end if;
2146 --
2147 -- Close enrollment i.e. update the per_in_ler to processed.
2148 --
2149 if l_pel_cnt>0 then
2150 ben_close_enrollment.close_single_enrollment
2151 (p_per_in_ler_id => l_per_in_ler_id
2152 ,p_effective_date => p_effective_date
2153 ,p_business_group_id => p_business_group_id
2154 ,p_validate => false
2155 ,p_close_uneai_flag => 'Y'
2156 ,p_uneai_effective_date => p_effective_date
2157 );
2158 end if;
2159 --
2160 l_actn := 'Calling Ben_batch_utils.write_comp...';
2161 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
2162 ,p_effective_date => p_effective_date
2163 );
2164 If (p_validate = 'Y') then
2165 Rollback to process_default_enrt_savepoint;
2166 End if;
2167 --
2168 l_actn := 'Calling write_person_category...';
2169 write_person_category (p_audit_log => p_audit_log
2170 ,p_person_id => p_person_id
2171 ,p_business_group_id => p_business_group_id
2172 ,p_effective_date => p_effective_date
2173 );
2174 --
2175 If p_person_action_id is not null then
2176 --
2177 l_actn := 'Calling ben_person_actions_api.update_person_actions...';
2178 --
2179 update ben_person_actions
2180 set action_status_cd = 'P'
2181 where person_action_id = p_person_action_id;
2182 --
2183 End if;
2184 --
2185 g_rec.person_id := p_person_id;
2186 g_rec.ler_id := l_ler_id;
2187 g_rec.per_in_ler_id := l_per_in_ler_id;
2188 g_rec.lf_evt_ocrd_dt := l_lf_evt_ocrd_dt;
2189 g_rec.replcd_flag := 'N';
2190 g_rec.crtd_flag := 'N';
2191 g_rec.tmprl_flag := 'N';
2192 g_rec.dltd_flag := 'N';
2193 g_rec.open_and_clsd_flag := 'N';
2194 g_rec.not_crtd_flag := 'N';
2195 g_rec.clsd_flag := 'Y';
2196 g_rec.stl_actv_flag := 'N';
2197 g_rec.clpsd_flag := 'N';
2198 g_rec.clsn_flag := 'N';
2199 g_rec.no_effect_flag := 'N';
2200 g_rec.cvrge_rt_prem_flag := 'N';
2201 g_rec.business_group_id := p_business_group_id;
2202 g_rec.effective_date := p_effective_date;
2203 --
2204 benutils.write(p_rec => g_rec);
2205 --
2206 g_persons_procd := g_persons_procd + 1;
2207 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
2208 hr_utility.set_location ('Leaving '|| l_proc,10);
2209 Exception
2210 When others then
2211 rollback to process_default_enrt_savepoint;
2212 g_persons_errored := g_persons_errored + 1;
2213 ben_batch_utils.write_error_rec;
2214 ben_batch_utils.write(p_text => fnd_message.get);
2215 ben_batch_utils.write(p_text => sqlerrm);
2216 ben_batch_utils.rpt_error(p_proc => l_proc
2217 ,p_last_actn => l_actn
2218 ,p_rpt_flag => TRUE);
2219 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
2220 ,p_effective_date => p_effective_date
2221 );
2222 If p_person_action_id is not null then
2223 ben_person_actions_api.update_person_actions
2224 (p_person_action_id => p_person_action_id
2225 ,p_action_status_cd => 'E'
2226 ,p_object_version_number => p_object_version_number
2227 ,p_effective_date => p_effective_date
2228 );
2229 End if;
2230 write_person_category (p_audit_log => p_audit_log
2231 ,p_error => TRUE
2232 ,p_person_id => p_person_id
2233 ,p_business_group_id => p_business_group_id
2234 ,p_effective_date => p_effective_date
2235 );
2236 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
2237 raise ben_batch_utils.g_record_error;
2238 end process_default_enrt;
2239
2240 -- ============================================================================
2241 -- << Procedure: Default_comp_obj_w >>
2242 -- ============================================================================
2243 Procedure Default_Comp_obj_w
2244 (p_validate in varchar2 default 'TRUE'
2245 ,p_per_in_ler_id in Number
2246 ,p_person_id in Number
2247 ,p_business_group_id in Number
2248 ,p_effective_date in Date
2249 ,p_pgm_id in Number
2250 ,p_pl_nip_id in Number default null
2251 ,p_susp_flag out nocopy varchar2
2252 ,p_batch_flag in varchar2 default 'FALSE'
2253 ,p_cls_enrt_flag in varchar2 default 'FALSE'
2254 ) is
2255
2256 l_proc Varchar2(80) := g_package || '.Default_Comp_obj_w';
2257 l_suspend_flag boolean;
2258 l_validate boolean;
2259 l_batch_flag boolean;
2260 l_cls_enrt_flag boolean;
2261 l_commit number;
2262 begin
2263 --
2264 fnd_msg_pub.initialize;
2265 hr_utility.set_location ('Entering '|| l_proc,10);
2266
2267 if UPPER(p_validate) = 'TRUE' then
2268 l_validate := true;
2269 else
2270 l_validate := false;
2271 end if;
2272 --
2273 if UPPER(p_batch_flag) = 'FALSE' then
2274 l_batch_flag := false;
2275 else
2276 l_batch_flag := true;
2277 end if;
2278 --
2279 if UPPER(p_cls_enrt_flag) = 'FALSE' then
2280 l_cls_enrt_flag := false;
2281 else
2282 l_cls_enrt_flag := true;
2283 end if;
2284 --
2285
2286 -- Bug 3989075, Put row in fnd_sessions for SS processing
2287 dt_fndate.change_ses_date
2288 (p_ses_date => p_effective_date,
2289 p_commit => l_commit);
2290
2291 Default_Comp_obj
2292 (p_validate => l_validate
2293 ,p_per_in_ler_id => p_per_in_ler_id
2294 ,p_person_id => p_person_id
2295 ,p_business_group_id => p_business_group_id
2296 ,p_effective_date => p_effective_date
2297 ,p_pgm_id => p_pgm_id
2298 ,p_pl_nip_id => p_pl_nip_id
2299 ,p_susp_flag => l_suspend_flag
2300 ,p_batch_flag => l_batch_flag
2301 ,p_cls_enrt_flag => l_cls_enrt_flag
2302 ,p_called_frm_ss => TRUE
2303 );
2304 --
2305 if l_suspend_flag = true then
2306 p_susp_flag := 'TRUE';
2307 else
2308 p_susp_flag := 'FALSE';
2309 end if;
2310 hr_utility.set_location ('Leaving '|| l_proc,20);
2311
2312 exception
2313 --
2314 when app_exception.application_exception then --Bug 4387247
2315 hr_utility.set_location ('Application Error in Default_Comp_obj_w.', 88);
2316 fnd_msg_pub.add;
2317 when others then
2318 hr_utility.set_location ('Other Error in Default_Comp_obj_w : '|| sqlerrm , 89);
2319 --Bug 4387247
2320 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2321 fnd_message.set_token('2',substr(sqlerrm,1,200));
2322 fnd_msg_pub.add;
2323 end Default_Comp_obj_w;
2324 --
2325 end ben_manage_default_enrt; -- End of Package.