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