[Home] [Help]
PACKAGE BODY: APPS.GHR_PROC_FUT_MT
Source
1 PACKAGE BODY GHR_PROC_FUT_MT AS
2 /* $Header: ghprocmt.pkb 120.5.12010000.4 2008/11/17 06:20:55 vmididho ship $ */
3 -- Global variable to Store return code
4
5 g_futr_proc_name VARCHAR2(100);
6
7 TYPE lt_request_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
8 g_request_ids lt_request_ids;
9 -- ============================================================================
10 -- << Procedure: execute_mt >>
11 -- Description:
12 -- This procedure is called from concurrent program. This procedure will
13 -- determine the batch size and call sub programs.
14 -- ============================================================================
15 PROCEDURE EXECUTE_MT( p_errbuf OUT NOCOPY VARCHAR2,
16 p_retcode OUT NOCOPY NUMBER,
17 p_poi IN ghr_pois.personnel_office_id%TYPE,
18 p_batch_size IN NUMBER,
19 p_thread_size IN NUMBER)
20 IS
21 -- Cursor for Future actions when POI parameter is entered
22 CURSOR c_futr_actions_poi(c_poi ghr_pois.personnel_office_id%TYPE) IS
23 SELECT a.person_id,a.effective_date,noa.order_of_processing,
24 a.pa_request_id,a.first_noa_code,a.object_version_number,
25 a.employee_last_name, a.employee_first_name,a.employee_national_identifier
26 FROM ghr_pa_requests a, ghr_pa_routing_history b,ghr_nature_of_actions noa
27 WHERE effective_date <= sysdate
28 AND pa_notification_id IS NULL
29 AND approval_date IS NOT NULL
30 AND a.pa_request_id = b.pa_request_id
31 AND noa.code = a.first_noa_code
32 AND c_poi =
33 (SELECT POEI_INFORMATION3 FROM per_position_extra_info
34 WHERE information_type = 'GHR_US_POS_GRP1'
35 AND position_id = NVL(a.to_position_id,a.from_position_id))
36 AND action_taken = 'FUTURE_ACTION'
37 AND EXISTS
38 (SELECT 1
39 FROM per_people_f per
40 WHERE per.person_id = a.person_id
41 AND a.effective_date BETWEEN
42 per.effective_start_date AND per.effective_end_date )
43 AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
44 FROM ghr_pa_routing_history
45 WHERE pa_request_id = a.pa_request_id)
46 ORDER BY person_id,effective_date,order_of_processing;
47
48 -- Cursor to find total future action records if POI parameter is entered
49 CURSOR c_tot_futr_actions_poi(c_poi ghr_pois.personnel_office_id%TYPE) IS
50 SELECT COUNT(*) fut_cnt
51 FROM ghr_pa_requests a, ghr_pa_routing_history b,ghr_nature_of_actions noa
52 WHERE effective_date <= sysdate
53 AND pa_notification_id IS NULL
54 AND approval_date IS NOT NULL
55 AND a.pa_request_id = b.pa_request_id
56 AND noa.code = a.first_noa_code
57 AND c_poi =
58 (SELECT POEI_INFORMATION3 FROM per_position_extra_info
59 WHERE information_type = 'GHR_US_POS_GRP1'
60 AND position_id = NVL(a.to_position_id,a.from_position_id))
61 AND action_taken = 'FUTURE_ACTION'
62 AND EXISTS
63 (SELECT 1
64 FROM per_people_f per
65 WHERE per.person_id = a.person_id
66 AND a.effective_date BETWEEN
67 per.effective_start_date AND per.effective_end_date )
68 AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
69 FROM ghr_pa_routing_history
70 WHERE pa_request_id = a.pa_request_id);
71
72
73 -- Cursor to fetch future action records when POI parameter is not entered.
74
75 CURSOR c_futr_actions IS
76 SELECT a.person_id,a.effective_date,noa.order_of_processing,
77 a.pa_request_id,a.first_noa_code,a.object_version_number,
78 a.employee_last_name, a.employee_first_name,a.employee_national_identifier
79 FROM ghr_pa_requests a, ghr_pa_routing_history b,ghr_nature_of_actions noa
80 WHERE effective_date <= sysdate
81 AND pa_notification_id IS NULL
82 AND approval_date IS NOT NULL
83 AND a.pa_request_id = b.pa_request_id
84 AND noa.code = a.first_noa_code
85 AND action_taken = 'FUTURE_ACTION'
86 AND EXISTS
87 (SELECT 1
88 FROM per_people_f per
89 WHERE per.person_id = a.person_id
90 AND a.effective_date BETWEEN
91 per.effective_start_date AND per.effective_end_date )
92 AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
93 FROM ghr_pa_routing_history
94 WHERE pa_request_id = a.pa_request_id)
95 ORDER BY person_id,effective_date,order_of_processing;
96
97 -- Cursor to find total future action records when POI parameter is not entered.
98 CURSOR c_tot_futr_actions IS
99 SELECT COUNT(*) fut_cnt
100 FROM ghr_pa_requests a, ghr_pa_routing_history b,ghr_nature_of_actions noa
101 WHERE effective_date <= sysdate
102 AND pa_notification_id IS NULL
103 AND approval_date IS NOT NULL
104 AND a.pa_request_id = b.pa_request_id
105 AND noa.code = a.first_noa_code
106 AND action_taken = 'FUTURE_ACTION'
107 AND EXISTS
108 (SELECT 1
109 FROM per_people_f per
110 WHERE per.person_id = a.person_id
111 AND a.effective_date BETWEEN
112 per.effective_start_date AND per.effective_end_date )
113 AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
114 FROM ghr_pa_routing_history
115 WHERE pa_request_id = a.pa_request_id);
116
117 CURSOR c_completion_status(c_session_id NUMBER) IS
118 SELECT max(completion_status) max_status
119 FROM GHR_MTS_TEMP
120 WHERE session_id = c_session_id;
121
122 -- Declaration of Local variables
123 l_person_id per_all_people.person_id%type;
124 l_effective_date ghr_pa_requests.effective_date%type;
125 l_batch_size NUMBER;
126 l_thread_size NUMBER;
127 l_batch_no NUMBER;
128 l_batch_counter NUMBER;
129 l_session_id NUMBER;
130 l_parent_request_id NUMBER;
131 l_completion_status NUMBER;
132 l_request_id NUMBER;
133 l_log_text VARCHAR2(2000);
134 l_user_name VARCHAR2(200);
135 l_new_line VARCHAR2(1);
136 l_result VARCHAR2(200);
137 l_status BOOLEAN;
138 l_count NUMBER;
139 -- Bug # 7510344
140 l_curr_business_group_id per_all_people_f.business_group_id%type;
141
142 rphase varchar2(80);
143 rstatus varchar2(80);
144 dphase varchar2(30);
145 dstatus varchar2(30);
146 message varchar2(240);
147 call_status boolean;
148
149 BEGIN
150 -- Initialization of variables.
151 l_batch_counter := 0;
152 l_batch_no := 1;
153 l_session_id := USERENV('SESSIONID');
154 l_parent_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
155 l_status := TRUE;
156 g_request_ids.DELETE;
157 g_futr_proc_name := 'GHR_Proc_Futr_Act' || '_' || l_parent_request_id;
158 -- Bug # 7510344
159 l_curr_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
160
161 -- Thread size should be minimum of 10.
162 IF p_thread_size IS NULL OR p_thread_size < 10 THEN
163 l_thread_size := 10;
164 ELSIF p_thread_size > 35 THEN
165 l_thread_size := 35;
166 ELSE
167 l_thread_size := p_thread_size;
168 END IF;
169
170 -- Batch size should be minimum of 1000.
171 IF p_batch_size IS NULL OR p_batch_size < 1000 THEN
172 l_batch_size := 1000;
173 ELSE
174 l_batch_size := p_batch_size;
175 END IF;
176
177 -- Find out Total future action records
178 IF p_poi IS NULL THEN
179 FOR l_tot_futr_actions IN c_tot_futr_actions LOOP
180 l_count := l_tot_futr_actions.fut_cnt;
181 END LOOP;
182 ELSE
183 FOR l_tot_futr_actions_poi IN c_tot_futr_actions_poi(p_poi) LOOP
184 l_count := l_tot_futr_actions_poi.fut_cnt;
185 END LOOP;
186 END IF;
187
188 -- Revise the batch size if the total future action record is more than
189 -- the product of thread size and batch size.
190 IF l_count > (l_thread_size * l_batch_size) THEN
191 l_batch_size := CEIL(l_count/l_thread_size);
192 END IF;
193
194 -- If Personnel office ID is entered, call the cursor c_futr_actions_poi
195 -- else call c_futr_actions
196 IF p_poi IS NULL THEN
197
198 -- Loop through the future actions and insert them into the appropriate batch.
199 -- If the batch size exceeds the limit and if the record belongs to different
200 -- person, insert the following records into the next batch.
201 FOR l_c_futr_actions IN c_futr_actions LOOP
202 l_result := NULL;
203 -- Bug#3726290 New business rule for NOAC 355
204 IF l_c_futr_actions.first_noa_code = '355' THEN
205 verify_355_business_rule(l_c_futr_actions.person_id,
206 l_c_futr_actions.effective_date,
207 l_result);
208 END IF;
209 IF NVL(l_result,'NOT EXISTS') = 'NOT EXISTS' THEN
210 -- If there is another record for the same person on same effective date, skip that record.
211 -- Bug 4127797 TAR 4256507.995
212 IF (NVL(l_person_id,hr_api.g_number) = l_c_futr_actions.person_id AND
213 NVL(l_effective_date,hr_api.g_eot) = l_c_futr_actions.effective_date) THEN
214 NULL;
215 ELSE
216 IF l_batch_counter >= l_batch_size AND NVL(l_person_id,hr_api.g_number) <> l_c_futr_actions.person_id THEN
217 l_batch_no := l_batch_no + 1;
218 l_batch_counter := 0;
219 END IF;
220 --Bug#3726290 Add the separation Business Rule here.
221
222 -- Insert values into the table
223 INSERT INTO GHR_MTS_TEMP(session_id, batch_no, pa_request_id, action_type)
224 VALUES(l_session_id,l_batch_no,l_c_futr_actions.pa_request_id, 'FUTURE');
225 l_person_id := l_c_futr_actions.person_id;
226 l_effective_date := l_c_futr_actions.effective_date;
227 l_batch_counter := l_batch_counter + 1;
228 END IF;
229 ELSE
230 --Bug#3726290 close the RPA, if p_result <> 'NOT EXISTS'
231 fnd_profile.get('USERNAME',l_user_name);
232 --bug#4896738
233 --hr_utility.set_location('Intial value of g_skip_grp_box '||decode(g_skip_grp_box,TRUE,1,0),9876);
234 g_skip_grp_box := TRUE;
235 --hr_utility.set_location('value before calling end sf52 g_skip_grp_box '||decode(g_skip_grp_box,TRUE,1,0),9875);
236 BEGIN --Bug# 6753024, Since we r not able to reproduce the issue. Fixing the issue as suggested by the
237 --customers. ref: 37668_CustomizedFuturesCode.doc
238 ghr_SF52_api.end_SF52
239 (p_validate => false
240 ,p_pa_request_id => l_c_futr_actions.pa_request_id
241 ,p_par_object_version_number => l_c_futr_actions.object_version_number
242 ,p_action_taken => 'CANCELED'
243 ,p_user_name => l_user_name
244 ,p_first_noa_code => '355'
245 );
246 l_log_text := substr(
247 'PA_REQUEST_ID: ' || to_char(l_c_futr_actions.pa_request_id) || ' ; ' ||
248 'Employee Name: ' || l_c_futr_actions.employee_last_name || ' ; ' || l_c_futr_actions.employee_first_name || l_new_line ||
249 'SSN: ' || l_c_futr_actions.employee_national_identifier || ' ; ' ||
250 'First NOA Code: ' || l_c_futr_actions.first_noa_code ||
251 ' has been closed as pending conversion action/Temp Appointment action ' || l_new_line ||
252 'is pending.' , 1, 2000);
253 create_ghr_errorlog(
254 p_program_name => g_futr_proc_name,
255 p_log_text => l_log_text,
256 p_message_name => '355_Business_Rule_Violation',
257 p_log_date => sysdate
258 );
259 l_person_id := l_c_futr_actions.person_id;
260 l_effective_date := l_c_futr_actions.effective_date;
261 l_batch_counter := l_batch_counter + 1;
262 --bug#4896738
263 g_skip_grp_box := FALSE;
264 -- hr_utility.set_location('value after resetting '||decode(g_skip_grp_box,TRUE,1,0),9874);
265 --Begin Bug# 6753024
266 EXCEPTION
267 WHEN OTHERS
268 THEN
269 l_log_text := SUBSTR (
270 'PA_REQUEST_ID: '
271 || TO_CHAR (l_c_futr_actions.pa_request_id)
272 || ' ; '
273 || 'Employee Name: '
274 || l_c_futr_actions.employee_last_name
275 || ' ; '
276 || l_c_futr_actions.employee_first_name
277 || l_new_line
278 || 'SSN: '
279 || l_c_futr_actions.employee_national_identifier
280 || ' ; '
281 || 'First NOA Code: '
282 || l_c_futr_actions.first_noa_code
283 || l_new_line
284 || ' Error Code: '
285 || SQLCODE
286 || ' Error Msg: '
287 || SQLERRM
288 || l_new_line,
289 1,
290 2000
291 );
292 create_ghr_errorlog (
293 p_program_name => g_futr_proc_name,
294 p_log_text => l_log_text,
295 p_message_name => '355_Buss_Rule_error',
296 p_log_date => SYSDATE
297 );
298
299 END;
300 --End Bug# 6753024
301 END IF;
302 END LOOP;
303 ELSE
304 FOR l_c_futr_actions IN c_futr_actions_poi(p_poi) LOOP
305 l_result := NULL;
306 -- Bug#3726290 New business rule for NOAC 355
307 IF l_c_futr_actions.first_noa_code = '355' THEN
308 verify_355_business_rule(l_c_futr_actions.person_id,
309 l_c_futr_actions.effective_date,
310 l_result);
311 END IF;
312 IF NVL(l_result,'NOT EXISTS') = 'NOT EXISTS' THEN
313 -- If there is another record for the same person on same effective date, skip that record.
314 -- Bug 4127797 TAR 4256507.995
315 IF (NVL(l_person_id,hr_api.g_number) = l_c_futr_actions.person_id AND
316 NVL(l_effective_date,hr_api.g_eot) = l_c_futr_actions.effective_date) THEN
317 NULL;
318 ELSE
319 IF l_batch_counter >= l_batch_size AND NVL(l_person_id,hr_api.g_number) <> l_c_futr_actions.person_id THEN
320 l_batch_no := l_batch_no + 1;
321 l_batch_counter := 0;
322 END IF;
323 -- Insert values into the table
324 INSERT INTO GHR_MTS_TEMP(session_id, batch_no, pa_request_id, action_type)
325 VALUES(l_session_id,l_batch_no,l_c_futr_actions.pa_request_id, 'FUTURE');
326 l_person_id := l_c_futr_actions.person_id;
327 l_effective_date := l_c_futr_actions.effective_date;
328 l_batch_counter := l_batch_counter + 1;
329 END IF;
330 ELSE
331 --Bug#3726290 close the RPA, if p_result <> 'NOT EXISTS'
332 fnd_profile.get('USERNAME',l_user_name);
333 --bug#4896738
334 --hr_utility.set_location('Intial value of g_skip_grp_box '||g_skip_grp_box,9873);
335 g_skip_grp_box := TRUE;
336 --hr_utility.set_location('value before calling end sf52 g_skip_grp_box '||g_skip_grp_box,9872);
337 BEGIN --Bug# 6753024
338 ghr_SF52_api.end_SF52
339 (p_validate => false
340 ,p_pa_request_id => l_c_futr_actions.pa_request_id
341 ,p_par_object_version_number => l_c_futr_actions.object_version_number
342 ,p_action_taken => 'CANCELED'
343 ,p_user_name => l_user_name
344 ,p_first_noa_code => '355'
345 );
346 l_log_text := substr(
347 'PA_REQUEST_ID: ' || to_char(l_c_futr_actions.pa_request_id) || ' ; ' ||
348 'Employee Name: ' || l_c_futr_actions.employee_last_name || ' ; ' || l_c_futr_actions.employee_first_name || l_new_line ||
349 'SSN: ' || l_c_futr_actions.employee_national_identifier || ' ; '||
350 'First NOA Code: ' || l_c_futr_actions.first_noa_code ||
351 ' has been closed as pending conversion action/Temp Appointment action ' || l_new_line ||
352 'is pending.' , 1, 2000);
353 create_ghr_errorlog(
354 p_program_name => g_futr_proc_name,
355 p_log_text => l_log_text,
356 p_message_name => '355_Business_Rule_Violation',
357 p_log_date => sysdate
358 );
359 l_person_id := l_c_futr_actions.person_id;
360 l_effective_date := l_c_futr_actions.effective_date;
361 --bug#4896738
362 g_skip_grp_box := FALSE;
363 -- hr_utility.set_location('value after resetting '||g_skip_grp_box,9871);
364 --Begin Bug# 6753024
365 EXCEPTION
366 WHEN OTHERS
367 THEN
368 l_log_text := SUBSTR (
369 'PA_REQUEST_ID: '
370 || TO_CHAR (l_c_futr_actions.pa_request_id)
371 || ' ; '
372 || 'Employee Name: '
373 || l_c_futr_actions.employee_last_name
374 || ' ; '
375 || l_c_futr_actions.employee_first_name
376 || l_new_line
377 || 'SSN: '
378 || l_c_futr_actions.employee_national_identifier
379 || ' ; '
380 || 'First NOA Code: '
381 || l_c_futr_actions.first_noa_code
382 || l_new_line
383 || ' Error Code: '
384 || SQLCODE
385 || ' Error Msg: '
386 || SQLERRM
387 || l_new_line,
388 1,
389 2000
390 );
391 create_ghr_errorlog (
392 p_program_name => g_futr_proc_name,
393 p_log_text => l_log_text,
394 p_message_name => '355_Buss_Rule_error',
395 p_log_date => SYSDATE
396 );
397 END; --End Bug# 6753024
398 END IF;
399 END LOOP;
400 END IF;
401 COMMIT;
402
403 -- Call child concurrent programs for each and every thread
404 l_log_text := 'Total number of employees: ' || l_count || ' : Number of Batches ' || l_batch_no || ' : Batch size ' || l_batch_size;
405 create_ghr_errorlog(
406 p_program_name => g_futr_proc_name,
407 p_log_text => l_log_text,
408 p_message_name => 'Number of Batches',
409 p_log_date => sysdate
410 );
411 COMMIT;
412 -- Commented for testing
413 FOR l_thread IN 1..l_batch_no LOOP
414 -- Concurrent program
415 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
416 application => 'GHR',
417 program => 'GHSUBPROCFUTMT',
418 sub_request => FALSE,
419 argument1=> l_session_id, -- Session ID
420 argument2=> l_thread, -- Batch no
421 argument3=> l_parent_request_id -- Parent request id
422 );
423 g_request_ids(l_thread) := l_request_id;
424 -- fnd_conc_global.set_req_globals(conc_status => 'PAUSED');
425 END LOOP;
426
427 COMMIT;
428
429 IF g_request_ids.COUNT > 0 THEN
430 -- Wait for the child concurrent programs to get finished
431 FOR l_thread_count IN 1..l_batch_no LOOP
432 l_status := TRUE;
433 hr_utility.set_location('batch ' || l_thread_count,1000);
434 hr_utility.set_location('request id ' || g_request_ids(l_thread_count),1000);
435 WHILE l_status = TRUE LOOP
436 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(g_request_ids(l_thread_count),'','',rphase,rstatus,dphase,dstatus, message);
437 hr_utility.set_location('dphase ' || dphase,1000);
438 IF dphase = 'COMPLETE' THEN
439 l_status := FALSE;
440 ELSE
441 dbms_lock.sleep(5);
442 END IF;
443 END LOOP;
444 END LOOP;
445 END IF;
446
447 FOR l_cur_compl_status IN c_completion_status(l_session_id) LOOP
448 l_completion_status := l_cur_compl_status.max_status;
449 END LOOP;
450
451 --hr_utility.trace_off;
452 -- Assigning Return codes
453 IF l_completion_status = 2 THEN
454 p_retcode := 2;
455 p_errbuf := 'There were errors in SF52''s which could NOT be routed to approver''s Inbox. Detail in GHR_PROCESS_LOG';
456 ELSIF l_completion_status = 1 THEN
457 p_retcode := 1;
458 p_errbuf := 'There were errors in SF52''s which were routed to approver''s Inbox. Detail in GHR_PROCESS_LOG' ;
459 ELSE
460 p_retcode := 0;
461 END IF;
462
463 -- Bug # 7510344
464 --setting back the business group
465 if l_curr_business_group_id <> fnd_profile.value('PER_BUSINESS_GROUP_ID') then
466 fnd_profile.put('PER_BUSINESS_GROUP_ID',l_curr_business_group_id);
467 end if;
468
469 -- Delete the temporary table data.
470 DELETE FROM GHR_MTS_TEMP
471 WHERE session_id = l_session_id;
472
473 COMMIT;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 p_retcode := 1;
478 p_errbuf := SQLERRM;
479 DELETE FROM GHR_MTS_TEMP
480 WHERE session_id = l_session_id;
481 COMMIT;
482
483 END EXECUTE_MT;
484
485 -- ============================================================================
486 -- << Procedure: sub_proc_futr_act >>
487 -- Description:
488 -- This procedure is called from master conc. program. This procedure will
489 -- call RPA processing method. Also whenever error occurs the same is reported
490 -- to the process log.
491 -- ============================================================================
492
493 PROCEDURE SUB_PROC_FUTR_ACT(p_errbuf OUT NOCOPY VARCHAR2,
494 p_retcode OUT NOCOPY NUMBER,
495 p_session_id IN NUMBER,
496 p_batch_no IN NUMBER,
497 p_parent_request_id IN NUMBER)
498 IS
499 CURSOR c_batch_mts(c_session_id IN NUMBER,
500 c_batch_no IN NUMBER) IS
501 SELECT pa_request_id, batch_no
502 FROM GHR_MTS_TEMP
503 WHERE session_id = c_session_id
504 AND batch_no = c_batch_no;
505
506 CURSOR c_get_req(c_pa_request_id IN ghr_pa_requests.pa_request_id%type) IS
507 SELECT *
508 FROM ghr_pa_requests
509 WHERE pa_request_id = c_pa_request_id;
510
511 CURSOR c_sessionid is
512 select userenv('sessionid') sesid from dual;
513 -- Local Variables
514 l_sf52_rec ghr_pa_requests%rowtype;
515 l_new_line VARCHAR2(1);
516 l_log_text VARCHAR2(2000);
517 l_futr_proc_name VARCHAR2(50);
518 e_refresh EXCEPTION; -- Exception for refresh
519 l_retcode NUMBER;
520 l_result VARCHAR2(30);
521 l_error_message VARCHAR2(2000);
522 l_proc varchar2(30);
523 l_sid NUMBER;
524
525
526 -- Start of Bug 3602261
527
528 l_object_version_number ghr_pa_requests.object_version_number%type;
529
530 CURSOR c_ovn (p_pa_request_id ghr_pa_requests.pa_request_id%type) IS -- 3769917
531 SELECT par.object_version_number
532 FROM ghr_pa_requests par
533 WHERE par.pa_request_id = p_pa_request_id; -- 3769917
534
535 -- End of Bug 3602261
536
537 -- Bug # 7510344
538 cursor c_per_bus_group_id(p_person_id in per_people_f.person_id%TYPE,
539 p_effective_date in date)
540 is
541 select ppf.business_group_id
542 from per_people_f ppf
543 where ppf.person_id = p_person_id
544 and p_effective_date between ppf.effective_start_date
545 and ppf.effective_end_date;
546
547 l_bus_group_id per_all_people_f.business_group_id%type;
548 -- End of Bug # 7510344
549
550
551 BEGIN
552
553 FOR s_id IN c_sessionid
554 LOOP
555 l_sid := s_id.sesid;
556 EXIT;
557 END LOOP;
558
559 BEGIN
560 UPDATE fnd_sessions SET SESSION_ID = l_sid
561 WHERE SESSION_ID = l_sid;
562 IF SQL%NOTFOUND THEN
563 INSERT INTO fnd_sessions
564 (SESSION_ID,EFFECTIVE_DATE)
565 VALUES
566 (l_sid,sysdate);
567 END IF;
568 END;
569
570 -- Local initialization
571 l_new_line := substr('
572 ',1,1);
573 l_proc := 'SUB_PROC_FUTR_ACT';
574 g_futr_proc_name := 'GHR_Proc_Futr_Act' || '_' || p_parent_request_id;
575 -- Loop through the temporary table for that batch number and session id
576 FOR l_batch_mts IN c_batch_mts(p_session_id,p_batch_no) LOOP
577 -- Loop through the RPA record
578 FOR l_get_req IN c_get_req(l_batch_mts.pa_request_id) LOOP
579 l_sf52_rec := l_get_req;
580 --============================================
581 BEGIN
582 -- Process RPA
583 -- Bug 2639698 If To Pay is less than From Pay, no need to process. Just route it to inbox
584
585 -- FWFA Changes Bug#4444609 Added the following ELSIF condition
586 IF UPPER(SUBSTR(l_sf52_rec.request_number,1,3)) IN ('MTC','MSL') AND
587 l_sf52_rec.pay_rate_determinant IN ('3','4','J','K','U','V') AND
588 l_sf52_rec.effective_date >= to_date('01/05/2005','dd/mm/yyyy') AND
589 (l_sf52_rec.to_retention_allowance is NOT NULL OR
590 l_sf52_rec.to_retention_allow_percentage is NOT NULL)THEN
591 l_log_text := 'Request Number : ' || l_sf52_rec.request_number || l_new_line ||
592 'PA_REQUEST_ID : ' || to_char(l_sf52_rec.pa_request_id) || l_new_line ||
593 'Employee Name : ' || l_sf52_rec.employee_last_name || ' ,'
594 || l_sf52_rec.employee_first_name || l_new_line ||
595 'SSN : ' || l_sf52_rec.employee_national_identifier || l_new_line ||
596 'First NOA Code: ' || l_sf52_rec.first_noa_code || l_new_line ||
597 'Second NOA Code: ' || l_sf52_rec.second_noa_code || l_new_line ||
598 'Warning: The person has an existing retention allowance authorization. ' || l_new_line ||
599 'Action: Please review the retention allowance amount for this employee,' || l_new_line ||
600 'and process the action' ; -- Bug 3320086 Changed error message.
601
602 hr_utility.set_location(l_log_text,1511);
603 create_ghr_errorlog(
604 p_program_name => g_futr_proc_name,
605 p_log_text => l_log_text,
606 p_message_name => 'SF52 Routed to Inbox',
607 p_log_date => sysdate
608 );
609 Route_Errored_SF52(
610 p_sf52 => l_sf52_rec,
611 p_error => l_log_text,
612 p_result => l_result
613 );
614 l_retcode := 5; -- Error - but route to inbox
615 -- Bug 4699780 For cases with to salary less than from salary, it should be routed to inbox
616 -- only if it's not FWFA
617 /*ELSIF ( UPPER(SUBSTR(l_sf52_rec.request_number,1,3)) = 'MSL' AND l_sf52_rec.first_noa_code = '894')
618 AND (l_sf52_rec.to_basic_pay < l_sf52_rec.from_basic_pay) AND
619 NOT (l_sf52_rec.pay_rate_determinant IN ('3','4','J','K','U','V') AND
620 l_sf52_rec.effective_date >= to_date('01/05/2005','dd/mm/yyyy')) THEN
621 l_log_text := 'Request Number : ' || l_sf52_rec.request_number || l_new_line ||
622 'PA_REQUEST_ID : ' || to_char(l_sf52_rec.pa_request_id) || l_new_line ||
623 'Employee Name : ' || l_sf52_rec.employee_last_name || ' ,'
624 || l_sf52_rec.employee_first_name || l_new_line ||
625 'SSN : ' || l_sf52_rec.employee_national_identifier || l_new_line ||
626 'First NOA Code: ' || l_sf52_rec.first_noa_code || l_new_line ||
627 'Second NOA Code: ' || l_sf52_rec.second_noa_code || l_new_line ||
628 'Error: The From Side Basic Pay exceeds the To Side Basic Pay. ' || l_new_line ||
629 'Cause: The Personnel Action attempted to update the employee''s salary with a ' || l_new_line ||
630 'decreased amount of Basic Pay. ' || l_new_line ||
631 'Action: Please review the personnel action to verify the Grade and Step, Pay Table amounts,' || l_new_line ||
632 'and Pay Rate Determinant code for this employee.' ; -- Bug 3320086 Changed error message.
633
634 hr_utility.set_location(l_log_text,1511);
635 create_ghr_errorlog(
636 p_program_name => g_futr_proc_name,
637 p_log_text => l_log_text,
638 p_message_name => 'SF52 Routed to Inbox',
639 p_log_date => sysdate
640 );
641 Route_Errored_SF52(
642 p_sf52 => l_sf52_rec,
643 p_error => l_log_text,
644 p_result => l_result
645 );
646 l_retcode := 5; -- Error - but route to inbox --
647 -- FWFA Changes
648 */
649 ELSE
650
651 --7510344
652 If l_sf52_rec.person_id is not null then
653 for c_per_bus_rec in c_per_bus_group_id(l_sf52_rec.person_id,l_sf52_rec.effective_date)
654 loop
655 l_bus_group_id := c_per_bus_rec.business_group_id;
656 exit;
657 end loop;
658 end if;
659
660 if l_bus_group_id <> fnd_profile.value('PER_BUSINESS_GROUP_ID') then
661 --Putting the BUSINESS GROUP_ID
662 fnd_profile.put('PER_BUSINESS_GROUP_ID',l_bus_group_id);
663 end if;
664 --7510344
665
666
667 SAVEPOINT future_Action;
668 GHR_PROCESS_SF52.Process_SF52(
669 p_sf52_data => l_sf52_rec,
670 p_process_type => 'FUTURE');
671
672 -- Start of Bug 3602261
673 ghr_sf52_post_update.get_notification_details
674 (p_pa_request_id => l_sf52_rec.pa_request_id,
675 p_effective_date => l_sf52_rec.effective_date,
676 p_from_position_id => l_sf52_rec.from_position_id,
677 p_to_position_id => l_sf52_rec.to_position_id,
678 p_agency_code => l_sf52_rec.agency_code,
679 p_from_agency_code => l_sf52_rec.from_agency_code,
680 p_from_agency_desc => l_sf52_rec.from_agency_desc,
681 p_from_office_symbol => l_sf52_rec.from_office_symbol,
682 p_personnel_office_id => l_sf52_rec.personnel_office_id,
683 p_employee_dept_or_agency => l_sf52_rec.employee_dept_or_agency,
684 p_to_office_symbol => l_sf52_rec.to_office_symbol
685 );
686 FOR ovn_rec IN c_ovn (l_sf52_rec.pa_request_id) LOOP
687 l_object_version_number := ovn_rec.object_version_number;
688 END LOOP;
689 ghr_par_upd.upd
690 (p_pa_request_id => l_sf52_rec.pa_request_id,
691 p_object_version_number => l_object_version_number,
692 p_from_position_id => l_sf52_rec.from_position_id,
693 p_to_position_id => l_sf52_rec.to_position_id,
694 p_agency_code => l_sf52_rec.agency_code,
695 p_from_agency_code => l_sf52_rec.from_agency_code,
696 p_from_agency_desc => l_sf52_rec.from_agency_desc,
697 p_from_office_symbol => l_sf52_rec.from_office_symbol,
698 p_personnel_office_id => l_sf52_rec.personnel_office_id,
699 p_employee_dept_or_agency => l_sf52_rec.employee_dept_or_agency,
700 p_to_office_symbol => l_sf52_rec.to_office_symbol
701 );
702 -- End of Bug 3602261
703
704 l_log_text := 'Request Number : ' || l_sf52_rec.request_number || l_new_line ||
705 'PA_REQUEST_ID : ' || to_char(l_sf52_rec.pa_request_id) || l_new_line ||
706 'Employee Name : ' || l_sf52_rec.employee_last_name || ' ,'
707 || l_sf52_rec.employee_first_name || l_new_line ||
708 'SSN : ' || l_sf52_rec.employee_national_identifier || l_new_line ||
709 'First NOA Code: ' || l_sf52_rec.first_noa_code || l_new_line ||
710 'Second NOA Code: ' || l_sf52_rec.second_noa_code || l_new_line ||
711 'Processed Successfully';
712
713 create_ghr_errorlog(
714 p_program_name => g_futr_proc_name,
715 p_log_text => l_log_text,
716 p_message_name => 'SF52 Processed Successfully',
717 p_log_date => sysdate
718 );
719 END IF; -- IF ( UPPER(SUBSTR(l_sf52_rec.request_number,1,3)) = 'MSL'
720 EXCEPTION
721 --Bug# 5634990 added the package GHR_PROCESS_SF52 exception and modified error msg
722 WHEN GHR_PROCESS_SF52.e_refresh THEN
723 BEGIN
724 ROLLBACK TO future_Action;
725 IF NVL(l_retcode, 0) <> 2 THEN
726 l_retcode := 1; /* warning */
727 END IF;
728 -- Enter a record in process log
729 l_log_text := SUBSTR(
730 'Request Number : ' || l_sf52_rec.request_number || l_new_line ||
731 'PA_REQUEST_ID : ' || to_char(l_sf52_rec.pa_request_id) || l_new_line ||
732 'Employee Name : ' || l_sf52_rec.employee_last_name || ' ,' ||
733 l_sf52_rec.employee_first_name || l_new_line ||
734 'SSN : ' || l_sf52_rec.employee_national_identifier || l_new_line ||
735 'First NOA Code: ' || l_sf52_rec.first_noa_code || l_new_line ||
736 'Second NOA Code: ' || l_sf52_rec.second_noa_code || l_new_line ||
737 'Action: RPA related information has changed. Retrieve the RPA from the groupbox to review the refreshed information, make necessary changes, and update HR',1,2000);
738 create_ghr_errorlog(
739 p_program_name => g_futr_proc_name,
740 p_log_text => l_log_text,
741 p_message_name => 'Future SF52 Routed to Inbox',--Bug#5634990
742 p_log_date => sysdate
743 );
744 l_error_message := 'Action: RPA related information has changed. Retrieve the RPA from the groupbox to review the refreshed information, make necessary changes, and update HR';--Bug#5634990
745 Route_Errored_SF52(
746 p_sf52 => l_sf52_rec,
747 p_error => substr(l_error_message,1 ,512),
748 p_result => l_result
749 );
750 COMMIT;
751 END;
752
753 WHEN OTHERS THEN
754 BEGIN
755 IF SQLCODE = -6508 then
756 -- Program Unit not found
757 -- This usually happens and the only solution know so far is
758 -- to re-start the conc. manager. So all the SF52's are routed unnecessarily
759 l_retcode := 2; /* Error*/
760 p_errbuf := ' Program raised Error - Program Unit not Found. Details in Process Log.';
761 l_log_text := substr('Initiate Process Future Dated SF52 Due For Processing Terminated due to following error : ' || Sqlerrm(sqlcode), 1, 2000);
762
763 ROLLBACK TO future_Action;
764 create_ghr_errorlog(
765 p_program_name => g_futr_proc_name,
766 p_log_text => l_log_text,
767 p_message_name => 'Process Terminated',
768 p_log_date => sysdate
769 );
770 COMMIT;
771 RETURN;
772 END IF;
773
774 ROLLBACK TO future_Action;
775 IF nvl(l_retcode, 0) <> 2 THEN
776 l_retcode := 1; /* warning */
777 END IF;
778
779 -- Enter a record in process log
780 l_log_text := substr(
781 'Request Number : ' || l_sf52_rec.request_number || l_new_line ||
782 'PA_REQUEST_ID : ' || to_char(l_sf52_rec.pa_request_id) || l_new_line ||
783 'Employee Name : ' || l_sf52_rec.employee_last_name || ' ,' ||
784 l_sf52_rec.employee_first_name || l_new_line ||
785 'SSN : ' || l_sf52_rec.employee_national_identifier || l_new_line ||
786 'First NOA Code: ' || l_sf52_rec.first_noa_code || l_new_line ||
787 'Second NOA Code: ' || l_sf52_rec.second_noa_code || l_new_line ||
788 'Error : ' || sqlerrm(sqlcode) , 1, 2000);
789 create_ghr_errorlog(
790 p_program_name => g_futr_proc_name,
791 p_log_text => l_log_text,
792 p_message_name => 'SF52 Errored Out',
793 p_log_date => sysdate
794 );
795 COMMIT;
796 EXCEPTION
797 WHEN OTHERS THEN
798 -- Error
799 l_retcode := 2;
800 p_errbuf := 'Process was errored out while creating Error Log. Error: ' || substr(sqlerrm(sqlcode), 1, 50);
801 RETURN;
802 END;
803 l_error_message := substr(sqlerrm(sqlcode), 1, 512);
804 Route_Errored_SF52(
805 p_sf52 => l_sf52_rec,
806 p_error => substr(l_error_message,1 ,512),
807 p_result => l_result
808 );
809 IF l_result = '2' THEN
810 l_retcode := 2;
811 END IF;
812 COMMIT;
813 END; -- End RPA Processing
814
815 END LOOP;
816
817 END LOOP;
818 -- Set Concurrent program completion messages
819
820 IF l_retcode = 2 THEN
821 p_retcode := 2;
822 hr_utility.set_location('Ret code ' || to_char(l_retcode),1);
823 p_errbuf := 'There were errors in SF52''s which could NOT be routed to approver''s Inbox. Detail in GHR_PROCESS_LOG';
824 ELSIF l_retcode = 5 THEN
825 p_retcode := 2;
826 hr_utility.set_location('Ret code ' || to_char(l_retcode),1);
827 p_errbuf := 'There were errors in SF52''s which were routed to approver''s Inbox. Detail in GHR_PROCESS_LOG';
828 ELSIF l_retcode IS NOT NULL THEN
829 -- Warning
830 p_retcode := 1;
831 p_errbuf := 'There were errors in SF52''s which were routed to approver''s Inbox. Detail in GHR_PROCESS_LOG' ;
832 ELSIF l_retcode IS NULL THEN
833 p_retcode := 0;
834 END IF;
835
836 -- Update the completion status.
837 UPDATE GHR_MTS_TEMP
838 SET completion_status = p_retcode
839 WHERE session_id = p_session_id
840 AND batch_no = p_batch_no;
841
842 COMMIT;
843
844
845 END SUB_PROC_FUTR_ACT;
846
847
848 --
849
850 -- ============================================================================
851 -- << Procedure: create_ghr_errorlog >>
852 -- Description:
853 -- This procedure inserts the log text into Federal Process log
854 -- ============================================================================
855
856 PROCEDURE create_ghr_errorlog(
857 p_program_name in ghr_process_log.program_name%type,
858 p_log_text in ghr_process_log.log_text%type,
859 p_message_name in ghr_process_log.message_name%type,
860 p_log_date in ghr_process_log.log_date%type
861 ) is
862
863 l_proc varchar2(30);
864 Begin
865 l_proc := 'create_ghr_errorlog';
866 hr_utility.set_location( 'Entering : ' || l_proc, 10);
867 insert into ghr_process_log
868 (process_log_id
869 ,program_name
870 ,log_text
871 ,message_name
872 ,log_date
873 )
874 values
875 (ghr_process_log_s.nextval
876 ,p_program_name
877 ,p_log_text
878 ,p_message_name
879 ,p_log_date
880 );
881 hr_utility.set_location( 'Leaving : ' || l_proc, 20);
882
883 END create_ghr_errorlog;
884
885
886 Procedure Route_Errored_SF52(
887 p_sf52 in out nocopy ghr_pa_requests%rowtype,
888 p_error in varchar2,
889 p_result out nocopy varchar2) is
890
891 l_u_prh_object_version_number number;
892 l_i_pa_routing_history_id number;
893 l_i_prh_object_version_number number;
894
895 l_log_text varchar2(2000);
896 l_proc varchar2(30);
897 l_new_line varchar2(1);
898 l_sf52 ghr_pa_requests%rowtype ;
899 Begin
900 l_proc := 'Route_Errerd_SF52';
901 l_new_line := substr('
902 ',1,1);
903 l_sf52 := p_sf52; --NOCOPY Changes
904 hr_utility.set_location( 'Entering : ' || l_proc, 10);
905 savepoint route_errored_sf52;
906 hr_utility.set_location( l_proc, 20);
907 l_log_text := 'Request Number : ' || p_sf52.request_number || l_new_line ||
908 'PA_REQUEST_ID : ' || to_char(p_sf52.pa_request_id) ||
909 ' has errors.' || l_new_line ||
910 'Error : ' || p_error || l_new_line ||
911 'Errored while routing it to the approver''s Inbox ';
912
913 ghr_api.call_workflow(
914 p_pa_request_id => p_sf52.pa_request_id,
915 p_action_taken => 'CONTINUE',
916 p_error => p_error);
917 hr_utility.set_location( 'Leaving : ' || l_proc, 20);
918 Exception
919 when others then
920 hr_utility.set_location(l_proc || ' workflow errored out', 30);
921 rollback to route_errored_sf52;
922 p_result := '0';
923 l_log_text := substr(
924 'Request Number : ' || p_sf52.request_number || l_new_line ||
925 'PA_REQUEST_ID : ' || to_char(p_sf52.pa_request_id) || l_new_line ||
926 'Employee Name : ' || p_SF52.employee_last_name || ' ,' || p_sf52.employee_first_name || l_new_line ||
927 'SSN : ' || p_sf52.employee_national_identifier || l_new_line ||
928 'First NOA Code: ' || p_sf52.first_noa_code || l_new_line ||
929 'Second NOA Code: ' || p_sf52.second_noa_code || l_new_line ||
930 'Errored while routing it to the approver''s Inbox ' || l_new_line ||
931 'Error : ' || sqlerrm(sqlcode), 1, 2000);
932 create_ghr_errorlog(
933 p_program_name => g_futr_proc_name,
934 p_log_text => l_log_text,
935 p_message_name => 'Routing Error',
936 p_log_date => sysdate
937 );
938 hr_utility.set_location(l_proc , 40);
939 p_result := '2';
940 p_sf52 := l_sf52; --Added for nocopy changes
941
942 End Route_Errored_SF52;
943
944 -- ============================================================================
945 -- << Procedure: verify_355_business_rule >>
946 -- Bug#3726290
947 -- Description:
948 -- This procedure verifies the business rule implemented for NOA code 355
949 -- ============================================================================
950 -- created the procedure to implement new business rule for NOAC 355
951
952 PROCEDURE verify_355_business_rule(
953 p_person_id IN NUMBER,
954 p_effective_date IN DATE,
955 p_result OUT NOCOPY VARCHAR2
956 ) IS
957
958 l_proc VARCHAR2(30);
959 l_dummy VARCHAR2(30);
960 l_ovn NUMBER;
961
962 CURSOR c_pending_action_exists(c_person_id NUMBER, c_effective_date Date) IS
963 SELECT 'X'
964 FROM ghr_pa_requests a, ghr_pa_routing_history b
965 WHERE a.effective_date between (c_effective_date - 2) and (c_effective_date + 1)
966 AND a.person_id = c_person_id
967 AND (substr(a.first_noa_code,1,1) = '5' OR a.first_noa_code IN ('760','762','765'))
968 AND pa_notification_id IS NULL
969 AND approval_date IS NOT NULL
970 AND a.pa_request_id = b.pa_request_id
971 AND action_taken = 'FUTURE_ACTION'
972 AND EXISTS
973 (SELECT 1
974 FROM per_people_f per
975 WHERE per.person_id = a.person_id
976 AND a.effective_date BETWEEN
977 per.effective_start_date AND per.effective_end_date )
978 AND b.pa_routing_history_id = (SELECT max(pa_routing_history_id)
979 FROM ghr_pa_routing_history
980 WHERE pa_request_id = a.pa_request_id);
981 CURSOR c_processed_action_exists(c_person_id NUMBER, c_effective_date DATE) IS
982 SELECT 'Y'
983 FROM ghr_pa_requests
984 WHERE effective_date between (c_effective_date - 14) and (c_effective_date + 1)
985 AND person_id = c_person_id
986 AND (substr(first_noa_code,1,1) = '5' OR first_noa_code IN ('760','762','765'))
987 AND pa_notification_id IS NOT NULL
988 AND (NVL(first_noa_cancel_or_correct,'C') <> 'CANCEL' OR NVL(second_noa_cancel_or_correct,'C') <> 'CANCEL');
989
990 BEGIN
991 l_proc := 'verify_355_business_rule';
992 hr_utility.set_location( 'Entering : ' || l_proc, 10);
993 OPEN c_pending_action_exists(p_person_id,p_effective_date);
994 FETCH c_pending_action_exists into l_dummy;
995 IF c_pending_action_exists%NOTFOUND THEN
996 close c_pending_action_exists;
997 OPEN c_processed_action_exists(p_person_id,p_effective_date);
998 FETCH c_processed_action_exists into l_dummy;
999 IF c_processed_action_exists%NOTFOUND THEN
1000 p_result := 'NOT EXISTS';
1001 ELSE
1002 p_result := 'EXISTS';
1003 END IF;
1004 CLOSE c_processed_action_exists;
1005 ELSE
1006 p_result := 'EXISTS';
1007 close c_pending_action_exists;
1008 END IF;
1009
1010 hr_utility.set_location( 'Leaving : ' || l_proc, 20);
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 p_result := NULL;
1014 raise;
1015 END verify_355_business_rule;
1016
1017
1018 END GHR_PROC_FUT_MT;