[Home] [Help]
PACKAGE BODY: APPS.PAY_RUN_RESULT_PKG
Source
1 package body pay_run_result_pkg as
2 /* $Header: pycorrrp.pkb 120.4.12010000.1 2008/07/27 22:23:27 appldev ship $ */
3 --
4 /*
5 Name
6 get_result_value
7 Description
8
9 This function is used to retrieve the run result value in
10 a sparse matrix solution.
11 */
12 function get_result_value(p_run_result_id in number,
13 p_input_value_id in number,
14 p_iv_name in varchar2,
15 p_jurisdiction_code in varchar2,
16 p_business_group_id in number
17 ) return varchar2
18 is
19 --
20 l_result_value pay_run_result_values.result_value%type;
21 l_legislation_code per_business_groups.legislation_code%type;
22 l_inp_val_name pay_input_values_f.name%type;
23 l_found boolean;
24 --
25 begin
26 begin
27 --
28 select result_value
29 into l_result_value
30 from pay_run_result_values
31 where run_result_id = p_run_result_id
32 and input_value_id = p_input_value_id;
33 --
34 return l_result_value;
35 --
36 exception
37 --
38 when no_data_found then
39 --
40 /* No Data Found then the value is either null or we
41 need to return the jurisdiction code
42 */
43 if (p_jurisdiction_code is null) then
44 return null;
45 else
46 --
47 select legislation_code
48 into l_legislation_code
49 from per_business_groups
50 where business_group_id = p_business_group_id;
51 --
52 pay_core_utils.get_leg_context_iv_name('JURISDICTION_CODE',
53 l_legislation_code,
54 l_inp_val_name,
55 l_found
56 );
57 --
58 if (l_found = FALSE) then
59 l_inp_val_name := 'Jurisdiction';
60 end if;
61 --
62 if (l_inp_val_name = p_iv_name) then
63 return p_jurisdiction_code;
64 else
65 return null;
66 end if;
67 --
68 end if;
69 --
70 end;
71 end get_result_value;
72 --
73 /*
74 Name
75 create_run_result
76 Description
77
78 This procedure creates the run result for an element entry.
79 */
80 procedure create_run_result(p_element_entry_id in number,
81 p_session_date in date,
82 p_business_group_id in number,
83 p_jc_name in varchar2,
84 p_rr_sparse in boolean,
85 p_rr_sparse_jc in boolean,
86 p_asg_action_id in number default null,
87 p_run_result_id out nocopy number
88 )
89 is
90 --
91 cursor get_b_eevs(p_element_entry_id number,
92 p_session_date date ) is
93 select peev.input_value_id,
94 piv.uom,
95 peev.screen_entry_value value,
96 peev.element_entry_value_id
97 from pay_input_values_f piv,
98 pay_element_entry_values_f peev
99 where peev.element_entry_id = p_element_entry_id
100 and piv.input_value_id = peev.input_value_id
101 and p_session_date between peev.effective_start_date
102 and peev.effective_end_date
103 and p_session_date between piv.effective_start_date
104 and piv.effective_end_date;
105 --
106 l_jurisdiction_code pay_run_results.jurisdiction_code%type;
107 l_assignment_id per_assignments_f.assignment_id%type;
108 l_element_type_id pay_element_types_f.element_type_id%type;
109 l_entry_type pay_element_entries_f.entry_type%type;
110 l_input_currency_code pay_element_types_f.input_currency_code%type;
111 l_output_currency_code pay_element_types_f.output_currency_code%type;
112 l_run_result_id pay_run_results.run_result_id%type;
113 l_amount pay_run_result_values.result_value%type;
114 l_rr_status pay_run_results.status%type;
115 l_jurisdiction_eev_id pay_element_entry_values_f.element_entry_value_id%type;
116 l_original_entry_id number;
117 l_rr_source_id number;
118 l_currency_type varchar2(30);
119 l_rr_sparse varchar2(10);
120 l_local_unit_id number;
121 save_value boolean;
122 l_time_definition_type pay_element_types_f.time_definition_type%type;
123 l_time_definition_id pay_run_results.time_definition_id%type;
124 l_s_time_definition_id pay_run_results.time_definition_id%type;
125 l_time_def_start_date per_time_periods.start_date%type;
126 l_time_def_end_date per_time_periods.end_date%type;
127 begin
128 --
129 /* Make sure the entry exists first
130 */
131 begin
132 --
133 select ee.assignment_id,
134 et.element_type_id,
135 ee.entry_type,
136 ee.original_entry_id,
137 et.input_currency_code,
138 et.output_currency_code,
139 pay_run_results_s.nextval,
140 hr_dynsql.get_local_unit(ee.assignment_id,
141 p_session_date),
142 et.time_definition_type,
143 et.time_definition_id
144 into l_assignment_id,
145 l_element_type_id,
146 l_entry_type,
147 l_original_entry_id,
148 l_input_currency_code,
149 l_output_currency_code,
150 l_run_result_id,
151 l_local_unit_id,
152 l_time_definition_type,
153 l_s_time_definition_id
154 from pay_element_entries_f ee,
155 pay_element_types_f et
156 where ee.element_entry_id = p_element_entry_id
157 and et.element_type_id = ee.element_type_id
158 and p_session_date between ee.effective_start_date
159 and ee.effective_end_date
160 and p_session_date between et.effective_start_date
161 and et.effective_end_date;
162 --
163 exception
164 when NO_DATA_FOUND then
165 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
166 hr_utility.set_message_token('PROCEDURE',
167 'pay_run_result_pkg.create_run_result');
168 hr_utility.set_message_token('STEP','1');
169 hr_utility.raise_error;
170 end;
171 --
172 if (p_asg_action_id is null) then
173 l_rr_status := 'U';
174 else
175 l_rr_status := 'P';
176 end if;
177
178 -- First get the Jurisdiction if one exists.
179 begin
180 select eev.screen_entry_value,
181 eev.element_entry_value_id
182 into l_jurisdiction_code,
183 l_jurisdiction_eev_id
184 from pay_element_entry_values_f eev,
185 pay_input_values_f piv,
186 pay_element_entries_f pee
187 where pee.element_entry_id = p_element_entry_id
188 and eev.element_entry_id = pee.element_entry_id
189 and eev.input_value_id = piv.input_value_id
190 and piv.name = p_jc_name
191 and p_session_date between pee.effective_start_date
192 and pee.effective_end_date
193 and p_session_date between eev.effective_start_date
194 and eev.effective_end_date
195 and p_session_date between piv.effective_start_date
196 and piv.effective_end_date;
197 exception
198 when no_data_found then
199 l_jurisdiction_code := null;
200 l_jurisdiction_eev_id := -1;
201 end;
202
203 --
204 -- Set the run result source_id.
205 --
206 l_rr_source_id := p_element_entry_id;
207 --
208 -- #3482270. original entry support for adjustments.
209 --
210 if l_entry_type = 'B' then
211 l_rr_source_id := nvl(l_original_entry_id, p_element_entry_id);
212 end if;
213
214 --
215 -- #4482023. Time Definition has to be stamped on run results
216 -- for Balance Adjustment and Balance Initializations.
217 --
218
219 if l_entry_type = 'B' then
220
221 if l_time_definition_type = 'G' then
222
223 pay_core_utils.get_time_definition
224 ( p_element_entry => p_element_entry_id,
225 p_asg_act_id => p_asg_action_id,
226 p_time_def_id => l_time_definition_id );
227
228 elsif l_time_definition_type = 'S' then
229
230 l_time_definition_id := l_s_time_definition_id;
231
232 end if;
233
234 if l_time_definition_id is not null then
235
236 --
237 -- #5066120. Set start and end dates to effective date of
238 -- the Balance Adjustment / Balance Initialization.
239 l_time_def_start_date := p_session_date;
240 l_time_def_end_date := p_session_date;
241
242 end if;
243
244 end if;
245
246
247 begin
248 --
249 insert into pay_run_results
250 (run_result_id,
251 element_type_id,
252 assignment_action_id,
253 entry_type,
254 source_id,
255 source_type,
256 status,
257 jurisdiction_code,
258 element_entry_id,
259 local_unit_id,
260 time_definition_id,
261 start_date,
262 end_date)
263 values
264 (l_run_result_id,
265 l_element_type_id,
266 p_asg_action_id,
267 l_entry_type,
268 l_rr_source_id,
269 'E',
270 l_rr_status,
271 l_jurisdiction_code,
272 p_element_entry_id,
273 l_local_unit_id,
274 l_time_definition_id,
275 l_time_def_start_date,
276 l_time_def_end_date);
277 --
278 exception
279 when NO_DATA_FOUND then
280 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
281 hr_utility.set_message_token('PROCEDURE',
282 'pay_run_result_pkg.create_run_result');
283 hr_utility.set_message_token('STEP','2');
284 hr_utility.raise_error;
285 end;
286
287 --
288 if (l_entry_type = 'B' and
289 l_input_currency_code <> l_output_currency_code) then
290 --
291 -- insert run results values converting all money uom's to the output
292 -- currency value.
293 --
294 l_currency_type:=hr_currency_pkg.get_rate_type
295 (p_business_group_id,
296 p_session_date,
297 'P');
298 if (l_currency_type is NULL)
299 then
300 hr_utility.set_message(801,'HR_52349_NO_RATE_TYPE');
301 hr_utility.raise_error;
302 end if;
303 end if;
304 --
305 begin
306
307 for peev in get_b_eevs(p_element_entry_id, p_session_date) loop
308
309 if (peev.uom='M'
310 and (l_entry_type = 'B' and
311 l_input_currency_code <> l_output_currency_code))
312 then
313 begin
314 l_amount:=fnd_number.number_to_canonical(
315 hr_currency_pkg.convert_amount(l_input_currency_code,
316 l_output_currency_code,
317 p_session_date,
318 peev.value,
319 l_currency_type));
320 exception
321 when gl_currency_api.NO_RATE then
322 hr_utility.set_message(801,'HR_6405_PAYM_NO_EXCHANGE_RATE');
323 hr_utility.set_message_token('RATE1', l_input_currency_code);
324 hr_utility.set_message_token('RATE2', l_output_currency_code);
325 hr_utility.raise_error;
326 when gl_currency_api.INVALID_CURRENCY then
327 hr_utility.set_message(801,'HR_52350_INVALID_CURRENCY');
328 hr_utility.set_message_token('RATE1', l_input_currency_code);
329 hr_utility.set_message_token('RATE2', l_output_currency_code);
330 hr_utility.raise_error;
331 end;
332 else
333 l_amount:=peev.value;
334 end if;
335
336 /* Work out whether we need to create
337 the result
338 */
339 save_value := TRUE;
340 if ( p_rr_sparse = TRUE) then
341 if (l_jurisdiction_eev_id = peev.element_entry_value_id) then
342 if (p_rr_sparse_jc = TRUE) then
343 save_value := FALSE;
344 end if;
345 else
346 if (l_amount is null) then
347 save_value := FALSE;
348 end if;
349 end if;
350 end if;
351 --
352 if (save_value = TRUE) then
353 --
354 insert into pay_run_result_values
355 (input_value_id,
356 run_result_id,
357 result_value,
358 formula_result_flag)
359 values
360 (peev.input_value_id,
361 l_run_result_id,
362 l_amount,
363 'N');
364 --
365 end if;
366 end loop;
367 --
368 exception
369 when no_data_found then
370 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
371 hr_utility.set_message_token('PROCEDURE',
372 'pay_run_result_pkg.create_run_result');
373 hr_utility.set_message_token('STEP','3');
374 hr_utility.raise_error;
375 end;
376
377 --
378 p_run_result_id := l_run_result_id;
379 --
380 end create_run_result;
381 --
382 /*
383 Name
384 create_run_result
385 Description
386
387 This procedure creates the run result for an element type.
388 */
389 procedure create_indirect_rr(p_element_type_id in number,
390 p_run_result_id in number,
391 p_session_date in date,
392 p_business_group_id in number,
393 p_jc_name in varchar2,
394 p_rr_sparse in boolean,
395 p_rr_sparse_jc in boolean,
396 p_asg_action_id in number default null,
397 p_ind_run_result_id out nocopy number
398 )
399 is
400 --
401 cursor get_iv (p_et_id number,
402 p_effdate date)
403 is
404 select piv.input_value_id,
405 piv.name
406 from pay_input_values_f piv
407 where piv.element_type_id = p_et_id
408 and p_effdate between piv.effective_start_date
409 and piv.effective_end_date;
410 --
411 l_entry_type pay_element_entries_f.entry_type%type;
412 l_run_result_id pay_run_results.run_result_id%type;
413 l_entry_id pay_run_results.source_id%type;
414 l_rr_status pay_run_results.status%type;
415 l_local_unit_id number;
416 save_value boolean;
417 l_time_definition_id pay_run_results.time_definition_id%type;
418 l_start_date pay_run_results.start_date%type;
419 l_end_date pay_run_results.end_date%type;
420 --
421 begin
422 begin
423 --
424 select prr.entry_type,
425 prr.source_id,
426 pay_run_results_s.nextval,
427 local_unit_id,
428 time_definition_id,
429 start_date,
430 end_date
431 into l_entry_type,
432 l_entry_id,
433 l_run_result_id,
434 l_local_unit_id,
435 l_time_definition_id,
436 l_start_date,
437 l_end_date
438 from pay_run_results prr
439 where prr.run_result_id = p_run_result_id;
440 --
441 exception
442 when NO_DATA_FOUND then
443 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
444 hr_utility.set_message_token('PROCEDURE',
445 'pay_run_result_pkg.create_indirect_rr');
446 hr_utility.set_message_token('STEP','1');
447 hr_utility.raise_error;
448 end;
449 --
450 if (p_asg_action_id is null) then
451 l_rr_status := 'U';
452 else
453 l_rr_status := 'P';
454 end if;
455 --
456 --
457 -- #4482023. Time Definition has to be stamped on indirect run results
458 -- when the parent results has the time definition.
459 --
460
461 insert into pay_run_results
462 (run_result_id,
463 element_type_id,
464 assignment_action_id,
465 entry_type,
466 source_id,
467 source_type,
468 status,
469 jurisdiction_code,
470 element_entry_id,
471 local_unit_id,
472 time_definition_id,
473 start_date,
474 end_date)
475 values
476 (l_run_result_id,
477 p_element_type_id,
478 p_asg_action_id,
479 l_entry_type,
480 l_entry_id,
481 'I',
482 l_rr_status,
483 null,
484 null,
485 l_local_unit_id,
486 l_time_definition_id,
487 l_start_date,
488 l_end_date);
489 --
490 for ivrec in get_iv(p_element_type_id,
491 p_session_date) loop
492 --
493 /* Work out whether we need to create
494 the result
495 */
496 save_value := TRUE;
497 if ( p_rr_sparse = TRUE) then
498 if (p_jc_name = ivrec.name) then
499 if (p_rr_sparse_jc = TRUE) then
500 save_value := FALSE;
501 end if;
502 else
503 save_value := FALSE;
504 end if;
505 end if;
506 --
507 if (save_value = TRUE) then
508 --
509 insert into pay_run_result_values
510 (input_value_id,
511 run_result_id,
512 result_value,
513 formula_result_flag)
514 values
515 (ivrec.input_value_id,
516 l_run_result_id,
517 null,
518 'N');
519 end if;
520 --
521 end loop;
522 --
523 p_ind_run_result_id := l_run_result_id;
524 --
525 end create_indirect_rr;
526 --
527 /*
528 Name
529 maintain_rr_value
530 Description
531
532 This procedure creates the run result value.
533 */
534 procedure maintain_rr_value(p_run_result_id in number,
535 p_session_date in date,
536 p_input_value_id in number,
537 p_value in varchar2,
538 p_formula_result_flag in varchar2,
539 p_jc_name in varchar2,
540 p_rr_sparse in boolean,
541 p_rr_sparse_jc in boolean,
542 p_mode in varchar2
543 )
544 is
545 --
546 l_iv_name pay_input_values_f.name%type;
547 save_value boolean;
548 --
549 begin
550 --
551 select name
552 into l_iv_name
553 from pay_input_values_f
554 where input_value_id = p_input_value_id
555 and p_session_date between effective_start_date
556 and effective_end_date;
557 --
558 /* Set the jurisdiction value on the RR if needed */
559 if (l_iv_name = p_jc_name) then
560 --
561 update pay_run_results
562 set jurisdiction_code = p_value
563 where run_result_id = p_run_result_id;
564 --
565 end if;
566 --
567 /* Work out whether we need to create
568 the result
569 */
570 save_value := TRUE;
571 if ( p_rr_sparse = TRUE) then
572 if (l_iv_name = p_jc_name) then
573 if (p_rr_sparse_jc = TRUE) then
574 save_value := FALSE;
575 end if;
576 else
577 if (p_value is null) then
578 save_value := FALSE;
579 end if;
580 end if;
581 end if;
582 --
583 if (save_value = TRUE) then
584 --
585 declare
586 l_dummy number;
587 begin
588 --
589 select 1
590 into l_dummy
591 from pay_run_result_values
592 where run_result_id = p_run_result_id
593 and input_value_id = p_input_value_id;
594 --
595 update pay_run_result_values
596 set result_value = p_value,
597 formula_result_flag = p_formula_result_flag
598 where run_result_id = p_run_result_id
599 and input_value_id = p_input_value_id;
600 --
601 exception
602 when no_data_found then
603 --
604 insert into pay_run_result_values
605 (input_value_id,
606 run_result_id,
607 result_value,
608 formula_result_flag)
609 values
610 (p_input_value_id,
611 p_run_result_id,
612 p_value,
613 p_formula_result_flag);
614 --
615 end;
616
617 end if;
618 --
619 end maintain_rr_value;
620 --
621 function create_run_result_direct
622 (p_element_type_id in number,
623 p_assignment_action_id in number,
624 p_entry_type in varchar2,
625 p_source_id in number,
626 p_source_type in varchar2,
627 p_status in varchar2,
628 p_local_unit_id in number,
629 p_start_date in date,
630 p_end_date in date,
631 p_element_entry_id in number,
632 p_time_def_id in number
633 )
634 return number
635 is
636 l_run_result_id number;
637 begin
638 --
639 select pay_run_results_s.nextval
640 into l_run_result_id
641 from dual;
642 --
643 insert into pay_run_results
644 (run_result_id,
645 element_type_id,
646 assignment_action_id,
647 entry_type,
648 source_id,
649 source_type,
650 status,
651 jurisdiction_code,
652 element_entry_id,
653 local_unit_id,
654 time_definition_id,
655 start_date,
656 end_date)
657 values
658 (l_run_result_id,
659 p_element_type_id,
660 p_assignment_action_id,
661 p_entry_type,
662 p_source_id,
663 p_source_type,
664 p_status,
665 null,
666 p_element_entry_id,
667 null,
668 p_time_def_id,
669 p_start_date,
670 p_end_date);
671 --
672 return l_run_result_id;
673 --
674 end create_run_result_direct;
675 --
676 end pay_run_result_pkg;