1 Package Body hr_tis_bus as
2 /* $Header: hrtisrhi.pkb 120.3 2008/02/25 13:24:06 avarri ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_tis_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_topic_integrations_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |-----------------------< chk_non_updateable_args >------------------------|
18 -- ----------------------------------------------------------------------------
19 -- {Start Of Comments}
20 --
21 -- Description:
22 -- This procedure is used to ensure that non updateable attributes have
23 -- not been updated. If an attribute has been updated an error is generated.
24 --
25 -- Pre Conditions:
26 -- g_old_rec has been populated with details of the values currently in
27 -- the database.
28 --
29 -- In Arguments:
30 -- p_rec has been populated with the updated values the user would like the
31 -- record set to.
32 --
33 -- Post Success:
34 -- Processing continues if all the non updateable attributes have not
35 -- changed.
36 --
37 -- Post Failure:
38 -- An application error is raised if any of the non updatable attributes
39 -- have been altered.
40 --
41 -- {End Of Comments}
42 -- ----------------------------------------------------------------------------
43 Procedure chk_non_updateable_args
44 (p_rec in hr_tis_shd.g_rec_type
45 ) IS
46 --
47 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
48 --
49 Begin
50 --
51 -- Only proceed with the validation if a row exists for the current
52 -- record in the HR Schema.
53 --
54 IF NOT hr_tis_shd.api_updating
55 (p_topic_integrations_id => p_rec.topic_integrations_id
56 ,p_object_version_number => p_rec.object_version_number
57 ) THEN
58 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
59 fnd_message.set_token('PROCEDURE ', l_proc);
60 fnd_message.set_token('STEP ', '5');
61 fnd_message.raise_error;
62 END IF;
63
64
65 End chk_non_updateable_args;
66
67 -- ----------------------------------------------------------------------------
68 -- |-----------------------< chk_integration_id>------------------------------|
69 -- ----------------------------------------------------------------------------
70 -- {Start Of Comments}
71 --
72 -- Description:
73 -- This procedure ensures integration_id is present in hr_ki_integrations
74 -- and it is mandatory.
75 -- Pre Conditions:
76 -- g_old_rec has been populated with details of the values currently in
77 -- the database.
78 --
79 -- In Arguments:
80 -- p_integration_id
81 --
82 -- Post Success:
83 -- Processing continues if integration_id is valid
84 --
85 -- Post Failure:
86 -- An application error is raised if integration_id is invalid
87 --
88 -- {End Of Comments}
89 -- ----------------------------------------------------------------------------
90
91 Procedure chk_integration_id
92 (p_integration_id in number
93 ) IS
94 --
95 l_proc varchar2(72) := g_package || 'chk_integration_id';
96 l_key hr_ki_integrations.integration_id%type;
97 cursor csr_int is
98 select INTEGRATION_ID
99 from hr_ki_integrations
100 where integration_id = p_integration_id;
101
102
103 --
104 Begin
105 hr_utility.set_location('Entering:'||l_proc,10);
106
107 --integration_id should not be null
108
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'INTEGRATION_ID'
112 ,p_argument_value => p_integration_id
113 );
114
115
116 hr_utility.set_location('Validating:'||l_proc,20);
117
118
119 open csr_int;
120 fetch csr_int into l_key;
121 hr_utility.set_location('After fetching :'||l_proc,30);
122 if (csr_int%notfound) then
123 close csr_int;
124 fnd_message.set_name('PER','PER_449962_TIS_INT_ID_ABSENT');
125 fnd_message.raise_error;
126 end if;
127 close csr_int;
128
129 hr_utility.set_location(' Leaving:'||l_proc,40);
130 exception
131 when app_exception.application_exception then
132 if hr_multi_message.exception_add
133 (p_associated_column1 => 'HR_KI_TOPIC_INTEGRATIONS.INTEGRATION_ID'
134 )then
135 hr_utility.set_location(' Leaving:'||l_proc, 50);
136 raise;
137 end if;
138 hr_utility.set_location(' Leaving:'||l_proc,60);
139 End chk_integration_id;
140
141
142 -- ----------------------------------------------------------------------------
143 -- |-----------------------< chk_topic_id>------------------------------------|
144 -- ----------------------------------------------------------------------------
145 -- {Start Of Comments}
146 --
147 -- Description:
148 -- This procedure ensures topic_id is present in hr_ki_topics
149 -- and it is mandatory.
150 -- Pre Conditions:
151 -- g_old_rec has been populated with details of the values currently in
152 -- the database.
153 --
154 -- In Arguments:
155 -- p_topic_id
156 --
157 -- Post Success:
158 -- Processing continues if topic_id exists in parent table.
159 --
160 -- Post Failure:
161 -- An application error is raised if topic_id does not exist in parent table
162 --
163 -- {End Of Comments}
164 -- ----------------------------------------------------------------------------
165
166 Procedure chk_topic_id
167 (p_topic_id in number
168 ) IS
169 --
170 l_proc varchar2(72) := g_package || 'chk_topic_id';
171 l_key hr_ki_topics.topic_id%type;
172 cursor csr_int is
173 select topic_id
174 from hr_ki_topics
175 where topic_id = p_topic_id;
176 --
177 Begin
178 hr_utility.set_location('Entering:'||l_proc,10);
179
180 --topic_id should not be null
181
182 hr_api.mandatory_arg_error
183 (p_api_name => l_proc
184 ,p_argument => 'TOPIC_ID'
185 ,p_argument_value => p_topic_id
186 );
187
188 hr_utility.set_location('Validating:'||l_proc,20);
189
190 open csr_int;
191 fetch csr_int into l_key;
192 hr_utility.set_location('After fetching :'||l_proc,30);
193 if (csr_int%notfound) then
194 close csr_int;
195 fnd_message.set_name('PER','PER_449963_TIS_TOPIC_ID_ABSENT');
196 fnd_message.raise_error;
197 end if;
198 close csr_int;
199
200 hr_utility.set_location(' Leaving:'||l_proc,40);
201 exception
202 when app_exception.application_exception then
203 if hr_multi_message.exception_add
204 (p_associated_column1 => 'HR_KI_TOPIC_INTEGRATIONS.TOPIC_ID'
205 )then
206 hr_utility.set_location(' Leaving:'||l_proc, 50);
207 raise;
208 end if;
209 hr_utility.set_location(' Leaving:'||l_proc,60);
210 End chk_topic_id;
211
212
213 -- ----------------------------------------------------------------------------
214 -- |-----------------------< CHK_UNIQUE_RECORD>-------------------------------|
215 -- ----------------------------------------------------------------------------
216 -- {Start Of Comments}
217 --
218 -- Description:
219 -- This procedure ensures if record is unique for combination of
220 -- topic_id,integration_id
221 --
222 -- Pre Conditions:
223 -- g_old_rec has been populated with details of the values currently in
224 -- the database.
225 --
226 -- In Arguments:
227 -- p_topic_id,p_integration_id
228 --
229 -- Post Success:
230 -- Processing continues record is valid
231 --
232 -- Post Failure:
233 -- An application error is raised for invalid record
234 --
235 -- {End Of Comments}
236 -- ----------------------------------------------------------------------------
237 Procedure CHK_UNIQUE_RECORD
238 (
239 p_topic_id in number
240 ,p_integration_id in number
241 ) IS
242 --
243 l_proc varchar2(72) := g_package || 'CHK_UNIQUE_RECORD';
244 l_found varchar2(1);
245
246 cursor csr_int_options is
247 select null
248 from hr_ki_topic_integrations
249 where topic_id = p_topic_id
250 and integration_id = p_integration_id;
251 --
252 Begin
253 hr_utility.set_location('Entering:'||l_proc,10);
254
255 -- Only proceed with topic_id/ integration_id validation when the
256 -- Multiple Message List does not already contain an errors
257 -- associated with the topic_id/integration_id columns.
258 --
259 if hr_multi_message.no_exclusive_error
260 (p_check_column1 => 'HR_KI_TOPIC_INTEGRATIONS.TOPIC_ID'
261 ,p_associated_column1 => 'HR_KI_TOPIC_INTEGRATIONS.TOPIC_ID'
262 ,p_check_column2 => 'HR_KI_TOPIC_INTEGRATIONS.INTEGRATION_ID'
263 ,p_associated_column2 => 'HR_KI_TOPIC_INTEGRATIONS.INTEGRATION_ID'
264 ) then
265
266
267 open csr_int_options;
268 fetch csr_int_options into l_found;
269
270 if (csr_int_options%found) then
271 close csr_int_options;
272 fnd_message.set_name('PER','PER_449964_TIS_INVALID_COMB');
273 fnd_message.raise_error;
274 end if;
275 close csr_int_options;
276
277 end if;
278 exception
279 when app_exception.application_exception then
280 if hr_multi_message.exception_add
281 (p_same_associated_columns => 'Y'
282 )then
283 hr_utility.set_location(' Leaving:'||l_proc, 40);
284 raise;
285 end if;
286 hr_utility.set_location(' Leaving:'||l_proc,50);
287 End CHK_UNIQUE_RECORD;
288
289
290 -- ----------------------------------------------------------------------------
291 -- |-----------------------< CHK_UNIQUE_RECORD_UPD>---------------------------|
292 -- ----------------------------------------------------------------------------
293 -- {Start Of Comments}
294 --
295 -- Description:
296 -- This procedure ensures if record is unique for combination of
297 -- topic_id,integration_id
298 -- If earlier procedure is used for update validation then error will be
299 -- thrown even if combination is unique and not null as earlier query
300 -- does not have addtional p_topic_integrations_id condition in the cursor.
301 -- Hence for current record also earlier cursor throws error.
302 -- We can not combine these 2 methods as p_topic_integrations_id is not
303 -- available at the time of insert_validation
304 --
305 -- Pre Conditions:
306 -- g_old_rec has been populated with details of the values currently in
307 -- the database.
308 --
309 -- In Arguments:
310 -- p_topic_id,p_integration_id,p_topic_integrations_id
311 --
312 -- Post Success:
313 -- Processing continues record is valid
314 --
315 -- Post Failure:
316 -- An application error is raised for invalid record
317 --
318 -- {End Of Comments}
319 -- ----------------------------------------------------------------------------
320 Procedure CHK_UNIQUE_RECORD_UPD
321 (
322 p_topic_id in number
323 ,p_integration_id in number
324 ,p_topic_integrations_id in number
325 ) IS
326 --
327 l_proc varchar2(72) := g_package || 'CHK_UNIQUE_RECORD_UPD';
328 l_found varchar2(1);
329
330 cursor csr_int_options is
331 select null
332 from hr_ki_topic_integrations
333 where topic_id = p_topic_id
334 and integration_id = p_integration_id
335 and topic_integrations_id <> p_topic_integrations_id;
336 --
337 Begin
338 hr_utility.set_location('Entering:'||l_proc,10);
339
340 -- Only proceed with topic_id/ integration_id validation when the
341 -- Multiple Message List does not already contain an errors
342 -- associated with the topic_id/integration_id columns.
343 --
344 if hr_multi_message.no_exclusive_error
345 (p_check_column1 => 'HR_KI_TOPIC_INTEGRATIONS.TOPIC_ID'
346 ,p_associated_column1 => 'HR_KI_TOPIC_INTEGRATIONS.TOPIC_ID'
347 ,p_check_column2 => 'HR_KI_TOPIC_INTEGRATIONS.INTEGRATION_ID'
348 ,p_associated_column2 => 'HR_KI_TOPIC_INTEGRATIONS.INTEGRATION_ID'
349 ) then
350
351
352 open csr_int_options;
353 fetch csr_int_options into l_found;
354
355 if (csr_int_options%found) then
356 close csr_int_options;
357 fnd_message.set_name('PER','PER_449964_TIS_INVALID_COMB');
358 fnd_message.raise_error;
359 end if;
360 close csr_int_options;
361
362 end if;
363 exception
364 when app_exception.application_exception then
365 if hr_multi_message.exception_add
366 (p_same_associated_columns => 'Y'
367 )then
368 hr_utility.set_location(' Leaving:'||l_proc, 40);
369 raise;
370 end if;
371 hr_utility.set_location(' Leaving:'||l_proc,50);
372 End CHK_UNIQUE_RECORD_UPD;
373
374
375 --
376 -- ----------------------------------------------------------------------------
377 -- |---------------------------< insert_validate >----------------------------|
378 -- ----------------------------------------------------------------------------
379 Procedure insert_validate
380 (p_rec in hr_tis_shd.g_rec_type
381 ) is
382 --
383 l_proc varchar2(72) := g_package||'insert_validate';
384 --
385 Begin
386 hr_utility.set_location('Entering:'||l_proc, 5);
387 --
388 -- Call all supporting business operations
389 --
390 --
391 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
392 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
393 --
394 -- Validate Dependent Attributes
395 --
396 --
397 CHK_TOPIC_ID
398 (
399 p_topic_id => p_rec.topic_id
400 );
401
402 CHK_INTEGRATION_ID
403 (
404 p_integration_id => p_rec.integration_id
405 );
406
407 CHK_UNIQUE_RECORD
408 (
409 p_topic_id => p_rec.topic_id
410 ,p_integration_id => p_rec.integration_id
411 );
412
413 hr_utility.set_location(' Leaving:'||l_proc, 10);
414 End insert_validate;
415 --
416 -- ----------------------------------------------------------------------------
417 -- |---------------------------< update_validate >----------------------------|
418 -- ----------------------------------------------------------------------------
419 Procedure update_validate
420 (p_rec in hr_tis_shd.g_rec_type
421 ) is
422 --
423 l_proc varchar2(72) := g_package||'update_validate';
424 --
425 Begin
426 hr_utility.set_location('Entering:'||l_proc, 5);
427 --
428 -- Call all supporting business operations
429 --
430 --
431 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
432 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
433 --
434 -- Validate Dependent Attributes
435 --
436 chk_non_updateable_args
437 (p_rec => p_rec
438 );
439
440 CHK_TOPIC_ID
441 (
442 p_topic_id => p_rec.topic_id
443 );
444
445 CHK_INTEGRATION_ID
446 (
447 p_integration_id => p_rec.integration_id
448 );
449
450 CHK_UNIQUE_RECORD_UPD
451 (
452 p_topic_id => p_rec.topic_id
453 ,p_integration_id => p_rec.integration_id
454 ,p_topic_integrations_id =>p_rec.topic_integrations_id
455 );
456
457 --
458 --
459 hr_utility.set_location(' Leaving:'||l_proc, 10);
460 End update_validate;
461 --
462 -- ----------------------------------------------------------------------------
463 -- |---------------------------< delete_validate >----------------------------|
464 -- ----------------------------------------------------------------------------
465 Procedure delete_validate
466 (p_rec in hr_tis_shd.g_rec_type
467 ) is
468 --
469 l_proc varchar2(72) := g_package||'delete_validate';
470 --
471 Begin
472 hr_utility.set_location('Entering:'||l_proc, 5);
473 --
474 -- Call all supporting business operations
475 --
476 hr_utility.set_location(' Leaving:'||l_proc, 10);
477 End delete_validate;
478 --
479 end hr_tis_bus;