[Home] [Help]
PACKAGE BODY: APPS.IRC_ISC_BUS
Source
1 Package Body irc_isc_bus as
2 /* $Header: iriscrhi.pkb 120.0 2005/07/26 15:11:17 mbocutt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_isc_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_search_criteria_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_search_criteria_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare local variables
26 --
27 l_proc varchar2(72) := g_package||'set_security_group_id';
28 --
29 begin
30 --
31 hr_utility.set_location('Entering:'|| l_proc, 10);
32 --
33 -- Ensure that all the mandatory parameter are not null
34 --
35 hr_api.mandatory_arg_error
36 (p_api_name => l_proc
37 ,p_argument => 'search_criteria_id'
38 ,p_argument_value => p_search_criteria_id
39 );
40 --
41 hr_utility.set_location(' Leaving:'|| l_proc, 20);
42 --
43 end set_security_group_id;
44 --
45 -- ---------------------------------------------------------------------------
46 -- |---------------------< return_legislation_code >-------------------------|
47 -- ---------------------------------------------------------------------------
48 --
49 Function return_legislation_code
50 (p_search_criteria_id in number
51 )
52 Return Varchar2 Is
53 --
54 -- Declare local variables
55 --
56 l_legislation_code varchar2(150) := 'NONE';
57 l_proc varchar2(72) := g_package||'return_legislation_code';
58 --
59 Begin
60 --
61 hr_utility.set_location('Entering:'|| l_proc, 10);
62 --
63 -- Ensure that all the mandatory parameter are not null
64 --
65 hr_api.mandatory_arg_error
66 (p_api_name => l_proc
67 ,p_argument => 'search_criteria_id'
68 ,p_argument_value => p_search_criteria_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 40);
72 return l_legislation_code;
73 end return_legislation_code;
74 --
75 -- ----------------------------------------------------------------------------
76 -- |-----------------------------< chk_ddf >----------------------------------|
77 -- ----------------------------------------------------------------------------
78 --
79 -- Description:
80 -- Validates all the Developer Descriptive Flexfield values.
81 --
82 -- Prerequisites:
83 -- All other columns have been validated. Must be called as the
84 -- last step from insert_validate and update_validate.
85 --
86 -- In Arguments:
87 -- p_rec
88 --
89 -- Post Success:
90 -- If the Developer Descriptive Flexfield structure column and data values
91 -- are all valid this procedure will end normally and processing will
92 -- continue.
93 --
94 -- Post Failure:
95 -- If the Developer Descriptive Flexfield structure column value or any of
96 -- the data values are invalid then an application error is raised as
97 -- a PL/SQL exception.
98 --
99 -- Access Status:
100 -- Internal Row Handler Use Only.
101 --
102 -- ----------------------------------------------------------------------------
103 procedure chk_ddf
104 (p_rec in irc_isc_shd.g_rec_type
105 ) is
106 --
107 l_proc varchar2(72) := g_package || 'chk_ddf';
108 --
109 begin
110 hr_utility.set_location('Entering:'||l_proc,10);
111 --
112 if ((p_rec.search_criteria_id is not null) and (
113 nvl(irc_isc_shd.g_old_rec.isc_information_category, hr_api.g_varchar2) <>
114 nvl(p_rec.isc_information_category, hr_api.g_varchar2) or
115 nvl(irc_isc_shd.g_old_rec.isc_information1, hr_api.g_varchar2) <>
116 nvl(p_rec.isc_information1, hr_api.g_varchar2) or
117 nvl(irc_isc_shd.g_old_rec.isc_information2, hr_api.g_varchar2) <>
118 nvl(p_rec.isc_information2, hr_api.g_varchar2) or
119 nvl(irc_isc_shd.g_old_rec.isc_information3, hr_api.g_varchar2) <>
120 nvl(p_rec.isc_information3, hr_api.g_varchar2) or
121 nvl(irc_isc_shd.g_old_rec.isc_information4, hr_api.g_varchar2) <>
122 nvl(p_rec.isc_information4, hr_api.g_varchar2) or
123 nvl(irc_isc_shd.g_old_rec.isc_information5, hr_api.g_varchar2) <>
124 nvl(p_rec.isc_information5, hr_api.g_varchar2) or
125 nvl(irc_isc_shd.g_old_rec.isc_information6, hr_api.g_varchar2) <>
126 nvl(p_rec.isc_information6, hr_api.g_varchar2) or
127 nvl(irc_isc_shd.g_old_rec.isc_information7, hr_api.g_varchar2) <>
128 nvl(p_rec.isc_information7, hr_api.g_varchar2) or
129 nvl(irc_isc_shd.g_old_rec.isc_information8, hr_api.g_varchar2) <>
130 nvl(p_rec.isc_information8, hr_api.g_varchar2) or
131 nvl(irc_isc_shd.g_old_rec.isc_information9, hr_api.g_varchar2) <>
132 nvl(p_rec.isc_information9, hr_api.g_varchar2) or
133 nvl(irc_isc_shd.g_old_rec.isc_information10, hr_api.g_varchar2) <>
134 nvl(p_rec.isc_information10, hr_api.g_varchar2) or
135 nvl(irc_isc_shd.g_old_rec.isc_information11, hr_api.g_varchar2) <>
136 nvl(p_rec.isc_information11, hr_api.g_varchar2) or
137 nvl(irc_isc_shd.g_old_rec.isc_information12, hr_api.g_varchar2) <>
138 nvl(p_rec.isc_information12, hr_api.g_varchar2) or
139 nvl(irc_isc_shd.g_old_rec.isc_information13, hr_api.g_varchar2) <>
140 nvl(p_rec.isc_information13, hr_api.g_varchar2) or
141 nvl(irc_isc_shd.g_old_rec.isc_information14, hr_api.g_varchar2) <>
142 nvl(p_rec.isc_information14, hr_api.g_varchar2) or
143 nvl(irc_isc_shd.g_old_rec.isc_information15, hr_api.g_varchar2) <>
144 nvl(p_rec.isc_information15, hr_api.g_varchar2) or
145 nvl(irc_isc_shd.g_old_rec.isc_information16, hr_api.g_varchar2) <>
146 nvl(p_rec.isc_information16, hr_api.g_varchar2) or
147 nvl(irc_isc_shd.g_old_rec.isc_information17, hr_api.g_varchar2) <>
148 nvl(p_rec.isc_information17, hr_api.g_varchar2) or
149 nvl(irc_isc_shd.g_old_rec.isc_information18, hr_api.g_varchar2) <>
150 nvl(p_rec.isc_information18, hr_api.g_varchar2) or
151 nvl(irc_isc_shd.g_old_rec.isc_information19, hr_api.g_varchar2) <>
152 nvl(p_rec.isc_information19, hr_api.g_varchar2) or
153 nvl(irc_isc_shd.g_old_rec.isc_information20, hr_api.g_varchar2) <>
154 nvl(p_rec.isc_information20, hr_api.g_varchar2) or
155 nvl(irc_isc_shd.g_old_rec.isc_information21, hr_api.g_varchar2) <>
156 nvl(p_rec.isc_information21, hr_api.g_varchar2) or
157 nvl(irc_isc_shd.g_old_rec.isc_information22, hr_api.g_varchar2) <>
158 nvl(p_rec.isc_information22, hr_api.g_varchar2) or
159 nvl(irc_isc_shd.g_old_rec.isc_information23, hr_api.g_varchar2) <>
160 nvl(p_rec.isc_information23, hr_api.g_varchar2) or
161 nvl(irc_isc_shd.g_old_rec.isc_information24, hr_api.g_varchar2) <>
162 nvl(p_rec.isc_information24, hr_api.g_varchar2) or
163 nvl(irc_isc_shd.g_old_rec.isc_information25, hr_api.g_varchar2) <>
164 nvl(p_rec.isc_information25, hr_api.g_varchar2) or
165 nvl(irc_isc_shd.g_old_rec.isc_information26, hr_api.g_varchar2) <>
166 nvl(p_rec.isc_information26, hr_api.g_varchar2) or
167 nvl(irc_isc_shd.g_old_rec.isc_information27, hr_api.g_varchar2) <>
168 nvl(p_rec.isc_information27, hr_api.g_varchar2) or
169 nvl(irc_isc_shd.g_old_rec.isc_information28, hr_api.g_varchar2) <>
170 nvl(p_rec.isc_information28, hr_api.g_varchar2) or
171 nvl(irc_isc_shd.g_old_rec.isc_information29, hr_api.g_varchar2) <>
172 nvl(p_rec.isc_information29, hr_api.g_varchar2) or
173 nvl(irc_isc_shd.g_old_rec.isc_information30, hr_api.g_varchar2) <>
174 nvl(p_rec.isc_information30, hr_api.g_varchar2) ))
175 or (p_rec.search_criteria_id is null) then
176 --
177 -- Only execute the validation if absolutely necessary:
178 -- a) During update, the structure column value or any
179 -- of the attribute values have actually changed.
180 -- b) During insert.
181 --
182 hr_dflex_utility.ins_or_upd_descflex_attribs
183 (p_appl_short_name => 'PER'
184 ,p_descflex_name => 'IRC_SEARCH_CRITERIA_DDF'
185 ,p_attribute_category => p_rec.isc_information_category
186 ,p_attribute1_name => 'ISC_INFORMATION1'
187 ,p_attribute1_value => p_rec.isc_information1
188 ,p_attribute2_name => 'ISC_INFORMATION2'
189 ,p_attribute2_value => p_rec.isc_information2
190 ,p_attribute3_name => 'ISC_INFORMATION3'
191 ,p_attribute3_value => p_rec.isc_information3
192 ,p_attribute4_name => 'ISC_INFORMATION4'
193 ,p_attribute4_value => p_rec.isc_information4
194 ,p_attribute5_name => 'ISC_INFORMATION5'
195 ,p_attribute5_value => p_rec.isc_information5
196 ,p_attribute6_name => 'ISC_INFORMATION6'
197 ,p_attribute6_value => p_rec.isc_information6
198 ,p_attribute7_name => 'ISC_INFORMATION7'
199 ,p_attribute7_value => p_rec.isc_information7
200 ,p_attribute8_name => 'ISC_INFORMATION8'
201 ,p_attribute8_value => p_rec.isc_information8
202 ,p_attribute9_name => 'ISC_INFORMATION9'
203 ,p_attribute9_value => p_rec.isc_information9
204 ,p_attribute10_name => 'ISC_INFORMATION10'
205 ,p_attribute10_value => p_rec.isc_information10
206 ,p_attribute11_name => 'ISC_INFORMATION11'
207 ,p_attribute11_value => p_rec.isc_information11
208 ,p_attribute12_name => 'ISC_INFORMATION12'
209 ,p_attribute12_value => p_rec.isc_information12
210 ,p_attribute13_name => 'ISC_INFORMATION13'
211 ,p_attribute13_value => p_rec.isc_information13
212 ,p_attribute14_name => 'ISC_INFORMATION14'
213 ,p_attribute14_value => p_rec.isc_information14
214 ,p_attribute15_name => 'ISC_INFORMATION15'
215 ,p_attribute15_value => p_rec.isc_information15
216 ,p_attribute16_name => 'ISC_INFORMATION16'
217 ,p_attribute16_value => p_rec.isc_information16
218 ,p_attribute17_name => 'ISC_INFORMATION17'
219 ,p_attribute17_value => p_rec.isc_information17
220 ,p_attribute18_name => 'ISC_INFORMATION18'
221 ,p_attribute18_value => p_rec.isc_information18
222 ,p_attribute19_name => 'ISC_INFORMATION19'
223 ,p_attribute19_value => p_rec.isc_information19
224 ,p_attribute20_name => 'ISC_INFORMATION20'
225 ,p_attribute20_value => p_rec.isc_information20
226 ,p_attribute21_name => 'ISC_INFORMATION21'
227 ,p_attribute21_value => p_rec.isc_information21
228 ,p_attribute22_name => 'ISC_INFORMATION22'
229 ,p_attribute22_value => p_rec.isc_information22
230 ,p_attribute23_name => 'ISC_INFORMATION23'
231 ,p_attribute23_value => p_rec.isc_information23
232 ,p_attribute24_name => 'ISC_INFORMATION24'
233 ,p_attribute24_value => p_rec.isc_information24
234 ,p_attribute25_name => 'ISC_INFORMATION25'
235 ,p_attribute25_value => p_rec.isc_information25
236 ,p_attribute26_name => 'ISC_INFORMATION26'
237 ,p_attribute26_value => p_rec.isc_information26
238 ,p_attribute27_name => 'ISC_INFORMATION27'
239 ,p_attribute27_value => p_rec.isc_information27
240 ,p_attribute28_name => 'ISC_INFORMATION28'
241 ,p_attribute28_value => p_rec.isc_information28
242 ,p_attribute29_name => 'ISC_INFORMATION29'
243 ,p_attribute29_value => p_rec.isc_information29
244 ,p_attribute30_name => 'ISC_INFORMATION30'
245 ,p_attribute30_value => p_rec.isc_information30
246 );
247 end if;
248 --
249 hr_utility.set_location(' Leaving:'||l_proc,20);
250 end chk_ddf;
251 --
252 -- ----------------------------------------------------------------------------
253 -- |------------------------------< chk_df >----------------------------------|
254 -- ----------------------------------------------------------------------------
255 --
256 -- Description:
257 -- Validates all the Descriptive Flexfield values.
258 --
259 -- Prerequisites:
260 -- All other columns have been validated. Must be called as the
261 -- last step from insert_validate and update_validate.
262 --
263 -- In Arguments:
264 -- p_rec
265 --
266 -- Post Success:
267 -- If the Descriptive Flexfield structure column and data values are
268 -- all valid this procedure will end normally and processing will
269 -- continue.
270 --
271 -- Post Failure:
272 -- If the Descriptive Flexfield structure column value or any of
273 -- the data values are invalid then an application error is raised as
274 -- a PL/SQL exception.
275 --
276 -- Access Status:
277 -- Internal Row Handler Use Only.
278 --
279 -- ----------------------------------------------------------------------------
280 procedure chk_df
281 (p_rec in irc_isc_shd.g_rec_type
282 ) is
283 --
284 l_proc varchar2(72) := g_package || 'chk_df';
285 --
286 begin
287 hr_utility.set_location('Entering:'||l_proc,10);
288 --
289 if ((p_rec.search_criteria_id is not null) and (
290 nvl(irc_isc_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
291 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
292 nvl(irc_isc_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
293 nvl(p_rec.attribute1, hr_api.g_varchar2) or
294 nvl(irc_isc_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
295 nvl(p_rec.attribute2, hr_api.g_varchar2) or
296 nvl(irc_isc_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
297 nvl(p_rec.attribute3, hr_api.g_varchar2) or
298 nvl(irc_isc_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
299 nvl(p_rec.attribute4, hr_api.g_varchar2) or
300 nvl(irc_isc_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
301 nvl(p_rec.attribute5, hr_api.g_varchar2) or
302 nvl(irc_isc_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
303 nvl(p_rec.attribute6, hr_api.g_varchar2) or
304 nvl(irc_isc_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
305 nvl(p_rec.attribute7, hr_api.g_varchar2) or
306 nvl(irc_isc_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
307 nvl(p_rec.attribute8, hr_api.g_varchar2) or
308 nvl(irc_isc_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
309 nvl(p_rec.attribute9, hr_api.g_varchar2) or
310 nvl(irc_isc_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
311 nvl(p_rec.attribute10, hr_api.g_varchar2) or
312 nvl(irc_isc_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
313 nvl(p_rec.attribute11, hr_api.g_varchar2) or
314 nvl(irc_isc_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
315 nvl(p_rec.attribute12, hr_api.g_varchar2) or
316 nvl(irc_isc_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
317 nvl(p_rec.attribute13, hr_api.g_varchar2) or
318 nvl(irc_isc_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
319 nvl(p_rec.attribute14, hr_api.g_varchar2) or
320 nvl(irc_isc_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
321 nvl(p_rec.attribute15, hr_api.g_varchar2) or
322 nvl(irc_isc_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
323 nvl(p_rec.attribute16, hr_api.g_varchar2) or
324 nvl(irc_isc_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
325 nvl(p_rec.attribute17, hr_api.g_varchar2) or
326 nvl(irc_isc_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
327 nvl(p_rec.attribute18, hr_api.g_varchar2) or
328 nvl(irc_isc_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
329 nvl(p_rec.attribute19, hr_api.g_varchar2) or
330 nvl(irc_isc_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
331 nvl(p_rec.attribute20, hr_api.g_varchar2) or
332 nvl(irc_isc_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
333 nvl(p_rec.attribute21, hr_api.g_varchar2) or
334 nvl(irc_isc_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
335 nvl(p_rec.attribute22, hr_api.g_varchar2) or
336 nvl(irc_isc_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
337 nvl(p_rec.attribute23, hr_api.g_varchar2) or
338 nvl(irc_isc_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
339 nvl(p_rec.attribute24, hr_api.g_varchar2) or
340 nvl(irc_isc_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
341 nvl(p_rec.attribute25, hr_api.g_varchar2) or
342 nvl(irc_isc_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
343 nvl(p_rec.attribute26, hr_api.g_varchar2) or
344 nvl(irc_isc_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
345 nvl(p_rec.attribute27, hr_api.g_varchar2) or
346 nvl(irc_isc_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
347 nvl(p_rec.attribute28, hr_api.g_varchar2) or
348 nvl(irc_isc_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
349 nvl(p_rec.attribute29, hr_api.g_varchar2) or
350 nvl(irc_isc_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
351 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
352 or (p_rec.search_criteria_id is null) then
353 --
354 -- Only execute the validation if absolutely necessary:
355 -- a) During update, the structure column value or any
356 -- of the attribute values have actually changed.
357 -- b) During insert.
358 --
359 hr_dflex_utility.ins_or_upd_descflex_attribs
360 (p_appl_short_name => 'PER'
361 ,p_descflex_name => 'IRC_SEARCH_CRITERIA'
362 ,p_attribute_category => p_rec.attribute_category
363 ,p_attribute1_name => 'ATTRIBUTE1'
364 ,p_attribute1_value => p_rec.attribute1
365 ,p_attribute2_name => 'ATTRIBUTE2'
366 ,p_attribute2_value => p_rec.attribute2
367 ,p_attribute3_name => 'ATTRIBUTE3'
368 ,p_attribute3_value => p_rec.attribute3
369 ,p_attribute4_name => 'ATTRIBUTE4'
370 ,p_attribute4_value => p_rec.attribute4
371 ,p_attribute5_name => 'ATTRIBUTE5'
372 ,p_attribute5_value => p_rec.attribute5
373 ,p_attribute6_name => 'ATTRIBUTE6'
374 ,p_attribute6_value => p_rec.attribute6
375 ,p_attribute7_name => 'ATTRIBUTE7'
376 ,p_attribute7_value => p_rec.attribute7
377 ,p_attribute8_name => 'ATTRIBUTE8'
378 ,p_attribute8_value => p_rec.attribute8
379 ,p_attribute9_name => 'ATTRIBUTE9'
380 ,p_attribute9_value => p_rec.attribute9
381 ,p_attribute10_name => 'ATTRIBUTE10'
382 ,p_attribute10_value => p_rec.attribute10
383 ,p_attribute11_name => 'ATTRIBUTE11'
384 ,p_attribute11_value => p_rec.attribute11
385 ,p_attribute12_name => 'ATTRIBUTE12'
386 ,p_attribute12_value => p_rec.attribute12
387 ,p_attribute13_name => 'ATTRIBUTE13'
388 ,p_attribute13_value => p_rec.attribute13
389 ,p_attribute14_name => 'ATTRIBUTE14'
390 ,p_attribute14_value => p_rec.attribute14
391 ,p_attribute15_name => 'ATTRIBUTE15'
392 ,p_attribute15_value => p_rec.attribute15
393 ,p_attribute16_name => 'ATTRIBUTE16'
394 ,p_attribute16_value => p_rec.attribute16
395 ,p_attribute17_name => 'ATTRIBUTE17'
396 ,p_attribute17_value => p_rec.attribute17
397 ,p_attribute18_name => 'ATTRIBUTE18'
398 ,p_attribute18_value => p_rec.attribute18
399 ,p_attribute19_name => 'ATTRIBUTE19'
400 ,p_attribute19_value => p_rec.attribute19
401 ,p_attribute20_name => 'ATTRIBUTE20'
402 ,p_attribute20_value => p_rec.attribute20
403 ,p_attribute21_name => 'ATTRIBUTE21'
404 ,p_attribute21_value => p_rec.attribute21
405 ,p_attribute22_name => 'ATTRIBUTE22'
406 ,p_attribute22_value => p_rec.attribute22
407 ,p_attribute23_name => 'ATTRIBUTE23'
408 ,p_attribute23_value => p_rec.attribute23
409 ,p_attribute24_name => 'ATTRIBUTE24'
410 ,p_attribute24_value => p_rec.attribute24
411 ,p_attribute25_name => 'ATTRIBUTE25'
412 ,p_attribute25_value => p_rec.attribute25
413 ,p_attribute26_name => 'ATTRIBUTE26'
414 ,p_attribute26_value => p_rec.attribute26
415 ,p_attribute27_name => 'ATTRIBUTE27'
416 ,p_attribute27_value => p_rec.attribute27
417 ,p_attribute28_name => 'ATTRIBUTE28'
418 ,p_attribute28_value => p_rec.attribute28
419 ,p_attribute29_name => 'ATTRIBUTE29'
420 ,p_attribute29_value => p_rec.attribute29
421 ,p_attribute30_name => 'ATTRIBUTE30'
422 ,p_attribute30_value => p_rec.attribute30
423 );
424 end if;
425 --
426 hr_utility.set_location(' Leaving:'||l_proc,20);
427 end chk_df;
428 --
429 -- ----------------------------------------------------------------------------
430 -- |-----------------------< chk_non_updateable_args >------------------------|
431 -- ----------------------------------------------------------------------------
432 -- {Start Of Comments}
433 --
434 -- Description:
435 -- This procedure is used to ensure that non updateable attributes have
436 -- not been updated. If an attribute has been updated an error is generated.
437 --
438 -- Pre Conditions:
439 -- g_old_rec has been populated with details of the values currently in
440 -- the database.
441 --
442 -- In Arguments:
443 -- p_rec has been populated with the updated values the user would like the
444 -- record set to.
445 --
446 -- Post Success:
447 -- Processing continues if all the non updateable attributes have not
448 -- changed.
449 --
450 -- Post Failure:
451 -- An application error is raised if any of the non updatable attributes
452 -- have been altered.
453 --
454 -- {End Of Comments}
455 -- ----------------------------------------------------------------------------
456 Procedure chk_non_updateable_args
457 (p_effective_date in date
458 ,p_rec in irc_isc_shd.g_rec_type
459 ) IS
460 --
461 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
462 --
463 Begin
464 --
465 -- Only proceed with the validation if a row exists for the current
466 -- record in the HR Schema.
467 --
468 IF NOT irc_isc_shd.api_updating
469 (p_search_criteria_id => p_rec.search_criteria_id
470 ,p_object_version_number => p_rec.object_version_number
471 ) THEN
472 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
473 fnd_message.set_token('PROCEDURE ', l_proc);
474 fnd_message.set_token('STEP ', '5');
475 fnd_message.raise_error;
476 END IF;
477 --
478 -- Add checks to ensure non-updateable args have
479 -- not been updated.
480 --
481 if p_rec.search_criteria_id <>
482 irc_isc_shd.g_old_rec.search_criteria_id then
483 hr_api.argument_changed_error
484 (p_api_name => l_proc
485 ,p_argument => 'SEARCH_CRITERIA_ID'
486 ,p_base_table => irc_isc_shd.g_tab_nam
487 );
488 end if;
489 --
490 --
491 if p_rec.object_id <> irc_isc_shd.g_old_rec.object_id then
492 if p_rec.object_type = 'PERSON' then
493 hr_api.argument_changed_error
494 ( p_api_name => l_proc
495 ,p_argument => 'PERSON_ID'
496 ,p_base_table => irc_isc_shd.g_tab_nam
497 );
498 elsif p_rec.object_type = 'VACANCY' then
499 hr_api.argument_changed_error
500 ( p_api_name => l_proc
501 ,p_argument => 'VACANCY_ID'
502 ,p_base_table => irc_isc_shd.g_tab_nam
503 );
504 end if;
505 end if;
506 End chk_non_updateable_args;
507 --
508 -- ----------------------------------------------------------------------------
509 -- |-----------------------------< chk_person_id >-----------------------------|
510 -- ----------------------------------------------------------------------------
511 -- {Start Of Comments}
512 --
513 -- Description:
514 -- This procedure is used to ensure that object_id exists in PER_ALL_PEOPLE_F
515 -- as 'PERSON' type when the object_type is 'PERSON'
516 --
517 -- Pre Conditions:
518 --
519 -- In Arguments:
520 -- p_person_id
521 --
522 -- Post Success:
523 -- Processing continues if object_id is valid.
524 --
525 -- Post Failure:
526 -- An application error is raised if object_id is invalid.
527 --
528 -- {End Of Comments}
529 -- ----------------------------------------------------------------------------
530 Procedure chk_person_id
531 (p_person_id in irc_search_criteria.object_id%TYPE
532 ,p_effective_date in Date
533 ) IS
534 --
535 l_proc varchar2(72) := g_package || 'chk_person_id';
536 l_person_id varchar2(1);
537 --
538 cursor csr_person_id is
539 select null
540 from per_all_people_f ppf
541 where ppf.person_id = p_person_id
542 and trunc(p_effective_date) between ppf.effective_start_date
543 and ppf.effective_end_date;
544 --
545 begin
546 hr_utility.set_location('Entering:'||l_proc,10);
547 -- Check that Person_ID is not null.
548 hr_api.mandatory_arg_error
549 (p_api_name => l_proc
550 ,p_argument => 'PERSON_ID'
551 ,p_argument_value => p_person_id
552 );
553 -- Check that Person_ID(Object_id) exists in per_all_people_f
554 hr_utility.set_location(l_proc,20);
555 open csr_person_id;
556 fetch csr_person_id into l_person_id;
557 hr_utility.set_location(l_proc,30);
558 if csr_person_id%NOTFOUND then
559 close csr_person_id;
560 fnd_message.set_name('PER','IRC_412008_BAD_PARTY_PERSON_ID');
561 fnd_message.raise_error;
562 end if;
563 close csr_person_id;
564 hr_utility.set_location(' Leaving:'||l_proc,40);
565 exception
566 when app_exception.application_exception then
567 if hr_multi_message.exception_add
568 (p_associated_column1 =>
569 'IRC_SEARCH_CRITERIA.OBJECT_ID'
570 ) then
571 hr_utility.set_location(' Leaving:'||l_proc,50);
572 raise;
573 end if;
574 hr_utility.set_location(' Leaving:'||l_proc,60);
575 end chk_person_id;
576 --
577 -- ----------------------------------------------------------------------------
578 -- |-----------------------------< chk_unique_work >--------------------------|
579 -- ----------------------------------------------------------------------------
580 -- {Start Of Comments}
581 --
582 -- Description:
583 -- This procedure is used to ensure only one set of work preferences exists
584 -- for a person
585 --
586 -- Pre Conditions:
587 --
588 -- In Arguments:
589 -- p_person_id
590 --
591 -- Post Success:
592 -- Processing continues if there is not an existing set of work choices .
593 --
594 -- Post Failure:
595 -- An application error is raised if the work choices exist.
596 --
597 -- {End Of Comments}
598 -- ----------------------------------------------------------------------------
599 Procedure chk_unique_work
600 (p_object_id in irc_search_criteria.object_id%TYPE
601 ) IS
602 --
603 l_proc varchar2(72) := g_package || 'chk_unique_work';
604 --
605 cursor csr_work_choices is
606 select 1
607 from irc_search_criteria isc
608 where isc.object_id = p_object_id
609 and isc.object_type = 'WPREF';
610 l_dummy number;
611 --
612 begin
613 hr_utility.set_location('Entering:'||l_proc,10);
614 open csr_work_choices;
615 fetch csr_work_choices into l_dummy;
616 hr_utility.set_location(l_proc,20);
617 if csr_work_choices%found then
618 close csr_work_choices;
619 fnd_message.set_name('PER','IRC_412127_TOO_MANY_WORK_CH');
620 fnd_message.raise_error;
621 else
622 close csr_work_choices;
623 end if;
624 hr_utility.set_location(' Leaving:'||l_proc,40);
625 exception
626 when app_exception.application_exception then
627 if hr_multi_message.exception_add
628 (p_associated_column1 =>
629 'IRC_SEARCH_CRITERIA.OBJECT_ID'
630 ) then
631 hr_utility.set_location(' Leaving:'||l_proc,50);
632 raise;
633 end if;
634 hr_utility.set_location(' Leaving:'||l_proc,60);
635 end chk_unique_work;
636 --
637 -- ----------------------------------------------------------------------------
638 -- |-----------------------------< chk_search_name >--------------------------|
639 -- ----------------------------------------------------------------------------
640 -- {Start Of Comments}
641 --
642 -- Description:
643 -- This procedure ensures that search_name is unique for a person_id
644 --
645 -- Pre Conditions:
646 -- g_old_rec has been populated with details of the values currently in
647 -- the database.
648 --
649 -- In Arguments:
650 -- p_search_name
651 -- p_person_id
652 -- p_search_criteria_id
653 -- p_object_version_number
654 --
655 -- Post Success:
656 -- Processing continues if search_name is valid.
657 --
658 -- Post Failure:
659 -- An application error is raised search_name is invalid.
660 --
661 -- {End Of Comments}
662 -- ----------------------------------------------------------------------------
663 Procedure chk_search_name
664 (p_search_name in irc_search_criteria.search_name%TYPE
665 ,p_person_id in irc_search_criteria.object_id%TYPE
666 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
667 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
668 ) IS
669 --
670 l_proc varchar2(72) := g_package || 'chk_search_name';
671 l_search_name varchar2(1);
672 l_api_updating boolean;
673 --
674 cursor csr_search_name is
675 select null from irc_search_criteria isc
676 where isc.search_name = p_search_name
677 and isc.object_id = p_person_id ;
678 --
679 begin
680 hr_utility.set_location('Entering:'||l_proc,10);
681 if p_search_name is not null then
682 --
683 hr_utility.set_location(l_proc,20);
684 l_api_updating := irc_isc_shd.api_updating
685 (p_search_criteria_id => p_search_criteria_id
686 ,p_object_version_number => p_object_version_number
687 );
688 hr_utility.set_location(l_proc,30);
689 if (l_api_updating and
690 p_search_name <>
691 NVL(irc_isc_shd.g_old_rec.search_name,hr_api.g_varchar2)
692 ) or (NOT l_api_updating) then
693 -- Check that search_name is unique for a person_id
694 hr_utility.set_location(l_proc,40);
695 open csr_search_name;
696 fetch csr_search_name into l_search_name;
697 hr_utility.set_location(l_proc,50);
698 if csr_search_name%FOUND then
699 close csr_search_name;
700 fnd_message.set_name('PER','IRC_412009_SEARCH_NAME_EXISTS');
701 fnd_message.raise_error;
702 end if;
703 close csr_search_name;
704 end if;
705 end if;
706 hr_utility.set_location(' Leaving:'||l_proc,60);
707 exception
708 when app_exception.application_exception then
709 if hr_multi_message.exception_add
710 (p_associated_column1 =>
711 'IRC_SEARCH_CRITERIA.SEARCH_NAME'
712 ) then
713 hr_utility.set_location(' Leaving:'||l_proc,70);
714 raise;
715 end if;
716 hr_utility.set_location(' Leaving:'||l_proc,80);
717 end chk_search_name;
718 --
719 -- ----------------------------------------------------------------------------
720 -- |-------------------------< chk_distance_to_location >----------------------|
721 -- ----------------------------------------------------------------------------
722 -- {Start Of Comments}
723 --
724 -- Description:
725 -- This procedure ensures that distance to location is not less then zero.
726 -- Checks that location is not null if distance to location is a valid
727 -- positive number.
728 --
729 -- Pre Conditions:
730 -- g_old_rec has been populated with details of the values currently in
731 -- the database.
732 --
733 -- In Arguments:
734 -- p_distance_to_location
735 -- p_location
736 -- p_search_criteria_id
737 -- p_object_version_number
738 --
739 -- Post Success:
740 -- Processing continues if distance_to_location and location are valid.
741 --
742 -- Post Failure:
743 -- An application error is raised if either distance_to_location or location
744 -- is invalid.
745 --
746 -- {End Of Comments}
747 -- ----------------------------------------------------------------------------
748 Procedure chk_distance_to_location
749 (p_distance_to_location in irc_search_criteria.distance_to_location%TYPE
750 ,p_geocode_location in irc_search_criteria.geocode_location%TYPE
751 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
752 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
753 ) IS
754 --
755 l_proc varchar2(72) := g_package || 'chk_distance_to_location';
756 l_api_updating boolean;
757 --
758 begin
759 hr_utility.set_location('Entering:'||l_proc,10);
760 if p_distance_to_location is not null then
761 --
762 hr_utility.set_location(l_proc,20);
763 l_api_updating := irc_isc_shd.api_updating
764 (p_search_criteria_id => p_search_criteria_id
765 ,p_object_version_number => p_object_version_number
766 );
767 --
768 hr_utility.set_location(l_proc,30);
769 if (l_api_updating and
770 p_distance_to_location <>
771 NVL(irc_isc_shd.g_old_rec.distance_to_location,hr_api.g_number))
772 or (NOT l_api_updating) then
773 -- Check that distance_to_location is a valid positive number.
774 hr_utility.set_location(l_proc,40);
775 if p_distance_to_location < 0 then
776 fnd_message.set_name('PER','IRC_412010_BAD_DISTANCE_TO_LOC');
777 hr_multi_message.add
778 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.DISTANCE_TO_LOCATION'
779 );
780 else
781 -- Check that location is a valid.
782 hr_utility.set_location(l_proc,50);
783 if p_geocode_location is null then
784 fnd_message.set_name('PER','IRC_412011_BAD_LOCATION');
785 hr_multi_message.add
786 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.GEOCODE_LOCATION'
787 );
788 end if;
789 end if;
790 end if;
791 else
792 -- the distance to location is null - check that the geocode location is too
793 hr_utility.set_location(l_proc,60);
794 if p_geocode_location is not null then
795 fnd_message.set_name('PER','IRC_412164_NO_DISTANCE');
796 hr_multi_message.add
797 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.DISTANCE_TO_LOCATION'
798 );
799 end if;
800 end if;
801 hr_utility.set_location(' Leaving:'||l_proc,60);
802 end chk_distance_to_location;
803 --
804 -- ----------------------------------------------------------------------------
805 -- |-------------------------< chk_location_id >-----------------------------|
806 -- ----------------------------------------------------------------------------
807 -- {Start Of Comments}
808 --
809 -- Description:
810 -- This procedure ensures that the location_id is valid
811 --
812 -- Pre Conditions:
813 -- g_old_rec has been populated with details of the values currently in
814 -- the database.
815 --
816 -- In Arguments:
817 -- p_location_id
818 -- p_search_criteria_id
819 -- p_object_version_number
820 --
821 -- Post Success:
822 -- Processing continues if the location_id is valid
823 --
824 -- Post Failure:
825 -- An application error is raised if the location_id is not valid
826 --
827 -- {End Of Comments}
828 -- ----------------------------------------------------------------------------
829 Procedure chk_location_id
830 (p_location_id in irc_search_criteria.location_id%TYPE
831 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
832 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
833 ) IS
834 --
835 l_proc varchar2(72) := g_package || 'chk_location_id';
836 l_api_updating boolean;
837 --
838 l_dummy number;
839 --
840 cursor chk_loc is
841 select 1 from hr_locations_all
842 where location_id=p_location_id;
843 --
844 begin
845 hr_utility.set_location('Entering:'||l_proc,10);
846 if p_location_id is not null then
847 --
848 hr_utility.set_location(l_proc,20);
849 l_api_updating := irc_isc_shd.api_updating
850 (p_search_criteria_id => p_search_criteria_id
851 ,p_object_version_number => p_object_version_number
852 );
853 --
854 hr_utility.set_location(l_proc,30);
855 if (l_api_updating and
856 p_location_id <>
857 NVL(irc_isc_shd.g_old_rec.location_id,hr_api.g_number))
858 or (NOT l_api_updating) then
859 -- Check that location_id is a valid
860 hr_utility.set_location(l_proc,40);
861 open chk_loc;
862 fetch chk_loc into l_dummy;
863 if chk_loc%notfound then
864 close chk_loc;
865 fnd_message.set_name('PER','IRC_412165_BAD_LOCATION_ID');
866 hr_multi_message.add
867 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.LOCATION_ID'
868 );
869 else
870 close chk_loc;
871 end if;
872 end if;
873 end if;
874 hr_utility.set_location(' Leaving:'||l_proc,60);
875 end chk_location_id;
876 --
877 -- ----------------------------------------------------------------------------
878 -- |-------------------------< chk_longitude_latitude >-----------------------|
879 -- ----------------------------------------------------------------------------
880 -- {Start Of Comments}
881 --
882 -- Description:
883 -- This procedure ensures that either both the longitude and latitude are
884 -- specified, or neither is specified
885 --
886 -- Pre Conditions:
887 -- g_old_rec has been populated with details of the values currently in
888 -- the database.
889 --
890 -- In Arguments:
891 -- p_longitude
892 -- p_latitude
893 -- p_search_criteria_id
894 -- p_object_version_number
895 --
896 -- Post Success:
897 -- Processing continues if the data is correct
898 --
899 -- Post Failure:
900 -- An application error is raised if only one of longitude or latitude are
901 -- specified
902 --
903 -- {End Of Comments}
904 -- ----------------------------------------------------------------------------
905 Procedure chk_longitude_latitude
906 (p_longitude in number
907 ,p_latitude in number
908 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
909 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
910 ) IS
911 --
912 l_proc varchar2(72) := g_package || 'chk_longitude_latitude';
913 --
914 begin
915 hr_utility.set_location('Entering:'||l_proc,10);
916 if (p_longitude is not null and p_latitude is null)
917 or (p_longitude is null and p_latitude is not null) then
918 --
919 hr_utility.set_location(l_proc,20);
920 fnd_message.set_name('PER','IRC_412166_LONG_LAT');
921 hr_multi_message.add
922 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.GEOMETRY'
923 );
924 end if;
925 hr_utility.set_location(' Leaving:'||l_proc,60);
926 end chk_longitude_latitude;
927 --
928 -- ----------------------------------------------------------------------------
929 -- |----------------------------< chk_use_for_matching >----------------------|
930 -- ----------------------------------------------------------------------------
931 -- {Start Of Comments}
932 --
933 -- Description:
934 -- This procedure ensures that use for matching should have a value of 'Y' or
935 -- 'N'
936 --
937 -- Pre Conditions:
938 -- g_old_rec has been populated with details of the values currently in
939 -- the database.
940 --
941 -- In Arguments:
942 -- p_use_for_matching
943 -- p_search_criteria_id
944 -- p_object_version_number
945 --
946 -- Post Success:
947 -- Processing continues if use_for_matching is valid.
948 --
949 -- Post Failure:
950 -- An application error is raised use_for_matching is invalid.
951 --
952 -- {End Of Comments}
953 -- ----------------------------------------------------------------------------
954 Procedure chk_use_for_matching
955 (p_use_for_matching in irc_search_criteria.use_for_matching%TYPE
956 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
957 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
958 ) IS
959 --
960 l_proc varchar2(72) := g_package || 'chk_use_for_matching';
961 l_api_updating boolean;
962 --
963 begin
964 hr_utility.set_location('Entering:'||l_proc,10);
965 if p_use_for_matching is not null then
966 --
967 hr_utility.set_location(l_proc,20);
968 l_api_updating := irc_isc_shd.api_updating
969 (p_search_criteria_id => p_search_criteria_id
970 ,p_object_version_number => p_object_version_number
971 );
972 --
973 hr_utility.set_location(l_proc,30);
974 if (l_api_updating and
975 p_use_for_matching <>
976 NVL(irc_isc_shd.g_old_rec.use_for_matching,hr_api.g_varchar2)
977 ) or (NOT l_api_updating) then
978 -- Check that use_for_matching is having a valid value of 'Y' or 'N'
979 hr_utility.set_location(l_proc,40);
980 if not p_use_for_matching in ('Y','N') then
981 fnd_message.set_name('PER','IRC_412012_BAD_USE_FOR_MATCHIN');
982 fnd_message.raise_error;
983 end if;
984 end if;
985 end if;
986 hr_utility.set_location(' Leaving:'||l_proc,50);
987 exception
988 when app_exception.application_exception then
989 if hr_multi_message.exception_add
990 (p_associated_column1 =>
991 'IRC_SEARCH_CRITERIA.USE_FOR_MATCHING'
992 ) then
993 hr_utility.set_location(' Leaving:'||l_proc,60);
994 raise;
995 end if;
996 hr_utility.set_location(' Leaving:'||l_proc,70);
997 end chk_use_for_matching;
998 --
999 -- ----------------------------------------------------------------------------
1000 -- |----------------------------< chk_match_competence >----------------------|
1001 -- ----------------------------------------------------------------------------
1002 -- {Start Of Comments}
1003 --
1004 -- Description:
1005 -- This procedure ensures that use for matching should have a value of 'Y' or
1006 -- 'N'
1007 --
1008 -- Pre Conditions:
1009 -- g_old_rec has been populated with details of the values currently in
1010 -- the database.
1011 --
1012 -- In Arguments:
1013 -- p_match_competence
1014 -- p_search_criteria_id
1015 -- p_object_version_number
1016 --
1017 -- Post Success:
1018 -- Processing continues if match_competence is valid.
1019 --
1020 -- Post Failure:
1021 -- An application error is raised match_competence is invalid.
1022 --
1023 -- {End Of Comments}
1024 -- ----------------------------------------------------------------------------
1025 Procedure chk_match_competence
1026 (p_match_competence in irc_search_criteria.match_competence%TYPE
1027 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1028 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1029 ) IS
1030 --
1031 l_proc varchar2(72) := g_package || 'chk_match_competence';
1032 l_api_updating boolean;
1033 --
1034 begin
1035 hr_utility.set_location('Entering:'||l_proc,10);
1036 if p_match_competence is not null then
1037 hr_utility.set_location(l_proc,20);
1038 l_api_updating := irc_isc_shd.api_updating
1039 (p_search_criteria_id => p_search_criteria_id
1040 ,p_object_version_number => p_object_version_number
1041 );
1042 --
1043 hr_utility.set_location(l_proc,30);
1044 if (l_api_updating and
1045 p_match_competence <>
1046 NVL(irc_isc_shd.g_old_rec.match_competence,hr_api.g_varchar2)
1047 ) or (NOT l_api_updating) then
1048 -- Check that match_competence is having a valid value of 'Y' or 'N'
1049 hr_utility.set_location(l_proc,40);
1050 if not p_match_competence in ('Y','N') then
1051 fnd_message.set_name('PER','IRC_412013_BAD_MATCH_COMPETENC');
1052 fnd_message.raise_error;
1053 end if;
1054 end if;
1055 end if;
1056 hr_utility.set_location(' Leaving:'||l_proc,50);
1057 exception
1058 when app_exception.application_exception then
1059 if hr_multi_message.exception_add
1060 (p_associated_column1 =>
1061 'IRC_SEARCH_CRITERIA.MATCH_COMPETENCE'
1062 ) then
1063 hr_utility.set_location(' Leaving:'||l_proc,60);
1064 raise;
1065 end if;
1066 hr_utility.set_location(' Leaving:'||l_proc,70);
1067 end chk_match_competence;
1068 --
1069 -- ----------------------------------------------------------------------------
1070 -- |--------------------------< chk_match_qualification >----------------------|
1071 -- ----------------------------------------------------------------------------
1072 -- {Start Of Comments}
1073 --
1074 -- Description:
1075 -- This procedure ensures that match qualification should have a value of 'Y'
1076 -- or 'N'
1077 --
1078 -- Pre Conditions:
1079 -- g_old_rec has been populated with details of the values currently in
1080 -- the database.
1081 --
1082 -- In Arguments:
1083 -- p_match_qualification
1084 -- p_search_criteria_id
1085 -- p_object_version_number
1086 --
1087 -- Post Success:
1088 -- Processing continues if match_qualification is valid.
1089 --
1090 -- Post Failure:
1091 -- An application error is raised match_qualification is invalid.
1092 --
1093 -- {End Of Comments}
1094 -- ----------------------------------------------------------------------------
1095 Procedure chk_match_qualification
1096 (p_match_qualification in irc_search_criteria.match_qualification%TYPE
1097 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1098 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1099 ) IS
1100 --
1101 l_proc varchar2(72) := g_package || 'chk_match_qualification';
1102 l_api_updating boolean;
1103 --
1104 begin
1105 hr_utility.set_location('Entering:'||l_proc,10);
1106 if p_match_qualification is not null then
1107 --
1108 hr_utility.set_location(l_proc,20);
1109 l_api_updating := irc_isc_shd.api_updating
1110 (p_search_criteria_id => p_search_criteria_id
1111 ,p_object_version_number => p_object_version_number
1112 );
1113 --
1114 hr_utility.set_location(l_proc,30);
1115 if (l_api_updating and
1116 p_match_qualification <>
1117 NVL(irc_isc_shd.g_old_rec.match_qualification,hr_api.g_varchar2)
1118 ) or (NOT l_api_updating) then
1119 -- Check that match_qualification is having a valid value of 'Y' or 'N'
1120 hr_utility.set_location(l_proc,40);
1121 if not p_match_qualification in ('Y','N') then
1122 fnd_message.set_name('PER','IRC_412014_BAD_MATCH_QUALIFICA');
1123 fnd_message.raise_error;
1124 end if;
1125 end if;
1126 end if;
1127 hr_utility.set_location(' Leaving:'||l_proc,50);
1128 exception
1129 when app_exception.application_exception then
1130 if hr_multi_message.exception_add
1131 (p_associated_column1 =>
1132 'IRC_SEARCH_CRITERIA.MATCH_QUALIFICATION'
1133 ) then
1134 hr_utility.set_location(' Leaving:'||l_proc,60);
1135 raise;
1136 end if;
1137 hr_utility.set_location(' Leaving:'||l_proc,70);
1138 end chk_match_qualification;
1139 --
1140 -- ----------------------------------------------------------------------------
1141 -- |------------------------------< chk_vacancy_id >---------------------------|
1142 -- ----------------------------------------------------------------------------
1143 -- {Start Of Comments}
1144 --
1145 -- Description:
1146 -- This procedure ensures that vacancy_id exists in per_all_vacancies.
1147 --
1148 -- Pre Conditions:
1149 -- g_old_rec has been populated with details of the values currently in
1150 -- the database.
1151 --
1152 -- In Arguments:
1153 -- p_vacancy_id
1154 -- p_effective_date
1155 --
1156 -- Post Success:
1157 -- Processing continues if vacancy_id is valid.
1158 --
1159 -- Post Failure:
1160 -- An application error is raised if vacancy_id is invalid.
1161 --
1162 -- {End Of Comments}
1163 -- ----------------------------------------------------------------------------
1164 Procedure chk_vacancy_id
1165 (p_vacancy_id in irc_search_criteria.object_id%TYPE
1166 ,p_effective_date in date
1167 ) IS
1168 --
1169 l_proc varchar2(72) := g_package || 'chk_vacancy_id';
1170 l_vacancy_id varchar2(1);
1171 --
1172 cursor csr_vacancy_id is
1173 select null from per_all_vacancies pav
1174 where pav.vacancy_id = p_vacancy_id;
1175 --
1176 begin
1177 hr_utility.set_location('Entering:'||l_proc,10);
1178 -- Check that Vacancy_id is not null.
1179 hr_api.mandatory_arg_error
1180 (p_api_name => l_proc
1181 ,p_argument => 'VACANCY_ID'
1182 ,p_argument_value => p_vacancy_id
1183 );
1184 -- Check that vacancy_id exists in per_all_vacancies.
1185 hr_utility.set_location(l_proc,20);
1186 open csr_vacancy_id;
1187 fetch csr_vacancy_id into l_vacancy_id;
1188 hr_utility.set_location(l_proc,30);
1189 if csr_vacancy_id%NOTFOUND then
1190 close csr_vacancy_id;
1191 fnd_message.set_name('PER','IRC_412015_BAD_VACANCY_ID');
1192 fnd_message.raise_error;
1193 end if;
1194 close csr_vacancy_id;
1195 hr_utility.set_location(' Leaving:'||l_proc,40);
1196 exception
1197 when app_exception.application_exception then
1198 if hr_multi_message.exception_add
1199 (p_associated_column1 =>
1200 'IRC_SEARCH_CRITERIA.OBJECT_ID'
1201 ) then
1202 hr_utility.set_location(' Leaving:'||l_proc,50);
1203 raise;
1204 end if;
1205 hr_utility.set_location(' Leaving:'||l_proc,60);
1206 end chk_vacancy_id;
1207 --
1208 -- ----------------------------------------------------------------------------
1209 -- |----------------------------< chk_min_qual_level >-------------------------|
1210 -- ----------------------------------------------------------------------------
1211 -- {Start Of Comments}
1212 --
1213 -- Description:
1214 -- This procedure ensures that min_qual_level exists in
1215 -- per_qualification_types.
1216 --
1217 -- Pre Conditions:
1218 -- g_old_rec has been populated with details of the values currently in
1219 -- the database.
1220 --
1221 -- In Arguments:
1222 -- p_min_qual_level
1223 -- p_search_criteria_id
1224 -- p_object_version_number
1225 --
1226 -- Post Success:
1227 -- Processing continues if min_qual_level is valid.
1228 --
1229 -- Post Failure:
1230 -- An application error is raised if min_qual_level is invalid.
1231 --
1232 -- {End Of Comments}
1233 -- ----------------------------------------------------------------------------
1234 Procedure chk_min_qual_level
1235 (p_min_qual_level in irc_search_criteria.min_qual_level%TYPE
1236 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1237 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1238 ) IS
1239 --
1240 l_proc varchar2(72) := g_package || 'chk_min_qual_level';
1241 l_min_qual_level varchar2(1);
1242 l_api_updating boolean;
1243 --
1244 cursor csr_min_qual_level is
1245 select null from per_qualification_types pqt
1246 where pqt.qualification_type_id = p_min_qual_level;
1247 --
1248 begin
1249 hr_utility.set_location('Entering:'||l_proc,10);
1250 if p_min_qual_level is not null then
1251 --
1252 hr_utility.set_location(l_proc,20);
1253 l_api_updating := irc_isc_shd.api_updating
1254 (p_search_criteria_id => p_search_criteria_id
1255 ,p_object_version_number => p_object_version_number
1256 );
1257 --
1258 hr_utility.set_location(l_proc,30);
1259 if (l_api_updating and
1260 p_min_qual_level <>
1261 NVL(irc_isc_shd.g_old_rec.min_qual_level,hr_api.g_number)
1262 ) or (NOT l_api_updating) then
1263 hr_utility.set_location(l_proc,40);
1264 open csr_min_qual_level;
1265 fetch csr_min_qual_level into l_min_qual_level ;
1266 hr_utility.set_location(l_proc,50);
1267 -- Check that min_qual_level exists in per_qualification_types
1268 if csr_min_qual_level%NOTFOUND then
1269 close csr_min_qual_level;
1270 fnd_message.set_name('PER','IRC_412016_BAD_MIN_QUAL_LEVEL');
1271 fnd_message.raise_error;
1272 end if;
1273 close csr_min_qual_level;
1274 end if;
1275 end if;
1276 hr_utility.set_location(' Leaving:'||l_proc,60);
1277 exception
1278 when app_exception.application_exception then
1279 if hr_multi_message.exception_add
1280 (p_associated_column1 =>
1281 'IRC_SEARCH_CRITERIA.MIN_QUAL_LEVEL'
1282 ) then
1283 hr_utility.set_location(' Leaving:'||l_proc,70);
1284 raise;
1285 end if;
1286 hr_utility.set_location(' Leaving:'||l_proc,80);
1287 end chk_min_qual_level;
1288 --
1289 -- ----------------------------------------------------------------------------
1290 -- |----------------------------< chk_qual_rank >-----------------------------|
1291 -- ----------------------------------------------------------------------------
1292 -- {Start Of Comments}
1293 --
1294 -- Description:
1295 -- This procedure ensures that max_qual_level is greater than min_qual_level
1296 -- based on values of rank in PER_QUALIFICATION_TYPES.
1297 --
1298 -- Pre Conditions:
1299 --
1300 -- In Arguments:
1301 -- p_min_qual_level
1302 -- p_max_qual_level
1303 --
1304 -- Post Success:
1305 -- Processing continues if max_qual_level is greater than min_qual_level.
1306 --
1307 -- Post Failure:
1308 -- An application error is raised if min_qual_level is greater than
1309 -- max_qual_level.
1310 --
1311 -- {End Of Comments}
1312 -- ----------------------------------------------------------------------------
1313 Procedure chk_qual_rank
1314 ( p_min_qual_level in irc_search_criteria.min_qual_level%TYPE
1315 , p_max_qual_level in irc_search_criteria.max_qual_level%TYPE
1316 ) IS
1317 --
1318 l_proc varchar2(72):= g_package|| 'chk_qual_rank';
1319 l_min_qual_rank per_qualification_types.rank%TYPE;
1320 l_max_qual_rank per_qualification_types.rank%TYPE;
1321 --
1322 cursor csr_qual_rank(c_qual_type_id per_qualification_types.qualification_type_id%TYPE) is
1323 select rank from per_qualification_types where qualification_type_id = c_qual_type_id;
1324 --
1325 begin
1326 hr_utility.set_location('Entering:'||l_proc,10);
1327 -- Only proceed with rank validation when multiple message list does not
1328 -- already contain an error associated with the min_qual_level or max_qual_level
1329 -- columns.
1330 hr_utility.set_location(l_proc,15);
1331 if hr_multi_message.no_all_inclusive_error
1332 ( p_check_column1 => 'IRC_SEARCH_CRITERIA.MIN_QUAL_LEVEL'
1333 ,p_check_column2 => 'IRC_SEARCH_CRITERIA.MAX_QUAL_LEVEL'
1334 ) then
1335 --
1336 hr_utility.set_location(l_proc,20);
1337 if ( p_min_qual_level is not null) AND (p_max_qual_level is not null) then
1338 hr_utility.set_location(l_proc,25);
1339 open csr_qual_rank(p_min_qual_level);
1340 fetch csr_qual_rank into l_min_qual_rank;
1341 close csr_qual_rank;
1342 open csr_qual_rank(p_max_qual_level);
1343 fetch csr_qual_rank into l_max_qual_rank;
1344 close csr_qual_rank;
1345 end if;
1346 if (l_min_qual_rank > l_max_qual_rank)then
1347 hr_utility.set_location(l_proc,30);
1348 fnd_message.set_name('PER', 'IRC_412139_MIN_MAX_QUAL_RANK');
1349 fnd_message.raise_error;
1350 end if;
1351 end if;
1352 hr_utility.set_location('Leaving:'||l_proc,40);
1353 exception
1354 when app_exception.application_exception then
1355 if hr_multi_message.exception_add
1356 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.MIN_QUAL_LEVEL'
1357 ,p_associated_column2 => 'IRC_SEARCH_CRITERIA.MAX_QUAL_LEVEL'
1358 ) then
1359 hr_utility.set_location(' Leaving:'||l_proc,50);
1360 raise;
1361 end if;
1362 hr_utility.set_location(' Leaving:'||l_proc,60);
1363 end chk_qual_rank;
1364 --
1365 -- ----------------------------------------------------------------------------
1366 -- |------------------------------< chk_min_salary >---------------------------|
1367 -- ----------------------------------------------------------------------------
1368 -- {Start Of Comments}
1369 --
1370 -- Description:
1371 -- This procedure ensures that min_salary is a positive number.
1372 -- Also checks that salary_currency is not null when min_salary is a valid
1373 -- positive number.
1374 --
1375 -- Pre Conditions:
1376 -- g_old_rec has been populated with details of the values currently in
1377 -- the database.
1378 --
1379 -- In Arguments:
1380 -- min_salary
1381 -- p_search_criteria_id
1382 -- p_object_version_number
1383 --
1384 -- Post Success:
1385 -- Processing continues if both min_salary and salary_currency are valid.
1386 --
1387 -- Post Failure:
1388 -- An application error is raised if either min_salary or salary_currency is
1389 -- invalid.
1390 --
1391 -- {End Of Comments}
1392 -- ----------------------------------------------------------------------------
1393 Procedure chk_min_salary
1394 (p_min_salary in irc_search_criteria.min_salary%TYPE
1395 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1396 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1397 ) IS
1398 --
1399 l_proc varchar2(72) := g_package || 'chk_min_salary';
1400 l_min_salary varchar2(1);
1401 l_api_updating boolean;
1402 --
1403 begin
1404 hr_utility.set_location('Entering:'||l_proc,10);
1405 if p_min_salary is not null then
1406 --
1407 hr_utility.set_location(l_proc,20);
1408 l_api_updating := irc_isc_shd.api_updating
1409 (p_search_criteria_id => p_search_criteria_id
1410 ,p_object_version_number => p_object_version_number
1411 );
1412 --
1413 hr_utility.set_location(l_proc,30);
1414 if (l_api_updating and
1415 p_min_salary <>
1416 NVL(irc_isc_shd.g_old_rec.min_salary,hr_api.g_number)
1417 ) or (NOT l_api_updating) then
1418 -- Check that min_salary is a valid positive number.
1419 hr_utility.set_location(l_proc,40);
1420 if p_min_salary < 0 then
1421 fnd_message.set_name('PER','IRC_412017_BAD_MIN_SALARY');
1422 fnd_message.raise_error;
1423 end if;
1424 end if;
1425 end if;
1426 hr_utility.set_location(' Leaving:'||l_proc,50);
1427 exception
1428 when app_exception.application_exception then
1429 if hr_multi_message.exception_add
1430 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.MIN_SALARY'
1431 ) then
1432 hr_utility.set_location(' Leaving:'||l_proc,60);
1433 raise;
1434 end if;
1435 hr_utility.set_location(' Leaving:'||l_proc,70);
1436 end chk_min_salary;
1437 --
1438 -- ----------------------------------------------------------------------------
1439 -- |------------------------------< chk_max_salary >---------------------------|
1440 -- ----------------------------------------------------------------------------
1441 -- {Start Of Comments}
1442 --
1443 -- Description:
1444 -- This procedure ensures that max_salary is a positive number and is greater
1445 -- then min_salary.
1446 -- Also checks that salary_currency is not null when max_salary is a valid
1447 -- positive number.
1448 --
1449 -- Pre Conditions:
1450 -- g_old_rec has been populated with details of the values currently in
1451 -- the database.
1452 --
1453 -- In Arguments:
1454 -- max_salary
1455 -- min_salary
1456 -- p_search_criteria_id
1457 -- p_object_version_number
1458 --
1459 -- Post Success:
1460 -- Processing continues if max_salary and salary_currency are valid.
1461 --
1462 -- Post Failure:
1463 -- An application error is raised if either max_salary or salary_currency is
1464 -- invalid.
1465 --
1466 -- {End Of Comments}
1467 -- ----------------------------------------------------------------------------
1468 Procedure chk_max_salary
1469 (p_max_salary in irc_search_criteria.max_salary%TYPE
1470 ,p_min_salary in irc_search_criteria.min_salary%TYPE
1471 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1472 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1473 ) IS
1474 --
1475 l_proc varchar2(72) := g_package || 'chk_max_salary';
1476 l_min_salary varchar2(1);
1477 l_api_updating boolean;
1478 --
1479 begin
1480 hr_utility.set_location('Entering:'||l_proc,10);
1481 --
1482 -- Only proceed with max_salary validation when multiple message list does not
1483 -- already contain an error associated with the min_salary columns.
1484 hr_utility.set_location(l_proc,15);
1485 if hr_multi_message.no_exclusive_error(
1486 p_check_column1 => 'IRC_SEARCH_CRITERIA.MIN_SALARY'
1487 ) then
1488 if p_max_salary is not null then
1489 --
1490 hr_utility.set_location(l_proc,20);
1491 l_api_updating := irc_isc_shd.api_updating
1492 (p_search_criteria_id => p_search_criteria_id
1493 ,p_object_version_number => p_object_version_number
1494 );
1495 --
1496 hr_utility.set_location(l_proc,30);
1497 if (l_api_updating and
1498 p_max_salary <>
1499 NVL(irc_isc_shd.g_old_rec.max_salary,hr_api.g_number)
1500 ) or (NOT l_api_updating) then
1501 -- Check that max_salary is greater then min salary.
1502 hr_utility.set_location(l_proc,40);
1503 if p_max_salary < 0 or p_max_salary < NVL(p_min_salary,hr_api.g_number)
1504 then
1505 fnd_message.set_name('PER','IRC_412019_MAX_BELOW_MIN_SAL');
1506 fnd_message.raise_error;
1507 end if;
1508 end if;
1509 end if;
1510 end if;
1511 --
1512 hr_utility.set_location(' Leaving:'||l_proc,50);
1513 exception
1514 -- When Multiple error detection is enabled handle the application errors
1515 -- which have been raised by this procedure. Transfer the error to the
1516 -- Multiple message list and associate the error with max_salary and
1517 -- min_salary
1518 when app_exception.application_exception then
1519 if hr_multi_message.exception_add
1520 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.MIN_SALARY'
1521 ,p_associated_column2 => 'IRC_SEARCH_CRITERIA.MAX_SALARY'
1522 ) then
1523 hr_utility.set_location(' Leaving:'||l_proc,60);
1524 raise;
1525 end if;
1526 hr_utility.set_location(' Leaving:'||l_proc,70);
1527 end chk_max_salary;
1528 --
1529 -- ----------------------------------------------------------------------------
1530 -- |-----------------------------< chk_employee >-----------------------------|
1531 -- ----------------------------------------------------------------------------
1532 -- {Start Of Comments}
1533 --
1534 -- Description:
1535 -- This procedure is used to ensure that employee has a valid value of 'Y' or
1536 -- 'N'.
1537 --
1538 -- Pre Conditions:
1539 -- g_old_rec has been populated with details of the values currently in
1540 -- the database.
1541 --
1542 -- In Arguments:
1543 -- p_employee
1544 -- p_search_criteria_id
1545 -- p_object_version_number
1546 --
1547 -- Post Success:
1548 -- Processing continues if employee value is valid.
1549 --
1550 -- Post Failure:
1551 -- An application error is raised if employee value is invalid.
1552 --
1553 -- {End Of Comments}
1554 -- ----------------------------------------------------------------------------
1555 Procedure chk_employee
1556 (p_employee in irc_search_criteria.employee%TYPE
1557 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1558 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1559 ) IS
1560 --
1561 l_proc varchar2(72) := g_package || 'chk_employee';
1562 l_api_updating boolean;
1563 --
1564 begin
1565 hr_utility.set_location('Entering:'||l_proc,10);
1566 l_api_updating := irc_isc_shd.api_updating
1567 (p_search_criteria_id => p_search_criteria_id
1568 ,p_object_version_number => p_object_version_number
1569 );
1570 --
1571 hr_utility.set_location(l_proc,20);
1572 if (l_api_updating and p_employee <> irc_isc_shd.g_old_rec.employee)
1573 or (NOT l_api_updating) then
1574 -- Check that employee is having a valid value of 'Y' or 'N'
1575 hr_utility.set_location(l_proc,30);
1576 if (p_employee is null) or (p_employee Not in ('Y','N')) then
1577 fnd_message.set_name('PER','IRC_412020_BAD_EMPLOYEE');
1578 fnd_message.raise_error;
1579 end if;
1580 end if;
1581 hr_utility.set_location(' Leaving:'||l_proc,40);
1582 exception
1583 when app_exception.application_exception then
1584 if hr_multi_message.exception_add
1585 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.EMPLOYEE'
1586 ) then
1587 hr_utility.set_location(' Leaving:'||l_proc,50);
1588 raise;
1589 end if;
1590 hr_utility.set_location(' Leaving:'||l_proc,60);
1591 end chk_employee;
1592 -- ----------------------------------------------------------------------------
1593 -- |-----------------------------< chk_contractor >---------------------------|
1594 -- ----------------------------------------------------------------------------
1595 -- {Start Of Comments}
1596 --
1597 -- Description:
1598 -- This procedure is used to ensure that contractor has a valid value of 'Y'
1599 -- or 'N'.
1600 --
1601 -- Pre Conditions:
1602 -- g_old_rec has been populated with details of the values currently in
1603 -- the database.
1604 --
1605 -- In Arguments:
1606 -- p_contractor
1607 -- p_search_criteria_id
1608 -- p_object_version_number
1609 --
1610 -- Post Success:
1611 -- Processing continues if contractor value is valid.
1612 --
1613 -- Post Failure:
1614 -- An application error is raised if contractor value is invalid.
1615 --
1616 -- {End Of Comments}
1617 -- ----------------------------------------------------------------------------
1618 Procedure chk_contractor
1619 (p_contractor in irc_search_criteria.contractor%TYPE
1620 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1621 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1622 ) IS
1623 --
1624 l_proc varchar2(72) := g_package || 'chk_contractor';
1625 l_api_updating boolean;
1626 --
1627 begin
1628 hr_utility.set_location('Entering:'||l_proc,10);
1629 l_api_updating := irc_isc_shd.api_updating
1630 (p_search_criteria_id => p_search_criteria_id
1631 ,p_object_version_number => p_object_version_number
1632 );
1633 hr_utility.set_location(l_proc,20);
1634 if (l_api_updating and p_contractor <> irc_isc_shd.g_old_rec.contractor)
1635 or (NOT l_api_updating) then
1636 -- Check that contractor is having a valid value of 'Y' or 'N'
1637 hr_utility.set_location(l_proc,30);
1638 if (p_contractor is null) or (p_contractor Not in ('Y','N')) then
1639 fnd_message.set_name('PER','IRC_412021_BAD_CONTRACTOR');
1640 fnd_message.raise_error;
1641 end if;
1642 end if;
1643 hr_utility.set_location(' Leaving:'||l_proc,40);
1644 exception
1645 when app_exception.application_exception then
1646 if hr_multi_message.exception_add
1647 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.CONTRACTOR'
1648 ) then
1649 hr_utility.set_location(' Leaving:'||l_proc,50);
1650 raise;
1651 end if;
1652 hr_utility.set_location(' Leaving:'||l_proc,60);
1653 end chk_contractor;
1654 -- ----------------------------------------------------------------------------
1655 -- |------------------------< chk_professional_area >-------------------------|
1656 -- ----------------------------------------------------------------------------
1657 -- {Start Of Comments}
1658 --
1659 -- Description:
1660 -- This procedure is used to ensure that professional area exists in
1661 -- hr_lookups
1662 --
1663 -- Pre Conditions:
1664 -- g_old_rec has been populated with details of the values currently in
1665 -- the database.
1666 --
1667 -- In Arguments:
1668 -- p_professional_area
1669 -- p_effective_date
1670 -- p_search_criteria_id
1671 -- p_object_version_number
1672 --
1673 -- Post Success:
1674 -- Processing continues if professional_area is valid.
1675 --
1676 -- Post Failure:
1677 -- An application error is raised if professional_area is invalid.
1678 --
1679 -- {End Of Comments}
1680 -- ----------------------------------------------------------------------------
1681 Procedure chk_professional_area
1682 (p_professional_area in irc_search_criteria.professional_area%TYPE
1683 ,p_effective_date in date
1684 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1685 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1686 ) IS
1687 --
1688 l_proc varchar2(72) := g_package || 'chk_professional_area';
1689 l_api_updating boolean;
1690 l_ret boolean;
1691 --
1692 begin
1693 hr_utility.set_location('Entering:'||l_proc,10);
1694 l_api_updating := irc_isc_shd.api_updating
1695 (p_search_criteria_id => p_search_criteria_id
1696 ,p_object_version_number => p_object_version_number
1697 );
1698 hr_utility.set_location(l_proc,20);
1699 if p_professional_area is not null then
1700 hr_utility.set_location(l_proc,30);
1701 if (l_api_updating and
1702 p_professional_area <>
1703 NVL(irc_isc_shd.g_old_rec.professional_area,hr_api.g_varchar2)
1704 ) or (NOT l_api_updating) then
1705 -- Check that professional_area exists in hr_lookups
1706 hr_utility.set_location(l_proc,40);
1707 l_ret := hr_api.not_exists_in_hr_lookups(
1708 p_effective_date => p_effective_date
1709 ,p_lookup_type => 'IRC_PROFESSIONAL_AREA'
1710 ,p_lookup_code => p_professional_area);
1711 if l_ret = true then
1712 fnd_message.set_name('PER','IRC_412022_BAD_PROF_AREA');
1713 fnd_message.raise_error;
1714 end if;
1715 end if;
1716 end if;
1717 hr_utility.set_location(' Leaving:'||l_proc,50);
1718 exception
1719 when app_exception.application_exception then
1720 if hr_multi_message.exception_add
1721 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.PROFESSIONAL_AREA'
1722 ) then
1723 hr_utility.set_location(' Leaving:'||l_proc,60);
1724 raise;
1725 end if;
1726 hr_utility.set_location(' Leaving:'||l_proc,70);
1727 end chk_professional_area;
1728 --
1729 -- ----------------------------------------------------------------------------
1730 -- |------------------------< chk_employment_category >------------------------|
1731 -- ----------------------------------------------------------------------------
1732 -- {Start Of Comments}
1733 --
1734 -- Description:
1735 -- This procedure is used to ensure that employment category exists in
1736 -- hr_lookups
1737 --
1738 -- Pre Conditions:
1739 -- g_old_rec has been populated with details of the values currently in
1740 -- the database.
1741 --
1742 -- In Arguments:
1743 -- p_employment_category
1744 -- p_effective_date
1745 -- p_search_criteria_id
1746 -- p_object_version_number
1747 --
1748 -- Post Success:
1749 -- Processing continues if employment category is valid.
1750 --
1751 -- Post Failure:
1752 -- An application error is raised if employment category is invalid.
1753 --
1754 -- {End Of Comments}
1755 -- ----------------------------------------------------------------------------
1756 Procedure chk_employment_category
1757 (p_employment_category in irc_search_criteria.employment_category%TYPE
1758 ,p_effective_date in date
1759 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1760 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1761 ) IS
1762 --
1763 l_proc varchar2(72) := g_package || 'chk_employment_category';
1764 l_api_updating boolean;
1765 l_ret boolean;
1766 --
1767 begin
1768 hr_utility.set_location('Entering:'||l_proc,10);
1769 if p_employment_category is not null then
1770 --
1771 hr_utility.set_location(l_proc,20);
1772 l_api_updating := irc_isc_shd.api_updating
1773 (p_search_criteria_id => p_search_criteria_id
1774 ,p_object_version_number => p_object_version_number
1775 );
1776 --
1777 hr_utility.set_location(l_proc,30);
1778 if (l_api_updating and
1779 p_employment_category <>
1780 NVL(irc_isc_shd.g_old_rec.employment_category,hr_api.g_varchar2)
1781 ) or (NOT l_api_updating) then
1782 -- Check that employment_category exists in hr_lookups
1783 hr_utility.set_location(l_proc,40);
1784 l_ret := hr_api.not_exists_in_hr_lookups(
1785 p_effective_date => p_effective_date
1786 ,p_lookup_type => 'IRC_EMP_CAT'
1787 ,p_lookup_code => p_employment_category);
1788 if l_ret = true then
1789 fnd_message.set_name('PER','IRC_412023_BAD_EMP_CATEGORY');
1790 fnd_message.raise_error;
1791 end if;
1792 end if;
1793 end if;
1794 hr_utility.set_location(' Leaving:'||l_proc,50);
1795 exception
1796 when app_exception.application_exception then
1797 if hr_multi_message.exception_add
1798 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.EMPLOYMENT_CATEGORY'
1799 ) then
1800 hr_utility.set_location(' Leaving:'||l_proc,60);
1801 raise;
1802 end if;
1803 hr_utility.set_location(' Leaving:'||l_proc,70);
1804 end chk_employment_category;
1805 --
1806 -- ----------------------------------------------------------------------------
1807 -- |--------------------------< chk_work_at_home >----------------------------|
1808 -- ----------------------------------------------------------------------------
1809 -- {Start Of Comments}
1810 --
1811 -- Description:
1812 -- This procedure is used to ensure that work_at_home exists in
1813 -- hr_lookups
1814 --
1815 -- Pre Conditions:
1816 -- g_old_rec has been populated with details of the values currently in
1817 -- the database.
1818 --
1819 -- In Arguments:
1820 -- p_work_at_home
1821 -- p_effective_date
1822 -- p_search_criteria_id
1823 -- p_object_version_number
1824 --
1825 -- Post Success:
1826 -- Processing continues if work_at_home is valid.
1827 --
1828 -- Post Failure:
1829 -- An application error is raised if work_at_home is invalid.
1830 --
1831 -- {End Of Comments}
1832 -- ----------------------------------------------------------------------------
1833 Procedure chk_work_at_home
1834 (p_work_at_home in irc_search_criteria.work_at_home%TYPE
1835 ,p_effective_date in date
1836 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1837 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1838 ) IS
1839 --
1840 l_proc varchar2(72) := g_package || 'chk_work_at_home';
1841 l_api_updating boolean;
1842 l_ret boolean;
1843 --
1844 begin
1845 hr_utility.set_location('Entering:'||l_proc,10);
1846 if p_work_at_home is not null then
1847 --
1848 hr_utility.set_location(l_proc,20);
1849 l_api_updating := irc_isc_shd.api_updating
1850 (p_search_criteria_id => p_search_criteria_id
1851 ,p_object_version_number => p_object_version_number
1852 );
1853 --
1854 hr_utility.set_location(l_proc,30);
1855 if (l_api_updating and
1856 p_work_at_home <>
1857 NVL(irc_isc_shd.g_old_rec.work_at_home,hr_api.g_varchar2)
1858 ) or (NOT l_api_updating) then
1859 -- Check that work_at_home exists in hr_lookups
1860 hr_utility.set_location(l_proc,40);
1861 l_ret := hr_api.not_exists_in_hr_lookups(
1862 p_effective_date => p_effective_date
1863 ,p_lookup_type => 'IRC_WORK_AT_HOME'
1864 ,p_lookup_code => p_work_at_home);
1865 if l_ret = true then
1866 fnd_message.set_name('PER','IRC_412024_BAD_WORK_AT_HOME');
1867 fnd_message.raise_error;
1868 end if;
1869 end if;
1870 end if;
1871 hr_utility.set_location(' Leaving:'||l_proc,50);
1872 exception
1873 when app_exception.application_exception then
1874 if hr_multi_message.exception_add
1875 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.WORK_AT_HOME'
1876 ) then
1877 hr_utility.set_location(' Leaving:'||l_proc,60);
1878 raise;
1879 end if;
1880 hr_utility.set_location(' Leaving:'||l_proc,70);
1881 end chk_work_at_home;
1882 --
1883 -- ----------------------------------------------------------------------------
1884 -- |------------------------< chk_travel_percentage >--------------------------|
1885 -- ----------------------------------------------------------------------------
1886 -- {Start Of Comments}
1887 --
1888 -- Description:
1889 -- This procedure is used to ensure that travel percentage exists in
1890 -- hr_lookups
1891 --
1892 -- Pre Conditions:
1893 -- g_old_rec has been populated with details of the values currently in
1894 -- the database.
1895 --
1896 -- In Arguments:
1897 -- p_travel_percentage
1898 -- p_effective_date
1899 -- p_search_criteria_id
1900 -- p_object_version_number
1901 --
1902 -- Post Success:
1903 -- Processing continues if travel_percentage is valid.
1904 --
1905 -- Post Failure:
1906 -- An application error is raised if travel_percentage is invalid.
1907 --
1908 -- {End Of Comments}
1909 -- ----------------------------------------------------------------------------
1910 Procedure chk_travel_percentage
1911 (p_travel_percentage in irc_search_criteria.travel_percentage%TYPE
1912 ,p_effective_date in date
1913 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1914 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1915 ) IS
1916 --
1917 l_proc varchar2(72) := g_package || 'chk_travel_percentage';
1918 l_api_updating boolean;
1919 l_ret boolean;
1920 --
1921 begin
1922 hr_utility.set_location('Entering:'||l_proc,10);
1923 if p_travel_percentage is not null then
1924 --
1925 hr_utility.set_location(l_proc,20);
1926 l_api_updating := irc_isc_shd.api_updating
1927 (p_search_criteria_id => p_search_criteria_id
1928 ,p_object_version_number => p_object_version_number
1929 );
1930 --
1931 hr_utility.set_location(l_proc,30);
1932 if (l_api_updating and
1933 p_travel_percentage <>
1934 NVL(irc_isc_shd.g_old_rec.travel_percentage,hr_api.g_number))
1935 or (NOT l_api_updating) then
1936 -- Check that travel_percentage exists in hr_lookups
1937 hr_utility.set_location(l_proc,40);
1938 l_ret := hr_api.not_exists_in_hr_lookups(
1939 p_effective_date => p_effective_date
1940 ,p_lookup_type => 'IRC_TRAVEL_PERCENTAGE'
1941 ,p_lookup_code => to_char(p_travel_percentage));
1942 if l_ret = true then
1943 fnd_message.set_name('PER','IRC_412025_BAD_TRAVEL_PERCENT');
1944 fnd_message.raise_error;
1945 end if;
1946 end if;
1947 end if;
1948 hr_utility.set_location(' Leaving:'||l_proc,50);
1949 exception
1950 when app_exception.application_exception then
1951 if hr_multi_message.exception_add
1952 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.TRAVEL_PERCENTAGE'
1953 ) then
1954 hr_utility.set_location(' Leaving:'||l_proc,60);
1955 raise;
1956 end if;
1957 hr_utility.set_location(' Leaving:'||l_proc,70);
1958 end chk_travel_percentage;
1959 --
1960 -- ----------------------------------------------------------------------------
1961 -- |---------------------------< chk_salary_period >--------------------------|
1962 -- ----------------------------------------------------------------------------
1963 -- {Start Of Comments}
1964 --
1965 -- Description:
1966 -- This procedure is used to ensure that salary period exists in
1967 -- hr_lookups
1968 --
1969 -- Pre Conditions:
1970 -- g_old_rec has been populated with details of the values currently in
1971 -- the database.
1972 --
1973 -- In Arguments:
1974 -- p_salary_period
1975 -- p_effective_date
1976 -- p_search_criteria_id
1977 -- p_object_version_number
1978 --
1979 -- Post Success:
1980 -- Processing continues if salary_period is valid.
1981 --
1982 -- Post Failure:
1983 -- An application error is raised if salary_period is invalid.
1984 --
1985 -- {End Of Comments}
1986 -- ----------------------------------------------------------------------------
1987 Procedure chk_salary_period
1988 (p_salary_period in irc_search_criteria.salary_period%TYPE
1989 ,p_min_salary in irc_search_criteria.min_salary%TYPE
1990 ,p_max_salary in irc_search_criteria.max_salary%TYPE
1991 ,p_effective_date in date
1992 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
1993 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
1994 ) IS
1995 --
1996 l_proc varchar2(72) := g_package || 'chk_salary_period';
1997 l_api_updating boolean;
1998 l_max_salary irc_search_criteria.min_salary%type;
1999 l_min_salary irc_search_criteria.max_salary%type;
2000 l_ret boolean;
2001 --
2002 begin
2003 hr_utility.set_location('Entering:'||l_proc,10);
2004 l_api_updating := irc_isc_shd.api_updating
2005 (p_search_criteria_id => p_search_criteria_id
2006 ,p_object_version_number => p_object_version_number
2007 );
2008 hr_utility.set_location(l_proc,20);
2009 if (p_max_salary = hr_api.g_number) then
2010 l_max_salary :=irc_isc_shd.g_old_rec.max_salary;
2011 else
2012 l_max_salary := p_max_salary;
2013 end if;
2014 --
2015 if (p_min_salary = hr_api.g_number) then
2016 l_min_salary :=irc_isc_shd.g_old_rec.min_salary;
2017 else
2018 l_min_salary := p_min_salary;
2019 end if;
2020 --
2021 hr_utility.set_location(l_proc,30);
2022 if (l_api_updating and
2023 p_salary_period <>
2024 NVL(irc_isc_shd.g_old_rec.salary_period,hr_api.g_varchar2)
2025 ) or (NOT l_api_updating) then
2026 -- Check that p_salary_period is not null if there is a salary
2027 if ((l_max_salary is not null or l_min_salary is not null)
2028 and p_salary_period is null) then
2029 fnd_message.set_name('PER','IRC_412065_NO_SAL_PERIOD');
2030 fnd_message.raise_error;
2031 end if;
2032 if p_salary_period is not null then
2033 -- Check that p_salary_period exists in hr_lookups
2034 hr_utility.set_location(l_proc,40);
2035 if hr_api.not_exists_in_hr_lookups(
2036 p_effective_date => p_effective_date
2037 ,p_lookup_type => 'PAY_BASIS'
2038 ,p_lookup_code => p_salary_period)
2039 then
2040 fnd_message.set_name('PER','IRC_412066_BAD_SAL_PERIOD');
2041 fnd_message.raise_error;
2042 end if;
2043 end if;
2044 end if;
2045 hr_utility.set_location(' Leaving:'||l_proc,50);
2046 exception
2047 when app_exception.application_exception then
2048 if hr_multi_message.exception_add
2049 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.SALARY_PERIOD'
2050 ) then
2051 hr_utility.set_location(' Leaving:'||l_proc,60);
2052 raise;
2053 end if;
2054 hr_utility.set_location(' Leaving:'||l_proc,70);
2055 end chk_salary_period;
2056 --
2057 -- ----------------------------------------------------------------------------
2058 -- |--------------------------< chk_salary_currency >--------------------------|
2059 -- ----------------------------------------------------------------------------
2060 -- {Start Of Comments}
2061 --
2062 -- Description:
2063 -- This procedure is used to ensure that salary_currency exists in
2064 -- fnd_currencies
2065 --
2066 -- Pre Conditions:
2067 -- g_old_rec has been populated with details of the values currently in
2068 -- the database.
2069 --
2070 -- In Arguments:
2071 -- p_salary_currency
2072 -- p_min_sal
2073 -- p_max_sal
2074 -- p_effective_date
2075 -- p_search_criteria_id
2076 -- p_object_version_number
2077 --
2078 -- Post Success:
2079 -- Processing continues if salary_currency is valid.
2080 --
2081 -- Post Failure:
2082 -- An application error is raised salary_currency is invalid.
2083 --
2084 -- {End Of Comments}
2085 -- ----------------------------------------------------------------------------
2086 Procedure chk_salary_currency
2087 (p_salary_currency in irc_search_criteria.salary_currency%TYPE
2088 ,p_min_salary in irc_search_criteria.min_salary%TYPE
2089 ,p_max_salary in irc_search_criteria.max_salary%TYPE
2090 ,p_effective_date in date
2091 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
2092 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
2093 ) IS
2094 --
2095 l_proc varchar2(72) := g_package || 'chk_salary_currency';
2096 l_salary_currency varchar2(1);
2097 l_api_updating boolean;
2098 --
2099 cursor csr_salary_currency is
2100 select null from fnd_currencies fc
2101 where p_salary_currency = fc.currency_code
2102 and p_effective_date between
2103 nvl(fc.start_date_active, p_effective_date)
2104 and nvl(fc.end_date_active, p_effective_date);
2105 --
2106 begin
2107 hr_utility.set_location('Entering:'||l_proc,10);
2108 -- Only proceed with max_salary validation when multiple message list does not
2109 -- already contain an error associated with the min_salary or max_salary
2110 -- columns.
2111 hr_utility.set_location(l_proc,15);
2112 if hr_multi_message.no_all_inclusive_error
2113 ( p_check_column1 => 'IRC_SEARCH_CRITERIA.MIN_SALARY'
2114 ,p_check_column2 => 'IRC_SEARCH_CRITERIA.MAX_SALARY'
2115 ) then
2116 --
2117 l_api_updating := irc_isc_shd.api_updating
2118 (p_search_criteria_id => p_search_criteria_id
2119 ,p_object_version_number => p_object_version_number
2120 );
2121 --
2122 hr_utility.set_location(l_proc,20);
2123 if (l_api_updating and
2124 (NVL(p_salary_currency,hr_api.g_varchar2) <>
2125 NVL(irc_isc_shd.g_old_rec.salary_currency,hr_api.g_varchar2))
2126 or
2127 (NVL(p_max_salary,hr_api.g_number) <>
2128 NVL(irc_isc_shd.g_old_rec.max_salary,hr_api.g_number))
2129 or
2130 (NVL(p_min_salary,hr_api.g_number) <>
2131 NVL(irc_isc_shd.g_old_rec.min_salary,hr_api.g_number))
2132 )
2133 or (NOT l_api_updating) then
2134 --
2135 -- Check that salary currency is not null if either min_sal or max_sal is
2136 -- not null.
2137 --
2138 hr_utility.set_location(l_proc,30);
2139 if (p_min_salary is not null or p_max_salary is not null) and
2140 (p_salary_currency is null) then
2141 fnd_message.set_name('PER','IRC_412018_CURRENCY_NOT_FOUND');
2142 fnd_message.raise_error;
2143 end if;
2144 --
2145 -- Check that salary_currency exists in fnd_currencies.
2146 --
2147 if p_salary_currency is not null then
2148 hr_utility.set_location(l_proc,40);
2149 open csr_salary_currency;
2150 fetch csr_salary_currency into l_salary_currency;
2151 hr_utility.set_location(l_proc,50);
2152 if csr_salary_currency%NOTFOUND then
2153 close csr_salary_currency;
2154 fnd_message.set_name('PER','IRC_412026_BAD_SALARY_CURRENCY');
2155 fnd_message.raise_error;
2156 end if;
2157 close csr_salary_currency;
2158 end if;
2159 end if;
2160 end if;
2161 hr_utility.set_location(' Leaving:'||l_proc,60);
2162 exception
2163 --
2164 -- When Multiple error detection is enabled handle the application errors
2165 -- which have been raised by this procedure. Transfer the error to the
2166 -- Multiple message list and associate the error with max_salary,
2167 -- min_salary and salary_currency columns.
2168 when app_exception.application_exception then
2169 if hr_multi_message.exception_add
2170 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.MIN_SALARY'
2171 ,p_associated_column2 => 'IRC_SEARCH_CRITERIA.MAX_SALARY'
2172 ,p_associated_column3 => 'IRC_SEARCH_CRITERIA.SALARY_CURRENCY'
2173 ) then
2174 hr_utility.set_location(' Leaving:'||l_proc,70);
2175 raise;
2176 end if;
2177 hr_utility.set_location(' Leaving:'||l_proc,80);
2178 end chk_salary_currency;
2179 --
2180 -- ----------------------------------------------------------------------------
2181 -- |------------------------< chk_date_posted >-------------------------------|
2182 -- ----------------------------------------------------------------------------
2183 -- {Start Of Comments}
2184 --
2185 -- Description:
2186 -- This procedure is used to ensure that date posted value exists in
2187 -- hr_lookups
2188 --
2189 -- Pre Conditions:
2190 -- g_old_rec has been populated with details of the values currently in
2191 -- the database.
2192 --
2193 -- In Arguments:
2194 -- p_date_posted
2195 -- p_effective_date
2196 -- p_search_criteria_id
2197 -- p_object_version_number
2198 --
2199 -- Post Success:
2200 -- Processing continues if date posted is valid.
2201 --
2202 -- Post Failure:
2203 -- An application error is raised if date posted is invalid.
2204 --
2205 -- {End Of Comments}
2206 -- ----------------------------------------------------------------------------
2207 Procedure chk_date_posted
2208 (p_date_posted in irc_search_criteria.date_posted%TYPE
2209 ,p_effective_date in date
2210 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
2211 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
2212 ) IS
2213 --
2214 l_proc varchar2(72) := g_package || 'chk_date_posted';
2215 l_api_updating boolean;
2216 l_ret boolean;
2217 --
2218 begin
2219 hr_utility.set_location('Entering:'||l_proc,10);
2220 if p_date_posted is not null then
2221 --
2222 hr_utility.set_location(l_proc,20);
2223 l_api_updating := irc_isc_shd.api_updating
2224 (p_search_criteria_id => p_search_criteria_id
2225 ,p_object_version_number => p_object_version_number
2226 );
2227 --
2228 hr_utility.set_location(l_proc,30);
2229 if (l_api_updating and
2230 p_date_posted <>
2231 NVL(irc_isc_shd.g_old_rec.date_posted,hr_api.g_varchar2)
2232 ) or (NOT l_api_updating) then
2233 -- Check that date_posted exists in hr_lookups
2234 hr_utility.set_location(l_proc,40);
2235 l_ret := hr_api.not_exists_in_hr_lookups(
2236 p_effective_date => p_effective_date
2237 ,p_lookup_type => 'IRC_VACANCY_SEARCH_DATE'
2238 ,p_lookup_code => p_date_posted);
2239 if l_ret = true then
2240 fnd_message.set_name('PER','IRC_412112_BAD_DATE_POSTED');
2241 fnd_message.raise_error;
2242 end if;
2243 end if;
2244 end if;
2245 hr_utility.set_location(' Leaving:'||l_proc,50);
2246 exception
2247 when app_exception.application_exception then
2248 if hr_multi_message.exception_add
2249 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.DATE_POSTED'
2250 ) then
2251 hr_utility.set_location(' Leaving:'||l_proc,60);
2252 raise;
2253 end if;
2254 hr_utility.set_location(' Leaving:'||l_proc,70);
2255 end chk_date_posted;
2256 --
2257 -- ----------------------------------------------------------------------------
2258 -- |------------------------< chk_employee_contractor>------------------------|
2259 -- ----------------------------------------------------------------------------
2260 -- {Start Of Comments}
2261 --
2262 -- Description:
2263 -- This procedure is used to raise a warning to the manager if both the
2264 -- Employee and Contractor flags are unchecked
2265 --
2266 -- Pre Conditions:
2267 -- g_old_rec has been populated with details of the values currently in
2268 -- the database.
2269 --
2270 -- In Arguments:
2271 -- p_employee
2272 -- p_contractor
2273 --
2274 -- Post Success:
2275 -- Processing continues if both the employee and contractor checkboxes are
2276 -- not unchecked.
2277 --
2278 -- Post Failure:
2279 -- A warning message is raised if both the employee and contractor
2280 -- checkboxes are unchecked.
2281 --
2282 -- {End Of Comments}
2283 -- ----------------------------------------------------------------------------
2284 Procedure chk_employee_contractor
2285 (p_employee in irc_search_criteria.employee%TYPE
2286 ,p_contractor in irc_search_criteria.contractor%TYPE
2287 ) IS
2288 --
2289 l_proc varchar2(72) := g_package || 'chk_employee_contractor';
2290 l_api_updating boolean;
2291 l_ret boolean;
2292 --
2293 begin
2294 hr_utility.set_location('Entering:'||l_proc,10);
2295 if p_employee = 'N' and p_contractor = 'N' then
2296 --
2297 hr_utility.set_location(l_proc,20);
2298 fnd_message.set_name('PER', 'IRC_412152_VAC_EMP_CON_WARN');
2299 hr_multi_message.add
2300 (p_message_type => hr_multi_message.g_warning_msg
2301 );
2302 --
2303 end if;
2304 hr_utility.set_location(' Leaving:'||l_proc,30);
2305 --
2306 end chk_employee_contractor;
2307 --
2308 -- ----------------------------------------------------------------------------
2309 -- |-------------------------< chk_keywords >---------------------------------|
2310 -- ----------------------------------------------------------------------------
2311 -- {Start Of Comments}
2312 --
2313 -- Description:
2314 -- This procedure is used to ensure that the keyword is valid
2315 --
2316 -- Pre Conditions:
2317 -- g_old_rec has been populated with details of the values currently in
2318 -- the database.
2319 --
2320 -- In Arguments:
2321 -- p_keywords
2322 -- p_search_criteria_id
2323 -- p_object_version_number
2324 --
2325 -- Post Success:
2326 -- Processing continues if the keyword is valid.
2327 --
2328 -- Post Failure:
2329 -- An application error is raised if the keyword is invalid.
2330 --
2331 -- {End Of Comments}
2332 -- ----------------------------------------------------------------------------
2333 Procedure chk_keywords
2334 (p_keywords in irc_search_criteria.keywords%TYPE
2335 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
2336 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
2337 ) IS
2338 --
2339 l_proc varchar2(72) := g_package || 'chk_keywords';
2340 l_api_updating boolean;
2341 l_ret boolean;
2342 --
2343 begin
2344 hr_utility.set_location('Entering:'||l_proc,10);
2345 if p_keywords is not null then
2346 --
2347 hr_utility.set_location(l_proc,20);
2348 l_api_updating := irc_isc_shd.api_updating
2349 (p_search_criteria_id => p_search_criteria_id
2350 ,p_object_version_number => p_object_version_number
2351 );
2352 --
2353 hr_utility.set_location(l_proc,30);
2354 if ((l_api_updating and
2355 p_keywords <>
2356 NVL(irc_isc_shd.g_old_rec.keywords,hr_api.g_varchar2)
2357 ) or (NOT l_api_updating)) then
2358 hr_utility.set_location(l_proc,40);
2359 --
2360 -- Check the validity of the keywords by using the
2361 -- procedure irc_query_parser_pkg.isInvalidKeyword()
2362 --
2363 l_ret := irc_query_parser_pkg.isInvalidKeyword (input_text => p_keywords);
2364 if l_ret then
2365 hr_utility.set_location(l_proc,45);
2366 fnd_message.set_name('PER','IRC_INVALID_KEYWORDS');
2367 fnd_message.raise_error;
2368 end if;
2369 end if;
2370 end if;
2371 --
2372 hr_utility.set_location(' Leaving:'||l_proc,50);
2373 exception
2374 when app_exception.application_exception then
2375 if hr_multi_message.exception_add
2376 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.KEYWORDS'
2377 ) then
2378 hr_utility.set_location(' Leaving:'||l_proc,60);
2379 raise;
2380 end if;
2381 hr_utility.set_location(' Leaving:'||l_proc,70);
2382 end chk_keywords;
2383 --
2384 -- ----------------------------------------------------------------------------
2385 -- |---------------------------< insert_validate >----------------------------|
2386 -- ----------------------------------------------------------------------------
2387 Procedure insert_validate
2388 (p_effective_date in date
2389 ,p_rec in irc_isc_shd.g_rec_type
2390 ) is
2391 --
2392 l_proc varchar2(72) := g_package||'insert_validate';
2393 --
2394 Begin
2395 hr_utility.set_location('Entering:'||l_proc, 5);
2396 --
2397 -- Validate Important Attributes
2398 --
2399 -- Call all supporting business operations
2400 --
2401 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
2402 --
2403 -- After validating the set of important attributes,
2404 -- if Multiple message detection is enabled and at least
2405 -- one error has been found then abort further validation.
2406 --
2407 -- Validate Dependent Attributes
2408 --
2409 hr_utility.set_location(l_proc, 20);
2410 if p_rec.object_type in ('PERSON','WPREF') then
2411 --
2412 hr_utility.set_location(l_proc, 30);
2413 hr_api.mandatory_arg_error
2414 (p_api_name => l_proc
2415 ,p_argument => 'EFFECTIVE_DATE'
2416 ,p_argument_value => p_effective_date
2417 );
2418 --
2419 hr_utility.set_location(l_proc, 40);
2420 irc_isc_bus.chk_person_id(
2421 p_person_id => p_rec.object_id
2422 ,p_effective_date => p_effective_date
2423 );
2424 --
2425 hr_utility.set_location(l_proc, 50);
2426 irc_isc_bus.chk_search_name(
2427 p_search_name => p_rec.search_name
2428 ,p_person_id => p_rec.object_id
2429 ,p_search_criteria_id => p_rec.search_criteria_id
2430 ,p_object_version_number => p_rec.object_version_number
2431 );
2432 --
2433 hr_utility.set_location(l_proc, 60);
2434 irc_isc_bus.chk_distance_to_location(
2435 p_distance_to_location => p_rec.distance_to_location
2436 ,p_geocode_location => p_rec.geocode_location
2437 ,p_search_criteria_id => p_rec.search_criteria_id
2438 ,p_object_version_number => p_rec.object_version_number
2439 );
2440 --
2441 hr_utility.set_location(l_proc, 63);
2442 irc_isc_bus.chk_location_id(
2443 p_location_id => p_rec.location_id
2444 ,p_search_criteria_id => p_rec.search_criteria_id
2445 ,p_object_version_number => p_rec.object_version_number
2446 );
2447 --
2448 hr_utility.set_location(l_proc, 66);
2449 irc_isc_bus.chk_longitude_latitude(
2450 p_longitude => p_rec.longitude
2451 ,p_latitude => p_rec.latitude
2452 ,p_search_criteria_id => p_rec.search_criteria_id
2453 ,p_object_version_number => p_rec.object_version_number
2454 );
2455 --
2456 hr_utility.set_location(l_proc, 70);
2457 irc_isc_bus.chk_use_for_matching(
2458 p_use_for_matching => p_rec.use_for_matching
2459 ,p_search_criteria_id => p_rec.search_criteria_id
2460 ,p_object_version_number => p_rec.object_version_number
2461 );
2462 --
2463 hr_utility.set_location(l_proc, 80);
2464 irc_isc_bus.chk_match_competence(
2465 p_match_competence => p_rec.match_competence
2466 ,p_search_criteria_id => p_rec.search_criteria_id
2467 ,p_object_version_number => p_rec.object_version_number
2468 );
2469 --
2470 hr_utility.set_location(l_proc, 90);
2471 irc_isc_bus.chk_match_qualification(
2472 p_match_qualification => p_rec.match_qualification
2473 ,p_search_criteria_id => p_rec.search_criteria_id
2474 ,p_object_version_number => p_rec.object_version_number
2475 );
2476 --
2477 hr_utility.set_location(l_proc, 100);
2478 irc_isc_bus.chk_min_salary(
2479 p_min_salary => p_rec.min_salary
2480 ,p_search_criteria_id => p_rec.search_criteria_id
2481 ,p_object_version_number => p_rec.object_version_number
2482 );
2483 --
2484 end if;
2485
2486 if p_rec.object_type= 'WPREF' then
2487 hr_utility.set_location(l_proc, 114);
2488 irc_isc_bus.chk_unique_work (
2489 p_object_id => p_rec.object_id
2490 );
2491 hr_utility.set_location(l_proc, 117);
2492 irc_isc_bus.chk_work_at_home(
2493 p_work_at_home => p_rec.work_at_home
2494 ,p_effective_date => p_effective_date
2495 ,p_search_criteria_id => p_rec.search_criteria_id
2496 ,p_object_version_number => p_rec.object_version_number
2497 );
2498 hr_utility.set_location(l_proc, 119);
2499 irc_isc_bus.chk_keywords(
2500 p_keywords => p_rec.keywords
2501 ,p_search_criteria_id => p_rec.search_criteria_id
2502 ,p_object_version_number => p_rec.object_version_number
2503 );
2504 end if;
2505 --
2506 hr_utility.set_location(l_proc, 110);
2507 if p_rec.object_type= 'VACANCY' then
2508 --
2509 hr_utility.set_location(l_proc, 70);
2510 irc_isc_bus.chk_vacancy_id(
2511 p_vacancy_id => p_rec.object_id
2512 ,p_effective_date => p_effective_date
2513 );
2514 --
2515 hr_utility.set_location(l_proc, 120);
2516 irc_isc_bus.chk_min_qual_level(
2517 p_min_qual_level => p_rec.min_qual_level
2518 ,p_search_criteria_id => p_rec.search_criteria_id
2519 ,p_object_version_number => p_rec.object_version_number
2520 );
2521 --
2522 hr_utility.set_location(l_proc, 130);
2523 irc_isc_bus.chk_min_salary(
2524 p_min_salary => p_rec.min_salary
2525 ,p_search_criteria_id => p_rec.search_criteria_id
2526 ,p_object_version_number => p_rec.object_version_number
2527 );
2528 --
2529 hr_utility.set_location(l_proc, 140);
2530 irc_isc_bus.chk_max_salary(
2531 p_max_salary => p_rec.max_salary
2532 ,p_min_salary => p_rec.min_salary
2533 ,p_search_criteria_id => p_rec.search_criteria_id
2534 ,p_object_version_number => p_rec.object_version_number
2535 );
2536 --
2537 hr_utility.set_location(l_proc, 145);
2538 irc_isc_bus.chk_qual_rank(
2539 p_min_qual_level => p_rec.min_qual_level
2540 ,p_max_qual_level => p_rec.max_qual_level
2541 );
2542 --
2543 hr_utility.set_location(l_proc, 147);
2544 irc_isc_bus.chk_employee_contractor(
2545 p_employee => p_rec.employee
2546 ,p_contractor => p_rec.contractor
2547 );
2548 --
2549 end if;
2550 --
2551 hr_utility.set_location(l_proc, 150);
2552 irc_isc_bus.chk_salary_currency
2553 (p_salary_currency => p_rec.salary_currency
2554 ,p_min_salary => p_rec.min_salary
2555 ,p_max_salary => p_rec.max_salary
2556 ,p_effective_date => p_effective_date
2557 ,p_search_criteria_id => p_rec.search_criteria_id
2558 ,p_object_version_number => p_rec.object_version_number
2559 );
2560 --
2561 --
2562 hr_utility.set_location(l_proc, 155);
2563 irc_isc_bus.chk_salary_period(
2564 p_salary_period => p_rec.salary_period
2565 ,p_min_salary => p_rec.min_salary
2566 ,p_max_salary => p_rec.max_salary
2567 ,p_effective_date => p_effective_date
2568 ,p_search_criteria_id => p_rec.search_criteria_id
2569 ,p_object_version_number => p_rec.object_version_number
2570 );
2571 --
2572 hr_utility.set_location(l_proc, 160);
2573 irc_isc_bus.chk_employee(
2574 p_employee => p_rec.employee
2575 ,p_search_criteria_id => p_rec.search_criteria_id
2576 ,p_object_version_number => p_rec.object_version_number
2577 );
2578 --
2579 hr_utility.set_location(l_proc, 170);
2580 irc_isc_bus.chk_contractor(
2581 p_contractor => p_rec.contractor
2582 ,p_search_criteria_id => p_rec.search_criteria_id
2583 ,p_object_version_number => p_rec.object_version_number
2584 );
2585 --
2586 hr_utility.set_location(l_proc, 180);
2587 irc_isc_bus.chk_professional_area(
2588 p_professional_area => p_rec.professional_area
2589 ,p_effective_date => p_effective_date
2590 ,p_search_criteria_id => p_rec.search_criteria_id
2591 ,p_object_version_number => p_rec.object_version_number
2592 );
2593 --
2594 hr_utility.set_location(l_proc, 190);
2595 irc_isc_bus.chk_employment_category(
2596 p_employment_category => p_rec.employment_category
2597 ,p_effective_date => p_effective_date
2598 ,p_search_criteria_id => p_rec.search_criteria_id
2599 ,p_object_version_number => p_rec.object_version_number
2600 );
2601 --
2602 hr_utility.set_location(l_proc, 200);
2603 irc_isc_bus.chk_work_at_home(
2604 p_work_at_home => p_rec.work_at_home
2605 ,p_effective_date => p_effective_date
2606 ,p_search_criteria_id => p_rec.search_criteria_id
2607 ,p_object_version_number => p_rec.object_version_number
2608 );
2609 --
2610 hr_utility.set_location(l_proc, 210);
2611 irc_isc_bus.chk_travel_percentage(
2612 p_travel_percentage => p_rec.travel_percentage
2613 ,p_effective_date => p_effective_date
2614 ,p_search_criteria_id => p_rec.search_criteria_id
2615 ,p_object_version_number => p_rec.object_version_number
2616 );
2617 --
2618 hr_utility.set_location(l_proc, 215);
2619 irc_isc_bus.chk_date_posted(
2620 p_date_posted => p_rec.date_posted
2621 ,p_effective_date => p_effective_date
2622 ,p_search_criteria_id => p_rec.search_criteria_id
2623 ,p_object_version_number => p_rec.object_version_number
2624 );
2625 --
2626 hr_utility.set_location(l_proc, 220);
2627 irc_isc_bus.chk_ddf(p_rec);
2628 --
2629 hr_utility.set_location(l_proc, 230);
2630 irc_isc_bus.chk_df(p_rec);
2631 --
2632 hr_utility.set_location(' Leaving:'||l_proc, 240);
2633 End insert_validate;
2634 --
2635 -- ----------------------------------------------------------------------------
2636 -- |---------------------------< update_validate >----------------------------|
2637 -- ----------------------------------------------------------------------------
2638 Procedure update_validate
2639 (p_effective_date in date
2640 ,p_rec in irc_isc_shd.g_rec_type
2641 ) is
2642 --
2643 l_proc varchar2(72) := g_package||'update_validate';
2644 --
2645 Begin
2646 hr_utility.set_location('Entering:'||l_proc, 10);
2647 --
2648 -- Validate Important Attributes
2649 --
2650 -- Call all supporting business operations
2651 --
2652 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
2653 -- After validating the set of important attributes,
2654 -- if Multiple message detection is enabled and at least
2655 -- one error has been found then abort further validation.
2656 --
2657 -- Validate Dependent Attributes
2658 --
2659 hr_utility.set_location(l_proc,15);
2660 chk_non_updateable_args
2661 (p_effective_date => p_effective_date
2662 ,p_rec => p_rec
2663 );
2664 --
2665 --
2666 hr_utility.set_location(l_proc, 20);
2667
2668 if p_rec.object_type in( 'PERSON','WPREF') then
2669 --
2670 hr_utility.set_location(l_proc, 30);
2671 irc_isc_bus.chk_search_name(
2672 p_search_name => p_rec.search_name
2673 ,p_person_id => p_rec.object_id
2674 ,p_search_criteria_id => p_rec.search_criteria_id
2675 ,p_object_version_number => p_rec.object_version_number
2676 );
2677 --
2678 hr_utility.set_location(l_proc, 40);
2679 irc_isc_bus.chk_distance_to_location(
2680 p_distance_to_location => p_rec.distance_to_location
2681 ,p_geocode_location => p_rec.geocode_location
2682 ,p_search_criteria_id => p_rec.search_criteria_id
2683 ,p_object_version_number => p_rec.object_version_number
2684 );
2685 --
2686 hr_utility.set_location(l_proc, 43);
2687 irc_isc_bus.chk_location_id(
2688 p_location_id => p_rec.location_id
2689 ,p_search_criteria_id => p_rec.search_criteria_id
2690 ,p_object_version_number => p_rec.object_version_number
2691 );
2692 --
2693 hr_utility.set_location(l_proc, 46);
2694 irc_isc_bus.chk_longitude_latitude(
2695 p_longitude => p_rec.longitude
2696 ,p_latitude => p_rec.latitude
2697 ,p_search_criteria_id => p_rec.search_criteria_id
2698 ,p_object_version_number => p_rec.object_version_number
2699 );
2700 --
2701 hr_utility.set_location(l_proc, 50);
2702 irc_isc_bus.chk_use_for_matching(
2703 p_use_for_matching => p_rec.use_for_matching
2704 ,p_search_criteria_id => p_rec.search_criteria_id
2705 ,p_object_version_number => p_rec.object_version_number
2706 );
2707 --
2708 hr_utility.set_location(l_proc, 60);
2709 irc_isc_bus.chk_match_competence(
2710 p_match_competence => p_rec.match_competence
2711 ,p_search_criteria_id => p_rec.search_criteria_id
2712 ,p_object_version_number => p_rec.object_version_number
2713 );
2714 --
2715 hr_utility.set_location(l_proc, 70);
2716 irc_isc_bus.chk_match_qualification(
2717 p_match_qualification => p_rec.match_qualification
2718 ,p_search_criteria_id => p_rec.search_criteria_id
2719 ,p_object_version_number => p_rec.object_version_number
2720 );
2721 --
2722 hr_utility.set_location(l_proc, 80);
2723 irc_isc_bus.chk_min_salary(
2724 p_min_salary => p_rec.min_salary
2725 ,p_search_criteria_id => p_rec.search_criteria_id
2726 ,p_object_version_number => p_rec.object_version_number
2727 );
2728 --
2729 hr_utility.set_location(l_proc, 85);
2730 irc_isc_bus.chk_date_posted(
2731 p_date_posted => p_rec.date_posted
2732 ,p_effective_date => p_effective_date
2733 ,p_search_criteria_id => p_rec.search_criteria_id
2734 ,p_object_version_number => p_rec.object_version_number
2735 );
2736 --
2737 end if;
2738 --
2739 if p_rec.object_type= 'WPREF' then
2740 hr_utility.set_location(l_proc, 87);
2741 irc_isc_bus.chk_work_at_home(
2742 p_work_at_home => p_rec.work_at_home
2743 ,p_effective_date => p_effective_date
2744 ,p_search_criteria_id => p_rec.search_criteria_id
2745 ,p_object_version_number => p_rec.object_version_number
2746 );
2747 --
2748 irc_isc_bus.chk_keywords(
2749 p_keywords => p_rec.keywords
2750 ,p_search_criteria_id => p_rec.search_criteria_id
2751 ,p_object_version_number => p_rec.object_version_number
2752 );
2753 end if;
2754 --
2755 hr_utility.set_location(l_proc, 90);
2756 if p_rec.object_type= 'VACANCY' then
2757 --
2758 hr_utility.set_location(l_proc, 100);
2759 irc_isc_bus.chk_min_qual_level(
2760 p_min_qual_level => p_rec.min_qual_level
2761 ,p_search_criteria_id => p_rec.search_criteria_id
2762 ,p_object_version_number => p_rec.object_version_number
2763 );
2764 --
2765 hr_utility.set_location(l_proc, 110);
2766 irc_isc_bus.chk_min_salary(
2767 p_min_salary => p_rec.min_salary
2768 ,p_search_criteria_id => p_rec.search_criteria_id
2769 ,p_object_version_number => p_rec.object_version_number
2770 );
2771 --
2772 hr_utility.set_location(l_proc, 120);
2773 irc_isc_bus.chk_max_salary(
2774 p_max_salary => p_rec.max_salary
2775 ,p_min_salary => p_rec.min_salary
2776 ,p_search_criteria_id => p_rec.search_criteria_id
2777 ,p_object_version_number => p_rec.object_version_number
2778 );
2779 --
2780 hr_utility.set_location(l_proc, 125);
2781 irc_isc_bus.chk_qual_rank(
2782 p_min_qual_level => p_rec.min_qual_level
2783 ,p_max_qual_level => p_rec.max_qual_level
2784 );
2785 --
2786 hr_utility.set_location(l_proc, 127);
2787 irc_isc_bus.chk_employee_contractor(
2788 p_employee => p_rec.employee
2789 ,p_contractor => p_rec.contractor
2790 );
2791 --
2792 end if;
2793 --
2794 hr_utility.set_location(l_proc, 130);
2795 irc_isc_bus.chk_salary_currency
2796 (p_salary_currency => p_rec.salary_currency
2797 ,p_min_salary => p_rec.min_salary
2798 ,p_max_salary => p_rec.max_salary
2799 ,p_effective_date => p_effective_date
2800 ,p_search_criteria_id => p_rec.search_criteria_id
2801 ,p_object_version_number => p_rec.object_version_number
2802 );
2803 --
2804 hr_utility.set_location(l_proc, 135);
2805 irc_isc_bus.chk_salary_period(
2806 p_salary_period => p_rec.salary_period
2807 ,p_min_salary => p_rec.min_salary
2808 ,p_max_salary => p_rec.max_salary
2809 ,p_effective_date => p_effective_date
2810 ,p_search_criteria_id => p_rec.search_criteria_id
2811 ,p_object_version_number => p_rec.object_version_number
2812 );
2813 --
2814 hr_utility.set_location(l_proc, 140);
2815 irc_isc_bus.chk_employee(
2816 p_employee => p_rec.employee
2817 ,p_search_criteria_id => p_rec.search_criteria_id
2818 ,p_object_version_number => p_rec.object_version_number
2819 );
2820 --
2821 hr_utility.set_location(l_proc, 150);
2822 irc_isc_bus.chk_contractor(
2823 p_contractor => p_rec.contractor
2824 ,p_search_criteria_id => p_rec.search_criteria_id
2825 ,p_object_version_number => p_rec.object_version_number
2826 );
2827 --
2828 hr_utility.set_location(l_proc, 160);
2829 irc_isc_bus.chk_professional_area(
2830 p_professional_area => p_rec.professional_area
2831 ,p_effective_date => p_effective_date
2832 ,p_search_criteria_id => p_rec.search_criteria_id
2833 ,p_object_version_number => p_rec.object_version_number
2834 );
2835 --
2836 hr_utility.set_location(l_proc, 170);
2837 irc_isc_bus.chk_employment_category(
2838 p_employment_category => p_rec.employment_category
2839 ,p_effective_date => p_effective_date
2840 ,p_search_criteria_id => p_rec.search_criteria_id
2841 ,p_object_version_number => p_rec.object_version_number
2842 );
2843 --
2844 hr_utility.set_location(l_proc, 180);
2845 irc_isc_bus.chk_work_at_home(
2846 p_work_at_home => p_rec.work_at_home
2847 ,p_effective_date => p_effective_date
2848 ,p_search_criteria_id => p_rec.search_criteria_id
2849 ,p_object_version_number => p_rec.object_version_number
2850 );
2851 --
2852 hr_utility.set_location(l_proc, 190);
2853 irc_isc_bus.chk_travel_percentage(
2854 p_travel_percentage => p_rec.travel_percentage
2855 ,p_effective_date => p_effective_date
2856 ,p_search_criteria_id => p_rec.search_criteria_id
2857 ,p_object_version_number => p_rec.object_version_number
2858 );
2859 --
2860 hr_utility.set_location(l_proc, 200);
2861 irc_isc_bus.chk_ddf(p_rec);
2862 --
2863 hr_utility.set_location(l_proc, 210);
2864 irc_isc_bus.chk_df(p_rec);
2865 --
2866 hr_utility.set_location(' Leaving:'||l_proc, 10);
2867 End update_validate;
2868 --
2869 -- ----------------------------------------------------------------------------
2870 -- |---------------------------< delete_validate >----------------------------|
2871 -- ----------------------------------------------------------------------------
2872 Procedure delete_validate
2873 (p_rec in irc_isc_shd.g_rec_type
2874 ) is
2875 --
2876 l_proc varchar2(72) := g_package||'delete_validate';
2877 --
2878 Begin
2879 hr_utility.set_location('Entering:'||l_proc, 5);
2880 --
2881 -- Call all supporting business operations
2882 --
2883 hr_utility.set_location(' Leaving:'||l_proc, 10);
2884 End delete_validate;
2885 --
2886 end irc_isc_bus;