[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_SB_API
Source
4 -- Package Variables
1 Package Body PAY_IE_SB_API as
2 /* $Header: pyisbapi.pkb 115.4 2002/12/16 17:47:54 dsaxby ship $ */
3 --
5 --
6 g_package varchar2(33) := ' pay_ie_sb_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_ie_sb_details >-------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ie_sb_details
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_assignment_id in number
16 ,p_absence_start_date in date
17 ,p_absence_end_date in date
18 ,p_benefit_amount in number
19 ,p_benefit_type in varchar2
20 ,p_calculation_option in varchar2
21 ,p_reduced_tax_credit in number default null
22 ,p_reduced_standard_cutoff in number default null
23 ,p_incident_id in number default null
24 ,p_social_benefit_id out nocopy number
25 ,p_object_version_number out nocopy number
26 ,p_effective_start_date out nocopy date
27 ,p_effective_end_date out nocopy date
28 ) is
29 --
30 -- Declare cursors and local variables
31 --
32 l_flag varchar2(30);
33 l_object_version_number number;
34 l_effective_start_date date;
35 l_effective_end_Date date;
36 l_dummy_start_date date;
37 l_dummy_end_date date;
38 l_request_id number;
39 l_program_id number;
40 l_prog_appl_id number;
41 l_business_group_id number;
42 l_social_benefit_id number;
43 l_effective_date date;
44 l_absence_start_date date;
45 l_absence_end_date date;
46 l_proc varchar2(72) := g_package||'create_ie_sb_details';
47 CURSOR business_group_csr IS
48 SELECT business_group_id
49 FROM per_all_assignments_f
50 WHERE assignment_id = p_assignment_id
51 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
52 --
53 l_benefit_amount pay_ie_social_benefits_f.benefit_amount%type := p_benefit_amount;
57 begin
54 l_benefit_type pay_ie_social_benefits_f.benefit_type%type := p_benefit_type;
55 l_incident_id pay_ie_social_benefits_f.incident_id%type := p_incident_id;
56 --
58 hr_utility.set_location('Entering:'|| l_proc, 10);
59 --
60 -- Issue a savepoint
61 --
62 savepoint create_ie_sb_details;
63 --
64 -- Truncate the time portion from all IN date parameters
65 l_effective_date := trunc(p_effective_date);
66 l_absence_start_date := trunc(p_absence_start_date);
67 l_absence_end_date := trunc(p_absence_end_date);
68 -- Call Before Process User Hook
69 --
70 OPEN business_group_csr;
71 FETCH business_group_csr INTO l_business_group_id;
72 CLOSE business_group_csr;
73 --
74 if p_calculation_option = 'IE_OPTION0' then
75 l_absence_start_date := null;
76 l_absence_end_date := null;
77 l_benefit_amount := 0;
78 l_benefit_type := null;
79 l_benefit_type := null;
80 l_incident_id := null;
81 end if;
82
83 begin
84 pay_ie_sb_api_bk1.create_ie_sb_details_b
85 (p_effective_date => l_effective_date
86 ,p_business_group_id => l_business_group_id
87 ,p_assignment_id => p_assignment_id
88 ,p_absence_start_date => l_absence_start_date
89 ,p_absence_end_date => l_absence_end_date
90 ,p_benefit_amount => l_benefit_amount
91 ,p_benefit_type => l_benefit_type
92 ,p_calculation_option => p_calculation_option
93 ,p_reduced_tax_credit => p_reduced_tax_credit
94 ,p_reduced_standard_cutoff => p_reduced_standard_cutoff
95 ,p_incident_id => l_incident_id
96 );
97 exception
98 when hr_api.cannot_find_prog_unit then
99 hr_api.cannot_find_prog_unit_error
100 (p_module_name => 'create_ie_sb_details_f'
101 ,p_hook_type => 'BP'
102 );
103 end;
104 --
105 -- Validation in addition to Row Handlers
106 -- Process Logic
107 l_request_id := fnd_global.conc_request_id;
108 l_prog_appl_id := fnd_global.prog_appl_id;
109 l_program_id := fnd_global.conc_program_id;
110 pay_isb_ins.ins
111 (p_effective_date => l_effective_date
112 ,p_assignment_id => p_assignment_id
113 ,p_absence_start_date => l_absence_start_date
114 ,p_absence_end_date => l_absence_end_date
115 ,p_benefit_amount => l_benefit_amount
116 ,p_benefit_type => l_benefit_type
117 ,p_calculation_option => p_calculation_option
118 ,p_incident_id => l_incident_id
119 ,p_request_id => l_request_id
120 ,p_program_application_id => l_prog_appl_id
121 ,p_program_id => l_program_id
122 ,p_program_update_date => sysdate
123 ,p_reduced_tax_credit => p_reduced_tax_credit
124 ,p_reduced_standard_cutoff => p_reduced_standard_cutoff
125 ,p_social_benefit_id => l_social_benefit_id
126 ,p_object_version_number => l_object_version_number
127 ,p_effective_start_date => l_effective_start_date
128 ,p_effective_end_date => l_effective_end_date
129 );
130 -- Call After Process User Hook
131 --
132 begin
133 pay_ie_sb_api_bk1.create_ie_sb_details_a
134 (p_effective_date => l_effective_date
135 ,p_business_group_id => l_business_group_id
136 ,p_assignment_id => p_assignment_id
137 ,p_absence_start_date => l_absence_start_date
138 ,p_absence_end_date => l_absence_end_date
139 ,p_benefit_amount => l_benefit_amount
140 ,p_benefit_type => l_benefit_type
141 ,p_calculation_option => p_calculation_option
142 ,p_reduced_tax_credit => p_reduced_tax_credit
143 ,p_reduced_standard_cutoff => p_reduced_standard_cutoff
144 ,p_incident_id => l_incident_id
145 ,p_social_benefit_id => l_social_benefit_id
146 ,p_object_version_number => l_object_version_number
147 ,p_effective_start_date => l_effective_start_date
148 ,p_effective_end_date => l_effective_end_date
149 );
150 exception
151 when hr_api.cannot_find_prog_unit then
152 hr_api.cannot_find_prog_unit_error
153 (p_module_name => 'create_ie_sb_details_f'
154 ,p_hook_type => 'AP'
155 );
156 end;
157 --
158 -- When in validation only mode raise the Validate_Enabled exception
159 --
160 if p_validate then
161 raise hr_api.validate_enabled;
162 end if;
163 --
164 -- Set all output arguments
165 --
166 p_social_benefit_id := l_social_benefit_id;
167 p_object_version_number := l_object_version_number;
168 p_effective_start_date := l_effective_start_date;
169 p_effective_end_date := l_effective_end_Date;
170 --
171 hr_utility.set_location(' Leaving:'||l_proc, 70);
172 exception
173 when hr_api.validate_enabled then
174 --
175 -- As the Validate_Enabled exception has been raised
176 -- we must rollback to the savepoint
177 --
178 rollback to create_ie_sb_details;
179 --
180 -- Only set output warning arguments
181 -- (Any key or derived arguments must be set to null
182 -- when validation only mode is being used.)
183 --
184 p_social_benefit_id := null;
188 --
185 p_object_version_number := null;
186 hr_utility.set_location(' Leaving:'||l_proc, 80);
187 when others then
189 -- A validation or unexpected error has occured
190 --
191 rollback to create_ie_sb_details;
192 p_social_benefit_id := null;
193 p_object_version_number := null;
194 p_effective_start_date := null;
195 p_effective_end_date := null;
196 hr_utility.set_location(' Leaving:'||l_proc, 90);
197 raise;
198 end create_ie_sb_details;
199 --
200 --
201 --
202 -- ----------------------------------------------------------------------------
203 -- |------------------------< update_ie_sb_details >------------------------|
204 -- ----------------------------------------------------------------------------
205 --
206 procedure update_ie_sb_details
207 (p_validate in boolean default false
208 ,p_effective_date in date
209 ,p_datetrack_update_mode in varchar2
210 ,p_absence_start_date in date default hr_api.g_date
211 ,p_absence_end_date in date default hr_api.g_date
212 ,p_benefit_amount in number default hr_api.g_number
213 ,p_benefit_type in varchar2 default hr_api.g_varchar2
214 ,p_calculation_option in varchar2 default hr_api.g_varchar2
215 ,p_reduced_tax_credit in number default hr_api.g_number
216 ,p_reduced_standard_cutoff in number default hr_api.g_number
217 ,p_incident_id in number default hr_api.g_number
218 ,p_social_benefit_id in number
219 ,p_object_version_number in out nocopy number
220 ,p_effective_start_date out nocopy date
221 ,p_effective_end_date out nocopy date
222 ) is
223 l_proc varchar2(72) := g_package||'update_ie_sb_details';
224 l_effective_date date;
225 l_datetrack_update_mode number;
226 l_absence_start_date date;
227 l_absence_end_date date;
228 l_calculation_option varchar2(30);
229 l_social_benefit_id number;
230 l_object_version_number number := p_object_version_number;
231 l_effective_start_date date;
232 l_effective_end_date date;
233 l_assignment_id number;
234 l_business_group_id number;
235 l_update_mode varchar2(30);
236 l_request_id number;
237 l_prog_appl_id number;
238 l_program_id number;
239 --
240 l_benefit_amount pay_ie_social_benefits_f.benefit_amount%type := p_benefit_amount;
241 l_benefit_type pay_ie_social_benefits_f.benefit_type%type := p_benefit_type;
242 l_incident_id pay_ie_social_benefits_f.incident_id%type := p_incident_id;
243 l_reduced_tax_credit pay_ie_social_benefits_f.reduced_tax_credit%type := p_reduced_tax_credit;
244 l_reduced_standard_cutoff pay_ie_social_benefits_f.reduced_standard_cutoff%type := p_reduced_standard_cutoff;
245
246 --
247 CURSOR asg_csr IS
248 SELECT assignment_id,calculation_option
249 FROM pay_ie_social_benefits_f
250 WHERE social_benefit_id = p_social_benefit_id
251 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
252 --
253 CURSOR business_group_csr IS
254 SELECT business_group_id
255 FROM per_all_assignments_f
256 WHERE assignment_id = l_assignment_id
257 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
258 --
259 t_calculation_option pay_ie_social_benefits_f.calculation_option%type;
260 begin
261 hr_utility.set_location('Entering:'|| l_proc, 10);
262 --
263 -- Issue a savepoint
264 --
265 savepoint update_ie_sb_details;
266 --
267 -- Truncate the time portion from all IN date parameters
268 --
269 l_effective_date := trunc(p_effective_date);
270 l_absence_start_date := trunc(p_absence_start_date);
271 l_absence_end_date := trunc(p_absence_end_date);
272 --
273 -- Get assignment_id from the cursor
274 OPEN asg_csr;
275 FETCH asg_csr INTO l_assignment_id,t_calculation_option;
276 CLOSE asg_csr;
277 --
278 -- Get Business Group Id
279 --
280 OPEN business_group_csr;
281 FETCH business_group_csr INTO l_business_group_id;
282 CLOSE business_group_csr;
283 if p_calculation_option = 'IE_OPTION0' then
284 l_absence_start_date := null;
285 l_absence_end_date := null;
286 l_benefit_amount := 0;
287 l_benefit_type := null;
288 l_incident_id := null;
289 l_reduced_tax_credit := 0;
290 l_reduced_standard_cutoff := 0;
291 end if;
292
293 if t_calculation_option is not null then
294 if t_calculation_option = 'IE_OPTION1' and
295 (p_calculation_option = 'IE_OPTION2' or
296 p_calculation_option = 'IE_OPTION3' or
297 p_calculation_option = 'IE_OPTION4') then
298 fnd_message.set_name('PAY', 'HR_IE_NO_VALID_CALC_OPTION');
299 fnd_message.raise_error;
300 elsif t_calculation_option = 'IE_OPTION2' and
301 (p_calculation_option = 'IE_OPTION1' or
302 p_calculation_option = 'IE_OPTION3' or
306 elsif t_calculation_option = 'IE_OPTION3' and
303 p_calculation_option = 'IE_OPTION4') then
304 fnd_message.set_name('PAY', 'HR_IE_NO_VALID_CALC_OPTION');
305 fnd_message.raise_error;
307 (p_calculation_option = 'IE_OPTION1' or
308 p_calculation_option = 'IE_OPTION2' or
309 p_calculation_option = 'IE_OPTION4') then
310 fnd_message.set_name('PAY', 'HR_IE_NO_VALID_CALC_OPTION');
311 fnd_message.raise_error;
312 elsif t_calculation_option = 'IE_OPTION4' and
313 (p_calculation_option = 'IE_OPTION1' or
314 p_calculation_option = 'IE_OPTION2' or
315 p_calculation_option = 'IE_OPTION3') then
316 fnd_message.set_name('PAY', 'HR_IE_NO_VALID_CALC_OPTION');
317 fnd_message.raise_error;
318 end if;
319 end if;
320 --
321 -- Call Before Process User Hook
322 --
323 begin
324 pay_ie_sb_api_bk2.update_ie_sb_details_b
325 (p_effective_date => l_effective_date
326 ,p_business_group_id => l_business_group_id
327 ,p_datetrack_update_mode => p_datetrack_update_mode
328 ,p_absence_start_date => l_absence_start_date
329 ,p_absence_end_date => l_absence_end_date
330 ,p_benefit_amount => l_benefit_amount
331 ,p_benefit_type => l_benefit_type
332 ,p_calculation_option => p_calculation_option
333 ,p_reduced_tax_credit => l_reduced_tax_credit
334 ,p_reduced_standard_cutoff => l_reduced_standard_cutoff
335 ,p_incident_id => l_incident_id
336 ,p_social_benefit_id => p_social_benefit_id
337 ,p_object_version_number => l_object_version_number
338 );
339 exception
340 when hr_api.cannot_find_prog_unit then
341 hr_api.cannot_find_prog_unit_error
342 (p_module_name => 'update_ie_sb_details'
343 ,p_hook_type => 'BP'
344 );
345 end;
346 --
347 -- Set parameter values
348 --
349 l_request_id := fnd_global.conc_request_id;
350 l_prog_appl_id := fnd_global.prog_appl_id;
351 l_program_id := fnd_global.conc_program_id;
352 --
353 -- Call row handler procedure to update prsi details
354 --
355 pay_isb_upd.upd
356 (p_effective_date => l_effective_date
357 ,p_datetrack_mode => p_datetrack_update_mode
358 ,p_social_benefit_id => p_social_benefit_id
359 ,p_object_version_number => l_object_version_number
360 ,p_assignment_id => l_assignment_id
361 ,p_absence_start_date => l_absence_start_date
362 ,p_absence_end_date => l_absence_end_date
363 ,p_benefit_amount => l_benefit_amount
364 ,p_benefit_type => l_benefit_type
365 ,p_calculation_option => p_calculation_option
366 ,p_incident_id => l_incident_id
367 ,p_request_id => l_request_id
368 ,p_program_application_id => l_prog_appl_id
369 ,p_program_id => l_program_id
370 ,p_program_update_date => sysdate
371 ,p_reduced_tax_credit => l_reduced_tax_credit
372 ,p_reduced_standard_cutoff => l_reduced_standard_cutoff
373 ,p_effective_start_date => l_effective_start_date
374 ,p_effective_end_date => l_effective_end_date
375 );
376
377 begin
378 pay_ie_sb_api_bk2.update_ie_sb_details_a
379 (p_effective_date => l_effective_date
380 ,p_business_group_id => l_business_group_id
381 ,p_datetrack_update_mode => p_datetrack_update_mode
382 ,p_absence_start_date => l_absence_start_date
383 ,p_absence_end_date => l_absence_end_date
384 ,p_benefit_amount => l_benefit_amount
385 ,p_benefit_type => l_benefit_type
386 ,p_calculation_option => p_calculation_option
387 ,p_reduced_tax_credit => l_reduced_tax_credit
388 ,p_reduced_standard_cutoff => l_reduced_standard_cutoff
389 ,p_incident_id => l_incident_id
390 ,p_social_benefit_id => p_social_benefit_id
391 ,p_object_version_number => l_object_version_number
392 ,p_effective_start_date => l_effective_start_date
393 ,p_effective_end_date => l_effective_end_date
394 );
395 exception
396 when hr_api.cannot_find_prog_unit then
397 hr_api.cannot_find_prog_unit_error
398 (p_module_name => 'pay_ie_sb_api_bk2.update_ie_sb_details_a'
399 ,p_hook_type => 'AP'
400 );
401 end;
402 --
403 -- When in validation only mode raise the Validate_Enabled exception
404 --
405 if p_validate then
406 raise hr_api.validate_enabled;
407 end if;
408 --
409 --
410 -- Set all output arguments
411 --
412 p_object_version_number := l_object_version_number;
413 p_effective_start_date := l_effective_start_date;
414 p_effective_end_date := l_effective_end_Date;
415 --
416 hr_utility.set_location(' Leaving:'||l_proc, 70);
417 exception
418 when hr_api.validate_enabled then
422 --
419 --
420 -- As the Validate_Enabled exception has been raised
421 -- we must rollback to the savepoint
423 rollback to update_ie_sb_details;
424 --
425 -- Only set output warning arguments
426 -- (Any key or derived arguments must be set to null
427 -- when validation only mode is being used.)
428 --
429 p_object_version_number := l_object_version_number;
430 p_effective_start_date := null;
431 p_effective_end_Date := null;
432 hr_utility.set_location(' Leaving:'||l_proc, 80);
433 when others then
434 --
435 -- A validation or unexpected error has occured
436 --
437 rollback to update_ie_sb_details;
438 p_object_version_number := l_object_version_number;
439 p_effective_start_date := null;
440 p_effective_end_date := null;
441 hr_utility.set_location(' Leaving:'||l_proc, 90);
442 raise;
443 end update_ie_sb_details;
444 --
445 -- ----------------------------------------------------------------------------
446 -- |----------------------< delete_ie_social_benefits >-----------------------|
447 -- ----------------------------------------------------------------------------
448 --
449 procedure delete_ie_sb_details
450 (p_validate in boolean default false
451 ,p_effective_date in date
452 ,p_datetrack_delete_mode in varchar2
453 ,p_social_benefit_id in number
454 ,p_object_version_number in out nocopy number
455 ,p_effective_start_date out nocopy date
456 ,p_effective_end_date out nocopy date
457 )
458 is
459 l_proc varchar2(72) := g_package||'delete_ie_social_benefits';
460 l_assignment_id number;
461 l_effective_date date;
462 l_business_group_id number;
463 l_social_benefit_id number;
464 l_object_version_number number := p_object_version_number;
465 l_effective_start_date date;
466 l_effective_end_date date;
467 --
468 CURSOR asg_csr IS
469 SELECT assignment_id
470 FROM pay_ie_social_benefits_f
471 WHERE social_benefit_id = p_social_benefit_id
472 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
473 --
474 CURSOR business_group_csr IS
475 SELECT business_group_id
476 FROM per_all_assignments_f
477 WHERE assignment_id = l_assignment_id
478 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
479 begin
480 hr_utility.set_location('Entering:'|| l_proc, 10);
481 --
482 -- Issue a savepoint
483 --
484 savepoint delete_ie_sb_details;
485 --
486 -- Get assignment_id from the cursor
487 OPEN asg_csr;
488 FETCH asg_csr INTO l_assignment_id;
489 CLOSE asg_csr;
490 -- Get Business_group_id
491 OPEN business_group_csr;
492 FETCH business_group_csr INTO l_business_group_id;
493 CLOSE business_group_csr;
494 --
495 -- Call Before Process User Hook
496 --
497 begin
498 pay_ie_sb_api_bk3.delete_ie_sb_details_b
499 (p_effective_date => p_effective_date
500 ,p_business_group_id => l_business_group_id
501 ,p_datetrack_delete_mode => p_datetrack_delete_mode
502 ,p_social_benefit_id => p_social_benefit_id
503 ,p_object_version_number => l_object_version_number
504 );
505 exception
506 when hr_api.cannot_find_prog_unit then
507 hr_api.cannot_find_prog_unit_error
508 (p_module_name => 'delete_ie_sb_details'
509 ,p_hook_type => 'BP'
510 );
511 end;
512 --
513 -- Process Logic
514 --
515 -- Call row handler procedure to update prsi details
516 --
517 pay_isb_del.del
518 (P_EFFECTIVE_DATE => p_effective_date
519 ,P_DATETRACK_MODE => p_datetrack_delete_mode
520 ,P_SOCIAL_BENEFIT_ID => p_social_benefit_id
521 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
522 ,P_EFFECTIVE_START_DATE => l_effective_start_date
523 ,P_EFFECTIVE_END_DATE => l_effective_end_date
524 );
525 --
526 -- Call After Process User Hook
527 --
528 begin
529 pay_ie_sb_api_bk3.delete_ie_sb_details_a
530 (p_effective_date => p_effective_date
531 ,p_business_group_id => l_business_group_id
532 ,p_datetrack_delete_mode => p_datetrack_delete_mode
533 ,p_social_benefit_id => p_social_benefit_id
534 ,p_object_version_number => l_object_version_number
535 ,p_effective_start_date => l_effective_start_date
536 ,p_effective_end_date => l_effective_end_date
537 );
538 exception
539 when hr_api.cannot_find_prog_unit then
540 hr_api.cannot_find_prog_unit_error
541 (p_module_name => 'delete_ie_sb_details'
542 ,p_hook_type => 'AP'
543 );
544 end;
545 --
546 -- When in validation only mode raise the Validate_Enabled exception
547 --
548 if p_validate then
549 raise hr_api.validate_enabled;
550 end if;
551 --
552 -- Set all output arguments
553 --
557 --
554 p_object_version_number := l_object_version_number;
555 p_effective_start_date := l_effective_start_date;
556 p_effective_end_date := l_effective_end_Date;
558 hr_utility.set_location(' Leaving:'||l_proc, 70);
559 --
560 exception
561 when hr_api.validate_enabled then
562 --
563 -- As the Validate_Enabled exception has been raised
564 -- we must rollback to the savepoint
565 --
566 rollback to delete_ie_sb_details;
567 --
568 -- Only set output warning arguments
569 -- (Any key or derived arguments must be set to null
570 -- when validation only mode is being used.)
571 --
572 p_object_version_number := l_object_version_number;
573 p_effective_start_date := null;
574 p_effective_end_Date := null;
575 --
576 hr_utility.set_location(' Leaving:'||l_proc, 80);
577 when others then
578 --
579 -- A validation or unexpected error has occured
580 --
581 rollback to delete_ie_sb_details;
582 p_object_version_number := l_object_version_number;
583 p_effective_start_date := null;
584 p_effective_end_date := null;
585 hr_utility.set_location(' Leaving:'||l_proc, 90);
586 raise;
587 end delete_ie_sb_details;
588 end pay_ie_sb_api;