[Home] [Help]
PACKAGE BODY: APPS.PAY_ITERATE
Source
4 --
1 package body pay_iterate as
2 /* $Header: pyiterat.pkb 120.6.12010000.1 2008/07/27 22:56:35 appldev ship $ */
3 -- TYPES
5 -- The table types are just simple tables or various types. The records
6 -- are composite types of tables that contain a size (sz) to hold the
7 -- number of data items currently stored in the table. Data items are
8 -- stored in the tables within the records contiguously from 1 to sz.
9 --==================================================================
10 TYPE varchar_1_tbl IS TABLE OF VARCHAR(1) INDEX BY binary_integer;
11 TYPE boolean_tbl IS TABLE OF BOOLEAN INDEX BY binary_integer;
12 --
13 TYPE entry_rec IS RECORD
14 (
15 entry_id number_tbl,
16 high_value number_tbl,
17 low_value number_tbl,
18 high_value_result number_tbl,
19 low_value_result number_tbl,
20 guess_value number_tbl,
21 target_value number_tbl,
22 inter_mode number_tbl,
23 sz INTEGER
24 );
25 --
26 g_entry_list entry_rec;
27 g_asg_id per_assignments_f.assignment_id%type;
28 g_asg_act_id pay_assignment_actions.assignment_action_id%type;
29 --
30 G_INTER_MODE_INIT number := 0;
31 G_INTER_MODE_HIGH number := 1;
32 G_INTER_MODE_LOW number := 2;
33 G_INTER_MODE_NORM number := 3;
34 --
35 /*
36 Procedure: get_entry_position
37
38 Description:
39
40 This procedure searches for an already existing entry in the table
41 if it finds it then it returns the position otherwise return the
42 next free position.
43
44 */
45 procedure get_entry_position (p_entry_id in number,
46 p_found out nocopy boolean,
47 p_entry_loc out nocopy number
48 ) is
49 position number;
50 begin
51 --
52 position := 1;
53 --
54 p_found := FALSE;
55 if g_entry_list.sz = 0 then
56 p_entry_loc := 1;
57 return;
58 end if;
59 --
60 while (p_found = FALSE AND position <= g_entry_list.sz) loop
61 --
65 return;
62 if p_entry_id = g_entry_list.entry_id(position) then
63 p_found := TRUE;
64 p_entry_loc := position;
66 end if;
67 --
68 position := position+1;
69 end loop;
70 --
71 /* OK we dropped through must be new */
72 --
73 p_entry_loc := position;
74 return;
75 end;
76 --
77 /*
78 Procedure: initialise_amount
79
80 Description:
81
82 This procedure is used for initialisation with just the
83 target value is known.
84
85 */
86 function initialise_amount (
87 p_bg_id in number,
88 p_entry_id in number,
89 p_assignment_action_id in number default null,
90 p_target_value in number default null
91 ) return number is
92 l_high_gross number;
93 begin
94 l_high_gross := p_target_value * pay_iterate.get_high_gross_factor(p_bg_id);
95 --
96 return pay_iterate.initialise (p_entry_id => p_entry_id,
97 p_assignment_action_id => p_assignment_action_id,
98 p_high_value => l_high_gross,
99 p_low_value => p_target_value,
100 p_target_value => p_target_value
101 );
102 end initialise_amount;
103
104 /*
105 Procedure: initialise
106
107 Description:
108
109 This procedure is used to set up the high and low values for an
110 element entry.
111
112 */
113 function initialise (p_entry_id in number,
114 p_assignment_action_id in number default null,
115 p_high_value in number,
116 p_low_value in number,
117 p_target_value in number default null
118 ) return number is
119 found boolean;
120 entry_loc number;
121 l_asg_id pay_element_entries_f.assignment_id%type;
122 begin
123 --
124 if p_assignment_action_id is null
125 then
126 select distinct assignment_id
127 into l_asg_id
128 from pay_element_entries_f
129 where element_entry_id = p_entry_id;
130 --
131 if (l_asg_id <> g_asg_id) then
132 g_entry_list.entry_id.delete;
133 g_entry_list.high_value.delete;
134 g_entry_list.low_value.delete;
135 g_entry_list.high_value_result.delete;
136 g_entry_list.low_value_result.delete;
137 g_entry_list.guess_value.delete;
138 g_entry_list.target_value.delete;
139 g_entry_list.inter_mode.delete;
140 g_entry_list.sz := 0;
141 end if;
142 --
143 g_asg_id := l_asg_id;
144 else
145 if (p_assignment_action_id <> g_asg_act_id) then
146 g_entry_list.entry_id.delete;
147 g_entry_list.high_value.delete;
148 g_entry_list.low_value.delete;
149 g_entry_list.high_value_result.delete;
150 g_entry_list.low_value_result.delete;
151 g_entry_list.guess_value.delete;
152 g_entry_list.target_value.delete;
153 g_entry_list.inter_mode.delete;
154 g_entry_list.sz := 0;
155 end if;
156
157 g_asg_act_id := p_assignment_action_id;
158
159 end if;
160 --
161 --
162 get_entry_position(p_entry_id, found, entry_loc);
163 --
164 if found = FALSE then
165 g_entry_list.entry_id(entry_loc) := p_entry_id;
166 g_entry_list.sz := g_entry_list.sz +1;
167 end if;
168 --
169 g_entry_list.guess_value(entry_loc) := NULL;
170 g_entry_list.high_value_result(entry_loc) := NULL;
171 g_entry_list.low_value_result(entry_loc) := NULL;
172 g_entry_list.high_value(entry_loc) := p_high_value;
173 g_entry_list.low_value(entry_loc) := p_low_value;
174 g_entry_list.target_value(entry_loc) := p_target_value;
175 g_entry_list.inter_mode(entry_loc) := G_INTER_MODE_INIT;
176 --
177 return 0;
178 end initialise;
179 --
180 /*
181 Procedure: get_binary_guess
182
183 Description:
184
185 This performs a binary chop based on the mode that is sent in from
186 the parameters
187
188 */
189 function get_binary_guess (p_entry_id in number,
190 p_mode in varchar2) return number is
191 found boolean;
192 entry_loc number;
193 begin
194 --
195 /* Find the entry in the PL/SQL table */
196 get_entry_position(p_entry_id, found, entry_loc);
197 if found = false then
198 return 0;
199 end if;
200 --
201 g_entry_list.inter_mode(entry_loc) := G_INTER_MODE_NORM;
202 /* Do we need to increase the guess value? */
203 if p_mode = 'REDUCE' then
204 --
205 if g_entry_list.guess_value(entry_loc) is not NULL then
206 --
207 g_entry_list.high_value(entry_loc) :=
208 g_entry_list.guess_value(entry_loc);
209 end if;
210 --
211 g_entry_list.guess_value(entry_loc) :=
212 g_entry_list.low_value(entry_loc) +
213 ((g_entry_list.high_value(entry_loc) -
214 g_entry_list.low_value(entry_loc))/2);
215 return g_entry_list.guess_value(entry_loc);
216 --
217 else
218 if p_mode = 'INCREASE' then
222 g_entry_list.guess_value(entry_loc);
219 if g_entry_list.guess_value(entry_loc) is not NULL then
220 --
221 g_entry_list.low_value(entry_loc) :=
223 end if;
224 --
225
226 g_entry_list.guess_value(entry_loc) :=
227 g_entry_list.low_value(entry_loc) +
228 ((g_entry_list.high_value(entry_loc) -
229 g_entry_list.low_value(entry_loc))/2);
230 return g_entry_list.guess_value(entry_loc);
231 end if;
232 end if;
233 --
234 end get_binary_guess;
235 --
236 /*
237 Name : calc_inter_value
238
239 Description: This function returns the next guess value for iterative
240 looping. The formula is based on the interpolation
241 mathmatical solutions formula:-
242
243 x = a.f(b) - b.f(a)
244 ---------------
245 f(b) - f(a)
246
247 */
248 function calc_inter_value (a in number,
249 fa in number,
250 b in number,
251 fb in number)
252 return number is
253 --
254 div_number number;
255 res_number number;
256 begin
257 --
258 div_number := fb - fa;
259 res_number := (a * fb) - (b * fa);
260 -- avoid division by zero issue
261 if (div_number <> 0) then
262 res_number := res_number / div_number;
263 end if;
264 return res_number;
265 end calc_inter_value;
266 --
267 /*
268 Name : get_interpolation_guess
269
270 Description: This function performs the interpolation guessing algorithm.
271 The algorithm basically finds the point on a curve (equation
272 unknown) that intersects the y axis.
273 In this procedure the x axis is the value that we are trying
274 to find, the y axis is the distance away from the value that
275 we are trying to reach.
276
277 For example:-
278
279 In a Net to Gross calculation, then the x axis is
280 could be the gross value (this is the value we're
281 guessing), the y axis is the distance from the
282 required Net value, Calculated Net - Required Net.
283
284 */
285 function get_interpolation_guess (p_entry_id in number,
286 p_result in number default null)
287 return number is
288 found boolean;
289 entry_loc number;
290 begin
291 --
292 /* Find the entry in the PL/SQL table */
293 get_entry_position(p_entry_id, found, entry_loc);
294 --
295 /* Go get the high value */
296 if g_entry_list.inter_mode(entry_loc) = G_INTER_MODE_INIT then
297 g_entry_list.high_value_result(entry_loc) := g_entry_list.high_value_result(entry_loc) - g_entry_list.target_value(entry_loc);
298 g_entry_list.low_value_result(entry_loc) := g_entry_list.low_value_result(entry_loc) - g_entry_list.target_value(entry_loc);
299 g_entry_list.target_value(entry_loc) := 0;
300 g_entry_list.inter_mode(entry_loc) :=G_INTER_MODE_HIGH;
301 return g_entry_list.high_value(entry_loc);
302 end if;
303 --
304 /* Go get the low value */
305 if g_entry_list.inter_mode(entry_loc) = G_INTER_MODE_HIGH then
306 g_entry_list.high_value_result(entry_loc) := p_result - g_entry_list.target_value(entry_loc);
307 g_entry_list.inter_mode(entry_loc) := G_INTER_MODE_LOW;
308 return g_entry_list.low_value(entry_loc);
309 end if;
310 --
311 if g_entry_list.inter_mode(entry_loc) = G_INTER_MODE_LOW then
312 g_entry_list.low_value_result(entry_loc) :=
313 p_result - g_entry_list.target_value(entry_loc);
314 g_entry_list.inter_mode(entry_loc) := G_INTER_MODE_NORM;
315 --
316 /* OK make the first guess */
317 g_entry_list.guess_value(entry_loc) :=
318 calc_inter_value(
319 g_entry_list.low_value(entry_loc),
320 g_entry_list.low_value_result(entry_loc),
321 g_entry_list.high_value(entry_loc),
322 g_entry_list.high_value_result(entry_loc)
323 );
324 return g_entry_list.guess_value(entry_loc);
325 end if;
326 --
327 if g_entry_list.inter_mode(entry_loc) = G_INTER_MODE_NORM then
328 --
329 /* If the result returned is greater than the target then
330 replace the previous higher value
331 */
332 if p_result > g_entry_list.target_value(entry_loc) then
333 if g_entry_list.low_value_result(entry_loc) >
334 g_entry_list.target_value(entry_loc) then
335 --
336 g_entry_list.low_value(entry_loc) :=
337 g_entry_list.guess_value(entry_loc);
338 g_entry_list.low_value_result(entry_loc):=
339 p_result - g_entry_list.target_value(entry_loc);
340 else
341 g_entry_list.high_value(entry_loc) :=
342 g_entry_list.guess_value(entry_loc);
343 g_entry_list.high_value_result(entry_loc):=
344 p_result - g_entry_list.target_value(entry_loc);
345 end if;
346 else
347 if g_entry_list.low_value_result(entry_loc) <
351 g_entry_list.guess_value(entry_loc);
348 g_entry_list.target_value(entry_loc) then
349 --
350 g_entry_list.low_value(entry_loc) :=
352 g_entry_list.low_value_result(entry_loc):=
353 p_result - g_entry_list.target_value(entry_loc);
354 else
355 g_entry_list.high_value(entry_loc) :=
356 g_entry_list.guess_value(entry_loc);
357 g_entry_list.high_value_result(entry_loc):=
358 p_result - g_entry_list.target_value(entry_loc);
359 end if;
360 end if;
361 --
362 /* Now recalculate */
363 g_entry_list.guess_value(entry_loc) :=
364 calc_inter_value(
365 g_entry_list.low_value(entry_loc),
366 g_entry_list.low_value_result(entry_loc),
367 g_entry_list.high_value(entry_loc),
368 g_entry_list.high_value_result(entry_loc)
369 );
370 return round(g_entry_list.guess_value(entry_loc),2);
371 end if;
372 end get_interpolation_guess;
373 --
374 function is_first_setting (p_entry_id in number,
375 p_assignment_action_id in number default null)
376 return number is
377 found boolean;
378 entry_loc number;
379 begin
380 --
381 if p_assignment_action_id is not null
382 then
383
384 if (p_assignment_action_id <> g_asg_act_id) then
385 g_entry_list.entry_id.delete;
386 g_entry_list.high_value.delete;
387 g_entry_list.low_value.delete;
388 g_entry_list.high_value_result.delete;
389 g_entry_list.low_value_result.delete;
390 g_entry_list.guess_value.delete;
391 g_entry_list.target_value.delete;
392 g_entry_list.inter_mode.delete;
393 g_entry_list.sz := 0;
394 end if;
395
396 g_asg_act_id := p_assignment_action_id;
397 end if;
398 --
399 /* Find the entry in the PL/SQL table */
400 get_entry_position(p_entry_id, found, entry_loc);
401 --
402 /* If not found assume first run */
403 if (found = FALSE) then
404 return 1;
405 end if;
406 --
407 if (g_entry_list.inter_mode(entry_loc) = G_INTER_MODE_INIT) then
408 return 1;
409 end if;
410 return 0;
411 --
412 end is_first_setting;
413 --
414 function is_amount_set (p_entry_id in number,
415 p_assignment_action_id in number default null)
416 return number is
417 found boolean;
418 entry_loc number;
419 begin
420 --
421 if p_assignment_action_id is not null
422 then
423
424 if (p_assignment_action_id <> g_asg_act_id) then
425 g_entry_list.entry_id.delete;
426 g_entry_list.high_value.delete;
427 g_entry_list.low_value.delete;
428 g_entry_list.high_value_result.delete;
429 g_entry_list.low_value_result.delete;
430 g_entry_list.guess_value.delete;
431 g_entry_list.target_value.delete;
432 g_entry_list.inter_mode.delete;
433 g_entry_list.sz := 0;
434 end if;
435
436 g_asg_act_id := p_assignment_action_id;
437 end if;
438 --
439 /* Find the entry in the PL/SQL table */
440 get_entry_position(p_entry_id, found, entry_loc);
441 --
442 if (found = TRUE) then
443 return 1;
444 end if;
445 return 0;
446 --
447 end is_amount_set;
448 --
449 function get_high_value (p_entry_id in number)
450 return number is
451 found boolean;
452 entry_loc number;
453 begin
454 --
455 /* Find the entry in the PL/SQL table */
456 get_entry_position(p_entry_id, found, entry_loc);
457 --
458 return g_entry_list.high_value(entry_loc);
459 end get_high_value;
460 --
461 function get_target_value (p_entry_id in number)
462 return number is
463 found boolean;
464 entry_loc number;
465 begin
466 --
467 /* Find the entry in the PL/SQL table */
468 get_entry_position(p_entry_id, found, entry_loc);
469 --
470 return g_entry_list.target_value(entry_loc);
471 end get_target_value;
472 --
473 function get_low_value (p_entry_id in number)
474 return number is
475 found boolean;
476 entry_loc number;
477 begin
478 --
479 /* Find the entry in the PL/SQL table */
480 get_entry_position(p_entry_id, found, entry_loc);
481 --
482 return g_entry_list.low_value(entry_loc);
483 end get_low_value;
484 --
485 /*
486 Name : get_high_gross_factor
487
488 Description: This function is used by the iterative Fast Formula to
489 calculate the high gross factor, which is then used
490 to derive the initial high gross value.
491
492 */
493 function get_high_gross_factor (p_bg_id in number)
494 return number is
495 --
496 l_statem varchar2(2000); -- used with dynamic pl/sql
497 sql_cursor integer;
498 l_rows integer;
499 --
500 l_leg_code per_business_groups.legislation_code%type;
501 l_flag pay_legislation_rules.rule_mode%type;
502 l_found boolean;
503 l_factor number;
504 --
508 into l_leg_code
505 begin
506 --
507 select legislation_code
509 from per_business_groups_perf
510 where business_group_id = p_bg_id;
511 --
512 pay_core_utils.get_legislation_rule(p_legrul_name => 'ITERATE_DYN_HI_GRS_FACTOR',
513 p_legislation => l_leg_code,
514 p_legrul_value => l_flag,
515 p_found => l_found
516 );
517 --
518 if (l_found = FALSE ) then
519 l_factor := 2;
520 else
521 l_factor := fnd_number.canonical_to_number(l_flag);
522 end if;
523 --
524 return l_factor;
525 --
526 --
527 end get_high_gross_factor;
528 --
529 /* The following functions are used by loader scripts to load Run Type details
530 */
531 -----------------------------------------------------------------------------
532 /* Name : up_run_type
533 Purpose : Uploads the Run Type definition.
534 Arguments :
535 Notes :
536 */
537 -----------------------------------------------------------------------------
538 PROCEDURE up_run_type (p_rt_id number
539 ,p_rt_name varchar2
540 ,p_effective_start_date date
541 ,p_effective_end_date date
542 ,p_legislative_code varchar2
543 ,p_business_group varchar2
544 ,p_shortname varchar2
545 ,p_method varchar2
546 ,p_rt_name_tl varchar2
547 ,p_shortname_tl varchar2
548 ,p_eof_number number
549 ,p_srs_flag varchar2 default 'Y'
550 ,p_run_information_category varchar2 default null
551 ,p_run_information1 varchar2 default null
552 ,p_run_information2 varchar2 default null
553 ,p_run_information3 varchar2 default null
554 ,p_run_information4 varchar2 default null
555 ,p_run_information5 varchar2 default null
556 ,p_run_information6 varchar2 default null
557 ,p_run_information7 varchar2 default null
558 ,p_run_information8 varchar2 default null
559 ,p_run_information9 varchar2 default null
560 ,p_run_information10 varchar2 default null
561 ,p_run_information11 varchar2 default null
562 ,p_run_information12 varchar2 default null
563 ,p_run_information13 varchar2 default null
564 ,p_run_information14 varchar2 default null
565 ,p_run_information15 varchar2 default null
566 ,p_run_information16 varchar2 default null
567 ,p_run_information17 varchar2 default null
568 ,p_run_information18 varchar2 default null
569 ,p_run_information19 varchar2 default null
570 ,p_run_information20 varchar2 default null
571 ,p_run_information21 varchar2 default null
572 ,p_run_information22 varchar2 default null
573 ,p_run_information23 varchar2 default null
574 ,p_run_information24 varchar2 default null
575 ,p_run_information25 varchar2 default null
576 ,p_run_information26 varchar2 default null
577 ,p_run_information27 varchar2 default null
578 ,p_run_information28 varchar2 default null
579 ,p_run_information29 varchar2 default null
580 ,p_run_information30 varchar2 default null
581 )
582 is
583 --
584 -- 3 cursors for getting existing row, one for each mode.
585 --
586 cursor u_row_exists
587 is
588 select run_type_id
589 , run_type_name
590 , shortname
591 , srs_flag
592 , run_information_category
593 , run_information1
594 , run_information2
595 , run_information3
596 , run_information4
597 , run_information5
598 , run_information6
599 , run_information7
600 , run_information8
601 , run_information9
602 , run_information10
603 , run_information11
604 , run_information12
605 , run_information13
606 , run_information14
607 , run_information15
608 , run_information16
609 , run_information17
610 , run_information18
611 , run_information19
612 , run_information20
613 , run_information21
614 , run_information22
615 , run_information23
616 , run_information24
617 , run_information25
618 , run_information26
619 , run_information27
620 , run_information28
621 , run_information29
622 , run_information30
623 , legislation_code
624 , business_group_id
625 , effective_start_date
626 , effective_end_date
627 , object_version_number
628 from pay_run_types_f
629 where run_type_name = p_rt_name
630 and business_group_id = (select pbg.business_group_id
631 from per_business_groups pbg
632 where upper(pbg.name) = upper(p_business_group))
633 and legislation_code is null;
634 --
635 cursor s_row_exists
636 is
637 select run_type_id
638 , run_type_name
639 , shortname
640 , srs_flag
641 , run_information_category
642 , run_information1
646 , run_information5
643 , run_information2
644 , run_information3
645 , run_information4
647 , run_information6
648 , run_information7
649 , run_information8
650 , run_information9
651 , run_information10
652 , run_information11
653 , run_information12
654 , run_information13
655 , run_information14
656 , run_information15
657 , run_information16
658 , run_information17
659 , run_information18
660 , run_information19
661 , run_information20
662 , run_information21
663 , run_information22
664 , run_information23
665 , run_information24
666 , run_information25
667 , run_information26
668 , run_information27
669 , run_information28
670 , run_information29
671 , run_information30
672 , legislation_code
673 , business_group_id
674 , effective_start_date
675 , effective_end_date
676 , object_version_number
677 from pay_run_types_f
678 where run_type_name = p_rt_name
679 and legislation_code = p_legislative_code
680 and business_group_id is null;
681 --
682 cursor g_row_exists
683 is
684 select run_type_id
685 , run_type_name
686 , shortname
687 , srs_flag
688 , run_information_category
689 , run_information1
690 , run_information2
691 , run_information3
692 , run_information4
693 , run_information5
694 , run_information6
695 , run_information7
696 , run_information8
697 , run_information9
698 , run_information10
699 , run_information11
700 , run_information12
701 , run_information13
702 , run_information14
703 , run_information15
704 , run_information16
705 , run_information17
706 , run_information18
707 , run_information19
708 , run_information20
709 , run_information21
710 , run_information22
711 , run_information23
712 , run_information24
713 , run_information25
714 , run_information26
715 , run_information27
716 , run_information28
717 , run_information29
718 , run_information30
719 , legislation_code
720 , business_group_id
721 , effective_start_date
722 , effective_end_date
723 , object_version_number
724 from pay_run_types_f prt
725 where run_type_name = p_rt_name
726 and business_group_id is null
727 and legislation_code is null;
728 --
729 cursor get_bg_id
730 is
731 select business_group_id
732 from per_business_groups
733 where upper(name) = upper(p_business_group);
734 --
735 l_mode varchar2(30) := 'USER';
736 l_rt_id number;
737 l_rt_nm varchar2(80);
738 l_shrtnm varchar2(30);
739 l_srs_flag varchar2(30);
740 l_run_information_category varchar2(30);
741 l_run_information1 varchar2(150);
742 l_run_information2 varchar2(150);
743 l_run_information3 varchar2(150);
744 l_run_information4 varchar2(150);
745 l_run_information5 varchar2(150);
746 l_run_information6 varchar2(150);
747 l_run_information7 varchar2(150);
748 l_run_information8 varchar2(150);
749 l_run_information9 varchar2(150);
750 l_run_information10 varchar2(150);
751 l_run_information11 varchar2(150);
752 l_run_information12 varchar2(150);
753 l_run_information13 varchar2(150);
754 l_run_information14 varchar2(150);
755 l_run_information15 varchar2(150);
756 l_run_information16 varchar2(150);
757 l_run_information17 varchar2(150);
758 l_run_information18 varchar2(150);
759 l_run_information19 varchar2(150);
760 l_run_information20 varchar2(150);
761 l_run_information21 varchar2(150);
762 l_run_information22 varchar2(150);
763 l_run_information23 varchar2(150);
764 l_run_information24 varchar2(150);
765 l_run_information25 varchar2(150);
766 l_run_information26 varchar2(150);
767 l_run_information27 varchar2(150);
768 l_run_information28 varchar2(150);
769 l_run_information29 varchar2(150);
770 l_run_information30 varchar2(150);
771 l_esd date;
772 l_eed date;
773 l_lc varchar2(30);
774 l_bg number;
775 l_ovn number;
776 l_out_rt_id number;
777 l_out_esd date;
778 l_out_eed date;
779 l_out_ovn number;
780 --
781 -------------------------------------------
782 -- procedure insert_row
783 -------------------------------------------
784 procedure insert_row is
785 --
786 begin
787 --
788 hr_startup_data_api_support.enable_startup_mode(l_mode);
789 --
790 if l_mode <> 'USER' then
791 hr_startup_data_api_support.delete_owner_definitions;
792 hr_startup_data_api_support.create_owner_definition('PAY');
793 end if;
794 --
795 -- call insert api. End date will be EOT for now, will be updated
796 -- later if it should be anything other than eot.
797 --
798 pay_run_type_api.create_run_type
799 (p_effective_date => p_effective_start_date
800 -- ,p_language_code => 'US'
801 ,p_run_type_name => p_rt_name
802 ,p_run_method => p_method
803 ,p_business_group_id => l_bg
804 ,p_legislation_code => p_legislative_code
808 ,p_run_information1 => p_run_information1
805 ,p_shortname => p_shortname
806 ,p_srs_flag => p_srs_flag
807 ,p_run_information_category => p_run_information_category
809 ,p_run_information2 => p_run_information2
810 ,p_run_information3 => p_run_information3
811 ,p_run_information4 => p_run_information4
812 ,p_run_information5 => p_run_information5
813 ,p_run_information6 => p_run_information6
814 ,p_run_information7 => p_run_information7
815 ,p_run_information8 => p_run_information8
816 ,p_run_information9 => p_run_information9
817 ,p_run_information10 => p_run_information10
818 ,p_run_information11 => p_run_information11
819 ,p_run_information12 => p_run_information12
820 ,p_run_information13 => p_run_information13
821 ,p_run_information14 => p_run_information14
822 ,p_run_information15 => p_run_information15
823 ,p_run_information16 => p_run_information16
824 ,p_run_information17 => p_run_information17
825 ,p_run_information18 => p_run_information18
826 ,p_run_information19 => p_run_information19
827 ,p_run_information20 => p_run_information20
828 ,p_run_information21 => p_run_information21
829 ,p_run_information22 => p_run_information22
830 ,p_run_information23 => p_run_information23
831 ,p_run_information24 => p_run_information24
832 ,p_run_information25 => p_run_information25
833 ,p_run_information26 => p_run_information26
834 ,p_run_information27 => p_run_information27
835 ,p_run_information28 => p_run_information28
836 ,p_run_information29 => p_run_information29
837 ,p_run_information30 => p_run_information30
838 ,p_run_type_id => l_out_rt_id
839 ,p_effective_start_date => l_out_esd
840 ,p_effective_end_date => l_out_eed
841 ,p_object_version_number => l_out_ovn
842 );
843 --
844 -- cache the new details
845 --
846 select run_type_id
847 , run_type_name
848 , shortname
849 , srs_flag
850 , run_information_category
851 , run_information1
852 , run_information2
853 , run_information3
854 , run_information4
855 , run_information5
856 , run_information6
857 , run_information7
858 , run_information8
859 , run_information9
860 , run_information10
861 , run_information11
862 , run_information12
863 , run_information13
864 , run_information14
865 , run_information15
866 , run_information16
867 , run_information17
868 , run_information18
869 , run_information19
870 , run_information20
871 , run_information21
872 , run_information22
873 , run_information23
874 , run_information24
875 , run_information25
876 , run_information26
877 , run_information27
878 , run_information28
879 , run_information29
880 , run_information30
881 , legislation_code
882 , business_group_id
883 , effective_start_date
884 , effective_end_date
885 , object_version_number
886 , l_mode
887 into rec_uploaded
888 from pay_run_types_f
889 where run_type_id = l_out_rt_id;
890 --
891 end insert_row;
892 -------------------------------------------
893 -- procedure zap_insert
894 --
895 -- zap_insert indicates that the row being uploaded already exists on the
896 -- new db, but that it is a new datetracked row from the ldt, i.e. it is the
897 -- first time this row has been updated. There may well be further
898 -- datetracked rows for this row (handled by update_row), so the effective end
899 -- date is left as eot.
900 --
901 -------------------------------------------
902 procedure zap_insert(p_rt_id number) is
903 --
904 cursor chk_for_children
905 is
906 select run_type_usage_id
907 , object_version_number
908 , business_group_id
909 , legislation_code
910 , effective_start_date
911 from pay_run_type_usages_f
912 where parent_run_type_id = l_rt_id;
913 --
914 cursor chk_for_tl_children
915 is
916 select 1
917 from pay_run_types_f_tl
918 where run_type_id = l_rt_id;
919 --
920 cursor get_prods(p_sess number)
921 is
922 select product_short_name
923 from hr_owner_definitions
924 where session_id = p_sess;
925 --
926 cursor get_langs
927 is
928 select l.language_code
929 from fnd_languages l
930 where l.installed_flag in ('I','B')
931 and exists (select null
932 from pay_run_types_f_tl rtt
933 where rtt.run_type_id = p_rt_id
934 and rtt.language = l.language_code);
935
936 l_sess number;
937 l_ch_rtu_id number;
938 l_ch_ovn number;
939 l_ch_bg number;
940 l_ch_leg varchar2(30);
941 l_ch_esd date;
942 l_tl_exists number;
943 --
944 begin
945 --
946 hr_startup_data_api_support.enable_startup_mode(l_mode);
947 --
948 if l_mode <> 'USER' then
949 hr_startup_data_api_support.delete_owner_definitions;
950 hr_startup_data_api_support.create_owner_definition('PAY');
951 end if;
952 --
956 --
953 -- remove check_for_children as going to do a direct delete, not an api delete.
954 -- children will be temporarily orphaned, til the row is reinserted with
955 -- original id
957 hr_utility.trace('l_rt_id: '||to_char(l_rt_id));
958 hr_utility.trace('eff start date: '||to_char(p_effective_start_date,'dd-mon-yyyy'));
959 --
960 -- first get the original run_type_id
961 --
962 pay_prt_ins.set_base_key_value(p_rt_id);
963 --
964 -- now delete the row
965 --
966 delete from pay_run_types_f
967 where run_type_id = p_rt_id;
968 --
969 --
970 -- also need to delete the tl table row, as if one exists for a partic
971 -- run_type_id it will not insert another, hence updates will not get made
972 --
973 for each_row in get_langs loop
974 delete from pay_run_types_f_tl
975 where run_type_id = p_rt_id
976 and language = each_row.language_code;
977 end loop;
978 --
979 -- also need to delete the row in hr_application_ownerships, else will get
980 -- a unique contraint error
981 --
982 l_sess := nvl(hr_startup_data_api_support.g_startup_session_id
983 ,hr_startup_data_api_support.g_session_id);
984 --
985 for each_row in get_prods(l_sess) loop
986 delete from hr_application_ownerships
987 where key_name = 'RUN_TYPE_ID'
988 and key_value = p_rt_id
989 and product_name = each_row.product_short_name;
990 end loop;
991 --
992 pay_run_type_api.create_run_type
993 (p_effective_date => p_effective_start_date
994 ,p_run_type_name => p_rt_name
995 ,p_run_method => p_method
996 ,p_business_group_id => l_bg
997 ,p_legislation_code => p_legislative_code
998 ,p_shortname => p_shortname
999 ,p_srs_flag => p_srs_flag
1000 ,p_run_information_category => p_run_information_category
1001 ,p_run_information1 => p_run_information1
1002 ,p_run_information2 => p_run_information2
1003 ,p_run_information3 => p_run_information3
1004 ,p_run_information4 => p_run_information4
1005 ,p_run_information5 => p_run_information5
1006 ,p_run_information6 => p_run_information6
1007 ,p_run_information7 => p_run_information7
1008 ,p_run_information8 => p_run_information8
1009 ,p_run_information9 => p_run_information9
1010 ,p_run_information10 => p_run_information10
1011 ,p_run_information11 => p_run_information11
1012 ,p_run_information12 => p_run_information12
1013 ,p_run_information13 => p_run_information13
1014 ,p_run_information14 => p_run_information14
1015 ,p_run_information15 => p_run_information15
1016 ,p_run_information16 => p_run_information16
1017 ,p_run_information17 => p_run_information17
1018 ,p_run_information18 => p_run_information18
1019 ,p_run_information19 => p_run_information19
1020 ,p_run_information20 => p_run_information20
1021 ,p_run_information21 => p_run_information21
1022 ,p_run_information22 => p_run_information22
1023 ,p_run_information23 => p_run_information23
1024 ,p_run_information24 => p_run_information24
1025 ,p_run_information25 => p_run_information25
1026 ,p_run_information26 => p_run_information26
1027 ,p_run_information27 => p_run_information27
1028 ,p_run_information28 => p_run_information28
1029 ,p_run_information29 => p_run_information29
1030 ,p_run_information30 => p_run_information30
1031 ,p_run_type_id => l_out_rt_id
1032 ,p_effective_start_date => l_out_esd
1033 ,p_effective_end_date => l_out_eed
1034 ,p_object_version_number => l_out_ovn
1035 );
1036 --
1037 hr_utility.trace('AFTER ZAP CREATE');
1038 --
1039 -- set the uploaded cache
1040 --
1041 select run_type_id
1042 , run_type_name
1043 , shortname
1044 , srs_flag
1045 , run_information_category
1046 , run_information1
1047 , run_information2
1048 , run_information3
1049 , run_information4
1050 , run_information5
1051 , run_information6
1052 , run_information7
1053 , run_information8
1054 , run_information9
1055 , run_information10
1056 , run_information11
1057 , run_information12
1058 , run_information13
1059 , run_information14
1060 , run_information15
1061 , run_information16
1062 , run_information17
1063 , run_information18
1064 , run_information19
1065 , run_information20
1066 , run_information21
1067 , run_information22
1068 , run_information23
1069 , run_information24
1070 , run_information25
1071 , run_information26
1072 , run_information27
1073 , run_information28
1074 , run_information29
1075 , run_information30
1076 , legislation_code
1077 , business_group_id
1078 , effective_start_date
1079 , effective_end_date
1080 , object_version_number
1081 , l_mode
1082 into rec_uploaded
1083 from pay_run_types_f
1084 where run_type_id = l_out_rt_id;
1085 --
1086 end zap_insert;
1087 -------------------------------------------
1088 -- procedure update_row
1089 --
1093 -- set to the correct date by set_end_date.
1090 -- If multiple datatrack rows exist in the ldt for one particular row, multiple
1091 -- updates will have to be done achieve the dt history. The effective_end_date
1092 -- is left at eot. If eot is not the final end date of the dt row it will be
1094 --
1095 -------------------------------------------
1096 procedure update_row is
1097 --
1098 begin
1099 --
1100 hr_startup_data_api_support.enable_startup_mode(l_mode);
1101 --
1102 if l_mode <> 'USER' then
1103 hr_startup_data_api_support.delete_owner_definitions;
1104 hr_startup_data_api_support.create_owner_definition('PAY');
1105 end if;
1106 --
1107 pay_run_type_api.update_run_type
1108 (p_effective_date => p_effective_start_date
1109 ,p_datetrack_update_mode => 'UPDATE'
1110 ,p_run_type_id => rec_uploaded.rt_id
1111 ,p_object_version_number => rec_uploaded.rt_ovn
1112 ,p_business_group_id => l_bg
1113 ,p_legislation_code => p_legislative_code
1114 ,p_shortname => p_shortname
1115 ,p_srs_flag => p_srs_flag
1116 ,p_run_information_category => p_run_information_category
1117 ,p_run_information1 => p_run_information1
1118 ,p_run_information2 => p_run_information2
1119 ,p_run_information3 => p_run_information3
1120 ,p_run_information4 => p_run_information4
1121 ,p_run_information5 => p_run_information5
1122 ,p_run_information6 => p_run_information6
1123 ,p_run_information7 => p_run_information7
1124 ,p_run_information8 => p_run_information8
1125 ,p_run_information9 => p_run_information9
1126 ,p_run_information10 => p_run_information10
1127 ,p_run_information11 => p_run_information11
1128 ,p_run_information12 => p_run_information12
1129 ,p_run_information13 => p_run_information13
1130 ,p_run_information14 => p_run_information14
1131 ,p_run_information15 => p_run_information15
1132 ,p_run_information16 => p_run_information16
1133 ,p_run_information17 => p_run_information17
1134 ,p_run_information18 => p_run_information18
1135 ,p_run_information19 => p_run_information19
1136 ,p_run_information20 => p_run_information20
1137 ,p_run_information21 => p_run_information21
1138 ,p_run_information22 => p_run_information22
1139 ,p_run_information23 => p_run_information23
1140 ,p_run_information24 => p_run_information24
1141 ,p_run_information25 => p_run_information25
1142 ,p_run_information26 => p_run_information26
1143 ,p_run_information27 => p_run_information27
1144 ,p_run_information28 => p_run_information28
1145 ,p_run_information29 => p_run_information29
1146 ,p_run_information30 => p_run_information30
1147 ,p_effective_start_date => l_out_esd
1148 ,p_effective_end_date => l_out_eed
1149 );
1150 --
1151 -- cache the latest uploaded row
1152 --
1153 select run_type_id
1154 , run_type_name
1155 , shortname
1156 , srs_flag
1157 , run_information_category
1158 , run_information1
1159 , run_information2
1160 , run_information3
1161 , run_information4
1162 , run_information5
1163 , run_information6
1164 , run_information7
1165 , run_information8
1166 , run_information9
1167 , run_information10
1168 , run_information11
1169 , run_information12
1170 , run_information13
1171 , run_information14
1172 , run_information15
1173 , run_information16
1174 , run_information17
1175 , run_information18
1176 , run_information19
1177 , run_information20
1178 , run_information21
1179 , run_information22
1180 , run_information23
1181 , run_information24
1182 , run_information25
1183 , run_information26
1184 , run_information27
1185 , run_information28
1186 , run_information29
1187 , run_information30
1188 , legislation_code
1189 , business_group_id
1190 , effective_start_date
1191 , effective_end_date
1192 , object_version_number
1193 , l_mode
1194 into rec_uploaded
1195 from pay_run_types_f
1196 where run_type_id = rec_uploaded.rt_id
1197 and effective_start_date = l_out_esd
1198 and effective_end_date = l_out_eed;
1199 --
1200 end update_row;
1201 -------------------------------------------
1202 -- procedure SET_END_DATE
1203 --
1204 -- SET_END_DATE is used to set the end date of updated or newly inserted rows
1205 -- that are not set to the end of time.
1206 -- First check if any child rows exist. If they do, then ZAP them, so that the
1207 -- parent row can be end date deleted. If the children should still exist,
1208 -- they will be reinserted as part of the run type usage upload.
1209 --
1210 -------------------------------------------
1211 procedure set_end_date is
1212 --
1213 cursor chk_for_children
1214 is
1215 select run_type_usage_id
1216 , object_version_number
1217 , business_group_id
1218 , legislation_code
1219 from pay_run_type_usages_f
1220 where parent_run_type_id = rec_uploaded.rt_id
1221 and effective_end_date >= g_to_be_uploaded_eed;
1222 --
1226 from pay_run_types_f_tl
1223 cursor chk_for_tl_children
1224 is
1225 select 1
1227 where run_type_id = rec_uploaded.rt_id;
1228 --
1229 l_ch_rtu_id number;
1230 l_ch_ovn number;
1231 l_ch_bg number;
1232 l_ch_leg varchar2(30);
1233 l_tl_exists number;
1234 --
1235 begin
1236 --
1237 hr_startup_data_api_support.enable_startup_mode(l_mode);
1238 --
1239 if l_mode <> 'USER' then
1240 hr_startup_data_api_support.delete_owner_definitions;
1241 hr_startup_data_api_support.create_owner_definition('PAY');
1242 end if;
1243 --
1244 open chk_for_children;
1245 fetch chk_for_children into l_ch_rtu_id, l_ch_ovn, l_ch_bg, l_ch_leg;
1246 if chk_for_children%FOUND then
1247 --
1248 close chk_for_children;
1249 for each_child in chk_for_children loop
1250 --
1251 pay_run_type_usage_api.delete_run_type_usage
1252 (p_effective_date => g_to_be_uploaded_eed
1253 ,p_datetrack_delete_mode => 'ZAP'
1254 ,p_run_type_usage_id => each_child.run_type_usage_id
1255 ,p_object_version_number => each_child.object_version_number
1256 ,p_business_group_id => each_child.business_group_id
1257 ,p_legislation_code => each_child.legislation_code
1258 ,p_effective_start_date => l_out_esd
1259 ,p_effective_end_date => l_out_eed
1260 );
1261 end loop;
1262 else
1263 close chk_for_children;
1264 end if;
1265 --
1266 open chk_for_tl_children;
1267 fetch chk_for_tl_children into l_tl_exists;
1268 if chk_for_tl_children%FOUND then
1269 close chk_for_tl_children;
1270 pay_rtt_del.del_tl(p_run_type_id => rec_uploaded.rt_id);
1271 else
1272 close chk_for_tl_children;
1273 end if;
1274 --
1275 -- now delete the run type
1276 --
1277 pay_run_type_api.delete_run_type
1278 (p_effective_date => g_to_be_uploaded_eed
1279 ,p_datetrack_delete_mode => 'DELETE'
1280 ,p_run_type_id => rec_uploaded.rt_id
1281 ,p_object_version_number => rec_uploaded.rt_ovn
1282 ,p_business_group_id => rec_uploaded.rt_bg
1283 ,p_legislation_code => rec_uploaded.rt_leg_code
1284 ,p_effective_start_date => l_out_esd
1285 ,p_effective_end_date => l_out_eed
1286 );
1287 --
1288 end set_end_date;
1289 --
1290 --
1291 BEGIN -- up_run_type
1292 --
1293 -- Set the mode
1294 --
1295 If p_business_group IS NOT NULL then
1296 if p_legislative_code IS NULL THEN
1297 l_mode := 'USER';
1298 --
1299 -- get the bg id
1300 --
1301 open get_bg_id;
1302 fetch get_bg_id into l_bg;
1303 close get_bg_id;
1304 else
1305 -- raise error cannot have leg and bg populated
1306 null;
1307 end if;
1308 else -- bg is null
1309 if p_legislative_code is NOT NULL then
1310 l_mode := 'STARTUP';
1311 else
1312 l_mode := 'GENERIC';
1313 end if;
1314 end if;
1315 --
1316 -- Is the new row part of the same dt record as the last uploaded row?
1317 --
1318 IF p_eof_number = 1 then
1319 IF p_rt_id <> g_old_rt_id then
1320 --
1321 -- this is a new upload row. Check to see if the previous uploaded record's
1322 -- effective end date was eot. If not update the row to whatever the end
1323 -- date should be.
1324 --
1325 if g_to_be_uploaded_eed <> hr_api.g_eot then
1326 --
1327 if l_call_set_end_date then
1328 hr_utility.trace('before SET_END_DATE');
1329 set_end_date;
1330 hr_utility.trace('after SET_END_DATE');
1331 else
1332 null;
1333 end if;
1334 else
1335 -- clear down prev_upload_rec;
1336 null;
1337 end if;
1338 --
1339 -- Cache the row to be upload
1340 --
1341 g_to_be_uploaded_eed := p_effective_end_date;
1342 --
1343 g_old_rt_id := p_rt_id;
1344 --
1345 -- clear down the uploaded row, as now on new row
1346 --
1347 rec_uploaded.rt_id := '';
1348 rec_uploaded.rt_name := '';
1349 rec_uploaded.rt_shortname := '';
1350 rec_uploaded.rt_srs_flag := '';
1351 rec_uploaded.rt_run_information_category := '';
1352 rec_uploaded.rt_run_information1 :='';
1353 rec_uploaded.rt_run_information2 :='';
1354 rec_uploaded.rt_run_information3 :='';
1355 rec_uploaded.rt_run_information4 :='';
1356 rec_uploaded.rt_run_information5 :='';
1357 rec_uploaded.rt_run_information6 :='';
1358 rec_uploaded.rt_run_information7 :='';
1359 rec_uploaded.rt_run_information8 :='';
1360 rec_uploaded.rt_run_information9 :='';
1361 rec_uploaded.rt_run_information10 :='';
1362 rec_uploaded.rt_run_information11 :='';
1363 rec_uploaded.rt_run_information12 :='';
1364 rec_uploaded.rt_run_information13 :='';
1365 rec_uploaded.rt_run_information14 :='';
1366 rec_uploaded.rt_run_information15 :='';
1367 rec_uploaded.rt_run_information16 :='';
1368 rec_uploaded.rt_run_information17 :='';
1369 rec_uploaded.rt_run_information18 :='';
1370 rec_uploaded.rt_run_information19 :='';
1371 rec_uploaded.rt_run_information20 :='';
1372 rec_uploaded.rt_run_information21 :='';
1373 rec_uploaded.rt_run_information22 :='';
1374 rec_uploaded.rt_run_information23 :='';
1375 rec_uploaded.rt_run_information24 :='';
1376 rec_uploaded.rt_run_information25 :='';
1377 rec_uploaded.rt_run_information26 :='';
1378 rec_uploaded.rt_run_information27 :='';
1379 rec_uploaded.rt_run_information28 :='';
1380 rec_uploaded.rt_run_information29 :='';
1381 rec_uploaded.rt_run_information30 :='';
1382 rec_uploaded.rt_leg_code := '';
1386 rec_uploaded.rt_ovn := '';
1383 rec_uploaded.rt_bg := '';
1384 rec_uploaded.rt_esd := '';
1385 rec_uploaded.rt_eed := '';
1387 rec_uploaded.rt_mode := '';
1388 --
1389 -- Does the same row already exist on the db?
1390 --
1391 if l_mode = 'USER' then
1392 hr_utility.trace('p_rt_name: '||p_rt_name);
1393 open u_row_exists;
1394 fetch u_row_exists into l_rt_id, l_rt_nm, l_shrtnm, l_srs_flag
1395 ,l_run_information_category, l_run_information1,
1396 l_run_information2
1397 ,l_run_information3, l_run_information4,
1398 l_run_information5
1399 ,l_run_information6, l_run_information7,
1400 l_run_information8
1401 ,l_run_information9, l_run_information10,
1402 l_run_information11
1403 ,l_run_information12, l_run_information13,
1404 l_run_information14
1405 ,l_run_information15, l_run_information16,
1406 l_run_information17
1407 ,l_run_information18, l_run_information19,
1408 l_run_information20
1409 ,l_run_information21, l_run_information22,
1410 l_run_information23
1411 ,l_run_information24, l_run_information25,
1412 l_run_information26
1413 ,l_run_information27, l_run_information28,
1414 l_run_information29
1415 ,l_run_information30, l_lc, l_bg, l_esd, l_eed,
1416 l_ovn;
1417 IF u_row_exists%NOTFOUND then
1418 hr_utility.trace('l_rt_id: '||to_char(l_rt_id));
1419 close u_row_exists;
1420 insert_row;
1421 ELSE -- this row does already exist
1422 --
1423 -- see if any changes have been made
1424 --
1425 --
1426 if l_rt_nm <> p_rt_name
1427 or nvl(l_shrtnm,'NULL_VALUE') <> nvl(p_shortname,'NULL_VALUE')
1428 or nvl(l_srs_flag,'NULL_VALUE') <> nvl(p_srs_flag,'NULL_VALUE')
1429 or nvl(l_run_information_category,'NULL_VALUE')
1430 <> nvl(p_run_information_category,'NULL_VALUE')
1431 or nvl(l_run_information1,'NULL_VALUE')
1432 <> nvl(p_run_information1,'NULL_VALUE')
1433 or nvl(l_run_information2,'NULL_VALUE')
1434 <> nvl(p_run_information2,'NULL_VALUE')
1435 or nvl(l_run_information3,'NULL_VALUE')
1436 <> nvl(p_run_information3,'NULL_VALUE')
1437 or nvl(l_run_information4,'NULL_VALUE')
1438 <> nvl(p_run_information4,'NULL_VALUE')
1439 or nvl(l_run_information5,'NULL_VALUE')
1440 <> nvl(p_run_information5,'NULL_VALUE')
1441 or nvl(l_run_information6,'NULL_VALUE')
1442 <> nvl(p_run_information6,'NULL_VALUE')
1443 or nvl(l_run_information7,'NULL_VALUE')
1444 <> nvl(p_run_information7,'NULL_VALUE')
1445 or nvl(l_run_information8,'NULL_VALUE')
1446 <> nvl(p_run_information8,'NULL_VALUE')
1447 or nvl(l_run_information9,'NULL_VALUE')
1448 <> nvl(p_run_information9,'NULL_VALUE')
1449 or nvl(l_run_information10,'NULL_VALUE')
1450 <> nvl(p_run_information10,'NULL_VALUE')
1451 or nvl(l_run_information11,'NULL_VALUE')
1452 <> nvl(p_run_information11,'NULL_VALUE')
1453 or nvl(l_run_information12,'NULL_VALUE')
1454 <> nvl(p_run_information12,'NULL_VALUE')
1455 or nvl(l_run_information13,'NULL_VALUE')
1456 <> nvl(p_run_information13,'NULL_VALUE')
1457 or nvl(l_run_information14,'NULL_VALUE')
1458 <> nvl(p_run_information14,'NULL_VALUE')
1459 or nvl(l_run_information15,'NULL_VALUE')
1460 <> nvl(p_run_information15,'NULL_VALUE')
1461 or nvl(l_run_information16,'NULL_VALUE')
1462 <> nvl(p_run_information16,'NULL_VALUE')
1463 or nvl(l_run_information17,'NULL_VALUE')
1464 <> nvl(p_run_information17,'NULL_VALUE')
1465 or nvl(l_run_information18,'NULL_VALUE')
1466 <> nvl(p_run_information18,'NULL_VALUE')
1467 or nvl(l_run_information19,'NULL_VALUE')
1468 <> nvl(p_run_information19,'NULL_VALUE')
1469 or nvl(l_run_information20,'NULL_VALUE')
1470 <> nvl(p_run_information20,'NULL_VALUE')
1471 or nvl(l_run_information21,'NULL_VALUE')
1472 <> nvl(p_run_information21,'NULL_VALUE')
1473 or nvl(l_run_information22,'NULL_VALUE')
1474 <> nvl(p_run_information22,'NULL_VALUE')
1475 or nvl(l_run_information23,'NULL_VALUE')
1476 <> nvl(p_run_information23,'NULL_VALUE')
1477 or nvl(l_run_information24,'NULL_VALUE')
1478 <> nvl(p_run_information24,'NULL_VALUE')
1479 or nvl(l_run_information25,'NULL_VALUE')
1480 <> nvl(p_run_information25,'NULL_VALUE')
1481 or nvl(l_run_information26,'NULL_VALUE')
1482 <> nvl(p_run_information26,'NULL_VALUE')
1483 or nvl(l_run_information27,'NULL_VALUE')
1484 <> nvl(p_run_information27,'NULL_VALUE')
1485 or nvl(l_run_information28,'NULL_VALUE')
1486 <> nvl(p_run_information28,'NULL_VALUE')
1487 or nvl(l_run_information29,'NULL_VALUE')
1488 <> nvl(p_run_information29,'NULL_VALUE')
1489 or nvl(l_run_information30,'NULL_VALUE')
1490 <> nvl(p_run_information30,'NULL_VALUE')
1491 or l_esd <> p_effective_start_date then
1492 --
1493 hr_utility.trace('before zap l_rt_id: '||to_char(l_rt_id));
1494 zap_insert(l_rt_id);
1495 --
1496 else
1497 --
1498 -- check if just end date has changed
1499 --
1500 if l_eed <> p_effective_end_date then
1501 --
1502 -- don't actually have to update the row, as it is the same
1506 rec_uploaded.rt_id := l_rt_id;
1503 -- as an existing row except for the end date, which will be set
1504 -- using 'SET_END_DATE' later. So cache the values for later use.
1505 --
1507 rec_uploaded.rt_name := l_rt_nm;
1508 rec_uploaded.rt_shortname := l_shrtnm;
1509 rec_uploaded.rt_srs_flag := l_srs_flag;
1510 rec_uploaded.rt_run_information_category :=
1511 l_run_information_category;
1512 rec_uploaded.rt_run_information1 := l_run_information1;
1513 rec_uploaded.rt_run_information2 := l_run_information2;
1514 rec_uploaded.rt_run_information3 := l_run_information3;
1515 rec_uploaded.rt_run_information4 := l_run_information4;
1516 rec_uploaded.rt_run_information5 := l_run_information5;
1517 rec_uploaded.rt_run_information6 := l_run_information6;
1518 rec_uploaded.rt_run_information7 := l_run_information7;
1519 rec_uploaded.rt_run_information8 := l_run_information8;
1520 rec_uploaded.rt_run_information9 := l_run_information9;
1521 rec_uploaded.rt_run_information10 := l_run_information10;
1522 rec_uploaded.rt_run_information11 := l_run_information11;
1523 rec_uploaded.rt_run_information12 := l_run_information12;
1524 rec_uploaded.rt_run_information13 := l_run_information13;
1525 rec_uploaded.rt_run_information14 := l_run_information14;
1526 rec_uploaded.rt_run_information15 := l_run_information15;
1527 rec_uploaded.rt_run_information16 := l_run_information16;
1528 rec_uploaded.rt_run_information17 := l_run_information17;
1529 rec_uploaded.rt_run_information18 := l_run_information18;
1530 rec_uploaded.rt_run_information19 := l_run_information19;
1531 rec_uploaded.rt_run_information20 := l_run_information20;
1532 rec_uploaded.rt_run_information21 := l_run_information21;
1533 rec_uploaded.rt_run_information22 := l_run_information22;
1534 rec_uploaded.rt_run_information23 := l_run_information23;
1535 rec_uploaded.rt_run_information24 := l_run_information24;
1536 rec_uploaded.rt_run_information25 := l_run_information25;
1537 rec_uploaded.rt_run_information26 := l_run_information26;
1538 rec_uploaded.rt_run_information27 := l_run_information27;
1539 rec_uploaded.rt_run_information28 := l_run_information28;
1540 rec_uploaded.rt_run_information29 := l_run_information29;
1541 rec_uploaded.rt_run_information30 := l_run_information30;
1542 rec_uploaded.rt_leg_code := l_lc;
1543 rec_uploaded.rt_bg := l_bg;
1544 rec_uploaded.rt_esd := l_esd;
1545 rec_uploaded.rt_eed := l_eed;
1546 rec_uploaded.rt_ovn := l_ovn;
1547 rec_uploaded.rt_mode := l_mode;
1548 else
1549 l_call_set_end_date := false;
1550 end if;
1551 end if;
1552 --
1553 close u_row_exists;
1554 END IF; -- does row already exist for u_row_exists
1555 --
1556 elsif l_mode = 'STARTUP' then
1557 open s_row_exists;
1558 fetch s_row_exists into l_rt_id, l_rt_nm, l_shrtnm, l_srs_flag
1559 ,l_run_information_category, l_run_information1,
1560 l_run_information2
1561 ,l_run_information3, l_run_information4,
1562 l_run_information5
1563 ,l_run_information6, l_run_information7,
1564 l_run_information8
1565 ,l_run_information9, l_run_information10,
1566 l_run_information11
1567 ,l_run_information12, l_run_information13,
1568 l_run_information14
1569 ,l_run_information15, l_run_information16,
1570 l_run_information17
1571 ,l_run_information18, l_run_information19,
1572 l_run_information20
1573 ,l_run_information21, l_run_information22,
1574 l_run_information23
1575 ,l_run_information24, l_run_information25,
1576 l_run_information26
1577 ,l_run_information27, l_run_information28,
1578 l_run_information29
1579 ,l_run_information30, l_lc, l_bg, l_esd, l_eed,
1580 l_ovn;
1581 IF s_row_exists%NOTFOUND then
1582 close s_row_exists;
1583 insert_row;
1584 ELSE -- this row does already exist
1585 --
1586 -- see if any changes have been made
1587 --
1588 if l_rt_nm <> p_rt_name
1589 or nvl(l_shrtnm,'NULL_VALUE') <> nvl(p_shortname,'NULL_VALUE')
1590 or nvl(l_srs_flag,'NULL_VALUE') <> nvl(p_srs_flag,'NULL_VALUE')
1591 or nvl(l_run_information_category,'NULL_VALUE')
1592 <> nvl(p_run_information_category,'NULL_VALUE')
1593 or nvl(l_run_information1,'NULL_VALUE')
1594 <> nvl(p_run_information1,'NULL_VALUE')
1595 or nvl(l_run_information2,'NULL_VALUE')
1596 <> nvl(p_run_information2,'NULL_VALUE')
1597 or nvl(l_run_information3,'NULL_VALUE')
1598 <> nvl(p_run_information3,'NULL_VALUE')
1599 or nvl(l_run_information4,'NULL_VALUE')
1600 <> nvl(p_run_information4,'NULL_VALUE')
1601 or nvl(l_run_information5,'NULL_VALUE')
1602 <> nvl(p_run_information5,'NULL_VALUE')
1603 or nvl(l_run_information6,'NULL_VALUE')
1604 <> nvl(p_run_information6,'NULL_VALUE')
1605 or nvl(l_run_information7,'NULL_VALUE')
1606 <> nvl(p_run_information7,'NULL_VALUE')
1607 or nvl(l_run_information8,'NULL_VALUE')
1608 <> nvl(p_run_information8,'NULL_VALUE')
1609 or nvl(l_run_information9,'NULL_VALUE')
1610 <> nvl(p_run_information9,'NULL_VALUE')
1611 or nvl(l_run_information10,'NULL_VALUE')
1612 <> nvl(p_run_information10,'NULL_VALUE')
1613 or nvl(l_run_information11,'NULL_VALUE')
1614 <> nvl(p_run_information11,'NULL_VALUE')
1618 <> nvl(p_run_information13,'NULL_VALUE')
1615 or nvl(l_run_information12,'NULL_VALUE')
1616 <> nvl(p_run_information12,'NULL_VALUE')
1617 or nvl(l_run_information13,'NULL_VALUE')
1619 or nvl(l_run_information14,'NULL_VALUE')
1620 <> nvl(p_run_information14,'NULL_VALUE')
1621 or nvl(l_run_information15,'NULL_VALUE')
1622 <> nvl(p_run_information15,'NULL_VALUE')
1623 or nvl(l_run_information16,'NULL_VALUE')
1624 <> nvl(p_run_information16,'NULL_VALUE')
1625 or nvl(l_run_information17,'NULL_VALUE')
1626 <> nvl(p_run_information17,'NULL_VALUE')
1627 or nvl(l_run_information18,'NULL_VALUE')
1628 <> nvl(p_run_information18,'NULL_VALUE')
1629 or nvl(l_run_information19,'NULL_VALUE')
1630 <> nvl(p_run_information19,'NULL_VALUE')
1631 or nvl(l_run_information20,'NULL_VALUE')
1632 <> nvl(p_run_information20,'NULL_VALUE')
1633 or nvl(l_run_information21,'NULL_VALUE')
1634 <> nvl(p_run_information21,'NULL_VALUE')
1635 or nvl(l_run_information22,'NULL_VALUE')
1636 <> nvl(p_run_information22,'NULL_VALUE')
1637 or nvl(l_run_information23,'NULL_VALUE')
1638 <> nvl(p_run_information23,'NULL_VALUE')
1639 or nvl(l_run_information24,'NULL_VALUE')
1640 <> nvl(p_run_information24,'NULL_VALUE')
1641 or nvl(l_run_information25,'NULL_VALUE')
1642 <> nvl(p_run_information25,'NULL_VALUE')
1643 or nvl(l_run_information26,'NULL_VALUE')
1644 <> nvl(p_run_information26,'NULL_VALUE')
1645 or nvl(l_run_information27,'NULL_VALUE')
1646 <> nvl(p_run_information27,'NULL_VALUE')
1647 or nvl(l_run_information28,'NULL_VALUE')
1648 <> nvl(p_run_information28,'NULL_VALUE')
1649 or nvl(l_run_information29,'NULL_VALUE')
1650 <> nvl(p_run_information29,'NULL_VALUE')
1651 or nvl(l_run_information30,'NULL_VALUE')
1652 <> nvl(p_run_information30,'NULL_VALUE')
1653 or l_esd <> p_effective_start_date then
1654 --
1655 zap_insert(l_rt_id);
1656 --
1657 else
1658 --
1659 -- check if just end date has changed
1660 --
1661 if l_eed <> p_effective_end_date then
1662 --
1663 -- don't actually have to update the row, as it is the same
1664 -- as an existing row except for the end date, which will be set
1665 -- using 'SET_END_DATE' later. So cache the values for later use.
1666 --
1667 rec_uploaded.rt_id := l_rt_id;
1668 rec_uploaded.rt_name := l_rt_nm;
1669 rec_uploaded.rt_shortname := l_shrtnm;
1670 rec_uploaded.rt_srs_flag := l_srs_flag;
1671 rec_uploaded.rt_run_information_category :=
1672 l_run_information_category;
1673 rec_uploaded.rt_run_information1 := l_run_information1;
1674 rec_uploaded.rt_run_information2 := l_run_information2;
1675 rec_uploaded.rt_run_information3 := l_run_information3;
1676 rec_uploaded.rt_run_information4 := l_run_information4;
1677 rec_uploaded.rt_run_information5 := l_run_information5;
1678 rec_uploaded.rt_run_information6 := l_run_information6;
1679 rec_uploaded.rt_run_information7 := l_run_information7;
1680 rec_uploaded.rt_run_information8 := l_run_information8;
1681 rec_uploaded.rt_run_information9 := l_run_information9;
1682 rec_uploaded.rt_run_information10 := l_run_information10;
1683 rec_uploaded.rt_run_information11 := l_run_information11;
1684 rec_uploaded.rt_run_information12 := l_run_information12;
1685 rec_uploaded.rt_run_information13 := l_run_information13;
1686 rec_uploaded.rt_run_information14 := l_run_information14;
1687 rec_uploaded.rt_run_information15 := l_run_information15;
1688 rec_uploaded.rt_run_information16 := l_run_information16;
1689 rec_uploaded.rt_run_information17 := l_run_information17;
1690 rec_uploaded.rt_run_information18 := l_run_information18;
1691 rec_uploaded.rt_run_information19 := l_run_information19;
1692 rec_uploaded.rt_run_information20 := l_run_information20;
1693 rec_uploaded.rt_run_information21 := l_run_information21;
1694 rec_uploaded.rt_run_information22 := l_run_information22;
1695 rec_uploaded.rt_run_information23 := l_run_information23;
1696 rec_uploaded.rt_run_information24 := l_run_information24;
1697 rec_uploaded.rt_run_information25 := l_run_information25;
1698 rec_uploaded.rt_run_information26 := l_run_information26;
1699 rec_uploaded.rt_run_information27 := l_run_information27;
1700 rec_uploaded.rt_run_information28 := l_run_information28;
1701 rec_uploaded.rt_run_information29 := l_run_information29;
1702 rec_uploaded.rt_run_information30 := l_run_information30;
1703 rec_uploaded.rt_leg_code := l_lc;
1704 rec_uploaded.rt_bg := l_bg;
1705 rec_uploaded.rt_esd := l_esd;
1706 rec_uploaded.rt_eed := l_eed;
1707 rec_uploaded.rt_ovn := l_ovn;
1708 rec_uploaded.rt_mode := l_mode;
1709 else
1710 l_call_set_end_date := false;
1711 end if;
1712 end if;
1713 --
1714 close s_row_exists;
1715 END IF; -- does row already exist for s_row_exists
1716 --
1717 else -- l_mode = GENERIC
1718 open g_row_exists;
1719 fetch g_row_exists into l_rt_id, l_rt_nm, l_shrtnm, l_srs_flag
1720 ,l_run_information_category, l_run_information1,
1721 l_run_information2
1722 ,l_run_information3, l_run_information4,
1723 l_run_information5
1724 ,l_run_information6, l_run_information7,
1725 l_run_information8
1726 ,l_run_information9, l_run_information10,
1727 l_run_information11
1728 ,l_run_information12, l_run_information13,
1729 l_run_information14
1733 l_run_information20
1730 ,l_run_information15, l_run_information16,
1731 l_run_information17
1732 ,l_run_information18, l_run_information19,
1734 ,l_run_information21, l_run_information22,
1735 l_run_information23
1736 ,l_run_information24, l_run_information25,
1737 l_run_information26
1738 ,l_run_information27, l_run_information28,
1739 l_run_information29
1740 ,l_run_information30, l_lc, l_bg, l_esd, l_eed,
1741 l_ovn;
1742 IF g_row_exists%NOTFOUND then
1743 close g_row_exists;
1744 insert_row;
1745 ELSE -- this row does already exist
1746 --
1747 -- see if any changes have been made
1748 --
1749 if l_rt_nm <> p_rt_name
1750 or nvl(l_shrtnm,'NULL_VALUE') <> nvl(p_shortname,'NULL_VALUE')
1751 or nvl(l_srs_flag,'NULL_VALUE') <> nvl(p_srs_flag,'NULL_VALUE')
1752 or nvl(l_run_information_category,'NULL_VALUE')
1753 <> nvl(p_run_information_category,'NULL_VALUE')
1754 or nvl(l_run_information1,'NULL_VALUE')
1755 <> nvl(p_run_information1,'NULL_VALUE')
1756 or nvl(l_run_information2,'NULL_VALUE')
1757 <> nvl(p_run_information2,'NULL_VALUE')
1758 or nvl(l_run_information3,'NULL_VALUE')
1759 <> nvl(p_run_information3,'NULL_VALUE')
1760 or nvl(l_run_information4,'NULL_VALUE')
1761 <> nvl(p_run_information4,'NULL_VALUE')
1762 or nvl(l_run_information5,'NULL_VALUE')
1763 <> nvl(p_run_information5,'NULL_VALUE')
1764 or nvl(l_run_information6,'NULL_VALUE')
1765 <> nvl(p_run_information6,'NULL_VALUE')
1766 or nvl(l_run_information7,'NULL_VALUE')
1767 <> nvl(p_run_information7,'NULL_VALUE')
1768 or nvl(l_run_information8,'NULL_VALUE')
1769 <> nvl(p_run_information8,'NULL_VALUE')
1770 or nvl(l_run_information9,'NULL_VALUE')
1771 <> nvl(p_run_information9,'NULL_VALUE')
1772 or nvl(l_run_information10,'NULL_VALUE')
1773 <> nvl(p_run_information10,'NULL_VALUE')
1774 or nvl(l_run_information11,'NULL_VALUE')
1775 <> nvl(p_run_information11,'NULL_VALUE')
1776 or nvl(l_run_information12,'NULL_VALUE')
1777 <> nvl(p_run_information12,'NULL_VALUE')
1778 or nvl(l_run_information13,'NULL_VALUE')
1779 <> nvl(p_run_information13,'NULL_VALUE')
1780 or nvl(l_run_information14,'NULL_VALUE')
1781 <> nvl(p_run_information14,'NULL_VALUE')
1782 or nvl(l_run_information15,'NULL_VALUE')
1783 <> nvl(p_run_information15,'NULL_VALUE')
1784 or nvl(l_run_information16,'NULL_VALUE')
1785 <> nvl(p_run_information16,'NULL_VALUE')
1786 or nvl(l_run_information17,'NULL_VALUE')
1787 <> nvl(p_run_information17,'NULL_VALUE')
1788 or nvl(l_run_information18,'NULL_VALUE')
1789 <> nvl(p_run_information18,'NULL_VALUE')
1790 or nvl(l_run_information19,'NULL_VALUE')
1791 <> nvl(p_run_information19,'NULL_VALUE')
1792 or nvl(l_run_information20,'NULL_VALUE')
1793 <> nvl(p_run_information20,'NULL_VALUE')
1794 or nvl(l_run_information21,'NULL_VALUE')
1795 <> nvl(p_run_information21,'NULL_VALUE')
1796 or nvl(l_run_information22,'NULL_VALUE')
1797 <> nvl(p_run_information22,'NULL_VALUE')
1798 or nvl(l_run_information23,'NULL_VALUE')
1799 <> nvl(p_run_information23,'NULL_VALUE')
1800 or nvl(l_run_information24,'NULL_VALUE')
1801 <> nvl(p_run_information24,'NULL_VALUE')
1802 or nvl(l_run_information25,'NULL_VALUE')
1803 <> nvl(p_run_information25,'NULL_VALUE')
1804 or nvl(l_run_information26,'NULL_VALUE')
1805 <> nvl(p_run_information26,'NULL_VALUE')
1806 or nvl(l_run_information27,'NULL_VALUE')
1807 <> nvl(p_run_information27,'NULL_VALUE')
1808 or nvl(l_run_information28,'NULL_VALUE')
1809 <> nvl(p_run_information28,'NULL_VALUE')
1810 or nvl(l_run_information29,'NULL_VALUE')
1811 <> nvl(p_run_information29,'NULL_VALUE')
1812 or nvl(l_run_information30,'NULL_VALUE')
1813 <> nvl(p_run_information30,'NULL_VALUE')
1814 or l_esd <> p_effective_start_date then
1815 --
1816 zap_insert(l_rt_id);
1817 --
1818 else
1819 --
1820 -- check if just end date has changed
1821 --
1822 if l_eed <> p_effective_end_date then
1823 --
1824 -- don't actually have to update the row, as it is the same
1825 -- as an existing row except for the end date, which will be set
1826 -- using 'SET_END_DATE' later. So cache the values for later use.
1827 --
1828 rec_uploaded.rt_id := l_rt_id;
1829 rec_uploaded.rt_name := l_rt_nm;
1830 rec_uploaded.rt_shortname := l_shrtnm;
1831 rec_uploaded.rt_srs_flag := l_srs_flag;
1832 rec_uploaded.rt_run_information_category :=
1833 l_run_information_category;
1834 rec_uploaded.rt_run_information1 := l_run_information1;
1835 rec_uploaded.rt_run_information2 := l_run_information2;
1836 rec_uploaded.rt_run_information3 := l_run_information3;
1837 rec_uploaded.rt_run_information4 := l_run_information4;
1838 rec_uploaded.rt_run_information5 := l_run_information5;
1839 rec_uploaded.rt_run_information6 := l_run_information6;
1840 rec_uploaded.rt_run_information7 := l_run_information7;
1841 rec_uploaded.rt_run_information8 := l_run_information8;
1842 rec_uploaded.rt_run_information9 := l_run_information9;
1843 rec_uploaded.rt_run_information10 := l_run_information10;
1844 rec_uploaded.rt_run_information11 := l_run_information11;
1845 rec_uploaded.rt_run_information12 := l_run_information12;
1846 rec_uploaded.rt_run_information13 := l_run_information13;
1847 rec_uploaded.rt_run_information14 := l_run_information14;
1851 rec_uploaded.rt_run_information18 := l_run_information18;
1848 rec_uploaded.rt_run_information15 := l_run_information15;
1849 rec_uploaded.rt_run_information16 := l_run_information16;
1850 rec_uploaded.rt_run_information17 := l_run_information17;
1852 rec_uploaded.rt_run_information19 := l_run_information19;
1853 rec_uploaded.rt_run_information20 := l_run_information20;
1854 rec_uploaded.rt_run_information21 := l_run_information21;
1855 rec_uploaded.rt_run_information22 := l_run_information22;
1856 rec_uploaded.rt_run_information23 := l_run_information23;
1857 rec_uploaded.rt_run_information24 := l_run_information24;
1858 rec_uploaded.rt_run_information25 := l_run_information25;
1859 rec_uploaded.rt_run_information26 := l_run_information26;
1860 rec_uploaded.rt_run_information27 := l_run_information27;
1861 rec_uploaded.rt_run_information28 := l_run_information28;
1862 rec_uploaded.rt_run_information29 := l_run_information29;
1863 rec_uploaded.rt_run_information30 := l_run_information30;
1864 rec_uploaded.rt_leg_code := l_lc;
1865 rec_uploaded.rt_bg := l_bg;
1866 rec_uploaded.rt_esd := l_esd;
1867 rec_uploaded.rt_eed := l_eed;
1868 rec_uploaded.rt_ovn := l_ovn;
1869 rec_uploaded.rt_mode := l_mode;
1870 else
1871 l_call_set_end_date := false;
1872 end if;
1873 end if;
1874 --
1875 close g_row_exists;
1876 END IF; -- does row already exist for g_row_exists
1877 end if; -- what mode in
1878 --
1879 ELSE -- p_rt_id is same as g_old_rt_id so same dt row
1880 --
1881 -- update the g_to_be_uploaded_eed value
1882 --
1883 g_to_be_uploaded_eed := p_effective_end_date;
1884 --
1885 -- get row which should still be in uploaded_rec and compare with row
1886 -- being uploaded
1887 --
1888 if rec_uploaded.rt_name <> p_rt_name
1889 or rec_uploaded.rt_shortname <> p_shortname
1890 or rec_uploaded.rt_srs_flag <> p_srs_flag
1891 or rec_uploaded.rt_run_information_category <> p_run_information_category
1892 or rec_uploaded.rt_run_information1 <> rec_uploaded.rt_run_information1
1893 or rec_uploaded.rt_run_information2 <> rec_uploaded.rt_run_information2
1894 or rec_uploaded.rt_run_information3 <> rec_uploaded.rt_run_information3
1895 or rec_uploaded.rt_run_information4 <> rec_uploaded.rt_run_information4
1896 or rec_uploaded.rt_run_information5 <> rec_uploaded.rt_run_information5
1897 or rec_uploaded.rt_run_information6 <> rec_uploaded.rt_run_information6
1898 or rec_uploaded.rt_run_information7 <> rec_uploaded.rt_run_information7
1899 or rec_uploaded.rt_run_information8 <> rec_uploaded.rt_run_information8
1900 or rec_uploaded.rt_run_information9 <> rec_uploaded.rt_run_information9
1901 or rec_uploaded.rt_run_information10 <> rec_uploaded.rt_run_information10
1902 or rec_uploaded.rt_run_information11 <> rec_uploaded.rt_run_information11
1903 or rec_uploaded.rt_run_information12 <> rec_uploaded.rt_run_information12
1904 or rec_uploaded.rt_run_information13 <> rec_uploaded.rt_run_information13
1905 or rec_uploaded.rt_run_information14 <> rec_uploaded.rt_run_information14
1906 or rec_uploaded.rt_run_information15 <> rec_uploaded.rt_run_information15
1907 or rec_uploaded.rt_run_information16 <> rec_uploaded.rt_run_information16
1908 or rec_uploaded.rt_run_information17 <> rec_uploaded.rt_run_information17
1909 or rec_uploaded.rt_run_information18 <> rec_uploaded.rt_run_information18
1910 or rec_uploaded.rt_run_information19 <> rec_uploaded.rt_run_information19
1911 or rec_uploaded.rt_run_information20 <> rec_uploaded.rt_run_information20
1912 or rec_uploaded.rt_run_information21 <> rec_uploaded.rt_run_information21
1913 or rec_uploaded.rt_run_information22 <> rec_uploaded.rt_run_information22
1914 or rec_uploaded.rt_run_information23 <> rec_uploaded.rt_run_information23
1915 or rec_uploaded.rt_run_information24 <> rec_uploaded.rt_run_information24
1916 or rec_uploaded.rt_run_information25 <> rec_uploaded.rt_run_information25
1917 or rec_uploaded.rt_run_information26 <> rec_uploaded.rt_run_information26
1918 or rec_uploaded.rt_run_information27 <> rec_uploaded.rt_run_information27
1919 or rec_uploaded.rt_run_information28 <> rec_uploaded.rt_run_information28
1920 or rec_uploaded.rt_run_information29 <> rec_uploaded.rt_run_information29
1921 or rec_uploaded.rt_run_information30 <> rec_uploaded.rt_run_information30
1922 or rec_uploaded.rt_esd <> p_effective_start_date
1923 or rec_uploaded.rt_eed <> p_effective_end_date then
1924 --
1925 update_row;
1926 end if;
1927 --
1928 END IF; -- p_rt_id same as g_old_rt_id
1929 else -- p_eof_number = 2
1930 --
1931 -- This indicates the final ldt row has been uploaded, just need to check
1932 -- to see if the previous uploaded record's effective end date was eot.
1933 -- If not update the row to whatever the end date should be.
1934 --
1935 if g_to_be_uploaded_eed <> hr_api.g_eot then
1936 set_end_date;
1937 --update_row('SET_END_DATE');
1938 end if;
1939 end if;
1940 --
1941 END up_run_type;
1942 -----------------------------------------------------------------------------
1943 /* Name : up_run_type_usage
1944 Purpose : Uploads the Run Type Usage definition.
1945 Arguments :
1946 Notes :
1947 */
1948 -----------------------------------------------------------------------------
1949 PROCEDURE up_run_type_usage(p_rtu_id number
1950 ,p_parent_run_type_name varchar2
1951 ,p_child_run_type_name varchar2
1952 ,p_child_leg_code varchar2
1953 ,p_child_bg varchar2
1954 ,p_effective_start_date date
1955 ,p_effective_end_date date
1956 ,p_legislation_code varchar2
1957 ,p_business_group varchar2
1961 IS
1958 ,p_sequence number
1959 ,p_rtu_eof_number number
1960 )
1962 --
1963 -- 3 cursors for getting existing row, one for each mode.
1964 --
1965 cursor u_row_exists(p_parent_rt_id number
1966 ,p_child_rt_id number)
1967 is
1968 select run_type_usage_id
1969 , parent_run_type_id
1970 , child_run_type_id
1971 , sequence
1972 , legislation_code
1973 , business_group_id
1974 , effective_start_date
1975 , effective_end_date
1976 , object_version_number
1977 from pay_run_type_usages_f
1978 where parent_run_type_id = p_parent_rt_id
1979 and child_run_type_id = p_child_rt_id
1980 and business_group_id = (select pbg.business_group_id
1981 from per_business_groups pbg
1982 where upper(pbg.name) = p_business_group)
1983 and legislation_code is null;
1984 --
1985 cursor s_row_exists(p_parent_rt_id number
1986 ,p_child_rt_id number)
1987 is
1988 select run_type_usage_id
1989 , parent_run_type_id
1990 , child_run_type_id
1991 , sequence
1992 , legislation_code
1993 , business_group_id
1994 , effective_start_date
1995 , effective_end_date
1996 , object_version_number
1997 from pay_run_type_usages_f
1998 where parent_run_type_id = p_parent_rt_id
1999 and child_run_type_id = p_child_rt_id
2000 and legislation_code = p_legislation_code
2001 and business_group_id is null;
2002 --
2003 cursor g_row_exists(p_parent_rt_id number
2004 ,p_child_rt_id number)
2005 is
2006 select run_type_usage_id
2007 , parent_run_type_id
2008 , child_run_type_id
2009 , sequence
2010 , legislation_code
2011 , business_group_id
2012 , effective_start_date
2013 , effective_end_date
2014 , object_version_number
2015 from pay_run_type_usages_f
2016 where parent_run_type_id = p_parent_rt_id
2017 and child_run_type_id = p_child_rt_id
2018 and business_group_id is null
2019 and legislation_code is null;
2020 --
2021 cursor get_bg_id(p_bg_name varchar2)
2022 is
2023 select business_group_id
2024 from per_business_groups
2025 where UPPER(name) = upper(p_bg_name);
2026 --
2027 cursor get_parent_id (p_bg_id number)
2028 is
2029 select prt.run_type_id
2030 from pay_run_types_f prt
2031 where prt.run_type_name = p_parent_run_type_name
2032 and p_effective_start_date between prt.effective_start_date
2033 and prt.effective_end_date
2034 and ((p_business_group is not null
2035 and prt.business_group_id = p_bg_id)
2036 or (p_legislation_code is not null
2037 and prt.legislation_code = p_legislation_code)
2038 or (p_business_group is null
2039 and p_legislation_code is null
2040 and prt.business_group_id is null
2041 and prt.legislation_code is null));
2042 --
2043 cursor get_child_rt_id(p_bg_id number)
2044 is
2045 select prt.run_type_id
2046 from pay_run_types_f prt
2047 where prt.run_type_name = p_child_run_type_name
2048 and p_effective_start_date between prt.effective_start_date
2049 and prt.effective_end_date
2050 and ((p_child_bg is not null
2051 and prt.business_group_id = p_bg_id)
2052 or (p_child_leg_code is not null
2053 and prt.legislation_code = p_child_leg_code)
2054 or (p_child_bg is null
2055 and p_legislation_code is null
2056 and prt.business_group_id is null
2057 and prt.legislation_code is null));
2058 --
2059 cursor chk_valid_seq(p_par_id number
2060 ,p_sequence_num number
2061 ,p_eff_st_date date)
2062 is
2063 select run_type_usage_id
2064 from pay_run_type_usages_f
2065 where parent_run_type_id = p_par_id
2066 and sequence = p_sequence_num
2067 and p_eff_st_date between effective_start_date
2068 and effective_end_date;
2069 --
2070 l_mode varchar2(30) := 'USER';
2071 l_rtu_id number;
2072 l_par_rt_id number;
2073 l_ch_rt_id number;
2074 l_seq number;
2075 l_esd date;
2076 l_eed date;
2077 l_lc varchar2(30);
2078 l_bg number;
2079 l_ch_bg number;
2080 l_ovn number;
2081 l_out_rtu_id number;
2082 l_out_esd date;
2083 l_out_eed date;
2084 l_out_ovn number;
2085 --
2086 l_valid_seq number := 0;
2087 -------------------------------------------
2088 -- procedure insert_row
2089 -------------------------------------------
2090 procedure insert_row is
2091 --
2092 begin
2093 --
2094 hr_startup_data_api_support.enable_startup_mode(l_mode);
2095 --
2096 if l_mode <> 'USER' then
2097 hr_startup_data_api_support.delete_owner_definitions;
2098 hr_startup_data_api_support.create_owner_definition('PAY');
2099 end if;
2100 --
2101 -- call insert api. End date will be EOT for now, will be updated
2102 -- later if it should be anything other than eot.
2103 --
2104 pay_run_type_usage_api.create_run_type_usage
2105 (p_effective_date => p_effective_start_date
2106 ,p_parent_run_type_id => l_par_rt_id
2107 ,p_child_run_type_id => l_ch_rt_id
2111 ,p_run_type_usage_id => l_out_rtu_id
2108 ,p_sequence => p_sequence
2109 ,p_business_group_id => l_bg
2110 ,p_legislation_code => p_legislation_code
2112 ,p_effective_start_date => l_out_esd
2113 ,p_effective_end_date => l_out_eed
2114 ,p_object_version_number => l_out_ovn
2115 );
2116 --
2117 -- cache the new details
2118 --
2119 select run_type_usage_id
2120 , parent_run_type_id
2121 , child_run_type_id
2122 , sequence
2123 , legislation_code
2124 , business_group_id
2125 , effective_start_date
2126 , effective_end_date
2127 , object_version_number
2128 , l_mode
2129 into rec_rtu_uploaded
2130 from pay_run_type_usages_f
2131 where run_type_usage_id = l_out_rtu_id;
2132 --
2133 end insert_row;
2134 -------------------------------------------
2135 -- procedure zap_insert
2136 --
2137 -- zap_insert indicates that the row being uploaded already exists on the
2138 -- new db, but that it is a new datetracked row from the ldt, i.e. it is the
2139 -- first time this row has been updated. There may well be further
2140 -- datetracked rows for this row (handled by update_row), so the effective end
2141 -- date is left as eot.
2142 --
2143 -------------------------------------------
2144 procedure zap_insert(p_rtu_id number) is
2145 --
2146 begin
2147 --
2148 hr_startup_data_api_support.enable_startup_mode(l_mode);
2149 --
2150 if l_mode <> 'USER' then
2151 hr_startup_data_api_support.delete_owner_definitions;
2152 hr_startup_data_api_support.create_owner_definition('PAY');
2153 end if;
2154 --
2155 -- in order to update an existing row, need to purge the existing row
2156 -- then insert a new row.
2157 --
2158 hr_utility.trace('l_rtu_id: '||to_char(l_rtu_id));
2159 hr_utility.trace('eff start date: '||to_char(p_effective_start_date,'dd-mon-yyyy'));
2160 --
2161 -- use delete rather than api delete to bypass validation
2162 --
2163 delete from pay_run_type_usages_f
2164 where run_type_usage_id = p_rtu_id;
2165 --
2166 if l_valid_seq <> 0 then
2167 --
2168 delete from pay_run_type_usages_f
2169 where run_type_usage_id = l_valid_seq;
2170 end if;
2171 --
2172 pay_run_type_usage_api.create_run_type_usage
2173 (p_effective_date => p_effective_start_date
2174 ,p_parent_run_type_id => l_par_rt_id
2175 ,p_child_run_type_id => l_ch_rt_id
2176 ,p_sequence => p_sequence
2177 ,p_business_group_id => l_bg
2178 ,p_legislation_code => p_legislation_code
2179 ,p_run_type_usage_id => l_out_rtu_id
2180 ,p_effective_start_date => l_out_esd
2181 ,p_effective_end_date => l_out_eed
2182 ,p_object_version_number => l_out_ovn
2183 );
2184 --
2185 hr_utility.trace('AFTER ZAP CREATE');
2186 --
2187 -- set the uploaded cache
2188 --
2189 select run_type_usage_id
2190 , parent_run_type_id
2191 , child_run_type_id
2192 , sequence
2193 , legislation_code
2194 , business_group_id
2195 , effective_start_date
2196 , effective_end_date
2197 , object_version_number
2198 , l_mode
2199 into rec_rtu_uploaded
2200 from pay_run_type_usages_f
2201 where run_type_usage_id = l_out_rtu_id;
2202 --
2203 end zap_insert;
2204 -------------------------------------------
2205 -- procedure update_row
2206 --
2207 -- If multiple datatrack rows exist in the ldt for one particular row, multiple
2208 -- updates will have to be done achieve the dt history. The effective_end_date
2209 -- is left at eot. If eot is not the final end date of the dt row it will be
2210 -- set to the correct date by set_end_date.
2211 --
2212 -------------------------------------------
2213 procedure update_row is
2214 --
2215 begin
2216 --
2217 hr_startup_data_api_support.enable_startup_mode(l_mode);
2218 --
2219 if l_mode <> 'USER' then
2220 hr_startup_data_api_support.delete_owner_definitions;
2221 hr_startup_data_api_support.create_owner_definition('PAY');
2222 end if;
2223 --
2224 pay_run_type_usage_api.update_run_type_usage
2225 (p_effective_date => p_effective_start_date
2226 ,p_datetrack_update_mode => 'UPDATE'
2227 ,p_run_type_usage_id => rec_rtu_uploaded.rtu_id
2228 ,p_object_version_number => rec_rtu_uploaded.rtu_ovn
2229 ,p_sequence => p_sequence
2230 ,p_business_group_id => l_bg
2231 ,p_legislation_code => p_legislation_code
2232 ,p_effective_start_date => l_out_esd
2233 ,p_effective_end_date => l_out_eed
2234 );
2235 --
2236 -- cache the lastest uploaded row
2237 --
2238 select run_type_usage_id
2239 , parent_run_type_id
2240 , child_run_type_id
2241 , sequence
2242 , legislation_code
2243 , business_group_id
2244 , effective_start_date
2245 , effective_end_date
2246 , object_version_number
2247 , l_mode
2248 into rec_rtu_uploaded
2249 from pay_run_type_usages_f
2250 where run_type_usage_id = rec_rtu_uploaded.rtu_id
2251 and effective_start_date = l_out_esd
2252 and effective_end_date = l_out_eed;
2253 --
2254 end update_row;
2255 -------------------------------------------
2259 -- that are not set to the end of time.
2256 -- procedure SET_END_DATE
2257 --
2258 -- SET_END_DATE is used to set the end date of updated or newly inserted rows
2260 --
2261 -------------------------------------------
2262 procedure set_end_date is
2263 --
2264 begin
2265 --
2266 hr_startup_data_api_support.enable_startup_mode(l_mode);
2267 --
2268 if l_mode <> 'USER' then
2269 hr_startup_data_api_support.delete_owner_definitions;
2270 hr_startup_data_api_support.create_owner_definition('PAY');
2271 end if;
2272 --
2273 -- delete the run type usage
2274 --
2275 pay_run_type_usage_api.delete_run_type_usage
2276 (p_effective_date => g_rtu_to_be_uploaded_eed
2277 ,p_datetrack_delete_mode => 'DELETE'
2278 ,p_run_type_usage_id => rec_rtu_uploaded.rtu_id
2279 ,p_object_version_number => rec_rtu_uploaded.rtu_ovn
2280 ,p_business_group_id => rec_rtu_uploaded.rtu_bg
2281 ,p_legislation_code => rec_rtu_uploaded.rtu_leg_code
2282 ,p_effective_start_date => l_out_esd
2283 ,p_effective_end_date => l_out_eed
2284 );
2285 --
2286 end set_end_date;
2287 --
2288 BEGIN -- up_run_type_usage
2289 --
2290 -- Set the mode
2291 --
2292 If p_business_group IS NOT NULL then
2293 if p_legislation_code IS NULL THEN
2294 l_mode := 'USER';
2295 --
2296 -- get the bg id
2297 --
2298 open get_bg_id(p_business_group);
2299 fetch get_bg_id into l_bg;
2300 close get_bg_id;
2301 else
2302 -- raise error cannot have leg and bg populated
2303 null;
2304 end if;
2305 else -- bg is null
2306 if p_legislation_code is NOT NULL then
2307 l_mode := 'STARTUP';
2308 else
2309 l_mode := 'GENERIC';
2310 end if;
2311 end if;
2312 --
2313 -- Get the parent_run_type_id and child_run_type_id for the names that have
2314 -- been passed through.
2315 --
2316 OPEN get_parent_id(l_bg);
2317 FETCH get_parent_id into l_par_rt_id;
2318 CLOSE get_parent_id;
2319 hr_utility.trace('l_par_rt_id: '||to_char(l_par_rt_id));
2320 --
2321 OPEN get_bg_id(p_child_bg);
2322 FETCH get_bg_id into l_ch_bg;
2323 CLOSE get_bg_id;
2324 hr_utility.trace('l_ch_bg: '||to_char(l_ch_bg));
2325 --
2326 OPEN get_child_rt_id(l_ch_bg);
2327 FETCH get_child_rt_id into l_ch_rt_id;
2328 CLOSE get_child_rt_id;
2329 hr_utility.trace('l_ch_rt_id: '||to_char(l_ch_rt_id));
2330 --
2331 -- Is the new row the end of file row (rtu_eof_number = 2) ?
2332 -- Is the new row part of the same dt record as the last uploaded row?
2333 --
2334 IF p_rtu_eof_number = 1 then
2335 IF p_rtu_id <> g_old_rtu_id then
2336 --
2337 -- this is a new upload row. Check to see if the previous uploaded record's
2338 -- effective end date was eot. If not update the row to whatever the end
2339 -- date should be.
2340 --
2341 if g_rtu_to_be_uploaded_eed <> hr_api.g_eot then
2342 --
2343 if l_call_rtu_set_end_date then
2344 hr_utility.trace('before SET_END_DATE');
2345 set_end_date;
2346 hr_utility.trace('after SET_END_DATE');
2347 else
2348 null;
2349 end if;
2350 else
2351 -- clear down prev_upload_rec;
2352 null;
2353 end if;
2354 --
2355 -- Cache the row to be upload
2356 --
2357 g_rtu_to_be_uploaded_eed := p_effective_end_date;
2358 --
2359 g_old_rtu_id := p_rtu_id;
2360 --
2361 -- clear down the uploaded row, as now on new row
2362 --
2363 rec_rtu_uploaded.rtu_id := '';
2364 rec_rtu_uploaded.rtu_parent_rt_id := '';
2365 rec_rtu_uploaded.rtu_child_rt_id := '';
2366 rec_rtu_uploaded.rtu_sequence := '';
2367 rec_rtu_uploaded.rtu_leg_code := '';
2368 rec_rtu_uploaded.rtu_bg := '';
2369 rec_rtu_uploaded.rtu_esd := '';
2370 rec_rtu_uploaded.rtu_eed := '';
2371 rec_rtu_uploaded.rtu_ovn := '';
2372 rec_rtu_uploaded.rtu_mode := '';
2373 --
2374 -- Does the same row already exist on the db?
2375 --
2376 if l_mode = 'USER' then
2377 open u_row_exists(l_par_rt_id, l_ch_rt_id);
2378 fetch u_row_exists into l_rtu_id, l_par_rt_id, l_ch_rt_id, l_seq
2379 , l_lc, l_bg, l_esd, l_eed, l_ovn;
2380 IF u_row_exists%NOTFOUND then
2381 close u_row_exists;
2382 insert_row;
2383 ELSE -- this row does already exist
2384 --
2385 -- see if any changes have been made
2386 --
2387 if l_seq <> p_sequence
2388 or l_esd <> p_effective_start_date then
2389 --
2390 zap_insert(l_rtu_id);
2391 --
2392 else
2393 --
2394 -- check if just end date has changed
2395 --
2396 if l_eed <> p_effective_end_date then
2397 --
2398 -- don't actually have to update the row, as it is the same
2399 -- as an existing row except for the end date, which will be set
2400 -- using 'SET_END_DATE' later. So cache the values for later use.
2401 --
2402 rec_rtu_uploaded.rtu_id := l_rtu_id;
2403 rec_rtu_uploaded.rtu_parent_rt_id := l_par_rt_id;
2404 rec_rtu_uploaded.rtu_child_rt_id := l_ch_rt_id;
2405 rec_rtu_uploaded.rtu_leg_code := l_lc;
2406 rec_rtu_uploaded.rtu_bg := l_bg;
2407 rec_rtu_uploaded.rtu_esd := l_esd;
2408 rec_rtu_uploaded.rtu_eed := l_eed;
2409 rec_rtu_uploaded.rtu_ovn := l_ovn;
2410 rec_rtu_uploaded.rtu_mode := l_mode;
2411 else
2412 --
2413 -- Row already exists and no columns have changed, so set_end_date
2414 -- does not need to be called. Set a flag to indicate this.
2415 --
2416 l_call_rtu_set_end_date := false;
2417 end if;
2418 end if;
2419 --
2420 close u_row_exists;
2421 END IF; -- does row already exist for u_row_exists
2422 --
2423 elsif l_mode = 'STARTUP' then
2424 open s_row_exists(l_par_rt_id, l_ch_rt_id);
2425 fetch s_row_exists into l_rtu_id, l_par_rt_id, l_ch_rt_id, l_seq
2426 , l_lc, l_bg, l_esd, l_eed, l_ovn;
2427 IF s_row_exists%NOTFOUND then
2428 hr_utility.trace('this row does not exist');
2429 close s_row_exists;
2430 insert_row;
2431 ELSE -- this row does already exist
2432 hr_utility.trace('this row does exist');
2433 --
2434 -- see if any changes have been made
2435 --
2436 if l_seq <> p_sequence
2437 or l_esd <> p_effective_start_date then
2438 --
2439 if l_seq <> p_sequence then
2440 --
2441 -- check to see if there exists a row with this sequence and this
2442 -- parent id. If there is then get the rtu_id and also zap this row,
2443 -- so that the new row can be inserted.
2444 --
2445 open chk_valid_seq(l_par_rt_id, p_sequence, p_effective_start_date);
2446 fetch chk_valid_seq into l_valid_seq;
2447 if chk_valid_seq%FOUND then
2448 -- this sequence/parent_id exists, so store the rtu_id for zapping
2449 close chk_valid_seq;
2450 else
2451 -- sequence/parent_id does not exist so not extra zap to do,
2452 -- continue with the insert
2453 null;
2454 end if;
2455 --
2456 end if;
2457 hr_utility.trace('this row does exist - before zap');
2458 zap_insert(l_rtu_id);
2459 else
2460 --
2461 -- check if just end date has changed
2462 --
2463 if l_eed <> p_effective_end_date then
2464 hr_utility.trace('this row does exist - eed changed');
2465 --
2466 --
2467 -- don't actually have to update the row, as it is the same
2468 -- as an existing row except for the end date, which will be set
2469 -- using 'SET_END_DATE' later. So cache the values for later use.
2470 --
2471 rec_rtu_uploaded.rtu_id := l_rtu_id;
2472 rec_rtu_uploaded.rtu_parent_rt_id := l_par_rt_id;
2473 rec_rtu_uploaded.rtu_child_rt_id := l_ch_rt_id;
2474 rec_rtu_uploaded.rtu_sequence := l_seq;
2475 rec_rtu_uploaded.rtu_leg_code := l_lc;
2476 rec_rtu_uploaded.rtu_bg := l_bg;
2477 rec_rtu_uploaded.rtu_esd := l_esd;
2478 rec_rtu_uploaded.rtu_eed := l_eed;
2479 rec_rtu_uploaded.rtu_ovn := l_ovn;
2480 rec_rtu_uploaded.rtu_mode := l_mode;
2481 else
2482 --
2483 -- Row already exists and no columns have changed, so set_end_date
2484 -- does not need to be called. Set a flag to indicate this.
2485 --
2486 hr_utility.trace('row exists - no changes');
2487 l_call_rtu_set_end_date := false;
2488 end if;
2489 end if;
2490 --
2491 close s_row_exists;
2492 END IF; -- does row already exist for u_row_exists
2493 --
2494 else -- l_mode = GENERIC
2495 open g_row_exists(l_par_rt_id, l_ch_rt_id);
2496 fetch g_row_exists into l_rtu_id, l_par_rt_id, l_ch_rt_id, l_seq
2497 , l_lc, l_bg, l_esd, l_eed, l_ovn;
2498 IF g_row_exists%NOTFOUND then
2499 close g_row_exists;
2500 insert_row;
2501 ELSE -- this row does already exist
2502 --
2503 -- see if any changes have been made
2504 --
2505 if l_seq <> p_sequence
2506 or l_esd <> p_effective_start_date then
2507 --
2508 zap_insert(l_rtu_id);
2509 else
2510 --
2511 -- check if just end date has changed
2512 --
2513 if l_eed <> p_effective_end_date then
2514 --
2515 -- don't actually have to update the row, as it is the same
2516 -- as an existing row except for the end date, which will be set
2517 -- using 'SET_END_DATE' later. So cache the values for later use.
2518 --
2519 rec_rtu_uploaded.rtu_id := l_rtu_id;
2520 rec_rtu_uploaded.rtu_parent_rt_id := l_par_rt_id;
2521 rec_rtu_uploaded.rtu_child_rt_id := l_ch_rt_id;
2522 rec_rtu_uploaded.rtu_sequence := l_seq;
2523 rec_rtu_uploaded.rtu_leg_code := l_lc;
2524 rec_rtu_uploaded.rtu_bg := l_bg;
2525 rec_rtu_uploaded.rtu_esd := l_esd;
2526 rec_rtu_uploaded.rtu_eed := l_eed;
2527 rec_rtu_uploaded.rtu_ovn := l_ovn;
2528 rec_rtu_uploaded.rtu_mode := l_mode;
2529 else
2530 --
2531 -- Row already exists and no columns have changed, so set_end_date
2532 -- does not need to be called. Set a flag to indicate this.
2533 --
2534 l_call_rtu_set_end_date := false;
2535 end if;
2536 end if;
2537 --
2538 close g_row_exists;
2539 END IF; -- does row already exist for g_row_exists
2540 end if; -- what mode in
2541 --
2542 ELSE -- p_rtu_id is same as g_old_rtu_id so same dt row
2543 --
2544 -- update the g_rtu_to_be_uploaded_eed value
2545 --
2546 g_rtu_to_be_uploaded_eed := p_effective_end_date;
2547 --
2548 -- get row which should still be in uploaded_rec and compare with row
2549 -- being uploaded
2550 --
2551 if rec_rtu_uploaded.rtu_parent_rt_id <> l_par_rt_id
2552 or rec_rtu_uploaded.rtu_child_rt_id <>l_ch_rt_id
2553 or rec_rtu_uploaded.rtu_esd <> p_effective_start_date
2554 or rec_rtu_uploaded.rtu_eed <> p_effective_end_date then
2555 --
2556 update_row;
2557 end if;
2558 --
2559 END IF; -- p_rtu_id same as g_old_rtu_id
2560 else -- p_eof_number = 2
2561 --
2562 -- This indicates the final ldt row has been uploaded, just need to check
2563 -- to see if the previous uploaded record's effective end date was eot.
2564 -- If not update the row to whatever the end date should be.
2565 --
2566 if g_rtu_to_be_uploaded_eed <> hr_api.g_eot then
2567 set_end_date;
2568 end if;
2569 end if;
2570 --
2571 END up_run_type_usage;
2572 -----------------------------------------------------------------------------
2573 -- PROCEDURE translate_row
2574 -----------------------------------------------------------------------------
2575 PROCEDURE translate_row(p_base_rt_name varchar2
2576 ,p_rt_leg_code varchar2
2577 ,p_rt_bg varchar2
2578 ,p_rt_name_tl varchar2
2579 ,p_shortname_tl varchar2
2580 )
2581 is
2582 --
2583 cursor get_business_group_id
2584 is
2585 select business_group_id
2586 from per_business_groups
2587 where upper(name) = p_rt_bg;
2588 --
2589 cursor get_run_type_id(p_bg_id number)
2590 is
2591 select run_type_id
2592 from pay_run_types_f
2593 where run_type_name = p_base_rt_name
2594 and nvl(business_group_id, -1) = nvl(p_bg_id, -1)
2595 and nvl(legislation_code, 'CORE') = nvl(p_rt_leg_code, 'CORE');
2596 --
2597 l_bg_id number;
2598 l_rt_id number;
2599 --
2600 BEGIN
2601 if p_rt_bg is not null then
2602 open get_business_group_id;
2603 fetch get_business_group_id into l_bg_id;
2604 close get_business_group_id;
2605 end if;
2606 --
2607 open get_run_type_id(l_bg_id);
2608 fetch get_run_type_id into l_rt_id;
2609 close get_run_type_id;
2610 hr_utility.trace('rt id is: '||to_char(l_rt_id));
2611 --
2612 pay_rtt_upd.upd_tl(p_language_code => userenv('LANG')
2613 ,p_run_type_id => l_rt_id
2614 ,p_run_type_name => p_rt_name_tl
2615 ,p_shortname => p_shortname_tl
2616 );
2617 END translate_row;
2618 -----------------------------------------------------------------------------
2619 /* Name : up_run_type_org_method
2620 Purpose : Uploads the Organisation Payment Method for Run Type.
2621 Arguments :
2622 Notes :
2623 */
2624 procedure up_run_type_org_method (
2625 p_rt_opm_id number,
2626 p_rt_name varchar2,
2627 p_opm_name varchar2,
2628 p_effective_start_date date,
2629 p_effective_end_date date,
2630 p_priority number,
2631 p_percentage number,
2632 p_amount number,
2633 p_business_group varchar2,
2634 p_rt_bg varchar2,
2635 p_rt_lc varchar2,
2636 p_eof_number number
2637 )
2638 is
2639 --
2640 l_bg_id number;
2641 l_lc varchar2(30);
2642 l_rt_id number;
2643 l_opm_id number;
2644 --
2645 l_rom_id number;
2646 l_ovn number;
2647 l_esd date;
2648 l_eed date;
2649 l_out_rom_id number;
2650 l_out_ovn number;
2651 l_out_esd date;
2652 l_out_eed date;
2653 --
2654 l_error EXCEPTION;
2655 --
2656 cursor c_row_exists is
2657 select run_type_org_method_id
2658 , object_version_number
2659 , effective_start_date
2660 , effective_end_date
2661 from pay_run_type_org_methods_f
2662 where run_type_id = l_rt_id
2663 and org_payment_method_id = l_opm_id
2664 and business_group_id = l_bg_id;
2665 --
2666 procedure zap_insert_rom is
2667 --
2668 begin
2669 --
2670 hr_startup_data_api_support.enable_startup_mode('USER');
2671 --
2672 pay_run_type_org_method_api.delete_run_type_org_method(
2673 p_effective_date => p_effective_start_date
2674 ,p_datetrack_delete_mode => 'ZAP'
2675 ,p_run_type_org_method_id => l_rom_id
2676 ,p_object_version_number => l_ovn
2677 ,p_effective_start_date => l_esd
2678 ,p_effective_end_date => l_eed);
2679 --
2680 hr_startup_data_api_support.enable_startup_mode('USER');
2681 --
2682 pay_run_type_org_method_api.create_run_type_org_method(
2683 p_effective_date => p_effective_start_date
2684 ,p_run_type_id => l_rt_id
2685 ,p_org_payment_method_id => l_opm_id
2686 ,p_priority => p_priority
2687 ,p_percentage => p_percentage
2688 ,p_amount => p_amount
2689 ,p_business_group_id => l_bg_id
2690 ,p_run_type_org_method_id => l_out_rom_id
2691 ,p_object_version_number => l_out_ovn
2692 ,p_effective_start_date => l_out_esd
2693 ,p_effective_end_date => l_out_eed);
2694 --
2695 end;
2696 --
2697 procedure insert_rom is
2698 --
2699 begin
2700 --
2701 hr_startup_data_api_support.enable_startup_mode('USER');
2702 --
2703 pay_run_type_org_method_api.create_run_type_org_method(
2704 p_effective_date => p_effective_start_date
2705 ,p_run_type_id => l_rt_id
2706 ,p_org_payment_method_id => l_opm_id
2707 ,p_priority => p_priority
2708 ,p_percentage => p_percentage
2709 ,p_amount => p_amount
2710 ,p_business_group_id => l_bg_id
2711 ,p_run_type_org_method_id => l_out_rom_id
2712 ,p_object_version_number => l_out_ovn
2713 ,p_effective_start_date => l_out_esd
2714 ,p_effective_end_date => l_out_eed);
2715 --
2716 end;
2717 --
2718 procedure update_rom is
2719 --
2720 begin
2721 --
2722 hr_startup_data_api_support.enable_startup_mode('USER');
2723 --
2724 pay_run_type_org_method_api.update_run_type_org_method(
2725 p_effective_date => p_effective_start_date
2726 ,p_datetrack_update_mode => 'UPDATE'
2727 ,p_run_type_org_method_id => g_rom_rec.new_rom_id
2728 ,p_object_version_number => g_rom_rec.ovn
2729 ,p_priority => p_priority
2730 ,p_percentage => p_percentage
2731 ,p_amount => p_amount
2732 ,p_business_group_id => l_bg_id
2733 ,p_effective_start_date => l_out_esd
2734 ,p_effective_end_date => l_out_eed);
2735 --
2736 end;
2737 --
2738 procedure end_date_rom is
2739 --
2740 begin
2741 --
2742 hr_startup_data_api_support.enable_startup_mode('USER');
2743 --
2744 pay_run_type_org_method_api.delete_run_type_org_method(
2745 p_effective_date => g_rom_rec.old_eed
2746 ,p_datetrack_delete_mode => 'DELETE'
2747 ,p_run_type_org_method_id => g_rom_rec.new_rom_id
2748 ,p_object_version_number => g_rom_rec.ovn
2749 ,p_effective_start_date => g_rom_rec.new_esd
2750 ,p_effective_end_date => g_rom_rec.new_eed);
2751 --
2752 end;
2753 --
2754 Begin -- <Main Begin>
2755 --
2756 if p_eof_number = 1 then -- if this is not the last row to be uploaded
2757 --
2758 hr_utility.set_location('pay_iterate.up_run_type_org_method',10);
2759 --
2760 -- get the business group id
2761 --
2762 select business_group_id
2763 , legislation_code
2764 into l_bg_id
2765 , l_lc
2766 from per_business_groups
2767 where UPPER(name) = p_business_group;
2768 --
2769 hr_utility.set_location('pay_iterate.up_run_type_org_method',20);
2770 --
2771 -- Get the run type id
2772 --
2773 select prt.run_type_id
2774 into l_rt_id
2775 from pay_run_types_f prt
2776 where UPPER(prt.run_type_name) = p_rt_name
2777 and p_effective_start_date between prt.effective_start_date
2778 and prt.effective_end_date
2779 and ((p_rt_bg is not null
2780 and prt.business_group_id = l_bg_id)
2781 or (p_rt_lc is not null
2782 and prt.legislation_code = p_rt_lc)
2783 or (p_rt_bg is null
2784 and p_rt_lc is null
2785 and prt.business_group_id is null
2786 and prt.legislation_code is null));
2787 --
2788 hr_utility.set_location('pay_iterate.up_run_type_org_method',30);
2789 --
2790 -- get the org payment method id
2791 --
2792 select popm.org_payment_method_id
2793 into l_opm_id
2794 from pay_org_payment_methods_f popm
2795 where UPPER(popm.org_payment_method_name) = p_opm_name
2796 and p_effective_start_date between popm.effective_start_date
2797 and popm.effective_end_date
2798 and popm.business_group_id = l_bg_id;
2799 --
2800 hr_utility.set_location('pay_iterate.up_run_type_org_method',40);
2801 --
2802 open c_row_exists;
2803 fetch c_row_exists into l_rom_id, l_ovn, l_esd, l_eed;
2804 --
2805 if c_row_exists%found then
2806 --
2807 if (l_rom_id <> g_rom_rec.new_rom_id) then
2808 zap_insert_rom;
2809 elsif (p_rt_opm_id = g_rom_rec.old_rom_id) then
2810 update_rom;
2811 else
2812 raise l_error;
2813 end if;
2814 --
2815 else
2816 insert_rom;
2817 end if;
2818 --
2819 close c_row_exists;
2820 --
2821 if ((p_rt_opm_id <> g_rom_rec.old_rom_id)
2822 and (g_rom_rec.old_eed <> hr_api.g_eot)) then
2823 end_date_rom;
2824 end if;
2825 --
2826 g_rom_rec.old_rom_id := p_rt_opm_id;
2827 g_rom_rec.new_rom_id := l_out_rom_id;
2828 g_rom_rec.ovn := l_out_ovn;
2829 g_rom_rec.old_esd := p_effective_start_date;
2830 g_rom_rec.new_esd := l_out_esd;
2831 g_rom_rec.old_eed := p_effective_end_date;
2832 g_rom_rec.new_eed := l_out_eed;
2833 --
2834 hr_utility.set_location('pay_iterate.up_run_type_org_method',70);
2835 --
2836 else
2837 if g_rom_rec.old_eed <> hr_api.g_eot then
2838 end_date_rom;
2839 end if;
2840 end if;
2841 --
2842 EXCEPTION
2843 --
2844 WHEN l_error THEN
2845 hr_utility.set_message(801, 'HR_33700_LEG_USER_ROW_EXISTS');
2846 hr_utility.raise_error;
2847 --
2848 end up_run_type_org_method;
2849 --
2850 /* Name : up_element_type_usage
2851 Purpose : Uploads the Element Type Usage.
2852 Arguments :
2853 Notes :
2854 */
2855 procedure up_element_type_usage (
2856 p_etu_id number,
2857 p_rt_name varchar2,
2858 p_element_name varchar2,
2859 p_effective_start_date date,
2860 p_effective_end_date date,
2861 p_business_group varchar2,
2862 p_legislative_code varchar2,
2863 p_rt_bg_name varchar2,
2864 p_rt_leg_code varchar2,
2868 p_usage_type varchar2,
2865 p_et_bg_name varchar2,
2866 p_et_leg_code varchar2,
2867 p_inclusion_flag varchar2,
2869 p_eof_number number
2870 ) is
2871 --
2872 l_mode varchar2(30) := 'USER';
2873 --
2874 l_etu_id number;
2875 l_ovn number;
2876 l_lc varchar2(30);
2877 l_bg_id number;
2878 l_esd date;
2879 l_eed date;
2880 --
2881 l_in_rt_id number;
2882 l_in_et_id number;
2883 l_in_bg_id number;
2884 l_in_lc varchar2(30);
2885 --
2886 l_out_etu_id number;
2887 l_out_ovn number;
2888 l_out_esd date;
2889 l_out_eed date;
2890 --
2891 cursor c_row_exists_user is
2892 select element_type_usage_id
2893 , object_version_number
2894 , legislation_code
2895 , business_group_id
2896 , effective_start_date
2897 , effective_end_date
2898 from pay_element_type_usages_f
2899 where run_type_id = l_in_rt_id
2900 and element_type_id = l_in_et_id
2901 and ((business_group_id = l_in_bg_id)
2902 or (legislation_code = l_in_lc)
2903 or (business_group_id is null
2904 and legislation_code is null));
2905 --
2906 cursor c_row_exists_startup is
2907 select element_type_usage_id
2908 , object_version_number
2909 , legislation_code
2910 , business_group_id
2911 , effective_start_date
2912 , effective_end_date
2913 from pay_element_type_usages_f
2914 where run_type_id = l_in_rt_id
2915 and element_type_id = l_in_et_id
2916 and ((business_group_id in (select business_group_id
2917 from per_business_groups
2918 where legislation_code = p_legislative_code))
2919 or (legislation_code = p_legislative_code)
2920 or (business_group_id is null
2921 and legislation_code is null));
2922 --
2923 cursor c_row_exists_generic is
2924 select element_type_usage_id
2925 , object_version_number
2926 , legislation_code
2927 , business_group_id
2928 , effective_start_date
2929 , effective_end_date
2930 from pay_element_type_usages_f
2931 where run_type_id = l_in_rt_id
2932 and element_type_id = l_in_et_id;
2933 --
2934 procedure insert_etu is
2935 --
2936 begin
2937 --
2938 hr_startup_data_api_support.enable_startup_mode(l_mode);
2939 --
2940 if l_mode <> 'USER' then
2941 hr_startup_data_api_support.delete_owner_definitions;
2942 hr_startup_data_api_support.create_owner_definition('PAY');
2943 end if;
2944 --
2945 pay_element_type_usage_api.create_element_type_usage(
2946 p_effective_date => p_effective_start_date
2947 ,p_run_type_id => l_in_rt_id
2948 ,p_element_type_id => l_in_et_id
2949 ,p_inclusion_flag => p_inclusion_flag
2950 ,p_business_group_id => l_in_bg_id
2951 ,p_legislation_code => p_legislative_code
2952 ,p_usage_type => p_usage_type
2953 ,p_element_type_usage_id => l_out_etu_id
2954 ,p_object_version_number => l_out_ovn
2955 ,p_effective_start_date => l_out_esd
2956 ,p_effective_end_date => l_out_eed);
2957 --
2958 end;
2959 --
2960 procedure end_date_etu is
2961 --
2962 begin
2963 --
2964 hr_startup_data_api_support.enable_startup_mode(g_etu_rec.l_mode);
2965 --
2966 if l_mode <> 'USER' then
2967 hr_startup_data_api_support.delete_owner_definitions;
2968 hr_startup_data_api_support.create_owner_definition('PAY');
2969 end if;
2970 --
2971 pay_element_type_usage_api.delete_element_type_usage(
2972 p_effective_date => g_etu_rec.old_eed
2973 ,p_datetrack_delete_mode => 'DELETE'
2974 ,p_element_type_usage_id => g_etu_rec.new_etu_id
2975 ,p_object_version_number => g_etu_rec.ovn
2976 ,p_effective_start_date => g_etu_rec.new_esd
2977 ,p_effective_end_date => g_etu_rec.new_eed);
2978 --
2979 end;
2980 --
2981 procedure zap_insert_etu is
2982 --
2983 begin
2984 --
2985 hr_startup_data_api_support.enable_startup_mode(l_mode);
2986 --
2987 if l_mode <> 'USER' then
2991 --
2988 hr_startup_data_api_support.delete_owner_definitions;
2989 hr_startup_data_api_support.create_owner_definition('PAY');
2990 end if;
2992 pay_element_type_usage_api.delete_element_type_usage(
2993 p_effective_date => p_effective_start_date
2994 ,p_datetrack_delete_mode => 'ZAP'
2995 ,p_element_type_usage_id => l_etu_id
2996 ,p_object_version_number => l_ovn
2997 ,p_effective_start_date => l_esd
2998 ,p_effective_end_date => l_eed);
2999 --
3000 hr_startup_data_api_support.enable_startup_mode(l_mode);
3001 --
3002 if l_mode <> 'USER' then
3003 hr_startup_data_api_support.delete_owner_definitions;
3004 hr_startup_data_api_support.create_owner_definition('PAY');
3005 end if;
3006 --
3007 pay_element_type_usage_api.create_element_type_usage(
3008 p_effective_date => p_effective_start_date
3009 ,p_run_type_id => l_in_rt_id
3010 ,p_element_type_id => l_in_et_id
3011 ,p_inclusion_flag => p_inclusion_flag
3012 ,p_business_group_id => l_in_bg_id
3013 ,p_legislation_code => p_legislative_code
3014 ,p_usage_type => p_usage_type
3015 ,p_element_type_usage_id => l_out_etu_id
3016 ,p_object_version_number => l_out_ovn
3017 ,p_effective_start_date => l_out_esd
3018 ,p_effective_end_date => l_out_eed);
3019 --
3020 end;
3021 --
3022 procedure update_etu is
3023 --
3024 begin
3025 --
3026 hr_startup_data_api_support.enable_startup_mode(l_mode);
3027 --
3028 if l_mode <> 'USER' then
3029 hr_startup_data_api_support.delete_owner_definitions;
3030 hr_startup_data_api_support.create_owner_definition('PAY');
3031 end if;
3032 --
3033 pay_element_type_usage_api.update_element_type_usage(
3034 p_effective_date => p_effective_start_date
3035 ,p_datetrack_update_mode => 'UPDATE'
3036 ,p_inclusion_flag => p_inclusion_flag
3037 ,p_element_type_usage_id => l_etu_id
3038 ,p_object_version_number => l_ovn
3039 ,p_business_group_id => l_in_bg_id
3040 ,p_legislation_code => p_legislative_code
3041 ,p_usage_type => p_usage_type
3042 ,p_effective_start_date => l_out_esd
3043 ,p_effective_end_date => l_out_eed);
3044 --
3045 end;
3046 --
3047 procedure exists_error is
3048 --
3049 begin
3050 --
3051 if l_mode = 'STARTUP' then
3052 --
3053 hr_utility.set_message(801, 'HR_33699_USER_ROW_EXISTS');
3054 hr_utility.raise_error;
3055 --
3056 else
3057 --
3058 hr_utility.set_message(801, 'HR_33700_LEG_USER_ROW_EXISTS');
3059 hr_utility.raise_error;
3060 --
3061 end if;
3062 --
3063 end exists_error;
3064 --
3065 Begin -- <Main Begin>
3066 --
3067 if p_eof_number = 1 then
3068 --
3069 -- Set the mode in which the api will be called.
3070 --
3071 if p_business_group IS NOT NULL then
3072 l_mode := 'USER';
3073 elsif p_legislative_code IS NOT NULL then
3074 l_mode := 'STARTUP';
3075 else
3076 l_mode := 'GENERIC';
3077 end if;
3078 --
3079 -- Get the business group id
3080 --
3081 if p_business_group is not null then
3082 --
3083 select business_group_id
3084 , legislation_code
3085 into l_in_bg_id
3086 , l_in_lc
3087 from per_business_groups
3088 where UPPER(name) = p_business_group;
3089 --
3090 end if;
3091 --
3092 -- Get the run_type_id
3093 --
3094 select prt.run_type_id
3095 into l_in_rt_id
3096 from pay_run_types_f prt
3097 where UPPER(prt.run_type_name) = p_rt_name
3098 and p_effective_start_date between prt.effective_start_date
3099 and prt.effective_end_date
3100 and ((p_rt_bg_name is not null
3101 and prt.business_group_id = l_in_bg_id)
3102 or (p_rt_leg_code is not null
3103 and prt.legislation_code = p_rt_leg_code)
3104 or (p_rt_bg_name is null
3105 and p_rt_leg_code is null
3106 and prt.business_group_id is null
3107 and prt.legislation_code is null));
3108 --
3109 -- Get the element_type_id
3110 --
3111 select pet.element_type_id
3112 into l_in_et_id
3113 from pay_element_types_f pet
3114 where UPPER(pet.element_name) = p_element_name
3115 and p_effective_start_date between pet.effective_start_date
3116 and pet.effective_end_date
3117 and ((p_et_bg_name is not null
3118 and pet.business_group_id = l_in_bg_id)
3119 or (p_et_leg_code is not null
3120 and pet.legislation_code = p_et_leg_code)
3121 or (p_et_bg_name is null
3122 and p_et_leg_code is null
3123 and pet.business_group_id is null
3124 and pet.legislation_code is null));
3125 --
3126 -- Begin upload of element type usages
3127 --
3128 if l_mode = 'USER' then
3129 --
3130 open c_row_exists_user;
3131 fetch c_row_exists_user into l_etu_id, l_ovn, l_lc, l_bg_id, l_esd, l_eed;
3132 --
3133 if c_row_exists_user%found then
3134 --
3135 if ((l_etu_id = g_etu_rec.new_etu_id)
3136 and (p_etu_id = g_etu_rec.old_etu_id)) then
3137 update_etu;
3138 elsif ((l_bg_id is not null) and (l_in_bg_id = l_bg_id)) then
3139 zap_insert_etu;
3140 else
3141 exists_error;
3142 end if;
3143 --
3144 else
3145 insert_etu;
3146 end if;
3147 --
3148 close c_row_exists_user;
3149 --
3150 elsif l_mode = 'STARTUP' then
3151 --
3152 open c_row_exists_startup;
3153 fetch c_row_exists_startup into l_etu_id, l_ovn, l_lc, l_bg_id, l_esd, l_eed;
3154 --
3155 if c_row_exists_startup%found then
3156 --
3157 if ((l_etu_id = g_etu_rec.new_etu_id)
3158 and (p_etu_id = g_etu_rec.old_etu_id)) then
3159 update_etu;
3160 elsif ((l_lc is not null) and (l_lc = p_legislative_code)) then
3161 zap_insert_etu;
3162 else
3163 exists_error;
3164 end if;
3165 --
3169 --
3166 else
3167 insert_etu;
3168 end if;
3170 close c_row_exists_startup;
3171 --
3172 else
3173 --
3174 open c_row_exists_generic;
3175 fetch c_row_exists_generic into l_etu_id, l_ovn, l_lc, l_bg_id, l_esd, l_eed;
3176 --
3177 if c_row_exists_generic%found then
3178 --
3179 if ((l_etu_id = g_etu_rec.new_etu_id)
3180 and (p_etu_id = g_etu_rec.old_etu_id)) then
3181 update_etu;
3182 elsif ((l_lc is null) and (l_bg_id is null)) then
3183 zap_insert_etu;
3184 else
3185 exists_error;
3186 end if;
3187 --
3188 else
3189 insert_etu;
3190 end if;
3191 --
3192 close c_row_exists_generic;
3193 --
3194 end if;
3195 --
3196 if ((p_etu_id <> g_etu_rec.old_etu_id)
3197 and (g_etu_rec.old_eed <> hr_api.g_eot)) then
3198 end_date_etu;
3199 end if;
3200 --
3201 g_etu_rec.old_etu_id := p_etu_id;
3202 g_etu_rec.new_etu_id := l_out_etu_id;
3203 g_etu_rec.ovn := l_out_ovn;
3204 g_etu_rec.old_esd := p_effective_start_date;
3205 g_etu_rec.new_esd := l_out_esd;
3206 g_etu_rec.old_eed := p_effective_end_date;
3207 g_etu_rec.new_eed := l_out_eed;
3208 g_etu_rec.l_mode := l_mode;
3209 --
3210 else
3211 --
3212 if g_etu_rec.old_eed <> hr_api.g_eot then
3213 end_date_etu;
3214 end if;
3215 --
3216 end if;
3217 --
3218 end up_element_type_usage;
3219 -------------------------------------------------------------------------
3220 -- Function order_cumulative
3221 -- Description: This is called by the download section of pycoiter.lct
3222 -- when downloading entity PAY_RUN_TYPE. Cumulative run types
3223 -- can now have child run types which are themselves cumulative.
3224 -- The child run types need to be downloaded before the parent
3225 -- run types to enable the upload to work correctly. This fuction
3226 -- determines which run types with run_method of 'C' are also child
3227 -- run types, and assigns an order value of 'D'. The download
3228 -- sql, in the lct file, is ordered alphabetically in decending
3229 -- order, so 'D' will be downloaded before 'C', and hence uploaded
3230 -- before 'C'.
3231 -------------------------------------------------------------------------
3232 function order_cumulative (p_run_type_name in varchar2
3233 ,p_business_grp_name in varchar2
3234 ,p_legislation_code in varchar2)
3235 return VARCHAR2 is
3236 --
3237 cursor get_bg_id (p_bg_name varchar2)
3238 is
3239 select pbg.business_group_id
3240 from per_business_groups pbg
3241 where pbg.name = p_bg_name;
3242 --
3243 cursor csr_child_cumulative(p_leg_code varchar2
3244 ,p_bg number
3245 ,p_rt_name varchar2)
3246 is
3247 select prt.run_type_name
3248 , prt.run_method
3249 from pay_run_types_f prt
3250 , pay_run_type_usages_f rtu
3251 where prt.run_method = 'C'
3252 and nvl(prt.legislation_code, 'NULL') = nvl(p_leg_code, 'NULL')
3253 and nvl(prt.business_group_id, -1) = nvl(p_bg, -1)
3254 and prt.run_type_name = p_rt_name
3255 and prt.run_type_id = rtu.child_run_type_id;
3256 --
3257 l_bg per_business_groups.business_group_id%type;
3258 l_rt_name pay_run_types_f.run_type_name%type;
3259 l_rt_method pay_run_types_f.run_method%type;
3260 l_meth_order varchar2(2);
3261 --
3262 begin
3263 hr_utility.set_location('Entering: pay_iterate.order_cumulative',5);
3264 if p_business_grp_name is not null then
3265 open get_bg_id(p_business_grp_name);
3266 fetch get_bg_id into l_bg;
3267 close get_bg_id;
3268 else
3269 l_bg := '';
3270 end if;
3271 --
3272 open csr_child_cumulative(p_legislation_code, l_bg, p_run_type_name);
3273 fetch csr_child_cumulative into l_rt_name, l_rt_method;
3274 if csr_child_cumulative%notfound then
3275 hr_utility.set_location('pay_iterate.order_cumulative',10);
3276 close csr_child_cumulative;
3277 l_meth_order := 'C';
3278 else
3279 hr_utility.set_location('pay_iterate.order_cumulative',15);
3280 close csr_child_cumulative;
3281 l_meth_order := 'D';
3282 end if;
3283 --
3284 return l_meth_order;
3285 end order_cumulative;
3286 --
3287 begin
3288 /* initialise. */
3289 --
3290 g_entry_list.sz := 0;
3291 g_asg_id := -1;
3292 g_asg_act_id := -1;
3293 --
3294 end pay_iterate;