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