1 PACKAGE BODY PAY_GB_AEI_API as
2 /* $Header: pyaeigbi.pkb 120.11.12020000.4 2013/03/29 11:42:04 rajganga ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pay_gb_aei_api.';
7 --
8 --
9 -- ----------------------------------------------------------------------------
10 -- |-------------------------< pay_gb_ins_p45_3>-----------------------|
11 -- ----------------------------------------------------------------------------
12 -- Bug 1843915 Added the parameter p_aei_information8 to insert the column
13 -- P45_3_SEND_EDI_FLAG
14 -- Bug 6345375 Added following parameters :
15 -- p_aei_information9 -> PREVIOUS_TAX_PAID_NOTIFIED
16 -- p_aei_information10 -> NOT_PAID_BETWEEN_START_N_5APR
17 -- p_aei_information11 -> CONTINUE_SL_DEDUCTIONS
18 --
19
20 procedure pay_gb_ins_p45_3
21 (p_validate in boolean default false
22 ,p_assignment_id in number
23 ,p_business_group_id in number
24 ,p_information_type in varchar2
25 ,p_aei_information_category in varchar2 default null
26 ,p_aei_information1 in varchar2 default null
27 ,p_aei_information2 in varchar2 default null
28 ,p_aei_information3 in varchar2 default null
29 ,p_aei_information4 in varchar2 default null
30 ,p_aei_information5 in varchar2 default null
31 ,p_aei_information6 in varchar2 default null
32 ,p_aei_information7 in varchar2 default null
33 ,p_aei_information8 in varchar2 default null
34 ,p_aei_information9 in varchar2 default null
35 ,p_aei_information10 in varchar2 default null
36 ,p_aei_information11 in varchar2 default null
37 ,p_aei_information12 in varchar2 default null -- Bug 6994632 added for Prev Tax Pay Notified
38 ,p_object_version_number out nocopy number
39 ,p_assignment_extra_info_id out nocopy number
40 ) is
41 --
42 -- Declare cursors and local variables
43 --
44 l_legislation_code varchar2(2);
45 l_proc varchar2(72) := g_package||'pay_gb_ins_p45_3';
46 --
47 cursor csr_bg is
48 select legislation_code
49 from per_business_groups pbg
50 where pbg.business_group_id = p_business_group_id;
51 --
52 begin
53 hr_utility.set_location('Entering:'|| l_proc, 10);
54 --
55 -- Validation in addition to Row Handlers
56 --
57 -- Check that the specified business group is valid.
58 --
59 hr_utility.trace('Inside pay_gb_ins_p45_3');
60 open csr_bg;
61 fetch csr_bg
62 into l_legislation_code;
63 if csr_bg%notfound then
64 close csr_bg;
65 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
66 hr_utility.raise_error;
67 end if;
68 close csr_bg;
69 --
70 -- Check that the legislation of the specified business group is 'GB'.
71 --
72 if l_legislation_code <> 'GB' then
73 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
74 hr_utility.set_message_token('LEG_CODE','GB');
75 hr_utility.raise_error;
76 end if;
77
78 hr_utility.set_location(l_proc, 6);
79 -- Bug 3454500 check for Send EDI flag
80 if (p_aei_information8 is null) then
81 hr_utility.set_message(800, 'HR_GB_78120_MISSING_EDI_FLAG');
82 hr_utility.set_message_token('TYPE','P45(3)');
83 hr_utility.raise_error;
84 end if;
85 --
86 -- Call the Assignment Extra Information Business API
87 --
88 -- Bug 1843915 Added the parameter p_aei_information8 to insert the column
89 -- P45_3_SEND_EDI_FLAG
90
91 hr_assignment_extra_info_api.create_assignment_extra_info
92 (p_validate => p_validate
93 ,p_assignment_id => p_assignment_id
94 ,p_information_type => p_information_type
95 ,p_aei_information_category => p_aei_information_category
96 ,p_aei_information1 => p_aei_information1
97 ,p_aei_information2 => p_aei_information2
98 ,p_aei_information3 => p_aei_information3
99 ,p_aei_information4 => p_aei_information4
100 ,p_aei_information5 => p_aei_information5
101 ,p_aei_information6 => p_aei_information6
102 ,p_aei_information7 => p_aei_information7
103 ,p_aei_information8 => p_aei_information8
104 ,p_aei_information9 => p_aei_information9
105 ,p_aei_information10 => p_aei_information10
106 ,p_aei_information11 => p_aei_information11
107 ,p_aei_information12 => p_aei_information12 -- Bug 6994632 added for Prev Tax Pay Notified
108 ,p_object_version_number => p_object_version_number
109 ,p_assignment_extra_info_id => p_assignment_extra_info_id);
110
111 hr_utility.set_location(' Leaving:'||l_proc, 40);
112 end pay_gb_ins_p45_3;
113 --
114 --
115 --
116 -- ----------------------------------------------------------------------------
117 -- |-------------------------< pay_gb_upd_p45_3>-----------------------|
118 -- ----------------------------------------------------------------------------
122 -- p_aei_information9 -> PREVIOUS_TAX_PAID_NOTIFIED
119 -- Bug 1843915 Added the parameter p_aei_information8 to update the column
120 -- P45_3_SEND_EDI_FLAG
121 -- Bug 6345375 Added following parameters :
123 -- p_aei_information10 -> NOT_PAID_BETWEEN_START_N_5APR
124 -- p_aei_information11 -> CONTINUE_SL_DEDUCTIONS
125
126 procedure pay_gb_upd_p45_3
127 (p_validate in boolean default false
128 ,p_assignment_extra_info_id in number
129 ,p_business_group_id in number
130 ,p_object_version_number in out nocopy number
131 ,p_aei_information_category in varchar2 default null
132 ,p_aei_information1 in varchar2 default null
133 ,p_aei_information2 in varchar2 default null
134 ,p_aei_information3 in varchar2 default null
135 ,p_aei_information4 in varchar2 default null
136 ,p_aei_information5 in varchar2 default null
137 ,p_aei_information6 in varchar2 default null
138 ,p_aei_information7 in varchar2 default null
139 ,p_aei_information8 in varchar2 default null
140 ,p_aei_information9 in varchar2 default null
141 ,p_aei_information10 in varchar2 default null
142 ,p_aei_information11 in varchar2 default null
143 ,p_aei_information12 in varchar2 default null -- Bug 6994632 added for Prev Tax Pay Notified
144 )is
145 --
146 -- Declare cursors and local variables
147 --
148 l_legislation_code varchar2(2);
149 l_proc varchar2(72) := g_package||'pay_gb_upd_p45_3';
150 --
151 cursor csr_bg is
152 select legislation_code
153 from per_business_groups pbg
154 where pbg.business_group_id = p_business_group_id;
155 --
156 begin
157 hr_utility.set_location('Entering:'|| l_proc, 10);
158 --
159 -- Validation in addition to Row Handlers
160 --
161 -- Check that the specified business group is valid.
162 --
163 open csr_bg;
164 fetch csr_bg
165 into l_legislation_code;
166 if csr_bg%notfound then
167 close csr_bg;
168 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
169 hr_utility.raise_error;
170 end if;
171 close csr_bg;
172 --
173 -- Check that the legislation of the specified business group is 'GB'.
174 --
175 if l_legislation_code <> 'GB' then
176 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
177 hr_utility.set_message_token('LEG_CODE','GB');
178 hr_utility.raise_error;
179 end if;
180
181 hr_utility.set_location(l_proc, 6);
182 --
183 -- Call the Assignment Extra Information Business API
184 --
185 -- Bug 1843915 Added the parameter p_aei_information8 to update the column
186 -- P45_3_SEND_EDI_FLAG
187
188 hr_assignment_extra_info_api.update_assignment_extra_info
189 (p_validate => p_validate
190 ,p_assignment_extra_info_id => p_assignment_extra_info_id
191 ,p_object_version_number => p_object_version_number
192 ,p_aei_information_category => p_aei_information_category
193 ,p_aei_information1 => p_aei_information1
194 ,p_aei_information2 => p_aei_information2
195 ,p_aei_information3 => p_aei_information3
196 ,p_aei_information4 => p_aei_information4
197 ,p_aei_information5 => p_aei_information5
198 ,p_aei_information6 => p_aei_information6
199 ,p_aei_information7 => P_aei_information7
200 ,p_aei_information8 => P_aei_information8
201 ,p_aei_information9 => P_aei_information9
202 ,p_aei_information10 => P_aei_information10
203 ,p_aei_information11 => P_aei_information11
204 ,p_aei_information12 => P_aei_information12 -- Bug 6994632 added for Prev Tax Pay Notified
205 );
206
207 hr_utility.set_location(' Leaving:'||l_proc, 40);
208 end pay_gb_upd_p45_3;
209 --
210 -----------------------------------------------------------------------------
211 -- |-------------------------< pay_gb_ins_p45_info>-----------------------|
212 -- --------------------------------------------------------------------------
213 procedure pay_gb_ins_p45_info
214 (p_validate in boolean default false
215 ,p_assignment_id in number
216 ,p_business_group_id in number
217 ,p_person_id in number
218 ,p_effective_date in date
219 ,p_aggregated_paye_flag in varchar2 default null
220 ,p_information_type in varchar2
221 ,p_aei_information_category in varchar2 default null
222 ,p_aei_information1 in varchar2 default null
223 ,p_aei_information2 in varchar2 default null
224 ,p_aei_information3 in varchar2 default null
225 ,p_aei_information4 in varchar2 default null
226 ,p_object_version_number out nocopy number
227 ,p_assignment_extra_info_id out nocopy number
228 ) is
229 --
230 -- Declare cursors and local variables
231 --
232 l_legislation_code varchar2(2);
233 l_proc varchar2(72) := g_package||'pay_gb_ins_p45_info';
234 l_asg_tax_dist varchar2(50);
235 --
236 cursor csr_bg is
237 select legislation_code
238 from per_business_groups pbg
239 where pbg.business_group_id = p_business_group_id;
240
241 cursor csr_aggr_paye_flag (c_person_id in number,
242 c_effective_date in date) is
246 and c_effective_date between
243 select per_information10
244 from per_all_people_f
245 where person_id = c_person_id
247 effective_start_date and effective_end_date;
248
249 l_effective_date date;
250 l_aggregated_paye_flag per_all_people_f.per_information10%type;
251 l_period_of_service_id per_all_assignments_f.period_of_service_id%type;
252 l_object_version_number number;
253 l_assignment_extra_info_id number;
254 --
255 -- fetch the tax PAYE reference for the given asg. on the effective date
256 --
257 CURSOR tax_district(c_assignment_id in number,
258 c_effective_date in date) IS
259 SELECT hsck.segment1, period_of_service_id
260 FROM hr_soft_coding_keyflex hsck,
261 pay_all_payrolls_f papf,
262 per_all_assignments_f paaf
263 WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
264 AND papf.payroll_id = paaf.payroll_id
265 AND paaf.assignment_id = c_assignment_id
266 AND c_effective_date between
267 papf.effective_start_date and papf.effective_end_date
268 AND c_effective_date between
269 paaf.effective_start_date and paaf.effective_end_date;
270 --
271
272 --
273 -- to fetch all the aggregated assignments and corresponding extra info
274 -- based on effective date, if the extra info id is null then insert else update
275 -- except the current assignment; because we directly insert the value for this asg.
276 --
277 cursor csr_person_agg_asg (c_person_id in number,
278 c_tax_ref in varchar2,
279 c_effective_date in date,
280 c_assignment_id in number,
281 c_period_of_service_id in number) is
282 select distinct
283 a.assignment_id,
284 extra.assignment_extra_info_id,
285 extra.object_version_number ovn,
286 extra.aei_information_category,
287 extra.aei_information1,
288 extra.aei_information2,
289 extra.aei_information3,
290 extra.aei_information4
291 from per_all_assignments_f a,
292 pay_all_payrolls_f pay,
293 hr_soft_coding_keyflex flex,
294 per_assignment_status_types past,
295 per_assignment_extra_info extra
296 where a.person_id = c_person_id
297 and flex.segment1 = c_tax_ref
298 and pay.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
299 and a.payroll_id = pay.payroll_id
300 and a.assignment_status_type_id = past.assignment_status_type_id
301 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
302 and a.period_of_service_id = c_period_of_service_id
303 and c_effective_date between
304 pay.effective_start_date and pay.effective_end_date
305 and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_effective_date)
306 and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_effective_date)
307 and extra.assignment_id(+) = a.assignment_id
308 and extra.information_type(+) = p_information_type
309 and a.assignment_id <> c_assignment_id
310 ;
311
312 --
313 -- to fetch the last active/susp status date for the given assignment
314 --
315 cursor csr_asg_last_active_date(c_assignment_id number) is
316 select max(effective_end_date)
317 from per_all_assignments_f a,
318 per_assignment_status_types past
319 where a.assignment_id = c_assignment_id
320 and a.assignment_status_type_id = past.assignment_status_type_id
321 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
322
323 --
324 -- to fetch the earliest aggregation start date near to the manual issue date/override date.
325 --
326 cursor csr_latest_aggr_start_date(c_person_id number, c_effective_date date) is
327 select max(effective_end_date) + 1
328 from per_all_people_f
329 where person_id = c_person_id
330 and nvl(per_information10,'N') = 'N'
331 and effective_end_date < c_effective_date;
332
333 --
334 -- to check whether the given assignment present between
335 -- the earliest aggregation start date and manual issue date/override date.
336 --
337 cursor csr_asg_present_status(c_assignment_id number, c_start_date date, c_end_date date) is
338 select 1
339 from per_all_assignments_f a
340 where a.assignment_id = c_assignment_id
341 and a.effective_end_date >= c_start_date
342 and a.effective_start_date <= c_end_date;
343
344 l_found boolean;
345 l_dummy number;
346 l_asg_last_active_date date;
347 l_rec_asg_tax_dist varchar2(50);
348 l_rec_period_of_service_id number;
349 l_latest_aggr_start_date date;
350 --
351
352 begin
353 -- hr_utility.trace_on(null, 'ARUL');
354 hr_utility.set_location('Entering:'|| l_proc, 10);
355 --
356 -- Validation in addition to Row Handlers
357 --
358 -- Check that the specified business group is valid.
359 open csr_bg;
360 fetch csr_bg
361 into l_legislation_code;
362 if csr_bg%notfound then
363 close csr_bg;
364 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
365 hr_utility.raise_error;
366 end if;
367 close csr_bg;
368 hr_utility.set_location(l_proc,20);
369 --
373 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
370 -- Check that the legislation of the specified business group is 'GB'.
371 --
372 if l_legislation_code <> 'GB' then
374 hr_utility.set_message_token('LEG_CODE','GB');
375 hr_utility.raise_error;
376 end if;
377
378 hr_utility.set_location(l_proc, 30);
379
380 -- inserting the extra info for the current assignment.
381 --
382 hr_assignment_extra_info_api.create_assignment_extra_info
383 (p_validate => p_validate
384 ,p_assignment_id => p_assignment_id
385 ,p_information_type => p_information_type
386 ,p_aei_information_category => p_aei_information_category
387 ,p_aei_information1 => p_aei_information1
388 ,p_aei_information2 => p_aei_information2
389 ,p_aei_information3 => p_aei_information3
390 ,p_aei_information4 => p_aei_information4
391 ,p_object_version_number => p_object_version_number
392 ,p_assignment_extra_info_id => p_assignment_extra_info_id);
393 --
394 -- inserting the extra info for the current assignment ends.
395
396
397 -- first insert/update EIT info based on the manual issue date then
398 -- continue the same based on override date
399
400
401 hr_utility.set_location(l_proc,50);
402 --
403 -- considering the manual issue date as the effective date
404 --
405 l_effective_date := fnd_date.canonical_to_date(p_aei_information3);
406 --
407
408 hr_utility.set_location(l_proc,50);
409 --
410 -- fetching the Aggregated PAYE flag at manual issue date
411 --
412 open csr_aggr_paye_flag(p_person_id, l_effective_date);
413 fetch csr_aggr_paye_flag into l_aggregated_paye_flag;
414 close csr_aggr_paye_flag;
415 --
416
417 hr_utility.set_location(l_proc,60);
418
419 -- if PAYE flag 'Y' then
420 -- Call the Assignment Extra Information Business API
421 -- For each agg assignment.
422
423 if nvl(l_aggregated_paye_flag,'X') = 'Y' and l_effective_date is not null then
424
425 hr_utility.set_location(l_proc, 70);
426 -- Aggregated paye, so loop through active/suspended assignments
427 -- in current Tax District, and insert/update a row for each
428 -- based on record already exists (or) not, manual issue date is null
429 open tax_district(p_assignment_id, l_effective_date);
430 fetch tax_district into l_asg_tax_dist, l_period_of_service_id;
431 close tax_district;
432 --
433
434 --
435 -- to fetch the latest aggregation start date near to manual issue date.
436 --
437 l_latest_aggr_start_date := null;
438 open csr_latest_aggr_start_date(p_person_id, l_effective_date);
439 fetch csr_latest_aggr_start_date into l_latest_aggr_start_date;
440 close csr_latest_aggr_start_date;
441 --
442
443 hr_utility.set_location(l_proc, 80);
444
445 --
446 -- if extra info already exists for the asg and manual issue date is null then update
447 -- if extra info not found for the asg then insert extra info for that asg.
448 for r_rec in csr_person_agg_asg(p_person_id, l_asg_tax_dist,
449 l_effective_date, p_assignment_id,
450 l_period_of_service_id) loop
451
452 hr_utility.set_location(l_proc, 90);
453
454 --
455 -- fetch the last active/susp status of the r_rec assignemnt
456 --
457 l_asg_last_active_date := null;
458 open csr_asg_last_active_date(r_rec.assignment_id);
459 fetch csr_asg_last_active_date into l_asg_last_active_date;
460 close csr_asg_last_active_date;
461 --
462
463 --
464 -- fetch the tax reference and period of service id for the r_rec asg
465 -- on the last active/susp status date
466 --
467 open tax_district(r_rec.assignment_id, l_asg_last_active_date);
468 fetch tax_district into l_rec_asg_tax_dist, l_rec_period_of_service_id;
469 l_found := tax_district%found;
470 close tax_district;
471 --
472
473 if l_found and l_rec_asg_tax_dist = l_asg_tax_dist and
474 l_rec_period_of_service_id = l_period_of_service_id then
475
476 hr_utility.set_location(l_proc, 100);
477
478 if l_latest_aggr_start_date is not null then
479 hr_utility.set_location(l_proc, 110);
480 --
481 -- to check whther the given assignment present between
482 -- the earliest aggregation start date and manual issue date
483 --
484 open csr_asg_present_status(r_rec.assignment_id, l_latest_aggr_start_date, l_effective_date);
485 fetch csr_asg_present_status into l_dummy;
486 l_found := csr_asg_present_status%found;
487 close csr_asg_present_status;
488 --
489 end if;
490
491 if l_found then
492 hr_utility.set_location(l_proc, 120);
493 -- extra info id null then insert only the manual issue date
494 if r_rec.assignment_extra_info_id is null then
495 hr_utility.set_location(l_proc, 130);
496
497 hr_assignment_extra_info_api.create_assignment_extra_info
498 (p_validate => p_validate
502 ,p_aei_information1 => null
499 ,p_assignment_id => r_rec.assignment_id
500 ,p_information_type => p_information_type
501 ,p_aei_information_category => p_aei_information_category
503 ,p_aei_information2 => null
504 ,p_aei_information3 => p_aei_information3
505 ,p_aei_information4 => null
506 ,p_object_version_number => l_object_version_number
507 ,p_assignment_extra_info_id => l_assignment_extra_info_id);
508
509 -- extra info id not null and manual issue date is null then update
510 elsif r_rec.assignment_extra_info_id is not null and r_rec.aei_information3 is null then
511 hr_utility.set_location(l_proc, 140);
512 l_object_version_number := r_rec.ovn;
513 hr_assignment_extra_info_api.update_assignment_extra_info
514 (p_validate => p_validate
515 ,p_assignment_extra_info_id => r_rec.assignment_extra_info_id
516 ,p_object_version_number => l_object_version_number
517 ,p_aei_information_category => r_rec.aei_information_category
518 ,p_aei_information1 => r_rec.aei_information1
519 ,p_aei_information2 => r_rec.aei_information2
520 ,p_aei_information3 => p_aei_information3
521 ,p_aei_information4 => r_rec.aei_information4);
522
523 end if;
524 end if;
525 end if; -- paye reference, period of service id same
526 --
527 end loop;
528 --
529 hr_utility.set_location(l_proc, 150);
530 end if;
531 -- insert/update based on manual issue date ends
532
533
534 --
535 -- considering the override date as the effective date
536 --
537 l_effective_date := fnd_date.canonical_to_date(p_aei_information4);
538 --
539
540 hr_utility.set_location(l_proc,160);
541 -- fetching the Aggregated PAYE flag at override date
542 --
543 open csr_aggr_paye_flag(p_person_id, l_effective_date);
544 fetch csr_aggr_paye_flag into l_aggregated_paye_flag;
545 close csr_aggr_paye_flag;
546 --
547
548 hr_utility.set_location(l_proc,170);
549 --
550 -- if PAYE flag 'Y' then
551 -- Call the Assignment Extra Information Business API
552 -- For each agg assignment.
553 if nvl(l_aggregated_paye_flag,'X') = 'Y' and l_effective_date is not null then
554 hr_utility.set_location(l_proc, 180);
555 -- Aggregated paye, so loop through active assignments
556 -- in current Tax District, and insert/update a row for each
557 -- based on record already exists (or) not, override date is null
558 open tax_district(p_assignment_id, l_effective_date);
559 fetch tax_district into l_asg_tax_dist, l_period_of_service_id;
560 close tax_district;
561 --
562
563 --
564 -- to fetch the latest aggregation start date near to override date.
565 --
566 l_latest_aggr_start_date := null;
567 open csr_latest_aggr_start_date(p_person_id, l_effective_date);
568 fetch csr_latest_aggr_start_date into l_latest_aggr_start_date;
569 close csr_latest_aggr_start_date;
570 --
571
572 hr_utility.set_location(l_proc, 190);
573
574 --
575 -- if extra info already exists for the asg and override date is null then update
576 -- if extra info not found for the asg then insert extra info for that asg.
577 for r_rec in csr_person_agg_asg(p_person_id, l_asg_tax_dist,
578 l_effective_date, p_assignment_id,
579 l_period_of_service_id) loop
580
581 hr_utility.set_location(l_proc, 200);
582 --
583 -- fetch the last active/susp status of the r_rec assignemnt
584 --
585 l_asg_last_active_date := null;
586 open csr_asg_last_active_date(r_rec.assignment_id);
587 fetch csr_asg_last_active_date into l_asg_last_active_date;
588 close csr_asg_last_active_date;
589 --
590
591 --
592 -- fetch the tax reference and period of service id for the r_rec asg
593 -- on the last active/susp status date
594 --
595 open tax_district(r_rec.assignment_id, l_asg_last_active_date);
596 fetch tax_district into l_rec_asg_tax_dist, l_rec_period_of_service_id;
597 l_found := tax_district%found;
598 close tax_district;
599 --
600
601 if l_found and l_rec_asg_tax_dist = l_asg_tax_dist and
602 l_rec_period_of_service_id = l_period_of_service_id then
603
604 hr_utility.set_location(l_proc, 210);
605
606 if l_latest_aggr_start_date is not null then
607 hr_utility.set_location(l_proc, 220);
608 --
609 -- to check whther the given assignment present between
610 -- the earliest aggregation start date and override date
611 --
612 open csr_asg_present_status(r_rec.assignment_id, l_latest_aggr_start_date, l_effective_date);
613 fetch csr_asg_present_status into l_dummy;
614 l_found := csr_asg_present_status%found;
615 close csr_asg_present_status;
616 --
617 end if;
618
619 if l_found then
620 hr_utility.set_location(l_proc, 230);
624 hr_assignment_extra_info_api.create_assignment_extra_info
621 -- extra info id null then insert only the override date
622 if r_rec.assignment_extra_info_id is null then
623 hr_utility.set_location(l_proc, 240);
625 (p_validate => p_validate
626 ,p_assignment_id => r_rec.assignment_id
627 ,p_information_type => p_information_type
628 ,p_aei_information_category => p_aei_information_category
629 ,p_aei_information1 => null
630 ,p_aei_information2 => null
631 ,p_aei_information3 => null
632 ,p_aei_information4 => p_aei_information4
633 ,p_object_version_number => l_object_version_number
634 ,p_assignment_extra_info_id => l_assignment_extra_info_id);
635
636 -- extra info id not null and override date is null then update
637 elsif r_rec.assignment_extra_info_id is not null and r_rec.aei_information4 is null then
638 hr_utility.set_location(l_proc, 250);
639 l_object_version_number := r_rec.ovn;
640 hr_assignment_extra_info_api.update_assignment_extra_info
641 (p_validate => p_validate
642 ,p_assignment_extra_info_id => r_rec.assignment_extra_info_id
643 ,p_object_version_number => l_object_version_number
644 ,p_aei_information_category => r_rec.aei_information_category
645 ,p_aei_information1 => r_rec.aei_information1
646 ,p_aei_information2 => r_rec.aei_information2
647 ,p_aei_information3 => r_rec.aei_information3
648 ,p_aei_information4 => p_aei_information4);
649
650 end if;
651 end if;
652 end if; -- paye reference, period of service id same
653 --
654 end loop;
655 --
656 hr_utility.set_location(l_proc, 260);
657 end if;
658 -- insert/update based on override date ends
659
660 hr_utility.set_location(' Leaving:'||l_proc, 300);
661 -- hr_utility.trace_off;
662 end pay_gb_ins_p45_info;
663 -- -----------------------------------------------------------------------
664 -- |-------------------------< pay_gb_upd_p45_info>-----------------------|
665 -- -----------------------------------------------------------------------
666 procedure pay_gb_upd_p45_info
667 (p_validate in boolean default false
668 ,p_assignment_extra_info_id in number
669 ,p_business_group_id in number
670 ,p_assignment_id in number
671 ,p_person_id in number
672 ,p_effective_date in date
673 ,p_aggregated_paye_flag in varchar2 default null
674 ,p_object_version_number in out nocopy number
675 ,p_aei_information_category in varchar2 default null
676 ,p_aei_information1 in varchar2 default null
677 ,p_aei_information2 in varchar2 default null
678 ,p_aei_information3 in varchar2 default null
679 ,p_aei_information4 in varchar2 default null
680 )is
681 --
682 -- Declare cursors and local variables
683 --
684 l_legislation_code varchar2(2);
685 l_proc varchar2(72) := g_package||'pay_gb_upd_p45_info';
686 l_asg_tax_dist varchar2(50);
687 --
688 cursor csr_bg is
689 select legislation_code
690 from per_business_groups pbg
691 where pbg.business_group_id = p_business_group_id;
692 --
693
694 cursor csr_aggr_paye_flag (c_person_id in number,
695 c_effective_date in date) is
696 select per_information10
697 from per_all_people_f
698 where person_id = c_person_id
699 and c_effective_date between
700 effective_start_date and effective_end_date;
701
702 l_effective_date date;
703 l_aggregated_paye_flag per_all_people_f.per_information10%type;
704 l_period_of_service_id number;
705
706
707 l_old_aei_information3 per_assignment_extra_info.aei_information3%type;
708 l_old_aei_information4 per_assignment_extra_info.aei_information4%type;
709 l_old_effective_date date;
710 l_old_aggregated_paye_flag per_all_people_f.per_information10%type;
711 l_old_asg_tax_dist varchar2(50);
712 l_old_period_of_service_id number;
713 l_information_type per_assignment_extra_info.information_type%type;
714 l_assignment_extra_info_id number;
715 l_object_version_number number;
716
717 --
718 cursor csr_old_aei_info(c_assignment_extra_info_id number) is
719 select aei_information3, aei_information4, information_type
720 from per_assignment_extra_info
721 where assignment_extra_info_id = c_assignment_extra_info_id;
722 --
723
724 --
725 CURSOR tax_district(c_assignment_id in number,
726 c_effective_date in date) IS
727 SELECT hsck.segment1, period_of_service_id
728 FROM hr_soft_coding_keyflex hsck,
729 pay_all_payrolls_f papf,
730 per_all_assignments_f paaf
731 WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
732 AND papf.payroll_id = paaf.payroll_id
733 AND paaf.assignment_id = c_assignment_id
734 AND c_effective_date between
735 papf.effective_start_date and papf.effective_end_date
739
736 AND c_effective_date between
737 paaf.effective_start_date and paaf.effective_end_date;
738 --
740 --
741 -- to fetch all the aggregated assignments and corresponding extra info
742 -- based on old effective date. if the extra info id is not null then update information when
743 -- if both are same, effective date and manual issue date/override date; else no need to update.
744 -- based on new effective date, if the extra info id is null then insert else update
745 -- except the current assignment; will update separately after fetching the old values
746 --
747 cursor csr_per_agg_asg_extra (c_person_id in number,
748 c_tax_ref in varchar2,
749 c_effective_date in date,
750 c_information_type in varchar2,
751 c_assignment_id in number,
752 c_period_of_service_id in number) is
753 select distinct
754 a.assignment_id,
755 extra.assignment_extra_info_id,
756 extra.object_version_number ovn,
757 extra.aei_information_category,
758 extra.aei_information1,
759 extra.aei_information2,
760 extra.aei_information3,
761 extra.aei_information4
762 from per_all_assignments_f a,
763 pay_all_payrolls_f pay,
764 hr_soft_coding_keyflex flex,
765 per_assignment_status_types past,
766 per_assignment_extra_info extra
767 where a.person_id = c_person_id
768 and flex.segment1 = c_tax_ref
769 and pay.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
770 and a.payroll_id = pay.payroll_id
771 and extra.assignment_id(+) = a.assignment_id
772 and extra.information_type(+) = c_information_type
773 and a.assignment_status_type_id = past.assignment_status_type_id
774 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
775 and a.period_of_service_id = c_period_of_service_id
776 and c_effective_date between
777 pay.effective_start_date and pay.effective_end_date
778 and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_effective_date)
779 and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_effective_date)
780 and a.assignment_id <> c_assignment_id
781 ;
782
783 --
784 -- to fetch the last active/susp status date for the given assignment
785 --
786 cursor csr_asg_last_active_date(c_assignment_id number) is
787 select max(effective_end_date)
788 from per_all_assignments_f a,
789 per_assignment_status_types past
790 where a.assignment_id = c_assignment_id
791 and a.assignment_status_type_id = past.assignment_status_type_id
792 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
793
794 --
795 -- to fetch the earliest aggregation start date near to the manual issue date/override date.
796 --
797 cursor csr_latest_aggr_start_date(c_person_id number, c_effective_date date) is
798 select max(effective_end_date) + 1
799 from per_all_people_f
800 where person_id = c_person_id
801 and nvl(per_information10,'N') = 'N'
802 and effective_end_date < c_effective_date;
803
804 --
805 -- to check whether the given assignment present between
806 -- the earliest aggregation start date and manual issue date/override date.
807 --
808 cursor csr_asg_present_status(c_assignment_id number, c_start_date date, c_end_date date) is
809 select 1
810 from per_all_assignments_f a
811 where a.assignment_id = c_assignment_id
812 and a.effective_end_date >= c_start_date
813 and a.effective_start_date <= c_end_date;
814
815 l_found boolean;
816 l_dummy number;
817 l_asg_last_active_date date;
818 l_rec_asg_tax_dist varchar2(50);
819 l_rec_period_of_service_id number;
820 l_latest_aggr_start_date date;
821 l_old_latest_aggr_start_date date;
822 --
823
824 begin
825 -- hr_utility.trace_on(null, 'ARUL');
826 hr_utility.set_location('Entering:'|| l_proc, 10);
827 -- Validation in addition to Row Handlers
828 --
829 -- Check that the specified business group is valid.
830 --
831 open csr_bg;
832 fetch csr_bg
833 into l_legislation_code;
834 if csr_bg%notfound then
835 close csr_bg;
836 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
837 hr_utility.raise_error;
838 end if;
839 close csr_bg;
840 --
841
842 hr_utility.set_location(l_proc,20);
843 -- Check that the legislation of the specified business group is 'GB'.
844 --
845 if l_legislation_code <> 'GB' then
846 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
847 hr_utility.set_message_token('LEG_CODE','GB');
848 hr_utility.raise_error;
849 end if;
850 --
851
852 hr_utility.set_location(l_proc, 30);
853 hr_utility.trace('p_assignment_extra_info_id = ' || p_assignment_extra_info_id);
854 hr_utility.trace('p_aggregated_paye_flag = ' || p_aggregated_paye_flag);
855 hr_utility.trace('p_aei_information1 = ' || p_aei_information1);
856 hr_utility.trace('p_aei_information2 = ' || p_aei_information2);
857 hr_utility.trace('p_aei_information3 = ' || p_aei_information3);
858 hr_utility.trace('p_aei_information4 = ' || p_aei_information4);
859 --
860
861 -- fething the old manual issue date, old override date for the current assignment extra info id
862 open csr_old_aei_info(p_assignment_extra_info_id);
863 fetch csr_old_aei_info into l_old_aei_information3, l_old_aei_information4, l_information_type;
864 close csr_old_aei_info;
865
866 hr_utility.set_location(l_proc, 40);
867
868 --
869 -- update the current asg extra information if manual issue date or override date changed
870 -- so no need to update this current record again
871 --
872 if nvl(l_old_aei_information3,'X') <> nvl(p_aei_information3,'X') or
873 nvl(l_old_aei_information4,'X') <> nvl(p_aei_information4,'X') then
874 hr_utility.set_location(l_proc, 50);
875 hr_assignment_extra_info_api.update_assignment_extra_info
876 (p_validate => p_validate
877 ,p_assignment_extra_info_id => p_assignment_extra_info_id
878 ,p_object_version_number => p_object_version_number
879 ,p_aei_information_category => p_aei_information_category
880 ,p_aei_information1 => p_aei_information1
881 ,p_aei_information2 => p_aei_information2
882 ,p_aei_information3 => p_aei_information3
883 ,p_aei_information4 => p_aei_information4);
884 end if;
885 -- current record updation ends
886
887 -- first update EIT info based on the manual issue date then
888 -- continue the same based on override date
889
890 -- the entered manual issue date, old manual issue date are different
891 -- then will have to clear(update as null) all the agg. asg EIT's associated at old manual issue date and
892 -- update agg asg's extra info as of new manual issue date
893 if nvl(l_old_aei_information3,'X') <> nvl(p_aei_information3,'X') then
894 hr_utility.set_location(l_proc, 60);
895
896 --
897 -- considering the manual issue date as the effective date
898 --
899 l_old_effective_date := fnd_date.canonical_to_date(l_old_aei_information3);
900 --
901
902 hr_utility.set_location(l_proc, 70);
903 --
904 open csr_aggr_paye_flag(p_person_id, l_old_effective_date);
905 fetch csr_aggr_paye_flag into l_old_aggregated_paye_flag;
906 close csr_aggr_paye_flag;
907 --
908
909 -- Aggregated PAYE, loop through agg assignments in
910 --
911 if nvl(l_old_aggregated_paye_flag,'X') = 'Y' and l_old_effective_date is not null then
912 hr_utility.set_location(l_proc, 80);
913 -- Aggregated paye, so loop through active/suspended assignments
914 -- in old Tax District, and update manual issue date as null
915 -- for each row
916 open tax_district(p_assignment_id, l_old_effective_date);
917 fetch tax_district into l_old_asg_tax_dist, l_old_period_of_service_id;
918 close tax_district;
919 --
920
921 --
922 -- to fetch the latest aggregation start date near to old manual issue date.
923 --
924 l_old_latest_aggr_start_date := null;
925 open csr_latest_aggr_start_date(p_person_id, l_old_effective_date);
926 fetch csr_latest_aggr_start_date into l_old_latest_aggr_start_date;
927 close csr_latest_aggr_start_date;
928 --
929
930 hr_utility.set_location(l_proc, 90);
931 --
932 -- fetching all the agg asg's based on the manual issue date
933 --
934 for r_rec in csr_per_agg_asg_extra(p_person_id, l_old_asg_tax_dist,
935 l_old_effective_date,
936 l_information_type, p_assignment_id,
937 l_old_period_of_service_id)
938 loop
939 hr_utility.set_location(l_proc, 100);
940 --
941 -- fetch the last active/susp status of the r_rec assignemnt
942 --
943 l_asg_last_active_date := null;
944 open csr_asg_last_active_date(r_rec.assignment_id);
945 fetch csr_asg_last_active_date into l_asg_last_active_date;
946 close csr_asg_last_active_date;
947 --
948
949 --
950 -- fetch the tax reference and period of service id for the r_rec asg
951 -- on the last active/susp status date
952 --
953 open tax_district(r_rec.assignment_id, l_asg_last_active_date);
954 fetch tax_district into l_rec_asg_tax_dist, l_rec_period_of_service_id;
955 l_found := tax_district%found;
956 close tax_district;
957 --
958
959 if l_found and l_rec_asg_tax_dist = l_old_asg_tax_dist and
960 l_rec_period_of_service_id = l_old_period_of_service_id then
961
962 hr_utility.set_location(l_proc, 110);
963
964 if l_old_latest_aggr_start_date is not null then
965 hr_utility.set_location(l_proc, 120);
966 --
967 -- to check whther the given assignment present between
968 -- the earliest aggregation start date and old manual issue date
969 --
970 open csr_asg_present_status(r_rec.assignment_id, l_old_latest_aggr_start_date, l_old_effective_date);
971 fetch csr_asg_present_status into l_dummy;
972 l_found := csr_asg_present_status%found;
973 close csr_asg_present_status;
974 --
975 end if;
976
977 if l_found then
978 hr_utility.set_location(l_proc, 130);
979 --
980 -- if extra info id already exists for the asg and manual issue date is same, then update as null
981 --
982 if r_rec.assignment_extra_info_id is not null and r_rec.aei_information3 = l_old_aei_information3 then
983 hr_utility.set_location(l_proc, 140);
984
985 l_object_version_number := r_rec.ovn;
986 hr_assignment_extra_info_api.update_assignment_extra_info
987 (p_validate => p_validate
988 ,p_assignment_extra_info_id => r_rec.assignment_extra_info_id
989 ,p_object_version_number => l_object_version_number
990 ,p_aei_information_category => r_rec.aei_information_category
991 ,p_aei_information1 => r_rec.aei_information1
992 ,p_aei_information2 => r_rec.aei_information2
993 ,p_aei_information3 => null
994 ,p_aei_information4 => r_rec.aei_information4);
995 end if;
996 --
997 end if;
998 end if; -- paye reference, period of service id same
999 end loop;
1000 end if;
1001
1002 hr_utility.set_location(l_proc, 150);
1003
1004 --
1005 -- considering the entered manual issue date as the effective date
1006 --
1007 l_effective_date := fnd_date.canonical_to_date(p_aei_information3);
1008 --
1009
1010 hr_utility.set_location(l_proc, 160);
1011 --
1012 open csr_aggr_paye_flag(p_person_id, l_effective_date);
1013 fetch csr_aggr_paye_flag into l_aggregated_paye_flag;
1014 close csr_aggr_paye_flag;
1015 --
1016
1017 hr_utility.set_location(l_proc, 170);
1018
1019 if nvl(l_aggregated_paye_flag,'X') = 'Y' and l_effective_date is not null then -- PAYE as 'Y'
1020
1021 -- Aggregated PAYE, loop through agg assignments in
1022 -- current tax district
1023 hr_utility.set_location(l_proc, 180);
1024
1025
1026 -- Aggregated paye, so loop through active assignments
1027 -- in current Tax District, and insert a row for each.
1028 open tax_district(p_assignment_id, l_effective_date);
1029 fetch tax_district into l_asg_tax_dist, l_period_of_service_id;
1030 close tax_district;
1031 --
1032
1033 --
1034 -- to fetch the latest aggregation start date near to manual issue date.
1035 --
1036 l_latest_aggr_start_date := null;
1037 open csr_latest_aggr_start_date(p_person_id, l_effective_date);
1038 fetch csr_latest_aggr_start_date into l_latest_aggr_start_date;
1039 close csr_latest_aggr_start_date;
1040 --
1041
1042 hr_utility.set_location(l_proc, 190);
1043
1044 --
1045 -- if extra info already exists for the asg and manual issue date is null then update
1046 -- if extra info not found for the asg then insert extra info for that asg.
1047 --
1048 for r_rec in csr_per_agg_asg_extra(p_person_id, l_asg_tax_dist,
1049 l_effective_date, l_information_type,
1050 p_assignment_id, l_period_of_service_id) loop
1051 hr_utility.set_location(l_proc, 200);
1052 --
1053 -- fetch the last active/susp status of the r_rec assignemnt
1054 --
1055 l_asg_last_active_date := null;
1056 open csr_asg_last_active_date(r_rec.assignment_id);
1057 fetch csr_asg_last_active_date into l_asg_last_active_date;
1058 close csr_asg_last_active_date;
1059 --
1060
1061 --
1062 -- fetch the tax reference and period of service id for the r_rec asg
1063 -- on the last active/susp status date
1064 --
1065 open tax_district(r_rec.assignment_id, l_asg_last_active_date);
1066 fetch tax_district into l_rec_asg_tax_dist, l_rec_period_of_service_id;
1067 l_found := tax_district%found;
1068 close tax_district;
1069 --
1070
1071 if l_found and l_rec_asg_tax_dist = l_asg_tax_dist and
1072 l_rec_period_of_service_id = l_period_of_service_id then
1073
1074 hr_utility.set_location(l_proc, 210);
1075
1076 if l_latest_aggr_start_date is not null then
1077 hr_utility.set_location(l_proc, 220);
1078 --
1079 -- to check whther the given assignment present between
1080 -- the earliest aggregation start date and manual issue date
1081 --
1082 open csr_asg_present_status(r_rec.assignment_id, l_latest_aggr_start_date, l_effective_date);
1083 fetch csr_asg_present_status into l_dummy;
1084 l_found := csr_asg_present_status%found;
1085 close csr_asg_present_status;
1086 --
1087 end if;
1088
1089 if l_found then
1090 hr_utility.set_location(l_proc, 230);
1091 --
1092 -- extra info id null then insert only the override date
1093 if r_rec.assignment_extra_info_id is null then
1094 hr_utility.set_location(l_proc, 240);
1095 hr_assignment_extra_info_api.create_assignment_extra_info
1096 (p_validate => p_validate
1097 ,p_assignment_id => r_rec.assignment_id
1098 ,p_information_type => l_information_type
1099 ,p_aei_information_category => p_aei_information_category
1100 ,p_aei_information1 => null
1101 ,p_aei_information2 => null
1102 ,p_aei_information3 => p_aei_information3
1103 ,p_aei_information4 => null
1104 ,p_object_version_number => l_object_version_number
1105 ,p_assignment_extra_info_id => l_assignment_extra_info_id);
1106
1107 -- extra info id not null and override date is null then update
1108 elsif r_rec.assignment_extra_info_id is not null and r_rec.aei_information3 is null then
1109 hr_utility.set_location(l_proc, 250);
1110 l_object_version_number := r_rec.ovn;
1111 hr_assignment_extra_info_api.update_assignment_extra_info
1112 (p_validate => p_validate
1113 ,p_assignment_extra_info_id => r_rec.assignment_extra_info_id
1114 ,p_object_version_number => l_object_version_number
1115 ,p_aei_information_category => r_rec.aei_information_category
1116 ,p_aei_information1 => r_rec.aei_information1
1117 ,p_aei_information2 => r_rec.aei_information2
1118 ,p_aei_information3 => p_aei_information3
1119 ,p_aei_information4 => r_rec.aei_information4);
1120
1121 end if;
1122 --
1123 end if;
1124 end if; -- paye reference, period of service id same
1125 end loop;
1126 end if; -- PAYE as 'Y'
1127 end if; -- old and new manual issue date are different
1128 -- manual issue date updation ends
1129 --
1130
1131
1132 hr_utility.set_location(l_proc, 300);
1133
1134 -- the entered override date, old override date are different
1135 -- then will have to clear(update as null) all the agg. asg EIT's associated at old override date and
1136 -- update agg asg's extra info as of new override date
1137 if nvl(l_old_aei_information4,'X') <> nvl(p_aei_information4,'X') then
1138 hr_utility.set_location(l_proc, 310);
1139
1140 --
1141 -- considering the manual issue date as the effective date
1142 --
1143 l_old_effective_date := fnd_date.canonical_to_date(l_old_aei_information4);
1144 --
1145
1146 hr_utility.set_location(l_proc, 320);
1147 --
1148 open csr_aggr_paye_flag(p_person_id, l_old_effective_date);
1149 fetch csr_aggr_paye_flag into l_old_aggregated_paye_flag;
1150 close csr_aggr_paye_flag;
1151 --
1152
1153 -- Aggregated PAYE, loop through agg assignments in
1154 --
1155 if nvl(l_old_aggregated_paye_flag,'X') = 'Y' and l_old_effective_date is not null then
1156 hr_utility.set_location(l_proc, 330);
1157 -- Aggregated paye, so loop through active/suspended assignments
1158 -- in old Tax District, and update manual issue date as null
1159 -- for each row
1160 open tax_district(p_assignment_id, l_old_effective_date);
1161 fetch tax_district into l_old_asg_tax_dist, l_old_period_of_service_id;
1162 close tax_district;
1163 --
1164
1165 --
1166 -- to fetch the latest aggregation start date near to old override date.
1167 --
1168 l_old_latest_aggr_start_date := null;
1169 open csr_latest_aggr_start_date(p_person_id, l_old_effective_date);
1170 fetch csr_latest_aggr_start_date into l_old_latest_aggr_start_date;
1171 close csr_latest_aggr_start_date;
1172 --
1173
1174 hr_utility.set_location(l_proc, 340);
1175 --
1176 -- fetching all the agg asg's based on the old override date
1177 --
1181 l_old_period_of_service_id)
1178 for r_rec in csr_per_agg_asg_extra(p_person_id, l_old_asg_tax_dist,
1179 l_old_effective_date,
1180 l_information_type, p_assignment_id,
1182 loop
1183 hr_utility.set_location(l_proc, 350);
1184 --
1185 -- fetch the last active/susp status of the r_rec assignemnt
1186 --
1187 l_asg_last_active_date := null;
1188 open csr_asg_last_active_date(r_rec.assignment_id);
1189 fetch csr_asg_last_active_date into l_asg_last_active_date;
1190 close csr_asg_last_active_date;
1191 --
1192
1193 --
1194 -- fetch the tax reference and period of service id for the r_rec asg
1195 -- on the last active/susp status date
1196 --
1197 open tax_district(r_rec.assignment_id, l_asg_last_active_date);
1201 --
1198 fetch tax_district into l_rec_asg_tax_dist, l_rec_period_of_service_id;
1199 l_found := tax_district%found;
1200 close tax_district;
1202
1203 if l_found and l_rec_asg_tax_dist = l_old_asg_tax_dist and
1204 l_rec_period_of_service_id = l_old_period_of_service_id then
1205
1206 hr_utility.set_location(l_proc, 360);
1207
1208 if l_old_latest_aggr_start_date is not null then
1209 hr_utility.set_location(l_proc, 370);
1210 --
1211 -- to check whther the given assignment present between
1212 -- the earliest aggregation start date and old override date
1213 --
1214 open csr_asg_present_status(r_rec.assignment_id, l_old_latest_aggr_start_date, l_old_effective_date);
1215 fetch csr_asg_present_status into l_dummy;
1216 l_found := csr_asg_present_status%found;
1217 close csr_asg_present_status;
1218 --
1219 end if;
1220
1221 if l_found then
1222 hr_utility.set_location(l_proc, 380);
1223
1224 --
1225 -- if extra info id already exists for the asg and override date is same, then update as null
1226 --
1227 if r_rec.assignment_extra_info_id is not null and r_rec.aei_information4 = l_old_aei_information4 then
1228 hr_utility.set_location(l_proc, 390);
1229
1230 l_object_version_number := r_rec.ovn;
1231 hr_assignment_extra_info_api.update_assignment_extra_info
1232 (p_validate => p_validate
1233 ,p_assignment_extra_info_id => r_rec.assignment_extra_info_id
1234 ,p_object_version_number => l_object_version_number
1235 ,p_aei_information_category => r_rec.aei_information_category
1236 ,p_aei_information1 => r_rec.aei_information1
1237 ,p_aei_information2 => r_rec.aei_information2
1238 ,p_aei_information3 => r_rec.aei_information3
1239 ,p_aei_information4 => null);
1240 end if;
1241 --
1242 end if;
1243 end if; -- paye reference, period of service id same
1244 end loop;
1245 end if;
1246
1247 hr_utility.set_location(l_proc, 400);
1248
1249 --
1250 -- considering the entered override date as the effective date
1251 --
1252 l_effective_date := fnd_date.canonical_to_date(p_aei_information4);
1253 --
1254
1255 hr_utility.set_location(l_proc, 410);
1256 --
1257 open csr_aggr_paye_flag(p_person_id, l_effective_date);
1258 fetch csr_aggr_paye_flag into l_aggregated_paye_flag;
1259 close csr_aggr_paye_flag;
1260 --
1261
1262 hr_utility.set_location(l_proc, 420);
1263
1264 if nvl(l_aggregated_paye_flag,'X') = 'Y' and l_effective_date is not null then -- PAYE as 'Y'
1265
1266 -- Aggregated PAYE, loop through agg assignments in
1267 -- current tax district
1268 hr_utility.set_location(l_proc, 430);
1269
1270 -- Aggregated paye, so loop through active assignments
1271 -- in current Tax District, and insert a row for each.
1272 open tax_district(p_assignment_id, l_effective_date);
1273 fetch tax_district into l_asg_tax_dist, l_period_of_service_id;
1274 close tax_district;
1275 --
1276
1277 --
1278 -- to fetch the latest aggregation start date near to override date.
1279 --
1280 l_latest_aggr_start_date := null;
1281 open csr_latest_aggr_start_date(p_person_id, l_effective_date);
1282 fetch csr_latest_aggr_start_date into l_latest_aggr_start_date;
1283 close csr_latest_aggr_start_date;
1284 --
1285
1286 hr_utility.set_location(l_proc, 440);
1287
1288 --
1289 -- if extra info already exists for the asg and override date is null then update
1290 -- if extra info not found for the asg then insert extra info for that asg.
1291 --
1292 for r_rec in csr_per_agg_asg_extra(p_person_id, l_asg_tax_dist,
1293 l_effective_date, l_information_type
1294 , p_assignment_id, l_period_of_service_id) loop
1295 hr_utility.set_location(l_proc, 450);
1296 --
1297 -- fetch the last active/susp status of the r_rec assignemnt
1298 --
1299 l_asg_last_active_date := null;
1300 open csr_asg_last_active_date(r_rec.assignment_id);
1301 fetch csr_asg_last_active_date into l_asg_last_active_date;
1302 close csr_asg_last_active_date;
1303 --
1304
1305 --
1306 -- fetch the tax reference and period of service id for the r_rec asg
1307 -- on the last active/susp status date
1308 --
1309 open tax_district(r_rec.assignment_id, l_asg_last_active_date);
1310 fetch tax_district into l_rec_asg_tax_dist, l_rec_period_of_service_id;
1311 l_found := tax_district%found;
1312 close tax_district;
1313 --
1314
1315 if l_found and l_rec_asg_tax_dist = l_asg_tax_dist and
1316 l_rec_period_of_service_id = l_period_of_service_id then
1317
1318 hr_utility.set_location(l_proc, 460);
1319
1320 if l_latest_aggr_start_date is not null then
1321 hr_utility.set_location(l_proc, 470);
1322 --
1326 open csr_asg_present_status(r_rec.assignment_id, l_latest_aggr_start_date, l_effective_date);
1327 fetch csr_asg_present_status into l_dummy;
1328 l_found := csr_asg_present_status%found;
1329 close csr_asg_present_status;
1330 --
1331 end if;
1332
1333 if l_found then
1334 hr_utility.set_location(l_proc, 480);
1335
1336 --
1337 -- extra info id null then insert only the override date
1338 if r_rec.assignment_extra_info_id is null then
1339 hr_utility.set_location(l_proc, 490);
1340 hr_assignment_extra_info_api.create_assignment_extra_info
1341 (p_validate => p_validate
1342 ,p_assignment_id => r_rec.assignment_id
1343 ,p_information_type => l_information_type
1344 ,p_aei_information_category => p_aei_information_category
1345 ,p_aei_information1 => null
1346 ,p_aei_information2 => null
1347 ,p_aei_information3 => null
1348 ,p_aei_information4 => p_aei_information4
1349 ,p_object_version_number => l_object_version_number
1350 ,p_assignment_extra_info_id => l_assignment_extra_info_id);
1351
1352 -- extra info id not null and override date is null then update
1353 elsif r_rec.assignment_extra_info_id is not null and r_rec.aei_information4 is null then
1354 hr_utility.set_location(l_proc, 500);
1355 l_object_version_number := r_rec.ovn;
1356 hr_assignment_extra_info_api.update_assignment_extra_info
1357 (p_validate => p_validate
1358 ,p_assignment_extra_info_id => r_rec.assignment_extra_info_id
1359 ,p_object_version_number => l_object_version_number
1360 ,p_aei_information_category => r_rec.aei_information_category
1361 ,p_aei_information1 => r_rec.aei_information1
1362 ,p_aei_information2 => r_rec.aei_information2
1363 ,p_aei_information3 => r_rec.aei_information3
1364 ,p_aei_information4 => p_aei_information4);
1365
1366 end if;
1367 --
1368 end if;
1369 end if; -- paye reference, period of service id same
1370 end loop;
1371 end if; -- PAYE as 'Y'
1372 end if; -- old and new override are different
1373
1374 hr_utility.set_location(' Leaving:'||l_proc, 600);
1375 -- hr_utility.trace_off;
1376 end pay_gb_upd_p45_info;
1377 -- -----------------------------------------------------------------------
1378 -- |-------------------------< pay_gb_del_p45_info>-----------------------|
1379 -- -----------------------------------------------------------------------
1380 procedure pay_gb_del_p45_info
1381 (p_validate in boolean default false
1382 ,p_assignment_extra_info_id in number
1383 ,p_business_group_id in number
1384 ,p_object_version_number in number
1385 )is
1386 --
1387 -- Declare cursors and local variables
1388 --
1389 l_legislation_code varchar2(2);
1390 l_proc varchar2(72) := g_package||'pay_gb_del_p45_info';
1391 l_asg_tax_dist varchar2(50);
1392 l_person_id number;
1393 --
1394 cursor csr_bg is
1395 select legislation_code
1398 --
1323 -- to check whther the given assignment present between
1324 -- the earliest aggregation start date and override date
1325 --
1396 from per_business_groups pbg
1397 where pbg.business_group_id = p_business_group_id;
1399
1400 --
1401 cursor csr_aggr_paye_flag (c_person_id in number,
1402 c_effective_date in date) is
1403 select per_information10
1404 from per_all_people_f
1405 where person_id = c_person_id
1406 and c_effective_date between
1407 effective_start_date and effective_end_date;
1408 --
1409
1410 l_effective_date date;
1411 l_aggregated_paye_flag per_all_people_f.per_information10%type;
1412 l_period_of_service_id per_all_assignments_f.period_of_service_id%type;
1413 l_assignment_id per_all_assignments_f.assignment_id%type;
1414 l_object_version_number number;
1415
1416 l_aei_information3 per_assignment_extra_info.aei_information3%type;
1417 l_aei_information4 per_assignment_extra_info.aei_information4%type;
1418 l_information_type per_assignment_extra_info.information_type%type;
1419
1420 --
1421 cursor csr_aei_info(c_assignment_extra_info_id number) is
1422 select aei_information3, aei_information4, information_type, assignment_id
1423 from per_assignment_extra_info
1424 where assignment_extra_info_id = c_assignment_extra_info_id;
1425 --
1426
1427 --
1428 CURSOR tax_district(c_assignment_id in number,
1429 c_effective_date in date) IS
1430 SELECT hsck.segment1, period_of_service_id, person_id
1431 FROM hr_soft_coding_keyflex hsck,
1432 pay_all_payrolls_f papf,
1433 per_all_assignments_f paaf
1434 WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
1435 AND papf.payroll_id = paaf.payroll_id
1436 AND paaf.assignment_id = c_assignment_id
1437 AND c_effective_date between
1438 papf.effective_start_date and papf.effective_end_date
1439 AND c_effective_date between
1440 paaf.effective_start_date and paaf.effective_end_date;
1441 --
1442
1443 --
1444 -- to fetch all the aggregated assignments and corresponding extra info
1445 -- based on old effective date. if the extra info id is not null then update information as null
1446 -- if both are same, effective date and manual issue date/override date; else no need to update.
1447 -- based on new effective date, if the extra info id is null then insert else update
1448 -- except the current assignment; will delete separately after fetching the old values
1449 --
1450 cursor csr_per_agg_asg_extra (c_person_id in number,
1451 c_tax_ref in varchar2,
1452 c_effective_date in date,
1453 c_information_type in varchar2,
1454 c_assignment_id in number,
1455 c_period_of_service_id in number) is
1456 select distinct
1457 a.assignment_id,
1458 extra.assignment_extra_info_id,
1459 extra.object_version_number ovn,
1460 extra.aei_information_category,
1461 extra.aei_information1,
1462 extra.aei_information2,
1463 extra.aei_information3,
1464 extra.aei_information4
1465 from per_all_assignments_f a,
1466 pay_all_payrolls_f pay,
1467 hr_soft_coding_keyflex flex,
1468 per_assignment_status_types past,
1469 per_assignment_extra_info extra
1470 where a.person_id = c_person_id
1471 and flex.segment1 = c_tax_ref
1472 and pay.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
1473 and a.payroll_id = pay.payroll_id
1474 and extra.assignment_id(+) = a.assignment_id
1475 and extra.information_type(+) = c_information_type
1476 and a.assignment_status_type_id = past.assignment_status_type_id
1477 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
1478 and a.period_of_service_id = c_period_of_service_id
1479 and c_effective_date between
1480 pay.effective_start_date and pay.effective_end_date
1481 and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_effective_date)
1482 and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_effective_date)
1483 and a.assignment_id <> c_assignment_id
1484 ;
1485 --
1486 -- to fetch the last active/susp status date for the given assignment
1487 --
1488 cursor csr_asg_last_active_date(c_assignment_id number) is
1489 select max(effective_end_date)
1490 from per_all_assignments_f a,
1491 per_assignment_status_types past
1492 where a.assignment_id = c_assignment_id
1493 and a.assignment_status_type_id = past.assignment_status_type_id
1494 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
1495
1496 --
1497 -- to fetch the earliest aggregation start date near to the manual issue date/override date.
1498 --
1499 cursor csr_latest_aggr_start_date(c_person_id number, c_effective_date date) is
1500 select max(effective_end_date) + 1
1501 from per_all_people_f
1502 where person_id = c_person_id
1503 and nvl(per_information10,'N') = 'N'
1504 and effective_end_date < c_effective_date;
1505
1506 --
1507 -- to check whether the given assignment present between
1508 -- the earliest aggregation start date and manual issue date/override date.
1509 --
1510 cursor csr_asg_present_status(c_assignment_id number, c_start_date date, c_end_date date) is
1511 select 1
1512 from per_all_assignments_f a
1513 where a.assignment_id = c_assignment_id
1514 and a.effective_end_date >= c_start_date
1515 and a.effective_start_date <= c_end_date;
1516
1517 l_found boolean;
1518 l_dummy number;
1519 l_asg_last_active_date date;
1520 l_rec_asg_tax_dist varchar2(50);
1521 l_rec_period_of_service_id number;
1522 l_latest_aggr_start_date date;
1523 --
1524
1525 begin
1526 -- hr_utility.trace_on(null, 'ARUL');
1527 hr_utility.set_location('Entering:'|| l_proc, 10);
1528 -- Validation in addition to Row Handlers
1529 --
1530 -- Check that the specified business group is valid.
1531 --
1532 open csr_bg;
1533 fetch csr_bg
1534 into l_legislation_code;
1535 if csr_bg%notfound then
1536 close csr_bg;
1537 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1538 hr_utility.raise_error;
1539 end if;
1540 close csr_bg;
1541 --
1542 hr_utility.set_location(l_proc,20);
1543
1544 --
1545 -- Check that the legislation of the specified business group is 'GB'.
1546 --
1547 if l_legislation_code <> 'GB' then
1548 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
1549 hr_utility.set_message_token('LEG_CODE','GB');
1550 hr_utility.raise_error;
1551 end if;
1552 --
1553 hr_utility.trace('p_assignment_extra_info_id = ' || p_assignment_extra_info_id);
1554
1555 hr_utility.set_location(l_proc, 30);
1556 --
1557 -- fething the manual issue date, extra information type, assignment id
1558 --
1559 open csr_aei_info(p_assignment_extra_info_id);
1560 fetch csr_aei_info into l_aei_information3, l_aei_information4, l_information_type, l_assignment_id;
1561 close csr_aei_info;
1562 --
1563
1564 hr_utility.set_location(l_proc, 40);
1565
1566 -- delete the current asg extra info details separately
1567 --
1568 hr_assignment_extra_info_api.delete_assignment_extra_info
1569 (p_validate => false,
1570 p_assignment_extra_info_id => p_assignment_extra_info_id,
1571 p_object_version_number => p_object_version_number);
1572 -- deletion for the current asg extra info ends
1573
1574
1575 -- first update EIT info based on the manual issue date then
1576 -- continue the same based on override date
1577
1578 hr_utility.set_location(l_proc, 50);
1579 --
1580 -- considering the manual issue date as the effective date
1581 --
1582 l_effective_date := fnd_date.canonical_to_date(l_aei_information3);
1583 --
1584
1585 hr_utility.set_location(l_proc, 60);
1586 --
1587 -- fetch the tax district, period of servive id and persion id
1588 -- from the given asg extra info id
1589 --
1590 open tax_district(l_assignment_id, l_effective_date);
1591 fetch tax_district into l_asg_tax_dist, l_period_of_service_id, l_person_id;
1592 close tax_district;
1593 --
1594
1595 hr_utility.set_location(l_proc, 70);
1596 --
1597 -- fetching the Agg. PAYE flag value on the effective date
1598 --
1599 open csr_aggr_paye_flag(l_person_id, l_effective_date);
1600 fetch csr_aggr_paye_flag into l_aggregated_paye_flag;
1601 close csr_aggr_paye_flag;
1602 --
1603
1604 hr_utility.set_location(l_proc, 80);
1605 --
1606 -- When PAYE as 'Y' then update all the agg asg extra info manual issue date as null
1607 -- if the manual issue date is same with current asg' manual issue date
1608 --
1609 if nvl(l_aggregated_paye_flag,'X') = 'Y' and l_effective_date is not null then
1610 -- Aggregated PAYE, loop through agg assignments in
1611 -- current tax district
1612 --
1613 hr_utility.set_location(l_proc, 90);
1614
1615 --
1616 -- to fetch the latest aggregation start date near to manual issue date.
1617 --
1618 l_latest_aggr_start_date := null;
1619 open csr_latest_aggr_start_date(l_person_id, l_effective_date);
1620 fetch csr_latest_aggr_start_date into l_latest_aggr_start_date;
1621 close csr_latest_aggr_start_date;
1622 --
1623
1624 --
1625 -- fetching all the agg asg extra info details except the current asg extra info
1626 --
1627 for r_rec in csr_per_agg_asg_extra(l_person_id, l_asg_tax_dist,
1628 l_effective_date, l_information_type,
1629 l_assignment_id, l_period_of_service_id) loop
1630 hr_utility.set_location(l_proc, 100);
1631 --
1632 -- fetch the last active/susp status of the r_rec assignemnt
1633 --
1634 l_asg_last_active_date := null;
1635 open csr_asg_last_active_date(r_rec.assignment_id);
1636 fetch csr_asg_last_active_date into l_asg_last_active_date;
1637 close csr_asg_last_active_date;
1638 --
1639
1640 --
1641 -- fetch the tax reference and period of service id for the r_rec asg
1642 -- on the last active/susp status date
1643 --
1644 open tax_district(r_rec.assignment_id, l_asg_last_active_date);
1645 fetch tax_district into l_rec_asg_tax_dist, l_rec_period_of_service_id, l_person_id;
1646 l_found := tax_district%found;
1647 close tax_district;
1648 --
1649
1650 if l_found and l_rec_asg_tax_dist = l_asg_tax_dist and
1651 l_rec_period_of_service_id = l_period_of_service_id then
1652
1653 hr_utility.set_location(l_proc, 110);
1654
1655 if l_latest_aggr_start_date is not null then
1656 hr_utility.set_location(l_proc, 120);
1657 --
1658 -- to check whther the given assignment present between
1659 -- the earliest aggregation start date and manual issue date
1660 --
1661 open csr_asg_present_status(r_rec.assignment_id, l_latest_aggr_start_date, l_effective_date);
1662 fetch csr_asg_present_status into l_dummy;
1663 l_found := csr_asg_present_status%found;
1664 close csr_asg_present_status;
1665 --
1666 end if;
1667
1668 if l_found then
1669 hr_utility.set_location(l_proc, 130);
1670
1671 --
1672 -- if extra info id not null and manual issue date is same as current manual issue date then update as null
1673 --
1674 if r_rec.assignment_extra_info_id is not null and r_rec.aei_information3 = l_aei_information3 then
1675 hr_utility.set_location(l_proc, 140);
1676 l_object_version_number := r_rec.ovn;
1677 hr_assignment_extra_info_api.update_assignment_extra_info
1678 (p_validate => p_validate
1679 ,p_assignment_extra_info_id => r_rec.assignment_extra_info_id
1680 ,p_object_version_number => l_object_version_number
1681 ,p_aei_information_category => r_rec.aei_information_category
1682 ,p_aei_information1 => r_rec.aei_information1
1683 ,p_aei_information2 => r_rec.aei_information2
1684 ,p_aei_information3 => null
1685 ,p_aei_information4 => r_rec.aei_information4);
1686 end if;
1687 --
1688 end if;
1689 end if; -- paye reference, period of service id same
1690 end loop;
1691 --
1692 end if; -- PAYE as 'Y'
1693 --
1694
1695
1696 hr_utility.set_location(l_proc, 150);
1697 --
1698 -- considering the override date as the effective date
1699 --
1700 l_effective_date := fnd_date.canonical_to_date(l_aei_information4);
1701 --
1702
1703 hr_utility.set_location(l_proc, 160);
1704 --
1705 -- fetch the tax district, period of servive id and persion id
1706 -- from the given asg extra info id
1707 --
1708 open tax_district(l_assignment_id, l_effective_date);
1709 fetch tax_district into l_asg_tax_dist, l_period_of_service_id, l_person_id;
1710 close tax_district;
1711 --
1712
1713 hr_utility.set_location(l_proc, 170);
1714 --
1715 -- fetching the Agg. PAYE flag value on the effective date
1716 --
1717 open csr_aggr_paye_flag(l_person_id, l_effective_date);
1718 fetch csr_aggr_paye_flag into l_aggregated_paye_flag;
1719 close csr_aggr_paye_flag;
1720 --
1721
1722 hr_utility.set_location(l_proc, 180);
1723 --
1724 -- When PAYE as 'Y' then update all the agg asg extra info override date as null
1725 -- if the override date is same with current asg' override date
1726 --
1727 if nvl(l_aggregated_paye_flag,'X') = 'Y' and l_effective_date is not null then
1728 -- Aggregated PAYE, loop through agg assignments in
1729 -- current tax district
1730 --
1731 hr_utility.set_location(l_proc, 190);
1732
1733 --
1734 -- to fetch the latest aggregation start date near to override date.
1738 fetch csr_latest_aggr_start_date into l_latest_aggr_start_date;
1735 --
1736 l_latest_aggr_start_date := null;
1737 open csr_latest_aggr_start_date(l_person_id, l_effective_date);
1739 close csr_latest_aggr_start_date;
1740 --
1741
1742 --
1743 -- fetching all the agg asg extra info details except the current asg extra info
1744 --
1745 for r_rec in csr_per_agg_asg_extra(l_person_id, l_asg_tax_dist,
1746 l_effective_date, l_information_type,
1747 l_assignment_id, l_period_of_service_id) loop
1748 --
1749 -- fetch the last active/susp status of the r_rec assignemnt
1750 --
1751 l_asg_last_active_date := null;
1752 open csr_asg_last_active_date(r_rec.assignment_id);
1753 fetch csr_asg_last_active_date into l_asg_last_active_date;
1754 close csr_asg_last_active_date;
1755 --
1756
1757 --
1758 -- fetch the tax reference and period of service id for the r_rec asg
1759 -- on the last active/susp status date
1760 --
1761 open tax_district(r_rec.assignment_id, l_asg_last_active_date);
1762 fetch tax_district into l_rec_asg_tax_dist, l_rec_period_of_service_id, l_person_id;
1763 l_found := tax_district%found;
1764 close tax_district;
1765 --
1766
1767 if l_found and l_rec_asg_tax_dist = l_asg_tax_dist and
1768 l_rec_period_of_service_id = l_period_of_service_id then
1769
1770 hr_utility.set_location(l_proc, 200);
1771
1772 if l_latest_aggr_start_date is not null then
1773 hr_utility.set_location(l_proc, 210);
1774 --
1775 -- to check whther the given assignment present between
1776 -- the earliest aggregation start date and manual issue date
1777 --
1778 open csr_asg_present_status(r_rec.assignment_id, l_latest_aggr_start_date, l_effective_date);
1779 fetch csr_asg_present_status into l_dummy;
1780 l_found := csr_asg_present_status%found;
1781 close csr_asg_present_status;
1782 --
1783 end if;
1784
1785 if l_found then
1786 hr_utility.set_location(l_proc, 220);
1787
1788 --
1789 -- if extra info id not null and override date is same as current override date then update as null
1790 --
1791 if r_rec.assignment_extra_info_id is not null and r_rec.aei_information4 = l_aei_information4 then
1792 hr_utility.set_location(l_proc, 230);
1793 l_object_version_number := r_rec.ovn;
1794 hr_assignment_extra_info_api.update_assignment_extra_info
1795 (p_validate => p_validate
1796 ,p_assignment_extra_info_id => r_rec.assignment_extra_info_id
1797 ,p_object_version_number => l_object_version_number
1798 ,p_aei_information_category => r_rec.aei_information_category
1799 ,p_aei_information1 => r_rec.aei_information1
1800 ,p_aei_information2 => r_rec.aei_information2
1801 ,p_aei_information3 => r_rec.aei_information3
1802 ,p_aei_information4 => null);
1803 end if;
1804 --
1805 end if;
1806 end if; -- paye reference, period of service id same
1807 end loop;
1808 --
1809 end if; -- PAYE as 'Y'
1810 --
1811
1812 hr_utility.set_location(' Leaving:'||l_proc, 300);
1813 -- hr_utility.trace_off;
1814 end pay_gb_del_p45_info;
1815 -- --------------------------------------------------------------------
1816 -- |-------------------------< pay_gb_ins_p46>-----------------------|
1817 -- --------------------------------------------------------------------
1818 /* BUG 1843915 Added parameter p_aei_information3 for
1819 passing value of P46_SEND_EDI_FLAG */
1820 procedure pay_gb_ins_p46
1821 (p_validate in boolean default false
1822 ,p_assignment_id in number
1823 ,p_business_group_id in number
1824 ,p_information_type in varchar2
1825 ,p_aei_information_category in varchar2 default null
1826 ,p_aei_information1 in varchar2 default null
1827 ,p_aei_information2 in varchar2 default null
1828 ,p_aei_information3 in varchar2 default null
1829 ,p_aei_information4 in varchar2 default null
1830 ,p_aei_information5 in varchar2 default null
1831 ,p_aei_information6 in varchar2 default null
1832 ,p_object_version_number out nocopy number
1833 ,p_assignment_extra_info_id out nocopy number
1834 ) is
1835 --
1836 -- Declare cursors and local variables
1837 --
1838 l_legislation_code varchar2(2);
1839 l_proc varchar2(72) := g_package||'pay_gb_ins_p46';
1840 --
1841 cursor csr_bg is
1842 select legislation_code
1843 from per_business_groups pbg
1844 where pbg.business_group_id = p_business_group_id;
1845 --
1846 begin
1847 hr_utility.set_location('Entering:'|| l_proc, 10);
1848 --
1849 -- Validation in addition to Row Handlers
1850 --
1851 -- Check that the specified business group is valid.
1852 --
1853 open csr_bg;
1854 fetch csr_bg
1855 into l_legislation_code;
1856 if csr_bg%notfound then
1857 close csr_bg;
1858 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1859 hr_utility.raise_error;
1860 end if;
1861 close csr_bg;
1862 --
1863 -- Check that the legislation of the specified business group is 'GB'.
1864 --
1865 if l_legislation_code <> 'GB' then
1866 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
1867 hr_utility.set_message_token('LEG_CODE','GB');
1868 hr_utility.raise_error;
1869 end if;
1870
1871 hr_utility.set_location(l_proc, 6);
1872 -- Bug 3454500 check for Send EDI flag
1873 if (p_aei_information3 is null and p_aei_information6 is null) then
1874 hr_utility.set_message(800, 'HR_GB_78120_MISSING_EDI_FLAG');
1875 hr_utility.set_message_token('TYPE','P46');
1876 hr_utility.raise_error;
1877 end if;
1878 --
1879 -- Call the Assignment Extra Information Business API
1880 /* BUG 1843915 Added parameter p_aei_information3 for
1881 passing value of P46_SEND_EDI_FLAG */
1882 hr_assignment_extra_info_api.create_assignment_extra_info
1883 (p_validate => p_validate
1884 ,p_assignment_id => p_assignment_id
1885 ,p_information_type => p_information_type
1886 ,p_aei_information_category => p_aei_information_category
1887 ,p_aei_information1 => p_aei_information1
1888 ,p_aei_information2 => p_aei_information2
1889 ,p_aei_information3 => p_aei_information3
1890 ,p_aei_information4 => p_aei_information4
1891 ,p_aei_information5 => p_aei_information5
1892 ,p_aei_information6 => p_aei_information6
1893 ,p_object_version_number => p_object_version_number
1897 end pay_gb_ins_p46;
1894 ,p_assignment_extra_info_id => p_assignment_extra_info_id);
1895
1896 hr_utility.set_location(' Leaving:'||l_proc, 40);
1898 --
1899 --
1900 --
1901 -- ----------------------------------------------------------------------------
1902 -- |-------------------------< pay_gb_upd_p46>-----------------------|
1903 -- ----------------------------------------------------------------------------
1904 /* BUG 1843915 Added parameter p_aei_information3 for
1905 passing value of P46_SEND_EDI_FlAG */
1906 procedure pay_gb_upd_p46
1907 (p_validate in boolean default false
1908 ,p_assignment_extra_info_id in number
1909 ,p_business_group_id in number
1910 ,p_object_version_number in out nocopy number
1911 ,p_aei_information_category in varchar2 default null
1912 ,p_aei_information1 in varchar2 default null
1913 ,p_aei_information2 in varchar2 default null
1914 ,p_aei_information3 in varchar2 default null
1915 ,p_aei_information4 in varchar2 default null
1916 ,p_aei_information5 in varchar2 default null
1917 ,p_aei_information6 in varchar2 default null
1918 ) is
1919 --
1920 -- Declare cursors and local variables
1921 --
1922 l_legislation_code varchar2(2);
1923 l_proc varchar2(72) := g_package||'pay_gb_upd_p46';
1924 --
1925 cursor csr_bg is
1926 select legislation_code
1927 from per_business_groups pbg
1928 where pbg.business_group_id = p_business_group_id;
1929 --
1930 begin
1931 hr_utility.set_location('Entering:'|| l_proc, 10);
1932 --
1933 -- Validation in addition to Row Handlers
1934 --
1935 -- Check that the specified business group is valid.
1936 --
1937 open csr_bg;
1938 fetch csr_bg
1939 into l_legislation_code;
1940 if csr_bg%notfound then
1941 close csr_bg;
1942 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1943 hr_utility.raise_error;
1944 end if;
1945 close csr_bg;
1946 --
1947 -- Check that the legislation of the specified business group is 'GB'.
1948 --
1949 if l_legislation_code <> 'GB' then
1950 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
1951 hr_utility.set_message_token('LEG_CODE','GB');
1952 hr_utility.raise_error;
1953 end if;
1954
1955 hr_utility.set_location(l_proc, 6);
1956 --
1957 -- Call the Assignment Extra Information Business API
1958 --
1959 /* BUG 1843915 Added parameter p_aei_information3 for
1960 passing value of P46_SEND_EDI_FlAG */
1961 hr_assignment_extra_info_api.update_assignment_extra_info
1962 (p_validate => p_validate
1963 ,p_assignment_extra_info_id => p_assignment_extra_info_id
1964 ,p_object_version_number => p_object_version_number
1965 ,p_aei_information_category => p_aei_information_category
1966 ,p_aei_information1 => p_aei_information1
1967 ,p_aei_information2 => p_aei_information2
1968 ,p_aei_information3 => p_aei_information3
1969 ,p_aei_information4 => p_aei_information4
1970 ,p_aei_information5 => p_aei_information5
1971 ,p_aei_information6 => p_aei_information6
1972 );
1973 --
1974 hr_utility.set_location(' Leaving:'||l_proc, 40);
1975 end pay_gb_upd_p46;
1976 --
1977 -- ----------------------------------------------------------------------------
1978 -- |-------------------------< pay_gb_ins_p46_pennot>-----------------------|
1979 -- ----------------------------------------------------------------------------
1980 -- Bug 1843915 Added the parameter p_aei_information4 to insert the column
1981 -- P46_PENNOT_SEND_EDI_FLAG
1982
1983 procedure pay_gb_ins_p46_pennot
1984 (p_validate in boolean default false
1985 ,p_assignment_id in number
1986 ,p_business_group_id in number
1987 ,p_information_type in varchar2
1988 ,p_aei_information_category in varchar2 default null
1989 ,p_aei_information1 in varchar2 default null
1990 ,p_aei_information2 in varchar2 default null
1991 ,p_aei_information3 in varchar2 default null
1992 ,p_aei_information4 in varchar2 default null
1993 ,p_aei_information5 in varchar2 default null
1994 ,p_aei_information6 in varchar2 default null
1995 ,p_aei_information7 in varchar2 default null
1996 ,p_aei_information8 in varchar2 default null
1997 ,p_aei_information9 in varchar2 default null
1998 ,p_aei_information10 in varchar2 default null
2002 ) is
1999 ,p_aei_information11 in varchar2 default null
2000 ,p_object_version_number out nocopy number
2001 ,p_assignment_extra_info_id out nocopy number
2003 --
2004 -- Declare cursors and local variables
2005 --
2006 l_legislation_code varchar2(2);
2007 l_proc varchar2(72) := g_package||'pay_gb_ins_p46_pennot';
2008 --
2009 cursor csr_bg is
2010 select legislation_code
2011 from per_business_groups pbg
2012 where pbg.business_group_id = p_business_group_id;
2013 --
2014 begin
2015 hr_utility.set_location('Entering:'|| l_proc, 10);
2016 --
2017 -- Validation in addition to Row Handlers
2018 --
2019 -- Check that the specified business group is valid.
2020 --
2021 open csr_bg;
2022 fetch csr_bg
2023 into l_legislation_code;
2024 if csr_bg%notfound then
2025 close csr_bg;
2026 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2027 hr_utility.raise_error;
2028 end if;
2029 close csr_bg;
2030 --
2031 -- Check that the legislation of the specified business group is 'GB'.
2032 --
2033 if l_legislation_code <> 'GB' then
2034 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2035 hr_utility.set_message_token('LEG_CODE','GB');
2036 hr_utility.raise_error;
2037 end if;
2038
2039 hr_utility.set_location(l_proc, 6);
2040 -- Bug 3454500 check for Send EDI flag
2041 if (p_aei_information4 is null) then
2042 hr_utility.set_message(800, 'HR_GB_78120_MISSING_EDI_FLAG');
2043 hr_utility.set_message_token('TYPE','P46 Pension Notification');
2044 hr_utility.raise_error;
2045 end if;
2046 --
2047 -- Call the Assignment Extra Information Business API
2048 --
2049 -- Bug 1843915 Added the parameter p_aei_information4 to insert the column
2050 -- P46_PENNOT_SEND_EDI_FLAG
2051
2052 hr_assignment_extra_info_api.create_assignment_extra_info
2053 (p_validate => p_validate
2054 ,p_assignment_id => p_assignment_id
2055 ,p_information_type => p_information_type
2056 ,p_aei_information_category => p_aei_information_category
2057 ,p_aei_information1 => p_aei_information1
2058 ,p_aei_information2 => p_aei_information2
2059 ,p_aei_information3 => p_aei_information3
2060 ,p_aei_information4 => p_aei_information4
2061 ,p_aei_information5 => p_aei_information5
2062 ,p_aei_information6 => p_aei_information6
2063 ,p_aei_information7 => p_aei_information7
2064 ,p_aei_information8 => p_aei_information8
2065 ,p_aei_information9 => p_aei_information9
2066 ,p_aei_information10 => p_aei_information10
2067 ,p_aei_information11 => p_aei_information11
2068 ,p_object_version_number => p_object_version_number
2069 ,p_assignment_extra_info_id => p_assignment_extra_info_id);
2070 --
2071 hr_utility.set_location(' Leaving:'||l_proc, 40);
2072 end pay_gb_ins_p46_pennot;
2073 --
2074 --
2075 --
2076 -- ----------------------------------------------------------------------------
2077 -- |-------------------------< pay_gb_upd_p46_pennot>-----------------------|
2078 -- ----------------------------------------------------------------------------
2079 -- Bug 1843915 Added the parameter p_aei_information4 to insert the column
2080 -- P46_PENNOT_SEND_EDI_FLAG
2081
2082 procedure pay_gb_upd_p46_pennot
2083 (p_validate in boolean default false
2084 ,p_assignment_extra_info_id in number
2085 ,p_business_group_id in number
2086 ,p_object_version_number in out nocopy number
2087 ,p_aei_information_category in varchar2 default null
2088 ,p_aei_information1 in varchar2 default null
2089 ,p_aei_information2 in varchar2 default null
2090 ,p_aei_information3 in varchar2 default null
2091 ,p_aei_information4 in varchar2 default null
2092 ,p_aei_information5 in varchar2 default null
2093 ,p_aei_information6 in varchar2 default null
2094 ,p_aei_information7 in varchar2 default null
2095 ,p_aei_information8 in varchar2 default null
2096 ,p_aei_information9 in varchar2 default null
2097 ,p_aei_information10 in varchar2 default null
2098 ,p_aei_information11 in varchar2 default null
2099 )is
2100 --
2101 -- Declare cursors and local variables
2102 --
2103 l_legislation_code varchar2(2);
2104 l_proc varchar2(72) := g_package||'pay_gb_upd_p46_pennot';
2105 --
2106 cursor csr_bg is
2107 select legislation_code
2108 from per_business_groups pbg
2109 where pbg.business_group_id = p_business_group_id;
2110 --
2111 begin
2112 hr_utility.set_location('Entering:'|| l_proc, 10);
2113 --
2114 -- Validation in addition to Row Handlers
2115 --
2116 -- Check that the specified business group is valid.
2117 --
2118 hr_utility.set_location(l_proc, 20);
2119 open csr_bg;
2120 fetch csr_bg
2121 into l_legislation_code;
2122 if csr_bg%notfound then
2123 hr_utility.set_location(l_proc, 30);
2124 close csr_bg;
2125 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2126 hr_utility.raise_error;
2127 end if;
2128 close csr_bg;
2129 --
2130 -- Check that the legislation of the specified business group is 'GB'.
2131 --
2132 if l_legislation_code <> 'GB' then
2133 hr_utility.set_location(l_proc, 40);
2134 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2135 hr_utility.set_message_token('LEG_CODE','GB');
2139 hr_utility.set_location(l_proc, 50);
2136 hr_utility.raise_error;
2137 end if;
2138
2140 --
2141 -- Call the Assignment Extra Information Business API
2142 --
2143 -- Bug 1843915 Added the parameter p_aei_information4 to insert the column
2144 -- P46_PENNOT_SEND_EDI_FLAG
2145
2146 hr_assignment_extra_info_api.update_assignment_extra_info
2147 (p_validate => p_validate
2148 ,p_assignment_extra_info_id => p_assignment_extra_info_id
2149 ,p_object_version_number => p_object_version_number
2150 ,p_aei_information_category => p_aei_information_category
2151 ,p_aei_information1 => p_aei_information1
2152 ,p_aei_information2 => p_aei_information2
2153 ,p_aei_information3 => p_aei_information3
2154 ,p_aei_information4 => p_aei_information4
2155 ,p_aei_information5 => p_aei_information5
2156 ,p_aei_information6 => p_aei_information6
2157 ,p_aei_information7 => p_aei_information7
2158 ,p_aei_information8 => p_aei_information8
2159 ,p_aei_information9 => p_aei_information9
2160 ,p_aei_information10 => p_aei_information10
2161 ,p_aei_information11 => p_aei_information11);
2162 --
2163 hr_utility.set_location(' Leaving:'||l_proc, 60);
2164 end pay_gb_upd_p46_pennot;
2165 --
2166
2167 --P46(Expat):Added API procedures
2168 -- --------------------------------------------------------------------
2169 -- |-------------------------< pay_gb_ins_p46_expat>-----------------------|
2170 -- --------------------------------------------------------------------
2171 procedure pay_gb_ins_p46_expat
2172 (p_validate in boolean default false
2173 ,p_assignment_id in number
2174 ,p_business_group_id in number
2175 ,p_information_type in varchar2
2176 ,p_aei_information_category in varchar2 default null
2177 ,p_aei_information1 in varchar2 default null
2178 ,p_aei_information2 in varchar2 default null
2179 ,p_aei_information3 in varchar2 default null
2180 ,p_aei_information4 in varchar2 default null
2181 ,p_aei_information5 in varchar2 default null
2182 ,p_aei_information6 in varchar2 default null
2183 ,p_aei_information7 in varchar2 default null
2184 ,p_object_version_number out nocopy number
2185 ,p_assignment_extra_info_id out nocopy number
2186 ) is
2187 --
2188 -- Declare cursors and local variables
2189 --
2190 l_legislation_code varchar2(2);
2191 l_proc varchar2(72) := g_package||'pay_gb_ins_p46_expat';
2192 --
2193 cursor csr_bg is
2194 select legislation_code
2195 from per_business_groups pbg
2196 where pbg.business_group_id = p_business_group_id;
2197 --
2198 begin
2199 hr_utility.set_location('Entering:'|| l_proc, 10);
2200 --
2201 -- Validation in addition to Row Handlers
2202 --
2203 -- Check that the specified business group is valid.
2204 --
2205 open csr_bg;
2206 fetch csr_bg
2207 into l_legislation_code;
2208 if csr_bg%notfound then
2209 close csr_bg;
2210 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2211 hr_utility.raise_error;
2212 end if;
2213 close csr_bg;
2214 --
2215 -- Check that the legislation of the specified business group is 'GB'.
2216 --
2217 if l_legislation_code <> 'GB' then
2218 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2219 hr_utility.set_message_token('LEG_CODE','GB');
2220 hr_utility.raise_error;
2221 end if;
2222
2223 hr_utility.set_location(l_proc, 6);
2224 -- Bug 3454500 check for Send EDI flag
2225 if (p_aei_information3 is null) then
2226 hr_utility.set_message(800, 'HR_GB_78120_MISSING_EDI_FLAG');
2227 hr_utility.set_message_token('TYPE','P46');
2228 hr_utility.raise_error;
2229 end if;
2230 --
2231 -- Call the Assignment Extra Information Business API
2232
2233 hr_assignment_extra_info_api.create_assignment_extra_info
2234 (p_validate => p_validate
2235 ,p_assignment_id => p_assignment_id
2236 ,p_information_type => p_information_type
2237 ,p_aei_information_category => p_aei_information_category
2238 ,p_aei_information1 => p_aei_information1
2239 ,p_aei_information2 => p_aei_information2
2240 ,p_aei_information3 => p_aei_information3
2241 ,p_aei_information4 => p_aei_information4
2242 ,p_aei_information5 => p_aei_information5
2243 ,p_aei_information6 => p_aei_information6
2244 ,p_aei_information7 => p_aei_information7
2245 ,p_object_version_number => p_object_version_number
2246 ,p_assignment_extra_info_id => p_assignment_extra_info_id);
2247
2248 hr_utility.set_location(' Leaving:'||l_proc, 40);
2249 end pay_gb_ins_p46_expat;
2250 --
2251 --
2252 -- ----------------------------------------------------------------------------
2253 -- |-------------------------< pay_gb_upd_p46_expat>-----------------------|
2254 -- ----------------------------------------------------------------------------
2255 procedure pay_gb_upd_p46_expat
2256 (p_validate in boolean default false
2257 ,p_assignment_extra_info_id in number
2258 ,p_business_group_id in number
2259 ,p_object_version_number in out nocopy number
2260 ,p_aei_information_category in varchar2 default null
2261 ,p_aei_information1 in varchar2 default null
2262 ,p_aei_information2 in varchar2 default null
2263 ,p_aei_information3 in varchar2 default null
2264 ,p_aei_information4 in varchar2 default null
2268 ) is
2265 ,p_aei_information5 in varchar2 default null
2266 ,p_aei_information6 in varchar2 default null
2267 ,p_aei_information7 in varchar2 default null
2269 --
2270 -- Declare cursors and local variables
2271 --
2272 l_legislation_code varchar2(2);
2273 l_proc varchar2(72) := g_package||'pay_gb_upd_p46_expat';
2274 --
2275 cursor csr_bg is
2276 select legislation_code
2277 from per_business_groups pbg
2278 where pbg.business_group_id = p_business_group_id;
2279 --
2280 begin
2281 hr_utility.set_location('Entering:'|| l_proc, 10);
2282 --
2283 -- Validation in addition to Row Handlers
2284 --
2285 -- Check that the specified business group is valid.
2286 --
2287 open csr_bg;
2288 fetch csr_bg
2289 into l_legislation_code;
2290 if csr_bg%notfound then
2291 close csr_bg;
2292 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2293 hr_utility.raise_error;
2294 end if;
2295 close csr_bg;
2296 --
2297 -- Check that the legislation of the specified business group is 'GB'.
2298 --
2299 if l_legislation_code <> 'GB' then
2300 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2301 hr_utility.set_message_token('LEG_CODE','GB');
2302 hr_utility.raise_error;
2303 end if;
2304
2305 hr_utility.set_location(l_proc, 6);
2306 --
2307 -- Call the Assignment Extra Information Business API
2308 --
2309 hr_assignment_extra_info_api.update_assignment_extra_info
2310 (p_validate => p_validate
2311 ,p_assignment_extra_info_id => p_assignment_extra_info_id
2312 ,p_object_version_number => p_object_version_number
2313 ,p_aei_information_category => p_aei_information_category
2314 ,p_aei_information1 => p_aei_information1
2315 ,p_aei_information2 => p_aei_information2
2316 ,p_aei_information3 => p_aei_information3
2317 ,p_aei_information4 => p_aei_information4
2318 ,p_aei_information5 => p_aei_information5
2319 ,p_aei_information6 => p_aei_information6
2320 ,p_aei_information7 => p_aei_information7
2321 );
2322 --
2323 hr_utility.set_location(' Leaving:'||l_proc, 40);
2324 end pay_gb_upd_p46_expat;
2325
2326 --
2327 --
2328 -- ----------------------------------------------------------------------------
2329 -- |-------------------------< pay_gb_ins_rti_new_strt>-----------------------|
2330 -- ----------------------------------------------------------------------------
2331 procedure pay_gb_ins_rti_new_strt
2332 (p_validate in boolean default false
2333 ,p_assignment_id in number
2334 ,p_business_group_id in number
2335 ,p_information_type in varchar2 default null
2336 ,p_aei_information_category in varchar2 default null
2337 ,p_aei_information1 in varchar2 default null
2338 ,p_aei_information2 in varchar2 default null
2339 ,p_aei_information3 in varchar2 default null
2340 ,p_aei_information4 in varchar2 default null
2341 ,p_aei_information5 in varchar2 default null
2342 ,p_aei_information6 in varchar2 default null
2343 ,p_aei_information7 in varchar2 default null
2344 ,p_aei_information8 in varchar2 default null
2345 ,p_aei_information9 in varchar2 default null
2346 ,p_aei_information10 in varchar2 default null
2347 ,p_aei_information11 in varchar2 default null
2348 ,p_aei_information12 in varchar2 default null
2349 ,p_aei_information13 in varchar2 default null
2350 ,p_aei_information14 in varchar2 default null
2351 ,p_aei_information15 in varchar2 default null
2352 ,p_aei_information16 in varchar2 default null
2353 ,p_aei_information17 in varchar2 default null
2354 ,p_aei_information18 in varchar2 default null
2355 ,p_aei_information19 in varchar2 default null
2356 ,p_object_version_number out nocopy number
2357 ,p_assignment_extra_info_id out nocopy number
2358 ) is
2359 l_proc varchar2(72) := g_package||'pay_gb_ins_rti_new_strt';
2360 l_legislation_code varchar2(2);
2361 cursor csr_bg is
2362 select legislation_code
2363 from per_business_groups pbg
2364 where pbg.business_group_id = p_business_group_id;
2365 begin
2366 -- hr_utility.trace_on(null, 'GBRTI');
2367 open csr_bg;
2368 fetch csr_bg
2369 into l_legislation_code;
2370 if csr_bg%notfound then
2371 close csr_bg;
2372 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2373 hr_utility.raise_error;
2374 end if;
2375 close csr_bg;
2376 --
2377 -- Check that the legislation of the specified business group is 'GB'.
2378 --
2379 if l_legislation_code <> 'GB' then
2380 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2381 hr_utility.set_message_token('LEG_CODE','GB');
2382 hr_utility.raise_error;
2383 end if;
2384
2385 hr_utility.set_location(l_proc, 10);
2386 hr_utility.trace('p_assignment_extra_info_id = ' || p_assignment_extra_info_id);
2387 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
2388 hr_utility.trace('p_aei_information1 = ' || p_aei_information1);
2389 hr_utility.trace('p_aei_information2 = ' || p_aei_information2);
2390 hr_utility.trace('p_aei_information3 = ' || p_aei_information3);
2391 hr_utility.trace('p_aei_information4 = ' || p_aei_information4);
2395 hr_utility.trace('p_aei_information8 = ' || p_aei_information8);
2392 hr_utility.trace('p_aei_information5 = ' || p_aei_information5);
2393 hr_utility.trace('p_aei_information6 = ' || p_aei_information6);
2394 hr_utility.trace('p_aei_information7 = ' || p_aei_information7);
2396 hr_utility.trace('p_aei_information9 = ' || p_aei_information9);
2397 hr_utility.trace('p_aei_information10 = ' || p_aei_information10);
2398 hr_utility.trace('p_aei_information11 = ' || p_aei_information11);
2399 hr_utility.trace('p_aei_information12 = ' || p_aei_information12);
2400 hr_utility.trace('p_aei_information13 = ' || p_aei_information13);
2401 hr_utility.trace('p_aei_information14 = ' || p_aei_information14);
2402 hr_utility.trace('p_aei_information15 = ' || p_aei_information15);
2403 hr_utility.trace('p_aei_information16 = ' || p_aei_information16);
2404 hr_utility.trace('p_aei_information17 = ' || p_aei_information17);
2405 hr_utility.trace('p_aei_information18 = ' || p_aei_information18);
2406 hr_utility.trace('p_aei_information19 = ' || p_aei_information19);
2407
2408 --
2409
2410 hr_utility.set_location('Entering:'|| l_proc, 100);
2411 hr_assignment_extra_info_api.create_assignment_extra_info
2412 (p_validate => p_validate
2413 ,p_assignment_id => p_assignment_id
2414 ,p_information_type => p_information_type
2415 ,p_aei_information_category => p_aei_information_category
2416 ,p_aei_information1 => p_aei_information1
2417 ,p_aei_information2 => p_aei_information2
2418 ,p_aei_information3 => p_aei_information3
2419 ,p_aei_information4 => p_aei_information4
2420 ,p_aei_information5 => p_aei_information5
2421 ,p_aei_information6 => p_aei_information6
2422 ,p_aei_information7 => p_aei_information7
2423 ,p_aei_information8 => p_aei_information8
2424 ,p_aei_information9 => p_aei_information9
2425 ,p_aei_information10 => p_aei_information10
2426 ,p_aei_information11 => p_aei_information11
2427 ,p_aei_information12 => p_aei_information12
2428 ,p_aei_information13 => trim(p_aei_information13)
2429 ,p_aei_information14 => p_aei_information14
2430 ,p_aei_information15 => p_aei_information15
2431 ,p_aei_information16 => p_aei_information16
2432 ,p_aei_information17 => p_aei_information17
2433 ,p_aei_information18 => p_aei_information18
2434 ,p_aei_information19 => p_aei_information19
2435 ,p_object_version_number => p_object_version_number
2436 ,p_assignment_extra_info_id => p_assignment_extra_info_id);
2437
2438 hr_utility.set_location('Leaving:'|| l_proc, 200);
2439
2440 -- hr_utility.trace_off;
2441 end pay_gb_ins_rti_new_strt;
2442
2443 procedure pay_gb_upd_rti_new_strt
2444 (p_validate in boolean default false
2445 ,p_assignment_extra_info_id in number
2446 ,p_business_group_id in number
2447 ,p_object_version_number in out nocopy number
2448 ,p_aei_information_category in varchar2 default null
2449 ,p_aei_information1 in varchar2 default null
2450 ,p_aei_information2 in varchar2 default null
2451 ,p_aei_information3 in varchar2 default null
2452 ,p_aei_information4 in varchar2 default null
2453 ,p_aei_information5 in varchar2 default null
2454 ,p_aei_information6 in varchar2 default null
2455 ,p_aei_information7 in varchar2 default null
2456 ,p_aei_information8 in varchar2 default null
2457 ,p_aei_information9 in varchar2 default null
2458 ,p_aei_information10 in varchar2 default null
2459 ,p_aei_information11 in varchar2 default null
2460 ,p_aei_information12 in varchar2 default null
2461 ,p_aei_information13 in varchar2 default null
2462 ,p_aei_information14 in varchar2 default null
2463 ,p_aei_information15 in varchar2 default null
2464 ,p_aei_information16 in varchar2 default null
2465 ,p_aei_information17 in varchar2 default null
2466 ,p_aei_information18 in varchar2 default null
2467 ,p_aei_information19 in varchar2 default null
2468 ) is
2469
2470 l_proc varchar2(72) := g_package||'pay_gb_upd_rti_new_strt';
2471 l_legislation_code varchar2(2);
2472 cursor csr_bg is
2473 select legislation_code
2474 from per_business_groups pbg
2475 where pbg.business_group_id = p_business_group_id;
2476 begin
2477 hr_utility.set_location('Entering:'|| l_proc, 300);
2478
2479 open csr_bg;
2480 fetch csr_bg
2481 into l_legislation_code;
2482 if csr_bg%notfound then
2483 close csr_bg;
2484 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2485 hr_utility.raise_error;
2486 end if;
2487 close csr_bg;
2488 --
2489 -- Check that the legislation of the specified business group is 'GB'.
2490 --
2491 if l_legislation_code <> 'GB' then
2492 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2493 hr_utility.set_message_token('LEG_CODE','GB');
2494 hr_utility.raise_error;
2495 end if;
2496
2497 hr_assignment_extra_info_api.update_assignment_extra_info
2498 (p_validate => p_validate
2499 ,p_assignment_extra_info_id => p_assignment_extra_info_id
2500 ,p_object_version_number => p_object_version_number
2501 ,p_aei_information_category => p_aei_information_category
2505 ,p_aei_information4 => p_aei_information4
2502 ,p_aei_information1 => p_aei_information1
2503 ,p_aei_information2 => p_aei_information2
2504 ,p_aei_information3 => p_aei_information3
2506 ,p_aei_information5 => p_aei_information5
2507 ,p_aei_information6 => p_aei_information6
2508 ,p_aei_information7 => p_aei_information7
2509 ,p_aei_information8 => p_aei_information8
2510 ,p_aei_information9 => p_aei_information9
2511 ,p_aei_information10 => p_aei_information10
2512 ,p_aei_information11 => p_aei_information11
2513 ,p_aei_information12 => p_aei_information12
2514 ,p_aei_information13 => trim(p_aei_information13)
2515 ,p_aei_information14 => p_aei_information14
2516 ,p_aei_information15 => p_aei_information15
2517 ,p_aei_information16 => p_aei_information16
2518 ,p_aei_information17 => p_aei_information17
2519 ,p_aei_information18 => p_aei_information18
2520 ,p_aei_information19 => p_aei_information19
2521 );
2522 hr_utility.set_location('Leaving:'|| l_proc, 400);
2523 end pay_gb_upd_rti_new_strt;
2524 --
2525
2526 -- ---------------------------------------------------------------------------
2527 -- |-------------------------< pay_gb_ins_rti_agg_strt>-----------------------|
2528 -- ----------------------------------------------------------------------------
2529 procedure pay_gb_ins_rti_agg_strt
2530 (p_validate in boolean default false
2531 ,p_assignment_id in number
2532 ,p_business_group_id in number
2533 ,p_information_type in varchar2 default null
2534 ,p_aei_information_category in varchar2 default null
2535 ,p_aei_information1 in varchar2 default null
2536 ,p_aei_information2 in varchar2 default null
2537 ,p_aei_information3 in varchar2 default null
2538 ,p_aei_information4 in varchar2 default null
2539 ,p_object_version_number out nocopy number
2540 ,p_assignment_extra_info_id out nocopy number
2541 ) is
2542 l_proc varchar2(72) := g_package||'pay_gb_ins_rti_agg_strt';
2543 l_legislation_code varchar2(2);
2544 cursor csr_bg is
2545 select legislation_code
2546 from per_business_groups pbg
2547 where pbg.business_group_id = p_business_group_id;
2548 begin
2549 -- hr_utility.trace_on(null, 'GBRTI');
2550 open csr_bg;
2551 fetch csr_bg
2552 into l_legislation_code;
2553 if csr_bg%notfound then
2554 close csr_bg;
2555 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2556 hr_utility.raise_error;
2557 end if;
2558 close csr_bg;
2559 --
2560 -- Check that the legislation of the specified business group is 'GB'.
2561 --
2562 if l_legislation_code <> 'GB' then
2563 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2564 hr_utility.set_message_token('LEG_CODE','GB');
2565 hr_utility.raise_error;
2566 end if;
2567
2568 hr_utility.set_location(l_proc, 10);
2569 hr_utility.trace('p_assignment_extra_info_id = ' || p_assignment_extra_info_id);
2570 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
2571 hr_utility.trace('p_aei_information1 = ' || p_aei_information1);
2572 hr_utility.trace('p_aei_information2 = ' || p_aei_information2);
2573 hr_utility.trace('p_aei_information3 = ' || p_aei_information3);
2574 hr_utility.trace('p_aei_information4 = ' || p_aei_information4);
2575
2576 --
2577
2578 hr_utility.set_location('Entering:'|| l_proc, 100);
2579 hr_assignment_extra_info_api.create_assignment_extra_info
2580 (p_validate => p_validate
2581 ,p_assignment_id => p_assignment_id
2582 ,p_information_type => p_information_type
2583 ,p_aei_information_category => p_aei_information_category
2584 ,p_aei_information1 => p_aei_information1
2585 ,p_aei_information2 => p_aei_information2
2586 ,p_aei_information3 => p_aei_information3
2587 ,p_aei_information4 => p_aei_information4
2588 ,p_object_version_number => p_object_version_number
2589 ,p_assignment_extra_info_id => p_assignment_extra_info_id);
2590
2591 hr_utility.set_location('Leaving:'|| l_proc, 200);
2592
2593 -- hr_utility.trace_off;
2594 end pay_gb_ins_rti_agg_strt;
2595
2596 -- ---------------------------------------------------------------------------
2597 -- |-------------------------< pay_gb_upd_rti_agg_strt>-----------------------|
2598 -- ----------------------------------------------------------------------------
2599 procedure pay_gb_upd_rti_agg_strt
2600 (p_validate in boolean default false
2601 ,p_assignment_extra_info_id in number
2602 ,p_business_group_id in number
2603 ,p_object_version_number in out nocopy number
2604 ,p_aei_information_category in varchar2 default null
2605 ,p_aei_information1 in varchar2 default null
2606 ,p_aei_information2 in varchar2 default null
2607 ,p_aei_information3 in varchar2 default null
2608 ,p_aei_information4 in varchar2 default null
2609 ) is
2610
2611 l_proc varchar2(72) := g_package||'pay_gb_upd_rti_agg_strt';
2612 l_legislation_code varchar2(2);
2613 cursor csr_bg is
2614 select legislation_code
2615 from per_business_groups pbg
2616 where pbg.business_group_id = p_business_group_id;
2617 begin
2618 -- hr_utility.trace_on(null, 'GBRTI');
2622 fetch csr_bg
2619 hr_utility.set_location('Entering:'|| l_proc, 300);
2620
2621 open csr_bg;
2623 into l_legislation_code;
2624 if csr_bg%notfound then
2625 close csr_bg;
2626 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2627 hr_utility.raise_error;
2628 end if;
2629 close csr_bg;
2630 --
2631 -- Check that the legislation of the specified business group is 'GB'.
2632 --
2633 if l_legislation_code <> 'GB' then
2634 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
2635 hr_utility.set_message_token('LEG_CODE','GB');
2636 hr_utility.raise_error;
2637 end if;
2638
2639 hr_assignment_extra_info_api.update_assignment_extra_info
2640 (p_validate => p_validate
2641 ,p_assignment_extra_info_id => p_assignment_extra_info_id
2642 ,p_object_version_number => p_object_version_number
2643 ,p_aei_information_category => p_aei_information_category
2644 ,p_aei_information1 => p_aei_information1
2645 ,p_aei_information2 => p_aei_information2
2646 ,p_aei_information3 => p_aei_information3
2647 ,p_aei_information4 => p_aei_information4
2648 );
2649 hr_utility.set_location('Leaving:'|| l_proc, 400);
2650 end pay_gb_upd_rti_agg_strt;
2651
2652 end pay_gb_aei_api;
[Home] [Help]