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