[Home] [Help]
PACKAGE BODY: APPS.HR_CAGR_ENT_LINES_API
Source
1 PACKAGE BODY hr_cagr_ent_lines_api AS
2 /* $Header: pepclapi.pkb 120.1 2006/10/18 09:22:14 grreddy noship $ */
3 --
4 -- Package Variables
5 --
6 g_package VARCHAR2(33) := ' hr_cagr_ent_lines_api.';
7 --
8 PROCEDURE delete_from_engine_tables
9 (p_cagr_entitlement_line_id IN NUMBER
10 ,p_effective_date IN DATE) IS
11 --
12 CURSOR csr_entitlement_results IS
13 SELECT cagr_request_id,
14 cagr_entitlement_result_id,
15 assignment_id
16 FROM per_cagr_entitlement_results cer
17 WHERE cer.cagr_entitlement_line_id = p_cagr_entitlement_line_id
18 FOR UPDATE;
19 --
20 CURSOR csr_chk_for_other_lines
21 (p_cagr_entitlement_result_id IN NUMBER
22 ,p_cagr_request_id IN NUMBER
23 ,p_assignment_id IN NUMBER) IS
24 SELECT cagr_entitlement_result_id
25 FROM per_cagr_entitlement_results cer
26 WHERE cer.cagr_entitlement_line_id <> p_cagr_entitlement_line_id
27 AND cer.cagr_request_id = p_cagr_request_id
28 AND cer.cagr_entitlement_result_id <> p_cagr_entitlement_result_id
29 AND cer.assignment_id = p_assignment_id;
30 --
31 -- Declare Local Variables
32 --
33 l_proc VARCHAR2(72) := g_package||'delete_from_engine_tables';
34 l_result_id per_cagr_entitlement_results.cagr_entitlement_result_id %TYPE;
35 --
36 BEGIN
37 --
38 hr_utility.set_location('Entering:'|| l_proc, 10);
39 --
40 FOR c_results IN csr_entitlement_results LOOP
41 --
42 hr_utility.set_location(l_proc||'/'||c_results.cagr_entitlement_result_id
43 ||'/'||c_results.assignment_id, 20);
44 --
45 OPEN csr_chk_for_other_lines
46 (p_cagr_request_id => c_results.cagr_request_id
47 ,p_assignment_id => c_results.assignment_id
48 ,p_cagr_entitlement_result_id => c_results.cagr_entitlement_result_id);
49 --
50 FETCH csr_chk_for_other_lines INTO l_result_id;
51 --
52 -- IF no other results where found that belong to the same
53 -- assignment and request id as the line being deleted then
54 -- delete from retained rights, log and requests
55 --
56 IF csr_chk_for_other_lines%NOTFOUND THEN
57 --
58 hr_utility.set_location(l_proc,30);
59 --
60 DELETE FROM per_cagr_retained_rights
61 WHERE cagr_entitlement_line_id = p_cagr_entitlement_line_id;
62 --
63 hr_utility.set_location(l_proc,40);
64 --
65 DELETE FROM per_cagr_entitlement_results
66 WHERE cagr_entitlement_result_id = c_results.cagr_entitlement_result_id;
67 --
68 hr_utility.set_location(l_proc,50);
69 --
70 DELETE FROM per_cagr_log
71 WHERE cagr_request_id = c_results.cagr_request_id;
72 --
73 hr_utility.set_location(l_proc,60);
74 --
75 DELETE FROM per_cagr_requests
76 WHERE cagr_request_id = c_results.cagr_request_id;
77 --
78 hr_utility.set_location(l_proc,70);
79 --
80 CLOSE csr_chk_for_other_lines;
81 --
82 ELSE
83 --
84 hr_utility.set_location(l_proc,80);
85 --
86 DELETE FROM per_cagr_retained_rights
87 WHERE cagr_entitlement_result_id = c_results.cagr_entitlement_result_id;
88 --
89 hr_utility.set_location(l_proc,90);
90 --
91 DELETE FROM per_cagr_entitlement_results
92 WHERE cagr_entitlement_result_id = c_results.cagr_entitlement_result_id;
93 --
94 hr_utility.set_location(l_proc,100);
95 --
96 CLOSE csr_chk_for_other_lines;
97 --
98 END IF;
99 --
100 END LOOP;
101 --
102 hr_utility.set_location('Leaving:'|| l_proc, 999);
103 --
104 END delete_from_engine_tables;
105 --
106 -- ----------------------------------------------------------------------------
107 -- |------------------------< create_entitlement_line >----------------------|
108 -- ----------------------------------------------------------------------------
109 --
110 PROCEDURE create_entitlement_line
111 (p_validate IN BOOLEAN DEFAULT FALSE
112 ,p_cagr_entitlement_line_id OUT NOCOPY NUMBER
113 ,p_mandatory IN VARCHAR2
114 ,p_value IN VARCHAR2 DEFAULT NULL
115 ,p_range_from IN VARCHAR2 DEFAULT NULL
116 ,p_range_to IN VARCHAR2 DEFAULT NULL
117 ,p_effective_start_date OUT NOCOPY DATE
118 ,p_effective_end_date OUT NOCOPY DATE
119 ,p_grade_spine_id IN NUMBER DEFAULT NULL
120 ,p_parent_spine_id IN NUMBER DEFAULT NULL
121 ,p_cagr_entitlement_id IN NUMBER
122 ,p_status IN VARCHAR2
123 ,p_eligy_prfl_id IN NUMBER
124 ,p_step_id IN NUMBER DEFAULT NULL
125 ,p_from_step_id IN NUMBER DEFAULT NULL
126 ,p_to_step_id IN NUMBER DEFAULT NULL
127 ,p_object_version_number OUT NOCOPY NUMBER
128 ,p_oipl_id OUT NOCOPY NUMBER
129 ,p_effective_date IN DATE
130 ) IS
131 --
132 -- Declare cursors and local variables
133 --
134 CURSOR csr_cagr_information IS
135 SELECT pl_id,
136 pca.business_group_id,
137 cei.opt_id
138 FROM per_collective_agreements pca,
139 per_cagr_entitlements pce,
140 per_cagr_entitlement_items cei
141 WHERE pca.collective_agreement_id = pce.collective_agreement_id
142 AND pce.cagr_entitlement_id = p_cagr_entitlement_id
143 and cei.cagr_entitlement_item_id = pce.cagr_entitlement_item_id;
144 --
145 l_cagr_entitlement_line_id per_cagr_entitlement_lines_f.cagr_entitlement_line_id%TYPE;
146 l_effective_start_date per_cagr_entitlement_lines_f.effective_start_date%TYPE;
147 l_effective_end_date per_cagr_entitlement_lines_f.effective_end_date%TYPE;
148 l_effective_date DATE;
149 l_proc VARCHAR2(72) := g_package||'create_entitlement_line';
150 l_object_version_number per_cagr_entitlement_lines_f.object_version_number%TYPE;
151 l_business_group_id NUMBER;
152 l_opt_id ben_opt_f.opt_id%TYPE;
153 l_order_number ben_oipl_f.ordr_num%TYPE;
154 --
155 l_pl_typ_opt_typ_id NUMBER;
156 l_pl_typ_opt_typ_ovn NUMBER;
157 l_pl_id NUMBER;
158 --
159 l_oipl_id NUMBER;
160 l_oipl_ovn NUMBER;
161 --
162 l_prtn_elig_prfl_id NUMBER;
163 l_prtn_ovn NUMBER;
164 --
165 l_prtn_elig_id NUMBER;
166 l_prtn_elig_ovn NUMBER;
167 --
168 BEGIN
169 --
170 hr_utility.set_location('Entering:'|| l_proc, 10);
171 --
172 -- Issue a SAVEPOINT IF operating in validation only mode
173 --
174 SAVEPOINT create_entitlement_line;
175 --
176 -- Truncate date paramters
177 --
178 l_effective_date := TRUNC(p_effective_date);
179 --
180 OPEN csr_cagr_information;
181 FETCH csr_cagr_information INTO l_pl_id, l_business_group_id, l_opt_id;
182 CLOSE csr_cagr_information;
183 --
184 -- If the entitlement lines has been created as the DEFAULT
185 -- line for the Entitlement then set the option in plan id
186 -- to be the default as well.
187 --
188 IF p_eligy_prfl_id = 0 THEN
189 --
190 l_oipl_id := 0;
191 --
192 -- If the entitlement line has NOT been created as the
193 -- default line then create the option and link it
194 -- to the plan.
195 --
196 ELSE
197 --
198 BEGIN
199 --
200 hr_utility.set_location(l_proc, 40);
201 --
202 l_order_number := per_cagr_utility_pkg.get_next_order_number(p_pl_id => l_pl_id);
203 --
204 ben_option_in_plan_api.create_option_in_plan
205 (p_validate => p_validate
206 ,p_oipl_id => l_oipl_id
207 ,p_effective_start_date => l_effective_start_date
208 ,p_effective_end_date => l_effective_end_date
209 ,p_pl_id => l_pl_id
210 ,p_opt_id => l_opt_id
211 ,p_business_group_id => l_business_group_id
212 ,p_object_version_number => l_oipl_ovn
213 ,p_effective_date => l_effective_date
214 ,p_mndtry_flag => 'N' -- Default Value
215 ,p_dflt_flag => 'N' -- Default Value
216 ,p_oipl_stat_cd => 'A' -- Active
217 ,p_elig_apls_flag => 'N' -- Default Value
218 ,p_trk_inelig_per_flag => 'N' -- Default Value
219 ,p_auto_enrt_flag => 'N'
220 ,p_ordr_num => l_order_number);
221 --
222 hr_utility.set_location(l_proc||'/'||l_oipl_id, 50);
223 --
224 END;
225 --
226 BEGIN
227 --
228 ben_participation_elig_api.create_participation_elig
229 (p_prtn_elig_id =>l_prtn_elig_id
230 ,p_effective_start_date =>l_effective_start_date
231 ,p_effective_end_date =>l_effective_end_date
232 ,p_business_group_id =>l_business_group_id
233 ,p_prtn_eff_strt_dt_cd => 'AED'
234 --,p_pgm_id =>p_pgm_id
235 --,p_pl_id =>p_pl_id
236 ,p_oipl_id =>l_oipl_id
237 --,p_ptip_id =>p_ptip_id
238 --,p_plip_id =>p_plip_id
239 ,p_object_version_number =>l_prtn_elig_ovn
240 ,p_effective_date =>p_effective_date
241 ) ;
242 --
243 END;
244 --
245 BEGIN
246 --
247 hr_utility.set_location(l_proc, 60);
248 --
249 ben_prtn_elig_prfl_api.create_prtn_elig_prfl
250 (p_validate => p_validate
251 ,p_PRTN_ELIG_PRFL_ID => l_prtn_elig_prfl_id
252 ,p_EFFECTIVE_START_DATE => l_effective_start_date
253 ,p_EFFECTIVE_END_DATE => l_effective_end_date
254 ,p_BUSINESS_GROUP_ID => l_business_group_id
255 ,p_MNDTRY_FLAG => 'Y'
256 --,p_PRTN_ELIG_ID => to_number(name_in('CEP.PRTN_ELIG_ID'))
257 ,p_ELIGY_PRFL_ID => p_eligy_prfl_id
258 ,p_oipl_id => l_oipl_id
259 ,p_object_version_number => l_prtn_ovn
260 ,p_effective_date => l_effective_date);
261 --
262 hr_utility.set_location(l_proc||'/'||l_prtn_elig_prfl_id, 70);
263 --
264 END;
265 --
266 END IF;
267 --
268 -- Process Logic
269 --
270 BEGIN
271 --
272 -- Start of API User Hook for the before hook of create_entitlement_line
273 --
274 hr_utility.set_location(l_proc, 80);
275 --
276 hr_cagr_ent_lines_bk1.create_entitlement_line_b
277 (
278 p_mandatory => p_mandatory
279 ,p_value => p_value
280 ,p_range_from => p_range_from
281 ,p_range_to => p_range_to
282 ,p_grade_spine_id => p_grade_spine_id
283 ,p_parent_spine_id => p_parent_spine_id
284 ,p_cagr_entitlement_id => p_cagr_entitlement_id
285 ,p_status => p_status
286 ,p_oipl_id => l_oipl_id
287 ,p_eligy_prfl_id => p_eligy_prfl_id
288 ,p_step_id => p_step_id
289 ,p_from_step_id => p_from_step_id
290 ,p_to_step_id => p_to_step_id
291 ,p_effective_date => TRUNC(p_effective_date)
292 );
293 --
294 hr_utility.set_location(l_proc, 90);
295 --
296 EXCEPTION
297 WHEN hr_api.cannot_find_prog_unit THEN
298 hr_api.cannot_find_prog_unit_error
299 (
300 p_module_name => 'CREATE_entitlement_line'
301 ,p_hook_type => 'BP'
302 );
303 --
304 -- End of API User Hook for the before hook of create_entitlement_line
305 --
306 END;
307 --
308 hr_utility.set_location(l_proc, 100);
309 --
310 per_pcl_ins.ins
311 (p_cagr_entitlement_line_id => l_cagr_entitlement_line_id
312 ,p_mandatory => p_mandatory
313 ,p_value => p_value
314 ,p_range_from => p_range_from
315 ,p_range_to => p_range_to
316 ,p_effective_start_date => l_effective_start_date
317 ,p_effective_END_date => l_effective_END_date
318 ,p_grade_spine_id => p_grade_spine_id
319 ,p_parent_spine_id => p_parent_spine_id
320 ,p_cagr_entitlement_id => p_cagr_entitlement_id
321 ,p_status => p_status
322 ,p_oipl_id => l_oipl_id
323 ,p_eligy_prfl_id => p_eligy_prfl_id
324 ,p_step_id => p_step_id
325 ,p_from_step_id => p_from_step_id
326 ,p_to_step_id => p_to_step_id
327 ,p_object_version_number => l_object_version_number
328 ,p_effective_date => TRUNC(p_effective_date)
329 );
330 --
331 hr_utility.set_location(l_proc, 110);
332 --
333 BEGIN
334 --
335 -- Start of API User Hook for the after hook of create_entitlement_line
336 --
337 hr_utility.set_location(l_proc, 120);
338 --
339 hr_cagr_ent_lines_bk1.create_entitlement_line_a
340 (
341 p_cagr_entitlement_line_id => l_cagr_entitlement_line_id
342 ,p_mandatory => p_mandatory
343 ,p_value => p_value
344 ,p_range_from => p_range_from
345 ,p_range_to => p_range_to
346 ,p_effective_start_date => l_effective_start_date
347 ,p_effective_END_date => l_effective_END_date
348 ,p_grade_spine_id => p_grade_spine_id
349 ,p_parent_spine_id => p_parent_spine_id
350 ,p_cagr_entitlement_id => p_cagr_entitlement_id
351 ,p_status => p_status
352 ,p_oipl_id => l_oipl_id
353 ,p_eligy_prfl_id => p_eligy_prfl_id
354 ,p_step_id => p_step_id
355 ,p_from_step_id => p_from_step_id
356 ,p_to_step_id => p_to_step_id
357 ,p_object_version_number => l_object_version_number
358 ,p_effective_date => l_effective_date
359 );
360 --
361 hr_utility.set_location(l_proc, 130);
362 --
363 EXCEPTION
364 WHEN hr_api.cannot_find_prog_unit THEN
365 hr_api.cannot_find_prog_unit_error
366 (p_module_name => 'CREATE_entitlement_line'
367 ,p_hook_type => 'AP'
368 );
369 --
370 -- End of API User Hook for the after hook of create_entitlement_line
371 --
372 END;
373 --
374 hr_utility.set_location(l_proc, 140);
375 --
376 -- When in validation only mode RAISE the Validate_Enabled EXCEPTION
377 --
378 IF p_validate THEN
379 RAISE hr_api.validate_enabled;
380 END IF;
381 --
382 -- Set all output arguments
383 --
387 p_object_version_number := l_object_version_number;
384 p_cagr_entitlement_line_id := l_cagr_entitlement_line_id;
385 p_effective_start_date := l_effective_start_date;
386 p_effective_END_date := l_effective_END_date;
388 p_oipl_id := l_oipl_id;
389 --
390 hr_utility.set_location(' Leaving:'||l_proc, 999);
391 --
392 EXCEPTION
393 --
394 WHEN hr_api.validate_enabled THEN
395 --
396 -- As the Validate_Enabled EXCEPTION has been RAISEd
397 -- we must rollback to the SAVEPOINT
398 --
399 ROLLBACK TO create_entitlement_line;
400 --
401 -- Only set output warning arguments
402 -- (Any key or derived arguments must be set to null
403 -- WHEN validation only mode is being used.)
404 --
405 p_cagr_entitlement_line_id := null;
406 p_effective_start_date := null;
407 p_effective_END_date := null;
408 p_object_version_number := null;
409 --
410 hr_utility.set_location(' Leaving:'||l_proc, 80);
411 --
412 WHEN others THEN
413 --
414 -- A validation or unexpected error has occured
415 --
416 ROLLBACK TO create_entitlement_line;
417 --
418 -- set in out parameters and set out parameters
419 --
420 p_cagr_entitlement_line_id := null;
421 p_effective_start_date := null;
422 p_effective_END_date := null;
423 p_object_version_number := null;
424 p_oipl_id := null;
425 RAISE;
426 --
427 END create_entitlement_line;
428 -- ----------------------------------------------------------------------------
429 -- |------------------------< update_entitlement_line >--- ------------------|
430 -- ----------------------------------------------------------------------------
431 --
432 PROCEDURE update_entitlement_line
433 (p_validate in boolean default false
434 ,p_cagr_entitlement_line_id in NUMBER
435 ,p_mandatory in VARCHAR2 default hr_api.g_VARCHAR2
436 ,p_value in VARCHAR2 default hr_api.g_VARCHAR2
437 ,p_range_from in VARCHAR2 default hr_api.g_VARCHAR2
438 ,p_range_to in VARCHAR2 default hr_api.g_VARCHAR2
439 ,p_effective_start_date out nocopy DATE
440 ,p_effective_END_date out nocopy DATE
441 ,p_grade_spine_id in NUMBER default hr_api.g_number
442 ,p_parent_spine_id in NUMBER default hr_api.g_number
443 ,p_cagr_entitlement_id in NUMBER default hr_api.g_number
444 ,p_status in VARCHAR2 default hr_api.g_VARCHAR2
445 ,p_oipl_id in NUMBER default hr_api.g_number
446 ,p_eligy_prfl_id in NUMBER default hr_api.g_number
447 ,p_step_id in NUMBER default hr_api.g_number
448 ,p_from_step_id in NUMBER default hr_api.g_number
449 ,p_to_step_id in NUMBER default hr_api.g_number
450 ,p_object_version_number in out nocopy NUMBER
451 ,p_effective_date in DATE
452 ,p_datetrack_mode in VARCHAR2
453 ) is
454 --
455 CURSOR get_prtn_elig_prfl_id IS
456 SELECT b2.prtn_elig_prfl_id,
457 b2.object_version_number
458 FROM ben_prtn_elig_prfl_f b2,
459 ben_prtn_elig_f b1
460 WHERE p_effective_date BETWEEN b2.effective_start_Date
461 AND b2.effective_end_date
462 AND b2.prtn_elig_id = b1.prtn_elig_id
463 AND b1.oipl_id = p_oipl_id
464 AND p_effective_date BETWEEN b1.effective_start_date
465 AND b1.effective_end_date;
466 --
467 -- Declare cursors and local variables
468 --
469 l_proc VARCHAR2(72) := g_package||'update_entitlement_line';
470 l_object_version_number per_cagr_entitlement_lines_f.object_version_number%TYPE;
471 l_ovn per_cagr_entitlement_lines_f.object_version_number%TYPE := p_object_version_number;
472 l_effective_start_date per_cagr_entitlement_lines_f.effective_start_date%TYPE;
473 l_effective_end_date per_cagr_entitlement_lines_f.effective_END_date%TYPE;
474 l_effective_date DATE;
475 --
476 l_prtn_elig_prfl_id NUMBER;
477 l_prtn_eff_st_date DATE;
478 l_prtn_eff_end_date DATE;
479 l_prtn_ovn NUMBER;
480 l_oipl_id NUMBER;
481 --
482 BEGIN
483 --
484 hr_utility.set_location('Entering:'|| l_proc, 10);
485 --
486 l_effective_date := TRUNC(p_effective_date);
487 --
488 -- Issue a SAVEPOINT IF operating in validation only mode
489 --
490 SAVEPOINT update_entitlement_line;
491 --
492 hr_utility.set_location(l_proc, 20);
493 --
494 -- Process Logic
495 --
496 l_object_version_number := p_object_version_number;
497 --
498 BEGIN
499 --
500 -- Start of API User Hook for the before hook of update_entitlement_line
501 --
502 hr_cagr_ent_lines_bk2.update_entitlement_line_b
503 (
504 p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
505 ,p_mandatory => p_mandatory
506 ,p_value => p_value
507 ,p_range_from => p_range_from
508 ,p_range_to => p_range_to
512 ,p_status => p_status
509 ,p_grade_spine_id => p_grade_spine_id
510 ,p_parent_spine_id => p_parent_spine_id
511 ,p_cagr_entitlement_id => p_cagr_entitlement_id
513 ,p_oipl_id => p_oipl_id
514 ,p_eligy_prfl_id => p_eligy_prfl_id
515 ,p_step_id => p_step_id
516 ,p_from_step_id => p_from_step_id
517 ,p_to_step_id => p_to_step_id
518 ,p_object_version_number => p_object_version_number
519 ,p_effective_date => l_effective_date
520 ,p_datetrack_mode => p_datetrack_mode
521 );
522 EXCEPTION
523 WHEN hr_api.cannot_find_prog_unit THEN
524 hr_api.cannot_find_prog_unit_error
525 (p_module_name => 'UPDATE_entitlement_line'
526 ,p_hook_type => 'BP'
527 );
528 --
529 -- End of API User Hook for the before hook of update_entitlement_line
530 --
531 END;
532 --
533 /*
534 BEGIN
535 --
536 hr_utility.set_location(l_proc, 60);
537 --
538 OPEN get_prtn_elig_prfl_id;
539 FETCH get_prtn_elig_prfl_id INTO l_prtn_elig_prfl_id,l_prtn_ovn;
540 --
541 hr_utility.set_location(l_proc||'/'||l_prtn_elig_prfl_id, 65);
542 --
543 IF get_prtn_elig_prfl_id%NOTFOUND THEN
544 --
545 CLOSE get_prtn_elig_prfl_id;
546 --
547 hr_utility.set_message(800,'HR_289380_CAGR_OPT_ELIG_ERROR');
548 hr_utility.raise_error;
549 --
550 ELSE
551 --
552 CLOSE get_prtn_elig_prfl_id;
553 --
554 END IF;
555 --
556 ben_prtn_elig_prfl_api.update_prtn_elig_prfl
557 (p_validate => p_validate
558 ,p_PRTN_ELIG_PRFL_ID => l_prtn_elig_prfl_id
559 ,p_EFFECTIVE_START_DATE => l_prtn_eff_st_date
560 ,p_EFFECTIVE_END_DATE => l_prtn_eff_end_date
561 --,p_BUSINESS_GROUP_ID => l_business_group_id
562 --,p_MNDTRY_FLAG => 'Y'
563 --,p_PRTN_ELIG_ID => to_number(name_in('CEP.PRTN_ELIG_ID'))
564 ,p_ELIGY_PRFL_ID => p_eligy_prfl_id
565 --,p_oipl_id => l_oipl_id
566 ,p_object_version_number => l_prtn_ovn
567 ,p_effective_date => l_effective_date
568 ,p_datetrack_mode => p_datetrack_mode);
569 --
570 hr_utility.set_location(l_proc||'/'||l_prtn_elig_prfl_id, 70);
571 --
572 END;
573 */
574 --
575 per_pcl_upd.upd
576 (
577 p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
578 ,p_mandatory => p_mandatory
579 ,p_value => p_value
580 ,p_range_from => p_range_from
581 ,p_range_to => p_range_to
582 ,p_effective_start_date => l_effective_start_date
583 ,p_effective_END_date => l_effective_end_date
584 ,p_grade_spine_id => p_grade_spine_id
585 ,p_parent_spine_id => p_parent_spine_id
586 ,p_cagr_entitlement_id => p_cagr_entitlement_id
587 ,p_status => p_status
588 ,p_oipl_id => p_oipl_id
589 ,p_eligy_prfl_id => p_eligy_prfl_id
590 ,p_step_id => p_step_id
591 ,p_from_step_id => p_from_step_id
592 ,p_to_step_id => p_to_step_id
593 ,p_object_version_number => l_object_version_number
594 ,p_effective_date => TRUNC(p_effective_date)
595 ,p_datetrack_mode => p_datetrack_mode
596 );
597 --
598 BEGIN
599 --
600 -- Start of API User Hook for the after hook of update_entitlement_line
601 --
602 hr_cagr_ent_lines_bk2.update_entitlement_line_a
603 (
604 p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
605 ,p_mandatory => p_mandatory
606 ,p_value => p_value
607 ,p_range_from => p_range_from
608 ,p_range_to => p_range_to
609 ,p_effective_start_date => l_effective_start_date
610 ,p_effective_END_date => l_effective_END_date
611 ,p_grade_spine_id => p_grade_spine_id
612 ,p_parent_spine_id => p_parent_spine_id
613 ,p_cagr_entitlement_id => p_cagr_entitlement_id
614 ,p_status => p_status
615 ,p_oipl_id => p_oipl_id
616 ,p_eligy_prfl_id => p_eligy_prfl_id
617 ,p_step_id => p_step_id
618 ,p_from_step_id => p_from_step_id
619 ,p_to_step_id => p_to_step_id
620 ,p_object_version_number => l_object_version_number
621 ,p_effective_date => TRUNC(p_effective_date)
622 ,p_datetrack_mode => p_datetrack_mode
623 );
624 EXCEPTION
625 WHEN hr_api.cannot_find_prog_unit THEN
626 hr_api.cannot_find_prog_unit_error
627 (p_module_name => 'UPDATE_entitlement_line'
628 ,p_hook_type => 'AP'
629 );
630 --
634 --
631 -- End of API User Hook for the after hook of update_entitlement_line
632 --
633 END;
635 hr_utility.set_location(l_proc, 60);
636 --
637 -- When in validation only mode RAISE the Validate_Enabled EXCEPTION
638 --
639 IF p_validate THEN
640 RAISE hr_api.validate_enabled;
641 END IF;
642 --
643 -- Set all output arguments
644 --
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 --
649 hr_utility.set_location(' Leaving:'||l_proc, 70);
650 --
651 EXCEPTION
652 --
653 WHEN hr_api.validate_enabled THEN
654 --
655 -- As the Validate_Enabled EXCEPTION has been RAISEd
656 -- we must rollback to the SAVEPOINT
657 --
658 ROLLBACK TO update_entitlement_line;
659 --
660 -- Only set output warning arguments
661 -- (Any key or derived arguments must be set to null
662 -- WHEN validation only mode is being used.)
663 --
664 hr_utility.set_location(' Leaving:'||l_proc, 80);
665 --
666 WHEN others THEN
667 --
668 -- A validation or unexpected error has occured
669 --
670 ROLLBACK TO update_entitlement_line;
671 --
672 -- set in out parameters and set out parameters
673 --
674 p_effective_start_date := null;
675 p_effective_END_date := null;
676 p_object_version_number := l_ovn;
677 RAISE;
678 --
679 END update_entitlement_line;
680 -- ----------------------------------------------------------------------------
681 -- |------------------------< delete_entitlement_line >----------------------|
682 -- ----------------------------------------------------------------------------
683 --
684 PROCEDURE delete_entitlement_line
685 (p_validate in boolean default false
686 ,p_cagr_entitlement_line_id in NUMBER
687 ,p_effective_start_date out nocopy DATE
688 ,p_effective_END_date out nocopy DATE
689 ,p_object_version_number in out nocopy NUMBER
690 ,p_effective_date in DATE
691 ,p_datetrack_mode in VARCHAR2
692 ) is
693 --
694 -- Declare cursors and local variables
695 --
696 CURSOR csr_oipl_details IS
697 SELECT bof.oipl_id,
698 bof.object_version_number,
699 bpe.prtn_elig_id,
700 bpe.object_version_number,
701 bep.prtn_elig_prfl_id,
702 bep.object_version_number
703 FROM ben_prtn_elig_prfl_f bep,
704 ben_prtn_elig_f bpe,
705 ben_oipl_f bof,
706 per_cagr_entitlement_lines_f pcl
707 WHERE bep.prtn_elig_id = bpe.prtn_elig_id
708 AND bpe.oipl_id = bof.oipl_id
709 AND bof.oipl_id = pcl.oipl_id
710 AND pcl.cagr_entitlement_line_id = p_cagr_entitlement_line_id;
711 --
712
713
714 l_proc VARCHAR2(72) := g_package||'update_entitlement_line';
715 l_object_version_number per_cagr_entitlement_lines_f.object_version_number%TYPE;
716 l_ovn per_cagr_entitlement_lines_f.object_version_number%TYPE := p_object_version_number;
717 l_effective_start_date per_cagr_entitlement_lines_f.effective_start_date%TYPE;
718 l_effective_END_date per_cagr_entitlement_lines_f.effective_END_date%TYPE;
719 l_end_date DATE;
720 l_start_date DATE;
721 l_oipl_ovn NUMBER;
722 l_oipl_id NUMBER;
723 l_prtn_elig_id NUMBER;
724 l_prtn_elig_ovn NUMBER;
725 l_prtn_elig_prfl_id NUMBER;
726 l_prtn_elig_prfl_ovn NUMBER;
727 --
728 BEGIN
729 --
730 hr_utility.set_location('Entering:'|| l_proc, 10);
731 --
732 -- Issue a SAVEPOINT IF operating in validation only mode
733 --
734 SAVEPOINT delete_entitlement_line;
735 --
736 hr_utility.set_location(l_proc, 20);
737 --
738 -- Process Logic
739 --
740 l_object_version_number := p_object_version_number;
741 --
742 BEGIN
743 --
744 -- Start of API User Hook for the before hook of delete_entitlement_line
745 --
746 hr_cagr_ent_lines_bk3.delete_entitlement_line_b
747 (p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
748 ,p_object_version_number => p_object_version_number
749 ,p_effective_date => TRUNC(p_effective_date)
750 ,p_datetrack_mode => p_datetrack_mode);
751 --
752 EXCEPTION
753 WHEN hr_api.cannot_find_prog_unit THEN
754 hr_api.cannot_find_prog_unit_error
755 (p_module_name => 'DELETE_entitlement_line'
756 ,p_hook_type => 'BP'
757 );
758 --
759 -- End of API User Hook for the before hook of delete_entitlement_line
760 --
761 END;
762 --
763 -- If we are removing the entire record then also
764 -- delete the records created in the benefits table
765 -- for options in plans.
766 --
767 IF p_datetrack_mode = 'ZAP' THEN
768 --
769 -- Delete any results, retained rights, logs
770 -- that are linked to the entitlement line
771 --
772 delete_from_engine_tables
773 (p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
777 -- for the entitlement line
774 ,p_effective_date => p_effective_date);
775 --
776 -- Delete option in plans created
778 --
779 hr_utility.set_location(l_proc, 30);
780 --
781 OPEN csr_oipl_details;
782 FETCH csr_oipl_details INTO l_oipl_id
783 ,l_oipl_ovn
784 ,l_prtn_elig_id
785 ,l_prtn_elig_ovn
786 ,l_prtn_elig_prfl_id
787 ,l_prtn_elig_prfl_ovn;
788 --
789 IF csr_oipl_details%FOUND THEN
790 --
791 CLOSE csr_oipl_details;
792 --
793 hr_utility.set_location(l_proc||'/'||
794 l_prtn_elig_prfl_id||'/'||
795 l_prtn_elig_prfl_ovn, 40);
796 --
797 ben_prtn_elig_prfl_api.delete_prtn_elig_prfl
798 (p_validate => p_validate
799 ,p_prtn_elig_prfl_id => l_prtn_elig_prfl_id
800 ,p_effective_start_date => l_start_date
801 ,p_effective_end_date => l_end_date
802 ,p_object_version_number => l_prtn_elig_prfl_ovn
803 ,p_effective_date => p_effective_Date
804 ,p_datetrack_mode => p_datetrack_mode);
805 --
806 hr_utility.set_location(l_proc||'/'||
807 l_prtn_elig_id||'/'||
808 l_prtn_elig_ovn, 50);
809 --
810 ben_participation_elig_api.delete_participation_elig
811 (p_validate => p_validate
812 ,p_prtn_elig_id => l_prtn_elig_id
813 ,p_effective_start_date => l_start_date
814 ,p_effective_end_date => l_end_date
815 ,p_object_version_number => l_prtn_elig_ovn
816 ,p_effective_date => p_effective_date
817 ,p_datetrack_mode => p_datetrack_mode);
818 --
819 hr_utility.set_location(l_proc||'/'||
820 l_oipl_id||'/'||
821 l_oipl_ovn, 60);
822 --
823 ben_option_in_plan_api.delete_option_in_plan
824 (p_validate => p_validate
825 ,p_oipl_id => l_oipl_id
826 ,p_effective_start_date => l_start_date
827 ,p_effective_end_date => l_end_date
828 ,p_object_version_number => l_oipl_ovn
829 ,p_effective_date => p_effective_date
830 ,p_datetrack_mode => p_datetrack_mode);
831 --
832 ELSE
833 --
834 CLOSE csr_oipl_details;
835 --
836 END IF;
837 --
838 END IF;
839 --
840 per_pcl_del.del
841 (p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
842 ,p_effective_start_date => l_effective_start_date
843 ,p_effective_END_date => l_effective_END_date
844 ,p_object_version_number => l_object_version_number
845 ,p_effective_date => p_effective_date
846 ,p_datetrack_mode => p_datetrack_mode);
847 --
848 BEGIN
849 --
850 -- Start of API User Hook for the after hook of delete_entitlement_line
851 --
852 hr_cagr_ent_lines_bk3.delete_entitlement_line_a
853 (
854 p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
855 ,p_effective_start_date => l_effective_start_date
856 ,p_effective_END_date => l_effective_END_date
857 ,p_object_version_number => l_object_version_number
858 ,p_effective_date => TRUNC(p_effective_date)
859 ,p_datetrack_mode => p_datetrack_mode
860 );
861 EXCEPTION
862 WHEN hr_api.cannot_find_prog_unit THEN
863 hr_api.cannot_find_prog_unit_error
864 (p_module_name => 'DELETE_entitlement_line'
865 ,p_hook_type => 'AP'
866 );
867 --
868 -- End of API User Hook for the after hook of delete_entitlement_line
869 --
870 END;
871 --
872 hr_utility.set_location(l_proc, 60);
873 --
874 -- When in validation only mode RAISE the Validate_Enabled EXCEPTION
875 --
876 IF p_validate THEN
877 RAISE hr_api.validate_enabled;
878 END IF;
879 --
880 hr_utility.set_location(' Leaving:'||l_proc, 70);
881 --
882 EXCEPTION
883 --
884 WHEN hr_api.validate_enabled THEN
885 --
886 -- As the Validate_Enabled EXCEPTION has been RAISEd
887 -- we must rollback to the SAVEPOINT
888 --
889 ROLLBACK TO delete_entitlement_line;
890 --
891 -- Only set output warning arguments
892 -- (Any key or derived arguments must be set to null
893 -- WHEN validation only mode is being used.)
894 --
895 p_effective_start_date := null;
896 p_effective_END_date := null;
897 --
898 WHEN others THEN
899 --
900 -- A validation or unexpected error has occured
901 --
902 ROLLBACK TO delete_entitlement_line;
903 --
904 -- set in out parameters and set out parameters
905 --
906 p_effective_start_date := null;
907 p_effective_END_date := null;
908 p_object_version_number := l_ovn;
909 RAISE;
910 --
911 END delete_entitlement_line;
912 --
913 -- ----------------------------------------------------------------------------
914 -- |-------------------------------< lck >------------------------------------|
915 -- ----------------------------------------------------------------------------
916 --
917 PROCEDURE lck
918 (
919 p_cagr_entitlement_line_id in NUMBER
920 ,p_object_version_number in NUMBER
924 ,p_validation_END_date out nocopy DATE
921 ,p_effective_date in DATE
922 ,p_datetrack_mode in VARCHAR2
923 ,p_validation_start_date out nocopy DATE
925 ) is
926 --
927 --
928 -- Declare cursors and local variables
929 --
930 l_proc VARCHAR2(72) := g_package||'lck';
931 l_validation_start_date DATE;
932 l_validation_END_date DATE;
933 --
934 BEGIN
935 --
936 hr_utility.set_location('Entering:'|| l_proc, 10);
937 --
938 per_pcl_shd.lck
939 (
943 ,p_object_version_number => p_object_version_number
940 p_cagr_entitlement_line_id => p_cagr_entitlement_line_id
941 ,p_validation_start_date => l_validation_start_date
942 ,p_validation_END_date => l_validation_END_date
944 ,p_effective_date => p_effective_date
945 ,p_datetrack_mode => p_datetrack_mode
946 );
947 --
948 hr_utility.set_location(' Leaving:'||l_proc, 70);
949 --
950 END lck;
951 --
952 END hr_cagr_ent_lines_api;