[Home] [Help]
PACKAGE BODY: APPS.PER_PERFRALC_PKG
Source
1 PACKAGE BODY PER_PERFRALC_PKG AS
2 /*$Header: perfralc.pkb 115.9 2002/11/25 15:33:29 sfmorris ship $*/
3 --
4 procedure run_process (errbuf out nocopy varchar2,
5 retcode out nocopy number,
6 P_BUSINESS_GROUP_ID in number,
7 P_APPLICATION_ID in number,
8 P_LOOKUP_TYPE in varchar2,
9 P_ROW_TITLE in varchar2,
10 P_FUNCTION_TYPE in varchar2,
11 P_REQUIRED_DEFAULTS in varchar2,
12 P_DEFAULT_VALUE in varchar2,
13 P_LEGISLATION_CODE in varchar2) IS
14 --
15 l_legislation_code per_business_groups.legislation_code%type;
16 --
17 cursor csr_table_name is
18 select a.user_table_id
19 from pay_user_tables_v a
20 where upper (a.user_table_name) = upper (P_LOOKUP_TYPE)
21 and a.business_group_id = P_BUSINESS_GROUP_ID;
22
23 cursor csr_column_name (p_user_table_id number) is
24 select c.user_column_id
25 from pay_user_columns_v c
26 where p_user_table_id = c.user_table_id
27 and upper (c.user_column_name) = upper (P_FUNCTION_TYPE)
28 and nvl(c.business_group_id,P_BUSINESS_GROUP_ID) = P_BUSINESS_GROUP_ID
29 and nvl(c.legislation_code,l_legislation_code) = l_legislation_code;
30
31 cursor csr_lookup_code(p_user_table_id number) is
32 select hl.lookup_code, hl.meaning, b.user_row_id, b.effective_start_date, b.effective_end_date
33 from hr_lookups hl, pay_user_rows_f b
34 where hl.lookup_type = P_LOOKUP_TYPE
35 and b.user_table_id (+) = p_user_table_id
36 and nvl(b.business_group_id(+),P_BUSINESS_GROUP_ID) = P_BUSINESS_GROUP_ID
37 and nvl(b.legislation_code(+),l_legislation_code) = l_legislation_code
38 and b.row_low_range_or_name(+) = hl.lookup_code;
39
40 cursor csr_instance (p_user_row_id number, p_user_column_id number) is
41 select a.user_column_instance_id
42 from pay_user_column_instances_f a
43 where a.user_row_id = p_user_row_id
44 and a.user_column_id = p_user_column_id;
45
46 cursor csr_max_sequence (p_user_table_id number) is
47 select max(a.display_sequence)
48 from pay_user_rows_f a
49 where user_table_id = p_user_table_id
50 and nvl(a.business_group_id,P_BUSINESS_GROUP_ID) = P_BUSINESS_GROUP_ID
51 and nvl(a.legislation_code,l_legislation_code) = l_legislation_code;
52 --
53 l_order_code_new number;
54 --
55 l_table_rowid varchar2(100);
56 --
57 l_column_rowid varchar2(100);
58 l_column_instance_rowid varchar2(100);
59 --
60 l_user_row_id number;
61 l_user_table_id number;
62 l_user_column_id number;
63 l_user_column_instance_id number;
64 --
65 l_procedure varchar2(100);
66 --
67 l_lookup_code hr_lookups.lookup_code%type;
68 l_lookup_meaning hr_lookups.meaning%type;
69 --
70 l_effective_start_date date;
71 l_effective_end_date date;
72 --
73 begin
74 --
75 l_table_rowid := NULL;
76 --
77 l_column_rowid := NULL;
78 l_column_instance_rowid := NULL;
79 --
80 l_user_row_id := NULL;
81 l_user_table_id := NULL;
82 l_user_column_id := NULL;
83 l_user_column_instance_id := NULL;
84 --
85 l_procedure := 'Procedure run_process in the package per_perfralc_pkg';
86 --
87 hr_utility.set_location(l_procedure , 5);
88 --
89 if P_BUSINESS_GROUP_ID is not null then
90 select legislation_code into l_legislation_code
91 from per_business_groups
92 where business_group_id = P_BUSINESS_GROUP_ID;
93 --
94 else
95 --
96 l_legislation_code := P_LEGISLATION_CODE;
97 --
98 end if;
99 --
100 -- Creating The Tables
101 --
102 open csr_table_name;
103 fetch csr_table_name into l_user_table_id;
104 --
105 if csr_table_name%notfound then
106 pay_user_tables_pkg.insert_row (
107 P_ROWID => l_table_rowid
108 ,P_USER_TABLE_ID => l_user_table_id
109 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
110 ,P_LEGISLATION_CODE => l_legislation_code
111 ,P_LEGISLATION_SUBGROUP => NULL
112 ,P_RANGE_OR_MATCH => 'M'
113 ,P_USER_KEY_UNITS => 'T'
114 ,P_USER_TABLE_NAME => P_LOOKUP_TYPE
115 ,P_USER_ROW_TITLE => P_ROW_TITLE);
116 --
117 hr_utility.trace('Successefully created table '|| P_LOOKUP_TYPE);
118 --
119 end if;
120 --
121 close csr_table_name;
122 --
123 -- Creating The Columns
124 --
125 hr_utility.set_location(l_procedure , 10);
126 --
127 open csr_column_name(l_user_table_id);
128 fetch csr_column_name into l_user_column_id;
129 if csr_column_name%notfound then
130 pay_user_columns_pkg.insert_row (
131 P_ROWID => l_column_rowid
132 ,P_USER_COLUMN_ID => l_user_column_id
133 ,P_USER_TABLE_ID => l_user_table_id
134 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
135 ,P_LEGISLATION_CODE => l_legislation_code
136 ,P_LEGISLATION_SUBGROUP => NULL
137 ,P_USER_COLUMN_NAME => P_FUNCTION_TYPE
138 ,P_FORMULA_ID => NULL);
139 --
140 hr_utility.trace('Successefully created column '|| P_FUNCTION_TYPE);
141 --
142 end if;
143 --
144 close csr_column_name;
145 --
146 -- Creating The Rows
147 --
148 hr_utility.set_location(l_procedure , 15);
149 --
150 open csr_lookup_code(l_user_table_id);
151 loop
152 --
153 open csr_max_sequence(l_user_table_id);
154 fetch csr_max_sequence into l_order_code_new;
155 close csr_max_sequence;
156 --
157 fetch csr_lookup_code into l_lookup_code,l_lookup_meaning,l_user_row_id,
158 l_effective_start_date, l_effective_end_date;
159 exit when csr_lookup_code%notfound;
160 --
161 if l_effective_start_date is null then
162 l_effective_start_date := to_date('01/01/1900', 'DD/MM/YYYY');
163 end if;
164 --
165 if l_effective_end_date is null then
166 l_effective_end_date := to_date('31/12/4712', 'DD/MM/YYYY');
167 end if;
168 --
169 if l_effective_start_date <> to_date('01/01/1900','DD/MM/YYYY')
170 or l_effective_end_date <> to_date('31/12/4712', 'DD/MM/YYYY') then
171 null;
172 --
173 else
174 if l_user_row_id is null then
175 select pay_user_rows_s.nextval into l_user_row_id from dual;
176 --
177 if l_order_code_new is null then
178 l_order_code_new := 10;
179 else
180 l_order_code_new := l_order_code_new + 10;
181 end if;
182 --
183 insert into PAY_USER_ROWS_F
184 (USER_ROW_ID,
185 EFFECTIVE_START_DATE,
186 EFFECTIVE_END_DATE,
187 BUSINESS_GROUP_ID,
188 LEGISLATION_CODE,
189 USER_TABLE_ID,
190 ROW_LOW_RANGE_OR_NAME,
191 DISPLAY_SEQUENCE,
192 LEGISLATION_SUBGROUP,
193 ROW_HIGH_RANGE)
194 values (l_user_row_id,
195 to_date('01/01/1900', 'DD/MM/YYYY'),
196 to_date('31/12/4712', 'DD/MM/YYYY'),
197 P_BUSINESS_GROUP_ID,
198 l_legislation_code,
199 l_user_table_id,
200 l_lookup_code,
201 (l_order_code_new),
202 NULL,
203 NULL);
204 --
205 hr_utility.trace('Successefully created row '|| l_lookup_code);
206 --
207 end if;
208 --
209 -- Creating The Column Values
210 --
211 hr_utility.set_location(l_procedure , 20);
212 --
213 l_user_column_instance_id := null;
214 open csr_instance(l_user_row_id,l_user_column_id);
215 fetch csr_instance into l_user_column_instance_id;
216 close csr_instance;
217 --
218 if (l_user_column_instance_id is null) then
219 --
220 if P_REQUIRED_DEFAULTS = 'NONE' then
221 pay_user_column_instances_pkg.insert_row(
222 P_ROWID => l_column_instance_rowid
223 ,P_USER_COLUMN_INSTANCE_ID => l_user_column_instance_id
224 ,P_EFFECTIVE_START_DATE => to_date('01/01/1900', 'DD/MM/YYYY')
225 ,P_EFFECTIVE_END_DATE => to_date('31/12/4712', 'DD/MM/YYYY')
226 ,P_USER_ROW_ID => l_user_row_id
227 ,P_USER_COLUMN_ID => l_user_column_id
228 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
229 ,P_LEGISLATION_CODE => l_legislation_code
230 ,P_LEGISLATION_SUBGROUP => NULL
231 ,P_VALUE => NULL);
232 --
233 elsif P_REQUIRED_DEFAULTS = 'QUICKCODE_VALUE' then
234 pay_user_column_instances_pkg.insert_row(
235 P_ROWID => l_column_instance_rowid
236 ,P_USER_COLUMN_INSTANCE_ID => l_user_column_instance_id
237 ,P_EFFECTIVE_START_DATE => to_date('01/01/1900', 'DD/MM/YYYY')
238 ,P_EFFECTIVE_END_DATE => to_date('31/12/4712', 'DD/MM/YYYY')
239 ,P_USER_ROW_ID => l_user_row_id
240 ,P_USER_COLUMN_ID => l_user_column_id
241 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
242 ,P_LEGISLATION_CODE => l_legislation_code
243 ,P_LEGISLATION_SUBGROUP => NULL
244 ,P_VALUE => l_lookup_meaning);
245 --
246 else
247 pay_user_column_instances_pkg.insert_row(
248 P_ROWID => l_column_instance_rowid
249 ,P_USER_COLUMN_INSTANCE_ID => l_user_column_instance_id
250 ,P_EFFECTIVE_START_DATE => to_date('01/01/1900', 'DD/MM/YYYY')
251 ,P_EFFECTIVE_END_DATE => to_date('31/12/4712', 'DD/MM/YYYY')
252 ,P_USER_ROW_ID => l_user_row_id
253 ,P_USER_COLUMN_ID => l_user_column_id
254 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
255 ,P_LEGISLATION_CODE => l_legislation_code
256 ,P_LEGISLATION_SUBGROUP => NULL
257 ,P_VALUE => P_DEFAULT_VALUE);
258 end if;
259 --
260 end if;
261 --
262 end if;
263 --
264 end loop;
265 --
266 close csr_lookup_code;
267 --
268 errbuf := null;
269 retcode := 0;
270 --
271 exception
272 when others then
273 hr_utility.trace ('Error in the '||l_procedure||' - ORA '||to_char(SQLCODE));
274 errbuf := sqlerrm;
275 retcode := 2;
276 rollback;
277 --
278 end run_process;
279 --
280 ---------------------------------------------------------------------------
281 -- |---------------- Clean_pay_tables -------------------------------| -
282 ---------------------------------------------------------------------------
283 procedure clean_pay_tables
284 (cn_business_group_id IN per_business_groups.business_group_id%TYPE,
285 cp_user_table_name IN pay_user_tables.user_table_name%TYPE) IS
286
287
288 ln_row_count number := 1;
289 ln_user_table_id number;
290 ln_user_column_id number;
291 ln_user_row_id number;
292 ln_pay_user_rows number;
293
294
295 --
296 -- Local Cursor Definition
297 --
298
299 CURSOR Get_pay_user_tables_id (cp_user_table_name CHAR,
300 cn_business_group_id NUMBER) IS
301 SELECT USER_TABLE_ID
302 FROM PAY_USER_TABLES
303 WHERE USER_TABLE_NAME = cp_user_table_name and
304 BUSINESS_GROUP_ID = cn_business_group_id;
305
306
307 CURSOR Get_pay_user_columns (cp_user_table_id NUMBER,
308 cn_business_group_id NUMBER) IS
309 SELECT USER_COLUMN_ID
310 FROM PAY_USER_COLUMNS
311 WHERE USER_TABLE_ID = cp_user_table_id and
312 BUSINESS_GROUP_ID = cn_business_group_id;
313
314
315 CURSOR Get_pay_user_rows_f (cp_user_table_id NUMBER,
316 cn_business_group_id NUMBER) IS
317 SELECT USER_ROW_ID
318 FROM PAY_USER_ROWS_F
319 WHERE USER_TABLE_ID = cp_user_table_id and
320 BUSINESS_GROUP_ID = cn_business_group_id;
321
322 begin
323 hr_utility.set_location('perfralc - Clean_pay_tables ', 1);
324 -- Clean all the PAY_USER tables for one specific (lookup) table
325 --
326 --
327 -- Get first the user_table_id associated to the given user_table_name
328 OPEN Get_pay_user_tables_id (cp_user_table_name,cn_business_group_id);
329 FETCH Get_pay_user_tables_id into ln_user_table_id;
330 If (Get_pay_user_tables_id%FOUND) then
331 Begin
332 hr_utility.set_location('perfralc - Clean_pay_tables', 5);
333 hr_utility.set_location('user_table_id found for ' || cp_user_table_name, 5);
334 -- Get all the PAY_USER_COLUMNS associated to the user_table_name
335 -- For each column, get the Ids of the column instances to delete them
336 OPEN Get_pay_user_columns (ln_user_table_id,cn_business_group_id);
337 Loop
338 FETCH Get_pay_user_columns into ln_user_column_id;
339 EXIT When Get_pay_user_columns%NOTFOUND;
340 hr_utility.set_location('user_column_id found for ' || to_char(ln_user_column_id), 10);
341 -- Get all the PAY_USER_ROWS_F Columns
342 OPEN Get_pay_user_rows_f (ln_user_table_id,cn_business_group_id);
343 Loop
344 FETCH Get_pay_user_rows_f into ln_pay_user_rows;
345 EXIT When Get_pay_user_rows_f%NOTFOUND;
346
347 hr_utility.set_location('user_rows_f found. to be deleted ' || to_char(ln_pay_user_rows), 15);
348 -- for each pay_user_row found, delete the associated
349 -- PAY_USER_COLUMN_INSTANCES_F (related value for added column)
350 DELETE FROM PAY_USER_COLUMN_INSTANCES_F
351 WHERE USER_ROW_ID = ln_pay_user_rows and
352 USER_COLUMN_ID = ln_user_column_id and
353 BUSINESS_GROUP_ID = cn_business_group_id;
354
355 End Loop;
356 CLOSE Get_pay_user_rows_f;
357
358 End Loop;
359 CLOSE Get_pay_user_columns;
360
361 hr_utility.set_location('perfralc - delete pay_user_row, column and table rows' , 20);
362 -- Delete all the PAY_USER_ROW, COLUMNS and TABLE rows
363 DELETE FROM PAY_USER_ROWS_F
364 WHERE USER_TABLE_ID = ln_user_table_id and
365 BUSINESS_GROUP_ID = cn_business_group_id;
366
367 DELETE FROM PAY_USER_COLUMNS
368 WHERE USER_TABLE_ID = ln_user_table_id and
369 BUSINESS_GROUP_ID = cn_business_group_id;
370
371 DELETE FROM PAY_USER_TABLES
372 WHERE USER_TABLE_ID = ln_user_table_id and
373 BUSINESS_GROUP_ID = cn_business_group_id;
374 end;
375 end if;
376 CLOSE Get_pay_user_tables_id;
377 hr_utility.set_location('perfralc - End Clean_pay_tables', 50);
378 end;
379
380
381
382
383 ------------------------------------------------------------------
384 -- ----- set_instance_value ------------------------------------|
385 ------------------------------------------------------------------
386 Procedure Set_instance_value (cn_business_group_id IN per_business_groups.business_group_id%TYPE,
387 cp_legislation_code IN per_business_groups.legislation_code%TYPE,
388 cp_user_table_name IN pay_user_tables.user_table_name%TYPE ,
389 cp_user_column_name IN pay_user_columns.user_column_name%TYPE ,
390 cp_row_name IN pay_user_rows_f.row_low_range_or_name%TYPE ,
391 cp_value IN pay_user_column_instances_f.value%TYPE
392 ) IS
393
394
395 ln_count_column_instance_id NUMBER;
396 ln_count_row_id NUMBER;
397 ln_column_instance_id NUMBER;
398 ln_user_table_id NUMBER;
399 ln_user_column_id NUMBER;
400 ln_user_row_id NUMBER;
401
402 --
403 -- Local Cursor Definition
404
405 CURSOR Get_pay_user_tables_id (cp_user_table_name CHAR,
406 cn_business_group_id NUMBER) IS
407 SELECT USER_TABLE_ID
408 FROM PAY_USER_TABLES
409 WHERE USER_TABLE_NAME = cp_user_table_name
410 and BUSINESS_GROUP_ID = cn_business_group_id;
411
412
413 CURSOR Get_column_id (cp_user_table_id NUMBER,
414 cp_user_column_name pay_user_columns.user_column_name%TYPE,
415 cn_business_group_id NUMBER) IS
416 SELECT USER_COLUMN_ID
417 FROM PAY_USER_COLUMNS
418 WHERE USER_TABLE_ID = cp_user_table_id and
419 USER_COLUMN_NAME = cp_user_column_name and
420 BUSINESS_GROUP_ID = cn_business_group_id;
421
422
423
424 CURSOR Get_row_id (cp_user_table_id NUMBER,
425 cp_row_name IN pay_user_rows_f.row_low_range_or_name%TYPE,
426 cn_business_group_id NUMBER) IS
427 SELECT USER_ROW_ID
428 FROM PAY_USER_ROWS_F
429 WHERE USER_TABLE_ID = cp_user_table_id and
430 ROW_LOW_RANGE_OR_NAME = cp_row_name and
431 BUSINESS_GROUP_ID = cn_business_group_id;
432
433
434 CURSOR Get_Column_instance_id (cn_user_row_id NUMBER,
435 cn_user_column_id NUMBER,
436 cn_business_group_id NUMBER) IS
437 SELECT USER_COLUMN_INSTANCE_ID
438 FROM PAY_USER_COLUMN_INSTANCES_F
439 WHERE USER_ROW_ID = cn_user_row_id and
440 USER_COLUMN_ID = cn_user_column_id and
441 BUSINESS_GROUP_ID = cn_business_group_id;
442
443
444
445 Begin
446 -- First, Get the table_id
447 --
448 hr_utility.set_location('perfralc - set_instance_value - Entering',1);
449 OPEN Get_pay_user_tables_id (cp_user_table_name,cn_business_group_id);
450 FETCH Get_pay_user_tables_id into ln_user_table_id;
451 if (Get_pay_user_tables_id%NOTFOUND) Then
452 hr_utility.set_location('perfralc - set_instance_value - unknown table_name : ' || cp_user_table_name, 5);
453 Else
454 hr_utility.set_location(' --> table_id = ' || to_char(ln_user_table_id),2);
455 -- Get the column id
456 --
457 OPEN Get_column_id (ln_user_table_id,cp_user_column_name,cn_business_group_id);
458 FETCH Get_column_id into ln_user_column_id;
459 if (Get_column_id%NOTFOUND) Then
460 hr_utility.set_location('perfralc - set_instance_value - unknown column_name : ' || cp_user_column_name, 10);
461 Else
462 hr_utility.set_location(' --> column_id = ' || to_char(ln_user_column_id),2);
463 -- Get the row id, and check if only one instance is defined
464 --
465 ln_count_row_id := 0;
466 OPEN Get_row_id (ln_user_table_id,cp_row_name,cn_business_group_id);
467 Loop
468 FETCH Get_row_id into ln_user_row_id;
469 EXIT When Get_row_id%NOTFOUND;
470 ln_count_row_id := ln_count_row_id + 1;
471 End Loop;
472
473 if (ln_count_row_id = 0) Then
474 hr_utility.set_location('perfralc - set_instance_value - unknown row_name : ' || cp_row_name, 15);
475 Else if (ln_count_row_id > 1) Then
476 hr_utility.set_location('more than one effective row for ' || cp_row_name,16);
477 hr_utility.set_location('nothing to do ...',17);
478 else
479 Begin
480 hr_utility.set_location(' --> Row_id = ' || to_char(ln_user_row_id),18);
481 -- Get the column_instance_id to update the value
482 OPEN Get_column_instance_id (ln_user_row_id,ln_user_column_id,cn_business_group_id);
483
484 -- Check if we have more than one column (different effective dates) for this instance
485 ln_count_column_instance_id := 0;
486 Loop
487 FETCH Get_Column_instance_id into ln_column_instance_id;
488 EXIT When Get_Column_instance_id%NOTFOUND;
489 ln_count_column_instance_id := ln_count_column_instance_id + 1;
490 End Loop;
491
492 if (ln_count_column_instance_id > 1) then
493 hr_utility.set_location('no change to do. More than one instance : ' || cp_row_name, 15);
494 Else
495 Begin
496 hr_utility.set_location(' Update PAY_USER_COLUMN_INSTANCES_F for ' || to_char(ln_column_instance_id),16);
497 UPDATE PAY_USER_COLUMN_INSTANCES_F
498 set value = cp_value
499 where user_column_instance_id = ln_column_instance_id;
500 EXCEPTION
501 WHEN OTHERS Then
502 hr_utility.set_location ('perfralc - set_instance_value - unable to update row into PAY_USER_COLUMN_INSTANCES_F '|| to_char(ln_column_instance_id), 20);
503 End;
504 End if;
505 End;
506 end if;
507 End if;
508 CLOSE Get_row_id;
509 End if;
510 CLOSE Get_column_id;
511 End if;
512 CLOSE Get_pay_user_tables_id;
513
514 hr_utility.set_location('perfralc - Set_instance_value - Leaving',30);
515
516 End;
517
518 ------------------------------------------------------------------------
519 -- |--------- Update_Instance_Value ----------------------------------|
520 ------------------------------------------------------------------------
521 -- Procedure Update_instance_value
522 -- parameters are : Business_group (optional)
523 -- Legislation_Code (optional)
524 -- (Lookup) table_name
525 -- Alternative Lookup column_name
526 -- Instance_name
527 -- value to be updated for the instance
528 --
529 -- Only process the Business_group if provided
530 -- or Process all the business_groups for a given legislation_code
531
532 Procedure Update_Instance_value
533 (cn_business_group_id IN per_business_groups.business_group_id%TYPE,
534 cp_legislation_code IN per_business_groups.legislation_code%TYPE,
535 cp_user_table_name IN pay_user_tables.user_table_name%TYPE ,
536 cp_user_column_name IN pay_user_columns.user_column_name%TYPE ,
537 cp_row_name IN pay_user_rows_f.row_low_range_or_name%TYPE ,
538 cp_value IN pay_user_column_instances_f.value%TYPE
539 ) IS
540
541 ln_user_table_id NUMBER;
542 ln_user_column_id NUMBER;
543 ln_user_row_id NUMBER;
544 ln_temp_leg_code VARCHAR2(150);
545 ln_temp_business_group_id NUMBER;
546
547 --
548 -- Local Cursor Definition
549
550 CURSOR Get_Business_group_id (cp_legislation_code per_business_groups.legislation_code%TYPE) IS
551 SELECT BUSINESS_GROUP_ID
552 FROM PER_BUSINESS_GROUPS
553 WHERE LEGISLATION_CODE = cp_legislation_code;
554
555
556
557 Begin
558 hr_utility.set_location(' ',1);
559 hr_utility.set_location('per_perfralc_pkg - Entering update_instance_value', 1);
560 hr_utility.set_location('--------------------------------------------------',1);
561
562 -- First case - only process the business_group
563 if (cn_business_group_id is NOT NULL) Then
564 -- check the existance of this business_group,and get the associated legislation_code
565 ln_temp_leg_code := '';
566
567 Begin
568 select rtrim(ltrim(pbg.legislation_code)) into ln_temp_leg_code
569 from per_business_groups pbg
570 where pbg.business_group_id = cn_business_group_id;
571 end;
572
573 if (ln_temp_leg_code = '') then
574 hr_utility.set_location('unable to update the instance - unknown business_group ' || to_char(cn_business_group_id),10);
575 Else
576 Begin
577 if (ln_temp_leg_code <> '') and (cp_legislation_code <> '') and (ln_temp_leg_code <> cp_legislation_code) Then
578 hr_utility.set_location('unable to update the instance - incompatible business group and legislation code ',20);
579 else
580 -- call the update procedure only for the business_group
581 hr_utility.set_location(' Processed the business_group ' || to_char(cn_business_group_id),21);
582 set_instance_value(cn_business_group_id,
583 ln_temp_leg_code,
584 cp_user_table_name,
585 cp_user_column_name,
586 cp_row_name,
587 cp_value);
588 end if;
589 end;
590 end if;
591 else
592 -- Second case : process all the business_groups associated to the legislation_code
593 OPEN Get_Business_group_id (cp_legislation_code);
594 Loop
595 FETCH Get_Business_Group_id into ln_temp_business_group_id;
596 EXIT when Get_Business_Group_id%NOTFOUND;
597
598 -- call the update procedure for this business_group
599 set_instance_value(ln_temp_business_group_id,
600 ln_temp_leg_code,
601 cp_user_table_name,
602 cp_user_column_name,
603 cp_row_name,
604 cp_value);
605 End Loop;
606 end if;
607
608 hr_utility.set_location('perfralc - Updte_Instance_value - Leaving ..',30);
609 End;
610 --
611 end PER_PERFRALC_PKG;