[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_SINGLE_PER_PROCESS_PKG
Source
1 package body BEN_CWB_SINGLE_PER_PROCESS_PKG as
2 /* $Header: bencwbsp.pkb 120.6 2007/08/21 12:16:17 steotia noship $ */
3 --
4 -- --------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- --------------------------------------------------------------------------
7 --
8 g_package varchar2(33):=' ben_cwb_single_per_process_pkg.';
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- --------------------------------------------------------------------------
12 -- |----------------------< process >---------------------|
13 -- --------------------------------------------------------------------------
14 --
15 procedure process
16 (errbuf OUT NOCOPY VARCHAR2
17 ,retcode OUT NOCOPY NUMBER
18 ,p_validate in varchar2 default 'Y'
19 ,p_search_date in varchar2
20 ,p_person_id in number
21 ,p_business_group in number
22 ,p_group_pl_id in varchar2
23 ,p_lf_evt_dt_range in varchar2
24 ,p_lf_evt_dt in varchar2
25 ,p_run_from_ss in varchar2 default 'N'
26 ,p_clone_all_data_flag in varchar2 default 'N'
27 ,p_backout_and_process_flag in varchar2 default 'N') is
28
29 l_person_type varchar2(240);
30 l_method varchar2(240);
31 l_method_display varchar2(240);
32 l_special_flag varchar2(240);
33 l_start_date varchar2(240);
34 l_term_date varchar2(240);
35 l_no_payroll_warn varchar2(240);
36 l_no_salary_warn varchar2(240);
37 l_no_supervisor_warn varchar2(240);
38 l_no_position_warn varchar2(240);
39 l_no_paybasis_warn varchar2(240);
40 l_past_term_warn varchar2(240);
41 l_future_term_warn varchar2(240);
42 l_curr_absence_warn varchar2(240);
43 l_future_absence_warn varchar2(240);
44 l_data_freeze_date date;
45 l_search_date date;
46 l_lf_evt_dt_range date;
47 l_lf_evt_dt date;
48 l_group_pl_id number;
49 l_commit number;
50 l_clone_all_data_flag varchar2(30);
51 l_backout_and_process_flag varchar2(30);
52
53 l_group_per_in_ler_id number;
54 l_group_pl_name varchar2(240);
55 l_plan_name varchar2(240);
56 l_prsrv_bdgt_cd varchar2(30);
57 l_period varchar2(80);
58 l_elig_status varchar2(80);
59 l_event_status varchar2(80);
60 l_pp_stat_cd varchar2(80);
61 l_pl_id number;
62
63 l_benefit_action_id number;
64 l_object_version_number number;
65
66 l_err varchar2(240);
67 l_appl varchar2(30);
68 l_loc varchar2(240);
69 l_type varchar2(30);
70
71 l_index number;
72
73 cursor c_freeze_date
74 (v_pl_id in number
75 ,v_search_date in date) is
76 select max(enp.data_freeze_date) data_freeze_date
77 from ben_popl_enrt_typ_cycl_f petc
78 ,ben_enrt_perd enp
79 ,ben_yr_perd yr
80 ,ben_wthn_yr_perd wyr
81 where petc.pl_id = v_pl_id
82 and v_search_date between petc.effective_start_date and petc.effective_end_date
83 and petc.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
84 and yr.yr_perd_id = enp.yr_perd_id
85 and enp.wthn_yr_perd_id = wyr.wthn_yr_perd_id (+);
86 begin
87 /*
88 ben_batch_utils.write(p_validate);
89 ben_batch_utils.write(p_search_date);
90 ben_batch_utils.write(p_person_id);
91 ben_batch_utils.write(p_business_group);
92 ben_batch_utils.write(p_group_pl_id);
93 ben_batch_utils.write(p_lf_evt_dt);
94 ben_batch_utils.write(p_run_from_ss);
95 */
96 if(p_run_from_ss='N') then
97 l_group_pl_id := substr(p_group_pl_id,1,instr(p_group_pl_id,'^')-1);
98 else
99 l_group_pl_id := p_group_pl_id;
100 end if;
101
102 l_search_date := fnd_date.canonical_to_date(p_search_date);
103 l_lf_evt_dt_range := fnd_date.canonical_to_date(p_lf_evt_dt_range);
104 l_lf_evt_dt := fnd_date.canonical_to_date(p_lf_evt_dt);
105
106 l_clone_all_data_flag := p_clone_all_data_flag;
107 l_backout_and_process_flag := p_backout_and_process_flag;
108
109 dt_fndate.change_ses_date
110 (p_ses_date => l_search_date,
111 p_commit => l_commit);
112 ben_batch_utils.write ('+===========================================================================+');
113 ben_batch_utils.write ('Changing Session Date: '||l_search_date);
114 ben_batch_utils.write ('Commit on date : '||l_commit);
115 ben_batch_utils.write ('+===========================================================================+');
116
117 ben_batch_utils.write ('Time: '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
118 ben_batch_utils.write ('=====================Benefit Actions=======================');
119 ben_batch_utils.write ('||Parameter value ');
120 ben_batch_utils.write ('||p_request_id- ' || fnd_global.conc_request_id);
121 ben_batch_utils.write ('||p_program_application_id- ' || fnd_global.prog_appl_id);
122 ben_batch_utils.write ('||p_program_id- ' || fnd_global.conc_program_id);
123 ben_batch_utils.write ('==========================================================');
124 ben_benefit_actions_api.create_perf_benefit_actions
125 (p_benefit_action_id => l_benefit_action_id
126 , p_process_date => sysdate
127 , p_mode_cd => 'W'
128 , p_derivable_factors_flag => 'NONE'
129 , p_validate_flag => 'N'
130 , p_debug_messages_flag => 'N'
131 , p_business_group_id => p_business_group
132 , p_no_programs_flag => 'N'
133 , p_no_plans_flag => 'N'
134 , p_audit_log_flag => 'N'
135 , p_pl_id => l_group_pl_id
136 , p_pgm_id => -9999
137 , p_lf_evt_ocrd_dt => l_lf_evt_dt
138 , p_person_id => p_person_id
139 --, p_grant_price_val => p_grant_price_val
140 , p_object_version_number => l_object_version_number
141 , p_effective_date => l_search_date
142 , p_request_id => fnd_global.conc_request_id
143 , p_program_application_id => fnd_global.prog_appl_id
144 , p_program_id => fnd_global.conc_program_id
145 , p_program_update_date => SYSDATE
146 , p_bft_attribute30 => 'N'
147 );
148 ben_batch_utils.write ('Benefit Action Id: ' || l_benefit_action_id);
149 benutils.g_benefit_action_id := l_benefit_action_id;
150 commit;
151 savepoint fallback;
152 ben_batch_utils.write('Processing...');
153 ben_batch_utils.write('Validate: '||p_validate);
154 ben_batch_utils.write('Search Date: '||l_search_date);
155 ben_batch_utils.write('Person ID: '||p_person_id);
156 ben_batch_utils.write('Business group ID: '||p_business_group);
157 ben_batch_utils.write('Plan ID: '||l_group_pl_id);
158 ben_batch_utils.write('LER Date: '||l_lf_evt_dt);
159 ben_batch_utils.write('Run from SS? '||p_run_from_ss);
160
161 if(p_run_from_ss='N') then
162
163 open c_freeze_date(l_group_pl_id,l_search_date);
164 fetch c_freeze_date into l_data_freeze_date;
165 close c_freeze_date;
166 ben_batch_utils.write('Freeze Date: '||l_data_freeze_date);
167
168 ben_batch_utils.write ('+=================detect_method_and_warning=================================+');
169 begin
170 detect_method_and_warnings
171 (p_person_id => p_person_id
172 ,p_group_pl_id => l_group_pl_id
173 ,p_lf_evt_dt => l_lf_evt_dt
174 ,p_data_freeze_date => l_data_freeze_date
175 ,p_search_date => l_search_date
176 ,p_person_type => l_person_type
177 ,p_method => l_method
178 ,p_method_display => l_method_display
179 ,p_special_flag => l_special_flag
180 ,p_start_date => l_start_date
181 ,p_term_date => l_term_date
182 ,p_no_payroll_warn => l_no_payroll_warn
183 ,p_no_salary_warn => l_no_salary_warn
184 ,p_no_supervisor_warn => l_no_supervisor_warn
185 ,p_no_position_warn => l_no_position_warn
186 ,p_no_paybasis_warn => l_no_paybasis_warn
187 ,p_past_term_warn => l_past_term_warn
188 ,p_future_term_warn => l_future_term_warn
189 ,p_curr_absence_warn => l_curr_absence_warn
190 ,p_future_absence_warn => l_future_absence_warn);
191 exception
192 when others then
193 ben_batch_utils.write('detect_method_and_warnings failed');
194 ben_batch_utils.write(SQLERRM);
195 end;
196 ben_batch_utils.write ('+==========================Parameters=======================================+');
197 ben_batch_utils.write('After detect_method_and_warnings');
198 ben_batch_utils.write('Person Type: '||l_person_type);
199 ben_batch_utils.write('Processing method:'||l_method);
200 ben_batch_utils.write('Method Display: '||l_method_display);
201 ben_batch_utils.write('Special Flag: '||l_special_flag);
202 ben_batch_utils.write('Start Date: '||l_start_date);
203 ben_batch_utils.write('Termination Date: '||l_term_date);
204 ben_batch_utils.write ('+==========================Warnings=========================================+');
205
206 if(upper(l_no_payroll_warn)='Y') then
207 fnd_message.set_name('BEN','BEN_94050_ADMIN_PP_NO_PAYROLL');
208 ben_batch_utils.write(fnd_message.get);
209 end if;
210 if(upper(l_no_salary_warn)='Y') then
211 fnd_message.set_name('BEN','BEN_94051_ADMIN_PP_NO_SALARY');
212 ben_batch_utils.write(fnd_message.get);
213 end if;
214 if(upper(l_no_supervisor_warn)='Y') then
215 fnd_message.set_name('BEN','BEN_94052_ADMIN_PP_NO_SUPRVSR');
216 ben_batch_utils.write(fnd_message.get);
217 end if;
218 if(upper(l_no_position_warn)='Y') then
219 fnd_message.set_name('BEN','BEN_94053_ADMIN_PP_NO_POSITION');
220 ben_batch_utils.write(fnd_message.get);
221 end if;
222 if(upper(l_no_paybasis_warn)='Y') then
223 fnd_message.set_name('BEN','BEN_94054_ADMIN_PP_NO_PAYBASIS');
224 ben_batch_utils.write(fnd_message.get);
225 end if;
226
227 if(l_method='SPECIAL') then
228 fnd_message.set_name('BEN','BEN_94055_ADMIN_PP_SPL_PROCESS');
229 ben_batch_utils.write(fnd_message.get);
230 fnd_message.set_name('BEN','BEN_94056_ADMIN_PP_SPL_PRC_ABR');
231 ben_batch_utils.write(fnd_message.get);
232 fnd_message.set_name('BEN','BEN_94057_ADMIN_PP_SPL_PRC_ELG');
233 ben_batch_utils.write(fnd_message.get);
234 fnd_message.set_name('BEN','BEN_94058_ADMIN_PP_SPL_PRC_MGR');
235 ben_batch_utils.write(fnd_message.get);
236 l_clone_all_data_flag := 'Y';
237 elsif (l_method='REPROCESS') then
238 fnd_message.set_name('BEN','BEN_94063_ADMIN_PP_REPROCESS');
239 ben_batch_utils.write(fnd_message.get);
240 end if;
241
242 if((l_method='REPROCESS') or (l_method='PH_TO_NORMAL')) then
243 l_backout_and_process_flag := 'Y';
244 end if;
245
246 if(upper(l_past_term_warn)='Y') then
247 fnd_message.set_name('BEN','BEN_94059_ADMIN_PP_PAST_TERM');
248 ben_batch_utils.write(fnd_message.get);
249 end if;
250 if(upper(l_future_term_warn)='Y') then
251 fnd_message.set_name('BEN','BEN_94060_ADMIN_PP_FUTURE_TERM');
252 ben_batch_utils.write(fnd_message.get);
253 end if;
254 if(upper(l_curr_absence_warn)='Y') then
255 fnd_message.set_name('BEN','BEN_94061_ADMIN_PP_CURR_ABSNCE');
256 ben_batch_utils.write(fnd_message.get);
257 end if;
258 if(upper(l_future_absence_warn)='Y') then
259 fnd_message.set_name('BEN','BEN_94062_ADMIN_PP_FUTUR_ABSNC');
260 ben_batch_utils.write(fnd_message.get);
261 end if;
262
263 end if;
264
265 ben_batch_utils.write ('+==============Running participation process================================+');
266 begin
267
268 ben_batch_utils.write('p_validate: '||p_validate);
269 ben_batch_utils.write('l_search_date: '||l_search_date);
270 ben_batch_utils.write('p_person_id: '||p_person_id);
271 ben_batch_utils.write('p_business_group: '||p_business_group);
272 ben_batch_utils.write('l_group_pl_id: '||l_group_pl_id);
273 ben_batch_utils.write('l_lf_evt_dt: '||l_lf_evt_dt);
274
275 ben_batch_utils.write('Clone all data? '||l_clone_all_data_flag);
276 ben_batch_utils.write('Backout and reprocess? '||l_backout_and_process_flag);
277
278 run_participation_process
279 (p_validate => p_validate
280 ,p_effective_date => l_search_date
281 ,p_person_id => p_person_id
282 ,p_business_group_id => p_business_group
283 ,p_group_pl_id => l_group_pl_id
284 ,p_lf_evt_ocrd_dt => l_lf_evt_dt
285 ,p_clone_all_data_flag => l_clone_all_data_flag
286 ,p_backout_and_process_flag => l_backout_and_process_flag
287 ,p_group_per_in_ler_id => l_group_per_in_ler_id
288 ,p_group_pl_name => l_group_pl_name
289 ,p_plan_name => l_plan_name
290 ,p_prsrv_bdgt_cd => l_prsrv_bdgt_cd
291 ,p_period => l_period
292 ,p_elig_status => l_elig_status
293 ,p_event_status => l_event_status
294 ,p_pp_stat_cd => l_pp_stat_cd
295 ,p_pl_id => l_pl_id);
296
297 exception
298 when others then
299 ben_batch_utils.write('run_participation_process failed');
300 ben_batch_utils.write(SQLERRM);
301 end;
302
303 ben_batch_utils.write ('+===========================Results=========================================+');
304 ben_batch_utils.write('group_per_in_ler_id: '||l_group_per_in_ler_id);
305 ben_batch_utils.write('Group Plan Name: '||l_group_pl_name);
306 ben_batch_utils.write('Plan Name: '||l_plan_name);
307 ben_batch_utils.write('Preserve Budget Code: '||l_prsrv_bdgt_cd);
308 ben_batch_utils.write('Period: '||l_period);
309 ben_batch_utils.write('Eligibility Status: '||l_elig_status);
310 ben_batch_utils.write('Event Status: '||l_event_status);
311 ben_batch_utils.write('Participation Process Status Code: '||l_pp_stat_cd);
312 ben_batch_utils.write('Plan ID: '||l_pl_id);
313 ben_batch_utils.write ('+===========================================================================+');
314
315 if(l_group_per_in_ler_id=-1) then
316 ben_batch_utils.write('Cannot process person');
317 end if;
318
319 if(FND_MSG_PUB.COUNT_MSG()>0) then
320 raise ben_batch_utils.g_record_error;
321 end if;
322
323 exception
324 when others then
325 rollback to fallback;
326 ben_batch_utils.write('Run Participation Process has failed to enroll person');
327
328 --l_err := FND_MSG_PUB.GET_DETAIL(p_msg_index => FND_MSG_PUB.G_FIRST);
329 --l_err := replace(FND_MSG_PUB.GET_DETAIL(p_msg_index => FND_MSG_PUB.G_FIRST),chr(0),' ');
330 --l_appl := rtrim(substr(l_err,0,instr(l_err,' ')));
331 --l_err := rtrim(substr(ltrim(substr(l_err,instr(l_err,' '))),0,instr(ltrim(substr(l_err,instr(l_err,' '))),' ')));
332 --fnd_message.set_name (l_appl, l_err);
333 /*
334 FND_MESSAGE.Set_Encoded(FND_MSG_PUB.GET_DETAIL(p_msg_index => FND_MSG_PUB.G_FIRST));
335 */
336
340 else
337 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
338 if(i=1) then
339 l_index := FND_MSG_PUB.G_FIRST;
341 l_index := FND_MSG_PUB.G_NEXT;
342 end if;
343 l_err := FND_MSG_PUB.GET_DETAIL(p_msg_index => l_index);
344 insert into ben_transaction( transaction_id
345 ,transaction_type
346 ,status
347 ,attribute1
348 ,attribute2
349 --,attribute3
350 )
351 values( BEN_TRANSACTION_S.NEXTVAL
352 ,'EMPENROLL'||l_benefit_action_id
353 ,'E'
354 --,FND_MSG_PUB.GET_DETAIL(p_msg_index => l_index)
355 ,replace(l_err,chr(0),' ')
356 ,p_person_id
357 --l_loc
358 );
359 END LOOP;
360 commit;
361 FND_MESSAGE.Set_Encoded(l_err);
362 l_loc := FND_MESSAGE.GET_TOKEN('FND_ERROR_LOCATION_FIELD','Y');
363 l_type := FND_MESSAGE.GET_TOKEN('FND_MESSAGE_TYPE','Y');
364 raise;
365 end;
366
367 --
368 -- --------------------------------------------------------------------------
369 -- |-----------------------< recreate_error_stack >-------------------------|
370 -- --------------------------------------------------------------------------
371 --
372 procedure recreate_error_stack(p_request_id in number) is
373
374 cursor c_errors(v_request_id in number) is
375 select attribute1
376 from ben_benefit_actions actn, ben_transaction, fnd_concurrent_requests req
377 where req.request_id = v_request_id
378 and req.request_id = actn.request_id
379 and transaction_type = 'EMPENROLL'||benefit_action_id
380 and attribute2 = req.ARGUMENT3;
381 l_errors varchar2(240);
382 l_message varchar2(240);
383 begin
384 fnd_msg_pub.initialize;
385 open c_errors(p_request_id);
386 loop
387 fetch c_errors into l_errors;
388 EXIT WHEN c_errors%NOTFOUND;
389
390 l_message := replace(l_errors,' ',chr(0));
391 fnd_message.set_encoded(l_message);
392 FND_MSG_PUB.ADD;
393
394 end loop;
395 close c_errors;
396 end;
397
398 --
399 -- --------------------------------------------------------------------------
400 -- |----------------------< detect_method_and_warnings >---------------------|
401 -- --------------------------------------------------------------------------
402 --
403 procedure detect_method_and_warnings
404 (p_person_id in number
405 ,p_group_pl_id in number
406 ,p_lf_evt_dt in date
407 ,p_data_freeze_date in date
408 ,p_search_date in date
409 ,p_person_type out nocopy varchar2
410 ,p_method out nocopy varchar2
411 ,p_method_display out nocopy varchar2
412 ,p_special_flag out nocopy varchar2
413 ,p_start_date out nocopy varchar2
414 ,p_term_date out nocopy varchar2
415 ,p_no_payroll_warn out nocopy varchar2
416 ,p_no_salary_warn out nocopy varchar2
417 ,p_no_supervisor_warn out nocopy varchar2
418 ,p_no_position_warn out nocopy varchar2
419 ,p_no_paybasis_warn out nocopy varchar2
420 ,p_past_term_warn out nocopy varchar2
421 ,p_future_term_warn out nocopy varchar2
422 ,p_curr_absence_warn out nocopy varchar2
423 ,p_future_absence_warn out nocopy varchar2) is
424
425 cursor csr_person_type(p_person_id number
426 ,p_effective_date date) is
427 select ppt.system_person_type
428 from per_person_type_usages_f ptu
429 ,per_person_types ppt
430 where ptu.person_id = p_person_id
431 and p_effective_date between ptu.effective_start_date
432 and ptu.effective_end_date
433 and ptu.person_type_id = ppt.person_type_id
434 and ppt.system_person_type in ('EMP','CWK');
435
436 cursor csr_pds_details(p_person_id number
437 ,p_effective_date date) is
438 select date_start
439 ,actual_termination_date
440 ,final_process_date
441 ,projected_termination_date
442 from per_all_assignments_f asg
443 ,per_periods_of_service pds
444 where asg.person_id = p_person_id
445 and p_effective_date between
446 asg.effective_start_date and asg.effective_end_date
447 and asg.period_of_service_id = pds.period_of_service_id;
448
449 cursor csr_pdp_details(p_person_id number
450 ,p_effective_date date) is
451 select date_start
452 ,actual_termination_date
453 ,final_process_date
454 ,projected_termination_date
455 from per_periods_of_placement
456 where person_id = p_person_id
457 and p_effective_date between date_start
458 and nvl(actual_termination_date,to_date('4712/12/31','yyyy/mm/dd'));
459
460 cursor csr_pil_details(p_person_id number
461 ,p_group_pl_id number
462 ,p_lf_evt_dt date) is
463 select pil.per_in_ler_id
464 ,pil.per_in_ler_stat_cd
465 ,per.post_process_stat_cd
466 ,rates.group_per_in_ler_id rates_id
467 from ben_per_in_ler pil
468 ,ben_cwb_person_info per
469 ,ben_cwb_person_rates rates
470 where pil.person_id = p_person_id
471 and pil.group_pl_id = p_group_pl_id
472 and pil.lf_evt_ocrd_dt = p_lf_evt_dt
473 and pil.per_in_ler_stat_cd <> 'BCKDT'
474 and pil.per_in_ler_id = per.group_per_in_ler_id
475 and pil.per_in_ler_id = rates.group_per_in_ler_id (+)
476 and rates.oipl_id (+) = -1;
477
478 cursor csr_other_details(p_person_id number
479 ,p_eff_date date) is
480 select decode(asg.payroll_id,null,'Y','N') no_payroll
481 ,decode(ppp.pay_proposal_id,null,'Y','N') no_salary
482 ,decode(asg.supervisor_id, null, 'Y', 'N') no_supervisor
483 ,decode(asg.position_id, null, 'Y', 'N') no_position
484 ,decode(asg.pay_basis_id, null, 'Y', 'N') no_paybasis
485 ,decode(curr_abs.absence_attendance_id,null,'N','Y') curr_abs
486 ,decode(future_abs.absence_attendance_id,null,'N','Y') future_abs
487 from per_all_assignments_f asg
488 ,per_pay_proposals ppp
489 ,per_absence_attendances curr_abs
490 ,per_absence_attendances future_abs
491 where asg.person_id = p_person_id
492 and asg.primary_flag = 'Y'
493 and asg.assignment_type in ('E','C')
497 and ppp.approved (+) = 'Y'
494 and p_eff_date between asg.effective_start_date and asg.effective_end_date
495 and asg.assignment_id = ppp.assignment_id (+)
496 and ppp.proposed_salary_n (+) > 0
498 and asg.person_id = curr_abs.person_id (+)
499 and p_eff_date between curr_abs.date_start(+) and curr_abs.date_end (+)
500 and asg.person_id = future_abs.person_id (+)
501 and p_eff_date < future_abs.date_start (+);
502
503
504
505 -- Added to check hrchy used in plan design
506 -- Bug# 4395367
507
508 cursor csr_pl_dsgn_hrchy(p_group_pl_id number
509 ,p_lf_evt_dt date) is
510 select HRCHY_TO_USE_CD
511 from
512 BEN_ENRT_PERD enrt
513 , ben_cwb_pl_dsgn dsgn
514 where dsgn.pl_id = p_group_pl_id
515 and enrt.enrt_perd_id = dsgn.enrt_perd_id
516 and dsgn.lf_evt_ocrd_dt= p_lf_evt_dt;
517
518
519
520 -- variable declaration
521 l_per_in_ler_id number;
522 l_per_in_ler_stat_cd varchar2(30);
523 l_post_process_stat_cd varchar2(30);
524 l_rates_id number;
525 --
526 l_date_start date;
527 l_act_term_date date;
528 l_final_proc_date date;
529 l_proj_term_date date;
530 l_term_date date;
531 --
532 l_eff_date date;
533 --
534
535 --
536 l_hrchy_cd varchar2(5);
537 --
538
539 l_proc varchar2(72) := g_package||'detect_method_and_warnings';
540 begin
541 --
542 if g_debug then
543 hr_utility.set_location('Entering:'|| l_proc, 10);
544 end if;
545 --
546 -- find the person type of the person
547 open csr_person_type(p_person_id, p_search_date);
548 fetch csr_person_type into p_person_type;
549 close csr_person_type;
550 --
551 if g_debug then
552 hr_utility.set_location(l_proc, 20);
553 end if;
554 --
555 -- Detect method
556 --
557 -- get the pil details
558 open csr_pil_details(p_person_id, p_group_pl_id, p_lf_evt_dt);
559 fetch csr_pil_details into l_per_in_ler_id
560 ,l_per_in_ler_stat_cd
561 ,l_post_process_stat_cd
562 ,l_rates_id;
563 --
564 if g_debug then
565 hr_utility.set_location(l_proc, 30);
566 end if;
567 --
568 -- check whether the pil is present and has the status 'PROCESSED'
569 if (csr_pil_details%found) then
570 --
571 if g_debug then
572 hr_utility.set_location(l_proc, 40);
573 end if;
574 --
575 -- pil is present
576 if (l_post_process_stat_cd = 'PR') then
577 -- return null, so that caller will raise the error
578 p_method := null;
579
580 elsif (l_rates_id is null) then
581 -- rates record is not present. so placeholder to normal
582 p_method := 'PH_TO_NORMAL';
583 else
584 -- rates is also present. so reprocess
585 p_method := 'REPROCESS';
586 end if;
587 -- for both PH_TO_NORMAL and REPROCESS, special process is required
588 -- if no pds record exists on life event occured date
589 open csr_pds_details(p_person_id, p_lf_evt_dt);
590 fetch csr_pds_details into l_date_start
591 ,l_act_term_date
592 ,l_final_proc_date
593 ,l_proj_term_date;
594
595 if (csr_pds_details%notfound) then
596 p_special_flag := 'Y';
597 end if;
598 close csr_pds_details;
599 else
600 --
601 if g_debug then
602 hr_utility.set_location(l_proc, 50);
603 end if;
604 --
605 -- the person is going to processed for the first time.
606 -- check normal processing or special processing
607 if (p_person_type = 'EMP') then
608 -- check the pds details as on lf_evt_dt
609 --
610 if g_debug then
611 hr_utility.set_location(l_proc, 60);
612 end if;
613 --
614 open csr_pds_details(p_person_id, p_lf_evt_dt);
615 fetch csr_pds_details into l_date_start
616 ,l_act_term_date
617 ,l_final_proc_date
618 ,l_proj_term_date;
619
620 if (csr_pds_details%notfound) then
621 -- the person does not have valid assignment on lf_evt_dt
622 -- special processing
623 p_method := 'SPECIAL';
624 else
625 -- anything else is normal processing
626 p_method := 'NORMAL';
627 end if;
628 --
629 close csr_pds_details;
630 else -- the person is 'CWK'
631 --
632 if g_debug then
633 hr_utility.set_location(l_proc, 70);
634 end if;
635 --
636 -- check the pds details as on lf_evt_dt
637 open csr_pdp_details(p_person_id, p_lf_evt_dt);
638 fetch csr_pdp_details into l_date_start
639 ,l_act_term_date
640 ,l_final_proc_date
641 ,l_proj_term_date;
642
643 if (csr_pdp_details%notfound) then
644 -- the person does not have valid assignment on lf_evt_dt
645 -- special processing
646 p_method := 'SPECIAL';
647 else
648 -- anything else is normal processing
649 p_method := 'NORMAL';
650 end if;
651 --
652 close csr_pdp_details;
653 end if; -- of peson_type
657 --
654 --
655 --
656 end if;
658 close csr_pil_details;
659 --
660 if g_debug then
661 hr_utility.set_location(l_proc, 80);
662 end if;
663 --
664 -- Detect the employement period and warnings
665
666 -- find the employement period effective on the search_date
667 if p_person_type = 'EMP' then
668 open csr_pds_details(p_person_id, p_search_date);
669 fetch csr_pds_details into l_date_start
670 ,l_act_term_date
671 ,l_final_proc_date
672 ,l_proj_term_date;
673
674 close csr_pds_details;
675 -- set the start_date and term_date
676 p_start_date := l_date_start;
677 p_term_date := l_final_proc_date;
678 else -- the person is 'CWK'
679 open csr_pdp_details(p_person_id, p_search_date);
680 fetch csr_pdp_details into l_date_start
681 ,l_act_term_date
682 ,l_final_proc_date
683 ,l_proj_term_date;
684
685 close csr_pdp_details;
686 -- set the start_date and term_date
687 p_start_date := l_date_start;
688 p_term_date := l_act_term_date;
689 --
690 end if;
691 --
692 if g_debug then
693 hr_utility.set_location(l_proc, 90);
694 end if;
695 --
696 -- find the eff_date in this order : data_freeze_date, lf_evt_dt
697 l_eff_date := p_data_freeze_date;
698 if (l_eff_date is null) then
699 l_eff_date := p_lf_evt_dt;
700 end if;
701 --
702 --
703 if g_debug then
704 hr_utility.set_location(l_proc, 100);
705 end if;
706 --
707 -- set the warnings
708 open csr_other_details(p_person_id, l_eff_date);
709 fetch csr_other_details into p_no_payroll_warn
710 ,p_no_salary_warn
711 ,p_no_supervisor_warn
712 ,p_no_position_warn
713 ,p_no_paybasis_warn
714 ,p_curr_absence_warn
715 ,p_future_absence_warn;
716
717 open csr_pl_dsgn_hrchy(p_group_pl_id, p_lf_evt_dt);
718 fetch csr_pl_dsgn_hrchy into l_hrchy_cd;
719
720 if l_hrchy_cd = 'S' then
721 p_no_position_warn:= 'N';
722 end if;
723
724 if l_hrchy_cd = 'P' then
725 p_no_supervisor_warn:= 'N';
726 end if;
727
728 close csr_pl_dsgn_hrchy;
729
730 close csr_other_details;
731 --
732 --
733 if g_debug then
734 hr_utility.set_location(l_proc, 110);
735 end if;
736 --
737 l_date_start := null; l_act_term_date := null;
738 l_final_proc_date := null; l_proj_term_date := null;
739 -- check the future term warn
740 if p_person_type = 'EMP' then
741 open csr_pds_details(p_person_id, l_eff_date);
742 fetch csr_pds_details into l_date_start
743 ,l_act_term_date
744 ,l_final_proc_date
745 ,l_proj_term_date;
746
747 close csr_pds_details;
748 else -- the person is 'CWK'
749 open csr_pdp_details(p_person_id, l_eff_date);
750 fetch csr_pdp_details into l_date_start
751 ,l_act_term_date
752 ,l_final_proc_date
753 ,l_proj_term_date;
754
755 close csr_pdp_details;
756 end if;
757 --
758 --
759 if g_debug then
760 hr_utility.set_location(l_proc, 120);
761 end if;
762 --
763 l_term_date := l_act_term_date;
764 if l_term_date is null then
765 l_term_date := l_final_proc_date;
766 if l_term_date is null then
767 l_term_date := l_proj_term_date;
768 end if;
769 end if;
770 --
771 --
772 if g_debug then
773 hr_utility.set_location(l_proc, 130);
774 end if;
775 --
776 if l_term_date is not null then
777 if l_term_date <= l_eff_date then
778 p_past_term_warn := 'Y';
779 else
780 p_future_term_warn := 'Y';
781 end if;
782 end if;
783 --
784 --
785 if g_debug then
786 hr_utility.set_location(l_proc, 140);
787 end if;
788 --
789 -- set the p_method_display with lookup value
790 select meaning into p_method_display
791 from hr_lookups
792 where lookup_type = 'BEN_CWB_ADMIN_PP_TYPE'
793 and lookup_code = p_method;
794
795 -- set the p_term_date with lookup value, if it is null
796 if p_term_date is null then
797 select meaning into p_term_date
798 from hr_lookups
799 where lookup_type = 'BEN_CWB_MISC_TEXT'
800 and lookup_code = 'ONGOING';
801 end if;
802 --
803 if g_debug then
804 hr_utility.set_location(p_method,10);
805 hr_utility.set_location(p_start_date,20);
806 hr_utility.set_location(p_term_date,30);
807 hr_utility.set_location(p_no_payroll_warn,40);
808 hr_utility.set_location(p_no_salary_warn,50);
809 hr_utility.set_location(p_no_supervisor_warn,60);
810 hr_utility.set_location(p_no_position_warn,70);
811 hr_utility.set_location(p_no_paybasis_warn,80);
812 hr_utility.set_location(p_past_term_warn,90);
813 hr_utility.set_location(p_future_term_warn,100);
814 hr_utility.set_location(p_curr_absence_warn,110);
818 --
815 hr_utility.set_location(p_future_absence_warn,120);
816 hr_utility.set_location(p_person_type,120);
817 end if;
819 if g_debug then
820 hr_utility.set_location(' Leaving:'|| l_proc, 999);
821 end if;
822 --
823 end;
824 -- --------------------------------------------------------------------------
825 -- |----------------------< run_participation_process >---------------------|
826 -- --------------------------------------------------------------------------
827 --
828 procedure run_participation_process
829 (p_validate in varchar2 default 'N'
830 ,p_effective_date in date
831 ,p_person_id in number default null
832 ,p_business_group_id in number
833 ,p_group_pl_id in number default null
834 ,p_lf_evt_ocrd_dt in date default null
835 ,p_clone_all_data_flag in varchar2 default 'N'
836 ,p_backout_and_process_flag in varchar2 default 'N'
837 ,p_group_per_in_ler_id out nocopy number
838 ,p_group_pl_name out nocopy varchar2
839 ,p_plan_name out nocopy varchar2
840 ,p_prsrv_bdgt_cd out nocopy varchar2
841 ,p_period out nocopy varchar2
842 ,p_elig_status out nocopy varchar2
843 ,p_event_status out nocopy varchar2
844 ,p_pp_stat_cd out nocopy varchar2
845 ,p_pl_id out nocopy number) is
846 --
847 l_group_per_in_ler_id number;
848 l_group_pl_name ben_cwb_pl_dsgn.name%type;
849 l_plan_name ben_cwb_pl_dsgn.name%type;
850 l_prsrv_bdgt_cd ben_cwb_pl_dsgn.prsrv_bdgt_cd%type;
851 l_period varchar2(80);
852 l_elig_status varchar2(80);
853 l_event_status varchar2(80);
854 l_pp_stat_cd varchar2(80);
855 l_pl_id number;
856 --
857 l_proc varchar2(72) := g_package||'run_participation_process';
858 --
859 begin
860 --
861 ben_batch_utils.write('-');
862 ben_batch_utils.write(p_validate);
863 ben_batch_utils.write(p_effective_date);
864 ben_batch_utils.write(p_person_id);
865 ben_batch_utils.write(p_business_group_id);
866 ben_batch_utils.write(p_group_pl_id);
867 ben_batch_utils.write(p_lf_evt_ocrd_dt);
868 ben_batch_utils.write(p_clone_all_data_flag);
869 ben_batch_utils.write(p_backout_and_process_flag);
870 ben_batch_utils.write('-');
871
872 if g_debug then
873 hr_utility.set_location('Entering:'|| l_proc, 10);
874 hr_utility.set_location('p_effective_date :'||p_effective_date,20);
875 hr_utility.set_location('p_person_id :'||p_person_id,30);
876 hr_utility.set_location('p_business_group_id :'||p_business_group_id,40);
877 hr_utility.set_location('p_group_pl_id :'||p_group_pl_id, 50);
878 hr_utility.set_location('p_lf_evt_ocrd_dt :'||p_lf_evt_ocrd_dt, 60);
879 hr_utility.set_location('p_clone_all_data :'||p_clone_all_data_flag,70);
880 hr_utility.set_location('p_backout_and_process_flag :'
881 ||p_backout_and_process_flag, 80);
882 end if;
883
884 ben_manage_cwb_life_events.global_online_process_w(
885 p_validate => p_validate
886 ,p_effective_date => p_effective_date
887 ,p_person_id => p_person_id
888 ,p_business_group_id => p_business_group_id
889 ,p_pl_id => p_group_pl_id
890 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
891 ,p_clone_all_data_flag => p_clone_all_data_flag
892 ,p_backout_and_process_flag => p_backout_and_process_flag);
893 --
894 if g_debug then
895 hr_utility.set_location(l_proc, 20);
896 end if;
897 --
898 -- find the group_per_in_ler_id and elig_status of the person
899 begin
900 select pil.per_in_ler_id
901 ,grppln.name group_pl_name
902 ,decode(rates.group_per_in_ler_id, null, '-1',pln.name) plan_name
903 ,pln.prsrv_bdgt_cd prsrv_bdgt_cd
904 ,decode(grppln.wthn_yr_start_dt,null,null,grppln.wthn_yr_start_dt
905 ||' - '||grppln.wthn_yr_end_dt) period
906 ,elig_stat.meaning eligible_status
907 ,event_stat.meaning event_status
908 ,per.post_process_stat_cd pp_stat_cd
909 ,pln.pl_id
910 into l_group_per_in_ler_id
911 ,l_group_pl_name
912 ,l_plan_name
913 ,l_prsrv_bdgt_cd
914 ,l_period
915 ,l_elig_status
916 ,l_event_status
917 ,l_pp_stat_cd
918 ,l_pl_id
919 from ben_per_in_ler pil
920 ,ben_cwb_pl_dsgn grppln
921 ,ben_cwb_pl_dsgn pln
922 ,ben_cwb_person_rates rates
923 ,ben_cwb_person_info per
924 ,hr_lookups elig_stat
925 ,hr_lookups event_stat
926 where pil.person_id = p_person_id
927 and pil.group_pl_id = p_group_pl_id
928 and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
929 and pil.per_in_ler_stat_cd ='STRTD'
930 and pil.per_in_ler_id = per.group_per_in_ler_id
931 and event_stat.lookup_type (+) = 'BEN_PER_IN_LER_STAT'
932 and event_stat.lookup_code (+) = pil.per_in_ler_stat_cd
933 and event_stat.enabled_flag (+) = 'Y'
934 and pil.group_pl_id = grppln.pl_id
935 and grppln.oipl_id = -1
936 and pil.lf_evt_ocrd_dt = grppln.lf_evt_ocrd_dt
937 and pil.group_pl_id = pln.group_pl_id
938 and pil.lf_evt_ocrd_dt = pln.lf_evt_ocrd_dt
939 and decode(rates.group_per_in_ler_id, null, 'Y', pln.actual_flag)= 'Y'
940 and pln.oipl_id = -1
941 and pil.per_in_ler_id = rates.group_per_in_ler_id (+)
942 and decode(rates.group_per_in_ler_id,null,pil.group_pl_id,rates.pl_id)
943 = pln.pl_id
944 and rates.oipl_id (+)= -1
945 and elig_stat.lookup_type (+) = 'BEN_CWB_ELIG_CRITERIA'
946 and elig_stat.lookup_code (+) = rates.elig_flag
947 and elig_stat.enabled_flag (+) = 'Y'
948 and rownum = 1;
949 --
950 --
951 if g_debug then
952 hr_utility.set_location(l_proc, 30);
953 end if;
954 --
955 -- set the values to out parameters
956 p_group_per_in_ler_id := l_group_per_in_ler_id;
957 p_group_pl_name := l_group_pl_name;
958 p_plan_name := l_plan_name;
959 p_prsrv_bdgt_cd := l_prsrv_bdgt_cd;
960 p_period := l_period;
961 p_elig_status := l_elig_status;
962 p_event_status := l_event_status;
963 p_pp_stat_cd := l_pp_stat_cd;
964 p_pl_id := l_pl_id;
965 exception
966 when others then
967 --
968 if g_debug then
969 hr_utility.set_location(' Leaving:'|| l_proc, 49);
970 end if;
971 --
972 p_group_per_in_ler_id := -1;
973 end;
974 --
975 if g_debug then
976 hr_utility.set_location(' Leaving:'|| l_proc, 99);
977 end if;
978 --
979 end;
980 end BEN_CWB_SINGLE_PER_PROCESS_PKG;
981