[Home] [Help]
PACKAGE BODY: APPS.GHR_APPROVED_PA_REQUESTS
Source
1 PACKAGE BODY GHR_APPROVED_PA_REQUESTS AS
2 /* $Header: ghparapr.pkb 120.14.12010000.3 2008/08/26 10:03:38 vmididho ship $ */
3 g_package_name varchar2(31) := 'GHR_APPROVED_PA_REQUESTS.';
4 procedure create_pa_request_extra_info (
5 p_new_pa_request_id IN NUMBER
6 ,p_old_pa_request_id IN NUMBER)
7 IS
8 CURSOR c_rei IS
9 SELECT *
10 FROM ghr_pa_request_extra_info
11 WHERE pa_request_id = p_old_pa_request_id;
12 v_ovn NUMBER;
13 v_pa_request_extra_info_id NUMBER;
14 l_proc varchar2(61) := g_package_name || 'CREATE_PA_REQUEST_EXTRA_INFO';
15 BEGIN
16 hr_utility.set_location('Entering '|| l_proc, 10);
17 FOR r_rei IN c_rei LOOP
18 ghr_par_extra_info_api.create_pa_request_extra_info(
19 p_pa_request_id => p_new_pa_request_id
20 ,p_information_type => r_rei.information_type
21 ,p_rei_attribute_category => r_rei.rei_attribute_category
22 ,p_rei_attribute1 => r_rei.rei_attribute1
23 ,p_rei_attribute2 => r_rei.rei_attribute2
24 ,p_rei_attribute3 => r_rei.rei_attribute3
25 ,p_rei_attribute4 => r_rei.rei_attribute4
26 ,p_rei_attribute5 => r_rei.rei_attribute5
27 ,p_rei_attribute6 => r_rei.rei_attribute6
28 ,p_rei_attribute7 => r_rei.rei_attribute7
29 ,p_rei_attribute8 => r_rei.rei_attribute8
30 ,p_rei_attribute9 => r_rei.rei_attribute9
31 ,p_rei_attribute10 => r_rei.rei_attribute10
32 ,p_rei_attribute11 => r_rei.rei_attribute11
33 ,p_rei_attribute12 => r_rei.rei_attribute12
34 ,p_rei_attribute13 => r_rei.rei_attribute13
35 ,p_rei_attribute14 => r_rei.rei_attribute14
36 ,p_rei_attribute15 => r_rei.rei_attribute15
37 ,p_rei_attribute16 => r_rei.rei_attribute16
38 ,p_rei_attribute17 => r_rei.rei_attribute17
39 ,p_rei_attribute18 => r_rei.rei_attribute18
40 ,p_rei_attribute19 => r_rei.rei_attribute19
41 ,p_rei_attribute20 => r_rei.rei_attribute20
42 ,p_rei_information_category => r_rei.rei_information_category
43 ,p_rei_information1 => r_rei.rei_information1
44 ,p_rei_information2 => r_rei.rei_information2
45 ,p_rei_information3 => r_rei.rei_information3
46 ,p_rei_information4 => r_rei.rei_information4
47 ,p_rei_information5 => r_rei.rei_information5
48 ,p_rei_information6 => r_rei.rei_information6
49 ,p_rei_information7 => r_rei.rei_information7
50 ,p_rei_information8 => r_rei.rei_information8
51 ,p_rei_information9 => r_rei.rei_information9
52 ,p_rei_information10 => r_rei.rei_information10
53 ,p_rei_information11 => r_rei.rei_information11
54 ,p_rei_information12 => r_rei.rei_information12
55 ,p_rei_information13 => r_rei.rei_information13
56 ,p_rei_information14 => r_rei.rei_information14
57 ,p_rei_information15 => r_rei.rei_information15
58 ,p_rei_information16 => r_rei.rei_information16
59 ,p_rei_information17 => r_rei.rei_information17
60 ,p_rei_information18 => r_rei.rei_information18
61 ,p_rei_information19 => r_rei.rei_information19
62 ,p_rei_information20 => r_rei.rei_information20
63 ,p_rei_information21 => r_rei.rei_information21
64 ,p_rei_information22 => r_rei.rei_information22
65 ,p_rei_information23 => r_rei.rei_information23
66 ,p_rei_information24 => r_rei.rei_information24
67 ,p_rei_information25 => r_rei.rei_information25
68 ,p_rei_information26 => r_rei.rei_information26
69 ,p_rei_information27 => r_rei.rei_information27
70 ,p_rei_information28 => r_rei.rei_information28
71 ,p_rei_information29 => r_rei.rei_information29
72 ,p_rei_information30 => r_rei.rei_information30
73 ,p_pa_request_extra_info_id => v_pa_request_extra_info_id
74 ,p_object_version_number => v_ovn
75 );
76 END LOOP;
77 hr_utility.set_location('Exiting '|| l_proc, 100);
78 end;
79 -- -------------------------------------------------
80 PROCEDURE get_roles
81 -- -------------------------------------------------
82 (p_routing_group_id IN NUMBER
83 ,p_user_name IN VARCHAR2
84 ,p_initiator_flag IN OUT nocopy VARCHAR2
85 ,p_requester_flag IN OUT nocopy VARCHAR2
86 ,p_authorizer_flag IN OUT nocopy VARCHAR2
87 ,p_personnelist_flag IN OUT nocopy VARCHAR2
88 ,p_approver_flag IN OUT nocopy VARCHAR2
89 ,p_reviewer_flag IN OUT nocopy VARCHAR2
90 ) IS
91
92 l_proc varchar2(61) := g_package_name || 'GET_ROLES';
93 l_initiator_flag VARCHAR2(150);
94 l_requester_flag VARCHAR2(150);
95 l_authorizer_flag VARCHAR2(150);
96 l_personnelist_flag VARCHAR2(150);
97 l_approver_flag VARCHAR2(150);
98 l_reviewer_flag VARCHAR2(150);
99
100 CURSOR c_user_roles IS
101 SELECT pei.pei_information4 initiator_flag
102 ,pei.pei_information5 requester_flag
103 ,pei.pei_information6 authorizer_flag
104 ,pei.pei_information7 personnelist_flag
105 ,pei.pei_information8 approver_flag
106 ,pei.pei_information9 reviewer_flag
107 FROM per_people_extra_info pei
108 ,fnd_user usr
109 WHERE usr.user_name = p_user_name
110 AND pei.person_id = usr.employee_id
111 AND pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
112 AND pei.pei_information3 = p_routing_group_id;
113
114 BEGIN
115
116 l_initiator_flag := p_initiator_flag;
117 l_requester_flag := p_requester_flag;
118 l_authorizer_flag := p_authorizer_flag;
119 l_personnelist_flag := p_personnelist_flag;
120 l_approver_flag := p_approver_flag;
121 l_reviewer_flag := p_reviewer_flag;
122
123 hr_utility.set_location('Entering '|| l_proc, 5);
124
125 OPEN c_user_roles;
126 FETCH c_user_roles INTO
127 p_initiator_flag
128 ,p_requester_flag
129 ,p_authorizer_flag
130 ,p_personnelist_flag
131 ,p_approver_flag
132 ,p_reviewer_flag;
133
134 CLOSE c_user_roles;
135 hr_utility.set_location('Exiting '|| l_proc, 15);
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 p_initiator_flag := l_initiator_flag;
140 p_requester_flag := l_requester_flag;
141 p_authorizer_flag := l_authorizer_flag;
142 p_personnelist_flag := l_personnelist_flag;
143 p_approver_flag := l_approver_flag;
144 p_reviewer_flag := l_reviewer_flag;
145
146 END;
147
148 -- ---------------------------------
149 PROCEDURE can_cancel_or_correct(
150 -- ---------------------------------
151 p_pa_request_id in number
152 , p_which_noa in number
153 , p_row_id in varchar
154 , p_total_actions in out nocopy number
155 , p_corrections in out nocopy number
156 , p_rpas in out nocopy number
157 )
158 IS
159 l_proc varchar2(61) := g_package_name || 'CAN_CANCEL_OR_CORRECT';
160 l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
161 l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
162
163
164 --5725885 vmididho to improve performance
165 --5725885 modified the cursor instead of using view changed to use the query getting
166 -- a particular person records to improve the performance
167 /* CURSOR c_pa_requests(p_person_id IN NUMBER
168 -- , p_pa_request_id IN NUMBER
169 , p_effective_date IN DATE) IS
170 --SELECT *
171 SELECT PA_NOTIFICATION_ID
172 FROM GHR_PA_REQUESTS_V1
173 WHERE person_id = p_person_id
174 AND effective_date >= p_effective_date;*/
175
176 CURSOR c_pa_requests(p_person_id IN NUMBER)
177 IS
178 select PA_NOTIFICATION_ID,effective_date
179 from ghr_pa_requests par
180 where (level = 1 and pa_notification_id is not null) or (level > 1 and ( nvl(status, 'CANCELED') <> 'CANCELED'
181 AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL' AND first_noa_code <> '001' ) )
182 start with altered_pa_request_id is null
183 and person_id = p_person_id
184 and NVL(first_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
185 and first_noa_canc_pa_request_id is null
186 connect by prior pa_request_id = altered_pa_request_id
187 and
188 prior decode(first_noa_code, '002', second_noa_code, '001' , second_noa_code ,first_noa_code) = second_noa_code
189 UNION ALL
190 select PA_NOTIFICATION_ID,effective_date
191 from ghr_pa_requests par
192 where (level = 1 and pa_notification_id is not null)
193 or (level > 1 and ( nvl(status, 'CANCELED') <> 'CANCELED'
194 AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL' AND first_noa_code <> '001' ) )
195 start with altered_pa_request_id is null
196 and person_id = p_person_id
197 and NVL(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
198 and second_noa_code is not null and second_noa_canc_pa_request_id is null
199 connect by prior pa_request_id = altered_pa_request_id
200 and
201 prior second_noa_code = second_noa_code;
202
203 -- Bug# 4005884 Added cursor to verify the prior person type.
204 Cursor c_prior_person_type(p_Person_id NUMBER, p_effective_Date DATE) is
205 select 'EX_EMP' system_person_type
206 from ghr_pa_requests pa
207 where pa.noa_family_code = 'SEPARATION'
208 and pa.effective_date < p_effective_date
209 and pa.person_id = p_person_id
210 and exists (select '1'
211 from ghr_pa_history pah
212 where pah.pa_request_id = pa.pa_request_id);
213
214 l_system_type per_person_types.system_person_type%type;
215 l_total_actions number;
216 l_corrections number;
217 l_rpas number;
218 BEGIN
219 hr_utility.set_location('Entering '|| l_proc, 5);
220 l_total_actions := p_total_actions;
221 l_corrections := p_corrections;
222 l_rpas := p_rpas;
223 p_total_actions := 0;
224 p_rpas := 0;
225 p_corrections := 0;
226 find_last_request(p_pa_request_id => p_pa_request_id
227 , p_which_noa => p_which_noa
228 , p_row_id => p_row_id
229 , p_first_pa_request_rec => l_first_pa_request_rec
230 , p_last_pa_request_rec => l_last_pa_request_rec
231 , p_number_of_requests => p_corrections);
232 IF l_last_pa_request_rec.pa_notification_id IS NULL THEN
233 p_corrections := -1;
234 return;
235 END IF;
236 --
237 --
238 -- Bug# 4005884
239 If l_first_pa_request_rec.noa_family_code = 'CONV_APP' then
240 -- check to see if the person was an EX_EMP prior to the effective date of this action.
241 for prior_person_type in c_prior_person_type(l_first_pa_request_rec.person_id,
242 l_first_pa_request_rec.effective_date)
243 loop
244 l_system_type := prior_person_type.system_person_type;
245 exit;
246 end loop;
247 End if;
248 -- Bug# 1295751, Added CONV_APP to get subsequent actions
249 -- Bug# 4005884 Modified the CONV_APP condition to populate message only in case of PRIOR EX_EMP.
250 IF (l_first_pa_request_rec.noa_family_code = 'APP' OR
251 (l_first_pa_request_rec.noa_family_code = 'CONV_APP' AND l_system_type = 'EX_EMP')
252 )THEN
253 -- Get all subsequent action
254 FOR r_pa_requests IN c_pa_requests(p_person_id => l_first_pa_request_rec.person_id)
255 -- , p_pa_request_id => l_first_pa_request_rec.pa_request_id
256 -- , p_effective_date => l_first_pa_request_rec.effective_date)
257 LOOP
258 --5725885 modified to compare effective date inside the loop
259 IF r_pa_requests.effective_date >= l_first_pa_request_rec.effective_date then
260 p_total_actions := p_total_actions + 1;
261
262 IF r_pa_requests.pa_notification_id IS NULL THEN
263 p_rpas := p_rpas + 1;
264 END IF;
265 END IF;
266 END LOOP;
267 END IF;
268 /*
269 p_total_actions := p_total_actions - (p_rpas + p_corrections + 1);
270 IF p_total_actions > 0 THEN
271 IF p_corrections > 0 THEN
272 fnd_message.set_name('GHR', 'GHR_CANCEL_APP_WITH_CORRECTION');
273 fnd_message.set_token('CORRECT', p_corrections);
274 fnd_message.set_token('ORIGINAL', p_total_actions);
275 ELSE
276 fnd_message.set_name('GHR', 'GHR_CANCEL_APP');
277 fnd_message.set_token('ORIGINAL', p_total_actions);
278 END IF;
279 ELSIF p_corrections > 0 THEN
280 fnd_message.set_name('GHR', 'GHR_CANCEL_ANY_ACTION');
281 fnd_message.set_token('CORRECT', p_corrections);
282 END IF;
283 */
284
285 hr_utility.set_location(l_proc || ' First Pa Request ID : ' || to_char(l_first_pa_request_rec.pa_request_id), 10);
286 hr_utility.set_location(l_proc || ' Last Pa Request ID : ' || to_char(l_last_pa_request_rec.pa_request_id), 20);
287 hr_utility.set_location('Exiting '|| l_proc, 500);
288 EXCEPTION
289 WHEN OTHERS THEN
290 p_total_actions := l_total_actions;
291 p_corrections := l_corrections;
292 p_rpas := l_rpas;
293 END;
294
295 -- ---------------------------------
296 PROCEDURE find_last_request(
297 -- ---------------------------------
298 p_pa_request_id in number
299 , p_which_noa in number
300 , p_row_id in varchar
301 , p_first_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
302 , p_last_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
303 , p_number_of_requests in out nocopy number
304 )
305 is
306 l_proc varchar2(61) := g_package_name || 'FIND_LAST_REQUEST';
307 l_pa_req GHR_PA_REQUESTS%ROWTYPE;
308
309 -- 5925784 The below cursor is splitted into two due to UnionALL performance in 10g
310 /* CURSOR c_get_last_request IS
311 SELECT
312 effective_date
313 , DECODE(first_noa_code, '002', second_noa_code
314 , '001', second_noa_code
315 , first_noa_code) noa_code
316 , ROWNUM row_num
317 , LEVEL hierarchy_level
318 , pa_request_id
319 , pa_notification_id
320 , approval_date
321 , person_id
322 , employee_assignment_id
323 , 1 WHICH_NOA
324 , ROWID ROW_ID
325 , DECODE(pa_notification_id, NULL, 'Routed', 'Processed') action_type
326 , altered_pa_request_id
327 , status
328 FROM ghr_pa_requests par
329 WHERE (LEVEL = 1 and pa_notification_id IS NOT NULL)
330 or (level > 1
331 and ( nvl(status, 'CANCELED') <> 'CANCELED'
332 AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
333 AND first_noa_code <> '001'
334 )
335 )
336 START WITH altered_pa_request_id IS NULl
337 AND NVL(first_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
338 AND ROWID = CHARTOROWID(p_row_id)
339 AND p_which_noa = 1
340 AND first_noa_canc_pa_request_id IS NULL
341 CONNECT BY PRIOR pa_request_id = altered_pa_request_id
342 AND PRIOR DECODE(first_noa_code, '002', second_noa_code, '001', second_noa_code ,first_noa_code) = second_noa_code
343 UNION ALL
344 SELECT
345 effective_date
346 , second_noa_code
347 , ROWNUM
348 , LEVEL
349 , pa_request_id
350 , pa_notification_id
351 , approval_date
352 , par.person_id
353 , par.employee_assignment_id
354 , 2 which_noa
355 , par.ROWID
356 , DECODE(pa_notification_id, NULL, 'Routed', 'Processed')
357 , altered_pa_request_id
358 , par.status
359 FROM ghr_pa_requests par
360 WHERE (LEVEL = 1 AND pa_notification_id IS NOT NULL)
361 or (level > 1
362 and ( nvl(status, 'CANCELED') <> 'CANCELED'
363 AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
364 AND first_noa_code <> '001'
365 )
366 )
367 START WITH altered_pa_request_id IS NULL
368 AND NVL(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
369 AND second_noa_code IS NOT NULL
370 AND ROWID = chartorowid(p_row_id)
371 AND p_which_noa = 2
372 AND second_noa_canc_pa_request_id IS NULL
373 CONNECT BY PRIOR pa_request_id = altered_pa_request_id
374 AND PRIOR second_noa_code = second_noa_code
375 ORDER BY 1, 2, 3;*/
376
377
378 CURSOR c_get_last_request_1 IS
379 SELECT
380 effective_date
381 , DECODE(first_noa_code, '002', second_noa_code
382 , '001', second_noa_code
383 , first_noa_code) noa_code
384 , ROWNUM row_num
385 , LEVEL hierarchy_level
386 , pa_request_id
387 , pa_notification_id
388 , approval_date
389 , person_id
390 , employee_assignment_id
391 , 1 WHICH_NOA
392 , ROWID ROW_ID
393 , DECODE(pa_notification_id, NULL, 'Routed', 'Processed') action_type
394 , altered_pa_request_id
395 , status
396 FROM ghr_pa_requests par
397 WHERE (LEVEL = 1 and pa_notification_id IS NOT NULL)
398 or (level > 1
399 and ( nvl(status, 'CANCELED') <> 'CANCELED'
400 AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
401 AND first_noa_code <> '001'
402 )
403 )
404 START WITH altered_pa_request_id IS NULl
405 AND NVL(first_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
406 AND ROWID = CHARTOROWID(p_row_id)
407 -- AND p_which_noa = 1
408 AND first_noa_canc_pa_request_id IS NULL
409 CONNECT BY PRIOR pa_request_id = altered_pa_request_id
410 AND PRIOR DECODE(first_noa_code, '002', second_noa_code, '001', second_noa_code ,first_noa_code) = second_noa_code
411 ORDER BY 1, 2, 3;
412
413 CURSOR c_get_last_request_2 IS
414 SELECT
415 effective_date
416 , second_noa_code
417 , ROWNUM
418 , LEVEL
419 , pa_request_id
420 , pa_notification_id
421 , approval_date
422 , par.person_id
423 , par.employee_assignment_id
424 , 2 which_noa
425 , par.ROWID
426 , DECODE(pa_notification_id, NULL, 'Routed', 'Processed')
427 , altered_pa_request_id
428 , par.status
429 FROM ghr_pa_requests par
430 WHERE (LEVEL = 1 AND pa_notification_id IS NOT NULL)
431 or (level > 1
432 and ( nvl(status, 'CANCELED') <> 'CANCELED'
433 AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
434 AND first_noa_code <> '001'
435 )
436 )
437 START WITH altered_pa_request_id IS NULL
438 AND NVL(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
439 AND second_noa_code IS NOT NULL
440 AND ROWID = chartorowid(p_row_id)
441 -- AND p_which_noa = 2
442 AND second_noa_canc_pa_request_id IS NULL
443 CONNECT BY PRIOR pa_request_id = altered_pa_request_id
444 AND PRIOR second_noa_code = second_noa_code
445 ORDER BY 1, 2, 3;
446
447 --
448 CURSOR c_pa_request(p_rowid IN ROWID) IS
449 SELECT *
450 FROM ghr_pa_requests
451 WHERE ROWID = p_rowid;
452 --
453 l_last_row c_get_last_request_1%ROWTYPE;
454 l_first_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
455 l_last_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
456 l_number_of_requests number;
457
458 BEGIN
459
460 hr_utility.set_location('Entering '|| l_proc, 5);
461 l_first_pa_request_rec := p_first_pa_request_rec;
462 l_last_pa_request_rec := p_last_pa_request_rec;
463 l_number_of_requests := p_number_of_requests;
464
465 OPEN c_pa_request(chartorowid(p_row_id));
466 FETCH c_pa_request INTO p_first_pa_request_rec;
467 CLOSE c_pa_request;
468 hr_utility.set_location(l_proc || ' First PA Request id ' || p_first_pa_request_rec.pa_request_id, 15);
469 hr_utility.set_location(l_proc || ' First Family Code ' || p_first_pa_request_rec.noa_family_code, 25);
470 hr_utility.set_location(l_proc || ' First Person Id ' || p_first_pa_request_rec.person_id, 35);
471 hr_utility.set_location(l_proc || ' First ROW Id ' || rowidtochar(p_row_id), 40);
472 hr_utility.set_location(l_proc || ' Which NOA ' || p_which_noa, 43);
473
474 p_number_of_requests := -1;
475 /*
476 FOR r_get_last_request IN c_get_last_request LOOP
477 p_number_of_requests := p_number_of_requests + 1;
478 l_last_row := r_get_last_request;
479 END LOOP;*/
480
481 -- 5925784 Code change related to the performance issue on UNION ALL
482 -- breaking single into two different queries
483
484 If p_which_noa = 1 then
485 FOR r_get_last_request IN c_get_last_request_1 LOOP
486 p_number_of_requests := p_number_of_requests + 1;
487 l_last_row := r_get_last_request;
488 END LOOP;
489 Elsif p_which_noa = 2 then
490 FOR r_get_last_request IN c_get_last_request_2 LOOP
491 p_number_of_requests := p_number_of_requests + 1;
492 l_last_row := r_get_last_request;
493 END LOOP;
494 End If;
495
496 OPEN c_pa_request(l_last_row.row_id);
497 FETCH c_pa_request INTO p_last_pa_request_rec;
498 CLOSE c_pa_request;
499 hr_utility.set_location(l_proc || ' last PA Request id ' || p_last_pa_request_rec.pa_request_id, 45);
500 hr_utility.set_location(l_proc || ' Last ROW Id ' || rowidtochar(l_last_row.row_id), 55);
501 hr_utility.set_location('Exiting '|| l_proc, 500);
502 EXCEPTION
503 WHEN OTHERS THEN
504 p_first_pa_request_rec := l_first_pa_request_rec;
505 p_last_pa_request_rec := l_last_pa_request_rec;
506 p_number_of_requests := l_number_of_requests;
507
508 END;
509
510 ------------------------
511 -- Cancelation
512 ------------------------
513 function ghr_cancel_sf52 (
514 p_pa_request_id in number
515 , p_par_object_version_number in out nocopy number
516 , p_noa_id in number
517 , p_which_noa in number
518 , p_row_id in varchar2
519 , p_username in varchar2
520 , p_which_action in varchar2 default 'SUBSEQUENT'
521 , p_cancel_legal_authority in varchar2)
522 return number
523 is
524 --
525 l_proc varchar2(61) := g_package_name || 'GHR_CANCEL_SF52';
526 l_pa_req_rec ghr_pa_requests%rowtype;
527 l_par_object_version_number number;
528 l_1_pa_routing_history_id number;
529 l_1_prh_object_version_number number;
530 l_2_pa_routing_history_id number;
531 l_2_prh_object_version_number number;
532 l_noa_cancel_or_correct varchar2(10);
533 l_U_PRH_OBJECT_VERSION_NUMBER number;
534 l_i_pa_routing_history_id number;
535 L_I_PRH_OBJECT_VERSION_NUMBER number;
536 l_par_object_version_number1 number;
537 l_dummy number;
538 --
539 cursor c_pa_req is
540 select *
541 from ghr_pa_requests
542 where rowid = chartorowid(p_row_id);
543 --
544 cursor C_noa_id(p_noa_code varchar2, p_effective_date date) is
545 select nature_of_action_id, description
546 from ghr_nature_of_actions
547 where code = p_noa_code
548 and p_effective_date between date_from and nvl(date_to, p_effective_date);
549 --
550 cursor c_noa_code (p_noa_id number)is
551 select code
552 from ghr_nature_of_actions where nature_of_action_id = p_noa_id;
553 --
554 -- Added this cursor for bug # 2951865 to check if the person is persent in the system !!
555 CURSOR chk_person ( p_person_id NUMBER ) IS
556 SELECT person_id
557 FROM per_all_people_f
558 WHERE person_id = p_person_id ;
559 --
560 v_old_pa_request_id NUMBER;
561 --
562 begin
563 hr_utility.set_location('Entering '|| l_proc, 5);
564 l_par_object_version_number1 := p_par_object_version_number;
565
566 -- Get PA Request
567 open c_pa_req;
568 fetch c_pa_req into l_pa_req_rec;
569 close c_pa_req;
570 -- -------------------------------------------
571 -- Populate Second Noa Detail
572 -- -------------------------------------------
573 if p_which_noa = 1 then
574 l_pa_req_rec.second_action_la_code1 := l_pa_req_rec.first_action_la_code1;
575 l_pa_req_rec.second_action_la_code2 := l_pa_req_rec.first_action_la_code2;
576 l_pa_req_rec.second_action_la_desc1 := l_pa_req_rec.first_action_la_desc1;
577 l_pa_req_rec.second_action_la_desc2 := l_pa_req_rec.first_action_la_desc2;
578 l_pa_req_rec.second_noa_code := l_pa_req_rec.first_noa_code;
579 l_pa_req_rec.second_noa_desc := l_pa_req_rec.first_noa_desc;
580 l_pa_req_rec.second_noa_id := l_pa_req_rec.first_noa_id;
581 -- Bug#5036997 Added the information columns
582 l_pa_req_rec.second_lac1_information1 := l_pa_req_rec.first_lac1_information1;
583 l_pa_req_rec.second_lac1_information2 := l_pa_req_rec.first_lac1_information2;
584 l_pa_req_rec.second_lac2_information1 := l_pa_req_rec.first_lac2_information1;
585 l_pa_req_rec.second_lac2_information2 := l_pa_req_rec.first_lac2_information2;
586 end if;
587 -- -------------------------------------------
588 -- Populate First Noa Detail
589 -- -------------------------------------------
590 -- Cancellation NOA
591 l_pa_req_rec.first_noa_code := '001';
592 -- Get NOA Id
593 Open c_noa_id(l_pa_req_rec.first_noa_code, l_pa_req_rec.effective_date);
594 fetch c_noa_id into l_pa_req_rec.first_noa_id, l_pa_req_rec.first_noa_desc;
595 close c_noa_id;
596 --
597 hr_utility.set_location('First NOA Id '|| to_char(l_pa_req_rec.first_noa_id), 10);
598 --
599 l_pa_req_rec.first_action_la_code1 := p_cancel_legal_authority;
600 l_pa_req_rec.first_action_la_code2 := null;
601 l_pa_req_rec.first_action_la_desc1 := null;
602 l_pa_req_rec.first_action_la_desc2 := null;
603 v_old_pa_request_id := l_pa_req_rec.pa_request_id;
604 l_pa_req_rec.pa_request_id := null;
605 l_pa_req_rec.second_noa_id := p_noa_id;
606
607 open c_noa_code(p_noa_id);
608 fetch c_noa_code into l_pa_req_rec.second_noa_code;
609 close c_noa_code;
610 --
611 l_pa_req_rec.altered_pa_request_id := p_pa_request_id;
612 l_pa_req_rec.noa_family_code :=
613 ghr_pa_requests_pkg.get_noa_pm_family(l_pa_req_rec.first_noa_id);
614 -- l_pa_req_rec.notification_id := null;
615 --
616 -- Added these checks for bug #2951865
617 -- Check if additional_info person is present
618
619 OPEN chk_person(l_pa_req_rec.additional_info_person_id );
620 FETCH chk_person INTO l_dummy;
621 IF chk_person%NOTFOUND THEN
622 l_pa_req_rec.additional_info_person_id :=NULL;
623 l_pa_req_rec.additional_info_tel_number :=NULL ;
624 END IF;
625 CLOSE chk_person;
626
627 -- Check if Authorizer is present
628
629 OPEN chk_person(l_pa_req_rec.authorized_by_person_id );
630 FETCH chk_person INTO l_dummy;
631 IF chk_person%NOTFOUND THEN
632 l_pa_req_rec.authorized_by_person_id :=NULL;
633 l_pa_req_rec.authorized_by_title :=NULL ;
634 END IF;
635 CLOSE chk_person;
636
637 ---- Check if Requester is present
638
639 OPEN chk_person(l_pa_req_rec.requested_by_person_id );
640 FETCH chk_person INTO l_dummy;
641 IF chk_person%NOTFOUND THEN
642 l_pa_req_rec.requested_by_person_id :=NULL;
643 l_pa_req_rec.requested_by_title :=NULL ;
644 END IF;
645 CLOSE chk_person;
646
647 ---END Bug # 2951865
648
649 l_pa_req_rec.custom_pay_calc_flag := 'N';
650 --
651 hr_utility.set_location('Creating SF52 - p_username '||p_username || '-'|| l_proc, 15);
652 --
653 Ghr_sf52_api.create_sf52(
654 p_noa_family_code => l_pa_req_rec.noa_family_code
655 , p_routing_group_id => l_pa_req_rec.routing_group_id
656 , p_proposed_effective_asap_flag => l_pa_req_rec.proposed_effective_asap_flag
657 , p_academic_discipline => l_pa_req_rec.academic_discipline
658 , p_additional_info_person_id => l_pa_req_rec.additional_info_person_id
659 , p_additional_info_tel_number => l_pa_req_rec.additional_info_tel_number
660 , p_altered_pa_request_id => l_pa_req_rec.altered_pa_request_id
661 , p_annuitant_indicator => l_pa_req_rec.annuitant_indicator
662 , p_annuitant_indicator_desc => l_pa_req_rec.annuitant_indicator_desc
663 , p_appropriation_code1 => l_pa_req_rec.appropriation_code1
664 , p_appropriation_code2 => l_pa_req_rec.appropriation_code2
665 , p_authorized_by_person_id => l_pa_req_rec.authorized_by_person_id
666 , p_authorized_by_title => l_pa_req_rec.authorized_by_title
667 , p_award_amount => l_pa_req_rec.award_amount
668 , p_award_uom => l_pa_req_rec.award_uom
669 , p_bargaining_unit_status => l_pa_req_rec.bargaining_unit_status
670 , p_citizenship => l_pa_req_rec.citizenship
671 , p_concurrence_date => l_pa_req_rec.concurrence_date
672 , p_custom_pay_calc_flag => l_pa_req_rec.custom_pay_calc_flag
673 , p_duty_station_code => l_pa_req_rec.duty_station_code
674 , p_duty_station_desc => l_pa_req_rec.duty_station_desc
675 , p_duty_station_id => l_pa_req_rec.duty_station_id
676 , p_duty_station_location_id => l_pa_req_rec.duty_station_location_id
677 , p_education_level => l_pa_req_rec.education_level
678 , p_effective_date => l_pa_req_rec.effective_date
679 , p_employee_assignment_id => l_pa_req_rec.employee_assignment_id
680 , p_employee_date_of_birth => l_pa_req_rec.employee_date_of_birth
681 , p_employee_first_name => l_pa_req_rec.employee_first_name
682 , p_employee_last_name => l_pa_req_rec.employee_last_name
683 , p_employee_middle_names => l_pa_req_rec.employee_middle_names
684 , p_employee_national_identifier => l_pa_req_rec.employee_national_identifier
685 , p_fegli => l_pa_req_rec.fegli
686 , p_fegli_desc => l_pa_req_rec.fegli_desc
687 , p_first_action_la_code1 => l_pa_req_rec.first_action_la_code1
688 , p_first_action_la_code2 => l_pa_req_rec.first_action_la_code2
689 , p_first_action_la_desc1 => l_pa_req_rec.first_action_la_desc1
690 , p_first_action_la_desc2 => l_pa_req_rec.first_action_la_desc2
691 -- , p_first_noa_cancel_or_correct => l_pa_req_rec.first_noa_cancel_or_correct
692 , p_first_noa_code => l_pa_req_rec.first_noa_code
693 , p_first_noa_desc => l_pa_req_rec.first_noa_desc
694 , p_first_noa_id => l_pa_req_rec.first_noa_id
695 -- , p_first_noa_pa_request_id => l_pa_req_rec.first_noa_pa_request_id
696 , p_flsa_category => l_pa_req_rec.flsa_category
697 , p_forwarding_address_line1 => l_pa_req_rec.forwarding_address_line1
698 , p_forwarding_address_line2 => l_pa_req_rec.forwarding_address_line2
699 , p_forwarding_address_line3 => l_pa_req_rec.forwarding_address_line3
700 , p_forwarding_country => l_pa_req_rec.forwarding_country
701 , p_forwarding_country_short_nam => l_pa_req_rec.forwarding_country_short_name
702 , p_forwarding_postal_code => l_pa_req_rec.forwarding_postal_code
703 , p_forwarding_region_2 => l_pa_req_rec.forwarding_region_2
704 , p_forwarding_town_or_city => l_pa_req_rec.forwarding_town_or_city
705 , p_from_adj_basic_pay => l_pa_req_rec.from_adj_basic_pay
706 , p_from_basic_pay => l_pa_req_rec.from_basic_pay
707 , p_from_grade_or_level => l_pa_req_rec.from_grade_or_level
708 , p_from_locality_adj => l_pa_req_rec.from_locality_adj
709 , p_from_occ_code => l_pa_req_rec.from_occ_code
710 , p_from_other_pay_amount => l_pa_req_rec.from_other_pay_amount
711 , p_from_pay_basis => l_pa_req_rec.from_pay_basis
712 , p_from_pay_plan => l_pa_req_rec.from_pay_plan
713 -- FWFA Changes Bug#4444609
714 , p_input_pay_rate_determinant => l_pa_req_rec.input_pay_rate_determinant
715 , p_from_pay_table_identifier => l_pa_req_rec.from_pay_table_identifier
716 -- FWFA Changes
717 , p_from_position_id => l_pa_req_rec.from_position_id
718 , p_from_position_org_line1 => l_pa_req_rec.from_position_org_line1
719 , p_from_position_org_line2 => l_pa_req_rec.from_position_org_line2
720 , p_from_position_org_line3 => l_pa_req_rec.from_position_org_line3
721 , p_from_position_org_line4 => l_pa_req_rec.from_position_org_line4
722 , p_from_position_org_line5 => l_pa_req_rec.from_position_org_line5
723 , p_from_position_org_line6 => l_pa_req_rec.from_position_org_line6
724 , p_from_position_number => l_pa_req_rec.from_position_number
725 , p_from_position_seq_no => l_pa_req_rec.from_position_seq_no
726 , p_from_position_title => l_pa_req_rec.from_position_title
727 , p_from_step_or_rate => l_pa_req_rec.from_step_or_rate
728 , p_from_total_salary => l_pa_req_rec.from_total_salary
729 , p_functional_class => l_pa_req_rec.functional_class
730 , p_notepad => l_pa_req_rec.notepad
731 , p_part_time_hours => l_pa_req_rec.part_time_hours
732 , p_pay_rate_determinant => l_pa_req_rec.pay_rate_determinant
733 , p_person_id => l_pa_req_rec.person_id
734 , p_position_occupied => l_pa_req_rec.position_occupied
735 , p_proposed_effective_date => l_pa_req_rec.proposed_effective_date
736 , p_requested_by_person_id => l_pa_req_rec.requested_by_person_id
737 , p_requested_by_title => l_pa_req_rec.requested_by_title
738 , p_requested_date => l_pa_req_rec.requested_date
739 , p_requesting_office_remarks_de => l_pa_req_rec.requesting_office_remarks_desc
740 , p_requesting_office_remarks_fl => l_pa_req_rec.requesting_office_remarks_flag
741 , p_request_number => l_pa_req_rec.request_number
742 , p_resign_and_retire_reason_des => l_pa_req_rec.resign_and_retire_reason_desc
743 , p_retirement_plan => l_pa_req_rec.retirement_plan
744 , p_retirement_plan_desc => l_pa_req_rec.retirement_plan_desc
745 , p_second_action_la_code1 => l_pa_req_rec.second_action_la_code1
746 , p_second_action_la_code2 => l_pa_req_rec.second_action_la_code2
747 , p_second_action_la_desc1 => l_pa_req_rec.second_action_la_desc1
748 , p_second_action_la_desc2 => l_pa_req_rec.second_action_la_desc2
749 -- , p_second_noa_cancel_or_correct => l_pa_req_rec.second_noa_cancel_or_correct
750 , p_second_noa_code => l_pa_req_rec.second_noa_code
751 , p_second_noa_desc => l_pa_req_rec.second_noa_desc
752 , p_second_noa_id => l_pa_req_rec.second_noa_id
753 -- , p_second_noa_pa_request_id => l_pa_req_rec.second_noa_pa_request_id
754 , p_service_comp_date => l_pa_req_rec.service_comp_date
755 , p_supervisory_status => l_pa_req_rec.supervisory_status
756 , p_tenure => l_pa_req_rec.tenure
757 , p_to_adj_basic_pay => l_pa_req_rec.to_adj_basic_pay
758 , p_to_basic_pay => l_pa_req_rec.to_basic_pay
759 , p_to_grade_id => l_pa_req_rec.to_grade_id
760 , p_to_grade_or_level => l_pa_req_rec.to_grade_or_level
761 , p_to_job_id => l_pa_req_rec.to_job_id
762 , p_to_locality_adj => l_pa_req_rec.to_locality_adj
763 , p_to_occ_code => l_pa_req_rec.to_occ_code
764 , p_to_organization_id => l_pa_req_rec.to_organization_id
765 , p_to_other_pay_amount => l_pa_req_rec.to_other_pay_amount
766 , p_to_au_overtime => l_pa_req_rec.to_au_overtime
767 , p_to_auo_premium_pay_indicator => l_pa_req_rec.to_auo_premium_pay_indicator
768 , p_to_availability_pay => l_pa_req_rec.to_availability_pay
769 , p_to_ap_premium_pay_indicator => l_pa_req_rec.to_ap_premium_pay_indicator
770 , p_to_retention_allowance => l_pa_req_rec.to_retention_allowance
771 , p_to_supervisory_differential => l_pa_req_rec.to_supervisory_differential
772 , p_to_staffing_differential => l_pa_req_rec.to_staffing_differential
773 , p_to_pay_basis => l_pa_req_rec.to_pay_basis
774 , p_to_pay_plan => l_pa_req_rec.to_pay_plan
775 -- FWFA Changes Bug#4444609
776 , p_to_pay_table_identifier => l_pa_req_rec.to_pay_table_identifier
777 -- FWFA Changes
778 , p_to_position_id => l_pa_req_rec.to_position_id
779 , p_to_position_org_line1 => l_pa_req_rec.to_position_org_line1
780 , p_to_position_org_line2 => l_pa_req_rec.to_position_org_line2
781 , p_to_position_org_line3 => l_pa_req_rec.to_position_org_line3
782 , p_to_position_org_line4 => l_pa_req_rec.to_position_org_line4
783 , p_to_position_org_line5 => l_pa_req_rec.to_position_org_line5
784 , p_to_position_org_line6 => l_pa_req_rec.to_position_org_line6
785 , p_to_position_number => l_pa_req_rec.to_position_number
786 , p_to_position_seq_no => l_pa_req_rec.to_position_seq_no
787 , p_to_position_title => l_pa_req_rec.to_position_title
788 , p_to_step_or_rate => l_pa_req_rec.to_step_or_rate
789 , p_to_total_salary => l_pa_req_rec.to_total_salary
790 , p_veterans_preference => l_pa_req_rec.veterans_preference
791 , p_veterans_pref_for_rif => l_pa_req_rec.veterans_pref_for_rif
792 , p_veterans_status => l_pa_req_rec.veterans_status
793 , p_work_schedule => l_pa_req_rec.work_schedule
794 , p_work_schedule_desc => l_pa_req_rec.work_schedule_desc
795 , p_year_degree_attained => l_pa_req_rec.year_degree_attained
796 , p_first_noa_information1 => l_pa_req_rec.first_noa_information1
797 , p_first_noa_information2 => l_pa_req_rec.first_noa_information2
798 , p_first_noa_information3 => l_pa_req_rec.first_noa_information3
799 , p_first_noa_information4 => l_pa_req_rec.first_noa_information4
800 , p_first_noa_information5 => l_pa_req_rec.first_noa_information5
801 , p_second_lac1_information1 => l_pa_req_rec.second_lac1_information1
802 , p_second_lac1_information2 => l_pa_req_rec.second_lac1_information2
803 , p_second_lac1_information3 => l_pa_req_rec.second_lac1_information3
804 , p_second_lac1_information4 => l_pa_req_rec.second_lac1_information4
805 , p_second_lac1_information5 => l_pa_req_rec.second_lac1_information5
806 , p_second_lac2_information1 => l_pa_req_rec.second_lac2_information1
807 , p_second_lac2_information2 => l_pa_req_rec.second_lac2_information2
808 , p_second_lac2_information3 => l_pa_req_rec.second_lac2_information3
809 , p_second_lac2_information4 => l_pa_req_rec.second_lac2_information4
810 , p_second_lac2_information5 => l_pa_req_rec.second_lac2_information5
811 , p_second_noa_information1 => l_pa_req_rec.second_noa_information1
812 , p_second_noa_information2 => l_pa_req_rec.second_noa_information2
813 , p_second_noa_information3 => l_pa_req_rec.second_noa_information3
814 , p_second_noa_information4 => l_pa_req_rec.second_noa_information4
815 , p_second_noa_information5 => l_pa_req_rec.second_noa_information5
816 , p_first_lac1_information1 => l_pa_req_rec.first_lac1_information1
817 , p_first_lac1_information2 => l_pa_req_rec.first_lac1_information2
818 , p_first_lac1_information3 => l_pa_req_rec.first_lac1_information3
819 , p_first_lac1_information4 => l_pa_req_rec.first_lac1_information4
820 , p_first_lac1_information5 => l_pa_req_rec.first_lac1_information5
821 , p_first_lac2_information1 => l_pa_req_rec.first_lac2_information1
822 , p_first_lac2_information2 => l_pa_req_rec.first_lac2_information2
823 , p_first_lac2_information3 => l_pa_req_rec.first_lac2_information3
824 , p_first_lac2_information4 => l_pa_req_rec.first_lac2_information4
825 , p_first_lac2_information5 => l_pa_req_rec.first_lac2_information5
826 , p_attribute_category => l_pa_req_rec.attribute_category
827 , p_attribute1 => l_pa_req_rec.attribute1
828 , p_attribute2 => l_pa_req_rec.attribute2
829 , p_attribute3 => l_pa_req_rec.attribute3
830 , p_attribute4 => l_pa_req_rec.attribute4
831 , p_attribute5 => l_pa_req_rec.attribute5
832 , p_attribute6 => l_pa_req_rec.attribute6
833 , p_attribute7 => l_pa_req_rec.attribute7
834 , p_attribute8 => l_pa_req_rec.attribute8
835 , p_attribute9 => l_pa_req_rec.attribute9
836 , p_attribute10 => l_pa_req_rec.attribute10
837 , p_attribute11 => l_pa_req_rec.attribute11
838 , p_attribute12 => l_pa_req_rec.attribute12
839 , p_attribute13 => l_pa_req_rec.attribute13
840 , p_attribute14 => l_pa_req_rec.attribute14
841 , p_attribute15 => l_pa_req_rec.attribute15
842 , p_attribute16 => l_pa_req_rec.attribute16
843 , p_attribute17 => l_pa_req_rec.attribute17
844 , p_attribute18 => l_pa_req_rec.attribute18
845 , p_attribute19 => l_pa_req_rec.attribute19
846 , p_attribute20 => l_pa_req_rec.attribute20
847 , p_1_user_name_acted_on => p_username
848 , p_1_action_taken => 'INITIATED'
849 , P_2_user_name_routed_to => p_username
850 --Pradeep added for Bug#3650351
851 , p_award_percentage => l_pa_req_rec.award_percentage
852 -- out
853 , p_pa_request_id => l_pa_req_rec.pa_request_id
854 , p_par_object_version_number => l_par_object_version_number
855 , p_1_pa_routing_history_id => l_1_pa_routing_history_id
856 , p_1_prh_object_version_number => l_1_prh_object_version_number
857 , p_2_pa_routing_history_id => l_2_pa_routing_history_id
858 , p_2_prh_object_version_number => l_2_prh_object_version_number
859 -- Bug#4486823 RRR Changes
860 , p_payment_option => l_pa_req_rec.pa_incentive_payment_option
861 );
862 --
863 hr_utility.set_location('Created SF52 - OVN '||to_char(l_par_object_version_number)|| '-'|| l_proc, 20);
864 --
865 create_pa_request_extra_info(
866 p_new_pa_request_id => l_pa_req_rec.pa_request_id
867 ,p_old_pa_request_id => v_old_pa_request_id);
868 insert into ghr_pa_request_shadow (
869 pa_request_id,
870 academic_discipline,
871 annuitant_indicator,
872 appropriation_code1,
873 appropriation_code2,
874 bargaining_unit_status,
875 citizenship,
876 duty_station_id,
877 duty_station_location_id,
878 education_level,
879 fegli,
880 flsa_category,
881 forwarding_address_line1,
882 forwarding_address_line2,
883 forwarding_address_line3,
884 forwarding_country_short_name,
885 forwarding_postal_code,
886 forwarding_region_2,
887 forwarding_town_or_city,
888 functional_class,
889 part_time_hours,
890 pay_rate_determinant,
891 position_occupied,
892 retirement_plan,
893 service_comp_date,
894 supervisory_status,
895 tenure,
896 to_ap_premium_pay_indicator,
897 to_auo_premium_pay_indicator,
898 to_occ_code,
899 to_position_id,
900 to_retention_allowance,
901 to_staffing_differential,
902 to_step_or_rate,
903 to_supervisory_differential,
904 veterans_preference,
905 veterans_pref_for_rif,
906 veterans_status,
907 work_schedule,
908 year_degree_attained,
909 employee_first_name,
910 employee_last_name,
911 employee_middle_names,
912 employee_national_identifier,
913 employee_date_of_birth
914 )
915 values
916 (
917 l_pa_req_rec.pa_request_id,
918 l_pa_req_rec.academic_discipline,
919 l_pa_req_rec.annuitant_indicator,
920 l_pa_req_rec.appropriation_code1,
921 l_pa_req_rec.appropriation_code2,
922 l_pa_req_rec.bargaining_unit_status,
923 l_pa_req_rec.citizenship,
924 l_pa_req_rec.duty_station_id,
925 l_pa_req_rec.duty_station_location_id,
926 l_pa_req_rec.education_level,
927 l_pa_req_rec.fegli,
928 l_pa_req_rec.flsa_category,
929 l_pa_req_rec.forwarding_address_line1,
930 l_pa_req_rec.forwarding_address_line2,
931 l_pa_req_rec.forwarding_address_line3,
932 l_pa_req_rec.forwarding_country_short_name,
933 l_pa_req_rec.forwarding_postal_code,
934 l_pa_req_rec.forwarding_region_2,
935 l_pa_req_rec.forwarding_town_or_city,
936 l_pa_req_rec.functional_class,
937 l_pa_req_rec.part_time_hours,
938 l_pa_req_rec.pay_rate_determinant,
939 l_pa_req_rec.position_occupied,
940 l_pa_req_rec.retirement_plan,
941 l_pa_req_rec.service_comp_date,
942 l_pa_req_rec.supervisory_status,
943 l_pa_req_rec.tenure,
944 l_pa_req_rec.to_ap_premium_pay_indicator,
945 l_pa_req_rec.to_auo_premium_pay_indicator,
946 l_pa_req_rec.to_occ_code,
947 l_pa_req_rec.to_position_id,
948 l_pa_req_rec.to_retention_allowance,
949 l_pa_req_rec.to_staffing_differential,
950 l_pa_req_rec.to_step_or_rate,
951 l_pa_req_rec.to_supervisory_differential,
952 l_pa_req_rec.veterans_preference,
953 l_pa_req_rec.veterans_pref_for_rif,
954 l_pa_req_rec.veterans_status,
955 l_pa_req_rec.work_schedule,
956 l_pa_req_rec.year_degree_attained,
957 l_pa_req_rec.employee_first_name,
958 l_pa_req_rec.employee_last_name,
959 l_pa_req_rec.employee_middle_names,
960 l_pa_req_rec.employee_national_identifier,
961 l_pa_req_rec.employee_date_of_birth
962 );
963 --
964 hr_utility.set_location('Created SF52 Shadow ' || l_proc, 22);
965 --
966 if l_pa_req_rec.first_noa_code = '002' then
967 l_noa_cancel_or_correct := ghr_history_api.g_correct;
968 else
969 l_noa_cancel_or_correct := ghr_history_api.g_cancel;
970 end if;
971 hr_utility.set_location('Created SF52 - Pa REQUEST ID '||to_char(l_pa_req_rec.pa_request_id)|| '-'|| l_proc, 22);
972 hr_utility.set_location('Updating SF52 - OVN '||to_char(p_par_object_version_number)|| '-'|| l_proc, 25);
973 hr_utility.set_location(l_proc || 'Which NOA and Action ' || to_char(p_which_noa) || ' '|| p_which_action, 35);
974 if p_which_noa = 1 then
975 IF p_which_action = 'ORIGINAL' THEN
976 ghr_par_upd.upd(
977 P_PA_REQUEST_ID => p_pa_request_id
978 , P_OBJECT_VERSION_NUMBER => p_par_object_version_number
979 , p_first_noa_canc_pa_request_id => l_pa_req_rec.pa_request_id
980 );
981 ELSE
982 ghr_par_upd.upd(
983 P_PA_REQUEST_ID => p_pa_request_id
984 , P_OBJECT_VERSION_NUMBER => p_par_object_version_number
985 , p_first_noa_pa_request_id => l_pa_req_rec.pa_request_id
986 , p_first_noa_cancel_or_correct => l_noa_cancel_or_correct
987 );
988 END IF;
989 else
990 IF p_which_action = 'ORIGINAL' THEN
991 ghr_par_upd.upd(
992 P_PA_REQUEST_ID => p_pa_request_id
993 , P_OBJECT_VERSION_NUMBER => p_par_object_version_number
994 , p_second_noa_canc_pa_request_i => l_pa_req_rec.pa_request_id
995 );
996 ELSE
997 ghr_par_upd.upd(
998 P_PA_REQUEST_ID => p_pa_request_id
999 , P_OBJECT_VERSION_NUMBER => p_par_object_version_number
1000 , p_second_noa_pa_request_id => l_pa_req_rec.pa_request_id
1001 , p_second_noa_cancel_or_correct => l_noa_cancel_or_correct
1002 );
1003 END IF;
1004 end if;
1005 hr_utility.set_location('Updated SF52 - OVN '||to_char(p_par_object_version_number)|| '-'|| l_proc, 45);
1006 -- commit;
1007 hr_utility.set_location('Exiting '|| l_proc, 50);
1008 return l_pa_req_rec.PA_REQUEST_ID;
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 p_par_object_version_number := l_par_object_version_number1;
1012 end;
1013 ---------------------
1014 -- Correction
1015 ---------------------
1016 function ghr_correct_sf52 (
1017 p_pa_request_id in number
1018 , p_par_object_version_number in number
1019 , p_noa_id in number
1020 , p_which_noa in number
1021 , p_row_id in varchar
1022 , p_username in varchar)
1023 return number -- PA Request ID
1024 is
1025 --
1026 l_proc varchar2(61) := g_package_name || 'GHR_CORRECT_SF52';
1027 l_pa_req_rec ghr_pa_requests%rowtype;
1028 l_noa_cancel_or_correct varchar2(10);
1029 l_1_pa_routing_history_id number;
1030 l_1_prh_object_version_number number;
1031 l_2_pa_routing_history_id number;
1032 l_2_prh_object_version_number number;
1033 l_U_PRH_OBJECT_VERSION_NUMBER number;
1034 l_I_PA_ROUTING_HISTORY_ID number;
1035 l_I_PRH_OBJECT_VERSION_NUMBER number;
1036 l_par_object_version_number number;
1037 l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1038 -- l_U_PRH_OBJECT_VERSION_NUMBER number;
1039 --
1040 cursor c_pa_req(p_pa_request_id NUMBER) is
1041 select *
1042 from ghr_pa_requests
1043 where pa_request_id = p_pa_request_id;
1044
1045 cursor c_retro_pa_req is
1046 select * from ghr_pa_requests
1047 where pa_request_id = l_retro_pa_request_id;
1048
1049
1050 -- Cursor for Bug 3381960
1051 -- Need to find the RPA previous to the cancellation action
1052 l_prev_retro_pa_rec ghr_pa_requests%rowtype;
1053 --Bug#4116407 Modified the cursor. Added second_noa_cancel_or_correct CONDITION.
1054 CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,
1055 c_person_id ghr_pa_requests.person_id%type) IS
1056 SELECT *
1057 FROM ghr_pa_requests
1058 WHERE person_id = c_person_id
1059 AND pa_notification_id IS NOT NULL
1060 AND effective_date < c_retro_eff_date
1061 AND first_noa_code <> '001'
1062 AND NVL(first_noa_cancel_or_correct,'C') <> 'CANCEL'
1063 AND NVL(second_noa_cancel_or_correct,'C') <> 'CANCEL'
1064 ORDER BY pa_request_id desc;
1065
1066 cursor c_pa_req1(p_par_id number ) is
1067 select pa_notification_id,noa_family_code,
1068 second_noa_code
1069 from ghr_pa_requests
1070 where pa_request_id = p_par_id;
1071 --
1072 cursor C_noa_id(p_noa_code varchar2, p_effective_date date) is
1073 select nature_of_action_id, description
1074 from ghr_nature_of_actions
1075 where code = p_noa_code
1076 and p_effective_date between date_from and nvl(date_to, p_effective_date);
1077 --
1078 cursor c_noa_code (p_noa_id number)is
1079 select code
1080 from ghr_nature_of_actions where nature_of_action_id = p_noa_id;
1081 --
1082 -- Bug#3941541 Added parameter p_effective_date.
1083 cursor c_noa_fam_code(p_noa_code varchar2,p_effective_date date) is
1084 select noa_family_code from ghr_noa_families
1085 where nature_of_action_id in
1086 ( select nature_of_action_id from ghr_nature_of_actions
1087 where code = p_noa_code )
1088 and noa_family_code in
1089 ( select noa_family_code from ghr_families
1090 where update_hr_flag = 'Y')
1091 and p_effective_date between NVL(start_date_active,p_effective_date)
1092 and NVL(end_date_active,p_effective_date);
1093
1094 --
1095
1096
1097 --
1098 l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
1099 l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
1100 l_corrections NUMBER;
1101 l_which_noa NUMBER;
1102 l_dummy_number NUMBER;
1103 l_dummy_varchar VARCHAR2(10);
1104 l_noa_id_correct NUMBER;
1105 l_altered_pa_request_id NUMBER;
1106 l_effective_date ghr_pa_requests.effective_date%type;
1107 l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1108 l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1109 l_noa_family_code ghr_pa_requests.noa_family_code%type;
1110 l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;
1111 l_retro_first_noa ghr_nature_of_actions.code%type;
1112 l_retro_second_noa ghr_nature_of_actions.code%type;
1113 l_ia_flag varchar2(30);
1114 --bug 5172710
1115 l_to_total_salary ghr_pa_requests.to_total_salary%type;
1116 -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1117 l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1118 l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1119 l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1120 l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1121 l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1122 l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1123
1124 CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1125 SELECT to_position_org_line1,
1126 to_position_org_line2,
1127 to_position_org_line3,
1128 to_position_org_line4,
1129 to_position_org_line5,
1130 to_position_org_line6
1131 FROM ghr_pa_requests
1132 WHERE pa_request_id = c_pa_request_id;
1133 -- End Bug 2681842 and 3191676
1134
1135 begin
1136 --
1137 hr_utility.set_location('Entering '|| l_proc, 5);
1138
1139 find_last_request(p_pa_request_id => p_pa_request_id
1140 , p_which_noa => p_which_noa
1141 , p_row_id => p_row_id
1142 , p_first_pa_request_rec => l_first_pa_request_rec
1143 , p_last_pa_request_rec => l_last_pa_request_rec
1144 , p_number_of_requests => l_corrections);
1145 hr_utility.set_location(l_proc || 'Last PA Request ID ' || to_char(l_last_pa_request_rec.pa_request_id), 15);
1146 IF l_last_pa_request_rec.pa_notification_id IS NULL THEN
1147 l_corrections := -1;
1148 fnd_message.set_name('GHR', 'GHR_CANCEL_INVALID');
1149 fnd_message.raise_error;
1150 END IF;
1151
1152 --
1153 -- Get PA Request
1154 /*
1155 open c_pa_req;
1156 fetch c_pa_req into l_pa_req_rec;
1157 close c_pa_req;
1158 */
1159 l_pa_req_rec := l_last_pa_request_rec;
1160 l_which_noa := p_which_noa;
1161 if l_which_noa = 1 and l_pa_req_rec.first_noa_code = '002' then
1162 l_which_noa := 2;
1163 end if;
1164 -- -------------------------------------------
1165 -- Populate Second Noa Detail
1166 -- -------------------------------------------
1167 if l_which_noa = 1 then
1168 l_pa_req_rec.second_action_la_code1 := l_pa_req_rec.first_action_la_code1;
1169 l_pa_req_rec.second_action_la_code2 := l_pa_req_rec.first_action_la_code2;
1170 l_pa_req_rec.second_action_la_desc1 := l_pa_req_rec.first_action_la_desc1;
1171 l_pa_req_rec.second_action_la_desc2 := l_pa_req_rec.first_action_la_desc2;
1172 l_pa_req_rec.second_noa_code := l_pa_req_rec.first_noa_code;
1173 l_pa_req_rec.second_noa_desc := l_pa_req_rec.first_noa_desc;
1174 l_pa_req_rec.second_noa_id := l_pa_req_rec.first_noa_id;
1175 -- Bug#5036997 Added the information columns
1176 l_pa_req_rec.second_lac1_information1 := l_pa_req_rec.first_lac1_information1;
1177 l_pa_req_rec.second_lac1_information2 := l_pa_req_rec.first_lac1_information2;
1178 l_pa_req_rec.second_lac2_information1 := l_pa_req_rec.first_lac2_information1;
1179 l_pa_req_rec.second_lac2_information2 := l_pa_req_rec.first_lac2_information2;
1180 end if;
1181 -- -------------------------------------------
1182 -- Populate Second Noa Detail
1183 -- -------------------------------------------
1184 -- l_pa_req_rec.second_noa_code := l_pa_req_rec.first_noa_code;
1185 -- l_pa_req_rec.second_noa_id := p_noa_id;
1186 -- -------------------------------------------
1187 -- Populate First Noa Detail
1188 -- -------------------------------------------
1189 -- Correction NOA
1190 l_pa_req_rec.first_noa_code := '002';
1191 -- Get NOA Id
1192 Open c_noa_id(l_pa_req_rec.first_noa_code, l_pa_req_rec.effective_date);
1193 fetch c_noa_id into l_pa_req_rec.first_noa_id, l_pa_req_rec.first_noa_desc;
1194 close c_noa_id;
1195 l_pa_req_rec.first_action_la_code1 := null;
1196 l_pa_req_rec.first_action_la_code2 := null;
1197 l_pa_req_rec.first_action_la_desc1 := null;
1198 l_pa_req_rec.first_action_la_desc2 := null;
1199 --
1200 l_pa_req_rec.altered_pa_request_id := l_pa_req_rec.pa_request_id;
1201 l_pa_req_rec.pa_request_id := null;
1202 l_pa_req_rec.noa_family_code :=
1203 ghr_pa_requests_pkg.get_noa_pm_family(l_pa_req_rec.first_noa_id);
1204 -- --------
1205 -- Pay Calc
1206 -- --------
1207 -- l_pa_req_rec.custom_pay_calc_flag := 'Y';
1208 --
1209 l_effective_date := NULL;
1210 --
1211 -- Determine Intervening Actions
1212 --
1213 -- First determine presence of retro active actions
1214 l_ia_flag := 'N';
1215 -- Get the pa_notification_id from the original action
1216 FOR c_pa_rec1 IN c_pa_req1(p_pa_request_id) LOOP
1217 l_pa_notification_id := c_pa_rec1.pa_notification_id;
1218 l_noa_family_code := c_pa_rec1.noa_family_code;
1219 END LOOP;
1220 -- Bug#3941541
1221 IF l_pa_req_rec.second_noa_code = '825' THEN
1222 l_noa_family_code := 'GHR_INCENTIVE';
1223 END IF;
1224 hr_utility.set_location('noa family code is' || l_noa_family_code ,20);
1225 IF nvl(l_noa_family_code,hr_api.g_varchar2) not in
1226 ('APP','APPT_TRANS','APPT_INFO') THEN
1227 hr_utility.set_location('notification id is' || l_pa_req_rec.pa_notification_id,22 );
1228 --BUG # 7216635 Added the parameter p_noa_id_correct
1229 GHR_APPROVED_PA_REQUESTS.determine_ia(
1230 p_pa_request_id => p_pa_request_id,
1231 p_pa_notification_id => l_pa_notification_id,
1232 p_person_id => l_pa_req_rec.person_id,
1233 p_effective_date => l_pa_req_rec.effective_date,
1234 p_noa_id_correct => p_noa_id,
1235 p_retro_pa_request_id => l_retro_pa_request_id,
1236 p_retro_eff_date => l_effective_date,
1237 p_retro_first_noa => l_retro_first_noa,
1238 p_retro_second_noa => l_retro_second_noa);
1239
1240 IF l_effective_date is NOT NULL THEN
1241 l_ia_flag := 'Y';
1242 hr_utility.set_location('Intervening Action '||l_effective_date,10);
1243 IF l_retro_first_noa = '866' then
1244 l_effective_date := l_effective_date + 1;
1245 END IF;
1246 END IF;
1247
1248 IF l_ia_flag = 'Y' THEN
1249 FOR c_pa_rec1 IN c_pa_req1(l_retro_pa_request_id) LOOP
1250 l_retro_noa_family_code := c_pa_rec1.noa_family_code;
1251 IF l_retro_noa_family_code = 'CORRECT' THEN
1252 FOR c_noa_fam IN c_noa_fam_code(l_retro_second_noa,l_effective_date)
1253 LOOP
1254 l_retro_noa_family_code := c_noa_fam.noa_family_code;
1255 END LOOP;
1256 END IF;
1257 END LOOP;
1258 hr_utility.set_location('IA Action ',11);
1259 -- Bug 3381960
1260 -- If the retro action is cancellation action, then consider the RPA
1261 -- previous to this Cancellation RPA and use that for populating
1262 -- from side of this correction action.
1263
1264 IF NVL(l_retro_first_noa,hr_api.g_varchar2) = '001' THEN
1265 -- Get the To side of the RPA previous to this cancellation action
1266 -- Need to modify the cursor. Need to add Order of processing too
1267 hr_utility.set_location('Cancellation RPA',11);
1268 FOR l_pa_before_retro IN c_pa_before_retro(l_effective_date,l_pa_req_rec.person_id) LOOP
1269 l_prev_retro_pa_rec := l_pa_before_retro;
1270 EXIT;
1271 END LOOP;
1272
1273 l_pa_req_rec.from_position_id := l_prev_retro_pa_rec.to_position_id;
1274 l_pa_req_rec.from_position_title := l_prev_retro_pa_rec.to_position_title;
1275 l_pa_req_rec.from_position_number := l_prev_retro_pa_rec.to_position_number;
1276 l_pa_req_rec.from_position_seq_no := l_prev_retro_pa_rec.to_position_seq_no;
1277 l_pa_req_rec.from_pay_plan := l_prev_retro_pa_rec.to_pay_plan;
1278 l_pa_req_rec.from_occ_code := l_prev_retro_pa_rec.to_occ_code;
1279 l_pa_req_rec.from_grade_or_level := l_prev_retro_pa_rec.to_grade_or_level;
1280 l_pa_req_rec.from_step_or_rate := l_prev_retro_pa_rec.to_step_or_rate;
1281 l_pa_req_rec.from_total_salary := l_prev_retro_pa_rec.to_total_salary;
1282 l_pa_req_rec.from_pay_basis := l_prev_retro_pa_rec.to_pay_basis;
1283 -- FWFA Changes Bug#4444609
1284 l_pa_req_rec.input_pay_rate_determinant := l_prev_retro_pa_rec.pay_rate_determinant;
1285 l_pa_req_rec.from_pay_table_identifier := l_prev_retro_pa_rec.to_pay_table_identifier;
1286 -- FWFA Changes
1287 l_pa_req_rec.from_basic_pay := l_prev_retro_pa_rec.to_basic_pay;
1288 l_pa_req_rec.from_locality_adj := l_prev_retro_pa_rec.to_locality_adj;
1289 l_pa_req_rec.from_adj_basic_pay := l_prev_retro_pa_rec.to_adj_basic_pay;
1290 l_pa_req_rec.from_other_pay_amount := l_prev_retro_pa_rec.to_other_pay_amount;
1291 l_pa_req_rec.from_position_org_line1 := l_prev_retro_pa_rec.to_position_org_line1;
1292 l_pa_req_rec.from_position_org_line2 := l_prev_retro_pa_rec.to_position_org_line2;
1293 l_pa_req_rec.from_position_org_line3 := l_prev_retro_pa_rec.to_position_org_line3;
1294 l_pa_req_rec.from_position_org_line4 := l_prev_retro_pa_rec.to_position_org_line4;
1295 l_pa_req_rec.from_position_org_line5 := l_prev_retro_pa_rec.to_position_org_line5;
1296 l_pa_req_rec.from_position_org_line6 := l_prev_retro_pa_rec.to_position_org_line6;
1297
1298 ELSIF
1299 l_retro_noa_family_code in
1300 ( 'NON_PAY_DUTY_STATUS',
1301 'SEPARATION',
1302 'AWARD',
1303 'GHR_INCENTIVE'
1304 )
1305 THEN
1306 hr_utility.set_location('From Side data only MRPA',11);
1307 for c_ret_rec in c_retro_pa_req loop
1308 l_pa_req_rec.from_position_id := c_ret_rec.from_position_id;
1309 l_pa_req_rec.from_position_title := c_ret_rec.from_position_title;
1310 l_pa_req_rec.from_position_number := c_ret_rec.from_position_number;
1311 l_pa_req_rec.from_position_seq_no := c_ret_rec.from_position_seq_no;
1312 l_pa_req_rec.from_pay_plan := c_ret_rec.from_pay_plan;
1313 l_pa_req_rec.from_occ_code := c_ret_rec.from_occ_code;
1314 l_pa_req_rec.from_grade_or_level := c_ret_rec.from_grade_or_level;
1315 l_pa_req_rec.from_step_or_rate := c_ret_rec.from_step_or_rate;
1316 l_pa_req_rec.from_total_salary := c_ret_rec.from_total_salary;
1317 l_pa_req_rec.from_pay_basis := c_ret_rec.from_pay_basis;
1318 -- FWFA Changes Bug#4444609
1319 -- Bug# 4696860
1320 l_pa_req_rec.input_pay_rate_determinant := c_ret_rec.input_pay_rate_determinant;
1321 l_pa_req_rec.from_pay_table_identifier := c_ret_rec.from_pay_table_identifier;
1322 -- FWFA Changes
1323 l_pa_req_rec.from_basic_pay := c_ret_rec.from_basic_pay;
1324 l_pa_req_rec.from_locality_adj := c_ret_rec.from_locality_adj;
1325 l_pa_req_rec.from_adj_basic_pay := c_ret_rec.from_adj_basic_pay;
1326 l_pa_req_rec.from_other_pay_amount := c_ret_rec.from_other_pay_amount;
1327 l_pa_req_rec.from_position_org_line1 := c_ret_rec.from_position_org_line1;
1328 l_pa_req_rec.from_position_org_line2 := c_ret_rec.from_position_org_line2;
1329 l_pa_req_rec.from_position_org_line3 := c_ret_rec.from_position_org_line3;
1330 l_pa_req_rec.from_position_org_line4 := c_ret_rec.from_position_org_line4;
1331 l_pa_req_rec.from_position_org_line5 := c_ret_rec.from_position_org_line5;
1332 l_pa_req_rec.from_position_org_line6 := c_ret_rec.from_position_org_line6;
1333 exit;
1334 end loop;
1335 ELSE
1336 hr_utility.set_location('Non Cancel MRPA: ',11);
1337 for c_ret_rec in c_retro_pa_req loop
1338 l_pa_req_rec.from_position_id := c_ret_rec.to_position_id;
1339 l_pa_req_rec.from_position_title := c_ret_rec.to_position_title;
1340 l_pa_req_rec.from_position_number := c_ret_rec.to_position_number;
1341 l_pa_req_rec.from_position_seq_no := c_ret_rec.to_position_seq_no;
1342 l_pa_req_rec.from_pay_plan := c_ret_rec.to_pay_plan;
1343 l_pa_req_rec.from_occ_code := c_ret_rec.to_occ_code;
1344 l_pa_req_rec.from_grade_or_level := c_ret_rec.to_grade_or_level;
1345 l_pa_req_rec.from_step_or_rate := c_ret_rec.to_step_or_rate;
1346 l_pa_req_rec.from_total_salary := c_ret_rec.to_total_salary;
1347 l_pa_req_rec.from_pay_basis := c_ret_rec.to_pay_basis;
1348 -- FWFA Changes Bug#4444609
1349 l_pa_req_rec.input_pay_rate_determinant := c_ret_rec.pay_rate_determinant;
1350 l_pa_req_rec.from_pay_table_identifier := c_ret_rec.to_pay_table_identifier;
1351 -- FWFA Changes
1352 l_pa_req_rec.from_basic_pay := c_ret_rec.to_basic_pay;
1353 l_pa_req_rec.from_locality_adj := c_ret_rec.to_locality_adj;
1354 l_pa_req_rec.from_adj_basic_pay := c_ret_rec.to_adj_basic_pay;
1355 l_pa_req_rec.from_other_pay_amount := c_ret_rec.to_other_pay_amount;
1356 l_pa_req_rec.from_position_org_line1 := c_ret_rec.to_position_org_line1;
1357 l_pa_req_rec.from_position_org_line2 := c_ret_rec.to_position_org_line2;
1358 l_pa_req_rec.from_position_org_line3 := c_ret_rec.to_position_org_line3;
1359 l_pa_req_rec.from_position_org_line4 := c_ret_rec.to_position_org_line4;
1360 l_pa_req_rec.from_position_org_line5 := c_ret_rec.to_position_org_line5;
1361 l_pa_req_rec.from_position_org_line6 := c_ret_rec.to_position_org_line6;
1362 exit;
1363 end loop;
1364 END IF;
1365 ELSE
1366 hr_utility.set_location('non IA : '||l_pa_req_rec.pay_rate_determinant,11);
1367 l_noa_id_correct := hr_api.g_number;
1368 l_altered_pa_request_id := l_pa_req_rec.altered_pa_request_id;
1369 GHR_API.sf52_from_data_elements(
1370 p_person_id => l_pa_req_rec.person_id
1371 ,p_assignment_id => l_pa_req_rec.employee_assignment_id
1372 ,p_effective_date => nvl(l_effective_date, l_pa_req_rec.effective_date)
1373 ,p_altered_pa_request_id => l_altered_pa_request_id
1374 ,p_noa_id_corrected => l_noa_id_correct
1375 ,p_pa_history_id => l_dummy_number
1376 ,p_position_id => l_pa_req_rec.from_position_id
1377 ,p_position_title => l_pa_req_rec.from_position_title
1378 ,p_position_number => l_pa_req_rec.from_position_number
1379 ,p_position_seq_no => l_pa_req_rec.from_position_seq_no
1380 ,p_pay_plan => l_pa_req_rec.from_pay_plan
1381 ,p_job_id => l_dummy_number
1382 ,p_occ_code => l_pa_req_rec.from_occ_code
1383 ,p_grade_or_level => l_pa_req_rec.from_grade_or_level
1384 ,p_grade_id => l_dummy_number
1385 ,p_step_or_rate => l_pa_req_rec.from_step_or_rate
1386 ,p_total_salary => l_pa_req_rec.from_total_salary
1387 ,p_pay_basis => l_pa_req_rec.from_pay_basis
1388 -- FWFA Chagnes Bug#4444609
1389 ,p_pay_table_identifier => l_pa_req_rec.from_pay_table_identifier
1390 -- FWFA Changes
1391 ,p_basic_pay => l_pa_req_rec.from_basic_pay
1392 ,p_locality_adj => l_pa_req_rec.from_locality_adj
1393 ,p_adj_basic_pay => l_pa_req_rec.from_adj_basic_pay
1394 ,p_other_pay => l_pa_req_rec.from_other_pay_amount
1395 ,p_au_overtime => l_dummy_number
1396 ,p_auo_premium_pay_indicator => l_dummy_varchar
1397 ,p_availability_pay => l_dummy_number
1398 ,p_ap_premium_pay_indicator => l_dummy_varchar
1399 ,p_retention_allowance => l_dummy_number
1400 ,p_retention_allow_percentage => l_dummy_number
1401 ,p_supervisory_differential => l_dummy_number
1402 ,p_supervisory_diff_percentage => l_dummy_number
1403 ,p_staffing_differential => l_dummy_number
1404 ,p_staffing_diff_percentage => l_dummy_number
1405 ,p_organization_id => l_dummy_number
1406 ,p_position_org_line1 => l_pa_req_rec.from_position_org_line1
1407 ,p_position_org_line2 => l_pa_req_rec.from_position_org_line2
1408 ,p_position_org_line3 => l_pa_req_rec.from_position_org_line3
1409 ,p_position_org_line4 => l_pa_req_rec.from_position_org_line4
1410 ,p_position_org_line5 => l_pa_req_rec.from_position_org_line5
1411 ,p_position_org_line6 => l_pa_req_rec.from_position_org_line6
1412 ,p_duty_station_location_id => l_dummy_number
1413 -- FWFA Changes Bug#4444609
1414 ,p_pay_rate_determinant => l_pa_req_rec.input_pay_rate_determinant
1415 -- FWFA Changes
1416 ,p_work_schedule => l_dummy_varchar
1417 );
1418 END IF;
1419 END IF;
1420 -- Bug 2681842 and 3191676. Create SF52 including position org lines for 790 action
1421 IF (l_pa_req_rec.first_noa_code = '790' OR l_pa_req_rec.second_noa_code = '790') THEN
1422 FOR l_get_pos_org_lines IN c_get_pos_org_lines(l_pa_req_rec.altered_pa_request_id) LOOP
1423 l_to_position_org_line1 := l_get_pos_org_lines.to_position_org_line1;
1424 l_to_position_org_line2 := l_get_pos_org_lines.to_position_org_line2;
1425 l_to_position_org_line3 := l_get_pos_org_lines.to_position_org_line3;
1426 l_to_position_org_line4 := l_get_pos_org_lines.to_position_org_line4;
1427 l_to_position_org_line5 := l_get_pos_org_lines.to_position_org_line5;
1428 l_to_position_org_line6 := l_get_pos_org_lines.to_position_org_line6;
1429 END LOOP;
1430 END IF;
1431 -- Bug 3263056 Add to side details for Correction to 892/893
1432 -- Bug 4116407 Added code 867 in the IF Condition.
1433 IF l_pa_req_rec.second_noa_code IN ('867','892','893') THEN
1434 l_to_step_or_rate := l_pa_req_rec.from_step_or_rate;
1435 END IF;
1436 --Bug 5172710
1437 IF l_noa_family_code IN ('GHR_INCENTIVE') THEN
1438 l_to_total_salary := l_pa_req_rec.to_total_salary;
1439 END IF;
1440
1441
1442 Ghr_sf52_api.create_sf52(
1443 -- p_validate p_validate
1444 p_noa_family_code => l_pa_req_rec.noa_family_code
1445 , p_routing_group_id => l_pa_req_rec.routing_group_id
1446 , p_proposed_effective_asap_flag => l_pa_req_rec.proposed_effective_asap_flag
1447 -- , p_citizenship => l_pa_req_rec.citizenship
1448 , p_altered_pa_request_id => l_pa_req_rec.altered_pa_request_id
1449 , p_custom_pay_calc_flag => l_pa_req_rec.custom_pay_calc_flag
1450 , p_effective_date => l_pa_req_rec.effective_date
1451 , p_employee_date_of_birth => l_pa_req_rec.employee_date_of_birth
1452 , p_employee_first_name => l_pa_req_rec.employee_first_name
1453 , p_employee_last_name => l_pa_req_rec.employee_last_name
1454 , p_employee_middle_names => l_pa_req_rec.employee_middle_names
1455 , p_employee_national_identifier => l_pa_req_rec.employee_national_identifier
1456 , p_employee_assignment_id => l_pa_req_rec.employee_assignment_id
1457 , p_first_action_la_code1 => l_pa_req_rec.first_action_la_code1
1458 , p_first_action_la_code2 => l_pa_req_rec.first_action_la_code2
1459 , p_first_action_la_desc1 => l_pa_req_rec.first_action_la_desc1
1460 , p_first_action_la_desc2 => l_pa_req_rec.first_action_la_desc2
1461 , p_first_noa_code => l_pa_req_rec.first_noa_code
1462 , p_first_noa_desc => l_pa_req_rec.first_noa_desc
1463 , p_first_noa_id => l_pa_req_rec.first_noa_id
1464 , p_person_id => l_pa_req_rec.person_id
1465 , p_proposed_effective_date => l_pa_req_rec.proposed_effective_date
1466 , p_second_action_la_code1 => l_pa_req_rec.second_action_la_code1
1467 , p_second_action_la_code2 => l_pa_req_rec.second_action_la_code2
1468 , p_second_action_la_desc1 => l_pa_req_rec.second_action_la_desc1
1469 , p_second_action_la_desc2 => l_pa_req_rec.second_action_la_desc2
1470 , p_second_noa_code => l_pa_req_rec.second_noa_code
1471 , p_second_noa_desc => l_pa_req_rec.second_noa_desc
1472 , p_second_noa_id => l_pa_req_rec.second_noa_id
1473 ,p_from_position_id => l_pa_req_rec.from_position_id
1474 ,p_from_position_title => l_pa_req_rec.from_position_title
1475 ,p_from_position_number => l_pa_req_rec.from_position_number
1476 ,p_from_position_seq_no => l_pa_req_rec.from_position_seq_no
1477 ,p_from_pay_plan => l_pa_req_rec.from_pay_plan
1478 -- FWFA Changes Bug#4444609
1479 ,p_input_pay_rate_determinant => l_pa_req_rec.input_pay_rate_determinant
1480 ,p_from_pay_table_identifier => l_pa_req_rec.from_pay_table_identifier
1481 -- FWFA Changes
1482 ,p_from_occ_code => l_pa_req_rec.from_occ_code
1483 ,p_from_step_or_rate => l_pa_req_rec.from_step_or_rate
1484 ,p_from_grade_or_level => l_pa_req_rec.from_grade_or_level
1485 ,p_from_total_salary => l_pa_req_rec.from_total_salary
1486 ,p_from_pay_basis => l_pa_req_rec.from_pay_basis
1487 ,p_from_basic_pay => l_pa_req_rec.from_basic_pay
1488 ,p_from_locality_adj => l_pa_req_rec.from_locality_adj
1489 ,p_from_adj_basic_pay => l_pa_req_rec.from_adj_basic_pay
1490 ,p_from_other_pay_amount => l_pa_req_rec.from_other_pay_amount
1491 ,p_from_position_org_line1 => l_pa_req_rec.from_position_org_line1
1492 ,p_from_position_org_line2 => l_pa_req_rec.from_position_org_line2
1493 ,p_from_position_org_line3 => l_pa_req_rec.from_position_org_line3
1494 ,p_from_position_org_line4 => l_pa_req_rec.from_position_org_line4
1495 ,p_from_position_org_line5 => l_pa_req_rec.from_position_org_line5
1496 ,p_from_position_org_line6 => l_pa_req_rec.from_position_org_line6
1497 -- Sundar 2681726 and 3191676 these values need to be populated for 790 action
1498 -- Bug#5036997 Added the information columns
1499 ,p_second_lac1_information1 => l_pa_req_rec.second_lac1_information1
1500 ,p_second_lac1_information2 => l_pa_req_rec.second_lac1_information2
1501 ,p_second_lac2_information1 => l_pa_req_rec.second_lac2_information1
1502 ,p_second_lac2_information2 => l_pa_req_rec.second_lac2_information2
1503 ,p_to_position_org_line1 => l_to_position_org_line1
1504 ,p_to_position_org_line2 => l_to_position_org_line2
1505 ,p_to_position_org_line3 => l_to_position_org_line3
1506 ,p_to_position_org_line4 => l_to_position_org_line4
1507 ,p_to_position_org_line5 => l_to_position_org_line5
1508 ,p_to_position_org_line6 => l_to_position_org_line6
1509 --Bug 5172710
1510 , p_to_total_salary => l_to_total_salary
1511 --Bug#3263056 Added to side details for 892,893
1512 , p_to_step_or_rate => l_to_step_or_rate
1513 --Bug#3263056
1514 , p_1_user_name_acted_on => p_username
1515 , p_1_action_taken => 'INITIATED'
1516 , P_2_user_name_routed_to => p_username
1517 -- out
1518 , p_pa_request_id => l_pa_req_rec.pa_request_id
1519 , p_par_object_version_number => l_par_object_version_number
1520 , p_1_pa_routing_history_id => l_1_pa_routing_history_id
1521 , p_1_prh_object_version_number => l_1_prh_object_version_number
1522 , p_2_pa_routing_history_id => l_2_pa_routing_history_id
1523 , p_2_prh_object_version_number => l_2_prh_object_version_number
1524 , p_award_uom => l_pa_req_rec.award_uom
1525 -- Bug#4486823 RRR Changes
1526 , p_payment_option => l_pa_req_rec.pa_incentive_payment_option
1527
1528
1529 );
1530
1531 IF nvl(l_noa_family_code,hr_api.g_varchar2) = 'GHR_INCENTIVE' THEN
1532 hr_utility.set_location('Inserting incentive records',0);
1533 INSERT INTO ghr_pa_incentives( pa_incentive_id,
1534 pa_request_id ,
1535 pa_incentive_category,
1536 pa_incentive_category_percent ,
1537 pa_incentive_category_amount,
1538 pa_incentive_category_pmnt_dt ,
1539 pa_incentive_category_end_date)
1540 SELECT
1541 ghr_pa_incentives_s.nextval,
1542 l_pa_req_rec.pa_request_id,
1543 pa_incentive_category,
1544 pa_incentive_category_percent ,
1545 pa_incentive_category_amount,
1546 pa_incentive_category_pmnt_dt ,
1547 pa_incentive_category_end_date
1548 FROM GHR_PA_INCENTIVES
1549 WHERE pa_request_id = l_pa_req_rec.altered_pa_request_id;
1550 hr_utility.set_location('After Inserting incentive records',10);
1551 END IF;
1552 -- Bug#4486823 RRR Changes
1553
1554 hr_utility.set_location('Created SF52 - Altered PA Request ID '||to_char(l_pa_req_rec.altered_pa_request_id)|| '-'|| l_proc, 21);
1555 hr_utility.set_location('Created SF52 - PA request ID '||to_char(l_pa_req_rec.pa_request_id)|| '-'|| l_proc, 22);
1556 insert into ghr_pa_request_shadow (
1557 pa_request_id,
1558 academic_discipline,
1559 annuitant_indicator,
1560 appropriation_code1,
1561 appropriation_code2,
1562 bargaining_unit_status,
1563 citizenship,
1564 duty_station_id,
1565 duty_station_location_id,
1566 education_level,
1567 fegli,
1568 flsa_category,
1569 forwarding_address_line1,
1570 forwarding_address_line2,
1571 forwarding_address_line3,
1572 forwarding_country_short_name,
1573 forwarding_postal_code,
1574 forwarding_region_2,
1575 forwarding_town_or_city,
1576 functional_class,
1577 part_time_hours,
1578 pay_rate_determinant,
1579 position_occupied,
1580 retirement_plan,
1581 service_comp_date,
1582 supervisory_status,
1583 tenure,
1584 to_ap_premium_pay_indicator,
1585 to_auo_premium_pay_indicator,
1586 to_occ_code,
1587 to_position_id,
1588 to_retention_allowance,
1589 to_staffing_differential,
1590 to_step_or_rate,
1591 to_supervisory_differential,
1592 veterans_preference,
1593 veterans_pref_for_rif,
1594 veterans_status,
1595 work_schedule,
1596 year_degree_attained,
1597 employee_first_name,
1598 employee_last_name,
1599 employee_middle_names,
1600 employee_national_identifier,
1601 employee_date_of_birth )
1602
1603 values
1604 (
1605 l_pa_req_rec.pa_request_id,
1606 l_pa_req_rec.academic_discipline,
1607 l_pa_req_rec.annuitant_indicator,
1608 l_pa_req_rec.appropriation_code1,
1609 l_pa_req_rec.appropriation_code2,
1610 l_pa_req_rec.bargaining_unit_status,
1611 l_pa_req_rec.citizenship,
1612 l_pa_req_rec.duty_station_id,
1613 l_pa_req_rec.duty_station_location_id,
1614 l_pa_req_rec.education_level,
1615 l_pa_req_rec.fegli,
1616 l_pa_req_rec.flsa_category,
1617 l_pa_req_rec.forwarding_address_line1,
1618 l_pa_req_rec.forwarding_address_line2,
1619 l_pa_req_rec.forwarding_address_line3,
1620 l_pa_req_rec.forwarding_country_short_name,
1621 l_pa_req_rec.forwarding_postal_code,
1622 l_pa_req_rec.forwarding_region_2,
1623 l_pa_req_rec.forwarding_town_or_city,
1624 l_pa_req_rec.functional_class,
1625 l_pa_req_rec.part_time_hours,
1626 l_pa_req_rec.pay_rate_determinant,
1627 l_pa_req_rec.position_occupied,
1628 l_pa_req_rec.retirement_plan,
1629 l_pa_req_rec.service_comp_date,
1630 l_pa_req_rec.supervisory_status,
1631 l_pa_req_rec.tenure,
1632 l_pa_req_rec.to_ap_premium_pay_indicator,
1633 l_pa_req_rec.to_auo_premium_pay_indicator,
1634 l_pa_req_rec.to_occ_code,
1635 l_pa_req_rec.to_position_id,
1636 l_pa_req_rec.to_retention_allowance,
1637 l_pa_req_rec.to_staffing_differential,
1638 l_pa_req_rec.to_step_or_rate,
1639 l_pa_req_rec.to_supervisory_differential,
1640 l_pa_req_rec.veterans_preference,
1641 l_pa_req_rec.veterans_pref_for_rif,
1642 l_pa_req_rec.veterans_status,
1643 l_pa_req_rec.work_schedule,
1644 l_pa_req_rec.year_degree_attained,
1645 l_pa_req_rec.employee_first_name,
1646 l_pa_req_rec.employee_last_name,
1647 l_pa_req_rec.employee_middle_names,
1648 l_pa_req_rec.employee_national_identifier,
1649 l_pa_req_rec.employee_date_of_birth
1650 );
1651 --
1652 hr_utility.set_location('Created SF52 Shadow ' || l_proc, 22);
1653 --
1654 if l_pa_req_rec.first_noa_code = '002' then
1655 l_noa_cancel_or_correct := ghr_history_api.g_correct;
1656 else
1657 l_noa_cancel_or_correct := ghr_history_api.g_cancel;
1658 end if;
1659 hr_utility.set_location('Created SF52 - Pa REQUEST ID '||to_char(l_last_pa_request_rec.pa_request_id)|| '-'|| l_proc, 22);
1660 hr_utility.set_location(l_proc || ' Last rows object version # ' || to_char(l_last_pa_request_rec.object_version_number), 20);
1661 hr_utility.set_location('Updating SF52 - OVN '||to_char(l_last_pa_request_rec.object_version_number)|| '-'|| l_proc, 25);
1662 hr_utility.set_location('Updated SF52 - PA Request ID '||to_char(l_last_pa_request_rec.pa_request_id)|| '-'|| l_proc, 26);
1663 if l_which_noa = 1 then
1664 ghr_par_upd.upd(
1665 P_PA_REQUEST_ID => l_last_pa_request_rec.pa_request_id
1666 , P_OBJECT_VERSION_NUMBER => l_last_pa_request_rec.object_version_number
1667 , p_first_noa_pa_request_id => l_last_pa_request_rec.pa_request_id
1668 , p_first_noa_cancel_or_correct => l_noa_cancel_or_correct
1669 );
1670 else
1671 ghr_par_upd.upd(
1672 P_PA_REQUEST_ID => l_last_pa_request_rec.pa_request_id
1673 , P_OBJECT_VERSION_NUMBER => l_last_pa_request_rec.object_version_number
1674 , p_second_noa_pa_request_id => l_last_pa_request_rec.pa_request_id
1675 , p_second_noa_cancel_or_correct => l_noa_cancel_or_correct
1676 );
1677 end if;
1678 hr_utility.set_location('Updated SF52 - OVN '||to_char(l_last_pa_request_rec.object_version_number)|| '-'|| l_proc, 25);
1679 -- commit;
1680 --
1681 hr_utility.set_location('Exiting '|| l_proc, 15);
1682 --
1683 return l_pa_req_rec.PA_REQUEST_ID;
1684
1685 end;
1686 ---------------------
1687 -- Re-route
1688 ---------------------
1689 function ghr_reroute_sf52 (
1690 P_PA_REQUEST_ID IN NUMBER
1691 , p_par_object_version_number in out nocopy number
1692 ,P_ROUTING_GROUP_ID IN NUMBER
1693 ,P_USER_NAME IN VARCHAR2
1694 )
1695 return boolean
1696 is
1697 --
1698 l_proc varchar2(61) := g_package_name || 'GHR_REROUTE_SF52';
1699 l_INITIATOR_FLAG VARCHAR2(1);
1700 l_REQUESTER_FLAG VARCHAR2(1);
1701 l_AUTHORIZER_FLAG VARCHAR2(1);
1702 l_PERSONNELIST_FLAG VARCHAR2(1);
1703 l_APPROVER_FLAG VARCHAR2(1);
1704 l_REVIEWER_FLAG VARCHAR2(1);
1705 --
1706 l_return_value BOOLEAN;
1707 --
1708 l_1_pa_routing_history_id number;
1709 l_1_prh_object_version_number number;
1710 l_2_pa_routing_history_id number;
1711 l_2_prh_object_version_number number;
1712 l_U_PRH_OBJECT_VERSION_NUMBER number;
1713 l_I_PA_ROUTING_HISTORY_ID number;
1714 l_I_PRH_OBJECT_VERSION_NUMBER number;
1715 l_par_object_version_number number;
1716 l_par_object_version_number1 number;
1717 --
1718 begin
1719 --
1720 hr_utility.set_location('Entering '|| l_proc, 5);
1721 l_par_object_version_number1 := p_par_object_version_number;
1722 --
1723 get_roles(
1724 P_ROUTING_GROUP_ID => P_ROUTING_GROUP_ID
1725 , P_USER_NAME => P_USER_NAME
1726 , P_INITIATOR_FLAG => L_INITIATOR_FLAG
1727 , P_REQUESTER_FLAG => L_REQUESTER_FLAG
1728 , P_AUTHORIZER_FLAG => L_AUTHORIZER_FLAG
1729 , P_PERSONNELIST_FLAG => L_PERSONNELIST_FLAG
1730 , P_APPROVER_FLAG => L_APPROVER_FLAG
1731 , P_REVIEWER_FLAG => L_REVIEWER_FLAG
1732 );
1733
1734 -- FIX for 4145758
1735 -- If these 2 flags are NULL it means the user is no more associated to
1736 -- that RTG GRP anymore.
1737 --
1738 IF (L_APPROVER_FLAG IS NULL and L_PERSONNELIST_FLAG IS NULL ) THEN
1739 fnd_message.set_name('GHR','GHR_38926_RTGGRP_DSNT_EXST');
1740 return FALSE;
1741 -- set this message here to retrieve the same under form
1742 END IF;
1743
1744 hr_utility.set_location('after get_roles'||P_ROUTING_GROUP_ID ,123456);
1745 --
1746 l_return_value := (L_APPROVER_FLAG = 'Y' AND L_PERSONNELIST_FLAG = 'Y');
1747 --
1748 hr_utility.set_location('Roles Approver = ' || L_APPROVER_FLAG ||
1749 ' PERSONNELIST= ' || L_PERSONNELIST_FLAG || l_proc, 10);
1750 --
1751 if l_return_value then
1752 hr_utility.set_location('l_return_value is TRUE',10);
1753 ghr_api.call_workflow(p_pa_request_id, 'CONTINUE');
1754
1755 hr_utility.set_location('Re routed '|| l_proc, 15);
1756 hr_utility.set_location('Updated PA Requests '|| l_proc, 20);
1757 else
1758 -- return false otherwise instead of throwing error mesg
1759 -- Fix for 4145758
1760 hr_utility.set_location('l_return_value is FALSE',10);
1761 fnd_message.set_name('GHR', 'GHR_38550_REROUTE');
1762 return l_return_value;
1763 end if;
1764 --
1765 hr_utility.set_location('Exiting '|| l_proc, 25);
1766 --
1767 return l_return_value;
1768 EXCEPTION
1769 WHEN OTHERS THEN
1770 p_par_object_version_number := l_par_object_version_number;
1771 end;
1772 --
1773
1774 FUNCTION chk_intervene_seq (
1775 p_pa_request_id in number
1776 , p_pa_notification_id in number
1777 , p_person_id in number
1778 , p_effective_date in date
1779 , p_noa_id_correct in number)
1780 return NUMBER IS
1781 --
1782
1783 cursor c_multi_ia is
1784 select count(*)
1785 from ghr_pa_requests
1786 where pa_notification_id is not null
1787 and person_id = p_person_id
1788 and effective_date = p_effective_date
1789 group by person_id
1790 having count(*) > 1
1791 ;
1792 cursor c_out_of_seq is
1793 select 'Y' from
1794 ghr_pa_requests a
1795 where person_id = p_person_id
1796 and effective_date = p_effective_date
1797 and pa_notification_id is not null
1798 and pa_notification_id < p_pa_notification_id
1799 and not exists ( select 'Y' from ghr_pa_requests b
1800 where b.person_id = a.person_id
1801 and b.pa_notification_id is not null
1802 and b.pa_notification_id > a.pa_notification_id
1803 and b.altered_pa_request_id = a.pa_request_id
1804 and b.first_noa_code = '002'
1805 and b.second_noa_code = a.first_noa_code )
1806 ;
1807
1808 l_proc varchar2(61) := g_package_name || 'chk_intervene_seq';
1809 l_effective_date ghr_pa_requests.effective_date%type;
1810 l_retro_first_noa ghr_nature_of_actions.code%type;
1811 l_retro_second_noa ghr_nature_of_actions.code%type;
1812 l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1813 BEGIN
1814 hr_utility.set_location('Entering '|| l_proc, 5);
1815 -- Determine Intervening Action
1816 --BUG # 7216635 Added the parameter p_noa_id_correct
1817 GHR_APPROVED_PA_REQUESTS.determine_ia(
1818 p_pa_request_id => p_pa_request_id,
1819 p_pa_notification_id => p_pa_notification_id,
1820 p_person_id => p_person_id,
1821 p_effective_date => p_effective_date,
1822 p_noa_id_correct => p_noa_id_correct,
1823 p_retro_eff_date => l_effective_date,
1824 p_retro_pa_request_id => l_retro_pa_request_id,
1825 p_retro_first_noa => l_retro_first_noa,
1826 p_retro_second_noa => l_retro_second_noa);
1827 IF l_effective_date is not NULL THEN
1828 -- Check for the presence of multiple intervening actions
1829 FOR c_multi_ia_rec IN c_multi_ia
1830 LOOP
1831 -- Check for out of sequence
1832 hr_utility.set_location('Multiple IA action'|| l_proc, 15);
1833 FOR c_out_seq IN c_out_of_seq
1834 LOOP
1835 hr_utility.set_location('Out of Sequence Action'|| l_proc, 20);
1836 return 0;
1837 END LOOP;
1838 exit;
1839 END LOOP;
1840 END IF;
1841 hr_utility.set_location('Leaving '|| l_proc, 25);
1842 return 1;
1843 END;
1844
1845 procedure determine_ia (
1846 p_pa_request_id in number
1847 , p_pa_notification_id in number
1848 , p_person_id in number
1849 , p_effective_date in date
1850 , p_noa_id_correct in number
1851 , p_retro_pa_request_id out nocopy number
1852 , p_retro_eff_date out nocopy date
1853 , p_retro_first_noa out nocopy varchar2
1854 , p_retro_second_noa out nocopy varchar2 ) is
1855 --
1856 cursor c_determine_ia is
1857 select effective_date,first_noa_code,
1858 second_noa_code,pa_notification_id,pa_request_id
1859 from ghr_pa_requests a
1860 where pa_notification_id is not null
1861 and person_id = p_person_id
1862 and pa_notification_id > p_pa_notification_id
1863 and effective_date <= p_effective_date
1864 -- and first_noa_code not in ('001') -- Exclude all cancellations
1865 and pa_request_id not in ( -- Exclude all cancellation of correction actions
1866 select nvl(altered_pa_request_id,0)
1867 from ghr_pa_requests b
1868 where a.person_id = b.person_id
1869 and b.first_noa_code in ('001')
1870 and b.pa_notification_id is not null )
1871 and pa_request_id not in ( -- Exclude all the corrections on the current action
1872 select nvl(pa_request_id,0)
1873 from ghr_pa_requests c
1874 connect by prior pa_request_id = altered_pa_request_id
1875 start with altered_pa_request_id = p_pa_request_id )
1876 order by pa_notification_id desc;
1877
1878 --BUG 7216635 Added the following cursor to check processing order of the intervening
1879 -- action found
1880 cursor chk_ord_of_proc(p_ia_noac in varchar2)
1881 is
1882 select 1
1883 from ghr_nature_of_actions
1884 where nature_of_action_id = p_noa_id_correct
1885 and order_of_processing < (select order_of_processing
1886 from ghr_nature_of_actions
1887 where code = p_ia_noac
1888 and p_effective_date between nvl(date_from,p_effective_date)
1889 and nvl(date_to,p_effective_date)
1890 );
1891
1892
1893 l_proc varchar2(61) := g_package_name || 'determine_ia';
1894 chk_order boolean;
1895
1896 BEGIN
1897 hr_utility.set_location('Entering '|| l_proc, 5);
1898
1899 p_retro_eff_date := NULL;
1900 -- Determine Intervening Action
1901 FOR c_det_ia_rec IN c_determine_ia
1902 LOOP
1903 chk_order := TRUE;
1904 IF c_det_ia_rec.effective_date = p_effective_date and c_det_ia_rec.first_noa_code not in ('002','001') then
1905 --BUG 7216635 Added the following code to check processing order of the intervening
1906 -- action found if processed on same effective date if it is less than the correction initiated
1907 -- no need to consider as intervening
1908 -- This need to be verified for dual actions.
1909 for rec_chk_ord_of_proc in chk_ord_of_proc(p_ia_noac => c_det_ia_rec.first_noa_code)
1910 loop
1911 chk_order := FALSE;
1912 end loop;
1913 END IF;
1914 if chk_order then
1915 p_retro_pa_request_id := c_det_ia_rec.pa_request_id;
1916 p_retro_eff_date := c_det_ia_rec.effective_date;
1917 p_retro_first_noa := c_det_ia_rec.first_noa_code;
1918 p_retro_second_noa := c_det_ia_rec.second_noa_code;
1919 end if;
1920 --BUG # 7216635
1921 hr_utility.set_location('Intervening Action '|| c_det_ia_rec.pa_notification_id, 20);
1922 exit;
1923 END LOOP;
1924 hr_utility.set_location('Leaving '|| l_proc, 25);
1925
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928 p_retro_pa_request_id := NULL;
1929 p_retro_eff_date := NULL;
1930 p_retro_first_noa := NULL;
1931 p_retro_second_noa := NULL;
1932 END;
1933 end GHR_APPROVED_PA_REQUESTS ;