[Home] [Help]
PACKAGE BODY: APPS.PAY_USER_ROW_API
Source
1 Package Body pay_user_row_api as
2 /* $Header: pypurapi.pkb 120.5 2008/04/08 09:44:58 salogana noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pay_user_row_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< create_user_row >-----------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_user_row
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_user_table_id in number
16 ,p_row_low_range_or_name in varchar2
17 ,p_display_sequence in out nocopy NUMBER
18 ,p_business_group_id in number default null
19 ,p_legislation_code in varchar2 default null
20 ,p_disable_range_overlap_check in boolean default false
21 ,p_disable_units_check in boolean default false
22 ,p_row_high_range in varchar2 default null
23 ,p_user_row_id out nocopy number
24 ,p_object_version_number out nocopy number
25 ,p_effective_start_date out nocopy date
26 ,p_effective_end_date out nocopy date
27 /* Added for bug no: 6735596 */
28 ,p_base_row_low_range_or_name in varchar2
29 ) is
30 --
31 -- Declare cursors and local variables
32 --
33 l_in_out_parameter number;
34 l_effective_date date;
35 l_proc varchar2(72) := g_package||'create_user_row';
36 l_user_row_id number;
37 l_display_sequence number;
38 l_object_version_number number;
39 l_effective_start_date date;
40 l_effective_end_date date;
41 --
42 begin
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Issue a savepoint
46 --
47 savepoint create_user_row;
48 --
49 -- Remember IN OUT parameter IN values
50 --
51 l_in_out_parameter := p_display_sequence;
52
53 --
54 -- Truncate the time portion from all IN date parameters
55 --
56 l_effective_date := trunc(p_effective_date);
57
58 --
59 -- Round the Display Sequence
60 --
61 l_display_sequence := round(p_display_sequence);
62
63 --
64 -- Call Before Process User Hook
65 --
66 begin
67 pay_user_row_bk1.create_user_row_b
68 (p_effective_date => l_effective_date
69 ,p_user_table_id => p_user_table_id
70 ,p_row_low_range_or_name => p_base_row_low_range_or_name
71 ,p_display_sequence => l_display_sequence
72 ,p_business_group_id => p_business_group_id
73 ,p_legislation_code => p_legislation_code
74 ,p_disable_range_overlap_check => p_disable_range_overlap_check
75 ,p_disable_units_check => p_disable_units_check
76 ,p_row_high_range => p_row_high_range
77 );
78 exception
79 when hr_api.cannot_find_prog_unit then
80 hr_api.cannot_find_prog_unit_error
81 (p_module_name => 'create_user_row'
82 ,p_hook_type => 'BP'
83 );
84 end;
85 --
86 -- Process Logic
87 --
88 pay_pur_ins.ins
89 (p_effective_date => l_effective_date
90 ,p_user_table_id => p_user_table_id
91 ,p_row_low_range_or_name => p_base_row_low_range_or_name
92 ,p_business_group_id => p_business_group_id
93 ,p_legislation_code => p_legislation_code
94 ,p_display_sequence => l_display_sequence
95 ,p_row_high_range => p_row_high_range
96 ,p_disable_units_check => p_disable_units_check
97 ,p_disable_range_overlap_check => p_disable_range_overlap_check
98 ,p_user_row_id => l_user_row_id
99 ,p_object_version_number => l_object_version_number
100 ,p_effective_start_date => l_effective_start_date
101 ,p_effective_end_date => l_effective_end_date
102 ) ;
103 --
104 -- Call After Process User Hook
105 --
106 begin
107 pay_user_row_bk1.create_user_row_a
108 (p_effective_date => l_effective_date
109 ,p_user_table_id => p_user_table_id
110 ,p_row_low_range_or_name => p_base_row_low_range_or_name
111 ,p_display_sequence => l_display_sequence
112 ,p_business_group_id => p_business_group_id
113 ,p_legislation_code => p_legislation_code
114 ,p_disable_range_overlap_check => p_disable_range_overlap_check
115 ,p_disable_units_check => p_disable_units_check
116 ,p_row_high_range => p_row_high_range
117 ,p_user_row_id => l_user_row_id
118 ,p_object_version_number => l_object_version_number
119 ,p_effective_start_date => l_effective_start_date
120 ,p_effective_end_date => l_effective_end_date
121 );
122 exception
123 when hr_api.cannot_find_prog_unit then
124 hr_api.cannot_find_prog_unit_error
125 (p_module_name => 'create_user_row'
126 ,p_hook_type => 'AP'
127 );
128 end;
129 --
130 -- When in validation only mode raise the Validate_Enabled exception
131 --
132 if p_validate then
133 raise hr_api.validate_enabled;
134 end if;
135 --
136 -- Set all IN OUT and OUT parameters with out values
137 --
138 p_display_sequence := l_display_sequence ;
139 p_user_row_id := l_user_row_id ;
140 p_object_version_number := l_object_version_number ;
141 p_effective_start_date := l_effective_start_date ;
142 p_effective_end_date := l_effective_end_date ;
143
144 --
145 ----For MLS---------------------------------------------------------------------
146 pay_urt_ins.ins_tl(userenv('lang'), p_user_row_id, p_row_low_range_or_name);
147 --------------------------------------------------------------------------------
148 hr_utility.set_location(' Leaving:'||l_proc, 70);
149 exception
150 when hr_api.validate_enabled then
151 --
152 -- As the Validate_Enabled exception has been raised
153 -- we must rollback to the savepoint
154 --
155 rollback to create_user_row;
156 --
157 -- Reset IN OUT parameters and set OUT parameters
158 -- (Any key or derived arguments must be set to null
159 -- when validation only mode is being used.)
160 --
161 p_display_sequence := l_in_out_parameter;
162 p_user_row_id := null ;
163 p_object_version_number := null ;
164 p_effective_start_date := null ;
165 p_effective_end_date := null ;
166
167 hr_utility.set_location(' Leaving:'||l_proc, 80);
168 when others then
169 --
170 -- A validation or unexpected error has occured
171 --
172 rollback to create_user_row;
173 --
174 -- Reset IN OUT parameters and set all
175 -- OUT parameters, including warnings, to null
176 --
177 p_display_sequence := l_in_out_parameter;
178 p_user_row_id := null ;
179 p_object_version_number := null ;
180 p_effective_start_date := null ;
181 p_effective_end_date := null ;
182
183 hr_utility.set_location(' Leaving:'||l_proc, 90);
184 raise;
185 end create_user_row;
186
187 /* Since we added a parameter in create_user_row for bug fix 6735596
188 localization teams had issues.So we overloaded the create_user_row
189 to take 14 arguments and will call the package which has
190 15 arguments */
191
192 procedure create_user_row
193 (p_validate in boolean default false
194 ,p_effective_date in date
195 ,p_user_table_id in number
196 ,p_row_low_range_or_name in varchar2
197 ,p_display_sequence in out nocopy NUMBER
198 ,p_business_group_id in number default null
199 ,p_legislation_code in varchar2 default null
200 ,p_disable_range_overlap_check in boolean default false
201 ,p_disable_units_check in boolean default false
202 ,p_row_high_range in varchar2 default null
203 ,p_user_row_id out nocopy number
204 ,p_object_version_number out nocopy number
205 ,p_effective_start_date out nocopy date
206 ,p_effective_end_date out nocopy date
207 ) is
208 begin
209 create_user_row
210 (p_validate=>p_validate
211 ,p_effective_date=>p_effective_date
212 ,p_user_table_id =>p_user_table_id
213 ,p_row_low_range_or_name=>p_row_low_range_or_name
214 ,p_display_sequence=>p_display_sequence
215 ,p_business_group_id=>p_business_group_id
216 ,p_legislation_code=>p_legislation_code
217 ,p_disable_range_overlap_check=>p_disable_range_overlap_check
218 ,p_disable_units_check=>p_disable_units_check
219 ,p_row_high_range=>p_row_high_range
220 ,p_user_row_id=>p_user_row_id
221 ,p_object_version_number=>p_object_version_number
222 ,p_effective_start_date=>p_effective_start_date
223 ,p_effective_end_date=>p_effective_end_date
224 /* Added for bug no: 6735596 */
225 ,p_base_row_low_range_or_name=>p_row_low_range_or_name);
226 end;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |--------------------------< update_user_row >-----------------------------|
230 -- ----------------------------------------------------------------------------
231 --
232 procedure update_user_row
233 (p_validate in boolean default false
234 ,p_effective_date in date
235 ,p_datetrack_update_mode in varchar2
236 ,p_user_row_id in number
237 ,p_display_sequence in out nocopy number
238 ,p_object_version_number in out nocopy number
239 ,p_row_low_range_or_name in varchar2 default hr_api.g_varchar2
240 ,p_base_row_low_range_or_name in varchar2 default hr_api.g_varchar2
241 ,p_disable_range_overlap_check in boolean default false
242 ,p_disable_units_check in boolean default false
243 ,p_row_high_range in varchar2 default hr_api.g_varchar2
244 ,p_effective_start_date out nocopy date
245 ,p_effective_end_date out nocopy date
246 ) is
247 --
248 -- Declare cursors and local variables
249 --
250 l_in_out_parameter1 number;
251 l_in_out_parameter2 number;
252 l_effective_date date;
253 l_proc varchar2(72) := g_package||'update_user_row';
254 l_display_sequence number;
255 l_object_version_number number;
256 l_effective_start_date date;
257 l_effective_end_date date;
258 --
259 begin
260 hr_utility.set_location('Entering:'|| l_proc, 10);
261 --
262 -- Issue a savepoint
263 --
264 savepoint update_user_row;
265 --
266 -- Remember IN OUT parameter IN values
267 --
268 l_in_out_parameter1 := p_display_sequence;
269 l_in_out_parameter2 := p_object_version_number;
270 --
271 --
272 l_object_version_number := p_object_version_number;
273 --
274 -- Truncate the time portion from all IN date parameters
275 --
276 l_effective_date := trunc(p_effective_date);
277
278 --
279 -- Round the Display Sequence
280 --
281 l_display_sequence := round(p_display_sequence);
282
283 --
284 -- Call Before Process User Hook
285 --
286 begin
287 pay_user_row_bk2.update_user_row_b
288 (p_effective_date => l_effective_date
289 ,p_datetrack_update_mode => p_datetrack_update_mode
290 ,p_user_row_id => p_user_row_id
291 ,p_display_sequence => l_display_sequence
292 ,p_object_version_number => l_object_version_number
293 ,p_row_low_range_or_name => p_base_row_low_range_or_name
294 ,p_disable_range_overlap_check => p_disable_range_overlap_check
295 ,p_disable_units_check => p_disable_units_check
296 ,p_row_high_range => p_row_high_range
297 );
298 exception
299 when hr_api.cannot_find_prog_unit then
300 hr_api.cannot_find_prog_unit_error
301 (p_module_name => 'update_user_row'
302 ,p_hook_type => 'BP'
303 );
304 end;
305 --
306 -- Process Logic
307 --
308 pay_pur_upd.upd
309 (p_effective_date => l_effective_date
310 ,p_datetrack_mode => p_datetrack_update_mode
311 ,p_user_row_id => p_user_row_id
312 ,p_object_version_number => l_object_version_number
313 ,p_row_low_range_or_name => p_base_row_low_range_or_name
314 ,p_display_sequence => l_display_sequence
315 ,p_row_high_range => p_row_high_range
316 ,p_disable_units_check => p_disable_units_check
317 ,p_disable_range_overlap_check => p_disable_range_overlap_check
318 ,p_effective_start_date => l_effective_start_date
319 ,p_effective_end_date => l_effective_end_date
320 ) ;
321 --
322 -- Call After Process User Hook
323 --
324 begin
325 pay_user_row_bk2.update_user_row_a
326 (p_effective_date => l_effective_date
327 ,p_datetrack_update_mode => p_datetrack_update_mode
328 ,p_user_row_id => p_user_row_id
329 ,p_display_sequence => l_display_sequence
330 ,p_object_version_number => l_object_version_number
331 ,p_row_low_range_or_name => p_base_row_low_range_or_name
332 ,p_disable_range_overlap_check => p_disable_range_overlap_check
333 ,p_disable_units_check => p_disable_units_check
334 ,p_row_high_range => p_row_high_range
335 ,p_effective_start_date => l_effective_start_date
336 ,p_effective_end_date => l_effective_end_date
337 );
338
339 exception
340 when hr_api.cannot_find_prog_unit then
341 hr_api.cannot_find_prog_unit_error
342 (p_module_name => 'update_user_row'
343 ,p_hook_type => 'AP'
344 );
345 end;
346 --
347 -- When in validation only mode raise the Validate_Enabled exception
348 --
349 if p_validate then
350 raise hr_api.validate_enabled;
351 end if;
352 --
353 -- Set all IN OUT and OUT parameters with out values
354 --
355 p_display_sequence := l_display_sequence ;
356 p_object_version_number := l_object_version_number ;
357 p_effective_start_date := l_effective_start_date ;
358 p_effective_end_date := l_effective_end_date ;
359
360 --
361 ----For MLS---------------------------------------------------------------------
362 pay_urt_upd.upd_tl(userenv('lang'), p_user_row_id, p_row_low_range_or_name);
363 --------------------------------------------------------------------------------
364 hr_utility.set_location(' Leaving:'||l_proc, 70);
365 exception
366 when hr_api.validate_enabled then
367 --
368 -- As the Validate_Enabled exception has been raised
369 -- we must rollback to the savepoint
370 --
371 rollback to update_user_row;
372 --
373 -- Reset IN OUT parameters and set OUT parameters
374 -- (Any key or derived arguments must be set to null
375 -- when validation only mode is being used.)
376 --
377 p_display_sequence := l_in_out_parameter1;
378 p_object_version_number := l_in_out_parameter2;
379 p_effective_start_date := null ;
380 p_effective_end_date := null ;
381
382 hr_utility.set_location(' Leaving:'||l_proc, 80);
383 when others then
384 --
385 -- A validation or unexpected error has occured
386 --
387 rollback to update_user_row;
388 --
389 -- Reset IN OUT parameters and set all
390 -- OUT parameters, including warnings, to null
391 --
392 p_display_sequence := l_in_out_parameter1;
393 p_object_version_number := l_in_out_parameter2;
394 p_effective_start_date := null ;
395 p_effective_end_date := null ;
396
397 hr_utility.set_location(' Leaving:'||l_proc, 90);
398 raise;
399 end update_user_row;
400 --
401 -- ----------------------------------------------------------------------------
402 -- |--------------------------< delete_user_row >-----------------------------|
403 -- ----------------------------------------------------------------------------
404 --
405 procedure delete_user_row
406 (p_validate in boolean default false
407 ,p_effective_date in date
408 ,p_datetrack_update_mode in varchar2
409 ,p_user_row_id in number
410 ,p_object_version_number in out nocopy number
411 ,p_disable_range_overlap_check in boolean default false
412 ,p_effective_start_date out nocopy date
413 ,p_effective_end_date out nocopy date
414 ) is
415 --
416 -- Declare cursors and local variables
417 --
418 l_in_out_parameter number;
419 l_effective_date date;
420 l_proc varchar2(72) := g_package||'delete_user_row';
421 l_object_version_number number;
422 l_effective_start_date date;
423 l_effective_end_date date;
424 --
425 begin
426 hr_utility.set_location('Entering:'|| l_proc, 10);
427 --
428 -- Issue a savepoint
429 --
430 savepoint delete_user_row;
431 --
432 -- Remember IN OUT parameter IN values
433 --
434 l_in_out_parameter := p_object_version_number;
435 --
436 --
437 l_object_version_number := p_object_version_number;
438 --
439 -- Truncate the time portion from all IN date parameters
440 --
441 l_effective_date := trunc(p_effective_date);
442 --
443 -- Call Before Process User Hook
444 --
445 begin
446 pay_user_row_bk3.delete_user_row_b
447 (p_effective_date => l_effective_date
448 ,p_datetrack_update_mode => p_datetrack_update_mode
449 ,p_user_row_id => p_user_row_id
450 ,p_object_version_number => l_object_version_number
451 ,p_disable_range_overlap_check => p_disable_range_overlap_check
452 );
453 exception
454 when hr_api.cannot_find_prog_unit then
455 hr_api.cannot_find_prog_unit_error
456 (p_module_name => 'delete_user_row'
457 ,p_hook_type => 'BP'
458 );
459 end;
460 --
461 -- Process Logic
462 --
463 pay_pur_del.del
464 (p_effective_date => l_effective_date
465 ,p_datetrack_mode => p_datetrack_update_mode
466 ,p_user_row_id => p_user_row_id
467 ,p_object_version_number => l_object_version_number
468 ,p_disable_range_overlap_check => p_disable_range_overlap_check
469 ,p_effective_start_date => l_effective_start_date
470 ,p_effective_end_date => l_effective_end_date
471 );
472 --
473 -- Call After Process User Hook
474 --
475 begin
476 pay_user_row_bk3.delete_user_row_a
477 (p_effective_date => l_effective_date
478 ,p_datetrack_update_mode => p_datetrack_update_mode
479 ,p_user_row_id => p_user_row_id
480 ,p_object_version_number => l_object_version_number
481 ,p_disable_range_overlap_check => p_disable_range_overlap_check
482 ,p_effective_start_date => l_effective_start_date
483 ,p_effective_end_date => l_effective_end_date
484 );
485 exception
486 when hr_api.cannot_find_prog_unit then
487 hr_api.cannot_find_prog_unit_error
488 (p_module_name => 'delete_user_row'
489 ,p_hook_type => 'AP'
490 );
491 end;
492 --
493 -- When in validation only mode raise the Validate_Enabled exception
494 --
495 if p_validate then
496 raise hr_api.validate_enabled;
497 end if;
498 --
499 -- Set all IN OUT and OUT parameters with out values
500 --
501 p_object_version_number := l_object_version_number ;
502 p_effective_start_date := l_effective_start_date ;
503 p_effective_end_date := l_effective_end_date ;
504
505 --
506 ----For MLS---------------------------------------------------------------------
507 if (UPPER(p_datetrack_update_mode) = 'ZAP') then
508 pay_urt_del.del_tl(p_user_row_id);
509 end if;
510 --------------------------------------------------------------------------------
511 hr_utility.set_location(' Leaving:'||l_proc, 70);
512 exception
513 when hr_api.validate_enabled then
514 --
515 -- As the Validate_Enabled exception has been raised
516 -- we must rollback to the savepoint
517 --
518 rollback to delete_user_row;
519 --
520 -- Reset IN OUT parameters and set OUT parameters
521 -- (Any key or derived arguments must be set to null
522 -- when validation only mode is being used.)
523 --
524 p_object_version_number := l_in_out_parameter;
525 p_effective_start_date := null ;
526 p_effective_end_date := null ;
527
528 hr_utility.set_location(' Leaving:'||l_proc, 80);
529 when others then
530 --
531 -- A validation or unexpected error has occured
532 --
533 rollback to delete_user_row;
534 --
535 -- Reset IN OUT parameters and set all
536 -- OUT parameters, including warnings, to null
537 --
538 p_object_version_number := l_in_out_parameter;
539 p_effective_start_date := null ;
540 p_effective_end_date := null ;
541
542 hr_utility.set_location(' Leaving:'||l_proc, 90);
543 raise;
544 end delete_user_row;
545 --
546
547 end pay_user_row_api;