[Home] [Help]
PACKAGE BODY: APPS.PYSGBUPL
Source
1 PACKAGE BODY pysgbupl AS
2 -- /* $Header: pysgbupl.pkb 115.7 2004/01/22 02:40:28 abhargav ship $ */
3 --
4 -- +======================================================================+
5 -- | Copyright (c) 1997 Oracle Corporation UK Ltd |
6 -- | Reading, Berkshire, England |
7 -- | All rights reserved. |
8 -- +======================================================================+
9 -- SQL Script File Name : pysgbupl.pkb
10 -- Description : This script delivers Initial Balance Structure Creation
11 -- package for the Singapore localization (SG).
12 -- This package can be activated from the SG Initial Balance
13 -- Structure Creation SRS available through Forms. The user
14 -- needs to supply the batch name to run this process.
15 --
16 -- Given the limit of the input values per element type and the
17 -- batch id in that order, create_bal_upl_struct will first call
18 -- validate_batch_data to validate the batch data, then it will
19 -- create the element types, element links, input values, balance
20 -- feeds and link input values.
21 --
22 -- The SQL script PYDELSTR.sql when submitted by SRS
23 -- will delete the structure created by this package.
24 --
25 --
26 -- Change List:
27 -- ------------
28 --
29 -- ======================================================================
30 -- Version Date Author Bug No. Description of Change
31 -- ------- ----------- -------- ------- -----------------------------
32 -- 115.0 06-JUN-2000 JBailie Initial Version
33 -- 115.1 21-JUL-2000 JBailie Set ship state
34 -- 115.2 27-JUL-2000 JBailie Removed hr_utility.trace_on
35 -- 115.3 29-NOV-2001 Ragovind 2129823 GSCC Compliance Check
36 -- 115.4 10-DEC-2001 Rsirigir 2107303 added this line
37 -- REM checkfile:~PROD:~PATH:~FILE
38 -- after
39 -- REM dbdrv: sql ~PROD ~PATH ~FILE
40 -- none none none package &phase=plb
41 -- as part of GSCC Compliance Check
42 -- 115.5 10-DEC-2002 Apunekar 2689242 Added nocopy to out and in out parameters
43 -- 115.6 12-JAN-2004 abhargav 3371693 Modified the cursor 'csr_is_balance_fed'
44 -- for the performance reason.
45 -- ======================================================================
46 --
47 --
48 --
49 -- Global declarations
50 type char_array is table of varchar2(80) index by binary_integer;
51 type num_array is table of number(16) index by binary_integer;
52 --
53 -- Balance Type Cache
54 g_baltyp_tbl_id num_array;
55 g_baltyp_tbl_name char_array;
56 g_baltyp_tbl_uom char_array;
57 g_nxt_free_baltyp number;
58 --
59 -- Balance Dimension Cache
60 g_baldim_tbl_id num_array;
61 g_baldim_tbl_name char_array;
62 g_nxt_free_baldim number;
63 --
64 --
65 PROCEDURE local_error(retcode OUT NOCOPY number,
66 p_procedure IN varchar2,
67 p_step IN number) IS
68 --
69 /* This procedure is called whenever an error needs to be raised and
70 the retcode is set to 2 to indicate an error has occurred.
71 */
72 --
73 BEGIN
74 --
75 retcode := 2;
76 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
77 hr_utility.set_message_token('PROCEDURE', 'pysgbupl.'||p_procedure);
78 hr_utility.set_message_token('STEP', p_step);
79 hr_utility.raise_error;
80 --
81 END local_error;
82 --
83 --
84 procedure check_balance_type(p_baltype_id out nocopy number,
85 p_baltype_name varchar2,
86 p_busgrp_id number,
87 p_leg_code varchar2,
88 retcode out nocopy number)
89 is
90 l_balance_type_id number;
91 l_baltyp_name varchar2(80);
92 l_bal_uom varchar2(80);
93 l_count number;
94 l_found boolean;
95 begin
96 --
97 -- Search for the defined balance in the Cache.
98 --
99 hr_utility.set_location('pysgbupl.check_balance_type', 10);
100 l_balance_type_id := null;
101 l_baltyp_name := upper(p_baltype_name);
102 l_count := 1;
103 l_found := FALSE;
104 while (l_count < g_nxt_free_baltyp and l_found = FALSE) loop
105 if (l_baltyp_name = g_baltyp_tbl_name(l_count)) then
106 hr_utility.set_location('pysgbupl.check_balance_type', 20);
107 l_balance_type_id := g_baltyp_tbl_id(l_count);
108 l_found := TRUE;
109 end if;
110 l_count := l_count + 1;
111 end loop;
112 --
113 -- If the balance is not in the Cache get it from the database.
114 --
115 hr_utility.set_location('pysgbupl.check_balance_type', 30);
116 if (l_found = FALSE) then
117 BEGIN
118 --
119 select balance_type_id,
120 balance_uom
121 into l_balance_type_id, l_bal_uom
122 from pay_balance_types
123 where upper(balance_name) = l_baltyp_name
124 and ((business_group_id = p_busgrp_id)
125 or( business_group_id is null
126 and legislation_code = p_leg_code)
127 or( business_group_id is null
128 and legislation_code is null)
129 )
130 for update of balance_type_id;
131 --
132 --
133 -- Place the defined balance in cache.
134 --
135 hr_utility.set_location('pysgbupl.check_balance_type', 40);
136 g_baltyp_tbl_name(g_nxt_free_baltyp) := l_baltyp_name;
137 g_baltyp_tbl_uom(g_nxt_free_baltyp) := l_bal_uom;
138 g_baltyp_tbl_id(g_nxt_free_baltyp) := l_balance_type_id;
139 g_nxt_free_baltyp := g_nxt_free_baltyp + 1;
140 --
141 EXCEPTION WHEN no_data_found THEN
142 hr_utility.trace('Error: Failure to find balance type');
143 local_error(retcode, 'check_balance_type',1);
144 --
145 END;
146 --
147 end if;
148 --
149 p_baltype_id := l_balance_type_id;
150 --
151 end check_balance_type;
152 --
153 --
154 procedure check_balance_dim(p_baldim_id out nocopy number,
155 p_baldim_name varchar2,
156 p_busgrp_id number,
157 p_leg_code varchar2,
158 retcode out nocopy number)
159 is
160 l_baldim_name varchar2(80);
161 l_count number;
162 l_found boolean;
163 l_balance_dim_id number;
164 begin
165 --
166 -- Search for the defined balance in the Cache.
167 --
168 hr_utility.set_location('pysgbupl.check_balance_dim', 10);
169 l_balance_dim_id := null;
170 l_baldim_name := upper(p_baldim_name);
171 l_count := 1;
172 l_found := FALSE;
173 while (l_count < g_nxt_free_baldim and l_found = FALSE) loop
174 if (l_baldim_name = g_baldim_tbl_name(l_count)) then
175 hr_utility.set_location('pysgbupl.check_balance_dim', 20);
176 l_balance_dim_id := g_baldim_tbl_id(l_count);
177 l_found := TRUE;
178 end if;
179 l_count := l_count + 1;
180 end loop;
181 --
182 -- If the balance is not in the Cache get it from the database.
183 --
184 hr_utility.set_location('pysgbupl.check_balance_dim', 30);
185 if (l_found = FALSE) then
186 BEGIN
187 --
188 select balance_dimension_id
189 into l_balance_dim_id
190 from pay_balance_dimensions
191 where upper(dimension_name) = l_baldim_name
192 and ((business_group_id = p_busgrp_id)
193 or( business_group_id is null
194 and legislation_code = p_leg_code)
195 or( business_group_id is null
196 and legislation_code is null)
197 );
198 --
199 --
200 -- Place the defined balance in cache.
201 --
202 hr_utility.set_location('pysgbupl.check_balance_dim', 40);
203 g_baldim_tbl_name(g_nxt_free_baldim) := l_baldim_name;
204 g_baldim_tbl_id(g_nxt_free_baldim) := l_balance_dim_id;
205 g_nxt_free_baldim := g_nxt_free_baldim + 1;
206 --
207 EXCEPTION WHEN no_data_found THEN
208 hr_utility.trace('Error: Failure to find balance dimension');
209 local_error(retcode,'check_balance_dim',2);
210 --
211 END;
212 --
213 end if;
214 --
215 p_baldim_id := l_balance_dim_id;
216 --
217 end check_balance_dim;
218 --
219 FUNCTION validate_batch_data (p_batch_id number) RETURN number IS
220 --
221 /* This function verifies that the business group, balance types, and
222 balance dimensions actually exist. If not, it would return a retcode
223 of 2 and raise an exception.
224 */
225 --
226 retcode number := 0;
227 i number := 0;
228 l_bg_id per_business_groups.business_group_id%TYPE;
229 l_leg_code per_business_groups.legislation_code%TYPE;
230 l_bt_id pay_balance_types.balance_type_id%TYPE;
231 l_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
232 --
233 cursor c_each_batch (c_batch_id number) is
234 select balance_name,
235 dimension_name
236 from pay_balance_batch_lines
237 where batch_id = c_batch_id;
238 --
239 BEGIN
240 hr_utility.set_location('pysgbupl.validate_batch_data', 10);
241 BEGIN /* check business group exists */
242 select hou.business_group_id,
243 hou.legislation_code
244 into l_bg_id,
245 l_leg_code
246 from per_business_groups hou,
247 pay_balance_batch_headers bbh
248 where bbh.batch_id = p_batch_id
249 and upper(hou.name) = upper(bbh.business_group_name);
250 EXCEPTION WHEN no_data_found THEN
251 local_error(retcode, 'validate_batch_data', 3);
252 END;
253 --
254 hr_utility.set_location('pysgbupl.validate_batch_data', 20);
255 for l_each_batch_rec in c_each_batch (p_batch_id) loop
256 check_balance_type(l_bt_id, l_each_batch_rec.balance_name,
257 l_bg_id,
258 l_leg_code,
259 retcode);
260 check_balance_dim(l_bal_dim_id, l_each_batch_rec.dimension_name,
261 l_bg_id,
262 l_leg_code,
263 retcode);
264 end loop;
265 --
266 return retcode;
267 END validate_batch_data;
268 --
269 --
270 PROCEDURE create_bal_upl_struct (errbuf OUT NOCOPY varchar2,
271 retcode OUT NOCOPY number,
272 p_input_value_limit IN number,
273 p_batch_id IN number) IS
274 --
275 -- errbuf and retcode are special parameters needed for the SRS.
276 -- retcode = 0 means no error and retcode = 2 means an error occurred.
277 --
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_bal_count number;
292 l_dummy_id number;
293 l_bg_currency_code pay_element_types.output_currency_code%TYPE;
294 --
295 cursor csr_is_balance_fed (p_balance_type_id number,
296 p_business_group number)
297 is
298 select balance_feed_id
299 from pay_balance_feeds_f BF,
300 pay_input_values_f IV,
301 pay_element_types_f ET,
302 pay_element_classifications EC
303 where EC.classification_name = 'Balance Initialization'
304 and ET.classification_id = EC.classification_id
305 and IV.element_type_id = ET.element_type_id
306 and IV.input_value_id = BF.input_value_id
307 and BF.balance_type_id = p_balance_type_id
308 and nvl(BF.business_group_id, p_business_group) = p_business_group;
309 --
310 --
311 BEGIN
312 --
313 hr_utility.set_location('pysgbupl.create_bal_upl_struct', 10);
314 --
315 hr_utility.trace('Started Processing');
316 --
317 select pbg.business_group_id, bbh.business_group_name, pbg.currency_code
318 into l_bg_id, l_bg_name, l_bg_currency_code
319 from pay_balance_batch_headers bbh,
320 per_business_groups pbg
321 where batch_id = p_batch_id
322 and upper(pbg.name) = upper(bbh.business_group_name);
323 --
324 retcode := validate_batch_data (p_batch_id);
325 --
326 hr_utility.set_location('pysgbupl.create_bal_upl_struct', 20);
327 --
328 /* calculate no of elements needed based on 15 input values per element*/
329 l_n_elems := ceil ((g_nxt_free_baltyp - 1) / p_input_value_limit);
330
331 l_bal_count := 1;
332 for i in 1 .. l_n_elems loop
333 --
334 hr_utility.trace('i='||to_char(i));
335 --
336 hr_utility.set_location('pysgbupl.create_bal_upl_struct', 30);
337 j := 1;
338 while (l_bal_count< g_nxt_free_baltyp and j <= p_input_value_limit) loop
339
340 hr_utility.trace('j='||to_char(j));
341
342 hr_utility.set_location('pysgbupl.create_bal_upl_struct', 40);
343 --
344 -- Does this balance already have an initial balance feed.
345 --
346 open csr_is_balance_fed(g_baltyp_tbl_id(l_bal_count),l_bg_id);
347 fetch csr_is_balance_fed into l_dummy_id;
348
349 if (csr_is_balance_fed%notfound) then
350 /*
351 If this is the first balance found for this element
352 create the element.
353 */
354 hr_utility.trace('Processing: '||g_baltyp_tbl_name(l_bal_count));
355
356 if j = 1 then
357 /*
358 create an element type and name it as follows:
359 initial_value_element concatenated with the
360 batch id, and a number identifying which element
361 type it is that's being created.
362 */
363 l_element_name := 'Initial_Value_Element_' || -- keep this name
364 p_batch_id || -- as means to identify
365 '_' || -- elements to delete
366 to_char(i); -- by the Undo
367 --
368 hr_utility.trace ('Element Name is:' || l_element_name);
369 --
370 l_element_type_id := pay_db_pay_setup.create_element (
371 p_element_name => l_element_name,
372 p_effective_start_date => to_date('01/01/0001','DD/MM/YYYY'),
373 p_effective_end_date => to_date('31/12/4712','DD/MM/YYYY'),
374 p_classification_name => 'Balance Initialization',
375 p_input_currency_code => l_bg_currency_code,
379 p_process_in_run_flag => 'Y',
376 p_output_currency_code => l_bg_currency_code,
377 p_processing_type => 'N',
378 p_adjustment_only_flag => 'Y',
380 p_legislation_code => NULL,
381 p_business_group_name => l_bg_name,
382 p_processing_priority => 0,
383 p_post_termination_rule => 'Final Close');
384 --
385 update pay_element_types_f ELEM
386 set ELEM.element_information1 = 'B'
387 where element_type_id = l_element_type_id;
388 /*
389 create an element link for each element type created.
390 point it to each of the element type created.
391 */
392 l_elem_link_id := pay_db_pay_setup.create_element_link (
393 p_element_name => l_element_name,
394 p_link_to_all_pyrlls_fl => 'Y',
395 p_standard_link_flag => 'N',
396 p_effective_start_date => to_date('01-01-0001','DD-MM-YYYY'),
397 p_effective_end_date => to_date('31-12-4712','DD-MM-YYYY'),
398 p_business_group_name => l_bg_name);
399 --
400 end if;
401 /*
402 create an input value for each balance_name selected and
403 name it after the balance it is created for.
404 */
405 --
406 l_input_val_id := pay_db_pay_setup.create_input_value (
407 p_element_name => l_element_name,
408 p_name => substr(l_bal_name, 1, 28)||j,
409 p_uom_code => g_baltyp_tbl_uom(l_bal_count),
410 p_business_group_name => l_bg_name,
411 p_effective_start_date => to_date('01-01-0001','DD-MM-YYYY'),
412 p_effective_end_date => to_date('31-12-4712','DD-MM-YYYY'),
413 p_display_sequence => j+1);
414 /*
415 create a balance feed for each input value created.
416 point each to its corresponding input value.
417 */
418 hr_balances.ins_balance_feed(
419 p_option => 'INS_MANUAL_FEED',
420 p_input_value_id => l_input_val_id,
421 p_element_type_id => l_element_type_id,
422 p_primary_classification_id => NULL,
423 p_sub_classification_id => NULL,
424 p_sub_classification_rule_id => NULL,
425 p_balance_type_id => g_baltyp_tbl_id(l_bal_count),
426 p_scale => '1',
427 p_session_date => to_date('01-01-0001','DD-MM-YYYY'),
428 p_business_group => l_bg_name,
429 p_legislation_code => NULL,
430 p_mode => 'USER');
431 /*
432 create a link input value for each input value created.
433 */
434 hr_input_values.create_link_input_value(
435 p_insert_type => 'INSERT_INPUT_VALUE',
436 p_element_link_id => l_elem_link_id,
437 p_input_value_id => l_input_val_id,
438 p_input_value_name => substr(l_bal_name, 1 , 28)||j,
439 p_costable_type => NULL,
440 p_validation_start_date => to_date('01-01-0001','DD-MM-YYYY'),
441 p_validation_end_date => to_date('31-12-4712','DD-MM-YYYY'),
442 p_default_value => NULL,
443 p_max_value => NULL,
444 p_min_value => NULL,
445 p_warning_or_error_flag => NULL,
446 p_hot_default_flag => NULL,
447 p_legislation_code => NULL,
448 p_pay_value_name => NULL,
449 p_element_type_id => l_element_type_id);
450 --
451 j := j + 1;
452 end if;
453 close csr_is_balance_fed;
454 l_bal_count := l_bal_count + 1;
455 end loop;
456 --
457 end loop;
458 --
459 hr_utility.trace('Finished Processing');
460 commit;
461 --
462 END create_bal_upl_struct;
463 --
464 BEGIN
465 g_nxt_free_baltyp := 1;
466 g_nxt_free_baldim := 1;
467 END pysgbupl;