[Home] [Help]
PACKAGE BODY: APPS.HR_QSN_BUS
Source
1 Package Body hr_qsn_bus as
2 /* $Header: hrqsnrhi.pkb 120.4.12010000.3 2008/11/05 09:57:56 rsykam ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_qsn_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_questionnaire_template_id number default null;
15 --
16 --
17 --
18 -- ----------------------------------------------------------------------------
19 -- |------------------------< set_security_group_id >-------------------------|
20 -- ----------------------------------------------------------------------------
21 --
22 Procedure set_security_group_id
23 (p_questionnaire_template_id in number
24 ,p_associated_column1 in varchar2 default null
25 ) is
26 --
27 -- Declare cursor
28 --
29 cursor csr_sec_grp is
30 select inf.org_information14
31 from hr_organization_information inf
32 , hr_questionnaires qsn
33 where qsn.questionnaire_template_id = p_questionnaire_template_id
34 and inf.organization_id = qsn.business_group_id(+)
35 and inf.org_information_context || '' = 'Business Group Information';
36 -- order by per.effective_start_date;
37 --
38 -- Local variables
39 --
40 l_security_group_id number;
41 l_proc varchar2(72) := g_package||'set_security_group_id';
42 begin
43
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 --
46 -- Ensure that all the mandatory parameter are not null
47 --
48 hr_api.mandatory_arg_error(p_api_name => l_proc,
49 p_argument => 'questionnaire_template_id',
50 p_argument_value => p_questionnaire_template_id);
51 --
52 open csr_sec_grp;
53 fetch csr_sec_grp into l_security_group_id;
54 close csr_sec_grp;
55 -- Set the security_group_id in CLIENT_INFO
56 --
57 hr_api.set_security_group_id
58 (p_security_group_id => l_security_group_id
59 );
60
61 --
62 hr_utility.set_location(' Leaving:'|| l_proc, 20);
63
64 end set_security_group_id;
65 -- ----------------------------------------------------------------------------
66 -- |---------------------< chk_non_updateable_args >--------------------------|
67 -- ----------------------------------------------------------------------------
68 Procedure chk_non_updateable_args
69 (p_rec in hr_qsn_shd.g_rec_type
70 ) is
71 --
72 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
73 --
74 Begin
75 hr_utility.set_location('Entering:'||l_proc,10);
76 --
77 -- Only proceed with validation if a row exists for
78 -- the current record in the HR Schema
79 --
80 if not hr_qsn_shd.api_updating
81 (p_questionnaire_template_id => p_rec.questionnaire_template_id
82 ,p_object_version_number => p_rec.object_version_number
83 ) then
84 fnd_message.set_name('PER','HR_6153_ALL_PROCEDURE_FAIL');
85 fnd_message.set_token('PROCEDURE',l_proc);
86 fnd_message.set_token('STEP',20);
87 fnd_message.raise_error;
88 end if;
89 hr_utility.set_location(l_proc,30);
90 --
91 if nvl(p_rec.name,hr_api.g_varchar2) <> hr_qsn_shd.g_old_rec.name then
92 hr_api.argument_changed_error
93 (p_api_name => l_proc
94 ,p_argument => 'NAME'
95 ,p_base_table => hr_qsn_shd.g_tab_nam
96 );
97 end if;
98 hr_utility.set_location(l_proc,40);
99 --
100 /**
101 if nvl(p_rec.text,hr_api.g_varchar2) <> hr_qsn_shd.g_old_rec.text then
102 hr_api.argument_changed_error
103 (p_api_name => l_proc
104 ,p_argument => 'TEXT'
105 ,p_base_table => hr_qsn_shd.g_tab_nam
106 );
107 end if;
108 hr_utility.set_location(l_proc,50);
109 --
110 if nvl(p_rec.business_group_id, hr_api.g_number) <>
111 hr_qsn_shd.g_old_rec.business_group_id then
112 hr_api.argument_changed_error
113 (p_api_name => l_proc
114 ,p_argument => 'BUSINESS_GROUP_ID'
115 ,p_base_table => hr_qsn_shd.g_tab_nam
116 );
117 end if;
118 **/
119 --
120 hr_utility.set_location('Leaving: '|| l_proc,60);
121 --
122 end chk_non_updateable_args;
123 -- --------------------------------------------------------------------------
124 -- |------------------------< chk_name >------------------------------------|
125 -- --------------------------------------------------------------------------
126 --
127 Procedure chk_name
128 (p_name in hr_questionnaires.name%TYPE
129 ,p_business_group_id in hr_questionnaires.name%TYPE
130 )
131 is
132 --
133 l_proc varchar2(72) := g_package || 'chk_name';
134 l_exists varchar2(1);
135 --
136 -- Cursor to check that name is unique.
137 --
138 cursor csr_unique_name is
139 select null
140 from hr_questionnaires
141 where name = p_name
142 and ((p_business_group_id is null and business_group_id is null)
143 or business_group_id = p_business_group_id);
144 --
145 begin
146 hr_utility.set_location('Entering: '||l_proc,10);
147 --
148 if p_name is not null then
149 -- Check that name is unique
150 open csr_unique_name;
151 fetch csr_unique_name into l_exists;
152 if csr_unique_name%FOUND then
153 -- Name is not unique - raise error by calling constraint error
154 close csr_unique_name;
155 hr_qsn_shd.constraint_error('HR_QUESTIONNAIRES_UK1');
156 end if;
157 close csr_unique_name;
158 else
159 -- Name is null - raise error
160 fnd_message.set_name('PER','PER_52412_QSN_MAND_NAME');
161 fnd_message.raise_error;
162 end if;
163 --
164 hr_utility.set_location(' Leaving: '||l_proc, 50);
165 exception when app_exception.application_exception then
166 if hr_multi_message.exception_add
167 (p_associated_column1 => 'HR_QUESTIONNAIRES.NAME'
168 ) then
169 hr_utility.set_location(' Leaving:'|| l_proc, 60);
170 raise;
171 end if;
172 hr_utility.set_location(' Leaving:'|| l_proc, 70);
173 end chk_name;
174 --
175 -- ---------------------------------------------------------------------------
176 -- |----------------------------< chk_text >---------------------------------|
177 -- ---------------------------------------------------------------------------
178 --
179 Procedure chk_text
180 (p_text in hr_questionnaires.text%TYPE
181 )
182 is
183 --
184 c_max_size CONSTANT number := 27648;
185 l_proc varchar2(72) := g_package || 'chk_text';
186 --
187 begin
188 --
189 hr_utility.set_location('Entering: '||l_proc,10);
190 --
191 if p_text is not null then
192 -- Check the size of the text is less than max size
193 -- if Length(p_text) > c_max_size then
194 -- greater then max, so raise error
195 -- fnd_message.set_name('PER','PER_52414_QSN_TEXT_OVERSIZED');
196 -- fnd_message.raise_error;
197 -- end if;
198 null;
199 else
200 -- Text is mandatory, thus error
201 fnd_message.set_name('PER','PER_52413_QSN_MAND_TEXT');
202 fnd_message.raise_error;
203 end if;
204 --
205 hr_utility.set_location('Leaving: '||l_proc,50);
206 exception when app_exception.application_exception then
207 if hr_multi_message.exception_add
208 (p_associated_column1 => 'HR_QUESTIONNAIRES.TEXT'
209 ) then
210 hr_utility.set_location(' Leaving:'|| l_proc, 60);
211 raise;
212 end if;
213 hr_utility.set_location(' Leaving:'|| l_proc, 70);
214
215 --
216 end chk_text;
217 --
218 -- ---------------------------------------------------------------------------
219 -- |----------------------< chk_available_flag >-----------------------------|
220 -- ---------------------------------------------------------------------------
221 --
222 Procedure chk_available_flag
223 (p_available_flag in hr_questionnaires.available_flag%TYPE
224 ,p_effective_date in date
225 ,p_questionnaire_template_id in hr_questionnaires.questionnaire_template_id%TYPE
226 ,p_object_version_number in hr_questionnaires.object_version_number%TYPE
227 )
228 is
229 --
230 l_proc varchar2(72) := g_package ||'chk_available_flag';
231 l_api_updating boolean;
232 --
233 begin
234 --
235 hr_utility.set_location('Entering: '||l_proc,10);
236 --
237 l_api_updating := hr_qsn_shd.api_updating
238 (p_questionnaire_template_id => p_questionnaire_template_id
239 ,p_object_version_number => p_object_version_number);
240 hr_utility.set_location(l_proc,20);
241 --
242 if l_api_updating AND
243 (hr_qsn_shd.g_old_rec.available_flag
244 <> nvl(p_available_flag,hr_api.g_varchar2)) then
245 -- During update, and available_flag has changed
246 --
247 if hr_api.not_exists_in_hr_lookups
248 (p_effective_date => p_effective_date
249 ,p_lookup_type => 'YES_NO'
250 ,p_lookup_code => p_available_flag
251 ) then
252 -- Invalid lookup, raise error
253 hr_qsn_shd.constraint_error('HR_QUEST_AVAILABLE_FLAG_CHK');
254 end if;
255 -- We are allowing users in V4 SSHR to publish the questionnaire
256 -- during the process of creation itself. hence foll. check is not required
257 /* elsif not l_api_updating then
258 -- INSERT - validate that flag is 'N'
259 if nvl(p_available_flag,hr_api.g_varchar2) <> 'N' then
260 fnd_message.set_name('PER','PER_52415_QSN_INVAL_FLG_ON_INS');
261 fnd_message.raise_error;
262 end if; */
263 else
264 -- UPDATE and not changed - no need to validate
265 null;
266 end if;
267 --
268 hr_utility.set_location('Leaving: '||l_proc,50);
269 exception when app_exception.application_exception then
270 if hr_multi_message.exception_add
271 (p_associated_column1 => 'HR_QUESTIONNAIRES.AVAILABLE_FLAG'
272 ) then
273 hr_utility.set_location(' Leaving:'|| l_proc, 60);
274 raise;
275 end if;
276 hr_utility.set_location(' Leaving:'|| l_proc, 70);
277 --
278 end chk_available_flag;
279 --
280 -- ----------------------------------------------------------------------------
281 -- |-------------------------< chk_row_delete >-------------------------------|
282 -- ----------------------------------------------------------------------------
283 procedure chk_row_delete
284 (p_questionnaire_template_id in hr_questionnaires.questionnaire_template_id%TYPE)
285 is
286 --
287 l_proc varchar2(72) := g_package || 'chk_row_delete';
288 l_exists varchar2(1);
289 --
290 -- Cursor to check whether a child row exists in HR_QUEST_FIELDS
291 --
292 cursor csr_chk_child_row is
293 select null
294 from hr_quest_fields qsf
295 where qsf.questionnaire_template_id = p_questionnaire_template_id;
296 --
297 begin
298 --
299 hr_utility.set_location('Entering: '||l_proc,10);
300 --
301 hr_api.mandatory_arg_error
302 (p_api_name => l_proc
303 ,p_argument => 'questionnaire_template_id'
304 ,p_argument_value => p_questionnaire_template_id
305 );
306 -- determine if a child row exists
307 open csr_chk_child_row;
308 fetch csr_chk_child_row into l_exists;
309 if csr_chk_child_row%FOUND then
310 -- Raise error, as child row exists
311 close csr_chk_child_row;
312 fnd_message.set_name('PER','PER_52442_QSA_CHILD_EXISTS');
313 fnd_message.raise_error;
314 end if;
315 close csr_chk_child_row;
316 --
317 hr_utility.set_location('Leaving: '||l_proc, 20);
318 exception when app_exception.application_exception then
319 if hr_multi_message.exception_add
320 (p_associated_column1 => 'HR_QUESTIONNAIRES.AVAILABLE_FLAG'
321 ) then
322 hr_utility.set_location(' Leaving:'|| l_proc, 30);
323 raise;
324 end if;
325 hr_utility.set_location(' Leaving:'|| l_proc, 40);
326 --
327 end chk_row_delete;
328 -- ----------------------------------------------------------------------------
329 -- |---------------------------< insert_validate >----------------------------|
330 -- ----------------------------------------------------------------------------
331 Procedure insert_validate(p_rec in hr_qsn_shd.g_rec_type
332 ,p_effective_date in date
333 ) is
334 --
335 l_proc varchar2(72) := g_package||'insert_validate';
336 --
337 Begin
338 hr_utility.set_location('Entering:'||l_proc, 5);
339 --
340 -- Validate Important Attributes
341 --
342 if p_rec.business_group_id is not null then
343 hr_api.validate_bus_grp_id
344 (p_rec.business_group_id
345 ,p_associated_column1 => hr_qsn_shd.g_tab_nam
346 || '.BUSINESS_GROUP_ID');
347 end if;
348 chk_name(p_name => p_rec.name
349 ,p_business_group_id => p_rec.business_group_id
350 );
351 chk_available_flag(p_available_flag => p_rec.available_flag
352 ,p_effective_date => p_effective_date
353 ,p_questionnaire_template_id
354 => p_rec.questionnaire_template_id
355 ,p_object_version_number
356 => p_rec.object_version_number
357 );
358
359 chk_text(p_text => p_rec.text);
360
361 hr_multi_message.end_validation_set;
362
363 --
364 hr_utility.set_location(' Leaving:'||l_proc, 10);
365 End insert_validate;
366 --
367 -- ----------------------------------------------------------------------------
368 -- |---------------------------< update_validate >----------------------------|
369 -- ----------------------------------------------------------------------------
370 Procedure update_validate(p_rec in hr_qsn_shd.g_rec_type
371 ,p_effective_date in date
372 ) is
373 --
374 l_proc varchar2(72) := g_package||'update_validate';
375 --
376 Begin
377 hr_utility.set_location('Entering:'||l_proc, 5);
378 --
379 -- Call all supporting business operations
380 --
381 if p_rec.business_group_id is not null then
382 hr_api.validate_bus_grp_id
383 (p_business_group_id => p_rec.business_group_id
384 ,p_associated_column1 => hr_qsn_shd.g_tab_nam
385 || '.BUSINESS_GROUP_ID');
386 end if;
387 hr_multi_message.end_validation_set;
388
389 chk_non_updateable_args(p_rec);
390
391 chk_available_flag(p_rec.available_flag
392 ,p_effective_date
393 ,p_rec.questionnaire_template_id
394 ,p_rec.object_version_number
395 );
396 --
397 hr_utility.set_location(' Leaving:'||l_proc, 10);
398 End update_validate;
399 --
400 -- ----------------------------------------------------------------------------
401 -- |---------------------------< delete_validate >----------------------------|
402 -- ----------------------------------------------------------------------------
403 Procedure delete_validate(p_rec in hr_qsn_shd.g_rec_type) is
404 --
405 l_proc varchar2(72) := g_package||'delete_validate';
406 --
407 Begin
408 hr_utility.set_location('Entering:'||l_proc, 5);
409 --
410 -- Call all supporting business operations
411 --
412 chk_row_delete(p_questionnaire_template_id => p_rec.questionnaire_template_id);
413 hr_utility.set_location(' Leaving:'||l_proc, 10);
414 End delete_validate;
415 --
416 -- ----------------------------------------------------------------------------
417 -- |--------------------< return_legislation_code >---------------------------|
418 -- ----------------------------------------------------------------------------
419 Function return_legislation_code
420 (p_questionnaire_template_id in hr_questionnaires.questionnaire_template_id%TYPE
421 ) return varchar2 is
422 --
423 -- Cursor to find legislation code
424 --
425 cursor csr_leg_code is
426 select pbg.legislation_code
427 from per_business_groups pbg
428 , hr_questionnaires qsn
429 where qsn.questionnaire_template_id = p_questionnaire_template_id
430 and pbg.business_group_id = qsn.business_group_id;
431 --
432 -- Declare local variables
433 --
434 l_legislation_code varchar2(150);
435 l_proc varchar2(72) := 'return_legislation_code';
436 begin
437 hr_utility.set_location('Entering: '||l_proc, 10);
438 hr_api.mandatory_arg_error(p_api_name => l_proc
439 ,p_argument => 'questionnaire_template_id'
440 ,p_argument_value => p_questionnaire_template_id
441 );
442 if nvl(g_questionnaire_template_id, hr_api.g_number)
443 = p_questionnaire_template_id then
444 --
445 -- The legislation code has already been found with a previous
446 -- call to this function. Just return the value in the global
447 -- variable.
448 --
449 l_legislation_code := g_legislation_code;
450 hr_utility.set_location(l_proc,20);
451 else
452 --
453 -- The ID is different to the last call to this function
454 -- or this is the first call to this function.
455 --
456 open csr_leg_code;
457 fetch csr_leg_code into l_legislation_code;
458 if csr_leg_code%notfound then
459 --
460 -- The primary key is invalid therefore we must error
461 --
462 close csr_leg_code;
463 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
464 fnd_message.raise_error;
465 end if;
466 hr_utility.set_location(l_proc, 30);
467 --
468 -- Set the global variables so the values are
469 -- available for the next call to this function
470 --
471 close csr_leg_code;
472 g_questionnaire_template_id := p_questionnaire_template_id;
473 g_legislation_code := l_legislation_code;
474 end if;
475 hr_utility.set_location(' Leaving: '||l_proc, 40);
476 --
477 return l_legislation_code;
478 end return_legislation_code;
479 end hr_qsn_bus;