[Home] [Help]
PACKAGE BODY: APPS.PER_PGV_BUS
Source
1 Package Body per_pgv_bus as
2 /* $Header: pepgvrhi.pkb 115.11 2004/06/10 23:41:52 vissingh noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pgv_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_version_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_hierarchy_version_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_versions pgv
30 where pgv.hierarchy_version_id = p_hierarchy_version_id
31 and pbg.business_group_id (+) = pgv.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_version_id'
47 ,p_argument_value => p_hierarchy_version_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_version_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_versions pgv
90 where pgv.hierarchy_version_id = p_hierarchy_version_id
91 and pbg.business_group_id (+) = pgv.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_version_id'
107 ,p_argument_value => p_hierarchy_version_id
108 );
109 --
110 if ( nvl(per_pgv_bus.g_hierarchy_version_id, hr_api.g_number)
111 = p_hierarchy_version_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_pgv_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_pgv_bus.g_hierarchy_version_id := p_hierarchy_version_id;
142 per_pgv_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 -- |-------------------------------< chk_ddf >---------------------------------|
150 -- -----------------------------------------------------------------------------
151 --
152 --
153 -- Description:
154 -- Validates all the Developer Descriptive Flexfield values.
155 --
156 -- Prerequisites:
157 -- All other columns have been validated. Must be called as the
158 -- last step from insert_validate and update_validate.
159 --
160 -- In Arguments:
161 -- p_rec
162 --
163 -- Post Success:
164 -- If the Developer Descriptive Flexfield structure column and data values are
165 -- all valid this procedure will end normally and processing will
166 -- continue.
167 --
168 -- Post Failure:
169 -- If the Developer Descriptive Flexfield structure column value or any of
170 -- the data values are invalid then an application error is raised as
171 -- a PL/SQL exception.
172 --
173 -- Access Status:
174 -- Internal Row Handler Use Only.
175 --
176 -- ----------------------------------------------------------------------------
177 procedure chk_ddf
178 (p_rec in per_pgv_shd.g_rec_type) is
179 --
180 l_proc varchar2(72) := g_package||'chk_ddf';
181 l_error exception;
182 --
183 Begin
184 hr_utility.set_location('Entering:'||l_proc, 5);
185 --
186 -- Check if the row is being inserted or updated and a
187 -- value has changed
188 --
189 if (p_rec.hierarchy_version_id is null)
190 or ((p_rec.hierarchy_version_id is not null)
191 and
192 nvl(per_pgv_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
193 nvl(p_rec.information_category, hr_api.g_varchar2) or
194 nvl(per_pgv_shd.g_old_rec.information1, hr_api.g_varchar2) <>
195 nvl(p_rec.information1, hr_api.g_varchar2) or
196 nvl(per_pgv_shd.g_old_rec.information2, hr_api.g_varchar2) <>
197 nvl(p_rec.information2, hr_api.g_varchar2) or
198 nvl(per_pgv_shd.g_old_rec.information3, hr_api.g_varchar2) <>
199 nvl(p_rec.information3, hr_api.g_varchar2) or
200 nvl(per_pgv_shd.g_old_rec.information4, hr_api.g_varchar2) <>
201 nvl(p_rec.information4, hr_api.g_varchar2) or
202 nvl(per_pgv_shd.g_old_rec.information5, hr_api.g_varchar2) <>
203 nvl(p_rec.information5, hr_api.g_varchar2) or
204 nvl(per_pgv_shd.g_old_rec.information6, hr_api.g_varchar2) <>
205 nvl(p_rec.information6, hr_api.g_varchar2) or
206 nvl(per_pgv_shd.g_old_rec.information7, hr_api.g_varchar2) <>
207 nvl(p_rec.information7, hr_api.g_varchar2) or
208 nvl(per_pgv_shd.g_old_rec.information8, hr_api.g_varchar2) <>
209 nvl(p_rec.information8, hr_api.g_varchar2) or
210 nvl(per_pgv_shd.g_old_rec.information9, hr_api.g_varchar2) <>
211 nvl(p_rec.information9, hr_api.g_varchar2) or
212 nvl(per_pgv_shd.g_old_rec.information10, hr_api.g_varchar2) <>
213 nvl(p_rec.information10, hr_api.g_varchar2) or
214 nvl(per_pgv_shd.g_old_rec.information11, hr_api.g_varchar2) <>
215 nvl(p_rec.information11, hr_api.g_varchar2) or
216 nvl(per_pgv_shd.g_old_rec.information12, hr_api.g_varchar2) <>
217 nvl(p_rec.information12, hr_api.g_varchar2) or
218 nvl(per_pgv_shd.g_old_rec.information13, hr_api.g_varchar2) <>
219 nvl(p_rec.information13, hr_api.g_varchar2) or
220 nvl(per_pgv_shd.g_old_rec.information14, hr_api.g_varchar2) <>
221 nvl(p_rec.information14, hr_api.g_varchar2) or
222 nvl(per_pgv_shd.g_old_rec.information15, hr_api.g_varchar2) <>
223 nvl(p_rec.information15, hr_api.g_varchar2) or
224 nvl(per_pgv_shd.g_old_rec.information16, hr_api.g_varchar2) <>
225 nvl(p_rec.information16, hr_api.g_varchar2) or
226 nvl(per_pgv_shd.g_old_rec.information17, hr_api.g_varchar2) <>
227 nvl(p_rec.information17, hr_api.g_varchar2) or
228 nvl(per_pgv_shd.g_old_rec.information18, hr_api.g_varchar2) <>
229 nvl(p_rec.information18, hr_api.g_varchar2) or
230 nvl(per_pgv_shd.g_old_rec.information19, hr_api.g_varchar2) <>
231 nvl(p_rec.information19, hr_api.g_varchar2) or
232 nvl(per_pgv_shd.g_old_rec.information20, hr_api.g_varchar2) <>
233 nvl(p_rec.information20, hr_api.g_varchar2) or
234 nvl(per_pgv_shd.g_old_rec.information21, hr_api.g_varchar2) <>
235 nvl(p_rec.information21, hr_api.g_varchar2) or
236 nvl(per_pgv_shd.g_old_rec.information22, hr_api.g_varchar2) <>
237 nvl(p_rec.information22, hr_api.g_varchar2) or
238 nvl(per_pgv_shd.g_old_rec.information23, hr_api.g_varchar2) <>
239 nvl(p_rec.information23, hr_api.g_varchar2) or
240 nvl(per_pgv_shd.g_old_rec.information24, hr_api.g_varchar2) <>
241 nvl(p_rec.information24, hr_api.g_varchar2) or
242 nvl(per_pgv_shd.g_old_rec.information25, hr_api.g_varchar2) <>
243 nvl(p_rec.information25, hr_api.g_varchar2) or
244 nvl(per_pgv_shd.g_old_rec.information26, hr_api.g_varchar2) <>
245 nvl(p_rec.information26, hr_api.g_varchar2) or
246 nvl(per_pgv_shd.g_old_rec.information27, hr_api.g_varchar2) <>
247 nvl(p_rec.information27, hr_api.g_varchar2) or
248 nvl(per_pgv_shd.g_old_rec.information28, hr_api.g_varchar2) <>
249 nvl(p_rec.information28, hr_api.g_varchar2) or
250 nvl(per_pgv_shd.g_old_rec.information29, hr_api.g_varchar2) <>
251 nvl(p_rec.information29, hr_api.g_varchar2) or
252 nvl(per_pgv_shd.g_old_rec.information30, hr_api.g_varchar2) <>
253 nvl(p_rec.information30, hr_api.g_varchar2))
254 then
255 --
256 hr_dflex_utility.ins_or_upd_descflex_attribs
257 (p_appl_short_name => 'PER'
258 ,p_descflex_name => 'PER_GEN_HIERARCHY_VERSIONS_DDF'
259 ,p_attribute_category => p_rec.information_category
260 ,p_attribute1_name => 'INFORMATION1'
261 ,p_attribute1_value => p_rec.information1
262 ,p_attribute2_name => 'INFORMATION2'
263 ,p_attribute2_value => p_rec.information2
264 ,p_attribute3_name => 'INFORMATION3'
265 ,p_attribute3_value => p_rec.information3
266 ,p_attribute4_name => 'INFORMATION4'
267 ,p_attribute4_value => p_rec.information4
268 ,p_attribute5_name => 'INFORMATION5'
269 ,p_attribute5_value => p_rec.information5
270 ,p_attribute6_name => 'INFORMATION6'
271 ,p_attribute6_value => p_rec.information6
272 ,p_attribute7_name => 'INFORMATION7'
273 ,p_attribute7_value => p_rec.information7
274 ,p_attribute8_name => 'INFORMATION8'
275 ,p_attribute8_value => p_rec.information8
276 ,p_attribute9_name => 'INFORMATION9'
277 ,p_attribute9_value => p_rec.information9
278 ,p_attribute10_name => 'INFORMATION10'
279 ,p_attribute10_value => p_rec.information10
280 ,p_attribute11_name => 'INFORMATION11'
281 ,p_attribute11_value => p_rec.information11
282 ,p_attribute12_name => 'INFORMATION12'
283 ,p_attribute12_value => p_rec.information12
284 ,p_attribute13_name => 'INFORMATION13'
285 ,p_attribute13_value => p_rec.information13
286 ,p_attribute14_name => 'INFORMATION14'
287 ,p_attribute14_value => p_rec.information14
288 ,p_attribute15_name => 'INFORMATION15'
289 ,p_attribute15_value => p_rec.information15
290 ,p_attribute16_name => 'INFORMATION16'
291 ,p_attribute16_value => p_rec.information16
292 ,p_attribute17_name => 'INFORMATION17'
293 ,p_attribute17_value => p_rec.information17
294 ,p_attribute18_name => 'INFORMATION18'
295 ,p_attribute18_value => p_rec.information18
296 ,p_attribute19_name => 'INFORMATION19'
297 ,p_attribute19_value => p_rec.information19
298 ,p_attribute20_name => 'INFORMATION20'
299 ,p_attribute20_value => p_rec.information20
300 ,p_attribute21_name => 'INFORMATION21'
301 ,p_attribute21_value => p_rec.information21
302 ,p_attribute22_name => 'INFORMATION22'
303 ,p_attribute22_value => p_rec.information22
304 ,p_attribute23_name => 'INFORMATION23'
305 ,p_attribute23_value => p_rec.information23
306 ,p_attribute24_name => 'INFORMATION24'
307 ,p_attribute24_value => p_rec.information24
308 ,p_attribute25_name => 'INFORMATION25'
309 ,p_attribute25_value => p_rec.information25
310 ,p_attribute26_name => 'INFORMATION26'
311 ,p_attribute26_value => p_rec.information26
312 ,p_attribute27_name => 'INFORMATION27'
313 ,p_attribute27_value => p_rec.information27
314 ,p_attribute28_name => 'INFORMATION28'
315 ,p_attribute28_value => p_rec.information28
316 ,p_attribute29_name => 'INFORMATION29'
317 ,p_attribute29_value => p_rec.information29
318 ,p_attribute30_name => 'INFORMATION30'
319 ,p_attribute30_value => p_rec.information30
320 );
321 --
322 end if;
323 --
324 hr_utility.set_location(' Leaving:'||l_proc, 10);
325 end chk_ddf;
326 --
327
328
329 -- ----------------------------------------------------------------------------
330 -- |------------------------------< chk_df >----------------------------------|
331 -- ----------------------------------------------------------------------------
332 --
333 -- Description:
334 -- Validates all the Descriptive Flexfield values.
335 --
336 -- Prerequisites:
337 -- All other columns have been validated. Must be called as the
338 -- last step from insert_validate and update_validate.
339 --
340 -- In Arguments:
341 -- p_rec
342 --
343 -- Post Success:
344 -- If the Descriptive Flexfield structure column and data values are
345 -- all valid this procedure will end normally and processing will
346 -- continue.
347 --
348 -- Post Failure:
349 -- If the Descriptive Flexfield structure column value or any of
350 -- the data values are invalid then an application error is raised as
351 -- a PL/SQL exception.
352 --
353 -- Access Status:
354 -- Internal Row Handler Use Only.
355 --
356 -- ----------------------------------------------------------------------------
357 procedure chk_df
358 (p_rec in per_pgv_shd.g_rec_type
359 ) is
360 --
361 l_proc varchar2(72) := g_package || 'chk_df';
362 --
363 begin
364 hr_utility.set_location('Entering:'||l_proc,10);
365 --
366 if ((p_rec.hierarchy_version_id is not null) and (
367 nvl(per_pgv_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
368 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
369 nvl(per_pgv_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
370 nvl(p_rec.attribute1, hr_api.g_varchar2) or
371 nvl(per_pgv_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
372 nvl(p_rec.attribute2, hr_api.g_varchar2) or
376 nvl(p_rec.attribute4, hr_api.g_varchar2) or
373 nvl(per_pgv_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
374 nvl(p_rec.attribute3, hr_api.g_varchar2) or
375 nvl(per_pgv_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
377 nvl(per_pgv_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
378 nvl(p_rec.attribute5, hr_api.g_varchar2) or
379 nvl(per_pgv_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
380 nvl(p_rec.attribute6, hr_api.g_varchar2) or
381 nvl(per_pgv_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
382 nvl(p_rec.attribute7, hr_api.g_varchar2) or
383 nvl(per_pgv_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
384 nvl(p_rec.attribute8, hr_api.g_varchar2) or
385 nvl(per_pgv_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
386 nvl(p_rec.attribute9, hr_api.g_varchar2) or
387 nvl(per_pgv_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
388 nvl(p_rec.attribute10, hr_api.g_varchar2) or
389 nvl(per_pgv_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
390 nvl(p_rec.attribute11, hr_api.g_varchar2) or
391 nvl(per_pgv_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
392 nvl(p_rec.attribute12, hr_api.g_varchar2) or
393 nvl(per_pgv_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
394 nvl(p_rec.attribute13, hr_api.g_varchar2) or
395 nvl(per_pgv_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
396 nvl(p_rec.attribute14, hr_api.g_varchar2) or
397 nvl(per_pgv_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
398 nvl(p_rec.attribute15, hr_api.g_varchar2) or
399 nvl(per_pgv_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
400 nvl(p_rec.attribute16, hr_api.g_varchar2) or
401 nvl(per_pgv_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
402 nvl(p_rec.attribute17, hr_api.g_varchar2) or
403 nvl(per_pgv_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
404 nvl(p_rec.attribute18, hr_api.g_varchar2) or
405 nvl(per_pgv_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
406 nvl(p_rec.attribute19, hr_api.g_varchar2) or
407 nvl(per_pgv_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
408 nvl(p_rec.attribute20, hr_api.g_varchar2) or
409 nvl(per_pgv_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
410 nvl(p_rec.attribute21, hr_api.g_varchar2) or
411 nvl(per_pgv_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
412 nvl(p_rec.attribute22, hr_api.g_varchar2) or
413 nvl(per_pgv_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
414 nvl(p_rec.attribute23, hr_api.g_varchar2) or
415 nvl(per_pgv_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
416 nvl(p_rec.attribute24, hr_api.g_varchar2) or
417 nvl(per_pgv_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
418 nvl(p_rec.attribute25, hr_api.g_varchar2) or
419 nvl(per_pgv_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
420 nvl(p_rec.attribute26, hr_api.g_varchar2) or
421 nvl(per_pgv_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
422 nvl(p_rec.attribute27, hr_api.g_varchar2) or
423 nvl(per_pgv_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
424 nvl(p_rec.attribute28, hr_api.g_varchar2) or
425 nvl(per_pgv_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
426 nvl(p_rec.attribute29, hr_api.g_varchar2) or
427 nvl(per_pgv_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
428 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
429 or (p_rec.hierarchy_version_id is null) then
430 --
431 -- Only execute the validation if absolutely necessary:
432 -- a) During update, the structure column value or any
433 -- of the attribute values have actually changed.
434 -- b) During insert.
435 --
436 hr_dflex_utility.ins_or_upd_descflex_attribs
437 (p_appl_short_name => 'PER'
438 ,p_descflex_name => 'PER_GEN_HIERARCHY_VERSIONS'
439 ,p_attribute_category => p_rec.ATTRIBUTE_CATEGORY
440 ,p_attribute1_name => 'ATTRIBUTE1'
441 ,p_attribute1_value => p_rec.attribute1
442 ,p_attribute2_name => 'ATTRIBUTE2'
443 ,p_attribute2_value => p_rec.attribute2
444 ,p_attribute3_name => 'ATTRIBUTE3'
445 ,p_attribute3_value => p_rec.attribute3
446 ,p_attribute4_name => 'ATTRIBUTE4'
447 ,p_attribute4_value => p_rec.attribute4
448 ,p_attribute5_name => 'ATTRIBUTE5'
449 ,p_attribute5_value => p_rec.attribute5
450 ,p_attribute6_name => 'ATTRIBUTE6'
451 ,p_attribute6_value => p_rec.attribute6
452 ,p_attribute7_name => 'ATTRIBUTE7'
453 ,p_attribute7_value => p_rec.attribute7
454 ,p_attribute8_name => 'ATTRIBUTE8'
455 ,p_attribute8_value => p_rec.attribute8
456 ,p_attribute9_name => 'ATTRIBUTE9'
457 ,p_attribute9_value => p_rec.attribute9
458 ,p_attribute10_name => 'ATTRIBUTE10'
459 ,p_attribute10_value => p_rec.attribute10
460 ,p_attribute11_name => 'ATTRIBUTE11'
461 ,p_attribute11_value => p_rec.attribute11
462 ,p_attribute12_name => 'ATTRIBUTE12'
463 ,p_attribute12_value => p_rec.attribute12
464 ,p_attribute13_name => 'ATTRIBUTE13'
465 ,p_attribute13_value => p_rec.attribute13
469 ,p_attribute15_value => p_rec.attribute15
466 ,p_attribute14_name => 'ATTRIBUTE14'
467 ,p_attribute14_value => p_rec.attribute14
468 ,p_attribute15_name => 'ATTRIBUTE15'
470 ,p_attribute16_name => 'ATTRIBUTE16'
471 ,p_attribute16_value => p_rec.attribute16
472 ,p_attribute17_name => 'ATTRIBUTE17'
473 ,p_attribute17_value => p_rec.attribute17
474 ,p_attribute18_name => 'ATTRIBUTE18'
475 ,p_attribute18_value => p_rec.attribute18
476 ,p_attribute19_name => 'ATTRIBUTE19'
477 ,p_attribute19_value => p_rec.attribute19
478 ,p_attribute20_name => 'ATTRIBUTE20'
479 ,p_attribute20_value => p_rec.attribute20
480 ,p_attribute21_name => 'ATTRIBUTE21'
481 ,p_attribute21_value => p_rec.attribute21
482 ,p_attribute22_name => 'ATTRIBUTE22'
483 ,p_attribute22_value => p_rec.attribute22
484 ,p_attribute23_name => 'ATTRIBUTE23'
485 ,p_attribute23_value => p_rec.attribute23
486 ,p_attribute24_name => 'ATTRIBUTE24'
487 ,p_attribute24_value => p_rec.attribute24
488 ,p_attribute25_name => 'ATTRIBUTE25'
489 ,p_attribute25_value => p_rec.attribute25
490 ,p_attribute26_name => 'ATTRIBUTE26'
491 ,p_attribute26_value => p_rec.attribute26
492 ,p_attribute27_name => 'ATTRIBUTE27'
493 ,p_attribute27_value => p_rec.attribute27
494 ,p_attribute28_name => 'ATTRIBUTE28'
495 ,p_attribute28_value => p_rec.attribute28
496 ,p_attribute29_name => 'ATTRIBUTE29'
497 ,p_attribute29_value => p_rec.attribute29
498 ,p_attribute30_name => 'ATTRIBUTE30'
499 ,p_attribute30_value => p_rec.attribute30
500 );
501 end if;
502 --
503 hr_utility.set_location(' Leaving:'||l_proc,20);
504 end chk_df;
505 --
506 -- ----------------------------------------------------------------------------
507 -- |-----------------------< chk_non_updateable_args >------------------------|
508 -- ----------------------------------------------------------------------------
509 -- {Start Of Comments}
510 --
511 -- Description:
512 -- This procedure is used to ensure that non updateable attributes have
513 -- not been updated. If an attribute has been updated an error is generated.
514 --
515 -- Pre Conditions:
516 -- g_old_rec has been populated with details of the values currently in
517 -- the database.
518 --
519 -- In Arguments:
520 -- p_rec has been populated with the updated values the user would like the
521 -- record set to.
522 --
523 -- Post Success:
524 -- Processing continues if all the non updateable attributes have not
525 -- changed.
526 --
527 -- Post Failure:
528 -- An application error is raised if any of the non updatable attributes
529 -- have been altered.
530 --
531 -- {End Of Comments}
532 -- ----------------------------------------------------------------------------
533 Procedure chk_non_updateable_args
534 (p_effective_date in date
535 ,p_rec in per_pgv_shd.g_rec_type
536 ) IS
537 --
538 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
539 l_error EXCEPTION;
540 l_argument varchar2(30);
541 --
542 Begin
543 --
544 -- Only proceed with the validation if a row exists for the current
545 -- record in the HR Schema.
546 --
547 hr_utility.set_location('Entering:'|| l_proc, 10);
548 --
549 IF NOT per_pgv_shd.api_updating
550 (p_hierarchy_version_id => p_rec.hierarchy_version_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 hr_utility.set_location(l_proc, 15);
563 IF nvl(p_rec.business_group_id, hr_api.g_number) <>
564 nvl(per_pgv_shd.g_old_rec.business_group_id, hr_api.g_number) THEN
565 l_argument := 'business_group_id';
566 RAISE l_error;
567 END IF;
568 --
569 --
570 hr_utility.set_location(l_proc, 20);
571 --
572 IF nvl(p_rec.hierarchy_id, hr_api.g_number) <>
573 nvl(per_pgv_shd.g_old_rec.hierarchy_id, hr_api.g_number) THEN
574 l_argument := 'Hierarchy_Id';
575 RAISE l_error;
576 END IF;
577 hr_utility.set_location('Exiting:'|| l_proc, 30);
578 --
579 EXCEPTION
580 WHEN l_error THEN
581 hr_api.argument_changed_error
582 (p_api_name => l_proc
583 ,p_argument => l_argument);
584 WHEN OTHERS THEN
585 RAISE;
586 End chk_non_updateable_args;
587 --
588 --
592 --
589 -- ----------------------------------------------------------------------------
590 -- |-------------------------< chk_hierarchy_id >-----------------------------|
591 -- ----------------------------------------------------------------------------
593 -- Description:
594 -- Validates that HIERARCHY_ID exists.
595 --
596 -- Pre-conditions:
597 -- None.
598 --
599 -- In Arguments:
600 -- p_hierarchy_id
601 -- p_business_group_id
602 --
603 -- Post Success:
604 -- If the hierarchy_id exists then normal processing continues
605 --
606 -- Post Failure:
607 -- If the hierarchy_id does not exist then an application
608 -- error will be raised and processing is terminated.
609 --
610 -- Developer/Implementation Notes:
611 -- Duplicate validation exists on form, so any changes made here
612 -- or on form must be dual-maintained.
613 --
614 -- Access Status:
615 -- Internal Row Table Handler Use Only.
616 --
617 -- {End Of Comments}
618 --
619 PROCEDURE chk_hierarchy_id
620 ( p_hierarchy_id IN per_gen_hierarchy.hierarchy_id%TYPE,
621 p_business_group_id IN per_gen_hierarchy.business_group_id%TYPE)
622 IS
623 l_proc VARCHAR2(72) := g_package||'chk_hierarchy_id';
624 l_exists VARCHAR2(1) := 'N';
625 --
626 cursor csr_hierarchy_id IS
627 SELECT 'Y'
628 FROM per_gen_hierarchy
629 WHERE ( (p_business_group_id is not null and business_group_id = p_business_group_id)
630 or p_business_group_id is null )
631 AND hierarchy_id = p_hierarchy_id;
632 --
633 BEGIN
634 --
635 hr_utility.set_location('Entering:'|| l_proc, 10);
636 --
637 --
638 hr_api.mandatory_arg_error
639 (p_api_name => l_proc
640 ,p_argument => 'hierarchy_id'
641 ,p_argument_value => p_hierarchy_id
642 );
643 --
644 --
645 -- Check hierarchy_id existence
646 --
647 OPEN csr_hierarchy_id;
648 FETCH csr_hierarchy_id INTO l_exists;
649 --
650 hr_utility.set_location(l_proc, 20);
651 --
652 IF csr_hierarchy_id%notfound THEN
653 fnd_message.set_name('PER', 'HR_289071_PGV_HIER_NOT_EXIST');
654 fnd_message.raise_error;
655 CLOSE csr_hierarchy_id;
656 ELSE
657 CLOSE csr_hierarchy_id;
658 END IF;
659 --
660 --
661 hr_utility.set_location('Leaving:'||l_proc, 30);
662 --
663 END chk_hierarchy_id;
664 --
665 --
666 -- ----------------------------------------------------------------------------
667 -- |-------------------------< chk_multiple_versions >------------------------|
668 -- ----------------------------------------------------------------------------
669 --
670 -- Description:
671 -- Validates allow multiple version flag for the hierarchy type when the
672 -- version is not the first version for the hierarchy.
673 --
674 -- Pre-conditions:
675 -- None.
676 --
677 -- In Arguments:
678 -- p_hierarchy_id
679 --
680 -- Post Success:
681 -- If the version attemtped is the initial version for the hierarchy or
682 -- if allow multiple versions flag is 'Y' or null for the hierarchy type,
683 -- normal processing continues.
684 --
685 -- Post Failure:
686 -- If the version tried to be created is not the first version for the
687 -- hierarchy and allow multiple versions flag is 'N' for the hierarchy
688 -- type, error will be raised.
689 --
690 --
691 -- Developer/Implementation Notes:
692 -- None
693 --
694 -- Access Status:
695 -- Internal Row Table Handler Use Only.
696 --
697 -- {End Of Comments}
698 --
699
700 PROCEDURE chk_multiple_versions
701 (p_hierarchy_id IN Number
702 )
703 IS
704 --
705 CURSOR csr_multiple_version_flag IS
706 SELECT nvl(Information2, 'Y')
707 FROM per_shared_types
708 WHERE lookup_type = 'HIERARCHY_TYPE'
709 AND system_type_cd = (SELECT Type
710 FROM Per_Gen_Hierarchy
711 WHERE hierarchy_id = p_hierarchy_id)
712 AND system_type_cd = shared_type_code;
713 --
714 CURSOR csr_version_exists IS
715 SELECT 'Y'
716 FROM Per_Gen_Hierarchy_Versions
717 WHERE Hierarchy_Id = p_hierarchy_id;
718 --
719 --
720 l_multiple_versions Varchar2(1) := 'Y';
721 l_version_exists Varchar2(1);
722 l_proc Varchar2(72):= 'chk_multiple_versions';
723 --
724 BEGIN
725 --
726 hr_utility.set_location('Entering:'||l_proc, 10);
727 --
728 Open csr_multiple_version_flag;
729 Fetch csr_multiple_version_flag into l_multiple_versions;
730 Close csr_multiple_version_flag;
731 --
732 hr_utility.set_location(l_proc, 20);
733 --
734 Open csr_version_exists;
735 Fetch csr_version_exists into l_version_exists;
736 If csr_version_exists%found and l_multiple_versions = 'N'
737 then
738 hr_utility.set_location(l_proc, 30);
739 Close csr_version_exists;
740 fnd_message.set_name('PER','HR_449061_PGV_MULTIPLE_VERSION');
741 fnd_message.raise_error;
742 else
743 Close csr_version_exists;
744 End if;
745 --
749 --
746 hr_utility.set_location('Leaving:'||l_proc, 40);
747 --
748 end chk_multiple_versions;
750 -- ----------------------------------------------------------------------------
751 -- |-------------------------< chk_version_number >----------------------------|
752 -- ----------------------------------------------------------------------------
753 --
754 -- Description:
755 -- Validates that VERSION_NUMBER is a positive number.
756 --
757 -- Pre-conditions:
758 -- None.
759 --
760 -- In Arguments:
761 -- p_version_number
762 --
763 -- Post Success:
764 -- If the version_number is positive (or zero) then normal processing continues
765 --
766 -- Post Failure:
767 -- If the hierarchy_id does not exist then an application
768 -- error will be raised and processing is terminated.
769 --
770 -- Developer/Implementation Notes:
771 -- None.
772 --
773 -- Access Status:
774 -- Internal Row Table Handler Use Only.
775 --
776 -- {End Of Comments}
777 --
778 PROCEDURE chk_version_number
779 ( p_version_number IN per_gen_hierarchy_versions.version_number%TYPE
780 , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
781 , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
782 )
783 IS
784 l_proc VARCHAR2(72) := g_package||'chk_version_number';
785 l_api_updating boolean;
786 --
787 BEGIN
788 --
789 hr_utility.set_location('Entering:'|| l_proc, 10);
790 --
791 hr_utility.set_location(l_proc, 20);
792 hr_api.mandatory_arg_error
793 (p_api_name => l_proc
794 ,p_argument => 'version_number'
795 ,p_argument_value => p_version_number
796 );
797 --
798 hr_utility.set_location(l_proc, 30);
799 l_api_updating := per_pgv_shd.api_updating
800 (p_hierarchy_version_id => p_hierarchy_version_id
801 ,p_object_version_number => p_object_version_number);
802 --
803 hr_utility.set_location(l_proc, 40);
804 if ((l_api_updating and
805 nvl(per_pgv_shd.g_old_rec.version_number, hr_api.g_number) <>
806 nvl(p_version_number, hr_api.g_number))
807 or
808 NOT l_api_updating) then
809 --
810 hr_utility.set_location(l_proc, 50);
811 --
812 if p_version_number < 0 then
813 fnd_message.set_name('PER', 'HR_289078_PGV_VERSION_IS_NEG');
814 fnd_message.raise_error;
815 end if;
816 --
817 end if;
818 --
819 hr_utility.set_location('Leaving:'||l_proc, 60);
820 --
821 END chk_version_number;
822 -- ----------------------------------------------------------------------------
823 -- |-----------------------< chk_duplicate_version >--------------------------|
824 -- ----------------------------------------------------------------------------
825 --
826 -- Description:
827 -- Validates that VERSION_NUMBER is unique for a hierarchy.
828 -- Called only from insert_validate.
829 -- Pre-conditions:
830 -- None.
831 --
832 -- In Arguments:
833 -- p_version_number
834 -- p_hierarchy_id
835 --
836 -- Post Success:
837 -- If the version_number is unique then normal processing continues.
838 --
839 -- Post Failure:
840 -- If the version_number is already used for the hierarchy
841 -- error will be raised and processing is terminated.
842 --
843 -- Developer/Implementation Notes:
844 -- None.
845 --
846 -- Access Status:
847 -- Internal Row Table Handler Use Only.
848 --
849 -- {End Of Comments}
850 --
851 PROCEDURE chk_duplicate_version
852 ( p_version_number IN per_gen_hierarchy_versions.version_number%TYPE
853 , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
854 )
855 IS
856 l_proc VARCHAR2(72) := g_package||'chk_duplicate_version';
857 l_exists VARCHAR2(1);
858
859 CURSOR csr_dup_version IS
860 SELECT '1'
861 FROM per_gen_hierarchy_versions
862 WHERE hierarchy_id = p_hierarchy_id
863 and
864 version_number = p_version_number;
865 --
866 BEGIN
867 --
868 hr_utility.set_location('Entering:'|| l_proc, 10);
869 --
870 hr_utility.set_location(l_proc, 20);
871 hr_api.mandatory_arg_error
872 (p_api_name => l_proc
873 ,p_argument => 'hierarchy_id'
874 ,p_argument_value => p_hierarchy_id
875 );
876 --
877 hr_utility.set_location(l_proc, 30);
878 hr_api.mandatory_arg_error
879 (p_api_name => l_proc
880 ,p_argument => 'version_number'
881 ,p_argument_value => p_version_number
882 );
883
884 --
885 hr_utility.set_location(l_proc, 40);
886 --
887 OPEN csr_dup_version;
888 FETCH csr_dup_version INTO l_exists;
889 --
890 if csr_dup_version%FOUND then
891 CLOSE csr_dup_version;
892 fnd_message.set_name('PER', 'HR_449051_PGV_VERSION_DUP');
893 fnd_message.raise_error;
894 else
895 CLOSE csr_dup_version;
896 end if;
897 --
898 --
899 hr_utility.set_location('Leaving:'||l_proc, 50);
900 --
901 END chk_duplicate_version;
902 --
903 -- ----------------------------------------------------------------------------
907 -- Description:
904 -- |--------------------------< chk_date_from >-------------------------------|
905 -- ----------------------------------------------------------------------------
906 --
908 -- Validates that date_from of the hierarchy version is between date_from
909 -- and date_to of the business group, if business_group is not null.
910 --
911 -- Pre-conditions:
912 -- None.
913 --
914 -- In Arguments:
915 -- p_date_from
916 -- p_effective_date
917 -- p_business_group_id
918 --
919 -- Post Success:
920 -- If date_from of hierarchy version is between date_from and date_to
921 -- of the relevant business group then normal processing continues
922 --
923 -- Post Failure:
924 -- If the date_from of the hierarchy version is not between the date_from
925 -- and date_to of the relevant business group then an application
926 -- error will be raised and processing is terminated.
927 --
928 -- Developer/Implementation Notes:
929 -- Duplicate validation exists on form, so any changes made here
930 -- or on form must be dual-maintained
931 --
932 -- Access Status:
933 -- Internal Row Handler Use Only
934 --
935 -- {End Of Comments}
936 --
937 PROCEDURE chk_date_from
938 (p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
939 , p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
940 , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
941 , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
942 , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
943 , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
944 ) IS
945 --
946 l_proc VARCHAR2(72) := g_package||'chk_date_from';
947 l_date_between varchar2(1) := 'N';
948 l_api_updating boolean;
949 --
950 CURSOR csr_date_between IS
951 SELECT 'Y'
952 FROM hr_all_organization_units
953 WHERE p_date_from >= date_from
954 AND (p_date_from <= date_to
955 OR date_to is null)
956 AND (business_group_id = p_business_group_id);
957 --
958 --
959 CURSOR csr_other_open_ended_vers IS
960 SELECT 'Y'
961 FROM per_gen_hierarchy_versions
962 WHERE p_date_from > date_from
963 AND date_to is null
964 AND hierarchy_version_id <> nvl(p_hierarchy_version_id,-1)
965 AND hierarchy_id = p_hierarchy_id;
966 --
967 --
968 CURSOR csr_other_vers IS
969 SELECT 'Y'
970 FROM per_gen_hierarchy_versions
971 WHERE p_date_from >= date_from
972 AND (p_date_from <= date_to
973 OR date_to is null)
974 AND hierarchy_version_id <> nvl(p_hierarchy_version_id,-1)
975 AND hierarchy_id = p_hierarchy_id;
976 --
977 BEGIN
978 hr_utility.set_location('Entering:'|| l_proc, 10);
979 --
980 hr_utility.set_location(l_proc, 20);
981 hr_api.mandatory_arg_error
982 (p_api_name => l_proc
983 ,p_argument => 'Date_From'
984 ,p_argument_value => p_date_from
985 );
986 --
987 hr_utility.set_location(l_proc, 40);
988 l_api_updating := per_pgv_shd.api_updating
989 (p_hierarchy_version_id => p_hierarchy_version_id
990 ,p_object_version_number => p_object_version_number);
991 --
992 hr_utility.set_location(l_proc, 50);
993 if ((l_api_updating and
994 nvl(per_pgv_shd.g_old_rec.date_from, hr_api.g_date) <>
995 nvl(p_date_from, hr_api.g_date))
996 or
997 NOT l_api_updating) then
998 --
999 hr_utility.set_location(l_proc, 60);
1000 --
1001 -- Check date_from is between date_from and date_to for the business group
1002 --
1003 if p_business_group_id is not null then
1004 OPEN csr_date_between;
1005 FETCH csr_date_between INTO l_date_between;
1006 IF csr_date_between%notfound THEN
1007 CLOSE csr_date_between;
1008 fnd_message.set_name('PER', 'HR_289072_PGV_INV_DATE_FROM');
1009 fnd_message.raise_error;
1010 END IF;
1011 CLOSE csr_date_between;
1012 end if;
1013 --
1014 -- Check if the DATE_FROM is before DATE_TO
1015 --
1016 hr_utility.set_location(l_proc, 70);
1017 if not p_date_from <= nvl(p_date_to,p_date_from) then
1018 fnd_message.set_name('PER', 'HR_289073_PGV_DATE_FROM_BEFORE');
1019 fnd_message.raise_error;
1020 end if;
1021 --
1022 -- Check if the DATE_FROM is not after the DATE_FROM of an existing version that is open ended
1023 --
1024 hr_utility.set_location(l_proc, 75);
1025 --
1026 --
1027 OPEN csr_other_open_ended_vers;
1028 FETCH csr_other_open_ended_vers INTO l_date_between;
1029 IF csr_other_open_ended_vers%found THEN
1030 CLOSE csr_other_open_ended_vers;
1031 fnd_message.set_name('PER', 'HR_449052_PGV_OPEN_END_OVERLAP');
1032 fnd_message.raise_error;
1033 END IF;
1034 CLOSE csr_other_open_ended_vers;
1035 --
1036 -- Check if the DATE_FROM is not overlaped by dates of another version.
1037 --
1038 hr_utility.set_location(l_proc, 80);
1039 --
1040 OPEN csr_other_vers;
1041 FETCH csr_other_vers INTO l_date_between;
1042 IF csr_other_vers%found THEN
1043 CLOSE csr_other_vers;
1047 CLOSE csr_other_vers;
1044 fnd_message.set_name('PER', 'HR_289074_PGV_INV_DATE_OVERLAP');
1045 fnd_message.raise_error;
1046 END IF;
1048 --
1049 end if;
1050 hr_utility.set_location('Leaving:'||l_proc, 90);
1051 --
1052 END chk_date_from;
1053 --
1054 --
1055 -- ----------------------------------------------------------------------------
1056 -- |--------------------------< chk_date_to >---------------------------------|
1057 -- ----------------------------------------------------------------------------
1058 --
1059 -- Description:
1060 -- Validates that date_to of the hierarchy version is between date_from
1061 -- and date_to of the business group, if business_group is not null.
1062 --
1063 -- Pre-conditions:
1064 -- None.
1065 --
1066 -- In Arguments:
1067 -- p_date_to
1068 -- p_effective_date
1069 -- p_business_group_id
1070 --
1071 -- Post Success:
1072 -- If date_to of hierarchy version is between date_from and date_to
1073 -- of the relevant business group then normal processing continues
1074 --
1075 -- Post Failure:
1076 -- If the date_to of the hierarchy version is not between the date_from
1077 -- and date_to of the relevant business group then an application
1078 -- error will be raised and processing is terminated.
1079 --
1080 -- Developer/Implementation Notes:
1081 -- Duplicate validation exists on form, so any changes made here
1082 -- or on form must be dual-maintained
1083 --
1084 -- Access Status:
1085 -- Internal Row Handler Use Only
1086 --
1087 -- {End Of Comments}
1088 --
1089 PROCEDURE chk_date_to
1090 (p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
1091 , p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
1092 , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1093 , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
1094 , p_version_number IN number
1095 , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
1096 , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
1097 ) IS
1098 --
1099 l_proc VARCHAR2(72) := g_package||'chk_date_to';
1100 l_date_between varchar2(1) := 'N';
1101 l_api_updating boolean;
1102 l_date_to date;
1103 --
1104 CURSOR csr_date_between IS
1105 SELECT 'Y'
1106 FROM hr_all_organization_units
1107 WHERE l_date_to >= date_from
1108 AND (l_date_to <= date_to
1109 OR date_to is null)
1110 AND (organization_id = p_business_group_id);
1111 --
1112 --
1113 CURSOR csr_other_vers IS
1114 SELECT 'Y'
1115 FROM per_gen_hierarchy_versions
1116 WHERE l_date_to
1117 between date_from
1118 and nvl(date_to,l_date_to)
1119 AND hierarchy_version_id <> nvl(p_hierarchy_version_id,-1)
1120 AND hierarchy_id = p_hierarchy_id;
1121 --
1122 BEGIN
1123 hr_utility.set_location('Entering:'|| l_proc, 10);
1124 --
1125 l_date_to := nvl(p_date_to, hr_api.g_eot);
1126 --
1127 hr_utility.set_location(l_proc, 20);
1128 --
1129 hr_utility.set_location(l_proc, 30);
1130 l_api_updating := per_pgv_shd.api_updating
1131 (p_hierarchy_version_id => p_hierarchy_version_id
1132 ,p_object_version_number => p_object_version_number);
1133 --
1134 hr_utility.set_location(l_proc, 40);
1135 if ((l_api_updating and
1136 nvl(per_pgv_shd.g_old_rec.date_to, hr_api.g_date) <>
1137 nvl(p_date_to, hr_api.g_date))
1138 or
1139 NOT l_api_updating) then
1140 --
1141 hr_utility.set_location(l_proc, 50);
1142 --
1143 -- Check date_to is between date_from and date_to for the business group
1144 --
1145 if p_business_group_id is not null then
1146 OPEN csr_date_between;
1147 FETCH csr_date_between INTO l_date_between;
1148 IF csr_date_between%notfound THEN
1149 CLOSE csr_date_between;
1150 fnd_message.set_name('PER', 'HR_289072_PGV_INV_DATE_FROM');
1151 fnd_message.raise_error;
1152 END IF;
1153 CLOSE csr_date_between;
1154 end if;
1155 --
1156 -- Check if the DATE_TO is after DATE_FROM
1157 --
1158 hr_utility.set_location(l_proc, 60);
1159 if not l_date_to >= p_date_from then
1160 fnd_message.set_name('PER', 'HR_289073_PGV_DATE_FROM_BEFORE');
1161 fnd_message.raise_error;
1162 end if;
1163 --
1164 -- Check if the DATE_TO is not overlaped by dates of another version.
1165 --
1166 hr_utility.set_location(l_proc, 70);
1167 --
1168 OPEN csr_other_vers;
1169 FETCH csr_other_vers INTO l_date_between;
1170 IF csr_other_vers%found THEN
1171 CLOSE csr_other_vers;
1172 fnd_message.set_name('PER', 'HR_289074_PGV_INV_DATE_OVERLAP');
1173 fnd_message.raise_error;
1174 END IF;
1175 CLOSE csr_other_vers;
1176 --
1177 hr_utility.set_location('Leaving:'||l_proc, 80);
1178 end if;
1179 --
1180 --
1181 END chk_date_to;
1182 --
1183 --
1184 -- ----------------------------------------------------------------------------
1185 -- |--------------------------< chk_status >----------------------------------|
1186 -- ----------------------------------------------------------------------------
1187 --
1188 Procedure chk_status
1192 , p_object_version_number in per_gen_hierarchy_versions.object_version_number%TYPE
1189 (p_status in per_gen_hierarchy_versions.status%TYPE
1190 , p_effective_date in date
1191 , p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1193 )
1194 is
1195 --
1196 -- Declare local variables
1197 --
1198 l_proc varchar2(72) := g_package||'chk_status';
1199 l_api_updating boolean;
1200 --
1201 Begin
1202 --
1203 hr_utility.set_location('Entering: '||l_proc, 10);
1204 --
1205 hr_api.mandatory_arg_error
1206 (p_api_name => l_proc
1207 ,p_argument => 'effective_date'
1208 ,p_argument_value => p_effective_date
1209 );
1210 --
1211 hr_utility.set_location(l_proc, 20);
1212 --
1213 l_api_updating := per_pgv_shd.api_updating
1214 (p_hierarchy_version_id => p_hierarchy_version_id
1215 ,p_object_version_number => p_object_version_number);
1216 --
1217 hr_utility.set_location(l_proc, 30);
1218 if ((l_api_updating and
1219 nvl(per_pgv_shd.g_old_rec.status, hr_api.g_varchar2) <>
1220 nvl(p_status, hr_api.g_varchar2))
1221 or
1222 NOT l_api_updating) then
1223 --
1224 if hr_api.NOT_EXISTS_IN_HRSTANLOOKUPS
1225 (p_effective_date => p_effective_date
1226 ,p_lookup_type => 'ACTIVE_INACTIVE'
1227 ,p_lookup_code => p_status
1228 ) then
1229 -- Error Invalid Status
1230 fnd_message.set_name('PER', 'HR_289075_PGV_INV_STATUS');
1231 fnd_message.raise_error;
1232 end if;
1233 --
1234 hr_utility.set_location(l_proc, 40);
1235 --
1236 end if;
1237 --
1238 hr_utility.set_location('Leaving: '||l_proc, 50);
1239 --
1240 end chk_status;
1241 --
1242 --
1243 -- ----------------------------------------------------------------------------
1244 -- |--------------------------< chk_validate_flag >---------------------------|
1245 -- ----------------------------------------------------------------------------
1246 --
1247 Procedure chk_validate_flag
1248 (p_validate_flag in per_gen_hierarchy_versions.validate_flag%TYPE
1249 , p_effective_date in date
1250 , p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1251 , p_object_version_number in per_gen_hierarchy_versions.object_version_number%TYPE
1252 )
1253 is
1254 --
1255 -- Declare local variables
1256 --
1257 l_proc varchar2(72) := g_package||'chk_validate_flag';
1258 l_api_updating boolean;
1259 --
1260 Begin
1261 --
1262 hr_utility.set_location('Entering: '||l_proc, 10);
1263 --
1264 hr_api.mandatory_arg_error
1265 (p_api_name => l_proc
1266 ,p_argument => 'effective_date'
1267 ,p_argument_value => p_effective_date
1268 );
1269 --
1270 hr_utility.set_location(l_proc, 20);
1271 --
1272 l_api_updating := per_pgv_shd.api_updating
1273 (p_hierarchy_version_id => p_hierarchy_version_id
1274 ,p_object_version_number => p_object_version_number);
1275 --
1276 hr_utility.set_location(l_proc, 30);
1277 if ((l_api_updating and
1278 nvl(per_pgv_shd.g_old_rec.validate_flag, hr_api.g_varchar2) <>
1279 nvl(p_validate_flag, hr_api.g_varchar2))
1280 or
1281 NOT l_api_updating) then
1282 --
1283 if hr_api.NOT_EXISTS_IN_HRSTANLOOKUPS
1284 (p_effective_date => p_effective_date
1285 ,p_lookup_type => 'YES_NO'
1286 ,p_lookup_code => p_validate_flag
1287 ) then
1288 -- Error Invalid Validate Flag
1289 fnd_message.set_name('PER', 'HR_289076_PGV_INV_VALIDATE');
1290 fnd_message.raise_error;
1291 end if;
1292 --
1293 hr_utility.set_location(l_proc, 40);
1294 --
1295 end if;
1296 --
1297 hr_utility.set_location('Leaving: '||l_proc, 50);
1298 --
1299 end chk_validate_flag;
1300 --
1301 --
1302 -- ----------------------------------------------------------------------------
1303 -- |---------------------------< chk_delete >---------------------------------|
1304 -- ----------------------------------------------------------------------------
1305 Procedure chk_delete
1306 (p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1307 ) is
1308 --
1309 -- Declare local variables
1310 --
1311 l_exists varchar2(1) := 'Y';
1312 l_proc varchar2(72) := g_package||'chk_delete';
1313 --
1314 cursor csr_node_exists is
1315 select 'Y'
1316 from per_gen_hierarchy_nodes
1317 where hierarchy_version_id = p_hierarchy_version_id;
1318 --
1319 Begin
1320 --
1321 hr_utility.set_location('Entering: '||l_proc, 5);
1322 --
1323 hr_utility.set_location(l_proc, 10);
1324 hr_api.mandatory_arg_error
1325 (p_api_name => l_proc
1326 ,p_argument => 'hierarchy_version_id'
1327 ,p_argument_value => p_hierarchy_version_id
1328 );
1329 --
1330 -- Check if nodes of the hierarchy version exist
1331 --
1332 hr_utility.set_location(l_proc, 20);
1333 --
1334 open csr_node_exists;
1335 fetch csr_node_exists into l_exists;
1336 if csr_node_exists%notfound then
1337 close csr_node_exists;
1338 else
1339 close csr_node_exists;
1340 fnd_message.set_name('PER', 'HR_289077_PGV_DEL_HIER_VERS');
1341 fnd_message.raise_error;
1342 end if;
1343 --
1344 hr_utility.set_location('Leaving :'||l_proc, 30);
1345 --
1346 end chk_delete;
1347 --
1348 --
1349 -- ----------------------------------------------------------------------------
1350 -- |---------------------------< insert_validate >----------------------------|
1351 -- ----------------------------------------------------------------------------
1352 Procedure insert_validate
1353 (p_effective_date in date
1354 ,p_rec in per_pgv_shd.g_rec_type
1355 ) is
1356 --
1357 l_proc varchar2(72) := g_package||'insert_validate';
1358 --
1359 Begin
1360 hr_utility.set_location('Entering:'||l_proc, 5);
1361 --
1362 -- Call all supporting business operations
1363 --
1364 if p_rec.business_group_id is not null then
1365 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1366 end if;
1367
1368 --
1369 hr_utility.set_location(l_proc, 10);
1370 --
1371 -- Validate hierarchy_id
1372 -- ======================
1373 chk_hierarchy_id
1374 (p_hierarchy_id => p_rec.hierarchy_id
1375 ,p_business_group_id => p_rec.business_group_id
1376 );
1377 --
1378 hr_utility.set_location(l_proc, 20);
1379 --
1380 --
1381 -- Validate Allow Multiple Version flag
1382 -- =====================================
1383 chk_multiple_versions
1384 (p_hierarchy_id => p_rec.hierarchy_id
1385 );
1386 --
1387 -- Validate version_number
1388 -- ======================
1389 chk_version_number
1393 );
1390 (p_version_number => p_rec.version_number
1391 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1392 ,p_object_version_number => p_rec.object_version_number
1394 --
1395 hr_utility.set_location(l_proc, 30);
1396 --
1397 -- Validate version_number is unique
1398 -- ================================
1399 chk_duplicate_version
1400 (p_version_number => p_rec.version_number
1401 ,p_hierarchy_id => p_rec.hierarchy_id
1402 );
1403 --
1404 hr_utility.set_location(l_proc, 35);
1405 --
1406 -- Validate date_from
1407 -- ======================
1408 chk_date_from
1409 (p_date_from => p_rec.date_from
1410 ,p_date_to => p_rec.date_to
1411 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1412 ,p_hierarchy_id => p_rec.hierarchy_id
1413 ,p_business_group_id => p_rec.business_group_id
1414 ,p_object_version_number => p_rec.object_version_number
1415 );
1416 --
1417 hr_utility.set_location(l_proc, 40);
1418 --
1419 -- Validate date_to
1420 -- ======================
1421 chk_date_to
1422 (p_date_to => p_rec.date_to
1423 ,p_date_from => p_rec.date_from
1424 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1425 ,p_hierarchy_id => p_rec.hierarchy_id
1426 ,p_version_number => p_rec.version_number
1427 ,p_business_group_id => p_rec.business_group_id
1428 ,p_object_version_number => p_rec.object_version_number
1429 );
1430 --
1431 hr_utility.set_location(l_proc, 50);
1432 --
1433 -- Validate Status
1434 -- ======================
1435 chk_status
1436 (p_status => p_rec.status
1437 ,p_effective_date => p_effective_date
1438 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1439 ,p_object_version_number => p_rec.object_version_number
1440 );
1441 --
1442 hr_utility.set_location(l_proc, 60);
1443 --
1444 -- Validate Validate Flag
1445 -- ======================
1446 chk_validate_flag
1447 (p_validate_flag => p_rec.validate_flag
1448 ,p_effective_date => p_effective_date
1449 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1450 ,p_object_version_number => p_rec.object_version_number
1451 );
1452 --
1453 hr_utility.set_location(l_proc, 70);
1454 --
1455 per_pgv_bus.chk_ddf(p_rec);
1456
1457 per_pgv_bus.chk_df(p_rec);
1458 --
1459 hr_utility.set_location(' Leaving:'||l_proc, 80);
1460 End insert_validate;
1461 --
1462 -- ----------------------------------------------------------------------------
1463 -- |---------------------------< update_validate >----------------------------|
1464 -- ----------------------------------------------------------------------------
1465 Procedure update_validate
1466 (p_effective_date in date
1467 ,p_rec in per_pgv_shd.g_rec_type
1468 ) is
1469 --
1470 l_proc varchar2(72) := g_package||'update_validate';
1471 --
1472 Begin
1473 hr_utility.set_location('Entering:'||l_proc, 5);
1474 --
1475 -- Call all supporting business operations
1476 --
1477 if p_rec.business_group_id is not null then
1478 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1479 end if;
1480 --
1481 chk_non_updateable_args
1482 (p_effective_date => p_effective_date
1483 ,p_rec => p_rec
1484 );
1485 --
1486 hr_utility.set_location(l_proc, 10);
1487 --
1488 -- Validate version_number
1489 -- ======================
1490 chk_version_number
1491 (p_version_number => p_rec.version_number
1492 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1493 ,p_object_version_number => p_rec.object_version_number
1494 );
1495 --
1496 --
1497 hr_utility.set_location(l_proc, 20);
1498 --
1499 -- Validate date_from
1500 -- ======================
1501 chk_date_from
1502 (p_date_from => p_rec.date_from
1503 ,p_date_to => p_rec.date_to
1504 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1505 ,p_hierarchy_id => p_rec.hierarchy_id
1506 ,p_business_group_id => p_rec.business_group_id
1507 ,p_object_version_number => p_rec.object_version_number
1508 );
1509 --
1510 hr_utility.set_location(l_proc, 30);
1511 --
1512 -- Validate date_to
1513 -- ======================
1514 chk_date_to
1515 (p_date_to => p_rec.date_to
1516 ,p_date_from => p_rec.date_from
1517 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1518 ,p_hierarchy_id => p_rec.hierarchy_id
1519 ,p_version_number => p_rec.version_number
1520 ,p_business_group_id => p_rec.business_group_id
1521 ,p_object_version_number => p_rec.object_version_number
1522 );
1523 --
1524 hr_utility.set_location(l_proc, 40);
1525 --
1526 -- Validate Status
1527 -- ======================
1528 chk_status
1529 (p_status => p_rec.status
1530 ,p_effective_date => p_effective_date
1531 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1532 ,p_object_version_number => p_rec.object_version_number
1533 );
1534 --
1535 hr_utility.set_location(l_proc, 50);
1536 --
1537 -- Validate Validate Flag
1541 ,p_effective_date => p_effective_date
1538 -- ======================
1539 chk_validate_flag
1540 (p_validate_flag => p_rec.validate_flag
1542 ,p_hierarchy_version_id => p_rec.hierarchy_version_id
1543 ,p_object_version_number => p_rec.object_version_number
1544 );
1545 --
1546 hr_utility.set_location(l_proc, 60);
1547 --
1548 per_pgv_bus.chk_ddf(p_rec);
1549 per_pgv_bus.chk_df(p_rec);
1550 --
1551 hr_utility.set_location(' Leaving:'||l_proc, 70);
1552 --
1553 End update_validate;
1554 --
1555 --
1556 -- ----------------------------------------------------------------------------
1557 -- |---------------------------< delete_validate >----------------------------|
1558 -- ----------------------------------------------------------------------------
1559 Procedure delete_validate
1560 (p_rec in per_pgv_shd.g_rec_type
1561 ) is
1562 --
1563 l_proc varchar2(72) := g_package||'delete_validate';
1564 --
1565 Begin
1566 hr_utility.set_location('Entering:'||l_proc, 5);
1567 --
1568 -- Call all supporting business operations
1569 --
1570 hr_utility.set_location(l_proc, 10);
1571 --
1572 chk_delete
1573 (p_hierarchy_version_id => p_rec.hierarchy_version_id
1574 );
1575 --
1576 hr_utility.set_location(' Leaving:'||l_proc, 20);
1577 End delete_validate;
1578 --
1579 end per_pgv_bus;