[Home] [Help]
PACKAGE BODY: APPS.BEN_BATCH_UTILS
Source
1 package body ben_batch_utils as
2 /* $Header: benrptut.pkb 120.5.12010000.2 2008/08/05 14:52:56 ubhat ship $ */
3 /* ===========================================================================
4 * Name:
5 * Batch_utils
6 * Purpose:
7 * This package is provide all batch utility and data structure to simply
8 * batch process.
9 * History:
10 * Date Who Version What?
11 * ----------- --------- ------- -----------------------------------------
12 * 20 Nov 1998 Hdang 115.0 Created.
13 * 11 Dec 1998 Hdang 115.1 Add new functions (ret_str).
14 * 15 Dec 1998 Hdang 115.2 Add text into error message, and call Graham
15 * new procedure to load proc info into table.
16 * 18 Dec 1998 jcarpent 115.3 Change c_person cursor
17 * 22 Dec 1998 Hdang 115.4 Add audit_log into print_paramater.
18 * 23 Dec 1998 Hdang 115.5 Move person info into header.
19 * 29 Dec 1998 Hdang 115.6 Remove ini_proc_info from ini.
20 * 30 Dec 1998 Hdang 115.7 Add actn_cd in comp_cache.
21 * 06-Jan-1999 Hdang 115.8 Added new procedure for generic reports.
22 * 13-Jan-1999 Hdang 115.12 Set l_all = 'All'
23 * 03-Mar-1999 Stee 115.13 Removed dbms_output.put_line.
24 * 22-Mar-1999 TMathers 115.16 CHanged -MON- to /MM/
25 * 18-May-1999 jcarpent 115.17 Use trunc not to_char for zero of time.
26 * 20-JUL-1999 Gperry 115.18 genutils -> benutils package rename.
27 * 27-JUL-1999 mhoyes 115.19 - Changed g_report_rec ref it ben_type.
28 * 28-JUL-1999 mhoyes 115.20 - Made oipl_id hashing use > rather than <
29 * 19-Oct-1999 maagrawa 115.21 - Modified procedure write_error_rec to
30 * get correct error code.
31 * - Write the log information to the log file.
32 * 03-Nov-1999 lmcdonal 115.22 Added non_person_cd to end_process,
33 * write_logfile, create_restart...
34 *
35 * 18-JAN-00 pbodla 115.23 Fixed bug 4146(WWBUG 1120687)
36 * p_business_group_id added to benutils.formula
37 * call.
38 * 04-APR-00 mmogel 115.24 Added tokens to messages to make them
39 * more meaningful to the user
40 * 11-APR-00 gperry 115.25 Added application id to get over FIDO
41 * dup rows issue.
42 * 18-APR-00 shdas 115.26 changed c1 cursor(cache_comp_object) to
43 * outer join oipl with result so that rslts
44 * without oipls appear in the log and audit reports.(2641)
45 * 12-Mar-01 pbodla 115.14 - Bug 1674123 : Modified print_parameters
46 * mode_cd is used close_cd for Close
47 * enrollment process.
48 * 21-JAN-02 aprabhak 115.27 -added enrt_perd_id to print_parameters
49 * 12-Mar-02 maagrawa 115.28 - Added missing dbdrv command.
50 * 14-Mar-02 rpillay 115.28 - UTF8 Changes Bug 2254683
51 * 08-Jun-02 pabodla 115.29 Do not select the contingent worker
52 * assignment when assignment data is
53 * fetched.
54 * 18-Jun-02 ikasire 115.30 Bug 2394141 fixes
55 * 26-Dec-02 rpillay 115.33 NOCOPY changes
56 * 13-Feb-03 stee 115.34 HR MLS changes.
57 * 14-Feb-03 tmathers 115.35 Added whenever oserror.
58 * 02-Jun-03 glingapp 115.36 bug 2978945 Added function rows_exist. This is
59 * called to check for child records of derived
60 * factors.
61 * 30-Jun-03 vsethi 115.37 Changed reference for table ben_rptg_grp
62 * MLS compliant view ben_rptg_grp_v
63 * 12-Jan-04 vvprabhu 115.38 Changed the calls to dbms_describe.dbms_procedure
64 * to hr_general.describe_procedure in procedure
65 * get_rpt_header
66 * 21-Jun-04 kmahendr 115.39 Corrected Prompt for legal Entity.
67 * 20-Aug-04 nhunur 115.40 Added a procedure for person selection rule
68 * with proper error handling.
69 * 02-Nov-04 abparekh 115.41 Bug 3517604 - Added p_date_From to procedure
70 * standard_header
71 * 03-Nov-06 swjain 115.42 Bug 5331889 - passed person_id as input param
72 * in person_selection_rule and added input1 as
73 * additional param for future use
74 * 16-aug-06 gsehgal 115.43 Bug: 5450842 -- now p_mode will not be printed when
75 * passed as null
76 * 12-Dec-06 nkkrishn 115.44 5643310 - Invalid Person Records in Person Seleciton
77 * Rule will now be logged instead of erroring out the
78 * entire process
79 * 22-Jun-07 nhunur 115.45 perf changes
80 * 09-Aug-07 vvprabhu 115.23 Bug 5857493 - added g_audit_flag to
81 * control person selection rule error logging
82 * 22-Feb-2008 rtagarra 115.24 Bug 6840074
83 * ===========================================================================
84 */
85 --
86 -- Global variables declaration.
87 --
88 g_package varchar2(30) := 'ben_batch_utils.';
89 g_proc_info g_process_information_rec;
90 g_cache_person_types g_cache_person_types_rec;
91 g_pgm_tbl g_pgm_table;
92 g_pl_tbl g_pl_table;
93 g_pl_typ_tbl g_pl_typ_table;
94 g_opt_tbl g_opt_table;
95 --
96 -- ============================================================================
97 -- <<Function: ret_str>>
98 -- ============================================================================
99 --
100 Function ret_str(p_str varchar2, p_len number default 30) return varchar2 is
101 Begin
102 return(rpad(nvl(substr(p_str,1,p_len),' '),p_len));
103 End;
104 --
105 Function ret_str(p_num number, p_len number default 15) return varchar2 is
106 Begin
107 return(rpad(nvl(to_char(p_num),' '),p_len));
108 End;
109 --
110 Function ret_str(p_date date, p_len number default 12) return varchar2 is
111 Begin
112 return(rpad(nvl(to_char(p_date,'DD/MM/YYYY'),' '),p_len));
113 End;
114 --
115 -- ============================================================================
116 -- <<Ini_person>>
117 -- ============================================================================
118 --
119 Procedure ini_person is
120 L_proc varchar2(80) := g_package||'.ini_person';
121 Begin
122 hr_utility.set_location ('Entering '|| l_proc,5);
123 g_cache_person := NULL;
124 hr_utility.set_location ('Leaving ' || l_proc,10);
125 End ini_person;
126 --
127 -- ============================================================================
128 -- <<Ini_Comp_obj>>
129 -- ============================================================================
130 --
131 Procedure ini_comp_obj is
132 L_proc varchar2(80) := g_package||'.ini_comp_obj';
133 Begin
134 hr_utility.set_location ('Entering ' || l_proc, 5);
135 g_cache_comp.delete;
136 g_cache_comp_cnt := 0;
137 hr_utility.set_location ('Leaving ' || l_proc, 10);
138 End ini_comp_obj ;
139 --
140 -- ============================================================================
141 -- <<Cache_Comp_obj>>
142 -- ============================================================================
143 --
144 Procedure Cache_comp_obj(p_pgm_id in number Default NULL
145 ,p_pl_typ_id in Number Default NULL
146 ,p_pl_id in Number Default NULL
147 ,p_oipl_id in Number Default NULL
148 ,p_opt_id in number Default NULL
149 ,p_bnft_amt in number Default NULL
150 ,p_uom in varchar2 Default NULL
151 ,p_cst_amt in number Default NULL
152 ,p_credit_amt in number Default NULL
153 ,p_cvg_strt_dt in date Default NULL
154 ,p_cvg_thru_dt in date Default hr_api.g_eot
155 ,p_prtt_enrt_rslt_id in number default NULL
156 ,p_effective_date in date
157 ,P_actn_cd in varchar2
158 ,p_suspended in varchar2 default 'N'
159 ) is
160 Cursor c1 is
161 Select a.pgm_id, a.pl_typ_id, a.pl_id, a.oipl_id, b.opt_id
162 ,a.bnft_amt, a.uom
163 ,a.enrt_cvg_strt_dt, a.enrt_cvg_thru_dt
164 From ben_prtt_enrt_rslt_f a
165 ,ben_oipl_f b
166 Where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
167 and a.prtt_enrt_rslt_stat_cd is null
168 and p_effective_date between
169 a.effective_start_date and a.effective_end_date
170 and a.oipl_id = b.oipl_id(+)
171 and p_effective_date between
172 nvl(b.effective_start_date,p_effective_date)
173 and nvl(b.effective_end_date,p_effective_date)
174 ;
175 l_rec c1%rowtype;
176 l_cnt Number(15) := g_cache_comp_cnt;
177 L_proc varchar2(80) := g_package||'.Cache_comp_obj';
178 Begin
179 hr_utility.set_location ('Entering '||l_proc,10);
180 l_cnt := l_cnt + 1;
181 If (p_prtt_enrt_rslt_id is not NULL) then
182 Open c1;
183 fetch c1 into l_rec;
184 close c1;
185 g_cache_comp(l_cnt).pgm_id := l_rec.pgm_id;
186 g_cache_comp(l_cnt).pl_typ_id := l_rec.pl_typ_id;
187 g_cache_comp(l_cnt).pl_id := l_rec.pl_id;
188 g_cache_comp(l_cnt).oipl_id := l_rec.oipl_id;
189 g_cache_comp(l_cnt).opt_id := l_rec.opt_id;
190 g_cache_comp(l_cnt).bnft_amt := l_rec.bnft_amt;
191 g_cache_comp(l_cnt).uom := l_rec.uom;
192 g_cache_comp(l_cnt).cvg_strt_dt := l_rec.enrt_cvg_strt_dt;
193 g_cache_comp(l_cnt).cvg_thru_dt := l_rec.enrt_cvg_thru_dt;
194 g_cache_comp(l_cnt).prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
195 g_cache_comp(l_cnt).actn_cd := p_actn_cd;
196 Else
197 g_cache_comp(l_cnt).pgm_id := p_pgm_id;
198 g_cache_comp(l_cnt).pl_typ_id := p_pl_typ_id;
199 g_cache_comp(l_cnt).pl_id := p_pl_id;
200 g_cache_comp(l_cnt).oipl_id := p_oipl_id;
201 g_cache_comp(l_cnt).opt_id := p_opt_id;
202 g_cache_comp(l_cnt).bnft_amt := p_bnft_amt;
203 g_cache_comp(l_cnt).uom := p_uom;
204 g_cache_comp(l_cnt).cvg_strt_dt := p_cvg_strt_dt;
205 g_cache_comp(l_cnt).cvg_thru_dt := p_cvg_thru_dt;
206 g_cache_comp(l_cnt).prtt_enrt_rslt_id := NULL;
207 g_cache_comp(l_cnt).actn_cd := p_actn_cd;
208 End if;
209 If p_actn_cd = 'UPD' then
210 g_cache_comp(l_cnt).upd_flag := TRUE;
211 Elsif p_actn_cd = 'INS' then
212 g_cache_comp(l_cnt).ins_flag := TRUE;
213 Elsif p_actn_cd = 'DEL' then
214 g_cache_comp(l_cnt).del_flag := TRUE;
215 Elsif p_actn_cd = 'DEF' then
216 g_cache_comp(l_cnt).def_flag := TRUE;
217 End if;
218 If (p_suspended = 'Y') then
219 g_cache_comp(l_cnt).susp_flag := TRUE;
220 End if;
221 g_cache_comp_cnt := l_cnt;
222 hr_utility.set_location ('Leaving '||l_proc,10);
223 End Cache_comp_obj;
224 --
225 -- ============================================================================
226 -- <<get_actn_cd>>
227 -- ============================================================================
228 --
229 Function get_actn_cd(p_def_flag Boolean
230 ,p_upd_flag Boolean
231 ,p_ins_flag Boolean
232 ) return varchar2 is
233 l_def varchar2(10);
234 l_upd varchar2(10);
235 l_ins varchar2(10);
236 Begin
237 If (p_def_flag) then
238 l_def := 'Defaulted ';
239 End if;
240 If (p_upd_flag) then
241 l_upd := 'Updated ';
242 End if;
243 If (p_ins_flag) then
244 l_ins := 'Inserted ';
245 End if;
246 return '(' || l_def || l_upd || l_ins || ')';
247 End;
248 --
249 -- ============================================================================
250 -- <<Write_Comp>>
251 -- ============================================================================
252 --
253 Procedure write_comp (p_business_group_id in number
254 ,p_effective_date in date
255 )is
256 l_proc varchar2(80) := g_package||'.write_comp';
257 l_output varchar2(3000); -- UTF8 Change Bug 2254683 varchar2(1000)
258 l_first Boolean := TRUE;
259 Begin
260 hr_utility.set_location ('Entering '||l_proc,5 );
261 For i in 1..g_cache_comp_cnt loop
262 If (g_cache_comp(i).def_flag
263 or g_cache_comp(i).ins_flag
264 or g_cache_comp(i).upd_flag
265 ) then
266 If (l_first) then
267 write(p_text => 'Default Election Information');
268 write(p_text => '****************************');
269 l_first := FALSE;
270 End if;
271 l_output := '>> ' ||
272 get_pgm_name
273 (p_pgm_id => g_cache_comp(i).pgm_id
274 ,p_business_group_id => p_business_group_id
275 ,p_effective_date => p_effective_date ) || ', ' ||
276 get_pl_typ_name
277 (p_pl_typ_id => g_cache_comp(i).pl_typ_id
278 ,p_business_group_id => p_business_group_id
279 ,p_effective_date => p_effective_date ) || ', ' ||
280 get_pl_name
281 (p_pl_id => g_cache_comp(i).pl_id
282 ,p_business_group_id => p_business_group_id
283 ,p_effective_date => p_effective_date ) || ', ' ||
284 get_opt_name
285 (p_oipl_id => g_cache_comp(i).oipl_id
286 ,p_business_group_id => p_business_group_id
287 ,p_effective_date => p_effective_date ) ||
288 ', Start:' || to_char(g_cache_comp(i).cvg_strt_dt, 'DD/MM/YYYY') ||
289 ', End:'||to_char(g_cache_comp(i).cvg_thru_dt,'DD/MM/YYYY') ||
290 get_actn_cd(p_def_flag => g_cache_comp(i).def_flag
291 ,p_upd_flag => g_cache_comp(i).upd_flag
292 ,p_ins_flag => g_cache_comp(i).ins_flag
293 )
294 ;
295 write(p_text => l_output);
296 End if;
297 End loop;
298 l_first := TRUE;
299 For i in 1..g_cache_comp_cnt loop
300 If (g_cache_comp(i).del_flag) then
301 If (l_first) then
302 write(p_text => 'De-enrolled Election Information');
303 write(p_text => '********************************');
304 l_first := FALSE;
305 End if;
306 l_output := '>> ' ||
307 get_pgm_name
308 (p_pgm_id => g_cache_comp(i).pgm_id
309 ,p_business_group_id => p_business_group_id
310 ,p_effective_date => p_effective_date ) || ', ' ||
311 get_pl_typ_name
312 (p_pl_typ_id => g_cache_comp(i).pl_typ_id
313 ,p_business_group_id => p_business_group_id
314 ,p_effective_date => p_effective_date ) || ', ' ||
315 get_pl_name
316 (p_pl_id => g_cache_comp(i).pl_id
317 ,p_business_group_id => p_business_group_id
318 ,p_effective_date => p_effective_date ) || ', ' ||
319 get_opt_name
320 (p_oipl_id => g_cache_comp(i).oipl_id
321 ,p_business_group_id => p_business_group_id
322 ,p_effective_date => p_effective_date ) ||
323 ', Start:' || to_char(g_cache_comp(i).cvg_strt_dt, 'DD/MM/YYYY') ||
324 ', End:'||to_char(g_cache_comp(i).cvg_thru_dt,'DD/MM/YYYY') ;
325 write(p_text => l_output);
326 End if;
327 End loop;
328 hr_utility.set_location ('Leaving '||l_proc,10);
329 End write_comp;
330 --
331 -- ============================================================================
332 -- <<Ini_Comp_obj_name>>
333 -- ============================================================================
334 --
335 Procedure ini_comp_obj_name is
336 L_proc varchar2(80) := g_package||'.ini_comp_obj_name';
337 Begin
338 g_pgm_tbl.delete;
339 g_pl_tbl.delete;
340 g_pl_typ_tbl.delete;
341 g_opt_tbl.delete;
342 End ini_comp_obj_name;
343 --
344 -- ============================================================================
345 -- <<Ini_proc_info>>
346 -- ============================================================================
347 --
348 Procedure ini_proc_info is
349 L_proc varchar2(80) := g_package||'.ini_proc_info';
350 Begin
351 hr_utility.set_location ('Entering '||l_proc,05);
352 g_proc_info := NULL;
353 g_proc_info.start_date := sysdate;
354 g_proc_info.start_time_numeric := dbms_utility.get_time;
355 g_proc_info.num_persons_selected := 0;
356 g_proc_info.num_persons_errored := 0;
357 g_proc_info.num_persons_unprocessed := 0;
358 g_proc_info.num_persons_processed_succ := 0;
359 g_proc_info.num_persons_processed := 0;
360 g_num_processes := 0;
361 g_processes_tbl.delete;
362 hr_utility.set_location ('Leaving '||l_proc,10);
363 End ini_proc_info;
364
365 --
366 -- ============================================================================
367 -- <<Ini>>
368 -- ============================================================================
369 --
370 Procedure ini(p_actn_cd varchar2 default hr_api.g_varchar2) is
371 L_proc varchar2(80) := g_package||'.ini';
372 Begin
373 hr_utility.set_location ('Entering '||l_proc,05);
374 If(p_actn_cd = hr_api.g_varchar2) then
375 ini_comp_obj;
376 ini_person;
377 ini_comp_obj_name;
378 Elsif(upper(substr(p_actn_cd,1,6)) = 'PERSON' ) then
379 ini_person;
380 Elsif(upper(substr(p_actn_cd,1,8)) = 'COMP_OBJ' ) then
381 ini_comp_obj;
382 Elsif(upper(substr(p_actn_cd,1,9)) = 'COMP_NAME') then
383 ini_comp_obj_name;
384 Elsif(upper(substr(p_actn_cd,1,9)) = 'PROC_INFO') then
385 ini_proc_info;
386 End if;
387 hr_utility.set_location ('Leaving '||l_proc,10);
388 End ini;
389 --
390 -- ============================================================================
391 -- << Rpt_error >>
392 -- ============================================================================
393 --
394 procedure rpt_error (p_proc in varchar2
395 ,p_last_actn in varchar2
396 ,p_rpt_flag in boolean default FALSE
397 ) is
398 L_proc varchar2(80) := g_package||'.rpt_error';
399 Begin
400 If (p_rpt_flag ) then
401 write(p_text => '<<<Fail in '||p_proc||' while '|| p_last_actn||'>>>');
402 End if;
403 hr_utility.set_location('> Fail in ' || p_proc, 999 );
404 hr_utility.set_location('>> While ' || p_last_actn, 999);
405 End rpt_error;
406 --
407 -- ============================================================================
408 -- << Function: get_pgm_name >>
409 -- ============================================================================
410 --
411 Function get_pgm_name(p_pgm_id in number
412 ,p_business_group_id in number
413 ,p_effective_date in date
414 ,p_batch_flag in boolean default FALSE
415 ) return varchar2 is
416 cursor c1 is
417 Select pgm_id, name
418 From ben_pgm_f
419 Where pgm_id = p_pgm_id
420 And business_group_id = p_business_group_id
421 And p_effective_date between
422 effective_start_date and effective_end_date
423 ;
424 ret_str varchar2(500); --UTF8 Change Bug 2254683 varchar2(80)
425 l_proc varchar2(80) := g_package || '.get_pgm_name';
426 l_actn varchar2(80);
427 l_idx binary_integer;
428 l_fetch Boolean := FALSE;
429 begin
430 hr_utility.set_location ('Entering '||l_proc,10);
431 --
432 l_actn := 'Retrieve program from cache or database...';
433 --
434 If (p_pgm_id is NULL) then
435 ret_str := 'NO PROGRAM';
436 Elsif (p_pgm_id > g_mx_binary_integer) then
437 l_idx := -1 * mod(p_pgm_id, g_mx_binary_integer);
438 Else
439 l_idx := p_pgm_id;
440 End if;
441 If (g_pgm_tbl.exists(l_idx)) then
442 ret_str := g_pgm_tbl(l_idx).name;
443 If (g_pgm_tbl(l_idx).pgm_id <> p_pgm_id) then
444 l_fetch := TRUE;
445 End if;
446 Else
447 l_fetch := TRUE;
448 End if;
449 If (l_fetch) then
450 --
451 l_actn := 'Getting program from database...';
452 --
453 open c1;
454 fetch c1 into g_pgm_tbl(l_idx);
455 If c1%notfound then
456 ret_str := 'PGM NOT FOUND';
457 Else
458 ret_str := g_pgm_tbl(l_idx).name;
459 End if;
460 close c1;
461 End if;
462 hr_utility.set_location ('Leaving '||l_proc,70);
463 return ret_str;
464 Exception
465 when others then
466 rpt_error(p_proc => l_proc
467 ,p_last_actn => l_actn
468 ,p_rpt_flag => p_batch_flag
469 );
470 fnd_message.raise_error;
471 End get_pgm_name;
472 --
473 -- ============================================================================
474 -- << Function: get_pl_type_name >>
475 -- ============================================================================
476 --
477 Function get_pl_typ_name(p_pl_typ_id in number
478 ,p_business_group_id in number
479 ,p_effective_date in Date
480 ,p_batch_flag in boolean default FALSE
481 ) return varchar2 is
482 Cursor c1 is
483 Select pl_typ_id, name
484 From ben_pl_typ_f
485 Where pl_typ_id = p_pl_typ_id
486 And business_group_id = p_business_group_id
487 And p_effective_date between
488 effective_start_date and effective_end_date
489 ;
490 ret_str varchar2(500); --UTF8 Change Bug 2254683 varchar2(80)
491 l_proc varchar2(80) := g_package || '.get_pl_typ_name';
492 l_actn varchar2(80);
493 l_idx Binary_integer;
494 l_fetch Boolean := FALSE;
495 Begin
496 hr_utility.set_location ('Entering '||l_proc,10);
497 l_actn := 'Initializing...';
498 If (p_pl_typ_id is NULL) then
499 ret_str := 'NO PLAN TYPE';
500 Elsif (p_pl_typ_id > g_mx_binary_integer) then
501 l_idx := -1 * mod(p_pl_typ_id, g_mx_binary_integer);
502 Else
503 l_idx := p_pl_typ_id;
504 End if;
505 --
506 l_actn := 'Getting plan type name from cache or database...';
507 If g_pl_typ_tbl.exists(l_idx) then
508 ret_str := g_pl_typ_tbl(l_idx).name;
509 If (p_pl_typ_id <> g_pl_typ_tbl(l_idx).pl_typ_id) then
510 l_fetch := TRUE;
511 End if;
512 Else
513 l_fetch := TRUE;
514 End if;
515 If (l_fetch) then
516 open c1;
517 fetch c1 into g_pl_typ_tbl(l_idx);
518 If c1%notfound then
519 ret_str := 'PLAN TYPE NOT FOUND';
520 Else
521 ret_str := g_pl_typ_tbl(l_idx).name;
522 End if;
523 close c1;
524 End if;
525 hr_utility.set_location ('Leaving '||l_proc,70);
526 return ret_str;
527 Exception
528 When others then
529 rpt_error(p_proc => l_proc
530 ,p_last_actn => l_actn
531 ,p_rpt_flag => p_batch_flag
532 );
533 fnd_message.raise_error;
534 End get_pl_typ_name;
535 --
536 -- ============================================================================
537 -- << Function: get_pl_name >>
538 -- ============================================================================
539 --
540 Function get_pl_name(p_pl_id in number
541 ,p_business_group_id in number
542 ,p_effective_date in date
543 ,p_batch_flag in boolean default FALSE
544 ) return varchar2 is
545 Cursor c1 is
546 Select pl_id, name
547 From ben_pl_f
548 Where pl_id = p_pl_id
549 And business_group_id = p_business_group_id
550 And p_effective_date between
551 effective_start_date and effective_end_date
552 ;
553 ret_str varchar2(500); --UTF8 Chnage Bug 2254683 varchar2(80)
554 l_proc varchar2(80) := g_package || '.get_pl_name';
555 l_actn varchar2(80);
556 l_idx binary_integer;
557 l_fetch Boolean := FALSE;
558 Begin
559 hr_utility.set_location ('Entering '||l_proc,10);
560 --
561 l_actn := 'Initializing...';
562 --
563 If (p_pl_id is NULL) then
564 ret_str := 'NO PLAN';
565 Elsif (p_pl_id > g_mx_binary_integer) then
566 l_idx := -1 * mod(p_pl_id, g_mx_binary_integer);
567 Else
568 l_idx := p_pl_id;
569 End if;
570 --
571 l_actn := 'Getting plan name from cache or database...';
572 --
573 If g_pl_tbl.exists(l_idx) then
574 ret_str := g_pl_tbl(l_idx).name;
575 If (p_pl_id <> g_pl_tbl(l_idx).pl_id) then
576 l_fetch := TRUE;
577 End if;
578 Else
579 l_fetch := TRUE;
580 End if;
581 If (l_fetch) then
582 open c1;
583 fetch c1 into g_pl_tbl(l_idx);
584 If c1%notfound then
585 ret_str := 'PLAN NOT FOUND';
586 Else
587 ret_str := g_pl_tbl(l_idx).name;
588 End if;
589 close c1;
590 End if;
591 hr_utility.set_location ('Leaving '||l_proc,70);
592 return ret_str;
593 Exception
594 when others then
595 rpt_error(p_proc => l_proc
596 ,p_last_actn => l_actn
597 ,p_rpt_flag => p_batch_flag
598 );
599 fnd_message.raise_error;
600 End get_pl_name;
601 --
602 -- ============================================================================
603 -- << Procedure: get_opt_name >>
604 -- ============================================================================
605 --
606 Function get_opt_name(p_oipl_id in number
607 ,p_business_group_id in number
608 ,p_effective_date in date
609 ,p_batch_flag in boolean default FALSE
610 ) return varchar2 is
611 cursor c1 is
612 Select oipl.oipl_id, opt.opt_id, opt.name
613 From ben_oipl_f oipl
614 ,ben_opt_f opt
615 Where oipl.oipl_id = p_oipl_id
616 And oipl.opt_id = opt.opt_id
617 And oipl.business_group_id = p_business_group_id
618 And opt.business_group_id = p_business_group_id
619 And p_effective_date between
620 opt.effective_start_date and opt.effective_end_date
621 And p_effective_date between
622 oipl.effective_start_date and oipl.effective_end_date
623 ;
624 ret_str varchar2(500); --UTF8 Change Bug 2254683 varchar2(80)
625 l_proc varchar2(80):= g_package || '.get_opt_name';
626 l_actn varchar2(80);
627 l_idx binary_integer;
628 l_fetch Boolean := FALSE;
629 Begin
630 hr_utility.set_location ('Entering '||l_proc,10);
631 --
632 l_actn := 'Initializing...';
633 --
634 If (p_oipl_id is NULL) then
635 ret_str := 'NO OPTION';
636 Elsif (p_oipl_id > g_mx_binary_integer) then
637 l_idx := -1 * mod(p_oipl_id, g_mx_binary_integer);
638 Else
639 l_idx := p_oipl_id;
640 End if;
641 --
642 l_actn := 'Getting option name from cache or database...';
643 --
644 If g_opt_tbl.exists(l_idx) then
645 ret_str := g_opt_tbl(l_idx).name;
646 If (g_opt_tbl(l_idx).oipl_id <> p_oipl_id) then
647 l_fetch := TRUE;
648 End if;
649 Else
650 l_fetch := TRUE;
651 End if;
652 If (l_fetch) then
653 open c1;
654 fetch c1 into g_opt_tbl(l_idx);
655 If c1%notfound then
656 ret_str := 'OPTION NOT FOUND';
657 Else
658 ret_str := g_opt_tbl(l_idx).name;
659 End if;
660 close c1;
661 End if;
662 hr_utility.set_location ('Leaving '||l_proc,70);
663 return ret_str;
664 Exception
665 when others then
666 rpt_error(p_proc => l_proc
667 ,p_last_actn => l_actn
668 ,p_rpt_flag => p_batch_flag
669 );
670 fnd_message.raise_error;
671 End get_opt_name;
672 --
673 -- ============================================================================
674 -- <<Write_logfile>>
675 -- ============================================================================
676 --
677 Procedure write_logfile (p_num_pers_processed in number
678 ,p_num_pers_errored in number
679 ,p_non_person_cd in varchar2 default null
680 ) is
681 l_proc varchar2(80) := g_package||'.write_logfile';
682 begin
683 hr_utility.set_location ('Entering '||l_proc,10);
684 write(p_text => benutils.g_banner_minus);
685 write(p_text => 'Benefits Statistical Information');
686 write(p_text => benutils.g_banner_minus);
687
688 if p_non_person_cd is null then
689 write(p_text => 'Success persons ' || to_char(p_num_pers_processed));
690 write(p_text => 'Errored persons '|| to_char(p_num_pers_errored));
691 write(p_text => 'Total persons Procd '||
692 to_char(p_num_pers_processed+p_num_pers_errored));
693 else -- if p_non_person_cd = 'PREM'
694 write(p_text => 'Success premiums ' || to_char(p_num_pers_processed));
695 write(p_text => 'Errored premiums '|| to_char(p_num_pers_errored));
696 write(p_text => 'Total premiums Procd '||
697 to_char(p_num_pers_processed+p_num_pers_errored));
698 end if;
699 write(p_text => benutils.g_banner_minus);
700
701 hr_utility.set_location ('Leaving '||l_proc,99);
702 Exception
703 when others then
704 fnd_message.set_name('BEN','BEN_91663_BENMNGLE_LOGGING');
705 fnd_message.set_token('PROC',l_proc);
706 write(fnd_message.get);
707 fnd_message.raise_error;
708 end write_logfile;
709 --
710 -- ===========================================================================
711 -- << Procedure: Write_rec >>
712 -- ===========================================================================
713 --
714 Procedure Write_rec(p_typ_cd in varchar2
715 ,p_text in varchar2 default NULL
716 ,p_err_cd in varchar2 default NULL
717 ) is
718 l_proc varchar2(80) := g_package||'.write_rec';
719 l_rec ben_type.g_report_rec := g_rec;
720 Begin
721 hr_utility.set_location ('Entering ' || l_proc,05);
722 l_rec.rep_typ_cd := p_typ_cd;
723 l_rec.text := p_text;
724 l_rec.ERROR_MESSAGE_CODE := p_err_cd;
725 benutils.write(p_rec => l_rec);
726 hr_utility.set_location ('Leaving ' || l_proc,10);
727 End write_rec;
728 --
729 -- ============================================================================
730 -- <<End_process>>
731 -- ============================================================================
732 --
733 Procedure End_process(p_benefit_action_id in number
734 ,p_person_selected in number
735 ,p_business_group_id in number default NULL
736 ,p_non_person_cd in varchar2 default null
737 ) is
738 cursor c_actions is
739 Select count(*) amount,action_status_cd
740 from ben_person_actions act
741 where act.benefit_action_id = p_benefit_action_id
742 and act.action_status_cd in ('P','E','U')
743 and nvl(act.non_person_cd,'x') = nvl(p_non_person_cd,'x')
744 group by action_status_cd
745 ;
746 l_actions c_actions%rowtype;
747 l_proc varchar2(80) := g_package||'.End_process';
748 l_batch_proc_id number;
749 l_object_version_number number;
750 Begin
751 hr_utility.set_location ('Entering ' || l_proc,05);
752 --
753 -- Get totals for unprocessed, processed successfully and errored
754 --
755 open c_actions;
756 Loop
757 hr_utility.set_location (l_proc,6);
758 fetch c_actions into l_actions;
759 exit when c_actions%notfound;
760 If l_actions.action_status_cd = 'P' then
761 g_proc_info.num_persons_processed_succ := l_actions.amount;
762 Elsif l_actions.action_status_cd = 'E' then
763 g_proc_info.num_persons_errored := l_actions.amount;
764 Elsif l_actions.action_status_cd in ('U', 'T') then
765 g_proc_info.num_persons_unprocessed := l_actions.amount;
766 End if;
767 hr_utility.set_location (l_proc,7);
768 End loop;
769 hr_utility.set_location (l_proc,8);
770 close c_actions;
771 g_proc_info.num_persons_selected := p_person_selected;
772 hr_utility.set_location (l_proc,9);
773 --
774 -- Set value of number of persons processed
775 --
776 g_proc_info.num_persons_processed
777 := g_proc_info.num_persons_errored +
778 g_proc_info.num_persons_processed_succ;
779 hr_utility.set_location (l_proc,10);
780 hr_utility.set_location (l_proc||' start_date='||g_proc_info.start_date||'.',10);
781 ben_batch_proc_info_api.create_batch_proc_info
782 (P_VALIDATE => FALSE
783 ,P_BATCH_PROC_ID => l_batch_proc_id
784 ,P_BENEFIT_ACTION_ID => p_benefit_action_id
785 ,P_STRT_DT => trunc(g_proc_info.start_date)
786 ,P_END_DT => trunc(sysdate)
787 ,P_STRT_TM => to_char(g_proc_info.start_date,'HH24:MI:SS')
788 ,P_END_TM => to_char(sysdate,'HH24:MI:SS')
789 ,P_ELPSD_TM => to_char((dbms_utility.get_time -
790 g_proc_info.start_time_numeric)/100)||' seconds'
791 ,P_PER_SLCTD => g_proc_info.num_persons_selected
792 ,P_PER_PROC => g_proc_info.num_persons_processed
793 ,P_PER_UNPROC => g_proc_info.num_persons_unprocessed
794 ,P_PER_PROC_SUCC => g_proc_info.num_persons_processed_succ
795 ,P_PER_ERR => g_proc_info.num_persons_errored
796 ,P_BUSINESS_GROUP_ID => p_business_group_id
797 ,P_OBJECT_VERSION_NUMBER=> l_object_version_number
798 );
799 hr_utility.set_location (l_proc,11);
800 benutils.write_table_and_file(p_table => true,
801 p_file => true);
802 hr_utility.set_location (l_proc,12);
803 commit;
804 hr_utility.set_location ('Leaving ' || l_proc,100);
805 End end_process;
806 --
807 -- ============================================================================
808 -- <<Write>>
809 -- ============================================================================
810 --
811 Procedure write (p_text varchar2) is
812 l_proc varchar2(80) := g_package||'.Write';
813 Begin
814 --hr_utility.set_location ('Entering '||l_proc,05);
815 If fnd_global.conc_request_id <> -1 then
816 fnd_file.put_line(which=>fnd_file.log
817 ,buff => p_text);
818 End if;
819 --hr_utility.set_location ('Leaving '||l_proc,99);
820 End write;
821 --
822 -- ============================================================================
823 -- <<cache_person_information>>
824 -- ============================================================================
825 --
826 procedure cache_person_information
827 (p_person_id in number
828 ,p_business_group_id in number
829 ,p_effective_date in date
830 ,p_cache_time_perd_flag in boolean default TRUE
831 ,p_cache_pay_perd_flag in boolean default TRUE
832 ,p_cache_total_fte_flag in boolean default TRUE
833 ) is
834 --
835 cursor c_person is
836 select ppf.full_name
837 ,ppf.date_of_birth
838 ,ppf.date_of_death
839 ,ppf.benefit_group_id
840 ,bng.name
841 ,pps.date_start
842 ,pps.adjusted_svc_date
843 ,pad.postal_code
844 ,ppf.national_identifier
845 ,hao.name
846 From per_all_people_f ppf
847 ,per_periods_of_service pps
848 ,per_addresses pad
849 ,hr_all_organization_units_vl hao
850 ,ben_benfts_grp bng
851 Where ppf.person_id = p_person_id
852 And ppf.business_group_id = p_business_group_id
853 And ppf.business_group_id = hao.organization_id
854 And pps.person_id (+) = ppf.person_id
855 And nvl(pps.business_group_id(+),ppf.business_group_id)
856 = ppf.business_group_id
857 And pad.person_id (+) = ppf.person_id
858 And nvl(pad.business_group_id(+),ppf.business_group_id)
859 = ppf.business_group_id
860 And nvl(pad.primary_flag,'Y') = 'Y'
861 And p_effective_date between
862 nvl(pad.date_from(+),p_effective_date)
863 and nvl(pad.date_to(+),p_effective_date)
864 And bng.benfts_grp_id (+) = ppf.benefit_group_id
865 And nvl(bng.business_group_id(+),ppf.business_group_id)
866 = ppf.business_group_id
867 And p_effective_date between
868 ppf.effective_start_date and ppf.effective_end_date
869 ;
870 --
871 cursor c_assignment is
872 select paf.assignment_id
873 ,pbv.value
874 ,pat.per_system_status
875 ,paf.grade_id
876 ,paf.job_id
877 ,paf.pay_basis_id
878 ,paf.payroll_id
879 ,paf.location_id
880 ,paf.organization_id
881 ,paf.normal_hours
882 ,paf.frequency
883 ,paf.bargaining_unit_code
884 ,paf.labour_union_member_flag
885 ,paf.hourly_salaried_code
886 ,paf.assignment_status_type_id
887 ,paf.change_reason
888 ,paf.employment_category
889 ,ori.org_information1
890 ,oru.organization_id
891 ,oru.name
892 ,loc.location_code
893 ,ppf.payroll_name
894 From per_all_assignments_f paf
895 ,per_assignment_status_types pat
896 ,hr_organization_information ori
897 ,hr_all_organization_units_vl oru
898 ,hr_locations loc
899 ,pay_payrolls_f ppf
900 ,per_assignment_budget_values_f pbv
901 Where paf.person_id = p_person_id
902 and paf.assignment_type <> 'C'
903 and paf.primary_flag = 'Y'
904 and paf.business_group_id = p_business_group_id
905 and paf.location_id = loc.location_id (+)
906 and oru.organization_id (+) = paf.organization_id
907 and ori.organization_id (+) = oru.organization_id
908 and ori.org_information1(+) = 'HR_LEGAL'
909 and ppf.payroll_id (+) = paf.payroll_id
910 and p_effective_date between
911 nvl(ppf.effective_start_date,p_effective_date)
912 and nvl(ppf.effective_end_date,p_effective_date)
913 and pat.assignment_status_type_id (+) = paf.assignment_status_type_id
914 and pbv.assignment_id(+) = paf.assignment_id
915 and pbv.unit(+) = 'FTE'
916 and p_effective_date between
917 nvl(pbv.effective_start_date,p_effective_date)
918 and nvl(pbv.effective_end_date,p_effective_date)
919 and p_effective_date between
920 paf.effective_start_date and paf.effective_end_date
921 ;
922 --
923 cursor c_time_periods is
924 select tpe.start_date
925 ,tpe.end_date
926 From per_time_periods tpe
927 ,per_all_assignments_f paf
928 Where paf.person_id = p_person_id
929 and paf.primary_flag = 'Y'
930 and paf.assignment_type <> 'C'
931 and paf.business_group_id = p_business_group_id
932 and p_effective_date between
933 paf.effective_start_date and paf.effective_end_date
934 and tpe.payroll_id (+) = paf.payroll_id
935 and p_effective_date between
936 nvl(tpe.start_date,p_effective_date)
937 and nvl(tpe.end_date,p_effective_date)
938 ;
939 --
940 cursor c_person_type is
941 select per.person_type_id
942 ,ppt.user_person_type
943 ,ppt.system_person_type
944 From per_person_type_usages_f per
945 ,per_person_types ppt
946 Where per.person_id = p_person_id
947 and p_effective_date between
948 per.effective_start_date and per.effective_end_date
949 and per.person_type_id = ppt.person_type_id
950 order by decode(ppt.system_person_type,'EMP',1,2)
951 ;
952 --
953 cursor c_next_pay_period is
954 select tpe.start_date
955 ,tpe.end_date
956 from per_time_periods tpe
957 ,per_all_assignments_f asg
958 where tpe.payroll_id = asg.payroll_id
959 and asg.person_id = p_person_id
960 and asg.assignment_type <> 'C'
961 and asg.primary_flag = 'Y'
962 and p_effective_date between
963 asg.effective_start_date and asg.effective_end_date
964 and tpe.start_date > p_effective_date
965 order by tpe.start_date
966 ;
967 --
968 cursor c_total_fte is
969 select sum(pab.value)
970 from per_all_people_f ppf
971 ,per_all_assignments_f paf
972 ,per_assignment_budget_values_f pab
973 where ppf.person_id = p_person_id
974 and paf.assignment_type <> 'C'
975 and ppf.business_group_id = p_business_group_id
976 and p_effective_date between
977 ppf.effective_start_date and ppf.effective_end_date
978 and ppf.person_id = paf.person_id
979 and paf.business_group_id = ppf.business_group_id
980 and p_effective_date between
981 paf.effective_start_date and paf.effective_end_date
982 and pab.business_group_id = paf.business_group_id
983 and pab.assignment_id = paf.assignment_id
984 and pab.unit = 'FTE'
985 and p_effective_date between
986 pab.effective_start_date and pab.effective_end_date
987 ;
988 l_proc varchar2(80) := g_package||'.cache_person_information';
989 l_count number(9) := 0;
990 l_person_type c_person_type%rowtype;
991 begin
992 hr_utility.set_location ('Entering '||l_proc,05);
993 open c_person;
994 fetch c_person into g_cache_person.full_name
995 ,g_cache_person.date_of_birth
996 ,g_cache_person.date_of_death
997 ,g_cache_person.benefit_group_id
998 ,g_cache_person.benefit_group
999 ,g_cache_person.date_start
1000 ,g_cache_person.adjusted_svc_date
1001 ,g_cache_person.postal_code
1002 ,g_cache_person.national_identifier
1003 ,g_cache_person.bg_name
1004 ;
1005 If c_person%notfound then
1006 close c_person;
1007 fnd_message.set_name('BEN','BEN_91661_BENMNGLE_PERSON_FIND');
1008 fnd_message.set_token('PROC',l_proc);
1009 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1010 fnd_message.raise_error;
1011 End if;
1012 close c_person;
1013 --
1014 -- Open cursor to see if the person holds emp person type status and store all
1015 -- of the person type belong to the person.
1016 --
1017 g_cache_person.person_has_type_emp := 'N';
1018 g_cache_person_types.delete;
1019 open c_person_type;
1020 Loop
1021 fetch c_person_type into l_person_type;
1022 exit when c_person_type%notfound;
1023 l_count := l_count + 1;
1024 If l_person_type.system_person_type = 'EMP' then
1025 g_cache_person.person_has_type_emp := 'Y';
1026 End if;
1027 g_cache_person_types(l_count).user_person_type
1028 := l_person_type.user_person_type;
1029 g_cache_person_types(l_count).system_person_type
1030 := l_person_type.system_person_type;
1031 g_cache_person_types(l_count).person_type_id
1032 := l_person_type.person_type_id;
1033 End loop;
1034 close c_person_type;
1035 --
1036 -- Default First element in array to NULL
1037 --
1038 If not g_cache_person_types.exists(1) then
1039 g_cache_person_types(1).user_person_type := null;
1040 g_cache_person_types(1).system_person_type := null;
1041 g_cache_person_types(1).person_type_id := null;
1042 End if;
1043 --
1044 -- We need to do the assignment stuff seperately as we can't outer join
1045 -- as we need assignments with primary flags and applicants have non
1046 -- primary flag assignments so the hack is to do the select in two
1047 -- statements, although a fix could be to do a union to get the value
1048 -- for the assignment id.
1049 --
1050 open c_assignment;
1051 fetch c_assignment into g_cache_person.assignment_id
1052 ,g_cache_person.fte_value
1053 ,g_cache_person.per_system_status
1054 ,g_cache_person.grade_id
1055 ,g_cache_person.job_id
1056 ,g_cache_person.pay_basis_id
1057 ,g_cache_person.payroll_id
1058 ,g_cache_person.location_id
1059 ,g_cache_person.organization_id
1060 ,g_cache_person.normal_hours
1061 ,g_cache_person.frequency
1062 ,g_cache_person.bargaining_unit_code
1063 ,g_cache_person.labour_union_member_flag
1064 ,g_cache_person.hourly_salaried_code
1065 ,g_cache_person.assignment_status_type_id
1066 ,g_cache_person.change_reason
1067 ,g_cache_person.employment_category
1068 ,g_cache_person.org_information1
1069 ,g_cache_person.org_id
1070 ,g_cache_person.org_name
1071 ,g_cache_person.address_line_1
1072 ,g_cache_person.payroll_name
1073 ;
1074 If c_assignment%notfound then
1075 g_cache_person.assignment_id := null;
1076 g_cache_person.fte_value := null;
1077 g_cache_person.per_system_status := null;
1078 g_cache_person.grade_id := null;
1079 g_cache_person.job_id := null;
1080 g_cache_person.pay_basis_id := null;
1081 g_cache_person.payroll_id := null;
1082 g_cache_person.location_id := null;
1083 g_cache_person.organization_id := null;
1084 g_cache_person.normal_hours := null;
1085 g_cache_person.frequency := null;
1086 g_cache_person.bargaining_unit_code := null;
1087 g_cache_person.labour_union_member_flag := null;
1088 g_cache_person.assignment_status_type_id := null;
1089 g_cache_person.change_reason := null;
1090 g_cache_person.employment_category := null;
1091 g_cache_person.org_information1 := null;
1092 g_cache_person.org_id := null;
1093 g_cache_person.org_name := null;
1094 g_cache_person.address_line_1 := null;
1095 g_cache_person.payroll_name := null;
1096 End if;
1097 close c_assignment;
1098 --
1099 -- Time period is optional. Defaulted to TRUE
1100 --
1101 If ( p_cache_time_perd_flag) then
1102 open c_time_periods;
1103 fetch c_time_periods into g_cache_person.pay_period_start_date
1104 ,g_cache_person.pay_period_end_date
1105 ;
1106 If c_time_periods%notfound then
1107 g_cache_person.pay_period_start_date := null;
1108 g_cache_person.pay_period_end_date := null;
1109 End if;
1110 close c_time_periods;
1111 End if;
1112 --
1113 -- pay period cache is optional, defaulted to TRUE
1114 --
1115 If (p_cache_pay_perd_flag) then
1116 open c_next_pay_period;
1117 fetch c_next_pay_period into g_cache_person.pay_period_next_start_date
1118 ,g_cache_person.pay_period_next_end_date
1119 ;
1120 If c_next_pay_period%notfound then
1121 g_cache_person.pay_period_next_start_date := null;
1122 g_cache_person.pay_period_next_end_date := null;
1123 End if;
1124 close c_next_pay_period;
1125 End if;
1126 --
1127 -- cache budget value is optionsl, defaulted to TRUE
1128 --
1129 If (p_cache_total_fte_flag) then
1130 open c_total_fte;
1131 fetch c_total_fte into g_cache_person.total_fte_value;
1132 If c_total_fte%notfound then
1133 g_cache_person.total_fte_value := null;
1134 End if;
1135 close c_total_fte;
1136 End if;
1137 --
1138 -- Set the lf_evt_ocrd_dt to the effective date. If we are running in life
1139 -- event mode then this will be set later to the real life event date.
1140 --
1141 g_cache_person.lf_evt_ocrd_dt := p_effective_date;
1142 --
1143 -- Put person_id into g_rec cache
1144 --
1145 g_rec.person_id := p_person_id;
1146 g_rec.national_identifier := g_cache_person.national_identifier;
1147 hr_utility.set_location ('Leaving '||l_proc,10);
1148 end cache_person_information;
1149 --
1150 -- ============================================================================
1151 -- <<Person_header>>
1152 -- ============================================================================
1153 --
1154 Procedure person_header
1155 (p_person_id in number default null
1156 ,p_business_group_id in number
1157 ,p_effective_date in date
1158 ) is
1159 --
1160 l_proc varchar2(80) := g_package||'.person_header';
1161 l_output_string varchar2(2000); -- UTF8 Change Bug 2254683 varchar2(100)
1162 --
1163 Begin
1164 hr_utility.set_location ('Entering '||l_proc,10);
1165 --
1166 -- Cache person data
1167 --
1168 cache_person_information
1169 (p_person_id => p_person_id
1170 ,p_business_group_id => p_business_group_id
1171 ,p_effective_date => p_effective_date
1172 );
1173 --
1174 -- This should display something like this
1175 --
1176 -- *********************************************************************
1177 -- Name : John Smith (100) Type : Employee (1) Grp : Benefits Group (1)
1178 -- BG : Freds BG (100) Org : Freds Org(1) GRE : Retiree
1179 -- Loc : HQ (100) Pst : 86727 Pyr : Payroll 3B (1)
1180 --
1181 write(p_text => benutils.g_banner_asterix);
1182 l_output_string := 'Name: '||
1183 rpad(substr(g_cache_person.full_name,1,15),15,' ')||
1184 rpad(benutils.id(p_person_id),8,' ')||
1185 'Typ: '||
1186 rpad(substr(g_cache_person_types(1).user_person_type,1,15)
1187 ,15,' ') ||
1188 rpad('',8,' ')|| ' Grp: '||
1189 rpad(substr(g_cache_person.benefit_group,1,15),15,' ')||
1190 rpad(benutils.id(g_cache_person.benefit_group_id),8,' ');
1191 write(p_text => l_output_string);
1192 --
1193 -- loop through the rest of the person_types
1194 --
1195 For l_count in 2..g_cache_person_types.last loop
1196 l_output_string := rpad(' ',25,' ');
1197 l_output_string := l_output_string ||
1198 rpad(substr(g_cache_person_types(l_count).user_person_type,1,20),20,' ');
1199 write(l_output_string);
1200 End loop;
1201 l_output_string := 'BG: '||
1202 rpad(substr(g_cache_person.bg_name,1,15),15,' ')||
1203 rpad(benutils.id(p_business_group_id),8,' ')||
1204 ' Org: '||
1205 rpad(substr(g_cache_person.org_name,1,15),15,' ')||
1206 rpad(benutils.id(g_cache_person.org_id),8,' ');
1207 --
1208 -- Need to add GRE
1209 --
1210 write(p_text => l_output_string);
1211 l_output_string := 'Loc: '||
1212 rpad(substr(g_cache_person.address_line_1,1,15),15,' ')||
1213 rpad(benutils.id(g_cache_person.location_id),8,' ')||
1214 ' Pst: '||
1215 rpad(substr(g_cache_person.postal_code,1,15),15,' ')||
1216 rpad('',8,' ')||
1217 ' Pyr: '||
1218 rpad(substr(g_cache_person.payroll_name,1,15),15,' ')||
1219 rpad(benutils.id(g_cache_person.payroll_id),8,' ');
1220 write(p_text => l_output_string);
1221 hr_utility.set_location ('Leaving '||l_proc,10);
1222 end person_header;
1223 --
1224 -- ============================================================================
1225 -- <<Print_parameters>>
1226 -- ============================================================================
1227 --
1228 procedure print_parameters
1229 (p_thread_id in number
1230 ,p_validate in varchar2
1231 ,p_benefit_action_id in number
1232 ,p_effective_date in date
1233 ,p_business_group_id in number
1234 ,p_pgm_id in number default hr_api.g_number
1235 ,p_pl_id in number default hr_api.g_number
1236 ,p_popl_enrt_typ_cycl_id in number default hr_api.g_number
1237 ,p_person_id in number default hr_api.g_number
1238 ,p_person_type_id in number default hr_api.g_number
1239 ,p_ler_id in number default hr_api.g_number
1240 ,p_organization_id in number default hr_api.g_number
1241 ,p_benfts_grp_id in number default hr_api.g_number
1242 ,p_location_id in number default hr_api.g_number
1243 ,p_legal_entity_id in number default hr_api.g_number
1244 ,p_payroll_id in number default hr_api.g_number
1245 ,p_no_programs in varchar2 default hr_api.g_varchar2
1246 ,p_no_plans in varchar2 default hr_api.g_varchar2
1247 ,p_rptg_grp_id in number default hr_api.g_number
1248 ,p_pl_typ_id in number default hr_api.g_number
1249 ,p_opt_id in number default hr_api.g_number
1250 ,p_eligy_prfl_id in number default hr_api.g_number
1251 ,p_vrbl_rt_prfl_id in number default hr_api.g_number
1252 ,p_mode in varchar2 default hr_api.g_varchar2
1253 ,p_person_selection_rule_id in number default hr_api.g_number
1254 ,p_comp_selection_rule_id in number default hr_api.g_number
1255 ,p_enrt_perd_id in number default hr_api.g_number
1256 ,p_derivable_factors in varchar2 default hr_api.g_varchar2
1257 ,p_audit_log in varchar2 default hr_api.g_varchar2
1258 ) is
1259 l_proc varchar2(80) := g_package||'.print_parameters';
1260 l_string varchar2(80);
1261 l_actn varchar2(80);
1262 begin
1263 hr_utility.set_location ('Entering '||l_proc,10);
1264 write(p_text => 'Runtime Parameters');
1265 write(p_text => '------------------');
1266 -- bug 5450842
1267 if p_mode is not null AND p_mode <> hr_api.g_varchar2
1268 then
1269 write(p_text => 'Run Mode :' ||
1270 nvl(hr_general.decode_lookup('BEN_BENMNGLE_MD',p_mode),
1271 hr_general.decode_lookup('BEN_BENCLENR_MD',p_mode))); -- 1674123
1272 end if;
1273 -- end 5450842
1274 write(p_text => 'Validation Mode :' ||
1275 hr_general.decode_lookup('YES_NO',p_validate));
1276 write(p_text => 'Benefit Action ID :' ||
1277 to_char(p_benefit_action_id));
1278 write(p_text => 'Effective Date :' ||
1279 to_char(p_effective_date,'DD/MM/YYYY'));
1280 write(p_text =>'Business Group ID :' || p_business_group_id);
1281 if (nvl(p_enrt_perd_id, -1) <> hr_api.g_number) then
1282 write(p_text =>'Enrollment period Id :' || p_enrt_perd_id);
1283 end if;
1284 --
1285 l_actn := 'printing p_derivable_factors';
1286 If (nvl(p_derivable_factors,'xxxx') <> hr_api.g_varchar2) then
1287 write(p_text =>'Derivable Factors :'||
1288 hr_general.decode_lookup('YES_NO',p_derivable_factors));
1289 End if;
1290 --
1291 l_actn := 'Printing p_pgm_id';
1292 If (nvl(p_pgm_id,-1) <> hr_api.g_number) then
1293 write(p_text => 'Program ID :'||
1294 benutils.iftrue
1295 (p_expression => p_pgm_id is null
1296 ,p_true => 'All'
1297 ,p_false => p_pgm_id));
1298 End if;
1299 --
1300 l_actn := 'printing p_pl_id...';
1301 If (nvl(p_pl_id,-1) <> hr_api.g_number) then
1302 write(p_text => 'Plan ID :'||
1303 benutils.iftrue
1304 (p_expression => p_pl_id is null
1305 ,p_true => 'All'
1306 ,p_false => p_pl_id));
1307 End if;
1308 --
1309 l_actn := 'Printing p_pl_typ_id...';
1310 If (nvl(p_pl_typ_id,-1) <> hr_api.g_number) then
1311 write(p_text => 'Plan Type ID :'||
1312 benutils.iftrue
1313 (p_expression => p_pl_typ_id is null
1314 ,p_true => 'All'
1315 ,p_false => p_pl_typ_id));
1316 End if;
1317 --
1318 l_actn := 'Printting p_opt_id... ';
1319 If (nvl(p_opt_id,-1) <> hr_api.g_number) then
1320 write(p_text => 'Option ID :'||
1321 benutils.iftrue
1322 (p_expression => p_opt_id is null
1323 ,p_true => 'All'
1324 ,p_false => p_opt_id));
1325 End if;
1326 --
1327 l_actn := 'Printting p_popl_enrt_typ_cycl...';
1328 If (nvl(p_popl_enrt_typ_cycl_id,-1) <> hr_api.g_number) then
1329 write(p_text => 'Enrollment Type Cycle :'||
1330 benutils.iftrue
1331 (p_expression => p_popl_enrt_typ_cycl_id is null
1332 ,p_true => 'All'
1333 ,p_false => p_popl_enrt_typ_cycl_id));
1334 End if;
1335 --
1336 l_actn := 'Printting p_no_program...';
1337 If (nvl(p_no_programs,'xxxx') <> hr_api.g_varchar2) then
1338 write(p_text => 'Just Plans not in Programs :'||
1339 hr_general.decode_lookup('YES_NO',p_no_programs));
1340 End if;
1341 --
1342 l_actn := 'Printting p_no_plans...';
1343 If (nvl(p_no_plans,'xxxx') <> hr_api.g_varchar2) then
1344 write(p_text => 'Just Programs :'||
1345 hr_general.decode_lookup('YES_NO',p_no_plans));
1346 End if;
1347 --
1348 l_actn := 'Printting p_rptg_grp_id...';
1349 If (nvl(p_rptg_grp_id,-1) <> hr_api.g_number) then
1350 write(p_text => 'Reporting Group :'||
1351 benutils.iftrue
1352 (p_expression => p_rptg_grp_id is null
1353 ,p_true => 'All'
1354 ,p_false => p_rptg_grp_id));
1355 End if;
1356 --
1357 l_actn := 'Printting p_eligy_prfl_id...';
1358 If (nvl(p_eligy_prfl_id,-1) <> hr_api.g_number) then
1359 write(p_text => 'Eligiblity Profile :'||
1360 benutils.iftrue
1361 (p_expression => p_eligy_prfl_id is null
1362 ,p_true => 'All'
1363 ,p_false => p_eligy_prfl_id));
1364 End if;
1365 --
1366 l_actn := 'Printting p_vrbl_rt_prfl_id...';
1367 If (nvl(p_vrbl_rt_prfl_id,-1) <> hr_api.g_number) then
1368 write(p_text => 'Variable Rate Profile :'||
1369 benutils.iftrue
1370 (p_expression => p_vrbl_rt_prfl_id is null
1371 ,p_true => 'All'
1372 ,p_false => p_vrbl_rt_prfl_id));
1373 End if;
1374 --
1375 l_actn := 'Printting p_person_selection_rule_id...';
1376 If (nvl(p_person_selection_rule_id,-1) <> hr_api.g_number) then
1377 write(p_text => 'Person Selection Rule :'||
1378 benutils.iftrue
1379 (p_expression => p_person_selection_rule_id is null
1380 ,p_true => 'None'
1381 ,p_false => p_person_selection_rule_id));
1382 End if;
1383 --
1384 l_actn := 'Printting p_person_id...';
1385 If (nvl(p_person_id,-1) <> hr_api.g_number) then
1386 write(p_text => 'Person ID :'||
1387 benutils.iftrue
1388 (p_expression => p_person_id is null
1389 ,p_true => 'None'
1390 ,p_false => p_person_id));
1391 End if;
1392 --
1393 l_actn := 'Printting p_person_type_id...';
1394 If (nvl(p_person_type_id,-1) <> hr_api.g_number) then
1395 write(p_text => 'Person Type ID :'||
1396 benutils.iftrue
1397 (p_expression => p_person_type_id is null
1398 ,p_true => 'None'
1399 ,p_false => p_person_type_id));
1400 End if;
1401 --
1402 l_actn := 'Printting p_ler_id...';
1403 If (nvl(p_ler_id,-1) <> hr_api.g_number) then
1404 write(p_text => 'Ler ID :'||
1405 benutils.iftrue
1406 (p_expression => p_ler_id is null
1407 ,p_true => 'None'
1408 ,p_false => p_ler_id));
1409 End if;
1410 --
1411 l_actn := 'Printting p_organization_id...';
1412 If (nvl(p_organization_id,-1) <> hr_api.g_number) then
1413 write(p_text => 'Organization ID :'||
1414 benutils.iftrue
1415 (p_expression => p_organization_id is null
1416 ,p_true => 'None'
1417 ,p_false => p_organization_id));
1418 End if;
1419 --
1420 l_actn := 'Printting p_benfts_grp_id...';
1421 If (nvl(p_benfts_grp_id,-1) <> hr_api.g_number) then
1422 write(p_text => 'Benefits Group ID :'||
1423 benutils.iftrue
1424 (p_expression => p_benfts_grp_id is null
1425 ,p_true => 'None'
1426 ,p_false => p_benfts_grp_id));
1427 End if;
1428 --
1429 l_actn := 'Printting p_location_id...';
1430 If (nvl(p_location_id,-1) <> hr_api.g_number) then
1431 write(p_text => 'Location ID :'||
1432 benutils.iftrue
1433 (p_expression => p_location_id is null
1434 ,p_true => 'None'
1435 ,p_false => p_location_id));
1436 End if;
1437 --
1438 l_actn := 'Printting p_legal_entity_id...';
1439 If (nvl(p_legal_entity_id,-1) <> hr_api.g_number) then
1440 write(p_text => 'Legal Entity ID :'||
1441 benutils.iftrue
1442 (p_expression => p_legal_entity_id is null
1443 ,p_true => 'None'
1444 ,p_false => p_legal_entity_id));
1445 End if;
1446 --
1447 l_actn := 'Printting p_payroll_id...';
1448 If (nvl(p_payroll_id,-1) <> hr_api.g_number) then
1449 write(p_text => 'Payroll ID :'||
1450 benutils.iftrue
1451 (p_expression => p_payroll_id is null
1452 ,p_true => 'None'
1453 ,p_false => p_payroll_id));
1454 End if;
1455 --
1456 l_actn := 'Printting p_comp_selection_rule_id...';
1457 If (nvl(p_comp_selection_rule_id,-1) <> hr_api.g_number) then
1458 write(p_text => 'Comp Object Selection Rule :'||
1459 benutils.iftrue
1460 (p_expression => p_comp_selection_rule_id is null
1461 ,p_true => 'None'
1462 ,p_false => p_comp_selection_rule_id));
1463 End if;
1464 --
1465 l_actn := 'Printting p_audit_log...';
1466 If (nvl(p_audit_log,'xxxx') <> hr_api.g_varchar2) then
1467 write(p_text => 'Audit log flag :'||
1468 hr_general.decode_lookup('YES_NO',p_audit_log));
1469 End if;
1470 hr_utility.set_location ('Leaving '||l_proc,10);
1471 exception
1472 when others then
1473 ben_batch_utils.rpt_error(p_proc => l_proc
1474 ,p_last_actn => l_actn );
1475 raise;
1476 end print_parameters;
1477 --
1478 -- ============================================================================
1479 -- << Person_selection_Rule >>
1480 -- ============================================================================
1481 --
1482 Function person_selection_rule
1483 (p_person_id in Number
1484 ,p_business_group_id in Number
1485 ,p_person_selection_rule_id in Number
1486 ,p_effective_date in Date
1487 ,p_batch_flag in Boolean default FALSE
1488 ,p_input1 in varchar2 default null -- Bug 5331889
1489 ,p_input1_value in varchar2 default null
1490 ) return char is
1491 Cursor c1 is
1492 Select assignment_id
1493 From per_assignments_f paf
1494 Where paf.person_id = p_person_id
1495 and paf.assignment_type <> 'C'
1496 And paf.primary_flag = 'Y'
1497 And paf.business_group_id = p_business_group_id
1498 And p_effective_date between
1499 paf.effective_start_date and paf.effective_end_date;
1500 --
1501 Cursor c2 is
1502 Select assignment_id
1503 From per_all_assignments_f paf
1504 Where paf.person_id = p_person_id
1505 and paf.assignment_type <> 'C'
1506 And paf.primary_flag = 'Y'
1507 And paf.business_group_id = p_business_group_id
1508 And p_effective_date between
1509 paf.effective_start_date and paf.effective_end_date;
1510 --
1511 l_proc varchar2(80) := g_package||'.person_selection_rule';
1512 l_outputs ff_exec.outputs_t;
1513 l_return varchar2(30);
1514 l_assignment_id number;
1515 l_actn varchar2(80);
1516 Begin
1517 hr_utility.set_location ('Entering '||l_proc,10);
1518 --
1519 -- Get assignment ID form per_assignments_f table.
1520 --
1521 l_actn := 'Opening Assignment cursor...';
1522 -- Perf changes
1523 if hr_security.view_all = 'Y' and hr_general.get_xbg_profile = 'Y'
1524 then
1525 open c2;
1526 fetch c2 into l_assignment_id;
1527 If c2%notfound and g_audit_flag = true
1528 then
1529 --Log the person in log file and proceed with processing of other person records
1530 write(p_text => 'Warning : No Primary assignment found for this Person ID : '|| p_person_id); --5643310
1531 End if;
1532 close c2;
1533 else
1534 open c1;
1535 fetch c1 into l_assignment_id;
1536 If c1%notfound and g_audit_flag = true
1537 then
1538 --Log the person in log file and proceed with processing of other person records
1539 write(p_text => 'Warning : No Primary assignment found for this Person ID : '|| p_person_id); --5643310
1540 End if;
1541 close c1;
1542 end if;
1543 --
1544 -- Call formula initialise routine
1545 --
1546 l_actn := 'Calling benutils.formula procedure...';
1547 l_outputs := benutils.formula
1548 (p_formula_id => p_person_selection_rule_id
1549 ,p_effective_date => p_effective_date
1550 ,p_business_group_id => p_business_group_id
1551 ,p_assignment_id => l_assignment_id
1552 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
1553 ,p_param1_value => to_char(p_person_id)
1554 ,p_param2 => p_input1
1555 ,p_param2_value => p_input1_value);
1556 l_return := l_outputs(l_outputs.first).value;
1557 --
1558 l_actn := 'Evaluating benutils.formula return...';
1559 --
1560 If upper(l_return) not in ('Y', 'N') then
1561 --
1562 -- Defensive coding for Non Y return
1563 --
1564 rpt_error(p_proc => l_proc
1565 ,p_last_actn => l_actn
1566 ,p_rpt_flag => p_batch_flag);
1567 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
1568 fnd_message.set_token('RL',
1569 'person_selection_rule_id :'||p_person_selection_rule_id);
1570 fnd_message.set_token('PROC',l_proc);
1571 Raise ben_batch_utils.g_record_error;
1572 End if;
1573 return l_return;
1574 hr_utility.set_location ('Leaving '||l_proc,10);
1575 Exception
1576 When ben_batch_utils.g_record_error then
1577 raise;
1578 when others then
1579 rpt_error(p_proc => l_proc, p_last_actn => l_actn);
1580 raise;
1581 End person_selection_rule;
1582 --
1583 -- ============================================================================
1584 -- <<Check_all_slaves_finished>>
1585 -- ============================================================================
1586 --
1587 Procedure check_all_slaves_finished(p_rpt_flag Boolean default FALSE) is
1588 --
1589 l_proc varchar2(80) := g_package||'.check_all_slaves_finished';
1590 l_no_slaves boolean := true;
1591 l_dummy varchar2(1);
1592 l_actn varchar2(80);
1593 --
1594 Cursor c_slaves(p_request_id number) is
1595 Select null
1596 From fnd_concurrent_requests fnd
1597 Where fnd.phase_code <> 'C'
1598 And fnd.request_id = p_request_id;
1599 Begin
1600 hr_utility.set_location ('Entering '||l_proc,5);
1601 If g_num_processes <> 0 then
1602 l_actn := 'Checking Slaves.....';
1603 While l_no_slaves loop
1604 l_no_slaves := false;
1605 For l_count in 1..g_num_processes loop
1606 open c_slaves(g_processes_tbl(l_count));
1607 fetch c_slaves into l_dummy;
1608 If c_slaves%found then
1609 l_no_slaves := true;
1610 close c_slaves;
1611 exit;
1612 End if;
1613 Close c_slaves;
1614 End loop;
1615 If (l_no_slaves) then
1616 dbms_lock.sleep(5);
1617 End if;
1618 End loop;
1619 End if;
1620 hr_utility.set_location ('Leavinging '||l_proc,5);
1621 Exception
1622 when others then
1623 rpt_error(p_proc =>l_proc,p_last_actn=>l_actn,p_rpt_flag=>p_rpt_flag);
1624 raise;
1625 End check_all_slaves_finished;
1626 --
1627 -- ============================================================================
1628 -- <<Create_restart_person_actions>>
1629 -- ============================================================================
1630 --
1631 Procedure create_restart_person_actions
1632 (p_benefit_action_id in number
1633 ,p_effective_date in date
1634 ,p_chunk_size in number
1635 ,p_threads in number
1636 ,p_num_ranges out nocopy number
1637 ,p_num_persons out nocopy number
1638 ,p_commit_data in varchar2 default 'Y'
1639 ,p_non_person_cd in varchar2 default null
1640 ) is
1641 --
1642 cursor c_person_actions is
1643 Select act.person_action_id
1644 ,act.person_id
1645 ,act.ler_id
1646 From ben_person_actions act
1647 Where act.action_status_cd <> 'P'
1648 and nvl(act.non_person_cd,'x') = nvl(p_non_person_cd,'x')
1649 and act.benefit_action_id = p_benefit_action_id
1650 ;
1651 --
1652 l_proc varchar2(80) := g_package||'.create_restart_person_actions';
1653 l_person_details c_person_actions%rowtype;
1654 l_person_action_id number;
1655 l_object_version_number number;
1656 l_start_person_action_id number;
1657 l_end_person_action_id number;
1658 l_num_rows number := 0;
1659 l_rows number := 0;
1660 l_num_ranges number := 0;
1661 l_num_persons number := 0;
1662 l_range_id number;
1663 l_actn varchar2(80);
1664 Begin
1665 hr_utility.set_location ('Leaving '||l_proc,10);
1666 --
1667 -- Delete ranges from ben_batch_ranges table
1668 --
1669 l_actn := 'Calling ben_benmngle_purge.delete_batch_range_rows...';
1670 ben_benmngle_purge.delete_batch_range_rows
1671 (p_benefit_action_id => p_benefit_action_id,
1672 p_rows => l_rows);
1673 --
1674 open c_person_actions;
1675 Loop
1676 --
1677 l_actn := 'Fetching c_person_action cursor...';
1678 --
1679 fetch c_person_actions into l_person_details;
1680 exit when c_person_actions%notfound;
1681 l_num_rows := l_num_rows + 1;
1682 l_num_persons := l_num_persons + 1;
1683 l_end_person_action_id := l_person_details.person_action_id;
1684 If l_num_rows = 1 then
1685 l_start_person_action_id := l_person_details.person_action_id;
1686 End if;
1687 If l_num_rows = p_chunk_size then
1688 --
1689 -- Create a range of data to be multithreaded.
1690 --
1691 l_actn := 'Calling Ben_batch_ranges_api.create_batch_ranges(in)...';
1692 Ben_batch_ranges_api.create_batch_ranges
1693 (p_validate => false
1694 ,p_benefit_action_id => p_benefit_action_id
1695 ,p_range_id => l_range_id
1696 ,p_range_status_cd => 'U'
1697 ,p_starting_person_action_id => l_start_person_action_id
1698 ,p_ending_person_action_id => l_end_person_action_id
1699 ,p_object_version_number => l_object_version_number
1700 ,p_effective_date => p_effective_date
1701 );
1702 l_start_person_action_id := 0;
1703 l_end_person_action_id := 0;
1704 l_num_rows := 0;
1705 l_num_ranges := l_num_ranges + 1;
1706 End if;
1707 End loop;
1708 close c_person_actions;
1709 --
1710 -- Now create a range for any left over records that are less than
1711 -- the chunk size.
1712 --
1713 If l_num_rows <> 0 then
1714 --
1715 l_actn := 'Calling Ben_batch_ranges_api.create_batch_ranges(out)...';
1716 --
1717 ben_batch_ranges_api.create_batch_ranges
1718 (p_validate => false
1719 ,p_benefit_action_id => p_benefit_action_id
1720 ,p_range_id => l_range_id
1721 ,p_range_status_cd => 'U'
1722 ,p_starting_person_action_id => l_start_person_action_id
1723 ,p_ending_person_action_id => l_end_person_action_id
1724 ,p_object_version_number => l_object_version_number
1725 ,p_effective_date => p_effective_date
1726 );
1727 l_num_ranges := l_num_ranges + 1;
1728 End if;
1729 If p_commit_data = 'Y' then
1730 commit;
1731 End if;
1732 p_num_ranges := l_num_ranges;
1733 p_num_persons := l_num_persons;
1734 hr_utility.set_location ('Leaving '||l_proc,10);
1735 Exception
1736 when others then
1737 rpt_error(p_proc => l_proc, p_last_actn => l_actn);
1738 raise;
1739 End create_restart_person_actions;
1740 --
1741 -- ============================================================================
1742 -- <<Batch_report>>
1743 -- ============================================================================
1744 --
1745 Procedure batch_report
1746 (p_concurrent_request_id in number
1747 ,p_program_name in varchar2
1748 ,p_subtitle in varchar2 default NULL
1749 ,p_request_id out nocopy number
1750 ) is
1751 l_proc varchar2(80) := g_package||'.batch_reports';
1752 l_request_id number;
1753 Begin
1754 hr_utility.set_location('Entering :'||l_proc,10);
1755 If (p_subtitle is NULL) then
1756 l_request_id := fnd_request.submit_request
1757 (application => 'BEN'
1758 ,program => p_program_name
1759 ,description => NULL
1760 ,sub_request => FALSE
1761 ,argument1 => p_concurrent_request_id
1762 );
1763 Else
1764 l_request_id := fnd_request.submit_request
1765 (application => 'BEN'
1766 ,program => p_program_name
1767 ,description => NULL
1768 ,sub_request => FALSE
1769 ,argument1 => p_concurrent_request_id
1770 ,argument2 => p_subtitle
1771 );
1772 End if;
1773 If l_request_id = 0 then
1774 raise ben_batch_utils.g_record_error;
1775 Else
1776 p_request_id := l_request_id;
1777 End if;
1778 hr_utility.set_location('Leaving :'||l_proc,10);
1779 Exception
1780 when others then
1781 rpt_error(p_proc => l_proc, p_last_actn => 'Submitting ' || p_program_name);
1782 raise;
1783 End batch_report;
1784 --
1785 -- ============================================================================
1786 -- <<Write_error_rec>>
1787 -- ============================================================================
1788 --
1789 Procedure write_error_rec is
1790 l_msg_error_cd varchar2(80);
1791 l_actn varchar2(80);
1792 l_proc varchar2(80) := g_package||'.write_error_rec';
1793 l_msg varchar2(2000);
1794 begin
1795 --
1796 l_actn := 'getting error message..';
1797 --
1798 l_msg_error_cd := benutils.get_message_name;
1799 l_msg := fnd_message.get;
1800 --
1801 write(p_text => l_msg);
1802 --
1803 if l_msg_error_cd is null then
1804 l_msg_error_cd := 'ERROR NOT SPECIFIED';
1805 End if;
1806 --
1807 l_actn := 'calling ben_batch_utils.write_rec....';
1808 ben_batch_utils.write_rec(p_typ_cd => 'ERROR'
1809 ,p_err_cd => l_msg_error_cd
1810 ,p_text => l_msg
1811 );
1812 Exception
1813 when others then
1814 rpt_error(p_proc => l_proc, p_last_actn => l_actn);
1815 raise;
1816 End write_error_rec;
1817 --
1818 -- ============================================================================
1819 -- <<summary_by_action>>
1820 -- ============================================================================
1821 --
1822 procedure summary_by_action
1823 (p_concurrent_request_id in number
1824 ,p_cd_1 in varchar2, p_val_1 out nocopy number
1825 ,p_cd_2 in varchar2, p_val_2 out nocopy number
1826 ,p_cd_3 in varchar2, p_val_3 out nocopy number
1827 ,p_cd_4 in varchar2, p_val_4 out nocopy number
1828 ,p_cd_5 in varchar2, p_val_5 out nocopy number
1829 ,p_cd_6 in varchar2, p_val_6 out nocopy number
1830 ,p_cd_7 in varchar2, p_val_7 out nocopy number
1831 ,p_cd_8 in varchar2, p_val_8 out nocopy number
1832 ,p_cd_9 in varchar2, p_val_9 out nocopy number
1833 ,p_cd_10 in varchar2, p_val_10 out nocopy number
1834 ) is
1835 l_proc varchar2(80) := g_package||'.summary_by_action';
1836 Cursor c_reporting is
1837 Select count(*) amount, rep.rep_typ_cd
1838 from ben_reporting rep
1839 ,ben_benefit_actions bft
1840 where bft.benefit_action_id = rep.benefit_action_id
1841 and bft.request_id = p_concurrent_request_id
1842 and rep.rep_typ_cd in (p_cd_1, p_cd_2, p_cd_3, p_cd_4, p_cd_5
1843 ,p_cd_6, p_cd_7, p_cd_8, p_cd_9, p_cd_10)
1844 group by rep_typ_cd;
1845 --
1846 l_val_1 number :=0;
1847 l_val_2 number :=0;
1848 l_val_3 number :=0;
1849 l_val_4 number :=0;
1850 l_val_5 number :=0;
1851 l_val_6 number :=0;
1852 l_val_7 number :=0;
1853 l_val_8 number :=0;
1854 l_val_9 number :=0;
1855 l_val_10 number :=0;
1856 l_reporting c_reporting%rowtype;
1857 Begin
1858 hr_utility.set_location('Entering :'||l_proc,10);
1859 open c_reporting;
1860 loop
1861 fetch c_reporting into l_reporting;
1862 exit when c_reporting%notfound;
1863 If l_reporting.rep_typ_cd = p_cd_1 then
1864 p_val_1 := l_reporting.amount;
1865 Elsif l_reporting.rep_typ_cd = p_cd_2 then
1866 p_val_2 := l_reporting.amount;
1867 Elsif l_reporting.rep_typ_cd = p_cd_3 then
1868 p_val_3 := l_reporting.amount;
1869 Elsif l_reporting.rep_typ_cd = p_cd_4 then
1870 p_val_4 := l_reporting.amount;
1871 Elsif l_reporting.rep_typ_cd = p_cd_5 then
1872 p_val_5 := l_reporting.amount;
1873 Elsif l_reporting.rep_typ_cd = p_cd_6 then
1874 p_val_6 := l_reporting.amount;
1875 Elsif l_reporting.rep_typ_cd = p_cd_7 then
1876 p_val_7 := l_reporting.amount;
1877 Elsif l_reporting.rep_typ_cd = p_cd_8 then
1878 p_val_8 := l_reporting.amount;
1879 Elsif l_reporting.rep_typ_cd = p_cd_9 then
1880 p_val_9 := l_reporting.amount;
1881 Elsif l_reporting.rep_typ_cd = p_cd_10 then
1882 p_val_10 := l_reporting.amount;
1883 End if;
1884 End loop;
1885 close c_reporting;
1886 hr_utility.set_location('Leaving :'||l_proc,10);
1887 End summary_by_action;
1888 --
1889 -- ============================================================================
1890 -- <<Procedure: *get_rpt_header*>>
1891 -- ============================================================================
1892 --
1893 Procedure get_rpt_header
1894 (p_concurrent_request_id in number
1895 ,p_cd_1 out nocopy varchar2
1896 ,p_cd_2 out nocopy varchar2
1897 ,p_cd_3 out nocopy varchar2
1898 ,p_cd_4 out nocopy varchar2
1899 ,p_cd_5 out nocopy varchar2
1900 ,p_cd_6 out nocopy varchar2
1901 ,p_cd_7 out nocopy varchar2
1902 ,p_cd_8 out nocopy varchar2
1903 ,p_cd_9 out nocopy varchar2
1904 ,p_cd_10 out nocopy varchar2
1905 ,p_cd_11 out nocopy varchar2
1906 ,p_cd_12 out nocopy varchar2
1907 ,p_cd_13 out nocopy varchar2
1908 ,p_cd_14 out nocopy varchar2
1909 ,p_cd_15 out nocopy varchar2
1910 ,p_cd_16 out nocopy varchar2
1911 ,p_cd_17 out nocopy varchar2
1912 ,p_cd_18 out nocopy varchar2
1913 ,p_cd_19 out nocopy varchar2
1914 ,p_cd_20 out nocopy varchar2
1915 ) is
1916 Cursor c1 is
1917 select upper(c.execution_file_name)
1918 from fnd_concurrent_requests a
1919 ,fnd_concurrent_programs b
1920 ,fnd_executables c
1921 where a.request_id = p_concurrent_request_id
1922 and a.concurrent_program_id = b.concurrent_program_id
1923 and b.application_id = 805
1924 and c.application_id = 805
1925 and b.executable_id = c.executable_id
1926 ;
1927 L_proc varchar2(80) := g_package||'.get_rpt_header';
1928 l_exec varchar2(80);
1929 l_argumentNameVar dbms_describe.varchar2_table;
1930 l_OverLoadNum dbms_describe.number_table;
1931 l_PosNum dbms_describe.number_table;
1932 l_LevelNum dbms_describe.number_table;
1933 l_DataTypeNum dbms_describe.number_table;
1934 l_defaultValueNum dbms_describe.number_table;
1935 l_InOutNum dbms_describe.number_table;
1936 l_LengthNum dbms_describe.number_table;
1937 l_PrecisionNum dbms_describe.number_table;
1938 l_ScaleNum dbms_describe.number_table;
1939 l_RadixNum dbms_describe.number_table;
1940 l_SpareNum dbms_describe.number_table;
1941 l_cnt binary_integer := 1;
1942 lc binary_integer := 0;
1943 l_actn varchar2(80);
1944 Begin
1945 hr_utility.set_location('Entering :'||l_proc,05);
1946 l_actn := 'Openning C1 cursor...';
1947 open c1;
1948 fetch c1 into l_exec;
1949 If c1%found then
1950 l_actn := 'Calling hr_general.describe_procedure...'; -- Bug 1504327
1951 hr_general.describe_procedure --dbms_desbribe.dbms_procedure Bug 1504327
1952 (object_name => l_exec
1953 ,reserved1 => NULL
1954 ,reserved2 => NULL
1955 ,overload => l_OverLoadNum
1956 ,position => l_PosNum
1957 ,level => l_LevelNum
1958 ,argument_name => l_ArgumentNameVar
1959 ,datatype => l_DataTypeNum
1960 ,default_value => l_DefaultValueNum
1961 ,in_out => l_InOutNum
1962 ,length => l_LengthNum
1963 ,precision => l_precisionNum
1964 ,scale => l_scaleNum
1965 ,radix => l_RadixNum
1966 ,spare => l_SpareNum
1967 );
1968 l_actn := 'Outside while loop...';
1969 Begin
1970 While l_datatypenum(l_cnt) <> 0 loop
1971 If (upper(l_ArgumentNameVar(l_cnt)) in
1972 ('ERRBUF', 'RETCODE', 'P_BENEFIT_ACTION_ID')) then
1973 Null;
1974 Else
1975 Lc := Lc +1;
1976 If lc = 1 then
1977 p_cd_1 := upper(l_ArgumentNameVar(l_cnt));
1978 Elsif lc = 2 then
1979 p_cd_2 := upper(l_ArgumentNameVar(l_cnt));
1980 Elsif lc = 3 then
1981 p_cd_3 := upper(l_ArgumentNameVar(l_cnt));
1982 Elsif Lc = 4 then
1983 p_cd_4 := upper(l_ArgumentNameVar(l_cnt));
1984 Elsif Lc = 5 then
1985 p_cd_5 := upper(l_ArgumentNameVar(l_cnt));
1986 Elsif Lc = 6 then
1987 p_cd_6 := upper(l_ArgumentNameVar(l_cnt));
1988 Elsif Lc = 7 then
1989 p_cd_7 := upper(l_ArgumentNameVar(l_cnt));
1990 Elsif Lc = 8 then
1991 p_cd_8 := upper(l_ArgumentNameVar(l_cnt));
1992 Elsif Lc = 9 then
1993 p_cd_9 := upper(l_ArgumentNameVar(l_cnt));
1994 Elsif Lc = 10 then
1995 p_cd_10 := upper(l_ArgumentNameVar(l_cnt));
1996 Elsif Lc = 11 then
1997 p_cd_11 := upper(l_ArgumentNameVar(l_cnt));
1998 Elsif Lc = 12 then
1999 p_cd_12 := upper(l_ArgumentNameVar(l_cnt));
2000 Elsif Lc = 13 then
2001 p_cd_13 := upper(l_ArgumentNameVar(l_cnt));
2002 Elsif Lc = 14 then
2003 p_cd_14 := upper(l_ArgumentNameVar(l_cnt));
2004 Elsif Lc = 15 then
2005 p_cd_15 := upper(l_ArgumentNameVar(l_cnt));
2006 Elsif Lc = 16 then
2007 p_cd_16 := upper(l_ArgumentNameVar(l_cnt));
2008 Elsif Lc = 17 then
2009 p_cd_17 := upper(l_ArgumentNameVar(l_cnt));
2010 Elsif Lc = 18 then
2011 p_cd_18 := upper(l_ArgumentNameVar(l_cnt));
2012 Elsif Lc = 19 then
2013 p_cd_19 := upper(l_ArgumentNameVar(l_cnt));
2014 Elsif Lc = 20 then
2015 p_cd_20 := upper(l_ArgumentNameVar(l_cnt));
2016 End if;
2017 End if;
2018 l_cnt := l_cnt + 1;
2019 End loop;
2020 Exception
2021 when no_data_found then
2022 null;
2023 when others then
2024 raise;
2025 End;
2026 Else
2027 l_cnt := 0;
2028 End if;
2029 close c1;
2030 hr_utility.set_location('Leaving :'||l_proc,10);
2031 Exception
2032 When others then
2033 rpt_error(p_proc => l_proc, p_last_actn => l_actn);
2034 raise;
2035 End get_rpt_header;
2036 --
2037 -- ============================================================================
2038 -- <<Procedure: *standard_header*>>
2039 -- ============================================================================
2040 --
2041 Procedure standard_header
2042 (p_concurrent_request_id in number,
2043 p_concurrent_program_name out nocopy varchar2,
2044 p_process_date out nocopy date,
2045 p_mode out nocopy varchar2,
2046 p_derivable_factors out nocopy varchar2,
2047 p_validate out nocopy varchar2,
2048 p_person out nocopy varchar2,
2049 p_person_type out nocopy varchar2,
2050 p_program out nocopy varchar2,
2051 p_business_group out nocopy varchar2,
2052 p_plan out nocopy varchar2,
2053 p_popl_enrt_typ_cycl out nocopy varchar2,
2054 p_plans_not_in_programs out nocopy varchar2,
2055 p_just_programs out nocopy varchar2,
2056 p_comp_object_selection_rule out nocopy varchar2,
2057 p_person_selection_rule out nocopy varchar2,
2058 p_life_event_reason out nocopy varchar2,
2059 p_organization out nocopy varchar2,
2060 p_postal_zip_range out nocopy varchar2,
2061 p_reporting_group out nocopy varchar2,
2062 p_plan_type out nocopy varchar2,
2063 p_option out nocopy varchar2,
2064 p_eligibility_profile out nocopy varchar2,
2065 p_variable_rate_profile out nocopy varchar2,
2066 p_legal_entity out nocopy varchar2,
2067 p_payroll out nocopy varchar2,
2068 p_debug_message out nocopy varchar2,
2069 p_location out nocopy varchar2,
2070 p_audit_log out nocopy varchar2,
2071 p_benfts_group out nocopy varchar2,
2072 p_date_from out nocopy date, /* Bug 3517604 */
2073 p_status out nocopy varchar2) is
2074 --
2075 l_proc varchar2(80) := g_package||'.standard_header';
2076 l_all varchar2(80) := 'All';
2077 l_none varchar2(80) := 'None';
2078 --
2079 cursor c_benefit_actions is
2080 select bft.process_date,
2081 hr.meaning,
2082 hr1.meaning,
2083 hr2.meaning,
2084 nvl(ppf.full_name,l_all),
2085 nvl(ppt.user_person_type,l_all),
2086 nvl(pgm.name,l_all),
2087 pbg.name,
2088 nvl(pln.name,l_all),
2089 decode(hr5.meaning,
2090 null,
2091 l_all,
2092 hr5.meaning||
2093 ' '||
2094 pln2.name||
2095 ' '||
2096 pgm2.name||
2097 ' '||
2098 epo.strt_dt||
2099 ' '||
2100 epo.end_dt),
2101 hr3.meaning,
2102 hr4.meaning,
2103 nvl(ff.formula_name,l_none),
2104 nvl(ff2.formula_name,l_none),
2105 nvl(ler.name,l_all),
2106 nvl(org.name,l_all),
2107 decode(rzr.from_value||'-'||rzr.to_value,
2108 '-',
2109 l_all,
2110 rzr.from_value||'-'||rzr.to_value),
2111 nvl(bnr.name,l_all),
2112 nvl(ptp.name,l_all),
2113 nvl(opt.name,l_all),
2114 nvl(elp.name,l_all),
2115 nvl(vpf.name,l_all),
2116 nvl(tax.name,l_all),
2117 nvl(pay.payroll_name,l_all),
2118 decode(debug_messages_flag, 'Y', 'Yes', 'N', 'No', l_all) dg_msg,
2119 nvl(lc.location_code, l_all),
2120 decode(audit_log_flag, 'Y', 'Yes', 'N', 'No', l_all) audit_log,
2121 nvl(bnfg.name,l_all),
2122 conc.user_concurrent_program_name,
2123 fnd1.meaning,
2124 bft.date_from /* Bug 3517604 */
2125 from ben_benefit_actions bft,
2126 hr_lookups hr,
2127 hr_lookups hr1,
2128 hr_lookups hr2,
2129 hr_lookups hr3,
2130 hr_lookups hr4,
2131 hr_lookups hr5,
2132 fnd_lookups fnd1,
2133 per_people_f ppf,
2134 per_person_types ppt,
2135 ben_pgm_f pgm,
2136 per_business_groups pbg,
2137 ben_pl_f pln,
2138 ff_formulas_f ff,
2139 ff_formulas_f ff2,
2140 ben_ler_f ler,
2141 hr_all_organization_units_vl org,
2142 ben_rptg_grp_v bnr,
2143 ben_pl_typ_f ptp,
2144 ben_opt_f opt,
2145 ben_eligy_prfl_f elp,
2146 ben_vrbl_rt_prfl_f vpf,
2147 pay_payrolls_f pay,
2148 ben_pstl_zip_rng_f rzr,
2149 hr_tax_units_v tax,
2150 ben_popl_enrt_typ_cycl_f pop,
2151 ben_enrt_perd epo,
2152 ben_pl_f pln2,
2153 ben_pgm_f pgm2,
2154 fnd_concurrent_requests fnd,
2155 fnd_concurrent_programs_tl conc,
2156 hr_locations lc,
2157 ben_benfts_grp bnfg
2158 where fnd.request_id = p_concurrent_request_id
2159 and conc.concurrent_program_id = fnd.concurrent_program_id
2160 and conc.application_id = 805
2161 and conc.language = userenv('LANG') --Bug 2394141
2162 and bft.request_id = fnd.request_id
2163 and hr.lookup_code = bft.mode_cd
2164 and hr.lookup_type = 'BEN_BENMNGLE_MD'
2165 and hr1.lookup_code = bft.derivable_factors_flag
2166 and hr1.lookup_type = 'YES_NO'
2167 and hr2.lookup_code = bft.validate_flag
2168 and hr2.lookup_type = 'YES_NO'
2169 and hr3.lookup_code = bft.no_programs_flag
2170 and hr3.lookup_type = 'YES_NO'
2171 and hr4.lookup_code = bft.no_plans_flag
2172 and hr4.lookup_type = 'YES_NO'
2173 and hr5.lookup_code(+) = pop.enrt_typ_cycl_cd
2174 and hr5.lookup_type(+) = 'BEN_ENRT_TYP_CYCL'
2175 and fnd.status_code = fnd1.lookup_code
2176 and fnd1.lookup_type = 'CP_STATUS_CODE'
2177 and pop.popl_enrt_typ_cycl_id(+) = epo.popl_enrt_typ_cycl_id
2178 and bft.process_date
2179 between nvl(pop.effective_start_date,bft.process_date)
2180 and nvl(pop.effective_end_date,bft.process_date)
2181 and epo.enrt_perd_id(+) = bft.popl_enrt_typ_cycl_id
2182 and pln2.pl_id(+) = pop.pl_id
2183 and bft.process_date
2184 between nvl(pln2.effective_start_date,bft.process_date)
2185 and nvl(pln2.effective_end_date,bft.process_date)
2186 and pgm2.pgm_id(+) = pop.pgm_id
2187 and bft.process_date
2188 between nvl(pgm2.effective_start_date,bft.process_date)
2189 and nvl(pgm2.effective_end_date,bft.process_date)
2190 and ppf.person_id(+) = bft.person_id
2191 and bft.process_date
2192 between nvl(ppf.effective_start_date,bft.process_date)
2193 and nvl(ppf.effective_end_date,bft.process_date)
2194 and pay.payroll_id(+) = bft.payroll_id
2195 and bft.process_date
2196 between nvl(pay.effective_start_date,bft.process_date)
2197 and nvl(pay.effective_end_date,bft.process_date)
2198 and ppt.person_type_id(+) = bft.person_type_id
2199 and pgm.pgm_id(+) = bft.pgm_id
2200 and bft.process_date
2201 between nvl(pgm.effective_start_date,bft.process_date)
2202 and nvl(pgm.effective_end_date,bft.process_date)
2203 and pbg.business_group_id = bft.business_group_id
2204 and tax.tax_unit_id(+) = bft.legal_entity_id
2205 and pln.pl_id(+) = bft.pl_id
2206 and bft.process_date
2207 between nvl(pln.effective_start_date,bft.process_date)
2208 and nvl(pln.effective_end_date,bft.process_date)
2209 and ler.ler_id(+) = bft.ler_id
2210 and bft.process_date
2211 between nvl(ler.effective_start_date,bft.process_date)
2212 and nvl(ler.effective_end_date,bft.process_date)
2213 and rzr.pstl_zip_rng_id(+) = bft.pstl_zip_rng_id
2214 and bft.process_date
2215 between nvl(rzr.effective_start_date,bft.process_date)
2216 and nvl(rzr.effective_end_date,bft.process_date)
2217 and ptp.pl_typ_id(+) = bft.pl_typ_id
2218 and bft.process_date
2219 between nvl(ptp.effective_start_date,bft.process_date)
2220 and nvl(ptp.effective_end_date,bft.process_date)
2221 and opt.opt_id(+) = bft.opt_id
2222 and bft.process_date
2223 between nvl(opt.effective_start_date,bft.process_date)
2224 and nvl(opt.effective_end_date,bft.process_date)
2225 and ff.formula_id(+) = bft.comp_selection_rl
2226 and bft.process_date between
2227 nvl(ff.effective_start_date,bft.process_date)
2228 and nvl(ff.effective_end_date,bft.process_date)
2229 and ff2.formula_id(+) = bft.person_selection_rl
2230 and bft.process_date between
2231 nvl(ff2.effective_start_date,bft.process_date)
2232 and nvl(ff2.effective_end_date,bft.process_date)
2233 and bnr.rptg_grp_id(+) = bft.rptg_grp_id
2234 and elp.eligy_prfl_id(+) = bft.eligy_prfl_id
2235 and bft.process_date between
2236 nvl(elp.effective_start_date,bft.process_date)
2237 and nvl(elp.effective_end_date,bft.process_date)
2238 and vpf.vrbl_rt_prfl_id(+) = bft.vrbl_rt_prfl_id
2239 and bft.process_date between
2240 nvl(vpf.effective_start_date,bft.process_date)
2241 and nvl(vpf.effective_end_date,bft.process_date)
2242 and org.organization_id(+) = bft.organization_id
2243 and bft.process_date between
2244 nvl(org.date_from,bft.process_date)
2245 and nvl(org.date_to,bft.process_date)
2246 and nvl(bft.location_id,-1) = lc.location_id (+)
2247 and nvl(bft.benfts_grp_id,-1) = bnfg.benfts_grp_id (+)
2248 ;
2249 --
2250 begin
2251 --
2252 hr_utility.set_location('Entering :'||l_proc,10);
2253 --
2254 -- Get parameter information from batch process run
2255 --
2256 open c_benefit_actions;
2257 --
2258 fetch c_benefit_actions into p_process_date,
2259 p_mode,
2260 p_derivable_factors,
2261 p_validate,
2262 p_person,
2263 p_person_type,
2264 p_program,
2265 p_business_group,
2266 p_plan,
2267 p_popl_enrt_typ_cycl,
2268 p_plans_not_in_programs,
2269 p_just_programs,
2270 p_comp_object_selection_rule,
2271 p_person_selection_rule,
2272 p_life_event_reason,
2273 p_organization,
2274 p_postal_zip_range,
2275 p_reporting_group,
2276 p_plan_type,
2277 p_option,
2278 p_eligibility_profile,
2279 p_variable_rate_profile,
2280 p_legal_entity,
2281 p_payroll,
2282 p_debug_message,
2283 p_location,
2284 p_audit_log,
2285 p_benfts_group,
2286 p_concurrent_program_name,
2287 p_status,
2288 p_date_from; /* Bug 3517604 */
2289 --
2290 close c_benefit_actions;
2291 --
2292 hr_utility.set_location('Leaving :'||l_proc,10);
2293 --
2294 end standard_header;
2295 --
2296 --Bug 2978945
2297
2298 FUNCTION rows_exist
2299 (p_base_table_name IN VARCHAR2,
2300 p_base_key_column IN VARCHAR2,
2301 p_base_key_value IN NUMBER
2302 )
2303 RETURN BOOLEAN IS
2304 --
2305 l_proc VARCHAR2(72);
2306 l_ret_column number(1); -- Returning Sql Column
2307 g_dynamic_sql VARCHAR2(2000);
2308 g_debug BOOLEAN;
2309 --
2310 BEGIN
2311 g_debug := hr_utility.debug_enabled;
2312 IF g_debug THEN
2313 l_proc := g_package||'rows_exist';
2314 hr_utility.set_location('Entering:'||l_proc, 5);
2315 END IF;
2316 IF (p_base_key_value IS NOT NULL) THEN
2317 IF p_base_table_name IS NULL OR
2318 p_base_key_column IS NULL THEN
2319 -- Mandatory arg checking
2320 hr_api.mandatory_arg_error
2321 (p_api_name => l_proc,
2322 p_argument => 'p_base_table_name',
2323 p_argument_value => p_base_table_name);
2324 --
2325 hr_api.mandatory_arg_error
2326 (p_api_name => l_proc,
2327 p_argument => 'p_base_key_column',
2328 p_argument_value => p_base_key_column);
2329 --
2330 END IF;
2331 -- Define dynamic sql text with substitution tokens
2332 g_dynamic_sql :=
2333 'SELECT NULL '||
2334 'FROM '||LOWER(p_base_table_name)||' t '||
2335 'WHERE t.'||LOWER(p_base_key_column)||' = :p_base_key_value ';
2336
2337 EXECUTE IMMEDIATE g_dynamic_sql
2338 INTO l_ret_column
2339 USING p_base_key_value;
2340 -- one row exists so return true
2341 IF g_debug THEN
2342 hr_utility.set_location('Leaving:'||l_proc, 10);
2343 END IF;
2344 RETURN(TRUE);
2345 END IF;
2346 EXCEPTION
2347 WHEN NO_DATA_FOUND THEN
2348 IF g_debug THEN
2349 hr_utility.set_location('Leaving:'||l_proc, 15);
2350 END IF;
2351 -- return false as no rows exist
2352 RETURN(FALSE);
2353 WHEN TOO_MANY_ROWS THEN
2354 IF g_debug THEN
2355 hr_utility.set_location('Leaving:'||l_proc, 20);
2356 END IF;
2357 -- return TRUE because more than one row exists
2358 RETURN(TRUE);
2359 WHEN OTHERS THEN
2360 RAISE;
2361 END rows_exist;
2362
2363 --Bug 2978945
2364 --
2365 -- ==================================================================================
2366 -- << Procedure: person_selection_rule >>
2367 -- This procedure is added to report errors for a person while executing the selection rule
2368 -- and prevent the conc process from failing .
2369 -- ==================================================================================
2370 procedure person_selection_rule
2371 (p_person_id in Number
2372 ,p_business_group_id in Number
2373 ,p_person_selection_rule_id in Number
2374 ,p_effective_date in Date
2375 ,p_input1 in varchar2 default null -- Bug 5331889
2376 ,p_input1_value in varchar2 default null
2377 ,p_return in out nocopy varchar2
2378 ,p_err_message in out nocopy varchar2 ) as
2379
2380 Cursor c1 is
2381 Select assignment_id
2382 From per_assignments_f paf
2383 Where paf.person_id = p_person_id
2384 and paf.assignment_type <> 'C'
2385 And paf.primary_flag = 'Y'
2386 And paf.business_group_id = p_business_group_id
2387 And p_effective_date between paf.effective_start_date and paf.effective_end_date ;
2388 --
2389 Cursor c2 is
2390 Select assignment_id
2391 From per_all_assignments_f paf
2392 Where paf.person_id = p_person_id
2393 and paf.assignment_type <> 'C'
2394 And paf.primary_flag = 'Y'
2395 And paf.business_group_id = p_business_group_id
2396 And p_effective_date between paf.effective_start_date and paf.effective_end_date ;
2397 --
2398 l_proc varchar2(80) := g_package||'.person_selection_rule';
2399 l_outputs ff_exec.outputs_t;
2400 --l_return varchar2(30);
2401 l_assignment_id number;
2402 l_actn varchar2(80);
2403 value_exception exception ;
2404 Begin
2405 hr_utility.set_location ('Entering '||l_proc,10);
2406 --
2407 -- Get assignment ID form per_assignments_f table.
2408 --
2409 l_actn := 'Opening Assignment cursor...';
2410 --
2411 if hr_security.view_all = 'Y' and hr_general.get_xbg_profile = 'Y'
2412 then
2413 open c2;
2414 fetch c2 into l_assignment_id;
2415 If c2%notfound
2416 then
2417 write(p_text => 'Warning : No Primary assignment found for this Person ID : '|| p_person_id); --5643310
2418 End if;
2419 close c2;
2420 else
2421 open c1;
2422 fetch c1 into l_assignment_id;
2423 If c1%notfound
2424 then
2425 write(p_text => 'Warning : No Primary assignment found for this Person ID : '|| p_person_id); --5643310
2426 End if;
2427 close c1;
2428 end if;
2429 --
2430 -- Call formula initialise routine
2431 --
2432 l_actn := 'Calling benutils.formula procedure...';
2433
2434 l_outputs := benutils.formula
2435 (p_formula_id => p_person_selection_rule_id
2436 ,p_effective_date => p_effective_date
2437 ,p_business_group_id => p_business_group_id
2438 ,p_assignment_id => l_assignment_id
2439 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
2440 ,p_param1_value => to_char(p_person_id)
2441 ,p_param2 => p_input1
2442 ,p_param2_value => p_input1_value);
2443 p_return := l_outputs(l_outputs.first).value;
2444 --
2445 l_actn := 'Evaluating benutils.formula return...';
2446 --
2447 If upper(p_return) not in ('Y', 'N') then
2448 Raise value_exception ;
2449 End if;
2450 hr_utility.set_location ('Leaving '||l_proc,10);
2451 Exception
2452 When ben_batch_utils.g_record_error then
2453 p_return := 'N' ;
2454 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
2455 fnd_message.set_token('ID' ,to_char(p_person_id) );
2456 fnd_message.set_token('PROC',l_proc ) ;
2457 p_err_message := fnd_message.get ;
2458
2459 When value_exception then
2460 p_return := 'N' ;
2461 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
2462 fnd_message.set_token('RL','person_selection_rule_id :'||p_person_selection_rule_id);
2463 fnd_message.set_token('PROC',l_proc ) ;
2464 p_err_message := fnd_message.get ;
2465
2466 when others then
2467 p_return := 'N' ;
2468 p_err_message := 'Unhandled exception while processing Person : '||to_char(p_person_id)
2469 ||' in package : '|| l_proc ||'.' || substr(sqlerrm,1,170);
2470
2471 End person_selection_rule;
2472 --
2473 end ben_batch_utils;