[Home] [Help]
PACKAGE BODY: APPS.PAY_PL_PAYE_API
Source
1 Package Body PAY_PL_PAYE_API as
2 /* $Header: pyppdapi.pkb 120.1 2005/12/08 19:08:54 ssekhar noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'PAY_PL_PAYE_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< <create_pl_paye_details> >---------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_pl_paye_details
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_contract_category in varchar2
16 ,p_per_or_asg_id in number
17 ,p_business_group_id in number
18 ,p_tax_reduction in varchar2
19 ,p_tax_calc_with_spouse_child in varchar2
20 ,p_income_reduction in varchar2
21 ,p_income_reduction_amount in number default null
22 ,p_rate_of_tax in varchar2
23 ,p_paye_details_id out nocopy number
24 ,p_object_version_number out nocopy number
25 ,p_effective_start_date out nocopy date
26 ,p_effective_end_date out nocopy date
27 ,p_effective_date_warning out nocopy boolean
28 ) is
29 --
30 -- Declare cursors and local variables
31 --
32 cursor cur_prs is select min(papf.effective_start_date)
33 from per_all_people_f papf,
34 per_person_types ppt
35 where papf.person_type_id = ppt.person_type_id
36 and system_person_type in ('EMP','EMP_APL')
37 and papf.person_id = p_per_or_asg_id
38 and papf.business_group_id = p_business_group_id
39 and p_contract_category = 'NORMAL';
40
41 cursor cur_asg is select nvl(assignment_eff_start_date,term_eff_start_date) effective_start_date from
42 (select min(paaf.effective_start_date) assignment_eff_start_date
43 from per_all_assignments_f paaf,
44 hr_soft_coding_keyflex scl,
45 per_assignment_status_types past
46 where paaf.ASSIGNMENT_STATUS_TYPE_ID = past.ASSIGNMENT_STATUS_TYPE_ID
47 and paaf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
48 and paaf.assignment_id = p_per_or_asg_id
49 and paaf.business_group_id = p_business_group_id
50 and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN') and not exists ( select
51 paaf.effective_start_date from per_all_assignments_f paaf,
52 per_assignment_status_types past
53 where paaf.ASSIGNMENT_STATUS_TYPE_ID = past.ASSIGNMENT_STATUS_TYPE_ID
54 and past.per_system_status = 'TERM_ASSIGN'
55 and paaf.assignment_id = p_per_or_asg_id
56 and paaf.business_group_id = p_business_group_id
57 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date)),
58 (select min(paaf.effective_start_date) term_eff_start_date from per_all_assignments_f paaf,
59 per_assignment_status_types past
60 where paaf.ASSIGNMENT_STATUS_TYPE_ID = past.ASSIGNMENT_STATUS_TYPE_ID
61 and past.per_system_status = 'TERM_ASSIGN'
62 and paaf.assignment_id = p_per_or_asg_id
63 and paaf.business_group_id = p_business_group_id
64 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date);
65
66
67 l_in_out_parameter number;
68 l_effective_date date;
69 l_proc varchar2(72) := g_package||'create_pl_paye_details';
70 l_program_id number;
71 l_program_login_id number;
72 l_program_application_id number;
73 l_request_id number;
74 l_paye_details_id number;
75 l_object_version_number number;
76 l_effective_start_date date;
77 l_effective_end_date date;
78
79
80 begin
81 hr_utility.set_location('Entering:'|| l_proc, 10);
82 --
83 -- Issue a savepoint
84 --
85 savepoint create_pl_paye_details;
86 --
87 -- Remember IN OUT parameter IN values
88 --
89
90 --
91 -- Truncate the time portion from all IN date parameters
92 --
93 If p_contract_category = 'NORMAL' then
94 open cur_prs;
95 fetch cur_prs into l_effective_date;
96 close cur_prs;
97 else
98 open cur_asg;
99 fetch cur_asg into l_effective_date;
100 close cur_asg;
101 end if;
102
103 if p_effective_date > l_effective_date then
104 p_effective_date_warning := TRUE;
105 else
106 l_effective_date := trunc(p_effective_date);
107 p_effective_date_warning := FALSE;
108 end if;
109
110 -- l_effective_date := trunc(p_effective_date);
111
112 --
113 -- Call Before Process User Hook
114 --
115 begin
116 PAY_PL_PAYE_BK1.create_pl_paye_details_b
117 (p_effective_date => l_effective_date
118 ,p_contract_category => p_contract_category
119 ,p_business_group_id => p_business_group_id
120 ,p_per_or_asg_id => p_per_or_asg_id
121 ,p_tax_reduction => p_tax_reduction
122 ,p_tax_calc_with_spouse_child => p_tax_calc_with_spouse_child
123 ,p_income_reduction => p_income_reduction
124 ,p_income_reduction_amount => p_income_reduction_amount
125 ,p_rate_of_tax => p_rate_of_tax);
126 exception
127 when hr_api.cannot_find_prog_unit then
128 hr_api.cannot_find_prog_unit_error
129 (p_module_name => 'create_pl_paye_details'
130 ,p_hook_type => 'BP'
131 );
132 end;
133 --
134 -- Validation in addition to Row Handlers
135 --
136
137 --
138 -- Process Logic
139 --
140 pay_ppd_ins.ins(p_effective_date => l_effective_date
141 ,p_per_or_asg_id => p_per_or_asg_id
142 ,p_business_group_id => p_business_group_id
143 ,p_contract_category => p_contract_category
144 ,p_tax_reduction => p_tax_reduction
145 ,p_tax_calc_with_spouse_child => p_tax_calc_with_spouse_child
146 ,p_income_reduction => p_income_reduction
147 ,p_income_reduction_amount => p_income_reduction_amount
148 ,p_rate_of_tax => p_rate_of_tax
149 ,p_program_id => l_program_id
150 ,p_program_login_id => l_program_login_id
151 ,p_program_application_id => l_program_application_id
152 ,p_request_id => l_request_id
153 ,p_paye_details_id => l_paye_details_id
154 ,p_object_version_number => l_object_version_number
155 ,p_effective_start_date => l_effective_start_date
156 ,p_effective_end_date => l_effective_end_date
157 );
158
159
160
161 --
162 -- Call After Process User Hook
163 --
164 begin
165
166 pay_pl_paye_bk1.create_pl_paye_details_a
167 (p_effective_date => l_effective_date
168 ,p_contract_category => p_contract_category
169 ,p_per_or_asg_id => p_per_or_asg_id
170 ,p_business_group_id => p_business_group_id
171 ,p_tax_reduction => p_tax_reduction
172 ,p_tax_calc_with_spouse_child => p_tax_calc_with_spouse_child
173 ,p_income_reduction => p_income_reduction
174 ,p_income_reduction_amount => p_income_reduction_amount
175 ,p_rate_of_tax => p_rate_of_tax
176 ,p_paye_details_id => l_paye_details_id
177 ,p_object_version_number => l_object_version_number
178 ,p_effective_start_date => l_effective_start_date
179 ,p_effective_end_date => l_effective_end_date
180 );
181 exception
182 when hr_api.cannot_find_prog_unit then
183 hr_api.cannot_find_prog_unit_error
184 (p_module_name => 'create_pl_paye_api'
185 ,p_hook_type => 'AP'
186 );
187 end;
188 --
189 -- When in validation only mode raise the Validate_Enabled exception
190 --
191 if p_validate then
192 raise hr_api.validate_enabled;
193 end if;
194 --
195 -- Set all IN OUT and OUT parameters with out values
196 --
197 p_paye_details_id := l_paye_details_id;
198 p_object_version_number := l_object_version_number;
199 p_effective_start_date := l_effective_start_date;
200 p_effective_end_date := l_effective_end_date;
201 --
202 hr_utility.set_location(' Leaving:'||l_proc, 70);
203 exception
204 when hr_api.validate_enabled then
205 --
206 -- As the Validate_Enabled exception has been raised
207 -- we must rollback to the savepoint
208 --
209 rollback to create_pl_paye_details;
210 --
211 -- Reset IN OUT parameters and set OUT parameters
212 -- (Any key or derived arguments must be set to null
213 -- when validation only mode is being used.)
214 --
215 p_paye_details_id := NULL;
216 p_object_version_number := NULL;
217 p_effective_start_date := NULL;
218 p_effective_end_date := NULL;
219 hr_utility.set_location(' Leaving:'||l_proc, 80);
220 when others then
221 --
222 -- A validation or unexpected error has occured
223 --
224 rollback to create_pl_paye_details;
225 --
226 -- Reset IN OUT parameters and set all
227 -- OUT parameters, including warnings, to null
228 --
229 p_paye_details_id := NULL;
230 p_object_version_number := NULL;
231 p_effective_start_date := NULL;
232 p_effective_end_date := NULL;
233 hr_utility.set_location(' Leaving:'||l_proc, 90);
234 raise;
235 end CREATE_PL_PAYE_DETAILS;
236 --
237 -- ----------------------------------------------------------------------------
238 -- |-------------------------< update_pl_paye_details >-------------------------|
239 -- ----------------------------------------------------------------------------
240 procedure update_pl_paye_details
241 (p_validate in boolean default false
242 ,p_effective_date in date
243 ,p_datetrack_update_mode in varchar2
244 ,p_paye_details_id in number
245 ,p_object_version_number in out nocopy number
246 ,p_tax_reduction in varchar2 default hr_api.g_varchar2
247 ,p_tax_calc_with_spouse_child in varchar2 default hr_api.g_varchar2
248 ,p_income_reduction in varchar2 default hr_api.g_varchar2
249 ,p_income_reduction_amount in number default hr_api.g_number
250 ,p_rate_of_tax in varchar2 default hr_api.g_varchar2
251 ,p_effective_start_date out nocopy date
252 ,p_effective_end_date out nocopy date
253 )
254 is
255 --
256 -- Declare cursors and local variables
257 --
258 l_effective_date date;
259 l_proc varchar2(72) := g_package||'update_pl_paye_details';
260 l_program_id number;
261 l_program_login_id number;
262 l_program_application_id number;
263 l_request_id number;
264 l_effective_start_date date;
265 l_effective_end_date date;
266 l_object_version_number number;
267 l_in_out_parameter1 number;
268
269 begin
270
271 hr_utility.set_location('Entering:'|| l_proc, 10);
272 --
273 -- Issue a savepoint
274 --
275 savepoint update_pl_paye_details;
276 --
277 -- Remember IN OUT parameter IN values
278 --
279 l_in_out_parameter1 := p_object_version_number;
280 --
281 --
282 l_object_version_number := p_object_version_number;
283
284 --
285 -- Truncate the time portion from all IN date parameters
286 --
287 l_effective_date := trunc(p_effective_date);
288 --
289 -- Call Before Process User Hook
290 --
291 begin
292
293 PAY_PL_PAYE_BK2.update_pl_paye_details_b(p_effective_date => l_effective_date
294 ,p_paye_details_id => p_paye_details_id
295 ,p_datetrack_update_mode => p_datetrack_update_mode
296 ,p_tax_reduction => p_tax_reduction
297 ,p_tax_calc_with_spouse_child => p_tax_calc_with_spouse_child
298 ,p_income_reduction => p_income_reduction
299 ,p_income_reduction_amount => p_income_reduction_amount
300 ,p_rate_of_tax => p_rate_of_tax
301 ,p_object_version_number => l_object_version_number
302 );
303 exception
304 when hr_api.cannot_find_prog_unit then
305 hr_api.cannot_find_prog_unit_error
306 (p_module_name => 'update_pl_paye_details'
307 ,p_hook_type => 'BP'
308 );
309 end;
310
311 --
312 -- Process Logic
313 --
314 pay_ppd_upd.upd
315 (p_effective_date => l_effective_date
316 ,p_datetrack_mode => p_datetrack_update_mode
317 ,p_paye_details_id => p_paye_details_id
318 ,p_object_version_number => l_object_version_number
319 ,p_tax_reduction => p_tax_reduction
320 ,p_tax_calc_with_spouse_child => p_tax_calc_with_spouse_child
321 ,p_income_reduction => p_income_reduction
322 ,p_income_reduction_amount => p_income_reduction_amount
323 ,p_rate_of_tax => p_rate_of_tax
324 ,p_program_id => l_program_id
325 ,p_program_login_id => l_program_login_id
326 ,p_program_application_id => l_program_application_id
327 ,p_request_id => l_request_id
328 ,p_effective_start_date => l_effective_start_date
329 ,p_effective_end_date => l_effective_end_date
330 );
331
332 --
333 -- Call After Process User Hook
334 --
335 begin
336
337 pay_pl_paye_bk2.update_pl_paye_details_a
338 (p_effective_date => l_effective_date
339 ,p_paye_details_id => p_paye_details_id
343 ,p_income_reduction => p_income_reduction
340 ,p_datetrack_update_mode => p_datetrack_update_mode
341 ,p_tax_reduction => p_tax_reduction
342 ,p_tax_calc_with_spouse_child => p_tax_calc_with_spouse_child
344 ,p_income_reduction_amount => p_income_reduction_amount
345 ,p_rate_of_tax => p_rate_of_tax
346 ,p_object_version_number => l_object_version_number
347 ,p_effective_start_date => l_effective_start_date
348 ,p_effective_end_date => l_effective_end_date
349 );
350
351 exception
352 when hr_api.cannot_find_prog_unit then
353 hr_api.cannot_find_prog_unit_error
354 (p_module_name => 'update_pl_paye_details'
355 ,p_hook_type => 'AP'
356 );
357 end;
358 --
359 -- When in validation only mode raise the Validate_Enabled exception
360 --
361 if p_validate then
362 raise hr_api.validate_enabled;
363 end if;
364 --
365 -- Set all IN OUT and OUT parameters with out values
366 --
367 p_object_version_number := l_object_version_number;
368 p_effective_start_date := l_effective_start_date;
369 p_effective_end_date := l_effective_end_date;
370
371 --
372 hr_utility.set_location(' Leaving:'||l_proc, 70);
373 exception
374 when hr_api.validate_enabled then
375 --
376 -- As the Validate_Enabled exception has been raised
377 -- we must rollback to the savepoint
378 --
379 rollback to update_pl_paye_details;
380 --
381 -- Reset IN OUT parameters and set OUT parameters
382 -- (Any key or derived arguments must be set to null
383 -- when validation only mode is being used.)
384 --
385 p_object_version_number := l_in_out_parameter1;
386 p_effective_start_date := NULL;
387 p_effective_end_date := NULL;
388
389 hr_utility.set_location(' Leaving:'||l_proc, 80);
390 when others then
391 --
392 -- A validation or unexpected error has occured
393 --
394 rollback to update_pl_paye_details;
395 --
396 -- Reset IN OUT parameters and set all
397 -- OUT parameters, including warnings, to null
398 --
399 p_object_version_number := l_in_out_parameter1;
400 p_effective_start_date := NULL;
401 p_effective_end_date := NULL;
402 hr_utility.set_location(' Leaving:'||l_proc, 90);
403 raise;
404 end update_pl_paye_details;
405 --
406 -- ----------------------------------------------------------------------------
407 -- |-------------------------< delete_pl_paye_details >------------------------|
408 -- ----------------------------------------------------------------------------
409 --
410 procedure delete_pl_paye_details
411 (p_validate in boolean default false
412 ,p_effective_date in date
413 ,p_datetrack_delete_mode in varchar2
414 ,p_paye_details_id in number
415 ,p_object_version_number in out nocopy number
416 ,p_effective_start_date out nocopy date
417 ,p_effective_end_date out nocopy date
418 )
419 is
420 --
421 -- Declare cursors and local variables
422 --
423 l_effective_date date;
424 l_proc varchar2(72) := g_package||'delete_pl_paye_details';
425 l_program_id number;
426 l_program_login_id number;
427 l_program_application_id number;
428 l_request_id number;
429 l_sii_details_id number;
430 l_object_version_number number;
431 l_effective_start_date date;
432 l_effective_end_date date;
433 l_in_out_parameter1 number;
434
435 begin
436
437 hr_utility.set_location('Entering:'|| l_proc, 10);
438 --
439 -- Issue a savepoint
440 --
441 savepoint delete_pl_paye_details;
442 --
443 -- Remember IN OUT parameter IN values
444 --
445 l_in_out_parameter1 := p_object_version_number;
446 --
447 --
448 l_object_version_number := p_object_version_number;
449 --
450 -- Truncate the time portion from all IN date parameters
451 --
452 l_effective_date := trunc(p_effective_date);
453
454 --
455 -- Call Before Process User Hook
456 --
457
458 begin
459 PAY_PL_PAYE_BK3.delete_pl_paye_details_b
460 (p_effective_date => l_effective_date
461 ,p_paye_details_id => p_paye_details_id
462 ,p_datetrack_delete_mode => p_datetrack_delete_mode
463 ,p_object_version_number => p_object_version_number
464 );
465 exception
466 when hr_api.cannot_find_prog_unit then
467 hr_api.cannot_find_prog_unit_error
468 (p_module_name => 'delete_pl_paye_details'
469 ,p_hook_type => 'BP'
470 );
471 end;
472 --
473 --
474 -- Process Logic
475 --
476
477 pay_ppd_del.del
478 (p_effective_date => l_effective_date
479 ,p_datetrack_mode => p_datetrack_delete_mode
480 ,p_paye_details_id => p_paye_details_id
481 ,p_object_version_number => l_object_version_number
482 ,p_effective_start_date => l_effective_start_date
483 ,p_effective_end_date => l_effective_end_date
484 );
485 --
486
487
488 --
489 -- Call After Process User Hook
490 --
491 begin
492
493 pay_pl_paye_bk3.delete_pl_paye_details_a
494 (p_effective_date => l_effective_date
495 ,p_paye_details_id => p_paye_details_id
496 ,p_datetrack_delete_mode => p_datetrack_delete_mode
500 );
497 ,p_object_version_number => l_object_version_number
498 ,p_effective_start_date => l_effective_start_date
499 ,p_effective_end_date => l_effective_end_date
501
502 exception
503 when hr_api.cannot_find_prog_unit then
504 hr_api.cannot_find_prog_unit_error
505 (p_module_name => 'delete_pl_paye_details'
506 ,p_hook_type => 'AP'
507 );
508 end;
509 --
510 -- When in validation only mode raise the Validate_Enabled exception
511 --
512 if p_validate then
513 raise hr_api.validate_enabled;
514 end if;
515 --
516 -- Set all IN OUT and OUT parameters with out values
517 --
518 p_object_version_number := l_object_version_number;
519 p_effective_start_date := l_effective_start_date;
520 p_effective_end_date := l_effective_end_date;
521
522
523 --
524 hr_utility.set_location(' Leaving:'||l_proc, 70);
525 exception
526 when hr_api.validate_enabled then
527 --
528 -- As the Validate_Enabled exception has been raised
529 -- we must rollback to the savepoint
530 --
531 rollback to delete_pl_paye_details;
532 --
533 -- Reset IN OUT parameters and set OUT parameters
534 -- (Any key or derived arguments must be set to null
535 -- when validation only mode is being used.)
536 --
537 p_object_version_number := l_in_out_parameter1;
538 p_effective_start_date := NULL;
539 p_effective_end_date := NULL;
540
541 hr_utility.set_location(' Leaving:'||l_proc, 80);
542 when others then
543 --
544 -- A validation or unexpected error has occured
545 --
546 rollback to pay_pl_paye_details;
547 --
548 -- Reset IN OUT parameters and set all
549 -- OUT parameters, including warnings, to null
550 --
551 p_object_version_number := l_in_out_parameter1;
552 p_effective_start_date := NULL;
553 p_effective_end_date := NULL;
554 hr_utility.set_location(' Leaving:'||l_proc, 90);
555 raise;
556 end delete_pl_paye_details;
557 --
558 -- ----------------------------------------------------------------------------
559 -- |----------------------< create_pl_civil_paye_details >---------------------|
560 -- ----------------------------------------------------------------------------
561 procedure create_pl_civil_paye_details
562 (p_validate in boolean default false
563 ,p_effective_date in date
564 ,p_contract_category in varchar2 default 'CIVIL'
565 ,p_assignment_id in number
566 ,p_income_reduction_amount in number default null
567 ,p_rate_of_tax in varchar2 default 'C01'
568 ,p_paye_details_id out nocopy number
569 ,p_object_version_number out nocopy number
570 ,p_effective_start_date out nocopy date
571 ,p_effective_end_date out nocopy date
572 ,p_effective_date_warning out nocopy boolean)
573 is
574 --
575 -- Declare cursors and local variables
576 --
577
578 l_business_group_id per_business_groups.business_group_id%TYPE;
579 l_legislation_code per_business_groups.legislation_code%TYPE;
580 l_leg_code pay_user_column_instances_f.legislation_code%TYPE;
581 l_income_reduction ff_globals_f.global_value%type;
582 l_effective_date date;
583 l_effective_start_date date;
584 l_effective_end_date date;
585 l_object_version_number number;
586 l_paye_details_id number;
587 l_proc varchar2(72) := g_package||'create_pl_civil_paye_details';
588
589 cursor csr_get_derived_details is
590 select bus.business_group_id
591 , bus.legislation_code
592 from per_all_assignments_f paf
593 , per_business_groups_perf bus
594 where paf.assignment_id = p_assignment_id
595 and l_effective_date between paf.effective_start_date
596 and paf.effective_end_date
597 and bus.business_group_id = paf.business_group_id;
598
599 begin
600 hr_utility.set_location('Entering:'|| l_proc, 10);
601
602 l_leg_code := 'PL';
603
604 l_effective_date := trunc(p_effective_date);
605
606 open csr_get_derived_details;
607 fetch csr_get_derived_details into l_business_group_id,l_legislation_code;
608 --
609 if csr_get_derived_details%NOTFOUND then
610 --
611 close csr_get_derived_details;
612 --
613 hr_utility.set_message(801,'PAY_375848_PL_INVALID_ASG');
614 hr_utility.raise_error;
615 end if;
616 --
617 close csr_get_derived_details;
618
619 -- Since we will be re-setting the effective_start_date to the Assignment's start date,
620 -- we first validate the assignment id before deriving the effective_start_date.
621
622 pay_ppd_bus.chk_per_asg_id(p_effective_date => l_effective_date
623 ,p_per_or_asg_id => p_assignment_id
624 ,p_contract_category => 'CIVIL'
625 ,p_business_group_id => l_business_group_id
626 ,p_object_version_number => l_object_version_number);
627
628
629
630
631 -- Calling the Create PAYE API
632
633 pay_pl_paye_api.create_pl_paye_details
634 (p_validate => p_validate
635 ,p_effective_date => l_effective_date
636 ,p_contract_category => p_contract_category
637 ,p_per_or_asg_id => p_assignment_id
638 ,p_business_group_id => l_business_group_id
639 ,p_tax_reduction => null
643 ,p_rate_of_tax => p_rate_of_tax
640 ,p_tax_calc_with_spouse_child => null
641 ,p_income_reduction => null
642 ,p_income_reduction_amount => p_income_reduction_amount
644 ,p_paye_details_id => l_paye_details_id
645 ,p_object_version_number => l_object_version_number
646 ,p_effective_start_date => l_effective_start_date
647 ,p_effective_end_date => l_effective_end_date
648 ,p_effective_date_warning => p_effective_date_warning
649 );
650
651 end create_pl_civil_paye_details;
652 --
653 --
654 -- ----------------------------------------------------------------------------
655 -- |----------------------< create_pl_lump_paye_details >---------------------|
656 -- ----------------------------------------------------------------------------
657 procedure create_pl_lump_paye_details
658 (p_validate in boolean default false
659 ,p_effective_date in date
660 ,p_contract_category in varchar2 default 'LUMP'
661 ,p_assignment_id in number
662 ,p_rate_of_tax in varchar2
663 ,p_paye_details_id out nocopy number
664 ,p_object_version_number out nocopy number
665 ,p_effective_start_date out nocopy date
666 ,p_effective_end_date out nocopy date
667 ,p_effective_date_warning out nocopy boolean)
668 is
669 --
670 -- Declare cursors and local variables
671 --
672
673 l_business_group_id per_business_groups.business_group_id%TYPE;
674 l_legislation_code per_business_groups.legislation_code%TYPE;
675 l_leg_code pay_user_column_instances_f.legislation_code%TYPE;
676 l_effective_date date;
677 l_effective_start_date date;
678 l_effective_end_date date;
679 l_object_version_number number;
680 l_paye_details_id number;
681 l_proc varchar2(72) := g_package||'create_pl_lump_paye_details';
682
683 cursor csr_get_derived_details is
684 select bus.business_group_id
685 , bus.legislation_code
686 from per_all_assignments_f paf
687 , per_business_groups_perf bus
688 where paf.assignment_id = p_assignment_id
689 and l_effective_date between paf.effective_start_date
690 and paf.effective_end_date
691 and bus.business_group_id = paf.business_group_id;
692
693 cursor csr_contract_type is
694 select segment4
695 from hr_soft_coding_keyflex soft, per_all_assignments_f paaf
696 where soft.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
697 and paaf.assignment_id = p_assignment_id
698 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
699
700 l_contract_type hr_soft_coding_keyflex.segment4%TYPE;
701 l_rate_of_tax pay_pl_paye_details_f.rate_of_tax%TYPE;
702
703 begin
704 hr_utility.set_location('Entering:'|| l_proc, 10);
705
706 l_leg_code := 'PL';
707
708 l_effective_date := trunc(p_effective_date);
709
710 open csr_get_derived_details;
711 fetch csr_get_derived_details into l_business_group_id,l_legislation_code;
712 --
713 if csr_get_derived_details%NOTFOUND then
714 --
715 close csr_get_derived_details;
716 --
717 hr_utility.set_message(801,'PAY_375848_PL_INVALID_ASG');
718 hr_utility.raise_error;
719 end if;
720 --
721 close csr_get_derived_details;
722
723 -- Since we will be re-setting the effective_start_date to the Assignment's start date,
724 -- we first validate the assignment id before deriving the effective_start_date.
725
726 pay_ppd_bus.chk_per_asg_id(p_effective_date => l_effective_date
727 ,p_per_or_asg_id => p_assignment_id
728 ,p_contract_category => 'LUMP'
729 ,p_business_group_id => l_business_group_id
730 ,p_object_version_number => l_object_version_number);
731
732 open csr_contract_type;
733 fetch csr_contract_type into l_contract_type;
734 close csr_contract_type;
735
736 -- For Contract types L01, L02, L03, L04, L09, L10, L11 we will not store the Rate of Tax
737 -- in the table pay_pl_paye_details_f
738 if l_contract_type in ('L01','L02','L03','L04','L09','L10','L11') then
739 l_rate_of_tax := NULL;
740 else
741 l_rate_of_tax := p_rate_of_tax;
742 end if;
743
744
745 -- Calling the Create PAYE API
746
747 pay_pl_paye_api.create_pl_paye_details
748 (p_validate => p_validate
749 ,p_effective_date => l_effective_date
750 ,p_contract_category => p_contract_category
751 ,p_per_or_asg_id => p_assignment_id
752 ,p_business_group_id => l_business_group_id
753 ,p_tax_reduction => null
754 ,p_tax_calc_with_spouse_child => null
755 ,p_income_reduction => null
756 ,p_income_reduction_amount => null
757 ,p_rate_of_tax => l_rate_of_tax
758 ,p_paye_details_id => l_paye_details_id
759 ,p_object_version_number => l_object_version_number
760 ,p_effective_start_date => l_effective_start_date
761 ,p_effective_end_date => l_effective_end_date
762 ,p_effective_date_warning => p_effective_date_warning
763 );
764
765 end create_pl_lump_paye_details;
766 --
767
768 --
769 -- ----------------------------------------------------------------------------
770 -- |----------------------< create_pl_f_lump_paye_details >---------------------|
774 ,p_effective_date in date
771 -- ----------------------------------------------------------------------------
772 procedure create_pl_f_lump_paye_details
773 (p_validate in boolean default false
775 ,p_contract_category in varchar2 default 'F_LUMP'
776 ,p_assignment_id in number
777 ,p_rate_of_tax in varchar2
778 ,p_paye_details_id out nocopy number
779 ,p_object_version_number out nocopy number
780 ,p_effective_start_date out nocopy date
781 ,p_effective_end_date out nocopy date
782 ,p_effective_date_warning out nocopy boolean)
783 is
784 --
785 -- Declare cursors and local variables
786 --
787
788 l_business_group_id per_business_groups.business_group_id%TYPE;
789 l_legislation_code per_business_groups.legislation_code%TYPE;
790 l_leg_code pay_user_column_instances_f.legislation_code%TYPE;
791 l_effective_date date;
792 l_effective_start_date date;
793 l_effective_end_date date;
794 l_object_version_number number;
795 l_paye_details_id number;
796 l_proc varchar2(72) := g_package||'create_pl_f_lump_paye_details';
797
798 cursor csr_get_derived_details is
799 select bus.business_group_id
800 , bus.legislation_code
801 from per_all_assignments_f paf
802 , per_business_groups_perf bus
803 where paf.assignment_id = p_assignment_id
804 and l_effective_date between paf.effective_start_date
805 and paf.effective_end_date
806 and bus.business_group_id = paf.business_group_id;
807
808 begin
809 hr_utility.set_location('Entering:'|| l_proc, 10);
810
811 l_leg_code := 'PL';
812
813 l_effective_date := trunc(p_effective_date);
814
815 open csr_get_derived_details;
816 fetch csr_get_derived_details into l_business_group_id,l_legislation_code;
817 --
818 if csr_get_derived_details%NOTFOUND then
819 --
820 close csr_get_derived_details;
821 --
822 hr_utility.set_message(801,'PAY_375848_PL_INVALID_ASG');
823 hr_utility.raise_error;
824 end if;
825 --
826 close csr_get_derived_details;
827
828 -- Since we will be re-setting the effective_start_date to the Assignment's start date,
829 -- we first validate the assignment id before deriving the effective_start_date.
830
831 pay_ppd_bus.chk_per_asg_id(p_effective_date => l_effective_date
832 ,p_per_or_asg_id => p_assignment_id
833 ,p_contract_category => 'F_LUMP'
834 ,p_business_group_id => l_business_group_id
835 ,p_object_version_number => l_object_version_number);
836
837
838
839
840 -- Calling the Create PAYE API
841
842 pay_pl_paye_api.create_pl_paye_details
843 (p_validate => p_validate
844 ,p_effective_date => l_effective_date
845 ,p_contract_category => p_contract_category
846 ,p_per_or_asg_id => p_assignment_id
847 ,p_business_group_id => l_business_group_id
848 ,p_tax_reduction => null
849 ,p_tax_calc_with_spouse_child => null
850 ,p_income_reduction => null
851 ,p_income_reduction_amount => null
852 ,p_rate_of_tax => p_rate_of_tax
853 ,p_paye_details_id => l_paye_details_id
854 ,p_object_version_number => l_object_version_number
855 ,p_effective_start_date => l_effective_start_date
856 ,p_effective_end_date => l_effective_end_date
857 ,p_effective_date_warning => p_effective_date_warning
858 );
859
860 end create_pl_f_lump_paye_details;
861 --
862 -- ----------------------------------------------------------------------------
863 -- |----------------------< create_pl_normal_paye_details >---------------------|
864 -- ----------------------------------------------------------------------------
865
866 procedure create_pl_normal_paye_details
867 (p_validate in boolean default false
868 ,p_effective_date in date
869 ,p_contract_category in varchar2 default 'NORMAL'
870 ,p_person_id in number
871 ,p_tax_reduction in varchar2 default 'NOTAX'
872 ,p_tax_calc_with_spouse_child in varchar2 default 'N'
873 ,p_income_reduction in varchar2 default 'N01'
874 ,p_rate_of_tax in varchar2 default 'N01'
875 ,p_paye_details_id out nocopy number
876 ,p_object_version_number out nocopy number
877 ,p_effective_start_date out nocopy date
878 ,p_effective_end_date out nocopy date
879 ,p_effective_date_warning out nocopy boolean
880 )
881 is
882 --
883 -- Declare cursors and local variables
884 --
885
886 l_business_group_id per_business_groups.business_group_id%TYPE;
887 l_legislation_code per_business_groups.legislation_code%TYPE;
888 l_leg_code pay_user_column_instances_f.legislation_code%TYPE;
889 l_effective_date date;
890 l_effective_start_date date;
891 l_effective_end_date date;
892 l_object_version_number number;
893 l_paye_details_id number;
894 l_proc varchar2(72) := g_package||'create_pl_paye_sii_details';
895
896 cursor csr_get_derived_details is
897 select bus.business_group_id
898 , bus.legislation_code
899 from per_all_people_f papf
900 , per_business_groups_perf bus
901 where papf.person_id = p_person_id
905
902 and l_effective_date between papf.effective_start_date
903 and papf.effective_end_date
904 and bus.business_group_id = papf.business_group_id;
906
907 begin
908 hr_utility.set_location('Entering:'|| l_proc, 10);
909
910 l_leg_code := 'PL';
911 l_effective_date := trunc(p_effective_date);
912
913 open csr_get_derived_details;
914 fetch csr_get_derived_details into l_business_group_id,l_legislation_code;
915 --
916 if csr_get_derived_details%NOTFOUND then
917 --
918 close csr_get_derived_details;
919 --
920 hr_utility.set_message(801,'PAY_375848_PL_INVALID_ASG');
921 hr_utility.raise_error;
922 end if;
923 --
924 close csr_get_derived_details;
925 -- Since we will be re-setting the effective_start_date to the Person's start date,
926 -- we first validate the person id before deriving the effective_start_date.
927
928 pay_ppd_bus.chk_per_asg_id(p_effective_date => l_effective_date
929 ,p_per_or_asg_id => p_person_id
930 ,p_contract_category => 'NORMAL'
931 ,p_business_group_id => l_business_group_id
932 ,p_object_version_number => l_object_version_number);
933
934
935 -- Calling the Create PAYE API
936
937 pay_pl_paye_api.create_pl_paye_details
938 (p_validate => p_validate
939 ,p_effective_date => l_effective_date
940 ,p_contract_category => p_contract_category
941 ,p_per_or_asg_id => p_person_id
942 ,p_business_group_id => l_business_group_id
943 ,p_tax_reduction => p_tax_reduction
944 ,p_tax_calc_with_spouse_child => p_tax_calc_with_spouse_child
945 ,p_income_reduction => p_income_reduction
946 ,p_income_reduction_amount => null
947 ,p_rate_of_tax => p_rate_of_tax
948 ,p_paye_details_id => l_paye_details_id
949 ,p_object_version_number => l_object_version_number
950 ,p_effective_start_date => l_effective_start_date
951 ,p_effective_end_date => l_effective_end_date
952 ,p_effective_date_warning => p_effective_date_warning
953 );
954
955 end create_pl_normal_paye_details;
956 end PAY_PL_PAYE_API;