[Home] [Help]
PACKAGE BODY: APPS.PER_VACANCY_API
Source
1 Package Body PER_VACANCY_API as
2 /* $Header: pevacapi.pkb 120.0.12000000.2 2007/06/12 08:51:35 sidsaxen noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'PER_VACANCY_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< create_vacancy >--------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_vacancy
13 (
14 P_VALIDATE in boolean default false
15 , P_EFFECTIVE_DATE in date default null
16 , P_REQUISITION_ID in number
17 , P_DATE_FROM in date
18 , P_NAME in varchar2
19 , P_SECURITY_METHOD in varchar2 default 'B'
20 , P_BUSINESS_GROUP_ID in number
21 , P_POSITION_ID in number default null
22 , P_JOB_ID in number default null
23 , P_GRADE_ID in number default null
24 , P_ORGANIZATION_ID in number default null
25 , P_PEOPLE_GROUP_ID in number default null
26 , P_LOCATION_ID in number default null
27 , P_RECRUITER_ID in number default null
28 , P_DATE_TO in date default null
29 , P_DESCRIPTION in varchar2 default null
30 , P_NUMBER_OF_OPENINGS in number default null
31 , P_STATUS in varchar2 default null
32 , P_BUDGET_MEASUREMENT_TYPE in varchar2 default null
33 , P_BUDGET_MEASUREMENT_VALUE in number default null
34 , P_VACANCY_CATEGORY in varchar2 default null
35 , P_MANAGER_ID in number default null
36 , P_PRIMARY_POSTING_ID in number default null
37 , P_ASSESSMENT_ID in number default null
38 , P_ATTRIBUTE_CATEGORY in varchar2 default null
39 , P_ATTRIBUTE1 in varchar2 default null
40 , P_ATTRIBUTE2 in varchar2 default null
41 , P_ATTRIBUTE3 in varchar2 default null
42 , P_ATTRIBUTE4 in varchar2 default null
43 , P_ATTRIBUTE5 in varchar2 default null
44 , P_ATTRIBUTE6 in varchar2 default null
45 , P_ATTRIBUTE7 in varchar2 default null
46 , P_ATTRIBUTE8 in varchar2 default null
47 , P_ATTRIBUTE9 in varchar2 default null
48 , P_ATTRIBUTE10 in varchar2 default null
49 , P_ATTRIBUTE11 in varchar2 default null
50 , P_ATTRIBUTE12 in varchar2 default null
51 , P_ATTRIBUTE13 in varchar2 default null
52 , P_ATTRIBUTE14 in varchar2 default null
53 , P_ATTRIBUTE15 in varchar2 default null
54 , P_ATTRIBUTE16 in varchar2 default null
55 , P_ATTRIBUTE17 in varchar2 default null
56 , P_ATTRIBUTE18 in varchar2 default null
57 , P_ATTRIBUTE19 in varchar2 default null
58 , P_ATTRIBUTE20 in varchar2 default null
59 , P_ATTRIBUTE21 in varchar2 default null
60 , P_ATTRIBUTE22 in varchar2 default null
61 , P_ATTRIBUTE23 in varchar2 default null
62 , P_ATTRIBUTE24 in varchar2 default null
63 , P_ATTRIBUTE25 in varchar2 default null
64 , P_ATTRIBUTE26 in varchar2 default null
65 , P_ATTRIBUTE27 in varchar2 default null
66 , P_ATTRIBUTE28 in varchar2 default null
67 , P_ATTRIBUTE29 in varchar2 default null
68 , P_ATTRIBUTE30 in varchar2 default null
69 , P_OBJECT_VERSION_NUMBER out nocopy number
70 , P_VACANCY_ID out nocopy number
71 , p_inv_pos_grade_warning out nocopy boolean
72 , p_inv_job_grade_warning out nocopy boolean
73 ) is
74 --
75 -- Declare cursors and local variables
76 --
77 l_proc varchar2(72) := g_package||'create_vacancy';
78 l_vacancy_id number;
79 l_object_version_number number := 1;
80 l_date_from date := trunc(P_DATE_FROM);
81 l_date_to date := trunc(P_DATE_TO);
82 l_effective_date date;
83 l_inv_pos_grade_warning boolean;
84 l_inv_job_grade_warning boolean;
85 begin
86 hr_utility.set_location('Entering:'|| l_proc, 10);
87 --
88 -- Issue a savepoint
89 --
90 savepoint create_vacancy;
91 --
92 -- Truncate the time portion from all IN date parameters
93 --
94 if p_effective_date is null then
95 l_effective_date:=l_date_from;
96 else
97 l_effective_date:=trunc(p_effective_date);
98 end if;
99 --
100 -- Call Before Process User Hook
101 --
102 begin
103 PER_VACANCY_BK1.create_vacancy_b
104 ( P_EFFECTIVE_DATE => l_effective_date
105 , P_REQUISITION_ID => P_REQUISITION_ID
106 , P_DATE_FROM => l_date_from
107 , P_NAME => P_NAME
108 , P_SECURITY_METHOD => P_SECURITY_METHOD
109 , P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
110 , P_POSITION_ID => P_POSITION_ID
111 , P_JOB_ID => P_JOB_ID
112 , P_GRADE_ID => P_GRADE_ID
113 , P_ORGANIZATION_ID => P_ORGANIZATION_ID
114 , P_PEOPLE_GROUP_ID => P_PEOPLE_GROUP_ID
115 , P_LOCATION_ID => P_LOCATION_ID
116 , P_RECRUITER_ID => P_RECRUITER_ID
117 , P_DATE_TO => l_date_to
118 , P_DESCRIPTION => P_DESCRIPTION
119 , P_NUMBER_OF_OPENINGS => P_NUMBER_OF_OPENINGS
120 , P_STATUS => P_STATUS
121 , P_BUDGET_MEASUREMENT_TYPE => P_BUDGET_MEASUREMENT_TYPE
122 , P_BUDGET_MEASUREMENT_VALUE => P_BUDGET_MEASUREMENT_VALUE
123 , P_VACANCY_CATEGORY => P_VACANCY_CATEGORY
124 , P_MANAGER_ID => P_MANAGER_ID
125 , P_PRIMARY_POSTING_ID => P_PRIMARY_POSTING_ID
126 , P_ASSESSMENT_ID => P_ASSESSMENT_ID
127 , P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
128 , P_ATTRIBUTE1 => P_ATTRIBUTE1
129 , P_ATTRIBUTE2 => P_ATTRIBUTE2
130 , P_ATTRIBUTE3 => P_ATTRIBUTE3
131 , P_ATTRIBUTE4 => P_ATTRIBUTE4
132 , P_ATTRIBUTE5 => P_ATTRIBUTE5
133 , P_ATTRIBUTE6 => P_ATTRIBUTE6
134 , P_ATTRIBUTE7 => P_ATTRIBUTE7
135 , P_ATTRIBUTE8 => P_ATTRIBUTE8
136 , P_ATTRIBUTE9 => P_ATTRIBUTE9
137 , P_ATTRIBUTE10 => P_ATTRIBUTE10
138 , P_ATTRIBUTE11 => P_ATTRIBUTE11
139 , P_ATTRIBUTE12 => P_ATTRIBUTE12
140 , P_ATTRIBUTE13 => P_ATTRIBUTE13
141 , P_ATTRIBUTE14 => P_ATTRIBUTE14
142 , P_ATTRIBUTE15 => P_ATTRIBUTE15
143 , P_ATTRIBUTE16 => P_ATTRIBUTE16
144 , P_ATTRIBUTE17 => P_ATTRIBUTE17
145 , P_ATTRIBUTE18 => P_ATTRIBUTE18
146 , P_ATTRIBUTE19 => P_ATTRIBUTE19
147 , P_ATTRIBUTE20 => P_ATTRIBUTE20
148 , P_ATTRIBUTE21 => P_ATTRIBUTE21
149 , P_ATTRIBUTE22 => P_ATTRIBUTE22
150 , P_ATTRIBUTE23 => P_ATTRIBUTE23
151 , P_ATTRIBUTE24 => P_ATTRIBUTE24
152 , P_ATTRIBUTE25 => P_ATTRIBUTE25
153 , P_ATTRIBUTE26 => P_ATTRIBUTE26
154 , P_ATTRIBUTE27 => P_ATTRIBUTE27
155 , P_ATTRIBUTE28 => P_ATTRIBUTE28
156 , P_ATTRIBUTE29 => P_ATTRIBUTE29
157 , P_ATTRIBUTE30 => P_ATTRIBUTE30
158 );
159 exception
160 when hr_api.cannot_find_prog_unit then
161 hr_api.cannot_find_prog_unit_error
162 (p_module_name => 'create_vacancy'
163 ,p_hook_type => 'BP'
164 );
165 end;
166 --
167 -- Validation in addition to Row Handlers
168 --
169 --
170 -- Process Logic
171 --
172 per_vac_ins.ins
173 (p_effective_date => l_effective_date
174 ,p_business_group_id => P_BUSINESS_GROUP_ID
175 ,p_requisition_id => P_REQUISITION_ID
176 ,p_date_from => l_date_from
177 ,p_name => P_NAME
178 ,p_position_id => P_POSITION_ID
179 ,p_job_id => P_JOB_ID
180 ,p_grade_id => P_GRADE_ID
181 ,p_organization_id => P_ORGANIZATION_ID
182 ,p_people_group_id => P_PEOPLE_GROUP_ID
183 ,p_location_id => P_LOCATION_ID
184 ,p_recruiter_id => P_RECRUITER_ID
185 ,p_date_to => l_date_to
186 ,p_description => P_DESCRIPTION
187 ,p_number_of_openings => P_NUMBER_OF_OPENINGS
188 ,p_status => P_STATUS
189 ,p_attribute_category => P_ATTRIBUTE_CATEGORY
190 ,p_attribute1 => P_ATTRIBUTE1
191 ,p_attribute2 => P_ATTRIBUTE2
192 ,p_attribute3 => P_ATTRIBUTE3
193 ,p_attribute4 => P_ATTRIBUTE4
194 ,p_attribute5 => P_ATTRIBUTE5
195 ,p_attribute6 => P_ATTRIBUTE6
196 ,p_attribute7 => P_ATTRIBUTE7
197 ,p_attribute8 => P_ATTRIBUTE8
198 ,p_attribute9 => P_ATTRIBUTE9
199 ,p_attribute10 => P_ATTRIBUTE10
200 ,p_attribute11 => P_ATTRIBUTE11
201 ,p_attribute12 => P_ATTRIBUTE12
202 ,p_attribute13 => P_ATTRIBUTE13
203 ,p_attribute14 => P_ATTRIBUTE14
204 ,p_attribute15 => P_ATTRIBUTE15
205 ,p_attribute16 => P_ATTRIBUTE16
206 ,p_attribute17 => P_ATTRIBUTE17
207 ,p_attribute18 => P_ATTRIBUTE18
208 ,p_attribute19 => P_ATTRIBUTE19
209 ,p_attribute20 => P_ATTRIBUTE20
210 ,p_vacancy_category => P_VACANCY_CATEGORY
211 ,p_budget_measurement_type => P_BUDGET_MEASUREMENT_TYPE
212 ,p_budget_measurement_value => P_BUDGET_MEASUREMENT_VALUE
213 ,p_manager_id => P_MANAGER_ID
214 ,p_security_method => P_SECURITY_METHOD
215 ,p_primary_posting_id => P_PRIMARY_POSTING_ID
216 ,p_assessment_id => P_ASSESSMENT_ID
217 ,p_inv_pos_grade_warning => l_inv_pos_grade_warning
218 ,p_inv_job_grade_warning => l_inv_job_grade_warning
219 ,p_vacancy_id => l_vacancy_id
220 ,p_object_version_number => l_object_version_number
221 );
222 --
223 -- Call After Process User Hook
224 --
225 begin
226 PER_VACANCY_BK1.create_vacancy_a
227 ( p_EFFECTIVE_DATE => l_effective_date
228 , P_REQUISITION_ID => P_REQUISITION_ID
229 , P_DATE_FROM => l_date_from
230 , P_NAME => P_NAME
231 , P_SECURITY_METHOD => P_SECURITY_METHOD
232 , P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
233 , P_POSITION_ID => P_POSITION_ID
234 , P_JOB_ID => P_JOB_ID
235 , P_GRADE_ID => P_GRADE_ID
236 , P_ORGANIZATION_ID => P_ORGANIZATION_ID
237 , P_PEOPLE_GROUP_ID => P_PEOPLE_GROUP_ID
238 , P_LOCATION_ID => P_LOCATION_ID
239 , P_RECRUITER_ID => P_RECRUITER_ID
240 , P_DATE_TO => l_date_to
241 , P_DESCRIPTION => P_DESCRIPTION
242 , P_NUMBER_OF_OPENINGS => P_NUMBER_OF_OPENINGS
243 , P_STATUS => P_STATUS
244 , P_BUDGET_MEASUREMENT_TYPE => P_BUDGET_MEASUREMENT_TYPE
245 , P_BUDGET_MEASUREMENT_VALUE => P_BUDGET_MEASUREMENT_VALUE
246 , P_VACANCY_CATEGORY => P_VACANCY_CATEGORY
247 , P_MANAGER_ID => P_MANAGER_ID
248 , P_PRIMARY_POSTING_ID => P_PRIMARY_POSTING_ID
249 , P_ASSESSMENT_ID => P_ASSESSMENT_ID
250 , P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
251 , P_ATTRIBUTE1 => P_ATTRIBUTE1
252 , P_ATTRIBUTE2 => P_ATTRIBUTE2
253 , P_ATTRIBUTE3 => P_ATTRIBUTE3
254 , P_ATTRIBUTE4 => P_ATTRIBUTE4
255 , P_ATTRIBUTE5 => P_ATTRIBUTE5
256 , P_ATTRIBUTE6 => P_ATTRIBUTE6
257 , P_ATTRIBUTE7 => P_ATTRIBUTE7
258 , P_ATTRIBUTE8 => P_ATTRIBUTE8
259 , P_ATTRIBUTE9 => P_ATTRIBUTE9
260 , P_ATTRIBUTE10 => P_ATTRIBUTE10
261 , P_ATTRIBUTE11 => P_ATTRIBUTE11
262 , P_ATTRIBUTE12 => P_ATTRIBUTE12
263 , P_ATTRIBUTE13 => P_ATTRIBUTE13
264 , P_ATTRIBUTE14 => P_ATTRIBUTE14
265 , P_ATTRIBUTE15 => P_ATTRIBUTE15
266 , P_ATTRIBUTE16 => P_ATTRIBUTE16
267 , P_ATTRIBUTE17 => P_ATTRIBUTE17
268 , P_ATTRIBUTE18 => P_ATTRIBUTE18
269 , P_ATTRIBUTE19 => P_ATTRIBUTE19
270 , P_ATTRIBUTE20 => P_ATTRIBUTE20
271 , P_ATTRIBUTE21 => P_ATTRIBUTE21
272 , P_ATTRIBUTE22 => P_ATTRIBUTE22
273 , P_ATTRIBUTE23 => P_ATTRIBUTE23
274 , P_ATTRIBUTE24 => P_ATTRIBUTE24
278 , P_ATTRIBUTE28 => P_ATTRIBUTE28
275 , P_ATTRIBUTE25 => P_ATTRIBUTE25
276 , P_ATTRIBUTE26 => P_ATTRIBUTE26
277 , P_ATTRIBUTE27 => P_ATTRIBUTE27
279 , P_ATTRIBUTE29 => P_ATTRIBUTE29
280 , P_ATTRIBUTE30 => P_ATTRIBUTE30
281 , P_OBJECT_VERSION_NUMBER => l_object_version_number
282 , P_VACANCY_ID => l_vacancy_id
283 , p_inv_pos_grade_warning => l_inv_pos_grade_warning
284 , p_inv_job_grade_warning => l_inv_job_grade_warning
285 );
286 exception
287 when hr_api.cannot_find_prog_unit then
288 hr_api.cannot_find_prog_unit_error
289 (p_module_name => 'create_vacancy'
290 ,p_hook_type => 'AP'
291 );
292 end;
293 --
294 -- When in validation only mode raise the Validate_Enabled exception
295 --
296 if p_validate then
297 raise hr_api.validate_enabled;
298 end if;
299 --
300 -- Set all output arguments
301 --
302 P_VACANCY_ID := l_vacancy_id;
303 P_OBJECT_VERSION_NUMBER := l_object_version_number;
304 p_inv_pos_grade_warning := l_inv_pos_grade_warning;
305 p_inv_job_grade_warning := l_inv_job_grade_warning;
306 --
307 hr_utility.set_location(' Leaving:'||l_proc, 70);
308 exception
309 when hr_api.validate_enabled then
310 --
311 -- As the Validate_Enabled exception has been raised
312 -- we must rollback to the savepoint
313 --
314 rollback to create_vacancy;
315 --
316 -- Only set output warning arguments
317 -- (Any key or derived arguments must be set to null
318 -- when validation only mode is being used.)
319 --
320 P_VACANCY_ID := null;
321 P_OBJECT_VERSION_NUMBER := null;
322 p_inv_pos_grade_warning := l_inv_pos_grade_warning;
323 p_inv_job_grade_warning := l_inv_job_grade_warning;
324 hr_utility.set_location(' Leaving:'||l_proc, 80);
325 when others then
326 --
327 -- A validation or unexpected error has occured
328 --
329 P_VACANCY_ID := null;
330 P_OBJECT_VERSION_NUMBER := null;
331 p_inv_pos_grade_warning :=null;
332 p_inv_job_grade_warning := null;
333
334 rollback to create_vacancy;
335 hr_utility.set_location(' Leaving:'||l_proc, 90);
336 raise;
337 end create_vacancy;
338 --
339 -- ----------------------------------------------------------------------------
340 -- |--------------------------< update_vacancy >--------------------------|
341 -- ----------------------------------------------------------------------------
342 --
343 procedure update_vacancy
344 (
345 P_VALIDATE in boolean default false
346 , P_EFFECTIVE_DATE in date default null
347 , P_VACANCY_ID in number
348 , P_OBJECT_VERSION_NUMBER in out nocopy number
349 , P_DATE_FROM in date default hr_api.g_date
350 , P_POSITION_ID in number default hr_api.g_number
351 , P_JOB_ID in number default hr_api.g_number
352 , P_GRADE_ID in number default hr_api.g_number
353 , P_ORGANIZATION_ID in number default hr_api.g_number
354 , P_PEOPLE_GROUP_ID in number default hr_api.g_number
355 , P_LOCATION_ID in number default hr_api.g_number
356 , P_RECRUITER_ID in number default hr_api.g_number
357 , P_DATE_TO in date default hr_api.g_date
358 , P_SECURITY_METHOD in varchar2 default hr_api.g_varchar2
359 , P_DESCRIPTION in varchar2 default hr_api.g_varchar2
360 , P_NUMBER_OF_OPENINGS in number default hr_api.g_number
361 , P_STATUS in varchar2 default hr_api.g_varchar2
362 , P_BUDGET_MEASUREMENT_TYPE in varchar2 default hr_api.g_varchar2
363 , P_BUDGET_MEASUREMENT_VALUE in number default hr_api.g_number
364 , P_VACANCY_CATEGORY in varchar2 default hr_api.g_varchar2
365 , P_MANAGER_ID in number default hr_api.g_number
366 , P_PRIMARY_POSTING_ID in number default hr_api.g_number
367 , P_ASSESSMENT_ID in number default hr_api.g_number
368 , P_ATTRIBUTE_CATEGORY in varchar2 default hr_api.g_varchar2
369 , P_ATTRIBUTE1 in varchar2 default hr_api.g_varchar2
370 , P_ATTRIBUTE2 in varchar2 default hr_api.g_varchar2
371 , P_ATTRIBUTE3 in varchar2 default hr_api.g_varchar2
372 , P_ATTRIBUTE4 in varchar2 default hr_api.g_varchar2
373 , P_ATTRIBUTE5 in varchar2 default hr_api.g_varchar2
374 , P_ATTRIBUTE6 in varchar2 default hr_api.g_varchar2
375 , P_ATTRIBUTE7 in varchar2 default hr_api.g_varchar2
376 , P_ATTRIBUTE8 in varchar2 default hr_api.g_varchar2
377 , P_ATTRIBUTE9 in varchar2 default hr_api.g_varchar2
378 , P_ATTRIBUTE10 in varchar2 default hr_api.g_varchar2
379 , P_ATTRIBUTE11 in varchar2 default hr_api.g_varchar2
383 , P_ATTRIBUTE15 in varchar2 default hr_api.g_varchar2
380 , P_ATTRIBUTE12 in varchar2 default hr_api.g_varchar2
381 , P_ATTRIBUTE13 in varchar2 default hr_api.g_varchar2
382 , P_ATTRIBUTE14 in varchar2 default hr_api.g_varchar2
384 , P_ATTRIBUTE16 in varchar2 default hr_api.g_varchar2
385 , P_ATTRIBUTE17 in varchar2 default hr_api.g_varchar2
386 , P_ATTRIBUTE18 in varchar2 default hr_api.g_varchar2
387 , P_ATTRIBUTE19 in varchar2 default hr_api.g_varchar2
388 , P_ATTRIBUTE20 in varchar2 default hr_api.g_varchar2
389 , P_ATTRIBUTE21 in varchar2 default hr_api.g_varchar2
390 , P_ATTRIBUTE22 in varchar2 default hr_api.g_varchar2
391 , P_ATTRIBUTE23 in varchar2 default hr_api.g_varchar2
392 , P_ATTRIBUTE24 in varchar2 default hr_api.g_varchar2
393 , P_ATTRIBUTE25 in varchar2 default hr_api.g_varchar2
394 , P_ATTRIBUTE26 in varchar2 default hr_api.g_varchar2
395 , P_ATTRIBUTE27 in varchar2 default hr_api.g_varchar2
396 , P_ATTRIBUTE28 in varchar2 default hr_api.g_varchar2
397 , P_ATTRIBUTE29 in varchar2 default hr_api.g_varchar2
398 , P_ATTRIBUTE30 in varchar2 default hr_api.g_varchar2
399 , P_ASSIGNMENT_CHANGED out nocopy boolean
400 ,p_inv_pos_grade_warning out nocopy boolean
401 ,p_inv_job_grade_warning out nocopy boolean
402 )
403 is
404 --
405 -- Declare cursors and local variables
406 --
407 l_proc varchar2(72) := g_package||'update_vacancy';
408 l_date_from date := trunc(P_DATE_FROM);
409 l_date_to date := trunc(P_DATE_TO);
410 l_effective_date date;
411 l_asg_found number := null;
412 l_job_id number := null;
413 l_grade_id number := null;
414 l_people_group_id number := null;
415 l_organization_id number := null;
416 l_position_id number := null;
417 l_location_id number := null;
418 l_manager_id number := null;
419 l_recruiter_id number := null;
420 l_con_segments varchar2(100) := null;
421 l_comment_id number := null;
422 l_assignment_changed boolean := false;
423 l_object_version_number number := P_OBJECT_VERSION_NUMBER;
424 l_asg_ovn number;
425 l_inv_pos_grade_warning boolean;
426 l_inv_job_grade_warning boolean;
427 l_temp_ovn number := P_OBJECT_VERSION_NUMBER;
428 cursor csr_asg is
429 select assignment_id,paf.object_version_number
430 from per_all_assignments_f paf,
431 per_all_vacancies pav
432 where paf.vacancy_id = p_vacancy_id
433 and paf.vacancy_id = pav.vacancy_id
434 and paf.assignment_type = 'A';
435 begin
436 hr_utility.set_location('Entering:'|| l_proc, 10);
437 --
438 -- Issue a savepoint
439 --
440 savepoint update_vacancy;
441 --
442 -- Truncate the time portion from all IN date parameters
443 --
444 if p_effective_date is null then
445 l_effective_date:=l_date_from;
446 else
447 l_effective_date:=trunc(p_effective_date);
448 end if;
449 --
450 -- Call Before Process User Hook
451 --
452 begin
453 PER_VACANCY_BK2.update_vacancy_b
454 (P_EFFECTIVE_DATE => l_effective_date
455 , P_VACANCY_ID => P_VACANCY_ID
456 , P_OBJECT_VERSION_NUMBER => l_object_version_number
457 , P_DATE_FROM => l_date_from
458 , P_POSITION_ID => P_POSITION_ID
459 , P_JOB_ID => P_JOB_ID
460 , P_GRADE_ID => P_GRADE_ID
461 , P_ORGANIZATION_ID => P_ORGANIZATION_ID
462 , P_PEOPLE_GROUP_ID => P_PEOPLE_GROUP_ID
463 , P_LOCATION_ID => P_LOCATION_ID
464 , P_RECRUITER_ID => P_RECRUITER_ID
465 , P_DATE_TO => l_date_to
466 , P_SECURITY_METHOD => P_SECURITY_METHOD
467 , P_DESCRIPTION => P_DESCRIPTION
468 , P_NUMBER_OF_OPENINGS => P_NUMBER_OF_OPENINGS
469 , P_STATUS => P_STATUS
470 , P_BUDGET_MEASUREMENT_TYPE => P_BUDGET_MEASUREMENT_TYPE
471 , P_BUDGET_MEASUREMENT_VALUE => P_BUDGET_MEASUREMENT_VALUE
472 , P_VACANCY_CATEGORY => P_VACANCY_CATEGORY
473 , P_MANAGER_ID => P_MANAGER_ID
474 , P_PRIMARY_POSTING_ID => P_PRIMARY_POSTING_ID
475 , P_ASSESSMENT_ID => P_ASSESSMENT_ID
476 , P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
477 , P_ATTRIBUTE1 => P_ATTRIBUTE1
478 , P_ATTRIBUTE2 => P_ATTRIBUTE2
479 , P_ATTRIBUTE3 => P_ATTRIBUTE3
480 , P_ATTRIBUTE4 => P_ATTRIBUTE4
481 , P_ATTRIBUTE5 => P_ATTRIBUTE5
485 , P_ATTRIBUTE9 => P_ATTRIBUTE9
482 , P_ATTRIBUTE6 => P_ATTRIBUTE6
483 , P_ATTRIBUTE7 => P_ATTRIBUTE7
484 , P_ATTRIBUTE8 => P_ATTRIBUTE8
486 , P_ATTRIBUTE10 => P_ATTRIBUTE10
487 , P_ATTRIBUTE11 => P_ATTRIBUTE11
488 , P_ATTRIBUTE12 => P_ATTRIBUTE12
489 , P_ATTRIBUTE13 => P_ATTRIBUTE13
490 , P_ATTRIBUTE14 => P_ATTRIBUTE14
491 , P_ATTRIBUTE15 => P_ATTRIBUTE15
492 , P_ATTRIBUTE16 => P_ATTRIBUTE16
493 , P_ATTRIBUTE17 => P_ATTRIBUTE17
494 , P_ATTRIBUTE18 => P_ATTRIBUTE18
495 , P_ATTRIBUTE19 => P_ATTRIBUTE19
496 , P_ATTRIBUTE20 => P_ATTRIBUTE20
497 , P_ATTRIBUTE21 => P_ATTRIBUTE21
498 , P_ATTRIBUTE22 => P_ATTRIBUTE22
499 , P_ATTRIBUTE23 => P_ATTRIBUTE23
500 , P_ATTRIBUTE24 => P_ATTRIBUTE24
501 , P_ATTRIBUTE25 => P_ATTRIBUTE25
502 , P_ATTRIBUTE26 => P_ATTRIBUTE26
503 , P_ATTRIBUTE27 => P_ATTRIBUTE27
504 , P_ATTRIBUTE28 => P_ATTRIBUTE28
505 , P_ATTRIBUTE29 => P_ATTRIBUTE29
506 , P_ATTRIBUTE30 => P_ATTRIBUTE30
507 );
508 exception
509 when hr_api.cannot_find_prog_unit then
510 hr_api.cannot_find_prog_unit_error
511 (p_module_name => 'update_vacancy'
512 ,p_hook_type => 'BP'
513 );
514 end;
515 --
516 hr_utility.set_location(l_proc, 20);
517 --
518 -- Validation in addition to Row Handlers
519 --
520 per_vac_upd.upd
521 (p_effective_date => l_effective_date
522 ,p_vacancy_id => P_VACANCY_ID
523 ,p_object_version_number => l_object_version_number
524 ,p_date_from => l_date_from
525 ,p_position_id => P_POSITION_ID
526 ,p_job_id => P_JOB_ID
527 ,p_grade_id => P_GRADE_ID
528 ,p_organization_id => P_ORGANIZATION_ID
529 ,p_people_group_id => P_PEOPLE_GROUP_ID
530 ,p_location_id => P_LOCATION_ID
531 ,p_recruiter_id => P_RECRUITER_ID
532 ,p_date_to => l_date_to
533 ,p_number_of_openings => P_NUMBER_OF_OPENINGS
534 ,p_status => P_STATUS
535 ,p_attribute_category => P_ATTRIBUTE_CATEGORY
536 ,p_attribute1 => P_ATTRIBUTE1
537 ,p_attribute2 => P_ATTRIBUTE2
538 ,p_attribute3 => P_ATTRIBUTE3
539 ,p_attribute4 => P_ATTRIBUTE4
540 ,p_attribute5 => P_ATTRIBUTE5
541 ,p_attribute6 => P_ATTRIBUTE6
542 ,p_attribute7 => P_ATTRIBUTE7
543 ,p_attribute8 => P_ATTRIBUTE8
544 ,p_attribute9 => P_ATTRIBUTE9
545 ,p_attribute10 => P_ATTRIBUTE10
546 ,p_attribute11 => P_ATTRIBUTE11
547 ,p_attribute12 => P_ATTRIBUTE12
548 ,p_attribute13 => P_ATTRIBUTE13
549 ,p_attribute14 => P_ATTRIBUTE14
550 ,p_attribute15 => P_ATTRIBUTE15
551 ,p_attribute16 => P_ATTRIBUTE16
552 ,p_attribute17 => P_ATTRIBUTE17
553 ,p_attribute18 => P_ATTRIBUTE18
554 ,p_attribute19 => P_ATTRIBUTE19
555 ,p_attribute20 => P_ATTRIBUTE20
556 ,p_vacancy_category => P_VACANCY_CATEGORY
557 ,p_budget_measurement_type => P_BUDGET_MEASUREMENT_TYPE
558 ,p_budget_measurement_value => P_BUDGET_MEASUREMENT_VALUE
559 ,p_manager_id => P_MANAGER_ID
560 ,p_security_method => P_SECURITY_METHOD
561 ,p_primary_posting_id => P_PRIMARY_POSTING_ID
562 ,p_assessment_id => P_ASSESSMENT_ID
563 ,p_description => P_DESCRIPTION
564 ,p_inv_pos_grade_warning => l_inv_pos_grade_warning
565 ,p_inv_job_grade_warning => l_inv_job_grade_warning
566 );
567 --
568 hr_utility.set_location(l_proc, 30);
569 --
570 -- look to see if the assignment needs updating
571 --
572 l_ASSIGNMENT_CHANGED := FALSE;
573 --
574 if (p_organization_id <> per_vac_shd.g_old_rec.organization_id)
575 and (p_organization_id <> hr_api.g_number)
576 then
577 l_ASSIGNMENT_CHANGED := TRUE;
578 l_organization_id := p_organization_id;
579 l_position_id := p_position_id;
580 end if;
581 --
582 if (p_job_id <> per_vac_shd.g_old_rec.job_id)
583 and (p_job_id <> hr_api.g_number)
584 then
585 l_ASSIGNMENT_CHANGED := TRUE;
586 l_job_id := p_job_id;
587 l_position_id := p_position_id;
588 end if;
589 --
590 if (p_grade_id <> per_vac_shd.g_old_rec.grade_id)
591 and (p_grade_id <> hr_api.g_number)
592 then
593 l_ASSIGNMENT_CHANGED := TRUE;
594 l_grade_id := p_grade_id;
595 end if;
596 --
597 if (p_people_group_id <> per_vac_shd.g_old_rec.people_group_id)
598 and (p_people_group_id <> hr_api.g_number)
599 then
600 l_ASSIGNMENT_CHANGED := TRUE;
604 if (p_position_id <> per_vac_shd.g_old_rec.position_id)
601 l_people_group_id := p_people_group_id;
602 end if;
603 --
605 and (p_position_id <> hr_api.g_number)
606 then
607 l_ASSIGNMENT_CHANGED := TRUE;
608 l_position_id := p_position_id;
609 end if;
610 --
611 if (p_location_id <> per_vac_shd.g_old_rec.location_id)
612 and (p_location_id <> hr_api.g_number)
613 then
614 l_ASSIGNMENT_CHANGED := TRUE;
615 l_location_id := p_location_id;
616 end if;
617 --
618 if (p_recruiter_id <> per_vac_shd.g_old_rec.recruiter_id)
619 and (p_recruiter_id <> hr_api.g_number)
620 then
621 l_ASSIGNMENT_CHANGED := TRUE;
622 l_recruiter_id := p_recruiter_id;
623 end if;
624 --
625 if (p_manager_id <> per_vac_shd.g_old_rec.manager_id)
626 and (p_manager_id <> hr_api.g_number)
627 then
628 l_ASSIGNMENT_CHANGED := TRUE;
629 l_manager_id := p_manager_id;
630 end if;
631 --
632 if l_ASSIGNMENT_CHANGED = TRUE then
633 --
634 -- fix for the bug 5719667
635 l_position_id := p_position_id;
636 hr_utility.set_location(l_proc, 40);
637 --
638 update per_all_assignments_f asg
639 set asg.organization_id = nvl(l_organization_id, asg.organization_id)
640 ,asg.job_id = nvl(l_job_id, asg.job_id)
641 ,asg.grade_id = nvl(l_grade_id, asg.grade_id)
642 ,asg.people_group_id = nvl(l_people_group_id, asg.people_group_id)
643 ,asg.location_id = nvl(l_location_id, asg.location_id)
644 ,asg.recruiter_id = nvl(l_recruiter_id, asg.recruiter_id)
645 ,asg.supervisor_id = nvl(l_manager_id, asg.supervisor_id)
646 ,asg.position_id = decode
647 (l_organization_id||'.'||l_job_id,
648 per_vac_shd.g_old_rec.organization_id||'.'||per_vac_shd.g_old_rec.job_id,
649 nvl(l_position_id, asg.position_id),
650 l_position_id)
651 where asg.assignment_type = 'A'
652 and asg.vacancy_id = p_vacancy_id
653 and ( asg.organization_id <> nvl(l_organization_id,asg.organization_id)
654 or nvl(asg.job_id, -1) <> nvl(l_job_id,nvl(asg.job_id, -1))
655 or nvl(asg.grade_id, -1) <> nvl(l_grade_id,nvl(asg.grade_id, -1))
656 or nvl(asg.people_group_id, -1) <> nvl(l_people_group_id, nvl(asg.people_group_id, -1))
657 or nvl(asg.position_id, -1) <> nvl(l_position_id,nvl(asg.position_id, -1))
658 or nvl(asg.location_id, -1) <> nvl(l_location_id, nvl(asg.location_id, -1))
659 or nvl(asg.recruiter_id, -1) <> nvl(l_recruiter_id, nvl(asg.recruiter_id, -1))
660 or nvl(asg.supervisor_id, -1) <> nvl(l_manager_id, nvl(asg.supervisor_id, -1))
661 );
662 end if;
663 --
664 hr_utility.set_location(l_proc, 50);
665 --
666 -- Call After Process User Hook
667 --
668 begin
669 --
670 PER_VACANCY_BK2.update_vacancy_a(
671 P_EFFECTIVE_DATE => l_effective_date
672 ,P_VACANCY_ID => P_VACANCY_ID
673 ,P_OBJECT_VERSION_NUMBER => p_object_version_number
674 ,P_DATE_FROM => l_date_from
675 ,P_POSITION_ID => P_POSITION_ID
676 ,P_JOB_ID => P_JOB_ID
677 ,P_GRADE_ID => P_GRADE_ID
678 ,P_ORGANIZATION_ID => P_ORGANIZATION_ID
679 ,P_PEOPLE_GROUP_ID => P_PEOPLE_GROUP_ID
680 ,P_LOCATION_ID => P_LOCATION_ID
681 ,P_RECRUITER_ID => P_RECRUITER_ID
682 ,P_DATE_TO => l_date_to
683 ,P_SECURITY_METHOD => P_SECURITY_METHOD
684 ,P_DESCRIPTION => P_DESCRIPTION
685 ,P_NUMBER_OF_OPENINGS => P_NUMBER_OF_OPENINGS
686 ,P_STATUS => P_STATUS
687 ,P_BUDGET_MEASUREMENT_TYPE => P_BUDGET_MEASUREMENT_TYPE
688 ,P_BUDGET_MEASUREMENT_VALUE => P_BUDGET_MEASUREMENT_VALUE
689 ,P_VACANCY_CATEGORY => P_VACANCY_CATEGORY
690 ,P_MANAGER_ID => P_MANAGER_ID
691 ,P_PRIMARY_POSTING_ID => P_PRIMARY_POSTING_ID
692 ,P_ASSESSMENT_ID => P_ASSESSMENT_ID
693 ,P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
694 ,P_ATTRIBUTE1 => P_ATTRIBUTE1
695 ,P_ATTRIBUTE2 => P_ATTRIBUTE2
696 ,P_ATTRIBUTE3 => P_ATTRIBUTE3
697 ,P_ATTRIBUTE4 => P_ATTRIBUTE4
698 ,P_ATTRIBUTE5 => P_ATTRIBUTE5
699 ,P_ATTRIBUTE6 => P_ATTRIBUTE6
700 ,P_ATTRIBUTE7 => P_ATTRIBUTE7
701 ,P_ATTRIBUTE8 => P_ATTRIBUTE8
702 ,P_ATTRIBUTE9 => P_ATTRIBUTE9
703 ,P_ATTRIBUTE10 => P_ATTRIBUTE10
704 ,P_ATTRIBUTE11 => P_ATTRIBUTE11
705 ,P_ATTRIBUTE12 => P_ATTRIBUTE12
706 ,P_ATTRIBUTE13 => P_ATTRIBUTE13
707 ,P_ATTRIBUTE14 => P_ATTRIBUTE14
708 ,P_ATTRIBUTE15 => P_ATTRIBUTE15
709 ,P_ATTRIBUTE16 => P_ATTRIBUTE16
710 ,P_ATTRIBUTE17 => P_ATTRIBUTE17
711 ,P_ATTRIBUTE18 => P_ATTRIBUTE18
715 ,P_ATTRIBUTE22 => P_ATTRIBUTE22
712 ,P_ATTRIBUTE19 => P_ATTRIBUTE19
713 ,P_ATTRIBUTE20 => P_ATTRIBUTE20
714 ,P_ATTRIBUTE21 => P_ATTRIBUTE21
716 ,P_ATTRIBUTE23 => P_ATTRIBUTE23
717 ,P_ATTRIBUTE24 => P_ATTRIBUTE24
718 ,P_ATTRIBUTE25 => P_ATTRIBUTE25
719 ,P_ATTRIBUTE26 => P_ATTRIBUTE26
720 ,P_ATTRIBUTE27 => P_ATTRIBUTE27
721 ,P_ATTRIBUTE28 => P_ATTRIBUTE28
722 ,P_ATTRIBUTE29 => P_ATTRIBUTE29
723 ,P_ATTRIBUTE30 => P_ATTRIBUTE30
724 ,P_ASSIGNMENT_CHANGED => l_assignment_changed
725 ,p_inv_pos_grade_warning => l_inv_pos_grade_warning
726 ,p_inv_job_grade_warning => l_inv_job_grade_warning
727
728 );
729 exception
730 when hr_api.cannot_find_prog_unit then
731 hr_api.cannot_find_prog_unit_error
732 (p_module_name => 'update_vacancy'
733 ,p_hook_type => 'AP'
734 );
735 end;
736 --
737 -- When in validation only mode raise the Validate_Enabled exception
738 --
739 if p_validate then
740 raise hr_api.validate_enabled;
741 end if;
742 --
743 -- Set all output arguments
744 --
745 P_ASSIGNMENT_CHANGED := l_assignment_changed;
746 P_OBJECT_VERSION_NUMBER := l_object_version_number;
747 p_inv_pos_grade_warning := l_inv_pos_grade_warning;
748 p_inv_job_grade_warning := l_inv_job_grade_warning;
749 --
750 hr_utility.set_location(' Leaving:'||l_proc, 70);
751 exception
752 when hr_api.validate_enabled then
753 --
754 -- As the Validate_Enabled exception has been raised
755 -- we must rollback to the savepoint
756 --
757 rollback to update_vacancy;
758 p_inv_pos_grade_warning := l_inv_pos_grade_warning;
759 p_inv_job_grade_warning := l_inv_job_grade_warning;
760 --
761 -- Only set output warning arguments
762 -- (Any key or derived arguments must be set to null
763 -- when validation only mode is being used.)
764 --
765 hr_utility.set_location(' Leaving:'||l_proc, 80);
766 when others then
767 --
768 -- A validation or unexpected error has occured
769 --
770 p_assignment_changed := null;
771 P_OBJECT_VERSION_NUMBER := l_temp_ovn;
772 p_inv_pos_grade_warning := null;
773 p_inv_job_grade_warning := null;
774
775 rollback to update_vacancy;
776 hr_utility.set_location(' Leaving:'||l_proc, 90);
777 raise;
778 end update_vacancy;
779 --
780 -- ----------------------------------------------------------------------------
781 -- |--------------------------< delete_vacancy >--------------------------|
782 -- ----------------------------------------------------------------------------
783 --
784 procedure delete_vacancy
785 (
786 P_VALIDATE in boolean default false
787 , P_OBJECT_VERSION_NUMBER in number
788 , P_VACANCY_ID in number
789 )
790 is
791 --
792 -- Declare cursors and local variables
793 --
794
795 l_proc varchar2(72) := g_package||'delete_vacancy';
796 begin
797 hr_utility.set_location('Entering:'|| l_proc, 10);
798 --
799 -- Issue a savepoint
800 --
801 savepoint delete_vacancy;
802 --
803 -- Truncate the time portion from all IN date parameters
804 --
805 --
806 -- Call Before Process User Hook
807 --
808 begin
809
810 PER_VACANCY_BK3.delete_vacancy_b
811 (
812 P_OBJECT_VERSION_NUMBER
813 ,P_VACANCY_ID
814 );
815 exception
816 when hr_api.cannot_find_prog_unit then
817 hr_api.cannot_find_prog_unit_error
818 (p_module_name => 'delete_vacancy'
819 ,p_hook_type => 'BP'
820 );
821 end;
822 --
823 -- Validation in addition to Row Handlers
824 --
825 per_vac_del.del(
826 p_vacancy_id => P_VACANCY_ID
827 ,p_object_version_number => P_OBJECT_VERSION_NUMBER
828 );
829 --
830 -- Process Logic
831 --
832 --
833 -- Call After Process User Hook
834 --
835 begin
836 PER_VACANCY_BK3.delete_vacancy_a
837 (
838 P_OBJECT_VERSION_NUMBER
839 ,P_VACANCY_ID
840 );
841 exception
842 when hr_api.cannot_find_prog_unit then
843 hr_api.cannot_find_prog_unit_error
844 (p_module_name => 'delete_vacancy'
845 ,p_hook_type => 'AP'
846 );
847 end;
848 --
849 -- When in validation only mode raise the Validate_Enabled exception
850 --
851 if p_validate then
852 raise hr_api.validate_enabled;
853 end if;
854 --
855 -- Set all output arguments
856 --
857 --
858 hr_utility.set_location(' Leaving:'||l_proc, 70);
859 exception
860 when hr_api.validate_enabled then
861 --
862 -- As the Validate_Enabled exception has been raised
863 -- we must rollback to the savepoint
864 --
865 rollback to delete_vacancy;
866 --
867 -- Only set output warning arguments
868 -- (Any key or derived arguments must be set to null
869 -- when validation only mode is being used.)
870 --
871 hr_utility.set_location(' Leaving:'||l_proc, 80);
872 when others then
873 --
874 -- A validation or unexpected error has occured
875 --
876 rollback to delete_vacancy;
877 hr_utility.set_location(' Leaving:'||l_proc, 90);
878 raise;
879 end delete_vacancy;
880 --
881 end PER_VACANCY_API;