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