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