[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_RUNRSLT
Source
1 package body BEN_EXT_RUNRSLT as
2 /* $Header: benxrunr.pkb 120.1.12000000.2 2007/02/15 17:13:18 tjesumic noship $
3 +==============================================================================+
4 | Copyright (c) 1997 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 Name: Extract Run Result Process
9 Purpose : This process handles fields that are related to Element Entries and
10 Input Values.
11 History:
12 Date Who Version What?
13 ---- --- ------- -----
14 26 Apr 99 Anusree Sen 115.0 Created
15 14 May 99 Anusree Sen 115.1 Added Header
16 14 May 99 Anusree Sen 115.2 Deleted DBMS_OUTPUT
17 17 May 99 Isen 115.3 Removed null others exception handler
18 - bug 2422
19 25 May 99 Isen 115.4 Added procedure to initialize globals
20 01 Jul 99 Asen 115.5 Added code for decode fields.
21 14 jub 91 tilak 115.6 pay_payroll_actions.effective_Date
22 extracted for payroll end date criteria
23 30 nov 01 tjesumic 115.7 for performance cursor changed bug 2129704
24 14 dec 01 tjesumic 115.8 for performance cursor changed bug 2129704
25 06 feb 02 tjesumic 115.9 comment added ,why the global assignment id is not
26 used to control the assignment table
27 18 nov 02 lakrish 115.10 Bug 2669594,Added assignment for input value name
28 24-Dec-05 tjesumic 115.11 formula added for payroll
29 15-Feb-07 tjesumic 115.12 Hint added for c_asg cursor
30 --------------------------------------------------------------------------------
31 */
32 --
33 g_package varchar2(33) := ' ben_ext_runrslt.'; -- Global package name
34 --
35 -- procedure to initialize the globals - May, 99
36 -- -----------------------------------------------------------------------------
37 -- |----------------------< initialize_globals >-------------------------------------|
38 -- -----------------------------------------------------------------------------
39 PROCEDURE initialize_globals IS
40 --
41 l_proc varchar2(72) := g_package||'initialize_globals';
42 --
43 Begin
44 --
45 --
46 hr_utility.set_location('Entering'||l_proc, 5);
47 --
48 --
49 ben_ext_person.g_runrslt_element_name := null;
50 ben_ext_person.g_runrslt_element_id := null;
51 ben_ext_person.g_runrslt_reporting_name := null;
52 ben_ext_person.g_runrslt_element_description := null;
53 ben_ext_person.g_runrslt_classification := null;
54 ben_ext_person.g_runrslt_classification_id := null;
55 ben_ext_person.g_runrslt_processing_type := null;
56 ben_ext_person.g_runrslt_input_currency := null;
57 ben_ext_person.g_runrslt_output_currency := null;
58 ben_ext_person.g_runrslt_skip_rule := null;
59 ben_ext_person.g_runrslt_skip_rule_id := null;
60 ben_ext_person.g_runrslt_input_value_name := null;
61 ben_ext_person.g_runrslt_input_value_id := null;
62 ben_ext_person.g_runrslt_input_value_units := null;
63 ben_ext_person.g_runrslt_input_value_sequence := null;
64 ben_ext_person.g_runrslt_identifier := null;
65 ben_ext_person.g_runrslt_jurisdiction_code := null;
66 ben_ext_person.g_runrslt_status := null;
67 ben_ext_person.g_runrslt_source_type := null;
68 ben_ext_person.g_runrslt_entry_type := null;
69 ben_ext_person.g_runrslt_value := null;
70 ben_ext_person.g_runrslt_last_pay_date := null;
71 --
72 --
73 hr_utility.set_location('Exiting'||l_proc, 15);
74 --
75 --
76 End initialize_globals;
77 --
78 --
79 PROCEDURE main
80 ( p_person_id in number,
81 p_ext_rslt_id in number,
82 p_ext_file_id in number,
83 p_data_typ_cd in varchar2,
84 p_ext_typ_cd in varchar2,
85 p_chg_evt_cd in varchar2,
86 p_business_group_id in number,
87 p_effective_date in date) is
88 --
89 l_proc varchar2(72) := g_package||'main';
90 --
91 l_include varchar2(1) := 'Y';
92 l_dummy_no number ;
93 --
94 ---when the criteris is exclude theen use this cursor
95 --- global assignment id is not validated intentionally
96 --- if needed this can be added latter
97 -- if a person hired in jan , terminated in mar and rehired in
98 -- sep .
99 -- if the assg_id control allded then it is not possible for the person
100 -- to extract the salary of the whole year. inorder have the history
101 -- of the person runresult the global assg id is not controlled here
102
103 cursor c_runrslt_excl is
104 select
105 et.element_name element_name,
106 et.element_type_id element_id,
107 et.reporting_name reporting_name,
108 et.description description,
109 et.classification_id class_id,
110 ec.classification_name class_name,
111 et.processing_type process_type,
112 et.input_currency_code input_currency,
113 et.output_currency_code output_currency,
114 et.formula_id skip_rule_id, -- skip rule
115 ff.formula_name skip_rule_name,
116 iv.name value_name,
117 iv.uom value_unit,
118 iv.display_sequence value_seq,
119 iv.input_value_id value_id,
120 rr.run_result_id result_id,
121 rr.jurisdiction_code juris_code,
122 rr.status status,
123 rr.source_type source_type,
124 rr.source_id source_id,
125 rr.entry_type entry_type,
126 rv.result_value result_value,
127 ppa.effective_Date effective_Date
128 from
129 per_all_assignments_f asg,
130 pay_assignment_actions aac,
131 pay_input_values_f iv,
132 pay_element_types_f et,
133 pay_element_classifications ec,
134 ff_formulas_f ff,
135 pay_run_results rr,
136 pay_run_result_values rv,
137 pay_payroll_actions ppa
138 where
139 asg.person_id = p_person_id
140 --and asg.assignment_id = ben_ext_person.g_assignment_id --1969853
141 and asg.assignment_id = aac.assignment_id
142 and aac.assignment_action_id = rr.assignment_action_id
143 and iv.input_value_id = nvl(ben_ext_person.g_chg_input_value_id,iv.input_value_id)
144 and iv.element_type_id = et.element_type_id
145 and aac.payroll_action_id = ppa.payroll_action_id
146 and rr.element_type_id = et.element_type_id
147 and rr.run_result_id = rv.run_result_id
148 and rv.input_value_id = iv.input_value_id
149 and et.classification_id = ec.classification_id (+)
150 and et.formula_id = ff.formula_id (+)
151 and p_effective_date between nvl(iv.effective_start_date,p_effective_date)
152 and nvl(iv.effective_end_date ,p_effective_date)
153 and p_effective_date between nvl(et.effective_start_date,p_effective_date)
154 and nvl(et.effective_end_date ,p_effective_date)
155 and p_effective_date between nvl(asg.effective_start_date,p_effective_date)
156 and nvl(asg.effective_end_date ,p_effective_date)
157 and p_effective_date between nvl(ff.effective_start_date,p_effective_date)
158 and nvl(ff.effective_end_date,p_effective_date)
159 and rv.result_value is not null;
160
161 cursor c_asg
162 (p_start_date date , p_end_date date) is
163 select /*+ ordered */ aac.assignment_action_id ,
164 ppa.effective_Date effective_Date
165 from per_all_assignments_f asg,
166 pay_assignment_actions aac,
167 pay_payroll_actions ppa
168 where asg.person_id = p_person_id
169 --and asg.assignment_id = ben_ext_person.g_assignment_id --1969853
170 and asg.assignment_id = aac.assignment_id
171 and aac.payroll_action_id = ppa.payroll_action_id
172 and p_effective_date between asg.effective_start_date and asg.effective_end_date
173 and (p_start_date is null or (ppa.effective_date between
174 p_start_date and p_end_date ) ) ;
175
176 cursor c_rslt
177 (c_assignment_action_id number) is
178 select iv.name value_name,
179 iv.uom value_unit,
180 iv.display_sequence value_seq,
181 iv.input_value_id value_id,
182 rr.run_result_id result_id,
183 rr.jurisdiction_code juris_code,
184 rr.status status,
185 rr.source_type source_type,
186 rr.source_id source_id,
187 rr.entry_type entry_type,
188 rv.result_value result_value,
189 iv.element_type_id
190 from pay_run_results rr,
191 pay_input_values_f iv,
192 pay_run_result_values rv
193 where rr.assignment_action_id = c_assignment_action_id
194 and rr.element_type_id = iv.element_type_id
195 and (ben_ext_person.g_chg_input_value_id is null
196 or (iv.input_value_id = ben_ext_person.g_chg_input_value_id))
197 and rr.run_result_id = rv.run_result_id
198 and rv.input_value_id = iv.input_value_id
199 and rv.result_value is not null
200 and p_effective_date between iv.effective_start_date
201 and iv.effective_end_date ;
202
203
204 cursor c_rslt_p
205 (c_assignment_action_id number,
206 c_input_value_id number ,
207 c_element_type_id number) is
208 select iv.name value_name,
209 iv.uom value_unit,
210 iv.display_sequence value_seq,
211 iv.input_value_id value_id,
212 rr.run_result_id result_id,
213 rr.jurisdiction_code juris_code,
214 rr.status status,
215 rr.source_type source_type,
216 rr.source_id source_id,
217 rr.entry_type entry_type,
218 rv.result_value result_value,
219 iv.element_type_id
220 from pay_run_results rr,
221 pay_input_values_f iv,
222 pay_run_result_values rv
223 where rr.assignment_action_id = c_assignment_action_id
224 and rr.element_type_id = iv.element_type_id
225 and (ben_ext_person.g_chg_input_value_id is null
226 or (iv.input_value_id = ben_ext_person.g_chg_input_value_id))
227 and iv.input_value_id = c_input_value_id
228 and iv.element_type_id = c_element_type_id
229 and rr.run_result_id = rv.run_result_id
230 and rv.input_value_id = iv.input_value_id
231 and rv.result_value is not null
232 and p_effective_date between iv.effective_start_date
233 and iv.effective_end_date ;
234
235 cursor c_ele ( c_element_type_id number ) is
236 select et.element_name element_name,
237 et.element_type_id element_id,
238 et.reporting_name reporting_name,
239 et.description description,
240 et.classification_id class_id,
241 ec.classification_name class_name,
242 et.processing_type process_type,
243 et.input_currency_code input_currency,
244 et.output_currency_code output_currency,
245 et.formula_id skip_rule_id -- skip rule
246 from pay_element_types_f et,
247 pay_element_classifications ec
248 where et.element_type_id = c_element_type_id
249 and et.classification_id = ec.classification_id
250 and p_effective_date between nvl(et.effective_start_date,p_effective_date)
251 and nvl(et.effective_end_date ,p_effective_date);
252
253
254 cursor c_ff (c_formula_id number) is
255 select ff.formula_name skip_rule_name
256 from ff_formulas_f ff
257 where ff.formula_id = c_formula_id
258 and p_effective_date between ff.effective_start_date
259 and ff.effective_end_date ;
260
261
262 BEGIN
263 --
264 hr_utility.set_location('Entering'||l_proc, 5);
265 hr_utility.set_location('input_excld_flag ' || ben_ext_evaluate_inclusion.g_ele_input_excld_flag , 199 );
266 hr_utility.set_location('lastDate_excldflag'||ben_ext_evaluate_inclusion.g_payroll_last_Date_excld_flag, 199) ;
267
268 -- when the criteris is exclded
269
270 if ben_ext_evaluate_inclusion.g_ele_input_excld_flag = 'Y'
271 or ben_ext_evaluate_inclusion.g_payroll_last_Date_excld_flag = 'Y'
272 then
273 FOR runrslt IN c_runrslt_excl LOOP
274 --
275 -- initialize the globals - May, 99
276 initialize_globals;
277 --
278
279 ben_ext_evaluate_inclusion.Evaluate_Elm_Entry_Incl
280 (p_processing_type => runrslt.process_type ,
281 p_input_value_id => runrslt.value_id ,
282 p_business_group_id => p_business_group_id ,
283 p_pay_period_date => runrslt.effective_date ,
284 p_effective_date => p_effective_date ,
285 p_person_id => p_person_id ,
286 p_element_type_id => runrslt.element_id ,
287 p_source_id => runrslt.source_id,
288 p_source_Type => runrslt.source_type,
289 p_include => l_include
290 ) ;
291
292
293 IF l_include = 'Y' THEN
294 --
295 -- assign run result elemts info to global variables
296 --
297 ben_ext_person.g_runrslt_element_name := runrslt.element_name;
298 ben_ext_person.g_runrslt_element_id := runrslt.element_id;
299 ben_ext_person.g_runrslt_reporting_name := runrslt.reporting_name;
300 ben_ext_person.g_runrslt_element_description := runrslt.description;
301 ben_ext_person.g_runrslt_classification := runrslt.class_name;
302 ben_ext_person.g_runrslt_classification_id := runrslt.class_id;
303 ben_ext_person.g_runrslt_processing_type := runrslt.process_type;
304 ben_ext_person.g_runrslt_input_currency := runrslt.input_currency;
305 ben_ext_person.g_runrslt_output_currency := runrslt.output_currency;
306 ben_ext_person.g_runrslt_skip_rule := runrslt.skip_rule_name;
307 ben_ext_person.g_runrslt_skip_rule_id := runrslt.skip_rule_id;
308 ben_ext_person.g_runrslt_input_value_name := runrslt.value_name;
309 ben_ext_person.g_runrslt_input_value_id := runrslt.value_id;
310 ben_ext_person.g_runrslt_input_value_units := runrslt.value_unit;
311 ben_ext_person.g_runrslt_input_value_sequence := runrslt.value_seq;
312 ben_ext_person.g_runrslt_identifier := runrslt.result_id;
313 ben_ext_person.g_runrslt_jurisdiction_code := runrslt.juris_code;
314 ben_ext_person.g_runrslt_status := runrslt.status;
315 ben_ext_person.g_runrslt_source_type := runrslt.source_type;
316 ben_ext_person.g_runrslt_entry_type := runrslt.entry_type;
317 ben_ext_person.g_runrslt_value := runrslt.result_value;
318 ben_ext_person.g_runrslt_last_pay_date := runrslt.effective_date;
319
320 --
321 -- format and write
322 --
323 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
324 p_ext_file_id => p_ext_file_id,
325 p_data_typ_cd => p_data_typ_cd,
326 p_ext_typ_cd => p_ext_typ_cd,
327 p_rcd_typ_cd => 'D', --detail
328 p_low_lvl_cd => 'R', --payroll
329 p_person_id => p_person_id,
330 p_chg_evt_cd => p_chg_evt_cd,
331 p_business_group_id => p_business_group_id,
332 p_effective_date => p_effective_date
333 );
334 --
335 end if;
336 --
337 END LOOP;
338 --
339 else
340 initialize_globals;
341 ---intialise after writing rec
342 for r_asg in c_asg(ben_ext_person.g_pay_last_start_date
343 ,ben_ext_person.g_pay_last_end_date )
344 loop
345
346 --- the loop will be bracned in to two
347 --- 1 with the input_valu_id from criteria
348 --- 2 no criteria for input_value_id
349 if ben_ext_evaluate_inclusion.g_ele_input_list.count > 0 then
350 l_dummy_no := ben_ext_evaluate_inclusion.g_ele_input_list.count ;
351 for i in 1 .. l_dummy_no
352 loop
353 for r_rslt in c_rslt_p (r_asg.assignment_action_id,
354 ben_ext_evaluate_inclusion.g_ele_input_list(i),
355 ben_ext_evaluate_inclusion.g_ele_type_list(i))
356 Loop
357
358
359
360 for r_ele in c_ele ( r_rslt.element_type_id )
361 Loop
362
363 -- if rule define call for evaluation
364 l_include := 'Y' ;
365 if ben_ext_evaluate_inclusion.g_payroll_rl_incl_rqd = 'Y' then
366
367 ben_ext_evaluate_inclusion.Evaluate_Elm_Entry_Incl
368 (p_processing_type => r_ele.process_type ,
369 p_input_value_id => r_rslt.value_id ,
370 p_business_group_id => p_business_group_id ,
371 p_pay_period_date => r_asg.effective_date ,
372 p_effective_date => p_effective_date ,
373 p_person_id => p_person_id ,
374 p_element_type_id => r_ele.element_id ,
375 p_source_id => r_rslt.source_id,
376 p_source_Type => r_rslt.source_type,
377 p_include => l_include
378 ) ;
379 end if ;
380
381 IF l_include = 'Y' THEN
382
383 ben_ext_person.g_runrslt_last_pay_date := r_asg.effective_date;
384 ben_ext_person.g_runrslt_input_value_units := r_rslt.value_unit;
385 ben_ext_person.g_runrslt_input_value_sequence := r_rslt.value_seq;
386 ben_ext_person.g_runrslt_identifier := r_rslt.result_id;
387 ben_ext_person.g_runrslt_input_value_id := r_rslt.value_id;
388 ben_ext_person.g_runrslt_input_value_name := r_rslt.value_name; --Bug 2669594
389 ben_ext_person.g_runrslt_jurisdiction_code := r_rslt.juris_code;
390 ben_ext_person.g_runrslt_status := r_rslt.status;
391 ben_ext_person.g_runrslt_source_type := r_rslt.source_type;
392 ben_ext_person.g_runrslt_entry_type := r_rslt.entry_type;
393 ben_ext_person.g_runrslt_value := r_rslt.result_value;
394 ben_ext_person.g_runrslt_element_name := r_ele.element_name;
395 ben_ext_person.g_runrslt_element_id := r_ele.element_id;
396 ben_ext_person.g_runrslt_reporting_name := r_ele.reporting_name;
397 ben_ext_person.g_runrslt_element_description := r_ele.description;
398 ben_ext_person.g_runrslt_classification := r_ele.class_name;
399 ben_ext_person.g_runrslt_classification_id := r_ele.class_id;
400 ben_ext_person.g_runrslt_processing_type := r_ele.process_type;
401 ben_ext_person.g_runrslt_input_currency := r_ele.input_currency;
402 ben_ext_person.g_runrslt_output_currency := r_ele.output_currency;
403 ben_ext_person.g_runrslt_skip_rule_id := r_ele.skip_rule_id;
404 if r_ele.skip_rule_id is not null then
405 open c_ff (r_ele.skip_rule_id ) ;
406 fetch c_ff into ben_ext_person.g_runrslt_skip_rule ;
407 if c_ff%notfound then
408 ben_ext_person.g_runrslt_skip_rule := null ;
409 end if ;
410 close c_ff ;
411 end if;
412 -- format and write
413 --
414 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
415 p_ext_file_id => p_ext_file_id,
416 p_data_typ_cd => p_data_typ_cd,
417 p_ext_typ_cd => p_ext_typ_cd,
418 p_rcd_typ_cd => 'D', --detail
419 p_low_lvl_cd => 'R', --payroll
420 p_person_id => p_person_id,
421 p_chg_evt_cd => p_chg_evt_cd,
422 p_business_group_id => p_business_group_id,
423 p_effective_date => p_effective_date
424 );
425 end if ;
426 initialize_globals;
427
428 end loop ;
429 end loop ;
430
431 end loop ;
432
433 Else -- nor input value criteria
434
435 for r_rslt in c_rslt (r_asg.assignment_action_id)
436 Loop
437 for r_ele in c_ele ( r_rslt.element_type_id )
438 Loop
439 -- if rule define call for evaluation
440 l_include := 'Y' ;
441 if ben_ext_evaluate_inclusion.g_payroll_rl_incl_rqd = 'Y' then
442
443 ben_ext_evaluate_inclusion.Evaluate_Elm_Entry_Incl
444 (p_processing_type => r_ele.process_type ,
445 p_input_value_id => r_rslt.value_id ,
446 p_business_group_id => p_business_group_id ,
447 p_pay_period_date => r_asg.effective_date ,
448 p_effective_date => p_effective_date ,
449 p_person_id => p_person_id ,
450 p_element_type_id => r_ele.element_id ,
451 p_source_id => r_rslt.source_id,
452 p_source_Type => r_rslt.source_type,
453 p_include => l_include
454 );
455 end if ;
456
457 IF l_include = 'Y' THEN
458
459
460 ben_ext_person.g_runrslt_last_pay_date := r_asg.effective_date;
461 ben_ext_person.g_runrslt_input_value_units := r_rslt.value_unit;
462 ben_ext_person.g_runrslt_input_value_sequence := r_rslt.value_seq;
463 ben_ext_person.g_runrslt_identifier := r_rslt.result_id;
464 ben_ext_person.g_runrslt_input_value_id := r_rslt.value_id;
465 ben_ext_person.g_runrslt_input_value_name := r_rslt.value_name; -- Bug 2669594
466 ben_ext_person.g_runrslt_jurisdiction_code := r_rslt.juris_code;
467 ben_ext_person.g_runrslt_status := r_rslt.status;
468 ben_ext_person.g_runrslt_source_type := r_rslt.source_type;
469 ben_ext_person.g_runrslt_entry_type := r_rslt.entry_type;
470 ben_ext_person.g_runrslt_value := r_rslt.result_value;
471 ben_ext_person.g_runrslt_element_name := r_ele.element_name;
472 ben_ext_person.g_runrslt_element_id := r_ele.element_id;
473 ben_ext_person.g_runrslt_reporting_name := r_ele.reporting_name;
474 ben_ext_person.g_runrslt_element_description := r_ele.description;
475 ben_ext_person.g_runrslt_classification := r_ele.class_name;
476 ben_ext_person.g_runrslt_classification_id := r_ele.class_id;
477 ben_ext_person.g_runrslt_processing_type := r_ele.process_type;
478 ben_ext_person.g_runrslt_input_currency := r_ele.input_currency;
479 ben_ext_person.g_runrslt_output_currency := r_ele.output_currency;
480 ben_ext_person.g_runrslt_skip_rule_id := r_ele.skip_rule_id;
481 if r_ele.skip_rule_id is not null then
482 open c_ff (r_ele.skip_rule_id ) ;
483 fetch c_ff into ben_ext_person.g_runrslt_skip_rule ;
484 if c_ff%notfound then
485 ben_ext_person.g_runrslt_skip_rule := null ;
486 end if ;
487 close c_ff ;
488 end if;
489 -- format and write
490 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
491 p_ext_file_id => p_ext_file_id,
492 p_data_typ_cd => p_data_typ_cd,
493 p_ext_typ_cd => p_ext_typ_cd,
494 p_rcd_typ_cd => 'D', --detail
495 p_low_lvl_cd => 'R', --payroll
496 p_person_id => p_person_id,
497 p_chg_evt_cd => p_chg_evt_cd,
498 p_business_group_id => p_business_group_id,
499 p_effective_date => p_effective_date
500 );
501 end if ;
502 initialize_globals;
503
504 end loop ;
505
506 end loop ;
507
508 end if; -- eof input value criteria
509 end loop ; ---- asg loop
510 end if ; -- exclide flag
511 hr_utility.set_location('Exiting'||l_proc, 15);
512 --
513 END main;
514
515 END;