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