1 package body pqh_utility as
2 /* $Header: pqutilty.pkb 120.7 2008/02/25 10:48:34 brsinha noship $ */
3 --
4 -- Declaring global variables
5 --
6 g_warning_no number(10) := 0;
7 g_next_warning_no number(10) := 0;
8 g_warnings_table warnings_tab;
9 --
10 g_rule_level_cd pqh_rule_sets.rule_level_cd%type;
11 --
12 g_package varchar2(33) := ' pqh_utility'; -- Global package name
13 --
14 g_query_date date := null;
15 --
16 function get_shared_type_name (
17 p_shared_type_id IN Number,
18 p_business_group_id IN Number ) return varchar2 IS
19 Cursor csr_shd_type IS
20 select stt.shared_type_name
21 from per_shared_types_tl stt,
22 per_shared_types st
23 where stt.shared_type_id = st.shared_type_id
24 and stt.language = userenv('lang')
25 and st.shared_type_id = p_shared_type_id
26 and (st.business_group_id = p_business_group_id or st.business_group_id is null );
27
28 l_shared_type_name per_shared_types_tl.shared_type_name%TYPE;
29
30 Begin
31
32 If ( p_shared_type_id IS NULL OR p_business_group_id IS NULL) Then
33 Return NULL;
34 End If;
35
36 Open csr_shd_type;
37 Fetch csr_shd_type into l_shared_type_name;
38 Close csr_shd_type;
39
40 Return l_shared_type_name;
41
42 End;
43
44 --
45 Procedure chk_message_name(p_application_id IN number,
46 p_message_name IN varchar2);
47 --
48 Procedure get_rule_set_id(p_application_id IN number,
49 p_message_name IN varchar2,
50 p_rule_set_id OUT nocopy number);
51
52 Procedure get_org_business_group_id(p_organization_id IN number,
53 p_business_group_id OUT nocopy number);
54
55 FUNCTION get_exist_org_level_cd
56 (p_business_group_id IN number,
57 p_ref_rule_set_id IN number,
58 p_organization_id IN number,
59 p_rule_level_cd OUT nocopy varchar2)
60 RETURN BOOLEAN;
61
62 Procedure get_rule_set_level_cd(p_rule_set_id IN number,
63 p_rule_level_cd OUT nocopy varchar2);
64
65 ---------------------------------get_message_level_cd-------------------------
66 --
67 Procedure get_message_level_cd
68 (p_organization_id IN number default null,
69 p_application_id IN number,
70 p_message_name IN varchar2,
71 p_rule_level_cd OUT nocopy varchar2) is
72 --
73 l_proc varchar2(72) := g_package||'get_message_level_cd';
74 --
75 l_rule_set_id pqh_rules.rule_set_id%type;
76 l_rule_level_cd pqh_rule_sets.rule_level_cd%type;
77 l_business_group_id hr_all_organization_units.business_group_id%type;
78 l_record_found BOOLEAN := FALSE;
79 l_no_rule_sets number(10);
80 --
81 Cursor csr_rule_set is
82 Select rule_set_id
83 From pqh_rules a
84 Where a.application_id = p_application_id
85 AND a.message_name = p_message_name;
86 --
87 Begin
88 --
89 hr_utility.set_location('Entering:'||l_proc, 5);
90 --
91 -- Chk if the message_name is valid in fnd_messages.
92 --
93 Chk_message_name(p_application_id => p_application_id,
94 p_message_name => p_message_name);
95 --
96 -- A rule may belong to more than one rule set . The most severe error
97 -- level associated with the rule set must be raised.
98 --
99 p_rule_level_cd := NULL;
100 l_no_rule_sets := 0;
101 --
102 Open csr_rule_set;
103 --
104 loop
105 --
106 --
107 Fetch csr_rule_set into l_rule_set_id;
108 --
109 If csr_rule_set%notfound then
110 --
111 Exit;
112 --
113 End if;
114 --
115 l_no_rule_sets := l_no_rule_sets + 1;
116 l_record_found := FALSE;
117 --
118 -- If organization is not provided , we will return the error level of the
119 -- seeded rule set to which this rule belongs
120 --
121 --
122 If p_organization_id IS NOT NULL then
123 --
124 -- Added on 17-jan-2001.
125 -- Initialising l_business_group_id to NULL.
126 --
127 l_business_group_id := NULL;
128
129 get_org_business_group_id(p_organization_id => p_organization_id,
130 p_business_group_id => l_business_group_id);
131 --
132 --
133 -- Changed on 17-jan-2001.
134 -- If organization is not a valid org , we will return the error level
135 -- for the seeded rule.
136 --
137 If l_business_group_id IS NOT NULL then
138 --
139 l_record_found := get_exist_org_level_cd
140 (p_business_group_id => l_business_group_id,
141 p_ref_rule_set_id => l_rule_set_id,
142 p_organization_id => p_organization_id,
143 p_rule_level_cd => l_rule_level_cd);
144 --
145 Else
146 l_record_found := FALSE;
147 End if;
148 --
149 End if;
150 --
151 -- Check if the rule has been configured . Else return the error level
152 -- of the seeded rule set.
153 --
154 If NOT l_record_found then
155 --
156 get_rule_set_level_cd( p_rule_set_id => l_rule_set_id,
157 p_rule_level_cd => l_rule_level_cd);
158 --
159 End if;
160 --
161 -- Return the most severe error level
162 --
163 If l_rule_level_cd = 'I' then
164 --
165 If p_rule_level_cd IS NULL then
166 --
167 p_rule_level_cd := l_rule_level_cd;
168 --
169 End if;
170 --
171 End if;
172 --
173 If l_rule_level_cd = 'W' then
174 --
175 If p_rule_level_cd IS NULL OR p_rule_level_cd = 'I' then
176 --
177 p_rule_level_cd := l_rule_level_cd;
178 --
179 End if;
180 --
181 End if;
182 --
183 If l_rule_level_cd = 'E' then
184 --
185 If p_rule_level_cd IS NULL OR p_rule_level_cd = 'I' OR p_rule_level_cd = 'W' then
186 --
187 p_rule_level_cd := l_rule_level_cd;
188 --
189 End if;
190 --
191 End if;
192 --
193 --
194 End loop;
195 --
196 Close csr_rule_set;
197 --
198 --
199 If l_no_rule_sets = 0 then
200 --
201 -- This is a valid message in fnd_messages but has not been configured.
202 -- Hence return message level as error.
203 --
204 p_rule_level_cd := 'E' ;
205 --
206 End if;
207 --
208 --
209 hr_utility.set_location(' Leaving:'||l_proc, 10);
210 exception
211 when others then
212 p_rule_level_cd := null;
213 End;
214 --
215 ----------------------------chk_message_name----------------------------------
216 --
217 Procedure chk_message_name(p_application_id IN number,
218 p_message_name IN varchar2) is
219 Cursor c1 is
220 Select null
221 From fnd_new_messages a
222 Where a.application_id = p_application_id
223 AND a.message_name = p_message_name;
224 --
225 l_proc varchar2(72) := g_package||'chk_message_name';
226 l_dummy varchar2(1);
227 --
228 Begin
229 hr_utility.set_location('Entering:'||l_proc, 5);
230 --
231 -- Check if the message_name supplied is a valid message in fnd_messages
232 --
233 Open c1;
234 --
235 Fetch c1 into l_dummy;
236 --
237 If c1%notfound then
238 Close c1;
239 hr_utility.set_message(8302,'PQH_INVALID_MESSAGE_NAME');
240 hr_utility.raise_error;
241 End if;
242 --
243 Close c1;
244 --
245 --
246 hr_utility.set_location(' Leaving:'||l_proc, 10);
247 End;
248 --
249 ---------------------------get_rule_set_id-------------------------------------
250 --
251 Procedure get_rule_set_id(p_application_id IN number,
252 p_message_name IN varchar2,
253 p_rule_set_id OUT nocopy number) is
254 Cursor c1 is
255 Select rule_set_id
256 From pqh_rules a
257 Where a.application_id = p_application_id
258 AND a.message_name = p_message_name;
259 --
260 l_proc varchar2(72) := g_package||'get_rule_set_id';
261 --
262 Begin
263 --
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 -- Fetch the rule_set_id , if the message name exists in pqh_rules.
267 --
268 Open c1;
269 --
270 Fetch c1 into p_rule_set_id;
271 --
272 Close c1;
273 --
274 hr_utility.set_location(' Leaving:'||l_proc, 10);
275 exception
276 when others then
277 p_rule_set_id := null;
278 End;
279 --
280 ------------------------get_org_business_group_id-----------------------------
281 --
282 Procedure get_org_business_group_id(p_organization_id IN number,
283 p_business_group_id OUT nocopy number) is
284 Cursor c1 is
285 /**
286 Select business_group_id
287 From per_organization_units a
288 Where a.organization_id = p_organization_id
289 And a.organization_id <> a.business_group_id;
290 **/
291 --
292 -- Added on 17-jan-2001.
293 --
294 -- Retreiving the business group of the organization. A rule set may be created
295 -- for a business group also . Also , we support cross business groups in
296 -- position transaction . Hence looking at hr_all_organization units to
297 -- get the organizations business group.
298 --
299 Select business_group_id
300 From hr_all_organization_units a
301 Where a.organization_id = p_organization_id;
302 --
303 l_proc varchar2(72) := g_package||'get_org_business_group_id';
304 --
305 Begin
306 hr_utility.set_location('Entering:'||l_proc, 5);
307 --
308 --
309 Open c1;
310 --
311 Fetch c1 into p_business_group_id;
312 --
313 --
314 -- Change made on 17-jan-2001. Will not raise error if the org is not valid.
315 --
316 /**
317 If c1%notfound then
318 Close c1;
319 hr_utility.set_message(8302,'PQH_INVALID_ORGANISATION');
320 hr_utility.raise_error;
321 End if;
322 **/
323 --
324 Close c1;
325 --
326 --
327 hr_utility.set_location(' Leaving:'||l_proc, 10);
328 exception
329 when others then
330 p_business_group_id := null;
331 End;
332 --
333 function get_error_level (p_organization_structure_id in number,
334 p_starting_organization_id IN number,
335 p_referenced_rule_set_id in number,
336 p_business_group_id in number) return varchar2 is
337 cursor c1 (p_organization_structure_id in number,
338 p_starting_organization_id in number,
339 p_referenced_rule_set_id in number,
340 p_business_group_id in number) is
341 Select rule_level_cd
342 From pqh_rule_sets
343 Where business_group_id = p_business_group_id
344 AND referenced_rule_set_id = p_referenced_rule_set_id
345 and starting_organization_id = P_STARTING_ORGANIZATION_ID
346 and organization_structure_id = p_organization_structure_id;
347 begin
348 for i in c1 (p_organization_structure_id => p_organization_structure_id,
349 p_starting_organization_id => p_starting_organization_id,
350 p_referenced_rule_set_id => p_referenced_rule_set_id,
351 p_business_group_id => p_business_group_id) loop
352 return i.rule_level_cd;
353 end loop;
354 return null;
355 end get_error_level;
356 --
357 -----------------------------get_exist_org_level_cd----------------------------
358 --
359 FUNCTION get_exist_org_level_cd
360 (p_business_group_id IN number,
361 p_ref_rule_set_id IN number,
362 p_organization_id IN number,
363 p_rule_level_cd OUT nocopy varchar2)
364 RETURN BOOLEAN is
365 --
366 l_organization_structure_id pqh_rule_sets.organization_structure_id%type;
367 l_org_structure_version_id per_org_structure_versions.org_structure_version_id%type;
368 --
369 l_hierarchy_level number(10);
370 l_parent_node pqh_rule_sets.organization_id%type;
371 --
372 Cursor csr_bg_config is
373 Select a.rule_level_cd
374 From pqh_rule_sets a
375 Where a.business_group_id = p_business_group_id
376 AND a.referenced_rule_set_id = p_ref_rule_set_id
377 AND a.organization_id is null
378 AND a.organization_structure_id IS NULL;
379 --
380 -- Check if the rule has been configured specifically for this organization
381 -- alone
382 --
383 Cursor csr_org_config is
384 Select a.rule_level_cd
385 From pqh_rule_sets a
386 Where a.business_group_id = p_business_group_id
387 AND a.referenced_rule_set_id = p_ref_rule_set_id
388 AND a.organization_id = p_organization_id
389 AND a.organization_structure_id IS NULL;
390 --
391 -- The foll cursor selects all distinct org structures that have been
392 -- configured for the passed referenced rule set and business group . Ideally
393 -- this cursor should return one record only
394 -- We do not want to select configuartions made for induvidual organizations.
395 --
396 Cursor csr_org_struct is
397 Select distinct a.organization_structure_id
398 From pqh_rule_sets a
399 Where a.business_group_id = p_business_group_id
400 AND a.referenced_rule_set_id = p_ref_rule_set_id
401 and a.organization_structure_id IS NOT NULL;
402 --
403 -- Check if the passed organization is within the above structure and
404 -- return its parents if any , if the organization belongs to the org
405 -- structure
406 --
407 Cursor csr_parent_nodes(P_ORGANIZATION_ID in number ,
408 P_ORG_STRUCTURE_VERSION_ID in number) is
409 Select level,organization_id_parent
410 From per_org_structure_elements
411 where org_structure_version_id = p_org_structure_version_id
412 connect by prior organization_id_parent = organization_id_child
413 and ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
414 start with ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
415 and organization_id_child = P_ORGANIZATION_ID
416 UNION
417 Select 0,p_organization_id
418 from dual
419 order by 1 asc;
420 --
421 l_proc varchar2(72) := g_package||'get_exist_org_level_cd';
422 l_oh_rule boolean := FALSE;
423 l_rule_level_cd varchar2(30);
424 --
425 Begin
426 --
427 hr_utility.set_location('Entering:'||l_proc, 5);
428 --
429 -- Check if the rule has been configured for the organization alone
430 --
431 Open csr_org_config;
432 --
433 Fetch csr_org_config into p_rule_level_cd;
434 --
435 If csr_org_config%found then
436 hr_utility.set_location('Rule is defined for the org'||l_proc, 10);
437 Close csr_org_config;
438 RETURN TRUE;
439 End if;
440 --
441 Close csr_org_config;
442 --
443 --
444 -- Check if the rule has been configured for any parent organization
445 -- of the passed organization
446 --
447 Open csr_org_struct;
448 --
449 Loop
450 --
451 Fetch csr_org_struct into l_organization_structure_id;
452 --
453 If csr_org_struct%notfound then
454 exit;
455 End if;
456 --
457 -- get the latest version id for the organization structure.
458 --
459 get_org_structure_version_id
460 (p_org_structure_id => l_organization_structure_id,
461 p_org_structure_version_id=> l_org_structure_version_id);
462 --
463 -- Select all parent nodes for the organization
464 --
465 -- severest rule is being computed here if we get E, we go out of
466 -- this loop immediately else, we loop thru all the combinations
467 Open csr_parent_nodes(p_organization_id => p_organization_id,
468 p_org_structure_version_id => l_org_structure_version_id);
469 --
470 loop
471 --
472 Fetch csr_parent_nodes into l_hierarchy_level,l_parent_node;
473 --
474 hr_utility.set_location('node is '||l_parent_node, 30);
475 hr_utility.set_location('hierarchy_level is '||l_hierarchy_level, 40);
476 If csr_parent_nodes%notfound then
477 exit;
478 End if;
479 --
480 -- Check if the rule set has been configured for the parent node fetched
481 --
482 l_rule_level_cd := get_error_level (p_organization_structure_id => l_organization_structure_id,
483 p_starting_organization_id => l_parent_node,
484 p_business_group_id => p_business_group_id,
485 p_referenced_rule_set_id => p_ref_rule_set_id);
486 if l_rule_level_cd is not null then
487 hr_utility.set_location('found a rule for node'||l_parent_node||' and rule is'||l_rule_level_cd, 30);
488 l_oh_rule := TRUE;
489 if l_rule_level_cd ='E' then
490 p_rule_level_cd := 'E' ;
491 Close csr_org_struct;
492 Close csr_parent_nodes;
493 RETURN TRUE;
494 else
495 hr_utility.set_location('did not found a E'||l_proc, 40);
496 -- severest rule not found so far, should go for next hierarchy, if any
497 if nvl(p_rule_level_cd,'I') ='I' then
498 p_rule_level_cd := l_rule_level_cd;
499 end if;
500 exit;
501 -- lowest rule defined for this org-hier is pulled, we don't need to go thru
502 -- this hierachy any longer.
503 end if;
504 end if;
505 --
506 End loop;
507 --
508 Close csr_parent_nodes;
509 --
510 End loop;
511 --
512 Close csr_org_struct;
513 if l_oh_rule then
514 hr_utility.set_location('OH rule is being returned'||p_rule_level_cd, 50);
515 RETURN TRUE;
516 end if;
517 --
518 --bg check
519 --
520 Open csr_bg_config;
521 --
522 Fetch csr_bg_config into p_rule_level_cd;
523 --
524 If csr_bg_config%found then
525 hr_utility.set_location('BG rule is being returned'||p_rule_level_cd, 50);
526 Close csr_bg_config;
527 RETURN TRUE;
528 End if;
529 --
530 Close csr_bg_config;
531 hr_utility.set_location(' Leaving:'||l_proc, 10);
532 RETURN FALSE;
533 --
534 exception
535 when others then
536 p_rule_level_cd := null;
537 End;
538 --
539 --
540 -- ----------------------------------------------------------------------------
541 -- |------< get_org_structure_version_id >------|
542 -- ----------------------------------------------------------------------------
543 --
544 -- Description
545 -- This procedure is used to check if there is a overlap of entered
546 -- org structure and existing org structures for the Business Group
547 -- and Referenced rule set
548 --
549 Procedure get_org_structure_version_id(p_org_structure_id IN NUMBER,
550 p_org_structure_version_id OUT nocopy NUMBER) is
551 Cursor c1 is
552 Select org_structure_version_id
553 From per_org_structure_versions
554 Where organization_structure_id = p_org_structure_id
555 AND version_number =
556 (select max(version_number)
557 From per_org_structure_versions
558 Where organization_structure_id = p_org_structure_id);
559 --
560 l_proc varchar2(72) := g_package||'g_org_structure_version_id';
561 --
562 Begin
563 hr_utility.set_location('Entering:'||l_proc, 5);
564 --
565 Open c1;
566 Fetch c1 into p_org_structure_version_id;
567 If c1%notfound then
568 hr_utility.set_message(8302, 'PQH_ORG_STRUCT_VER_NOT_FOUND');
569 hr_utility.raise_error;
570 End if;
571 Close c1;
572 --
573 --
574 hr_utility.set_location(' Leaving:'||l_proc, 10);
575 exception
576 when others then
577 p_org_structure_version_id := null;
578 End;
579 --
580 -- ----------------------------------------------------------------------------
581 -- |------< get_rule_set_level_cd>------|
582 -- ----------------------------------------------------------------------------
583 --
584 -- Description
585 -- This procedure returns the level_cd for the rule_set_id
586 --
587 Procedure get_rule_set_level_cd(p_rule_set_id IN number,
588 p_rule_level_cd OUT nocopy varchar2) is
589 --
590 Cursor c1 is
591 Select rule_level_cd
592 From pqh_rule_sets a
593 Where a.rule_set_id = p_rule_set_id;
594 --
595 l_proc varchar2(72) := g_package||'get_rule_set_level_cd';
596 --
597 Begin
598 --
599 hr_utility.set_location('Entering:'||l_proc, 5);
600 --
601 Open c1;
602 Fetch c1 into p_rule_level_cd;
603 If c1%NOTFOUND then
604 p_rule_level_cd := NULL;
605 Close c1;
606 hr_utility.set_message(8302, 'PQH_INVALID_RULE_SET_ID');
607 hr_utility.raise_error;
608 End if;
609 Close c1;
610 --
611 hr_utility.set_location(' Leaving:'||l_proc, 10);
612 --
613 exception
614 when others then
615 p_rule_level_cd := null;
616 End get_rule_set_level_cd;
617 --
618 -- ----------------------------------------------------------------------------
619 -- |------< get_language_code>------|
620 -- ----------------------------------------------------------------------------
621 --
622 -- Description
623 -- This procedure returns the language_code
624 --
625 Procedure get_language_code( p_language_code OUT nocopy varchar2) is
626 --
627 Cursor c1 is
628 Select userenv('LANG')
629 From dual;
630 --
631 l_proc varchar2(72) := g_package||'get_language_code';
632 --
633 Begin
634 hr_utility.set_location('Entering:'||l_proc, 5);
635 --
636 --
637 Open c1;
638 Fetch c1 into p_language_code;
639 If c1%NOTFOUND then
640 p_language_code := 'US';
641 End if;
642 Close c1;
643 --
644 hr_utility.set_location(' Leaving:'||l_proc, 10);
645 --
646 exception
647 when others then
648 p_language_code := null;
649 End get_language_code;
650 --
651 --
652 Procedure init_query_date is
653 --
654 l_proc varchar2(72) := g_package||'init_query_date';
655 --
656 Begin
657 hr_utility.set_location('Entering:'||l_proc, 5);
658 --
659 g_query_date := null;
660 --
661 hr_utility.set_location('Leaving:'||l_proc, 10);
662 --
663 end;
664 --
665 Procedure set_query_date(p_effective_date in date) is
666 --
667 l_proc varchar2(72) := g_package||'set_query_date';
668 --
669 Begin
670 hr_utility.set_location('Entering:'||l_proc, 5);
671 --
672 g_query_date := p_effective_date;
673 --
674 hr_utility.set_location('Leaving:'||l_proc, 10);
675 --
676 end;
677 --
678 --
679 function get_query_date return date is
680 --
681 l_proc varchar2(72) := g_package||'get_query_date';
682 --
683 Begin
684 hr_utility.set_location('Entering:'||l_proc, 5);
685 --
686 --
687 return nvl(g_query_date, trunc(sysdate));
688 --
689 end;
690 --
691 -- ----------------------------------------------------------------------------
692 -- |------< init_warnings_table>------|
693 -- ----------------------------------------------------------------------------
694 --
695 Procedure init_warnings_table is
696 --
697 dummy_table warnings_tab;
698 l_proc varchar2(72) := g_package||'init_warnings_table';
699 --
700 Begin
701 hr_utility.set_location('Entering:'||l_proc, 5);
702 --
703 --
704 g_warning_no := 0;
705 g_next_warning_no := 0;
706 --
707 --Tried to assign NULL to the table . Does not work
708 --Hence assigned another dummy table
709 --g_warnings_table := NULL;
710 --
711 -- g_warnings_table := dummy_table;
712 g_warnings_table.DELETE;
713 --
714 hr_utility.set_location('Leaving:'||l_proc, 10);
715 --
716 end;
717 --
718 -- ----------------------------------------------------------------------------
719 -- |------< insert_warning>------|
720 -- ----------------------------------------------------------------------------
721 --
722 Procedure insert_warning(p_warnings_rec IN warnings_rec) is
723 --
724 l_new_warning boolean := TRUE;
725 cnt number(10);
726 l_proc varchar2(72) := g_package||'insert_warning';
727 --
728 Begin
729 hr_utility.set_location('Entering:'||l_proc, 5);
730 --
731 -- Check if this warning is already in table .
732 --
733 If g_warning_no > 0 then
734 --
735 For cnt in g_warnings_table.first..g_warnings_table.last loop
736 --
737 If p_warnings_rec.message_text = g_warnings_table(cnt).message_text then
738 --
739 l_new_warning := false;
740 Exit;
741 --
742 End if;
743 --
744 End loop;
745 --
746 End if;
747 --
748 -- Insert new warning
749 --
750 If l_new_warning then
751 --
752 -- Increment the warning no
753 --
754 g_warning_no := g_warning_no + 1;
755 --
756 -- Insert the input record into the next row in the warnings table.
757 --
758 g_warnings_table(g_warning_no).message_text := p_warnings_rec.message_text;
759 --
760 End if;
761 --
762 hr_utility.set_location('Leaving:'||l_proc, 10);
763 --
764 End;
765 --
766 -- ----------------------------------------------------------------------------
767 -- |------< get_next_warning>------|
768 -- ----------------------------------------------------------------------------
769 --
770 Procedure get_next_warning(p_warnings_rec OUT nocopy warnings_rec) is
771 --
772 l_proc varchar2(72) := g_package||'get_next_warning';
773 --
774 Begin
775 hr_utility.set_location('Entering:'||l_proc, 5);
776 --
777 g_next_warning_no := g_next_warning_no + 1;
778 --
779 -- Raise error if the next warning no exceeds the actual number of warnings
780 -- in the table.
781 --
782 If g_next_warning_no > g_warning_no then
783 --
784 hr_utility.set_message(8302,'PQH_INVALID_WARNING_NO');
785 hr_utility.raise_error;
786 --
787 End if;
788 --
789 -- Return the next warning.
790 --
791 p_warnings_rec.message_text := g_warnings_table(g_next_warning_no).message_text ;
792 --
793 hr_utility.set_location('Leaving:'||l_proc, 10);
794 --
795 exception
796 when others then
797 p_warnings_rec := null;
798 End;
799 --
800 -- ----------------------------------------------------------------------------
801 -- |------< get_all_warnings>------|
802 -- ----------------------------------------------------------------------------
803 --
804 Procedure get_all_warnings(p_warnings_tab OUT nocopy warnings_tab,
805 p_no_warnings OUT nocopy number) is
806 --
807 l_proc varchar2(72) := g_package||'get_all_warnings';
808 --
809 Begin
810 hr_utility.set_location('Entering:'||l_proc, 5);
811 --
812 --
813 p_warnings_tab := g_warnings_table;
814 --
815 --
816 p_no_warnings := g_warning_no;
817 --
818 hr_utility.set_location('Leaving:'||l_proc, 10);
819 --
820 exception
821 when others then
822 p_no_warnings := null;
823 End;
824
825 -- Rewriting hr_utility functions
826
827 -----------------------------------set_message --------------------------------
828 --
829 -- NAME
830 -- set_message
831 -- DESCRIPTION
832 -- Calls FND_MESSAGE.SET_NAME and sets the message name and application id as
833 -- package globals.
834 --
835 Procedure set_message (applid in number,
836 l_message_name in varchar2,
837 l_organization_id in number default NULL) is
838 --
839 l_rule_level_cd pqh_rule_sets.rule_level_cd%type;
840 --
841 --
842 l_proc varchar2(72) := g_package||'set_message';
843 --
844 begin
845 hr_utility.set_location('Entering:'||l_proc, 5);
846 --
847 --
848 --
849 get_message_level_cd(p_application_id => applid,
850 p_message_name => l_message_name,
851 p_organization_id=> l_organization_id,
852 p_rule_level_cd => l_rule_level_cd);
853 --
854 g_rule_level_cd := l_rule_level_cd;
855 --
856 hr_utility.set_message(applid,l_message_name);
857 --
858 --
859 hr_utility.set_location('Leaving:'||l_proc, 10);
860 --
861 end set_message;
862 --
863 -----------------------------------set_warning_message --------------------------------
864 --
865 -- NAME
866 -- set_warning_message
867 -- DESCRIPTION
868 -- Calls FND_MESSAGE.SET_NAME and sets the message name and application id as
869 -- package globals. Also sets the global rule level code as warning.
870 -- If the g_rule_level_cd is warning error is not thrown, only a warning is
871 -- shown.
872 --
873 Procedure set_warning_message (applid in number,
874 l_message_name in varchar2) is
875 --
876 --
877 --
878 l_proc varchar2(72) := g_package||'set_message';
879 --
880 begin
881 hr_utility.set_location('Entering:'||l_proc, 5);
882 --
883 --
884 --
885 g_rule_level_cd := 'W';
886 --
887 hr_utility.set_message(applid,l_message_name);
888 --
889 --
890 hr_utility.set_location('Leaving:'||l_proc, 10);
891 --
892 end set_warning_message;
893 --
894 ------------------------------ set_message_token ------------------------------
895 --
896 -- NAME
897 -- set_message_token
898 -- DESCRIPTION
899 -- Sets message token. Just calls AOL routine.
900 --
901 procedure set_message_token (l_token_name in varchar2,
902 l_token_value in varchar2) is
903 --
904 l_proc varchar2(72) := g_package||'set_message_token';
905 --
906 begin
907 hr_utility.set_location('Entering:'||l_proc, 5);
908 --
909 --
910 hr_utility.set_message_token(l_token_name,l_token_value);
911 --
912 --
913 hr_utility.set_location('Leaving:'||l_proc, 10);
914 --
915 end set_message_token;
916
917 ------------------------------ set_message_token ------------------------------
918 -- NAME
919 -- set_message_token
920 --
921 -- DESCRIPTION
922 -- Overloaded: Sets up a translated message token
923 -- Note that the application id passed a parameter is ignored.The FND_MESSAGE
924 -- routine uses the application of the last message that was set.
925 --
926 procedure set_message_token (l_applid in number,
927 l_token_name in varchar2,
928 l_token_message in varchar2) is
929 --
930 l_proc varchar2(72) := g_package||'set_message_token';
931 --
932 begin
933 hr_utility.set_location('Entering:'||l_proc, 5);
934 --
935 --
936 hr_utility.set_message_token(l_applid,l_token_name,l_token_message);
937 --
938 --
939 hr_utility.set_location('Leaving:'||l_proc, 10);
940 --
941 end set_message_token;
942 ------------------------------raise_error---------------------------------------
943 -- NAME
944 -- raise_error
945 --
946 -- DESCRIPTION
947 -- Raises error based on g_rule_level_cd
948 --
949 Procedure raise_error is
950 --
951 l_warnings_rec warnings_rec;
952 --
953 l_proc varchar2(72) := g_package||'raise_error';
954 --
955 begin
956 hr_utility.set_location('Entering:'||l_proc, 5);
957 --
958 --
959 If g_rule_level_cd = 'E' then
960 hr_utility.raise_error;
961 Elsif g_rule_level_cd = 'W' then
962 l_warnings_rec.message_text := hr_utility.get_message;
963 insert_warning(p_warnings_rec => l_warnings_rec);
964 End if;
965 --
966 --
967 hr_utility.set_location('Leaving:'||l_proc, 10);
968 --
969 End raise_error;
970 --
971 --
972 -------------------------------------------------------------------------------
973 -- decode_assignment_name
974 -------------------------------------------------------------------------------
975 --
976 -- Description : Common function to return assignment_name given assignment_id
977 --
978 FUNCTION DECODE_ASSIGNMENT_NAME(p_assignment_id in number)
979 Return VARCHAR2 is
980
981 ret_assignment_name varchar2(500);
982 --
983 l_proc varchar2(72) := g_package||'decode_assignment_name';
984 --
985 Cursor assignment_name is
986 Select substr(ppl.full_name||'('||hr_general.decode_lookup('PQH_GEN_LOV','EMP_
987 NUM')||'='||ppl.employee_number||')',1,240)
988 from per_all_assignments_f asg , per_all_people_f ppl,fnd_sessions ses
989 where asg.assignment_id = p_assignment_id
990 and asg.person_id = ppl.person_id
991 and ses.session_id = userenv('sessionid')
992 and ses.effective_date between ppl.effective_start_date and ppl.effective_end_date
993 and ses.effective_date between asg.effective_start_date and asg.effective_end_date;
994 Begin
995 hr_utility.set_location('Entering:'||l_proc, 5);
996 --
997 Open assignment_name;
998 Fetch assignment_name into ret_assignment_name;
999 Close assignment_name;
1000 --
1001 hr_utility.set_location('Leaving:'||l_proc, 10);
1002 --
1003 Return ret_assignment_name;
1004 End;
1005 --
1006 --
1007 function get_message_type_cd return varchar2 is
1008 l_proc varchar2(72) := g_package||'get_message_type_cd';
1009 --
1010 begin
1011 hr_utility.set_location('Entering:'||l_proc, 5);
1012 --
1013 return g_rule_level_cd;
1014 --
1015 hr_utility.set_location('Leaving:'||l_proc, 10);
1016 end;
1017 --
1018 --
1019 function get_message return varchar2 is
1020 l_proc varchar2(72) := g_package||'get_message';
1021 begin
1022 hr_utility.set_location('Entering:'||l_proc, 5);
1023 --
1024 return hr_utility.get_message;
1025 --
1026 hr_utility.set_location('Leaving:'||l_proc, 10);
1027 end;
1028 --
1029 --
1030 procedure save_point is
1031 begin
1032 savepoint a;
1033 end;
1034 --
1035 procedure roll_back is
1036 begin
1037 rollback to a;
1038 end;
1039 --
1040 --
1041 procedure set_session_date(p_date date) is
1042 PRAGMA AUTONOMOUS_TRANSACTION;
1043 l_commit number;
1044 begin
1045 dt_fndate.change_ses_date(trunc(p_date),l_commit);
1046 if l_commit=1 then
1047 commit;
1048 end if;
1049 end;
1050 --
1051 -- ------------------------------------------------------------------------
1052 --
1053 FUNCTION get_pos_budget_values(p_position_id in number,
1054 p_period_start_dt in date,
1055 p_period_end_dt in date,
1056 p_unit_of_measure in varchar2)
1057 RETURN number is
1058 --
1059 l_business_group_id hr_all_positions_f.business_group_id%type;
1060 l_position_name hr_all_positions_f.name%type := NULL;
1061 l_pbv number(27,2);
1062 --
1063 Cursor csr_pos is
1064 Select name,business_group_id
1065 From hr_all_positions_f_vl
1066 Where position_id = p_position_id;
1067 --
1068 l_proc varchar2(72) := g_package||'get_pos_budget_values';
1069 --
1070 Begin
1071 --
1072 hr_utility.set_location('Entering:'||l_proc, 5);
1073 --
1074 --
1075 -- Obtain the business group and position_name of the position.
1076 --
1077 Open csr_pos;
1078 Fetch csr_pos into l_position_name, l_business_group_id;
1079 Close csr_pos;
1080 --
1081 -- Call function that returns commitment.
1082 --
1083 l_pbv := hr_discoverer.get_actual_budget_values
1084 (p_unit => p_unit_of_measure,
1085 p_bus_group_id => l_business_group_id ,
1086 p_organization_id => NULL ,
1087 p_job_id => NULL ,
1088 p_position_id => p_position_id ,
1089 p_grade_id => NULL ,
1090 p_start_date => p_period_start_dt ,
1091 p_end_date => p_period_end_dt ,
1092 p_actual_val => NULL
1093 );
1094 --
1095 hr_utility.set_location('Leaving:'||l_proc, 10);
1096 --
1097 RETURN l_pbv;
1098 --
1099 Exception When others then
1100 --
1101 hr_utility.set_location('Exception:'||l_proc, 15);
1102 raise;
1103 --
1104 End;
1105 --
1106 Procedure get_all_unit_desc(p_worksheet_detail_id in number,
1107 p_unit1_desc out nocopy varchar2,
1108 p_unit2_desc out nocopy varchar2,
1109 p_unit3_desc out nocopy varchar2) is
1110 cursor c1 is select budget_unit1_id,budget_unit2_id,budget_unit3_id
1111 from pqh_budgets bgt,pqh_worksheets wks,
1112 pqh_worksheet_details wkd
1113 where wkd.worksheet_id = wks.worksheet_id
1114 and wks.budget_id = bgt.budget_id
1115 and wkd.worksheet_detail_id = p_worksheet_detail_id;
1116 l_budget_unit1_id pqh_budgets.budget_unit1_id%type;
1117 l_budget_unit2_id pqh_budgets.budget_unit1_id%type;
1118 l_budget_unit3_id pqh_budgets.budget_unit1_id%type;
1119 begin
1120 if p_worksheet_detail_id is not null then
1121 begin
1122 open c1;
1123 fetch c1 into
1124 l_budget_unit1_id,l_budget_unit2_id,l_budget_unit3_id;
1125 close c1;
1126 exception
1127 when others then
1128 hr_utility.set_message(8302,'PQH_INVALID_WKD_PASSED');
1129 hr_utility.raise_error;
1130 end;
1131 p_unit1_desc := get_unit_desc(l_budget_unit1_id);
1132 if l_budget_unit2_id is not null then
1133 p_unit2_desc := get_unit_desc(l_budget_unit2_id);
1134 else
1135 p_unit2_desc := null;
1136 end if;
1137 if l_budget_unit3_id is not null then
1138 p_unit3_desc := get_unit_desc(l_budget_unit3_id);
1139 else
1140 p_unit3_desc := null;
1141 end if;
1142 else
1143 hr_utility.set_message(8302,'PQH_INVALID_WKD_PASSED');
1144 hr_utility.raise_error;
1145 end if;
1146 exception
1147 when others then
1148 p_unit1_desc := null;
1149 p_unit2_desc := null;
1150 p_unit3_desc := null;
1151 hr_utility.set_message(8302,'PQH_INVALID_WKD_PASSED');
1152 hr_utility.raise_error;
1153 end get_all_unit_desc;
1154
1155
1156 function get_unit_desc(p_unit_id in number) return varchar2 is
1157 cursor c1 is select shared_type_name
1158 from per_shared_types_vl
1159 where lookup_type ='BUDGET_MEASUREMENT_TYPE'
1160 and shared_type_id = p_unit_id;
1161 l_shared_type_name per_shared_types_vl.shared_type_name%type;
1162 begin
1163 open c1;
1164 fetch c1 into l_shared_type_name;
1165 close c1;
1166 return l_shared_type_name;
1167 exception
1168 when others then
1169 hr_utility.set_message(8302,'PQH_INVALID_UNIT_ENTERED');
1170 hr_utility.raise_error;
1171 end get_unit_desc;
1172 --
1173 function chk_pos_pending_txns(p_position_id in number, p_position_transaction_id in number default null) return varchar2 is
1174 l_count_pending_txns number:=0;
1175 --
1176 cursor c_count_pending_txns(p_position_id number) is
1177 select count(*)
1178 from pqh_position_transactions ptx
1179 where position_id = p_position_id
1180 and nvl(ptx.transaction_status,'PENDING') in ('APPROVED','SUBMITTED','PENDING')
1181 and position_transaction_id <> nvl(p_position_transaction_id, -1);
1182 --
1183 begin
1184 open c_count_pending_txns(p_position_id);
1185 fetch c_count_pending_txns into l_count_pending_txns;
1186 close c_count_pending_txns;
1187 if l_count_pending_txns <> 0 then
1188 return 'Y';
1189 else
1190 return 'N';
1191 end if;
1192 return l_count_pending_txns;
1193 end;
1194 --
1195 --
1196 function get_attribute_name(p_table_alias in varchar2, p_column_name in varchar2) return varchar2 is
1197 l_attribute_name varchar2(100);
1198 --
1199 cursor c_attributes(p_table_alias in varchar2, p_column_name in varchar2) is
1200 select attribute_name
1201 from pqh_table_route trt, pqh_attributes_vl att
1202 where trt.table_route_id = att.master_table_route_id
1203 and trt.table_alias = p_table_alias
1204 and att.column_name = p_column_name;
1205 --
1206 begin
1207 open c_attributes(p_table_alias, p_column_name);
1208 fetch c_attributes into l_attribute_name;
1209 close c_attributes;
1210 return l_attribute_name;
1211 end;
1212 --
1213 procedure change_ptx_txn_status(
1214 p_position_transaction_id number,
1215 p_transaction_status varchar2,
1216 p_effective_date date default sysdate) is
1217 --
1218 l_object_version_number number;
1219 l_review_flag pqh_position_transactions.review_flag%TYPE; -- bug 6112935
1220 --
1221 cursor c_position_transactions(p_position_transaction_id number) is
1222 select object_version_number
1223 from pqh_position_transactions ptx
1224 where position_transaction_id = p_position_transaction_id;
1225 --
1226 begin
1227 open c_position_transactions(p_position_transaction_id);
1228 fetch c_position_transactions into l_object_version_number;
1229 close c_position_transactions;
1230 --lock the position transaction.
1231 pqh_ptx_shd.lck
1232 (
1233 p_position_transaction_id => p_position_transaction_id
1234 ,p_object_version_number => l_object_version_number
1235 );
1236
1237 hr_utility.set_location('p_status '||p_transaction_status||'l_review_flag '||l_review_flag, 15);
1238 -- If condition added for Bug 6112905 / Modified for bug 6524175
1239 if p_transaction_status in ('REJECT','TERMINATE','SUBMITTED') then
1240 l_review_flag := 'N';
1241 end if;
1242 hr_utility.set_location('p_status '||p_transaction_status||'l_review_flag '||l_review_flag, 25);
1243
1244 -- Update the position transaction
1245 pqh_position_transactions_api.update_position_transaction
1246 (
1247 p_validate => false
1248 ,p_position_transaction_id => p_position_transaction_id
1249 ,p_object_version_number => l_object_version_number
1250 ,p_effective_date => p_effective_date
1251 ,p_transaction_status => p_transaction_status -- bug 6112905
1252 ,p_review_flag => l_review_flag -- bug 6112905
1253 );
1254 --
1255 end;
1256 --
1257 --
1258 function position_exists(p_position_id number, p_effective_date date) return varchar2 is
1259 l_dummy varchar2(10);
1260 cursor c_position is
1261 select 'x'
1262 from hr_all_positions_f
1263 where position_id = p_position_id
1264 and p_effective_date between effective_start_date and effective_end_date;
1265 begin
1266 open c_position;
1267 fetch c_position into l_dummy;
1268 if c_position%found then
1269 close c_position;
1270 return 'Y';
1271 else
1272 close c_position;
1273 return 'N';
1274 end if;
1275 close c_position;
1276 end;
1277 --
1278 function position_start_date(p_position_id number) return date is
1279 l_date date;
1280 cursor c_position is
1281 select min(effective_start_date)
1282 from hr_all_positions_f
1283 where position_id = p_position_id;
1284 begin
1285 open c_position;
1286 fetch c_position into l_date;
1287 if c_position%found then
1288 close c_position;
1289 return l_date;
1290 else
1291 close c_position;
1292 return null;
1293 end if;
1294 close c_position;
1295 end;
1296 --
1297 --
1298 function decode_grade_rule (
1299 --
1300 p_grade_rule_id number, p_type varchar2) return varchar2 is
1301 --
1302 cursor csr_grade_rule is
1303 select value, minimum, maximum, mid_value
1304 from pay_grade_rules
1305 where grade_rule_id = p_grade_rule_id;
1306 --
1307 l_point_value number;
1308 l_min number;
1309 l_mid number;
1310 l_max number;
1311 --
1312 begin
1313 --
1314 -- Only open the cursor if the parameter is going to retrieve anything
1315 --
1316 if p_grade_rule_id is not null then
1317 --
1318 open csr_grade_rule;
1319 fetch csr_grade_rule into l_point_value, l_min, l_mid, l_max;
1320 close csr_grade_rule;
1321 --
1322 end if;
1323 if p_type = 'VALUE' then
1324 return l_point_value;
1325 elsif p_type = 'MIN' then
1326 return l_min;
1327 elsif p_type = 'MID' then
1328 return l_mid;
1329 elsif p_type = 'MAX' then
1330 return l_max;
1331 end if;
1332 return -1;
1333 end decode_grade_rule;
1334 --
1335 -----------------------------------------------------------------------------
1336 --
1337 -- Procedure to check if a valid value set id is passed and to return its
1338 -- values
1339 --
1340 Procedure chk_if_valid_value_set( p_value_set_id in number,
1341 p_value_set out nocopy g_value_set%type,
1342 p_error_status out nocopy number)
1343 is
1344 --
1345 -- The foll cursor returns values of a value set id.
1346 --
1347 Cursor csr_validation_type is
1348 Select *
1349 from fnd_flex_value_sets
1350 where flex_value_set_id = p_value_set_id;
1351 --
1352 l_validation_type fnd_flex_value_sets.validation_type%type;
1353 l_map varchar2(2000);
1354 --
1355 Begin
1356 --
1357 -- Check if a valid value set id is passed and fetch its values.
1358 --
1359 Open csr_validation_type;
1360 Fetch csr_validation_type into p_value_set;
1361 If csr_validation_type%notfound then
1362 --
1363 -- Invalid value set id
1364 --
1365 p_error_status := 1;
1366 Else
1367 p_error_status := 0;
1368 End if;
1369 Close csr_validation_type;
1370 --
1371 exception
1372 when others then
1373 p_value_set := null;
1374 p_error_status := null;
1375 End;
1376 --
1377 ----------------------------------------------------------------------------
1378 --
1379 -- Procedure to return the format mask for a number field given the size
1380 -- and its precision.
1381 --
1382 Procedure get_num_format_mask(p_size in number,
1383 p_precision in number,
1384 p_format_mask out nocopy varchar2) is
1385 --
1386 l_decimal varchar2(50) := NULL;
1387 --
1388 Begin
1389 --
1390 p_format_mask := NULL;
1391 --
1392 -- If there is a decimal part, then form the decimal part firt.
1393 --
1394 If p_precision > 0 then
1395 --
1396 l_decimal := '.';
1397 -- Form decimal part.
1398 For i in 1..p_precision loop
1399 l_decimal := l_decimal||'9';
1400 End loop;
1401 --
1402 -- The size of the field must be greater than the precision + 1
1403 --
1404 If p_size > p_precision+1 then
1405 --
1406 -- Form the format mask for the integral part.
1407 --
1408 For i in 1..(p_size - (p_precision + 1)) loop
1409 p_format_mask := p_format_mask||'9';
1410 End loop;
1411 --
1412 -- Concatenate with the mask for the decimal part.
1413 --
1414 p_format_mask := p_format_mask || l_decimal;
1415 Else
1416 p_format_mask := l_decimal;
1417 End if;
1418 Else
1419 --
1420 -- If there is no decimal part, then
1421 --
1422 If p_size > 0 then
1423 --
1424 -- Create the format mask for the integral part alone.
1425 --
1426 For i in 1..p_size loop
1427 p_format_mask := p_format_mask ||'9';
1428 End loop;
1429 End if;
1430 --
1431 End if;
1432 --
1433 exception
1434 when others then
1435 p_format_mask := null;
1436 End;
1437 --
1438 ----------------------------------------------------------------------------
1439 --
1440 -- Given the value set id , the item returns the corresponding sql statement
1441 -- / its format .
1442 --
1443 Procedure get_valueset(p_value_set_id in number,
1444 p_validation_type out nocopy varchar2,
1445 p_num_format_mask out nocopy varchar2,
1446 p_min_value out nocopy varchar2,
1447 p_max_value out nocopy varchar2,
1448 p_sql_stmt out nocopy varchar2,
1449 p_error_status out nocopy number) is
1450 --
1451 l_map varchar2(2000);
1452 l_value_set g_value_set%type;
1453 l_error_status number(10);
1454 --
1455 Begin
1456 --
1457 -- Check if a valid value set id is passed and fetch its validation type.
1458 --
1459 chk_if_valid_value_set( p_value_set_id => p_value_set_id,
1460 p_value_set => l_value_set,
1461 p_error_status => l_error_status);
1462 --
1463 If l_error_status <> 0 then
1464 --
1465 -- Invalid value set id
1466 --
1467 p_error_status := 1;
1468 p_sql_stmt := NULL;
1469 --
1470 Else
1471 p_validation_type := l_value_set.validation_type;
1472
1473 If l_value_set.validation_type = 'F' then
1474 --
1475 -- Call the fnd function that returns the sql stmt;
1476 --
1477 fnd_flex_val_api.get_table_vset_select
1478 (
1479 p_value_set_id => p_value_set_id,
1480 x_select => p_sql_stmt,
1481 x_mapping_code => l_map,
1482 x_success => p_error_status);
1483 --
1484 p_min_value := NULL;
1485 p_max_value := NULL;
1486 p_num_format_mask := NULL;
1487 --
1488 Elsif l_value_set.validation_type = 'N' then
1489 --
1490 -- The validation type is none
1491 --
1492 p_min_value := l_value_set.minimum_value;
1493 p_max_value := l_value_set.maximum_value;
1494 --
1495 -- If number , return its format mask.
1496 --
1497 If l_value_set.format_type = 'N' then
1498 --
1499 get_num_format_mask
1500 ( p_size => l_value_set.maximum_size,
1501 p_precision => l_value_set.number_precision,
1502 p_format_mask => p_num_format_mask);
1503 --
1504 Else
1505 --
1506 p_num_format_mask := NULL;
1507 --
1508 End if;
1509 --
1510 p_sql_stmt := NULL;
1511 p_error_status := 0;
1512 --
1513 Else
1514 --
1515 -- The validation type may be Independent / dependent.
1516 --
1517 p_sql_stmt := NULL;
1518 p_error_status := 0;
1519 p_num_format_mask := NULL;
1520 --
1521 End if;
1522 --
1523 End if;
1524 --
1525 --
1526 exception
1527 when others then
1528 p_validation_type := null;
1529 p_num_format_mask := null;
1530 p_min_value := null;
1531 p_max_value := null;
1532 p_sql_stmt := null;
1533 p_error_status := null;
1534 --
1535
1536 End;
1537
1538 Procedure get_valueset_sql(p_value_set_id in number,
1539 p_validation_type out nocopy varchar2,
1540 p_sql_stmt out nocopy varchar2,
1541 p_error_status out nocopy number) is
1542 --
1543 l_map varchar2(2000);
1544 l_value_set g_value_set%type;
1545 l_error_status number(10);
1546 l_value_column_name varchar2(2000);
1547 l_app_tab_name varchar2(2000);
1548 l_add_where_clause varchar2(2000);
1549 l_id_column_name varchar2(2000);
1550 --
1551 Begin
1552 --
1553 -- Check if a valid value set id is passed and fetch its validation type.
1554 --
1555 chk_if_valid_value_set( p_value_set_id => p_value_set_id,
1556 p_value_set => l_value_set,
1557 p_error_status => l_error_status);
1558 --
1559 If l_error_status <> 0 then
1560 --
1561 -- Invalid value set id
1562 --
1563 p_validation_type := null;
1564 p_error_status := 1;
1565 p_sql_stmt := NULL;
1566 --
1567 Else
1568 p_validation_type := l_value_set.validation_type;
1569
1570 If l_value_set.validation_type = 'F' then
1571 select value_column_name, application_table_name,
1572 additional_where_clause, id_column_name into l_value_column_name, l_app_tab_name,
1573 l_add_where_clause, l_id_column_name from fnd_flex_validation_tables where flex_value_set_id = p_value_set_id;
1574
1575 p_sql_stmt := rtrim('select '||l_id_column_name||' Id,'||l_value_column_name||' Val,'||'null Att_Name'||' from '||l_app_tab_name||' '||l_add_where_clause, ' ');
1576 --
1577 Else
1578 --
1579 -- The validation type may be Independent / dependent.
1580 --
1581 p_sql_stmt := NULL;
1582 p_error_status := 0;
1583 --
1584 End if;
1585 --
1586 End if;
1587 --
1588 --
1589 exception
1590 when others then
1591 p_sql_stmt := null;
1592 p_error_status := null;
1593 --
1594
1595 End get_valueset_sql;
1596 --
1597 FUNCTION get_display_value(p_value IN VARCHAR2,
1598 p_value_set_id IN NUMBER) return VARCHAR2 IS
1599 l_value_set_rec g_value_set%type;
1600 l_error_status number(10);
1601 l_display varchar2(2000);
1602 l_value_column_name varchar2(2000);
1603 l_app_tab_name varchar2(2000);
1604 l_add_where_clause varchar2(2000);
1605 l_id_column_name varchar2(2000);
1606 l_stmt varchar2(2000);
1607 l_per_business_group number := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
1608 BEGIN
1609 IF p_value_set_id IS NOT NULL THEN
1610 -- Check if a valid value set id is passed and fetch its validation type.
1611 chk_if_valid_value_set(p_value_set_id => p_value_set_id,
1612 p_value_set => l_value_set_rec,
1613 p_error_status => l_error_status);
1614 IF l_error_status <> 0 THEN
1615 -- Invalid value set id. No value set attached. so return value given.
1616 l_display := p_value;
1617 ELSE
1618 IF l_value_set_rec.validation_type = 'F' THEN
1619 SELECT value_column_name, application_table_name, additional_where_clause, id_column_name
1620 INTO l_value_column_name, l_app_tab_name, l_add_where_clause, l_id_column_name
1621 FROM fnd_flex_validation_tables
1622 WHERE flex_value_set_id = p_value_set_id;
1623 IF l_add_where_clause IS NOT NULL THEN
1624 IF INSTR(UPPER(l_add_where_clause), 'ORDER BY') <> 0 THEN
1625 l_add_where_clause := REPLACE(l_add_where_clause,
1626 SUBSTR(l_add_where_clause, INSTR(UPPER(l_add_where_clause), 'ORDER BY')),
1627 '');
1628 END IF;
1629 l_add_where_clause := REPLACE(UPPER(l_add_where_clause), 'WHERE', 'AND');
1630 l_stmt := RTRIM('select '||l_value_column_name||
1631 ' from '||l_app_tab_name||' '||
1632 'where '||l_id_column_name||'='||''''||p_value||''''||' '||l_add_where_clause,
1633 ' ');
1634 ELSE
1635 l_stmt := RTRIM('select '||l_value_column_name||
1636 ' from '||l_app_tab_name||' '||
1637 'where '||l_id_column_name||'='||''''||p_value||'''',
1638 ' ');
1639 END IF;
1640
1641 hr_utility.set_location('before '||l_stmt,10);
1642 hr_utility.set_location('1 -> '||substr(l_stmt,1,50),11);
1643 hr_utility.set_location('1 -> '||substr(l_stmt,1,50),11);
1644 hr_utility.set_location('2 -> '||substr(l_stmt,51,50),11);
1645 hr_utility.set_location('3 -> '||substr(l_stmt,101,50),11);
1646 hr_utility.set_location('4 -> '||substr(l_stmt,151,50),11);
1647 hr_utility.set_location('5 -> '||substr(l_stmt,201,50),11);
1648 hr_utility.set_location('6 -> '||substr(l_stmt,251,50),11);
1649 hr_utility.set_location('7 -> '||substr(l_stmt,301,50),11);
1650 hr_utility.set_location('8 -> '||substr(l_stmt,351,50),11);
1651 hr_utility.set_location('zzzzzzzzzzzzzzzzzzzzzzzzzzzz',11);
1652 -- Added by DN for CBR Enhancements
1653 IF INSTR(UPPER(l_stmt), ':1') <> 0 THEN
1654 -- Replace :1 with business_group_id;
1655 l_stmt := REPLACE(l_stmt, ':1', 'BUSINESS_GROUP_ID');
1656 ELSE
1657 -- Replace FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') with business_group_id;
1658 l_stmt := REPLACE(l_stmt, 'FND_PROFILE.VALUE(''PER_BUSINESS_GROUP_ID'')', 'BUSINESS_GROUP_ID');
1659 END IF;
1660 -- FOR RBC
1661 if instr(upper(l_stmt),':$PROFILES$.PER_BUSINESS_GROUP_ID') > 0 then
1662 hr_utility.set_location('inside bg pattern',11);
1663 l_stmt := REPLACE(l_stmt,':$PROFILES$.PER_BUSINESS_GROUP_ID','FND_PROFILE.VALUE(''PER_BUSINESS_GROUP_ID'')');
1664
1665 end if;
1666 if instr(upper(l_stmt),':$FLEX$.PER_DATES_STANDARD') > 0 then
1667 hr_utility.set_location('inside date pattern',11);
1668 /**
1669 l_stmt := REPLACE(l_stmt,'TO_DATE(:$FLEX$.PER_DATES_STANDARD,''YYYY/MM/DD HH24:MI:SS'')','trunc(sysdate)');
1670 **/
1671 l_stmt := REPLACE(l_stmt,'TO_DATE(:$FLEX$.PER_DATES_STANDARD,''YYYY/MM/DD HH24:MI:SS'')','pqh_utility.get_query_date');
1672 end if;
1673 hr_utility.set_location(' Now executing :'||l_stmt,909);
1674
1675 EXECUTE IMMEDIATE l_stmt INTO l_display;
1676 ELSE
1677 -- If validation type is not table then return entered value as result.
1678 l_display := p_value;
1679 END IF;
1680 END IF;
1681 ELSE
1682 -- If value set is null then sent the entered value as result.
1683 l_display := p_value;
1684 END IF;
1685 RETURN l_display;
1686 EXCEPTION
1687 WHEN OTHERS THEN
1688 hr_utility.set_location(sqlerrm,10);
1689 hr_utility.set_location('in exception stmt executed is',10);
1690 hr_utility.set_location('1 -> '||substr(l_stmt,1,50),11);
1691 hr_utility.set_location('2 -> '||substr(l_stmt,51,50),11);
1692 hr_utility.set_location('3 -> '||substr(l_stmt,101,50),11);
1693 hr_utility.set_location('4 -> '||substr(l_stmt,151,50),11);
1694 hr_utility.set_location('5 -> '||substr(l_stmt,201,50),11);
1695 hr_utility.set_location('6 -> '||substr(l_stmt,251,50),11);
1696 hr_utility.set_location('7 -> '||substr(l_stmt,301,50),11);
1697 hr_utility.set_location('8 -> '||substr(l_stmt,351,50),11);
1698 l_display := p_value;
1699 RETURN l_display;
1700 END;
1701 --
1702 FUNCTION get_display_value(p_value IN VARCHAR2,
1703 p_value_set_id IN NUMBER,
1704 p_prnt_valset_nm IN VARCHAR2,
1705 p_prnt_value IN VARCHAR2) return VARCHAR2 IS
1706 l_value_set_rec g_value_set%type;
1707 l_error_status number(10);
1708 l_display varchar2(2000);
1709 l_value_column_name varchar2(2000);
1710 l_app_tab_name varchar2(2000);
1711 l_add_where_clause varchar2(2000);
1712 l_id_column_name varchar2(2000);
1713 l_stmt varchar2(2000);
1714 l_per_business_group number := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
1715 BEGIN
1716 IF p_value_set_id IS NOT NULL THEN
1717 -- Check if a valid value set id is passed and fetch its validation type.
1718 chk_if_valid_value_set(p_value_set_id => p_value_set_id,
1719 p_value_set => l_value_set_rec,
1720 p_error_status => l_error_status);
1721 IF l_error_status <> 0 THEN
1722 -- Invalid value set id. No value set attached. so return value given.
1723 l_display := p_value;
1724 ELSE
1725 IF l_value_set_rec.validation_type = 'F' THEN
1726 SELECT value_column_name, application_table_name, additional_where_clause, id_column_name
1727 INTO l_value_column_name, l_app_tab_name, l_add_where_clause, l_id_column_name
1728 FROM fnd_flex_validation_tables
1729 WHERE flex_value_set_id = p_value_set_id;
1730 IF l_add_where_clause IS NOT NULL THEN
1731 IF INSTR(UPPER(l_add_where_clause), 'ORDER BY') <> 0 THEN
1732 l_add_where_clause := REPLACE(l_add_where_clause,
1733 SUBSTR(l_add_where_clause, INSTR(UPPER(l_add_where_clause), 'ORDER BY')),
1734 '');
1735 END IF;
1736 l_add_where_clause := REPLACE(UPPER(l_add_where_clause), 'WHERE', 'AND');
1737 l_stmt := RTRIM('select '||l_value_column_name||
1738 ' from '||l_app_tab_name||' '||
1739 'where '||l_id_column_name||'='||''''||p_value||''''||' '||l_add_where_clause,
1740 ' ');
1741 ELSE
1742 l_stmt := RTRIM('select '||l_value_column_name||
1743 ' from '||l_app_tab_name||' '||
1744 'where '||l_id_column_name||'='||''''||p_value||'''',
1745 ' ');
1746 END IF;
1747
1748 hr_utility.set_location('before replace stmt is ',10);
1749 hr_utility.set_location('1 -> '||substr(l_stmt,1,50),11);
1750 hr_utility.set_location('2 -> '||substr(l_stmt,51,50),11);
1751 hr_utility.set_location('3 -> '||substr(l_stmt,101,50),11);
1752 hr_utility.set_location('4 -> '||substr(l_stmt,151,50),11);
1753 hr_utility.set_location('5 -> '||substr(l_stmt,201,50),11);
1754 hr_utility.set_location('6 -> '||substr(l_stmt,251,50),11);
1755 hr_utility.set_location('7 -> '||substr(l_stmt,301,50),11);
1756 hr_utility.set_location('8 -> '||substr(l_stmt,351,50),11);
1757 hr_utility.set_location('zzzzzzzzzzzzzzzzzzzzzzzzzzzz',11);
1758 -- Added by DN for CBR Enhancements
1759 IF INSTR(UPPER(l_stmt), ':1') <> 0 THEN
1760 -- Replace :1 with business_group_id;
1761 l_stmt := REPLACE(l_stmt, ':1', 'BUSINESS_GROUP_ID');
1762 ELSE
1763 -- Replace FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') with business_group_id;
1764 l_stmt := REPLACE(l_stmt, 'FND_PROFILE.VALUE(''PER_BUSINESS_GROUP_ID'')', 'BUSINESS_GROUP_ID');
1765 END IF;
1766 if instr(upper(l_stmt),':$PROFILES$.PER_BUSINESS_GROUP_ID') > 0 then
1767 hr_utility.set_location('inside bg pattern',11);
1768 l_stmt := REPLACE(l_stmt,':$PROFILES$.PER_BUSINESS_GROUP_ID','FND_PROFILE.VALUE(''PER_BUSINESS_GROUP_ID'')');
1769 end if;
1770 if instr(upper(l_stmt),':$FLEX$.PER_DATES_STANDARD') > 0 then
1771 hr_utility.set_location('inside date pattern',11);
1772 l_stmt := REPLACE(l_stmt,'TO_DATE(:$FLEX$.PER_DATES_STANDARD,''YYYY/MM/DD HH24:MI:SS'')','trunc(sysdate)');
1773 end if;
1774 if instr(upper(l_stmt),':$FLEX$.'||p_prnt_valset_nm) > 0 then
1775 hr_utility.set_location('inside date pattern',11);
1776 l_stmt := REPLACE(l_stmt,':$FLEX$.'||p_prnt_valset_nm,p_prnt_value);
1777 end if;
1778 EXECUTE IMMEDIATE l_stmt INTO l_display;
1779 ELSE
1780 -- If validation type is not table then return entered value as result.
1781 l_display := p_value;
1782 END IF;
1783 END IF;
1784 ELSE
1785 -- If value set is null then sent the entered value as result.
1786 l_display := p_value;
1787 END IF;
1788 RETURN l_display;
1789 EXCEPTION
1790 WHEN OTHERS THEN
1791 hr_utility.set_location('in exception stmt executed is',10);
1792 hr_utility.set_location('1 -> '||substr(l_stmt,1,50),11);
1793 hr_utility.set_location('2 -> '||substr(l_stmt,51,50),11);
1794 hr_utility.set_location('3 -> '||substr(l_stmt,101,50),11);
1795 hr_utility.set_location('4 -> '||substr(l_stmt,151,50),11);
1796 hr_utility.set_location('5 -> '||substr(l_stmt,201,50),11);
1797 hr_utility.set_location('6 -> '||substr(l_stmt,251,50),11);
1798 hr_utility.set_location('7 -> '||substr(l_stmt,301,50),11);
1799 hr_utility.set_location('8 -> '||substr(l_stmt,351,50),11);
1800 l_display := p_value;
1801 RETURN l_display;
1802 END;
1803 --
1804 function get_transaction_category_id(p_short_name in varchar2, p_business_group_id in number default null) return number is
1805 l_transaction_category_id number;
1806 cursor c1 is
1807 select transaction_category_id
1808 from pqh_transaction_categories
1809 where short_name = p_short_name
1810 and business_group_id = p_business_group_id;
1811 begin
1812 if p_short_name is not null then
1813 open c1;
1814 fetch c1 into l_transaction_category_id;
1815 close c1;
1816 end if;
1817 return l_transaction_category_id;
1818 end;
1819 --
1820 --
1821 Procedure set_message_level_cd
1822 ( p_rule_level_cd IN varchar2) is
1823 --
1824 l_proc varchar2(72) := g_package||'get_message_level_cd';
1825 --
1826 Begin
1827 --
1828 if p_rule_level_cd = 'E' Then
1829 g_rule_level_cd := 'E' ;
1830 Elsif p_rule_level_cd = 'W' Then
1831 g_rule_level_cd := 'W' ;
1832 End if;
1833 --
1834 End;
1835 --
1836 --
1837 function get_ptx_create_flag(p_position_transaction_id number) return varchar2 is
1838 l_create_flag varchar2(10);
1839 cursor c_transaction_templates(p_transaction_id number) is
1840 select create_flag
1841 from pqh_transaction_templates ttl, pqh_templates tem
1842 where ttl.template_id = tem.template_id
1843 and ttl.transaction_id = p_transaction_id
1844 and rownum<2;
1845 begin
1846 open c_transaction_templates(p_position_transaction_id);
1847 fetch c_transaction_templates into l_create_flag;
1848 close c_transaction_templates;
1849 return l_create_flag;
1850 end;
1851 --
1852 --
1853 function get_pos_rec_eed(p_position_id number, p_start_date date) return date is
1854 l_eed date;
1855 cursor c_pos_rec_eed(p_position_id number, p_start_date date) is
1856 SELECT min(effective_start_date)-1 effective_end_date
1857 FROM
1858 (select effective_start_date
1859 from hr_all_positions_f
1860 where position_id = p_position_id
1861 and effective_start_date > p_start_date
1862 union
1863 select action_date effective_start_date
1864 from pqh_position_transactions
1865 where position_id = p_position_id
1866 and action_date > p_start_date
1867 and transaction_status = 'SUBMITTED'
1868 );
1869 begin
1870 open c_pos_rec_eed(p_position_id, p_start_date);
1871 fetch c_pos_rec_eed into l_eed;
1872 close c_pos_rec_eed;
1873 return NVL(l_eed, to_date('4712/12/31 12:00:00', 'RRRR/MM/DD HH:MI:SS'));
1874 end;
1875 --
1876 --
1877 function get_df_context_desc(p_df_name varchar2, p_context_code varchar2) return varchar2 is
1878 l_desc varchar2(100);
1879 cursor c1 is
1880 select description
1881 from FND_DESCR_FLEX_CONTEXTS_VL
1882 where application_id = 800 -- NS: 29-Mar-2006: Perf: SQL ID: 16596807
1883 and descriptive_flexfield_name = p_df_name
1884 and descriptive_flex_context_code = p_context_code;
1885 begin
1886 open c1;
1887 fetch c1 into l_desc;
1888 close c1;
1889 return l_desc;
1890 end;
1891 --
1892 function get_pte_context_desc(p_pte_id number) return varchar2 is
1893 l_information_type varchar2(100);
1894 l_df_context varchar2(100);
1895 cursor c1 is
1896 select information_type
1897 from pqh_ptx_extra_info
1898 where ptx_extra_info_id = p_pte_id;
1899 begin
1900 open c1;
1901 fetch c1 into l_information_type;
1902 close c1;
1903 l_df_context := get_df_context_desc('Extra Position Info DDF', l_information_type);
1904 return l_df_context;
1905 end;
1906 --
1907 function get_kf_structure_name(p_kf_short_name varchar2, p_id_flex_num number) return
1908 varchar2 is
1909 l_id_flex_structure_name varchar2(100);
1910 l_id_flex_structure_code varchar2(100);
1911 cursor c1 is
1912 select fs.id_flex_structure_name, fs.id_flex_structure_code
1913 from fnd_id_flex_structures_vl fs
1914 where fs.id_flex_code = p_kf_short_name
1915 and fs.id_flex_num = p_id_flex_num;
1916 begin
1917 open c1;
1918 fetch c1 into l_id_flex_structure_name, l_id_flex_structure_code;
1919 close c1;
1920 return l_id_flex_structure_name;
1921 end;
1922 --
1923 function get_tjr_classification(p_tjr_id number) return varchar2 is
1924 l_id_flex_num number;
1925 cursor c1 is
1926 select id_flex_num
1927 from pqh_txn_job_requirements tjr, PER_ANALYSIS_CRITERIA pac
1928 where txn_job_requirement_id = p_tjr_id
1929 and tjr.analysis_criteria_id = pac.analysis_criteria_id;
1930 begin
1931 open c1;
1932 fetch c1 into l_id_flex_num;
1933 close c1;
1934 return get_kf_structure_name('PEA', l_id_flex_num);
1935 end;
1936 --
1937 /* The function modified on 24-AUG-2001 to check for Public Sector Installation for
1938 a particular legislation */
1939 /* function is_pqh_installed return boolean is
1940 l_oracle_schema varchar2(40);
1941 l_status fnd_product_installations.status%type;
1942 l_industry fnd_product_installations.industry%type;
1943 l_pqh_installed boolean :=FALSE;
1944 --
1945 begin
1946 if ( fnd_installation.get_app_info('PQH',l_status,l_industry,l_oracle_schema)) then
1947 if l_status = 'I' then
1948 l_pqh_installed := TRUE;
1949 end if;
1950 end if;
1951 return l_pqh_installed;
1952 --
1953 end is_pqh_installed; */
1954 --
1955 /* The function changed on 11-SEP-2001 to incorporate new approach taken
1956 to identify Public Sector Product */
1957 --
1958 /* function is_pqh_installed(p_legislation_code IN VARCHAR2) return boolean is
1959 --
1960 begin
1961 --
1962 return hr_utility.chk_product_install('PQH',p_legislation_code);
1963 --
1964 end is_pqh_installed;*/
1965 --
1966 function is_pqh_installed(p_business_group_id IN NUMBER) return boolean is
1967 --
1968 Cursor csr_pqh_installed is
1969 Select org_information2
1970 from hr_organization_information
1971 where org_information_context = 'Public Sector Details'
1972 and organization_id = p_business_group_id ;
1973 l_pqh_installed varchar2(1);
1974 begin
1975 --
1976 open csr_pqh_installed;
1977 fetch csr_pqh_installed into l_pqh_installed;
1978 close csr_pqh_installed;
1979 if l_pqh_installed = 'Y' then
1980 return TRUE;
1981 else
1982 return FALSE;
1983 end if;
1984 --
1985 end is_pqh_installed;
1986 --
1987 function GET_PATEO_PROJECT_NAME(p_project_id in number) return varchar2 is
1988 cursor c1 is select project_name
1989 from gms_pqh_projects_v
1990 where project_id = p_project_id;
1991 l_name gms_pqh_projects_v.project_name%type;
1992 begin
1993 if p_project_id is not null then
1994 open c1;
1995 fetch c1 into l_name;
1996 close c1;
1997 end if;
1998 return l_name;
1999 end;
2000
2001 function GET_PATEO_TASK_NAME(p_task_id in number,
2002 p_project_id in number) return varchar2 is
2003 cursor c1 is select task_name
2004 from pa_tasks_expend_v
2005 where task_id = p_task_id
2006 and Project_id = p_project_id;
2007 l_name pa_tasks_expend_v.task_name%type;
2008 begin
2009 if p_project_id is not null and p_task_id is not null then
2010 open c1;
2011 fetch c1 into l_name;
2012 close c1;
2013 end if;
2014 return l_name;
2015 end;
2016
2017 function GET_PATEO_AWARD_NAME(p_award_id in number,
2018 p_project_id in number,
2019 p_task_id in number) return varchar2 is
2020 cursor c1 is select award_short_name
2021 from gms_pqh_awards_v
2022 where award_id = p_award_id
2023 and project_id = p_project_id
2024 and task_id = p_task_id;
2025 l_name gms_pqh_awards_v.award_short_name%type;
2026 begin
2027 if p_award_id is not null and p_task_id is not null and p_project_id is not null then
2028 open c1;
2029 fetch c1 into l_name;
2030 close c1;
2031 end if;
2032 return l_name;
2033 end;
2034
2035 function GET_PATEO_EXPENDITURE_TYPE(p_project_id in number,
2036 p_award_id in number,
2037 p_task_id in number,
2038 p_expenditure_type in varchar2) return varchar2 is
2039 cursor c1 is select expenditure_type
2040 from gms_pqh_exp_types_v
2041 where project_id = p_project_id
2042 and task_id = p_task_id
2043 and award_id = p_award_id
2044 and expenditure_type = p_expenditure_type
2045 and (sysdate between expnd_typ_start_date_active and
2046 nvl(expnd_typ_end_date_active , sysdate)) ;
2047 l_name gms_pqh_exp_types_v.expenditure_type%type;
2048 begin
2049 if p_project_id is not null and p_award_id is not null and p_task_id is not null and p_expenditure_type is not null then
2050 open c1;
2051 fetch c1 into l_name;
2052 close c1;
2053 end if;
2054 return l_name;
2055 end;
2056
2057 function GET_PATEO_ORGANIZATION_NAME(p_organization_id in number) return varchar2 is
2058 cursor c1 is select name
2059 from pa_organizations_expend_v
2060 where organization_id = p_organization_id;
2061 l_name pa_organizations_expend_v.name%type;
2062 begin
2063 if p_organization_id is not null then
2064 open c1;
2065 fetch c1 into l_name;
2066 close c1;
2067 end if;
2068 return l_name;
2069 end;
2070 --
2071 function pqh_rule_scope(p_business_group_id in number,
2072 p_organization_structure_id in number,
2073 p_starting_organization_id in number,
2074 p_organization_id in number) return varchar2 is
2075 l_scope varchar2(1000);
2076 l_oh_name per_organization_structures.name%type;
2077 begin
2078 if p_business_group_id is null then
2079 l_scope := hr_general.decode_lookup('PQH_RULE_SET_SCOPE','GLOBAL');
2080 else
2081 l_scope := hr_general.decode_lookup('PQH_RULE_SET_SCOPE','BG')||':'||hr_general.decode_organization(p_business_group_id);
2082 if p_organization_structure_id is not null then
2083 select name into l_oh_name from per_organization_structures where organization_structure_id = p_organization_structure_id;
2084 l_scope := l_scope||' / '||hr_general.decode_lookup('PQH_RULE_SET_SCOPE','OH')||':'||l_oh_name;
2085 if p_starting_organization_id is not null then
2086 l_scope := l_scope||' / '||hr_general.decode_lookup('PQH_RULE_SET_SCOPE','STORG')||':'||hr_general.decode_organization(p_starting_organization_id);
2087 end if;
2088 end if;
2089 if p_organization_id is not null then
2090 l_scope := l_scope||' / '||hr_general.decode_lookup('PQH_RULE_SET_SCOPE','ORG')||':'||hr_general.decode_organization(p_organization_id);
2091 end if;
2092 end if;
2093 return l_scope;
2094 end;
2095 --
2096 function get_rule_set_name(p_rule_set_id in number) return varchar2 is
2097 --
2098 l_rule_set_name varchar2(240);
2099 begin
2100 if p_rule_set_id is not null then
2101 select rule_set_name into l_rule_set_name from pqh_rule_sets_vl
2102 where rule_set_id = p_rule_set_id;
2103 end if;
2104 return l_rule_set_name;
2105 exception when others then
2106 return null;
2107 end; --get_rule_set_name
2108 --
2109 FUNCTION get_number_of_days (DURATION NUMBER, duration_units VARCHAR2)
2110 RETURN NUMBER
2111 IS
2112 BEGIN
2113 IF (duration_units = 'Y')
2114 THEN
2115 RETURN ADD_MONTHS (SYSDATE, DURATION * 12) - SYSDATE;
2116 ELSIF (duration_units = 'M')
2117 THEN
2118 RETURN ADD_MONTHS (SYSDATE, DURATION) - SYSDATE;
2119 ELSIF (duration_units = 'W')
2120 THEN
2121 RETURN DURATION * 7;
2122 ELSE
2123 RETURN DURATION;
2124 END IF;
2125 END;
2126 ---
2127 FUNCTION get_org_hierarchy_name(p_organization_structure_id IN NUMBER) RETURN VARCHAR2
2128 IS
2129 v_org_hier_name per_organization_structures.name%TYPE := NULL;
2130 CURSOR c_hier_cur
2131 IS SELECT name FROM per_organization_structures
2132 WHERE organization_structure_id = p_organization_structure_id;
2133 BEGIN
2134 IF p_organization_structure_id IS NOT NULL THEN
2135 OPEN c_hier_cur;
2136 FETCH c_hier_cur INTO v_org_hier_name;
2137 CLOSE c_hier_cur;
2138 END IF;
2139 RETURN v_org_hier_name;
2140 EXCEPTION
2141 WHEN OTHERS THEN
2142 IF c_hier_cur%ISOPEN THEN
2143 CLOSE c_hier_cur;
2144 END IF;
2145 RETURN NULL;
2146 END;
2147 --
2148 End pqh_utility;
[Home] [Help]