[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_PRSI_API
Source
1 Package Body pay_ie_prsi_api as
2 /* $Header: pysidapi.pkb 115.2 2002/12/06 14:46:25 jford noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pay_ie_prsi_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< find_update_mode >---------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 FUNCTION find_update_mode
13 (p_period_end_date in date
14 ,p_prsi_details_id in number) RETURN VARCHAR2 IS
15 --
16 --
17 CURSOR prsi_csr IS
18 SELECT hr_api.g_update_change_insert update_mode
19 FROM pay_ie_prsi_details_f
20 WHERE prsi_details_id = p_prsi_details_id
21 AND effective_start_date > p_period_end_date;
22 --
23 l_mode VARCHAR2(30);
24 Begin
25 --
26 OPEN prsi_csr;
27 FETCH prsi_csr INTO l_mode;
28 --
29 IF prsi_csr%notfound THEN
30 l_mode := hr_api.g_update;
31 END IF;
32 CLOSE prsi_csr;
33 --
34 RETURN l_mode;
35 End find_update_mode;
36 --
37 -- ----------------------------------------------------------------------------
38 -- |------------------------< create_ie_prsi_details >------------------------|
39 -- ----------------------------------------------------------------------------
40 --
41 procedure create_ie_prsi_details
42 (p_validate in boolean default false
43 ,p_effective_date in date
44 ,p_assignment_id in number
45 ,p_contribution_class in varchar2
46 ,p_overridden_subclass in varchar2 default Null
47 ,p_soc_ben_flag in varchar2 default Null
48 ,p_soc_ben_start_date in date default Null
49 ,p_overridden_ins_weeks in number default Null
50 ,p_non_standard_ins_weeks in number default Null
51 ,p_exemption_start_date in date default Null
52 ,p_exemption_end_date in date default Null
53 ,p_cert_issued_by in varchar2 default Null
54 ,p_director_flag in varchar2 default Null
55 ,p_community_flag in varchar2 default Null
56 ,p_prsi_details_id out nocopy number
57 ,p_object_version_number out nocopy number
58 ,p_effective_start_date out nocopy date
59 ,p_effective_end_date out nocopy date
60 ) is
61 --
62 -- Declare cursors and local variables
63 --
64 CURSOR period_csr IS
65 SELECT ptp.start_date, ptp.end_Date
66 FROM per_time_periods ptp, per_all_assignments_f paa
67 WHERE paa.assignment_id = p_assignment_id
68 AND paa.payroll_id = ptp.payroll_id
69 AND p_effective_date BETWEEN ptp.start_date AND ptp.end_date;
70 --
71 period_rec period_csr%ROWTYPE;
72 --
73 l_flag VARCHAR2(30);
74 l_proc varchar2(72) := g_package||'create_ie_prsi_details';
75 l_exemption_start_date date;
76 l_exemption_end_date date;
77 l_prsi_details_id number;
78 l_object_version_number number;
79 l_effective_start_date date;
80 l_effective_end_Date date;
81 l_dummy_start_date date;
82 l_dummy_end_date date;
83 l_request_id number;
84 l_program_id number;
85 l_prog_appl_id number;
86 l_business_group_id number;
87 --
88 CURSOR business_group_csr IS
89 SELECT business_group_id
90 FROM per_all_assignments_f
91 WHERE assignment_id = p_assignment_id
92 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
93 --
94 --
95 begin
96 --
97 -- Issue a savepoint
98 --
99 savepoint create_ie_prsi_details;
100 --
101 -- Truncate the time portion from all IN date parameters
102 --
103 l_exemption_start_date := trunc(p_exemption_start_date);
104 l_exemption_end_date := trunc(p_exemption_end_date);
105 --
106 -- Get business_group_id
107 --
108 OPEN business_group_csr;
109 FETCH business_group_csr INTO l_business_group_id;
110 CLOSE business_group_csr;
111 --
112 -- Call Before Process User Hook
113 --
114 begin
115 pay_ie_prsi_bk1.create_ie_prsi_details_b
116 (p_effective_date => p_effective_date
117 ,p_business_group_id => l_business_group_id
118 ,p_assignment_id => p_assignment_id
119 ,p_contribution_class => p_contribution_class
120 ,p_overridden_subclass => p_overridden_subclass
121 ,p_soc_ben_flag => p_soc_ben_flag
122 ,p_soc_ben_start_date => p_soc_ben_start_date
123 ,p_overridden_ins_weeks => p_overridden_ins_weeks
124 ,p_non_standard_ins_weeks => p_non_standard_ins_weeks
125 ,p_exemption_start_date => l_exemption_start_date
126 ,p_exemption_end_date => l_exemption_end_date
127 ,p_cert_issued_by => p_cert_issued_by
128 ,p_director_flag => p_director_flag
129 ,p_community_flag => p_community_flag
130 );
131 exception
132 when hr_api.cannot_find_prog_unit then
133 hr_api.cannot_find_prog_unit_error
134 (p_module_name => 'create_ie_prsi_details'
135 ,p_hook_type => 'BP'
136 );
137 end;
138 --
139 -- Process Logic
140 --
141 -- Set parameter values
142 --
143 l_request_id := fnd_global.conc_request_id;
144 l_prog_appl_id := fnd_global.prog_appl_id;
145 l_program_id := fnd_global.conc_program_id;
146 --
147 -- Insert record in pay_ie_prsi_details_f
148 --
149 pay_sid_ins.ins
150 ( p_effective_date => p_effective_date
151 ,p_assignment_id => p_assignment_id
152 ,p_contribution_class => p_contribution_class
153 ,p_overridden_subclass => p_overridden_subclass
154 ,p_soc_ben_flag => p_soc_ben_flag
155 ,p_soc_ben_start_date => p_soc_ben_start_date
156 ,p_overridden_ins_weeks => p_overridden_ins_weeks
157 ,p_non_standard_ins_weeks => p_non_standard_ins_weeks
158 ,p_exemption_start_date => l_exemption_start_date
159 ,p_exemption_end_date => l_exemption_end_date
160 ,p_cert_issued_by => p_cert_issued_by
161 ,p_director_flag => p_director_flag
162 ,p_community_flag => p_community_flag
163 ,p_request_id => l_request_id
164 ,p_program_application_id => l_prog_appl_id
165 ,p_program_id => l_program_id
166 ,p_program_update_date => sysdate
167 ,p_prsi_details_id => l_prsi_details_id
168 ,p_object_version_number => l_object_version_number
169 ,p_effective_start_date => l_effective_start_date
170 ,p_effective_end_date => l_effective_end_date
171 );
172 --
173 -- Get Start and End Date of current period
174 OPEN period_csr;
175 FETCH period_csr INTO period_rec;
176 CLOSE period_csr;
177 --
178 -- Check if overridden number of insurable weeks need to be updated to null
179 IF (p_overridden_ins_weeks IS NOT NULL)
180 AND (period_rec.end_date IS NOT NULL)
181 AND (l_effective_end_date > period_rec.end_date ) THEN
182 --
183 -- Lock above inserted row to refresh g_old_rec of row handler
184 --
185 DECLARE
186 l_validation_start_date date;
187 l_validation_end_date date;
188 BEGIN
189 pay_sid_shd.lck
190 ( p_effective_date => period_rec.end_date+1
191 ,p_datetrack_mode => hr_api.g_update
192 ,p_prsi_details_id => l_prsi_details_id
193 ,p_object_version_number => l_object_version_number
194 ,p_validation_start_date => l_validation_start_date
195 ,p_validation_end_date => l_validation_end_date );
196 END;
197 --
198 -- Update prsi details as of the begining of next pay period
199 --
200 pay_sid_upd.upd
201 ( p_effective_date => period_rec.end_date+1
202 ,p_datetrack_mode => hr_api.g_update
203 ,p_prsi_details_id => l_prsi_details_id
204 ,p_object_version_number => l_object_version_number
205 ,p_assignment_id => p_assignment_id
206 ,p_contribution_class => p_contribution_class
207 ,p_overridden_subclass => p_overridden_subclass
208 ,p_soc_ben_flag => p_soc_ben_flag
209 ,p_soc_ben_start_date => p_soc_ben_start_date
210 ,p_overridden_ins_weeks => NULL
211 ,p_non_standard_ins_weeks => p_non_standard_ins_weeks
212 ,p_exemption_start_date => l_exemption_start_date
213 ,p_exemption_end_date => l_exemption_end_date
214 ,p_cert_issued_by => p_cert_issued_by
215 ,p_director_flag => p_director_flag
216 ,p_community_flag => p_community_flag
217 ,p_request_id => l_request_id
218 ,p_program_application_id => l_prog_appl_id
219 ,p_program_id => l_program_id
220 ,p_program_update_date => sysdate
221 ,p_effective_start_date => l_dummy_start_date
222 ,p_effective_end_date => l_dummy_end_date
223 );
224 -- set out variables
225 l_object_version_number := l_object_version_number - 1;
226 l_effective_end_date := period_rec.end_date;
227 END IF;
228 --
229 -- Call After Process User Hook
230 --
231 begin
232 pay_ie_prsi_bk1.create_ie_prsi_details_a
233 (p_effective_date => p_effective_date
234 ,p_business_group_id => l_business_group_id
235 ,p_assignment_id => p_assignment_id
236 ,p_contribution_class => p_contribution_class
237 ,p_overridden_subclass => p_overridden_subclass
238 ,p_soc_ben_flag => p_soc_ben_flag
239 ,p_soc_ben_start_date => p_soc_ben_start_date
240 ,p_overridden_ins_weeks => p_overridden_ins_weeks
241 ,p_non_standard_ins_weeks => p_non_standard_ins_weeks
242 ,p_exemption_start_date => l_exemption_start_date
243 ,p_exemption_end_date => l_exemption_end_date
244 ,p_cert_issued_by => p_cert_issued_by
245 ,p_director_flag => p_director_flag
246 ,p_community_flag => p_community_flag
247 ,p_prsi_details_id => l_prsi_details_id
248 ,p_object_version_number => l_object_version_number
249 ,p_effective_start_date => l_effective_start_date
250 ,p_effective_end_date => l_effective_end_date
251 );
252 exception
253 when hr_api.cannot_find_prog_unit then
254 hr_api.cannot_find_prog_unit_error
255 (p_module_name => 'create_ie_prsi_details'
256 ,p_hook_type => 'AP'
257 );
258 end;
259 --
260 -- When in validation only mode raise the Validate_Enabled exception
261 --
262 if p_validate then
263 raise hr_api.validate_enabled;
264 end if;
265 --
266 -- Set all output arguments
267 --
268 p_prsi_details_id := l_prsi_details_id;
269 p_object_version_number := l_object_version_number;
270 p_effective_start_date := l_effective_start_date;
271 p_effective_end_date := l_effective_end_Date;
272 --
273 exception
274 when hr_api.validate_enabled then
275 --
276 -- As the Validate_Enabled exception has been raised
277 -- we must rollback to the savepoint
278 --
279 rollback to create_ie_prsi_details;
280 --
281 -- Only set output warning arguments
282 -- (Any key or derived arguments must be set to null
283 -- when validation only mode is being used.)
284 --
285 p_prsi_details_id := null;
286 p_object_version_number := null;
287 p_effective_start_date := null;
288 p_effective_end_Date := null;
289 hr_utility.set_location(' Leaving:'||l_proc, 80);
290 when others then
291 --
292 -- A validation or unexpected error has occured
293 --
294 rollback to create_ie_prsi_details;
295 p_object_version_number := l_object_version_number;
296 p_effective_start_date := null;
297 p_effective_end_date := null;
298
299 hr_utility.set_location(' Leaving:'||l_proc, 90);
300 raise;
301 end create_ie_prsi_details;
302 --
303 --
304 -- ----------------------------------------------------------------------------
305 -- |------------------------< update_ie_prsi_details >------------------------|
306 -- ----------------------------------------------------------------------------
307 --
308 procedure update_ie_prsi_details
309 (p_validate in boolean default false
310 ,p_effective_date in date
311 ,p_datetrack_update_mode in varchar2
312 ,p_prsi_details_id in number
313 ,p_contribution_class in varchar2 default hr_api.g_varchar2
314 ,p_overridden_subclass in varchar2 default hr_api.g_varchar2
315 ,p_soc_ben_flag in varchar2 default hr_api.g_varchar2
316 ,p_soc_ben_start_date in date default hr_api.g_date
317 ,p_overridden_ins_weeks in number default hr_api.g_number
318 ,p_non_standard_ins_weeks in number default hr_api.g_number
319 ,p_exemption_start_date in date default hr_api.g_date
320 ,p_exemption_end_date in date default hr_api.g_date
321 ,p_cert_issued_by in varchar2 default hr_api.g_varchar2
322 ,p_director_flag in varchar2 default hr_api.g_varchar2
323 ,p_community_flag in varchar2 default hr_api.g_varchar2
324 ,p_object_version_number in out nocopy number
325 ,p_effective_start_date out nocopy date
326 ,p_effective_end_date out nocopy date
327 ) IS
328 --
329 -- Declare cursors and local variables
333 l_exemption_end_date date;
330 --
331 l_proc varchar2(72) := g_package||'update_ie_prsi_details';
332 l_exemption_start_date date;
334 l_object_version_number number := p_object_version_number;
335 l_effective_start_date date;
336 l_effective_end_Date date;
337 l_dummy_start_date date;
338 l_dummy_end_Date date;
339 l_request_id number;
340 l_program_id number;
341 l_prog_appl_id number;
342 l_p45_effective_date date;
343 l_assignment_id number;
344 l_business_group_id number;
345 l_update_mode varchar2(30);
346 --
347 CURSOR asg_csr IS
348 SELECT assignment_id
349 FROM pay_ie_prsi_details_f
350 WHERE prsi_details_id = p_prsi_details_id
351 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
352 --
353 CURSOR business_group_csr IS
354 SELECT business_group_id
355 FROM per_all_assignments_f
356 WHERE assignment_id = l_assignment_id
357 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
358 --
359 CURSOR period_csr IS
360 SELECT ptp.start_date, ptp.end_Date
361 FROM per_time_periods ptp, per_all_assignments_f paa
362 WHERE paa.assignment_id = l_assignment_id
363 AND paa.payroll_id = ptp.payroll_id
364 AND p_effective_date BETWEEN ptp.start_date AND ptp.end_date;
365 --
366 period_rec period_csr%ROWTYPE;
367 --
368 --
369 begin
370 --
371 -- Issue a savepoint
372 --
373 savepoint update_ie_prsi_details;
374 --
375 -- Truncate the time portion from all IN date parameters
376 --
377 l_exemption_start_date := trunc(p_exemption_start_date);
378 l_exemption_end_date := trunc(p_exemption_end_date);
379 --
380 -- Get assignment_id from the cursor
381 OPEN asg_csr;
382 FETCH asg_csr INTO l_assignment_id;
383 CLOSE asg_csr;
384 --
385 -- Get Business Group Id
386 --
387 OPEN business_group_csr;
388 FETCH business_group_csr INTO l_business_group_id;
389 CLOSE business_group_csr;
390 --
391 -- Call Before Process User Hook
392 --
393 begin
394 pay_ie_prsi_bk2.update_ie_prsi_details_b
395 (p_effective_date => p_effective_date
396 ,p_datetrack_update_mode => p_datetrack_update_mode
397 ,p_business_group_id => l_business_group_id
398 ,p_prsi_details_id => p_prsi_details_id
399 ,p_contribution_class => p_contribution_class
400 ,p_overridden_subclass => p_overridden_subclass
401 ,p_soc_ben_flag => p_soc_ben_flag
402 ,p_soc_ben_start_date => p_soc_ben_start_date
403 ,p_overridden_ins_weeks => p_overridden_ins_weeks
404 ,p_non_standard_ins_weeks => p_non_standard_ins_weeks
405 ,p_exemption_start_date => p_exemption_start_date
406 ,p_exemption_end_date => p_exemption_end_date
407 ,p_cert_issued_by => p_cert_issued_by
408 ,p_director_flag => p_director_flag
409 ,p_community_flag => p_community_flag
410 ,p_object_version_number => l_object_version_number
411 );
412 exception
413 when hr_api.cannot_find_prog_unit then
414 hr_api.cannot_find_prog_unit_error
415 (p_module_name => 'update_ie_prsi_details'
416 ,p_hook_type => 'BP'
417 );
418 end;
419 --
420 -- Process Logic
421 --
422 -- Set parameter values
423 --
424 l_request_id := fnd_global.conc_request_id;
425 l_prog_appl_id := fnd_global.prog_appl_id;
426 l_program_id := fnd_global.conc_program_id;
427 --
428 -- Call row handler procedure to update prsi details
429 --
430 pay_sid_upd.upd
431 ( p_effective_date => p_effective_date
432 ,p_datetrack_mode => p_datetrack_update_mode
433 ,p_prsi_details_id => p_prsi_details_id
434 ,p_object_version_number => l_object_version_number
435 ,p_assignment_id => l_assignment_id
436 ,p_contribution_class => p_contribution_class
437 ,p_overridden_subclass => p_overridden_subclass
438 ,p_soc_ben_flag => p_soc_ben_flag
439 ,p_soc_ben_start_date => p_soc_ben_start_date
440 ,p_overridden_ins_weeks => p_overridden_ins_weeks
441 ,p_non_standard_ins_weeks => p_non_standard_ins_weeks
442 ,p_exemption_start_date => p_exemption_start_date
443 ,p_exemption_end_date => p_exemption_end_date
444 ,p_cert_issued_by => p_cert_issued_by
445 ,p_director_flag => p_director_flag
446 ,p_community_flag => p_community_flag
447 ,p_request_id => l_request_id
448 ,p_program_application_id => l_prog_appl_id
449 ,p_program_id => l_program_id
450 ,p_program_update_date => sysdate
451 ,p_effective_start_date => l_effective_start_date
452 ,p_effective_end_date => l_effective_end_date
453 );
454 --
458 CLOSE period_csr;
455 -- Get Start and End Date of current period
456 OPEN period_csr;
457 FETCH period_csr INTO period_rec;
459 --
460 -- Check if overridden_ins_weeks needs to be set to null as of next pay period
461 IF (p_overridden_ins_weeks IS NOT NULL)
462 AND (period_rec.end_date IS NOT NULL)
463 AND (l_effective_end_date > period_rec.end_date ) THEN
464 --
465 -- Set datetrack update mode to UPDATE if future rows don't exist
466 -- else set it to UPDATE_CHANGE_INSERT
467 --
468 l_update_mode := find_update_mode( p_period_end_date => period_rec.end_date
469 ,p_prsi_details_id => p_prsi_details_id );
470 --
471 -- Lock row to refresh g_old_rec of row handler
472 --
473 DECLARE
474 l_validation_start_date date;
475 l_validation_end_date date;
476 BEGIN
477 pay_sid_shd.lck
478 ( p_effective_date => period_rec.end_date+1
479 ,p_datetrack_mode => l_update_mode
480 ,p_prsi_details_id => p_prsi_details_id
481 ,p_object_version_number => l_object_version_number
482 ,p_validation_start_date => l_validation_start_date
483 ,p_validation_end_date => l_validation_end_date );
484 END;
485 --
486 -- Update record in the table
487 pay_sid_upd.upd
488 ( p_effective_date => period_rec.end_date+1
489 ,p_datetrack_mode => l_update_mode
490 ,p_prsi_details_id => p_prsi_details_id
491 ,p_object_version_number => l_object_version_number
492 ,p_assignment_id => l_assignment_id
493 ,p_contribution_class => p_contribution_class
494 ,p_overridden_subclass => p_overridden_subclass
495 ,p_soc_ben_flag => p_soc_ben_flag
496 ,p_soc_ben_start_date => p_soc_ben_start_date
497 ,p_overridden_ins_weeks => NULL
498 ,p_non_standard_ins_weeks => p_non_standard_ins_weeks
499 ,p_exemption_start_date => p_exemption_start_date
500 ,p_exemption_end_date => p_exemption_end_date
501 ,p_cert_issued_by => p_cert_issued_by
502 ,p_director_flag => p_director_flag
503 ,p_community_flag => p_community_flag
504 ,p_request_id => l_request_id
505 ,p_program_application_id => l_prog_appl_id
506 ,p_program_id => l_program_id
507 ,p_program_update_date => sysdate
508 ,p_effective_start_date => l_dummy_start_date
509 ,p_effective_end_date => l_dummy_end_date
510 );
511 --
512 -- set out variables
513 l_object_version_number := l_object_version_number - 1;
514 l_effective_end_date := period_rec.end_date;
515 END IF;
516 --
517 -- Get Start and End Date of current period
518
519 -- Call After Process User Hook
520 --
521 begin
522 pay_ie_prsi_bk2.update_ie_prsi_details_a
523 (p_effective_date => p_effective_date
524 ,p_business_group_id => l_business_group_id
525 ,p_datetrack_update_mode => p_datetrack_update_mode
526 ,p_prsi_details_id => p_prsi_details_id
527 ,p_contribution_class => p_contribution_class
528 ,p_overridden_subclass => p_overridden_subclass
529 ,p_soc_ben_flag => p_soc_ben_flag
530 ,p_soc_ben_start_date => p_soc_ben_start_date
531 ,p_overridden_ins_weeks => p_overridden_ins_weeks
532 ,p_non_standard_ins_weeks => p_non_standard_ins_weeks
533 ,p_exemption_start_date => p_exemption_start_date
534 ,p_exemption_end_date => p_exemption_end_date
540 ,p_effective_end_date => l_effective_end_date
535 ,p_cert_issued_by => p_cert_issued_by
536 ,p_director_flag => p_director_flag
537 ,p_community_flag => p_community_flag
538 ,p_object_version_number => l_object_version_number
539 ,p_effective_start_date => l_effective_start_date
541 );
542 exception
543 when hr_api.cannot_find_prog_unit then
544 hr_api.cannot_find_prog_unit_error
545 (p_module_name => 'update_ie_prsi_details'
546 ,p_hook_type => 'AP'
547 );
548 end;
549 --
550 -- When in validation only mode raise the Validate_Enabled exception
551 --
552 if p_validate then
553 raise hr_api.validate_enabled;
554 end if;
555 --
556 -- Set all output arguments
557 --
558 p_object_version_number := l_object_version_number;
559 p_effective_start_date := l_effective_start_date;
560 p_effective_end_date := l_effective_end_Date;
561 --
562 exception
563 when hr_api.validate_enabled then
564 --
565 -- As the Validate_Enabled exception has been raised
566 -- we must rollback to the savepoint
567 --
568 rollback to update_ie_prsi_details;
569 --
570 -- Only set output warning arguments
571 -- (Any key or derived arguments must be set to null
572 -- when validation only mode is being used.)
573 --
574 -- IN OUT parameter should be reset to its IN value
575 -- therefore no need to reset p_object_version_number
576 p_effective_start_date := null;
577 p_effective_end_Date := null;
578 hr_utility.set_location(' Leaving:'||l_proc, 80);
579 when others then
580 --
581 -- A validation or unexpected error has occured
582 --
583 rollback to update_ie_prsi_details;
584 p_object_version_number := l_object_version_number;
585 p_effective_start_date := null;
586 p_effective_end_date := null;
587 hr_utility.set_location(' Leaving:'||l_proc, 90);
588 raise;
589 end update_ie_prsi_details;
590
591 --
592 -- ----------------------------------------------------------------------------
593 -- |------------------------< delete_ie_prsi_details >------------------------|
594 -- ----------------------------------------------------------------------------
595 --
596 procedure delete_ie_prsi_details
597 (p_validate in boolean default false
598 ,p_effective_date in date
599 ,p_datetrack_delete_mode in varchar2
600 ,p_prsi_details_id in number
601 ,p_object_version_number in out nocopy number
602 ,p_effective_start_date out nocopy date
603 ,p_effective_end_date out nocopy date
604 ) IS
605 --
606 -- Declare cursors and local variables
607 --
608
609 l_proc varchar2(72) := g_package||'delete_ie_prsi_details';
610 l_object_version_number number := p_object_version_number;
611 l_effective_start_date date;
612 l_effective_end_Date date;
613 l_p45_effective_date date;
614 l_assignment_id number;
615 l_business_group_id number;
616 --
617 CURSOR asg_csr IS
618 SELECT assignment_id
619 FROM pay_ie_prsi_details_f
620 WHERE prsi_details_id = p_prsi_details_id
621 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
622 --
623 CURSOR business_group_csr IS
624 SELECT business_group_id
625 FROM per_all_assignments_f
626 WHERE assignment_id = l_assignment_id
627 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
628 --
629 --
630 begin
631 --
632 -- Issue a savepoint
633 --
634 savepoint delete_ie_prsi_details;
635 --
636 -- Get assignment_id from the cursor
637 OPEN asg_csr;
638 FETCH asg_csr INTO l_assignment_id;
639 CLOSE asg_csr;
640 --
641 -- Get Business_group_id
642 OPEN business_group_csr;
643 FETCH business_group_csr INTO l_business_group_id;
644 CLOSE business_group_csr;
645 --
646 --
647 -- Call Before Process User Hook
648 --
649 begin
650 pay_ie_prsi_bk3.delete_ie_prsi_details_b
651 (p_effective_date => p_effective_date
652 ,p_datetrack_delete_mode => p_datetrack_delete_mode
653 ,p_business_group_id => l_business_group_id
654 ,p_prsi_details_id => p_prsi_details_id
655 ,p_object_version_number => l_object_version_number
656 );
657 exception
658 when hr_api.cannot_find_prog_unit then
659 hr_api.cannot_find_prog_unit_error
660 (p_module_name => 'delete_ie_prsi_details'
661 ,p_hook_type => 'BP'
662 );
663 end;
664 --
665 -- Process Logic
666 --
667 -- Call row handler procedure to update prsi details
668 --
669 pay_sid_del.del
670 ( p_effective_date => p_effective_date
671 ,p_datetrack_mode => p_datetrack_delete_mode
672 ,p_prsi_details_id => p_prsi_details_id
673 ,p_object_version_number => l_object_version_number
674 ,p_effective_start_date => l_effective_start_date
675 ,p_effective_end_date => l_effective_end_date
676 );
677 --
678 --
679 -- Call After Process User Hook
680 --
681 begin
682 pay_ie_prsi_bk3.delete_ie_prsi_details_a
683 (p_effective_date => p_effective_date
684 ,p_business_group_id => l_business_group_id
685 ,p_datetrack_delete_mode => p_datetrack_delete_mode
686 ,p_prsi_details_id => p_prsi_details_id
690 );
687 ,p_object_version_number => l_object_version_number
688 ,p_effective_start_date => l_effective_start_date
689 ,p_effective_end_date => l_effective_end_date
691 exception
692 when hr_api.cannot_find_prog_unit then
693 hr_api.cannot_find_prog_unit_error
694 (p_module_name => 'delete_ie_prsi_details'
695 ,p_hook_type => 'AP'
696 );
697 end;
698 --
699 -- When in validation only mode raise the Validate_Enabled exception
700 --
701 if p_validate then
702 raise hr_api.validate_enabled;
703 end if;
704 --
705 -- Set all output arguments
706 --
707 p_object_version_number := l_object_version_number;
708 p_effective_start_date := l_effective_start_date;
709 p_effective_end_date := l_effective_end_Date;
710 --
711 exception
712 when hr_api.validate_enabled then
713 --
714 -- As the Validate_Enabled exception has been raised
715 -- we must rollback to the savepoint
716 --
717 rollback to delete_ie_prsi_details;
718 --
719 -- Only set output warning arguments
720 -- (Any key or derived arguments must be set to null
721 -- when validation only mode is being used.)
722 --
723 -- IN OUT parameter should be reset to its IN value therefore
724 -- no need to set p_object_version_number
725 p_effective_start_date := null;
726 p_effective_end_Date := null;
727 --
728 hr_utility.set_location(' Leaving:'||l_proc, 80);
729 when others then
730 --
731 -- A validation or unexpected error has occured
732 --
733 rollback to update_ie_prsi_details;
734 p_object_version_number := l_object_version_number;
735 p_effective_start_date := null;
736 p_effective_end_date := null;
737
738 hr_utility.set_location(' Leaving:'||l_proc, 90);
739 raise;
740 end delete_ie_prsi_details;
741
742 end pay_ie_prsi_api;