[Home] [Help]
PACKAGE BODY: APPS.PAY_EVENT_QUALIFIERS_API
Source
1 Package Body pay_event_qualifiers_api as
2 /* $Header: pyevqapi.pkb 120.0 2005/05/29 04:49:16 appldev noship $ */
3 --
4 -- Package Variables
5 --
6
7 g_package varchar2(33) := ' pay_event_qualifiers_api.';
8 --
9 -- ----------------------------------------------------------------------------
10 -- |-----------------------< create_event_qualifier> >----------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 procedure create_event_qualifier
14 (p_validate in boolean default false
15 ,p_effective_date in date
16 ,p_dated_table_id in number
17 ,p_column_name in varchar2
18 ,p_qualifier_name in varchar2
19 ,p_legislation_code in varchar2 default null
20 ,p_business_group_id in number default null
21 ,p_comparison_column in varchar2 default null
22 ,p_qualifier_definition in varchar2 default null
23 ,p_qualifier_where_clause in varchar2 default null
24 ,p_entry_qualification in varchar2 default null
25 ,p_assignment_qualification in varchar2 default null
26 ,p_multi_event_sql in varchar2 default null
27 ,p_event_qualifier_id out nocopy number
28 ,p_object_version_number out nocopy number
29 ,p_effective_start_date out nocopy date
30 ,p_effective_end_date out nocopy date
31 ) is
32 --
33 -- Declare cursors and local variables
34 --
35
36 l_proc varchar2(72) := g_package||'create_event_qualifier';
37 l_effective_date date;
38 l_current number;
39 --
40 -- Declare OUT variables
41 --
42 l_event_qualifier_id pay_event_qualifiers_f.event_qualifier_id%TYPE;
43 l_object_version_number pay_event_qualifiers_f.object_version_number%TYPE;
44 l_effective_start_date pay_event_qualifiers_f.effective_start_date%TYPE;
45 l_effective_end_date pay_event_qualifiers_f.effective_end_date%TYPE;
46
47 cursor csr_valid_name is
48 select event_qualifier_id
49 , effective_start_date
50 , effective_end_date
51 , object_version_number
52 from pay_event_qualifiers_f
53 where dated_table_id = p_dated_table_id
54 and column_name = p_column_name
55 and qualifier_name = p_qualifier_name;
56
57 begin
58 hr_utility.set_location('Entering:'|| l_proc, 10);
59 --
60 -- Issue a savepoint
61 --
62 savepoint create_event_qualifier;
63 --
64 -- Truncate the time portion from all IN date parameters
65 --
66 l_effective_date := trunc(p_effective_date);
67 hr_utility.set_location(l_proc, 20);
68
69
70 --------------------------------------------------------
71 -- Check for unique qualifier name,table_id,column_name
72 --------------------------------------------------------
73 open csr_valid_name;
74 fetch csr_valid_name
75 into l_event_qualifier_id
76 , l_effective_start_date
77 , l_effective_end_date
78 , l_object_version_number;
79
80 IF csr_valid_name%notfound then
81 -- The Qualifier we are trying to create does not yet exist
82 -- so create now.
83
84 --------------------------------------------------------
85 -- NB. At this point only one qualifier IN USE on a datetracked event
86 -- is supported, but we'll allow creation of the qual, but raise
87 -- an error in the evc api if insert is performed via UI
88 --------------------------------------------------------
89
90 --
91 -- Call Before Process User Hook
92 --
93 begin
94 pay_event_qualifiers_bk1.create_event_qualifier_b
95 (p_effective_date => l_effective_date
96 ,p_dated_table_id => p_dated_table_id
97 ,p_column_name => p_column_name
98 ,p_qualifier_name => p_qualifier_name
99 ,p_legislation_code => p_legislation_code
100 ,p_business_group_id => p_business_group_id
101 ,p_comparison_column => p_comparison_column
102 ,p_qualifier_definition => p_qualifier_definition
103 ,p_qualifier_where_clause => p_qualifier_where_clause
104 ,p_entry_qualification => p_entry_qualification
105 ,p_assignment_qualification => p_assignment_qualification
106 ,p_multi_event_sql => p_multi_event_sql
107 );
108
109 exception
110 when hr_api.cannot_find_prog_unit then
111 hr_api.cannot_find_prog_unit_error
112 (p_module_name => 'create_event_qualifier'
113 ,p_hook_type => 'BP'
114 );
115 end;
116
117 hr_utility.set_location(l_proc, 30);
118 --
119 -- Validation in addition to Row Handlers
120 --
121 hr_utility.set_location(l_proc, 40);
122
123
124 --
125 -- Process Logic
126 --
127 --
128 -- Call the row handler
129 --
130 pay_evq_ins.ins
131 (p_effective_date => l_effective_date
132 ,p_dated_table_id => p_dated_table_id
133 ,p_column_name => p_column_name
134 ,p_qualifier_name => p_qualifier_name
135 ,p_legislation_code => p_legislation_code
136 ,p_business_group_id => p_business_group_id
137 ,p_comparison_column => p_comparison_column
138 ,p_qualifier_definition => p_qualifier_definition
139 ,p_qualifier_where_clause => p_qualifier_where_clause
140 ,p_entry_qualification => p_entry_qualification
141 ,p_assignment_qualification => p_assignment_qualification
142 ,p_multi_event_sql => p_multi_event_sql
143 ,p_event_qualifier_id => l_event_qualifier_id
144 ,p_object_version_number => l_object_version_number
145 ,p_effective_start_date => l_effective_start_date
146 ,p_effective_end_date => l_effective_end_date
147 );
148 --
149 hr_utility.set_location(l_proc, 50);
150
151 --
152 -- Call After Process User Hook
153 --
154 begin
155 pay_event_qualifiers_bk1.create_event_qualifier_a
156 (p_effective_date => l_effective_date
157 ,p_dated_table_id => p_dated_table_id
158 ,p_column_name => p_column_name
159 ,p_qualifier_name => p_qualifier_name
160 ,p_legislation_code => p_legislation_code
161 ,p_business_group_id => p_business_group_id
162 ,p_comparison_column => p_comparison_column
163 ,p_qualifier_definition => p_qualifier_definition
164 ,p_qualifier_where_clause => p_qualifier_where_clause
165 ,p_entry_qualification => p_entry_qualification
166 ,p_assignment_qualification => p_assignment_qualification
167 ,p_multi_event_sql => p_multi_event_sql
168 ,p_event_qualifier_id => l_event_qualifier_id
169 ,p_object_version_number => l_object_version_number
170 ,p_effective_start_date => l_effective_start_date
171 ,p_effective_end_date => l_effective_end_date
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_event_qualifier'
177 ,p_hook_type => 'AP'
178 );
179 end;
180
181 END IF; -- Only done insert and hooks if %notfound
182 close csr_valid_name;
183 hr_utility.set_location(l_proc, 60);
184
185 --
186 -- When in validation only mode raise the Validate_Enabled exception
187 --
188 if p_validate then
189 raise hr_api.validate_enabled;
190 end if;
191 --
192 -- Set all output arguments
193 --
194 p_event_qualifier_id := l_event_qualifier_id;
195 p_effective_start_date := l_effective_start_date;
196 p_effective_end_date := l_effective_end_date;
197 p_object_version_number := l_object_version_number;
198 --
199 hr_utility.set_location(' Leaving:'||l_proc, 70);
200 exception
201 when hr_api.validate_enabled then
202 --
203 -- As the Validate_Enabled exception has been raised
204 -- we must rollback to the savepoint
205 --
206 rollback to create_event_qualifier;
207 --
208 -- Only set output warning arguments
209 -- (Any key or derived arguments must be set to null
210 -- when validation only mode is being used.)
211 --
212 p_event_qualifier_id := null;
213 p_effective_start_date := null;
214 p_effective_end_date := null;
215 p_object_version_number := null;
216 hr_utility.set_location(' Leaving:'||l_proc, 80);
217 when others then
218 --
219 -- A validation or unexpected error has occured
220 --
221 rollback to create_event_qualifier;
222 p_event_qualifier_id := null;
223 p_effective_start_date := null;
224 p_effective_end_date := null;
225 p_object_version_number := null;
226 hr_utility.set_location(' Leaving:'||l_proc, 90);
227 raise;
228 end create_event_qualifier;
229 --
230
231 --
232 -- ----------------------------------------------------------------------------
233 -- |----------------------< update_event_qualifier >-------------------------|
234 -- ----------------------------------------------------------------------------
235 --
236 procedure update_event_qualifier
237 (p_validate in boolean default false
238 ,p_effective_date in date
239 ,p_datetrack_update_mode in varchar2
240 ,p_event_qualifier_id in number
241 ,p_dated_table_id in number
242 ,p_column_name in varchar2
243 ,p_qualifier_name in varchar2
244 ,p_object_version_number in out nocopy number
245 ,p_legislation_code in varchar2 default hr_api.g_varchar2
246 ,p_business_group_id in number default hr_api.g_number
247 ,p_comparison_column in varchar2 default hr_api.g_varchar2
248 ,p_qualifier_definition in varchar2 default hr_api.g_varchar2
249 ,p_qualifier_where_clause in varchar2 default hr_api.g_varchar2
250 ,p_entry_qualification in varchar2 default hr_api.g_varchar2
251 ,p_assignment_qualification in varchar2 default hr_api.g_varchar2
255 ) is
252 ,p_multi_event_sql in varchar2 default hr_api.g_varchar2
253 ,p_effective_start_date out nocopy date
254 ,p_effective_end_date out nocopy date
256 --
257 -- Declare cursors and local variables
258 --
259
260 l_proc varchar2(72) := g_package||'update_event_qualifier';
261 l_effective_date date;
262 --
263 -- Declare OUT variables
264 --
265 l_effective_start_date pay_event_qualifiers_f.effective_start_date%TYPE;
266 l_effective_end_date pay_event_qualifiers_f.effective_end_date%TYPE;
267 --
268 -- Declare IN OUT variable
269 --
270 l_object_version_number pay_event_qualifiers_f.object_version_number%TYPE;
271 --
272 begin
273 hr_utility.set_location('Entering:'|| l_proc, 10);
274 --
275 -- Issue a savepoint and assign in-out parameters to local variable
276 --
277
278 l_object_version_number := p_object_version_number;
279 savepoint update_event_qualifier;
280 --
281 --
282 -- Truncate the time portion from all IN date parameters
283 --
284 l_effective_date := trunc(p_effective_date);
285 hr_utility.set_location(l_proc, 20);
286 --
287 -- Call Before Process User Hook
288 --
289 begin
290 pay_event_qualifiers_bk2.update_event_qualifier_b
291 (p_effective_date => l_effective_date
292 ,p_datetrack_update_mode => p_datetrack_update_mode
293 ,p_event_qualifier_id => p_event_qualifier_id
294 ,p_dated_table_id => p_dated_table_id
295 ,p_column_name => p_column_name
296 ,p_qualifier_name => p_qualifier_name
297 ,p_legislation_code => p_legislation_code
298 ,p_business_group_id => p_business_group_id
299 ,p_comparison_column => p_comparison_column
300 ,p_qualifier_definition => p_qualifier_definition
301 ,p_qualifier_where_clause => p_qualifier_where_clause
302 ,p_entry_qualification => p_entry_qualification
303 ,p_assignment_qualification => p_assignment_qualification
304 ,p_multi_event_sql => p_multi_event_sql
305 ,p_object_version_number => p_object_version_number
306 );
307 exception
308 when hr_api.cannot_find_prog_unit then
309 hr_api.cannot_find_prog_unit_error
310 (p_module_name => 'update_event_qualifier'
311 ,p_hook_type => 'BP'
312 );
313 end;
314 hr_utility.set_location(l_proc, 30);
315 --
316 -- Validation in addition to Row Handlers
317 --
318 hr_utility.set_location(l_proc, 40);
319 --
320 -- Process Logic
321 --
322 -- Call the row handler
323 --
324 pay_evq_upd.upd
325 (p_effective_date => l_effective_date
326 ,p_datetrack_mode => p_datetrack_update_mode
327 ,p_event_qualifier_id => p_event_qualifier_id
328 ,p_object_version_number => l_object_version_number
329 ,p_comparison_column => p_comparison_column
330 ,p_qualifier_definition => p_qualifier_definition
331 ,p_qualifier_where_clause => p_qualifier_where_clause
332 ,p_entry_qualification => p_entry_qualification
333 ,p_assignment_qualification => p_assignment_qualification
334 ,p_multi_event_sql => p_multi_event_sql
335 ,p_effective_start_date => l_effective_start_date
336 ,p_effective_end_date => l_effective_end_date
337 );
338 --
339
340 --
341 hr_utility.set_location(l_proc, 50);
342 --
343 -- Call After Process User Hook
344 --
345 begin
346 pay_event_qualifiers_bk2.update_event_qualifier_a
347 (p_effective_date => l_effective_date
348 ,p_datetrack_update_mode => p_datetrack_update_mode
349 ,p_event_qualifier_id => p_event_qualifier_id
350 ,p_dated_table_id => p_dated_table_id
351 ,p_column_name => p_column_name
352 ,p_qualifier_name => p_qualifier_name
353 ,p_legislation_code => p_legislation_code
354 ,p_business_group_id => p_business_group_id
355 ,p_comparison_column => p_comparison_column
356 ,p_qualifier_definition => p_qualifier_definition
357 ,p_qualifier_where_clause => p_qualifier_where_clause
358 ,p_entry_qualification => p_entry_qualification
359 ,p_assignment_qualification => p_assignment_qualification
360 ,p_multi_event_sql => p_multi_event_sql
361 ,p_object_version_number => p_object_version_number
362 ,p_effective_start_date => l_effective_start_date
363 ,p_effective_end_date => l_effective_end_date
364 );
365 exception
366 when hr_api.cannot_find_prog_unit then
367 hr_api.cannot_find_prog_unit_error
368 (p_module_name => 'update_event_qualifier_a'
369 ,p_hook_type => 'AP'
370 );
371 end;
372 hr_utility.set_location(l_proc, 60);
373 --
374 -- When in validation only mode raise the Validate_Enabled exception
375 --
376 if p_validate then
377 raise hr_api.validate_enabled;
378 end if;
379 --
380 -- Set all output arguments
381 --
382 p_effective_start_date := l_effective_start_date;
383 p_effective_end_date := l_effective_end_date;
384 p_object_version_number := p_object_version_number;
385 --
389 --
386 hr_utility.set_location(' Leaving:'||l_proc, 70);
387 exception
388 when hr_api.validate_enabled then
390 -- As the Validate_Enabled exception has been raised
391 -- we must rollback to the savepoint
392 --
393 rollback to update_event_qualifier;
394 --
395 -- Only set output warning arguments
396 -- (Any key or derived arguments must be set to null
397 -- when validation only mode is being used.)
398 --
399 p_object_version_number := l_object_version_number;
400 p_effective_start_date := null;
401 p_effective_end_date := null;
402 hr_utility.set_location(' Leaving:'||l_proc, 80);
403 when others then
404 --
405 -- A validation or unexpected error has occured
406 --
407 rollback to update_event_qualifier;
408 p_object_version_number := l_object_version_number;
409 p_effective_start_date := null;
410 p_effective_end_date := null;
411 hr_utility.set_location(' Leaving:'||l_proc, 90);
412 raise;
413 end update_event_qualifier;
414
415
416
417 -- ----------------------------------------------------------------------------
418 -- |------------------------< delete_event_qualifier >-----------------------|
419 -- ----------------------------------------------------------------------------
420 procedure delete_event_qualifier
421 (p_validate in boolean default false
422 ,p_effective_date in date
423 ,p_datetrack_delete_mode in varchar2
424 ,p_event_qualifier_id in number
425 ,p_object_version_number in out nocopy number
426 ,p_business_group_id in number default hr_api.g_number
427 ,p_legislation_code in varchar2 default hr_api.g_varchar2
428 ,p_effective_start_date out nocopy date
429 ,p_effective_end_date out nocopy date
430 ) is
431 --
432 -- Declare cursors and local variables
433 --
434 l_proc varchar2(72) := g_package||'delete_event_qualifier';
435 l_effective_date date;
436 --
437 -- Declare OUT variables
438 --
439 l_effective_start_date pay_event_qualifiers_f.effective_start_date%type;
440 l_effective_end_date pay_event_qualifiers_f.effective_end_date%type;
441 l_validation_start_date date;
442 l_validation_end_date date;
443 l_object_version_number pay_event_qualifiers_f.object_version_number%type;
444 --
445 begin
446 hr_utility.set_location('Entering:'|| l_proc, 5);
447 --
448 -- Issue a savepoint and assign in-out parameters to local variable
449 --
450 savepoint delete_event_qualifier;
451 l_object_version_number := p_object_version_number;
452 --
453 hr_utility.set_location(l_proc, 10);
454 --
455 l_effective_date := trunc(p_effective_date);
456 --
457 -- Call Before Process User Hook
458 --
459 begin
460 pay_event_qualifiers_bk3.delete_event_qualifier_b
461 (p_effective_date => l_effective_date
462 ,p_datetrack_delete_mode => p_datetrack_delete_mode
463 ,p_event_qualifier_id => p_event_qualifier_id
464 ,p_object_version_number => p_object_version_number
465 ,p_business_group_id => p_business_group_id
466 ,p_legislation_code => p_legislation_code
467 );
468
469 exception
470 when hr_api.cannot_find_prog_unit then
471 hr_api.cannot_find_prog_unit_error
472 (p_module_name => 'delete_event_qualifier'
473 ,p_hook_type => 'BP'
474 );
475 end;
476 --
477 hr_utility.set_location(l_proc, 20);
478 --
479 -- Validation in addition to Row Handlers
480 --
481 hr_utility.set_location(l_proc, 30);
482 --
483 -- Lock the non-translated table row handler for ZAP datetrack delete mode
484 --
485 if p_datetrack_delete_mode = hr_api.g_zap then
486 --
487 pay_evq_shd.lck(p_effective_date => l_effective_date
488 ,p_datetrack_mode => p_datetrack_delete_mode
489 ,p_event_qualifier_id => p_event_qualifier_id
490 ,p_object_version_number => p_object_version_number
491 ,p_validation_start_date => l_validation_start_date
492 ,p_validation_end_date => l_validation_end_date
493 );
494 --
495 end if; -- mode = ZAP
496 --
497 -- Call the row handler to delete the event_qualifier
498 --
499 pay_evq_del.del
500 (p_effective_date => l_effective_date
501 ,p_datetrack_mode => p_datetrack_delete_mode
502 ,p_event_qualifier_id => p_event_qualifier_id
503 ,p_object_version_number => p_object_version_number
504 ,p_effective_start_date => l_effective_start_date
505 ,p_effective_end_date => l_effective_end_date
506 );
507 --
508 hr_utility.set_location(l_proc, 50);
509 --
510 -- Call After Process User Hook
511 --
512 begin
513 pay_event_qualifiers_bk3.delete_event_qualifier_a
514 (p_effective_date => l_effective_date
515 ,p_datetrack_delete_mode => p_datetrack_delete_mode
516 ,p_event_qualifier_id => p_event_qualifier_id
517 ,p_object_version_number => p_object_version_number
518 ,p_business_group_id => p_business_group_id
519 ,p_legislation_code => p_legislation_code
520 ,p_effective_start_date => l_effective_start_date
524 end;
521 ,p_effective_end_date => l_effective_end_date
522 );
523 --
525 hr_utility.set_location(l_proc, 60);
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 out parameters
534 --
535 p_object_version_number := p_object_version_number;
536 p_effective_start_date := l_effective_start_date;
537 p_effective_end_date := l_effective_end_date;
538 --
539 hr_utility.set_location(l_proc, 70);
540 exception
541 when hr_api.validate_enabled then
542 --
543 -- As the Validate_Enabled exception has been raised
544 -- we must rollback to the savepoint
545 --
546 ROLLBACK TO delete_event_qualifier;
547 --
548 -- Only set output warning arguments
549 -- (Any key or derived arguments must be set to null
550 -- when validation only mode is being used.)
551 --
552 p_object_version_number := l_object_version_number;
553 p_effective_start_date := null;
554 p_effective_end_date := null;
555 --
556 when others then
557 --
558 -- A validation or unexpected error has occurred
559 --
560 ROLLBACK TO delete_event_qualifier;
561 p_object_version_number := l_object_version_number;
562 p_effective_start_date := null;
563 p_effective_end_date := null;
564 hr_utility.set_location(' Leaving:'||l_proc, 80);
565 raise;
566 --
567 end delete_event_qualifier;
568 -- ----------------------------------------------------------------------------
569
570 end pay_event_qualifiers_api;