1 Package Body irc_ias_bus as
2 /* $Header: iriasrhi.pkb 120.0.12010000.3 2008/08/05 10:48:01 ubhat ship $ */
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 --
103 -- Ensure that all the mandatory parameter are not null
104 --
105 --
106 if ( nvl(irc_ias_bus.g_assignment_status_id, hr_api.g_number)
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 and assignment_type = 'A';
232 --
233 cursor csr_emp_applicant_assignment is
234 select 1
235 from per_all_assignments_f paaf1
236 where assignment_id = p_assignment_id
237 and exists ( select null
238 from per_all_assignments_f paaf2
239 where paaf1.person_id = paaf2.person_id
240 and paaf1.vacancy_id = paaf2.vacancy_id
241 and paaf2.assignment_type = 'A');
242 --
243 begin
244 --
245 hr_utility.set_location('Entering:'|| l_proc, 10);
246 --
247 hr_api.mandatory_arg_error
248 (p_api_name => l_proc
249 ,p_argument => 'assignment_id'
250 ,p_argument_value => p_assignment_id
251 );
252 --
253 --
254 hr_utility.set_location(l_proc,20);
255 --
256 open csr_applicant_assignment;
257 fetch csr_applicant_assignment Into l_assignment_id;
258 --
259 if csr_applicant_assignment%notfound then
260 --
261 hr_utility.set_location(l_proc,30);
262 --
263 open csr_emp_applicant_assignment;
264 fetch csr_emp_applicant_assignment Into l_assignment_id;
265 --
266 if csr_emp_applicant_assignment%notfound then
267 hr_utility.set_location(l_proc,40);
268 close csr_emp_applicant_assignment;
269 close csr_applicant_assignment;
270 fnd_message.set_name ('PER', 'IRC_412006_ASG_NOT_APPL');
271 fnd_message.raise_error;
272 end if;
273 --
274 close csr_emp_applicant_assignment;
275 end if;
276 --
277 close csr_applicant_assignment;
278 --
279 hr_utility.set_location(' Leaving:'|| l_proc, 50);
280 --
281 exception
282 when app_exception.application_exception then
283 if hr_multi_message.exception_add
284 (p_associated_column1 => 'IRC_ASSIGNMENT_STATUSES.ASSIGNMENT_ID'
285 ) then
286 hr_utility.set_location(' Leaving:'|| l_proc, 60);
287 raise;
288 end if;
289 hr_utility.set_location(' Leaving:'|| l_proc, 70);
290 --
291 end chk_assignment_id;
292 --
293 -- ----------------------------------------------------------------------------
294 -- |------------------< chk_assignment_status_type_id >-----------------------
295 -- ----------------------------------------------------------------------------
296 -- {Start Of Comments}
297 --
298 -- Description:
299 -- This procedure is used to ensure that assignment status type Id exists
300 -- in table per_assignment_status_types.
301 --
302 -- Pre Conditions:
303 -- assignment status Id should exist in the table.
304 --
305 -- In Arguments:
306 -- p_asg_status_type_id is passed by the user.
307 --
308 -- Post Success:
309 -- Processing continues if assignment status Id exists.
310 --
311 -- Post Failure:
312 -- An error is raised if assignment status Id does not exist.
313 --
314 -- {End Of Comments}
315 -- ----------------------------------------------------------------------------
316 Procedure chk_assignment_status_type_id
317 (p_asg_status_type_id in
318 irc_assignment_statuses.assignment_status_type_id%type
319 ) is
320 --
321 l_proc varchar2(72) := g_package || 'chk_assignment_status_type_id';
322 --
323 l_asg_status_type_id irc_assignment_statuses.assignment_status_type_id%type;
324 --
325 cursor csr_exists is
326 select 1
327 from PER_ASSIGNMENT_STATUS_TYPES
328 where ASSIGNMENT_STATUS_TYPE_ID = P_ASG_STATUS_TYPE_ID;
329 --
330 begin
331 hr_utility.set_location('Entering:'|| l_proc, 10);
332 hr_api.mandatory_arg_error
333 (p_api_name => l_proc
334 ,p_argument => 'ASSIGNMENT_STATUS_TYPE_ID'
335 ,p_argument_value => p_asg_status_type_id
336 );
337 --
338 open csr_exists;
339 fetch csr_exists into l_asg_status_type_id;
340 hr_utility.set_location(l_proc,20);
341 if csr_exists%notfound then
342 close csr_exists;
343 fnd_message.set_name ('PER','IRC_412007_REC_ASG_BAD');
344 fnd_message.raise_error;
345 end if;
346 close csr_exists;
347 --
348 hr_utility.set_location(' Leaving:'|| l_proc, 30);
349 --
350 exception
351 when app_exception.application_exception then
352 if hr_multi_message.exception_add
353 (p_associated_column1=>
354 'IRC_ASSIGNMENT_STATUSES.ASSIGNMENT_STATUS_TYPE_ID'
355 ) then
356 hr_utility.set_location(' Leaving:'|| l_proc, 50);
357 raise;
358 end if;
359 hr_utility.set_location(' Leaving:'|| l_proc, 60);
360 end chk_assignment_status_type_id;
361 --
362 -- ----------------------------------------------------------------------------
363 -- |---------------------------< insert_validate >----------------------------|
364 -- ----------------------------------------------------------------------------
365 Procedure insert_validate
366 (p_rec in irc_ias_shd.g_rec_type
367 ) is
368 --
369 l_proc varchar2(72) := g_package||'insert_validate';
370 --
371 Begin
372 hr_utility.set_location('Entering:'||l_proc, 5);
373 chk_assignment_id
374 (p_assignment_id => p_rec.assignment_id
375 );
376 hr_utility.set_location(l_proc,10);
377 chk_assignment_status_type_id
378 (p_asg_status_type_id => p_rec.assignment_status_type_id
379 );
380 -- As this table does not have a mandatory business_group_id
381 -- column, ensure client_info is populated by calling the
382 -- irc_ias_bus.set_security_group_id procedure
383 --
384 hr_utility.set_location(l_proc, 20);
385 irc_ias_bus.set_security_group_id
386 (p_assignment_id => p_rec.assignment_id
387 ,p_associated_column1 => irc_ias_shd.g_tab_nam||'.ASSIGNMENT_ID'
388 );
389 hr_utility.set_location(' Leaving:'||l_proc, 30);
390 End insert_validate;
391 --
392 -- ----------------------------------------------------------------------------
393 -- |---------------------------< update_validate >----------------------------|
394 -- ----------------------------------------------------------------------------
395 Procedure update_validate
396 (p_rec in irc_ias_shd.g_rec_type
397 ) is
398 --
399 l_proc varchar2(72) := g_package||'update_validate';
400 --
401 Begin
402 hr_utility.set_location('Entering:'||l_proc, 5);
403 --
404 -- Call all supporting business operations
405 --
406 --
407 hr_utility.set_location('Entering:'||l_proc, 15);
408 --
409 chk_non_updateable_args
410 (p_rec => p_rec
411 );
412 --
413 hr_utility.set_location('Entering:'||l_proc, 20);
414 --
415 chk_assignment_status_type_id
416 (p_asg_status_type_id => p_rec.assignment_status_type_id
417 );
418 --
419 --
420 --
421 hr_utility.set_location(' Leaving:'||l_proc, 30);
422 End update_validate;
423 --
424 -- ----------------------------------------------------------------------------
425 -- |---------------------------< delete_validate >----------------------------|
426 -- ----------------------------------------------------------------------------
427 Procedure delete_validate
428 (p_rec in irc_ias_shd.g_rec_type
429 ) is
430 --
431 l_proc varchar2(72) := g_package||'delete_validate';
432 --
433 Begin
434 hr_utility.set_location('Entering:'||l_proc, 5);
435 --
436 -- Call all supporting business operations
437 --
438 hr_utility.set_location(' Leaving:'||l_proc, 10);
439 End delete_validate;
440 --
441 end irc_ias_bus;
[Home] [Help]