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