[Home] [Help]
PACKAGE BODY: APPS.IRC_ISC_BUS
Source
1 Package Body irc_isc_bus as
2 /* $Header: iriscrhi.pkb 120.1 2010/12/17 05:32:56 prasashe ship $ */
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 fnd_message.raise_error;
2300 --
2301 end if;
2302 hr_utility.set_location(' Leaving:'||l_proc,30);
2303 --
2304 exception
2305 when app_exception.application_exception then
2306 if hr_multi_message.exception_add
2307 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.EMPLOYEE'
2308 ) then
2309 hr_utility.set_location(' Leaving:'||l_proc,60);
2310 raise;
2311 end if;
2312 end chk_employee_contractor;
2313 --
2314 -- ----------------------------------------------------------------------------
2315 -- |-------------------------< chk_keywords >---------------------------------|
2316 -- ----------------------------------------------------------------------------
2317 -- {Start Of Comments}
2318 --
2319 -- Description:
2320 -- This procedure is used to ensure that the keyword is valid
2321 --
2322 -- Pre Conditions:
2323 -- g_old_rec has been populated with details of the values currently in
2324 -- the database.
2325 --
2326 -- In Arguments:
2327 -- p_keywords
2328 -- p_search_criteria_id
2329 -- p_object_version_number
2330 --
2331 -- Post Success:
2332 -- Processing continues if the keyword is valid.
2333 --
2334 -- Post Failure:
2335 -- An application error is raised if the keyword is invalid.
2336 --
2337 -- {End Of Comments}
2338 -- ----------------------------------------------------------------------------
2339 Procedure chk_keywords
2340 (p_keywords in irc_search_criteria.keywords%TYPE
2341 ,p_search_criteria_id in irc_search_criteria.search_criteria_id%TYPE
2342 ,p_object_version_number in irc_search_criteria.object_version_number%TYPE
2343 ) IS
2344 --
2345 l_proc varchar2(72) := g_package || 'chk_keywords';
2346 l_api_updating boolean;
2347 l_ret boolean;
2348 --
2349 begin
2350 hr_utility.set_location('Entering:'||l_proc,10);
2351 if p_keywords is not null then
2352 --
2353 hr_utility.set_location(l_proc,20);
2354 l_api_updating := irc_isc_shd.api_updating
2355 (p_search_criteria_id => p_search_criteria_id
2356 ,p_object_version_number => p_object_version_number
2357 );
2358 --
2359 hr_utility.set_location(l_proc,30);
2360 if ((l_api_updating and
2361 p_keywords <>
2362 NVL(irc_isc_shd.g_old_rec.keywords,hr_api.g_varchar2)
2363 ) or (NOT l_api_updating)) then
2364 hr_utility.set_location(l_proc,40);
2365 --
2366 -- Check the validity of the keywords by using the
2367 -- procedure irc_query_parser_pkg.isInvalidKeyword()
2368 --
2369 l_ret := irc_query_parser_pkg.isInvalidKeyword (input_text => p_keywords);
2370 if l_ret then
2371 hr_utility.set_location(l_proc,45);
2372 fnd_message.set_name('PER','IRC_INVALID_KEYWORDS');
2373 fnd_message.raise_error;
2374 end if;
2375 end if;
2376 end if;
2377 --
2378 hr_utility.set_location(' Leaving:'||l_proc,50);
2379 exception
2380 when app_exception.application_exception then
2381 if hr_multi_message.exception_add
2382 (p_associated_column1 => 'IRC_SEARCH_CRITERIA.KEYWORDS'
2383 ) then
2384 hr_utility.set_location(' Leaving:'||l_proc,60);
2385 raise;
2386 end if;
2387 hr_utility.set_location(' Leaving:'||l_proc,70);
2388 end chk_keywords;
2389 --
2390 -- ----------------------------------------------------------------------------
2391 -- |---------------------------< insert_validate >----------------------------|
2392 -- ----------------------------------------------------------------------------
2393 Procedure insert_validate
2394 (p_effective_date in date
2395 ,p_rec in irc_isc_shd.g_rec_type
2396 ) is
2397 --
2398 l_proc varchar2(72) := g_package||'insert_validate';
2399 --
2400 Begin
2401 hr_utility.set_location('Entering:'||l_proc, 5);
2402 --
2403 -- Validate Important Attributes
2404 --
2405 -- Call all supporting business operations
2406 --
2407 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
2408 --
2409 -- After validating the set of important attributes,
2410 -- if Multiple message detection is enabled and at least
2411 -- one error has been found then abort further validation.
2412 --
2413 -- Validate Dependent Attributes
2414 --
2415 hr_utility.set_location(l_proc, 20);
2416 if p_rec.object_type in ('PERSON','WPREF') then
2417 --
2418 hr_utility.set_location(l_proc, 30);
2419 hr_api.mandatory_arg_error
2420 (p_api_name => l_proc
2421 ,p_argument => 'EFFECTIVE_DATE'
2422 ,p_argument_value => p_effective_date
2423 );
2424 --
2425 hr_utility.set_location(l_proc, 40);
2426 irc_isc_bus.chk_person_id(
2427 p_person_id => p_rec.object_id
2428 ,p_effective_date => p_effective_date
2429 );
2430 --
2431 hr_utility.set_location(l_proc, 50);
2432 irc_isc_bus.chk_search_name(
2433 p_search_name => p_rec.search_name
2434 ,p_person_id => p_rec.object_id
2435 ,p_search_criteria_id => p_rec.search_criteria_id
2436 ,p_object_version_number => p_rec.object_version_number
2437 );
2438 --
2439 hr_utility.set_location(l_proc, 60);
2440 irc_isc_bus.chk_distance_to_location(
2441 p_distance_to_location => p_rec.distance_to_location
2442 ,p_geocode_location => p_rec.geocode_location
2443 ,p_search_criteria_id => p_rec.search_criteria_id
2444 ,p_object_version_number => p_rec.object_version_number
2445 );
2446 --
2447 hr_utility.set_location(l_proc, 63);
2448 irc_isc_bus.chk_location_id(
2449 p_location_id => p_rec.location_id
2450 ,p_search_criteria_id => p_rec.search_criteria_id
2451 ,p_object_version_number => p_rec.object_version_number
2452 );
2453 --
2454 hr_utility.set_location(l_proc, 66);
2455 irc_isc_bus.chk_longitude_latitude(
2456 p_longitude => p_rec.longitude
2457 ,p_latitude => p_rec.latitude
2458 ,p_search_criteria_id => p_rec.search_criteria_id
2459 ,p_object_version_number => p_rec.object_version_number
2460 );
2461 --
2462 hr_utility.set_location(l_proc, 70);
2463 irc_isc_bus.chk_use_for_matching(
2464 p_use_for_matching => p_rec.use_for_matching
2465 ,p_search_criteria_id => p_rec.search_criteria_id
2466 ,p_object_version_number => p_rec.object_version_number
2467 );
2468 --
2469 hr_utility.set_location(l_proc, 80);
2470 irc_isc_bus.chk_match_competence(
2471 p_match_competence => p_rec.match_competence
2472 ,p_search_criteria_id => p_rec.search_criteria_id
2473 ,p_object_version_number => p_rec.object_version_number
2474 );
2475 --
2476 hr_utility.set_location(l_proc, 90);
2477 irc_isc_bus.chk_match_qualification(
2478 p_match_qualification => p_rec.match_qualification
2479 ,p_search_criteria_id => p_rec.search_criteria_id
2480 ,p_object_version_number => p_rec.object_version_number
2481 );
2482 --
2483 hr_utility.set_location(l_proc, 100);
2484 irc_isc_bus.chk_min_salary(
2485 p_min_salary => p_rec.min_salary
2486 ,p_search_criteria_id => p_rec.search_criteria_id
2487 ,p_object_version_number => p_rec.object_version_number
2488 );
2489 --
2490 end if;
2491
2492 if p_rec.object_type= 'WPREF' then
2493 hr_utility.set_location(l_proc, 114);
2494 irc_isc_bus.chk_unique_work (
2495 p_object_id => p_rec.object_id
2496 );
2497 hr_utility.set_location(l_proc, 117);
2498 irc_isc_bus.chk_work_at_home(
2499 p_work_at_home => p_rec.work_at_home
2500 ,p_effective_date => p_effective_date
2501 ,p_search_criteria_id => p_rec.search_criteria_id
2502 ,p_object_version_number => p_rec.object_version_number
2503 );
2504 hr_utility.set_location(l_proc, 119);
2505 irc_isc_bus.chk_keywords(
2506 p_keywords => p_rec.keywords
2507 ,p_search_criteria_id => p_rec.search_criteria_id
2508 ,p_object_version_number => p_rec.object_version_number
2509 );
2510 end if;
2511 --
2512 hr_utility.set_location(l_proc, 110);
2513 if p_rec.object_type= 'VACANCY' then
2514 --
2515 hr_utility.set_location(l_proc, 70);
2516 irc_isc_bus.chk_vacancy_id(
2517 p_vacancy_id => p_rec.object_id
2518 ,p_effective_date => p_effective_date
2519 );
2520 --
2521 hr_utility.set_location(l_proc, 120);
2522 irc_isc_bus.chk_min_qual_level(
2523 p_min_qual_level => p_rec.min_qual_level
2524 ,p_search_criteria_id => p_rec.search_criteria_id
2525 ,p_object_version_number => p_rec.object_version_number
2526 );
2527 --
2528 hr_utility.set_location(l_proc, 130);
2529 irc_isc_bus.chk_min_salary(
2530 p_min_salary => p_rec.min_salary
2531 ,p_search_criteria_id => p_rec.search_criteria_id
2532 ,p_object_version_number => p_rec.object_version_number
2533 );
2534 --
2535 hr_utility.set_location(l_proc, 140);
2536 irc_isc_bus.chk_max_salary(
2537 p_max_salary => p_rec.max_salary
2538 ,p_min_salary => p_rec.min_salary
2539 ,p_search_criteria_id => p_rec.search_criteria_id
2540 ,p_object_version_number => p_rec.object_version_number
2541 );
2542 --
2543 hr_utility.set_location(l_proc, 145);
2544 irc_isc_bus.chk_qual_rank(
2545 p_min_qual_level => p_rec.min_qual_level
2546 ,p_max_qual_level => p_rec.max_qual_level
2547 );
2548 --
2549 hr_utility.set_location(l_proc, 147);
2550 irc_isc_bus.chk_employee_contractor(
2551 p_employee => p_rec.employee
2552 ,p_contractor => p_rec.contractor
2553 );
2554 --
2555 end if;
2556 --
2557 hr_utility.set_location(l_proc, 150);
2558 irc_isc_bus.chk_salary_currency
2559 (p_salary_currency => p_rec.salary_currency
2560 ,p_min_salary => p_rec.min_salary
2561 ,p_max_salary => p_rec.max_salary
2562 ,p_effective_date => p_effective_date
2563 ,p_search_criteria_id => p_rec.search_criteria_id
2564 ,p_object_version_number => p_rec.object_version_number
2565 );
2566 --
2567 --
2568 hr_utility.set_location(l_proc, 155);
2569 irc_isc_bus.chk_salary_period(
2570 p_salary_period => p_rec.salary_period
2571 ,p_min_salary => p_rec.min_salary
2572 ,p_max_salary => p_rec.max_salary
2573 ,p_effective_date => p_effective_date
2574 ,p_search_criteria_id => p_rec.search_criteria_id
2575 ,p_object_version_number => p_rec.object_version_number
2576 );
2577 --
2578 hr_utility.set_location(l_proc, 160);
2579 irc_isc_bus.chk_employee(
2580 p_employee => p_rec.employee
2581 ,p_search_criteria_id => p_rec.search_criteria_id
2582 ,p_object_version_number => p_rec.object_version_number
2583 );
2584 --
2585 hr_utility.set_location(l_proc, 170);
2586 irc_isc_bus.chk_contractor(
2587 p_contractor => p_rec.contractor
2588 ,p_search_criteria_id => p_rec.search_criteria_id
2589 ,p_object_version_number => p_rec.object_version_number
2590 );
2591 --
2592 hr_utility.set_location(l_proc, 180);
2593 irc_isc_bus.chk_professional_area(
2594 p_professional_area => p_rec.professional_area
2595 ,p_effective_date => p_effective_date
2596 ,p_search_criteria_id => p_rec.search_criteria_id
2597 ,p_object_version_number => p_rec.object_version_number
2598 );
2599 --
2600 hr_utility.set_location(l_proc, 190);
2601 irc_isc_bus.chk_employment_category(
2602 p_employment_category => p_rec.employment_category
2603 ,p_effective_date => p_effective_date
2604 ,p_search_criteria_id => p_rec.search_criteria_id
2605 ,p_object_version_number => p_rec.object_version_number
2606 );
2607 --
2608 hr_utility.set_location(l_proc, 200);
2609 irc_isc_bus.chk_work_at_home(
2610 p_work_at_home => p_rec.work_at_home
2611 ,p_effective_date => p_effective_date
2612 ,p_search_criteria_id => p_rec.search_criteria_id
2613 ,p_object_version_number => p_rec.object_version_number
2614 );
2615 --
2616 hr_utility.set_location(l_proc, 210);
2617 irc_isc_bus.chk_travel_percentage(
2618 p_travel_percentage => p_rec.travel_percentage
2619 ,p_effective_date => p_effective_date
2620 ,p_search_criteria_id => p_rec.search_criteria_id
2621 ,p_object_version_number => p_rec.object_version_number
2622 );
2623 --
2624 hr_utility.set_location(l_proc, 215);
2625 irc_isc_bus.chk_date_posted(
2626 p_date_posted => p_rec.date_posted
2627 ,p_effective_date => p_effective_date
2628 ,p_search_criteria_id => p_rec.search_criteria_id
2629 ,p_object_version_number => p_rec.object_version_number
2630 );
2631 --
2632 hr_utility.set_location(l_proc, 220);
2633 irc_isc_bus.chk_ddf(p_rec);
2634 --
2635 hr_utility.set_location(l_proc, 230);
2636 irc_isc_bus.chk_df(p_rec);
2637 --
2638 hr_utility.set_location(' Leaving:'||l_proc, 240);
2639 End insert_validate;
2640 --
2641 -- ----------------------------------------------------------------------------
2642 -- |---------------------------< update_validate >----------------------------|
2643 -- ----------------------------------------------------------------------------
2644 Procedure update_validate
2645 (p_effective_date in date
2646 ,p_rec in irc_isc_shd.g_rec_type
2647 ) is
2648 --
2649 l_proc varchar2(72) := g_package||'update_validate';
2650 --
2651 Begin
2652 hr_utility.set_location('Entering:'||l_proc, 10);
2653 --
2654 -- Validate Important Attributes
2655 --
2656 -- Call all supporting business operations
2657 --
2658 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
2659 -- After validating the set of important attributes,
2660 -- if Multiple message detection is enabled and at least
2661 -- one error has been found then abort further validation.
2662 --
2663 -- Validate Dependent Attributes
2664 --
2665 hr_utility.set_location(l_proc,15);
2666 chk_non_updateable_args
2667 (p_effective_date => p_effective_date
2668 ,p_rec => p_rec
2669 );
2670 --
2671 --
2672 hr_utility.set_location(l_proc, 20);
2673
2674 if p_rec.object_type in( 'PERSON','WPREF') then
2675 --
2676 hr_utility.set_location(l_proc, 30);
2677 irc_isc_bus.chk_search_name(
2678 p_search_name => p_rec.search_name
2679 ,p_person_id => p_rec.object_id
2680 ,p_search_criteria_id => p_rec.search_criteria_id
2681 ,p_object_version_number => p_rec.object_version_number
2682 );
2683 --
2684 hr_utility.set_location(l_proc, 40);
2685 irc_isc_bus.chk_distance_to_location(
2686 p_distance_to_location => p_rec.distance_to_location
2687 ,p_geocode_location => p_rec.geocode_location
2688 ,p_search_criteria_id => p_rec.search_criteria_id
2689 ,p_object_version_number => p_rec.object_version_number
2690 );
2691 --
2692 hr_utility.set_location(l_proc, 43);
2693 irc_isc_bus.chk_location_id(
2694 p_location_id => p_rec.location_id
2695 ,p_search_criteria_id => p_rec.search_criteria_id
2696 ,p_object_version_number => p_rec.object_version_number
2697 );
2698 --
2699 hr_utility.set_location(l_proc, 46);
2700 irc_isc_bus.chk_longitude_latitude(
2701 p_longitude => p_rec.longitude
2702 ,p_latitude => p_rec.latitude
2703 ,p_search_criteria_id => p_rec.search_criteria_id
2704 ,p_object_version_number => p_rec.object_version_number
2705 );
2706 --
2707 hr_utility.set_location(l_proc, 50);
2708 irc_isc_bus.chk_use_for_matching(
2709 p_use_for_matching => p_rec.use_for_matching
2710 ,p_search_criteria_id => p_rec.search_criteria_id
2711 ,p_object_version_number => p_rec.object_version_number
2712 );
2713 --
2714 hr_utility.set_location(l_proc, 60);
2715 irc_isc_bus.chk_match_competence(
2716 p_match_competence => p_rec.match_competence
2717 ,p_search_criteria_id => p_rec.search_criteria_id
2718 ,p_object_version_number => p_rec.object_version_number
2719 );
2720 --
2721 hr_utility.set_location(l_proc, 70);
2722 irc_isc_bus.chk_match_qualification(
2723 p_match_qualification => p_rec.match_qualification
2724 ,p_search_criteria_id => p_rec.search_criteria_id
2725 ,p_object_version_number => p_rec.object_version_number
2726 );
2727 --
2728 hr_utility.set_location(l_proc, 80);
2729 irc_isc_bus.chk_min_salary(
2730 p_min_salary => p_rec.min_salary
2731 ,p_search_criteria_id => p_rec.search_criteria_id
2732 ,p_object_version_number => p_rec.object_version_number
2733 );
2734 --
2735 hr_utility.set_location(l_proc, 85);
2736 irc_isc_bus.chk_date_posted(
2737 p_date_posted => p_rec.date_posted
2738 ,p_effective_date => p_effective_date
2739 ,p_search_criteria_id => p_rec.search_criteria_id
2740 ,p_object_version_number => p_rec.object_version_number
2741 );
2742 --
2743 end if;
2744 --
2745 if p_rec.object_type= 'WPREF' then
2746 hr_utility.set_location(l_proc, 87);
2747 irc_isc_bus.chk_work_at_home(
2748 p_work_at_home => p_rec.work_at_home
2749 ,p_effective_date => p_effective_date
2750 ,p_search_criteria_id => p_rec.search_criteria_id
2751 ,p_object_version_number => p_rec.object_version_number
2752 );
2753 --
2754 irc_isc_bus.chk_keywords(
2755 p_keywords => p_rec.keywords
2756 ,p_search_criteria_id => p_rec.search_criteria_id
2757 ,p_object_version_number => p_rec.object_version_number
2758 );
2759 end if;
2760 --
2761 hr_utility.set_location(l_proc, 90);
2762 if p_rec.object_type= 'VACANCY' then
2763 --
2764 hr_utility.set_location(l_proc, 100);
2765 irc_isc_bus.chk_min_qual_level(
2766 p_min_qual_level => p_rec.min_qual_level
2767 ,p_search_criteria_id => p_rec.search_criteria_id
2768 ,p_object_version_number => p_rec.object_version_number
2769 );
2770 --
2771 hr_utility.set_location(l_proc, 110);
2772 irc_isc_bus.chk_min_salary(
2773 p_min_salary => p_rec.min_salary
2774 ,p_search_criteria_id => p_rec.search_criteria_id
2775 ,p_object_version_number => p_rec.object_version_number
2776 );
2777 --
2778 hr_utility.set_location(l_proc, 120);
2779 irc_isc_bus.chk_max_salary(
2780 p_max_salary => p_rec.max_salary
2781 ,p_min_salary => p_rec.min_salary
2782 ,p_search_criteria_id => p_rec.search_criteria_id
2783 ,p_object_version_number => p_rec.object_version_number
2784 );
2785 --
2786 hr_utility.set_location(l_proc, 125);
2787 irc_isc_bus.chk_qual_rank(
2788 p_min_qual_level => p_rec.min_qual_level
2789 ,p_max_qual_level => p_rec.max_qual_level
2790 );
2791 --
2792 hr_utility.set_location(l_proc, 127);
2793 irc_isc_bus.chk_employee_contractor(
2794 p_employee => p_rec.employee
2795 ,p_contractor => p_rec.contractor
2796 );
2797 --
2798 end if;
2799 --
2800 hr_utility.set_location(l_proc, 130);
2801 irc_isc_bus.chk_salary_currency
2802 (p_salary_currency => p_rec.salary_currency
2803 ,p_min_salary => p_rec.min_salary
2804 ,p_max_salary => p_rec.max_salary
2805 ,p_effective_date => p_effective_date
2806 ,p_search_criteria_id => p_rec.search_criteria_id
2807 ,p_object_version_number => p_rec.object_version_number
2808 );
2809 --
2810 hr_utility.set_location(l_proc, 135);
2811 irc_isc_bus.chk_salary_period(
2812 p_salary_period => p_rec.salary_period
2813 ,p_min_salary => p_rec.min_salary
2814 ,p_max_salary => p_rec.max_salary
2815 ,p_effective_date => p_effective_date
2816 ,p_search_criteria_id => p_rec.search_criteria_id
2817 ,p_object_version_number => p_rec.object_version_number
2818 );
2819 --
2820 hr_utility.set_location(l_proc, 140);
2821 irc_isc_bus.chk_employee(
2822 p_employee => p_rec.employee
2823 ,p_search_criteria_id => p_rec.search_criteria_id
2824 ,p_object_version_number => p_rec.object_version_number
2825 );
2826 --
2827 hr_utility.set_location(l_proc, 150);
2828 irc_isc_bus.chk_contractor(
2829 p_contractor => p_rec.contractor
2830 ,p_search_criteria_id => p_rec.search_criteria_id
2831 ,p_object_version_number => p_rec.object_version_number
2832 );
2833 --
2834 hr_utility.set_location(l_proc, 160);
2835 irc_isc_bus.chk_professional_area(
2836 p_professional_area => p_rec.professional_area
2837 ,p_effective_date => p_effective_date
2838 ,p_search_criteria_id => p_rec.search_criteria_id
2839 ,p_object_version_number => p_rec.object_version_number
2840 );
2841 --
2842 hr_utility.set_location(l_proc, 170);
2843 irc_isc_bus.chk_employment_category(
2844 p_employment_category => p_rec.employment_category
2845 ,p_effective_date => p_effective_date
2846 ,p_search_criteria_id => p_rec.search_criteria_id
2847 ,p_object_version_number => p_rec.object_version_number
2848 );
2849 --
2850 hr_utility.set_location(l_proc, 180);
2851 irc_isc_bus.chk_work_at_home(
2852 p_work_at_home => p_rec.work_at_home
2853 ,p_effective_date => p_effective_date
2854 ,p_search_criteria_id => p_rec.search_criteria_id
2855 ,p_object_version_number => p_rec.object_version_number
2856 );
2857 --
2858 hr_utility.set_location(l_proc, 190);
2859 irc_isc_bus.chk_travel_percentage(
2860 p_travel_percentage => p_rec.travel_percentage
2861 ,p_effective_date => p_effective_date
2862 ,p_search_criteria_id => p_rec.search_criteria_id
2863 ,p_object_version_number => p_rec.object_version_number
2864 );
2865 --
2866 hr_utility.set_location(l_proc, 200);
2867 irc_isc_bus.chk_ddf(p_rec);
2868 --
2869 hr_utility.set_location(l_proc, 210);
2870 irc_isc_bus.chk_df(p_rec);
2871 --
2872 hr_utility.set_location(' Leaving:'||l_proc, 10);
2873 End update_validate;
2874 --
2875 -- ----------------------------------------------------------------------------
2876 -- |---------------------------< delete_validate >----------------------------|
2877 -- ----------------------------------------------------------------------------
2878 Procedure delete_validate
2879 (p_rec in irc_isc_shd.g_rec_type
2880 ) is
2881 --
2882 l_proc varchar2(72) := g_package||'delete_validate';
2883 --
2884 Begin
2885 hr_utility.set_location('Entering:'||l_proc, 5);
2886 --
2887 -- Call all supporting business operations
2888 --
2889 hr_utility.set_location(' Leaving:'||l_proc, 10);
2890 End delete_validate;
2891 --
2892 end irc_isc_bus;