[Home] [Help]
PACKAGE BODY: APPS.PAY_IP_BAL_UPL_STRUCT
Source
1 PACKAGE BODY pay_ip_bal_upl_struct AS
2 /* $Header: pyipbups.pkb 115.3 2002/12/04 12:35:59 atrivedi 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
9 -- Balance Type Cache
10 g_baltyp_tbl_id num_array;
11 g_baltyp_tbl_jl num_array;
12 g_baltyp_tbl_name char_array;
13 g_baltyp_tbl_uom char_array;
14 g_nxt_free_baltyp number;
15
16 -- Balance Dimension Cache
17 g_baldim_tbl_id num_array;
18 g_baldim_tbl_name char_array;
19 g_nxt_free_baldim number;
20
21 -- Jurisdiction Level Cache
22 g_jur_lev_tbl num_array;
23 g_nxt_free_jl number;
24
25 PROCEDURE local_error(retcode OUT NOCOPY number,
26 p_procedure IN varchar2,
27 p_step IN number) IS
28
29 /* This procedure is called whenever an error needs to be raised and
30 the retcode is set to 2 to indicate an error has occurred.
31 */
32
33 BEGIN
34
35 retcode := 2;
36 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
37 hr_utility.set_message_token('PROCEDURE', 'paybalup.'||p_procedure);
38 hr_utility.set_message_token('STEP', p_step);
39 hr_utility.raise_error;
40
41 END local_error;
42
43 procedure put_jl_in_cache (p_jl number)
44 is
45 l_jur_level number;
46 l_count number;
47 l_found boolean;
48 begin
49
50 -- Search for the defined balance in the Cache.
51
52 hr_utility.set_location('paybalup.put_jl_in_cache', 10);
53 l_jur_level := nvl(p_jl, 999);
54 l_count := 1;
55 l_found := FALSE;
56 while (l_count < g_nxt_free_jl and l_found = FALSE) loop
57 if (l_jur_level = g_jur_lev_tbl(l_count)) then
58 hr_utility.set_location('paybalup.put_jl_in_cache', 20);
59 l_found := TRUE;
60 end if;
61 l_count := l_count + 1;
62 end loop;
63
64 hr_utility.set_location('paybalup.put_jl_in_cache', 30);
65 if (l_found = FALSE) then
66 g_jur_lev_tbl(g_nxt_free_jl) := l_jur_level;
67 g_nxt_free_jl := g_nxt_free_jl + 1;
68 end if;
69
70 end put_jl_in_cache;
71
72 procedure check_balance_type(p_baltype_id out NOCOPY number,
73 p_baltype_name varchar2,
74 p_busgrp_id number,
75 p_leg_code varchar2,
76 retcode out NOCOPY number)
77 is
78 l_balance_type_id number;
79 l_baltyp_name varchar2(80);
80 l_bal_uom varchar2(80);
81 l_jurisdiction_level number;
82 l_count number;
83 l_found boolean;
84 begin
85
86 -- Search for the defined balance in the Cache.
87
88 hr_utility.set_location('paybalup.check_balance_type', 10);
89 hr_utility.trace('paybalup.check_balance_type p_baltype_id ' || p_baltype_id );
90 hr_utility.trace('paybalup.check_balance_type p_baltype_name ' || p_baltype_name);
91 hr_utility.trace('paybalup.check_balance_type p_busgrp_id ' || p_busgrp_id);
92 hr_utility.trace('paybalup.check_balance_type p_leg_code ' || p_leg_code);
93 l_balance_type_id := null;
94 l_baltyp_name := upper(p_baltype_name);
95 l_count := 1;
96 l_found := FALSE;
97 while (l_count < g_nxt_free_baltyp and l_found = FALSE) loop
98 if (l_baltyp_name = g_baltyp_tbl_name(l_count)) then
99 hr_utility.set_location('paybalup.check_balance_type', 20);
100 l_balance_type_id := g_baltyp_tbl_id(l_count);
101 l_found := TRUE;
102 end if;
103 l_count := l_count + 1;
104 end loop;
105
106 -- If the balance is not in the Cache get it from the database.
107
108 hr_utility.set_location('paybalup.check_balance_type' || l_baltyp_name, 30);
109 if (l_found = FALSE) then
110 BEGIN
111
112 select balance_type_id,
113 nvl(jurisdiction_level, 999),
114 balance_uom
115 into l_balance_type_id, l_jurisdiction_level, l_bal_uom
116 from pay_balance_types
117 where upper(balance_name) = l_baltyp_name
118 and ((business_group_id = p_busgrp_id)
119 or( business_group_id is null
120 and legislation_code = p_leg_code)
121 or( business_group_id is null
122 and legislation_code is null)
123 )
124 for update of balance_type_id;
125
126
127 -- Place the defined balance in cache.
128
129 hr_utility.set_location('paybalup.check_balance_type', 40);
130 g_baltyp_tbl_name(g_nxt_free_baltyp) := l_baltyp_name;
131 g_baltyp_tbl_uom(g_nxt_free_baltyp) := l_bal_uom;
132 g_baltyp_tbl_id(g_nxt_free_baltyp) := l_balance_type_id;
133 g_baltyp_tbl_jl(g_nxt_free_baltyp) := l_jurisdiction_level;
134 g_nxt_free_baltyp := g_nxt_free_baltyp + 1;
135 put_jl_in_cache(l_jurisdiction_level);
136
137 EXCEPTION WHEN no_data_found THEN
138 hr_utility.trace('Error: Failure to find balance type');
139 local_error(retcode, 'check_balance_type',1);
140
141 END;
142
143 end if;
144
145 p_baltype_id := l_balance_type_id;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 p_baltype_id := NULL;
150 end check_balance_type;
151
152
153 procedure check_balance_dim(p_baldim_id out NOCOPY number,
154 p_baldim_name varchar2,
155 p_busgrp_id number,
156 p_leg_code varchar2,
157 retcode out NOCOPY number)
158 is
159 l_baldim_name varchar2(80);
160 l_count number;
161 l_found boolean;
162 l_balance_dim_id number;
163 begin
164
165 -- Search for the defined balance in the Cache.
166
167 hr_utility.set_location('paybalup.check_balance_dim', 10);
168 l_balance_dim_id := null;
169 l_baldim_name := upper(p_baldim_name);
170 l_count := 1;
171 l_found := FALSE;
172 while (l_count < g_nxt_free_baldim and l_found = FALSE) loop
173 if (l_baldim_name = g_baldim_tbl_name(l_count)) then
174 hr_utility.set_location('paybalup.check_balance_dim', 20);
175 l_balance_dim_id := g_baldim_tbl_id(l_count);
176 l_found := TRUE;
177 end if;
178 l_count := l_count + 1;
179 end loop;
180
181 -- If the balance is not in the Cache get it from the database.
182
183 hr_utility.set_location('paybalup.check_balance_dim', 30);
184 if (l_found = FALSE) then
185 BEGIN
186
187 select balance_dimension_id
188 into l_balance_dim_id
189 from pay_balance_dimensions
190 where upper(dimension_name) = l_baldim_name
191 and ((business_group_id = p_busgrp_id)
192 or( business_group_id is null
193 and legislation_code = p_leg_code)
194 or( business_group_id is null
195 and legislation_code is null)
196 );
197
198
199 -- Place the defined balance in cache.
200
201 hr_utility.set_location('paybalup.check_balance_dim', 40);
202 g_baldim_tbl_name(g_nxt_free_baldim) := l_baldim_name;
203 g_baldim_tbl_id(g_nxt_free_baldim) := l_balance_dim_id;
204 g_nxt_free_baldim := g_nxt_free_baldim + 1;
205
206 EXCEPTION WHEN no_data_found THEN
207 hr_utility.trace('Error: Failure to find balance dimension');
208 local_error(retcode,'check_balance_dim',2);
209
210 END;
211
212 end if;
213
214 p_baldim_id := l_balance_dim_id;
215 EXCEPTION
216 WHEN OTHERS THEN
217 p_baldim_id := NULL;
218 end check_balance_dim;
219
220 FUNCTION validate_batch_data (p_batch_id number) RETURN number IS
221
222 /* This function verifies that the business group, balance types, and
223 balance dimensions actually exist. If not, it would return a retcode
224 of 2 and raise an exception.
225 */
226
227 retcode number := 0;
228 i number := 0;
229 l_bg_id per_business_groups.business_group_id%TYPE;
230 l_leg_code per_business_groups.legislation_code%TYPE;
231 l_bt_id pay_balance_types.balance_type_id%TYPE;
232 l_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
233
234 cursor c_each_batch (c_batch_id number) is
235 select balance_name,
236 dimension_name
237 from pay_balance_batch_lines
238 where batch_id = c_batch_id;
239
240 BEGIN
241 hr_utility.set_location('paybalup.validate_batch_data', 10);
242 BEGIN /* check business group exists */
243 select hou.business_group_id,
244 hou.legislation_code
245 into l_bg_id,
246 l_leg_code
247 from per_business_groups hou,
248 pay_balance_batch_headers bbh
249 where bbh.batch_id = p_batch_id
250 and upper(hou.name) = upper(bbh.business_group_name);
251 EXCEPTION WHEN no_data_found THEN
252 local_error(retcode, 'validate_batch_data', 3);
253 END;
254
255 hr_utility.set_location('paybalup.validate_batch_data', 20);
256 for l_each_batch_rec in c_each_batch (p_batch_id) loop
257 check_balance_type(l_bt_id, l_each_batch_rec.balance_name,
258 l_bg_id,
259 l_leg_code,
260 retcode);
261 check_balance_dim(l_bal_dim_id, l_each_batch_rec.dimension_name,
262 l_bg_id,
263 l_leg_code,
264 retcode);
265 end loop;
266
267 return retcode;
268 END validate_batch_data;
269
270
271 PROCEDURE create_bal_upl_struct (errbuf OUT NOCOPY varchar2,
272 retcode OUT NOCOPY number,
273 p_input_value_limit IN number,
274 p_batch_id IN number) IS
275
276 -- errbuf and retcode are special parameters needed for the SRS.
277 -- retcode = 0 means no error and retcode = 2 means an error occurred.
278
279 l_n_elems number := 0;
280 j number;
281 l_bal_uom pay_balance_types.balance_uom%TYPE;
282 l_element_name pay_element_types.element_name%TYPE;
283 l_element_type_id pay_element_types.element_type_id%TYPE;
284 l_elem_link_id pay_element_links.element_link_id%TYPE;
285 l_input_val_id pay_input_values.input_value_id%TYPE;
286 l_bal_name pay_balance_types.balance_name%TYPE;
287 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
288 l_bal_feed_id pay_balance_feeds.balance_feed_id%TYPE;
289 l_bg_name hr_organization_units.name%TYPE;
290 l_bg_id hr_organization_units.organization_id%TYPE;
291 l_jur_level number;
292 l_jur_count number;
293 l_bal_count number;
294 l_no_bal_for_jur number;
295 l_dummy_id number;
296 l_currency_code varchar2(3);
297 l_source_iv number(2) := 0;
298 l_source_iv_val VARCHAR2(30);
299 l_source_text_iv number(2) := 0;
300 l_source_text_iv_val VARCHAR2(30);
301 l_leg_code VARCHAR2(150);
302 l_seq_number NUMBER(2);
303 l_no_input_values NUMBER(10);
304
305 cursor csr_is_balance_fed (p_balance_type_id number,
306 p_business_group number)
307 is
308 select balance_feed_id
309 from pay_balance_feeds_f BF,
310 pay_input_values_f IV,
311 pay_element_types_f ET,
312 pay_element_classifications EC
313 where EC.classification_name = 'Balance Initialization'
314 and ET.classification_id = EC.classification_id
315 and IV.element_type_id = ET.element_type_id
316 and IV.input_value_id = BF.input_value_id
317 and BF.balance_type_id + 0 = p_balance_type_id
318 and nvl(BF.business_group_id, p_business_group) = p_business_group;
319
320 BEGIN
321 hr_utility.set_location('paybalup.create_bal_upl_struct', 10);
322
323 select pbg.business_group_id, bbh.business_group_name ,
324 pbg.currency_code, pbg.legislation_code
325 into l_bg_id, l_bg_name,
326 l_currency_code, l_leg_code
327 from pay_balance_batch_headers bbh,
328 per_business_groups pbg
329 where batch_id = p_batch_id
330 and upper(pbg.name) = upper(bbh.business_group_name);
331
332 retcode := validate_batch_data (p_batch_id);
333
334
335 begin
336 select 1, rule_mode
337 into l_source_iv, l_source_iv_val
338 from pay_legislation_rules
339 where rule_type ='SOURCE_IV'
340 and legislation_code = l_leg_code;
341
342 exception
343 when no_data_found then
344 l_source_iv := 0;
345 l_source_iv_val := NULL;
346 end;
347
348 begin
349 select 1, rule_mode
350 into l_source_text_iv, l_source_text_iv_val
351 from pay_legislation_rules
352 where rule_type ='SOURCE_TEXT_IV'
353 and legislation_code = l_leg_code;
354
355 exception
356 when no_data_found then
357 l_source_text_iv := 0;
358 l_source_text_iv_val := NULL;
359 end;
360
361
362 l_jur_count := 1;
363 while (l_jur_count < g_nxt_free_jl) loop
364
365 hr_utility.set_location('paybalup.create_bal_upl_struct', 20);
366 l_jur_level := g_jur_lev_tbl(l_jur_count);
367
368 l_no_bal_for_jur := 0;
369 l_bal_count := 1;
370 while (l_bal_count < g_nxt_free_baltyp) loop
371 if g_baltyp_tbl_jl(l_bal_count) = l_jur_level then
372 l_no_bal_for_jur := l_no_bal_for_jur + 1;
376
373 end if;
374 l_bal_count := l_bal_count + 1;
375 end loop;
377 if l_leg_code IN ('US','CA') then
378 l_no_input_values := p_input_value_limit - 1;
379 else
380 l_no_input_values := p_input_value_limit;
381 end if;
382
383 /* for cases where number of balances per jd > 15 */
384 l_n_elems := ceil (l_no_bal_for_jur / (l_no_input_values - (l_source_iv + l_source_text_iv)));
385
386 l_bal_count := 1;
387 for i in 1 .. l_n_elems loop
388
389 hr_utility.set_location('paybalup.create_bal_upl_struct', 30);
390 j := 1;
391 while (l_bal_count< g_nxt_free_baltyp
392 and j <= p_input_value_limit) loop
393
394 -- Does this balance have the same jurisdiction level as the
395 -- current jurisdiction level.
396
397 hr_utility.set_location('paybalup.create_bal_upl_struct', 40);
398 if (g_baltyp_tbl_jl(l_bal_count) = l_jur_level) then
399
400 -- Does this balance already have an initial balance feed.
401
402 open csr_is_balance_fed(g_baltyp_tbl_id(l_bal_count),
403 l_bg_id);
404 fetch csr_is_balance_fed into l_dummy_id;
405
406 if (csr_is_balance_fed%notfound) then
407 /*
408 If this is the first balance found for this element
409 create the element.
410 */
411 if j = 1 then
412 l_seq_number := 1;
413 /*
414 create an element type and name it as follows:
415 initial_value_element concatenated with the
416 batch id, jurisdiction level, and a number
417 identifying which element type it is that's being
418 created.
419 */
420 l_element_name := 'Initial_Value_Element_' ||
421 p_batch_id ||
422 '_' ||
423 l_jur_level||
424 '_' ||
425 to_char(i);
426
427 hr_utility.trace (
428 'Element Name is:' || l_element_name);
429
430 l_element_type_id := pay_db_pay_setup.create_element (
431 p_element_name => l_element_name,
432 p_effective_start_date =>
433 to_date('01/01/0001', 'DD/MM/YYYY'),
434 p_effective_end_date =>
435 to_date('31/12/4712','DD/MM/YYYY'),
436 p_classification_name =>
437 'Balance Initialization',
438 p_input_currency_code => l_currency_code,
439 p_output_currency_code => l_currency_code,
440 p_processing_type => 'N',
441 p_adjustment_only_flag => 'Y',
442 p_process_in_run_flag => 'Y',
443 p_legislation_code => NULL,
444 p_business_group_name => l_bg_name,
445 p_processing_priority => 0,
446 p_post_termination_rule => 'Final Close');
447
448 hr_utility.trace (
449 'Element name after is:' || l_element_name);
450
451 update pay_element_types_f ELEM
452 set ELEM.element_information1 = 'B'
453 where element_type_id = l_element_type_id;
454 /*
455 create an element link for each element type created.
456 point it to each of the element type created.
457 */
458 l_elem_link_id :=
459 pay_db_pay_setup.create_element_link (
460 p_element_name => l_element_name,
461 p_link_to_all_pyrlls_fl => 'Y',
462 p_standard_link_flag => 'N',
463 p_effective_start_date =>
464 to_date('01-01-0001','DD-MM-YYYY'),
465 p_effective_end_date =>
466 to_date('31-12-4712','DD-MM-YYYY'),
467 p_business_group_name => l_bg_name);
468 /*
469 create a 'Jurisdiction' input value for each
470 element type.
471 */
472 if l_leg_code IN ('US','CA') then
473 l_input_val_id :=
474 pay_db_pay_setup.create_input_value (
475 p_element_name => l_element_name,
476 p_name => 'Jurisdiction',
477 p_uom_code => 'C',
478 p_business_group_name => l_bg_name,
479 p_display_sequence => l_seq_number,
480 p_effective_start_date =>
481 to_date('01-01-0001','DD-MM-YYYY'),
482 p_effective_end_date =>
483 to_date('31-12-4712','DD-MM-YYYY'));
484 l_seq_number := l_seq_number + 1;
485
486 hr_input_values.create_link_input_value(
490 p_input_value_name => 'Jurisdiction',
487 p_insert_type => 'INSERT_INPUT_VALUE',
488 p_element_link_id => l_elem_link_id,
489 p_input_value_id => l_input_val_id,
491 p_costable_type => NULL,
492 p_validation_start_date =>
493 to_date('01-01-0001','DD-MM-YYYY'),
494 p_validation_end_date =>
495 to_date('31-12-4712','DD-MM-YYYY'),
496 p_default_value => NULL,
497 p_max_value => NULL,
498 p_min_value => NULL,
499 p_warning_or_error_flag => NULL,
500 p_hot_default_flag => NULL,
501 p_legislation_code => NULL,
502 p_pay_value_name => NULL,
503 p_element_type_id => l_element_type_id);
504
505 end if; -- l_leg_code = 'US'
506
507 if l_source_iv = 1 then
508
509 l_input_val_id :=
510 pay_db_pay_setup.create_input_value (
511 p_element_name => l_element_name,
512 p_name => l_source_iv_val,
513 p_uom_code => 'C',
514 p_business_group_name => l_bg_name,
515 p_display_sequence => l_seq_number,
516 p_effective_start_date =>
517 to_date('01-01-0001','DD-MM-YYYY'),
518 p_effective_end_date =>
519 to_date('31-12-4712','DD-MM-YYYY'));
520 l_seq_number := l_seq_number + 1;
521
522 hr_input_values.create_link_input_value(
523 p_insert_type => 'INSERT_INPUT_VALUE',
524 p_element_link_id => l_elem_link_id,
525 p_input_value_id => l_input_val_id,
526 p_input_value_name => l_source_iv_val,
527 p_costable_type => NULL,
528 p_validation_start_date =>
529 to_date('01-01-0001','DD-MM-YYYY'),
530 p_validation_end_date =>
531 to_date('31-12-4712','DD-MM-YYYY'),
532 p_default_value => NULL,
533 p_max_value => NULL,
534 p_min_value => NULL,
535 p_warning_or_error_flag => NULL,
536 p_hot_default_flag => NULL,
537 p_legislation_code => NULL,
538 p_pay_value_name => NULL,
539 p_element_type_id => l_element_type_id);
540
541 end if; -- l_source_iv = 1
542
543 if l_source_text_iv = 1 then
544
545 l_input_val_id :=
546 pay_db_pay_setup.create_input_value (
547 p_element_name => l_element_name,
548 p_name => l_source_text_iv_val,
549 p_uom_code => 'C',
550 p_business_group_name => l_bg_name,
551 p_display_sequence => l_seq_number,
552 p_effective_start_date =>
553 to_date('01-01-0001','DD-MM-YYYY'),
554 p_effective_end_date =>
555 to_date('31-12-4712','DD-MM-YYYY'));
556 l_seq_number := l_seq_number + 1;
557
558 hr_input_values.create_link_input_value(
559 p_insert_type => 'INSERT_INPUT_VALUE',
560 p_element_link_id => l_elem_link_id,
561 p_input_value_id => l_input_val_id,
562 p_input_value_name => l_source_text_iv_val,
563 p_costable_type => NULL,
564 p_validation_start_date =>
565 to_date('01-01-0001','DD-MM-YYYY'),
566 p_validation_end_date =>
567 to_date('31-12-4712','DD-MM-YYYY'),
568 p_default_value => NULL,
569 p_max_value => NULL,
570 p_min_value => NULL,
571 p_warning_or_error_flag => NULL,
572 p_hot_default_flag => NULL,
573 p_legislation_code => NULL,
574 p_pay_value_name => NULL,
575 p_element_type_id => l_element_type_id);
576
577 end if; -- l_source_text_iv = 1
578
579 j := l_seq_number;
580
581 end if;
582 /*
583 create an input value for each balance_name selected and
584 name it after the balance it is created for.
585 */
586
587 l_input_val_id := pay_db_pay_setup.create_input_value (
588 p_element_name => l_element_name,
589 p_name =>
593 p_business_group_name => l_bg_name,
590 substr(l_bal_name, 1, 28)||j,
591 p_uom_code =>
592 g_baltyp_tbl_uom(l_bal_count),
594 p_effective_start_date =>
595 to_date('01-01-0001','DD-MM-YYYY'),
596 p_effective_end_date =>
597 to_date('31-12-4712','DD-MM-YYYY'),
598 p_display_sequence => j);
599 /*
600 create a balance feed for each input value created.
601 point each to its corresponding input value.
602 */
603 hr_balances.ins_balance_feed(
604 p_option => 'INS_MANUAL_FEED',
605 p_input_value_id => l_input_val_id,
606 p_element_type_id => l_element_type_id,
607 p_primary_classification_id => NULL,
608 p_sub_classification_id => NULL,
609 p_sub_classification_rule_id => NULL,
610 p_balance_type_id =>
611 g_baltyp_tbl_id(l_bal_count),
612 p_scale => '1',
613 p_session_date =>
614 to_date('01-01-0001','DD-MM-YYYY'),
615 p_business_group => l_bg_id,
616 p_legislation_code => NULL,
617 p_mode => 'USER');
618 /*
619 create a link input value for each input value created.
620 */
621 hr_input_values.create_link_input_value(
622 p_insert_type => 'INSERT_INPUT_VALUE',
623 p_element_link_id => l_elem_link_id,
624 p_input_value_id => l_input_val_id,
625 p_input_value_name =>
626 substr(l_bal_name, 1 , 28)||j,
627 p_costable_type => NULL,
628 p_validation_start_date =>
629 to_date('01-01-0001','DD-MM-YYYY'),
630 p_validation_end_date =>
631 to_date('31-12-4712','DD-MM-YYYY'),
632 p_default_value => NULL,
633 p_max_value => NULL,
634 p_min_value => NULL,
635 p_warning_or_error_flag => NULL,
636 p_hot_default_flag => NULL,
637 p_legislation_code => NULL,
638 p_pay_value_name => NULL,
639 p_element_type_id => l_element_type_id);
640
641 j := j + 1;
642 end if;
643 close csr_is_balance_fed;
644 end if;
645 l_bal_count := l_bal_count + 1;
646 end loop;
647
648 end loop;
649 l_jur_count := l_jur_count + 1;
650 end loop;
651
652 commit;
653
654 END create_bal_upl_struct;
655
656 BEGIN
657 g_nxt_free_baltyp := 1;
658 g_nxt_free_baldim := 1;
659 g_nxt_free_jl := 1;
660 END pay_ip_bal_upl_struct;