[Home] [Help]
PACKAGE BODY: APPS.GHR_PAR_EXTRA_INFO_API
Source
1 Package Body ghr_par_extra_info_api as
2 /* $Header: ghreiapi.pkb 120.9 2012/01/19 06:59:33 rpahune ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ghr_par_extra_info_api.';
7 --
8
9 PROCEDURE validate_extra_info
10 (p_pa_request_id NUMBER
11 ,p_information_type VARCHAR2
12 ,p_rei_information_category VARCHAR2
13 ,p_rei_information1 VARCHAR2
14 ,p_rei_information2 VARCHAR2
15 ,p_rei_information3 VARCHAR2
16 ,p_rei_information4 VARCHAR2
17 ,p_rei_information5 VARCHAR2
18 ,p_rei_information6 VARCHAR2
19 ,p_rei_information7 VARCHAR2
20 ,p_rei_information8 VARCHAR2
21 ,p_rei_information9 VARCHAR2
22 ,p_rei_information10 VARCHAR2
23 ,p_rei_information11 VARCHAR2
24 ,p_rei_information12 VARCHAR2
25 ,p_rei_information13 VARCHAR2
26 ,p_rei_information14 VARCHAR2
27 ,p_rei_information15 VARCHAR2
28 ,p_rei_information16 VARCHAR2
29 ,p_rei_information17 VARCHAR2
30 ,p_rei_information18 VARCHAR2
31 ,p_rei_information19 VARCHAR2
32 ,p_rei_information20 VARCHAR2
33 ,p_rei_information21 VARCHAR2
34 ,p_rei_information22 VARCHAR2
35 ,p_rei_information23 VARCHAR2
36 ,p_rei_information24 VARCHAR2
37 ,p_rei_information25 VARCHAR2
38 ,p_rei_information26 VARCHAR2
39 ,p_rei_information27 VARCHAR2
40 ,p_rei_information28 VARCHAR2
41 ,p_rei_information29 VARCHAR2
42 ,p_rei_information30 VARCHAR2
43 ,p_ben_ei_validate varchar2 default 'FALSE');
44
45 -- ----------------------------------------------------------------------------
46 -- |----------------------< create_pa_request_extra_info >----------------------|
47 -- ----------------------------------------------------------------------------
48 --
49 procedure create_pa_request_extra_info
50 (p_validate in boolean default false
51 ,p_pa_request_id in number
52 ,p_information_type in varchar2
53 ,p_rei_attribute_category in varchar2 default null
54 ,p_rei_attribute1 in varchar2 default null
55 ,p_rei_attribute2 in varchar2 default null
56 ,p_rei_attribute3 in varchar2 default null
57 ,p_rei_attribute4 in varchar2 default null
58 ,p_rei_attribute5 in varchar2 default null
59 ,p_rei_attribute6 in varchar2 default null
60 ,p_rei_attribute7 in varchar2 default null
61 ,p_rei_attribute8 in varchar2 default null
62 ,p_rei_attribute9 in varchar2 default null
63 ,p_rei_attribute10 in varchar2 default null
64 ,p_rei_attribute11 in varchar2 default null
65 ,p_rei_attribute12 in varchar2 default null
66 ,p_rei_attribute13 in varchar2 default null
67 ,p_rei_attribute14 in varchar2 default null
68 ,p_rei_attribute15 in varchar2 default null
69 ,p_rei_attribute16 in varchar2 default null
70 ,p_rei_attribute17 in varchar2 default null
71 ,p_rei_attribute18 in varchar2 default null
72 ,p_rei_attribute19 in varchar2 default null
73 ,p_rei_attribute20 in varchar2 default null
74 ,p_rei_information_category in varchar2 default null
75 ,p_rei_information1 in varchar2 default null
76 ,p_rei_information2 in varchar2 default null
77 ,p_rei_information3 in varchar2 default null
78 ,p_rei_information4 in varchar2 default null
79 ,p_rei_information5 in varchar2 default null
80 ,p_rei_information6 in varchar2 default null
81 ,p_rei_information7 in varchar2 default null
82 ,p_rei_information8 in varchar2 default null
83 ,p_rei_information9 in varchar2 default null
84 ,p_rei_information10 in varchar2 default null
85 ,p_rei_information11 in varchar2 default null
86 ,p_rei_information12 in varchar2 default null
87 ,p_rei_information13 in varchar2 default null
88 ,p_rei_information14 in varchar2 default null
89 ,p_rei_information15 in varchar2 default null
90 ,p_rei_information16 in varchar2 default null
91 ,p_rei_information17 in varchar2 default null
92 ,p_rei_information18 in varchar2 default null
93 ,p_rei_information19 in varchar2 default null
94 ,p_rei_information20 in varchar2 default null
95 ,p_rei_information21 in varchar2 default null
96 ,p_rei_information22 in varchar2 default null
97 ,p_rei_information23 in varchar2 default null
98 ,p_rei_information24 in varchar2 default null
99 ,p_rei_information25 in varchar2 default null
100 ,p_rei_information26 in varchar2 default null
101 ,p_rei_information27 in varchar2 default null
102 ,p_rei_information28 in varchar2 default null
103 ,p_rei_information29 in varchar2 default null
104 ,p_rei_information30 in varchar2 default null
105 ,p_pa_request_extra_info_id out NOCOPY number
106 ,p_object_version_number out NOCOPY number
107 ,p_ben_ei_validate in varchar2 default 'FALSE'
108 ) is
109 --
110 -- Declare cursors and local variables
111 --
112 l_proc varchar2(72);
113 --
114 CURSOR c_noa_family_code(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
115 SELECT noa_family_code
116 FROM ghr_pa_requests
117 WHERE pa_request_id = c_pa_request_id;
118 l_noa_family_code ghr_pa_requests.noa_family_code%type;
119
120 begin
121 l_proc := g_package||'create_pa_request_extra_info';
122 hr_utility.set_location('Entering:'|| l_proc, 5);
123 --
124 savepoint create_pa_request_extra_info;
125 --
126 --
127 -- Call Before Process User Hook
128 --
129 begin
130 ghr_par_extra_info_bk1.create_par_extra_info_b (
131 p_pa_request_id => p_pa_request_id
132 ,p_information_type => p_information_type
133 ,p_rei_attribute_category => p_rei_attribute_category
134 ,p_rei_attribute1 => p_rei_attribute1
135 ,p_rei_attribute2 => p_rei_attribute2
136 ,p_rei_attribute3 => p_rei_attribute3
137 ,p_rei_attribute4 => p_rei_attribute4
138 ,p_rei_attribute5 => p_rei_attribute5
139 ,p_rei_attribute6 => p_rei_attribute6
140 ,p_rei_attribute7 => p_rei_attribute7
141 ,p_rei_attribute8 => p_rei_attribute8
142 ,p_rei_attribute9 => p_rei_attribute9
143 ,p_rei_attribute10 => p_rei_attribute10
144 ,p_rei_attribute11 => p_rei_attribute11
145 ,p_rei_attribute12 => p_rei_attribute12
146 ,p_rei_attribute13 => p_rei_attribute13
147 ,p_rei_attribute14 => p_rei_attribute14
148 ,p_rei_attribute15 => p_rei_attribute15
149 ,p_rei_attribute16 => p_rei_attribute16
150 ,p_rei_attribute17 => p_rei_attribute17
151 ,p_rei_attribute18 => p_rei_attribute18
152 ,p_rei_attribute19 => p_rei_attribute19
153 ,p_rei_attribute20 => p_rei_attribute20
154 ,p_rei_information_category => p_rei_information_category
155 ,p_rei_information1 => p_rei_information1
156 ,p_rei_information2 => p_rei_information2
157 ,p_rei_information3 => p_rei_information3
158 ,p_rei_information4 => p_rei_information4
159 ,p_rei_information5 => p_rei_information5
160 ,p_rei_information6 => p_rei_information6
161 ,p_rei_information7 => p_rei_information7
162 ,p_rei_information8 => p_rei_information8
163 ,p_rei_information9 => p_rei_information9
164 ,p_rei_information10 => p_rei_information10
165 ,p_rei_information11 => p_rei_information11
166 ,p_rei_information12 => p_rei_information12
167 ,p_rei_information13 => p_rei_information13
168 ,p_rei_information14 => p_rei_information14
169 ,p_rei_information15 => p_rei_information15
170 ,p_rei_information16 => p_rei_information16
171 ,p_rei_information17 => p_rei_information17
172 ,p_rei_information18 => p_rei_information18
173 ,p_rei_information19 => p_rei_information19
174 ,p_rei_information20 => p_rei_information20
175 ,p_rei_information21 => p_rei_information21
176 ,p_rei_information22 => p_rei_information22
177 ,p_rei_information23 => p_rei_information23
178 ,p_rei_information24 => p_rei_information24
179 ,p_rei_information25 => p_rei_information25
180 ,p_rei_information26 => p_rei_information26
181 ,p_rei_information27 => p_rei_information27
182 ,p_rei_information28 => p_rei_information28
183 ,p_rei_information29 => p_rei_information29
184 ,p_rei_information30 => p_rei_information30
185 );
186 exception
187 when hr_api.cannot_find_prog_unit then
188 hr_api.cannot_find_prog_unit_error
189 (p_module_name => 'create_par_extra_info',
190 p_hook_type => 'BP'
191 );
192 end;
193 --
194 -- End of Before Process User Hook call
195 --
196 hr_utility.set_location(l_proc, 7);
197 -- Validation
198
199 FOR l_cur_noa_family_code IN c_noa_family_code(p_pa_request_id) LOOP
200 l_noa_family_code := l_cur_noa_family_code.noa_family_code;
201 END LOOP;
202
203 IF l_noa_family_code IN ('APP','CONV_APP','EXT_NTE') THEN
204 validate_extra_info
205 (p_pa_request_id => p_pa_request_id
206 ,p_information_type => p_information_type
207 ,p_rei_information_category => p_rei_information_category
208 ,p_rei_information1 => p_rei_information1
209 ,p_rei_information2 => p_rei_information2
210 ,p_rei_information3 => p_rei_information3
211 ,p_rei_information4 => p_rei_information4
212 ,p_rei_information5 => p_rei_information5
213 ,p_rei_information6 => p_rei_information6
214 ,p_rei_information7 => p_rei_information7
215 ,p_rei_information8 => p_rei_information8
216 ,p_rei_information9 => p_rei_information9
217 ,p_rei_information10 => p_rei_information10
218 ,p_rei_information11 => p_rei_information11
219 ,p_rei_information12 => p_rei_information12
220 ,p_rei_information13 => p_rei_information13
221 ,p_rei_information14 => p_rei_information14
222 ,p_rei_information15 => p_rei_information15
223 ,p_rei_information16 => p_rei_information16
224 ,p_rei_information17 => p_rei_information17
225 ,p_rei_information18 => p_rei_information18
226 ,p_rei_information19 => p_rei_information19
227 ,p_rei_information20 => p_rei_information20
228 ,p_rei_information21 => p_rei_information21
229 ,p_rei_information22 => p_rei_information22
230 ,p_rei_information23 => p_rei_information23
231 ,p_rei_information24 => p_rei_information24
232 ,p_rei_information25 => p_rei_information25
233 ,p_rei_information26 => p_rei_information26
234 ,p_rei_information27 => p_rei_information27
235 ,p_rei_information28 => p_rei_information28
236 ,p_rei_information29 => p_rei_information29
237 ,p_rei_information30 => p_rei_information30
238 ,p_ben_ei_validate => p_ben_ei_validate
239 );
240 END IF; -- IF l_noa_family_code = 'APP' THEN
241 --
242
243 -- Process Logic
244
245 ghr_rei_ins.ins
246 (p_pa_request_extra_info_id => p_pa_request_extra_info_id
247 ,p_pa_request_id => p_pa_request_id
248 ,p_information_type => p_information_type
249 ,p_rei_attribute_category => p_rei_attribute_category
250 ,p_rei_attribute1 => p_rei_attribute1
251 ,p_rei_attribute2 => p_rei_attribute2
252 ,p_rei_attribute3 => p_rei_attribute3
253 ,p_rei_attribute4 => p_rei_attribute4
254 ,p_rei_attribute5 => p_rei_attribute5
255 ,p_rei_attribute6 => p_rei_attribute6
256 ,p_rei_attribute7 => p_rei_attribute7
257 ,p_rei_attribute8 => p_rei_attribute8
258 ,p_rei_attribute9 => p_rei_attribute9
259 ,p_rei_attribute10 => p_rei_attribute10
260 ,p_rei_attribute11 => p_rei_attribute11
261 ,p_rei_attribute12 => p_rei_attribute12
262 ,p_rei_attribute13 => p_rei_attribute13
263 ,p_rei_attribute14 => p_rei_attribute14
264 ,p_rei_attribute15 => p_rei_attribute15
265 ,p_rei_attribute16 => p_rei_attribute16
266 ,p_rei_attribute17 => p_rei_attribute17
267 ,p_rei_attribute18 => p_rei_attribute18
268 ,p_rei_attribute19 => p_rei_attribute19
269 ,p_rei_attribute20 => p_rei_attribute20
270 ,p_rei_information_category => p_rei_information_category
271 ,p_rei_information1 => p_rei_information1
272 ,p_rei_information2 => p_rei_information2
273 ,p_rei_information3 => p_rei_information3
274 ,p_rei_information4 => p_rei_information4
275 ,p_rei_information5 => p_rei_information5
276 ,p_rei_information6 => p_rei_information6
277 ,p_rei_information7 => p_rei_information7
278 ,p_rei_information8 => p_rei_information8
279 ,p_rei_information9 => p_rei_information9
280 ,p_rei_information10 => p_rei_information10
281 ,p_rei_information11 => p_rei_information11
282 ,p_rei_information12 => p_rei_information12
283 ,p_rei_information13 => p_rei_information13
284 ,p_rei_information14 => p_rei_information14
285 ,p_rei_information15 => p_rei_information15
286 ,p_rei_information16 => p_rei_information16
287 ,p_rei_information17 => p_rei_information17
288 ,p_rei_information18 => p_rei_information18
289 ,p_rei_information19 => p_rei_information19
290 ,p_rei_information20 => p_rei_information20
291 ,p_rei_information21 => p_rei_information21
292 ,p_rei_information22 => p_rei_information22
293 ,p_rei_information23 => p_rei_information23
294 ,p_rei_information24 => p_rei_information24
295 ,p_rei_information25 => p_rei_information25
296 ,p_rei_information26 => p_rei_information26
297 ,p_rei_information27 => p_rei_information27
298 ,p_rei_information28 => p_rei_information28
299 ,p_rei_information29 => p_rei_information29
300 ,p_rei_information30 => p_rei_information30
301 ,p_object_version_number => p_object_version_number
302 ,p_validate => false
303 );
304 --
305 hr_utility.set_location(l_proc, 8);
306 --
307 --
308 -- Call After Process User Hook
309 --
310
311 begin
312 ghr_par_extra_info_bk1.create_par_extra_info_a (
313 p_pa_request_extra_info_id => p_pa_request_extra_info_id
314 ,p_pa_request_id => p_pa_request_id
315 ,p_information_type => p_information_type
316 ,p_rei_attribute_category => p_rei_attribute_category
317 ,p_rei_attribute1 => p_rei_attribute1
318 ,p_rei_attribute2 => p_rei_attribute2
319 ,p_rei_attribute3 => p_rei_attribute3
320 ,p_rei_attribute4 => p_rei_attribute4
321 ,p_rei_attribute5 => p_rei_attribute5
322 ,p_rei_attribute6 => p_rei_attribute6
323 ,p_rei_attribute7 => p_rei_attribute7
324 ,p_rei_attribute8 => p_rei_attribute8
325 ,p_rei_attribute9 => p_rei_attribute9
326 ,p_rei_attribute10 => p_rei_attribute10
327 ,p_rei_attribute11 => p_rei_attribute11
328 ,p_rei_attribute12 => p_rei_attribute12
329 ,p_rei_attribute13 => p_rei_attribute13
330 ,p_rei_attribute14 => p_rei_attribute14
331 ,p_rei_attribute15 => p_rei_attribute15
332 ,p_rei_attribute16 => p_rei_attribute16
333 ,p_rei_attribute17 => p_rei_attribute17
334 ,p_rei_attribute18 => p_rei_attribute18
335 ,p_rei_attribute19 => p_rei_attribute19
336 ,p_rei_attribute20 => p_rei_attribute20
337 ,p_rei_information_category => p_rei_information_category
338 ,p_rei_information1 => p_rei_information1
339 ,p_rei_information2 => p_rei_information2
340 ,p_rei_information3 => p_rei_information3
341 ,p_rei_information4 => p_rei_information4
342 ,p_rei_information5 => p_rei_information5
343 ,p_rei_information6 => p_rei_information6
344 ,p_rei_information7 => p_rei_information7
345 ,p_rei_information8 => p_rei_information8
346 ,p_rei_information9 => p_rei_information9
347 ,p_rei_information10 => p_rei_information10
348 ,p_rei_information11 => p_rei_information11
349 ,p_rei_information12 => p_rei_information12
350 ,p_rei_information13 => p_rei_information13
351 ,p_rei_information14 => p_rei_information14
352 ,p_rei_information15 => p_rei_information15
353 ,p_rei_information16 => p_rei_information16
354 ,p_rei_information17 => p_rei_information17
355 ,p_rei_information18 => p_rei_information18
356 ,p_rei_information19 => p_rei_information19
357 ,p_rei_information20 => p_rei_information20
358 ,p_rei_information21 => p_rei_information21
359 ,p_rei_information22 => p_rei_information22
360 ,p_rei_information23 => p_rei_information23
361 ,p_rei_information24 => p_rei_information24
362 ,p_rei_information25 => p_rei_information25
363 ,p_rei_information26 => p_rei_information26
364 ,p_rei_information27 => p_rei_information27
365 ,p_rei_information28 => p_rei_information28
366 ,p_rei_information29 => p_rei_information29
367 ,p_rei_information30 => p_rei_information30
368 ,p_object_version_number => p_object_version_number
369 );
370 exception
371 when hr_api.cannot_find_prog_unit then
372 hr_api.cannot_find_prog_unit_error
373 (p_module_name => 'create_par_extra_info',
374 p_hook_type => 'AP'
375 );
376 end;
377 --
378 -- End of After Process User Hook call
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 hr_utility.set_location(' Leaving:'||l_proc, 11);
387 exception
388 when hr_api.validate_enabled then
389 --
390 -- As the Validate_Enabled exception has been raised
391 -- we must rollback to the savepoint
392 --
393 ROLLBACK TO create_pa_request_extra_info;
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_pa_request_extra_info_id := null;
400 p_object_version_number := null;
401 --
402 when others then
403 ROLLBACK TO create_pa_request_extra_info;
404 p_pa_request_extra_info_id := null;
405 p_object_version_number := null;
406 hr_utility.set_location(' Leaving:'||l_proc, 12);
407 raise;
408
409 end create_pa_request_extra_info;
410 --
411 -- ----------------------------------------------------------------------------
412 -- |----------------------< update_pa_request_extra_info >----------------------|
413 -- ----------------------------------------------------------------------------
414 --
415 procedure update_pa_request_extra_info
416 (p_validate in boolean default false
417 ,p_pa_request_extra_info_id in number
418 ,p_object_version_number in out NOCOPY number
419 ,p_rei_attribute_category in varchar2 default hr_api.g_varchar2
420 ,p_rei_attribute1 in varchar2 default hr_api.g_varchar2
421 ,p_rei_attribute2 in varchar2 default hr_api.g_varchar2
422 ,p_rei_attribute3 in varchar2 default hr_api.g_varchar2
423 ,p_rei_attribute4 in varchar2 default hr_api.g_varchar2
424 ,p_rei_attribute5 in varchar2 default hr_api.g_varchar2
425 ,p_rei_attribute6 in varchar2 default hr_api.g_varchar2
426 ,p_rei_attribute7 in varchar2 default hr_api.g_varchar2
427 ,p_rei_attribute8 in varchar2 default hr_api.g_varchar2
428 ,p_rei_attribute9 in varchar2 default hr_api.g_varchar2
429 ,p_rei_attribute10 in varchar2 default hr_api.g_varchar2
430 ,p_rei_attribute11 in varchar2 default hr_api.g_varchar2
431 ,p_rei_attribute12 in varchar2 default hr_api.g_varchar2
432 ,p_rei_attribute13 in varchar2 default hr_api.g_varchar2
433 ,p_rei_attribute14 in varchar2 default hr_api.g_varchar2
434 ,p_rei_attribute15 in varchar2 default hr_api.g_varchar2
435 ,p_rei_attribute16 in varchar2 default hr_api.g_varchar2
436 ,p_rei_attribute17 in varchar2 default hr_api.g_varchar2
437 ,p_rei_attribute18 in varchar2 default hr_api.g_varchar2
438 ,p_rei_attribute19 in varchar2 default hr_api.g_varchar2
439 ,p_rei_attribute20 in varchar2 default hr_api.g_varchar2
440 ,p_rei_information_category in varchar2 default hr_api.g_varchar2
441 ,p_rei_information1 in varchar2 default hr_api.g_varchar2
442 ,p_rei_information2 in varchar2 default hr_api.g_varchar2
443 ,p_rei_information3 in varchar2 default hr_api.g_varchar2
444 ,p_rei_information4 in varchar2 default hr_api.g_varchar2
445 ,p_rei_information5 in varchar2 default hr_api.g_varchar2
446 ,p_rei_information6 in varchar2 default hr_api.g_varchar2
447 ,p_rei_information7 in varchar2 default hr_api.g_varchar2
448 ,p_rei_information8 in varchar2 default hr_api.g_varchar2
449 ,p_rei_information9 in varchar2 default hr_api.g_varchar2
450 ,p_rei_information10 in varchar2 default hr_api.g_varchar2
451 ,p_rei_information11 in varchar2 default hr_api.g_varchar2
452 ,p_rei_information12 in varchar2 default hr_api.g_varchar2
453 ,p_rei_information13 in varchar2 default hr_api.g_varchar2
454 ,p_rei_information14 in varchar2 default hr_api.g_varchar2
455 ,p_rei_information15 in varchar2 default hr_api.g_varchar2
456 ,p_rei_information16 in varchar2 default hr_api.g_varchar2
457 ,p_rei_information17 in varchar2 default hr_api.g_varchar2
458 ,p_rei_information18 in varchar2 default hr_api.g_varchar2
459 ,p_rei_information19 in varchar2 default hr_api.g_varchar2
460 ,p_rei_information20 in varchar2 default hr_api.g_varchar2
461 ,p_rei_information21 in varchar2 default hr_api.g_varchar2
462 ,p_rei_information22 in varchar2 default hr_api.g_varchar2
463 ,p_rei_information23 in varchar2 default hr_api.g_varchar2
464 ,p_rei_information24 in varchar2 default hr_api.g_varchar2
465 ,p_rei_information25 in varchar2 default hr_api.g_varchar2
466 ,p_rei_information26 in varchar2 default hr_api.g_varchar2
467 ,p_rei_information27 in varchar2 default hr_api.g_varchar2
468 ,p_rei_information28 in varchar2 default hr_api.g_varchar2
469 ,p_rei_information29 in varchar2 default hr_api.g_varchar2
470 ,p_rei_information30 in varchar2 default hr_api.g_varchar2
471 ,p_ben_ei_validate in varchar2 default 'FALSE'
472 ) is
473 --
474 -- Declare cursors and local variables
475 --
476 l_proc varchar2(72) ;
477 l_object_version_number ghr_pa_request_extra_info.object_version_number%TYPE;
478 l_obj_version_number ghr_pa_request_extra_info.object_version_number%TYPE; -- NOCOPY changes
479 --
480 CURSOR c_noa_family_code(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
481 SELECT noa_family_code
482 FROM ghr_pa_requests
483 WHERE pa_request_id = c_pa_request_id;
484 l_noa_family_code ghr_pa_requests.noa_family_code%type;
485 l_pa_req_id ghr_pa_requests.pa_request_id%type;
486 l_information_type ghr_pa_request_extra_info.information_type%type;
487
488 CURSOR c_pa_req_id (c_pa_req_extra_info_id ghr_pa_request_extra_info.pa_request_extra_info_id%type) IS
489 SELECT pa_request_id, information_type
490 FROM ghr_pa_request_extra_info
491 WHERE pa_request_extra_info_id = c_pa_req_extra_info_id;
492
493 begin
494 l_proc := g_package||'update_pa_request_extra_info';
495 hr_utility.set_location('Entering:'|| l_proc, 5);
496 l_obj_version_number := p_object_version_number;
497 --
498 savepoint update_pa_request_extra_info;
499 --
500 --
501 -- Call Before Process User Hook
502 --
503 begin
504 ghr_par_extra_info_bk2.update_par_extra_info_b (
505 p_pa_request_extra_info_id => p_pa_request_extra_info_id
506 ,p_rei_attribute_category => p_rei_attribute_category
507 ,p_rei_attribute1 => p_rei_attribute1
508 ,p_rei_attribute2 => p_rei_attribute2
509 ,p_rei_attribute3 => p_rei_attribute3
510 ,p_rei_attribute4 => p_rei_attribute4
511 ,p_rei_attribute5 => p_rei_attribute5
512 ,p_rei_attribute6 => p_rei_attribute6
513 ,p_rei_attribute7 => p_rei_attribute7
514 ,p_rei_attribute8 => p_rei_attribute8
515 ,p_rei_attribute9 => p_rei_attribute9
516 ,p_rei_attribute10 => p_rei_attribute10
517 ,p_rei_attribute11 => p_rei_attribute11
518 ,p_rei_attribute12 => p_rei_attribute12
519 ,p_rei_attribute13 => p_rei_attribute13
520 ,p_rei_attribute14 => p_rei_attribute14
521 ,p_rei_attribute15 => p_rei_attribute15
522 ,p_rei_attribute16 => p_rei_attribute16
523 ,p_rei_attribute17 => p_rei_attribute17
524 ,p_rei_attribute18 => p_rei_attribute18
525 ,p_rei_attribute19 => p_rei_attribute19
526 ,p_rei_attribute20 => p_rei_attribute20
527 ,p_rei_information_category => p_rei_information_category
528 ,p_rei_information1 => p_rei_information1
529 ,p_rei_information2 => p_rei_information2
530 ,p_rei_information3 => p_rei_information3
531 ,p_rei_information4 => p_rei_information4
532 ,p_rei_information5 => p_rei_information5
533 ,p_rei_information6 => p_rei_information6
534 ,p_rei_information7 => p_rei_information7
535 ,p_rei_information8 => p_rei_information8
536 ,p_rei_information9 => p_rei_information9
537 ,p_rei_information10 => p_rei_information10
538 ,p_rei_information11 => p_rei_information11
539 ,p_rei_information12 => p_rei_information12
540 ,p_rei_information13 => p_rei_information13
541 ,p_rei_information14 => p_rei_information14
542 ,p_rei_information15 => p_rei_information15
543 ,p_rei_information16 => p_rei_information16
544 ,p_rei_information17 => p_rei_information17
545 ,p_rei_information18 => p_rei_information18
546 ,p_rei_information19 => p_rei_information19
547 ,p_rei_information20 => p_rei_information20
548 ,p_rei_information21 => p_rei_information21
549 ,p_rei_information22 => p_rei_information22
550 ,p_rei_information23 => p_rei_information23
551 ,p_rei_information24 => p_rei_information24
552 ,p_rei_information25 => p_rei_information25
553 ,p_rei_information26 => p_rei_information26
554 ,p_rei_information27 => p_rei_information27
555 ,p_rei_information28 => p_rei_information28
556 ,p_rei_information29 => p_rei_information29
557 ,p_rei_information30 => p_rei_information30
558 ,p_object_version_number => p_object_version_number
559 );
560 exception
561 when hr_api.cannot_find_prog_unit then
562 p_object_version_number := l_obj_version_number; -- NOCOPY Changes
563 hr_api.cannot_find_prog_unit_error
564 (p_module_name => 'update_par_extra_info',
565 p_hook_type => 'BP'
566 );
567 end;
568 --
569 -- End of Before Process User Hook call
570 --
571 hr_utility.set_location(l_proc, 7);
572 --
573 -- Store the original ovn in case we rollback when p_validate is true
574 --
575 l_object_version_number := p_object_version_number;
576 --
577 -- Validation
578 FOR l_cur_pa_req IN c_pa_req_id(p_pa_request_extra_info_id) LOOP
579 l_pa_req_id := l_cur_pa_req.pa_request_id;
580 l_information_type := l_cur_pa_req.information_type;
581 END LOOP;
582
583 FOR l_cur_noa_family_code IN c_noa_family_code(l_pa_req_id) LOOP
584 l_noa_family_code := l_cur_noa_family_code.noa_family_code;
585 END LOOP;
586
587 IF l_noa_family_code IN ('APP','CONV_APP','EXT_NTE') THEN
588 validate_extra_info
589 (p_pa_request_id => l_pa_req_id
590 ,p_information_type => l_information_type
591 ,p_rei_information_category => p_rei_information_category
592 ,p_rei_information1 => p_rei_information1
593 ,p_rei_information2 => p_rei_information2
594 ,p_rei_information3 => p_rei_information3
595 ,p_rei_information4 => p_rei_information4
596 ,p_rei_information5 => p_rei_information5
597 ,p_rei_information6 => p_rei_information6
598 ,p_rei_information7 => p_rei_information7
599 ,p_rei_information8 => p_rei_information8
600 ,p_rei_information9 => p_rei_information9
601 ,p_rei_information10 => p_rei_information10
602 ,p_rei_information11 => p_rei_information11
603 ,p_rei_information12 => p_rei_information12
604 ,p_rei_information13 => p_rei_information13
605 ,p_rei_information14 => p_rei_information14
606 ,p_rei_information15 => p_rei_information15
607 ,p_rei_information16 => p_rei_information16
608 ,p_rei_information17 => p_rei_information17
609 ,p_rei_information18 => p_rei_information18
610 ,p_rei_information19 => p_rei_information19
611 ,p_rei_information20 => p_rei_information20
612 ,p_rei_information21 => p_rei_information21
613 ,p_rei_information22 => p_rei_information22
614 ,p_rei_information23 => p_rei_information23
615 ,p_rei_information24 => p_rei_information24
616 ,p_rei_information25 => p_rei_information25
617 ,p_rei_information26 => p_rei_information26
618 ,p_rei_information27 => p_rei_information27
619 ,p_rei_information28 => p_rei_information28
620 ,p_rei_information29 => p_rei_information29
621 ,p_rei_information30 => p_rei_information30
622 ,p_ben_ei_validate => p_ben_ei_validate
623 );
624 END IF; -- IF l_noa_family_code = 'APP' THEN
625 --
626 -- Process Logic - Update pa_request Extra Info details
627 --
628
629
630 ghr_rei_upd.upd
631 (p_pa_request_extra_info_id => p_pa_request_extra_info_id
632 ,p_rei_attribute_category => p_rei_attribute_category
633 ,p_rei_attribute1 => p_rei_attribute1
634 ,p_rei_attribute2 => p_rei_attribute2
635 ,p_rei_attribute3 => p_rei_attribute3
636 ,p_rei_attribute4 => p_rei_attribute4
637 ,p_rei_attribute5 => p_rei_attribute5
638 ,p_rei_attribute6 => p_rei_attribute6
639 ,p_rei_attribute7 => p_rei_attribute7
640 ,p_rei_attribute8 => p_rei_attribute8
641 ,p_rei_attribute9 => p_rei_attribute9
642 ,p_rei_attribute10 => p_rei_attribute10
643 ,p_rei_attribute11 => p_rei_attribute11
644 ,p_rei_attribute12 => p_rei_attribute12
645 ,p_rei_attribute13 => p_rei_attribute13
646 ,p_rei_attribute14 => p_rei_attribute14
647 ,p_rei_attribute15 => p_rei_attribute15
648 ,p_rei_attribute16 => p_rei_attribute16
649 ,p_rei_attribute17 => p_rei_attribute17
650 ,p_rei_attribute18 => p_rei_attribute18
651 ,p_rei_attribute19 => p_rei_attribute19
652 ,p_rei_attribute20 => p_rei_attribute20
653 ,p_rei_information_category => p_rei_information_category
654 ,p_rei_information1 => p_rei_information1
655 ,p_rei_information2 => p_rei_information2
656 ,p_rei_information3 => p_rei_information3
657 ,p_rei_information4 => p_rei_information4
658 ,p_rei_information5 => p_rei_information5
659 ,p_rei_information6 => p_rei_information6
660 ,p_rei_information7 => p_rei_information7
661 ,p_rei_information8 => p_rei_information8
662 ,p_rei_information9 => p_rei_information9
663 ,p_rei_information10 => p_rei_information10
664 ,p_rei_information11 => p_rei_information11
665 ,p_rei_information12 => p_rei_information12
666 ,p_rei_information13 => p_rei_information13
667 ,p_rei_information14 => p_rei_information14
668 ,p_rei_information15 => p_rei_information15
669 ,p_rei_information16 => p_rei_information16
670 ,p_rei_information17 => p_rei_information17
671 ,p_rei_information18 => p_rei_information18
672 ,p_rei_information19 => p_rei_information19
673 ,p_rei_information20 => p_rei_information20
674 ,p_rei_information21 => p_rei_information21
675 ,p_rei_information22 => p_rei_information22
676 ,p_rei_information23 => p_rei_information23
677 ,p_rei_information24 => p_rei_information24
678 ,p_rei_information25 => p_rei_information25
679 ,p_rei_information26 => p_rei_information26
680 ,p_rei_information27 => p_rei_information27
681 ,p_rei_information28 => p_rei_information28
682 ,p_rei_information29 => p_rei_information29
683 ,p_rei_information30 => p_rei_information30
684 ,p_object_version_number => p_object_version_number
685 ,p_validate => false
686 );
687 --
688 hr_utility.set_location(l_proc, 8);
689 --
690 --
691 -- Call After Process User Hook
692 --
693 begin
694 ghr_par_extra_info_bk2.update_par_extra_info_a (
695 p_pa_request_extra_info_id => p_pa_request_extra_info_id
696 ,p_rei_attribute_category => p_rei_attribute_category
697 ,p_rei_attribute1 => p_rei_attribute1
698 ,p_rei_attribute2 => p_rei_attribute2
699 ,p_rei_attribute3 => p_rei_attribute3
700 ,p_rei_attribute4 => p_rei_attribute4
701 ,p_rei_attribute5 => p_rei_attribute5
702 ,p_rei_attribute6 => p_rei_attribute6
703 ,p_rei_attribute7 => p_rei_attribute7
704 ,p_rei_attribute8 => p_rei_attribute8
705 ,p_rei_attribute9 => p_rei_attribute9
706 ,p_rei_attribute10 => p_rei_attribute10
707 ,p_rei_attribute11 => p_rei_attribute11
708 ,p_rei_attribute12 => p_rei_attribute12
709 ,p_rei_attribute13 => p_rei_attribute13
710 ,p_rei_attribute14 => p_rei_attribute14
711 ,p_rei_attribute15 => p_rei_attribute15
712 ,p_rei_attribute16 => p_rei_attribute16
713 ,p_rei_attribute17 => p_rei_attribute17
714 ,p_rei_attribute18 => p_rei_attribute18
715 ,p_rei_attribute19 => p_rei_attribute19
716 ,p_rei_attribute20 => p_rei_attribute20
717 ,p_rei_information_category => p_rei_information_category
718 ,p_rei_information1 => p_rei_information1
719 ,p_rei_information2 => p_rei_information2
720 ,p_rei_information3 => p_rei_information3
721 ,p_rei_information4 => p_rei_information4
722 ,p_rei_information5 => p_rei_information5
723 ,p_rei_information6 => p_rei_information6
724 ,p_rei_information7 => p_rei_information7
725 ,p_rei_information8 => p_rei_information8
726 ,p_rei_information9 => p_rei_information9
727 ,p_rei_information10 => p_rei_information10
728 ,p_rei_information11 => p_rei_information11
729 ,p_rei_information12 => p_rei_information12
730 ,p_rei_information13 => p_rei_information13
731 ,p_rei_information14 => p_rei_information14
732 ,p_rei_information15 => p_rei_information15
733 ,p_rei_information16 => p_rei_information16
734 ,p_rei_information17 => p_rei_information17
735 ,p_rei_information18 => p_rei_information18
736 ,p_rei_information19 => p_rei_information19
737 ,p_rei_information20 => p_rei_information20
738 ,p_rei_information21 => p_rei_information21
739 ,p_rei_information22 => p_rei_information22
740 ,p_rei_information23 => p_rei_information23
741 ,p_rei_information24 => p_rei_information24
742 ,p_rei_information25 => p_rei_information25
743 ,p_rei_information26 => p_rei_information26
744 ,p_rei_information27 => p_rei_information27
745 ,p_rei_information28 => p_rei_information28
746 ,p_rei_information29 => p_rei_information29
747 ,p_rei_information30 => p_rei_information30
748 ,p_object_version_number => p_object_version_number
749
750 );
751 exception
752 when hr_api.cannot_find_prog_unit then
753 p_object_version_number := l_obj_version_number; -- NOCOPY Changes
754 hr_api.cannot_find_prog_unit_error
755 (p_module_name => 'update_par_extra_info',
756 p_hook_type => 'AP'
757 );
758 end;
759 --
760 -- End of After Process User Hook call
761 --
762 -- When in validation only mode raise the Validate_Enabled exception
763 --
764 if p_validate then
765 raise hr_api.validate_enabled;
766 end if;
767 --
768 hr_utility.set_location(' Leaving:'||l_proc, 11);
769 exception
770 when hr_api.validate_enabled then
771 --
772 -- As the Validate_Enabled exception has been raised
773 -- we must rollback to the savepoint
774 --
775 ROLLBACK TO update_pa_request_extra_info;
776 p_object_version_number := l_obj_version_number; -- NOCOPY Changes
777 --
778 -- Only set output warning arguments
779 -- (Any key or derived arguments must be set to null
780 -- when validation only mode is being used.)
781 --
782 p_object_version_number := l_object_version_number;
783
784 when others then
785 ROLLBACK TO update_pa_request_extra_info;
786 p_object_version_number := l_obj_version_number; -- NOCOPY Changes
787 raise;
788 --
789 hr_utility.set_location(' Leaving:'||l_proc, 12);
790 end update_pa_request_extra_info;
791 --
792 --
793 -- ----------------------------------------------------------------------------
794 -- |----------------------< delete_pa_request_extra_info >----------------------|
795 -- ----------------------------------------------------------------------------
796 --
797 procedure delete_pa_request_extra_info
798 (p_validate in boolean default false
799 ,p_pa_request_extra_info_id in number
800 ,p_object_version_number in number
801 ) is
802 --
803 -- Declare cursors and local variables
804 --
805 l_proc varchar2(72);
806 --
807 begin
808 l_proc := g_package||'delete_pa_request_extra_info';
809 hr_utility.set_location('Entering:'|| l_proc, 5);
810 --
811 savepoint delete_pa_request_extra_info;
812 --
813 --
814 -- Call Before Process User Hook
815 --
816 begin
817 ghr_par_extra_info_bk3.delete_par_extra_info_b (
818 p_pa_request_extra_info_id => p_pa_request_extra_info_id
819 ,p_object_version_number => p_object_version_number
820 );
821 exception
822 when hr_api.cannot_find_prog_unit then
823 hr_api.cannot_find_prog_unit_error
824 (p_module_name => 'delete_par_extra_info',
825 p_hook_type => 'BP'
826 );
827 end;
828 --
829 -- End of Before Process User Hook call
830 --
831 hr_utility.set_location(l_proc, 7);
832 --
833 -- Process Logic - Delete pa_request Extra Info details
834 --
835 ghr_rei_del.del
836 (p_pa_request_extra_info_id => p_pa_request_extra_info_id
837 ,p_object_version_number => p_object_version_number
838 ,p_validate => false
839 );
840 --
841 hr_utility.set_location(l_proc, 8);
842 --
843 --
844 -- Call After Process User Hook
845 --
846 begin
847 ghr_par_extra_info_bk3.delete_par_extra_info_a (
848 p_pa_request_extra_info_id => p_pa_request_extra_info_id
849 ,p_object_version_number => p_object_version_number
850 );
851 exception
852 when hr_api.cannot_find_prog_unit then
853 hr_api.cannot_find_prog_unit_error
854 (p_module_name => 'delete_par_extra_info',
855 p_hook_type => 'AP'
856 );
857 end;
858 --
859 -- End of After Process User Hook call
860 --
861 -- When in validation only mode raise the Validate_Enabled exception
862 --
863 if p_validate then
864 raise hr_api.validate_enabled;
865 end if;
866 --
867 hr_utility.set_location(' Leaving:'||l_proc, 11);
868 exception
869 when hr_api.validate_enabled then
870 --
871 -- As the Validate_Enabled exception has been raised
872 -- we must rollback to the savepoint
873 --
874 ROLLBACK TO delete_pa_request_extra_info;
875 --
876 when others then
877 ROLLBACK TO delete_pa_request_extra_info;
878 raise;
879
880 hr_utility.set_location(' Leaving:'||l_proc, 12);
881 end delete_pa_request_extra_info;
882 --
883 --
884
885 PROCEDURE validate_extra_info
886 (p_pa_request_id NUMBER
887 ,p_information_type VARCHAR2
888 ,p_rei_information_category VARCHAR2
889 ,p_rei_information1 VARCHAR2
890 ,p_rei_information2 VARCHAR2
891 ,p_rei_information3 VARCHAR2
892 ,p_rei_information4 VARCHAR2
893 ,p_rei_information5 VARCHAR2
894 ,p_rei_information6 VARCHAR2
895 ,p_rei_information7 VARCHAR2
896 ,p_rei_information8 VARCHAR2
897 ,p_rei_information9 VARCHAR2
898 ,p_rei_information10 VARCHAR2
899 ,p_rei_information11 VARCHAR2
900 ,p_rei_information12 VARCHAR2
901 ,p_rei_information13 VARCHAR2
902 ,p_rei_information14 VARCHAR2
903 ,p_rei_information15 VARCHAR2
904 ,p_rei_information16 VARCHAR2
905 ,p_rei_information17 VARCHAR2
906 ,p_rei_information18 VARCHAR2
907 ,p_rei_information19 VARCHAR2
908 ,p_rei_information20 VARCHAR2
909 ,p_rei_information21 VARCHAR2
910 ,p_rei_information22 VARCHAR2
911 ,p_rei_information23 VARCHAR2
912 ,p_rei_information24 VARCHAR2
913 ,p_rei_information25 VARCHAR2
914 ,p_rei_information26 VARCHAR2
915 ,p_rei_information27 VARCHAR2
916 ,p_rei_information28 VARCHAR2
917 ,p_rei_information29 VARCHAR2
918 ,p_rei_information30 VARCHAR2
919 ,p_ben_ei_validate varchar2 default 'FALSE') IS
920
921 CURSOR c_get_pa_req(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
922 SELECT *
923 FROM ghr_pa_requests
924 WHERE pa_request_id = c_pa_request_id;
925
926 CURSOR c_payroll_id(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
927 SELECT rei_information3 payroll_id
928 FROM ghr_pa_request_extra_info
929 WHERE pa_request_id = c_pa_request_id
930 AND information_type = 'GHR_US_PAR_PAYROLL_TYPE';
931
932 CURSOR c_start_date(c_payroll_id pay_payrolls_f.payroll_id%type, c_year varchar2, c_month varchar2) IS
933 SELECT min(start_date) start_date
934 FROM per_time_periods
935 WHERE payroll_id = c_payroll_id
936 AND TO_CHAR(start_date,'YYYY') = c_year
937 AND TO_CHAR(start_date,'MM') = c_month;
938
939
940 l_retirement_plan ghr_pa_requests.retirement_plan%type;
941 l_pa_request_id ghr_pa_requests.pa_request_id%type;
942 l_start_date per_time_periods.start_date%type;
943 l_effective_date ghr_pa_requests.effective_date%type;
944 l_first_noa_code ghr_pa_requests.first_noa_code%type;
945 l_tenure ghr_pa_requests.tenure%type;
946 l_annuitant_indicator ghr_pa_requests.annuitant_indicator%type;
947 l_assignment_id ghr_pa_requests.employee_assignment_id%type;
948
949 l_health_plan ghr_pa_request_extra_info.rei_information5%type;
950 l_enrollment_option ghr_pa_request_extra_info.rei_information6%type;
951 l_date_temp_elig date;
952 l_fegli ghr_pa_requests.fegli%type;
953 l_tsp_status ghr_pa_request_extra_info.rei_information15%type;
954 l_payroll_id ghr_pa_request_extra_info.rei_information3%type;
955 l_noa_family_code ghr_pa_requests.noa_family_code%type;
956 l_st_month VARCHAR2(20);
957 l_end_month VARCHAR2(20);
958 l_pay_month VARCHAR2(20);
959
960 BEGIN
961 -- Validation for US Federal Benefits
962 IF p_information_type = 'GHR_US_PAR_BENEFITS' THEN
963
964 IF p_ben_ei_validate = 'TRUE' THEN
965 -- Get PA Request Record details
966 hr_utility.set_location('---------------- Validation starts-------------------------------',1234);
967 FOR l_cur_pa_req IN c_get_pa_req(p_pa_request_id) LOOP
968 l_retirement_plan := l_cur_pa_req.retirement_plan;
969 l_pa_request_id := l_cur_pa_req.pa_request_id;
970 l_effective_date := l_cur_pa_req.effective_date;
971 l_first_noa_code := l_cur_pa_req.first_noa_code;
972 l_tenure := l_cur_pa_req.tenure;
973 l_fegli := l_cur_pa_req.fegli;
974 l_noa_family_code := l_cur_pa_req.noa_family_code;
975 l_annuitant_indicator := l_cur_pa_req.annuitant_indicator;
976 l_assignment_id := l_cur_pa_req.employee_assignment_id;
977 END LOOP;
978
979 l_health_plan := p_rei_information5;
980 l_enrollment_option := p_rei_information6;
981 l_date_temp_elig := fnd_date.canonical_to_date(p_rei_information4);
982 l_tsp_status := p_rei_information15;
983
984 GHR_BEN_VALIDATION.validate_benefits
985 (p_effective_date => l_effective_date,
986 p_which_eit => 'R',
987 p_pa_request_id => l_pa_request_id,
988 p_first_noa_code => l_first_noa_code,
989 p_noa_family_code => l_noa_family_code,
990 p_health_plan => l_health_plan,
991 p_enrollment_option => l_enrollment_option,
992 p_date_fehb_elig => fnd_date.canonical_to_date(p_rei_information3),
993 p_date_temp_elig => l_date_temp_elig,
994 p_temps_total_cost => p_rei_information8,
995 p_pre_tax_waiver => p_rei_information9,
996 p_tsp_scd => fnd_date.canonical_to_date(p_rei_information12),
997 p_tsp_amount => p_rei_information13,
998 p_tsp_rate => p_rei_information14,
999 p_tsp_status => p_rei_information15,
1000 p_tsp_status_date => fnd_date.canonical_to_date(p_rei_information16),
1001 p_agency_contrib_date => fnd_date.canonical_to_date(p_rei_information17),
1002 p_emp_contrib_date => fnd_date.canonical_to_date(p_rei_information18),
1003 p_tenure => l_tenure,
1004 p_retirement_plan => l_retirement_plan,
1005 p_fegli_elig_exp_date => fnd_date.canonical_to_date(p_rei_information10),
1006 p_fers_elig_exp_date => fnd_date.canonical_to_date(p_rei_information11),
1007 p_annuitant_indicator => l_annuitant_indicator,
1008 p_assignment_id => l_assignment_id,
1009 --Bug 12973371 Added TSP Roth Functionality Validations
1010 p_tsp_roth_401k_amt => p_rei_information19,
1011 p_tsp_roth_401k_rate => p_rei_information20
1012
1013 );
1014
1015
1016
1017
1018 END IF; -- IF p_ben_ei_validate = 'TRUE' THEN
1019 END IF; -- IF p_information_type = 'GHR_US_PAR_BENEFITS' THEN
1020 END validate_extra_info;
1021
1022 END ghr_par_extra_info_api;