[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_BACKOUT_POST_PROCESS
Source
1 PACKAGE BODY BEN_CWB_BACKOUT_POST_PROCESS AS
2 /* $Header: bencwbbp.pkb 120.17.12020000.5 2013/04/19 06:41:38 sgnanama ship $ */
3 /* ===========================================================================+
4 * Name
5 * Compensation Workbench Backout Post Process
6 * Purpose
7 * This package is used to check validity of parameters passed in via SRS
8 * or via a PL/SQL function or procedure. This package will make a call
9 * to process backout compensation workbench postings for all comp.
10 * object for each person.
11 *
12 * Version Date Author Comment
13 * ---------+-----------+---------+----------------------------------------------
14 * 120.0 01-Oct-10 sgnanama ER:5257917
15 * 120.2 01-Nov-10 sgnanama Included placeholder persons
16 * 120.3 02-Dec-10 sgnanama corrected dbdrv
17 * 120.4 10-Jan-11 sgnanama Corrected new_sal/prev_sal,reason,bonus in report
18 * Added process_access to change task access
19 * 120.5 12-Jan-11 sgnanama Corected gscc error
20 * 120.6 21-Jan-11 sgnanama Update submit code in process_access
21 * 120.7 10-Feb-11 sgnanama Update task status code in process_access
22 * 120.8 15-Feb-11 sgnanama Send FYI Notification
23 * Bug fixes : 11731996, 11732038, 11732038, 11741078, 11736671, 11736130
24 * 120.9 25-Feb-11 sgnanama Bug fixes : 11788202, 11741025, 11803107
25 * 120.10 02-Jun-11 sgnanama Bug fixes : 12580980, 12571024
26 * 120.11 03-Jun-11 sgnanama Bug fixes : 12581640, 12607360
27 * 120.12 03-Jun-11 sgnanama Corrected the msg number for BEN_94739_PAYROLL_RAN
28 * 120.13 22-Jun-11 naramasa Bug fixes : 12635353
29 * 120.14 29-Jun-11 sgnanama Bug fixes : 12696399, 12616726
30 * 120.16 01-Jul-11 sgnanama Bug fixes : 12714771
31 * 120.17 07-Jul-11 sgnanama Bug fixes : 12714771
32 * 120.18 30-Jan-12 naramasa Bug fixes : 12581404
33 * 120.17.x.4 3-Feb-12 sgnanama Bug fixes : 16246942
34 * 120.17.x.5 22-Apr-13 sgnanama Bug fixes : additional fix for 12581404
35 * ==========================================================================+
36 */
37
38 --
39 -- Global cursor and variables declaration
40 --
41 TYPE plan_override_date IS RECORD (
42 plan ben_cwb_pl_dsgn.pl_id%type,
43 date ben_cwb_pl_dsgn.ovrid_rt_strt_dt%type);
44
45 TYPE g_override_date_t IS TABLE OF plan_override_date;
46
47 TYPE plan_abr_info IS RECORD (
48 pl_id ben_cwb_pl_dsgn.pl_id%type,
49 oipl_id ben_cwb_pl_dsgn.oipl_id%type,
50 element_type_id ben_cwb_pl_dsgn.ws_element_type_id%type,
51 input_value_id ben_cwb_pl_dsgn.ws_input_value_id%type);
52
53 TYPE g_abr_info_t IS TABLE OF plan_abr_info;
54
55 g_package VARCHAR2 (80) := 'BEN_CWB_BACKOUT_POST_PROCESS';
56 g_max_errors_allowed NUMBER (9) := 200;
57 g_persons_errored NUMBER (9) := 0;
58 g_persons_procd NUMBER (9) := 0;
59 g_person_selected NUMBER (9) := 0;
60 g_lf_evt_closed NUMBER (9) := 0;
61 g_lf_evt_not_closed NUMBER (9) := 0;
62 g_proc VARCHAR2 (80);
63 g_actn VARCHAR2 (2000);
64 g_debug_level VARCHAR2 (1);
65 g_slave_error EXCEPTION;
66 g_max_error EXCEPTION;
67 g_person_errored BOOLEAN;
68
69 g_override_dates g_override_date_t := g_override_date_t();
70 g_plan_abr_info g_abr_info_t := g_abr_info_t();
71
72 CURSOR c_table_correction_data(v_benefit_action_id IN NUMBER)
73 IS
74 SELECT o.oipl_id,
75 rpt.group_per_in_ler_id,
76 rpt.pl_id,
77 group_oipl.oipl_id group_oipl_id,
78 rpt.person_id
79 FROM ben_oipl_f o,
80 ben_cwb_rpt_detail rpt,
81 ben_oipl_f local_oipl,
82 ben_opt_f local_opt,
83 ben_opt_f group_opt,
84 ben_oipl_f group_oipl
85 WHERE o.pl_id = rpt.pl_id
86 AND rpt.benefit_action_id = v_benefit_action_id
87 AND rpt.oipl_id = -1
88 AND local_oipl.oipl_id = o.oipl_id
89 AND local_opt.opt_id = local_oipl.opt_id
90 AND group_opt.group_opt_id = local_opt.group_opt_id
91 AND group_oipl.opt_id = group_opt.group_opt_id
92 AND NOT EXISTS
93 (SELECT NULL
94 FROM ben_cwb_rpt_detail
95 WHERE oipl_id = o.oipl_id
96 AND benefit_action_id = v_benefit_action_id)
97 GROUP BY o.oipl_id,
98 rpt.group_per_in_ler_id,
99 rpt.pl_id,
100 group_oipl.oipl_id,
101 rpt.person_id;
102
103 CURSOR c_get_abr_info(v_lf_evt_ocrd_date IN DATE
104 ,v_pl_id IN NUMBER
105 ,v_oipl_id IN NUMBER)
106 IS
107 SELECT ws_element_type_id, ws_input_value_id
108 FROM ben_cwb_pl_dsgn
109 WHERE lf_evt_ocrd_dt = v_lf_evt_ocrd_date
110 AND pl_id = v_pl_id
111 AND oipl_id = v_oipl_id;
112
113 CURSOR c_override_start_date(v_group_pl_id IN NUMBER
114 ,v_pl_id IN NUMBER
115 ,v_group_oipl_id IN NUMBER
116 ,v_oipl_id IN NUMBER
117 ,v_lf_evt_ocrd_date IN DATE)
118 IS
119 SELECT ovrid_rt_strt_dt
120 FROM ben_cwb_pl_dsgn dsgn
121 WHERE group_pl_id = v_group_pl_id
122 AND pl_id = v_pl_id
123 AND group_oipl_id = v_group_oipl_id
124 AND oipl_id = v_oipl_id
125 AND lf_evt_ocrd_dt = v_lf_evt_ocrd_date;
126 CURSOR c_pil_ovn (v_per_in_ler_id IN NUMBER)
127 IS
128 SELECT object_version_number
129 FROM ben_per_in_ler pil
130 WHERE pil.per_in_ler_id = v_per_in_ler_id;
131
132 CURSOR c_info_ovn (v_group_per_in_ler_id IN NUMBER)
133 IS
134 SELECT object_version_number
135 FROM ben_cwb_person_info info
136 WHERE info.group_per_in_ler_id = v_group_per_in_ler_id;
137
138 CURSOR c_rate_ovn (v_group_per_in_ler_id IN NUMBER, v_pl_id IN NUMBER, v_oipl_id IN NUMBER)
139 IS
140 SELECT object_version_number
141 FROM ben_cwb_person_rates rt
142 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
143 AND rt.pl_id = v_pl_id
144 AND rt.oipl_id = v_oipl_id;
145
146 CURSOR c_grp_ovn (
147 v_group_per_in_ler_id IN NUMBER
148 , v_group_pl_id IN NUMBER
149 , v_group_oipl_id IN NUMBER
150 )
151 IS
152 SELECT grp.object_version_number
153 ,grp.access_cd
154 ,grp.approval_cd
155 ,grp.submit_cd
156 ,per.full_name
157 ,per.person_id
158 ,per.group_per_in_ler_id
159 FROM ben_cwb_person_groups grp
160 ,ben_cwb_person_info per
161 WHERE grp.group_per_in_ler_id = v_group_per_in_ler_id
162 AND grp.group_pl_id = v_group_pl_id
163 AND grp.group_oipl_id = v_group_oipl_id
164 AND per.group_per_in_ler_id = grp.group_per_in_ler_id;
165
166 CURSOR c_bg_and_mgr_name (v_group_per_in_ler_id IN NUMBER, v_effective_date IN DATE)
167 IS
168 SELECT bg.NAME
169 , per.full_name
170 , info.business_group_id
171 FROM per_business_groups_perf bg
172 , ben_cwb_person_info info
173 , per_all_people_f per
174 , ben_per_in_ler pil
175 WHERE info.group_per_in_ler_id = v_group_per_in_ler_id
176 AND bg.business_group_id = info.business_group_id
177 AND v_effective_date >= bg.date_from
178 AND ( bg.date_to IS NULL
179 OR bg.date_to >= v_effective_date)
180 AND info.group_per_in_ler_id = pil.per_in_ler_id
181 AND pil.ws_mgr_id = per.person_id(+)
182 AND v_effective_date BETWEEN per.effective_start_date(+) AND per.effective_end_date(+);
183
184 CURSOR c_emp_num_and_emp_name(v_group_per_in_ler_id IN NUMBER)
185 IS
186 SELECT nvl(per.custom_name,per.full_name) full_name
187 , per.employee_number
188 , per.assignment_id
189 , per.business_group_id
190 , per.legislation_code
191 FROM ben_cwb_person_info per
192 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
193
194 CURSOR c_batch_proc_info (v_benefit_action_id IN NUMBER)
195 IS
196 SELECT info.batch_proc_id
197 , info.object_version_number
198 FROM ben_batch_proc_info info
199 WHERE info.benefit_action_id = v_benefit_action_id;
200
201 CURSOR c_error_per_summary (v_benefit_action_id IN NUMBER)
202 IS
203 SELECT COUNT (*) amount
204 FROM ben_cwb_rpt_detail
205 WHERE person_rate_id = -9999
206 AND status_cd = 'E'
207 AND benefit_action_id = v_benefit_action_id;
208
209 CURSOR c_succ_per_summary (v_benefit_action_id IN NUMBER)
210 IS
211 SELECT COUNT (*) amount
212 FROM ben_cwb_rpt_detail
213 WHERE person_rate_id = -9999
214 AND status_cd IN ('WC', 'SC', 'W')
215 AND benefit_action_id = v_benefit_action_id;
216
217 CURSOR c_lf_evt_open_summary (v_benefit_action_id IN NUMBER)
218 IS
219 SELECT COUNT (*) amount
220 FROM ben_cwb_rpt_detail
221 WHERE person_rate_id = -9999
222 AND lf_evt_closed_flag = 'N'
223 AND benefit_action_id = v_benefit_action_id;
224
225 CURSOR c_lf_evt_close_summary (v_benefit_action_id IN NUMBER)
226 IS
227 SELECT COUNT (*) amount
228 FROM ben_cwb_rpt_detail
229 WHERE person_rate_id = -9999
230 AND lf_evt_closed_flag = 'Y'
231 AND benefit_action_id = v_benefit_action_id;
232
233 CURSOR c_person_selection (
234 v_pl_id IN NUMBER
235 , v_lf_evt_orcd_date IN DATE
236 , v_person_id IN NUMBER
237 , v_manager_id IN NUMBER
238 , v_business_group_id IN NUMBER
239 , v_effective_date IN DATE
240 , v_local_plan_list IN VARCHAR2
241 )
242 IS
243 SELECT pil.person_id
244 , max(pil.per_in_ler_id) per_in_ler_id
245 , max(nvl(per.custom_name,per.full_name)) full_name
246 , max(bg.NAME) NAME
247 , per.business_group_id
248 FROM ben_per_in_ler pil
249 , ben_per_in_ler mgr_pil
250 , ben_cwb_group_hrchy hrchy
251 , per_business_groups_perf bg
252 , ben_cwb_person_info per
253 , ben_cwb_person_rates rates
254 , ben_cwb_pl_dsgn dsgn
255 WHERE pil.per_in_ler_stat_cd = 'PROCD'
256 AND pil.group_pl_id = v_pl_id
257 AND pil.lf_evt_ocrd_dt = v_lf_evt_orcd_date
258 AND per.group_per_in_ler_id = pil.per_in_ler_id
259 AND ( v_person_id IS NULL
260 OR pil.person_id = v_person_id)
261 AND ( v_business_group_id IS NULL
262 OR per.business_group_id = v_business_group_id)
263 AND per.business_group_id = bg.business_group_id
264 AND v_effective_date >= bg.date_from
265 AND ( bg.date_to IS NULL
266 OR bg.date_to >= v_effective_date)
267 AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
268 AND hrchy.mgr_per_in_ler_id = mgr_pil.per_in_ler_id
269 AND ( ( v_manager_id IS NULL
270 AND hrchy.lvl_num = (SELECT MAX (lvl_num)
271 FROM ben_cwb_group_hrchy
272 WHERE emp_per_in_ler_id = hrchy.emp_per_in_ler_id)
273 )
274 OR ( mgr_pil.person_id = v_manager_id
275 AND hrchy.lvl_num > 0)
276 )
277 AND rates.group_per_in_ler_id = pil.per_in_ler_id
278 AND rates.pl_id = dsgn.pl_id
279 AND rates.oipl_id = dsgn.oipl_id
280 --AND rates.elig_flag = 'Y'
281 AND (v_local_plan_list is NULL
282 OR (instr(v_local_plan_list,to_char(dsgn.pl_id)) >0 ) )
283 AND rates.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
284 AND dsgn.oipl_id=-1
285 AND nvl(dsgn.do_not_process_flag,'N') <> 'Y'
286 GROUP BY pil.person_id, per.business_group_id
287 ORDER BY full_name;
288
289 CURSOR c_placeholder_selection (
290 v_pl_id IN NUMBER
291 , v_lf_evt_orcd_date IN DATE
292 , v_person_id IN NUMBER
293 , v_manager_id IN NUMBER
294 , v_business_group_id IN NUMBER
295 , v_effective_date IN DATE
296 , v_local_plan_list IN VARCHAR2
297 )
298 IS
299 SELECT pil.person_id
300 , (pil.per_in_ler_id) per_in_ler_id
301 , (nvl(per.custom_name,per.full_name)) full_name
302 , bg.NAME
303 , per.legislation_code
304 , per.business_group_id
305 FROM
306 ben_per_in_ler pil,
307 ben_per_in_ler mgr_pil,
308 ben_cwb_person_info per,
309 ben_cwb_group_hrchy hrchy,
310 per_business_groups_perf bg
311 where pil.per_in_ler_stat_cd = 'PROCD'
312 AND pil.group_pl_id = per.group_pl_id
313 AND pil.lf_evt_ocrd_dt = per.lf_evt_ocrd_dt
314 AND per.group_per_in_ler_id = pil.per_in_ler_id
315 AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
316 AND hrchy.mgr_per_in_ler_id = mgr_pil.per_in_ler_id
317 AND ( v_person_id IS NULL
318 OR pil.person_id = v_person_id)
319 AND ( ( v_manager_id IS NULL
320 AND hrchy.lvl_num = (SELECT MAX (lvl_num)
321 FROM ben_cwb_group_hrchy
322 WHERE emp_per_in_ler_id = hrchy.emp_per_in_ler_id)
323 )
324 OR ( mgr_pil.person_id = v_manager_id
325 AND hrchy.lvl_num > 0)
326 )
327 and not exists(
328 select null from
329 ben_cwb_person_rates
330 where group_per_in_ler_id = pil.per_in_ler_id
331 )
332 and (v_business_group_id is null or
333 per.business_group_id = v_business_group_id)
334 AND bg.business_group_id = per.business_group_id
335 AND v_effective_date >= bg.date_from
336 AND ( bg.date_to IS NULL
337 OR bg.date_to >= v_effective_date)
338 and per.group_pl_id = v_pl_id
339 and per.lf_evt_ocrd_dt = v_lf_evt_orcd_date
340 ;
341
342 CURSOR c_pils_for_access (
343 v_pl_id IN NUMBER
344 , v_lf_evt_orcd_date IN DATE
345 , v_person_id IN NUMBER
346 , v_manager_id IN NUMBER
347 , v_business_group_id IN NUMBER
348 , v_effective_date IN DATE
349 , v_local_plan_list IN VARCHAR2
350 )
351 IS
352 SELECT distinct (mgr_per.group_per_in_ler_id) per_in_ler_id
353 , (mgr_per.full_name) full_name
354 FROM ben_per_in_ler pil
355 --, ben_per_in_ler mgr_pil
356 , ben_cwb_group_hrchy hrchy
357 --, ben_cwb_group_hrchy mgr_hrchy
358 , per_business_groups_perf bg
359 , ben_cwb_person_info per
360 , ben_cwb_person_info mgr_per
361 --, ben_cwb_person_rates rates
362 --, ben_cwb_pl_dsgn dsgn
363 WHERE pil.per_in_ler_stat_cd = 'PROCD'
364 AND pil.group_pl_id = v_pl_id
365 AND pil.lf_evt_ocrd_dt = v_lf_evt_orcd_date
366 AND per.group_per_in_ler_id = pil.per_in_ler_id
367 AND ( v_person_id IS NULL
368 OR pil.person_id = v_person_id)
369 AND ( v_business_group_id IS NULL
370 OR per.business_group_id = v_business_group_id)
371 AND per.business_group_id = bg.business_group_id
372 AND v_effective_date >= bg.date_from
373 AND ( bg.date_to IS NULL
374 OR bg.date_to >= v_effective_date)
375 AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
376 AND hrchy.lvl_num > 0
377 AND mgr_per.group_per_in_ler_id = hrchy.mgr_per_in_ler_id
378 AND ( ( v_manager_id IS NULL
379 )
380 OR hrchy.emp_per_in_ler_id in ( select mmgr.per_in_ler_id from ben_per_in_ler mmgr where
381 mmgr.ws_mgr_id = v_manager_id and mmgr.per_in_ler_stat_cd = 'PROCD')
382 )
383 /*AND rates.group_per_in_ler_id = pil.per_in_ler_id
384 AND rates.pl_id = dsgn.pl_id
385 AND rates.oipl_id = dsgn.oipl_id
386 --AND rates.elig_flag = 'Y'
387 AND (v_local_plan_list is NULL
388 OR (instr(v_local_plan_list,to_char(dsgn.pl_id)) >0 ) )
389 AND rates.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
390 AND dsgn.oipl_id=-1
391 AND nvl(dsgn.do_not_process_flag,'N') <> 'Y'*/
392 -- GROUP BY pil.person_id, per.business_group_id
393 ORDER BY full_name;
394
395
396 CURSOR c_check_eligibility (v_group_per_in_ler_id IN NUMBER)
397 IS
398 select null
399 from ben_cwb_person_rates
400 where group_per_in_ler_id = v_group_per_in_ler_id
401 and oipl_id = -1
402 and elig_flag = 'Y';
403
404 CURSOR c_per_in_ler_ids (
405 v_group_pl_id IN NUMBER
406 , v_employee_in_bg IN NUMBER
407 , v_person_id IN NUMBER
408 , v_lf_evt_ocrd_dt IN DATE
409 )
410 IS
411 SELECT pil.per_in_ler_id
412 , pil.per_in_ler_stat_cd
413 , pil.object_version_number
414 FROM ben_per_in_ler pil
415 WHERE pil.group_pl_id = v_group_pl_id
416 AND pil.person_id = v_person_id
417 AND pil.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
418 AND pil.per_in_ler_stat_cd = 'PROCD'
419 AND ( v_employee_in_bg IS NULL
420 OR pil.business_group_id = v_employee_in_bg);
421
422 CURSOR c_range_for_thread (v_benefit_action_id IN NUMBER)
423 IS
424 SELECT ran.range_id
425 , ran.starting_person_action_id
426 , ran.ending_person_action_id
427 FROM ben_batch_ranges ran
428 WHERE ran.range_status_cd = 'U'
429 AND ran.benefit_action_id = v_benefit_action_id
430 AND ROWNUM < 2
431 FOR UPDATE OF ran.range_status_cd;
432
433 CURSOR c_person_for_thread (
434 v_benefit_action_id IN NUMBER
435 , v_start_person_action_id IN NUMBER
436 , v_end_person_action_id IN NUMBER
437 )
438 IS
439 SELECT ben.person_id
440 , ben.person_action_id
441 , ben.object_version_number
442 , ben.ler_id
443 , ben.non_person_cd
444 FROM ben_person_actions ben
445 WHERE ben.benefit_action_id = v_benefit_action_id
446 AND ben.action_status_cd <> 'P'
447 AND ben.person_action_id BETWEEN v_start_person_action_id AND v_end_person_action_id
448 ORDER BY ben.person_action_id;
449
450 CURSOR c_parameter (v_benefit_action_id IN NUMBER)
451 IS
452 SELECT ben.*
453 FROM ben_benefit_actions ben
454 WHERE ben.benefit_action_id = v_benefit_action_id;
455
456 CURSOR c_performance_promotion (v_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
457 IS
458 SELECT dsgn.perf_revw_strt_dt
459 , nvl(dsgn.ovr_perf_revw_strt_dt, dsgn.perf_revw_strt_dt)
460 , dsgn.asg_updt_eff_date
461 , dsgn.emp_interview_typ_cd
462 FROM ben_cwb_pl_dsgn dsgn
463 WHERE dsgn.pl_id = v_pl_id
464 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
465 AND dsgn.oipl_id = -1;
466
467 CURSOR c_component_reason (v_pl_id IN NUMBER, v_effective_date IN DATE)
468 IS
469 SELECT COUNT (*)
470 FROM ben_oipl_f oipl
471 , ben_opt_f opt
472 WHERE oipl.pl_id = v_pl_id
473 AND oipl.opt_id = opt.opt_id
474 and opt.component_reason is not null
475 AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
476 AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date;
477
478 CURSOR c_task_type (v_group_per_in_ler_id IN NUMBER)
479 IS
480 SELECT dsgn.ws_sub_acty_typ_cd
481 FROM ben_cwb_person_rates rt
482 , ben_cwb_pl_dsgn dsgn
483 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
484 AND rt.pl_id = dsgn.pl_id
485 AND rt.oipl_id = dsgn.oipl_id
486 AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt;
487
488 CURSOR c_ranking_info (v_group_per_in_ler_id IN NUMBER
489 ,v_perf_revw_strt_dt IN DATE
490 ,v_plan_id IN NUMBER)
491 IS
492 SELECT xtra_info.aei_information1
493 , xtra_info.aei_information2
494 , xtra_info.aei_information4
495 , xtra_info.assignment_id
496 , xtra_info.object_version_number
497 , xtra_info.assignment_extra_info_id
498 FROM per_assignment_extra_info xtra_info
499 , ben_cwb_person_info per
500 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
501 AND xtra_info.assignment_id = per.assignment_id
502 AND xtra_info.information_type = 'CWBRANK'
503 AND xtra_info.aei_information1 IS NOT NULL
504 AND xtra_info.aei_information3 = v_group_per_in_ler_id
505 AND xtra_info.aei_information5 = fnd_date.date_to_canonical(v_perf_revw_strt_dt)
506 AND xtra_info.aei_information6 = v_plan_id;
507
508 CURSOR c_ranking_info_date ( v_group_per_in_ler_id IN NUMBER
509 ,v_eff_dt IN DATE
510 ,v_ranked_by IN VARCHAR2)
511
512 IS
513 SELECT xtra_info.aei_information1
514 , xtra_info.aei_information2
515 , xtra_info.aei_information4
516 , xtra_info.assignment_id
517 , xtra_info.object_version_number
518 FROM per_assignment_extra_info xtra_info
519 , ben_cwb_person_info per
520 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
521 AND xtra_info.assignment_id = per.assignment_id
522 AND xtra_info.information_type = 'CWBRANK'
523 AND xtra_info.aei_information5 = fnd_date.date_to_canonical(v_eff_dt)
524 AND xtra_info.aei_information2 = v_ranked_by;
525
526
527 CURSOR c_element_input_value_name(v_input_value_id IN NUMBER,
528 v_element_type_id IN NUMBER,
529 v_effective_date IN DATE
530 )
531 IS
532 select pet.element_name||': '||piv.name
533 from pay_input_values_f piv,
534 pay_element_types_f pet
535 where piv.input_value_id = v_input_value_id
536 and piv.element_type_id = v_element_type_id
537 and piv.element_type_id = pet.element_type_id
538 and v_effective_date between piv.effective_start_date and piv.effective_end_date
539 and v_effective_date between pet.effective_start_date and pet.effective_end_date;
540
541 CURSOR c_prev_pay_proposal (v_group_per_in_ler_id IN NUMBER, v_effective_date IN DATE)
542 IS
543 SELECT asg.assignment_id
544 , asg.pay_basis_id
545 , ppp.proposed_salary_n
546 , ppp.object_version_number
547 FROM per_all_assignments_f asg
548 , per_pay_bases ppb
549 , per_pay_proposals ppp
550 , ben_cwb_person_info per
551 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
552 AND asg.assignment_id = per.assignment_id
553 AND v_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
554 AND ppb.pay_basis_id = asg.pay_basis_id
555 AND ppp.assignment_id = asg.assignment_id
556 AND ppp.approved = 'Y'
557 AND ppp.change_date =
558 (SELECT MAX (ppp1.change_date)
559 FROM per_pay_proposals ppp1
560 WHERE ppp1.assignment_id = asg.assignment_id
561 AND ppp1.approved = 'Y'
562 AND change_date <= v_effective_date);
563
564 CURSOR c_future_pay_proposal (v_group_per_in_ler_id IN NUMBER, v_effective_date IN DATE)
565 IS
566 SELECT ppp.proposed_salary_n
567 FROM per_pay_proposals ppp
568 , ben_cwb_person_info per
569 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
570 AND ppp.assignment_id = per.assignment_id
571 AND ppp.change_date > v_effective_date
572 AND ppp.approved='Y'; -- Bug 12635353
573
574 CURSOR c_person_info (v_group_per_in_ler_id IN NUMBER)
575 IS
576 SELECT per.business_group_id
577 , per.base_salary
578 , per.base_salary_currency
579 , initcap(base_salary_frequency) base_salary_frequency
580 , pay_annulization_factor
581 , fte_factor
582 , per.assignment_id
583 FROM ben_cwb_person_info per
584 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
585
586 CURSOR c_group_plan_name (v_group_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
587 IS
588 SELECT dsgn.NAME
589 , dsgn.group_pl_id
590 FROM ben_cwb_pl_dsgn dsgn
591 WHERE dsgn.pl_id = v_group_pl_id
592 AND dsgn.pl_id = dsgn.group_pl_id
593 AND dsgn.group_oipl_id = -1
594 AND dsgn.oipl_id = dsgn.group_oipl_id
595 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt;
596
597 CURSOR c_group_option_name (v_group_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
598 IS
599 SELECT dsgn.NAME
600 , dsgn.group_oipl_id
601 FROM ben_cwb_pl_dsgn dsgn
602 WHERE dsgn.pl_id = v_group_pl_id
603 AND dsgn.pl_id = dsgn.group_pl_id
604 AND dsgn.oipl_id = dsgn.group_oipl_id
605 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
606 AND dsgn.oipl_id <> -1;
607
608 CURSOR c_plan_name (v_group_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
609 IS
610 SELECT dsgn.NAME
611 , dsgn.pl_id
612 FROM ben_cwb_pl_dsgn dsgn
613 WHERE dsgn.group_pl_id = v_group_pl_id
614 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
615 AND dsgn.oipl_id = -1
616 AND dsgn.pl_id <> dsgn.group_pl_id;
617
618 CURSOR c_option_name (v_group_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
619 IS
620 SELECT dsgn.NAME
621 , dsgn.oipl_id
622 FROM ben_cwb_pl_dsgn dsgn
623 WHERE dsgn.group_pl_id = v_group_pl_id
624 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
625 AND dsgn.pl_id <> dsgn.group_pl_id
626 AND dsgn.oipl_id <> -1;
627
628 CURSOR c_actions (v_benefit_action_id IN NUMBER)
629 IS
630 SELECT COUNT (*) amount
631 , action_status_cd
632 FROM ben_person_actions act
633 WHERE act.benefit_action_id = v_benefit_action_id
634 AND act.action_status_cd IN ('P', 'E', 'U')
635 GROUP BY action_status_cd;
636
637 CURSOR c_posted_element(v_assignment_id IN NUMBER
638 ,v_element_type_id IN NUMBER
639 ,v_input_value_id IN NUMBER
640 ,v_effective_date IN DATE)
641 IS
642 select eev.screen_entry_value
643 from pay_element_entries_f ee,
644 pay_element_entry_values_f eev
645 where ee.assignment_id = v_assignment_id
646 and ee.element_type_id = v_element_type_id
647 and v_effective_date between ee.effective_start_date and ee.effective_end_date
648 and eev.element_entry_id = ee.element_entry_id
649 and eev.input_value_id = v_input_value_id
650 and eev.effective_start_date = ee.effective_start_date
651 and eev.effective_end_date = ee.effective_end_date
652 and eev.screen_entry_value is not null
653 order by ee.effective_start_date;
654
655 CURSOR c_posted_salary(v_pay_proposal_id IN NUMBER)
656 IS
657 select proposed_salary_n
658 from per_pay_proposals
659 where pay_proposal_id = v_pay_proposal_id;
660
661 CURSOR c_posted_rating(v_person_id IN NUMBER,
662 v_effective_date IN DATE)
663 IS
664 select perf.performance_rating
665 from per_performance_reviews perf
666 where perf.person_id = v_person_id
667 and perf.review_date = v_effective_date;
668
669 CURSOR c_slaves(v_request_id IN NUMBER)
670 IS
671 Select null
672 From fnd_concurrent_requests fnd
673 Where request_id = v_request_id
674 and status_code = 'E';
675
676 CURSOR c_overrides_perf_prom(v_group_per_in_ler_id IN NUMBER,
677 v_lf_evt_ocrd_dt IN DATE)
678 IS
679 SELECT trans.transaction_id,
680 trans.attribute1,
681 trans.attribute2
682 FROM ben_transaction trans,
683 ben_cwb_pl_dsgn dsgn
684 WHERE trans.transaction_id IN
685 (SELECT DISTINCT pl_id
686 FROM ben_cwb_person_rates rates
687 WHERE group_per_in_ler_id = v_group_per_in_ler_id)
688 AND trans.transaction_type = 'CWBPPOVDT' || to_char(v_lf_evt_ocrd_dt,'yyyy/mm/dd');
689
690
691 CURSOR c_sal_comp_rates (
692 v_group_per_in_ler_id IN NUMBER
693 , v_group_pl_id IN NUMBER
694 , v_lf_evt_orcd_dt IN DATE
695 , v_effective_date IN DATE
696 )
697 IS
698 SELECT nvl(rt.ws_val,0) ws_val
699 , rt.person_rate_id
700 , opt.component_reason
701 , dsgn.salary_change_reason
702 , dsgn.pl_id
703 , dsgn.oipl_id
704 , dsgn.group_pl_id
705 , dsgn.group_oipl_id
706 , dsgn.ws_nnmntry_uom units
707 , dsgn.ws_sub_acty_typ_cd
708 , pil.ws_mgr_id
709 , info.full_name
710 , info.employee_number
711 , info.business_group_id
712 , rt.elig_sal_val
713 , xchg.xchg_rate
714 , rt.elig_flag
715 , rt.currency
716 , rt.comp_posting_date
717 FROM ben_cwb_person_rates rt
718 , ben_oipl_f oipl
719 , ben_cwb_pl_dsgn dsgn
720 , ben_opt_f opt
721 , ben_cwb_person_info info
722 , ben_per_in_ler pil
723 , ben_cwb_xchg xchg
724 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
725 AND rt.group_pl_id = v_group_pl_id
726 --AND rt.oipl_id <> -1
727 --AND rt.elig_flag = 'Y' (for logging)
728 AND rt.lf_evt_ocrd_dt = v_lf_evt_orcd_dt
729 --AND nvl(rt.ws_val,0) <> 0
730 AND rt.pl_id = dsgn.pl_id
731 AND rt.oipl_id = dsgn.oipl_id
732 AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
733 AND rt.group_per_in_ler_id = info.group_per_in_ler_id
734 AND rt.group_per_in_ler_id = pil.per_in_ler_id
735 AND oipl.oipl_id = rt.oipl_id
736 AND oipl.opt_id = opt.opt_id
737 AND opt.component_reason is not null
738 AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
739 AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date
740 and xchg.group_pl_id = rt.group_pl_id
741 and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
742 and xchg.currency = rt.currency
743 AND exists (select null from ben_cwb_pl_dsgn where pl_id = rt.pl_id and oipl_id = -1 and nvl(do_not_process_flag,'N') <> 'Y');
744
745
746 CURSOR c_non_sal_comp_rates (v_group_per_in_ler_id IN NUMBER, v_effective_date IN DATE)
747 IS
748 SELECT rt.ws_val
749 , rt.person_rate_id
750 , rt.pl_id
751 , rt.oipl_id
752 , rt.object_version_number
753 , rt.lf_evt_ocrd_dt
754 , dsgn.ws_sub_acty_typ_cd
755 , dsgn.ws_abr_id
756 , dsgn.salary_change_reason
757 , dsgn.ws_nnmntry_uom units
758 , dsgn.acty_ref_perd_cd
759 , dsgn.business_group_id
760 , dsgn.group_pl_id
761 , dsgn.group_oipl_id
762 , pil.ws_mgr_id
763 , info.full_name
764 , info.employee_number
765 , info.assignment_id
766 , opt.component_reason
767 , info.base_salary_currency
768 , dsgn.uom_precision
769 , info.base_salary
770 , rt.elig_sal_val
771 , initcap(info.base_salary_frequency) base_salary_frequency
772 , info.pay_annulization_factor
773 , dsgn.pl_annulization_factor
774 , xchg.xchg_rate
775 , rt.elig_flag
776 , info.fte_factor
777 , rt.currency
778 , rt.comp_posting_date
779 FROM ben_cwb_person_rates rt
780 , ben_cwb_pl_dsgn dsgn
781 , ben_cwb_person_info info
782 , ben_per_in_ler pil
783 , ben_oipl_f oipl
784 , ben_opt_f opt
785 , ben_cwb_xchg xchg
786 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
787 AND rt.pl_id = dsgn.pl_id
788 AND rt.oipl_id = dsgn.oipl_id
789 --AND rt.elig_flag = 'Y' (for logging)
790 AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
791 AND rt.group_per_in_ler_id = info.group_per_in_ler_id
792 AND rt.group_per_in_ler_id = pil.per_in_ler_id
793 AND rt.oipl_id = oipl.oipl_id (+)
794 AND oipl.opt_id = opt.opt_id (+)
795 -- AND opt.component_reason(+) is null --12571024
796 and not exists (select null from ben_opt_f opt1 where opt.opt_id = opt1.opt_id and opt1.component_reason is not null) --12616726
797 AND v_effective_date BETWEEN opt.effective_start_date (+) AND opt.effective_end_date (+)
798 AND v_effective_date BETWEEN oipl.effective_start_date (+)AND oipl.effective_end_date (+)
799 and xchg.group_pl_id = rt.group_pl_id
800 and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
801 and xchg.currency = rt.currency
802 AND exists (select null from ben_cwb_pl_dsgn where pl_id = rt.pl_id and oipl_id = -1 and nvl(do_not_process_flag,'N') <> 'Y');
803
804 CURSOR c_input_value_precision(
805 v_assignment_id NUMBER
806 , v_effective_date DATE
807 )
808 IS
809 SELECT decode(piv.uom,NULL,2,'M',nvl(curr.PRECISION,2),5) PRECISION
810 FROM per_all_assignments_f asg,
811 per_pay_bases ppb,
812 pay_input_values_f piv,
813 pay_element_types_f pet,
814 fnd_currencies curr
815 WHERE asg.assignment_id = v_assignment_id
816 AND v_effective_date BETWEEN asg.effective_start_date
817 AND asg.effective_end_date
818 AND asg.pay_basis_id = ppb.pay_basis_id
819 AND ppb.input_value_id = piv.input_value_id
820 AND v_effective_date BETWEEN piv.effective_start_date
821 AND piv.effective_end_date
822 AND piv.element_type_id = pet.element_type_id
823 AND v_effective_date BETWEEN pet.effective_start_date
824 AND pet.effective_end_date
825 AND pet.input_currency_code = curr.currency_code;
826
827 CURSOR c_sal_factors(v_group_pl_id IN NUMBER,
828 v_lf_evt_ocrd_dt IN DATE,
829 v_group_per_in_ler_id IN NUMBER
830 )
831 IS
832 SELECT dsgn.pl_annulization_factor,
833 dsgn.uom_precision,
834 info.pay_annulization_factor,
835 dsgn.salary_change_reason
836 FROM ben_cwb_pl_dsgn dsgn,
837 ben_cwb_person_info info
838 WHERE dsgn.group_pl_id = v_group_pl_id
839 AND dsgn.group_pl_id = dsgn.pl_id
840 AND dsgn.oipl_id = -1
841 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
842 AND info.group_per_in_ler_id = v_group_per_in_ler_id;
843
844 --12581640
845 cursor c_payroll_run ( p_assignmant_id IN number,
846 p_posting_date IN date,
847 p_effective_date IN date) IS
848 select 'Y'
849 from per_all_assignments_f asst,
850 per_time_periods period
851 where asst.assignment_id = p_assignmant_id
852 and asst.payroll_id = period.payroll_id
853 and p_posting_date between period.start_date and period.end_date
854 and p_effective_date > period.cut_off_date;
855
856
857
858 --
859 -- ============================================================================
860 -- <<write>>
861 -- ============================================================================
862 --
863
864 PROCEDURE WRITE (p_string IN VARCHAR2)
865 IS
866 BEGIN
867 ben_batch_utils.WRITE (p_string);
868 END;
869
870 --
871 -- ============================================================================
872 -- <<write_s>>
873 -- ============================================================================
874 --
875
876 PROCEDURE write_s (p_string IN VARCHAR2)
877 IS
878 BEGIN
879 IF (g_debug_level = 'S')
880 THEN
881 WRITE (p_string);
882 END IF;
883 END;
884
885 --
886 -- ============================================================================
887 -- <<write_m>>
888 -- ============================================================================
889 --
890
891 PROCEDURE write_m (p_string IN VARCHAR2)
892 IS
893 BEGIN
894 IF ( g_debug_level = 'M'
895 OR g_debug_level = 'H'
896 OR g_debug_level = 'S')
897 THEN
898 WRITE (p_string);
899 END IF;
900 END;
901
902 --
903 -- ============================================================================
904 -- <<write_h>>
905 -- ============================================================================
906 --
907
908 PROCEDURE write_h (p_string IN VARCHAR2)
909 IS
910 BEGIN
911 IF ( g_debug_level = 'H'
912 OR g_debug_level = 'S')
913 THEN
914 WRITE (p_string);
915 END IF;
916 END;
917
918
919 --
920 -- ============================================================================
921 -- <<change_task_status>>
922 -- ============================================================================
923 --
924
925 PROCEDURE change_task_status( p_per_in_ler_id IN NUMBER ,
926 p_effective_date IN DATE)
927 IS
928
929 cursor c_cwb_person_task is
930 select t.task_id, t.object_version_number, t.status_cd, t.task_last_update_date, g.wksht_grp_cd
931 from ben_cwb_person_tasks t,
932 ben_cwb_wksht_grp g
933 where t.group_per_in_ler_id = p_per_in_ler_id
934 and CWB_WKSHT_GRP_ID = t.task_id;
935
936 BEGIN
937
938 FOR l_task in c_cwb_person_task LOOP
939 IF(l_task.object_version_number IS NOT NULL AND l_task.task_id IS NOT NULL AND (nvl(l_task.status_cd,'XX') = 'CO' AND nvl(l_task.wksht_grp_cd,'XX') = 'RVW')) THEN
940 BEN_CWB_PERSON_TASKS_API.update_person_task
941 ( p_group_per_in_ler_id => p_per_in_ler_id
942 ,p_task_id => l_task.task_id
943 ,p_status_cd => 'IP'
944 ,p_task_last_update_date => nvl(p_effective_date,l_task.task_last_update_date) --additional fix for bug 12581404
945 ,p_object_version_number => l_task.object_version_number
946 );
947 ELSIF(l_task.object_version_number IS NOT NULL AND l_task.task_id IS NOT NULL AND (nvl(l_task.status_cd,'XX') = 'CO' AND nvl(l_task.wksht_grp_cd,'RVW') <> 'RVW')) THEN
948 BEN_CWB_PERSON_TASKS_API.update_person_task
949 ( p_group_per_in_ler_id => p_per_in_ler_id
950 ,p_task_id => l_task.task_id
951 ,p_status_cd => 'IP'
952 ,p_object_version_number => l_task.object_version_number
953 );
954 END IF;
955 END LOOP;
956
957
958 EXCEPTION
959 WHEN OTHERS THEN
960 WRITE(SQLERRM);
961 write('Change Task status for p_per_in_ler_id '||p_per_in_ler_id||' errored');
962 hr_utility.raise_error;
963 END;
964
965
966 --
967 -- ============================================================================
968 -- <<process_access>>
969 -- ============================================================================
970 --
971
972 PROCEDURE process_access( p_group_pl_id IN NUMBER
973 , p_lf_evt_orcd_date IN DATE
974 , p_mgr_per_id IN g_number_type
975 , p_validate IN VARCHAR2 DEFAULT 'N'
976 , p_send_fyi IN VARCHAR2 DEFAULT 'N'
977 , p_effective_date IN DATE
978 )
979 IS
980
981 CURSOR c_notify (
982 v_group_per_in_ler_id IN NUMBER
983 , v_group_pl_id IN NUMBER
984 , v_group_oipl_id IN NUMBER
985 )
986 IS
987 SELECT per.full_name
988 ,per.person_id
989 ,per.group_per_in_ler_id
990 ,pl.name plan_name
991 FROM ben_cwb_pl_dsgn pl
992 ,ben_cwb_person_info per
993 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
994 and per.group_pl_id = pl.pl_id
995 and pl.oipl_id = -1;
996
997 --12580980
998 CURSOR c_mrg_access_change (
999 v_mgr_per_in_ler_id IN NUMBER
1000 ) IS
1001 SELECT distinct hrchy.mgr_per_in_ler_id mgr_per_in_ler_id
1002 from ben_cwb_group_hrchy hrchy,
1003 ben_per_in_ler pil
1004 where hrchy.mgr_per_in_ler_id = v_mgr_per_in_ler_id
1005 and hrchy.emp_per_in_ler_id = pil.per_in_ler_id
1006 and hrchy.lvl_num > 0
1007 and pil.per_in_ler_stat_cd = 'STRTD'
1008 AND pil.group_pl_id = p_group_pl_id
1009 AND pil.lf_evt_ocrd_dt = p_lf_evt_orcd_date;
1010
1011 l_grp_ovn c_grp_ovn%ROWTYPE;
1012 l_notify c_notify%ROWTYPE;
1013 l_emps_not_found BOOLEAN := FALSE;
1014 l_no_of_man_picked NUMBER := 0;
1015 l_no_of_man_access_changed NUMBER := 0;
1016 l_transaction_id NUMBER;
1017 l_mrg_access_change c_mrg_access_change%ROWTYPE;
1018 l_validate_flag BOOLEAN := FALSE;
1019 BEGIN
1020
1021 g_proc := 'process_access';
1022 g_actn := 'processing access routine...';
1023 write(g_actn);
1024 SAVEPOINT cwb_post_process_access;
1025
1026 FOR l_count IN 1 .. p_mgr_per_id.COUNT LOOP
1027
1028 write_h('mgr_per_in_ler_id is '||p_mgr_per_id(l_count));
1029 l_no_of_man_picked := l_no_of_man_picked + 1;
1030
1031 OPEN c_grp_ovn (p_mgr_per_id(l_count), p_group_pl_id, -1);
1032
1033 FETCH c_grp_ovn
1034 INTO l_grp_ovn;
1035 CLOSE c_grp_ovn;
1036
1037 OPEN c_mrg_access_change (p_mgr_per_id(l_count));
1038 FETCH c_mrg_access_change
1039 INTO l_mrg_access_change;
1040
1041 IF(c_mrg_access_change%FOUND) THEN
1042
1043 write_h('l_mrg_access_change.mgr_per_in_ler_id is '||l_mrg_access_change.mgr_per_in_ler_id);
1044 write_h('l_grp_ovn.access_cd is '||l_grp_ovn.access_cd || ' : l_grp_ovn.approval_cd is '||l_grp_ovn.approval_cd || ' : l_grp_ovn.submit_cd is '||l_grp_ovn.submit_cd);
1045
1046 IF l_grp_ovn.object_version_number IS NOT NULL AND
1047 (nvl(l_grp_ovn.access_cd,'UP') = 'RO' OR nvl(l_grp_ovn.approval_cd, 'AP') = 'PR' OR nvl(l_grp_ovn.submit_cd,'SU') = 'SU')
1048 THEN
1049 write_m('Access, Approval and Submit code updated for ' || l_grp_ovn.full_name);
1050 BEGIN
1051 IF (p_validate = 'Y') THEN
1052 l_validate_flag := true;
1053 END IF;
1054 ben_cwb_person_groups_api.update_group_budget
1055 (p_validate => l_validate_flag
1056 , p_group_per_in_ler_id => p_mgr_per_id(l_count)
1057 , p_group_pl_id => p_group_pl_id
1058 , p_group_oipl_id => -1
1059 , p_access_cd => 'UP'
1060 , p_approval_date => null
1061 , p_approval_cd => null
1062 , p_submit_cd => 'NS'
1063 , p_submit_date => null
1064 , p_object_version_number => l_grp_ovn.object_version_number
1065 );
1066 l_no_of_man_access_changed := l_no_of_man_access_changed +1;
1067 change_task_status(p_mgr_per_id(l_count),p_effective_date);
1068
1069 IF(p_send_fyi = 'Y' ) THEN
1070 OPEN c_notify (p_mgr_per_id(l_count), p_group_pl_id, -1);
1071 FETCH c_notify INTO l_notify;
1072 CLOSE c_notify;
1073
1074 insert into ben_transaction ( transaction_id,
1075 transaction_type,
1076 attribute1, -- from_person_id,
1077 attribute2, -- to_person_id,
1078 attribute3, -- to_per_in_ler_id,
1079 attribute4, -- plan_name
1080 attribute5, -- for_strt_dt
1081 attribute6, -- for_end_dt
1082 attribute7, -- new_access_cd
1083 attribute40,-- comments
1084 attribute9, -- last updated date/time
1085 attribute10, -- old_access_cd
1086 attribute11, -- requestor first name
1087 attribute12 -- requestor last name
1088 )
1089 values ( ben_transaction_s.nextval,
1090 'CWBNTF',
1091 null, --p_from_person_id,
1092 l_notify.person_id,
1093 l_notify.group_per_in_ler_id,
1094 l_notify.plan_name,
1095 null,
1096 null,
1097 hr_general.decode_lookup('BEN_WS_ACC', 'UP' ),
1098 'Your worksheet is now available for further updates. Please make any changes as required and re-submit for approval.' ,
1099 fnd_date.date_to_canonical(sysdate),
1100 hr_general.decode_lookup('BEN_WS_ACC', l_grp_ovn.access_cd ),
1101 null, --l_requestor_first_name,
1102 'System Administrator' --l_requestor_last_name
1103 )
1104 returning transaction_id into l_transaction_id ;
1105
1106 ben_cwb_wf_ntf.cwb_fyi_ntf_api (l_transaction_id,
1107 'ACCESS',
1108 l_notify.person_id,
1109 -99,--for sysadmin --p_from_person_id,
1110 l_notify.group_per_in_ler_id );
1111 write('FYI Notification sent to ' || l_notify.full_name);
1112 END IF;
1113
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 WRITE(SQLERRM);
1117 write('Access processing for '||p_mgr_per_id(l_count)||'errored');
1118 END;
1119
1120 ELSE
1121 write_m('Access and approval cd not update for ' || l_grp_ovn.full_name || ' as status is not RO');
1122 END IF;
1123 END IF;
1124 CLOSE c_mrg_access_change;
1125 write_h('--------------------------------------');
1126 END LOOP;
1127 -- CLOSE c_pils_for_access;
1128
1129 WRITE('Number of persons picked for access change is ' || l_no_of_man_picked);
1130 WRITE('Number of persons access changed is '||l_no_of_man_access_changed);
1131
1132 IF (p_validate = 'Y')
1133 THEN
1134 g_actn := 'Running in rollback mode, access processing rolled back...';
1135 WRITE (g_actn);
1136 ROLLBACK TO cwb_post_process_access;
1137 END IF;
1138
1139 END;
1140
1141 --
1142 -- ============================================================================
1143 -- <<End_process>>
1144 -- ============================================================================
1145 --
1146 PROCEDURE end_process (
1147 p_benefit_action_id IN NUMBER
1148 , p_person_selected IN NUMBER
1149 , p_business_group_id IN NUMBER DEFAULT NULL
1150 )
1151 IS
1152 l_actions c_actions%ROWTYPE;
1153 l_batch_proc_id NUMBER;
1154 l_object_version_number NUMBER;
1155 BEGIN
1156 --
1157 -- Get totals for unprocessed, processed successfully and errored
1158 --
1159 g_proc := 'end_process';
1160 OPEN c_actions (p_benefit_action_id);
1161
1162 LOOP
1163 FETCH c_actions
1164 INTO l_actions;
1165
1166 EXIT WHEN c_actions%NOTFOUND;
1167
1168 IF l_actions.action_status_cd = 'P'
1169 THEN
1170 g_exec_param_rec.persons_proc_succ := l_actions.amount;
1171 ELSIF l_actions.action_status_cd = 'E'
1172 THEN
1173 g_exec_param_rec.persons_errored := l_actions.amount;
1174 END IF;
1175 END LOOP;
1176
1177 CLOSE c_actions;
1178
1179 OPEN c_error_per_summary (p_benefit_action_id);
1180
1181 FETCH c_error_per_summary
1182 INTO g_exec_param_rec.persons_errored;
1183
1184 CLOSE c_error_per_summary;
1185
1186 OPEN c_succ_per_summary (p_benefit_action_id);
1187
1188 FETCH c_succ_per_summary
1189 INTO g_exec_param_rec.persons_proc_succ;
1190
1191 CLOSE c_succ_per_summary;
1192
1193 OPEN c_lf_evt_open_summary (p_benefit_action_id);
1194
1195 FETCH c_lf_evt_open_summary
1196 INTO g_exec_param_rec.lf_evt_not_closed;
1197
1198 CLOSE c_lf_evt_open_summary;
1199
1200 OPEN c_lf_evt_close_summary (p_benefit_action_id);
1201
1202 FETCH c_lf_evt_close_summary
1203 INTO g_exec_param_rec.lf_evt_closed;
1204
1205 CLOSE c_lf_evt_close_summary;
1206
1207 --
1208 -- Set value of number of persons processed
1209 --
1210 g_exec_param_rec.persons_selected :=
1211 g_exec_param_rec.persons_errored + g_exec_param_rec.persons_proc_succ;
1212 ben_batch_proc_info_api.create_batch_proc_info
1213 (p_validate => FALSE
1214 , p_batch_proc_id => l_batch_proc_id
1215 , p_benefit_action_id => p_benefit_action_id
1216 , p_strt_dt => TRUNC
1217 (g_exec_param_rec.start_date
1218 )
1219 , p_end_dt => TRUNC (SYSDATE)
1220 , p_strt_tm => TO_CHAR
1221 (g_exec_param_rec.start_date
1222 , 'HH24:MI:SS'
1223 )
1224 , p_end_tm => TO_CHAR (SYSDATE
1225 , 'HH24:MI:SS'
1226 )
1227 , p_elpsd_tm => TO_CHAR
1228 ((DBMS_UTILITY.get_time
1229 - g_exec_param_rec.start_time
1230 )
1231 / 100
1232 )
1233 || ' seconds'
1234 , p_per_slctd => g_exec_param_rec.persons_selected
1235 , p_per_proc => g_exec_param_rec.lf_evt_closed
1236 , p_per_unproc => g_exec_param_rec.lf_evt_not_closed
1237 , p_per_proc_succ => g_exec_param_rec.persons_proc_succ
1238 , p_per_err => g_exec_param_rec.persons_errored
1239 , p_business_group_id => p_business_group_id
1240 , p_object_version_number => l_object_version_number
1241 );
1242 COMMIT;
1243 END end_process;
1244
1245 --
1246 -- ============================================================================
1247 -- << Procedure: init >>
1248 -- ============================================================================
1249 --
1250 PROCEDURE init (p_group_plan_id IN NUMBER, p_lf_evt_ocrd_dt IN DATE)
1251 IS
1252 l_group_plan_rec c_group_plan_name%ROWTYPE;
1253 l_group_option_rec c_group_option_name%ROWTYPE;
1254 l_actual_plan_rec c_plan_name%ROWTYPE;
1255 l_actual_option_rec c_option_name%ROWTYPE;
1256 BEGIN
1257 g_proc := 'init';
1258
1259 g_cwb_rpt_person.person_rate_id :=null;
1260 g_cwb_rpt_person.pl_id :=null;
1261 g_cwb_rpt_person.person_id :=null;
1262 g_cwb_rpt_person.oipl_id :=null;
1263 g_cwb_rpt_person.group_pl_id :=null;
1264 g_cwb_rpt_person.group_oipl_id :=null;
1265 g_cwb_rpt_person.full_name :=null;
1266 g_cwb_rpt_person.emp_number :=null;
1267 g_cwb_rpt_person. business_group_name :=null;
1268 g_cwb_rpt_person.business_group_id :=null;
1269 g_cwb_rpt_person.manager_name :=null;
1270 g_cwb_rpt_person.ws_mgr_id :=null;
1271 g_cwb_rpt_person.pl_name :=null;
1272 g_cwb_rpt_person.opt_name :=null;
1273 g_cwb_rpt_person.amount :=null;
1274 g_cwb_rpt_person.units :=null;
1275 g_cwb_rpt_person.performance_rating :=null;
1276 g_cwb_rpt_person.assignment_changed :=null;
1277 g_cwb_rpt_person.status :=null;
1278 g_cwb_rpt_person.lf_evt_closed :=null;
1279 g_cwb_rpt_person.error_or_warning_text :=null;
1280 g_cwb_rpt_person.benefit_action_id :=null;
1281 OPEN c_group_plan_name (p_group_plan_id, p_lf_evt_ocrd_dt);
1282
1283 FETCH c_group_plan_name
1284 INTO l_group_plan_rec;
1285
1286 g_group_plan_name := l_group_plan_rec.NAME;
1287
1288 CLOSE c_group_plan_name;
1289
1290 OPEN c_group_option_name (p_group_plan_id, p_lf_evt_ocrd_dt);
1291
1292 LOOP
1293 FETCH c_group_option_name
1294 INTO l_group_option_rec;
1295
1296 EXIT WHEN c_group_option_name%NOTFOUND;
1297 g_cache_group_options (l_group_option_rec.group_oipl_id) := l_group_option_rec.NAME;
1298 END LOOP;
1299
1300 CLOSE c_group_option_name;
1301
1302 OPEN c_plan_name (p_group_plan_id, p_lf_evt_ocrd_dt);
1303
1304 LOOP
1305 FETCH c_plan_name
1306 INTO l_actual_plan_rec;
1307
1308 EXIT WHEN c_plan_name%NOTFOUND;
1309 g_cache_actual_plans (l_actual_plan_rec.pl_id) := l_actual_plan_rec.NAME;
1310 END LOOP;
1311
1312 CLOSE c_plan_name;
1313
1314 OPEN c_option_name (p_group_plan_id, p_lf_evt_ocrd_dt);
1315
1316 LOOP
1317 FETCH c_option_name
1318 INTO l_actual_option_rec;
1319
1320 EXIT WHEN c_option_name%NOTFOUND;
1321 g_cache_actual_options (l_actual_option_rec.oipl_id) := l_actual_option_rec.NAME;
1322 END LOOP;
1323
1324 CLOSE c_option_name;
1325 END;
1326
1327 --
1328 -- ============================================================================
1329 -- << Procedure: insert_person_actions >>
1330 -- ============================================================================
1331 --
1332 PROCEDURE insert_person_actions (
1333 p_per_actn_id_array IN g_number_type
1334 , p_per_id IN g_number_type
1335 , p_group_per_in_ler_id IN g_number_type
1336 , p_benefit_action_id IN NUMBER
1337 , p_is_placeholder IN g_number_type
1338 )
1339 IS
1340 l_num_rows NUMBER := p_per_actn_id_array.COUNT;
1341 BEGIN
1342 g_proc := 'insert_person_actions';
1343 write('Time before inserting person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1344 FORALL l_count IN 1 .. p_per_actn_id_array.COUNT
1345 --
1346 INSERT INTO ben_person_actions
1347 (person_action_id
1348 , person_id
1349 , ler_id
1350 , benefit_action_id
1351 , action_status_cd
1352 , object_version_number
1353 , NON_PERSON_CD
1354 )
1355 VALUES (p_per_actn_id_array (l_count)
1356 , p_per_id (l_count)
1357 , p_group_per_in_ler_id (l_count)
1358 , p_benefit_action_id
1359 , 'U'
1360 , 1
1361 , decode(p_is_placeholder (l_count),1,'Y','N')
1362 );
1363
1364 write_m ('Time before inserting ben batch ranges '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1365 INSERT INTO ben_batch_ranges
1366 (range_id
1367 , benefit_action_id
1368 , range_status_cd
1369 , starting_person_action_id
1370 , ending_person_action_id
1371 , object_version_number
1372 )
1373 VALUES (ben_batch_ranges_s.NEXTVAL
1374 , p_benefit_action_id
1375 , 'U'
1376 , p_per_actn_id_array (1)
1377 , p_per_actn_id_array (l_num_rows)
1378 , 1
1379 );
1380 write_m ('Time at end of insert person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1381 END;
1382
1383 --
1384 -- ============================================================================
1385 -- << Procedure: print_cache >>
1386 -- ============================================================================
1387 --
1388 PROCEDURE print_cache
1389 IS
1390 l_evaluated NUMBER (9) := 0;
1391 l_successful NUMBER (9) := 0;
1392 l_error NUMBER (9) := 0;
1393 l_closed_le NUMBER (9) := 0;
1394 l_open_le NUMBER (9) := 0;
1395 l_previous NUMBER := -1;
1396 l_message_number NUMBER;
1397 l_message_text VARCHAR2 (2000);
1398 BEGIN
1399 g_proc := 'print_cache';
1400 WRITE ('Time before printing cache '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1401 WRITE ('Populating records into reporting tables...');
1402 WRITE_H ('g_cache_cwb_rpt_person.COUNT = ' || g_cache_cwb_rpt_person.COUNT);
1403 --
1404 FOR i IN 1 .. g_cache_cwb_rpt_person.COUNT
1405 LOOP
1406 BEGIN
1407 IF(g_cache_cwb_rpt_person (i).status='E') THEN
1408 l_message_number := fnd_number.canonical_to_number
1409 (substr(g_cache_cwb_rpt_person (i).error_or_warning_text,1,
1410 instr(g_cache_cwb_rpt_person (i).error_or_warning_text,' ')));
1411 END IF;
1412 l_message_text := g_cache_cwb_rpt_person (i).error_or_warning_text;
1413 EXCEPTION
1414 WHEN others THEN
1415 l_message_text := '-1 Oracle Internal Error. Check logfile for details.';
1416 END;
1417 --12607360
1418 IF(g_cache_cwb_rpt_person (i).ws_sub_acty_typ_cd = 'ICM7' AND
1419 nvl(g_cache_cwb_rpt_person (i).amount_posted,0) = 0 AND g_cache_cwb_rpt_person (i).status <>'E') THEN
1420 -- g_cache_cwb_rpt_person (i).new_sal:=g_cache_cwb_rpt_person (i).prev_sal;
1421 g_cache_cwb_rpt_person (i).new_sal:=g_cache_cwb_rpt_person (i).prev_sal - g_cache_cwb_rpt_person (i).amount;
1422 ELSE
1423 g_cache_cwb_rpt_person (i).new_sal:= null;
1424 END IF;
1425 /*
1426 g_cache_cwb_rpt_person (i).amount is null OR
1427 g_cache_cwb_rpt_person (i).amount = 0) THEN
1428 g_cache_cwb_rpt_person (i).prev_sal := null;
1429 g_cache_cwb_rpt_person (i).new_sal := null;
1430 END IF;*/
1431 INSERT INTO ben_cwb_rpt_detail
1432 (benefit_action_id
1433 , person_rate_id
1434 , pl_id
1435 , person_id
1436 , country_code
1437 , group_per_in_ler_id
1438 , oipl_id
1439 , group_pl_id
1440 , group_oipl_id
1441 , ws_mgr_id
1442 , lf_evt_ocrd_dt
1443 , full_name
1444 , employee_number
1445 , business_group_id
1446 , business_group_name
1447 , manager_name
1448 , pl_name
1449 , opt_name
1450 , amount
1451 , units
1452 , performance_rating
1453 , assignment_changed_flag
1454 , status_cd
1455 , lf_evt_closed_flag
1456 , error_or_warning_text
1457 , cwb_rpt_detail_id
1458 , base_salary_currency
1459 , currency
1460 , base_salary
1461 , elig_salary
1462 , percent_of_elig_sal
1463 , base_sal_freq
1464 , pay_ann_factor
1465 , pl_ann_factor
1466 , conversion_factor
1467 , adjusted_amount
1468 , prev_sal
1469 , new_sal
1470 , pay_proposal_id
1471 , pay_basis_id
1472 , element_entry_id
1473 , exchange_rate
1474 , effective_date
1475 , reason
1476 , eligibility
1477 , fte_factor
1478 , element_input_value
1479 , amount_posted
1480 , assignment_id
1481 , element_entry_value_id
1482 , input_value_id
1483 , element_type_id
1484 , eev_screen_entry_value
1485 , elmnt_processing_type
1486 , uom_precision
1487 , ws_sub_acty_typ_cd
1488 , posted_rating
1489 , rating_type
1490 , rating_date
1491 , prior_job
1492 , posted_job
1493 , proposed_job
1494 , prior_position
1495 , posted_position
1496 , proposed_position
1497 , prior_grade
1498 , posted_grade
1499 , proposed_grade
1500 , prior_group
1501 , posted_group
1502 , proposed_group
1503 , prior_flex1
1504 , posted_flex1
1505 , proposed_flex1
1506 , prior_flex2
1507 , posted_flex2
1508 , proposed_flex2
1509 , prior_flex3
1510 , posted_flex3
1511 , proposed_flex3
1512 , prior_flex4
1513 , posted_flex4
1514 , proposed_flex4
1515 , prior_flex5
1516 , posted_flex5
1517 , proposed_flex5
1518 , prior_flex6
1519 , posted_flex6
1520 , proposed_flex6
1521 , prior_flex7
1522 , posted_flex7
1523 , proposed_flex7
1524 , prior_flex8
1525 , posted_flex8
1526 , proposed_flex8
1527 , prior_flex9
1528 , posted_flex9
1529 , proposed_flex9
1530 , prior_flex10
1531 , posted_flex10
1532 , proposed_flex10
1533 , prior_flex11
1534 , posted_flex11
1535 , proposed_flex11
1536 , prior_flex12
1537 , posted_flex12
1538 , proposed_flex12
1539 , prior_flex13
1540 , posted_flex13
1541 , proposed_flex13
1542 , prior_flex14
1543 , posted_flex14
1544 , proposed_flex14
1545 , prior_flex15
1546 , posted_flex15
1547 , proposed_flex15
1548 , prior_flex16
1549 , posted_flex16
1550 , proposed_flex16
1551 , prior_flex17
1552 , posted_flex17
1553 , proposed_flex17
1554 , prior_flex18
1555 , posted_flex18
1556 , proposed_flex18
1557 , prior_flex19
1558 , posted_flex19
1559 , proposed_flex19
1560 , prior_flex20
1561 , posted_flex20
1562 , proposed_flex20
1563 , prior_flex21
1564 , posted_flex21
1565 , proposed_flex21
1566 , prior_flex22
1567 , posted_flex22
1568 , proposed_flex22
1569 , prior_flex23
1570 , posted_flex23
1571 , proposed_flex23
1572 , prior_flex24
1573 , posted_flex24
1574 , proposed_flex24
1575 , prior_flex25
1576 , posted_flex25
1577 , proposed_flex25
1578 , prior_flex26
1579 , posted_flex26
1580 , proposed_flex26
1581 , prior_flex27
1582 , posted_flex27
1583 , proposed_flex27
1584 , prior_flex28
1585 , posted_flex28
1586 , proposed_flex28
1587 , prior_flex29
1588 , posted_flex29
1589 , proposed_flex29
1590 , prior_flex30
1591 , posted_flex30
1592 , proposed_flex30
1593 , asgn_change_reason
1594 , pending_workflow
1595 , new_rpt
1596 , prev_eev_screen_entry_value)
1597 VALUES (benutils.g_benefit_action_id
1598 , g_cache_cwb_rpt_person (i).person_rate_id
1599 , g_cache_cwb_rpt_person (i).pl_id
1600 , g_cache_cwb_rpt_person (i).person_id
1601 , g_cache_cwb_rpt_person (i).country_code
1602 , g_cache_cwb_rpt_person (i).group_per_in_ler_id
1603 , g_cache_cwb_rpt_person (i).oipl_id
1604 , g_cache_cwb_rpt_person (i).group_pl_id
1605 , g_cache_cwb_rpt_person (i).group_oipl_id
1606 , g_cache_cwb_rpt_person (i).ws_mgr_id
1607 , g_cache_cwb_rpt_person (i).lf_evt_ocrd_date
1608 , g_cache_cwb_rpt_person (i).full_name
1609 , g_cache_cwb_rpt_person (i).emp_number
1610 , g_cache_cwb_rpt_person (i).business_group_id
1611 , g_cache_cwb_rpt_person (i).business_group_name
1612 , g_cache_cwb_rpt_person (i).manager_name
1613 , g_cache_cwb_rpt_person (i).pl_name
1614 , g_cache_cwb_rpt_person (i).opt_name
1615 , g_cache_cwb_rpt_person (i).amount
1616 , g_cache_cwb_rpt_person (i).units
1617 , g_cache_cwb_rpt_person (i).performance_rating
1618 , g_cache_cwb_rpt_person (i).assignment_changed
1619 , g_cache_cwb_rpt_person (i).status
1620 , g_cache_cwb_rpt_person (i).lf_evt_closed
1621 , l_message_text
1622 , ben_cwb_rpt_detail_s.NEXTVAL
1623 , g_cache_cwb_rpt_person (i).base_salary_currency
1624 , g_cache_cwb_rpt_person (i).currency
1625 , round(g_cache_cwb_rpt_person (i).base_salary*g_cache_cwb_rpt_person (i).pay_ann_factor/g_cache_cwb_rpt_person (i).pl_ann_factor,
1626 nvl(g_cache_cwb_rpt_person (i).uom_precision,2))
1627 , g_cache_cwb_rpt_person (i).elig_salary
1628 , g_cache_cwb_rpt_person (i).percent_of_elig_sal
1629 , g_cache_cwb_rpt_person (i).base_sal_freq
1630 , g_cache_cwb_rpt_person (i).pay_ann_factor
1631 , g_cache_cwb_rpt_person (i).pl_ann_factor
1632 , g_cache_cwb_rpt_person (i).conversion_factor
1633 , g_cache_cwb_rpt_person (i).adjusted_amount
1634 , g_cache_cwb_rpt_person (i).prev_sal
1635 , g_cache_cwb_rpt_person (i).new_sal
1636 , g_cache_cwb_rpt_person (i).pay_proposal_id
1637 , g_cache_cwb_rpt_person (i).pay_basis_id
1638 , g_cache_cwb_rpt_person (i).element_entry_id
1639 , g_cache_cwb_rpt_person (i).exchange_rate
1640 , g_cache_cwb_rpt_person (i).effective_date
1641 , substr(hr_general.decode_lookup('PROPOSAL_REASON',g_cache_cwb_rpt_person (i).reason),1,30) -- bug : 7042887/10149579
1642 , g_cache_cwb_rpt_person (i).eligibility
1643 , g_cache_cwb_rpt_person (i).fte_factor
1644 , g_cache_cwb_rpt_person (i).element_input_value
1645 , nvl(g_cache_cwb_rpt_person (i).amount_posted,0)
1646 , g_cache_cwb_rpt_person (i).assignment_id
1647 , g_cache_cwb_rpt_person (i).element_entry_value_id
1648 , g_cache_cwb_rpt_person (i).input_value_id
1649 , g_cache_cwb_rpt_person (i).element_type_id
1650 , g_cache_cwb_rpt_person (i).eev_screen_entry_value
1651 , g_cache_cwb_rpt_person (i).elmnt_processing_type
1652 , g_cache_cwb_rpt_person (i).uom_precision
1653 , g_cache_cwb_rpt_person (i).ws_sub_acty_typ_cd
1654 , substr(g_cache_cwb_rpt_person (i).posted_rating,1,30)
1655 , substr(g_cache_cwb_rpt_person (i).rating_type,1,30)
1656 , substr(g_cache_cwb_rpt_person (i).rating_date,1,30)
1657 , substr(g_cache_cwb_rpt_person (i).prior_job,1,700) --sg
1658 , substr(g_cache_cwb_rpt_person (i).posted_job,1,700) --sg
1659 , substr(g_cache_cwb_rpt_person (i).proposed_job,1,700) --sg
1660 , substr(g_cache_cwb_rpt_person (i).prior_position,1,240) --sg
1661 , substr(g_cache_cwb_rpt_person (i).posted_position,1,240) --sg
1662 , substr(g_cache_cwb_rpt_person (i).proposed_position,1,240) --sg
1663 , substr(g_cache_cwb_rpt_person (i).prior_grade,1,240) --sg
1664 , substr(g_cache_cwb_rpt_person (i).posted_grade,1,240) --sg
1665 , substr(g_cache_cwb_rpt_person (i).proposed_grade,1,240) --sg
1666 , substr(g_cache_cwb_rpt_person (i).prior_group,1,240) --sg
1667 , substr(g_cache_cwb_rpt_person (i).posted_group,1,240) --sg
1668 , substr(g_cache_cwb_rpt_person (i).proposed_group,1,240) --sg
1669 , substr(g_cache_cwb_rpt_person (i).prior_flex1,1,30)
1670 , substr(g_cache_cwb_rpt_person (i).posted_flex1,1,30)
1671 , substr(g_cache_cwb_rpt_person (i).proposed_flex1,1,30)
1672 , substr(g_cache_cwb_rpt_person (i).prior_flex2,1,30)
1673 , substr(g_cache_cwb_rpt_person (i).posted_flex2,1,30)
1674 , substr(g_cache_cwb_rpt_person (i).proposed_flex2,1,30)
1675 , substr(g_cache_cwb_rpt_person (i).prior_flex3,1,30)
1676 , substr(g_cache_cwb_rpt_person (i).posted_flex3,1,30)
1677 , substr(g_cache_cwb_rpt_person (i).proposed_flex3,1,30)
1678 , substr(g_cache_cwb_rpt_person (i).prior_flex4,1,30)
1679 , substr(g_cache_cwb_rpt_person (i).posted_flex4,1,30)
1680 , substr(g_cache_cwb_rpt_person (i).proposed_flex4,1,30)
1681 , substr(g_cache_cwb_rpt_person (i).prior_flex5,1,30)
1682 , substr(g_cache_cwb_rpt_person (i).posted_flex5,1,30)
1683 , substr(g_cache_cwb_rpt_person (i).proposed_flex5,1,30)
1684 , substr(g_cache_cwb_rpt_person (i).prior_flex6,1,30)
1685 , substr(g_cache_cwb_rpt_person (i).posted_flex6,1,30)
1686 , substr(g_cache_cwb_rpt_person (i).proposed_flex6,1,30)
1687 , substr(g_cache_cwb_rpt_person (i).prior_flex7,1,30)
1688 , substr(g_cache_cwb_rpt_person (i).posted_flex7,1,30)
1689 , substr(g_cache_cwb_rpt_person (i).proposed_flex7,1,30)
1690 , substr(g_cache_cwb_rpt_person (i).prior_flex8,1,30)
1691 , substr(g_cache_cwb_rpt_person (i).posted_flex8,1,30)
1692 , substr(g_cache_cwb_rpt_person (i).proposed_flex8,1,30)
1693 , substr(g_cache_cwb_rpt_person (i).prior_flex9,1,30)
1694 , substr(g_cache_cwb_rpt_person (i).posted_flex9,1,30)
1695 , substr(g_cache_cwb_rpt_person (i).proposed_flex9,1,30)
1696 , substr(g_cache_cwb_rpt_person (i).prior_flex10,1,30)
1697 , substr(g_cache_cwb_rpt_person (i).posted_flex10,1,30)
1698 , substr(g_cache_cwb_rpt_person (i).proposed_flex10,1,30)
1699 , substr(g_cache_cwb_rpt_person (i).prior_flex11,1,30)
1700 , substr(g_cache_cwb_rpt_person (i).posted_flex11,1,30)
1701 , substr(g_cache_cwb_rpt_person (i).proposed_flex11,1,30)
1702 , substr(g_cache_cwb_rpt_person (i).prior_flex12,1,30)
1703 , substr(g_cache_cwb_rpt_person (i).posted_flex12,1,30)
1704 , substr(g_cache_cwb_rpt_person (i).proposed_flex12,1,30)
1705 , substr(g_cache_cwb_rpt_person (i).prior_flex13,1,30)
1706 , substr(g_cache_cwb_rpt_person (i).posted_flex13,1,30)
1707 , substr(g_cache_cwb_rpt_person (i).proposed_flex13,1,30)
1708 , substr(g_cache_cwb_rpt_person (i).prior_flex14,1,30)
1709 , substr(g_cache_cwb_rpt_person (i).posted_flex14,1,30)
1710 , substr(g_cache_cwb_rpt_person (i).proposed_flex14,1,30)
1711 , substr(g_cache_cwb_rpt_person (i).prior_flex15,1,30)
1712 , substr(g_cache_cwb_rpt_person (i).posted_flex15,1,30)
1713 , substr(g_cache_cwb_rpt_person (i).proposed_flex15,1,30)
1714 , substr(g_cache_cwb_rpt_person (i).prior_flex16,1,30)
1715 , substr(g_cache_cwb_rpt_person (i).posted_flex16,1,30)
1716 , substr(g_cache_cwb_rpt_person (i).proposed_flex16,1,30)
1717 , substr(g_cache_cwb_rpt_person (i).prior_flex17,1,30)
1718 , substr(g_cache_cwb_rpt_person (i).posted_flex17,1,30)
1719 , substr(g_cache_cwb_rpt_person (i).proposed_flex17,1,30)
1720 , substr(g_cache_cwb_rpt_person (i).prior_flex18,1,30)
1721 , substr(g_cache_cwb_rpt_person (i).posted_flex18,1,30)
1722 , substr(g_cache_cwb_rpt_person (i).proposed_flex18,1,30)
1723 , substr(g_cache_cwb_rpt_person (i).prior_flex19,1,30)
1724 , substr(g_cache_cwb_rpt_person (i).posted_flex19,1,30)
1725 , substr(g_cache_cwb_rpt_person (i).proposed_flex19,1,30)
1726 , substr(g_cache_cwb_rpt_person (i).prior_flex20,1,30)
1727 , substr(g_cache_cwb_rpt_person (i).posted_flex20,1,30)
1728 , substr(g_cache_cwb_rpt_person (i).proposed_flex20,1,30)
1729 , substr(g_cache_cwb_rpt_person (i).prior_flex21,1,30)
1730 , substr(g_cache_cwb_rpt_person (i).posted_flex21,1,30)
1731 , substr(g_cache_cwb_rpt_person (i).proposed_flex21,1,30)
1732 , substr(g_cache_cwb_rpt_person (i).prior_flex22,1,30)
1733 , substr(g_cache_cwb_rpt_person (i).posted_flex22,1,30)
1734 , substr(g_cache_cwb_rpt_person (i).proposed_flex22,1,30)
1735 , substr(g_cache_cwb_rpt_person (i).prior_flex23,1,30)
1736 , substr(g_cache_cwb_rpt_person (i).posted_flex23,1,30)
1737 , substr(g_cache_cwb_rpt_person (i).proposed_flex23,1,30)
1738 , substr(g_cache_cwb_rpt_person (i).prior_flex24,1,30)
1739 , substr(g_cache_cwb_rpt_person (i).posted_flex24,1,30)
1740 , substr(g_cache_cwb_rpt_person (i).proposed_flex24,1,30)
1741 , substr(g_cache_cwb_rpt_person (i).prior_flex25,1,30)
1742 , substr(g_cache_cwb_rpt_person (i).posted_flex25,1,30)
1743 , substr(g_cache_cwb_rpt_person (i).proposed_flex25,1,30)
1744 , substr(g_cache_cwb_rpt_person (i).prior_flex26,1,30)
1745 , substr(g_cache_cwb_rpt_person (i).posted_flex26,1,30)
1746 , substr(g_cache_cwb_rpt_person (i).proposed_flex26,1,30)
1747 , substr(g_cache_cwb_rpt_person (i).prior_flex27,1,30)
1748 , substr(g_cache_cwb_rpt_person (i).posted_flex27,1,30)
1749 , substr(g_cache_cwb_rpt_person (i).proposed_flex27,1,30)
1750 , substr(g_cache_cwb_rpt_person (i).prior_flex28,1,30)
1751 , substr(g_cache_cwb_rpt_person (i).posted_flex28,1,30)
1752 , substr(g_cache_cwb_rpt_person (i).proposed_flex28,1,30)
1753 , substr(g_cache_cwb_rpt_person (i).prior_flex29,1,30)
1754 , substr(g_cache_cwb_rpt_person (i).posted_flex29,1,30)
1755 , substr(g_cache_cwb_rpt_person (i).proposed_flex29,1,30)
1756 , substr(g_cache_cwb_rpt_person (i).prior_flex30,1,30)
1757 , substr(g_cache_cwb_rpt_person (i).posted_flex30,1,30)
1758 , substr(g_cache_cwb_rpt_person (i).proposed_flex30,1,30)
1759 , substr(g_cache_cwb_rpt_person (i).asgn_change_reason,1,30)
1760 , g_cache_cwb_rpt_person (i).pending_workflow
1761 , 'Y'
1762 , g_cache_cwb_rpt_person (i).prev_eev_screen_entry_value);
1763 IF l_previous <> g_cache_cwb_rpt_person (i).person_id
1764 THEN
1765 l_previous := g_cache_cwb_rpt_person (i).person_id;
1766
1767 INSERT INTO ben_cwb_rpt_detail
1768 (benefit_action_id
1769 , person_rate_id
1770 , person_id
1771 , country_code
1772 , business_group_id
1773 , business_group_name
1774 , status_cd
1775 , lf_evt_closed_flag
1776 , cwb_rpt_detail_id
1777 )
1778 VALUES (benutils.g_benefit_action_id
1779 , -9999
1780 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).person_id
1781 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).country_code
1782 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_id
1783 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_name
1784 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status
1785 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).lf_evt_closed
1786 , ben_cwb_rpt_detail_s.NEXTVAL
1787 );
1788
1789 IF (g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status = 'E')
1790 THEN
1791 l_error := l_error + 1;
1792 END IF;
1793
1794 IF ( g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status = 'SC'
1795 OR g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status = 'WC'
1796 OR g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status = 'W'
1797 )
1798 THEN
1799 l_successful := l_successful + 1;
1800 END IF;
1801
1802 IF (g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).lf_evt_closed = 'Y')
1803 THEN
1804 l_closed_le := l_closed_le + 1;
1805 END IF;
1806
1807 IF (g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).lf_evt_closed = 'N')
1808 THEN
1809 l_open_le := l_open_le + 1;
1810 END IF;
1811 END IF;
1812 END LOOP;
1813 WRITE ('Time at the end of printing cache '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1814 --
1815 l_evaluated := l_successful + l_error;
1816 WRITE ('=======================Summary of the run =========================');
1817 WRITE ('No of persons evaluated in this thread ' || l_evaluated);
1818 WRITE ('No of persons successful in this thread ' || l_successful);
1819 WRITE ('No of persons errored in this thread ' || l_error);
1820 WRITE ('No of life events close in this thread ' || l_closed_le);
1821 WRITE ('No of life events opened in this thread ' || l_open_le);
1822 END;
1823
1824 --
1825 -- ============================================================================
1826 -- << Procedure: table_corrections >>
1827 -- ============================================================================
1828 --
1829 PROCEDURE table_corrections (
1830 p_benefit_action_id IN NUMBER
1831 )
1832 IS
1833 l_table_correction_rec c_table_correction_data%ROWTYPE;
1834 BEGIN
1835 WRITE('table corrections');
1836 FOR l_table_correction_rec IN c_table_correction_data(p_benefit_action_id)
1837 LOOP
1838 INSERT INTO ben_cwb_rpt_detail (
1839 benefit_action_id,
1840 person_id,
1841 pl_id,
1842 oipl_id,
1843 group_per_in_ler_id,
1844 group_oipl_id,
1845 cwb_rpt_detail_id
1846 )
1847 VALUES (
1848 p_benefit_action_id,
1849 l_table_correction_rec.person_id,
1850 l_table_correction_rec.pl_id,
1851 l_table_correction_rec.oipl_id,
1852 l_table_correction_rec.group_per_in_ler_id,
1853 l_table_correction_rec.group_oipl_id,
1854 ben_cwb_rpt_detail_s.NEXTVAL
1855 );
1856 WRITE(
1857 p_benefit_action_id||'-'||
1858 l_table_correction_rec.person_id||'-'||
1859 l_table_correction_rec.pl_id||'-'||
1860 l_table_correction_rec.oipl_id||'-'||
1861 l_table_correction_rec.group_oipl_id
1862 );
1863 END LOOP;
1864 --EXCEPTION
1865 END;
1866
1867 --
1868 -- ============================================================================
1869 -- << Procedure: get_plan_abr_info >>
1870 -- ============================================================================
1871 --
1872 PROCEDURE get_plan_abr_info(
1873 p_lf_evt_ocrd_date IN DATE
1874 , p_pl_id IN NUMBER
1875 , p_oipl_id IN NUMBER
1876 , p_element_type_id OUT NOCOPY NUMBER
1877 , p_input_value_id OUT NOCOPY NUMBER
1878 )
1879 IS
1880 l_found boolean;
1881 l_plan_abr_info plan_abr_info;
1882 l_element_type_id number;
1883 l_input_value_id number;
1884 BEGIN
1885 l_found := false;
1886 FOR element IN 1..g_plan_abr_info.COUNT
1887 LOOP
1888 if(g_plan_abr_info(element).pl_id = p_pl_id) then
1889 if(g_plan_abr_info(element).oipl_id = p_oipl_id) then
1890 l_element_type_id := g_plan_abr_info(element).element_type_id;
1891 l_input_value_id := g_plan_abr_info(element).input_value_id;
1892 l_found := true;
1893 end if;
1894 end if;
1895 END LOOP;
1896 if(l_found = false) then
1897 OPEN c_get_abr_info(p_lf_evt_ocrd_date
1898 ,p_pl_id
1899 ,p_oipl_id);
1900 FETCH c_get_abr_info INTO l_element_type_id,l_input_value_id;
1901 CLOSE c_get_abr_info;
1902 l_plan_abr_info.pl_id := p_pl_id;
1903 l_plan_abr_info.oipl_id := p_oipl_id;
1904 l_plan_abr_info.element_type_id := l_element_type_id;
1905 l_plan_abr_info.input_value_id := l_input_value_id;
1906 g_plan_abr_info.extend;
1907 g_plan_abr_info(g_plan_abr_info.last) := l_plan_abr_info;
1908 end if;
1909 p_element_type_id := l_element_type_id;
1910 p_input_value_id := l_input_value_id;
1911 EXCEPTION
1912 WHEN others THEN
1913 WRITE('Error at get_plan_abr_info');
1914 WRITE(SQLERRM);
1915 END;
1916
1917 --
1918 -- ============================================================================
1919 -- << Procedure: process_life_event >>
1920 -- ============================================================================
1921 --
1922
1923 PROCEDURE process_life_event (
1924 p_person_id IN NUMBER
1925 , p_lf_evt_ocrd_date IN DATE
1926 , p_plan_id IN NUMBER
1927 , p_group_per_in_ler_id IN NUMBER
1928 , p_effective_date IN DATE
1929 , p_employees_in_bg IN NUMBER
1930 )
1931 IS
1932 pil_rec c_per_in_ler_ids%ROWTYPE;
1933 l_procd_dt DATE;
1934 l_strtd_dt DATE;
1935 l_voidd_dt DATE;
1936 l_count_closed_lers NUMBER := 0;
1937 l_pil_ovn c_pil_ovn%ROWTYPE;
1938 l_info_ovn c_info_ovn%ROWTYPE;
1939 BEGIN
1940
1941 write_m ('Time before processing the life events '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1942 OPEN c_per_in_ler_ids (p_plan_id, p_employees_in_bg, p_person_id, p_lf_evt_ocrd_date);
1943
1944 LOOP
1945 FETCH c_per_in_ler_ids
1946 INTO pil_rec;
1947
1948 EXIT WHEN c_per_in_ler_ids%NOTFOUND;
1949
1950 IF (pil_rec.per_in_ler_id <> p_group_per_in_ler_id)
1951 THEN
1952 write_h ('selected per_in_ler_id ' || pil_rec.per_in_ler_id || ' for opening');
1953 ben_person_life_event_api.update_person_life_event
1954 (p_per_in_ler_id => pil_rec.per_in_ler_id
1955 , p_per_in_ler_stat_cd => 'STRTD'
1956 , p_procd_dt => l_procd_dt
1957 , p_voidd_dt => l_voidd_dt
1958 , p_strtd_dt => l_strtd_dt
1959 , p_object_version_number => pil_rec.object_version_number
1960 , p_effective_date => p_effective_date
1961 );
1962 END IF;
1963 END LOOP;
1964
1965 CLOSE c_per_in_ler_ids;
1966 write_m ('Time after processing the life events '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1967
1968 g_persons_procd := g_persons_procd + 1;
1969
1970 OPEN c_per_in_ler_ids (p_plan_id, p_employees_in_bg, p_person_id, p_lf_evt_ocrd_date);
1971
1972 LOOP
1973 FETCH c_per_in_ler_ids
1974 INTO pil_rec;
1975
1976 EXIT WHEN c_per_in_ler_ids%NOTFOUND;
1977
1978 IF ( pil_rec.per_in_ler_id <> p_group_per_in_ler_id
1979 AND pil_rec.per_in_ler_stat_cd = 'PROCD'
1980 )
1981 THEN
1982 write_h ('Following actual per_in_ler_id ' || pil_rec.per_in_ler_id || ' still closed');
1983 l_count_closed_lers := l_count_closed_lers + 1;
1984 END IF;
1985 END LOOP;
1986
1987 CLOSE c_per_in_ler_ids;
1988
1989 IF (l_count_closed_lers = 0)
1990 THEN
1991 write_h ('selected the group_per_in_ler_id ' || p_group_per_in_ler_id || ' for opening');
1992
1993 OPEN c_pil_ovn (p_group_per_in_ler_id);
1994
1995 FETCH c_pil_ovn
1996 INTO l_pil_ovn;
1997
1998 CLOSE c_pil_ovn;
1999 write_h ('Time before updating the person life event '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2000 ben_person_life_event_api.update_person_life_event
2001 (p_per_in_ler_id => p_group_per_in_ler_id
2002 , p_per_in_ler_stat_cd => 'STRTD'
2003 , p_procd_dt => l_procd_dt
2004 , p_voidd_dt => l_voidd_dt
2005 , p_strtd_dt => l_strtd_dt
2006 , p_object_version_number => l_pil_ovn.object_version_number
2007 , p_effective_date => p_effective_date
2008 );
2009 write_h ('Time after updating the person life event '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2010
2011
2012 OPEN c_info_ovn (p_group_per_in_ler_id);
2013
2014 FETCH c_info_ovn
2015 INTO l_info_ovn;
2016 CLOSE c_info_ovn;
2017
2018 WRITE ('updating post process stat code...');
2019 ben_cwb_person_info_api.update_person_info
2020 (p_group_per_in_ler_id => p_group_per_in_ler_id
2021 , p_post_process_stat_cd => null
2022 , p_object_version_number => l_info_ovn.object_version_number
2023 );
2024
2025 -- ************ audit changes ************* --
2026 --Changes for 11741025
2027 ben_cwb_audit_api.update_per_record(p_per_in_ler_id => p_group_per_in_ler_id
2028 ,p_old_val => null
2029 ,p_audit_type_cd => 'BOA' );
2030 -- **************************************** --
2031
2032 WRITE ('creating cache for reporting...');
2033
2034 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'N';
2035 ELSE
2036 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'Y';
2037 END IF;
2038 END;
2039 --
2040
2041 --
2042 -- ----------------------------------------------------------------------------
2043 -- |-------------------------< get_ele_dt_del_mode>---------------------------|
2044 -- ----------------------------------------------------------------------------
2045 --
2046 function get_ele_dt_del_mode
2047 (p_effective_date in date,
2048 p_base_key_value in number)
2049 return varchar2 is
2050
2051 l_zap_mode boolean;
2052 l_delete_mode boolean;
2053 l_future_change_mode boolean;
2054 l_delete_next_change_mode boolean;
2055 l_del_mode varchar2(30);
2056 l_zap_start_date date;
2057 l_zap_end_date date;
2058 l_delete_start_date date;
2059 l_delete_end_date date;
2060 l_del_future_start_date date;
2061 l_del_future_end_date date;
2062 l_del_next_start_date date;
2063 l_del_next_end_date date;
2064 --
2065 begin
2066
2067 dt_api.find_dt_del_modes -- _and_dates
2068 (p_effective_date => p_effective_date,
2069 p_base_table_name => 'PAY_ELEMENT_ENTRIES_F',
2070 p_base_key_column => 'ELEMENT_ENTRY_ID',
2071 p_base_key_value => p_base_key_value,
2072 p_zap => l_zap_mode,
2073 p_delete => l_delete_mode,
2074 p_future_change => l_future_change_mode,
2075 p_delete_next_change => l_delete_next_change_mode); /*,
2076 p_zap_start_date => l_zap_start_date,
2077 p_zap_end_date => l_zap_end_date,
2078 p_delete_start_date => l_delete_start_date,
2079 p_delete_end_date => l_delete_end_date,
2080 p_del_future_start_date => l_del_future_start_date,
2081 p_del_future_end_date => l_del_future_end_date,
2082 p_del_next_start_date => l_del_next_start_date,
2083 p_del_next_end_date => l_del_next_end_date);*/
2084 --
2085 return l_del_mode;
2086
2087 end get_ele_dt_del_mode;
2088 --
2089 -- ----------------------------------------------------------------------------
2090 -- |----------------------< backout_cwb_element >--------------------------|
2091 -- ----------------------------------------------------------------------------
2092 --
2093 procedure backout_cwb_element(
2094 p_validate IN BOOLEAN
2095 ,p_element_entry_value_id in number
2096 ,p_business_group_id IN NUMBER
2097 ,p_person_id IN NUMBER
2098 ,p_acty_ref_perd in varchar2 default null
2099 ,p_acty_base_rt_id in number default null
2100 ,p_element_link_id IN NUMBER default null
2101 ,p_rt_end_date IN DATE default null
2102 ,p_effective_date IN DATE default null
2103 ,p_dt_delete_mode IN VARCHAR2 default null
2104 ,p_amt in number default null ) is
2105 --
2106 l_proc VARCHAR2(72) := 'backout_cwb_element';
2107 l_element_link_id number;
2108 l_element_type_id number;
2109 l_input_value_id NUMBER;
2110 l_element_name varchar2(80);
2111 l_processing_type varchar2(30);
2112 l_assignment_id NUMBER;
2113 l_payroll_id NUMBER;
2114 l_element_entry_id NUMBER;
2115 l_element_entry_start_date date;
2116 l_element_entry_end_date date;
2117 l_object_version_number NUMBER;
2118 l_original_entry_id number;
2119 l_entry_type varchar2(30);
2120 l_curr_val_char varchar2 (60);
2121 l_delete_warning BOOLEAN;
2122 l_dt_delete_mode varchar2(80);
2123 l_effective_start_date DATE;
2124 l_effective_end_date DATE;
2125 l_effective_date date;
2126 l_string varchar2(4000);
2127 L_ELEMENT_ENTRY_VALUE_ID NUMBER;
2128 --
2129 cursor c_min_max_dt(p_element_entry_id number) is
2130 select min(effective_start_date),
2131 max(effective_end_date)
2132 from pay_element_entries_f
2133 where element_entry_id = p_element_entry_id;
2134 --
2135 l_min_start_date date;
2136 l_max_end_date date;
2137 --
2138 cursor c_ele_info(p_element_entry_value_id number) is
2139 select pel.element_link_id,
2140 pel.element_type_id,
2141 pev.input_value_id,
2142 pet.element_name,
2143 pet.processing_type
2144 from pay_element_types_f pet,
2145 pay_element_links_f pel,
2146 pay_element_entries_f pee,
2147 pay_element_entry_values_f pev
2148 where pev.element_entry_value_id = p_element_entry_value_id
2149 and pee.element_entry_id = pev.element_entry_id
2150 and pev.effective_start_date between pee.effective_start_date
2151 and pee.effective_end_date
2152 and pel.element_link_id = pee.element_link_id
2153 and pee.effective_start_date between pel.effective_start_date
2154 and pel.effective_end_date
2155 and pet.element_type_id = pel.element_type_id
2156 and pel.effective_start_date between pet.effective_start_date
2157 and pet.effective_end_date;
2158 --
2159 cursor get_element_entry_id (p_element_type_id in number
2160 ,p_input_value_id in number
2161 ,p_element_entry_value_id in number
2162 ,p_effective_date in date) is
2163 select asg.assignment_id,
2164 asg.payroll_id,
2165 pee.element_entry_id,
2166 pee.effective_start_date,
2167 pee.effective_end_date,
2168 pee.object_version_number,
2169 pee.original_entry_id,
2170 pee.entry_type,
2171 pee.element_link_id,
2172 pev.screen_entry_value
2173 from per_all_assignments_f asg,
2174 pay_element_links_f pel,
2175 pay_element_entries_f pee,
2176 pay_element_entry_values_f pev
2177 where asg.person_id = p_person_id
2178 and pee.assignment_id = asg.assignment_id
2179 and p_effective_date between asg.effective_start_date
2180 and asg.effective_end_date
2181 and pee.creator_type = 'F'
2182 and pee.entry_type = 'E'
2183 and p_effective_date <= pee.effective_end_date
2184 and pel.element_link_id = pee.element_link_id
2185 and pee.effective_start_date between pel.effective_start_date
2186 and pel.effective_end_date
2187 and pel.element_type_id = p_element_type_id
2188 and pev.element_entry_id = pee.element_entry_id
2189 and pev.input_value_id = p_input_value_id
2190 and (p_element_entry_value_id is null or
2191 pev.element_entry_value_id = p_element_entry_value_id)
2192 and pev.effective_start_date between pee.effective_start_date
2193 and pee.effective_end_date
2194 order by pee.effective_start_date ;
2195 --
2196
2197 -- added for 8392328
2198 cursor c_get_ovn (p_element_entry_id in number
2199 ,p_effective_date in date) is
2200 select object_version_number
2201 from pay_element_entries_f pee
2202 where pee.element_entry_id = p_element_entry_id
2203 and p_effective_date = pee.effective_end_date;
2204 --
2205 begin
2206 --
2207 g_actn := 'processing backout_cwb_element...';
2208 write(g_actn);
2209 write_h('Element_link_id='||to_char(p_element_link_id));
2210 write_h('p_element_entry_value_id='||to_char(p_element_entry_value_id));
2211 write_h('Effective_date='||to_char(p_effective_date));
2212 write_h('p_rt_end_date='||to_char(p_rt_end_date));
2213 --
2214 -- After discussing with CWB team decide to go with simple approach.
2215 -- Issues to consider
2216 -- Can elements be recurring. CWB team - not
2217 -- if element type is attached in cwb pl design table, can backout work ? YES
2218 -- Ignore proration etc., YES
2219 -- Element entry is shared by mutliple cwb entries ? NO
2220 --
2221 -- Assumptions.
2222 -- ben_cwb_person_rates.ELEMENT_ENTRY_VALUE_ID will be passed this routine.
2223 -- Element type from ben_cwb_plan_design overrides from abr.
2224 -- Abr information have to be fetched similar to cwb post process.
2225 --
2226 --
2227 -- if no element entry was created to start with, return
2228 --
2229 if p_element_entry_value_id is null then
2230 write_h('no element entry '||l_proc);
2231 write_h('Leaving 1: '||l_proc);
2232 return;
2233 end if;
2234 --
2235 -- find the element type and input value based on element_entry_value_id
2236 -- attached to prtt rt.
2237 --
2238 open c_ele_info(p_element_entry_value_id);
2239 fetch c_ele_info into
2240 l_element_link_id,
2241 l_element_type_id,
2242 l_input_value_id,
2243 l_element_name,
2244 l_processing_type;
2245 --
2246 if c_ele_info%notfound then
2247 close c_ele_info;
2248 --
2249 -- entry_value_id attached to prtt rt does not exist. This is possible
2250 -- prior to FP C when ct. could delete the entries
2251 --
2252 write_h('Leaving 2: '||l_proc);
2253 return;
2254 end if;
2255 close c_ele_info;
2256 --
2257 l_effective_date := p_effective_date;
2258 --
2259 write_h('ele type='||l_element_type_id);
2260 write_h('inp val='||l_input_value_id);
2261 write_h('l_effective_date='||l_effective_date);
2262
2263 --
2264 -- find the element entry that needs to be deleted.
2265 --
2266 open get_element_entry_id(-- p_enrt_rslt_id
2267 l_element_type_id
2268 ,l_input_value_id
2269 ,l_element_entry_value_id
2270 ,l_effective_date);
2271 fetch get_element_entry_id into
2272 l_assignment_id,
2273 l_payroll_id,
2274 l_element_entry_id,
2275 l_element_entry_start_date,
2276 l_element_entry_end_date,
2277 l_object_version_number,
2278 l_original_entry_id,
2279 l_entry_type,
2280 l_element_link_id,
2281 l_curr_val_char;
2282 --
2283 if get_element_entry_id%notfound then
2284 close get_element_entry_id;
2285 -- element entry already ended.
2286 hr_utility.set_location('element entry already ended',8);
2287 hr_utility.set_location('Leaving: '||l_proc,7);
2288
2289 -- 9999 is it needed.
2290 ben_warnings.load_warning
2291 (p_application_short_name => 'BEN',
2292 p_message_name => 'BEN_93455_ELE_ALREADY_ENDED',
2293 p_parma => l_element_name,
2294 p_parmb => to_char(l_effective_date),
2295 p_person_id => p_person_id);
2296 --
2297 if fnd_global.conc_request_id in ( 0,-1) then
2298 --
2299 fnd_message.set_name('BEN','BEN_93455_ELE_ALREADY_ENDED');
2300 fnd_message.set_token('PARMA',l_element_name);
2301 fnd_message.set_token('PARMB',to_char(l_effective_date));
2302 l_string := fnd_message.get;
2303 benutils.write(p_text => l_string);
2304 --
2305 end if;
2306 --
2307 --
2308 -- Could delete the entries
2309 --
2310 write_h('Leaving 3: '||l_proc);
2311 --
2312 --
2313 return;
2314 --
2315 end if;
2316 --
2317 -- Check if element is already processed in payroll, then make a
2318 -- quickpay entries. -- 9999
2319 --
2320 -- Add the function 9999
2321 l_dt_delete_mode := get_ele_dt_del_mode(p_effective_date, l_element_entry_id);
2322 --
2323 -- get the min effective_start date also.
2324 --
2325 open c_min_max_dt(l_element_entry_id);
2326 fetch c_min_max_dt into l_min_start_date,l_max_end_date;
2327 close c_min_max_dt;
2328 --
2329 if l_processing_type <> 'R' or p_effective_date < l_min_start_date then
2330 l_dt_delete_mode := hr_api.g_zap;
2331 else
2332 if p_effective_date = l_min_start_date then
2333 l_dt_delete_mode := hr_api.g_zap;
2334 else
2335 l_effective_date := p_effective_date -1;
2336 -- added for 8548730
2337 l_dt_delete_mode := get_ele_dt_del_mode(l_effective_date, l_element_entry_id);
2338 -- added if-block for 8392328
2339 if l_dt_delete_mode = hr_api.g_future_change then
2340 open c_get_ovn(l_element_entry_id,l_effective_date);
2341 fetch c_get_ovn into l_object_version_number;
2342 close c_get_ovn;
2343 end if;
2344 end if;
2345 end if;
2346 --
2347
2348 write_h('l_dt_delete_mode = ' || l_dt_delete_mode);
2349 write_h('l_element_entry_id = ' || l_element_entry_id);
2350 write_h('l_processing_type = ' || l_processing_type);
2351 --
2352 -- If procesing type id Non Recussring then zap the element entry.
2353 -- If it is recurring then check whether the min effective_start date
2354 -- less than the p_effective date, if so then do a future change, otherwise
2355 -- zap it.
2356 --
2357 py_element_entry_api.delete_element_entry
2358 (p_validate => p_validate
2359 ,p_datetrack_delete_mode => l_dt_delete_mode
2360 ,p_effective_date => l_effective_date
2361 ,p_element_entry_id => l_element_entry_id
2362 ,p_object_version_number => l_object_version_number
2363 ,p_effective_start_date => l_effective_start_date
2364 ,p_effective_end_date => l_effective_end_date
2365 ,p_delete_warning => l_delete_warning);
2366 --
2367 write_h('Leaving 4:'||l_proc);
2368 --
2369 END backout_cwb_element;
2370
2371
2372 --
2373 --
2374 -- *************************************************************************
2375 -- * << Procedure: delete_elements_and_salary >>
2376 -- *************************************************************************
2377 --
2378 procedure delete_elements_and_salary
2379 ( p_per_in_ler_id in number
2380 , p_update_summary in boolean
2381 , p_assignment_id in number
2382 , p_warning_text IN OUT NOCOPY VARCHAR2
2383 , p_warning IN OUT NOCOPY BOOLEAN
2384 , p_pay_proposal_id OUT NOCOPY number
2385 , p_element_entry_value_id OUT NOCOPY number
2386 ) is
2387 -- CWBGLOBAL
2388 cursor c_cwb_person_rates is
2389 select distinct pl_id, oipl_id, pay_proposal_id
2390 from ben_cwb_person_rates
2391 where group_per_in_ler_id = p_per_in_ler_id
2392 order by pay_proposal_id;
2393 --
2394 l_pl_id number;
2395 l_oipl_id number;
2396 l_pay_annual_ftr number;
2397 l_plan_pay_annual_ftr number;
2398 --
2399 --12696399
2400 cursor c_chk_rts_exists is
2401 select rates.ELEMENT_ENTRY_VALUE_ID,
2402 rates.COMP_POSTING_DATE,
2403 rates.person_id,
2404 rates.object_version_number,
2405 rates.ws_val,
2406 per_saladmin_utility.get_pay_annualization_factor(info.assignment_id,rates.comp_posting_date,null,'PERIOD') pay_annual_ftr,
2407 dsgn.pl_annulization_factor plan_pay_annual_ftr --12714771
2408 from ben_cwb_person_rates rates,
2409 ben_cwb_person_info info,
2410 ben_cwb_pl_dsgn dsgn
2411 where rates.group_per_in_ler_id = p_per_in_ler_id
2412 and rates.pl_id = l_pl_id
2413 and rates.oipl_id = l_oipl_id
2414 and rates.group_per_in_ler_id = info.group_per_in_ler_id
2415 and dsgn.group_pl_id = info.group_pl_id
2416 and dsgn.group_pl_id = dsgn.pl_id
2417 and dsgn.oipl_id = -1
2418 and dsgn.lf_evt_ocrd_dt = info.lf_evt_ocrd_dt;
2419 --
2420 l_pay_proposal_id number;
2421 l_pay_proposal_id_prev number := -1;
2422 --
2423 cursor c_pay_proposals is
2424 select object_version_number, business_group_id
2425 from per_pay_proposals
2426 where pay_proposal_id = l_pay_proposal_id;
2427
2428 cursor c_proposed_salary is
2429 select proposed_salary_n
2430 from per_pay_proposals
2431 where pay_proposal_id = l_pay_proposal_id;
2432
2433 cursor c_element_entry_val(l_element_entry_val_id IN number) is
2434 select screen_entry_value from pay_element_entry_values_f
2435 where element_entry_value_id = l_element_entry_val_id;
2436
2437 --
2438 --***************audit changes***************--
2439 --
2440 cursor c_cwb_person_info is
2441 select object_version_number
2442 from ben_cwb_person_info
2443 where group_per_in_ler_id = p_per_in_ler_id;
2444
2445 cursor c_plan_salary is
2446 select
2447 nvl(plRt.ws_val,0) ws_val
2448 from
2449 ben_cwb_pl_dsgn pl
2450 ,ben_cwb_person_rates plRt
2451 ,ben_cwb_person_info per
2452 where plRt.group_per_in_ler_id = p_per_in_ler_id
2453 and plRt.group_per_in_ler_id = per.group_per_in_ler_id
2454 and plRt.pl_id = pl.pl_id
2455 and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
2456 and plRt.oipl_id = pl.oipl_id
2457 and pl.oipl_id = -1
2458 and pl.ws_sub_acty_typ_cd = 'ICM7';
2459
2460 cursor c_grade_range is
2461 select
2462 (per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
2463 (per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
2464 (per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
2465 from
2466 ben_cwb_pl_dsgn pl
2467 ,ben_cwb_person_rates plRt
2468 ,ben_cwb_person_info per
2469 where plRt.group_per_in_ler_id = p_per_in_ler_id
2470 and plRt.group_per_in_ler_id = per.group_per_in_ler_id
2471 and plRt.pl_id = pl.pl_id
2472 and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
2473 and plRt.oipl_id = pl.oipl_id
2474 and pl.oipl_id = -1;
2475
2476 cursor c_option_salary ( l_oipl_ordr_num number) is
2477 select
2478 nvl(optRt.ws_val,0) ws_val
2479 from
2480 ben_cwb_pl_dsgn pl
2481 ,ben_cwb_person_rates optRt
2482 ,ben_cwb_person_info per
2483 where optRt.group_per_in_ler_id = p_per_in_ler_id
2484 and optRt.group_per_in_ler_id = per.group_per_in_ler_id
2485 and optRt.pl_id = pl.pl_id
2486 and optRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
2487 and optRt.oipl_id = pl.oipl_id
2488 and pl.oipl_id <> -1
2489 and pl.oipl_ordr_num = l_oipl_ordr_num
2490 and pl.ws_sub_acty_typ_cd in ('ICM7','ICM11');
2491
2492 l_pl_ws_val Number := 0;
2493 l_opt1_ws_val Number := 0;
2494 l_opt2_ws_val Number := 0;
2495 l_opt3_ws_val Number := 0;
2496 l_opt4_ws_val Number := 0;
2497 l_new_salary Number := null;
2498 l_base_salary Number := null;
2499
2500 --
2501 l_proc varchar2(50) := g_package||'.cwb_delete_routine';
2502 l_task_id number;
2503 l_group_pl_id number;
2504 l_person_rate_id number;
2505 l_group_oipl_id number;
2506 l_object_version_number number;
2507 l_dum number;
2508 l_salary_warning boolean;
2509
2510 l_object_version_number_prop number;
2511 l_business_group_id_prop number;
2512 l_dummy number;
2513 l_dummy1 number;
2514 l_dummy2 number;
2515 l_ELEMENT_ENTRY_VALUE_ID number;
2516 l_COMP_POSTING_DATE date;
2517 l_person_id number;
2518 future_pay_proposal_rec c_future_pay_proposal%ROWTYPE;
2519 l_message VARCHAR2 (600);
2520 l_app_name VARCHAR2 (240);
2521 l_message_name VARCHAR2 (240);
2522 l_new_sal_cwb number;
2523 l_new_sal_hr number;
2524 l_precision number;
2525 l_element_entry_val_hr number;
2526 l_element_entry_val_cwb number;
2527 --
2528 begin
2529
2530 g_proc := 'delete_elements_and_salary';
2531 g_actn := 'processing delete_elements_and_salary routine...';
2532 write(g_actn);
2533 --calculate cwb new salary
2534 for l_grade_range in c_grade_range loop
2535 l_base_salary := l_grade_range.base_salary;
2536 l_new_salary := 0;
2537 for l_option_salary in c_option_salary(1) loop
2538 l_opt1_ws_val := l_option_salary.ws_val;
2539 end loop;
2540 for l_option_salary in c_option_salary(2) loop
2541 l_opt2_ws_val := l_option_salary.ws_val;
2542 end loop;
2543 for l_option_salary in c_option_salary(3) loop
2544 l_opt3_ws_val := l_option_salary.ws_val;
2545 end loop;
2546 for l_option_salary in c_option_salary(4) loop
2547 l_opt4_ws_val := l_option_salary.ws_val;
2548 end loop;
2549 l_new_salary := l_base_salary + l_opt1_ws_val + l_opt2_ws_val + l_opt3_ws_val + l_opt4_ws_val;
2550 write_h('options sum : l_new_salary :'||l_new_salary);
2551
2552 for l_plan_salary in c_plan_salary loop
2553 l_new_salary := l_base_salary + l_plan_salary.ws_val;
2554 end loop;
2555 write_h('plan: l_new_salary :'||l_new_salary);
2556 end loop;
2557 l_new_sal_cwb := l_new_salary;
2558
2559 --
2560 -- CWBGLOBAL
2561
2562 l_pay_proposal_id_prev := -1;
2563 --
2564 open c_cwb_person_rates;
2565 loop
2566
2567 l_ELEMENT_ENTRY_VALUE_ID := null;
2568 l_COMP_POSTING_DATE := null;
2569 l_object_version_number := null;
2570 --
2571 fetch c_cwb_person_rates into l_pl_id, l_oipl_id,
2572 l_pay_proposal_id;
2573 exit when c_cwb_person_rates%NOTFOUND ;
2574
2575 open c_chk_rts_exists;
2576 fetch c_chk_rts_exists into l_ELEMENT_ENTRY_VALUE_ID,
2577 l_COMP_POSTING_DATE ,
2578 l_person_id,
2579 l_object_version_number,
2580 l_element_entry_val_cwb,
2581 l_pay_annual_ftr, --12696399
2582 l_plan_pay_annual_ftr;
2583 close c_chk_rts_exists;
2584
2585 if l_COMP_POSTING_DATE is not null then
2586 OPEN c_future_pay_proposal (p_per_in_ler_id, l_COMP_POSTING_DATE);
2587
2588 FETCH c_future_pay_proposal
2589 INTO future_pay_proposal_rec;
2590 CLOSE c_future_pay_proposal;
2591
2592 OPEN c_input_value_precision(p_assignment_id,l_COMP_POSTING_DATE);
2593 FETCH c_input_value_precision INTO l_precision;
2594 CLOSE c_input_value_precision;
2595 open c_proposed_salary;
2596 fetch c_proposed_salary
2597 into l_new_sal_hr;
2598 close c_proposed_salary;
2599 if l_precision is null then
2600 l_precision := 2;
2601 end if;
2602 write_h('l_precision : '|| l_precision);
2603 write_h('l_pay_annual_ftr ' || l_pay_annual_ftr || ' : l_plan_pay_annual_ftr ' || l_plan_pay_annual_ftr);
2604 --12714771
2605 if (l_pay_proposal_id is not null AND l_pay_annual_ftr is not null AND l_plan_pay_annual_ftr is not null ) then
2606 l_new_sal_cwb := l_new_sal_cwb * l_plan_pay_annual_ftr / l_pay_annual_ftr;
2607 end if;
2608 l_new_sal_cwb := round(l_new_sal_cwb,l_precision);
2609 l_new_sal_hr := round(l_new_sal_hr,l_precision);
2610 write_h('New Salary in CWB is ' || l_new_sal_cwb || ' : New Salary in HR is '||l_new_sal_hr);
2611
2612 IF(l_pay_proposal_id is not null AND future_pay_proposal_rec.proposed_salary_n is not null) THEN
2613 p_warning := TRUE;
2614 fnd_message.set_name ('BEN', 'BEN_94685_FUTURE_SAL_PROP_WARN');
2615 l_message := fnd_message.get_encoded;
2616 fnd_message.set_encoded(l_message);
2617 --
2618 fnd_message.parse_encoded(encoded_message => l_message,
2619 app_short_name => l_app_name,
2620 message_name => l_message_name);
2621 IF g_person_errored = FALSE THEN
2622 p_warning_text := substr(p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2623 END IF;
2624 g_person_errored := TRUE;
2625 write('Future dated salary proposal exists');
2626
2627 ELSIF (l_pay_proposal_id is not null AND l_new_sal_cwb <> l_new_sal_hr) THEN
2628 p_warning := TRUE;
2629 fnd_message.set_name ('BEN', 'BEN_94748_CWB_RECENT_SALY_CHG');
2630 l_message := fnd_message.get_encoded;
2631 fnd_message.set_encoded(l_message);
2632 fnd_message.parse_encoded(encoded_message => l_message,
2633 app_short_name => l_app_name,
2634 message_name => l_message_name);
2635 IF g_person_errored = FALSE THEN
2636 p_warning_text := substr(p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2637 END IF;
2638 g_person_errored := TRUE;
2639 write('This employee had a recent update to Salary or Pay Basis after postprocess. Hence could not back-out this employee');
2640
2641 ELSE
2642
2643 if l_pay_proposal_id is not null then
2644 --
2645 open c_pay_proposals;
2646 fetch c_pay_proposals
2647 into l_object_version_number_prop, l_business_group_id_prop;
2648 close c_pay_proposals;
2649 --
2650 end if;
2651 --
2652 -- Delete element entry if attached to rate row.
2653 --
2654 write_h('l_element_entry_value_id = ' || l_ELEMENT_ENTRY_VALUE_ID);
2655 write_h('l_business_group_id_prop = ' || l_business_group_id_prop);
2656 write_h('l_pay_proposal_id = ' || l_pay_proposal_id);
2657 write_h('l_person_id = ' || l_person_id);
2658 write_h('l_comp_posting_date = '|| l_COMP_POSTING_DATE);
2659 if (l_ELEMENT_ENTRY_VALUE_ID is not null and
2660 l_COMP_POSTING_DATE is not null) then
2661
2662 open c_element_entry_val(l_ELEMENT_ENTRY_VALUE_ID);
2663 fetch c_element_entry_val
2664 into l_element_entry_val_hr;
2665 close c_element_entry_val;
2666 l_element_entry_val_hr := round(l_element_entry_val_hr,l_precision);
2667 --12696399
2668 if (l_pay_annual_ftr is not null AND l_plan_pay_annual_ftr is not null ) then
2669 l_element_entry_val_cwb := l_element_entry_val_cwb * l_plan_pay_annual_ftr / l_pay_annual_ftr;
2670 end if;
2671 l_element_entry_val_cwb := round(l_element_entry_val_cwb,l_precision);
2672 write_h('Element Value in CWB is ' || l_element_entry_val_cwb || ' : Element Value in HR is '|| l_element_entry_val_hr);
2673
2674 if(l_element_entry_val_cwb <> l_element_entry_val_hr) then
2675 p_warning := TRUE;
2676 fnd_message.set_name ('BEN', 'BEN_94749_CWB_RECENT_ELE_CHG');
2677 l_message := fnd_message.get_encoded;
2678 fnd_message.set_encoded(l_message);
2679 fnd_message.parse_encoded(encoded_message => l_message,
2680 app_short_name => l_app_name,
2681 message_name => l_message_name);
2682 IF g_person_errored = FALSE THEN
2683 p_warning_text := substr(p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2684 END IF;
2685 g_person_errored := TRUE;
2686 write('This employee had a recent update to Element after postprocess. Hence could not back-out this employee');
2687 end if;
2688
2689 write('Calling backout_cwb_element');
2690 backout_cwb_element(
2691 p_element_entry_value_id => l_ELEMENT_ENTRY_VALUE_ID
2692 ,p_validate => false
2693 ,p_business_group_id => l_business_group_id_prop
2694 ,p_person_id => l_person_id
2695 ,p_effective_date => l_COMP_POSTING_DATE
2696 );
2697
2698 end if;
2699 --
2700 -- Bug 5130397 : When CWB plan has options attached, then all corresponding rows in BEN_CWB_PERSON_RATES
2701 -- has pay_proposal_id populated and this being same ID, we should not call delete API
2702 -- more than once. Hence added following check : l_pay_proposal_id <> l_pay_proposal_id_prev
2703 --
2704 if l_pay_proposal_id is not null AND
2705 l_pay_proposal_id <> l_pay_proposal_id_prev
2706 then
2707 --
2708 write('Calling hr_maintain_proposal_api.delete_salary_proposal');
2709 write_h('l_pay_proposal_id = ' || l_pay_proposal_id);
2710 hr_maintain_proposal_api.delete_salary_proposal
2711 ( p_pay_proposal_id => l_pay_proposal_id
2712 ,p_business_group_id => l_business_group_id_prop
2713 ,p_object_version_number => l_object_version_number_prop
2714 ,p_validate => false
2715 ,p_salary_warning => l_salary_warning ) ;
2716 --
2717 l_pay_proposal_id_prev := l_pay_proposal_id;
2718 --
2719 end if;
2720 --
2721 END IF; --future_pay_proposal_rec
2722 END IF;
2723 end loop;
2724 close c_cwb_person_rates;
2725
2726 p_pay_proposal_id := l_pay_proposal_id;
2727 p_element_entry_value_id := l_ELEMENT_ENTRY_VALUE_ID;
2728 write('Finished delete_elements_and_salary routine...');
2729 IF(p_warning) THEN
2730 RAISE ben_batch_utils.g_record_error;
2731 WRITE('Raising error in delete_elements_and_salary');
2732 END IF;
2733
2734 exception
2735 --
2736 when others then
2737 --
2738 WRITE('Error at delete_elements_and_salary');
2739 WRITE(SQLERRM);
2740 --
2741 if c_cwb_person_rates%isopen then
2742 close c_cwb_person_rates;
2743 end if;
2744 if c_chk_rts_exists%isopen then
2745 close c_chk_rts_exists;
2746 end if;
2747 if c_pay_proposals%isopen then
2748 close c_pay_proposals;
2749 end if;
2750 if c_cwb_person_info%isopen then
2751 close c_cwb_person_info;
2752 end if;
2753 RAISE;
2754 --
2755 END delete_elements_and_salary;
2756 --
2757
2758 --
2759 -- ============================================================================
2760 -- << Procedure: backout_rating >>
2761 -- ============================================================================
2762 --
2763 procedure backout_rating
2764 (p_person_id in number
2765 ,p_txn_rec in ben_cwb_asg_update.g_txn%rowtype
2766 ,p_business_group_id in number
2767 ,p_audit_log in varchar2 default 'N'
2768 ,p_process_status in out nocopy varchar2
2769 ,p_group_per_in_ler_id in number default null
2770 ,p_effective_date in date) is
2771 l_proc varchar2(80) := g_package || '.backout_rating';
2772 l_evt_ovn number;
2773 l_next_review_date_warning boolean;
2774 l_perf_date date;
2775 l_event_id number;
2776 l_performance_review_id number;
2777 l_perf_ovn number;
2778 l_update_event_id number;
2779 l_update_review_id number;
2780 l_event_type varchar2(30);
2781 --
2782 cursor c_performance_id_in_db is
2783 select perf.performance_review_id
2784 ,perf.event_id
2785 ,perf.object_version_number
2786 from per_performance_reviews perf
2787 where perf.person_id = p_person_id
2788 and perf.review_date = l_perf_date;
2789 --
2790 cursor c_perf_id_attached_event_type is
2791 select evt.type,
2792 evt.object_version_number
2793 from per_events evt
2794 where evt.assignment_id = p_txn_rec.assignment_id
2795 and evt.date_start = l_perf_date
2796 and evt.event_id = l_event_id;
2797 --
2798
2799 begin
2800 --
2801 hr_utility.set_location('Entering ' || l_proc, 5);
2802 l_event_type := null;
2803 --
2804 if (p_txn_rec.attribute1 is null or
2805 p_txn_rec.attribute3 is null) then
2806 hr_utility.set_location('No Processing returning ' || l_proc, 5);
2807 return;
2808 else
2809 l_perf_date := to_date(p_txn_rec.attribute1, 'yyyy/mm/dd');
2810
2811 l_perf_date := p_effective_date;
2812
2813 hr_utility.set_location('l_perf_date ' || l_perf_date, 10);
2814
2815 open c_performance_id_in_db;
2816 fetch c_performance_id_in_db into l_performance_review_id, l_event_id, l_perf_ovn;
2817 close c_performance_id_in_db;
2818
2819 if l_event_id is not null then
2820 open c_perf_id_attached_event_type;
2821 fetch c_perf_id_attached_event_type into l_event_type,l_evt_ovn;
2822 close c_perf_id_attached_event_type;
2823 end if;
2824
2825 hr_utility.set_location(l_performance_review_id||','||l_event_id||','||l_perf_date,15);
2826 hr_utility.set_location(l_event_type,16);
2827 hr_utility.set_location(p_txn_rec.attribute2,17);
2828
2829 if(l_event_type is not null) then
2830 if (l_event_type <> nvl(p_txn_rec.attribute2,'-1')) then
2831 hr_utility.set_location('Rating type mismatch '||l_event_type||'&'||p_txn_rec.attribute2, 20);
2832 fnd_message.set_name ('BEN', 'BEN_93371_RATING_EXST_FOR_DATE');
2833 fnd_message.raise_error;
2834 end if;
2835 end if;
2836
2837 if(l_performance_review_id is not null) then
2838 write_h('Deleting Review Record');
2839 l_update_review_id := l_performance_review_id;
2840 hr_perf_review_api.delete_perf_review(
2841 p_validate => false
2842 ,p_performance_review_id => l_performance_review_id
2843 ,p_object_version_number => l_perf_ovn);
2844 l_update_review_id := null;
2845 end if;
2846
2847 if (l_event_id is not null) then
2848 write_h('Deleting Event Record');
2849 l_update_event_id := l_event_id;
2850 per_events_api.delete_event(
2851 p_validate => false
2852 ,p_event_id => l_event_id
2853 ,p_object_version_number => l_evt_ovn);
2854 l_update_event_id := null;
2855 end if;
2856
2857 end if;
2858
2859 --
2860 if p_group_per_in_ler_id is not null then
2861 --
2862 update ben_cwb_person_info
2863 set new_perf_event_id = l_update_event_id,
2864 new_perf_review_id = l_update_review_id
2865 where group_per_in_ler_id = p_group_per_in_ler_id;
2866 --
2867 end if;
2868 --
2869 p_process_status := 'CWB_PERF_SUS';
2870 --
2871 hr_utility.set_location('Leaving ' || l_proc, 5);
2872 --
2873 EXCEPTION
2874 WHEN OTHERS THEN
2875 p_process_status := null;
2876 raise;
2877
2878 end backout_rating;
2879
2880 --
2881 -- ============================================================================
2882 -- << Procedure: process_person >>
2883 -- ============================================================================
2884 --
2885 PROCEDURE process_person (
2886 p_validate IN VARCHAR2 DEFAULT 'N'
2887 , p_person_id IN NUMBER DEFAULT NULL
2888 , p_person_action_id IN NUMBER DEFAULT NULL
2889 , p_object_version_number IN OUT NOCOPY NUMBER
2890 , p_business_group_id IN NUMBER
2891 , p_lf_evt_ocrd_date IN DATE
2892 , p_plan_id IN NUMBER
2893 , p_group_per_in_ler_id IN NUMBER
2894 , p_effective_date IN DATE
2895 , p_audit_log IN VARCHAR2 DEFAULT 'N'
2896 , p_debug_level IN VARCHAR2 DEFAULT NULL
2897 , p_process_sal_comp IN VARCHAR2 DEFAULT 'N'
2898 , p_employees_in_bg IN NUMBER
2899 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
2900 , p_is_placeholder IN VARCHAR2
2901 , p_is_backout_perf IN VARCHAR2 DEFAULT 'N'
2902 )
2903 IS
2904 l_comp_error BOOLEAN := FALSE;
2905 l_perf_error BOOLEAN := FALSE;
2906 l_promo_error BOOLEAN := FALSE;
2907 l_actual_termination_date DATE;
2908 l_perf_revw_strt_dt DATE;
2909 l_perf_revw_new_strt_dt DATE;
2910 l_asg_updt_eff_date DATE;
2911 l_interview_typ_cd VARCHAR2 (80);
2912 l_ranking_info c_ranking_info%ROWTYPE;
2913 l_ranking_info_date c_ranking_info_date%ROWTYPE;
2914 l_per_in_ler_id NUMBER;
2915 l_ovn NUMBER;
2916 l_assignment_extra_info_id NUMBER;
2917 l_cache_cwb_rpt_person g_cache_cwb_rpt_person_rec;
2918 l_bg_and_mgr_name c_bg_and_mgr_name%ROWTYPE;
2919 l_amount NUMBER := NULL;
2920 l_perf_txn ben_cwb_asg_update.g_txn%ROWTYPE;
2921 l_asg_txn ben_cwb_asg_update.g_txn%ROWTYPE;
2922 l_rate_ovn c_rate_ovn%ROWTYPE;
2923 l_grp_ovn c_grp_ovn%ROWTYPE;
2924 l_emp_num_and_emp_name c_emp_num_and_emp_name%ROWTYPE;
2925 l_rating_status VARCHAR2 (200);
2926 l_promotion_status VARCHAR2 (200);
2927 l_comp_person_rec g_cwb_rpt_person_rec;
2928 l_perf_person_rec g_cwb_rpt_person_rec;
2929 l_promo_person_rec g_cwb_rpt_person_rec;
2930 l_pay_proposal_id NUMBER;
2931 l_error BOOLEAN;
2932 l_collected_message VARCHAR2 (2000);
2933 l_element_entry_value_id NUMBER;
2934 l_posted_perf_rating VARCHAR2 (200);
2935 l_message VARCHAR2 (600);
2936 l_message_name VARCHAR2 (240);
2937 l_app_name VARCHAR2 (240);
2938 l_amount_posted VARCHAR2(60);
2939 l_warning BOOLEAN;
2940 l_warning_text VARCHAR2 (2000);
2941 l_is_eligible BOOLEAN;
2942 l_dummy c_check_eligibility%ROWTYPE;
2943 l_overrides_perf_prom c_overrides_perf_prom%ROWTYPE;
2944 l_count NUMBER := 0;
2945 per_bg_rec c_person_info%ROWTYPE;
2946 l_sal_factors c_sal_factors%ROWTYPE;
2947 asg_rec c_prev_pay_proposal%ROWTYPE;
2948 l_precision NUMBER;
2949 l_payroll_run c_payroll_run%ROWTYPE;
2950 l_counter NUMBER;
2951 l_input_value_id NUMBER;
2952 l_element_type_id NUMBER;
2953 l_last_payroll_date DATE;
2954 l_element_input_value VARCHAR2 (2000);
2955
2956 cursor c_element_proc_stat(element_id number) is
2957 select pet.element_name,
2958 pet.processing_type
2959 from pay_element_types_f pet
2960 where pet.element_type_id = element_id;
2961 l_element_proc_stat c_element_proc_stat%ROWTYPE;
2962
2963 --12581640
2964 CURSOR c_posting_date(v_group_per_in_ler_id number) is
2965 select min(comp_posting_date)
2966 from ben_cwb_person_rates
2967 where group_per_in_ler_id = v_group_per_in_ler_id
2968 and comp_posting_date is not null ;
2969 l_posting_date date;
2970
2971 --
2972
2973 BEGIN
2974 g_proc := 'process_person';
2975 SAVEPOINT backout_post_process_person;
2976
2977 l_error := FALSE;
2978 g_person_errored := FALSE;
2979
2980 open c_check_eligibility(p_group_per_in_ler_id);
2981 fetch c_check_eligibility into l_dummy;
2982 If c_check_eligibility%found then
2983 l_is_eligible := TRUE;
2984 else
2985 l_is_eligible := FALSE;
2986 End if;
2987 Close c_check_eligibility;
2988
2989 IF(l_is_eligible = FALSE) THEN
2990 WRITE('Ineligible Person');
2991 END IF;
2992
2993 WRITE ('initializing global names for this thread... ');
2994 init (p_plan_id, p_lf_evt_ocrd_date);
2995
2996 OPEN c_bg_and_mgr_name (p_group_per_in_ler_id, p_effective_date);
2997
2998 FETCH c_bg_and_mgr_name
2999 INTO l_bg_and_mgr_name;
3000
3001 CLOSE c_bg_and_mgr_name;
3002
3003 OPEN c_emp_num_and_emp_name(p_group_per_in_ler_id);
3004 FETCH c_emp_num_and_emp_name into l_emp_num_and_emp_name;
3005 CLOSE c_emp_num_and_emp_name;
3006
3007 OPEN c_posting_date(p_group_per_in_ler_id);
3008 FETCH c_posting_date into l_posting_date;
3009 CLOSE c_posting_date;
3010 if(l_posting_date is null) then
3011 l_posting_date := p_effective_date;
3012 end if;
3013
3014
3015 if(p_is_placeholder='N') then
3016
3017 OPEN c_performance_promotion (p_plan_id, p_lf_evt_ocrd_date);
3018 FETCH c_performance_promotion
3019 INTO l_perf_revw_strt_dt
3020 , l_perf_revw_new_strt_dt
3021 , l_asg_updt_eff_date
3022 , l_interview_typ_cd;
3023
3024 CLOSE c_performance_promotion;
3025
3026 IF((l_asg_updt_eff_date IS NOT NULL)or(l_perf_revw_strt_dt IS NOT NULL)) THEN
3027 OPEN c_overrides_perf_prom(p_group_per_in_ler_id, p_lf_evt_ocrd_date);
3028 FETCH c_overrides_perf_prom INTO l_overrides_perf_prom;
3029 CLOSE c_overrides_perf_prom;
3030 END IF;
3031
3032 write_h ('=====================Processing Person ==========================');
3033 write_h ('||Person Id ' || p_person_id);
3034 write_h ('||Per_in_ler_id ' || p_group_per_in_ler_id);
3035 write_h ('||Person Action id ' || p_person_action_id);
3036 write_h ('||Plan id ' || p_plan_id);
3037 write_h ('||Employees in bg ' || p_employees_in_bg);
3038 write_h ('||Employee bg ' || l_emp_num_and_emp_name.business_group_id);
3039 write_h ('================================================================');
3040
3041 g_actn := 'Process compensation for the person...';
3042 WRITE (g_actn);
3043
3044 -- Processesing only for eligible employess.
3045 -- Bug: 8323386
3046
3047 IF l_is_eligible = TRUE then
3048
3049 --cursors opened for reports
3050 OPEN c_person_info(p_group_per_in_ler_id);
3051 FETCH c_person_info INTO per_bg_rec;
3052 CLOSE c_person_info;
3053 OPEN c_prev_pay_proposal(p_group_per_in_ler_id, l_posting_date);
3054 FETCH c_prev_pay_proposal INTO asg_rec;
3055 CLOSE c_prev_pay_proposal;
3056 OPEN c_input_value_precision(asg_rec.assignment_id,p_effective_date);
3057 FETCH c_input_value_precision INTO l_precision;
3058 CLOSE c_input_value_precision;
3059 OPEN c_sal_factors(p_plan_id,p_lf_evt_ocrd_date,p_group_per_in_ler_id);
3060 FETCH c_sal_factors INTO l_sal_factors;
3061 CLOSE c_sal_factors;
3062
3063 l_comp_person_rec.full_name := l_emp_num_and_emp_name.full_name;
3064 l_comp_person_rec.person_id := p_person_id;
3065 l_comp_person_rec.emp_number := l_emp_num_and_emp_name.employee_number;
3066 l_comp_person_rec.business_group_name := l_bg_and_mgr_name.name;
3067 l_comp_person_rec.manager_name := l_bg_and_mgr_name.full_name;
3068 l_comp_person_rec.pl_name := g_group_plan_name;
3069 l_comp_person_rec.business_group_id := l_bg_and_mgr_name.business_group_id;
3070 l_comp_person_rec.country_code := l_emp_num_and_emp_name.legislation_code;
3071 l_comp_person_rec.group_per_in_ler_id := p_group_per_in_ler_id;
3072
3073 BEGIN
3074 SAVEPOINT delete_elements_and_salary;
3075 write_m ('Time before processing delete_elements_and_salary '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3076 --11736671
3077 l_last_payroll_date := PER_SALADMIN_UTILITY.get_last_payroll_dt(asg_rec.assignment_id);
3078 --12581640
3079 write_h('l_posting_date is ' ||l_posting_date);
3080 write_h('l_last_payroll_date is '|| l_last_payroll_date);
3081 OPEN c_payroll_run (asg_rec.assignment_id,l_posting_date,p_effective_date);
3082 FETCH c_payroll_run INTO l_payroll_run;
3083 IF (c_payroll_run%FOUND OR (l_last_payroll_date is not null AND l_last_payroll_date >= least(l_posting_date,p_effective_date) )) THEN
3084 fnd_message.set_name ('BEN', 'BEN_94739_PAYROLL_RAN');
3085 l_message := fnd_message.get_encoded;
3086 fnd_message.set_encoded(l_message);
3087 --
3088 fnd_message.parse_encoded(encoded_message => l_message,
3089 app_short_name => l_app_name,
3090 message_name => l_message_name);
3091 l_comp_person_rec.error_or_warning_text := substr(fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
3092 WRITE(l_comp_person_rec.error_or_warning_text);
3093 ROLLBACK TO delete_elements_and_salary;
3094 l_comp_error := TRUE;
3095 g_person_errored := TRUE;
3096 l_error := TRUE;
3097 WRITE (g_actn);
3098 ELSE
3099 delete_elements_and_salary (p_per_in_ler_id => p_group_per_in_ler_id
3100 , p_update_summary => false
3101 , p_assignment_id => asg_rec.assignment_id
3102 , p_warning_text => l_warning_text
3103 , p_warning => l_error
3104 , p_pay_proposal_id => l_pay_proposal_id
3105 , p_element_entry_value_id => l_element_entry_value_id
3106 );
3107 write_m ('Time after processing delete_elements_and_salary '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3108 write_h ('l_pay_proposal_id : '||l_pay_proposal_id);
3109 write_h ('l_element_entry_value_id : '||l_element_entry_value_id);
3110 l_comp_error := FALSE;
3111 l_comp_person_rec.status := 'SC';
3112 END IF;
3113 CLOSE c_payroll_run;
3114
3115 EXCEPTION
3116 WHEN OTHERS
3117 THEN
3118 WRITE('Error in delete_elements_and_salary : '||SQLERRM);
3119 IF c_payroll_run%ISOPEN THEN
3120 close c_payroll_run;
3121 END IF;
3122
3123 l_comp_person_rec.status := 'E';
3124 l_message := fnd_message.get_encoded;
3125 if(l_message is null) then
3126 fnd_message.set_name ('BEN', 'BEN_94685_FUTURE_SAL_PROP_WARN');
3127 l_message := fnd_message.get_encoded;
3128 end if;
3129 fnd_message.set_encoded(l_message);
3130 --
3131 fnd_message.parse_encoded(encoded_message => l_message,
3132 app_short_name => l_app_name,
3133 message_name => l_message_name);
3134 l_comp_person_rec.error_or_warning_text := l_warning_text;
3135 WRITE(l_comp_person_rec.error_or_warning_text);
3136 ROLLBACK TO delete_elements_and_salary;
3137 l_comp_error := TRUE;
3138 g_person_errored := TRUE;
3139 l_error := TRUE;
3140 WRITE (g_actn);
3141 END;
3142
3143 END IF;
3144
3145 IF(p_is_backout_perf = 'Y') THEN
3146 l_perf_person_rec.rating_date := hr_general.end_of_time;
3147 if l_perf_revw_strt_dt is not null then
3148 OPEN ben_cwb_asg_update.g_txn (l_emp_num_and_emp_name.assignment_id,
3149 ben_cwb_asg_update.g_ws_perf_rec_type||to_char(l_perf_revw_strt_dt, 'yyyy/mm/dd')
3150 ||l_interview_typ_cd);
3151
3152 FETCH ben_cwb_asg_update.g_txn INTO l_perf_txn;
3153 CLOSE ben_cwb_asg_update.g_txn;
3154 end if;
3155 IF((l_perf_txn.attribute1 is not null and l_perf_txn.attribute3 is not null)
3156 and (l_is_eligible = TRUE)
3157 ) THEN
3158 BEGIN
3159 SAVEPOINT process_rating;
3160 g_actn := 'found assignment id in the transaction table backing out rating...';
3161 WRITE (g_actn);
3162 write_m ('Time before backing out rating '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3163 if(p_is_self_service = 'Y') then
3164 WRITE('override performance review date = '||l_overrides_perf_prom.attribute2);
3165 l_perf_revw_strt_dt := to_date(l_overrides_perf_prom.attribute2,'yyyy/mm/dd');
3166 else
3167 WRITE('performance review date = '||l_perf_revw_strt_dt);
3168 l_perf_revw_strt_dt := l_perf_revw_strt_dt;
3169 end if;
3170 l_perf_person_rec.rating_date := l_perf_revw_strt_dt;
3171 l_perf_person_rec.rating_type := l_perf_txn.attribute2;
3172 l_perf_person_rec.performance_rating := substrb(hr_general.decode_lookup('PERFORMANCE_RATING',l_perf_txn.attribute3),1,30);
3173 if(l_error) then
3174 fnd_message.raise_error;
3175 end if;
3176
3177 backout_rating (p_person_id => p_person_id
3178 , p_txn_rec => l_perf_txn
3179 , p_business_group_id => l_emp_num_and_emp_name.business_group_id
3180 , p_audit_log => p_audit_log
3181 , p_process_status => l_rating_status
3182 , p_group_per_in_ler_id => p_group_per_in_ler_id
3183 , p_effective_date => l_perf_revw_strt_dt
3184 );
3185
3186 write_m ('Time after backing out rating '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3187
3188 IF l_rating_status = 'CWB_PERF_SUS' THEN
3189 g_actn := 'Person ' || p_person_id || ' backed out successfully for Performance Rating';
3190 WRITE (g_actn);
3191 END IF;
3192
3193 l_perf_person_rec.status := 'SC';
3194
3195 WRITE('Performance rating backed out is '||l_perf_person_rec.performance_rating);
3196 l_perf_error := FALSE;
3197 EXCEPTION
3198 WHEN OTHERS THEN
3199 WRITE('Error at Performance rating '||SQLERRM);
3200 l_perf_person_rec.status := 'E';
3201 l_message := fnd_message.get_encoded;
3202 fnd_message.set_encoded(l_message);
3203 --
3204 fnd_message.parse_encoded(encoded_message => l_message,
3205 app_short_name => l_app_name,
3206 message_name => l_message_name);
3207 l_perf_person_rec.error_or_warning_text := substr(fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
3208 g_person_errored := TRUE;
3209 WRITE(l_perf_person_rec.error_or_warning_text);
3210 ROLLBACK TO process_rating;
3211 IF(benutils.get_message_name = 'BEN_93371_RATING_EXST_FOR_DATE') THEN
3212 --l_perf_person_rec.error_or_warning_text := fnd_message.get;
3213 write_m ('Time after processing rating '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3214 WRITE('Failed Performance rating is '||l_perf_person_rec.performance_rating);
3215 l_rating_status := 'CWB_PERF_SUS';
3216 ELSE
3217 l_perf_error := TRUE;
3218 l_error := TRUE;
3219 END IF;
3220 g_actn := 'Person ' || p_person_id || ' failed for Performance Rating';
3221 WRITE (g_actn);
3222 END;
3223 END IF;
3224 ELSE
3225 l_perf_person_rec.rating_date := hr_general.end_of_time;
3226 l_perf_person_rec.status := 'SC';
3227 END IF;
3228
3229 BEGIN
3230 IF ( l_comp_error
3231 OR l_perf_error
3232 OR l_promo_error)
3233 THEN
3234 l_error := TRUE;
3235 g_person_errored := TRUE;
3236 RAISE ben_batch_utils.g_record_error;
3237 END IF;
3238
3239 process_life_event(p_person_id
3240 , p_lf_evt_ocrd_date
3241 , p_plan_id
3242 , p_group_per_in_ler_id
3243 , p_effective_date
3244 , p_employees_in_bg);
3245
3246 EXCEPTION
3247 WHEN OTHERS THEN
3248 WRITE('Life Event not closed due to error');
3249 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'N';
3250 END;
3251
3252 l_perf_person_rec.full_name := l_emp_num_and_emp_name.full_name;
3253 l_perf_person_rec.person_id := p_person_id;
3254 l_perf_person_rec.emp_number := l_emp_num_and_emp_name.employee_number;
3255 l_perf_person_rec.business_group_name := l_bg_and_mgr_name.name;
3256 l_perf_person_rec.manager_name := l_bg_and_mgr_name.full_name;
3257 l_perf_person_rec.pl_name := g_group_plan_name;
3258 l_perf_person_rec.business_group_id := l_bg_and_mgr_name.business_group_id;
3259 l_perf_person_rec.country_code := l_emp_num_and_emp_name.legislation_code;
3260 l_perf_person_rec.group_per_in_ler_id := p_group_per_in_ler_id;
3261
3262 l_promo_person_rec.full_name := l_emp_num_and_emp_name.full_name;
3263 l_promo_person_rec.person_id := p_person_id;
3264 l_promo_person_rec.emp_number := l_emp_num_and_emp_name.employee_number;
3265 l_promo_person_rec.business_group_name := l_bg_and_mgr_name.name;
3266 l_promo_person_rec.manager_name := l_bg_and_mgr_name.full_name;
3267 l_promo_person_rec.pl_name := g_group_plan_name;
3268 l_promo_person_rec.business_group_id := l_bg_and_mgr_name.business_group_id;
3269 l_promo_person_rec.country_code := l_emp_num_and_emp_name.legislation_code;
3270 l_promo_person_rec.group_per_in_ler_id := p_group_per_in_ler_id;
3271 l_promo_person_rec.assignment_id := l_emp_num_and_emp_name.assignment_id;
3272 l_promo_person_rec.assignment_changed := 'N';
3273 l_promo_person_rec.status := 'SC';
3274
3275 /*
3276 BEGIN
3277 SAVEPOINT process_ranking;
3278 IF l_perf_revw_strt_dt is not null THEN
3279
3280 write_m ('Time before processing the rank '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3281 FOR l_ranking_info IN c_ranking_info (p_group_per_in_ler_id
3282 ,l_perf_revw_strt_dt
3283 ,p_plan_id)
3284 LOOP
3285 WRITE ('Deleting ranking for this person...');
3286
3287 IF c_ranking_info_date%ISOPEN THEN
3288 CLOSE c_ranking_info_date;
3289 END IF;
3290
3291 OPEN c_ranking_info_date(p_group_per_in_ler_id,
3292 l_perf_revw_strt_dt ,
3293 l_ranking_info.aei_information2);
3294 FETCH c_ranking_info_date INTO l_ranking_info_date;
3295 IF c_ranking_info_date%FOUND THEN
3296 write ('Found a rank which needs to be deleted...');
3297 hr_assignment_extra_info_api.delete_assignment_extra_info(
3298 p_validate => false
3299 ,p_assignment_extra_info_id => l_ranking_info.assignment_extra_info_id
3300 ,p_object_version_number => l_ranking_info.object_version_number
3301 );
3302 ELSE
3303 WRITE ('Found a rank which need not be deleted...');
3304 END IF;
3305 CLOSE c_ranking_info_date;
3306
3307 END LOOP;
3308 l_perf_person_rec.status := 'SC';
3309
3310 write_m ('Time after processing the rank '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3311 END IF;
3312 EXCEPTION
3313 WHEN OTHERS THEN
3314 WRITE('Error at Ranking '||SQLERRM);
3315 l_perf_person_rec.status := 'E';
3316 l_message := fnd_message.get_encoded;
3317 fnd_message.set_encoded(l_message);
3318 --
3319 fnd_message.parse_encoded(encoded_message => l_message,
3320 app_short_name => l_app_name,
3321 message_name => l_message_name);
3322 l_perf_person_rec.error_or_warning_text := substr(fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
3323 WRITE(l_perf_person_rec.error_or_warning_text);
3324 ROLLBACK TO process_ranking;
3325 l_perf_error := TRUE;
3326 l_error := TRUE;
3327 g_person_errored := TRUE;
3328 g_actn := 'Person ' || p_person_id || ' failed for Ranking';
3329 WRITE (g_actn);
3330 END;
3331 */
3332
3333 WRITE ('creating cache for reporting...');
3334 --sugu start
3335
3336 -- l_count := g_cache_cwb_rpt_person.COUNT;
3337 l_count := 0;
3338 IF p_process_sal_comp = 'Y' THEN
3339 FOR sal_rate_rec IN c_sal_comp_rates (p_group_per_in_ler_id
3340 , p_plan_id
3341 , p_lf_evt_ocrd_date
3342 , p_effective_date
3343 )
3344 LOOP
3345 l_comp_person_rec.person_rate_id := sal_rate_rec.person_rate_id;
3346 l_comp_person_rec.pl_id := sal_rate_rec.pl_id;
3347 l_comp_person_rec.oipl_id := sal_rate_rec.oipl_id;
3348 l_comp_person_rec.group_pl_id := sal_rate_rec.group_pl_id;
3349 l_comp_person_rec.group_oipl_id := sal_rate_rec.group_oipl_id;
3350 l_comp_person_rec.full_name := sal_rate_rec.full_name;
3351 l_comp_person_rec.emp_number := sal_rate_rec.employee_number;
3352 l_comp_person_rec.business_group_id := sal_rate_rec.business_group_id;
3353 l_comp_person_rec.ws_mgr_id := sal_rate_rec.ws_mgr_id;
3354 l_comp_person_rec.units := null;
3355
3356 l_comp_person_rec.base_salary_currency := per_bg_rec.base_salary_currency;
3357 l_comp_person_rec.base_salary := per_bg_rec.base_salary;
3358 l_comp_person_rec.elig_salary := round(sal_rate_rec.elig_sal_val,l_precision);
3359 l_comp_person_rec.amount := round(nvl(sal_rate_rec.ws_val,0),l_precision);
3360 if(l_comp_person_rec.elig_salary is null OR l_comp_person_rec.elig_salary = 0 ) then
3361 l_comp_person_rec.percent_of_elig_sal := 0;
3362 else
3363 l_comp_person_rec.percent_of_elig_sal :=
3364 round((l_comp_person_rec.amount/l_comp_person_rec.elig_salary)*100,l_precision);
3365 end if;
3366 l_comp_person_rec.base_sal_freq := per_bg_rec.base_salary_frequency;
3367 l_comp_person_rec.pay_ann_factor := l_sal_factors.pay_annulization_factor;
3368 l_comp_person_rec.pl_ann_factor := l_sal_factors.pl_annulization_factor;
3369 l_comp_person_rec.conversion_factor :=
3370 round(l_sal_factors.pl_annulization_factor/l_sal_factors.pay_annulization_factor,
3371 l_precision);
3372 l_comp_person_rec.adjusted_amount := round(0,l_precision);
3373 l_comp_person_rec.prev_sal := round(nvl(asg_rec.proposed_salary_n,0),l_precision);
3374 --l_comp_person_rec.new_sal := round(nvl(asg_rec.proposed_salary_n,0),l_precision);
3375 l_comp_person_rec.exchange_rate := sal_rate_rec.xchg_rate;
3376 l_comp_person_rec.effective_date := sal_rate_rec.comp_posting_date;
3377 l_comp_person_rec.reason := sal_rate_rec.component_reason;
3378 l_comp_person_rec.eligibility := sal_rate_rec.elig_flag;
3379 l_comp_person_rec.fte_factor := per_bg_rec.fte_factor;
3380 l_comp_person_rec.group_per_in_ler_id := p_group_per_in_ler_id;
3381 l_comp_person_rec.currency := sal_rate_rec.currency;
3382 l_comp_person_rec.lf_evt_ocrd_date := p_lf_evt_ocrd_date;
3383 l_comp_person_rec.ws_sub_acty_typ_cd := 'ICM7';
3384 /*if(l_error OR l_warning) then
3385 l_comp_person_rec.error_or_warning_text := substr(l_warning_text,1,2000);
3386 end if;*/
3387
3388 l_count := l_count + 1;
3389 l_cache_cwb_rpt_person (l_count).person_rate_id := sal_rate_rec.person_rate_id;
3390 l_cache_cwb_rpt_person (l_count).pl_id := sal_rate_rec.pl_id;
3391 l_cache_cwb_rpt_person (l_count).oipl_id := sal_rate_rec.oipl_id;
3392 l_cache_cwb_rpt_person (l_count).group_pl_id := sal_rate_rec.group_pl_id;
3393 l_cache_cwb_rpt_person (l_count).group_oipl_id := sal_rate_rec.group_oipl_id;
3394 l_cache_cwb_rpt_person (l_count).full_name := sal_rate_rec.full_name;
3395 l_cache_cwb_rpt_person (l_count).emp_number := sal_rate_rec.employee_number;
3396 l_cache_cwb_rpt_person (l_count).business_group_id := sal_rate_rec.business_group_id;
3397 l_cache_cwb_rpt_person (l_count).ws_mgr_id := sal_rate_rec.ws_mgr_id;
3398 l_cache_cwb_rpt_person (l_count).units := null;
3399
3400 l_cache_cwb_rpt_person (l_count).base_salary_currency := per_bg_rec.base_salary_currency;
3401 l_cache_cwb_rpt_person (l_count).base_salary := per_bg_rec.base_salary;
3402 l_cache_cwb_rpt_person (l_count).elig_salary := round(sal_rate_rec.elig_sal_val,l_precision);
3403 l_cache_cwb_rpt_person (l_count).amount := round(nvl(sal_rate_rec.ws_val,0),l_precision);
3404 if(l_cache_cwb_rpt_person (l_count).elig_salary is null OR l_cache_cwb_rpt_person (l_count).elig_salary = 0 ) then
3405 l_cache_cwb_rpt_person (l_count).percent_of_elig_sal := 0;
3406 else
3407 l_cache_cwb_rpt_person (l_count).percent_of_elig_sal :=
3408 round((l_cache_cwb_rpt_person (l_count).amount/l_cache_cwb_rpt_person (l_count).elig_salary)*100,l_precision);
3409 end if;
3410 l_cache_cwb_rpt_person (l_count).base_sal_freq := per_bg_rec.base_salary_frequency;
3411 l_cache_cwb_rpt_person (l_count).pay_ann_factor := l_sal_factors.pay_annulization_factor;
3412 l_cache_cwb_rpt_person (l_count).pl_ann_factor := l_sal_factors.pl_annulization_factor;
3413 l_cache_cwb_rpt_person (l_count).conversion_factor :=
3414 round(l_sal_factors.pl_annulization_factor/l_sal_factors.pay_annulization_factor,
3415 l_precision);
3416 --l_cache_cwb_rpt_person (l_count).adjusted_amount := round(0,l_precision);
3417 l_cache_cwb_rpt_person (l_count).prev_sal := round(nvl(asg_rec.proposed_salary_n,0),l_precision);
3418 l_cache_cwb_rpt_person (l_count).new_sal := round(nvl(asg_rec.proposed_salary_n,0),l_precision);
3419 l_cache_cwb_rpt_person (l_count).exchange_rate := sal_rate_rec.xchg_rate;
3420 l_cache_cwb_rpt_person (l_count).effective_date := sal_rate_rec.comp_posting_date;
3421 l_cache_cwb_rpt_person (l_count).reason := sal_rate_rec.component_reason;
3422 l_cache_cwb_rpt_person (l_count).eligibility := sal_rate_rec.elig_flag;
3423 l_cache_cwb_rpt_person (l_count).fte_factor := per_bg_rec.fte_factor;
3424
3425 l_cache_cwb_rpt_person (l_count).pay_proposal_id := l_pay_proposal_id;
3426 l_cache_cwb_rpt_person (l_count).pay_basis_id := asg_rec.pay_basis_id;
3427
3428 l_cache_cwb_rpt_person (l_count).assignment_id := asg_rec.assignment_id;
3429 l_cache_cwb_rpt_person (l_count).uom_precision := l_sal_factors.uom_precision;
3430 l_cache_cwb_rpt_person (l_count).ws_sub_acty_typ_cd := 'ICM7';
3431
3432 l_cache_cwb_rpt_person (l_count).group_per_in_ler_id := p_group_per_in_ler_id;
3433 l_cache_cwb_rpt_person (l_count).currency := sal_rate_rec.currency;
3434 l_cache_cwb_rpt_person (l_count).lf_evt_ocrd_date := p_lf_evt_ocrd_date;
3435
3436 if(l_error or l_warning) then --7218121
3437 l_cache_cwb_rpt_person (l_count).error_or_warning_text := substr(l_comp_person_rec.error_or_warning_text,1,2000);
3438 end if;
3439
3440 END LOOP;
3441
3442 -- ELSE --12571024
3443 END IF;
3444 FOR rt_rec in c_non_sal_comp_rates (p_group_per_in_ler_id, p_effective_date)
3445 LOOP
3446
3447 l_comp_person_rec.adjusted_amount := null;
3448 l_comp_person_rec.amount := null;
3449 l_comp_person_rec.amount_posted := null;
3450 l_comp_person_rec.assignment_changed := null;
3451 l_comp_person_rec.assignment_id := null;
3452 l_comp_person_rec.base_sal_freq := null;
3453 l_comp_person_rec.base_salary := null;
3454 l_comp_person_rec.base_salary_currency := null;
3455 l_comp_person_rec.benefit_action_id := null;
3456 l_comp_person_rec.business_group_id := null;
3457 l_comp_person_rec.business_group_name := null;
3458 l_comp_person_rec.conversion_factor := null;
3459 l_comp_person_rec.country_code := null;
3460 l_comp_person_rec.currency := null;
3461 l_comp_person_rec.eev_screen_entry_value:= null;
3462 l_comp_person_rec.effective_date := null;
3463 l_comp_person_rec.element_entry_id := null;
3464 l_comp_person_rec.element_entry_value_id:= null;
3465 l_comp_person_rec.element_input_value := null;
3466 l_comp_person_rec.elmnt_processing_type := null;
3467 --11803107
3468 -- IF(l_element_type_id is null and l_input_value_id is null) then
3469 get_plan_abr_info(
3470 p_lf_evt_ocrd_date => rt_rec.lf_evt_ocrd_dt
3471 , p_pl_id => rt_rec.pl_id
3472 , p_oipl_id => rt_rec.oipl_id
3473 , p_element_type_id => l_element_type_id
3474 , p_input_value_id => l_input_value_id
3475 );
3476 --16246942
3477 l_element_input_value := 'No Element specified';
3478 IF(l_element_type_id is not NULL) THEN
3479 FOR l_element_proc_stat in c_element_proc_stat(l_element_type_id) LOOP
3480 l_comp_person_rec.elmnt_processing_type := l_element_proc_stat.processing_type;
3481 END LOOP;
3482 IF(l_input_value_id is not NULL) THEN
3483 OPEN c_element_input_value_name(l_input_value_id,l_element_type_id, rt_rec.comp_posting_date);
3484 FETCH c_element_input_value_name INTO l_element_input_value;
3485 CLOSE c_element_input_value_name;
3486 END IF;
3487 END IF;
3488 l_comp_person_rec.element_input_value := SUBSTR(l_element_input_value,1,80);
3489 -- END IF;
3490 l_comp_person_rec.element_type_id := l_element_type_id;
3491 l_comp_person_rec.input_value_id := l_input_value_id;
3492 l_comp_person_rec.elig_salary := null;
3493 l_comp_person_rec.eligibility := null;
3494 l_comp_person_rec.emp_number := null;
3495 --l_comp_person_rec.error_or_warning_text := null;
3496 l_comp_person_rec.exchange_rate := null;
3497 l_comp_person_rec.fte_factor := null;
3498 l_comp_person_rec.full_name := null;
3499 l_comp_person_rec.group_oipl_id := null;
3500 l_comp_person_rec.group_per_in_ler_id := null;
3501 l_comp_person_rec.group_pl_id := null;
3502 l_comp_person_rec.lf_evt_closed := null;
3503 l_comp_person_rec.lf_evt_ocrd_date := null;
3504 l_comp_person_rec.manager_name := null;
3505 l_comp_person_rec.new_sal := null;
3506 l_comp_person_rec.oipl_id := null;
3507 l_comp_person_rec.opt_name := null;
3508 l_comp_person_rec.pay_ann_factor := null;
3509 l_comp_person_rec.pay_basis_id := null;
3510 l_comp_person_rec.pay_proposal_id := null;
3511 l_comp_person_rec.percent_of_elig_sal := null;
3512 l_comp_person_rec.performance_rating := null;
3513 l_comp_person_rec.person_id := null;
3514 l_comp_person_rec.pl_ann_factor := null;
3515 l_comp_person_rec.pl_id := null;
3516 l_comp_person_rec.pl_name := null;
3517 l_comp_person_rec.prev_eev_screen_entry_value := null;
3518 l_comp_person_rec.prev_sal := null;
3519 l_comp_person_rec.rating_date := null;
3520 l_comp_person_rec.reason := null;
3521 l_comp_person_rec.status := null;
3522 l_comp_person_rec.units := null;
3523 l_comp_person_rec.uom_precision := null;
3524 l_comp_person_rec.ws_mgr_id := null;
3525 l_comp_person_rec.ws_sub_acty_typ_cd := null;
3526
3527 l_comp_person_rec.person_rate_id := rt_rec.person_rate_id;
3528 l_comp_person_rec.pl_id := rt_rec.pl_id;
3529 l_comp_person_rec.oipl_id := rt_rec.oipl_id;
3530 l_comp_person_rec.group_pl_id := rt_rec.group_pl_id;
3531 l_comp_person_rec.group_oipl_id := rt_rec.group_oipl_id;
3532 l_comp_person_rec.full_name := rt_rec.full_name;
3533 l_comp_person_rec.emp_number := rt_rec.employee_number;
3534 l_comp_person_rec.business_group_id := rt_rec.business_group_id;
3535 l_comp_person_rec.units := rt_rec.units;
3536 l_comp_person_rec.ws_mgr_id := rt_rec.ws_mgr_id;
3537
3538 l_comp_person_rec.base_salary_currency := rt_rec.base_salary_currency;
3539 l_comp_person_rec.base_salary := rt_rec.base_salary;
3540 l_comp_person_rec.elig_salary := round(rt_rec.elig_sal_val,rt_rec.uom_precision);
3541 l_comp_person_rec.amount := round(nvl(rt_rec.ws_val,0),rt_rec.uom_precision);
3542
3543 IF(l_comp_person_rec.elig_salary is null OR l_comp_person_rec.elig_salary = 0 ) THEN
3544 l_comp_person_rec.percent_of_elig_sal := 0;
3545 ELSE
3546 l_comp_person_rec.percent_of_elig_sal :=
3547 round((l_comp_person_rec.amount/l_comp_person_rec.elig_salary)*100,rt_rec.uom_precision);
3548 END IF;
3549
3550 l_comp_person_rec.base_sal_freq := rt_rec.base_salary_frequency;
3551 l_comp_person_rec.pay_ann_factor := rt_rec.pay_annulization_factor;
3552 l_comp_person_rec.pl_ann_factor := rt_rec.pl_annulization_factor;
3553 l_comp_person_rec.exchange_rate := rt_rec.xchg_rate;
3554 l_comp_person_rec.effective_date := rt_rec.comp_posting_date;
3555 l_comp_person_rec.reason := rt_rec.component_reason;
3556 l_comp_person_rec.eligibility := rt_rec.elig_flag;
3557 l_comp_person_rec.fte_factor := rt_rec.fte_factor;
3558
3559 IF(rt_rec.ws_sub_acty_typ_cd='ICM7') THEN
3560 l_comp_person_rec.conversion_factor :=
3561 round(rt_rec.pl_annulization_factor/rt_rec.pay_annulization_factor,
3562 6);
3563 ELSE
3564 l_comp_person_rec.conversion_factor := 1;
3565 END IF;
3566 l_comp_person_rec.adjusted_amount := l_comp_person_rec.amount;
3567 l_comp_person_rec.assignment_id := rt_rec.assignment_id;
3568 l_comp_person_rec.uom_precision := rt_rec.uom_precision;
3569 l_comp_person_rec.ws_sub_acty_typ_cd := rt_rec.ws_sub_acty_typ_cd;
3570 l_comp_person_rec.currency := rt_rec.currency;
3571 l_comp_person_rec.lf_evt_ocrd_date := p_lf_evt_ocrd_date;
3572 l_comp_person_rec.group_per_in_ler_id := p_group_per_in_ler_id;
3573
3574 --l_comp_person_rec.pay_proposal_id := null;
3575 l_comp_person_rec.pay_proposal_id := l_pay_proposal_id;
3576 l_comp_person_rec.element_entry_value_id:= l_element_entry_value_id;
3577 l_comp_person_rec.pay_basis_id := null;
3578 l_comp_person_rec.pay_basis_id := asg_rec.pay_basis_id;
3579 l_comp_person_rec.prev_sal := nvl(asg_rec.proposed_salary_n,0);
3580 l_comp_person_rec.reason := rt_rec.salary_change_reason;
3581
3582 l_count := l_count + 1;
3583 l_cache_cwb_rpt_person (l_count).person_rate_id := l_comp_person_rec.person_rate_id;
3584 l_cache_cwb_rpt_person (l_count).pl_id := l_comp_person_rec.pl_id;
3585 l_cache_cwb_rpt_person (l_count).oipl_id := l_comp_person_rec.oipl_id;
3586 l_cache_cwb_rpt_person (l_count).group_pl_id := l_comp_person_rec.group_pl_id;
3587 l_cache_cwb_rpt_person (l_count).group_oipl_id := l_comp_person_rec.group_oipl_id;
3588 l_cache_cwb_rpt_person (l_count).full_name := l_comp_person_rec.full_name;
3589 l_cache_cwb_rpt_person (l_count).emp_number := l_comp_person_rec.emp_number;
3590 l_cache_cwb_rpt_person (l_count).business_group_id := l_comp_person_rec.business_group_id;
3591 l_cache_cwb_rpt_person (l_count).ws_mgr_id := l_comp_person_rec.ws_mgr_id;
3592 l_cache_cwb_rpt_person (l_count).units := l_comp_person_rec.units;
3593 l_cache_cwb_rpt_person (l_count).assignment_id := l_comp_person_rec.assignment_id;
3594
3595 l_cache_cwb_rpt_person (l_count).base_salary_currency := l_comp_person_rec.base_salary_currency;
3596 l_cache_cwb_rpt_person (l_count).elig_salary := l_comp_person_rec.elig_salary;
3597 l_cache_cwb_rpt_person (l_count).amount := l_comp_person_rec.amount;
3598 l_cache_cwb_rpt_person (l_count).percent_of_elig_sal := l_comp_person_rec.percent_of_elig_sal;
3599 l_cache_cwb_rpt_person (l_count).conversion_factor := l_comp_person_rec.conversion_factor;
3600 l_cache_cwb_rpt_person (l_count).exchange_rate := l_comp_person_rec.exchange_rate;
3601 l_cache_cwb_rpt_person (l_count).effective_date := l_comp_person_rec.effective_date;
3602 l_cache_cwb_rpt_person (l_count).eligibility := l_comp_person_rec.eligibility;
3603
3604 l_cache_cwb_rpt_person (l_count).adjusted_amount := l_comp_person_rec.adjusted_amount;
3605 l_cache_cwb_rpt_person (l_count).uom_precision := l_comp_person_rec.uom_precision;
3606 l_cache_cwb_rpt_person (l_count).currency := l_comp_person_rec.currency;
3607
3608 l_cache_cwb_rpt_person (l_count).ws_sub_acty_typ_cd :=l_comp_person_rec.ws_sub_acty_typ_cd;
3609
3610 l_cache_cwb_rpt_person (l_count).group_per_in_ler_id := p_group_per_in_ler_id;
3611 l_cache_cwb_rpt_person (l_count).lf_evt_ocrd_date := p_lf_evt_ocrd_date;
3612
3613 IF rt_rec.ws_sub_acty_typ_cd = 'ICM7' THEN
3614 l_cache_cwb_rpt_person (l_count).prev_sal := l_comp_person_rec.prev_sal;
3615 --l_cache_cwb_rpt_person (l_count).new_sal := l_comp_person_rec.prev_sal;
3616 l_cache_cwb_rpt_person (l_count).base_salary := l_comp_person_rec.base_salary;
3617 l_cache_cwb_rpt_person (l_count).base_sal_freq := l_comp_person_rec.base_sal_freq;
3618 l_cache_cwb_rpt_person (l_count).pay_ann_factor := l_comp_person_rec.pay_ann_factor;
3619 l_cache_cwb_rpt_person (l_count).pl_ann_factor := l_comp_person_rec.pl_ann_factor;
3620 l_cache_cwb_rpt_person (l_count).reason := l_comp_person_rec.reason;
3621 l_cache_cwb_rpt_person (l_count).fte_factor := l_comp_person_rec.fte_factor;
3622 l_cache_cwb_rpt_person (l_count).pay_proposal_id := l_comp_person_rec.pay_proposal_id;
3623 l_cache_cwb_rpt_person (l_count).pay_basis_id := l_comp_person_rec.pay_basis_id;
3624 ELSE
3625 l_cache_cwb_rpt_person (l_count).prev_sal := null;
3626 l_cache_cwb_rpt_person (l_count).base_salary := null;
3627 l_cache_cwb_rpt_person (l_count).base_sal_freq := null;
3628 l_cache_cwb_rpt_person (l_count).pay_ann_factor := null;
3629 l_cache_cwb_rpt_person (l_count).pl_ann_factor := null;
3630 l_cache_cwb_rpt_person (l_count).reason := null;
3631 l_cache_cwb_rpt_person (l_count).fte_factor := null;
3632 l_cache_cwb_rpt_person (l_count).pay_proposal_id := null;
3633 l_cache_cwb_rpt_person (l_count).pay_basis_id := null;
3634 l_cache_cwb_rpt_person (l_count).element_entry_value_id := l_comp_person_rec.element_entry_value_id;
3635 l_cache_cwb_rpt_person (l_count).input_value_id := l_comp_person_rec.input_value_id;
3636 l_cache_cwb_rpt_person (l_count).element_type_id := l_comp_person_rec.element_type_id;
3637 l_cache_cwb_rpt_person (l_count).eev_screen_entry_value := l_comp_person_rec.eev_screen_entry_value;
3638 l_cache_cwb_rpt_person (l_count).element_input_value := l_comp_person_rec.element_input_value;
3639 l_cache_cwb_rpt_person (l_count).elmnt_processing_type := l_comp_person_rec.elmnt_processing_type;
3640 l_cache_cwb_rpt_person (l_count).prev_eev_screen_entry_value := l_comp_person_rec.prev_eev_screen_entry_value;
3641
3642 END IF;
3643 END LOOP;
3644 -- END IF;
3645
3646 --sugu end
3647 FOR v_counter IN 1 .. l_cache_cwb_rpt_person.COUNT
3648 LOOP
3649 IF(trim(l_collected_message) is null) THEN
3650 l_collected_message := substr(l_collected_message||
3651 l_cache_cwb_rpt_person (v_counter).error_or_warning_text,1,2000);
3652 END IF;
3653 END LOOP;
3654
3655 IF(l_error) THEN
3656 l_perf_person_rec.status := 'E';
3657 END IF;
3658 --g_cache_cwb_rpt_person (g_cache_cwb_rpt_person.COUNT + 1) := l_comp_person_rec;
3659 g_cache_cwb_rpt_person (g_cache_cwb_rpt_person.COUNT + 1) := l_perf_person_rec;
3660 g_cache_cwb_rpt_person (g_cache_cwb_rpt_person.COUNT + 1) := l_promo_person_rec;
3661 IF(trim(l_collected_message) is null) THEN
3662 l_collected_message := substr(l_collected_message||
3663 l_comp_person_rec.error_or_warning_text||
3664 l_perf_person_rec.error_or_warning_text,1,2000);
3665 END IF;
3666
3667 FOR v_counter IN 1 .. l_cache_cwb_rpt_person.COUNT
3668 LOOP
3669 l_amount_posted := null;
3670 --WRITE('Counter: '||v_counter);
3671 l_cache_cwb_rpt_person (v_counter).manager_name := l_bg_and_mgr_name.full_name;
3672 l_cache_cwb_rpt_person (v_counter).business_group_name := l_bg_and_mgr_name.NAME;
3673 l_cache_cwb_rpt_person (v_counter).person_id := p_person_id;
3674 l_cache_cwb_rpt_person (v_counter).country_code := l_emp_num_and_emp_name.legislation_code;
3675 if((NOT l_error)and(p_validate <> 'Y')
3676 and(l_cache_cwb_rpt_person (v_counter).element_entry_value_id is not null)) then
3677 WRITE('Element_entry_value_id: '||l_cache_cwb_rpt_person (v_counter).element_entry_value_id);
3678 OPEN c_posted_element(l_cache_cwb_rpt_person (v_counter).assignment_id
3679 ,l_cache_cwb_rpt_person (v_counter).element_type_id
3680 ,l_cache_cwb_rpt_person (v_counter).input_value_id
3681 ,l_cache_cwb_rpt_person (v_counter).effective_date);
3682 --FETCH c_posted_element INTO l_cache_cwb_rpt_person (v_counter).amount_posted;
3683 FETCH c_posted_element INTO l_amount_posted;
3684 CLOSE c_posted_element;
3685 l_cache_cwb_rpt_person (v_counter).amount_posted:= fnd_number.canonical_to_number(l_amount_posted);
3686 WRITE('Amount posted: '||l_cache_cwb_rpt_person (v_counter).amount_posted);
3687 end if;
3688 if((NOT l_error)and (p_validate <> 'Y')and
3689 (l_cache_cwb_rpt_person (v_counter).pay_proposal_id is not null)) then
3690 WRITE('Pay_proposal_id: '||l_cache_cwb_rpt_person (v_counter).pay_proposal_id);
3691 OPEN c_posted_salary(l_cache_cwb_rpt_person (v_counter).pay_proposal_id);
3692 FETCH c_posted_salary INTO l_cache_cwb_rpt_person (v_counter).new_sal;
3693 CLOSE c_posted_salary;
3694 -- l_cache_cwb_rpt_person (v_counter).amount_posted
3695 -- := l_cache_cwb_rpt_person (v_counter).new_sal - l_cache_cwb_rpt_person (v_counter).prev_sal;
3696 WRITE('New Sal: '||l_cache_cwb_rpt_person (v_counter).new_sal);
3697 end if;
3698
3699 if(NOT l_error) THEN
3700 l_cache_cwb_rpt_person (v_counter).lf_evt_closed := 'N';
3701 else
3702 l_cache_cwb_rpt_person (v_counter).lf_evt_closed := 'Y';
3703 l_cache_cwb_rpt_person (v_counter).effective_date := NULL;
3704 end if;
3705
3706 l_warning := FALSE; --warning not supported completely yet
3707
3708 IF(l_warning) THEN
3709 l_cache_cwb_rpt_person (v_counter).status := 'W';
3710 ELSE
3711 IF(NOT l_error) THEN
3712 IF (l_cache_cwb_rpt_person (v_counter).amount IS NULL)
3713 THEN
3714 l_cache_cwb_rpt_person (v_counter).status := 'SC';
3715 ELSE
3716 IF (l_amount IS NULL)
3717 THEN
3718 l_amount := 0;
3719 END IF;
3720 l_amount := l_amount + l_cache_cwb_rpt_person (v_counter).amount;
3721 l_cache_cwb_rpt_person (v_counter).status := 'SC';
3722 END IF;
3723
3724 ELSE
3725 l_cache_cwb_rpt_person (v_counter).status := 'E';
3726 END IF;
3727 END IF;
3728
3729
3730 IF l_cache_cwb_rpt_person (v_counter).pl_id = l_cache_cwb_rpt_person (v_counter).group_pl_id
3731 THEN
3732 l_cache_cwb_rpt_person (v_counter).pl_name := g_group_plan_name;
3733 ELSE
3734 l_cache_cwb_rpt_person (v_counter).pl_name :=
3735 g_cache_actual_plans (l_cache_cwb_rpt_person (v_counter).pl_id);
3736 END IF;
3737
3738 IF l_cache_cwb_rpt_person (v_counter).oipl_id <> -1
3739 THEN
3740 IF l_cache_cwb_rpt_person (v_counter).oipl_id =
3741 l_cache_cwb_rpt_person (v_counter).group_oipl_id
3742 THEN
3743 l_cache_cwb_rpt_person (v_counter).opt_name :=
3744 g_cache_group_options (l_cache_cwb_rpt_person (v_counter).oipl_id);
3745 ELSE
3746 l_cache_cwb_rpt_person (v_counter).opt_name :=
3747 g_cache_actual_options (l_cache_cwb_rpt_person (v_counter).oipl_id);
3748 END IF;
3749 ELSE
3750 l_cache_cwb_rpt_person (v_counter).error_or_warning_text := substr(l_collected_message,1,2000);
3751 END IF;
3752
3753 g_cache_cwb_rpt_person (g_cache_cwb_rpt_person.COUNT + 1) :=
3754 l_cache_cwb_rpt_person (v_counter);
3755 END LOOP;
3756
3757 WRITE('populating g_cache_cwb_sum_person');
3758 g_cache_cwb_sum_person (p_person_id).person_id := p_person_id;
3759 g_cache_cwb_sum_person (p_person_id).bg_name := l_bg_and_mgr_name.NAME;
3760 g_cache_cwb_sum_person (p_person_id).bg_id := p_business_group_id;
3761 g_cache_cwb_sum_person (p_person_id).country_code := l_emp_num_and_emp_name.legislation_code;
3762
3763 l_warning := FALSE; --warning not supported completely yet
3764
3765 IF(l_warning) THEN
3766 g_cache_cwb_sum_person (p_person_id).status := 'W';
3767 ELSE
3768 IF(NOT l_error) THEN
3769 IF (l_amount IS NULL)
3770 THEN
3771 g_cache_cwb_sum_person (p_person_id).status := 'SC';
3772 ELSE
3773 g_cache_cwb_sum_person (p_person_id).status := 'SC';
3774 END IF;
3775 ELSE
3776 g_cache_cwb_sum_person (p_person_id).status := 'E';
3777 -- RAISE ben_batch_utils.g_record_error;
3778 END IF;
3779 END IF;
3780
3781 else
3782 BEGIN
3783 write_h ('=====================Processing Person ==========================');
3784 write_h ('||Person Id ' || p_person_id);
3785 write_h ('||Per_in_ler_id ' || p_group_per_in_ler_id);
3786 write_h ('||Person Action id ' || p_person_action_id);
3787 write_h ('||Plan id ' || p_plan_id);
3788 write_h ('=================================================================');
3789 l_counter := g_cache_cwb_rpt_person.COUNT;
3790 l_counter := l_counter + 1;
3791
3792 g_cache_cwb_rpt_person(l_counter).group_pl_id:= p_plan_id;
3793 g_cache_cwb_rpt_person(l_counter).person_id:= p_person_id;
3794 g_cache_cwb_rpt_person(l_counter).assignment_id := l_emp_num_and_emp_name.assignment_id;
3795 g_cache_cwb_rpt_person(l_counter).emp_number := l_emp_num_and_emp_name.employee_number;
3796 g_cache_cwb_rpt_person(l_counter).group_per_in_ler_id:= p_group_per_in_ler_id;
3797 g_cache_cwb_rpt_person(l_counter).full_name:= l_emp_num_and_emp_name.full_name;
3798 g_cache_cwb_rpt_person(l_counter).business_group_name:= l_bg_and_mgr_name.name;
3799 g_cache_cwb_rpt_person(l_counter).business_group_id:= l_bg_and_mgr_name.business_group_id;
3800 g_cache_cwb_rpt_person(l_counter).manager_name:= l_bg_and_mgr_name.full_name;
3801 g_cache_cwb_rpt_person(l_counter).pl_name:= g_group_plan_name;
3802 g_cache_cwb_rpt_person(l_counter).country_code:= l_emp_num_and_emp_name.legislation_code;
3803 g_cache_cwb_rpt_person(l_counter).lf_evt_ocrd_date:= p_lf_evt_ocrd_date;
3804 g_cache_cwb_rpt_person(l_counter).group_per_in_ler_id := p_group_per_in_ler_id;
3805 --g_cache_cwb_rpt_person(l_counter).oipl_id := -1;
3806 --g_cache_cwb_rpt_person(l_counter).eligibility := 'N';
3807
3808 process_life_event(p_person_id
3809 , p_lf_evt_ocrd_date
3810 , p_plan_id
3811 , p_group_per_in_ler_id
3812 , p_effective_date
3813 , p_employees_in_bg);
3814 WRITE('Placeholder Life Event opened.');
3815 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'N';
3816 g_cache_cwb_rpt_person(l_counter).status:= 'SC';
3817 g_cache_cwb_rpt_person(l_counter).lf_evt_closed:= 'N';
3818
3819 EXCEPTION
3820 WHEN OTHERS THEN
3821 WRITE('Life Event not closed due to error');
3822 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'Y';
3823 g_cache_cwb_rpt_person(l_counter).status:= 'E';
3824 g_cache_cwb_rpt_person(l_counter).lf_evt_closed:= 'Y';
3825 END;
3826 end if;
3827
3828 IF (p_validate = 'Y')
3829 THEN
3830 g_actn := 'Running in rollback mode, person rolled back...';
3831 WRITE (g_actn);
3832 ROLLBACK TO backout_post_process_person;
3833 END IF;
3834
3835 IF p_person_action_id IS NOT NULL
3836 THEN
3837 g_actn := 'Updating person actions as processed...';
3838 WRITE (g_actn);
3839 write_h ('Time before updating the person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3840 write_h ('=====================Updating Person Actions==========================');
3841 write_h ('||Person Action id ' || p_person_action_id);
3842 write_h ('||p_object_version_number ' || p_object_version_number);
3843 write_h ('||p_effective_date ' || p_effective_date);
3844 write_h ('================================================================');
3845 ben_person_actions_api.update_person_actions
3846 (p_person_action_id => p_person_action_id
3847 , p_action_status_cd => 'P'
3848 , p_object_version_number => p_object_version_number
3849 , p_effective_date => p_effective_date
3850 );
3851 WRITE ('Time after updating the person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3852 END IF;
3853
3854 g_actn := 'Finished processing the person...';
3855 Write ('----------------------------------------------------');
3856 WRITE (g_actn);
3857 EXCEPTION
3858 WHEN OTHERS
3859 THEN
3860 WRITE('Error at Process Person');
3861 ROLLBACK TO backout_post_process_person;
3862
3863 g_persons_errored := g_persons_errored + 1;
3864 ben_batch_utils.rpt_error (p_proc => g_proc, p_last_actn => g_actn, p_rpt_flag => TRUE);
3865
3866 IF p_person_action_id IS NOT NULL
3867 THEN
3868 ben_person_actions_api.update_person_actions
3869 (p_person_action_id => p_person_action_id
3870 , p_action_status_cd => 'E'
3871 , p_object_version_number => p_object_version_number
3872 , p_effective_date => p_effective_date
3873 );
3874
3875 g_cache_cwb_sum_person (p_person_id).status := 'E';
3876 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'Y';
3877 g_cache_cwb_sum_person (p_person_id).country_code := l_emp_num_and_emp_name.legislation_code;
3878 -- g_cache_cwb_rpt_person (g_cache_cwb_rpt_person.COUNT + 1) := g_cwb_rpt_person;
3879
3880 END IF;
3881 WRITE (benutils.get_message_name);
3882 WRITE (fnd_message.get);
3883 WRITE (SQLERRM||' in process_person');
3884 RAISE ben_batch_utils.g_record_error;
3885 END;
3886
3887
3888 -- ============================================================================
3889 -- << Procedure: Do_Multithread >>
3890 -- Description:
3891 -- this is a main procedure to invoke the Compensation Workbench backout
3892 -- post process.
3893 -- ============================================================================
3894 PROCEDURE do_multithread (
3895 errbuf OUT NOCOPY VARCHAR2
3896 , retcode OUT NOCOPY NUMBER
3897 , p_validate IN VARCHAR2 DEFAULT 'N'
3898 , p_benefit_action_id IN NUMBER
3899 , p_thread_id IN NUMBER
3900 , p_effective_date IN VARCHAR2
3901 , p_audit_log IN VARCHAR2 DEFAULT 'N'
3902 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
3903 , p_is_backout_perf IN VARCHAR2 DEFAULT 'N'
3904 )
3905 IS
3906 l_parm c_parameter%ROWTYPE;
3907 l_commit NUMBER;
3908 l_range_id NUMBER;
3909 l_record_number NUMBER := 0;
3910 l_start_person_action_id NUMBER := 0;
3911 l_end_person_action_id NUMBER := 0;
3912 l_effective_date DATE;
3913 l_threads NUMBER;
3914 l_chunk_size NUMBER;
3915 g_max_errors_allowed NUMBER;
3916 BEGIN
3917 g_proc := 'do_multithread';
3918 benutils.g_benefit_action_id := p_benefit_action_id;
3919 WRITE (g_actn);
3920 write_h ('=====================do_multithread=============');
3921 write_h ('||Parameter Description ');
3922 write_h ('||p_effective_dates - ' || p_effective_date);
3923 write_h ('||p_validate - ' || p_validate);
3924 write_h ('||p_benefit_action_id - ' || p_benefit_action_id);
3925 write_h ('||p_thread_id - ' || p_thread_id);
3926 write_h ('||p_audit_log - ' || p_audit_log);
3927 write_h ('||p_is_self_service - ' || p_is_self_service);
3928 write_h ('||p_is_backout_perf - ' || p_is_backout_perf);
3929 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
3930 --l_effective_date := TRUNC (TO_DATE (p_effective_date, 'YYYY/MM/DD HH24:MI:SS'));
3931 write_m ('l_effective_date is ' || l_effective_date);
3932 g_actn := 'Put row in fnd_sessions...';
3933 WRITE (g_actn);
3934 write_h ('dt_fndate.change_ses_date with ' || l_effective_date);
3935 dt_fndate.change_ses_date (p_ses_date => l_effective_date, p_commit => l_commit);
3936
3937 IF (l_commit = 1)
3938 THEN
3939 write_h ('The session date is committed...');
3940 COMMIT;
3941 END IF;
3942
3943 OPEN c_parameter (p_benefit_action_id);
3944
3945 FETCH c_parameter
3946 INTO l_parm;
3947
3948 CLOSE c_parameter;
3949
3950 benutils.get_parameter (p_business_group_id => l_parm.business_group_id
3951 , p_batch_exe_cd => 'BENCWBBP'
3952 , p_threads => l_threads
3953 , p_chunk_size => l_chunk_size
3954 , p_max_errors => g_max_errors_allowed
3955 );
3956
3957 g_debug_level := l_parm.debug_messages_flag;
3958
3959 write_m ('Time before processing the ranges '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3960
3961 ---- bug 7126872, global var used by salary api to distinguish unapproved proposal from cwb
3962 IF(l_parm.bft_attribute1 = 'Y') THEN
3963 g_is_cwb_component_plan := 'Y';
3964 END IF;
3965 LOOP
3966 OPEN c_range_for_thread (p_benefit_action_id);
3967
3968 FETCH c_range_for_thread
3969 INTO l_range_id
3970 , l_start_person_action_id
3971 , l_end_person_action_id;
3972
3973 EXIT WHEN c_range_for_thread%NOTFOUND;
3974
3975 CLOSE c_range_for_thread;
3976
3977 IF (l_range_id IS NOT NULL)
3978 THEN
3979 write_h ('Range with range_id ' || l_range_id || ' with Starting person action id '
3980 || l_start_person_action_id
3981 );
3982 write_h (' and Ending Person Action id ' || l_end_person_action_id || ' is selected');
3983 g_actn := 'Marking ben_batch_ranges for range_id ' || l_range_id || ' as processed...';
3984 WRITE (g_actn);
3985
3986 UPDATE ben_batch_ranges ran
3987 SET ran.range_status_cd = 'P'
3988 WHERE ran.range_id = l_range_id;
3989
3990 COMMIT;
3991 END IF;
3992
3993 g_cache_person_process.DELETE;
3994 g_actn := 'Loading person data into g_cache_person_process cache...';
3995 WRITE (g_actn);
3996 WRITE ('Time'||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3997
3998 OPEN c_person_for_thread (p_benefit_action_id
3999 , l_start_person_action_id
4000 , l_end_person_action_id
4001 );
4002
4003 l_record_number := 0;
4004
4005 LOOP
4006 FETCH c_person_for_thread
4007 INTO g_cache_person_process (l_record_number + 1).person_id
4008 , g_cache_person_process (l_record_number + 1).person_action_id
4009 , g_cache_person_process (l_record_number + 1).object_version_number
4010 , g_cache_person_process (l_record_number + 1).per_in_ler_id
4011 , g_cache_person_process (l_record_number + 1).non_person_cd;
4012
4013 EXIT WHEN c_person_for_thread%NOTFOUND;
4014 --
4015 l_record_number := l_record_number + 1;
4016 END LOOP;
4017
4018 CLOSE c_person_for_thread;
4019 WRITE ('Time '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4020
4021 WRITE ('Number of Persons selected in this range ' || g_cache_person_process.COUNT);
4022 write_h ('======Parameters required for processing this person ====');
4023 write_h ('||l_parm.business_group_id ' || l_parm.business_group_id);
4024 write_h ('||l_parm.lf_evt_ocrd_dt ' || l_parm.lf_evt_ocrd_dt);
4025 write_h ('||l_parm.pl_id ' || l_parm.pl_id);
4026 write_h ('||l_parm.debug_messages_flag ' || l_parm.debug_messages_flag);
4027 write_h ('||l_parm.bft_attribute1 ' || l_parm.bft_attribute1);
4028 write_h ('=======================================================');
4029 WRITE ('Time '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4030
4031 IF l_record_number > 0
4032 THEN
4033 FOR l_cnt IN 1 .. l_record_number
4034 LOOP
4035 --
4036 BEGIN
4037 g_actn := 'Calling process_person...';
4038 process_person
4039 (p_validate => p_validate
4040 , p_person_id => g_cache_person_process (l_cnt).person_id
4041 , p_business_group_id => l_parm.business_group_id
4042 , p_effective_date => l_effective_date
4043 , p_lf_evt_ocrd_date => l_parm.lf_evt_ocrd_dt
4044 , p_plan_id => l_parm.pl_id
4045 , p_group_per_in_ler_id => g_cache_person_process (l_cnt).per_in_ler_id
4046 , p_person_action_id => g_cache_person_process (l_cnt).person_action_id
4047 , p_object_version_number => g_cache_person_process (l_cnt).object_version_number
4048 , p_audit_log => p_audit_log
4049 , p_debug_level => l_parm.debug_messages_flag
4050 , p_process_sal_comp => l_parm.bft_attribute1
4051 , p_employees_in_bg => l_parm.bft_attribute3
4052 , p_is_self_service => p_is_self_service
4053 , p_is_placeholder => g_cache_person_process (l_cnt).non_person_cd
4054 , p_is_backout_perf => p_is_backout_perf
4055 );
4056 EXCEPTION
4057 WHEN OTHERS
4058 THEN
4059 WRITE(SQLERRM||' in multithread, caught in process_person call');
4060 IF (g_persons_errored > g_max_errors_allowed)
4061 THEN
4062 g_actn := '<<Compensation Workbench Max Error Limit '||g_max_errors_allowed ||' Reached >> ';
4063 WRITE (g_actn);
4064 fnd_message.set_name ('BEN', 'BEN_93145_MAX_LIMIT_REACHED');
4065 -- removed RAISE ben_batch_utils.g_record_error;
4066 raise g_max_error;
4067 END IF;
4068
4069 NULL;
4070 END;
4071 END LOOP;
4072
4073 write_m ('Time after processing the ranges '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4074 ELSE
4075 --
4076 g_actn := 'Erroring out since no person is found in range...';
4077 --
4078 fnd_message.set_name ('BEN', 'BEN_91709_PER_NOT_FND_IN_RNG');
4079 fnd_message.set_token ('PROCEDURE', g_proc);
4080 fnd_message.raise_error;
4081 END IF;
4082
4083 COMMIT;
4084 END LOOP;
4085 g_is_cwb_component_plan := 'N';
4086 print_cache;
4087 EXCEPTION
4088 WHEN g_max_error THEN
4089 WRITE(SQLERRM);
4090 print_cache;
4091 table_corrections(p_benefit_action_id);
4092 COMMIT;
4093 raise g_max_error;
4094 WHEN OTHERS
4095 THEN
4096 WRITE(SQLERRM);
4097 print_cache;
4098 table_corrections(p_benefit_action_id);
4099 COMMIT;
4100 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4101 fnd_message.set_token ('PROCEDURE', g_proc);
4102 fnd_message.set_token ('STEP', g_actn);
4103 fnd_message.raise_error;
4104
4105 END;
4106
4107
4108 -- ============================================================================
4109 -- << Procedure: process >>
4110 -- Description:
4111 -- this is a main procedure to invoke the Compensation Workbench back out
4112 -- post process.
4113 -- ============================================================================
4114 PROCEDURE process (
4115 errbuf OUT NOCOPY VARCHAR2
4116 , retcode OUT NOCOPY NUMBER
4117 , p_effective_date IN VARCHAR2
4118 , p_validate IN VARCHAR2
4119 , p_pl_id IN NUMBER
4120 , p_lf_evt_orcd_date IN VARCHAR2
4121 , p_person_id IN NUMBER DEFAULT NULL
4122 , p_manager_id IN NUMBER DEFAULT NULL
4123 , p_employees_in_bg IN NUMBER DEFAULT NULL
4124 , p_audit_log IN VARCHAR2 DEFAULT 'N'
4125 , p_hidden_audit_log IN VARCHAR2
4126 , p_debug_level IN VARCHAR2 DEFAULT 'L'
4127 , p_bg_id IN NUMBER
4128 , p_is_multi_thread IN VARCHAR2 DEFAULT 'Y'
4129 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
4130 , p_person_selection_rule_id IN NUMBER DEFAULT NULL
4131 , p_is_backout_perf IN VARCHAR2 DEFAULT 'N'
4132 , p_local_plan_list IN VARCHAR2 DEFAULT NULL
4133 , p_is_open_access IN VARCHAR2 DEFAULT 'N'
4134 , p_send_fyi IN VARCHAR2 DEFAULT 'N'
4135 )
4136 IS
4137 --
4138 -- local variable declaration.
4139 --
4140 l_effective_date DATE;
4141 l_commit NUMBER;
4142 l_chunk_size NUMBER;
4143 l_request_id NUMBER;
4144 l_threads NUMBER;
4145 l_benefit_action_id NUMBER;
4146 l_object_version_number NUMBER;
4147 l_num_ranges NUMBER := 0;
4148 l_num_persons NUMBER := 0;
4149 l_comp_reason_count NUMBER;
4150 l_silent_error EXCEPTION;
4151 l_slave_errored EXCEPTION;
4152 l_process_compents VARCHAR2 (1) := 'N';
4153 l_num_rows NUMBER := 0;
4154 ps_rec c_person_selection%ROWTYPE;
4155 l_person_action_ids g_number_type := g_number_type ();
4156 l_person_ids g_number_type := g_number_type ();
4157 l_per_in_ler_ids g_number_type := g_number_type ();
4158 l_is_placeholder g_number_type := g_number_type ();
4159 l_lf_evt_orcd_date DATE;
4160 l_dummy c_slaves%ROWTYPE;
4161 l_person_ok varchar2(1) := 'Y';
4162 l_err_message varchar2(2000);
4163 l_person_id per_all_people_f.person_id%type;
4164 pl_rec c_placeholder_selection%ROWTYPE;
4165 l_count NUMBER;
4166 l_mgr_per_in_ler_ids g_number_type := g_number_type ();
4167 l_pils_for_access c_pils_for_access%ROWTYPE;
4168 l_num_mgr NUMBER := 0;
4169 BEGIN
4170 g_actn := 'Stating the backout post-process...';
4171 WRITE (g_actn);
4172 g_proc := g_package || '.process';
4173 g_debug_level := p_debug_level;
4174 l_lf_evt_orcd_date := trunc(fnd_date.canonical_to_date(p_lf_evt_orcd_date));
4175 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
4176 write_h ('=====================process====================');
4177 write_h ('||Parameter Description ');
4178 write_h ('||p_effective_dates - ' || l_effective_date);
4179 write_h ('||p_validate - ' || p_validate);
4180 write_h ('||p_pl_id - ' || p_pl_id);
4181 write_h ('||p_le_orcd_date - ' || l_lf_evt_orcd_date);
4182 write_h ('||p_person_id - ' || p_person_id);
4183 write_h ('||p_manager_id - ' || p_manager_id);
4184 write_h ('||p_employees_in_bg - ' || p_employees_in_bg);
4185 write_h ('||p_audit_log - ' || p_audit_log);
4186 write_h ('||p_bg_id - ' || p_bg_id);
4187 write_h ('||p_is_multi_thread - ' || p_is_multi_thread);
4188 write_h ('||p_is_self_service - ' || p_is_self_service);
4189 write_h ('||p_person_select_rule_id -' || p_person_selection_rule_id);
4190 write_h ('||p_is_backout_perf - ' || p_is_backout_perf);
4191 write_h ('||p_is_open_access - ' || p_is_open_access);
4192 write_h ('||p_local_plan_list - ' || p_local_plan_list);
4193 write_h ('||p_send_fyi - ' || p_send_fyi);
4194 write_h ('================================================');
4195 write_m ('l_effective_date is ' || l_effective_date);
4196 g_actn := 'Put row in fnd_sessions...';
4197 WRITE (g_actn);
4198 write_h ('dt_fndate.change_ses_date with ' || l_effective_date);
4199 dt_fndate.change_ses_date (p_ses_date => l_effective_date, p_commit => l_commit);
4200 write_h ('Commit value for dt_fndate is ' || l_commit);
4201
4202 IF (l_commit = 1)
4203 THEN
4204 write_h ('The session date is committed...');
4205 COMMIT;
4206 END IF;
4207
4208 g_actn := 'initializing the process parameters';
4209 WRITE (g_actn);
4210 g_exec_param_rec.persons_selected := 0;
4211 g_exec_param_rec.persons_proc_succ := 0;
4212 g_exec_param_rec.persons_errored := 0;
4213 g_exec_param_rec.lf_evt_closed := 0;
4214 g_exec_param_rec.lf_evt_not_closed := 0;
4215 g_exec_param_rec.business_group_id := p_bg_id;
4216 g_exec_param_rec.start_date := SYSDATE;
4217 g_exec_param_rec.start_time := DBMS_UTILITY.get_time;
4218
4219 g_actn := 'Checking for valid read-only-reason to find salary components...';
4220 WRITE (g_actn);
4221 WRITE ('Time'||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4222
4223 OPEN c_component_reason (p_pl_id, l_effective_date);
4224 FETCH c_component_reason INTO l_comp_reason_count;
4225 CLOSE c_component_reason;
4226
4227 WRITE ('Time'||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4228
4229 write_m ('Options with component reason is ' || l_comp_reason_count);
4230
4231 IF l_comp_reason_count > 0 THEN
4232 l_process_compents := 'Y';
4233 END IF;
4234
4235 g_actn := 'Calling ben_batch_utils.ini...';
4236 WRITE (g_actn);
4237 write_h ('ben_batch_utils.ini with PROC_INFO');
4238 ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
4239 g_actn := 'Calling benutils.get_parameter...';
4240 WRITE (g_actn);
4241 write_h ('benutils.get_parameter with ' || p_bg_id || ' ' || 'BENCWBBP' || ' '
4242 || g_max_errors_allowed
4243 );
4244 benutils.get_parameter (p_business_group_id => p_bg_id
4245 , p_batch_exe_cd => 'BENCWBBP'
4246 , p_threads => l_threads
4247 , p_chunk_size => l_chunk_size
4248 , p_max_errors => g_max_errors_allowed
4249 );
4250 write_h ('Values of l_threads is ' || l_threads || ' and l_chunk_size is ' || l_chunk_size);
4251 benutils.g_thread_id := 99; -- need to investigate why this is needed
4252 g_actn := 'Creating benefit actions...';
4253 WRITE (g_actn);
4254 WRITE ('Time'||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4255 write_h ('=====================Benefit Actions=======================');
4256 write_h ('||Parameter value ');
4257 write_h ('||p_request_id- ' || fnd_global.conc_request_id);
4258 write_h ('||p_program_application_id- ' || fnd_global.prog_appl_id);
4259 write_h ('||p_program_id- ' || fnd_global.conc_program_id);
4260 write_h ('==========================================================');
4261 ben_benefit_actions_api.create_perf_benefit_actions
4262 (p_benefit_action_id => l_benefit_action_id
4263 , p_process_date => l_effective_date
4264 , p_mode_cd => 'W'
4265 , p_derivable_factors_flag => 'NONE'
4266 , p_validate_flag => p_validate
4267 , p_debug_messages_flag => NVL (p_debug_level
4268 , 'N'
4269 )
4270 , p_business_group_id => p_bg_id
4271 , p_no_programs_flag => 'N'
4272 , p_no_plans_flag => 'N'
4273 , p_audit_log_flag => p_audit_log
4274 , p_pl_id => p_pl_id
4275 , p_pgm_id => -9999
4276 , p_lf_evt_ocrd_dt => l_lf_evt_orcd_date
4277 , p_person_id => p_person_id
4278 , p_object_version_number => l_object_version_number
4279 , p_effective_date => l_effective_date
4280 , p_request_id => fnd_global.conc_request_id
4281 , p_program_application_id => fnd_global.prog_appl_id
4282 , p_program_id => fnd_global.conc_program_id
4283 , p_program_update_date => SYSDATE
4284 , p_bft_attribute1 => l_process_compents
4285 , p_bft_attribute3 => p_employees_in_bg
4286 , p_bft_attribute4 => p_manager_id
4287 , p_bft_attribute5 => p_person_selection_rule_id
4288 , p_bft_attribute6 => p_is_backout_perf
4289 , p_bft_attribute7 => p_is_open_access
4290 , p_bft_attribute8 => p_send_fyi
4291 );
4292 write ('Benefit Action Id is ' || l_benefit_action_id);
4293 benutils.g_benefit_action_id := l_benefit_action_id;
4294 g_actn := 'Inserting Person Actions...';
4295 WRITE (g_actn);
4296 write_m ('Time before processing the person selections '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4297
4298 OPEN c_placeholder_selection (p_pl_id
4299 , l_lf_evt_orcd_date
4300 , p_person_id
4301 , p_manager_id
4302 , p_employees_in_bg
4303 , l_effective_date
4304 , p_local_plan_list
4305 );
4306
4307 LOOP
4308 FETCH c_placeholder_selection
4309 INTO pl_rec;
4310
4311 EXIT WHEN c_placeholder_selection%NOTFOUND;
4312
4313 l_person_ok := 'Y';
4314 l_person_id :=pl_rec.person_id;
4315
4316 If p_person_selection_rule_id is not NULL then
4317 --
4318 begin
4319 ben_batch_utils.person_selection_rule
4320 (p_person_id => l_person_id
4321 ,p_business_group_id => pl_rec.business_group_id
4322 ,p_person_selection_rule_id=> p_person_selection_rule_id
4323 ,p_effective_date => l_effective_date
4324 ,p_return => l_person_ok
4325 ,p_err_message => l_err_message );
4326
4327 if l_err_message is not null
4328 then
4329 Ben_batch_utils.write(p_text =>
4330 '<< Person id : '||to_char(l_person_id)||' failed.'||
4331 ' Reason : '|| l_err_message ||' >>' );
4332 l_err_message := NULL ;
4333 end if ;
4334 exception
4335 when others then
4336 l_person_ok:='N';
4337 end;
4338 --
4339 End if;
4340
4341
4342 If l_person_ok = 'Y' then
4343
4344 l_num_rows := l_num_rows + 1;
4345 l_num_persons := l_num_persons + 1;
4346 l_person_action_ids.EXTEND (1);
4347 l_person_ids.EXTEND (1);
4348 l_per_in_ler_ids.EXTEND (1);
4349 l_is_placeholder.EXTEND (1);
4350
4351 SELECT ben_person_actions_s.NEXTVAL
4352 INTO l_person_action_ids (l_num_rows)
4353 FROM DUAL;
4354
4355 l_person_ids (l_num_rows) := pl_rec.person_id;
4356 l_per_in_ler_ids (l_num_rows) := pl_rec.per_in_ler_id;
4357 l_is_placeholder (l_num_rows) := 1;
4358
4359 write_h ('============Placeholder Person Header==================');
4360 write_h ('||Person Name ' || pl_rec.full_name);
4361 write_h ('||Business Group ' || pl_rec.NAME);
4362 write_h ('||Person Id ' || pl_rec.person_id);
4363 write_h ('||Per_in_ler_id ' || pl_rec.per_in_ler_id);
4364 write_h ('||Person Action id ' || l_person_action_ids (l_num_rows));
4365 write_h ('=======================================================');
4366
4367 end if;
4368
4369 IF l_num_rows = l_chunk_size
4370 THEN
4371 l_num_ranges := l_num_ranges + 1;
4372 insert_person_actions (p_per_actn_id_array => l_person_action_ids
4373 , p_per_id => l_person_ids
4374 , p_group_per_in_ler_id => l_per_in_ler_ids
4375 , p_benefit_action_id => l_benefit_action_id
4376 , p_is_placeholder => l_is_placeholder
4377 );
4378 l_num_rows := 0;
4379 l_person_action_ids.DELETE;
4380 l_person_ids.DELETE;
4381 l_per_in_ler_ids.DELETE;
4382 l_is_placeholder.DELETE;
4383 END IF;
4384 g_cache_cwb_sum_person (pl_rec.person_id).person_id := pl_rec.person_id;
4385 g_cache_cwb_sum_person (pl_rec.person_id).bg_name := pl_rec.NAME;
4386 g_cache_cwb_sum_person (pl_rec.person_id).bg_id := p_bg_id;
4387 g_cache_cwb_sum_person (pl_rec.person_id).country_code := pl_rec.legislation_code;
4388 g_cache_cwb_sum_person (pl_rec.person_id).person_name := pl_rec.full_name;
4389 g_cache_cwb_sum_person (pl_rec.person_id).benefit_action_id := l_benefit_action_id;
4390 END LOOP;
4391
4392 CLOSE c_placeholder_selection;
4393
4394 OPEN c_person_selection (p_pl_id
4395 , l_lf_evt_orcd_date
4396 , p_person_id
4397 , p_manager_id
4398 , p_employees_in_bg
4399 , l_effective_date
4400 , p_local_plan_list
4401 );
4402
4403 LOOP
4404 FETCH c_person_selection
4405 INTO ps_rec;
4406
4407 EXIT WHEN c_person_selection%NOTFOUND;
4408
4409 l_person_ok := 'Y';
4410 l_person_id :=ps_rec.person_id;
4411
4412 If p_person_selection_rule_id is not NULL then
4413 --
4414 begin
4415 ben_batch_utils.person_selection_rule
4416 (p_person_id => l_person_id
4417 ,p_business_group_id => ps_rec.business_group_id
4418 ,p_person_selection_rule_id=> p_person_selection_rule_id
4419 ,p_effective_date => l_effective_date
4420 ,p_return => l_person_ok
4421 ,p_err_message => l_err_message );
4422
4423 if l_err_message is not null
4424 then
4425 Ben_batch_utils.write(p_text =>
4426 '<< Person id : '||to_char(l_person_id)||' failed.'||
4427 ' Reason : '|| l_err_message ||' >>' );
4428 l_err_message := NULL ;
4429 end if ;
4430 exception
4431 when others then
4432 l_person_ok:='N';
4433 end;
4434 --
4435 End if;
4436
4437
4438 If l_person_ok = 'Y' then
4439
4440 l_num_rows := l_num_rows + 1;
4441 l_num_persons := l_num_persons + 1;
4442 l_person_action_ids.EXTEND (1);
4443 l_person_ids.EXTEND (1);
4444 l_per_in_ler_ids.EXTEND (1);
4445 l_is_placeholder.EXTEND (1);
4446
4447
4448 SELECT ben_person_actions_s.NEXTVAL
4449 INTO l_person_action_ids (l_num_rows)
4450 FROM DUAL;
4451
4452 l_person_ids (l_num_rows) := ps_rec.person_id;
4453 l_per_in_ler_ids (l_num_rows) := ps_rec.per_in_ler_id;
4454 l_is_placeholder(l_num_rows) := 0;
4455
4456 write_h ('=====================Person Header====================');
4457 write_h ('||Person Name ' || ps_rec.full_name);
4458 write_h ('||Business Group ' || ps_rec.NAME);
4459 write_h ('||Person Id ' || ps_rec.person_id);
4460 write_h ('||Per_in_ler_id ' || ps_rec.per_in_ler_id);
4461 write_h ('||Person Action id ' || l_person_action_ids (l_num_rows));
4462 write_h ('=======================================================');
4463
4464 end if;
4465
4466 IF l_num_rows = l_chunk_size
4467 THEN
4468 l_num_ranges := l_num_ranges + 1;
4469 insert_person_actions (p_per_actn_id_array => l_person_action_ids
4470 , p_per_id => l_person_ids
4471 , p_group_per_in_ler_id => l_per_in_ler_ids
4472 , p_benefit_action_id => l_benefit_action_id
4473 , p_is_placeholder => l_is_placeholder
4474 );
4475 l_num_rows := 0;
4476 l_person_action_ids.DELETE;
4477 l_person_ids.DELETE;
4478 l_per_in_ler_ids.DELETE;
4479 l_is_placeholder.DELETE;
4480 END IF;
4481 END LOOP;
4482
4483 CLOSE c_person_selection;
4484
4485 g_person_selected := l_num_rows;
4486 WRITE ('Total no of person selected - ' || g_person_selected);
4487 g_actn := 'Inserting the last range of persons if exists...';
4488 WRITE (g_actn);
4489 write_m ('Time after processing the person selections '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4490
4491
4492 IF l_num_rows <> 0
4493 THEN
4494 l_num_ranges := l_num_ranges + 1;
4495 insert_person_actions (p_per_actn_id_array => l_person_action_ids
4496 , p_per_id => l_person_ids
4497 , p_group_per_in_ler_id => l_per_in_ler_ids
4498 , p_benefit_action_id => l_benefit_action_id
4499 , p_is_placeholder => l_is_placeholder
4500 );
4501 l_num_rows := 0;
4502 l_person_action_ids.DELETE;
4503 l_person_ids.DELETE;
4504 l_per_in_ler_ids.DELETE;
4505 l_is_placeholder.DELETE;
4506 END IF;
4507
4508 COMMIT;
4509
4510 l_num_mgr := 0;
4511 IF(p_is_open_access = 'Y') THEN
4512 OPEN c_pils_for_access (p_pl_id
4513 , l_lf_evt_orcd_date
4514 , p_person_id
4515 , p_manager_id
4516 , p_employees_in_bg
4517 , l_effective_date
4518 , p_local_plan_list
4519 );
4520
4521 LOOP
4522 FETCH c_pils_for_access
4523 INTO l_pils_for_access;
4524
4525 EXIT WHEN c_pils_for_access%NOTFOUND;
4526 l_num_mgr := l_num_mgr + 1;
4527 l_mgr_per_in_ler_ids.EXTEND (1);
4528 l_mgr_per_in_ler_ids(l_num_mgr) := l_pils_for_access.per_in_ler_id;
4529
4530 END LOOP;
4531 CLOSE c_pils_for_access;
4532 END IF;
4533
4534 g_actn := 'Submitting job to con-current manager...';
4535 WRITE (g_actn);
4536 g_actn := 'Preparing for launching concurrent requests';
4537 WRITE (g_actn);
4538 ben_batch_utils.g_num_processes := 0;
4539 ben_batch_utils.g_processes_tbl.DELETE;
4540
4541 write_m ('Time before launching the threads '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4542
4543 IF l_num_ranges > 1
4544 THEN
4545 FOR l_count IN 1 .. LEAST (l_threads, l_num_ranges) - 1
4546 LOOP
4547 write_h ('=====================Request Parameters===================');
4548 write_h ('||Parameter value ');
4549 write_h ('||argument2- ' || l_benefit_action_id);
4550 write_h ('||argument3- ' || l_count);
4551 write_h ('==========================================================');
4552 l_request_id :=
4553 fnd_request.submit_request (application => 'BEN'
4554 , program => 'BENCWBPT'
4555 , description => NULL
4556 , sub_request => FALSE
4557 , argument1 => p_validate
4558 , argument2 => l_benefit_action_id
4559 , argument3 => l_count
4560 , argument4 => p_effective_date
4561 , argument5 => p_audit_log
4562 , argument6 => p_is_self_service
4563 , argument7 => p_is_backout_perf
4564 );
4565 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
4566 ben_batch_utils.g_processes_tbl (ben_batch_utils.g_num_processes) := l_request_id;
4567 write_m ('request id for this thread ' || l_request_id);
4568 COMMIT;
4569 END LOOP;
4570 ELSIF l_num_ranges = 0
4571 THEN
4572 WRITE ('<< No Person got selected with above selection criteria >>');
4573 fnd_message.set_name ('BEN', 'BEN_91769_NOONE_TO_PROCESS');
4574 fnd_message.set_token ('PROC', g_proc);
4575 RAISE l_silent_error;
4576 END IF;
4577
4578 write_m ('Time after launching the threads '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4579
4580 write_h ('=====================do_multithread in Process============');
4581 write_h ('||Parameter value ');
4582 write_h ('||p_benefit_action_id- ' || l_benefit_action_id);
4583 write_h ('||p_thread_id- ' || (l_threads + 1));
4584 write_h ('==========================================================');
4585 do_multithread (errbuf => errbuf
4586 , retcode => retcode
4587 , p_validate => p_validate
4588 , p_benefit_action_id => l_benefit_action_id
4589 , p_thread_id => l_threads + 1
4590 , p_effective_date => p_effective_date
4591 , p_audit_log => p_audit_log
4592 , p_is_self_service => p_is_self_service
4593 , p_is_backout_perf => p_is_backout_perf
4594 );
4595 g_actn := 'Calling ben_batch_utils.check_all_slaves_finished...';
4596 WRITE (g_actn);
4597
4598 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
4599
4600 IF(p_is_open_access = 'Y') THEN
4601 g_actn := 'Calling process_access...';
4602 WRITE (g_actn);
4603
4604 process_access( p_pl_id
4605 , l_lf_evt_orcd_date
4606 , l_mgr_per_in_ler_ids
4607 , p_validate
4608 , p_send_fyi
4609 , l_effective_date
4610 );
4611 END IF;
4612
4613 g_actn := 'Calling end_process...';
4614 WRITE (g_actn);
4615
4616 write_h ('=====================End Process==========');
4617 write_h ('||Parameter value ');
4618 write_h ('||p_benefit_action_id- ' || l_benefit_action_id);
4619 write_h ('||p_person_selected- ' || l_num_persons);
4620 write_h ('==========================================================');
4621 end_process (p_benefit_action_id => l_benefit_action_id
4622 , p_person_selected => l_num_persons
4623 , p_business_group_id => p_bg_id
4624 );
4625 table_corrections(l_benefit_action_id);
4626 g_actn := 'Finished Process Procedure...';
4627 WRITE (g_actn);
4628
4629 BEGIN
4630 For l_count in 1..ben_batch_utils.g_num_processes loop
4631 open c_slaves(ben_batch_utils.g_processes_tbl(l_count));
4632 fetch c_slaves into l_dummy;
4633 If c_slaves%found then
4634 close c_slaves;
4635 raise l_slave_errored;
4636 exit;
4637 End if;
4638 Close c_slaves;
4639 End loop;
4640 EXCEPTION
4641 WHEN l_slave_errored THEN
4642 --fnd_message.set_name ('BEN', 'BEN_94890_CWB_PROC_SLAVE_ERROR');
4643 --fnd_message.set_name('BEN', 'BEN_93145_MAX_LIMIT_REACHED');
4644 g_actn:= 'slave processes';
4645 raise g_slave_error;
4646 END;
4647
4648 EXCEPTION
4649 --
4650 WHEN l_silent_error
4651 THEN
4652 WRITE (fnd_message.get);
4653 IF (l_num_ranges > 0) THEN
4654 WRITE('END_PROCESS');
4655 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
4656 end_process (p_benefit_action_id => l_benefit_action_id
4657 , p_person_selected => l_num_persons
4658 , p_business_group_id => p_bg_id
4659 );
4660 END IF;
4661 --
4662 WHEN g_slave_error THEN
4663 WRITE (fnd_message.get);
4664 WRITE (SQLERRM);
4665 WRITE ('Big Error Occurred');
4666 IF (l_num_ranges > 0) THEN
4667 WRITE('END_PROCESS');
4668 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
4669 end_process (p_benefit_action_id => l_benefit_action_id
4670 , p_person_selected => l_num_persons
4671 , p_business_group_id => p_bg_id
4672 );
4673 END IF;
4674 fnd_message.clear();
4675 fnd_message.set_name('BEN', 'BEN_94890_CWB_PROC_SLAVE_ERROR');
4676 --fnd_message.set_name('BEN', 'BEN_93145_MAX_LIMIT_REACHED');
4677 --fnd_message.raise_error;
4678 RAISE_APPLICATION_ERROR(-20001,fnd_global.Newline||fnd_message.get||fnd_global.Newline);
4679 --
4680 WHEN g_max_error THEN
4681 WRITE (fnd_message.get);
4682 WRITE (SQLERRM);
4683 WRITE ('Big Error Occurred');
4684 IF (l_num_ranges > 0) THEN
4685 WRITE('END_PROCESS');
4686 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
4687 end_process (p_benefit_action_id => l_benefit_action_id
4688 , p_person_selected => l_num_persons
4689 , p_business_group_id => p_bg_id
4690 );
4691 END IF;
4692
4693 fnd_message.set_name('BEN', 'BEN_93145_MAX_LIMIT_REACHED');
4694 --fnd_message.raise_error;
4695 RAISE_APPLICATION_ERROR(-20001,fnd_global.Newline||fnd_message.get||fnd_global.Newline);
4696 --
4697 WHEN OTHERS THEN
4698 WRITE (fnd_message.get);
4699 WRITE (SQLERRM);
4700 WRITE ('Big Error Occurred');
4701 IF (l_num_ranges > 0) THEN
4702 WRITE('END_PROCESS');
4703 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
4704 end_process (p_benefit_action_id => l_benefit_action_id
4705 , p_person_selected => l_num_persons
4706 , p_business_group_id => p_bg_id
4707 );
4708 END IF;
4709
4710 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4711 fnd_message.set_token ('PROCEDURE', g_proc);
4712 fnd_message.set_token ('STEP', g_actn);
4713 fnd_message.raise_error;
4714 END;
4715
4716
4717 -- Package end
4718 END;