1 PACKAGE BODY HXT_BATCH_PROCESS AS
2 /* $Header: hxtbat.pkb 120.25.12020000.10 2012/11/16 10:40:09 asrajago 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 -- Bug 13388573
8 g_rollback_error EXCEPTION;
9
10 FUNCTION Call_Gen_Error(p_batch_id IN NUMBER
11 ,p_location IN VARCHAR2
12 ,p_error_text IN VARCHAR2
13 ,p_oracle_error_text IN VARCHAR2 default NULL )
14 RETURN NUMBER;
15 FUNCTION Get_Transfer_Batch_Status(p_batch_id NUMBER,
16 p_batch_status OUT NOCOPY VARCHAR2)
17 RETURN NUMBER; -- SPR C352 by BC
18
19
20
21
22 -- Bug 12993528\
23 -- Created this following pay_paywsqee_pkg.GET_INPUT_VALUE_DETAILS
24 -- We also pick up the OTL Info type DFF sequence for the input values
25 -- and store that.
26
27 procedure GET_INPUT_VALUE_DETAILS (
28 --
29 -- Returns the input value details for the element selected by an LOV
30 --
31 p_element_type_id number,
32 p_effective_date date,
33 p_input_value_id1 in out nocopy number,
34 p_input_value_id2 in out nocopy number,
35 p_input_value_id3 in out nocopy number,
36 p_input_value_id4 in out nocopy number,
37 p_input_value_id5 in out nocopy number,
38 p_input_value_id6 in out nocopy number,
39 p_input_value_id7 in out nocopy number,
40 p_input_value_id8 in out nocopy number,
41 p_input_value_id9 in out nocopy number,
42 p_input_value_id10 in out nocopy number,
43 p_input_value_id11 in out nocopy number,
44 p_input_value_id12 in out nocopy number,
45 p_input_value_id13 in out nocopy number,
46 p_input_value_id14 in out nocopy number,
47 p_input_value_id15 in out nocopy number,
48 p_name1 in out nocopy varchar2,
49 p_name2 in out nocopy varchar2,
50 p_name3 in out nocopy varchar2,
51 p_name4 in out nocopy varchar2,
52 p_name5 in out nocopy varchar2,
53 p_name6 in out nocopy varchar2,
54 p_name7 in out nocopy varchar2,
55 p_name8 in out nocopy varchar2,
56 p_name9 in out nocopy varchar2,
57 p_name10 in out nocopy varchar2,
58 p_name11 in out nocopy varchar2,
59 p_name12 in out nocopy varchar2,
60 p_name13 in out nocopy varchar2,
61 p_name14 in out nocopy varchar2,
62 p_name15 in out nocopy varchar2,
63 p_lookup_type1 in out nocopy varchar2,
64 p_lookup_type2 in out nocopy varchar2,
65 p_lookup_type3 in out nocopy varchar2,
66 p_lookup_type4 in out nocopy varchar2,
67 p_lookup_type5 in out nocopy varchar2,
68 p_lookup_type6 in out nocopy varchar2,
69 p_lookup_type7 in out nocopy varchar2,
70 p_lookup_type8 in out nocopy varchar2,
71 p_lookup_type9 in out nocopy varchar2,
72 p_lookup_type10 in out nocopy varchar2,
73 p_lookup_type11 in out nocopy varchar2,
74 p_lookup_type12 in out nocopy varchar2,
75 p_lookup_type13 in out nocopy varchar2,
76 p_lookup_type14 in out nocopy varchar2,
77 p_lookup_type15 in out nocopy varchar2,
78 p_otl_sequence1 in out nocopy number,
79 p_otl_sequence2 in out nocopy number,
80 p_otl_sequence3 in out nocopy number,
81 p_otl_sequence4 in out nocopy number,
82 p_otl_sequence5 in out nocopy number,
83 p_otl_sequence6 in out nocopy number,
84 p_otl_sequence7 in out nocopy number,
85 p_otl_sequence8 in out nocopy number,
86 p_otl_sequence9 in out nocopy number,
87 p_otl_sequence10 in out nocopy number,
88 p_otl_sequence11 in out nocopy number,
89 p_otl_sequence12 in out nocopy number,
90 p_otl_sequence13 in out nocopy number,
91 p_otl_sequence14 in out nocopy number,
92 p_otl_sequence15 in out nocopy number
93 ) is
94 --
95 -- This picks up the details from PAY as well as OTL's Info type DFF.
96 -- Bug 14273944
97 -- Modified the below cursor to take care of Earnings element as well
98 cursor SET_OF_INPUT_VALUES is
99 --
100 SELECT *
101 FROM (
102 select iv.input_value_id,
103 ivtl.name,
104 iv.lookup_type,
105 iv.uom,
106 TO_NuMBER(REPLACE(fdfcu.application_column_name,'ATTRIBUTE')) otl_sequence,
107 iv.display_sequence
108 from pay_input_values_f iv,
109 pay_input_values_f_tl ivtl,
110 fnd_descr_flex_column_usages fdfcu
111 where p_effective_date between iv.effective_start_date
112 and iv.effective_end_date
113 and iv.element_type_id = p_element_type_id
114 and ivtl.INPUT_VALUE_ID = iv.INPUT_VALUE_ID
115 and ivtl.LANGUAGE = userenv('LANG')
116 and fdfcu.application_id = 809
117 and fdfcu.descriptive_flexfield_name = 'OTC Information Types'
118 and fdfcu.descriptive_flex_context_code = 'ELEMENT - '||to_char(p_element_type_id)
119 and fdfcu.end_user_column_name = iv.name
120 UNION
121 select iv.input_value_id,
122 ivtl.name,
123 iv.lookup_type,
124 iv.uom,
125 NULL otl_sequence,
126 iv.display_sequence
127 from pay_input_values_f iv,
128 pay_input_values_f_tl ivtl
129 --
130 where p_effective_date between iv.effective_start_date
131 and iv.effective_end_date
132 and iv.element_type_id = p_element_type_id
133 and ivtl.INPUT_VALUE_ID = iv.INPUT_VALUE_ID
134 and ivtl.LANGUAGE = userenv('LANG')
135 and mandatory_flag = 'X'
136 )
137 order by display_sequence, name;
138
139 -- Just in case the element is only in OTLR, not in OTL, then we need the old cursor.
140 cursor SET_OF_INPUT_VALUES_old is
141 --
142 select iv.input_value_id,
143 ivtl.name,
144 iv.lookup_type,
145 iv.uom,
146 rank() over (partition by iv.element_type_id order by iv.display_sequence, iv.name) otl_sequence,
147 iv.display_sequence
148 from pay_input_values_f iv,
149 pay_input_values_f_tl ivtl
150 --
151 where p_effective_date between iv.effective_start_date
152 and iv.effective_end_date
153 and iv.element_type_id = p_element_type_id
154 and ivtl.INPUT_VALUE_ID = iv.INPUT_VALUE_ID
155 and ivtl.LANGUAGE = userenv('LANG')
156 order by iv.display_sequence, iv.name;
157
158
159 -- Bug 14827900
160 -- Checks if the element is present in OTL Info Types DFF.
161 CURSOR get_oit_context(p_element_type_id IN NUMBER)
162 IS SELECT 1
163 FROM fnd_descr_flex_contexts
164 WHERE application_id = 809
165 AND descriptive_flexfield_name = 'OTC Information Types'
166 AND descriptive_flex_context_code = 'ELEMENT - '||TO_CHAR(p_element_type_id);
167
168 --
169 input_value_number integer;
170
171 l_seq_table IV_seq_table;
172 fetched_input_value IV_SEQ;
173 l_oit_exists NUMBER;
174
175 begin
176 --
177 -- First, nullify all the entry values to ensure that we overwrite any
178 -- previous fetches
179 --
180 p_input_value_id1 := null;
181 p_input_value_id2 := null;
182 p_input_value_id3 := null;
183 p_input_value_id4 := null;
184 p_input_value_id5 := null;
185 p_input_value_id6 := null;
186 p_input_value_id7 := null;
187 p_input_value_id8 := null;
188 p_input_value_id9 := null;
189 p_input_value_id10 := null;
190 p_input_value_id11 := null;
191 p_input_value_id12 := null;
192 p_input_value_id13 := null;
193 p_input_value_id14 := null;
194 p_input_value_id15 := null;
195
196 --
197 p_name1 := null;
198 p_name2 := null;
199 p_name3 := null;
200 p_name4 := null;
201 p_name5 := null;
202 p_name6 := null;
203 p_name7 := null;
204 p_name8 := null;
205 p_name9 := null;
206 p_name10 := null;
207 p_name11 := null;
208 p_name12 := null;
209 p_name13 := null;
210 p_name14 := null;
211 p_name15 := null;
212 --
213 p_lookup_type1 := null;
214 p_lookup_type2 := null;
215 p_lookup_type3 := null;
216 p_lookup_type4 := null;
217 p_lookup_type5 := null;
218 p_lookup_type6 := null;
219 p_lookup_type7 := null;
220 p_lookup_type8 := null;
221 p_lookup_type9 := null;
222 p_lookup_type10 := null;
223 p_lookup_type11 := null;
224 p_lookup_type12 := null;
225 p_lookup_type13 := null;
226 p_lookup_type14 := null;
227 p_lookup_type15 := null;
228
229 IF NOT g_list_iv_seq_table.EXISTS(TO_CHAR(p_element_type_id))
230 THEN
231 IF g_debug
232 THEN
233 hr_utility.trace(' IV sequence does not exist in cache ');
234 hr_utility.trace(' Checking from OTL and Pay setup ');
235 END IF;
236
237 -- Bug 14827900
238 -- Added this cursor.
239 -- We have two kinds of elements, elements in OTL and OTLR and elements not in OTL and in OTLR.
240 -- This cursor checks if they are in OTL.
241 OPEN get_oit_context(p_element_type_id);
242
243 FETCH get_oit_context INTO l_oit_exists;
244
245 -- If its not in OTL, check only the PAY tables.
246 IF get_oit_context%NOTFOUND
247 THEN
248
249 IF g_debug
250 THEN
251 hr_utility.trace(' IV sequence does not exist in OTL setup ');
252 hr_utility.trace(' Checking only Pay setup ');
253 END IF;
254
255 OPEN set_of_input_values_old;
256 FETCH set_of_input_values_old BULK COLLECT INTO l_seq_table;
257 CLOSE set_of_input_values_old;
258
259 IF l_seq_table.COUNT > 0
260 THEN
261 g_list_iv_seq_table(TO_CHAR(p_element_type_id)) := l_seq_table;
262 END IF;
263
264 ELSE
265
266 -- They do exist in OTL, check the OIT DFF along with Payroll.
267 OPEN SET_OF_INPUT_VALUES;
268 FETCH SET_OF_INPUT_VALUES BULK COLLECT INTO l_seq_table;
269 CLOSE SET_OF_INPUT_VALUES;
270
271 IF l_seq_table.COUNT > 0
272 THEN
273 g_list_iv_seq_table(TO_CHAR(p_element_type_id)) := l_seq_table;
274 END IF;
275 END IF;
276
277 CLOSE get_oit_context;
278
279 END IF;
280
281 l_seq_table := g_list_iv_seq_table(to_char(p_element_type_id));
282
283
284 --for fetched_input_value in set_of_input_values LOOP
285 for i IN l_seq_table.FIRST..l_seq_table.LAST
286 LOOP
287
288 IF g_debug
289 THEN
290 hr_utility.trace(' Sequence details ');
291 hr_utility.trace('OTL Sequence : '||l_seq_table(i).otl_sequence);
292 hr_utility.trace('IV Id : '||l_seq_table(i).input_value_id);
293 hr_utility.trace('Name : '||l_seq_table(i).name);
294 hr_utility.trace('UOM : '||l_seq_table(i).UOM);
295 hr_utility.trace('Lookup type : '||l_seq_table(i).lookup_type);
296 END IF;
297 --
298 fetched_input_value := l_seq_table(i);
299 input_value_number := i;
300
301 -- Now we need to put the input value details into the right parameters
302 -- to pass back to the form; the comments within the action for
303 -- input_value_number = 1 also apply for all the others
304 --
305 if input_value_number = 1 then
306 --
307 -- assign the out parameters
308 --
309 p_input_value_id1 := fetched_input_value.input_value_id;
310 p_name1 := fetched_input_value.name;
311 p_lookup_type1 := fetched_input_value.lookup_type;
312 p_otl_sequence1 := fetched_input_value.otl_sequence;
313 --
314 elsif input_value_number =2 then
315 --
316 p_input_value_id2 := fetched_input_value.input_value_id;
317 p_name2 := fetched_input_value.name;
318 p_lookup_type2 := fetched_input_value.lookup_type;
319 p_otl_sequence2 := fetched_input_value.otl_sequence;
320 --
321 elsif input_value_number =3 then
322 --
323 p_input_value_id3 := fetched_input_value.input_value_id;
324 p_name3 := fetched_input_value.name;
325 p_lookup_type3 := fetched_input_value.lookup_type;
326 p_otl_sequence3 := fetched_input_value.otl_sequence;
327
328 --
329 elsif input_value_number =4 then
330 --
331 p_input_value_id4 := fetched_input_value.input_value_id;
332 p_name4 := fetched_input_value.name;
333 p_lookup_type4 := fetched_input_value.lookup_type;
334 p_otl_sequence4 := fetched_input_value.otl_sequence;
335 --
336 elsif input_value_number =5 then
337 --
338 p_input_value_id5 := fetched_input_value.input_value_id;
339 p_name5 := fetched_input_value.name;
340 p_lookup_type5 := fetched_input_value.lookup_type;
341 p_otl_sequence5 := fetched_input_value.otl_sequence;
342 --
343 elsif input_value_number =6 then
344 --
345 p_input_value_id6 := fetched_input_value.input_value_id;
346 p_name6 := fetched_input_value.name;
347 p_lookup_type6 := fetched_input_value.lookup_type;
348 p_otl_sequence6 := fetched_input_value.otl_sequence;
349 --
350 elsif input_value_number =7 then
351 --
352 p_input_value_id7 := fetched_input_value.input_value_id;
353 p_name7 := fetched_input_value.name;
354 p_lookup_type7 := fetched_input_value.lookup_type;
355 p_otl_sequence7 := fetched_input_value.otl_sequence;
356 --
357 elsif input_value_number =8 then
358 --
359 p_input_value_id8 := fetched_input_value.input_value_id;
360 p_name8 := fetched_input_value.name;
361 p_lookup_type8 := fetched_input_value.lookup_type;
362 p_otl_sequence8 := fetched_input_value.otl_sequence;
363 --
364 elsif input_value_number =9 then
365 --
366 p_input_value_id9 := fetched_input_value.input_value_id;
367 p_name9 := fetched_input_value.name;
368 p_lookup_type9 := fetched_input_value.lookup_type;
369 p_otl_sequence9 := fetched_input_value.otl_sequence;
370 --
371 elsif input_value_number =10 then
372 --
373 p_input_value_id10 := fetched_input_value.input_value_id;
374 p_name10 := fetched_input_value.name;
375 p_lookup_type10 := fetched_input_value.lookup_type;
376 p_otl_sequence10 := fetched_input_value.otl_sequence;
377 --
378 elsif input_value_number =11 then
379 --
380 p_input_value_id11 := fetched_input_value.input_value_id;
381 p_name11 := fetched_input_value.name;
382 p_lookup_type11 := fetched_input_value.lookup_type;
383 p_otl_sequence11 := fetched_input_value.otl_sequence;
384 --
385 elsif input_value_number =12 then
386 --
387 p_input_value_id12 := fetched_input_value.input_value_id;
388 p_name12 := fetched_input_value.name;
389 p_lookup_type12 := fetched_input_value.lookup_type;
390 p_otl_sequence12 := fetched_input_value.otl_sequence;
391 --
392 elsif input_value_number =13 then
393 --
394 p_input_value_id13 := fetched_input_value.input_value_id;
395 p_name13 := fetched_input_value.name;
396 p_lookup_type13 := fetched_input_value.lookup_type;
397 p_otl_sequence13 := fetched_input_value.otl_sequence;
398 --
399 elsif input_value_number =14 then
400 --
401 p_input_value_id14 := fetched_input_value.input_value_id;
402 p_name14 := fetched_input_value.name;
403 p_lookup_type14 := fetched_input_value.lookup_type;
404 p_otl_sequence14 := fetched_input_value.otl_sequence;
405 --
406 elsif input_value_number =15 then
407 --
408 p_input_value_id15 := fetched_input_value.input_value_id;
409 p_name15 := fetched_input_value.name;
410 p_lookup_type15 := fetched_input_value.lookup_type;
411 p_otl_sequence15 := fetched_input_value.otl_sequence;
412
413 end if;
414 --
415 end loop;
416 --
417 end get_input_value_details;
418
419
420
421 -----------------------------------------------------------------
422
423 /********Bug: 4620315 **********/
424
425 /* Function to set the default value for the profile 'HXT_MERGE_BATCH_TIMECARDS' */
426
427 FUNCTION merge_batches
428 RETURN fnd_profile_option_values.profile_option_value%TYPE
429 AS
430 l_merge_batches fnd_profile_option_values.profile_option_value%TYPE;
431 l_merge_batches_default CONSTANT fnd_profile_option_values.profile_option_value%TYPE := 'N';
432
433 BEGIN
434 l_merge_batches := fnd_profile.VALUE ('HXT_MERGE_BATCH_TIMECARDS');
435
436 IF (l_merge_batches IS NULL)
437 THEN
438 l_merge_batches := l_merge_batches_default;
439 END IF;
440
441 RETURN l_merge_batches;
442 END merge_batches;
443
444 /* Procedure to merge all TCs in the Batch range processed during Validate for BEE (normal and retro)
445 process into new separate consolidated batches for Valid/Warning/error TC's and deleting the
446 empty batches left behind. All the TC's that pass validation get copied into a new BEE Batch
447 containing all valid TCs. All TCs that fail with warning in the validation get copied into a new
448 BEE Batch containing all warning TCs. All TCs that fail with Error in the validation get copied
449 into a new BEE Batch containing all Errored TCs. */
450
451 PROCEDURE merge_batches (p_merge_batch_name VARCHAR2,
452 p_merge_batches MERGE_BATCHES_TYPE_TABLE,
453 p_del_empty_batches DEL_EMPTY_BATCHES_TYPE_TABLE,
454 p_bus_group_id NUMBER,
455 p_mode VARCHAR2
456 )
457 IS
458 l_valid_batch_id PAY_BATCH_HEADERS.BATCH_ID%TYPE;
459 l_error_batch_id PAY_BATCH_HEADERS.BATCH_ID%TYPE;
460 l_warning_batch_id PAY_BATCH_HEADERS.BATCH_ID%TYPE;
461 l_temp_batch_upd_id PAY_BATCH_HEADERS.BATCH_ID%TYPE;
462 l_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
463 l_valid_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
464 l_error_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
465 l_warning_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
466 l_object_version_number PAY_BATCH_HEADERS.OBJECT_VERSION_NUMBER%TYPE;
467 l_ovn HXT_TIMECARDS_F.OBJECT_VERSION_NUMBER%TYPE;
468 l_string1 VARCHAR2(5);
469 l_string2 VARCHAR2(5);
470 l_loop_index1 BINARY_INTEGER;
471 l_loop_index2 BINARY_INTEGER;
472 l_proc VARCHAR2(72);
473 l_assignment_no PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
474 l_parent_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
475 l_batch_type VARCHAR2(10);
476
477
478 -- Bug 13954828
479 TYPE NUMTAB IS TABLE OF NUMBER;
480 l_tc_tab NUMTAB;
481
482 BEGIN
483 g_debug := hr_utility.debug_enabled;
484
485 IF g_debug THEN
486 l_proc := 'hxt_batch_process.merge_batches';
487 hr_utility.set_location('Entering: '||l_proc, 10);
488 END IF;
489
490 l_batch_name := p_merge_batch_name;
491
492 IF p_mode = 'NR' THEN /* Non-Retro */
493 l_string1 := ' C';
494 l_string2 := '_C_';
495 ELSIF p_mode = 'R' THEN /* Retro */
496 l_string1 := ' C R';
497 l_string2 := '_C_R';
498 END IF;
499
500 l_loop_index1 := p_merge_batches.first;
501
502 /*** To loop through all validated TCs and merge them into new separate consolidated batches for
503 Valid/Warning/error TCs based on the TCs 'valid_tc_retcode' value. ***/
504 LOOP
505
506 EXIT WHEN NOT p_merge_batches.exists(l_loop_index1);
507
508 IF g_debug THEN
509 hr_utility.set_location('Inside merge_batches proc loop: '||p_merge_batches(l_loop_index1).batch_id, 20);
510 END IF;
511
512 l_temp_batch_upd_id := null;
513 l_batch_type := null;
514
515 IF p_merge_batches(l_loop_index1).valid_tc_retcode = 0 THEN /* For valid Tcs */
516
517 IF l_valid_batch_id is null THEN
518 l_valid_batch_name := l_batch_name;
519 IF g_debug THEN
520 hr_utility.set_location('Before creating new valid batch header', 30);
521 END IF;
522 pay_batch_element_entry_api.create_batch_header (p_session_date => sysdate,
523 p_batch_name => l_valid_batch_name,
524 p_business_group_id => p_bus_group_id,
525 p_action_if_exists => 'I',
526 p_batch_reference => l_valid_batch_name||l_string1,
527 p_batch_source => 'OTM',
528 p_reject_if_future_changes => 'N',
529 p_batch_id => l_valid_batch_id,
530 p_object_version_number => l_object_version_number
531 ); /* For creating new batch for valid Tcs. */
532 IF g_debug THEN
533 hr_utility.set_location('After creating new valid batch header: '||l_valid_batch_id, 40);
534 END IF;
535 l_valid_batch_name := l_valid_batch_name||l_string2||to_char(l_valid_batch_id);
536 pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
537 p_batch_id => l_valid_batch_id,
538 p_object_version_number => l_object_version_number,
539 p_batch_name => l_valid_batch_name
540 ); /* For updating the batch_name of the newly created batch */
541
542 fnd_file.put_line (fnd_file.log, 'Successful Batch Name: '||l_valid_batch_name);
543
544 IF g_debug THEN
545 hr_utility.set_location('After updating valid batch name: '||l_valid_batch_name, 50);
546 END IF;
547 BEGIN
548 UPDATE hxt_batch_states
549 SET status = 'VV'
550 WHERE batch_id = l_valid_batch_id; /* For updating the status of the newly created batch */
551 END;
552 IF g_debug THEN
553 hr_utility.set_location('After updating valid batch status in hxt_batch_states', 60);
554 END IF;
555 END IF;
556
557 l_temp_batch_upd_id := l_valid_batch_id;
558
559 ELSIF p_merge_batches(l_loop_index1).valid_tc_retcode = 1 THEN /* For warning Tcs */
560
561 IF l_warning_batch_id is null THEN
562 l_warning_batch_name := l_batch_name||'_W';
563 IF g_debug THEN
564 hr_utility.set_location('Before creating new warning batch header', 70);
565 END IF;
566 pay_batch_element_entry_api.create_batch_header (p_session_date => sysdate,
567 p_batch_name => l_warning_batch_name,
568 p_business_group_id => p_bus_group_id,
569 p_action_if_exists => 'I',
570 p_batch_reference => l_warning_batch_name||l_string1,
571 p_batch_source => 'OTM',
572 p_reject_if_future_changes => 'N',
573 p_batch_id => l_warning_batch_id,
574 p_object_version_number => l_object_version_number
575 ); /* For creating new batch for warning Tcs. */
576 IF g_debug THEN
577 hr_utility.set_location('After creating new warning batch header: '||l_warning_batch_id, 80);
578 END IF;
579 l_warning_batch_name := l_warning_batch_name||l_string2||to_char(l_warning_batch_id);
580 pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
581 p_batch_id => l_warning_batch_id,
582 p_object_version_number => l_object_version_number,
583 p_batch_name => l_warning_batch_name
584 ); /* For updating the batch_name of the newly created batch */
585
586 fnd_file.put_line (fnd_file.log, 'Warning Batch Name: '||l_warning_batch_name);
587
588 IF g_debug THEN
589 hr_utility.set_location('After updating warning batch name: '||l_warning_batch_name, 90);
590 END IF;
591 BEGIN
592 UPDATE hxt_batch_states
593 SET status = 'VW'
594 WHERE batch_id = l_warning_batch_id; /* For updating the status of the newly created batch */
595 END;
596 IF g_debug THEN
597 hr_utility.set_location('After updating warning batch status in hxt_batch_states', 100);
598 END IF;
599 END IF;
600
601 l_temp_batch_upd_id := l_warning_batch_id;
602 l_batch_type := 'Warning';
603
604 ELSIF p_merge_batches(l_loop_index1).valid_tc_retcode >= 2 THEN /* For errored Tcs */
605
606 IF l_error_batch_id is null THEN
607 l_error_batch_name := l_batch_name||'_E';
608 IF g_debug THEN
609 hr_utility.set_location('Before creating new error batch header', 110);
610 END IF;
611 pay_batch_element_entry_api.create_batch_header (p_session_date => sysdate,
612 p_batch_name => l_error_batch_name,
613 p_business_group_id => p_bus_group_id,
614 p_action_if_exists => 'I',
615 p_batch_reference => l_error_batch_name||l_string1,
616 p_batch_source => 'OTM',
617 p_reject_if_future_changes => 'N',
618 p_batch_id => l_error_batch_id,
619 p_object_version_number => l_object_version_number
620 ); /* For creating new batch for erroded Tcs. */
621 IF g_debug THEN
622 hr_utility.set_location('after creating new error batch header: '||l_error_batch_id, 120);
623 END IF;
624 l_error_batch_name := l_error_batch_name||l_string2||to_char(l_error_batch_id);
625 pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
626 p_batch_id => l_error_batch_id,
627 p_object_version_number => l_object_version_number,
628 p_batch_name => l_error_batch_name
629 ); /* For updating the batch_name of the newly created batch */
630
631 fnd_file.put_line (fnd_file.log, 'Error Batch Name: '||l_error_batch_name);
632
633 IF g_debug THEN
634 hr_utility.set_location('After updating error batch name: '||l_error_batch_name, 130);
635 END IF;
636 BEGIN
637 UPDATE hxt_batch_states
638 SET status = 'VE'
639 WHERE batch_id = l_error_batch_id; /* For updating the status of the newly created batch */
640 END;
641 IF g_debug THEN
642 hr_utility.set_location('After updating error batch status in hxt_batch_states', 140);
643 END IF;
644 END IF;
645
646 l_temp_batch_upd_id := l_error_batch_id;
647 l_batch_type := 'Error';
648
649 END IF;
650
651 IF g_debug THEN
652 hr_utility.set_location('Before updating TC reference: '||p_merge_batches(l_loop_index1).tc_id||
653 ' Mode: '||p_mode, 150);
654 END IF;
655
656 IF p_mode = 'NR' THEN /* For updating the TC references of Non-Retro Batches to the newly created batch */
657 l_ovn := p_merge_batches(l_loop_index1).object_version_number;
658 HXT_DML.UPDATE_HXT_TIMECARDS (p_rowid => p_merge_batches(l_loop_index1).tc_rowid,
659 p_id => p_merge_batches(l_loop_index1).tc_id,
660 p_for_person_id => p_merge_batches(l_loop_index1).for_person_id,
661 p_time_period_id => p_merge_batches(l_loop_index1).time_period_id,
662 p_auto_gen_flag => p_merge_batches(l_loop_index1).auto_gen_flag,
663 p_batch_id => l_temp_batch_upd_id,
664 p_approv_person_id => p_merge_batches(l_loop_index1).approv_person_id,
665 p_approved_timestamp => p_merge_batches(l_loop_index1).approved_timestamp,
666 p_created_by => p_merge_batches(l_loop_index1).created_by,
667 p_creation_date => p_merge_batches(l_loop_index1).creation_date,
668 p_last_updated_by => p_merge_batches(l_loop_index1).last_updated_by,
669 p_last_update_date => p_merge_batches(l_loop_index1).last_update_date,
670 p_last_update_login => p_merge_batches(l_loop_index1).last_update_login,
671 p_payroll_id => p_merge_batches(l_loop_index1).payroll_id,
672 p_status => p_merge_batches(l_loop_index1).status,
673 p_effective_start_date => p_merge_batches(l_loop_index1).effective_start_date,
674 p_effective_end_date => p_merge_batches(l_loop_index1).effective_end_date,
675 p_object_version_number => l_ovn
676 );
677 ELSIF p_mode = 'R' THEN /* For updating the TC references of Retro Batches to the newly created batch */
678 BEGIN
679 UPDATE hxt_det_hours_worked_f
680 SET retro_batch_id = l_temp_batch_upd_id,
681 object_version_number = object_version_number + 1
682 WHERE retro_batch_id = p_merge_batches(l_loop_index1).batch_id
683 AND tim_id = p_merge_batches(l_loop_index1).tc_id;
684
685 -- Bug 13954828
686 -- Updating the RDB tables with the new Merge batch.
687 UPDATE hxc_ret_pay_latest_details
688 SET batch_id = l_temp_batch_upd_id
689 WHERE batch_id = p_merge_batches(l_loop_index1).batch_id
690 RETURNING timecard_id
691 BULK COLLECT INTO l_tc_tab;
692
693 IF l_tc_tab.COUNT > 0
694 THEN
695 hr_utility.trace('List of timecards updated in hxc_ret_pay_latest_details ');
696 FOR i IN l_tc_tab.FIRST..l_tc_tab.LAST
697 LOOP
698 hr_utility.trace(' Timecard id '||l_tc_tab(i));
699 END LOOP;
700 END IF;
701
702 l_tc_tab := NUMTAB();
703
704 UPDATE hxc_ret_pay_details
705 SET batch_id = l_temp_batch_upd_id
706 WHERE batch_id = p_merge_batches(l_loop_index1).batch_id
707 RETURNING timecard_id
708 BULK COLLECT INTO l_tc_tab;
709
710 IF l_tc_tab.COUNT > 0
711 THEN
712 hr_utility.trace('List of timecards updated in hxc_ret_pay_details ');
713 FOR i IN l_tc_tab.FIRST..l_tc_tab.LAST
714 LOOP
715 hr_utility.trace(' Timecard id '||l_tc_tab(i));
716 END LOOP;
717 END IF;
718
719
720 END;
721
722 IF l_batch_type in ('Warning', 'Error') THEN
723 BEGIN
724 SELECT assignment_number
725 INTO l_assignment_no
726 FROM per_all_assignments_f
727 WHERE person_id = (SELECT for_person_id
728 FROM hxt_timecards_x
729 WHERE id = p_merge_batches(l_loop_index1).tc_id
730 )
731 AND sysdate between effective_start_date and effective_end_date;
732
733 SELECT pbh.batch_name
734 INTO l_parent_batch_name
735 FROM pay_batch_headers pbh
736 WHERE pbh.batch_id = (SELECT tc.batch_id
737 FROM hxt_timecards_x tc
738 WHERE tc.id = p_merge_batches(l_loop_index1).tc_id
739 );
740
741 EXCEPTION
742 WHEN others THEN
743 null;
744 -- Bug 10143866
745 hr_utility.trace(dbms_utility.format_error_backtrace);
746
747 END;
748
749 fnd_file.put_line (fnd_file.log, 'Assignment# = '|| l_assignment_no||
750 ' has an '||l_batch_type||' Timecard in the Batch: '||l_parent_batch_name);
751 END IF;
752
753 END IF;
754
755 IF g_debug THEN
756 hr_utility.set_location('After updating TC reference', 160);
757 END IF;
758
759 l_loop_index1 := p_merge_batches.next(l_loop_index1);
760
761 END LOOP;
762
763 l_loop_index2 := p_del_empty_batches.first;
764
765 LOOP /* To loop through empty batches left behind and delete them */
766
767 EXIT WHEN NOT p_del_empty_batches.exists(l_loop_index2);
768 IF g_debug THEN
769 hr_utility.set_location('Before deleting empty batches: '||p_del_empty_batches(l_loop_index2).batch_id||
770 ' ovn: '||p_del_empty_batches(l_loop_index2).batch_ovn, 170);
771 END IF;
772
773 pay_batch_element_entry_api.delete_batch_header (p_batch_id => p_del_empty_batches(l_loop_index2).batch_id,
774 p_object_version_number => p_del_empty_batches(l_loop_index2).batch_ovn
775 );
776 IF g_debug THEN
777 hr_utility.set_location('After deleting empty batches', 180);
778 END IF;
779
780 BEGIN
781 DELETE FROM hxt_batch_states
782 WHERE batch_id = p_del_empty_batches(l_loop_index2).batch_id;
783 END;
784
785 IF g_debug THEN
786 hr_utility.set_location('After deleting empty batches from hxt_batch_states', 190);
787 END IF;
788
789 l_loop_index2 := p_del_empty_batches.next(l_loop_index2);
790
791 END LOOP;
792
793 IF g_debug THEN
794 hr_utility.set_location('Leaving: '||l_proc, 200);
795 END IF;
796
797 END merge_batches;
798
799 /********Bug: 4620315 **********/
800
801 PROCEDURE Main_Process (
802 errbuf OUT NOCOPY VARCHAR2,
803 retcode OUT NOCOPY NUMBER,
804 p_payroll_id IN NUMBER,
805 p_date_earned IN VARCHAR2, --ORA128 --FAS111
806 p_time_period_id IN NUMBER DEFAULT NULL, -- SPR C166
807 p_from_batch_num IN NUMBER DEFAULT NULL,
808 p_to_batch_num IN NUMBER DEFAULT NULL,
809 p_ref_num IN VARCHAR2 DEFAULT NULL,
810 p_process_mode IN VARCHAR2,
811 p_bus_group_id IN NUMBER,
812 p_merge_flag IN VARCHAR2 DEFAULT '0',
813 p_merge_batch_name IN VARCHAR2 DEFAULT NULL,
814 p_merge_batch_specified IN VARCHAR2 DEFAULT NULL
815 ) IS
816 -- Cursor returns all batch's with timecards for specified payroll,
817 -- time period, batch id, and batch ref that haven't been transferred.
818 l_date_earned DATE := to_date(p_date_earned,'YYYY/MM/DD HH24:MI:SS');
819
820 -- Bug 9876599
821 -- Added condition to ignore batches which are Archived.
822
823 CURSOR cur_batch(c_payroll_id NUMBER,
824 c_time_period_id NUMBER,
825 c_batch_num NUMBER,
826 c_reference_num VARCHAR2) IS
827 SELECT pbh.batch_id,
828 hbs.status batch_status, --SIR020
829 pbh.batch_reference ,
830 pbh.object_version_number
831 FROM pay_batch_headers pbh, --GLOBAL
832 hxt_batch_states hbs --SIR020
833 WHERE pbh.business_group_id = p_bus_group_id --GLOBAL
834 AND hbs.batch_id = pbh.batch_id --SIR020
835 AND pbh.batch_id BETWEEN nvl(c_batch_num,0)
836 AND nvl(c_batch_num,999999999999)
837 AND (pbh.batch_reference LIKE nvl(c_reference_num , '%')
838 OR (pbh.batch_reference IS NULL
839 AND c_reference_num IS NULL))
840 AND exists (SELECT 'x'
841 FROM hxt_timecards_x tim --SIR017
842 WHERE tim.batch_id = pbh.batch_id
843 ) --bug 5748118
844 AND NOT EXISTS ( SELECT 1
845 FROM hxc_data_sets hds,
846 hxt_timecards_f tim
847 WHERE tim.batch_id = pbh.batch_id
848 AND hds.data_set_id = tim.data_set_id
849 AND hds.status IN ('OFF_LINE','ARCHIVE_IN_PROGRESS','RESTORE_IN_PROGRESS')
850 )
851 AND pbh.batch_status = 'U' ; --bug 2709527
852 --
853 -- local variables
854 --
855 l_batch_id NUMBER;
856 l_batch_requested NUMBER DEFAULT NULL; -- SPR C166 BY BC
857 l_do_cursor VARCHAR2(1) DEFAULT 'N';
858 l_starting_batch_num NUMBER; -- SPR C166
859 l_ending_batch_num NUMBER; -- SPR C166
860 l_process_mode VARCHAR2(80);
861 l_session_date DATE;
862 l_batch_status VARCHAR2(30);
863 l_pay_retcode NUMBER DEFAULT 0;
864 l_valid_retcode NUMBER DEFAULT 0;
865 l_sum_retcode NUMBER DEFAULT 0;
866 l_main_retcode NUMBER DEFAULT 0;
867 l_final_pay_retcode NUMBER DEFAULT 0;
868 l_final_valid_retcode NUMBER DEFAULT 0;
869 l_final_main_retcode NUMBER DEFAULT 0;
870 l_final_sum_retcode NUMBER DEFAULT 0;
871 l_rollback_retcode NUMBER DEFAULT 0; -- SPR C163
872 l_final_rollback_retcode NUMBER DEFAULT 0; -- SPR C163
873 l_errbuf VARCHAR2(80)DEFAULT NULL;
874 l_retcode NUMBER DEFAULT 0;
875 -----------------------------------------------------------------
876 -- begin SPR C352 by BC
877 -- Because changes were so numerous,I have cut and re-edited the
878 -- entire main function for cleaner audit trailing.
879 -----------------------------------------------------------------
880 l_counter NUMBER DEFAULT 0;
881 l_payroll_id VARCHAR2(30)DEFAULT NULL;
882 l_return NUMBER;
883 l_trans_batch_status NUMBER DEFAULT 0;
884 l_trans_status_code VARCHAR2(10)DEFAULT NULL;
885 b_we_have_batches BOOLEAN DEFAULT TRUE;
886 b_range_is_active BOOLEAN DEFAULT FALSE;
887 b_skip_this_one BOOLEAN DEFAULT FALSE;
888 b_inverted_batch_nums BOOLEAN DEFAULT FALSE;
889
890 /********Bug: 4620315 **********/
891
892 l_cnt BINARY_INTEGER;
893 l_count BINARY_INTEGER;
894 l_loop_index BINARY_INTEGER;
895 l_merge_batches merge_batches_type_table;
896 p_merge_batches merge_batches_type_table;
897 l_del_empty_batches del_empty_batches_type_table;
898
899 /********Bug: 4620315 **********/
900
901 BEGIN
902 g_debug :=hr_utility.debug_enabled;
903 if g_debug then
904 hr_utility.trace(p_bus_group_id);
905 hr_utility.trace(g_time_period_id);
906 hr_utility.trace(l_batch_requested);
907 hr_utility.trace( p_ref_num);
908 end if;
909
910
911 l_date_earned := to_date(to_char(trunc(l_date_earned),'DD/MM/RRRR'),'DD/MM/RRRR');
912 HXT_UTIL.DEBUG('Start process.');-- debug only --HXT115
913 g_time_period_id := p_time_period_id;
914 l_payroll_id := to_char(p_payroll_id);
915 --
916 --Validate and Transfer, Transfer, or Rollback TAMS/O data
917 --
918 l_ending_batch_num := p_to_batch_num;
919 l_starting_batch_num := p_from_batch_num;
920 --
921 -- Determine if the user selected a single batch in either field
922 --
923 IF l_ending_batch_num IS NULL THEN
924 IF l_starting_batch_num IS NOT NULL THEN
925 l_batch_requested := l_starting_batch_num;
926 END IF;
927 ELSE
928 IF l_starting_batch_num IS NULL THEN
929 l_batch_requested := l_ending_batch_num;
930 END IF;
931 END IF;
932 --
933 -- Determine if a range has been selected by the user
934 --
935 IF l_starting_batch_num IS NOT NULL AND l_ending_batch_num IS NOT NULL THEN
936 HXT_UTIL.DEBUG('A range has been selected by the user');-- debug only --HXT115
937 b_range_is_active := TRUE;
938 l_batch_requested := l_starting_batch_num;
939 IF l_starting_batch_num > l_ending_batch_num THEN
940 b_we_have_batches := FALSE;
941 b_inverted_batch_nums := TRUE;
942 END IF;
943 END IF;
944 --
945 -- Loop through all batches in range requested by the user
946 -- (only once through this loop if single batch or no specific batch
947 -- requested)
948 --
949 WHILE b_we_have_batches LOOP
950 BEGIN
951 HXT_UTIL.DEBUG('Beginning we have batches loop');-- debug only --HXT115
952 --
953 -- Select and process all user specified batches for this payroll/reference
954 -- number
955 -- Process batch range specified by the user, else do all available
956 --
957
958
959 FOR batch_rec IN cur_batch(p_payroll_id,
960 g_time_period_id,
961 l_batch_requested,
962 p_ref_num)
963 LOOP
964 HXT_UTIL.DEBUG('Batch number is ' || TO_CHAR(batch_rec.batch_id));
965 l_batch_id := batch_rec.batch_id;
966 -- l_counter := l_counter + 1;
967 --
968 -- rollback all PayMix data per user request
969 --
970 IF p_process_mode = 'D' and batch_rec.batch_status = 'VT' THEN --2709527
971 l_counter := l_counter + 1;
972 HXT_UTIL.DEBUG('Now ROLLING BACK');-- debug only --HXT115
973 -- Delete prior errors for this batch
974 -- Del_Prior_Errors(batch_rec.batch_id);
975 rollback_paymix(batch_rec.batch_id,
976 g_time_period_id, l_rollback_retcode); --SPR C166 BY BC
977 IF l_rollback_retcode > l_final_rollback_retcode then
978 l_final_rollback_retcode := l_rollback_retcode;
979 END IF;
980 IF l_rollback_retcode <> 0 THEN
981 Set_Batch_Status(l_date_earned,
982 batch_rec.batch_id,
983 'VE'); --SIR020
984 END IF;
985 --
986 -- process user requests to validate Timecards
987 --
988 ELSIF p_process_mode = 'V' and batch_rec.batch_status <> 'VT' THEN
989 l_counter := l_counter + 1;
990
991 /********Bug: 4620315 **********/
992 /*** To record the empty batch details ***/
993
994 IF p_merge_flag = '1' THEN
995 IF g_debug THEN
996 hr_utility.trace('Populating del_empty_batches record: '||'batchid: '||batch_rec.batch_id||
997 ' ovn '||batch_rec.object_version_number);
998 END IF;
999 l_cnt := NVL(l_del_empty_batches.LAST,0) +1;
1000 l_del_empty_batches(l_cnt).batch_id := batch_rec.batch_id;
1001 l_del_empty_batches(l_cnt).batch_ovn := batch_rec.object_version_number;
1002 END IF;
1003
1004 /********Bug: 4620315 **********/
1005
1006 -- Check for a valid status code
1007 -- IF batch_rec.batch_status = 'VT' THEN
1008 -- null; -- Don't revalidate batches that have been sent to PayMIX ORA128
1009 -- l_final_valid_retcode := 2;
1010 -- FND_MESSAGE.SET_NAME('HXT','HXT_39348_TC_VAL_NOT_REPROC'); -- HXT11
1011 -- Insert_Pay_Batch_Errors( batch_rec.batch_id,
1012 -- 'VE', --SIR020
1013 -- '', --HXT11
1014 -- l_return);
1015 -- ELSE
1016 --
1017 -- Validate batch, status:0=Normal, 1=Warning,
1018 -- 2=Stop Level Data Error, 3=System
1019 --
1020 HXT_UTIL.DEBUG('Begin timecard validation.');-- debug only --HXT115
1021 -- Delete prior errors for this batch
1022 -- Del_Prior_Errors(batch_rec.batch_id);
1023 HXT_BATCH_VAL.Val_Batch(batch_rec.batch_id,
1024 g_time_period_id,
1025 l_valid_retcode,
1026 p_merge_flag,
1027 p_merge_batches);
1028
1029 /********Bug: 4620315 **********/
1030 /*** To record the validated timecards details ***/
1031
1032 IF p_merge_flag = '1' THEN
1033 l_loop_index := p_merge_batches.first;
1034 LOOP
1035 EXIT WHEN NOT p_merge_batches.exists(l_loop_index);
1036 l_count := NVL(l_merge_batches.LAST,0) +1;
1037 l_merge_batches(l_count).batch_id := p_merge_batches(l_loop_index).batch_id;
1038 l_merge_batches(l_count).tc_id := p_merge_batches(l_loop_index).tc_id;
1039 l_merge_batches(l_count).valid_tc_retcode := p_merge_batches(l_loop_index).valid_tc_retcode;
1040 l_merge_batches(l_count).tc_rowid := p_merge_batches(l_loop_index).tc_rowid;
1041 l_merge_batches(l_count).for_person_id := p_merge_batches(l_loop_index).for_person_id;
1042 l_merge_batches(l_count).time_period_id := p_merge_batches(l_loop_index).time_period_id;
1043 l_merge_batches(l_count).auto_gen_flag := p_merge_batches(l_loop_index).auto_gen_flag;
1044 l_merge_batches(l_count).approv_person_id := p_merge_batches(l_loop_index).approv_person_id;
1045 l_merge_batches(l_count).approved_timestamp := p_merge_batches(l_loop_index).approved_timestamp;
1046 l_merge_batches(l_count).created_by := p_merge_batches(l_loop_index).created_by;
1047 l_merge_batches(l_count).creation_date := p_merge_batches(l_loop_index).creation_date;
1048 l_merge_batches(l_count).last_updated_by := p_merge_batches(l_loop_index).last_updated_by;
1049 l_merge_batches(l_count).last_update_date := p_merge_batches(l_loop_index).last_update_date;
1050 l_merge_batches(l_count).last_update_login := p_merge_batches(l_loop_index).last_update_login;
1051 l_merge_batches(l_count).payroll_id := p_merge_batches(l_loop_index).payroll_id;
1052 l_merge_batches(l_count).status := p_merge_batches(l_loop_index).status;
1053 l_merge_batches(l_count).effective_start_date := p_merge_batches(l_loop_index).effective_start_date;
1054 l_merge_batches(l_count).effective_end_date := p_merge_batches(l_loop_index).effective_end_date;
1055 l_merge_batches(l_count).object_version_number := p_merge_batches(l_loop_index).object_version_number;
1056 l_loop_index := p_merge_batches.next(l_loop_index);
1057 END LOOP;
1058 END IF;
1059
1060 /********Bug: 4620315 **********/
1061
1062 --
1063 -- Set error return code from concurrent process
1064 --
1065 IF l_valid_retcode > l_final_valid_retcode then
1066 l_final_valid_retcode := l_valid_retcode;
1067 END IF;
1068 -- Successful Validation, Set batch to ready Status
1069 IF l_valid_retcode = 0 then
1070 HXT_UTIL.DEBUG('Successful timecard validation.');--debug onlyHXT115
1071 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VV'); --SIR020
1072 END IF;
1073 -- set status to Warning and lets user know we have a TAMS/O
1074 -- User Level Data Error for this batch
1075 IF l_valid_retcode = 1 then
1076 HXT_UTIL.DEBUG('Timecard validation warnings.');-- debug only HXT115
1077 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VW'); --SIR020
1078 FND_MESSAGE.SET_NAME('HXT','HXT_39349_CHK_IND_TCARD_ERRS'); -- HXT11
1079 Insert_Pay_Batch_Errors( batch_rec.batch_id,
1080 'W',
1081 '', -- HXT11
1082 l_return);
1083 END IF;
1084 IF l_valid_retcode >= 2 THEN
1085 HXT_UTIL.DEBUG('Timecard validation errors.');-- debug only HXT115
1086 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VE'); --SIR020
1087 FND_MESSAGE.SET_NAME('HXT','HXT_39349_CHK_IND_TCARD_ERRS'); --HXT11
1088 Insert_Pay_Batch_Errors( batch_rec.batch_id,
1089 'VE', --SIR020
1090 '', --HXT11
1091 l_return);
1092 END IF;
1093 --END IF; -- bug 2709527
1094 --
1095 -- Process transfer to PayMIX
1096 --
1097 ELSIF p_process_mode = 'T' and batch_rec.batch_status <> 'VT' THEN
1098 l_counter := l_counter + 1;
1099 -- Don't allow batches in a Hold status to be Transferred to PayMIX
1100 IF batch_rec.batch_status = 'H' THEN
1101 l_final_valid_retcode := 2;
1102 FND_MESSAGE.SET_NAME('HXT','HXT_39350_CANT_TRANS_HLD_PAYMX');--HXT11
1103 Insert_Pay_Batch_Errors( batch_rec.batch_id,
1104 'VE', --SIR020
1105 '', -- HXT11
1106 l_return);
1107 -- Don't move to PayMIX while Timecard errors exist
1108 ELSIF batch_rec.batch_status in ('VE','ET') THEN --SIR020
1109 l_final_valid_retcode := 2;
1110 FND_MESSAGE.SET_NAME('HXT','HXT_39351_CANT_TRANS_ERR_PAYMX');-- HXT11
1111 Insert_Pay_Batch_Errors( batch_rec.batch_id,
1112 'VE', --SIR020
1113 '', -- HXT11
1114 l_return);
1115 -- ELSIF (batch_rec.batch_status = 'VT') THEN
1116 -- l_final_valid_retcode := 2;
1117 -- FND_MESSAGE.SET_NAME('HXT','HXT_39352_BTCHS_PREV_TRANS'); -- HXT11
1118 -- Insert_Pay_Batch_Errors( batch_rec.batch_id,
1119 -- 'VE', --SIR020
1120 -- '', -- HXT11
1121 -- l_return);
1122 ELSIF batch_rec.batch_status in ('VV','VW') THEN --SIR020
1123 -- move to PayMIX
1124 HXT_UTIL.DEBUG('Now moving to BEE.');-- debug only --HXT115
1125 sum_to_mix(batch_rec.batch_id, g_time_period_id, l_sum_retcode);
1126 IF l_sum_retcode > l_final_sum_retcode then
1127 l_final_sum_retcode := l_sum_retcode;
1128 END IF;
1129 IF (l_sum_retcode = 0) then
1130 HXT_UTIL.DEBUG('Successful move to BEE.');-- debug only --HXT115
1131 -- bug 848062 Fassadi the p_date_earned replaced with l_date_earned.
1132 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VT');
1133 END IF;
1134 IF (l_sum_retcode = 3) then
1135 HXT_UTIL.DEBUG('Error moving to BEE.');-- debug only --HXT115
1136 Set_Batch_Status(l_date_earned, batch_rec.batch_id, 'VE');--SIR020
1137 END IF;
1138 ELSE
1139 l_final_valid_retcode := 2;
1140 FND_MESSAGE.SET_NAME('HXT','HXT_39353_BTCHS_MST_BE_VALDTED');-- HXT11
1141 Insert_Pay_Batch_Errors( batch_rec.batch_id,
1142 'VE', --SIR020
1143 '', -- HXT11
1144 l_return);
1145 END IF; -- check status before processing
1146 ELSE
1147 NULL; --bug2709527
1148 END IF; -- end process selections
1149 l_valid_retcode := 0;
1150 l_sum_retcode := 0;
1151 END LOOP; -- for loop process specific batch
1152 --
1153 -- Select the next batch in the range if applicable, else exit loop
1154 --
1155 IF b_range_is_active THEN
1156 IF l_batch_requested < l_ending_batch_num THEN
1157 l_batch_requested := l_batch_requested + 1;
1158 b_skip_this_one := FALSE;
1159 ELSE
1160 b_we_have_batches := FALSE;
1161 END IF;
1162 ELSE
1163 b_we_have_batches := FALSE;
1164 END IF;
1165 EXCEPTION
1166 WHEN NO_DATA_FOUND THEN
1167 IF b_range_is_active THEN
1168 IF l_batch_requested < l_ending_batch_num THEN
1169 l_batch_requested := l_batch_requested + 1;
1170 b_skip_this_one := FALSE;
1171 ELSE
1172 b_we_have_batches := FALSE;
1173 END IF;
1174 ELSE
1175 b_we_have_batches := FALSE;
1176 END IF;
1177 -- Bug 10143866
1178 -- Added this logging in case of a NO_DATA_FOUND.
1179 hr_utility.trace(dbms_utility.format_error_backtrace);
1180 hr_utility.trace('Batch '||l_batch_id||' threw a NO_DATA_FOUND');
1181 hr_utility.trace('Backtrace this error to Batch validation log ');
1182 hr_utility.trace('Skipping to next batch ');
1183 WHEN g_lookup_not_found THEN --SIR517 PWM 18FEB00
1184 raise g_lookup_not_found ; --propogate to the next level
1185 WHEN g_rollback_error THEN
1186 -- Bug 10143866
1187 hr_utility.trace(dbms_utility.format_error_backtrace);
1188 FND_FILE.put_line(fnd_file.log,'Rollback errored out ');
1189 -- Set_Batch_Status(l_date_earned, l_batch_id, 'VE');
1190 --commit;
1191 raise g_rollback_error;
1192 WHEN OTHERS THEN
1193 -- Bug 10143866
1194 hr_utility.trace(dbms_utility.format_error_backtrace);
1195 Set_Batch_Status(l_date_earned, l_batch_id, 'VE');
1196 Insert_Pay_Batch_Errors( l_batch_id,
1197 'VE',
1198 sqlerrm,
1199 l_return);
1200 --commit;
1201 IF b_range_is_active THEN
1202 IF l_batch_requested < l_ending_batch_num THEN
1203 l_batch_requested := l_batch_requested + 1;
1204 b_skip_this_one := FALSE;
1205 ELSE
1206 b_we_have_batches := FALSE;
1207 END IF;
1208 ELSE
1209 b_we_have_batches := FALSE;
1210 END IF;
1211 END; -- batches
1212 END LOOP; -- while more batches exist in the range
1213 -- end SPR C166 BY BC
1214 -- Check for error totals to return a status from concurrent manager.
1215 -- Normal
1216 FND_MESSAGE.SET_NAME('HXT','HXT_39358_COMP_NORMAL'); -- HXT11
1217 l_errbuf := FND_MESSAGE.GET; -- HXT11
1218 FND_MESSAGE.CLEAR; -- HXT11
1219 l_retcode := 0;
1220 -- No batches seleceted at all
1221 IF l_counter = 0 THEN
1222 FND_MESSAGE.SET_NAME('HXT','HXT_39359_NO_BATCHES_SEL'); -- HXT11
1223 l_errbuf := FND_MESSAGE.GET; -- HXT11
1224 FND_MESSAGE.CLEAR; -- HXT11
1225 l_retcode := 2;
1226 END IF;
1227 IF b_inverted_batch_nums = TRUE THEN
1228 FND_MESSAGE.SET_NAME('HXT','HXT_39360_STR_BTCH_NUM_TOO_LRG'); --HXT11
1229 l_errbuf := FND_MESSAGE.GET; -- HXT11
1230 FND_MESSAGE.CLEAR; -- HXT11
1231 l_retcode := 2;
1232 END IF;
1233 IF l_final_rollback_retcode > 0 THEN
1234 FND_MESSAGE.SET_NAME('HXT','HXT_39361_ERR_DURING_ROLLBACK'); -- HXT11
1235 l_errbuf := FND_MESSAGE.GET; -- HXT11
1236 FND_MESSAGE.CLEAR; -- HXT11
1237 l_retcode := 2;
1238 END IF;
1239 -- A warning was returned from the validate process
1240 IF l_final_valid_retcode = 1 THEN
1241 l_retcode := 1;
1242 END IF;
1243 IF l_final_valid_retcode = 2 THEN
1244 HXT_UTIL.DEBUG('l_final_valid_retcode is 2');-- debug only --HXT115
1245 FND_MESSAGE.SET_NAME('HXT','HXT_39362_BATCH_ERROR'); -- HXT11
1246 l_errbuf := FND_MESSAGE.GET; -- HXT11
1247 FND_MESSAGE.CLEAR; -- HXT11
1248 l_retcode := 2;
1249 END IF;
1250 -- a system level error occured somewhere during processing
1251 IF (l_final_valid_retcode = 3 OR l_final_sum_retcode = 3) THEN
1252 FND_MESSAGE.SET_NAME('HXT','HXT_39363_SYSTEM_ERROR'); -- HXT11
1253 l_errbuf := FND_MESSAGE.GET; -- HXT11
1254 FND_MESSAGE.CLEAR; -- HXT11
1255 l_retcode := 2;
1256 END IF;
1257 retcode := l_retcode;
1258 errbuf := l_errbuf;
1259 HXT_UTIL.DEBUG('Retcode:' || TO_CHAR(l_retcode) || ' ' || l_errbuf);-- debug only
1260
1261 /********Bug: 4620315 **********/
1262 /*** To merge the batch TCs by calling 'merge_batches' procedure ***/
1263
1264 IF p_merge_flag = '1' and p_process_mode = 'V' THEN
1265 IF g_debug THEN
1266 hr_utility.trace('Before calling merge_batches proc');
1267 END IF;
1268 merge_batches (p_merge_batch_name,
1269 l_merge_batches,
1270 l_del_empty_batches,
1271 p_bus_group_id,
1272 'NR'
1273 );
1274 END IF;
1275
1276 /********Bug: 4620315 **********/
1277
1278 IF retcode = 2 THEN /* Bug: 6064910 */
1279 COMMIT;
1280 END IF;
1281
1282 EXCEPTION
1283 WHEN g_lookup_not_found THEN --SIR517 PWM 18FEB00 TESTING
1284 HXT_UTIL.DEBUG('Oops...g_lookup_not_found in procedure sum_to_mix');
1285 l_errbuf := substr(FND_MESSAGE.GET,1,65);
1286 errbuf := l_errbuf;
1287 HXT_BATCH_PROCESS.Insert_Pay_Batch_Errors( l_batch_id,
1288 'VE', -- RETROPAY
1289 '',
1290 l_return);
1291 retcode := 2;
1292 Set_Batch_Status(l_date_earned, l_batch_id, 'VE');
1293 --commit;
1294 IF retcode = 2 THEN /* Bug: 6064910 */
1295 COMMIT;
1296 END IF;
1297 WHEN g_rollback_error THEN --SIR517 PWM 18FEB00 TESTING
1298 HXT_UTIL.DEBUG('Oops...Rollback Error');
1299 l_errbuf := substr(FND_MESSAGE.GET,1,65);
1300 errbuf := l_errbuf;
1301 FND_FILE.put_line(fnd_file.log,errbuf);
1302 raise;
1303 WHEN OTHERS THEN
1304 -- Bug 10143866
1305 hr_utility.trace(dbms_utility.format_error_backtrace);
1306 retcode := 2;
1307 FND_MESSAGE.SET_NAME('HXT','HXT_39363_SYSTEM_ERROR');
1308 l_errbuf := FND_MESSAGE.GET;
1309 FND_MESSAGE.CLEAR;
1310 errbuf := l_errbuf;
1311 Set_Batch_Status(l_date_earned, l_batch_id, 'VE');
1312 Insert_Pay_Batch_Errors( l_batch_id,
1313 'VE',
1314 sqlerrm,
1315 l_return);
1316 --commit;
1317 IF retcode = 2 THEN /* Bug: 6064910 */
1318 COMMIT;
1319 END IF;
1320 END main_process;
1321 --------------------------------------------------------------------------------
1322 FUNCTION convert_lookup (p_lookup_code IN VARCHAR2,
1323 p_lookup_type IN VARCHAR2,
1324 p_date_active IN DATE)
1325 RETURN VARCHAR2 IS
1326 l_meaning HR_LOOKUPS.MEANING%TYPE;
1327 cursor get_meaning_cur(p_code VARCHAR2, p_type VARCHAR2, p_date DATE) is
1328 SELECT fcl.meaning
1329 FROM hr_lookups fcl
1330 WHERE fcl.lookup_code = p_code
1331 AND fcl.lookup_type = p_type
1332 AND fcl.enabled_flag = 'Y'
1333 AND p_date BETWEEN nvl(fcl.start_date_active, p_date)
1334 AND nvl(fcl.end_date_active, p_date);
1335 BEGIN
1336
1337 if g_debug then
1338 hr_utility.set_location('convert_lookup',10);
1339 end if;
1340 HXT_UTIL.DEBUG('convert_lookup - code = '||p_lookup_code||'
1341 type = '||p_lookup_type||' date = '||fnd_date.date_to_chardate(p_date_active));
1342
1343
1344 -- Bug 8888777
1345 -- Need to do a conversion only if IV expected is in Display format.
1346 -- Skip the conversion if IV_format is INTERNAL = Y
1347 IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL
1348 AND g_IV_format = 'N'
1349 THEN
1350 if g_debug then
1351 hr_utility.set_location('convert_lookup',20);
1352 end if;
1353 OPEN get_meaning_cur(p_lookup_code, p_lookup_type, p_date_active);
1354 FETCH get_meaning_cur into l_meaning;
1355 if g_debug then
1356 hr_utility.trace('l_meaning :'||l_meaning);
1357 end if;
1358 IF get_meaning_cur%NOTFOUND then
1359 if g_debug then
1360 hr_utility.set_location('convert_lookup',30);
1361 end if;
1362 FND_MESSAGE.SET_NAME('HXT','HXT_39483_LOOKUP_NOT_FOUND');
1363 FND_MESSAGE.SET_TOKEN('CODE', p_lookup_code);
1364 FND_MESSAGE.SET_TOKEN('TYPE', p_lookup_type);
1365 RAISE g_lookup_not_found;
1366 END IF;
1367 ELSE
1368 if g_debug then
1369 hr_utility.set_location('convert_lookup',40);
1370 end if;
1371 l_meaning := p_lookup_code;
1372 if g_debug then
1373 hr_utility.trace('l_meaning :'||l_meaning);
1374 end if;
1375 END IF;
1376 if g_debug then
1377 hr_utility.set_location('convert_lookup',50);
1378 end if;
1379 RETURN l_meaning;
1380 END convert_lookup;
1381 --
1382 -- This function is crated to get the lookup_code for translated
1383 -- input-value names
1384 --
1385 FUNCTION get_lookup_code (p_meaning IN VARCHAR2,
1386 p_date_active IN DATE)
1387 RETURN VARCHAR2 IS
1388 l_lookup_code HR_LOOKUPS.lookup_code%TYPE;
1389 cursor get_lookup_code_cur is
1390 SELECT lookup_code
1391 FROM fnd_lookup_values
1392 WHERE meaning = p_meaning
1393 AND lookup_type = 'NAME_TRANSLATIONS'
1394 AND enabled_flag = 'Y'
1395 AND p_date_active BETWEEN nvl(start_date_active, p_date_active)
1396 AND nvl(end_date_active, p_date_active);
1397 BEGIN
1398 g_debug :=hr_utility.debug_enabled;
1399 if g_debug then
1400 hr_utility.set_location('get_lookup_code',10);
1401 end if;
1402 HXT_UTIL.DEBUG('get_lookup_ code for meaning = '||p_meaning||'
1403 type = '||'NAME_TRANSLATIONS'||' date = '||fnd_date.date_to_chardate(p_date_active));
1404 if g_debug then
1405 hr_utility.trace('p_meaning :'||p_meaning);
1406 end if;
1407 IF p_meaning IS NOT NULL THEN
1408 if g_debug then
1409 hr_utility.set_location('get_lookup_code',20);
1410 end if;
1411 OPEN get_lookup_code_cur;
1412 FETCH get_lookup_code_cur into l_lookup_code;
1413 if g_debug then
1414 hr_utility.trace('l_lookup_code :'||l_lookup_code);
1415 end if;
1416 IF get_lookup_code_cur%NOTFOUND then
1417 if g_debug then
1418 hr_utility.set_location('get_lookup_code',30);
1419 hr_utility.trace('get_lookup_code_cur NOT FOUND');
1420 end if;
1421 -- FND_MESSAGE.SET_NAME('HXT','HXT_39483_LOOKUP_NOT_FOUND');
1422 -- FND_MESSAGE.SET_TOKEN('CODE', p_meaning); --SIR517 PWM 18FEB00
1423 -- FND_MESSAGE.SET_TOKEN('TYPE', 'NAME_TRANSLATIONS'); --SIR517 PWM 18FEB00
1424 -- RAISE g_lookup_not_found;
1425 null; -- This is done to fix bug 1761779 -- 17/May/2001
1426 END IF;
1427 ELSE
1428 if g_debug then
1429 hr_utility.set_location('get_lookup_code',40);
1430 end if;
1431 l_lookup_code := p_meaning;
1432 if g_debug then
1433 hr_utility.trace('p_meaning is null');
1434 hr_utility.trace('l_lookup_code:'||l_lookup_code);
1435 end if;
1436 END IF;
1437 if g_debug then
1438 hr_utility.trace('l_lookup_code :'||l_lookup_code);
1439 hr_utility.set_location('get_lookup_code',50);
1440 end if;
1441 RETURN l_lookup_code;
1442 END get_lookup_code;
1443 -- Place OTM data into BEE values per input values
1444 -- HXT_UTIL.DEBUG('Putting OTM data into BEE values per input values'); --HXT115
1445 -- In order to get the input-value logic work in different legislations we need
1446 -- to create (SEED) new lookups for 'Hours' , 'Hourly Rate', 'Rate Multiple',
1447 -- and 'Rate Code' with lookup_type of 'NAME_TRANSLATION' and lookup_code
1448 -- of 'HOURS', 'HOURLY_RATE', 'RATE_MULTIPLE' and 'RATE_CODE' respectively.
1449 -- Then the customers in different countries need to create the above input
1450 -- values with the name which is directly translated from the above names for
1451 -- OTM elements.
1452 -- For example: In French the user must create an input value for 'Hours'
1453 -- to be 'Heures' and then to determine which input value 'Heures' is
1454 -- associated with we look at the hr_lookups and if we find an entry with
1455 -- lookup_type = 'NAME_TRANSLATIONS' and lookup_code = 'HOURS' and
1456 -- Meaning to be 'Heures' then we know that this input value would map
1457 -- to 'Hours'.
1458 -- What need to be noted that it is the customer's responsibilty to create
1459 -- input values which are the direct translation of 'Hours','Hourly Rate',
1460 -- 'Pay Value' , 'Rate Multiple' and 'Rate Code'
1461 --
1462 PROCEDURE dtl_to_BEE(p_values_rec IN HXT_BATCH_VALUES_V%ROWTYPE,
1463 p_sum_retcode IN OUT NOCOPY NUMBER,
1464 p_batch_sequence IN NUMBER)
1465 IS
1466 --l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
1467 l_batch_line_id PAY_BATCH_LINES.BATCH_LINE_ID%TYPE;
1468 l_value_meaning hr_lookups.meaning%TYPE;
1469 l_return NUMBER;
1470 l_line_ovn number;
1471
1472 -- Bug 12993528
1473 TYPE input_value_record IS RECORD
1474 (sequence PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE,
1475 name PAY_INPUT_VALUES_F_TL.NAME%TYPE, --FORMS60
1476 lookup PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE,
1477 otl_sequence NUMBER );
1478
1479 TYPE input_values_table IS TABLE OF input_value_record
1480 INDEX BY BINARY_INTEGER;
1481 hxt_value input_values_table;
1482
1483 TYPE pbl_values_table IS TABLE OF PAY_BATCH_LINES.VALUE_1%TYPE
1484 INDEX BY BINARY_INTEGER;
1485 pbl_value pbl_values_table;
1486
1487 CURSOR c_date_input_value(cp_element_type_id NUMBER
1488 ,cp_assignment_id NUMBER
1489 ,cp_effective_date DATE) IS
1490 SELECT distinct PIV.name -- PIV.display_sequence
1491 FROM --pay_element_types_f PET
1492 pay_input_values_f PIV
1493 ,pay_accrual_plans PAP
1494 ,pay_net_calculation_rules PNCR
1495 WHERE--PET.element_type_id = cp_element_type_id
1496 --AND PET.element_type_id = PIV.element_type_id
1497 PIV.element_type_id = cp_element_type_id
1498 AND cp_effective_date between PIV.effective_start_date
1499 and PIV.effective_end_date
1500 AND PNCR.date_input_value_id = PIV.input_value_id
1501 AND PNCR.input_value_id <> PAP.pto_input_value_id
1502 AND PNCR.input_value_id <> PAP.co_input_value_id
1503 AND PNCR.accrual_plan_id = PAP.accrual_plan_id
1504 AND PAP.accrual_plan_id IN
1505 (SELECT PAPL.accrual_plan_id
1506 FROM pay_accrual_plans PAPL
1507 ,pay_element_links_f PEL
1508 ,pay_element_entries_f PEE
1509 WHERE PEL.element_type_id = PAPL.accrual_plan_element_type_id
1510 AND cp_effective_date between PEL.effective_start_date
1511 and PEL.effective_end_date
1512 AND PEE.element_link_id = PEL.element_link_id
1513 AND PEE.assignment_id = cp_assignment_id
1514 AND cp_effective_date between PEE.effective_start_date
1515 and PEE.effective_end_date
1516 );
1517
1518
1519 -- Bug 8888777
1520 -- Added the following cursor, to pick up Input values from
1521 -- the summary table.
1522 CURSOR get_input_values(p_id IN NUMBER)
1523 IS SELECT
1524 attribute1,
1525 attribute2,
1526 attribute3,
1527 attribute4,
1528 attribute5,
1529 attribute6,
1530 attribute7,
1531 attribute8,
1532 attribute9,
1533 attribute10,
1534 attribute11,
1535 attribute12,
1536 attribute13,
1537 attribute14,
1538 attribute15
1539 FROM hxt_sum_hours_worked_f
1540 WHERE id = p_id;
1541
1542 -- Bug 13777315
1543 -- Takes in all the parameters that determine Rollup
1544 -- The rolled up values are compared against the Normal view
1545 -- to get all the rowids the SUM is comprised of.
1546
1547 CURSOR get_rowids( p_cost_allocation_keyflex_id NUMBER,
1548 p_element_type_id NUMBER,
1549 p_assignment_id NUMBER,
1550 p_batch_id NUMBER,
1551 p_assignment_number VARCHAR2,
1552 p_concatenated_segments VARCHAR2,
1553 p_element_name VARCHAR2,
1554 p_meaning VARCHAR2,
1555 p_segment1 VARCHAR2,
1556 p_segment2 VARCHAR2,
1557 p_segment3 VARCHAR2,
1558 p_segment4 VARCHAR2,
1559 p_segment5 VARCHAR2,
1560 p_segment6 VARCHAR2,
1561 p_segment7 VARCHAR2,
1562 p_segment8 VARCHAR2,
1563 p_segment9 VARCHAR2,
1564 p_segment10 VARCHAR2,
1565 p_segment11 VARCHAR2,
1566 p_segment12 VARCHAR2,
1567 p_segment13 VARCHAR2,
1568 p_segment14 VARCHAR2,
1569 p_segment15 VARCHAR2,
1570 p_segment16 VARCHAR2,
1571 p_segment17 VARCHAR2,
1572 p_segment18 VARCHAR2,
1573 p_segment19 VARCHAR2,
1574 p_segment20 VARCHAR2,
1575 p_segment21 VARCHAR2,
1576 p_segment22 VARCHAR2,
1577 p_segment23 VARCHAR2,
1578 p_segment24 VARCHAR2,
1579 p_segment25 VARCHAR2,
1580 p_segment26 VARCHAR2,
1581 p_segment27 VARCHAR2,
1582 p_segment28 VARCHAR2,
1583 p_segment29 VARCHAR2,
1584 p_segment30 VARCHAR2,
1585 p_amount NUMBER,
1586 p_rate_multiple NUMBER,
1587 p_hourly_rate NUMBER,
1588 p_flex_value VARCHAR2,
1589 p_location_code VARCHAR2,
1590 p_fcl_tax_rule_code VARCHAR2,
1591 p_separate_check_flag VARCHAR2,
1592 p_state_name VARCHAR2,
1593 p_county_name VARCHAR2,
1594 p_city_name VARCHAR2,
1595 p_zip_code VARCHAR2)
1596 IS SELECT hrw_rowid
1597 FROM hxt_batch_values_v
1598 WHERE element_type_id = p_element_type_id
1599 AND assignment_id = p_assignment_id
1600 AND batch_id = p_batch_id
1601 AND assignment_number = p_assignment_number
1602 AND element_name = p_element_name
1603 AND ( cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
1604 OR cost_allocation_keyflex_id IS NULL)
1605 AND ( concatenated_segments = p_concatenated_segments
1606 OR concatenated_segments IS NULL)
1607 AND ( reason = p_meaning
1608 OR reason IS NULL)
1609 AND ( segment1 = p_segment1
1610 OR segment1 IS NULL)
1611 AND ( segment2 = p_segment2
1612 OR segment2 IS NULL)
1613 AND ( segment3 = p_segment3
1614 OR segment3 IS NULL)
1615 AND ( segment4 = p_segment4
1616 OR segment4 IS NULL)
1617 AND ( segment5 = p_segment5
1618 OR segment5 IS NULL)
1619 AND ( segment6 = p_segment6
1620 OR segment6 IS NULL)
1621 AND ( segment7 = p_segment7
1622 OR segment7 IS NULL)
1623 AND ( segment8 = p_segment8
1624 OR segment8 IS NULL)
1625 AND ( segment9 = p_segment9
1626 OR segment9 IS NULL)
1627 AND ( segment10 = p_segment10
1628 OR segment10 IS NULL)
1629 AND ( segment11 = p_segment11
1630 OR segment11 IS NULL)
1631 AND ( segment12 = p_segment12
1632 OR segment12 IS NULL)
1633 AND ( segment13 = p_segment13
1634 OR segment13 IS NULL)
1635 AND ( segment14 = p_segment14
1636 OR segment14 IS NULL)
1637 AND ( segment15 = p_segment15
1638 OR segment15 IS NULL)
1639 AND ( segment16 = p_segment16
1640 OR segment16 IS NULL)
1641 AND ( segment17 = p_segment17
1642 OR segment17 IS NULL)
1643 AND ( segment18 = p_segment18
1644 OR segment18 IS NULL)
1645 AND ( segment19 = p_segment19
1646 OR segment19 IS NULL)
1647 AND ( segment20 = p_segment20
1648 OR segment20 IS NULL)
1649 AND ( segment21 = p_segment21
1650 OR segment21 IS NULL)
1651 AND ( segment22 = p_segment22
1652 OR segment22 IS NULL)
1653 AND ( segment23 = p_segment23
1654 OR segment23 IS NULL)
1655 AND ( segment24 = p_segment24
1656 OR segment24 IS NULL)
1657 AND ( segment25 = p_segment25
1658 OR segment25 IS NULL)
1659 AND ( segment26 = p_segment26
1660 OR segment26 IS NULL)
1661 AND ( segment27 = p_segment27
1662 OR segment27 IS NULL)
1663 AND ( segment28 = p_segment28
1664 OR segment28 IS NULL)
1665 AND ( segment29 = p_segment29
1666 OR segment29 IS NULL)
1667 AND ( segment30 = p_segment30
1668 OR segment30 IS NULL)
1669 AND ( amount = p_amount
1670 OR amount IS NULL)
1671 AND ( rate_multiple = p_rate_multiple
1672 OR rate_multiple IS NULL)
1673 AND ( hourly_rate = p_hourly_rate
1674 OR hourly_rate IS NULL)
1675 AND ( rate_code = p_flex_value
1676 OR rate_code IS NULL)
1677 AND ( location_code = p_location_code
1678 OR location_code IS NULL)
1679 AND ( fcl_tax_rule_code = p_fcl_tax_rule_code
1680 OR fcl_tax_rule_code IS NULL)
1681 AND ( separate_check_flag = p_separate_check_flag
1682 OR separate_check_flag IS NULL)
1683 AND ( state_name = p_state_name
1684 OR state_name IS NULL)
1685 AND ( county_name = p_county_name
1686 OR county_name IS NULL)
1687 AND ( city_name = p_city_name
1688 OR city_name IS NULL)
1689 AND ( zip_code = p_zip_code
1690 OR zip_code IS NULL) ;
1691
1692 TYPE VARCHARTAB IS TABLE OF VARCHAR2(50);
1693 l_rowtab VARCHARTAB;
1694
1695 l_piv_name varchar2(30);
1696 lv_pbl_flag varchar2(1) := 'N';
1697
1698
1699 BEGIN
1700
1701 if g_debug then
1702 hr_utility.set_location('dtl_to_BEE',10);
1703 end if;
1704 -- Initialize table
1705 FOR i in 1..15 LOOP
1706 hxt_value(i).sequence := null;
1707 hxt_value(i).name := null;
1708 hxt_value(i).lookup := null;
1709 END LOOP;
1710
1711 -- Bug 8888777
1712 -- Added the following call to pick up required input values
1713 -- from the summary table.
1714
1715 -- Bug 9774867
1716 -- Added a TO_CHAR conversion for the indexes below.
1717
1718 IF NOT g_xiv_table.EXISTS(TO_CHAR(p_values_rec.parent_id))
1719 THEN
1720 OPEN get_input_values(p_values_rec.parent_id);
1721 FETCH get_input_values
1722 INTO g_xiv_table(TO_CHAR(p_values_rec.parent_id));
1723 CLOSE get_input_values;
1724 END IF;
1725
1726 -- Get input values details for this element
1727 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
1728
1729 -- Bug 12993528
1730 -- Replaced the pay_paywsqee_pkg call with our own new procedure.
1731 -- Added new params otl_sequence for each of the 15 input values.
1732
1733 GET_INPUT_VALUE_DETAILS(p_values_rec.element_type_id,
1734 p_values_rec.date_worked,
1735 hxt_value(1).sequence,
1736 hxt_value(2).sequence,
1737 hxt_value(3).sequence,
1738 hxt_value(4).sequence,
1739 hxt_value(5).sequence,
1740 hxt_value(6).sequence,
1741 hxt_value(7).sequence,
1742 hxt_value(8).sequence,
1743 hxt_value(9).sequence,
1744 hxt_value(10).sequence,
1745 hxt_value(11).sequence,
1746 hxt_value(12).sequence,
1747 hxt_value(13).sequence,
1748 hxt_value(14).sequence,
1749 hxt_value(15).sequence,
1750 hxt_value(1).name,
1751 hxt_value(2).name,
1752 hxt_value(3).name,
1753 hxt_value(4).name,
1754 hxt_value(5).name,
1755 hxt_value(6).name,
1756 hxt_value(7).name,
1757 hxt_value(8).name,
1758 hxt_value(9).name,
1759 hxt_value(10).name,
1760 hxt_value(11).name,
1761 hxt_value(12).name,
1762 hxt_value(13).name,
1763 hxt_value(14).name,
1764 hxt_value(15).name,
1765 hxt_value(1).lookup,
1766 hxt_value(2).lookup,
1767 hxt_value(3).lookup,
1768 hxt_value(4).lookup,
1769 hxt_value(5).lookup,
1770 hxt_value(6).lookup,
1771 hxt_value(7).lookup,
1772 hxt_value(8).lookup,
1773 hxt_value(9).lookup,
1774 hxt_value(10).lookup,
1775 hxt_value(11).lookup,
1776 hxt_value(12).lookup,
1777 hxt_value(13).lookup,
1778 hxt_value(14).lookup,
1779 hxt_value(15).lookup,
1780 hxt_value(1).otl_sequence,
1781 hxt_value(2).otl_sequence,
1782 hxt_value(3).otl_sequence,
1783 hxt_value(4).otl_sequence,
1784 hxt_value(5).otl_sequence,
1785 hxt_value(6).otl_sequence,
1786 hxt_value(7).otl_sequence,
1787 hxt_value(8).otl_sequence,
1788 hxt_value(9).otl_sequence,
1789 hxt_value(10).otl_sequence,
1790 hxt_value(11).otl_sequence,
1791 hxt_value(12).otl_sequence,
1792 hxt_value(13).otl_sequence,
1793 hxt_value(14).otl_sequence,
1794 hxt_value(15).otl_sequence
1795 );
1796 if g_debug then
1797 hr_utility.set_location('dtl_to_BEE',20);
1798 end if;
1799 -- Place OTM data into BEE values per input values
1800 HXT_UTIL.DEBUG('Putting OTM data into BEE values per input values'); --HXT115
1801 --
1802 -- In order to get the input-value logic work in different legislations we
1803 -- need to create (SEED) new lookups for 'Hours', 'Hourly Rate',
1804 -- 'Rate Multiple', and 'Rate Code' with lookup_type of 'NAME_TRANSLATION'
1805 -- and lookup_code of 'HOURS', 'HOURLY_RATE', 'RATE_MULTIPLE' and
1806 -- 'RATE_CODE' respectively. Then the customers in different countries
1807 -- need to create the above input values with the name which is directly
1808 -- translated from the above names for OTM elements.
1809 --
1810 -- For example: In French the user must create an input value for 'Hours'
1811 -- to be 'Heures' and then to determine which input value 'Heures' is
1812 -- associated with we look at the hr_lookups and if we find an entry with
1813 -- lookup_type = 'NAME_TRANSLATIONS' and lookup_code = 'HOURS' and Meaning
1814 -- to be 'Heures' then we know that this input vale woul map to 'Hours'.
1815 --
1816 -- What need to be noted that it is the customer's responsibilty to create
1817 -- input values which are the direct translation of 'Hours','Hourly Rate',
1818 -- 'Pay Value' , 'Rate Multiple' and 'Rate Code'
1819 --
1820 FOR i in 1..15 LOOP
1821 --
1822 -- We need to get the lookup_code for the input_value names before
1823 -- processing the further logic on the screen value for the input values.
1824 --
1825 lv_pbl_flag := 'N';
1826 if g_debug then
1827 hr_utility.set_location('dtl_to_BEE',30);
1828 hr_utility.trace('hxt_value_name_'||to_char(i)||' :'|| hxt_value(i).name);
1829 hr_utility.trace('p_values_rec.date_worked:'||p_values_rec.date_worked);
1830 end if;
1831 l_value_meaning := get_lookup_code (hxt_value(i).name,
1832 p_values_rec.date_worked);
1833 if g_debug then
1834 hr_utility.trace('l_value_meaning :'|| l_value_meaning);
1835 end if;
1836 if l_value_meaning = 'HOURS' then
1837 if g_debug then
1838 hr_utility.set_location('dtl_to_BEE',40);
1839 end if;
1840
1841 -- Bug 13777315
1842 -- If IV Upgrade is done, there is no need to convert anything.
1843 -- But the value should be in canonical format to take care of conversion.
1844
1845 IF g_iv_upgrade = 'N'
1846 THEN
1847 pbl_value(i) := convert_lookup(p_values_rec.hours,
1848 hxt_value(i).lookup,
1849 p_values_rec.date_worked);
1850
1851 ELSIF g_iv_upgrade = 'Y'
1852 THEN
1853
1854 pbl_value(i) := FND_NUMBER.number_to_canonical(p_values_rec.hours);
1855
1856 END IF;
1857
1858 if g_debug then
1859 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1860 end if;
1861 elsif l_value_meaning = 'AMOUNT' then
1862 if g_debug then
1863 hr_utility.set_location('dtl_to_BEE',50);
1864 end if;
1865
1866 -- Bug 13777315
1867 IF g_iv_upgrade = 'N'
1868 THEN
1869
1870 pbl_value(i) := convert_lookup(p_values_rec.amount,
1871 hxt_value(i).lookup,
1872 p_values_rec.date_worked);
1873 ELSIF g_iv_upgrade = 'Y'
1874 THEN
1875 pbl_value(i) := FND_NUMBER.number_to_canonical(p_values_rec.amount);
1876 END IF;
1877
1878 if g_debug then
1879 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1880 end if;
1881 elsif l_value_meaning = 'RATE_MULTIPLE' then
1882 if g_debug then
1883 hr_utility.set_location('dtl_to_BEE',60);
1884 end if;
1885
1886 -- Bug 13777315
1887 IF g_iv_upgrade = 'N'
1888 THEN
1889
1890 pbl_value(i) := convert_lookup(p_values_rec.rate_multiple,
1891 hxt_value(i).lookup,
1892 p_values_rec.date_worked);
1893 ELSIF g_iv_upgrade = 'Y'
1894 THEN
1895 pbl_value(i) := FND_NUMBER.number_to_canonical(p_values_rec.rate_multiple);
1896 END IF;
1897
1898 if g_debug then
1899 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1900 end if;
1901 elsif l_value_meaning = 'HOURLY_RATE' then
1902 if g_debug then
1903 hr_utility.set_location('dtl_to_BEE',70);
1904 end if;
1905
1906 -- Bug 13777315
1907 IF g_iv_upgrade = 'N'
1908 THEN
1909
1910 pbl_value(i) := convert_lookup(p_values_rec.hourly_rate,
1911 hxt_value(i).lookup,
1912 p_values_rec.date_worked);
1913 ELSIF g_iv_upgrade = 'Y'
1914 THEN
1915 pbl_value(i) := FND_NUMBER.number_to_canonical(p_values_rec.hourly_rate);
1916 END IF;
1917
1918 if g_debug then
1919 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1920 end if;
1921 elsif l_value_meaning = 'RATE' then
1922 if g_debug then
1923 hr_utility.set_location('dtl_to_BEE',80);
1924 end if;
1925
1926 -- Bug 13777315
1927 IF g_iv_upgrade = 'N'
1928 THEN
1929
1930 pbl_value(i) := convert_lookup(p_values_rec.hourly_rate,
1931 hxt_value(i).lookup,
1932 p_values_rec.date_worked);
1933 ELSIF g_iv_upgrade = 'Y'
1934 THEN
1935 pbl_value(i) := FND_NUMBER.number_to_canonical(p_values_rec.hourly_rate);
1936 END IF;
1937
1938 if g_debug then
1939 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1940 end if;
1941 elsif l_value_meaning = 'RATE_CODE' then
1942 if g_debug then
1943 hr_utility.set_location('dtl_to_BEE',90);
1944 end if;
1945 pbl_value(i) := convert_lookup(p_values_rec.rate_code,
1946 hxt_value(i).lookup,
1947 p_values_rec.date_worked);
1948 if g_debug then
1949 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1950 end if;
1951 -- BEGIN US localization
1952 elsif hxt_value(i).name = 'Jurisdiction' then
1953 if g_debug then
1954 hr_utility.set_location('dtl_to_BEE',100);
1955 end if;
1956 if( p_values_rec.state_name is not null or
1957 p_values_rec.county_name is not null or
1958 p_values_rec.city_name is not null or
1959 p_values_rec.zip_code is not null)
1960 then
1961 pbl_value(i):= convert_lookup( pay_ac_utility.get_geocode
1962 (p_values_rec.state_name, p_values_rec.county_name, p_values_rec.city_name,
1963 p_values_rec.zip_code),
1964 hxt_value(i).lookup,
1965 p_values_rec.date_worked);
1966 else
1967 pbl_value(i) := convert_lookup(p_values_rec.location_code,
1968 hxt_value(i).lookup,
1969 p_values_rec.date_worked);
1970 end if;
1971
1972 if g_debug
1973 then
1974 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1975 end if;
1976 elsif hxt_value(i).name = 'Deduction Processing' then
1977 if g_debug then
1978 hr_utility.set_location('dtl_to_BEE',110);
1979 end if;
1980 pbl_value(i) := convert_lookup(p_values_rec.fcl_tax_rule_code,
1981 hxt_value(i).lookup,
1982 p_values_rec.date_worked);
1983 if g_debug then
1984 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1985 end if;
1986 elsif hxt_value(i).name = 'Separate Check' then
1987 if g_debug then
1988 hr_utility.set_location('dtl_to_BEE',120);
1989 end if;
1990 pbl_value(i) := convert_lookup(p_values_rec.separate_check_flag,
1991 hxt_value(i).lookup,
1992 p_values_rec.date_worked);
1993 if g_debug then
1994 hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1995 end if;
1996 -- END US localization
1997 elsif hxt_value(i).name is not null THEN -- pbl_value(i) := NULL;
1998 if g_debug then
1999 hr_utility.set_location('dtl_to_BEE',130);
2000 end if;
2001 OPEN c_date_input_value(p_values_rec.element_type_id
2002 ,p_values_rec.assignment_id
2003 ,p_values_rec.date_worked);
2004 LOOP
2005 if g_debug then
2006 hr_utility.set_location('dtl_to_BEE',140);
2007 end if;
2008 FETCH c_date_input_value into l_piv_name;
2009 EXIT WHEN c_date_input_value%NOTFOUND;
2010 if g_debug then
2011 hr_utility.trace('l_piv_name :'||l_piv_name);
2012 hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
2013 end if;
2014 IF l_piv_name = hxt_value(i).name THEN
2015 if g_debug then
2016 hr_utility.set_location('dtl_to_BEE',150);
2017 end if;
2018 -- pbl_value(i) := to_char(p_values_rec.date_worked,'DD-MON-YYYY');
2019 pbl_value(i) := fnd_date.date_to_canonical(p_values_rec.date_worked);
2020 lv_pbl_flag := 'Y';
2021 if g_debug then
2022 hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
2023 end if;
2024 exit;
2025 END IF;
2026 END LOOP;
2027 CLOSE c_date_input_value;
2028
2029 -- Bug 9650990
2030 -- Do this processing only if pbl_value(i) is still not set for input values with non NULL name.
2031 if g_debug then
2032 hr_utility.trace('Before : lv_pbl_flag :'||lv_pbl_flag);
2033 end if;
2034
2035 IF lv_pbl_flag = 'N' --THEN
2036 -- Bug 13639441
2037 -- Added code here
2038 AND NVL(FND_PROFILE.VALUE('HXT_ALLOW_SUM_CUSTOM_ATTRIBUTE'),'N') = 'N'
2039 THEN
2040
2041 hr_utility.trace('Coming into new code as if profile is turned OFF');
2042
2043 -- Bug 8888777
2044 -- Control is here means that no fixed input value is encountered, but
2045 -- still some IV with a Non NULL name. Convert this and copy it.
2046
2047 -- Bug 9774867
2048 -- Added a TO_CHAR conversion for the indexes below.
2049
2050 -- Bug 12993528
2051 -- Modified the below IF conditions to check hxt_value(i).otl_sequence
2052 -- instead of i. To make it clearer, we are now comparing this otl_sequence
2053 -- instead of the sequence of input values.
2054
2055 IF hxt_value(i).otl_sequence = 1
2056 THEN
2057 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute1,
2058 hxt_value (i).lookup,
2059 p_values_rec.date_worked);
2060 lv_pbl_flag := 'Y';
2061 ELSIF hxt_value(i).otl_sequence = 2
2062 THEN
2063 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute2,
2064 hxt_value (i).lookup,
2065 p_values_rec.date_worked);
2066 lv_pbl_flag := 'Y';
2067 ELSIF hxt_value(i).otl_sequence = 3
2068 THEN
2069 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute3,
2070 hxt_value (i).lookup,
2071 p_values_rec.date_worked);
2072 lv_pbl_flag := 'Y';
2073 ELSIF hxt_value(i).otl_sequence = 4
2074 THEN
2075 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute4,
2076 hxt_value (i).lookup,
2077 p_values_rec.date_worked);
2078 lv_pbl_flag := 'Y';
2079 ELSIF hxt_value(i).otl_sequence = 5
2080 THEN
2081 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute5,
2082 hxt_value (i).lookup,
2083 p_values_rec.date_worked);
2084 lv_pbl_flag := 'Y';
2085 ELSIF hxt_value(i).otl_sequence = 6
2086 THEN
2087 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute6,
2088 hxt_value (i).lookup,
2089 p_values_rec.date_worked);
2090 lv_pbl_flag := 'Y';
2091 ELSIF hxt_value(i).otl_sequence = 7
2092 THEN
2093 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute7,
2094 hxt_value (i).lookup,
2095 p_values_rec.date_worked);
2096 lv_pbl_flag := 'Y';
2097 ELSIF hxt_value(i).otl_sequence = 8
2098 THEN
2099 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute8,
2100 hxt_value (i).lookup,
2101 p_values_rec.date_worked);
2102 lv_pbl_flag := 'Y';
2103 ELSIF hxt_value(i).otl_sequence = 9
2104 THEN
2105 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute9,
2106 hxt_value (i).lookup,
2107 p_values_rec.date_worked);
2108 lv_pbl_flag := 'Y';
2109 ELSIF hxt_value(i).otl_sequence = 10
2110 THEN
2111 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute10,
2112 hxt_value (i).lookup,
2113 p_values_rec.date_worked);
2114 lv_pbl_flag := 'Y';
2115 ELSIF hxt_value(i).otl_sequence = 11
2116 THEN
2117 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute11,
2118 hxt_value (i).lookup,
2119 p_values_rec.date_worked);
2120 lv_pbl_flag := 'Y';
2121 ELSIF hxt_value(i).otl_sequence = 12
2122 THEN
2123 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute12,
2124 hxt_value (i).lookup,
2125 p_values_rec.date_worked);
2126 lv_pbl_flag := 'Y';
2127 ELSIF hxt_value(i).otl_sequence = 13
2128 THEN
2129 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute13,
2130 hxt_value (i).lookup,
2131 p_values_rec.date_worked);
2132 lv_pbl_flag := 'Y';
2133 ELSIF hxt_value(i).otl_sequence = 14
2134 THEN
2135 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute14,
2136 hxt_value (i).lookup,
2137 p_values_rec.date_worked);
2138 lv_pbl_flag := 'Y';
2139 ELSIF hxt_value(i).otl_sequence = 15
2140 THEN
2141 pbl_value(i) := convert_lookup(g_xiv_table(TO_CHAR(p_values_rec.parent_id)).attribute15,
2142 hxt_value (i).lookup,
2143 p_values_rec.date_worked);
2144 lv_pbl_flag := 'Y';
2145 END IF;
2146
2147
2148 END IF;
2149
2150
2151 if lv_pbl_flag = 'N' then
2152 if g_debug then
2153 hr_utility.set_location('dtl_to_BEE',160);
2154 end if;
2155 pbl_value(i) := NULL;
2156 if g_debug then
2157 hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
2158 end if;
2159 end if;
2160 if g_debug then
2161 hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
2162 end if;
2163 else
2164 if g_debug then
2165 hr_utility.set_location('dtl_to_BEE',180);
2166 end if;
2167 pbl_value(i) := NULL;
2168 if g_debug then
2169 hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
2170 end if;
2171 end if;
2172 if g_debug then
2173 hr_utility.set_location('dtl_to_BEE',190);
2174 end if;
2175 HXT_UTIL.DEBUG('value_'||to_char(i)||' = '||pbl_value(i)); --HXT115
2176 END LOOP;
2177 -- Get Batch Line ID
2178 if g_debug then
2179 hr_utility.set_location('dtl_to_BEE',200);
2180 end if;
2181 -- Get next sequence number
2182 -- l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_values_rec.batch_id);
2183 if g_debug then
2184 hr_utility.set_location('dtl_to_BEE',210);
2185 end if;
2186 HXT_UTIL.DEBUG('batch_sequence = '||to_char(p_batch_sequence)); --HXT115
2187 -- Insert data into BEE table
2188 PAY_BATCH_ELEMENT_ENTRY_API.create_batch_line
2189 (p_session_date => sysdate
2190 ,p_batch_id => p_values_rec.batch_id
2191 ,p_batch_line_status => 'U'
2192 ,p_assignment_id => p_values_rec.assignment_id
2193 ,p_assignment_number => p_values_rec.assignment_number
2194 ,p_batch_sequence => p_batch_sequence
2195 ,p_concatenated_segments => p_values_rec.concatenated_segments
2196 ,p_cost_allocation_keyflex_id => p_values_rec.cost_allocation_keyflex_id
2197 ,p_effective_date => p_values_rec.date_worked
2198 ,p_effective_start_date => p_values_rec.date_worked
2199 ,p_effective_end_date => p_values_rec.date_worked
2200 ,p_element_name => p_values_rec.element_name
2201 ,p_element_type_id => p_values_rec.element_type_id
2202 ,p_entry_type => 'E'
2203 ,p_date_earned => p_values_rec.date_worked
2204 ,p_reason => p_values_rec.reason
2205 ,p_segment1 => p_values_rec.segment1
2206 ,p_segment2 => p_values_rec.segment2
2207 ,p_segment3 => p_values_rec.segment3
2208 ,p_segment4 => p_values_rec.segment4
2209 ,p_segment5 => p_values_rec.segment5
2210 ,p_segment6 => p_values_rec.segment6
2211 ,p_segment7 => p_values_rec.segment7
2212 ,p_segment8 => p_values_rec.segment8
2213 ,p_segment9 => p_values_rec.segment9
2214 ,p_segment10 => p_values_rec.segment10
2215 ,p_segment11 => p_values_rec.segment11
2216 ,p_segment12 => p_values_rec.segment12
2217 ,p_segment13 => p_values_rec.segment13
2218 ,p_segment14 => p_values_rec.segment14
2219 ,p_segment15 => p_values_rec.segment15
2220 ,p_segment16 => p_values_rec.segment16
2221 ,p_segment17 => p_values_rec.segment17
2222 ,p_segment18 => p_values_rec.segment18
2223 ,p_segment19 => p_values_rec.segment19
2224 ,p_segment20 => p_values_rec.segment20
2225 ,p_segment21 => p_values_rec.segment21
2226 ,p_segment22 => p_values_rec.segment22
2227 ,p_segment23 => p_values_rec.segment23
2228 ,p_segment24 => p_values_rec.segment24
2229 ,p_segment25 => p_values_rec.segment25
2230 ,p_segment26 => p_values_rec.segment26
2231 ,p_segment27 => p_values_rec.segment27
2232 ,p_segment28 => p_values_rec.segment28
2233 ,p_segment29 => p_values_rec.segment29
2234 ,p_segment30 => p_values_rec.segment30
2235 ,p_value_1 => pbl_value(1)
2236 ,p_value_2 => pbl_value(2)
2237 ,p_value_3 => pbl_value(3)
2238 ,p_value_4 => pbl_value(4)
2239 ,p_value_5 => pbl_value(5)
2240 ,p_value_6 => pbl_value(6)
2241 ,p_value_7 => pbl_value(7)
2242 ,p_value_8 => pbl_value(8)
2243 ,p_value_9 => pbl_value(9)
2244 ,p_value_10 => pbl_value(10)
2245 ,p_value_11 => pbl_value(11)
2246 ,p_value_12 => pbl_value(12)
2247 ,p_value_13 => pbl_value(13)
2248 ,p_value_14 => pbl_value(14)
2249 ,p_value_15 => pbl_value(15)
2250 ,p_batch_line_id => l_batch_line_id
2251 ,p_object_version_number => l_line_ovn
2252 ,p_iv_all_internal_format => g_IV_format -- Bug 8888777
2253 );
2254 HXT_UTIL.DEBUG('Successful INSERT INTO pay_batch_lines'); --HXT115
2255 -- Update OTM detail row to show BEE line entry id
2256 IF p_values_rec.hrw_rowid IS NOT NULL THEN
2257 UPDATE HXT_DET_HOURS_WORKED_F
2258 set PBL_LINE_ID = l_batch_line_id
2259 WHERE rowid = p_values_rec.hrw_rowid;
2260
2261 IF g_debug
2262 THEN
2263 hr_utility.trace('Updated HXT_DET_HOURS_WORKED_F with line id '||l_batch_line_id);
2264 END IF;
2265
2266 -- Bug 13777315
2267 ELSE
2268
2269 -- Print out all the inputs for the Batch line.
2270 IF g_debug
2271 THEN
2272 hr_utility.trace(p_values_rec.cost_allocation_keyflex_id );
2273 hr_utility.trace(p_values_rec.element_type_id );
2274 hr_utility.trace(p_values_rec.assignment_id);
2275 hr_utility.trace(p_values_rec.batch_id );
2276 hr_utility.trace(p_values_rec.assignment_number );
2277 hr_utility.trace(p_values_rec.concatenated_segments );
2278 hr_utility.trace(p_values_rec.element_name );
2279 hr_utility.trace(p_values_rec.reason);
2280 hr_utility.trace(p_values_rec.segment1 );
2281 hr_utility.trace(p_values_rec.segment2);
2282 hr_utility.trace(p_values_rec.segment3);
2283 hr_utility.trace(p_values_rec.segment4);
2284 hr_utility.trace(p_values_rec.segment5);
2285 hr_utility.trace(p_values_rec.segment6);
2286 hr_utility.trace(p_values_rec.segment7);
2287 hr_utility.trace(p_values_rec.segment8);
2288 hr_utility.trace(p_values_rec.segment9);
2289 hr_utility.trace(p_values_rec.segment10);
2290 hr_utility.trace(p_values_rec.segment11);
2291 hr_utility.trace(p_values_rec.segment12);
2292 hr_utility.trace(p_values_rec.segment13);
2293 hr_utility.trace(p_values_rec.segment14);
2294 hr_utility.trace(p_values_rec.segment15);
2295 hr_utility.trace(p_values_rec.segment16);
2296 hr_utility.trace(p_values_rec.segment17);
2297 hr_utility.trace(p_values_rec.segment18);
2298 hr_utility.trace(p_values_rec.segment19);
2299 hr_utility.trace(p_values_rec.segment20);
2300 hr_utility.trace(p_values_rec.segment21);
2301 hr_utility.trace(p_values_rec.segment22);
2302 hr_utility.trace(p_values_rec.segment23);
2303 hr_utility.trace(p_values_rec.segment24);
2304 hr_utility.trace(p_values_rec.segment25);
2305 hr_utility.trace(p_values_rec.segment26);
2306 hr_utility.trace(p_values_rec.segment27);
2307 hr_utility.trace(p_values_rec.segment28);
2308 hr_utility.trace(p_values_rec.segment29);
2309 hr_utility.trace(p_values_rec.segment30);
2310 hr_utility.trace(p_values_rec.amount );
2311 hr_utility.trace(p_values_rec.rate_multiple);
2312 hr_utility.trace(p_values_rec.hourly_rate );
2313 hr_utility.trace(p_values_rec.rate_code);
2314 hr_utility.trace(p_values_rec.location_code);
2315 hr_utility.trace(p_values_rec.fcl_tax_rule_code );
2316 hr_utility.trace(p_values_rec.separate_check_flag);
2317 hr_utility.trace(p_values_rec.state_name);
2318 hr_utility.trace(p_values_rec.county_name );
2319 hr_utility.trace(p_values_rec.city_name );
2320 hr_utility.trace(p_values_rec.zip_code );
2321
2322 END IF;
2323
2324 -- Pass the input parameters to the original view
2325 -- to get the individual rowids.
2326 -- This happens only if HXT_ROLLUP_BATCH_HOURS is set.
2327 -- Eg. Reg. 8 hrs on all 5 days would have rolled up to 40 hours, in one record.
2328 -- Here, we pass the input values except date and hours to the original view
2329 -- to fetch the rowids for those 8 values.
2330
2331 OPEN get_rowids ( p_values_rec.cost_allocation_keyflex_id,
2332 p_values_rec.element_type_id,
2333 p_values_rec.assignment_id,
2334 p_values_rec.batch_id,
2335 p_values_rec.assignment_number,
2336 p_values_rec.concatenated_segments,
2337 p_values_rec.element_name,
2338 p_values_rec.reason,
2339 p_values_rec.segment1,
2340 p_values_rec.segment2,
2341 p_values_rec.segment3,
2342 p_values_rec.segment4,
2343 p_values_rec.segment5,
2344 p_values_rec.segment6,
2345 p_values_rec.segment7,
2346 p_values_rec.segment8,
2347 p_values_rec.segment9,
2348 p_values_rec.segment10,
2349 p_values_rec.segment11,
2350 p_values_rec.segment12,
2351 p_values_rec.segment13,
2352 p_values_rec.segment14,
2353 p_values_rec.segment15,
2354 p_values_rec.segment16,
2355 p_values_rec.segment17,
2356 p_values_rec.segment18,
2357 p_values_rec.segment19,
2358 p_values_rec.segment20,
2359 p_values_rec.segment21,
2360 p_values_rec.segment22,
2361 p_values_rec.segment23,
2362 p_values_rec.segment24,
2363 p_values_rec.segment25,
2364 p_values_rec.segment26,
2365 p_values_rec.segment27,
2366 p_values_rec.segment28,
2367 p_values_rec.segment29,
2368 p_values_rec.segment30,
2369 p_values_rec.amount,
2370 p_values_rec.rate_multiple,
2371 p_values_rec.hourly_rate,
2372 p_values_rec.rate_code,
2373 p_values_rec.location_code,
2374 p_values_rec.fcl_tax_rule_code,
2375 p_values_rec.separate_check_flag,
2376 p_values_rec.state_name,
2377 p_values_rec.county_name,
2378 p_values_rec.city_name,
2379 p_values_rec.zip_code );
2380
2381 FETCH get_rowids BULK COLLECT
2382 INTO l_rowtab;
2383 CLOSE get_rowids;
2384
2385 IF l_rowtab.COUNT > 0
2386 THEN
2387
2388 -- For all the rowids we found out here, update the pbl_line_id.
2389
2390 FORALL i IN l_rowtab.FIRST..l_rowtab.LAST
2391 UPDATE hxt_det_hours_worked_f
2392 SET pbl_line_id = l_batch_line_id
2393 WHERE rowid = CHARTOROWID(l_rowtab(i));
2394
2395 IF g_debug
2396 THEN
2397 hr_utility.trace('Printing Rowids being updated with batch line '||l_batch_line_id);
2398 FOR i IN l_rowtab.FIRST..l_rowtab.LAST
2399 LOOP
2400 hr_utility.trace('Row -'||l_rowtab(i));
2401 END LOOP;
2402 END IF;
2403 END IF;
2404
2405 END IF;
2406
2407 HXT_UTIL.DEBUG('Successful UPDATE hxt_det_hours_worked_f'); --HXT115
2408 EXCEPTION
2409 WHEN g_lookup_not_found THEN
2410 HXT_UTIL.DEBUG('Oops...g_lookup_not_found'); --HXT115
2411 p_sum_retcode := 3;
2412 RAISE g_lookup_not_found; --SIR517 PWM 18FEB00 Re-raise the exception for the calling procedure
2413 WHEN others THEN
2414 HXT_UTIL.DEBUG(sqlerrm); --HXT115
2415 HXT_UTIL.DEBUG('Oops...others'); --HXT115
2416 FND_MESSAGE.SET_NAME('HXT','HXT_39354_ERR_INS_PAYMX_INFO');
2417 FND_MESSAGE.SET_TOKEN('SQLERR', sqlerrm);
2418 Insert_Pay_Batch_Errors( p_values_rec.batch_id, 'VE', '', l_return);
2419 p_sum_retcode := 3;
2420 -- Bug 10143866
2421 hr_utility.trace(dbms_utility.format_error_backtrace);
2422
2423 RAISE g_error_ins_batch_lines; --SIR517 PWM 18FEB00 Re-raise the exception for the calling procedure
2424 END dtl_to_BEE;
2425
2426
2427 PROCEDURE sum_to_mix (p_batch_id IN NUMBER,
2428 p_time_period_id IN NUMBER,
2429 p_sum_retcode IN OUT NOCOPY NUMBER) IS
2430 PRAGMA AUTONOMOUS_TRANSACTION; --115.37
2431 CURSOR hxt_values_cur IS
2432 SELECT *
2433 FROM hxt_batch_values_v
2434 WHERE batch_id = p_batch_id ;
2435 CURSOR hxt_hours_cur IS
2436 SELECT *
2437 FROM hxt_batch_sum_hours_rollup_v
2438 WHERE batch_id = p_batch_id;
2439 CURSOR hxt_amounts_cur IS
2440 SELECT *
2441 FROM hxt_batch_sum_amounts_v
2442 WHERE batch_id = p_batch_id;
2443 l_values_rec hxt_values_cur%ROWTYPE;
2444 we_have_lines BOOLEAN;
2445 l_return NUMBER;
2446 l_batch_sequence PAY_BATCH_LINES.BATCH_SEQUENCE%TYPE;
2447 BEGIN
2448 p_sum_retcode := 0;
2449
2450 -- Bug 8888777
2451 -- Added the following call to pick up the upgrade status during the run.
2452
2453 g_iv_upgrade := get_upgrade_status(p_batch_id);
2454 --
2455 -- If profile value set to 'Y', sum hours only to send to BEE
2456 --
2457 IF (nvl(fnd_profile.value('HXT_ROLLUP_BATCH_HOURS'),'N') = 'Y') THEN
2458 OPEN hxt_hours_cur;
2459 FETCH hxt_hours_cur into l_values_rec;
2460 IF hxt_hours_cur%FOUND THEN
2461 we_have_lines := TRUE;
2462 ELSE
2463 we_have_lines := FALSE;
2464 END IF;
2465 --
2466 -- Otherwise, do not sum hours, send hours and amounts to BEE
2467 --
2468 ELSE
2469 OPEN hxt_values_cur;
2470 FETCH hxt_values_cur into l_values_rec;
2471 IF hxt_values_cur%FOUND THEN
2472 we_have_lines := TRUE;
2473 ELSE
2474 we_have_lines := FALSE;
2475 END IF;
2476 END IF;
2477 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(l_values_rec.batch_id);
2478 WHILE we_have_lines AND p_sum_retcode = 0 LOOP
2479 dtl_to_BEE(l_values_rec, p_sum_retcode,l_batch_sequence);
2480 --
2481 -- If profile value set to 'Y', then we're sending sum of hours only
2482 --
2483 IF (nvl(fnd_profile.value('HXT_ROLLUP_BATCH_HOURS'),'N') = 'Y') THEN
2484 FETCH hxt_hours_cur into l_values_rec;
2485 IF hxt_hours_cur%FOUND THEN
2486 we_have_lines := TRUE;
2487 l_batch_sequence := l_batch_sequence + 1;
2488 ELSE
2489 we_have_lines := FALSE;
2490 END IF;
2491 --
2492 -- Otherwise, we're sending hours and amounts
2493 --
2494 ELSE
2495 FETCH hxt_values_cur into l_values_rec;
2496 IF hxt_values_cur%FOUND THEN
2497 we_have_lines := TRUE;
2498 l_batch_sequence := l_batch_sequence + 1;
2499 ELSE
2500 we_have_lines := FALSE;
2501 END IF;
2502 END IF;
2503 END LOOP;
2504 --
2505 -- Close appropriate cursor
2506 --
2507 IF (nvl(fnd_profile.value('HXT_ROLLUP_BATCH_HOURS'),'N') = 'Y') THEN
2508 CLOSE hxt_hours_cur;
2509 ELSE
2510 CLOSE hxt_values_cur;
2511 END IF;
2512 --
2513 -- If profile value is set to 'Y', now send amounts only to BEE
2514 --
2515 IF (nvl(fnd_profile.value('HXT_ROLLUP_BATCH_HOURS'),'N') = 'Y') THEN
2516 OPEN hxt_amounts_cur;
2517 FETCH hxt_amounts_cur into l_values_rec;
2518 IF hxt_amounts_cur%FOUND THEN
2519 we_have_lines := TRUE;
2520 ELSE
2521 we_have_lines := FALSE;
2522 END IF;
2523 l_batch_sequence := l_batch_sequence + 1;
2524 WHILE we_have_lines AND p_sum_retcode = 0 LOOP
2525 dtl_to_BEE(l_values_rec, p_sum_retcode,l_batch_sequence);
2526 -- dtl_to_BEE(l_values_rec, p_sum_retcode);
2527 FETCH hxt_amounts_cur into l_values_rec;
2528 IF hxt_amounts_cur%FOUND THEN
2529 we_have_lines := TRUE;
2530 l_batch_sequence := l_batch_sequence + 1;
2531 ELSE
2532 we_have_lines := FALSE;
2533 END IF;
2534 END LOOP;
2535 CLOSE hxt_amounts_cur;
2536 END IF;
2537 HXT_RETRO_VAL.Mark_Rows_Complete(p_batch_id);
2538
2539 -- Bug 9494444
2540 -- New call for Retrieval Dashboard's detail section
2541 -- Need to pick up line by line details of this batch and
2542 -- update in the Retrieval Dashboard tables.
2543 snap_retrieval_details(p_batch_id);
2544
2545 COMMIT; --115.37
2546 HXT_UTIL.DEBUG('Successful COMMIT');
2547 EXCEPTION
2548 WHEN g_lookup_not_found THEN --SIR517 PWM 18FEB00
2549 ROLLBACK; --115.37
2550 HXT_UTIL.DEBUG('Oops...g_lookup_not_found in procedure sum_to_mix');
2551 p_sum_retcode := 3;
2552 raise g_lookup_not_found ; --propogate to the calling procedure
2553 WHEN g_error_ins_batch_lines THEN --SIR517 PWM 18FEB00
2554 ROLLBACK; --115.37
2555 HXT_UTIL.DEBUG('Error attempting to insert paymix information');
2556 FND_MESSAGE.SET_NAME('HXT','HXT_39354_ERR_INS_PAYMX_INFO');
2557 FND_MESSAGE.SET_TOKEN('SQLERR',sqlerrm);
2558 Insert_Pay_Batch_Errors( p_batch_id, 'VE', '', l_return);
2559 HXT_UTIL.DEBUG(' back from calling insert_pay_batch_errors');
2560 p_sum_retcode := 3;
2561 raise g_error_ins_batch_lines ;
2562 WHEN others THEN
2563 ROLLBACK; --115.37
2564 HXT_UTIL.DEBUG(sqlerrm);
2565 HXT_UTIL.DEBUG('Oops...others');
2566 FND_MESSAGE.SET_NAME('HXT','HXT_39354_ERR_INS_PAYMX_INFO');
2567 FND_MESSAGE.SET_TOKEN('SQLERR', sqlerrm);
2568 Insert_Pay_Batch_Errors( p_batch_id, 'VE', '', l_return);
2569 p_sum_retcode := 3;
2570 -- Bug 10143866
2571 hr_utility.trace(dbms_utility.format_error_backtrace);
2572
2573 END sum_to_mix;
2574 --------------------------------------------------------------------------------
2575 PROCEDURE Transfer_To_Payroll( p_batch_id IN NUMBER
2576 , p_payroll_id IN VARCHAR2
2577 , p_batch_status IN VARCHAR2
2578 , p_ref_num IN VARCHAR2
2579 , p_process_mode IN VARCHAR2
2580 , p_pay_retcode IN OUT NOCOPY NUMBER) IS
2581 CURSOR cur_sess_date IS
2582 SELECT fnd_date.date_to_chardate(end_date) end_date --SIR149 --FORMS60
2583 FROM per_time_periods
2584 WHERE time_period_id = g_time_period_id;
2585 l_req_id NUMBER;
2586 l_errbuf VARCHAR2(80);
2587 l_retcode NUMBER;
2588 l_session_date VARCHAR2(30);
2589 l_to_batch NUMBER := p_batch_id;
2590 l_num NUMBER;
2591 l_process_mode VARCHAR2(80);
2592 l_return NUMBER; -- SPR C352 by BC
2593 l_message VARCHAR2(256);
2594 g_pipe_session VARCHAR2(30);
2595 get_next_item BOOLEAN default TRUE;
2596 kount NUMBER default 0;
2597 BEGIN
2598 -- Clear retcode
2599 p_pay_retcode := 0;
2600 l_process_mode := p_process_mode;
2601 -- Get session date
2602 --begin SPR C166
2603 IF g_time_period_id IS NULL THEN
2604 l_session_date := fnd_date.date_to_chardate(SYSDATE); --SIR149 --FORMS60
2605 ELSE
2606 OPEN cur_sess_date;
2607 FETCH cur_sess_date INTO l_session_date;
2608 CLOSE cur_sess_date;
2609 END IF;
2610 /*--DEBUG ONLY BEGIN
2611
2612 select 'PIPE' || userenv('sessionid')
2613 into g_pipe_session
2614 from dual;
2615 if g_debug then
2616 hr_utility.set_location('PAY_US_PDT_PROCESS.TRANSFER_TO_PAYROLL', 1);
2617 end if;
2618 --DEBUG ONLY END*/
2619 EXCEPTION
2620 WHEN OTHERS THEN
2621 FND_MESSAGE.SET_NAME('HXT','HXT_39357_BATCH_ERR');
2622 FND_MESSAGE.SET_TOKEN('MESSAGE', l_errbuf);
2623 FND_MESSAGE.SET_TOKEN('SQLERR', sqlerrm);
2624 Insert_Pay_Batch_Errors(
2625 p_batch_id,
2626 'VE',
2627 '',
2628 l_return);
2629 p_pay_retcode := 3;
2630 -- Bug 10143866
2631 hr_utility.trace(dbms_utility.format_error_backtrace);
2632
2633 END transfer_to_payroll;
2634 ---------------------------------------------
2635 FUNCTION Call_Gen_Error( p_batch_id IN NUMBER
2636 ,p_location IN VARCHAR2
2637 ,p_error_text IN VARCHAR2
2638 ,p_oracle_error_text IN VARCHAR2 default NULL )
2639 RETURN NUMBER IS
2640 -- calls error processing procedure --
2641 BEGIN
2642 HXT_UTIL.GEN_EXCEPTION
2643 (p_location||'. Batch Id = '||to_char(p_batch_id)
2644 ,p_error_text
2645 ,p_oracle_error_text
2646 ,null);
2647 RETURN 2;
2648 END call_gen_error;
2649 -- begin SPR C352 by BC-----------------------------
2650 PROCEDURE Del_Prior_Errors( p_batch_id NUMBER ) IS
2651 -- delete all prior batch level errors
2652 BEGIN
2653 NULL;
2654 END del_prior_errors;
2655 ---------------------------------------------------------------------
2656 PROCEDURE Set_batch_status(p_date_earned DATE,
2657 p_batch_id NUMBER,
2658 p_status VARCHAR2 )IS
2659 BEGIN
2660 IF (p_status = 'VT' AND p_date_earned IS NOT NULL) THEN
2661 UPDATE hxt_batch_states
2662 SET date_earned = p_date_earned
2663 WHERE batch_id = p_batch_id;
2664 END IF;
2665 UPDATE hxt_batch_states
2666 SET status = p_status
2667 WHERE batch_id = p_batch_id;
2668 --COMMIT;
2669 END Set_batch_status;
2670 ------------------------------------------------------------------
2671 FUNCTION Get_Transfer_Batch_Status(p_batch_id NUMBER,
2672 p_batch_status OUT NOCOPY VARCHAR2 )
2673 RETURN NUMBER IS
2674 l_batch_status VARCHAR2(10);
2675 BEGIN
2676 SELECT status
2677 INTO l_batch_status
2678 FROM hxt_batch_states
2679 WHERE batch_id = p_batch_id;
2680 p_batch_status := l_batch_status;
2681 IF l_batch_status in ('E','VE') THEN
2682 return(2);
2683 ELSIF l_batch_status in ('VW','TW') THEN
2684 return(1);
2685 ELSE
2686 return(0);
2687 END IF;
2688 EXCEPTION
2689 WHEN OTHERS THEN
2690 -- Bug 10143866
2691 hr_utility.trace(dbms_utility.format_error_backtrace);
2692 RETURN(3);
2693
2694
2695 END Get_Transfer_Batch_Status;
2696 -----------------------------------------------------------
2697 PROCEDURE rollback_PayMIX(p_batch_id IN NUMBER,
2698 p_time_period_id IN NUMBER,
2699 p_rollback_retcode OUT NOCOPY NUMBER) IS
2700 l_return NUMBER DEFAULT 0;
2701 l_line_id NUMBER DEFAULT NULL;
2702 l_sql_error VARCHAR2(4000) DEFAULT NULL;
2703 l_new_batch NUMBER DEFAULT 0;
2704 l_payroll_id NUMBER;
2705 l_business_group_id NUMBER;
2706 l_batch_name VARCHAR2(30);
2707 l_batch_reference VARCHAR2(30);
2708 l_tim_id NUMBER;
2709 l_batch_created boolean;
2710 l_index BINARY_INTEGER := 0;
2711
2712
2713 TYPE retro_rec IS RECORD
2714 (retro_batch_id NUMBER,
2715 retro_ovn NUMBER);
2716
2717 TYPE retro_tab IS TABLE OF retro_rec INDEX BY BINARY_INTEGER;
2718 l_retro_tab retro_tab;
2719
2720 -- Obtain start and end dates.
2721 -- If period not specified by user or period is unavailable,
2722 -- process all dates in the batch
2723 --
2724 -- Delete PayMIX cursor
2725 -- only batch lines for the time period selected by the user, all if
2726 -- none specified
2727 --
2728 --HXT11 not needed. declared in FOR loop batch_line_rec batch_line_cur%ROWTYPE;
2729 CURSOR batch_line_cur IS
2730 SELECT line.assignment_number, line.effective_date, line.batch_line_id,line.OBJECT_VERSION_NUMBER
2731 FROM pay_batch_lines line
2732 WHERE line.batch_id = p_batch_id;
2733 cursor c_get_batch_lines(p_batch_id number) is
2734 select batch_line_id,object_version_number
2735 from pay_batch_lines
2736 where batch_id = p_batch_id;
2737 -- Bug 13388573
2738 /*
2739 cursor c_get_retro_batch_ids(p_batch_id number) is
2740 SELECT distinct(hrw.retro_batch_id) retro_batch_id,pbh.object_version_number
2741 FROM hxt_det_hours_worked_f hrw,
2742 hxt_timecards_f tim,
2743 pay_batch_headers pbh
2744 WHERE hrw.tim_id = tim.id
2745 AND tim.batch_id = pbh.batch_id
2746 AND tim.batch_id = p_batch_id
2747 AND hrw.retro_batch_id IS NOT NULL;
2748 */
2749
2750 cursor c_get_retro_batch_ids(p_batch_id number) is
2751 SELECT distinct(hrw.retro_batch_id) retro_batch_id,pbh.object_version_number,
2752 pbh.batch_status
2753 FROM hxt_det_hours_worked_f hrw,
2754 hxt_timecards_f tim,
2755 pay_batch_headers pbh
2756 WHERE hrw.tim_id = tim.id
2757 AND hrw.retro_batch_id = pbh.batch_id
2758 AND tim.batch_id = p_batch_id
2759 AND hrw.retro_batch_id IS NOT NULL;
2760
2761 CURSOR c_get_batch_ovn(p_batch_id number) is
2762 select object_version_number
2763 from pay_batch_headers
2764 where batch_id = p_batch_id;
2765 l_batch_ovn pay_batch_headers.object_version_number%type;
2766 l_batch_line_id pay_batch_lines.batch_line_id%type;
2767 l_line_ovn pay_batch_headers.object_version_number%type;
2768 l_num VARCHAR2(5);
2769 BEGIN
2770 -- if no time period is specified, delete all PayMIX entries
2771 IF p_time_period_id IS NULL THEN
2772 -- Delete PayMIX
2773 --
2774
2775 IF g_debug
2776 THEN
2777 hr_utility.trace('Time period is null ');
2778 hr_utility.trace('Getting Batch lines for batch id '||p_batch_id);
2779 END IF;
2780
2781 FND_FILE.put_line(FND_FILE.log,'Processing Batch id '||p_batch_id);
2782
2783 for l_rec in c_get_batch_lines(p_batch_id)
2784 loop
2785 FND_FILE.put_line(FND_FILE.log,'=> Processing Batch Line id '||l_rec.batch_line_id);
2786 hr_utility.trace('=>Processing Batch Line id '||l_rec.batch_line_id);
2787 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
2788 (p_batch_line_id => l_rec.batch_line_id
2789 ,p_object_version_number => l_rec.object_version_number
2790 );
2791 end loop;
2792 -- Update Batch status
2793 --
2794 --BEGIN GLOBAL - we no longer need to manipulate PayMIX/BEE batch status;
2795 -- but we still need to manipulate HXT_BATCH_STATES
2796 UPDATE hxt_batch_states
2797 SET status = 'H'
2798 WHERE batch_id = p_batch_id;
2799
2800 IF g_debug
2801 THEN
2802 hr_utility.trace( 'updated hxt_batch_states for this batch ');
2803 END IF;
2804 -- step 1 - delete retro_batch lines for any timecards in this (regular) batch.
2805 --Then we loop through the cursor and delete the batch lines and batches as follows.
2806 FND_FILE.put_line(FND_FILE.log,'=>Picking Retro batches for '||p_batch_id);
2807 for l_rec in c_get_retro_batch_ids(p_batch_id) loop
2808
2809 FND_FILE.put_line(FND_FILE.log,'=> Retro Batch Id: '||l_rec.retro_batch_id);
2810 hr_utility.trace('=> Retro Batch Id: '||l_rec.retro_batch_id);
2811
2812 IF l_rec.batch_status IN ( 'T','V')
2813 THEN
2814 hr_utility.trace('Retro batch '||l_rec.retro_batch_id||' is in status T');
2815 FND_FILE.put_line(FND_file.log,'Retro batch '||l_rec.retro_batch_id||' is in status Transferred or Valid');
2816 FND_FILE.put_line(FND_file.log,'Retro batch '||l_rec.retro_batch_id||' should be Unprocessed to proceed further');
2817 RAISE g_rollback_error;
2818 END IF;
2819
2820 -- delete the batch lines
2821 for l_line_rec in c_get_batch_lines(l_rec.retro_batch_id) loop
2822 FND_FILE.put_line(FND_FILE.log,'=>------- Retro Line Id: '||l_line_rec.batch_line_id);
2823 hr_utility.trace('=>------- Retro Line Id: '||l_line_rec.batch_line_id);
2824 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
2825 (p_batch_line_id => l_line_rec.batch_line_id
2826 ,p_object_version_number => l_line_rec.object_version_number
2827 );
2828 end loop;
2829 -- delete the batch
2830 -- Bug 13388573
2831 -- Will delete the header later.
2832 /*
2833 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_header
2834 (p_batch_id => l_rec.retro_batch_id
2835 ,p_object_version_number => l_rec.object_version_number
2836 );
2837 */
2838 l_index := l_index + 1;
2839 l_retro_tab(l_index).retro_batch_id := l_rec.retro_batch_id;
2840 l_retro_tab(l_index).retro_ovn := l_rec.object_version_number;
2841
2842 end loop;
2843
2844 -- step 2 - delete retro_batch for any timecards in this (regular) batch.
2845 -- we can delete it because retro processing does not re-use
2846 -- batches.
2847 -- step 3 - set pay_status, retro_batch_id for any timecards in this
2848 -- (regular) batch.
2849 -- this will set the whole timecard back to 'P', effectively
2850 -- removing the retro nature of the timecard. when the timecard
2851 -- is resent to PayMIX, the detail rows with the most recent
2852 -- effective dates will be sent. This is what we want, because
2853 -- those rows will include any adjustments made by time entry
2854 -- personnel.
2855 --SIR424 PWM 17JAN00 Clear the pbl_line_id during the rollback
2856 UPDATE hxt_det_hours_worked_f
2857 SET retro_batch_id = NULL,
2858 pay_status = 'P',
2859 pbl_line_id = NULL
2860 WHERE rowid in (
2861 SELECT hrw.rowid
2862 FROM hxt_det_hours_worked_f hrw,
2863 hxt_timecards_f tim
2864 WHERE hrw.tim_id = tim.id
2865 AND tim.batch_id=p_batch_id);
2866
2867 -- Bug 13388573
2868 -- Deleting batch headers now.
2869
2870 -- Bug 14244623
2871 -- Added the IF condition below to avoid -6512 for empty tables.
2872 IF l_retro_tab.COUNT > 0
2873 THEN
2874
2875 FOR i IN l_retro_tab.FIRST..l_retro_tab.LAST
2876 LOOP
2877 FND_FILE.put_line(FND_FILE.log,'=>--Deleting header for Batch '||l_retro_tab(i).retro_batch_id);
2878 hr_utility.trace('=>--Deleting header for Batch '||l_retro_tab(i).retro_batch_id);
2879
2880 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_header
2881 (p_batch_id => l_retro_tab(i).retro_batch_id
2882 ,p_object_version_number => l_retro_tab(i).retro_ovn
2883 );
2884 END LOOP;
2885
2886 END IF;
2887
2888
2889 -- step 4 - SET the date_earned (Process Date) back to NULL on the hxt_batch_states table
2890 UPDATE hxt_batch_states
2891 SET date_earned = NULL
2892 WHERE batch_id = p_batch_id;
2893 --COMMIT;
2894 p_rollback_retcode := 0;
2895
2896 ELSE
2897 -- When time period is specified, a split batch could occurr.
2898 -- Create a new batch header id for timecards being rolled back
2899 -- This will allow the user to send any timecards remaining
2900 -- in PayMIX on to Payroll. Rollback timecards will always get
2901 -- a new batch id so they can be processed separately.
2902 l_new_batch := hxt_time_gen.Get_Next_Batch_Id;
2903 -- Update the corresponding timecards with the new batch number
2904 -- Delete batch lines for the time period selected by the user
2905 -- Delete any existing batch line errors
2906 --HXT11FOR batch_line_rec IN batch_line_cur(l_period_start_date, l_period_end_date) LOOP
2907 FOR batch_line_rec IN batch_line_cur LOOP --HXT11
2908 -- HXT_UTIL.DEBUG(batch_line_rec.assignment_number);--debug only HXT115
2909 -- HXT_UTIL.DEBUG(TO_CHAR(batch_line_rec.from_date));--debug only HXT115
2910 -- HXT_UTIL.DEBUG(TO_CHAR(batch_line_rec.to_date));--debug only HXT115
2911 -- Locate the Timecard associated with this particular batch line.
2912 -- we will create a new batch, the first time we enter this loop
2913 if not l_batch_created then
2914 SELECT business_group_id, batch_name, batch_reference
2915 INTO l_business_group_id, l_batch_name, l_batch_reference
2916 FROM pay_batch_headers
2917 WHERE batch_id = p_batch_id;
2918 PAY_BATCH_ELEMENT_ENTRY_API.create_batch_header
2919 (p_session_date => sysdate
2920 ,p_batch_name => l_batch_name
2921 ,p_batch_status => 'U'
2922 ,p_business_group_id => l_business_group_id
2923 ,p_action_if_exists => 'R'
2924 ,p_batch_reference => l_batch_reference
2925 ,p_batch_source => 'OTM'
2926 ,p_purge_after_transfer => 'N'
2927 ,p_reject_if_future_changes => 'N'
2928 ,p_batch_id => l_new_batch
2929 ,p_object_version_number => l_batch_ovn
2930 );
2931 l_batch_created := true;
2932 end if;
2933 --we lock the row corresponding to the batch line id and ovn
2934 pay_btl_shd.lck(p_batch_line_id => batch_line_rec.batch_line_id
2935 ,p_object_version_number => batch_line_rec.object_version_number
2936 );
2937 SELECT DISTINCT(hrw.tim_id)
2938 INTO l_tim_id
2939 FROM hxt_det_hours_worked hrw, per_assignments_f asm --C421
2940 WHERE asm.assignment_number = batch_line_rec.assignment_number
2941 AND hrw.assignment_id = asm.assignment_id;
2942 /* AND hrw.parent_id > 0; HXT111*/
2943 -- Set a new batch number for timecards with elements being
2944 -- deleted from PayMIX
2945 UPDATE hxt_timecards tim
2946 SET tim.batch_id = l_new_batch
2947 WHERE tim.batch_id = p_batch_id
2948 AND tim.id = l_tim_id;
2949 -- Delete actual PayMIX batch lines
2950 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
2951 (p_batch_line_id => batch_line_rec.batch_line_id
2952 ,p_object_version_number => batch_line_rec.object_version_number
2953 );
2954 END LOOP;
2955 -- Add the new batch header line for all rollback timecards
2956 OPEN c_get_batch_lines (p_batch_id);
2957 FETCH c_get_batch_lines INTO l_batch_line_id, l_line_ovn;
2958 IF c_get_batch_lines%NOTFOUND
2959 THEN
2960 -- no batch lines found. So we can delete this batch
2961 OPEN c_get_batch_ovn (p_batch_id);
2962 FETCH c_get_batch_ovn INTO l_batch_ovn;
2963 CLOSE c_get_batch_ovn;
2964 pay_batch_element_entry_api.delete_batch_header (
2965 p_batch_id => p_batch_id,
2966 p_object_version_number => l_batch_ovn
2967 );
2968 END IF;
2969 CLOSE c_get_batch_lines;
2970 END IF; -- timeperiod NULL
2971 --COMMIT;
2972 p_rollback_retcode := 0;
2973 EXCEPTION
2974 -- Bug 13388573
2975 -- Modified this section to do ROLLBACK first and then Raise instead of adjustng batch status.
2976 WHEN OTHERS THEN
2977 ROLLBACK;
2978 HXT_UTIL.DEBUG('Error: ' || sqlerrm); --HXT115
2979 l_sql_error := sqlerrm;
2980 FND_FILE.put_line(fnd_file.log,'Error: '||sqlerrm);
2981 p_rollback_retcode := 3;
2982 -- Bug 10143866
2983 hr_utility.trace(dbms_utility.format_error_backtrace);
2984 RAISE g_rollback_error;
2985
2986 END rollback_PayMIX;
2987 -------------------------------------------------------------------
2988 PROCEDURE Insert_Pay_Batch_Errors( p_batch_id IN NUMBER,
2989 p_error_level IN VARCHAR2,
2990 p_exception_details IN VARCHAR2,
2991 p_return_code OUT NOCOPY NUMBER)IS
2992 l_error_msg VARCHAR2(240);
2993 BEGIN
2994 IF p_exception_details IS NULL THEN
2995 l_error_msg := FND_MESSAGE.GET;
2996 FND_MESSAGE.CLEAR;
2997 ELSE
2998 l_ERROR_MSG := p_exception_details;
2999 END IF;
3000 HXT_UTIL.GEN_EXCEPTION
3001 ('Batch processing. Batch Id = '||to_char(p_batch_id)
3002 ,l_error_msg
3003 ,null
3004 ,null);
3005 --COMMIT;
3006 p_return_code := 0;
3007 EXCEPTION
3008 WHEN OTHERS THEN
3009 p_return_code := 1;
3010 -- Bug 10143866
3011 hr_utility.trace(dbms_utility.format_error_backtrace);
3012
3013 END Insert_Pay_Batch_Errors;
3014 ------------------------------------------------------------------
3015 -- end SPR C163, C166 by BC
3016 ------------------------------------------------------------------
3017 PROCEDURE CALL_GEN_ERROR2 ( p_batch_id IN NUMBER
3018 , p_tim_id IN NUMBER
3019 , p_hrw_id IN NUMBER
3020 , p_time_period_id IN NUMBER
3021 , p_error_msg IN VARCHAR2
3022 , p_loc IN VARCHAR2
3023 , p_sql_err IN VARCHAR2
3024 , p_TYPE IN VARCHAR2) IS --HXT11i1
3025 CURSOR tim_dates is
3026 SELECT effective_start_date,
3027 effective_end_date
3028 FROM HXT_TIMECARDS_X
3029 WHERE id = p_tim_id;
3030 l_eff_start DATE;
3031 l_eff_end DATE;
3032 BEGIN
3033 OPEN tim_dates;
3034 FETCH tim_dates into l_eff_start, l_eff_end;
3035 if tim_dates%FOUND then
3036 HXT_UTIL.GEN_ERROR(p_batch_id
3037 , p_tim_id
3038 , p_hrw_id
3039 , p_time_period_id
3040 , p_error_msg
3041 , p_loc
3042 , p_sql_err
3043 , l_eff_start
3044 , l_eff_end
3045 , p_type); --HXT11i1
3046 END IF;
3047 CLOSE tim_dates;
3048 END call_gen_error2;
3049
3050 -- Bug 8888777
3051 -- Below function added to pick up the upgrade status.
3052
3053 FUNCTION get_upgrade_status(p_batch_id IN NUMBER)
3054 RETURN VARCHAR2
3055 IS
3056
3057 CURSOR get_bg_id
3058 IS SELECT business_group_id
3059 FROM pay_batch_headers
3060 WHERE batch_id = p_batch_id;
3061
3062 l_bg_id NUMBER;
3063
3064 BEGIN
3065 OPEN get_bg_id;
3066 FETCH get_bg_id INTO l_bg_id;
3067 CLOSE get_bg_id;
3068 pay_core_utils.get_upgrade_status(l_bg_id,'BEE_IV_UPG',g_IV_UPGRADE);
3069
3070 IF g_iv_upgrade = 'Y'
3071 THEN
3072 g_IV_format := 'Y';
3073 ELSE
3074 g_IV_format := 'N';
3075 END IF;
3076 RETURN g_iv_upgrade;
3077
3078 END get_upgrade_status;
3079
3080 -- Bug 9494444
3081 -- Added this new procedure to snap the details of this
3082 -- batch upto lines in pay_batch_lines to the tables for
3083 -- recording this for Dashboard.
3084
3085 PROCEDURE snap_retrieval_details(p_batch_id IN NUMBER)
3086 IS
3087
3088
3089 -- Datatypes
3090 TYPE VARCHARTAB IS TABLE OF VARCHAR2(100);
3091 TYPE NUMBERTAB IS TABLE OF NUMBER;
3092 TYPE DATETAB IS TABLE OF DATE;
3093
3094 resource_id_tab NUMBERTAB;
3095 time_building_block_id_tab NUMBERTAB;
3096 approval_status_tab VARCHARTAB;
3097 start_time_tab DATETAB;
3098 stop_time_tab DATETAB;
3099 org_id_tab NUMBERTAB;
3100 business_group_id_tab NUMBERTAB;
3101 timecard_id_tab NUMBERTAB;
3102 app_set_id_tab NUMBERTAB;
3103 attribute1_tab VARCHARTAB;
3104 attribute2_tab VARCHARTAB;
3105 attribute3_tab VARCHARTAB;
3106 measure_tab NUMBERTAB;
3107 object_version_number_tab NUMBERTAB;
3108 old_ovn_tab NUMBERTAB;
3109 old_measure_tab NUMBERTAB;
3110 old_attribute1_tab VARCHARTAB;
3111 old_attribute2_tab VARCHARTAB;
3112 old_attribute3_tab VARCHARTAB;
3113 pbl_id_tab NUMBERTAB;
3114 retro_pbl_id_tab NUMBERTAB;
3115 old_pbl_id_tab NUMBERTAB;
3116 request_id_tab NUMBERTAB;
3117 old_request_id_tab NUMBERTAB;
3118 batch_id_tab NUMBERTAB;
3119 retro_batch_id_tab NUMBERTAB;
3120 old_batch_id_tab NUMBERTAB;
3121 rowid_tab VARCHARTAB;
3122
3123 -- Bug 12919783
3124 -- Added new tables for additional columns
3125 hxt_measure_tab NUMBERTAB;
3126 hxt_att1_tab NUMBERTAB;
3127 hxt_att2_tab NUMBERTAB;
3128 hxt_att3_tab NUMBERTAB;
3129 hxt_request_tab NUMBERTAB;
3130
3131 -- To pick up each timecard in the batch.
3132 CURSOR get_timecards(p_batch_id IN NUMBER)
3133 IS SELECT id
3134 FROM hxt_timecards_f
3135 WHERE batch_id = p_batch_id;
3136
3137
3138 -- To pick up the individual details
3139
3140 -- Bug 12919783
3141 -- Added new columns to take care of Rollback Batches from BEE process
3142
3143 -- Bug 14267796
3144 -- Added Application Set in the SELECTed columns list.
3145
3146 CURSOR get_ret_details(p_tim_id IN NUMBER,
3147 p_batch_id IN NUMBER)
3148 IS
3149 SELECT /*+ INDEX(det HXT_DET_HOURS_WORKED_F_SUM_FK) */
3150 ret.resource_id,
3151 ret.time_building_block_id,
3152 ret.approval_status,
3153 ret.start_time,
3154 ret.stop_time,
3155 ret.org_id,
3156 ret.business_group_id,
3157 ret.timecard_id,
3158 ret.application_set_id,
3159 det.element_type_id,
3160 ret.attribute2,
3161 ret.attribute3,
3162 det.hours,
3163 ret.object_version_number,
3164 ret.old_ovn,
3165 ret.old_measure,
3166 ret.old_attribute1,
3167 ret.old_attribute2,
3168 ret.old_attribute3,
3169 det.pbl_line_id,
3170 ret.retro_pbl_id,
3171 ret.old_pbl_id,
3172 FND_GLOBAL.conc_request_id,
3173 ret.old_request_id,
3174 p_batch_id,
3175 ret.retro_batch_id,
3176 ret.old_batch_id,
3177 ROWIDTOCHAR(ret.rowid),
3178 ret.measure,
3179 ret.attribute1,
3180 ret.attribute2,
3181 ret.attribute3,
3182 ret.request_id
3183 FROM hxt_sum_hours_worked_f sum,
3184 hxt_det_hours_worked_f det,
3185 hxc_ret_pay_latest_details ret
3186 WHERE sum.tim_id = p_tim_id
3187 AND sum.id = det.parent_id
3188 AND ret.time_building_block_id = sum.time_building_block_id
3189 AND ret.object_version_number = sum.time_building_block_ovn;
3190
3191
3192 l_tim_id NUMBER;
3193
3194 BEGIN
3195
3196 OPEN get_timecards(p_batch_id);
3197 LOOP
3198 -- Pick up the timecards.
3199 FETCH get_timecards INTO l_tim_id;
3200 EXIT WHEN get_timecards%NOTFOUND;
3201
3202 -- For each timecard, pick up the details.
3203 -- The cursor is built in such a way that if there are multiple
3204 -- details per tbb id- OVN combination in hxt_det_hours_worked_f
3205 -- they get picked up multiple times.
3206 -- Eg. hxc_ret_pay_latest_details shows 12 hrs Reg
3207 -- In OTLR this is 8 hrs Reg and 4 hrs Overtime.
3208 -- So we pick up the 12 hrs Reg, delete the record
3209 -- and insert 8 hrs Reg and 4 hrs Overtime.
3210 OPEN get_ret_details(l_tim_id,p_batch_id);
3211
3212 -- Bug 12919783
3213 -- Additional columns added
3214
3215 FETCH get_ret_details BULK COLLECT INTO
3216 resource_id_tab,
3217 time_building_block_id_tab,
3218 approval_status_tab,
3219 start_time_tab,
3220 stop_time_tab,
3221 org_id_tab,
3222 business_group_id_tab,
3223 timecard_id_tab,
3224 app_set_id_tab, -- Bug 14267796
3225 attribute1_tab,
3226 attribute2_tab,
3227 attribute3_tab,
3228 measure_tab,
3229 object_version_number_tab,
3230 old_ovn_tab,
3231 old_measure_tab,
3232 old_attribute1_tab,
3233 old_attribute2_tab,
3234 old_attribute3_tab,
3235 pbl_id_tab,
3236 retro_pbl_id_tab,
3237 old_pbl_id_tab,
3238 request_id_tab,
3239 old_request_id_tab,
3240 batch_id_tab,
3241 retro_batch_id_tab,
3242 old_batch_id_tab,
3243 rowid_tab,
3244 hxt_measure_tab,
3245 hxt_att1_tab,
3246 hxt_att2_tab,
3247 hxt_att3_tab,
3248 hxt_request_tab;
3249
3250 -- Delete the entries already there.
3251 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
3252 DELETE FROM hxc_ret_pay_latest_details
3253 WHERE ROWID = CHARTOROWID(rowid_tab(i));
3254
3255 -- Insert the new entries.
3256
3257 -- Bug 12919783
3258 -- New columns added
3259 -- These columns would record the original values from SS/TK and will be
3260 -- used in Rollback Batches from BEE process.
3261
3262 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
3263 INSERT INTO hxc_ret_pay_latest_details
3264 ( resource_id,
3265 time_building_block_id,
3266 approval_status,
3267 start_time,
3268 stop_time,
3269 org_id,
3270 business_group_id,
3271 timecard_id,
3272 application_set_id, -- Bug 14267796
3273 attribute1,
3274 attribute2,
3275 attribute3,
3276 measure,
3277 object_version_number,
3278 old_ovn,
3279 old_measure,
3280 old_attribute1,
3281 old_attribute2,
3282 old_attribute3,
3283 pbl_id,
3284 retro_pbl_id,
3285 old_pbl_id,
3286 request_id,
3287 old_request_id,
3288 batch_id,
3289 retro_batch_id,
3290 old_batch_id,
3291 hxt_element_id,
3292 hxt_attribute2,
3293 hxt_attribute3,
3294 hxt_measure,
3295 hxt_request_id,
3296 hxt_batch_id)
3297 VALUES ( resource_id_tab(i),
3298 time_building_block_id_tab(i),
3299 approval_status_tab(i),
3300 start_time_tab(i),
3301 stop_time_tab(i),
3302 org_id_tab(i),
3303 business_group_id_tab(i),
3304 timecard_id_tab(i),
3305 app_set_id_tab(i), -- Bug 14267796
3306 attribute1_tab(i),
3307 attribute2_tab(i),
3308 attribute3_tab(i),
3309 measure_tab(i),
3310 object_version_number_tab(i),
3311 old_ovn_tab(i),
3312 old_measure_tab(i),
3313 old_attribute1_tab(i),
3314 old_attribute2_tab(i),
3315 old_attribute3_tab(i),
3316 pbl_id_tab(i),
3317 retro_pbl_id_tab(i),
3318 old_pbl_id_tab(i),
3319 request_id_tab(i),
3320 old_request_id_tab(i),
3321 batch_id_tab(i),
3322 retro_batch_id_tab(i),
3323 old_batch_id_tab(i),
3324 hxt_att1_tab(i),
3325 hxt_att2_tab(i),
3326 hxt_att3_tab(i),
3327 hxt_measure_tab(i),
3328 hxt_request_tab(i),
3329 p_batch_id
3330 );
3331
3332 COMMIT;
3333
3334 CLOSE get_ret_details;
3335
3336 END LOOP;
3337 CLOSE get_timecards;
3338
3339 END snap_retrieval_details;
3340
3341 --begin
3342
3343 END HXT_batch_process;
[Home] [Help]