[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_DEPLOYMENT_SWI
Source
1 Package Body hr_person_deployment_swi As
2 /* $Header: hrpdtswi.pkb 120.13.12020000.7 2012/10/09 15:22:20 pathota ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'hr_person_deployment_swi.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 -- ----------------------------------------------------------------------------
10 -- |-----------------------< create_person_deployment >-----------------------|
11 -- ----------------------------------------------------------------------------
12 PROCEDURE create_person_deployment
13 (p_validate in number default hr_api.g_false_num
14 ,p_from_business_group_id in number
15 ,p_to_business_group_id in number
16 ,p_from_person_id in number
17 ,p_to_person_id in number default null
18 ,p_person_type_id in number default null
19 ,p_start_date in date
20 ,p_end_date in date default null
21 ,p_employee_number in varchar2 default null
22 ,p_leaving_reason in varchar2 default null
23 ,p_leaving_person_type_id in number default null
24 ,p_permanent in varchar2 default null
25 ,p_deplymt_policy_id in number default null
26 ,p_organization_id in number
27 ,p_location_id in number default null
28 ,p_job_id in number default null
29 ,p_position_id in number default null
30 ,p_grade_id in number default null
31 ,p_supervisor_id in number default null
32 ,p_supervisor_assignment_id in number default null
33 ,p_retain_direct_reports in varchar2 default null
34 ,p_payroll_id in number default null
35 ,p_pay_basis_id in number default null
36 ,p_proposed_salary in varchar2 default null
37 ,p_people_group_id in number default null
38 ,p_soft_coding_keyflex_id in number default null
39 ,p_assignment_status_type_id in number default null
40 ,p_ass_status_change_reason in varchar2 default null
41 ,p_assignment_category in varchar2 default null
42 ,p_per_information1 in varchar2 default null
43 ,p_per_information2 in varchar2 default null
44 ,p_per_information3 in varchar2 default null
45 ,p_per_information4 in varchar2 default null
46 ,p_per_information5 in varchar2 default null
47 ,p_per_information6 in varchar2 default null
48 ,p_per_information7 in varchar2 default null
49 ,p_per_information8 in varchar2 default null
50 ,p_per_information9 in varchar2 default null
51 ,p_per_information10 in varchar2 default null
52 ,p_per_information11 in varchar2 default null
53 ,p_per_information12 in varchar2 default null
54 ,p_per_information13 in varchar2 default null
55 ,p_per_information14 in varchar2 default null
56 ,p_per_information15 in varchar2 default null
57 ,p_per_information16 in varchar2 default null
58 ,p_per_information17 in varchar2 default null
59 ,p_per_information18 in varchar2 default null
60 ,p_per_information19 in varchar2 default null
61 ,p_per_information20 in varchar2 default null
62 ,p_per_information21 in varchar2 default null
63 ,p_per_information22 in varchar2 default null
64 ,p_per_information23 in varchar2 default null
65 ,p_per_information24 in varchar2 default null
66 ,p_per_information25 in varchar2 default null
67 ,p_per_information26 in varchar2 default null
68 ,p_per_information27 in varchar2 default null
69 ,p_per_information28 in varchar2 default null
70 ,p_per_information29 in varchar2 default null
71 ,p_per_information30 in varchar2 default null
72 ,p_deployment_reason in varchar2 default null
73 ,p_person_deployment_id in number
74 ,p_object_version_number out nocopy number
75 ,p_return_status out nocopy varchar2
76 ) is
77 --
78 -- Variables for API Boolean parameters
79 l_validate boolean;
80 l_policy_duration_warning boolean;
81 --
82 -- Variables for IN/OUT parameters
83 --
84 -- Other variables
85 l_person_deployment_id number;
86 l_proc varchar2(72) := g_package ||'create_person_deployment';
87 Begin
88 hr_utility.set_location(' Entering:' || l_proc,10);
89 --
90 -- Issue a savepoint
91 --
92 savepoint create_person_deployment_swi;
93 --
94 -- Initialise Multiple Message Detection
95 --
96 hr_multi_message.enable_message_list;
97 --
98 -- Remember IN OUT parameter IN values
99 --
100 --
101 -- Convert constant values to their corresponding boolean value
102 --
103 l_validate :=
104 hr_api.constant_to_boolean
105 (p_constant_value => p_validate);
106 --
107 -- Register Surrogate ID or user key values
108 --
109 hr_pdt_ins.set_base_key_value
110 (p_person_deployment_id => p_person_deployment_id
111 );
112 --
113 -- Call API
114 --
115 hr_person_deployment_api.create_person_deployment
116 (p_validate => l_validate
117 ,p_from_business_group_id => p_from_business_group_id
118 ,p_to_business_group_id => p_to_business_group_id
119 ,p_from_person_id => p_from_person_id
120 ,p_to_person_id => p_to_person_id
121 ,p_person_type_id => p_person_type_id
122 ,p_start_date => p_start_date
123 ,p_end_date => p_end_date
124 ,p_deployment_reason => p_deployment_reason
125 ,p_employee_number => p_employee_number
126 ,p_leaving_reason => p_leaving_reason
127 ,p_leaving_person_type_id => p_leaving_person_type_id
128 ,p_permanent => p_permanent
129 ,p_deplymt_policy_id => p_deplymt_policy_id
130 ,p_organization_id => p_organization_id
131 ,p_location_id => p_location_id
132 ,p_job_id => p_job_id
133 ,p_position_id => p_position_id
134 ,p_grade_id => p_grade_id
135 ,p_supervisor_id => p_supervisor_id
136 ,p_supervisor_assignment_id => p_supervisor_assignment_id
137 ,p_retain_direct_reports => p_retain_direct_reports
138 ,p_payroll_id => p_payroll_id
139 ,p_pay_basis_id => p_pay_basis_id
140 ,p_proposed_salary => p_proposed_salary
141 ,p_people_group_id => p_people_group_id
142 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
143 ,p_assignment_status_type_id => p_assignment_status_type_id
144 ,p_ass_status_change_reason => p_ass_status_change_reason
145 ,p_assignment_category => p_assignment_category
146 ,p_per_information1 => p_per_information1
147 ,p_per_information2 => p_per_information2
148 ,p_per_information3 => p_per_information3
149 ,p_per_information4 => p_per_information4
150 ,p_per_information5 => p_per_information5
151 ,p_per_information6 => p_per_information6
152 ,p_per_information7 => p_per_information7
153 ,p_per_information8 => p_per_information8
154 ,p_per_information9 => p_per_information9
155 ,p_per_information10 => p_per_information10
156 ,p_per_information11 => p_per_information11
157 ,p_per_information12 => p_per_information12
158 ,p_per_information13 => p_per_information13
159 ,p_per_information14 => p_per_information14
160 ,p_per_information15 => p_per_information15
161 ,p_per_information16 => p_per_information16
162 ,p_per_information17 => p_per_information17
163 ,p_per_information18 => p_per_information18
164 ,p_per_information19 => p_per_information19
165 ,p_per_information20 => p_per_information20
166 ,p_per_information21 => p_per_information21
167 ,p_per_information22 => p_per_information22
168 ,p_per_information23 => p_per_information23
169 ,p_per_information24 => p_per_information24
170 ,p_per_information25 => p_per_information25
171 ,p_per_information26 => p_per_information26
172 ,p_per_information27 => p_per_information27
173 ,p_per_information28 => p_per_information28
174 ,p_per_information29 => p_per_information29
175 ,p_per_information30 => p_per_information30
176 ,p_person_deployment_id => l_person_deployment_id
177 ,p_object_version_number => p_object_version_number
178 ,p_policy_duration_warning => l_policy_duration_warning
179 );
180 --
181 -- Convert API warning boolean parameter values to specific
182 -- messages and add them to Multiple Message List
183 --
184 /*
185 NOT IN USE FOR INITIAL RELEASE
186 if l_policy_duration_warning then
187 fnd_message.set_name('EDIT HERE: APP_CODE', 'EDIT_HERE: MESSAGE_NAME ');
188 hr_multi_message.add
189 (p_message_type => hr_multi_message.g_warning_msg
190 );
191 end if; --
192 */
193 -- Convert API non-warning boolean parameter values
194 --
195 --
196 -- Derive the API return status value based on whether
197 -- messages of any type exist in the Multiple Message List.
198 -- Also disable Multiple Message Detection.
199 --
200 p_return_status := hr_multi_message.get_return_status_disable;
201 hr_utility.set_location(' Leaving:' || l_proc,20);
202 --
203 exception
204 when hr_multi_message.error_message_exist then
205 --
206 -- Catch the Multiple Message List exception which
207 -- indicates API processing has been aborted because
208 -- at least one message exists in the list.
209 --
210 rollback to create_person_deployment_swi;
211 --
212 -- Reset IN OUT parameters and set OUT parameters
213 --
214 p_object_version_number := null;
215 p_return_status := hr_multi_message.get_return_status_disable;
216 hr_utility.set_location(' Leaving:' || l_proc, 30);
217 when others then
218 --
219 -- When Multiple Message Detection is enabled catch
220 -- any Application specific or other unexpected
221 -- exceptions. Adding appropriate details to the
222 -- Multiple Message List. Otherwise re-raise the
223 -- error.
224 --
225 rollback to create_person_deployment_swi;
226 if hr_multi_message.unexpected_error_add(l_proc) then
227 hr_utility.set_location(' Leaving:' || l_proc,40);
228 raise;
229 end if;
230 --
231 -- Reset IN OUT and set OUT parameters
232 --
233 p_object_version_number := null;
234 p_return_status := hr_multi_message.get_return_status_disable;
235 hr_utility.set_location(' Leaving:' || l_proc,50);
236 end create_person_deployment;
237 -- ----------------------------------------------------------------------------
238 -- |-----------------------< update_person_deployment >-----------------------|
239 -- ----------------------------------------------------------------------------
240 PROCEDURE update_person_deployment
241 (p_validate in number default hr_api.g_false_num
242 ,p_person_deployment_id in number
243 ,p_object_version_number in out nocopy number
244 ,p_person_type_id in number default hr_api.g_number
245 ,p_start_date in date default hr_api.g_date
246 ,p_end_date in date default hr_api.g_date
247 ,p_employee_number in varchar2 default hr_api.g_varchar2
248 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
249 ,p_leaving_person_type_id in number default hr_api.g_number
250 ,p_status in varchar2 default hr_api.g_varchar2
251 ,p_status_change_reason in varchar2 default hr_api.g_varchar2
252 ,p_deplymt_policy_id in number default hr_api.g_number
253 ,p_organization_id in number default hr_api.g_number
254 ,p_location_id in number default hr_api.g_number
255 ,p_job_id in number default hr_api.g_number
256 ,p_position_id in number default hr_api.g_number
257 ,p_grade_id in number default hr_api.g_number
258 ,p_supervisor_id in number default hr_api.g_number
259 ,p_supervisor_assignment_id in number default hr_api.g_number
260 ,p_retain_direct_reports in varchar2 default hr_api.g_varchar2
261 ,p_payroll_id in number default hr_api.g_number
262 ,p_pay_basis_id in number default hr_api.g_number
263 ,p_proposed_salary in varchar2 default hr_api.g_varchar2
264 ,p_people_group_id in number default hr_api.g_number
265 ,p_soft_coding_keyflex_id in number default hr_api.g_number
266 ,p_assignment_status_type_id in number default hr_api.g_number
267 ,p_ass_status_change_reason in varchar2 default hr_api.g_varchar2
268 ,p_assignment_category in varchar2 default hr_api.g_varchar2
269 ,p_per_information1 in varchar2 default hr_api.g_varchar2
270 ,p_per_information2 in varchar2 default hr_api.g_varchar2
271 ,p_per_information3 in varchar2 default hr_api.g_varchar2
272 ,p_per_information4 in varchar2 default hr_api.g_varchar2
273 ,p_per_information5 in varchar2 default hr_api.g_varchar2
274 ,p_per_information6 in varchar2 default hr_api.g_varchar2
275 ,p_per_information7 in varchar2 default hr_api.g_varchar2
276 ,p_per_information8 in varchar2 default hr_api.g_varchar2
277 ,p_per_information9 in varchar2 default hr_api.g_varchar2
278 ,p_per_information10 in varchar2 default hr_api.g_varchar2
279 ,p_per_information11 in varchar2 default hr_api.g_varchar2
280 ,p_per_information12 in varchar2 default hr_api.g_varchar2
281 ,p_per_information13 in varchar2 default hr_api.g_varchar2
282 ,p_per_information14 in varchar2 default hr_api.g_varchar2
283 ,p_per_information15 in varchar2 default hr_api.g_varchar2
284 ,p_per_information16 in varchar2 default hr_api.g_varchar2
285 ,p_per_information17 in varchar2 default hr_api.g_varchar2
286 ,p_per_information18 in varchar2 default hr_api.g_varchar2
287 ,p_per_information19 in varchar2 default hr_api.g_varchar2
288 ,p_per_information20 in varchar2 default hr_api.g_varchar2
289 ,p_per_information21 in varchar2 default hr_api.g_varchar2
290 ,p_per_information22 in varchar2 default hr_api.g_varchar2
291 ,p_per_information23 in varchar2 default hr_api.g_varchar2
292 ,p_per_information24 in varchar2 default hr_api.g_varchar2
293 ,p_per_information25 in varchar2 default hr_api.g_varchar2
294 ,p_per_information26 in varchar2 default hr_api.g_varchar2
295 ,p_per_information27 in varchar2 default hr_api.g_varchar2
296 ,p_per_information28 in varchar2 default hr_api.g_varchar2
297 ,p_per_information29 in varchar2 default hr_api.g_varchar2
298 ,p_per_information30 in varchar2 default hr_api.g_varchar2
299 ,p_deployment_reason in varchar2 default hr_api.g_varchar2
300 ,p_return_status out nocopy varchar2
301 ) is
302 --
303 -- Variables for API Boolean parameters
304 l_validate boolean;
305 l_policy_duration_warning boolean;
306 --
307 -- Variables for IN/OUT parameters
308 l_object_version_number number;
309 --
310 -- Other variables
311 l_proc varchar2(72) := g_package ||'update_person_deployment';
312 Begin
313 hr_utility.set_location(' Entering:' || l_proc,10);
314 --
315 -- Issue a savepoint
316 --
317 savepoint update_person_deployment_swi;
318 --
319 -- Initialise Multiple Message Detection
320 --
321 hr_multi_message.enable_message_list;
322 --
323 -- Remember IN OUT parameter IN values
324 --
325 l_object_version_number := p_object_version_number;
326 --
327 -- Convert constant values to their corresponding boolean value
328 --
329 l_validate :=
330 hr_api.constant_to_boolean
331 (p_constant_value => p_validate);
332 --
333 -- Register Surrogate ID or user key values
334 --
335 --
336 -- Call API
337 --
338 hr_person_deployment_api.update_person_deployment
339 (p_validate => l_validate
340 ,p_person_deployment_id => p_person_deployment_id
341 ,p_object_version_number => p_object_version_number
342 ,p_person_type_id => p_person_type_id
343 ,p_start_date => p_start_date
344 ,p_end_date => p_end_date
345 ,p_deployment_reason => p_deployment_reason
346 ,p_employee_number => p_employee_number
347 ,p_leaving_reason => p_leaving_reason
348 ,p_leaving_person_type_id => p_leaving_person_type_id
349 ,p_status => p_status
350 ,p_status_change_reason => p_status_change_reason
351 ,p_deplymt_policy_id => p_deplymt_policy_id
352 ,p_organization_id => p_organization_id
353 ,p_location_id => p_location_id
354 ,p_job_id => p_job_id
355 ,p_position_id => p_position_id
356 ,p_grade_id => p_grade_id
357 ,p_supervisor_id => p_supervisor_id
358 ,p_supervisor_assignment_id => p_supervisor_assignment_id
359 ,p_retain_direct_reports => p_retain_direct_reports
360 ,p_payroll_id => p_payroll_id
361 ,p_pay_basis_id => p_pay_basis_id
362 ,p_proposed_salary => p_proposed_salary
363 ,p_people_group_id => p_people_group_id
364 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
365 ,p_assignment_status_type_id => p_assignment_status_type_id
366 ,p_ass_status_change_reason => p_ass_status_change_reason
367 ,p_assignment_category => p_assignment_category
368 ,p_per_information1 => p_per_information1
369 ,p_per_information2 => p_per_information2
370 ,p_per_information3 => p_per_information3
371 ,p_per_information4 => p_per_information4
372 ,p_per_information5 => p_per_information5
373 ,p_per_information6 => p_per_information6
374 ,p_per_information7 => p_per_information7
375 ,p_per_information8 => p_per_information8
376 ,p_per_information9 => p_per_information9
377 ,p_per_information10 => p_per_information10
378 ,p_per_information11 => p_per_information11
379 ,p_per_information12 => p_per_information12
380 ,p_per_information13 => p_per_information13
381 ,p_per_information14 => p_per_information14
382 ,p_per_information15 => p_per_information15
383 ,p_per_information16 => p_per_information16
384 ,p_per_information17 => p_per_information17
385 ,p_per_information18 => p_per_information18
386 ,p_per_information19 => p_per_information19
387 ,p_per_information20 => p_per_information20
388 ,p_per_information21 => p_per_information21
389 ,p_per_information22 => p_per_information22
390 ,p_per_information23 => p_per_information23
391 ,p_per_information24 => p_per_information24
392 ,p_per_information25 => p_per_information25
393 ,p_per_information26 => p_per_information26
394 ,p_per_information27 => p_per_information27
395 ,p_per_information28 => p_per_information28
396 ,p_per_information29 => p_per_information29
397 ,p_per_information30 => p_per_information30
398 ,p_policy_duration_warning => l_policy_duration_warning
399 );
400 --
401 -- Convert API warning boolean parameter values to specific
402 -- messages and add them to Multiple Message List
403 --
404 /*
405 NOT IN USE FOR INITIAL RELEASE
406 if l_policy_duration_warning then
407 fnd_message.set_name('EDIT HERE: APP_CODE', 'EDIT_HERE: MESSAGE_NAME ');
408 hr_multi_message.add
409 (p_message_type => hr_multi_message.g_warning_msg
410 );
411 end if; --
412 */
413 -- Convert API non-warning boolean parameter values
414 --
415 --
416 -- Derive the API return status value based on whether
417 -- messages of any type exist in the Multiple Message List.
418 -- Also disable Multiple Message Detection.
419 --
420 p_return_status := hr_multi_message.get_return_status_disable;
421 hr_utility.set_location(' Leaving:' || l_proc,20);
422 --
423 exception
424 when hr_multi_message.error_message_exist then
425 --
426 -- Catch the Multiple Message List exception which
427 -- indicates API processing has been aborted because
428 -- at least one message exists in the list.
429 --
430 rollback to update_person_deployment_swi;
431 --
432 -- Reset IN OUT parameters and set OUT parameters
433 --
434 p_object_version_number := l_object_version_number;
435 p_return_status := hr_multi_message.get_return_status_disable;
436 hr_utility.set_location(' Leaving:' || l_proc, 30);
437 when others then
438 --
439 -- When Multiple Message Detection is enabled catch
440 -- any Application specific or other unexpected
441 -- exceptions. Adding appropriate details to the
442 -- Multiple Message List. Otherwise re-raise the
443 -- error.
444 --
445 rollback to update_person_deployment_swi;
446 if hr_multi_message.unexpected_error_add(l_proc) then
447 hr_utility.set_location(' Leaving:' || l_proc,40);
448 raise;
449 end if;
450 --
451 -- Reset IN OUT and set OUT parameters
452 --
453 p_object_version_number := l_object_version_number;
454 p_return_status := hr_multi_message.get_return_status_disable;
455 hr_utility.set_location(' Leaving:' || l_proc,50);
456 end update_person_deployment;
457 -- ----------------------------------------------------------------------------
458 -- |-----------------------< delete_person_deployment >-----------------------|
459 -- ----------------------------------------------------------------------------
460 PROCEDURE delete_person_deployment
461 (p_validate in number default hr_api.g_false_num
462 ,p_person_deployment_id in number
463 ,p_object_version_number in number
464 ,p_return_status out nocopy varchar2
465 ) is
466 --
467 -- Variables for API Boolean parameters
468 l_validate boolean;
469 --
470 -- Variables for IN/OUT parameters
471 --
472 -- Other variables
473 l_proc varchar2(72) := g_package ||'delete_person_deployment';
474
475 cursor c_del_trans is
476 select hat.transaction_id transaction_id,
477 hats.transaction_step_id transaction_step_id
478 from hr_api_transactions hat,
479 hr_api_transaction_steps hats
480 where hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
481 and hat.transaction_id = hats.transaction_id
482 and hats.information1 = to_char(p_person_deployment_id);
483
484 Begin
485 hr_utility.set_location(' Entering:' || l_proc,10);
486 --
487 -- Issue a savepoint
488 --
489 savepoint delete_person_deployment_swi;
490 --
491 -- Initialise Multiple Message Detection
492 --
493 hr_multi_message.enable_message_list;
494 --
495 -- Remember IN OUT parameter IN values
496 --
497 --
498 -- Convert constant values to their corresponding boolean value
499 --
500 l_validate :=
501 hr_api.constant_to_boolean
502 (p_constant_value => p_validate);
503 --
504 -- Register Surrogate ID or user key values
505 --
506
507 --
508 -- Call API
509 --
510 hr_person_deployment_api.delete_person_deployment
511 (p_validate => l_validate
512 ,p_person_deployment_id => p_person_deployment_id
513 ,p_object_version_number => p_object_version_number
514 );
515 --
516 --Deleting any existing transactions for this p_person_deployment_id
517 for a in c_del_trans
518 loop
519 hr_transaction_swi.delete_transaction(p_transaction_id => a.transaction_id);
520
521 end loop;
522 -- Convert API warning boolean parameter values to specific
523 -- messages and add them to Multiple Message List
524 --
525 --
526 -- Convert API non-warning boolean parameter values
527 --
528 --
529 -- Derive the API return status value based on whether
530 -- messages of any type exist in the Multiple Message List.
531 -- Also disable Multiple Message Detection.
532 --
533 p_return_status := hr_multi_message.get_return_status_disable;
534 hr_utility.set_location(' Leaving:' || l_proc,20);
535 --
536 exception
537 when hr_multi_message.error_message_exist then
538 --
539 -- Catch the Multiple Message List exception which
540 -- indicates API processing has been aborted because
541 -- at least one message exists in the list.
542 --
543 rollback to delete_person_deployment_swi;
544 --
545 -- Reset IN OUT parameters and set OUT parameters
546 --
547 p_return_status := hr_multi_message.get_return_status_disable;
548 hr_utility.set_location(' Leaving:' || l_proc, 30);
549 when others then
550 --
551 -- When Multiple Message Detection is enabled catch
552 -- any Application specific or other unexpected
553 -- exceptions. Adding appropriate details to the
554 -- Multiple Message List. Otherwise re-raise the
555 -- error.
556 --
557 rollback to delete_person_deployment_swi;
558 if hr_multi_message.unexpected_error_add(l_proc) then
559 hr_utility.set_location(' Leaving:' || l_proc,40);
560 raise;
561 end if;
562 --
563 -- Reset IN OUT and set OUT parameters
564 --
565 p_return_status := hr_multi_message.get_return_status_disable;
566 hr_utility.set_location(' Leaving:' || l_proc,50);
567 end delete_person_deployment;
568 -- ----------------------------------------------------------------------------
569 -- |-----------------------< initiate_deployment >----------------------------|
570 -- ----------------------------------------------------------------------------
571 PROCEDURE initiate_deployment
572 (p_validate in number default hr_api.g_false_num
573 ,p_person_deployment_id in number
574 ,p_object_version_number in out nocopy number
575 ,p_host_person_id out nocopy number
576 ,p_host_per_ovn out nocopy number
577 ,p_host_assignment_id out nocopy number
578 ,p_host_asg_ovn out nocopy number
579 ,p_return_status out nocopy varchar2
580 ) IS
581 --
582 -- Variables for API Boolean parameters
583 l_validate boolean;
584 l_already_applicant_warning boolean;
585 --
586 -- Variables for IN/OUT parameters
587 l_object_version_number number;
588 --
589 -- Other variables
590 l_proc varchar2(72) := g_package ||'initiate_deployment';
591 Begin
592 hr_utility.set_location(' Entering:' || l_proc,10);
593 --
594 -- Issue a savepoint
595 --
596 savepoint initiate_deployment_swi;
597 --
598 -- Initialise Multiple Message Detection
599 --
600 hr_multi_message.enable_message_list;
601 --
602 -- Remember IN OUT parameter IN values
603 --
604 l_object_version_number := p_object_version_number;
605 --
606 -- Convert constant values to their corresponding boolean value
607 --
608 l_validate :=
609 hr_api.constant_to_boolean
610 (p_constant_value => p_validate);
611 --
612 -- Register Surrogate ID or user key values
613 --
614 --
615 -- Call API
616 --
617 hr_person_deployment_api.initiate_deployment
618 (p_validate => l_validate
619 ,p_person_deployment_id => p_person_deployment_id
620 ,p_object_version_number => p_object_version_number
621 ,p_host_person_id => p_host_person_id
622 ,p_host_per_ovn => p_host_per_ovn
623 ,p_host_assignment_id => p_host_assignment_id
624 ,p_host_asg_ovn => p_host_asg_ovn
625 ,p_already_applicant_warning => l_already_applicant_warning
626 );
627 --
628 -- Convert API warning boolean parameter values to specific
629 -- messages and add them to Multiple Message List
630 --
631 if l_already_applicant_warning then
632 fnd_message.set_name('PER','HR_449649_DPL_NO_INIT_APL');
633 hr_multi_message.add
634 (p_message_type => hr_multi_message.g_warning_msg
635 );
636 end if;
637 --
638 -- Convert API non-warning boolean parameter values
639 --
640 --
641 -- Derive the API return status value based on whether
642 -- messages of any type exist in the Multiple Message List.
643 -- Also disable Multiple Message Detection.
644 --
645 p_return_status := hr_multi_message.get_return_status_disable;
646 hr_utility.set_location(' Leaving:' || l_proc,20);
647 --
648 exception
649 when hr_multi_message.error_message_exist then
650 --
651 -- Catch the Multiple Message List exception which
652 -- indicates API processing has been aborted because
653 -- at least one message exists in the list.
654 --
655 rollback to initiate_deployment_swi;
656 --
657 -- Reset IN OUT parameters and set OUT parameters
658 --
659 p_object_version_number := l_object_version_number;
660 p_host_person_id := null;
661 p_host_per_ovn := null;
662 p_host_assignment_id := null;
663 p_host_asg_ovn := null;
664 p_return_status := hr_multi_message.get_return_status_disable;
665 hr_utility.set_location(' Leaving:' || l_proc, 30);
666 when others then
667 --
668 -- When Multiple Message Detection is enabled catch
669 -- any Application specific or other unexpected
670 -- exceptions. Adding appropriate details to the
671 -- Multiple Message List. Otherwise re-raise the
672 -- error.
673 --
674 rollback to initiate_deployment_swi;
675 if hr_multi_message.unexpected_error_add(l_proc) then
676 hr_utility.set_location(' Leaving:' || l_proc,40);
677 raise;
678 end if;
679 --
680 -- Reset IN OUT and set OUT parameters
681 --
682 p_object_version_number := l_object_version_number;
683 p_host_person_id := null;
684 p_host_per_ovn := null;
685 p_host_assignment_id := null;
686 p_host_asg_ovn := null;
687 p_return_status := hr_multi_message.get_return_status_disable;
688 hr_utility.set_location(' Leaving:' || l_proc,50);
689 end initiate_deployment;
690 -- ----------------------------------------------------------------------------
691 -- |-----------------------< change_deployment_dates >------------------------|
692 -- ----------------------------------------------------------------------------
693 PROCEDURE change_deployment_dates
694 (p_validate in number default hr_api.g_false_num
695 ,p_person_deployment_id in number
696 ,p_object_version_number in out nocopy number
697 ,p_start_date in date default hr_api.g_date
698 ,p_end_date in date default hr_api.g_date
699 ,p_deplymt_policy_id in number default hr_api.g_number
700 ,p_return_status out nocopy varchar2
701 ) IS
702 --
703 -- Variables for API Boolean parameters
704 l_validate boolean;
705 --
706 -- Variables for IN/OUT parameters
707 l_object_version_number number;
708 --
709 -- Other variables
710 --
711 l_proc varchar2(72) := g_package ||'change_deployment_dates';
712 Begin
713 hr_utility.set_location(' Entering:' || l_proc,10);
714 --
715 -- Issue a savepoint
716 --
717 savepoint change_deployment_dates_swi;
718 --
719 -- Initialise Multiple Message Detection
720 --
721 hr_multi_message.enable_message_list;
722 --
723 -- Remember IN OUT parameter IN values
724 --
725 l_object_version_number := p_object_version_number;
726 --
727 -- Convert constant values to their corresponding boolean value
728 --
729 l_validate :=
730 hr_api.constant_to_boolean
731 (p_constant_value => p_validate);
732 --
733 -- Register Surrogate ID or user key values
734 --
735 --
736 -- Call API
737 --
738 hr_person_deployment_api.change_deployment_dates
739 (p_validate => l_validate
740 ,p_person_deployment_id => p_person_deployment_id
741 ,p_object_version_number => p_object_version_number
742 ,p_start_date => p_start_date
743 ,p_end_date => p_end_date
744 ,p_deplymt_policy_id => p_deplymt_policy_id
745 );
746 --
747 -- Convert API warning boolean parameter values to specific
748 -- messages and add them to Multiple Message List
749 --
750 --
751 -- Convert API non-warning boolean parameter values
752 --
753 --
754 -- Derive the API return status value based on whether
755 -- messages of any type exist in the Multiple Message List.
756 -- Also disable Multiple Message Detection.
757 --
758 p_return_status := hr_multi_message.get_return_status_disable;
759 hr_utility.set_location(' Leaving:' || l_proc,20);
760 --
761 exception
762 when hr_multi_message.error_message_exist then
763 --
764 -- Catch the Multiple Message List exception which
765 -- indicates API processing has been aborted because
766 -- at least one message exists in the list.
767 --
768 rollback to change_deployment_dates_swi;
769 --
770 -- Reset IN OUT parameters and set OUT parameters
771 --
772 p_object_version_number := l_object_version_number;
773 p_return_status := hr_multi_message.get_return_status_disable;
774 hr_utility.set_location(' Leaving:' || l_proc, 30);
775 when others then
776 --
777 -- When Multiple Message Detection is enabled catch
778 -- any Application specific or other unexpected
779 -- exceptions. Adding appropriate details to the
780 -- Multiple Message List. Otherwise re-raise the
781 -- error.
782 --
783 rollback to change_deployment_dates_swi;
784 if hr_multi_message.unexpected_error_add(l_proc) then
785 hr_utility.set_location(' Leaving:' || l_proc,40);
786 raise;
787 end if;
788 --
789 -- Reset IN OUT and set OUT parameters
790 --
791 p_object_version_number := l_object_version_number;
792 p_return_status := hr_multi_message.get_return_status_disable;
793 hr_utility.set_location(' Leaving:' || l_proc,50);
794 end change_deployment_dates;
795 -- ----------------------------------------------------------------------------
796 -- |-----------------------< return_from_deployment >-------------------------|
797 -- ----------------------------------------------------------------------------
798 PROCEDURE return_from_deployment
799 (p_validate in number default hr_api.g_false_num
800 ,p_person_deployment_id in number
801 ,p_object_version_number in out nocopy number
802 ,p_end_date in date default hr_api.g_date
803 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
804 ,p_leaving_person_type_id in number default hr_api.g_number
805 ,p_return_status out nocopy varchar2
806 ) IS
807 --
808 -- Variables for API Boolean parameters
809 l_validate boolean;
810 --
811 -- Variables for IN/OUT parameters
812 --
813 l_object_version_number number;
814 --
815 -- Other variables
816 l_proc varchar2(72) := g_package ||'change_deployment_dates';
817 Begin
818 hr_utility.set_location(' Entering:' || l_proc,10);
819 --
820 -- Issue a savepoint
821 --
822 savepoint return_from_deployment_swi;
823 --
824 -- Initialise Multiple Message Detection
825 --
826 hr_multi_message.enable_message_list;
827 --
828 -- Remember IN OUT parameter IN values
829 --
830 l_object_version_number := p_object_version_number;
831 --
832 -- Convert constant values to their corresponding boolean value
833 --
834 l_validate :=
835 hr_api.constant_to_boolean
836 (p_constant_value => p_validate);
837 --
838 -- Register Surrogate ID or user key values
839 --
840 --
841 -- Call API
842 --
843 hr_person_deployment_api.return_from_deployment
844 (p_validate => l_validate
845 ,p_person_deployment_id => p_person_deployment_id
846 ,p_object_version_number => p_object_version_number
847 ,p_end_date => p_end_date
848 ,p_leaving_reason => p_leaving_reason
849 ,p_leaving_person_type_id => p_leaving_person_type_id
850 );
851 --
852 -- Convert API warning boolean parameter values to specific
853 -- messages and add them to Multiple Message List
854 --
855 --
856 -- Convert API non-warning boolean parameter values
857 --
858 --
859 -- Derive the API return status value based on whether
860 -- messages of any type exist in the Multiple Message List.
861 -- Also disable Multiple Message Detection.
862 --
863 p_return_status := hr_multi_message.get_return_status_disable;
864 hr_utility.set_location(' Leaving:' || l_proc,20);
865 --
866 exception
867 when hr_multi_message.error_message_exist then
868 --
869 -- Catch the Multiple Message List exception which
870 -- indicates API processing has been aborted because
871 -- at least one message exists in the list.
872 --
873 rollback to return_from_deployment_swi;
874 --
875 -- Reset IN OUT parameters and set OUT parameters
876 --
877 p_object_version_number := l_object_version_number;
878 p_return_status := hr_multi_message.get_return_status_disable;
879 hr_utility.set_location(' Leaving:' || l_proc, 30);
880 when others then
881 --
882 -- When Multiple Message Detection is enabled catch
883 -- any Application specific or other unexpected
884 -- exceptions. Adding appropriate details to the
885 -- Multiple Message List. Otherwise re-raise the
886 -- error.
887 --
888 rollback to return_from_deployment_swi;
889 if hr_multi_message.unexpected_error_add(l_proc) then
890 hr_utility.set_location(' Leaving:' || l_proc,40);
891 raise;
892 end if;
893 --
894 -- Reset IN OUT and set OUT parameters
895 --
896 p_object_version_number := l_object_version_number;
897 p_return_status := hr_multi_message.get_return_status_disable;
898 hr_utility.set_location(' Leaving:' || l_proc,50);
899 end return_from_deployment;
900
901 -- ----------------------------------------------------------------------------
902 -- |--------------------------< update_attachment >----------------------------|
903 -- ----------------------------------------------------------------------------
904 --
905 procedure update_attachment
906 (p_entity_name in varchar2 default null
907 ,p_pk1_value in varchar2 default null
908 ,p_rowid in varchar2 ) is
909
910
911
912 l_proc varchar2(72) := g_package ||'update_attachment';
913 l_rowid varchar2(50);
914 l_language varchar2(30) ;
915 data_error exception;
916 --
917
918 cursor csr_get_attached_doc is
919 select *
920 from fnd_attached_documents
921 where rowid = p_rowid;
922 --
923 cursor csr_get_doc(csr_p_document_id in number) is
924 select *
925 from fnd_documents
926 where document_id = csr_p_document_id;
927 --
928 cursor csr_get_doc_tl (csr_p_lang in varchar2
929 ,csr_p_document_id in number) is
930 select *
931 from fnd_documents_tl
932 where document_id = csr_p_document_id
933 and language = csr_p_lang;
934 --
935 l_attached_doc_pre_upd csr_get_attached_doc%rowtype;
936 l_doc_pre_upd csr_get_doc%rowtype;
937 l_doc_tl_pre_upd csr_get_doc_tl%rowtype;
938 --
939 --
940 Begin
941 hr_utility.set_location(' Entering:' || l_proc,10);
942 --
943 -- Get language
944 select userenv('LANG') into l_language from dual;
945 --
946 -- Get the before update nullable fields which are not used by the
947 -- Web page to ensure the values are propagated.
948 Open csr_get_attached_doc;
949 fetch csr_get_attached_doc into l_attached_doc_pre_upd;
950 IF csr_get_attached_doc%NOTFOUND THEN
951 close csr_get_attached_doc;
952 raise data_error;
953 END IF;
954
955 Open csr_get_doc(l_attached_doc_pre_upd.document_id);
956 fetch csr_get_doc into l_doc_pre_upd;
957 IF csr_get_doc%NOTFOUND then
958 close csr_get_doc;
959 raise data_error;
960 END IF;
961
962 Open csr_get_doc_tl (csr_p_lang => l_language
963 ,csr_p_document_id => l_attached_doc_pre_upd.document_id);
964 fetch csr_get_doc_tl into l_doc_tl_pre_upd;
965 IF csr_get_doc_tl%NOTFOUND then
966 close csr_get_doc_tl;
967 raise data_error;
968 END IF;
969
970 hr_utility.set_location(' before fnd_attached_documents_pkg.lock_row :' || l_proc,20);
971 -- Now, lock the rows.
972 fnd_attached_documents_pkg.lock_row
973 (x_rowid => p_rowid
974 ,x_attached_document_id =>
975 l_attached_doc_pre_upd.attached_document_id
976 ,x_document_id => l_doc_pre_upd.document_id
977 ,x_seq_num => l_attached_doc_pre_upd.seq_num
978 ,x_entity_name => l_attached_doc_pre_upd.entity_name
979 ,x_column1 => l_attached_doc_pre_upd.column1
980 ,x_pk1_value => l_attached_doc_pre_upd.pk1_value
981 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
982 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
983 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
984 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
985 ,x_automatically_added_flag =>
986 l_attached_doc_pre_upd.automatically_added_flag
987 ,x_attribute_category =>
988 l_attached_doc_pre_upd.attribute_category
989 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
990 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
991 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
992 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
993 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
994 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
995 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
996 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
997 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
998 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
999 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1000 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1001 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1002 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1003 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1004 ,x_datatype_id => l_doc_pre_upd.datatype_id
1005 ,x_category_id => l_doc_pre_upd.category_id
1006 ,x_security_type => l_doc_pre_upd.security_type
1007 ,x_security_id => l_doc_pre_upd.security_id
1008 ,x_publish_flag => l_doc_pre_upd.publish_flag
1009 ,x_image_type => l_doc_pre_upd.image_type
1010 ,x_storage_type => l_doc_pre_upd.storage_type
1011 ,x_usage_type => l_doc_pre_upd.usage_type
1012 ,x_start_date_active => l_doc_pre_upd.start_date_active
1013 ,x_end_date_active => l_doc_pre_upd.end_date_active
1014 ,x_language => l_doc_tl_pre_upd.language
1015 ,x_description => l_doc_tl_pre_upd.description
1016 ,x_file_name => l_doc_pre_upd.file_name
1017 ,x_media_id => l_doc_pre_upd.media_id
1018 ,x_doc_attribute_category =>
1019 l_doc_tl_pre_upd.doc_attribute_category
1020 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1021 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1022 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1023 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1024 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1025 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1026 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1027 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1028 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1029 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1030 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1031 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1032 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1033 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1034 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15
1035 ,x_url => l_doc_pre_upd.url
1036 ,x_title => l_doc_tl_pre_upd.title
1037 );
1038
1039
1040 -- Update document to fnd_attached_documents, fnd_documents,
1041 -- fnd_documents_tl
1042 --
1043 hr_utility.set_location(' before fnd_attached_documents_pkg.update_row :' || l_proc,30);
1044 fnd_attached_documents_pkg.update_row
1045 (x_rowid => p_rowid
1046 ,x_attached_document_id =>
1047 l_attached_doc_pre_upd.attached_document_id
1048 ,x_document_id => l_doc_pre_upd.document_id
1049 ,x_last_update_date => trunc(sysdate)
1050 ,x_last_updated_by => l_attached_doc_pre_upd.last_updated_by
1051 ,x_seq_num => l_attached_doc_pre_upd.seq_num
1052 ,x_entity_name => p_entity_name
1053 ,x_column1 => l_attached_doc_pre_upd.column1
1054 ,x_pk1_value => p_pk1_value
1055 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
1056 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
1057 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
1058 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
1059 ,x_automatically_added_flag =>
1060 l_attached_doc_pre_upd.automatically_added_flag
1061 ,x_attribute_category =>
1062 l_attached_doc_pre_upd.attribute_category
1063 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
1064 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
1065 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
1066 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
1067 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
1068 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
1069 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
1070 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
1071 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
1072 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
1073 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1074 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1075 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1076 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1077 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1078 /* columns necessary for creating a document on the fly */
1079 ,x_datatype_id => l_doc_pre_upd.datatype_id
1080 ,x_category_id => l_doc_pre_upd.category_id
1081 ,x_security_type => l_doc_pre_upd.security_type
1082 ,x_security_id => l_doc_pre_upd.security_id
1083 ,x_publish_flag => l_doc_pre_upd.publish_flag
1084 ,x_image_type => l_doc_pre_upd.image_type
1085 ,x_storage_type => l_doc_pre_upd.storage_type
1086 ,x_usage_type => l_doc_pre_upd.usage_type
1087 ,x_start_date_active => trunc(sysdate)
1088 ,x_end_date_active => l_doc_pre_upd.end_date_active
1089 ,x_language => l_language
1090 ,x_description => l_doc_tl_pre_upd.description
1091 ,x_file_name => l_doc_pre_upd.file_name
1092 ,x_media_id => l_doc_pre_upd.media_id
1093 ,x_doc_attribute_category =>
1094 l_doc_tl_pre_upd.doc_attribute_category
1095 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1096 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1097 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1098 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1099 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1100 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1101 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1102 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1103 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1104 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1105 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1106 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1107 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1108 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1109 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15
1110 ,x_url => l_doc_pre_upd.url
1111 ,x_title => l_doc_tl_pre_upd.title
1112 );
1113
1114 hr_utility.set_location(' after fnd_attached_documents_pkg.update_row :' || l_proc,40);
1115 hr_utility.set_location(' Leaving:' || l_proc,50);
1116
1117 EXCEPTION
1118 when others then
1119 hr_utility.set_location(' Error in :' || l_proc,60);
1120 raise;
1121 --
1122 End update_attachment;
1123
1124 -- ----------------------------------------------------------------------------
1125 -- |--------------------------< merge_attachments >----------------------------|
1126 -- ----------------------------------------------------------------------------
1127 --
1128
1129 procedure merge_attachments(p_transaction_id in number,
1130 p_absence_attendance_id in number,
1131 p_return_status in out nocopy varchar2)
1132 is
1133 -- Other variables
1134 l_proc varchar2(72) := g_package ||'merge_attachments';
1135 l_rowid varchar2(50);
1136 lv_pk1_value varchar2(72) := p_absence_attendance_id||'_'||p_transaction_id ;
1137 data_error exception;
1138 lv_entity_name constant varchar2(30) := 'HR_PERSON_DEPLOYMENTS';
1139
1140 cursor csr_get_attached_doc is
1141 select *
1142 from fnd_attached_documents
1143 where entity_name=lv_entity_name
1144 and pk1_value=lv_pk1_value;
1145
1146 CURSOR C (X_attached_document_id in number) IS
1147 SELECT rowid
1148 FROM fnd_attached_documents
1149 WHERE attached_document_id = X_attached_document_id;
1150 --
1151 Begin
1152 hr_utility.set_location(' Entering:' || l_proc,10);
1153 --
1154 -- Issue a savepoint
1155 --
1156 savepoint attachments_person_absence_swi;
1157 --
1158 -- Initialise Multiple Message Detection
1159 --
1160 hr_multi_message.enable_message_list;
1161
1162 for attached_documents_rec in csr_get_attached_doc loop
1163 OPEN C (attached_documents_rec.attached_document_id);
1164 FETCH C INTO l_rowid;
1165 if (C%NOTFOUND) then
1166 CLOSE C;
1167 RAISE NO_DATA_FOUND;
1168 end if;
1169 CLOSE C;
1170 -- call the update_attachement for each attached doc
1171 update_attachment
1172 (p_entity_name=>lv_entity_name
1173 ,p_pk1_value=> p_absence_attendance_id
1174 ,p_rowid=>l_rowid);
1175
1176 end loop;
1177
1178 p_return_status := hr_multi_message.get_return_status_disable;
1179
1180 hr_utility.set_location(' Leaving:' || l_proc,20);
1181 --
1182 exception
1183 when others then
1184 --
1185 -- When Multiple Message Detection is enabled catch
1186 -- any Application specific or other unexpected
1187 -- exceptions. Adding appropriate details to the
1188 -- Multiple Message List. Otherwise re-raise the
1189 -- error.
1190 --
1191 rollback to attachments_person_absence_swi;
1192 if hr_multi_message.unexpected_error_add(l_proc) then
1193 hr_utility.set_location(' Leaving:' || l_proc,40);
1194 raise;
1195 end if;
1196 --
1197 -- Reset IN OUT and set OUT parameters
1198 p_return_status := hr_multi_message.get_return_status_disable;
1199
1200 hr_utility.set_location(' Leaving:' || l_proc,50);
1201 end merge_attachments;
1202
1203
1204
1205 Procedure process_api
1206 ( p_document in CLOB
1207 ,p_return_status out nocopy VARCHAR2
1208 ,p_validate in number default hr_api.g_false_num
1209 ,p_effective_date in date default null
1210 )
1211 IS
1212 l_postState VARCHAR2(2);
1213 l_return_status VARCHAR2(1);
1214 l_commitElement xmldom.DOMElement;
1215 l_object_version_number number;
1216 l_parser xmlparser.Parser;
1217 l_CommitNode xmldom.DOMNode;
1218 l_proc varchar2(72) := g_package || 'process_api';
1219
1220 l_occurrence number;
1221 lv_action varchar2(30);
1222 l_transaction_id number;
1223 l_person_deployment_id varchar2(240);
1224
1225
1226 Cursor c_get_depl(p_person_deployment_id number) is
1227 select person_deployment_id, from_person_id,from_business_group_id
1228 from hr_person_deployments
1229 where person_deployment_id = p_person_deployment_id;
1230
1231
1232 BEGIN
1233 --hr_utility.trace_on(null,'ORCLE');
1234 hr_utility.set_location(' Entering:' || l_proc,10);
1235 hr_utility.set_location(' CLOB --> xmldom.DOMNode:' || l_proc,15);
1236 savepoint deployment_process_api;
1237 l_parser := xmlparser.newParser;
1238 xmlparser.ParseCLOB(l_parser,p_document);
1239 l_CommitNode := xmldom.makeNode(xmldom.getDocumentElement(xmlparser.getDocument(l_parser)));
1240
1241 hr_utility.set_location('Extracting the PostState:' || l_proc,20);
1242
1243 l_commitElement := xmldom.makeElement(l_CommitNode);
1244 l_postState := xmldom.getAttribute(l_commitElement, 'PS');
1245 -- l_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
1246
1247 -- overiding for poststate
1248 -- CancelMode
1249 if(l_postState = '2') then
1250 -- Check if the transaction is for delete
1251 begin
1252 lv_action:= hr_xml_util.get_node_value(hr_transaction_swi.g_txn_ctx.TRANSACTION_ID,'GoldAction','Transaction/TransCtx/CNode',
1253 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1254 end;
1255 if(lv_action='ReturnMode') then
1256 -- reset the poststate
1257 l_postState:= '3';
1258 end if;
1259 end if;
1260
1261
1262 if(lv_action='UpdateMode') then
1263 -- set the poststate to update
1264 l_postState:= '2';
1265 end if;
1266
1267 if(l_postState = '0') then
1268 if(lv_action='ReturnMode') then
1269 -- reset the poststate
1270 l_postState:= '3';
1271 end if;
1272 end if;
1273
1274 if l_postState = '0' then
1275
1276 create_person_deployment
1277 (p_validate => p_validate
1278 ,p_from_business_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'FromBusinessGroupId',null)
1279 ,p_to_business_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ToBusinessGroupId',null)
1280 ,p_from_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'FromPersonId',null)
1281 ,p_to_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ToPersonId',null)
1282 ,p_person_type_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PersonTypeId',null)
1283 ,p_start_date => hr_transaction_swi.getDateValue(l_CommitNode,'StartDate',null)
1284 ,p_end_date => hr_transaction_swi.getDateValue(l_CommitNode,'EndDate',null)
1285 ,p_employee_number => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EmployeeNumber',null)
1286 ,p_leaving_reason => hr_transaction_swi.getVarchar2Value(l_CommitNode,'LeavingReason',null)
1287 ,p_leaving_person_type_id => hr_transaction_swi.getNumberValue(l_CommitNode,'LeavingPersonTypeId',null)
1288 ,p_permanent => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Permanent',null)
1289 ,p_Deplymt_Policy_Id => hr_transaction_swi.getNumberValue(l_CommitNode,'DeplymtPolicyId',null)
1290 ,p_organization_id => hr_transaction_swi.getNumberValue(l_CommitNode,'OrganizationId',null)
1291 ,p_location_id => hr_transaction_swi.getNumberValue(l_CommitNode,'LocationId',null)
1292 ,p_job_id => hr_transaction_swi.getNumberValue(l_CommitNode,'JobId',null)
1293 ,p_position_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PositionId',null)
1294 ,p_grade_id => hr_transaction_swi.getNumberValue(l_CommitNode,'GradeId',null)
1295 ,p_supervisor_id => hr_transaction_swi.getNumberValue(l_CommitNode,'SupervisorId',null)
1296 ,p_supervisor_assignment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'SupervisorAssignmentId',null)
1297 ,p_retain_direct_reports => hr_transaction_swi.getVarchar2Value(l_CommitNode,'RetainDirectReports',null)
1298 ,p_payroll_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PayrollId',null)
1299 ,p_pay_basis_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PayBasisId',null)
1300 ,p_proposed_salary => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ProposedSalary',null)
1301 ,p_people_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PeopleGroupId',null)
1302 ,p_soft_coding_keyflex_id => hr_transaction_swi.getNumberValue(l_CommitNode,'SoftCodingKeyflexId',null)
1303 ,p_assignment_status_type_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentStatusTypeId',null)
1304 ,p_ass_status_change_reason => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssStatusChangeReason',null)
1305 ,p_assignment_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssignmentCategory',null)
1306 ,p_per_information1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation1',null)
1307 ,p_per_information2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation2',null)
1308 ,p_per_information3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation3',null)
1309 ,p_per_information4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation4',null)
1310 ,p_per_information5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation5',null)
1311 ,p_per_information6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation6',null)
1312 ,p_per_information7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation7',null)
1313 ,p_per_information8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation8',null)
1314 ,p_per_information9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation9',null)
1315 ,p_per_information10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation10',null)
1316 ,p_per_information11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation11',null)
1317 ,p_per_information12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation12',null)
1318 ,p_per_information13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation13',null)
1319 ,p_per_information14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation14',null)
1320 ,p_per_information15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation15',null)
1321 ,p_per_information16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation16',null)
1322 ,p_per_information17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation17',null)
1323 ,p_per_information18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation18',null)
1324 ,p_per_information19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation19',null)
1325 ,p_per_information20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation20',null)
1326 ,p_per_information21 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation21',null)
1327 ,p_per_information22 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation22',null)
1328 ,p_per_information23 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation23',null)
1329 ,p_per_information24 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation24',null)
1330 ,p_per_information25 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation25',null)
1331 ,p_per_information26 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation26',null)
1332 ,p_per_information27 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation27',null)
1333 ,p_per_information28 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation28',null)
1334 ,p_per_information29 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation29',null)
1335 ,p_per_information30 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation30',null)
1336 ,p_deployment_reason => hr_transaction_swi.getvarchar2value(l_commitnode,'DeploymentReason',null)
1337 ,p_person_deployment_id => hr_transaction_swi.getvarchar2value(l_commitnode,'PersonDeploymentId',null)
1338 ,p_object_version_number => l_object_version_number
1339 ,p_return_status => l_return_status);
1340
1341
1342 /*Start of code for bug#14547912*/
1343 l_person_deployment_id := hr_transaction_swi.getvarchar2value(l_commitnode,'PersonDeploymentId',null);
1344 begin
1345 select max(hat.transaction_id)
1346 into l_transaction_id
1347 from hr_api_transactions hat,hr_api_transaction_steps hats
1348 where hat.transaction_id = hats.transaction_id
1349 and hats.information1 = l_person_deployment_id
1350 and hats.information12 = 'CreateMode';
1351 exception
1352 when others then
1353 l_transaction_id := null;
1354 end;
1355
1356 if (l_transaction_id is not null)
1357 then
1358 set_start_date(l_transaction_id,hr_transaction_swi.getDateValue(l_CommitNode,'StartDate',null));
1359 end if;
1360
1361 /*End of code for bug#14547912*/
1362
1363 elsif l_postState = '2' then
1364
1365 l_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
1366
1367 update_person_deployment
1368 (p_validate => p_validate
1369 ,p_person_deployment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PersonDeploymentId')
1370 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1371 ,P_PERSON_TYPE_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'PersonTypeId')
1372 ,P_START_DATE => hr_transaction_swi.getDateValue(l_CommitNode,'StartDate')
1373 ,P_END_DATE => hr_transaction_swi.getDateValue(l_CommitNode,'EndDate')
1374 ,P_EMPLOYEE_NUMBER => hr_transaction_swi.getVarchar2Value(l_CommitNode,'EmployeeNumber')
1375 ,P_LEAVING_REASON => hr_transaction_swi.getVarchar2Value(l_CommitNode,'LeavingReason')
1376 ,P_LEAVING_PERSON_TYPE_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'LeavingPersonTypeId')
1377 ,P_STATUS => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Status')
1378 ,P_STATUS_CHANGE_REASON => hr_transaction_swi.getVarchar2Value(l_CommitNode,'StatusChangeReason')
1379 ,P_DEPLYMT_POLICY_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'DeplymtPolicyId')
1380 ,P_ORGANIZATION_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'OrganizationId')
1381 ,P_LOCATION_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'LocationId')
1382 ,P_JOB_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'JobId')
1383 ,P_POSITION_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'PositionId')
1384 ,P_GRADE_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'GradeId')
1385 ,P_SUPERVISOR_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'SupervisorId')
1386 ,P_SUPERVISOR_ASSIGNMENT_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'SupervisorAssignmentId')
1387 ,P_RETAIN_DIRECT_REPORTS => hr_transaction_swi.getVarchar2Value(l_CommitNode,'RetainDirectReports')
1388 ,P_PAYROLL_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'PayrollId')
1389 ,P_PAY_BASIS_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'PayBasisId')
1390 ,P_PROPOSED_SALARY => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ProposedSalary')
1391 ,P_PEOPLE_GROUP_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'PeopleGroupId')
1392 ,P_SOFT_CODING_KEYFLEX_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'SoftCodingKeyflexId')
1393 ,P_ASSIGNMENT_STATUS_TYPE_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentStatusTypeId')
1394 ,P_ASS_STATUS_CHANGE_REASON => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssStatusChangeReason')
1395 ,P_ASSIGNMENT_CATEGORY => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AssignmentCategory')
1396 ,P_PER_INFORMATION1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation1')
1397 ,P_PER_INFORMATION2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation2')
1398 ,P_PER_INFORMATION3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation3')
1399 ,P_PER_INFORMATION4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation4')
1400 ,P_PER_INFORMATION5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation5')
1401 ,P_PER_INFORMATION6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation6')
1402 ,P_PER_INFORMATION7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation7')
1403 ,P_PER_INFORMATION8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation8')
1404 ,P_PER_INFORMATION9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation9')
1405 ,P_PER_INFORMATION10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation10')
1406 ,P_PER_INFORMATION11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation11')
1407 ,P_PER_INFORMATION12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation12')
1408 ,P_PER_INFORMATION13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation13')
1409 ,P_PER_INFORMATION14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation14')
1410 ,P_PER_INFORMATION15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation15')
1411 ,P_PER_INFORMATION16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation16')
1412 ,P_PER_INFORMATION17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation17')
1413 ,P_PER_INFORMATION18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation18')
1414 ,P_PER_INFORMATION19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation19')
1415 ,P_PER_INFORMATION20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation20')
1416 ,P_PER_INFORMATION21 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation21')
1417 ,P_PER_INFORMATION22 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation22')
1418 ,P_PER_INFORMATION23 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation23')
1419 ,P_PER_INFORMATION24 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation24')
1420 ,P_PER_INFORMATION25 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation25')
1421 ,P_PER_INFORMATION26 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation26')
1422 ,P_PER_INFORMATION27 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation27')
1423 ,P_PER_INFORMATION28 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation28')
1424 ,P_PER_INFORMATION29 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation29')
1425 ,P_PER_INFORMATION30 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PerInformation30')
1426 ,P_DEPLOYMENT_REASON => hr_transaction_swi.getVarchar2Value(l_CommitNode,'DeploymentReason')
1427 ,p_return_status => l_return_status);
1428
1429
1430 elsif l_postState = '3' then
1431
1432 l_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
1433 return_from_deployment
1434 (p_validate => p_validate
1435 ,p_person_deployment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PersonDeploymentId')
1436 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1437 ,P_END_DATE => to_date(to_char(hr_transaction_swi.getDateValue(l_CommitNode,'EndDate'),'RRRR-MM-DD'),'RRRR-MM-DD') -- Modified for bug#14549229
1438 ,P_LEAVING_REASON => hr_transaction_swi.getVarchar2Value(l_CommitNode,'LeavingReason')
1439 ,P_LEAVING_PERSON_TYPE_ID => hr_transaction_swi.getNumberValue(l_CommitNode,'LeavingPersonTypeId')
1440 ,p_return_status => l_return_status);
1441
1442
1443 end if;
1444
1445 -- finally call the attachements update
1446 if( l_return_status <> 'E') then
1447 merge_attachments(hr_transaction_swi.g_txn_ctx.TRANSACTION_ID,
1448 hr_transaction_swi.getNumberValue(l_CommitNode,'PersonDeploymentId'),
1449 l_return_status);
1450 end if;
1451
1452 p_return_status := l_return_status;
1453 hr_utility.set_location( l_proc||'p_return_status = '||p_return_status,38);
1454 hr_utility.set_location('Exiting:' || l_proc,40);
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 rollback to deployment_process_api;
1458 hr_utility.trace('Exception in .process_api:' || SQLERRM );
1459 hr_utility.set_location(' Leaving:' || l_proc,50);
1460
1461 raise;
1462
1463 END process_api;
1464
1465 /*****************************************************************************************/
1466 /*FUNCTION TO GET PERSON DEPLOYMENT TYPE FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1467 /*****************************************************************************************/
1468
1469
1470 function getDeploymentType(p_transaction_id in number,
1471 p_person_deployment_id in number) return varchar2
1472
1473 IS
1474 c_proc constant varchar2(30) := 'getDeploymentType';
1475 lv_permanent hr_api_transaction_steps.Information8%type;
1476 begin
1477 g_debug := hr_utility.debug_enabled;
1478 if g_debug then
1479 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1480 end if;
1481
1482 if(p_transaction_id is not null) then
1483 begin
1484 select Information8
1485 into lv_permanent
1486 from hr_api_transaction_steps
1487 where transaction_id=p_transaction_id;
1488 exception
1489 when others then
1490 lv_permanent:=null;
1491 end;
1492 end if;
1493
1494 if lv_permanent = 'Y'
1495 then
1496 lv_permanent := 'P';
1497 else
1498 if lv_permanent is null OR lv_permanent = 'N'
1499 then
1500 lv_permanent := 'T';
1501 end if;
1502 end if;
1503
1504 return lv_permanent;
1505 if g_debug then
1506 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1507 end if;
1508
1509 exception
1510 when others then
1511 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1512 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1513 raise;
1514 end getDeploymentType;
1515
1516
1517
1518 /****************************************************************************************/
1519 /*FUNCTION TO GET PERSON DEPLOYMENT ID FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1520 /****************************************************************************************/
1521
1522 function getPersonDeploymentId(p_transaction_id in number,
1523 p_person_deployment_id in number) return number
1524
1525 IS
1526 c_proc constant varchar2(30) := 'getPersonDeploymentId';
1527 lv_deployment_id hr_api_transaction_steps.Information1%type;
1528 begin
1529 g_debug := hr_utility.debug_enabled;
1530 if g_debug then
1531 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1532 end if;
1533
1534 if(p_transaction_id is not null) then
1535 begin
1536 select Information1
1537 into lv_deployment_id
1538 from hr_api_transaction_steps
1539 where transaction_id=p_transaction_id;
1540 exception
1541 when others then
1542 lv_deployment_id:=null;
1543 end;
1544 end if;
1545
1546
1547 return lv_deployment_id;
1548
1549 if g_debug then
1550 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1551 end if;
1552
1553 exception
1554 when others then
1555 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1556 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1557 raise;
1558 end getPersonDeploymentId;
1559
1560
1561
1562
1563 /*********************************************************************************/
1564 /*FUNCTION TO GET FROM_PERSON_ID FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1565 /*********************************************************************************/
1566
1567 function getFromPersonId(p_transaction_id in number,
1568 p_person_deployment_id in number) return number
1569
1570 IS
1571 c_proc constant varchar2(30) := 'getFromPersonId';
1572 lv_from_person_id hr_api_transaction_steps.Information2%type;
1573 begin
1574 g_debug := hr_utility.debug_enabled;
1575 if g_debug then
1576 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1577 end if;
1578
1579 if(p_transaction_id is not null) then
1580 begin
1581 select Information2
1582 into lv_from_person_id
1583 from hr_api_transaction_steps
1584 where transaction_id=p_transaction_id;
1585 exception
1586 when others then
1587 lv_from_person_id:=null;
1588 end;
1589 end if;
1590
1591
1592 return lv_from_person_id;
1593
1594 if g_debug then
1595 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1596 end if;
1597
1598 exception
1599 when others then
1600 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1601 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1602 raise;
1603 end getFromPersonId;
1604
1605 /***************************************************************************************/
1606 /*FUNCTION TO GET TO_BUSINESS_GROUP_ID FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1607 /***************************************************************************************/
1608
1609 function getToBgId(p_transaction_id in number,
1610 p_person_deployment_id in number) return number
1611
1612 IS
1613 c_proc constant varchar2(30) := 'getToBgId';
1614 lv_to_bg_id hr_api_transaction_steps.Information3%type;
1615 begin
1616 g_debug := hr_utility.debug_enabled;
1617 if g_debug then
1618 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1619 end if;
1620
1621 if(p_transaction_id is not null) then
1622 begin
1623 select Information3
1624 into lv_to_bg_id
1625 from hr_api_transaction_steps
1626 where transaction_id=p_transaction_id;
1627 exception
1628 when others then
1629 lv_to_bg_id:=null;
1630 end;
1631 end if;
1632
1633
1634 return lv_to_bg_id;
1635
1636 if g_debug then
1637 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1638 end if;
1639
1640 exception
1641 when others then
1642 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1643 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1644 raise;
1645 end getToBgId;
1646
1647 /*********************************************************************************/
1648 /*FUNCTION TO GET FROM_PERSON_ID FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1649 /*********************************************************************************/
1650
1651 function getPersonTypeId(p_transaction_id in number,
1652 p_person_deployment_id in number) return number
1653
1654 IS
1655 c_proc constant varchar2(30) := 'getPersonTypeId';
1656 lv_person_type_id hr_api_transaction_steps.Information4%type;
1657 begin
1658 g_debug := hr_utility.debug_enabled;
1659 if g_debug then
1660 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1661 end if;
1662
1663 if(p_transaction_id is not null) then
1664 begin
1665 select Information4
1666 into lv_person_type_id
1667 from hr_api_transaction_steps
1668 where transaction_id=p_transaction_id;
1669 exception
1670 when others then
1671 lv_person_type_id:=null;
1672 end;
1673 end if;
1674
1675
1676 return lv_person_type_id;
1677
1678 if g_debug then
1679 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1680 end if;
1681
1682 exception
1683 when others then
1684 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1685 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1686 raise;
1687 end getPersonTypeId;
1688
1689
1690
1691 /*****************************************************************************/
1692 /*FUNCTION TO GET START_DATE FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1693 /*****************************************************************************/
1694
1695 function getStartDate(p_transaction_id in number,
1696 p_person_deployment_id in number) return date
1697
1698 IS
1699 c_proc constant varchar2(30) := 'getStartDate';
1700 lv_start_date hr_api_transaction_steps.Information5%type;
1701 begin
1702 g_debug := hr_utility.debug_enabled;
1703 if g_debug then
1704 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1705 end if;
1706
1707 if(p_transaction_id is not null) then
1708 begin
1709 select to_date(Information5,'YYYY-MM-DD')
1710 into lv_start_date
1711 from hr_api_transaction_steps
1712 where transaction_id=p_transaction_id;
1713 exception
1714 when others then
1715 lv_start_date:=null;
1716 end;
1717 end if;
1718
1719
1720 return lv_start_date;
1721
1722 if g_debug then
1723 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1724 end if;
1725
1726 exception
1727 when others then
1728 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1729 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1730 raise;
1731 end getStartDate;
1732
1733
1734 /***************************************************************************/
1735 /*FUNCTION TO GET END_DATE FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1736 /***************************************************************************/
1737
1738 function getEndDate(p_transaction_id in number,
1739 p_person_deployment_id in number) return date
1740
1741 IS
1742 c_proc constant varchar2(30) := 'getEndDate';
1743 lv_end_date hr_api_transaction_steps.Information6%type;
1744
1745 begin
1746 g_debug := hr_utility.debug_enabled;
1747 if g_debug then
1748 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1749 end if;
1750
1751 if(p_transaction_id is not null) then
1752 begin
1753 select to_date(Information6,'YYYY-MM-DD')
1754 into lv_end_date
1755 from hr_api_transaction_steps
1756 where transaction_id=p_transaction_id;
1757 exception
1758 when others then
1759 lv_end_date:=null;
1760 end;
1761 end if;
1762
1763
1764 return lv_end_date;
1765
1766 if g_debug then
1767 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1768 end if;
1769
1770 exception
1771 when others then
1772 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1773 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1774 raise;
1775 end getEndDate;
1776
1777
1778
1779
1780 /**********************************************************************************/
1781 /*FUNCTION TO GET EMPLOYEE_NUMBER FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1782 /**********************************************************************************/
1783
1784 function getEmployeeNumber(p_transaction_id in number,
1785 p_person_deployment_id in number) return varchar2
1786
1787 IS
1788 c_proc constant varchar2(30) := 'getEmployeeNumber';
1789 lv_employee_number hr_api_transaction_steps.Information7%type;
1790
1791 begin
1792 g_debug := hr_utility.debug_enabled;
1793 if g_debug then
1794 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1795 end if;
1796
1797 if(p_transaction_id is not null) then
1798 begin
1799 select Information7
1800 into lv_employee_number
1801 from hr_api_transaction_steps
1802 where transaction_id=p_transaction_id;
1803 exception
1804 when others then
1805 lv_employee_number:=null;
1806 end;
1807 end if;
1808
1809
1810 return lv_employee_number;
1811
1812 if g_debug then
1813 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1814 end if;
1815
1816 exception
1817 when others then
1818 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1819 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1820 raise;
1821 end getEmployeeNumber;
1822
1823
1824
1825 /*****************************************************************************************/
1826 /*FUNCTION TO GET FROM_BUSINESS_GROUP_ID FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1827 /*****************************************************************************************/
1828
1829 function getFromBgId(p_transaction_id in number,
1830 p_person_deployment_id in number) return number
1831
1832 IS
1833 c_proc constant varchar2(30) := 'getFromBgId';
1834 lv_to_bg_id hr_api_transaction_steps.Information9%type;
1835
1836 begin
1837 g_debug := hr_utility.debug_enabled;
1838 if g_debug then
1839 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1840 end if;
1841
1842 if(p_transaction_id is not null) then
1843 begin
1844 select Information9
1845 into lv_to_bg_id
1846 from hr_api_transaction_steps
1847 where transaction_id=p_transaction_id;
1848 exception
1849 when others then
1850 lv_to_bg_id:=null;
1851 end;
1852 end if;
1853
1854
1855 return lv_to_bg_id;
1856
1857 if g_debug then
1858 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1859 end if;
1860
1861 exception
1862 when others then
1863 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1864 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1865 raise;
1866 end getFromBgId;
1867
1868
1869
1870 /**********************************************************************************/
1871 /*FUNCTION TO GET ORGANIZATION_ID FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1872 /**********************************************************************************/
1873
1874 function getOrganizationId(p_transaction_id in number,
1875 p_person_deployment_id in number) return number
1876
1877 IS
1878 c_proc constant varchar2(30) := 'getOrganizationId';
1879 lv_organization_id hr_api_transaction_steps.Information10%type;
1880
1881 begin
1882 g_debug := hr_utility.debug_enabled;
1883 if g_debug then
1884 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1885 end if;
1886
1887 if(p_transaction_id is not null) then
1888 begin
1889 select Information10
1890 into lv_organization_id
1891 from hr_api_transaction_steps
1892 where transaction_id=p_transaction_id;
1893 exception
1894 when others then
1895 lv_organization_id:=null;
1896 end;
1897 end if;
1898
1899
1900 return lv_organization_id;
1901
1902 if g_debug then
1903 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1904 end if;
1905
1906 exception
1907 when others then
1908 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1909 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1910 raise;
1911 end getOrganizationId;
1912
1913 /***************************************************************************/
1914 /*FUNCTION TO GET JOB_ID FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
1915 /***************************************************************************/
1916
1917 function getJobId(p_transaction_id in number,
1918 p_person_deployment_id in number) return number
1919
1920 IS
1921 c_proc constant varchar2(30) := 'getJobId';
1922 lv_job_id hr_api_transaction_steps.Information11%type;
1923
1924 begin
1925 g_debug := hr_utility.debug_enabled;
1926 if g_debug then
1927 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1928 end if;
1929
1930 if(p_transaction_id is not null) then
1931 begin
1932 select Information11
1933 into lv_job_id
1934 from hr_api_transaction_steps
1935 where transaction_id=p_transaction_id;
1936 exception
1937 when others then
1938 lv_job_id:=null;
1939 end;
1940 end if;
1941
1942
1943 return lv_job_id;
1944
1945 if g_debug then
1946 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1947 end if;
1948
1949 exception
1950 when others then
1951 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1952 Wf_Core.Context(g_package, c_proc, p_transaction_id);
1953 raise;
1954 end getJobId;
1955
1956 /*********************************/
1957 /*FUNCTION -- IS UPDATE ALLOWED */
1958 /*********************************/
1959
1960
1961
1962 function isUpdateAllowed(p_transaction_id in number,
1963 p_person_deployment_id in number,
1964 p_deployment_type in Varchar2,
1965 p_transaction_status in varchar2) return varchar2
1966
1967 IS
1968 c_proc constant varchar2(30) := 'isUpdateAllowed';
1969 lv_UpdateAllowed varchar2(30);
1970
1971 begin
1972 g_debug := hr_utility.debug_enabled;
1973 if g_debug then
1974 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1975 end if;
1976
1977 -- need to revisit with the common code for handling update
1978 -- based on the current transaction owner
1979
1980 -- for now this will only allow for transaction owner to update
1981
1982 if(p_transaction_id is not null) then
1983 if(hr_transaction_swi.istxnowner(p_transaction_id,fnd_global.employee_id)) then
1984 if p_deployment_type ='P' then
1985 if p_transaction_status = 'APPROVED' then
1986 lv_UpdateAllowed := 'UpdateEnabled1';
1987 else
1988 lv_UpdateAllowed := 'UpdateDisabled';
1989 end if;
1990 else
1991 if p_transaction_status = 'APPROVED' then
1992 lv_UpdateAllowed := 'UpdateEnabled1';
1993 else
1994 lv_UpdateAllowed := 'UpdateDisabled';
1995 end if;
1996
1997 end if;
1998
1999 else
2000 lv_UpdateAllowed := 'UpdateDisabled';
2001 end if;
2002
2003
2004 end if;
2005
2006 return lv_UpdateAllowed;
2007
2008 if g_debug then
2009 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2010 end if;
2011
2012 exception
2013 when others then
2014 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2015 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2016 raise;
2017 end isUpdateAllowed;
2018
2019
2020 /*********************************/
2021 /*FUNCTION -- IS DELETE ALLOWED */
2022 /*********************************/
2023
2024
2025
2026 function isDeleteAllowed(p_transaction_id in number,
2027 p_person_deployment_id in number,
2028 p_deployment_type in Varchar2,
2029 p_transaction_status in varchar2) return varchar2
2030
2031 IS
2032 c_proc constant varchar2(30) := 'isDeleteAllowed';
2033 lv_DeleteAllowed varchar2(30);
2034
2035 begin
2036 g_debug := hr_utility.debug_enabled;
2037 if g_debug then
2038 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2039 end if;
2040
2041 -- need to revisit with the common code for handling update
2042 -- based on the current transaction owner
2043
2044 -- for now this will only allow for transaction owner to update
2045
2046 if(p_transaction_id is not null) then
2047 if(hr_transaction_swi.istxnowner(p_transaction_id,fnd_global.employee_id)) then
2048 if p_deployment_type ='P' then
2049 if p_transaction_status in ('COMPLETE','APPROVED','REJECTED') then
2050 lv_DeleteAllowed := 'DeleteEnabled';
2051 elsif (p_transaction_status in ('PENDING_APPROVAL','RETURN_INIT_PENDING_APPROVAL')) then
2052 if (fnd_profile.value('HR_APRVL_TXN_INITIATOR_DEL_ENABLED') = 'Y')
2053 then
2054 lv_DeleteAllowed := 'DeleteEnabled';
2055 else
2056 lv_DeleteAllowed := 'DeleteDisabled';
2057 end if;
2058 else
2059 lv_DeleteAllowed := 'DeleteDisabled';
2060 end if;
2061 else
2062 if p_transaction_status in ('APPROVED','COMPLETE','REJECTED') then
2063 lv_DeleteAllowed := 'DeleteEnabled';
2064 elsif (p_transaction_status in ('PENDING_APPROVAL','RETURN_INIT_PENDING_APPROVAL')) then
2065 if (fnd_profile.value('HR_APRVL_TXN_INITIATOR_DEL_ENABLED') = 'Y')
2066 then
2067 lv_DeleteAllowed := 'DeleteEnabled';
2068 else
2069 lv_DeleteAllowed := 'DeleteDisabled';
2070 end if;
2071 else
2072 lv_DeleteAllowed := 'DeleteDisabled';
2073 end if;
2074
2075 end if;
2076
2077 else
2078 lv_DeleteAllowed := 'DeleteDisabled';
2079 end if;
2080 end if;
2081
2082 return lv_DeleteAllowed;
2083
2084 if g_debug then
2085 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2086 end if;
2087
2088 exception
2089 when others then
2090 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2091 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2092 raise;
2093 end isDeleteAllowed;
2094
2095
2096 /*******************************/
2097 /*FUNCTION -- IS INIT ALLOWED */
2098 /*******************************/
2099
2100
2101
2102 function isInitAllowed(p_transaction_id in number,
2103 p_person_deployment_id in number,
2104 p_transaction_status in varchar2) return varchar2
2105
2106 IS
2107 c_proc constant varchar2(30) := 'isInitAllowed';
2108 lv_Init_Allowed varchar2(30);
2109
2110 begin
2111 g_debug := hr_utility.debug_enabled;
2112 if g_debug then
2113 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2114 end if;
2115
2116 -- need to revisit with the common code for handling update
2117 -- based on the current transaction owner
2118
2119 -- for now this will only allow for transaction owner to update
2120
2121 if(p_transaction_id is not null) then
2122 if(hr_transaction_swi.istxnowner(p_transaction_id,fnd_global.employee_id)) then
2123
2124 if p_transaction_status ='APPROVED' then
2125 lv_Init_Allowed := 'InitEnabled';
2126 else
2127 lv_Init_Allowed := 'InitDisabled';
2128 end if;
2129 else
2130 lv_Init_Allowed := 'InitDisabled';
2131 end if;
2132 end if;
2133
2134 return lv_Init_Allowed;
2135
2136 if g_debug then
2137 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2138 end if;
2139
2140 exception
2141 when others then
2142 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2143 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2144 raise;
2145 end isInitAllowed;
2146
2147
2148 /**************************************/
2149 /*FUNCTION -- IS RETURN INIT ALLOWED */
2150 /**************************************/
2151
2152
2153
2154 function isReturnInitAllowed(p_transaction_id in number,
2155 p_person_deployment_id in number,
2156 p_deployment_type in Varchar2,
2157 p_transaction_status in varchar2) return varchar2
2158
2159 IS
2160 c_proc constant varchar2(30) := 'isReturnInitAllowed';
2161 lv_ret_Init_Allowed varchar2(30);
2162
2163 begin
2164 g_debug := hr_utility.debug_enabled;
2165 if g_debug then
2166 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2167 end if;
2168
2169 -- need to revisit with the common code for handling update
2170 -- based on the current transaction owner
2171
2172 -- for now this will only allow for transaction owner to update
2173
2174 if(p_transaction_id is not null) then
2175 if(hr_transaction_swi.istxnowner(p_transaction_id,fnd_global.employee_id)) then
2176 if p_deployment_type = 'P' then
2177 lv_ret_Init_Allowed := 'ReturnDisabled';
2178 else
2179 if p_transaction_status in ('ACTIVE') then
2180 lv_ret_Init_Allowed := 'ReturnEnabled';
2181 else
2182 lv_ret_Init_Allowed := 'ReturnDisabled';
2183 end if;
2184 end if;
2185
2186 else
2187 lv_ret_Init_Allowed := 'ReturnDisabled';
2188 end if;
2189 end if;
2190
2191 return lv_ret_Init_Allowed;
2192
2193 if g_debug then
2194 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2195 end if;
2196
2197 exception
2198 when others then
2199 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2200 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2201 raise;
2202 end isReturnInitAllowed;
2203
2204
2205 /***************************************************************************************/
2206 /*FUNCTION TO GET ACTION FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
2207 /***************************************************************************************/
2208
2209 function getActionMode(p_transaction_id in number,
2210 p_person_deployment_id in number) return varchar2
2211
2212 IS
2213 c_proc constant varchar2(30) := 'getActionMode';
2214 lv_action_mode hr_api_transaction_steps.Information12%type;
2215 begin
2216 g_debug := hr_utility.debug_enabled;
2217 if g_debug then
2218 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2219 end if;
2220
2221 if(p_transaction_id is not null) then
2222 begin
2223 select Information12
2224 into lv_action_mode
2225 from hr_api_transaction_steps
2226 where transaction_id=p_transaction_id;
2227 exception
2228 when others then
2229 lv_action_mode:=null;
2230 end;
2231 end if;
2232
2233
2234 return lv_action_mode;
2235
2236 if g_debug then
2237 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2238 end if;
2239
2240 exception
2241 when others then
2242 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2243 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2244 raise;
2245 end getActionMode;
2246
2247 /***************************************************************************************/
2248 /*FUNCTION TO GET STATUS FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
2249 /***************************************************************************************/
2250
2251 function getStatus(p_transaction_id in number,
2252 p_person_deployment_id in number,
2253 p_transaction_status in varchar2) return varchar2
2254
2255 IS
2256 c_proc constant varchar2(30) := 'getStatus';
2257 lv_action_mode hr_api_transaction_steps.Information12%type;
2258 lv_status Varchar2(40);
2259 begin
2260 -- hr_utility.trace_on (null,'ORAC');
2261 g_debug := hr_utility.debug_enabled;
2262 hr_utility.set_location(c_proc||'p_transaction_id = '||p_transaction_id,12);
2263 hr_utility.set_location(c_proc||'p_person_deployment_id = '||p_person_deployment_id,13);
2264 hr_utility.set_location(c_proc||'p_transaction_status = '||p_transaction_status,14);
2265 if g_debug then
2266 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2267 end if;
2268
2269 if(p_transaction_id is not null) then
2270 begin
2271 select Information12
2272 into lv_action_mode
2273 from hr_api_transaction_steps
2274 where transaction_id=p_transaction_id;
2275 exception
2276 when others then
2277 lv_action_mode:=null;
2278 end;
2279 end if;
2280 hr_utility.set_location(c_proc||'lv_action_mode = '||lv_action_mode,15);
2281 if (lv_action_mode is not null) then
2282
2283 if lv_action_mode in ('CreateMode','UpdateMode','ActUpdateMode') then
2284 if p_transaction_status in ('AC') then
2285 lv_status := 'APPROVED';
2286 elsif p_transaction_status in ('Y','YS') then
2287 lv_status := 'PENDING_APPROVAL';
2288 elsif p_transaction_status in ('C','RI','RIS','RO','ROS') then
2289 lv_status := 'RETURNED_FOR_CORRECTION';
2290 elsif p_transaction_status in ('REJECT') then
2291 lv_status := 'REJECTED';
2292 end if;
2293 elsif lv_action_mode in ('ReturnMode') then
2294 if p_transaction_status in ('AC') then
2295 lv_status := 'RETURN_COMPLETE';
2296 elsif p_transaction_status in ('Y','YS') then
2297 lv_status := 'RETURN_INIT_PENDING_APPROVAL';
2298 elsif p_transaction_status in ('C','RI','RIS','RO','ROS') then
2299 lv_status := 'RETURN_INIT_RETURN_FOR_CRRCTN';
2300 elsif p_transaction_status in ('REJECT') then
2301 lv_status := 'REJECTED';
2302 end if;
2303 end if;
2304 end if;
2305
2306
2307 return lv_status;
2308
2309 if g_debug then
2310 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2311 end if;
2312
2313 exception
2314 when others then
2315 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2316 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2317 raise;
2318 end getStatus;
2319
2320
2321
2322 /*********************************************************************************/
2323 /*FUNCTION TO GET TO_PERSON_ID FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
2324 /*********************************************************************************/
2325
2326 function getToPersonId(p_transaction_id in number,
2327 p_person_deployment_id in number) return number
2328
2329 IS
2330 c_proc constant varchar2(30) := 'getToPersonId';
2331 lv_from_person_id hr_api_transaction_steps.Information13%type;
2332 begin
2333 g_debug := hr_utility.debug_enabled;
2334 if g_debug then
2335 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2336 end if;
2337
2338 if(p_transaction_id is not null) then
2339 begin
2340 select Information13
2341 into lv_from_person_id
2342 from hr_api_transaction_steps
2343 where transaction_id=p_transaction_id;
2344 exception
2345 when others then
2346 lv_from_person_id:=null;
2347 end;
2348 end if;
2349
2350
2351 return lv_from_person_id;
2352
2353 if g_debug then
2354 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2355 end if;
2356
2357 exception
2358 when others then
2359 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2360 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2361 raise;
2362 end getToPersonId;
2363
2364
2365 /****************************************************************************************/
2366 /*FUNCTION TO GET OBJECT_VERSION_NUMBER FROM TRANSACTION STEP AFTER DENORMALIZING THE EO*/
2367 /****************************************************************************************/
2368
2369 function getOvn(p_transaction_id in number,
2370 p_person_deployment_id in number) return number
2371
2372 IS
2373 c_proc constant varchar2(30) := 'getOvn';
2374 lv_ovn hr_api_transaction_steps.Information14%type;
2375 begin
2376 g_debug := hr_utility.debug_enabled;
2377 if g_debug then
2378 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2379 end if;
2380
2381 if(p_transaction_id is not null) then
2382 begin
2383 select Information14
2384 into lv_ovn
2385 from hr_api_transaction_steps
2386 where transaction_id=p_transaction_id;
2387 exception
2388 when others then
2389 lv_ovn:=null;
2390 end;
2391 end if;
2392
2393
2394 return lv_ovn;
2395
2396 if g_debug then
2397 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2398 end if;
2399
2400 exception
2401 when others then
2402 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2403 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2404 raise;
2405 end getOvn;
2406
2407
2408 /***************************************************************************************/
2409 /*FUNCTION TO GET STATUS OF DEPLOYMENT AFTER APPROVAL AND REJECTION */
2410 /***************************************************************************************/
2411
2412 function getDeploymentStatus(p_deployment_id in number,
2413 p_deployment_status in varchar2) return varchar2
2414
2415 IS
2416 c_proc constant varchar2(30) := 'getDeploymentStatus';
2417 lv_status Varchar2(40) := p_deployment_status;
2418 lv_transaction_count number := 0;
2419 lv_applied varchar2(2) := 'N';
2420 lv_approval_required varchar2(2) := 'N';
2421 lv_action varchar2(20) := NULL;
2422
2423 begin
2424 -- hr_utility.trace_on (null,'ORAC');
2425 g_debug := hr_utility.debug_enabled;
2426
2427 if g_debug then
2428 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2429 end if;
2430
2431 if upper(p_deployment_status) = 'DRAFT'
2432 then
2433 select count(hat.transaction_id)
2434 into lv_transaction_count
2435 from hr_api_transactions hat,
2436 hr_api_transaction_steps hats
2437 where hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2438 and hats.transaction_id = hat.transaction_id
2439 and hats.information1 = to_char(p_deployment_id)
2440 and not(hr_person_deployment_swi.getDeploymentType(hat.transaction_id,NULL) IS NULL and hat.status = 'W')
2441 and hat.status in ('AC')
2442 and not exists ( select 'e'
2443 from hr_api_transactions t,
2444 hr_api_transaction_steps hat
2445 WHERE t.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2446 and hat.transaction_id = t.transaction_id
2447 and hat.information1 = to_char(p_deployment_id)
2448 and not(hr_person_deployment_swi.getDeploymentType(t.transaction_id,NULL) IS NULL and t.status = 'W')
2449 and t.status not in ('D','E','AC'));
2450
2451 if lv_transaction_count = 0
2452 then
2453 lv_status := p_deployment_status;
2454 else
2455 begin
2456 select ACTION
2457 into lv_action
2458 from PQH_SS_APPROVAL_HISTORY
2459 where transaction_history_id in (select hat.transaction_id
2460 from hr_api_transactions hat, hr_api_transaction_steps hats
2461 where hat.transaction_id = hats.transaction_id
2462 and hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2463 and hats.information1 = to_char(p_deployment_id)
2464 and not(hr_person_deployment_swi.getDeploymentType(hat.transaction_id,NULL) IS NULL and hat.status = 'W') )
2465 and creation_date = (select max(CREATION_DATE)
2466 from PQH_SS_APPROVAL_HISTORY
2467 where transaction_history_id in (select hat.transaction_id
2468 from hr_api_transactions hat, hr_api_transaction_steps hats
2469 where hat.transaction_id = hats.transaction_id
2470 and hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2471 and hats.information1 = to_char(p_deployment_id)
2472 and not(hr_person_deployment_swi.getDeploymentType(hat.transaction_id,NULL) IS NULL and hat.status = 'W') )
2473 );
2474
2475 exception
2476 when others then lv_action := null;
2477 end;
2478
2479 if lv_action in ('APPROVED','RESUBMIT') /*Added RESUBMIT for bug#13964565*/
2480 then
2481 lv_status := 'APPROVED';
2482 elsif lv_action = 'REJECTED'
2483 then
2484 lv_status := 'REJECTED';
2485 else
2486 lv_status := p_deployment_status;
2487 end if;
2488
2489 end if;
2490 end if;
2491
2492 if upper(p_deployment_status) = 'ACTIVE'
2493 then
2494
2495 select count(hat.transaction_id)
2496 into lv_transaction_count
2497 from hr_api_transactions hat,
2498 hr_api_transaction_steps hats
2499 where hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2500 and hats.transaction_id = hat.transaction_id
2501 and hats.information1 = to_char(p_deployment_id)
2502 and not(hr_person_deployment_swi.getDeploymentType(hat.transaction_id,NULL) IS NULL and hat.status = 'W')
2503 and hat.status in ('AC')
2504 and not exists ( select 'e'
2505 from hr_api_transactions t,
2506 hr_api_transaction_steps hat
2507 WHERE t.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2508 and hat.transaction_id = t.transaction_id
2509 and hat.information1 = to_char(p_deployment_id)
2510 and not(hr_person_deployment_swi.getDeploymentType(t.transaction_id,NULL) IS NULL and t.status = 'W')
2511 and t.status not in ('D','E','AC'));
2512
2513
2514 if lv_transaction_count = 0
2515 then
2516 lv_status := p_deployment_status;
2517 else
2518
2519 begin
2520 select ACTION
2521 into lv_action
2522 from PQH_SS_APPROVAL_HISTORY
2523 where transaction_history_id in (select hat.transaction_id
2524 from hr_api_transactions hat, hr_api_transaction_steps hats
2525 where hat.transaction_id = hats.transaction_id
2526 and hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2527 and hats.information1 = to_char(p_deployment_id)
2528 and upper(hats.information12) in ('RETURNMODE','ACTUPDATEMODE')
2529 and not(hr_person_deployment_swi.getDeploymentType(hat.transaction_id,NULL) IS NULL and hat.status = 'W') )
2530 and creation_date = (select max(CREATION_DATE)
2531 from PQH_SS_APPROVAL_HISTORY
2532 where transaction_history_id in (select hat.transaction_id
2533 from hr_api_transactions hat, hr_api_transaction_steps hats
2534 where hat.transaction_id = hats.transaction_id
2535 and hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2536 and hats.information1 = to_char(p_deployment_id)
2537 and upper(hats.information12) in ('RETURNMODE','ACTUPDATEMODE')
2538 and not(hr_person_deployment_swi.getDeploymentType(hat.transaction_id,NULL) IS NULL and hat.status = 'W') )
2539 );
2540
2541 exception
2542 when others then lv_action := null;
2543 end;
2544
2545 if lv_action = 'REJECTED'
2546 then
2547 lv_status := 'REJECTED';
2548 else
2549 lv_status := p_deployment_status;
2550 end if;
2551
2552
2553 end if;
2554
2555
2556 end if;
2557
2558
2559
2560 if upper(p_deployment_status) = 'COMPLETE'
2561 then
2562
2563 select count(hat.transaction_id)
2564 into lv_transaction_count
2565 from hr_api_transactions hat,
2566 hr_api_transaction_steps hats
2567 where hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2568 and hats.transaction_id = hat.transaction_id
2569 and hats.information1 = to_char(p_deployment_id)
2570 and not(hr_person_deployment_swi.getDeploymentType(hat.transaction_id,NULL) IS NULL and hat.status = 'W')
2571 and hat.status in ('AC')
2572 and not exists ( select 'e'
2573 from hr_api_transactions t,
2574 hr_api_transaction_steps hat
2575 WHERE t.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2576 and hat.transaction_id = t.transaction_id
2577 and hat.information1 = to_char(p_deployment_id)
2578 and not(hr_person_deployment_swi.getDeploymentType(t.transaction_id,NULL) IS NULL and t.status = 'W')
2579 and t.status not in ('D','E','AC'));
2580
2581
2582 if lv_transaction_count = 0
2583 then
2584 lv_status := p_deployment_status;
2585 else
2586
2587 begin
2588 select ACTION
2589 into lv_action
2590 from PQH_SS_APPROVAL_HISTORY
2591 where transaction_history_id in (select hat.transaction_id
2592 from hr_api_transactions hat, hr_api_transaction_steps hats
2593 where hat.transaction_id = hats.transaction_id
2594 and hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2595 and hats.information1 = to_char(p_deployment_id)
2596 and upper(hats.information12) = 'RETURNMODE'
2597 and not(hr_person_deployment_swi.getDeploymentType(hat.transaction_id,NULL) IS NULL and hat.status = 'W') )
2598 and creation_date = (select max(CREATION_DATE)
2599 from PQH_SS_APPROVAL_HISTORY
2600 where transaction_history_id in (select hat.transaction_id
2601 from hr_api_transactions hat, hr_api_transaction_steps hats
2602 where hat.transaction_id = hats.transaction_id
2603 and hat.transaction_ref_table='HR_PERSON_DEPLOYMENT'
2604 and hats.information1 = to_char(p_deployment_id)
2605 and upper(hats.information12) = 'RETURNMODE'
2606 and not(hr_person_deployment_swi.getDeploymentType(hat.transaction_id,NULL) IS NULL and hat.status = 'W') )
2607 );
2608
2609 exception
2610 when others then lv_action := null;
2611 end;
2612
2613 if lv_action = 'APPROVED'
2614 then
2615 lv_status := 'RETURN_COMPLETE';
2616 else
2617 lv_status := p_deployment_status;
2618 end if;
2619
2620
2621 end if;
2622
2623
2624 end if;
2625
2626 return lv_status;
2627
2628 if g_debug then
2629 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2630 end if;
2631
2632 exception
2633 when others then
2634 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2635 Wf_Core.Context(g_package, c_proc, p_deployment_id);
2636 raise;
2637 end getDeploymentStatus;
2638
2639 /**************************************************************************************/
2640 /*FUNCTION TO GET STATUS OF DEPLOYMENT AFTER APPROVAL AND REJECTION */
2641 /***************************************************************************************/
2642
2643 function getDeploymentStatus(p_transaction_id in number,
2644 p_transaction_step_id in number,
2645 p_transaction_status in varchar2) return varchar2
2646
2647 IS
2648 c_proc constant varchar2(30) := 'getDeploymentStatus2';
2649 lv_status Varchar2(40) := p_transaction_status;
2650 lv_action varchar2(20) := NULL;
2651
2652 begin
2653 -- hr_utility.trace_on (null,'ORAC');
2654 g_debug := hr_utility.debug_enabled;
2655
2656 if g_debug then
2657 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2658 end if;
2659
2660 if upper(p_transaction_status) = 'AC'
2661 then
2662
2663
2664 begin
2665 select ACTION
2666 into lv_action
2667 from PQH_SS_APPROVAL_HISTORY
2668 where transaction_history_id = p_transaction_id
2669 and creation_date = (select max(CREATION_DATE)
2670 from PQH_SS_APPROVAL_HISTORY
2671 where transaction_history_id = p_transaction_id)
2672 -- and upper('ACTUPDATEMODE') NOT IN (SELECT UPPER(information12) from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id)
2673 -- and upper('RETURNMODE') NOT IN (SELECT UPPER(information12) from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id)
2674 -- and upper('UPDATEMODE') NOT IN (SELECT UPPER(information12) from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id)
2675 and (SELECT UPPER(information12) from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id) not in ('ACTUPDATEMODE','RETURNMODE','UPDATEMODE');
2676
2677 exception
2678 when others then lv_action := null;
2679 end;
2680
2681 if lv_action = 'REJECTED'
2682 then
2683 lv_status := 'REJECTED';
2684 else
2685 lv_status := p_transaction_status;
2686 end if;
2687
2688
2689 end if;
2690
2691
2692 return lv_status;
2693
2694 if g_debug then
2695 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2696 end if;
2697
2698 exception
2699 when others then
2700 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2701 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2702 raise;
2703 end getDeploymentStatus;
2704
2705 procedure getGoldApprovalMsgSubject(document_id IN Varchar2,
2706 display_type IN Varchar2,
2707 document IN OUT NOCOPY varchar2,
2708 document_type IN OUT NOCOPY Varchar2)
2709 is
2710 c_proc varchar2(30) default 'getApprovalMsgSubject';
2711 lv_item_type wf_item_activity_statuses.item_type%type;
2712 lv_item_key wf_item_activity_statuses.item_key%type;
2713 l_creator_person_id per_people_f.person_id%type;
2714 l_creator_disp_name wf_users.display_name%type;
2715 l_creator_username wf_users.name%type;
2716 l_current_person_id per_people_f.person_id%type;
2717 l_current_disp_name wf_users.display_name%type;
2718 l_current_username wf_users.name%type;
2719 lv_process_display_name wf_runnable_processes_v.display_name%type;
2720 lv_ntf_sub_msg wf_item_attribute_values.text_value%type;
2721 lv_custom_callBack varchar2(60);
2722 l_sqlbuf Varchar2(1000);
2723
2724 begin
2725 g_debug := hr_utility.debug_enabled;
2726 if g_debug then
2727 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
2728 end if;
2729
2730 -- check if we have custom callback for the Approval Ntf subject
2731 --
2732 /* begin
2733 lv_custom_callBack := hr_xml_util.get_node_value(
2734 p_transaction_id => getattrnumber(document_id,'HR_TRANSACTION_REF_ID_ATTR','TRUE')
2735 ,p_desired_node_value => 'ApprNtfSubCallBack'
2736 ,p_xpath => 'Transaction/TransCtx');
2737 If lv_custom_callBack is not null Then
2738 l_sqlbuf:= 'BEGIN ' || lv_custom_callBack
2739 || ' (document_id => :1 '
2740 || ' ,display_type => :2 '
2741 || ' ,document => :3 '
2742 || ' ,document_type => :4 ); END; ';
2743 EXECUTE IMMEDIATE l_sqlbuf using in document_id,
2744 in display_type,
2745 in out document,
2746 in out document_type;
2747 return;
2748 End If;
2749 exception
2750 when others then
2751 document :=null;
2752 hr_utility.set_location('hr_workflow_ss.getApprovalMsgSubject errored for custom call: '||SQLERRM ||' '||to_char(SQLCODE), 100);
2753 Wf_Core.Context('hr_workflow_ss', 'getApprovalMsgSubject', document_id, display_type);
2754 raise;
2755 end;
2756 */
2757
2758
2759 -- get the itemtype and item key for the notification id
2760 if g_debug then
2761 hr_utility.set_location('Calling hr_workflow_ss.get_item_type_and_key for NtfId:'||document_id, 11);
2762 end if;
2763 hr_workflow_ss.get_item_type_and_key(document_id,lv_item_type,lv_item_key);
2764
2765 -- get the process display name
2766 if g_debug then
2767 hr_utility.set_location('Calling getProcessDisplayName',12);
2768 hr_utility.set_location('ItemType:'||lv_item_type,13);
2769 hr_utility.set_location('ItemKey:'||lv_item_key,14);
2770 end if;
2771 -- lv_process_display_name := hr_workflow_ss.getProcessDisplayName(lv_item_type,lv_item_key);
2772
2773 lv_ntf_sub_msg := wf_engine.GetItemAttrText(itemtype => lv_item_type ,
2774 itemkey => lv_item_key,
2775 aname => 'HR_NTF_SUB_FND_MSG_ATTR',
2776 ignore_notfound=>true);
2777
2778 if(lv_ntf_sub_msg is null) then
2779 lv_process_display_name := hr_workflow_ss.getProcessDisplayName(lv_item_type,lv_item_key);
2780 else
2781 fnd_message.set_name('PER',lv_ntf_sub_msg);
2782 lv_process_display_name:= fnd_message.get;
2783 end if;
2784
2785
2786 l_creator_person_id:= wf_engine.GetItemAttrNumber
2787 (itemtype => lv_item_type
2788 ,itemkey => lv_item_key
2789 ,aname => 'CREATOR_PERSON_ID');
2790
2791
2792 l_current_person_id:= wf_engine.GetItemAttrNumber
2793 (itemtype => lv_item_type
2794 ,itemkey => lv_item_key
2795 ,aname => 'CURRENT_PERSON_ID');
2796 if g_debug then
2797 hr_utility.set_location('Creator_person_id:'||l_creator_person_id,15);
2798 hr_utility.set_location('Current_person_id:'||l_current_person_id,16);
2799 end if;
2800 if g_debug then
2801 hr_utility.set_location('Building subject for NtfId:'||document_id,17);
2802 end if;
2803 if(l_creator_person_id=l_current_person_id) then
2804 if g_debug then
2805 hr_utility.set_location('calling wf_directory.GetUserName for person_id:'||l_creator_person_id,18);
2806 end if;
2807
2808 -- get creator display name from role
2809 wf_directory.GetUserName
2810 (p_orig_system => 'PER'
2811 ,p_orig_system_id => l_creator_person_id
2812 ,p_name => l_creator_username
2813 ,p_display_name => l_creator_disp_name);
2814
2815 if hr_workflow_ss.getOrganizationManagersubject(lv_item_type,lv_item_key) is not null then
2816 l_creator_disp_name := hr_workflow_ss.getOrganizationManagersubject(lv_item_type,lv_item_key) || ' (proposed by ' || l_creator_disp_name || ')';
2817 end if;
2818 -- Subject pattern
2819 -- "Change Job for Doe, John "
2820 if g_debug then
2821 hr_utility.set_location('Getting message HR_SS_APPROVER_MSG_SUB_SELF',19);
2822 end if;
2823 fnd_message.set_name('PER','HR_SS_APPROVER_MSG_SUB_SELF');
2824 fnd_message.set_token('PROCESS_DISPLAY_NAME',lv_process_display_name,false);
2825 fnd_message.set_token('CURRENT_PERSON_DISPLAY_NAME',l_creator_disp_name,false);
2826 document := fnd_message.get;
2827
2828 else
2829 -- get creator display name from role
2830 if g_debug then
2831 hr_utility.set_location('calling wf_directory.GetUserName for person_id:'||l_creator_person_id,20);
2832 end if;
2833 wf_directory.GetUserName
2834 (p_orig_system => 'PER'
2835 ,p_orig_system_id => l_creator_person_id
2836 ,p_name => l_creator_username
2837 ,p_display_name => l_creator_disp_name);
2838
2839 -- get current person display name from role
2840 if g_debug then
2841 hr_utility.set_location('calling wf_directory.GetUserName for person_id:'||l_current_person_id,21);
2842 end if;
2843 wf_directory.GetUserName
2844 (p_orig_system => 'PER'
2845 ,p_orig_system_id => l_current_person_id
2846 ,p_name => l_current_username
2847 ,p_display_name => l_current_disp_name);
2848
2849 -- check if the username/wfrole is null or display name is null
2850 if(l_current_username is null OR l_current_disp_name is null) then
2851 -- To support name format, should not rely on the stored person name.
2852 -- Should rely on the person_id to get the name in correct format
2853
2854 begin
2855 if l_current_person_id is not null then
2856 l_current_disp_name := hr_person_name.get_person_name(l_current_person_id,sysdate );
2857 end if;
2858 exception
2859 when others then
2860 l_current_disp_name := null;
2861 end;
2862
2863 if(l_current_disp_name is null ) then
2864 -- cud still be null if person doesnot exist in per_all_people_f as of now.
2865 -- resort to the existing code of fetching from wf item attribute.
2866 l_current_disp_name := wf_engine.GetItemAttrText
2867 (itemtype => lv_item_type
2868 ,itemkey => lv_item_key
2869 ,aname => 'CURRENT_PERSON_DISPLAY_NAME');
2870 end if;
2871
2872 end if;
2873
2874 -- Subject pattern
2875 -- "Change Job for Doe, John (proposed by Bond, James)"
2876 if g_debug then
2877 hr_utility.set_location('Getting message HR_SS_APPROVER_MSG_SUB_REPORTS',22);
2878 end if;
2879
2880 fnd_message.set_name('PER','HR_SS_APPROVER_MSG_SUB_REPORTS');
2881 fnd_message.set_token('PROCESS_DISPLAY_NAME',lv_process_display_name,false);
2882 fnd_message.set_token('CURRENT_PERSON_DISPLAY_NAME',l_current_disp_name||' requires your approval',false);
2883 fnd_message.set_token('CREATOR_PERSON_DISPLAY_NAME',l_creator_disp_name,false);
2884 document := fnd_message.get;
2885 end if;
2886
2887
2888 -- set the document type
2889 document_type := wf_notification.doc_html;
2890
2891 if g_debug then
2892 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2893 end if;
2894
2895 exception
2896 when others then
2897 document :=null;
2898 hr_utility.set_location('hr_workflow_ss.getApprovalMsgSubject errored : '||SQLERRM ||' '||to_char(SQLCODE), 40);
2899 Wf_Core.Context('hr_workflow_ss', 'getApprovalMsgSubject', document_id, display_type);
2900 raise;
2901 end getGoldApprovalMsgSubject;
2902
2903 procedure set_start_date(p_transaction_id in number,p_effective_date in date)
2904 is
2905
2906 l_item_key varchar2(240);
2907 l_item_type varchar2(240);
2908 l_proc varchar2(72) := g_package || 'set_start_date';
2909 begin
2910
2911 hr_utility.set_location('Entering: '||l_proc,10);
2912
2913 begin
2914 select item_type,item_key
2915 into l_item_type,l_item_key
2916 from hr_api_transactions
2917 where transaction_id = p_transaction_id;
2918
2919 hr_utility.set_location(l_proc||'l_item_key='||l_item_key,41);
2920 Exception
2921 when others then l_item_key := null;
2922 l_item_type := null;
2923 End;
2924 hr_utility.set_location('Entering: '||l_proc,42);
2925 if ((l_item_key is not null) and (l_item_type is not null) and (p_effective_date is not null))
2926 then
2927 Wf_Engine.SetItemAttrDate(itemtype => l_item_type
2928 ,itemkey => l_item_key
2929 ,aname => 'CURRENT_EFFECTIVE_DATE'
2930 ,avalue => TO_DATE(TO_CHAR(p_effective_date,'RRRR-MM-DD'),'RRRR-MM-DD'));
2931
2932 hr_utility.set_location('Entering: '||l_proc,43);
2933 end if;
2934 exception
2935 when others then
2936 hr_utility.set_location(l_proc||sqlerrm,50);
2937 null;
2938 end;
2939
2940 end hr_person_deployment_swi;