[Home] [Help]
PACKAGE BODY: APPS.BEN_MAINTAIN_DESIGNEE_ELIG
Source
1 package body ben_maintain_designee_elig as
2 /* $Header: bendsgel.pkb 120.10.12020000.3 2012/12/10 09:29:02 usaraswa ship $ */
3 --
4 /* ============================================================================
5 * Name
6 * Maintain Designee Eligibility
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 check to see if
11 * all designees for the person and comp object are still eligible, if
12 * not, it will update the status to "not covered" and end date the row.
13 *
14 * History
15 * Date Who Version What?
16 * ------- --------- ------- --------------------------------------
17 * 30-Nov-98 jcarpent 115.0 Created
18 * 22-Dec-98 jcarpent 115.1 Changed end date logic
19 * 03-Jan-98 Tmathers 115.2 Made compilable.
20 * 06-Jan-98 jcarpent 115.3 Added reporting.
21 * 19-Feb-99 gperry 115.4 Added call to communciations for when
22 * a dependent is being deleted.
23 * 23-Feb-99 jcarpent 115.5 Changed effective date format
24 * 03-Mar-99 stee 115.6 Removed dbms_output.put_line.
25 * 22-Mar-99 Tmathers 115.8 Changed -MON- to /MM/'
26 * 28-Apr-99 lmcdonal 115.10 prtt_enrt_rslt now has a status code.
27 * 30-apr-99 shdas 115.11 modified contexts to rule calls.
28 * 10-may-99 jcarpent 115.12 Check ('VOIDD', 'BCKDT') for pil stat
29 * 18-may-99 jcarpent 115.13 Changed delete_pdp to update_pdp,
30 * init PROC_INFO, and added commits
31 * 25-may-99 jcarpent 115.14 Changed end date handling if bendetdt
32 * returns null. Removed sub-titles
33 * for error reports to fix concurrent
34 * manager display.
35 * 05-JUL-99 stee 115.15 Create benefit assignments for
36 * dependents who become ineligible.
37 * 20-JUL-99 Gperry 115.16 genutils -> benutils package rename.
38 * 24-Aug-99 maagrawa 115.17 Dependents related changes.
39 * 30-Aug-99 maagrawa 115.18 Added parameter p_dpnt_inelig_rsn_cd
40 * to dependent eligibility process
41 * to return ineligible reason.
42 * 02-Sep-99 maagrawa 115.19 Added HIPAA communications.
43 * 07-Sep-99 tguy 115.20 fixed call to pay_mag_util
44 * 06-Oct-99 tguy 115.21 added call to dt_fndate
45 * 12-Oct-99 maagrawa 115.22 ben_env_object.init call added
46 * to do_multithread.
47 * 02-Nov-99 maagrawa 115.23 Coverage through date to be effective
48 * date if the calculated date is less
49 * than coverage start date.
50 * 03-Jan-00 maagrawa 115.24 Added pl_typ_id to comm. process.
51 * 17-Jan-00 maagrawa 115.25 Moved the HIPAA logic to bencommu
52 * and bencomde.
53 * 28-Jan-00 pbodla 115.26 - Fidelity Bug : 5445, Move the benefits
54 * assignment creation out nocopy of for loop.
55 * - Submit_all_reports is modified
56 * 17-Feb-00 jcarpent 115.27 - Pass correct elig_thru_dt to
57 * update_elig_dpnt. bug 4719
58 * 18-Feb-00 gperry 115.28 Multithread was calling wrong process.
59 * now calling BENMDSGL.
60 * WWBUG 1201093.
61 * 25-Feb-00 jcarpent 115.29 - Pass person_id to person selection rl.
62 * 14-Mar-00 maagrawa 115.30 Pass the elig_change_dt as the life event
63 * occured date to comm. process.(1230019)
64 * 31-Mar-00 maagrawa 115.31 Pass the cvg_strt_dt to dpnt.
65 * eligibilty process (4929).
66 * If found ineligible, end the eligibiltity
67 * for all the comp. objects depending on
68 * the level at which eligibilty is
69 * defined.
70 * 03-Apr-00 mmogel 115.32 - Added tokens to messages to make them
71 * more meaningful to the user
72 * 07-Jun-00 jcarpent 115.33 - Added init of l_returned_end_dt since
73 * was bleeding to other comp objects
74 * for dependent. (4728)
75 * 29-aug-01 pbodla 115.34 - bug:1949361 jurisdiction code is
76 * derived inside benutils.formula
77 * 18-dec-01 tjesumic 115.35 - cwb changes
78 * 20-dec-01 ikasire 115.36 added dbdrv lines
79 * 20-dec-01 ikasire 115.37 added commit
80 * 12-feb-02 pabodla 115.38 Bug 1579948 Fix: if g_profile_value is
81 * 'N' then do not write no designation
82 * change records into the batch log tables
83 * 04-App-02 pabodla 115.39 Bug 1579948 Fix: added if stmt to
84 * restrict a row to write into the
85 * ben_batch_dpnt_info if
86 * g_profile_value set to "No". This is
87 * done for improving the performance
88 * of audit log
89 * 30-Apr-02 kmahendr 115.40 Added write calls to capture error messages.
90 * 08-Jun-02 pabodla 115.41 Do not select the contingent worker
91 * assignment when assignment data is
92 * fetched.
93 * 11-jul-02 tjesumic 115.42 # 2455430 The coverage end date is calculated
94 * on the basis of event occured date
95 * 19-dec-2002 pabodla 115.43 NOCOPY Changes
96 * 02-Jun-2003 rpgupta 115.44 Bug 2985206
97 * Changed cursor c_person
98 * 02-Jun-2003 rpgupta 115.45 Bug 2985206
99 * Changed cursor c_person again to merge
100 * similar join conditions
101 * 02-Jun-2003 hmani 115.46 Bug 2985206
102 * Changed cursor c_person again to merge
103 * similar join conditions - Rearcsed in
104 * 27-Jul-2003 pabodla 115.47 Bug 3056894 - Added personal_flag to
105 * c_designation to consider only
106 * personal relationships.
107 * 16-Jun-2004 bmanyam 115.48 Bug 3657077 - In calc_dpnt_cvg_end_dt procedure
108 * passed the correct param (l_cvg_end_rl) ot
109 * ben_determine_date.main
110 * 05-Aug-2004 rpgupta 115.49 Bug 3808703 - c_person does not return any
111 * rows when the pl_id is specified. Its coded to
112 * use pl_id only for plans not in programs.
113 * 03-Dec-2004 ikasire 115.50 Bug 4046914
114 * 30-dec-2004 nhunur 115.51 4031733 - No need to open cursor c_state
115 * 08-Jan-2005 mmudigon115.52 4398114 - changed <= to >= in line 1836
116 * 05-May-06 rtagarra 115.53 Bug#5070692:Changed the parameter value from FALSE to TRUE so that when run the
117 Maintain Designee Eligibility warnings will come in the log file when dependent coverage
118 start and end date has date earlier than LE occured date.
119 * 09-May-06 rtagarra 115.54 Change in exception block for process_designee_elig.
120 * 26-May-06 bmanyam 115.55 5100008 - EGD elig_thru_dt is the
121 date eligibility is lost. Previously the elig_thru_dt
122 was updated with PDP cvg_thru_dt.
123 07-aug-06 ssarkar 115.56 5442301 - dont update elig_cvrd_dpnt if it is overriden
124 * 01-Dec-06 rtagarra 115.57 5662220 - Added check so that when there is no condition for a person in person_selection_rule
125 then skip the person.
126 * 05-Apr-07 rtagarra 115.58 5908080 - Continued for fix 5442301: Added date condition for c_designation.
127 * 14-Jan-07 rtagarra 115.59 6747807 - Fixed cursor c_person and c_designation
128 * 05-Feb-09 krupani 115.61 7718592 - Reverted back fix against 5908080
129 * 27-Sep-12 usaraswa 115.62 14552686 - when dependent enrollment record(other than coverage thru date) is
130 overrided through enrollment override form then dependent coverage is not ending though the dependent became ineligible.
131 10-Dec-12 usaraswa 115.63 14775822 - Modified cursor c_designation to fetch only one row though multiple rows
132 are present for dependent in per_contact_relationships table.
133 * -----------------------------------------------------------------------------
134 */
135 --
136 -- Global cursor and variables declaration
137 --
138 g_package varchar2(80) := 'Ben_maintain_designee_elig';
139 g_persons_processed number(9) := 0;
140 g_persons_ended number(9) := 0;
141 g_persons_passed number(9) := 0;
142 g_persons_errored number(9) := 0;
143 g_designations_ended number(9) := 0;
144 g_designations_remaining number(9) := 0;
145 g_max_errors_allowed number(9) := 200;
146 type g_report_rec is record
147 (
148 person_id /*per_people_f */per_all_people_f.person_id%type,
149 pgm_id ben_pgm_f.pgm_id%type,
150 pl_id ben_pl_f.pl_id%type,
151 oipl_id ben_oipl_f.oipl_id%type,
152 dpnt_cvg_strt_dt date,
153 dpnt_cvg_thru_dt date,
154 contact_type hr_lookups.lookup_code%type,
155 actn_cd hr_lookups.lookup_code%type);
156 --
157 type g_report_table is table of g_report_rec
158 index by binary_integer;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |-------------------------< calc_dpnt_cvg_end_dt >-------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 procedure calc_dpnt_cvg_end_dt(
165 p_person_id in number default NULL
166 ,p_pgm_id in number default NULL
167 ,p_pl_id in number default NULL
168 ,p_oipl_id in number default NULL
169 ,p_ptip_id in number default NULL
170 ,p_business_group_id in number
171 ,p_effective_date in date
172 ,p_returned_end_dt out nocopy date
173 ) is
174 l_proc varchar2(72) := g_package||'calc_dpnt_cvg_end_dt';
175 l_enrt_cvg_end_dt date;
176 l_cvg_strt_cd varchar2(30);
177 l_cvg_strt_rl number;
178 l_cvg_end_cd varchar2(30);
179 l_cvg_end_rl number(15);
180 l_step integer;
181 l_effective date;
182 begin
183 hr_utility.set_location(' Entering:'||l_proc,10);
184 ben_prtt_enrt_result_api.determine_dpnt_cvg_dt_cd
185 (p_pgm_id => p_pgm_id
186 ,P_ptip_id => p_ptip_id
187 ,p_pl_id => p_pl_id
188 ,p_ler_id => null
189 ,p_effective_date => p_effective_date
190 ,p_business_group_id => p_business_group_id
191 ,p_cvg_strt_cd => l_cvg_strt_cd
192 ,p_cvg_strt_rl => l_cvg_strt_rl
193 ,p_cvg_end_cd => l_cvg_end_cd
194 ,p_cvg_end_rl => l_cvg_end_rl
195 );
196 hr_utility.set_location(' cvg end dt cd :'||l_cvg_end_cd ,10);
197 hr_utility.set_location(' elig chnage dt:'||ben_evaluate_dpnt_elg_profiles.get_elig_change_dt,109);
198 -- tilak 2455430 : I dont think we need the if condition as it is
199 -- only AED and ODBED are avaialble
200 -- for end dt codes This conditon makes the AED calc wrong , get_elig_change_dt return
201 -- Date of birth -1 that is not the AED date but ODBED
202
203 --if (l_cvg_end_cd in ('AED','ODBED','AFDEM','ALDEM','AFDFEM','OMFED',
204 -- 'TMFED','30DFLED','TDBED','60DFLED','SDFED','SDBED',
205 -- 'TODFED')) then
206 -- l_enrt_cvg_end_dt:=ben_evaluate_dpnt_elg_profiles.get_elig_change_dt;
207 -- hr_utility.set_location(' cvg end dt :'||l_enrt_cvg_end_dt ,110);
208 --else
209 ben_determine_date.main
210 (P_DATE_CD => l_cvg_end_cd
211 ,p_formula_id => l_cvg_end_rl -- l_cvg_strt_rl Bug: 3657077; end-date-rule should be passed.
212 ,P_PER_IN_LER_ID => null
213 ,P_PERSON_ID => p_person_id
214 ,P_PGM_ID => p_pgm_id
215 ,P_PL_ID => p_pl_id
216 ,P_OIPL_ID => p_oipl_id
217 ,P_BUSINESS_GROUP_ID => p_business_group_id
218 ,P_EFFECTIVE_DATE => l_effective
219 ,P_LF_EVT_OCRD_DT => ben_evaluate_dpnt_elg_profiles.
220 get_elig_change_dt + 1
221 ,P_RETURNED_DATE => l_enrt_cvg_end_dt
222 );
223 hr_utility.set_location(' cvg end dt :'||l_enrt_cvg_end_dt ,111);
224 --end if;
225 if l_enrt_cvg_end_dt is null then
226 l_enrt_cvg_end_dt:=ben_evaluate_dpnt_elg_profiles.get_elig_change_dt;
227 hr_utility.set_location(' cvg end dt :'||l_enrt_cvg_end_dt ,112);
228 end if;
229 p_returned_end_dt := l_enrt_cvg_end_dt;
230 hr_utility.set_location(' Leaving:'||l_proc, 70);
231 end calc_dpnt_cvg_end_dt;
232 --
233 -- ===========================================================================
234 -- << Procedure: ben_batch_utils.batch_report >>
235 -- ===========================================================================
236 --
237 Procedure Submit_all_reports (p_rpt_flag Boolean default FALSE) is
238 l_proc varchar2(80) := g_package||'.submit_all_reports';
239 l_actn varchar2(80);
240 l_request_id number;
241 Begin
242 hr_utility.set_location ('Entering '||l_proc,05);
243 l_actn := 'Calling ben_batch_utils.batch_report (BENDEAUD)...';
244 --
245 If fnd_global.conc_request_id <> -1 then
246 --
247 ben_batch_utils.batch_report
248 (p_concurrent_request_id => fnd_global.conc_request_id
249 ,p_program_name => 'BENDEAUD'
250 ,p_request_id => l_request_id
251 );
252 l_actn := 'Calling ben_batch_utils.batch_report (BENDESUM)...';
253 ben_batch_utils.batch_report
254 (p_concurrent_request_id => fnd_global.conc_request_id
255 ,p_program_name => 'BENDESUM'
256 ,p_request_id => l_request_id
257 );
258 l_actn := 'Calling ben_batch_utils.batch_report (BENERTYP)...';
259 ben_batch_utils.batch_report
260 (p_concurrent_request_id => fnd_global.conc_request_id
261 ,p_program_name => 'BENERTYP'
262 ,p_request_id => l_request_id
263 -- ,p_subtitle =>
264 -- 'MANAGE DEFAULT ENROLLMENTS - ERROR DETAIL BY ERROR TYPE'
265 );
266 l_actn := 'Calling ben_batch_utils.batch_report (BENERPER)...';
267 ben_batch_utils.batch_report
268 (p_concurrent_request_id => fnd_global.conc_request_id
269 ,p_program_name => 'BENERPER'
270 ,p_request_id => l_request_id
271 -- ,p_subtitle =>
272 -- 'MANAGE DEFAULT ENROLLMENTS - ERROR DETAIL BY PERSON'
273 );
274 end if;
275 hr_utility.set_location ('Leaving '||l_proc,10);
276 Exception
277 When others then
278 ben_batch_utils.rpt_error(p_proc => l_proc
279 ,p_last_actn => l_actn
280 ,p_rpt_flag => p_rpt_flag
281 );
282 raise;
283 End Submit_all_reports;
284 --
285 -- ============================================================================
286 -- << Procedure: Do_Multithread >>
287 -- Description:
288 -- this is a main procedure to invoke the maintain designee elig process.
289 -- ============================================================================
290 procedure do_multithread
291 (errbuf out nocopy varchar2
292 ,retcode out nocopy number
293 ,p_validate in varchar2 default 'N'
294 ,p_benefit_action_id in number
295 ,p_thread_id in number
296 ,p_effective_date in varchar2
297 ,p_business_group_id in number
298 ) is
299 --
300 -- Local variable declaration
301 --
302 l_proc varchar2(80) := g_package||'.do_multithread';
303 l_person_id ben_person_actions.person_id%type;
304 l_person_action_id ben_person_actions.person_action_id%type;
305 l_object_version_number ben_person_actions.object_version_number%type;
306 l_ler_id ben_person_actions.ler_id%type;
307 l_range_id ben_batch_ranges.range_id%type;
308 l_record_number number := 0;
309 l_start_person_action_id number := 0;
310 l_end_person_action_id number := 0;
311 l_actn varchar2(80);
312 l_cnt number(5):= 0;
313 l_chunk_size number(15);
314 l_threads number(15);
315 l_effective_date date;
316 --
317 -- Cursors declaration
318 --
319 Cursor c_range_thread is
320 Select ran.range_id
321 ,ran.starting_person_action_id
322 ,ran.ending_person_action_id
323 From ben_batch_ranges ran
324 Where ran.range_status_cd = 'U'
325 And ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
326 And rownum < 2
327 For update of ran.range_status_cd
328 ;
329 Cursor c_person_thread is
330 Select ben.person_id
331 ,ben.person_action_id
332 ,ben.object_version_number
333 ,ben.ler_id
334 From ben_person_actions ben
335 Where ben.benefit_action_id = p_benefit_action_id
336 And ben.action_status_cd <> 'P'
337 And ben.person_action_id between
338 l_start_person_action_id and l_end_person_action_id
339 Order by ben.person_action_id
340 ;
341 Cursor c_parameter is
342 Select *
343 From ben_benefit_actions ben
344 Where ben.benefit_action_id = p_benefit_action_id
345 ;
346 l_parm c_parameter%rowtype;
347 l_commit number;
348 --
349 Begin
350 hr_utility.set_location ('Entering '||l_proc,05);
351 --
352 /*
353 l_effective_date:=to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
354 l_effective_date:=to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR'),'DD/MM/RRRR');
355 */
356 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
357 --
358 -- Put row in fnd_sessions
359 --
360 dt_fndate.change_ses_date
361 (p_ses_date => l_effective_date,
362 p_commit => l_commit);
363 --
364 l_actn := 'Calling benutils.get_parameter...';
365 benutils.get_parameter(p_business_group_id => p_business_group_Id
366 ,p_batch_exe_cd => 'BENDSGEL'
367 ,p_threads => l_threads
368 ,p_chunk_size => l_chunk_size
369 ,p_max_errors => g_max_errors_allowed);
370 --
371 -- Set up benefits environment
372 --
373 ben_env_object.init(p_business_group_id => p_business_group_id,
374 p_effective_date => l_effective_date,
375 p_thread_id => p_thread_id,
376 p_chunk_size => l_chunk_size,
377 p_threads => l_threads,
378 p_max_errors => g_max_errors_allowed,
379 p_benefit_action_id => p_benefit_action_id);
380 --
381 l_actn := 'Calling ben_batch_utils.ini...';
382 ben_batch_utils.ini;
383 --
384 -- Copy benefit action id to global in benutils package
385 --
386 benutils.g_benefit_action_id := p_benefit_action_id;
387 benutils.g_thread_id := p_thread_id;
388 g_persons_errored := 0;
389 g_persons_processed := 0;
390 open c_parameter;
391 fetch c_parameter into l_parm;
392 close c_parameter;
393 --
394 l_actn := 'Calling ben_batch_utils.print_parameters...';
395 --
396 ben_batch_utils.print_parameters
397 (p_thread_id => p_thread_id
398 ,p_benefit_action_id => p_benefit_action_id
399 ,p_validate => p_validate
400 ,p_business_group_id => p_business_group_id
401 ,p_effective_date => l_effective_date
402 ,p_person_id => l_parm.person_id
403 ,p_person_selection_rule_id => l_parm.person_selection_rl
404 ,p_comp_selection_rule_id => l_parm.comp_selection_rl
405 ,p_pgm_id => l_parm.pgm_id
406 ,p_pl_id => l_parm.pl_id
407 ,p_person_type_id => l_parm.person_type_id
408 ,p_ler_id => null
409 ,p_organization_id => l_parm.organization_id
410 ,p_benfts_grp_id => l_parm.benfts_grp_id
411 ,p_location_id => l_parm.location_id
412 ,p_legal_entity_id => l_parm.legal_entity_id
413 ,p_payroll_id => l_parm.payroll_id
414 );
415 --
416 -- While loop to only try and fetch records while they exist
417 -- we always try and fetch the size of the chunk, if we get less
418 -- then we know that the process is finished so we end the while loop.
419 -- The process is as follows :
420 -- 1) Lock the rows that are not processed
421 -- 2) Grab as many rows as we can upto the chunk size
422 -- 3) Put each row into the person cache.
423 -- 4) Process the person cache
424 -- 5) Go to number 1 again.
425 --
426 Loop
427 l_actn := 'Opening c_range thread and fetch range...';
428 open c_range_thread;
429 fetch c_range_thread into l_range_id
430 ,l_start_person_action_id
431 ,l_end_person_action_id;
432 exit when c_range_thread%notfound;
433 close c_range_thread;
434 If(l_range_id is not NULL) then
435 --
436 l_actn := 'Updating ben_batch_ranges row...';
437 --
438 update ben_batch_ranges ran set ran.range_status_cd = 'P'
439 where ran.range_id = l_range_id;
440 commit;
441 End if;
442 --
443 -- Remove all records from cache
444 --
445 l_actn := 'Clearing g_cache_person_process cache...';
446 g_cache_person_process.delete;
447 open c_person_thread;
448 l_record_number := 0;
449 Loop
450 --
451 l_actn := 'Loading person data into g_cache_person_process cache...';
452 --
453 fetch c_person_thread
454 into g_cache_person_process(l_record_number+1).person_id
455 ,g_cache_person_process(l_record_number+1).person_action_id
456 ,g_cache_person_process(l_record_number+1).object_version_number
457 ,g_cache_person_process(l_record_number+1).ler_id;
458 exit when c_person_thread%notfound;
459 l_record_number := l_record_number + 1;
460 End loop;
461 close c_person_thread;
462 --
463 l_actn := 'Preparing to default each participant from cache...' ;
464 --
465 If l_record_number > 0 then
466 --
467 -- Process the rows from the person process cache
468 --
469 For l_cnt in 1..l_record_number loop
470 Begin
471 ben_maintain_designee_elig.process_designee_elig
472 (p_validate => p_validate
473 ,p_person_id => g_cache_person_process(l_cnt).person_id
474 ,p_person_action_id => g_cache_person_process(l_cnt).person_action_id
475 ,p_comp_selection_rl => l_parm.comp_selection_rl
476 ,p_pgm_id => l_parm.pgm_id
477 ,p_pl_id => l_parm.pl_id
478 ,p_object_version_number => g_cache_person_process(l_cnt).object_version_number
479 ,p_business_group_id => p_business_group_id
480 ,p_effective_date => l_effective_date
481 );
482 Exception
483 When others then
484 If (g_persons_errored > g_max_errors_allowed) then
485 fnd_message.raise_error;
486 End if;
487 End;
488 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE); --Bug 5070692
489 End loop;
490 Else
491 --
492 l_actn := 'Erroring out nocopy since not person is found in range...' ;
493 --
494 fnd_message.set_name('BEN','BEN_91709_PER_NOT_FND_IN_RNG');
495 fnd_message.set_token('PROCEDURE', l_proc);
496 fnd_message.raise_error;
497 End if;
498 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE); --Bug 5070692
499 End loop;
500 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE); -- Bug5070692
501 --
502 l_actn := 'Calling Log_statistics...';
503 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
504 ,p_num_pers_errored => g_persons_errored
505 );
506 hr_utility.set_location ('Leaving '||l_proc,70);
507 Exception
508 When others then
509 ben_batch_utils.rpt_error(p_proc => l_proc
510 ,p_last_actn => l_actn
511 ,p_rpt_flag => TRUE
512 );
513 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
514 ,p_num_pers_errored => g_persons_errored
515 );
516 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
517 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
518 fnd_message.set_token('PROCEDURE', l_proc);
519 fnd_message.set_token('STEP',l_actn );
520 fnd_message.raise_error;
521 End do_multithread;
522 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
523 -- << Procedure: Restart >>
524 -- *****************************************************************
525 --
526 procedure restart (errbuf out nocopy varchar2
527 ,retcode out nocopy number
528 ,p_benefit_action_id in number
529 ) is
530 --
531 -- Cursor Declaration
532 --
533 cursor c_parameters is
534 Select process_date
535 ,mode_cd
536 ,validate_flag
537 ,person_id
538 ,person_type_id
539 ,pgm_id
540 ,business_group_id
541 ,pl_id
542 ,popl_enrt_typ_cycl_id
543 ,person_selection_rl
544 ,comp_selection_rl
545 ,ler_id
546 ,organization_id
547 ,benfts_grp_id
548 ,location_id
549 ,legal_entity_id
550 ,payroll_id
551 ,debug_messages_flag
552 From ben_benefit_actions ben
553 Where ben.benefit_action_id = p_benefit_action_id;
554 --
555 -- Local Variable declaration.
556 --
557 l_proc varchar2(80) := g_package||'.restart';
558 l_parameters c_parameters%rowtype;
559 l_errbuf varchar2(80);
560 l_retcode number;
561 l_actn varchar2(80);
562 Begin
563 hr_utility.set_location ('Entering '||l_proc,10);
564 --
565 -- get the parameters for a previous run and do a restart
566 --
567 l_actn := 'Getting parameter data...';
568 open c_parameters;
569 fetch c_parameters into l_parameters;
570 If c_parameters%notfound then
571 ben_batch_utils.rpt_error(p_proc => l_proc
572 ,p_last_actn => l_actn
573 ,p_rpt_flag => TRUE
574 );
575 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
576 fnd_message.set_token('PROC',l_proc);
577 fnd_message.raise_error;
578 End if;
579 close c_parameters;
580 --
581 -- Call process procedure with parameters for restart
582 --
583 l_actn := 'Calling process...';
584 Process (errbuf => l_errbuf
585 ,retcode => l_retcode
586 ,p_benefit_action_id => p_benefit_action_id
587 ,p_effective_date => l_parameters.process_date
588 ,p_validate => l_parameters.validate_flag
589 ,p_person_id => l_parameters.person_id
590 ,p_person_type_id => l_parameters.person_type_id
591 ,p_business_group_id => l_parameters.business_group_id
592 ,p_person_selection_rule_id => l_parameters.person_selection_rl
593 ,p_comp_selection_rule_id => l_parameters.comp_selection_rl
594 ,p_pgm_id => l_parameters.pgm_id
595 ,p_pl_id => l_parameters.pl_id
596 ,p_organization_id => l_parameters.organization_id
597 ,p_benfts_grp_id => l_parameters.benfts_grp_id
598 ,p_location_id => l_parameters.location_id
599 ,p_legal_entity_id => l_parameters.legal_entity_id
600 ,p_payroll_id => l_parameters.payroll_id
601 ,p_debug_messages => l_parameters.debug_messages_flag
602 );
603 hr_utility.set_location ('Leaving '||l_proc,70);
604 Exception
605 when others then
606 ben_batch_utils.rpt_error(p_proc => l_proc
607 ,p_last_actn => l_actn
608 ,p_rpt_flag => TRUE
609 );
610 raise;
611 end restart;
612 --
613 -- *************************************************************************
614 -- * << Procedure: Process >>
615 -- *************************************************************************
616 --
617 procedure process(errbuf out nocopy varchar2
618 ,retcode out nocopy number
619 ,p_benefit_action_id in number
620 ,p_effective_date in varchar2
621 ,p_validate in varchar2 default 'N'
622 ,p_person_id in number default null
623 ,p_person_type_id in number default null
624 ,p_business_group_id in number
625 ,p_person_selection_rule_id in number default null
626 ,p_comp_selection_rule_id in number default null
627 ,p_pgm_id in number default null
628 ,p_pl_id in number default null
629 ,p_organization_id in number default null
630 ,p_benfts_grp_id in number default null
631 ,p_location_id in number default null
632 ,p_legal_entity_id in number default null
633 ,p_payroll_id in number default null
634 ,p_debug_messages in varchar2 default 'N'
635 ) is
636 l_effective_date date;
637 l_part_person_id number;
638 --
639 -- Cursors declaration.
640 --
641 cursor c_person is
642 Select distinct ecd.dpnt_person_id, pil.person_id
643 From ben_prtt_enrt_rslt_f pen,
644 ben_elig_cvrd_dpnt_f ecd,
645 ben_per_in_ler pil
646 Where pen.business_group_id = p_business_group_id
647 and pen.prtt_enrt_rslt_stat_cd is null
648 and ( p_pgm_id is null or pen.pgm_id=p_pgm_id)
649 and ( p_pl_id is null or pen.pl_id = p_pl_id)--3808703
650 --(pen.pl_id=p_pl_id and pen.pgm_id is null))
651 and l_effective_date between
652 pen.effective_start_date and pen.effective_end_date
653 and l_effective_date between
654 pen.enrt_cvg_strt_dt and nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot)
655 and ecd.prtt_enrt_rslt_id=pen.prtt_enrt_rslt_id
656 and (nvl(ecd.ovrdn_flag,'N')='N' or
657 nvl(ecd.ovrdn_thru_dt,l_effective_date)>=l_effective_date)
658 and ecd.business_group_id = pen.business_group_id
659 and l_effective_date between
660 ecd.effective_start_date and ecd.effective_end_date
661 and l_effective_date between
662 ecd.cvg_strt_dt and ecd.cvg_thru_dt
663 and (p_person_id is NULL or pen.person_id = P_person_id)
664 and (p_person_type_id is null
665 or exists (select null
666 from per_person_type_usages ptu
667 where ptu.person_id = pen.person_id
668 and ptu.person_type_id = P_person_type_id
669 )
670 )
671 /* bug 2985206 */
672 /* and ( (p_location_id is null and p_organization_id is null
673 and p_legal_entity_id is null and p_payroll_id is null
674 ) or exists (select null
675 from per_assignments_f asg
676 where nvl(asg.location_id,hr_api.g_number) =
677 nvl(p_location_id,hr_api.g_number)
678 and nvl(asg.organization_id,hr_api.g_number) =
679 nvl(p_organization_id,hr_api.g_number)
680 and nvl(asg.soft_coding_keyflex_id,
681 hr_api.g_number) =
682 nvl(p_legal_entity_id,hr_api.g_number)
683 and nvl(asg.payroll_id, hr_api.g_number)=
684 nvl(p_payroll_id,hr_api.g_number)
685 and asg.person_id = pen.person_id
686 and asg.assignment_type <> 'C'
687 and asg.primary_flag = 'Y'
688 and asg.business_group_id =
689 pen.business_group_id
690 and l_effective_date between
691 asg.effective_start_date and
692 asg.effective_end_date
693 )
694 )
695 */
696 and (
697 (p_location_id is null and p_organization_id is null
698 and p_payroll_id is null
699 )
700 or exists (select null
701 from per_assignments_f asg
702 where nvl(asg.location_id,hr_api.g_number) =
703 nvl(p_location_id,nvl(asg.location_id,hr_api.g_number))
704 and nvl(asg.organization_id,hr_api.g_number) =
705 nvl(p_organization_id,nvl(asg.organization_id,hr_api.g_number))
706 and nvl(asg.payroll_id,hr_api.g_number) =
707 nvl(p_payroll_id,nvl(asg.payroll_id,hr_api.g_number))
708 and asg.person_id = pen.person_id
709 and asg.assignment_type <> 'C'
710 and asg.primary_flag = 'Y'
711 and asg.business_group_id =
712 pen.business_group_id
713 and l_effective_date between
714 asg.effective_start_date and
715 asg.effective_end_date
716 )
717 )
718 and (p_legal_entity_id is null
719 or exists (select null
720 from hr_soft_coding_keyflex hsc,
721 per_all_assignments_f asg
722 where asg.person_id = pil.person_id
723 and asg.assignment_type <> 'C'
724 and asg.primary_flag = 'Y'
725 and asg.business_group_id = pil.business_group_id
726 and l_effective_date
727 between asg.effective_start_date and asg.effective_end_date
728 and asg.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
729 and hsc.segment1 = to_char(p_legal_entity_id)
730 )
731 )
732 /* end bug 2985206 */
733 and (p_benfts_grp_id is null
734 or exists(select null
735 from ben_benfts_grp bng
736 ,/*per_people_f*/ per_all_people_f ppf
737 where bng.benfts_grp_id = p_benfts_grp_id
738 and bng.business_group_id = pen.business_group_id
739 and ppf.person_id = pen.person_id
740 and ppf.benefit_group_id = bng.benfts_grp_id
741 and l_effective_date between
742 ppf.effective_start_date and
743 ppf.effective_end_date
744 )
745 )
746 and pil.per_in_ler_id=ecd.per_in_ler_id
747 --and pil.business_group_id=ecd.business_group_id
748 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
749 --and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
750 and pen.prtt_enrt_rslt_stat_cd is null
751 ;
752 --
753 -- local variable declaration.
754 --
755 l_request_id number;
756 l_proc varchar2(80) := g_package||'.process';
757 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
758 l_object_version_number ben_benefit_actions.object_version_number%type;
759 l_person_id /*per_people_f*/ per_all_people_f.person_id%type;
760 l_person_action_id ben_person_actions.person_action_id%type;
761 l_ler_id ben_ler_f.ler_id%type;
762 l_range_id ben_batch_ranges.range_id%type;
763 l_chunk_size number := 20;
764 l_threads number := 1;
765 l_start_person_action_id number := 0;
766 l_end_person_action_id number := 0;
767 l_prev_person_id number := 0;
768 rl_ret char(1);
769 skip boolean;
770 l_person_cnt number := 0;
771 l_cnt number := 0;
772 l_actn varchar2(80);
773 l_num_range number := 0;
774 l_chunk_num number := 1;
775 l_num_row number := 0;
776 l_commit number;
777 --
778 Begin
779
780 hr_utility.set_location ('Entering '||l_proc,10);
781
782 --
783 /*
784 l_effective_date:=to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
785 l_effective_date:=to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR'),'DD/MM/RRRR');
786 */
787 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
788 --
789 -- Put row in fnd_sessions
790 --
791 dt_fndate.change_ses_date
792 (p_ses_date => l_effective_date,
793 p_commit => l_commit);
794 --
795 l_actn := 'Initialize the ben_batch_utils cache...';
796 --
797 ben_batch_utils.ini;
798 --
799 l_actn := 'Initialize the ben_batch_utils cache...';
800 --
801 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
802 --
803 -- Check that all the mandatory input parameters
804 -- such as p_business_group_id, p_mode, p_effective_date
805 --
806 l_actn := 'Checking arguments...';
807 hr_api.mandatory_arg_error(p_api_name => g_package
808 ,p_argument => 'p_business_group_id'
809 ,p_argument_value => p_business_group_id
810 );
811 hr_api.mandatory_arg_error(p_api_name => g_package
812 ,p_argument => 'p_effective_date'
813 ,p_argument_value => p_effective_date
814 );
815 --
816 -- Get chunk_size and Thread values for multi-thread process, and check to
817 -- assure they are sensible.
818 -- chunk_size between(10 and 100). If not in range, default to 20.
819 -- threads between <1 and 100>. If not in range, default to 1
820 --
821 l_actn := 'Calling benutils.get_parameter...';
822 benutils.get_parameter(p_business_group_id => p_business_group_Id
823 ,p_batch_exe_cd => 'BENDSGEL'
824 ,p_threads => l_threads
825 ,p_chunk_size => l_chunk_size
826 ,p_max_errors => g_max_errors_allowed);
827 benutils.g_benefit_action_id := p_benefit_action_id;
828 benutils.g_thread_id := 99;
829 --
830 -- Create benefit actions parameters in the benefit action table.
831 -- Do not create is a benefit action already exists, in other words
832 -- we are doing a restart.
833 --
834 If(p_benefit_action_id is null) then
835 ben_benefit_actions_api.create_benefit_actions
836 (p_validate => false
837 ,p_benefit_action_id => l_benefit_action_id
838 ,p_process_date => l_effective_date
839 ,p_mode_cd => 'S'
840 ,p_derivable_factors_flag => 'N'
841 ,p_validate_flag => p_validate
842 ,p_person_id => p_person_id
843 ,p_person_type_id => p_person_type_id
844 ,p_pgm_id => p_pgm_id
845 ,p_business_group_id => p_business_group_id
846 ,p_pl_id => p_pl_id
847 ,p_popl_enrt_typ_cycl_id => null
848 ,p_no_programs_flag => 'N'
849 ,p_no_plans_flag => 'N'
850 ,p_comp_selection_rl => p_comp_selection_rule_id
851 ,p_person_selection_rl => p_person_selection_rule_id
852 ,p_ler_id => null
853 ,p_organization_id => p_organization_id
854 ,p_benfts_grp_id => p_benfts_grp_id
855 ,p_location_id => p_location_id
856 ,p_pstl_zip_rng_id => NULL
857 ,p_rptg_grp_id => NULL
858 ,p_pl_typ_id => NULL
859 ,p_opt_id => NULL
860 ,p_eligy_prfl_id => NULL
861 ,p_vrbl_rt_prfl_id => NULL
862 ,p_legal_entity_id => p_legal_entity_id
863 ,p_payroll_id => p_payroll_id
864 ,p_debug_messages_flag => p_debug_messages
865 ,p_object_version_number => l_object_version_number
866 ,p_effective_date => l_effective_date
867 ,p_request_id => fnd_global.conc_request_id
868 ,p_program_application_id => fnd_global.prog_appl_id
869 ,p_program_id => fnd_global.conc_program_id
870 ,p_program_update_date => sysdate
871 );
872 benutils.g_benefit_action_id := l_benefit_action_id;
873 --
874 -- Delete/clear ranges from ben_batch_ranges table
875 --
876 l_actn := 'Delete rows from ben_batch_ranges..';
877 Delete from ben_batch_ranges
878 Where benefit_action_id = l_benefit_action_id;
879 --
880 -- Now lets create person actions for all the people we are going to
881 -- process in the BENEADEB run.
882 --
883 open c_person;
884 l_person_cnt := 0;
885 l_cnt := 0;
886 l_actn := 'Loading person_actions table..';
887 Loop
888 fetch c_person into l_person_id,l_part_person_id;
889 Exit when c_person%notfound;
890 l_cnt := l_cnt + 1;
891 l_actn := 'Calling ben_batch_utils.person_selection_rule...';
892 hr_utility.set_location('person_id='||to_char(l_person_id),999);
893 --
894 -- if p_person_selection_rule_id is pass, test rule.
895 -- If the rule return 'N' then
896 -- Bug 5662220: If the rule doesnt return 'Y' or 'N' then also skip the person
897 -- skip that person_id.
898 --
899 skip := FALSE;
900 If (p_person_selection_rule_id is not NULL) then
901 l_actn := 'Calling Ben_batch_utils.person_selection_rule...';
902 rl_ret := ben_batch_utils.person_selection_rule
903 (p_person_id => l_part_person_id
904 ,p_business_group_id => p_business_group_id
905 ,p_person_selection_rule_id=> p_person_selection_rule_id
906 ,p_effective_date => l_effective_date
907 );
908 -- If (rl_ret = 'N') then
909 If (rl_ret <> 'Y') then --Bug 5662220
910 skip := TRUE;
911 End if;
912 End if;
913 --
914 -- Store person_id into person actions table.
915 --
916 If ( not skip) then
917 l_actn := 'Calling Ben_person_actions_api.create_person_actions...';
918 Ben_person_actions_api.create_person_actions
919 (p_validate => false
920 ,p_person_action_id => l_person_action_id
921 ,p_person_id => l_person_id
922 ,p_ler_id => 0
923 ,p_benefit_action_id => l_benefit_action_id
924 ,p_action_status_cd => 'U'
925 ,p_chunk_number => l_chunk_num
926 ,p_object_version_number => l_object_version_number
927 ,p_effective_date => l_effective_date
928 );
929 l_num_row := l_num_row + 1;
930 l_person_cnt := l_person_cnt + 1;
931 l_end_person_action_id := l_person_action_id;
932 If l_num_row = 1 then
933 l_start_person_action_id := l_person_action_id;
934 End if;
935 If l_num_row = l_chunk_size then
936 --
937 -- Create a range of data to be multithreaded.
938 --
939 l_actn := 'Calling Ben_batch_ranges_api.create_batch_ranges(in)...';
940 Ben_batch_ranges_api.create_batch_ranges
941 (p_validate => false
942 ,p_benefit_action_id => l_benefit_action_id
943 ,p_range_id => l_range_id
944 ,p_range_status_cd => 'U'
945 ,p_starting_person_action_id => l_start_person_action_id
946 ,p_ending_person_action_id => l_end_person_action_id
947 ,p_object_version_number => l_object_version_number
948 ,p_effective_date => l_effective_date
949 );
950 l_start_person_action_id := 0;
951 l_end_person_action_id := 0;
952 l_num_row := 0;
953 l_num_range := l_num_range + 1;
954 l_chunk_num := l_chunk_num + 1;
955 End if;
956 End if;
957 End loop;
958 Close c_person;
959 If (l_num_row <> 0) then
960 l_actn := 'Calling Ben_batch_ranges_api.create_batch_ranges(Last)...';
961 Ben_batch_ranges_api.create_batch_ranges
962 (p_validate => false
963 ,p_benefit_action_id => l_benefit_action_id
964 ,p_range_id => l_range_id
965 ,p_range_status_cd => 'U'
966 ,p_starting_person_action_id => l_start_person_action_id
967 ,p_ending_person_action_id => l_end_person_action_id
968 ,p_object_version_number => l_object_version_number
969 ,p_effective_date => l_effective_date
970 );
971 l_num_range := l_num_range + 1;
972 End if;
973 Else
974 l_benefit_action_id := p_benefit_action_id;
975 l_actn := 'Calling Ben_batch_utils.create_restart_person_actions...';
976 Ben_batch_utils.create_restart_person_actions
977 (p_benefit_action_id => p_benefit_action_id
978 ,p_effective_date => l_effective_date
979 ,p_chunk_size => l_chunk_size
980 ,p_threads => l_threads
981 ,p_num_ranges => l_num_range
982 ,p_num_persons => l_person_cnt
983 );
984 End if;
985 commit;
986 --
987 -- Now to multithread the code.
988 --
989 If l_num_range > 1 then
990 For l_count in 1..least(l_threads,l_num_range)-1 loop
991 --
992 l_actn := 'Submitting job to con-current manager...';
993 l_request_id := fnd_request.submit_request
994 (application => 'BEN'
995 ,program => 'BENMDSGL'
996 ,description => NULL
997 ,sub_request => FALSE
998 ,argument1 => p_validate
999 ,argument2 => l_benefit_action_id
1000 ,argument3 => l_count
1001 ,argument4 => p_effective_date
1002 ,argument5 => p_business_group_id
1003 );
1004 --
1005 -- Store the request id of the concurrent request
1006 --
1007 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
1008 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
1009 := l_request_id;
1010 End loop;
1011 Elsif (l_num_range = 0 ) then
1012 l_actn := 'Calling Ben_batch_utils.print_parameters...';
1013 Ben_batch_utils.print_parameters
1014 (p_thread_id => 99
1015 ,p_benefit_action_id => l_benefit_action_id
1016 ,p_validate => p_validate
1017 ,p_business_group_id => p_business_group_id
1018 ,p_effective_date => l_effective_date
1019 ,p_mode => null
1020 ,p_comp_selection_rule_id => p_comp_selection_rule_id
1021 ,p_pgm_id => p_pgm_id
1022 ,p_pl_id => p_pl_id
1023 ,p_popl_enrt_typ_cycl_id => null
1024 ,p_person_id => p_person_id
1025 ,p_person_selection_rule_id => p_person_selection_rule_id
1026 ,p_person_type_id => p_person_type_id
1027 ,p_ler_id => null
1028 ,p_organization_id => p_organization_id
1029 ,p_benfts_grp_id => p_benfts_grp_id
1030 ,p_location_id => p_location_id
1031 ,p_legal_entity_id => p_legal_entity_id
1032 ,p_payroll_id => p_payroll_id
1033 );
1034 Ben_batch_utils.write(p_text =>
1035 '<< No Person got selected with above selection criteria >>' );
1036 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
1037 fnd_message.set_token('PROC',l_proc);
1038 fnd_message.raise_error;
1039 End if;
1040 --
1041 l_actn := 'Calling do_multithread...';
1042 do_multithread(errbuf => errbuf
1043 ,retcode => retcode
1044 ,p_validate => p_validate
1045 ,p_benefit_action_id => l_benefit_action_id
1046 ,p_thread_id => l_threads+1
1047 ,p_effective_date => p_effective_date
1048 ,p_business_group_id => p_business_group_id
1049 );
1050 --
1051 l_actn := 'Calling ben_batch_utils.check_all_slaves_finished...';
1052 --
1053 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1054 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1055 ,p_person_selected => l_person_cnt
1056 ,p_business_group_id => p_business_group_id);
1057 submit_all_reports;
1058 hr_utility.set_location ('Leaving '||l_proc,70);
1059 --
1060 Exception
1061 when others then
1062 ben_batch_utils.rpt_error(p_proc => l_proc
1063 ,p_last_actn => l_actn
1064 ,p_rpt_flag => TRUE );
1065 benutils.write(p_text => fnd_message.get);
1066 benutils.write(p_text => sqlerrm);
1067 -- benutils.write(p_text => 'Big Error Occured');
1068 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1069 If (l_num_range > 0) then
1070 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1071 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1072 ,p_person_selected => l_person_cnt
1073 ,p_business_group_id => p_business_group_id
1074 ) ;
1075 -- submit_all_reports;
1076 End if;
1077 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1078 fnd_message.set_token('PROCEDURE', l_proc);
1079 fnd_message.set_token('STEP', l_actn );
1080 fnd_message.raise_error;
1081 End process;
1082 --
1083 -- ============================================================================
1084 -- << comp_selection_Rule >>
1085 -- ============================================================================
1086 --
1087 function comp_selection_rule
1088 (p_person_id in number
1089 ,p_business_group_id in number
1090 ,p_pgm_id in number
1091 ,p_pl_id in number
1092 ,p_pl_typ_id in number
1093 ,p_opt_id in number
1094 ,p_ler_id in number
1095 ,p_oipl_id in number
1096 ,p_comp_selection_rule_id in number
1097 ,p_effective_date in date
1098 ) return char is
1099 cursor c1 is
1100 select assignment_id,organization_id
1101 from per_assignments_f paf
1102 where paf.person_id = p_person_id
1103 and paf.assignment_type <> 'C'
1104 and paf.primary_flag = 'Y'
1105 and paf.business_group_id = p_business_group_id
1106 and p_effective_date between
1107 paf.effective_start_date and paf.effective_end_date;
1108
1109 Cursor c_state is
1110 select region_2
1111 from hr_locations_all loc,per_all_assignments_f asg
1112 where loc.location_id = asg.location_id
1113 and asg.person_id = p_person_id
1114 and asg.primary_flag = 'Y'
1115 and p_effective_date between
1116 asg.effective_start_date and asg.effective_end_date
1117 and asg.business_group_id=p_business_group_id;
1118
1119 l_jurisdiction_code varchar2(30);
1120 l_state c_state%rowtype;
1121 l_proc varchar2(80) := g_package||'.person_selection_rule';
1122 l_outputs ff_exec.outputs_t;
1123 l_return varchar2(30);
1124 l_assignment_id number;
1125 l_organization_id number;
1126 l_step integer;
1127 begin
1128 l_step := 10;
1129 hr_utility.set_location ('Entering '||l_proc,10);
1130 --
1131 -- Get assignment ID,organization_id form per_assignments_f table.
1132 --
1133 open c1;
1134 fetch c1 into l_assignment_id,l_organization_id;
1135 if c1%notfound then
1136 ben_batch_utils.rpt_error(p_proc => l_proc,
1137 p_last_actn => 'Step = '||to_char(l_step),p_rpt_flag => TRUE);
1138 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
1139 fnd_message.set_token('PROC',l_proc);
1140 fnd_message.set_token('ID' , to_char(p_person_id));
1141 raise ben_maintain_designee_elig.g_record_error;
1142 end if;
1143 close c1;
1144 l_step := 20;
1145 --
1146
1147 /* -- 4031733 - Cursor c_state populates l_state variable which is no longer
1148 -- used in the package. Cursor can be commented
1149
1150 if p_person_id is not null then
1151 open c_state;
1152 fetch c_state into l_state;
1153 close c_state;
1154
1155 if l_state.region_2 is not null then
1156 l_jurisdiction_code :=
1157 pay_mag_utils.lookup_jurisdiction_code
1158 (p_state => l_state.region_2);
1159 end if;
1160 end if;
1161 */
1162 -- Call formula initialise routine
1163 --
1164 l_outputs := benutils.formula
1165 (p_formula_id => p_comp_selection_rule_id
1166 ,p_effective_date => p_effective_date
1167 ,p_pgm_id => p_pgm_id
1168 ,p_pl_id => p_pl_id
1169 ,p_pl_typ_id => p_pl_typ_id
1170 ,p_opt_id => p_opt_id
1171 ,p_ler_id => p_ler_id
1172 ,p_business_group_id => p_business_group_id
1173 ,p_assignment_id => l_assignment_id
1174 ,p_organization_id => l_organization_id
1175 ,p_jurisdiction_code => l_jurisdiction_code);
1176
1177 l_return := l_outputs(l_outputs.first).value;
1178 l_step := 30;
1179 if upper(l_return) not in ('Y', 'N') then
1180 --
1181 -- Defensive coding for Non Y return
1182 --
1183 ben_batch_utils.rpt_error(p_proc => l_proc,
1184 p_last_actn => 'Step = '||to_char(l_step),p_rpt_flag => TRUE);
1185 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
1186 fnd_message.set_token('RL','formula_id :'||p_comp_selection_rule_id);
1187 fnd_message.set_token('PROC',l_proc);
1188 raise ben_maintain_designee_elig.g_record_error;
1189 end if;
1190 return l_return;
1191 hr_utility.set_location ('Leaving '||l_proc,10);
1192 exception
1193 when others then
1194 ben_batch_utils.rpt_error(p_proc => l_proc,
1195 p_last_actn => 'Step = '||to_char(l_step),p_rpt_flag => TRUE);
1196 raise;
1197 end comp_selection_rule;
1198 --
1199 -- ============================================================================
1200 -- << Procedure: process_designee_elig >>
1201 -- ============================================================================
1202 --
1203 Procedure Process_designee_elig
1204 (p_validate in varchar2 default 'N'
1205 ,p_person_id in number default null
1206 ,p_person_action_id in number default null
1207 ,p_comp_selection_rl in number
1208 ,p_pgm_id in number
1209 ,p_pl_id in number
1210 ,p_object_version_number in out nocopy number
1211 ,p_business_group_id in number
1212 ,p_effective_date in date
1213 ) is
1214 --
1215 -- Local Cursor
1216 --
1217 cursor c_designation is
1218 Select pen.pgm_id,
1219 pen.pl_id,
1220 pen.pl_typ_id,
1221 pen.oipl_id,
1222 pen.person_id,
1223 pen.ptip_id,
1224 pen.ler_id,
1225 ecd.elig_cvrd_dpnt_id,
1226 ecd.object_version_number,
1227 ecd.ovrdn_flag,
1228 ecd.ovrdn_thru_dt,
1229 ecd.cvg_strt_dt,
1230 ecd.effective_start_date,
1231 ctr.contact_relationship_id,
1232 ctr.contact_type,
1233 ctr.date_end, -- 5100008 Added this
1234 -- CWB Changes.
1235 ecd.per_in_ler_id
1236 From ben_prtt_enrt_rslt_f pen,
1237 ben_elig_cvrd_dpnt_f ecd,
1238 ben_per_in_ler pil,
1239 per_contact_relationships ctr
1240 Where pen.business_group_id = p_business_group_id
1241 and pen.prtt_enrt_rslt_stat_cd is null
1242 and ecd.dpnt_person_id = p_person_id
1243 and (nvl(ecd.ovrdn_flag,'N')='N' or
1244 nvl(ecd.ovrdn_thru_dt,p_effective_date)>=p_effective_date)
1245 and p_effective_date between
1246 pen.effective_start_date and pen.effective_end_date
1247 and p_effective_date between
1248 pen.enrt_cvg_strt_dt and nvl(pen.enrt_cvg_thru_dt,
1249 hr_api.g_eot)
1250 and ecd.prtt_enrt_rslt_id=pen.prtt_enrt_rslt_id
1251 and ecd.business_group_id = pen.business_group_id
1252 and p_effective_date between
1253 ecd.effective_start_date and ecd.effective_end_date
1254 and p_effective_date between
1255 ecd.cvg_strt_dt and ecd.cvg_thru_dt
1256 and ctr.person_id=pen.person_id
1257 -- Bug 3056894
1258 and ctr.personal_flag='Y'
1259 and ctr.business_group_id=p_business_group_id
1260 and ctr.contact_person_id=p_person_id
1261 --Bug 14775822
1262 and ctr.date_start in (SELECT max (date_start)
1263 FROM per_contact_relationships
1264 WHERE contact_person_id = p_person_id
1265 AND person_id = pen.person_id
1266 AND personal_flag = 'Y'
1267 AND business_group_id = p_business_group_id
1268 AND date_start <= p_effective_date)
1269 --Bug 14775822
1270 and pil.per_in_ler_id=ecd.per_in_ler_id
1271 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1272 --and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
1273 and pen.prtt_enrt_rslt_stat_cd is null
1274 ;
1275 --
1276 cursor c_opt(l_oipl_id number) is
1277 select opt_id from ben_oipl_f oipl
1278 where oipl.oipl_id = l_oipl_id
1279 and p_effective_date between
1280 oipl.effective_start_date and oipl.effective_end_date;
1281
1282 l_opt c_opt%rowtype;
1283 --
1284 cursor c_dpnt_dsgn_lvl(v_pgm_id in number) is
1285 select pgm.dpnt_dsgn_lvl_cd
1286 from ben_pgm_f pgm
1287 where pgm.pgm_id = v_pgm_id
1288 and pgm.business_group_id = p_business_group_id
1289 and p_effective_date between
1290 pgm.effective_start_date and pgm.effective_end_date;
1291 --
1292 cursor c_ptip_elig(v_person_id in number,
1293 v_ptip_id in number) is
1294 select pep.elig_per_id
1295 from ben_elig_per_f pep,
1296 ben_pl_f pln,
1297 ben_ptip_f ptip,
1298 ben_per_in_ler pil
1299 where pep.person_id = v_person_id
1300 and pep.pl_id = pln.pl_id
1301 and pln.pl_typ_id = ptip.pl_typ_id
1302 and ptip.ptip_id = v_ptip_id
1303 and ptip.pgm_id = pep.pgm_id
1304 and p_effective_date between
1305 pep.effective_start_date and pep.effective_end_date
1306 and p_effective_date between
1307 pln.effective_start_date and pln.effective_end_date
1308 and p_effective_date between
1309 ptip.effective_start_date and ptip.effective_end_date
1310 and pep.per_in_ler_id = pil.per_in_ler_id(+)
1311 and (pil.per_in_ler_stat_cd is null OR
1312 pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT'));
1313 --
1314 cursor c_pgm_elig(v_person_id in number,
1315 v_pgm_id in number) is
1316 select pep.elig_per_id
1317 from ben_elig_per_f pep,
1318 ben_per_in_ler pil
1319 where pep.person_id = v_person_id
1320 and pep.pgm_id = v_pgm_id
1321 and pep.pl_id is not null
1322 and p_effective_date between
1323 pep.effective_start_date and pep.effective_end_date
1324 and pep.per_in_ler_id = pil.per_in_ler_id(+)
1325 and (pil.per_in_ler_stat_cd is null OR
1326 pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT'));
1327 --
1328 cursor c_pl_elig(v_person_id in number,
1329 v_pl_id in number,
1330 v_pgm_id in number) is
1331 select pep.elig_per_id
1332 from ben_elig_per_f pep,
1333 ben_per_in_ler pil
1334 where pep.person_id = v_person_id
1335 and pep.pl_id = v_pl_id
1336 and ((v_pgm_id is null and pep.pgm_id is null) OR
1337 (pep.pgm_id = v_pgm_id))
1338 and p_effective_date between
1339 pep.effective_start_date and pep.effective_end_date
1340 and pep.per_in_ler_id = pil.per_in_ler_id(+)
1341 and (pil.per_in_ler_stat_cd is null OR
1342 pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT'));
1343 --
1344 cursor c_max_create_dt(v_elig_per_id in number) is
1345 select max(egd.create_dt)
1346 from ben_elig_dpnt egd,
1347 ben_per_in_ler pil
1348 where egd.elig_per_id = v_elig_per_id
1349 and egd.dpnt_person_id = p_person_id
1350 and egd.per_in_ler_id = pil.per_in_ler_id (+)
1351 and (pil.per_in_ler_stat_cd is null OR
1352 pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT'));
1353 --
1354 cursor c_egd(v_elig_per_id in number,
1355 v_create_dt in date) is
1356 select egd.*
1357 from ben_elig_dpnt egd,
1358 ben_per_in_ler pil
1359 where egd.elig_per_id = v_elig_per_id
1360 and egd.dpnt_person_id = p_person_id
1361 and egd.dpnt_inelig_flag = 'N'
1362 and egd.create_dt = v_create_dt
1363 and egd.per_in_ler_id = pil.per_in_ler_id (+)
1364 and (pil.per_in_ler_stat_cd is null OR
1365 pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')) ;
1366 --
1367 -- Local Variables
1368 --
1369 l_proc Varchar2(80) := g_package || '.process_designee_elig';
1370 l_output_string Varchar2(80);
1371 l_validate boolean;
1372 l_actn varchar2(80);
1373 l_action varchar2(30);
1374 l_rule_ret varchar2(30);
1375 l_elig_flag varchar2(30);
1376 l_inelig_rsn_cd varchar2(30);
1377 l_datetrack_mode varchar2(30);
1378 l_pel_cnt binary_integer := 0;
1379 l_susp_flag boolean;
1380 l_output varchar2(2000);
1381 l_person_ended varchar2(30):='N';
1382 l_this_person_ended varchar2(30);
1383 l_part_person_id number;
1384 l_effective_start_date date;
1385 l_effective_end_date date;
1386 l_level varchar2(30):=null; -- it will figure it out
1387 l_returned_strt_dt date;
1388 l_object_version_number number;
1389 l_perhasmultptus boolean;
1390 l_assignment_id number;
1391 l_returned_end_dt date;
1392 l_cache g_report_table;
1393 l_id number;
1394 l_ovn1 number;
1395 l_cache_cnt number:=0;
1396 l_found boolean := false;
1397 l_egd_rec ben_elig_dpnt%rowtype;
1398 l_dummy varchar2(1);
1399 l_comp_ineligible boolean := FALSE;
1400 l_temp_person_id number;
1401 l_min_returned_end_dt date;
1402 l_elig_per_id number;
1403 l_max_create_dt date;
1404 l_egd_elig_thru_dt date; -- 5100008
1405
1406 --
1407 begin
1408 hr_utility.set_location ('Entering '|| l_proc,10);
1409 l_actn := 'Initializing...';
1410 Savepoint process_default_enrt_savepoint;
1411 --
1412 -- Cache person data and write personal data into cache.
1413 --
1414 l_actn := 'Calling ben_batch_utils.person_header...';
1415 ben_batch_utils.person_header
1416 (p_person_id => p_person_id
1417 ,p_business_group_id => p_business_group_id
1418 ,p_effective_date => p_effective_date
1419 );
1420 --
1421 l_actn := 'Calling ben_batch_utils.ini(COMP_OBJ)...';
1422 ben_batch_utils.ini('COMP_OBJ');
1423 For l_rec in c_designation loop
1424 hr_utility.set_location(l_proc,101);
1425
1426 l_returned_end_dt:=null;
1427
1428 if l_rec.oipl_id is not null then
1429 open c_opt(l_rec.oipl_id);
1430 fetch c_opt into l_opt;
1431 close c_opt;
1432 end if;
1433
1434 l_this_person_ended:='N';
1435 l_part_person_id:=l_rec.person_id;
1436 l_pel_cnt := l_pel_cnt + 1;
1437 --
1438 l_actn := 'Processing (person:' ||to_char(p_person_id) ||
1439 ' pgm:' || to_char(l_rec.pgm_id) ||
1440 ' Pl_no_Pgm:' || to_char(l_rec.pl_id) || ')';
1441 --
1442 --
1443 -- Check if the comp object requirements are satisfied
1444 -- Note: pgm_id and pl_id args already checked in the cursor
1445 --
1446 l_rule_ret:='Y';
1447 if p_comp_selection_rl is not null then
1448 hr_utility.set_location(l_proc,102);
1449 l_rule_ret:=comp_selection_rule(
1450 p_person_id => p_person_id
1451 ,p_business_group_id => p_business_group_id
1452 ,p_pgm_id => l_rec.pgm_id
1453 ,p_pl_id => l_rec.pl_id
1454 ,p_pl_typ_id => l_rec.pl_typ_id
1455 ,p_opt_id => l_opt.opt_id
1456 ,p_oipl_id => l_rec.oipl_id
1457 ,p_ler_id => l_rec.ler_id
1458 ,p_comp_selection_rule_id => p_comp_selection_rl
1459 ,p_effective_date => p_effective_date
1460 );
1461 end if;
1462 hr_utility.set_location(l_proc,103);
1463 if l_rule_ret='Y' then
1464 hr_utility.set_location(l_proc,104);
1465 --
1466 -- Check to see if the dependent is still eligible
1467 --
1468 hr_utility.set_location('contact_person_id='||to_char(p_person_id),1100);
1469 hr_utility.set_location('oipl_id='||to_char(l_rec.oipl_id),1100);
1470 hr_utility.set_location('ptip_id='||to_char(l_rec.ptip_id),1100);
1471 hr_utility.set_location('pl_id='||to_char(l_rec.pl_id),1100);
1472 hr_utility.set_location('pgm_id='||to_char(l_rec.pgm_id),1100);
1473 hr_utility.set_location('contact_relationship_id_id='||to_char(l_rec.contact_relationship_id),1100);
1474 --
1475 if l_rec.pgm_id is not null then
1476 --
1477 open c_dpnt_dsgn_lvl(l_rec.pgm_id);
1478 fetch c_dpnt_dsgn_lvl into l_level;
1479 close c_dpnt_dsgn_lvl;
1480 --
1481 end if;
1482 --
1483 if l_level is null then
1484 --
1485 l_level := 'PL';
1486 --
1487 end if;
1488 --
1489 ben_evaluate_dpnt_elg_profiles.main
1490 (p_contact_relationship_id => l_rec.contact_relationship_id,
1491 p_contact_person_id => p_person_id,
1492 p_pgm_id => l_rec.pgm_id,
1493 p_pl_id => l_rec.pl_id,
1494 p_ptip_id => l_rec.ptip_id,
1495 p_oipl_id => l_rec.oipl_id,
1496 p_business_group_id => p_business_group_id,
1497 p_per_in_ler_id => null,
1498 p_lf_evt_ocrd_dt => null,
1499 p_effective_date => p_effective_date,
1500 p_dpnt_cvg_strt_dt => l_rec.cvg_strt_dt,
1501 p_level => l_level,
1502 p_dependent_eligible_flag => l_elig_flag,
1503 p_dpnt_inelig_rsn_cd => l_inelig_rsn_cd);
1504 --
1505 -- If the dependent is not eligible end date row
1506 --
1507 hr_utility.set_location(l_proc,105);
1508 hr_utility.set_location('l_elig_flag'||l_elig_flag,8888);
1509 --
1510 if l_elig_flag<>'Y' then
1511 --
1512 hr_utility.set_location(l_proc,106);
1513 --
1514 -- 5100008 : Added this to fetch EGD ELIG_THRU_DT
1515 if (l_rec.date_end <= p_effective_date) then
1516 l_egd_elig_thru_dt := l_rec.date_end;
1517 else
1518 l_egd_elig_thru_dt := ben_evaluate_dpnt_elg_profiles.get_elig_change_dt;
1519 end if;
1520 -- 5100008 ENDS
1521
1522 --
1523 -- Get the dpnt cvg end date
1524 -- Bug 5442301 -- dont update cvg_thru date if it is overriden
1525 hr_utility.set_location('SARKAR l_rec.ovrdn_flag '||l_rec.ovrdn_flag,8888);
1526 hr_utility.set_location('SARKAR l_rec.ovrdn_thru_dt '||l_rec.ovrdn_thru_dt,8888);
1527
1528 -- 14552686 - when dependent enrollment record(other than coverage thru date) is
1529 -- overrided through enrollment override form then dependent coverage
1530 -- is not ending though the dependent became ineligible.
1531
1532 if nvl(l_rec.ovrdn_flag,'N') = 'Y' and
1533 -- nvl(l_rec.ovrdn_thru_dt,hr_api.g_eot) >= p_effective_date then --commented for bug 14552686
1534 l_rec.ovrdn_thru_dt is not null and
1535 l_rec.ovrdn_thru_dt >= p_effective_date then -- code added for bug 14552686
1536
1537 null;
1538 else
1539 calc_dpnt_cvg_end_dt(
1540 p_person_id => l_rec.person_id
1541 ,p_pgm_id => l_rec.pgm_id
1542 ,p_pl_id => l_rec.pl_id
1543 ,p_oipl_id => l_rec.oipl_id
1544 ,p_ptip_id => l_rec.ptip_id
1545 ,p_business_group_id => p_business_group_id
1546 ,p_effective_date => p_effective_date
1547 ,p_returned_end_dt => l_returned_end_dt);
1548 end if;
1549
1550 --
1551
1552 -- if nvl(l_min_returned_end_dt, l_returned_end_dt) >= l_returned_end_dt then
1553 -- 5100008 : BEN_ASG needs to be created on ELIG_THRU_DT, not on CVG_THRU_DT
1554 -- Hence commented above line.
1555 if nvl(l_min_returned_end_dt, l_egd_elig_thru_dt) >= l_egd_elig_thru_dt then
1556
1557 --
1558 l_comp_ineligible := TRUE;
1559 l_temp_person_id := l_rec.person_id;
1560 l_min_returned_end_dt := l_egd_elig_thru_dt;
1561 --
1562 end if;
1563 --
1564 -- Call api
1565 --
1566 if p_effective_date=l_rec.effective_start_date then
1567 l_datetrack_mode:=hr_api.g_correction;
1568 else
1569 l_datetrack_mode:=hr_api.g_update;
1570 end if;
1571 --
1572 -- According to the cursor, the coverage has started as of the
1573 -- effective date, so the coverage through date should atleast be
1574 -- the coverge start date. If the coverage through date is less
1575 -- than the coverage start date, we assign a value of effective
1576 -- date.
1577 --
1578 if l_returned_end_dt < l_rec.cvg_strt_dt then
1579 --
1580 l_returned_end_dt := p_effective_date;
1581 --
1582 end if;
1583 --
1584 hr_utility.set_location('pdp_id='||to_char(l_rec.elig_cvrd_dpnt_id),111);
1585 hr_utility.set_location('cvg_thru_dt='||to_char(l_returned_end_dt),112);
1586 -- -- Bug 5442301 -- dont update cvg_thru date if it is overriden
1587
1588 -- 14552686 - when dependent enrollment record(other than coverage thru date) is
1589 -- overrided through enrollment override form then dependent coverage
1590 -- is not ending though the dependent became ineligible.
1591 if nvl(l_rec.ovrdn_flag,'N') = 'Y' and
1592 -- nvl(l_rec.ovrdn_thru_dt,hr_api.g_eot) >= p_effective_date then
1593 l_rec.ovrdn_thru_dt is not null and
1594 l_rec.ovrdn_thru_dt >= p_effective_date then -- code added for bug 14552686
1595
1596 null;
1597 else
1598 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
1599 p_elig_cvrd_dpnt_id => l_rec.elig_cvrd_dpnt_id
1600 ,p_effective_start_date => l_effective_start_date
1601 ,p_effective_end_date => l_effective_end_date
1602 ,p_object_version_number => l_rec.object_version_number
1603 ,p_business_group_id => p_business_group_id
1604 ,p_effective_date => p_effective_date
1605 ,p_cvg_thru_dt => l_returned_end_dt
1606 ,p_datetrack_mode => l_datetrack_mode
1607 ,p_program_application_id => fnd_global.prog_appl_id
1608 ,p_program_id => fnd_global.conc_program_id
1609 ,p_request_id => fnd_global.conc_request_id
1610 ,p_program_update_date => sysdate
1611 ,p_multi_row_actn => false
1612 );
1613 end if;
1614 --
1615 l_found := ben_ELIG_DPNT_api.get_elig_dpnt_rec(
1616 p_elig_cvrd_dpnt_id => l_rec.elig_cvrd_dpnt_id
1617 ,p_effective_date => p_effective_date
1618 ,p_elig_dpnt_rec => l_egd_rec);
1619 --
1620 if l_found and l_egd_rec.dpnt_inelig_flag = 'N' then
1621 --
1622 ben_elig_dpnt_api.update_elig_dpnt(
1623 p_elig_dpnt_id => l_egd_rec.elig_dpnt_id
1624 ,p_object_version_number => l_egd_rec.object_version_number
1625 ,p_effective_date => p_effective_date
1626 ,p_elig_thru_dt => l_egd_elig_thru_dt --l_returned_end_dt 5100008
1627 ,p_dpnt_inelig_flag => 'Y'
1628 ,p_inelg_rsn_cd => l_inelig_rsn_cd
1629 );
1630 --
1631 end if;
1632 --
1633 if l_level = 'PGM' then
1634 --
1635 open c_pgm_elig(l_rec.person_id,
1636 l_rec.pgm_id);
1637 --
1638 elsif l_level = 'PTIP' then
1639 --
1640 open c_ptip_elig(l_rec.person_id,
1641 l_rec.ptip_id);
1642 --
1643 elsif l_level = 'PL' then
1644 --
1645 open c_pl_elig(l_rec.person_id,
1646 l_rec.pl_id,
1647 l_rec.pgm_id);
1648 --
1649 end if;
1650 --
1651 loop
1652 --
1653 if l_level = 'PGM' then
1654 --
1655 fetch c_pgm_elig into l_elig_per_id;
1656 --
1657 if c_pgm_elig%notfound then
1658 --
1659 exit;
1660 --
1661 end if;
1662 --
1663 elsif l_level = 'PTIP' then
1664 --
1665 fetch c_ptip_elig into l_elig_per_id;
1666 --
1667 if c_ptip_elig%notfound then
1668 --
1669 exit;
1670 --
1671 end if;
1672 --
1673 elsif l_level = 'PL' then
1674 --
1675 fetch c_pl_elig into l_elig_per_id;
1676 --
1677 if c_pl_elig%notfound then
1678 --
1679 exit;
1680 --
1681 end if;
1682 --
1683 else
1684 --
1685 exit;
1686 --
1687 end if;
1688 --
1689 open c_max_create_dt(l_elig_per_id);
1690 fetch c_max_create_dt into l_max_create_dt;
1691 close c_max_create_dt;
1692 --
1693 for l_egd in c_egd(l_elig_per_id, l_max_create_dt) loop
1694 --
1695 ben_elig_dpnt_api.update_elig_dpnt(
1696 p_elig_dpnt_id => l_egd.elig_dpnt_id
1697 ,p_object_version_number => l_egd.object_version_number
1698 ,p_effective_date => p_effective_date
1699 ,p_elig_thru_dt => l_egd_elig_thru_dt --l_returned_end_dt
1700 ,p_dpnt_inelig_flag => 'Y'
1701 ,p_inelg_rsn_cd => l_inelig_rsn_cd);
1702 --
1703 end loop;
1704 --
1705 end loop;
1706 --
1707 if l_level = 'PGM' then
1708 --
1709 close c_pgm_elig;
1710 --
1711 elsif l_level = 'PTIP' then
1712 --
1713 close c_ptip_elig;
1714 --
1715 elsif l_level = 'PL' then
1716 --
1717 close c_pl_elig;
1718 --
1719 end if;
1720 --
1721 /* --
1722 -- Create benefits assignment for dependent - COBRA requirement.
1723 --
1724 ben_assignment_internal.copy_empasg_to_benasg
1725 (p_person_id => l_rec.person_id
1726 ,p_dpnt_person_id => p_person_id
1727 ,p_effective_date => l_returned_end_dt + 1
1728 ,p_assignment_id => l_assignment_id
1729 ,p_object_version_number => l_object_version_number
1730 ,p_perhasmultptus => l_perhasmultptus
1731 );
1732 */
1733 --
1734 -- Create communication if required
1735 --
1736 ben_generate_communications.main
1737 (p_person_id => l_rec.person_id,
1738 p_dpnt_person_id => p_person_id,
1739 -- CWB Changes.
1740 p_per_in_ler_id => l_rec.per_in_ler_id,
1741 p_pgm_id => l_rec.pgm_id,
1742 p_pl_id => l_rec.pl_id,
1743 p_pl_typ_id => l_rec.pl_typ_id,
1744 p_business_group_id => p_business_group_id,
1745 p_proc_cd1 => 'DPNTENDENRT',
1746 p_proc_cd2 => 'HPADPNTLC',
1747 p_effective_date => p_effective_date,
1748 p_lf_evt_ocrd_dt => ben_evaluate_dpnt_elg_profiles.get_elig_change_dt,
1749 p_source => 'bendsgel');
1750 --
1751 g_designations_ended:=g_designations_ended+1;
1752 l_person_ended:='Y';
1753 l_this_person_ended:='Y';
1754 else
1755 hr_utility.set_location(l_proc,107);
1756 g_designations_remaining:=g_designations_remaining+1;
1757 end if;
1758 hr_utility.set_location(l_proc,108);
1759 --
1760 end if;
1761 --
1762 -- write the audit info for this
1763 --
1764 -- bump up the counter
1765 --
1766 l_cache_cnt:=l_cache_cnt+1;
1767 --
1768 -- set the values in cache, this cache is so that rollback may
1769 -- happen (p_validate set) and still the audit will be written
1770 --
1771 if l_this_person_ended='Y' then
1772 l_cache(l_cache_cnt).actn_cd:='ENDED';
1773 else
1774 l_cache(l_cache_cnt).actn_cd:='NOCHG';
1775 end if;
1776 l_cache(l_cache_cnt).person_id:=l_rec.person_id;
1777 l_cache(l_cache_cnt).pgm_id:=l_rec.pgm_id;
1778 l_cache(l_cache_cnt).pl_id:=l_rec.pl_id;
1779 l_cache(l_cache_cnt).oipl_id:=l_rec.oipl_id;
1780 l_cache(l_cache_cnt).contact_type:=l_rec.contact_type;
1781 l_cache(l_cache_cnt).dpnt_cvg_strt_dt:=l_rec.cvg_strt_dt;
1782 l_cache(l_cache_cnt).dpnt_cvg_thru_dt:=l_returned_end_dt;
1783 --
1784 hr_utility.set_location(l_proc,109);
1785 --
1786 end loop;
1787 --
1788 if l_comp_ineligible then
1789 --
1790 -- Person is ineligible for atleast one comp object
1791 -- Create benefits assignment for dependent - COBRA requirement.
1792 --
1793 ben_assignment_internal.copy_empasg_to_benasg
1794 (p_person_id => l_temp_person_id
1795 ,p_dpnt_person_id => p_person_id
1796 ,p_effective_date => least(l_min_returned_end_dt+1, p_effective_date+1)
1797 ,p_assignment_id => l_assignment_id
1798 ,p_object_version_number => l_object_version_number
1799 ,p_perhasmultptus => l_perhasmultptus
1800 );
1801 --
1802 end if;
1803 --
1804 hr_utility.set_location(l_proc,110);
1805 l_actn := 'Calling Ben_batch_utils.write_comp...';
1806 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
1807 ,p_effective_date => p_effective_date
1808 );
1809 l_actn := 'Calling write_rec (category)...';
1810 If (p_validate = 'Y') then
1811 Rollback to process_default_enrt_savepoint;
1812 End if;
1813 --
1814 -- Store the participant Id on the related_person_id so that
1815 -- may get participant counts for summary report
1816 --
1817 ben_batch_utils.g_rec.related_person_id:=l_part_person_id;
1818 hr_utility.set_location('g_profile_value = '||g_profile_value,100);
1819 if l_person_ended='Y' then
1820 Ben_batch_utils.write_rec(p_typ_cd => 'DSGENDED');
1821 else
1822 --
1823 --Bug 1579948 Fix
1824 --
1825 if(g_profile_value <> 'N') then
1826 --
1827 Ben_batch_utils.write_rec(p_typ_cd => 'DSGNOCHG');
1828 --
1829 end if;
1830 --
1831 end if;
1832 --
1833 -- write audit report info
1834 --
1835 -- Bug 1579948 Fix: Added if stmt to restrict a row to write into
1836 -- ben_batch_dpnt_info if the g_profile_value set to "No". This is
1837 -- done for improving the performance of audit log
1838 --
1839 For i in 1..l_cache_cnt Loop
1840 hr_utility.set_location(l_proc||' pgm_id='||to_char(l_cache(i).pgm_id),111);
1841 if((l_cache(i).actn_cd = 'ENDED')
1842 or (l_cache(i).actn_cd = 'NOCHG'
1843 and nvl(g_profile_value, 'Y') = 'Y')) then
1844 ben_batch_dpnt_info_api.create_batch_dpnt_info
1845 (p_batch_dpnt_id => l_id
1846 ,p_person_id => l_cache(i).person_id
1847 ,p_benefit_action_id => benutils.g_benefit_action_id
1848 ,p_business_group_id => p_business_group_id
1849 ,p_pgm_id => l_cache(i).pgm_id
1850 ,p_pl_id => l_cache(i).pl_id
1851 ,p_oipl_id => l_cache(i).oipl_id
1852 ,p_contact_typ_cd => l_cache(i).contact_type
1853 ,p_enrt_cvg_strt_dt => l_cache(i).dpnt_cvg_strt_dt
1854 ,p_enrt_cvg_thru_dt => l_cache(i).dpnt_cvg_thru_dt
1855 ,p_actn_cd => l_cache(i).actn_cd
1856 ,p_object_version_number => l_ovn1
1857 ,p_dpnt_person_id => p_person_id
1858 ,p_effective_date => p_effective_date
1859 );
1860 end if;
1861 End loop;
1862 --
1863 If p_person_action_id is not null then
1864 --
1865 l_actn := 'Calling ben_person_actions_api.update_person_actions...';
1866 --
1867 ben_person_actions_api.update_person_actions
1868 (p_person_action_id => p_person_action_id
1869 ,p_action_status_cd => 'P'
1870 ,p_object_version_number => p_object_version_number
1871 ,p_effective_date => p_effective_date
1872 );
1873 End if;
1874 g_persons_processed := g_persons_processed + 1;
1875 commit;
1876 hr_utility.set_location ('Leaving '|| l_proc,10);
1877 Exception
1878 When others then
1879 rollback to process_default_enrt_savepoint;
1880 g_persons_errored := g_persons_errored + 1;
1881 -- capture the error message
1882 benutils.write(p_text => fnd_message.get);
1883 benutils.write(p_text => sqlerrm);
1884 ben_batch_utils.write_error_rec;
1885 ben_batch_utils.rpt_error(p_proc => l_proc
1886 ,p_last_actn => l_actn
1887 ,p_rpt_flag => TRUE);
1888 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
1889 ,p_effective_date => p_effective_date
1890 );
1891 If p_person_action_id is not null then
1892 ben_person_actions_api.update_person_actions
1893 (p_person_action_id => p_person_action_id
1894 ,p_action_status_cd => 'E'
1895 ,p_object_version_number => p_object_version_number
1896 ,p_effective_date => p_effective_date
1897 );
1898 End if;
1899 commit;
1900 raise ben_batch_utils.g_record_error;
1901 end process_designee_elig;
1902 --
1903 end ben_maintain_designee_elig; -- End of Package.