1 PACKAGE BODY HXT_RETRO_PROCESS AS
2 /* $Header: hxtrprc.pkb 120.7 2011/08/22 09:53:58 asrajago ship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 FUNCTION Call_Gen_Error( p_batch_id IN NUMBER
5 , p_location IN VARCHAR2
6 , p_error_text IN VARCHAR2
7 , p_oracle_error_text IN VARCHAR2 default NULL ) RETURN NUMBER;
8
9
10 -----------------------------------------------------------------
11 PROCEDURE Main_Retro (
12 errbuf OUT NOCOPY VARCHAR2,
13 retcode OUT NOCOPY NUMBER,
14 p_payroll_id IN NUMBER,
15 p_date_earned IN VARCHAR2,
16 p_retro_batch_id IN NUMBER DEFAULT NULL,
17 p_retro_batch_id_end IN NUMBER DEFAULT NULL,
18 p_ref_num IN VARCHAR2 DEFAULT NULL,
19 p_process_mode IN VARCHAR2,
20 p_bus_group_id IN NUMBER,
21 p_merge_flag IN VARCHAR2 DEFAULT '0',
22 p_merge_batch_name IN VARCHAR2 DEFAULT NULL,
23 p_merge_batch_specified IN VARCHAR2 DEFAULT NULL
24 ) IS
25 -- Cursor returns all batch's with timecards for specified payroll,
26 -- time period, batch id, and batch ref that haven't been transferred.
27
28
29 -- Bug 9876599
30 -- Added condition to ignore batches which are Archived.
31 -- Either fully or partially.
32
33 CURSOR cur_batch(c_payroll_id NUMBER,
34 c_retro_batch_id NUMBER,
35 c_reference_num VARCHAR2) IS
36 SELECT distinct(det.retro_batch_id) batch_id,
37 tim.id tim_id,
38 tbs.status batch_status,
39 pbh.object_version_number
40 FROM pay_batch_headers pbh,
41 hxt_batch_states tbs,
42 hxt_timecards_x tim,
43 hxt_det_hours_worked_x det
44 WHERE det.pay_status = 'R'
45 AND tbs.batch_id = det.retro_batch_id
46 AND det.retro_batch_id BETWEEN nvl(c_retro_batch_id,0)
47 AND nvl(c_retro_batch_id,999999999999)
48 AND (pbh.batch_reference LIKE nvl(c_reference_num , '%')
49 OR (pbh.batch_reference IS NULL AND c_reference_num IS NULL))
50 AND tbs.status in ('VE','H','VT','VV','VW') -- RETROPAY
51 AND tim.id = det.tim_id
52 AND pbh.batch_id = tbs.batch_id
53 AND NOT EXISTS( SELECT 1
54 FROM hxt_det_hours_worked_f_ar ar
55 WHERE ar.retro_batch_id = pbh.batch_id)
56 AND pbh.business_group_id = p_bus_group_id;
57
58 batch_rec cur_batch%ROWTYPE;
59 --
60 -- v115.11 start
61 -- Adding cursor to retrieve all valid batch ranges.
62
63 -- Bug 12855865
64 -- Modified the cursor below to take a start and end batch.
65
66 /*
67
68 cursor c_batch_ranges is
69 SELECT pbh.batch_name, pbh.batch_id
70 FROM pay_batch_headers pbh
71 WHERE pbh.business_group_id = p_bus_group_id
72 AND EXISTS (SELECT 'x'
73 FROM hxt_det_hours_worked_x det, hxt_batch_states hbs
74 WHERE hbs.batch_id = pbh.batch_id
75 AND det.retro_batch_id = hbs.batch_id
76 AND hbs.status in ('VE','H','VT','VV','VW'))
77 ORDER BY pbh.batch_id;
78 */
79
80 cursor c_batch_ranges (p_start IN NUMBER,
81 p_end IN NUMBER)
82 is
83 SELECT pbh.batch_id
84 FROM pay_batch_headers pbh
85 WHERE pbh.business_group_id = p_bus_group_id
86 AND pbh.batch_id BETWEEN p_start
87 AND p_end
88 AND EXISTS (SELECT 'x'
89 FROM hxt_det_hours_worked_x det, hxt_batch_states hbs
90 WHERE hbs.batch_id = pbh.batch_id
91 AND det.retro_batch_id = hbs.batch_id
92 AND hbs.status in ('VE','H','VT','VV','VW'))
93 ORDER BY pbh.batch_id;
94
95
96 -- v115.11 end
97 -- local variables
98 --
99 l_batch_id NUMBER;
100 l_process_mode VARCHAR2(80);
101 l_session_date DATE;
102 l_batch_status VARCHAR2(30);
103 l_pay_retcode NUMBER DEFAULT 0;
104 l_valid_retcode NUMBER DEFAULT 0;
105 l_sum_retcode NUMBER DEFAULT 0;
106 l_main_retcode NUMBER DEFAULT 0;
107 l_final_pay_retcode NUMBER DEFAULT 0;
108 l_final_valid_retcode NUMBER DEFAULT 0;
109 l_final_main_retcode NUMBER DEFAULT 0;
110 l_final_sum_retcode NUMBER DEFAULT 0;
111 l_rollback_retcode NUMBER DEFAULT 0;
112 l_final_rollback_retcode NUMBER DEFAULT 0;
113 l_errbuf VARCHAR2(2000)DEFAULT NULL;
114 v_err_buf VARCHAR2(2000)DEFAULT NULL;
115 l_retcode NUMBER DEFAULT 0;
116 l_date_earned DATE := to_date(p_date_earned,'YYYY/MM/DD HH24:MI:SS');
117 l_kounter NUMBER DEFAULT 0;
118 l_payroll_id VARCHAR2(30)DEFAULT NULL;
119 l_retro_batch_id NUMBER; --BSE115M
120 l_return NUMBER;
121 l_trans_batch_status NUMBER DEFAULT 0;
122 l_trans_status_code VARCHAR2(10)DEFAULT NULL;
123 l_period_end_date DATE;
124 b_we_have_batches BOOLEAN DEFAULT TRUE;
125 -- v115.11 start
126 -- adding new variables
127 l_starting_batch_num NUMBER;
128 l_ending_batch_num NUMBER;
129
130 TYPE NUMTAB IS TABLE OF NUMBER;
131 l_batches NUMTAB;
132
133 TYPE v_bat_rec IS RECORD (
134 batch_id NUMBER (15));
135
136 TYPE r_bat_rec IS TABLE OF v_bat_rec
137 INDEX BY BINARY_INTEGER;
138
139 list_batch_rec_ids r_bat_rec;
140 l_index BINARY_INTEGER;
141
142 b_start BOOLEAN;
143 b_stop BOOLEAN;
144
145 -- v115.11 end
146
147 /********Bug: 4620315 **********/
148
149 l_cnt BINARY_INTEGER;
150 l_count BINARY_INTEGER;
151 l_loop_index BINARY_INTEGER;
152 l_loop_flag BOOLEAN;
153 l_merge_batches HXT_BATCH_PROCESS.MERGE_BATCHES_TYPE_TABLE;
154 p_merge_batches HXT_BATCH_PROCESS.MERGE_BATCHES_TYPE_TABLE;
155 l_del_empty_batches HXT_BATCH_PROCESS.DEL_EMPTY_BATCHES_TYPE_TABLE;
156
157 /********Bug: 4620315 **********/
158
159 BEGIN
160 g_debug := hr_utility.debug_enabled;
161 -- commented out. using sysdate view, now. RTF
162 -- insert into fnd_sessions values (userenv('SESSIONID'), trunc(SYSDATE));
163 -- commit;
164 --HXT_UTIL.DEBUG('Start process.');-- debug only --HXT115
165 -- time period will be ignored completely when passed in as NULL
166 l_date_earned := to_date(to_char(trunc(l_date_earned),'DD/MM/RRRR'),'DD/MM/RRRR');
167 l_payroll_id := to_char(p_payroll_id);
168 l_retro_batch_id := p_retro_batch_id; --BSE115M
169 --
170 --Validate , Transfer, or Rollback TAMS/O data
171 --
172 --
173 -- v115.11 start
174 -- check for ranges
175 l_starting_batch_num := p_retro_batch_id;
176 l_ending_batch_num := p_retro_batch_id_end;
177 --
178 -- Table population
179 l_index := 1;
180 IF l_ending_batch_num IS NULL THEN
181 IF l_starting_batch_num IS NOT NULL THEN
182 list_batch_rec_ids(l_index).batch_id := l_starting_batch_num;
183 ELSE
184 list_batch_rec_ids(l_index).batch_id := null;
185 END IF;
186 ELSE
187 IF l_starting_batch_num IS NULL THEN
188 list_batch_rec_ids(l_index).batch_id := l_ending_batch_num;
189 END IF;
190 END IF;
191 --
192 -- Initialize booleans
193 b_start := FALSE;
194 b_stop := FALSE;
195
196 IF l_starting_batch_num > l_ending_batch_num THEN
197 b_stop := TRUE;
198 END IF;
199
200 --
201 -- Determine if a range has been selected by the user
202 --
203 IF l_starting_batch_num IS NOT NULL AND l_ending_batch_num IS NOT NULL AND b_stop = FALSE
204 THEN
205
206 -- Bug 12855865
207 -- Commented out this construct of blindly picking up all the batches in the bg id
208 -- one by one and scanning the start and end records.
209 /*
210
211 For C_All_batches in c_batch_ranges
212 Loop
213 if (C_All_batches.batch_id = l_starting_batch_num)
214 then
215 b_start := TRUE;
216 end if;
217
218 -- Add to table
219 if b_start = TRUE then
220 list_batch_rec_ids(l_index).batch_id := C_All_batches.batch_id;
221 l_index := l_index+1;
222 end if;
223
224 if (C_All_batches.batch_id = l_ending_batch_num)
225 then
226 b_start := FALSE;
227 end if;
228 End Loop; -- C_All_batches
229
230
231 For C_All_batches in c_batch_ranges(l_starting_batch_num,
232 l_ending_batch_num)
233 Loop
234 list_batch_rec_ids(l_index).batch_id := C_All_batches.batch_id;
235 l_index := l_index+1;
236 End Loop; -- C_All_batches
237
238 */
239 -- Replaced it with one BULK collect and copy into the list.
240
241 OPEN c_batch_ranges(l_starting_batch_num,
242 l_ending_batch_num);
243 FETCH c_batch_ranges BULK COLLECT INTO l_batches;
244 CLOSE c_batch_ranges;
245
246 IF l_batches.COUNT > 0
247 THEN
248 FOR i IN l_batches.FIRST..l_batches.LAST
249 LOOP
250 list_batch_rec_ids(l_index).batch_id := l_batches(i);
251 l_index := l_index +1;
252 END LOOP;
253 END IF;
254
255 End If; -- l_starting_batch_num and l_ending_batch_num IS NOT NULL
256
257 --
258 -- v115.11 end
259 --
260 --
261 -- Loop through all retro batches in payroll specified by user
262 --
263 -- v115.11
264 -- Change looping to use the newly populated PL/SQL table list_batch_rec_ids
265
266 l_index := null;
267 l_index := list_batch_rec_ids.first;
268
269 WHILE l_index is not null LooP
270 BEGIN
271 --HXT_UTIL.DEBUG('Beginning we have batches loop');
272 --
273 -- Select and process all user specified batches for this payroll/reference number
274 -- Process batch range specified by the user, else do all available
275 --
276 l_loop_flag := TRUE;
277
278 HXT_UTIL.DEBUG('payroll_id = '||to_char(p_payroll_id)||' retro_batch_id = '
279 ||to_char(list_batch_rec_ids(l_index).batch_id)||' ref_num = '||p_ref_num);
280 FOR batch_rec IN cur_batch(p_payroll_id, list_batch_rec_ids(l_index).batch_id, p_ref_num ) LOOP
281 --HXT_UTIL.DEBUG('Batch number is ' || TO_CHAR(batch_rec.batch_id));
282 l_batch_id := batch_rec.batch_id;
283 l_kounter := l_kounter + 1;
284 --
285 -- rollback all PayMix data per user request
286 --
287 IF p_process_mode = 'V' THEN
288
289 /********Bug: 4620315 **********/
290 /*** To record empty batch details ***/
291
292 IF (p_merge_flag = '1' and l_loop_flag = TRUE) THEN
293
294 IF g_debug THEN
295 hr_utility.trace('Populating del_empty_batches record: '||'batchid: '||batch_rec.batch_id||
296 ' ovn '||batch_rec.object_version_number);
297 END IF;
298
299 l_cnt := NVL(l_del_empty_batches.LAST,0) +1;
300 l_del_empty_batches(l_cnt).batch_id := batch_rec.batch_id;
301 l_del_empty_batches(l_cnt).batch_ovn := batch_rec.object_version_number;
302 l_loop_flag := FALSE;
303 END IF;
304
305 /********Bug: 4620315 **********/
306
307 -- Check for a valid status code
308 IF (batch_rec.batch_status = 'VT') THEN
309 l_final_valid_retcode := 2;
310 FND_MESSAGE.SET_NAME('HXT','HXT_39348_TC_VAL_NOT_REPROC'); --HXT111
311 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( batch_rec.batch_id,
312 'VE',
313 '',
314 l_return);
315 ELSE
316 --
317 -- Validate batch, status:0=Normal, 1=Warning, 2=Stop Level Data Error, 3=System
318 --
319 --HXT_UTIL.DEBUG('Begin timecard validation.');
320 -- Delete prior errors for this batch
321 HXT_BATCH_PROCESS.Del_Prior_Errors(batch_rec.batch_id);
322 HXT_RETRO_VAL.Val_Retro_Timecard(batch_rec.batch_id,
323 batch_rec.tim_id,
324 l_valid_retcode,
325 p_merge_flag,
326 p_merge_batches);
327
328 /********Bug: 4620315 **********/
329 /*** To record validated TCs details ***/
330
331 IF p_merge_flag = '1' THEN
332 l_loop_index := p_merge_batches.first;
333 LOOP
334 EXIT WHEN NOT p_merge_batches.exists(l_loop_index);
335 l_count := NVL(l_merge_batches.LAST,0) +1;
336 l_merge_batches(l_count).batch_id := p_merge_batches(l_loop_index).batch_id;
337 l_merge_batches(l_count).tc_id := p_merge_batches(l_loop_index).tc_id;
338 l_merge_batches(l_count).valid_tc_retcode := p_merge_batches(l_loop_index).valid_tc_retcode;
339 l_loop_index := p_merge_batches.next(l_loop_index);
340 END LOOP;
341 END IF;
342
343 /********Bug: 4620315 **********/
344
345 --
346 -- Set error return code from concurrent process
347 --
348 IF l_valid_retcode > l_final_valid_retcode then
349 l_final_valid_retcode := l_valid_retcode;
350 END IF;
351 -- Successful Validation, Set batch to ready Status
352 IF l_valid_retcode = 0 then
353 --HXT_UTIL.DEBUG('Successful timecard validation.');
354 HXT_BATCH_PROCESS.Set_batch_status(l_date_earned, batch_rec.batch_id, 'VV');
355 END IF;
356 -- set status to Warning and lets user know we have a TAMS/O
357 -- User Level Data Error for this batch
358 IF l_valid_retcode = 1 then
359 --HXT_UTIL.DEBUG('Timecard validation warnings.');
360 HXT_BATCH_PROCESS.Set_batch_status(l_date_earned, batch_rec.batch_id, 'VW');
361 FND_MESSAGE.SET_NAME('HXT','HXT_39349_CHK_IND_TCARD_ERRS'); --HXT111
362 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( batch_rec.batch_id,
363 'W',
364 '',
365 l_return);
366 END IF;
367 IF l_valid_retcode > 2 THEN
368 --HXT_UTIL.DEBUG('Timecard validation errors.');
369 HXT_BATCH_PROCESS.Set_batch_status(l_date_earned, batch_rec.batch_id, 'VE');
370 FND_MESSAGE.SET_NAME('HXT','HXT_39349_CHK_IND_TCARD_ERRS');
371 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( batch_rec.batch_id,
372 'VE',
373 '',
374 l_return);
375 END IF;
376 END IF; -- valid status code
377 --
378 -- Process transfer to PayMIX
379 --
380 ELSIF p_process_mode = 'T' THEN
381 -- Don't allow batches in a Hold status to be Transferred to PayMIX
382 IF batch_rec.batch_status = 'H' THEN
383 l_final_valid_retcode := 2;
384 FND_MESSAGE.SET_NAME('HXT','HXT_39350_CANT_TRANS_HLD_PAYMX');
385 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( batch_rec.batch_id,
386 'VE',
387 '',
388 l_return);
389 -- Don't move to PayMIX while Timecard errors exist
390 ELSIF batch_rec.batch_status in ('VE','ET') THEN -- RETROPAY
391 l_final_valid_retcode := 2;
392 FND_MESSAGE.SET_NAME('HXT','HXT_39351_CANT_TRANS_ERR_PAYMX');
393 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( batch_rec.batch_id,
394 'VE',
395 '',
396 l_return);
397 ELSIF (batch_rec.batch_status = 'VT') THEN
398 l_final_valid_retcode := 2;
399 FND_MESSAGE.SET_NAME('HXT','HXT_39352_BTCHS_PREV_TRANS');
400 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( batch_rec.batch_id,
401 'VE',
402 '',
403 l_return);
404 ELSIF batch_rec.batch_status in ('VV','VW') THEN -- RETROPAY
405 HXT_UTIL.DEBUG('Now moving to PayMIX.');
406 HXT_RETRO_MIX.retro_sum_to_mix(batch_rec.batch_id,
407 batch_rec.tim_id, l_sum_retcode,
408 v_err_buf);
409 HXT_UTIL.DEBUG('back from moving to PayMIX. v_er_buf is '||v_err_buf);
410 HXT_UTIL.DEBUG('back from moving to PayMIX. l_sum_retcode is '||to_char(l_sum_retcode));
411 IF l_sum_retcode > l_final_sum_retcode then
412 l_final_sum_retcode := l_sum_retcode;
413 END IF;
414 IF (l_sum_retcode = 0) then
415 --HXT_UTIL.DEBUG('Successful move to PayMIX.');
416 HXT_BATCH_PROCESS.Set_batch_status(l_date_earned, batch_rec.batch_id, 'VT');
417 ELSE
418 retcode := 2;
419 FND_MESSAGE.SET_NAME('HXT','HXT_39452_RETRO_SYSTEM_ERROR');
420 IF v_err_buf IS NULL THEN
421 FND_MESSAGE.SET_TOKEN('ERR_BUF',sqlerrm);
422 ELSE
423 FND_MESSAGE.SET_TOKEN('ERR_BUF',v_err_buf);
424 END IF;
425 l_errbuf := FND_MESSAGE.GET;
426 FND_MESSAGE.CLEAR;
427 errbuf := l_errbuf;
428 HXT_BATCH_PROCESS.Set_batch_status(l_date_earned, l_batch_id, 'VE');
429 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( l_batch_id,
430 'VE',
431 sqlerrm,
432 l_return);
433 commit;
434 return;
435
436 END IF;
437 IF (l_sum_retcode = 3) then
438 HXT_BATCH_PROCESS.Set_batch_status(l_date_earned, batch_rec.batch_id, 'VE');
439 END IF;
440 ELSE
441 l_final_valid_retcode := 2;
442 FND_MESSAGE.SET_NAME('HXT','HXT_39353_BTCHS_MST_BE_VALDTED');
443 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( batch_rec.batch_id,
444 'VE', -- RETROPAY
445 '',
446 l_return);
447 END IF; -- check status before processing
448 END IF; -- end process selections
449 l_valid_retcode := 0;
450 l_sum_retcode := 0;
451 END LOOP; -- for loop process specific batch
452 --
453 -- Select the next batch in the range if applicable, else exit loop
454 --
455 l_index := list_batch_rec_ids.NEXT(l_index);
456 EXCEPTION
457 WHEN NO_DATA_FOUND THEN
458 l_index := null;
459 -- Bug 10143866
460 -- Added this logging in case of a NO_DATA_FOUND.
461 -- For cts having RDBMS 10g onwards, uncomment the below line to
462 -- backtrace to the exact spot.
463 -- hr_utility.trace(dbms_utility.format_error_backtrace);
464 hr_utility.trace('Batch '||batch_rec.batch_id||' threw a NO_DATA_FOUND');
465 hr_utility.trace('Backtrace this error to Batch validation log ');
466 hr_utility.trace('Skipping to next batch ');
467 WHEN OTHERS THEN
468 HXT_BATCH_PROCESS.Set_batch_status(l_date_earned, batch_rec.batch_id, 'VE');
469 HXT_UTIL.DEBUG('Other exception.'||sqlerrm);
470 l_index := null;
471 END; -- batches
472 END LOOP; -- while more batches exist in the range l_index not equal to null
473 -- Check for error totals to return a status from concurrent manager.
474 -- Normal
475 FND_MESSAGE.SET_NAME('HXT','HXT_39358_COMP_NORMAL');
476 l_errbuf := FND_MESSAGE.GET;
477 FND_MESSAGE.CLEAR;
478 --HXT111 l_errbuf := 'Completed Normal.';
479 l_retcode := 0;
480 -- No batches selected at all
481 IF l_kounter = 0 THEN
482 FND_MESSAGE.SET_NAME('HXT','HXT_39359_NO_BATCHES_SEL');
483 l_errbuf := FND_MESSAGE.GET;
484 FND_MESSAGE.CLEAR;
485 --HXT111 l_errbuf := 'ERROR: No batches selected for processing, check batch status.';
486 l_retcode := 2;
487 END IF;
488 -- v115.11 start
489 IF b_stop = TRUE Then
490 FND_MESSAGE.SET_NAME('HXT','HXT_39360_STR_BTCH_NUM_TOO_LRG');
491 l_errbuf := FND_MESSAGE.GET;
492 FND_MESSAGE.CLEAR;
493 l_retcode := 2;
494 END IF;
495 -- v115.11 end
496 IF l_final_rollback_retcode > 0 THEN
497 IF v_err_buf IS NULL THEN
498 FND_MESSAGE.SET_NAME('HXT','HXT_39361_ERR_DURING_ROLLBACK');
499 ELSE
500 FND_MESSAGE.SET_NAME('HXT','HXT_39450_RETRO_ROLLBACK_ERROR');
501 FND_MESSAGE.SET_TOKEN('ERR_BUF',v_err_buf);
502 END IF;
503 l_errbuf := FND_MESSAGE.GET;
504 FND_MESSAGE.CLEAR;
505 l_retcode := 2;
506 END IF;
507 -- A warning was returned from the validate process
508 IF l_final_valid_retcode = 1 THEN
509 l_retcode := 1;
510 END IF;
511 -- A stop-level error was returned from the validate process
512 IF l_final_valid_retcode = 2 THEN
513 IF v_err_buf IS NULL THEN
514 FND_MESSAGE.SET_NAME('HXT','HXT_39362_BATCH_ERROR');
515 ELSE
516 FND_MESSAGE.SET_NAME('HXT','HXT_39451_RETRO_BATCH_ERROR');
517 FND_MESSAGE.SET_TOKEN('ERR_BUF',v_err_buf);
518 END IF;
519 l_errbuf := FND_MESSAGE.GET;
520 FND_MESSAGE.CLEAR;
521 l_retcode := 2;
522 END IF;
523 -- a system level error occured somewhere during processing
524 IF (l_final_valid_retcode = 3 OR l_final_sum_retcode = 3) THEN
525 IF v_err_buf IS NULL THEN
526 FND_MESSAGE.SET_NAME('HXT','HXT_39363_SYSTEM_ERROR');
527 ELSE
528 FND_MESSAGE.SET_NAME('HXT','HXT_39452_RETRO_SYSTEM_ERROR');
529 FND_MESSAGE.SET_TOKEN('ERR_BUF',v_err_buf);
530 END IF;
531 l_errbuf := FND_MESSAGE.GET;
532 FND_MESSAGE.CLEAR;
533 l_retcode := 2;
534 END IF;
535 retcode := l_retcode;
536 errbuf := l_errbuf;
537 --HXT_UTIL.DEBUG('Retcode:' || TO_CHAR(l_retcode) || ' ' || l_errbuf);
538 -- begin C431
539 -- commented out. using sysdate view, now. RTF
540 --delete from fnd_sessions where session_id = userenv('SESSIONID');
541
542 /********Bug: 4620315 **********/
543 /*** To merge the batch TCs by calling 'merge_batches' procedure ***/
544
545 IF p_merge_flag = '1' and p_process_mode = 'V' THEN
546
547 IF g_debug THEN
548 hr_utility.trace('before calling merge_batches proc');
549 END IF;
550
551 HXT_BATCH_PROCESS.merge_batches (p_merge_batch_name,
552 l_merge_batches,
553 l_del_empty_batches,
554 p_bus_group_id,
555 'R'
556 );
557 END IF;
558
559 /********Bug: 4620315 **********/
560
561 commit;
562 -- end C431
563
564 EXCEPTION
565
566 WHEN OTHERS THEN
567 -- commented out. using sysdate view, now. RTF
568 -- delete from fnd_sessions where session_id = userenv('SESSIONID');
569 -- commit;
570 retcode := 2;
571 IF v_err_buf IS NULL THEN
572 FND_MESSAGE.SET_NAME('HXT','HXT_39363_SYSTEM_ERROR');
573 ELSE
574 FND_MESSAGE.SET_NAME('HXT','HXT_39452_RETRO_SYSTEM_ERROR');
575 FND_MESSAGE.SET_TOKEN('ERR_BUF',v_err_buf);
576 END IF;
577 l_errbuf := FND_MESSAGE.GET;
578 FND_MESSAGE.CLEAR;
579 errbuf := l_errbuf;
580 HXT_BATCH_PROCESS.Set_batch_status(l_date_earned, l_batch_id, 'VE');
581 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( l_batch_id,
582 'VE', -- RETROPAY
583 sqlerrm,
584 l_return);
585 commit;
586 END main_retro;
587 ---------------------------------------------
588 FUNCTION Call_Gen_Error( p_batch_id IN NUMBER
589 ,p_location IN VARCHAR2
590 ,p_error_text IN VARCHAR2
591 ,p_oracle_error_text IN VARCHAR2 default NULL ) RETURN NUMBER IS
592 -- calls error processing procedure --
593 BEGIN
594 HXT_UTIL.Gen_Error(p_batch_id, 0, 0, /*g_time_period_id*/NULL, p_error_text,
595 p_location, p_oracle_error_text
596 ,trunc(sysdate) -- C431
597 ,hr_general.end_of_time
598 ,'ERR');
599
600 RETURN 2;
601 END call_gen_error;
602 ---------------------------------------------------------------------
603 END hxt_retro_process;
[Home] [Help]