[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_BAL_UPL_STRUCT
Source
1 PACKAGE BODY pay_in_bal_upl_struct AS
2 /* $Header: pyinbups.pkb 120.2 2006/04/27 04:01:00 rpalli noship $ */
3
4
5 -- Global declarations
6 type char_array is table of varchar2(80) index by binary_integer;
7 type num_array is table of number(16) index by binary_integer;
8 g_package constant VARCHAR2(100) := 'pay_in_bal_upl_struct.' ;
9 g_leg_code CONSTANT VARCHAR2(2):='IN';
10 g_debug BOOLEAN ;
11
12 -- Balance Type Cache
13 g_baltyp_tbl_id num_array;
14 g_baltyp_tbl_jl num_array;
15 g_baltyp_tbl_name char_array;
16 g_baltyp_tbl_uom char_array;
17 g_nxt_free_baltyp number;
18
19 -- Balance Dimension Cache
20 g_baldim_tbl_id num_array;
21 g_baldim_tbl_name char_array;
22 g_nxt_free_baldim number;
23
24 -- Jurisdiction Level Cache
25 g_jur_lev_tbl num_array;
26 g_nxt_free_jl number;
27
28 PROCEDURE local_error(retcode OUT NOCOPY number,
29 p_procedure IN varchar2,
30 p_step IN number) IS
31
32 /* This procedure is called whenever an error needs to be raised and
33 the retcode is set to 2 to indicate an error has occurred.
34 */
35
36 BEGIN
37
38 retcode := 2;
39 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
40 hr_utility.set_message_token('PROCEDURE', g_package||p_procedure);
41 hr_utility.set_message_token('STEP', p_step);
42 hr_utility.raise_error;
43
44 END local_error;
45
46 PROCEDURE put_jl_in_cache (p_jl NUMBER)
47 IS
48 l_jur_level number;
49 l_count number;
50 l_found boolean;
51 l_procedure VARCHAR2(100);
52 l_message VARCHAR2(255);
53 BEGIN
54
55 -- Search for the defined balance in the Cache.
56 g_debug := hr_utility.debug_enabled ;
57 l_procedure := g_package || 'put_jl_in_cache' ;
58 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
59
60 l_jur_level := nvl(p_jl, 999);
61 l_count := 1;
62 l_found := FALSE;
63 WHILE (l_count < g_nxt_free_jl and l_found = FALSE) LOOP
64 if (l_jur_level = g_jur_lev_tbl(l_count)) then
65 pay_in_utils.set_location(g_debug,l_procedure,20);
66 l_found := TRUE;
67 END IF;
68 l_count := l_count + 1;
69 END LOOP;
70
71 IF (l_found = FALSE) THEN
72 g_jur_lev_tbl(g_nxt_free_jl) := l_jur_level;
73 g_nxt_free_jl := g_nxt_free_jl + 1;
74 END IF;
75 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
76 EXCEPTION
77 WHEN OTHERS THEN
78 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
79 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
80 pay_in_utils.trace(l_message,l_procedure);
81 END put_jl_in_cache;
82
83 PROCEDURE check_balance_type(p_baltype_id out NOCOPY NUMBER,
84 p_baltype_name VARCHAR2,
85 p_busgrp_id NUMBER,
86 p_leg_code VARCHAR2,
87 retcode OUT NOCOPY NUMBER)
88 IS
89 l_balance_type_id NUMBER;
90 l_baltyp_name VARCHAR2(80);
91 l_bal_uom VARCHAR2(80);
92 l_jurisdiction_level NUMBER;
93 l_count NUMBER;
94 l_found BOOLEAN;
95 l_procedure VARCHAR2(100);
96 l_message VARCHAR2(255);
97
98 CURSOR csr_bal_type (l_bal_type_name IN VARCHAR2) IS
99 SELECT balance_type_id,
100 nvl(jurisdiction_level, 999),
101 balance_uom
102 FROM pay_balance_types
103 WHERE upper(balance_name) = upper(l_baltyp_name)
104 AND ((business_group_id = p_busgrp_id)
105 OR( business_group_id is null
106 AND legislation_code = p_leg_code)
107 OR( business_group_id IS NULL
108 AND legislation_code IS NULL)
109 )
110 FOR UPDATE OF balance_type_id;
111 BEGIN
112
113 -- Search for the defined balance in the Cache.
114 g_debug := hr_utility.debug_enabled ;
115 l_procedure := g_package || 'check_balance_type' ;
116 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
117
118 if g_debug then
119 pay_in_utils.trace('******************************','********************');
120 pay_in_utils.trace('p_baltype_id : ',p_baltype_id);
121 pay_in_utils.trace('p_baltype_name : ',p_baltype_name);
122 pay_in_utils.trace('p_busgrp_id : ',p_busgrp_id);
123 pay_in_utils.trace('p_leg_code : ',p_leg_code);
124 pay_in_utils.trace('******************************','********************');
125 end if;
126
127 l_balance_type_id := NULL;
128 l_baltyp_name := p_baltype_name;
129 l_count := 1;
130 l_found := FALSE;
131 WHILE (l_count < g_nxt_free_baltyp and l_found = FALSE) LOOP
132 IF (l_baltyp_name = g_baltyp_tbl_name(l_count)) THEN
133 pay_in_utils.set_location(g_debug,l_procedure,20);
134 l_balance_type_id := g_baltyp_tbl_id(l_count);
135 l_found := TRUE;
136 END IF;
137 l_count := l_count + 1;
138 END LOOP;
139
140 -- If the balance is not in the Cache get it from the database.
141
142 pay_in_utils.set_location(g_debug,l_procedure,30);
143 pay_in_utils.trace('l_baltyp_name : ',l_baltyp_name);
144 if (l_found = FALSE) then
145 OPEN csr_bal_type(l_baltyp_name);
146 --
147 FETCH csr_bal_type
148 INTO l_balance_type_id, l_jurisdiction_level, l_bal_uom;
149 --
150 IF csr_bal_type%NOTFOUND THEN
151 --
152 CLOSE csr_bal_type;
153 pay_in_utils.trace('Error: Failure to find balance type',l_procedure);
154 local_error(retcode, 'check_balance_type',1);
155 --
156 END IF;
157 CLOSE csr_bal_type;
158
159
160
161
162 -- Place the defined balance in cache.
163
164 pay_in_utils.set_location(g_debug,l_procedure,40);
165 g_baltyp_tbl_name(g_nxt_free_baltyp) := l_baltyp_name;
166 g_baltyp_tbl_uom(g_nxt_free_baltyp) := l_bal_uom;
167 g_baltyp_tbl_id(g_nxt_free_baltyp) := l_balance_type_id;
168 g_baltyp_tbl_jl(g_nxt_free_baltyp) := l_jurisdiction_level;
169 g_nxt_free_baltyp := g_nxt_free_baltyp + 1;
170 put_jl_in_cache(l_jurisdiction_level);
171
172
173 END IF;
174 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
175
176 p_baltype_id := l_balance_type_id;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
181 IF csr_bal_type%ISOPEN THEN
182 CLOSE csr_bal_type;
183 END IF;
184 p_baltype_id := NULL;
185 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
186 pay_in_utils.trace(l_message,l_procedure);
187 END check_balance_type;
188
189
190 PROCEDURE check_balance_dim(p_baldim_id OUT NOCOPY NUMBER,
191 p_baldim_name VARCHAR2,
192 p_busgrp_id NUMBER,
193 p_leg_code VARCHAR2,
194 retcode OUT NOCOPY NUMBER)
195 IS
196 l_baldim_name varchar2(80);
197 l_count number;
198 l_found boolean;
199 l_balance_dim_id number;
200 l_procedure VARCHAR2(100);
201 l_message VARCHAR2(255);
202
203 CURSOR csr_bal_dim (l_bal_dim_name IN VARCHAR2) IS
204 SELECT balance_dimension_id
205 FROM pay_balance_dimensions
206 WHERE upper(dimension_name) = l_baldim_name
207 AND ((business_group_id = p_busgrp_id)
208 OR( business_group_id IS NULL
209 AND legislation_code = p_leg_code)
210 OR( business_group_id IS NULL
211 AND legislation_code IS NULL)
212 );
213 BEGIN
214
215 -- Search for the defined balance in the Cache.
216 g_debug := hr_utility.debug_enabled ;
217 l_procedure := g_package || 'check_balance_dim' ;
218 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
219 if g_debug then
220 pay_in_utils.trace('******************************','********************');
221 pay_in_utils.trace('p_baldim_name : ',p_baldim_name);
222 pay_in_utils.trace('p_busgrp_id : ',p_busgrp_id);
223 pay_in_utils.trace('p_leg_code : ',p_leg_code);
224 pay_in_utils.trace('******************************','********************');
225 end if;
226 l_balance_dim_id := NULL;
227 l_baldim_name := UPPER(p_baldim_name);
228 l_count := 1;
229 l_found := FALSE;
230 WHILE (l_count < g_nxt_free_baldim AND l_found = FALSE) LOOP
231 IF (l_baldim_name = g_baldim_tbl_name(l_count)) THEN
232 pay_in_utils.set_location(g_debug,l_procedure,20);
233 l_balance_dim_id := g_baldim_tbl_id(l_count);
234 l_found := TRUE;
235 END IF;
236 l_count := l_count + 1;
237 END LOOP;
238
239 -- If the balance is not in the Cache get it from the database.
240
241 pay_in_utils.set_location(g_debug,l_procedure,30);
242 IF (l_found = FALSE) then
243 OPEN csr_bal_dim(l_baldim_name);
244 FETCH csr_bal_dim
245 INTO l_balance_dim_id;
246
247 IF csr_bal_dim%NOTFOUND THEN
248 --
249 CLOSE csr_bal_dim;
250 hr_utility.trace('Error: Failure to find balance dimension');
251 local_error(retcode,'check_balance_dim',2);
252 --
253 END IF;
254 CLOSE csr_bal_dim;
255
256
257
258 -- Place the defined balance in cache.
259
260 pay_in_utils.set_location(g_debug,l_procedure,40);
261 g_baldim_tbl_name(g_nxt_free_baldim) := l_baldim_name;
262 g_baldim_tbl_id(g_nxt_free_baldim) := l_balance_dim_id;
263 g_nxt_free_baldim := g_nxt_free_baldim + 1;
264
265 end if;
266
267 p_baldim_id := l_balance_dim_id;
268
269 if g_debug then
270 pay_in_utils.trace('******************************','********************');
271 pay_in_utils.trace('p_baldim_id : ',p_baldim_id);
272 pay_in_utils.trace('retcode : ',retcode);
273 pay_in_utils.trace('******************************','********************');
274 end if;
275
276 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
281 IF csr_bal_dim%ISOPEN THEN
282 CLOSE csr_bal_dim;
283 END IF;
284 p_baldim_id := NULL;
285 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
286 pay_in_utils.trace(l_message,l_procedure);
287 END check_balance_dim;
288
289 FUNCTION validate_batch_data (p_batch_id number) RETURN number IS
290
291 /* This function verifies that the business group, balance types, and
292 balance dimensions actually exist. If not, it would return a retcode
293 of 2 and raise an exception.
294 */
295
296 retcode number := 0;
297 i number := 0;
298 l_bg_id per_business_groups.business_group_id%TYPE;
299 l_leg_code per_business_groups.legislation_code%TYPE;
300 l_bt_id pay_balance_types.balance_type_id%TYPE;
301 l_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
302 l_procedure VARCHAR2(100);
303 l_message VARCHAR2(255);
304
305 CURSOR csr_bg IS
306 select hou.business_group_id,
307 hou.legislation_code
308 from per_business_groups hou,
309 pay_balance_batch_headers bbh
310 where bbh.batch_id = p_batch_id
311 and upper(hou.name) = upper(bbh.business_group_name);
312
313 cursor c_each_batch (c_batch_id number) is
314 select balance_name,
315 dimension_name
316 from pay_balance_batch_lines
317 where batch_id = c_batch_id;
318
319 BEGIN
320 g_debug := hr_utility.debug_enabled ;
321 l_procedure := g_package || 'validate_batch_data' ;
322 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
323 OPEN csr_bg;
324 FETCH csr_bg
325 INTO l_bg_id, l_leg_code;
326
327 IF csr_bg%NOTFOUND THEN
328 CLOSE csr_bg;
329 local_error(retcode, 'validate_batch_data', 3);
330 END IF;
331 CLOSE csr_bg;
332
333 pay_in_utils.set_location(g_debug,l_procedure,20);
334 for l_each_batch_rec in c_each_batch (p_batch_id) loop
335
336 check_balance_type(l_bt_id, l_each_batch_rec.balance_name,
337 l_bg_id,
341 check_balance_dim(l_bal_dim_id, l_each_batch_rec.dimension_name,
338 l_leg_code,
339 retcode);
340
342 l_bg_id,
343 l_leg_code,
344 retcode);
345 end loop;
346
347 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
348
349 if g_debug then
350 pay_in_utils.trace('******************************','********************');
351 pay_in_utils.trace('retcode : ',retcode);
352 pay_in_utils.trace('******************************','********************');
353 end if;
354
355 return retcode;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
360 IF csr_bg%ISOPEN THEN
361 CLOSE csr_bg;
362 END IF;
363 IF c_each_batch%ISOPEN THEN
364 CLOSE c_each_batch;
365 END IF;
366 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
367 pay_in_utils.trace(l_message,l_procedure);
368 local_error(retcode,'validate_batch_data',4);
369 END validate_batch_data;
370
371
372 PROCEDURE create_bal_upl_struct (errbuf OUT NOCOPY varchar2,
373 retcode OUT NOCOPY number,
374 p_input_value_limit IN number,
375 p_batch_id IN number) IS
376
377 -- errbuf and retcode are special parameters needed for the SRS.
378 -- retcode = 0 means no error and retcode = 2 means an error occurred.
379
380 l_n_elems number := 0;
381 j number;
382 l_bal_uom pay_balance_types.balance_uom%TYPE;
383 l_element_name pay_element_types.element_name%TYPE;
384 l_element_type_id pay_element_types.element_type_id%TYPE;
385 l_elem_link_id pay_element_links.element_link_id%TYPE;
386 l_input_val_id pay_input_values.input_value_id%TYPE;
387 l_bal_name pay_balance_types.balance_name%TYPE;
388 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
389 l_bal_feed_id pay_balance_feeds.balance_feed_id%TYPE;
390 l_bg_name hr_organization_units.name%TYPE;
391 l_bg_id hr_organization_units.organization_id%TYPE;
392 l_jur_level number;
393 l_jur_count number;
394 l_bal_count number;
395 l_no_bal_for_jur number;
396 l_dummy_id number;
397 l_currency_code per_business_groups.currency_code%TYPE;
398 l_source_iv number(2) := 0;
399 l_source_iv_val VARCHAR2(30);
400 l_jur_iv number(2) := 0;
401 l_jur_iv_val VARCHAR2(30);
402 l_source_text_iv number(2) := 0;
403 l_source_text_iv_val VARCHAR2(30);
404 l_source_text2_iv number(2) := 0;
405 l_source_text2_iv_val VARCHAR2(30);
406 l_leg_code pay_legislation_rules.legislation_code%TYPE;
407 l_seq_number NUMBER(2);
408 l_no_input_values NUMBER(10);
409
410 l_message VARCHAR2(255);
411 l_procedure VARCHAR2(100);
412
413 cursor csr_is_balance_fed (p_balance_type_id number,
414 p_business_group number)
415 is
416 select balance_feed_id
417 from pay_balance_feeds_f BF,
418 pay_input_values_f IV,
419 pay_element_types_f ET,
420 pay_element_classifications EC
421 where EC.classification_name = 'Balance Initialization'
422 and ET.classification_id = EC.classification_id
423 and IV.element_type_id = ET.element_type_id
424 and IV.input_value_id = BF.input_value_id
425 and BF.balance_type_id = p_balance_type_id
426 and nvl(BF.business_group_id, p_business_group) = p_business_group;
427
428 CURSOR csr_bg IS
429 SELECT pbg.business_group_id
430 ,bbh.business_group_name
431 ,pbg.currency_code
432 ,pbg.legislation_code
433 FROM pay_balance_batch_headers bbh
434 ,per_business_groups pbg
435 WHERE batch_id = p_batch_id
436 AND upper(pbg.name) = upper(bbh.business_group_name);
437
438 CURSOR csr_rule1 (p_leg_code IN VARCHAR2)
439 IS
440 SELECT 1, rule_mode
441 FROM pay_legislation_rules
442 WHERE rule_type ='SOURCE_IV'
443 AND legislation_code = p_leg_code;
444
445 CURSOR csr_rule2 (p_leg_code IN VARCHAR2)
446 IS
447 SELECT 1, rule_mode
448 FROM pay_legislation_rules
449 WHERE rule_type ='SOURCE_TEXT_IV'
450 AND legislation_code = p_leg_code;
451
452 CURSOR csr_rule3 (p_leg_code IN VARCHAR2)
453 IS
454 select 1, input_value_name
455 from pay_legislation_contexts plc, ff_contexts fc
456 where legislation_code= p_leg_code
457 and plc.context_id = fc.context_id
458 and fc.context_name = 'JURISDICTION_CODE';
459
460 CURSOR csr_rule4 (p_leg_code IN VARCHAR2)
461 IS
462 select 1, input_value_name
463 from pay_legislation_contexts plc, ff_contexts fc
464 where legislation_code= p_leg_code
465 and plc.context_id = fc.context_id
466 and fc.context_name = 'SOURCE_TEXT2';
467
468 BEGIN
469
470 g_debug := hr_utility.debug_enabled ;
471 l_procedure := g_package || 'create_bal_upl_struct' ;
472 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
473
474 OPEN csr_bg;
478
475 FETCH csr_bg
476 INTO l_bg_id, l_bg_name, l_currency_code, l_leg_code;
477 CLOSE csr_bg;
479
480 retcode := validate_batch_data (p_batch_id);
481
482 --
483 OPEN csr_rule1 (l_leg_code);
484 FETCH csr_rule1
485 INTO l_source_iv, l_source_iv_val;
486 --
487 IF csr_rule1%NOTFOUND THEN
488 --
489 l_source_iv := 0;
490 l_source_iv_val := NULL;
491 END IF;
492 --
493 CLOSE csr_rule1;
494
495 OPEN csr_rule2 (l_leg_code);
496 FETCH csr_rule2
497 INTO l_source_text_iv, l_source_text_iv_val;
498 --
499 IF csr_rule2%NOTFOUND THEN
500 --
501 l_source_text_iv := 0;
502 l_source_text_iv_val := NULL;
503 END IF;
504 --
505 CLOSE csr_rule2;
506
507 OPEN csr_rule3 (l_leg_code);
508 FETCH csr_rule3
509 INTO l_jur_iv, l_jur_iv_val;
510 --
511 IF csr_rule3%NOTFOUND THEN
512 --
513 l_jur_iv := 0;
514 l_jur_iv_val := NULL;
515 END IF;
516 --
517 CLOSE csr_rule3;
518
519 OPEN csr_rule4 (l_leg_code);
520 FETCH csr_rule4
521 INTO l_source_text2_iv, l_source_text2_iv_val;
522 --
523 IF csr_rule4%NOTFOUND THEN
524 --
525 l_source_text2_iv := 0;
526 l_source_text2_iv_val := NULL;
527 END IF;
528 --
529 CLOSE csr_rule4;
530
531 l_jur_count := 1;
532
533 while (l_jur_count < g_nxt_free_jl) loop
534
535 pay_in_utils.set_location(g_debug,l_procedure,20);
536 l_jur_level := g_jur_lev_tbl(l_jur_count);
537
538 l_no_bal_for_jur := 0;
539 l_bal_count := 1;
540
541 while (l_bal_count < g_nxt_free_baltyp) loop
542 if g_baltyp_tbl_jl(l_bal_count) = l_jur_level then
543 l_no_bal_for_jur := l_no_bal_for_jur + 1;
544 end if;
545 l_bal_count := l_bal_count + 1;
546 end loop;
547
548 IF (p_input_value_limit IS null) OR (p_input_value_limit < 5 ) THEN
549 l_no_input_values := 5;
550 ELSE
551 l_no_input_values := p_input_value_limit;
552
553 END IF;
554
555
556 /* for cases where number of balances per jd > 15 */
557 l_n_elems := ceil (l_no_bal_for_jur / (l_no_input_values - (1 + l_source_iv + l_source_text_iv + l_source_text2_iv)));
558
559 l_bal_count := 1;
560 for i in 1 .. l_n_elems loop
561
562 pay_in_utils.set_location(g_debug,l_procedure,30);
563 j := 1;
564 while (l_bal_count< g_nxt_free_baltyp
565 and j <= l_no_input_values) loop
566
567 -- Does this balance have the same jurisdiction level as the
568 -- current jurisdiction level.
569
570 pay_in_utils.set_location(g_debug,l_procedure,40);
571 if (g_baltyp_tbl_jl(l_bal_count) = l_jur_level) then
572
573 -- Does this balance already have an initial balance feed.
574
575 open csr_is_balance_fed(g_baltyp_tbl_id(l_bal_count),
576 l_bg_id);
577 fetch csr_is_balance_fed into l_dummy_id;
578
579 if (csr_is_balance_fed%notfound) then
580 /*
581 If this is the first balance found for this element
582 create the element.
583 */
584 l_bal_name := g_baltyp_tbl_name(l_bal_count);
585 if j = 1 then
586 l_seq_number := 1;
587 /*
588 create an element type and name it as follows:
589 initial_value_element concatenated with the
590 batch id, jurisdiction level, and a number
591 identifying which element type it is that's being
592 created.
593 */
594 l_element_name := 'Initial_Value_Element_' ||
595 p_batch_id ||
596 '_' ||
597 l_jur_level||
598 '_' ||
599 to_char(i);
600
601 pay_in_utils.trace (
602 'Element Name is:' || l_element_name, l_procedure);
603
604 l_element_type_id := pay_db_pay_setup.create_element (
605 p_element_name => l_element_name,
606 p_effective_start_date =>
607 to_date('01/01/0001', 'DD/MM/YYYY'),
608 p_effective_end_date =>
609 to_date('31/12/4712','DD/MM/YYYY'),
610 p_classification_name =>
611 'Balance Initialization',
612 p_input_currency_code => l_currency_code,
613 p_output_currency_code => l_currency_code,
614 p_processing_type => 'N',
615 p_adjustment_only_flag => 'Y',
616 p_process_in_run_flag => 'Y',
617 p_legislation_code => NULL,
618 p_business_group_name => l_bg_name,
619 p_processing_priority => 0,
623 'Element name after is:' || l_element_name, l_procedure);
620 p_post_termination_rule => 'Final Close');
621
622 pay_in_utils.trace (
624
625 update pay_element_types_f ELEM
626 set ELEM.element_information1 = 'B'
627 where element_type_id = l_element_type_id;
628 /*
629 create an element link for each element type created.
630 point it to each of the element type created.
631 */
632 l_elem_link_id :=
633 pay_db_pay_setup.create_element_link (
634 p_element_name => l_element_name,
635 p_link_to_all_pyrlls_fl => 'Y',
636 p_standard_link_flag => 'N',
637 p_effective_start_date =>
638 to_date('01-01-0001','DD-MM-YYYY'),
639 p_effective_end_date =>
640 to_date('31-12-4712','DD-MM-YYYY'),
641 p_business_group_name => l_bg_name);
642 /*
643 create a 'Jurisdiction' input value for each
644 element type.
645 */
646 if l_leg_code =g_leg_code then
647 IF l_jur_iv = 1 then
648 l_input_val_id :=
649 pay_db_pay_setup.create_input_value (
650 p_element_name => l_element_name,
651 p_name => l_jur_iv_val,
652 p_uom_code => 'C',
653 p_business_group_name => l_bg_name,
654 p_display_sequence => l_seq_number,
655 p_effective_start_date =>
656 to_date('01-01-0001','DD-MM-YYYY'),
657 p_effective_end_date =>
658 to_date('31-12-4712','DD-MM-YYYY'));
659 l_seq_number := l_seq_number + 1;
660
661 hr_input_values.create_link_input_value(
662 p_insert_type => 'INSERT_INPUT_VALUE',
663 p_element_link_id => l_elem_link_id,
664 p_input_value_id => l_input_val_id,
665 p_input_value_name => l_jur_iv_val,
666 p_costable_type => NULL,
667 p_validation_start_date =>
668 to_date('01-01-0001','DD-MM-YYYY'),
669 p_validation_end_date =>
670 to_date('31-12-4712','DD-MM-YYYY'),
671 p_default_value => NULL,
672 p_max_value => NULL,
673 p_min_value => NULL,
674 p_warning_or_error_flag => NULL,
675 p_hot_default_flag => NULL,
676 p_legislation_code => NULL,
677 p_pay_value_name => NULL,
678 p_element_type_id => l_element_type_id);
679 END IF; -- l_jur_iv = 1
680 end if; -- g_leg_code = 'IN'
681
682 if l_source_iv = 1 then
683
684 l_input_val_id :=
685 pay_db_pay_setup.create_input_value (
686 p_element_name => l_element_name,
687 p_name => l_source_iv_val,
688 p_uom_code => 'C',
689 p_business_group_name => l_bg_name,
690 p_display_sequence => l_seq_number,
691 p_effective_start_date =>
692 to_date('01-01-0001','DD-MM-YYYY'),
693 p_effective_end_date =>
694 to_date('31-12-4712','DD-MM-YYYY'));
695 l_seq_number := l_seq_number + 1;
696
697 hr_input_values.create_link_input_value(
698 p_insert_type => 'INSERT_INPUT_VALUE',
699 p_element_link_id => l_elem_link_id,
700 p_input_value_id => l_input_val_id,
701 p_input_value_name => l_source_iv_val,
702 p_costable_type => NULL,
703 p_validation_start_date =>
704 to_date('01-01-0001','DD-MM-YYYY'),
705 p_validation_end_date =>
706 to_date('31-12-4712','DD-MM-YYYY'),
707 p_default_value => NULL,
708 p_max_value => NULL,
709 p_min_value => NULL,
710 p_warning_or_error_flag => NULL,
711 p_hot_default_flag => NULL,
712 p_legislation_code => NULL,
713 p_pay_value_name => NULL,
714 p_element_type_id => l_element_type_id);
715
716 end if; -- l_source_iv = 1
717
718 if l_source_text_iv = 1 then
719
720 l_input_val_id :=
721 pay_db_pay_setup.create_input_value (
722 p_element_name => l_element_name,
726 p_display_sequence => l_seq_number,
723 p_name => l_source_text_iv_val,
724 p_uom_code => 'C',
725 p_business_group_name => l_bg_name,
727 p_effective_start_date =>
728 to_date('01-01-0001','DD-MM-YYYY'),
729 p_effective_end_date =>
730 to_date('31-12-4712','DD-MM-YYYY'));
731 l_seq_number := l_seq_number + 1;
732
733 hr_input_values.create_link_input_value(
734 p_insert_type => 'INSERT_INPUT_VALUE',
735 p_element_link_id => l_elem_link_id,
736 p_input_value_id => l_input_val_id,
737 p_input_value_name => l_source_text_iv_val,
738 p_costable_type => NULL,
739 p_validation_start_date =>
740 to_date('01-01-0001','DD-MM-YYYY'),
741 p_validation_end_date =>
742 to_date('31-12-4712','DD-MM-YYYY'),
743 p_default_value => NULL,
744 p_max_value => NULL,
745 p_min_value => NULL,
746 p_warning_or_error_flag => NULL,
747 p_hot_default_flag => NULL,
748 p_legislation_code => NULL,
749 p_pay_value_name => NULL,
750 p_element_type_id => l_element_type_id);
751
752 end if; -- l_source_text_iv = 1
753
754 if l_source_text2_iv = 1 then
755
756 l_input_val_id :=
757 pay_db_pay_setup.create_input_value (
758 p_element_name => l_element_name,
759 p_name => l_source_text2_iv_val,
760 p_uom_code => 'C',
761 p_business_group_name => l_bg_name,
762 p_display_sequence => l_seq_number,
763 p_effective_start_date =>
764 to_date('01-01-0001','DD-MM-YYYY'),
765 p_effective_end_date =>
766 to_date('31-12-4712','DD-MM-YYYY'));
767 l_seq_number := l_seq_number + 1;
768
769 hr_input_values.create_link_input_value(
770 p_insert_type => 'INSERT_INPUT_VALUE',
771 p_element_link_id => l_elem_link_id,
772 p_input_value_id => l_input_val_id,
773 p_input_value_name => l_source_text2_iv_val,
774 p_costable_type => NULL,
775 p_validation_start_date =>
776 to_date('01-01-0001','DD-MM-YYYY'),
777 p_validation_end_date =>
778 to_date('31-12-4712','DD-MM-YYYY'),
779 p_default_value => NULL,
780 p_max_value => NULL,
781 p_min_value => NULL,
782 p_warning_or_error_flag => NULL,
783 p_hot_default_flag => NULL,
784 p_legislation_code => NULL,
785 p_pay_value_name => NULL,
786 p_element_type_id => l_element_type_id);
787
788 end if; -- l_source_text2_iv = 1
789
790 j := l_seq_number;
791
792 end if;
793 /*
794 create an input value for each balance_name selected and
795 name it after the balance it is created for.
796 */
797
798 l_input_val_id := pay_db_pay_setup.create_input_value (
799 p_element_name => l_element_name,
800 p_name =>
801 substr(l_bal_name, 1, 28)||j,
802 p_uom_code =>
803 g_baltyp_tbl_uom(l_bal_count),
804 p_business_group_name => l_bg_name,
805 p_effective_start_date =>
806 to_date('01-01-0001','DD-MM-YYYY'),
807 p_effective_end_date =>
808 to_date('31-12-4712','DD-MM-YYYY'),
809 p_display_sequence => j);
810 /*
811 create a balance feed for each input value created.
812 point each to its corresponding input value.
813 */
814 hr_balances.ins_balance_feed(
815 p_option => 'INS_MANUAL_FEED',
816 p_input_value_id => l_input_val_id,
817 p_element_type_id => l_element_type_id,
818 p_primary_classification_id => NULL,
819 p_sub_classification_id => NULL,
820 p_sub_classification_rule_id => NULL,
821 p_balance_type_id =>
822 g_baltyp_tbl_id(l_bal_count),
823 p_scale => '1',
827 p_legislation_code => NULL,
824 p_session_date =>
825 to_date('01-01-0001','DD-MM-YYYY'),
826 p_business_group => l_bg_id,
828 p_mode => 'USER');
829 /*
830 create a link input value for each input value created.
831 */
832 hr_input_values.create_link_input_value(
833 p_insert_type => 'INSERT_INPUT_VALUE',
834 p_element_link_id => l_elem_link_id,
835 p_input_value_id => l_input_val_id,
836 p_input_value_name =>
837 substr(l_bal_name, 1 , 28)||j,
838 p_costable_type => NULL,
839 p_validation_start_date =>
840 to_date('01-01-0001','DD-MM-YYYY'),
841 p_validation_end_date =>
842 to_date('31-12-4712','DD-MM-YYYY'),
843 p_default_value => NULL,
844 p_max_value => NULL,
845 p_min_value => NULL,
846 p_warning_or_error_flag => NULL,
847 p_hot_default_flag => NULL,
848 p_legislation_code => NULL,
849 p_pay_value_name => NULL,
850 p_element_type_id => l_element_type_id);
851
852 j := j + 1;
853 end if;
854 close csr_is_balance_fed;
855 end if;
856 l_bal_count := l_bal_count + 1;
857 end loop;
858
859 end loop;
860 l_jur_count := l_jur_count + 1;
861 end loop;
862
863 commit;
864 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
865 EXCEPTION
866 WHEN OTHERS THEN
867 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,60);
868 IF csr_bg%ISOPEN THEN
869 CLOSE csr_bg;
870 END IF;
871 IF csr_rule1%ISOPEN THEN
872 CLOSE csr_rule1;
873 END IF;
874 IF csr_rule2%ISOPEN THEN
875 CLOSE csr_rule2;
876 END IF;
877 IF csr_rule3%ISOPEN THEN
878 CLOSE csr_rule3;
879 END IF;
880 IF csr_rule4%ISOPEN THEN
881 CLOSE csr_rule4;
882 END IF;
883 IF csr_is_balance_fed%ISOPEN THEN
884 CLOSE csr_is_balance_fed;
885 END IF;
886 errbuf :=SQLERRM;
887 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
888 pay_in_utils.trace(l_message,l_procedure);
889 local_error(retcode,'create_bal_upl_struct',1);
890
891 END create_bal_upl_struct;
892
893 BEGIN
894 g_nxt_free_baltyp := 1;
895 g_nxt_free_baldim := 1;
896 g_nxt_free_jl := 1;
897 END pay_in_bal_upl_struct;
898