[Home] [Help]
PACKAGE BODY: APPS.PER_PGH_BUS
Source
1 Package Body per_pgh_bus as
2 /* $Header: pepghrhi.pkb 120.0 2005/05/31 14:05:56 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pgh_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_hierarchy_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_hierarchy_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_perf pbg
29 , per_gen_hierarchy pgh
30 where pgh.hierarchy_id = p_hierarchy_id
31 and pbg.business_group_id (+) = pgh.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 => 'hierarchy_id'
47 ,p_argument_value => p_hierarchy_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_hierarchy_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_perf pbg
89 , per_gen_hierarchy pgh
90 where pgh.hierarchy_id = p_hierarchy_id
91 and pbg.business_group_id (+) = pgh.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 => 'hierarchy_id'
107 ,p_argument_value => p_hierarchy_id
108 );
109 --
110 if ( nvl(per_pgh_bus.g_hierarchy_id, hr_api.g_number)
111 = p_hierarchy_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 := per_pgh_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 per_pgh_bus.g_hierarchy_id := p_hierarchy_id;
142 per_pgh_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_ddf >---------------------------------|
151 -- -----------------------------------------------------------------------------
152 --
153 --
154 -- Description:
155 -- Validates all the Developer Descriptive Flexfield values.
156 --
157 -- Prerequisites:
158 -- All other columns have been validated. Must be called as the
159 -- last step from insert_validate and update_validate.
160 --
161 -- In Arguments:
162 -- p_rec
163 --
164 -- Post Success:
165 -- If the Developer Descriptive Flexfield structure column and data values are
166 -- all valid this procedure will end normally and processing will
167 -- continue.
168 --
169 -- Post Failure:
170 -- If the Developer Descriptive Flexfield structure column value or any of
171 -- the data values are invalid then an application error is raised as
172 -- a PL/SQL exception.
173 --
174 -- Access Status:
175 -- Internal Row Handler Use Only.
176 --
177 -- ----------------------------------------------------------------------------
178 procedure chk_ddf
179 (p_rec in per_pgh_shd.g_rec_type) is
180 --
181 l_proc varchar2(72) := g_package||'chk_ddf';
182 l_error exception;
183 --
184 Begin
185 hr_utility.set_location('Entering:'||l_proc, 5);
186 --
187 -- Check if the row is being inserted or updated and a
188 -- value has changed
189 --
190 if (p_rec.hierarchy_id is null)
191 or ((p_rec.hierarchy_id is not null)
192 and
193 nvl(per_pgh_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
194 nvl(p_rec.information_category, hr_api.g_varchar2) or
195 nvl(per_pgh_shd.g_old_rec.information1, hr_api.g_varchar2) <>
196 nvl(p_rec.information1, hr_api.g_varchar2) or
197 nvl(per_pgh_shd.g_old_rec.information2, hr_api.g_varchar2) <>
198 nvl(p_rec.information2, hr_api.g_varchar2) or
199 nvl(per_pgh_shd.g_old_rec.information3, hr_api.g_varchar2) <>
200 nvl(p_rec.information3, hr_api.g_varchar2) or
201 nvl(per_pgh_shd.g_old_rec.information4, hr_api.g_varchar2) <>
202 nvl(p_rec.information4, hr_api.g_varchar2) or
203 nvl(per_pgh_shd.g_old_rec.information5, hr_api.g_varchar2) <>
204 nvl(p_rec.information5, hr_api.g_varchar2) or
205 nvl(per_pgh_shd.g_old_rec.information6, hr_api.g_varchar2) <>
206 nvl(p_rec.information6, hr_api.g_varchar2) or
207 nvl(per_pgh_shd.g_old_rec.information7, hr_api.g_varchar2) <>
208 nvl(p_rec.information7, hr_api.g_varchar2) or
209 nvl(per_pgh_shd.g_old_rec.information8, hr_api.g_varchar2) <>
210 nvl(p_rec.information8, hr_api.g_varchar2) or
211 nvl(per_pgh_shd.g_old_rec.information9, hr_api.g_varchar2) <>
212 nvl(p_rec.information9, hr_api.g_varchar2) or
213 nvl(per_pgh_shd.g_old_rec.information10, hr_api.g_varchar2) <>
214 nvl(p_rec.information10, hr_api.g_varchar2) or
215 nvl(per_pgh_shd.g_old_rec.information11, hr_api.g_varchar2) <>
216 nvl(p_rec.information11, hr_api.g_varchar2) or
217 nvl(per_pgh_shd.g_old_rec.information12, hr_api.g_varchar2) <>
218 nvl(p_rec.information12, hr_api.g_varchar2) or
219 nvl(per_pgh_shd.g_old_rec.information13, hr_api.g_varchar2) <>
220 nvl(p_rec.information13, hr_api.g_varchar2) or
221 nvl(per_pgh_shd.g_old_rec.information14, hr_api.g_varchar2) <>
222 nvl(p_rec.information14, hr_api.g_varchar2) or
223 nvl(per_pgh_shd.g_old_rec.information15, hr_api.g_varchar2) <>
224 nvl(p_rec.information15, hr_api.g_varchar2) or
225 nvl(per_pgh_shd.g_old_rec.information16, hr_api.g_varchar2) <>
226 nvl(p_rec.information16, hr_api.g_varchar2) or
227 nvl(per_pgh_shd.g_old_rec.information17, hr_api.g_varchar2) <>
228 nvl(p_rec.information17, hr_api.g_varchar2) or
229 nvl(per_pgh_shd.g_old_rec.information18, hr_api.g_varchar2) <>
230 nvl(p_rec.information18, hr_api.g_varchar2) or
231 nvl(per_pgh_shd.g_old_rec.information19, hr_api.g_varchar2) <>
232 nvl(p_rec.information19, hr_api.g_varchar2) or
233 nvl(per_pgh_shd.g_old_rec.information20, hr_api.g_varchar2) <>
234 nvl(p_rec.information20, hr_api.g_varchar2) or
235 nvl(per_pgh_shd.g_old_rec.information21, hr_api.g_varchar2) <>
236 nvl(p_rec.information21, hr_api.g_varchar2) or
237 nvl(per_pgh_shd.g_old_rec.information22, hr_api.g_varchar2) <>
238 nvl(p_rec.information22, hr_api.g_varchar2) or
239 nvl(per_pgh_shd.g_old_rec.information23, hr_api.g_varchar2) <>
240 nvl(p_rec.information23, hr_api.g_varchar2) or
241 nvl(per_pgh_shd.g_old_rec.information24, hr_api.g_varchar2) <>
242 nvl(p_rec.information24, hr_api.g_varchar2) or
243 nvl(per_pgh_shd.g_old_rec.information25, hr_api.g_varchar2) <>
244 nvl(p_rec.information25, hr_api.g_varchar2) or
245 nvl(per_pgh_shd.g_old_rec.information26, hr_api.g_varchar2) <>
246 nvl(p_rec.information26, hr_api.g_varchar2) or
247 nvl(per_pgh_shd.g_old_rec.information27, hr_api.g_varchar2) <>
248 nvl(p_rec.information27, hr_api.g_varchar2) or
249 nvl(per_pgh_shd.g_old_rec.information28, hr_api.g_varchar2) <>
250 nvl(p_rec.information28, hr_api.g_varchar2) or
251 nvl(per_pgh_shd.g_old_rec.information29, hr_api.g_varchar2) <>
252 nvl(p_rec.information29, hr_api.g_varchar2) or
253 nvl(per_pgh_shd.g_old_rec.information30, hr_api.g_varchar2) <>
254 nvl(p_rec.information30, hr_api.g_varchar2))
255 then
256 --
257 hr_dflex_utility.ins_or_upd_descflex_attribs
258 (p_appl_short_name => 'PER'
259 ,p_descflex_name => 'Hierarchy Type Developer DF'
260 ,p_attribute_category => p_rec.information_category
261 ,p_attribute1_name => 'INFORMATION1'
262 ,p_attribute1_value => p_rec.information1
263 ,p_attribute2_name => 'INFORMATION2'
264 ,p_attribute2_value => p_rec.information2
265 ,p_attribute3_name => 'INFORMATION3'
266 ,p_attribute3_value => p_rec.information3
267 ,p_attribute4_name => 'INFORMATION4'
268 ,p_attribute4_value => p_rec.information4
269 ,p_attribute5_name => 'INFORMATION5'
270 ,p_attribute5_value => p_rec.information5
271 ,p_attribute6_name => 'INFORMATION6'
272 ,p_attribute6_value => p_rec.information6
273 ,p_attribute7_name => 'INFORMATION7'
274 ,p_attribute7_value => p_rec.information7
275 ,p_attribute8_name => 'INFORMATION8'
276 ,p_attribute8_value => p_rec.information8
277 ,p_attribute9_name => 'INFORMATION9'
278 ,p_attribute9_value => p_rec.information9
279 ,p_attribute10_name => 'INFORMATION10'
280 ,p_attribute10_value => p_rec.information10
281 ,p_attribute11_name => 'INFORMATION11'
282 ,p_attribute11_value => p_rec.information11
283 ,p_attribute12_name => 'INFORMATION12'
284 ,p_attribute12_value => p_rec.information12
285 ,p_attribute13_name => 'INFORMATION13'
286 ,p_attribute13_value => p_rec.information13
287 ,p_attribute14_name => 'INFORMATION14'
288 ,p_attribute14_value => p_rec.information14
289 ,p_attribute15_name => 'INFORMATION15'
290 ,p_attribute15_value => p_rec.information15
291 ,p_attribute16_name => 'INFORMATION16'
292 ,p_attribute16_value => p_rec.information16
293 ,p_attribute17_name => 'INFORMATION17'
294 ,p_attribute17_value => p_rec.information17
295 ,p_attribute18_name => 'INFORMATION18'
296 ,p_attribute18_value => p_rec.information18
297 ,p_attribute19_name => 'INFORMATION19'
298 ,p_attribute19_value => p_rec.information19
299 ,p_attribute20_name => 'INFORMATION20'
300 ,p_attribute20_value => p_rec.information20
301 ,p_attribute21_name => 'INFORMATION21'
302 ,p_attribute21_value => p_rec.information21
303 ,p_attribute22_name => 'INFORMATION22'
304 ,p_attribute22_value => p_rec.information22
308 ,p_attribute24_value => p_rec.information24
305 ,p_attribute23_name => 'INFORMATION23'
306 ,p_attribute23_value => p_rec.information23
307 ,p_attribute24_name => 'INFORMATION24'
309 ,p_attribute25_name => 'INFORMATION25'
310 ,p_attribute25_value => p_rec.information25
311 ,p_attribute26_name => 'INFORMATION26'
312 ,p_attribute26_value => p_rec.information26
313 ,p_attribute27_name => 'INFORMATION27'
314 ,p_attribute27_value => p_rec.information27
315 ,p_attribute28_name => 'INFORMATION28'
316 ,p_attribute28_value => p_rec.information28
317 ,p_attribute29_name => 'INFORMATION29'
318 ,p_attribute29_value => p_rec.information29
319 ,p_attribute30_name => 'INFORMATION30'
320 ,p_attribute30_value => p_rec.information30
321 );
322 --
323 end if;
324 --
325 hr_utility.set_location(' Leaving:'||l_proc, 10);
326 end chk_ddf;
327 --
328 --
329 --
330 -- ----------------------------------------------------------------------------
331 -- |------------------------------< chk_df >----------------------------------|
332 -- ----------------------------------------------------------------------------
333 --
334 -- Description:
335 -- Validates all the Descriptive Flexfield values.
336 --
337 -- Prerequisites:
338 -- All other columns have been validated. Must be called as the
339 -- last step from insert_validate and update_validate.
340 --
341 -- In Arguments:
342 -- p_rec
343 --
344 -- Post Success:
345 -- If the Descriptive Flexfield structure column and data values are
346 -- all valid this procedure will end normally and processing will
347 -- continue.
348 --
349 -- Post Failure:
350 -- If the Descriptive Flexfield structure column value or any of
351 -- the data values are invalid then an application error is raised as
352 -- a PL/SQL exception.
353 --
354 -- Access Status:
355 -- Internal Row Handler Use Only.
356 --
357 -- ----------------------------------------------------------------------------
358 procedure chk_df
359 (p_rec in per_pgh_shd.g_rec_type
360 ) is
361 --
362 l_proc varchar2(72) := g_package || 'chk_df';
363 --
364 begin
365 hr_utility.set_location('Entering:'||l_proc,10);
366 --
367 if ((p_rec.hierarchy_id is not null) and (
368 nvl(per_pgh_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
369 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
370 nvl(per_pgh_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
371 nvl(p_rec.attribute1, hr_api.g_varchar2) or
372 nvl(per_pgh_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
373 nvl(p_rec.attribute2, hr_api.g_varchar2) or
374 nvl(per_pgh_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
375 nvl(p_rec.attribute3, hr_api.g_varchar2) or
376 nvl(per_pgh_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
377 nvl(p_rec.attribute4, hr_api.g_varchar2) or
378 nvl(per_pgh_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
379 nvl(p_rec.attribute5, hr_api.g_varchar2) or
380 nvl(per_pgh_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
381 nvl(p_rec.attribute6, hr_api.g_varchar2) or
382 nvl(per_pgh_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
383 nvl(p_rec.attribute7, hr_api.g_varchar2) or
384 nvl(per_pgh_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
385 nvl(p_rec.attribute8, hr_api.g_varchar2) or
386 nvl(per_pgh_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
387 nvl(p_rec.attribute9, hr_api.g_varchar2) or
388 nvl(per_pgh_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
389 nvl(p_rec.attribute10, hr_api.g_varchar2) or
390 nvl(per_pgh_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
391 nvl(p_rec.attribute11, hr_api.g_varchar2) or
392 nvl(per_pgh_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
393 nvl(p_rec.attribute12, hr_api.g_varchar2) or
394 nvl(per_pgh_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
395 nvl(p_rec.attribute13, hr_api.g_varchar2) or
396 nvl(per_pgh_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
397 nvl(p_rec.attribute14, hr_api.g_varchar2) or
398 nvl(per_pgh_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
399 nvl(p_rec.attribute15, hr_api.g_varchar2) or
400 nvl(per_pgh_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
401 nvl(p_rec.attribute16, hr_api.g_varchar2) or
402 nvl(per_pgh_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
403 nvl(p_rec.attribute17, hr_api.g_varchar2) or
404 nvl(per_pgh_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
405 nvl(p_rec.attribute18, hr_api.g_varchar2) or
406 nvl(per_pgh_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
407 nvl(p_rec.attribute19, hr_api.g_varchar2) or
408 nvl(per_pgh_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
409 nvl(p_rec.attribute20, hr_api.g_varchar2) or
410 nvl(per_pgh_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
411 nvl(p_rec.attribute21, hr_api.g_varchar2) or
412 nvl(per_pgh_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
413 nvl(p_rec.attribute22, hr_api.g_varchar2) or
414 nvl(per_pgh_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
415 nvl(p_rec.attribute23, hr_api.g_varchar2) or
416 nvl(per_pgh_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
417 nvl(p_rec.attribute24, hr_api.g_varchar2) or
421 nvl(p_rec.attribute26, hr_api.g_varchar2) or
418 nvl(per_pgh_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
419 nvl(p_rec.attribute25, hr_api.g_varchar2) or
420 nvl(per_pgh_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
422 nvl(per_pgh_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
423 nvl(p_rec.attribute27, hr_api.g_varchar2) or
424 nvl(per_pgh_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
425 nvl(p_rec.attribute28, hr_api.g_varchar2) or
426 nvl(per_pgh_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
427 nvl(p_rec.attribute29, hr_api.g_varchar2) or
428 nvl(per_pgh_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
429 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
430 or (p_rec.hierarchy_id is null) then
431 --
432 -- Only execute the validation if absolutely necessary:
433 -- a) During update, the structure column value or any
434 -- of the attribute values have actually changed.
435 -- b) During insert.
436 --
437 null;
438 hr_dflex_utility.ins_or_upd_descflex_attribs
439 (p_appl_short_name => 'PER'
440 ,p_descflex_name => 'PER_GEN_HIERARCHY'
441 ,p_attribute_category => p_rec.ATTRIBUTE_CATEGORY
442 ,p_attribute1_name => 'ATTRIBUTE1'
443 ,p_attribute1_value => p_rec.attribute1
444 ,p_attribute2_name => 'ATTRIBUTE2'
445 ,p_attribute2_value => p_rec.attribute2
446 ,p_attribute3_name => 'ATTRIBUTE3'
447 ,p_attribute3_value => p_rec.attribute3
448 ,p_attribute4_name => 'ATTRIBUTE4'
449 ,p_attribute4_value => p_rec.attribute4
450 ,p_attribute5_name => 'ATTRIBUTE5'
451 ,p_attribute5_value => p_rec.attribute5
452 ,p_attribute6_name => 'ATTRIBUTE6'
453 ,p_attribute6_value => p_rec.attribute6
454 ,p_attribute7_name => 'ATTRIBUTE7'
455 ,p_attribute7_value => p_rec.attribute7
456 ,p_attribute8_name => 'ATTRIBUTE8'
457 ,p_attribute8_value => p_rec.attribute8
458 ,p_attribute9_name => 'ATTRIBUTE9'
459 ,p_attribute9_value => p_rec.attribute9
460 ,p_attribute10_name => 'ATTRIBUTE10'
461 ,p_attribute10_value => p_rec.attribute10
462 ,p_attribute11_name => 'ATTRIBUTE11'
463 ,p_attribute11_value => p_rec.attribute11
464 ,p_attribute12_name => 'ATTRIBUTE12'
465 ,p_attribute12_value => p_rec.attribute12
466 ,p_attribute13_name => 'ATTRIBUTE13'
467 ,p_attribute13_value => p_rec.attribute13
468 ,p_attribute14_name => 'ATTRIBUTE14'
469 ,p_attribute14_value => p_rec.attribute14
470 ,p_attribute15_name => 'ATTRIBUTE15'
471 ,p_attribute15_value => p_rec.attribute15
472 ,p_attribute16_name => 'ATTRIBUTE16'
473 ,p_attribute16_value => p_rec.attribute16
474 ,p_attribute17_name => 'ATTRIBUTE17'
475 ,p_attribute17_value => p_rec.attribute17
476 ,p_attribute18_name => 'ATTRIBUTE18'
477 ,p_attribute18_value => p_rec.attribute18
478 ,p_attribute19_name => 'ATTRIBUTE19'
479 ,p_attribute19_value => p_rec.attribute19
480 ,p_attribute20_name => 'ATTRIBUTE20'
481 ,p_attribute20_value => p_rec.attribute20
482 ,p_attribute21_name => 'ATTRIBUTE21'
483 ,p_attribute21_value => p_rec.attribute21
484 ,p_attribute22_name => 'ATTRIBUTE22'
485 ,p_attribute22_value => p_rec.attribute22
486 ,p_attribute23_name => 'ATTRIBUTE23'
487 ,p_attribute23_value => p_rec.attribute23
488 ,p_attribute24_name => 'ATTRIBUTE24'
489 ,p_attribute24_value => p_rec.attribute24
490 ,p_attribute25_name => 'ATTRIBUTE25'
491 ,p_attribute25_value => p_rec.attribute25
492 ,p_attribute26_name => 'ATTRIBUTE26'
493 ,p_attribute26_value => p_rec.attribute26
494 ,p_attribute27_name => 'ATTRIBUTE27'
495 ,p_attribute27_value => p_rec.attribute27
496 ,p_attribute28_name => 'ATTRIBUTE28'
497 ,p_attribute28_value => p_rec.attribute28
498 ,p_attribute29_name => 'ATTRIBUTE29'
499 ,p_attribute29_value => p_rec.attribute29
500 ,p_attribute30_name => 'ATTRIBUTE30'
501 ,p_attribute30_value => p_rec.attribute30
502 );
503 end if;
504 --
505 hr_utility.set_location(' Leaving:'||l_proc,20);
506 end chk_df;
507 --
508 -- ----------------------------------------------------------------------------
509 -- |-----------------------< chk_non_updateable_args >------------------------|
510 -- ----------------------------------------------------------------------------
511 -- {Start Of Comments}
512 --
513 -- Description:
514 -- This procedure is used to ensure that non updateable attributes have
515 -- not been updated. If an attribute has been updated an error is generated.
516 --
517 -- Pre Conditions:
521 -- In Arguments:
518 -- g_old_rec has been populated with details of the values currently in
519 -- the database.
520 --
522 -- p_rec has been populated with the updated values the user would like the
523 -- record set to.
524 --
525 -- Post Success:
526 -- Processing continues if all the non updateable attributes have not
527 -- changed.
528 --
529 -- Post Failure:
530 -- An application error is raised if any of the non updatable attributes
531 -- have been altered.
532 --
533 -- {End Of Comments}
534 -- ----------------------------------------------------------------------------
535 Procedure chk_non_updateable_args
536 (p_effective_date in date
537 ,p_rec in per_pgh_shd.g_rec_type
538 ) IS
539 --
540 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
541 l_error EXCEPTION;
542 l_argument varchar2(30);
543 --
544 Begin
545 --
546 -- Only proceed with the validation if a row exists for the current
547 -- record in the HR Schema.
548 --
549 IF NOT per_pgh_shd.api_updating
550 (p_hierarchy_id => p_rec.hierarchy_id
551 ,p_object_version_number => p_rec.object_version_number
552 ) THEN
553 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
554 fnd_message.set_token('PROCEDURE ', l_proc);
555 fnd_message.set_token('STEP ', '5');
556 fnd_message.raise_error;
557 END IF;
558 --
559 -- EDIT_HERE: Add checks to ensure non-updateable args have
560 -- not been updated.
561 --
562 IF nvl(p_rec.business_group_id, hr_api.g_number) <>
563 nvl(per_pgh_shd.g_old_rec.business_group_id, hr_api.g_number) THEN
564 l_argument := 'business_group_id';
565 RAISE l_error;
566 END IF;
567 --
568 --
569 IF nvl(p_rec.type, hr_api.g_varchar2) <>
570 nvl(per_pgh_shd.g_old_rec.type, hr_api.g_varchar2) THEN
571 l_argument := 'TYPE';
572 RAISE l_error;
573 END IF;
574 --
575 EXCEPTION
576 WHEN l_error THEN
577 hr_api.argument_changed_error
578 (p_api_name => l_proc
579 ,p_argument => l_argument);
580 WHEN OTHERS THEN
581 RAISE;
582 End chk_non_updateable_args;
583 --
584 --
585 -- ----------------------------------------------------------------------------
586 -- |-------------------------< chk_hierarchy_id >-----------------------------|
587 -- ----------------------------------------------------------------------------
588 --
589 -- Description:
590 -- Validates that the hierarchy id parameter is null on insert.
591 --
592 -- Pre-conditions:
593 -- None.
594 --
595 -- In Arguments:
596 -- p_hierarchy_id
597 --
598 -- Post Success:
599 -- If the hierarchy_id parameter is NULL then
600 -- normal processing continues
601 --
602 -- Post Failure:
603 -- If the hierarchy_id is not null as an insert parameter an application
604 -- error will be raised and processing is terminated.
605 --
606 -- Developer/Implementation Notes:
607 -- Duplicate validation exists on form, so any changes made here
608 -- or on form must be dual-maintained.
609 --
610 -- Access Status:
611 -- Internal Row Table Handler Use Only.
612 --
613 -- {End Of Comments}
614 --
615 PROCEDURE chk_hierarchy_id
616 (p_hierarchy_id IN number) is
617 --
618 l_proc VARCHAR2(72) := g_package||'chk_hierarchy_id';
619 --
620 BEGIN
621 --
622 hr_utility.set_location('Entering:'|| l_proc, 10);
623 --
624 if p_hierarchy_id is not null then
625 --
626 fnd_message.set_name('PER', 'HR_289056_PGH_PK_NOT_NULL');
627 fnd_message.raise_error;
628 --
629 end if;
630 --
631 hr_utility.set_location('Leaving:'||l_proc, 30);
632 --
633 END chk_hierarchy_id;
634 --
635 -- ----------------------------------------------------------------------------
636 -- |-------------------------< chk_name >-------------------------------------|
637 -- ----------------------------------------------------------------------------
638 --
639 -- Description:
640 -- Validates that NAME of the hierarchy is UNIQUE within the
641 -- business group, if business group is set, or unique across
642 -- all business groups if global generic hierarchy (calendar).
643 --
644 -- Pre-conditions:
645 -- None.
646 --
647 -- In Arguments:
648 -- p_name
649 -- p_effective_date
650 -- p_business_group_id
651 --
652 -- Post Success:
653 -- If the name attribute is UNIQUE then
654 -- normal processing continues
655 --
656 -- Post Failure:
657 -- If the name attribute is already present then an application
658 -- error will be raised and processing is terminated.
659 --
660 -- Developer/Implementation Notes:
661 -- Duplicate validation exists on form, so any changes made here
662 -- or on form must be dual-maintained.
663 --
664 -- Access Status:
665 -- Internal Row Table Handler Use Only.
666 --
667 -- {End Of Comments}
668 --
669 PROCEDURE chk_name
670 (p_name in varchar2
671 ,p_hierarchy_id in number
672 ,p_business_group_id in number
676 l_exists VARCHAR2(1) := 'N';
673 ,p_object_version_number in number) IS
674 --
675 l_proc VARCHAR2(72) := g_package||'chk_name';
677 l_api_updating BOOLEAN;
678 --
679 -- Cursor to check uniqueness.
680 --
681 cursor csr_unique_name IS
682 SELECT 'Y'
683 FROM per_gen_hierarchy
684 WHERE (business_group_id = p_business_group_id or p_business_group_id is null)
685 AND hierarchy_id <> nvl(p_hierarchy_id,-1)
686 AND name = p_name;
687 --
688 BEGIN
689 --
690 hr_utility.set_location('Entering:'|| l_proc, 10);
691 --
692 hr_api.mandatory_arg_error
693 (p_api_name => l_proc
694 ,p_argument => 'NAME'
695 ,p_argument_value => p_name);
696 --
697 l_api_updating := per_pgh_shd.api_updating
698 (p_hierarchy_id => p_hierarchy_id
699 ,p_object_version_number => p_object_version_number);
700 --
701 -- Check name uniqueness
702 --
703 if ((l_api_updating and
704 nvl(per_pgh_shd.g_old_rec.name, hr_api.g_varchar2) <>
705 nvl(p_name, hr_api.g_varchar2))
706 or NOT l_api_updating) then
707 --
708 OPEN csr_unique_name;
709 --
710 FETCH csr_unique_name INTO l_exists;
711 --
712 hr_utility.set_location(l_proc, 20);
713 --
714 IF csr_unique_name%notfound THEN
715 --
716 CLOSE csr_unique_name;
717 --
718 ELSE
719 --
720 CLOSE csr_unique_name;
721 fnd_message.set_name('PER', 'HR_289057_PGH_DUP_HIER_NAME');
722 fnd_message.raise_error;
723 --
724 END IF;
725 --
726 end if;
727 --
728 hr_utility.set_location('Leaving:'||l_proc, 30);
729 --
730 END chk_name;
731 --
732 -- ----------------------------------------------------------------------------
733 -- |-------------------------< chk_type >-------------------------------------|
734 -- ----------------------------------------------------------------------------
735 --
736 -- Description:
737 -- Validates that type of hierarchy is present in
738 -- HR_STANDARD_LOOKUPS for 'HIERARCHY_TYPE' lookup code.
739 -- Also checks that hr_cross_business_group profile = 'Y' if inserting
740 -- a custom or georgraphical calendar coverage hierarchy that is global,
741 -- otherwise raise an error.
742 --
743 -- Pre-conditions:
744 -- None.
745 --
746 -- In Arguments:
747 -- p_type
748 -- p_effective_date
749 -- p_business_group_id
750 --
751 -- Post Success:
752 -- If the type attribute is present and valid then
753 -- normal processing continues
754 --
755 -- Post Failure:
756 -- If the type attribute is present and invalid then an application
757 -- error will be raised and processing is terminated.
758 --
759 -- Development Notes:
760 -- Duplicate validation exists on form, so any changes made here
761 -- or on form must be dual-maintained.
762 --
763 -- Access Status:
764 -- Internal Row Table Handler Use Only.
765 --
766 -- {End Of Comments}
767 --
768 PROCEDURE chk_type
769 (p_type IN varchar2,
770 p_effective_date IN DATE,
771 p_business_group_id IN number) IS
772 --
773 l_proc VARCHAR2(72) := g_package||'chk_type';
774 --
775 BEGIN
776 --
777 hr_utility.set_location('Entering:'|| l_proc, 10);
778 --
779 hr_api.mandatory_arg_error
780 (p_api_name => l_proc
781 ,p_argument => 'effective_date'
782 ,p_argument_value => p_effective_date);
783 --
784 -- Check if type exists in hr_lookups
785 --
786 if hr_api.NOT_EXISTS_IN_HRSTANLOOKUPS
787 (p_effective_date => p_effective_date
788 ,p_lookup_type => 'HIERARCHY_TYPE'
789 ,p_lookup_code => p_type) then
790 --
791 fnd_message.set_name('PER', 'HR_289058_PGH_INV_HIER_TYPE');
792 fnd_message.raise_error;
793 --
794 end if;
795
796 -- Validate that for a custom calendar hierarchy or a geographical hierarchy, the hierarchy
797 -- cannot be global unless the HR_CROSS_BUSINESS_GROUP profile is set appropriately.
798 if p_business_group_id is NULL then
799 if substr(p_type,0,7) = 'PER_CAL' then
800 if nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') <> 'Y' then
801 -- raise error
802 fnd_message.set_name('PER', 'PER_289185_CAL_GLB_INV');
803 fnd_message.raise_error;
804 end if;
805 end if;
806 end if;
807 --
808 hr_utility.set_location('Leaving:'||l_proc, 20);
809 --
810 END chk_type;
811 --
812 --
813 -- ----------------------------------------------------------------------------
814 -- |-------------------------< chk_duplicate_name >--------------------------|
815 -- ----------------------------------------------------------------------------
816 --
817 -- Description:
818 -- Validates allow duplicate name flag for the hierarchy type that
819 -- determines if the hierarchy type can be used to build multiple
820 -- hierarchies.
821 --
822 -- Pre-conditions:
823 -- None.
824 --
825 -- In Arguments:
826 -- p_type
827 --
828 -- Post Success:
832 --
829 -- If there are no hierarchies existing of the hierarchy type or
830 -- if allow duplicate name flag is 'Y' or null for the hierarchy type,
831 -- normal processing continues.
833 -- Post Failure:
834 -- If the hierarchy tried to be created is not the first for the
835 -- hierarchy type and allow multiple versions flag is 'N' for the hierarchy
836 -- type, error will be raised.
837 --
838 --
839 -- Developer/Implementation Notes:
840 -- None
841 --
842 -- Access Status:
843 -- Internal Row Table Handler Use Only.
844 --
845 -- {End Of Comments}
846 --
847
848 PROCEDURE chk_duplicate_name
849 (p_type IN Varchar2
850 )
851 IS
852 --
853 CURSOR csr_duplicate_name_flag IS
854 SELECT nvl(Information3, 'Y')
855 FROM per_shared_types
856 WHERE lookup_type = 'HIERARCHY_TYPE'
857 AND system_type_cd = p_type
858 AND system_type_cd = shared_type_code;
859 --
860 CURSOR csr_hierarchy_exists IS
861 SELECT 'Y'
862 FROM Per_Gen_Hierarchy
863 WHERE type = p_type;
864 --
865 --
866 l_duplicate_name Varchar2(1) := 'Y';
867 l_hierarchy_exists Varchar2(1);
868 l_proc Varchar2(72):= 'chk_duplicate_name';
869 --
870 BEGIN
871 --
872 hr_utility.set_location('Entering:'||l_proc, 10);
873 --
874 Open csr_duplicate_name_flag;
875 Fetch csr_duplicate_name_flag into l_duplicate_name;
876 Close csr_duplicate_name_flag;
877 --
878 hr_utility.set_location(l_proc, 20);
879 --
880 Open csr_hierarchy_exists;
881 Fetch csr_hierarchy_exists into l_hierarchy_exists;
882 If csr_hierarchy_exists%found and l_duplicate_name = 'N'
883 then
884 hr_utility.set_location(l_proc, 30);
885 Close csr_hierarchy_exists;
886 fnd_message.set_name('PER','HR_449062_PGH_DUPLICATE_NAME');
887 fnd_message.raise_error;
888 else
889 Close csr_hierarchy_exists;
890 End if;
891 --
892 hr_utility.set_location('Leaving:'||l_proc, 40);
893 --
894 end chk_duplicate_name;
895 --
896 -- ----------------------------------------------------------------------------
897 -- |---------------------------< chk_delete >---------------------------------|
898 -- ----------------------------------------------------------------------------
899 Procedure chk_delete
900 (p_hierarchy_id in number) is
901 --
902 -- Declare local variables
903 --
904 l_exists varchar2(1) := 'N';
905 l_proc varchar2(72) := g_package||'chk_delete';
906 --
907 cursor csr_vers_exists is
908 select 'Y'
909 from per_gen_hierarchy_versions
910 where hierarchy_id = p_hierarchy_id;
911 --
912 cursor csr_ev is
913 select 'X'
914 from per_calendar_entries
915 where hierarchy_id = p_hierarchy_id;
916 --
917 Begin
918 --
919 hr_utility.set_location('Entering: '||l_proc, 5);
920 --
921 hr_api.mandatory_arg_error
922 (p_api_name => l_proc
923 ,p_argument => 'hierarchy_id'
924 ,p_argument_value => p_hierarchy_id);
925 --
926 -- Check if versions of the hierarchy exist
927 --
928 hr_utility.set_location(l_proc, 20);
929 --
930 open csr_vers_exists;
931 --
932 fetch csr_vers_exists into l_exists;
933 --
934 if csr_vers_exists%notfound then
935 --
936 close csr_vers_exists;
937 --
938 else
939 --
940 close csr_vers_exists;
941 fnd_message.set_name('PER', 'HR_289059_PGH_DEL_HIERARCHY');
942 fnd_message.raise_error;
943 --
944 end if;
945
946 -- prevent delete if Calendar Hierarchy child record exists
947 open csr_ev;
948 fetch csr_ev into l_exists;
949 if csr_ev%notfound then
950 close csr_ev;
951 else
952 close csr_ev;
953 fnd_message.set_name('PER', 'HR_449078_CAL_ENTRY_VAL_EXISTS');
954 fnd_message.raise_error;
955 end if;
956
957 --
958 hr_utility.set_location('Leaving :'||l_proc, 30);
959 --
960 end chk_delete;
961 --
962 -- ----------------------------------------------------------------------------
963 -- |---------------------------< insert_validate >----------------------------|
964 -- ----------------------------------------------------------------------------
965 Procedure insert_validate
966 (p_effective_date in date
967 ,p_rec in per_pgh_shd.g_rec_type
968 ) is
969 --
970 l_proc varchar2(72) := g_package||'insert_validate';
971 --
972 Begin
973 hr_utility.set_location('Entering:'||l_proc, 5);
974 --
975 -- Call all supporting business operations
976 --
977 if p_rec.business_group_id is not null then
978 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
979 end if;
980 --
981 --
982 -- Call developer descriptive flexfield validation routines
983 per_pgh_bus.chk_ddf(p_rec);
984 --
985 per_pgh_bus.chk_df(p_rec);
986 --
987 --
988 -- Validate hierarchy_id
989 -- =====================
990 --
991 hr_utility.set_location(l_proc, 10);
992 --
993 chk_hierarchy_id
994 (p_hierarchy_id => p_rec.hierarchy_id);
995 --
996 --
997 hr_utility.set_location(l_proc, 20);
998 --
999 --
1000 -- Validate name
1001 -- =================
1002 --
1003 chk_name
1004 (p_name => p_rec.name
1005 , p_hierarchy_id => p_rec.hierarchy_id
1006 , p_business_group_id => p_rec.business_group_id
1007 , p_object_version_number => p_rec.object_version_number
1008 );
1009 --
1010 hr_utility.set_location(l_proc, 30);
1011 --
1012 --
1013 -- Validate type
1014 -- ======================
1015 chk_type
1016 (p_type => p_rec.type
1017 ,p_effective_date => p_effective_date
1018 ,p_business_group_id => p_rec.business_group_id);
1019 --
1020 --
1021 -- Validate Allow Duplicate Name Flag
1022 -- ==================================
1023 chk_duplicate_name
1024 (p_type => p_rec.type
1025 );
1026 --
1027 hr_utility.set_location('Leaving: '||l_proc, 40);
1028 --
1029 End insert_validate;
1030 --
1031 -- ----------------------------------------------------------------------------
1032 -- |---------------------------< update_validate >----------------------------|
1033 -- ----------------------------------------------------------------------------
1034 Procedure update_validate
1035 (p_effective_date in date
1036 ,p_rec in per_pgh_shd.g_rec_type
1037 ) is
1038 --
1039 l_proc varchar2(72) := g_package||'update_validate';
1040 --
1041 Begin
1042 hr_utility.set_location('Entering:'||l_proc, 5);
1043 --
1044 -- Call all supporting business operations
1045 --
1046 if p_rec.business_group_id is not null then
1047 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1048 end if;
1049 --
1050 hr_utility.set_location(l_proc, 10);
1051 chk_non_updateable_args
1052 (p_effective_date => p_effective_date
1053 ,p_rec => p_rec
1054 );
1055 --
1056 --
1057 hr_utility.set_location(l_proc, 20);
1058 --
1059 -- Validate name
1060 -- =================
1061 --
1062 chk_name
1063 (p_name => p_rec.name
1064 , p_hierarchy_id => p_rec.hierarchy_id
1065 , p_business_group_id => p_rec.business_group_id
1066 , p_object_version_number => p_rec.object_version_number
1067 );
1068 --
1069 hr_utility.set_location(l_proc, 30);
1070 --
1071 -- Call developer descriptive flexfield validation routines
1072 --
1073 per_pgh_bus.chk_ddf(p_rec);
1074 --
1075 hr_utility.set_location(l_proc, 40);
1076 --
1077 --
1078 per_pgh_bus.chk_df(p_rec);
1079 --
1080 hr_utility.set_location(' Leaving:'||l_proc, 50);
1081 End update_validate;
1082 --
1083 -- ----------------------------------------------------------------------------
1084 -- |---------------------------< delete_validate >----------------------------|
1085 -- ----------------------------------------------------------------------------
1086 Procedure delete_validate
1087 (p_rec in per_pgh_shd.g_rec_type
1088 ) is
1089 --
1090 l_proc varchar2(72) := g_package||'delete_validate';
1091 --
1092 Begin
1093 hr_utility.set_location('Entering:'||l_proc, 5);
1094 --
1095 -- Call all supporting business operations
1096 --
1097 hr_utility.set_location(l_proc, 10);
1098 --
1099 chk_delete
1100 (p_hierarchy_id => p_rec.hierarchy_id
1101 );
1102 --
1103 hr_utility.set_location(' Leaving:'||l_proc, 20);
1104 End delete_validate;
1105 --
1106 end per_pgh_bus;