1 Package Body irc_ias_bus as
2 /* $Header: iriasrhi.pkb 120.5 2011/02/16 12:43:23 vmummidi noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_ias_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_assignment_status_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_assignment_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , per_all_assignments_f asg
31 where pbg.business_group_id = asg.business_group_id
32 and asg.assignment_id = p_assignment_id ;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 open csr_sec_grp;
46 fetch csr_sec_grp into l_security_group_id;
47 --
48 if csr_sec_grp%notfound then
49 --
50 close csr_sec_grp;
51 --
52 -- The primary key is invalid therefore we must error
53 --
54 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
55 hr_multi_message.add
56 (p_associated_column1
57 => nvl(p_associated_column1,'ASSIGNMENT_ID')
58 );
59 --
60 else
61 close csr_sec_grp;
62 --
63 -- Set the security_group_id in CLIENT_INFO
64 --
65 hr_api.set_security_group_id
66 (p_security_group_id => l_security_group_id
67 );
68 end if;
69 --
70 hr_utility.set_location(' Leaving:'|| l_proc, 20);
71 --
72 end set_security_group_id;
73 --
74 -- ---------------------------------------------------------------------------
75 -- |---------------------< return_legislation_code >-------------------------|
76 -- ---------------------------------------------------------------------------
77 --
78 Function return_legislation_code
79 (p_assignment_status_id in number
80 )
81 Return Varchar2 Is
82 --
83 -- Declare cursor
84 --
85 cursor csr_leg_code is
86 select pbg.legislation_code
87 from per_business_groups_perf pbg
88 , per_all_assignments_f asg
89 , irc_assignment_statuses ias
90 where pbg.business_group_id = asg.business_group_id
91 and asg.assignment_id = ias.assignment_id
92 and ias.assignment_status_id = p_assignment_status_id ;
93 --
94 -- Declare local variables
95 --
96 l_legislation_code varchar2(150);
97 l_proc varchar2(72) := g_package||'return_legislation_code';
98 --
99 Begin
100 --
101 hr_utility.set_location('Entering:'|| l_proc, 10);
102 --
106 if ( nvl(irc_ias_bus.g_assignment_status_id, hr_api.g_number)
103 -- Ensure that all the mandatory parameter are not null
104 --
105 --
107 = p_assignment_status_id) then
108 --
109 -- The legislation code has already been found with a previous
110 -- call to this function. Just return the value in the global
111 -- variable.
112 --
113 l_legislation_code := irc_ias_bus.g_legislation_code;
114 hr_utility.set_location(l_proc, 20);
115 else
116 --
117 -- The ID is different to the last call to this function
118 -- or this is the first call to this function.
119 --
120 open csr_leg_code;
121 fetch csr_leg_code into l_legislation_code;
122 --
123 if csr_leg_code%notfound then
124 --
125 -- The primary key is invalid therefore we must error
126 --
127 close csr_leg_code;
128 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
129 fnd_message.raise_error;
130 end if;
131 hr_utility.set_location(l_proc,30);
132 --
133 -- Set the global variables so the values are
134 -- available for the next call to this function.
135 --
136 close csr_leg_code;
137 irc_ias_bus.g_assignment_status_id := p_assignment_status_id;
138 irc_ias_bus.g_legislation_code := l_legislation_code;
139 end if;
140 hr_utility.set_location(' Leaving:'|| l_proc, 40);
141 return l_legislation_code;
142 end return_legislation_code;
143 --
144 -- ----------------------------------------------------------------------------
145 -- |-----------------------< chk_non_updateable_args >------------------------|
146 -- ----------------------------------------------------------------------------
147 -- {Start Of Comments}
148 --
149 -- Description:
150 -- This procedure is used to ensure that non updateable attributes have
151 -- not been updated. If an attribute has been updated an error is generated.
152 --
153 -- Pre Conditions:
154 -- g_old_rec has been populated with details of the values currently in
155 -- the database.
156 --
157 -- In Arguments:
158 -- p_rec has been populated with the updated values the user would like the
159 -- record set to.
160 --
161 -- Post Success:
162 -- Processing continues if all the non updateable attributes have not
163 -- changed.
164 --
165 -- Post Failure:
166 -- An application error is raised if any of the non updatable attributes
167 -- have been altered.
168 --
169 -- {End Of Comments}
170 -- ----------------------------------------------------------------------------
171 Procedure chk_non_updateable_args
172 (p_rec in irc_ias_shd.g_rec_type
173 ) IS
174 --
175 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
176 --
177 Begin
178 --
179 -- Only proceed with the validation if a row exists for the current
180 -- record in the HR Schema.
181 --
182 IF NOT irc_ias_shd.api_updating
183 (p_assignment_status_id => p_rec.assignment_status_id
184 ,p_object_version_number => p_rec.object_version_number
185 ) THEN
186 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
187 fnd_message.set_token('PROCEDURE ', l_proc);
188 fnd_message.set_token('STEP ', '5');
189 fnd_message.raise_error;
190 END IF;
191 --
192
193 End chk_non_updateable_args;
194 --
195 -- ----------------------------------------------------------------------------
196 -- |---------------------------< chk_assignment_id >--------------------------|
197 -- ----------------------------------------------------------------------------
198 -- {Start Of Comments}
199 --
200 -- Description:
201 -- This procedure is used to ensure that assignment Id exists in table
202 -- per_all_assignments_f.
203 --
204 -- Pre Conditions:
205 -- assignment Id should exist in the table.
206 --
207 -- In Arguments:
208 -- p_assignment_id is passed by the user.
209 --
210 -- Post Success:
211 -- Processing continues if assignment Id exists.
212 --
213 -- Post Failure:
214 -- An error is raised if assignment Id does not exist.
215 --
216 -- {End Of Comments}
217 -- ----------------------------------------------------------------------------
218 Procedure chk_assignment_id
219 (p_assignment_id in
220 irc_assignment_statuses.assignment_id%type
221 ) is
222 --
223 l_proc varchar2(72) := g_package || 'chk_assignment_id';
224 --
225 l_assignment_id irc_assignment_statuses.assignment_id%type ;
226 --
227 cursor csr_applicant_assignment is
228 select 1
229 from per_all_assignments_f
230 where assignment_id = p_assignment_id;
231 --
232 begin
233 --
234 hr_utility.set_location('Entering:'|| l_proc, 10);
235 --
236 hr_api.mandatory_arg_error
237 (p_api_name => l_proc
238 ,p_argument => 'assignment_id'
239 ,p_argument_value => p_assignment_id
240 );
241 --
242 --
243 hr_utility.set_location(l_proc,20);
244 --
245 open csr_applicant_assignment;
246 fetch csr_applicant_assignment Into l_assignment_id;
247 --
248 if csr_applicant_assignment%notfound then
249 --
250 hr_utility.set_location(l_proc,30);
251 --
252 close csr_applicant_assignment;
253 fnd_message.set_name ('PER', 'IRC_412006_ASG_NOT_APPL');
254 fnd_message.raise_error;
255 end if;
256 --
257 close csr_applicant_assignment;
258 --
259 hr_utility.set_location(' Leaving:'|| l_proc, 50);
260 --
264 (p_associated_column1 => 'IRC_ASSIGNMENT_STATUSES.ASSIGNMENT_ID'
261 exception
262 when app_exception.application_exception then
263 if hr_multi_message.exception_add
265 ) then
266 hr_utility.set_location(' Leaving:'|| l_proc, 60);
267 raise;
268 end if;
269 hr_utility.set_location(' Leaving:'|| l_proc, 70);
270 --
271 end chk_assignment_id;
272 --
273
274
275 -- ----------------------------------------------------------------------------
276 -- |------------------< chk_comments >-----------------------
277 -- ----------------------------------------------------------------------------
278 -- {Start Of Comments}
279 --
280 -- Description:
281 -- This procedure is used to ensure that comments do not contain more than
282 -- 500 characters
283 --
284 -- In Arguments:
285 -- p_comments is passed by the user.
286 --
287 -- Post Success:
288 -- Processing continues if comments are less than 200 characters.
289 --
290 -- Post Failure:
291 -- An error is raised if comments are more than 200 characters.
292 --
293 -- {End Of Comments}
294 -- ----------------------------------------------------------------------------
295 Procedure chk_comments
296 (p_comments in varchar2
297 ) is
298 --
299 l_proc varchar2(72) := g_package || 'chk_comments';
300 --
301 --
302
303 begin
304 --
305 hr_utility.set_location('Entering:'|| l_proc, 10);
306 --
307 --
308 --
309 hr_utility.set_location(l_proc,20);
310 --
311
312 --
313 if lengthb(p_comments)>500 then
314 --
315 hr_utility.set_location(l_proc,30);
316 --
317 --- this message has to be changed to the new message
318 fnd_message.set_name ('PER', 'IRC_412599_COMMENT_LEN_EROOR');
319 fnd_message.raise_error;
320 end if;
321 --
322 --
323 hr_utility.set_location(' Leaving:'|| l_proc, 50);
324 --
325 exception
326 when app_exception.application_exception then
327 if hr_multi_message.exception_add
328 (p_associated_column1 => 'IRC_ASSIGNMENT_STATUSES.ASSIGNMENT_ID'
329 ) then
330 hr_utility.set_location(' Leaving:'|| l_proc, 60);
331 raise;
332 end if;
333 hr_utility.set_location(' Leaving:'|| l_proc, 70);
334 --
335 end chk_comments;
336
337
338
339 -- ----------------------------------------------------------------------------
340 -- |------------------< chk_assignment_status_type_id >-----------------------
341 -- ----------------------------------------------------------------------------
342 -- {Start Of Comments}
343 --
344 -- Description:
345 -- This procedure is used to ensure that assignment status type Id exists
346 -- in table per_assignment_status_types.
347 --
348 -- Pre Conditions:
349 -- assignment status Id should exist in the table.
350 --
351 -- In Arguments:
352 -- p_asg_status_type_id is passed by the user.
353 --
354 -- Post Success:
355 -- Processing continues if assignment status Id exists.
356 --
357 -- Post Failure:
358 -- An error is raised if assignment status Id does not exist.
359 --
360 -- {End Of Comments}
361 -- ----------------------------------------------------------------------------
362 Procedure chk_assignment_status_type_id
363 (p_asg_status_type_id in
364 irc_assignment_statuses.assignment_status_type_id%type
365 ) is
366 --
367 l_proc varchar2(72) := g_package || 'chk_assignment_status_type_id';
368 --
369 l_asg_status_type_id irc_assignment_statuses.assignment_status_type_id%type;
370 --
371 cursor csr_exists is
372 select 1
373 from PER_ASSIGNMENT_STATUS_TYPES
374 where ASSIGNMENT_STATUS_TYPE_ID = P_ASG_STATUS_TYPE_ID;
375 --
376 begin
377 hr_utility.set_location('Entering:'|| l_proc, 10);
378 hr_api.mandatory_arg_error
379 (p_api_name => l_proc
380 ,p_argument => 'ASSIGNMENT_STATUS_TYPE_ID'
381 ,p_argument_value => p_asg_status_type_id
382 );
383 --
384 open csr_exists;
385 fetch csr_exists into l_asg_status_type_id;
386 hr_utility.set_location(l_proc,20);
387 if csr_exists%notfound then
388 close csr_exists;
389 fnd_message.set_name ('PER','IRC_412007_REC_ASG_BAD');
390 fnd_message.raise_error;
391 end if;
392 close csr_exists;
393 --
394 hr_utility.set_location(' Leaving:'|| l_proc, 30);
395 --
396 exception
397 when app_exception.application_exception then
398 if hr_multi_message.exception_add
399 (p_associated_column1=>
400 'IRC_ASSIGNMENT_STATUSES.ASSIGNMENT_STATUS_TYPE_ID'
401 ) then
402 hr_utility.set_location(' Leaving:'|| l_proc, 50);
403 raise;
404 end if;
405 hr_utility.set_location(' Leaving:'|| l_proc, 60);
406 end chk_assignment_status_type_id;
407 --
408 -- ----------------------------------------------------------------------------
409 -- |---------------------------< insert_validate >----------------------------|
410 -- ----------------------------------------------------------------------------
411 Procedure insert_validate
412 (p_rec in irc_ias_shd.g_rec_type
413 ) is
414 --
415 l_proc varchar2(72) := g_package||'insert_validate';
416 --
417 Begin
418 hr_utility.set_location('Entering:'||l_proc, 5);
419 chk_assignment_id
420 (p_assignment_id => p_rec.assignment_id
421 );
422 hr_utility.set_location(l_proc,10);
423 chk_assignment_status_type_id
424 (p_asg_status_type_id => p_rec.assignment_status_type_id
425 );
426 -- As this table does not have a mandatory business_group_id
427 -- column, ensure client_info is populated by calling the
428 -- irc_ias_bus.set_security_group_id procedure
429 --
430 hr_utility.set_location(l_proc, 20);
431 irc_ias_bus.set_security_group_id
432 (p_assignment_id => p_rec.assignment_id
433 ,p_associated_column1 => irc_ias_shd.g_tab_nam||'.ASSIGNMENT_ID'
434 );
435 hr_utility.set_location(l_proc, 25);
436
437 chk_comments(p_comments => p_rec.status_change_comments);
438
439
440 hr_utility.set_location(' Leaving:'||l_proc, 30);
441 End insert_validate;
442 --
443 -- ----------------------------------------------------------------------------
444 -- |---------------------------< update_validate >----------------------------|
445 -- ----------------------------------------------------------------------------
446 Procedure update_validate
447 (p_rec in irc_ias_shd.g_rec_type
448 ) is
449 --
450 l_proc varchar2(72) := g_package||'update_validate';
451 --
452 Begin
453 hr_utility.set_location('Entering:'||l_proc, 5);
454 --
455 -- Call all supporting business operations
456 --
457 --
458 hr_utility.set_location('Entering:'||l_proc, 15);
459 --
460 chk_non_updateable_args
461 (p_rec => p_rec
462 );
463 --
464 hr_utility.set_location('Entering:'||l_proc, 20);
465 --
466 chk_assignment_status_type_id
467 (p_asg_status_type_id => p_rec.assignment_status_type_id
468 );
469 --
470 --
471 chk_comments(p_comments => p_rec.status_change_comments);
472 --
473 hr_utility.set_location(' Leaving:'||l_proc, 30);
474 End update_validate;
475 --
476 -- ----------------------------------------------------------------------------
477 -- |---------------------------< delete_validate >----------------------------|
478 -- ----------------------------------------------------------------------------
479 Procedure delete_validate
480 (p_rec in irc_ias_shd.g_rec_type
481 ) is
482 --
483 l_proc varchar2(72) := g_package||'delete_validate';
484 --
485 Begin
486 hr_utility.set_location('Entering:'||l_proc, 5);
487 --
488 -- Call all supporting business operations
489 --
490 hr_utility.set_location(' Leaving:'||l_proc, 10);
491 End delete_validate;
492 --
493 end irc_ias_bus;
[Home] [Help]