[Home] [Help]
PACKAGE BODY: APPS.PAY_TIME_DEFINITION_API
Source
1 Package Body PAY_TIME_DEFINITION_API as
2 /* $Header: pytdfapi.pkb 120.3 2005/09/21 03:56:23 adkumar noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PAY_TIME_DEFINITION_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< generate_time_periods >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure generate_time_periods
13 (p_time_definition_id in number
14 ) is
15 --
16 l_earliest_start_date date;
17 l_latest_end_date date;
18 l_no_of_existing_periods number;
19
20 l_definition_type varchar2(30);
21 l_start_date date;
22 l_period_type varchar2 (30);
23 l_legislation_code varchar2 (30);
24 l_business_group_id number (15);
25 l_number_of_years number (9);
26 l_period_time_definition_id number (9);
27
28 l_time_definition_id_to_pass number (9);
29
30 l_first_period_start_date date;
31 l_first_period_end_date date;
32 l_gen_first_period_start_date date;
33 l_gen_first_period_end_date date;
34 l_period_start_date date;
35 l_period_end_date date;
36
37 l_next_period_start_date date;
38
39 l_multiple number;
40 l_base_period_type varchar2(1);
41 l_period_number number;
42 l_period_name per_time_periods.period_name%type ;
43 l_end_years_marker date;
44 l_display_period_type per_time_period_types.display_period_type%type ;
45 l_date date;
46
47 l_next_leg_start_date date;
48 l_leg_start_date date;
49
50 l_day_adj_multiple number;
51 l_period_unit_multiple number;
52
53 l_dpit_period_unit varchar2(30);
54 l_dpit_period_type varchar2(30);
55 l_dpit_day_adjustment varchar2(30);
56
57 l_proc varchar2(72) := g_package||'generate_time_periods';
58
59 WEEKLY CONSTANT varchar2(1) := 'W';
60 MONTHLY CONSTANT varchar2(1) := 'M';
61 SEMIMONTHLY CONSTANT varchar2(1) := 'S';
62
63 --
64 cursor csr_time_definition_details is
65 select nvl(definition_type,'P'),
66 start_date,
67 period_type,
68 business_group_id,
69 legislation_code,
70 number_of_years,
71 period_time_definition_id
72 from pay_time_definitions
73 where time_definition_id = p_time_definition_id;
74
75 cursor csr_existing_period_details is
76 select min(start_date),
77 max(end_date),
78 count(time_period_id)
79 from per_time_periods ptp
80 where ptp.time_definition_id = p_time_definition_id;
81
82 cursor csr_first_period_end_date is
83 select end_date
84 from per_time_periods ptp
85 where ptp.time_definition_id = p_time_definition_id
86 and start_date = l_first_period_start_date;
87
88 --
89 function add_multiples
90 (p_date in date,
91 p_base_period_type in varchar2,
92 p_multiple in number,
93 p_first_period_end_date in date
94 ) return date is
95 --
96 l_ret_date date;
97 --
98 begin
99 --
100 if p_base_period_type = WEEKLY then
101 return (p_date + (7 * p_multiple ));
102 elsif p_base_period_type = MONTHLY then
103 return (add_months(p_date , p_multiple));
104 else
105 if p_multiple > 0 then
106 l_ret_date := hr_payrolls.next_semi_month(p_date, p_first_period_end_date);
107 return l_ret_date;
108 else
109 l_ret_date := hr_payrolls.prev_semi_month(p_date, p_first_period_end_date);
110 return l_ret_date;
111 end if;
112 end if;
113 end add_multiples;
114 --
115 --
116 Procedure get_period_number(
117 p_time_definition_id in number,
118 p_first_period_end_date in date,
119 p_legislation_code in varchar2,
120 p_business_group_id in number,
121 p_leg_start_date out nocopy date,
122 p_period_number out nocopy number
123 ) is
124
125 l_leg_start_date date;
126 l_next_start_date date;
127 no_periods number;
128 begin
129 --
130 begin
131 select to_date(plr.rule_mode || '/' ||
132 to_char(p_first_period_end_date, 'YYYY'), 'DD/MM/YYYY')
133 into l_leg_start_date
134 from pay_legislation_rules plr
135 where plr.rule_type = 'L'
136 and plr.legislation_code = nvl(p_legislation_code,
137 hr_api.return_legislation_code(p_business_group_id) );
138 exception
139 when NO_DATA_FOUND then
140 l_leg_start_date := to_date('01/01/' ||
141 to_char(p_first_period_end_date, 'YYYY'), 'DD/MM/YYYY');
142 end;
143 --
144 if l_leg_start_date > p_first_period_end_date then
145 l_leg_start_date := add_months(l_leg_start_date, -12);
146 end if;
147 --
148 no_periods := 0;
149
150 l_next_start_date := l_leg_start_date;
151 while p_first_period_end_date >= l_next_start_date loop
152 l_next_start_date := pay_core_dates.get_time_definition_date(
153 p_time_def_id =>p_time_definition_id,
154 p_effective_date =>l_next_start_date,
155 p_bus_grp =>p_business_group_id);
156 no_periods := no_periods + 1;
157 end loop;
158 --
159 p_period_number := no_periods;
160 p_leg_start_date := l_leg_start_date;
161 end ;
162 --
163 --
164 begin
165 --
166 -- Get the time definition details.
167
168 open csr_time_definition_details;
169
170 fetch csr_time_definition_details
171 into l_definition_type,
172 l_start_date,
173 l_period_type,
174 l_business_group_id,
175 l_legislation_code,
176 l_number_of_years,
177 l_period_time_definition_id;
178
179 if csr_time_definition_details%notfound then
180
181 hr_utility.set_location(l_proc, 20);
182 close csr_time_definition_details;
183 fnd_message.set_name('PAY','PAY_34056_FLSA_INV_TIME_DEF_ID');
184 fnd_message.raise_error;
185
186 end if;
187
188 close csr_time_definition_details;
189 hr_utility.set_location(l_proc, 30);
190
191 if l_definition_type in ('P', 'E', 'C') then
192
193 hr_utility.set_location(l_proc, 40);
194 return;
195
196 end if;
197
198 --
199 if l_period_type is not null then
200 l_time_definition_id_to_pass := p_time_definition_id;
201 hr_payrolls.get_period_details
202 (p_proc_period_type => l_period_type,
203 p_base_period_type => l_base_period_type,
204 p_multiple => l_multiple);
205
206 elsif l_period_time_definition_id is not null then
207 l_time_definition_id_to_pass := l_period_time_definition_id;
208 end if;
209
210 hr_utility.set_location(l_proc, 50);
211
212 -- Check if there are already periods existing for this time definition.
213 -- If the periods are already existing the user is trying to increase
214 -- number of years of the time definition.
215
216 open csr_existing_period_details;
217 fetch csr_existing_period_details into l_earliest_start_date,
218 l_latest_end_date, l_no_of_existing_periods;
219 close csr_existing_period_details;
220
221 if l_no_of_existing_periods = 0 then
222 -- Time Periods do not exist for the time definition.
223 hr_utility.set_location(l_proc, 60);
224
225 l_first_period_start_date := l_start_date;
226 if l_period_time_definition_id is not null then
227 -- if Time Definition is static and point in time definition id is not null
228 l_next_period_start_date := pay_core_dates.get_time_definition_date(
229 p_time_def_id =>l_time_definition_id_to_pass,
230 p_effective_date =>l_start_date,
231 p_bus_grp =>l_business_group_id);
232
233 l_first_period_end_date := l_next_period_start_date -1;
234
235 hr_utility.set_location(l_proc, 70);
236
237 elsif l_period_type is not null then
238 -- if Time Definition is static and period type is not null
239 l_first_period_end_date := add_multiples
240 ( p_date => l_start_date - 1 ,
241 p_base_period_type => l_base_period_type,
242 p_multiple => l_multiple,
243 p_first_period_end_date => l_start_date - 1
244 ) ;
245 hr_utility.set_location(l_proc, 80);
246 end if;
247 l_gen_first_period_start_date := l_first_period_start_date;
248 l_gen_first_period_end_date := l_first_period_end_date;
249
250 else
251
252 -- Time Periods have been generated before.
253
254 hr_utility.set_location(l_proc, 90);
255
256 l_first_period_start_date := l_earliest_start_date;
257
258 open csr_first_period_end_date;
259 fetch csr_first_period_end_date into l_first_period_end_date;
260 close csr_first_period_end_date;
261
262 l_gen_first_period_start_date := l_latest_end_date + 1;
263
264 if l_period_time_definition_id is not null then
265 l_next_period_start_date := pay_core_dates.get_time_definition_date(
266 p_time_def_id =>l_time_definition_id_to_pass,
267 p_effective_date =>l_gen_first_period_start_date,
268 p_bus_grp =>l_business_group_id);
269
270 l_gen_first_period_end_date := l_next_period_start_date -1;
271 hr_utility.set_location(l_proc, 100);
272 elsif l_period_type is not null then
273
274 l_gen_first_period_end_date := add_multiples
275 ( p_date => l_gen_first_period_start_date - 1 ,
276 p_base_period_type => l_base_period_type,
277 p_multiple => l_multiple,
278 p_first_period_end_date => l_first_period_end_date
279 ) ;
280 hr_utility.set_location(l_proc, 110);
281 end if;
282
283 end if;
284 --
285 hr_utility.set_location(l_proc, 160);
286
287 if l_gen_first_period_end_date < l_gen_first_period_start_date then
288 fnd_message.set_name('PAY','PAY_33414_FLSA_CROSS_VAL3');
289 fnd_message.raise_error;
290 End if;
291
292
293 -- Generate the periods.
294
295 -- Derive the period number of the first period.
296
297 get_period_number(
298 p_time_definition_id => l_time_definition_id_to_pass,
299 p_first_period_end_date => l_gen_first_period_end_date,
300 p_legislation_code => l_legislation_code,
301 p_business_group_id => l_business_group_id,
302 p_leg_start_date => l_leg_start_date,
303 p_period_number => l_period_number);
304
305
306 -- Insert the time periods for the number of years required
307
308 hr_utility.set_location(l_proc, 120);
309
310 l_end_years_marker := add_months(l_first_period_start_date,
311 (12 * l_number_of_years) );
312
313 l_next_leg_start_date := add_months(l_leg_start_date, 12);
314
315 l_period_start_date := l_gen_first_period_start_date;
316 l_period_end_date := l_gen_first_period_end_date;
317
318 while (l_period_start_date < l_end_years_marker) loop
319
320 hr_utility.set_location(l_proc, 130);
321
322 begin
323
324 select NVL(tpt.display_period_type, l_period_type)
325 into l_display_period_type
326 from per_time_period_types_vl tpt
327 where tpt.period_type = l_period_type;
328
329 l_period_name := to_char(l_period_number) || ' '
330 || to_char(l_period_end_date, 'YYYY') || ' '
331 || l_display_period_type ;
332
333 hr_utility.set_location(l_proc, 140);
334
335 exception
336 when NO_DATA_FOUND then
337 l_period_name := to_char(l_period_number) || ' '
338 || to_char(l_period_end_date, 'YYYY') || ' '
339 ;
340 l_period_type := 'Dynamic Period';
341 hr_utility.set_location(l_proc, 232);
342 end;
343
344 if l_period_time_definition_id is not null then
345 l_next_period_start_date := pay_core_dates.get_time_definition_date(
346 p_time_def_id =>l_time_definition_id_to_pass,
347 p_effective_date =>l_period_start_date,
348 p_bus_grp =>l_business_group_id);
349
350 l_period_end_date := l_next_period_start_date -1;
351 hr_utility.set_location(l_proc, 150);
352
353 else
354 l_next_period_start_date := add_multiples
355 ( p_date => l_period_start_date,
356 p_base_period_type => l_base_period_type,
357 p_multiple => l_multiple,
358 p_first_period_end_date => l_first_period_end_date
359 );
360
361 l_period_end_date := l_next_period_start_date -1;
362 hr_utility.set_location(l_proc, 160);
363
364 end if;
365
366 if l_period_end_date < l_period_start_date then
367 fnd_message.set_name('PAY','PAY_33414_FLSA_CROSS_VAL3');
368 fnd_message.raise_error;
369 End if;
370
371 insert into per_time_periods
372 (time_period_id,
373 start_date,
374 end_date,
375 period_type,
376 period_num,
377 period_name,
378 time_definition_id
379 )
380 select
381 per_time_periods_s.nextval,
382 l_period_start_date,
383 l_period_end_date,
384 l_period_type,
385 l_period_number,
386 l_period_name,
387 p_time_definition_id
388 from sys.dual;
389
390 l_period_start_date := l_period_end_date + 1 ;
391
392 if l_period_end_date >= l_next_leg_start_date then
393
394 hr_utility.set_location(l_proc, 170);
395
396 l_period_number := 1;
397 l_next_leg_start_date := add_months(l_next_leg_start_date, 12);
398
399 else
400
401 hr_utility.set_location(l_proc, 180);
402
403 l_period_number := l_period_number + 1;
404
405 end if;
406
407 hr_utility.set_location(l_proc, 260);
408
409 end loop;
410
411 --
412 end generate_time_periods;
413 --
414 -- ----------------------------------------------------------------------------
415 -- |------------------------< CREATE_TIME_DEFINITION >------------------------|
416 -- ----------------------------------------------------------------------------
417 --
418 procedure create_time_definition
419 (p_validate in boolean default false
420 ,p_effective_date in date
421 ,p_short_name in varchar2
422 ,p_definition_name in varchar2
423 ,p_period_type in varchar2 default null
424 ,p_period_unit in varchar2 default null
425 ,p_day_adjustment in varchar2 default null
426 ,p_dynamic_code in varchar2 default null
427 ,p_business_group_id in number default null
428 ,p_legislation_code in varchar2 default null
429 ,p_definition_type in varchar2 default 'P'
430 ,p_number_of_years in number default null
431 ,p_start_date in date default null
432 ,p_period_time_definition_id in number default null
433 ,p_creator_id in number default null
434 ,p_creator_type in varchar2 default null
435 ,p_time_definition_id out nocopy number
436 ,p_object_version_number out nocopy number
437 ) is
438 --
439 -- Declare cursors and local variables
440 --
441 l_effective_date date;
442 l_start_date date;
443 l_proc varchar2(72) := g_package||'create_time_definition';
444 l_object_version_number number;
445 l_time_definition_id number;
446 begin
447 hr_utility.set_location('Entering:'|| l_proc, 10);
448 --
449 -- Issue a savepoint
450 --
451 savepoint create_time_definition;
452 --
453 -- Remember IN OUT parameter IN values
454 --
455 -- Truncate the time portion from all IN date parameters
456 --
457 l_effective_date := trunc(p_effective_date);
458 l_start_date := trunc(p_start_date);
459 --
460 -- Call Before Process User Hook
461 --
462 begin
463 pay_time_definition_bk1.create_time_definition_b
464 (p_effective_date => l_effective_date
465 ,p_short_name => p_short_name
466 ,p_definition_name => p_definition_name
467 ,p_period_type => p_period_type
468 ,p_period_unit => p_period_unit
469 ,p_day_adjustment => p_day_adjustment
470 ,p_dynamic_code => p_dynamic_code
471 ,p_business_group_id => p_business_group_id
472 ,p_legislation_code => p_legislation_code
473 ,p_definition_type => p_definition_type
474 ,p_number_of_years => p_number_of_years
475 ,p_start_date => l_start_date
476 ,p_period_time_definition_id => p_period_time_definition_id
477 ,p_creator_id => p_creator_id
478 ,p_creator_type => p_creator_type
479 );
480 exception
481 when hr_api.cannot_find_prog_unit then
482 hr_api.cannot_find_prog_unit_error
483 (p_module_name => 'create_time_definition'
484 ,p_hook_type => 'BP'
485 );
486 end;
487
488 --
489 -- Process Logic
490 --
491 pay_tdf_ins.ins
492 (p_effective_date => l_effective_date
493 ,p_short_name => p_short_name
494 ,p_definition_name => p_definition_name
495 ,p_period_type => p_period_type
496 ,p_period_unit => p_period_unit
497 ,p_day_adjustment => p_day_adjustment
498 ,p_dynamic_code => p_dynamic_code
499 ,p_business_group_id => p_business_group_id
500 ,p_legislation_code => p_legislation_code
501 ,p_definition_type => p_definition_type
502 ,p_number_of_years => p_number_of_years
503 ,p_start_date => l_start_date
504 ,p_period_time_definition_id => p_period_time_definition_id
505 ,p_creator_id => p_creator_id
506 ,p_creator_type => p_creator_type
507 ,p_time_definition_id => l_time_definition_id
508 ,p_object_version_number => l_object_version_number
509 );
510
511 generate_time_periods
512 (p_time_definition_id => l_time_definition_id);
513
514 --
515 -- Call After Process User Hook
516 --
517 begin
518 pay_time_definition_bk1.create_time_definition_a
519 (p_effective_date => l_effective_date
520 ,p_short_name => p_short_name
521 ,p_definition_name => p_definition_name
522 ,p_period_type => p_period_type
523 ,p_period_unit => p_period_unit
524 ,p_day_adjustment => p_day_adjustment
525 ,p_dynamic_code => p_dynamic_code
526 ,p_business_group_id => p_business_group_id
527 ,p_legislation_code => p_legislation_code
528 ,p_definition_type => p_definition_type
529 ,p_number_of_years => p_number_of_years
530 ,p_start_date => l_start_date
531 ,p_period_time_definition_id => p_period_time_definition_id
532 ,p_creator_id => p_creator_id
533 ,p_creator_type => p_creator_type
534 ,p_time_definition_id => l_time_definition_id
535 ,p_object_version_number => l_object_version_number
536 );
537 exception
538 when hr_api.cannot_find_prog_unit then
539 hr_api.cannot_find_prog_unit_error
540 (p_module_name => 'create_time_definition'
541 ,p_hook_type => 'AP'
542 );
543 end;
544 --
545 -- When in validation only mode raise the Validate_Enabled exception
546 --
547 if p_validate then
548 raise hr_api.validate_enabled;
549 end if;
550 --
551 -- Set all IN OUT and OUT parameters with out values
552 --
553 p_time_definition_id := l_time_definition_id;
554 p_object_version_number := l_object_version_number;
555 --
556 hr_utility.set_location(' Leaving:'||l_proc, 70);
557
558 exception
559 when hr_api.validate_enabled then
560 --
561 -- As the Validate_Enabled exception has been raised
562 -- we must rollback to the savepoint
563 --
564 rollback to create_time_definition;
565 --
566 -- Reset IN OUT parameters and set OUT parameters
567 --
568 p_time_definition_id := null;
569 p_object_version_number := null;
570
571 hr_utility.set_location(' Leaving:'||l_proc, 80);
572 when others then
573 --
574 -- A validation or unexpected error has occured
575 --
576 rollback to create_time_definition;
577 --
578 -- Reset IN OUT parameters and set all
579 -- OUT parameters, including warnings, to null
580 --
581 p_time_definition_id := null;
582 p_object_version_number := null;
583 hr_utility.set_location(' Leaving:'||l_proc, 90);
584 raise;
585 end create_time_definition;
586 --
587 -- ----------------------------------------------------------------------------
588 -- |------------------------< UPDATE_TIME_DEFINITION >------------------------|
589 -- ----------------------------------------------------------------------------
590 --
591 procedure update_time_definition
592 (p_validate in boolean default false
593 ,p_effective_date in date
594 ,p_time_definition_id in number
595 ,p_definition_name in varchar2 default hr_api.g_varchar2
596 ,p_period_type in varchar2 default hr_api.g_varchar2
597 ,p_period_unit in varchar2 default hr_api.g_varchar2
598 ,p_day_adjustment in varchar2 default hr_api.g_varchar2
599 ,p_dynamic_code in varchar2 default hr_api.g_varchar2
600 ,p_number_of_years in number default hr_api.g_number
601 ,p_start_date in date default hr_api.g_date
602 ,p_period_time_definition_id in number default hr_api.g_number
603 ,p_creator_id in number default hr_api.g_number
604 ,p_creator_type in varchar2 default hr_api.g_varchar2
605 ,p_object_version_number in out nocopy number
606 ) is
607 --
608 -- Declare cursors and local variables
609 --
610 l_effective_date date;
611 l_proc varchar2(72) := g_package||'update_time_definition';
612 l_object_version_number number;
613 l_regenerate_periods boolean;
614 l_delete_periods boolean;
615 l_in_out_parameter number;
616 l_definition_type varchar2(30);
617 --
618 cursor csr_definition_type is
619 select definition_type
620 from pay_time_definitions
621 where time_definition_id = p_time_definition_id;
622 --
623 begin
624 hr_utility.set_location('Entering:'|| l_proc, 10);
625 --
626 -- Issue a savepoint
627 --
628 savepoint update_time_definition;
629 --
630 -- Remember IN OUT parameter IN values
631 --
632 l_in_out_parameter := p_object_version_number;
633 --
634 l_object_version_number := p_object_version_number;
635 --
636 -- Truncate the time portion from all IN date parameters
637 --
638 l_effective_date := trunc(p_effective_date);
639
640 --
641 -- Call Before Process User Hook
642 --
643 begin
644 pay_time_definition_bk2.update_time_definition_b
645 (p_effective_date => l_effective_date
646 ,p_time_definition_id => p_time_definition_id
647 ,p_definition_name => p_definition_name
648 ,p_period_type => p_period_type
649 ,p_period_unit => p_period_unit
650 ,p_day_adjustment => p_day_adjustment
651 ,p_dynamic_code => p_dynamic_code
652 ,p_number_of_years => p_number_of_years
653 ,p_start_date => p_start_date
654 ,p_period_time_definition_id => p_period_time_definition_id
655 ,p_creator_id => p_creator_id
656 ,p_creator_type => p_creator_type
657 ,p_object_version_number => l_object_version_number
658 );
659 exception
660 when hr_api.cannot_find_prog_unit then
661 hr_api.cannot_find_prog_unit_error
662 (p_module_name => 'update_time_definition'
663 ,p_hook_type => 'BP'
664 );
665 end;
666
667 --
668 -- Process Logic
669 --
670
671 pay_tdf_upd.upd
672 (p_effective_date => l_effective_date
673 ,p_time_definition_id => p_time_definition_id
674 ,p_object_version_number => l_object_version_number
675 ,p_regenerate_periods => l_regenerate_periods
676 ,p_delete_periods => l_delete_periods
677 ,p_definition_name => p_definition_name
678 ,p_period_type => p_period_type
679 ,p_period_unit => p_period_unit
680 ,p_day_adjustment => p_day_adjustment
681 ,p_dynamic_code => p_dynamic_code
682 ,p_number_of_years => p_number_of_years
683 ,p_start_date => p_start_date
684 ,p_period_time_definition_id => p_period_time_definition_id
685 ,p_creator_id => p_creator_id
686 ,p_creator_type => p_creator_type
687 );
688
689 if l_delete_periods then
690
691 open csr_definition_type;
692 fetch csr_definition_type into l_definition_type;
693 close csr_definition_type;
694
695 if l_definition_type not in ('P', 'E', 'C') then
696
697 delete from per_time_periods
698 where time_definition_id = p_time_definition_id;
699
700 generate_time_periods
701 (p_time_definition_id => p_time_definition_id);
702
703 end if;
704
705 elsif l_regenerate_periods then
706
707 generate_time_periods
708 (p_time_definition_id => p_time_definition_id);
709
710 end if;
711
712 --
713 -- Call After Process User Hook
714 --
715 begin
716 pay_time_definition_bk2.update_time_definition_a
717 (p_effective_date => l_effective_date
718 ,p_time_definition_id => p_time_definition_id
719 ,p_definition_name => p_definition_name
720 ,p_period_type => p_period_type
721 ,p_period_unit => p_period_unit
722 ,p_day_adjustment => p_day_adjustment
723 ,p_dynamic_code => p_dynamic_code
724 ,p_number_of_years => p_number_of_years
725 ,p_start_date => p_start_date
726 ,p_period_time_definition_id => p_period_time_definition_id
727 ,p_creator_id => p_creator_id
728 ,p_creator_type => p_creator_type
729 ,p_object_version_number => l_object_version_number
730 );
731 exception
732 when hr_api.cannot_find_prog_unit then
733 hr_api.cannot_find_prog_unit_error
734 (p_module_name => 'update_time_definition'
735 ,p_hook_type => 'AP'
736 );
737 end;
738 --
739 -- When in validation only mode raise the Validate_Enabled exception
740 --
741 if p_validate then
742 raise hr_api.validate_enabled;
743 end if;
744 --
745 -- Set all IN OUT and OUT parameters with out values
746 --
747 p_object_version_number := l_object_version_number;
748 --
749 hr_utility.set_location(' Leaving:'||l_proc, 70);
750
751 exception
752 when hr_api.validate_enabled then
753 --
754 -- As the Validate_Enabled exception has been raised
755 -- we must rollback to the savepoint
756 --
757 rollback to update_time_definition;
758 --
759 -- Reset IN OUT parameters and set OUT parameters
760 -- (Any key or derived arguments must be set to null
761 -- when validation only mode is being used.)
762 --
763 p_object_version_number := l_in_out_parameter;
764
765 hr_utility.set_location(' Leaving:'||l_proc, 80);
766 when others then
767 --
768 -- A validation or unexpected error has occured
769 --
770 rollback to update_time_definition;
771 --
772 -- Reset IN OUT parameters and set all
773 -- OUT parameters, including warnings, to null
774 --
775 p_object_version_number := l_in_out_parameter;
776
777 hr_utility.set_location(' Leaving:'||l_proc, 90);
778 raise;
779 end update_time_definition;
780 --
781 -- ----------------------------------------------------------------------------
782 -- |--------------------------< delete_time_definition >----------------------|
783 -- ----------------------------------------------------------------------------
784 --
785 procedure delete_time_definition
786 (p_validate in boolean default false
787 ,p_effective_date in date
788 ,p_time_definition_id in number
789 ,p_object_version_number in number
790 ) is
791 --
792 -- Declare cursors and local variables
793 --
794 l_effective_date date;
795 l_proc varchar2(72) := g_package||'delete_time_definition';
796 begin
797 hr_utility.set_location('Entering:'|| l_proc, 10);
798 --
799 -- Issue a savepoint
800 --
801 savepoint delete_time_definition;
802 --
803 --
804 -- Truncate the time portion from all IN date parameters
805 --
806 l_effective_date := trunc(p_effective_date);
807
808 --
809 -- Call Before Process User Hook
810 --
811 begin
812 pay_time_definition_bk3.delete_time_definition_b
813 (p_effective_date => l_effective_date
814 ,p_time_definition_id => p_time_definition_id
815 ,p_object_version_number => p_object_version_number
816 );
817 exception
818 when hr_api.cannot_find_prog_unit then
819 hr_api.cannot_find_prog_unit_error
820 (p_module_name => 'delete_time_definition'
821 ,p_hook_type => 'BP'
822 );
823 end;
824
825 --
826 -- Process Logic
827 --
828
829 delete from per_time_periods
830 where time_definition_id = p_time_definition_id;
831
832 delete from pay_time_def_usages
833 where time_definition_id = p_time_definition_id;
834
835 pay_tdf_del.del
836 (p_time_definition_id => p_time_definition_id
837 ,p_object_version_number => p_object_version_number
838 );
839
840 --
841 -- Call After Process User Hook
842 --
843 begin
844 pay_time_definition_bk3.delete_time_definition_a
845 (p_effective_date => l_effective_date
846 ,p_time_definition_id => p_time_definition_id
847 ,p_object_version_number => p_object_version_number
848 );
849 exception
850 when hr_api.cannot_find_prog_unit then
851 hr_api.cannot_find_prog_unit_error
852 (p_module_name => 'delete_time_definition'
853 ,p_hook_type => 'AP'
854 );
855 end;
856 --
857 -- When in validation only mode raise the Validate_Enabled exception
858 --
859 if p_validate then
860 raise hr_api.validate_enabled;
861 end if;
862 --
863 hr_utility.set_location(' Leaving:'||l_proc, 70);
864 exception
865 when hr_api.validate_enabled then
866 --
867 -- As the Validate_Enabled exception has been raised
868 -- we must rollback to the savepoint
869 --
870 rollback to delete_time_definition;
871 --
872 hr_utility.set_location(' Leaving:'||l_proc, 80);
873 when others then
874 --
875 -- A validation or unexpected error has occured
876 --
877 rollback to delete_time_definition;
878 --
879 hr_utility.set_location(' Leaving:'||l_proc, 90);
880 raise;
881 end delete_time_definition;
882 --
883 end PAY_TIME_DEFINITION_API;