[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_PAYROLL
Source
1 package body BEN_EXT_PAYROLL as
2 /* $Header: benxpayr.pkb 120.3.12010000.3 2008/09/05 13:16:27 vkodedal ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Extract Payroll Process.
12 Purpose:
13 This process handles fields that are related to Element Entries and Input Values.
14 History:
15 Date Who Version What?
16 ---- --- ------- -----
17 18 Dec 98 Ty Hayden 115.0 Created.
18 21 Dec 98 Ty Hayden 115.1 Added g_chg_input_value_idn to cursor.
19 09 Mar 99 G Perry 115.2 IS to AS.
20 13 Mar 99 Ty Hayden 115.3 Moved globals to ben_ext_person.
21 17 May 99 Isen 115.4 Removed null others exception handler
22 - bug 2422.
23 25 May 99 Isen 115.5 Added procedure to initialize the globals
24 01 Jul 99 Asen 115.6 Added code for decode fields.
25 1 Jul 00 Sdas 115.7 Added g3_element_entry_value_is.
26 2 apr 02 tjesumic 115.9 g_element_eev_eff_strt_date,g_element_eev_eff_end_date
27 added for 2202990
28 3 apr 02 tjesumic 115.10 dbdrv added
29 24-Dec-05 tjesumic 115.11 formula added for payroll
30 24-Dec-05 tjesumic 115.12 formula added for payroll
31 27-Feb-06 anshghos 115.13 modified cursor c_pay, removed last where clause of
32 "and screen_entry_value is not null;"
33 This is cause in element repeating level, elements without a
34 PayValue were not being picked up.
35 11-Jun-07 tjesumic 115.14 for performance c_pay cursor splited into differenct cursors
36 05-sep-08 vkodedal 115.15 performance fix - allowance extract - penserver - 7371957
37 */
38 -----------------------------------------------------------------------------------
39 --
40 g_package varchar2(33) := ' ben_ext_payroll.'; -- Global package name
41 --
42 -- procedure to initialize the globals - May, 99
43 -- --------------------------------------------------------------------------------
44 -- |----------------< initialize_globals >----------------------------------------------|
45 -- --------------------------------------------------------------------------------
46 PROCEDURE initialize_globals IS
47 --
48 --
49 l_proc varchar2(72) := g_package||'initialize_globals';
50 --
51 Begin
52 --
53 --
54 hr_utility.set_location('Entering'||l_proc, 5);
55 --
56 ben_ext_person.g_element_name := null;
57 ben_ext_person.g_element_id := null;
58 ben_ext_person.g_element_reporting_name := null;
59 ben_ext_person.g_element_description := null;
60 ben_ext_person.g_element_classification_name := null;
61 ben_ext_person.g_element_classification_id := null;
62 ben_ext_person.g_element_processing_type := null;
63 ben_ext_person.g_element_input_currency_code := null;
64 ben_ext_person.g_element_output_currency_code := null;
65 ben_ext_person.g_element_skip_rule := null;
66 ben_ext_person.g_element_skip_rule_id := null;
67 ben_ext_person.g_element_input_value_name := null;
68 ben_ext_person.g_element_input_value_id := null;
69 ben_ext_person.g_element_input_value_units := null;
70 ben_ext_person.g_element_input_value_sequence := null;
71 ben_ext_person.g_element_entry_value := null;
72 ben_ext_person.g_element_entry_costing := null;
73 ben_ext_person.g_element_entry_costing_id := null;
74 ben_ext_person.g_element_entry_reason := null;
75 ben_ext_person.g_element_entry_id := null;
76 ben_ext_person.g_element_entry_eff_start_date := null;
77 ben_ext_person.g_element_entry_eff_end_date := null;
78 ben_ext_person.g_element_entry_value_id := null;
79 ben_ext_person.g_element_eev_eff_strt_date := null;
80 ben_ext_person.g_element_eev_eff_end_date := null;
81 --
82 hr_utility.set_location('Exiting'||l_proc, 15);
83 --
84 End initialize_globals;
85 --
86 --
87 PROCEDURE main
88 ( p_person_id in number,
89 p_ext_rslt_id in number,
90 p_ext_file_id in number,
91 p_data_typ_cd in varchar2,
92 p_ext_typ_cd in varchar2,
93 p_chg_evt_cd in varchar2,
94 p_business_group_id in number,
95 p_effective_date in date) is
96 --
97 l_proc varchar2(72) := g_package||'main';
98 --
99 l_include varchar2(1) := 'Y';
100 --
101
102 --Changes for Penserver: Start
103 /* cursor c_pay is
104 select
105 iv.name,
106 iv.uom,
107 iv.display_sequence,
108 iv.input_value_id,
109 iv.element_type_id,
110 eev.screen_entry_value,
111 eev.element_entry_value_id,
112 eev.effective_start_date eev_effective_start_date,
113 eev.effective_end_date eev_effective_end_date,
114 ee.cost_allocation_keyflex_id,
115 ee.reason,
116 ee.effective_start_date,
117 ee.effective_end_date,
118 ee.element_entry_id
119 from
120 pay_element_entry_values_f eev,
121 pay_element_entries_f ee,
122 per_all_assignments_f asg,
123 pay_input_values_f iv
124 where
125 asg.person_id = p_person_id
126 and asg.assignment_id = ee.assignment_id
127 and ee.element_entry_id = eev.element_entry_id
128 and iv.input_value_id = eev.input_value_id
129 and (ben_ext_person.g_chg_input_value_id is null
130 or ben_ext_person.g_chg_input_value_id = iv.input_value_id
131 )
132 and p_effective_date between asg.effective_start_date and asg.effective_end_date
133 and p_effective_date between ee.effective_start_date and ee.effective_end_date
134 and p_effective_date between eev.effective_start_date and eev.effective_end_date
135 and p_effective_date between iv.effective_start_date and iv.effective_end_date
136 ;
137 */
138 CURSOR c_get_evnt_group
139 IS
140 SELECT val_1
141 FROM BEN_EXT_CRIT_VAL val,
142 BEN_EXT_CRIT_TYP typ,
143 BEN_EXT_CRIT_PRFL prfl
144 WHERE val.ext_crit_typ_id = typ.ext_crit_typ_id
145 AND typ.ext_crit_prfl_id = prfl.ext_crit_prfl_id
146 AND prfl.name = 'PQP GB PenServer Periodic Changes Criteria - Allowance History'
147 AND prfl.legislation_code = 'GB'
148 AND typ.crit_typ_cd = 'CPE'
149 AND typ.legislation_code = 'GB'
150 AND val.legislation_code = 'GB';
151
152 l_allowance_flag varchar2(2) := 'N';
153
154 TYPE get_pay_ref_csr_typ IS REF CURSOR;
155 c_pay get_pay_ref_csr_typ;
156
157
158 TYPE get_csr_val IS RECORD
159 (
160 name pay_input_values_f.name%TYPE,
161 uom pay_input_values_f.uom%TYPE,
162 display_sequence pay_input_values_f.display_sequence%TYPE,
163 input_value_id pay_input_values_f.input_value_id%TYPE,
164 element_type_id pay_input_values_f.element_type_id%TYPE,
165 screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE,
166 element_entry_value_id pay_element_entry_values_f.element_entry_value_id%TYPE,
167 eev_effective_start_date pay_element_entry_values_f.effective_start_date%TYPE,
168 eev_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE,
169 cost_allocation_keyflex_id pay_element_entries_f.cost_allocation_keyflex_id%TYPE,
170 reason pay_element_entries_f.reason%TYPE,
171 effective_start_date pay_element_entries_f.effective_start_date%TYPE,
172 effective_end_date pay_element_entries_f.effective_end_date%TYPE,
173 element_entry_id pay_element_entries_f.element_entry_id%TYPE
174 );
175
176 pay get_csr_val;
177 --Changes for Penserver: Stop
178
179
180 cursor c_ele_type (p_element_type_id number) is
181 select
182 et.element_name,
183 et.reporting_name,
184 et.description,
185 et.classification_id,
186 et.processing_type,
187 et.input_currency_code,
188 et.output_currency_code,
189 et.formula_id -- skip rule
190 from pay_element_types_f et
191 where et.element_type_id = p_element_type_id
192 and p_effective_date between et.effective_start_date and et.effective_end_date
193 ;
194
195
196 cursor c_ele_clas (p_classification_id number) is
197 select ec.classification_name
198 from pay_element_classifications ec
199 where ec.classification_id = p_classification_id
200 ;
201
202 cursor c_ff_name (p_formula_id number) is
203 select ff.formula_name
204 from ff_formulas_f ff
205 where ff.formula_id = p_formula_id
206 and p_effective_date between ff.effective_start_date and ff.effective_end_date
207 ;
208
209 cursor c_ele_conc (p_cost_allocation_keyflex_id number) is
210 select ca.concatenated_segments
211 from pay_cost_allocation_keyflex ca
212 where ca.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
213 ;
214
215
216 /*
217
218 cursor c_pay is
219 select
220 et.element_name,
221 et.element_type_id,
222 et.reporting_name,
223 et.description,
224 et.classification_id,
225 ec.classification_name,
226 et.processing_type,
227 et.input_currency_code,
228 et.output_currency_code,
229 et.formula_id, -- skip rule
230 ff.formula_name,
231 iv.name,
232 iv.uom,
233 iv.display_sequence,
234 iv.input_value_id,
235 eev.screen_entry_value,
236 eev.element_entry_value_id,
237 eev.effective_start_date eev_effective_start_date,
238 eev.effective_end_date eev_effective_end_date,
239 ee.cost_allocation_keyflex_id,
240 ca.concatenated_segments,
241 ee.reason,
242 ee.effective_start_date,
243 ee.effective_end_date,
244 ee.element_entry_id
245 from
246 pay_element_entry_values_f eev,
247 pay_element_entries_f ee,
248 per_all_assignments_f asg,
249 per_all_people_f per,
250 pay_input_values_f iv,
251 pay_element_types_f et,
252 pay_element_classifications ec,
253 ff_formulas_f ff,
254 pay_cost_allocation_keyflex ca
255 where
256 per.person_id = p_person_id
257 and iv.input_value_id = nvl(ben_ext_person.g_chg_input_value_id,iv.input_value_id)
258 and ee.element_entry_id = eev.element_entry_id
259 and asg.assignment_id = ee.assignment_id
260 and per.person_id = asg.person_id
261 and iv.input_value_id = eev.input_value_id
262 and iv.element_type_id = et.element_type_id
263 and et.classification_id = ec.classification_id (+)
264 and ee.cost_allocation_keyflex_id = ca.cost_allocation_keyflex_id (+)
265 and et.formula_id = ff.formula_id (+)
266 and p_effective_date between nvl(asg.effective_start_date,p_effective_date)
267 and nvl(asg.effective_end_date ,p_effective_date)
268 and p_effective_date between nvl(per.effective_start_date,p_effective_date)
269 and nvl(per.effective_end_date ,p_effective_date)
270 and p_effective_date between nvl(ee.effective_start_date,p_effective_date)
271 and nvl(ee.effective_end_date ,p_effective_date)
272 and p_effective_date between nvl(eev.effective_start_date,p_effective_date)
273 and nvl(eev.effective_end_date ,p_effective_date)
274 and p_effective_date between nvl(iv.effective_start_date,p_effective_date)
275 and nvl(iv.effective_end_date ,p_effective_date)
276 and p_effective_date between nvl(et.effective_start_date,p_effective_date)
277 and nvl(et.effective_end_date ,p_effective_date)
278 and p_effective_date between nvl(ff.effective_start_date,p_effective_date)
279 and nvl(ff.effective_end_date,p_effective_date);
280 -- and screen_entry_value is not null;
281
282 */
283
284
285 BEGIN
286 --
287 hr_utility.set_location('Entering'||l_proc, 5);
288 --
289 --Changes for Penserver: Start
290 FOR event IN c_get_evnt_group
291 LOOP
292 hr_utility.set_location('event.val_1: '||event.val_1, 100);
293 IF event.val_1 = p_chg_evt_cd
294 THEN
295 hr_utility.set_location('Set allowance flag= Y and exit loop', 100);
296 l_allowance_flag := 'Y';
297 EXIT;
298 END IF;
299 END LOOP;
300
301 hr_utility.set_location('l_allowance_flag: '||l_allowance_flag, 100);
302
303 IF l_allowance_flag = 'Y'
304 THEN
305 OPEN c_pay FOR
306 select
307 iv.name,
308 iv.uom,
309 iv.display_sequence,
310 iv.input_value_id,
311 iv.element_type_id,
312 eev.screen_entry_value,
313 eev.element_entry_value_id,
314 eev.effective_start_date eev_effective_start_date,
315 eev.effective_end_date eev_effective_end_date,
316 ee.cost_allocation_keyflex_id,
317 ee.reason,
318 ee.effective_start_date,
319 ee.effective_end_date,
320 ee.element_entry_id
321 from
322 pay_element_entry_values_f eev,
323 pay_element_entries_f ee,
324 per_all_assignments_f asg,
325 pay_input_values_f iv
326 where
327 asg.person_id = p_person_id
328 and asg.assignment_id = ee.assignment_id
329 and ee.element_entry_id = eev.element_entry_id
330 and iv.input_value_id = eev.input_value_id
331 and ee.element_type_id in (Select element_type_id
332 From PAY_ELEMENT_TYPE_RULES rule,
333 PAY_EVENT_GROUP_USAGES usg,
334 pay_event_groups grp
335 Where rule.element_set_id = usg.element_set_id
336 AND usg.event_group_id = grp.event_group_id
337 AND grp.event_group_name = 'PQP_GB_PSI_ALL_ELEMENT_ENTRIES'
338 AND grp.legislation_code = 'GB')
339 and (ben_ext_person.g_chg_input_value_id is null
340 or ben_ext_person.g_chg_input_value_id = iv.input_value_id
341 )
342 and p_effective_date between asg.effective_start_date and asg.effective_end_date
343 and p_effective_date between ee.effective_start_date and ee.effective_end_date
344 and p_effective_date between eev.effective_start_date and eev.effective_end_date
345 and p_effective_date between iv.effective_start_date and iv.effective_end_date;
346
347 ELSE
348 OPEN c_pay FOR
349 select
350 iv.name,
351 iv.uom,
352 iv.display_sequence,
353 iv.input_value_id,
354 iv.element_type_id,
355 eev.screen_entry_value,
356 eev.element_entry_value_id,
357 eev.effective_start_date eev_effective_start_date,
358 eev.effective_end_date eev_effective_end_date,
359 ee.cost_allocation_keyflex_id,
360 ee.reason,
361 ee.effective_start_date,
362 ee.effective_end_date,
363 ee.element_entry_id
364 from
365 pay_element_entry_values_f eev,
366 pay_element_entries_f ee,
367 per_all_assignments_f asg,
368 pay_input_values_f iv
369 where
370 asg.person_id = p_person_id
371 and asg.assignment_id = ee.assignment_id
372 and ee.element_entry_id = eev.element_entry_id
373 and iv.input_value_id = eev.input_value_id
374 and (ben_ext_person.g_chg_input_value_id is null
375 or ben_ext_person.g_chg_input_value_id = iv.input_value_id
376 )
377 and p_effective_date between asg.effective_start_date and asg.effective_end_date
378 and p_effective_date between ee.effective_start_date and ee.effective_end_date
379 and p_effective_date between eev.effective_start_date and eev.effective_end_date
380 and p_effective_date between iv.effective_start_date and iv.effective_end_date;
381 END IF;
382
383 LOOP
384 FETCH c_pay INTO pay;
385 EXIT WHEN c_pay%NOTFOUND;
386
387 -- initialize the globals - May, 99
388 initialize_globals;
389
390 ben_ext_person.g_element_input_value_name := pay.name;
391 ben_ext_person.g_element_input_value_units := pay.uom;
392 ben_ext_person.g_element_input_value_sequence := pay.display_sequence;
393 ben_ext_person.g_element_input_value_id := pay.input_value_id;
394 ben_ext_person.g_element_id := pay.element_type_id;
395 ben_ext_person.g_element_entry_value := pay.screen_entry_value;
396 ben_ext_person.g_element_entry_value_id := pay.element_entry_value_id;
397 ben_ext_person.g_element_entry_eff_start_date := pay.effective_start_date;
398 ben_ext_person.g_element_entry_eff_end_date := pay.effective_end_date;
399 ben_ext_person.g_element_entry_costing_id := pay.cost_allocation_keyflex_id;
400 ben_ext_person.g_element_entry_reason := pay.reason;
401 ben_ext_person.g_element_eev_eff_strt_date := pay.eev_effective_start_date;
402 ben_ext_person.g_element_eev_eff_end_date := pay.eev_effective_end_date;
403 ben_ext_person.g_element_entry_id := pay.element_entry_id;
404
405 open c_ele_type(pay.element_type_id) ;
406 fetch c_ele_type into
407 ben_ext_person.g_element_name ,
408 ben_ext_person.g_element_reporting_name,
409 ben_ext_person.g_element_description ,
410 ben_ext_person.g_element_classification_id ,
411 ben_ext_person.g_element_processing_type,
412 ben_ext_person.g_element_input_currency_code ,
413 ben_ext_person.g_element_output_currency_code,
414 ben_ext_person.g_element_skip_rule_id;
415 close c_ele_type ;
416 --
417
418 ben_ext_evaluate_inclusion.Evaluate_Elm_Entry_Incl
419 (p_processing_type => ben_ext_person.g_element_processing_type ,
420 p_input_value_id => pay.input_value_id ,
421 p_business_group_id => p_business_group_id ,
422 p_effective_date => p_effective_date ,
423 p_person_id => p_person_id ,
424 p_element_type_id => pay.element_type_id ,
425 p_source_id => null ,
426 p_source_Type => null ,
427 p_element_entry_id => pay.element_entry_id ,
428 p_include => l_include
429 ) ;
430
431 IF l_include = 'Y' THEN
432 --
433 -- assign enrollment info to global variables
434 --
435
436
437 if ben_ext_person.g_element_classification_id is not null then
438 open c_ele_clas (ben_ext_person.g_element_classification_id) ;
439 fetch c_ele_clas into ben_ext_person.g_element_classification_name ;
440 close c_ele_clas ;
441 end if ;
442
443 if ben_ext_person.g_element_skip_rule_id is not null then
444 open c_ff_name (ben_ext_person.g_element_skip_rule_id) ;
445 fetch c_ff_name into ben_ext_person.g_element_skip_rule ;
446 close c_ff_name ;
447 end if ;
448
449 if ben_ext_person.g_element_entry_costing_id is not null then
450 open c_ele_conc (ben_ext_person.g_element_entry_costing_id) ;
451 fetch c_ele_conc into ben_ext_person.g_element_entry_costing ;
452 close c_ele_conc ;
453 end if ;
454
455 hr_utility.set_location('eeveffdate '||ben_ext_person.g_element_eev_eff_strt_date||
456 '-'||ben_ext_person.g_element_eev_eff_end_date ,99);
457 --
458 -- format and write
459 --
460 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
461 p_ext_file_id => p_ext_file_id,
462 p_data_typ_cd => p_data_typ_cd,
463 p_ext_typ_cd => p_ext_typ_cd,
464 p_rcd_typ_cd => 'D', --detail
465 p_low_lvl_cd => 'Y', --payroll
466 p_person_id => p_person_id,
467 p_chg_evt_cd => p_chg_evt_cd,
468 p_business_group_id => p_business_group_id,
469 p_effective_date => p_effective_date
470 );
471 --
472 end if;
473
474 END LOOP;
475 CLOSE c_pay;
476
477 /* FOR pay IN c_pay LOOP
478 -- initialize the globals - May, 99
479 initialize_globals;
480
481 ben_ext_person.g_element_input_value_name := pay.name;
482 ben_ext_person.g_element_input_value_units := pay.uom;
483 ben_ext_person.g_element_input_value_sequence := pay.display_sequence;
484 ben_ext_person.g_element_input_value_id := pay.input_value_id;
485 ben_ext_person.g_element_id := pay.element_type_id;
486 ben_ext_person.g_element_entry_value := pay.screen_entry_value;
487 ben_ext_person.g_element_entry_value_id := pay.element_entry_value_id;
488 ben_ext_person.g_element_entry_eff_start_date := pay.effective_start_date;
489 ben_ext_person.g_element_entry_eff_end_date := pay.effective_end_date;
490 ben_ext_person.g_element_entry_costing_id := pay.cost_allocation_keyflex_id;
491 ben_ext_person.g_element_entry_reason := pay.reason;
492 ben_ext_person.g_element_eev_eff_strt_date := pay.eev_effective_start_date;
493 ben_ext_person.g_element_eev_eff_end_date := pay.eev_effective_end_date;
494 ben_ext_person.g_element_entry_id := pay.element_entry_id;
495
496
497 open c_ele_type(pay.element_type_id) ;
498 fetch c_ele_type into
499 ben_ext_person.g_element_name ,
500 ben_ext_person.g_element_reporting_name,
501 ben_ext_person.g_element_description ,
502 ben_ext_person.g_element_classification_id ,
503 ben_ext_person.g_element_processing_type,
504 ben_ext_person.g_element_input_currency_code ,
505 ben_ext_person.g_element_output_currency_code,
506 ben_ext_person.g_element_skip_rule_id
507 ;
508 close c_ele_type ;
509
510
511 --
512
513 ben_ext_evaluate_inclusion.Evaluate_Elm_Entry_Incl
514 (p_processing_type => ben_ext_person.g_element_processing_type ,
515 p_input_value_id => pay.input_value_id ,
516 p_business_group_id => p_business_group_id ,
517 p_effective_date => p_effective_date ,
518 p_person_id => p_person_id ,
519 p_element_type_id => pay.element_type_id ,
520 p_source_id => null ,
521 p_source_Type => null ,
522 p_element_entry_id => pay.element_entry_id ,
523 p_include => l_include
524 ) ;
525
526
527 IF l_include = 'Y' THEN
528 --
529 -- assign enrollment info to global variables
530 --
531
532
533 if ben_ext_person.g_element_classification_id is not null then
534 open c_ele_clas (ben_ext_person.g_element_classification_id) ;
535 fetch c_ele_clas into ben_ext_person.g_element_classification_name ;
536 close c_ele_clas ;
537 end if ;
538
539 if ben_ext_person.g_element_skip_rule_id is not null then
540 open c_ff_name (ben_ext_person.g_element_skip_rule_id) ;
541 fetch c_ff_name into ben_ext_person.g_element_skip_rule ;
542 close c_ff_name ;
543 end if ;
544
545 if ben_ext_person.g_element_entry_costing_id is not null then
546 open c_ele_conc (ben_ext_person.g_element_entry_costing_id) ;
547 fetch c_ele_conc into ben_ext_person.g_element_entry_costing ;
548 close c_ele_conc ;
549 end if ;
550
551 hr_utility.set_location('eeveffdate '||ben_ext_person.g_element_eev_eff_strt_date||
552 '-'||ben_ext_person.g_element_eev_eff_end_date ,99);
553 --
554 -- format and write
555 --
556 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
557 p_ext_file_id => p_ext_file_id,
558 p_data_typ_cd => p_data_typ_cd,
559 p_ext_typ_cd => p_ext_typ_cd,
560 p_rcd_typ_cd => 'D', --detail
561 p_low_lvl_cd => 'Y', --payroll
562 p_person_id => p_person_id,
563 p_chg_evt_cd => p_chg_evt_cd,
564 p_business_group_id => p_business_group_id,
565 p_effective_date => p_effective_date
566 );
567 --
568 end if;
569 --
570 END LOOP; */
571 --Changes for Penserver: Stop
572 --
573 hr_utility.set_location('Exiting'||l_proc, 15);
574 --
575 END main;
576
577 END;