[Home] [Help]
PACKAGE BODY: APPS.PAY_LINK_INPUT_VALUES_API
Source
1 Package Body PAY_LINK_INPUT_VALUES_API as
2 /* $Header: pylivapi.pkb 115.4 2003/01/29 11:57:01 scchakra noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PAY_LIV_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_liv_internal >---------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure CREATE_LIV_INTERNAL
13 (p_effective_date in date
14 ,p_element_link_id in number
15 ,p_input_value_id in number
16 ,p_costed_flag in varchar2
17 ,p_default_value in varchar2
18 ,p_max_value in varchar2
19 ,p_min_value in varchar2
20 ,p_warning_or_error in varchar2
21 ,p_link_input_value_id out nocopy number
22 ,p_effective_start_date out nocopy date
23 ,p_effective_end_date out nocopy date
24 ,p_object_version_number out nocopy number
25 ,p_pay_basis_warning out nocopy boolean
26 ) is
27 --
28 -- Declare cursors and local variables
29 --
30
31 l_proc varchar2(72) := g_package||'create_liv_internal';
32 l_effective_date date;
33 l_effective_start_date date;
34 l_effective_end_date date;
35 l_exists varchar2(1);
36 l_rec pay_input_values_f%rowtype;
37 l_costable_type pay_element_links_f.costable_type%type;
38 l_default_value pay_link_input_values_f.default_value%type;
39 l_max_value pay_link_input_values_f.max_value%type;
40 l_min_value pay_link_input_values_f.min_value%type;
41 l_warning_or_error pay_link_input_values_f.warning_or_error%type;
42 l_costed_flag pay_link_input_values_f.costed_flag%type;
43 l_link_input_value_id pay_link_input_values_f.link_input_value_id%type;
44 l_object_version_number pay_link_input_values_f.object_version_number%type;
45
46
47 Cursor C_input_values
48 is
49 select *
53 and effective_end_date;
50 from pay_input_values_f
51 where input_value_id = p_input_value_id
52 and p_effective_date between effective_start_date
54
55 Cursor C_costable_type
56 is
57 select costable_type
58 from pay_element_links_f
59 where element_link_id = p_element_link_id
60 and p_effective_date between effective_start_date
61 and effective_end_date;
62
63 Cursor c_pay_basis
64 is
65 select null
66 from per_pay_bases
67 where input_value_id = p_input_value_id;
68
69 --
70 begin
71 hr_utility.set_location('Entering:'|| l_proc, 10);
72 --
73 -- Truncate the time portion from all IN date parameters
74 --
75 l_effective_date := trunc(p_effective_date);
76 --
77 -- Validation in addition to Row Handlers
78 --
79 Open C_Input_values;
80 Fetch C_Input_values Into l_rec;
81 Close C_Input_values;
82 --
83 -- Set the defaults for the Input Values
84 --
85 If l_rec.hot_default_flag = 'Y' Then
86 l_default_value := null;
87 l_max_value := null;
88 l_min_value := null;
89 l_warning_or_error := null;
90 Else
91 l_default_value := l_rec.default_value;
92 l_max_value := l_rec.max_value;
93 l_min_value := l_rec.min_value;
94 l_warning_or_error := l_rec.warning_or_error;
95 End if;
96
97 Open C_costable_type;
98 Fetch C_costable_type into l_costable_type;
99 Close C_costable_type;
100
101 l_costed_flag := p_costed_flag;
102 --
103 -- Set the default for costed flag depending on costable_type of
104 -- element link.
105 --
106
107 If (upper(l_rec.name) = 'PAY VALUE' and
108 (l_costable_type in ('C','F','D'))) Then
109 l_costed_flag := 'Y';
110 End if;
111 --
112 -- Raise a warning if the input value is a pay basis for the element.
113 --
114 Open c_pay_basis;
115 Loop
116 Fetch c_pay_basis into l_exists;
117 If c_pay_basis%found then
118 p_pay_basis_warning := True;
119 Else
120 exit;
121 End if;
122 End Loop;
123 Close c_pay_basis;
124 --
125 -- Process Logic
126 --
127 pay_liv_ins.ins
128 (p_effective_date => l_effective_date
129 ,p_element_link_id => p_element_link_id
130 ,p_input_value_id => p_input_value_id
131 ,p_costed_flag => l_costed_flag
132 ,p_default_value => l_default_value
133 ,p_max_value => l_max_value
134 ,p_min_value => l_min_value
135 ,p_warning_or_error => l_warning_or_error
136 ,p_link_input_value_id => l_link_input_value_id
137 ,p_object_version_number => l_object_version_number
138 ,p_effective_start_date => l_effective_start_date
139 ,p_effective_end_date => l_effective_end_date
140 );
141 --
142 -- Set all output arguments
143 --
144 p_link_input_value_id := l_link_input_value_id;
145 p_object_version_number := l_object_version_number;
146 p_effective_start_date := l_effective_start_date;
147 p_effective_end_date := l_effective_end_date;
148 --
149 hr_utility.set_location(' Leaving:'||l_proc, 40);
150
151 end CREATE_LIV_INTERNAL;
152 --
153 --
154 -- ----------------------------------------------------------------------------
155 -- |------------------------< update_link_input_values >----------------------|
156 -- ----------------------------------------------------------------------------
157 --
158 procedure UPDATE_LINK_INPUT_VALUES
159 (p_validate in boolean
160 ,p_effective_date in date
161 ,p_datetrack_update_mode in varchar2
162 ,p_link_input_value_id in number
163 ,p_object_version_number in out nocopy number
164 ,p_costed_flag in varchar2
165 ,p_default_value in varchar2
166 ,p_max_value in varchar2
167 ,p_min_value in varchar2
168 ,p_warning_or_error in varchar2
169 ,p_effective_start_date out nocopy date
170 ,p_effective_end_date out nocopy date
171 ,p_pay_basis_warning out nocopy boolean
172 ,p_default_range_warning out nocopy boolean
176 ) is
173 ,p_default_formula_warning out nocopy boolean
174 ,p_assignment_id_warning out nocopy boolean
175 ,p_formula_message out nocopy varchar2
177 --
178 -- Declare cursors and local variables
179 --
180
181 l_proc varchar2(72) := g_package||'update_liv';
182 l_effective_date date;
183 l_effective_end_date date;
184 l_effective_start_date date;
185 l_exists varchar2(1);
186 l_default_range_warning boolean;
187 l_default_formula_warning boolean;
188 l_assignment_id_warning boolean;
189 l_formula_message fnd_new_messages.message_text%type;
190 l_default_value pay_link_input_values_f.default_value%type := p_default_value;
191 l_element_link_id pay_link_input_values_f.element_link_id%type;
192 l_input_value_id pay_link_input_values_f.input_value_id%type;
193 l_object_version_number pay_link_input_values_f.object_version_number%type;
194 l_lookup_type hr_lookups.lookup_type%type;
195
196 Cursor c_link_input_value_id
197 is
198 select element_link_id, input_value_id
199 from pay_link_input_values_f
200 where link_input_value_id = p_link_input_value_id
201 and p_effective_date between effective_start_date
202 and effective_end_date;
203
204 Cursor c_pay_basis
205 is
206 select null
207 from per_pay_bases
208 where input_value_id = l_input_value_id;
209
210 Cursor c_lookup_type(p_input_value_id number)
211 is
212 select lookup_type
213 from pay_input_values_f
214 where input_value_id = p_input_value_id
215 and p_effective_date between effective_start_date
216 and effective_end_date;
217
218 Cursor c_lookup_code(p_lookup_type varchar2)
219 is
220 select lookup_code
221 from hr_lookups
222 where lookup_type = p_lookup_type
223 and upper(meaning) = p_default_value
224 and enabled_flag = 'Y'
225 and p_effective_date between nvl(start_date_active, p_effective_date)
226 and nvl(end_date_active, p_effective_date);
227
228 --
229 begin
230 hr_utility.set_location('Entering:'|| l_proc, 10);
231 --
232 -- Issue a savepoint
233 --
234 savepoint update_link_input_values;
235 --
236 -- Truncate the time portion from all IN date parameters
237 --
238 l_effective_date := trunc(p_effective_date);
239 --
240 -- Initialize all IN/OUT parameters
241 --
242 l_object_version_number := p_object_version_number;
243 --
244 -- Check all the mandatory parameters are specified.
245 --
246 If (nvl(p_link_input_value_id,hr_api.g_number) = hr_api.g_number) then
247 hr_api.mandatory_arg_error
248 (p_api_name => l_proc
249 ,p_argument => 'link_input_value_id'
250 ,p_argument_value => p_link_input_value_id
251 );
252 End if;
253
254 --
255 -- Get the element_link_id and input_value_id.
256 --
257 Open C_link_input_value_id;
258 Fetch C_link_input_value_id into l_element_link_id, l_input_value_id;
259 If C_link_input_value_id%notfound then
260 Close C_link_input_value_id;
261 --
262 -- The primary key is invalid
263 --
264 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
265 fnd_message.raise_error;
266 End if;
267 Close C_link_input_value_id;
268 --
269 -- Get the lookup code for the default value
270 --
271 Open c_lookup_type(l_input_value_id);
272 Fetch c_lookup_type into l_lookup_type;
273 Close c_lookup_type;
274
275 If l_lookup_type is not null and p_default_value is not null then
276 --
277 Open c_lookup_code(l_lookup_type);
278 Fetch c_lookup_code into l_default_value;
279 Close c_lookup_code;
280 --
281 -- Raise error is lookup validation fails.
282 --
283 If l_default_value is null then
284 fnd_message.set_name('PAY', 'PAY_6171_INPVAL_NO_LOOKUP');
285 fnd_message.raise_error;
286 End if;
287 End if;
288 --
289 -- Call Before Process User Hook
290 --
291 begin
292 PAY_LINK_INPUT_VALUES_BK1.update_link_input_values_b
293 (p_effective_date => l_effective_date
294 ,p_datetrack_update_mode => p_datetrack_update_mode
295 ,p_link_input_value_id => p_link_input_value_id
296 ,p_element_link_id => l_element_link_id
297 ,p_input_value_id => l_input_value_id
298 ,p_costed_flag => p_costed_flag
299 ,p_default_value => l_default_value
300 ,p_max_value => p_max_value
301 ,p_min_value => p_min_value
302 ,p_warning_or_error => p_warning_or_error
303 ,p_object_version_number => l_object_version_number
304 );
305 exception
306 when hr_api.cannot_find_prog_unit then
307 hr_api.cannot_find_prog_unit_error
308 (p_module_name => 'UPDATE_LINK_INPUT_VALUES'
309 ,p_hook_type => 'BP'
310 );
311 end;
312 --
313 -- Validation in addition to Row Handlers
314 --
315 --
316 -- Raise a warning if the input value is a pay basis for the element.
317 --
318 Open c_pay_basis;
319 Fetch c_pay_basis into l_exists;
320 If c_pay_basis%found then
321 p_pay_basis_warning := true;
322 Else
323 p_pay_basis_warning := false;
324 End if;
325 Close c_pay_basis;
326 --
327 -- Process Logic
328 --
329 pay_liv_upd.upd
330 (p_effective_date => l_effective_date
331 ,p_datetrack_mode => p_datetrack_update_mode
332 ,p_element_link_id => l_element_link_id
333 ,p_input_value_id => l_input_value_id
334 ,p_costed_flag => p_costed_flag
335 ,p_default_value => l_default_value
336 ,p_max_value => p_max_value
337 ,p_min_value => p_min_value
338 ,p_warning_or_error => p_warning_or_error
339 ,p_link_input_value_id => p_link_input_value_id
340 ,p_object_version_number => l_object_version_number
341 ,p_effective_start_date => l_effective_start_date
342 ,p_effective_end_date => l_effective_end_date
343 ,p_default_range_warning => l_default_range_warning
347 );
344 ,p_default_formula_warning => l_default_formula_warning
345 ,p_assignment_id_warning => l_assignment_id_warning
346 ,p_formula_message => l_formula_message
348 --
349 -- Call After Process User Hook
350 --
351 begin
352 PAY_LINK_INPUT_VALUES_BK1.update_link_input_values_a
353 (p_effective_date => l_effective_date
354 ,p_datetrack_update_mode => p_datetrack_update_mode
355 ,p_link_input_value_id => p_link_input_value_id
356 ,p_element_link_id => l_element_link_id
357 ,p_input_value_id => l_input_value_id
358 ,p_costed_flag => p_costed_flag
359 ,p_default_value => l_default_value
360 ,p_max_value => p_max_value
361 ,p_min_value => p_min_value
362 ,p_warning_or_error => p_warning_or_error
363 ,p_effective_start_date => l_effective_start_date
364 ,p_effective_end_date => l_effective_end_date
365 ,p_object_version_number => l_object_version_number
366 ,p_pay_basis_warning => p_pay_basis_warning
367 ,p_default_range_warning => l_default_range_warning
368 ,p_default_formula_warning => l_default_formula_warning
369 ,p_assignment_id_warning => l_assignment_id_warning
370 ,p_formula_message => l_formula_message
371 );
372 exception
373 when hr_api.cannot_find_prog_unit then
374 hr_api.cannot_find_prog_unit_error
375 (p_module_name => 'UPDATE_LINK_INPUT_VALUES'
376 ,p_hook_type => 'AP'
377 );
378 end;
379 --
380 -- When in validation only mode raise the Validate_Enabled exception
381 --
382 if p_validate then
383 raise hr_api.validate_enabled;
384 end if;
385 --
386 -- Set all output arguments
387 --
388 p_object_version_number := l_object_version_number;
389 p_effective_start_date := l_effective_start_date;
390 p_effective_end_date := l_effective_end_date;
391 p_default_range_warning := l_default_range_warning;
392 p_default_formula_warning := l_default_formula_warning;
393 p_assignment_id_warning := l_assignment_id_warning;
394 p_formula_message := l_formula_message;
395 --
396 hr_utility.set_location(' Leaving:'||l_proc, 70);
397 exception
398 when hr_api.validate_enabled then
399 --
400 -- As the Validate_Enabled exception has been raised
401 -- we must rollback to the savepoint
402 --
403 rollback to update_link_input_values;
404 --
405 -- Only set output warning arguments
406 -- (Any key or derived arguments must be set to null
407 -- when validation only mode is being used.)
408 --
409 p_object_version_number := p_object_version_number;
410 p_effective_start_date := null;
411 p_effective_end_date := null;
412
413 hr_utility.set_location(' Leaving:'||l_proc, 80);
414 when others then
415 --
416 -- A validation or unexpected error has occured
417 --
418 rollback to update_link_input_values;
419 p_object_version_number := p_object_version_number;
420 p_effective_start_date := null;
421 p_effective_end_date := null;
422 p_pay_basis_warning := null;
423 p_default_range_warning := null;
424 p_default_formula_warning := null;
425 hr_utility.set_location(' Leaving:'||l_proc, 90);
426 raise;
427
428 end UPDATE_LINK_INPUT_VALUES;
429 --
430 --
431 -- ----------------------------------------------------------------------------
432 -- |------------------------< delete_link_input_values >----------------------|
433 -- ----------------------------------------------------------------------------
434 --
435 procedure DELETE_LINK_INPUT_VALUES
436 (p_validate in boolean
437 ,p_effective_date in date
438 ,p_datetrack_delete_mode in varchar2
439 ,p_link_input_value_id in number
440 ,p_effective_start_date out nocopy date
441 ,p_effective_end_date out nocopy date
442 ,p_object_version_number in out nocopy number
443 ) is
444 --
445 -- Declare cursors and local variables
446 --
447
448 l_proc varchar2(72) := g_package||'delete_liv';
449 l_effective_date date;
450 l_effective_end_date date;
451 l_effective_start_date date;
452 l_object_version_number pay_link_input_values_f.object_version_number%type;
453
454 begin
455 hr_utility.set_location('Entering:'|| l_proc, 10);
456 --
457 -- Issue a savepoint
458 --
459 savepoint delete_link_input_values;
460 --
461 -- Truncate the time portion from all IN date parameters
462 --
463 l_effective_date := trunc(p_effective_date);
464 --
465 -- Initialize all IN/OUT parameters
466 --
467 l_object_version_number := p_object_version_number;
468 --
469 -- Call Before Process User Hook
470 --
471 begin
472 PAY_LINK_INPUT_VALUES_BK2.delete_link_input_values_b
473 (p_effective_date => l_effective_date
474 ,p_datetrack_delete_mode => p_datetrack_delete_mode
475 ,p_link_input_value_id => p_link_input_value_id
476 ,p_object_version_number => l_object_version_number
477 );
478 exception
479 when hr_api.cannot_find_prog_unit then
480 hr_api.cannot_find_prog_unit_error
481 (p_module_name => 'DELETE_LINK_INPUT_VALUES'
482 ,p_hook_type => 'BP'
483 );
484 end;
485 --
486 -- Validation in addition to Row Handlers
487 --
488 -- An error is raised if the datetrack_delete_mode is either 'ZAP' or
489 -- 'DELETE' when this API is executed as a stand alone procedure.
490 --
491 If p_datetrack_delete_mode in ('ZAP','DELETE') then
492 fnd_message.set_name('PAY','HR_7098_INPVAL_NO_CHANGES');
493 fnd_message.raise_error;
494 End if;
495 --
496 -- Process Logic
497 --
498 pay_liv_del.del
499 (p_effective_date => l_effective_date
500 ,p_datetrack_mode => p_datetrack_delete_mode
501 ,p_link_input_value_id => p_link_input_value_id
502 ,p_object_version_number => l_object_version_number
503 ,p_effective_start_date => l_effective_start_date
504 ,p_effective_end_date => l_effective_end_date
505 );
506 --
507 -- Call After Process User Hook
508 --
509 begin
510 PAY_LINK_INPUT_VALUES_BK2.delete_link_input_values_a
511 (p_effective_date => p_effective_date
512 ,p_datetrack_delete_mode => p_datetrack_delete_mode
513 ,p_link_input_value_id => p_link_input_value_id
514 ,p_object_version_number => l_object_version_number
515 ,p_effective_start_date => l_effective_start_date
516 ,p_effective_end_date => l_effective_end_date
517 );
518 exception
519 when hr_api.cannot_find_prog_unit then
520 hr_api.cannot_find_prog_unit_error
521 (p_module_name => 'DELETE_LINK_INPUT_VALUES'
522 ,p_hook_type => 'AP'
523 );
524 end;
525 --
526 -- When in validation only mode raise the Validate_Enabled exception
527 --
528 if p_validate then
529 raise hr_api.validate_enabled;
530 end if;
531 --
532 -- Set all output arguments
533 --
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 hr_utility.set_location(' Leaving:'||l_proc, 70);
539 exception
540 when hr_api.validate_enabled then
541 --
542 -- As the Validate_Enabled exception has been raised
543 -- we must rollback to the savepoint
544 --
545 rollback to delete_link_input_values;
546 --
547 -- Only set output warning arguments
548 -- (Any key or derived arguments must be set to null
549 -- when validation only mode is being used.)
550 --
551 p_effective_start_date := null;
552 p_effective_end_date := null;
553 p_object_version_number := p_object_version_number;
554 hr_utility.set_location(' Leaving:'||l_proc, 80);
555 when others then
556 --
557 -- A validation or unexpected error has occured
558 --
559 rollback to delete_link_input_values;
560 p_effective_start_date := null;
561 p_effective_end_date := null;
562 p_object_version_number := p_object_version_number;
563 hr_utility.set_location(' Leaving:'||l_proc, 90);
564 raise;
565 end DELETE_LINK_INPUT_VALUES;
566
567 end PAY_LINK_INPUT_VALUES_API;