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