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