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