[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_POST_PROCESS
Source
1 PACKAGE BODY BEN_CWB_POST_PROCESS AS
2 /* $Header: bencwbpp.pkb 120.58.12010000.6 2008/11/13 06:21:18 sgnanama ship $ */
3 --
4 /* ============================================================================
5 * Name
6 * Compensation Workbench Post Process
7 *
8 * Purpose
9 * The purpose of this package is to perform the postprocess process of
10 * compensation workbench.
11 *
12 * History
13 * 23-DEC-01 aprabhak 115.0 created
14 * 08-FEB-02 aprabhak 115.1 next version
15 * 21-FEB-02 aprabhak 115.3 person with no WS amount
16 * will get the WS status updated to
17 * processed
18 * 02-MAR-02 aprabhak 115.4 Added a new message for effective date
19 * before assigned life event date
20 * 08-MAR-02 aprabhak 115.5 Modified the salary rise to consider
21 * the precision. The logging will be done
22 * only when you have the audit_log flag is
23 * 'Y'. Modified the choice loop to avoid
24 * the multi-row edit for the persons of
25 * x-bg. Modified the name of the routines
26 * of default_comp_obj and process_default
27 * _enrt.
28 * 10-MAR-02 aprabhak 115.6 The salary conversions now uses the newly
29 * developed benutils routine plan_to_basis_
30 * _conversion. Change the position of the
31 * p_preson_id in the process routine.
32 * 11-MAR-02 aprabhak 115.7 added the per_pay_bases to the element
33 * cursor and approved condition to the
34 * salary cursor
35 * 15-MAR-02 aprabhak 115.8 after the pp runs the worksheet access
36 * to RO it the prior access is updateable
37 * 18-MAR-02 aprabhak 115.9 the parameter p_annulization_factor is
38 * changed to p_assignment_id to the call
39 * to plan_to_basis_conversion.
40 * 27-MAR-02 aprabhak 115.10 Adding the business_group_id condition
41 * to the c_pl_typ_rt_val cursor to avoid
42 * full table scan.
43 * 03-Sep-02 maagrawa 115.11 Added new procedures for promotion
44 * and performance rating updates.
45 * 08-Nov-02 aprabhak 115.12 Included the changes for itemization.
46 * 19-Feb-03 maagrawa 115.13 Make calls to ben_cwb_asg_update to
47 * update performance and promotions.
48 * 05-Feb-03 aprabhak 115.14 Fixed 2815207
49 * 11-Mar-03 pbodla 115.15 Changes for : 2695023 - When pay proposal
50 * is created link it to associated
51 * participant rate row to allow backout if
52 * needed.
53 * 13-Mar-03 pbodla 115.16 Changes for : 2695023 - Fixed typo in
54 * update_prtt_rt_val api call.
55 * 20-Mar-03 aprabhak 115.17 Fixed 2857327 and 2357197.
56 * 20-May-03 aprabhak 115.18 Fixed 2968662.
57 * 18-Jun-03 aprabhak 115.19 Fixed 3011682
58 * 27-Jul-03 aprabhak 115.20 Fixed 3005203
59 * 09-sep-03 sthota 115.21 Modified the effective date format. Fixed
60 * 3084042
61 * 09-Oct-03 sthota 115.26 Fixing the bug 3084042.
62 * 23-Nov-03 aprabhak 115.27 Fixing the bug 3259373.
63 * 02-Jan-04 aprabhak 115.28 Global Budgeting
64 * 10-Feb-04 aprabhak 115.29 Ignore backed out pils in pil cursor
65 * 22-Feb-04 aprabhak 115.31 Uncommented code for comp posting date
66 * 22-Feb-04 aprabhak 115.32 nvl condition to p_debug_level
67 * 08-Mar-04 aprabhak 115.36 Fixed 3490171.
68 * 09-Mar-04 aprabhak 115.37 Fixed the null rows in audit log report
69 * 16-Mar-04 aprabhak 115.38 For Drop3
70 * 20-Mar-04 aprabhak 115.39 Fixed 3490387, 3484230
71 * 25-May-04 maagrawa 115.40 Splitting of perf/promo record.
72 * 04-Jun-04 aprabhak 115.41 Corrected the threading issue.
73 * 04-Jun-04 aprabhak 115.42 Corrected the thread process name
74 * 29-Jun-04 aprabhak 115.43 Fixed the bug #3712169
75 * 30-Jun-04 aprabhak 115.44 bg_id passed to the assignment changes
76 * is obtained ben_cwb_person_info
77 * 06-Jul-04 aprabhak 115.45 Access Change Routine
78 * 09-Jul-04 aprabhak 115.46 Recent Sal Change Message Corrected
79 * 14-Jul-04 aprabhak 115.47 fixed the issues reported in drop 13
80 * 22-Jul-04 aprabhak 115.48 Fixed the caching issues in error report.
81 * 10-Dec-04 aprabhak 115.49 Fixed bug#4030870
82 * 01-Feb-05 steotia 115.50 ben_cwb_audit_api call to record end of
83 * Compensation event post process
84 * 31-May-05 steotia 115.51 Bugfix 4387327
85 * 25-Jul-05 steotia 115.52 Bugfix 4503153
86 * 28-Jul-05 maagrawa 115.53 4510733: Auto Aprove pay proposal
87 * when components are involved.
88 * 01-Aug-05 steotia 115.54 Added NOCOPY hint
89 * 05-Oct-05 steotia 115.55 4607721: Fix in process of rating.
90 * 17-Nov-05 maagrawa 115.56 Do not error for terminated emps
91 * when posting salary/elements.
92 * 28-Nov-05 maagrawa 115.57 4752433:Allow salary components along
93 * with other compensation components.
94 * 30-Nov-05 maagrawa 115.58 Fixed no-data-found error with optName.
95 * 06-Dec-05 maagrawa 115.59 Fixed salary components cursors.
96 * 04-Jan-06 steotia 115.60 Override dates functionality added.
97 * 14-Feb-06 steotia 115.61 4997896:Termination check for perf/prom
98 * not on run date but resp. eff/ovrd date
99 * 06-Mar-06 steotia 115.62 Enhancing logging for new audit report
100 * and logging
101 * 16-Mar-06 steotia 115.63 same as above
102 * 21-Mar-06 steotia 115.64 equalising population across
103 * compensation, perf or asgn changes
104 * 22-Mar-06 steotia 115.65 5109850: taking in LE date as varchar2
105 * 23-Mar-06 steotia 115.66 Fixing component plan logging
106 * [l_warning_text size increased, logging
107 * at -1 level also for component plan,
108 * ws_sub_acty_typ_cd added to comp cursor],
109 * All or nothing error flagging,
110 * elmnt_processing_type for recurring el,
111 * ineligible persons logged
112 * 03-Apr-06 steotia 115.67 Logging even in error for sal_rate
113 * Element determintion rule called
114 * 07-Apr-06 steotia 115.68 5141153: Corrections on el. detn. rule
115 * 12-Apr-06 steotia 115.70 Fixing a possible char to numeric convern.
116 * problem in extracting message number
117 * 26-Apr-06 steotia 115.71 Fixing component plan logging for plan
118 * lvl amount posted record, checking
119 * input currency of element, inserting
120 * 2 new error messages
121 * 29-Apr-06 steotia 115.73 Correcting sal change reason logic
122 * 10-May-06 steotia 115.74 5158117: Non-Mon rate exclusion
123 * 5181394: Future dated sal prop warning
124 * 16-May-06 steotia 115.75 5222874: missing data for recurring element
125 * 16-May-06 steotia 115.76 5158117: Salary basis element check add
126 * 19-May-06 steotia 115.78 Logging changes
127 * 14-Jul-06 steotia 115.79 Added force close of LE
128 * 17-Jul-06 steotia 115.80 5392779: Properly converting base_salary
129 * 5375170: String overflow error
130 * 11-Aug-06 steotia 115.81 5413842: In case of one emp in multiple
131 * local plan
132 * 25-Aug-06 steotia 115.82 5487492: No force close in rollback
133 * 12-Sep-06 steotia 115.83 5413842: Downloads need complete rows
134 * 13-Sep-06 steotia 115.84 5483387: Wrong order of concatenation
135 * 13-Sep-06 steotia 115.85 5528259: (+) in c_posted_promotions reqd.
136 * 20-Sep-06 steotia 115.86 5531065: Using Performance Overrides (but
137 * only if used through SS)
138 * 28-Sep-06 steotia 115.87 5413842: moving to parent thread
139 * 05-Oct-06 steotia 115.88 Putting do_not_process_flag check with
140 * rates to take care of multiple
141 * enrollments
142 * 17-Oct-06 steotia 115.89 5527054: using 5 precision if uom of
143 * input_value is not null or Money
144 * 5460693: using option level effective
145 * for salary proposal changes
146 * 02-Nov-06 steotia 115.90 5521472: if slave errors master errors
147 * 06-Nov-06 steotia 115.91 5235393: null->0 for amounts/salary
148 * 17-Nov-06 maagrawa 115.92 Do not post zeros or nulls for salary
149 * components.
150 * 23-Nov-06 steotia 115.93 5659359: No more error stacking
151 * 3926221: Ineligs get no perf/promotion
152 * 3928529: process_access overhauled
153 * trunc used to get effective date
154 * 18-Jan-07 maagrawa 115.94 Log old and new salary when salary
155 * changed error is thrown.
156 * 04-Mar-07 steotia 115.95 5505775: CWB Enhancement
157 * Introducing Person Selection Rule
158 * 25-Apr-07 steotia 115.96 Closing LE of placeholder mgs also.
159 * 16-Jan-08 steotia 115.101 Compare rounded [proposal vs base sal]
160 * Rate Start Date enabled
161 * 18-Jan-08 steotia 115.102 Checking for ws_abr_id for above
162 * 18-Jan-08 steotia 115.103 Overriding modified
163 * 08-Apr-08 sgnanama 115.104 Added p_use_rate_start_date in submit_request
164 * 22-Apr-08 cakunuru 115.105 Changed the cursor c_placeholder_selection:
165 * will check for ineligible employees who are not managers.
166 * 7-May-08 sgnanama 115.106 selected business_group_id of the person in the
167 * c_person_selection and c_placeholder_selection and pass
168 * the same to the procedure evaluating the person seelction rule.
169 * 20-May-08 cakunuru 115.107 Changed the message in process_sal_comp_rates.
170 * 27-May-08 sgnanama 115.108 7126872:Added g_is_cwb_component_plan which is
171 * used by salary api to distinguish unapproved
172 * proposal from cwb
173 * 10-Jun-08 cakunuru 115.109 7155018: Added a condition for the cursor
174 * c_pils_for_access to check for approval_cd with 'AP'.
175 * 10-Jun-08 cakunuru 115.110 Changed the dbdrv checkfile comment.
176 * 18-Aug-08 cakunuru 115.111 6994188: Set the effective_date as null if error occurs.
177 * 05-Nov-08 cakunuru 115.112 7042887: Modified reason to get the meaning
178 * instead of reason code in the print_cache procedure.
179 * 13-Nov-08 sgnanama 115.113 7218121: Modified the check to assign the warning text
180 * to p_cache_cwb_rpt_person in process_sal_comp_rates
181 * --------------------------------------------------------------------------
182 */
183 --
184 -- Global cursor and variables declaration
185 --
186 TYPE plan_override_date IS RECORD (
187 plan ben_cwb_pl_dsgn.pl_id%type,
188 date ben_cwb_pl_dsgn.ovrid_rt_strt_dt%type);
189
190 TYPE g_override_date_t IS TABLE OF plan_override_date;
191
192 TYPE plan_abr_info IS RECORD (
193 pl_id ben_cwb_pl_dsgn.pl_id%type,
194 oipl_id ben_cwb_pl_dsgn.oipl_id%type,
195 element_type_id ben_cwb_pl_dsgn.ws_element_type_id%type,
196 input_value_id ben_cwb_pl_dsgn.ws_input_value_id%type);
197
198 TYPE g_abr_info_t IS TABLE OF plan_abr_info;
199
200 g_package VARCHAR2 (80) := 'BEN_CWB_POST_PROCESS';
201 g_max_errors_allowed NUMBER (9) := 200;
202 g_persons_errored NUMBER (9) := 0;
203 g_persons_procd NUMBER (9) := 0;
204 g_person_selected NUMBER (9) := 0;
205 g_lf_evt_closed NUMBER (9) := 0;
206 g_lf_evt_not_closed NUMBER (9) := 0;
207 g_proc VARCHAR2 (80);
208 g_actn VARCHAR2 (2000);
209 g_debug_level VARCHAR2 (1);
210 g_slave_error EXCEPTION;
211 g_max_error EXCEPTION;
212 g_person_errored BOOLEAN;
213
214 g_override_dates g_override_date_t := g_override_date_t();
215 g_plan_abr_info g_abr_info_t := g_abr_info_t();
216
217 CURSOR c_table_correction_data(v_benefit_action_id IN NUMBER)
218 IS
219 SELECT o.oipl_id,
220 rpt.group_per_in_ler_id,
221 rpt.pl_id,
222 group_oipl.oipl_id group_oipl_id,
223 rpt.person_id
224 FROM ben_oipl_f o,
225 ben_cwb_rpt_detail rpt,
226 ben_oipl_f local_oipl,
227 ben_opt_f local_opt,
228 ben_opt_f group_opt,
229 ben_oipl_f group_oipl
230 WHERE o.pl_id = rpt.pl_id
231 AND rpt.benefit_action_id = v_benefit_action_id
232 AND rpt.oipl_id = -1
233 AND local_oipl.oipl_id = o.oipl_id
234 AND local_opt.opt_id = local_oipl.opt_id
235 AND group_opt.group_opt_id = local_opt.group_opt_id
236 AND group_oipl.opt_id = group_opt.group_opt_id
237 AND NOT EXISTS
238 (SELECT NULL
239 FROM ben_cwb_rpt_detail
240 WHERE oipl_id = o.oipl_id
241 AND benefit_action_id = v_benefit_action_id)
242 GROUP BY o.oipl_id,
243 rpt.group_per_in_ler_id,
244 rpt.pl_id,
245 group_oipl.oipl_id,
246 rpt.person_id;
247
248 CURSOR c_get_abr_info(v_lf_evt_ocrd_date IN DATE
249 ,v_pl_id IN NUMBER
250 ,v_oipl_id IN NUMBER)
251 IS
252 SELECT ws_element_type_id, ws_input_value_id
253 FROM ben_cwb_pl_dsgn
254 WHERE lf_evt_ocrd_dt = v_lf_evt_ocrd_date
255 AND pl_id = v_pl_id
256 AND oipl_id = v_oipl_id;
257
258 CURSOR c_override_start_date(v_group_pl_id IN NUMBER
259 ,v_pl_id IN NUMBER
260 ,v_group_oipl_id IN NUMBER
261 ,v_oipl_id IN NUMBER
262 ,v_lf_evt_ocrd_date IN DATE)
263 IS
264 SELECT ovrid_rt_strt_dt
265 FROM ben_cwb_pl_dsgn dsgn
266 WHERE group_pl_id = v_group_pl_id
267 AND pl_id = v_pl_id
268 AND group_oipl_id = v_group_oipl_id
269 AND oipl_id = v_oipl_id
270 AND lf_evt_ocrd_dt = v_lf_evt_ocrd_date;
271
272
273 CURSOR c_pil_ovn (v_per_in_ler_id IN NUMBER)
274 IS
275 SELECT object_version_number
276 FROM ben_per_in_ler pil
277 WHERE pil.per_in_ler_id = v_per_in_ler_id;
278
279 CURSOR c_info_ovn (v_group_per_in_ler_id IN NUMBER)
280 IS
281 SELECT object_version_number
282 FROM ben_cwb_person_info info
283 WHERE info.group_per_in_ler_id = v_group_per_in_ler_id;
284
285 CURSOR c_rate_ovn (v_group_per_in_ler_id IN NUMBER, v_pl_id IN NUMBER, v_oipl_id IN NUMBER)
286 IS
287 SELECT object_version_number
288 FROM ben_cwb_person_rates rt
289 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
290 AND rt.pl_id = v_pl_id
291 AND rt.oipl_id = v_oipl_id;
292
293 CURSOR c_grp_ovn (
294 v_group_per_in_ler_id IN NUMBER
295 , v_group_pl_id IN NUMBER
296 , v_group_oipl_id IN NUMBER
297 )
298 IS
299 SELECT object_version_number
300 ,access_cd
301 ,approval_cd
302 FROM ben_cwb_person_groups grp
303 WHERE grp.group_per_in_ler_id = v_group_per_in_ler_id
304 AND grp.group_pl_id = v_group_pl_id
305 AND grp.group_oipl_id = v_group_oipl_id;
306
307 CURSOR c_bg_and_mgr_name (v_group_per_in_ler_id IN NUMBER, v_effective_date IN DATE)
308 IS
309 SELECT bg.NAME
310 , per.full_name
311 , info.business_group_id
312 FROM per_business_groups_perf bg
313 , ben_cwb_person_info info
314 , per_all_people_f per
315 , ben_per_in_ler pil
316 WHERE info.group_per_in_ler_id = v_group_per_in_ler_id
317 AND bg.business_group_id = info.business_group_id
318 AND v_effective_date >= bg.date_from
319 AND ( bg.date_to IS NULL
320 OR bg.date_to >= v_effective_date)
321 AND info.group_per_in_ler_id = pil.per_in_ler_id
322 AND pil.ws_mgr_id = per.person_id(+)
323 AND v_effective_date BETWEEN per.effective_start_date(+) AND per.effective_end_date(+);
324
325 CURSOR c_emp_num_and_emp_name(v_group_per_in_ler_id IN NUMBER)
326 IS
327 SELECT nvl(per.custom_name,per.full_name) full_name
328 , per.employee_number
329 , per.assignment_id
330 , per.business_group_id
331 , per.legislation_code
332 FROM ben_cwb_person_info per
333 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
334
335 CURSOR c_prior_assignment(v_group_per_in_ler_id IN NUMBER)
336 IS
337 SELECT nvl(per.custom_name,per.full_name) full_name
338 , per.employee_number
339 , per.assignment_id
340 , per.business_group_id
341 , per.legislation_code
342 , per.job_id
343 , job.name job
344 , per.position_id
345 , pos.name position
346 , per.grade_id
347 , grades.name grade
348 , per.people_group_id
349 , ppl_groups.group_name
350 , per.ass_attribute1
351 , per.ass_attribute2
352 , per.ass_attribute3
353 , per.ass_attribute4
354 , per.ass_attribute5
355 , per.ass_attribute6
356 , per.ass_attribute7
357 , per.ass_attribute8
358 , per.ass_attribute9
359 , per.ass_attribute10
360 , per.ass_attribute11
361 , per.ass_attribute12
362 , per.ass_attribute13
363 , per.ass_attribute14
364 , per.ass_attribute15
365 , per.ass_attribute16
366 , per.ass_attribute17
367 , per.ass_attribute18
368 , per.ass_attribute19
369 , per.ass_attribute20
370 , per.ass_attribute21
371 , per.ass_attribute22
372 , per.ass_attribute23
373 , per.ass_attribute24
374 , per.ass_attribute25
375 , per.ass_attribute26
376 , per.ass_attribute27
377 , per.ass_attribute28
378 , per.ass_attribute29
379 , per.ass_attribute30
380 FROM ben_cwb_person_info per
381 , per_jobs_tl job
382 , hr_all_positions_f_tl pos
383 , per_grades_tl grades
384 , pay_people_groups ppl_groups
385 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
386 AND job.job_id(+) = per.job_id
387 AND job.language(+) = userenv('LANG')
388 AND pos.position_id(+) = per.position_id
389 AND pos.language(+) = userenv('LANG')
390 AND grades.grade_id(+) = per.grade_id
391 AND grades.language(+) = userenv('LANG')
392 AND ppl_groups.people_group_id(+) = per.people_group_id;
393
394 CURSOR c_batch_proc_info (v_benefit_action_id IN NUMBER)
395 IS
396 SELECT info.batch_proc_id
397 , info.object_version_number
398 FROM ben_batch_proc_info info
399 WHERE info.benefit_action_id = v_benefit_action_id;
400
401 CURSOR c_error_per_summary (v_benefit_action_id IN NUMBER)
402 IS
403 SELECT COUNT (*) amount
404 FROM ben_cwb_rpt_detail
405 WHERE person_rate_id = -9999
406 AND status_cd = 'E'
407 AND benefit_action_id = v_benefit_action_id;
408
409 CURSOR c_succ_per_summary (v_benefit_action_id IN NUMBER)
410 IS
411 SELECT COUNT (*) amount
412 FROM ben_cwb_rpt_detail
413 WHERE person_rate_id = -9999
414 AND status_cd IN ('WC', 'SC', 'W')
415 AND benefit_action_id = v_benefit_action_id;
416
417 CURSOR c_lf_evt_open_summary (v_benefit_action_id IN NUMBER)
418 IS
419 SELECT COUNT (*) amount
420 FROM ben_cwb_rpt_detail
421 WHERE person_rate_id = -9999
422 AND lf_evt_closed_flag = 'N'
423 AND benefit_action_id = v_benefit_action_id;
424
425 CURSOR c_lf_evt_close_summary (v_benefit_action_id IN NUMBER)
426 IS
427 SELECT COUNT (*) amount
428 FROM ben_cwb_rpt_detail
429 WHERE person_rate_id = -9999
430 AND lf_evt_closed_flag = 'Y'
431 AND benefit_action_id = v_benefit_action_id;
432
433 CURSOR c_placeholder_selection (
434 v_pl_id IN NUMBER
435 , v_lf_evt_orcd_date IN DATE
436 , v_person_id IN NUMBER
437 , v_manager_id IN NUMBER
438 , v_business_group_id IN NUMBER
439 , v_effective_date IN DATE
440 )
441 IS
442 SELECT pil.person_id
443 , (pil.per_in_ler_id) per_in_ler_id
444 , (nvl(per.custom_name,per.full_name)) full_name
445 , bg.NAME
446 , per.legislation_code
447 , per.business_group_id
448 FROM
449 ben_per_in_ler pil,
450 ben_per_in_ler mgr_pil,
451 ben_cwb_person_info per,
452 ben_cwb_group_hrchy hrchy,
453 per_business_groups_perf bg
454 where pil.per_in_ler_stat_cd = 'STRTD'
455 AND pil.group_pl_id = per.group_pl_id
456 AND pil.lf_evt_ocrd_dt = per.lf_evt_ocrd_dt
457 AND per.group_per_in_ler_id = pil.per_in_ler_id
458 AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
459 AND hrchy.mgr_per_in_ler_id = mgr_pil.per_in_ler_id
460 AND ( v_person_id IS NULL
461 OR pil.person_id = v_person_id)
462 AND ( ( v_manager_id IS NULL
463 AND hrchy.lvl_num = (SELECT MAX (lvl_num)
464 FROM ben_cwb_group_hrchy
465 WHERE emp_per_in_ler_id = hrchy.emp_per_in_ler_id)
466 )
467 OR ( mgr_pil.person_id = v_manager_id
468 AND hrchy.lvl_num > 0)
469 )
470 and not exists(
471 select null from
472 ben_cwb_person_rates
473 where group_per_in_ler_id = pil.per_in_ler_id
474 )
475 and (v_business_group_id is null or
476 per.business_group_id = v_business_group_id)
477 AND bg.business_group_id = per.business_group_id
478 AND v_effective_date >= bg.date_from
479 AND ( bg.date_to IS NULL
480 OR bg.date_to >= v_effective_date)
481 and per.group_pl_id = v_pl_id
482 and per.lf_evt_ocrd_dt = v_lf_evt_orcd_date
483 ;
484
485 CURSOR c_person_selection (
486 v_pl_id IN NUMBER
487 , v_lf_evt_orcd_date IN DATE
488 , v_person_id IN NUMBER
489 , v_manager_id IN NUMBER
490 , v_business_group_id IN NUMBER
491 , v_effective_date IN DATE
492 )
493 IS
494 SELECT pil.person_id
495 , max(pil.per_in_ler_id) per_in_ler_id
496 , max(nvl(per.custom_name,per.full_name)) full_name
497 , max(bg.NAME) NAME
498 , per.business_group_id
499 FROM ben_per_in_ler pil
500 , ben_per_in_ler mgr_pil
501 , ben_cwb_group_hrchy hrchy
502 , per_business_groups_perf bg
503 , ben_cwb_person_info per
504 , ben_cwb_person_rates rates
505 , ben_cwb_pl_dsgn dsgn
506 WHERE pil.per_in_ler_stat_cd = 'STRTD'
507 AND pil.group_pl_id = v_pl_id
508 AND pil.lf_evt_ocrd_dt = v_lf_evt_orcd_date
509 AND per.group_per_in_ler_id = pil.per_in_ler_id
510 AND ( v_person_id IS NULL
511 OR pil.person_id = v_person_id)
512 AND ( v_business_group_id IS NULL
513 OR per.business_group_id = v_business_group_id)
514 AND per.business_group_id = bg.business_group_id
515 AND v_effective_date >= bg.date_from
516 AND ( bg.date_to IS NULL
517 OR bg.date_to >= v_effective_date)
518 AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
519 AND hrchy.mgr_per_in_ler_id = mgr_pil.per_in_ler_id
520 AND ( ( v_manager_id IS NULL
521 AND hrchy.lvl_num = (SELECT MAX (lvl_num)
522 FROM ben_cwb_group_hrchy
523 WHERE emp_per_in_ler_id = hrchy.emp_per_in_ler_id)
524 )
525 OR ( mgr_pil.person_id = v_manager_id
526 AND hrchy.lvl_num > 0)
527 )
528 AND rates.group_per_in_ler_id = pil.per_in_ler_id
529 AND rates.pl_id = dsgn.pl_id
530 AND rates.oipl_id = dsgn.oipl_id
531 --AND rates.elig_flag = 'Y'
532 AND rates.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
533 AND dsgn.oipl_id=-1
534 AND nvl(dsgn.do_not_process_flag,'N') <> 'Y'
535 GROUP BY pil.person_id, per.business_group_id
536 ORDER BY full_name;
537
538 CURSOR c_check_eligibility (v_group_per_in_ler_id IN NUMBER)
539 IS
540 select null
541 from ben_cwb_person_rates
542 where group_per_in_ler_id = v_group_per_in_ler_id
543 and oipl_id = -1
544 and elig_flag = 'Y';
545
546 CURSOR c_per_in_ler_ids (
547 v_group_pl_id IN NUMBER
548 , v_employee_in_bg IN NUMBER
549 , v_person_id IN NUMBER
550 , v_lf_evt_ocrd_dt IN DATE
551 )
552 IS
553 SELECT pil.per_in_ler_id
554 , pil.per_in_ler_stat_cd
555 , pil.object_version_number
556 FROM ben_per_in_ler pil
557 WHERE pil.group_pl_id = v_group_pl_id
558 AND pil.person_id = v_person_id
559 AND pil.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
560 AND pil.per_in_ler_stat_cd = 'STRTD'
561 AND ( v_employee_in_bg IS NULL
562 OR pil.business_group_id = v_employee_in_bg);
563
564 CURSOR c_range_for_thread (v_benefit_action_id IN NUMBER)
565 IS
566 SELECT ran.range_id
567 , ran.starting_person_action_id
568 , ran.ending_person_action_id
569 FROM ben_batch_ranges ran
570 WHERE ran.range_status_cd = 'U'
571 AND ran.benefit_action_id = v_benefit_action_id
572 AND ROWNUM < 2
573 FOR UPDATE OF ran.range_status_cd;
574
575 CURSOR c_person_for_thread (
576 v_benefit_action_id IN NUMBER
577 , v_start_person_action_id IN NUMBER
578 , v_end_person_action_id IN NUMBER
579 )
580 IS
581 SELECT ben.person_id
582 , ben.person_action_id
583 , ben.object_version_number
584 , ben.ler_id
585 , ben.non_person_cd
586 FROM ben_person_actions ben
587 WHERE ben.benefit_action_id = v_benefit_action_id
588 AND ben.action_status_cd <> 'P'
589 AND ben.person_action_id BETWEEN v_start_person_action_id AND v_end_person_action_id
590 ORDER BY ben.person_action_id;
591
592 CURSOR c_parameter (v_benefit_action_id IN NUMBER)
593 IS
594 SELECT ben.*
595 FROM ben_benefit_actions ben
596 WHERE ben.benefit_action_id = v_benefit_action_id;
597
598 CURSOR c_actual_termination_date (v_person_id IN NUMBER)
599 IS
600 SELECT actual_termination_date
601 FROM per_periods_of_service
602 WHERE person_id = v_person_id
603 AND date_start = (SELECT MAX (date_start)
604 FROM per_periods_of_service
605 WHERE person_id = v_person_id
606 GROUP BY person_id);
607
608 CURSOR c_performance_promotion (v_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
609 IS
610 SELECT dsgn.perf_revw_strt_dt
611 , nvl(dsgn.ovr_perf_revw_strt_dt, dsgn.perf_revw_strt_dt)
612 , dsgn.asg_updt_eff_date
613 , dsgn.emp_interview_typ_cd
614 FROM ben_cwb_pl_dsgn dsgn
615 WHERE dsgn.pl_id = v_pl_id
616 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
617 AND dsgn.oipl_id = -1;
618
619 CURSOR c_component_reason (v_pl_id IN NUMBER, v_effective_date IN DATE)
620 IS
621 SELECT COUNT (*)
622 FROM ben_oipl_f oipl
623 , ben_opt_f opt
624 WHERE oipl.pl_id = v_pl_id
625 AND oipl.opt_id = opt.opt_id
626 and opt.component_reason is not null
627 AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
628 AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date;
629
630 CURSOR c_task_type (v_group_per_in_ler_id IN NUMBER)
631 IS
632 SELECT dsgn.ws_sub_acty_typ_cd
633 FROM ben_cwb_person_rates rt
634 , ben_cwb_pl_dsgn dsgn
635 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
636 AND rt.pl_id = dsgn.pl_id
637 AND rt.oipl_id = dsgn.oipl_id
638 AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt;
639
640 CURSOR c_input_value_precision(
641 v_assignment_id NUMBER
642 , v_effective_date DATE
643 )
644 IS
645 SELECT decode(piv.uom,NULL,2,'M',nvl(curr.PRECISION,2),5) PRECISION
646 FROM per_all_assignments_f asg,
647 per_pay_bases ppb,
648 pay_input_values_f piv,
649 pay_element_types_f pet,
650 fnd_currencies curr
651 WHERE asg.assignment_id = v_assignment_id
652 AND v_effective_date BETWEEN asg.effective_start_date
653 AND asg.effective_end_date
654 AND asg.pay_basis_id = ppb.pay_basis_id
655 AND ppb.input_value_id = piv.input_value_id
656 AND v_effective_date BETWEEN piv.effective_start_date
657 AND piv.effective_end_date
658 AND piv.element_type_id = pet.element_type_id
659 AND v_effective_date BETWEEN pet.effective_start_date
660 AND pet.effective_end_date
661 AND pet.input_currency_code = curr.currency_code;
662
663 CURSOR c_sal_comp_rates_tot (
664 v_group_per_in_ler_id IN NUMBER
665 , v_group_pl_id IN NUMBER
666 , v_lf_evt_orcd_dt IN DATE
667 ,v_effective_date in date
668 )
669 IS
670 SELECT SUM (rt.ws_val)
671 FROM ben_cwb_person_rates rt
672 ,ben_oipl_f oipl
673 ,ben_opt_f opt
674 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
675 AND rt.group_pl_id = v_group_pl_id
676 AND rt.oipl_id <> -1
677 AND rt.lf_evt_ocrd_dt = v_lf_evt_orcd_dt
678 AND rt.ws_val <> 0
679 AND oipl.oipl_id = rt.oipl_id
680 AND oipl.opt_id = opt.opt_id
681 AND opt.component_reason is not null
682 AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
683 AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date
684 AND rt.elig_flag = 'Y';
685
686 CURSOR c_salary_effective_date(
687 v_group_per_in_ler_id IN NUMBER,
688 v_rule_based IN VARCHAR2
689 ) IS
690 SELECT DECODE(v_rule_based,'Y',
691 min(WS_RT_START_DATE),
692 min(OVRID_RT_STRT_DT)) effective_date
693 FROM ben_cwb_person_rates rt
694 , ben_oipl_f oipl
695 , ben_cwb_pl_dsgn dsgn
696 , ben_opt_f opt
697 , ben_cwb_person_info info
698 , ben_per_in_ler pil
699 , ben_cwb_xchg xchg
700 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
701 AND rt.pl_id = dsgn.pl_id
702 AND rt.oipl_id = dsgn.oipl_id
703 AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
704 AND rt.group_per_in_ler_id = info.group_per_in_ler_id
705 AND rt.group_per_in_ler_id = pil.per_in_ler_id
706 AND oipl.oipl_id = rt.oipl_id
707 AND oipl.opt_id = opt.opt_id
708 AND opt.component_reason is not null
709 AND OVRID_RT_STRT_DT BETWEEN opt.effective_start_date AND opt.effective_end_date
710 AND OVRID_RT_STRT_DT BETWEEN oipl.effective_start_date AND oipl.effective_end_date
711 and xchg.group_pl_id = rt.group_pl_id
712 and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
713 and xchg.currency = rt.currency
714 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');
715
716 CURSOR c_sal_comp_rates (
717 v_group_per_in_ler_id IN NUMBER
718 , v_group_pl_id IN NUMBER
719 , v_lf_evt_orcd_dt IN DATE
720 , v_effective_date IN DATE
721 )
722 IS
723 SELECT nvl(rt.ws_val,0) ws_val
724 , rt.person_rate_id
725 , opt.component_reason
726 , dsgn.salary_change_reason
727 , dsgn.pl_id
728 , dsgn.oipl_id
729 , dsgn.group_pl_id
730 , dsgn.group_oipl_id
731 , dsgn.ws_nnmntry_uom units
732 , dsgn.ws_sub_acty_typ_cd
733 , pil.ws_mgr_id
734 , info.full_name
735 , info.employee_number
736 , info.business_group_id
737 , rt.elig_sal_val
738 , xchg.xchg_rate
739 , rt.elig_flag
740 , rt.currency
741 FROM ben_cwb_person_rates rt
742 , ben_oipl_f oipl
743 , ben_cwb_pl_dsgn dsgn
744 , ben_opt_f opt
745 , ben_cwb_person_info info
746 , ben_per_in_ler pil
747 , ben_cwb_xchg xchg
748 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
749 AND rt.group_pl_id = v_group_pl_id
750 --AND rt.oipl_id <> -1
751 --AND rt.elig_flag = 'Y' (for logging)
752 AND rt.lf_evt_ocrd_dt = v_lf_evt_orcd_dt
753 --AND nvl(rt.ws_val,0) <> 0
754 AND rt.pl_id = dsgn.pl_id
755 AND rt.oipl_id = dsgn.oipl_id
756 AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
757 AND rt.group_per_in_ler_id = info.group_per_in_ler_id
758 AND rt.group_per_in_ler_id = pil.per_in_ler_id
759 AND oipl.oipl_id = rt.oipl_id
760 AND oipl.opt_id = opt.opt_id
761 AND opt.component_reason is not null
762 AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
763 AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date
764 and xchg.group_pl_id = rt.group_pl_id
765 and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
766 and xchg.currency = rt.currency
767 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');
768
769
770 CURSOR c_non_sal_comp_rates (v_group_per_in_ler_id IN NUMBER, v_effective_date IN DATE)
771 IS
772 SELECT rt.ws_val
773 , rt.person_rate_id
774 , rt.pl_id
775 , rt.oipl_id
776 , rt.object_version_number
777 , dsgn.ws_sub_acty_typ_cd
778 , dsgn.ws_abr_id
779 , dsgn.salary_change_reason
780 , dsgn.ws_nnmntry_uom units
781 , dsgn.acty_ref_perd_cd
782 , dsgn.business_group_id
783 , dsgn.group_pl_id
784 , dsgn.group_oipl_id
785 , pil.ws_mgr_id
786 , info.full_name
787 , info.employee_number
788 , info.assignment_id
789 , opt.component_reason
790 , info.base_salary_currency
791 , dsgn.uom_precision
792 , info.base_salary
793 , rt.elig_sal_val
794 , initcap(info.base_salary_frequency) base_salary_frequency
795 , info.pay_annulization_factor
796 , dsgn.pl_annulization_factor
797 , xchg.xchg_rate
798 , rt.elig_flag
799 , info.fte_factor
800 , rt.currency
801 FROM ben_cwb_person_rates rt
802 , ben_cwb_pl_dsgn dsgn
803 , ben_cwb_person_info info
804 , ben_per_in_ler pil
805 , ben_oipl_f oipl
806 , ben_opt_f opt
807 , ben_cwb_xchg xchg
808 WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
809 AND rt.pl_id = dsgn.pl_id
810 AND rt.oipl_id = dsgn.oipl_id
811 --AND rt.elig_flag = 'Y' (for logging)
812 AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
813 AND rt.group_per_in_ler_id = info.group_per_in_ler_id
814 AND rt.group_per_in_ler_id = pil.per_in_ler_id
815 AND rt.oipl_id = oipl.oipl_id (+)
816 AND oipl.opt_id = opt.opt_id (+)
817 AND v_effective_date BETWEEN opt.effective_start_date (+) AND opt.effective_end_date (+)
818 AND v_effective_date BETWEEN oipl.effective_start_date (+)AND oipl.effective_end_date (+)
819 and xchg.group_pl_id = rt.group_pl_id
820 and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
821 and xchg.currency = rt.currency
822 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');
823
824 CURSOR c_ranking_info (v_group_per_in_ler_id IN NUMBER)
825 IS
826 SELECT xtra_info.aei_information1
827 , xtra_info.aei_information2
828 , xtra_info.aei_information4
829 , xtra_info.assignment_id
830 , xtra_info.object_version_number
831 , xtra_info.assignment_extra_info_id
832 FROM per_assignment_extra_info xtra_info
833 , ben_cwb_person_info per
834 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
835 AND xtra_info.assignment_id = per.assignment_id
836 AND xtra_info.information_type = 'CWBRANK'
837 AND xtra_info.aei_information1 IS NOT NULL
838 AND xtra_info.aei_information3 IS NULL;
839
840 CURSOR c_ranking_info_date ( v_group_per_in_ler_id IN NUMBER
841 ,v_eff_dt IN DATE
842 ,v_ranked_by IN VARCHAR2)
843
844 IS
845 SELECT xtra_info.aei_information1
846 , xtra_info.aei_information2
847 , xtra_info.aei_information4
848 , xtra_info.assignment_id
849 , xtra_info.object_version_number
850 FROM per_assignment_extra_info xtra_info
851 , ben_cwb_person_info per
852 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
853 AND xtra_info.assignment_id = per.assignment_id
854 AND xtra_info.information_type = 'CWBRANK'
855 AND xtra_info.aei_information5 = fnd_date.date_to_canonical(v_eff_dt)
856 AND xtra_info.aei_information2 = v_ranked_by;
857
858
859 CURSOR c_prev_pay_proposal (v_group_per_in_ler_id IN NUMBER, v_effective_date IN DATE)
860 IS
861 SELECT asg.assignment_id
862 , asg.pay_basis_id
863 , ppp.proposed_salary_n
864 , ppp.object_version_number
865 FROM per_all_assignments_f asg
866 , per_pay_bases ppb
867 , per_pay_proposals ppp
868 , ben_cwb_person_info per
869 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
870 AND asg.assignment_id = per.assignment_id
871 AND v_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
872 AND ppb.pay_basis_id = asg.pay_basis_id
873 AND ppp.assignment_id = asg.assignment_id
874 AND ppp.approved = 'Y'
875 AND ppp.change_date =
876 (SELECT MAX (ppp1.change_date)
877 FROM per_pay_proposals ppp1
878 WHERE ppp1.assignment_id = asg.assignment_id
879 AND ppp1.approved = 'Y'
880 AND change_date < v_effective_date);
881
882 CURSOR c_future_pay_proposal (v_group_per_in_ler_id IN NUMBER, v_effective_date IN DATE)
883 IS
884 SELECT ppp.proposed_salary_n
885 FROM per_pay_proposals ppp
886 , ben_cwb_person_info per
887 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
888 AND ppp.assignment_id = per.assignment_id
889 AND ppp.change_date > v_effective_date;
890
891 CURSOR c_element_entry (
892 v_pay_basis_id IN NUMBER
893 , v_assignmnet_id IN NUMBER
894 , v_effective_date IN DATE
895 )
896 IS
897 SELECT ele.element_entry_id
898 ,ele.element_type_id
899 FROM per_pay_bases bas
900 , pay_element_entries_f ele
901 , pay_element_entry_values_f entval
902 WHERE bas.pay_basis_id = v_pay_basis_id
903 AND entval.input_value_id = bas.input_value_id
904 AND v_effective_date BETWEEN entval.effective_start_date AND entval.effective_end_date
905 AND ele.assignment_id = v_assignmnet_id
906 AND v_effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date
907 AND ele.element_entry_id = entval.element_entry_id;
908
909 CURSOR c_tot_chg_amt_for_proposal (v_pay_proposal_id IN NUMBER)
910 IS
911 SELECT SUM (comp.change_amount_n) tamt
912 FROM per_pay_proposal_components comp
913 WHERE comp.pay_proposal_id = v_pay_proposal_id;
914
915 CURSOR c_person_info (v_group_per_in_ler_id IN NUMBER)
916 IS
917 SELECT per.business_group_id
918 , per.base_salary
919 , per.base_salary_currency
920 , initcap(base_salary_frequency) base_salary_frequency
921 , pay_annulization_factor
922 , fte_factor
923 FROM ben_cwb_person_info per
924 WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
925
926 CURSOR c_group_plan_name (v_group_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
927 IS
928 SELECT dsgn.NAME
929 , dsgn.group_pl_id
930 FROM ben_cwb_pl_dsgn dsgn
931 WHERE dsgn.pl_id = v_group_pl_id
932 AND dsgn.pl_id = dsgn.group_pl_id
933 AND dsgn.group_oipl_id = -1
934 AND dsgn.oipl_id = dsgn.group_oipl_id
935 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt;
936
937 CURSOR c_group_option_name (v_group_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
938 IS
939 SELECT dsgn.NAME
940 , dsgn.group_oipl_id
941 FROM ben_cwb_pl_dsgn dsgn
942 WHERE dsgn.pl_id = v_group_pl_id
943 AND dsgn.pl_id = dsgn.group_pl_id
944 AND dsgn.oipl_id = dsgn.group_oipl_id
945 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
946 AND dsgn.oipl_id <> -1;
947
948 CURSOR c_plan_name (v_group_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
949 IS
950 SELECT dsgn.NAME
951 , dsgn.pl_id
952 FROM ben_cwb_pl_dsgn dsgn
953 WHERE dsgn.group_pl_id = v_group_pl_id
954 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
955 AND dsgn.oipl_id = -1
956 AND dsgn.pl_id <> dsgn.group_pl_id;
957
958 CURSOR c_option_name (v_group_pl_id IN NUMBER, v_lf_evt_ocrd_dt IN DATE)
959 IS
960 SELECT dsgn.NAME
961 , dsgn.oipl_id
962 FROM ben_cwb_pl_dsgn dsgn
963 WHERE dsgn.group_pl_id = v_group_pl_id
964 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
965 AND dsgn.pl_id <> dsgn.group_pl_id
966 AND dsgn.oipl_id <> -1;
967
968 CURSOR c_actions (v_benefit_action_id IN NUMBER)
969 IS
970 SELECT COUNT (*) amount
971 , action_status_cd
972 FROM ben_person_actions act
973 WHERE act.benefit_action_id = v_benefit_action_id
974 AND act.action_status_cd IN ('P', 'E', 'U')
975 GROUP BY action_status_cd;
976
977 CURSOR c_pils_for_access( v_group_pl_id IN NUMBER
978 ,v_lf_evt_ocrd_dt IN DATE
979 )
980 IS
981 SELECT pil.per_in_ler_id
982 ,nvl(info.custom_name,info.full_name) full_name
983 FROM ben_per_in_ler pil
984 ,ben_cwb_person_groups pgroup
985 ,ben_cwb_person_info info
986 WHERE pil.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
987 AND pil.group_pl_id = v_group_pl_id
988 AND pgroup.group_per_in_ler_id = pil.per_in_ler_id
989 AND pgroup.group_oipl_id = -1
990 AND ( nvl(pgroup.access_cd,'UP') = 'UP' OR approval_cd = 'AP' )
991 AND info.group_per_in_ler_id = pil.per_in_ler_id
992 AND NOT EXISTS (
993 SELECT NULL
994 FROM ben_cwb_group_hrchy h, ben_per_in_ler p
995 ,ben_cwb_person_rates r
996 WHERE h.mgr_per_in_ler_id = pil.per_in_ler_id
997 AND h.lvl_num > 0
998 AND p.per_in_ler_id = h.emp_per_in_ler_id
999 AND p.lf_evt_ocrd_dt = pil.lf_evt_ocrd_dt
1000 AND p.group_pl_id = pil.group_pl_id
1001 AND p.per_in_ler_stat_cd = 'STRTD'
1002 AND r.group_per_in_ler_id = h.emp_per_in_ler_id
1003 AND r.oipl_id = -1
1004 AND r.elig_flag = 'Y'
1005 )
1006 AND NOT EXISTS (
1007 SELECT NULL
1008 FROM ben_per_in_ler p
1009 WHERE p.per_in_ler_id = pil.per_in_ler_id
1010 AND p.lf_evt_ocrd_dt = pil.lf_evt_ocrd_dt
1011 AND p.group_pl_id = pil.group_pl_id
1012 AND p.per_in_ler_stat_cd = 'STRTD'
1013 AND NOT EXISTS (
1014 SELECT NULL
1015 FROM ben_cwb_group_hrchy h
1016 WHERE h.mgr_per_in_ler_id = p.per_in_ler_id
1017 )
1018 );
1019
1020 CURSOR c_emp_pils_still_open(v_mgr_per_in_ler_id IN NUMBER)
1021 IS
1022 SELECT emp_per_in_ler_id
1023 FROM ben_per_in_ler pil
1024 ,ben_cwb_group_hrchy hrchy
1025 WHERE hrchy.mgr_per_in_ler_id = v_mgr_per_in_ler_id
1026 AND hrchy.lvl_num > 0
1027 AND pil.per_in_ler_id = hrchy.emp_per_in_ler_id
1028 AND pil.per_in_ler_stat_cd = 'STRTD'
1029 AND rownum = 1;
1030
1031 CURSOR c_sal_factors(v_group_pl_id IN NUMBER,
1032 v_lf_evt_ocrd_dt IN DATE,
1033 v_group_per_in_ler_id IN NUMBER
1034 )
1035 IS
1036 SELECT dsgn.pl_annulization_factor,
1037 dsgn.uom_precision,
1038 info.pay_annulization_factor,
1039 dsgn.salary_change_reason
1040 FROM ben_cwb_pl_dsgn dsgn,
1041 ben_cwb_person_info info
1042 WHERE dsgn.group_pl_id = v_group_pl_id
1043 AND dsgn.group_pl_id = dsgn.pl_id
1044 AND dsgn.oipl_id = -1
1045 AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
1046 AND info.group_per_in_ler_id = v_group_per_in_ler_id;
1047
1048 CURSOR c_element_input_currency(v_element_type_id IN NUMBER,
1049 v_effective_date IN DATE
1050 )
1051 IS
1052 SELECT input_currency_code
1053 FROM pay_element_types_f
1054 WHERE element_type_id = v_element_type_id
1055 AND v_effective_date BETWEEN effective_start_date AND effective_end_date;
1056
1057 CURSOR c_element_input_value_name(v_input_value_id IN NUMBER,
1058 v_element_type_id IN NUMBER,
1059 v_effective_date IN DATE
1060 )
1061 IS
1062 select pet.element_name||': '||piv.name
1063 , processing_type
1064 , input_currency_code
1065 from pay_input_values_f piv,
1066 pay_element_types_f pet
1067 where piv.input_value_id = v_input_value_id
1068 and piv.element_type_id = v_element_type_id
1069 and piv.element_type_id = pet.element_type_id
1070 and v_effective_date between piv.effective_start_date and piv.effective_end_date
1071 and v_effective_date between pet.effective_start_date and pet.effective_end_date;
1072
1073 CURSOR c_posted_element(v_assignment_id IN NUMBER
1074 ,v_element_type_id IN NUMBER
1075 ,v_input_value_id IN NUMBER
1076 ,v_effective_date IN DATE)
1077 IS
1078 select eev.screen_entry_value
1079 from pay_element_entries_f ee,
1080 pay_element_entry_values_f eev
1081 where ee.assignment_id = v_assignment_id
1082 and ee.element_type_id = v_element_type_id
1083 and v_effective_date between ee.effective_start_date and ee.effective_end_date
1084 and eev.element_entry_id = ee.element_entry_id
1085 and eev.input_value_id = v_input_value_id
1086 and eev.effective_start_date = ee.effective_start_date
1087 and eev.effective_end_date = ee.effective_end_date
1088 and eev.screen_entry_value is not null
1089 order by ee.effective_start_date;
1090
1091 CURSOR c_posted_salary(v_pay_proposal_id IN NUMBER)
1092 IS
1093 select proposed_salary_n
1094 from per_pay_proposals
1095 where pay_proposal_id = v_pay_proposal_id;
1096
1097 CURSOR c_posted_rating(v_person_id IN NUMBER,
1098 v_effective_date IN DATE)
1099 IS
1100 select perf.performance_rating
1101 from per_performance_reviews perf
1102 where perf.person_id = v_person_id
1103 and perf.review_date = v_effective_date;
1104
1105 CURSOR c_posted_promotions(v_assignment_id IN NUMBER,
1106 v_effective_date IN DATE)
1107 IS
1108 select asgn.job_id
1109 , job.name job
1110 , asgn.position_id
1111 , pos.name position
1112 , asgn.grade_id
1113 , grade.name grade
1114 , asgn.people_group_id
1115 , people_group.group_name
1116 , asgn.ass_attribute1
1117 , asgn.ass_attribute2
1118 , asgn.ass_attribute3
1119 , asgn.ass_attribute4
1120 , asgn.ass_attribute5
1121 , asgn.ass_attribute6
1122 , asgn.ass_attribute7
1123 , asgn.ass_attribute8
1124 , asgn.ass_attribute9
1125 , asgn.ass_attribute10
1126 , asgn.ass_attribute11
1127 , asgn.ass_attribute12
1128 , asgn.ass_attribute13
1129 , asgn.ass_attribute14
1130 , asgn.ass_attribute15
1131 , asgn.ass_attribute16
1132 , asgn.ass_attribute17
1133 , asgn.ass_attribute18
1134 , asgn.ass_attribute19
1135 , asgn.ass_attribute20
1136 , asgn.ass_attribute21
1137 , asgn.ass_attribute22
1138 , asgn.ass_attribute23
1139 , asgn.ass_attribute24
1140 , asgn.ass_attribute25
1141 , asgn.ass_attribute26
1142 , asgn.ass_attribute27
1143 , asgn.ass_attribute28
1144 , asgn.ass_attribute29
1145 , asgn.ass_attribute30
1146 from per_all_assignments_f asgn
1147 , per_jobs_tl job
1148 , hr_all_positions_f_tl pos
1149 , per_grades_tl grade
1150 , pay_people_groups people_group
1151 where assignment_id = v_assignment_id
1152 and v_effective_date between effective_start_date and effective_end_date
1153 and job.job_id(+) = asgn.job_id
1154 and job.language(+) = userenv('LANG')
1155 and pos.position_id(+) = asgn.position_id
1156 and pos.language(+) = userenv('LANG')
1157 and grade.grade_id(+) = asgn.grade_id
1158 and grade.language(+) = userenv('LANG')
1159 and people_group.people_group_id(+) = asgn.people_group_id;
1160
1161 CURSOR c_proposed_promotions(v_transaction_id IN NUMBER,
1162 v_transaction_type IN VARCHAR2)
1163 IS
1164 select job.name job
1165 , pos.name position
1166 , grade.name grade
1167 , people_group.group_name
1168 from ben_transaction asgn
1169 , per_jobs_tl job
1170 , hr_all_positions_f_tl pos
1171 , per_grades_tl grade
1172 , pay_people_groups people_group
1173 where asgn.transaction_id = v_transaction_id
1174 and asgn.transaction_type = v_transaction_type
1175 and job.job_id(+) = asgn.attribute5
1176 and job.language(+) = userenv('LANG')
1177 and pos.position_id(+) = asgn.attribute6
1178 and pos.language(+) = userenv('LANG')
1179 and grade.grade_id(+) = asgn.attribute7
1180 and grade.language(+) = userenv('LANG')
1181 and people_group.people_group_id(+) = asgn.attribute8;
1182
1183 CURSOR c_overrides_perf_prom(v_group_per_in_ler_id IN NUMBER,
1184 v_lf_evt_ocrd_dt IN DATE)
1185 IS
1186 SELECT trans.transaction_id,
1187 trans.attribute1,
1188 trans.attribute2
1189 FROM ben_transaction trans,
1190 ben_cwb_pl_dsgn dsgn
1191 WHERE trans.transaction_id IN
1192 (SELECT DISTINCT pl_id
1193 FROM ben_cwb_person_rates rates
1194 WHERE group_per_in_ler_id = v_group_per_in_ler_id)
1195 AND trans.transaction_type = 'CWBPPOVDT' || to_char(v_lf_evt_ocrd_dt,'yyyy/mm/dd');
1196
1197 CURSOR c_slaves(v_request_id IN NUMBER)
1198 IS
1199 Select null
1200 From fnd_concurrent_requests fnd
1201 Where request_id = v_request_id
1202 and status_code = 'E';
1203
1204 CURSOR c_get_ws_rate_start_dt(
1205 v_group_per_in_ler_id IN NUMBER,
1206 v_group_pl_id IN NUMBER,
1207 v_pl_id IN NUMBER,
1208 v_oipl_id IN NUMBER,
1209 v_group_oipl_id IN NUMBER,
1210 v_lf_evt_ocrd_dt IN DATE)
1211 IS
1212 SELECT WS_RT_START_DATE
1213 FROM BEN_CWB_PERSON_RATES
1214 WHERE GROUP_PER_IN_LER_ID = v_group_per_in_ler_id
1215 AND PL_ID = v_pl_id
1216 AND OIPL_ID = v_oipl_id
1217 AND LF_EVT_OCRD_DT = v_lf_evt_ocrd_dt
1218 AND GROUP_PL_ID = v_group_pl_id
1219 AND GROUP_OIPL_ID = v_group_oipl_id
1220 ;
1221 --
1222 -- ============================================================================
1223 -- <<write>>
1224 -- ============================================================================
1225 --
1226
1227 PROCEDURE WRITE (p_string IN VARCHAR2)
1228 IS
1229 BEGIN
1230 ben_batch_utils.WRITE (p_string);
1231 END;
1232
1233 --
1234 -- ============================================================================
1235 -- <<write_s>>
1236 -- ============================================================================
1237 --
1238
1239 PROCEDURE write_s (p_string IN VARCHAR2)
1240 IS
1241 BEGIN
1242 IF (g_debug_level = 'S')
1243 THEN
1244 WRITE (p_string);
1245 END IF;
1246 END;
1247
1248 --
1249 -- ============================================================================
1250 -- <<write_m>>
1251 -- ============================================================================
1252 --
1253
1254 PROCEDURE write_m (p_string IN VARCHAR2)
1255 IS
1256 BEGIN
1257 IF ( g_debug_level = 'M'
1258 OR g_debug_level = 'H'
1259 OR g_debug_level = 'S')
1260 THEN
1261 WRITE (p_string);
1262 END IF;
1263 END;
1264
1265 --
1266 -- ============================================================================
1267 -- <<write_h>>
1268 -- ============================================================================
1269 --
1270
1271 PROCEDURE write_h (p_string IN VARCHAR2)
1272 IS
1273 BEGIN
1274 IF ( g_debug_level = 'H'
1275 OR g_debug_level = 'S')
1276 THEN
1277 WRITE (p_string);
1278 END IF;
1279 END;
1280
1281
1282 --
1283 -- ============================================================================
1284 -- <<process_access>>
1285 -- ============================================================================
1286 --
1287
1288 PROCEDURE process_access( p_group_pl_id IN NUMBER
1289 ,p_lf_evt_ocrd_dt IN DATE
1290 ,p_validate IN VARCHAR2 DEFAULT 'N'
1291 )
1292 IS
1293 l_pils_for_access c_pils_for_access%ROWTYPE;
1294 l_grp_ovn c_grp_ovn%ROWTYPE;
1295 l_emp_pils_still_open c_emp_pils_still_open%ROWTYPE;
1296 l_emps_not_found BOOLEAN := FALSE;
1297 l_no_of_man_picked NUMBER := 0;
1298 l_no_of_man_access_changed NUMBER := 0;
1299 BEGIN
1300
1301 g_proc := 'process_access';
1302 g_actn := 'processing access routine...';
1303 write(g_actn);
1304 SAVEPOINT cwb_post_process_access;
1305
1306 IF c_pils_for_access%ISOPEN THEN
1307 close c_pils_for_access;
1308 END IF;
1309
1310 OPEN c_pils_for_access(p_group_pl_id,p_lf_evt_ocrd_dt );
1311 LOOP
1312
1313 FETCH c_pils_for_access INTO l_pils_for_access;
1314
1315 EXIT WHEN c_pils_for_access%NOTFOUND;
1316
1317 write_h('Processing access for the employee ' || l_pils_for_access.full_name);
1318 write_h('l_pils_for_access.per_in_ler_id is '||l_pils_for_access.per_in_ler_id);
1319 l_no_of_man_picked := l_no_of_man_picked + 1;
1320 /*
1321 l_emps_not_found := FALSE;
1322
1323 OPEN c_emp_pils_still_open(l_pils_for_access.mgr_per_in_ler_id);
1324 FETCH c_emp_pils_still_open INTO l_emp_pils_still_open;
1325 IF c_emp_pils_still_open%NOTFOUND THEN
1326 l_emps_not_found := TRUE;
1327 write_h('All Employees are processed for this manager and eligible for status update');
1328 ELSE
1329 write_h('Some Employees are not processed for this manager and not eligible for status update');
1330 END IF;
1331 CLOSE c_emp_pils_still_open;
1332
1333 IF l_emps_not_found THEN
1334 */
1335 OPEN c_grp_ovn (l_pils_for_access.per_in_ler_id, p_group_pl_id, -1);
1336
1337 FETCH c_grp_ovn
1338 INTO l_grp_ovn;
1339 CLOSE c_grp_ovn;
1340
1341 write_h('l_grp_ovn.access_cd is '||l_grp_ovn.access_cd);
1342
1343 IF l_grp_ovn.object_version_number IS NOT NULL AND
1344 (nvl(l_grp_ovn.access_cd,'UP') = 'UP' OR nvl(l_grp_ovn.approval_cd, 'AP') = 'AP')
1345 THEN
1346 write_m('Access and approval cd update for '||l_pils_for_access.full_name);
1347 BEGIN
1348 ben_cwb_person_groups_api.update_group_budget
1349 (p_group_per_in_ler_id => l_pils_for_access.per_in_ler_id
1350 , p_group_pl_id => p_group_pl_id
1351 , p_group_oipl_id => -1
1352 , p_access_cd => 'RO'
1353 , p_approval_date => sysdate
1354 , p_approval_cd => 'PR'
1355 , p_object_version_number => l_grp_ovn.object_version_number
1356 );
1357 l_no_of_man_access_changed := l_no_of_man_access_changed +1;
1358 EXCEPTION
1359 WHEN OTHERS THEN
1360 WRITE(SQLERRM);
1361 write('Access processing for '||l_pils_for_access.full_name ||'errored');
1362 END;
1363
1364 ELSE
1365 write_m('Access and approval cd not update for '||l_pils_for_access.full_name || ' as status is not UP');
1366 END IF;
1367 --END IF;
1368 write_h('--------------------------------------');
1369 END LOOP;
1370 CLOSE c_pils_for_access;
1371
1372 WRITE('Number of persons picked for access change is ' || l_no_of_man_picked);
1373 WRITE('Number of persons access changed is '||l_no_of_man_access_changed);
1374
1375 IF (p_validate = 'Y')
1376 THEN
1377 g_actn := 'Running in rollback mode, access processing rolled back...';
1378 WRITE (g_actn);
1379 ROLLBACK TO cwb_post_process_access;
1380 END IF;
1381
1382 END;
1383
1384
1385 --
1386 -- ============================================================================
1387 -- <<End_process>>
1388 -- ============================================================================
1389 --
1390 PROCEDURE end_process (
1391 p_benefit_action_id IN NUMBER
1392 , p_person_selected IN NUMBER
1393 , p_business_group_id IN NUMBER DEFAULT NULL
1394 )
1395 IS
1396 l_actions c_actions%ROWTYPE;
1397 l_batch_proc_id NUMBER;
1398 l_object_version_number NUMBER;
1399 BEGIN
1400 --
1401 -- Get totals for unprocessed, processed successfully and errored
1402 --
1403 g_proc := 'end_process';
1404 OPEN c_actions (p_benefit_action_id);
1405
1406 LOOP
1407 FETCH c_actions
1408 INTO l_actions;
1409
1410 EXIT WHEN c_actions%NOTFOUND;
1411
1412 IF l_actions.action_status_cd = 'P'
1413 THEN
1414 g_exec_param_rec.persons_proc_succ := l_actions.amount;
1415 ELSIF l_actions.action_status_cd = 'E'
1416 THEN
1417 g_exec_param_rec.persons_errored := l_actions.amount;
1418 END IF;
1419 END LOOP;
1420
1421 CLOSE c_actions;
1422
1423 OPEN c_error_per_summary (p_benefit_action_id);
1424
1425 FETCH c_error_per_summary
1426 INTO g_exec_param_rec.persons_errored;
1427
1428 CLOSE c_error_per_summary;
1429
1430 OPEN c_succ_per_summary (p_benefit_action_id);
1431
1432 FETCH c_succ_per_summary
1433 INTO g_exec_param_rec.persons_proc_succ;
1434
1435 CLOSE c_succ_per_summary;
1436
1437 OPEN c_lf_evt_open_summary (p_benefit_action_id);
1438
1439 FETCH c_lf_evt_open_summary
1440 INTO g_exec_param_rec.lf_evt_not_closed;
1441
1442 CLOSE c_lf_evt_open_summary;
1443
1444 OPEN c_lf_evt_close_summary (p_benefit_action_id);
1445
1446 FETCH c_lf_evt_close_summary
1447 INTO g_exec_param_rec.lf_evt_closed;
1448
1449 CLOSE c_lf_evt_close_summary;
1450
1451 --
1452 -- Set value of number of persons processed
1453 --
1454 g_exec_param_rec.persons_selected :=
1455 g_exec_param_rec.persons_errored + g_exec_param_rec.persons_proc_succ;
1456 ben_batch_proc_info_api.create_batch_proc_info
1457 (p_validate => FALSE
1458 , p_batch_proc_id => l_batch_proc_id
1459 , p_benefit_action_id => p_benefit_action_id
1460 , p_strt_dt => TRUNC
1461 (g_exec_param_rec.start_date
1462 )
1463 , p_end_dt => TRUNC (SYSDATE)
1464 , p_strt_tm => TO_CHAR
1465 (g_exec_param_rec.start_date
1466 , 'HH24:MI:SS'
1467 )
1468 , p_end_tm => TO_CHAR (SYSDATE
1469 , 'HH24:MI:SS'
1470 )
1471 , p_elpsd_tm => TO_CHAR
1472 ((DBMS_UTILITY.get_time
1473 - g_exec_param_rec.start_time
1474 )
1475 / 100
1476 )
1477 || ' seconds'
1478 , p_per_slctd => g_exec_param_rec.persons_selected
1479 , p_per_proc => g_exec_param_rec.lf_evt_closed
1480 , p_per_unproc => g_exec_param_rec.lf_evt_not_closed
1481 , p_per_proc_succ => g_exec_param_rec.persons_proc_succ
1482 , p_per_err => g_exec_param_rec.persons_errored
1483 , p_business_group_id => p_business_group_id
1484 , p_object_version_number => l_object_version_number
1485 );
1486 COMMIT;
1487 END end_process;
1488
1489 PROCEDURE init (p_group_plan_id IN NUMBER, p_lf_evt_ocrd_dt IN DATE)
1490 IS
1491 l_group_plan_rec c_group_plan_name%ROWTYPE;
1492 l_group_option_rec c_group_option_name%ROWTYPE;
1493 l_actual_plan_rec c_plan_name%ROWTYPE;
1494 l_actual_option_rec c_option_name%ROWTYPE;
1495 BEGIN
1496 g_proc := 'init';
1497
1498 g_cwb_rpt_person.person_rate_id :=null;
1499 g_cwb_rpt_person.pl_id :=null;
1500 g_cwb_rpt_person.person_id :=null;
1501 g_cwb_rpt_person.oipl_id :=null;
1502 g_cwb_rpt_person.group_pl_id :=null;
1503 g_cwb_rpt_person.group_oipl_id :=null;
1504 g_cwb_rpt_person.full_name :=null;
1505 g_cwb_rpt_person.emp_number :=null;
1506 g_cwb_rpt_person. business_group_name :=null;
1507 g_cwb_rpt_person.business_group_id :=null;
1508 g_cwb_rpt_person.manager_name :=null;
1509 g_cwb_rpt_person.ws_mgr_id :=null;
1510 g_cwb_rpt_person.pl_name :=null;
1511 g_cwb_rpt_person.opt_name :=null;
1512 g_cwb_rpt_person.amount :=null;
1513 g_cwb_rpt_person.units :=null;
1514 g_cwb_rpt_person.performance_rating :=null;
1515 g_cwb_rpt_person.assignment_changed :=null;
1516 g_cwb_rpt_person.status :=null;
1517 g_cwb_rpt_person.lf_evt_closed :=null;
1518 g_cwb_rpt_person.error_or_warning_text :=null;
1519 g_cwb_rpt_person.benefit_action_id :=null;
1520 OPEN c_group_plan_name (p_group_plan_id, p_lf_evt_ocrd_dt);
1521
1522 FETCH c_group_plan_name
1523 INTO l_group_plan_rec;
1524
1525 g_group_plan_name := l_group_plan_rec.NAME;
1526
1527 CLOSE c_group_plan_name;
1528
1529 OPEN c_group_option_name (p_group_plan_id, p_lf_evt_ocrd_dt);
1530
1531 LOOP
1532 FETCH c_group_option_name
1533 INTO l_group_option_rec;
1534
1535 EXIT WHEN c_group_option_name%NOTFOUND;
1536 g_cache_group_options (l_group_option_rec.group_oipl_id) := l_group_option_rec.NAME;
1537 END LOOP;
1538
1539 CLOSE c_group_option_name;
1540
1541 OPEN c_plan_name (p_group_plan_id, p_lf_evt_ocrd_dt);
1542
1543 LOOP
1544 FETCH c_plan_name
1545 INTO l_actual_plan_rec;
1546
1547 EXIT WHEN c_plan_name%NOTFOUND;
1548 g_cache_actual_plans (l_actual_plan_rec.pl_id) := l_actual_plan_rec.NAME;
1549 END LOOP;
1550
1551 CLOSE c_plan_name;
1552
1553 OPEN c_option_name (p_group_plan_id, p_lf_evt_ocrd_dt);
1554
1555 LOOP
1556 FETCH c_option_name
1557 INTO l_actual_option_rec;
1558
1559 EXIT WHEN c_option_name%NOTFOUND;
1560 g_cache_actual_options (l_actual_option_rec.oipl_id) := l_actual_option_rec.NAME;
1561 END LOOP;
1562
1563 CLOSE c_option_name;
1564 END;
1565
1566 PROCEDURE insert_person_actions (
1567 p_per_actn_id_array IN g_number_type
1568 , p_per_id IN g_number_type
1569 , p_group_per_in_ler_id IN g_number_type
1570 , p_benefit_action_id IN NUMBER
1571 , p_is_placeholder IN g_number_type
1572 )
1573 IS
1574 l_num_rows NUMBER := p_per_actn_id_array.COUNT;
1575 BEGIN
1576 g_proc := 'insert_person_actions';
1577 write('Time before inserting person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1578 FORALL l_count IN 1 .. p_per_actn_id_array.COUNT
1579 --
1580 INSERT INTO ben_person_actions
1581 (person_action_id
1582 , person_id
1583 , ler_id
1584 , benefit_action_id
1585 , action_status_cd
1586 , object_version_number
1587 , NON_PERSON_CD
1588 )
1589 VALUES (p_per_actn_id_array (l_count)
1590 , p_per_id (l_count)
1591 , p_group_per_in_ler_id (l_count)
1592 , p_benefit_action_id
1593 , 'U'
1594 , 1
1595 , decode(p_is_placeholder (l_count),1,'Y','N')
1596 );
1597
1598 write_m ('Time before inserting ben batch ranges '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1599 INSERT INTO ben_batch_ranges
1600 (range_id
1601 , benefit_action_id
1602 , range_status_cd
1603 , starting_person_action_id
1604 , ending_person_action_id
1605 , object_version_number
1606 )
1607 VALUES (ben_batch_ranges_s.NEXTVAL
1608 , p_benefit_action_id
1609 , 'U'
1610 , p_per_actn_id_array (1)
1611 , p_per_actn_id_array (l_num_rows)
1612 , 1
1613 );
1614 write_m ('Time at end of insert person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1615 END;
1616
1617 PROCEDURE print_cache
1618 IS
1619 l_evaluated NUMBER (9) := 0;
1620 l_successful NUMBER (9) := 0;
1621 l_error NUMBER (9) := 0;
1622 l_closed_le NUMBER (9) := 0;
1623 l_open_le NUMBER (9) := 0;
1624 l_previous NUMBER := -1;
1625 l_message_number NUMBER;
1626 l_message_text VARCHAR2 (2000);
1627 BEGIN
1628 g_proc := 'print_cache';
1629 WRITE ('Time before printing cache '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
1630 WRITE ('Populating records into reporting tables...');
1631 --
1632 FOR i IN 1 .. g_cache_cwb_rpt_person.COUNT
1633 LOOP
1634 BEGIN
1635 IF(g_cache_cwb_rpt_person (i).status='E') THEN
1636 l_message_number := fnd_number.canonical_to_number
1637 (substr(g_cache_cwb_rpt_person (i).error_or_warning_text,1,
1638 instr(g_cache_cwb_rpt_person (i).error_or_warning_text,' ')));
1639 END IF;
1640 l_message_text := g_cache_cwb_rpt_person (i).error_or_warning_text;
1641 EXCEPTION
1642 WHEN others THEN
1643 l_message_text := '-1 Oracle Internal Error. Check logfile for details.';
1644 END;
1645 IF(g_cache_cwb_rpt_person (i).ws_sub_acty_typ_cd = 'ICM7' AND
1646 nvl(g_cache_cwb_rpt_person (i).amount_posted,0) = 0) THEN
1647 g_cache_cwb_rpt_person (i).new_sal:=g_cache_cwb_rpt_person (i).prev_sal;
1648 END IF;/*
1649 g_cache_cwb_rpt_person (i).amount is null OR
1650 g_cache_cwb_rpt_person (i).amount = 0) THEN
1651 g_cache_cwb_rpt_person (i).prev_sal := null;
1652 g_cache_cwb_rpt_person (i).new_sal := null;
1653 END IF;*/
1654 INSERT INTO ben_cwb_rpt_detail
1655 (benefit_action_id
1656 , person_rate_id
1657 , pl_id
1658 , person_id
1659 , country_code
1660 , group_per_in_ler_id
1661 , oipl_id
1662 , group_pl_id
1663 , group_oipl_id
1664 , ws_mgr_id
1665 , lf_evt_ocrd_dt
1666 , full_name
1667 , employee_number
1668 , business_group_id
1669 , business_group_name
1670 , manager_name
1671 , pl_name
1672 , opt_name
1673 , amount
1674 , units
1675 , performance_rating
1676 , assignment_changed_flag
1677 , status_cd
1678 , lf_evt_closed_flag
1679 , error_or_warning_text
1680 , cwb_rpt_detail_id
1681 , base_salary_currency
1682 , currency
1683 , base_salary
1684 , elig_salary
1685 , percent_of_elig_sal
1686 , base_sal_freq
1687 , pay_ann_factor
1688 , pl_ann_factor
1689 , conversion_factor
1690 , adjusted_amount
1691 , prev_sal
1692 , new_sal
1693 , pay_proposal_id
1694 , pay_basis_id
1695 , element_entry_id
1696 , exchange_rate
1697 , effective_date
1698 , reason
1699 , eligibility
1700 , fte_factor
1701 , element_input_value
1702 , amount_posted
1703 , assignment_id
1704 , element_entry_value_id
1705 , input_value_id
1706 , element_type_id
1707 , eev_screen_entry_value
1708 , elmnt_processing_type
1709 , uom_precision
1710 , ws_sub_acty_typ_cd
1711 , posted_rating
1712 , rating_type
1713 , rating_date
1714 , prior_job
1715 , posted_job
1716 , proposed_job
1717 , prior_position
1718 , posted_position
1719 , proposed_position
1720 , prior_grade
1721 , posted_grade
1722 , proposed_grade
1723 , prior_group
1724 , posted_group
1725 , proposed_group
1726 , prior_flex1
1727 , posted_flex1
1728 , proposed_flex1
1729 , prior_flex2
1730 , posted_flex2
1731 , proposed_flex2
1732 , prior_flex3
1733 , posted_flex3
1734 , proposed_flex3
1735 , prior_flex4
1736 , posted_flex4
1737 , proposed_flex4
1738 , prior_flex5
1739 , posted_flex5
1740 , proposed_flex5
1741 , prior_flex6
1742 , posted_flex6
1743 , proposed_flex6
1744 , prior_flex7
1745 , posted_flex7
1746 , proposed_flex7
1747 , prior_flex8
1748 , posted_flex8
1749 , proposed_flex8
1750 , prior_flex9
1751 , posted_flex9
1752 , proposed_flex9
1753 , prior_flex10
1754 , posted_flex10
1755 , proposed_flex10
1756 , prior_flex11
1757 , posted_flex11
1758 , proposed_flex11
1759 , prior_flex12
1760 , posted_flex12
1761 , proposed_flex12
1762 , prior_flex13
1763 , posted_flex13
1764 , proposed_flex13
1765 , prior_flex14
1766 , posted_flex14
1767 , proposed_flex14
1768 , prior_flex15
1769 , posted_flex15
1770 , proposed_flex15
1771 , prior_flex16
1772 , posted_flex16
1773 , proposed_flex16
1774 , prior_flex17
1775 , posted_flex17
1776 , proposed_flex17
1777 , prior_flex18
1778 , posted_flex18
1779 , proposed_flex18
1780 , prior_flex19
1781 , posted_flex19
1782 , proposed_flex19
1783 , prior_flex20
1784 , posted_flex20
1785 , proposed_flex20
1786 , prior_flex21
1787 , posted_flex21
1788 , proposed_flex21
1789 , prior_flex22
1790 , posted_flex22
1791 , proposed_flex22
1792 , prior_flex23
1793 , posted_flex23
1794 , proposed_flex23
1795 , prior_flex24
1796 , posted_flex24
1797 , proposed_flex24
1798 , prior_flex25
1799 , posted_flex25
1800 , proposed_flex25
1801 , prior_flex26
1802 , posted_flex26
1803 , proposed_flex26
1804 , prior_flex27
1805 , posted_flex27
1806 , proposed_flex27
1807 , prior_flex28
1808 , posted_flex28
1809 , proposed_flex28
1810 , prior_flex29
1811 , posted_flex29
1812 , proposed_flex29
1813 , prior_flex30
1814 , posted_flex30
1815 , proposed_flex30
1816 , asgn_change_reason
1817 , pending_workflow
1818 , new_rpt
1819 , prev_eev_screen_entry_value)
1820 VALUES (benutils.g_benefit_action_id
1821 , g_cache_cwb_rpt_person (i).person_rate_id
1822 , g_cache_cwb_rpt_person (i).pl_id
1823 , g_cache_cwb_rpt_person (i).person_id
1824 , g_cache_cwb_rpt_person (i).country_code
1825 , g_cache_cwb_rpt_person (i).group_per_in_ler_id
1826 , g_cache_cwb_rpt_person (i).oipl_id
1827 , g_cache_cwb_rpt_person (i).group_pl_id
1828 , g_cache_cwb_rpt_person (i).group_oipl_id
1829 , g_cache_cwb_rpt_person (i).ws_mgr_id
1830 , g_cache_cwb_rpt_person (i).lf_evt_ocrd_date
1831 , g_cache_cwb_rpt_person (i).full_name
1832 , g_cache_cwb_rpt_person (i).emp_number
1833 , g_cache_cwb_rpt_person (i).business_group_id
1834 , g_cache_cwb_rpt_person (i).business_group_name
1835 , g_cache_cwb_rpt_person (i).manager_name
1836 , g_cache_cwb_rpt_person (i).pl_name
1837 , g_cache_cwb_rpt_person (i).opt_name
1838 , g_cache_cwb_rpt_person (i).amount
1839 , g_cache_cwb_rpt_person (i).units
1840 , g_cache_cwb_rpt_person (i).performance_rating
1841 , g_cache_cwb_rpt_person (i).assignment_changed
1842 , g_cache_cwb_rpt_person (i).status
1843 , g_cache_cwb_rpt_person (i).lf_evt_closed
1844 , l_message_text
1845 , ben_cwb_rpt_detail_s.NEXTVAL
1846 , g_cache_cwb_rpt_person (i).base_salary_currency
1847 , g_cache_cwb_rpt_person (i).currency
1848 , 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,
1849 nvl(g_cache_cwb_rpt_person (i).uom_precision,2))
1850 , g_cache_cwb_rpt_person (i).elig_salary
1851 , g_cache_cwb_rpt_person (i).percent_of_elig_sal
1852 , g_cache_cwb_rpt_person (i).base_sal_freq
1853 , g_cache_cwb_rpt_person (i).pay_ann_factor
1854 , g_cache_cwb_rpt_person (i).pl_ann_factor
1855 , g_cache_cwb_rpt_person (i).conversion_factor
1856 , g_cache_cwb_rpt_person (i).adjusted_amount
1857 , g_cache_cwb_rpt_person (i).prev_sal
1858 , g_cache_cwb_rpt_person (i).new_sal
1859 , g_cache_cwb_rpt_person (i).pay_proposal_id
1860 , g_cache_cwb_rpt_person (i).pay_basis_id
1861 , g_cache_cwb_rpt_person (i).element_entry_id
1862 , g_cache_cwb_rpt_person (i).exchange_rate
1863 , g_cache_cwb_rpt_person (i).effective_date
1864 , hr_general.decode_lookup('PROPOSAL_REASON',g_cache_cwb_rpt_person (i).reason) -- bug : 7042887
1865 , g_cache_cwb_rpt_person (i).eligibility
1866 , g_cache_cwb_rpt_person (i).fte_factor
1867 , g_cache_cwb_rpt_person (i).element_input_value
1868 , nvl(g_cache_cwb_rpt_person (i).amount_posted,0)
1869 , g_cache_cwb_rpt_person (i).assignment_id
1870 , g_cache_cwb_rpt_person (i).element_entry_value_id
1871 , g_cache_cwb_rpt_person (i).input_value_id
1872 , g_cache_cwb_rpt_person (i).element_type_id
1873 , g_cache_cwb_rpt_person (i).eev_screen_entry_value
1874 , g_cache_cwb_rpt_person (i).elmnt_processing_type
1875 , g_cache_cwb_rpt_person (i).uom_precision
1876 , g_cache_cwb_rpt_person (i).ws_sub_acty_typ_cd
1877 , substr(g_cache_cwb_rpt_person (i).posted_rating,1,30)
1878 , substr(g_cache_cwb_rpt_person (i).rating_type,1,30)
1879 , substr(g_cache_cwb_rpt_person (i).rating_date,1,30)
1880 , substr(g_cache_cwb_rpt_person (i).prior_job,1,700) --sg
1881 , substr(g_cache_cwb_rpt_person (i).posted_job,1,700) --sg
1882 , substr(g_cache_cwb_rpt_person (i).proposed_job,1,700) --sg
1883 , substr(g_cache_cwb_rpt_person (i).prior_position,1,240) --sg
1884 , substr(g_cache_cwb_rpt_person (i).posted_position,1,240) --sg
1885 , substr(g_cache_cwb_rpt_person (i).proposed_position,1,240) --sg
1886 , substr(g_cache_cwb_rpt_person (i).prior_grade,1,240) --sg
1887 , substr(g_cache_cwb_rpt_person (i).posted_grade,1,240) --sg
1888 , substr(g_cache_cwb_rpt_person (i).proposed_grade,1,240) --sg
1889 , substr(g_cache_cwb_rpt_person (i).prior_group,1,240) --sg
1890 , substr(g_cache_cwb_rpt_person (i).posted_group,1,240) --sg
1891 , substr(g_cache_cwb_rpt_person (i).proposed_group,1,240) --sg
1892 , substr(g_cache_cwb_rpt_person (i).prior_flex1,1,30)
1893 , substr(g_cache_cwb_rpt_person (i).posted_flex1,1,30)
1894 , substr(g_cache_cwb_rpt_person (i).proposed_flex1,1,30)
1895 , substr(g_cache_cwb_rpt_person (i).prior_flex2,1,30)
1896 , substr(g_cache_cwb_rpt_person (i).posted_flex2,1,30)
1897 , substr(g_cache_cwb_rpt_person (i).proposed_flex2,1,30)
1898 , substr(g_cache_cwb_rpt_person (i).prior_flex3,1,30)
1899 , substr(g_cache_cwb_rpt_person (i).posted_flex3,1,30)
1900 , substr(g_cache_cwb_rpt_person (i).proposed_flex3,1,30)
1901 , substr(g_cache_cwb_rpt_person (i).prior_flex4,1,30)
1902 , substr(g_cache_cwb_rpt_person (i).posted_flex4,1,30)
1903 , substr(g_cache_cwb_rpt_person (i).proposed_flex4,1,30)
1904 , substr(g_cache_cwb_rpt_person (i).prior_flex5,1,30)
1905 , substr(g_cache_cwb_rpt_person (i).posted_flex5,1,30)
1906 , substr(g_cache_cwb_rpt_person (i).proposed_flex5,1,30)
1907 , substr(g_cache_cwb_rpt_person (i).prior_flex6,1,30)
1908 , substr(g_cache_cwb_rpt_person (i).posted_flex6,1,30)
1909 , substr(g_cache_cwb_rpt_person (i).proposed_flex6,1,30)
1910 , substr(g_cache_cwb_rpt_person (i).prior_flex7,1,30)
1911 , substr(g_cache_cwb_rpt_person (i).posted_flex7,1,30)
1912 , substr(g_cache_cwb_rpt_person (i).proposed_flex7,1,30)
1913 , substr(g_cache_cwb_rpt_person (i).prior_flex8,1,30)
1914 , substr(g_cache_cwb_rpt_person (i).posted_flex8,1,30)
1915 , substr(g_cache_cwb_rpt_person (i).proposed_flex8,1,30)
1916 , substr(g_cache_cwb_rpt_person (i).prior_flex9,1,30)
1917 , substr(g_cache_cwb_rpt_person (i).posted_flex9,1,30)
1918 , substr(g_cache_cwb_rpt_person (i).proposed_flex9,1,30)
1919 , substr(g_cache_cwb_rpt_person (i).prior_flex10,1,30)
1920 , substr(g_cache_cwb_rpt_person (i).posted_flex10,1,30)
1921 , substr(g_cache_cwb_rpt_person (i).proposed_flex10,1,30)
1922 , substr(g_cache_cwb_rpt_person (i).prior_flex11,1,30)
1923 , substr(g_cache_cwb_rpt_person (i).posted_flex11,1,30)
1924 , substr(g_cache_cwb_rpt_person (i).proposed_flex11,1,30)
1925 , substr(g_cache_cwb_rpt_person (i).prior_flex12,1,30)
1926 , substr(g_cache_cwb_rpt_person (i).posted_flex12,1,30)
1927 , substr(g_cache_cwb_rpt_person (i).proposed_flex12,1,30)
1928 , substr(g_cache_cwb_rpt_person (i).prior_flex13,1,30)
1929 , substr(g_cache_cwb_rpt_person (i).posted_flex13,1,30)
1930 , substr(g_cache_cwb_rpt_person (i).proposed_flex13,1,30)
1931 , substr(g_cache_cwb_rpt_person (i).prior_flex14,1,30)
1932 , substr(g_cache_cwb_rpt_person (i).posted_flex14,1,30)
1933 , substr(g_cache_cwb_rpt_person (i).proposed_flex14,1,30)
1934 , substr(g_cache_cwb_rpt_person (i).prior_flex15,1,30)
1935 , substr(g_cache_cwb_rpt_person (i).posted_flex15,1,30)
1936 , substr(g_cache_cwb_rpt_person (i).proposed_flex15,1,30)
1937 , substr(g_cache_cwb_rpt_person (i).prior_flex16,1,30)
1938 , substr(g_cache_cwb_rpt_person (i).posted_flex16,1,30)
1939 , substr(g_cache_cwb_rpt_person (i).proposed_flex16,1,30)
1940 , substr(g_cache_cwb_rpt_person (i).prior_flex17,1,30)
1941 , substr(g_cache_cwb_rpt_person (i).posted_flex17,1,30)
1942 , substr(g_cache_cwb_rpt_person (i).proposed_flex17,1,30)
1943 , substr(g_cache_cwb_rpt_person (i).prior_flex18,1,30)
1944 , substr(g_cache_cwb_rpt_person (i).posted_flex18,1,30)
1945 , substr(g_cache_cwb_rpt_person (i).proposed_flex18,1,30)
1946 , substr(g_cache_cwb_rpt_person (i).prior_flex19,1,30)
1947 , substr(g_cache_cwb_rpt_person (i).posted_flex19,1,30)
1948 , substr(g_cache_cwb_rpt_person (i).proposed_flex19,1,30)
1949 , substr(g_cache_cwb_rpt_person (i).prior_flex20,1,30)
1950 , substr(g_cache_cwb_rpt_person (i).posted_flex20,1,30)
1951 , substr(g_cache_cwb_rpt_person (i).proposed_flex20,1,30)
1952 , substr(g_cache_cwb_rpt_person (i).prior_flex21,1,30)
1953 , substr(g_cache_cwb_rpt_person (i).posted_flex21,1,30)
1954 , substr(g_cache_cwb_rpt_person (i).proposed_flex21,1,30)
1955 , substr(g_cache_cwb_rpt_person (i).prior_flex22,1,30)
1956 , substr(g_cache_cwb_rpt_person (i).posted_flex22,1,30)
1957 , substr(g_cache_cwb_rpt_person (i).proposed_flex22,1,30)
1958 , substr(g_cache_cwb_rpt_person (i).prior_flex23,1,30)
1959 , substr(g_cache_cwb_rpt_person (i).posted_flex23,1,30)
1960 , substr(g_cache_cwb_rpt_person (i).proposed_flex23,1,30)
1961 , substr(g_cache_cwb_rpt_person (i).prior_flex24,1,30)
1962 , substr(g_cache_cwb_rpt_person (i).posted_flex24,1,30)
1963 , substr(g_cache_cwb_rpt_person (i).proposed_flex24,1,30)
1964 , substr(g_cache_cwb_rpt_person (i).prior_flex25,1,30)
1965 , substr(g_cache_cwb_rpt_person (i).posted_flex25,1,30)
1966 , substr(g_cache_cwb_rpt_person (i).proposed_flex25,1,30)
1967 , substr(g_cache_cwb_rpt_person (i).prior_flex26,1,30)
1968 , substr(g_cache_cwb_rpt_person (i).posted_flex26,1,30)
1969 , substr(g_cache_cwb_rpt_person (i).proposed_flex26,1,30)
1970 , substr(g_cache_cwb_rpt_person (i).prior_flex27,1,30)
1971 , substr(g_cache_cwb_rpt_person (i).posted_flex27,1,30)
1972 , substr(g_cache_cwb_rpt_person (i).proposed_flex27,1,30)
1973 , substr(g_cache_cwb_rpt_person (i).prior_flex28,1,30)
1974 , substr(g_cache_cwb_rpt_person (i).posted_flex28,1,30)
1975 , substr(g_cache_cwb_rpt_person (i).proposed_flex28,1,30)
1976 , substr(g_cache_cwb_rpt_person (i).prior_flex29,1,30)
1977 , substr(g_cache_cwb_rpt_person (i).posted_flex29,1,30)
1978 , substr(g_cache_cwb_rpt_person (i).proposed_flex29,1,30)
1979 , substr(g_cache_cwb_rpt_person (i).prior_flex30,1,30)
1980 , substr(g_cache_cwb_rpt_person (i).posted_flex30,1,30)
1981 , substr(g_cache_cwb_rpt_person (i).proposed_flex30,1,30)
1982 , substr(g_cache_cwb_rpt_person (i).asgn_change_reason,1,30)
1983 , g_cache_cwb_rpt_person (i).pending_workflow
1984 , 'Y'
1985 , g_cache_cwb_rpt_person (i).prev_eev_screen_entry_value);
1986 IF l_previous <> g_cache_cwb_rpt_person (i).person_id
1987 THEN
1988 l_previous := g_cache_cwb_rpt_person (i).person_id;
1989
1990 INSERT INTO ben_cwb_rpt_detail
1991 (benefit_action_id
1992 , person_rate_id
1993 , person_id
1994 , country_code
1995 , business_group_id
1996 , business_group_name
1997 , status_cd
1998 , lf_evt_closed_flag
1999 , cwb_rpt_detail_id
2000 )
2001 VALUES (benutils.g_benefit_action_id
2002 , -9999
2003 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).person_id
2004 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).country_code
2005 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_id
2006 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_name
2007 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status
2008 , g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).lf_evt_closed
2009 , ben_cwb_rpt_detail_s.NEXTVAL
2010 );
2011
2012 IF (g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status = 'E')
2013 THEN
2014 l_error := l_error + 1;
2015 END IF;
2016
2017 IF ( g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status = 'SC'
2018 OR g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status = 'WC'
2019 OR g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status = 'W'
2020 )
2021 THEN
2022 l_successful := l_successful + 1;
2023 END IF;
2024
2025 IF (g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).lf_evt_closed = 'Y')
2026 THEN
2027 l_closed_le := l_closed_le + 1;
2028 END IF;
2029
2030 IF (g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).lf_evt_closed = 'N')
2031 THEN
2032 l_open_le := l_open_le + 1;
2033 END IF;
2034 END IF;
2035 END LOOP;
2036 WRITE ('Time at the end of printing cache '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2037 --
2038 l_evaluated := l_successful + l_error;
2039 WRITE ('=======================Summary of the run =========================');
2040 WRITE ('No of persons evaluated in this thread ' || l_evaluated);
2041 WRITE ('No of persons successful in this thread ' || l_successful);
2042 WRITE ('No of persons errored in this thread ' || l_error);
2043 WRITE ('No of life events closed in this thread ' || l_closed_le);
2044 WRITE ('No of life events open in this thread ' || l_open_le);
2045 END;
2046
2047 PROCEDURE table_corrections (
2048 p_benefit_action_id IN NUMBER
2049 )
2050 IS
2051 l_table_correction_rec c_table_correction_data%ROWTYPE;
2052 BEGIN
2053 WRITE('table corrections');
2054 FOR l_table_correction_rec IN c_table_correction_data(p_benefit_action_id)
2055 LOOP
2056 INSERT INTO ben_cwb_rpt_detail (
2057 benefit_action_id,
2058 person_id,
2059 pl_id,
2060 oipl_id,
2061 group_per_in_ler_id,
2062 group_oipl_id,
2063 cwb_rpt_detail_id
2064 )
2065 VALUES (
2066 p_benefit_action_id,
2067 l_table_correction_rec.person_id,
2068 l_table_correction_rec.pl_id,
2069 l_table_correction_rec.oipl_id,
2070 l_table_correction_rec.group_per_in_ler_id,
2071 l_table_correction_rec.group_oipl_id,
2072 ben_cwb_rpt_detail_s.NEXTVAL
2073 );
2074 WRITE(
2075 p_benefit_action_id||'-'||
2076 l_table_correction_rec.person_id||'-'||
2077 l_table_correction_rec.pl_id||'-'||
2078 l_table_correction_rec.oipl_id||'-'||
2079 l_table_correction_rec.group_oipl_id
2080 );
2081 END LOOP;
2082 --EXCEPTION
2083 END;
2084
2085 PROCEDURE get_plan_abr_info(
2086 p_lf_evt_ocrd_date IN DATE
2087 , p_pl_id IN NUMBER
2088 , p_oipl_id IN NUMBER
2089 , p_element_type_id OUT NOCOPY NUMBER
2090 , p_input_value_id OUT NOCOPY NUMBER
2091 )
2092 IS
2093 l_found boolean;
2094 l_plan_abr_info plan_abr_info;
2095 l_element_type_id number;
2096 l_input_value_id number;
2097 BEGIN
2098 l_found := false;
2099 FOR element IN 1..g_plan_abr_info.COUNT
2100 LOOP
2101 if(g_plan_abr_info(element).pl_id = p_pl_id) then
2102 if(g_plan_abr_info(element).oipl_id = p_oipl_id) then
2103 l_element_type_id := g_plan_abr_info(element).element_type_id;
2104 l_input_value_id := g_plan_abr_info(element).input_value_id;
2105 l_found := true;
2106 end if;
2107 end if;
2108 END LOOP;
2109 if(l_found = false) then
2110 OPEN c_get_abr_info(p_lf_evt_ocrd_date
2111 ,p_pl_id
2112 ,p_oipl_id);
2113 FETCH c_get_abr_info INTO l_element_type_id,l_input_value_id;
2114 CLOSE c_get_abr_info;
2115 l_plan_abr_info.pl_id := p_pl_id;
2116 l_plan_abr_info.oipl_id := p_oipl_id;
2117 l_plan_abr_info.element_type_id := l_element_type_id;
2118 l_plan_abr_info.input_value_id := l_input_value_id;
2119 g_plan_abr_info.extend;
2120 g_plan_abr_info(g_plan_abr_info.last) := l_plan_abr_info;
2121 end if;
2122 p_element_type_id := l_element_type_id;
2123 p_input_value_id := l_input_value_id;
2124 EXCEPTION
2125 WHEN others THEN
2126 WRITE('Error at get_plan_abr_info');
2127 WRITE(SQLERRM);
2128 END;
2129
2130 FUNCTION get_ws_rate_start_dt(
2131 p_group_per_in_ler_id IN NUMBER,
2132 p_group_pl_id IN NUMBER,
2133 p_pl_id IN NUMBER,
2134 p_oipl_id IN NUMBER,
2135 p_group_oipl_id IN NUMBER,
2136 p_lf_evt_ocrd_dt IN DATE
2137 )
2138 RETURN DATE
2139 IS
2140 l_rate_start_date DATE;
2141 BEGIN
2142 OPEN c_get_ws_rate_start_dt(
2143 p_group_per_in_ler_id,
2144 p_group_pl_id,
2145 p_pl_id,
2146 p_oipl_id,
2147 p_group_oipl_id,
2148 p_lf_evt_ocrd_dt);
2149 FETCH c_get_ws_rate_start_dt INTO l_rate_start_date;
2150 CLOSE c_get_ws_rate_start_dt;
2151 RETURN l_rate_start_date;
2152 END ;
2153
2154 FUNCTION get_override_start_date(
2155 p_lf_evt_ocrd_date IN DATE
2156 , p_group_pl_id IN NUMBER
2157 , p_pl_id IN NUMBER
2158 , p_group_oipl_id IN NUMBER
2159 , p_oipl_id IN NUMBER
2160 , p_effective_date IN DATE
2161 )
2162 RETURN DATE
2163 IS
2164 l_local_plan varchar2(50);
2165 l_local_option varchar2(50);
2166 l_group_plan varchar2(50);
2167 l_group_option varchar2(50);
2168 l_count number;
2169 l_found boolean;
2170 l_index number;
2171 l_plan number;
2172 l_group_pl_id number;
2173 l_pl_id number;
2174 l_group_oipl_id number;
2175 l_oipl_id number;
2176 --l_lf_evt_ocrd_date date;
2177 l_date date;
2178 l_plan_date plan_override_date;
2179 BEGIN
2180 l_local_plan := p_pl_id;
2181 l_local_option := p_oipl_id;
2182 l_group_plan := p_group_pl_id;
2183 l_group_option := p_group_oipl_id;
2184 l_count := 1;
2185 l_found := null;
2186 /*WRITE(p_lf_evt_ocrd_date||' '||p_group_pl_id||' '||p_pl_id||' '||p_group_oipl_id
2187 ||' '||p_oipl_id||' '||p_effective_date);*/
2188 WHILE l_count <= 4 LOOP
2189 CASE l_count
2190 WHEN 1 THEN if(((l_found is null)or(l_found <> true))and(l_local_option <> -1)) then
2191 l_plan_date.plan := l_local_option;
2192 FOR element IN 1..g_override_dates.COUNT
2193 LOOP
2194 if(g_override_dates(element).plan = l_local_option) then
2195 if(g_override_dates(element).date is not null) then
2196 l_found := true; --found entry in table with date
2197 l_date := g_override_dates(element).date;
2198 WRITE(g_override_dates(element).date
2199 ||' Override date found for Local Option : '
2200 || g_override_dates(element).plan);
2201 else
2202 l_found := false; --found entry : dont run cursor!
2203 end if;
2204 end if;
2205 EXIT when(l_found = true);
2206 END LOOP;
2207 if(l_found is null) then
2208 OPEN c_override_start_date(p_group_pl_id,p_pl_id,p_group_oipl_id,p_oipl_id,p_lf_evt_ocrd_date);
2209 FETCH c_override_start_date INTO l_date;
2210 CLOSE c_override_start_date;
2211 if(l_date is not null) then
2212 l_found := true;
2213 end if;
2214 l_plan_date.date := l_date;
2215 g_override_dates.extend;
2216 g_override_dates(g_override_dates.last) := l_plan_date;
2217 end if;
2218 end if;
2219 WHEN 2 THEN if(((l_found is null)or(l_found <> true))and(l_group_option <> -1)) then
2220 l_plan_date.plan := l_group_option;
2221 FOR element IN 1..g_override_dates.COUNT
2222 LOOP
2223 if(g_override_dates(element).plan = l_group_option) then
2224 if(g_override_dates(element).date is not null) then
2225 l_found := true; --found entry in table with date
2226 l_date := g_override_dates(element).date;
2227 WRITE(g_override_dates(element).date
2228 ||' Override date found for Group Option : '
2229 || g_override_dates(element).plan);
2230 else
2231 l_found := false; --found entry : dont run cursor!
2232 end if;
2233 end if;
2234 EXIT when(l_found = true);
2235 END LOOP;
2236 if(l_found is null) then
2237 OPEN c_override_start_date(p_group_pl_id,p_group_pl_id,p_group_oipl_id,p_group_oipl_id,p_lf_evt_ocrd_date);
2238 FETCH c_override_start_date INTO l_date;
2239 CLOSE c_override_start_date;
2240 if(l_date is not null) then
2241 l_found := true;
2242 end if;
2243 l_plan_date.date := l_date;
2244 g_override_dates.extend;
2245 g_override_dates(g_override_dates.last) := l_plan_date;
2246 end if;
2247 end if;
2248 WHEN 3 THEN if(((l_found is null)or(l_found <> true))and(l_local_plan<>l_group_plan)) then
2249 l_plan_date.plan := l_local_plan;
2250 FOR element IN 1..g_override_dates.COUNT
2251 LOOP
2252 if(g_override_dates(element).plan = l_local_plan) then
2253 if(g_override_dates(element).date is not null) then
2254 l_found := true; --found entry in table with date
2255 l_date := g_override_dates(element).date;
2256 WRITE(g_override_dates(element).date
2257 ||' Override date found for Local Plan : '
2258 || g_override_dates(element).plan);
2259 else
2260 l_found := false; --found entry : dont run cursor!
2261 end if;
2262 end if;
2263 EXIT when(l_found = true);
2264 END LOOP;
2265 if(l_found is null) then
2266 OPEN c_override_start_date(p_group_pl_id,p_pl_id,-1,-1,p_lf_evt_ocrd_date);
2267 FETCH c_override_start_date INTO l_date;
2268 CLOSE c_override_start_date;
2269 if(l_date is not null) then
2270 l_found := true;
2271 end if;
2272 l_plan_date.date := l_date;
2273 g_override_dates.extend;
2274 g_override_dates(g_override_dates.last) := l_plan_date;
2275 end if;
2276 end if;
2277 WHEN 4 THEN if((l_found is null)or(l_found <> true)) then
2278 l_plan_date.plan := l_group_plan;
2279 FOR element IN 1..g_override_dates.COUNT
2280 LOOP
2281 if(g_override_dates(element).plan = l_group_plan) then
2282 if(g_override_dates(element).date is not null) then
2283 l_found := true; --found entry in table with date
2284 l_date := g_override_dates(element).date;
2285 WRITE(g_override_dates(element).date
2286 ||' Override date found for Group Plan : '
2287 || g_override_dates(element).plan);
2288 else
2289 l_found := false; --found entry : dont run cursor!
2290 end if;
2291 end if;
2292 EXIT when(l_found = true);
2293 END LOOP;
2294 if(l_found is null) then
2295 OPEN c_override_start_date(p_group_pl_id,p_group_pl_id,-1,-1,p_lf_evt_ocrd_date);
2296 FETCH c_override_start_date INTO l_date;
2297 CLOSE c_override_start_date;
2298 if(l_date is not null) then
2299 l_found := true;
2300 end if;
2301 l_plan_date.date := l_date;
2302 g_override_dates.extend;
2303 g_override_dates(g_override_dates.last) := l_plan_date;
2304 end if;
2305 end if;
2306 END CASE;
2307 l_count := l_count + 1;
2308 END LOOP;
2309 /*
2310 FOR element IN 1..g_override_dates.COUNT
2311 LOOP
2312 WRITE(g_override_dates(element).plan||' - '||g_override_dates(element).date);
2313 END LOOP;
2314 */
2315 RETURN l_date;
2316 END;
2317
2318 PROCEDURE process_life_event (
2319 p_person_id IN NUMBER
2320 , p_lf_evt_ocrd_date IN DATE
2321 , p_plan_id IN NUMBER
2322 , p_group_per_in_ler_id IN NUMBER
2323 , p_effective_date IN DATE
2324 , p_employees_in_bg IN NUMBER
2325 )
2326 IS
2327 pil_rec c_per_in_ler_ids%ROWTYPE;
2328 l_procd_dt DATE;
2329 l_strtd_dt DATE;
2330 l_voidd_dt DATE;
2331 l_count_open_lers NUMBER := 0;
2332 l_pil_ovn c_pil_ovn%ROWTYPE;
2333 l_info_ovn c_info_ovn%ROWTYPE;
2334 BEGIN
2335
2336 write_m ('Time before processing the life events '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2337 OPEN c_per_in_ler_ids (p_plan_id, p_employees_in_bg, p_person_id, p_lf_evt_ocrd_date);
2338
2339 LOOP
2340 FETCH c_per_in_ler_ids
2341 INTO pil_rec;
2342
2343 EXIT WHEN c_per_in_ler_ids%NOTFOUND;
2344
2345 IF (pil_rec.per_in_ler_id <> p_group_per_in_ler_id)
2346 THEN
2347 write_h ('selected per_in_ler_id ' || pil_rec.per_in_ler_id || ' for closing');
2348 ben_person_life_event_api.update_person_life_event
2349 (p_per_in_ler_id => pil_rec.per_in_ler_id
2350 , p_per_in_ler_stat_cd => 'PROCD'
2351 , p_procd_dt => l_procd_dt
2352 , p_voidd_dt => l_voidd_dt
2353 , p_strtd_dt => l_strtd_dt
2354 , p_object_version_number => pil_rec.object_version_number
2355 , p_effective_date => p_effective_date
2356 );
2357 END IF;
2358 END LOOP;
2359
2360 CLOSE c_per_in_ler_ids;
2361 write_m ('Time after processing the life events '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2362
2363 g_persons_procd := g_persons_procd + 1;
2364
2365 OPEN c_per_in_ler_ids (p_plan_id, p_employees_in_bg, p_person_id, p_lf_evt_ocrd_date);
2366
2367 LOOP
2368 FETCH c_per_in_ler_ids
2369 INTO pil_rec;
2370
2371 EXIT WHEN c_per_in_ler_ids%NOTFOUND;
2372
2373 IF ( pil_rec.per_in_ler_id <> p_group_per_in_ler_id
2374 AND pil_rec.per_in_ler_stat_cd <> 'PROCD'
2375 )
2376 THEN
2377 write_h ('Following actual per_in_ler_id ' || pil_rec.per_in_ler_id || ' still open');
2378 l_count_open_lers := l_count_open_lers + 1;
2379 END IF;
2380 END LOOP;
2381
2382 CLOSE c_per_in_ler_ids;
2383
2384 IF (l_count_open_lers = 0)
2385 THEN
2386 write_h ('selected the group_per_in_ler_id ' || p_group_per_in_ler_id || ' for closing');
2387
2388 OPEN c_pil_ovn (p_group_per_in_ler_id);
2389
2390 FETCH c_pil_ovn
2391 INTO l_pil_ovn;
2392
2393 CLOSE c_pil_ovn;
2394 write_h ('Time before updating the person life event '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2395 ben_person_life_event_api.update_person_life_event
2396 (p_per_in_ler_id => p_group_per_in_ler_id
2397 , p_per_in_ler_stat_cd => 'PROCD'
2398 , p_procd_dt => l_procd_dt
2399 , p_voidd_dt => l_voidd_dt
2400 , p_strtd_dt => l_strtd_dt
2401 , p_object_version_number => l_pil_ovn.object_version_number
2402 , p_effective_date => p_effective_date
2403 );
2404 write_h ('Time after updating the person life event '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2405
2406
2407 OPEN c_info_ovn (p_group_per_in_ler_id);
2408
2409 FETCH c_info_ovn
2410 INTO l_info_ovn;
2411 CLOSE c_info_ovn;
2412
2413 WRITE ('updating post process stat code...');
2414 ben_cwb_person_info_api.update_person_info
2415 (p_group_per_in_ler_id => p_group_per_in_ler_id
2416 , p_post_process_stat_cd => 'PR'
2417 , p_object_version_number => l_info_ovn.object_version_number
2418 );
2419
2420 -- ************ audit changes ************* --
2421 ben_cwb_audit_api.update_per_record(p_per_in_ler_id => p_group_per_in_ler_id);
2422 -- **************************************** --
2423
2424 WRITE ('creating cache for reporting...');
2425
2426 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'Y';
2427 ELSE
2428 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'N';
2429 END IF;
2430 END;
2431
2432 PROCEDURE process_sal_comp_rates (
2433 p_effective_date IN DATE
2434 , p_lf_evt_ocrd_date IN DATE
2435 , p_group_pl_id IN NUMBER
2436 , p_group_per_in_ler_id IN NUMBER
2437 , p_person_id IN NUMBER
2438 , p_cache_cwb_rpt_person IN OUT NOCOPY g_cache_cwb_rpt_person_rec
2439 , p_cwb_rpt_person_rec IN OUT NOCOPY g_cwb_rpt_person_rec
2440 , p_debug_level IN VARCHAR2 DEFAULT NULL
2441 , p_use_rate_start_date IN VARCHAR2 DEFAULT 'N'
2442 , p_pay_proposal_id OUT NOCOPY NUMBER
2443 , p_pay_basis_id OUT NOCOPY NUMBER
2444 , p_warning OUT NOCOPY BOOLEAN
2445 )
2446 IS
2447 asg_rec c_prev_pay_proposal%ROWTYPE;
2448 elm_rec c_element_entry%ROWTYPE;
2449 tot_com_amt_rec c_tot_chg_amt_for_proposal%ROWTYPE;
2450 per_bg_rec c_person_info%ROWTYPE;
2451 sal_rate_rec c_sal_comp_rates%ROWTYPE;
2452 l_rate_ovn c_rate_ovn%ROWTYPE;
2453 l_sal_factors c_sal_factors%ROWTYPE;
2454 l_effective_date DATE;
2455 l_precision NUMBER;
2456 l_pay_proposal_id NUMBER;
2457 l_sal_incr NUMBER;
2458 l_ele_ent_id NUMBER;
2459 l_prev_sal NUMBER;
2460 l_object_version_number NUMBER;
2461 l_dummy1 BOOLEAN;
2462 l_dummy2 BOOLEAN;
2463 l_dummy3 BOOLEAN;
2464 l_dummy4 BOOLEAN;
2465 l_component_ovn NUMBER;
2466 l_component_id NUMBER;
2467 l_component_reason VARCHAR2 (200);
2468 l_count NUMBER := 0;
2469 l_total NUMBER;
2470 l_error BOOLEAN;
2471 l_warning_text VARCHAR2 (2000);
2472 l_oipl_id NUMBER;
2473 l_message VARCHAR2 (600);
2474 l_message_name VARCHAR2 (240);
2475 l_app_name VARCHAR2 (240);
2476 l_pay_basis_id NUMBER;
2477 future_pay_proposal_rec c_future_pay_proposal%ROWTYPE;
2478 l_element_input_currency VARCHAR2 (30);
2479 BEGIN
2480 g_actn := ' calling sal admin api for components ...';
2481 g_proc := 'process_sal_comp_rates';
2482 WRITE (g_actn);
2483
2484 l_error := FALSE;
2485 p_warning := FALSE;
2486
2487 IF(p_use_rate_start_date = 'Y') THEN
2488 WRITE ('Postings are ws rate start date based');
2489 END IF;
2490
2491 OPEN c_salary_effective_date(p_group_per_in_ler_id,p_use_rate_start_date);
2492 FETCH c_salary_effective_date INTO l_effective_date;
2493 CLOSE c_salary_effective_date;
2494
2495 IF(l_effective_date IS NULL AND p_use_rate_start_date = 'Y') THEN
2496 g_actn :=
2497 'The salary is not posted as no rate start date is defined.';
2498 WRITE (g_actn);
2499 fnd_message.set_name ('BEN', 'BEN_94906_CWB_NO_RATE_STRT_DT');
2500 l_message := fnd_message.get_encoded;
2501 fnd_message.set_encoded(l_message);
2502 --
2503 fnd_message.parse_encoded(encoded_message => l_message,
2504 app_short_name => l_app_name,
2505 message_name => l_message_name);
2506 IF g_person_errored = FALSE THEN
2507 l_warning_text := substr(l_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2508 END IF;
2509 l_error := TRUE;
2510 g_person_errored := TRUE;
2511 END IF;
2512
2513 IF(l_effective_date IS NULL) THEN
2514 l_effective_date:=p_effective_date;
2515 END IF;
2516 WRITE ('Effective date :'||l_effective_date);
2517
2518 OPEN c_prev_pay_proposal (p_group_per_in_ler_id, l_effective_date);
2519
2520 FETCH c_prev_pay_proposal
2521 INTO asg_rec;
2522 CLOSE c_prev_pay_proposal;
2523
2524 l_pay_basis_id := asg_rec.pay_basis_id;
2525
2526 IF (asg_rec.pay_basis_id IS NOT NULL)
2527 THEN
2528 OPEN c_element_entry (asg_rec.pay_basis_id, asg_rec.assignment_id, l_effective_date);
2529
2530 FETCH c_element_entry
2531 INTO elm_rec;
2532 l_prev_sal := asg_rec.proposed_salary_n;
2533 write_s ('Previous salary is ' || l_prev_sal);
2534
2535 IF c_element_entry%FOUND
2536 THEN
2537 l_ele_ent_id := elm_rec.element_entry_id;
2538 write_m ('Element entry found...');
2539 ELSE
2540 l_ele_ent_id := NULL;
2541 write_m ('Element entry not found...');
2542 END IF;
2543
2544 CLOSE c_element_entry;
2545
2546 OPEN c_input_value_precision(asg_rec.assignment_id,l_effective_date);
2547 FETCH c_input_value_precision INTO l_precision;
2548 CLOSE c_input_value_precision;
2549
2550 ELSE
2551 g_actn :=
2552 'The salary is not posted as no pay basis is defined.';
2553 WRITE (g_actn);
2554 fnd_message.set_name ('BEN', 'BEN_94674_CWB_NO_PAY_BASIS');
2555 l_message := fnd_message.get_encoded;
2556 fnd_message.set_encoded(l_message);
2557 --
2558 fnd_message.parse_encoded(encoded_message => l_message,
2559 app_short_name => l_app_name,
2560 message_name => l_message_name);
2561 IF g_person_errored = FALSE THEN
2562 l_warning_text := substr(l_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2563 END IF;
2564 l_error := TRUE;
2565 g_person_errored := TRUE;
2566 END IF;
2567
2568 IF(l_precision IS NULL) THEN
2569 l_precision:=2;
2570 END IF;
2571
2572 OPEN c_person_info (p_group_per_in_ler_id);
2573
2574 FETCH c_person_info
2575 INTO per_bg_rec;
2576
2577 CLOSE c_person_info;
2578
2579 write_h(per_bg_rec.base_salary);
2580 write_h(asg_rec.proposed_salary_n);
2581 write_h(l_precision);
2582
2583 IF (round(per_bg_rec.base_salary,l_precision) <>
2584 round(asg_rec.proposed_salary_n,l_precision))
2585 THEN
2586 g_actn :=
2587 'This employee had a recent update to Base Salary '
2588 || 'or Pay Basis. The new salary could not be posted.'
2589 || ' Either delete the recent update and rerun this process,'
2590 || 'or apply the new salary manually. ';
2591 WRITE (g_actn);
2592 write_m('Salary in HR '||asg_rec.proposed_salary_n);
2593 write_m('Salary in CWB '||per_bg_rec.base_salary);
2594 fnd_message.set_name ('BEN', 'BEN_91141_CWB_RECENT_SAL_CHG');
2595 l_message := fnd_message.get_encoded;
2596 fnd_message.set_encoded(l_message);
2597 --
2598 fnd_message.parse_encoded(encoded_message => l_message,
2599 app_short_name => l_app_name,
2600 message_name => l_message_name);
2601 IF g_person_errored = FALSE THEN
2602 l_warning_text := substr(l_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2603 END IF;
2604 l_error := TRUE;
2605 g_person_errored := TRUE;
2606 --fnd_message.raise_error;
2607 END IF;
2608
2609 OPEN c_sal_comp_rates_tot (p_group_per_in_ler_id,
2610 p_group_pl_id,
2611 p_lf_evt_ocrd_date,
2612 l_effective_date);
2613 FETCH c_sal_comp_rates_tot INTO l_total;
2614 CLOSE c_sal_comp_rates_tot;
2615
2616 IF ((l_total IS NOT NULL) AND (NOT l_error)) THEN
2617
2618 WRITE ('Inserting salary proposal...');
2619 write_m ('Time before inserting the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2620 BEGIN
2621 hr_maintain_proposal_api.insert_salary_proposal
2622 (p_pay_proposal_id => l_pay_proposal_id
2623 , p_assignment_id => asg_rec.assignment_id
2624 , p_business_group_id => per_bg_rec.business_group_id
2625 , p_change_date => l_effective_date
2626 , p_object_version_number => l_object_version_number
2627 , p_multiple_components => 'Y'
2628 , p_approved => 'N'
2629 , p_validate => FALSE
2630 , p_element_entry_id => l_ele_ent_id
2631 , p_inv_next_sal_date_warning => l_dummy1
2632 , p_proposed_salary_warning => l_dummy2
2633 , p_approved_warning => l_dummy3
2634 , p_payroll_warning => l_dummy4
2635 );
2636 OPEN c_future_pay_proposal (p_group_per_in_ler_id, l_effective_date);
2637
2638 FETCH c_future_pay_proposal
2639 INTO future_pay_proposal_rec;
2640 CLOSE c_future_pay_proposal;
2641
2642 IF(future_pay_proposal_rec.proposed_salary_n is not null) THEN
2643 p_warning := TRUE;
2644 fnd_message.set_name ('BEN', 'BEN_94685_FUTURE_SAL_PROP_WARN');
2645 l_message := fnd_message.get_encoded;
2646 fnd_message.set_encoded(l_message);
2647 --
2648 fnd_message.parse_encoded(encoded_message => l_message,
2649 app_short_name => l_app_name,
2650 message_name => l_message_name);
2651 IF g_person_errored = FALSE THEN
2652 l_warning_text := substr(l_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2653 END IF;
2654 g_person_errored := TRUE;
2655 --l_warning_text := l_warning_text||'Future dated salary proposal exists';
2656 END IF;
2657
2658 EXCEPTION
2659 WHEN OTHERS THEN
2660 WRITE('Error in insert_salary_proposal : '||SQLERRM);
2661 l_error := TRUE;
2662 l_message := fnd_message.get_encoded;
2663 fnd_message.set_encoded(l_message);
2664 --
2665 fnd_message.parse_encoded(encoded_message => l_message,
2666 app_short_name => l_app_name,
2667 message_name => l_message_name);
2668 IF g_person_errored = FALSE THEN
2669 l_warning_text := substr(l_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2670 END IF;
2671 g_person_errored := TRUE;
2672 END;
2673 write_m ('Time after inserting the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2674 END IF;
2675
2676 OPEN c_sal_comp_rates (p_group_per_in_ler_id
2677 , p_group_pl_id
2678 , p_lf_evt_ocrd_date
2679 , l_effective_date
2680 );
2681 write_m ('Time before looping salary components '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2682
2683 LOOP
2684 FETCH c_sal_comp_rates
2685 INTO sal_rate_rec;
2686 l_oipl_id := sal_rate_rec.oipl_id;
2687 EXIT WHEN c_sal_comp_rates%NOTFOUND;
2688
2689 IF(l_oipl_id <> -1) THEN
2690 --IF l_total IS NOT NULL THEN
2691 OPEN c_sal_factors(p_group_pl_id,p_lf_evt_ocrd_date,p_group_per_in_ler_id);
2692 FETCH c_sal_factors INTO l_sal_factors;
2693 CLOSE c_sal_factors;
2694 l_sal_incr := round(sal_rate_rec.ws_val * l_sal_factors.pl_annulization_factor / l_sal_factors.pay_annulization_factor,l_precision);
2695 WRITE('l_sal_factors.pl_annulization_factor is '||l_sal_factors.pl_annulization_factor);
2696 WRITE('l_sal_factors.pay_annulization_factor is '||l_sal_factors.pay_annulization_factor);
2697 WRITE('l_sal_factors.uom_precision is '||l_sal_factors.uom_precision);
2698
2699 --END IF;
2700 END IF;
2701
2702 WRITE ('Populating report record for salary processing with components...');
2703 p_cwb_rpt_person_rec.person_rate_id := sal_rate_rec.person_rate_id;
2704 p_cwb_rpt_person_rec.pl_id := sal_rate_rec.pl_id;
2705 p_cwb_rpt_person_rec.oipl_id := sal_rate_rec.oipl_id;
2706 p_cwb_rpt_person_rec.group_pl_id := sal_rate_rec.group_pl_id;
2707 p_cwb_rpt_person_rec.group_oipl_id := sal_rate_rec.group_oipl_id;
2708 p_cwb_rpt_person_rec.full_name := sal_rate_rec.full_name;
2709 p_cwb_rpt_person_rec.emp_number := sal_rate_rec.employee_number;
2710 p_cwb_rpt_person_rec.business_group_id := sal_rate_rec.business_group_id;
2711 p_cwb_rpt_person_rec.ws_mgr_id := sal_rate_rec.ws_mgr_id;
2712 p_cwb_rpt_person_rec.units := null;
2713
2714 p_cwb_rpt_person_rec.base_salary_currency := per_bg_rec.base_salary_currency;
2715 p_cwb_rpt_person_rec.base_salary := per_bg_rec.base_salary;
2716 p_cwb_rpt_person_rec.elig_salary := round(sal_rate_rec.elig_sal_val,l_precision);
2717 p_cwb_rpt_person_rec.amount := round(nvl(sal_rate_rec.ws_val,0),l_precision);
2718 if(p_cwb_rpt_person_rec.elig_salary is null OR p_cwb_rpt_person_rec.elig_salary = 0 ) then
2719 p_cwb_rpt_person_rec.percent_of_elig_sal := 0;
2720 else
2721 p_cwb_rpt_person_rec.percent_of_elig_sal :=
2722 round((p_cwb_rpt_person_rec.amount/p_cwb_rpt_person_rec.elig_salary)*100,l_precision);
2723 end if;
2724 p_cwb_rpt_person_rec.base_sal_freq := per_bg_rec.base_salary_frequency;
2725 p_cwb_rpt_person_rec.pay_ann_factor := l_sal_factors.pay_annulization_factor;
2726 p_cwb_rpt_person_rec.pl_ann_factor := l_sal_factors.pl_annulization_factor;
2727 p_cwb_rpt_person_rec.conversion_factor :=
2728 round(l_sal_factors.pl_annulization_factor/l_sal_factors.pay_annulization_factor,
2729 l_precision);
2730 p_cwb_rpt_person_rec.adjusted_amount := round(nvl(l_sal_incr,0),l_precision);
2731 p_cwb_rpt_person_rec.prev_sal := round(nvl(asg_rec.proposed_salary_n,0),l_precision);
2732 p_cwb_rpt_person_rec.exchange_rate := sal_rate_rec.xchg_rate;
2733 p_cwb_rpt_person_rec.effective_date := l_effective_date;
2734 p_cwb_rpt_person_rec.reason := sal_rate_rec.component_reason;
2735 p_cwb_rpt_person_rec.eligibility := sal_rate_rec.elig_flag;
2736 p_cwb_rpt_person_rec.fte_factor := per_bg_rec.fte_factor;
2737 p_cwb_rpt_person_rec.group_per_in_ler_id := p_group_per_in_ler_id;
2738 p_cwb_rpt_person_rec.currency := sal_rate_rec.currency;
2739 p_cwb_rpt_person_rec.lf_evt_ocrd_date := p_lf_evt_ocrd_date;
2740 p_cwb_rpt_person_rec.ws_sub_acty_typ_cd := 'ICM7';
2741
2742
2743 if(l_error OR p_warning) then
2744 p_cwb_rpt_person_rec.error_or_warning_text := substr(l_warning_text,1,2000);
2745 end if;
2746
2747 IF(l_oipl_id <> -1) THEN
2748 IF (l_total IS NOT NULL AND nvl(l_sal_incr,0) <> 0 AND (NOT l_error) AND sal_rate_rec.elig_flag = 'Y') THEN
2749 WRITE ('Inserting salary proposal component...');
2750 write_h ('==============Inserting Salary component ========');
2751 write_h ('||Parameter Description ');
2752 write_h ('||p_assignment_id - ' || asg_rec.assignment_id);
2753 write_h ('||p_business_group_id - ' || per_bg_rec.business_group_id);
2754 write_h ('||p_change_date - ' || l_effective_date);
2755 write_h ('||p_component_reason - ' || sal_rate_rec.component_reason);
2756 write_h ('||p_component_increase - ' || l_sal_incr);
2757 write_h ('================================================');
2758 write_m ('Time before inserting the salary proposal components '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2759 BEGIN
2760 OPEN c_element_input_currency(elm_rec.element_type_id,l_effective_date);
2761 FETCH c_element_input_currency INTO l_element_input_currency;
2762 CLOSE c_element_input_currency;
2763
2764 IF(l_element_input_currency is not null
2765 AND l_element_input_currency <> sal_rate_rec.currency) THEN
2766 WRITE('Currency in CWB does not match Input Currency of the Element Type');
2767 write_m('Currency for Element '||l_element_input_currency);
2768 write_m('Currency in CWB '||sal_rate_rec.currency);
2769
2770 fnd_message.set_name ('BEN', 'BEN_94673_EL_CURR_MISMATCH');
2771 l_message := fnd_message.get_encoded;
2772 fnd_message.set_encoded(l_message);
2773 --
2774 fnd_message.parse_encoded(encoded_message => l_message,
2775 app_short_name => l_app_name,
2776 message_name => l_message_name);
2777 IF g_person_errored = FALSE THEN
2778 l_warning_text := substr(l_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2779 END IF;
2780 l_error := TRUE;
2781 g_person_errored := TRUE;
2782 --fnd_message.raise_error;
2783
2784 END IF;
2785 hr_maintain_proposal_api.insert_proposal_component
2786 (p_component_id => l_component_id
2787 , p_pay_proposal_id => l_pay_proposal_id
2788 , p_business_group_id => per_bg_rec.business_group_id
2789 , p_approved => 'Y'
2790 , p_component_reason => sal_rate_rec.component_reason
2791 , p_change_amount_n => l_sal_incr
2792 , p_object_version_number => l_component_ovn
2793 );
2794 EXCEPTION
2795 WHEN OTHERS THEN
2796 WRITE('Error in insert_proposal_component : '||SQLERRM);
2797 l_error := TRUE;
2798 l_message := fnd_message.get_encoded;
2799 fnd_message.set_encoded(l_message);
2800 --
2801 fnd_message.parse_encoded(encoded_message => l_message,
2802 app_short_name => l_app_name,
2803 message_name => l_message_name);
2804 IF g_person_errored = FALSE THEN
2805 l_warning_text := substr(l_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2806 END IF;
2807 g_person_errored := TRUE;
2808 END;
2809
2810 write_m('Time after inserting the salary proposal components '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2811 OPEN c_rate_ovn (p_group_per_in_ler_id, sal_rate_rec.pl_id, sal_rate_rec.oipl_id);
2812
2813 FETCH c_rate_ovn
2814 INTO l_rate_ovn;
2815
2816 CLOSE c_rate_ovn;
2817 write_m ('Time before updating the person rates with proposal and element entry ids '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2818 ben_cwb_person_rates_api.update_person_rate
2819 (p_group_per_in_ler_id => p_group_per_in_ler_id
2820 , p_pl_id => sal_rate_rec.pl_id
2821 , p_oipl_id => sal_rate_rec.oipl_id
2822 , p_pay_proposal_id => l_pay_proposal_id
2823 , p_comp_posting_date => l_effective_date
2824 , p_object_version_number => l_rate_ovn.object_version_number
2825 );
2826 write_m ('Time after updating the person rates with proposal and element entry ids '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2827 WRITE ('Pay proposal id and element entry id populated in to the table');
2828 END IF;
2829 END IF;
2830
2831
2832 l_count := l_count + 1;
2833 p_cache_cwb_rpt_person (l_count).person_rate_id := sal_rate_rec.person_rate_id;
2834 p_cache_cwb_rpt_person (l_count).pl_id := sal_rate_rec.pl_id;
2835 p_cache_cwb_rpt_person (l_count).oipl_id := sal_rate_rec.oipl_id;
2836 p_cache_cwb_rpt_person (l_count).group_pl_id := sal_rate_rec.group_pl_id;
2837 p_cache_cwb_rpt_person (l_count).group_oipl_id := sal_rate_rec.group_oipl_id;
2838 p_cache_cwb_rpt_person (l_count).full_name := sal_rate_rec.full_name;
2839 p_cache_cwb_rpt_person (l_count).emp_number := sal_rate_rec.employee_number;
2840 p_cache_cwb_rpt_person (l_count).business_group_id := sal_rate_rec.business_group_id;
2841 p_cache_cwb_rpt_person (l_count).ws_mgr_id := sal_rate_rec.ws_mgr_id;
2842 p_cache_cwb_rpt_person (l_count).units := null;
2843
2844 p_cache_cwb_rpt_person (l_count).base_salary_currency := per_bg_rec.base_salary_currency;
2845 p_cache_cwb_rpt_person (l_count).base_salary := per_bg_rec.base_salary;
2846 p_cache_cwb_rpt_person (l_count).elig_salary := round(sal_rate_rec.elig_sal_val,l_precision);
2847 p_cache_cwb_rpt_person (l_count).amount := round(nvl(sal_rate_rec.ws_val,0),l_precision);
2848 if(p_cache_cwb_rpt_person (l_count).elig_salary is null OR p_cache_cwb_rpt_person (l_count).elig_salary = 0 ) then
2849 p_cache_cwb_rpt_person (l_count).percent_of_elig_sal := 0;
2850 else
2851 p_cache_cwb_rpt_person (l_count).percent_of_elig_sal :=
2852 round((p_cache_cwb_rpt_person (l_count).amount/p_cache_cwb_rpt_person (l_count).elig_salary)*100,l_precision);
2853 end if;
2854 p_cache_cwb_rpt_person (l_count).base_sal_freq := per_bg_rec.base_salary_frequency;
2855 p_cache_cwb_rpt_person (l_count).pay_ann_factor := l_sal_factors.pay_annulization_factor;
2856 p_cache_cwb_rpt_person (l_count).pl_ann_factor := l_sal_factors.pl_annulization_factor;
2857 p_cache_cwb_rpt_person (l_count).conversion_factor :=
2858 round(l_sal_factors.pl_annulization_factor/l_sal_factors.pay_annulization_factor,
2859 l_precision);
2860 p_cache_cwb_rpt_person (l_count).adjusted_amount := round(nvl(l_sal_incr,0),l_precision);
2861 p_cache_cwb_rpt_person (l_count).prev_sal := round(nvl(asg_rec.proposed_salary_n,0),l_precision);
2862 p_cache_cwb_rpt_person (l_count).exchange_rate := sal_rate_rec.xchg_rate;
2863 p_cache_cwb_rpt_person (l_count).effective_date := l_effective_date;
2864 p_cache_cwb_rpt_person (l_count).reason := sal_rate_rec.component_reason;
2865 p_cache_cwb_rpt_person (l_count).eligibility := sal_rate_rec.elig_flag;
2866 p_cache_cwb_rpt_person (l_count).fte_factor := per_bg_rec.fte_factor;
2867
2868 p_cache_cwb_rpt_person (l_count).pay_proposal_id := l_pay_proposal_id;
2869 p_cache_cwb_rpt_person (l_count).pay_basis_id := asg_rec.pay_basis_id;
2870
2871 p_cache_cwb_rpt_person (l_count).assignment_id := asg_rec.assignment_id;
2872 p_cache_cwb_rpt_person (l_count).uom_precision := l_sal_factors.uom_precision;
2873 p_cache_cwb_rpt_person (l_count).ws_sub_acty_typ_cd := 'ICM7';
2874
2875 p_cache_cwb_rpt_person (l_count).group_per_in_ler_id := p_group_per_in_ler_id;
2876 p_cache_cwb_rpt_person (l_count).currency := sal_rate_rec.currency;
2877 p_cache_cwb_rpt_person (l_count).lf_evt_ocrd_date := p_lf_evt_ocrd_date;
2878
2879 if(l_error or p_warning) then --7218121
2880 p_cache_cwb_rpt_person (l_count).error_or_warning_text := substr(l_warning_text,1,2000);
2881 end if;
2882 END LOOP;
2883 write_m ('Time after looping salary components '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2884 CLOSE c_sal_comp_rates;
2885
2886 OPEN c_tot_chg_amt_for_proposal (l_pay_proposal_id);
2887 FETCH c_tot_chg_amt_for_proposal INTO tot_com_amt_rec;
2888 CLOSE c_tot_chg_amt_for_proposal;
2889
2890 IF (l_total IS NOT NULL AND (NOT l_error)) THEN
2891 write_m ('Time before updating the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2892 BEGIN
2893 hr_maintain_proposal_api.update_salary_proposal
2894 (p_pay_proposal_id => l_pay_proposal_id
2895 , p_object_version_number => l_object_version_number
2896 , p_proposed_salary_n => (l_prev_sal
2897 + tot_com_amt_rec.tamt)
2898 , p_approved => 'Y'
2899 , p_inv_next_sal_date_warning => l_dummy1
2900 , p_proposal_reason => l_sal_factors.salary_change_reason
2901 , p_proposed_salary_warning => l_dummy2
2902 , p_approved_warning => l_dummy3
2903 , p_payroll_warning => l_dummy4
2904 );
2905 write_m ('Time after updating the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
2906 EXCEPTION
2907 WHEN OTHERS THEN
2908 WRITE('Error in update_salary_proposal : '||SQLERRM);
2909 l_error := TRUE;
2910 l_message := fnd_message.get_encoded;
2911 fnd_message.set_encoded(l_message);
2912 --
2913 fnd_message.parse_encoded(encoded_message => l_message,
2914 app_short_name => l_app_name,
2915 message_name => l_message_name);
2916 IF g_person_errored = FALSE THEN
2917 l_warning_text := substr(l_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
2918 END IF;
2919 g_person_errored := TRUE;
2920 END;
2921 END IF;
2922
2923 p_pay_proposal_id := l_pay_proposal_id;
2924 p_pay_basis_id := l_pay_basis_id;
2925
2926 IF(l_error) THEN
2927 RAISE ben_batch_utils.g_record_error;
2928 WRITE('Raising error in process_sal_comp_rates');
2929 END IF;
2930
2931 END;
2932
2933 PROCEDURE process_sal_rate (
2934 p_effective_date IN DATE
2935 , p_lf_evt_ocrd_date IN DATE
2936 , p_group_pl_id IN NUMBER
2937 , p_group_per_in_ler_id IN NUMBER
2938 , p_person_id IN NUMBER
2939 , p_pl_id IN NUMBER
2940 , p_oipl_id IN NUMBER
2941 , p_ws_abr_id IN NUMBER
2942 , p_ws_val IN NUMBER
2943 , p_currency IN VARCHAR2
2944 , p_nip_acty_ref_perd_cd IN VARCHAR2
2945 , p_business_group_id IN NUMBER
2946 , p_object_version_number IN NUMBER
2947 , p_salary_change_reason IN VARCHAR2
2948 , p_debug_level IN VARCHAR2 DEFAULT NULL
2949 , p_precision IN NUMBER
2950 , p_warning_text OUT NOCOPY VARCHAR2
2951 , p_prev_sal OUT NOCOPY VARCHAR2
2952 , p_pay_proposal_id OUT NOCOPY NUMBER
2953 , p_pay_basis_id OUT NOCOPY NUMBER
2954 , p_warning OUT NOCOPY BOOLEAN
2955 )
2956 IS
2957 asg_rec c_prev_pay_proposal%ROWTYPE;
2958 elm_rec c_element_entry%ROWTYPE;
2959 l_person_info c_person_info%ROWTYPE;
2960 l_rate_ovn c_rate_ovn%ROWTYPE;
2961 l_pay_proposal_id NUMBER;
2962 l_sal_incr NUMBER;
2963 l_ele_ent_id NUMBER;
2964 l_prev_sal NUMBER;
2965 l_object_version_number NUMBER;
2966 l_dummy1 BOOLEAN;
2967 l_dummy2 BOOLEAN;
2968 l_dummy3 BOOLEAN;
2969 l_dummy4 BOOLEAN;
2970 l_error BOOLEAN;
2971 l_message VARCHAR2 (600);
2972 l_message_name VARCHAR2 (240);
2973 l_app_name VARCHAR2 (240);
2974 future_pay_proposal_rec c_future_pay_proposal%ROWTYPE;
2975 l_element_input_currency VARCHAR2 (30);
2976 BEGIN
2977 g_actn := 'Preparing to call sal admin api ...';
2978 g_proc := 'process_sal_rate';
2979 WRITE (g_actn);
2980 l_error := FALSE;
2981 p_warning := FALSE;
2982 p_warning_text := null;
2983
2984 OPEN c_prev_pay_proposal (p_group_per_in_ler_id, p_effective_date);
2985
2986 FETCH c_prev_pay_proposal
2987 INTO asg_rec;
2988
2989 CLOSE c_prev_pay_proposal;
2990
2991 OPEN c_person_info (p_group_per_in_ler_id);
2992
2993 FETCH c_person_info
2994 INTO l_person_info;
2995
2996 CLOSE c_person_info;
2997
2998 IF (asg_rec.pay_basis_id IS NOT NULL)
2999 THEN
3000 WRITE ('Found salary basis for the person...');
3001 write_h ('Pay basis id for the person is ' || asg_rec.pay_basis_id);
3002 p_pay_basis_id := asg_rec.pay_basis_id;
3003
3004 OPEN c_element_entry (asg_rec.pay_basis_id, asg_rec.assignment_id, p_effective_date);
3005
3006 FETCH c_element_entry
3007 INTO elm_rec;
3008 l_prev_sal := asg_rec.proposed_salary_n;
3009 write_s ('Previous salary is ' || l_prev_sal);
3010
3011 IF c_element_entry%FOUND
3012 THEN
3013 l_ele_ent_id := elm_rec.element_entry_id;
3014 write_h ('Element entry found ' || l_ele_ent_id);
3015 ELSE
3016 l_ele_ent_id := NULL;
3017 write_h ('Element entry not found...');
3018 END IF;
3019 CLOSE c_element_entry;
3020
3021
3022 ELSE
3023 g_actn :=
3024 'The salary is not posted as no pay basis is defined.';
3025 WRITE (g_actn);
3026 fnd_message.set_name ('BEN', 'BEN_94674_CWB_NO_PAY_BASIS');
3027 l_message := fnd_message.get_encoded;
3028 fnd_message.set_encoded(l_message);
3029 --
3030 fnd_message.parse_encoded(encoded_message => l_message,
3031 app_short_name => l_app_name,
3032 message_name => l_message_name);
3033 IF g_person_errored = FALSE THEN
3034 p_warning_text := p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get;
3035 END IF;
3036 l_error := TRUE;
3037 g_person_errored := TRUE;
3038 END IF;
3039
3040 WRITE ('Checking for salary proposal changes...');
3041
3042 write_h(asg_rec.proposed_salary_n);
3043 write_h(l_person_info.base_salary);
3044 write_h(p_precision);
3045
3046 IF (round(asg_rec.proposed_salary_n,p_precision) <>
3047 round(l_person_info.base_salary,p_precision))
3048 THEN
3049 g_actn :=
3050 'This employee had a recent update to Base Salary '
3051 || 'or Pay Basis. The new salary could not be posted.'
3052 || ' Either delete the recent update and rerun this process,'
3053 || 'or apply the new salary manually. ';
3054 WRITE (g_actn);
3055 write_m('Salary in HR '||asg_rec.proposed_salary_n);
3056 write_m('Salary in CWB '||l_person_info.base_salary);
3057 fnd_message.set_name ('BEN', 'BEN_91141_CWB_RECENT_SAL_CHG');
3058 l_message := fnd_message.get_encoded;
3059 fnd_message.set_encoded(l_message);
3060 --
3061 fnd_message.parse_encoded(encoded_message => l_message,
3062 app_short_name => l_app_name,
3063 message_name => l_message_name);
3064 IF g_person_errored = FALSE THEN
3065 p_warning_text := p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get;
3066 END IF;
3067 l_error := TRUE;
3068 g_person_errored := TRUE;
3069 --fnd_message.raise_error;
3070 END IF;
3071 IF(NOT l_error) THEN
3072 l_prev_sal := asg_rec.proposed_salary_n;
3073 g_actn := 'Inserting salary proposal for non_comp_salary';
3074 WRITE (g_actn);
3075 write_h ('==============Inserting Salary proposal========');
3076 write_h ('||Parameter Description ');
3077 write_h ('||p_assignment_id - ' || asg_rec.assignment_id);
3078 write_h ('||p_business_group_id - ' || p_business_group_id);
3079 write_h ('||p_change_date - ' || p_effective_date);
3080 write_h ('||p_proposal_reason - ' || p_salary_change_reason);
3081 write_h ('||p_proposed_salary increase - ' || p_ws_val);
3082 write_h ('================================================');
3083 write_m ('Time before inserting the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3084 BEGIN
3085 OPEN c_element_input_currency(elm_rec.element_type_id,p_effective_date);
3086 FETCH c_element_input_currency INTO l_element_input_currency;
3087 CLOSE c_element_input_currency;
3088
3089 IF(p_currency is not null
3090 AND l_element_input_currency <> p_currency) THEN
3091 WRITE('Currency in CWB does not match Input Currency of the Element Type');
3092
3093 write_m('Currency for Element '||l_element_input_currency);
3094 write_m('Currency in CWB '||p_currency);
3095 fnd_message.set_name ('BEN', 'BEN_94673_EL_CURR_MISMATCH');
3096 l_message := fnd_message.get_encoded;
3097 fnd_message.set_encoded(l_message);
3098 --
3099 fnd_message.parse_encoded(encoded_message => l_message,
3100 app_short_name => l_app_name,
3101 message_name => l_message_name);
3102 IF g_person_errored = FALSE THEN
3103 p_warning_text := p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get;
3104 END IF;
3105 l_error := TRUE;
3106 g_person_errored := TRUE;
3107 END IF;
3108 hr_maintain_proposal_api.insert_salary_proposal
3109 (p_pay_proposal_id => l_pay_proposal_id
3110 , p_assignment_id => asg_rec.assignment_id
3111 , p_business_group_id => p_business_group_id
3112 , p_change_date => p_effective_date
3113 , p_proposal_reason => p_salary_change_reason
3114 , p_proposed_salary_n => (l_prev_sal
3115 + p_ws_val
3116 --l_sal_incr --p_ws_val
3117 )
3118 , p_object_version_number => l_object_version_number
3119 , p_multiple_components => 'N'
3120 , p_approved => 'Y'
3121 , p_validate => FALSE
3122 , p_element_entry_id => l_ele_ent_id
3123 , p_inv_next_sal_date_warning => l_dummy1
3124 , p_proposed_salary_warning => l_dummy2
3125 , p_approved_warning => l_dummy3
3126 , p_payroll_warning => l_dummy4
3127 );
3128 OPEN c_future_pay_proposal (p_group_per_in_ler_id, p_effective_date);
3129
3130 FETCH c_future_pay_proposal
3131 INTO future_pay_proposal_rec;
3132 CLOSE c_future_pay_proposal;
3133
3134 IF(future_pay_proposal_rec.proposed_salary_n is not null) THEN
3135 p_warning := TRUE;
3136 fnd_message.set_name ('BEN', 'BEN_94685_FUTURE_SAL_PROP_WARN');
3137 l_message := fnd_message.get_encoded;
3138 fnd_message.set_encoded(l_message);
3139 --
3140 fnd_message.parse_encoded(encoded_message => l_message,
3141 app_short_name => l_app_name,
3142 message_name => l_message_name);
3143 IF g_person_errored = FALSE THEN
3144 p_warning_text := p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get;
3145 END IF;
3146 g_person_errored := TRUE;
3147 --p_warning_text := p_warning_text||'Future dated salary proposal exists';
3148 END IF;
3149 p_prev_sal := l_prev_sal;
3150 p_pay_proposal_id := l_pay_proposal_id;
3151 EXCEPTION
3152 WHEN OTHERS THEN
3153 p_prev_sal := l_prev_sal;
3154 p_pay_proposal_id := l_pay_proposal_id;
3155 WRITE('Exception at insert_salary_proposal : '||SQLERRM);
3156 l_error := TRUE; l_message := fnd_message.get_encoded;
3157 fnd_message.set_encoded(l_message);
3158 --
3159 fnd_message.parse_encoded(encoded_message => l_message,
3160 app_short_name => l_app_name,
3161 message_name => l_message_name);
3162 IF g_person_errored = FALSE THEN
3163 p_warning_text := p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get;
3164 END IF;
3165 g_person_errored := TRUE;
3166 --p_warning_text := p_warning_text||SQLERRM;
3167 END;
3168 write_m ('Time after inserting the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3169 write_h ('Pay Proposal_id is ' || l_pay_proposal_id);
3170 write_h ('p_element_entry_id is ' || l_ele_ent_id);
3171 g_actn := 'updating pay proposal and element entry into person rates...';
3172 WRITE (g_actn);
3173
3174 OPEN c_rate_ovn (p_group_per_in_ler_id, p_pl_id, p_oipl_id);
3175
3176 FETCH c_rate_ovn
3177 INTO l_rate_ovn;
3178
3179 CLOSE c_rate_ovn;
3180 write_m ('Time before updating person rates '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3181 ben_cwb_person_rates_api.update_person_rate
3182 (p_group_per_in_ler_id => p_group_per_in_ler_id
3183 , p_pl_id => p_pl_id
3184 , p_oipl_id => p_oipl_id
3185 --, p_element_entry_value_id => l_ele_ent_id
3186 , p_pay_proposal_id => l_pay_proposal_id
3187 , p_object_version_number => l_rate_ovn.object_version_number
3188 );
3189 write_m ('Time after updating person rates '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3190 WRITE ('Pay proposal id and element entry id populated in to the table');
3191 END IF;
3192
3193 IF(l_error) THEN
3194 RAISE ben_batch_utils.g_record_error;
3195 END IF;
3196
3197 END;
3198
3199 PROCEDURE process_non_sal_rate (
3200 p_effective_date IN DATE
3201 , p_lf_evt_ocrd_date IN DATE
3202 , p_group_pl_id IN NUMBER
3203 , p_group_per_in_ler_id IN NUMBER
3204 , p_person_id IN NUMBER
3205 , p_pl_id IN NUMBER
3206 , p_oipl_id IN NUMBER
3207 , p_ws_abr_id IN NUMBER
3208 , p_ws_val IN NUMBER
3209 , p_nip_acty_ref_perd_cd IN VARCHAR2
3210 , p_business_group_id IN NUMBER
3211 , p_object_version_number IN NUMBER
3212 , p_debug_level IN VARCHAR2 DEFAULT NULL
3213 , p_input_value_id IN OUT NOCOPY NUMBER
3214 , p_element_type_id IN OUT NOCOPY NUMBER
3215 , p_warning_text IN OUT NOCOPY VARCHAR2
3216 , p_element_entry_value_id OUT NOCOPY NUMBER
3217 , p_eev_screen_entry_value OUT NOCOPY NUMBER
3218 )
3219 IS
3220 l_ovn NUMBER;
3221 l_rate_ovn c_rate_ovn%ROWTYPE;
3222 l_object_version_number NUMBER := p_object_version_number;
3223 l_error BOOLEAN;
3224 l_emp_num_and_emp_name c_emp_num_and_emp_name%ROWTYPE;
3225 l_message VARCHAR2 (600);
3226 l_message_name VARCHAR2 (240);
3227 l_app_name VARCHAR2 (240);
3228 BEGIN
3229 g_actn := 'calling element entry ...';
3230 g_proc := 'process_non_sal_rate';
3231 WRITE (g_actn);
3232
3233 IF(p_warning_text is not null) THEN
3234 l_error := TRUE;
3235 ELSE
3236 l_error := FALSE;
3237 END IF;
3238
3239 write_h ('=====================Element Entry ==========================');
3240 write_h ('||Person Id ' || p_person_id);
3241 write_h ('||p_ws_abr_id ' || p_ws_abr_id);
3242 write_h ('||p_nip_acty_ref_perd_cd ' || p_nip_acty_ref_perd_cd);
3243 write_h ('||p_effective_date ' || p_effective_date);
3244 write_h ('||p_ws_val ' || p_ws_val);
3245 write_h ('||p_pl_id ' || p_pl_id);
3246 write_h ('||l_object_version_number ' || l_object_version_number);
3247 write_h ('||p_business_group_id ' || p_business_group_id);
3248 write_h ('||l_element_entry_value_id ' || p_element_entry_value_id);
3249 write_h ('||l_eev_screen_entry_value ' || p_eev_screen_entry_value);
3250 write_h ('||l_element_type_id ' || p_element_type_id);
3251 write_h ('||l_input_value_id ' || p_input_value_id);
3252 write_h ('================================================================');
3253 BEGIN
3254 IF(NOT l_error) THEN
3255 ben_element_entry.create_enrollment_element
3256 (p_person_id => p_person_id
3257 , p_acty_base_rt_id => p_ws_abr_id
3258 , p_acty_ref_perd => p_nip_acty_ref_perd_cd
3259 , p_rt_start_date => p_effective_date
3260 , p_rt => p_ws_val
3261 , p_pl_id => p_pl_id
3262 , p_prv_object_version_number => l_object_version_number
3263 , p_business_group_id => p_business_group_id
3264 , p_effective_date => p_effective_date
3265 , p_element_entry_value_id => p_element_entry_value_id
3266 , p_eev_screen_entry_value => p_eev_screen_entry_value
3267 , p_input_value_id => p_input_value_id
3268 , p_element_type_id => p_element_type_id
3269 );
3270 END IF;
3271 EXCEPTION
3272 WHEN OTHERS THEN
3273 WRITE('Exception at create_enrollment_element : '||SQLERRM);
3274 l_error := TRUE;
3275 l_message := fnd_message.get_encoded;
3276 fnd_message.set_encoded(l_message);
3277 --
3278 fnd_message.parse_encoded(encoded_message => l_message,
3279 app_short_name => l_app_name,
3280 message_name => l_message_name);
3281 IF g_person_errored = FALSE THEN
3282 p_warning_text := p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get;
3283 END IF;
3284 g_person_errored := TRUE;
3285 END;
3286 g_actn := 'updating the element entry id into person rates ...';
3287 WRITE (g_actn);
3288
3289 OPEN c_rate_ovn (p_group_per_in_ler_id, p_pl_id, p_oipl_id);
3290
3291 FETCH c_rate_ovn
3292 INTO l_rate_ovn;
3293
3294 CLOSE c_rate_ovn;
3295 BEGIN
3296 IF(NOT l_error) THEN
3297 ben_cwb_person_rates_api.update_person_rate
3298 (p_group_per_in_ler_id => p_group_per_in_ler_id
3299 , p_pl_id => p_pl_id
3300 , p_oipl_id => p_oipl_id
3301 , p_element_entry_value_id => p_element_entry_value_id
3302 , p_object_version_number => l_rate_ovn.object_version_number
3303 );
3304 END IF;
3305 EXCEPTION
3306 WHEN OTHERS THEN
3307 WRITE('Exception at update_person_rate : '||SQLERRM);
3308 l_error := TRUE;
3309 l_message := fnd_message.get_encoded;
3310 fnd_message.set_encoded(l_message);
3311 --
3312 fnd_message.parse_encoded(encoded_message => l_message,
3313 app_short_name => l_app_name,
3314 message_name => l_message_name);
3315 IF g_person_errored = FALSE THEN
3316 p_warning_text := p_warning_text||fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get;
3317 END IF;
3318 g_person_errored := TRUE;
3319 END;
3320
3321 write_h ('l_element_entry_value_id ' || p_element_entry_value_id);
3322 write_h ('l_eev_screen_entry_value ' || p_eev_screen_entry_value);
3323 write_h ('l_element_type_id ' || p_element_type_id);
3324 write_h ('l_input_value_id ' || p_input_value_id);
3325
3326 IF(l_error) THEN
3327 RAISE ben_batch_utils.g_record_error;
3328 END IF;
3329 END;
3330
3331 PROCEDURE compensation_object (
3332 p_group_per_in_ler_id IN NUMBER
3333 , p_person_id IN NUMBER
3334 , p_effective_date IN DATE
3335 , p_group_pl_id IN NUMBER
3336 , p_lf_evt_ocrd_date IN DATE
3337 , p_cache_cwb_rpt_person IN OUT NOCOPY g_cache_cwb_rpt_person_rec
3338 , p_cwb_rpt_person_rec IN OUT NOCOPY g_cwb_rpt_person_rec
3339 , p_grant_price_val IN NUMBER DEFAULT NULL
3340 , p_audit_log IN VARCHAR2 DEFAULT 'N'
3341 , p_debug_level IN VARCHAR2 DEFAULT NULL
3342 , p_process_sal_comp IN VARCHAR2 DEFAULT 'N'
3343 , p_use_rate_start_date IN VARCHAR2 DEFAULT 'N'
3344 , p_pay_proposal_id OUT NOCOPY NUMBER
3345 , p_element_entry_value_id OUT NOCOPY NUMBER
3346 , p_warning OUT NOCOPY BOOLEAN
3347 )
3348 IS
3349 l_rate_ovn c_rate_ovn%ROWTYPE;
3350 l_sal_factors c_sal_factors%ROWTYPE;
3351 l_precision NUMBER;
3352 l_amount NUMBER := NULL;
3353 l_count NUMBER := 0;
3354 l_warning VARCHAR2 (2000);
3355 l_override_start_date DATE;
3356 l_effective_date DATE;
3357 l_prev_sal NUMBER;
3358 l_pay_proposal_id NUMBER;
3359 l_error BOOLEAN;
3360 l_pay_basis_id NUMBER;
3361 l_element_entry_value_id NUMBER;
3362 l_input_value_id NUMBER;
3363 l_element_type_id NUMBER;
3364 l_eev_screen_entry_value NUMBER;
3365 l_element_input_value VARCHAR2 (2000);
3366 l_processing_type VARCHAR2 (30);
3367 l_currency_cd VARCHAR2 (30);
3368 l_input_currency VARCHAR2 (30);
3369 l_message VARCHAR2 (600);
3370 l_message_name VARCHAR2 (240);
3371 l_app_name VARCHAR2 (240);
3372 asg_rec c_prev_pay_proposal%ROWTYPE;
3373 BEGIN
3374 g_proc := 'compensation_object';
3375
3376 l_error := FALSE;
3377 p_warning := FALSE;
3378 p_pay_proposal_id := null;
3379 p_element_entry_value_id := null;
3380
3381 IF p_process_sal_comp = 'Y' THEN
3382 g_actn := 'Processing salary components...';
3383 WRITE (g_actn);
3384 BEGIN
3385 write_h ('Time before calling process_sal_comp_rates '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3386 process_sal_comp_rates (p_effective_date => p_effective_date
3387 , p_lf_evt_ocrd_date => p_lf_evt_ocrd_date
3388 , p_group_pl_id => p_group_pl_id
3389 , p_person_id => p_person_id
3390 , p_cache_cwb_rpt_person => p_cache_cwb_rpt_person
3391 , p_cwb_rpt_person_rec => p_cwb_rpt_person_rec
3392 , p_group_per_in_ler_id => p_group_per_in_ler_id
3393 , p_debug_level => p_debug_level
3394 , p_pay_proposal_id => p_pay_proposal_id
3395 , p_pay_basis_id => l_pay_basis_id
3396 , p_warning => p_warning
3397 , p_use_rate_start_date => p_use_rate_start_date
3398 );
3399
3400 write_h ('Time after calling process_sal_comp_rates '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3401 EXCEPTION
3402 WHEN OTHERS THEN
3403 WRITE('Error in process_sal_comp_rates : '||SQLERRM);
3404 l_error := TRUE;
3405 END;
3406 END IF;
3407
3408 g_actn := 'Processing non component rates...';
3409 write_h ('Time before processing non component rates '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3410 WRITE (g_actn);
3411 l_count := p_cache_cwb_rpt_person.COUNT;
3412
3413 for rt_rec in c_non_sal_comp_rates (p_group_per_in_ler_id, p_effective_date) loop
3414
3415 p_cwb_rpt_person_rec.adjusted_amount := null;
3416 p_cwb_rpt_person_rec.amount := null;
3417 p_cwb_rpt_person_rec.amount_posted := null;
3418 p_cwb_rpt_person_rec.assignment_changed := null;
3419 p_cwb_rpt_person_rec.assignment_id := null;
3420 p_cwb_rpt_person_rec.base_sal_freq := null;
3421 p_cwb_rpt_person_rec.base_salary := null;
3422 p_cwb_rpt_person_rec.base_salary_currency := null;
3423 p_cwb_rpt_person_rec.benefit_action_id := null;
3424 p_cwb_rpt_person_rec.business_group_id := null;
3425 p_cwb_rpt_person_rec.business_group_name := null;
3426 p_cwb_rpt_person_rec.conversion_factor := null;
3427 p_cwb_rpt_person_rec.country_code := null;
3428 p_cwb_rpt_person_rec.currency := null;
3429 p_cwb_rpt_person_rec.eev_screen_entry_value:= null;
3430 p_cwb_rpt_person_rec.effective_date := null;
3431 p_cwb_rpt_person_rec.element_entry_id := null;
3432 p_cwb_rpt_person_rec.element_entry_value_id:= null;
3433 p_cwb_rpt_person_rec.element_input_value := null;
3434 p_cwb_rpt_person_rec.element_type_id := null;
3435 p_cwb_rpt_person_rec.elig_salary := null;
3436 p_cwb_rpt_person_rec.eligibility := null;
3437 p_cwb_rpt_person_rec.elmnt_processing_type := null;
3438 p_cwb_rpt_person_rec.emp_number := null;
3439 p_cwb_rpt_person_rec.error_or_warning_text := null;
3440 p_cwb_rpt_person_rec.exchange_rate := null;
3441 p_cwb_rpt_person_rec.fte_factor := null;
3442 p_cwb_rpt_person_rec.full_name := null;
3443 p_cwb_rpt_person_rec.group_oipl_id := null;
3444 p_cwb_rpt_person_rec.group_per_in_ler_id := null;
3445 p_cwb_rpt_person_rec.group_pl_id := null;
3446 p_cwb_rpt_person_rec.input_value_id := null;
3447 p_cwb_rpt_person_rec.lf_evt_closed := null;
3448 p_cwb_rpt_person_rec.lf_evt_ocrd_date := null;
3449 p_cwb_rpt_person_rec.manager_name := null;
3450 p_cwb_rpt_person_rec.new_sal := null;
3451 p_cwb_rpt_person_rec.oipl_id := null;
3452 p_cwb_rpt_person_rec.opt_name := null;
3453 p_cwb_rpt_person_rec.pay_ann_factor := null;
3454 p_cwb_rpt_person_rec.pay_basis_id := null;
3455 p_cwb_rpt_person_rec.pay_proposal_id := null;
3456 p_cwb_rpt_person_rec.percent_of_elig_sal := null;
3457 p_cwb_rpt_person_rec.performance_rating := null;
3458 p_cwb_rpt_person_rec.person_id := null;
3459 p_cwb_rpt_person_rec.pl_ann_factor := null;
3460 p_cwb_rpt_person_rec.pl_id := null;
3461 p_cwb_rpt_person_rec.pl_name := null;
3462 p_cwb_rpt_person_rec.prev_eev_screen_entry_value := null;
3463 p_cwb_rpt_person_rec.prev_sal := null;
3464 p_cwb_rpt_person_rec.rating_date := null;
3465 p_cwb_rpt_person_rec.reason := null;
3466 p_cwb_rpt_person_rec.status := null;
3467 p_cwb_rpt_person_rec.units := null;
3468 p_cwb_rpt_person_rec.uom_precision := null;
3469 p_cwb_rpt_person_rec.ws_mgr_id := null;
3470 p_cwb_rpt_person_rec.ws_sub_acty_typ_cd := null;
3471 l_prev_sal := null;
3472 l_pay_proposal_id := null;
3473 l_pay_basis_id := null;
3474 l_element_entry_value_id := null;
3475 l_input_value_id := null;
3476 l_element_type_id := null;
3477 l_eev_screen_entry_value := null;
3478 l_element_input_value := 'No Element specified';
3479 l_processing_type := null;
3480 l_currency_cd := null;
3481 l_input_currency := null;
3482
3483 write_h ('The compensation type is ' || rt_rec.ws_sub_acty_typ_cd);
3484 l_amount := rt_rec.ws_val;
3485
3486 -- anniversary date change
3487 if rt_rec.component_reason is null then
3488 if(p_use_rate_start_date = 'Y' and rt_rec.ws_abr_id is not null) then
3489 l_override_start_date := get_ws_rate_start_dt(
3490 p_group_per_in_ler_id => p_group_per_in_ler_id,
3491 p_group_pl_id => rt_rec.group_pl_id,
3492 p_pl_id => rt_rec.pl_id,
3493 p_oipl_id => rt_rec.oipl_id,
3494 p_group_oipl_id => rt_rec.group_oipl_id,
3495 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_date);
3496 if(l_override_start_date is null) then
3497 l_error := TRUE;
3498 fnd_message.set_name ('BEN', 'BEN_94906_CWB_NO_RATE_STRT_DT');
3499 l_message := fnd_message.get_encoded;
3500 fnd_message.set_encoded(l_message);
3501 fnd_message.parse_encoded(encoded_message => l_message,
3502 app_short_name => l_app_name,
3503 message_name => l_message_name);
3504 IF g_person_errored = FALSE THEN
3505 l_warning := substr(fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
3506 END IF;
3507 g_person_errored := TRUE;
3508 l_effective_date := null;
3509 else
3510 write_h('Found ws rate start date: '||l_override_start_date);
3511 l_effective_date := l_override_start_date;
3512 end if;
3513 else
3514 l_override_start_date := get_override_start_date(
3515 p_lf_evt_ocrd_date => p_lf_evt_ocrd_date
3516 ,p_group_pl_id => rt_rec.group_pl_id
3517 ,p_pl_id => rt_rec.pl_id
3518 ,p_group_oipl_id => rt_rec.group_oipl_id
3519 ,p_oipl_id => rt_rec.oipl_id
3520 ,p_effective_date => p_effective_date
3521 );
3522 IF(l_override_start_date is not null) THEN
3523 l_effective_date := l_override_start_date;
3524 ELSE
3525 l_effective_date := p_effective_date;
3526 END IF;
3527 end if;
3528
3529 WRITE('Posting date : ' || l_effective_date); p_cwb_rpt_person_rec.person_rate_id := rt_rec.person_rate_id;
3530 p_cwb_rpt_person_rec.pl_id := rt_rec.pl_id;
3531 p_cwb_rpt_person_rec.oipl_id := rt_rec.oipl_id;
3532 p_cwb_rpt_person_rec.group_pl_id := rt_rec.group_pl_id;
3533 p_cwb_rpt_person_rec.group_oipl_id := rt_rec.group_oipl_id;
3534 p_cwb_rpt_person_rec.full_name := rt_rec.full_name;
3535 p_cwb_rpt_person_rec.emp_number := rt_rec.employee_number;
3536 p_cwb_rpt_person_rec.business_group_id := rt_rec.business_group_id;
3537 p_cwb_rpt_person_rec.units := rt_rec.units;
3538 p_cwb_rpt_person_rec.ws_mgr_id := rt_rec.ws_mgr_id;
3539
3540 p_cwb_rpt_person_rec.base_salary_currency := rt_rec.base_salary_currency;
3541 p_cwb_rpt_person_rec.base_salary := rt_rec.base_salary;
3542 p_cwb_rpt_person_rec.elig_salary := round(rt_rec.elig_sal_val,rt_rec.uom_precision);
3543 p_cwb_rpt_person_rec.amount := round(nvl(rt_rec.ws_val,0),rt_rec.uom_precision);
3544
3545 IF(p_cwb_rpt_person_rec.elig_salary is null OR p_cwb_rpt_person_rec.elig_salary = 0 ) THEN
3546 p_cwb_rpt_person_rec.percent_of_elig_sal := 0;
3547 ELSE
3548 p_cwb_rpt_person_rec.percent_of_elig_sal :=
3549 round((p_cwb_rpt_person_rec.amount/p_cwb_rpt_person_rec.elig_salary)*100,rt_rec.uom_precision);
3550 END IF;
3551
3552 p_cwb_rpt_person_rec.base_sal_freq := rt_rec.base_salary_frequency;
3553 p_cwb_rpt_person_rec.pay_ann_factor := rt_rec.pay_annulization_factor;
3554 p_cwb_rpt_person_rec.pl_ann_factor := rt_rec.pl_annulization_factor;
3555 p_cwb_rpt_person_rec.exchange_rate := rt_rec.xchg_rate;
3556 p_cwb_rpt_person_rec.effective_date := l_effective_date;
3557 p_cwb_rpt_person_rec.reason := rt_rec.component_reason;
3558 p_cwb_rpt_person_rec.eligibility := rt_rec.elig_flag;
3559 p_cwb_rpt_person_rec.fte_factor := rt_rec.fte_factor;
3560
3561 IF(rt_rec.ws_sub_acty_typ_cd='ICM7') THEN
3562 p_cwb_rpt_person_rec.conversion_factor :=
3563 round(rt_rec.pl_annulization_factor/rt_rec.pay_annulization_factor,
3564 6);
3565 ELSE
3566 p_cwb_rpt_person_rec.conversion_factor := 1;
3567 BEGIN
3568 WRITE_H(rt_rec.ws_abr_id||' '||l_effective_date||' '||rt_rec.assignment_id||' '||rt_rec.business_group_id);
3569 WRITE_H(rt_rec.pl_id||' '||p_group_per_in_ler_id);
3570 if(rt_rec.ws_abr_id is not null and l_effective_date is not null) then
3571 WRITE_H('Calling element determintaion with fol data');
3572 ben_manage_cwb_life_events.exec_element_det_rl
3573 (p_acty_base_rt_id => rt_rec.ws_abr_id,
3574 p_effective_date => l_effective_date,
3575 p_assignment_id => rt_rec.assignment_id,
3576 p_organization_id => null,
3577 p_business_group_id => rt_rec.business_group_id,
3578 p_pl_id => rt_rec.pl_id,
3579 p_ler_id => p_group_per_in_ler_id,
3580 p_element_type_id => l_element_type_id,
3581 p_input_value_id => l_input_value_id,
3582 p_currency_cd => l_currency_cd);
3583 WRITE('Rule called and returned input_value_id: '||l_input_value_id||' and element_type_id: '||l_element_type_id);
3584 end if;
3585 EXCEPTION
3586 WHEN others THEN
3587 WRITE('No Element Rule Found ');
3588 --WRITE(SQLERRM);
3589 fnd_message.CLEAR();
3590 END;
3591 if(l_element_type_id is null and l_input_value_id is null) then
3592 get_plan_abr_info(
3593 p_lf_evt_ocrd_date => p_lf_evt_ocrd_date
3594 , p_pl_id => rt_rec.pl_id
3595 , p_oipl_id => rt_rec.oipl_id
3596 , p_element_type_id => l_element_type_id
3597 , p_input_value_id => l_input_value_id
3598 );
3599 end if;
3600 p_cwb_rpt_person_rec.input_value_id := l_input_value_id;
3601 p_cwb_rpt_person_rec.element_type_id := l_element_type_id;
3602 --p_cwb_rpt_person_rec.eev_screen_entry_value := l_eev_screen_entry_value;
3603 l_currency_cd := rt_rec.currency;
3604
3605 OPEN c_element_input_value_name(l_input_value_id,l_element_type_id,l_effective_date);
3606 FETCH c_element_input_value_name INTO l_element_input_value,l_processing_type,l_input_currency;
3607 CLOSE c_element_input_value_name;
3608 p_cwb_rpt_person_rec.element_input_value := SUBSTR(l_element_input_value,1,80); --sg
3609 p_cwb_rpt_person_rec.elmnt_processing_type := l_processing_type;
3610 END IF;
3611
3612 p_cwb_rpt_person_rec.adjusted_amount := p_cwb_rpt_person_rec.amount;
3613 p_cwb_rpt_person_rec.assignment_id := rt_rec.assignment_id;
3614 p_cwb_rpt_person_rec.uom_precision := rt_rec.uom_precision;
3615 p_cwb_rpt_person_rec.ws_sub_acty_typ_cd := rt_rec.ws_sub_acty_typ_cd;
3616 p_cwb_rpt_person_rec.currency := rt_rec.currency;
3617 p_cwb_rpt_person_rec.lf_evt_ocrd_date := p_lf_evt_ocrd_date;
3618 p_cwb_rpt_person_rec.group_per_in_ler_id := p_group_per_in_ler_id;
3619
3620 p_cwb_rpt_person_rec.pay_proposal_id := null;
3621 p_cwb_rpt_person_rec.pay_basis_id := null;
3622
3623 IF (rt_rec.ws_val IS NOT NULL AND
3624 rt_rec.ws_val <> 0 AND rt_rec.elig_flag = 'Y') THEN
3625 WRITE ('Valid worksheet amount found for processing ...');
3626 --p_cwb_rpt_person_rec.amount := l_amount;
3627
3628 IF rt_rec.ws_sub_acty_typ_cd = 'ICM7' THEN
3629 g_actn := 'Processing salary without components...';
3630 WRITE (g_actn);
3631
3632 OPEN c_sal_factors(p_group_pl_id,p_lf_evt_ocrd_date,p_group_per_in_ler_id);
3633 FETCH c_sal_factors INTO l_sal_factors;
3634 CLOSE c_sal_factors;
3635 OPEN c_input_value_precision(rt_rec.assignment_id,l_effective_date);
3636 FETCH c_input_value_precision INTO l_precision;
3637 CLOSE c_input_value_precision;
3638 IF(l_precision IS NULL) THEN
3639 l_precision := l_sal_factors.uom_precision;
3640 END IF;
3641 l_amount := round(rt_rec.ws_val * l_sal_factors.pl_annulization_factor/l_sal_factors.pay_annulization_factor,l_precision);
3642
3643 if p_process_sal_comp = 'N' then
3644 g_actn := 'Processing salary ...';
3645 WRITE (g_actn);
3646
3647 write_h('l_sal_factors.pl_annulization_factor is '||l_sal_factors.pl_annulization_factor);
3648 write_h('l_sal_factors.pay_annulization_factor is '||l_sal_factors.pay_annulization_factor);
3649 write_h('l_precision is '||l_precision);
3650 write_s ('The amount ' || rt_rec.ws_val || ' Converted amount ' || l_amount);
3651
3652 write_m ('Time before calling process_sal_rate '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3653 BEGIN
3654 process_sal_rate (p_effective_date => l_effective_date
3655 , p_lf_evt_ocrd_date => p_lf_evt_ocrd_date
3656 , p_group_pl_id => p_group_pl_id
3657 , p_person_id => p_person_id
3658 , p_pl_id => rt_rec.pl_id
3659 , p_oipl_id => rt_rec.oipl_id
3660 , p_ws_abr_id => rt_rec.ws_abr_id
3661 , p_ws_val => l_amount
3662 , p_currency => rt_rec.currency
3663 , p_salary_change_reason => l_sal_factors.salary_change_reason
3664 , p_nip_acty_ref_perd_cd => rt_rec.acty_ref_perd_cd
3665 , p_business_group_id => rt_rec.business_group_id
3666 , p_object_version_number => rt_rec.object_version_number
3667 , p_group_per_in_ler_id => p_group_per_in_ler_id
3668 , p_debug_level => p_debug_level
3669 , p_precision => l_precision
3670 , p_warning_text => l_warning
3671 , p_prev_sal => l_prev_sal
3672 , p_pay_proposal_id => l_pay_proposal_id
3673 , p_pay_basis_id => l_pay_basis_id
3674 , p_warning => p_warning);
3675 EXCEPTION
3676 WHEN OTHERS THEN
3677 WRITE('Error in process_sal_rate : '||SQLERRM);
3678 l_error := TRUE;
3679 END;
3680
3681 write_m ('Time after calling process_sal_rate '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3682
3683 p_cwb_rpt_person_rec.adjusted_amount := round(nvl(l_amount,0),l_precision);
3684 p_cwb_rpt_person_rec.prev_sal := round(nvl(l_prev_sal,0),l_precision);
3685 p_cwb_rpt_person_rec.pay_proposal_id := l_pay_proposal_id;
3686 p_cwb_rpt_person_rec.pay_basis_id := l_pay_basis_id;
3687 p_cwb_rpt_person_rec.reason := rt_rec.salary_change_reason;
3688 else
3689 p_cwb_rpt_person_rec.pay_proposal_id := p_pay_proposal_id;
3690 p_cwb_rpt_person_rec.reason := rt_rec.salary_change_reason;
3691 p_cwb_rpt_person_rec.pay_basis_id := l_pay_basis_id;
3692
3693 end if;
3694 ELSE
3695 g_actn := 'Processing non salary rates...';
3696
3697 WRITE('Element input currency : '||l_input_currency);
3698 WRITE('Rate currency : '||rt_rec.currency);
3699 WRITE('Non Monetary UOM: '||rt_rec.units);
3700
3701 IF(rt_rec.units is null) THEN
3702 IF(l_input_currency<>rt_rec.currency) THEN
3703 WRITE('Currency in CWB does not match Input Currency of the Element Type');
3704 fnd_message.set_name ('BEN', 'BEN_94673_EL_CURR_MISMATCH');
3705 l_error := TRUE;
3706
3707 l_message := fnd_message.get_encoded;
3708 fnd_message.set_encoded(l_message);
3709 --
3710 fnd_message.parse_encoded(encoded_message => l_message,
3711 app_short_name => l_app_name,
3712 message_name => l_message_name);
3713 IF g_person_errored = FALSE THEN
3714 l_warning := substr(fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
3715 END IF;
3716 g_person_errored := TRUE;
3717
3718 END IF;
3719 END IF;
3720
3721 WRITE (g_actn);
3722
3723 l_amount := rt_rec.ws_val;
3724 p_cwb_rpt_person_rec.units := rt_rec.units;
3725
3726 write_m ('Time before calling process_non_sal_rate '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3727 BEGIN
3728 OPEN c_posted_element(rt_rec.assignment_id
3729 ,l_element_type_id
3730 ,l_input_value_id
3731 ,l_effective_date);
3732 FETCH c_posted_element INTO p_cwb_rpt_person_rec.prev_eev_screen_entry_value;
3733 CLOSE c_posted_element;
3734 --WRITE('Old screen value: '||p_cwb_rpt_person_rec.prev_eev_screen_entry_value);
3735 process_non_sal_rate (p_effective_date => l_effective_date
3736 , p_lf_evt_ocrd_date => p_lf_evt_ocrd_date
3737 , p_group_pl_id => p_group_pl_id
3738 , p_person_id => p_person_id
3739 , p_pl_id => rt_rec.pl_id
3740 , p_oipl_id => rt_rec.oipl_id
3741 , p_ws_abr_id => rt_rec.ws_abr_id
3742 , p_ws_val => l_amount
3743 , p_nip_acty_ref_perd_cd => rt_rec.acty_ref_perd_cd
3744 , p_business_group_id => rt_rec.business_group_id
3745 , p_object_version_number => rt_rec.object_version_number
3746 , p_group_per_in_ler_id => p_group_per_in_ler_id
3747 , p_debug_level => p_debug_level
3748 , p_warning_text => l_warning
3749 , p_element_entry_value_id => l_element_entry_value_id
3750 , p_input_value_id => l_input_value_id
3751 , p_element_type_id => l_element_type_id
3752 , p_eev_screen_entry_value => l_eev_screen_entry_value);
3753 EXCEPTION
3754 WHEN OTHERS THEN
3755 WRITE('Error in process_non_sal_rate :'||SQLERRM);
3756 l_error := TRUE;
3757 END;
3758 p_cwb_rpt_person_rec.adjusted_amount := round(nvl(l_amount,0),rt_rec.uom_precision);
3759 p_cwb_rpt_person_rec.element_entry_value_id := l_element_entry_value_id;
3760 p_element_entry_value_id := l_element_entry_value_id;
3761 p_cwb_rpt_person_rec.eev_screen_entry_value := l_eev_screen_entry_value;
3762 write_m ('Time after calling process_non_sal_rate '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3763 END IF; -- non-salary.
3764 END IF;
3765 IF ((rt_rec.ws_val IS NULL OR
3766 rt_rec.ws_val = 0) AND rt_rec.elig_flag = 'Y'
3767 AND rt_rec.ws_sub_acty_typ_cd = 'ICM7') THEN
3768 WRITE ('No worksheet amount found. Obtaining other reqd data for reporting...');
3769 OPEN c_prev_pay_proposal (p_group_per_in_ler_id, l_effective_date);
3770 FETCH c_prev_pay_proposal
3771 INTO asg_rec;
3772 CLOSE c_prev_pay_proposal;
3773 p_cwb_rpt_person_rec.pay_basis_id := asg_rec.pay_basis_id;
3774 p_cwb_rpt_person_rec.prev_sal := nvl(asg_rec.proposed_salary_n,0);
3775 p_cwb_rpt_person_rec.reason := rt_rec.salary_change_reason;
3776 --write_h('Prev Sal : '||p_cwb_rpt_person_rec.prev_sal);
3777 END IF; -- ws_amt is not null
3778
3779 l_count := l_count + 1;
3780 p_cache_cwb_rpt_person (l_count).person_rate_id := p_cwb_rpt_person_rec.person_rate_id;
3781 p_cache_cwb_rpt_person (l_count).pl_id := p_cwb_rpt_person_rec.pl_id;
3782 p_cache_cwb_rpt_person (l_count).oipl_id := p_cwb_rpt_person_rec.oipl_id;
3783 p_cache_cwb_rpt_person (l_count).group_pl_id := p_cwb_rpt_person_rec.group_pl_id;
3784 p_cache_cwb_rpt_person (l_count).group_oipl_id := p_cwb_rpt_person_rec.group_oipl_id;
3785 p_cache_cwb_rpt_person (l_count).full_name := p_cwb_rpt_person_rec.full_name;
3786 p_cache_cwb_rpt_person (l_count).emp_number := p_cwb_rpt_person_rec.emp_number;
3787 p_cache_cwb_rpt_person (l_count).business_group_id := p_cwb_rpt_person_rec.business_group_id;
3788 p_cache_cwb_rpt_person (l_count).ws_mgr_id := p_cwb_rpt_person_rec.ws_mgr_id;
3789 p_cache_cwb_rpt_person (l_count).units := p_cwb_rpt_person_rec.units;
3790 p_cache_cwb_rpt_person (l_count).assignment_id := p_cwb_rpt_person_rec.assignment_id;
3791
3792 p_cache_cwb_rpt_person (l_count).base_salary_currency := p_cwb_rpt_person_rec.base_salary_currency;
3793 p_cache_cwb_rpt_person (l_count).elig_salary := p_cwb_rpt_person_rec.elig_salary;
3794 p_cache_cwb_rpt_person (l_count).amount := p_cwb_rpt_person_rec.amount;
3795 p_cache_cwb_rpt_person (l_count).percent_of_elig_sal := p_cwb_rpt_person_rec.percent_of_elig_sal;
3796 p_cache_cwb_rpt_person (l_count).conversion_factor := p_cwb_rpt_person_rec.conversion_factor;
3797 p_cache_cwb_rpt_person (l_count).exchange_rate := p_cwb_rpt_person_rec.exchange_rate;
3798 p_cache_cwb_rpt_person (l_count).effective_date := p_cwb_rpt_person_rec.effective_date;
3799 p_cache_cwb_rpt_person (l_count).eligibility := p_cwb_rpt_person_rec.eligibility;
3800
3801 p_cache_cwb_rpt_person (l_count).adjusted_amount := p_cwb_rpt_person_rec.adjusted_amount;
3802 p_cache_cwb_rpt_person (l_count).uom_precision := p_cwb_rpt_person_rec.uom_precision;
3803 p_cache_cwb_rpt_person (l_count).currency := p_cwb_rpt_person_rec.currency;
3804
3805 p_cache_cwb_rpt_person (l_count).ws_sub_acty_typ_cd :=p_cwb_rpt_person_rec.ws_sub_acty_typ_cd;
3806
3807 p_cache_cwb_rpt_person (l_count).group_per_in_ler_id := p_group_per_in_ler_id;
3808 p_cache_cwb_rpt_person (l_count).lf_evt_ocrd_date := p_lf_evt_ocrd_date;
3809
3810 IF rt_rec.ws_sub_acty_typ_cd = 'ICM7' THEN
3811 p_cache_cwb_rpt_person (l_count).prev_sal := p_cwb_rpt_person_rec.prev_sal;
3812 p_cache_cwb_rpt_person (l_count).base_salary := p_cwb_rpt_person_rec.base_salary;
3813 p_cache_cwb_rpt_person (l_count).base_sal_freq := p_cwb_rpt_person_rec.base_sal_freq;
3814 p_cache_cwb_rpt_person (l_count).pay_ann_factor := p_cwb_rpt_person_rec.pay_ann_factor;
3815 p_cache_cwb_rpt_person (l_count).pl_ann_factor := p_cwb_rpt_person_rec.pl_ann_factor;
3816 p_cache_cwb_rpt_person (l_count).reason := p_cwb_rpt_person_rec.reason;
3817 p_cache_cwb_rpt_person (l_count).fte_factor := p_cwb_rpt_person_rec.fte_factor;
3818 p_cache_cwb_rpt_person (l_count).pay_proposal_id := p_cwb_rpt_person_rec.pay_proposal_id;
3819 p_cache_cwb_rpt_person (l_count).pay_basis_id := p_cwb_rpt_person_rec.pay_basis_id;
3820 ELSE
3821 p_cache_cwb_rpt_person (l_count).prev_sal := null;
3822 p_cache_cwb_rpt_person (l_count).base_salary := null;
3823 p_cache_cwb_rpt_person (l_count).base_sal_freq := null;
3824 p_cache_cwb_rpt_person (l_count).pay_ann_factor := null;
3825 p_cache_cwb_rpt_person (l_count).pl_ann_factor := null;
3826 p_cache_cwb_rpt_person (l_count).reason := null;
3827 p_cache_cwb_rpt_person (l_count).fte_factor := null;
3828 p_cache_cwb_rpt_person (l_count).pay_proposal_id := null;
3829 p_cache_cwb_rpt_person (l_count).pay_basis_id := null;
3830 p_cache_cwb_rpt_person (l_count).element_entry_value_id := p_cwb_rpt_person_rec.element_entry_value_id;
3831 p_cache_cwb_rpt_person (l_count).input_value_id := p_cwb_rpt_person_rec.input_value_id;
3832 p_cache_cwb_rpt_person (l_count).element_type_id := p_cwb_rpt_person_rec.element_type_id;
3833 p_cache_cwb_rpt_person (l_count).eev_screen_entry_value := p_cwb_rpt_person_rec.eev_screen_entry_value;
3834 p_cache_cwb_rpt_person (l_count).element_input_value := p_cwb_rpt_person_rec.element_input_value;
3835 p_cache_cwb_rpt_person (l_count).elmnt_processing_type := p_cwb_rpt_person_rec.elmnt_processing_type;
3836 p_cache_cwb_rpt_person (l_count).prev_eev_screen_entry_value := p_cwb_rpt_person_rec.prev_eev_screen_entry_value;
3837 END IF;
3838
3839
3840 IF (l_warning IS NOT NULL) THEN
3841 WRITE('Writing error message in cache : '||l_warning);
3842 p_cache_cwb_rpt_person (l_count).error_or_warning_text := substr(l_warning,1,2000);
3843 p_cwb_rpt_person_rec.error_or_warning_text := substr(l_warning,1,2000);
3844 END IF;
3845 IF((rt_rec.elig_flag = 'Y')AND(NOT l_error)) THEN
3846 g_actn := 'updating posting date...';
3847 WRITE (g_actn);
3848
3849 OPEN c_rate_ovn (p_group_per_in_ler_id, rt_rec.pl_id, rt_rec.oipl_id);
3850 FETCH c_rate_ovn INTO l_rate_ovn;
3851 CLOSE c_rate_ovn;
3852
3853 write_h ('=====================posting date =========================');
3854 write_h ('||p_group_per_in_ler_id ' || p_group_per_in_ler_id);
3855 write_h ('||p_pl_id ' || rt_rec.pl_id);
3856 write_h ('||p_oipl_id ' || rt_rec.oipl_id);
3857 write_h ('||p_comp_posting_date ' || l_effective_date);
3858 write_h ('||p_object_version_number ' || rt_rec.object_version_number);
3859 write_h ('================================================================');
3860 ben_cwb_person_rates_api.update_person_rate
3861 (p_group_per_in_ler_id => p_group_per_in_ler_id
3862 , p_pl_id => rt_rec.pl_id
3863 , p_oipl_id => rt_rec.oipl_id
3864 , p_comp_posting_date => l_effective_date
3865 , p_object_version_number => l_rate_ovn.object_version_number);
3866 END IF;
3867 end if; -- component_reason is null
3868 END LOOP;
3869 write_m ('Time after processing non component rates '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
3870
3871 IF(l_error) THEN
3872 WRITE('Raising exception at compensation_object');
3873 RAISE ben_batch_utils.g_record_error;
3874 END IF;
3875 END;
3876
3877 --
3878 -- ============================================================================
3879 -- << Procedure: process_person >>
3880 -- ============================================================================
3881 --
3882 PROCEDURE process_person (
3883 p_validate IN VARCHAR2 DEFAULT 'N'
3884 , p_person_id IN NUMBER DEFAULT NULL
3885 , p_person_action_id IN NUMBER DEFAULT NULL
3886 , p_object_version_number IN OUT NOCOPY NUMBER
3887 , p_business_group_id IN NUMBER
3888 , p_lf_evt_ocrd_date IN DATE
3889 , p_plan_id IN NUMBER
3890 , p_group_per_in_ler_id IN NUMBER
3891 , p_grant_price_val IN NUMBER DEFAULT NULL
3892 , p_effective_date IN DATE
3893 , p_audit_log IN VARCHAR2 DEFAULT 'N'
3894 , p_debug_level IN VARCHAR2 DEFAULT NULL
3895 , p_process_sal_comp IN VARCHAR2 DEFAULT 'N'
3896 , p_employees_in_bg IN NUMBER
3897 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
3898 , p_is_placeholder IN VARCHAR2
3899 , p_use_rate_start_date IN VARCHAR2
3900 )
3901 IS
3902 l_comp_error BOOLEAN := FALSE;
3903 l_perf_error BOOLEAN := FALSE;
3904 l_promo_error BOOLEAN := FALSE;
3905 l_actual_termination_date DATE;
3906 l_perf_revw_strt_dt DATE;
3907 l_perf_revw_new_strt_dt DATE;
3908 l_asg_updt_eff_date DATE;
3909 l_interview_typ_cd VARCHAR2 (80);
3910 l_ranking_info c_ranking_info%ROWTYPE;
3911 l_ranking_info_date c_ranking_info_date%ROWTYPE;
3912 l_per_in_ler_id NUMBER;
3913 l_ovn NUMBER;
3914 l_assignment_extra_info_id NUMBER;
3915 l_cache_cwb_rpt_person g_cache_cwb_rpt_person_rec;
3916 l_bg_and_mgr_name c_bg_and_mgr_name%ROWTYPE;
3917 l_amount NUMBER := NULL;
3918 l_perf_txn ben_cwb_asg_update.g_txn%ROWTYPE;
3919 l_asg_txn ben_cwb_asg_update.g_txn%ROWTYPE;
3920 l_rate_ovn c_rate_ovn%ROWTYPE;
3921 l_grp_ovn c_grp_ovn%ROWTYPE;
3922 l_emp_num_and_emp_name c_emp_num_and_emp_name%ROWTYPE;
3923 l_rating_status VARCHAR2 (200);
3924 l_promotion_status VARCHAR2 (200);
3925 l_promo_person_rec g_cwb_rpt_person_rec;
3926 l_perf_person_rec g_cwb_rpt_person_rec;
3927 l_pay_proposal_id NUMBER;
3928 tot_com_amt_rec c_tot_chg_amt_for_proposal%ROWTYPE;
3929 l_error BOOLEAN;
3930 l_collected_message VARCHAR2 (2000);
3931 l_element_entry_value_id NUMBER;
3932 l_posted_perf_rating VARCHAR2 (200);
3933 l_posted_promotions c_posted_promotions%ROWTYPE;
3934 l_prior_assignment_dtls c_prior_assignment%ROWTYPE;
3935 l_proposed_promotions c_proposed_promotions%ROWTYPE;
3936 l_message VARCHAR2 (600);
3937 l_message_name VARCHAR2 (240);
3938 l_app_name VARCHAR2 (240);
3939 l_amount_posted VARCHAR2(60);
3940 l_warning BOOLEAN;
3941 l_overrides_perf_prom c_overrides_perf_prom%ROWTYPE;
3942 l_is_eligible BOOLEAN;
3943 l_dummy c_check_eligibility%ROWTYPE;
3944 l_counter NUMBER;
3945 BEGIN
3946 g_proc := 'process_person';
3947 SAVEPOINT cwb_post_process_person;
3948
3949 l_error := FALSE;
3950 g_person_errored := FALSE;
3951
3952 open c_check_eligibility(p_group_per_in_ler_id);
3953 fetch c_check_eligibility into l_dummy;
3954 If c_check_eligibility%found then
3955 l_is_eligible := TRUE;
3956 else
3957 l_is_eligible := FALSE;
3958 End if;
3959 Close c_check_eligibility;
3960
3961 IF(l_is_eligible = FALSE) THEN
3962 WRITE('Ineligible Person');
3963 END IF;
3964
3965 WRITE ('initializing global names for this thread... ');
3966 init (p_plan_id, p_lf_evt_ocrd_date);
3967
3968 OPEN c_bg_and_mgr_name (p_group_per_in_ler_id, p_effective_date);
3969
3970 FETCH c_bg_and_mgr_name
3971 INTO l_bg_and_mgr_name;
3972
3973 CLOSE c_bg_and_mgr_name;
3974
3975 OPEN c_emp_num_and_emp_name(p_group_per_in_ler_id);
3976 FETCH c_emp_num_and_emp_name into l_emp_num_and_emp_name;
3977 CLOSE c_emp_num_and_emp_name;
3978
3979 if(p_is_placeholder='N') then
3980
3981 OPEN c_performance_promotion (p_plan_id, p_lf_evt_ocrd_date);
3982
3983 FETCH c_performance_promotion
3984 INTO l_perf_revw_strt_dt
3985 , l_perf_revw_new_strt_dt
3986 , l_asg_updt_eff_date
3987 , l_interview_typ_cd;
3988
3989 CLOSE c_performance_promotion;
3990
3991 IF((l_asg_updt_eff_date IS NOT NULL)or(l_perf_revw_strt_dt IS NOT NULL)) THEN
3992 OPEN c_overrides_perf_prom(p_group_per_in_ler_id, p_lf_evt_ocrd_date);
3993 FETCH c_overrides_perf_prom INTO l_overrides_perf_prom;
3994 CLOSE c_overrides_perf_prom;
3995 END IF;
3996
3997 write_h ('=====================Processing Person ==========================');
3998 write_h ('||Person Id ' || p_person_id);
3999 write_h ('||Per_in_ler_id ' || p_group_per_in_ler_id);
4000 write_h ('||Person Action id ' || p_person_action_id);
4001 write_h ('||Plan id ' || p_plan_id);
4002 write_h ('||Employees in bg ' || p_employees_in_bg);
4003 write_h ('||Employee bg ' || l_emp_num_and_emp_name.business_group_id);
4004 write_h ('================================================================');
4005
4006 g_actn := 'Process compensation for the person...';
4007 WRITE (g_actn);
4008
4009 BEGIN
4010 SAVEPOINT process_compensation_object;
4011 write_m ('Time before processing compensation object '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4012 compensation_object (p_group_per_in_ler_id => p_group_per_in_ler_id
4013 , p_person_id => p_person_id
4014 , p_effective_date => p_effective_date
4015 , p_group_pl_id => p_plan_id
4016 , p_lf_evt_ocrd_date => p_lf_evt_ocrd_date
4017 , p_cache_cwb_rpt_person => l_cache_cwb_rpt_person
4018 , p_cwb_rpt_person_rec => g_cwb_rpt_person
4019 , p_grant_price_val => p_grant_price_val
4020 , p_audit_log => p_audit_log
4021 , p_debug_level => p_debug_level
4022 , p_process_sal_comp => p_process_sal_comp
4023 , p_pay_proposal_id => l_pay_proposal_id
4024 , p_element_entry_value_id => l_element_entry_value_id
4025 , p_warning => l_warning
4026 , p_use_rate_start_date => p_use_rate_start_date
4027 );
4028 write_m ('Time after processing compensation object '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4029 l_comp_error := FALSE;
4030 EXCEPTION
4031 WHEN OTHERS
4032 THEN
4033 WRITE('Error in compensation_object : '||SQLERRM);
4034 ROLLBACK TO process_compensation_object;
4035 l_comp_error := TRUE;
4036 g_person_errored := TRUE;
4037 END;
4038
4039 if l_perf_revw_strt_dt is not null then
4040 OPEN ben_cwb_asg_update.g_txn (l_emp_num_and_emp_name.assignment_id,
4041 ben_cwb_asg_update.g_ws_perf_rec_type||to_char(l_perf_revw_strt_dt, 'yyyy/mm/dd')
4042 ||l_interview_typ_cd);
4043
4044 FETCH ben_cwb_asg_update.g_txn INTO l_perf_txn;
4045 CLOSE ben_cwb_asg_update.g_txn;
4046 end if;
4047
4048 if l_asg_updt_eff_date is not null then
4049 OPEN ben_cwb_asg_update.g_txn (l_emp_num_and_emp_name.assignment_id,
4050 ben_cwb_asg_update.g_ws_asg_rec_type||to_char(l_asg_updt_eff_date, 'yyyy/mm/dd'));
4051
4052 FETCH ben_cwb_asg_update.g_txn INTO l_asg_txn;
4053 CLOSE ben_cwb_asg_update.g_txn;
4054 end if;
4055
4056 IF l_asg_txn.assignment_id is not null or l_perf_txn.assignment_id IS NOT NULL THEN
4057 --
4058 OPEN c_actual_termination_date (p_person_id);
4059 FETCH c_actual_termination_date INTO l_actual_termination_date;
4060 CLOSE c_actual_termination_date;
4061
4062 IF l_actual_termination_date IS NOT NULL THEN
4063 IF (((l_asg_txn.assignment_id is not null) and (l_asg_updt_eff_date >= l_actual_termination_date)) OR
4064 ((l_perf_txn.assignment_id is not null) and (l_perf_revw_strt_dt >= l_actual_termination_date)) ) THEN
4065 WRITE ('The person was terminated on ' || l_actual_termination_date
4066 || ' promotion or performance was not applied' );
4067 fnd_message.set_name ('BEN', 'BEN_93365_PERSON_TERMINATED');
4068 l_error := TRUE;
4069 --fnd_message.raise_error;
4070 END IF;
4071 END IF;
4072 END IF;
4073
4074 l_perf_person_rec.full_name := l_emp_num_and_emp_name.full_name;
4075 l_perf_person_rec.person_id := p_person_id;
4076 l_perf_person_rec.emp_number := l_emp_num_and_emp_name.employee_number;
4077 l_perf_person_rec.business_group_name := l_bg_and_mgr_name.name;
4078 l_perf_person_rec.manager_name := l_bg_and_mgr_name.full_name;
4079 l_perf_person_rec.pl_name := g_group_plan_name;
4080 l_perf_person_rec.business_group_id := l_bg_and_mgr_name.business_group_id;
4081 l_perf_person_rec.country_code := l_emp_num_and_emp_name.legislation_code;
4082 l_perf_person_rec.group_per_in_ler_id := p_group_per_in_ler_id;
4083 -- if override is null and mode is SS then do not process OR if PUI then usual
4084 IF (((p_is_self_service = 'Y' and l_overrides_perf_prom.attribute2 is not null)
4085 OR (p_is_self_service = 'N')) and
4086 (l_perf_txn.attribute1 is not null and l_perf_txn.attribute3 is not null)
4087 and (l_is_eligible = TRUE)
4088 ) THEN
4089
4090 BEGIN
4091 SAVEPOINT process_rating;
4092 g_actn := 'found assignment id in the transaction table processing rating...';
4093 WRITE (g_actn);
4094 write_m ('Time before processing rating '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4095
4096 if(p_is_self_service = 'Y') then
4097 WRITE('override performance review date = '||l_overrides_perf_prom.attribute2);
4098 l_perf_revw_strt_dt := to_date(l_overrides_perf_prom.attribute2,'yyyy/mm/dd');
4099 else
4100 WRITE('performance review date = '||l_perf_revw_strt_dt);
4101 l_perf_revw_strt_dt := l_perf_revw_strt_dt;
4102 end if;
4103
4104 l_perf_person_rec.rating_date := l_perf_revw_strt_dt;
4105 l_perf_person_rec.rating_type := l_perf_txn.attribute2;
4106 l_perf_person_rec.performance_rating := substrb(hr_general.decode_lookup('PERFORMANCE_RATING',l_perf_txn.attribute3),1,30);
4107
4108 if(l_error) then
4109 fnd_message.raise_error;
4110 end if;
4111
4112 ben_cwb_asg_update.process_rating (p_person_id => p_person_id
4113 , p_txn_rec => l_perf_txn
4114 , p_business_group_id => l_emp_num_and_emp_name.business_group_id
4115 , p_audit_log => p_audit_log
4116 , p_process_status => l_rating_status
4117 , p_group_per_in_ler_id => p_group_per_in_ler_id
4118 , p_effective_date => l_perf_revw_strt_dt
4119 );
4120
4121 write_m ('Time after processing rating '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4122
4123 IF l_rating_status = 'CWB_PERF_SUS' THEN
4124 g_actn := 'Person ' || p_person_id || ' processed successfully for Performance Rating';
4125 WRITE (g_actn);
4126 END IF;
4127
4128 l_perf_person_rec.status := 'SC';
4129
4130 WRITE('Performance rating is '||l_perf_person_rec.performance_rating);
4131 l_perf_error := FALSE;
4132
4133 EXCEPTION
4134 WHEN OTHERS THEN
4135 WRITE('Error at Performance rating '||SQLERRM);
4136 l_perf_person_rec.status := 'E';
4137 l_message := fnd_message.get_encoded;
4138 fnd_message.set_encoded(l_message);
4139 --
4140 fnd_message.parse_encoded(encoded_message => l_message,
4141 app_short_name => l_app_name,
4142 message_name => l_message_name);
4143 l_perf_person_rec.error_or_warning_text := substr(fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
4144 g_person_errored := TRUE;
4145 WRITE(l_perf_person_rec.error_or_warning_text);
4146 ROLLBACK TO process_rating;
4147 IF(benutils.get_message_name = 'BEN_93371_RATING_EXST_FOR_DATE') THEN
4148 --l_perf_person_rec.error_or_warning_text := fnd_message.get;
4149 write_m ('Time after processing rating '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4150 WRITE('Failed Performance rating is '||l_perf_person_rec.performance_rating);
4151 l_rating_status := 'CWB_PERF_SUS';
4152 ELSE
4153 l_perf_error := TRUE;
4154 l_error := TRUE;
4155 END IF;
4156 g_actn := 'Person ' || p_person_id || ' failed for Performance Rating';
4157 WRITE (g_actn);
4158 END;
4159 ELSE
4160 l_perf_person_rec.rating_date := hr_general.end_of_time;
4161 l_perf_person_rec.status := 'SC';
4162 END IF;
4163 --
4164
4165 l_promo_person_rec.full_name := l_emp_num_and_emp_name.full_name;
4166 l_promo_person_rec.person_id := p_person_id;
4167 l_promo_person_rec.emp_number := l_emp_num_and_emp_name.employee_number;
4168 l_promo_person_rec.business_group_name := l_bg_and_mgr_name.name;
4169 l_promo_person_rec.manager_name := l_bg_and_mgr_name.full_name;
4170 l_promo_person_rec.pl_name := g_group_plan_name;
4171 l_promo_person_rec.business_group_id := l_bg_and_mgr_name.business_group_id;
4172 l_promo_person_rec.country_code := l_emp_num_and_emp_name.legislation_code;
4173 l_promo_person_rec.group_per_in_ler_id := p_group_per_in_ler_id;
4174 l_promo_person_rec.assignment_id := l_emp_num_and_emp_name.assignment_id;
4175 -- if override is null and mode is SS then do not process OR if PUI then usual
4176 IF (((p_is_self_service = 'Y' and l_overrides_perf_prom.attribute2 is not null)
4177 OR (p_is_self_service = 'N')) and
4178 (l_asg_txn.attribute1 is not NULL)
4179 and (l_is_eligible=TRUE)) THEN
4180
4181 BEGIN
4182 SAVEPOINT process_promotions;
4183 g_actn := 'processing promotions ...';
4184 WRITE (g_actn);
4185 write_m ('Time before processing promotions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4186
4187 if(p_is_self_service = 'Y') then
4188 WRITE('override promotion date = '||l_overrides_perf_prom.attribute1);
4189 l_asg_updt_eff_date := to_date(l_overrides_perf_prom.attribute1,'yyyy/mm/dd');
4190 else
4191 WRITE('promotion date = '||l_asg_updt_eff_date);
4192 l_asg_updt_eff_date := l_asg_updt_eff_date;
4193 end if;
4194
4195 OPEN c_prior_assignment(p_group_per_in_ler_id);
4196 FETCH c_prior_assignment into l_prior_assignment_dtls;
4197 CLOSE c_prior_assignment;
4198 l_promo_person_rec.assignment_changed := 'Y';
4199 l_promo_person_rec.prior_job := l_prior_assignment_dtls.job;
4200 l_promo_person_rec.prior_position := l_prior_assignment_dtls.position;
4201 l_promo_person_rec.prior_grade := l_prior_assignment_dtls.grade;
4202 l_promo_person_rec.prior_group := l_prior_assignment_dtls.group_name;
4203
4204 OPEN c_proposed_promotions(to_number(l_emp_num_and_emp_name.assignment_id),
4205 ben_cwb_asg_update.g_ws_asg_rec_type
4206 ||l_asg_txn.attribute1);
4207 FETCH c_proposed_promotions into l_proposed_promotions;
4208 CLOSE c_proposed_promotions;
4209 WRITE(ben_cwb_asg_update.g_ws_asg_rec_type||l_asg_txn.attribute1);
4210
4211 l_promo_person_rec.proposed_job := l_proposed_promotions.job;
4212 l_promo_person_rec.proposed_position := l_proposed_promotions.position;
4213 l_promo_person_rec.proposed_grade := l_proposed_promotions.grade;
4214 l_promo_person_rec.proposed_group := l_proposed_promotions.group_name;
4215
4216 l_promo_person_rec.prior_flex1 := l_prior_assignment_dtls.ass_attribute1;
4217 l_promo_person_rec.proposed_flex1 := l_asg_txn.attribute11;
4218 l_promo_person_rec.prior_flex2 := l_prior_assignment_dtls.ass_attribute2;
4219 l_promo_person_rec.proposed_flex2 := l_asg_txn.attribute12;
4220 l_promo_person_rec.prior_flex3 := l_prior_assignment_dtls.ass_attribute3;
4221 l_promo_person_rec.proposed_flex3 := l_asg_txn.attribute13;
4222 l_promo_person_rec.prior_flex4 := l_prior_assignment_dtls.ass_attribute4;
4223 l_promo_person_rec.proposed_flex4 := l_asg_txn.attribute14;
4224 l_promo_person_rec.prior_flex5 := l_prior_assignment_dtls.ass_attribute5;
4225 l_promo_person_rec.proposed_flex5 := l_asg_txn.attribute15;
4226 l_promo_person_rec.prior_flex6 := l_prior_assignment_dtls.ass_attribute6;
4227 l_promo_person_rec.proposed_flex6 := l_asg_txn.attribute16;
4228 l_promo_person_rec.prior_flex7 := l_prior_assignment_dtls.ass_attribute7;
4229 l_promo_person_rec.proposed_flex7 := l_asg_txn.attribute17;
4230 l_promo_person_rec.prior_flex8 := l_prior_assignment_dtls.ass_attribute8;
4231 l_promo_person_rec.proposed_flex8 := l_asg_txn.attribute18;
4232 l_promo_person_rec.prior_flex9 := l_prior_assignment_dtls.ass_attribute9;
4233 l_promo_person_rec.proposed_flex9 := l_asg_txn.attribute19;
4234 l_promo_person_rec.prior_flex10 := l_prior_assignment_dtls.ass_attribute10;
4235 l_promo_person_rec.proposed_flex10 := l_asg_txn.attribute20;
4236 l_promo_person_rec.prior_flex11 := l_prior_assignment_dtls.ass_attribute11;
4237 l_promo_person_rec.proposed_flex11 := l_asg_txn.attribute21;
4238 l_promo_person_rec.prior_flex12 := l_prior_assignment_dtls.ass_attribute12;
4239 l_promo_person_rec.proposed_flex12 := l_asg_txn.attribute22;
4240 l_promo_person_rec.prior_flex13 := l_prior_assignment_dtls.ass_attribute13;
4241 l_promo_person_rec.proposed_flex13 := l_asg_txn.attribute23;
4242 l_promo_person_rec.prior_flex14 := l_prior_assignment_dtls.ass_attribute14;
4243 l_promo_person_rec.proposed_flex14 := l_asg_txn.attribute24;
4244 l_promo_person_rec.prior_flex15 := l_prior_assignment_dtls.ass_attribute15;
4245 l_promo_person_rec.proposed_flex15 := l_asg_txn.attribute25;
4246 l_promo_person_rec.prior_flex16 := l_prior_assignment_dtls.ass_attribute16;
4247 l_promo_person_rec.proposed_flex16 := l_asg_txn.attribute26;
4248 l_promo_person_rec.prior_flex17 := l_prior_assignment_dtls.ass_attribute17;
4249 l_promo_person_rec.proposed_flex17 := l_asg_txn.attribute27;
4250 l_promo_person_rec.prior_flex18 := l_prior_assignment_dtls.ass_attribute18;
4251 l_promo_person_rec.proposed_flex18 := l_asg_txn.attribute28;
4252 l_promo_person_rec.prior_flex19 := l_prior_assignment_dtls.ass_attribute19;
4253 l_promo_person_rec.proposed_flex19 := l_asg_txn.attribute29;
4254 l_promo_person_rec.prior_flex20 := l_prior_assignment_dtls.ass_attribute20;
4255 l_promo_person_rec.proposed_flex20 := l_asg_txn.attribute30;
4256 l_promo_person_rec.prior_flex21 := l_prior_assignment_dtls.ass_attribute21;
4257 l_promo_person_rec.proposed_flex21 := l_asg_txn.attribute31;
4258 l_promo_person_rec.prior_flex22 := l_prior_assignment_dtls.ass_attribute22;
4259 l_promo_person_rec.proposed_flex22 := l_asg_txn.attribute32;
4260 l_promo_person_rec.prior_flex23 := l_prior_assignment_dtls.ass_attribute23;
4261 l_promo_person_rec.proposed_flex23 := l_asg_txn.attribute33;
4262 l_promo_person_rec.prior_flex24 := l_prior_assignment_dtls.ass_attribute24;
4263 l_promo_person_rec.proposed_flex24 := l_asg_txn.attribute34;
4264 l_promo_person_rec.prior_flex25 := l_prior_assignment_dtls.ass_attribute25;
4265 l_promo_person_rec.proposed_flex25 := l_asg_txn.attribute35;
4266 l_promo_person_rec.prior_flex26 := l_prior_assignment_dtls.ass_attribute26;
4267 l_promo_person_rec.proposed_flex26 := l_asg_txn.attribute36;
4268 l_promo_person_rec.prior_flex27 := l_prior_assignment_dtls.ass_attribute27;
4269 l_promo_person_rec.proposed_flex27 := l_asg_txn.attribute37;
4270 l_promo_person_rec.prior_flex28 := l_prior_assignment_dtls.ass_attribute28;
4271 l_promo_person_rec.proposed_flex28 := l_asg_txn.attribute38;
4272 l_promo_person_rec.prior_flex29 := l_prior_assignment_dtls.ass_attribute29;
4273 l_promo_person_rec.proposed_flex29 := l_asg_txn.attribute39;
4274 l_promo_person_rec.prior_flex30 := l_prior_assignment_dtls.ass_attribute30;
4275 l_promo_person_rec.proposed_flex30 := l_asg_txn.attribute40;
4276 l_promo_person_rec.asgn_change_reason := l_asg_txn.attribute3;
4277 l_promo_person_rec.effective_date := l_asg_updt_eff_date;
4278
4279 if(l_error) then
4280 fnd_message.raise_error;
4281 end if;
4282
4283 ben_cwb_asg_update.process_promotions (p_person_id => p_person_id
4284 , p_asg_txn_rec => l_asg_txn
4285 , p_business_group_id => l_emp_num_and_emp_name.business_group_id
4286 , p_audit_log => p_audit_log
4287 , p_process_status => l_promotion_status
4288 , p_group_per_in_ler_id => p_group_per_in_ler_id
4289 , p_effective_date => l_asg_updt_eff_date
4290 );
4291 IF l_promotion_status = 'CWB_PROM_SUS' THEN
4292 g_actn := 'Person ' || p_person_id || ' processed successfully for assignment changes';
4293 WRITE (g_actn);
4294 l_promo_person_rec.status := 'SC';
4295 l_promo_error := FALSE;
4296 ELSE
4297 l_promo_person_rec.status := 'SC';
4298 END IF;
4299 write_m ('Time after processing promotions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4300 EXCEPTION
4301 WHEN OTHERS THEN
4302 WRITE('Error at processing promotions'||SQLERRM);
4303 l_promo_person_rec.status := 'E';
4304 l_message := fnd_message.get_encoded;
4305 fnd_message.set_encoded(l_message);
4306 --
4307 fnd_message.parse_encoded(encoded_message => l_message,
4308 app_short_name => l_app_name,
4309 message_name => l_message_name);
4310 l_promo_person_rec.error_or_warning_text := substr(fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
4311 WRITE(l_promo_person_rec.error_or_warning_text);
4312 ROLLBACK TO process_promotions;
4313 l_promo_error := TRUE;
4314 l_error := TRUE;
4315 g_person_errored := TRUE;
4316 g_actn:='Person ' || p_person_id || ' failed for assignment changes';
4317 WRITE (g_actn);
4318 END;
4319 ELSE
4320 l_promo_person_rec.assignment_changed := 'N';
4321 l_promo_person_rec.status := 'SC';
4322 END IF;
4323 --
4324
4325 BEGIN
4326 IF ( l_comp_error
4327 OR l_perf_error
4328 OR l_promo_error)
4329 THEN
4330 l_error := TRUE;
4331 g_person_errored := TRUE;
4332 RAISE ben_batch_utils.g_record_error;
4333 END IF;
4334
4335 process_life_event(p_person_id
4336 , p_lf_evt_ocrd_date
4337 , p_plan_id
4338 , p_group_per_in_ler_id
4339 , p_effective_date
4340 , p_employees_in_bg);
4341
4342 EXCEPTION
4343 WHEN OTHERS THEN
4344 WRITE('Life Event not closed due to error');
4345 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'N';
4346 END;
4347
4348 BEGIN
4349 SAVEPOINT process_ranking;
4350 IF l_perf_revw_strt_dt is not null THEN
4351
4352 write_m ('Time before processing the rank '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4353 FOR l_ranking_info IN c_ranking_info (p_group_per_in_ler_id)
4354 LOOP
4355 WRITE ('Updating ranking for this person...');
4356
4357 IF c_ranking_info_date%ISOPEN THEN
4358 CLOSE c_ranking_info_date;
4359 END IF;
4360
4361 OPEN c_ranking_info_date(p_group_per_in_ler_id,
4362 l_perf_revw_strt_dt ,
4363 l_ranking_info.aei_information2);
4364 FETCH c_ranking_info_date INTO l_ranking_info_date;
4365 IF c_ranking_info_date%NOTFOUND THEN
4366 write ('Found a rank which needs to be updated...');
4367 hr_assignment_extra_info_api.create_assignment_extra_info
4368 ( p_validate => false
4369 ,p_assignment_id => l_ranking_info.assignment_id
4370 ,p_information_type => 'CWBRANK'
4371 ,p_aei_information_category => 'CWBRANK'
4372 ,p_aei_information1 => l_ranking_info.aei_information1
4373 ,p_aei_information2 => l_ranking_info.aei_information2
4374 ,p_aei_information3 => p_group_per_in_ler_id
4375 ,p_aei_information4 => l_ranking_info.aei_information4
4376 ,p_aei_information5 => fnd_date.date_to_canonical(l_perf_revw_strt_dt)
4377 ,p_aei_information6 => p_plan_id
4378 ,p_assignment_extra_info_id => l_assignment_extra_info_id
4379 ,p_object_version_number => l_ovn );
4380 ELSE
4381 WRITE ('Found a rank which need not be updated...');
4382 END IF;
4383 CLOSE c_ranking_info_date;
4384
4385
4386
4387 END LOOP;
4388
4389 write_m ('Time after processing the rank '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4390 END IF;
4391 EXCEPTION
4392 WHEN OTHERS THEN
4393 WRITE('Error at Ranking '||SQLERRM);
4394 l_perf_person_rec.status := 'E';
4395 l_message := fnd_message.get_encoded;
4396 fnd_message.set_encoded(l_message);
4397 --
4398 fnd_message.parse_encoded(encoded_message => l_message,
4399 app_short_name => l_app_name,
4400 message_name => l_message_name);
4401 l_perf_person_rec.error_or_warning_text := substr(fnd_message.get_number(l_app_name,l_message_name)||' '||fnd_message.get,1,2000);
4402 WRITE(l_perf_person_rec.error_or_warning_text);
4403 ROLLBACK TO process_ranking;
4404 l_perf_error := TRUE;
4405 l_error := TRUE;
4406 g_person_errored := TRUE;
4407 g_actn := 'Person ' || p_person_id || ' failed for Ranking';
4408 WRITE (g_actn);
4409 END;
4410
4411 WRITE ('creating cache for reporting...');
4412
4413 FOR v_counter IN 1 .. l_cache_cwb_rpt_person.COUNT
4414 LOOP
4415 IF(trim(l_collected_message) is null) THEN
4416 l_collected_message := substr(l_collected_message||
4417 l_cache_cwb_rpt_person (v_counter).error_or_warning_text,1,2000);
4418 END IF;
4419 /* -- not stacking up messages
4420 if(instr(l_collected_message,l_cache_cwb_rpt_person (v_counter).error_or_warning_text)=0 or
4421 instr(l_collected_message,l_cache_cwb_rpt_person (v_counter).error_or_warning_text)is null) then
4422
4423 l_collected_message := substr(l_collected_message||
4424 l_cache_cwb_rpt_person (v_counter).error_or_warning_text,1,2000);
4425 end if;
4426 */
4427 END LOOP;
4428
4429 --IF l_rating_status = 'CWB_PERF_SUS' THEN
4430 if (l_perf_person_rec.rating_date is not null) then
4431 if((NOT l_error)and(p_validate <> 'Y')and
4432 (l_perf_txn.attribute1 is not null and l_perf_txn.attribute3 is not null)
4433 ) then
4434 open c_posted_rating(l_perf_person_rec.person_id,
4435 l_perf_person_rec.rating_date);
4436 fetch c_posted_rating into l_posted_perf_rating;
4437 close c_posted_rating;
4438
4439 l_perf_person_rec.posted_rating :=
4440 substrb(hr_general.decode_lookup('PERFORMANCE_RATING',l_posted_perf_rating),1,30);
4441
4442 end if;
4443 IF(l_error) THEN
4444 l_perf_person_rec.status := 'E';
4445 END IF;
4446 g_cache_cwb_rpt_person (g_cache_cwb_rpt_person.COUNT + 1) := l_perf_person_rec;
4447 IF(trim(l_collected_message) is null) THEN
4448 l_collected_message := substr(l_collected_message||
4449 l_perf_person_rec.error_or_warning_text,1,2000);
4450 END IF;
4451 --l_collected_message||' '||l_perf_person_rec.error_or_warning_text;
4452 END IF;
4453
4454 --IF l_promotion_status = 'CWB_PROM_SUS' THEN
4455 IF (l_promo_person_rec.assignment_id is not null) then
4456 if((NOT l_error)and(p_validate <> 'Y')and(l_asg_txn.attribute1 is not null)) then
4457 open c_posted_promotions(l_promo_person_rec.assignment_id,
4458 l_promo_person_rec.effective_date);
4459 fetch c_posted_promotions into l_posted_promotions;
4460 close c_posted_promotions;
4461
4462 l_promo_person_rec.posted_job := l_posted_promotions.job;
4463 l_promo_person_rec.posted_position := l_posted_promotions.position;
4464 l_promo_person_rec.posted_grade := l_posted_promotions.grade;
4465 l_promo_person_rec.posted_group := l_posted_promotions.group_name;
4466 l_promo_person_rec.posted_flex1 := l_posted_promotions.ass_attribute1;
4467 l_promo_person_rec.posted_flex2 := l_posted_promotions.ass_attribute2;
4468 l_promo_person_rec.posted_flex3 := l_posted_promotions.ass_attribute3;
4469 l_promo_person_rec.posted_flex4 := l_posted_promotions.ass_attribute4;
4470 l_promo_person_rec.posted_flex5 := l_posted_promotions.ass_attribute5;
4471 l_promo_person_rec.posted_flex6 := l_posted_promotions.ass_attribute6;
4472 l_promo_person_rec.posted_flex7 := l_posted_promotions.ass_attribute7;
4473 l_promo_person_rec.posted_flex8 := l_posted_promotions.ass_attribute8;
4474 l_promo_person_rec.posted_flex9 := l_posted_promotions.ass_attribute9;
4475 l_promo_person_rec.posted_flex10 := l_posted_promotions.ass_attribute10;
4476 l_promo_person_rec.posted_flex11 := l_posted_promotions.ass_attribute11;
4477 l_promo_person_rec.posted_flex12 := l_posted_promotions.ass_attribute12;
4478 l_promo_person_rec.posted_flex13 := l_posted_promotions.ass_attribute13;
4479 l_promo_person_rec.posted_flex14 := l_posted_promotions.ass_attribute14;
4480 l_promo_person_rec.posted_flex15 := l_posted_promotions.ass_attribute15;
4481 l_promo_person_rec.posted_flex16 := l_posted_promotions.ass_attribute16;
4482 l_promo_person_rec.posted_flex17 := l_posted_promotions.ass_attribute17;
4483 l_promo_person_rec.posted_flex18 := l_posted_promotions.ass_attribute18;
4484 l_promo_person_rec.posted_flex19 := l_posted_promotions.ass_attribute19;
4485 l_promo_person_rec.posted_flex20 := l_posted_promotions.ass_attribute20;
4486 l_promo_person_rec.posted_flex21 := l_posted_promotions.ass_attribute21;
4487 l_promo_person_rec.posted_flex22 := l_posted_promotions.ass_attribute22;
4488 l_promo_person_rec.posted_flex23 := l_posted_promotions.ass_attribute23;
4489 l_promo_person_rec.posted_flex24 := l_posted_promotions.ass_attribute24;
4490 l_promo_person_rec.posted_flex25 := l_posted_promotions.ass_attribute25;
4491 l_promo_person_rec.posted_flex26 := l_posted_promotions.ass_attribute26;
4492 l_promo_person_rec.posted_flex27 := l_posted_promotions.ass_attribute27;
4493 l_promo_person_rec.posted_flex28 := l_posted_promotions.ass_attribute28;
4494 l_promo_person_rec.posted_flex29 := l_posted_promotions.ass_attribute29;
4495 l_promo_person_rec.posted_flex30 := l_posted_promotions.ass_attribute30;
4496
4497 end if;
4498 WRITE('Writing Promotion Record into Cache...');
4499 IF(l_error) THEN
4500 l_promo_person_rec.status := 'E';
4501 END IF;
4502 g_cache_cwb_rpt_person (g_cache_cwb_rpt_person.COUNT + 1) := l_promo_person_rec;
4503 IF(trim(l_collected_message) is null) THEN
4504 l_collected_message := substr(l_collected_message||
4505 l_promo_person_rec.error_or_warning_text,1,2000);
4506 END IF;
4507 --l_collected_message||' '||l_promo_person_rec.error_or_warning_text;
4508 WRITE(l_promo_person_rec.full_name);
4509 WRITE(l_promo_person_rec.assignment_changed);
4510 END IF;
4511
4512 FOR v_counter IN 1 .. l_cache_cwb_rpt_person.COUNT
4513 LOOP
4514 l_amount_posted := null;
4515 --WRITE('Counter: '||v_counter);
4516 l_cache_cwb_rpt_person (v_counter).manager_name := l_bg_and_mgr_name.full_name;
4517 l_cache_cwb_rpt_person (v_counter).business_group_name := l_bg_and_mgr_name.NAME;
4518 l_cache_cwb_rpt_person (v_counter).person_id := p_person_id;
4519 l_cache_cwb_rpt_person (v_counter).country_code := l_emp_num_and_emp_name.legislation_code;
4520 if((NOT l_error)and(p_validate <> 'Y')
4521 and(l_cache_cwb_rpt_person (v_counter).element_entry_value_id is not null)) then
4522 WRITE('Element_entry_value_id: '||l_cache_cwb_rpt_person (v_counter).element_entry_value_id);
4523 OPEN c_posted_element(l_cache_cwb_rpt_person (v_counter).assignment_id
4524 ,l_cache_cwb_rpt_person (v_counter).element_type_id
4525 ,l_cache_cwb_rpt_person (v_counter).input_value_id
4526 ,l_cache_cwb_rpt_person (v_counter).effective_date);
4527 --FETCH c_posted_element INTO l_cache_cwb_rpt_person (v_counter).amount_posted;
4528 FETCH c_posted_element INTO l_amount_posted;
4529 CLOSE c_posted_element;
4530 l_cache_cwb_rpt_person (v_counter).amount_posted:= fnd_number.canonical_to_number(l_amount_posted);
4531 WRITE('Amount posted: '||l_cache_cwb_rpt_person (v_counter).amount_posted);
4532 end if;
4533 if((NOT l_error)and(p_validate <> 'Y')and
4534 (l_cache_cwb_rpt_person (v_counter).pay_proposal_id is not null)) then
4535 WRITE('Pay_proposal_id: '||l_cache_cwb_rpt_person (v_counter).pay_proposal_id);
4536 OPEN c_posted_salary(l_cache_cwb_rpt_person (v_counter).pay_proposal_id);
4537 FETCH c_posted_salary INTO l_cache_cwb_rpt_person (v_counter).new_sal;
4538 CLOSE c_posted_salary;
4539 l_cache_cwb_rpt_person (v_counter).amount_posted
4540 := l_cache_cwb_rpt_person (v_counter).new_sal - l_cache_cwb_rpt_person (v_counter).prev_sal;
4541 WRITE('New Sal: '||l_cache_cwb_rpt_person (v_counter).new_sal);
4542 end if;
4543
4544 if(NOT l_error) THEN
4545 l_cache_cwb_rpt_person (v_counter).lf_evt_closed := 'Y';
4546 else
4547 l_cache_cwb_rpt_person (v_counter).lf_evt_closed := 'N';
4548 l_cache_cwb_rpt_person (v_counter).effective_date := NULL;
4549 end if;
4550
4551 l_warning := FALSE; --warning not supported completely yet
4552
4553 IF(l_warning) THEN
4554 l_cache_cwb_rpt_person (v_counter).status := 'W';
4555 ELSE
4556 IF(NOT l_error) THEN
4557 IF (l_cache_cwb_rpt_person (v_counter).amount IS NULL)
4558 THEN
4559 l_cache_cwb_rpt_person (v_counter).status := 'SC';
4560 ELSE
4561 IF (l_amount IS NULL)
4562 THEN
4563 l_amount := 0;
4564 END IF;
4565 l_amount := l_amount + l_cache_cwb_rpt_person (v_counter).amount;
4566 l_cache_cwb_rpt_person (v_counter).status := 'SC';
4567 END IF;
4568
4569 ELSE
4570 l_cache_cwb_rpt_person (v_counter).status := 'E';
4571 END IF;
4572 END IF;
4573
4574
4575 IF l_cache_cwb_rpt_person (v_counter).pl_id = l_cache_cwb_rpt_person (v_counter).group_pl_id
4576 THEN
4577 l_cache_cwb_rpt_person (v_counter).pl_name := g_group_plan_name;
4578 ELSE
4579 l_cache_cwb_rpt_person (v_counter).pl_name :=
4580 g_cache_actual_plans (l_cache_cwb_rpt_person (v_counter).pl_id);
4581 END IF;
4582
4583 IF l_cache_cwb_rpt_person (v_counter).oipl_id <> -1
4584 THEN
4585 IF l_cache_cwb_rpt_person (v_counter).oipl_id =
4586 l_cache_cwb_rpt_person (v_counter).group_oipl_id
4587 THEN
4588 l_cache_cwb_rpt_person (v_counter).opt_name :=
4589 g_cache_group_options (l_cache_cwb_rpt_person (v_counter).oipl_id);
4590 ELSE
4591 l_cache_cwb_rpt_person (v_counter).opt_name :=
4592 g_cache_actual_options (l_cache_cwb_rpt_person (v_counter).oipl_id);
4593 END IF;
4594 ELSE
4595 l_cache_cwb_rpt_person (v_counter).error_or_warning_text := substr(l_collected_message,1,2000);
4596 END IF;
4597
4598 g_cache_cwb_rpt_person (g_cache_cwb_rpt_person.COUNT + 1) :=
4599 l_cache_cwb_rpt_person (v_counter);
4600 END LOOP;
4601 WRITE('populating g_cache_cwb_sum_person');
4602 g_cache_cwb_sum_person (p_person_id).person_id := p_person_id;
4603 g_cache_cwb_sum_person (p_person_id).bg_name := l_bg_and_mgr_name.NAME;
4604 g_cache_cwb_sum_person (p_person_id).bg_id := p_business_group_id;
4605 g_cache_cwb_sum_person (p_person_id).country_code := l_emp_num_and_emp_name.legislation_code;
4606
4607 l_warning := FALSE; --warning not supported completely yet
4608
4609 IF(l_warning) THEN
4610 g_cache_cwb_sum_person (p_person_id).status := 'W';
4611 ELSE
4612 IF(NOT l_error) THEN
4613 IF (l_amount IS NULL)
4614 THEN
4615 g_cache_cwb_sum_person (p_person_id).status := 'SC';
4616 ELSE
4617 g_cache_cwb_sum_person (p_person_id).status := 'SC';
4618 END IF;
4619 ELSE
4620 g_cache_cwb_sum_person (p_person_id).status := 'E';
4621 RAISE ben_batch_utils.g_record_error;
4622 END IF;
4623 END IF;
4624 else
4625 BEGIN
4626 write_h ('=====================Processing Person ==========================');
4627 write_h ('||Person Id ' || p_person_id);
4628 write_h ('||Per_in_ler_id ' || p_group_per_in_ler_id);
4629 write_h ('||Person Action id ' || p_person_action_id);
4630 write_h ('||Plan id ' || p_plan_id);
4631 write_h ('=================================================================');
4632 l_counter := g_cache_cwb_rpt_person.COUNT;
4633 l_counter := l_counter + 1;
4634
4635 g_cache_cwb_rpt_person(l_counter).group_pl_id:= p_plan_id;
4636 g_cache_cwb_rpt_person(l_counter).person_id:= p_person_id;
4637 g_cache_cwb_rpt_person(l_counter).assignment_id := l_emp_num_and_emp_name.assignment_id;
4638 g_cache_cwb_rpt_person(l_counter).emp_number := l_emp_num_and_emp_name.employee_number;
4639 g_cache_cwb_rpt_person(l_counter).group_per_in_ler_id:= p_group_per_in_ler_id;
4640 g_cache_cwb_rpt_person(l_counter).full_name:= l_emp_num_and_emp_name.full_name;
4641 g_cache_cwb_rpt_person(l_counter).business_group_name:= l_bg_and_mgr_name.name;
4642 g_cache_cwb_rpt_person(l_counter).business_group_id:= l_bg_and_mgr_name.business_group_id;
4643 g_cache_cwb_rpt_person(l_counter).manager_name:= l_bg_and_mgr_name.full_name;
4644 g_cache_cwb_rpt_person(l_counter).pl_name:= g_group_plan_name;
4645 g_cache_cwb_rpt_person(l_counter).country_code:= l_emp_num_and_emp_name.legislation_code;
4646 g_cache_cwb_rpt_person(l_counter).lf_evt_ocrd_date:= p_lf_evt_ocrd_date;
4647 g_cache_cwb_rpt_person(l_counter).group_per_in_ler_id := p_group_per_in_ler_id;
4648 --g_cache_cwb_rpt_person(l_counter).oipl_id := -1;
4649 --g_cache_cwb_rpt_person(l_counter).eligibility := 'N';
4650
4651 process_life_event(p_person_id
4652 , p_lf_evt_ocrd_date
4653 , p_plan_id
4654 , p_group_per_in_ler_id
4655 , p_effective_date
4656 , p_employees_in_bg);
4657 WRITE('Placeholder Life Event closed.');
4658 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'Y';
4659 g_cache_cwb_rpt_person(l_counter).status:= 'SC';
4660 g_cache_cwb_rpt_person(l_counter).lf_evt_closed:= 'Y';
4661
4662 EXCEPTION
4663 WHEN OTHERS THEN
4664 WRITE('Life Event not closed due to error');
4665 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'N';
4666 g_cache_cwb_rpt_person(l_counter).status:= 'E';
4667 g_cache_cwb_rpt_person(l_counter).lf_evt_closed:= 'N';
4668 END;
4669 end if;
4670 IF (p_validate = 'Y')
4671 THEN
4672 g_actn := 'Running in rollback mode, person rolled back...';
4673 WRITE (g_actn);
4674 ROLLBACK TO cwb_post_process_person;
4675 END IF;
4676
4677 IF p_person_action_id IS NOT NULL
4678 THEN
4679 g_actn := 'Updating person actions as processed...';
4680 WRITE (g_actn);
4681 write_h ('Time before updating the person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4682 write_h ('=====================Updating Person Actions==========================');
4683 write_h ('||Person Action id ' || p_person_action_id);
4684 write_h ('||p_object_version_number ' || p_object_version_number);
4685 write_h ('||p_effective_date ' || p_effective_date);
4686 write_h ('================================================================');
4687 ben_person_actions_api.update_person_actions
4688 (p_person_action_id => p_person_action_id
4689 , p_action_status_cd => 'P'
4690 , p_object_version_number => p_object_version_number
4691 , p_effective_date => p_effective_date
4692 );
4693 WRITE ('Time after updating the person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4694 END IF;
4695
4696 g_actn := 'Finished processing the person...';
4697 Write ('----------------------------------------------------');
4698 WRITE (g_actn);
4699 EXCEPTION
4700 WHEN OTHERS
4701 THEN
4702 WRITE('Error at Process Person');
4703 ROLLBACK TO cwb_post_process_person;
4704
4705 IF ((g_is_force_on_per = 'Y')and(p_validate <> 'Y')) THEN
4706 write_h('Forcing life event to close');
4707 process_life_event(
4708 p_person_id
4709 , p_lf_evt_ocrd_date
4710 , p_plan_id
4711 , p_group_per_in_ler_id
4712 , p_effective_date
4713 , p_employees_in_bg);
4714 END IF;
4715
4716 g_persons_errored := g_persons_errored + 1;
4717 ben_batch_utils.rpt_error (p_proc => g_proc, p_last_actn => g_actn, p_rpt_flag => TRUE);
4718
4719 IF p_person_action_id IS NOT NULL
4720 THEN
4721 ben_person_actions_api.update_person_actions
4722 (p_person_action_id => p_person_action_id
4723 , p_action_status_cd => 'E'
4724 , p_object_version_number => p_object_version_number
4725 , p_effective_date => p_effective_date
4726 );
4727 /*
4728 g_cwb_rpt_person.status := 'E';
4729 g_cwb_rpt_person.lf_evt_closed := 'N';
4730 g_cwb_rpt_person.full_name := l_emp_num_and_emp_name.full_name;
4731 g_cwb_rpt_person.emp_number := l_emp_num_and_emp_name.employee_number;
4732 g_cwb_rpt_person.person_id := p_person_id;
4733 g_cwb_rpt_person.business_group_name := l_bg_and_mgr_name.NAME;
4734 g_cwb_rpt_person.manager_name := l_bg_and_mgr_name.full_name;
4735 g_cwb_rpt_person.pl_name := g_group_plan_name;
4736
4737 IF g_cwb_rpt_person.group_oipl_id <> -1 THEN
4738 g_cwb_rpt_person.opt_name := g_cache_group_options (g_cwb_rpt_person.group_oipl_id);
4739 END IF;
4740
4741 g_cwb_rpt_person.error_or_warning_text := substr(benutils.get_message_name || ' '||fnd_message.get,1,2000);
4742
4743 IF l_comp_error THEN
4744 g_cwb_rpt_person.error_or_warning_text := substr(g_cwb_rpt_person.error_or_warning_text ||
4745 ' '||'Person ' || p_person_id || ' failed for compensation',1,2000);
4746 END IF;
4747
4748 IF l_perf_error THEN
4749 g_cwb_rpt_person.amount := null;
4750 g_cwb_rpt_person.units := null;
4751 g_cwb_rpt_person.error_or_warning_text := substr(g_cwb_rpt_person.error_or_warning_text ||
4752 ' '||'Person ' || p_person_id || ' failed for Performance Rating',1,2000);
4753 END IF;
4754
4755 IF l_promo_error THEN
4756 g_cwb_rpt_person.amount := null;
4757 g_cwb_rpt_person.units := null;
4758 g_cwb_rpt_person.error_or_warning_text := substr(g_cwb_rpt_person.error_or_warning_text ||
4759 ' '||'Person ' || p_person_id || ' failed for assignment changes',1,2000);
4760 END IF;
4761 */
4762 g_cache_cwb_sum_person (p_person_id).status := 'E';
4763 g_cache_cwb_sum_person (p_person_id).lf_evt_closed := 'N';
4764 g_cache_cwb_sum_person (p_person_id).country_code := l_emp_num_and_emp_name.legislation_code;
4765 -- g_cache_cwb_rpt_person (g_cache_cwb_rpt_person.COUNT + 1) := g_cwb_rpt_person;
4766
4767 END IF;
4768 WRITE (benutils.get_message_name);
4769 WRITE (fnd_message.get);
4770 WRITE (SQLERRM||' in process_person');
4771 RAISE ben_batch_utils.g_record_error;
4772 END;
4773
4774 -- ============================================================================
4775 -- << Procedure: Do_Multithread >>
4776 -- Description:
4777 -- this is a main procedure to invoke the Compensation Workbench post
4778 -- process.
4779 -- ============================================================================
4780 PROCEDURE do_multithread (
4781 errbuf OUT NOCOPY VARCHAR2
4782 , retcode OUT NOCOPY NUMBER
4783 , p_validate IN VARCHAR2 DEFAULT 'N'
4784 , p_benefit_action_id IN NUMBER
4785 , p_thread_id IN NUMBER
4786 , p_effective_date IN VARCHAR2
4787 , p_audit_log IN VARCHAR2 DEFAULT 'N'
4788 , p_is_force_on_per IN VARCHAR2 DEFAULT 'N'
4789 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
4790 , p_use_rate_start_date IN VARCHAR2 DEFAULT 'N'
4791 )
4792 IS
4793 l_parm c_parameter%ROWTYPE;
4794 l_commit NUMBER;
4795 l_range_id NUMBER;
4796 l_record_number NUMBER := 0;
4797 l_start_person_action_id NUMBER := 0;
4798 l_end_person_action_id NUMBER := 0;
4799 l_effective_date DATE;
4800 l_threads NUMBER;
4801 l_chunk_size NUMBER;
4802 g_max_errors_allowed NUMBER;
4803 BEGIN/*
4804 if(p_thread_id = 4) then
4805 dbms_lock.sleep(30);
4806 end if;*/
4807 g_actn := 'Started do_multithread for the thread ' || p_thread_id;
4808 g_proc := 'do_multithread';
4809 benutils.g_benefit_action_id := p_benefit_action_id;
4810 WRITE (g_actn);
4811 write_h ('=====================do_multithread=============');
4812 write_h ('||Parameter Description ');
4813 write_h ('||p_effective_dates - ' || p_effective_date);
4814 write_h ('||p_validate - ' || p_validate);
4815 write_h ('||p_benefit_action_id - ' || p_benefit_action_id);
4816 write_h ('||p_thread_id - ' || p_thread_id);
4817 write_h ('||p_audit_log - ' || p_audit_log);
4818 write_h ('||p_is_force_on_per - ' || p_is_force_on_per);
4819 write_h ('||p_is_self_service - ' || p_is_self_service);
4820 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
4821 --l_effective_date := TRUNC (TO_DATE (p_effective_date, 'YYYY/MM/DD HH24:MI:SS'));
4822 write_m ('l_effective_date is ' || l_effective_date);
4823 g_actn := 'Put row in fnd_sessions...';
4824 WRITE (g_actn);
4825 write_h ('dt_fndate.change_ses_date with ' || l_effective_date);
4826 dt_fndate.change_ses_date (p_ses_date => l_effective_date, p_commit => l_commit);
4827
4828 IF (l_commit = 1)
4829 THEN
4830 write_h ('The session date is committed...');
4831 COMMIT;
4832 END IF;
4833
4834 g_is_force_on_per := p_is_force_on_per;
4835
4836 OPEN c_parameter (p_benefit_action_id);
4837
4838 FETCH c_parameter
4839 INTO l_parm;
4840
4841 CLOSE c_parameter;
4842
4843 benutils.get_parameter (p_business_group_id => l_parm.business_group_id
4844 , p_batch_exe_cd => 'BENCWBPP'
4845 , p_threads => l_threads
4846 , p_chunk_size => l_chunk_size
4847 , p_max_errors => g_max_errors_allowed
4848 );
4849
4850 g_debug_level := l_parm.debug_messages_flag;
4851
4852 write_m ('Time before processing the ranges '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4853
4854 ---- bug 7126872, global var used by salary api to distinguish unapproved proposal from cwb
4855 IF(l_parm.bft_attribute1 = 'Y') THEN
4856 g_is_cwb_component_plan := 'Y';
4857 END IF;
4858 LOOP
4859 OPEN c_range_for_thread (p_benefit_action_id);
4860
4861 FETCH c_range_for_thread
4862 INTO l_range_id
4863 , l_start_person_action_id
4864 , l_end_person_action_id;
4865
4866 EXIT WHEN c_range_for_thread%NOTFOUND;
4867
4868 CLOSE c_range_for_thread;
4869
4870 IF (l_range_id IS NOT NULL)
4871 THEN
4872 write_h ('Range with range_id ' || l_range_id || ' with Starting person action id '
4873 || l_start_person_action_id
4874 );
4875 write_h (' and Ending Person Action id ' || l_end_person_action_id || ' is selected');
4876 g_actn := 'Marking ben_batch_ranges for range_id ' || l_range_id || ' as processed...';
4877 WRITE (g_actn);
4878
4879 UPDATE ben_batch_ranges ran
4880 SET ran.range_status_cd = 'P'
4881 WHERE ran.range_id = l_range_id;
4882
4883 COMMIT;
4884 END IF;
4885
4886 g_cache_person_process.DELETE;
4887 g_actn := 'Loading person data into g_cache_person_process cache...';
4888 WRITE (g_actn);
4889 WRITE ('Time'||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4890
4891 OPEN c_person_for_thread (p_benefit_action_id
4892 , l_start_person_action_id
4893 , l_end_person_action_id
4894 );
4895
4896 l_record_number := 0;
4897
4898 LOOP
4899 FETCH c_person_for_thread
4900 INTO g_cache_person_process (l_record_number + 1).person_id
4901 , g_cache_person_process (l_record_number + 1).person_action_id
4902 , g_cache_person_process (l_record_number + 1).object_version_number
4903 , g_cache_person_process (l_record_number + 1).per_in_ler_id
4904 , g_cache_person_process (l_record_number + 1).non_person_cd;
4905
4906 EXIT WHEN c_person_for_thread%NOTFOUND;
4907 --
4908 l_record_number := l_record_number + 1;
4909 END LOOP;
4910
4911 CLOSE c_person_for_thread;
4912 WRITE ('Time '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4913
4914 WRITE ('Number of Persons selected in this range ' || g_cache_person_process.COUNT);
4915 write_h ('======Parameters required for processing this person ====');
4916 write_h ('||l_parm.business_group_id ' || l_parm.business_group_id);
4917 write_h ('||l_parm.lf_evt_ocrd_dt ' || l_parm.lf_evt_ocrd_dt);
4918 write_h ('||l_parm.grant_price_val ' || l_parm.grant_price_val);
4919 write_h ('||l_parm.pl_id ' || l_parm.pl_id);
4920 write_h ('||l_parm.debug_messages_flag ' || l_parm.debug_messages_flag);
4921 write_h ('||l_parm.bft_attribute1 ' || l_parm.bft_attribute1);
4922 write_h ('=======================================================');
4923 WRITE ('Time '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4924
4925 IF l_record_number > 0
4926 THEN
4927 FOR l_cnt IN 1 .. l_record_number
4928 LOOP
4929 --
4930 BEGIN
4931 g_actn := 'Calling process_person...';
4932 process_person
4933 (p_validate => p_validate
4934 , p_person_id => g_cache_person_process (l_cnt).person_id
4935 , p_business_group_id => l_parm.business_group_id
4936 , p_effective_date => l_effective_date
4937 , p_lf_evt_ocrd_date => l_parm.lf_evt_ocrd_dt
4938 , p_grant_price_val => l_parm.grant_price_val
4939 , p_plan_id => l_parm.pl_id
4940 , p_group_per_in_ler_id => g_cache_person_process (l_cnt).per_in_ler_id
4941 , p_person_action_id => g_cache_person_process (l_cnt).person_action_id
4942 , p_object_version_number => g_cache_person_process (l_cnt).object_version_number
4943 , p_audit_log => p_audit_log
4944 , p_debug_level => l_parm.debug_messages_flag
4945 , p_process_sal_comp => l_parm.bft_attribute1
4946 , p_employees_in_bg => l_parm.bft_attribute3
4947 , p_is_self_service => p_is_self_service
4948 , p_is_placeholder => g_cache_person_process (l_cnt).non_person_cd
4949 , p_use_rate_start_date => p_use_rate_start_date
4950 );
4951 EXCEPTION
4952 WHEN OTHERS
4953 THEN
4954 WRITE(SQLERRM||' in multithread, caught in process_person call');
4955 IF (g_persons_errored > g_max_errors_allowed)
4956 THEN
4957 g_actn := '<<Compensation Workbench Max Error Limit '||g_max_errors_allowed ||' Reached >> ';
4958 WRITE (g_actn);
4959 fnd_message.set_name ('BEN', 'BEN_93145_MAX_LIMIT_REACHED');
4960 -- removed RAISE ben_batch_utils.g_record_error;
4961 raise g_max_error;
4962 END IF;
4963
4964 NULL;
4965 END;
4966 END LOOP;
4967
4968 write_m ('Time after processing the ranges '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
4969 ELSE
4970 --
4971 g_actn := 'Erroring out since no person is found in range...';
4972 --
4973 fnd_message.set_name ('BEN', 'BEN_91709_PER_NOT_FND_IN_RNG');
4974 fnd_message.set_token ('PROCEDURE', g_proc);
4975 fnd_message.raise_error;
4976 END IF;
4977
4978 COMMIT;
4979 END LOOP;
4980 g_is_cwb_component_plan := 'N';
4981 print_cache;
4982 EXCEPTION
4983 WHEN g_max_error THEN
4984 WRITE(SQLERRM);
4985 print_cache;
4986 table_corrections(p_benefit_action_id);
4987 COMMIT;
4988 raise g_max_error;
4989 WHEN OTHERS
4990 THEN
4991 WRITE(SQLERRM);
4992 print_cache;
4993 table_corrections(p_benefit_action_id);
4994 COMMIT;
4995 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4996 fnd_message.set_token ('PROCEDURE', g_proc);
4997 fnd_message.set_token ('STEP', g_actn);
4998 fnd_message.raise_error;
4999 END;
5000
5001 PROCEDURE process (
5002 errbuf OUT NOCOPY VARCHAR2
5003 , retcode OUT NOCOPY NUMBER
5004 , p_effective_date IN VARCHAR2
5005 , p_validate IN VARCHAR2
5006 , p_pl_id IN NUMBER
5007 , p_lf_evt_orcd_date IN VARCHAR2
5008 , p_person_id IN NUMBER DEFAULT NULL
5009 , p_manager_id IN NUMBER DEFAULT NULL
5010 , p_employees_in_bg IN NUMBER DEFAULT NULL
5011 , p_grant_price_val IN NUMBER DEFAULT NULL
5012 , p_audit_log IN VARCHAR2 DEFAULT 'N'
5013 , p_hidden_audit_log IN VARCHAR2
5014 , p_debug_level IN VARCHAR2 DEFAULT 'L'
5015 , p_bg_id IN NUMBER
5016 , p_is_multi_thread IN VARCHAR2 DEFAULT 'Y'
5017 , p_is_force_on_per IN VARCHAR2 DEFAULT 'N'
5018 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
5019 , p_person_selection_rule_id IN NUMBER DEFAULT NULL
5020 , p_use_rate_start_date IN VARCHAR2 DEFAULT 'N'
5021 )
5022 IS
5023 --
5024 -- local variable declaration.
5025 --
5026 l_effective_date DATE;
5027 l_commit NUMBER;
5028 l_chunk_size NUMBER;
5029 l_request_id NUMBER;
5030 l_threads NUMBER;
5031 l_benefit_action_id NUMBER;
5032 l_object_version_number NUMBER;
5033 l_num_ranges NUMBER := 0;
5034 l_num_persons NUMBER := 0;
5035 l_comp_reason_count NUMBER;
5036 l_silent_error EXCEPTION;
5037 l_slave_errored EXCEPTION;
5038 l_process_compents VARCHAR2 (1) := 'N';
5039 l_num_rows NUMBER := 0;
5040 ps_rec c_person_selection%ROWTYPE;
5041 l_person_action_ids g_number_type := g_number_type ();
5042 l_person_ids g_number_type := g_number_type ();
5043 l_per_in_ler_ids g_number_type := g_number_type ();
5044 l_is_placeholder g_number_type := g_number_type ();
5045 l_lf_evt_orcd_date DATE;
5046 l_dummy c_slaves%ROWTYPE;
5047 l_person_ok varchar2(1) := 'Y';
5048 l_err_message varchar2(2000);
5049 l_person_id per_all_people_f.person_id%type;
5050 pl_rec c_placeholder_selection%ROWTYPE;
5051 l_count NUMBER;
5052 BEGIN
5053 g_actn := 'Stating the post-process...';
5054 WRITE (g_actn);
5055 g_proc := g_package || '.process';
5056 g_debug_level := p_debug_level;
5057 l_lf_evt_orcd_date := trunc(fnd_date.canonical_to_date(p_lf_evt_orcd_date));
5058 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
5059 write_h ('=====================process====================');
5060 write_h ('||Parameter Description ');
5061 write_h ('||p_effective_dates - ' || l_effective_date);
5062 write_h ('||p_validate - ' || p_validate);
5063 write_h ('||p_pl_id - ' || p_pl_id);
5064 write_h ('||p_le_orcd_date - ' || l_lf_evt_orcd_date);
5065 write_h ('||p_person_id - ' || p_person_id);
5066 write_h ('||p_manager_id - ' || p_manager_id);
5067 write_h ('||p_employees_in_bg - ' || p_employees_in_bg);
5068 write_h ('||p_grant_price_val - ' || p_grant_price_val);
5069 write_h ('||p_audit_log - ' || p_audit_log);
5070 write_h ('||p_bg_id - ' || p_bg_id);
5071 write_h ('||p_is_multi_thread - ' || p_is_multi_thread);
5072 write_h ('||p_is_force_on_per - ' || p_is_force_on_per);
5073 write_h ('||p_use_rate_start_date - ' || p_use_rate_start_date);
5074 write_h ('||p_is_self_service - ' || p_is_self_service);
5075 write_h ('================================================');
5076 write_m ('l_effective_date is ' || l_effective_date);
5077 g_actn := 'Put row in fnd_sessions...';
5078 WRITE (g_actn);
5079 write_h ('dt_fndate.change_ses_date with ' || l_effective_date);
5080 dt_fndate.change_ses_date (p_ses_date => l_effective_date, p_commit => l_commit);
5081 write_h ('Commit value for dt_fndate is ' || l_commit);
5082
5083 IF (l_commit = 1)
5084 THEN
5085 write_h ('The session date is committed...');
5086 COMMIT;
5087 END IF;
5088
5089 g_actn := 'initializing the process parameters';
5090 WRITE (g_actn);
5091 g_exec_param_rec.persons_selected := 0;
5092 g_exec_param_rec.persons_proc_succ := 0;
5093 g_exec_param_rec.persons_errored := 0;
5094 g_exec_param_rec.lf_evt_closed := 0;
5095 g_exec_param_rec.lf_evt_not_closed := 0;
5096 g_exec_param_rec.business_group_id := p_bg_id;
5097 g_exec_param_rec.start_date := SYSDATE;
5098 g_exec_param_rec.start_time := DBMS_UTILITY.get_time;
5099 g_actn := 'Checking for valid read-only-reason to find salary components...';
5100 WRITE (g_actn);
5101 WRITE ('Time'||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
5102
5103 OPEN c_component_reason (p_pl_id, l_effective_date);
5104 FETCH c_component_reason INTO l_comp_reason_count;
5105 CLOSE c_component_reason;
5106
5107 WRITE ('Time'||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
5108
5109 write_m ('Options with component reason is ' || l_comp_reason_count);
5110
5111 IF l_comp_reason_count > 0 THEN
5112 l_process_compents := 'Y';
5113 END IF;
5114
5115 g_actn := 'Calling ben_batch_utils.ini...';
5116 WRITE (g_actn);
5117 write_h ('ben_batch_utils.ini with PROC_INFO');
5118 ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
5119 g_actn := 'Calling benutils.get_parameter...';
5120 WRITE (g_actn);
5121 write_h ('benutils.get_parameter with ' || p_bg_id || ' ' || 'BENCWBPP' || ' '
5122 || g_max_errors_allowed
5123 );
5124 benutils.get_parameter (p_business_group_id => p_bg_id
5125 , p_batch_exe_cd => 'BENCWBPP'
5126 , p_threads => l_threads
5127 , p_chunk_size => l_chunk_size
5128 , p_max_errors => g_max_errors_allowed
5129 );
5130 write_h ('Values of l_threads is ' || l_threads || ' and l_chunk_size is ' || l_chunk_size);
5131 benutils.g_thread_id := 99; -- need to investigate why this is needed
5132 g_actn := 'Creating benefit actions...';
5133 WRITE (g_actn);
5134 WRITE ('Time'||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
5135 write_h ('=====================Benefit Actions=======================');
5136 write_h ('||Parameter value ');
5137 write_h ('||p_request_id- ' || fnd_global.conc_request_id);
5138 write_h ('||p_program_application_id- ' || fnd_global.prog_appl_id);
5139 write_h ('||p_program_id- ' || fnd_global.conc_program_id);
5140 write_h ('==========================================================');
5141 ben_benefit_actions_api.create_perf_benefit_actions
5142 (p_benefit_action_id => l_benefit_action_id
5143 , p_process_date => l_effective_date
5144 , p_mode_cd => 'W'
5145 , p_derivable_factors_flag => 'NONE'
5146 , p_validate_flag => p_validate
5147 , p_debug_messages_flag => NVL (p_debug_level
5148 , 'N'
5149 )
5150 , p_business_group_id => p_bg_id
5151 , p_no_programs_flag => 'N'
5152 , p_no_plans_flag => 'N'
5153 , p_audit_log_flag => p_audit_log
5154 , p_pl_id => p_pl_id
5155 , p_pgm_id => -9999
5156 , p_lf_evt_ocrd_dt => l_lf_evt_orcd_date
5157 , p_person_id => p_person_id
5158 , p_grant_price_val => p_grant_price_val
5159 , p_object_version_number => l_object_version_number
5160 , p_effective_date => l_effective_date
5161 , p_request_id => fnd_global.conc_request_id
5162 , p_program_application_id => fnd_global.prog_appl_id
5163 , p_program_id => fnd_global.conc_program_id
5164 , p_program_update_date => SYSDATE
5165 , p_bft_attribute1 => l_process_compents
5166 , p_bft_attribute3 => p_employees_in_bg
5167 , p_bft_attribute4 => p_manager_id
5168 );
5169 write ('Benefit Action Id is ' || l_benefit_action_id);
5170 benutils.g_benefit_action_id := l_benefit_action_id;
5171 g_actn := 'Inserting Person Actions...';
5172 WRITE (g_actn);
5173 write_m ('Time before processing the person selections '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
5174
5175 OPEN c_placeholder_selection (p_pl_id
5176 , l_lf_evt_orcd_date
5177 , p_person_id
5178 , p_manager_id
5179 , p_employees_in_bg
5180 , l_effective_date
5181 );
5182
5183 LOOP
5184 FETCH c_placeholder_selection
5185 INTO pl_rec;
5186
5187 EXIT WHEN c_placeholder_selection%NOTFOUND;
5188
5189 l_person_ok := 'Y';
5190 l_person_id :=pl_rec.person_id;
5191
5192 If p_person_selection_rule_id is not NULL then
5193 --
5194 begin
5195 ben_batch_utils.person_selection_rule
5196 (p_person_id => l_person_id
5197 ,p_business_group_id => pl_rec.business_group_id
5198 ,p_person_selection_rule_id=> p_person_selection_rule_id
5199 ,p_effective_date => l_effective_date
5200 ,p_return => l_person_ok
5201 ,p_err_message => l_err_message );
5202
5203 if l_err_message is not null
5204 then
5205 Ben_batch_utils.write(p_text =>
5206 '<< Person id : '||to_char(l_person_id)||' failed.'||
5207 ' Reason : '|| l_err_message ||' >>' );
5208 l_err_message := NULL ;
5209 end if ;
5210 exception
5211 when others then
5212 l_person_ok:='N';
5213 end;
5214 --
5215 End if;
5216
5217
5218 If l_person_ok = 'Y' then
5219
5220 l_num_rows := l_num_rows + 1;
5221 l_num_persons := l_num_persons + 1;
5222 l_person_action_ids.EXTEND (1);
5223 l_person_ids.EXTEND (1);
5224 l_per_in_ler_ids.EXTEND (1);
5225 l_is_placeholder.EXTEND (1);
5226
5227 SELECT ben_person_actions_s.NEXTVAL
5228 INTO l_person_action_ids (l_num_rows)
5229 FROM DUAL;
5230
5231 l_person_ids (l_num_rows) := pl_rec.person_id;
5232 l_per_in_ler_ids (l_num_rows) := pl_rec.per_in_ler_id;
5233 l_is_placeholder (l_num_rows) := 1;
5234
5235 write_h ('============Placeholder Person Header==================');
5236 write_h ('||Person Name ' || pl_rec.full_name);
5237 write_h ('||Business Group ' || pl_rec.NAME);
5238 write_h ('||Person Id ' || pl_rec.person_id);
5239 write_h ('||Per_in_ler_id ' || pl_rec.per_in_ler_id);
5240 write_h ('||Person Action id ' || l_person_action_ids (l_num_rows));
5241 write_h ('=======================================================');
5242
5243 end if;
5244
5245 IF l_num_rows = l_chunk_size
5246 THEN
5247 l_num_ranges := l_num_ranges + 1;
5248 insert_person_actions (p_per_actn_id_array => l_person_action_ids
5249 , p_per_id => l_person_ids
5250 , p_group_per_in_ler_id => l_per_in_ler_ids
5251 , p_benefit_action_id => l_benefit_action_id
5252 , p_is_placeholder => l_is_placeholder
5253 );
5254 l_num_rows := 0;
5255 l_person_action_ids.DELETE;
5256 l_person_ids.DELETE;
5257 l_per_in_ler_ids.DELETE;
5258 l_is_placeholder.DELETE;
5259 END IF;
5260 g_cache_cwb_sum_person (pl_rec.person_id).person_id := pl_rec.person_id;
5261 g_cache_cwb_sum_person (pl_rec.person_id).bg_name := pl_rec.NAME;
5262 g_cache_cwb_sum_person (pl_rec.person_id).bg_id := p_bg_id;
5263 g_cache_cwb_sum_person (pl_rec.person_id).country_code := pl_rec.legislation_code;
5264 g_cache_cwb_sum_person (pl_rec.person_id).person_name := pl_rec.full_name;
5265 g_cache_cwb_sum_person (pl_rec.person_id).benefit_action_id := l_benefit_action_id;
5266 END LOOP;
5267
5268 CLOSE c_placeholder_selection;
5269
5270 OPEN c_person_selection (p_pl_id
5271 , l_lf_evt_orcd_date
5272 , p_person_id
5273 , p_manager_id
5274 , p_employees_in_bg
5275 , l_effective_date
5276 );
5277
5278 LOOP
5279 FETCH c_person_selection
5280 INTO ps_rec;
5281
5282 EXIT WHEN c_person_selection%NOTFOUND;
5283
5284 l_person_ok := 'Y';
5285 l_person_id :=ps_rec.person_id;
5286
5287 If p_person_selection_rule_id is not NULL then
5288 --
5289 begin
5290 ben_batch_utils.person_selection_rule
5291 (p_person_id => l_person_id
5292 ,p_business_group_id => ps_rec.business_group_id
5293 ,p_person_selection_rule_id=> p_person_selection_rule_id
5294 ,p_effective_date => l_effective_date
5295 ,p_return => l_person_ok
5296 ,p_err_message => l_err_message );
5297
5298 if l_err_message is not null
5299 then
5300 Ben_batch_utils.write(p_text =>
5301 '<< Person id : '||to_char(l_person_id)||' failed.'||
5302 ' Reason : '|| l_err_message ||' >>' );
5303 l_err_message := NULL ;
5304 end if ;
5305 exception
5306 when others then
5307 l_person_ok:='N';
5308 end;
5309 --
5310 End if;
5311
5312
5313 If l_person_ok = 'Y' then
5314
5315 l_num_rows := l_num_rows + 1;
5316 l_num_persons := l_num_persons + 1;
5317 l_person_action_ids.EXTEND (1);
5318 l_person_ids.EXTEND (1);
5319 l_per_in_ler_ids.EXTEND (1);
5320 l_is_placeholder.EXTEND (1);
5321
5322
5323 SELECT ben_person_actions_s.NEXTVAL
5324 INTO l_person_action_ids (l_num_rows)
5325 FROM DUAL;
5326
5327 l_person_ids (l_num_rows) := ps_rec.person_id;
5328 l_per_in_ler_ids (l_num_rows) := ps_rec.per_in_ler_id;
5329 l_is_placeholder(l_num_rows) := 0;
5330
5331 write_h ('=====================Person Header====================');
5332 write_h ('||Person Name ' || ps_rec.full_name);
5333 write_h ('||Business Group ' || ps_rec.NAME);
5334 write_h ('||Person Id ' || ps_rec.person_id);
5335 write_h ('||Per_in_ler_id ' || ps_rec.per_in_ler_id);
5336 write_h ('||Person Action id ' || l_person_action_ids (l_num_rows));
5337 write_h ('=======================================================');
5338
5339 end if;
5340
5341 IF l_num_rows = l_chunk_size
5342 THEN
5343 l_num_ranges := l_num_ranges + 1;
5344 insert_person_actions (p_per_actn_id_array => l_person_action_ids
5345 , p_per_id => l_person_ids
5346 , p_group_per_in_ler_id => l_per_in_ler_ids
5347 , p_benefit_action_id => l_benefit_action_id
5348 , p_is_placeholder => l_is_placeholder
5349 );
5350 l_num_rows := 0;
5351 l_person_action_ids.DELETE;
5352 l_person_ids.DELETE;
5353 l_per_in_ler_ids.DELETE;
5354 l_is_placeholder.DELETE;
5355 END IF;
5356 END LOOP;
5357
5358 CLOSE c_person_selection;
5359
5360 g_person_selected := l_num_rows;
5361 WRITE ('Total no of person selected - ' || g_person_selected);
5362 g_actn := 'Inserting the last range of persons if exists...';
5363 WRITE (g_actn);
5364 write_m ('Time after processing the person selections '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
5365
5366
5367 IF l_num_rows <> 0
5368 THEN
5369 l_num_ranges := l_num_ranges + 1;
5370 insert_person_actions (p_per_actn_id_array => l_person_action_ids
5371 , p_per_id => l_person_ids
5372 , p_group_per_in_ler_id => l_per_in_ler_ids
5373 , p_benefit_action_id => l_benefit_action_id
5374 , p_is_placeholder => l_is_placeholder
5375 );
5376 l_num_rows := 0;
5377 l_person_action_ids.DELETE;
5378 l_person_ids.DELETE;
5379 l_per_in_ler_ids.DELETE;
5380 l_is_placeholder.DELETE;
5381 END IF;
5382
5383 COMMIT;
5384 g_actn := 'Submitting job to con-current manager...';
5385 WRITE (g_actn);
5386 g_actn := 'Preparing for launching concurrent requests';
5387 WRITE (g_actn);
5388 ben_batch_utils.g_num_processes := 0;
5389 ben_batch_utils.g_processes_tbl.DELETE;
5390
5391 write_m ('Time before launching the threads '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
5392
5393 IF l_num_ranges > 1
5394 THEN
5395 FOR l_count IN 1 .. LEAST (l_threads, l_num_ranges) - 1
5396 LOOP
5397 write_h ('=====================Request Parameters===================');
5398 write_h ('||Parameter value ');
5399 write_h ('||argument2- ' || l_benefit_action_id);
5400 write_h ('||argument3- ' || l_count);
5401 write_h ('==========================================================');
5402 l_request_id :=
5403 fnd_request.submit_request (application => 'BEN'
5404 , program => 'BENCWBMT'
5405 , description => NULL
5406 , sub_request => FALSE
5407 , argument1 => p_validate
5408 , argument2 => l_benefit_action_id
5409 , argument3 => l_count
5410 , argument4 => p_effective_date
5411 , argument5 => p_audit_log
5412 , argument6 => p_is_force_on_per
5413 , argument7 => p_is_self_service
5414 , argument8 => p_use_rate_start_date
5415 );
5416 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
5417 ben_batch_utils.g_processes_tbl (ben_batch_utils.g_num_processes) := l_request_id;
5418 write_m ('request id for this thread ' || l_request_id);
5419 COMMIT;
5420 END LOOP;
5421 ELSIF l_num_ranges = 0
5422 THEN
5423 WRITE ('<< No Person got selected with above selection criteria >>');
5424 fnd_message.set_name ('BEN', 'BEN_91769_NOONE_TO_PROCESS');
5425 fnd_message.set_token ('PROC', g_proc);
5426 RAISE l_silent_error;
5427 END IF;
5428
5429 write_m ('Time after launching the threads '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
5430
5431 write_h ('=====================do_multithread in Process============');
5432 write_h ('||Parameter value ');
5433 write_h ('||p_benefit_action_id- ' || l_benefit_action_id);
5434 write_h ('||p_thread_id- ' || (l_threads + 1));
5435 write_h ('==========================================================');
5436 do_multithread (errbuf => errbuf
5437 , retcode => retcode
5438 , p_validate => p_validate
5439 , p_benefit_action_id => l_benefit_action_id
5440 , p_thread_id => l_threads + 1
5441 , p_effective_date => p_effective_date
5442 , p_audit_log => p_audit_log
5443 , p_is_force_on_per => p_is_force_on_per
5444 , p_is_self_service => p_is_self_service
5445 , p_use_rate_start_date => p_use_rate_start_date
5446 );
5447 g_actn := 'Calling ben_batch_utils.check_all_slaves_finished...';
5448 WRITE (g_actn);
5449
5450 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
5451 g_actn := 'Calling end_process...';
5452 WRITE (g_actn);
5453
5454 write_h ('=====================End Process==========');
5455 write_h ('||Parameter value ');
5456 write_h ('||p_pl_id- ' || p_pl_id);
5457 write_h ('||p_lf_evt_orcd_date- ' || l_lf_evt_orcd_date);
5458 write_h ('==========================================================');
5459
5460 process_access( p_pl_id
5461 ,l_lf_evt_orcd_date
5462 ,p_validate
5463 );
5464
5465 write_h ('=====================End Process==========');
5466 write_h ('||Parameter value ');
5467 write_h ('||p_benefit_action_id- ' || l_benefit_action_id);
5468 write_h ('||p_person_selected- ' || l_num_persons);
5469 write_h ('==========================================================');
5470 end_process (p_benefit_action_id => l_benefit_action_id
5471 , p_person_selected => l_num_persons
5472 , p_business_group_id => p_bg_id
5473 );
5474 table_corrections(l_benefit_action_id);
5475 g_actn := 'Finished Process Procedure...';
5476 WRITE (g_actn);
5477
5478 BEGIN
5479 For l_count in 1..ben_batch_utils.g_num_processes loop
5480 open c_slaves(ben_batch_utils.g_processes_tbl(l_count));
5481 fetch c_slaves into l_dummy;
5482 If c_slaves%found then
5483 close c_slaves;
5484 raise l_slave_errored;
5485 exit;
5486 End if;
5487 Close c_slaves;
5488 End loop;
5489 EXCEPTION
5490 WHEN l_slave_errored THEN
5491 --fnd_message.set_name ('BEN', 'BEN_94890_CWB_PROC_SLAVE_ERROR');
5492 --fnd_message.set_name('BEN', 'BEN_93145_MAX_LIMIT_REACHED');
5493 g_actn:= 'slave processes';
5494 raise g_slave_error;
5495 END;
5496
5497 EXCEPTION
5498 --
5499 WHEN l_silent_error
5500 THEN
5501 WRITE (fnd_message.get);
5502 IF (l_num_ranges > 0) THEN
5503 WRITE('END_PROCESS');
5504 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
5505 end_process (p_benefit_action_id => l_benefit_action_id
5506 , p_person_selected => l_num_persons
5507 , p_business_group_id => p_bg_id
5508 );
5509 END IF;
5510 --
5511 WHEN g_slave_error THEN
5512 WRITE (fnd_message.get);
5513 WRITE (SQLERRM);
5514 WRITE ('Big Error Occurred');
5515 IF (l_num_ranges > 0) THEN
5516 WRITE('END_PROCESS');
5517 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
5518 end_process (p_benefit_action_id => l_benefit_action_id
5519 , p_person_selected => l_num_persons
5520 , p_business_group_id => p_bg_id
5521 );
5522 END IF;
5523 fnd_message.clear();
5524 fnd_message.set_name('BEN', 'BEN_94890_CWB_PROC_SLAVE_ERROR');
5525 --fnd_message.set_name('BEN', 'BEN_93145_MAX_LIMIT_REACHED');
5526 --fnd_message.raise_error;
5527 RAISE_APPLICATION_ERROR(-20001,fnd_global.Newline||fnd_message.get||fnd_global.Newline);
5528 --
5529 WHEN g_max_error THEN
5530 WRITE (fnd_message.get);
5531 WRITE (SQLERRM);
5532 WRITE ('Big Error Occurred');
5533 IF (l_num_ranges > 0) THEN
5534 WRITE('END_PROCESS');
5535 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
5536 end_process (p_benefit_action_id => l_benefit_action_id
5537 , p_person_selected => l_num_persons
5538 , p_business_group_id => p_bg_id
5539 );
5540 END IF;
5541
5542 fnd_message.set_name('BEN', 'BEN_93145_MAX_LIMIT_REACHED');
5543 --fnd_message.raise_error;
5544 RAISE_APPLICATION_ERROR(-20001,fnd_global.Newline||fnd_message.get||fnd_global.Newline);
5545 --
5546 WHEN OTHERS THEN
5547 WRITE (fnd_message.get);
5548 WRITE (SQLERRM);
5549 WRITE ('Big Error Occurred');
5550 IF (l_num_ranges > 0) THEN
5551 WRITE('END_PROCESS');
5552 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
5553 end_process (p_benefit_action_id => l_benefit_action_id
5554 , p_person_selected => l_num_persons
5555 , p_business_group_id => p_bg_id
5556 );
5557 END IF;
5558
5559 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
5560 fnd_message.set_token ('PROCEDURE', g_proc);
5561 fnd_message.set_token ('STEP', g_actn);
5562 fnd_message.raise_error;
5563 END;
5564 END;