[Home] [Help]
PACKAGE BODY: APPS.PAY_INPUT_VALUE_API
Source
1 Package Body PAY_INPUT_VALUE_API as
2 /* $Header: pyivlapi.pkb 115.3 2003/10/08 05:30:08 swinton noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PAY_INPUT_VALUE_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< CREATE_INPUT_VALUE >--------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure CREATE_INPUT_VALUE
13 (P_VALIDATE in boolean Default false
14 ,P_EFFECTIVE_DATE in date
15 ,P_ELEMENT_TYPE_ID in number
16 ,P_NAME in varchar2
17 ,P_UOM in varchar2
18 ,P_LOOKUP_TYPE in varchar2 Default Null
19 ,P_FORMULA_ID in number Default Null
20 ,P_VALUE_SET_ID in number Default Null
21 ,P_DISPLAY_SEQUENCE in number Default Null
22 ,P_GENERATE_DB_ITEMS_FLAG in varchar2 Default 'N'
23 ,P_HOT_DEFAULT_FLAG in varchar2 Default 'N'
24 ,P_MANDATORY_FLAG in varchar2 Default 'N'
25 ,P_DEFAULT_VALUE in varchar2 Default Null
26 ,P_MAX_VALUE in varchar2 Default Null
27 ,P_MIN_VALUE in varchar2 Default Null
28 ,P_WARNING_OR_ERROR in varchar2 Default Null
29 ,P_INPUT_VALUE_ID OUT NOCOPY number
30 ,P_OBJECT_VERSION_NUMBER OUT NOCOPY number
31 ,P_EFFECTIVE_START_DATE OUT NOCOPY date
32 ,P_EFFECTIVE_END_DATE OUT NOCOPY date
33 ,P_DEFAULT_VAL_WARNING OUT NOCOPY boolean
34 ,P_MIN_MAX_WARNING OUT NOCOPY boolean
35 ,P_PAY_BASIS_WARNING OUT NOCOPY boolean
36 ,P_FORMULA_WARNING OUT NOCOPY boolean
37 ,P_ASSIGNMENT_ID_WARNING OUT NOCOPY boolean
38 ,P_FORMULA_MESSAGE OUT NOCOPY varchar2
39 ) is
40 --
41 -- Declare cursors and local variables
42 --
43
44 cursor csr_derived_values
45 is
46 select business_group_id,legislation_code,legislation_subgroup
47 from pay_element_types_f
48 where element_type_id = p_element_type_id;
49
50
51 l_business_group_id pay_input_values_f.business_group_id%type;
52 l_legislation_code pay_input_values_f.legislation_code%type;
53 l_legislation_subgroup pay_input_values_f.legislation_subgroup%type;
54
55
56 l_proc varchar2(72) := g_package||'CREATE_INPUT_VALUE';
57 begin
58 hr_utility.set_location('Entering:'|| l_proc, 10);
59 --
60 -- Issue a savepoint
61 --
62 savepoint CREATE_INPUT_VALUE;
63 --
64 -- Call Before Process User Hook
65 --
66
67 open csr_derived_values;
68 fetch csr_derived_values into l_business_group_id,l_legislation_code,l_legislation_subgroup;
69 close csr_derived_values;
70
71 begin
72 PAY_INPUT_VALUE_bk1.CREATE_INPUT_VALUE_b
73 (P_VALIDATE
74 ,trunc(P_EFFECTIVE_DATE)
75 ,P_ELEMENT_TYPE_ID
76 ,L_BUSINESS_GROUP_ID
77 ,P_NAME
78 ,P_UOM
79 ,P_LOOKUP_TYPE
80 ,L_LEGISLATION_CODE
81 ,P_FORMULA_ID
82 ,P_VALUE_SET_ID
83 ,P_DISPLAY_SEQUENCE
84 ,P_GENERATE_DB_ITEMS_FLAG
85 ,P_HOT_DEFAULT_FLAG
86 ,P_MANDATORY_FLAG
87 ,P_DEFAULT_VALUE
88 ,L_LEGISLATION_SUBGROUP
89 ,P_MAX_VALUE
90 ,P_MIN_VALUE
91 ,P_WARNING_OR_ERROR
92 );
93 exception
94 when hr_api.cannot_find_prog_unit then
95 hr_api.cannot_find_prog_unit_error
96 (p_module_name => 'CREATE_INPUT_VALUE'
97 ,p_hook_type => 'BP'
98 );
99 end;
100 --
101 -- Validation in addition to Row Handlers
102 --
103 --
104 -- Process Logic
105 --
106
107 pay_ivl_ins.ins(
108 p_effective_date => trunc(p_effective_date)
109 ,p_element_type_id => p_element_type_id
110 ,p_display_sequence => p_display_sequence
111 ,p_generate_db_items_flag => p_generate_db_items_flag
112 ,p_hot_default_flag => p_hot_default_flag
113 ,p_mandatory_flag => p_mandatory_flag
114 ,p_name => p_name
115 ,p_uom => p_uom
116 ,p_lookup_type => p_lookup_type
117 ,p_business_group_id => l_business_group_id
118 ,p_legislation_code => l_legislation_code
119 ,p_formula_id => p_formula_id
120 ,p_value_set_id => p_value_set_id
121 ,p_default_value => p_default_value
122 ,p_legislation_subgroup => l_legislation_subgroup
123 ,p_max_value => p_max_value
124 ,p_min_value => p_min_value
125 ,p_warning_or_error => p_warning_or_error
126 ,p_input_value_id => p_input_value_id
127 ,p_object_version_number => p_object_version_number
128 ,p_effective_start_date => p_effective_start_date
129 ,p_effective_end_date => p_effective_end_date
130 ,p_default_val_warning => p_default_val_warning
131 ,p_min_max_warning => p_min_max_warning
132 ,p_pay_basis_warning => p_pay_basis_warning
133 ,p_formula_warning => p_formula_warning
134 ,p_assignment_id_warning => p_assignment_id_warning
135 ,p_formula_message => p_formula_message
136 );
137
138 --
139 -- Call After Process User Hook
140 --
141 begin
142 PAY_INPUT_VALUE_bk1.CREATE_INPUT_VALUE_a
143 ( P_VALIDATE
144 ,trunc(P_EFFECTIVE_DATE)
145 ,P_ELEMENT_TYPE_ID
146 ,L_BUSINESS_GROUP_ID
147 ,P_NAME
148 ,P_UOM
149 ,P_LOOKUP_TYPE
150 ,L_LEGISLATION_CODE
151 ,P_FORMULA_ID
152 ,P_VALUE_SET_ID
153 ,P_DISPLAY_SEQUENCE
154 ,P_GENERATE_DB_ITEMS_FLAG
155 ,P_HOT_DEFAULT_FLAG
156 ,P_MANDATORY_FLAG
157 ,P_DEFAULT_VALUE
158 ,L_LEGISLATION_SUBGROUP
159 ,P_MAX_VALUE
160 ,P_MIN_VALUE
161 ,P_WARNING_OR_ERROR
162 ,P_INPUT_VALUE_ID
163 ,P_OBJECT_VERSION_NUMBER
164 ,P_EFFECTIVE_START_DATE
165 ,P_EFFECTIVE_END_DATE
166 ,P_DEFAULT_VAL_WARNING
167 ,P_MIN_MAX_WARNING
168 ,P_PAY_BASIS_WARNING
169 ,P_FORMULA_WARNING
170 ,P_ASSIGNMENT_ID_WARNING
171 ,P_FORMULA_MESSAGE
172 );
173 exception
174 when hr_api.cannot_find_prog_unit then
175 hr_api.cannot_find_prog_unit_error
176 (p_module_name => 'CREATE_INPUT_VALUE'
177 ,p_hook_type => 'AP'
178 );
179 end;
180 --
181 -- When in validation only mode raise the Validate_Enabled exception
182 --
183 if p_validate then
184 raise hr_api.validate_enabled;
185 end if;
186 --
187 -- Set all output arguments
188 --
189 p_input_value_id := p_input_value_id;
190 p_object_version_number := p_object_version_number;
191
192 --
193 hr_utility.set_location(' Leaving:'||l_proc, 70);
194 exception
195 when hr_api.validate_enabled then
196 --
197 -- As the Validate_Enabled exception has been raised
198 -- we must rollback to the savepoint
199 --
200 rollback to CREATE_INPUT_VALUE;
201 --
202 -- Only set output warning arguments
203 -- (Any key or derived arguments must be set to null
204 -- when validation only mode is being used.)
205 --
206 p_input_value_id := null;
207 p_object_version_number := null;
208 p_default_val_warning := null;
209 p_min_max_warning := null;
210 p_pay_basis_warning := null;
211 p_formula_warning := null;
212 p_assignment_id_warning := null;
213 p_formula_message := null;
214
215 hr_utility.set_location(' Leaving:'||l_proc, 80);
216 when others then
217 --
218 -- A validation or unexpected error has occured
219 --
220 rollback to CREATE_INPUT_VALUE;
221 hr_utility.set_location(' Leaving:'||l_proc, 90);
222 raise;
223 end CREATE_INPUT_VALUE;
224 --
225 --
226 -- ----------------------------------------------------------------------------
227 -- |--------------------------< UPDATE_INPUT_VALUE >--------------------------|
228 -- ----------------------------------------------------------------------------
229 --
230 procedure UPDATE_INPUT_VALUE
231 ( P_VALIDATE IN boolean Default false
232 ,P_EFFECTIVE_DATE IN date
233 ,P_DATETRACK_MODE IN varchar2
234 ,P_INPUT_VALUE_ID IN number
235 ,P_OBJECT_VERSION_NUMBER IN OUT NOCOPY number
236 ,P_NAME IN varchar2 Default hr_api.g_varchar2
237 ,P_UOM IN varchar2 Default hr_api.g_varchar2
238 ,P_LOOKUP_TYPE IN varchar2 Default hr_api.g_varchar2
239 ,P_FORMULA_ID IN number Default hr_api.g_number
240 ,P_VALUE_SET_ID IN number Default hr_api.g_number
241 ,P_DISPLAY_SEQUENCE IN number Default hr_api.g_number
242 ,P_GENERATE_DB_ITEMS_FLAG IN varchar2 Default hr_api.g_varchar2
243 ,P_HOT_DEFAULT_FLAG IN varchar2 Default hr_api.g_varchar2
244 ,P_MANDATORY_FLAG IN varchar2 Default hr_api.g_varchar2
245 ,P_DEFAULT_VALUE IN varchar2 Default hr_api.g_varchar2
246 ,P_MAX_VALUE IN varchar2 Default hr_api.g_varchar2
247 ,P_MIN_VALUE IN varchar2 Default hr_api.g_varchar2
248 ,P_WARNING_OR_ERROR IN varchar2 Default hr_api.g_varchar2
249 ,P_EFFECTIVE_START_DATE OUT NOCOPY date
250 ,P_EFFECTIVE_END_DATE OUT NOCOPY date
251 ,P_DEFAULT_VAL_WARNING OUT NOCOPY boolean
252 ,P_MIN_MAX_WARNING OUT NOCOPY boolean
253 ,P_LINK_INP_VAL_WARNING OUT NOCOPY boolean
254 ,P_PAY_BASIS_WARNING OUT NOCOPY boolean
255 ,P_FORMULA_WARNING OUT NOCOPY boolean
256 ,P_ASSIGNMENT_ID_WARNING OUT NOCOPY boolean
257 ,P_FORMULA_MESSAGE OUT NOCOPY varchar2
258 ) is
259 --
260 -- Declare cursors and local variables
261 --
262 cursor csr_derived_values
263 is
264 select business_group_id,legislation_code,element_type_id,legislation_subgroup
265 from pay_input_values_f
266 where input_value_id = p_input_value_id;
267
268
269 l_element_type_id pay_input_values_f.element_type_id%type;
270 l_business_group_id pay_input_values_f.business_group_id%type;
271 l_legislation_code pay_input_values_f.legislation_code%type;
272 l_legislation_subgroup pay_input_values_f.legislation_subgroup%type;
273
274 l_proc varchar2(72) := g_package||'UPDATE_INPUT_VALUE';
275 begin
276 hr_utility.set_location('Entering:'|| l_proc, 10);
277 --
278 -- Issue a savepoint
279 --
280 savepoint UPDATE_INPUT_VALUE;
281 --
282 -- Call Before Process User Hook
283 --
284 begin
285 PAY_INPUT_VALUE_bk2.UPDATE_INPUT_VALUE_b
286 (P_VALIDATE
287 ,trunc(P_EFFECTIVE_DATE)
288 ,P_DATETRACK_MODE
289 ,P_INPUT_VALUE_ID
290 ,P_OBJECT_VERSION_NUMBER
291 ,P_NAME
292 ,P_UOM
293 ,P_LOOKUP_TYPE
294 ,P_FORMULA_ID
295 ,P_VALUE_SET_ID
296 ,P_DISPLAY_SEQUENCE
297 ,P_GENERATE_DB_ITEMS_FLAG
298 ,P_HOT_DEFAULT_FLAG
299 ,P_MANDATORY_FLAG
300 ,P_DEFAULT_VALUE
301 ,P_MAX_VALUE
302 ,P_MIN_VALUE
303 ,P_WARNING_OR_ERROR
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_INPUT_VALUE'
309 ,p_hook_type => 'BP'
310 );
311 end;
312 --
313 -- Validation in addition to Row Handlers
314 --
315 open csr_derived_values;
316 fetch csr_derived_values into l_business_group_id,l_legislation_code,l_element_type_id,l_legislation_subgroup;
317 close csr_derived_values;
318 --
319 -- Process Logic
320 --
321
322 pay_ivl_upd.upd
323 (p_effective_date => trunc(p_effective_date)
324 ,p_datetrack_mode => p_datetrack_mode
325 ,p_input_value_id => p_input_value_id
326 ,p_object_version_number => p_object_version_number
327 ,p_element_type_id => l_element_type_id
328 ,p_display_sequence => p_display_sequence
329 ,p_generate_db_items_flag => p_generate_db_items_flag
330 ,p_hot_default_flag => p_hot_default_flag
331 ,p_mandatory_flag => p_mandatory_flag
332 ,p_name => p_name
333 ,p_uom => p_uom
334 ,p_lookup_type => p_lookup_type
335 ,p_business_group_id => l_business_group_id
336 ,p_legislation_code => l_legislation_code
337 ,p_formula_id => p_formula_id
338 ,p_value_set_id => p_value_set_id
339 ,p_default_value => p_default_value
340 ,p_legislation_subgroup => l_legislation_subgroup
341 ,p_max_value => p_max_value
342 ,p_min_value => p_min_value
343 ,p_warning_or_error => p_warning_or_error
344 ,p_effective_start_date => p_effective_start_date
345 ,p_effective_end_date => p_effective_end_date
346 ,p_default_val_warning => p_default_val_warning
347 ,p_min_max_warning => p_min_max_warning
348 ,p_link_inp_val_warning => p_link_inp_val_warning
349 ,p_pay_basis_warning => p_pay_basis_warning
350 ,p_formula_warning => p_formula_warning
351 ,p_assignment_id_warning => p_assignment_id_warning
352 ,p_formula_message => p_formula_message
353 );
354
355 --
356 -- Call After Process User Hook
357 --
361 ,trunc(P_EFFECTIVE_DATE)
358 begin
359 PAY_INPUT_VALUE_bk2.UPDATE_INPUT_VALUE_a
360 (P_VALIDATE
362 ,P_DATETRACK_MODE
363 ,P_INPUT_VALUE_ID
364 ,P_OBJECT_VERSION_NUMBER
365 ,P_NAME
366 ,P_UOM
367 ,P_LOOKUP_TYPE
368 ,P_FORMULA_ID
369 ,P_VALUE_SET_ID
370 ,P_DISPLAY_SEQUENCE
371 ,P_GENERATE_DB_ITEMS_FLAG
372 ,P_HOT_DEFAULT_FLAG
373 ,P_MANDATORY_FLAG
374 ,P_DEFAULT_VALUE
375 ,P_MAX_VALUE
376 ,P_MIN_VALUE
377 ,P_WARNING_OR_ERROR
378 ,P_EFFECTIVE_START_DATE
379 ,P_EFFECTIVE_END_DATE
380 ,P_DEFAULT_VAL_WARNING
381 ,P_MIN_MAX_WARNING
382 ,P_LINK_INP_VAL_WARNING
383 ,P_PAY_BASIS_WARNING
384 ,P_FORMULA_WARNING
385 ,P_ASSIGNMENT_ID_WARNING
386 ,P_FORMULA_MESSAGE
387 );
388 exception
389 when hr_api.cannot_find_prog_unit then
390 hr_api.cannot_find_prog_unit_error
391 (p_module_name => 'UPDATE_INPUT_VALUE'
392 ,p_hook_type => 'AP'
393 );
394 end;
395 --
396 -- When in validation only mode raise the Validate_Enabled exception
397 --
398 if p_validate then
399 raise hr_api.validate_enabled;
400 end if;
401 --
402 -- Set all output arguments
403 --
404 p_object_version_number := null;
405 p_default_val_warning := null;
406 p_min_max_warning := null;
407 p_link_inp_val_warning := null;
408 p_pay_basis_warning := null;
409 p_formula_warning := null;
410 p_assignment_id_warning := null;
411 p_formula_message := null;
412 --
413 hr_utility.set_location(' Leaving:'||l_proc, 70);
414 exception
415 when hr_api.validate_enabled then
416 --
417 -- As the Validate_Enabled exception has been raised
418 -- we must rollback to the savepoint
419 --
420 rollback to UPDATE_INPUT_VALUE;
421 --
422 -- Only set output warning arguments
423 -- (Any key or derived arguments must be set to null
424 -- when validation only mode is being used.)
425 --
426 p_object_version_number := p_object_version_number;
427 hr_utility.set_location(' Leaving:'||l_proc, 80);
428 when others then
429 --
430 -- A validation or unexpected error has occured
431 --
432 rollback to UPDATE_INPUT_VALUE;
433 hr_utility.set_location(' Leaving:'||l_proc, 90);
434 raise;
435 end UPDATE_INPUT_VALUE;
436 --
437 --
438 --
439 -- ----------------------------------------------------------------------------
440 -- |--------------------------< DELETE_INPUT_VALUE >--------------------------|
441 -- ----------------------------------------------------------------------------
442 --
443 procedure DELETE_INPUT_VALUE
444 ( P_VALIDATE IN boolean default false
445 ,P_EFFECTIVE_DATE IN date
446 ,P_DATETRACK_DELETE_MODE IN varchar2
447 ,P_INPUT_VALUE_ID IN number
448 ,P_OBJECT_VERSION_NUMBER IN OUT NOCOPY number
449 ,P_EFFECTIVE_START_DATE OUT NOCOPY date
450 ,P_EFFECTIVE_END_DATE OUT NOCOPY date
451 ,P_BALANCE_FEEDS_WARNING OUT NOCOPY boolean
452 ) is
453 --
454 -- Declare cursors and local variables
455 --
456
457 l_proc varchar2(72) := g_package||'DELETE_INPUT_VALUE';
458 begin
459 hr_utility.set_location('Entering:'|| l_proc, 10);
460 --
461 -- Issue a savepoint
462 --
463 savepoint DELETE_INPUT_VALUE;
464 --
465 -- Call Before Process User Hook
466 --
467 begin
468 PAY_INPUT_VALUE_bk3.DELETE_INPUT_VALUE_b
469 (P_VALIDATE
470 ,trunc(P_EFFECTIVE_DATE)
471 ,P_DATETRACK_DELETE_MODE
472 ,P_INPUT_VALUE_ID
476 when hr_api.cannot_find_prog_unit then
473 ,P_OBJECT_VERSION_NUMBER
474 );
475 exception
477 hr_api.cannot_find_prog_unit_error
478 (p_module_name => 'DELETE_INPUT_VALUE'
479 ,p_hook_type => 'BP'
480 );
481 end;
482 --
483 -- Validation in addition to Row Handlers
484 --
485 if P_DATETRACK_DELETE_MODE = 'DELETE' then
486 hr_utility.set_message(801,'HR_7098_INPVAL_NO_CHANGES');
487 hr_utility.raise_error;
488 end if;
489
490
491
492 --
493 -- Process Logic
494 --
495
496 pay_ivl_del.del
497 (p_effective_date => trunc(p_effective_date)
498 ,p_datetrack_mode => p_datetrack_delete_mode
499 ,p_input_value_id => p_input_value_id
500 ,p_object_version_number => p_object_version_number
501 ,p_effective_start_date => p_effective_start_date
502 ,p_effective_end_date => p_effective_end_date
503 ,p_balance_feeds_warning => p_balance_feeds_warning);
504
505 --
506 -- Call After Process User Hook
507 --
508 begin
509 PAY_INPUT_VALUE_bk3.DELETE_INPUT_VALUE_a
510 ( P_VALIDATE
511 ,trunc(P_EFFECTIVE_DATE)
512 ,P_DATETRACK_DELETE_MODE
513 ,P_INPUT_VALUE_ID
514 ,P_OBJECT_VERSION_NUMBER
515 ,P_EFFECTIVE_START_DATE
516 ,P_EFFECTIVE_END_DATE
517 ,p_balance_feeds_warning
518 );
519 exception
520 when hr_api.cannot_find_prog_unit then
521 hr_api.cannot_find_prog_unit_error
522 (p_module_name => 'DELETE_INPUT_VALUE'
523 ,p_hook_type => 'AP'
524 );
525 end;
526 --
527 -- When in validation only mode raise the Validate_Enabled exception
528 --
529 if p_validate then
530 raise hr_api.validate_enabled;
531 end if;
532 --
533 -- Set all output arguments
534 --
535 p_object_version_number := p_object_version_number;
536 --
537 hr_utility.set_location(' Leaving:'||l_proc, 70);
538 exception
539 when hr_api.validate_enabled then
540 --
541 -- As the Validate_Enabled exception has been raised
542 -- we must rollback to the savepoint
543 --
544 rollback to DELETE_INPUT_VALUE;
545 --
546 -- Only set output warning arguments
547 -- (Any key or derived arguments must be set to null
548 -- when validation only mode is being used.)
549 --
550 p_object_version_number := null;
551 p_balance_feeds_warning := null;
552
553 hr_utility.set_location(' Leaving:'||l_proc, 80);
554 when others then
555 --
556 -- A validation or unexpected error has occured
557 --
558 rollback to DELETE_INPUT_VALUE;
559 hr_utility.set_location(' Leaving:'||l_proc, 90);
560 raise;
561 end DELETE_INPUT_VALUE;
562 --
563 end PAY_INPUT_VALUE_API;