[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_DEPLOYMENT_API
Source
1 Package Body HR_PERSON_DEPLOYMENT_API as
2 /* $Header: hrpdtapi.pkb 120.25.12020000.3 2013/03/14 12:53:59 pathota ship $ */
3 --
4 -- Type declarations
5 --
6 TYPE t_contact_created IS RECORD
7 (home_contact_person_id number, host_contact_person_id number);
8 TYPE t_contacts_created is table of t_contact_created;
9 --
10 -- Package Variables
11 --
12 g_package varchar2(33) := ' HR_PERSON_DEPLOYMENT_API.';
13 --
14 -- ----------------------------------------------------------------------------
15 -- |--------------------------< CREATE_PERSON_DEPLOYMENT >--------------------|
16 -- ----------------------------------------------------------------------------
17 --
18 procedure create_person_deployment
19 (p_validate in boolean default false
20 ,p_from_business_group_id in number
21 ,p_to_business_group_id in number
22 ,p_from_person_id in number
23 ,p_to_person_id in number default null
24 ,p_person_type_id in number default null
25 ,p_start_date in date
26 ,p_end_date in date default null
27 ,p_deployment_reason in varchar2 default null
28 ,p_employee_number in varchar2 default null
29 ,p_leaving_reason in varchar2 default null
30 ,p_leaving_person_type_id in number default null
31 ,p_permanent in varchar2 default null
32 ,p_deplymt_policy_id in number default null
33 ,p_organization_id in number
34 ,p_location_id in number default null
35 ,p_job_id in number default null
36 ,p_position_id in number default null
37 ,p_grade_id in number default null
38 ,p_supervisor_id in number default null
39 ,p_supervisor_assignment_id in number default null
40 ,p_retain_direct_reports in varchar2 default null
41 ,p_payroll_id in number default null
42 ,p_pay_basis_id in number default null
43 ,p_proposed_salary in varchar2 default null
44 ,p_people_group_id in number default null
45 ,p_soft_coding_keyflex_id in number default null
46 ,p_assignment_status_type_id in number default null
47 ,p_ass_status_change_reason in varchar2 default null
48 ,p_assignment_category in varchar2 default null
49 ,p_per_information1 in varchar2 default null
50 ,p_per_information2 in varchar2 default null
51 ,p_per_information3 in varchar2 default null
52 ,p_per_information4 in varchar2 default null
53 ,p_per_information5 in varchar2 default null
54 ,p_per_information6 in varchar2 default null
55 ,p_per_information7 in varchar2 default null
56 ,p_per_information8 in varchar2 default null
57 ,p_per_information9 in varchar2 default null
58 ,p_per_information10 in varchar2 default null
59 ,p_per_information11 in varchar2 default null
60 ,p_per_information12 in varchar2 default null
61 ,p_per_information13 in varchar2 default null
62 ,p_per_information14 in varchar2 default null
63 ,p_per_information15 in varchar2 default null
64 ,p_per_information16 in varchar2 default null
65 ,p_per_information17 in varchar2 default null
66 ,p_per_information18 in varchar2 default null
67 ,p_per_information19 in varchar2 default null
68 ,p_per_information20 in varchar2 default null
69 ,p_per_information21 in varchar2 default null
70 ,p_per_information22 in varchar2 default null
71 ,p_per_information23 in varchar2 default null
72 ,p_per_information24 in varchar2 default null
73 ,p_per_information25 in varchar2 default null
74 ,p_per_information26 in varchar2 default null
75 ,p_per_information27 in varchar2 default null
76 ,p_per_information28 in varchar2 default null
77 ,p_per_information29 in varchar2 default null
78 ,p_per_information30 in varchar2 default null
79 ,p_person_deployment_id out nocopy number
80 ,p_object_version_number out nocopy number
81 ,p_policy_duration_warning out nocopy boolean
82 ) is
83 --
84 -- Declare cursors and local variables
85 --
86 l_person_deployment_id number;
87 l_object_version_number number;
88 l_policy_duration_warning boolean := false;
89 l_start_date date;
90 l_end_date date;
91 l_status_change_date date;
92 l_per_information_category varchar2(30);
93 l_proc varchar2(72) := g_package||'create_person_deployment';
94 --
95 cursor csr_derive_legislation(p_business_group_id number) is
96 select pbg.legislation_code
97 from per_business_groups pbg
98 where pbg.business_group_id = p_business_group_id;
99 begin
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Issue a savepoint
103 --
104 savepoint create_person_deployment;
105 --
106 -- Remember IN OUT parameter IN values
107 --
108
109 --
110 -- Truncate the time portion from all IN date parameters
111 --
112 l_start_date := trunc(p_start_date);
113 l_end_date := trunc(p_end_date);
114 --
115 -- Call Before Process User Hook
116 --
117 begin
118 HR_PERSON_DEPLOYMENT_BK1.CREATE_PERSON_DEPLOYMENT_B
119 (p_from_business_group_id => p_from_business_group_id
120 ,p_to_business_group_id => p_to_business_group_id
121 ,p_from_person_id => p_from_person_id
122 ,p_to_person_id => p_to_person_id
123 ,p_person_type_id => p_person_type_id
124 ,p_start_date => l_start_date
125 ,p_end_date => l_end_date
126 ,p_deployment_reason => p_deployment_reason
127 ,p_employee_number => p_employee_number
128 ,p_leaving_reason => p_leaving_reason
129 ,p_leaving_person_type_id => p_leaving_person_type_id
130 ,p_permanent => p_permanent
131 ,p_deplymt_policy_id => p_deplymt_policy_id
132 ,p_organization_id => p_organization_id
133 ,p_location_id => p_location_id
134 ,p_job_id => p_job_id
135 ,p_position_id => p_position_id
136 ,p_grade_id => p_grade_id
137 ,p_supervisor_id => p_supervisor_id
138 ,p_supervisor_assignment_id => p_supervisor_assignment_id
139 ,p_retain_direct_reports => p_retain_direct_reports
140 ,p_payroll_id => p_payroll_id
141 ,p_pay_basis_id => p_pay_basis_id
142 ,p_proposed_salary => p_proposed_salary
143 ,p_people_group_id => p_people_group_id
144 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
145 ,p_assignment_status_type_id => p_assignment_status_type_id
146 ,p_ass_status_change_reason => p_ass_status_change_reason
147 ,p_assignment_category => p_assignment_category
148 ,p_per_information1 => p_per_information1
149 ,p_per_information2 => p_per_information2
150 ,p_per_information3 => p_per_information3
151 ,p_per_information4 => p_per_information4
152 ,p_per_information5 => p_per_information5
153 ,p_per_information6 => p_per_information6
154 ,p_per_information7 => p_per_information7
155 ,p_per_information8 => p_per_information8
156 ,p_per_information9 => p_per_information9
157 ,p_per_information10 => p_per_information10
158 ,p_per_information11 => p_per_information11
159 ,p_per_information12 => p_per_information12
160 ,p_per_information13 => p_per_information13
161 ,p_per_information14 => p_per_information14
162 ,p_per_information15 => p_per_information15
163 ,p_per_information16 => p_per_information16
164 ,p_per_information17 => p_per_information17
165 ,p_per_information18 => p_per_information18
166 ,p_per_information19 => p_per_information19
167 ,p_per_information20 => p_per_information20
168 ,p_per_information21 => p_per_information21
169 ,p_per_information22 => p_per_information22
170 ,p_per_information23 => p_per_information23
171 ,p_per_information24 => p_per_information24
172 ,p_per_information25 => p_per_information25
173 ,p_per_information26 => p_per_information26
174 ,p_per_information27 => p_per_information27
175 ,p_per_information28 => p_per_information28
176 ,p_per_information29 => p_per_information29
177 ,p_per_information30 => p_per_information30
178 );
179 exception
180 when hr_api.cannot_find_prog_unit then
181 hr_api.cannot_find_prog_unit_error
182 (p_module_name => 'CREATE_PERSON_DEPLOYMENT'
183 ,p_hook_type => 'BP'
184 );
185 end;
186 --
187 -- Validation in addition to Row Handlers
188 --
189 -- derive context for Person Developer DF
190 open csr_derive_legislation(p_to_business_group_id);
191 fetch csr_derive_legislation into l_per_information_category;
192 close csr_derive_legislation;
193 --
194 -- Process Logic
195 --
196 hr_pdt_ins.ins
197 (p_from_business_group_id => p_from_business_group_id
198 ,p_to_business_group_id => p_to_business_group_id
199 ,p_from_person_id => p_from_person_id
200 ,p_person_type_id => p_person_type_id
201 ,p_start_date => l_start_date
202 ,p_status => 'DRAFT' --always DRAFT on create
203 ,p_to_person_id => p_to_person_id
204 ,p_end_date => l_end_date
205 ,p_deployment_reason => p_deployment_reason
206 ,p_employee_number => p_employee_number
207 ,p_leaving_reason => p_leaving_reason
208 ,p_leaving_person_type_id => p_leaving_person_type_id
209 ,p_permanent => p_permanent
210 ,p_status_change_reason => null
211 ,p_deplymt_policy_id => p_deplymt_policy_id
212 ,p_organization_id => p_organization_id
213 ,p_location_id => p_location_id
214 ,p_job_id => p_job_id
215 ,p_position_id => p_position_id
216 ,p_grade_id => p_grade_id
217 ,p_supervisor_id => p_supervisor_id
218 ,p_supervisor_assignment_id => p_supervisor_assignment_id
219 ,p_retain_direct_reports => p_retain_direct_reports
220 ,p_payroll_id => p_payroll_id
221 ,p_pay_basis_id => p_pay_basis_id
222 ,p_proposed_salary => p_proposed_salary
223 ,p_people_group_id => p_people_group_id
224 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
225 ,p_assignment_status_type_id => p_assignment_status_type_id
226 ,p_ass_status_change_reason => p_ass_status_change_reason
227 ,p_assignment_category => p_assignment_category
228 ,p_per_information_category => l_per_information_category
229 ,p_per_information1 => p_per_information1
230 ,p_per_information2 => p_per_information2
231 ,p_per_information3 => p_per_information3
232 ,p_per_information4 => p_per_information4
233 ,p_per_information5 => p_per_information5
234 ,p_per_information6 => p_per_information6
235 ,p_per_information7 => p_per_information7
236 ,p_per_information8 => p_per_information8
237 ,p_per_information9 => p_per_information9
238 ,p_per_information10 => p_per_information10
239 ,p_per_information11 => p_per_information11
240 ,p_per_information12 => p_per_information12
241 ,p_per_information13 => p_per_information13
242 ,p_per_information14 => p_per_information14
243 ,p_per_information15 => p_per_information15
244 ,p_per_information16 => p_per_information16
245 ,p_per_information17 => p_per_information17
246 ,p_per_information18 => p_per_information18
247 ,p_per_information19 => p_per_information19
248 ,p_per_information20 => p_per_information20
249 ,p_per_information21 => p_per_information21
250 ,p_per_information22 => p_per_information22
251 ,p_per_information23 => p_per_information23
252 ,p_per_information24 => p_per_information24
253 ,p_per_information25 => p_per_information25
254 ,p_per_information26 => p_per_information26
255 ,p_per_information27 => p_per_information27
256 ,p_per_information28 => p_per_information28
257 ,p_per_information29 => p_per_information29
258 ,p_per_information30 => p_per_information30
259 ,p_person_deployment_id => l_person_deployment_id
260 ,p_object_version_number => l_object_version_number
261 );
262
263 --
264 -- Call After Process User Hook
265 --
266 begin
267 HR_PERSON_DEPLOYMENT_BK1.CREATE_PERSON_DEPLOYMENT_A
268 (p_from_business_group_id => p_from_business_group_id
269 ,p_to_business_group_id => p_to_business_group_id
270 ,p_from_person_id => p_from_person_id
271 ,p_to_person_id => p_to_person_id
272 ,p_person_type_id => p_person_type_id
273 ,p_start_date => l_start_date
274 ,p_end_date => l_end_date
275 ,p_deployment_reason => p_deployment_reason
276 ,p_employee_number => p_employee_number
277 ,p_leaving_reason => p_leaving_reason
278 ,p_leaving_person_type_id => p_leaving_person_type_id
279 ,p_permanent => p_permanent
280 ,p_deplymt_policy_id => p_deplymt_policy_id
281 ,p_organization_id => p_organization_id
282 ,p_location_id => p_location_id
283 ,p_job_id => p_job_id
284 ,p_position_id => p_position_id
285 ,p_grade_id => p_grade_id
286 ,p_supervisor_id => p_supervisor_id
287 ,p_supervisor_assignment_id => p_supervisor_assignment_id
288 ,p_retain_direct_reports => p_retain_direct_reports
289 ,p_payroll_id => p_payroll_id
290 ,p_pay_basis_id => p_pay_basis_id
291 ,p_proposed_salary => p_proposed_salary
292 ,p_people_group_id => p_people_group_id
293 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
294 ,p_assignment_status_type_id => p_assignment_status_type_id
295 ,p_ass_status_change_reason => p_ass_status_change_reason
296 ,p_assignment_category => p_assignment_category
297 ,p_per_information1 => p_per_information1
298 ,p_per_information2 => p_per_information2
299 ,p_per_information3 => p_per_information3
300 ,p_per_information4 => p_per_information4
301 ,p_per_information5 => p_per_information5
302 ,p_per_information6 => p_per_information6
303 ,p_per_information7 => p_per_information7
304 ,p_per_information8 => p_per_information8
305 ,p_per_information9 => p_per_information9
306 ,p_per_information10 => p_per_information10
307 ,p_per_information11 => p_per_information11
308 ,p_per_information12 => p_per_information12
309 ,p_per_information13 => p_per_information13
310 ,p_per_information14 => p_per_information14
311 ,p_per_information15 => p_per_information15
312 ,p_per_information16 => p_per_information16
313 ,p_per_information17 => p_per_information17
314 ,p_per_information18 => p_per_information18
315 ,p_per_information19 => p_per_information19
316 ,p_per_information20 => p_per_information20
317 ,p_per_information21 => p_per_information21
318 ,p_per_information22 => p_per_information22
319 ,p_per_information23 => p_per_information23
320 ,p_per_information24 => p_per_information24
321 ,p_per_information25 => p_per_information25
322 ,p_per_information26 => p_per_information26
323 ,p_per_information27 => p_per_information27
324 ,p_per_information28 => p_per_information28
325 ,p_per_information29 => p_per_information29
326 ,p_per_information30 => p_per_information30
327 ,p_person_deployment_id => l_person_deployment_id
328 ,p_object_version_number => l_object_version_number
329 ,p_policy_duration_warning => l_policy_duration_warning
330 );
331 exception
332 when hr_api.cannot_find_prog_unit then
333 hr_api.cannot_find_prog_unit_error
334 (p_module_name => 'CREATE_PERSON_DEPLOYMENT'
335 ,p_hook_type => 'AP'
336 );
337 end;
338 --
339 -- When in validation only mode raise the Validate_Enabled exception
340 --
341 if p_validate then
342 raise hr_api.validate_enabled;
343 end if;
344 --
345 -- Set all IN OUT and OUT parameters with out values
346 --
347 p_person_deployment_id := l_person_deployment_id;
348 p_object_version_number := l_object_version_number;
349 p_policy_duration_warning := l_policy_duration_warning;
350 --
351 hr_utility.set_location(' Leaving:'||l_proc, 70);
352 exception
353 when hr_api.validate_enabled then
354 --
355 -- As the Validate_Enabled exception has been raised
356 -- we must rollback to the savepoint
357 --
358 rollback to create_person_deployment;
359 --
360 -- Reset IN OUT parameters and set OUT parameters
361 -- (Any key or derived arguments must be set to null
362 -- when validation only mode is being used.)
363 --
364 p_person_deployment_id := null;
365 p_object_version_number := null;
366 p_policy_duration_warning := null;
367 hr_utility.set_location(' Leaving:'||l_proc, 80);
368 when others then
369 --
370 -- A validation or unexpected error has occured
371 --
372 rollback to create_person_deployment;
373 --
374 -- Reset IN OUT parameters and set all
375 -- OUT parameters, including warnings, to null
376 --
377 p_person_deployment_id := null;
378 p_object_version_number := null;
379 p_policy_duration_warning := null;
380 hr_utility.set_location(' Leaving:'||l_proc, 90);
381 raise;
382 end create_person_deployment;
383 --
384 --
385 -- ----------------------------------------------------------------------------
386 -- |--------------------------< UPDATE_PERSON_DEPLOYMENT >--------------------|
387 -- ----------------------------------------------------------------------------
388 --
389 procedure update_person_deployment
390 (p_validate in boolean default false
391 ,p_person_deployment_id in number
392 ,p_object_version_number in out nocopy number
393 ,p_to_person_id in number default hr_api.g_number
394 ,p_person_type_id in number default hr_api.g_number
395 ,p_start_date in date default hr_api.g_date
396 ,p_end_date in date default hr_api.g_date
397 ,p_deployment_reason in varchar2 default hr_api.g_varchar2
398 ,p_employee_number in varchar2 default hr_api.g_varchar2
399 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
400 ,p_leaving_person_type_id in number default hr_api.g_number
401 ,p_status in varchar2 default hr_api.g_varchar2
402 ,p_status_change_reason in varchar2 default hr_api.g_varchar2
403 ,p_deplymt_policy_id in number default hr_api.g_number
404 ,p_organization_id in number default hr_api.g_number
405 ,p_location_id in number default hr_api.g_number
406 ,p_job_id in number default hr_api.g_number
407 ,p_position_id in number default hr_api.g_number
408 ,p_grade_id in number default hr_api.g_number
409 ,p_supervisor_id in number default hr_api.g_number
410 ,p_supervisor_assignment_id in number default hr_api.g_number
411 ,p_retain_direct_reports in varchar2 default hr_api.g_varchar2
412 ,p_payroll_id in number default hr_api.g_number
413 ,p_pay_basis_id in number default hr_api.g_number
414 ,p_proposed_salary in varchar2 default hr_api.g_varchar2
415 ,p_people_group_id in number default hr_api.g_number
416 ,p_soft_coding_keyflex_id in number default hr_api.g_number
417 ,p_assignment_status_type_id in number default hr_api.g_number
418 ,p_ass_status_change_reason in varchar2 default hr_api.g_varchar2
419 ,p_assignment_category in varchar2 default hr_api.g_varchar2
420 ,p_per_information1 in varchar2 default hr_api.g_varchar2
421 ,p_per_information2 in varchar2 default hr_api.g_varchar2
422 ,p_per_information3 in varchar2 default hr_api.g_varchar2
423 ,p_per_information4 in varchar2 default hr_api.g_varchar2
424 ,p_per_information5 in varchar2 default hr_api.g_varchar2
425 ,p_per_information6 in varchar2 default hr_api.g_varchar2
426 ,p_per_information7 in varchar2 default hr_api.g_varchar2
427 ,p_per_information8 in varchar2 default hr_api.g_varchar2
428 ,p_per_information9 in varchar2 default hr_api.g_varchar2
429 ,p_per_information10 in varchar2 default hr_api.g_varchar2
430 ,p_per_information11 in varchar2 default hr_api.g_varchar2
431 ,p_per_information12 in varchar2 default hr_api.g_varchar2
432 ,p_per_information13 in varchar2 default hr_api.g_varchar2
433 ,p_per_information14 in varchar2 default hr_api.g_varchar2
434 ,p_per_information15 in varchar2 default hr_api.g_varchar2
435 ,p_per_information16 in varchar2 default hr_api.g_varchar2
436 ,p_per_information17 in varchar2 default hr_api.g_varchar2
437 ,p_per_information18 in varchar2 default hr_api.g_varchar2
438 ,p_per_information19 in varchar2 default hr_api.g_varchar2
439 ,p_per_information20 in varchar2 default hr_api.g_varchar2
440 ,p_per_information21 in varchar2 default hr_api.g_varchar2
441 ,p_per_information22 in varchar2 default hr_api.g_varchar2
442 ,p_per_information23 in varchar2 default hr_api.g_varchar2
443 ,p_per_information24 in varchar2 default hr_api.g_varchar2
444 ,p_per_information25 in varchar2 default hr_api.g_varchar2
445 ,p_per_information26 in varchar2 default hr_api.g_varchar2
446 ,p_per_information27 in varchar2 default hr_api.g_varchar2
447 ,p_per_information28 in varchar2 default hr_api.g_varchar2
448 ,p_per_information29 in varchar2 default hr_api.g_varchar2
449 ,p_per_information30 in varchar2 default hr_api.g_varchar2
450 ,p_policy_duration_warning out nocopy boolean
451 ) is
452 --
453 -- Declare cursors and local variables
454 --
455 l_object_version_number number;
456 l_policy_duration_warning boolean := false;
457 l_start_date date;
458 l_end_date date;
459 l_status_change_date date;
460 l_proc varchar2(72) := g_package||'update_person_deployment';
461 begin
462 hr_utility.set_location('Entering:'|| l_proc, 10);
463 --
464 -- Issue a savepoint
465 --
466 savepoint update_person_deployment;
467 --
468 -- Remember IN OUT parameter IN values
469 --
470 l_object_version_number := p_object_version_number;
471
472 --
473 -- Truncate the time portion from all IN date parameters
474 --
475 l_start_date := trunc(p_start_date);
476 l_end_date := trunc(p_end_date);
477
478 --
479 -- Call Before Process User Hook
480 --
481 begin
482 HR_PERSON_DEPLOYMENT_BK2.UPDATE_PERSON_DEPLOYMENT_B
483 (p_person_deployment_id => p_person_deployment_id
484 ,p_object_version_number => p_object_version_number
485 ,p_to_person_id => p_to_person_id
486 ,p_person_type_id => p_person_type_id
487 ,p_start_date => l_start_date
488 ,p_status => p_status
489 ,p_status_change_reason => p_status_change_reason
490 ,p_end_date => l_end_date
491 ,p_deployment_reason => p_deployment_reason
492 ,p_employee_number => p_employee_number
493 ,p_leaving_reason => p_leaving_reason
494 ,p_leaving_person_type_id => p_leaving_person_type_id
495 ,p_deplymt_policy_id => p_deplymt_policy_id
496 ,p_organization_id => p_organization_id
497 ,p_location_id => p_location_id
498 ,p_job_id => p_job_id
499 ,p_position_id => p_position_id
500 ,p_grade_id => p_grade_id
501 ,p_supervisor_id => p_supervisor_id
502 ,p_supervisor_assignment_id => p_supervisor_assignment_id
503 ,p_retain_direct_reports => p_retain_direct_reports
504 ,p_payroll_id => p_payroll_id
505 ,p_pay_basis_id => p_pay_basis_id
506 ,p_proposed_salary => p_proposed_salary
507 ,p_people_group_id => p_people_group_id
508 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
509 ,p_assignment_status_type_id => p_assignment_status_type_id
510 ,p_ass_status_change_reason => p_ass_status_change_reason
511 ,p_assignment_category => p_assignment_category
512 ,p_per_information1 => p_per_information1
513 ,p_per_information2 => p_per_information2
514 ,p_per_information3 => p_per_information3
515 ,p_per_information4 => p_per_information4
516 ,p_per_information5 => p_per_information5
517 ,p_per_information6 => p_per_information6
518 ,p_per_information7 => p_per_information7
519 ,p_per_information8 => p_per_information8
520 ,p_per_information9 => p_per_information9
521 ,p_per_information10 => p_per_information10
522 ,p_per_information11 => p_per_information11
523 ,p_per_information12 => p_per_information12
524 ,p_per_information13 => p_per_information13
525 ,p_per_information14 => p_per_information14
526 ,p_per_information15 => p_per_information15
527 ,p_per_information16 => p_per_information16
528 ,p_per_information17 => p_per_information17
529 ,p_per_information18 => p_per_information18
530 ,p_per_information19 => p_per_information19
531 ,p_per_information20 => p_per_information20
532 ,p_per_information21 => p_per_information21
533 ,p_per_information22 => p_per_information22
534 ,p_per_information23 => p_per_information23
535 ,p_per_information24 => p_per_information24
536 ,p_per_information25 => p_per_information25
537 ,p_per_information26 => p_per_information26
538 ,p_per_information27 => p_per_information27
539 ,p_per_information28 => p_per_information28
540 ,p_per_information29 => p_per_information29
541 ,p_per_information30 => p_per_information30
542 );
543 exception
544 when hr_api.cannot_find_prog_unit then
545 hr_api.cannot_find_prog_unit_error
546 (p_module_name => 'UPDATE_PERSON_DEPLOYMENT'
547 ,p_hook_type => 'BP'
548 );
549 end;
550 --
551 -- Validation in addition to Row Handlers
552 --
553
554
555
556 --
557 -- Process Logic
558 --
559 hr_pdt_upd.upd
560 (p_person_deployment_id => p_person_deployment_id
561 ,p_object_version_number => p_object_version_number
562 ,p_to_person_id => p_to_person_id
563 ,p_person_type_id => p_person_type_id
564 ,p_start_date => l_start_date
565 ,p_status => p_status
566 ,p_status_change_reason => p_status_change_reason
567 ,p_end_date => l_end_date
568 ,p_deployment_reason => p_deployment_reason
569 ,p_employee_number => p_employee_number
570 ,p_leaving_reason => p_leaving_reason
571 ,p_leaving_person_type_id => p_leaving_person_type_id
572 ,p_deplymt_policy_id => p_deplymt_policy_id
573 ,p_organization_id => p_organization_id
574 ,p_location_id => p_location_id
575 ,p_job_id => p_job_id
576 ,p_position_id => p_position_id
577 ,p_grade_id => p_grade_id
578 ,p_supervisor_id => p_supervisor_id
579 ,p_supervisor_assignment_id => p_supervisor_assignment_id
580 ,p_retain_direct_reports => p_retain_direct_reports
581 ,p_payroll_id => p_payroll_id
582 ,p_pay_basis_id => p_pay_basis_id
583 ,p_proposed_salary => p_proposed_salary
584 ,p_people_group_id => p_people_group_id
585 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
586 ,p_assignment_status_type_id => p_assignment_status_type_id
587 ,p_ass_status_change_reason => p_ass_status_change_reason
588 ,p_assignment_category => p_assignment_category
589 ,p_per_information1 => p_per_information1
590 ,p_per_information2 => p_per_information2
591 ,p_per_information3 => p_per_information3
592 ,p_per_information4 => p_per_information4
593 ,p_per_information5 => p_per_information5
594 ,p_per_information6 => p_per_information6
595 ,p_per_information7 => p_per_information7
596 ,p_per_information8 => p_per_information8
597 ,p_per_information9 => p_per_information9
598 ,p_per_information10 => p_per_information10
599 ,p_per_information11 => p_per_information11
600 ,p_per_information12 => p_per_information12
601 ,p_per_information13 => p_per_information13
602 ,p_per_information14 => p_per_information14
603 ,p_per_information15 => p_per_information15
604 ,p_per_information16 => p_per_information16
605 ,p_per_information17 => p_per_information17
606 ,p_per_information18 => p_per_information18
607 ,p_per_information19 => p_per_information19
608 ,p_per_information20 => p_per_information20
609 ,p_per_information21 => p_per_information21
610 ,p_per_information22 => p_per_information22
611 ,p_per_information23 => p_per_information23
612 ,p_per_information24 => p_per_information24
613 ,p_per_information25 => p_per_information25
614 ,p_per_information26 => p_per_information26
615 ,p_per_information27 => p_per_information27
616 ,p_per_information28 => p_per_information28
617 ,p_per_information29 => p_per_information29
618 ,p_per_information30 => p_per_information30
619 );
620
621 --
622 -- Call After Process User Hook
623 --
624 begin
625 HR_PERSON_DEPLOYMENT_BK2.UPDATE_PERSON_DEPLOYMENT_A
626 (p_person_deployment_id => p_person_deployment_id
627 ,p_object_version_number => p_object_version_number
628 ,p_to_person_id => p_to_person_id
629 ,p_person_type_id => p_person_type_id
630 ,p_start_date => l_start_date
631 ,p_status => p_status
632 ,p_status_change_reason => p_status_change_reason
633 ,p_end_date => l_end_date
634 ,p_deployment_reason => p_deployment_reason
635 ,p_employee_number => p_employee_number
636 ,p_leaving_reason => p_leaving_reason
637 ,p_leaving_person_type_id => p_leaving_person_type_id
638 ,p_deplymt_policy_id => p_deplymt_policy_id
639 ,p_organization_id => p_organization_id
640 ,p_location_id => p_location_id
641 ,p_job_id => p_job_id
642 ,p_position_id => p_position_id
643 ,p_grade_id => p_grade_id
644 ,p_supervisor_id => p_supervisor_id
645 ,p_supervisor_assignment_id => p_supervisor_assignment_id
646 ,p_retain_direct_reports => p_retain_direct_reports
647 ,p_payroll_id => p_payroll_id
648 ,p_pay_basis_id => p_pay_basis_id
649 ,p_proposed_salary => p_proposed_salary
650 ,p_people_group_id => p_people_group_id
651 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
652 ,p_assignment_status_type_id => p_assignment_status_type_id
653 ,p_ass_status_change_reason => p_ass_status_change_reason
654 ,p_assignment_category => p_assignment_category
655 ,p_per_information1 => p_per_information1
656 ,p_per_information2 => p_per_information2
657 ,p_per_information3 => p_per_information3
658 ,p_per_information4 => p_per_information4
659 ,p_per_information5 => p_per_information5
660 ,p_per_information6 => p_per_information6
661 ,p_per_information7 => p_per_information7
662 ,p_per_information8 => p_per_information8
663 ,p_per_information9 => p_per_information9
664 ,p_per_information10 => p_per_information10
665 ,p_per_information11 => p_per_information11
666 ,p_per_information12 => p_per_information12
667 ,p_per_information13 => p_per_information13
668 ,p_per_information14 => p_per_information14
669 ,p_per_information15 => p_per_information15
670 ,p_per_information16 => p_per_information16
671 ,p_per_information17 => p_per_information17
672 ,p_per_information18 => p_per_information18
673 ,p_per_information19 => p_per_information19
674 ,p_per_information20 => p_per_information20
675 ,p_per_information21 => p_per_information21
676 ,p_per_information22 => p_per_information22
677 ,p_per_information23 => p_per_information23
678 ,p_per_information24 => p_per_information24
679 ,p_per_information25 => p_per_information25
680 ,p_per_information26 => p_per_information26
681 ,p_per_information27 => p_per_information27
682 ,p_per_information28 => p_per_information28
683 ,p_per_information29 => p_per_information29
684 ,p_per_information30 => p_per_information30
685 ,p_policy_duration_warning => l_policy_duration_warning
686 );
687 exception
688 when hr_api.cannot_find_prog_unit then
689 hr_api.cannot_find_prog_unit_error
690 (p_module_name => 'UPDATE_PERSON_DEPLOYMENT'
691 ,p_hook_type => 'AP'
692 );
693 end;
694 --
695 -- When in validation only mode raise the Validate_Enabled exception
696 --
697 if p_validate then
698 raise hr_api.validate_enabled;
699 end if;
700 --
701 -- Set all IN OUT and OUT parameters with out values
702 --
703 p_policy_duration_warning := l_policy_duration_warning;
704 --
705 hr_utility.set_location(' Leaving:'||l_proc, 70);
706 exception
707 when hr_api.validate_enabled then
708 --
709 -- As the Validate_Enabled exception has been raised
710 -- we must rollback to the savepoint
711 --
712 rollback to update_person_deployment;
713 --
714 -- Reset IN OUT parameters and set OUT parameters
715 -- (Any key or derived arguments must be set to null
716 -- when validation only mode is being used.)
717 --
718 p_object_version_number := l_object_version_number;
719 p_policy_duration_warning := null;
720 hr_utility.set_location(' Leaving:'||l_proc, 80);
721 when others then
722 --
723 -- A validation or unexpected error has occured
724 --
725 rollback to update_person_deployment;
726 --
727 -- Reset IN OUT parameters and set all
728 -- OUT parameters, including warnings, to null
729 --
730 p_object_version_number := l_object_version_number;
731 p_policy_duration_warning := null;
732 hr_utility.set_location(' Leaving:'||l_proc, 90);
733 raise;
734 end update_person_deployment;
735 --
736 --
737 -- ----------------------------------------------------------------------------
738 -- |--------------------------< DELETE_PERSON_DEPLOYMENT >--------------------|
739 -- ----------------------------------------------------------------------------
740 --
741 procedure delete_person_deployment
742 (p_validate in boolean default false
743 ,p_person_deployment_id in number
744 ,p_object_version_number in number
745 ) is
746 --
747 -- Declare cursors and local variables
748 --
749 l_proc varchar2(72) := g_package||'delete_person_deployment';
750 begin
751 hr_utility.set_location('Entering:'|| l_proc, 10);
752 --
753 -- Issue a savepoint
754 --
755 savepoint delete_person_deployment;
756 --
757 -- Remember IN OUT parameter IN values
758 --
759
760 --
761 -- Truncate the time portion from all IN date parameters
762 --
763
764 --
765 -- Call Before Process User Hook
766 --
767 begin
768 HR_PERSON_DEPLOYMENT_BK3.DELETE_PERSON_DEPLOYMENT_B
769 (p_person_deployment_id => p_person_deployment_id
770 ,p_object_version_number => p_object_version_number
771 );
772 exception
773 when hr_api.cannot_find_prog_unit then
774 hr_api.cannot_find_prog_unit_error
775 (p_module_name => 'DELETE_PERSON_DEPLOYMENT'
776 ,p_hook_type => 'BP'
777 );
778 end;
779 --
780 -- Validation in addition to Row Handlers
781 --
782
783 --
784 -- Process Logic
785 --
786 hr_pdt_del.del
787 (p_person_deployment_id => p_person_deployment_id
788 ,p_object_version_number => p_object_version_number
789 );
790
791 --
792 -- Call After Process User Hook
793 --
794 begin
795 HR_PERSON_DEPLOYMENT_BK3.DELETE_PERSON_DEPLOYMENT_A
796 (p_person_deployment_id => p_person_deployment_id
797 ,p_object_version_number => p_object_version_number
798 );
799 exception
800 when hr_api.cannot_find_prog_unit then
801 hr_api.cannot_find_prog_unit_error
802 (p_module_name => 'DELETE_PERSON_DEPLOYMENT'
803 ,p_hook_type => 'AP'
804 );
805 end;
806 --
807 -- When in validation only mode raise the Validate_Enabled exception
808 --
809 if p_validate then
810 raise hr_api.validate_enabled;
811 end if;
812 --
813 -- Set all IN OUT and OUT parameters with out values
814 --
815 --
816 hr_utility.set_location(' Leaving:'||l_proc, 70);
817 exception
818 when hr_api.validate_enabled then
819 --
820 -- As the Validate_Enabled exception has been raised
821 -- we must rollback to the savepoint
822 --
823 rollback to delete_person_deployment;
824 --
825 -- Reset IN OUT parameters and set OUT parameters
826 -- (Any key or derived arguments must be set to null
827 -- when validation only mode is being used.)
828 --
829 hr_utility.set_location(' Leaving:'||l_proc, 80);
830 when others then
831 --
832 -- A validation or unexpected error has occured
833 --
834 rollback to delete_person_deployment;
835 --
836 -- Reset IN OUT parameters and set all
837 -- OUT parameters, including warnings, to null
838 --
839 hr_utility.set_location(' Leaving:'||l_proc, 90);
840 raise;
841 end delete_person_deployment;
842 --
843 -- ----------------------------------------------------------------------------
844 -- |--------------------------< initiate_deployment >-------------------------|
845 -- ----------------------------------------------------------------------------
846 --
847 procedure initiate_deployment
848 (p_validate in boolean default false
849 ,p_person_deployment_id in number
850 ,p_object_version_number in out nocopy number
851 ,p_host_person_id out nocopy number
852 ,p_host_per_ovn out nocopy number
853 ,p_host_assignment_id out nocopy number
854 ,p_host_asg_ovn out nocopy number
855 ,p_already_applicant_warning out nocopy boolean
856 ) is
857 --
858 -- Declare cursors and local variables
859 l_home_first_name per_all_people_f.last_name%type; -- 8605683
860 --
861 l_proc varchar2(72) := g_package||'initiate_deployment';
862 l_object_version_number hr_person_deployments.object_version_number%type;
863 --
864 -- Key data from per and asg
865 l_host_employee_number per_all_people_f.employee_number%type;
866 l_host_applicant_number per_all_people_f.applicant_number%type;
867 l_home_last_name per_all_people_f.last_name%type;
868 l_home_sex per_all_people_f.sex%type;
869 l_home_party_id per_all_people_f.party_id%type;
870 l_home_original_date_of_hire per_all_people_f.original_date_of_hire%type;
871 l_host_person_id number;
872 l_host_per_ovn number;
873 l_host_assignment_id number;
874 l_host_asg_ovn number;
875 l_host_asg_ovn1 number;
876 l_host_application_id number;
877 l_host_apl_ovn number;
878 l_host_per_esd date;
879 l_host_per_eed date;
880 l_host_asg_esd date;
881 l_host_asg_eed date;
882 l_host_per_full_name per_all_people_f.full_name%type;
883 l_home_asg_esd date;
884 l_home_asg_eed date;
885 l_host_person_extra_info_id number;
886 l_host_pei_ovn number;
887 l_contact_person_id number;
888 l_host_pyp_id number;
889 l_host_pyp_ovn number;
890 l_dummy_n number;
891
892 --variables for OUT and INOUT in API calls
893 l_cagr_grade_def_id number;
894 l_cagr_concatenated_segments varchar2(2000);
895 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
896 l_soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%TYPE;
897 l_comment_id per_all_assignments_f.comment_id%TYPE;
898 l_host_per_comment_id number;
899 l_host_asg_sequence number;
900 l_host_asg_number per_all_assignments_f.assignment_number%type;
901 l_assignment_status_type_id number;
902 l_special_ceiling_step_id number;
903 l_group_name pay_people_groups.group_name%TYPE;
904 l_host_contact_person_id number;
905 l_host_ctr_id number;
906 l_host_ctr_ovn number;
907 l_host_contact_per_ovn number;
908 l_host_contact_per_esd date;
909 l_host_contact_per_eed date;
910 l_host_contact_full_name per_all_people_f.full_name%type;
911 l_host_contact_per_comment_id number;
912 l_pyp_element_entry_id number;
913 --
914 l_name_combination_warning boolean;
915 l_assign_payroll_warning boolean;
916 l_orig_hire_warning boolean;
917 l_appl_override_warning boolean;
918 l_oversubscribed_vacancy_id number;
919 l_no_managers_warning boolean;
920 l_other_manager_warning boolean;
921 l_org_now_no_manager_warning boolean;
922 l_hourly_salaried_warning boolean;
923 l_gsp_post_process_warning varchar2(2000);
924 l_tax_district_changed_warning boolean;
925 l_entries_changed_warning varchar2(1);
926 l_spp_delete_warning boolean;
927 l_last_std_process_date_out date;
928 l_supervisor_warning boolean;
929 l_event_warning boolean;
930 l_interview_warning boolean;
931 l_review_warning boolean;
932 l_recruiter_warning boolean;
933 l_asg_future_changes_warning boolean;
934 l_pay_proposal_warning boolean;
935 l_dod_warning boolean;
936 l_inv_next_sal_date_warning boolean;
937 l_proposed_salary_warning boolean;
938 l_approved_warning boolean;
939 l_payroll_warning boolean;
940 --new variables declared
941 l_created_by per_all_assignments_f.created_by%TYPE;
942 l_creation_date per_all_assignments_f.creation_date%TYPE;
943 l_last_update_date per_all_assignments_f.last_update_date%TYPE;
944 l_last_updated_by per_all_assignments_f.last_updated_by%TYPE;
945 l_last_update_login per_all_assignments_f.last_update_login%TYPE;
946 l_payroll_id_updated BOOLEAN;
947 l_business_group_id hr_all_organization_units.organization_id%TYPE;
948 l_validation_start_date DATE;
949 l_validation_end_date DATE;
950 l_effective_start_date DATE;
951 l_effective_end_date DATE;
952 l_datetrack_update_mode varchar2(30);
953 --
954 l_later_count Number; -- Added for bug#16461888
955 --
956 --Warnings connected sepcifically with deployments
957 l_policy_duration_warning boolean;
958 l_already_applicant_warning boolean := false;
959 --
960 l_varray_d hr_dflex_utility.l_ignore_dfcode_varray
961 := hr_dflex_utility.l_ignore_dfcode_varray();
962 --
963 --Cursors and related variables
964 --
965 --
966 -- fix for bug 6593649
967 l_attachments varchar2(1);
968 cursor csr_get_attached_doc is
969 select null
970 from fnd_attached_documents
971 where PK1_VALUE =p_person_deployment_id
972 and ENTITY_NAME ='HR_PERSON_DEPLOYMENTS';
973 --
974 -- fix for bug 6593649
975 --
976 cursor csr_person_deployment(p_person_deployment_id number) is
977 select *
978 from hr_person_deployments dpl
979 where dpl.person_deployment_id = p_person_deployment_id;
980 --
981 l_dpl_rec csr_person_deployment%rowtype;
982 --
983 cursor csr_other_active_dpl(p_person_deployment_id number) is
984 select 1
985 from hr_person_deployments pdt1
986 where pdt1.person_deployment_id = p_person_deployment_id
987 and exists (select 1
988 from hr_person_deployments pdt2
989 where pdt2.person_deployment_id <> pdt1.person_deployment_id
990 and pdt2.from_person_id = pdt1.from_person_id
991 and pdt2.status in ('ACTIVE','COMPLETE')
992 and pdt1.start_date <= nvl(pdt2.end_date,hr_api.g_eot));
993 --
994 cursor csr_home_per_values(p_person_id number) is
995 select papf.last_name, papf.sex, papf.party_id, papf.original_date_of_hire,papf.first_name -- 8605683 , 8688303
996 from per_all_people_f papf
997 where papf.person_id = p_person_id
998 and l_dpl_rec.start_date between
999 papf.effective_start_date and papf.effective_end_date;
1000 --
1001 cursor csr_host_per_values(p_person_id number) is
1002 select papf.object_version_number
1003 from per_all_people_f papf
1004 where papf.person_id = p_person_id
1005 and l_dpl_rec.start_date between
1006 papf.effective_start_date and papf.effective_end_date;
1007 --
1008 cursor csr_host_asg_ovn(p_assignment_id number) is
1009 select paaf.object_version_number
1010 from per_all_assignments_f paaf
1011 where paaf.assignment_id = p_assignment_id
1012 and l_dpl_rec.start_date between
1013 paaf.effective_start_date and paaf.effective_end_date;
1014 --
1015 cursor csr_active_home_asgs(p_person_id number) is
1016 select paaf.assignment_id, paaf.object_version_number
1017 from per_all_assignments_f paaf,
1018 per_assignment_status_types past
1019 where paaf.person_id = p_person_id
1020 and l_dpl_rec.start_date between
1021 paaf.effective_start_date and paaf.effective_end_date
1022 and paaf.assignment_type = 'E'
1023 and paaf.assignment_status_type_id = past.assignment_status_type_id
1024 and past.per_system_status = 'ACTIVE_ASSIGN';
1025 --
1026 cursor csr_home_pds_details(p_person_id number) is
1027 select pds.period_of_service_id, pds.object_version_number
1028 from per_periods_of_service pds
1029 where pds.person_id = p_person_id
1030 and l_dpl_rec.start_date >= pds.date_start
1031 and pds.actual_termination_date is null;
1032 --
1033 l_home_pds_id number;
1034 l_home_pds_ovn number;
1035 --
1036 cursor csr_dpl_contacts(p_person_deployment_id number) is
1037 select *
1038 from hr_person_deplymt_contacts pdc
1039 where pdc.person_deployment_id = p_person_deployment_id;
1040 --
1041 cursor csr_contact_rel_details(p_contact_relationship_id number) is
1042 select *
1043 from per_contact_relationships ctr
1044 where ctr.contact_relationship_id = p_contact_relationship_id;
1045 --
1046 l_contact_rel_details csr_contact_rel_details%rowtype;
1047 --
1048 cursor csr_contact_person_details(p_person_id number, p_effective_date date) is
1049 select *
1050 from per_all_people_f papf
1051 where papf.person_id = p_person_id
1052 and p_effective_date between
1053 papf.effective_start_date and papf.effective_end_date;
1054 --
1055 l_contact_person_details csr_contact_person_details%rowtype;
1056 l_contact_created t_contact_created;
1057 l_contacts_created t_contacts_created := t_contacts_created();
1058 l_index_number number;
1059 --
1060 cursor csr_dpl_eits(p_person_deployment_id number) is
1061 select *
1062 from hr_person_deplymt_eits pde
1063 where pde.person_deployment_id = p_person_deployment_id;
1064 --
1065 cursor csr_eit_details(p_person_extra_info_id number) is
1066 select *
1067 from per_people_extra_info pei
1068 where pei.person_extra_info_id = p_person_extra_info_id;
1069 --
1070 l_eit_details csr_eit_details%rowtype;
1071 --
1072 /* cursor csr_direct_reports(p_person_id number, p_effective_date date) is
1073 select asg.assignment_id, asg.effective_start_date
1074 from per_all_assignments_f asg
1075 where asg.supervisor_id = p_person_id
1076 and asg.effective_end_date > p_effective_date;*/
1077 --
1078 cursor csr_direct_reports(p_person_id number, p_start_date date) is
1079 select *
1080 from per_all_assignments_f asg
1081 where asg.supervisor_id = p_person_id
1082 and p_start_date between asg.effective_start_date and asg.effective_end_date;
1083
1084 ------------------
1085 cursor csr_fut_dt_rows(p_person_id number, p_start_date date) is
1086 select asg.assignment_id, asg.effective_start_date,asg.effective_end_date
1087 from per_all_assignments_f asg
1088 where asg.supervisor_id = p_person_id
1089 and asg.effective_start_date > p_start_date;
1090 -----
1091
1092 -- local variables for people group
1093 l_group_id number :=null;
1094 l_pgp_segment1 varchar2(60):=hr_api.g_varchar2;
1095 l_pgp_segment2 varchar2(60):=hr_api.g_varchar2;
1096 l_pgp_segment3 varchar2(60):=hr_api.g_varchar2;
1097 l_pgp_segment4 varchar2(60):=hr_api.g_varchar2;
1098 l_pgp_segment5 varchar2(60):=hr_api.g_varchar2;
1099 l_pgp_segment6 varchar2(60):=hr_api.g_varchar2;
1100 l_pgp_segment7 varchar2(60):=hr_api.g_varchar2;
1101 l_pgp_segment8 varchar2(60):=hr_api.g_varchar2;
1102 l_pgp_segment9 varchar2(60):=hr_api.g_varchar2;
1103 l_pgp_segment10 varchar2(60):=hr_api.g_varchar2;
1104 l_pgp_segment11 varchar2(60):=hr_api.g_varchar2;
1105 l_pgp_segment12 varchar2(60):=hr_api.g_varchar2;
1106 l_pgp_segment13 varchar2(60):=hr_api.g_varchar2;
1107 l_pgp_segment14 varchar2(60):=hr_api.g_varchar2;
1108 l_pgp_segment15 varchar2(60):=hr_api.g_varchar2;
1109 l_pgp_segment16 varchar2(60):=hr_api.g_varchar2;
1110 l_pgp_segment17 varchar2(60):=hr_api.g_varchar2;
1111 l_pgp_segment18 varchar2(60):=hr_api.g_varchar2;
1112 l_pgp_segment19 varchar2(60):=hr_api.g_varchar2;
1113 l_pgp_segment20 varchar2(60):=hr_api.g_varchar2;
1114 l_pgp_segment21 varchar2(60):=hr_api.g_varchar2;
1115 l_pgp_segment22 varchar2(60):=hr_api.g_varchar2;
1116 l_pgp_segment23 varchar2(60):=hr_api.g_varchar2;
1117 l_pgp_segment24 varchar2(60):=hr_api.g_varchar2;
1118 l_pgp_segment25 varchar2(60):=hr_api.g_varchar2;
1119 l_pgp_segment26 varchar2(60):=hr_api.g_varchar2;
1120 l_pgp_segment27 varchar2(60):=hr_api.g_varchar2;
1121 l_pgp_segment28 varchar2(60):=hr_api.g_varchar2;
1122 l_pgp_segment29 varchar2(60):=hr_api.g_varchar2;
1123 l_pgp_segment30 varchar2(60):=hr_api.g_varchar2;
1124
1125 cursor c_pgp_segments (cur_p_people_group_id number) is
1126 select segment1,
1127 segment2,
1128 segment3,
1129 segment4,
1130 segment5,
1131 segment6,
1132 segment7,
1133 segment8,
1134 segment9,
1135 segment10,
1136 segment11,
1137 segment12,
1138 segment13,
1139 segment14,
1140 segment15,
1141 segment16,
1142 segment17,
1143 segment18,
1144 segment19,
1145 segment20,
1146 segment21,
1147 segment22,
1148 segment23,
1149 segment24,
1150 segment25,
1151 segment26,
1152 segment27,
1153 segment28,
1154 segment29,
1155 segment30
1156 from pay_people_groups
1157 where people_group_id = cur_p_people_group_id;
1158 -- for people group fields population.
1159
1160 begin
1161 hr_utility.set_location('Entering:'|| l_proc, 10);
1162 --
1163 -- Issue a savepoint
1164 --
1165 savepoint initiate_deployment;
1166 --
1167 -- Remember IN OUT parameter IN values
1168 --
1169 l_object_version_number := p_object_version_number;
1170 --
1171 -- Truncate the time portion from all IN date parameters
1172 --
1173 --
1174 -- Validation in addition to Row Handlers
1175 --
1176 open csr_person_deployment(p_person_deployment_id);
1177 fetch csr_person_deployment into l_dpl_rec;
1178 if csr_person_deployment%notfound then
1179 close csr_person_deployment;
1180 fnd_message.set_name('PER','HR_449609_DPL_NOT_EXIST');
1181 fnd_message.raise_error;
1182 else
1183 close csr_person_deployment;
1184 --
1185 hr_utility.set_location(l_proc,30);
1186 --
1187 end if;
1188 --
1189 if l_dpl_rec.status <> 'DRAFT' then
1190 fnd_message.set_name('PER','HR_449614_PDT_ALREADY_INIT');
1191 fnd_message.raise_error;
1192 end if;
1193 --
1194 open csr_other_active_dpl(p_person_deployment_id);
1195 fetch csr_other_active_dpl into l_dummy_n;
1196 if csr_other_active_dpl%found then
1197 close csr_other_active_dpl;
1198 fnd_message.set_name('PER','HR_449615_PDT_OTHER_ACTIVE');
1199 fnd_message.raise_error;
1200 else
1201 close csr_other_active_dpl;
1202 end if;
1203 --
1204 -- Set up the arrays for bypassing flex validation for information that does
1205 -- not exist in the deployment proposal
1206 --
1207 l_varray_d.delete;
1208 l_varray_d.extend(9);
1209 l_varray_d(1):='PER_ASSIGNMENTS';
1210 l_varray_d(2):='PER_PAY_PROPOSALS';
1211 l_varray_d(3):='PER_CONTACTS';
1212 l_varray_d(4):='Contact Relship Developer DF';
1213 l_varray_d(5):='PER_PEOPLE_EXTRA_INFO';
1214 l_varray_d(6):='PER_PERIODS_OF_SERVICE';
1215 l_varray_d(7):='PER_PDS_DEVELOPER_DF';
1216 --
1217 -- Added for bug 5491169
1218 l_varray_d(8):='PER_PEOPLE';
1219
1220 --Added for bug 14277481
1221 l_varray_d(9):='Person Developer DF';
1222
1223 hr_dflex_utility.create_ignore_df_validation(p_rec=>l_varray_d);
1224 --
1225 hr_utility.set_location(l_proc,20);
1226 --
1227 -- Process Logic
1228 --
1229 --
1230 -- Store parameter values which may be changed by the API calls
1231 --
1232 l_host_employee_number := l_dpl_rec.employee_number;
1233 --
1234 open csr_home_per_values(l_dpl_rec.from_person_id);
1235 fetch csr_home_per_values into l_home_last_name,l_home_sex,l_home_party_id,
1236 l_home_original_date_of_hire,l_home_first_name; -- 8605683 , 8688303
1237 close csr_home_per_values;
1238 --
1239 hr_utility.set_location(l_proc,40);
1240 --
1241 -- Set the global transfer in process variable used by person rowhandler validation
1242 --
1243 per_per_bus.g_global_transfer_in_process := true;
1244 --
1245 -- Start of termination or update to the Home BG records
1246 --
1247 if nvl(l_dpl_rec.permanent,'N') = 'Y' then
1248 --
1249 hr_utility.set_location(l_proc,170);
1250 --
1251 -- termination of home employee record at (l_dpl_rec.start_date-1)
1252 --
1253
1254 fnd_profile.put(name => 'HR_PROPAGATE_DATA_CHANGES'
1255 ,val => 'N');
1256
1257 open csr_home_pds_details(l_dpl_rec.from_person_id);
1258 fetch csr_home_pds_details into l_home_pds_id, l_home_pds_ovn;
1259 close csr_home_pds_details;
1260 --
1261 hr_ex_employee_api.actual_termination_emp
1262 (p_validate => p_validate
1263 ,p_effective_date => l_dpl_rec.start_date-1
1264 ,p_period_of_service_id => l_home_pds_id
1265 ,p_object_version_number => l_home_pds_ovn
1266 ,p_actual_termination_date => l_dpl_rec.start_date-1
1267 ,p_last_standard_process_date => null
1268 ,p_last_std_process_date_out => l_last_std_process_date_out
1269 ,p_supervisor_warning => l_supervisor_warning
1270 ,p_event_warning => l_event_warning
1271 ,p_interview_warning => l_interview_warning
1272 ,p_review_warning => l_review_warning
1273 ,p_recruiter_warning => l_recruiter_warning
1274 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1275 ,p_entries_changed_warning => l_entries_changed_warning
1276 ,p_pay_proposal_warning => l_pay_proposal_warning
1277 ,p_dod_warning => l_dod_warning
1278 );
1279
1280 fnd_profile.put(name => 'HR_PROPAGATE_DATA_CHANGES'
1281 ,val => 'Y');
1282 else
1283 --
1284 hr_utility.set_location(l_proc,160);
1285 --
1286 -- suspend the active home assignments, leaving the others.
1287 --
1288 for l_home_asg_rec in csr_active_home_asgs(l_dpl_rec.from_person_id) loop
1289 hr_assignment_api.suspend_emp_asg
1290 (p_validate => p_validate
1291 ,p_effective_date => l_dpl_rec.start_date
1292 ,p_datetrack_update_mode => 'UPDATE'
1293 ,p_assignment_id => l_home_asg_rec.assignment_id
1294 ,p_object_version_number => l_home_asg_rec.object_version_number
1295 ,p_effective_start_date => l_home_asg_esd
1296 ,p_effective_end_date => l_home_asg_eed
1297 );
1298 end loop;
1299 end if;
1300 --
1301 -- If retain_direct_reports was set, then need to update supervisor_id column for
1302 -- the direct reports to point to the l_host_person_id
1303 -- Do direct sql because there may be future updates to the records, and we do not
1304 -- want this to cause whole initiation to fail.
1305 --Moved this logic at later stage
1306 /* if nvl(l_dpl_rec.retain_direct_reports,'N') = 'Y' then
1307 for l_reports in csr_direct_reports
1308 (l_dpl_rec.from_person_id,l_dpl_rec.start_date) loop
1309 update per_all_assignments_f
1310 set supervisor_id = l_host_person_id,
1311 supervisor_assignment_id =
1312 decode(supervisor_assignment_id,null,null,l_host_assignment_id),
1313 object_version_number = object_version_number+1
1314 where assignment_id = l_reports.assignment_id
1315 and effective_start_date = l_reports.effective_start_date;
1316 end loop;
1317 end if;*/
1318 --
1319 -- End of termination or update to the Home BG records
1320 --
1321 --
1322 -- Start of create or update person section
1323 --
1324 if l_dpl_rec.to_person_id is null then --creating new emp in host BG
1325 --
1326 hr_utility.set_location(l_proc,50);
1327 --
1328 hr_employee_api.create_employee
1329 (p_validate => p_validate
1330 ,p_hire_date => l_dpl_rec.start_date
1331 ,p_business_group_id => l_dpl_rec.to_business_group_id
1332 ,p_last_name => l_home_last_name
1333 ,p_first_name => l_home_first_name -- 8605683 , 8688303
1334 ,p_sex => l_home_sex
1335 ,p_person_type_id => l_dpl_rec.person_type_id
1336 ,p_per_information_category => l_dpl_rec.per_information_category
1337 ,p_per_information1 => l_dpl_rec.per_information1
1338 ,p_per_information2 => l_dpl_rec.per_information2
1339 ,p_per_information3 => l_dpl_rec.per_information3
1340 ,p_per_information4 => l_dpl_rec.per_information4
1341 ,p_per_information5 => l_dpl_rec.per_information5
1342 ,p_per_information6 => l_dpl_rec.per_information6
1343 ,p_per_information7 => l_dpl_rec.per_information7
1344 ,p_per_information8 => l_dpl_rec.per_information8
1345 ,p_per_information9 => l_dpl_rec.per_information9
1346 ,p_per_information10 => l_dpl_rec.per_information10
1347 ,p_per_information11 => l_dpl_rec.per_information11
1348 ,p_per_information12 => l_dpl_rec.per_information12
1349 ,p_per_information13 => l_dpl_rec.per_information13
1350 ,p_per_information14 => l_dpl_rec.per_information14
1351 ,p_per_information15 => l_dpl_rec.per_information15
1352 ,p_per_information16 => l_dpl_rec.per_information16
1353 ,p_per_information17 => l_dpl_rec.per_information17
1354 ,p_per_information18 => l_dpl_rec.per_information18
1355 ,p_per_information19 => l_dpl_rec.per_information19
1356 ,p_per_information20 => l_dpl_rec.per_information20
1357 ,p_per_information21 => l_dpl_rec.per_information21
1358 ,p_per_information22 => l_dpl_rec.per_information22
1359 ,p_per_information23 => l_dpl_rec.per_information23
1360 ,p_per_information24 => l_dpl_rec.per_information24
1361 ,p_per_information25 => l_dpl_rec.per_information25
1362 ,p_per_information26 => l_dpl_rec.per_information26
1363 ,p_per_information27 => l_dpl_rec.per_information27
1364 ,p_per_information28 => l_dpl_rec.per_information28
1365 ,p_per_information29 => l_dpl_rec.per_information29
1366 ,p_per_information30 => l_dpl_rec.per_information30
1367 ,p_original_date_of_hire => l_home_original_date_of_hire
1368 ,p_adjusted_svc_date => l_home_original_date_of_hire
1369 ,p_party_id => l_home_party_id
1370 ,p_employee_number => l_host_employee_number
1371 ,p_person_id => l_host_person_id
1372 ,p_assignment_id => l_host_assignment_id
1373 ,p_per_object_version_number => l_host_per_ovn
1374 ,p_asg_object_version_number => l_host_asg_ovn
1375 ,p_per_effective_start_date => l_host_per_esd
1376 ,p_per_effective_end_date => l_host_per_eed
1377 ,p_full_name => l_host_per_full_name
1378 ,p_per_comment_id => l_host_per_comment_id
1379 ,p_assignment_sequence => l_host_asg_sequence
1380 ,p_assignment_number => l_host_asg_number
1381 ,p_name_combination_warning => l_name_combination_warning
1382 ,p_assign_payroll_warning => l_assign_payroll_warning
1383 ,p_orig_hire_warning => l_orig_hire_warning
1384 );
1385 elsif hr_person_type_usage_info.is_person_of_type
1386 (p_effective_date => l_dpl_rec.start_date
1387 ,p_person_id => l_dpl_rec.to_person_id
1388 ,p_system_person_type => 'EMP') then
1389 --already emp so raise error
1390 fnd_message.set_name('PER','HR_449616_PDT_ALREADY_EMP');
1391 fnd_message.raise_error;
1392
1393 elsif hr_person_type_usage_info.is_person_of_type
1394 (p_effective_date => l_dpl_rec.start_date
1395 ,p_person_id => l_dpl_rec.to_person_id
1396 ,p_system_person_type => 'CWK') then
1397 --already cwk so raise error
1398 fnd_message.set_name('PER','HR_449617_PDT_ALREADY_CWK');
1399 fnd_message.raise_error;
1400
1401 elsif hr_person_type_usage_info.is_person_of_type
1402 (p_effective_date => l_dpl_rec.start_date
1403 ,p_person_id => l_dpl_rec.to_person_id
1404 ,p_system_person_type => 'APL') then
1405 --error out in this case, there is no way to create an employee record from here
1406 fnd_message.set_name('PER','HR_449618_PDT_ALREADY_APL');
1407 fnd_message.raise_error;
1408 --
1409 else --we should have the all clear to hire existing person
1410 --
1411 hr_utility.set_location(l_proc,70);
1412 --
1413 l_host_person_id := l_dpl_rec.to_person_id;
1414 --
1415 open csr_host_per_values(l_dpl_rec.to_person_id);
1416 fetch csr_host_per_values into l_host_per_ovn;
1417 close csr_host_per_values;
1418 --
1419 hr_employee_api.hire_into_job
1420 (p_validate => p_validate
1421 ,p_effective_date => l_dpl_rec.start_date
1422 ,p_person_id => l_host_person_id
1423 ,p_object_version_number => l_host_per_ovn
1424 ,p_employee_number => l_host_employee_number
1425 -- ,p_datetrack_update_mode => 'UPDATE'
1426 ,p_person_type_id => l_dpl_rec.person_type_id
1427 ,p_national_identifier => null
1428 ,p_per_information7 => l_dpl_rec.per_information7
1429 ,p_assignment_id => l_host_assignment_id
1430 ,p_effective_start_date => l_host_per_esd
1431 ,p_effective_end_date => l_host_per_eed
1432 ,p_assign_payroll_warning => l_assign_payroll_warning
1433 ,p_orig_hire_warning => l_orig_hire_warning
1434 );
1435 --
1436 open csr_host_asg_ovn(l_host_assignment_id);
1437 fetch csr_host_asg_ovn into l_host_asg_ovn;
1438 close csr_host_asg_ovn;
1439 --
1440 end if;
1441 --
1442 hr_utility.set_location('host person id '||l_host_person_id,77);
1443 --
1444 --Code moved here
1445 if nvl(l_dpl_rec.retain_direct_reports,'N') = 'Y' then
1446
1447 --fetch the reportees
1448 for l_reports in csr_direct_reports(l_dpl_rec.from_person_id,l_dpl_rec.start_date)
1449 loop
1450 --Check for the update mode
1451 if l_dpl_rec.start_date=l_reports.effective_start_date then
1452 l_datetrack_update_mode:='CORRECTION';
1453
1454 elsif ((l_reports.effective_end_date <> hr_api.g_eot)and
1455 (l_reports.effective_end_date >l_dpl_rec.start_date) ) then
1456 --l_datetrack_update_mode:='UPDATE_CHANGE_INSERT'; /*Commented for bug#16461888*/
1457
1458 /*Start of code for bug#16461888*/
1459
1460 Select count(effective_start_date)
1461 into l_later_count
1462 from per_all_assignments_f
1463 where assignment_id = l_reports.assignment_id
1464 and effective_start_date > (select effective_end_date
1465 from per_all_assignments_f
1466 where assignment_id = l_reports.assignment_id
1467 and l_dpl_rec.start_date between effective_start_date and effective_end_date);
1468
1469 if (l_later_count > 0 ) then
1470 l_datetrack_update_mode:='UPDATE_CHANGE_INSERT';
1471 else
1472 l_datetrack_update_mode:='UPDATE';
1473 end if;
1474
1475 /*End of code for bug#16461888*/
1476
1477 elsif (l_reports.effective_end_date= hr_api.g_eot) then
1478 l_datetrack_update_mode:='UPDATE';
1479
1480 end if;
1481
1482
1483 if l_datetrack_update_mode = 'CORRECTION' then
1484
1485 update per_all_assignments_f
1486 set supervisor_id = l_host_person_id,
1487 supervisor_assignment_id =
1488 decode(supervisor_assignment_id,null,null,l_host_assignment_id),
1489 object_version_number = object_version_number+1
1490 where assignment_id = l_reports.assignment_id
1491 and effective_start_date = l_reports.effective_start_date;
1492
1493 elsif l_datetrack_update_mode ='UPDATE_CHANGE_INSERT' then
1494
1495 per_asg_upd.upd
1496 (p_assignment_id => l_reports.assignment_id
1497 ,p_effective_start_date => l_effective_start_date --l_effective_start_date
1498 ,p_effective_end_date => l_effective_end_date
1499 ,p_business_group_id => l_business_group_id
1500 ,p_assignment_status_type_id => l_reports.assignment_status_type_id
1501 ,p_assignment_type => l_reports.assignment_type --modified
1502 ,p_supervisor_id => l_host_person_id --modified
1503 ,p_supervisor_assignment_id => l_host_assignment_id --modified
1504 ,p_primary_flag => l_reports.primary_flag
1505 ,p_period_of_service_id => l_reports.period_of_service_id
1506 ,p_comment_id => l_comment_id
1507 ,p_object_version_number => l_reports.object_version_number
1508 ,p_payroll_id_updated => l_payroll_id_updated
1509 ,p_other_manager_warning => l_other_manager_warning
1510 ,p_no_managers_warning => l_no_managers_warning
1511 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1512 ,p_validation_start_date => l_validation_start_date
1513 ,p_validation_end_date => l_validation_end_date
1514 ,p_effective_date => l_dpl_rec.start_date
1515 ,p_datetrack_mode => 'UPDATE_CHANGE_INSERT'
1516 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1517 );
1518
1519 elsif l_datetrack_update_mode='UPDATE' then
1520
1521 per_asg_upd.upd
1522 (p_assignment_id => l_reports.assignment_id
1523 ,p_effective_start_date => l_effective_start_date --l_effective_start_date
1524 ,p_effective_end_date => l_effective_end_date
1525 ,p_business_group_id => l_business_group_id
1526 ,p_assignment_status_type_id => l_reports.assignment_status_type_id
1527 ,p_assignment_type => l_reports.assignment_type --modified
1528 ,p_supervisor_id => l_host_person_id --modified
1529 ,p_supervisor_assignment_id => l_host_assignment_id --modified
1530 ,p_primary_flag => l_reports.primary_flag
1531 ,p_period_of_service_id => l_reports.period_of_service_id
1532 ,p_comment_id => l_comment_id
1533 ,p_object_version_number => l_reports.object_version_number
1534 ,p_payroll_id_updated => l_payroll_id_updated
1535 ,p_other_manager_warning => l_other_manager_warning
1536 ,p_no_managers_warning => l_no_managers_warning
1537 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1538 ,p_validation_start_date => l_validation_start_date
1539 ,p_validation_end_date => l_validation_end_date
1540 ,p_effective_date => l_dpl_rec.start_date
1541 ,p_datetrack_mode => 'UPDATE'
1542 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1543 );
1544 end if;
1545 end loop;
1546 end if;
1547
1548 --now write code to handle future dt rows
1549 for l_fut_dt_rows in csr_fut_dt_rows(l_dpl_rec.from_person_id,l_dpl_rec.start_date)
1550 loop
1551 update per_all_assignments_f
1552 set supervisor_id = l_host_person_id,
1553 supervisor_assignment_id =
1554 decode(supervisor_assignment_id,null,null,l_host_assignment_id),
1555 object_version_number = object_version_number+1
1556 where assignment_id = l_fut_dt_rows.assignment_id
1557 and effective_start_date = l_fut_dt_rows.effective_start_date;
1558 end loop;
1559 --
1560 -- End of create or update person section
1561 --
1562 -- Start of update new host assignment section
1563 --
1564 l_soft_coding_keyflex_id := l_dpl_rec.soft_coding_keyflex_id;
1565 --
1566 --
1567 hr_utility.set_location(l_proc,80);
1568 --
1569 hr_assignment_api.update_emp_asg
1570 (p_validate => p_validate
1571 ,p_effective_date => l_dpl_rec.start_date
1572 ,p_datetrack_update_mode => 'CORRECTION'
1573 ,p_assignment_id => l_host_assignment_id
1574 ,p_object_version_number => l_host_asg_ovn
1575 ,p_supervisor_id => l_dpl_rec.supervisor_id
1576 ,p_assignment_number => l_host_asg_number
1577 ,p_change_reason => l_dpl_rec.ass_status_change_reason
1578 -- ,p_assignment_status_type_id => l_dpl_rec.assignment_status_type_id
1579 ,p_assignment_status_type_id =>
1580 nvl(l_dpl_rec.assignment_status_type_id,hr_api.g_number)
1581 ,p_comments => null
1582 ,p_date_probation_end => hr_api.g_date
1583 ,p_default_code_comb_id => hr_api.g_number
1584 ,p_frequency => hr_api.g_varchar2
1585 ,p_internal_address_line => hr_api.g_varchar2
1586 ,p_manager_flag => hr_api.g_varchar2
1587 ,p_normal_hours => hr_api.g_number
1588 ,p_perf_review_period => hr_api.g_number
1589 ,p_perf_review_period_frequency => hr_api.g_varchar2
1590 ,p_projected_assignment_end => l_dpl_rec.end_date
1591 ,p_probation_period => hr_api.g_number
1592 ,p_probation_unit => hr_api.g_varchar2
1593 ,p_sal_review_period => hr_api.g_number
1594 ,p_sal_review_period_frequency => hr_api.g_varchar2
1595 ,p_set_of_books_id => hr_api.g_number
1596 ,p_source_type => hr_api.g_varchar2
1597 ,p_time_normal_finish => hr_api.g_varchar2
1598 ,p_time_normal_start => hr_api.g_varchar2
1599 ,p_bargaining_unit_code => hr_api.g_varchar2
1600 ,p_labour_union_member_flag => hr_api.g_varchar2
1601 ,p_hourly_salaried_code => hr_api.g_varchar2
1602 ,p_ass_attribute_category => hr_api.g_varchar2
1603 ,p_ass_attribute1 => hr_api.g_varchar2
1604 ,p_ass_attribute2 => hr_api.g_varchar2
1605 ,p_ass_attribute3 => hr_api.g_varchar2
1606 ,p_ass_attribute4 => hr_api.g_varchar2
1607 ,p_ass_attribute5 => hr_api.g_varchar2
1608 ,p_ass_attribute6 => hr_api.g_varchar2
1609 ,p_ass_attribute7 => hr_api.g_varchar2
1610 ,p_ass_attribute8 => hr_api.g_varchar2
1611 ,p_ass_attribute9 => hr_api.g_varchar2
1612 ,p_ass_attribute10 => hr_api.g_varchar2
1613 ,p_ass_attribute11 => hr_api.g_varchar2
1614 ,p_ass_attribute12 => hr_api.g_varchar2
1615 ,p_ass_attribute13 => hr_api.g_varchar2
1616 ,p_ass_attribute14 => hr_api.g_varchar2
1617 ,p_ass_attribute15 => hr_api.g_varchar2
1618 ,p_ass_attribute16 => hr_api.g_varchar2
1619 ,p_ass_attribute17 => hr_api.g_varchar2
1620 ,p_ass_attribute18 => hr_api.g_varchar2
1621 ,p_ass_attribute19 => hr_api.g_varchar2
1622 ,p_ass_attribute20 => hr_api.g_varchar2
1623 ,p_ass_attribute21 => hr_api.g_varchar2
1624 ,p_ass_attribute22 => hr_api.g_varchar2
1625 ,p_ass_attribute23 => hr_api.g_varchar2
1626 ,p_ass_attribute24 => hr_api.g_varchar2
1627 ,p_ass_attribute25 => hr_api.g_varchar2
1628 ,p_ass_attribute26 => hr_api.g_varchar2
1629 ,p_ass_attribute27 => hr_api.g_varchar2
1630 ,p_ass_attribute28 => hr_api.g_varchar2
1631 ,p_ass_attribute29 => hr_api.g_varchar2
1632 ,p_ass_attribute30 => hr_api.g_varchar2
1633 ,p_title => hr_api.g_varchar2
1634 ,p_segment1 => hr_api.g_varchar2
1635 ,p_segment2 => hr_api.g_varchar2
1636 ,p_segment3 => hr_api.g_varchar2
1637 ,p_segment4 => hr_api.g_varchar2
1638 ,p_segment5 => hr_api.g_varchar2
1639 ,p_segment6 => hr_api.g_varchar2
1640 ,p_segment7 => hr_api.g_varchar2
1641 ,p_segment8 => hr_api.g_varchar2
1642 ,p_segment9 => hr_api.g_varchar2
1643 ,p_segment10 => hr_api.g_varchar2
1644 ,p_segment11 => hr_api.g_varchar2
1645 ,p_segment12 => hr_api.g_varchar2
1646 ,p_segment13 => hr_api.g_varchar2
1647 ,p_segment14 => hr_api.g_varchar2
1648 ,p_segment15 => hr_api.g_varchar2
1649 ,p_segment16 => hr_api.g_varchar2
1650 ,p_segment17 => hr_api.g_varchar2
1651 ,p_segment18 => hr_api.g_varchar2
1652 ,p_segment19 => hr_api.g_varchar2
1653 ,p_segment20 => hr_api.g_varchar2
1654 ,p_segment21 => hr_api.g_varchar2
1655 ,p_segment22 => hr_api.g_varchar2
1656 ,p_segment23 => hr_api.g_varchar2
1657 ,p_segment24 => hr_api.g_varchar2
1658 ,p_segment25 => hr_api.g_varchar2
1659 ,p_segment26 => hr_api.g_varchar2
1660 ,p_segment27 => hr_api.g_varchar2
1661 ,p_segment28 => hr_api.g_varchar2
1662 ,p_segment29 => hr_api.g_varchar2
1663 ,p_segment30 => hr_api.g_varchar2
1664 ,p_concat_segments => hr_api.g_varchar2
1665 ,p_contract_id => hr_api.g_number
1666 ,p_establishment_id => hr_api.g_number
1667 ,p_collective_agreement_id => hr_api.g_number
1668 ,p_cagr_id_flex_num => hr_api.g_number
1669 ,p_cag_segment1 => hr_api.g_varchar2
1670 ,p_cag_segment2 => hr_api.g_varchar2
1671 ,p_cag_segment3 => hr_api.g_varchar2
1672 ,p_cag_segment4 => hr_api.g_varchar2
1673 ,p_cag_segment5 => hr_api.g_varchar2
1674 ,p_cag_segment6 => hr_api.g_varchar2
1675 ,p_cag_segment7 => hr_api.g_varchar2
1676 ,p_cag_segment8 => hr_api.g_varchar2
1677 ,p_cag_segment9 => hr_api.g_varchar2
1678 ,p_cag_segment10 => hr_api.g_varchar2
1679 ,p_cag_segment11 => hr_api.g_varchar2
1680 ,p_cag_segment12 => hr_api.g_varchar2
1681 ,p_cag_segment13 => hr_api.g_varchar2
1682 ,p_cag_segment14 => hr_api.g_varchar2
1683 ,p_cag_segment15 => hr_api.g_varchar2
1684 ,p_cag_segment16 => hr_api.g_varchar2
1685 ,p_cag_segment17 => hr_api.g_varchar2
1686 ,p_cag_segment18 => hr_api.g_varchar2
1687 ,p_cag_segment19 => hr_api.g_varchar2
1688 ,p_cag_segment20 => hr_api.g_varchar2
1689 ,p_notice_period => hr_api.g_number
1690 ,p_notice_period_uom => hr_api.g_varchar2
1691 ,p_employee_category => hr_api.g_varchar2
1692 ,p_work_at_home => hr_api.g_varchar2
1693 ,p_job_post_source_name => hr_api.g_varchar2
1694 ,p_supervisor_assignment_id => l_dpl_rec.supervisor_assignment_id
1695 ,p_cagr_grade_def_id => l_cagr_grade_def_id
1696 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
1697 ,p_concatenated_segments => l_concatenated_segments
1698 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1699 ,p_comment_id => l_comment_id
1700 ,p_effective_start_date => l_host_asg_esd
1701 ,p_effective_end_date => l_host_asg_eed
1702 ,p_no_managers_warning => l_no_managers_warning
1703 ,p_other_manager_warning => l_other_manager_warning
1704 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1705 ,p_gsp_post_process_warning => l_gsp_post_process_warning
1706 );
1707 --
1708 hr_utility.set_location(l_proc,90);
1709 --
1710 -- sturlapa start
1711
1712 if l_dpl_rec.people_group_id is not null then
1713
1714 open c_pgp_segments(l_dpl_rec.people_group_id);
1715
1716 fetch c_pgp_segments into l_pgp_segment1,
1717 l_pgp_segment2,
1718 l_pgp_segment3,
1719 l_pgp_segment4,
1720 l_pgp_segment5,
1721 l_pgp_segment6,
1722 l_pgp_segment7,
1723 l_pgp_segment8,
1724 l_pgp_segment9,
1725 l_pgp_segment10,
1726 l_pgp_segment11,
1727 l_pgp_segment12,
1728 l_pgp_segment13,
1729 l_pgp_segment14,
1730 l_pgp_segment15,
1731 l_pgp_segment16,
1732 l_pgp_segment17,
1733 l_pgp_segment18,
1734 l_pgp_segment19,
1735 l_pgp_segment20,
1736 l_pgp_segment21,
1737 l_pgp_segment22,
1738 l_pgp_segment23,
1739 l_pgp_segment24,
1740 l_pgp_segment25,
1741 l_pgp_segment26,
1742 l_pgp_segment27,
1743 l_pgp_segment28,
1744 l_pgp_segment29,
1745 l_pgp_segment30;
1746
1747 close c_pgp_segments;
1748 end if;
1749
1750 /**
1751 * pass the people group id as null then internally it is trying to pull
1752 * existing ccid with concatatnated list.
1753 */
1754 hr_assignment_api.update_emp_asg_criteria
1755 (p_validate => p_validate
1756 ,p_effective_date => l_dpl_rec.start_date
1757 ,p_datetrack_update_mode => 'CORRECTION'
1758 ,p_assignment_id => l_host_assignment_id
1759 ,p_called_from_mass_update => false
1760 ,p_grade_id => l_dpl_rec.grade_id
1761 ,p_position_id => l_dpl_rec.position_id
1762 ,p_job_id => l_dpl_rec.job_id
1763 ,p_payroll_id => l_dpl_rec.payroll_id
1764 ,p_location_id => l_dpl_rec.location_id
1765 ,p_organization_id => l_dpl_rec.organization_id
1766 ,p_pay_basis_id => l_dpl_rec.pay_basis_id
1767 ,p_segment1 => l_pgp_segment1 --hr_api.g_varchar2
1768 ,p_segment2 => l_pgp_segment2 --hr_api.g_varchar2
1769 ,p_segment3 => l_pgp_segment3 --hr_api.g_varchar2
1770 ,p_segment4 => l_pgp_segment4 --hr_api.g_varchar2
1771 ,p_segment5 => l_pgp_segment5 --hr_api.g_varchar2
1772 ,p_segment6 => l_pgp_segment6 --hr_api.g_varchar2
1773 ,p_segment7 => l_pgp_segment7 --hr_api.g_varchar2
1774 ,p_segment8 => l_pgp_segment8 --hr_api.g_varchar2
1775 ,p_segment9 => l_pgp_segment9 --hr_api.g_varchar2
1776 ,p_segment10 => l_pgp_segment10 --hr_api.g_varchar2
1777 ,p_segment11 => l_pgp_segment11 --hr_api.g_varchar2
1778 ,p_segment12 => l_pgp_segment12 --hr_api.g_varchar2
1779 ,p_segment13 => l_pgp_segment13 --hr_api.g_varchar2
1780 ,p_segment14 => l_pgp_segment14 --hr_api.g_varchar2
1781 ,p_segment15 => l_pgp_segment15 --hr_api.g_varchar2
1782 ,p_segment16 => l_pgp_segment16 --hr_api.g_varchar2
1783 ,p_segment17 => l_pgp_segment17 --hr_api.g_varchar2
1784 ,p_segment18 => l_pgp_segment18 --hr_api.g_varchar2
1785 ,p_segment19 => l_pgp_segment19 --hr_api.g_varchar2
1786 ,p_segment20 => l_pgp_segment20 --hr_api.g_varchar2
1787 ,p_segment21 => l_pgp_segment21 --hr_api.g_varchar2
1788 ,p_segment22 => l_pgp_segment22 --hr_api.g_varchar2
1789 ,p_segment23 => l_pgp_segment23 --hr_api.g_varchar2
1790 ,p_segment24 => l_pgp_segment24 --hr_api.g_varchar2
1791 ,p_segment25 => l_pgp_segment25 --hr_api.g_varchar2
1792 ,p_segment26 => l_pgp_segment26 --hr_api.g_varchar2
1793 ,p_segment27 => l_pgp_segment27 --hr_api.g_varchar2
1794 ,p_segment28 => l_pgp_segment28 --hr_api.g_varchar2
1795 ,p_segment29 => l_pgp_segment29 --hr_api.g_varchar2
1796 ,p_segment30 => l_pgp_segment30 --hr_api.g_varchar2
1797 ,p_employment_category => l_dpl_rec.assignment_category
1798 ,p_concat_segments => hr_api.g_varchar2
1799 ,p_contract_id => hr_api.g_number
1800 ,p_establishment_id => hr_api.g_number
1801 ,p_scl_segment1 => hr_api.g_varchar2
1802 ,p_grade_ladder_pgm_id => hr_api.g_number
1803 ,p_supervisor_assignment_id => l_dpl_rec.supervisor_assignment_id
1804 ,p_object_version_number => l_host_asg_ovn
1805 ,p_special_ceiling_step_id => l_special_ceiling_step_id
1806 ,p_people_group_id => l_group_id --l_dpl_rec.people_group_id -- sturlapa
1807 ,p_soft_coding_keyflex_id => l_dpl_rec.soft_coding_keyflex_id
1808 ,p_group_name => l_group_name
1809 ,p_effective_start_date => l_host_asg_esd
1810 ,p_effective_end_date => l_host_asg_eed
1811 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1812 ,p_other_manager_warning => l_other_manager_warning
1813 ,p_spp_delete_warning => l_spp_delete_warning
1814 ,p_entries_changed_warning => l_entries_changed_warning
1815 ,p_tax_district_changed_warning => l_tax_district_changed_warning
1816 ,p_concatenated_segments => l_concatenated_segments
1817 ,p_gsp_post_process_warning => l_gsp_post_process_warning
1818 );
1819 -- sturlapa end
1820 --
1821 -- End of update new host assignment section
1822 --
1823 -- Create a salary proposal
1824 --
1825 if nvl(l_dpl_rec.permanent,'N') = 'Y'
1826 and l_dpl_rec.proposed_salary is not null then
1827 --
1828 hr_utility.set_location(l_proc,100);
1829 --
1830 hr_maintain_proposal_api.insert_salary_proposal
1831 (p_validate => p_validate
1832 ,p_pay_proposal_id => l_host_pyp_id
1833 ,p_assignment_id => l_host_assignment_id
1834 ,p_business_group_id => l_dpl_rec.to_business_group_id
1835 ,p_change_date => l_dpl_rec.start_date
1836 ,p_comments => null
1837 ,p_next_sal_review_date => null
1838 ,p_proposal_reason => null
1839 ,p_proposed_salary_n => l_dpl_rec.proposed_salary
1840 ,p_forced_ranking => null
1841 ,p_performance_review_id => null
1842 ,p_attribute_category => null
1843 ,p_attribute1 => null
1844 ,p_attribute2 => null
1845 ,p_attribute3 => null
1846 ,p_attribute4 => null
1847 ,p_attribute5 => null
1848 ,p_attribute6 => null
1849 ,p_attribute7 => null
1850 ,p_attribute8 => null
1851 ,p_attribute9 => null
1852 ,p_attribute10 => null
1853 ,p_attribute11 => null
1854 ,p_attribute12 => null
1855 ,p_attribute13 => null
1856 ,p_attribute14 => null
1857 ,p_attribute15 => null
1858 ,p_attribute16 => null
1859 ,p_attribute17 => null
1860 ,p_attribute18 => null
1861 ,p_attribute19 => null
1862 ,p_attribute20 => null
1863 ,p_object_version_number => l_host_pyp_ovn
1864 ,p_multiple_components => 'N'
1865 ,p_approved => 'Y'
1866 ,p_element_entry_id => l_pyp_element_entry_id
1867 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
1868 ,p_proposed_salary_warning => l_proposed_salary_warning
1869 ,p_approved_warning => l_approved_warning
1870 ,p_payroll_warning => l_payroll_warning
1871 );
1872 --
1873 end if;
1874 --
1875 -- Create contacts and relationships in host BG
1876 --
1877 for l_dpl_contact_rec in csr_dpl_contacts(p_person_deployment_id) loop
1878 <<contacts>>
1879 -- Fetch details from home BG
1880 --
1881 hr_utility.set_location(l_proc,110);
1882 --
1883 open csr_contact_rel_details(l_dpl_contact_rec.contact_relationship_id);
1884 fetch csr_contact_rel_details into l_contact_rel_details;
1885 close csr_contact_rel_details;
1886 --
1887 open csr_contact_person_details
1888 (l_contact_rel_details.contact_person_id,l_dpl_rec.start_date);
1889 fetch csr_contact_person_details into l_contact_person_details;
1890 close csr_contact_person_details;
1891 --
1892 --Loop around l_contacts_created to see if the contact already created in host
1893 --
1894 l_index_number := l_contacts_created.FIRST;
1895 l_contact_person_id := null;
1896 --
1897 WHILE l_index_number is not null loop
1898 <<created_contacts>>
1899 --
1900 hr_utility.set_location(l_proc,120);
1901 --
1902 if l_contact_rel_details.contact_person_id =
1903 l_contacts_created(l_index_number).home_contact_person_id then
1904 l_contact_person_id :=
1905 l_contacts_created(l_index_number).host_contact_person_id;
1906 --
1907 hr_utility.set_location(l_proc||' '||l_contact_person_id,123);
1908 --
1909 exit;
1910 else
1911 --
1912 hr_utility.set_location(l_proc,127);
1913 --
1914 l_contact_person_id := null;
1915 end if;
1916 l_index_number := l_contacts_created.NEXT(l_index_number);
1917 END LOOP created_contacts;
1918 --
1919 hr_utility.set_location(l_proc,130);
1920 hr_utility.set_location('host person id '||l_host_person_id,131);
1921 --
1922 hr_contact_rel_api.create_contact --use fetched details copying to host BG
1923 (p_validate => p_validate
1924 ,p_start_date => l_dpl_rec.start_date
1925 ,p_business_group_id => l_dpl_rec.to_business_group_id
1926 ,p_person_id => l_host_person_id
1927 ,p_contact_person_id => l_contact_person_id
1928 ,p_contact_type => l_contact_rel_details.contact_type
1929 ,p_primary_contact_flag => l_contact_rel_details.primary_contact_flag
1930 ,p_date_start => l_contact_rel_details.date_start
1931 ,p_date_end => l_contact_rel_details.date_end
1932 ,p_personal_flag => l_contact_rel_details.personal_flag
1933 ,p_last_name => l_contact_person_details.last_name
1934 ,p_sex => l_contact_person_details.sex
1935 ,p_date_of_birth => l_contact_person_details.date_of_birth
1936 ,p_email_address => l_contact_person_details.email_address
1937 ,p_first_name => l_contact_person_details.first_name
1938 ,p_known_as => l_contact_person_details.known_as
1939 ,p_marital_status => l_contact_person_details.marital_status
1940 ,p_middle_names => l_contact_person_details.middle_names
1941 ,p_nationality => l_contact_person_details.nationality
1942 ,p_national_identifier => l_contact_person_details.national_identifier
1943 ,p_previous_last_name => l_contact_person_details.previous_last_name
1944 ,p_title => l_contact_person_details.title
1945 ,p_work_telephone => l_contact_person_details.work_telephone
1946 ,p_correspondence_language => l_contact_person_details.correspondence_language
1947 ,p_honors => l_contact_person_details.honors
1948 ,p_pre_name_adjunct => l_contact_person_details.pre_name_adjunct
1949 ,p_suffix => l_contact_person_details.suffix
1950 ,p_create_mirror_flag => 'N'
1951 ,p_contact_relationship_id => l_host_ctr_id
1952 ,p_ctr_object_version_number => l_host_ctr_ovn
1953 ,p_per_person_id => l_host_contact_person_id
1954 ,p_per_object_version_number => l_host_contact_per_ovn
1955 ,p_per_effective_start_date => l_host_contact_per_esd
1956 ,p_per_effective_end_date => l_host_contact_per_eed
1957 ,p_full_name => l_host_contact_full_name
1958 ,p_per_comment_id => l_host_contact_per_comment_id
1959 ,p_name_combination_warning => l_name_combination_warning
1960 ,p_orig_hire_warning => l_orig_hire_warning
1961 );
1962 --
1963 -- Now store the contact person_ids from home and host for checking in next loop
1964 --
1965 --
1966 hr_utility.set_location(l_proc,140);
1967 --
1968 l_contact_created.home_contact_person_id := l_contact_rel_details.contact_person_id;
1969 l_contact_created.host_contact_person_id := l_host_contact_person_id;
1970 l_contacts_created.EXTEND;
1971 l_contacts_created(l_contacts_created.LAST) := l_contact_created;
1972 --
1973 end loop contacts;
1974 --
1975 -- Create EITs in host BG
1976 --
1977 for l_dpl_eit_rec in csr_dpl_eits(p_person_deployment_id) loop
1978 --
1979 hr_utility.set_location(l_proc,150);
1980 --
1981 -- Fetch details of EIT from home BG
1982 open csr_eit_details(l_dpl_eit_rec.person_extra_info_id);
1983 fetch csr_eit_details into l_eit_details;
1984 close csr_eit_details;
1985 --
1986 hr_person_extra_info_api.create_person_extra_info
1987 (p_validate => p_validate
1988 ,p_person_id => l_host_person_id
1989 ,p_information_type => l_eit_details.information_type
1990 ,p_pei_information_category => l_eit_details.pei_information_category
1991 ,p_pei_information1 => l_eit_details.pei_information1
1992 ,p_pei_information2 => l_eit_details.pei_information2
1993 ,p_pei_information3 => l_eit_details.pei_information3
1994 ,p_pei_information4 => l_eit_details.pei_information4
1995 ,p_pei_information5 => l_eit_details.pei_information5
1996 ,p_pei_information6 => l_eit_details.pei_information6
1997 ,p_pei_information7 => l_eit_details.pei_information7
1998 ,p_pei_information8 => l_eit_details.pei_information8
1999 ,p_pei_information9 => l_eit_details.pei_information9
2000 ,p_pei_information10 => l_eit_details.pei_information10
2001 ,p_pei_information11 => l_eit_details.pei_information11
2002 ,p_pei_information12 => l_eit_details.pei_information12
2003 ,p_pei_information13 => l_eit_details.pei_information13
2004 ,p_pei_information14 => l_eit_details.pei_information14
2005 ,p_pei_information15 => l_eit_details.pei_information15
2006 ,p_pei_information16 => l_eit_details.pei_information16
2007 ,p_pei_information17 => l_eit_details.pei_information17
2008 ,p_pei_information18 => l_eit_details.pei_information18
2009 ,p_pei_information19 => l_eit_details.pei_information19
2010 ,p_pei_information20 => l_eit_details.pei_information20
2011 ,p_pei_information21 => l_eit_details.pei_information21
2012 ,p_pei_information22 => l_eit_details.pei_information22
2013 ,p_pei_information23 => l_eit_details.pei_information23
2014 ,p_pei_information24 => l_eit_details.pei_information24
2015 ,p_pei_information25 => l_eit_details.pei_information25
2016 ,p_pei_information26 => l_eit_details.pei_information26
2017 ,p_pei_information27 => l_eit_details.pei_information27
2018 ,p_pei_information28 => l_eit_details.pei_information28
2019 ,p_pei_information29 => l_eit_details.pei_information29
2020 ,p_pei_information30 => l_eit_details.pei_information30
2021 ,p_person_extra_info_id => l_host_person_extra_info_id
2022 ,p_object_version_number => l_host_pei_ovn
2023 );
2024 end loop;
2025 hr_utility.set_location(l_proc,180);
2026 --
2027 -- update the proposal with the new details to keep it up to date
2028 --
2029 if nvl(l_dpl_rec.permanent,'N') = 'Y' then
2030 --
2031 hr_utility.set_location(l_proc,190);
2032 --
2033 hr_person_deployment_api.update_person_deployment
2034 (p_validate => p_validate
2035 ,p_person_deployment_id => p_person_deployment_id
2036 ,p_object_version_number => p_object_version_number
2037 ,p_to_person_id => l_host_person_id
2038 ,p_status => 'COMPLETE'
2039 ,p_policy_duration_warning => l_policy_duration_warning
2040 );
2041 else
2042 --
2043 hr_utility.set_location(l_proc,200);
2044 --
2045 hr_person_deployment_api.update_person_deployment
2046 (p_validate => p_validate
2047 ,p_person_deployment_id => p_person_deployment_id
2048 ,p_object_version_number => p_object_version_number
2049 ,p_to_person_id => l_host_person_id
2050 ,p_status => 'ACTIVE'
2051 ,p_policy_duration_warning => l_policy_duration_warning
2052 );
2053 end if;
2054 --
2055 hr_dflex_utility.remove_ignore_df_validation;
2056 --
2057 --
2058 -- Raise Workflow Business Event....to be implemented in a later phase
2059 --
2060
2061 --
2062 -- When in validation only mode raise the Validate_Enabled exception
2063 --
2064 if p_validate then
2065 raise hr_api.validate_enabled;
2066 end if;
2067 --
2068 -- Set all IN OUT and OUT parameters with out values
2069 --
2070 p_host_person_id := l_host_person_id;
2071 p_host_per_ovn := l_host_per_ovn;
2072 p_host_assignment_id := l_host_assignment_id;
2073 p_host_asg_ovn := l_host_asg_ovn;
2074 p_already_applicant_warning := l_already_applicant_warning;
2075 --
2076 --
2077 -- fix for bug 6593649
2078 open csr_get_attached_doc;
2079 fetch csr_get_attached_doc into l_attachments;
2080 if csr_get_attached_doc%found then
2081 close csr_get_attached_doc;
2082 hr_utility.set_location(l_host_person_id,200);
2083
2084 update fnd_attached_documents
2085 set ENTITY_NAME='PER_PEOPLE_F' ,PK1_VALUE =l_host_person_id
2086 WHERE PK1_VALUE=p_person_deployment_id
2087 and ENTITY_NAME ='HR_PERSON_DEPLOYMENTS' ;
2088
2089 else
2090 hr_utility.set_location(l_host_person_id,220);
2091 close csr_get_attached_doc;
2092 end if;
2093 --
2094 -- fix for bug 6593649
2095 --
2096 per_per_bus.g_global_transfer_in_process := false;
2097 --
2098 hr_utility.set_location(' Leaving:'||l_proc, 700);
2099 exception
2100 when hr_api.validate_enabled then
2101 --
2102 -- As the Validate_Enabled exception has been raised
2103 -- we must rollback to the savepoint
2104 --
2105 rollback to initiate_deployment;
2106 --
2107 hr_dflex_utility.remove_ignore_df_validation;
2108 --
2109 --
2110 -- Reset IN OUT parameters and set OUT parameters
2111 -- (Any key or derived arguments must be set to null
2112 -- when validation only mode is being used.)
2113 --
2114 p_object_version_number := l_object_version_number;
2115 p_host_person_id := null;
2116 p_host_per_ovn := null;
2117 p_host_assignment_id := null;
2118 p_host_asg_ovn := null;
2119 p_already_applicant_warning := null;
2120 --
2121 per_per_bus.g_global_transfer_in_process := false;
2122 --
2123 hr_utility.set_location(' Leaving:'||l_proc, 800);
2124 when others then
2125 --
2126 -- A validation or unexpected error has occured
2127 --
2128 rollback to initiate_deployment;
2129 --
2130 hr_dflex_utility.remove_ignore_df_validation;
2131 --
2132 --
2133 -- Reset IN OUT parameters and set all
2134 -- OUT parameters, including warnings, to null
2135 --
2136 p_object_version_number := l_object_version_number;
2137 p_host_person_id := null;
2138 p_host_per_ovn := null;
2139 p_host_assignment_id := null;
2140 p_host_asg_ovn := null;
2141 p_already_applicant_warning := null;
2142 --
2143 per_per_bus.g_global_transfer_in_process := false;
2144 --
2145 hr_utility.set_location(' Leaving:'||l_proc, 900);
2146 raise;
2147 end initiate_deployment;
2148 --
2149 --
2150 -- ----------------------------------------------------------------------------
2151 -- |--------------------------< change_deployment_dates >---------------------|
2152 -- ----------------------------------------------------------------------------
2153 --
2154 procedure change_deployment_dates
2155 (p_validate in boolean default false
2156 ,p_person_deployment_id in number
2157 ,p_object_version_number in out nocopy number
2158 ,p_start_date in date default hr_api.g_date
2159 ,p_end_date in date default hr_api.g_date
2160 ,p_deplymt_policy_id in number default hr_api.g_number
2161 ) is
2162 --
2163 -- Declare cursors and local variables
2164 --
2165 l_proc varchar2(72) := g_package||'change_deployment_dates';
2166 --
2167 l_object_version_number number;
2168 l_start_date_in date;
2169 l_end_date_in date;
2170 l_policy_duration_warning boolean;
2171 l_dummy number;
2172 l_warn_ee varchar2(1);
2173 --
2174 l_cagr_grade_def_id number;
2175 l_cagr_concatenated_segments varchar2(2000);
2176 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%type;
2177 l_soft_coding_keyflex_id number;
2178 l_comment_id number;
2179 l_host_asg_esd date;
2180 l_host_asg_eed date;
2181 l_no_managers_warning boolean;
2182 l_other_manager_warning boolean;
2183 l_hourly_salaried_warning boolean;
2184 l_gsp_post_process_warning varchar2(2000);
2185 --
2186 l_varray_d hr_dflex_utility.l_ignore_dfcode_varray
2187 := hr_dflex_utility.l_ignore_dfcode_varray();
2188
2189 cursor csr_person_deployment(p_person_deployment_id number) is
2190 select *
2191 from hr_person_deployments dpl
2192 where dpl.person_deployment_id = p_person_deployment_id;
2193 --
2194 l_dpl_rec csr_person_deployment%rowtype;
2195 --
2196 cursor csr_susp_home_asgs(p_person_deployment_id number) is
2197 select paaf.assignment_id, paaf.effective_start_date
2198 from per_all_assignments_f paaf,
2199 per_assignment_status_types past
2200 where paaf.person_id = L_DPL_REC.FROM_PERSON_ID
2201 and paaf.effective_start_date = L_DPL_REC.START_DATE
2202 and paaf.assignment_status_type_id = past.assignment_status_type_id
2203 and past.per_system_status = 'SUSP_ASSIGN';
2204 --
2205 cursor csr_overlap_asg_update
2206 (p_assignment_id number, p_old_date date, p_new_date date) is
2207 select 1
2208 from per_all_assignments_f paaf
2209 where paaf.assignment_id = p_assignment_id
2210 and paaf.effective_end_date = p_old_date-1
2211 and paaf.effective_start_date >= p_new_date;
2212 --
2213 cursor csr_host_assignments(p_start_date date) is
2214 select asg.assignment_id,asg.object_version_number,asg.effective_start_date
2215 from per_all_assignments_f asg,
2216 hr_person_deployments pdt,
2217 per_periods_of_service pds
2218 where asg.person_id = pdt.to_person_id
2219 and pdt.person_deployment_id = p_person_deployment_id
2220 and asg.period_of_service_id = pds.period_of_service_id
2221 and pds.date_start = p_start_date
2222 and trunc(sysdate) between asg.effective_start_date and
2223 asg.effective_end_date;
2224
2225 -- Commented for bug 5636625
2226 -- and asg.projected_assignment_end is not null;
2227 --
2228 begin
2229 --
2230 hr_utility.set_location('Entering:'|| l_proc, 10);
2231 --
2232 -- Issue a savepoint
2233 --
2234 savepoint change_deployment_dates;
2235 --
2236 -- Remember IN OUT parameter IN values
2237 --
2238 l_object_version_number := p_object_version_number;
2239 --
2240 -- Truncate the time portion from all IN date parameters
2241 --
2242 l_start_date_in := trunc(p_start_date);
2243 l_end_date_in := trunc(p_end_date);
2244 --
2245 hr_utility.set_location(l_proc,20);
2246 --
2247 -- Validation in addition to Row Handlers
2248 --
2249 open csr_person_deployment(p_person_deployment_id);
2250 fetch csr_person_deployment into l_dpl_rec;
2251 if csr_person_deployment%notfound then
2252 close csr_person_deployment;
2253 fnd_message.set_name('PER','HR_449609_DPL_NOT_EXIST');
2254 fnd_message.raise_error;
2255 else
2256 close csr_person_deployment;
2257 --
2258 hr_utility.set_location(l_proc,30);
2259 --
2260 end if;
2261 --
2262 if l_dpl_rec.status = 'DRAFT' then
2263 fnd_message.set_name('PER','HR_449619_PDT_DRAFT_NO_CHG');
2264 fnd_message.raise_error;
2265 elsif l_dpl_rec.status = 'COMPLETE' then
2266 fnd_message.set_name('PER','HR_449620_PDT_COMPLETE_NO_CHG');
2267 fnd_message.raise_error;
2268 end if;
2269 --
2270 -- Added Bipul
2271 if (l_dpl_rec.status = 'ACTIVE' and
2272 l_end_date_in is not null
2273 and l_end_date_in < trunc(sysdate)) then
2274 fnd_message.set_name('PER','HR_449772_PDT_INV_END_DATE');
2275 fnd_message.raise_error;
2276 end if;
2277
2278
2279 hr_utility.set_location(l_proc,40);
2280 --
2281 if nvl(l_start_date_in,l_dpl_rec.start_date) >=
2282 nvl(l_end_date_in,l_dpl_rec.end_date) then
2283 fnd_message.set_name('PER','HR_449621_PDT_CHG_DATES');
2284 fnd_message.raise_error;
2285 end if;
2286 --
2287 if nvl(l_dpl_rec.permanent,'N') = 'Y' then
2288 fnd_message.set_name('PER','HR_449622_PDT_PERM_NO_CHG');
2289 fnd_message.raise_error;
2290 end if;
2291 --
2292 -- Process Logic
2293 --
2294 if ((nvl(l_start_date_in,hr_api.g_date) <> hr_api.g_date)
2295 OR (l_end_date_in is null and l_dpl_rec.end_date is not null)
2296 OR (l_end_date_in <> hr_api.g_date)) then
2297 --
2298 -- At least one date has changed, proceed according to which one changed
2299 --
2300
2301 if nvl(l_start_date_in,hr_api.g_date) = hr_api.g_date then
2302 --
2303 hr_utility.set_location(l_proc,50);
2304 --
2305 --start date the same
2306 l_start_date_in := l_dpl_rec.start_date;
2307 else
2308 --
2309 hr_utility.set_location(l_proc,60);
2310 --
2311 --start date changed
2312 hr_change_start_date_api.update_start_date
2313 (p_validate => p_validate
2314 ,p_person_id => l_dpl_rec.to_person_id
2315 ,p_old_start_date => l_dpl_rec.start_date
2316 ,p_new_start_date => l_start_date_in
2317 ,p_update_type => 'E'
2318 ,p_applicant_number => null
2319 ,p_warn_ee => l_warn_ee
2320 );
2321 --
2322 for l_asg in csr_susp_home_asgs(p_person_deployment_id) loop
2323 --
2324 hr_utility.set_location(l_proc,70);
2325 hr_utility.set_location('assignment id '||l_asg.assignment_id,71);
2326 --
2327 open csr_overlap_asg_update
2328 (l_asg.assignment_id,l_dpl_rec.start_date,l_start_date_in);
2329 fetch csr_overlap_asg_update into l_dummy;
2330 if csr_overlap_asg_update%notfound then
2331 --
2332 update per_all_assignments_f paaf
2333 set paaf.effective_start_date = l_start_date_in
2334 where paaf.assignment_id = l_asg.assignment_id
2335 and paaf.effective_start_date = l_dpl_rec.start_date;
2336 --
2337 update per_all_assignments_f paaf
2338 set paaf.effective_end_date = l_start_date_in-1
2339 where paaf.assignment_id = l_asg.assignment_id
2340 and paaf.effective_end_date = l_dpl_rec.start_date-1;
2341 --
2342 close csr_overlap_asg_update;
2343 else
2344 close csr_overlap_asg_update;
2345 fnd_message.set_name('PER','HR_449623_PDT_CHG_ASG_OVERLAP');
2346 fnd_message.raise_error;
2347 end if;
2348 --
2349 end loop;
2350 end if;
2351 --
2352 -- Bug 5635350 modified the following if condition
2353 -- if l_end_date_in = hr_api.g_date then
2354 if l_end_date_in = hr_api.g_date or l_end_date_in = l_dpl_rec.end_date then
2355 --
2356 hr_utility.set_location(l_proc,80);
2357 --
2358 --end date the same
2359 l_end_date_in := l_dpl_rec.end_date;
2360 else
2361 --
2362 hr_utility.set_location(l_proc,90);
2363 --
2364 -- The end date has changed, this is simply an update to projected asg end in host
2365 -- If start_date also moved later than sysdate we have to do correction instead
2366
2367 l_varray_d.delete;
2368 l_varray_d.extend(1);
2369 l_varray_d(1):='PER_ASSIGNMENTS';
2370 hr_dflex_utility.create_ignore_df_validation(p_rec=>l_varray_d);
2371
2372 for l_host in csr_host_assignments(l_start_date_in) loop
2373 --
2374 -- if l_start_date_in < trunc(sysdate) then
2375 if l_host.effective_start_date < trunc(sysdate) then
2376 --
2377 -- Start date is
2378 --
2379 hr_assignment_api.update_emp_asg
2380 (p_validate => p_validate
2381 ,p_effective_date => trunc(sysdate)
2382 ,p_datetrack_update_mode => 'UPDATE'
2383 ,p_assignment_id => l_host.assignment_id
2384 ,p_object_version_number => l_host.object_version_number
2385 ,p_projected_assignment_end => l_end_date_in
2386 ,p_cagr_grade_def_id => l_cagr_grade_def_id
2387 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
2388 ,p_concatenated_segments => l_concatenated_segments
2389 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
2390 ,p_comment_id => l_comment_id
2391 ,p_effective_start_date => l_host_asg_esd
2392 ,p_effective_end_date => l_host_asg_eed
2393 ,p_no_managers_warning => l_no_managers_warning
2394 ,p_other_manager_warning => l_other_manager_warning
2395 ,p_hourly_salaried_warning => l_hourly_salaried_warning
2396 ,p_gsp_post_process_warning => l_gsp_post_process_warning
2397 );
2398 else
2399 hr_assignment_api.update_emp_asg
2400 (p_validate => p_validate
2401 ,p_effective_date => trunc(sysdate)
2402 ,p_datetrack_update_mode => 'CORRECTION'
2403 ,p_assignment_id => l_host.assignment_id
2404 ,p_object_version_number => l_host.object_version_number
2405 ,p_projected_assignment_end => l_end_date_in
2406 ,p_cagr_grade_def_id => l_cagr_grade_def_id
2407 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
2408 ,p_concatenated_segments => l_concatenated_segments
2409 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
2410 ,p_comment_id => l_comment_id
2411 ,p_effective_start_date => l_host_asg_esd
2412 ,p_effective_end_date => l_host_asg_eed
2413 ,p_no_managers_warning => l_no_managers_warning
2414 ,p_other_manager_warning => l_other_manager_warning
2415 ,p_hourly_salaried_warning => l_hourly_salaried_warning
2416 ,p_gsp_post_process_warning => l_gsp_post_process_warning
2417 );
2418 end if;
2419 --
2420 end loop;
2421 end if;
2422
2423 hr_dflex_utility.remove_ignore_df_validation;
2424 --
2425 -- update the proposal with the new details to keep it up to date
2426 --
2427 hr_utility.set_location(l_proc,100);
2428 --
2429 hr_person_deployment_api.update_person_deployment
2430 (p_validate => p_validate
2431 ,p_person_deployment_id => p_person_deployment_id
2432 ,p_object_version_number => p_object_version_number
2433 ,p_start_date => l_start_date_in
2434 ,p_end_date => l_end_date_in
2435 ,p_policy_duration_warning => l_policy_duration_warning
2436 );
2437 end if;
2438 --
2439 -- When in validation only mode raise the Validate_Enabled exception
2440 --
2441 if p_validate then
2442 raise hr_api.validate_enabled;
2443 end if;
2444 --
2445 -- Set all IN OUT and OUT parameters with out values
2446 --
2447
2448 --
2449 hr_utility.set_location(' Leaving:'||l_proc, 700);
2450 exception
2451 when hr_api.validate_enabled then
2452 --
2453 -- As the Validate_Enabled exception has been raised
2454 -- we must rollback to the savepoint
2455 --
2456 rollback to change_deployment_dates;
2457 hr_dflex_utility.remove_ignore_df_validation;
2458 --
2459 -- Reset IN OUT parameters and set OUT parameters
2460 -- (Any key or derived arguments must be set to null
2461 -- when validation only mode is being used.)
2462 --
2463 p_object_version_number := l_object_version_number;
2464 hr_utility.set_location(' Leaving:'||l_proc, 800);
2465 when others then
2466 --
2467 -- A validation or unexpected error has occured
2468 --
2469 rollback to change_deployment_dates;
2470 hr_dflex_utility.remove_ignore_df_validation;
2471 --
2472 -- Reset IN OUT parameters and set all
2473 -- OUT parameters, including warnings, to null
2474 --
2475 p_object_version_number := l_object_version_number;
2476 hr_utility.set_location(' Leaving:'||l_proc, 900);
2477 raise;
2478 end change_deployment_dates;
2479 --
2480 --
2481 -- ----------------------------------------------------------------------------
2482 -- |--------------------------< return_from_deployment >----------------------|
2483 -- ----------------------------------------------------------------------------
2484 --
2485 procedure return_from_deployment
2486 (p_validate in boolean default false
2487 ,p_person_deployment_id in number
2488 ,p_object_version_number in out nocopy number
2489 ,p_end_date in date default hr_api.g_date
2490 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
2491 ,p_leaving_person_type_id in number default hr_api.g_number
2492 ) is
2493 --
2494 -- Declare cursors and local variables
2495 --
2496 l_proc varchar2(72) := g_package||'return_from_deployment';
2497 l_object_version_number number;
2498 --
2499 l_end_date_in date;
2500 l_leaving_reason varchar2(30);
2501 l_leaving_person_type_id number;
2502 l_home_assignment_id number;
2503 l_home_asg_ovn number;
2504 l_home_asg_esd date;
2505 l_home_asg_eed date;
2506 l_host_pds_id number;
2507 l_host_pds_ovn number;
2508 --
2509 l_policy_duration_warning boolean;
2510 l_last_std_process_date_out date;
2511 l_supervisor_warning boolean;
2512 l_event_warning boolean;
2513 l_interview_warning boolean;
2514 l_review_warning boolean;
2515 l_recruiter_warning boolean;
2516 l_asg_future_changes_warning boolean;
2517 l_entries_changed_warning varchar2(1);
2518 l_pay_proposal_warning boolean;
2519 l_dod_warning boolean;
2520 --
2521 cursor csr_person_deployment(p_person_deployment_id number) is
2522 select *
2523 from hr_person_deployments dpl
2524 where dpl.person_deployment_id = p_person_deployment_id;
2525 --
2526 l_dpl_rec csr_person_deployment%rowtype;
2527 --
2528 cursor csr_host_pds(p_person_id number) is
2529 select pds.period_of_service_id, pds.object_version_number
2530 from per_periods_of_service pds
2531 where pds.person_id = p_person_id
2532 and pds.date_start = L_DPL_REC.START_DATE;
2533 --
2534 -- following cursor needs to get the assignments which were suspended in line with
2535 -- start of deployment, but ignore others
2536 --
2537 cursor csr_susp_home_asgs(p_person_id number) is
2538 select paaf.assignment_id, paaf.object_version_number
2539 from per_all_assignments_f paaf,
2540 per_assignment_status_types past
2541 where paaf.person_id = p_person_id
2542 and L_END_DATE_IN between
2543 paaf.effective_start_date and paaf.effective_end_date
2544 and paaf.assignment_type = 'E'
2545 and paaf.assignment_status_type_id = past.assignment_status_type_id
2546 and past.per_system_status = 'SUSP_ASSIGN'
2547 and exists
2548 (select 1
2549 from per_all_assignments_f paaf1,
2550 per_assignment_status_types past1
2551 where paaf1.assignment_id = paaf.assignment_id
2552 and paaf1.effective_start_date = L_DPL_REC.START_DATE
2553 and paaf1.assignment_status_type_id = past1.assignment_status_type_id
2554 and past1.per_system_status = 'SUSP_ASSIGN')
2555 and exists
2556 (select 1
2557 from per_all_assignments_f paaf2,
2558 per_assignment_status_types past2
2559 where paaf2.assignment_id = paaf.assignment_id
2560 and paaf2.effective_end_date = L_DPL_REC.START_DATE-1
2561 and paaf2.assignment_status_type_id = past2.assignment_status_type_id
2562 and past2.per_system_status = 'ACTIVE_ASSIGN');
2563 --
2564 begin
2565 hr_utility.set_location('Entering:'|| l_proc, 10);
2566 --
2567 -- Issue a savepoint
2568 --
2569 savepoint return_from_deployment;
2570 --
2571 -- Remember IN OUT parameter IN values
2572 --
2573 l_object_version_number := p_object_version_number;
2574 --
2575 -- Truncate the time portion from all IN date parameters
2576 --
2577 l_end_date_in := trunc(p_end_date);
2578 --
2579 -- Validation in addition to Row Handlers
2580 --
2581 open csr_person_deployment(p_person_deployment_id);
2582 fetch csr_person_deployment into l_dpl_rec;
2583 if csr_person_deployment%notfound then
2584 close csr_person_deployment;
2585 fnd_message.set_name('PER','HR_449609_DPL_NOT_EXIST');
2586 fnd_message.raise_error;
2587 else
2588 close csr_person_deployment;
2589 --
2590 hr_utility.set_location(l_proc,20);
2591 --
2592 end if;
2593 --
2594 if l_dpl_rec.status = 'DRAFT' then
2595 fnd_message.set_name('PER','HR_449624_PDT_DRAFT_NO_END');
2596 fnd_message.raise_error;
2597 elsif l_dpl_rec.status = 'COMPLETE' then
2598 fnd_message.set_name('PER','HR_449625_PDT_COMPLETE_NO_END');
2599 fnd_message.raise_error;
2600 end if;
2601 --
2602 if l_dpl_rec.start_date > nvl(l_end_date_in,l_dpl_rec.end_date) then
2603 fnd_message.set_name('PER','HR_449621_PDT_CHG_DATES');
2604 fnd_message.raise_error;
2605 end if;
2606 --
2607 if nvl(l_dpl_rec.permanent,'N') = 'Y' then
2608 fnd_message.set_name('PER','HR_449626_PDT_PERM_NO_END');
2609 fnd_message.raise_error;
2610 end if;
2611 --
2612 -- Process Logic
2613 --
2614 l_leaving_reason := p_leaving_reason;
2615 l_leaving_person_type_id := p_leaving_person_type_id;
2616 --
2617 if nvl(l_end_date_in,hr_api.g_date) = hr_api.g_date then
2618 hr_utility.set_location(l_proc,30);
2619 l_end_date_in := l_dpl_rec.end_date;
2620 end if;
2621 --
2622 if nvl(l_leaving_reason,hr_api.g_varchar2) = hr_api.g_varchar2 then
2623 hr_utility.set_location(l_proc,40);
2624 l_leaving_reason := l_dpl_rec.leaving_reason;
2625 end if;
2626 --
2627 if nvl(l_leaving_person_type_id,hr_api.g_number) = hr_api.g_number then
2628 hr_utility.set_location(l_proc,50);
2629 l_leaving_person_type_id := l_dpl_rec.leaving_person_type_id;
2630 end if;
2631
2632 hr_person_deployment_api.update_person_deployment
2633 (p_validate => p_validate
2634 ,p_person_deployment_id => p_person_deployment_id
2635 ,p_object_version_number => p_object_version_number
2636 ,p_end_date => l_end_date_in
2637 ,p_status => 'COMPLETE'
2638 ,p_leaving_reason => l_leaving_reason
2639 ,p_leaving_person_type_id => l_leaving_person_type_id
2640 ,p_policy_duration_warning => l_policy_duration_warning
2641 );
2642
2643 --
2644 -- Terminate the host employment
2645 --
2646 open csr_host_pds(l_dpl_rec.to_person_id);
2647 fetch csr_host_pds into l_host_pds_id,l_host_pds_ovn;
2648 close csr_host_pds;
2649 --
2650 hr_utility.set_location(l_proc,60);
2651 --
2652 hr_ex_employee_api.actual_termination_emp
2653 (p_validate => p_validate
2654 ,p_effective_date => l_end_date_in
2655 ,p_period_of_service_id => l_host_pds_id
2656 ,p_object_version_number => l_host_pds_ovn
2657 ,p_actual_termination_date => l_end_date_in
2658 ,p_person_type_id => l_leaving_person_type_id
2659 ,p_leaving_reason => l_leaving_reason
2660
2661 -- Changed for bug 5512320
2662 ,p_last_standard_process_date => null
2663 ,p_last_std_process_date_out => l_last_std_process_date_out
2664
2665 ,p_supervisor_warning => l_supervisor_warning
2666 ,p_event_warning => l_event_warning
2667 ,p_interview_warning => l_interview_warning
2668 ,p_review_warning => l_review_warning
2669 ,p_recruiter_warning => l_recruiter_warning
2670 ,p_asg_future_changes_warning => l_asg_future_changes_warning
2671 ,p_entries_changed_warning => l_entries_changed_warning
2672 ,p_pay_proposal_warning => l_pay_proposal_warning
2673 ,p_dod_warning => l_dod_warning
2674 );
2675 --
2676 -- Reactivate the suspended home assignments
2677 --
2678 for l_asg_rec in csr_susp_home_asgs(l_dpl_rec.from_person_id) loop
2679 --
2680 hr_utility.set_location(l_proc,70);
2681 hr_utility.set_location('assignment_id '||l_asg_rec.assignment_id,71);
2682 --
2683 hr_assignment_api.activate_emp_asg
2684 (p_validate => p_validate
2685 ,p_effective_date => l_end_date_in+1
2686 ,p_datetrack_update_mode => 'UPDATE'
2687 ,p_assignment_id => l_asg_rec.assignment_id
2688 ,p_change_reason => null
2689 ,p_object_version_number => l_asg_rec.object_version_number
2690 ,p_assignment_status_type_id => null --null causes it to be set to default
2691 ,p_effective_start_date => l_home_asg_esd
2692 ,p_effective_end_date => l_home_asg_eed
2693 );
2694 end loop;
2695 --
2696 -- update the proposal with the new details to keep it up to date
2697 --
2698 hr_utility.set_location(l_proc,80);
2699 --
2700
2701 --
2702 -- When in validation only mode raise the Validate_Enabled exception
2703 --
2704 if p_validate then
2705 raise hr_api.validate_enabled;
2706 end if;
2707 --
2708 -- Set all IN OUT and OUT parameters with out values
2709 --
2710 hr_utility.set_location(' Leaving:'||l_proc, 700);
2711 exception
2712 when hr_api.validate_enabled then
2713 --
2714 -- As the Validate_Enabled exception has been raised
2715 -- we must rollback to the savepoint
2716 --
2717 rollback to return_from_deployment;
2718 --
2719 -- Reset IN OUT parameters and set OUT parameters
2720 -- (Any key or derived arguments must be set to null
2721 -- when validation only mode is being used.)
2722 --
2723 p_object_version_number := l_object_version_number;
2724 hr_utility.set_location(' Leaving:'||l_proc, 800);
2725 when others then
2726 --
2727 -- A validation or unexpected error has occured
2728 --
2729 rollback to return_from_deployment;
2730 --
2731 -- Reset IN OUT parameters and set all
2732 -- OUT parameters, including warnings, to null
2733 --
2734 p_object_version_number := l_object_version_number;
2735 hr_utility.set_location(' Leaving:'||l_proc, 900);
2736 raise;
2737 end return_from_deployment;
2738 --
2739 end HR_PERSON_DEPLOYMENT_API;