[Home] [Help]
PACKAGE BODY: APPS.PAY_BATCH_BALANCEADJ_WRAPPER
Source
1 PACKAGE BODY pay_batch_balanceadj_wrapper AS
2 /* $Header: paybbawebadi.pkb 120.19 2011/05/17 12:56:30 nchinnam ship $ */
3
4 value_found EXCEPTION;
5
6 FUNCTION get_costing_info
7 (
8 l_concat_segments in varchar2,
9 l_segment1 in varchar2 default null,
10 l_segment2 in varchar2 default null,
11 l_segment3 in varchar2 default null,
12 l_segment4 in varchar2 default null,
13 l_segment5 in varchar2 default null,
14 l_segment6 in varchar2 default null,
15 l_segment7 in varchar2 default null,
16 l_segment8 in varchar2 default null,
17 l_segment9 in varchar2 default null,
18 l_segment10 in varchar2 default null,
19 l_segment11 in varchar2 default null,
20 l_segment12 in varchar2 default null,
21 l_segment13 in varchar2 default null,
22 l_segment14 in varchar2 default null,
23 l_segment15 in varchar2 default null,
24 l_segment16 in varchar2 default null,
25 l_segment17 in varchar2 default null,
26 l_segment18 in varchar2 default null,
27 l_segment19 in varchar2 default null,
28 l_segment20 in varchar2 default null,
29 l_segment21 in varchar2 default null,
30 l_segment22 in varchar2 default null,
31 l_segment23 in varchar2 default null,
32 l_segment24 in varchar2 default null,
33 l_segment25 in varchar2 default null,
34 l_segment26 in varchar2 default null,
35 l_segment27 in varchar2 default null,
36 l_segment28 in varchar2 default null,
37 l_segment29 in varchar2 default null,
38 l_segment30 in varchar2 default null
39 ) return number is
40
41 l_ccid number := -1;
42
43 Begin
44
45 -- find the cost_allocation_keyflex_id
46
47 l_ccid := hr_entry.maintain_cost_keyflex(
48 p_cost_keyflex_structure => g_flex_num,
49 p_cost_allocation_keyflex_id => -1,
50 p_concatenated_segments => l_concat_segments,
51 p_summary_flag =>'N',
52 p_start_date_active => NULL,
53 p_end_date_active => NULL,
54 p_segment1 =>l_segment1,
55 p_segment2 =>l_segment2,
56 p_segment3 =>l_segment3,
57 p_segment4 =>l_segment4,
58 p_segment5 =>l_segment5,
59 p_segment6 =>l_segment6,
60 p_segment7 =>l_segment7,
61 p_segment8 =>l_segment8,
62 p_segment9 =>l_segment9,
63 p_segment10 =>l_segment10,
64 p_segment11 =>l_segment11,
65 p_segment12 =>l_segment12,
66 p_segment13 =>l_segment13,
67 p_segment14 =>l_segment14,
68 p_segment15 =>l_segment15,
69 p_segment16 =>l_segment16,
70 p_segment17 =>l_segment17,
71 p_segment18 =>l_segment18,
72 p_segment19 =>l_segment19,
73 p_segment20 =>l_segment20,
74 p_segment21 =>l_segment21,
75 p_segment22 =>l_segment22,
76 p_segment23 =>l_segment23,
77 p_segment24 =>l_segment24,
78 p_segment25 =>l_segment25,
79 p_segment26 =>l_segment26,
80 p_segment27 =>l_segment27,
81 p_segment28 =>l_segment28,
82 p_segment29 =>l_segment29,
83 p_segment30 =>l_segment30);
84
85 hr_utility.trace('A4 CKF call p_cost_allocation_keyflex is : '|| l_ccid);
86
87 return(l_ccid);
88
89 end get_costing_info;
90
91
92 PROCEDURE create_batch_header(p_batch_name in varchar2,
93 p_business_group_id in number,
94 p_batch_reference in varchar2 default null,
95 p_batch_source in varchar2 default null,
96 p_batch_status in varchar2 default 'U',
97 p_batch_id out nocopy number ) is
98
99 l_bg_name per_business_groups.name%TYPE;
100 l_batch_name pay_balance_batch_headers.batch_name%TYPE := null;
101
102 l_new_header_id number;
103
104 Begin
105
106 hr_utility.trace('p_batch_name' || p_batch_name);
107 hr_utility.trace('p_business_group_id' || p_business_group_id);
108 hr_utility.trace('p_batch_reference' || p_batch_reference);
109 hr_utility.trace('p_batch_source' || p_batch_source);
110 hr_utility.trace('p_batch_status' || p_batch_status);
111 hr_utility.trace('p_batch_id' || p_batch_id);
112
113 -- hr_utility.trace_on(null,'webadi');
114 select name into l_bg_name
115 from per_business_groups
116 where business_group_id = p_business_group_id;
117
118
119 hr_utility.trace('l_batch_name, before selecting' || l_batch_name);
120 select batch_name into l_batch_name
121 from pay_balance_batch_headers
122 where upper(batch_name) = upper(p_batch_name)
123 and business_group_id = p_business_group_id;
124
125 hr_utility.trace('l_batch_name, after selecting' || l_batch_name);
126
127 if l_batch_name is not null then
128
129 hr_utility.trace('l_batch_name, exception:' || l_batch_name);
130
131 raise value_found;
132
133 end if;
134
135 EXCEPTION
136
137 when value_found then
138
139 hr_utility.trace('exception: value_found');
140
141 hr_utility.trace('Please enter a unique name for Batch Name parameter');
142 hr_utility.set_message('PAY','Please enter a unique name for Batch Name parameter');
143 -- Bug: 5079557
144 hr_utility.raise_error;
145
146 -- return;
147
148 when no_data_found then
149
150 hr_utility.trace('exception: no data found');
151
152 --select max(batch_id) into p_batch_id
153 select pay_batch_headers_s.nextval into p_batch_id
154 from dual;
155
156 -- create batch header
157
158 insert into pay_balance_batch_headers
159 (batch_id,
160 batch_name,
161 business_group_id,
162 batch_status,
163 batch_reference,
164 batch_source,
165 business_group_name,
166 payroll_id,
167 payroll_name,
168 upload_date,
169 batch_type)
170 values (p_batch_id, --pay_balance_batch_headers_s.nextval,
171 p_batch_name,
172 p_business_group_id,
173 'U', -- Unprocessed
174 p_batch_reference,
175 p_batch_source,
176 l_bg_name,
177 null,
178 null,
179 sysdate,
180 'A');
181
182 end create_batch_header;
183
184
185 PROCEDURE update_batch_header(
186 p_batch_id in number,
187 p_batch_name in varchar2 default hr_api.g_varchar2,
188 p_batch_reference in varchar2 default hr_api.g_varchar2,
189 p_batch_source in varchar2 default hr_api.g_varchar2,
190 p_batch_status in varchar2 default hr_api.g_varchar2) is
191
192 l_batch_status pay_balance_batch_headers.batch_status%TYPE;
193 l_bg_name per_business_groups.name%TYPE;
194 l_batch_name pay_balance_batch_headers.batch_name%TYPE := null;
195
196
197 Begin
198
199 --hr_utility.trace_on(null,'ram');
200 -- Bug: 5171907
201 hr_utility.trace('p_batch_id: ' || p_batch_id);
202 hr_utility.trace('p_batch_name: ' || p_batch_name);
203 hr_utility.trace('p_batch_reference: ' || p_batch_reference);
204 hr_utility.trace('p_batch_source: ' || p_batch_source);
205 hr_utility.trace('p_batch_status: ' || p_batch_status);
206
207 -- Bug: 5226336
208
209 SELECT batch_status, business_group_name
210 INTO l_batch_status, l_bg_name
211 FROM pay_balance_batch_headers
212 WHERE batch_id = p_batch_id;
213
214 hr_utility.trace('l_batch_status: ' || l_batch_status);
215 hr_utility.trace('l_bg_name: ' || l_bg_name);
216
217 SELECT batch_name
218 INTO l_batch_name
219 FROM pay_balance_batch_headers
220 WHERE upper(batch_name) = upper(p_batch_name)
221 AND business_group_name = l_bg_name;
222
223 IF l_batch_name is not null THEN
224 hr_utility.trace('l_batch_name, exception:' || l_batch_name);
225 raise value_found;
226 END IF;
227
228 EXCEPTION
229
230 WHEN value_found THEN
231
232 hr_utility.trace('exception: value_found');
233 hr_utility.trace('Please enter a unique name for Batch Name parameter');
234 hr_utility.set_message('PAY','Please enter a unique name for Batch Name parameter');
235 hr_utility.raise_error;
236
237 WHEN others THEN
238 BEGIN
239 hr_utility.trace('exception: no data found');
240
241 IF l_batch_status in ('L','T','C') THEN
242 RAISE value_found;
243 ELSE
244 BEGIN
245 hr_utility.trace('updating batch headers...');
246 hr_utility.trace('p_batch_name: ' || p_batch_name);
247 hr_utility.trace('p_batch_reference: ' || p_batch_reference);
248 hr_utility.trace('p_batch_source: ' || p_batch_source);
249 hr_utility.trace('p_batch_id: ' || p_batch_id);
250
251 UPDATE pay_balance_batch_headers
252 SET batch_name = p_batch_name,
253 batch_reference = p_batch_reference,
254 batch_source = p_batch_source
255 WHERE batch_id = p_batch_id;
256
257 hr_utility.trace('updating batch headers is done');
258
259 EXCEPTION
260
261 WHEN OTHERS THEN
262 hr_utility.trace('Exception: unable to update pay_balance_batch_headers table.');
263 hr_utility.raise_error;
264 END;
265 END IF;
266
267 EXCEPTION
268
269 WHEN value_found THEN
270 hr_utility.trace('exception: value_found');
271 hr_utility.raise_error;
272 END;
273 end update_batch_header;
274
275
276 PROCEDURE update_batch_groups_lines(
277 p_batch_id in number,
278 p_batch_name in varchar2,
279 p_batch_group_id in number, -- NEW
280 p_batch_line_id in number, -- NEW
281 p_effective_date in date, -- effective date
282 p_employee_id in varchar2, -- Employee Name
283 p_assignment_id in varchar2, -- assignment_number
284 p_element_name in varchar2,
285 p_element_type_id in number,
286 p_element_link_id in number ,
287 p_payroll_id in number default null,
288 p_business_group_id in number,
289 p_consolidation_set_id in number default null,
290 p_gre_id in number default null,
291 p_prepay_flag in varchar2 ,
292 p_costing_flag in varchar2 ,
293 p_cost_allocation_keyflex in number default null,
294 p_concatenated_segments in varchar2 default null,
295 segment1 in varchar2 default null,
296 segment2 in varchar2 default null,
297 segment3 in varchar2 default null,
298 segment4 in varchar2 default null,
299 segment5 in varchar2 default null,
300 segment6 in varchar2 default null,
301 segment7 in varchar2 default null,
302 segment8 in varchar2 default null,
303 segment9 in varchar2 default null,
304 segment10 in varchar2 default null,
305 segment11 in varchar2 default null,
306 segment12 in varchar2 default null,
307 segment13 in varchar2 default null,
308 segment14 in varchar2 default null,
309 segment15 in varchar2 default null,
310 segment16 in varchar2 default null,
311 segment17 in varchar2 default null,
312 segment18 in varchar2 default null,
313 segment19 in varchar2 default null,
314 segment20 in varchar2 default null,
315 segment21 in varchar2 default null,
316 segment22 in varchar2 default null,
317 segment23 in varchar2 default null,
318 segment24 in varchar2 default null,
319 segment25 in varchar2 default null,
320 segment26 in varchar2 default null,
321 segment27 in varchar2 default null,
322 segment28 in varchar2 default null,
323 segment29 in varchar2 default null,
324 segment30 in varchar2 default null,
325 p_ee_value1 in varchar2 default null,
326 p_ee_value2 in varchar2 default null,
327 p_ee_value3 in varchar2 default null,
328 p_ee_value4 in varchar2 default null,
329 p_ee_value5 in varchar2 default null,
330 p_ee_value6 in varchar2 default null,
331 p_ee_value7 in varchar2 default null,
332 p_ee_value8 in varchar2 default null,
333 p_ee_value9 in varchar2 default null,
334 p_ee_value10 in varchar2 default null,
335 p_ee_value11 in varchar2 default null,
336 p_ee_value12 in varchar2 default null,
337 p_ee_value13 in varchar2 default null,
338 p_ee_value14 in varchar2 default null,
339 p_ee_value15 in varchar2 default null,
340 p_col1 in number default null,
341 p_col2 in number default null,
342 p_col3 in number default null,
343 p_col4 in number default null,
344 p_col5 in number default null,
345 p_col_val1 in varchar2 default null,
346 p_col_val2 in varchar2 default null,
347 p_col_val3 in varchar2 default null,
348 p_col_val4 in varchar2 default null,
349 p_col_val5 in varchar2 default null) IS
350
351 l_batch_group_status pay_adjust_batch_groups.batch_group_status%TYPE;
352 l_batch_line_status pay_adjust_batch_lines.batch_line_status%TYPE;
353
354 l_cakff_id number;
355 l_batch_line_id pay_adjust_batch_lines.batch_line_id%TYPE;
356
357 Begin
358
359
360 --hr_utility.trace_on(null,'webadi');
361 hr_utility.trace('p_batch_id is : '|| p_batch_id );
362 hr_utility.trace('p_batch_name is : '|| p_batch_name );
363 hr_utility.trace('p_batch_group_id is : '|| p_batch_group_id );
364 hr_utility.trace('p_batch_line_id is : '|| p_batch_line_id );
365 hr_utility.trace('p_effective_date is : '|| p_effective_date );
366 hr_utility.trace('p_employee_id is : '|| p_employee_id);
367 hr_utility.trace('p_assignment_id is : '|| p_assignment_id );
368 hr_utility.trace('p_element_name is : '|| p_element_name );
369 hr_utility.trace('p_element_type_id is : '|| p_element_type_id);
370 hr_utility.trace('p_element_link_id is : '|| p_element_link_id );
371 hr_utility.trace('p_payroll_id is : '|| p_payroll_id );
372 hr_utility.trace('p_business_group_id is : '|| p_business_group_id );
373 hr_utility.trace('p_consolidation_set_id is : '|| p_consolidation_set_id);
374 hr_utility.trace('p_gre_id is : '|| p_gre_id );
375 hr_utility.trace('p_prepay_flag is : '||p_prepay_flag );
376 hr_utility.trace('p_costing_flag is : '|| p_costing_flag );
377 hr_utility.trace('p_cost_allocation_keyflex is : '|| p_cost_allocation_keyflex );
378 hr_utility.trace('p_concatenated_segments is : '|| p_concatenated_segments );
379 hr_utility.trace('segment1 is : '|| segment1 );
380 hr_utility.trace('segment2 is : '|| segment2 );
381 hr_utility.trace('segment3 is : '|| segment3 );
382 hr_utility.trace('segment4 is : '|| segment4 );
383 hr_utility.trace('segment5 is : '|| segment5 );
384 hr_utility.trace('segment6 is : '|| segment6 );
385 hr_utility.trace('segment7 is : '|| segment7 );
386 hr_utility.trace('segment8 is : '|| segment8 );
387 hr_utility.trace('segment9 is : '|| segment9 );
388 hr_utility.trace('segment10 is : '||segment10 );
389 hr_utility.trace('segment11 is : '||segment11 );
390 hr_utility.trace('segment12 is : '||segment12 );
391 hr_utility.trace('segment13 is : '||segment13 );
392 hr_utility.trace('segment14 is : '||segment14 );
393 hr_utility.trace('segment15 is : '||segment15 );
394 hr_utility.trace('segment16 is : '|| segment16);
395 hr_utility.trace('segment17 is : '|| segment17 );
396 hr_utility.trace('segment18 is : '|| segment18 );
397 hr_utility.trace('segment19 is : '|| segment19 );
398 hr_utility.trace('segment20 is : '|| segment20 );
399 hr_utility.trace('segment21 is : '|| segment21 );
400 hr_utility.trace('segment22 is : '|| segment22 );
401 hr_utility.trace('segment23 is : '|| segment23 );
402 hr_utility.trace('segment24 is : '|| segment24 );
403 hr_utility.trace('segment25 is : '||segment25 );
404 hr_utility.trace('segment26 is : '||segment26 );
405 hr_utility.trace('segment27 cons is : '||segment27 );
406 hr_utility.trace('segment28 is : '||segment28 );
407 hr_utility.trace('segment29 is : '||segment29 );
408 hr_utility.trace('segment30 is : '||segment30 );
409 hr_utility.trace('p_ee_value1 is : '|| p_ee_value1 );
410 hr_utility.trace('p_ee_value2 is : '|| p_ee_value2 );
411 hr_utility.trace('p_ee_value3 is : '|| p_ee_value3 );
412 hr_utility.trace('p_ee_value4 is : '|| p_ee_value4 );
413 hr_utility.trace('p_ee_value5 is : '|| p_ee_value5 );
414 hr_utility.trace('p_ee_value6 is : '|| p_ee_value6 );
415 hr_utility.trace('p_ee_value7 is : '|| p_ee_value7 );
416 hr_utility.trace('p_ee_value8 is : '|| p_ee_value8 );
417 hr_utility.trace('p_ee_value9 is : '|| p_ee_value9 );
418 hr_utility.trace('p_ee_value10 is : '||p_ee_value10 );
419 hr_utility.trace('p_ee_value11 is : '||p_ee_value11 );
420 hr_utility.trace('p_ee_value12 is : '||p_ee_value12 );
421 hr_utility.trace('p_ee_value13 is : '||p_ee_value13 );
422 hr_utility.trace('p_ee_value14 is : '||p_ee_value14 );
423 hr_utility.trace('p_ee_value15 is : '||p_ee_value15 );
424 hr_utility.trace('p_col1 is : '|| p_col1 );
425 hr_utility.trace('p_col2 is : '|| p_col2 );
426 hr_utility.trace('p_col3 is : '|| p_col3 );
427 hr_utility.trace('p_col4 is : '|| p_col4 );
428 hr_utility.trace('p_col5 is : '|| p_col5 );
429 hr_utility.trace('p_col_val1 is : '|| p_col_val1 );
430 hr_utility.trace('p_col_val2 is : '|| p_col_val2 );
431 hr_utility.trace('p_col_val3 is : '|| p_col_val3 );
432 hr_utility.trace('p_col_val4 is : '|| p_col_val4 );
433 hr_utility.trace('p_col_val5 is : '|| p_col_val5 );
434
435 select batch_group_status into l_batch_group_status
436 from pay_adjust_batch_groups
437 where batch_group_id = p_batch_group_id;
438
439 if l_batch_group_status in ('L','T','C') then
440
441 raise value_found;
442
443 else
444
445 update pay_adjust_batch_groups
446 set consolidation_set_id = p_consolidation_set_id,
447 payroll_id = p_payroll_id,
448 effective_date = p_effective_date,
449 prepay_flag = p_prepay_flag
450 where batch_group_id = p_batch_group_id;
451
452 /* here we can assume that the batch_line is also not in
453 'L','T','C' status */
454
455 if p_costing_flag = 'Y' then
456
457 l_cakff_id := get_costing_info(p_concatenated_segments,
458 segment1,segment2,segment3,segment4,segment5,
459 segment6,segment7,segment8,segment9,segment10,
460 segment11,segment12,segment13,segment14,segment15,
461 segment16,segment17,segment18,segment19,segment20,
462 segment21,segment22,segment23,segment24,segment25,
463 segment26,segment27,segment28,segment29,segment30);
464
465 end if;
466
467 update pay_adjust_batch_lines
468 set assignment_id = p_assignment_id,
469 tax_unit_id = p_gre_id,
470 entry_value1 = p_ee_value1,
471 entry_value2 = p_ee_value2,
472 entry_value3 = p_ee_value3,
473 entry_value4 = p_ee_value4,
474 entry_value5 = p_ee_value5,
475 entry_value6 = p_ee_value6,
476 entry_value7 = p_ee_value7,
477 entry_value8 = p_ee_value8,
478 entry_value9 = p_ee_value9,
479 entry_value10 = p_ee_value10,
480 entry_value11 = p_ee_value11,
481 entry_value12 = p_ee_value12,
482 entry_value13 = p_ee_value13,
483 entry_value14 = p_ee_value14,
484 entry_value15 = p_ee_value15,
485 balance_adj_cost_flag = p_costing_flag,
486 cost_allocation_keyflex_id = l_cakff_id
487 where batch_line_id = l_batch_line_id;
488
489
490 end if;
491
492
493 exception when value_found then
494
495 hr_utility.trace('Cannot update Batch Lines');
496 return;
497
498 end update_batch_groups_lines;
499
500
501 PROCEDURE upload_data(
502 p_batch_id in number,
503 p_batch_name in varchar2,
504 p_effective_date in date, -- effective date
505 p_employee_id in varchar2, -- Employee Name
506 p_assignment_id in varchar2, -- assignment_number
507 p_element_name in varchar2,
508 p_element_type_id in number,
509 p_element_link_id in number default null,
510 p_payroll_id in varchar2 default null, -- Payroll Name
511 p_business_group_id in number,
512 p_consolidation_set_id in number default null,
513 p_gre_id in varchar2 default null, -- GRE Name
514 p_prepay_flag in varchar2,
515 p_costing_flag in varchar2,
516 p_cost_allocation_keyflex in number default null,
517 p_concatenated_segments in varchar2 default null,
518 segment1 in varchar2 default null,
519 segment2 in varchar2 default null,
520 segment3 in varchar2 default null,
521 segment4 in varchar2 default null,
522 segment5 in varchar2 default null,
523 segment6 in varchar2 default null,
524 segment7 in varchar2 default null,
525 segment8 in varchar2 default null,
526 segment9 in varchar2 default null,
527 segment10 in varchar2 default null,
528 segment11 in varchar2 default null,
529 segment12 in varchar2 default null,
530 segment13 in varchar2 default null,
531 segment14 in varchar2 default null,
532 segment15 in varchar2 default null,
533 segment16 in varchar2 default null,
534 segment17 in varchar2 default null,
535 segment18 in varchar2 default null,
536 segment19 in varchar2 default null,
537 segment20 in varchar2 default null,
538 segment21 in varchar2 default null,
539 segment22 in varchar2 default null,
540 segment23 in varchar2 default null,
541 segment24 in varchar2 default null,
542 segment25 in varchar2 default null,
543 segment26 in varchar2 default null,
544 segment27 in varchar2 default null,
545 segment28 in varchar2 default null,
546 segment29 in varchar2 default null,
547 segment30 in varchar2 default null,
548 p_ee_value1 in varchar2 default null,
549 p_ee_value2 in varchar2 default null,
550 p_ee_value3 in varchar2 default null,
551 p_ee_value4 in varchar2 default null,
552 p_ee_value5 in varchar2 default null,
553 p_ee_value6 in varchar2 default null,
554 p_ee_value7 in varchar2 default null,
555 p_ee_value8 in varchar2 default null,
556 p_ee_value9 in varchar2 default null,
557 p_ee_value10 in varchar2 default null,
558 p_ee_value11 in varchar2 default null,
559 p_ee_value12 in varchar2 default null,
560 p_ee_value13 in varchar2 default null,
561 p_ee_value14 in varchar2 default null,
562 p_ee_value15 in varchar2 default null,
563 p_col1 in number default null,
564 p_col2 in number default null,
565 p_col3 in number default null,
566 p_col4 in number default null,
567 p_col5 in number default null,
568 p_col_val1 in varchar2 default null,
569 p_col_val2 in varchar2 default null,
570 p_col_val3 in varchar2 default null,
571 p_col_val4 in varchar2 default null,
572 p_col_val5 in varchar2 default null,
573 p_batch_line_id in number default null,
574 p_batch_group_id in number default null,
575 p_batch_line_status in varchar2 default null,
576 p_mode in varchar2 default null) IS
577
578 cursor csr_check_batch_group(ln_batch_id number,
579 ln_consolidation_set_id number,
580 ln_payroll_id number,
581 ln_effective_date date,
582 ln_prepay_flag varchar2) IS
583 select batch_group_id, batch_group_status
584 from pay_adjust_batch_groups
585 where batch_id = ln_batch_id
586 and consolidation_set_id = ln_consolidation_set_id
587 and payroll_id = ln_payroll_id
588 and effective_date = ln_effective_date
589 and prepay_flag = ln_prepay_flag;
590
591 cursor csr_check_batch_line(ln_batch_id number,
592 ln_batch_group_id number,
593 ln_assignment_id number,
594 ln_element_type_id number) IS
595 select batch_line_id, batch_line_status
596 from pay_adjust_batch_lines
597 where batch_id = ln_batch_id
598 and batch_group_id = ln_batch_group_id
599 and assignment_id = ln_assignment_id
600 and element_type_id = ln_element_type_id;
601
602 -- Bug: 5212904
603
604 CURSOR csr_get_batch_line_details IS
605 SELECT batch_line_status, batch_group_id, assignment_id
606 FROM pay_adjust_batch_lines
607 WHERE batch_line_id = p_batch_line_id
608 AND batch_id = p_batch_id;
609
610 CURSOR csr_get_batch_group_details(l_batch_group_id number) IS
611 SELECT batch_group_status, consolidation_set_id,effective_date, prepay_flag
612 FROM pay_adjust_batch_groups
613 WHERE batch_group_id = l_batch_group_id;
614
615 -- Modified for the bug: 5212923
616
617 cursor c_get_input_value_id(cp_element_type_id number,
618 cp_eff_date date) is
619
620 select input_value_id,name,rownum
621 from (select inv.input_value_id,inv.name name,rownum
622 from pay_input_values_f inv
623 where inv.element_type_id= cp_element_type_id
624 and SYSDATE between inv.effective_start_date
625 and inv.effective_end_date
626 order by inv.display_sequence,inv.name);
627
628
629 -- Modified the following query for the Bug: 5079557
630
631 cursor c_get_payroll_id IS
632 select paf.payroll_id
633 from per_assignments_f paf
634 where paf.assignment_number = p_assignment_id
635 and paf.business_group_id = p_business_group_id
636 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
637
638 -- Get the Cost Allocation Keyflex num
639 cursor c_get_caflexnum(cp_bg_id number) IS
640 select cost_allocation_structure
641 from per_business_groups
642 where business_group_id = cp_bg_id;
643
644 -- Get the GRE ID based on GRE Name
645 cursor c_get_gre_id(cp_bg_id number,cp_gre_name varchar2) IS
646
647 SELECT hout.organization_id
648 FROM hr_organization_information hoi,
649 hr_organization_units hou,
650 hr_all_organization_units_tl hout
651 WHERE hoi.organization_id = hou.organization_id
652 AND hou.organization_id = hout.organization_id
653 AND hoi.ORG_INFORMATION_CONTEXT = 'CLASS'
654 AND org_information1 = 'HR_LEGAL'
655 AND hou.business_group_id = cp_bg_id
656 AND hout.name = cp_gre_name
657 AND hout.language = userenv('LANG');
658
659 -- Get Consolidation Set ID if user did not enter it in the spreadsheet.
660 -- Bug: 5079557
661
662 cursor c_get_consolidation_set_id IS
663 select pcs.consolidation_set_id
664 from per_assignments_f paf, pay_payrolls_f ppf, pay_consolidation_sets pcs
665 where paf.assignment_number = p_assignment_id
666 and paf.business_group_id = p_business_group_id
667 and sysdate between paf.effective_start_date and paf.effective_end_date
668 and paf.payroll_id = ppf.payroll_id
669 and sysdate between ppf.effective_start_date and ppf.effective_end_date
670 and ppf.consolidation_set_id = pcs.consolidation_set_id;
671
672 -- Get GRE if user did not enter it in the spreadsheet.
673 -- Bug: 5079557
674
675 cursor get_gre IS
676 select segment1
677 from per_all_assignments_f paf,
678 hr_soft_coding_keyflex hsck
679 where paf.business_group_id = p_business_group_id
680 and sysdate between paf.effective_start_date and paf.effective_end_date
681 and paf.assignment_number = p_assignment_id
682 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
683
684 -- Added the cursor to handle multiple GRE's for CA legislation.
685
686 cursor get_element_type IS
687 select element_information4 from pay_element_types_f
688 where element_type_id = p_element_type_id
689 and sysdate between effective_start_date and effective_end_date
690 and business_group_id = p_business_group_id;
691
692
693 cursor get_gre_ca IS
694 select segment1, segment11, segment12,
695 nvl(segment1,nvl(segment11,segment12))
696 from per_all_assignments_f paf,
697 hr_soft_coding_keyflex hsck
698 where paf.business_group_id = p_business_group_id
699 and sysdate between paf.effective_start_date and paf.effective_end_date
700 and paf.assignment_number = p_assignment_id
701 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
702
703 -- Added to know the legislation code for a business group
704
705 cursor get_legislation_code IS
706 select legislation_code
707 from per_business_groups
708 where business_group_id = p_business_group_id;
709
710
711 /* cursor to get the assignment_id */
712
713 CURSOR csr_get_asg_id is
714 select paf.assignment_id
715 from per_all_assignments_f paf,
716 per_all_people_f ppf
717 where ltrim(ppf.full_name) = p_employee_id
718 and ppf.person_id = paf.person_id
719 and ppf.business_group_id = p_business_group_id
720 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
721 and paf.assignment_number = p_assignment_id
722 and p_effective_date between paf.effective_start_date and paf.effective_end_date
723 and paf.business_group_id = p_business_group_id;
724
725 CURSOR csr_get_eff_dates(l_assignment_id number) IS
726 select effective_start_date, effective_end_date
727 from per_all_assignments_f
728 where assignment_id = l_assignment_id
729 and business_group_id = p_business_group_id
730 and p_effective_date between effective_start_date and effective_end_date;
731
732
733 i number;
734
735 ln_assignment_id per_assignments_f.assignment_id%TYPE;
736 l_inp_id pay_input_values_f.input_value_id%TYPE;
737 l_inp_name pay_input_values_f.name%TYPE;
738 l_inp_ds pay_input_values_f.display_sequence%TYPE;
739 p_concat_segments varchar2(240) default null;
740
741 l_cakff_id number;
742
743 l_segment1 varchar2(240) default null;
744 l_segment2 varchar2(240) default null;
745 l_segment3 varchar2(240) default null;
746 l_segment4 varchar2(240) default null;
747 l_segment5 varchar2(240) default null;
748 l_segment6 varchar2(240) default null;
749 l_segment7 varchar2(240) default null;
750 l_segment8 varchar2(240) default null;
751 l_segment9 varchar2(240) default null;
752 l_segment10 varchar2(240) default null;
753 l_segment11 varchar2(240) default null;
754 l_segment12 varchar2(240) default null;
755 l_segment13 varchar2(240) default null;
756 l_segment14 varchar2(240) default null;
757 l_segment15 varchar2(240) default null;
758 l_segment16 varchar2(240) default null;
759 l_segment17 varchar2(240) default null;
760 l_segment18 varchar2(240) default null;
761 l_segment19 varchar2(240) default null;
762 l_segment20 varchar2(240) default null;
763 l_segment21 varchar2(240) default null;
764 l_segment22 varchar2(240) default null;
765 l_segment23 varchar2(240) default null;
766 l_segment24 varchar2(240) default null;
767 l_segment25 varchar2(240) default null;
768 l_segment26 varchar2(240) default null;
769 l_segment27 varchar2(240) default null;
770 l_segment28 varchar2(240) default null;
771 l_segment29 varchar2(240) default null;
772 l_segment30 varchar2(240) default null;
773
774 l_batch_group_id number;
775 l_batch_group_status varchar2(10);
776
777 l_batch_line_id number := p_batch_line_id;
778 l_batch_line_status varchar2(10);
779
780 ln_payroll_id number;
781 -- Bug: 5079557
782 l_consolidation_set_id number := p_consolidation_set_id;
783
784 ex_cannot_update_bg EXCEPTION;
785 ex_cannot_update_bl EXCEPTION;
786 ln_gre_id number;
787 ln_rec_count number;
788
789 lv_batch_group_exists varchar2(2);
790 lv_batch_line_exists varchar2(2);
791 l_internal_display_funct_val varchar2(60);
792 l_input_value_counter number := 0;
793 l_exception_message varchar2(100);
794 l_exception_id number;
795 l_effective_start_date date;
796 l_effective_end_date date;
797 l_default_jd varchar2(2);
798
799 l_costing_flag varchar2(1);
800 l_prepay_flag varchar2(1);
801 l_old_prepay_flag varchar2(1);
802
803 l_leg_code varchar2(2);
804 l_element_information_type varchar2(10);
805
806 ln_segment1 number;
807 ln_segment11 number;
808 ln_segment12 number;
809 ln_ca_gre number;
810
811 -- Bug: 5212904 (Issue# 3)
812
813 l_batch_line_exists varchar2(2);
814 l_user_modified_batch_grp varchar2(2);
815 ln_old_assignment_id per_assignments_f.assignment_id%TYPE;
816 l_old_consolidation_set_id number;
817 l_old_effective_date date;
818 l_old_prepay_flag varchar2(1);
819 l_old_batch_group_status varchar2(10);
820 l_old_batch_group_id number;
821
822 l_date_input date;
823
824 /* MAIN */
825
826 Begin
827
828 --hr_utility.trace_on(null,'ram');
829 hr_utility.trace('p_batch_id is : '|| p_batch_id );
830 hr_utility.trace('p_batch_name is : '|| p_batch_name );
831 hr_utility.trace('p_effective_date is : '|| p_effective_date );
832 hr_utility.trace('p_mode is : '|| p_mode );
833 hr_utility.trace('p_batch_group_id is : '|| p_batch_group_id );
834 hr_utility.trace('p_batch_line_id is : '|| p_batch_line_id );
835 hr_utility.trace('p_batch_line_status is : '|| p_batch_line_status );
836 hr_utility.trace('p_employee_id is : '|| p_employee_id);
837 hr_utility.trace('p_assignment_id is : '|| p_assignment_id );
838 hr_utility.trace('p_element_name is : '|| p_element_name );
839 hr_utility.trace('p_element_type_id is : '|| p_element_type_id);
840 hr_utility.trace('p_element_link_id is : '|| p_element_link_id );
841 hr_utility.trace('p_payroll_id is : '|| p_payroll_id );
842 hr_utility.trace('p_business_group_id is : '|| p_business_group_id );
843 hr_utility.trace('p_consolidation_set_id is : '|| p_consolidation_set_id);
844 hr_utility.trace('p_gre_id is : '|| p_gre_id );
845 hr_utility.trace('p_prepay_flag is : '||p_prepay_flag );
846 hr_utility.trace('p_costing_flag is : '|| p_costing_flag );
847 hr_utility.trace('p_cost_allocation_keyflex is : '|| p_cost_allocation_keyflex );
848 hr_utility.trace('p_concatenated_segments is : '|| p_concatenated_segments );
849 hr_utility.trace('segment1 is : '|| segment1 );
850 hr_utility.trace('segment2 is : '|| segment2 );
851 hr_utility.trace('segment3 is : '|| segment3 );
852 hr_utility.trace('segment4 is : '|| segment4 );
853 hr_utility.trace('segment5 is : '|| segment5 );
854 hr_utility.trace('segment6 is : '|| segment6 );
855 hr_utility.trace('segment7 is : '|| segment7 );
856 hr_utility.trace('segment8 is : '|| segment8 );
857 hr_utility.trace('segment9 is : '|| segment9 );
858 hr_utility.trace('segment10 is : '||segment10 );
859 hr_utility.trace('segment11 is : '||segment11 );
860 hr_utility.trace('segment12 is : '||segment12 );
861 hr_utility.trace('segment13 is : '||segment13 );
862 hr_utility.trace('segment14 is : '||segment14 );
863 hr_utility.trace('segment15 is : '||segment15 );
864 hr_utility.trace('segment16 is : '|| segment16);
865 hr_utility.trace('segment17 is : '|| segment17 );
866 hr_utility.trace('segment18 is : '|| segment18 );
867 hr_utility.trace('segment19 is : '|| segment19 );
868 hr_utility.trace('segment20 is : '|| segment20 );
869 hr_utility.trace('segment21 is : '|| segment21 );
870 hr_utility.trace('segment22 is : '|| segment22 );
871 hr_utility.trace('segment23 is : '|| segment23 );
872 hr_utility.trace('segment24 is : '|| segment24 );
873 hr_utility.trace('segment25 is : '||segment25 );
874 hr_utility.trace('segment26 is : '||segment26 );
875 hr_utility.trace('segment27 is : '||segment27 );
876 hr_utility.trace('segment28 is : '||segment28 );
877 hr_utility.trace('segment29 is : '||segment29 );
878 hr_utility.trace('segment30 is : '||segment30 );
879 hr_utility.trace('p_ee_value1 is : '|| p_ee_value1 );
880 hr_utility.trace('p_ee_value2 is : '|| p_ee_value2 );
881 hr_utility.trace('p_ee_value3 is : '|| p_ee_value3 );
882 hr_utility.trace('p_ee_value4 is : '|| p_ee_value4 );
883 hr_utility.trace('p_ee_value5 is : '|| p_ee_value5 );
884 hr_utility.trace('p_ee_value6 is : '|| p_ee_value6 );
885 hr_utility.trace('p_ee_value7 is : '|| p_ee_value7 );
886 hr_utility.trace('p_ee_value8 is : '|| p_ee_value8 );
887 hr_utility.trace('p_ee_value9 is : '|| p_ee_value9 );
888 hr_utility.trace('p_ee_value10 is : '||p_ee_value10 );
889 hr_utility.trace('p_ee_value11 is : '||p_ee_value11 );
890 hr_utility.trace('p_ee_value12 is : '||p_ee_value12 );
891 hr_utility.trace('p_ee_value13 is : '||p_ee_value13 );
892 hr_utility.trace('p_ee_value14 is : '||p_ee_value14 );
893 hr_utility.trace('p_ee_value15 is : '||p_ee_value15 );
894 hr_utility.trace('p_col1 is : '|| p_col1 );
895 hr_utility.trace('p_col2 is : '|| p_col2 );
896 hr_utility.trace('p_col3 is : '|| p_col3 );
897 hr_utility.trace('p_col4 is : '|| p_col4 );
898 hr_utility.trace('p_col5 is : '|| p_col5 );
899 hr_utility.trace('p_col_val1 is : '|| p_col_val1 );
900 hr_utility.trace('p_col_val2 is : '|| p_col_val2 );
901 hr_utility.trace('p_col_val3 is : '|| p_col_val3 );
902 hr_utility.trace('p_col_val4 is : '|| p_col_val4 );
903 hr_utility.trace('p_col_val5 is : '|| p_col_val5 );
904 hr_utility.set_location('p_col_val5 is : '|| p_col_val5,10 );
905
906 -- Bug: 5200900
907 -- Storing input values in Global variables.
908
909 g_ee_value1 := p_ee_value1;
910 g_ee_value2 := p_ee_value2;
911 g_ee_value3 := p_ee_value3;
912 g_ee_value4 := p_ee_value4;
913 g_ee_value5 := p_ee_value5;
914 g_ee_value6 := p_ee_value6;
915 g_ee_value7 := p_ee_value7;
916 g_ee_value8 := p_ee_value8;
917 g_ee_value9 := p_ee_value9;
918 g_ee_value10 := p_ee_value10;
919 g_ee_value11 := p_ee_value11;
920 g_ee_value12 := p_ee_value12;
921 g_ee_value13 := p_ee_value13;
922 g_ee_value14 := p_ee_value14;
923 g_ee_value15 := p_ee_value15;
924
925 if p_costing_flag is null then
926 hr_utility.trace('p_costing_flag is null satisfied');
927 l_costing_flag := 'N';
928 else
929 l_costing_flag := p_costing_flag;
930 end if;
931
932
933 if p_prepay_flag is null then
934 hr_utility.trace('p_prepay_flag is null satisfied');
935 l_prepay_flag := 'N';
936 else
937 l_prepay_flag := p_prepay_flag;
938 end if;
939
940 if g_element_type_id is null then
941 hr_utility.trace('g_element_type_id is null satisfied ');
942 g_element_type_id := -1;
943 end if;
944
945 i := 0;
946 ln_rec_count := 0;
947
948 open c_get_payroll_id;
949 fetch c_get_payroll_id into ln_payroll_id;
950 close c_get_payroll_id;
951
952 -- If payroll is not attached, raise an error.
953
954 if ln_payroll_id is null then
955 hr_utility.trace('ln_payroll_id is null satisfied');
956 hr_utility.raise_error;
957 end if;
958
959 OPEN csr_get_asg_id;
960 FETCH csr_get_asg_id into ln_assignment_id;
961 IF csr_get_asg_id%NOTFOUND THEN
962 hr_utility.raise_error;
963 END IF;
964 CLOSE csr_get_asg_id;
965 hr_utility.trace('Assignment_id :'||to_char(ln_assignment_id));
966
967 -- Fetch Consolidation Set Id if user did not enter it.
968
969 if l_consolidation_set_id is null then
970 open c_get_consolidation_set_id;
971 fetch c_get_consolidation_set_id into l_consolidation_set_id;
972 close c_get_consolidation_set_id;
973 end if;
974
975 -- Fetch Legislation code.
976
977 open get_legislation_code;
978 fetch get_legislation_code into l_leg_code;
979 close get_legislation_code;
980
981 -- Check the input value has lookup and
982
983 -- Defaulting the Jurisdiction value if user did not enter it.
984
985 IF l_leg_code = 'CA' THEN
986
987 l_default_jd := pay_ca_emp_tax_inf.get_tax_detail_char
988 (ln_assignment_id,
989 null,
990 null,
991 p_effective_date,
992 'EMPPROV');
993
994 hr_utility.trace('l_default_jd: ' || l_default_jd);
995
996 END IF;
997
998 -- Fetch GRE name if user did not enter it.
999 -- Modified to handle multiple GREs for CA legislation.
1000
1001 if p_gre_id is null then
1002
1003 IF l_leg_code = 'CA' then
1004
1005 hr_utility.trace('l_leg_code = CA is satisfied');
1006 hr_utility.trace('Opening cursor: get_gre_ca ...');
1007
1008 open get_gre_ca;
1009 fetch get_gre_ca into ln_segment1, ln_segment11,
1010 ln_segment12,ln_ca_gre;
1011 close get_gre_ca;
1012
1013 OPEN get_element_type;
1014 FETCH get_element_type into l_element_information_type;
1015
1016 IF get_element_type%NOTFOUND THEN
1017
1018 ln_gre_id := ln_ca_gre;
1019
1020 ELSE
1021
1022 IF l_element_information_type = 'T4/RL1' THEN
1023
1024 hr_utility.trace('l_element_information_type = T4/RL1');
1025 ln_gre_id := ln_segment1;
1026
1027 ELSIF l_element_information_type = 'T4A/RL1' THEN
1028
1029 hr_utility.trace('l_element_information_type = T4A/RL1');
1030 ln_gre_id := ln_segment11;
1031
1032 ELSIF l_element_information_type = 'T4A/RL2' THEN
1033
1034 hr_utility.trace('l_element_information_type = T4A/RL2');
1035 ln_gre_id := ln_segment12;
1036
1037 END IF;
1038
1039 IF ln_gre_id is null THEN
1040
1041 ln_gre_id := ln_ca_gre;
1042
1043 END IF;
1044
1045 END IF;
1046
1047 CLOSE get_element_type;
1048
1049 else
1050 hr_utility.trace('l_leg_code != CA is satisfied');
1051 open get_gre;
1052 fetch get_gre into ln_gre_id;
1053 close get_gre;
1054 end if;
1055
1056 else
1057 open c_get_gre_id(p_business_group_id,p_gre_id);
1058 fetch c_get_gre_id into ln_gre_id;
1059 close c_get_gre_id;
1060 end if;
1061
1062
1063 hr_utility.trace('GRE_Id :'||to_char(ln_gre_id));
1064 hr_utility.trace('Payroll Id : '||to_char(ln_payroll_id));
1065 hr_utility.trace('G_Element_type_Id : '||to_char(g_element_type_id));
1066 hr_utility.trace('G_ip_id1 : '||to_char(g_ip_id1));
1067 hr_utility.trace('G_ip_id2 : '||to_char(g_ip_id2));
1068 hr_utility.trace('G_ip_id3 : '||to_char(g_ip_id3));
1069 hr_utility.trace('G_ip_id4 : '||to_char(g_ip_id4));
1070 hr_utility.trace('G_ip_id5 : '||to_char(g_ip_id5));
1071 hr_utility.trace('G_ip_id6 : '||to_char(g_ip_id6));
1072 hr_utility.trace('G_ip_id7 : '||to_char(g_ip_id7));
1073 hr_utility.trace('G_ip_id8 : '||to_char(g_ip_id8));
1074 hr_utility.trace('G_ip_id9 : '||to_char(g_ip_id9));
1075 hr_utility.trace('G_ip_id10 : '||to_char(g_ip_id10));
1076 hr_utility.trace('G_ip_id11 : '||to_char(g_ip_id11));
1077 hr_utility.trace('G_ip_id12 : '||to_char(g_ip_id12));
1078 hr_utility.trace('G_ip_id13 : '||to_char(g_ip_id13));
1079 hr_utility.trace('G_ip_id14 : '||to_char(g_ip_id14));
1080 hr_utility.trace('G_ip_id15 : '||to_char(g_ip_id15));
1081
1082 -- for the element find the input value ids
1083
1084
1085 if g_element_type_id <> p_element_type_id then
1086 -- Bug : 5079557
1087 -- Get the Cost Allocation Keyflex num
1088 open c_get_caflexnum(p_business_group_id);
1089 fetch c_get_caflexnum into g_flex_num;
1090 close c_get_caflexnum;
1091
1092 hr_utility.trace('g_element_type_id <> p_element_type_id satisfied ');
1093 g_element_type_id := p_element_type_id;
1094
1095 open c_get_input_value_id(p_element_type_id,p_effective_date);
1096 loop
1097 fetch c_get_input_value_id into l_inp_id, l_inp_name,l_inp_ds;
1098
1099 hr_utility.trace('Input_value_id is : '|| to_char(l_inp_id));
1100 hr_utility.trace('Input value Name is : '|| l_inp_name);
1101 hr_utility.trace('Input value sequence is : '|| to_char(l_inp_ds));
1102
1103 exit when c_get_input_value_id%NOTFOUND;
1104
1105 i := i + 1 ;
1106 if i =1 then
1107
1108 g_ip_id1 := l_inp_id;
1109
1110 if l_inp_name = 'Jurisdiction' then
1111 g_display_sequence := l_inp_ds;
1112 end if;
1113
1114 elsif i = 2 then
1115
1116 g_ip_id2 := l_inp_id;
1117
1118 if l_inp_name = 'Jurisdiction' then
1119 g_display_sequence := l_inp_ds;
1120 end if;
1121
1122 elsif i = 3 then
1123
1124 g_ip_id3 := l_inp_id;
1125
1126 if l_inp_name = 'Jurisdiction' then
1127 g_display_sequence := l_inp_ds;
1128 end if;
1129
1130 elsif i = 4 then
1131
1132 g_ip_id4 := l_inp_id;
1133
1134 if l_inp_name = 'Jurisdiction' then
1135 g_display_sequence := l_inp_ds;
1136 end if;
1137
1138 elsif i = 5 then
1139
1140 g_ip_id5 := l_inp_id;
1141
1142 if l_inp_name = 'Jurisdiction' then
1143 g_display_sequence := l_inp_ds;
1144 end if;
1145
1146 elsif i = 6 then
1147
1148 g_ip_id6 := l_inp_id;
1149
1150 if l_inp_name = 'Jurisdiction' then
1151 g_display_sequence := l_inp_ds;
1152 end if;
1153
1154 elsif i = 7 then
1155
1156 g_ip_id7 := l_inp_id;
1157
1158 if l_inp_name = 'Jurisdiction' then
1159 g_display_sequence := l_inp_ds;
1160 end if;
1161
1162 elsif i = 8 then
1163
1164 g_ip_id8 := l_inp_id;
1165
1166 if l_inp_name = 'Jurisdiction' then
1167 g_display_sequence := l_inp_ds;
1168 end if;
1169
1170 elsif i = 9 then
1171
1172 g_ip_id9 := l_inp_id;
1173
1174 if l_inp_name = 'Jurisdiction' then
1175 g_display_sequence := l_inp_ds;
1176 end if;
1177
1178 elsif i = 10 then
1179
1180 g_ip_id10 := l_inp_id;
1181
1182 if l_inp_name = 'Jurisdiction' then
1183 g_display_sequence := l_inp_ds;
1184 end if;
1185
1186 elsif i = 11 then
1187
1188 g_ip_id11 := l_inp_id;
1189
1190 if l_inp_name = 'Jurisdiction' then
1191 g_display_sequence := l_inp_ds;
1192 end if;
1193
1194 elsif i = 12 then
1195
1196 g_ip_id12 := l_inp_id;
1197
1198 if l_inp_name = 'Jurisdiction' then
1199 g_display_sequence := l_inp_ds;
1200 end if;
1201
1202 elsif i = 13 then
1203
1204 g_ip_id13 := l_inp_id;
1205
1206 if l_inp_name = 'Jurisdiction' then
1207 g_display_sequence := l_inp_ds;
1208 end if;
1209
1210 elsif i = 14 then
1211
1212 g_ip_id14 := l_inp_id;
1213
1214 if l_inp_name = 'Jurisdiction' then
1215 g_display_sequence := l_inp_ds;
1216 end if;
1217
1218 elsif i = 15 then
1219
1220 g_ip_id15 := l_inp_id;
1221
1222 if l_inp_name = 'Jurisdiction' then
1223 g_display_sequence := l_inp_ds;
1224 end if;
1225
1226
1227 end if;
1228 -- store in global variables to be used later
1229
1230 end loop;
1231 close c_get_input_value_id;
1232
1233 hr_utility.trace('Done with c_get_input_value_id cursor ');
1234
1235 end if; -- g_element_type_id <> p_element_type_id
1236
1237
1238 if l_leg_code = 'CA' then
1239 CASE g_display_sequence
1240
1241 WHEN 1 THEN IF g_ee_value1 is null THEN
1242 g_ee_value1 := l_default_jd;
1243 hr_utility.trace('Modifed g_ee_value1: ' || g_ee_value1);
1244 END IF;
1245 WHEN 2 THEN IF g_ee_value2 is null THEN
1246 g_ee_value2 := l_default_jd;
1247 hr_utility.trace('Modifed l_ee_value2: ' || g_ee_value2);
1248 END IF;
1249 WHEN 3 THEN IF g_ee_value3 is null THEN
1250 g_ee_value3 := l_default_jd;
1251 hr_utility.trace('Modifed g_ee_value3: ' || g_ee_value3);
1252 END IF;
1253 WHEN 4 THEN IF g_ee_value4 is null THEN
1254 g_ee_value4 := l_default_jd;
1255 hr_utility.trace('Modifed g_ee_value4: ' || g_ee_value4);
1256 END IF;
1257 WHEN 5 THEN IF g_ee_value5 is null THEN
1258 g_ee_value5 := l_default_jd;
1259 hr_utility.trace('Modifed g_ee_value5: ' || g_ee_value5);
1260 END IF;
1261 WHEN 6 THEN IF g_ee_value6 is null THEN
1262 g_ee_value6 := l_default_jd;
1263 hr_utility.trace('Modifed g_ee_value6: ' || g_ee_value6);
1264 END IF;
1265 WHEN 7 THEN IF g_ee_value7 is null THEN
1266 g_ee_value7 := l_default_jd;
1267 hr_utility.trace('Modifed g_ee_value7: ' || g_ee_value7);
1268 END IF;
1269 WHEN 8 THEN IF g_ee_value8 is null THEN
1270 g_ee_value8 := l_default_jd;
1271 hr_utility.trace('Modifed g_ee_value8: ' || g_ee_value8);
1272 END IF;
1273 WHEN 9 THEN IF g_ee_value9 is null THEN
1274 g_ee_value9 := l_default_jd;
1275 hr_utility.trace('Modifed g_ee_value9: ' || g_ee_value9);
1276 END IF;
1277 WHEN 10 THEN IF g_ee_value10 is null THEN
1278 g_ee_value10 := l_default_jd;
1279 hr_utility.trace('Modifed g_ee_value10: ' || g_ee_value10);
1280 END IF;
1281 WHEN 11 THEN IF g_ee_value11 is null THEN
1282 g_ee_value11 := l_default_jd;
1283 hr_utility.trace('Modifed g_ee_value11: ' || g_ee_value11);
1284 END IF;
1285 WHEN 12 THEN IF g_ee_value12 is null THEN
1286 g_ee_value12 := l_default_jd;
1287 hr_utility.trace('Modifed g_ee_value12: ' || g_ee_value12);
1288 END IF;
1289 WHEN 13 THEN IF g_ee_value13 is null THEN
1290 g_ee_value13 := l_default_jd;
1291 hr_utility.trace('Modifed g_ee_value13: ' || g_ee_value13);
1292 END IF;
1293 WHEN 14 THEN IF g_ee_value14 is null THEN
1294 g_ee_value14 := l_default_jd;
1295 hr_utility.trace('Modifed g_ee_value14: ' || g_ee_value14);
1296 END IF;
1297 WHEN 15 THEN IF g_ee_value15 is null THEN
1298 g_ee_value15 := l_default_jd;
1299 hr_utility.trace('Modifed g_ee_value15: ' || g_ee_value15);
1300 END IF;
1301 END CASE;
1302
1303 end if;
1304
1305 -- Added for Bug: 5079530
1306 begin
1307 l_input_value_counter := 1;
1308 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value1, 1, sysdate, p_batch_id, 'P');
1309 l_input_value_counter := l_input_value_counter +1;
1310 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value2, 2, sysdate, p_batch_id, 'P');
1311 l_input_value_counter := l_input_value_counter +1;
1312 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value3, 3, sysdate, p_batch_id, 'P');
1313 l_input_value_counter := l_input_value_counter +1;
1314 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value4, 4, sysdate, p_batch_id, 'P');
1315 l_input_value_counter := l_input_value_counter +1;
1316 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value5, 5, sysdate, p_batch_id, 'P');
1317 l_input_value_counter := l_input_value_counter +1;
1318 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value6, 6, sysdate, p_batch_id, 'P');
1319 l_input_value_counter := l_input_value_counter +1;
1320 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value7, 7, sysdate, p_batch_id, 'P');
1321 l_input_value_counter := l_input_value_counter +1;
1322 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value8, 8, sysdate, p_batch_id, 'P');
1323 l_input_value_counter := l_input_value_counter +1;
1324 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value9, 9, sysdate, p_batch_id, 'P');
1325 l_input_value_counter := l_input_value_counter +1;
1326 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value10,10, sysdate, p_batch_id, 'P');
1327 l_input_value_counter := l_input_value_counter +1;
1328 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value11,11, sysdate, p_batch_id, 'P');
1329 l_input_value_counter := l_input_value_counter +1;
1330 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value12,12, sysdate, p_batch_id, 'P');
1331 l_input_value_counter := l_input_value_counter +1;
1332 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value13,13, sysdate, p_batch_id, 'P');
1333 l_input_value_counter := l_input_value_counter +1;
1334 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value14,14, sysdate, p_batch_id, 'P');
1335 l_input_value_counter := l_input_value_counter +1;
1336 l_internal_display_funct_val := convert_internal_to_display(p_element_type_id, p_ee_value15,15, sysdate, p_batch_id, 'P');
1337
1338 exception
1339
1340 when others then
1341 hr_utility.trace('#################################################');
1342 hr_utility.trace('ERROR while uploading input values');
1343 hr_utility.trace('Enter valide input values for element input types');
1344 hr_utility.trace('#################################################');
1345 --hr_utility.set_message('PAY','PAY_449776_INPUT_VALUE_FORMAT');
1346 --hr_utility.set_message('PAY','PAY_6306_INPUT_VALUE_FORMAT');
1347
1348 case l_input_value_counter
1349 when 1 then l_exception_id := g_ip_id1;
1350 when 2 then l_exception_id := g_ip_id2;
1351 when 3 then l_exception_id := g_ip_id3;
1352 when 4 then l_exception_id := g_ip_id4;
1353 when 5 then l_exception_id := g_ip_id5;
1354 when 6 then l_exception_id := g_ip_id6;
1355 when 7 then l_exception_id := g_ip_id7;
1356 when 8 then l_exception_id := g_ip_id8;
1357 when 9 then l_exception_id := g_ip_id9;
1358 when 10 then l_exception_id := g_ip_id10;
1359 when 11 then l_exception_id := g_ip_id11;
1360 when 12 then l_exception_id := g_ip_id12;
1361 when 13 then l_exception_id := g_ip_id13;
1362 when 14 then l_exception_id := g_ip_id14;
1363 when 15 then l_exception_id := g_ip_id15;
1364 end case;
1365
1366 hr_utility.trace('l_exception_id: '|| l_exception_id);
1367
1368 SELECT name INTO l_exception_message
1369 FROM pay_input_values_f
1370 WHERE element_type_id= p_element_type_id
1371 AND p_effective_date between effective_start_date and effective_end_date
1372 AND input_value_id = l_exception_id
1373 ORDER BY display_sequence, name;
1374
1375 hr_utility.trace('l_exception_message: '|| l_exception_message);
1376
1377 hr_utility.set_message('PAY',l_exception_message);
1378
1379 --hr_utility.set_message_token('COLUMN', l_exception_message);
1380 hr_utility.trace('l_exception_message:'||l_exception_id);
1381 hr_utility.raise_error;
1382 end;
1383
1384 -- This moved to above condition.
1385 -- Bug: 5079557
1386 /*
1387 -- Get the Cost Allocation Keyflex num
1388 open c_get_caflexnum(p_business_group_id);
1389 fetch c_get_caflexnum into g_flex_num;
1390 close c_get_caflexnum;
1391 **/
1392
1393 -- Bug: 5212904
1394
1395 /*
1396 Check if batch_line_id already exists. Then fetch its batch_group_id.
1397 Check whether consolidation_set_id, effective_date, prepay flag,
1398 Employee name and assignment number have been modified.
1399 */
1400 IF l_batch_line_id IS NOT NULL THEN
1401
1402 OPEN csr_get_batch_line_details;
1403 FETCH csr_get_batch_line_details INTO l_batch_line_status, l_old_batch_group_id, ln_old_assignment_id;
1404 IF csr_get_batch_line_details%NOTFOUND THEN
1405 l_batch_line_exists := 'N';
1406 ELSE
1407 l_batch_line_exists := 'Y';
1408 END IF;
1409 CLOSE csr_get_batch_line_details;
1410
1411 hr_utility.trace('l_batch_line_exists: ' || l_batch_line_exists);
1412
1413 /* If we need to raise an error if user updates the above mentioned columns we can reuse the following code.
1414
1415 IF l_batch_line_exists = 'Y' THEN
1416 IF ln_old_assignment_id != ln_assignment_id THEN
1417 hr_utility.trace('ln_old_assignment_id: ' || ln_old_assignment_id);
1418 hr_utility.trace('ln_assignment_id: ' || ln_assignment_id);
1419 hr_utility.trace('ERROR: Assignment Number can not be modified');
1420 hr_utility.raise_error;
1421 ELSE
1422 OPEN csr_get_batch_group_details(l_old_batch_group_id);
1423 FETCH csr_get_batch_group_details INTO l_old_batch_group_status, l_old_consolidation_set_id, l_old_effective_date, l_old_prepay_flag;
1424 CLOSE csr_get_batch_group_details;
1425
1426 IF l_old_consolidation_set_id != l_consolidation_set_id
1427 OR l_old_effective_date != p_effective_date
1428 OR l_old_prepay_flag != l_prepay_flag THEN
1429 l_user_modified_batch_grp := 'Y';
1430 END IF;
1431 END IF;
1432 END IF;
1433 */
1434 END IF;
1435
1436 /*
1437 Get the Pl/SQL table values and check see if a row exists in the
1438 table for the combination of
1439 - consolidation_set_id
1440 - payroll_id
1441 - effective_date
1442 - prepay flag
1443 - batch_id
1444 */
1445
1446
1447
1448
1449 hr_utility.trace('lv_batch_group_exists'|| lv_batch_group_exists);
1450 lv_batch_group_exists := 'N';
1451
1452 hr_utility.trace('pay_batch_balanceadj_wrapper.gtr_batch_group_data.count'||pay_batch_balanceadj_wrapper.gtr_batch_group_data.count);
1453 hr_utility.trace('lv_batch_group_exists'|| lv_batch_group_exists);
1454
1455 begin
1456
1457 hr_utility.trace('p_batch_id-->'||p_batch_id);
1458 hr_utility.trace('l_consolidation_set_id-->' || l_consolidation_set_id);
1459 hr_utility.trace('ln_payroll_id -->' || ln_payroll_id);
1460 hr_utility.trace('p_effective_date -->' || p_effective_date);
1461 hr_utility.trace('l_prepay_flag -->' || l_prepay_flag);
1462
1463 OPEN csr_check_batch_group(p_batch_id,l_consolidation_set_id,ln_payroll_id,p_effective_date,l_prepay_flag);
1464 FETCH csr_check_batch_group INTO l_batch_group_id, l_batch_group_status;
1465 IF csr_check_batch_group%NOTFOUND THEN
1466 lv_batch_group_exists := 'N';
1467 ELSE
1468 lv_batch_group_exists := 'Y';
1469 END IF;
1470 CLOSE csr_check_batch_group;
1471
1472 if lv_batch_group_exists = 'N' then
1473
1474 hr_utility.trace('No data found for pay_adjust_batch_groups ');
1475 select pay_adjust_batch_groups_s.nextval into l_batch_group_id
1476 from dual;
1477
1478 insert into pay_adjust_batch_groups
1479 (batch_group_id,
1480 batch_id,
1481 batch_group_status,
1482 consolidation_set_id,
1483 payroll_id,
1484 effective_date,
1485 prepay_flag)
1486 values (l_batch_group_id,
1487 p_batch_id,
1488 'U',
1489 l_consolidation_set_id,
1490 ln_payroll_id,
1491 p_effective_date,
1492 l_prepay_flag);
1493
1494 l_batch_group_status := 'U';
1495 hr_utility.trace('Done inserting into pay_adjust_batch_groups table');
1496 hr_utility.trace('New Batch Group id: '||to_char(l_batch_group_id));
1497
1498 end if; /* lv_batch_group_exists = 'N' */
1499
1500 end;
1501
1502
1503 /*
1504 If value is found then check if we can add in new lines else raise an erorr
1505 that the batch/group/line is closed for update/insert.
1506
1507 cannot create new batch lines if the group status is
1508 'L' --> ??
1509 'T' --> Transferred
1510 'C' --> Completed
1511 */
1512
1513 hr_utility.trace('l_batch_group_status : '||l_batch_group_status);
1514
1515 if l_batch_group_status not in ('L','T','C') then
1516
1517 /* check if batch line exists */
1518 hr_utility.trace('l_batch_group_status satisfied not in L, T, C ');
1519
1520 begin
1521
1522 IF l_batch_line_id IS NULL THEN
1523 OPEN csr_check_batch_line(p_batch_id, l_batch_group_id, ln_assignment_id, p_element_type_id);
1524 FETCH csr_check_batch_line into l_batch_line_id, l_batch_line_status;
1525 IF csr_check_batch_line%NOTFOUND THEN
1526 l_batch_line_exists := 'N';
1527 ELSE
1528 l_batch_line_exists := 'Y';
1529 END IF;
1530 CLOSE csr_check_batch_line;
1531 END IF;
1532
1533 hr_utility.trace('l_batch_line_id : '|| l_batch_line_id);
1534 hr_utility.trace('l_batch_line_status : '||l_batch_line_status);
1535
1536
1537 IF l_batch_line_exists = 'Y' THEN
1538
1539 if l_batch_line_status in ('C','T') then
1540
1541 hr_utility.trace('l_batch_line_status is C, T raise exception ');
1542 raise ex_cannot_update_bl; -- cannot update a completed/ transferred line
1543
1544 else
1545
1546 if l_costing_flag = 'Y' then
1547
1548 hr_utility.trace('Costing Flag is set to Yes ');
1549 l_cakff_id := get_costing_info(p_concatenated_segments,
1550 segment1,segment2,segment3,segment4,segment5,
1551 segment6,segment7,segment8,segment9,segment10,
1552 segment11,segment12,segment13,segment14,segment15,
1553 segment16,segment17,segment18,segment19,segment20,
1554 segment21,segment22,segment23,segment24,segment25,
1555 segment26,segment27,segment28,segment29,segment30);
1556
1557 hr_utility.trace('l_cakff_id :'||to_char(l_cakff_id));
1558
1559 end if;
1560
1561 hr_utility.trace('Update of pay_adjust_batch_lines ');
1562
1563 UPDATE pay_adjust_batch_lines
1564 SET entry_value1 = g_ee_value1,
1565 entry_value2 = g_ee_value2,
1566 entry_value3 = g_ee_value3,
1567 entry_value4 = g_ee_value4,
1568 entry_value5 = g_ee_value5,
1569 entry_value6 = g_ee_value6,
1570 entry_value7 = g_ee_value7,
1571 entry_value8 = g_ee_value8,
1572 entry_value9 = g_ee_value9,
1573 entry_value10 = g_ee_value10,
1574 entry_value11 = g_ee_value11,
1575 entry_value12 = g_ee_value12,
1576 entry_value13 = g_ee_value13,
1577 entry_value14 = g_ee_value14,
1578 entry_value15 = g_ee_value15,
1579 balance_adj_cost_flag = l_costing_flag,
1580 cost_allocation_keyflex_id = l_cakff_id,
1581 tax_unit_id = ln_gre_id,
1582 batch_line_status = 'U',
1583 batch_group_id = l_batch_group_id,
1584 assignment_id = ln_assignment_id
1585 WHERE batch_line_id = l_batch_line_id;
1586
1587 end if; /*End of If l_batch_line_status in ('C','T') */
1588
1589 else
1590 hr_utility.trace('No data found for pay_adjust_batch_lines ');
1591
1592 IF l_costing_flag = 'Y' THEN
1593 hr_utility.trace('Costing Flag is set to Yes ');
1594 l_cakff_id := get_costing_info(p_concatenated_segments,
1595 segment1,segment2,segment3,segment4,segment5,
1596 segment6,segment7,segment8,segment9,segment10,
1597 segment11,segment12,segment13,segment14,segment15,
1598 segment16,segment17,segment18,segment19,segment20,
1599 segment21,segment22,segment23,segment24,segment25,
1600 segment26,segment27,segment28,segment29,segment30);
1601 hr_utility.trace('l_cakff_id :'||to_char(l_cakff_id));
1602 END IF;
1603
1604 hr_utility.trace('inserting into pay_adjust_batch_lines table');
1605 insert into pay_adjust_batch_lines(batch_line_id,
1606 batch_id,
1607 batch_line_status,
1608 batch_group_id,
1609 batch_line_sequence,
1610 assignment_id,
1611 element_type_id,
1612 input_value_id1,
1613 input_value_id2,
1614 input_value_id3,
1615 input_value_id4,
1616 input_value_id5,
1617 input_value_id6,
1618 input_value_id7,
1619 input_value_id8,
1620 input_value_id9,
1621 input_value_id10,
1622 input_value_id11,
1623 input_value_id12,
1624 input_value_id13,
1625 input_value_id14,
1626 input_value_id15,
1627 entry_value1,
1628 entry_value2,
1629 entry_value3,
1630 entry_value4,
1631 entry_value5,
1632 entry_value6,
1633 entry_value7,
1634 entry_value8,
1635 entry_value9,
1636 entry_value10,
1637 entry_value11,
1638 entry_value12,
1639 entry_value13,
1640 entry_value14,
1641 entry_value15,
1642 balance_adj_cost_flag,
1643 cost_allocation_keyflex_id,
1644 tax_unit_id)
1645 values (pay_adjust_batch_lines_s.nextval,
1646 p_batch_id,
1647 'U',
1648 l_batch_group_id,
1649 1,
1650 ln_assignment_id,
1651 p_element_type_id,
1652 g_ip_id1,
1653 g_ip_id2,
1654 g_ip_id3,
1655 g_ip_id4,
1656 g_ip_id5,
1657 g_ip_id6,
1658 g_ip_id7,
1659 g_ip_id8,
1660 g_ip_id9,
1661 g_ip_id10,
1662 g_ip_id11,
1663 g_ip_id12,
1664 g_ip_id13,
1665 g_ip_id14,
1666 g_ip_id15,
1667 g_ee_value1,
1668 g_ee_value2,
1669 g_ee_value3,
1670 g_ee_value4,
1671 g_ee_value5,
1672 g_ee_value6,
1673 g_ee_value7,
1674 g_ee_value8,
1675 g_ee_value9,
1676 g_ee_value10,
1677 g_ee_value11,
1678 g_ee_value12,
1679 g_ee_value13,
1680 g_ee_value14,
1681 g_ee_value15,
1682 l_costing_flag,
1683 l_cakff_id,
1684 ln_gre_id);
1685
1686 END IF; /* End if lv_batch_line_exists = 'Y' */
1687
1688 END; /* End of block started at l_batch_group_status not in ('L','T','C') */
1689
1690 else /* l_batch_group_status */
1691
1692 hr_utility.trace('raising exception l_batch_group_status : '||l_batch_group_status);
1693 raise ex_cannot_update_bg;
1694
1695 end if; /* l_batch_group_status */
1696
1697 exception
1698
1699 when ex_cannot_update_bg then
1700
1701 hr_utility.trace('Batch Group is either Transferred or Complete, cannot update the batch');
1702
1703 when ex_cannot_update_bl then
1704
1705 hr_utility.trace('Batch Line is either Transferred or Complete, cannot update the batch');
1706
1707
1708 --hr_utility.trace_off;
1709 end;
1710
1711 /*
1712 Function to be used to display input values in correct format
1713 for BBA Spreadsheet correct errros page. Used in Cotent queupry .
1714 */
1715
1716 function convert_internal_to_display
1717 (p_element_type_id in varchar2,
1718 p_input_value in varchar2,
1719 p_input_value_number in number,
1720 p_session_date in date,
1721 p_batch_id in number,
1722 p_calling_mode in varchar2
1723 ) return varchar2 is
1724 --
1725 --
1726 l_bee_iv_upgrade varchar2(1);
1727 --
1728 l_display_value varchar2(60) ; -- := p_input_value;
1729 l_internal_value varchar2(60) := p_input_value;
1730 l_dummy varchar2(100);
1731 --
1732 l_uom_value pay_input_values_f.UOM%TYPE;
1733 l_lookup_type pay_input_values_f.LOOKUP_TYPE%TYPE;
1734 l_value_set_id pay_input_values_f.VALUE_SET_ID%TYPE;
1735 l_currency_code pay_element_types_f.input_currency_code%TYPE;
1736 l_count number;
1737 l_found number;
1738 l_rgeflg varchar2(2);
1739 --
1740 -- Bug: 5200900
1741 cursor csr_valid_lookup
1742 (p_lookup_type varchar2,
1743 p_meaning varchar2) IS
1744 SELECT HL.lookup_code
1745 FROM hr_lookups HL
1746 WHERE HL.lookup_type = p_lookup_type
1747 AND UPPER(HL.meaning) = UPPER(p_meaning);
1748
1749 cursor csr_valid_lookup_code
1750 (p_lookup_type varchar2,
1751 p_lookup_code varchar2) IS
1752 SELECT HL.meaning
1753 FROM hr_lookups HL
1754 WHERE HL.lookup_type = p_lookup_type
1755 AND HL.lookup_code = p_lookup_code;
1756 --
1757 cursor csr_iv is
1758 select inv.UOM,
1759 inv.LOOKUP_TYPE,
1760 inv.VALUE_SET_ID,
1761 etp.input_currency_code
1762 from pay_input_values_f inv,
1763 pay_element_types_f etp
1764 where inv.element_type_id = p_element_type_id
1765 and etp.element_type_id = p_element_type_id
1766 and p_session_date between inv.effective_start_date
1767 and inv.effective_end_date
1768 and p_session_date between etp.effective_start_date
1769 and etp.effective_end_date
1770 order by inv.display_sequence,inv.name;
1771
1772 CURSOR csr_input_value( p_element_type_id IN NUMBER
1773 , p_input_value_number IN NUMBER
1774 ) IS
1775 SELECT piv.uom
1776 , piv.lookup_type
1777 , piv.value_set_id
1778 , pet.input_currency_code
1779 FROM pay_input_values_f piv,
1780 pay_element_types_f pet
1781 WHERE piv.element_type_id = p_element_type_id
1782 AND pet.element_type_id = p_element_type_id
1783 AND piv.input_value_id = p_input_value_number
1784 AND p_session_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1785 AND p_session_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1786 ORDER BY piv.display_sequence,piv.name;
1787 --
1788 begin
1789 --hr_utility.trace_on(null,'RK');
1790 hr_utility.trace(' p_input_value ->' || p_input_value);
1791 hr_utility.trace(' p_element_type_id -> ' || p_element_type_id );
1792 hr_utility.trace(' p_input_value -> ' || p_input_value );
1793 hr_utility.trace(' p_input_value_id -> ' || p_input_value_number);
1794 hr_utility.trace(' p_session_date -> ' || p_session_date );
1795 hr_utility.trace(' p_batch_id -> ' || p_batch_id );
1796
1797 --
1798 if p_input_value is null then
1799 return p_input_value;
1800 end if;
1801 --
1802 l_count := 1;
1803 l_found := 0;
1804
1805 for p_iv_rec in csr_iv loop
1806 --
1807 if l_count = p_input_value_number then
1808 l_uom_value := p_iv_rec.uom;
1809 l_lookup_type := p_iv_rec.LOOKUP_TYPE;
1810 l_value_set_id := p_iv_rec.VALUE_SET_ID;
1811 l_currency_code := p_iv_rec.input_currency_code;
1812 --
1813 l_found := 1;
1814 exit;
1815 end if;
1816 --
1817 l_count := l_count + 1;
1818 --
1819 end loop;
1820 --
1821 if l_found = 0 then
1822 return p_input_value;
1823 end if;
1824
1825 /* OPEN csr_input_value(p_element_type_id, p_input_value_id);
1826 FETCH csr_input_value INTO l_uom_value, l_lookup_type, l_value_set_id, l_currency_code;
1827 IF csr_input_value%NOTFOUND THEN
1828 RETURN p_input_value;
1829 END IF; */
1830
1831
1832
1833 hr_utility.trace('======================================================');
1834 hr_utility.trace(' p_input_value_id -> ' || p_input_value_number);
1835 hr_utility.trace(' l_uom_value -> ' || l_uom_value );
1836 hr_utility.trace(' l_lookup_type -> ' || l_lookup_type );
1837 hr_utility.trace(' l_value_set_id -> ' || l_value_set_id);
1838 hr_utility.trace('======================================================');
1839
1840 --
1841 -- BBA now handles input value of date in canonical format.
1842 -- However the EE API expects the data in the DD-MON-YYYY format.
1843 -- The DD-MON-YYYY is the default format of the fnd_date.
1844 --
1845 hr_utility.trace('p_input_value, before D ->' || p_input_value);
1846 hr_utility.trace('l_display_value, before D ->' || l_display_value);
1847 if l_uom_value = 'D' then
1848
1849 begin
1850
1851 IF p_calling_mode = 'Q' THEN
1852 return p_input_value;
1853 END IF;
1854
1855 -- l_display_value := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(p_input_value)); /* COMMENTED FOR BUG 11830805 */
1856
1857 l_display_value := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(p_input_value), calendar_aware=>FND_DATE.calendar_aware_alt); /* FOR BUG 11830805 */
1858
1859
1860 hr_utility.trace('after usind fnd_date package l_display_value: ' || l_display_value);
1861 hr_utility.trace('p_input_value in D ->' || p_input_value);
1862 hr_utility.trace('l_display_value in D ->' || l_display_value);
1863
1864 exception
1865
1866 when others then
1867 hr_utility.trace(' In Exception -> ' || l_display_value);
1868 hr_utility.set_message('PAY','PAY_6306_INPUT_VALUE_FORMAT');
1869 hr_utility.set_message_token('UNIT_OF_MEASURE', hr_general.decode_lookup ('UNITS', l_uom_value ));
1870 hr_utility.raise_error;
1871 end;
1872
1873 else
1874
1875 begin
1876
1877 hr_utility.trace('p_input_value in else ->' || p_input_value);
1878 hr_utility.trace('l_display_value in else ->' || l_display_value);
1879 l_display_value := p_input_value;
1880
1881 exception
1882
1883 when others then
1884 hr_utility.trace(' In Exception ->l_display_value := p_input_value; ' || l_display_value);
1885 hr_utility.set_message('PAY','PAY_6306_INPUT_VALUE_FORMAT');
1886 hr_utility.set_message_token('UNIT_OF_MEASURE', hr_general.decode_lookup ('UNITS', l_uom_value ));
1887 hr_utility.raise_error;
1888
1889 end;
1890
1891 end if;
1892
1893 --
1894 if (l_lookup_type is not null and
1895 l_internal_value is not null) then
1896 --
1897
1898 -- Bug: 5200900
1899
1900 IF p_calling_mode = 'Q' THEN
1901 OPEN csr_valid_lookup_code(l_lookup_type, l_internal_value);
1902 FETCH csr_valid_lookup_code into l_display_value;
1903 IF csr_valid_lookup_code%FOUND THEN
1904 return l_display_value;
1905 END IF;
1906 CLOSE csr_valid_lookup_code;
1907 END IF;
1908
1909 OPEN csr_valid_lookup(l_lookup_type, l_internal_value);
1910 FETCH csr_valid_lookup INTO l_display_value ;
1911
1912 -- Bug: 5200900
1913
1914 IF csr_valid_lookup%NOTFOUND THEN
1915
1916 hr_utility.trace('ERROR: Invalid lookup Value');
1917 hr_utility.raise_error();
1918
1919 ELSE
1920
1921 hr_utility.trace('Info: Valid lookup');
1922
1923 CASE p_input_value_number
1924
1925 WHEN 1 THEN g_ee_value1 := l_display_value;
1926 hr_utility.trace('Updated g_ee_value1 :' || g_ee_value1);
1927 WHEN 2 THEN g_ee_value2 := l_display_value;
1928 hr_utility.trace('Updated g_ee_value2 :' || g_ee_value2);
1929 WHEN 3 THEN g_ee_value3 := l_display_value;
1930 hr_utility.trace('Updated g_ee_value3 :' || g_ee_value3);
1931 WHEN 4 THEN g_ee_value4 := l_display_value;
1932 hr_utility.trace('Updated g_ee_value4 :' || g_ee_value4);
1933 WHEN 5 THEN g_ee_value5 := l_display_value;
1934 hr_utility.trace('Updated g_ee_value5 :' || g_ee_value5);
1935 WHEN 6 THEN g_ee_value6 := l_display_value;
1936 hr_utility.trace('Updated g_ee_value6 :' || g_ee_value6);
1937 WHEN 7 THEN g_ee_value7 := l_display_value;
1938 hr_utility.trace('Updated g_ee_value7 :' || g_ee_value7);
1939 WHEN 8 THEN g_ee_value8 := l_display_value;
1940 hr_utility.trace('Updated g_ee_value8 :' || g_ee_value8);
1941 WHEN 9 THEN g_ee_value9 := l_display_value;
1942 hr_utility.trace('Updated g_ee_value9 :' || g_ee_value9);
1943 WHEN 10 THEN g_ee_value10 := l_display_value;
1944 hr_utility.trace('Updated g_ee_value10 :' || g_ee_value10);
1945 WHEN 11 THEN g_ee_value11 := l_display_value;
1946 hr_utility.trace('Updated g_ee_value11 :' || g_ee_value11);
1947 WHEN 12 THEN g_ee_value12 := l_display_value;
1948 hr_utility.trace('Updated g_ee_value12 :' || g_ee_value12);
1949 WHEN 13 THEN g_ee_value13 := l_display_value;
1950 hr_utility.trace('Updated g_ee_value13 :' || g_ee_value13);
1951 WHEN 14 THEN g_ee_value14 := l_display_value;
1952 hr_utility.trace('Updated g_ee_value14 :' || g_ee_value14);
1953 WHEN 15 THEN g_ee_value15 := l_display_value;
1954 hr_utility.trace('Updated g_ee_value15 :' || g_ee_value15);
1955 END CASE;
1956
1957 END IF;
1958
1959 CLOSE csr_valid_lookup;
1960
1961 --
1962 elsif (l_value_set_id is not null and
1963 l_internal_value is not null) then
1964 --
1965 begin
1966 l_display_value := pay_input_values_pkg.decode_vset_value(
1967 l_value_set_id, l_internal_value);
1968
1969 exception
1970
1971 when others then
1972 hr_utility.trace(' In Exception l_value_set_id is not null and-> ' || l_display_value);
1973 hr_utility.set_message('PAY','PAY_6306_INPUT_VALUE_FORMAT');
1974 hr_utility.set_message_token('UNIT_OF_MEASURE', hr_general.decode_lookup ('UNITS', l_uom_value ));
1975 hr_utility.raise_error;
1976
1977 end;
1978
1979 --
1980 else
1981 --
1982 begin
1983
1984 -- Bug: 5204994
1985
1986 -- hr_chkfmt.changeformat(
1987 -- l_internal_value, /* the value to be formatted (out - display) */
1988 -- l_display_value, /* the formatted value on output (out - canonical) */
1989 -- l_uom_value, /* the format to check */
1990 -- l_currency_code );
1991
1992 -- Replaced the above commented code with the following code for the bug# 5204994.
1993
1994 hr_chkfmt.checkformat(l_internal_value,
1995 l_uom_value,
1996 l_display_value,
1997 null,
1998 null,
1999 'N',
2000 l_rgeflg,
2001 l_currency_code);
2002
2003 exception
2004
2005 when others then
2006 hr_utility.trace(' In Exception hr_chkfmt.changeformat(-> ' || l_display_value);
2007 hr_utility.set_message('PAY','PAY_6306_INPUT_VALUE_FORMAT');
2008 hr_utility.set_message_token('UNIT_OF_MEASURE', hr_general.decode_lookup ('UNITS', l_uom_value ));
2009 hr_utility.raise_error;
2010
2011 end;
2012
2013
2014
2015 --
2016 end if;
2017 --
2018
2019 return l_display_value;
2020 --
2021 exception
2022 when others then
2023 hr_utility.set_message('PAY','PAY_6306_INPUT_VALUE_FORMAT');
2024 hr_utility.set_message_token('UNIT_OF_MEASURE', hr_general.decode_lookup ('UNITS', l_uom_value ));
2025
2026 hr_utility.raise_error;
2027 --
2028 end convert_internal_to_display;
2029
2030 end pay_batch_balanceadj_wrapper;