[Home] [Help]
PACKAGE BODY: APPS.PER_BIL_SHD
Source
1 Package Body per_bil_shd as
2 /* $Header: pebilrhi.pkb 115.10 2003/04/10 09:19:39 jheer noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_bil_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 l_proc varchar2(72) := g_package||'return_api_dml_status';
16 --
17 Begin
18 hr_utility.set_location('Entering:'||l_proc, 5);
19 --
20 Return (nvl(g_api_dml, false));
21 --
22 hr_utility.set_location(' Leaving:'||l_proc, 10);
23 End return_api_dml_status;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |---------------------------< constraint_error >---------------------------|
27 -- ----------------------------------------------------------------------------
28 Procedure constraint_error
29 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
30 --
31 l_proc varchar2(72) := g_package||'constraint_error';
32 --
33 Begin
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 --
36 If (p_constraint_name = 'hr_summary_PK') Then
37 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
38 fnd_message.set_token('PROCEDURE', l_proc);
39 fnd_message.set_token('STEP','5');
40 fnd_message.raise_error;
41 Elsif (p_constraint_name = 'PARENT_RECORD') Then
42 fnd_message.set_name('PER', 'PER_74879_PARENT_RECORD');
43 fnd_message.raise_error;
44 Elsif p_constraint_name = 'CHILD_RECORD_ITU' then
45 fnd_message.set_name('PER', 'PER_74880_CHILD_RECORD');
46 fnd_message.set_token('TYPE', 'Item Type Usage');
47 fnd_message.raise_error;
48 Elsif p_constraint_name = 'CHILD_RECORD_VR' then
49 fnd_message.set_name('PER', 'PER_74880_CHILD_RECORD');
50 fnd_message.set_token('TYPE', 'Valid Restriction');
51 fnd_message.raise_error;
52 Elsif p_constraint_name = 'CHILD_RECORD_RTU' then
53 fnd_message.set_name('PER', 'PER_74880_CHILD_RECORD');
54 fnd_message.set_token('TYPE', 'Restriction Usage');
55 fnd_message.raise_error;
56 Elsif p_constraint_name = 'CHILD_RECORD_KTU' then
57 fnd_message.set_name('PER', 'PER_74880_CHILD_RECORD');
58 fnd_message.set_token('TYPE', 'Key Type Usage');
59 fnd_message.raise_error;
60 Elsif p_constraint_name = 'CHILD_RECORD_RV' then
61 fnd_message.set_name('PER', 'PER_74880_CHILD_RECORD');
62 fnd_message.set_token('TYPE', 'Restriction Value');
63 fnd_message.raise_error;
64 Elsif p_constraint_name = 'CHILD_RECORD_KV' then
65 fnd_message.set_name('PER', 'PER_74880_CHILD_RECORD');
66 fnd_message.set_token('TYPE', 'Key Value');
67 fnd_message.raise_error;
68 Elsif p_constraint_name = 'CHILD_RECORD_IV' then
69 fnd_message.set_name('PER', 'PER_74880_CHILD_RECORD');
70 fnd_message.set_token('TYPE', 'Item Value');
71 fnd_message.raise_error;
72 Elsif p_constraint_name = 'CHILD_RECORD_VKT' then
73 fnd_message.set_name('PER', 'PER_74880_CHILD_RECORD');
74 fnd_message.set_token('TYPE', 'Valid Key Type');
75 fnd_message.raise_error;
76 Elsif p_constraint_name = 'CHILD_RECORD_PR' then
77 fnd_message.set_name('PER', 'PER_74880_CHILD_RECORD');
78 fnd_message.set_token('TYPE', 'Process Run');
79 fnd_message.raise_error;
80 Elsif (p_constraint_name = 'UNIQUE_ROW') Then
81 fnd_message.set_name('PER', 'PER_74881_UNIQUE_ROW');
82 fnd_message.raise_error;
83 Elsif (p_constraint_name = 'UNIQUE_SEQUENCE') Then
84 fnd_message.set_name('PER', 'PER_74881_UNIQUE_ROW');
85 fnd_message.raise_error;
86 Elsif (p_constraint_name = 'RECORD_PROTECT') Then
87 fnd_message.set_name('PER', 'PER_74882_RECORD_PROTECT');
88 fnd_message.raise_error;
89 Else
90 fnd_message.set_name('PER', 'HR_7877_API_INVALID_CONSTRAINT');
91 fnd_message.set_token('PROCEDURE', l_proc);
92 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
93 fnd_message.raise_error;
94 End If;
95 --
96 hr_utility.set_location(' Leaving:'||l_proc, 10);
97 End constraint_error;
98 --
99 -- ----------------------------------------------------------------------------
100 -- |-----------------------------< api_updating >-----------------------------|
101 -- ----------------------------------------------------------------------------
102 Function api_updating
103 (
104 p_id_value in number,
105 p_object_version_number in number
106 ) Return Boolean Is
107 --
108 --
109 -- Cursor selects the 'current' row from the HR Schema
110 --
111 Cursor C_Sel1 is
112 select
113 type,
114 business_group_id,
115 object_version_number,
116 id_value,
117 fk_value1,
118 fk_value2,
119 fk_value3,
120 text_value1,
121 text_value2,
122 text_value3,
123 text_value4,
124 text_value5,
125 text_value6,
126 text_value7,
127 num_value1,
128 num_value2,
129 num_value3,
130 date_value1,
131 date_value2,
132 date_value3,
133 created_by
134 from hr_summary
135 where id_value = p_id_value;
136 --
137 l_proc varchar2(72) := g_package||'api_updating';
138 l_fct_ret boolean;
139 --
140 Begin
141 hr_utility.set_location('Entering:'||l_proc, 5);
142 --
143 If (
144 p_object_version_number is null and p_id_value is null
145 ) Then
146 --
147 -- One of the primary key arguments is null therefore we must
148 -- set the returning function value to false
149 --
150 l_fct_ret := false;
151 Else
152 If (
153 p_object_version_number = g_old_rec.object_version_number and p_id_value = g_old_rec.id_value
154 ) Then
155 hr_utility.set_location(l_proc, 10);
156 --
157 -- The g_old_rec is current therefore we must
158 -- set the returning function to true
159 --
160 l_fct_ret := true;
161 Else
162 --
163 -- Select the current row into g_old_rec
164 --
165 Open C_Sel1;
166 Fetch C_Sel1 Into g_old_rec;
167 If C_Sel1%notfound Then
168 Close C_Sel1;
169 --
170 -- The primary key is invalid therefore we must error
171 --
172 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
173 hr_utility.raise_error;
174 End If;
175 Close C_Sel1;
176 If (p_object_version_number <> g_old_rec.object_version_number) Then
177 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
178 hr_utility.raise_error;
179 End If;
180 hr_utility.set_location(l_proc, 15);
181 l_fct_ret := true;
182 End If;
183 End If;
184 hr_utility.set_location(' Leaving:'||l_proc, 20);
185 Return (l_fct_ret);
186 --
187 End api_updating;
188 --
189 -- ----------------------------------------------------------------------------
190 -- |---------------------------------< lck >----------------------------------|
191 -- ----------------------------------------------------------------------------
192 Procedure lck
193 (
194 p_id_value in number,
195 p_object_version_number in number
196 ) is
197 --
198 -- Cursor selects the 'current' row from the HR Schema
199 --
200 Cursor C_Sel1 is
201 select type,
202 business_group_id,
203 object_version_number,
204 id_value,
205 fk_value1,
206 fk_value2,
207 fk_value3,
208 text_value1,
209 text_value2,
210 text_value3,
211 text_value4,
212 text_value5,
213 text_value6,
214 text_value7,
215 num_value1,
216 num_value2,
217 num_value3,
218 date_value1,
219 date_value2,
220 date_value3,
221 created_by
222 from hr_summary
223 where id_value = p_id_value
224 for update nowait;
225 --
226 l_proc varchar2(72) := g_package||'lck';
227 --
228 Begin
229 hr_utility.set_location('Entering:'||l_proc, 5);
230 --
231 -- Add any mandatory argument checking here:
232 -- Example:
233 -- hr_api.mandatory_arg_error
234 -- (p_api_name => l_proc,
235 -- p_argument => 'object_version_number',
236 -- p_argument_value => p_object_version_number);
237 --
238 Open C_Sel1;
239 Fetch C_Sel1 Into g_old_rec;
240 If C_Sel1%notfound then
241 Close C_Sel1;
242 --
243 -- The primary key is invalid therefore we must error
244 --
245 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
246 hr_utility.raise_error;
247 End If;
248 Close C_Sel1;
249 If (p_object_version_number <> g_old_rec.object_version_number) Then
250 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
251 hr_utility.raise_error;
252 End If;
253 --
254 hr_utility.set_location(' Leaving:'||l_proc, 10);
255 --
256 -- We need to trap the ORA LOCK exception
257 --
258 Exception
259 When HR_Api.Object_Locked then
260 --
261 -- The object is locked therefore we need to supply a meaningful
262 -- error message.
263 --
264 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
265 hr_utility.set_message_token('TABLE_NAME', 'hr_summary');
266 hr_utility.raise_error;
267 End lck;
268 --
269 -- ----------------------------------------------------------------------------
270 -- |-----------------------------< convert_args >-----------------------------|
271 -- ----------------------------------------------------------------------------
272 Function convert_args
273 (
274 p_type in varchar2,
275 p_business_group_id in number,
276 p_object_version_number in number,
277 p_id_value in number,
278 p_fk_value1 in number,
279 p_fk_value2 in number,
280 p_fk_value3 in number,
281 p_text_value1 in varchar2,
282 p_text_value2 in varchar2,
283 p_text_value3 in varchar2,
284 p_text_value4 in varchar2,
285 p_text_value5 in varchar2,
286 p_text_value6 in varchar2,
287 p_text_value7 in varchar2,
288 p_num_value1 in number,
289 p_num_value2 in number,
290 p_num_value3 in number,
291 p_date_value1 in date,
292 p_date_value2 in date,
293 p_date_value3 in date
294 )
295 Return g_rec_type is
296 --
297 l_rec g_rec_type;
298 l_proc varchar2(72) := g_package||'convert_args';
299 --
300 Begin
301 --
302 hr_utility.set_location('Entering:'||l_proc, 5);
303 --
304 -- Convert arguments into local l_rec structure.
305 --
306 l_rec.type := p_type;
307 l_rec.business_group_id := p_business_group_id;
308 l_rec.object_version_number := p_object_version_number;
309 l_rec.id_value := p_id_value;
310 l_rec.fk_value1 := p_fk_value1;
311 l_rec.fk_value2 := p_fk_value2;
312 l_rec.fk_value3 := p_fk_value3;
313 l_rec.text_value1 := p_text_value1;
314 l_rec.text_value2 := p_text_value2;
315 l_rec.text_value3 := p_text_value3;
316 l_rec.text_value4 := p_text_value4;
317 l_rec.text_value5 := p_text_value5;
318 l_rec.text_value6 := p_text_value6;
319 l_rec.text_value7 := p_text_value7;
320 l_rec.num_value1 := p_num_value1;
321 l_rec.num_value2 := p_num_value2;
322 l_rec.num_value3 := p_num_value3;
323 l_rec.date_value1 := p_date_value1;
324 l_rec.date_value2 := p_date_value2;
325 l_rec.date_value3 := p_date_value3;
326 --
327 -- Return the plsql record structure.
328 --
329 hr_utility.set_location(' Leaving:'||l_proc, 10);
330 Return(l_rec);
331 --
332 End convert_args;
333 --
334 -- ----------------------------------------------------------------------------
335 -- |---------------------------------< row_exist >----------------------------|
336 -- ----------------------------------------------------------------------------
337 Function row_exist (p_rec in per_bil_shd.g_rec_type) return boolean is
338
339 cursor csr_exists is
340 select 'x'
341 from hr_summary
342 where (type = p_rec.type
343 and p_rec.type in ('TEMPLATE','ITEM_TYPE','KEY_TYPE','RESTRICTION_TYPE')
344 and text_value1 = p_rec.text_value1
345 and business_group_id = p_rec.business_group_id
346 )
347 or (type = p_rec.type
348 and p_rec.type IN ('RESTRICTION_USAGE','VALID_KEY_TYPE','KEY_TYPE_USAGE','VALID_RESTRICTION')
349 and fk_value1 = p_rec.fk_value1
350 and fk_value2 = p_rec.fk_value2
354 and p_rec.type = 'ITEM_TYPE_USAGE'
351 and business_group_id = p_rec.business_group_id
352 )
353 or (type = p_rec.type
355 and fk_value1 = p_rec.fk_value1
356 and text_value1 = p_rec.text_value1
357 and business_group_id = p_rec.business_group_id
358 )
359 or (type = p_rec.type
360 and p_rec.type = 'PROCESS_RUN'
361 and text_value2 = p_rec.text_value2
362 and text_value1 = p_rec.text_value1
363 and business_group_id = p_rec.business_group_id
364 )
365 or (type = p_rec.type
366 and p_rec.type = 'RESTRICTION_VALUE'
367 and fk_value1 = p_rec.fk_value1
368 and text_value1 = p_rec.text_value1
369 and business_group_id = p_rec.business_group_id
370 );
371 --
372 l_dummy varchar2(1);
373 --
374 Begin
375 --
376 hr_utility.set_location('Entering: row_exist', 5);
377 --
378 open csr_exists;
379 fetch csr_exists into l_dummy;
380 --
381 if csr_exists%found then
382 close csr_exists;
383 --
384 hr_utility.set_location('Leaving: row_exist', 10);
385 --
386 return true;
387 else
388 close csr_exists;
389 --
390 hr_utility.set_location('Leaving: row_exist', 11);
391 --
392 return false;
393 end if;
394 --
395 End row_exist;
396 --
397 --
398 -- ----------------------------------------------------------------------------
399 -- |------< lookup_exists >------|
400 -- ----------------------------------------------------------------------------
401 --
402 Procedure lookup_exists (p_type in varchar2,
403 p_code in varchar2) is
404 --
405 cursor csr_lookup is
406 select 'x'
407 from hr_lookups
408 where lookup_type = p_type
409 and lookup_code = p_code
410 and application_id = 800;
411 --
412 l_dummy varchar2(1);
413 --
414 Begin
415 --
416 hr_utility.set_location('Entering: lookup_exist', 5);
417 --
418 open csr_lookup;
419 fetch csr_lookup into l_dummy;
420 --
421 if csr_lookup%found then
422 null;
423 else
424 fnd_message.set_name('PER','PER_74884_LOOKUP_EXIST');
425 fnd_message.set_token('NAME',p_code);
426 fnd_message.raise_error;
427 end if;
428 --
429 hr_utility.set_location('Leaving: lookup_exist', 10);
430 --
431 End lookup_exists;
432 --
433 -- ----------------------------------------------------------------------------
434 -- |-----------------------------< sequence_exist >---------------------------|
435 -- ----------------------------------------------------------------------------
436 Function sequence_exist (p_rec in per_bil_shd.g_rec_type) return boolean is
437 --
438 cursor csr_exists is
439 select 'x'
440 from hr_summary_item_type_usage
441 where sequence_number = p_rec.num_value1
442 and business_group_id = p_rec.business_group_id
443 and template_id = p_rec.fk_value1;
444 --
445 l_dummy varchar2(1);
446 --
447 Begin
448 --
449 hr_utility.set_location('Entering: sequence_exist', 5);
450 --
451 open csr_exists;
452 fetch csr_exists into l_dummy;
453 --
454 if csr_exists%found then
455 close csr_exists;
456 --
457 hr_utility.set_location('Leaving: row_exist', 10);
458 --
459 return true;
460 else
461 close csr_exists;
462 --
463 hr_utility.set_location('Leaving: row_exist', 11);
464 --
465 return false;
466 end if;
467 --
468 End sequence_exist;
469 -- ----------------------------------------------------------------------------
470 -- |-----------------------------< check_restriction_sql >---------------------|
471 -- ----------------------------------------------------------------------------
472 procedure check_restriction_sql (p_stmt in out nocopy varchar2
473 ,p_business_group_id in number) is
474 --
475 TYPE TestCurTyp IS REF CURSOR;
476 test_csr TestCurTyp;
477 l_value varchar2(32000) := null;
478 l_meaning varchar2(240) :=null;
479 l_pos_bg number := 1;
480 l_stmt varchar2(32000);
481 l_from number := 1;
482 l_error boolean;
483 --
484 begin
485 --
486 if ( instr(p_stmt,' VALUE ') > 0
487 or instr(p_stmt,' VALUE'||fnd_global.local_chr(10)) > 0 )
488 /* Added additional checks that MEANING column exists - bug 2348887 */
489 AND ( instr(p_stmt,' MEANING ') > 0
490 or instr(p_stmt,' MEANING'||fnd_global.local_chr(10)) > 0 ) then
491 l_error := false;
492 else
493 l_error := true;
494 end if;
495 --
496 begin
497 if instr(lower(p_stmt),':ctl_globals.business_group_id',l_from,1) > 0 then
498 --
499 -- Loop thro' to change all occurrences of :ctl_globals.business_group_id
500 -- into lower case
501 --
502 loop
503 l_pos_bg := instr(lower(p_stmt),':ctl_globals.business_group_id',l_from,1);
504 exit when l_pos_bg is null or l_pos_bg > length(p_stmt);
505 if l_pos_bg = 0 then
506 if length(p_stmt) >= l_pos_bg then
510 exit;
507 l_stmt := l_stmt||substr(p_stmt,l_from,length(p_stmt)+1-l_from);
508 l_pos_bg := null;
509 else
511 end if;
512 end if;
513 l_stmt := l_stmt||substr(p_stmt,l_from,(l_pos_bg-l_from))||lower(substr(p_stmt,l_pos_bg,30));
514 l_from := l_pos_bg+30;
515 end loop;
516 --
517 -- Replace variable with business group id
518 l_stmt := replace(l_stmt,':ctl_globals.business_group_id',p_business_group_id);
519 --
520 else
521 l_stmt := p_stmt;
522 end if;
523 --
524 open test_csr for l_stmt;
525 fetch test_csr into l_value, l_meaning;
526 close test_csr;
527 --
528 exception when others then
529 l_error := true;
530 end;
531 --
532 p_stmt := l_stmt;
533 --
534 if l_error then
535 fnd_message.set_name('PER','PER_74887_INVALID_SQL');
536 fnd_message.raise_error;
537 end if;
538 --
539 end check_restriction_sql;
540 -- ----------------------------------------------------------------------------
541 -- |-----------------------------< valid_value >---------------------------|
542 -- ----------------------------------------------------------------------------
543 Procedure valid_value (p_rec in per_bil_shd.g_rec_type) is
544 --
545 cursor csr_lov_exists (p_ru_id number,
546 p_bus_grp_id number) is
547 select rt.restriction_sql,
548 rt.title
549 from hr_summary_restriction_type rt,
550 hr_summary_restriction_usage ru,
551 hr_summary_valid_restriction vr
552 where vr.restriction_type_id = rt.restriction_type_id
553 and ru.valid_restriction_id = vr.valid_restriction_id
554 and ru.restriction_usage_id = p_ru_id
555 and ru.business_group_id = p_bus_grp_id;
556 --
557 l_restriction_sql varchar2(240) := null;
558 l_sql varchar2(1000);
559 l_rt_title varchar2(100);
560 l_value varchar2(100);
561 l_meaning varchar2(240);
562 l_found boolean := false;
563 source_cursor integer;
564 ignore integer;
565 TYPE TestCurTyp IS REF CURSOR;
566 test_csr TestCurTyp;
567 --
568 Begin
569 --
570 hr_utility.set_location('Entering: valid_value', 5);
571 --
572 open csr_lov_exists(p_rec.fk_value1,p_rec.business_group_id);
573 fetch csr_lov_exists into l_restriction_sql,l_rt_title;
574 if csr_lov_exists%found and l_restriction_sql is not null then
575 close csr_lov_exists;
576 --
577 begin
578 --
579 per_bil_shd.check_restriction_sql (p_stmt => l_restriction_sql
580 ,p_business_group_id => p_rec.business_group_id);
581 --
582 open test_csr for l_restriction_sql;
583 loop
584 fetch test_csr into l_value, l_meaning;
585 if test_csr%notfound then
586 l_found := false;
587 exit;
588 end if;
589 if l_value = p_rec.text_value1 then
590 l_found := true;
591 exit;
592 end if;
593 end loop;
594 close test_csr;
595 --
596 if not l_found then
597 fnd_message.set_name('PER','PER_74888_RESTRICTION_SQL');
598 fnd_message.set_token('RESTRICTION_TYPE',l_rt_title);
599 fnd_message.raise_error;
600 end if;
601 /*
602 source_cursor := dbms_sql.open_cursor;
603 --
604 begin
605 l_restriction_sql := replace(l_restriction_sql,':ctl_globals.business_group_id',p_rec.business_group_id);
606 hr_utility.set_location('Valid_Value: Parsing SQL', 10);
607 dbms_sql.parse(source_cursor,l_restriction_sql,dbms_sql.v7);
608 hr_utility.set_location('Valid_Value: Defining_column', 15);
609 dbms_sql.define_column(source_cursor,1,l_value,100);
610 ignore := dbms_sql.execute(source_cursor);
611 loop
612 if dbms_sql.fetch_rows(source_cursor) > 0 then
613 hr_utility.set_location('Valid_Value: column_value', 20);
614 dbms_sql.column_value(source_cursor,1,l_value);
615 if l_value = p_rec.text_value1 then
616 l_found := true;
617 exit;
618 end if;
619 else
620 l_found := false;
621 exit;
622 end if;
623 end loop;
624 --
625 dbms_sql.close_cursor(source_cursor);
626 --
627 if not l_found then
628 fnd_message.set_name('PER','PER_74888_RESTRICTION_SQL');
629 fnd_message.set_token('RESTRICTION_TYPE',l_rt_title);
630 fnd_message.raise_error;
631 end if;
632 --*/
633 end;
634 else
635 close csr_lov_exists;
636 end if;
637 --
638 hr_utility.set_location('Leaving: valid_value', 25);
639 --
640 End valid_value;
641 --
642 -- ----------------------------------------------------------------------------
643 -- | < get_restriction_meaning > |
647 ,p_value in varchar2)return varchar2 is
644 -- | Added for bug 2348887 to enable meanings to be shown in GSP form |
645 -- ----------------------------------------------------------------------------
646 function get_restriction_meaning(p_valid_restriction_id in number
648 --
649 cursor csr_get_sql is
650 select rt.restriction_sql
651 , rt.business_group_id
652 from hr_summary_restriction_type rt,
653 hr_summary_valid_restriction vr
654 where rt.restriction_type_id = vr.restriction_type_id
655 and rt.business_group_id = vr.business_group_id
656 and vr.valid_restriction_id = p_valid_restriction_id;
657 --
658 l_stmt varchar2(32000);
659 l_business_group_id number;
660 --
661 TYPE TestCurTyp IS REF CURSOR;
662 test_csr TestCurTyp;
663 l_meaning varchar2(32000) := null;
664 --
665 BEGIN
666 hr_utility.set_location('Entered get_restriction_meaning',5);
667 open csr_get_sql;
668 fetch csr_get_sql into l_stmt, l_business_group_id;
669 close csr_get_sql;
670 --
671 /* Call Check_restriction_sql to ensure that any occurrence of
672 :ctl_globals.business_group_id are replaced with the business group id value */
673 per_bil_shd.check_restriction_sql (p_stmt => l_stmt
674 ,p_business_group_id => l_business_group_id);
675 --
676 l_stmt := 'Select rs.meaning from (' || l_stmt || ') rs where rs.value = '''||p_value ||''' ';
677 hr_utility.set_location('About to open cursor',17);
678 open test_csr for l_stmt;
679 hr_utility.set_location('Opened cursor',20);
680 fetch test_csr into l_meaning;
681 hr_utility.set_location('fetched meaning:'||l_meaning,25);
682 close test_csr;
683 --
684 return l_meaning;
685 --
686 exception when others then
687 /* If the cursor cannot find the meaning, then display the value */
688 l_meaning := p_value;
689 hr_utility.set_location('meaning defaulted',50);
690 END get_restriction_meaning;
691 --
692 -- ----------------------------------------------------------------------------
693 -- |-------------------------------< parent_found >----------------------------|
694 -- ----------------------------------------------------------------------------
695 Function parent_found (p_rec in per_bil_shd.g_rec_type) return boolean is
696 --
697 cursor csr_exists (p_value number) is
698 select 'x'
699 from hr_summary
700 where id_value = p_value
701 and business_group_id = p_rec.business_group_id;
702 --
703 l_dummy varchar2(1);
704 l_found boolean := FALSE;
705 --
706 Begin
707 --
708 hr_utility.set_location('Entering: parent_found', 5);
709 --
710 if p_rec.fk_value1 is not null then
711 open csr_exists(p_rec.fk_value1);
712 fetch csr_exists into l_dummy;
713 --
714 if csr_exists%found then
715 l_found := TRUE;
716 else
717 l_found := FALSE;
718 end if;
719 close csr_exists;
720 end if;
721 if p_rec.fk_value2 is not null and l_found then
722 open csr_exists(p_rec.fk_value2);
723 fetch csr_exists into l_dummy;
724 --
725 if csr_exists%found then
726 l_found := TRUE;
727 else
728 l_found := FALSE;
729 end if;
730 close csr_exists;
731 end if;
732 if p_rec.fk_value3 is not null and l_found then
733 open csr_exists(p_rec.fk_value3);
734 fetch csr_exists into l_dummy;
735 --
736 if csr_exists%found then
737 l_found := TRUE;
738 else
739 l_found := FALSE;
740 end if;
741 close csr_exists;
742 end if;
743 --
744 hr_utility.set_location('Leaving: parent_found', 10);
745 --
746 return l_found;
747 --
748 End parent_found;
749 --
750 Function chk_date_valid (p_rec in per_bil_shd.g_rec_type) return boolean is
751 --
752 cursor csr_get_datatype IS
753 select rt.data_type
754 from hr_summary_restriction_type rt,
755 hr_summary_valid_restriction vr,
756 hr_summary_restriction_usage rtu
757 where rt.restriction_type_id = vr.restriction_type_id
758 and rt.business_group_id = vr.business_group_id
759 and vr.valid_restriction_id = rtu.valid_restriction_id
760 and rtu.restriction_usage_id = p_rec.fk_value1;
761 --
762 l_datatype varchar2(10);
763 l_date date;
764 --
765 Begin
766 --
767 hr_utility.set_location('Entering: chk_valid_date', 5);
768 --
769 open csr_get_datatype;
770 fetch csr_get_datatype into l_datatype;
771 close csr_get_datatype;
772 if l_datatype = 'D' then
773 select to_date(p_rec.text_value1,'YYYY/MM/DD HH24:MI:SS')
774 into l_date
775 from dual;
776 end if;
777 --
778 hr_utility.set_location('Leaving: chk_valid_date', 10);
779 --
780 return(true);
781 Exception
782 When Others Then
783 hr_utility.set_location('Leaving: chk_valid_date', 11);
784 return(false);
785 End chk_date_valid;
786 --
787 end per_bil_shd;