1 Package Body irc_irt_bus as
2 /* $Header: irirtrhi.pkb 120.0 2005/07/26 15:10 mbocutt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_irt_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_recruiting_site_id number default null;
14 g_language varchar2(4) default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |-----------------------< chk_non_updateable_args >------------------------|
18 -- ----------------------------------------------------------------------------
19 -- {Start Of Comments}
20 --
21 -- Description:
22 -- This procedure is used to ensure that non updateable attributes have
23 -- not been updated. If an attribute has been updated an error is generated.
24 --
25 -- Pre Conditions:
26 -- g_old_rec has been populated with details of the values currently in
27 -- the database.
28 --
29 -- In Arguments:
30 -- p_rec has been populated with the updated values the user would like the
31 -- record set to.
32 --
33 -- Post Success:
34 -- Processing continues if all the non updateable attributes have not
35 -- changed.
36 --
37 -- Post Failure:
38 -- An application error is raised if any of the non updatable attributes
39 -- have been altered.
40 --
41 -- {End Of Comments}
42 -- ----------------------------------------------------------------------------
43 Procedure chk_non_updateable_args
44 (p_rec in irc_irt_shd.g_rec_type
45 ) IS
46 --
47 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
48 --
49 Begin
50 --
51 -- Only proceed with the validation if a row exists for the current
52 -- record in the HR Schema.
53 --
54 IF NOT irc_irt_shd.api_updating
55 (p_recruiting_site_id => p_rec.recruiting_site_id
56 ,p_language => p_rec.language
57 ) THEN
58 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
59 fnd_message.set_token('PROCEDURE ', l_proc);
60 fnd_message.set_token('STEP ', '5');
61 fnd_message.raise_error;
62 END IF;
63 --
64 End chk_non_updateable_args;
65 --
66 -- ----------------------------------------------------------------------------
67 -- |---------------------------< chk_source_lang >----------------------------|
68 -- ----------------------------------------------------------------------------
69 Procedure chk_source_lang
70 (p_language in varchar2
71 ,p_source_lang in varchar2
72 ) is
73 --
74 cursor csr_language is
75 select l.installed_flag
76 from fnd_languages l
77 where l.language_code = p_source_lang;
78 --
79 l_proc varchar2(72) := g_package || 'chk_source_lang';
80 l_installed_flag varchar2(30);
81 --
82 Begin
83 hr_utility.set_location('Entering:'||l_proc, 10);
84 --
85 -- Only proceed with SQL validation if absolutely necessary
86 --
87
88 if ((irc_irt_shd.g_old_rec.recruiting_site_id is null and p_language is not null)
89 or (irc_irt_shd.g_old_rec.recruiting_site_id is not null
90 and nvl(irc_irt_shd.g_old_rec.language, hr_api.g_varchar2)
91 <> nvl(p_language, hr_api.g_varchar2))) then
92 --
93 hr_utility.set_location(l_proc,30);
94 --
95 -- Check value has been passed
96 --
97 hr_api.mandatory_arg_error
98 (p_api_name => l_proc
99 ,p_argument => 'source_lang'
100 ,p_argument_value => p_source_lang
101 );
102 --
103 hr_utility.set_location(l_proc,40);
104 --
105 -- Check source language exists and is base or installed language
106 --
107 open csr_language;
108 fetch csr_language into l_installed_flag;
109 if csr_language%notfound then
110 close csr_language;
111 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
112 fnd_message.set_token('PROCEDURE', l_proc);
113 fnd_message.set_token('STEP','10');
114 fnd_message.raise_error;
115 end if;
116 close csr_language;
117 --
118 if nvl(l_installed_flag,hr_api.g_varchar2) not in ('I','B') then
119 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
120 fnd_message.set_token('PROCEDURE', l_proc);
121 fnd_message.set_token('STEP','20');
122 fnd_message.raise_error;
123 end if;
124 --
125 end if;
126 --
127 hr_utility.set_location('Leaving:'||l_proc, 100);
128 End chk_source_lang;
129 -- ----------------------------------------------------------------------------
130 -- |-----------------------< chk_site_name>-----------------------------------|
131 -- ----------------------------------------------------------------------------
132 -- {Start Of Comments}
133 --
134 -- Description:
135 -- This procedure ensures a valid Site name is entered
136 -- Pre Conditions:
137 -- g_old_rec has been populated with details of the values currently in
138 -- the database.
139 --
140 -- In Arguments:
141 -- p_site_name
142 -- p_language
143 -- Post Success:
144 -- Processing continues if site name is not null and unique
145 --
146 -- Post Failure:
147 -- An application error is raised if site name is null or exists already
148 --
149 -- {End Of Comments}
150 -- ----------------------------------------------------------------------------
151 Procedure chk_site_name
152 (p_site_name in irc_all_recruiting_sites_tl.site_name%TYPE
153 ,p_language in irc_all_recruiting_sites_tl.language%TYPE
154 ) IS
155 --
156 l_proc varchar2(72) := g_package || 'chk_site_name';
157 l_name varchar2(1);
158 cursor csr_name is
159 select null
160 from irc_all_recruiting_sites_tl
161 where site_name = p_site_name
162 and language = p_language;
163 --
164 Begin
165 hr_utility.set_location('Entering:'||l_proc,10);
166 if (p_site_name is null)
167 then
168 fnd_message.set_name('PER','IRC_412104_RSE_NAME_NULL');
169 fnd_message.raise_error;
170 end if;
171 hr_utility.set_location('Entering:'||l_proc,20);
172 if ((irc_irt_shd.g_old_rec.recruiting_site_id is
173 null)
174 or (irc_irt_shd.g_old_rec.recruiting_site_id is not null
175 and irc_irt_shd.g_old_rec.site_name <> p_site_name)) then
176 open csr_name;
177 fetch csr_name into l_name;
178 hr_utility.set_location('Entering:'||l_proc,30);
179 if (csr_name%found)
180 then
181 close csr_name;
182 fnd_message.set_name('PER','IRC_412105_RSE_NAME_EXIST');
183 fnd_message.raise_error;
184 end if;
185 close csr_name;
186 end if;
187 hr_utility.set_location(' Leaving:'||l_proc,35);
188 exception
189 when app_exception.application_exception then
190 if hr_multi_message.exception_add
191 (p_associated_column1 => 'irc_all_recruiting_sites_tl.site_name'
192 )then
193 hr_utility.set_location(' Leaving:'||l_proc, 40);
194 raise;
195 end if;
196 hr_utility.set_location(' Leaving:'||l_proc,50);
197 End chk_site_name;
198 --
199 --
200 -- ----------------------------------------------------------------------------
201 -- |-------------------------< chk_redirection_url >--------------------------|
202 -- ----------------------------------------------------------------------------
203 -- {Start Of Comments}
204 --
205 -- Description:
206 -- This procedure ensures that REDIRECTION_URL is null if THIRD_PARTY = 'Y'
207 --
208 -- Pre Conditions:
209 -- g_old_rec has been populated with details of the values currently in
210 -- the database.
211 --
212 -- In Arguments:
213 -- p_redirection_url
214 -- p_recruiting_site_id
215 --
216 -- Post Success:
217 -- Processing continues if redirection_url is valid.
218 --
219 -- Post Failure:
220 -- An application error is raised if redirection_url is invalid.
221 --
222 -- {End Of Comments}
223 -- ----------------------------------------------------------------------------
224 Procedure chk_redirection_url
225 (p_redirection_url in varchar2
226 ,p_recruiting_site_id in irc_all_recruiting_sites_tl.recruiting_site_id%TYPE
227 ) IS
228 --
229 l_proc varchar2(72) := g_package || 'chk_redirection_url';
230 --
231 l_third_party irc_all_recruiting_sites.third_party%TYPE;
232 cursor csr_third_party
233 is select third_party
234 from irc_all_recruiting_sites
235 where recruiting_site_id = p_recruiting_site_id;
236 --
237 begin
238 hr_utility.set_location('Entering:'||l_proc,10);
239 --
240 -- Continue if updating and either the third party or redirection_url fields
241 -- have been updated, or if inserting.
242 --
243 hr_utility.set_location(l_proc,20);
244 --
245 if ((irc_irt_shd.g_old_rec.recruiting_site_id is
246 null)
247 or (irc_irt_shd.g_old_rec.recruiting_site_id is not null
248 and nvl(irc_irt_shd.g_old_rec.redirection_url,hr_api.g_varchar2)
249 <> nvl(p_redirection_url,hr_api.g_varchar2)))
250 then
251 hr_utility.set_location(l_proc,25);
252 open csr_third_party;
253 fetch csr_third_party into l_third_party;
254 close csr_third_party;
255 --
256 if (l_third_party = 'Y'
257 and p_redirection_url is not null) then
258 hr_utility.set_location(l_proc,60);
259 fnd_message.set_name('PER','IRC_412096_BAD_TP_REDIR_URL');
260 fnd_message.raise_error;
261 end if;
262 end if;
263 hr_utility.set_location(' Leaving:'||l_proc,70);
264 exception
265 when app_exception.application_exception then
266 if hr_multi_message.exception_add
267 (p_associated_column1 =>
268 'IRC_ALL_RECRUITING_SITES_TL.REDIRECTION_URL'
269 ) then
270 hr_utility.set_location(' Leaving:'||l_proc,80);
271 raise;
272 end if;
273 hr_utility.set_location(' Leaving:'||l_proc,90);
274 end chk_redirection_url;
275 --
276 --
277 -- ----------------------------------------------------------------------------
278 -- |-------------------------< chk_posting_url >--------------------------|
279 -- ----------------------------------------------------------------------------
280 -- {Start Of Comments}
281 --
282 -- Description:
283 -- This procedure ensures that POSTING_URL is null if THIRD_PARTY = 'N'
284 -- and not null if THIRD_PARTY = 'Y'
285 --
286 -- Pre Conditions:
287 -- g_old_rec has been populated with details of the values currently in
288 -- the database.
289 --
290 -- In Arguments:
291 -- p_posting_url
292 -- p_recruiting_site_id
293 --
294 -- Post Success:
295 -- Processing continues if redirection_url is valid.
296 --
297 -- Post Failure:
298 -- An application error is raised if redirection_url is invalid.
299 --
300 -- {End Of Comments}
301 -- ----------------------------------------------------------------------------
302 Procedure chk_posting_url
303 (p_posting_url in varchar2
304 ,p_recruiting_site_id in irc_all_recruiting_sites_tl.recruiting_site_id%TYPE
305 ) IS
306 --
307 l_proc varchar2(72) := g_package || 'chk_posting_url';
308 --
309 l_third_party irc_all_recruiting_sites.third_party%TYPE;
310 cursor csr_third_party
311 is select third_party
312 from irc_all_recruiting_sites
313 where recruiting_site_id = p_recruiting_site_id;
314 --
315 begin
316 hr_utility.set_location('Entering:'||l_proc,10);
317 --
318 -- Continue if updating and either the third party or redirection_url fields
319 -- have been updated, or if inserting.
320 --
321 hr_utility.set_location(l_proc,20);
322 --
323 if ((irc_irt_shd.g_old_rec.recruiting_site_id is
324 null)
325 or (irc_irt_shd.g_old_rec.recruiting_site_id is not null
326 and nvl(irc_irt_shd.g_old_rec.posting_url,hr_api.g_varchar2)
327 <> nvl(p_posting_url,hr_api.g_varchar2)))
328 then
329 hr_utility.set_location(l_proc,25);
330 open csr_third_party;
331 fetch csr_third_party into l_third_party;
332 close csr_third_party;
333 --
334 if ( ( l_third_party = 'N'
335 and p_posting_url is not null)
336 or ( l_third_party = 'Y'
337 and p_posting_url is null)
338 )
339 then
340 hr_utility.set_location(l_proc,60);
341 fnd_message.set_name('PER','IRC_412097_BAD_TP_POSTING_URL');
342 fnd_message.raise_error;
343 end if;
344 end if;
345 hr_utility.set_location(' Leaving:'||l_proc,70);
346 exception
347 when app_exception.application_exception then
348 if hr_multi_message.exception_add
349 (p_associated_column1 =>
350 'IRC_ALL_RECRUITING_SITES_TL.POSTING_URL'
351 ) then
352 hr_utility.set_location(' Leaving:'||l_proc,80);
353 raise;
354 end if;
355 hr_utility.set_location(' Leaving:'||l_proc,90);
356 end chk_posting_url;
357 --
358 --
359 -- ----------------------------------------------------------------------------
360 -- |---------------------------< insert_validate >----------------------------|
361 -- ----------------------------------------------------------------------------
362 Procedure insert_validate
363 (p_rec in irc_irt_shd.g_rec_type
364 ,p_recruiting_site_id in irc_all_recruiting_sites_tl.RECRUITING_SITE_ID%type
365 ) is
366 --
367 l_proc varchar2(72) := g_package||'insert_validate';
368 --
369 Begin
370 hr_utility.set_location('Entering:'||l_proc, 5);
371 --
372 -- Call all supporting business operations
373 --
374 irc_irt_bus.chk_source_lang
375 (
376 p_language => p_rec.language
377 ,p_source_lang => p_rec.source_lang
378 );
379 hr_utility.set_location(l_proc, 10);
380 irc_irt_bus.chk_site_name(
381 p_site_name => p_rec.site_name
382 ,p_language => p_rec.language
383 );
384 hr_utility.set_location(l_proc, 20);
385 irc_irt_bus.chk_redirection_url
386 (
387 p_redirection_url => p_rec.redirection_url
388 ,p_recruiting_site_id => p_recruiting_site_id
389 );
390 hr_utility.set_location(l_proc, 30);
391 irc_irt_bus.chk_posting_url
392 (
393 p_posting_url => p_rec.posting_url
394 ,p_recruiting_site_id => p_recruiting_site_id
395 );
396 hr_utility.set_location(l_proc, 40);
397 --
398 hr_utility.set_location(' Leaving:'||l_proc, 50);
399 End insert_validate;
400 --
401 -- ----------------------------------------------------------------------------
402 -- |---------------------------< update_validate >----------------------------|
403 -- ----------------------------------------------------------------------------
404 Procedure update_validate
405 (p_rec in irc_irt_shd.g_rec_type
406 ) is
407 --
408 l_proc varchar2(72) := g_package||'update_validate';
409 --
410 Begin
411 hr_utility.set_location('Entering:'||l_proc, 5);
412 --
413 -- Call all supporting business operations
414 -- Validate Dependent Attributes
415 --
416 chk_non_updateable_args
417 (p_rec => p_rec
418 );
419 --
420 irc_irt_bus.chk_source_lang
421 (
422 p_language => p_rec.language
423 ,p_source_lang => p_rec.source_lang
424 );
425 hr_utility.set_location(l_proc, 10);
426 irc_irt_bus.chk_site_name(
427 p_site_name => p_rec.site_name
428 ,p_language => p_rec.language
429 );
430 hr_utility.set_location(l_proc, 20);
431 irc_irt_bus.chk_redirection_url
432 (
433 p_redirection_url => p_rec.redirection_url
434 ,p_recruiting_site_id => p_rec.recruiting_site_id
435 );
436 hr_utility.set_location(l_proc, 30);
437 irc_irt_bus.chk_posting_url
438 (
439 p_posting_url => p_rec.posting_url
440 ,p_recruiting_site_id => p_rec.recruiting_site_id
441 );
442 hr_utility.set_location(l_proc, 40);
443 --
444 hr_utility.set_location(' Leaving:'||l_proc, 50);
445 End update_validate;
446 --
447 -- ----------------------------------------------------------------------------
448 -- |---------------------------< delete_validate >----------------------------|
449 -- ----------------------------------------------------------------------------
450 Procedure delete_validate
451 (p_rec in irc_irt_shd.g_rec_type
452 ) is
453 --
454 l_proc varchar2(72) := g_package||'delete_validate';
455 --
456 Begin
457 hr_utility.set_location('Entering:'||l_proc, 5);
458 --
459 -- Call all supporting business operations
460 --
461 hr_utility.set_location(' Leaving:'||l_proc, 10);
462 End delete_validate;
463 --
464 end irc_irt_bus;