[Home] [Help]
PACKAGE BODY: APPS.PAY_BEE_UPGRADE_PKG
Source
1 Package body PAY_BEE_UPGRADE_PKG as
2 /* $Header: pybeeupg.pkb 120.1 2006/02/21 06:42 bshukla noship $ */
3 --
4 --
5 --
6 -- Declare a cursor containing all records with date format.
7 CURSOR csr_batch_lines (p_assignment_id number, p_bg_id number,
8 p_leg_code varchar2) IS
9 SELECT pbl.batch_line_id,
10 pbl.value_1,
11 pbl.value_2,
12 pbl.value_3,
13 pbl.value_4,
14 pbl.value_5,
15 pbl.value_6,
16 pbl.value_7,
17 pbl.value_8,
18 pbl.value_9,
19 pbl.value_10,
20 pbl.value_11,
21 pbl.value_12,
22 pbl.value_13,
23 pbl.value_14,
24 pbl.value_15,
25 pbh.business_group_id,
26 p_leg_code legislation_code,
27 pbl.effective_date,
28 pbl.element_type_id,
29 pbl.element_name,
30 pbl.assignment_id,
31 pbl.assignment_number,
32 pbl.batch_line_status
33 FROM pay_batch_lines pbl,
34 pay_batch_headers pbh
35 WHERE pbl.assignment_id is not null
36 AND pbl.assignment_id = p_assignment_id
37 AND pbh.business_group_id = p_bg_id
38 AND pbh.batch_id = pbl.batch_id
39 AND p_assignment_id is not null
40 UNION ALL
41 SELECT pbl.batch_line_id,
42 pbl.value_1,
43 pbl.value_2,
44 pbl.value_3,
45 pbl.value_4,
46 pbl.value_5,
47 pbl.value_6,
48 pbl.value_7,
49 pbl.value_8,
50 pbl.value_9,
51 pbl.value_10,
52 pbl.value_11,
53 pbl.value_12,
54 pbl.value_13,
55 pbl.value_14,
56 pbl.value_15,
57 pbh.business_group_id,
58 p_leg_code legislation_code,
59 pbl.effective_date,
60 pbl.element_type_id,
61 pbl.element_name,
62 asg.assignment_id,
63 pbl.assignment_number,
64 pbl.batch_line_status
65 FROM pay_batch_lines pbl,
66 pay_batch_headers pbh,
67 per_all_assignments_f asg
68 WHERE pbh.business_group_id = p_bg_id
69 AND pbl.assignment_id is null
70 AND asg.assignment_id = p_assignment_id
71 AND asg.business_group_id = p_bg_id
72 AND pbl.effective_date between asg.effective_start_date and asg.effective_end_date
73 AND upper(pbl.assignment_number) = upper(asg.assignment_number)
74 AND pbh.batch_id = pbl.batch_id
75 AND p_assignment_id is not null
76 UNION ALL
77 SELECT pbl.batch_line_id,
78 pbl.value_1,
79 pbl.value_2,
80 pbl.value_3,
81 pbl.value_4,
82 pbl.value_5,
83 pbl.value_6,
84 pbl.value_7,
85 pbl.value_8,
86 pbl.value_9,
87 pbl.value_10,
88 pbl.value_11,
89 pbl.value_12,
90 pbl.value_13,
91 pbl.value_14,
92 pbl.value_15,
93 pbh.business_group_id,
94 p_leg_code legislation_code,
95 pbl.effective_date,
96 pbl.element_type_id,
97 pbl.element_name,
98 pbl.assignment_id,
99 pbl.assignment_number,
100 pbl.batch_line_status
101 FROM pay_batch_lines pbl,
102 pay_batch_headers pbh
103 WHERE pbh.business_group_id = p_bg_id
104 AND pbl.assignment_id is null
105 AND (pbl.assignment_number is null or
106 not exists
107 (select null
108 from per_all_assignments_f asg
109 where asg.assignment_id = p_assignment_id
110 and asg.business_group_id = p_bg_id
111 and upper(pbl.assignment_number) = upper(asg.assignment_number)
112 and pbl.effective_date between asg.effective_start_date and asg.effective_end_date))
113 AND pbh.batch_id = pbl.batch_id
114 AND p_assignment_id is null
115 ORDER BY element_type_id,element_name;
116 --
117 --
118 g_rec csr_batch_lines%ROWTYPE;
119 --
120 TYPE input_currency_code_tbl IS TABLE OF pay_element_types_f.input_currency_code%TYPE INDEX BY binary_integer;
121 TYPE name_tbl IS TABLE OF pay_input_values_f.name%TYPE INDEX BY binary_integer;
122 TYPE display_sequence_tbl IS TABLE OF pay_input_values_f.display_sequence%TYPE INDEX BY binary_integer;
123 TYPE uom_tbl IS TABLE OF pay_input_values_f.uom%TYPE INDEX BY binary_integer;
124 TYPE lookup_type_tbl IS TABLE OF pay_input_values_f.lookup_type%TYPE INDEX BY binary_integer;
125 TYPE value_set_id_tbl IS TABLE OF pay_input_values_f.value_set_id%TYPE INDEX BY binary_integer;
126 --
127 type input_value_tbl_rec is record(
128 input_currency_code input_currency_code_tbl,
129 name name_tbl,
130 display_sequence display_sequence_tbl,
131 uom uom_tbl,
132 lookup_type lookup_type_tbl,
133 value_set_id value_set_id_tbl);
134 --
135 g_input_values input_value_tbl_rec;
136 g_element_type_id pay_element_types_f.element_type_id%TYPE := NULL;
137 g_element_name pay_element_types_f.element_name%TYPE := NULL;
138 g_effective_date pay_batch_lines.effective_date%TYPE := NULL;
139 g_business_group_id per_business_groups.business_group_id%TYPE := NULL;
140 g_legislation_code per_business_groups.legislation_code%TYPE := NULL;
141 --
142 type input_value_rec is record(
143 element_type_id pay_element_types_f.element_type_id%TYPE,
144 element_name pay_element_types_f.element_name%TYPE,
145 input_currency_code pay_element_types_f.input_currency_code%TYPE,
146 name pay_input_values_f.name%TYPE,
147 display_sequence pay_input_values_f.display_sequence%TYPE,
148 uom pay_input_values_f.uom%TYPE,
149 lookup_type pay_input_values_f.lookup_type%TYPE,
150 value_set_id pay_input_values_f.value_set_id%TYPE);
151 --
152 --
153 -- -------------------------------------------------------------------------
154 -- Procedure to get the input values for a given element type id.
155 -- -------------------------------------------------------------------------
156 PROCEDURE get_input_value(
157 p_element_type_id in number,
158 p_element_name in varchar2,
159 p_business_group_id in number,
160 p_legislation_code in varchar2,
161 p_effective_date in date) is
162 --
163 l_found boolean := false;
164 --
165 CURSOR csr_input_values(p_element_type_id number,
166 p_element_name varchar2,
167 p_effective_date date,
168 p_business_group_id number,
169 p_legislation_code varchar2) IS
170 SELECT piv.name,
171 piv.display_sequence, piv.uom,piv.lookup_type,
172 piv.value_set_id,pet.input_currency_code
173 FROM pay_element_types_f pet,
174 pay_input_values_f piv
175 WHERE p_element_type_id is not null
176 AND p_element_type_id = pet.element_type_id
177 AND p_effective_date BETWEEN pet.effective_start_date
178 AND pet.effective_end_date
179 AND pet.element_type_id = piv.element_type_id
180 AND p_effective_date BETWEEN piv.effective_start_date
181 AND piv.effective_end_date
182 UNION ALL
183 SELECT piv.name,
184 piv.display_sequence, piv.uom,piv.lookup_type,
185 piv.value_set_id,pet.input_currency_code
186 FROM pay_element_types_f pet,
187 pay_input_values_f piv
188 WHERE p_element_type_id is null
189 AND upper(p_element_name) = upper(pet.element_name)
190 AND (pet.business_group_id = p_business_group_id OR
191 (pet.business_group_id is null AND pet.legislation_code = p_legislation_code) OR
192 (pet.business_group_id is null AND pet.legislation_code is null))
193 AND p_effective_date BETWEEN pet.effective_start_date
194 AND pet.effective_end_date
195 AND pet.element_type_id = piv.element_type_id
196 AND p_effective_date BETWEEN piv.effective_start_date
197 AND piv.effective_end_date
198 ORDER BY display_sequence, name;
199 --
200 begin
201 --
202 --
203 if (g_element_type_id = p_element_type_id and
204 g_element_name = p_element_name and
205 g_effective_date = p_effective_date and
206 g_business_group_id = p_business_group_id and
207 g_legislation_code = p_legislation_code) then
208 --
209 l_found := true;
210 --
211 end if;
212 --
213 if not l_found then
214 --
215 g_input_values.name.delete;
216 g_input_values.display_sequence.delete;
217 g_input_values.uom.delete;
218 g_input_values.lookup_type.delete;
219 g_input_values.value_set_id.delete;
220 g_input_values.input_currency_code.delete;
221 --
222 open csr_input_values(p_element_type_id,
223 p_element_name,
224 p_effective_date,
225 p_business_group_id,
226 p_legislation_code);
227 --
228 fetch csr_input_values bulk collect into
229 g_input_values.name,
230 g_input_values.display_sequence,
231 g_input_values.uom,
232 g_input_values.lookup_type,
233 g_input_values.value_set_id,
234 g_input_values.input_currency_code;
235 --
236 close csr_input_values;
237 --
238 g_element_type_id := p_element_type_id;
239 g_element_name := p_element_name;
240 g_effective_date := p_effective_date;
241 g_business_group_id := p_business_group_id;
242 g_legislation_code := p_legislation_code;
243 --
244 end if;
245 --
246 end get_input_value;
247 ----------------------------------------------------------------------------
248 --
249 -- -------------------------------------------------------------------------
250 -- Function to convert input values from display format to internal format.
251 -- -------------------------------------------------------------------------
252 function convert_display_to_internal
253 (p_input_value varchar2,
254 p_uom_value varchar2,
255 p_lookup_type varchar2,
256 p_value_set_id number,
257 p_currency_code varchar2,
258 p_batch_line_id varchar2,
259 p_batch_line_status varchar2,
260 p_iv_number number)
261 return varchar2 is
262 --
263 l_display_value varchar2(80) := p_input_value;
264 l_formatted_value varchar2(80) := p_input_value;
265 l_dummy varchar2(100);
266 --
267 lov_error exception;
268 --
269 -- cursor csr_valid_lookup
270 -- (p_lookup_type varchar2,
271 -- p_meaning varchar2) is
272 -- select HL.lookup_code
273 -- from hr_lookups HL
274 -- where HL.lookup_type = p_lookup_type
275 -- and HL.meaning = p_meaning;
276 --
277 cursor csr_valid_lookup
278 (p_lookup_type varchar2,
279 p_meaning varchar2) is
280 SELECT FLV.LOOKUP_CODE
281 FROM FND_LOOKUP_VALUES FLV
282 WHERE FLV.VIEW_APPLICATION_ID = 3
283 AND FLV.SECURITY_GROUP_ID = decode(substr(userenv('CLIENT_INFO'),55,1),
284 ' ', 0, NULL, 0, '0', 0,
285 fnd_global.lookup_security_group(FLV.LOOKUP_TYPE,FLV.VIEW_APPLICATION_ID))
286 AND decode(FLV.TAG,
287 NULL, 'Y',
288 decode(substr(FLV.TAG,1,1),
289 '+', decode(sign(instr(FLV.TAG, HR_API.GET_LEGISLATION_CONTEXT)),
290 1, 'Y', 'N'),
291 '-', decode(sign(instr(FLV.TAG, HR_API.GET_LEGISLATION_CONTEXT)),
292 1, 'N', 'Y'),
293 'Y' )
294 ) = 'Y'
295 AND flv.lookup_type = p_lookup_type
296 AND flv.meaning = p_meaning;
297 --
298 begin
299 --
300 hr_utility.set_location('pay_bee_upgrade_pkg.convert_display_to_internal',1);
301 --
302 if (p_lookup_type is not null and
303 l_display_value is not null) then
304 --
305 open csr_valid_lookup(p_lookup_type, l_display_value);
306 fetch csr_valid_lookup into l_formatted_value ;
307 --
308 if csr_valid_lookup%NOTFOUND then
309 close csr_valid_lookup;
310 if p_batch_line_status in ('V','T') then
311 pay_core_utils.push_message (800,'HR_449107_BEE_UPG_LK_NONE','A');
312 pay_core_utils.push_token ('BATCH_LINE_ID', p_batch_line_id);
313 pay_core_utils.push_token ('VALUE_ID', 'VALUE_'||p_iv_number);
314 pay_core_utils.push_token ('LOOKUP_TYPE', p_lookup_type);
315 pay_core_utils.push_token ('UOM', p_uom_value);
316 pay_core_utils.push_token ('VALUE', l_display_value);
317 --hr_utility.raise_error;
318 raise lov_error;
319 else
320 l_formatted_value := p_input_value;
321 end if;
322 end if;
323 --
324 fetch csr_valid_lookup into l_dummy;
325 --
326 if csr_valid_lookup%FOUND then
330 pay_core_utils.push_token ('BATCH_LINE_ID', p_batch_line_id);
327 close csr_valid_lookup;
328 if p_batch_line_status in ('V','T') then
329 pay_core_utils.push_message (800,'HR_449108_BEE_UPG_LK_MANY','A');
331 pay_core_utils.push_token ('VALUE_ID', 'VALUE_'||p_iv_number);
332 pay_core_utils.push_token ('LOOKUP_TYPE', p_lookup_type);
333 pay_core_utils.push_token ('UOM', p_uom_value);
334 pay_core_utils.push_token ('VALUE', l_display_value);
335 --hr_utility.raise_error;
336 raise lov_error;
337 else
338 l_formatted_value := p_input_value;
339 end if;
340 end if;
341 --
342 close csr_valid_lookup;
343 --
344 elsif (p_value_set_id is not null and
345 l_display_value is not null) then
346 --
347 l_formatted_value := pay_input_values_pkg.decode_vset_meaning(
348 p_value_set_id, l_display_value);
349 --
350 if l_formatted_value is null then
351 if p_batch_line_status in ('V','T') then
352 pay_core_utils.push_message (800,'HR_449109_BEE_UPG_VS_NONE','A');
353 pay_core_utils.push_token ('BATCH_LINE_ID', p_batch_line_id);
354 pay_core_utils.push_token ('VALUE_ID', 'VALUE_'||p_iv_number);
355 pay_core_utils.push_token ('VALUE_SET_ID', p_value_set_id);
356 pay_core_utils.push_token ('UOM', p_uom_value);
357 pay_core_utils.push_token ('VALUE', l_display_value);
358 --hr_utility.raise_error;
359 raise lov_error;
360 else
361 l_formatted_value := p_input_value;
362 end if;
363 end if;
364 --
365 else
366 --
367 if (p_uom_value <> 'D'
368 or (p_uom_value = 'D' and INSTR(l_display_value,'-',1,2) <> 0)) then
369 --
370 hr_chkfmt.checkformat (
371 l_display_value, /* the value to be formatted (out - display) */
372 p_uom_value, /* the format to check */
373 l_formatted_value, /* the formatted value on output (out - canonical) */
374 null, /* minimum value (can be null) */
375 null, /* maximum value (can be null) */
376 'Y', /* is ok to be null ? */
377 l_dummy, /* used for range checking */
378 p_currency_code );
379 --
380 end if;
381 --
382 end if;
383 --
384 --
385 hr_utility.trace('IN:'||p_input_value);
386 hr_utility.trace('OUT:'||l_formatted_value);
387 hr_utility.set_location('pay_bee_upgrade_pkg.convert_display_to_internal',2);
388 --
389 if ((p_input_value is not null and l_formatted_value is null) or
390 (p_input_value is null)) then
391 return p_input_value;
392 else
393 return l_formatted_value;
394 end if;
395 --
396 exception
397 when lov_error then
398 --
399 hr_utility.set_location('pay_bee_upgrade_pkg.convert_display_to_internal',3);
400 --
401 hr_utility.raise_error;
402 when others then
403 --
404 hr_utility.set_location('pay_bee_upgrade_pkg.convert_display_to_internal',4);
405 --
406 if p_batch_line_status in ('V','T') then
407 pay_core_utils.push_message (800,'HR_449110_BEE_UPG_CONV','A');
408 pay_core_utils.push_token ('BATCH_LINE_ID', p_batch_line_id);
409 pay_core_utils.push_token ('VALUE_ID', 'VALUE_'||p_iv_number);
410 pay_core_utils.push_token ('UOM', p_uom_value);
411 pay_core_utils.push_token ('VALUE', l_display_value);
412 hr_utility.raise_error;
413 else
414 return p_input_value;
415 end if;
416 --
417 end convert_display_to_internal;
418 -- -------------------------------------------------------------------------
419 --
420 --
421 PROCEDURE convert_pay_input (p_rec csr_batch_lines%ROWTYPE) IS
422 --
423 l_temp csr_batch_lines%ROWTYPE;
424 l_input_value_number integer;
425 l_input_value_count integer;
426 --
427 l_fetched_input_value input_value_rec;
428 BEGIN
429 --
430 -- Initialize to default values.
431 --
432 l_temp.value_1 := p_rec.value_1;
433 l_temp.value_2 := p_rec.value_2;
434 l_temp.value_3 := p_rec.value_3;
435 l_temp.value_4 := p_rec.value_4;
436 l_temp.value_5 := p_rec.value_5;
437 l_temp.value_6 := p_rec.value_6;
438 l_temp.value_7 := p_rec.value_7;
439 l_temp.value_8 := p_rec.value_8;
440 l_temp.value_9 := p_rec.value_9;
441 l_temp.value_10 := p_rec.value_10;
442 l_temp.value_11 := p_rec.value_11;
443 l_temp.value_12 := p_rec.value_12;
444 l_temp.value_13 := p_rec.value_13;
445 l_temp.value_14 := p_rec.value_14;
446 l_temp.value_15 := p_rec.value_15;
447 --
448 get_input_value(p_element_type_id => p_rec.element_type_id,
449 p_element_name => p_rec.element_name,
450 p_effective_date => p_rec.effective_date,
454 l_input_value_count := g_input_values.name.count;
451 p_business_group_id => p_rec.business_group_id,
452 p_legislation_code => p_rec.legislation_code);
453 --
455 --
456 for i in 1..l_input_value_count loop
457
458 l_fetched_input_value.uom := g_input_values.uom(i);
459 l_fetched_input_value.lookup_type := g_input_values.lookup_type(i);
460 l_fetched_input_value.value_set_id := g_input_values.value_set_id(i);
461 l_fetched_input_value.input_currency_code := g_input_values.input_currency_code(i);
462
463 l_input_value_number := i;
464
465
466 if l_input_value_number = 1 then
467 --
468 l_temp.value_1 := convert_display_to_internal(p_rec.value_1,
469 l_fetched_input_value.uom,
470 l_fetched_input_value.lookup_type,
471 l_fetched_input_value.value_set_id,
472 l_fetched_input_value.input_currency_code,
473 p_rec.batch_line_status,
474 p_rec.batch_line_id,
475 l_input_value_number);
476 --
477 elsif l_input_value_number = 2 then
478 --
479 l_temp.value_2 := convert_display_to_internal(p_rec.value_2,
480 l_fetched_input_value.uom,
481 l_fetched_input_value.lookup_type,
482 l_fetched_input_value.value_set_id,
483 l_fetched_input_value.input_currency_code,
484 p_rec.batch_line_status,
485 p_rec.batch_line_id,
486 l_input_value_number);
487 --
488 elsif l_input_value_number = 3 then
489 --
490 l_temp.value_3 := convert_display_to_internal(p_rec.value_3,
491 l_fetched_input_value.uom,
492 l_fetched_input_value.lookup_type,
493 l_fetched_input_value.value_set_id,
494 l_fetched_input_value.input_currency_code,
495 p_rec.batch_line_status,
496 p_rec.batch_line_id,
497 l_input_value_number);
498 --
499 elsif l_input_value_number = 4 then
500 --
501 l_temp.value_4 := convert_display_to_internal(p_rec.value_4,
502 l_fetched_input_value.uom,
503 l_fetched_input_value.lookup_type,
504 l_fetched_input_value.value_set_id,
505 l_fetched_input_value.input_currency_code,
506 p_rec.batch_line_status,
507 p_rec.batch_line_id,
508 l_input_value_number);
509 --
510 elsif l_input_value_number = 5 then
511 --
512 l_temp.value_5 := convert_display_to_internal(p_rec.value_5,
513 l_fetched_input_value.uom,
514 l_fetched_input_value.lookup_type,
515 l_fetched_input_value.value_set_id,
516 l_fetched_input_value.input_currency_code,
517 p_rec.batch_line_status,
518 p_rec.batch_line_id,
519 l_input_value_number);
520 --
521 elsif l_input_value_number = 6 then
522 --
523 l_temp.value_6 := convert_display_to_internal(p_rec.value_6,
524 l_fetched_input_value.uom,
525 l_fetched_input_value.lookup_type,
526 l_fetched_input_value.value_set_id,
527 l_fetched_input_value.input_currency_code,
528 p_rec.batch_line_status,
529 p_rec.batch_line_id,
530 l_input_value_number);
531 --
532 elsif l_input_value_number = 7 then
533 --
537 l_fetched_input_value.value_set_id,
534 l_temp.value_7 := convert_display_to_internal(p_rec.value_7,
535 l_fetched_input_value.uom,
536 l_fetched_input_value.lookup_type,
538 l_fetched_input_value.input_currency_code,
539 p_rec.batch_line_status,
540 p_rec.batch_line_id,
541 l_input_value_number);
542 --
543 elsif l_input_value_number = 8 then
544 --
545 l_temp.value_8 := convert_display_to_internal(p_rec.value_8,
546 l_fetched_input_value.uom,
547 l_fetched_input_value.lookup_type,
548 l_fetched_input_value.value_set_id,
549 l_fetched_input_value.input_currency_code,
550 p_rec.batch_line_status,
551 p_rec.batch_line_id,
552 l_input_value_number);
553 --
554 elsif l_input_value_number = 9 then
555 --
556 l_temp.value_9 := convert_display_to_internal(p_rec.value_9,
557 l_fetched_input_value.uom,
558 l_fetched_input_value.lookup_type,
559 l_fetched_input_value.value_set_id,
560 l_fetched_input_value.input_currency_code,
561 p_rec.batch_line_status,
562 p_rec.batch_line_id,
563 l_input_value_number);
564 --
565 elsif l_input_value_number = 10 then
566 --
567 l_temp.value_10 := convert_display_to_internal(p_rec.value_10,
568 l_fetched_input_value.uom,
569 l_fetched_input_value.lookup_type,
570 l_fetched_input_value.value_set_id,
571 l_fetched_input_value.input_currency_code,
572 p_rec.batch_line_status,
573 p_rec.batch_line_id,
574 l_input_value_number);
575 --
576 elsif l_input_value_number = 11 then
577 --
578 l_temp.value_11 := convert_display_to_internal(p_rec.value_11,
579 l_fetched_input_value.uom,
580 l_fetched_input_value.lookup_type,
581 l_fetched_input_value.value_set_id,
582 l_fetched_input_value.input_currency_code,
583 p_rec.batch_line_status,
584 p_rec.batch_line_id,
585 l_input_value_number);
586 --
587 elsif l_input_value_number = 12 then
588 --
589 l_temp.value_12 := convert_display_to_internal(p_rec.value_12,
590 l_fetched_input_value.uom,
591 l_fetched_input_value.lookup_type,
592 l_fetched_input_value.value_set_id,
593 l_fetched_input_value.input_currency_code,
594 p_rec.batch_line_status,
595 p_rec.batch_line_id,
596 l_input_value_number);
597 --
598 elsif l_input_value_number = 13 then
599 --
600 l_temp.value_13 := convert_display_to_internal(p_rec.value_13,
601 l_fetched_input_value.uom,
602 l_fetched_input_value.lookup_type,
603 l_fetched_input_value.value_set_id,
604 l_fetched_input_value.input_currency_code,
605 p_rec.batch_line_status,
606 p_rec.batch_line_id,
607 l_input_value_number);
608 --
609 elsif l_input_value_number = 14 then
610 --
611 l_temp.value_14 := convert_display_to_internal(p_rec.value_14,
615 l_fetched_input_value.input_currency_code,
612 l_fetched_input_value.uom,
613 l_fetched_input_value.lookup_type,
614 l_fetched_input_value.value_set_id,
616 p_rec.batch_line_status,
617 p_rec.batch_line_id,
618 l_input_value_number);
619 --
620 elsif l_input_value_number = 15 then
621 --
622 l_temp.value_15 := convert_display_to_internal(p_rec.value_15,
623 l_fetched_input_value.uom,
624 l_fetched_input_value.lookup_type,
625 l_fetched_input_value.value_set_id,
626 l_fetched_input_value.input_currency_code,
627 p_rec.batch_line_status,
628 p_rec.batch_line_id,
629 l_input_value_number);
630 --
631 exit; -- stop looping after the fifteenth input value
632 end if;
633
634 end loop;
635
636 if (l_temp.value_1 <> p_rec.value_1 or
637 l_temp.value_2 <> p_rec.value_2 or
638 l_temp.value_3 <> p_rec.value_3 or
639 l_temp.value_4 <> p_rec.value_4 or
640 l_temp.value_5 <> p_rec.value_5 or
641 l_temp.value_6 <> p_rec.value_6 or
642 l_temp.value_7 <> p_rec.value_7 or
643 l_temp.value_8 <> p_rec.value_8 or
644 l_temp.value_9 <> p_rec.value_9 or
645 l_temp.value_10 <> p_rec.value_10 or
646 l_temp.value_11 <> p_rec.value_11 or
647 l_temp.value_12 <> p_rec.value_12 or
648 l_temp.value_13 <> p_rec.value_13 or
649 l_temp.value_14 <> p_rec.value_14 or
650 l_temp.value_15 <> p_rec.value_15) then
651 --
652 begin
653 --
654 payplnk.g_payplnk_call := true;
655 --
656 update pay_batch_lines
657 set value_1 = l_temp.value_1,
658 value_2 = l_temp.value_2,
659 value_3 = l_temp.value_3,
660 value_4 = l_temp.value_4,
661 value_5 = l_temp.value_5,
662 value_6 = l_temp.value_6,
663 value_7 = l_temp.value_7,
664 value_8 = l_temp.value_8,
665 value_9 = l_temp.value_9,
666 value_10 = l_temp.value_10,
667 value_11 = l_temp.value_11,
668 value_12 = l_temp.value_12,
669 value_13 = l_temp.value_13,
670 value_14 = l_temp.value_14,
671 value_15 = l_temp.value_15
672 where batch_line_id = p_rec.batch_line_id;
673 --
674 payplnk.g_payplnk_call := false;
675 --
676 exception
677 when others then
678 --
679 payplnk.g_payplnk_call := false;
680 --
681 pay_core_utils.push_message (800,'HR_449111_BEE_UPG_UPD','A');
682 pay_core_utils.push_token ('BATCH_LINE_ID', p_rec.batch_line_id);
683 hr_utility.raise_error;
684 --
685 end;
686 --
687 end if;
688
689 END convert_pay_input;
690 --
691 -- ----------------------------------------------------------------------------
692 -- |--------------------------< upgrade_iv_values >----------------------------|
693 -- ----------------------------------------------------------------------------
694 -- {Start Of Comments}
695 --
696 -- Description:
697 -- This procedure upgrades all input values for all batch lines
698 -- belong to a given assignment.
699 --
700 -- In Parameters:
701 -- NAME REQD TYPE DESCRIPTION
702 -- ---- ---- ---- -----------
703 -- P_ASSIGNMENT_ID Yes number Assignment Id.
704 -- P_BUSINESS_GROUP_ID Yes number Business group of record.
705 --
706 -- {End Of Comments}
707 --
708 procedure upgrade_iv_values
709 (P_ASG_ID in number
710 ) is
711 --
712 --
713 cursor csr_min_asg_act is
714 select cact.chunk_number,
715 pbg.business_group_id,
716 pbg.legislation_code,
717 cact.payroll_action_id
718 from pay_temp_object_actions cact,
719 pay_payroll_actions pct,
720 per_business_groups pbg
721 where cact.object_id = P_ASG_ID
722 and pct.payroll_action_id = cact.payroll_action_id
723 and pbg.business_group_id = pct.business_group_id;
724 --
725 l_chunk_number pay_temp_object_actions.chunk_number%TYPE;
726 --
727 L_ASG_ID per_all_assignments_f.assignment_id%TYPE := P_ASG_ID;
731 --
728 L_BG_ID per_all_assignments_f.business_group_id%TYPE NULL;
729 L_LEG_CODE per_business_groups.legislation_code%TYPE := NULL;
730 L_PCT_ID pay_payroll_actions.payroll_action_id%TYPE := NULL;
732 cursor csr_max_chunck_num (p_pct_id number) is
733 select max(cact.chunk_number) last_chunk
734 from pay_temp_object_actions cact
735 where cact.payroll_action_id = p_pct_id;
736 --
737 L_MAX_CHUNK pay_temp_object_actions.chunk_number%TYPE;
738 --
739 begin
740 --
741 --
742 hr_utility.set_location('pay_bee_upgrade_pkg.upgrade_iv_values',1);
743 --
744 --
745 open csr_min_asg_act;
746 fetch csr_min_asg_act into l_chunk_number,L_BG_ID,L_LEG_CODE,L_PCT_ID;
747 close csr_min_asg_act;
748 --
749
750 --
751 -- Within this loop each records will be fetched and its associated table contents
752 -- will be updated.
753 --
754
755 --
756 hr_utility.trace('ASG ID:'||L_ASG_ID);
757 hr_utility.trace('LEG CODE:'||L_LEG_CODE);
758 hr_utility.trace('BG ID:'||L_ASG_ID);
759 --
760
761 FOR g_rec IN csr_batch_lines(L_ASG_ID,L_BG_ID,L_LEG_CODE) LOOP
762
763 --
764 hr_utility.trace('BL ID:'||g_rec.batch_line_id);
765 --
766
767 -- Call subprogramms to update associated tables.
768 convert_pay_input(g_rec);
769
770 END LOOP;
771
772 --
773 -- If it is the last chunk then carry out the conversion for any
774 -- remaining batch lines.
775 --
776 open csr_max_chunck_num(L_PCT_ID);
777 fetch csr_max_chunck_num into L_MAX_CHUNK;
778 close csr_max_chunck_num;
779 --
780 if l_chunk_number = L_MAX_CHUNK then
781 --
782 --
783 -- Within this loop each records will be fetched and its associated table contents
784 -- will be updated.
785 --
786 --
787 hr_utility.trace('ASG ID:'||NULL);
788 hr_utility.trace('LEG CODE:'||L_LEG_CODE);
789 hr_utility.trace('BG ID:'||L_ASG_ID);
790 --
791 FOR g_rec IN csr_batch_lines(NULL,L_BG_ID,L_LEG_CODE) LOOP
792 --
793 hr_utility.trace('BL ID:'||g_rec.batch_line_id);
794 --
795 -- Call subprogramms to update associated tables.
796 convert_pay_input(g_rec);
797 --
798 END LOOP;
799 --
800 end if;
801 --
802
803 --
804 hr_utility.set_location('pay_bee_upgrade_pkg.upgrade_iv_values',2);
805 --
806 --
807 end;
808 --
809 --
810 --
811 --
812 --
813 function upgrade_status
814 (p_business_group_id number
815 ,p_short_name varchar2
816 ) return varchar2 is
817 --
818 l_status pay_upgrade_status.status%type;
819 --
820 begin
821 pay_core_utils.get_upgrade_status(p_business_group_id,p_short_name,l_status);
822 --
823 return l_status;
824 --
825 end upgrade_status;
826
827 end PAY_BEE_UPGRADE_PKG;