[Home] [Help]
PACKAGE BODY: APPS.PAY_RETRO_STATUS_LOAD
Source
1 Package Body pay_retro_status_load as
2 /* $Header: pyrtsupl.pkb 120.4.12010000.1 2008/11/19 09:02:48 nerao ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pay_retro_status_load.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------------< load_retro_asg_and_entries >----------------------|
10 -- ----------------------------------------------------------------------------
11 procedure load_retro_asg_and_entries
12 (p_assignment_id in number
13 ,p_reprocess_date in date
14 ,p_approval_status in varchar2 default null
15 ,p_retro_entry_tab in t_retro_entry_tab
16 ,p_retro_assignment_id out nocopy number
17 )
18 is
19 l_proc varchar2(72) := g_package||'load_retro_asg_and_entries';
20 l_retro_assignment_id number;
21 l_retro_entry_rec t_retro_entry_rec;
22 l_idx binary_integer;
23 begin
24 hr_utility.set_location('Entering:'|| l_proc, 10);
25 --
26 -- Issue a savepoint
27 --
28 savepoint load_retro_asg_and_entries;
29
30 l_idx := p_retro_entry_tab.first;
31
32 --
33 -- Create a superseding retro assignment.
34 --
35 pay_retro_status_internal.create_super_retro_asg
36 (p_assignment_id => p_assignment_id
37 ,p_reprocess_date => p_reprocess_date
38 ,p_start_date => null
39 ,p_approval_status => p_approval_status
40 ,p_owner_type => pay_retro_status_internal.g_user
41 ,p_retro_assignment_id => l_retro_assignment_id
42 );
43
44 loop
45 exit when not p_retro_entry_tab.exists(l_idx);
46 l_retro_entry_rec := p_retro_entry_tab(l_idx);
47 --
48 -- Create or update the retro entry.
49 --
50 pay_retro_status_internal.maintain_retro_entry
51 (p_retro_assignment_id => l_retro_assignment_id
52 ,p_element_entry_id => l_retro_entry_rec.element_entry_id
53 ,p_reprocess_date => l_retro_entry_rec.reprocess_date
54 ,p_retro_component_id => l_retro_entry_rec.retro_component_id
55 ,p_owner_type => pay_retro_status_internal.g_user
56 ,p_system_reprocess_date => null
57 ,p_entry_param_name => 'ENTRY'||l_idx
58 );
59
60 l_idx := p_retro_entry_tab.next(l_idx);
61 end loop;
62
63 --
64 -- Set out variables
65 --
66 p_retro_assignment_id := l_retro_assignment_id;
67
68 hr_utility.set_location(' Leaving:'||l_proc, 80);
69 exception
70 when others then
71 --
72 -- A validation or unexpected error has occured
73 --
74 rollback to load_retro_asg_and_entries;
75 hr_utility.set_location(' Leaving:'||l_proc, 90);
76 raise;
77 end load_retro_asg_and_entries;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |--------------------------< get_assignment_id >---------------------------|
81 -- ----------------------------------------------------------------------------
82 function get_assignment_id
83 (p_business_group_id in number
84 ,p_assignment_number in varchar2
85 ,p_full_name in varchar2 default null
86 ) return number
87 is
88 l_assignment_id number;
89 begin
90 begin
91 select distinct paf.assignment_id
92 into l_assignment_id
93 from
94 per_all_assignments_f paf
95 ,per_all_people_f ppf
96 where
97 paf.assignment_number = p_assignment_number
98 and paf.business_group_id+0 = p_business_group_id
99 and ppf.person_id = paf.person_id
100 and paf.effective_start_date between ppf.effective_start_date
101 and ppf.effective_end_date
102 and nvl(ppf.full_name, hr_api.g_varchar2)
103 = nvl(p_full_name, nvl(ppf.full_name, hr_api.g_varchar2))
104 ;
105 exception
106 when no_data_found then
107 --
108 -- No assignment found for this assignment number (and full name).
109 --
110 fnd_message.set_name('PAY','PAY_34308_RTS_NO_ASG_FOUND');
111 fnd_message.raise_error;
112 --
113 when too_many_rows then
114 --
115 -- More than one assignment found for this assignment number.
116 --
117 fnd_message.set_name('PAY','PAY_34309_RTS_MANY_ASG_FOUND');
118 fnd_message.raise_error;
119 --
120 end;
121 --
122 return l_assignment_id;
123
124 end get_assignment_id;
125 --
126 -- ----------------------------------------------------------------------------
127 -- |-------------------------< get_retro_entry_rec >--------------------------|
128 -- ----------------------------------------------------------------------------
129 procedure get_retro_entry_rec
130 (p_entry_number in number
131 ,p_business_group_id in number
132 ,p_assignment_id in number
133 ,p_element_name in varchar2
134 ,p_element_entry_id in number
135 ,p_reprocess_date in date
136 ,p_component_name in varchar2
137 ,p_retro_entry_rec out nocopy t_retro_entry_rec
138 ,p_values_set out nocopy boolean
139 )
140 is
141 l_proc varchar2(72) := g_package||'get_retro_entry_rec';
142 l_element_entry_id number;
143 l_retro_component_id number;
144 l_rec t_retro_entry_rec;
145 l_leg_code varchar2(5);
146 begin
147 hr_utility.set_location('Entering:'|| l_proc, 10);
148 --
149 -- If element_name and element_entry_id are both null, this record is ignored.
150 -- Setting values_set flag to False and exit immediately.
151 --
152 if (p_element_name is null)
153 and (p_element_entry_id is null) then
154 --
155 hr_utility.set_location(l_proc, 20);
156 p_values_set := false;
157 return;
158 end if;
159 --
160 hr_utility.set_location(l_proc, 30);
161 --
162 -- Identify element entry id.
163 --
164 l_leg_code := hr_api.return_legislation_code(p_business_group_id);
165
166 if p_element_entry_id is not null then
167 l_element_entry_id := p_element_entry_id;
168 else
169 --
170 -- Derive the element entry id from the element name.
171 --
172 begin
173 select distinct pee.element_entry_id
174 into l_element_entry_id
175 from
176 pay_element_types_f_tl pettl
177 ,pay_element_types_f pet
178 ,pay_element_links_f pel
179 ,pay_element_entries_f pee
180 where
181 pettl.element_name = p_element_name
182 and pettl.language = userenv('lang')
183 and pet.element_type_id = pettl.element_type_id
184 and nvl(pet.business_group_id, p_business_group_id) = p_business_group_id
185 and nvl(pet.legislation_code, l_leg_code) = l_leg_code
186 and pel.element_type_id = pet.element_type_id
187 and pel.business_group_id+0 = p_business_group_id
188 and pee.assignment_id = p_assignment_id
189 and pee.element_link_id = pel.element_link_id
190 and pee.creator_type in ('A', 'F', 'H', 'Q', 'SP', 'UT', 'M', 'S')
191 ;
192
193 exception
194 when no_data_found then
195 --
196 -- No entry found for this assignment.
197 --
198 fnd_message.set_name('PAY','PAY_34310_RTS_NO_ENT_FOUND');
199 fnd_message.set_token('ELEMENT_NAME', p_element_name);
200 fnd_message.raise_error;
201 --
202 when too_many_rows then
203 --
204 -- More thant one entry found for this assignment.
205 --
206 fnd_message.set_name('PAY','PAY_34311_RTS_MANY_ENT_FOUND');
207 fnd_message.set_token('ELEMENT_NAME', p_element_name);
208 fnd_message.raise_error;
209 --
210 end;
211 end if;
212 --
213 -- Identify retro component id.
214 --
215 if p_component_name is not null then
216 begin
217 select
218 retro_component_id into l_retro_component_id
219 from pay_retro_components
220 where
221 component_name = p_component_name
222 and nvl(legislation_code, l_leg_code) = l_leg_code;
223 exception
224 when no_data_found then
225 fnd_message.set_name('PAY','PAY_33167_RCU_INV_RETRO_COMP');
226 fnd_message.raise_error;
227 end;
228 end if;
229 --
230 -- Set the retro entry record.
231 --
232 l_rec.element_entry_id := l_element_entry_id;
233 l_rec.reprocess_date := p_reprocess_date;
234 l_rec.retro_component_id := l_retro_component_id;
235
236 --
237 -- Set out variables.
238 --
239 p_retro_entry_rec := l_rec;
240 p_values_set := true;
241
242 hr_utility.set_location(' Leaving:'||l_proc, 80);
243 end get_retro_entry_rec;
244 --
245 -- ----------------------------------------------------------------------------
246 -- |-------------------------< get_retro_entry_tab >--------------------------|
247 -- ----------------------------------------------------------------------------
248 procedure get_retro_entry_tab
249 (p_business_group_id in number
250 ,p_assignment_id in number
251 --
252 ,p_entry1_element_name in varchar2 default null
253 ,p_entry1_element_entry_id in number default null
254 ,p_entry1_reprocess_date in date default null
255 ,p_entry1_component_name in varchar2 default null
256 --
257 ,p_entry2_element_name in varchar2 default null
258 ,p_entry2_element_entry_id in number default null
259 ,p_entry2_reprocess_date in date default null
260 ,p_entry2_component_name in varchar2 default null
261 --
262 ,p_entry3_element_name in varchar2 default null
263 ,p_entry3_element_entry_id in number default null
264 ,p_entry3_reprocess_date in date default null
265 ,p_entry3_component_name in varchar2 default null
266 --
267 ,p_entry4_element_name in varchar2 default null
268 ,p_entry4_element_entry_id in number default null
269 ,p_entry4_reprocess_date in date default null
270 ,p_entry4_component_name in varchar2 default null
271 --
272 ,p_entry5_element_name in varchar2 default null
273 ,p_entry5_element_entry_id in number default null
274 ,p_entry5_reprocess_date in date default null
275 ,p_entry5_component_name in varchar2 default null
276 --
277 ,p_entry6_element_name in varchar2 default null
278 ,p_entry6_element_entry_id in number default null
279 ,p_entry6_reprocess_date in date default null
280 ,p_entry6_component_name in varchar2 default null
281 --
282 ,p_entry7_element_name in varchar2 default null
283 ,p_entry7_element_entry_id in number default null
284 ,p_entry7_reprocess_date in date default null
285 ,p_entry7_component_name in varchar2 default null
286 --
287 ,p_entry8_element_name in varchar2 default null
288 ,p_entry8_element_entry_id in number default null
289 ,p_entry8_reprocess_date in date default null
290 ,p_entry8_component_name in varchar2 default null
291 --
292 ,p_entry9_element_name in varchar2 default null
293 ,p_entry9_element_entry_id in number default null
294 ,p_entry9_reprocess_date in date default null
295 ,p_entry9_component_name in varchar2 default null
296 --
297 ,p_entry10_element_name in varchar2 default null
298 ,p_entry10_element_entry_id in number default null
299 ,p_entry10_reprocess_date in date default null
300 ,p_entry10_component_name in varchar2 default null
301 --
302 ,p_entry11_element_name in varchar2 default null
303 ,p_entry11_element_entry_id in number default null
304 ,p_entry11_reprocess_date in date default null
305 ,p_entry11_component_name in varchar2 default null
306 --
307 ,p_entry12_element_name in varchar2 default null
308 ,p_entry12_element_entry_id in number default null
309 ,p_entry12_reprocess_date in date default null
310 ,p_entry12_component_name in varchar2 default null
311 --
312 ,p_entry13_element_name in varchar2 default null
313 ,p_entry13_element_entry_id in number default null
314 ,p_entry13_reprocess_date in date default null
315 ,p_entry13_component_name in varchar2 default null
316 --
317 ,p_entry14_element_name in varchar2 default null
318 ,p_entry14_element_entry_id in number default null
319 ,p_entry14_reprocess_date in date default null
320 ,p_entry14_component_name in varchar2 default null
321 --
322 ,p_entry15_element_name in varchar2 default null
323 ,p_entry15_element_entry_id in number default null
324 ,p_entry15_reprocess_date in date default null
325 ,p_entry15_component_name in varchar2 default null
326 --
327 ,p_retro_entry_tab out nocopy t_retro_entry_tab
328 )
329 is
330 l_proc varchar2(72) := g_package||'get_retro_entry_tab';
331 l_retro_entry_rec t_retro_entry_rec;
332 l_retro_entry_tab t_retro_entry_tab;
333 l_values_set boolean;
334 l_idx number:= 0;
335 begin
336 hr_utility.set_location('Entering:'|| l_proc, 10);
337
338 --
339 -- Entry 1
340 --
341 l_values_set := false;
342 get_retro_entry_rec
343 (p_entry_number => 1
344 ,p_business_group_id => p_business_group_id
345 ,p_assignment_id => p_assignment_id
346 ,p_element_name => p_entry1_element_name
347 ,p_element_entry_id => p_entry1_element_entry_id
348 ,p_reprocess_date => p_entry1_reprocess_date
349 ,p_component_name => p_entry1_component_name
350 ,p_retro_entry_rec => l_retro_entry_rec
351 ,p_values_set => l_values_set
352 );
353 if l_values_set then
354 l_retro_entry_tab(1) := l_retro_entry_rec;
355 end if;
356
357 --
358 -- Entry 2
359 --
360 l_values_set := false;
361 get_retro_entry_rec
362 (p_entry_number => 2
363 ,p_business_group_id => p_business_group_id
364 ,p_assignment_id => p_assignment_id
365 ,p_element_name => p_entry2_element_name
366 ,p_element_entry_id => p_entry2_element_entry_id
367 ,p_reprocess_date => p_entry2_reprocess_date
368 ,p_component_name => p_entry2_component_name
372 if l_values_set then
369 ,p_retro_entry_rec => l_retro_entry_rec
370 ,p_values_set => l_values_set
371 );
373 l_retro_entry_tab(2) := l_retro_entry_rec;
374 end if;
375
376 --
377 -- Entry 3
378 --
379 l_values_set := false;
380 get_retro_entry_rec
381 (p_entry_number => 3
382 ,p_business_group_id => p_business_group_id
383 ,p_assignment_id => p_assignment_id
384 ,p_element_name => p_entry3_element_name
385 ,p_element_entry_id => p_entry3_element_entry_id
386 ,p_reprocess_date => p_entry3_reprocess_date
387 ,p_component_name => p_entry3_component_name
388 ,p_retro_entry_rec => l_retro_entry_rec
389 ,p_values_set => l_values_set
390 );
391 if l_values_set then
392 l_retro_entry_tab(3) := l_retro_entry_rec;
393 end if;
394
395 --
396 -- Entry 4
397 --
398 l_values_set := false;
399 get_retro_entry_rec
400 (p_entry_number => 4
401 ,p_business_group_id => p_business_group_id
402 ,p_assignment_id => p_assignment_id
403 ,p_element_name => p_entry4_element_name
404 ,p_element_entry_id => p_entry4_element_entry_id
405 ,p_reprocess_date => p_entry4_reprocess_date
406 ,p_component_name => p_entry4_component_name
407 ,p_retro_entry_rec => l_retro_entry_rec
408 ,p_values_set => l_values_set
409 );
410 if l_values_set then
411 l_retro_entry_tab(4) := l_retro_entry_rec;
412 end if;
413
414 --
415 -- Entry 5
416 --
417 l_values_set := false;
418 get_retro_entry_rec
419 (p_entry_number => 5
420 ,p_business_group_id => p_business_group_id
421 ,p_assignment_id => p_assignment_id
422 ,p_element_name => p_entry5_element_name
423 ,p_element_entry_id => p_entry5_element_entry_id
424 ,p_reprocess_date => p_entry5_reprocess_date
425 ,p_component_name => p_entry5_component_name
426 ,p_retro_entry_rec => l_retro_entry_rec
427 ,p_values_set => l_values_set
428 );
429 if l_values_set then
430 l_retro_entry_tab(5) := l_retro_entry_rec;
431 end if;
432
433 --
434 -- Entry 6
435 --
436 l_values_set := false;
437 get_retro_entry_rec
438 (p_entry_number => 6
439 ,p_business_group_id => p_business_group_id
440 ,p_assignment_id => p_assignment_id
441 ,p_element_name => p_entry6_element_name
442 ,p_element_entry_id => p_entry6_element_entry_id
443 ,p_reprocess_date => p_entry6_reprocess_date
444 ,p_component_name => p_entry6_component_name
445 ,p_retro_entry_rec => l_retro_entry_rec
446 ,p_values_set => l_values_set
447 );
448 if l_values_set then
449 l_retro_entry_tab(6) := l_retro_entry_rec;
450 end if;
451
452 --
453 -- Entry 7
454 --
455 l_values_set := false;
456 get_retro_entry_rec
457 (p_entry_number => 7
458 ,p_business_group_id => p_business_group_id
459 ,p_assignment_id => p_assignment_id
460 ,p_element_name => p_entry7_element_name
461 ,p_element_entry_id => p_entry7_element_entry_id
462 ,p_reprocess_date => p_entry7_reprocess_date
463 ,p_component_name => p_entry7_component_name
464 ,p_retro_entry_rec => l_retro_entry_rec
465 ,p_values_set => l_values_set
466 );
467 if l_values_set then
468 l_retro_entry_tab(7) := l_retro_entry_rec;
469 end if;
470
471 --
472 -- Entry 8
473 --
474 l_values_set := false;
475 get_retro_entry_rec
476 (p_entry_number => 8
477 ,p_business_group_id => p_business_group_id
478 ,p_assignment_id => p_assignment_id
479 ,p_element_name => p_entry8_element_name
480 ,p_element_entry_id => p_entry8_element_entry_id
481 ,p_reprocess_date => p_entry8_reprocess_date
482 ,p_component_name => p_entry8_component_name
483 ,p_retro_entry_rec => l_retro_entry_rec
484 ,p_values_set => l_values_set
485 );
486 if l_values_set then
487 l_retro_entry_tab(8) := l_retro_entry_rec;
488 end if;
489
490 --
491 -- Entry 9
492 --
493 l_values_set := false;
494 get_retro_entry_rec
495 (p_entry_number => 9
496 ,p_business_group_id => p_business_group_id
497 ,p_assignment_id => p_assignment_id
498 ,p_element_name => p_entry9_element_name
499 ,p_element_entry_id => p_entry9_element_entry_id
500 ,p_reprocess_date => p_entry9_reprocess_date
504 );
501 ,p_component_name => p_entry9_component_name
502 ,p_retro_entry_rec => l_retro_entry_rec
503 ,p_values_set => l_values_set
505 if l_values_set then
506 l_retro_entry_tab(9) := l_retro_entry_rec;
507 end if;
508
509 --
510 -- Entry 10
511 --
512 l_values_set := false;
513 get_retro_entry_rec
514 (p_entry_number => 10
515 ,p_business_group_id => p_business_group_id
516 ,p_assignment_id => p_assignment_id
517 ,p_element_name => p_entry10_element_name
518 ,p_element_entry_id => p_entry10_element_entry_id
519 ,p_reprocess_date => p_entry10_reprocess_date
520 ,p_component_name => p_entry10_component_name
521 ,p_retro_entry_rec => l_retro_entry_rec
522 ,p_values_set => l_values_set
523 );
524 if l_values_set then
525 l_retro_entry_tab(10) := l_retro_entry_rec;
526 end if;
527
528 --
529 -- Entry 11
530 --
531 l_values_set := false;
532 get_retro_entry_rec
533 (p_entry_number => 11
534 ,p_business_group_id => p_business_group_id
535 ,p_assignment_id => p_assignment_id
536 ,p_element_name => p_entry11_element_name
537 ,p_element_entry_id => p_entry11_element_entry_id
538 ,p_reprocess_date => p_entry11_reprocess_date
539 ,p_component_name => p_entry11_component_name
540 ,p_retro_entry_rec => l_retro_entry_rec
541 ,p_values_set => l_values_set
542 );
543 if l_values_set then
544 l_retro_entry_tab(11) := l_retro_entry_rec;
545 end if;
546
547 --
548 -- Entry 12
549 --
550 l_values_set := false;
551 get_retro_entry_rec
552 (p_entry_number => 12
553 ,p_business_group_id => p_business_group_id
554 ,p_assignment_id => p_assignment_id
555 ,p_element_name => p_entry12_element_name
556 ,p_element_entry_id => p_entry12_element_entry_id
557 ,p_reprocess_date => p_entry12_reprocess_date
558 ,p_component_name => p_entry12_component_name
559 ,p_retro_entry_rec => l_retro_entry_rec
560 ,p_values_set => l_values_set
561 );
562 if l_values_set then
563 l_retro_entry_tab(12) := l_retro_entry_rec;
564 end if;
565
566 --
567 -- Entry 13
568 --
569 l_values_set := false;
570 get_retro_entry_rec
571 (p_entry_number => 13
572 ,p_business_group_id => p_business_group_id
573 ,p_assignment_id => p_assignment_id
574 ,p_element_name => p_entry13_element_name
575 ,p_element_entry_id => p_entry13_element_entry_id
576 ,p_reprocess_date => p_entry13_reprocess_date
577 ,p_component_name => p_entry13_component_name
578 ,p_retro_entry_rec => l_retro_entry_rec
579 ,p_values_set => l_values_set
580 );
581 if l_values_set then
582 l_retro_entry_tab(13) := l_retro_entry_rec;
583 end if;
584
585 --
586 -- Entry 14
587 --
588 l_values_set := false;
589 get_retro_entry_rec
590 (p_entry_number => 14
591 ,p_business_group_id => p_business_group_id
592 ,p_assignment_id => p_assignment_id
593 ,p_element_name => p_entry14_element_name
594 ,p_element_entry_id => p_entry14_element_entry_id
595 ,p_reprocess_date => p_entry14_reprocess_date
596 ,p_component_name => p_entry14_component_name
597 ,p_retro_entry_rec => l_retro_entry_rec
598 ,p_values_set => l_values_set
599 );
600 if l_values_set then
601 l_retro_entry_tab(14) := l_retro_entry_rec;
602 end if;
603
604 --
605 -- Entry 15
606 --
607 l_values_set := false;
608 get_retro_entry_rec
609 (p_entry_number => 15
610 ,p_business_group_id => p_business_group_id
611 ,p_assignment_id => p_assignment_id
612 ,p_element_name => p_entry15_element_name
613 ,p_element_entry_id => p_entry15_element_entry_id
614 ,p_reprocess_date => p_entry15_reprocess_date
615 ,p_component_name => p_entry15_component_name
616 ,p_retro_entry_rec => l_retro_entry_rec
617 ,p_values_set => l_values_set
618 );
619 if l_values_set then
620 l_retro_entry_tab(15) := l_retro_entry_rec;
621 end if;
622
623 --
624 -- Set out variable.
625 --
626 p_retro_entry_tab := l_retro_entry_tab;
627
628 hr_utility.set_location(' Leaving:'||l_proc, 80);
629 end get_retro_entry_tab;
630 --
631 -- ----------------------------------------------------------------------------
632 -- |----------------------< load_retro_asg_and_entries >----------------------|
636 ,p_assignment_number in varchar2
633 -- ----------------------------------------------------------------------------
634 procedure load_retro_asg_and_entries
635 (p_business_group_id in number
637 ,p_full_name in varchar2 default null
638 ,p_reprocess_date in date
639 ,p_approval_status in varchar2 default null
640 --
641 ,p_entry1_element_name in varchar2 default null
642 ,p_entry1_element_entry_id in number default null
643 ,p_entry1_reprocess_date in date default null
644 ,p_entry1_component_name in varchar2 default null
645 --
646 ,p_entry2_element_name in varchar2 default null
647 ,p_entry2_element_entry_id in number default null
648 ,p_entry2_reprocess_date in date default null
649 ,p_entry2_component_name in varchar2 default null
650 --
651 ,p_entry3_element_name in varchar2 default null
652 ,p_entry3_element_entry_id in number default null
653 ,p_entry3_reprocess_date in date default null
654 ,p_entry3_component_name in varchar2 default null
655 --
656 ,p_entry4_element_name in varchar2 default null
657 ,p_entry4_element_entry_id in number default null
658 ,p_entry4_reprocess_date in date default null
659 ,p_entry4_component_name in varchar2 default null
660 --
661 ,p_entry5_element_name in varchar2 default null
662 ,p_entry5_element_entry_id in number default null
663 ,p_entry5_reprocess_date in date default null
664 ,p_entry5_component_name in varchar2 default null
665 --
666 ,p_entry6_element_name in varchar2 default null
667 ,p_entry6_element_entry_id in number default null
668 ,p_entry6_reprocess_date in date default null
669 ,p_entry6_component_name in varchar2 default null
670 --
671 ,p_entry7_element_name in varchar2 default null
672 ,p_entry7_element_entry_id in number default null
673 ,p_entry7_reprocess_date in date default null
674 ,p_entry7_component_name in varchar2 default null
675 --
676 ,p_entry8_element_name in varchar2 default null
677 ,p_entry8_element_entry_id in number default null
678 ,p_entry8_reprocess_date in date default null
679 ,p_entry8_component_name in varchar2 default null
680 --
681 ,p_entry9_element_name in varchar2 default null
682 ,p_entry9_element_entry_id in number default null
683 ,p_entry9_reprocess_date in date default null
684 ,p_entry9_component_name in varchar2 default null
685 --
686 ,p_entry10_element_name in varchar2 default null
687 ,p_entry10_element_entry_id in number default null
688 ,p_entry10_reprocess_date in date default null
689 ,p_entry10_component_name in varchar2 default null
690 --
691 ,p_entry11_element_name in varchar2 default null
692 ,p_entry11_element_entry_id in number default null
693 ,p_entry11_reprocess_date in date default null
694 ,p_entry11_component_name in varchar2 default null
695 --
696 ,p_entry12_element_name in varchar2 default null
697 ,p_entry12_element_entry_id in number default null
698 ,p_entry12_reprocess_date in date default null
699 ,p_entry12_component_name in varchar2 default null
700 --
701 ,p_entry13_element_name in varchar2 default null
702 ,p_entry13_element_entry_id in number default null
703 ,p_entry13_reprocess_date in date default null
704 ,p_entry13_component_name in varchar2 default null
705 --
706 ,p_entry14_element_name in varchar2 default null
707 ,p_entry14_element_entry_id in number default null
708 ,p_entry14_reprocess_date in date default null
709 ,p_entry14_component_name in varchar2 default null
710 --
711 ,p_entry15_element_name in varchar2 default null
712 ,p_entry15_element_entry_id in number default null
713 ,p_entry15_reprocess_date in date default null
714 ,p_entry15_component_name in varchar2 default null
715 --
716 ,p_retro_assignment_id out nocopy number
717 )
718 is
719 l_proc varchar2(72) := g_package||'load_retro_asg_and_entries';
720 l_retro_assignment_id number;
721 l_assignment_id number;
722 l_retro_entry_tab t_retro_entry_tab;
723 begin
724 hr_utility.set_location('Entering:'|| l_proc, 10);
725 --
726 -- Check mandatory parameters have been set
727 --
728 hr_api.mandatory_arg_error
729 (p_api_name => l_proc
730 ,p_argument => 'business_group_id'
731 ,p_argument_value => p_business_group_id
732 );
733
734 hr_api.mandatory_arg_error
735 (p_api_name => l_proc
736 ,p_argument => 'assignment_number'
737 ,p_argument_value => p_assignment_number
738 );
739
740 --
741 -- Validate business group.
742 --
746 --
743 hr_api.validate_bus_grp_id
744 (p_business_group_id => p_business_group_id
745 );
747 -- Get assignment id.
748 --
749 l_assignment_id := get_assignment_id
750 (p_business_group_id => p_business_group_id
751 ,p_assignment_number => p_assignment_number
752 ,p_full_name => p_full_name
753 );
754 --
755 -- Get the retro entry table.
756 --
757 get_retro_entry_tab
758 (p_business_group_id => p_business_group_id
759 ,p_assignment_id => l_assignment_id
760 --
761 ,p_entry1_element_name => p_entry1_element_name
762 ,p_entry1_element_entry_id => p_entry1_element_entry_id
763 ,p_entry1_reprocess_date => p_entry1_reprocess_date
764 ,p_entry1_component_name => p_entry1_component_name
765 --
766 ,p_entry2_element_name => p_entry2_element_name
767 ,p_entry2_element_entry_id => p_entry2_element_entry_id
768 ,p_entry2_reprocess_date => p_entry2_reprocess_date
769 ,p_entry2_component_name => p_entry2_component_name
770 --
771 ,p_entry3_element_name => p_entry3_element_name
772 ,p_entry3_element_entry_id => p_entry3_element_entry_id
773 ,p_entry3_reprocess_date => p_entry3_reprocess_date
774 ,p_entry3_component_name => p_entry3_component_name
775 --
776 ,p_entry4_element_name => p_entry4_element_name
777 ,p_entry4_element_entry_id => p_entry4_element_entry_id
778 ,p_entry4_reprocess_date => p_entry4_reprocess_date
779 ,p_entry4_component_name => p_entry4_component_name
780 --
781 ,p_entry5_element_name => p_entry5_element_name
782 ,p_entry5_element_entry_id => p_entry5_element_entry_id
783 ,p_entry5_reprocess_date => p_entry5_reprocess_date
784 ,p_entry5_component_name => p_entry5_component_name
785 --
786 ,p_entry6_element_name => p_entry6_element_name
787 ,p_entry6_element_entry_id => p_entry6_element_entry_id
788 ,p_entry6_reprocess_date => p_entry6_reprocess_date
789 ,p_entry6_component_name => p_entry6_component_name
790 --
791 ,p_entry7_element_name => p_entry7_element_name
792 ,p_entry7_element_entry_id => p_entry7_element_entry_id
793 ,p_entry7_reprocess_date => p_entry7_reprocess_date
794 ,p_entry7_component_name => p_entry7_component_name
795 --
796 ,p_entry8_element_name => p_entry8_element_name
797 ,p_entry8_element_entry_id => p_entry8_element_entry_id
798 ,p_entry8_reprocess_date => p_entry8_reprocess_date
799 ,p_entry8_component_name => p_entry8_component_name
800 --
801 ,p_entry9_element_name => p_entry9_element_name
802 ,p_entry9_element_entry_id => p_entry9_element_entry_id
803 ,p_entry9_reprocess_date => p_entry9_reprocess_date
804 ,p_entry9_component_name => p_entry9_component_name
805 --
806 ,p_entry10_element_name => p_entry10_element_name
807 ,p_entry10_element_entry_id => p_entry10_element_entry_id
808 ,p_entry10_reprocess_date => p_entry10_reprocess_date
809 ,p_entry10_component_name => p_entry10_component_name
810 --
811 ,p_entry11_element_name => p_entry11_element_name
812 ,p_entry11_element_entry_id => p_entry11_element_entry_id
813 ,p_entry11_reprocess_date => p_entry11_reprocess_date
814 ,p_entry11_component_name => p_entry11_component_name
815 --
816 ,p_entry12_element_name => p_entry12_element_name
817 ,p_entry12_element_entry_id => p_entry12_element_entry_id
818 ,p_entry12_reprocess_date => p_entry12_reprocess_date
819 ,p_entry12_component_name => p_entry12_component_name
820 --
821 ,p_entry13_element_name => p_entry13_element_name
822 ,p_entry13_element_entry_id => p_entry13_element_entry_id
823 ,p_entry13_reprocess_date => p_entry13_reprocess_date
824 ,p_entry13_component_name => p_entry13_component_name
825 --
826 ,p_entry14_element_name => p_entry14_element_name
827 ,p_entry14_element_entry_id => p_entry14_element_entry_id
828 ,p_entry14_reprocess_date => p_entry14_reprocess_date
829 ,p_entry14_component_name => p_entry14_component_name
830 --
831 ,p_entry15_element_name => p_entry15_element_name
832 ,p_entry15_element_entry_id => p_entry15_element_entry_id
833 ,p_entry15_reprocess_date => p_entry15_reprocess_date
834 ,p_entry15_component_name => p_entry15_component_name
835 --
836 ,p_retro_entry_tab => l_retro_entry_tab
837 );
838
839 --
840 -- Call the table version of procedure.
841 --
842 load_retro_asg_and_entries
843 (p_assignment_id => l_assignment_id
844 ,p_reprocess_date => p_reprocess_date
845 ,p_approval_status => p_approval_status
846 ,p_retro_entry_tab => l_retro_entry_tab
847 ,p_retro_assignment_id => l_retro_assignment_id
848 );
849
850 --
851 -- Set out variables
852 --
853 p_retro_assignment_id := l_retro_assignment_id;
854
855 hr_utility.set_location(' Leaving:'||l_proc, 80);
856 end load_retro_asg_and_entries;
857 --
858 -- ----------------------------------------------------------------------------
859 -- |----------------------< update_or_delete_retro_asg >----------------------|
860 -- ----------------------------------------------------------------------------
861 procedure update_or_delete_retro_asg
862 (p_business_group_id in number
863 ,p_assignment_number in varchar2
864 ,p_full_name in varchar2 default null
865 ,p_reprocess_date in date default null
866 ,p_approval_status in varchar2 default null
867 ,p_update_or_delete_mode in varchar2 default g_update_mode
868 )
869 is
870 l_proc varchar2(72) := g_package||'update_or_delete_retro_asg';
871 l_retro_assignment_id number;
872 l_replaced_retro_asg_id number;
873 l_assignment_id number;
874 begin
875 hr_utility.set_location('Entering:'|| l_proc, 10);
876 --
877 --
878 -- Check mandatory parameters have been set
879 --
880 hr_api.mandatory_arg_error
881 (p_api_name => l_proc
882 ,p_argument => 'business_group_id'
883 ,p_argument_value => p_business_group_id
884 );
885
886 hr_api.mandatory_arg_error
887 (p_api_name => l_proc
888 ,p_argument => 'assignment_number'
889 ,p_argument_value => p_assignment_number
890 );
891
892 --
893 -- Validate business group.
894 --
895 hr_api.validate_bus_grp_id
896 (p_business_group_id => p_business_group_id
897 );
898 --
899 -- Get assignment id.
900 --
901 l_assignment_id := get_assignment_id
902 (p_business_group_id => p_business_group_id
903 ,p_assignment_number => p_assignment_number
904 ,p_full_name => p_full_name
905 );
906
907 l_retro_assignment_id := pay_retro_status_internal.get_unprocessed_retro_asg
908 (p_assignment_id => l_assignment_id);
909
910 if l_retro_assignment_id is null then
911 --
912 -- No unprocessed retro assignment is found.
913 --
914 fnd_message.set_name('PAY','PAY_34312_RTS_NO_RTA_FOUND');
915 fnd_message.raise_error;
916 --
917 end if;
918
919 if p_update_or_delete_mode = g_update_mode then
920 --
921 pay_retro_status_internal.update_retro_asg
922 (p_retro_assignment_id => l_retro_assignment_id
923 ,p_reprocess_date => nvl(p_reprocess_date, hr_api.g_date)
924 ,p_approval_status => nvl(p_approval_status, hr_api.g_varchar2)
925 ,p_owner_type => pay_retro_status_internal.g_user
926 );
927 --
928 elsif p_update_or_delete_mode = g_delete_mode then
929 --
930 pay_retro_status_internal.delete_retro_asg
931 (p_retro_assignment_id => l_retro_assignment_id
932 ,p_owner_type => pay_retro_status_internal.g_user
933 ,p_replaced_retro_asg_id => l_replaced_retro_asg_id
934 );
935 else
936 --
937 -- The upload mode is not either UPDATE or DELETE.
938 -- Raise an assertion error.
939 --
940 pay_core_utils.assert_condition
941 (l_proc||':chk_update_or_delete_mode' ,false);
942 end if;
943
944 hr_utility.set_location(' Leaving:'||l_proc, 80);
945 end update_or_delete_retro_asg;
946 --
947 procedure update_reprocess_date(
948 p_business_group_id in number
949 ,p_assignment_number in varchar2
950 ,p_full_name in varchar2 default null
951 ,p_reprocess_date in date
952 ,p_new_retro_asg_id out nocopy number
953 ) is
954
955 --local variables.
956 l_proc varchar2(72) := g_package||'update_reprocess_date';
957 l_assignment_id number;
958
959 begin
960 hr_utility.set_location('Entering : '|| l_proc, 10);
961 --
962 -- Check mandatory parameters have been set
963 --
964 hr_utility.set_location('Checking mandatory argument business_group_id : '|| l_proc, 15);
965 hr_api.mandatory_arg_error
966 (p_api_name => l_proc
967 ,p_argument => 'business_group_id'
968 ,p_argument_value => p_business_group_id
969 );
970
971 hr_utility.set_location('Checking mandatory argument assignment_number : '|| l_proc, 20);
972 hr_api.mandatory_arg_error
973 (p_api_name => l_proc
974 ,p_argument => 'assignment_number'
975 ,p_argument_value => p_assignment_number
976 );
977 hr_utility.set_location('Checking mandatory argument reprocess_date : '|| l_proc, 30);
978 hr_api.mandatory_arg_error
979 (p_api_name => l_proc
980 ,p_argument => 'reprocess_date'
981 ,p_argument_value => p_reprocess_date
982 );
983
984 --
985 -- Validate business group.
986 --
987 hr_utility.set_location('Validating business group : '|| l_proc, 40);
988 hr_api.validate_bus_grp_id
989 (p_business_group_id => p_business_group_id
990 );
991 --
992 -- Get assignment id.
993 --
994 hr_utility.set_location('Getting assignment id : '|| l_proc, 50);
995 l_assignment_id := get_assignment_id
996 (p_business_group_id => p_business_group_id
997 ,p_assignment_number => p_assignment_number
998 ,p_full_name => p_full_name
999 );
1000
1001 hr_utility.set_location('Calling pay_retro_status_internal.update_reprocess_date : '|| l_proc, 60);
1002 pay_retro_status_internal.update_reprocess_date(
1003 p_assignment_id => l_assignment_id
1004 ,p_reprocess_date => p_reprocess_date
1005 ,p_retro_asg_id => p_new_retro_asg_id);
1006
1007 hr_utility.set_location('Leaving:'|| l_proc, 100);
1008
1009 end update_reprocess_date;
1010 --
1011 end pay_retro_status_load;