[Home] [Help]
PACKAGE BODY: APPS.HR_DEI_BUS
Source
1 Package Body hr_dei_bus as
2 /* $Header: hrdeirhi.pkb 120.1 2005/09/09 08:32 kramajey noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_dei_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_document_extra_info_id number default null;
15 g_person_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_document_extra_info_id in number
23 ,p_associated_column1 in varchar2 default null
24 ) is
25 --
26 -- Declare cursor
27 --
28 cursor csr_sec_grp is
29 select pbg.security_group_id,
30 pbg.legislation_code
31 from per_business_groups_perf pbg
32 , hr_document_extra_info dei
33 , per_all_people_f ppf
34 where dei.document_extra_info_id = p_document_extra_info_id
35 and ppf.person_id = dei.person_id
36 and pbg.business_group_id = ppf.business_group_id;
37 --
38 -- Declare local variables
39 --
40 l_security_group_id number;
41 l_proc varchar2(72) := g_package||'set_security_group_id';
42 l_legislation_code varchar2(150);
43 --
44 begin
45 --
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47 --
48 -- Ensure that all the mandatory parameter are not null
49 --
50 hr_api.mandatory_arg_error
51 (p_api_name => l_proc
52 ,p_argument => 'document_extra_info_id'
53 ,p_argument_value => p_document_extra_info_id
54 );
55 --
56 open csr_sec_grp;
57 fetch csr_sec_grp into l_security_group_id
58 , l_legislation_code;
59 --
60 if csr_sec_grp%notfound then
61 --
62 close csr_sec_grp;
63 --
64 -- The primary key is invalid therefore we must error
65 --
66 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
67 hr_multi_message.add
68 (p_associated_column1
69 => nvl(p_associated_column1,'DOCUMENT_EXTRA_INFO_ID')
70 );
71 --
72 else
73 close csr_sec_grp;
74 --
75 -- Set the security_group_id in CLIENT_INFO
76 --
77 hr_api.set_security_group_id
78 (p_security_group_id => l_security_group_id
79 );
80 --
81 -- Set the sessions legislation context in HR_SESSION_DATA
82 --
83 hr_api.set_legislation_context(l_legislation_code);
84 end if;
85 --
86 hr_utility.set_location(' Leaving:'|| l_proc, 20);
87 --
88 end set_security_group_id;
89 --
90 -- ---------------------------------------------------------------------------
91 -- |---------------------< return_legislation_code >-------------------------|
92 -- ---------------------------------------------------------------------------
93 --
94 Function return_legislation_code
95 (p_document_extra_info_id in number
96 )
97 Return Varchar2 Is
98 --
99 -- Declare cursor
100 --
101 cursor csr_leg_code is
102 select pbg.legislation_code
103 from per_business_groups_perf pbg
104 , hr_document_extra_info dei
105 , per_all_people_f ppf
106 where dei.document_extra_info_id = p_document_extra_info_id
107 and ppf.person_id = dei.person_id
108 and pbg.business_group_id = ppf.business_group_id;
109 --
110 -- Declare local variables
111 --
112 l_legislation_code varchar2(150);
113 l_proc varchar2(72) := g_package||'return_legislation_code';
114 --
115 Begin
116 --
117 hr_utility.set_location('Entering:'|| l_proc, 10);
118 --
119 -- Ensure that all the mandatory parameter are not null
120 --
121 hr_api.mandatory_arg_error
122 (p_api_name => l_proc
123 ,p_argument => 'document_extra_info_id'
124 ,p_argument_value => p_document_extra_info_id
125 );
126 --
127 if ( nvl(hr_dei_bus.g_document_extra_info_id, hr_api.g_number)
128 = p_document_extra_info_id) then
129 --
130 -- The legislation code has already been found with a previous
131 -- call to this function. Just return the value in the global
132 -- variable.
133 --
134 l_legislation_code := hr_dei_bus.g_legislation_code;
135 hr_utility.set_location(l_proc, 20);
136 else
137 --
138 -- The ID is different to the last call to this function
139 -- or this is the first call to this function.
140 --
141 open csr_leg_code;
142 fetch csr_leg_code into l_legislation_code;
143 --
144 if csr_leg_code%notfound then
145 --
146 -- The primary key is invalid therefore we must error
147 --
148 close csr_leg_code;
149 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
150 fnd_message.raise_error;
151 end if;
152 hr_utility.set_location(l_proc,30);
153 --
154 -- Set the global variables so the values are
155 -- available for the next call to this function.
156 --
157 close csr_leg_code;
158 hr_dei_bus.g_document_extra_info_id := p_document_extra_info_id;
159 hr_dei_bus.g_legislation_code := l_legislation_code;
160 end if;
161 hr_utility.set_location(' Leaving:'|| l_proc, 40);
162 return l_legislation_code;
163 end return_legislation_code;
164 --
165 --
166 Function return_leg_code_perid
167 (p_person_id in number
168 )
169 Return Varchar2 Is
170 --
171 -- Declare cursor
172 --
173 cursor csr_leg_code is
174 select pbg.legislation_code
175 from per_business_groups_perf pbg
176 , per_all_people_f ppf
177 where ppf.person_id = p_person_id
178 and pbg.business_group_id = ppf.business_group_id;
179 --
180 -- Declare local variables
181 --
182 l_legislation_code varchar2(150);
183 l_proc varchar2(72) := g_package||'return_legislation_code';
184 --
185 Begin
186 --
187 hr_utility.set_location('Entering:'|| l_proc, 10);
188 --
189 -- Ensure that all the mandatory parameter are not null
190 --
191 hr_api.mandatory_arg_error
192 (p_api_name => l_proc
193 ,p_argument => 'person_id'
194 ,p_argument_value => p_person_id
195 );
196 --
197 if ( nvl(hr_dei_bus.g_person_id, hr_api.g_number)
198 = p_person_id) then
199 --
200 -- The legislation code has already been found with a previous
201 -- call to this function. Just return the value in the global
202 -- variable.
203 --
204 l_legislation_code := hr_dei_bus.g_legislation_code;
205 hr_utility.set_location(l_proc, 20);
206 else
207 --
208 -- The ID is different to the last call to this function
209 -- or this is the first call to this function.
210 --
211 open csr_leg_code;
212 fetch csr_leg_code into l_legislation_code;
213 --
214 if csr_leg_code%notfound then
215 --
216 -- The primary key is invalid therefore we must error
217 --
218 close csr_leg_code;
219 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
220 fnd_message.raise_error;
221 end if;
222 hr_utility.set_location(l_proc,30);
223 --
224 -- Set the global variables so the values are
225 -- available for the next call to this function.
226 --
227 close csr_leg_code;
228 hr_dei_bus.g_person_id := p_person_id;
229 hr_dei_bus.g_legislation_code := l_legislation_code;
230 end if;
231 hr_utility.set_location(' Leaving:'|| l_proc, 40);
232 return l_legislation_code;
233 end return_leg_code_perid;
234 --
235
236 -- ----------------------------------------------------------------------------
237 -- |-----------------------------< chk_ddf >----------------------------------|
238 -- ----------------------------------------------------------------------------
239 --
240 -- Description:
241 -- Validates all the Developer Descriptive Flexfield values.
242 --
243 -- Prerequisites:
244 -- All other columns have been validated. Must be called as the
245 -- last step from insert_validate and update_validate.
246 --
247 -- In Arguments:
248 -- p_rec
249 --
250 -- Post Success:
251 -- If the Developer Descriptive Flexfield structure column and data values
252 -- are all valid this procedure will end normally and processing will
253 -- continue.
254 --
255 -- Post Failure:
256 -- If the Developer Descriptive Flexfield structure column value or any of
257 -- the data values are invalid then an application error is raised as
258 -- a PL/SQL exception.
259 --
260 -- Access Status:
261 -- Internal Row Handler Use Only.
262 --
263 -- ----------------------------------------------------------------------------
264 procedure chk_ddf
265 (p_rec in hr_dei_shd.g_rec_type
266 ) is
267 --
268 l_proc varchar2(72) := g_package || 'chk_ddf';
269 --
270 begin
271 hr_utility.set_location('Entering:'||l_proc,10);
272 --
273 if ((p_rec.document_extra_info_id is not null) and (
274 nvl(hr_dei_shd.g_old_rec.dei_information_category, hr_api.g_varchar2) <>
275 nvl(p_rec.dei_information_category, hr_api.g_varchar2) or
276 nvl(hr_dei_shd.g_old_rec.dei_information1, hr_api.g_varchar2) <>
277 nvl(p_rec.dei_information1, hr_api.g_varchar2) or
278 nvl(hr_dei_shd.g_old_rec.dei_information2, hr_api.g_varchar2) <>
279 nvl(p_rec.dei_information2, hr_api.g_varchar2) or
280 nvl(hr_dei_shd.g_old_rec.dei_information3, hr_api.g_varchar2) <>
281 nvl(p_rec.dei_information3, hr_api.g_varchar2) or
282 nvl(hr_dei_shd.g_old_rec.dei_information4, hr_api.g_varchar2) <>
283 nvl(p_rec.dei_information4, hr_api.g_varchar2) or
284 nvl(hr_dei_shd.g_old_rec.dei_information5, hr_api.g_varchar2) <>
285 nvl(p_rec.dei_information5, hr_api.g_varchar2) or
286 nvl(hr_dei_shd.g_old_rec.dei_information6, hr_api.g_varchar2) <>
287 nvl(p_rec.dei_information6, hr_api.g_varchar2) or
288 nvl(hr_dei_shd.g_old_rec.dei_information7, hr_api.g_varchar2) <>
289 nvl(p_rec.dei_information7, hr_api.g_varchar2) or
290 nvl(hr_dei_shd.g_old_rec.dei_information8, hr_api.g_varchar2) <>
291 nvl(p_rec.dei_information8, hr_api.g_varchar2) or
292 nvl(hr_dei_shd.g_old_rec.dei_information9, hr_api.g_varchar2) <>
293 nvl(p_rec.dei_information9, hr_api.g_varchar2) or
294 nvl(hr_dei_shd.g_old_rec.dei_information10, hr_api.g_varchar2) <>
295 nvl(p_rec.dei_information10, hr_api.g_varchar2) or
296 nvl(hr_dei_shd.g_old_rec.dei_information11, hr_api.g_varchar2) <>
297 nvl(p_rec.dei_information11, hr_api.g_varchar2) or
298 nvl(hr_dei_shd.g_old_rec.dei_information12, hr_api.g_varchar2) <>
299 nvl(p_rec.dei_information12, hr_api.g_varchar2) or
300 nvl(hr_dei_shd.g_old_rec.dei_information13, hr_api.g_varchar2) <>
301 nvl(p_rec.dei_information13, hr_api.g_varchar2) or
302 nvl(hr_dei_shd.g_old_rec.dei_information14, hr_api.g_varchar2) <>
303 nvl(p_rec.dei_information14, hr_api.g_varchar2) or
304 nvl(hr_dei_shd.g_old_rec.dei_information15, hr_api.g_varchar2) <>
305 nvl(p_rec.dei_information15, hr_api.g_varchar2) or
306 nvl(hr_dei_shd.g_old_rec.dei_information16, hr_api.g_varchar2) <>
307 nvl(p_rec.dei_information16, hr_api.g_varchar2) or
308 nvl(hr_dei_shd.g_old_rec.dei_information17, hr_api.g_varchar2) <>
309 nvl(p_rec.dei_information17, hr_api.g_varchar2) or
310 nvl(hr_dei_shd.g_old_rec.dei_information18, hr_api.g_varchar2) <>
311 nvl(p_rec.dei_information18, hr_api.g_varchar2) or
312 nvl(hr_dei_shd.g_old_rec.dei_information19, hr_api.g_varchar2) <>
313 nvl(p_rec.dei_information19, hr_api.g_varchar2) or
314 nvl(hr_dei_shd.g_old_rec.dei_information20, hr_api.g_varchar2) <>
315 nvl(p_rec.dei_information20, hr_api.g_varchar2) or
316 nvl(hr_dei_shd.g_old_rec.dei_information21, hr_api.g_varchar2) <>
317 nvl(p_rec.dei_information21, hr_api.g_varchar2) or
318 nvl(hr_dei_shd.g_old_rec.dei_information22, hr_api.g_varchar2) <>
319 nvl(p_rec.dei_information22, hr_api.g_varchar2) or
320 nvl(hr_dei_shd.g_old_rec.dei_information23, hr_api.g_varchar2) <>
321 nvl(p_rec.dei_information23, hr_api.g_varchar2) or
322 nvl(hr_dei_shd.g_old_rec.dei_information24, hr_api.g_varchar2) <>
323 nvl(p_rec.dei_information24, hr_api.g_varchar2) or
324 nvl(hr_dei_shd.g_old_rec.dei_information25, hr_api.g_varchar2) <>
325 nvl(p_rec.dei_information25, hr_api.g_varchar2) or
326 nvl(hr_dei_shd.g_old_rec.dei_information26, hr_api.g_varchar2) <>
327 nvl(p_rec.dei_information26, hr_api.g_varchar2) or
328 nvl(hr_dei_shd.g_old_rec.dei_information27, hr_api.g_varchar2) <>
329 nvl(p_rec.dei_information27, hr_api.g_varchar2) or
330 nvl(hr_dei_shd.g_old_rec.dei_information28, hr_api.g_varchar2) <>
331 nvl(p_rec.dei_information28, hr_api.g_varchar2) or
332 nvl(hr_dei_shd.g_old_rec.dei_information29, hr_api.g_varchar2) <>
333 nvl(p_rec.dei_information29, hr_api.g_varchar2) or
334 nvl(hr_dei_shd.g_old_rec.dei_information30, hr_api.g_varchar2) <>
335 nvl(p_rec.dei_information30, hr_api.g_varchar2) ))
336 or (p_rec.document_extra_info_id is null) then
337 --
338 -- Only execute the validation if absolutely necessary:
339 -- a) During update, the structure column value or any
340 -- of the attribute values have actually changed.
341 -- b) During insert.
342 --
343 hr_dflex_utility.ins_or_upd_descflex_attribs
344 (p_appl_short_name => 'HR'
345 ,p_descflex_name => 'Extra Document Info DDF'
346 ,p_attribute_category => p_rec.dei_information_category
347 ,p_attribute1_name => 'DEI_INFORMATION1'
348 ,p_attribute1_value => p_rec.dei_information1
349 ,p_attribute2_name => 'DEI_INFORMATION2'
350 ,p_attribute2_value => p_rec.dei_information2
351 ,p_attribute3_name => 'DEI_INFORMATION3'
352 ,p_attribute3_value => p_rec.dei_information3
353 ,p_attribute4_name => 'DEI_INFORMATION4'
354 ,p_attribute4_value => p_rec.dei_information4
355 ,p_attribute5_name => 'DEI_INFORMATION5'
356 ,p_attribute5_value => p_rec.dei_information5
357 ,p_attribute6_name => 'DEI_INFORMATION6'
358 ,p_attribute6_value => p_rec.dei_information6
359 ,p_attribute7_name => 'DEI_INFORMATION7'
360 ,p_attribute7_value => p_rec.dei_information7
361 ,p_attribute8_name => 'DEI_INFORMATION8'
362 ,p_attribute8_value => p_rec.dei_information8
363 ,p_attribute9_name => 'DEI_INFORMATION9'
364 ,p_attribute9_value => p_rec.dei_information9
365 ,p_attribute10_name => 'DEI_INFORMATION10'
366 ,p_attribute10_value => p_rec.dei_information10
367 ,p_attribute11_name => 'DEI_INFORMATION11'
368 ,p_attribute11_value => p_rec.dei_information11
369 ,p_attribute12_name => 'DEI_INFORMATION12'
370 ,p_attribute12_value => p_rec.dei_information12
371 ,p_attribute13_name => 'DEI_INFORMATION13'
372 ,p_attribute13_value => p_rec.dei_information13
373 ,p_attribute14_name => 'DEI_INFORMATION14'
374 ,p_attribute14_value => p_rec.dei_information14
375 ,p_attribute15_name => 'DEI_INFORMATION15'
376 ,p_attribute15_value => p_rec.dei_information15
377 ,p_attribute16_name => 'DEI_INFORMATION16'
378 ,p_attribute16_value => p_rec.dei_information16
379 ,p_attribute17_name => 'DEI_INFORMATION17'
380 ,p_attribute17_value => p_rec.dei_information17
381 ,p_attribute18_name => 'DEI_INFORMATION18'
382 ,p_attribute18_value => p_rec.dei_information18
383 ,p_attribute19_name => 'DEI_INFORMATION19'
384 ,p_attribute19_value => p_rec.dei_information19
385 ,p_attribute20_name => 'DEI_INFORMATION20'
386 ,p_attribute20_value => p_rec.dei_information20
387 ,p_attribute21_name => 'DEI_INFORMATION21'
388 ,p_attribute21_value => p_rec.dei_information21
389 ,p_attribute22_name => 'DEI_INFORMATION22'
390 ,p_attribute22_value => p_rec.dei_information22
391 ,p_attribute23_name => 'DEI_INFORMATION23'
392 ,p_attribute23_value => p_rec.dei_information23
393 ,p_attribute24_name => 'DEI_INFORMATION24'
394 ,p_attribute24_value => p_rec.dei_information24
395 ,p_attribute25_name => 'DEI_INFORMATION25'
396 ,p_attribute25_value => p_rec.dei_information25
397 ,p_attribute26_name => 'DEI_INFORMATION26'
398 ,p_attribute26_value => p_rec.dei_information26
399 ,p_attribute27_name => 'DEI_INFORMATION27'
400 ,p_attribute27_value => p_rec.dei_information27
401 ,p_attribute28_name => 'DEI_INFORMATION28'
402 ,p_attribute28_value => p_rec.dei_information28
403 ,p_attribute29_name => 'DEI_INFORMATION29'
404 ,p_attribute29_value => p_rec.dei_information29
405 ,p_attribute30_name => 'DEI_INFORMATION30'
406 ,p_attribute30_value => p_rec.dei_information30
407 );
408
409 end if;
410 --
411 hr_utility.set_location(' Leaving:'||l_proc,20);
412 end chk_ddf;
413 --
414 -- ----------------------------------------------------------------------------
415 -- |------------------------------< chk_df >----------------------------------|
416 -- ----------------------------------------------------------------------------
417 --
418 -- Description:
419 -- Validates all the Descriptive Flexfield values.
420 --
421 -- Prerequisites:
422 -- All other columns have been validated. Must be called as the
423 -- last step from insert_validate and update_validate.
424 --
425 -- In Arguments:
426 -- p_rec
427 --
428 -- Post Success:
429 -- If the Descriptive Flexfield structure column and data values are
430 -- all valid this procedure will end normally and processing will
431 -- continue.
432 --
433 -- Post Failure:
434 -- If the Descriptive Flexfield structure column value or any of
435 -- the data values are invalid then an application error is raised as
436 -- a PL/SQL exception.
437 --
438 -- Access Status:
439 -- Internal Row Handler Use Only.
440 --
441 -- ----------------------------------------------------------------------------
442 procedure chk_df
443 (p_rec in hr_dei_shd.g_rec_type
444 ) is
445 --
446 l_proc varchar2(72) := g_package || 'chk_df';
447 --
448 begin
449 hr_utility.set_location('Entering:'||l_proc,10);
450 --
451 if ((p_rec.document_extra_info_id is not null) and (
452 nvl(hr_dei_shd.g_old_rec.dei_attribute_category, hr_api.g_varchar2) <>
453 nvl(p_rec.dei_attribute_category, hr_api.g_varchar2) or
454 nvl(hr_dei_shd.g_old_rec.dei_attribute1, hr_api.g_varchar2) <>
455 nvl(p_rec.dei_attribute1, hr_api.g_varchar2) or
456 nvl(hr_dei_shd.g_old_rec.dei_attribute2, hr_api.g_varchar2) <>
457 nvl(p_rec.dei_attribute2, hr_api.g_varchar2) or
458 nvl(hr_dei_shd.g_old_rec.dei_attribute3, hr_api.g_varchar2) <>
459 nvl(p_rec.dei_attribute3, hr_api.g_varchar2) or
460 nvl(hr_dei_shd.g_old_rec.dei_attribute4, hr_api.g_varchar2) <>
461 nvl(p_rec.dei_attribute4, hr_api.g_varchar2) or
462 nvl(hr_dei_shd.g_old_rec.dei_attribute5, hr_api.g_varchar2) <>
463 nvl(p_rec.dei_attribute5, hr_api.g_varchar2) or
464 nvl(hr_dei_shd.g_old_rec.dei_attribute6, hr_api.g_varchar2) <>
465 nvl(p_rec.dei_attribute6, hr_api.g_varchar2) or
466 nvl(hr_dei_shd.g_old_rec.dei_attribute7, hr_api.g_varchar2) <>
467 nvl(p_rec.dei_attribute7, hr_api.g_varchar2) or
468 nvl(hr_dei_shd.g_old_rec.dei_attribute8, hr_api.g_varchar2) <>
469 nvl(p_rec.dei_attribute8, hr_api.g_varchar2) or
470 nvl(hr_dei_shd.g_old_rec.dei_attribute9, hr_api.g_varchar2) <>
471 nvl(p_rec.dei_attribute9, hr_api.g_varchar2) or
472 nvl(hr_dei_shd.g_old_rec.dei_attribute10, hr_api.g_varchar2) <>
473 nvl(p_rec.dei_attribute10, hr_api.g_varchar2) or
474 nvl(hr_dei_shd.g_old_rec.dei_attribute11, hr_api.g_varchar2) <>
475 nvl(p_rec.dei_attribute11, hr_api.g_varchar2) or
476 nvl(hr_dei_shd.g_old_rec.dei_attribute12, hr_api.g_varchar2) <>
477 nvl(p_rec.dei_attribute12, hr_api.g_varchar2) or
478 nvl(hr_dei_shd.g_old_rec.dei_attribute13, hr_api.g_varchar2) <>
479 nvl(p_rec.dei_attribute13, hr_api.g_varchar2) or
480 nvl(hr_dei_shd.g_old_rec.dei_attribute14, hr_api.g_varchar2) <>
481 nvl(p_rec.dei_attribute14, hr_api.g_varchar2) or
482 nvl(hr_dei_shd.g_old_rec.dei_attribute15, hr_api.g_varchar2) <>
483 nvl(p_rec.dei_attribute15, hr_api.g_varchar2) or
484 nvl(hr_dei_shd.g_old_rec.dei_attribute16, hr_api.g_varchar2) <>
485 nvl(p_rec.dei_attribute16, hr_api.g_varchar2) or
486 nvl(hr_dei_shd.g_old_rec.dei_attribute17, hr_api.g_varchar2) <>
487 nvl(p_rec.dei_attribute17, hr_api.g_varchar2) or
488 nvl(hr_dei_shd.g_old_rec.dei_attribute18, hr_api.g_varchar2) <>
489 nvl(p_rec.dei_attribute18, hr_api.g_varchar2) or
490 nvl(hr_dei_shd.g_old_rec.dei_attribute19, hr_api.g_varchar2) <>
491 nvl(p_rec.dei_attribute19, hr_api.g_varchar2) or
492 nvl(hr_dei_shd.g_old_rec.dei_attribute20, hr_api.g_varchar2) <>
493 nvl(p_rec.dei_attribute20, hr_api.g_varchar2) or
494 nvl(hr_dei_shd.g_old_rec.dei_attribute21, hr_api.g_varchar2) <>
495 nvl(p_rec.dei_attribute21, hr_api.g_varchar2) or
496 nvl(hr_dei_shd.g_old_rec.dei_attribute22, hr_api.g_varchar2) <>
497 nvl(p_rec.dei_attribute22, hr_api.g_varchar2) or
498 nvl(hr_dei_shd.g_old_rec.dei_attribute23, hr_api.g_varchar2) <>
499 nvl(p_rec.dei_attribute23, hr_api.g_varchar2) or
500 nvl(hr_dei_shd.g_old_rec.dei_attribute24, hr_api.g_varchar2) <>
501 nvl(p_rec.dei_attribute24, hr_api.g_varchar2) or
502 nvl(hr_dei_shd.g_old_rec.dei_attribute25, hr_api.g_varchar2) <>
503 nvl(p_rec.dei_attribute25, hr_api.g_varchar2) or
504 nvl(hr_dei_shd.g_old_rec.dei_attribute26, hr_api.g_varchar2) <>
505 nvl(p_rec.dei_attribute26, hr_api.g_varchar2) or
506 nvl(hr_dei_shd.g_old_rec.dei_attribute27, hr_api.g_varchar2) <>
507 nvl(p_rec.dei_attribute27, hr_api.g_varchar2) or
508 nvl(hr_dei_shd.g_old_rec.dei_attribute28, hr_api.g_varchar2) <>
509 nvl(p_rec.dei_attribute28, hr_api.g_varchar2) or
510 nvl(hr_dei_shd.g_old_rec.dei_attribute29, hr_api.g_varchar2) <>
511 nvl(p_rec.dei_attribute29, hr_api.g_varchar2) or
512 nvl(hr_dei_shd.g_old_rec.dei_attribute30, hr_api.g_varchar2) <>
513 nvl(p_rec.dei_attribute30, hr_api.g_varchar2) ))
514 or (p_rec.document_extra_info_id is null) then
515 --
516 -- Only execute the validation if absolutely necessary:
517 -- a) During update, the structure column value or any
518 -- of the attribute values have actually changed.
519 -- b) During insert.
520 --
521 hr_dflex_utility.ins_or_upd_descflex_attribs
522 (p_appl_short_name => 'HR'
523 ,p_descflex_name => 'HR_DOCUMENT_EXTRA_INFO'
524 ,p_attribute_category => p_rec.dei_attribute_category
525 ,p_attribute1_name => 'DEI_ATTRIBUTE1'
526 ,p_attribute1_value => p_rec.dei_attribute1
527 ,p_attribute2_name => 'DEI_ATTRIBUTE2'
528 ,p_attribute2_value => p_rec.dei_attribute2
529 ,p_attribute3_name => 'DEI_ATTRIBUTE3'
530 ,p_attribute3_value => p_rec.dei_attribute3
531 ,p_attribute4_name => 'DEI_ATTRIBUTE4'
532 ,p_attribute4_value => p_rec.dei_attribute4
533 ,p_attribute5_name => 'DEI_ATTRIBUTE5'
534 ,p_attribute5_value => p_rec.dei_attribute5
535 ,p_attribute6_name => 'DEI_ATTRIBUTE6'
536 ,p_attribute6_value => p_rec.dei_attribute6
537 ,p_attribute7_name => 'DEI_ATTRIBUTE7'
538 ,p_attribute7_value => p_rec.dei_attribute7
539 ,p_attribute8_name => 'DEI_ATTRIBUTE8'
540 ,p_attribute8_value => p_rec.dei_attribute8
541 ,p_attribute9_name => 'DEI_ATTRIBUTE9'
542 ,p_attribute9_value => p_rec.dei_attribute9
543 ,p_attribute10_name => 'DEI_ATTRIBUTE10'
544 ,p_attribute10_value => p_rec.dei_attribute10
545 ,p_attribute11_name => 'DEI_ATTRIBUTE11'
546 ,p_attribute11_value => p_rec.dei_attribute11
547 ,p_attribute12_name => 'DEI_ATTRIBUTE12'
548 ,p_attribute12_value => p_rec.dei_attribute12
549 ,p_attribute13_name => 'DEI_ATTRIBUTE13'
550 ,p_attribute13_value => p_rec.dei_attribute13
551 ,p_attribute14_name => 'DEI_ATTRIBUTE14'
552 ,p_attribute14_value => p_rec.dei_attribute14
553 ,p_attribute15_name => 'DEI_ATTRIBUTE15'
554 ,p_attribute15_value => p_rec.dei_attribute15
555 ,p_attribute16_name => 'DEI_ATTRIBUTE16'
556 ,p_attribute16_value => p_rec.dei_attribute16
557 ,p_attribute17_name => 'DEI_ATTRIBUTE17'
558 ,p_attribute17_value => p_rec.dei_attribute17
559 ,p_attribute18_name => 'DEI_ATTRIBUTE18'
560 ,p_attribute18_value => p_rec.dei_attribute18
561 ,p_attribute19_name => 'DEI_ATTRIBUTE19'
562 ,p_attribute19_value => p_rec.dei_attribute19
563 ,p_attribute20_name => 'DEI_ATTRIBUTE20'
564 ,p_attribute20_value => p_rec.dei_attribute20
565 ,p_attribute21_name => 'DEI_ATTRIBUTE21'
566 ,p_attribute21_value => p_rec.dei_attribute21
567 ,p_attribute22_name => 'DEI_ATTRIBUTE22'
568 ,p_attribute22_value => p_rec.dei_attribute22
569 ,p_attribute23_name => 'DEI_ATTRIBUTE23'
570 ,p_attribute23_value => p_rec.dei_attribute23
571 ,p_attribute24_name => 'DEI_ATTRIBUTE24'
572 ,p_attribute24_value => p_rec.dei_attribute24
573 ,p_attribute25_name => 'DEI_ATTRIBUTE25'
574 ,p_attribute25_value => p_rec.dei_attribute25
575 ,p_attribute26_name => 'DEI_ATTRIBUTE26'
576 ,p_attribute26_value => p_rec.dei_attribute26
577 ,p_attribute27_name => 'DEI_ATTRIBUTE27'
578 ,p_attribute27_value => p_rec.dei_attribute27
579 ,p_attribute28_name => 'DEI_ATTRIBUTE28'
580 ,p_attribute28_value => p_rec.dei_attribute28
581 ,p_attribute29_name => 'DEI_ATTRIBUTE29'
582 ,p_attribute29_value => p_rec.dei_attribute29
583 ,p_attribute30_name => 'DEI_ATTRIBUTE30'
584 ,p_attribute30_value => p_rec.dei_attribute30
585 );
586 end if;
587 --
588 hr_utility.set_location(' Leaving:'||l_proc,20);
589 end chk_df;
590 --
591 -- ----------------------------------------------------------------------------
592 -- |-----------------------< chk_non_updateable_args >------------------------|
593 -- ----------------------------------------------------------------------------
594 -- {Start Of Comments}
595 --
596 -- Description:
597 -- This procedure is used to ensure that non updateable attributes have
598 -- not been updated. If an attribute has been updated an error is generated.
599 --
600 -- Pre Conditions:
601 -- g_old_rec has been populated with details of the values currently in
602 -- the database.
603 --
604 -- In Arguments:
605 -- p_rec has been populated with the updated values the user would like the
606 -- record set to.
607 --
608 -- Post Success:
609 -- Processing continues if all the non updateable attributes have not
610 -- changed.
611 --
612 -- Post Failure:
613 -- An application error is raised if any of the non updatable attributes
614 -- have been altered.
615 --
616 -- {End Of Comments}
617 -- ----------------------------------------------------------------------------
618 Procedure chk_non_updateable_args
619 (p_rec in hr_dei_shd.g_rec_type
620 ) IS
621 --
622 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
623 --
624 Begin
625 --
626 -- Only proceed with the validation if a row exists for the current
627 -- record in the HR Schema.
628 --
629 IF NOT hr_dei_shd.api_updating
630 (p_document_extra_info_id => p_rec.document_extra_info_id
631 ,p_object_version_number => p_rec.object_version_number
632 ) THEN
633 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
634 fnd_message.set_token('PROCEDURE ', l_proc);
635 fnd_message.set_token('STEP ', '5');
636 fnd_message.raise_error;
637 END IF;
638 --
639
640 --Check for Non- updation of person_id
641 --
642 --
643 if nvl(p_rec.person_id, hr_api.g_number) <>
644 hr_dei_shd.g_old_rec.person_id then
645
646 hr_api.argument_changed_error
647 (p_api_name => l_proc
648 ,p_argument => 'PERSON_ID'
649 ,p_base_table => hr_dei_shd.g_tab_nam
650 );
651
652 end if;
653
654 --Check for Non- updation of DOCUMENT_TYPE_ID
655 --
656 --
657 if nvl(p_rec.document_type_id, hr_api.g_number) <>
658 hr_dei_shd.g_old_rec.document_type_id then
659
660 hr_api.argument_changed_error
661 (p_api_name => l_proc
662 ,p_argument => 'DOCUMENT_TYPE_ID'
663 ,p_base_table => hr_dei_shd.g_tab_nam
664 );
665
666 end if;
667 --
668 --Check for Non- updation of document extra info id
669 --
670 --
671 hr_utility.set_location(l_proc, 8);
672 if nvl(p_rec.document_extra_info_id, hr_api.g_number) <>
673 hr_dei_shd.g_old_rec.document_extra_info_id then
674 hr_api.argument_changed_error
675 (p_api_name => l_proc
676 ,p_argument => 'DOCUMENT_EXTRA_INFO_ID'
677 ,p_base_table => hr_dei_shd.g_tab_nam
678 );
679 end if;
680 --
681 End chk_non_updateable_args;
682 --
683 -- ---------------------------------------------------------------------------
684 -- |------------------------< chk_date_from >-------------------------------|
685 -- ---------------------------------------------------------------------------
686 --
687 -- Description:
688 -- Validates that date from is less than or equal to date to .
689 --
690 -- Pre-conditions:
691 -- Format of p_date_from and p_date_to must be correct.
692 --
693 -- In Arguments:
694 -- p_document_extra_info_id
695 -- p_date_from
696 -- p_date_to
697 -- p_object_version_number
698 --
699 -- Post Success:
700 -- If a given date from is less than or equal to a given date to then
701 -- processing continues.
702 --
703 -- Post Failure:
704 -- If a given date from is not less than or equal to a given date to then
705 -- an application error will be raised and processing is terminated.
706 --
707 -- Access Status:
708 -- Internal Table Handler Use Only.
709 --
710 -- {End Of Comments}
711 -- ----------------------------------------------------------------------------
712 procedure chk_date_from
713 (p_document_extra_info_id in hr_document_extra_info.document_extra_info_id%type
714 ,p_date_from in hr_document_extra_info.date_from%TYPE
715 ,p_date_to in hr_document_extra_info.date_to%TYPE
716 ,p_object_version_number in hr_document_extra_info.object_version_number%TYPE)
717 is
718 --
719 l_exists varchar2(1);
720 l_proc varchar2(72) := g_package||'chk_date_from';
721 l_api_updating boolean;
722 --
723 begin
724 hr_utility.set_location('Entering:'|| l_proc, 1);
725 --
726 -- Check mandatory parameters have been set
727 --
728 hr_api.mandatory_arg_error
729 (p_api_name => l_proc
730 ,p_argument => 'date_from'
731 ,p_argument_value => p_date_from
732 );
733 --
734 --
735 -- Check that the date_from values is less than
736 -- or equal to the date_to value for the current
737 -- record
738 --
739 if p_date_from > nvl(p_date_to, hr_api.g_eot) then
740
741 hr_utility.set_message(801, 'HR_7303_ADD_DATE_FROM_EARLIER');
742 hr_utility.raise_error;
743 end if;
744 --
745
746 --
747 hr_utility.set_location(' Leaving:'|| l_proc, 2);
748 exception
749 when app_exception.application_exception then
750 if hr_multi_message.exception_add
751 (p_associated_column1 => 'HR_DOCUMENT_EXTRA_INFO.DATE_FROM'
752 ,p_associated_column2 => 'HR_DOCUMENT_EXTRA_INFO.DATE_TO'
753 ) then
754 hr_utility.set_location(' Leaving:'||l_proc,3);
755 raise;
756 end if;
757 hr_utility.set_location(' Leaving:'||l_proc,4);
758 --
759 end chk_date_from;
760 --
761 -- ---------------------------------------------------------------------------
762 -- |------------------------< chk_date_to >--------------------------------|
763 -- ---------------------------------------------------------------------------
764 --
765 -- Description:
766 -- Validates that date to is greater than or equal to date
767 -- from.
768 --
769 -- Pre-conditions:
770 -- Format of p_date_from and p_date_to must be correct.
771 --
772 -- In Arguments:
773 -- p_document_extra_info_id
774 -- p_date_from
775 -- p_date_to
776 -- p_object_version_number
777 --
778 -- Post Success:
779 -- If a given date to is greater than or equal to a given date from then
780 -- processing continues.
781 --
782 -- Post Failure:
783 -- If a given date to is not greater than or equal to a given date from then
784 -- an application error will be raised and processing is terminated.
785 --
786 -- Access status:
787 -- Internal Table Handler Use Only.
788 --
789 -- {End Of Comments}
790 -- ----------------------------------------------------------------------------
791 procedure chk_date_to
792 (p_document_extra_info_id in hr_document_extra_info.document_extra_info_id%TYPE
793 ,p_date_from in hr_document_extra_info.date_from%TYPE
794 ,p_date_to in hr_document_extra_info.date_to%TYPE
795 ,p_object_version_number in hr_document_extra_info.object_version_number%TYPE)
796 is
797 --
798 l_exists varchar2(1);
799 l_proc varchar2(72) := g_package||'chk_date_to';
800 l_date_to date;
801 l_api_updating boolean;
802 --
803 begin
804 hr_utility.set_location('Entering:'|| l_proc, 1);
805 --
806 -- Check mandatory parameters have been set
807 --
808 hr_api.mandatory_arg_error
809 (p_api_name => l_proc
810 ,p_argument => 'date_from'
811 ,p_argument_value => p_date_from
812 );
813
814 hr_api.mandatory_arg_error
815 (p_api_name => l_proc
816 ,p_argument => 'date_to'
817 ,p_argument_value => p_date_to
818 );
819
820 -- Checks that the value for date_to is greater than or
821 -- equal to the corresponding value for date_from for the
822 -- same record
823 --
824 if nvl(p_date_to, hr_api.g_eot) < p_date_from then
825
826 hr_utility.set_message(801, 'HR_7301_ADD_DATE_TO_LATER');
827 hr_utility.raise_error;
828 end if;
829 --
830
831 hr_utility.set_location(' Leaving:'|| l_proc, 3);
832 exception
833 when app_exception.application_exception then
834 if hr_multi_message.exception_add
835 (p_same_associated_columns => 'Y'
836 ) then
837 hr_utility.set_location(' Leaving:'||l_proc,4);
838 raise;
839 end if;
840 hr_utility.set_location(' Leaving:'||l_proc,5);
841 --
842 end chk_date_to;
843 --
844 -- ----------------------------------------------------------------------------
845 -- |-------------------------< chk_document_type_id >------------------------|
846 -- ----------------------------------------------------------------------------
847 -- {Start Of Comments}
848 --
849 -- Description:
850 -- This procedure ensures that document_type_id value is valid.
851 --
852 -- Pre Conditions:
853 -- g_old_rec has been populated with details of the values currently in
854 -- the database.
855 --
856 -- In Arguments:
857 -- p_document_type_id
858 -- p_document_extra_info_id
859 -- p_object_version_number
860 --
861 -- Post Success:
862 -- Processing continues if document_type_id is valid and if updating,
863 -- old_rec.document_type_id is null
864 --
865 -- Post Failure:
866 -- An application error is raised
867 -- if updating and old_rec.business_group_id is not null, or
868 -- business_group_id is not valid.
869 --
870 -- {End Of Comments}
871 -- ----------------------------------------------------------------------------
872 procedure chk_document_type_id
873 (
874 p_document_type_id in hr_document_extra_info.document_type_id%TYPE
875 )
876 is
877 --
878
879 --
880 l_proc varchar2(72) := g_package||'chk_document_type_id';
881 l_api_updating boolean;
882 l_doc_type_id number;
883
884 -- Cursor for Validating Document Type Id
885
886 cursor csr_valid_doc_id is
887 select document_type_id
888 from hr_document_types hdt,
889 hr_lookups hrl
890 where hrl.lookup_code = hdt.category_code
891 and hrl.lookup_type = 'DOCUMENT_CATEGORY'
892 and hdt.document_type_id = p_document_type_id;
893 --
894 begin
895 hr_utility.set_location('Entering:'||l_proc, 5);
896
897 --
898 -- Check that the Document Type Id is linked to a
899 -- valid document type id on hr_document_types
900 --
901
902
903 open csr_valid_doc_id;
904 fetch csr_valid_doc_id into l_doc_type_id;
905 if csr_valid_doc_id%notfound then
906
907 --
908 close csr_valid_doc_id;
909 hr_utility.set_message(800, 'HR_449710_DOR_INVL_VAL');
910 hr_utility.set_message_token('OBJECT', 'DOCUMENT_TYPE_ID');
911 hr_utility.set_message_token('TABLE', 'HR_DOCUMENT_TYPES');
912 hr_utility.raise_error;
913 --
914 else
915 close csr_valid_doc_id;
916
917 end if;
918 hr_utility.set_location(l_proc, 20);
919 --
920
921 --
922
923
924
925 hr_utility.set_location('Leaving:'||l_proc, 30);
926
927 exception
928 when app_exception.application_exception then
929 if hr_multi_message.exception_add
930 (p_associated_column1 => 'hr_document_types.document_type_id'
931 ) then
932 hr_utility.set_location('Leaving:'||l_proc, 50);
933 raise;
934 end if;
935 hr_utility.set_location('Leaving:'||l_proc,60);
936 end chk_document_type_id;
937 --
938 -- ---------------------------------------------------------------------------
939 -- |---------------------------< chk_person_id >----------------------------|
940 -- ---------------------------------------------------------------------------
941 --
942 -- Description:
943 -- - Validates that a person id exists in table per_people_f.
944 -- - Validates that the business group of the address matches
945 -- the business group of the person.
946 --
947 -- Pre-conditions:
948 -- None.
949 --
950 -- In Arguments:
951 -- p_person_id
952 --
953 --
954 -- Post Success:
955 -- If a row does exist in per_people_f for the given person id then
956 -- processing continues.
957 --
958 -- Post Failure:
959 -- If a row does not exist in per_people_f for the given person id then
960 -- an application error will be raised and processing is terminated.
961 --
962 -- Access Status:
963 -- Internal Table Handler Use Only.
964 --
965 -- {End Of Comments}
966 -- ----------------------------------------------------------------------------
967 procedure chk_person_id
968 (p_document_extra_info_id in hr_document_extra_info.document_extra_info_id%TYPE
969 ,p_object_version_number in hr_document_extra_info.object_version_number%TYPE
970 ,p_person_id in hr_document_extra_info.person_id%TYPE
971 )
972 is
973 --
974 l_proc varchar2(72) := g_package||'chk_person_id';
975 --
976 l_api_updating boolean;
977 l_person_id number;
978 --
979 cursor csr_valid_pers is
980 select person_id
981 from per_people_f ppf
982 where ppf.person_id = p_person_id;
983 --
984 begin
985 hr_utility.set_location('Entering:'|| l_proc, 10);
986 --
987 -- Check mandatory parameters have been set
988 --
989 hr_api.mandatory_arg_error
990 (p_api_name => l_proc
991 ,p_argument => 'person_id'
992 ,p_argument_value => p_person_id
993 );
994 --
995 --
996 -- Check that the Person ID is linked to a
997 -- valid person on PER_PEOPLE_F
998 --
999 open csr_valid_pers;
1000 fetch csr_valid_pers into l_person_id;
1001 if csr_valid_pers%notfound then
1002
1003 --
1004 close csr_valid_pers;
1005 hr_utility.set_message(801, 'HR_7298_ADD_PERSON_INVALID');
1006 hr_utility.raise_error;
1007 --
1008 else
1009 close csr_valid_pers;
1010 hr_utility.set_location(l_proc, 20);
1011 --
1012
1013
1014 end if;
1015
1016 --
1017 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1018
1019 exception
1020 when app_exception.application_exception then
1021 if hr_multi_message.exception_add
1022 (p_associated_column1 => 'HR_DOCUMENT_EXTRA_INFO.PERSON_ID'
1023 ) then
1024 hr_utility.set_location(' Leaving:'||l_proc,60);
1025 raise;
1026 end if;
1027 hr_utility.set_location(' Leaving:'||l_proc,70);
1028 end chk_person_id;
1029 --
1030
1031 --
1032
1033 -- ----------------------------------------------------------------------------
1034 -- |---------------------------< insert_validate >----------------------------|
1035 -- ----------------------------------------------------------------------------
1036 Procedure insert_validate
1037 (p_rec in hr_dei_shd.g_rec_type
1038 ) is
1039 --
1040 l_proc varchar2(72) := g_package||'insert_validate';
1041 --
1042 Begin
1043 hr_utility.set_location('Entering:'||l_proc, 5);
1044 --
1045 -- Call all supporting business operations
1046 --
1047 per_per_bus.set_security_group_id(p_person_id => p_rec.person_id);
1048
1049 --
1050 -- Validate Dependent Attributes
1051 --
1052 -- Validate date from
1053 --
1054 chk_date_from
1055 (p_document_extra_info_id => p_rec.document_extra_info_id
1056 ,p_date_from => p_rec.date_from
1057 ,p_date_to => p_rec.date_to
1058 ,p_object_version_number => p_rec.object_version_number
1059 );
1060 -- Validate Date To
1061 chk_date_to
1062 (p_document_extra_info_id => p_rec.document_extra_info_id
1063 ,p_date_from => p_rec.date_from
1064 ,p_date_to => p_rec.date_to
1065 ,p_object_version_number => p_rec.object_version_number
1066 );
1067 -- Validate Document type ID
1068 chk_document_type_id
1069 (p_document_type_id => p_rec.document_type_id
1070 );
1071 -- Validate Person ID
1072 chk_person_id
1073 (p_document_extra_info_id => p_rec.document_extra_info_id
1074 ,p_object_version_number => p_rec.object_version_number
1075 ,p_person_id => p_rec.person_id
1076 );
1077
1078
1079 hr_utility.set_location(l_proc, 10);
1080 --
1081 -- hr_dei_bus.chk_ddf(p_rec);
1082 --
1083 -- hr_dei_bus.chk_df(p_rec);
1084 --
1085 -- hr_utility.set_location(' Leaving:'||l_proc, 10);
1086 End insert_validate;
1087 --
1088
1089
1090
1091 -- ----------------------------------------------------------------------------
1092 -- |---------------------------< update_validate >----------------------------|
1093 -- ----------------------------------------------------------------------------
1094 Procedure update_validate
1095 (p_rec in hr_dei_shd.g_rec_type
1096 ) is
1097 --
1098 l_proc varchar2(72) := g_package||'update_validate';
1099 --
1100 Begin
1101 hr_utility.set_location('Entering:'||l_proc, 5);
1102 --
1103 -- Call all supporting business operations
1104 --
1105 per_per_bus.set_security_group_id(p_person_id => p_rec.person_id);
1106 --
1107 -- Validate Dependent Attributes
1108 --
1109 chk_non_updateable_args(p_rec => p_rec);
1110 --
1111 --
1112 -- Validate date from
1113 --
1114 chk_date_from
1115 (p_document_extra_info_id => p_rec.document_extra_info_id
1116 ,p_date_from => p_rec.date_from
1117 ,p_date_to => p_rec.date_to
1118 ,p_object_version_number => p_rec.object_version_number
1119 );
1120 -- Validate Date To
1121 chk_date_to
1122 (p_document_extra_info_id => p_rec.document_extra_info_id
1123 ,p_date_from => p_rec.date_from
1124 ,p_date_to => p_rec.date_to
1125 ,p_object_version_number => p_rec.object_version_number
1126 );
1127 -- Validate Document type ID
1128 chk_document_type_id
1129 (p_document_type_id => p_rec.document_type_id
1130 );
1131
1132
1133 -- hr_dei_bus.chk_ddf(p_rec);
1134 --
1135 -- hr_dei_bus.chk_df(p_rec);
1136 --
1137 hr_utility.set_location(' Leaving:'||l_proc, 10);
1138 End update_validate;
1139 --
1140 -- ----------------------------------------------------------------------------
1141 -- |---------------------------< delete_validate >----------------------------|
1142 -- ----------------------------------------------------------------------------
1143 Procedure delete_validate
1144 (p_rec in hr_dei_shd.g_rec_type
1145 ) is
1146 --
1147 l_proc varchar2(72) := g_package||'delete_validate';
1148 --
1149 Begin
1150 hr_utility.set_location('Entering:'||l_proc, 5);
1151 --
1152 -- Call all supporting business operations
1153 --
1154 hr_utility.set_location(' Leaving:'||l_proc, 10);
1155 End delete_validate;
1156 --
1157 end hr_dei_bus;