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