[Home] [Help]
PACKAGE BODY: APPS.PER_VACANCY_SWI
Source
1 Package Body per_vacancy_swi As
2 /* $Header: pevacswi.pkb 120.8 2008/02/27 15:17:53 amikukum noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'per_vacancy_swi.';
7 g_commiting_via_workflow varchar2(30);
8 --
9 -- ----------------------------------------------------------------------------
10 -- |----------------------------< create_vacancy >----------------------------|
11 -- ----------------------------------------------------------------------------
12 PROCEDURE create_vacancy
13 (p_validate in number default hr_api.g_false_num
14 ,p_effective_date in date
15 ,p_requisition_id in number
16 ,p_date_from in date
17 ,p_name in varchar2
18 ,p_security_method in varchar2 default null
19 ,p_business_group_id in number
20 ,p_position_id in number default null
21 ,p_job_id in number default null
22 ,p_grade_id in number default null
23 ,p_organization_id in number default null
24 ,p_people_group_id in number default null
25 ,p_location_id in number default null
26 ,p_recruiter_id in number default null
27 ,p_date_to in date default null
28 ,p_description in varchar2 default null
29 ,p_number_of_openings in number default null
30 ,p_status in varchar2 default null
31 ,p_budget_measurement_type in varchar2 default null
32 ,p_budget_measurement_value in number default null
33 ,p_vacancy_category in varchar2 default null
34 ,p_manager_id in number default null
35 ,p_primary_posting_id in number default null
36 ,p_assessment_id in number default null
37 ,p_attribute_category in varchar2 default null
38 ,p_attribute1 in varchar2 default null
39 ,p_attribute2 in varchar2 default null
40 ,p_attribute3 in varchar2 default null
41 ,p_attribute4 in varchar2 default null
42 ,p_attribute5 in varchar2 default null
43 ,p_attribute6 in varchar2 default null
44 ,p_attribute7 in varchar2 default null
45 ,p_attribute8 in varchar2 default null
46 ,p_attribute9 in varchar2 default null
47 ,p_attribute10 in varchar2 default null
48 ,p_attribute11 in varchar2 default null
49 ,p_attribute12 in varchar2 default null
50 ,p_attribute13 in varchar2 default null
51 ,p_attribute14 in varchar2 default null
52 ,p_attribute15 in varchar2 default null
53 ,p_attribute16 in varchar2 default null
54 ,p_attribute17 in varchar2 default null
55 ,p_attribute18 in varchar2 default null
56 ,p_attribute19 in varchar2 default null
57 ,p_attribute20 in varchar2 default null
58 ,p_attribute21 in varchar2 default null
59 ,p_attribute22 in varchar2 default null
60 ,p_attribute23 in varchar2 default null
61 ,p_attribute24 in varchar2 default null
62 ,p_attribute25 in varchar2 default null
63 ,p_attribute26 in varchar2 default null
64 ,p_attribute27 in varchar2 default null
65 ,p_attribute28 in varchar2 default null
66 ,p_attribute29 in varchar2 default null
67 ,p_attribute30 in varchar2 default null
68 ,p_object_version_number out nocopy number
69 ,p_vacancy_id in number
70 ,p_return_status out nocopy varchar2
71 ) is
72 --
73 -- Variables for API Boolean parameters
74 l_validate boolean;
75 l_inv_pos_grade_warning boolean;
76 l_inv_job_grade_warning boolean;
77 --
78 -- Variables for IN/OUT parameters
79 --
80 -- Other variables
81 l_vacancy_id number;
82 l_proc varchar2(72) := g_package ||'create_vacancy';
83 Begin
84 hr_utility.set_location(' Entering:' || l_proc,10);
85 --
86 -- Issue a savepoint
87 --
88 savepoint create_vacancy_swi;
89 --
90 -- Initialise Multiple Message Detection
91 --
92 hr_multi_message.enable_message_list;
93 --
94 -- Remember IN OUT parameter IN values
95 --
96 --
97 -- Convert constant values to their corresponding boolean value
98 --
99 l_validate :=
100 hr_api.constant_to_boolean
101 (p_constant_value => p_validate);
102 --
103 -- Register Surrogate ID or user key values
104 --
105 per_vac_ins.set_base_key_value
106 (p_vacancy_id => p_vacancy_id
107 );
108 --
109 -- Call API
110 --
111 per_vacancy_api.create_vacancy
112 (p_validate => l_validate
113 ,p_effective_date => p_effective_date
114 ,p_requisition_id => p_requisition_id
115 ,p_date_from => p_date_from
116 ,p_name => p_name
117 ,p_security_method => p_security_method
118 ,p_business_group_id => p_business_group_id
119 ,p_position_id => p_position_id
120 ,p_job_id => p_job_id
121 ,p_grade_id => p_grade_id
122 ,p_organization_id => p_organization_id
123 ,p_people_group_id => p_people_group_id
124 ,p_location_id => p_location_id
125 ,p_recruiter_id => p_recruiter_id
126 ,p_date_to => p_date_to
127 ,p_description => p_description
128 ,p_number_of_openings => p_number_of_openings
129 ,p_status => p_status
130 ,p_budget_measurement_type => p_budget_measurement_type
131 ,p_budget_measurement_value => p_budget_measurement_value
132 ,p_vacancy_category => p_vacancy_category
133 ,p_manager_id => p_manager_id
134 ,p_primary_posting_id => p_primary_posting_id
135 ,p_assessment_id => p_assessment_id
136 ,p_attribute_category => p_attribute_category
137 ,p_attribute1 => p_attribute1
138 ,p_attribute2 => p_attribute2
139 ,p_attribute3 => p_attribute3
140 ,p_attribute4 => p_attribute4
141 ,p_attribute5 => p_attribute5
142 ,p_attribute6 => p_attribute6
143 ,p_attribute7 => p_attribute7
144 ,p_attribute8 => p_attribute8
145 ,p_attribute9 => p_attribute9
146 ,p_attribute10 => p_attribute10
147 ,p_attribute11 => p_attribute11
148 ,p_attribute12 => p_attribute12
149 ,p_attribute13 => p_attribute13
150 ,p_attribute14 => p_attribute14
151 ,p_attribute15 => p_attribute15
152 ,p_attribute16 => p_attribute16
153 ,p_attribute17 => p_attribute17
154 ,p_attribute18 => p_attribute18
155 ,p_attribute19 => p_attribute19
156 ,p_attribute20 => p_attribute20
157 ,p_attribute21 => p_attribute21
158 ,p_attribute22 => p_attribute22
159 ,p_attribute23 => p_attribute23
160 ,p_attribute24 => p_attribute24
161 ,p_attribute25 => p_attribute25
162 ,p_attribute26 => p_attribute26
163 ,p_attribute27 => p_attribute27
164 ,p_attribute28 => p_attribute28
165 ,p_attribute29 => p_attribute29
166 ,p_attribute30 => p_attribute30
167 ,p_object_version_number => p_object_version_number
168 ,p_vacancy_id => l_vacancy_id
169 ,p_inv_pos_grade_warning => l_inv_pos_grade_warning
170 ,p_inv_job_grade_warning => l_inv_job_grade_warning
171 );
172 --
173 if (nvl(g_commiting_via_workflow,'FALSE')='FALSE') then
174 -- we are not commiting from workflow, so check for warnings
175 --
176 -- Convert API warning boolean parameter values to specific
177 -- messages and add them to Multiple Message List
178 --
179 if l_inv_pos_grade_warning then
180 fnd_message.set_name('PER', 'HR_POS_GRADE_CHK');
181 hr_multi_message.add
182 (p_associated_column1 => 'PER_ALL_VACANCIES.POSITION_ID'
183 ,p_associated_column2 => 'PER_ALL_VACANCIES.GRADE_ID'
184 ,p_message_type => hr_multi_message.g_warning_msg
185 );
186 end if;
187 if l_inv_job_grade_warning then
188 fnd_message.set_name('PER', 'HR_JOB_GRADE_CHK');
189 hr_multi_message.add
190 (p_associated_column1 => 'PER_ALL_VACANCIES.JOB_ID'
191 ,p_associated_column2 => 'PER_ALL_VACANCIES.GRADE_ID'
192 ,p_message_type => hr_multi_message.g_warning_msg
193 );
194 end if;
195 end if;
196 --
197 -- Convert API non-warning boolean parameter values
198 --
199 --
200 -- Derive the API return status value based on whether
201 -- messages of any type exist in the Multiple Message List.
202 -- Also disable Multiple Message Detection.
203 --
204 p_return_status := hr_multi_message.get_return_status_disable;
205 hr_utility.set_location(' Leaving:' || l_proc,20);
206 --
207 exception
208 when hr_multi_message.error_message_exist then
209 --
210 -- Catch the Multiple Message List exception which
211 -- indicates API processing has been aborted because
212 -- at least one error message exists in the list.
213 --
214 rollback to create_vacancy_swi;
215 --
216 -- Reset IN OUT paramters and set OUT parameters
217 --
218 p_object_version_number := null;
219 p_return_status := hr_multi_message.get_return_status_disable;
220 hr_utility.set_location(' Leaving:' || l_proc,30);
221 when others then
222 --
223 -- When Multiple Message Detection is enabled catch
224 -- any Application specific or other unexpected
225 -- exceptions. Adding appropriate details to the
226 -- Multiple Message List. Otherwise re-raise
227 -- the error.
228 --
229 rollback to create_vacancy_swi;
230 if hr_multi_message.unexpected_error_add(l_proc) then
231 hr_utility.set_location(' Leaving:' || l_proc, 40);
232 raise;
233 end if;
234 --
235 -- Reset IN OUT parameters and set OUT parameters
236 --
237 p_object_version_number := null;
238 p_return_status := hr_multi_message.get_return_status_disable;
239 hr_utility.set_location(' Leaving: ' || l_proc, 50);
240 end create_vacancy;
241 -- ----------------------------------------------------------------------------
242 -- |----------------------------< delete_vacancy >----------------------------|
243 -- ----------------------------------------------------------------------------
244 PROCEDURE delete_vacancy
245 (p_validate in number default hr_api.g_false_num
246 ,p_object_version_number in number
247 ,p_vacancy_id in number
248 ,p_return_status out nocopy varchar2
249 ) is
250 --
251 -- Variables for API Boolean parameters
252 l_validate boolean;
253 --
254 -- Variables for IN/OUT parameters
255 --
256 -- Other variables
257 l_proc varchar2(72) := g_package ||'delete_vacancy';
258 Begin
259 hr_utility.set_location(' Entering:' || l_proc,10);
260 --
261 -- Issue a savepoint
262 --
263 savepoint delete_vacancy_swi;
264 --
265 -- Initialise Multiple Message Detection
266 --
267 hr_multi_message.enable_message_list;
268 --
269 -- Remember IN OUT parameter IN values
270 --
271 --
272 -- Convert constant values to their corresponding boolean value
273 --
274 l_validate :=
275 hr_api.constant_to_boolean
276 (p_constant_value => p_validate);
277 --
278 -- Register Surrogate ID or user key values
279 --
280 --
281 -- Call API
282 --
283 per_vacancy_api.delete_vacancy
284 (p_validate => l_validate
285 ,p_object_version_number => p_object_version_number
286 ,p_vacancy_id => p_vacancy_id
287 );
288 --
289 -- Convert API warning boolean parameter values to specific
290 -- messages and add them to Multiple Message List
291 --
292 --
293 -- Convert API non-warning boolean parameter values
294 --
295 --
296 -- Derive the API return status value based on whether
297 -- messages of any type exist in the Multiple Message List.
298 -- Also disable Multiple Message Detection.
299 --
300 p_return_status := hr_multi_message.get_return_status_disable;
301 hr_utility.set_location(' Leaving:' || l_proc,20);
302 --
303 exception
304 when hr_multi_message.error_message_exist then
305 --
306 -- Catch the Multiple Message List exception which
307 -- indicates API processing has been aborted because
308 -- at least one error message exists in the list.
309 --
310 rollback to delete_vacancy_swi;
311 --
312 -- Reset IN OUT paramters and set OUT parameters
313 --
314 p_return_status := hr_multi_message.get_return_status_disable;
315 hr_utility.set_location(' Leaving:' || l_proc,30);
316 when others then
317 --
318 -- When Multiple Message Detection is enabled catch
319 -- any Application specific or other unexpected
320 -- exceptions. Adding appropriate details to the
321 -- Multiple Message List. Otherwise re-raise
322 -- the error.
323 --
324 rollback to delete_vacancy_swi;
325 if hr_multi_message.unexpected_error_add(l_proc) then
326 hr_utility.set_location(' Leaving:' || l_proc, 40);
327 raise;
328 end if;
329 --
330 -- Reset IN OUT parameters and set OUT parameters
331 --
335 -- ----------------------------------------------------------------------------
332 p_return_status := hr_multi_message.get_return_status_disable;
333 hr_utility.set_location(' Leaving: ' || l_proc, 50);
334 end delete_vacancy;
336 -- |----------------------------< update_vacancy >----------------------------|
337 -- ----------------------------------------------------------------------------
338 PROCEDURE update_vacancy
339 (p_validate in number default hr_api.g_false_num
340 ,p_effective_date in date
341 ,p_vacancy_id in number
342 ,p_object_version_number in out nocopy number
343 ,p_date_from in date default hr_api.g_date
344 ,p_position_id in number default hr_api.g_number
345 ,p_job_id in number default hr_api.g_number
346 ,p_grade_id in number default hr_api.g_number
347 ,p_organization_id in number default hr_api.g_number
348 ,p_people_group_id in number default hr_api.g_number
349 ,p_location_id in number default hr_api.g_number
350 ,p_recruiter_id in number default hr_api.g_number
351 ,p_date_to in date default hr_api.g_date
352 ,p_security_method in varchar2 default hr_api.g_varchar2
353 ,p_description in varchar2 default hr_api.g_varchar2
354 ,p_number_of_openings in number default hr_api.g_number
355 ,p_status in varchar2 default hr_api.g_varchar2
356 ,p_budget_measurement_type in varchar2 default hr_api.g_varchar2
357 ,p_budget_measurement_value in number default hr_api.g_number
358 ,p_vacancy_category in varchar2 default hr_api.g_varchar2
359 ,p_manager_id in number default hr_api.g_number
360 ,p_primary_posting_id in number default hr_api.g_number
361 ,p_assessment_id in number default hr_api.g_number
362 ,p_attribute_category in varchar2 default hr_api.g_varchar2
363 ,p_attribute1 in varchar2 default hr_api.g_varchar2
364 ,p_attribute2 in varchar2 default hr_api.g_varchar2
365 ,p_attribute3 in varchar2 default hr_api.g_varchar2
366 ,p_attribute4 in varchar2 default hr_api.g_varchar2
367 ,p_attribute5 in varchar2 default hr_api.g_varchar2
368 ,p_attribute6 in varchar2 default hr_api.g_varchar2
369 ,p_attribute7 in varchar2 default hr_api.g_varchar2
370 ,p_attribute8 in varchar2 default hr_api.g_varchar2
371 ,p_attribute9 in varchar2 default hr_api.g_varchar2
372 ,p_attribute10 in varchar2 default hr_api.g_varchar2
373 ,p_attribute11 in varchar2 default hr_api.g_varchar2
374 ,p_attribute12 in varchar2 default hr_api.g_varchar2
375 ,p_attribute13 in varchar2 default hr_api.g_varchar2
376 ,p_attribute14 in varchar2 default hr_api.g_varchar2
377 ,p_attribute15 in varchar2 default hr_api.g_varchar2
378 ,p_attribute16 in varchar2 default hr_api.g_varchar2
379 ,p_attribute17 in varchar2 default hr_api.g_varchar2
380 ,p_attribute18 in varchar2 default hr_api.g_varchar2
381 ,p_attribute19 in varchar2 default hr_api.g_varchar2
382 ,p_attribute20 in varchar2 default hr_api.g_varchar2
383 ,p_attribute21 in varchar2 default hr_api.g_varchar2
384 ,p_attribute22 in varchar2 default hr_api.g_varchar2
385 ,p_attribute23 in varchar2 default hr_api.g_varchar2
386 ,p_attribute24 in varchar2 default hr_api.g_varchar2
387 ,p_attribute25 in varchar2 default hr_api.g_varchar2
388 ,p_attribute26 in varchar2 default hr_api.g_varchar2
389 ,p_attribute27 in varchar2 default hr_api.g_varchar2
390 ,p_attribute28 in varchar2 default hr_api.g_varchar2
391 ,p_attribute29 in varchar2 default hr_api.g_varchar2
392 ,p_attribute30 in varchar2 default hr_api.g_varchar2
393 ,p_assignment_changed out nocopy number
394 ,p_return_status out nocopy varchar2
395 ) is
396 --
397 -- Variables for API Boolean parameters
398 l_validate boolean;
399 l_assignment_changed boolean;
400 l_inv_pos_grade_warning boolean;
401 l_inv_job_grade_warning boolean;
402 --
403 -- Variables for IN/OUT parameters
404 l_object_version_number number;
405 --
406 -- Other variables
407 l_proc varchar2(72) := g_package ||'update_vacancy';
408 Begin
409 hr_utility.set_location(' Entering:' || l_proc,10);
410 --
411 -- Issue a savepoint
412 --
413 savepoint update_vacancy_swi;
414 --
415 -- Initialise Multiple Message Detection
416 --
417 hr_multi_message.enable_message_list;
418 --
419 -- Remember IN OUT parameter IN values
420 --
421 l_object_version_number := p_object_version_number;
422 --
423 -- Convert constant values to their corresponding boolean value
427 (p_constant_value => p_validate);
424 --
425 l_validate :=
426 hr_api.constant_to_boolean
428 l_assignment_changed :=
429 hr_api.constant_to_boolean
430 (p_constant_value => p_assignment_changed);
431 --
432 -- Register Surrogate ID or user key values
433 --
434 --
435 -- Call API
436 --
437 per_vacancy_api.update_vacancy
438 (p_validate => l_validate
439 ,p_effective_date => p_effective_date
440 ,p_vacancy_id => p_vacancy_id
441 ,p_object_version_number => p_object_version_number
442 ,p_date_from => p_date_from
443 ,p_position_id => p_position_id
444 ,p_job_id => p_job_id
445 ,p_grade_id => p_grade_id
446 ,p_organization_id => p_organization_id
447 ,p_people_group_id => p_people_group_id
448 ,p_location_id => p_location_id
449 ,p_recruiter_id => p_recruiter_id
450 ,p_date_to => p_date_to
451 ,p_security_method => p_security_method
452 ,p_description => p_description
453 ,p_number_of_openings => p_number_of_openings
454 ,p_status => p_status
455 ,p_budget_measurement_type => p_budget_measurement_type
456 ,p_budget_measurement_value => p_budget_measurement_value
457 ,p_vacancy_category => p_vacancy_category
458 ,p_manager_id => p_manager_id
459 ,p_primary_posting_id => p_primary_posting_id
460 ,p_assessment_id => p_assessment_id
461 ,p_attribute_category => p_attribute_category
462 ,p_attribute1 => p_attribute1
463 ,p_attribute2 => p_attribute2
464 ,p_attribute3 => p_attribute3
465 ,p_attribute4 => p_attribute4
466 ,p_attribute5 => p_attribute5
467 ,p_attribute6 => p_attribute6
468 ,p_attribute7 => p_attribute7
469 ,p_attribute8 => p_attribute8
470 ,p_attribute9 => p_attribute9
471 ,p_attribute10 => p_attribute10
472 ,p_attribute11 => p_attribute11
473 ,p_attribute12 => p_attribute12
474 ,p_attribute13 => p_attribute13
475 ,p_attribute14 => p_attribute14
476 ,p_attribute15 => p_attribute15
477 ,p_attribute16 => p_attribute16
478 ,p_attribute17 => p_attribute17
479 ,p_attribute18 => p_attribute18
480 ,p_attribute19 => p_attribute19
481 ,p_attribute20 => p_attribute20
482 ,p_attribute21 => p_attribute21
483 ,p_attribute22 => p_attribute22
484 ,p_attribute23 => p_attribute23
485 ,p_attribute24 => p_attribute24
486 ,p_attribute25 => p_attribute25
487 ,p_attribute26 => p_attribute26
488 ,p_attribute27 => p_attribute27
489 ,p_attribute28 => p_attribute28
490 ,p_attribute29 => p_attribute29
491 ,p_attribute30 => p_attribute30
492 ,p_assignment_changed => l_assignment_changed
493 ,p_inv_pos_grade_warning => l_inv_pos_grade_warning
494 ,p_inv_job_grade_warning => l_inv_job_grade_warning
495 );
496 --
497 if (nvl(g_commiting_via_workflow,'FALSE')='FALSE') then
498 -- we are not commiting from workflow, so check for warnings
499 --
500 -- Convert API warning boolean parameter values to specific
501 -- messages and add them to Multiple Message List
502 --
503 if l_inv_pos_grade_warning then
504 fnd_message.set_name('PER', 'HR_POS_GRADE_CHK');
505 hr_multi_message.add
506 (p_message_type => hr_multi_message.g_warning_msg
507 );
508 end if;
509 if l_inv_job_grade_warning then
510 fnd_message.set_name('PER', 'HR_JOB_GRADE_CHK');
511 hr_multi_message.add
512 (p_message_type => hr_multi_message.g_warning_msg
513 );
514 end if;
515 end if;
516 --
517 -- Convert API non-warning boolean parameter values
518 --
519 p_assignment_changed :=
520 hr_api.boolean_to_constant
521 (p_boolean_value => l_assignment_changed
522 );
523 --
524 -- Derive the API return status value based on whether
525 -- messages of any type exist in the Multiple Message List.
526 -- Also disable Multiple Message Detection.
527 --
528 p_return_status := hr_multi_message.get_return_status_disable;
529 hr_utility.set_location(' Leaving:' || l_proc,20);
530 --
531 exception
532 when hr_multi_message.error_message_exist then
533 --
534 -- Catch the Multiple Message List exception which
535 -- indicates API processing has been aborted because
536 -- at least one error message exists in the list.
537 --
538 rollback to update_vacancy_swi;
539 --
540 -- Reset IN OUT paramters and set OUT parameters
541 --
542 p_object_version_number := l_object_version_number;
543 p_assignment_changed := null;
547 --
544 p_return_status := hr_multi_message.get_return_status_disable;
545 hr_utility.set_location(' Leaving:' || l_proc,30);
546 when others then
548 -- When Multiple Message Detection is enabled catch
549 -- any Application specific or other unexpected
550 -- exceptions. Adding appropriate details to the
551 -- Multiple Message List. Otherwise re-raise
552 -- the error.
553 --
554 rollback to update_vacancy_swi;
555 if hr_multi_message.unexpected_error_add(l_proc) then
556 hr_utility.set_location(' Leaving:' || l_proc, 40);
557 raise;
558 end if;
559 --
560 -- Reset IN OUT parameters and set OUT parameters
561 --
562 p_object_version_number := l_object_version_number;
563 p_assignment_changed := null;
564 p_return_status := hr_multi_message.get_return_status_disable;
565 hr_utility.set_location(' Leaving: ' || l_proc, 50);
566 end update_vacancy;
567 --
568 procedure process_api
569 (
570 p_document in CLOB
571 ,p_return_status out nocopy VARCHAR2
572 ,p_validate in number default hr_api.g_false_num
573 ,p_effective_date in date default null
574 )
575 IS
576 l_postState VARCHAR2(2);
577 l_return_status VARCHAR2(1);
578 l_object_version_number number;
579 l_commitElement xmldom.DOMElement;
580 l_parser xmlparser.Parser;
581 l_CommitNode xmldom.DOMNode;
582 l_proc varchar2(72) := g_package || 'process_api';
583
584 -- Variables for OUT parameters
585 l_effective_date date := trunc(sysdate);
586 l_vacancy_id number;
587 l_assignment_changed number;
588 l_update_status VARCHAR2(30);
589
590 --
591
592 BEGIN
593 --
594 hr_utility.set_location(' Entering:' || l_proc,10);
595 hr_utility.set_location(' CLOB --> xmldom.DOMNode:' || l_proc,15);
596 --
597 g_commiting_via_workflow:='TRUE';
598 l_parser := xmlparser.newParser;
599 xmlparser.ParseCLOB(l_parser,p_document);
600 l_CommitNode := xmldom.makeNode(xmldom.getDocumentElement(xmlparser.getDocument(l_parser)));
601 --
602 hr_utility.set_location('Extracting the PostState:' || l_proc,20);
603 l_commitElement := xmldom.makeElement(l_CommitNode);
604 l_postState := xmldom.getAttribute(l_commitElement, 'PS');
605 --
606 --Get the values for in/out parameters
607 --
608 l_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
609 l_vacancy_id := hr_transaction_swi.getNumberValue(l_CommitNode,'VacancyId');
610 l_update_status := hr_transaction_swi.getVarchar2Value(l_CommitNode,'Status',NULL);
611 if l_update_status = 'PENDING'
612 then
613 l_update_status := 'APPROVED';
614 end if;
615 --
616 if p_effective_date is null
617 then
618 --
619 l_effective_date := trunc(sysdate);
620 --
621 else
622 --
623 l_effective_date := p_effective_date;
624 --
625 end if;
626 --
627 if l_postState = '0' then
628 --
629 hr_utility.set_location('creating :' || l_proc,30);
630 --
631 create_vacancy
632 (p_validate => p_validate
633 ,p_effective_date => l_effective_date
634 ,p_requisition_id => hr_transaction_swi.getNumberValue(l_CommitNode,'RequisitionId',NULL)
635 ,p_date_from => hr_transaction_swi.getDateValue(l_CommitNode,'DateFrom',NULL)
636 ,p_name => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Name',NULL)
637 ,p_security_method => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SecurityMethod',NULL)
638 ,p_business_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'BusinessGroupId',NULL)
639 ,p_position_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PositionId',NULL)
640 ,p_job_id => hr_transaction_swi.getNumberValue(l_CommitNode,'JobId',NULL)
641 ,p_grade_id => hr_transaction_swi.getNumberValue(l_CommitNode,'GradeId',NULL)
642 ,p_organization_id => hr_transaction_swi.getNumberValue(l_CommitNode,'OrganizationId',NULL)
643 ,p_people_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PeopleGroupId',NULL)
644 ,p_location_id => hr_transaction_swi.getNumberValue(l_CommitNode,'LocationId',NULL)
645 ,p_recruiter_id => hr_transaction_swi.getNumberValue(l_CommitNode,'RecruiterId',NULL)
646 ,p_date_to => hr_transaction_swi.getDateValue(l_CommitNode,'DateTo',NULL)
647 ,p_description => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Description',NULL)
648 ,p_number_of_openings => hr_transaction_swi.getNumberValue(l_CommitNode,'NumberOfOpenings',NULL)
649 ,p_status => 'APPROVED'
650 ,p_budget_measurement_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'BudgetMeasurementType',NULL)
651 ,p_budget_measurement_value => getNumberValueP2(l_CommitNode,'BudgetMeasurementValue',NULL)
652 ,p_vacancy_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'VacancyCategory',NULL)
653 ,p_manager_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ManagerId',NULL)
657 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1',NULL)
654 ,p_primary_posting_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PrimaryPostingId',NULL)
655 ,p_assessment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssessmentId',NULL)
656 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory',NULL)
658 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2',NULL)
659 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3',NULL)
660 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4',NULL)
661 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5',NULL)
662 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6',NULL)
663 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7',NULL)
664 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8',NULL)
665 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9',NULL)
666 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10',NULL)
667 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11',NULL)
668 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12',NULL)
669 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13',NULL)
670 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14',NULL)
671 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15',NULL)
672 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16',NULL)
673 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17',NULL)
674 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18',NULL)
675 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19',NULL)
676 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20',NULL)
677 ,p_attribute21 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute21',NULL)
678 ,p_attribute22 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute22',NULL)
679 ,p_attribute23 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute23',NULL)
680 ,p_attribute24 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute24',NULL)
681 ,p_attribute25 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute25',NULL)
682 ,p_attribute26 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute26',NULL)
683 ,p_attribute27 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute27',NULL)
684 ,p_attribute28 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute28',NULL)
685 ,p_attribute29 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute29',NULL)
686 ,p_attribute30 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute30',NULL)
687 ,p_object_version_number => l_object_version_number
688 ,p_vacancy_id => l_vacancy_id
689 ,p_return_status => l_return_status
690 );
691 --
692 elsif l_postState = '2' then
693 --
694 hr_utility.set_location('updating :' || l_proc,32);
695 --
696 -- call update vacancy
697 --
698 update_vacancy
699 (p_validate => p_validate
700 ,p_effective_date => l_effective_date
701 ,p_vacancy_id => l_vacancy_id
702 ,p_object_version_number => l_object_version_number
703 ,p_date_from => hr_transaction_swi.getDateValue(l_CommitNode,'DateFrom',NULL)
704 ,p_position_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PositionId',NULL)
705 ,p_job_id => hr_transaction_swi.getNumberValue(l_CommitNode,'JobId',NULL)
706 ,p_grade_id => hr_transaction_swi.getNumberValue(l_CommitNode,'GradeId',NULL)
707 ,p_organization_id => hr_transaction_swi.getNumberValue(l_CommitNode,'OrganizationId',NULL)
708 ,p_people_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PeopleGroupId',NULL)
709 ,p_location_id => hr_transaction_swi.getNumberValue(l_CommitNode,'LocationId',NULL)
710 ,p_recruiter_id => hr_transaction_swi.getNumberValue(l_CommitNode,'RecruiterId',NULL)
711 ,p_date_to => hr_transaction_swi.getDateValue(l_CommitNode,'DateTo',NULL)
712 ,p_security_method => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SecurityMethod',NULL)
713 ,p_description => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Description',NULL)
714 ,p_number_of_openings => hr_transaction_swi.getNumberValue(l_CommitNode,'NumberOfOpenings',NULL)
715 ,p_status => l_update_status
716 ,p_budget_measurement_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'BudgetMeasurementType',NULL)
720 ,p_primary_posting_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PrimaryPostingId',NULL)
717 ,p_budget_measurement_value => getNumberValueP2(l_CommitNode,'BudgetMeasurementValue',NULL)
718 ,p_vacancy_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'VacancyCategory',NULL)
719 ,p_manager_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ManagerId',NULL)
721 ,p_assessment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssessmentId',NULL)
722 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory',NULL)
723 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1',NULL)
724 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2',NULL)
725 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3',NULL)
726 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4',NULL)
727 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5',NULL)
728 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6',NULL)
729 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7',NULL)
730 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8',NULL)
731 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9',NULL)
732 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10',NULL)
733 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11',NULL)
734 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12',NULL)
735 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13',NULL)
736 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14',NULL)
737 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15',NULL)
738 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16',NULL)
739 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17',NULL)
740 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18',NULL)
741 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19',NULL)
742 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20',NULL)
743 ,p_attribute21 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute21',NULL)
744 ,p_attribute22 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute22',NULL)
745 ,p_attribute23 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute23',NULL)
746 ,p_attribute24 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute24',NULL)
747 ,p_attribute25 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute25',NULL)
748 ,p_attribute26 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute26',NULL)
749 ,p_attribute27 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute27',NULL)
750 ,p_attribute28 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute28',NULL)
751 ,p_attribute29 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute29',NULL)
752 ,p_attribute30 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute30',NULL)
753 ,p_assignment_changed => l_assignment_changed
754 ,p_return_status => l_return_status
755 );
756 --
757 end if;
758
759 p_return_status := l_return_status;
760 g_commiting_via_workflow:='FALSE';
761
762 hr_utility.set_location
763 ('Exiting :'|| l_proc || ': return status :'|| l_return_status || ':',40);
764 exception
765 when others then
766 g_commiting_via_workflow:='FALSE';
767
768 end process_api;
769 --
770 Function getNumberValueP2(
771 commitNode in xmldom.DOMNode,
772 attributeName in VARCHAR2,
773 gmisc_value in number default hr_api.g_number)
774 return NUMBER IS
775 --
776 l_number NUMBER(22,2);
777 l_isNull VARCHAR2(22);
778 l_element xmldom.DOMElement;
779 l_proc varchar2(72) := g_package || 'getNumberValue';
780 --
781 Begin
782 --
783 hr_utility.set_location(' Entering:' || l_proc,10);
784 l_number := xslprocessor.valueof(commitNode,attributeName);
785 l_element := xmldom.makeElement(commitNode);
786 l_isNull := xmldom.getAttribute(l_element, 'null');
787 if l_isNull = 'true' then
788 l_number := NULL;
789 else
790 l_number := NVL(l_number, gmisc_value);
791 end if;
792 hr_utility.set_location(' Exiting :' || l_proc,15);
793 --
794 return l_number;
795 --
796 End getNumberValueP2;
797 --
798 -- ----------------------------------------------------------------------------
799 -- |-----------------------------< handleAttachmentsWhenCommit >---------------|
800 -- ----------------------------------------------------------------------------
801 --
802 procedure handleAttachmentsWhenCommit(p_vacancy_id in number) is
803 PRAGMA AUTONOMOUS_TRANSACTION;
804 l_proc varchar2(72) := g_package || 'handleAttachmentsWhenCommit';
805 begin
806 hr_utility.set_location(' Entering:' || l_proc,10);
807 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_EXT_VAC_APPROVED',X_pk1_value=>p_vacancy_id,X_delete_document_flag=>'Y');
808 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_INT_VAC_APPROVED',X_pk1_value=>p_vacancy_id,X_delete_document_flag=>'Y');
809
810 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_INT_VAC',X_from_pk1_value => p_vacancy_id,X_to_entity_name=>'IRC_INT_VAC_APPROVED',X_to_pk1_value=>p_vacancy_id);
811 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_EXT_VAC',X_from_pk1_value => p_vacancy_id,X_to_entity_name=>'IRC_EXT_VAC_APPROVED',X_to_pk1_value=>p_vacancy_id);
812
813 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_EXT_VAC',X_pk1_value=>p_vacancy_id,X_delete_document_flag=>'Y');
814 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_INT_VAC',X_pk1_value=>p_vacancy_id,X_delete_document_flag=>'Y');
815 commit;
816 hr_utility.set_location(' Exiting:' || l_proc,20);
817 end;
818 --
819 -- ----------------------------------------------------------------------------
820 -- |-----------------------------< handleAttachmentsWhenRejected >-------------|
821 -- ----------------------------------------------------------------------------
822 --
823 procedure handleAttachmentsWhenRejected(p_vacancy_id in number) is
824 PRAGMA AUTONOMOUS_TRANSACTION;
825 l_proc varchar2(72) := g_package || 'handleAttachmentsWhenRejected';
826 begin
827 hr_utility.set_location(' Entering:' || l_proc,10);
828 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_EXT_VAC',X_pk1_value=>p_vacancy_id,X_delete_document_flag=>'Y');
829 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_INT_VAC',X_pk1_value=>p_vacancy_id,X_delete_document_flag=>'Y');
830 commit;
831 hr_utility.set_location(' Exiting:' || l_proc,20);
832 end;
833
834 -- ---------------------------------------------------------------------------
835 -- |-----------------------------< handleAttachmentsWhenEditing >--------------|
836 -- ----------------------------------------------------------------------------
837 --
838 procedure handleAttachmentsWhenEdit(p_vacancy_id in number) is
839 PRAGMA AUTONOMOUS_TRANSACTION;
840 l_proc varchar2(72) := g_package || 'handleAttachmentsWhenEdit';
841 begin
842 hr_utility.set_location(' Entering:' || l_proc,10);
843
844 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_EXT_VAC',X_pk1_value=>p_vacancy_id,X_delete_document_flag=>'Y');
845 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'IRC_INT_VAC',X_pk1_value=>p_vacancy_id,X_delete_document_flag=>'Y');
846
847 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_INT_VAC_APPROVED',X_from_pk1_value => p_vacancy_id,X_to_entity_name=>'IRC_INT_VAC',X_to_pk1_value=>p_vacancy_id);
848 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_EXT_VAC_APPROVED',X_from_pk1_value => p_vacancy_id,X_to_entity_name=>'IRC_EXT_VAC',X_to_pk1_value=>p_vacancy_id);
849 commit;
850 hr_utility.set_location(' Exiting:' || l_proc,20);
851 end;
852
853 -- ---------------------------------------------------------------------------
854 -- |-----------------------------< copyAttachments >---------------------------|
855 -- ----------------------------------------------------------------------------
856 --
857 procedure copyAttachments(p_from_vacancy_id in number,p_to_vacancy_id in number) is
858 PRAGMA AUTONOMOUS_TRANSACTION;
859 l_proc varchar2(72) := g_package || 'copyAttachments';
860 begin
861 hr_utility.set_location(' Entering:' || l_proc,10);
862
863 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_INT_VAC_APPROVED',X_from_pk1_value => p_from_vacancy_id,X_to_entity_name=>'IRC_INT_VAC',X_to_pk1_value=>p_to_vacancy_id);
864 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name =>'IRC_EXT_VAC_APPROVED',X_from_pk1_value => p_from_vacancy_id,X_to_entity_name=>'IRC_EXT_VAC',X_to_pk1_value=>p_to_vacancy_id);
865 commit;
866 hr_utility.set_location(' Exiting:' || l_proc,20);
867 end;
868
869 -- --------------------------------------------------------------------------
870 -- |-----------------------------< finalize_transaction >---------------------|
871 -- ----------------------------------------------------------------------------
872
873 procedure finalize_transaction
874 (
875 p_transaction_id in number
876 ,p_event in varchar2
877 ,p_return_status out nocopy varchar2
878 )
879 is
880 l_vacancy_id number;
881 l_return_status varchar2(1);
882 l_proc varchar2(72) := g_package || 'finalize_transaction';
883 --
884 cursor csr_vacancy_id is
885 select transaction_ref_id
886 from hr_api_transactions hrt
887 where hrt.transaction_id = p_transaction_id;
888 --
889 begin
890 --
891 hr_utility.set_location(' Entering:' || l_proc,10);
892 hr_utility.set_location(l_proc || ' Event:' || p_event,20);
893 --
894 open csr_vacancy_id;
895 fetch csr_vacancy_id into l_vacancy_id;
896
897 if csr_vacancy_id%found then
898 close csr_vacancy_id;
899 --
900 hr_utility.set_location(l_proc,30);
901
902 if p_event = 'APPROVED' then
903 hr_utility.set_location(l_proc, 40);
904 handleAttachmentsWhenCommit(l_vacancy_id);
905
906 elsif p_event = 'REJECTED' OR p_event = 'DELETED' OR p_event = 'CANCEL' then
907 hr_utility.set_location(l_proc, 50);
908 handleAttachmentsWhenRejected(l_vacancy_id);
909 end if;
910
911 end if;
912 --
913 close csr_vacancy_id;
914 p_return_status := hr_multi_message.get_return_status_disable;
915 hr_utility.set_location('Exiting:' || l_proc,60);
916 --
917 end finalize_transaction;
918
919
920 end per_vacancy_swi;