[Home] [Help]
PACKAGE BODY: APPS.HR_QSV_BUS
Source
1 Package Body hr_qsv_bus as
2 /* $Header: hrqsvrhi.pkb 115.9 2003/08/27 00:17:34 hpandya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_qsv_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be used by the
11 -- return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_quest_answer_val_id number default null;
15 g_questionnaire_template_id hr_quest_fields.questionnaire_template_id%TYPE;
16 --
17
18 -- ----------------------------------------------------------------------------
19 -- |------------------------< chk_non_updateable_args >-----------------------|
20 -- ----------------------------------------------------------------------------
21 --
22 Procedure chk_non_updateable_args
23 (p_rec in hr_qsv_shd.g_rec_type
24 )
25 is
26 --
27 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
28 l_error exception;
29 l_argument varchar2(30);
30 --
31 begin
32 --
33 hr_utility.set_location('Entering: '||l_proc, 10);
34 --
35 -- Only proceed with validation if a row exists for the
36 -- current record in the HR Schema.
37 --
38 if not hr_qsv_shd.api_updating
39 (p_quest_answer_val_id => p_rec.quest_answer_val_id
40 ,p_object_version_number => p_rec.object_version_number
41 ) then
42 fnd_message.set_name('PER','HR_6153_ALL_PROCEDURE_FAIL');
43 fnd_message.set_token('PROCEDURE',l_proc);
44 fnd_message.set_token('STEP',20);
45 fnd_message.raise_error;
46 end if;
47 hr_utility.set_location(l_proc, 30);
48 --
49 if nvl(p_rec.questionnaire_answer_id, hr_api.g_number)
50 <> hr_qsv_shd.g_old_rec.questionnaire_answer_id then
51 l_argument := 'questionnaire_answer_id';
52 raise l_error;
53 end if;
54 hr_utility.set_location(l_proc, 40);
55 --
56 if nvl(p_rec.field_id, hr_api.g_number) <> hr_qsv_shd.g_old_rec.field_id then
57 l_argument := 'field_id';
58 raise l_error;
59 end if;
60 hr_utility.set_location('Leaving: '||l_proc,50);
61 --
62 exception
63 when l_error then
64 hr_api.argument_changed_error
65 (p_api_name => l_proc
66 ,p_argument => l_argument
67 );
68 when others then
69 raise;
70 --
71 end chk_non_updateable_args;
72 --
73 -- ----------------------------------------------------------------------------
74 -- |-------------------< chk_questionnaire_answer_id >------------------------|
75 -- ----------------------------------------------------------------------------
76 procedure chk_questionnaire_answer_id
77 (p_questionnaire_answer_id
78 in hr_quest_answer_values.questionnaire_answer_id%TYPE
79 )
80 is
81 --
82 l_proc varchar2(72) := g_package || 'chk_questionnaire_answer_id';
83 l_exists varchar2(1);
84 --
85 -- Cursor to check if questionnaire_answer_id exists
86 --
87 cursor csr_id_exists is
88 select qsa.questionnaire_template_id
89 from hr_quest_answers qsa
90 where qsa.questionnaire_answer_id = p_questionnaire_answer_id;
91 --
92 begin
93 --
94 hr_utility.set_location('Entering: '||l_proc,10);
95 --
96 if p_questionnaire_answer_id is null then
97 -- error, as this cannot be null
98 fnd_message.set_name('PER','PER_52446_QSV_MAND_ANS_ID');
99 fnd_message.raise_error;
100 else
101 -- Check that id exists in HR_QUEST_ANSWERS table
102 open csr_id_exists;
103 -- Fetch questionnaire_template_id for later use.
104 fetch csr_id_exists into g_questionnaire_template_id;
105 if csr_id_exists%NOTFOUND then
106 -- id doent exist, so error.
107 close csr_id_exists;
108 hr_qsv_shd.constraint_error('HR_QUEST_ANSWER_VALUES_FK1');
109 end if;
110 close csr_id_exists;
111 end if;
112 --
113 hr_utility.set_location('Leaving: '||l_proc,20);
114 --
115 end chk_questionnaire_answer_id;
116 --
117 -- ----------------------------------------------------------------------------
118 -- |----------------------------< chk_field_id >------------------------------|
119 -- ----------------------------------------------------------------------------
120 --
121 procedure chk_field_id
122 (p_field_id in hr_quest_answer_values.field_id%TYPE
123 ,p_questionnaire_answer_id
124 in hr_quest_answer_values.questionnaire_answer_id%TYPE
125 )
126 is
127 --
128 l_proc varchar2(72) := g_package || 'chk_field_id';
129 l_exists varchar2(1);
130 l_qsa_qu_temp_id hr_quest_answers.questionnaire_template_id%TYPE;
131 l_questionnaire_template_id hr_quest_fields.questionnaire_template_id%TYPE;
132 --
133 -- Cursor to check that field_id exists in HR_QUEST_FIELDS
134 --
135 cursor csr_id_exists is
136 select qsf.questionnaire_template_id
137 from hr_quest_fields qsf
138 where qsf.field_id = p_field_id;
139 --
140 -- Cursor to determine if id is unique for the given questionnaire_answer_id
141 --
142 cursor csr_chk_unique is
143 select null
144 from hr_quest_answer_values qsv
145 where qsv.questionnaire_answer_id = p_questionnaire_answer_id
146 and qsv.field_id = p_field_id;
147 --
148 begin
149 --
150 hr_utility.set_location('Entering: '||l_proc,10);
151 --
152 if p_field_id is null then
153 -- Error, as this is mandatory
154 fnd_message.set_name('PER','PER_52447_QSV_MAND_FIELD_ID');
155 fnd_message.raise_error;
156 else
157 -- determine if id exists in HR_QUEST_FIELDS
158 open csr_id_exists;
159 fetch csr_id_exists into l_questionnaire_template_id;
160 if csr_id_exists%NOTFOUND then
161 -- Id does not exist, so error.
162 close csr_id_exists;
163 hr_qsv_shd.constraint_error('HR_QUEST_ANSWER_VALUES_FK2');
164 end if;
165 close csr_id_exists;
166 --
167 hr_utility.set_location(l_proc,20);
168 --
169 -- Check that field_id is unique for the given questionnaire_answer_id
170 --
171 open csr_chk_unique;
172 fetch csr_chk_unique into l_exists;
173 if csr_chk_unique%FOUND then
174 -- field_id is not unique, so error
175 close csr_chk_unique;
176 hr_qsv_shd.constraint_error('HR_QUEST_ANSWER_VALUES_UK1');
177 end if;
178 close csr_chk_unique;
179 --
180 hr_utility.set_location(l_proc,30);
181 --
182 -- Check that qsf.questionnaire_template_id is the same as
183 -- qsa.questionnaire_template_id.
184 --
185 if l_questionnaire_template_id <> g_questionnaire_template_id then
186 -- field is not a part of the questionnaire being answered.
187 fnd_message.set_name('PER','PER_52448_QSV_INVAL_FIELD_ID');
188 fnd_message.raise_error;
189 end if;
190 --
191 end if;
192 --
193 hr_utility.set_location('Leaving: '||l_proc,20);
194 --
195 end chk_field_id;
196 --
197 -- ----------------------------------------------------------------------------
198 -- |-----------------------< chk_delete_allowed >-----------------------------|
199 -- ----------------------------------------------------------------------------
200 --
201 procedure chk_delete_allowed
202 (p_value in hr_quest_answer_values.value%TYPE
203 )
204 is
205 --
206 l_proc varchar2(72) := g_package || 'chk_delete_allowed';
207 --
208 begin
209 --
210 hr_utility.set_location('Entering: '||l_proc,10);
211 --
212 -- Deleted check on p_value to see if it is not null and raise
213 -- error PER_52449_QSV_ANSWER_NOT_NULL, as this is required
214 -- for appraisal deletion using API, to clean up appraisals.
215 -- Bug 3104804.
216 --
217 hr_utility.set_location('Leaving: '||l_proc,20);
218 --
219 end chk_delete_allowed;
220 --
221 -- ----------------------------------------------------------------------------
222 -- |---------------------------< insert_validate >----------------------------|
223 -- ----------------------------------------------------------------------------
224 Procedure insert_validate(p_rec in hr_qsv_shd.g_rec_type) is
225 --
226 l_proc varchar2(72) := g_package||'insert_validate';
227 --
228 Begin
229 hr_utility.set_location('Entering:'||l_proc, 5);
230 --
231 -- Set the business group id
232 --
233 hr_qsa_bus.set_security_group_id
234 (p_questionnaire_answer_id=> p_rec.questionnaire_answer_id);
235 --
236 hr_utility.set_location('Entering:'||l_proc, 7);
237 --
238 -- Reset global g_questionnaire_template_id
239 --
240 g_questionnaire_template_id := null;
241 --
242 -- Call all supporting business operations
243 --
244 chk_questionnaire_answer_id
245 (p_questionnaire_answer_id => p_rec.questionnaire_answer_id);
246 --
247 chk_field_id
248 (p_field_id => p_rec.field_id
249 ,p_questionnaire_answer_id => p_rec.questionnaire_answer_id
250 );
251 --
252 --
253 hr_utility.set_location(' Leaving:'||l_proc, 10);
254 End insert_validate;
255 --
256 -- ----------------------------------------------------------------------------
257 -- |---------------------------< update_validate >----------------------------|
258 -- ----------------------------------------------------------------------------
259 Procedure update_validate(p_rec in hr_qsv_shd.g_rec_type) is
260 --
261 l_proc varchar2(72) := g_package||'update_validate';
262 --
263 Begin
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 -- Set the business group id
267 --
268 hr_qsa_bus.set_security_group_id
269 (p_questionnaire_answer_id=> p_rec.questionnaire_answer_id);
270 --
271 hr_utility.set_location('Entering:'||l_proc, 7);
272 --
273 -- Reset global g_questionnaire_template_id to null
274 --
275 g_questionnaire_template_id := null;
276 --
277 -- Call all supporting business operations
278 --
279 hr_qsv_bus.chk_non_updateable_args(p_rec);
280 --
281 --
282 hr_utility.set_location(' Leaving:'||l_proc, 10);
283 End update_validate;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |---------------------------< delete_validate >----------------------------|
287 -- ----------------------------------------------------------------------------
288 Procedure delete_validate(p_rec in hr_qsv_shd.g_rec_type) is
289 --
290 l_proc varchar2(72) := g_package||'delete_validate';
291 --
292 Begin
293 hr_utility.set_location('Entering:'||l_proc, 5);
294 --
295 -- Call all supporting business operations
296 --
297 chk_delete_allowed
298 (p_value => hr_qsv_shd.g_old_rec.value);
299 --
300 hr_utility.set_location(' Leaving:'||l_proc, 10);
301 End delete_validate;
302 --
303 -- ----------------------------------------------------------------------------
304 -- |---------------------< return_legislation_code >--------------------------|
305 -- ----------------------------------------------------------------------------
306 --
307 function return_legislation_code
308 (p_quest_answer_val_id in hr_quest_answer_values.quest_answer_val_id%TYPE
309 ) return varchar2 is
310 --
311 -- Cursor to find legislation code
312 --
313 cursor csr_leg_code is
314 select pbg.legislation_code
315 from per_business_groups pbg
316 , hr_quest_answers qsa
317 , hr_quest_answer_values qsv
318 where p_quest_answer_val_id = qsv.quest_answer_val_id
319 and qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
320 and qsa.business_group_id = pbg.business_group_id;
321 --
322 -- Declare local variables
323 --
324 l_legislation_code varchar2(150);
325 l_proc varchar2(72) := 'return_legislation_code';
326 --
327 begin
328 --
329 hr_utility.set_location('Entering: '||l_proc, 10);
330 --
331 hr_api.mandatory_arg_error(p_api_name => l_proc
332 ,p_argument => 'quest_answer_val_id'
333 ,p_argument_value => p_quest_answer_val_id
334 );
335 if nvl(g_quest_answer_val_id, hr_api.g_number)
336 = p_quest_answer_val_id then
337 --
338 -- The legislation code has already been found with a previous call
339 -- to this function. Just return the value in the global variable.
340 --
341 l_legislation_code := g_legislation_code;
342 hr_utility.set_location(l_proc,20);
343 else
344 --
345 -- The ID is different to the last call to this function, or this
346 -- is the first call to this function.
347 --
348 open csr_leg_code;
349 fetch csr_leg_code into l_legislation_code;
350 if csr_leg_code%NOTFOUND then
351 --
352 -- The primary key is invalid therefore we must error
353 --
354 close csr_leg_code;
355 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
356 fnd_message.raise_error;
357 end if;
358 hr_utility.set_location(l_proc,30);
359 --
360 -- Set the global variables so the values are available for the
361 -- next call to this function.
362 --
363 close csr_leg_code;
364 g_quest_answer_val_id := p_quest_answer_val_id;
365 g_legislation_code := l_legislation_code;
366 end if;
367 --
368 hr_utility.set_location('Leaving: '||l_proc,40);
369 --
370 return l_legislation_code;
371 end return_legislation_code;
372 --
373 end hr_qsv_bus;