1 Package Body pqh_pei_ddf as
2 /* $Header: pqpeiddf.pkb 115.5 2003/12/17 15:15:15 hsajja noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_pei_ddf.'; -- Global package name
9 --
10 --
11 --
12 -- -----------------------------------------------------------------------------
13 -- |-------------------------------< ddf >--------------------------------------|
14 -- -----------------------------------------------------------------------------
15 -- {Start of Comments}
16 --
17 -- Description:
18 -- This procedure controls the validation processing required for
19 -- developer descriptive flexfields by calling the relevant validation
20 -- procedures. These are called dependant on the value of the relevant
21 -- entity reference field value.
22 --
23 -- Pre Conditions:
24 -- A fully validated entity record structure.
25 --
26 -- In Arguments:
27 -- p_rec (Record structure for relevant entity).
28 --
29 -- Post Success:
30 -- Processing of the calling api continues.
31 --
32 -- Post Failure:
33 -- A failure can only occur under two circumstances:
34 -- 1) The value of reference field is not supported.
35 -- 2) If when the reference field value is null and not all
36 -- the information arguments are not null(i.e. information
37 -- arguments cannot be set without a corresponding reference
38 -- field value).
39 --
40 -- Developer Implementation Notes:
41 -- Developer defined.
42 --
43 -- Access Status:
44 -- Internal Development Use Only.
45 --
46 -- {End of Comments}
47 -- -----------------------------------------------------------------------------
48 procedure ddf
49 (
50 p_person_extra_info_id in number ,
51 p_person_id in number ,
52 p_information_type in varchar2 ,
53 p_request_id in number ,
54 p_program_application_id in number ,
55 p_program_id in number ,
56 p_program_update_date in date ,
57 p_pei_attribute_category in varchar2 ,
58 p_pei_attribute1 in varchar2 ,
59 p_pei_attribute2 in varchar2 ,
60 p_pei_attribute3 in varchar2 ,
61 p_pei_attribute4 in varchar2 ,
62 p_pei_attribute5 in varchar2 ,
63 p_pei_attribute6 in varchar2 ,
64 p_pei_attribute7 in varchar2 ,
65 p_pei_attribute8 in varchar2 ,
66 p_pei_attribute9 in varchar2 ,
67 p_pei_attribute10 in varchar2 ,
68 p_pei_attribute11 in varchar2 ,
69 p_pei_attribute12 in varchar2 ,
70 p_pei_attribute13 in varchar2 ,
71 p_pei_attribute14 in varchar2 ,
72 p_pei_attribute15 in varchar2 ,
73 p_pei_attribute16 in varchar2 ,
74 p_pei_attribute17 in varchar2 ,
75 p_pei_attribute18 in varchar2 ,
76 p_pei_attribute19 in varchar2 ,
77 p_pei_attribute20 in varchar2 ,
78 p_pei_information_category in varchar2 ,
79 p_pei_information1 in varchar2 ,
80 p_pei_information2 in varchar2 ,
81 p_pei_information3 in varchar2 ,
82 p_pei_information4 in varchar2 ,
83 p_pei_information5 in varchar2 ,
84 p_pei_information6 in varchar2 ,
85 p_pei_information7 in varchar2 ,
86 p_pei_information8 in varchar2 ,
87 p_pei_information9 in varchar2 ,
88 p_pei_information10 in varchar2 ,
89 p_pei_information11 in varchar2 ,
90 p_pei_information12 in varchar2 ,
91 p_pei_information13 in varchar2 ,
92 p_pei_information14 in varchar2 ,
93 p_pei_information15 in varchar2 ,
94 p_pei_information16 in varchar2 ,
95 p_pei_information17 in varchar2 ,
96 p_pei_information18 in varchar2 ,
97 p_pei_information19 in varchar2 ,
98 p_pei_information20 in varchar2 ,
99 p_pei_information21 in varchar2 ,
100 p_pei_information22 in varchar2 ,
101 p_pei_information23 in varchar2 ,
102 p_pei_information24 in varchar2 ,
103 p_pei_information25 in varchar2 ,
104 p_pei_information26 in varchar2 ,
105 p_pei_information27 in varchar2 ,
106 p_pei_information28 in varchar2 ,
107 p_pei_information29 in varchar2 ,
108 p_pei_information30 in varchar2
109 ) is
110
111 --
112 l_proc varchar2(72) := g_package||'ddf';
113 --
114 Cursor get_eit_name(p_information_type varchar2) is
115 select descriptive_flex_context_name from FND_DESCR_FLEX_CONTEXTS_VL FLV
116 WHERE FLV.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_information_type
117 AND FLV.DESCRIPTIVE_FLEXFIELD_NAME = 'Extra Person Info DDF'
118 AND FLV.APPLICATION_ID = 800;
119 --
120 l_eit_name varchar2(240);
121 --
122 Begin
123 hr_utility.set_location('Entering:'||l_proc, 5);
124
125 if NVL(p_information_type,'X') = 'PQH_ROLE_USERS' then
126 --
127 chk_default_role
128 (p_person_extra_info_id => p_person_extra_info_id
129 ,p_information_type => p_information_type
130 ,p_person_id => p_person_id
131 ,p_pei_information3 => p_pei_information3
132 ,p_pei_information4 => p_pei_information4
133 );
134 --
135 elsif NVL(p_information_type, 'X') in ('PQH_TENURE_STATUS', 'PQH_ACADEMIC_RANK') then
136 --
137 if hr_general2.is_person_type(p_person_id, 'CWK', hr_general.effective_date) then
138 for each_rec in get_eit_name(p_information_type) loop
139 l_eit_name := each_rec.descriptive_flex_context_name;
140 end loop;
141 hr_utility.set_message(8302, 'PQH_CWK_EXTRA_INFO_NOT_ALLOWED');
142 hr_utility.set_message_token('EIT', l_eit_name);
143 hr_utility.raise_error;
144 end if;
145 end if;
146
147 hr_utility.set_location(' Leaving:'||l_proc, 10);
148
149 Exception
150 WHEN others THEN
151 raise;
152 End ddf;
153
154
155 Procedure chk_default_role
156 (
157 p_person_extra_info_id in per_people_extra_info.person_extra_info_id%TYPE
158 ,p_information_type in per_people_extra_info.information_type%TYPE
159 ,p_person_id in per_people_extra_info.person_id%TYPE
160 ,p_pei_information3 in per_people_extra_info.pei_information3%TYPE
161 ,p_pei_information4 in per_people_extra_info.pei_information4%TYPE
162 ) is
163
164 --
165 l_proc varchar2(72) := g_package||'chk_default_role';
166 l_role_id pqh_roles.role_id%TYPE;
167 l_role_name pqh_roles.role_name%TYPE;
168 --
169 CURSOR csr_duplicate_role IS
170 SELECT pei_information3
171 FROM per_people_extra_info
172 WHERE person_extra_info_id <> p_person_extra_info_id
173 AND NVL(information_type,'X') = 'PQH_ROLE_USERS'
174 AND person_id = p_person_id
175 AND pei_information3 = p_pei_information3;
176
177 --
178 CURSOR csr_duplicate_default_role IS
179 SELECT pei_information3
180 FROM per_people_extra_info
181 WHERE person_extra_info_id <> p_person_extra_info_id
182 AND NVL(information_type,'X') = 'PQH_ROLE_USERS'
183 AND person_id = p_person_id
184 AND NVL(pei_information4,'N') = 'Y';
185 --
186 Begin
187 hr_utility.set_location('Entering:'||l_proc, 5);
188 --
189 -- check if the same role is being assigned more then once to the same person
190 --
191 OPEN csr_duplicate_role;
192 FETCH csr_duplicate_role INTO l_role_id;
193 CLOSE csr_duplicate_role;
194
195 IF l_role_id IS NOT NULL THEN
196 --
197 -- raise error
198 --
199 hr_utility.set_message(8302,'PQH_DUPLICATE_ROLE');
200 hr_utility.raise_error;
201
202 END IF; -- role id is not null
203 --
204 -- check if the person already has a default role
205 --
206 IF NVL(p_pei_information4,'N') = 'Y' THEN
207 --
208 OPEN csr_duplicate_default_role;
209 FETCH csr_duplicate_default_role INTO l_role_id;
210 CLOSE csr_duplicate_default_role;
211
212 IF l_role_id IS NOT NULL THEN
213 --
214 -- raise error
215 --
216 hr_utility.set_message(8302,'PQH_MANY_DEFAULT_ROLES');
217 hr_utility.raise_error;
218
219 END IF; -- role id is not null
220
221 END IF; -- current role is default
222
223 hr_utility.set_location(' Leaving:'||l_proc, 10);
224
225 Exception
226 WHEN others THEN
227 raise;
228 End chk_default_role;
229
230
231 --
232 --
233 end pqh_pei_ddf;
[Home] [Help]