[Home] [Help]
PACKAGE BODY: APPS.PER_JOB_BUS
Source
1 Package Body per_job_bus as
2 /* $Header: pejobrhi.pkb 120.0.12010000.2 2009/05/12 06:16:11 varanjan ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_job_bus.'; -- Global package name
9 --
10 --
11 -- The following two global variables are only to be
12 -- used by the return_legislation_code function.
13 --
14 g_legislation_code varchar2(150) default null;
15 g_job_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |--------------------< chk_job_definition_id >---------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 -- Desciption :
22 --
23 -- Validates that JOB_DEFINITION_ID is not null
24 --
25 -- Validates that JOB_DEFINITION_ID in the PER_JOB_DEFINITIONS table
26 -- exists for the record specified by JOB_DEFINITION_ID.
27 -- Validate that JOB_DEFINITION_ID is unique for each business group.
28 --
29 -- Pre-conditions:
30 -- None.
31 --
32 -- In Arguments :
33 -- p_job_definition_id
34 -- p_business_group_id
35 -- p_job_id
36 -- p_object_version_number
37 --
38 -- Post Success :
39 -- If the above business rules are satisfied, processing continues
40 --
41 -- Post Failure :
42 -- If the above business rules are violated, an application error
43 -- is raised and processing terminates
44 --
45 -- Access Status :
46 -- Internal Table Handler Use only.
47 --
48 -- {End of Comments}
49 --
50 -- -----------------------------------------------------------------------
51 procedure chk_job_definition_id
52 (p_job_definition_id in number,
53 p_business_group_id in number,
54 p_job_id in number default null,
55 p_object_version_number in number default null
56 ) is
57 --
58 l_proc varchar2(72) := g_package||'chk_job_definition_id';
59 l_exists varchar2(1);
60 l_api_updating boolean;
61 --
62 cursor csr_job_def is
63 select 'x'
64 from per_job_definitions
65 where job_definition_id = p_job_definition_id;
66 --
67 cursor csr_unique_job_def is
68 select 'x'
69 from per_jobs
70 where job_definition_id = p_job_definition_id
71 and business_group_id + 0 = p_business_group_id;
72 --
73 begin
74 hr_utility.set_location('Entering:'||l_proc, 1);
75 --
76 -- Check mandatory parameters have been set
77 --
78 hr_api.mandatory_arg_error
79 (p_api_name => l_proc
80 ,p_argument => 'job_definition_id'
81 ,p_argument_value => p_job_definition_id
82 );
83 --
84 hr_utility.set_location(l_proc, 2);
85 --
86 l_api_updating := per_job_shd.api_updating
87 (p_job_id => p_job_id
88 ,p_object_version_number => p_object_version_number);
89 --
90 hr_utility.set_location(l_proc, 3);
91 --
92 if ((l_api_updating and
93 (per_job_shd.g_old_rec.job_definition_id <>
94 p_job_definition_id)) or (NOT l_api_updating)) then
95 --
96 hr_utility.set_location(l_proc, 4);
97 --
98 open csr_job_def;
99 fetch csr_job_def into l_exists;
100 if csr_job_def%notfound then
101 close csr_job_def;
102 per_job_shd.constraint_error(p_constraint_name => 'PER_JOBS_FK2');
103 end if;
104 close csr_job_def;
105 --
106 hr_utility.set_location(l_proc, 5);
107 --
108 open csr_unique_job_def;
109 fetch csr_unique_job_def into l_exists;
110 if csr_unique_job_def%found then
111 close csr_unique_job_def;
112 hr_utility.set_message(801,'PER_7810_DEF_JOB_EXISTS');
113 hr_utility.raise_error;
114 end if;
115 close csr_unique_job_def;
116 --
117 end if;
118 hr_utility.set_location('Leaving '||l_proc, 6);
119 --
120 end chk_job_definition_id;
121 --
122 -- ---------------------------------------------------------------------------
123 -- |---------------------------< chk_dates >--------------------------------|
124 -- ---------------------------------------------------------------------------
125 --
126 -- Desciption :
127 --
128 -- Validates DATE_FROM is not null
129 --
130 -- Validates that DATE_FROM is less than or equal to the value for
131 -- DATE_TO on the same JOB record
132 --
133 -- Pre-conditions:
134 -- Format of p_date_effective must be correct
135 --
136 -- In Arguments :
137 -- p_job_id
138 -- p_date_from
139 -- p_date_to
140 -- p_object_version_number
141 --
142 -- Post Success :
143 -- If the above business rules are satisfied, processing continues
144 --
145 -- Post Failure :
146 -- If the above business rules are violated, an application error
147 -- is raised and processing terminates
148 --
149 -- Access Status :
150 -- Internal Table Handler Use only.
151 --
152 -- {End of Comments}
153 --
154 -- ---------------------------------------------------------------------------
155 procedure chk_dates
156 (p_job_id in number default null
157 ,p_date_from in date
158 ,p_date_to in date
159 ,p_object_version_number in number default null) is
160 --
161 l_proc varchar2(72) := g_package||'chk_dates';
162 l_api_updating boolean;
163 --
164 begin
165 hr_utility.set_location('Entering:'||l_proc, 1);
166 --
167 -- Check mandatory parameters have been set
168 --
169
170 hr_api.mandatory_arg_error
171 (p_api_name => l_proc
172 ,p_argument => 'date_from'
173 ,p_argument_value => p_date_from
174 );
175 hr_utility.set_location(l_proc, 2);
176 --
177 -- Only proceed with validation if :
178 -- a) The current g_old_rec is current and
179 -- b) The date_end value has changed
180 --
181 l_api_updating := per_job_shd.api_updating
182 (p_job_id => p_job_id
183 ,p_object_version_number => p_object_version_number);
184 --
185 if (((l_api_updating and
186 (nvl(per_job_shd.g_old_rec.date_to,hr_api.g_eot) <>
187 nvl(p_date_to,hr_api.g_eot)) or
188 (per_job_shd.g_old_rec.date_from <> p_date_from)) or
189 (NOT l_api_updating))) then
190 --
191 -- Check that date_from <= date_to
192 --
193 hr_utility.set_location(l_proc, 3);
194 --
195 if p_date_from > nvl(p_date_to,hr_api.g_eot) then
196 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
197 hr_utility.set_message_token('PROCEDURE', l_proc);
198 hr_utility.set_message_token('STEP', '3');
199 hr_utility.raise_error;
200 end if;
201 --
202 end if;
203 --
204 hr_utility.set_location(' Leaving:'||l_proc, 4);
205 end chk_dates;
206 --
207 -- -----------------------------------------------------------------------
208 -- |------------------------------< chk_df >-----------------------------|
209 -- -----------------------------------------------------------------------
210 --
211 -- Description:
212 -- Validates the all Descriptive Flexfield values.
213 --
214 -- Pre-conditions:
215 -- All other columns have been validated. Must be called as the
216 -- last step from insert_validate and update_validate.
217 --
218 -- In Arguments:
219 -- p_rec
220 --
221 -- Post Success:
222 -- If the Descriptive Flexfield structure column and data values are
223 -- all valid this procedure will end normally and processing will
224 -- continue.
225 --
226 -- Post Failure:
227 -- If the Descriptive Flexfield structure column value or any of
228 -- the data values are invalid then an application error is raised as
229 -- a PL/SQL exception.
230 --
231 -- Access Status:
232 -- Internal Row Handler Use Only.
233 --
234 procedure chk_df
235 (p_rec in per_job_shd.g_rec_type) is
236 --
237 l_proc varchar2(72) := g_package||'chk_df';
238 --
239 begin
240 hr_utility.set_location('Entering:'||l_proc, 10);
241 --
242 if ((p_rec.job_id is not null) and (
243 nvl(per_job_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
244 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
245 nvl(per_job_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
246 nvl(p_rec.attribute1, hr_api.g_varchar2) or
247 nvl(per_job_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
248 nvl(p_rec.attribute2, hr_api.g_varchar2) or
249 nvl(per_job_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
250 nvl(p_rec.attribute3, hr_api.g_varchar2) or
251 nvl(per_job_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
252 nvl(p_rec.attribute4, hr_api.g_varchar2) or
253 nvl(per_job_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
254 nvl(p_rec.attribute5, hr_api.g_varchar2) or
255 nvl(per_job_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
256 nvl(p_rec.attribute6, hr_api.g_varchar2) or
257 nvl(per_job_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
258 nvl(p_rec.attribute7, hr_api.g_varchar2) or
259 nvl(per_job_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
260 nvl(p_rec.attribute8, hr_api.g_varchar2) or
261 nvl(per_job_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
262 nvl(p_rec.attribute9, hr_api.g_varchar2) or
263 nvl(per_job_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
264 nvl(p_rec.attribute10, hr_api.g_varchar2) or
265 nvl(per_job_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
266 nvl(p_rec.attribute11, hr_api.g_varchar2) or
267 nvl(per_job_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
268 nvl(p_rec.attribute12, hr_api.g_varchar2) or
269 nvl(per_job_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
270 nvl(p_rec.attribute13, hr_api.g_varchar2) or
271 nvl(per_job_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
272 nvl(p_rec.attribute14, hr_api.g_varchar2) or
273 nvl(per_job_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
274 nvl(p_rec.attribute15, hr_api.g_varchar2) or
275 nvl(per_job_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
276 nvl(p_rec.attribute16, hr_api.g_varchar2) or
277 nvl(per_job_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
278 nvl(p_rec.attribute17, hr_api.g_varchar2) or
279 nvl(per_job_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
280 nvl(p_rec.attribute18, hr_api.g_varchar2) or
281 nvl(per_job_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
282 nvl(p_rec.attribute19, hr_api.g_varchar2) or
283 nvl(per_job_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
284 nvl(p_rec.attribute20, hr_api.g_varchar2)))
285 or
286 (p_rec.job_id is null) then
287 --
288 -- Only execute the validation if absolutely necessary:
289 -- a) During update, the structure column value or any
290 -- of the attribute values have actually changed.
291 -- b) During insert.
292 --
293 hr_dflex_utility.ins_or_upd_descflex_attribs
294 (p_appl_short_name => 'PER'
295 ,p_descflex_name => 'PER_JOBS'
296 ,p_attribute_category => p_rec.attribute_category
297 ,p_attribute1_name => 'ATTRIBUTE1'
298 ,p_attribute1_value => p_rec.attribute1
299 ,p_attribute2_name => 'ATTRIBUTE2'
300 ,p_attribute2_value => p_rec.attribute2
301 ,p_attribute3_name => 'ATTRIBUTE3'
302 ,p_attribute3_value => p_rec.attribute3
303 ,p_attribute4_name => 'ATTRIBUTE4'
304 ,p_attribute4_value => p_rec.attribute4
305 ,p_attribute5_name => 'ATTRIBUTE5'
306 ,p_attribute5_value => p_rec.attribute5
307 ,p_attribute6_name => 'ATTRIBUTE6'
308 ,p_attribute6_value => p_rec.attribute6
309 ,p_attribute7_name => 'ATTRIBUTE7'
310 ,p_attribute7_value => p_rec.attribute7
311 ,p_attribute8_name => 'ATTRIBUTE8'
312 ,p_attribute8_value => p_rec.attribute8
313 ,p_attribute9_name => 'ATTRIBUTE9'
314 ,p_attribute9_value => p_rec.attribute9
315 ,p_attribute10_name => 'ATTRIBUTE10'
316 ,p_attribute10_value => p_rec.attribute10
317 ,p_attribute11_name => 'ATTRIBUTE11'
318 ,p_attribute11_value => p_rec.attribute11
319 ,p_attribute12_name => 'ATTRIBUTE12'
320 ,p_attribute12_value => p_rec.attribute12
321 ,p_attribute13_name => 'ATTRIBUTE13'
322 ,p_attribute13_value => p_rec.attribute13
323 ,p_attribute14_name => 'ATTRIBUTE14'
324 ,p_attribute14_value => p_rec.attribute14
325 ,p_attribute15_name => 'ATTRIBUTE15'
326 ,p_attribute15_value => p_rec.attribute15
327 ,p_attribute16_name => 'ATTRIBUTE16'
328 ,p_attribute16_value => p_rec.attribute16
329 ,p_attribute17_name => 'ATTRIBUTE17'
330 ,p_attribute17_value => p_rec.attribute17
331 ,p_attribute18_name => 'ATTRIBUTE18'
332 ,p_attribute18_value => p_rec.attribute18
333 ,p_attribute19_name => 'ATTRIBUTE19'
334 ,p_attribute19_value => p_rec.attribute19
335 ,p_attribute20_name => 'ATTRIBUTE20'
336 ,p_attribute20_value => p_rec.attribute20
337 );
338 end if;
339 --
340 hr_utility.set_location(' Leaving:'||l_proc, 20);
341 end chk_df;
342 --
343 -- -----------------------------------------------------------------------
344 -- |------------------------------< chk_ddf >----------------------------|
345 -- -----------------------------------------------------------------------
346 --
347 -- Description:
348 -- Validates the all Developer Descriptive Flexfield values.
349 --
350 -- Pre-conditions:
351 -- All other columns have been validated. Must be called as the
352 -- last step from insert_validate and update_validate.
353 --
354 -- In Arguments:
355 -- p_rec
356 --
357 -- Post Success:
358 -- If the Developer Descriptive Flexfield structure column and data values
359 -- are all valid this procedure will end normally and processing will
360 -- continue.
361 --
362 -- Post Failure:
363 -- If the Developer Descriptive Flexfield structure column value or any of
364 -- the data values are invalid then an application error is raised as
365 -- a PL/SQL exception.
366 --
367 -- Access Status:
368 -- Internal Row Handler Use Only.
369 --
370 procedure chk_ddf
371 (p_rec in per_job_shd.g_rec_type) is
372 --
373 l_proc varchar2(72) := g_package||'chk_ddf';
374 --
375 begin
376 hr_utility.set_location('Entering:'||l_proc, 10);
377 --
378 if ((p_rec.job_id is not null) and (
379 nvl(per_job_shd.g_old_rec.job_information_category, hr_api.g_varchar2) <>
380 nvl(p_rec.job_information_category, hr_api.g_varchar2) or
381 nvl(per_job_shd.g_old_rec.job_information1, hr_api.g_varchar2) <>
382 nvl(p_rec.job_information1, hr_api.g_varchar2) or
383 nvl(per_job_shd.g_old_rec.job_information2, hr_api.g_varchar2) <>
384 nvl(p_rec.job_information2, hr_api.g_varchar2) or
385 nvl(per_job_shd.g_old_rec.job_information3, hr_api.g_varchar2) <>
386 nvl(p_rec.job_information3, hr_api.g_varchar2) or
387 nvl(per_job_shd.g_old_rec.job_information4, hr_api.g_varchar2) <>
388 nvl(p_rec.job_information4, hr_api.g_varchar2) or
389 nvl(per_job_shd.g_old_rec.job_information5, hr_api.g_varchar2) <>
390 nvl(p_rec.job_information5, hr_api.g_varchar2) or
391 nvl(per_job_shd.g_old_rec.job_information6, hr_api.g_varchar2) <>
392 nvl(p_rec.job_information6, hr_api.g_varchar2) or
393 nvl(per_job_shd.g_old_rec.job_information7, hr_api.g_varchar2) <>
394 nvl(p_rec.job_information7, hr_api.g_varchar2) or
395 nvl(per_job_shd.g_old_rec.job_information8, hr_api.g_varchar2) <>
396 nvl(p_rec.job_information8, hr_api.g_varchar2) or
397 nvl(per_job_shd.g_old_rec.job_information9, hr_api.g_varchar2) <>
398 nvl(p_rec.job_information9, hr_api.g_varchar2) or
399 nvl(per_job_shd.g_old_rec.job_information10, hr_api.g_varchar2) <>
400 nvl(p_rec.job_information10, hr_api.g_varchar2) or
401 nvl(per_job_shd.g_old_rec.job_information11, hr_api.g_varchar2) <>
402 nvl(p_rec.job_information11, hr_api.g_varchar2) or
403 nvl(per_job_shd.g_old_rec.job_information12, hr_api.g_varchar2) <>
404 nvl(p_rec.job_information12, hr_api.g_varchar2) or
405 nvl(per_job_shd.g_old_rec.job_information13, hr_api.g_varchar2) <>
406 nvl(p_rec.job_information13, hr_api.g_varchar2) or
407 nvl(per_job_shd.g_old_rec.job_information14, hr_api.g_varchar2) <>
408 nvl(p_rec.job_information14, hr_api.g_varchar2) or
409 nvl(per_job_shd.g_old_rec.job_information15, hr_api.g_varchar2) <>
410 nvl(p_rec.job_information15, hr_api.g_varchar2) or
411 nvl(per_job_shd.g_old_rec.job_information16, hr_api.g_varchar2) <>
412 nvl(p_rec.job_information16, hr_api.g_varchar2) or
413 nvl(per_job_shd.g_old_rec.job_information17, hr_api.g_varchar2) <>
414 nvl(p_rec.job_information17, hr_api.g_varchar2) or
415 nvl(per_job_shd.g_old_rec.job_information18, hr_api.g_varchar2) <>
416 nvl(p_rec.job_information18, hr_api.g_varchar2) or
417 nvl(per_job_shd.g_old_rec.job_information19, hr_api.g_varchar2) <>
418 nvl(p_rec.job_information19, hr_api.g_varchar2) or
419 nvl(per_job_shd.g_old_rec.job_information20, hr_api.g_varchar2) <>
420 nvl(p_rec.job_information20, hr_api.g_varchar2)))
421 or
422 (p_rec.job_id is null) then
423 --
424 -- Only execute the validation if absolutely necessary:
425 -- a) During update, the structure column value or any
426 -- of the job_information values have actually changed.
427 -- b) During insert.
428 --
429 hr_dflex_utility.ins_or_upd_descflex_attribs
430 (p_appl_short_name => 'PER'
431 ,p_descflex_name => 'Job Developer DF'
432 ,p_attribute_category => p_rec.job_information_category
433 ,p_attribute1_name => 'JOB_INFORMATION1'
434 ,p_attribute1_value => p_rec.job_information1
435 ,p_attribute2_name => 'JOB_INFORMATION2'
436 ,p_attribute2_value => p_rec.job_information2
437 ,p_attribute3_name => 'JOB_INFORMATION3'
438 ,p_attribute3_value => p_rec.job_information3
439 ,p_attribute4_name => 'JOB_INFORMATION4'
440 ,p_attribute4_value => p_rec.job_information4
441 ,p_attribute5_name => 'JOB_INFORMATION5'
442 ,p_attribute5_value => p_rec.job_information5
443 ,p_attribute6_name => 'JOB_INFORMATION6'
444 ,p_attribute6_value => p_rec.job_information6
445 ,p_attribute7_name => 'JOB_INFORMATION7'
446 ,p_attribute7_value => p_rec.job_information7
447 ,p_attribute8_name => 'JOB_INFORMATION8'
448 ,p_attribute8_value => p_rec.job_information8
449 ,p_attribute9_name => 'JOB_INFORMATION9'
450 ,p_attribute9_value => p_rec.job_information9
451 ,p_attribute10_name => 'JOB_INFORMATION10'
452 ,p_attribute10_value => p_rec.job_information10
453 ,p_attribute11_name => 'JOB_INFORMATION11'
454 ,p_attribute11_value => p_rec.job_information11
455 ,p_attribute12_name => 'JOB_INFORMATION12'
456 ,p_attribute12_value => p_rec.job_information12
457 ,p_attribute13_name => 'JOB_INFORMATION13'
458 ,p_attribute13_value => p_rec.job_information13
459 ,p_attribute14_name => 'JOB_INFORMATION14'
460 ,p_attribute14_value => p_rec.job_information14
461 ,p_attribute15_name => 'JOB_INFORMATION15'
462 ,p_attribute15_value => p_rec.job_information15
463 ,p_attribute16_name => 'JOB_INFORMATION16'
464 ,p_attribute16_value => p_rec.job_information16
465 ,p_attribute17_name => 'JOB_INFORMATION17'
466 ,p_attribute17_value => p_rec.job_information17
467 ,p_attribute18_name => 'JOB_INFORMATION18'
468 ,p_attribute18_value => p_rec.job_information18
469 ,p_attribute19_name => 'JOB_INFORMATION19'
470 ,p_attribute19_value => p_rec.job_information19
471 ,p_attribute20_name => 'JOB_INFORMATION20'
472 ,p_attribute20_value => p_rec.job_information20
473 );
474 end if;
475 --
476 hr_utility.set_location(' Leaving:'||l_proc, 20);
477 end chk_ddf;
478 --
479 -- ---------------------------------------------------------------------------
480 -- |---------------------------< insert_validate >----------------------------|
481 -- ----------------------------------------------------------------------------
482 Procedure insert_validate(p_rec in per_job_shd.g_rec_type) is
483 --
484 l_proc varchar2(72) := g_package||'insert_validate';
485 --
486 Begin
487
488 hr_utility.set_location('Entering:'||l_proc, 5);
489 --
490 -- Call all supporting business operations
491 --
492 -- Validate Business Group
493 --
494 hr_api.validate_bus_grp_id(p_rec.business_group_id);
495 --
496 hr_utility.set_location(l_proc, 6);
497 --
498 -- Validate date from and date_to
499 --
500 chk_dates
501 (p_date_from => p_rec.date_from,
502 p_date_to => p_rec.date_to
503 );
504 --
505 hr_utility.set_location(l_proc, 7);
506 --
507 -- Validate job definition id
508 --
509 chk_job_definition_id
510 (p_job_definition_id => p_rec.job_definition_id,
511 p_business_group_id => p_rec.business_group_id
512 );
513 --
514 -- chk_emp_rights_flag
515 --
516 hr_utility.set_location(l_proc, 8);
517 --
518 chk_emp_rights_flag
519 (p_emp_rights_flag => p_rec.emp_rights_flag
520 ,p_rec => p_rec);
521 --
522 -- chk_job_group_id
523 --
524 hr_utility.set_location(l_proc, 9);
525 --
526 chk_job_group_id
527 (p_job_group_id => p_rec.job_group_id
528 ,p_business_group_id => p_rec.business_group_id);
529 --
530 -- chk_approval_authority
531 --
532 hr_utility.set_location(l_proc, 10);
533 --
534 chk_approval_authority
535 (p_approval_authority => p_rec.approval_authority
536 ,p_rec => p_rec);
537 --
538 -- chk_benchmark_job_flag
539 --
540 hr_utility.set_location(l_proc, 11);
541 --
542 chk_benchmark_job_flag
543 (p_benchmark_job_flag => p_rec.benchmark_job_flag
544 ,p_benchmark_job_id => p_rec.benchmark_job_id
545 ,p_rec => p_rec);
546 --
547 -- chk_benchmark_job_id
548 --
549 hr_utility.set_location(l_proc, 12);
550 --
551 chk_benchmark_job_id
552 (p_benchmark_job_id => p_rec.benchmark_job_id
553 ,p_job_id => p_rec.job_id
554 ,p_business_group_id => p_rec.business_group_id
555 ,p_rec => p_rec);
556 --
557 -- Flexfield Validation
558 --
559 hr_utility.set_location(l_proc, 13);
560 --
561 chk_ddf(p_rec => p_rec);
562 --
563 chk_df(p_rec => p_rec);
564 --
565 hr_utility.set_location('Leaving:'||l_proc, 14);
566 End insert_validate;
567 --
568 -- ----------------------------------------------------------------------------
569 -- |---------------------------< update_validate >----------------------------|
570 -- ----------------------------------------------------------------------------
571 Procedure update_validate(p_rec in per_job_shd.g_rec_type) is
572 --
573 l_proc varchar2(72) := g_package||'update_validate';
574 --
575 Begin
576
577 hr_utility.set_location('Entering:'||l_proc, 5);
578 --
579 -- Call all supporting business operations
580 --
581 -- Validate Business Group
582 --
583 hr_api.validate_bus_grp_id(p_rec.business_group_id);
584 --
585 hr_utility.set_location(l_proc, 10);
586 --
587 -- chk_non_updateable_args
588 --
589 chk_non_updateable_args
590 (p_date_from => p_rec.date_from
591 ,p_rec => p_rec);
592 --
593 hr_utility.set_location(l_proc, 15);
594 --
595 -- Validate date effective
596 --
597 chk_dates
598 (p_job_id => p_rec.job_id,
599 p_date_from => p_rec.date_from,
600 p_date_to => p_rec.date_to,
601 p_object_version_number => p_rec.object_version_number
602 );
603 --
604 hr_utility.set_location(l_proc, 20);
605 --
606 -- Validate job definition id
607 --
608 chk_job_definition_id
609 (p_job_definition_id => p_rec.job_definition_id,
610 p_business_group_id => p_rec.business_group_id,
611 p_job_id => p_rec.job_id,
612 p_object_version_number => p_rec.object_version_number
613 );
614 --
615 -- check that the emp_rights_flag is set to Y or N
616 --
617 hr_utility.set_location(l_proc, 30);
618 --
619 chk_emp_rights_flag
620 (p_emp_rights_flag => p_rec.emp_rights_flag
621 ,p_rec => p_rec);
622 --
623 -- chk_approval_authority
624 --
625 hr_utility.set_location(l_proc, 35);
626 --
627 chk_approval_authority
628 (p_approval_authority => p_rec.approval_authority
629 ,p_rec => p_rec);
630 --
631 -- check that both the benchmark_job_flag and benchmark_job_id are not
632 -- populated
633 --
634 hr_utility.set_location(l_proc, 40);
635 --
636 chk_benchmark_job_flag
637 (p_benchmark_job_flag => p_rec.benchmark_job_flag
638 ,p_benchmark_job_id => p_rec.benchmark_job_id
639 ,p_rec => p_rec);
640 --
641 -- chk_benchmark_job_id
642 --
643 hr_utility.set_location(l_proc, 45);
644 --
645 chk_benchmark_job_id
646 (p_benchmark_job_id => p_rec.benchmark_job_id
647 ,p_job_id => p_rec.job_id
648 ,p_business_group_id => p_rec.business_group_id
649 ,p_rec => p_rec);
650 --
651 hr_utility.set_location(l_proc, 50);
652 --
653 -- Flexfield Validation
654 --
655 chk_ddf(p_rec => p_rec);
656 --
657 chk_df(p_rec => p_rec);
658 --
659 hr_utility.set_location(' Leaving:'||l_proc, 55);
660 --
661 End update_validate;
662 --
663 -- ----------------------------------------------------------------------------
664 -- |---------------------------< delete_validate >----------------------------|
665 -- ----------------------------------------------------------------------------
666 Procedure delete_validate(p_rec in per_job_shd.g_rec_type) is
667 --
668 cursor csr_bg is select business_group_id from per_jobs
669 where job_id = p_rec.job_id;
670 l_business_group_id number;
671 l_proc varchar2(72) := g_package||'delete_validate';
672 --
673 Begin
674 hr_utility.set_location('Entering:'||l_proc, 5);
675 --
676 -- Call all supporting business operations
677 --
678 open csr_bg;
679 fetch csr_bg into l_business_group_id;
680 close csr_bg;
681 per_job_bus.check_delete_record
682 (p_job_id => p_rec.job_id
683 ,p_business_group_id => l_business_group_id);
684
685 hr_utility.set_location(' Leaving:'||l_proc, 10);
686 End delete_validate;
687 --
688 --
689 -- ---------------------------------------------------------------------------
690 -- |---------------------< return_legislation_code >-------------------------|
691 -- ---------------------------------------------------------------------------
692 --
693 function return_legislation_code
694 (p_job_id in number
695 ) return varchar2 is
696 --
697 -- Declare cursor
698 --
699 cursor csr_leg_code is
700 select pbg.legislation_code
701 from per_business_groups pbg
702 , per_jobs job
703 where job.job_id = p_job_id
704 and pbg.business_group_id = job.business_group_id;
705 --
706 -- Declare local variables
707 --
708 l_legislation_code varchar2(150);
709 l_proc varchar2(72) := g_package||'return_legislation_code';
710 begin
711 hr_utility.set_location('Entering:'|| l_proc, 10);
712 --
713 -- Ensure that all the mandatory parameter are not null
714 --
715 hr_api.mandatory_arg_error(p_api_name => l_proc,
716 p_argument => 'job_id',
717 p_argument_value => p_job_id);
718 --
719 if nvl(g_job_id, hr_api.g_number) = p_job_id then
720 --
721 -- The legislation code has already been found with a previous
722 -- call to this function. Just return the value in the global
723 -- variable.
724 --
725 l_legislation_code := g_legislation_code;
726 hr_utility.set_location(l_proc, 20);
727 else
728 --
729 -- The ID is different to the last call to this function
730 -- or this is the first call to this function.
731 --
732 open csr_leg_code;
733 fetch csr_leg_code into l_legislation_code;
734 if csr_leg_code%notfound then
735 close csr_leg_code;
736 --
737 -- The primary key is invalid therefore we must error
738 --
739 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
740 hr_utility.raise_error;
741 end if;
742 --
743 close csr_leg_code;
744 g_job_id := p_job_id;
745 g_legislation_code := l_legislation_code;
746 end if;
747 hr_utility.set_location(' Leaving:'|| l_proc, 30);
748 --
749 return l_legislation_code;
750 end return_legislation_code;
751 --
752 -- ----------------------------------------------------------------------------
753 -- |-----------------------< chk_non_updateable_args >------------------------|
754 -- ----------------------------------------------------------------------------
755 Procedure chk_non_updateable_args
756 (p_date_from in date
757 ,p_rec in per_job_shd.g_rec_type
758 ) IS
759 --
760 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
761 l_error EXCEPTION;
762 l_argument varchar2(30);
763 --
764 Begin
765 --
766 -- Only proceed with the validation if a row exists for the current
767 -- record in the HR Schema.
768 --
769 IF NOT per_job_shd.api_updating
770 (p_job_id => p_rec.job_id,
771 p_object_version_number => p_rec.object_version_number
772 ) THEN
773 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
774 fnd_message.set_token('PROCEDURE ', l_proc);
775 fnd_message.set_token('STEP ', '5');
776 fnd_message.raise_error;
777 END IF;
778 --
779 IF (nvl(p_rec.business_group_id,hr_api.g_number) <>
780 nvl(per_job_shd.g_old_rec.business_group_id,hr_api.g_number)
781 ) THEN
782 l_argument := 'business_group_id';
783 RAISE l_error;
784 END IF;
785
786 IF (nvl(p_rec.job_group_id,hr_api.g_number) <>
787 nvl(per_job_shd.g_old_rec.job_group_id,hr_api.g_number)
788 ) THEN
789 l_argument := 'job_group_id';
790 RAISE l_error;
791 END IF;
792
793 IF (nvl(p_rec.job_id,hr_api.g_number) <>
794 nvl(per_job_shd.g_old_rec.job_id,hr_api.g_number)
795 ) THEN
796 l_argument := 'job_id';
797 RAISE l_error;
798 END IF;
799
800 EXCEPTION
801 WHEN l_error THEN
802 hr_api.argument_changed_error
803 (p_api_name => l_proc
804 ,p_argument => l_argument);
805 WHEN OTHERS THEN
806 RAISE;
807 End chk_non_updateable_args;
808 --
809 -- -------------------------------------------------------------------------+
810 -- |------------------------< chk_emp_rights_flag >-------------------------|
811 -- -------------------------------------------------------------------------+
812 procedure chk_emp_rights_flag
813 (p_emp_rights_flag in per_jobs.emp_rights_flag%TYPE
814 ,p_rec in per_job_shd.g_rec_type) is
815
816 l_proc varchar2(72) := g_package||'chk_emp_rights_flag';
817 l_api_updating boolean;
818
819 begin
820 hr_utility.set_location('Entering:'|| l_proc, 10);
821
822 l_api_updating := per_job_shd.api_updating
823 (p_job_id => p_rec.job_id
824 ,p_object_version_number => p_rec.object_version_number);
825
826 if (l_api_updating and
827 (nvl(per_job_shd.g_old_rec.emp_rights_flag,hr_api.g_varchar2) <>
828 nvl(p_emp_rights_flag,hr_api.g_varchar2)) or NOT l_api_updating) then
829
830 if (p_emp_rights_flag <> 'Y' and p_emp_rights_flag <> 'N' and
831 p_emp_rights_flag is not null) then
832 hr_utility.set_message(801,'HR_289476_EMP_RIGHTS_FLAG');
833 hr_utility.raise_error;
834 end if;
835
836 end if;
837
838 hr_utility.set_location('Leaving:'|| l_proc, 20);
839
840 end chk_emp_rights_flag;
841 --
842 -- -------------------------------------------------------------------------+
843 -- |--------------------------< chk_job_group_id >--------------------------|
844 -- -------------------------------------------------------------------------+
845 procedure chk_job_group_id
846 (p_job_group_id in per_jobs.job_group_id%TYPE
847 ,p_business_group_id in per_jobs.business_group_id%TYPE
848 ) is
849 -- Bug 3177195 Changed the where clause for the cursor
850 cursor csr_job_group_id is
851 select job_group_id
852 from per_job_groups
853 where job_group_id = p_job_group_id
854 and (business_group_id is null
855 or business_group_id = p_business_group_id);
856
857 l_job_group_id per_jobs.job_group_id%TYPE;
858 l_proc varchar2(72) := g_package||'chk_job_group_id';
859
860 begin
861 hr_utility.set_location('Entering:'|| l_proc, 10);
862
863 open csr_job_group_id;
864 fetch csr_job_group_id into l_job_group_id;
865 close csr_job_group_id;
866
867 if l_job_group_id is null then
868 hr_utility.set_message(801, 'HR_289477_JOB_GROUP_ID');
869 hr_utility.raise_error;
870 end if;
871
872 hr_utility.set_location('Leaving:'|| l_proc, 20);
873
874 end chk_job_group_id;
875 --
876 -- -------------------------------------------------------------------------+
877 -- |-----------------------< chk_approval_authority >-----------------------|
878 -- -------------------------------------------------------------------------+
879 procedure chk_approval_authority
880 (p_approval_authority in per_jobs.approval_authority%TYPE
881 ,p_rec in per_job_shd.g_rec_type) is
882
883 l_proc varchar2(72) := g_package||'chk_approval_authority';
884 l_approval_authority number := p_approval_authority;
885 l_api_updating boolean;
886
887 begin
888
889 hr_utility.set_location('Entering:'|| l_proc, 10);
890
891 l_api_updating := per_job_shd.api_updating
892 (p_job_id => p_rec.job_id
893 ,p_object_version_number => p_rec.object_version_number);
894
895 if (l_api_updating and
896 (nvl(per_job_shd.g_old_rec.approval_authority,hr_api.g_number) <>
897 nvl(p_approval_authority,hr_api.g_number)) or
898 NOT l_api_updating) then
899
900 if (l_approval_authority < 0) then
901 hr_utility.set_message(801, 'HR_289991_APPROVAL_AUTHORITY');
902 hr_utility.raise_error;
903 end if;
904
905 end if;
906
907 hr_utility.set_location('Leaving:'|| l_proc, 20);
908
909 end chk_approval_authority;
910 --
911 -- -------------------------------------------------------------------------+
912 -- |-----------------------< chk_benchmark_job_flag >-----------------------|
913 -- -------------------------------------------------------------------------+
914 procedure chk_benchmark_job_flag
915 (p_benchmark_job_flag in per_jobs.benchmark_job_flag%TYPE
916 ,p_benchmark_job_id in per_jobs.benchmark_job_id%TYPE
917 ,p_rec in per_job_shd.g_rec_type) is
918
919 l_proc varchar2(72) := g_package||'chk_benchmark_job_flag';
920 l_api_updating boolean;
921 begin
922
923 hr_utility.set_location('Entering:'|| l_proc, 10);
924
925 l_api_updating := per_job_shd.api_updating
926 (p_job_id => p_rec.job_id
927 ,p_object_version_number => p_rec.object_version_number);
928
929 if (l_api_updating and
930 (nvl(per_job_shd.g_old_rec.benchmark_job_flag,'N') <>
931 nvl(p_benchmark_job_flag, 'N')) or
932 (nvl(per_job_shd.g_old_rec.benchmark_job_id, hr_api.g_number) <>
933 nvl(p_benchmark_job_id, hr_api.g_number)) or
934 NOT l_api_updating) then
935
936 if (p_benchmark_job_flag = 'Y' and p_benchmark_job_id is not null) then
937 hr_utility.set_message(801, 'HR_289474_BENCHMARK_JOB_FLAG');
938 hr_utility.raise_error;
939 end if;
940
941 end if;
942
943 hr_utility.set_location('Leaving:'|| l_proc, 20);
944
945 end chk_benchmark_job_flag;
946 --
947 -- --------------------------------------------------------------------------+
948 -- |------------------------< chk_benchmark_job_id >-------------------------|
949 -- --------------------------------------------------------------------------+
950 procedure chk_benchmark_job_id
951 (p_benchmark_job_id in per_jobs.benchmark_job_id%TYPE
952 ,p_job_id in per_jobs.job_id%TYPE
953 ,p_business_group_id in per_jobs.business_group_id%TYPE
954 ,p_rec in per_job_shd.g_rec_type) is
955
956 cursor csr_benchmark_job_id is
957 select 1
958 from per_jobs
959 --
960 -- Bug 3213738
961 -- Changed where clause to filter based on benchmark_job_id
962 -- and relaxed check on business group.
963 --
964 where job_id = p_benchmark_job_id
965 and p_benchmark_job_id <> nvl(p_job_id,hr_api.g_number)
966 and benchmark_job_flag = 'Y';
967
968 l_benchmark_job_id per_jobs.benchmark_job_id%TYPE;
969 l_proc varchar2(72) := g_package||'chk_benchmark_job_id';
970 l_api_updating boolean;
971
972 begin
973
974 hr_utility.set_location('Entering:'|| l_proc, 10);
975
976 l_api_updating := per_job_shd.api_updating
977 (p_job_id => p_rec.job_id
978 ,p_object_version_number => p_rec.object_version_number);
979
980 if (l_api_updating and
981 (nvl(per_job_shd.g_old_rec.benchmark_job_id,hr_api.g_number) <>
982 nvl(p_benchmark_job_id,hr_api.g_number)) or NOT l_api_updating) then
983
984 if (p_benchmark_job_id is not null) then
985
986 open csr_benchmark_job_id;
987 fetch csr_benchmark_job_id into l_benchmark_job_id;
988 close csr_benchmark_job_id;
989
990 if l_benchmark_job_id is null then
991 hr_utility.set_message(801, 'HR_289475_BENCHMARK_JOB_ID');
992 hr_utility.raise_error;
993 end if;
994
995 end if;
996
997 end if;
998
999 hr_utility.set_location('Leaving:'|| l_proc, 20);
1000
1001 end chk_benchmark_job_id ;
1002 --
1003
1004 -- --------------------------------------------------------------------------+
1005 -- |------------------------< check_unique_name >-------------------------|
1006 -- --------------------------------------------------------------------------+
1007 procedure check_unique_name(p_job_id in number,
1008 p_business_group_id in number,
1009 p_name in varchar2) is
1010 --
1011 cursor csr_name is select null
1012 from per_jobs j
1013 where ((p_job_id is not null
1014 and j.job_id <> p_job_id)
1015 or p_job_id is null)
1016 and j.business_group_id + 0 = p_business_group_id
1017 and j.name = p_name;
1018 --
1019 g_dummy_number number;
1020 v_not_unique boolean := FALSE;
1021 l_proc varchar2(72) := g_package||'check_unique_name';
1022 --
1023 -- Check the job name is unique
1024 --
1025 begin
1026 --
1027 open csr_name;
1028 fetch csr_name into g_dummy_number;
1029 v_not_unique := csr_name%FOUND;
1030 close csr_name;
1031 --
1032 if v_not_unique then
1033 hr_utility.set_message(801,'PER_7810_DEF_JOB_EXISTS');
1034 hr_utility.raise_error;
1035 end if;
1036 --
1037 hr_utility.set_location(l_proc, 10);
1038 --
1039 end check_unique_name;
1040 --
1041 -- --------------------------------------------------------------------------+
1042 -- |------------------------< check_date_from >-------------------------|
1043 -- --------------------------------------------------------------------------+
1044 procedure check_date_from(p_job_id in number,
1045 p_date_from in date) is
1046 --
1047 cursor csr_date_from is select null
1048 from per_valid_grades vg
1049 where vg.job_id = p_job_id
1050 and p_date_from > vg.date_from;
1051 --
1052 g_dummy_number number;
1053 v_job_date_greater boolean := FALSE;
1054 l_proc varchar2(72) := g_package||'check_date_from';
1055 --
1056 begin
1057 hr_utility.set_location('check date',99);
1058 --
1059 -- If the date from item in the jobs block is greater than
1060 -- the date from item in the grades block then raise an error
1061 --
1062 open csr_date_from;
1063 fetch csr_date_from into g_dummy_number;
1064 v_job_date_greater := csr_date_from%FOUND;
1065 close csr_date_from;
1066 --
1067 if v_job_date_greater then
1068 hr_utility.set_message(801,'PER_7825_DEF_GRD_JOB_START_JOB');
1069 hr_utility.raise_error;
1070 end if;
1071 --
1072 hr_utility.set_location(l_proc, 10);
1073 --
1074 end check_date_from;
1075 --
1076 -- --------------------------------------------------------------------------+
1077 -- |------------------------< check_altered_end_date >-----------------------|
1078 -- --------------------------------------------------------------------------+
1079 procedure check_altered_end_date(p_business_group_id number,
1080 p_job_id number,
1081 p_end_of_time date,
1082 p_date_to date,
1083 p_early_date_to in out nocopy boolean,
1084 p_early_date_from in out nocopy boolean) is
1085 --
1086 cursor csr_date_to is select null
1087 from per_valid_grades vg
1088 where vg.business_group_id + 0 = p_business_group_id
1089 and vg.job_id = p_job_id
1090 and nvl(vg.date_to, p_end_of_time) > p_date_to;
1091 --
1092 cursor csr_date_from is select null
1093 from per_valid_grades vg
1094 where vg.business_group_id + 0 = p_business_group_id
1095 and vg.job_id = p_job_id
1096 and vg.date_from > p_date_to;
1097 --
1098 g_dummy_number number;
1099 l_proc varchar2(72) := g_package||'check_altered_end_date';
1100 --
1101 begin
1102 --
1103 open csr_date_to;
1104 fetch csr_date_to into g_dummy_number;
1105 p_early_date_to := csr_date_to%FOUND;
1106 close csr_date_to;
1107 --
1108 hr_utility.set_location(l_proc, 10);
1109 --
1110 open csr_date_from;
1111 fetch csr_date_from into g_dummy_number;
1112 p_early_date_from := csr_date_from%FOUND;
1113 close csr_date_from;
1114 --
1115 hr_utility.set_location(l_proc, 20);
1116 --
1117 end check_altered_end_date;
1118 --
1119 -- ---------------------------------------------------------------------------
1120 -- |-----------------------< check_delete_record >--------------------------|
1121 -- ---------------------------------------------------------------------------
1122 procedure check_delete_record(p_job_id number,
1123 p_business_group_id number) is
1124 --
1125 -- Changed 01-Oct-99 SCNair (per_all_positions to hr_all_positions_f) date track
1126 -- requirement
1127 --
1128 cursor csr_position is select null
1129 from hr_all_positions_f pst1
1130 where pst1.job_id = p_job_id;
1131 --
1132 cursor csr_assignment is select null
1133 from per_all_assignments_f a
1134 where a.job_id = p_job_id
1135 and a.job_id is not null;
1136 --
1137 cursor csr_grade is select null
1138 from per_valid_grades vg1
1139 where vg1.business_group_id + 0 = p_business_group_id
1140 and vg1.job_id = p_job_id;
1141 --
1142 cursor csr_requirement is select null
1143 from per_job_requirements jre1
1144 where jre1.job_id = p_job_id;
1145 --
1146 cursor csr_evaluation is select null
1147 from per_job_evaluations jev1
1148 where jev1.job_id = p_job_id;
1149 --
1150 cursor csr_elementp is select null
1151 from per_career_path_elements cpe1
1152 where cpe1.parent_job_id = p_job_id;
1153 --
1154 cursor csr_elements is select null
1155 from per_career_path_elements cpe1
1156 where cpe1.subordinate_job_id = p_job_id;
1157 --
1158 cursor csr_budget is select null
1159 from per_budget_elements bde1
1160 where bde1.job_id = p_job_id
1161 and bde1.job_id is not null;
1162 --
1163 cursor csr_vacancy is select null
1164 from per_vacancies vac
1165 where vac.job_id = p_job_id
1166 and vac.job_id is not null;
1167 --
1168 cursor csr_link is select null
1169 from pay_element_links_f eln
1170 where eln.job_id = p_job_id
1171 and eln.job_id is not null;
1172 --
1173 cursor csr_role is select null
1174 from per_roles rol
1175 where rol.job_id = p_job_id
1176 and rol.job_id is not null;
1177 --
1178 g_dummy_number number;
1179 v_record_exists boolean := FALSE;
1180 v_dummy boolean := FALSE;
1181 l_sql_text VARCHAR2(2000);
1182 l_status VARCHAR2(1);
1183 l_industry VARCHAR2(1);
1184 l_oci_out VARCHAR2(1);
1185 l_sql_cursor NUMBER;
1186 l_rows_fetched NUMBER;
1187 l_proc varchar2(72) := g_package||'check_delete_record';
1188 --
1189 begin
1190 --
1191 -- Check there are no values in per_valid_grades, per_job_requirements,
1192 -- per_job_evaluations, per_career_path_elements (check on parent and
1193 -- subordinate id), hr_all_positions_f, per_budget_elements,
1194 -- PER_all_assignments, per_vacancies_f, per_element_links_f
1195 --
1196 --
1197 --
1198 open csr_position;
1199 fetch csr_position into g_dummy_number;
1200 v_record_exists := csr_position%FOUND;
1201 close csr_position;
1202 --
1203 if v_record_exists then
1204 hr_utility.set_message(801,'PER_7813_DEF_JOB_DEL_POS');
1205 hr_utility.raise_error;
1206 end if;
1207 --
1208 hr_utility.set_location(l_proc, 10);
1209 --
1210 --
1211 --
1212 open csr_assignment;
1213 fetch csr_assignment into g_dummy_number;
1214 v_record_exists := csr_assignment%FOUND;
1215 close csr_assignment;
1216 --
1217 if v_record_exists then
1218 hr_utility.set_message(801,'PER_7817_DEF_JOB_DEL_EMP');
1219 hr_utility.raise_error;
1220 end if;
1221 --
1222 hr_utility.set_location(l_proc, 20);
1223 --
1224 --
1225 --
1226 open csr_grade;
1227 fetch csr_grade into g_dummy_number;
1228 v_record_exists := csr_grade%FOUND;
1229 close csr_grade;
1230 --
1231 if v_record_exists then
1232 hr_utility.set_message(801,'PER_7812_DEF_JOB_DEL_GRADE');
1233 hr_utility.raise_error;
1234 end if;
1235 --
1236 hr_utility.set_location(l_proc, 30);
1237 --
1238 --
1239 --
1240 open csr_requirement;
1241 fetch csr_requirement into g_dummy_number;
1242 v_record_exists := csr_requirement%FOUND;
1243 close csr_requirement;
1244 --
1245 if v_record_exists then
1246 hr_utility.set_message(801,'PER_7814_DEF_JOB_DEL_REQ');
1247 hr_utility.raise_error;
1248 end if;
1249 --
1250 hr_utility.set_location(l_proc, 40);
1251 --
1252 --
1253 --
1254 open csr_evaluation;
1255 fetch csr_evaluation into g_dummy_number;
1256 v_record_exists := csr_evaluation%FOUND;
1257 close csr_evaluation;
1258 --
1259 if v_record_exists then
1260 hr_utility.set_message(801,'PER_7815_DEF_JOB_DEL_EVAL');
1261 hr_utility.raise_error;
1262 end if;
1263 --
1264 hr_utility.set_location(l_proc, 50);
1265 --
1266 --
1267 open csr_elementp;
1268 fetch csr_elementp into g_dummy_number;
1269 v_record_exists := csr_elementp%FOUND;
1270 close csr_elementp;
1271 --
1272 if v_record_exists then
1273 hr_utility.set_message(801,'PER_7811_DEF_JOB_DEL_PATH');
1274 hr_utility.raise_error;
1275 end if;
1276 --
1277 hr_utility.set_location(l_proc, 60);
1278 --
1279 --
1280 --
1281 open csr_elements;
1282 fetch csr_elements into g_dummy_number;
1283 v_record_exists := csr_elements%FOUND;
1284 close csr_elements;
1285 --
1286 if v_record_exists then
1287 hr_utility.set_message(801,'PER_7811_DEF_JOB_DEL_PATH');
1288 hr_utility.raise_error;
1289 end if;
1290 --
1291 hr_utility.set_location(l_proc, 70);
1292 --
1293 --
1294 open csr_budget;
1295 fetch csr_budget into g_dummy_number;
1296 v_record_exists := csr_budget%FOUND;
1297 close csr_budget;
1298 --
1299 if v_record_exists then
1300 hr_utility.set_message(801,'PER_7816_DEF_JOB_DEL_BUD');
1301 hr_utility.raise_error;
1302 end if;
1303 --
1304 hr_utility.set_location(l_proc, 80);
1305 --
1306 --
1307 open csr_vacancy;
1308 fetch csr_vacancy into g_dummy_number;
1309 v_record_exists := csr_vacancy%FOUND;
1310 close csr_vacancy;
1311 --
1312 if v_record_exists then
1313 hr_utility.set_message(801,'HR_6945_JOB_DEL_RAC');
1314 hr_utility.raise_error;
1315 end if;
1316 --
1317 hr_utility.set_location(l_proc, 90);
1318 --
1319 --
1320 open csr_link;
1321 fetch csr_link into g_dummy_number;
1322 v_record_exists := csr_link%FOUND;
1323 close csr_link;
1324 --
1325 if v_record_exists then
1326 hr_utility.set_message(801,'HR_6946_JOB_DEL_LINK');
1327 hr_utility.raise_error;
1328 end if;
1329 --
1330 hr_utility.set_location(l_Proc, 100);
1331 --
1332 --
1333 open csr_role;
1334 fetch csr_role into g_dummy_number;
1335 v_record_exists := csr_role%FOUND;
1336 close csr_role;
1337 --
1338 if v_record_exists then
1339 hr_utility.set_message(800,'PER_52684_JOB_DEL_ROLE');
1340 hr_utility.raise_error;
1341 end if;
1342 --
1343 hr_utility.set_location(l_proc, 110);
1344 --
1345 -- is po installed?
1346 --
1347 if (fnd_installation.get(appl_id => 201
1348 ,dep_appl_id => 201
1349 ,status => l_status
1350 ,industry => l_industry))
1351 then
1352 --
1353 -- If fully installed (l_status = 'I')
1354 --
1355 if l_status = 'I'
1356 then
1357 -- Dynamic SQL cursor to get round the problem of Table not existing.
1358 -- Shouldn't be a problem after 10.6, but better safe than sorry.
1359 -- This uses a similar method to OCI but Via PL/SQL instead.
1360 --
1361 -- #358988 removed the table alias 'pcc' which didn't match the column
1362 -- alias ppc. RMF 17-Apr-96.
1363 --
1364 begin
1365 l_sql_text := 'select null '
1366 ||'from sys.dual '
1367 ||'where exists( select null '
1368 ||' from po_position_controls_all '
1369 ||' where job_id = '
1370 ||to_char(p_job_id)
1371 ||' ) ';
1372 --
1373 -- Open Cursor for Processing Sql statment.
1374 --
1375 l_sql_cursor := dbms_sql.open_cursor;
1376 --
1377 --
1378 -- Parse SQL statement.
1379 --
1380 dbms_sql.parse(l_sql_cursor, l_sql_text, dbms_sql.v7);
1381 --
1382 -- Map the local variables to each returned Column
1383 --
1384 dbms_sql.define_column(l_sql_cursor, 1,l_oci_out,1);
1385 --
1386 -- Execute the SQL statement.
1387 --
1388 l_rows_fetched := dbms_sql.execute(l_sql_cursor);
1389 --
1390 if (dbms_sql.fetch_rows(l_sql_cursor) > 0)
1391 then
1392 fnd_message.set_name('PAY','HR_6048_PO_POS_DEL_POS_CONT');
1393 fnd_message.raise_error;
1394 end if;
1395 --
1396 -- Close cursor used for processing SQL statement.
1397 --
1398 dbms_sql.close_cursor(l_sql_cursor);
1399 end;
1400 end if;
1401 end if;
1402 --
1403 hr_utility.set_location(l_proc, 120);
1404 --
1405 per_ota_predel_validation.ota_predel_job_validation(p_job_id);
1406 --
1407 hr_utility.set_location(l_proc, 130);
1408 --
1409 pa_job.pa_predel_validation(p_job_id);
1410 --
1411 end check_delete_record;
1412 --
1413 -- ---------------------------------------------------------------------------
1414 -- |-----------------------< check_evaluation_dates >-----------------------|
1415 -- ---------------------------------------------------------------------------
1416 procedure check_evaluation_dates(p_jobid in number,
1417 p_job_date_from in date,
1418 p_job_date_to in date) is
1419
1420
1421 cursor csr_job_evaluations(p_job_id in number) is
1422 select jbe.job_evaluation_id,
1423 jbe.date_evaluated
1424 from per_job_evaluations jbe
1425 where jbe.job_id = csr_job_evaluations.p_job_id;
1426
1427 --
1428 begin
1429 --
1430
1431 if p_jobid is not null then
1432 for l_job_evaluation in csr_job_evaluations(
1433 p_job_id => p_jobid) loop
1434 if l_job_evaluation.date_evaluated not between
1435 nvl(p_job_date_from, hr_api.g_sot) and
1436 nvl(p_job_date_to, hr_api.g_eot) then
1437 fnd_message.set_name('PER', 'HR_52603_JOB_JBE_OUT_PERIOD');
1438 hr_utility.raise_error;
1439 end if;
1440 end loop;
1441 end if;
1442
1443 --
1444 exception
1445 --
1446
1447 when others then
1448 if csr_job_evaluations%isopen then
1449 close csr_job_evaluations;
1450 end if;
1451 raise;
1452
1453 --
1454 end check_evaluation_dates;
1455 --
1456 end per_job_bus;