[Home] [Help]
PACKAGE BODY: APPS.HR_QSA_BUS
Source
1 Package Body hr_qsa_bus as
2 /* $Header: hrqsarhi.pkb 115.12 2003/08/27 00:16:05 hpandya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_qsa_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_questionnaire_answer_id number default null;
15 --
16 --
17 -- ----------------------------------------------------------------------------
18 -- |------------------------< set_security_group_id >-------------------------|
19 -- ----------------------------------------------------------------------------
20 --
21 procedure set_security_group_id
22 (p_questionnaire_answer_id in hr_quest_answers.questionnaire_answer_id%TYPE
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select inf.org_information14
29 from hr_organization_information inf
30 , hr_quest_answers qsa
31 where qsa.questionnaire_answer_id = p_questionnaire_answer_id
32 and inf.organization_id = qsa.business_group_id
33 and inf.org_information_context || '' = 'Business Group Information';
34 --
35 -- Local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 begin
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error(p_api_name => l_proc,
45 p_argument => 'questionnaire_answer_id',
46 p_argument_value => p_questionnaire_answer_id);
47 --
48 open csr_sec_grp;
49 fetch csr_sec_grp into l_security_group_id;
50 if csr_sec_grp%notfound then
51 close csr_sec_grp;
52 --
53 -- The primary key is invalid therefore we must error
54 --
55 hr_utility.set_message('PER', 'HR_7220_INVALID_PRIMARY_KEY');
56 hr_utility.raise_error;
57 end if;
58 close csr_sec_grp;
59 --
60 -- Set the security_group_id in CLIENT_INFO
61 --
62 hr_api.set_security_group_id
63 (p_security_group_id => l_security_group_id
64 );
65 --
66 hr_utility.set_location(' Leaving:'|| l_proc, 20);
67 end set_security_group_id;
68 -- ----------------------------------------------------------------------------
69 -- |-------------------< chk_questionnaire_template_id >----------------------|
70 -- ----------------------------------------------------------------------------
71 --
72 -- Description:
73 -- Validates that the questionnaire_template_id exists in HR_QUESTIONNAIRES.
74 -- Also, validates that the questionnaire_template_id is valid against the
75 -- PER_APPRAISAL_TEMPLATES table, when type = 'APPRAISAL', or valid against
76 -- the PER_APPRAISALS table when type = 'PARTICIPANT'.
77 --
78 -- Pre-requisites:
79 -- p_type, p_type_object_id and p_business_group_id are all valid.
80 --
81 -- IN Parameters:
82 -- p_questionnaire_template_id
83 -- p_type
84 -- p_type_object_id
85 -- p_business_group_id
86 --
87 -- Post Success:
88 -- Processing continues if questionnaire_template_id is valid.
89 --
90 -- Post Failure:
91 -- An application error is raised, and processing is terminated if the
92 -- questionniare_template_id is invalid.
93 --
94 -- Developer/Implementation Notes:
95 -- None.
96 --
97 -- Access Status:
98 -- Internal Row Handler Use Only.
99 --
100 procedure chk_questionnaire_template_id
101 (p_questionnaire_template_id in
102 HR_QUEST_ANSWERS.questionnaire_template_id%TYPE
103 ,p_type in
104 HR_QUEST_ANSWERS.type%TYPE
105 ,p_type_object_id in
106 HR_QUEST_ANSWERS.type_object_id%TYPE
107 ,p_business_group_id in
108 HR_QUEST_ANSWERS.business_group_id%TYPE
109 )
110 is
111 --
112 l_proc varchar2(72) := 'chk_questionnaire_template_id';
113 l_exists varchar2(1);
114 l_bus_grp_id HR_QUEST_ANSWERS.business_group_id%TYPE;
115 --
116 -- Cursor to determins if questionnaire_template_id exists in
117 -- HR_QUESTIONNAIRES.
118 --
119 cursor csr_id_exists is
120 select business_group_id
121 from hr_questionnaires qsn
122 where qsn.questionnaire_template_id = p_questionnaire_template_id;
123 --
124 -- Cursor to determine if questionnaire_template_id is valid when
125 -- the type = 'APPRAISAL'
126 --
127 cursor csr_appraisal_valid is
128 select null
129 from per_appraisal_templates pat,
130 per_appraisals pa
131 where p_questionnaire_template_id = pat.questionnaire_template_id
132 and p_type_object_id = pa.appraisal_id
133 and pa.appraisal_template_id = pat.appraisal_template_id;
134 --
135 --
136
137 --
138 begin
139 --
140 hr_utility.set_location('Entering: '||l_proc, 10);
141 --
142 if p_questionnaire_template_id is null then
143 -- Item is a mandatory parameter, thus error.
144 fnd_message.set_name('PER','PER_52430_QSA_MAND_TEMPLATE');
145 fnd_message.raise_error;
146 else
147 hr_utility.set_location(l_proc,20);
148 --
149 -- Check item exists in hr_questionnaires.
150 open csr_id_exists;
151 fetch csr_id_exists into l_bus_grp_id;
152 if csr_id_exists%NOTFOUND then
153 -- questionnaire_template_id doesnt exist in hr_questionnaires
154 close csr_id_exists;
155 hr_qsa_shd.constraint_error('HR_QUEST_ANSWERS_FK1');
156 end if;
157 close csr_id_exists;
158 --
159 hr_utility.set_location(l_proc,30);
160 --
161 if p_type = 'APPRAISAL' then
162 -- Check that questionnaire_template_id is valid against
163 -- PER_APPRAISAL_TEMPLATES
164 open csr_appraisal_valid;
165 fetch csr_appraisal_valid into l_exists;
166 if csr_appraisal_valid%NOTFOUND then
167 -- Invalid questionnaire_template_id, according to
168 -- per_appraisal_templates and per_appraisals.
169 close csr_appraisal_valid;
170 fnd_message.set_name('PER','PER_52431_QSA_INVAL_TEMP_ID');
171 fnd_message.raise_error;
172 end if;
173 close csr_appraisal_valid;
174 --
175 hr_utility.set_location(l_proc,40);
176 --
177 elsif p_type = 'PARTICIPANT' then
178 --
179 hr_utility.set_location(l_proc,50);
180 --
181 --
182 hr_utility.set_location(l_proc,60);
183 --
184 else
185 -- currently no other types, so do nothing.
186 null;
187 end if;
188 -- rbanda
189 -- disabling this check as Participants from different BG's
190 -- will be taking part in the Appraisal process and when they
191 -- answer Questionnaire the BGId will be the Participants BGId
192 -- but the Questionnaire Template will be from Appraisals BG
193 --
194 -- Check that business group = questionnaire templates business group.
195 /*
196 if l_bus_grp_id <> p_business_group_id then
197 -- Invalid business group
198 fnd_message.set_name('PER','PER_52440_QSA_TEMPLT_NOT_IN_BG');
199 fnd_message.raise_error;
200 end if;
201 */
202 --
203 hr_utility.set_location(l_proc,70);
204 --
205 end if;
206 --
207 hr_utility.set_location('Leaving: '||l_proc,80);
208 --
209 end chk_questionnaire_template_id;
210 --
211 -- ----------------------------------------------------------------------------
212 -- |--------------------------< chk_type >------------------------------------|
213 -- ----------------------------------------------------------------------------
214 procedure chk_type
215 (p_type in HR_QUEST_ANSWERS.TYPE%TYPE
216 ,p_effective_date in date
217 )
218 is
219 --
220 l_proc varchar2(72) := g_package || 'chk_type';
221 --
222 begin
223 --
224 hr_utility.set_location('Entering: '||l_proc,10);
225 --
226 if p_type is not null then
227 -- Check that p_type exists in lookup
228 if hr_api.not_exists_in_hr_lookups
229 (p_effective_date => p_effective_date
230 ,p_lookup_type => 'QUEST_OBJECT_TYPE'
231 ,p_lookup_code => p_type
232 ) then
233 -- p_type does not exist in lookup
234 hr_qsa_shd.constraint_error('HR_QUEST_ANS_TYPE_CHK');
235 end if;
236 else
237 -- p_type is null
238 fnd_message.set_name('PER','PER_52432_QSA_MAND_TYPE');
239 fnd_message.raise_error;
240 end if;
241 --
242 hr_utility.set_location('Leaving: '||l_proc,20);
243 --
244 end chk_type;
245 --
246 -- ----------------------------------------------------------------------------
247 -- |----------------------< chk_type_object_id >------------------------------|
248 -- ----------------------------------------------------------------------------
249 procedure chk_type_object_id
250 (p_type_object_id in HR_QUEST_ANSWERS.type_object_id%TYPE
251 ,p_type in HR_QUEST_ANSWERS.type%TYPE
252 ,p_business_group_id in HR_QUEST_ANSWERS.business_group_id%TYPE
253 )
254 is
255 --
256 l_proc varchar2(72):= g_package || 'chk_type_object_id';
257 l_bus_grp_id HR_QUEST_ANSWERS.business_group_id%TYPE;
258 l_part_in_tab PER_PARTICIPANTS.participation_in_table%TYPE;
259 l_part_in_col PER_PARTICIPANTS.participation_in_column%TYPE;
260 l_exists varchar2(1);
261 lv_cross_business_group VARCHAR2(10); -- bug 1980440 fix
262 --
263 -- Cursor to determine if type_object_id is valid when
264 -- type = 'APPRAISAL'
265 --
266 cursor csr_type_appraisal is
267 select business_group_id
268 from per_appraisals pa
269 where p_type_object_id = pa.appraisal_id;
270 --
271 -- Cursor to determine if type_object_id is valid when
272 -- type = 'PARTICIPANT'
273 --
274 cursor csr_type_participant is
275 select business_group_id, participation_in_table, participation_in_column
276 from per_participants pp
277 where p_type_object_id = pp.participant_id;
278 --
279 -- Cursor to check that type_object_id is unique
280 -- for the given type.
281 --
282 cursor csr_unique_id is
283 select null
284 from hr_quest_answers qsa
285 where qsa.type_object_id = p_type_object_id
286 and qsa.type = p_type;
287 begin
288 --
289 hr_utility.set_location('Entering: '||l_proc, 10);
290 --
291 if p_type_object_id is null then
292 -- type_object_id is mandatory, thus raise error.
293 fnd_message.set_name('PER','PER_52433_QSA_MAND_OBJECT_ID');
294 fnd_message.raise_error;
295 else
296 -- Check that type_object_id is unique.
297 open csr_unique_id;
298 fetch csr_unique_id into l_exists;
299 if csr_unique_id%FOUND then
300 close csr_unique_id;
301 hr_qsa_shd.constraint_error('HR_QUEST_ANSWERS_UK1');
302 end if;
303 close csr_unique_id;
304 --
305 hr_utility.set_location(l_proc,20);
306 --
307 if p_type = 'APPRAISAL' then
308 --
309 hr_utility.set_location(l_proc,30);
310 --
311 -- Check type_object_id for APPRAISAL
312 open csr_type_appraisal;
313 fetch csr_type_appraisal into l_bus_grp_id;
314 if csr_type_appraisal%NOTFOUND then
315 -- type_object_id doesnt exist as an appraisal_id
316 close csr_type_appraisal;
317 fnd_message.set_name('PER','PER_52434_QSA_INVAL_TYPE_OBJ');
318 fnd_message.raise_error;
319 else
320 --
321 hr_utility.set_location(l_proc,40);
322 --
323 close csr_type_appraisal;
324 if l_bus_grp_id <> p_business_group_id then
325 -- Appraisal doesnt exist in the given business group
326 fnd_message.set_name('PER','PER_52435_QSA_OBJ_NOT_IN_BG');
327 fnd_message.raise_error;
328 end if;
329 end if;
330 elsif p_type = 'PARTICIPANT' then
331 --
332 hr_utility.set_location(l_proc,50);
333 --
334 lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP'); -- bug 1980440 fix
335 -- Check type_object_id for PARTICIPANT
336 open csr_type_participant;
337 fetch csr_type_participant
338 into l_bus_grp_id, l_part_in_tab, l_part_in_col;
339 if csr_type_participant%NOTFOUND then
340 -- type object_id doesnt exist as a participant_id
341 close csr_type_participant;
342 fnd_message.set_name('PER','PER_52436_QSA_OBJ_ID_INVALID');
343 fnd_message.raise_error;
344 else
345 close csr_type_participant;
346 --
347 hr_utility.set_location(l_proc,60);
348 -- bug 1980440 fix starts
349 if lv_cross_business_group <> 'Y' THEN
350 if l_bus_grp_id <> p_business_group_id then
351 -- Participant doesnt exist in the given business group
352 fnd_message.set_name('PER','PER_52437_QSA_OBJ_NOT_IN_BG');
353 fnd_message.raise_error;
354 end if;
355 end if;
356 -- bug 1980440 fix ends
357 --
358 hr_utility.set_location(l_proc,70);
359 --
360 if (l_part_in_tab <> 'PER_APPRAISALS') and
361 (l_part_in_col <> 'APPRAISAL_ID') then
362 -- Invalid combination, raise error.
363 fnd_message.set_name('PER','PER_52438_QSA_OBJ_NOT_IN_APR');
364 fnd_message.raise_error;
365 end if;
366 end if;
367 else
368 --
369 hr_utility.set_location(l_proc,80);
370 --
371 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
372 fnd_message.set_token('PROCEDURE',l_proc);
373 fnd_message.set_token('STEP','80');
374 fnd_message.raise_error;
375 end if;
376 --
377 hr_utility.set_location('Leaving: '||l_proc,90);
378 end if;
379 --
380 end chk_type_object_id;
381 --
382 -- ----------------------------------------------------------------------------
383 -- |-------------------------< chk_row_delete >-------------------------------|
384 -- ----------------------------------------------------------------------------
385 procedure chk_row_delete
386 (p_questionnaire_answer_id
387 in hr_quest_answers.questionnaire_answer_id%TYPE
388 )
389 is
390 --
391 l_proc varchar2(72) := g_package || 'chk_row_delete';
392 l_exists varchar2(1);
393 --
394 -- Cursor to check whether a child row exists in HR_QUEST_ANSWER_VALUES
395 --
396 cursor csr_chk_child_row is
397 select null
398 from hr_quest_answer_values qsv
399 where qsv.questionnaire_answer_id = p_questionnaire_answer_id;
400 --
401 begin
402 --
403 hr_utility.set_location('Entering: '||l_proc,10);
404 --
405 hr_api.mandatory_arg_error
406 (p_api_name => l_proc
407 ,p_argument => 'questionnaire_answer_id'
408 ,p_argument_value => p_questionnaire_answer_id
409 );
410
411 -- determine if a child row exists
412 open csr_chk_child_row;
413 fetch csr_chk_child_row into l_exists;
414 if csr_chk_child_row%FOUND then
415 -- Raise error, as child row exists
416 close csr_chk_child_row;
417 fnd_message.set_name('PER','PER_52442_QSA_CHILD_EXISTS');
418 fnd_message.raise_error;
419 end if;
420 close csr_chk_child_row;
421 --
422 hr_utility.set_location('Leaving: '||l_proc, 20);
423 --
424 end chk_row_delete;
425 --
426 -- ----------------------------------------------------------------------------
427 -- |---------------------------< insert_validate >----------------------------|
428 -- ----------------------------------------------------------------------------
429 Procedure insert_validate(p_rec in hr_qsa_shd.g_rec_type
430 ,p_effective_date in date
431 ) is
432 --
433 l_proc varchar2(72) := g_package||'insert_validate';
434 --
435 Begin
436 hr_utility.set_location('Entering:'||l_proc, 5);
437 --
438 -- Call all supporting business operations
439 --
440 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
441 --
442 chk_type(p_type => p_rec.type
443 ,p_effective_date => p_effective_date
444 );
445 --
446 chk_type_object_id(p_type_object_id => p_rec.type_object_id
447 ,p_type => p_rec.type
448 ,p_business_group_id => p_rec.business_group_id
449 );
450 --
451 chk_questionnaire_template_id
452 (p_questionnaire_template_id => p_rec.questionnaire_template_id
453 ,p_type => p_rec.type
454 ,p_type_object_id => p_rec.type_object_id
455 ,p_business_group_id => p_rec.business_group_id
456 );
457 --
458 hr_utility.set_location(' Leaving:'||l_proc, 10);
459 End insert_validate;
460 --
461 -- ----------------------------------------------------------------------------
462 -- |---------------------------< update_validate >----------------------------|
463 -- ----------------------------------------------------------------------------
464 Procedure update_validate(p_rec in hr_qsa_shd.g_rec_type
465 ,p_effective_date in date) is
466 --
467 l_proc varchar2(72) := g_package||'update_validate';
468 --
469 Begin
470 hr_utility.set_location('Entering:'||l_proc, 5);
471 --
472 -- Call all supporting business operations
473 --
474 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
475 --
476 hr_utility.set_location(' Leaving:'||l_proc, 10);
477 End update_validate;
478 --
479 -- ----------------------------------------------------------------------------
480 -- |---------------------------< delete_validate >----------------------------|
481 -- ----------------------------------------------------------------------------
482 Procedure delete_validate(p_rec in hr_qsa_shd.g_rec_type) is
483 --
484 l_proc varchar2(72) := g_package||'delete_validate';
485 --
486 Begin
487 hr_utility.set_location('Entering:'||l_proc, 5);
488 --
489 -- Call all supporting business operations
490 --
491 chk_row_delete(p_questionnaire_answer_id
492 => p_rec.questionnaire_answer_id);
493 --
494 hr_utility.set_location(' Leaving:'||l_proc, 10);
495 End delete_validate;
496 --
497 -- ----------------------------------------------------------------------------
498 -- |--------------------< return_legislation_code >---------------------------|
499 -- ----------------------------------------------------------------------------
500 --
501 function return_legislation_code
502 (p_questionnaire_answer_id in hr_quest_answers.questionnaire_answer_id%TYPE
503 ) return varchar2 is
504 --
505 -- Cursor to find legislation code
506 --
507 cursor csr_leg_code is
508 select pbg.legislation_code
509 from per_business_groups pbg
510 , hr_quest_answers qsa
511 where qsa.questionnaire_answer_id = p_questionnaire_answer_id
512 and pbg.business_group_id = qsa.business_group_id;
513 --
514 -- Declare local variables
515 --
516 l_legislation_code varchar2(150);
517 l_proc varchar2(72) := 'return_legislation_code';
518 begin
519 hr_utility.set_location('Entering: '|| l_proc, 10);
520 --
521 hr_api.mandatory_arg_error(p_api_name => l_proc
522 ,p_argument => 'questionnaire_answer_id'
523 ,p_argument_value => p_questionnaire_answer_id
524 );
525 --
526 if nvl(g_questionnaire_answer_id, hr_api.g_number)
527 = p_questionnaire_answer_id then
528 --
529 -- The legislation code has already been found with a previous call
530 -- to this function Just return the value in the global variable.
531 --
532 l_legislation_code := g_legislation_code;
533 hr_utility.set_location(l_proc,20);
534 else
535 --
536 -- The ID is different to the last call to this function, or this
537 -- is the first call to this function.
538 --
539 open csr_leg_code;
540 fetch csr_leg_code into l_legislation_code;
541 if csr_leg_code%notfound then
542 --
543 -- The primary key is invalid, therefore we must error
544 --
545 close csr_leg_code;
546 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
547 fnd_message.raise_error;
548 end if;
549 hr_utility.set_location(l_proc,30);
550 --
551 -- Set the global variable so the values are available for the
552 -- next call to this function.
553 --
554 close csr_leg_code;
555 g_questionnaire_answer_id := p_questionnaire_answer_id;
556 g_legislation_code := l_legislation_code;
557 end if;
558 hr_utility.set_location('Leaving: '||l_proc, 40);
559 --
560 return l_legislation_code;
561 end return_legislation_code;
562 --
563 end hr_qsa_bus;