[Home] [Help]
PACKAGE BODY: APPS.HR_QSF_BUS
Source
1 Package Body hr_qsf_bus as
2 /* $Header: hrqsfrhi.pkb 115.11 2003/08/27 00:16:45 hpandya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_qsf_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_field_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |-----------------------< chk_non_updateable_args >------------------------|
18 -- ----------------------------------------------------------------------------
19 Procedure chk_non_updateable_args
20 (p_rec in hr_qsf_shd.g_rec_type
21 ,p_effective_date in date
22 )
23 is
24 --
25 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
26 begin
27 hr_utility.set_location('Entering: '||l_proc,10);
28 --
29 -- Only proceed with the validation if a row exists for the current
30 -- record in the HR Schema
31 --
32 if not hr_qsf_shd.api_updating
33 (p_field_id => p_rec.field_id
34 ,p_object_version_number => p_rec.object_version_number
35 ) then
36 fnd_message.set_name('PER','HR_6153_ALL_PROCEDURE_FAIL');
37 fnd_message.set_token('PROCEDURE',l_proc);
38 fnd_message.set_token('STEP','20');
39 fnd_message.raise_error;
40 end if;
41 hr_utility.set_location(l_proc,30);
42 --
43 if nvl(p_rec.questionnaire_template_id,hr_api.g_number) <>
44 hr_qsf_shd.g_old_rec.questionnaire_template_id then
45
46 hr_api.argument_changed_error
47 (p_api_name => l_proc
48 ,p_argument => 'QUESTIONNAIRE_TEMPLATE_ID'
49 ,p_base_table => hr_qsf_shd.g_tab_nam
50 );
51 end if;
52 hr_utility.set_location(l_proc,40);
53 --
54 if nvl(p_rec.name,hr_api.g_varchar2) <> hr_qsf_shd.g_old_rec.name then
55
56 hr_api.argument_changed_error
57 (p_api_name => l_proc
58 ,p_argument => 'NAME'
59 ,p_base_table => hr_qsf_shd.g_tab_nam
60 );
61 end if;
62 hr_utility.set_location(l_proc,50);
63 --
64 if nvl(p_rec.type,hr_api.g_varchar2) <> hr_qsf_shd.g_old_rec.type then
65
66 hr_api.argument_changed_error
67 (p_api_name => l_proc
68 ,p_argument => 'TYPE'
69 ,p_base_table => hr_qsf_shd.g_tab_nam
70 );
71 end if;
72 hr_utility.set_location(l_proc,60);
73 --
74 if nvl(p_rec.html_text,hr_api.g_varchar2)
75 <> hr_qsf_shd.g_old_rec.html_text then
76
77 hr_api.argument_changed_error
78 (p_api_name => l_proc
79 ,p_argument => 'HTML_TEXT'
80 ,p_base_table => hr_qsf_shd.g_tab_nam
81 );
82 end if;
83 hr_utility.set_location(l_proc,70);
84 --
85 if nvl(p_rec.sql_required_flag,hr_api.g_varchar2)
86 <> hr_qsf_shd.g_old_rec.sql_required_flag then
87
88 hr_api.argument_changed_error
89 (p_api_name => l_proc
90 ,p_argument => 'SQL_REQUIRED_FLAG'
91 ,p_base_table => hr_qsf_shd.g_tab_nam
92 );
93 end if;
94 hr_utility.set_location('Leaving: '||l_proc,80);
95 --
96 end chk_non_updateable_args;
97 --
98 --
99 -- ----------------------------------------------------------------------------
100 -- |-------------------< chk_questionnaire_template_id >----------------------|
101 -- ----------------------------------------------------------------------------
102 --
103 Procedure chk_questionnaire_template_id
104 (p_questionnaire_template_id
105 in hr_quest_fields.questionnaire_template_id%TYPE
106 )
107 is
108 --
109 l_proc varchar2(72) := g_package || 'chk_questionnaire_template_id';
110 l_exists varchar2(1);
111 --
112 -- Cursor to check that questionnaire_template_id exists
113 --
114 cursor csr_id_exists is
115 select null
116 from hr_questionnaires
117 where questionnaire_template_id = p_questionnaire_template_id;
118 --
119 begin
120 hr_utility.set_location('Entering: '||l_proc,10);
121 --
122 if p_questionnaire_template_id is not null then
123 -- check that it exists
124 open csr_id_exists;
125 fetch csr_id_exists into l_exists;
126 if csr_id_exists%notfound then
127 -- Questionnaire template id not found - raise error
128 close csr_id_exists;
129 hr_qsf_shd.constraint_error('HR_QUEST_FIELDS_FK');
130 end if;
131 close csr_id_exists;
132 else
133 -- Questionnaire template id is null - raise error
134 fnd_message.set_name('PER','PER_52419_QSF_MAND_TEMPLATE_ID');
135 fnd_message.raise_error;
136 end if;
137 --
138 hr_utility.set_location('Leaving: '||l_proc,50);
139
140 exception when app_exception.application_exception then
141 if hr_multi_message.exception_add
142 (p_associated_column1 => 'HR_QUEST_FIELDS.QUESTIONNAIRE_TEMPLATE_ID'
143 ) then
144 hr_utility.set_location(' Leaving:'|| l_proc, 60);
145 raise;
146 end if;
147 hr_utility.set_location(' Leaving:'|| l_proc, 70);
148 --
149 end chk_questionnaire_template_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |----------------------------< chk_name >----------------------------------|
153 -- ----------------------------------------------------------------------------
154 --
155 Procedure chk_name
156 (p_name in hr_quest_fields.name%TYPE
157 )
158 is
159 --
160 l_proc varchar2(72) := g_package || 'chk_name';
161 --
162 begin
163 --
164 hr_utility.set_location('Entering: '||l_proc,10);
165 --
166 if p_name is null then
167 -- Raise error, since name is a mandatory column.
168 fnd_message.set_name('PER','PER_52423_QSF_MAND_NAME');
169 fnd_message.raise_error;
170 end if;
171 --
172 hr_utility.set_location('Leaving: '||l_proc,20);
173
174 exception when app_exception.application_exception then
175 if hr_multi_message.exception_add
176 (p_associated_column1 => 'HR_QUEST_FIELDS.NAME'
177 ) then
178 hr_utility.set_location(' Leaving:'|| l_proc, 30);
179 raise;
180 end if;
181 hr_utility.set_location(' Leaving:'|| l_proc, 40);
182 --
183 end chk_name;
184 -- ----------------------------------------------------------------------------
185 -- |----------------------------< chk_type >----------------------------------|
186 -- ----------------------------------------------------------------------------
187 --
188 Procedure chk_type
189 (p_type in hr_quest_fields.type%TYPE
190 ,p_effective_date in date
191 ) is
192 --
193 l_proc varchar2(72) := g_package || 'chk_type';
194 --
195 begin
196 --
197 hr_utility.set_location('Entering: '||l_proc,10);
198 --
199 if p_type is not null then
200 -- Check that p_type exists in lookup
201 if hr_api.not_exists_in_hr_lookups
202 (p_effective_date => p_effective_date
203 ,p_lookup_type => 'QUEST_FIELD_TYPE'
204 ,p_lookup_code => p_type
205 ) then
206 -- p_type does not exist in lookup
207 hr_qsf_shd.constraint_error('HR_QUEST_FIELDS_TYPE_CHK');
208 end if;
209 else
210 -- p_type is null
211 fnd_message.set_name('PER','PER_52426_QSF_MAND_TYPE');
212 fnd_message.raise_error;
213 end if;
214 --
215 hr_utility.set_location('Leaving: '||l_proc,20);
216
217 exception when app_exception.application_exception then
218 if hr_multi_message.exception_add
219 (p_associated_column1 => 'HR_QUEST_FIELDS.TYPE'
220 ) then
221 hr_utility.set_location(' Leaving:'|| l_proc, 30);
222 raise;
223 end if;
224 hr_utility.set_location(' Leaving:'|| l_proc, 40);
225 --
226 end chk_type;
227 --
228 -- ---------------------------------------------------------------------------
229 -- |----------------------< chk_html_text >----------------------------------|
230 -- ---------------------------------------------------------------------------
231 --
232 Procedure chk_html_text
233 (p_html_text in hr_quest_fields.html_text%TYPE
234 )
235 is
236 --
237 c_max_size CONSTANT number := 27648;
238 l_proc varchar2(72) := g_package || 'chk_html_text';
239 --
240 begin
241 --
242 hr_utility.set_location('Entering: '||l_proc,10);
243 --
244 if p_html_text is not null then
245 -- Check that the size of the text is less than max_size
246 -- if Length(p_html_text) > c_max_size then
247 -- Greater than max, so error
248 -- fnd_message.set_name('PER','PER_52420_QSF_HTML_TXT_OVRSZD');
249 -- fnd_message.raise_error;
250 -- end if;
251 null;
252 else
253 -- html_text is null
254 fnd_message.set_name('PER','PER_52421_QSF_MAND_HTML_TEXT');
255 fnd_message.raise_error;
256 end if;
257 --
258 hr_utility.set_location('Leaving: '||l_proc,20);
259
260 exception when app_exception.application_exception then
261 if hr_multi_message.exception_add
262 (p_associated_column1 => 'HR_QUEST_FIELDS.HTML_TEXT'
263 ) then
264 hr_utility.set_location(' Leaving:'|| l_proc, 30);
265 raise;
266 end if;
267 hr_utility.set_location(' Leaving:'|| l_proc, 40);
268 --
269 end chk_html_text;
270 --
271 -- ---------------------------------------------------------------------------
272 -- |----------------------< chk_sql_required_flag >--------------------------|
273 -- ---------------------------------------------------------------------------
274 --
275 Procedure chk_sql_required_flag
276 (p_sql_required_flag in hr_quest_fields.sql_required_flag%TYPE
277 ,p_effective_date in date
278 ) is
279 --
280 l_proc varchar2(72) := g_package || 'chk_sql_required_flag';
281 --
282 begin
283 --
284 hr_utility.set_location('Entering: '||l_proc,10);
285 --
286 if p_sql_required_flag is not null then
287 -- Check that it exists in HR_LOOKUPS
288 if hr_api.not_exists_in_hr_lookups
289 (p_effective_date => p_effective_date
290 ,p_lookup_type => 'YES_NO'
291 ,p_lookup_code => p_sql_required_flag
292 ) then
293 -- Doesnt exist in lookups, so error
294 hr_qsf_shd.constraint_error('HR_QUEST_FIELDS_SQL_FLAG_CHK');
295 end if;
296 else
297 -- p_sql_required flag is null, yet is a mandatory column
298 fnd_message.set_name('PER','PER_52422_QSF_MAND_REQD_FLAG');
299 fnd_message.raise_error;
300 end if;
301 --
302 hr_utility.set_location('Leaving: '||l_proc,20);
303
304 exception when app_exception.application_exception then
305 if hr_multi_message.exception_add
306 (p_associated_column1 => 'HR_QUEST_FIELDS.SQL_REQUIRED_FLAG'
307 ) then
308 hr_utility.set_location(' Leaving:'|| l_proc, 30);
309 raise;
310 end if;
311 hr_utility.set_location(' Leaving:'|| l_proc, 40);
312 --
313 end chk_sql_required_flag;
314 --
315 -- ----------------------------------------------------------------------------
316 -- |---------------------------< insert_validate >----------------------------|
317 -- ----------------------------------------------------------------------------
318 Procedure insert_validate(p_rec in hr_qsf_shd.g_rec_type,
319 p_effective_date in date) is
320 --
321 l_proc varchar2(72) := g_package||'insert_validate';
322 --
323 Begin
324 hr_utility.set_location('Entering:'||l_proc, 5);
325 --
326 hr_qsn_bus.set_security_group_id
327 (p_questionnaire_template_id => p_rec.questionnaire_template_id);
328 --
329 hr_utility.set_location('Entering:'||l_proc, 7);
330 -- Call all supporting business operations
331 --
332 chk_questionnaire_template_id(p_rec.questionnaire_template_id);
333 chk_name(p_rec.name);
334 chk_type(p_rec.type, p_effective_date);
335 chk_sql_required_flag(p_rec.sql_required_flag, p_effective_date);
336 chk_html_text(p_rec.html_text);
337
338 hr_multi_message.end_validation_set;
339 --
340 --
341 hr_utility.set_location(' Leaving:'||l_proc, 10);
342 End insert_validate;
343 --
344 -- ----------------------------------------------------------------------------
345 -- |---------------------------< update_validate >----------------------------|
346 -- ----------------------------------------------------------------------------
347 Procedure update_validate(p_rec in hr_qsf_shd.g_rec_type
348 ,p_effective_date in date) is
349 --
350 l_proc varchar2(72) := g_package||'update_validate';
351 --
352 Begin
353 hr_utility.set_location('Entering:'||l_proc, 5);
354 --
355 hr_qsn_bus.set_security_group_id
356 (p_questionnaire_template_id => p_rec.questionnaire_template_id
357 ,p_associated_column1 => hr_qsn_shd.g_tab_nam || '.BUSINESS_GROUP_ID'
358 );
359
360 hr_multi_message.end_validation_set;
361 --
362 hr_utility.set_location('Entering:'||l_proc, 7);
363 -- Call all supporting business operations
364 --
365 chk_non_updateable_args
366 (p_rec => p_rec
367 ,p_effective_date => p_effective_date
368 );
369 --
370 hr_utility.set_location(' Leaving:'||l_proc, 10);
371 End update_validate;
372 --
373 -- ----------------------------------------------------------------------------
374 -- |---------------------------< delete_validate >----------------------------|
375 -- ----------------------------------------------------------------------------
376 Procedure delete_validate(p_rec in hr_qsf_shd.g_rec_type) is
377 --
378 l_proc varchar2(72) := g_package||'delete_validate';
379 --
380 Begin
381 hr_utility.set_location('Entering:'||l_proc, 5);
382 --
383 -- Call all supporting business operations
384 --
385 hr_utility.set_location(' Leaving:'||l_proc, 10);
386 End delete_validate;
387 --
388 -- ----------------------------------------------------------------------------
389 -- |--------------------< return_legislation_code >---------------------------|
390 -- ----------------------------------------------------------------------------
391 Function return_legislation_code
392 (p_field_id in hr_quest_fields.field_id%TYPE
393 ) return varchar2 is
394 --
395 -- Cursor to find legislation code
396 --
397 cursor csr_leg_code is
398 select pbg.legislation_code
399 from per_business_groups pbg
400 , hr_questionnaires qsn
401 , hr_quest_fields qsf
402 where qsf.field_id = p_field_id
403 and qsn.questionnaire_template_id = qsf.questionnaire_template_id
404 and qsn.business_group_id = pbg.business_group_id;
405 --
406 -- Declare local variables
407 --
408 l_legislation_code varchar2(150);
409 l_proc varchar2(72) := 'return_legislation_code';
410 begin
411 hr_utility.set_location('Entering: '||l_proc,10);
412 hr_api.mandatory_arg_error(p_api_name => l_proc
413 ,p_argument => 'field_id'
414 ,p_argument_value => p_field_id
415 );
416 if nvl(g_field_id, hr_api.g_number) = p_field_id then
417 --
418 -- The legislation code has already been found with a previous
419 -- call to this function. Just return the value in the global
420 -- variable.
421 --
422 l_legislation_code := g_legislation_code;
423 hr_utility.set_location(l_proc,20);
424 else
425 --
426 -- The ID is different to the last call to this function
427 -- or this is the first call to this function.
428 --
429 open csr_leg_code;
430 fetch csr_leg_code into l_legislation_code;
431 if csr_leg_code%notfound then
432 --
433 -- The primary key is invalid, therefore we must error
434 --
435 close csr_leg_code;
436 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
437 fnd_message.raise_error;
438 end if;
439 hr_utility.set_location(l_proc,30);
440 --
441 -- Set the global variables so the values are available
442 -- for the next call to this function.
443 --
444 close csr_leg_code;
445 g_field_id := p_field_id;
446 g_legislation_code := l_legislation_code;
447 end if;
448 hr_utility.set_location(' Leaving: '||l_proc, 40);
449 --
450 return l_legislation_code;
451 end return_legislation_code;
452 --
453 end hr_qsf_bus;