[Home] [Help]
1: PACKAGE BODY HXT_BATCH_PROCESS AS
2: /* $Header: hxtbat.pkb 120.12.12010000.1 2008/07/25 09:47:57 appldev ship $ */
3: g_debug boolean := hr_utility.debug_enabled;
4: g_time_period_id NUMBER := NULL;
5: g_lookup_not_found EXCEPTION;
6: g_error_ins_batch_lines EXCEPTION; --SIR517 PWM 18FEB00
7: FUNCTION Call_Gen_Error(p_batch_id IN NUMBER
68: l_parent_batch_name PAY_BATCH_HEADERS.BATCH_NAME%TYPE;
69: l_batch_type VARCHAR2(10);
70:
71: BEGIN
72: g_debug := hr_utility.debug_enabled;
73:
74: IF g_debug THEN
75: l_proc := 'hxt_batch_process.merge_batches';
76: hr_utility.set_location('Entering: '||l_proc, 10);
72: g_debug := hr_utility.debug_enabled;
73:
74: IF g_debug THEN
75: l_proc := 'hxt_batch_process.merge_batches';
76: hr_utility.set_location('Entering: '||l_proc, 10);
77: END IF;
78:
79: l_batch_name := p_merge_batch_name;
80:
94:
95: EXIT WHEN NOT p_merge_batches.exists(l_loop_index1);
96:
97: IF g_debug THEN
98: hr_utility.set_location('Inside merge_batches proc loop: '||p_merge_batches(l_loop_index1).batch_id, 20);
99: END IF;
100:
101: l_temp_batch_upd_id := null;
102: l_batch_type := null;
105:
106: IF l_valid_batch_id is null THEN
107: l_valid_batch_name := l_batch_name;
108: IF g_debug THEN
109: hr_utility.set_location('Before creating new valid batch header', 30);
110: END IF;
111: pay_batch_element_entry_api.create_batch_header (p_session_date => sysdate,
112: p_batch_name => l_valid_batch_name,
113: p_business_group_id => p_bus_group_id,
118: p_batch_id => l_valid_batch_id,
119: p_object_version_number => l_object_version_number
120: ); /* For creating new batch for valid Tcs. */
121: IF g_debug THEN
122: hr_utility.set_location('After creating new valid batch header: '||l_valid_batch_id, 40);
123: END IF;
124: l_valid_batch_name := l_valid_batch_name||l_string2||to_char(l_valid_batch_id);
125: pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
126: p_batch_id => l_valid_batch_id,
130:
131: fnd_file.put_line (fnd_file.log, 'Successful Batch Name: '||l_valid_batch_name);
132:
133: IF g_debug THEN
134: hr_utility.set_location('After updating valid batch name: '||l_valid_batch_name, 50);
135: END IF;
136: BEGIN
137: UPDATE hxt_batch_states
138: SET status = 'VV'
138: SET status = 'VV'
139: WHERE batch_id = l_valid_batch_id; /* For updating the status of the newly created batch */
140: END;
141: IF g_debug THEN
142: hr_utility.set_location('After updating valid batch status in hxt_batch_states', 60);
143: END IF;
144: END IF;
145:
146: l_temp_batch_upd_id := l_valid_batch_id;
149:
150: IF l_warning_batch_id is null THEN
151: l_warning_batch_name := l_batch_name||'_W';
152: IF g_debug THEN
153: hr_utility.set_location('Before creating new warning batch header', 70);
154: END IF;
155: pay_batch_element_entry_api.create_batch_header (p_session_date => sysdate,
156: p_batch_name => l_warning_batch_name,
157: p_business_group_id => p_bus_group_id,
162: p_batch_id => l_warning_batch_id,
163: p_object_version_number => l_object_version_number
164: ); /* For creating new batch for warning Tcs. */
165: IF g_debug THEN
166: hr_utility.set_location('After creating new warning batch header: '||l_warning_batch_id, 80);
167: END IF;
168: l_warning_batch_name := l_warning_batch_name||l_string2||to_char(l_warning_batch_id);
169: pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
170: p_batch_id => l_warning_batch_id,
174:
175: fnd_file.put_line (fnd_file.log, 'Warning Batch Name: '||l_warning_batch_name);
176:
177: IF g_debug THEN
178: hr_utility.set_location('After updating warning batch name: '||l_warning_batch_name, 90);
179: END IF;
180: BEGIN
181: UPDATE hxt_batch_states
182: SET status = 'VW'
182: SET status = 'VW'
183: WHERE batch_id = l_warning_batch_id; /* For updating the status of the newly created batch */
184: END;
185: IF g_debug THEN
186: hr_utility.set_location('After updating warning batch status in hxt_batch_states', 100);
187: END IF;
188: END IF;
189:
190: l_temp_batch_upd_id := l_warning_batch_id;
194:
195: IF l_error_batch_id is null THEN
196: l_error_batch_name := l_batch_name||'_E';
197: IF g_debug THEN
198: hr_utility.set_location('Before creating new error batch header', 110);
199: END IF;
200: pay_batch_element_entry_api.create_batch_header (p_session_date => sysdate,
201: p_batch_name => l_error_batch_name,
202: p_business_group_id => p_bus_group_id,
207: p_batch_id => l_error_batch_id,
208: p_object_version_number => l_object_version_number
209: ); /* For creating new batch for erroded Tcs. */
210: IF g_debug THEN
211: hr_utility.set_location('after creating new error batch header: '||l_error_batch_id, 120);
212: END IF;
213: l_error_batch_name := l_error_batch_name||l_string2||to_char(l_error_batch_id);
214: pay_batch_element_entry_api.update_batch_header (p_session_date => sysdate,
215: p_batch_id => l_error_batch_id,
219:
220: fnd_file.put_line (fnd_file.log, 'Error Batch Name: '||l_error_batch_name);
221:
222: IF g_debug THEN
223: hr_utility.set_location('After updating error batch name: '||l_error_batch_name, 130);
224: END IF;
225: BEGIN
226: UPDATE hxt_batch_states
227: SET status = 'VE'
227: SET status = 'VE'
228: WHERE batch_id = l_error_batch_id; /* For updating the status of the newly created batch */
229: END;
230: IF g_debug THEN
231: hr_utility.set_location('After updating error batch status in hxt_batch_states', 140);
232: END IF;
233: END IF;
234:
235: l_temp_batch_upd_id := l_error_batch_id;
237:
238: END IF;
239:
240: IF g_debug THEN
241: hr_utility.set_location('Before updating TC reference: '||p_merge_batches(l_loop_index1).tc_id||
242: ' Mode: '||p_mode, 150);
243: END IF;
244:
245: IF p_mode = 'NR' THEN /* For updating the TC references of Non-Retro Batches to the newly created batch */
302:
303: END IF;
304:
305: IF g_debug THEN
306: hr_utility.set_location('After updating TC reference', 160);
307: END IF;
308:
309: l_loop_index1 := p_merge_batches.next(l_loop_index1);
310:
315: LOOP /* To loop through empty batches left behind and delete them */
316:
317: EXIT WHEN NOT p_del_empty_batches.exists(l_loop_index2);
318: IF g_debug THEN
319: hr_utility.set_location('Before deleting empty batches: '||p_del_empty_batches(l_loop_index2).batch_id||
320: ' ovn: '||p_del_empty_batches(l_loop_index2).batch_ovn, 170);
321: END IF;
322:
323: pay_batch_element_entry_api.delete_batch_header (p_batch_id => p_del_empty_batches(l_loop_index2).batch_id,
323: pay_batch_element_entry_api.delete_batch_header (p_batch_id => p_del_empty_batches(l_loop_index2).batch_id,
324: p_object_version_number => p_del_empty_batches(l_loop_index2).batch_ovn
325: );
326: IF g_debug THEN
327: hr_utility.set_location('After deleting empty batches', 180);
328: END IF;
329:
330: BEGIN
331: DELETE FROM hxt_batch_states
332: WHERE batch_id = p_del_empty_batches(l_loop_index2).batch_id;
333: END;
334:
335: IF g_debug THEN
336: hr_utility.set_location('After deleting empty batches from hxt_batch_states', 190);
337: END IF;
338:
339: l_loop_index2 := p_del_empty_batches.next(l_loop_index2);
340:
340:
341: END LOOP;
342:
343: IF g_debug THEN
344: hr_utility.set_location('Leaving: '||l_proc, 200);
345: END IF;
346:
347: END merge_batches;
348:
437:
438: /********Bug: 4620315 **********/
439:
440: BEGIN
441: g_debug :=hr_utility.debug_enabled;
442: if g_debug then
443: hr_utility.trace(p_bus_group_id);
444: hr_utility.trace(g_time_period_id);
445: hr_utility.trace(l_batch_requested);
439:
440: BEGIN
441: g_debug :=hr_utility.debug_enabled;
442: if g_debug then
443: hr_utility.trace(p_bus_group_id);
444: hr_utility.trace(g_time_period_id);
445: hr_utility.trace(l_batch_requested);
446: hr_utility.trace( p_ref_num);
447: end if;
440: BEGIN
441: g_debug :=hr_utility.debug_enabled;
442: if g_debug then
443: hr_utility.trace(p_bus_group_id);
444: hr_utility.trace(g_time_period_id);
445: hr_utility.trace(l_batch_requested);
446: hr_utility.trace( p_ref_num);
447: end if;
448:
441: g_debug :=hr_utility.debug_enabled;
442: if g_debug then
443: hr_utility.trace(p_bus_group_id);
444: hr_utility.trace(g_time_period_id);
445: hr_utility.trace(l_batch_requested);
446: hr_utility.trace( p_ref_num);
447: end if;
448:
449:
442: if g_debug then
443: hr_utility.trace(p_bus_group_id);
444: hr_utility.trace(g_time_period_id);
445: hr_utility.trace(l_batch_requested);
446: hr_utility.trace( p_ref_num);
447: end if;
448:
449:
450: l_date_earned := to_date(to_char(trunc(l_date_earned),'DD/MM/RRRR'),'DD/MM/RRRR');
531: /*** To record the empty batch details ***/
532:
533: IF p_merge_flag = '1' THEN
534: IF g_debug THEN
535: hr_utility.trace('Populating del_empty_batches record: '||'batchid: '||batch_rec.batch_id||
536: ' ovn '||batch_rec.object_version_number);
537: END IF;
538: l_cnt := NVL(l_del_empty_batches.LAST,0) +1;
539: l_del_empty_batches(l_cnt).batch_id := batch_rec.batch_id;
786: /*** To merge the batch TCs by calling 'merge_batches' procedure ***/
787:
788: IF p_merge_flag = '1' and p_process_mode = 'V' THEN
789: IF g_debug THEN
790: hr_utility.trace('Before calling merge_batches proc');
791: END IF;
792: merge_batches (p_merge_batch_name,
793: l_merge_batches,
794: l_del_empty_batches,
850: AND nvl(fcl.end_date_active, p_date);
851: BEGIN
852:
853: if g_debug then
854: hr_utility.set_location('convert_lookup',10);
855: end if;
856: HXT_UTIL.DEBUG('convert_lookup - code = '||p_lookup_code||'
857: type = '||p_lookup_type||' date = '||fnd_date.date_to_chardate(p_date_active));
858: IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL THEN
856: HXT_UTIL.DEBUG('convert_lookup - code = '||p_lookup_code||'
857: type = '||p_lookup_type||' date = '||fnd_date.date_to_chardate(p_date_active));
858: IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL THEN
859: if g_debug then
860: hr_utility.set_location('convert_lookup',20);
861: end if;
862: OPEN get_meaning_cur(p_lookup_code, p_lookup_type, p_date_active);
863: FETCH get_meaning_cur into l_meaning;
864: if g_debug then
861: end if;
862: OPEN get_meaning_cur(p_lookup_code, p_lookup_type, p_date_active);
863: FETCH get_meaning_cur into l_meaning;
864: if g_debug then
865: hr_utility.trace('l_meaning :'||l_meaning);
866: end if;
867: IF get_meaning_cur%NOTFOUND then
868: if g_debug then
869: hr_utility.set_location('convert_lookup',30);
865: hr_utility.trace('l_meaning :'||l_meaning);
866: end if;
867: IF get_meaning_cur%NOTFOUND then
868: if g_debug then
869: hr_utility.set_location('convert_lookup',30);
870: end if;
871: FND_MESSAGE.SET_NAME('HXT','HXT_39483_LOOKUP_NOT_FOUND');
872: FND_MESSAGE.SET_TOKEN('CODE', p_lookup_code);
873: FND_MESSAGE.SET_TOKEN('TYPE', p_lookup_type);
874: RAISE g_lookup_not_found;
875: END IF;
876: ELSE
877: if g_debug then
878: hr_utility.set_location('convert_lookup',40);
879: end if;
880: l_meaning := p_lookup_code;
881: if g_debug then
882: hr_utility.trace('l_meaning :'||l_meaning);
878: hr_utility.set_location('convert_lookup',40);
879: end if;
880: l_meaning := p_lookup_code;
881: if g_debug then
882: hr_utility.trace('l_meaning :'||l_meaning);
883: end if;
884: END IF;
885: if g_debug then
886: hr_utility.set_location('convert_lookup',50);
882: hr_utility.trace('l_meaning :'||l_meaning);
883: end if;
884: END IF;
885: if g_debug then
886: hr_utility.set_location('convert_lookup',50);
887: end if;
888: RETURN l_meaning;
889: END convert_lookup;
890: --
903: AND enabled_flag = 'Y'
904: AND p_date_active BETWEEN nvl(start_date_active, p_date_active)
905: AND nvl(end_date_active, p_date_active);
906: BEGIN
907: g_debug :=hr_utility.debug_enabled;
908: if g_debug then
909: hr_utility.set_location('get_lookup_code',10);
910: end if;
911: HXT_UTIL.DEBUG('get_lookup_ code for meaning = '||p_meaning||'
905: AND nvl(end_date_active, p_date_active);
906: BEGIN
907: g_debug :=hr_utility.debug_enabled;
908: if g_debug then
909: hr_utility.set_location('get_lookup_code',10);
910: end if;
911: HXT_UTIL.DEBUG('get_lookup_ code for meaning = '||p_meaning||'
912: type = '||'NAME_TRANSLATIONS'||' date = '||fnd_date.date_to_chardate(p_date_active));
913: if g_debug then
910: end if;
911: HXT_UTIL.DEBUG('get_lookup_ code for meaning = '||p_meaning||'
912: type = '||'NAME_TRANSLATIONS'||' date = '||fnd_date.date_to_chardate(p_date_active));
913: if g_debug then
914: hr_utility.trace('p_meaning :'||p_meaning);
915: end if;
916: IF p_meaning IS NOT NULL THEN
917: if g_debug then
918: hr_utility.set_location('get_lookup_code',20);
914: hr_utility.trace('p_meaning :'||p_meaning);
915: end if;
916: IF p_meaning IS NOT NULL THEN
917: if g_debug then
918: hr_utility.set_location('get_lookup_code',20);
919: end if;
920: OPEN get_lookup_code_cur;
921: FETCH get_lookup_code_cur into l_lookup_code;
922: if g_debug then
919: end if;
920: OPEN get_lookup_code_cur;
921: FETCH get_lookup_code_cur into l_lookup_code;
922: if g_debug then
923: hr_utility.trace('l_lookup_code :'||l_lookup_code);
924: end if;
925: IF get_lookup_code_cur%NOTFOUND then
926: if g_debug then
927: hr_utility.set_location('get_lookup_code',30);
923: hr_utility.trace('l_lookup_code :'||l_lookup_code);
924: end if;
925: IF get_lookup_code_cur%NOTFOUND then
926: if g_debug then
927: hr_utility.set_location('get_lookup_code',30);
928: hr_utility.trace('get_lookup_code_cur NOT FOUND');
929: end if;
930: -- FND_MESSAGE.SET_NAME('HXT','HXT_39483_LOOKUP_NOT_FOUND');
931: -- FND_MESSAGE.SET_TOKEN('CODE', p_meaning); --SIR517 PWM 18FEB00
924: end if;
925: IF get_lookup_code_cur%NOTFOUND then
926: if g_debug then
927: hr_utility.set_location('get_lookup_code',30);
928: hr_utility.trace('get_lookup_code_cur NOT FOUND');
929: end if;
930: -- FND_MESSAGE.SET_NAME('HXT','HXT_39483_LOOKUP_NOT_FOUND');
931: -- FND_MESSAGE.SET_TOKEN('CODE', p_meaning); --SIR517 PWM 18FEB00
932: -- FND_MESSAGE.SET_TOKEN('TYPE', 'NAME_TRANSLATIONS'); --SIR517 PWM 18FEB00
934: null; -- This is done to fix bug 1761779 -- 17/May/2001
935: END IF;
936: ELSE
937: if g_debug then
938: hr_utility.set_location('get_lookup_code',40);
939: end if;
940: l_lookup_code := p_meaning;
941: if g_debug then
942: hr_utility.trace('p_meaning is null');
938: hr_utility.set_location('get_lookup_code',40);
939: end if;
940: l_lookup_code := p_meaning;
941: if g_debug then
942: hr_utility.trace('p_meaning is null');
943: hr_utility.trace('l_lookup_code:'||l_lookup_code);
944: end if;
945: END IF;
946: if g_debug then
939: end if;
940: l_lookup_code := p_meaning;
941: if g_debug then
942: hr_utility.trace('p_meaning is null');
943: hr_utility.trace('l_lookup_code:'||l_lookup_code);
944: end if;
945: END IF;
946: if g_debug then
947: hr_utility.trace('l_lookup_code :'||l_lookup_code);
943: hr_utility.trace('l_lookup_code:'||l_lookup_code);
944: end if;
945: END IF;
946: if g_debug then
947: hr_utility.trace('l_lookup_code :'||l_lookup_code);
948: hr_utility.set_location('get_lookup_code',50);
949: end if;
950: RETURN l_lookup_code;
951: END get_lookup_code;
944: end if;
945: END IF;
946: if g_debug then
947: hr_utility.trace('l_lookup_code :'||l_lookup_code);
948: hr_utility.set_location('get_lookup_code',50);
949: end if;
950: RETURN l_lookup_code;
951: END get_lookup_code;
952: -- Place OTM data into BEE values per input values
1020: lv_pbl_flag varchar2(1) := 'N';
1021: BEGIN
1022:
1023: if g_debug then
1024: hr_utility.set_location('dtl_to_BEE',10);
1025: end if;
1026: -- Initialize table
1027: FOR i in 1..15 LOOP
1028: hxt_value(i).sequence := null;
1078: hxt_value(13).lookup,
1079: hxt_value(14).lookup,
1080: hxt_value(15).lookup);
1081: if g_debug then
1082: hr_utility.set_location('dtl_to_BEE',20);
1083: end if;
1084: -- Place OTM data into BEE values per input values
1085: HXT_UTIL.DEBUG('Putting OTM data into BEE values per input values'); --HXT115
1086: --
1108: -- processing the further logic on the screen value for the input values.
1109: --
1110: lv_pbl_flag := 'N';
1111: if g_debug then
1112: hr_utility.set_location('dtl_to_BEE',30);
1113: hr_utility.trace('hxt_value_name_'||to_char(i)||' :'|| hxt_value(i).name);
1114: hr_utility.trace('p_values_rec.date_worked:'||p_values_rec.date_worked);
1115: end if;
1116: l_value_meaning := get_lookup_code (hxt_value(i).name,
1109: --
1110: lv_pbl_flag := 'N';
1111: if g_debug then
1112: hr_utility.set_location('dtl_to_BEE',30);
1113: hr_utility.trace('hxt_value_name_'||to_char(i)||' :'|| hxt_value(i).name);
1114: hr_utility.trace('p_values_rec.date_worked:'||p_values_rec.date_worked);
1115: end if;
1116: l_value_meaning := get_lookup_code (hxt_value(i).name,
1117: p_values_rec.date_worked);
1110: lv_pbl_flag := 'N';
1111: if g_debug then
1112: hr_utility.set_location('dtl_to_BEE',30);
1113: hr_utility.trace('hxt_value_name_'||to_char(i)||' :'|| hxt_value(i).name);
1114: hr_utility.trace('p_values_rec.date_worked:'||p_values_rec.date_worked);
1115: end if;
1116: l_value_meaning := get_lookup_code (hxt_value(i).name,
1117: p_values_rec.date_worked);
1118: if g_debug then
1115: end if;
1116: l_value_meaning := get_lookup_code (hxt_value(i).name,
1117: p_values_rec.date_worked);
1118: if g_debug then
1119: hr_utility.trace('l_value_meaning :'|| l_value_meaning);
1120: end if;
1121: if l_value_meaning = 'HOURS' then
1122: if g_debug then
1123: hr_utility.set_location('dtl_to_BEE',40);
1119: hr_utility.trace('l_value_meaning :'|| l_value_meaning);
1120: end if;
1121: if l_value_meaning = 'HOURS' then
1122: if g_debug then
1123: hr_utility.set_location('dtl_to_BEE',40);
1124: end if;
1125: pbl_value(i) := convert_lookup(p_values_rec.hours,
1126: hxt_value(i).lookup,
1127: p_values_rec.date_worked);
1125: pbl_value(i) := convert_lookup(p_values_rec.hours,
1126: hxt_value(i).lookup,
1127: p_values_rec.date_worked);
1128: if g_debug then
1129: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1130: end if;
1131: elsif l_value_meaning = 'AMOUNT' then
1132: if g_debug then
1133: hr_utility.set_location('dtl_to_BEE',50);
1129: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1130: end if;
1131: elsif l_value_meaning = 'AMOUNT' then
1132: if g_debug then
1133: hr_utility.set_location('dtl_to_BEE',50);
1134: end if;
1135: pbl_value(i) := convert_lookup(p_values_rec.amount,
1136: hxt_value(i).lookup,
1137: p_values_rec.date_worked);
1135: pbl_value(i) := convert_lookup(p_values_rec.amount,
1136: hxt_value(i).lookup,
1137: p_values_rec.date_worked);
1138: if g_debug then
1139: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1140: end if;
1141: elsif l_value_meaning = 'RATE_MULTIPLE' then
1142: if g_debug then
1143: hr_utility.set_location('dtl_to_BEE',60);
1139: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1140: end if;
1141: elsif l_value_meaning = 'RATE_MULTIPLE' then
1142: if g_debug then
1143: hr_utility.set_location('dtl_to_BEE',60);
1144: end if;
1145: pbl_value(i) := convert_lookup(p_values_rec.rate_multiple,
1146: hxt_value(i).lookup,
1147: p_values_rec.date_worked);
1145: pbl_value(i) := convert_lookup(p_values_rec.rate_multiple,
1146: hxt_value(i).lookup,
1147: p_values_rec.date_worked);
1148: if g_debug then
1149: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1150: end if;
1151: elsif l_value_meaning = 'HOURLY_RATE' then
1152: if g_debug then
1153: hr_utility.set_location('dtl_to_BEE',70);
1149: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1150: end if;
1151: elsif l_value_meaning = 'HOURLY_RATE' then
1152: if g_debug then
1153: hr_utility.set_location('dtl_to_BEE',70);
1154: end if;
1155: pbl_value(i) := convert_lookup(p_values_rec.hourly_rate,
1156: hxt_value(i).lookup,
1157: p_values_rec.date_worked);
1155: pbl_value(i) := convert_lookup(p_values_rec.hourly_rate,
1156: hxt_value(i).lookup,
1157: p_values_rec.date_worked);
1158: if g_debug then
1159: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1160: end if;
1161: elsif l_value_meaning = 'RATE' then
1162: if g_debug then
1163: hr_utility.set_location('dtl_to_BEE',80);
1159: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1160: end if;
1161: elsif l_value_meaning = 'RATE' then
1162: if g_debug then
1163: hr_utility.set_location('dtl_to_BEE',80);
1164: end if;
1165: pbl_value(i) := convert_lookup(p_values_rec.hourly_rate,
1166: hxt_value(i).lookup,
1167: p_values_rec.date_worked);
1165: pbl_value(i) := convert_lookup(p_values_rec.hourly_rate,
1166: hxt_value(i).lookup,
1167: p_values_rec.date_worked);
1168: if g_debug then
1169: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1170: end if;
1171: elsif l_value_meaning = 'RATE_CODE' then
1172: if g_debug then
1173: hr_utility.set_location('dtl_to_BEE',90);
1169: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1170: end if;
1171: elsif l_value_meaning = 'RATE_CODE' then
1172: if g_debug then
1173: hr_utility.set_location('dtl_to_BEE',90);
1174: end if;
1175: pbl_value(i) := convert_lookup(p_values_rec.rate_code,
1176: hxt_value(i).lookup,
1177: p_values_rec.date_worked);
1175: pbl_value(i) := convert_lookup(p_values_rec.rate_code,
1176: hxt_value(i).lookup,
1177: p_values_rec.date_worked);
1178: if g_debug then
1179: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1180: end if;
1181: -- BEGIN US localization
1182: elsif hxt_value(i).name = 'Jurisdiction' then
1183: if g_debug then
1180: end if;
1181: -- BEGIN US localization
1182: elsif hxt_value(i).name = 'Jurisdiction' then
1183: if g_debug then
1184: hr_utility.set_location('dtl_to_BEE',100);
1185: end if;
1186: if( p_values_rec.state_name is not null or
1187: p_values_rec.county_name is not null or
1188: p_values_rec.city_name is not null or
1198: hxt_value(i).lookup,
1199: p_values_rec.date_worked);
1200: end if;
1201: if g_debug then
1202: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1203: end if;
1204: elsif hxt_value(i).name = 'Deduction Processing' then
1205: if g_debug then
1206: hr_utility.set_location('dtl_to_BEE',110);
1202: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1203: end if;
1204: elsif hxt_value(i).name = 'Deduction Processing' then
1205: if g_debug then
1206: hr_utility.set_location('dtl_to_BEE',110);
1207: end if;
1208: pbl_value(i) := convert_lookup(p_values_rec.fcl_tax_rule_code,
1209: hxt_value(i).lookup,
1210: p_values_rec.date_worked);
1208: pbl_value(i) := convert_lookup(p_values_rec.fcl_tax_rule_code,
1209: hxt_value(i).lookup,
1210: p_values_rec.date_worked);
1211: if g_debug then
1212: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1213: end if;
1214: elsif hxt_value(i).name = 'Separate Check' then
1215: if g_debug then
1216: hr_utility.set_location('dtl_to_BEE',120);
1212: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1213: end if;
1214: elsif hxt_value(i).name = 'Separate Check' then
1215: if g_debug then
1216: hr_utility.set_location('dtl_to_BEE',120);
1217: end if;
1218: pbl_value(i) := convert_lookup(p_values_rec.separate_check_flag,
1219: hxt_value(i).lookup,
1220: p_values_rec.date_worked);
1218: pbl_value(i) := convert_lookup(p_values_rec.separate_check_flag,
1219: hxt_value(i).lookup,
1220: p_values_rec.date_worked);
1221: if g_debug then
1222: hr_utility.trace('pbl_value_'||to_char(i)||' :'|| pbl_value(i));
1223: end if;
1224: -- END US localization
1225: elsif hxt_value(i).name is not null THEN -- pbl_value(i) := NULL;
1226: if g_debug then
1223: end if;
1224: -- END US localization
1225: elsif hxt_value(i).name is not null THEN -- pbl_value(i) := NULL;
1226: if g_debug then
1227: hr_utility.set_location('dtl_to_BEE',130);
1228: end if;
1229: OPEN c_date_input_value(p_values_rec.element_type_id
1230: ,p_values_rec.assignment_id
1231: ,p_values_rec.date_worked);
1230: ,p_values_rec.assignment_id
1231: ,p_values_rec.date_worked);
1232: LOOP
1233: if g_debug then
1234: hr_utility.set_location('dtl_to_BEE',140);
1235: end if;
1236: FETCH c_date_input_value into l_piv_name;
1237: EXIT WHEN c_date_input_value%NOTFOUND;
1238: if g_debug then
1235: end if;
1236: FETCH c_date_input_value into l_piv_name;
1237: EXIT WHEN c_date_input_value%NOTFOUND;
1238: if g_debug then
1239: hr_utility.trace('l_piv_name :'||l_piv_name);
1240: hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
1241: end if;
1242: IF l_piv_name = hxt_value(i).name THEN
1243: if g_debug then
1236: FETCH c_date_input_value into l_piv_name;
1237: EXIT WHEN c_date_input_value%NOTFOUND;
1238: if g_debug then
1239: hr_utility.trace('l_piv_name :'||l_piv_name);
1240: hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
1241: end if;
1242: IF l_piv_name = hxt_value(i).name THEN
1243: if g_debug then
1244: hr_utility.set_location('dtl_to_BEE',150);
1240: hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
1241: end if;
1242: IF l_piv_name = hxt_value(i).name THEN
1243: if g_debug then
1244: hr_utility.set_location('dtl_to_BEE',150);
1245: end if;
1246: -- pbl_value(i) := to_char(p_values_rec.date_worked,'DD-MON-YYYY');
1247: pbl_value(i) := fnd_date.date_to_canonical(p_values_rec.date_worked);
1248: lv_pbl_flag := 'Y';
1246: -- pbl_value(i) := to_char(p_values_rec.date_worked,'DD-MON-YYYY');
1247: pbl_value(i) := fnd_date.date_to_canonical(p_values_rec.date_worked);
1248: lv_pbl_flag := 'Y';
1249: if g_debug then
1250: hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1251: end if;
1252: exit;
1253: END IF;
1254: END LOOP;
1254: END LOOP;
1255: CLOSE c_date_input_value;
1256: if lv_pbl_flag = 'N' then
1257: if g_debug then
1258: hr_utility.set_location('dtl_to_BEE',160);
1259: end if;
1260: pbl_value(i) := NULL;
1261: if g_debug then
1262: hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1258: hr_utility.set_location('dtl_to_BEE',160);
1259: end if;
1260: pbl_value(i) := NULL;
1261: if g_debug then
1262: hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1263: end if;
1264: end if;
1265: if g_debug then
1266: hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
1262: hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1263: end if;
1264: end if;
1265: if g_debug then
1266: hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
1267: end if;
1268: else
1269: if g_debug then
1270: hr_utility.set_location('dtl_to_BEE',180);
1266: hr_utility.trace('lv_pbl_flag :'||lv_pbl_flag);
1267: end if;
1268: else
1269: if g_debug then
1270: hr_utility.set_location('dtl_to_BEE',180);
1271: end if;
1272: pbl_value(i) := NULL;
1273: if g_debug then
1274: hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1270: hr_utility.set_location('dtl_to_BEE',180);
1271: end if;
1272: pbl_value(i) := NULL;
1273: if g_debug then
1274: hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1275: end if;
1276: end if;
1277: if g_debug then
1278: hr_utility.set_location('dtl_to_BEE',190);
1274: hr_utility.trace('pbl_value_'||to_char(i)||' :'||pbl_value(i));
1275: end if;
1276: end if;
1277: if g_debug then
1278: hr_utility.set_location('dtl_to_BEE',190);
1279: end if;
1280: HXT_UTIL.DEBUG('value_'||to_char(i)||' = '||pbl_value(i)); --HXT115
1281: END LOOP;
1282: -- Get Batch Line ID
1280: HXT_UTIL.DEBUG('value_'||to_char(i)||' = '||pbl_value(i)); --HXT115
1281: END LOOP;
1282: -- Get Batch Line ID
1283: if g_debug then
1284: hr_utility.set_location('dtl_to_BEE',200);
1285: end if;
1286: -- Get next sequence number
1287: -- l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_values_rec.batch_id);
1288: if g_debug then
1285: end if;
1286: -- Get next sequence number
1287: -- l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_values_rec.batch_id);
1288: if g_debug then
1289: hr_utility.set_location('dtl_to_BEE',210);
1290: end if;
1291: HXT_UTIL.DEBUG('batch_sequence = '||to_char(p_batch_sequence)); --HXT115
1292: -- Insert data into BEE table
1293: PAY_BATCH_ELEMENT_ENTRY_API.create_batch_line
1550: select 'PIPE' || userenv('sessionid')
1551: into g_pipe_session
1552: from dual;
1553: if g_debug then
1554: hr_utility.set_location('PAY_US_PDT_PROCESS.TRANSFER_TO_PAYROLL', 1);
1555: end if;
1556: --DEBUG ONLY END*/
1557: EXCEPTION
1558: WHEN OTHERS THEN