1 Package Body ghr_cmp_bus as
2 /* $Header: ghcmprhi.pkb 120.0 2005/05/29 02:54:02 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_cmp_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_complaint_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_complaint_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , ghr_complaints2 cmp
30 where cmp.complaint_id = p_complaint_id
31 and pbg.business_group_id = cmp.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'complaint_id'
47 ,p_argument_value => p_complaint_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_complaint_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , ghr_complaints2 cmp
90 where cmp.complaint_id = p_complaint_id
91 and pbg.business_group_id (+) = cmp.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'complaint_id'
107 ,p_argument_value => p_complaint_id
108 );
109 --
110 if ( nvl(ghr_cmp_bus.g_complaint_id, hr_api.g_number)
111 = p_complaint_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := ghr_cmp_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 ghr_cmp_bus.g_complaint_id := p_complaint_id;
142 ghr_cmp_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147
148 --
149 -- ----------------------------------------------------------------------------
150 -- |-----------------------< chk_non_updateable_args >------------------------|
151 -- ----------------------------------------------------------------------------
152 -- {Start Of Comments}
153 --
154 -- Description:
155 -- This procedure is used to ensure that non updateable attributes have
156 -- not been updated. If an attribute has been updated an error is generated.
157 --
158 -- Pre Conditions:
159 -- g_old_rec has been populated with details of the values currently in
160 -- the database.
161 --
162 -- In Arguments:
163 -- p_rec has been populated with the updated values the user would like the
164 -- record set to.
165 --
166 -- Post Success:
167 -- Processing continues if all the non updateable attributes have not
168 -- changed.
169 --
170 -- Post Failure:
171 -- An application error is raised if any of the non updatable attributes
172 -- have been altered.
173 --
174 -- {End Of Comments}
175 -- ----------------------------------------------------------------------------
176 Procedure chk_non_updateable_args
177 (p_effective_date in date
178 ,p_rec in ghr_cmp_shd.g_rec_type
179 ) IS
180 --
181 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
182 l_error EXCEPTION;
183 l_argument varchar2(30);
184 --
185 Begin
186 --
187 -- Only proceed with the validation if a row exists for the current
188 -- record in the HR Schema.
189 --
190 IF NOT ghr_cmp_shd.api_updating
191 (p_complaint_id => p_rec.complaint_id
192 ,p_object_version_number => p_rec.object_version_number
193 ) THEN
194 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
195 fnd_message.set_token('PROCEDURE ', l_proc);
196 fnd_message.set_token('STEP ', '5');
197 fnd_message.raise_error;
198 END IF;
199 --
200 -- EDIT_HERE: Add checks to ensure non-updateable args have
201 -- not been updated.
202 --
203 EXCEPTION
204 WHEN l_error THEN
205 hr_api.argument_changed_error
206 (p_api_name => l_proc
207 ,p_argument => l_argument);
208 WHEN OTHERS THEN
209 RAISE;
210 End chk_non_updateable_args;
211 /*
212 --
213 -- JH: Added DFF
214 -- -----------------------------------------------------------------------
215 -- |------------------------------< chk_df >-----------------------------|
216 -- -----------------------------------------------------------------------
217 --
218 -- Description:
219 -- Validates the all Descriptive Flexfield values.
220 --
221 -- Pre-conditions:
222 -- All other columns have been validated. Must be called as the
223 -- last step from insert_validate and update_validate.
224 --
225 -- In Arguments:
226 -- p_rec
227 --
228 -- Post Success:
229 -- If the Descriptive Flexfield structure column and data values are
230 -- all valid this procedure will end normally and processing will
231 -- continue.
232 --
233 -- Post Failure:
234 -- If the Descriptive Flexfield structure column value or any of
235 -- the data values are invalid then an application error is raised as
236 -- a PL/SQL exception.
237 --
238 -- Access Status:
239 -- Internal Row Handler Use Only.
240 --
241 procedure chk_df
242 (p_rec in ghr_cmp_shd.g_rec_type) is
243 --
244 l_proc varchar2(72);
245 --
246 begin
247 if g_debug then
248 l_proc := g_package||'chk_df';
249 hr_utility.set_location('Entering:'||l_proc, 10);
250 end if;
251 --
252 if ((p_rec.complaint_id is not null) and (
253 nvl(ghr_cmp_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
254 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
255 nvl(ghr_cmp_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
256 nvl(p_rec.attribute1, hr_api.g_varchar2) or
257 nvl(ghr_cmp_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
258 nvl(p_rec.attribute2, hr_api.g_varchar2) or
259 nvl(ghr_cmp_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
260 nvl(p_rec.attribute3, hr_api.g_varchar2) or
261 nvl(ghr_cmp_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
262 nvl(p_rec.attribute4, hr_api.g_varchar2) or
263 nvl(ghr_cmp_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
264 nvl(p_rec.attribute5, hr_api.g_varchar2) or
265 nvl(ghr_cmp_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
266 nvl(p_rec.attribute6, hr_api.g_varchar2) or
267 nvl(ghr_cmp_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
268 nvl(p_rec.attribute7, hr_api.g_varchar2) or
269 nvl(ghr_cmp_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
270 nvl(p_rec.attribute8, hr_api.g_varchar2) or
271 nvl(ghr_cmp_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
272 nvl(p_rec.attribute9, hr_api.g_varchar2) or
273 nvl(ghr_cmp_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
274 nvl(p_rec.attribute10, hr_api.g_varchar2) or
275 nvl(ghr_cmp_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
276 nvl(p_rec.attribute11, hr_api.g_varchar2) or
277 nvl(ghr_cmp_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
278 nvl(p_rec.attribute12, hr_api.g_varchar2) or
279 nvl(ghr_cmp_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
280 nvl(p_rec.attribute13, hr_api.g_varchar2) or
281 nvl(ghr_cmp_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
282 nvl(p_rec.attribute14, hr_api.g_varchar2) or
283 nvl(ghr_cmp_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
284 nvl(p_rec.attribute15, hr_api.g_varchar2) or
285 nvl(ghr_cmp_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
286 nvl(p_rec.attribute16, hr_api.g_varchar2) or
287 nvl(ghr_cmp_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
288 nvl(p_rec.attribute17, hr_api.g_varchar2) or
289 nvl(ghr_cmp_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
290 nvl(p_rec.attribute18, hr_api.g_varchar2) or
291 nvl(ghr_cmp_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
292 nvl(p_rec.attribute19, hr_api.g_varchar2) or
293 nvl(ghr_cmp_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
294 nvl(p_rec.attribute20, hr_api.g_varchar2) or
295 nvl(ghr_cmp_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
296 nvl(p_rec.attribute21, hr_api.g_varchar2) or
297 nvl(ghr_cmp_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
298 nvl(p_rec.attribute22, hr_api.g_varchar2) or
299 nvl(ghr_cmp_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
300 nvl(p_rec.attribute23, hr_api.g_varchar2) or
301 nvl(ghr_cmp_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
302 nvl(p_rec.attribute24, hr_api.g_varchar2) or
303 nvl(ghr_cmp_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
304 nvl(p_rec.attribute25, hr_api.g_varchar2) or
305 nvl(ghr_cmp_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
306 nvl(p_rec.attribute26, hr_api.g_varchar2) or
307 nvl(ghr_cmp_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
308 nvl(p_rec.attribute27, hr_api.g_varchar2) or
309 nvl(ghr_cmp_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
310 nvl(p_rec.attribute28, hr_api.g_varchar2) or
311 nvl(ghr_cmp_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
312 nvl(p_rec.attribute29, hr_api.g_varchar2) or
313 nvl(ghr_cmp_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
314 nvl(p_rec.attribute30, hr_api.g_varchar2)))
315 or (p_rec.complaint_id is null) then
316 --
317 -- Only execute the validation if absolutely necessary:
318 -- a) During update, the structure column value or any
319 -- of the attribute values have actually changed.
320 -- b) During insert.
321 --
322 hr_dflex_utility.ins_or_upd_descflex_attribs
323 (p_appl_short_name => 'GHR'
324 ,p_descflex_name => 'GHR_COMPLAINTS2'
325 ,p_attribute_category => p_rec.attribute_category
326 ,p_attribute1_name => 'ATTRIBUTE1'
327 ,p_attribute1_value => p_rec.attribute1
328 ,p_attribute2_name => 'ATTRIBUTE2'
329 ,p_attribute2_value => p_rec.attribute2
330 ,p_attribute3_name => 'ATTRIBUTE3'
331 ,p_attribute3_value => p_rec.attribute3
332 ,p_attribute4_name => 'ATTRIBUTE4'
333 ,p_attribute4_value => p_rec.attribute4
334 ,p_attribute5_name => 'ATTRIBUTE5'
335 ,p_attribute5_value => p_rec.attribute5
336 ,p_attribute6_name => 'ATTRIBUTE6'
337 ,p_attribute6_value => p_rec.attribute6
338 ,p_attribute7_name => 'ATTRIBUTE7'
339 ,p_attribute7_value => p_rec.attribute7
340 ,p_attribute8_name => 'ATTRIBUTE8'
341 ,p_attribute8_value => p_rec.attribute8
342 ,p_attribute9_name => 'ATTRIBUTE9'
346 ,p_attribute11_name => 'ATTRIBUTE11'
343 ,p_attribute9_value => p_rec.attribute9
344 ,p_attribute10_name => 'ATTRIBUTE10'
345 ,p_attribute10_value => p_rec.attribute10
347 ,p_attribute11_value => p_rec.attribute11
348 ,p_attribute12_name => 'ATTRIBUTE12'
349 ,p_attribute12_value => p_rec.attribute12
350 ,p_attribute13_name => 'ATTRIBUTE13'
351 ,p_attribute13_value => p_rec.attribute13
352 ,p_attribute14_name => 'ATTRIBUTE14'
353 ,p_attribute14_value => p_rec.attribute14
354 ,p_attribute15_name => 'ATTRIBUTE15'
355 ,p_attribute15_value => p_rec.attribute15
356 ,p_attribute16_name => 'ATTRIBUTE16'
357 ,p_attribute16_value => p_rec.attribute16
358 ,p_attribute17_name => 'ATTRIBUTE17'
359 ,p_attribute17_value => p_rec.attribute17
360 ,p_attribute18_name => 'ATTRIBUTE18'
361 ,p_attribute18_value => p_rec.attribute18
362 ,p_attribute19_name => 'ATTRIBUTE19'
363 ,p_attribute19_value => p_rec.attribute19
364 ,p_attribute20_name => 'ATTRIBUTE20'
365 ,p_attribute20_value => p_rec.attribute20
366 ,p_attribute21_name => 'ATTRIBUTE21'
367 ,p_attribute21_value => p_rec.attribute21
368 ,p_attribute22_name => 'ATTRIBUTE22'
369 ,p_attribute22_value => p_rec.attribute22
370 ,p_attribute23_name => 'ATTRIBUTE23'
371 ,p_attribute23_value => p_rec.attribute23
372 ,p_attribute24_name => 'ATTRIBUTE24'
373 ,p_attribute24_value => p_rec.attribute24
374 ,p_attribute25_name => 'ATTRIBUTE25'
375 ,p_attribute25_value => p_rec.attribute25
376 ,p_attribute26_name => 'ATTRIBUTE26'
377 ,p_attribute26_value => p_rec.attribute26
378 ,p_attribute27_name => 'ATTRIBUTE27'
379 ,p_attribute27_value => p_rec.attribute27
380 ,p_attribute28_name => 'ATTRIBUTE28'
381 ,p_attribute28_value => p_rec.attribute28
382 ,p_attribute29_name => 'ATTRIBUTE29'
383 ,p_attribute29_value => p_rec.attribute29
384 ,p_attribute30_name => 'ATTRIBUTE30'
385 ,p_attribute30_value => p_rec.attribute30
386 );
387 end if;
388 --
389 if g_debug then
390 hr_utility.set_location(' Leaving:'||l_proc, 20);
391 end if;
392
393 end chk_df;
394 */
395 --
396
397 --
398 -- ----------------------------------------------------------------------------
399 -- |---------------------------< insert_validate >----------------------------|
400 -- ----------------------------------------------------------------------------
401 Procedure insert_validate
402 (p_effective_date in date
403 ,p_rec in ghr_cmp_shd.g_rec_type
404 ) is
405 --
406 l_proc varchar2(72) := g_package||'insert_validate';
407 --
408 Begin
409 hr_utility.set_location('Entering:'||l_proc, 5);
410 --
411 -- Call all supporting business operations
412 --
413 -- EDIT_HERE: The following call to hr_api.validate_bus_grp_id
414 -- will only be valid when the business_group_id is not null.
415 -- As this column is defined as optional on the table then
416 -- different logic will be required to handle the null case.
417 -- If this is a start-up data entity then:
418 -- a) add code to stop null values being processed by this
419 -- row handler
420 -- If this is not a start-up data entity then either:
421 -- b) ignore the security_group_id value held in
422 -- client_info. This includes performing lookup
423 -- validation against the HR_STANDARD_LOOKUPS view.
424 -- or c) (less likely) ensure the correct security_group_id
425 -- value is set in client_info.
426 -- Remove this comment when the edit has been completed.
427 --hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
428 --
429 --
430 -- Validate flex fields.
431 --
432 /*
433 -- JH DFF
434 ghr_cmp_shd.chk_df(p_rec => p_rec);
435 if g_debug then
436 hr_utility.set_location(l_proc, 210);
437 end if;
438 */
439 --
440 hr_utility.set_location(' Leaving:'||l_proc, 10);
441 End insert_validate;
442 --
443 -- ----------------------------------------------------------------------------
444 -- |---------------------------< update_validate >----------------------------|
445 -- ----------------------------------------------------------------------------
446 Procedure update_validate
447 (p_effective_date in date
448 ,p_rec in ghr_cmp_shd.g_rec_type
449 ) is
450 --
451 l_proc varchar2(72) := g_package||'update_validate';
452 --
453 Begin
454 hr_utility.set_location('Entering:'||l_proc, 5);
455 --
459 -- will only be valid when the business_group_id is not null.
456 -- Call all supporting business operations
457 --
458 -- EDIT_HERE: The following call to hr_api.validate_bus_grp_id
460 -- As this column is defined as optional on the table then
461 -- different logic will be required to handle the null case.
462 -- If this is a start-up data entity then:
463 -- a) add code to stop null values being processed by this
464 -- row handler
465 -- If this is not a start-up data entity then either:
466 -- b) ignore the security_group_id value held in
467 -- client_info. This includes performing lookup
468 -- validation against the HR_STANDARD_LOOKUPS view.
469 -- or c) (less likely) ensure the correct security_group_id
470 -- value is set in client_info.
471 -- Remove this comment when the edit has been completed.
472 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
473 --
474 chk_non_updateable_args
475 (p_effective_date => p_effective_date
476 ,p_rec => p_rec
477 );
478 --
479 --
480 -- Validate flex fields.
481 --
482 /*
483 -- JH DFF
484 ghr_cmp_shd.chk_df(p_rec => p_rec);
485 if g_debug then
486 hr_utility.set_location(l_proc, 210);
487 end if;
488 */
489 --
490 hr_utility.set_location(' Leaving:'||l_proc, 10);
491 End update_validate;
492 --
493 -- ----------------------------------------------------------------------------
494 -- |---------------------------< delete_validate >----------------------------|
495 -- ----------------------------------------------------------------------------
496 Procedure delete_validate
497 (p_rec in ghr_cmp_shd.g_rec_type
498 ) is
499 --
500 l_proc varchar2(72) := g_package||'delete_validate';
501 --
502 Begin
503 hr_utility.set_location('Entering:'||l_proc, 5);
504 --
505 -- Call all supporting business operations
506 --
507 hr_utility.set_location(' Leaving:'||l_proc, 10);
508 End delete_validate;
509 --
510 end ghr_cmp_bus;
[Home] [Help]