[Home] [Help]
PACKAGE BODY: APPS.PER_ZA_UTILITY_PKG
Source
1 PACKAGE BODY per_za_utility_pkg AS
2 /* $Header: pezautly.pkb 120.4 2005/08/17 10:47:27 nragavar noship $ */
3 /* Copyright (c) Oracle Corporation 2002. All rights reserved. */
4 /*
5 PRODUCT
6 Oracle Human Resources - ZA Utility Package
7 NAME
8 pezautly.pkb
9
10 DESCRIPTION
11 .
12
13 PUBLIC FUNCTIONS
14 per_za_table_meaning
15 Function returns value from the specific user table for the
16 specified business group. Function uses the passed Effective date
17 else it picks up from Fnd_Sessions Table for fetching from the
18 date tracked User table rows and User table column values.
19 References:
20 PER_ZA_LEARNERSHIP_AGREEMENT_V
21
22 PRIVATE FUNCTIONS
23 <none>
24 NOTES
25 .
26
27 MODIFICATION HISTORY
28 Person Date Version Bug Comments
29 ------------- ----------- ------- ------- -------------------------------
30 Nageswara 24/06/2005 115.4 4346970 Added new procedure insert_rr_value
31 J.N. Louw 22/11/2002 115.5 2224332 Updated maintain_ipv_links
32 J.N. Louw 07/11/2002 115.4 2224332 Added maintain_ipv_links
33 insert_ipv_link
34 insert_ee_value
35 L. Kloppers 17/10/2002 115.3 Added PROCEDURE za_term_cat_update
36 as a dummy for initial Core HR testing
37 L. Kloppers 06/05/2002 115.2 2266156 Added Exception handling to
38 FUNCTION get_table_value
39 L. Kloppers 02/05/2002 115.1 2266156 Added overloaded version of
40 FUNCTION get_table_value
41 J.N. Louw 25/04/2002 115.0 2266156 New version of the package
42 For previous history see
43 pezatbme.pkh
44
45 */
46
47 ----------------------------------------------------------------------------
48 -- Package Global Value
49 ----------------------------------------------------------------------------
50 g_leg_code varchar2(2) := 'ZA';
51 g_Legislation_Code varchar2(2);
52 g_cached boolean := FALSE;
53 g_effective_date date := null;
54
55 -------------------------------------------------------------------------------
56 -- ZA_TERM_CAT_UPDATE
57 -------------------------------------------------------------------------------
58 PROCEDURE za_term_cat_update (
59 p_existing_leaving_reason IN hr_lookups.lookup_code%TYPE
60 , p_seeded_leaving_reason IN hr_lookups.lookup_code%TYPE
61 )
62 AS
63 -------------------------------------------------------------------------------
64 BEGIN -- MAIN --
65 -------------------------------------------------------------------------------
66 hr_utility.set_location('per_za_utility_pkg.za_term_cat_update',1);
67 hr_utility.set_location('per_za_utility_pkg.za_term_cat_update',2);
68
69 EXCEPTION
70 WHEN OTHERS THEN
71 hr_utility.set_location('per_za_utility_pkg.za_term_cat_update',3);
72 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
73 hr_utility.raise_error;
74 -------------------------------------------------------------------------------
75 END za_term_cat_update;
76
77
78 ----------------------------------------------------------------------------
79 -- PER_ZA_TABLE_MEANING
80 ----------------------------------------------------------------------------
81 FUNCTION per_za_table_meaning (
82 p_table_name in varchar2
83 , p_column in varchar2
84 , p_value in varchar2
85 , p_business_group_id in number
86 , p_effective_date in date
87 ) RETURN VARCHAR2
88 AS
89 l_effective_date date;
90 l_meaning varchar2(80);
91 BEGIN
92 -- Use either the supplied date, or the date from fnd_sessions
93 --
94 if (p_effective_date is null) then
95 if (g_effective_date is null) then
96 begin
97 select effective_date
98 into g_effective_date
99 from fnd_sessions
100 where session_id = userenv('sessionid');
101 end;
102 end if;
103 l_effective_date := g_effective_date;
104 else
105 l_effective_date := p_effective_date;
106 end if;
107 --
108 -- get the legislation code for the specified
109 -- business group
110 begin
111 if g_cached = FALSE THEN
112 select legislation_code
113 into g_Legislation_Code
114 from per_business_groups
115 where business_group_id = P_Business_Group_id;
116 g_cached := TRUE;
117 end if;
118 end;
119
120
121 --Fetch the Table Meaning for the specific User Table
122 --for the specified Business group id.
123 begin
124 select pur.row_low_range_or_name
125 into l_meaning
126 from pay_user_column_instances_f puci,
127 pay_user_columns puc ,
128 pay_user_rows_f pur ,
129 pay_user_tables put
130 where put.user_table_name = p_table_name
131 and puc.user_table_id = put.user_table_id
132 and pur.user_table_id = put.user_table_id
133 and puci.user_row_id = pur.user_row_id
134 and puci.user_column_id = puc.user_column_id
135 and puc.user_column_name = p_column
136 and puci.value = p_value
137 and l_effective_date between pur.effective_start_date
138 and pur.effective_end_date
139 and l_effective_date between puci.effective_start_date
140 and puci.effective_end_date
141 and nvl (puci.business_group_id, P_Business_Group_id)
142 = P_Business_Group_id
143 and nvl (puci.legislation_code, g_Legislation_Code)
144 = g_Legislation_Code;
145 exception
146 when no_data_found then
147 l_meaning := null;
148 end;
149
150 return l_meaning;
151 END per_za_table_meaning;
152
153 ----------------------------------------------------------------------------
154 -- CHK_ENTRY_IN_LOOKUP
155 ----------------------------------------------------------------------------
156 FUNCTION chk_entry_in_lookup (
157 p_lookup_type IN hr_leg_lookups.lookup_type%TYPE
158 , p_entry_val IN hr_leg_lookups.meaning%TYPE
159 , p_effective_date IN hr_leg_lookups.start_date_active%TYPE
160 , p_message OUT NOCOPY VARCHAR2
161 ) RETURN VARCHAR2
162 AS
163
164 CURSOR c_entry_in_lookup IS
165 select 'X'
166 from hr_leg_lookups hll
167 where hll.LOOKUP_TYPE = p_lookup_type
168 and hll.meaning = p_entry_val
169 and hll.enabled_flag = 'Y'
170 and p_effective_date between nvl(hll.start_date_active, p_effective_date)
171 and nvl(hll.end_date_active, p_effective_date);
172 CURSOR c_lookup_values IS
173 select hll.meaning
174 from hr_leg_lookups hll
175 where hll.LOOKUP_TYPE = p_lookup_type
176 and hll.enabled_flag = 'Y'
177 and p_effective_date between nvl(hll.start_date_active, p_effective_date)
178 and nvl(hll.end_date_active, p_effective_date)
179 order by hll.lookup_code;
180
181 l_found_value_in_lookup VARCHAR2(1);
182 -- There is 255 character limit on the error screen
183 l_msg VARCHAR2(255) := ' ';
184
185 BEGIN
186 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
187 hr_utility.set_location('per_za_utility_pkg.chk_entry_in_lookup',1);
188
189 -- Check if the value exists in the lookup
190 OPEN c_entry_in_lookup;
191 hr_utility.set_location('per_za_utility_pkg.chk_entry_in_lookup',2);
192
193 FETCH c_entry_in_lookup INTO l_found_value_in_lookup;
194 IF c_entry_in_lookup%FOUND THEN
195 hr_utility.set_location('per_za_utility_pkg.chk_entry_in_lookup',3);
196 l_found_value_in_lookup := 'Y';
197 ELSE
198 hr_utility.set_location('per_za_utility_pkg.chk_entry_in_lookup',4);
199 l_found_value_in_lookup := 'N';
200 END IF;
201 CLOSE c_entry_in_lookup;
202
203 hr_utility.set_location('per_za_utility_pkg.chk_entry_in_lookup',5);
204
205 -- If the value did not exist, create a message with all the
206 -- possible value in the lookup
207 IF l_found_value_in_lookup = 'N' THEN
208 hr_utility.set_location('per_za_utility_pkg.chk_entry_in_lookup',6);
209
210 FOR v_lookup_values IN c_lookup_values LOOP
211 l_msg := l_msg||', '||v_lookup_values.meaning;
212 END LOOP;
213
214 hr_utility.set_location('per_za_utility_pkg.chk_entry_in_lookup',7);
215
216 l_msg := substr(l_msg,3,215);
217 l_msg := 'Value must be one of the following: '||l_msg;
218
219 END IF;
220
221 hr_utility.set_location('per_za_utility_pkg.chk_entry_in_lookup',8);
222
223 -- Setup Out variables and Return statements
224 p_message := l_msg;
225 RETURN l_found_value_in_lookup;
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 IF c_entry_in_lookup%ISOPEN THEN
230 CLOSE c_entry_in_lookup;
231 END IF;
232 hr_utility.set_location('per_za_utility_pkg.chk_entry_in_lookup',9);
233 hr_utility.trace('Sql error code: '||TO_CHAR(SQLCODE));
234 hr_utility.trace('Sql error msg: '||SUBSTR(SQLERRM(SQLCODE), 1, 100));
235 hr_utility.raise_error;
236
237 END chk_entry_in_lookup;
238
239
240 ----------------------------------------------------------------------------
241 -- GET_TABLE_VALUE
242 ----------------------------------------------------------------------------
243 FUNCTION get_table_value (
244 p_table_name IN VARCHAR2
245 , p_col_name IN VARCHAR2
246 , p_row_value IN VARCHAR2
247 , p_effective_date IN DATE
248 ) RETURN VARCHAR2
249 AS
250 l_effective_date date;
251 l_range_or_match pay_user_tables.range_or_match%type;
252 l_table_id pay_user_tables.user_table_id%type;
253 l_value pay_user_column_instances_f.value%type;
254
255 begin
256 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
257 --
258 -- Use either the supplied date, or the date from fnd_sessions
259 --
260 if (p_effective_date is null) then
261 begin
262 hr_utility.set_location ('hruserdt.get_table_value', 1);
263 select effective_date
264 into l_effective_date
265 from fnd_sessions
266 where session_id = userenv('sessionid');
267 end;
268 else
269 l_effective_date := p_effective_date;
270 end if;
271 --
272 -- get the type of query to be performed, either range or match
273 --
274 hr_utility.set_location ('hruserdt.get_table_value', 3);
275 select tab.range_or_match
276 , tab.user_table_id
277 into l_range_or_match
278 , l_table_id
279 from pay_user_tables tab
280 where upper(tab.user_table_name) = upper(p_table_name)
281 and tab.legislation_code = g_leg_code;
282 --
283 if (l_range_or_match = 'M') then -- matched
284 begin
285 hr_utility.set_location ('hruserdt.get_table_value', 4);
286 select CINST.value
287 into l_value
288 from pay_user_column_instances_f CINST
289 , pay_user_columns C
290 , pay_user_rows_f R
291 , pay_user_tables TAB
292 where TAB.user_table_id = l_table_id
293 and C.user_table_id = TAB.user_table_id
294 and C.legislation_code = g_leg_code
295 and upper (C.user_column_name) = upper (p_col_name)
296 and CINST.user_column_id = C.user_column_id
297 and R.user_table_id = TAB.user_table_id
298 and l_effective_date between R.effective_start_date
299 and R.effective_end_date
300 and R.legislation_code = g_leg_code
301 and decode
302 ( TAB.user_key_units
303 , 'D', to_char(fnd_date.canonical_to_date(p_row_value))
304 , 'N', p_row_value
305 , 'T', upper (p_row_value)
306 , null
307 ) = decode
308 ( TAB.user_key_units
309 , 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name))
310 , 'N', R.row_low_range_or_name
311 , 'T', upper (R.row_low_range_or_name)
312 , null
313 )
314 and CINST.user_row_id = R.user_row_id
315 and l_effective_date between CINST.effective_start_date
316 and CINST.effective_end_date
317 and CINST.legislation_code = g_leg_code;
318 --
319 return l_value;
320
321 exception
322
323 when NO_DATA_FOUND then
324
325 return l_value;
326
327 end;
328 else -- range
329 begin
330 hr_utility.set_location ('hruserdt.get_table_value', 5);
331 select CINST.value
332 into l_value
333 from pay_user_column_instances_f CINST
334 , pay_user_columns C
335 , pay_user_rows_f R
336 , pay_user_tables TAB
337 where TAB.user_table_id = l_table_id
338 and C.user_table_id = TAB.user_table_id
339 and C.legislation_code = g_leg_code
340 and upper (C.user_column_name) = upper (p_col_name)
341 and CINST.user_column_id = C.user_column_id
342 and R.user_table_id = TAB.user_table_id
343 and l_effective_date between R.effective_start_date
344 and R.effective_end_date
345 and R.legislation_code = g_leg_code
346 and fnd_number.canonical_to_number (p_row_value)
347 between fnd_number.canonical_to_number (R.row_low_range_or_name)
348 and fnd_number.canonical_to_number (R.row_high_range)
349 and TAB.user_key_units = 'N'
350 and CINST.user_row_id = R.user_row_id
351 and l_effective_date between CINST.effective_start_date
352 and CINST.effective_end_date
353 and CINST.legislation_code = g_leg_code;
354 --
355 return l_value;
356
357 exception
358
359 when NO_DATA_FOUND then
360
361 return l_value;
362
363 end;
364 end if;
365
366 end get_table_value;
367
368 ----------------------------------------------------------------------------
369 -- GET_TABLE_VALUE Overloaded version to select for a Business Group
370 -- The function is meant for selecting from a Legislative User Table, with
371 -- Legislative Columns, but User (or Business Group) Rows and Values
372 ----------------------------------------------------------------------------
373 FUNCTION get_table_value (
374 p_table_name IN VARCHAR2
375 , p_col_name IN VARCHAR2
376 , p_row_value IN VARCHAR2
377 , p_effective_date IN DATE
378 , p_business_group_id IN VARCHAR2
379 ) RETURN VARCHAR2
380 AS
381 l_effective_date date;
382 l_range_or_match pay_user_tables.range_or_match%type;
383 l_table_id pay_user_tables.user_table_id%type;
384 l_value pay_user_column_instances_f.value%type;
385
386 begin
387 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
388 --
389 -- Use either the supplied date, or the date from fnd_sessions
390 --
391 if (p_effective_date is null) then
392 begin
393 hr_utility.set_location ('hruserdt.get_table_value', 1);
394 select effective_date
395 into l_effective_date
396 from fnd_sessions
397 where session_id = userenv('sessionid');
398 end;
399 else
400 l_effective_date := p_effective_date;
401 end if;
402 --
403 -- get the type of query to be performed, either range or match
404 --
405 hr_utility.set_location ('hruserdt.get_table_value', 3);
406 select tab.range_or_match
407 , tab.user_table_id
408 into l_range_or_match
409 , l_table_id
410 from pay_user_tables tab
411 where upper(tab.user_table_name) = upper(p_table_name)
412 and tab.legislation_code = g_leg_code;
413 --
414 if (l_range_or_match = 'M') then -- matched
415 begin
416 hr_utility.set_location ('hruserdt.get_table_value', 4);
417 select CINST.value
418 into l_value
419 from pay_user_column_instances_f CINST
420 , pay_user_columns C
421 , pay_user_rows_f R
422 , pay_user_tables TAB
423 where TAB.user_table_id = l_table_id
424 and C.user_table_id = TAB.user_table_id
425 and C.legislation_code = g_leg_code
426 and upper (C.user_column_name) = upper (p_col_name)
427 and CINST.user_column_id = C.user_column_id
428 and R.user_table_id = TAB.user_table_id
429 and l_effective_date between R.effective_start_date
430 and R.effective_end_date
431 and R.business_group_id = p_business_group_id
432 and decode
433 ( TAB.user_key_units
434 , 'D', to_char(fnd_date.canonical_to_date(p_row_value))
435 , 'N', p_row_value
436 , 'T', upper (p_row_value)
437 , null
438 ) = decode
439 ( TAB.user_key_units
440 , 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name))
441 , 'N', R.row_low_range_or_name
442 , 'T', upper (R.row_low_range_or_name)
443 , null
444 )
445 and CINST.user_row_id = R.user_row_id
446 and l_effective_date between CINST.effective_start_date
447 and CINST.effective_end_date
448 and CINST.business_group_id = p_business_group_id;
449 --
450 return l_value;
451
452 exception
453
454 when NO_DATA_FOUND then
455
456 return l_value;
457
458 end;
459 else -- range
460 begin
461 hr_utility.set_location ('hruserdt.get_table_value', 5);
462 select CINST.value
463 into l_value
464 from pay_user_column_instances_f CINST
465 , pay_user_columns C
466 , pay_user_rows_f R
467 , pay_user_tables TAB
468 where TAB.user_table_id = l_table_id
469 and C.user_table_id = TAB.user_table_id
470 and C.legislation_code = g_leg_code
471 and upper (C.user_column_name) = upper (p_col_name)
472 and CINST.user_column_id = C.user_column_id
473 and R.user_table_id = TAB.user_table_id
474 and l_effective_date between R.effective_start_date
475 and R.effective_end_date
476 and R.business_group_id = p_business_group_id
477 and fnd_number.canonical_to_number (p_row_value)
478 between fnd_number.canonical_to_number (R.row_low_range_or_name)
479 and fnd_number.canonical_to_number (R.row_high_range)
480 and TAB.user_key_units = 'N'
481 and CINST.user_row_id = R.user_row_id
482 and l_effective_date between CINST.effective_start_date
483 and CINST.effective_end_date
484 and CINST.business_group_id = p_business_group_id;
485 --
486 return l_value;
487
488 exception
489
490 when NO_DATA_FOUND then
491
492 return l_value;
493
494 end;
495 end if;
496
497 end get_table_value;
498
499 -------------------------------------------------------------------------------
500 -- insert_ipv_link
501 -- This procedure handles the insert of values into pay_link_input_values_f
502 -------------------------------------------------------------------------------
503 PROCEDURE insert_ipv_link (
504 p_effective_start_date IN pay_link_input_values_f.effective_start_date%TYPE
505 , p_effective_end_date IN pay_link_input_values_f.effective_end_date%TYPE
506 , p_element_link_id IN pay_link_input_values_f.element_link_id%TYPE
507 , p_input_value_id IN pay_link_input_values_f.input_value_id%TYPE
508 , p_costed_flag IN pay_link_input_values_f.costed_flag%TYPE
509 , p_default_value IN pay_link_input_values_f.default_value%TYPE
510 , p_max_value IN pay_link_input_values_f.max_value%TYPE
511 , p_min_value IN pay_link_input_values_f.min_value%TYPE
512 , p_warning_or_error IN pay_link_input_values_f.warning_or_error%TYPE
513 )
514 AS
515 ------------
516 -- Variables
517 ------------
518 l_link_input_pk pay_link_input_values_f.link_input_value_id%TYPE;
519
520 -------------------------------------------------------------------------------
521 BEGIN -- MAIN --
522 -------------------------------------------------------------------------------
523 hr_utility.set_location('per_za_utility_pkg.insert_ipv_link',1);
524 -- get link_input_value_id from the sequence
525 SELECT pay_link_input_values_s.nextval
526 INTO l_link_input_pk
527 FROM dual;
528 hr_utility.set_location('per_za_utility_pkg.insert_ipv_link',2);
529 INSERT
530 INTO pay_link_input_values_f
531 ( link_input_value_id
532 , effective_start_date
533 , effective_end_date
534 , element_link_id
535 , input_value_id
536 , costed_flag
537 , default_value
538 , max_value
539 , min_value
540 , warning_or_error
541 , last_update_date
542 , last_updated_by
543 , last_update_login
544 , created_by
545 , creation_date
546 )
547 VALUES
548 ( l_link_input_pk
549 , p_effective_start_date
550 , p_effective_end_date
551 , p_element_link_id
552 , p_input_value_id
553 , p_costed_flag
554 , p_default_value
555 , p_max_value
556 , p_min_value
557 , p_warning_or_error
558 , sysdate
559 , -1
560 , -1
561 , -1
562 , sysdate
563 );
564 hr_utility.set_location('per_za_utility_pkg.insert_ipv_link',3);
565
566 EXCEPTION
567 WHEN OTHERS THEN
568 hr_utility.set_location('per_za_utility_pkg.insert_ipv_link',4);
569 hr_utility.trace('Sql error code: '||TO_CHAR(SQLCODE));
570 hr_utility.trace('Sql error msg: ' ||SUBSTR(SQLERRM(SQLCODE), 1, 100));
571 hr_utility.raise_error;
572
573 -------------------------------------------------------------------------------
574 END insert_ipv_link;
575
576 -------------------------------------------------------------------------------
577 -- insert_ee_value
578 -------------------------------------------------------------------------------
579 PROCEDURE insert_ee_value (
580 p_effective_start_date IN pay_element_entry_values_f.effective_start_date%TYPE
581 , p_effective_end_date IN pay_element_entry_values_f.effective_end_date%TYPE
582 , p_input_value_id IN pay_element_entry_values_f.input_value_id%TYPE
583 , p_element_entry_id IN pay_element_entry_values_f.element_entry_id%TYPE
584 , p_screen_entry_value IN pay_element_entry_values_f.screen_entry_value%TYPE
585 )
586 AS
587 ------------
588 -- Variables
589 ------------
590 l_entry_value_pk pay_element_entry_values_f.element_entry_value_id%TYPE;
591
592 -------------------------------------------------------------------------------
593 BEGIN -- MAIN --
594 -------------------------------------------------------------------------------
595 hr_utility.set_location('per_za_utility_pkg.insert_ee_value',1);
596 -- Get the element_entry_value_id from the sequence
597 SELECT pay_element_entry_values_s.nextval
598 INTO l_entry_value_pk
599 FROM dual;
600 hr_utility.set_location('per_za_utility_pkg.insert_ee_value',2);
601 -- Insert a new row using the sequence value
602 INSERT
603 INTO pay_element_entry_values_f
604 ( element_entry_value_id
605 , effective_start_date
606 , effective_end_date
607 , input_value_id
608 , element_entry_id
609 , screen_entry_value
610 )
611 VALUES
612 ( l_entry_value_pk
613 , p_effective_start_date
614 , p_effective_end_date
615 , p_input_value_id
616 , p_element_entry_id
617 , p_screen_entry_value
618 );
619
620 hr_utility.set_location('per_za_utility_pkg.insert_ee_value',3);
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 hr_utility.set_location('per_za_utility_pkg.insert_ee_value',4);
625 hr_utility.trace('Sql error code: '||TO_CHAR(SQLCODE));
626 hr_utility.trace('Sql error msg: ' ||SUBSTR(SQLERRM(SQLCODE), 1, 100));
627 hr_utility.raise_error;
628 -------------------------------------------------------------------------------
629 END insert_ee_value;
630
631 -------------------------------------------------------------------------------
632 -- elm_link_start_date
633 -------------------------------------------------------------------------------
634 FUNCTION elm_link_start_date (
635 p_element_link_id IN pay_element_links_f.element_link_id%TYPE
636 )
637 RETURN pay_element_links_f.effective_start_date%TYPE AS
638 ---------
639 -- Cursor
640 ---------
641 CURSOR c_start_date(
642 p_elm_lnk_id IN pay_element_links_f.element_link_id%TYPE
643 )
644 IS
645 select min(pel.effective_start_date)
646 from pay_element_links_f pel
647 where pel.element_link_id = p_elm_lnk_id;
648
649 ------------
650 -- Variables
651 ------------
652 l_min_eff_start_date pay_element_links_f.effective_start_date%TYPE;
653
654 -------------------------------------------------------------------------------
655 BEGIN -- MAIN --
656 -------------------------------------------------------------------------------
657 hr_utility.set_location('per_za_utility_pkg.elm_link_start_date',1);
658
659 OPEN c_start_date(p_element_link_id);
660 FETCH c_start_date INTO l_min_eff_start_date;
661 CLOSE c_start_date;
662
663 hr_utility.set_location('per_za_utility_pkg.elm_link_start_date',2);
664 RETURN l_min_eff_start_date;
665
666 EXCEPTION
667 WHEN OTHERS THEN
668 hr_utility.set_location('per_za_utility_pkg.elm_link_start_date',3);
669 IF c_start_date%ISOPEN THEN
670 hr_utility.set_location('per_za_utility_pkg.elm_link_start_date',4);
671 CLOSE c_start_date;
672 END IF;
673 hr_utility.set_location('per_za_utility_pkg.elm_link_start_date',5);
674 hr_utility.trace('Sql error code: '||TO_CHAR(SQLCODE));
675 hr_utility.trace('Sql error msg: ' ||SUBSTR(SQLERRM(SQLCODE), 1, 100));
676 hr_utility.raise_error;
677 -------------------------------------------------------------------------------
678 END elm_link_start_date;
679
680 -------------------------------------------------------------------------------
681 -- elm_link_end_date
682 -------------------------------------------------------------------------------
683 FUNCTION elm_link_end_date (
684 p_element_link_id IN pay_element_links_f.element_link_id%TYPE
685 )
686 RETURN pay_element_links_f.effective_end_date%TYPE AS
687 ---------
688 -- Cursor
689 ---------
690 CURSOR c_end_date(
691 p_elm_lnk_id IN pay_element_links_f.element_link_id%TYPE
692 )
693 IS
694 select max(pel.effective_end_date)
695 from pay_element_links_f pel
696 where pel.element_link_id = p_elm_lnk_id;
697
698 ------------
699 -- Variables
700 ------------
701 l_max_eff_end_date pay_element_links_f.effective_end_date%TYPE;
702
703 -------------------------------------------------------------------------------
704 BEGIN -- MAIN --
705 -------------------------------------------------------------------------------
706 hr_utility.set_location('per_za_utility_pkg.elm_link_end_date',1);
707
708 OPEN c_end_date(p_element_link_id);
709 FETCH c_end_date INTO l_max_eff_end_date;
710 CLOSE c_end_date;
711
712 hr_utility.set_location('per_za_utility_pkg.elm_link_end_date',2);
713 RETURN l_max_eff_end_date;
714
715 EXCEPTION
716 WHEN OTHERS THEN
717 hr_utility.set_location('per_za_utility_pkg.elm_link_end_date',3);
718 IF c_end_date%ISOPEN THEN
719 hr_utility.set_location('per_za_utility_pkg.elm_link_end_date',4);
720 CLOSE c_end_date;
721 END IF;
722 hr_utility.set_location('per_za_utility_pkg.elm_link_end_date',5);
723 hr_utility.trace('Sql error code: '||TO_CHAR(SQLCODE));
724 hr_utility.trace('Sql error msg: ' ||SUBSTR(SQLERRM(SQLCODE), 1, 100));
725 hr_utility.raise_error;
726 -------------------------------------------------------------------------------
727 END elm_link_end_date;
728
729 -------------------------------------------------------------------------------
730 -- maintain_ipv_links
731 -------------------------------------------------------------------------------
732 PROCEDURE maintain_ipv_links
733 AS
734 ---------
735 -- Cursor
736 ---------
737 CURSOR c_input_values
738 IS
739 select
740 piv.effective_start_date
741 , piv.effective_end_date
742 , pel.element_link_id
743 , piv.input_value_id
744 , piv.default_value
745 , piv.max_value
746 , piv.min_value
747 , piv.warning_or_error
748 from
749 pay_element_links_f pel
750 , pay_input_values_f piv
751 , pay_element_types_f pet
752 where
753 pet.element_type_id = pel.element_type_id
754 and pet.element_type_id = piv.element_type_id
755 and pet.legislation_code = 'ZA'
756 and pet.business_group_id is null
757 and pel.effective_end_date between piv.effective_start_date
758 and piv.effective_end_date
759 and pel.effective_end_date between pet.effective_start_date
760 and pet.effective_end_date
761 and pel.effective_end_date =
762 ( select max(pel2.effective_end_date)
763 from pay_element_links_f pel2
764 where pel2.element_link_id = pel.element_link_id
765 )
766 and not exists
767 ( select
768 null
769 from
770 pay_link_input_values_f pli
771 where
772 pli.element_link_id = pel.element_link_id
773 and pli.input_value_id = piv.input_value_id
774 and pli.effective_start_date >=
775 ( select min(pel2.effective_start_date)
776 from pay_element_links_f pel2
777 where pel2.element_link_id = pli.element_link_id
778 )
779 and pli.effective_end_date <=
780 ( select max(pel2.effective_end_date)
781 from pay_element_links_f pel2
782 where pel2.element_link_id = pli.element_link_id
783 )
784 );
785
786 ---------
787 -- Cursor
788 ---------
789 CURSOR c_element_entries(
790 p_element_link_id IN pay_element_links_f.element_link_id%TYPE
791 )
792 IS
793 select pee.effective_start_date effective_start_date
794 , pee.effective_end_date effective_end_date
795 , pee.element_entry_id element_entry_id
796 from pay_element_entries_f pee
797 where pee.element_link_id = p_element_link_id;
798
799 ------------
800 -- Variables
801 ------------
802 l_ipv_link_start_date pay_element_links_f.effective_start_date%TYPE;
803 l_ipv_link_end_date pay_element_links_f.effective_end_date%TYPE;
804
805 -------------------------------------------------------------------------------
806 BEGIN -- MAIN --
807 -------------------------------------------------------------------------------
808 ------------------------------------------
809 -- hr_utility.trace_on(null,'perlegza_sql');
810 ------------------------------------------
811 hr_utility.set_location('per_za_utility_pkg.maintain_ipv_links',1);
812 <<non_linked_input_values>>
813 FOR v_input_value IN c_input_values LOOP
814 hr_utility.trace('Input Value ID: '||TO_CHAR(v_input_value.input_value_id));
815
816 l_ipv_link_start_date := greatest( elm_link_start_date(v_input_value.element_link_id)
817 , v_input_value.effective_start_date);
818
819 l_ipv_link_end_date := least( elm_link_end_date(v_input_value.element_link_id)
820 , v_input_value.effective_end_date);
821
822 -- Insert a link for any non linked input values where
823 -- an element exists
824 insert_ipv_link (
825 p_effective_start_date => l_ipv_link_start_date
826 , p_effective_end_date => l_ipv_link_end_date
827 , p_element_link_id => v_input_value.element_link_id
828 , p_input_value_id => v_input_value.input_value_id
829 , p_costed_flag => 'N'
830 , p_default_value => v_input_value.default_value
831 , p_max_value => v_input_value.max_value
832 , p_min_value => v_input_value.min_value
833 , p_warning_or_error => v_input_value.warning_or_error
834 );
835
836 <<non_entered_input_values>>
837 FOR v_entry IN c_element_entries (
838 p_element_link_id => v_input_value.element_link_id
839 )
840 LOOP
841 hr_utility.trace('Element Entry ID: '||TO_CHAR(v_entry.element_entry_id));
842 -- Create a NULL entry for every element entry
843 insert_ee_value (
844 p_effective_start_date => v_entry.effective_start_date
845 , p_effective_end_date => v_entry.effective_end_date
846 , p_input_value_id => v_input_value.input_value_id
847 , p_element_entry_id => v_entry.element_entry_id
848 , p_screen_entry_value => NULL
849 );
850
851 END LOOP non_entered_input_values;
852 END LOOP non_linked_input_values;
853
854 hr_utility.set_location('per_za_utility_pkg.maintain_ipv_links',2);
855
856 EXCEPTION
857 WHEN OTHERS THEN
858 hr_utility.set_location('per_za_utility_pkg.maintain_ipv_links',3);
859 hr_utility.trace('Sql error code: '||TO_CHAR(SQLCODE));
860 hr_utility.trace('Sql error msg: ' ||SUBSTR(SQLERRM(SQLCODE), 1, 100));
861 hr_utility.raise_error;
862 ------------------------
863 -- hr_utility.trace_off;
864 ------------------------
865 -------------------------------------------------------------------------------
866 END maintain_ipv_links;
867 ----------------------------------------------------------------------------
868
869 -- -------------------------------------------------------------------------------------------
870 -- Return the ID for a given context.
871 -- -------------------------------------------------------------------------------------------
872 --
873 FUNCTION get_context_id(p_context_name VARCHAR2) RETURN NUMBER IS
874 --
875 --
876 -- Return the ID for a given context.
877 --
878 CURSOR csr_context(p_context_name VARCHAR2) IS
879 SELECT context_id
880 FROM ff_contexts
881 WHERE context_name = p_context_name;
882 --
883 --
884 -- Local variables.
885 --
886 l_context_id NUMBER;
887 --
888 BEGIN
889 --
890 hr_utility.set_location('Entering: ' || 'per_za_utility_pkg.get_context_id', 10);
891 --
892 OPEN csr_context(p_context_name);
893 FETCH csr_context INTO l_context_id;
894 CLOSE csr_context;
895 --
896 hr_utility.set_location('Leaving: ' || 'per_za_utility_pkg.get_context_id', 20);
897 --
898 RETURN l_context_id;
899 --
900 END get_context_id;
901
902
903 ----------------------------------------------------------------------------
904 -- Procedure inserts input value for a perticular run result and input value
905 ----------------------------------------------------------------------------
906
907 PROCEDURE insert_rr_value (
908 p_input_value_id IN pay_input_values_f.input_value_id%TYPE
909 ,p_run_result_id IN pay_run_results.run_result_id%TYPE
910 ,p_result_value IN pay_run_result_values.result_value%TYPE
911 )
912 AS
913 ------------
914 -- Variable
915 ------------
916 rec_exists number;
917
918 l_clar_no_con number;
919 l_Dir_no_con varchar2(60);
920 l_clar_no number;
921 l_Dir_no varchar2(60);
922 l_input_value_name pay_input_values_f.name%TYPE;
923
924
925 -- Cursors
926
927 Cursor cur_run_res_con is
928 select prr.ASSIGNMENT_ACTION_ID
929 ,prr.ELEMENT_ENTRY_ID
930 ,peef.ASSIGNMENT_ID
931 from pay_run_results prr
932 ,pay_element_entries_f peef
933 where prr.element_entry_id = peef.element_entry_id
934 and prr.run_result_id = p_run_result_id;
935
936 cur_run_res_con_rec cur_run_res_con%ROWTYPE;
937 -------------------------------------------------------------------------------
938 BEGIN -- MAIN --
939 -------------------------------------------------------------------------------
940 hr_utility.set_location('per_za_utility_pkg.insert_rr_value',1);
941
942 l_clar_no_con := get_context_id('SOURCE_NUMBER');
943 l_Dir_no_con := get_context_id('SOURCE_TEXT');
944
945
946 open cur_run_res_con;
947 fetch cur_run_res_con into cur_run_res_con_rec;
948
949 select pivf.name
950 into l_input_value_name
951 from pay_input_values_f pivf
952 where pivf.INPUT_VALUE_ID = p_input_value_id
953 and rownum = 1;
954
955 hr_utility.set_location('per_za_utility_pkg.insert_rr_value',2);
956
957 insert into pay_run_result_values (
958 INPUT_VALUE_ID
959 ,RUN_RESULT_ID
960 ,RESULT_VALUE)
961 (select
962 p_input_value_id
963 ,p_run_result_id
964 ,p_result_value
965 from dual
966 where not exists ( select null
967 from pay_run_result_values
968 where INPUT_VALUE_ID = p_input_value_id
969 and run_result_id = p_run_result_id
970 )
971 );
972
973 if l_input_value_name = 'Tax Directive Number' then
974 pay_za_rules.get_source_text_context
975 (cur_run_res_con_rec.assignment_action_id
976 ,cur_run_res_con_rec.element_entry_id
977 ,l_Dir_no);
978
979 if l_Dir_no is not null then
980 INSERT INTO pay_action_contexts
981 (assignment_action_id
982 ,assignment_id
983 ,context_id
984 ,context_value)
985 (select cur_run_res_con_rec.assignment_action_id
986 ,cur_run_res_con_rec.assignment_id
987 ,l_Dir_no_con
988 ,l_dir_no
989 from dual
990 where not exists (select null
991 from pay_action_contexts
992 where assignment_action_id = cur_run_res_con_rec.assignment_action_id
993 and assignment_id = cur_run_res_con_rec.assignment_id
994 and context_id = l_Dir_no_con
995 and context_value = l_dir_no )
996 );
997 end if;
998 elsif l_input_value_name = 'Clearance Number' then
999
1000 pay_za_rules.get_source_number_context
1001 (cur_run_res_con_rec.assignment_action_id
1002 ,cur_run_res_con_rec.element_entry_id
1003 ,l_clar_no);
1004
1005 if l_clar_no is not null then
1006 INSERT INTO pay_action_contexts
1007 (assignment_action_id
1008 ,assignment_id
1009 ,context_id
1010 ,context_value)
1011 (select cur_run_res_con_rec.assignment_action_id
1012 ,cur_run_res_con_rec.assignment_id
1013 ,l_clar_no_con
1014 ,l_clar_no
1015 from dual
1016 where not exists (select null
1017 from pay_action_contexts
1018 where assignment_action_id = cur_run_res_con_rec.assignment_action_id
1019 and assignment_id = cur_run_res_con_rec.assignment_id
1020 and context_id = l_clar_no_con
1021 and context_value = l_clar_no )
1022 );
1023 end if;
1024 end if;
1025 --
1026
1027 if cur_run_res_con%ISOPEN then
1028 close cur_run_res_con;
1029 end if;
1030 hr_utility.set_location('per_za_utility_pkg.insert_rr_value',3);
1031
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 hr_utility.set_location('per_za_utility_pkg.insert_rr_value',4);
1035 hr_utility.trace('Sql error code: '||TO_CHAR(SQLCODE));
1036 hr_utility.trace('Sql error msg: ' ||SUBSTR(SQLERRM(SQLCODE), 1, 100));
1037 hr_utility.raise_error;
1038 ---------------------------------------------------------------------------
1039 END insert_rr_value;
1040 ---------------------------------------------------------------------------
1041
1042
1043 END per_za_utility_pkg;
1044 ---------------------------------------------------------------------------