1 PACKAGE BODY HXT_BATCH_PROCESS AS
2 /* $Header: hxtbat.pkb 120.12.12010000.1 2008/07/25 09:47:57 appldev ship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 g_time_period_id NUMBER := NULL;
5 g_lookup_not_found EXCEPTION;
6 g_error_ins_batch_lines EXCEPTION; --SIR517 PWM 18FEB00
7 FUNCTION Call_Gen_Error(p_batch_id IN NUMBER
8 ,p_location IN VARCHAR2
9 ,p_error_text IN VARCHAR2
10 ,p_oracle_error_text IN VARCHAR2 default NULL )
11 RETURN NUMBER;
12 FUNCTION Get_Transfer_Batch_Status(p_batch_id NUMBER,
13 p_batch_status OUT NOCOPY VARCHAR2)
14 RETURN NUMBER; -- SPR C352 by BC
15 -----------------------------------------------------------------
16
17 /********Bug: 4620315 **********/
18
19 /* Function to set the default value for the profile 'HXT_MERGE_BATCH_TIMECARDS' */
20
21 FUNCTION merge_batches
22 RETURN fnd_profile_option_values.profile_option_value%TYPE
23 AS
24 l_merge_batches fnd_profile_option_values.profile_option_value%TYPE;
25 l_merge_batches_default CONSTANT fnd_profile_option_values.profile_option_value%TYPE := 'N';
26
27 BEGIN
28 l_merge_batches := fnd_profile.VALUE ('HXT_MERGE_BATCH_TIMECARDS');
29
30 IF (l_merge_batches IS NULL)
31 THEN
32 l_merge_batches := l_merge_batches_default;
33 END IF;
34
35 RETURN l_merge_batches;
36 END merge_batches;
37
38 /* Procedure to merge all TCs in the Batch range processed during Validate for BEE (normal and retro)
39 process into new separate consolidated batches for Valid/Warning/error TC's and deleting the
40 empty batches left behind. All the TC's that pass validation get copied into a new BEE Batch
41 containing all valid TCs. All TCs that fail with warning in the validation get copied into a new
42 BEE Batch containing all warning TCs. All TCs that fail with Error in the validation get copied
43 into a new BEE Batch containing all Errored TCs. */
44
45 PROCEDURE merge_batches (p_merge_batch_name VARCHAR2,
46 p_merge_batches MERGE_BATCHES_TYPE_TABLE,
47 p_del_empty_batches DEL_EMPTY_BATCHES_TYPE_TABLE,
48 p_bus_group_id NUMBER,
49 p_mode VARCHAR2
50 )
51 IS
52 l_valid_batch_id PAY_BATCH_HEADERS.BATCH_ID%TYPE;
53 l_error_batch_id PAY_BATCH_HEADERS.BATCH_ID%TYPE;
54 l_warning_batch_id PAY_BATCH_HEADERS.BATCH_ID%TYPE;
55 l_temp_batch_upd_id PAY_BATCH_HEADERS.BATCH_ID%TYPE;
56 l_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
57 l_valid_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
58 l_error_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
59 l_warning_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
60 l_object_version_number PAY_BATCH_HEADERS.OBJECT_VERSION_NUMBER%TYPE;
61 l_ovn HXT_TIMECARDS_F.OBJECT_VERSION_NUMBER%TYPE;
62 l_string1 VARCHAR2(5);
63 l_string2 VARCHAR2(5);
64 l_loop_index1 BINARY_INTEGER;
65 l_loop_index2 BINARY_INTEGER;
66 l_proc VARCHAR2(72);
67 l_assignment_no PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
68 l_parent_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
69 l_batch_type VARCHAR2(10);
70
71 BEGIN
72 g_debug := hr_utility.debug_enabled;
73
74 IF g_debug THEN
75 l_proc := 'hxt_batch_process.merge_batches';
76 hr_utility.set_location('Entering: '||l_proc, 10);
77 END IF;
78
79 l_batch_name := p_merge_batch_name;
80
81 IF p_mode = 'NR' THEN /* Non-Retro */
82 l_string1 := ' C';
83 l_string2 := '_C_';
84 ELSIF p_mode = 'R' THEN /* Retro */
85 l_string1 := ' C R';
86 l_string2 := '_C_R';
87 END IF;
88
89 l_loop_index1 := p_merge_batches.first;
90
91 /*** To loop through all validated TCs and merge them into new separate consolidated batches for
92 Valid/Warning/error TCs based on the TCs 'valid_tc_retcode' value. ***/
93 LOOP
94
95 EXIT WHEN NOT p_merge_batches.exists(l_loop_index1);
96
97 IF g_debug THEN
98 hr_utility.set_location('Inside merge_batches proc loop: '||p_merge_batches(l_loop_index1).batch_id, 20);
99 END IF;
100
101 l_temp_batch_upd_id := null;
102 l_batch_type := null;
103
104 IF p_merge_batches(l_loop_index1).valid_tc_retcode = 0 THEN /* For valid Tcs */
105
106 IF l_valid_batch_id is null THEN
107 l_valid_batch_name := l_batch_name;
108 IF g_debug THEN
109 hr_utility.set_location('Before creating new valid batch header', 30);
110 END IF;
111 pay_batch_element_entry_api.create_batch_header (p_session_date => sysdate,
112 p_batch_name => l_valid_batch_name,
113 p_business_group_id => p_bus_group_id,
114 p_action_if_exists => 'I',
115 p_batch_reference => l_valid_batch_name||l_string1,
116 p_batch_source => 'OTM',
117 p_reject_if_future_changes => 'N',
118 p_batch_id => l_valid_batch_id,
119 p_object_version_number => l_object_version_number
120 ); /* For creating new batch for valid Tcs. */
121 IF g_debug THEN
122 hr_utility.set_location('After creating new valid batch header: '||l_valid_batch_id, 40);
123 END IF;
124 l_valid_batch_name := l_valid_batch_name||l_string2||to_char(l_valid_batch_id);
125 pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
126 p_batch_id => l_valid_batch_id,
127 p_object_version_number => l_object_version_number,
128 p_batch_name => l_valid_batch_name
129 ); /* For updating the batch_name of the newly created batch */
130
131 fnd_file.put_line (fnd_file.log, 'Successful Batch Name: '||l_valid_batch_name);
132
133 IF g_debug THEN
134 hr_utility.set_location('After updating valid batch name: '||l_valid_batch_name, 50);
135 END IF;
136 BEGIN
137 UPDATE hxt_batch_states
138 SET status = 'VV'
139 WHERE batch_id = l_valid_batch_id; /* For updating the status of the newly created batch */
140 END;
141 IF g_debug THEN
142 hr_utility.set_location('After updating valid batch status in hxt_batch_states', 60);
143 END IF;
144 END IF;
145
146 l_temp_batch_upd_id := l_valid_batch_id;
147
148 ELSIF p_merge_batches(l_loop_index1).valid_tc_retcode = 1 THEN /* For warning Tcs */
149
150 IF l_warning_batch_id is null THEN
151 l_warning_batch_name := l_batch_name||'_W';
152 IF g_debug THEN
153 hr_utility.set_location('Before creating new warning batch header', 70);
154 END IF;
155 pay_batch_element_entry_api.create_batch_header (p_session_date => sysdate,
156 p_batch_name => l_warning_batch_name,
157 p_business_group_id => p_bus_group_id,
158 p_action_if_exists => 'I',
159 p_batch_reference => l_warning_batch_name||l_string1,
160 p_batch_source => 'OTM',
161 p_reject_if_future_changes => 'N',
162 p_batch_id => l_warning_batch_id,
163 p_object_version_number => l_object_version_number
164 ); /* For creating new batch for warning Tcs. */
165 IF g_debug THEN
166 hr_utility.set_location('After creating new warning batch header: '||l_warning_batch_id, 80);
167 END IF;
168 l_warning_batch_name := l_warning_batch_name||l_string2||to_char(l_warning_batch_id);
169 pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
170 p_batch_id => l_warning_batch_id,
171 p_object_version_number => l_object_version_number,
172 p_batch_name => l_warning_batch_name
173 ); /* For updating the batch_name of the newly created batch */
174
175 fnd_file.put_line (fnd_file.log, 'Warning Batch Name: '||l_warning_batch_name);
176
177 IF g_debug THEN
178 hr_utility.set_location('After updating warning batch name: '||l_warning_batch_name, 90);
179 END IF;
180 BEGIN
181 UPDATE hxt_batch_states
182 SET status = 'VW'
183 WHERE batch_id = l_warning_batch_id; /* For updating the status of the newly created batch */
184 END;
185 IF g_debug THEN
186 hr_utility.set_location('After updating warning batch status in hxt_batch_states', 100);
187 END IF;
188 END IF;
189
190 l_temp_batch_upd_id := l_warning_batch_id;
191 l_batch_type := 'Warning';
192
193 ELSIF p_merge_batches(l_loop_index1).valid_tc_retcode >= 2 THEN /* For errored Tcs */
194
195 IF l_error_batch_id is null THEN
196 l_error_batch_name := l_batch_name||'_E';
197 IF g_debug THEN
198 hr_utility.set_location('Before creating new error batch header', 110);
199 END IF;
200 pay_batch_element_entry_api.create_batch_header (p_session_date => sysdate,
201 p_batch_name => l_error_batch_name,
202 p_business_group_id => p_bus_group_id,
203 p_action_if_exists => 'I',
204 p_batch_reference => l_error_batch_name||l_string1,
205 p_batch_source => 'OTM',
206 p_reject_if_future_changes => 'N',
207 p_batch_id => l_error_batch_id,
208 p_object_version_number => l_object_version_number
209 ); /* For creating new batch for erroded Tcs. */
210 IF g_debug THEN
211 hr_utility.set_location('after creating new error batch header: '||l_error_batch_id, 120);
212 END IF;
213 l_error_batch_name := l_error_batch_name||l_string2||to_char(l_error_batch_id);
214 pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
215 p_batch_id => l_error_batch_id,
216 p_object_version_number => l_object_version_number,
217 p_batch_name => l_error_batch_name
218 ); /* For updating the batch_name of the newly created batch */
219
220 fnd_file.put_line (fnd_file.log, 'Error Batch Name: '||l_error_batch_name);
221
222 IF g_debug THEN
223 hr_utility.set_location('After updating error batch name: '||l_error_batch_name, 130);
224 END IF;
225 BEGIN
226 UPDATE hxt_batch_states
227 SET status = 'VE'
228 WHERE batch_id = l_error_batch_id; /* For updating the status of the newly created batch */
229 END;
230 IF g_debug THEN
231 hr_utility.set_location('After updating error batch status in hxt_batch_states', 140);
232 END IF;
233 END IF;
234
235 l_temp_batch_upd_id := l_error_batch_id;
236 l_batch_type := 'Error';
237
238 END IF;
239
240 IF g_debug THEN
241 hr_utility.set_location('Before updating TC reference: '||p_merge_batches(l_loop_index1).tc_id||
242 ' Mode: '||p_mode, 150);
243 END IF;
244
245 IF p_mode = 'NR' THEN /* For updating the TC references of Non-Retro Batches to the newly created batch */
246 l_ovn := p_merge_batches(l_loop_index1).object_version_number;
247 HXT_DML.UPDATE_HXT_TIMECARDS (p_rowid => p_merge_batches(l_loop_index1).tc_rowid,
248 p_id => p_merge_batches(l_loop_index1).tc_id,
249 p_for_person_id => p_merge_batches(l_loop_index1).for_person_id,
250 p_time_period_id => p_merge_batches(l_loop_index1).time_period_id,
251 p_auto_gen_flag => p_merge_batches(l_loop_index1).auto_gen_flag,
252 p_batch_id => l_temp_batch_upd_id,
253 p_approv_person_id => p_merge_batches(l_loop_index1).approv_person_id,
254 p_approved_timestamp => p_merge_batches(l_loop_index1).approved_timestamp,
255 p_created_by => p_merge_batches(l_loop_index1).created_by,
256 p_creation_date => p_merge_batches(l_loop_index1).creation_date,
257 p_last_updated_by => p_merge_batches(l_loop_index1).last_updated_by,
258 p_last_update_date => p_merge_batches(l_loop_index1).last_update_date,
259 p_last_update_login => p_merge_batches(l_loop_index1).last_update_login,
260 p_payroll_id => p_merge_batches(l_loop_index1).payroll_id,
261 p_status => p_merge_batches(l_loop_index1).status,
262 p_effective_start_date => p_merge_batches(l_loop_index1).effective_start_date,
263 p_effective_end_date => p_merge_batches(l_loop_index1).effective_end_date,
264 p_object_version_number => l_ovn
265 );
266 ELSIF p_mode = 'R' THEN /* For updating the TC references of Retro Batches to the newly created batch */
267 BEGIN
268 UPDATE hxt_det_hours_worked_f
269 SET retro_batch_id = l_temp_batch_upd_id,
270 object_version_number = object_version_number + 1
271 WHERE retro_batch_id = p_merge_batches(l_loop_index1).batch_id
272 AND tim_id = p_merge_batches(l_loop_index1).tc_id;
273 END;
274
275 IF l_batch_type in ('Warning', 'Error') THEN
276 BEGIN
277 SELECT assignment_number
278 INTO l_assignment_no
279 FROM per_all_assignments_f
280 WHERE person_id = (SELECT for_person_id
281 FROM hxt_timecards_x
282 WHERE id = p_merge_batches(l_loop_index1).tc_id
283 )
284 AND sysdate between effective_start_date and effective_end_date;
285
286 SELECT pbh.batch_name
287 INTO l_parent_batch_name
288 FROM pay_batch_headers pbh
289 WHERE pbh.batch_id = (SELECT tc.batch_id
290 FROM hxt_timecards_x tc
291 WHERE tc.id = p_merge_batches(l_loop_index1).tc_id
292 );
293
294 EXCEPTION
295 WHEN others THEN
296 null;
297 END;
298
299 fnd_file.put_line (fnd_file.log, 'Assignment# = '|| l_assignment_no||
300 ' has an '||l_batch_type||' Timecard in the Batch: '||l_parent_batch_name);
301 END IF;
302
303 END IF;
304
305 IF g_debug THEN
306 hr_utility.set_location('After updating TC reference', 160);
307 END IF;
308
309 l_loop_index1 := p_merge_batches.next(l_loop_index1);
310
311 END LOOP;
312
313 l_loop_index2 := p_del_empty_batches.first;
314
315 LOOP /* To loop through empty batches left behind and delete them */
316
317 EXIT WHEN NOT p_del_empty_batches.exists(l_loop_index2);
318 IF g_debug THEN
319 hr_utility.set_location('Before deleting empty batches: '||p_del_empty_batches(l_loop_index2).batch_id||
320 ' ovn: '||p_del_empty_batches(l_loop_index2).batch_ovn, 170);
321 END IF;
322
323 pay_batch_element_entry_api.delete_batch_header (p_batch_id => p_del_empty_batches(l_loop_index2).batch_id,
324 p_object_version_number => p_del_empty_batches(l_loop_index2).batch_ovn
325 );
326 IF g_debug THEN
327 hr_utility.set_location('After deleting empty batches', 180);
328 END IF;
329
330 BEGIN
331 DELETE FROM hxt_batch_states
332 WHERE batch_id = p_del_empty_batches(l_loop_index2).batch_id;
333 END;
334
335 IF g_debug THEN
336 hr_utility.set_location('After deleting empty batches from hxt_batch_states', 190);
337 END IF;
338
339 l_loop_index2 := p_del_empty_batches.next(l_loop_index2);
340
341 END LOOP;
342
343 IF g_debug THEN
344 hr_utility.set_location('Leaving: '||l_proc, 200);
345 END IF;
346
347 END merge_batches;
348
349 /********Bug: 4620315 **********/
350
351 PROCEDURE Main_Process (
352 errbuf OUT NOCOPY VARCHAR2,
353 retcode OUT NOCOPY NUMBER,
354 p_payroll_id IN NUMBER,
355 p_date_earned IN VARCHAR2, --ORA128 --FAS111
356 p_time_period_id IN NUMBER DEFAULT NULL, -- SPR C166
357 p_from_batch_num IN NUMBER DEFAULT NULL,
358 p_to_batch_num IN NUMBER DEFAULT NULL,
359 p_ref_num IN VARCHAR2 DEFAULT NULL,
360 p_process_mode IN VARCHAR2,
361 p_bus_group_id IN NUMBER,
362 p_merge_flag IN VARCHAR2 DEFAULT '0',
363 p_merge_batch_name IN VARCHAR2 DEFAULT NULL,
364 p_merge_batch_specified IN VARCHAR2 DEFAULT NULL
365 ) IS
366 -- Cursor returns all batch's with timecards for specified payroll,
367 -- time period, batch id, and batch ref that haven't been transferred.
368 l_date_earned DATE := to_date(p_date_earned,'YYYY/MM/DD HH24:MI:SS');
369 CURSOR cur_batch(c_payroll_id NUMBER,
370 c_time_period_id NUMBER,
371 c_batch_num NUMBER,
372 c_reference_num VARCHAR2) IS
373 SELECT pbh.batch_id,
374 hbs.status batch_status, --SIR020
375 pbh.batch_reference ,
376 pbh.object_version_number
377 FROM pay_batch_headers pbh, --GLOBAL
378 hxt_batch_states hbs --SIR020
379 WHERE pbh.business_group_id = p_bus_group_id --GLOBAL
380 AND hbs.batch_id = pbh.batch_id --SIR020
381 AND pbh.batch_id BETWEEN nvl(c_batch_num,0)
382 AND nvl(c_batch_num,999999999999)
383 AND (pbh.batch_reference LIKE nvl(c_reference_num , '%')
384 OR (pbh.batch_reference IS NULL
385 AND c_reference_num IS NULL))
386 AND exists (SELECT 'x'
387 FROM hxt_timecards_x tim --SIR017
388 WHERE tim.batch_id = pbh.batch_id
389 ) --bug 5748118
390 AND pbh.batch_status = 'U' ; --bug 2709527
391 --
392 -- local variables
393 --
394 l_batch_id NUMBER;
395 l_batch_requested NUMBER DEFAULT NULL; -- SPR C166 BY BC
396 l_do_cursor VARCHAR2(1) DEFAULT 'N';
397 l_starting_batch_num NUMBER; -- SPR C166
398 l_ending_batch_num NUMBER; -- SPR C166
399 l_process_mode VARCHAR2(80);
400 l_session_date DATE;
401 l_batch_status VARCHAR2(30);
402 l_pay_retcode NUMBER DEFAULT 0;
403 l_valid_retcode NUMBER DEFAULT 0;
404 l_sum_retcode NUMBER DEFAULT 0;
405 l_main_retcode NUMBER DEFAULT 0;
406 l_final_pay_retcode NUMBER DEFAULT 0;
407 l_final_valid_retcode NUMBER DEFAULT 0;
408 l_final_main_retcode NUMBER DEFAULT 0;
409 l_final_sum_retcode NUMBER DEFAULT 0;
410 l_rollback_retcode NUMBER DEFAULT 0; -- SPR C163
411 l_final_rollback_retcode NUMBER DEFAULT 0; -- SPR C163
412 l_errbuf VARCHAR2(80)DEFAULT NULL;
413 l_retcode NUMBER DEFAULT 0;
414 -----------------------------------------------------------------
415 -- begin SPR C352 by BC
416 -- Because changes were so numerous,I have cut and re-edited the
417 -- entire main function for cleaner audit trailing.
418 -----------------------------------------------------------------
419 l_counter NUMBER DEFAULT 0;
420 l_payroll_id VARCHAR2(30)DEFAULT NULL;
421 l_return NUMBER;
422 l_trans_batch_status NUMBER DEFAULT 0;
423 l_trans_status_code VARCHAR2(10)DEFAULT NULL;
424 b_we_have_batches BOOLEAN DEFAULT TRUE;
425 b_range_is_active BOOLEAN DEFAULT FALSE;
426 b_skip_this_one BOOLEAN DEFAULT FALSE;
427 b_inverted_batch_nums BOOLEAN DEFAULT FALSE;
428
429 /********Bug: 4620315 **********/
430
431 l_cnt BINARY_INTEGER;
432 l_count BINARY_INTEGER;
433 l_loop_index BINARY_INTEGER;
434 l_merge_batches merge_batches_type_table;
435 p_merge_batches merge_batches_type_table;
436 l_del_empty_batches del_empty_batches_type_table;
437
438 /********Bug: 4620315 **********/
439
440 BEGIN
441 g_debug :=hr_utility.debug_enabled;
442 if g_debug then
443 hr_utility.trace(p_bus_group_id);
444 hr_utility.trace(g_time_period_id);
445 hr_utility.trace(l_batch_requested);
446 hr_utility.trace( p_ref_num);
447 end if;
448
449
450 l_date_earned := to_date(to_char(trunc(l_date_earned),'DD/MM/RRRR'),'DD/MM/RRRR');
451 HXT_UTIL.DEBUG('Start process.');-- debug only --HXT115
452 g_time_period_id := p_time_period_id;
453 l_payroll_id := to_char(p_payroll_id);
454 --
455 --Validate and Transfer, Transfer, or Rollback TAMS/O data
456 --
457 l_ending_batch_num := p_to_batch_num;
458 l_starting_batch_num := p_from_batch_num;
459 --
460 -- Determine if the user selected a single batch in either field
461 --
462 IF l_ending_batch_num IS NULL THEN
463 IF l_starting_batch_num IS NOT NULL THEN
464 l_batch_requested := l_starting_batch_num;
465 END IF;
466 ELSE
467 IF l_starting_batch_num IS NULL THEN
468 l_batch_requested := l_ending_batch_num;
469 END IF;
470 END IF;
471 --
472 -- Determine if a range has been selected by the user
473 --
474 IF l_starting_batch_num IS NOT NULL AND l_ending_batch_num IS NOT NULL THEN
475 HXT_UTIL.DEBUG('A range has been selected by the user');-- debug only --HXT115
476 b_range_is_active := TRUE;
477 l_batch_requested := l_starting_batch_num;
478 IF l_starting_batch_num > l_ending_batch_num THEN
479 b_we_have_batches := FALSE;
480 b_inverted_batch_nums := TRUE;
481 END IF;
482 END IF;
483 --
484 -- Loop through all batches in range requested by the user
485 -- (only once through this loop if single batch or no specific batch
486 -- requested)
487 --
488 WHILE b_we_have_batches LOOP
489 BEGIN
490 HXT_UTIL.DEBUG('Beginning we have batches loop');-- debug only --HXT115
491 --
492 -- Select and process all user specified batches for this payroll/reference
493 -- number
494 -- Process batch range specified by the user, else do all available
495 --
496
497
498 FOR batch_rec IN cur_batch(p_payroll_id,
499 g_time_period_id,
500 l_batch_requested,
501 p_ref_num)
502 LOOP
503 HXT_UTIL.DEBUG('Batch number is ' || TO_CHAR(batch_rec.batch_id));
504 l_batch_id := batch_rec.batch_id;
505 -- l_counter := l_counter + 1;
506 --
507 -- rollback all PayMix data per user request
508 --
509 IF p_process_mode = 'D' and batch_rec.batch_status = 'VT' THEN --2709527
510 l_counter := l_counter + 1;
511 HXT_UTIL.DEBUG('Now ROLLING BACK');-- debug only --HXT115
512 -- Delete prior errors for this batch
513 -- Del_Prior_Errors(batch_rec.batch_id);
514 rollback_paymix(batch_rec.batch_id,
515 g_time_period_id, l_rollback_retcode); --SPR C166 BY BC
516 IF l_rollback_retcode > l_final_rollback_retcode then
517 l_final_rollback_retcode := l_rollback_retcode;
518 END IF;
519 IF l_rollback_retcode <> 0 THEN
520 Set_Batch_Status(l_date_earned,
521 batch_rec.batch_id,
522 'VE'); --SIR020
523 END IF;
524 --
525 -- process user requests to validate Timecards
526 --
527 ELSIF p_process_mode = 'V' and batch_rec.batch_status <> 'VT' THEN
528 l_counter := l_counter + 1;
529
530 /********Bug: 4620315 **********/
531 /*** To record the empty batch details ***/
532
533 IF p_merge_flag = '1' THEN
534 IF g_debug THEN
535 hr_utility.trace('Populating del_empty_batches record: '||'batchid: '||batch_rec.batch_id||
536 ' ovn '||batch_rec.object_version_number);
537 END IF;
538 l_cnt := NVL(l_del_empty_batches.LAST,0) +1;
539 l_del_empty_batches(l_cnt).batch_id := batch_rec.batch_id;
540 l_del_empty_batches(l_cnt).batch_ovn := batch_rec.object_version_number;
541 END IF;
542
543 /********Bug: 4620315 **********/
544
545 -- Check for a valid status code
546 -- IF batch_rec.batch_status = 'VT' THEN
547 -- null; -- Don't revalidate batches that have been sent to PayMIX ORA128
548 -- l_final_valid_retcode := 2;
549 -- FND_MESSAGE.SET_NAME('HXT','HXT_39348_TC_VAL_NOT_REPROC'); -- HXT11
550 -- Insert_Pay_Batch_Errors( batch_rec.batch_id,
551 -- 'VE', --SIR020
552 -- '', --HXT11
553 -- l_return);
554 -- ELSE
555 --
556 -- Validate batch, status:0=Normal, 1=Warning,
557 -- 2=Stop Level Data Error, 3=System
558 --
559 HXT_UTIL.DEBUG('Begin timecard validation.');-- debug only --HXT115
560 -- Delete prior errors for this batch
561 -- Del_Prior_Errors(batch_rec.batch_id);
562 HXT_BATCH_VAL.Val_Batch(batch_rec.batch_id,
563 g_time_period_id,
564 l_valid_retcode,
565 p_merge_flag,
566 p_merge_batches);
567
568 /********Bug: 4620315 **********/
569 /*** To record the validated timecards details ***/
570
571 IF p_merge_flag = '1' THEN
572 l_loop_index := p_merge_batches.first;
573 LOOP
574 EXIT WHEN NOT p_merge_batches.exists(l_loop_index);
575 l_count := NVL(l_merge_batches.LAST,0) +1;
576 l_merge_batches(l_count).batch_id := p_merge_batches(l_loop_index).batch_id;
577 l_merge_batches(l_count).tc_id := p_merge_batches(l_loop_index).tc_id;
578 l_merge_batches(l_count).valid_tc_retcode := p_merge_batches(l_loop_index).valid_tc_retcode;
579 l_merge_batches(l_count).tc_rowid := p_merge_batches(l_loop_index).tc_rowid;
580 l_merge_batches(l_count).for_person_id := p_merge_batches(l_loop_index).for_person_id;
581 l_merge_batches(l_count).time_period_id := p_merge_batches(l_loop_index).time_period_id;
582 l_merge_batches(l_count).auto_gen_flag := p_merge_batches(l_loop_index).auto_gen_flag;
583 l_merge_batches(l_count).approv_person_id := p_merge_batches(l_loop_index).approv_person_id;
584 l_merge_batches(l_count).approved_timestamp := p_merge_batches(l_loop_index).approved_timestamp;
585 l_merge_batches(l_count).created_by := p_merge_batches(l_loop_index).created_by;
586 l_merge_batches(l_count).creation_date := p_merge_batches(l_loop_index).creation_date;
587 l_merge_batches(l_count).last_updated_by := p_merge_batches(l_loop_index).last_updated_by;
588 l_merge_batches(l_count).last_update_date := p_merge_batches(l_loop_index).last_update_date;
589 l_merge_batches(l_count).last_update_login := p_merge_batches(l_loop_index).last_update_login;
590 l_merge_batches(l_count).payroll_id := p_merge_batches(l_loop_index).payroll_id;
591 l_merge_batches(l_count).status := p_merge_batches(l_loop_index).status;
592 l_merge_batches(l_count).effective_start_date := p_merge_batches(l_loop_index).effective_start_date;
593 l_merge_batches(l_count).effective_end_date := p_merge_batches(l_loop_index).effective_end_date;
594 l_merge_batches(l_count).object_version_number := p_merge_batches(l_loop_index).object_version_number;
595 l_loop_index := p_merge_batches.next(l_loop_index);
596 END LOOP;
597 END IF;
598
599 /********Bug: 4620315 **********/
600
601 --
602 -- Set error return code from concurrent process
603 --
604 IF l_valid_retcode > l_final_valid_retcode then
605 l_final_valid_retcode := l_valid_retcode;
606 END IF;
607 -- Successful Validation, Set batch to ready Status
608 IF l_valid_retcode = 0 then
609 HXT_UTIL.DEBUG('Successful timecard validation.');--debug onlyHXT115
610 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VV'); --SIR020
611 END IF;
612 -- set status to Warning and lets user know we have a TAMS/O
613 -- User Level Data Error for this batch
614 IF l_valid_retcode = 1 then
615 HXT_UTIL.DEBUG('Timecard validation warnings.');-- debug only HXT115
616 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VW'); --SIR020
617 FND_MESSAGE.SET_NAME('HXT','HXT_39349_CHK_IND_TCARD_ERRS'); -- HXT11
618 Insert_Pay_Batch_Errors( batch_rec.batch_id,
619 'W',
620 '', -- HXT11
621 l_return);
622 END IF;
623 IF l_valid_retcode >= 2 THEN
624 HXT_UTIL.DEBUG('Timecard validation errors.');-- debug only HXT115
625 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VE'); --SIR020
626 FND_MESSAGE.SET_NAME('HXT','HXT_39349_CHK_IND_TCARD_ERRS'); --HXT11
627 Insert_Pay_Batch_Errors( batch_rec.batch_id,
628 'VE', --SIR020
629 '', --HXT11
630 l_return);
631 END IF;
632 --END IF; -- bug 2709527
633 --
634 -- Process transfer to PayMIX
635 --
636 ELSIF p_process_mode = 'T' and batch_rec.batch_status <> 'VT' THEN
637 l_counter := l_counter + 1;
638 -- Don't allow batches in a Hold status to be Transferred to PayMIX
639 IF batch_rec.batch_status = 'H' THEN
640 l_final_valid_retcode := 2;
641 FND_MESSAGE.SET_NAME('HXT','HXT_39350_CANT_TRANS_HLD_PAYMX');--HXT11
642 Insert_Pay_Batch_Errors( batch_rec.batch_id,
643 'VE', --SIR020
644 '', -- HXT11
645 l_return);
646 -- Don't move to PayMIX while Timecard errors exist
647 ELSIF batch_rec.batch_status in ('VE','ET') THEN --SIR020
648 l_final_valid_retcode := 2;
649 FND_MESSAGE.SET_NAME('HXT','HXT_39351_CANT_TRANS_ERR_PAYMX');-- HXT11
650 Insert_Pay_Batch_Errors( batch_rec.batch_id,
651 'VE', --SIR020
652 '', -- HXT11
653 l_return);
654 -- ELSIF (batch_rec.batch_status = 'VT') THEN
655 -- l_final_valid_retcode := 2;
656 -- FND_MESSAGE.SET_NAME('HXT','HXT_39352_BTCHS_PREV_TRANS'); -- HXT11
657 -- Insert_Pay_Batch_Errors( batch_rec.batch_id,
658 -- 'VE', --SIR020
659 -- '', -- HXT11
660 -- l_return);
661 ELSIF batch_rec.batch_status in ('VV','VW') THEN --SIR020
662 -- move to PayMIX
663 HXT_UTIL.DEBUG('Now moving to BEE.');-- debug only --HXT115
664 sum_to_mix(batch_rec.batch_id, g_time_period_id, l_sum_retcode);
665 IF l_sum_retcode > l_final_sum_retcode then
666 l_final_sum_retcode := l_sum_retcode;
667 END IF;
668 IF (l_sum_retcode = 0) then
669 HXT_UTIL.DEBUG('Successful move to BEE.');-- debug only --HXT115
670 -- bug 848062 Fassadi the p_date_earned replaced with l_date_earned.
671 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VT');
672 END IF;
673 IF (l_sum_retcode = 3) then
674 HXT_UTIL.DEBUG('Error moving to BEE.');-- debug only --HXT115
675 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VE');--SIR020
676 END IF;
677 ELSE
678 l_final_valid_retcode := 2;
679 FND_MESSAGE.SET_NAME('HXT','HXT_39353_BTCHS_MST_BE_VALDTED');-- HXT11
680 Insert_Pay_Batch_Errors( batch_rec.batch_id,
681 'VE', --SIR020
682 '', -- HXT11
683 l_return);
684 END IF; -- check status before processing
685 ELSE
686 NULL; --bug2709527
687 END IF; -- end process selections
688 l_valid_retcode := 0;
689 l_sum_retcode := 0;
690 END LOOP; -- for loop process specific batch
691 --
692 -- Select the next batch in the range if applicable, else exit loop
693 --
694 IF b_range_is_active THEN
695 IF l_batch_requested < l_ending_batch_num THEN
696 l_batch_requested := l_batch_requested + 1;
697 b_skip_this_one := FALSE;
698 ELSE
699 b_we_have_batches := FALSE;
700 END IF;
701 ELSE
702 b_we_have_batches := FALSE;
703 END IF;
704 EXCEPTION
705 WHEN NO_DATA_FOUND THEN
706 IF b_range_is_active THEN
707 IF l_batch_requested < l_ending_batch_num THEN
708 l_batch_requested := l_batch_requested + 1;
709 b_skip_this_one := FALSE;
710 ELSE
711 b_we_have_batches := FALSE;
712 END IF;
713 ELSE
714 b_we_have_batches := FALSE;
715 END IF;
716 WHEN g_lookup_not_found THEN --SIR517 PWM 18FEB00
717 raise g_lookup_not_found ; --propogate to the next level
718 WHEN OTHERS THEN
719 Set_Batch_Status(l_date_earned, l_batch_id, 'VE');
720 Insert_Pay_Batch_Errors( l_batch_id,
721 'VE',
722 sqlerrm,
723 l_return);
724 --commit;
725 IF b_range_is_active THEN
726 IF l_batch_requested < l_ending_batch_num THEN
727 l_batch_requested := l_batch_requested + 1;
728 b_skip_this_one := FALSE;
729 ELSE
730 b_we_have_batches := FALSE;
731 END IF;
732 ELSE
733 b_we_have_batches := FALSE;
734 END IF;
735 END; -- batches
736 END LOOP; -- while more batches exist in the range
737 -- end SPR C166 BY BC
738 -- Check for error totals to return a status from concurrent manager.
739 -- Normal
740 FND_MESSAGE.SET_NAME('HXT','HXT_39358_COMP_NORMAL'); -- HXT11
741 l_errbuf := FND_MESSAGE.GET; -- HXT11
742 FND_MESSAGE.CLEAR; -- HXT11
743 l_retcode := 0;
744 -- No batches seleceted at all
745 IF l_counter = 0 THEN
746 FND_MESSAGE.SET_NAME('HXT','HXT_39359_NO_BATCHES_SEL'); -- HXT11
747 l_errbuf := FND_MESSAGE.GET; -- HXT11
748 FND_MESSAGE.CLEAR; -- HXT11
749 l_retcode := 2;
750 END IF;
751 IF b_inverted_batch_nums = TRUE THEN
752 FND_MESSAGE.SET_NAME('HXT','HXT_39360_STR_BTCH_NUM_TOO_LRG'); --HXT11
753 l_errbuf := FND_MESSAGE.GET; -- HXT11
754 FND_MESSAGE.CLEAR; -- HXT11
755 l_retcode := 2;
756 END IF;
757 IF l_final_rollback_retcode > 0 THEN
758 FND_MESSAGE.SET_NAME('HXT','HXT_39361_ERR_DURING_ROLLBACK'); -- HXT11
759 l_errbuf := FND_MESSAGE.GET; -- HXT11
760 FND_MESSAGE.CLEAR; -- HXT11
761 l_retcode := 2;
762 END IF;
763 -- A warning was returned from the validate process
764 IF l_final_valid_retcode = 1 THEN
765 l_retcode := 1;
766 END IF;
767 IF l_final_valid_retcode = 2 THEN
768 HXT_UTIL.DEBUG('l_final_valid_retcode is 2');-- debug only --HXT115
769 FND_MESSAGE.SET_NAME('HXT','HXT_39362_BATCH_ERROR'); -- HXT11
770 l_errbuf := FND_MESSAGE.GET; -- HXT11
771 FND_MESSAGE.CLEAR; -- HXT11
772 l_retcode := 2;
773 END IF;
774 -- a system level error occured somewhere during processing
775 IF (l_final_valid_retcode = 3 OR l_final_sum_retcode = 3) THEN
776 FND_MESSAGE.SET_NAME('HXT','HXT_39363_SYSTEM_ERROR'); -- HXT11
777 l_errbuf := FND_MESSAGE.GET; -- HXT11
778 FND_MESSAGE.CLEAR; -- HXT11
779 l_retcode := 2;
780 END IF;
781 retcode := l_retcode;
782 errbuf := l_errbuf;
783 HXT_UTIL.DEBUG('Retcode:' || TO_CHAR(l_retcode) || ' ' || l_errbuf);-- debug only
784
785 /********Bug: 4620315 **********/
786 /*** To merge the batch TCs by calling 'merge_batches' procedure ***/
787
788 IF p_merge_flag = '1' and p_process_mode = 'V' THEN
789 IF g_debug THEN
790 hr_utility.trace('Before calling merge_batches proc');
791 END IF;
792 merge_batches (p_merge_batch_name,
793 l_merge_batches,
794 l_del_empty_batches,
795 p_bus_group_id,
796 'NR'
797 );
798 END IF;
799
800 /********Bug: 4620315 **********/
801
802 IF retcode = 2 THEN /* Bug: 6064910 */
803 COMMIT;
804 END IF;
805
806 EXCEPTION
807 WHEN g_lookup_not_found THEN --SIR517 PWM 18FEB00 TESTING
808 HXT_UTIL.DEBUG('Oops...g_lookup_not_found in procedure sum_to_mix');
809 l_errbuf := substr(FND_MESSAGE.GET,1,65);
810 errbuf := l_errbuf;
811 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( l_batch_id,
812 'VE', -- RETROPAY
813 '',
814 l_return);
815 retcode := 2;
816 Set_Batch_Status(l_date_earned, l_batch_id, 'VE');
817 --commit;
818 IF retcode = 2 THEN /* Bug: 6064910 */
819 COMMIT;
820 END IF;
821 WHEN OTHERS THEN
822 retcode := 2;
823 FND_MESSAGE.SET_NAME('HXT','HXT_39363_SYSTEM_ERROR');
824 l_errbuf := FND_MESSAGE.GET;
825 FND_MESSAGE.CLEAR;
826 errbuf := l_errbuf;
827 Set_Batch_Status(l_date_earned, l_batch_id, 'VE');
828 Insert_Pay_Batch_Errors( l_batch_id,
829 'VE',
830 sqlerrm,
831 l_return);
832 --commit;
833 IF retcode = 2 THEN /* Bug: 6064910 */
834 COMMIT;
835 END IF;
836 END main_process;
837 --------------------------------------------------------------------------------
838 FUNCTION convert_lookup (p_lookup_code IN VARCHAR2,
839 p_lookup_type IN VARCHAR2,
840 p_date_active IN DATE)
841 RETURN VARCHAR2 IS
842 l_meaning HR_LOOKUPS.MEANING%TYPE;
843 cursor get_meaning_cur(p_code VARCHAR2, p_type VARCHAR2, p_date DATE) is
844 SELECT fcl.meaning
845 FROM hr_lookups fcl
846 WHERE fcl.lookup_code = p_code
847 AND fcl.lookup_type = p_type
848 AND fcl.enabled_flag = 'Y'
849 AND p_date BETWEEN nvl(fcl.start_date_active, p_date)
850 AND nvl(fcl.end_date_active, p_date);
851 BEGIN
852
853 if g_debug then
854 hr_utility.set_location('convert_lookup',10);
855 end if;
856 HXT_UTIL.DEBUG('convert_lookup - code = '||p_lookup_code||'
857 type = '||p_lookup_type||' date = '||fnd_date.date_to_chardate(p_date_active));
858 IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL THEN
859 if g_debug then
860 hr_utility.set_location('convert_lookup',20);
861 end if;
862 OPEN get_meaning_cur(p_lookup_code, p_lookup_type, p_date_active);
863 FETCH get_meaning_cur into l_meaning;
864 if g_debug then
865 hr_utility.trace('l_meaning :'||l_meaning);
866 end if;
867 IF get_meaning_cur%NOTFOUND then
868 if g_debug then
869 hr_utility.set_location('convert_lookup',30);
870 end if;
871 FND_MESSAGE.SET_NAME('HXT','HXT_39483_LOOKUP_NOT_FOUND');
872 FND_MESSAGE.SET_TOKEN('CODE', p_lookup_code);
873 FND_MESSAGE.SET_TOKEN('TYPE', p_lookup_type);
874 RAISE g_lookup_not_found;
875 END IF;
876 ELSE
877 if g_debug then
878 hr_utility.set_location('convert_lookup',40);
879 end if;
880 l_meaning := p_lookup_code;
881 if g_debug then
882 hr_utility.trace('l_meaning :'||l_meaning);
883 end if;
884 END IF;
885 if g_debug then
886 hr_utility.set_location('convert_lookup',50);
887 end if;
888 RETURN l_meaning;
889 END convert_lookup;
890 --
891 -- This function is crated to get the lookup_code for translated
892 -- input-value names
893 --
894 FUNCTION get_lookup_code (p_meaning IN VARCHAR2,
895 p_date_active IN DATE)
896 RETURN VARCHAR2 IS
897 l_lookup_code HR_LOOKUPS.lookup_code%TYPE;
898 cursor get_lookup_code_cur is
899 SELECT lookup_code
900 FROM fnd_lookup_values
901 WHERE meaning = p_meaning
902 AND lookup_type = 'NAME_TRANSLATIONS'
903 AND enabled_flag = 'Y'
904 AND p_date_active BETWEEN nvl(start_date_active, p_date_active)
905 AND nvl(end_date_active, p_date_active);
906 BEGIN
907 g_debug :=hr_utility.debug_enabled;
908 if g_debug then
909 hr_utility.set_location('get_lookup_code',10);
910 end if;
911 HXT_UTIL.DEBUG('get_lookup_ code for meaning = '||p_meaning||'
912 type = '||'NAME_TRANSLATIONS'||' date = '||fnd_date.date_to_chardate(p_date_active));
913 if g_debug then
914 hr_utility.trace('p_meaning :'||p_meaning);
915 end if;
916 IF p_meaning IS NOT NULL THEN
917 if g_debug then
918 hr_utility.set_location('get_lookup_code',20);
919 end if;
920 OPEN get_lookup_code_cur;
921 FETCH get_lookup_code_cur into l_lookup_code;
922 if g_debug then
923 hr_utility.trace('l_lookup_code :'||l_lookup_code);
924 end if;
925 IF get_lookup_code_cur%NOTFOUND then
926 if g_debug then
927 hr_utility.set_location('get_lookup_code',30);
928 hr_utility.trace('get_lookup_code_cur NOT FOUND');
929 end if;
930 -- FND_MESSAGE.SET_NAME('HXT','HXT_39483_LOOKUP_NOT_FOUND');
931 -- FND_MESSAGE.SET_TOKEN('CODE', p_meaning); --SIR517 PWM 18FEB00
932 -- FND_MESSAGE.SET_TOKEN('TYPE', 'NAME_TRANSLATIONS'); --SIR517 PWM 18FEB00
933 -- RAISE g_lookup_not_found;
934 null; -- This is done to fix bug 1761779 -- 17/May/2001
935 END IF;
936 ELSE
937 if g_debug then
938 hr_utility.set_location('get_lookup_code',40);
939 end if;
940 l_lookup_code := p_meaning;
941 if g_debug then
942 hr_utility.trace('p_meaning is null');
943 hr_utility.trace('l_lookup_code:'||l_lookup_code);
944 end if;
945 END IF;
946 if g_debug then
947 hr_utility.trace('l_lookup_code :'||l_lookup_code);
948 hr_utility.set_location('get_lookup_code',50);
949 end if;
950 RETURN l_lookup_code;
951 END get_lookup_code;
952 -- Place OTM data into BEE values per input values
953 -- HXT_UTIL.DEBUG('Putting OTM data into BEE values per input values'); --HXT115
954 -- In order to get the input-value logic work in different legislations we need
955 -- to create (SEED) new lookups for 'Hours' , 'Hourly Rate', 'Rate Multiple',
956 -- and 'Rate Code' with lookup_type of 'NAME_TRANSLATION' and lookup_code
957 -- of 'HOURS', 'HOURLY_RATE', 'RATE_MULTIPLE' and 'RATE_CODE' respectively.
958 -- Then the customers in different countries need to create the above input
959 -- values with the name which is directly translated from the above names for
960 -- OTM elements.
961 -- For example: In French the user must create an input value for 'Hours'
962 -- to be 'Heures' and then to determine which input value 'Heures' is
963 -- associated with we look at the hr_lookups and if we find an entry with
964 -- lookup_type = 'NAME_TRANSLATIONS' and lookup_code = 'HOURS' and
965 -- Meaning to be 'Heures' then we know that this input value would map
966 -- to 'Hours'.
967 -- What need to be noted that it is the customer's responsibilty to create
968 -- input values which are the direct translation of 'Hours','Hourly Rate',
969 -- 'Pay Value' , 'Rate Multiple' and 'Rate Code'
970 --
971 PROCEDURE dtl_to_BEE(p_values_rec IN HXT_BATCH_VALUES_V%ROWTYPE,
972 p_sum_retcode IN OUT NOCOPY NUMBER,
973 p_batch_sequence IN NUMBER) IS
974 --l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
975 l_batch_line_id PAY_BATCH_LINES.BATCH_LINE_ID%TYPE;
976 l_value_meaning hr_lookups.meaning%TYPE;
977 l_return NUMBER;
978 l_line_ovn number;
979 TYPE input_value_record IS RECORD
980 (sequence PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE,
981 name PAY_INPUT_VALUES_F_TL.NAME%TYPE, --FORMS60
982 lookup PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE);
983 TYPE input_values_table IS TABLE OF input_value_record
984 INDEX BY BINARY_INTEGER;
985 hxt_value input_values_table;
986 TYPE pbl_values_table IS TABLE OF PAY_BATCH_LINES.VALUE_1%TYPE
987 INDEX BY BINARY_INTEGER;
988 pbl_value pbl_values_table;
989 CURSOR c_date_input_value(cp_element_type_id NUMBER
990 ,cp_assignment_id NUMBER
991 ,cp_effective_date DATE) IS
992 SELECT distinct PIV.name -- PIV.display_sequence
993 FROM --pay_element_types_f PET
994 pay_input_values_f PIV
995 ,pay_accrual_plans PAP
996 ,pay_net_calculation_rules PNCR
997 WHERE--PET.element_type_id = cp_element_type_id
998 --AND PET.element_type_id = PIV.element_type_id
999 PIV.element_type_id = cp_element_type_id
1000 AND cp_effective_date between PIV.effective_start_date
1001 and PIV.effective_end_date
1002 AND PNCR.date_input_value_id = PIV.input_value_id
1003 AND PNCR.input_value_id <> PAP.pto_input_value_id
1004 AND PNCR.input_value_id <> PAP.co_input_value_id
1005 AND PNCR.accrual_plan_id = PAP.accrual_plan_id
1006 AND PAP.accrual_plan_id IN
1007 (SELECT PAPL.accrual_plan_id
1008 FROM pay_accrual_plans PAPL
1009 ,pay_element_links_f PEL
1010 ,pay_element_entries_f PEE
1011 WHERE PEL.element_type_id = PAPL.accrual_plan_element_type_id
1012 AND cp_effective_date between PEL.effective_start_date
1013 and PEL.effective_end_date
1014 AND PEE.element_link_id = PEL.element_link_id
1015 AND PEE.assignment_id = cp_assignment_id
1016 AND cp_effective_date between PEE.effective_start_date
1017 and PEE.effective_end_date
1018 );
1019 l_piv_name varchar2(30);
1020 lv_pbl_flag varchar2(1) := 'N';
1021 BEGIN
1022
1023 if g_debug then
1024 hr_utility.set_location('dtl_to_BEE',10);
1025 end if;
1026 -- Initialize table
1027 FOR i in 1..15 LOOP
1028 hxt_value(i).sequence := null;
1029 hxt_value(i).name := null;
1030 hxt_value(i).lookup := null;
1031 END LOOP;
1032 -- Get input values details for this element
1033 HXT_UTIL.DEBUG('Getting input values for element '||to_char(p_values_rec.element_type_id)||' date '||fnd_date.date_to_chardate(p_values_rec.date_worked)); --FORMS60 --HXT115
1034 pay_paywsqee_pkg.GET_INPUT_VALUE_DETAILS(p_values_rec.element_type_id,
1035 p_values_rec.date_worked,
1036 hxt_value(1).sequence,
1037 hxt_value(2).sequence,
1038 hxt_value(3).sequence,
1039 hxt_value(4).sequence,
1040 hxt_value(5).sequence,
1041 hxt_value(6).sequence,
1042 hxt_value(7).sequence,
1043 hxt_value(8).sequence,
1044 hxt_value(9).sequence,
1045 hxt_value(10).sequence,
1046 hxt_value(11).sequence,
1047 hxt_value(12).sequence,
1048 hxt_value(13).sequence,
1049 hxt_value(14).sequence,
1050 hxt_value(15).sequence,
1051 hxt_value(1).name,
1052 hxt_value(2).name,
1053 hxt_value(3).name,
1054 hxt_value(4).name,
1055 hxt_value(5).name,
1056 hxt_value(6).name,
1057 hxt_value(7).name,
1058 hxt_value(8).name,
1059 hxt_value(9).name,
1060 hxt_value(10).name,
1061 hxt_value(11).name,
1062 hxt_value(12).name,
1063 hxt_value(13).name,
1064 hxt_value(14).name,
1065 hxt_value(15).name,
1066 hxt_value(1).lookup,
1067 hxt_value(2).lookup,
1068 hxt_value(3).lookup,
1069 hxt_value(4).lookup,
1070 hxt_value(5).lookup,
1071 hxt_value(6).lookup,
1072 hxt_value(7).lookup,
1073 hxt_value(8).lookup,
1074 hxt_value(9).lookup,
1075 hxt_value(10).lookup,
1076 hxt_value(11).lookup,
1077 hxt_value(12).lookup,
1078 hxt_value(13).lookup,
1079 hxt_value(14).lookup,
1080 hxt_value(15).lookup);
1081 if g_debug then
1082 hr_utility.set_location('dtl_to_BEE',20);
1083 end if;
1084 -- Place OTM data into BEE values per input values
1085 HXT_UTIL.DEBUG('Putting OTM data into BEE values per input values'); --HXT115
1086 --
1087 -- In order to get the input-value logic work in different legislations we
1088 -- need to create (SEED) new lookups for 'Hours', 'Hourly Rate',
1089 -- 'Rate Multiple', and 'Rate Code' with lookup_type of 'NAME_TRANSLATION'
1090 -- and lookup_code of 'HOURS', 'HOURLY_RATE', 'RATE_MULTIPLE' and
1091 -- 'RATE_CODE' respectively. Then the customers in different countries
1092 -- need to create the above input values with the name which is directly
1093 -- translated from the above names for OTM elements.
1094 --
1095 -- For example: In French the user must create an input value for 'Hours'
1096 -- to be 'Heures' and then to determine which input value 'Heures' is
1097 -- associated with we look at the hr_lookups and if we find an entry with
1098 -- lookup_type = 'NAME_TRANSLATIONS' and lookup_code = 'HOURS' and Meaning
1099 -- to be 'Heures' then we know that this input vale woul map to 'Hours'.
1100 --
1101 -- What need to be noted that it is the customer's responsibilty to create
1102 -- input values which are the direct translation of 'Hours','Hourly Rate',
1103 -- 'Pay Value' , 'Rate Multiple' and 'Rate Code'
1104 --
1105 FOR i in 1..15 LOOP
1106 --
1107 -- We need to get the lookup_code for the input_value names before
1108 -- processing the further logic on the screen value for the input values.
1109 --
1110 lv_pbl_flag := 'N';
1111 if g_debug then
1112 hr_utility.set_location('dtl_to_BEE',30);
1113 hr_utility.trace('hxt_value_name_'||to_char(i)||' :'|| hxt_value(i).name);
1114 hr_utility.trace('p_values_rec.date_worked:'||p_values_rec.date_worked);
1115 end if;
1116 l_value_meaning := get_lookup_code (hxt_value(i).name,
1117 p_values_rec.date_worked);
1118 if g_debug then
1119 hr_utility.trace('l_value_meaning :'|| l_value_meaning);
1120 end if;
1121 if l_value_meaning = 'HOURS' then
1122 if g_debug then
1123 hr_utility.set_location('dtl_to_BEE',40);
1124 end if;
1125 pbl_value(i) := convert_lookup(p_values_rec.hours,
1126 hxt_value(i).lookup,
1127 p_values_rec.date_worked);
1128 if g_debug then
1129 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1130 end if;
1131 elsif l_value_meaning = 'AMOUNT' then
1132 if g_debug then
1133 hr_utility.set_location('dtl_to_BEE',50);
1134 end if;
1135 pbl_value(i) := convert_lookup(p_values_rec.amount,
1136 hxt_value(i).lookup,
1137 p_values_rec.date_worked);
1138 if g_debug then
1139 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1140 end if;
1141 elsif l_value_meaning = 'RATE_MULTIPLE' then
1142 if g_debug then
1143 hr_utility.set_location('dtl_to_BEE',60);
1144 end if;
1145 pbl_value(i) := convert_lookup(p_values_rec.rate_multiple,
1146 hxt_value(i).lookup,
1147 p_values_rec.date_worked);
1148 if g_debug then
1149 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1150 end if;
1151 elsif l_value_meaning = 'HOURLY_RATE' then
1152 if g_debug then
1153 hr_utility.set_location('dtl_to_BEE',70);
1154 end if;
1155 pbl_value(i) := convert_lookup(p_values_rec.hourly_rate,
1156 hxt_value(i).lookup,
1157 p_values_rec.date_worked);
1158 if g_debug then
1159 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1160 end if;
1161 elsif l_value_meaning = 'RATE' then
1162 if g_debug then
1163 hr_utility.set_location('dtl_to_BEE',80);
1164 end if;
1165 pbl_value(i) := convert_lookup(p_values_rec.hourly_rate,
1166 hxt_value(i).lookup,
1167 p_values_rec.date_worked);
1168 if g_debug then
1169 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1170 end if;
1171 elsif l_value_meaning = 'RATE_CODE' then
1172 if g_debug then
1173 hr_utility.set_location('dtl_to_BEE',90);
1174 end if;
1175 pbl_value(i) := convert_lookup(p_values_rec.rate_code,
1176 hxt_value(i).lookup,
1177 p_values_rec.date_worked);
1178 if g_debug then
1179 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1180 end if;
1181 -- BEGIN US localization
1182 elsif hxt_value(i).name = 'Jurisdiction' then
1183 if g_debug then
1184 hr_utility.set_location('dtl_to_BEE',100);
1185 end if;
1186 if( p_values_rec.state_name is not null or
1187 p_values_rec.county_name is not null or
1188 p_values_rec.city_name is not null or
1189 p_values_rec.zip_code is not null)
1190 then
1191 pbl_value(i):= convert_lookup( pay_ac_utility.get_geocode
1192 (p_values_rec.state_name, p_values_rec.county_name, p_values_rec.city_name,
1193 p_values_rec.zip_code),
1194 hxt_value(i).lookup,
1195 p_values_rec.date_worked);
1196 else
1197 pbl_value(i) := convert_lookup(p_values_rec.location_code,
1198 hxt_value(i).lookup,
1199 p_values_rec.date_worked);
1200 end if;
1201 if g_debug then
1202 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1203 end if;
1204 elsif hxt_value(i).name = 'Deduction Processing' then
1205 if g_debug then
1206 hr_utility.set_location('dtl_to_BEE',110);
1207 end if;
1208 pbl_value(i) := convert_lookup(p_values_rec.fcl_tax_rule_code,
1209 hxt_value(i).lookup,
1210 p_values_rec.date_worked);
1211 if g_debug then
1212 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1213 end if;
1214 elsif hxt_value(i).name = 'Separate Check' then
1215 if g_debug then
1216 hr_utility.set_location('dtl_to_BEE',120);
1217 end if;
1218 pbl_value(i) := convert_lookup(p_values_rec.separate_check_flag,
1219 hxt_value(i).lookup,
1220 p_values_rec.date_worked);
1221 if g_debug then
1222 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1223 end if;
1224 -- END US localization
1225 elsif hxt_value(i).name is not null THEN -- pbl_value(i) := NULL;
1226 if g_debug then
1227 hr_utility.set_location('dtl_to_BEE',130);
1228 end if;
1229 OPEN c_date_input_value(p_values_rec.element_type_id
1230 ,p_values_rec.assignment_id
1231 ,p_values_rec.date_worked);
1232 LOOP
1233 if g_debug then
1234 hr_utility.set_location('dtl_to_BEE',140);
1235 end if;
1236 FETCH c_date_input_value into l_piv_name;
1237 EXIT WHEN c_date_input_value%NOTFOUND;
1238 if g_debug then
1239 hr_utility.trace('l_piv_name :'||l_piv_name);
1240 hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
1241 end if;
1242 IF l_piv_name = hxt_value(i).name THEN
1243 if g_debug then
1244 hr_utility.set_location('dtl_to_BEE',150);
1245 end if;
1246 -- pbl_value(i) := to_char(p_values_rec.date_worked,'DD-MON-YYYY');
1247 pbl_value(i) := fnd_date.date_to_canonical(p_values_rec.date_worked);
1248 lv_pbl_flag := 'Y';
1249 if g_debug then
1250 hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1251 end if;
1252 exit;
1253 END IF;
1254 END LOOP;
1255 CLOSE c_date_input_value;
1256 if lv_pbl_flag = 'N' then
1257 if g_debug then
1258 hr_utility.set_location('dtl_to_BEE',160);
1259 end if;
1260 pbl_value(i) := NULL;
1261 if g_debug then
1262 hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1263 end if;
1264 end if;
1265 if g_debug then
1266 hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
1267 end if;
1268 else
1269 if g_debug then
1270 hr_utility.set_location('dtl_to_BEE',180);
1271 end if;
1272 pbl_value(i) := NULL;
1273 if g_debug then
1274 hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1275 end if;
1276 end if;
1277 if g_debug then
1278 hr_utility.set_location('dtl_to_BEE',190);
1279 end if;
1280 HXT_UTIL.DEBUG('value_'||to_char(i)||' = '||pbl_value(i)); --HXT115
1281 END LOOP;
1282 -- Get Batch Line ID
1283 if g_debug then
1284 hr_utility.set_location('dtl_to_BEE',200);
1285 end if;
1286 -- Get next sequence number
1287 -- l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_values_rec.batch_id);
1288 if g_debug then
1289 hr_utility.set_location('dtl_to_BEE',210);
1290 end if;
1291 HXT_UTIL.DEBUG('batch_sequence = '||to_char(p_batch_sequence)); --HXT115
1292 -- Insert data into BEE table
1293 PAY_BATCH_ELEMENT_ENTRY_API.create_batch_line
1294 (p_session_date => sysdate
1295 ,p_batch_id => p_values_rec.batch_id
1296 ,p_batch_line_status => 'U'
1297 ,p_assignment_id => p_values_rec.assignment_id
1298 ,p_assignment_number => p_values_rec.assignment_number
1299 ,p_batch_sequence => p_batch_sequence
1300 ,p_concatenated_segments => p_values_rec.concatenated_segments
1301 ,p_cost_allocation_keyflex_id => p_values_rec.cost_allocation_keyflex_id
1302 ,p_effective_date => p_values_rec.date_worked
1303 ,p_effective_start_date => p_values_rec.date_worked
1304 ,p_effective_end_date => p_values_rec.date_worked
1305 ,p_element_name => p_values_rec.element_name
1306 ,p_element_type_id => p_values_rec.element_type_id
1307 ,p_entry_type => 'E'
1308 ,p_date_earned => p_values_rec.date_worked
1309 ,p_reason => p_values_rec.reason
1310 ,p_segment1 => p_values_rec.segment1
1311 ,p_segment2 => p_values_rec.segment2
1312 ,p_segment3 => p_values_rec.segment3
1313 ,p_segment4 => p_values_rec.segment4
1314 ,p_segment5 => p_values_rec.segment5
1315 ,p_segment6 => p_values_rec.segment6
1316 ,p_segment7 => p_values_rec.segment7
1317 ,p_segment8 => p_values_rec.segment8
1318 ,p_segment9 => p_values_rec.segment9
1319 ,p_segment10 => p_values_rec.segment10
1320 ,p_segment11 => p_values_rec.segment11
1321 ,p_segment12 => p_values_rec.segment12
1322 ,p_segment13 => p_values_rec.segment13
1323 ,p_segment14 => p_values_rec.segment14
1324 ,p_segment15 => p_values_rec.segment15
1325 ,p_segment16 => p_values_rec.segment16
1326 ,p_segment17 => p_values_rec.segment17
1327 ,p_segment18 => p_values_rec.segment18
1328 ,p_segment19 => p_values_rec.segment19
1329 ,p_segment20 => p_values_rec.segment20
1330 ,p_segment21 => p_values_rec.segment21
1331 ,p_segment22 => p_values_rec.segment22
1332 ,p_segment23 => p_values_rec.segment23
1333 ,p_segment24 => p_values_rec.segment24
1334 ,p_segment25 => p_values_rec.segment25
1335 ,p_segment26 => p_values_rec.segment26
1336 ,p_segment27 => p_values_rec.segment27
1337 ,p_segment28 => p_values_rec.segment28
1338 ,p_segment29 => p_values_rec.segment29
1339 ,p_segment30 => p_values_rec.segment30
1340 ,p_value_1 => pbl_value(1)
1341 ,p_value_2 => pbl_value(2)
1342 ,p_value_3 => pbl_value(3)
1343 ,p_value_4 => pbl_value(4)
1344 ,p_value_5 => pbl_value(5)
1345 ,p_value_6 => pbl_value(6)
1346 ,p_value_7 => pbl_value(7)
1347 ,p_value_8 => pbl_value(8)
1348 ,p_value_9 => pbl_value(9)
1349 ,p_value_10 => pbl_value(10)
1350 ,p_value_11 => pbl_value(11)
1351 ,p_value_12 => pbl_value(12)
1352 ,p_value_13 => pbl_value(13)
1353 ,p_value_14 => pbl_value(14)
1354 ,p_value_15 => pbl_value(15)
1355 ,p_batch_line_id => l_batch_line_id
1356 ,p_object_version_number => l_line_ovn
1357 );
1358 HXT_UTIL.DEBUG('Successful INSERT INTO pay_batch_lines'); --HXT115
1359 -- Update OTM detail row to show BEE line entry id
1360 IF p_values_rec.hrw_rowid IS NOT NULL THEN
1361 UPDATE HXT_DET_HOURS_WORKED_F
1362 set PBL_LINE_ID = l_batch_line_id
1363 WHERE rowid = p_values_rec.hrw_rowid;
1364 END IF;
1365 HXT_UTIL.DEBUG('Successful UPDATE hxt_det_hours_worked_f'); --HXT115
1366 EXCEPTION
1367 WHEN g_lookup_not_found THEN
1368 HXT_UTIL.DEBUG('Oops...g_lookup_not_found'); --HXT115
1369 p_sum_retcode := 3;
1370 RAISE g_lookup_not_found; --SIR517 PWM 18FEB00 Re-raise the exception for the calling procedure
1371 WHEN others THEN
1372 HXT_UTIL.DEBUG(sqlerrm); --HXT115
1373 HXT_UTIL.DEBUG('Oops...others'); --HXT115
1374 FND_MESSAGE.SET_NAME('HXT','HXT_39354_ERR_INS_PAYMX_INFO');
1375 FND_MESSAGE.SET_TOKEN('SQLERR', sqlerrm);
1376 Insert_Pay_Batch_Errors( p_values_rec.batch_id, 'VE', '', l_return);
1377 p_sum_retcode := 3;
1378 RAISE g_error_ins_batch_lines; --SIR517 PWM 18FEB00 Re-raise the exception for the calling procedure
1379 END dtl_to_BEE;
1380 PROCEDURE sum_to_mix (p_batch_id IN NUMBER,
1381 p_time_period_id IN NUMBER,
1382 p_sum_retcode IN OUT NOCOPY NUMBER) IS
1383 PRAGMA AUTONOMOUS_TRANSACTION; --115.37
1384 CURSOR hxt_values_cur IS
1385 SELECT *
1386 FROM hxt_batch_values_v
1387 WHERE batch_id = p_batch_id ;
1388 CURSOR hxt_hours_cur IS
1389 SELECT *
1390 FROM hxt_batch_sum_hours_rollup_v
1391 WHERE batch_id = p_batch_id;
1392 CURSOR hxt_amounts_cur IS
1393 SELECT *
1394 FROM hxt_batch_sum_amounts_v
1395 WHERE batch_id = p_batch_id;
1396 l_values_rec hxt_values_cur%ROWTYPE;
1397 we_have_lines BOOLEAN;
1398 l_return NUMBER;
1399 l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
1400 BEGIN
1401 p_sum_retcode := 0;
1402 --
1403 -- If profile value set to 'Y', sum hours only to send to BEE
1404 --
1405 IF (nvl(fnd_profile.value('HXT_ROLLUP_BATCH_HOURS'),'N') = 'Y') THEN
1406 OPEN hxt_hours_cur;
1407 FETCH hxt_hours_cur into l_values_rec;
1408 IF hxt_hours_cur%FOUND THEN
1409 we_have_lines := TRUE;
1410 ELSE
1411 we_have_lines := FALSE;
1412 END IF;
1413 --
1414 -- Otherwise, do not sum hours, send hours and amounts to BEE
1415 --
1416 ELSE
1417 OPEN hxt_values_cur;
1418 FETCH hxt_values_cur into l_values_rec;
1419 IF hxt_values_cur%FOUND THEN
1420 we_have_lines := TRUE;
1421 ELSE
1422 we_have_lines := FALSE;
1423 END IF;
1424 END IF;
1425 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(l_values_rec.batch_id);
1426 WHILE we_have_lines AND p_sum_retcode = 0 LOOP
1427 dtl_to_BEE(l_values_rec, p_sum_retcode,l_batch_sequence);
1428 --
1429 -- If profile value set to 'Y', then we're sending sum of hours only
1430 --
1431 IF (nvl(fnd_profile.value('HXT_ROLLUP_BATCH_HOURS'),'N') = 'Y') THEN
1432 FETCH hxt_hours_cur into l_values_rec;
1433 IF hxt_hours_cur%FOUND THEN
1434 we_have_lines := TRUE;
1435 l_batch_sequence := l_batch_sequence + 1;
1436 ELSE
1437 we_have_lines := FALSE;
1438 END IF;
1439 --
1440 -- Otherwise, we're sending hours and amounts
1441 --
1442 ELSE
1443 FETCH hxt_values_cur into l_values_rec;
1444 IF hxt_values_cur%FOUND THEN
1445 we_have_lines := TRUE;
1446 l_batch_sequence := l_batch_sequence + 1;
1447 ELSE
1448 we_have_lines := FALSE;
1449 END IF;
1450 END IF;
1451 END LOOP;
1452 --
1453 -- Close appropriate cursor
1454 --
1455 IF (nvl(fnd_profile.value('HXT_ROLLUP_BATCH_HOURS'),'N') = 'Y') THEN
1456 CLOSE hxt_hours_cur;
1457 ELSE
1458 CLOSE hxt_values_cur;
1459 END IF;
1460 --
1461 -- If profile value is set to 'Y', now send amounts only to BEE
1462 --
1463 IF (nvl(fnd_profile.value('HXT_ROLLUP_BATCH_HOURS'),'N') = 'Y') THEN
1464 OPEN hxt_amounts_cur;
1465 FETCH hxt_amounts_cur into l_values_rec;
1466 IF hxt_amounts_cur%FOUND THEN
1467 we_have_lines := TRUE;
1468 ELSE
1469 we_have_lines := FALSE;
1470 END IF;
1471 l_batch_sequence := l_batch_sequence + 1;
1472 WHILE we_have_lines AND p_sum_retcode = 0 LOOP
1473 dtl_to_BEE(l_values_rec, p_sum_retcode,l_batch_sequence);
1474 -- dtl_to_BEE(l_values_rec, p_sum_retcode);
1475 FETCH hxt_amounts_cur into l_values_rec;
1476 IF hxt_amounts_cur%FOUND THEN
1477 we_have_lines := TRUE;
1478 l_batch_sequence := l_batch_sequence + 1;
1479 ELSE
1480 we_have_lines := FALSE;
1481 END IF;
1482 END LOOP;
1483 CLOSE hxt_amounts_cur;
1484 END IF;
1485 HXT_RETRO_VAL.Mark_Rows_Complete(p_batch_id);
1486 COMMIT; --115.37
1487 HXT_UTIL.DEBUG('Successful COMMIT');
1488 EXCEPTION
1489 WHEN g_lookup_not_found THEN --SIR517 PWM 18FEB00
1490 ROLLBACK; --115.37
1491 HXT_UTIL.DEBUG('Oops...g_lookup_not_found in procedure sum_to_mix');
1492 p_sum_retcode := 3;
1493 raise g_lookup_not_found ; --propogate to the calling procedure
1494 WHEN g_error_ins_batch_lines THEN --SIR517 PWM 18FEB00
1495 ROLLBACK; --115.37
1496 HXT_UTIL.DEBUG('Error attempting to insert paymix information');
1497 FND_MESSAGE.SET_NAME('HXT','HXT_39354_ERR_INS_PAYMX_INFO');
1498 FND_MESSAGE.SET_TOKEN('SQLERR',sqlerrm);
1499 Insert_Pay_Batch_Errors( p_batch_id, 'VE', '', l_return);
1500 HXT_UTIL.DEBUG(' back from calling insert_pay_batch_errors');
1501 p_sum_retcode := 3;
1502 raise g_error_ins_batch_lines ;
1503 WHEN others THEN
1504 ROLLBACK; --115.37
1505 HXT_UTIL.DEBUG(sqlerrm);
1506 HXT_UTIL.DEBUG('Oops...others');
1507 FND_MESSAGE.SET_NAME('HXT','HXT_39354_ERR_INS_PAYMX_INFO');
1508 FND_MESSAGE.SET_TOKEN('SQLERR', sqlerrm);
1509 Insert_Pay_Batch_Errors( p_batch_id, 'VE', '', l_return);
1510 p_sum_retcode := 3;
1511 END sum_to_mix;
1512 --------------------------------------------------------------------------------
1513 PROCEDURE Transfer_To_Payroll( p_batch_id IN NUMBER
1514 , p_payroll_id IN VARCHAR2
1515 , p_batch_status IN VARCHAR2
1516 , p_ref_num IN VARCHAR2
1517 , p_process_mode IN VARCHAR2
1518 , p_pay_retcode IN OUT NOCOPY NUMBER) IS
1519 CURSOR cur_sess_date IS
1520 SELECT fnd_date.date_to_chardate(end_date) end_date --SIR149 --FORMS60
1521 FROM per_time_periods
1522 WHERE time_period_id = g_time_period_id;
1523 l_req_id NUMBER;
1524 l_errbuf VARCHAR2(80);
1525 l_retcode NUMBER;
1526 l_session_date VARCHAR2(30);
1527 l_to_batch NUMBER := p_batch_id;
1528 l_num NUMBER;
1529 l_process_mode VARCHAR2(80);
1530 l_return NUMBER; -- SPR C352 by BC
1531 l_message VARCHAR2(256);
1532 g_pipe_session VARCHAR2(30);
1533 get_next_item BOOLEAN default TRUE;
1534 kount NUMBER default 0;
1535 BEGIN
1536 -- Clear retcode
1537 p_pay_retcode := 0;
1538 l_process_mode := p_process_mode;
1539 -- Get session date
1540 --begin SPR C166
1541 IF g_time_period_id IS NULL THEN
1542 l_session_date := fnd_date.date_to_chardate(SYSDATE); --SIR149 --FORMS60
1543 ELSE
1544 OPEN cur_sess_date;
1545 FETCH cur_sess_date INTO l_session_date;
1546 CLOSE cur_sess_date;
1547 END IF;
1548 /*--DEBUG ONLY BEGIN
1549
1550 select 'PIPE' || userenv('sessionid')
1551 into g_pipe_session
1552 from dual;
1553 if g_debug then
1554 hr_utility.set_location('PAY_US_PDT_PROCESS.TRANSFER_TO_PAYROLL', 1);
1555 end if;
1556 --DEBUG ONLY END*/
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 FND_MESSAGE.SET_NAME('HXT','HXT_39357_BATCH_ERR');
1560 FND_MESSAGE.SET_TOKEN('MESSAGE', l_errbuf);
1561 FND_MESSAGE.SET_TOKEN('SQLERR', sqlerrm);
1562 Insert_Pay_Batch_Errors(
1563 p_batch_id,
1564 'VE',
1565 '',
1566 l_return);
1567 p_pay_retcode := 3;
1568 END transfer_to_payroll;
1569 ---------------------------------------------
1570 FUNCTION Call_Gen_Error( p_batch_id IN NUMBER
1571 ,p_location IN VARCHAR2
1572 ,p_error_text IN VARCHAR2
1573 ,p_oracle_error_text IN VARCHAR2 default NULL )
1574 RETURN NUMBER IS
1575 -- calls error processing procedure --
1576 BEGIN
1577 HXT_UTIL.GEN_EXCEPTION
1578 (p_location||'. Batch Id = '||to_char(p_batch_id)
1579 ,p_error_text
1580 ,p_oracle_error_text
1581 ,null);
1582 RETURN 2;
1583 END call_gen_error;
1584 -- begin SPR C352 by BC-----------------------------
1585 PROCEDURE Del_Prior_Errors( p_batch_id NUMBER ) IS
1586 -- delete all prior batch level errors
1587 BEGIN
1588 NULL;
1589 END del_prior_errors;
1590 ---------------------------------------------------------------------
1591 PROCEDURE Set_batch_status(p_date_earned DATE,
1592 p_batch_id NUMBER,
1593 p_status VARCHAR2 )IS
1594 BEGIN
1595 IF (p_status = 'VT' AND p_date_earned IS NOT NULL) THEN
1596 UPDATE hxt_batch_states
1597 SET date_earned = p_date_earned
1598 WHERE batch_id = p_batch_id;
1599 END IF;
1600 UPDATE hxt_batch_states
1601 SET status = p_status
1602 WHERE batch_id = p_batch_id;
1603 --COMMIT;
1604 END Set_batch_status;
1605 ------------------------------------------------------------------
1606 FUNCTION Get_Transfer_Batch_Status(p_batch_id NUMBER,
1607 p_batch_status OUT NOCOPY VARCHAR2 )
1608 RETURN NUMBER IS
1609 l_batch_status VARCHAR2(10);
1610 BEGIN
1611 SELECT status
1612 INTO l_batch_status
1613 FROM hxt_batch_states
1614 WHERE batch_id = p_batch_id;
1615 p_batch_status := l_batch_status;
1616 IF l_batch_status in ('E','VE') THEN
1617 return(2);
1618 ELSIF l_batch_status in ('VW','TW') THEN
1619 return(1);
1620 ELSE
1621 return(0);
1622 END IF;
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 RETURN(3);
1626 END Get_Transfer_Batch_Status;
1627 -----------------------------------------------------------
1628 PROCEDURE rollback_PayMIX(p_batch_id IN NUMBER,
1629 p_time_period_id IN NUMBER,
1630 p_rollback_retcode OUT NOCOPY NUMBER) IS
1631 l_return NUMBER DEFAULT 0;
1632 l_line_id NUMBER DEFAULT NULL;
1633 l_sql_error VARCHAR2(80) DEFAULT NULL;
1634 l_new_batch NUMBER DEFAULT 0;
1635 l_payroll_id NUMBER;
1636 l_business_group_id NUMBER;
1637 l_batch_name VARCHAR2(30);
1638 l_batch_reference VARCHAR2(30);
1639 l_tim_id NUMBER;
1640 l_batch_created boolean;
1641 -- Obtain start and end dates.
1642 -- If period not specified by user or period is unavailable,
1643 -- process all dates in the batch
1644 --
1645 -- Delete PayMIX cursor
1646 -- only batch lines for the time period selected by the user, all if
1647 -- none specified
1648 --
1649 --HXT11 not needed. declared in FOR loop batch_line_rec batch_line_cur%ROWTYPE;
1650 CURSOR batch_line_cur IS
1651 SELECT line.assignment_number, line.effective_date, line.batch_line_id,line.OBJECT_VERSION_NUMBER
1652 FROM pay_batch_lines line
1653 WHERE line.batch_id = p_batch_id;
1654 cursor c_get_batch_lines(p_batch_id number) is
1655 select batch_line_id,object_version_number
1656 from pay_batch_lines
1657 where batch_id = p_batch_id;
1658 cursor c_get_retro_batch_ids(p_batch_id number) is
1659 SELECT distinct(hrw.retro_batch_id) retro_batch_id,pbh.object_version_number
1660 FROM hxt_det_hours_worked_f hrw,
1661 hxt_timecards_f tim,
1662 pay_batch_headers pbh
1663 WHERE hrw.tim_id = tim.id
1664 AND tim.batch_id = pbh.batch_id
1665 AND tim.batch_id = p_batch_id
1666 AND hrw.retro_batch_id IS NOT NULL;
1667 CURSOR c_get_batch_ovn(p_batch_id number) is
1668 select object_version_number
1669 from pay_batch_headers
1670 where batch_id = p_batch_id;
1671 l_batch_ovn pay_batch_headers.object_version_number%type;
1672 l_batch_line_id pay_batch_lines.batch_line_id%type;
1673 l_line_ovn pay_batch_headers.object_version_number%type;
1674 BEGIN
1675 -- if no time period is specified, delete all PayMIX entries
1676 IF p_time_period_id IS NULL THEN
1677 -- Delete PayMIX
1678 --
1679 for l_rec in c_get_batch_lines(p_batch_id) loop
1680 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
1681 (p_batch_line_id => l_rec.batch_line_id
1682 ,p_object_version_number => l_rec.object_version_number
1683 );
1684 end loop;
1685 -- Update Batch status
1686 --
1687 --BEGIN GLOBAL - we no longer need to manipulate PayMIX/BEE batch status;
1688 -- but we still need to manipulate HXT_BATCH_STATES
1689 UPDATE hxt_batch_states
1690 SET status = 'H'
1691 WHERE batch_id = p_batch_id;
1692 -- step 1 - delete retro_batch lines for any timecards in this (regular) batch.
1693 --Then we loop through the cursor and delete the batch lines and batches as follows.
1694 for l_rec in c_get_retro_batch_ids(p_batch_id) loop
1695 -- delete the batch lines
1696 for l_line_rec in c_get_batch_lines(l_rec.retro_batch_id) loop
1697 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
1698 (p_batch_line_id => l_line_rec.batch_line_id
1699 ,p_object_version_number => l_line_rec.object_version_number
1700 );
1701 end loop;
1702 -- delete the batch
1703 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_header
1704 (p_batch_id => l_rec.retro_batch_id
1705 ,p_object_version_number => l_rec.object_version_number
1706 );
1707 end loop;
1708 -- step 2 - delete retro_batch for any timecards in this (regular) batch.
1709 -- we can delete it because retro processing does not re-use
1710 -- batches.
1711 -- step 3 - set pay_status, retro_batch_id for any timecards in this
1712 -- (regular) batch.
1713 -- this will set the whole timecard back to 'P', effectively
1714 -- removing the retro nature of the timecard. when the timecard
1715 -- is resent to PayMIX, the detail rows with the most recent
1716 -- effective dates will be sent. This is what we want, because
1717 -- those rows will include any adjustments made by time entry
1718 -- personnel.
1719 --SIR424 PWM 17JAN00 Clear the pbl_line_id during the rollback
1720 UPDATE hxt_det_hours_worked_f
1721 SET retro_batch_id = NULL,
1722 pay_status = 'P',
1723 pbl_line_id = NULL
1724 WHERE rowid in (
1725 SELECT hrw.rowid
1726 FROM hxt_det_hours_worked_f hrw,
1727 hxt_timecards_f tim
1728 WHERE hrw.tim_id = tim.id
1729 AND tim.batch_id=p_batch_id);
1730 -- step 4 - SET the date_earned (Process Date) back to NULL on the hxt_batch_states table
1731 UPDATE hxt_batch_states
1732 SET date_earned = NULL
1733 WHERE batch_id = p_batch_id;
1734 --COMMIT;
1735 p_rollback_retcode := 0;
1736 ELSE
1737 -- When time period is specified, a split batch could occurr.
1738 -- Create a new batch header id for timecards being rolled back
1739 -- This will allow the user to send any timecards remaining
1740 -- in PayMIX on to Payroll. Rollback timecards will always get
1741 -- a new batch id so they can be processed separately.
1742 l_new_batch := hxt_time_gen.Get_Next_Batch_Id;
1743 -- Update the corresponding timecards with the new batch number
1744 -- Delete batch lines for the time period selected by the user
1745 -- Delete any existing batch line errors
1746 --HXT11FOR batch_line_rec IN batch_line_cur(l_period_start_date, l_period_end_date) LOOP
1747 FOR batch_line_rec IN batch_line_cur LOOP --HXT11
1748 -- HXT_UTIL.DEBUG(batch_line_rec.assignment_number);--debug only HXT115
1749 -- HXT_UTIL.DEBUG(TO_CHAR(batch_line_rec.from_date));--debug only HXT115
1750 -- HXT_UTIL.DEBUG(TO_CHAR(batch_line_rec.to_date));--debug only HXT115
1751 -- Locate the Timecard associated with this particular batch line.
1752 -- we will create a new batch, the first time we enter this loop
1753 if not l_batch_created then
1754 SELECT business_group_id, batch_name, batch_reference
1755 INTO l_business_group_id, l_batch_name, l_batch_reference
1756 FROM pay_batch_headers
1757 WHERE batch_id = p_batch_id;
1758 PAY_BATCH_ELEMENT_ENTRY_API.create_batch_header
1759 (p_session_date => sysdate
1760 ,p_batch_name => l_batch_name
1761 ,p_batch_status => 'U'
1762 ,p_business_group_id => l_business_group_id
1763 ,p_action_if_exists => 'R'
1764 ,p_batch_reference => l_batch_reference
1765 ,p_batch_source => 'OTM'
1766 ,p_purge_after_transfer => 'N'
1767 ,p_reject_if_future_changes => 'N'
1768 ,p_batch_id => l_new_batch
1769 ,p_object_version_number => l_batch_ovn
1770 );
1771 l_batch_created := true;
1772 end if;
1773 --we lock the row corresponding to the batch line id and ovn
1774 pay_btl_shd.lck(p_batch_line_id => batch_line_rec.batch_line_id
1775 ,p_object_version_number => batch_line_rec.object_version_number
1776 );
1777 SELECT DISTINCT(hrw.tim_id)
1778 INTO l_tim_id
1779 FROM hxt_det_hours_worked hrw, per_assignments_f asm --C421
1780 WHERE asm.assignment_number = batch_line_rec.assignment_number
1781 AND hrw.assignment_id = asm.assignment_id;
1782 /* AND hrw.parent_id > 0; HXT111*/
1783 -- Set a new batch number for timecards with elements being
1784 -- deleted from PayMIX
1785 UPDATE hxt_timecards tim
1786 SET tim.batch_id = l_new_batch
1787 WHERE tim.batch_id = p_batch_id
1788 AND tim.id = l_tim_id;
1789 -- Delete actual PayMIX batch lines
1790 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
1791 (p_batch_line_id => batch_line_rec.batch_line_id
1792 ,p_object_version_number => batch_line_rec.object_version_number
1793 );
1794 END LOOP;
1795 -- Add the new batch header line for all rollback timecards
1796 OPEN c_get_batch_lines (p_batch_id);
1797 FETCH c_get_batch_lines INTO l_batch_line_id, l_line_ovn;
1798 IF c_get_batch_lines%NOTFOUND
1799 THEN
1800 -- no batch lines found. So we can delete this batch
1801 OPEN c_get_batch_ovn (p_batch_id);
1802 FETCH c_get_batch_ovn INTO l_batch_ovn;
1803 CLOSE c_get_batch_ovn;
1804 pay_batch_element_entry_api.delete_batch_header (
1805 p_batch_id => p_batch_id,
1806 p_object_version_number => l_batch_ovn
1807 );
1808 END IF;
1809 CLOSE c_get_batch_lines;
1810 END IF; -- timeperiod NULL
1811 --COMMIT;
1812 p_rollback_retcode := 0;
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 HXT_UTIL.DEBUG('Error: ' || sqlerrm); --HXT115
1816 l_sql_error := sqlerrm;
1817 ROLLBACK;
1818 Insert_Pay_Batch_Errors( p_batch_id,
1819 'VE',
1820 l_sql_error,
1821 l_return);
1822 p_rollback_retcode := 3;
1823 END rollback_PayMIX;
1824 -------------------------------------------------------------------
1825 PROCEDURE Insert_Pay_Batch_Errors( p_batch_id IN NUMBER,
1826 p_error_level IN VARCHAR2,
1827 p_exception_details IN VARCHAR2,
1828 p_return_code OUT NOCOPY NUMBER)IS
1829 l_error_msg VARCHAR2(240);
1830 BEGIN
1831 IF p_exception_details IS NULL THEN
1832 l_error_msg := FND_MESSAGE.GET;
1833 FND_MESSAGE.CLEAR;
1834 ELSE
1835 l_ERROR_MSG := p_exception_details;
1836 END IF;
1837 HXT_UTIL.GEN_EXCEPTION
1838 ('Batch processing. Batch Id = '||to_char(p_batch_id)
1839 ,l_error_msg
1840 ,null
1841 ,null);
1842 --COMMIT;
1843 p_return_code := 0;
1844 EXCEPTION
1845 WHEN OTHERS THEN
1846 p_return_code := 1;
1847 END Insert_Pay_Batch_Errors;
1848 ------------------------------------------------------------------
1849 -- end SPR C163, C166 by BC
1850 ------------------------------------------------------------------
1851 PROCEDURE CALL_GEN_ERROR2 ( p_batch_id IN NUMBER
1852 , p_tim_id IN NUMBER
1853 , p_hrw_id IN NUMBER
1854 , p_time_period_id IN NUMBER
1855 , p_error_msg IN VARCHAR2
1856 , p_loc IN VARCHAR2
1857 , p_sql_err IN VARCHAR2
1858 , p_TYPE IN VARCHAR2) IS --HXT11i1
1859 CURSOR tim_dates is
1860 SELECT effective_start_date,
1861 effective_end_date
1862 FROM HXT_TIMECARDS_X
1863 WHERE id = p_tim_id;
1864 l_eff_start DATE;
1865 l_eff_end DATE;
1866 BEGIN
1867 OPEN tim_dates;
1868 FETCH tim_dates into l_eff_start, l_eff_end;
1869 if tim_dates%FOUND then
1870 HXT_UTIL.GEN_ERROR(p_batch_id
1871 , p_tim_id
1872 , p_hrw_id
1873 , p_time_period_id
1874 , p_error_msg
1875 , p_loc
1876 , p_sql_err
1877 , l_eff_start
1878 , l_eff_end
1879 , p_type); --HXT11i1
1880 END IF;
1881 CLOSE tim_dates;
1882 END call_gen_error2;
1883 --begin
1884
1885 END HXT_batch_process;
[Home] [Help]