1 Package Body per_cni_bus as
2 /* $Header: pecnirhi.pkb 120.0 2005/05/31 06:49 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cni_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_config_information_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_config_information_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 -- per_ri_config_information 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 Null--pbg.security_group_id,
34 --pbg.legislation_code
35 from --per_business_groups_perf pbg
36 per_ri_config_information cni
37 -- , EDIT_HERE table_name(s) 333
38 where cni.config_information_id = p_config_information_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 => 'config_information_id'
56 ,p_argument_value => p_config_information_id
57 );
58 --
59 open csr_sec_grp;
60 fetch csr_sec_grp into l_legislation_code;
61 --
62 if csr_sec_grp%notfound then
63 --
64 close csr_sec_grp;
65 --
66 -- The primary key is invalid therefore we must error
67 --
68 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
69 hr_multi_message.add
70 (p_associated_column1
71 => nvl(p_associated_column1,'CONFIG_INFORMATION_ID')
72 );
73 --
74 else
75 close csr_sec_grp;
76 --
77 -- Set the security_group_id in CLIENT_INFO
78 --
79 hr_api.set_security_group_id
80 (p_security_group_id => l_security_group_id
81 );
82 --
83 -- Set the sessions legislation context in HR_SESSION_DATA
84 --
85 hr_api.set_legislation_context(l_legislation_code);
86 end if;
87 --
88 hr_utility.set_location(' Leaving:'|| l_proc, 20);
89 --
90 end set_security_group_id;
91 --
92 -- ---------------------------------------------------------------------------
93 -- |---------------------< return_legislation_code >-------------------------|
94 -- ---------------------------------------------------------------------------
95 --
96 Function return_legislation_code
97 (p_config_information_id in number
98 )
99 Return Varchar2 Is
100 --
101 -- Declare cursor
102 --
103 -- EDIT_HERE In the following cursor statement add join(s) between
104 -- per_ri_config_information and PER_BUSINESS_GROUPS_PERF
105 -- so that the legislation_code for
106 -- the current business group context can be derived.
107 -- Remove this comment when the edit has been completed.
108 cursor csr_leg_code is
109 select Null--pbg.legislation_code
110 from --per_business_groups_perf pbg
111 per_ri_config_information cni
112 -- , EDIT_HERE table_name(s) 333
113 where cni.config_information_id = p_config_information_id;
114 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
115 --
116 -- Declare local variables
117 --
118 l_legislation_code varchar2(150);
119 l_proc varchar2(72) := g_package||'return_legislation_code';
120 --
121 Begin
122 --
123 hr_utility.set_location('Entering:'|| l_proc, 10);
124 --
125 -- Ensure that all the mandatory parameter are not null
126 --
127 hr_api.mandatory_arg_error
128 (p_api_name => l_proc
129 ,p_argument => 'config_information_id'
130 ,p_argument_value => p_config_information_id
131 );
132 --
133 if ( nvl(per_cni_bus.g_config_information_id, hr_api.g_number)
134 = p_config_information_id) then
135 --
136 -- The legislation code has already been found with a previous
137 -- call to this function. Just return the value in the global
138 -- variable.
139 --
140 l_legislation_code := per_cni_bus.g_legislation_code;
141 hr_utility.set_location(l_proc, 20);
142 else
143 --
144 -- The ID is different to the last call to this function
145 -- or this is the first call to this function.
146 --
147 open csr_leg_code;
148 fetch csr_leg_code into l_legislation_code;
149 --
150 if csr_leg_code%notfound then
151 --
152 -- The primary key is invalid therefore we must error
153 --
154 close csr_leg_code;
155 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
156 fnd_message.raise_error;
157 end if;
158 hr_utility.set_location(l_proc,30);
159 --
160 -- Set the global variables so the values are
161 -- available for the next call to this function.
162 --
163 close csr_leg_code;
164 per_cni_bus.g_config_information_id := p_config_information_id;
165 per_cni_bus.g_legislation_code := l_legislation_code;
166 end if;
167 hr_utility.set_location(' Leaving:'|| l_proc, 40);
168 return l_legislation_code;
169 end return_legislation_code;
170 --
171 -- ----------------------------------------------------------------------------
172 -- |-----------------------------< chk_ddf >----------------------------------|
173 -- ----------------------------------------------------------------------------
174 --
175 -- Description:
176 -- Validates all the Developer Descriptive Flexfield values.
177 --
178 -- Prerequisites:
179 -- All other columns have been validated. Must be called as the
180 -- last step from insert_validate and update_validate.
181 --
182 -- In Arguments:
183 -- p_rec
184 --
185 -- Post Success:
186 -- If the Developer Descriptive Flexfield structure column and data values
187 -- are all valid this procedure will end normally and processing will
188 -- continue.
189 --
190 -- Post Failure:
191 -- If the Developer Descriptive Flexfield structure column value or any of
192 -- the data values are invalid then an application error is raised as
193 -- a PL/SQL exception.
194 --
195 -- Access Status:
196 -- Internal Row Handler Use Only.
197 --
198 -- ----------------------------------------------------------------------------
199 procedure chk_ddf
200 (p_rec in per_cni_shd.g_rec_type
201 ) is
202 --
203 l_proc varchar2(72) := g_package || 'chk_ddf';
204 --
205 begin
206 hr_utility.set_location('Entering:'||l_proc,10);
207 --
208 /*
209 if ((p_rec.config_information_id is not null) and (
210 nvl(per_cni_shd.g_old_rec.config_information_category, hr_api.g_varchar2) <>
211 nvl(p_rec.config_information_category, hr_api.g_varchar2) or
212 nvl(per_cni_shd.g_old_rec.config_information1, hr_api.g_varchar2) <>
213 nvl(p_rec.config_information1, hr_api.g_varchar2) or
214 nvl(per_cni_shd.g_old_rec.config_information2, hr_api.g_varchar2) <>
215 nvl(p_rec.config_information2, hr_api.g_varchar2) or
216 nvl(per_cni_shd.g_old_rec.config_information3, hr_api.g_varchar2) <>
217 nvl(p_rec.config_information3, hr_api.g_varchar2) or
218 nvl(per_cni_shd.g_old_rec.config_information4, hr_api.g_varchar2) <>
219 nvl(p_rec.config_information4, hr_api.g_varchar2) or
220 nvl(per_cni_shd.g_old_rec.config_information5, hr_api.g_varchar2) <>
221 nvl(p_rec.config_information5, hr_api.g_varchar2) or
222 nvl(per_cni_shd.g_old_rec.config_information6, hr_api.g_varchar2) <>
223 nvl(p_rec.config_information6, hr_api.g_varchar2) or
224 nvl(per_cni_shd.g_old_rec.config_information7, hr_api.g_varchar2) <>
225 nvl(p_rec.config_information7, hr_api.g_varchar2) or
226 nvl(per_cni_shd.g_old_rec.config_information8, hr_api.g_varchar2) <>
227 nvl(p_rec.config_information8, hr_api.g_varchar2) or
228 nvl(per_cni_shd.g_old_rec.config_information9, hr_api.g_varchar2) <>
229 nvl(p_rec.config_information9, hr_api.g_varchar2) or
230 nvl(per_cni_shd.g_old_rec.config_information10, hr_api.g_varchar2) <>
231 nvl(p_rec.config_information10, hr_api.g_varchar2) or
232 nvl(per_cni_shd.g_old_rec.config_information11, hr_api.g_varchar2) <>
233 nvl(p_rec.config_information11, hr_api.g_varchar2) or
234 nvl(per_cni_shd.g_old_rec.config_information12, hr_api.g_varchar2) <>
235 nvl(p_rec.config_information12, hr_api.g_varchar2) or
236 nvl(per_cni_shd.g_old_rec.config_information13, hr_api.g_varchar2) <>
237 nvl(p_rec.config_information13, hr_api.g_varchar2) or
238 nvl(per_cni_shd.g_old_rec.config_information14, hr_api.g_varchar2) <>
239 nvl(p_rec.config_information14, hr_api.g_varchar2) or
240 nvl(per_cni_shd.g_old_rec.config_information15, hr_api.g_varchar2) <>
241 nvl(p_rec.config_information15, hr_api.g_varchar2) or
242 nvl(per_cni_shd.g_old_rec.config_information16, hr_api.g_varchar2) <>
243 nvl(p_rec.config_information16, hr_api.g_varchar2) or
244 nvl(per_cni_shd.g_old_rec.config_information17, hr_api.g_varchar2) <>
245 nvl(p_rec.config_information17, hr_api.g_varchar2) or
246 nvl(per_cni_shd.g_old_rec.config_information18, hr_api.g_varchar2) <>
247 nvl(p_rec.config_information18, hr_api.g_varchar2) or
248 nvl(per_cni_shd.g_old_rec.config_information19, hr_api.g_varchar2) <>
249 nvl(p_rec.config_information19, hr_api.g_varchar2) or
250 nvl(per_cni_shd.g_old_rec.config_information20, hr_api.g_varchar2) <>
251 nvl(p_rec.config_information20, hr_api.g_varchar2) or
252 nvl(per_cni_shd.g_old_rec.config_information21, hr_api.g_varchar2) <>
253 nvl(p_rec.config_information21, hr_api.g_varchar2) or
254 nvl(per_cni_shd.g_old_rec.config_information22, hr_api.g_varchar2) <>
255 nvl(p_rec.config_information22, hr_api.g_varchar2) or
256 nvl(per_cni_shd.g_old_rec.config_information23, hr_api.g_varchar2) <>
257 nvl(p_rec.config_information23, hr_api.g_varchar2) or
258 nvl(per_cni_shd.g_old_rec.config_information24, hr_api.g_varchar2) <>
259 nvl(p_rec.config_information24, hr_api.g_varchar2) or
260 nvl(per_cni_shd.g_old_rec.config_information25, hr_api.g_varchar2) <>
261 nvl(p_rec.config_information25, hr_api.g_varchar2) or
262 nvl(per_cni_shd.g_old_rec.config_information26, hr_api.g_varchar2) <>
263 nvl(p_rec.config_information26, hr_api.g_varchar2) or
264 nvl(per_cni_shd.g_old_rec.config_information27, hr_api.g_varchar2) <>
265 nvl(p_rec.config_information27, hr_api.g_varchar2) or
266 nvl(per_cni_shd.g_old_rec.config_information28, hr_api.g_varchar2) <>
267 nvl(p_rec.config_information28, hr_api.g_varchar2) or
268 nvl(per_cni_shd.g_old_rec.config_information29, hr_api.g_varchar2) <>
269 nvl(p_rec.config_information29, hr_api.g_varchar2) or
270 nvl(per_cni_shd.g_old_rec.config_information30, hr_api.g_varchar2) <>
271 nvl(p_rec.config_information30, hr_api.g_varchar2) or
272 nvl(per_cni_shd.g_old_rec.config_information_id, hr_api.g_varchar2) <>
273 nvl(p_rec.config_information_id, hr_api.g_number) ))
274 or (p_rec.config_information_id is null) then
275 */
276 --
277 -- Only execute the validation if absolutely necessary:
278 -- a) During update, the structure column value or any
279 -- of the attribute values have actually changed.
280 -- b) During insert.
281 --
282 If 1=1 Then
283 hr_dflex_utility.ins_or_upd_descflex_attribs
284 (p_appl_short_name => 'PER'
285 ,p_descflex_name => 'Configuration Information DF'
286 ,p_attribute_category => p_rec.config_information_category
287 ,p_attribute1_name => 'CONFIG_INFORMATION1'
288 ,p_attribute1_value => p_rec.config_information1
292 ,p_attribute3_value => p_rec.config_information3
289 ,p_attribute2_name => 'CONFIG_INFORMATION2'
290 ,p_attribute2_value => p_rec.config_information2
291 ,p_attribute3_name => 'CONFIG_INFORMATION3'
293 ,p_attribute4_name => 'CONFIG_INFORMATION4'
294 ,p_attribute4_value => p_rec.config_information4
295 ,p_attribute5_name => 'CONFIG_INFORMATION5'
296 ,p_attribute5_value => p_rec.config_information5
297 ,p_attribute6_name => 'CONFIG_INFORMATION6'
298 ,p_attribute6_value => p_rec.config_information6
299 ,p_attribute7_name => 'CONFIG_INFORMATION7'
300 ,p_attribute7_value => p_rec.config_information7
301 ,p_attribute8_name => 'CONFIG_INFORMATION8'
302 ,p_attribute8_value => p_rec.config_information8
303 ,p_attribute9_name => 'CONFIG_INFORMATION9'
304 ,p_attribute9_value => p_rec.config_information9
305 ,p_attribute10_name => 'CONFIG_INFORMATION10'
306 ,p_attribute10_value => p_rec.config_information10
307 ,p_attribute11_name => 'CONFIG_INFORMATION11'
308 ,p_attribute11_value => p_rec.config_information11
309 ,p_attribute12_name => 'CONFIG_INFORMATION12'
310 ,p_attribute12_value => p_rec.config_information12
311 ,p_attribute13_name => 'CONFIG_INFORMATION13'
312 ,p_attribute13_value => p_rec.config_information13
313 ,p_attribute14_name => 'CONFIG_INFORMATION14'
314 ,p_attribute14_value => p_rec.config_information14
315 ,p_attribute15_name => 'CONFIG_INFORMATION15'
316 ,p_attribute15_value => p_rec.config_information15
317 ,p_attribute16_name => 'CONFIG_INFORMATION16'
318 ,p_attribute16_value => p_rec.config_information16
319 ,p_attribute17_name => 'CONFIG_INFORMATION17'
320 ,p_attribute17_value => p_rec.config_information17
321 ,p_attribute18_name => 'CONFIG_INFORMATION18'
322 ,p_attribute18_value => p_rec.config_information18
323 ,p_attribute19_name => 'CONFIG_INFORMATION19'
324 ,p_attribute19_value => p_rec.config_information19
325 ,p_attribute20_name => 'CONFIG_INFORMATION20'
326 ,p_attribute20_value => p_rec.config_information20
327 ,p_attribute21_name => 'CONFIG_INFORMATION21'
328 ,p_attribute21_value => p_rec.config_information21
329 ,p_attribute22_name => 'CONFIG_INFORMATION22'
330 ,p_attribute22_value => p_rec.config_information22
331 ,p_attribute23_name => 'CONFIG_INFORMATION23'
332 ,p_attribute23_value => p_rec.config_information23
333 ,p_attribute24_name => 'CONFIG_INFORMATION24'
334 ,p_attribute24_value => p_rec.config_information24
335 ,p_attribute25_name => 'CONFIG_INFORMATION25'
336 ,p_attribute25_value => p_rec.config_information25
337 ,p_attribute26_name => 'CONFIG_INFORMATION26'
338 ,p_attribute26_value => p_rec.config_information26
339 ,p_attribute27_name => 'CONFIG_INFORMATION27'
340 ,p_attribute27_value => p_rec.config_information27
341 ,p_attribute28_name => 'CONFIG_INFORMATION28'
342 ,p_attribute28_value => p_rec.config_information28
343 ,p_attribute29_name => 'CONFIG_INFORMATION29'
344 ,p_attribute29_value => p_rec.config_information29
345 ,p_attribute30_name => 'CONFIG_INFORMATION30'
346 ,p_attribute30_value => p_rec.config_information30
347 );
348 end if;
349 --
350 hr_utility.set_location(' Leaving:'||l_proc,20);
351 end chk_ddf;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |-----------------------< chk_non_updateable_args >------------------------|
355 -- ----------------------------------------------------------------------------
356 -- {Start Of Comments}
357 --
358 -- Description:
359 -- This procedure is used to ensure that non updateable attributes have
360 -- not been updated. If an attribute has been updated an error is generated.
361 --
362 -- Pre Conditions:
363 -- g_old_rec has been populated with details of the values currently in
364 -- the database.
365 --
366 -- In Arguments:
367 -- p_rec has been populated with the updated values the user would like the
368 -- record set to.
369 --
370 -- Post Success:
371 -- Processing continues if all the non updateable attributes have not
372 -- changed.
373 --
374 -- Post Failure:
375 -- An application error is raised if any of the non updatable attributes
376 -- have been altered.
377 --
378 -- {End Of Comments}
379 -- ----------------------------------------------------------------------------
380 Procedure chk_non_updateable_args
381 (p_effective_date in date
382 ,p_rec in per_cni_shd.g_rec_type
383 ) IS
384 --
385 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
386 --
387 Begin
388 --
389 -- Only proceed with the validation if a row exists for the current
390 -- record in the HR Schema.
391 --
392 IF NOT per_cni_shd.api_updating
393 (p_config_information_id => p_rec.config_information_id
394 ) THEN
395 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
396 fnd_message.set_token('PROCEDURE ', l_proc);
397 fnd_message.set_token('STEP ', '5');
398 fnd_message.raise_error;
399 END IF;
400 --
401 -- EDIT_HERE: Add checks to ensure non-updateable args have
402 -- not been updated.
403 --
404 End chk_non_updateable_args;
405 --
406 -- ----------------------------------------------------------------------------
407 -- |---------------------------< insert_validate >----------------------------|
408 -- ----------------------------------------------------------------------------
409 Procedure insert_validate
410 (p_effective_date in date
411 ,p_rec in per_cni_shd.g_rec_type
412 ) is
413 --
414 l_proc varchar2(72) := g_package||'insert_validate';
415 --
416 Begin
417 hr_utility.set_location('Entering:'||l_proc, 5);
418 --
419 -- Call all supporting business operations
420 --
421 --
422 -- EDIT_HERE: As this table does not have a mandatory business_group_id
423 -- column, ensure client_info is populated by calling a suitable
424 -- ???_???_bus.set_security_group_id procedure, or add one of the following
425 -- comments:
426 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
427 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
428 --
429 -- Validate Dependent Attributes
430 --
431 --
432 hr_utility.trace('Context is ' || p_rec.config_information_category);
433 per_cni_bus.chk_ddf(p_rec);
434 --
435 hr_utility.set_location(' Leaving:'||l_proc, 10);
436 End insert_validate;
437 --
438 -- ----------------------------------------------------------------------------
439 -- |---------------------------< update_validate >----------------------------|
440 -- ----------------------------------------------------------------------------
441 Procedure update_validate
442 (p_effective_date in date
443 ,p_rec in per_cni_shd.g_rec_type
444 ) is
445 --
446 l_proc varchar2(72) := g_package||'update_validate';
447 --
448 Begin
449 hr_utility.set_location('Entering:'||l_proc, 5);
450 --
451 -- Call all supporting business operations
452 --
453 --
454 -- EDIT_HERE: As this table does not have a mandatory business_group_id
455 -- column, ensure client_info is populated by calling a suitable
456 -- ???_???_bus.set_security_group_id procedure, or add one of the following
457 -- comments:
458 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
459 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
460 --
461 -- Validate Dependent Attributes
462 --
463 chk_non_updateable_args
464 (p_effective_date => p_effective_date
465 ,p_rec => p_rec
466 );
467 --
468 --
469 per_cni_bus.chk_ddf(p_rec);
470 --
471 hr_utility.set_location(' Leaving:'||l_proc, 10);
472 End update_validate;
473 --
474 -- ----------------------------------------------------------------------------
475 -- |---------------------------< delete_validate >----------------------------|
476 -- ----------------------------------------------------------------------------
477 Procedure delete_validate
478 (p_rec in per_cni_shd.g_rec_type
479 ) is
480 --
481 l_proc varchar2(72) := g_package||'delete_validate';
482 --
483 Begin
484 hr_utility.set_location('Entering:'||l_proc, 5);
485 --
486 -- Call all supporting business operations
487 --
488 hr_utility.set_location(' Leaving:'||l_proc, 10);
489 End delete_validate;
490 --
491 end per_cni_bus;