[Home] [Help]
PACKAGE BODY: APPS.BEN_EXTRACT
Source
1 Package Body ben_extract as
2 /* $Header: benxtrct.pkb 120.11.12010000.2 2008/08/05 15:01:27 ubhat ship $ */
3 --
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' ben_extract.'; -- Global package name
10 --
11 g_debug boolean := hr_utility.debug_enabled;
12 --
13 Procedure setup_rcd_typ_lvl (
14 p_ext_file_id in number
15 )
16 is
17 -- ----------------------------------------------------------------------------
18 -- |------< setup_rcd_typ_lvl >------|-private-
19 -- ----------------------------------------------------------------------------
20 --
21 l_proc varchar2(72);
22
23 lb_rec_not_exists BOOLEAN :=TRUE;
24 --
25
26 cursor ext_rcd_rqd_c (
27 p_ext_file_id IN NUMBER
28 )
29 is
30 select distinct a.low_lvl_cd
31 from ben_ext_rcd a,
32 ben_ext_rcd_in_file b
33 where a.ext_rcd_id = b.ext_rcd_id
34 and b.ext_file_id = p_ext_file_id
35 and b.rqd_flag = 'Y'
36 -- Bug fix 1702733 - header/trailer type records should not be checked for required flag = 'Y' here
37 -- since they never get proccessed in xtrct_skltn. Also, ben_ext_person.process_ext_recs
38 -- processes only Detail Type records.
39 -- Hence the gtt_rcd_rqd_vals.rcd_found flag for 'H'/'T' type records, if retreived,
40 -- remains unchanged as FALSE, and this leads to raising of required_error exception
41 -- in ben_ext_person.process_ext_levels.
42 and a.rcd_type_cd = 'D'
43 -- end fix 1702733
44 and a.low_lvl_cd <> 'P';
45
46
47
48
49 cursor ext_rcd_rqd_seq_c (
50 p_ext_file_id IN NUMBER
51 )
52 is
53 select a.low_lvl_cd,b.seq_num
54 from ben_ext_rcd a,
55 ben_ext_rcd_in_file b
56 where a.ext_rcd_id = b.ext_rcd_id
57 and b.ext_file_id = p_ext_file_id
58 and b.rqd_flag = 'Y'
59 and a.rcd_type_cd in ( 'D','S') ; -- subheader
60 -- we need person level, person may be excluded if he does no have address
61 --and a.low_lvl_cd <> 'P';
62
63
64
65 cursor ext_rcd_typ_c (
66 p_ext_file_id IN NUMBER
67 )
68 is
69 select a.ext_rcd_id,
70 b.sort1_data_elmt_in_rcd_id,
71 b.sort2_data_elmt_in_rcd_id,
72 b.sort3_data_elmt_in_rcd_id,
73 b.sort4_data_elmt_in_rcd_id,
74 b.ext_rcd_in_file_id,
75 b.seq_num,
76 b.sprs_cd,
77 b.any_or_all_cd,
78 a.rcd_type_cd,
79 a.low_lvl_cd
80 from ben_ext_rcd a,
81 ben_ext_rcd_in_file b
82 where a.ext_rcd_id = b.ext_rcd_id
83 and b.ext_file_id = p_ext_file_id
84 order by b.seq_num;
85
86 --
87 Begin
88 --
89 g_debug := hr_utility.debug_enabled;
90 if g_debug then
91 l_proc := g_package||'setup_rcd_typ_lvl';
92 hr_utility.set_location('Entering:'||l_proc, 5);
93 end if;
94 --
95 /*
96 nw variable created with seq number
97 FOR rqd IN ext_rcd_rqd_c (p_ext_file_id => p_ext_file_id)
98 LOOP
99 lb_rec_not_exists := FALSE;
100
101 gtt_rcd_rqd_vals(ext_rcd_rqd_c%rowcount).low_lvl_cd := rqd.low_lvl_cd;
102 gtt_rcd_rqd_vals(ext_rcd_rqd_c%rowcount).rcd_found := FALSE;
103
104 END LOOP;
105
106 IF lb_rec_not_exists
107 THEN
108 gtt_rcd_rqd_vals(1).low_lvl_cd := 'NOREQDRCD';
109 gtt_rcd_rqd_vals(1).rcd_found := TRUE;
110 END IF;
111 */
112
113 -- reocrd level mandatory , not low level
114
115 lb_rec_not_exists:= TRUE;
116 FOR rqd IN ext_rcd_rqd_seq_c (p_ext_file_id => p_ext_file_id)
117 LOOP
118 lb_rec_not_exists := FALSE;
119
120 gtt_rcd_rqd_vals_seq(ext_rcd_rqd_seq_c%rowcount).low_lvl_cd := rqd.low_lvl_cd;
121 gtt_rcd_rqd_vals_seq(ext_rcd_rqd_seq_c%rowcount).rcd_found := FALSE;
122 gtt_rcd_rqd_vals_seq(ext_rcd_rqd_seq_c%rowcount).seq_num := rqd.seq_num ;
123
124 END LOOP;
125
126 IF lb_rec_not_exists
127 THEN
128 gtt_rcd_rqd_vals_seq(1).low_lvl_cd := 'NOREQDRCD';
129 gtt_rcd_rqd_vals_seq(1).rcd_found := TRUE;
130 END IF;
131 -- eof
132
133
134
135
136 lb_rec_not_exists:= TRUE;
137
138 FOR rtyp IN ext_rcd_typ_c (p_ext_file_id => p_ext_file_id)
139 LOOP
140 lb_rec_not_exists := FALSE;
141 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).ext_rcd_id
142 := rtyp.ext_rcd_id;
143 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).sort1
144 := rtyp.sort1_data_elmt_in_rcd_id;
145 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).sort2
146 := rtyp.sort2_data_elmt_in_rcd_id;
147 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).sort3
148 := rtyp.sort3_data_elmt_in_rcd_id;
149 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).sort4
150 := rtyp.sort4_data_elmt_in_rcd_id;
151 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).ext_rcd_in_file_id
152 := rtyp.ext_rcd_in_file_id;
153 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).seq_num
154 := rtyp.seq_num;
155 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).sprs_cd
156 := rtyp.sprs_cd;
157 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).any_or_all_cd
158 := rtyp.any_or_all_cd;
159 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).rcd_type_cd
160 := rtyp.rcd_type_cd;
161 gtt_rcd_typ_vals(ext_rcd_typ_c%rowcount).low_lvl_cd
162 := rtyp.low_lvl_cd;
163
164 END LOOP;
165
166 IF lb_rec_not_exists
167 THEN
168 gtt_rcd_typ_vals(1).low_lvl_cd := 'NOREQTYP';
169 END IF;
170 --
171 if g_debug then
172 hr_utility.set_location('Exiting:'||l_proc, 15);
173 end if;
174 --
175 End setup_rcd_typ_lvl;
176
177 --
178 -- ----------------------------------------------------------------------------
179 -- |------< xtrct_skltn>------|
180 -- ----------------------------------------------------------------------------
181 --
182 -- Description
183 -- This procedure will assign values for extract specific parameters
184 -- needed for each thread, such as extract levels/cursors to include.
185 -- This procedure will loop through person_id values predefined for each
186 -- thread and invoke extract processing for each person
187 --
188 -- Pre-Conditions
189 -- None.
190 --
191 -- In Parameters
192 -- p_ext_dfn_id
193 -- p_run_date
194 -- p_business_group_id
195 -- p_run_date
196 --
197 -- Post Success
198 -- Processing continues
199 --
200 -- Post Failure
201 -- No database changes
202 --
203 -- Access Status
204 -- Internal table handler use only.
205 --
206 -- ----------------------------------------------------------------------------------------------
207 Procedure xtrct_skltn(p_ext_dfn_id in number,
208 p_business_group_id in number,
209 p_effective_date in date,
210 p_benefit_action_id in number,
211 p_range_id in number,
212 p_start_person_action_id in number,
213 p_end_person_action_id in number,
214 p_data_typ_cd in varchar2,
215 p_ext_typ_cd in varchar2,
216 p_ext_crit_prfl_id in number,
217 p_ext_rslt_id in number,
218 p_ext_file_id in number,
219 p_ext_strt_dt in date,
220 p_ext_end_dt in date,
221 p_prmy_sort_cd in varchar2,
222 p_scnd_sort_cd in varchar2,
223 p_request_id in number,
224 p_use_eff_dt_for_chgs_flag in varchar2,
225 p_penserv_mode in varchar2
226 )
227 is
228 --
229 l_proc varchar2(72);
230 --
231 l_personid_va benutils.g_number_table := benutils.g_number_table();
232 l_pactid_va benutils.g_number_table := benutils.g_number_table();
233 l_pactovn_va benutils.g_number_table := benutils.g_number_table();
234 l_lerid_va benutils.g_number_table := benutils.g_number_table();
235 --
236 cursor bus_c
237 is
238 select name
239 from per_business_groups_perf
240 where business_group_id = p_business_group_id;
241 --
242 cursor c_ext_dfn
243 is
244 /* select spcl_hndl_flag,
245 upd_cm_sent_dt_flag,
246 ext_global_flag
247 from ben_ext_dfn xdf
248 where xdf.ext_dfn_id = p_ext_dfn_id;
249 */ -- Commented in Bug fix 4545881
250
251 select xdf.spcl_hndl_flag,
252 xdf.upd_cm_sent_dt_flag,
253 decode(xdf.data_typ_cd,'CW','Y',xcr.ext_global_flag) ext_global_flag
254 from ben_ext_dfn xdf,
255 ben_ext_crit_prfl xcr
256 where xdf.ext_dfn_id = p_ext_dfn_id
257 and xdf.ext_crit_prfl_id = xcr.ext_crit_prfl_id (+) ;
258 --
259 cursor c_overide_dt_cd(p_crit_typ_cd in varchar2) is
260 select xcv.val_1
261 from ben_ext_crit_val xcv,
262 ben_ext_crit_typ xct
263 where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
264 and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
265 and xct.crit_typ_cd = p_crit_typ_cd ;
266
267
268 l_err_message fnd_new_messages.message_text%type ;
269 --
270 BEGIN
271 --
272 g_debug := hr_utility.debug_enabled;
273 if g_debug then
274 l_proc := g_package||'xtrct_skltn';
275 hr_utility.set_location('Entering:'||l_proc, 5);
276 end if;
277 --
278 -- This next condition was added for performance enhancements. The
279 -- encompassed routines should only be called once per thread, not once
280 -- per chunk. th 3/2/2000
281 --
282 If nvl(g_request_id,-2) <> p_request_id
283 OR p_request_id = -1
284 then
285 --
286 -- Initialize globals
287 --
288 ben_ext_person.g_err_num := null;
289 ben_ext_person.g_err_name := null;
290 ben_ext_person.g_chg_enrt_rslt_id := null;
291 ben_ext_person.g_chg_input_value_id := null;
292 ben_ext_person.g_pay_last_start_date:= null;
293 ben_ext_person.g_pay_last_end_date := null;
294
295 --
296 g_ext_dfn_id := p_ext_dfn_id;
297 g_request_id := p_request_id;
298 g_ext_rslt_id := p_ext_rslt_id;
299 g_ext_strt_dt := p_ext_strt_dt;
300 g_ext_end_dt := p_ext_end_dt;
301 g_effective_date := p_effective_date;
302 g_spcl_hndl_flag := null;
303 g_run_date := sysdate;
304 --
305 open c_ext_dfn;
306 fetch c_ext_dfn into g_spcl_hndl_flag,
307 ben_ext_person.g_upd_cm_sent_dt_flag ,
308 ben_ext_person.g_ext_global_flag ;
309 close c_ext_dfn;
310 hr_utility.set_location( 'GLOBAL Flag ' || ben_ext_person.g_ext_global_flag , 99 ) ;
311 /*
312 --
313 -- MH moved call to thread level (ben_ext_thread.do_multithread)
314 -- rather than chunk level to minimize
315 -- memory consumption
316 --
317 --
318 -- Determine extract Levels
319 --
320 set_ext_lvls(p_ext_file_id => p_ext_file_id,
321 p_business_group_id => p_business_group_id);
322 */
323 --
324 -- retrieve business group name only if it is required
325 g_proc_business_group_id := p_business_group_id ;
326 if g_bg_csr = 'Y' then
327 open bus_c;
328 fetch bus_c into g_business_group_name;
329 close bus_c;
330 g_proc_business_group_name := g_business_group_name ;
331 end if;
332 --
333 -- Load Inclusion Tables for later processing.
334 --
335 IF p_ext_crit_prfl_id is not null THEN
336 --
337 ben_ext_evaluate_inclusion.Determine_Incl_Crit_To_Check(p_ext_crit_prfl_id);
338
339 -- cache the person and benefit overide dates.
340 -- get the person overide date
341 open c_overide_dt_cd ('PASOR') ;
342 fetch c_overide_dt_cd into g_pasor_dt_cd ;
343 close c_overide_dt_cd ;
344
345 -- get the benefit overide date
346 open c_overide_dt_cd ('BDTOR') ;
347 fetch c_overide_dt_cd into g_bdtor_dt_cd ;
348 close c_overide_dt_cd ;
349
350 END IF;
351 --
352 -- initialize extract totals
353 --
354 g_trans_num := 0;
355 g_per_num := 0;
356 g_error_num := 0;
357 /*
358 --
359 -- Initialize tables
360 gtt_rcd_rqd_vals.DELETE;
361 --
362 -- MH moved call to thread level (ben_ext_thread.do_multithread)
363 -- rather than chunk level to minimize
364 -- memory consumption
365 --
366 -- For each Person - pb24/3/00:'not sure what this comment is meant to mean'?
367 --
368 -- Setup record and required level tables
369 --
370 setup_rcd_typ_lvl
371 (p_ext_file_id => p_ext_file_id
372 );
373 --
374 */
375 end if;
376 --
377 -- Get the person id range for the person action id range
378 --
379 ben_maintain_benefit_actions.get_peractionrange_persondets
380 (p_benefit_action_id => p_benefit_action_id
381 ,p_start_person_action_id => p_start_person_action_id
382 ,p_end_person_action_id => p_end_person_action_id
383 --
384 ,p_personid_va => l_personid_va
385 ,p_pactid_va => l_pactid_va
386 ,p_pactovn_va => l_pactovn_va
387 ,p_lerid_va => l_lerid_va
388 );
389 --
390 if l_personid_va.count > 0
391 then
392 --
393 for vaen in l_personid_va.first..l_personid_va.last
394 loop
395 --
396 if g_debug then
397 hr_utility.set_location(' Person ID '||l_personid_va(vaen), 00 );
398 end if;
399 Begin
400 --
401 ben_ext_person.process_ext_person
402 (p_person_id => l_personid_va(vaen)
403 ,p_ext_dfn_id => p_ext_dfn_id
404 ,p_ext_rslt_id => p_ext_rslt_id
405 ,p_ext_file_id => p_ext_file_id
406 ,p_ext_crit_prfl_id => p_ext_crit_prfl_id
407 ,p_data_typ_cd => p_data_typ_cd
408 ,p_ext_typ_cd => p_ext_typ_cd
409 ,p_effective_date => p_effective_date
410 ,p_business_group_id => p_business_group_id
411 ,p_penserv_mode => p_penserv_mode ----------vkodedal changes for penserver 30-apr-2008
412 );
413 --
414 -- update the status for the person proceesed
415 -- this helps to restart only the person not processed
416 --
417 --
418 -- Bug 4161111 perf issue in GSI.
419 --
420 ben_person_actions_api.update_person_actions
424 ,p_effective_date => p_effective_date);
421 (p_person_action_id =>l_pactid_va(vaen)
422 ,p_action_status_cd => 'P'
423 ,p_object_version_number =>l_pactovn_va(vaen)
425 --
426 Exception
427 when ben_ext_person.detail_restart_error then
428
429 --- update the range to warning
430 --- the warning will be later converted to erro because
431 --- if error the range the subseqent range may not be executed , every time spawn the thread
432 --- the process validated for the errored thread so we set the range status to 'W'
433
434 update ben_batch_ranges set range_status_cd = 'W'
435 where range_id = p_range_id and range_status_cd = 'P';
436
437 l_err_message := ben_ext_fmt.get_error_msg(ben_Ext_person.g_err_num,
438 ben_Ext_person.g_err_name,ben_Ext_person.g_elmt_name ) ;
439 if g_debug then
440 hr_utility.set_location('err msg ' || l_err_message, 99.98 );
441 end if;
442 ben_ext_person.write_error(
443 p_err_num => ben_Ext_person.g_err_num,
444 p_err_name => l_err_message,
445 p_typ_cd => 'E',
446 p_request_id => ben_extract.g_request_id,
447 p_ext_rslt_id => p_ext_rslt_id
448 );
449 --- the changes are commited along with the error message in thge write error process
450
451 when Others then
452
453 --- update the range to warning
454 --- the warning will be later converted to erro because
455 --- if error the range the subseqent range may not be executed , every time spawn the thread
456 --- the process validated for the errored thread so we set the range status to 'W'
457
458 update ben_batch_ranges set range_status_cd = 'W'
459 where range_id = p_range_id and range_status_cd = 'P';
460 if nvl(ben_ext_person.g_err_num,-1) <> 94102 then
461 l_err_message := substr(sqlerrm,1,2000) ;
462 if g_debug then
463 hr_utility.set_location('err msg ' || l_err_message, 99.98 );
464 end if;
465 ben_ext_person.write_error(
466 p_err_num => 94701,
467 p_err_name => l_err_message,
468 p_typ_cd => 'E',
469 p_request_id => ben_extract.g_request_id,
470 p_ext_rslt_id => p_ext_rslt_id
471 );
472 --- the changes are commited along with the error message in thge write error process
473 end if ;
474 ben_ext_person.g_err_num := null ;
475
476 End ;
477 end loop;
478 --
479 end if;
480 --
481 /*
482 FOR person IN per_cursor LOOP
483 --
484 --
485 --
486 if g_debug then
487 hr_utility.set_location(' Person ID ' || person.person_id, 00 );
488 end if;
489 ben_ext_person.process_ext_person
490 ( p_person_id => person.person_id
491 , p_ext_dfn_id => p_ext_dfn_id
492 , p_ext_rslt_id => p_ext_rslt_id
493 , p_ext_file_id => p_ext_file_id
494 , p_ext_crit_prfl_id => p_ext_crit_prfl_id
495 , p_data_typ_cd => p_data_typ_cd
496 , p_ext_typ_cd => p_ext_typ_cd
497 , p_effective_date => p_effective_date
498 , p_business_group_id => p_business_group_id
499 );
500
501 --- update the status for the person proceesed
502 --- this helps to restart only the person not processed
503 update ben_person_actions act
504 set action_status_cd = 'P'
505 where person_id = person.person_id
506 and benefit_action_id = p_benefit_action_id;
507 --
508 END LOOP; -- person
509 */
510 --
511 --
512 if g_debug then
513 hr_utility.set_location('Exiting'||l_proc, 70);
514 end if;
515 --
516 commit;
517 --
518 EXCEPTION
519 --
520 WHEN g_max_err_num_exception THEN
521 --
522 update ben_batch_ranges set range_status_cd = 'E'
523 where range_id = p_range_id;
524 --
525 commit;
526 --
527 --
528 END xtrct_skltn;
529 --
530
531 --
532 -- ----------------------------------------------------------------------------
533 -- |------< set_ext_lvls >------|
534 -- ----------------------------------------------------------------------------
535 -- This procedure will determine extract levels and cursors required
536 -- for a given exrtact file layout definition. Package global variables
537 -- will be assigned values 'Y' or 'N' as appropriate.
538 --
539 Procedure set_ext_lvls(p_ext_file_id in number,
540 p_business_group_id in number
541 ) IS
542 --
543 l_proc varchar2(72);
544 --
545 l_dummy varchar2(30);
546 l_rec_lvl_cd varchar2(30);
547 l_cursor_cd varchar2(30);
551 --
548 --
549 l_err_name varchar2(50);
550 job_failure exception;
552 --
553 cursor ext_rec_lvl_c (p_ext_file_id number) is
554 select
555 decode(sum(decode(a.low_lvl_cd,'P',1,0)),0,'N','Y') g_per_lvl,
556 decode(sum(decode(a.low_lvl_cd,'E',1,0)),0,'N','Y') g_enrt_lvl,
557 decode(sum(decode(a.low_lvl_cd,'PR',1,0)),0,'N','Y') g_prem_lvl,
558 decode(sum(decode(a.low_lvl_cd,'D',1,0)),0,'N','Y') g_dpnt_lvl,
559 decode(sum(decode(a.low_lvl_cd,'Y',1,0)),0,'N','Y') g_payroll_lvl,
560 decode(sum(decode(a.low_lvl_cd,'G',1,0)),0,'N','Y') g_elig_lvl,
561 decode(sum(decode(a.low_lvl_cd,'F',1,0)),0,'N','Y') g_flex_lvl,
562 decode(sum(decode(a.low_lvl_cd,'B',1,0)),0,'N','Y') g_bnf_lvl,
563 decode(sum(decode(a.low_lvl_cd,'A',1,0)),0,'N','Y') g_actn_lvl,
564 decode(sum(decode(a.low_lvl_cd,'R',1,0)),0,'N','Y') g_runrslt_lvl,
565 decode(sum(decode(a.low_lvl_cd,'CO',1,0)),0,'N','Y') g_contact_lvl,
566 decode(sum(decode(a.low_lvl_cd,'ED',1,0)),0,'N','Y') g_eligdpnt_lvl,
567 decode(sum(decode(a.low_lvl_cd,'WG',1,0)),0,'N','Y') g_cwbgr_lvl,
568 decode(sum(decode(a.low_lvl_cd,'WR',1,0)),0,'N','Y') g_cwbrt_lvl,
569 decode(sum(decode(a.low_lvl_cd,'OR',1,0)),0,'N','Y') g_org_lvl, -- subheader
570 decode(sum(decode(a.low_lvl_cd,'PO',1,0)),0,'N','Y') g_pos_lvl, -- subheader
571 decode(sum(decode(a.low_lvl_cd,'JB',1,0)),0,'N','Y') g_job_lvl, -- subheader
572 decode(sum(decode(a.low_lvl_cd,'GR',1,0)),0,'N','Y') g_grd_lvl, -- subheader
573 decode(sum(decode(a.low_lvl_cd,'LO',1,0)),0,'N','Y') g_loc_lvl, -- subheader
574 decode(sum(decode(a.low_lvl_cd,'PY',1,0)),0,'N','Y') g_pay_lvl, -- subheader
575 decode(sum(decode(a.low_lvl_cd,'T',1,0)),0,'N','Y') g_otl_summ_lvl,
576 decode(sum(decode(a.low_lvl_cd,'TS',1,0)),0,'N','Y') g_otl_detl_lvl
577 from ben_ext_rcd a,
578 ben_ext_rcd_in_file b
579 where a.ext_rcd_id = b.ext_rcd_id
580 and b.ext_file_id = p_ext_file_id;
581
582 cursor ext_cursors_c (p_ext_file_id number) is
583 select
584 decode(sum(decode(e.csr_cd,'ADR',1,0)),0,'N','Y') g_addr_csr,
585 decode(sum(decode(e.csr_cd,'ASG',1,0)),0,'N','Y') g_asg_csr,
586 decode(sum(decode(e.csr_cd,'PHN',1,0)),0,'N','Y') g_phn_csr,
587 decode(sum(decode(e.csr_cd,'RT',1,0)),0,'N','Y') g_rt_csr,
588 decode(sum(decode(e.csr_cd,'LER',1,0)),0,'N','Y') g_ler_csr,
589 decode(sum(decode(e.csr_cd,'BGR',1,0)),0,'N','Y') g_bgr_csr,
590 decode(sum(decode(e.csr_cd,'MA',1,0)),0,'N','Y') g_ma_csr,
591 decode(sum(decode(e.csr_cd,'BP',1,0)),0,'N','Y') g_bp_csr,
592 decode(sum(decode(e.csr_cd,'BA',1,0)),0,'N','Y') g_ba_csr,
593 decode(sum(decode(e.csr_cd,'CHCRT',1,0)),0,'N','Y') g_chcrt_csr,
594 decode(sum(decode(e.csr_cd,'CHC',1,0)),0,'N','Y') g_chc_csr,
595 decode(sum(decode(e.csr_cd,'CMA',1,0)),0,'N','Y') g_cma_csr,
596 decode(sum(decode(e.csr_cd,'DP',1,0)),0,'N','Y') g_dp_csr,
597 decode(sum(decode(e.csr_cd,'DA',1,0)),0,'N','Y') g_da_csr,
598 decode(sum(decode(e.csr_cd,'DPCP',1,0)),0,'N','Y') g_dpcp_csr,
599 decode(sum(decode(e.csr_cd,'BG',1,0)),0,'N','Y') g_bg_csr,
600 decode(sum(decode(e.csr_cd,'BB1',1,0)),0,'N','Y') g_bb1_csr,
601 decode(sum(decode(e.csr_cd,'BB2',1,0)),0,'N','Y') g_bb2_csr,
602 decode(sum(decode(e.csr_cd,'BB3',1,0)),0,'N','Y') g_bb3_csr,
603 decode(sum(decode(e.csr_cd,'BB4',1,0)),0,'N','Y') g_bb4_csr,
604 decode(sum(decode(e.csr_cd,'BB5',1,0)),0,'N','Y') g_bb5_csr,
605 decode(sum(decode(e.csr_cd,'PPCP',1,0)),0,'N','Y') g_ppcp_csr,
606 decode(sum(decode(e.csr_cd,'PGN',1,0)),0,'N','Y') g_pgn_csr,
607 decode(sum(decode(e.csr_cd,'ABS',1,0)),0,'N','Y') g_abs_csr,
608 decode(sum(decode(e.csr_cd,'PPREM',1,0)),0,'N','Y') g_pprem_csr,
609 decode(sum(decode(e.csr_cd,'EPREM',1,0)),0,'N','Y') g_eprem_csr,
610 decode(sum(decode(e.csr_cd,'FLXCR',1,0)),0,'N','Y') g_flxcr_csr,
611 decode(sum(decode(e.csr_cd,'ERGRP',1,0)),0,'N','Y') g_ergrp_csr,
612 decode(sum(decode(e.csr_cd,'PRGRP',1,0)),0,'N','Y') g_prgrp_csr,
613 decode(sum(decode(e.csr_cd,'ASA',1,0)),0,'N','Y') g_asa_csr,
614 decode(sum(decode(e.csr_cd,'EPLYR',1,0)),0,'N','Y') g_eplyr_csr,
615 decode(sum(decode(e.csr_cd,'PPLYR',1,0)),0,'N','Y') g_pplyr_csr,
616 decode(sum(decode(e.csr_cd,'ELER',1,0)),0,'N','Y') g_eler_csr,
617 decode(sum(decode(e.csr_cd,'PLER',1,0)),0,'N','Y') g_pler_csr,
618 decode(sum(decode(e.csr_cd,'PMPR',1,0)),0,'N','Y') g_pmpr_csr,
619 decode(sum(decode(e.csr_cd,'PMTPR',1,0)),0,'N','Y') g_pmtpr_csr,
620 decode(sum(decode(e.csr_cd,'INTRM',1,0)),0,'N','Y') g_intrm_csr,
621 decode(sum(decode(e.csr_cd,'INT',1,0)),0,'N','Y') g_int_csr,
622 decode(sum(decode(e.csr_cd,'CBRA',1,0)),0,'N','Y') g_cbra_csr,
623 decode(sum(decode(e.csr_cd,'COA',1,0)),0,'N','Y') g_coa_csr,
624 decode(sum(decode(e.csr_cd,'COP',1,0)),0,'N','Y') g_cop_csr,
625 decode(sum(decode(e.csr_cd,'COED',1,0)),0,'N','Y') g_coed_csr,
626 decode(sum(decode(e.csr_cd,'COCD',1,0)),0,'N','Y') g_cocd_csr,
627 decode(sum(decode(e.csr_cd,'COB',1,0)),0,'N','Y') g_cob_csr,
628 decode(sum(decode(e.csr_cd,'COSL',1,0)),0,'N','Y') g_cosl_csr,
629 decode(sum(decode(e.csr_cd,'COEL',1,0)),0,'N','Y') g_coel_csr,
630 decode(sum(decode(e.csr_cd,'EDP',1,0)),0,'N','Y') g_edp_csr,
634 decode(sum(decode(e.csr_cd,'BSL',1,0)),0,'N','Y') g_bsl_csr,
631 decode(sum(decode(e.csr_cd,'EDA',1,0)),0,'N','Y') g_eda_csr,
632 decode(sum(decode(e.csr_cd,'POS',1,0)),0,'N','Y') g_pos_csr,
633 decode(sum(decode(e.csr_cd,'SUP',1,0)),0,'N','Y') g_sup_csr,
635 decode(sum(decode(e.csr_cd,'SHL',1,0)),0,'N','Y') g_shl_csr,
636 decode(sum(decode(e.csr_cd,'CWPG',1,0)),0,'N','Y') g_cwbdg_csr ,
637 decode(sum(decode(e.csr_cd,'CWPR',1,0)),0,'N','Y') g_cwbawr_csr,
638 decode(sum(decode(e.csr_cd,'CBRADM',1,0)),0,'N','Y') g_cbradm_csr
639 from ben_ext_rcd_in_file a,
640 ben_ext_rcd b,
641 ben_ext_data_elmt_in_rcd c,
642 ben_ext_data_elmt d,
643 ben_ext_fld e
644 where a.ext_file_id = p_ext_file_id
645 and a.ext_rcd_id = b.ext_rcd_id
646 and c.ext_rcd_id = b.ext_rcd_id
647 and d.ext_data_elmt_id = c.ext_data_elmt_id
648 and e.ext_fld_id = d.ext_fld_id;
649
650 -- subheader global variable for multithread
651 cursor c_ext_file (p_file_id number) is
652 select ext_data_elmt_in_rcd_id1,
653 ext_data_elmt_in_rcd_id2
654 from ben_Ext_file exf
655 where exf.ext_file_id = p_file_id ;
656
657
658 cursor c_ext_elmt (p_data_elmt_in_rcd_id number
659 ) is
660 select exf.short_name
661 from ben_ext_fld exf,
662 ben_Ext_data_elmt_in_rcd edr,
663 ben_ext_data_elmt ede
664 where edr.ext_data_elmt_in_rcd_id = p_data_elmt_in_rcd_id
665 and edr.ext_data_elmt_id = ede.ext_Data_elmt_id
666 and ede.ext_fld_id = exf.ext_fld_id (+)
667 ;
668
669 l_ext_rcd c_ext_file%rowtype ;
670 -- eof subheader
671
672
673
674 --
675 begin
676 --
677 g_debug := hr_utility.debug_enabled;
678 if g_debug then
679 l_proc := g_package||'set_ext_lvls';
680 hr_utility.set_location('Entering'||l_proc, 5);
681 end if;
682 --
683 -- determine extract record levels:
684 -- ==============================================================
685 open ext_rec_lvl_c(p_ext_file_id => p_ext_file_id);
686 fetch ext_rec_lvl_c into
687 g_per_lvl,
688 g_enrt_lvl,
689 g_prem_lvl,
690 g_dpnt_lvl,
691 g_payroll_lvl,
692 g_elig_lvl,
693 g_flex_lvl,
694 g_bnf_lvl,
695 g_actn_lvl,
696 g_runrslt_lvl,
697 g_contact_lvl,
698 g_eligdpnt_lvl,
699 g_cwb_bdgt_lvl ,
700 g_cwb_awrd_lvl ,
701 g_org_lvl, -- subheader
702 g_pos_lvl, -- subheader
703 g_job_lvl, -- subheader
704 g_grd_lvl, -- subheader
705 g_loc_lvl, -- subheader
706 g_pay_lvl, -- subheader
707 g_otl_summ_lvl,
708 g_otl_detl_lvl;
709 --
710 close ext_rec_lvl_c;
711
712 if g_org_lvl = 'Y' or g_pos_lvl = 'Y' or g_job_lvl = 'Y' or g_loc_lvl = 'Y' or
713 g_pay_lvl = 'Y' or g_grd_lvl = 'Y'
714 then
715 g_subhead_dfn := 'Y' ;
716 end if ;
717
718 --
719 -- ============================================
720 -- determine extract cursor needed:
721 -- ============================================
722 --
723
724 open ext_cursors_c (p_ext_file_id => p_ext_file_id);
725 fetch ext_cursors_c into
726 g_addr_csr,
727 g_asg_csr,
728 g_phn_csr,
729 g_rt_csr,
730 g_ler_csr,
731 g_bgr_csr,
732 g_ma_csr,
733 g_bp_csr,
734 g_ba_csr,
735 g_chcrt_csr,
736 g_chc_csr,
737 g_cma_csr,
738 g_dp_csr,
739 g_da_csr,
740 g_dpcp_csr,
741 g_bg_csr,
742 g_bb1_csr,
743 g_bb2_csr,
744 g_bb3_csr,
745 g_bb4_csr,
746 g_bb5_csr,
747 g_ppcp_csr,
748 g_pgn_csr,
749 g_abs_csr,
750 g_pprem_csr,
751 g_eprem_csr,
752 g_flxcr_csr,
753 g_ergrp_csr,
754 g_prgrp_csr,
755 g_asa_csr,
756 g_eplyr_csr,
757 g_pplyr_csr,
758 g_eler_csr,
759 g_pler_csr,
760 g_pmpr_csr,
761 g_pmtpr_csr,
762 g_intrm_csr,
763 g_int_csr,
764 g_cbra_csr,
765 g_coa_csr,
766 g_cop_csr,
767 g_coed_csr,
768 g_cocd_csr,
769 g_cob_csr,
770 g_cosl_csr,
771 g_coel_csr,
772 g_edp_csr,
773 g_eda_csr,
774 g_pos_csr,
775 g_sup_csr,
776 g_bsl_csr,
777 g_shl_csr,
778 g_cwbdg_csr,
779 g_cwbawr_csr,
780 g_cbradm_csr ;
781
782 --
783 close ext_cursors_c;
784
785 --
786
787 --subhead
788
789 if ben_ext_thread.g_ext_group_elmt1 is null then
790 open c_ext_file(p_ext_file_id) ;
794 if l_ext_rcd.ext_data_elmt_in_rcd_id1 is not null then
791 fetch c_ext_file into l_ext_rcd ;
792 close c_ext_file ;
793
795 open c_ext_elmt(l_ext_rcd.ext_data_elmt_in_rcd_id1) ;
796 fetch c_ext_elmt into ben_ext_thread.g_ext_group_elmt1 ;
797 close c_ext_elmt ;
798
799 if l_ext_rcd.ext_data_elmt_in_rcd_id2 is not null then
800 open c_ext_elmt(l_ext_rcd.ext_data_elmt_in_rcd_id2) ;
801 fetch c_ext_elmt into ben_ext_thread.g_ext_group_elmt2 ;
802 close c_ext_elmt ;
803 end if ;
804 end if ;
805 end if ;
806 -- eof subheader
807
808 if g_debug then
809 hr_utility.set_location('Exiting'||l_proc, 15);
810 end if;
811 --
812 End set_ext_lvls;
813 --
814 End ben_extract;