1 package body hr_tca_utility as
2 /* $Header: petcautl.pkb 115.3 2004/06/29 10:09:41 tpapired noship $ */
3
4 g_package varchar2(30) := 'hr_tca_utility.';
5 -- ----------------------------------------------------------------------------
6 -- |------------------------------< get_person_id >---------------------------|
7 -- ----------------------------------------------------------------------------
8 PROCEDURE get_person_id
9 (p_party_id in number
10 ,p_effective_date in date default sysdate
11 ,p_person_id out nocopy number
12 ,p_matches out nocopy varchar
13 ) is
14
15 l_person_id number;
16 l_effective_date date;
17 l_count number :=0;
18 l_proc varchar2(80) := g_package||'get_person_id';
19 --
20 -- Cursor to check for persons of a valid person_type for given party_id
21 --
22 cursor person_cur (p_party_id in number) IS
23 SELECT ppf.PERSON_ID
24 ,typ.SYSTEM_PERSON_TYPE
25 FROM per_all_people_f ppf
26 ,per_person_types typ
27 ,per_person_type_usages_f ptu
28 WHERE ppf.party_id = p_party_id
29 AND l_effective_date between ppf.effective_start_date
30 and ppf.effective_end_date
31 AND ppf.person_id = ptu.person_id
32 AND typ.person_type_id = ptu.person_type_id
33 AND l_effective_date between ptu.effective_start_date
34 and ptu.effective_end_date
35 ORDER BY DECODE(typ.system_person_type
36 ,'EMP' ,1
37 ,'CWK' ,2
38 ,'APL' ,3
39 ,'EX_EMP',4
40 ,'EX_CWK',5
41 ,'EX_APL',6
42 ,7
43 ), ppf.person_id desc;
44 --
45 person_type person_cur%rowtype;
46 --
47 -- A person can have multiple person_types and can have the same person_id for other
48 -- person types, which should not be counted as multiple matches. This cursor gets
49 -- all other person_id's except the person fetched in the above cursor. This clearly
50 -- identifies if more than one match exists for the given party_id
51 --
52 cursor multiple_person_cur (p_party_id in number,p_person_id in number) IS
53 SELECT ppf.PERSON_ID
54 FROM per_all_people_f ppf
55 WHERE ppf.party_id = p_party_id
56 AND l_effective_date between ppf.effective_start_date
57 and ppf.effective_end_date
58 AND ppf.person_id <> p_person_id;
59 --
60 multiple_person_type multiple_person_cur%rowtype;
61 --
62 Begin
63 --
64 hr_utility.set_location('Entering '||l_proc,10);
65 --
66 l_effective_date := trunc(p_effective_date);
67 -- initially set this to 'N'.
68 p_matches := 'N';
69
70 open person_cur(p_party_id);
71 LOOP
72 fetch person_cur into person_type;
73 exit when person_cur%notfound;
74 p_person_id := person_type.person_id;
75 --
76 --
77 p_matches := 'Y';
78 --
79 -- since atleast one person has a valid match, no need to check
80 -- further, but check for existence of more matches with second cursor.
81 -- This cursor fetches all other persons except the person fetched above
82 --
83 open multiple_person_cur(p_party_id,p_person_id);
84 fetch multiple_person_cur into multiple_person_type;
85 exit when multiple_person_cur%notfound;
86 p_matches := 'W';
87 exit;
88 --
89 end loop;
90 --
91 close person_cur;
92 --
93 if multiple_person_cur%ISOPEN then
94 close multiple_person_cur;
95 end if;
96 --
97 hr_utility.set_location('Leaving '||l_proc,100);
98 --
99 end;
100 --
101 end hr_tca_utility;
102 --
[Home] [Help]