1 PACKAGE BODY paybalup AS
2 -- $Header: paybalup.pkb 120.0 2005/05/29 02:33:37 appldev noship $
3 --
4 -- Copyright (c) Oracle Corporation 1991-1995 All rights reserved.
5 --
6 --
7 /*
8 NAME
9 paybalup.pkb -- Create a structure for balance upload.
10 --
11 USAGE
12 This package can be activated from the Initial Balance
13 Structure Creation SRS available through Forms. The user
14 needs to supply the batch name to run this process.
15 --
16 DESCRIPTION
17 Given the limit of the input values per element type and the
18 batch id in that order, create_bal_upl_struct will first call
19 validate_batch_data to validate the batch data, then it will
20 create the element types, element links, input values, balance
21 feeds and link input values. The algorithm works as follows:
22 it finds all the common balances across assignments with the
23 same jurisdiction level, and order these groups of balances
24 into a list of most common to least common, and then group
25 these groups of balances into one logical element type. It
26 then creates one element link for each element type created,
27 and one input value for each group of identical balances
28 (1 balance), and one balance feed and link input value for
29 each input value created. If the next batch being processed
30 has the same balances as a previously processed batch,
31 create_bal_upl_struct would know not to create the input values,
32 balance feeds and link input values for these balances again.
33 --
34 MODIFIED (DD-MON-YYYY)
35 T Grisco 14-SEP-1995 Created.
36 T Grisco 29-SEP-1995 Major design changes.
37 T Grisco 03-OCT-1995 Fixed problem with multiple business groups.
38 T Grisco 03-OCT-1995 Removed business group name as a parameter.
39 T Grisco 03-OCT-1995 Changed some local variables' types.
40 T Grisco 16-MAY-1996 Fixed bug #366661 where selecting from
41 pay_balance_feeds_f returns more than 1 row
42 because it's date-tracked.
43 T Grisco 25-MAY-1996 Fixed performance bug #368631.
44 N Bristow 17-JUN-1996 Bug 374880. Changed structure creation to
45 make use of pl/sql tables to improve
46 performance. Also fixed previous change.
47 J Alloun 30-JUL-1996 Added error handling.
48 M Fender 10-JUL-1997 Removed show errors call
49 A Mills 28-AUG-1997 Altered date checking for NLS compliance.
50 N Bristow 06-JUL-1998 Bug 692520. Now checking the balance types
51 and dimensions for business group id.
52 M Reid 25-JUL-2000 Corrected call to ins_balance_feed to use
53 business_group_id
54 JARTHURT 15-JAN-2001 Changed hard-coded USD currency code to use
55 instead the business group's currency code.
56 Change made so this script can be used for
57 Canadian legislation.
58 M Reid 18-APR-2001 Use uom_code instead of uom for Jurisdiction
59 T Habara 15-JUL-2004 115.6 Bug 3768760. Modified csr_is_balance_fed.
60 Added nocopy. GSCC standards.
61 */
62 --
63 --
64 -- Global declarations
65 type char_array is table of varchar2(80) index by binary_integer;
66 type num_array is table of number(16) index by binary_integer;
67 --
68 -- Balance Type Cache
69 g_baltyp_tbl_id num_array;
70 g_baltyp_tbl_jl num_array;
71 g_baltyp_tbl_name char_array;
72 g_baltyp_tbl_uom char_array;
73 g_nxt_free_baltyp number;
74 --
75 -- Balance Dimension Cache
76 g_baldim_tbl_id num_array;
77 g_baldim_tbl_name char_array;
78 g_nxt_free_baldim number;
79 --
80 -- Jurisdiction Level Cache
81 g_jur_lev_tbl num_array;
82 g_nxt_free_jl number;
83 --
84 PROCEDURE local_error(retcode OUT nocopy number,
85 p_procedure IN varchar2,
86 p_step IN number) IS
87 --
88 /* This procedure is called whenever an error needs to be raised and
89 the retcode is set to 2 to indicate an error has occurred.
90 */
91 --
92 BEGIN
93 --
94 retcode := 2;
95 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
96 hr_utility.set_message_token('PROCEDURE', 'paybalup.'||p_procedure);
97 hr_utility.set_message_token('STEP', p_step);
98 hr_utility.raise_error;
99 --
100 END local_error;
101 --
102 procedure put_jl_in_cache (p_jl number)
103 is
104 l_jur_level number;
105 l_count number;
106 l_found boolean;
107 begin
108 --
109 -- Search for the defined balance in the Cache.
110 --
111 hr_utility.set_location('paybalup.put_jl_in_cache', 10);
112 l_jur_level := nvl(p_jl, 999);
113 l_count := 1;
114 l_found := FALSE;
115 while (l_count < g_nxt_free_jl and l_found = FALSE) loop
116 if (l_jur_level = g_jur_lev_tbl(l_count)) then
117 hr_utility.set_location('paybalup.put_jl_in_cache', 20);
118 l_found := TRUE;
119 end if;
120 l_count := l_count + 1;
121 end loop;
122 --
123 hr_utility.set_location('paybalup.put_jl_in_cache', 30);
124 if (l_found = FALSE) then
125 g_jur_lev_tbl(g_nxt_free_jl) := l_jur_level;
126 g_nxt_free_jl := g_nxt_free_jl + 1;
127 end if;
128 --
129 end put_jl_in_cache;
130 --
131 procedure check_balance_type(p_baltype_id out nocopy number,
132 p_baltype_name varchar2,
133 p_busgrp_id number,
134 p_leg_code varchar2,
135 retcode out nocopy number)
136 is
137 l_balance_type_id number;
138 l_baltyp_name varchar2(80);
139 l_bal_uom varchar2(80);
140 l_jurisdiction_level number;
141 l_count number;
142 l_found boolean;
143 begin
144 --
145 -- Search for the defined balance in the Cache.
146 --
147 hr_utility.set_location('paybalup.check_balance_type', 10);
148 l_balance_type_id := null;
149 l_baltyp_name := upper(p_baltype_name);
150 l_count := 1;
151 l_found := FALSE;
152 while (l_count < g_nxt_free_baltyp and l_found = FALSE) loop
153 if (l_baltyp_name = g_baltyp_tbl_name(l_count)) then
154 hr_utility.set_location('paybalup.check_balance_type', 20);
155 l_balance_type_id := g_baltyp_tbl_id(l_count);
156 l_found := TRUE;
157 end if;
158 l_count := l_count + 1;
159 end loop;
160 --
161 -- If the balance is not in the Cache get it from the database.
162 --
163 hr_utility.set_location('paybalup.check_balance_type', 30);
164 if (l_found = FALSE) then
165 BEGIN
166 --
167 select balance_type_id,
168 nvl(jurisdiction_level, 999),
169 balance_uom
170 into l_balance_type_id, l_jurisdiction_level, l_bal_uom
171 from pay_balance_types
172 where upper(balance_name) = l_baltyp_name
173 and ((business_group_id = p_busgrp_id)
174 or( business_group_id is null
175 and legislation_code = p_leg_code)
176 or( business_group_id is null
177 and legislation_code is null)
178 )
179 for update of balance_type_id;
180 --
181 --
182 -- Place the defined balance in cache.
183 --
184 hr_utility.set_location('paybalup.check_balance_type', 40);
185 g_baltyp_tbl_name(g_nxt_free_baltyp) := l_baltyp_name;
186 g_baltyp_tbl_uom(g_nxt_free_baltyp) := l_bal_uom;
187 g_baltyp_tbl_id(g_nxt_free_baltyp) := l_balance_type_id;
188 g_baltyp_tbl_jl(g_nxt_free_baltyp) := l_jurisdiction_level;
189 g_nxt_free_baltyp := g_nxt_free_baltyp + 1;
190 put_jl_in_cache(l_jurisdiction_level);
191 --
192 EXCEPTION WHEN no_data_found THEN
193 hr_utility.trace('Error: Failure to find balance type');
194 local_error(retcode, 'check_balance_type',1);
195 --
196 END;
197 --
198 end if;
199 --
200 p_baltype_id := l_balance_type_id;
201 --
202 end check_balance_type;
203 --
204 --
205 procedure check_balance_dim(p_baldim_id out nocopy number,
206 p_baldim_name varchar2,
207 p_busgrp_id number,
208 p_leg_code varchar2,
209 retcode out nocopy number)
210 is
211 l_baldim_name varchar2(80);
212 l_count number;
213 l_found boolean;
214 l_balance_dim_id number;
215 begin
216 --
217 -- Search for the defined balance in the Cache.
218 --
219 hr_utility.set_location('paybalup.check_balance_dim', 10);
220 l_balance_dim_id := null;
221 l_baldim_name := upper(p_baldim_name);
222 l_count := 1;
223 l_found := FALSE;
224 while (l_count < g_nxt_free_baldim and l_found = FALSE) loop
225 if (l_baldim_name = g_baldim_tbl_name(l_count)) then
226 hr_utility.set_location('paybalup.check_balance_dim', 20);
227 l_balance_dim_id := g_baldim_tbl_id(l_count);
228 l_found := TRUE;
229 end if;
230 l_count := l_count + 1;
231 end loop;
232 --
233 -- If the balance is not in the Cache get it from the database.
234 --
235 hr_utility.set_location('paybalup.check_balance_dim', 30);
236 if (l_found = FALSE) then
237 BEGIN
238 --
239 select balance_dimension_id
240 into l_balance_dim_id
241 from pay_balance_dimensions
242 where upper(dimension_name) = l_baldim_name
243 and ((business_group_id = p_busgrp_id)
244 or( business_group_id is null
245 and legislation_code = p_leg_code)
246 or( business_group_id is null
247 and legislation_code is null)
248 );
249 --
250 --
251 -- Place the defined balance in cache.
252 --
253 hr_utility.set_location('paybalup.check_balance_dim', 40);
254 g_baldim_tbl_name(g_nxt_free_baldim) := l_baldim_name;
255 g_baldim_tbl_id(g_nxt_free_baldim) := l_balance_dim_id;
256 g_nxt_free_baldim := g_nxt_free_baldim + 1;
257 --
258 EXCEPTION WHEN no_data_found THEN
259 hr_utility.trace('Error: Failure to find balance dimension');
260 local_error(retcode,'check_balance_dim',2);
261 --
262 END;
263 --
264 end if;
265 --
266 p_baldim_id := l_balance_dim_id;
267 --
268 end check_balance_dim;
269 --
270 FUNCTION validate_batch_data (p_batch_id number) RETURN number IS
271 --
272 /* This function verifies that the business group, balance types, and
273 balance dimensions actually exist. If not, it would return a retcode
274 of 2 and raise an exception.
275 */
276 --
277 retcode number := 0;
278 i number := 0;
279 l_bg_id per_business_groups.business_group_id%TYPE;
280 l_leg_code per_business_groups.legislation_code%TYPE;
281 l_bt_id pay_balance_types.balance_type_id%TYPE;
282 l_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
283 --
284 cursor c_each_batch (c_batch_id number) is
285 select balance_name,
286 dimension_name
287 from pay_balance_batch_lines
288 where batch_id = c_batch_id;
289 --
290 BEGIN
291 hr_utility.set_location('paybalup.validate_batch_data', 10);
292 BEGIN /* check business group exists */
293 select hou.business_group_id,
294 hou.legislation_code
295 into l_bg_id,
296 l_leg_code
297 from per_business_groups hou,
298 pay_balance_batch_headers bbh
299 where bbh.batch_id = p_batch_id
300 and upper(hou.name) = upper(bbh.business_group_name);
301 EXCEPTION WHEN no_data_found THEN
302 local_error(retcode, 'validate_batch_data', 3);
303 END;
304 --
305 hr_utility.set_location('paybalup.validate_batch_data', 20);
306 for l_each_batch_rec in c_each_batch (p_batch_id) loop
307 check_balance_type(l_bt_id, l_each_batch_rec.balance_name,
308 l_bg_id,
309 l_leg_code,
310 retcode);
311 check_balance_dim(l_bal_dim_id, l_each_batch_rec.dimension_name,
312 l_bg_id,
313 l_leg_code,
314 retcode);
315 end loop;
316 --
317 return retcode;
318 END validate_batch_data;
319 --
320 --
321 PROCEDURE create_bal_upl_struct (errbuf OUT nocopy varchar2,
322 retcode OUT nocopy number,
323 p_input_value_limit IN number,
324 p_batch_id IN number) IS
325 --
326 -- errbuf and retcode are special parameters needed for the SRS.
327 -- retcode = 0 means no error and retcode = 2 means an error occurred.
328 --
329 l_n_elems number := 0;
330 j number;
331 l_bal_uom pay_balance_types.balance_uom%TYPE;
332 l_element_name pay_element_types.element_name%TYPE;
333 l_element_type_id pay_element_types.element_type_id%TYPE;
334 l_elem_link_id pay_element_links.element_link_id%TYPE;
335 l_input_val_id pay_input_values.input_value_id%TYPE;
336 l_bal_name pay_balance_types.balance_name%TYPE;
337 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
338 l_bal_feed_id pay_balance_feeds.balance_feed_id%TYPE;
339 l_bg_name hr_organization_units.name%TYPE;
340 l_bg_id hr_organization_units.organization_id%TYPE;
341 l_jur_level number;
342 l_jur_count number;
343 l_bal_count number;
344 l_no_bal_for_jur number;
345 l_dummy_id number;
346 l_currency_code varchar2(3);
347 --
348 cursor csr_is_balance_fed (p_balance_type_id number,
349 p_business_group number)
350 is
351 select balance_feed_id
352 from pay_balance_feeds_f BF,
353 pay_input_values_f IV,
354 pay_element_types_f ET,
355 pay_element_classifications EC
356 where EC.balance_initialization_flag = 'Y'
357 and ET.classification_id = EC.classification_id
358 and ET.effective_start_date = to_date('0001/01/01','YYYY/MM/DD')
359 and ET.effective_end_date = to_date('4712/12/31','YYYY/MM/DD')
360 and IV.element_type_id = ET.element_type_id
361 and IV.input_value_id = BF.input_value_id
362 and IV.effective_start_date = to_date('0001/01/01','YYYY/MM/DD')
363 and IV.effective_end_date = to_date('4712/12/31','YYYY/MM/DD')
364 and BF.balance_type_id = p_balance_type_id
365 and BF.effective_start_date = to_date('0001/01/01','YYYY/MM/DD')
366 and BF.effective_end_date = to_date('4712/12/31','YYYY/MM/DD')
367 and nvl(BF.business_group_id, p_business_group) = p_business_group;
368 --
369 BEGIN
370 hr_utility.set_location('paybalup.create_bal_upl_struct', 10);
371 --
372 select pbg.business_group_id, pbg.name ,
373 pbg.currency_code
374 into l_bg_id, l_bg_name,
375 l_currency_code
376 from pay_balance_batch_headers bbh,
377 per_business_groups pbg
378 where batch_id = p_batch_id
379 and upper(pbg.name) = upper(bbh.business_group_name);
380 --
381 retcode := validate_batch_data (p_batch_id);
382 --
383 l_jur_count := 1;
384 while (l_jur_count < g_nxt_free_jl) loop
385 --
386 hr_utility.set_location('paybalup.create_bal_upl_struct', 20);
387 l_jur_level := g_jur_lev_tbl(l_jur_count);
388 --
389 l_no_bal_for_jur := 0;
390 l_bal_count := 1;
391 while (l_bal_count < g_nxt_free_baltyp) loop
392 if g_baltyp_tbl_jl(l_bal_count) = l_jur_level then
393 l_no_bal_for_jur := l_no_bal_for_jur + 1;
394 end if;
395 l_bal_count := l_bal_count + 1;
396 end loop;
397 --
398 /* for cases where number of balances per jd > 15 */
399 l_n_elems := ceil (l_no_bal_for_jur / p_input_value_limit);
400 --
401 l_bal_count := 1;
402 for i in 1 .. l_n_elems loop
403 --
404 hr_utility.set_location('paybalup.create_bal_upl_struct', 30);
405 j := 1;
406 while (l_bal_count< g_nxt_free_baltyp
407 and j <= p_input_value_limit) loop
408 --
409 -- Does this balance have the same jurisdiction level as the
410 -- current jurisdiction level.
411 --
412 hr_utility.set_location('paybalup.create_bal_upl_struct', 40);
413 if (g_baltyp_tbl_jl(l_bal_count) = l_jur_level) then
414 --
415 -- Does this balance already have an initial balance feed.
416 --
417 open csr_is_balance_fed(g_baltyp_tbl_id(l_bal_count),
418 l_bg_id);
419 fetch csr_is_balance_fed into l_dummy_id;
420
421 if (csr_is_balance_fed%notfound) then
422 /*
423 If this is the first balance found for this element
424 create the element.
425 */
426 if j = 1 then
427 /*
428 create an element type and name it as follows:
429 initial_value_element concatenated with the
430 batch id, jurisdiction level, and a number
431 identifying which element type it is that's being
432 created.
433 */
434 l_element_name := 'Initial_Value_Element_' ||
435 p_batch_id ||
436 '_' ||
437 l_jur_level||
438 '_' ||
439 to_char(i);
440 --
441 hr_utility.trace (
442 'Element Name is:' || l_element_name);
443 --
444 l_element_type_id := pay_db_pay_setup.create_element (
445 p_element_name => l_element_name,
446 p_effective_start_date =>
447 to_date('01/01/0001', 'DD/MM/YYYY'),
448 p_effective_end_date =>
449 to_date('31/12/4712','DD/MM/YYYY'),
450 p_classification_name =>
451 'Balance Initialization',
452 p_input_currency_code => l_currency_code,
453 p_output_currency_code => l_currency_code,
454 p_processing_type => 'N',
455 p_adjustment_only_flag => 'Y',
456 p_process_in_run_flag => 'Y',
457 p_legislation_code => NULL,
458 p_business_group_name => l_bg_name,
459 p_processing_priority => 0,
460 p_post_termination_rule => 'Final Close');
461 --
462 hr_utility.trace (
463 'Element name after is:' || l_element_name);
464 --
465 update pay_element_types_f ELEM
466 set ELEM.element_information1 = 'B'
467 where element_type_id = l_element_type_id;
468 /*
469 create an element link for each element type created.
470 point it to each of the element type created.
471 */
472 l_elem_link_id :=
473 pay_db_pay_setup.create_element_link (
474 p_element_name => l_element_name,
475 p_link_to_all_pyrlls_fl => 'Y',
476 p_standard_link_flag => 'N',
477 p_effective_start_date =>
478 to_date('01-01-0001','DD-MM-YYYY'),
479 p_effective_end_date =>
480 to_date('31-12-4712','DD-MM-YYYY'),
481 p_business_group_name => l_bg_name);
482 /*
483 create a 'Jurisdiction' input value for each
484 element type.
485 */
486 l_input_val_id :=
487 pay_db_pay_setup.create_input_value (
488 p_element_name => l_element_name,
489 p_name => 'Jurisdiction',
490 p_uom_code => 'C',
491 p_business_group_name => l_bg_name,
492 p_display_sequence => 1,
493 p_effective_start_date =>
494 to_date('01-01-0001','DD-MM-YYYY'),
495 p_effective_end_date =>
496 to_date('31-12-4712','DD-MM-YYYY'));
497 --
498 hr_input_values.create_link_input_value(
499 p_insert_type => 'INSERT_INPUT_VALUE',
500 p_element_link_id => l_elem_link_id,
501 p_input_value_id => l_input_val_id,
502 p_input_value_name => 'Jurisdiction',
503 p_costable_type => NULL,
504 p_validation_start_date =>
505 to_date('01-01-0001','DD-MM-YYYY'),
506 p_validation_end_date =>
507 to_date('31-12-4712','DD-MM-YYYY'),
508 p_default_value => NULL,
509 p_max_value => NULL,
510 p_min_value => NULL,
511 p_warning_or_error_flag => NULL,
512 p_hot_default_flag => NULL,
513 p_legislation_code => NULL,
514 p_pay_value_name => NULL,
515 p_element_type_id => l_element_type_id);
516 --
517 end if;
518 /*
519 create an input value for each balance_name selected and
520 name it after the balance it is created for.
521 */
522 --
523 l_input_val_id := pay_db_pay_setup.create_input_value (
524 p_element_name => l_element_name,
525 p_name =>
526 substr(l_bal_name, 1, 28)||j,
527 p_uom_code =>
528 g_baltyp_tbl_uom(l_bal_count),
529 p_business_group_name => l_bg_name,
530 p_effective_start_date =>
531 to_date('01-01-0001','DD-MM-YYYY'),
532 p_effective_end_date =>
533 to_date('31-12-4712','DD-MM-YYYY'),
534 p_display_sequence => j+1);
535 /*
536 create a balance feed for each input value created.
537 point each to its corresponding input value.
538 */
539 hr_balances.ins_balance_feed(
540 p_option => 'INS_MANUAL_FEED',
541 p_input_value_id => l_input_val_id,
542 p_element_type_id => l_element_type_id,
543 p_primary_classification_id => NULL,
544 p_sub_classification_id => NULL,
545 p_sub_classification_rule_id => NULL,
546 p_balance_type_id =>
547 g_baltyp_tbl_id(l_bal_count),
548 p_scale => '1',
549 p_session_date =>
550 to_date('01-01-0001','DD-MM-YYYY'),
551 p_business_group => l_bg_id,
552 p_legislation_code => NULL,
553 p_mode => 'USER');
554 /*
555 create a link input value for each input value created.
556 */
557 hr_input_values.create_link_input_value(
558 p_insert_type => 'INSERT_INPUT_VALUE',
559 p_element_link_id => l_elem_link_id,
560 p_input_value_id => l_input_val_id,
561 p_input_value_name =>
562 substr(l_bal_name, 1 , 28)||j,
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 j := j + 1;
578 end if;
579 close csr_is_balance_fed;
580 end if;
581 l_bal_count := l_bal_count + 1;
582 end loop;
583 --
584 end loop;
585 l_jur_count := l_jur_count + 1;
586 end loop;
587 --
588 commit;
589 --
590 END create_bal_upl_struct;
591 --
592 BEGIN
593 g_nxt_free_baltyp := 1;
594 g_nxt_free_baldim := 1;
595 g_nxt_free_jl := 1;
596 END paybalup;
[Home] [Help]