[Home] [Help]
PACKAGE BODY: APPS.HR_PERSON_DEPLOYMENT_API
Source
1 Package Body HR_PERSON_DEPLOYMENT_API as
2 /* $Header: hrpdtapi.pkb 120.23 2007/11/22 08:30:43 ghshanka noship $ */
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 --
860 l_proc varchar2(72) := g_package||'initiate_deployment';
861 l_object_version_number hr_person_deployments.object_version_number%type;
862 --
863 -- Key data from per and asg
864 l_host_employee_number per_all_people_f.employee_number%type;
865 l_host_applicant_number per_all_people_f.applicant_number%type;
866 l_home_last_name per_all_people_f.last_name%type;
867 l_home_sex per_all_people_f.sex%type;
868 l_home_party_id per_all_people_f.party_id%type;
869 l_home_original_date_of_hire per_all_people_f.original_date_of_hire%type;
870 l_host_person_id number;
871 l_host_per_ovn number;
872 l_host_assignment_id number;
873 l_host_asg_ovn number;
874 l_host_asg_ovn1 number;
875 l_host_application_id number;
876 l_host_apl_ovn number;
877 l_host_per_esd date;
878 l_host_per_eed date;
879 l_host_asg_esd date;
880 l_host_asg_eed date;
881 l_host_per_full_name per_all_people_f.full_name%type;
882 l_home_asg_esd date;
883 l_home_asg_eed date;
884 l_host_person_extra_info_id number;
885 l_host_pei_ovn number;
886 l_contact_person_id number;
887 l_host_pyp_id number;
888 l_host_pyp_ovn number;
889 l_dummy_n number;
890
891 --variables for OUT and INOUT in API calls
892 l_cagr_grade_def_id number;
893 l_cagr_concatenated_segments varchar2(2000);
894 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
895 l_soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%TYPE;
896 l_comment_id per_all_assignments_f.comment_id%TYPE;
897 l_host_per_comment_id number;
898 l_host_asg_sequence number;
899 l_host_asg_number per_all_assignments_f.assignment_number%type;
900 l_assignment_status_type_id number;
901 l_special_ceiling_step_id number;
902 l_group_name pay_people_groups.group_name%TYPE;
903 l_host_contact_person_id number;
904 l_host_ctr_id number;
905 l_host_ctr_ovn number;
906 l_host_contact_per_ovn number;
907 l_host_contact_per_esd date;
908 l_host_contact_per_eed date;
909 l_host_contact_full_name per_all_people_f.full_name%type;
910 l_host_contact_per_comment_id number;
911 l_pyp_element_entry_id number;
912 --
913 l_name_combination_warning boolean;
914 l_assign_payroll_warning boolean;
915 l_orig_hire_warning boolean;
916 l_appl_override_warning boolean;
917 l_oversubscribed_vacancy_id number;
918 l_no_managers_warning boolean;
919 l_other_manager_warning boolean;
920 l_org_now_no_manager_warning boolean;
921 l_hourly_salaried_warning boolean;
922 l_gsp_post_process_warning varchar2(2000);
923 l_tax_district_changed_warning boolean;
924 l_entries_changed_warning varchar2(1);
925 l_spp_delete_warning boolean;
926 l_last_std_process_date_out date;
927 l_supervisor_warning boolean;
928 l_event_warning boolean;
929 l_interview_warning boolean;
930 l_review_warning boolean;
931 l_recruiter_warning boolean;
932 l_asg_future_changes_warning boolean;
933 l_pay_proposal_warning boolean;
934 l_dod_warning boolean;
935 l_inv_next_sal_date_warning boolean;
936 l_proposed_salary_warning boolean;
937 l_approved_warning boolean;
938 l_payroll_warning boolean;
939 --new variables declared
940 l_created_by per_all_assignments_f.created_by%TYPE;
941 l_creation_date per_all_assignments_f.creation_date%TYPE;
942 l_last_update_date per_all_assignments_f.last_update_date%TYPE;
943 l_last_updated_by per_all_assignments_f.last_updated_by%TYPE;
944 l_last_update_login per_all_assignments_f.last_update_login%TYPE;
945 l_payroll_id_updated BOOLEAN;
946 l_business_group_id hr_all_organization_units.organization_id%TYPE;
947 l_validation_start_date DATE;
948 l_validation_end_date DATE;
949 l_effective_start_date DATE;
950 l_effective_end_date DATE;
951 l_datetrack_update_mode varchar2(30);
952 --
953 --
954 --Warnings connected sepcifically with deployments
955 l_policy_duration_warning boolean;
956 l_already_applicant_warning boolean := false;
957 --
958 l_varray_d hr_dflex_utility.l_ignore_dfcode_varray
959 := hr_dflex_utility.l_ignore_dfcode_varray();
960 --
961 --Cursors and related variables
962 --
963 --
964 -- fix for bug 6593649
965 l_attachments varchar2(1);
966 cursor csr_get_attached_doc is
967 select null
968 from fnd_attached_documents
969 where PK1_VALUE =p_person_deployment_id
970 and ENTITY_NAME ='HR_PERSON_DEPLOYMENTS';
971 --
972 -- fix for bug 6593649
973 --
974 cursor csr_person_deployment(p_person_deployment_id number) is
975 select *
976 from hr_person_deployments dpl
977 where dpl.person_deployment_id = p_person_deployment_id;
978 --
979 l_dpl_rec csr_person_deployment%rowtype;
980 --
981 cursor csr_other_active_dpl(p_person_deployment_id number) is
982 select 1
983 from hr_person_deployments pdt1
984 where pdt1.person_deployment_id = p_person_deployment_id
985 and exists (select 1
986 from hr_person_deployments pdt2
987 where pdt2.person_deployment_id <> pdt1.person_deployment_id
988 and pdt2.from_person_id = pdt1.from_person_id
989 and pdt2.status in ('ACTIVE','COMPLETE')
990 and pdt1.start_date <= nvl(pdt2.end_date,hr_api.g_eot));
991 --
992 cursor csr_home_per_values(p_person_id number) is
993 select papf.last_name, papf.sex, papf.party_id, papf.original_date_of_hire
994 from per_all_people_f papf
995 where papf.person_id = p_person_id
996 and l_dpl_rec.start_date between
997 papf.effective_start_date and papf.effective_end_date;
998 --
999 cursor csr_host_per_values(p_person_id number) is
1000 select papf.object_version_number
1001 from per_all_people_f papf
1002 where papf.person_id = p_person_id
1003 and l_dpl_rec.start_date between
1004 papf.effective_start_date and papf.effective_end_date;
1005 --
1006 cursor csr_host_asg_ovn(p_assignment_id number) is
1007 select paaf.object_version_number
1008 from per_all_assignments_f paaf
1009 where paaf.assignment_id = p_assignment_id
1010 and l_dpl_rec.start_date between
1011 paaf.effective_start_date and paaf.effective_end_date;
1012 --
1013 cursor csr_active_home_asgs(p_person_id number) is
1014 select paaf.assignment_id, paaf.object_version_number
1015 from per_all_assignments_f paaf,
1016 per_assignment_status_types past
1017 where paaf.person_id = p_person_id
1018 and l_dpl_rec.start_date between
1019 paaf.effective_start_date and paaf.effective_end_date
1020 and paaf.assignment_type = 'E'
1021 and paaf.assignment_status_type_id = past.assignment_status_type_id
1022 and past.per_system_status = 'ACTIVE_ASSIGN';
1023 --
1024 cursor csr_home_pds_details(p_person_id number) is
1025 select pds.period_of_service_id, pds.object_version_number
1026 from per_periods_of_service pds
1027 where pds.person_id = p_person_id
1028 and l_dpl_rec.start_date >= pds.date_start
1029 and pds.actual_termination_date is null;
1030 --
1031 l_home_pds_id number;
1032 l_home_pds_ovn number;
1033 --
1034 cursor csr_dpl_contacts(p_person_deployment_id number) is
1035 select *
1036 from hr_person_deplymt_contacts pdc
1037 where pdc.person_deployment_id = p_person_deployment_id;
1038 --
1039 cursor csr_contact_rel_details(p_contact_relationship_id number) is
1040 select *
1041 from per_contact_relationships ctr
1042 where ctr.contact_relationship_id = p_contact_relationship_id;
1043 --
1044 l_contact_rel_details csr_contact_rel_details%rowtype;
1045 --
1046 cursor csr_contact_person_details(p_person_id number, p_effective_date date) is
1047 select *
1048 from per_all_people_f papf
1049 where papf.person_id = p_person_id
1050 and p_effective_date between
1051 papf.effective_start_date and papf.effective_end_date;
1052 --
1053 l_contact_person_details csr_contact_person_details%rowtype;
1054 l_contact_created t_contact_created;
1055 l_contacts_created t_contacts_created := t_contacts_created();
1056 l_index_number number;
1057 --
1058 cursor csr_dpl_eits(p_person_deployment_id number) is
1059 select *
1060 from hr_person_deplymt_eits pde
1061 where pde.person_deployment_id = p_person_deployment_id;
1062 --
1063 cursor csr_eit_details(p_person_extra_info_id number) is
1064 select *
1065 from per_people_extra_info pei
1066 where pei.person_extra_info_id = p_person_extra_info_id;
1067 --
1068 l_eit_details csr_eit_details%rowtype;
1069 --
1070 /* cursor csr_direct_reports(p_person_id number, p_effective_date date) is
1071 select asg.assignment_id, asg.effective_start_date
1072 from per_all_assignments_f asg
1073 where asg.supervisor_id = p_person_id
1074 and asg.effective_end_date > p_effective_date;*/
1075 --
1076 cursor csr_direct_reports(p_person_id number, p_start_date date) is
1077 select *
1078 from per_all_assignments_f asg
1079 where asg.supervisor_id = p_person_id
1080 and p_start_date between asg.effective_start_date and asg.effective_end_date;
1081
1082 ------------------
1083 cursor csr_fut_dt_rows(p_person_id number, p_start_date date) is
1084 select asg.assignment_id, asg.effective_start_date,asg.effective_end_date
1085 from per_all_assignments_f asg
1086 where asg.supervisor_id = p_person_id
1087 and asg.effective_start_date > p_start_date;
1088 -----
1089
1090 -- local variables for people group
1091 l_group_id number :=null;
1092 l_pgp_segment1 varchar2(60):=hr_api.g_varchar2;
1093 l_pgp_segment2 varchar2(60):=hr_api.g_varchar2;
1094 l_pgp_segment3 varchar2(60):=hr_api.g_varchar2;
1095 l_pgp_segment4 varchar2(60):=hr_api.g_varchar2;
1096 l_pgp_segment5 varchar2(60):=hr_api.g_varchar2;
1097 l_pgp_segment6 varchar2(60):=hr_api.g_varchar2;
1098 l_pgp_segment7 varchar2(60):=hr_api.g_varchar2;
1099 l_pgp_segment8 varchar2(60):=hr_api.g_varchar2;
1100 l_pgp_segment9 varchar2(60):=hr_api.g_varchar2;
1101 l_pgp_segment10 varchar2(60):=hr_api.g_varchar2;
1102 l_pgp_segment11 varchar2(60):=hr_api.g_varchar2;
1103 l_pgp_segment12 varchar2(60):=hr_api.g_varchar2;
1104 l_pgp_segment13 varchar2(60):=hr_api.g_varchar2;
1105 l_pgp_segment14 varchar2(60):=hr_api.g_varchar2;
1106 l_pgp_segment15 varchar2(60):=hr_api.g_varchar2;
1107 l_pgp_segment16 varchar2(60):=hr_api.g_varchar2;
1108 l_pgp_segment17 varchar2(60):=hr_api.g_varchar2;
1109 l_pgp_segment18 varchar2(60):=hr_api.g_varchar2;
1110 l_pgp_segment19 varchar2(60):=hr_api.g_varchar2;
1111 l_pgp_segment20 varchar2(60):=hr_api.g_varchar2;
1112 l_pgp_segment21 varchar2(60):=hr_api.g_varchar2;
1113 l_pgp_segment22 varchar2(60):=hr_api.g_varchar2;
1114 l_pgp_segment23 varchar2(60):=hr_api.g_varchar2;
1115 l_pgp_segment24 varchar2(60):=hr_api.g_varchar2;
1116 l_pgp_segment25 varchar2(60):=hr_api.g_varchar2;
1117 l_pgp_segment26 varchar2(60):=hr_api.g_varchar2;
1118 l_pgp_segment27 varchar2(60):=hr_api.g_varchar2;
1119 l_pgp_segment28 varchar2(60):=hr_api.g_varchar2;
1120 l_pgp_segment29 varchar2(60):=hr_api.g_varchar2;
1121 l_pgp_segment30 varchar2(60):=hr_api.g_varchar2;
1122
1123 cursor c_pgp_segments (cur_p_people_group_id number) is
1124 select segment1,
1125 segment2,
1126 segment3,
1127 segment4,
1128 segment5,
1129 segment6,
1130 segment7,
1131 segment8,
1132 segment9,
1133 segment10,
1134 segment11,
1135 segment12,
1136 segment13,
1137 segment14,
1138 segment15,
1139 segment16,
1140 segment17,
1141 segment18,
1142 segment19,
1143 segment20,
1144 segment21,
1145 segment22,
1146 segment23,
1147 segment24,
1148 segment25,
1149 segment26,
1150 segment27,
1151 segment28,
1152 segment29,
1153 segment30
1154 from pay_people_groups
1155 where people_group_id = cur_p_people_group_id;
1156 -- for people group fields population.
1157
1158 begin
1159 hr_utility.set_location('Entering:'|| l_proc, 10);
1160 --
1161 -- Issue a savepoint
1162 --
1163 savepoint initiate_deployment;
1164 --
1165 -- Remember IN OUT parameter IN values
1166 --
1167 l_object_version_number := p_object_version_number;
1168 --
1169 -- Truncate the time portion from all IN date parameters
1170 --
1171 --
1172 -- Validation in addition to Row Handlers
1173 --
1174 open csr_person_deployment(p_person_deployment_id);
1175 fetch csr_person_deployment into l_dpl_rec;
1176 if csr_person_deployment%notfound then
1177 close csr_person_deployment;
1178 fnd_message.set_name('PER','HR_449609_DPL_NOT_EXIST');
1179 fnd_message.raise_error;
1180 else
1181 close csr_person_deployment;
1182 --
1183 hr_utility.set_location(l_proc,30);
1184 --
1185 end if;
1186 --
1187 if l_dpl_rec.status <> 'DRAFT' then
1188 fnd_message.set_name('PER','HR_449614_PDT_ALREADY_INIT');
1189 fnd_message.raise_error;
1190 end if;
1191 --
1192 open csr_other_active_dpl(p_person_deployment_id);
1193 fetch csr_other_active_dpl into l_dummy_n;
1194 if csr_other_active_dpl%found then
1195 close csr_other_active_dpl;
1196 fnd_message.set_name('PER','HR_449615_PDT_OTHER_ACTIVE');
1197 fnd_message.raise_error;
1198 else
1199 close csr_other_active_dpl;
1200 end if;
1201 --
1202 -- Set up the arrays for bypassing flex validation for information that does
1203 -- not exist in the deployment proposal
1204 --
1205 l_varray_d.delete;
1206 l_varray_d.extend(8);
1207 l_varray_d(1):='PER_ASSIGNMENTS';
1208 l_varray_d(2):='PER_PAY_PROPOSALS';
1209 l_varray_d(3):='PER_CONTACTS';
1210 l_varray_d(4):='Contact Relship Developer DF';
1211 l_varray_d(5):='PER_PEOPLE_EXTRA_INFO';
1212 l_varray_d(6):='PER_PERIODS_OF_SERVICE';
1213 l_varray_d(7):='PER_PDS_DEVELOPER_DF';
1214 --
1215 -- Added for bug 5491169
1216 l_varray_d(8):='PER_PEOPLE';
1217
1218 hr_dflex_utility.create_ignore_df_validation(p_rec=>l_varray_d);
1219 --
1220 hr_utility.set_location(l_proc,20);
1221 --
1222 -- Process Logic
1223 --
1224 --
1225 -- Store parameter values which may be changed by the API calls
1226 --
1227 l_host_employee_number := l_dpl_rec.employee_number;
1228 --
1229 open csr_home_per_values(l_dpl_rec.from_person_id);
1230 fetch csr_home_per_values into l_home_last_name,l_home_sex,l_home_party_id,
1231 l_home_original_date_of_hire;
1232 close csr_home_per_values;
1233 --
1234 hr_utility.set_location(l_proc,40);
1235 --
1236 -- Set the global transfer in process variable used by person rowhandler validation
1237 --
1238 per_per_bus.g_global_transfer_in_process := true;
1239 --
1240 -- Start of termination or update to the Home BG records
1241 --
1242 if nvl(l_dpl_rec.permanent,'N') = 'Y' then
1243 --
1244 hr_utility.set_location(l_proc,170);
1245 --
1246 -- termination of home employee record at (l_dpl_rec.start_date-1)
1247 --
1248
1249 fnd_profile.put(name => 'HR_PROPAGATE_DATA_CHANGES'
1250 ,val => 'N');
1251
1252 open csr_home_pds_details(l_dpl_rec.from_person_id);
1253 fetch csr_home_pds_details into l_home_pds_id, l_home_pds_ovn;
1254 close csr_home_pds_details;
1255 --
1256 hr_ex_employee_api.actual_termination_emp
1257 (p_validate => p_validate
1258 ,p_effective_date => l_dpl_rec.start_date-1
1259 ,p_period_of_service_id => l_home_pds_id
1260 ,p_object_version_number => l_home_pds_ovn
1261 ,p_actual_termination_date => l_dpl_rec.start_date-1
1262 ,p_last_standard_process_date => null
1263 ,p_last_std_process_date_out => l_last_std_process_date_out
1264 ,p_supervisor_warning => l_supervisor_warning
1265 ,p_event_warning => l_event_warning
1266 ,p_interview_warning => l_interview_warning
1267 ,p_review_warning => l_review_warning
1268 ,p_recruiter_warning => l_recruiter_warning
1269 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1270 ,p_entries_changed_warning => l_entries_changed_warning
1271 ,p_pay_proposal_warning => l_pay_proposal_warning
1272 ,p_dod_warning => l_dod_warning
1273 );
1274
1275 fnd_profile.put(name => 'HR_PROPAGATE_DATA_CHANGES'
1276 ,val => 'Y');
1277 else
1278 --
1279 hr_utility.set_location(l_proc,160);
1280 --
1281 -- suspend the active home assignments, leaving the others.
1282 --
1283 for l_home_asg_rec in csr_active_home_asgs(l_dpl_rec.from_person_id) loop
1284 hr_assignment_api.suspend_emp_asg
1285 (p_validate => p_validate
1286 ,p_effective_date => l_dpl_rec.start_date
1287 ,p_datetrack_update_mode => 'UPDATE'
1288 ,p_assignment_id => l_home_asg_rec.assignment_id
1289 ,p_object_version_number => l_home_asg_rec.object_version_number
1290 ,p_effective_start_date => l_home_asg_esd
1291 ,p_effective_end_date => l_home_asg_eed
1292 );
1293 end loop;
1294 end if;
1295 --
1296 -- If retain_direct_reports was set, then need to update supervisor_id column for
1297 -- the direct reports to point to the l_host_person_id
1298 -- Do direct sql because there may be future updates to the records, and we do not
1299 -- want this to cause whole initiation to fail.
1300 --Moved this logic at later stage
1301 /* if nvl(l_dpl_rec.retain_direct_reports,'N') = 'Y' then
1302 for l_reports in csr_direct_reports
1303 (l_dpl_rec.from_person_id,l_dpl_rec.start_date) loop
1304 update per_all_assignments_f
1305 set supervisor_id = l_host_person_id,
1306 supervisor_assignment_id =
1307 decode(supervisor_assignment_id,null,null,l_host_assignment_id),
1308 object_version_number = object_version_number+1
1309 where assignment_id = l_reports.assignment_id
1310 and effective_start_date = l_reports.effective_start_date;
1311 end loop;
1312 end if;*/
1313 --
1314 -- End of termination or update to the Home BG records
1315 --
1316 --
1317 -- Start of create or update person section
1318 --
1319 if l_dpl_rec.to_person_id is null then --creating new emp in host BG
1320 --
1321 hr_utility.set_location(l_proc,50);
1322 --
1323 hr_employee_api.create_employee
1324 (p_validate => p_validate
1325 ,p_hire_date => l_dpl_rec.start_date
1326 ,p_business_group_id => l_dpl_rec.to_business_group_id
1327 ,p_last_name => l_home_last_name
1328 ,p_sex => l_home_sex
1329 ,p_person_type_id => l_dpl_rec.person_type_id
1330 ,p_per_information_category => l_dpl_rec.per_information_category
1331 ,p_per_information1 => l_dpl_rec.per_information1
1332 ,p_per_information2 => l_dpl_rec.per_information2
1333 ,p_per_information3 => l_dpl_rec.per_information3
1334 ,p_per_information4 => l_dpl_rec.per_information4
1335 ,p_per_information5 => l_dpl_rec.per_information5
1336 ,p_per_information6 => l_dpl_rec.per_information6
1337 ,p_per_information7 => l_dpl_rec.per_information7
1338 ,p_per_information8 => l_dpl_rec.per_information8
1339 ,p_per_information9 => l_dpl_rec.per_information9
1340 ,p_per_information10 => l_dpl_rec.per_information10
1341 ,p_per_information11 => l_dpl_rec.per_information11
1342 ,p_per_information12 => l_dpl_rec.per_information12
1343 ,p_per_information13 => l_dpl_rec.per_information13
1344 ,p_per_information14 => l_dpl_rec.per_information14
1345 ,p_per_information15 => l_dpl_rec.per_information15
1346 ,p_per_information16 => l_dpl_rec.per_information16
1347 ,p_per_information17 => l_dpl_rec.per_information17
1348 ,p_per_information18 => l_dpl_rec.per_information18
1349 ,p_per_information19 => l_dpl_rec.per_information19
1350 ,p_per_information20 => l_dpl_rec.per_information20
1351 ,p_per_information21 => l_dpl_rec.per_information21
1352 ,p_per_information22 => l_dpl_rec.per_information22
1353 ,p_per_information23 => l_dpl_rec.per_information23
1354 ,p_per_information24 => l_dpl_rec.per_information24
1355 ,p_per_information25 => l_dpl_rec.per_information25
1356 ,p_per_information26 => l_dpl_rec.per_information26
1357 ,p_per_information27 => l_dpl_rec.per_information27
1358 ,p_per_information28 => l_dpl_rec.per_information28
1359 ,p_per_information29 => l_dpl_rec.per_information29
1360 ,p_per_information30 => l_dpl_rec.per_information30
1361 ,p_original_date_of_hire => l_home_original_date_of_hire
1362 ,p_adjusted_svc_date => l_home_original_date_of_hire
1363 ,p_party_id => l_home_party_id
1364 ,p_employee_number => l_host_employee_number
1365 ,p_person_id => l_host_person_id
1366 ,p_assignment_id => l_host_assignment_id
1367 ,p_per_object_version_number => l_host_per_ovn
1368 ,p_asg_object_version_number => l_host_asg_ovn
1369 ,p_per_effective_start_date => l_host_per_esd
1370 ,p_per_effective_end_date => l_host_per_eed
1371 ,p_full_name => l_host_per_full_name
1372 ,p_per_comment_id => l_host_per_comment_id
1373 ,p_assignment_sequence => l_host_asg_sequence
1374 ,p_assignment_number => l_host_asg_number
1375 ,p_name_combination_warning => l_name_combination_warning
1376 ,p_assign_payroll_warning => l_assign_payroll_warning
1377 ,p_orig_hire_warning => l_orig_hire_warning
1378 );
1379 elsif hr_person_type_usage_info.is_person_of_type
1380 (p_effective_date => l_dpl_rec.start_date
1381 ,p_person_id => l_dpl_rec.to_person_id
1382 ,p_system_person_type => 'EMP') then
1383 --already emp so raise error
1384 fnd_message.set_name('PER','HR_449616_PDT_ALREADY_EMP');
1385 fnd_message.raise_error;
1386
1387 elsif hr_person_type_usage_info.is_person_of_type
1388 (p_effective_date => l_dpl_rec.start_date
1389 ,p_person_id => l_dpl_rec.to_person_id
1390 ,p_system_person_type => 'CWK') then
1391 --already cwk so raise error
1392 fnd_message.set_name('PER','HR_449617_PDT_ALREADY_CWK');
1393 fnd_message.raise_error;
1394
1395 elsif hr_person_type_usage_info.is_person_of_type
1396 (p_effective_date => l_dpl_rec.start_date
1397 ,p_person_id => l_dpl_rec.to_person_id
1398 ,p_system_person_type => 'APL') then
1399 --error out in this case, there is no way to create an employee record from here
1400 fnd_message.set_name('PER','HR_449618_PDT_ALREADY_APL');
1401 fnd_message.raise_error;
1402 --
1403 else --we should have the all clear to hire existing person
1404 --
1405 hr_utility.set_location(l_proc,70);
1406 --
1407 l_host_person_id := l_dpl_rec.to_person_id;
1408 --
1409 open csr_host_per_values(l_dpl_rec.to_person_id);
1410 fetch csr_host_per_values into l_host_per_ovn;
1411 close csr_host_per_values;
1412 --
1413 hr_employee_api.hire_into_job
1414 (p_validate => p_validate
1415 ,p_effective_date => l_dpl_rec.start_date
1416 ,p_person_id => l_host_person_id
1417 ,p_object_version_number => l_host_per_ovn
1418 ,p_employee_number => l_host_employee_number
1419 -- ,p_datetrack_update_mode => 'UPDATE'
1420 ,p_person_type_id => l_dpl_rec.person_type_id
1421 ,p_national_identifier => null
1422 ,p_per_information7 => l_dpl_rec.per_information7
1423 ,p_assignment_id => l_host_assignment_id
1424 ,p_effective_start_date => l_host_per_esd
1425 ,p_effective_end_date => l_host_per_eed
1426 ,p_assign_payroll_warning => l_assign_payroll_warning
1427 ,p_orig_hire_warning => l_orig_hire_warning
1428 );
1429 --
1430 open csr_host_asg_ovn(l_host_assignment_id);
1431 fetch csr_host_asg_ovn into l_host_asg_ovn;
1432 close csr_host_asg_ovn;
1433 --
1434 end if;
1435 --
1436 hr_utility.set_location('host person id '||l_host_person_id,77);
1437 --
1438 --Code moved here
1439 if nvl(l_dpl_rec.retain_direct_reports,'N') = 'Y' then
1440
1441 --fetch the reportees
1442 for l_reports in csr_direct_reports(l_dpl_rec.from_person_id,l_dpl_rec.start_date)
1443 loop
1444 --Check for the update mode
1445 if l_dpl_rec.start_date=l_reports.effective_start_date then
1446 l_datetrack_update_mode:='CORRECTION';
1447
1448 elsif ((l_reports.effective_end_date <> hr_api.g_eot)and
1449 (l_reports.effective_end_date >l_dpl_rec.start_date) ) then
1450 l_datetrack_update_mode:='UPDATE_CHANGE_INSERT';
1451
1452 elsif (l_reports.effective_end_date= hr_api.g_eot) then
1453 l_datetrack_update_mode:='UPDATE';
1454
1455 end if;
1456
1457
1458 if l_datetrack_update_mode = 'CORRECTION' then
1459
1460 update per_all_assignments_f
1461 set supervisor_id = l_host_person_id,
1462 supervisor_assignment_id =
1463 decode(supervisor_assignment_id,null,null,l_host_assignment_id),
1464 object_version_number = object_version_number+1
1465 where assignment_id = l_reports.assignment_id
1466 and effective_start_date = l_reports.effective_start_date;
1467
1468 elsif l_datetrack_update_mode ='UPDATE_CHANGE_INSERT' then
1469
1470 per_asg_upd.upd
1471 (p_assignment_id => l_reports.assignment_id
1472 ,p_effective_start_date => l_effective_start_date --l_effective_start_date
1473 ,p_effective_end_date => l_effective_end_date
1474 ,p_business_group_id => l_business_group_id
1475 ,p_assignment_status_type_id => l_reports.assignment_status_type_id
1476 ,p_assignment_type => l_reports.assignment_type --modified
1477 ,p_supervisor_id => l_host_person_id --modified
1478 ,p_supervisor_assignment_id => l_host_assignment_id --modified
1479 ,p_primary_flag => l_reports.primary_flag
1480 ,p_period_of_service_id => l_reports.period_of_service_id
1481 ,p_comment_id => l_comment_id
1482 ,p_object_version_number => l_reports.object_version_number
1483 ,p_payroll_id_updated => l_payroll_id_updated
1484 ,p_other_manager_warning => l_other_manager_warning
1485 ,p_no_managers_warning => l_no_managers_warning
1486 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1487 ,p_validation_start_date => l_validation_start_date
1488 ,p_validation_end_date => l_validation_end_date
1489 ,p_effective_date => l_dpl_rec.start_date
1490 ,p_datetrack_mode => 'UPDATE_CHANGE_INSERT'
1491 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1492 );
1493
1494 elsif l_datetrack_update_mode='UPDATE' then
1495
1496 per_asg_upd.upd
1497 (p_assignment_id => l_reports.assignment_id
1498 ,p_effective_start_date => l_effective_start_date --l_effective_start_date
1499 ,p_effective_end_date => l_effective_end_date
1500 ,p_business_group_id => l_business_group_id
1501 ,p_assignment_status_type_id => l_reports.assignment_status_type_id
1502 ,p_assignment_type => l_reports.assignment_type --modified
1503 ,p_supervisor_id => l_host_person_id --modified
1504 ,p_supervisor_assignment_id => l_host_assignment_id --modified
1505 ,p_primary_flag => l_reports.primary_flag
1506 ,p_period_of_service_id => l_reports.period_of_service_id
1507 ,p_comment_id => l_comment_id
1508 ,p_object_version_number => l_reports.object_version_number
1509 ,p_payroll_id_updated => l_payroll_id_updated
1510 ,p_other_manager_warning => l_other_manager_warning
1511 ,p_no_managers_warning => l_no_managers_warning
1512 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1513 ,p_validation_start_date => l_validation_start_date
1514 ,p_validation_end_date => l_validation_end_date
1515 ,p_effective_date => l_dpl_rec.start_date
1516 ,p_datetrack_mode => 'UPDATE'
1517 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1518 );
1519 end if;
1520 end loop;
1521 end if;
1522
1523 --now write code to handle future dt rows
1524 for l_fut_dt_rows in csr_fut_dt_rows(l_dpl_rec.from_person_id,l_dpl_rec.start_date)
1525 loop
1526 update per_all_assignments_f
1527 set supervisor_id = l_host_person_id,
1528 supervisor_assignment_id =
1529 decode(supervisor_assignment_id,null,null,l_host_assignment_id),
1530 object_version_number = object_version_number+1
1531 where assignment_id = l_fut_dt_rows.assignment_id
1532 and effective_start_date = l_fut_dt_rows.effective_start_date;
1533 end loop;
1534 --
1535 -- End of create or update person section
1536 --
1537 -- Start of update new host assignment section
1538 --
1539 l_soft_coding_keyflex_id := l_dpl_rec.soft_coding_keyflex_id;
1540 --
1541 --
1542 hr_utility.set_location(l_proc,80);
1543 --
1544 hr_assignment_api.update_emp_asg
1545 (p_validate => p_validate
1546 ,p_effective_date => l_dpl_rec.start_date
1547 ,p_datetrack_update_mode => 'CORRECTION'
1548 ,p_assignment_id => l_host_assignment_id
1549 ,p_object_version_number => l_host_asg_ovn
1550 ,p_supervisor_id => l_dpl_rec.supervisor_id
1551 ,p_assignment_number => l_host_asg_number
1552 ,p_change_reason => l_dpl_rec.ass_status_change_reason
1553 -- ,p_assignment_status_type_id => l_dpl_rec.assignment_status_type_id
1554 ,p_assignment_status_type_id =>
1555 nvl(l_dpl_rec.assignment_status_type_id,hr_api.g_number)
1556 ,p_comments => null
1557 ,p_date_probation_end => hr_api.g_date
1558 ,p_default_code_comb_id => hr_api.g_number
1559 ,p_frequency => hr_api.g_varchar2
1560 ,p_internal_address_line => hr_api.g_varchar2
1561 ,p_manager_flag => hr_api.g_varchar2
1562 ,p_normal_hours => hr_api.g_number
1563 ,p_perf_review_period => hr_api.g_number
1564 ,p_perf_review_period_frequency => hr_api.g_varchar2
1565 ,p_projected_assignment_end => l_dpl_rec.end_date
1566 ,p_probation_period => hr_api.g_number
1567 ,p_probation_unit => hr_api.g_varchar2
1568 ,p_sal_review_period => hr_api.g_number
1569 ,p_sal_review_period_frequency => hr_api.g_varchar2
1570 ,p_set_of_books_id => hr_api.g_number
1571 ,p_source_type => hr_api.g_varchar2
1572 ,p_time_normal_finish => hr_api.g_varchar2
1573 ,p_time_normal_start => hr_api.g_varchar2
1574 ,p_bargaining_unit_code => hr_api.g_varchar2
1575 ,p_labour_union_member_flag => hr_api.g_varchar2
1576 ,p_hourly_salaried_code => hr_api.g_varchar2
1577 ,p_ass_attribute_category => hr_api.g_varchar2
1578 ,p_ass_attribute1 => hr_api.g_varchar2
1579 ,p_ass_attribute2 => hr_api.g_varchar2
1580 ,p_ass_attribute3 => hr_api.g_varchar2
1581 ,p_ass_attribute4 => hr_api.g_varchar2
1582 ,p_ass_attribute5 => hr_api.g_varchar2
1583 ,p_ass_attribute6 => hr_api.g_varchar2
1584 ,p_ass_attribute7 => hr_api.g_varchar2
1585 ,p_ass_attribute8 => hr_api.g_varchar2
1586 ,p_ass_attribute9 => hr_api.g_varchar2
1587 ,p_ass_attribute10 => hr_api.g_varchar2
1588 ,p_ass_attribute11 => hr_api.g_varchar2
1589 ,p_ass_attribute12 => hr_api.g_varchar2
1590 ,p_ass_attribute13 => hr_api.g_varchar2
1591 ,p_ass_attribute14 => hr_api.g_varchar2
1592 ,p_ass_attribute15 => hr_api.g_varchar2
1593 ,p_ass_attribute16 => hr_api.g_varchar2
1594 ,p_ass_attribute17 => hr_api.g_varchar2
1595 ,p_ass_attribute18 => hr_api.g_varchar2
1596 ,p_ass_attribute19 => hr_api.g_varchar2
1597 ,p_ass_attribute20 => hr_api.g_varchar2
1598 ,p_ass_attribute21 => hr_api.g_varchar2
1599 ,p_ass_attribute22 => hr_api.g_varchar2
1600 ,p_ass_attribute23 => hr_api.g_varchar2
1601 ,p_ass_attribute24 => hr_api.g_varchar2
1602 ,p_ass_attribute25 => hr_api.g_varchar2
1603 ,p_ass_attribute26 => hr_api.g_varchar2
1604 ,p_ass_attribute27 => hr_api.g_varchar2
1605 ,p_ass_attribute28 => hr_api.g_varchar2
1606 ,p_ass_attribute29 => hr_api.g_varchar2
1607 ,p_ass_attribute30 => hr_api.g_varchar2
1608 ,p_title => hr_api.g_varchar2
1609 ,p_segment1 => hr_api.g_varchar2
1610 ,p_segment2 => hr_api.g_varchar2
1611 ,p_segment3 => hr_api.g_varchar2
1612 ,p_segment4 => hr_api.g_varchar2
1613 ,p_segment5 => hr_api.g_varchar2
1614 ,p_segment6 => hr_api.g_varchar2
1615 ,p_segment7 => hr_api.g_varchar2
1616 ,p_segment8 => hr_api.g_varchar2
1617 ,p_segment9 => hr_api.g_varchar2
1618 ,p_segment10 => hr_api.g_varchar2
1619 ,p_segment11 => hr_api.g_varchar2
1620 ,p_segment12 => hr_api.g_varchar2
1621 ,p_segment13 => hr_api.g_varchar2
1622 ,p_segment14 => hr_api.g_varchar2
1623 ,p_segment15 => hr_api.g_varchar2
1624 ,p_segment16 => hr_api.g_varchar2
1625 ,p_segment17 => hr_api.g_varchar2
1626 ,p_segment18 => hr_api.g_varchar2
1627 ,p_segment19 => hr_api.g_varchar2
1628 ,p_segment20 => hr_api.g_varchar2
1629 ,p_segment21 => hr_api.g_varchar2
1630 ,p_segment22 => hr_api.g_varchar2
1631 ,p_segment23 => hr_api.g_varchar2
1632 ,p_segment24 => hr_api.g_varchar2
1633 ,p_segment25 => hr_api.g_varchar2
1634 ,p_segment26 => hr_api.g_varchar2
1635 ,p_segment27 => hr_api.g_varchar2
1636 ,p_segment28 => hr_api.g_varchar2
1637 ,p_segment29 => hr_api.g_varchar2
1638 ,p_segment30 => hr_api.g_varchar2
1639 ,p_concat_segments => hr_api.g_varchar2
1640 ,p_contract_id => hr_api.g_number
1641 ,p_establishment_id => hr_api.g_number
1642 ,p_collective_agreement_id => hr_api.g_number
1643 ,p_cagr_id_flex_num => hr_api.g_number
1644 ,p_cag_segment1 => hr_api.g_varchar2
1645 ,p_cag_segment2 => hr_api.g_varchar2
1646 ,p_cag_segment3 => hr_api.g_varchar2
1647 ,p_cag_segment4 => hr_api.g_varchar2
1648 ,p_cag_segment5 => hr_api.g_varchar2
1649 ,p_cag_segment6 => hr_api.g_varchar2
1650 ,p_cag_segment7 => hr_api.g_varchar2
1651 ,p_cag_segment8 => hr_api.g_varchar2
1652 ,p_cag_segment9 => hr_api.g_varchar2
1653 ,p_cag_segment10 => hr_api.g_varchar2
1654 ,p_cag_segment11 => hr_api.g_varchar2
1655 ,p_cag_segment12 => hr_api.g_varchar2
1656 ,p_cag_segment13 => hr_api.g_varchar2
1657 ,p_cag_segment14 => hr_api.g_varchar2
1658 ,p_cag_segment15 => hr_api.g_varchar2
1659 ,p_cag_segment16 => hr_api.g_varchar2
1660 ,p_cag_segment17 => hr_api.g_varchar2
1661 ,p_cag_segment18 => hr_api.g_varchar2
1662 ,p_cag_segment19 => hr_api.g_varchar2
1663 ,p_cag_segment20 => hr_api.g_varchar2
1664 ,p_notice_period => hr_api.g_number
1665 ,p_notice_period_uom => hr_api.g_varchar2
1666 ,p_employee_category => hr_api.g_varchar2
1667 ,p_work_at_home => hr_api.g_varchar2
1668 ,p_job_post_source_name => hr_api.g_varchar2
1669 ,p_supervisor_assignment_id => l_dpl_rec.supervisor_assignment_id
1670 ,p_cagr_grade_def_id => l_cagr_grade_def_id
1671 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
1672 ,p_concatenated_segments => l_concatenated_segments
1673 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1674 ,p_comment_id => l_comment_id
1675 ,p_effective_start_date => l_host_asg_esd
1676 ,p_effective_end_date => l_host_asg_eed
1677 ,p_no_managers_warning => l_no_managers_warning
1678 ,p_other_manager_warning => l_other_manager_warning
1679 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1680 ,p_gsp_post_process_warning => l_gsp_post_process_warning
1681 );
1682 --
1683 hr_utility.set_location(l_proc,90);
1684 --
1685 -- sturlapa start
1686
1687 if l_dpl_rec.people_group_id is not null then
1688
1689 open c_pgp_segments(l_dpl_rec.people_group_id);
1690
1691 fetch c_pgp_segments into l_pgp_segment1,
1692 l_pgp_segment2,
1693 l_pgp_segment3,
1694 l_pgp_segment4,
1695 l_pgp_segment5,
1696 l_pgp_segment6,
1697 l_pgp_segment7,
1698 l_pgp_segment8,
1699 l_pgp_segment9,
1700 l_pgp_segment10,
1701 l_pgp_segment11,
1702 l_pgp_segment12,
1703 l_pgp_segment13,
1704 l_pgp_segment14,
1705 l_pgp_segment15,
1706 l_pgp_segment16,
1707 l_pgp_segment17,
1708 l_pgp_segment18,
1709 l_pgp_segment19,
1710 l_pgp_segment20,
1711 l_pgp_segment21,
1712 l_pgp_segment22,
1713 l_pgp_segment23,
1714 l_pgp_segment24,
1715 l_pgp_segment25,
1716 l_pgp_segment26,
1717 l_pgp_segment27,
1718 l_pgp_segment28,
1719 l_pgp_segment29,
1720 l_pgp_segment30;
1721
1722 close c_pgp_segments;
1723 end if;
1724
1725 /**
1726 * pass the people group id as null then internally it is trying to pull
1727 * existing ccid with concatatnated list.
1728 */
1729 hr_assignment_api.update_emp_asg_criteria
1730 (p_validate => p_validate
1731 ,p_effective_date => l_dpl_rec.start_date
1732 ,p_datetrack_update_mode => 'CORRECTION'
1733 ,p_assignment_id => l_host_assignment_id
1734 ,p_called_from_mass_update => false
1735 ,p_grade_id => l_dpl_rec.grade_id
1736 ,p_position_id => l_dpl_rec.position_id
1737 ,p_job_id => l_dpl_rec.job_id
1738 ,p_payroll_id => l_dpl_rec.payroll_id
1739 ,p_location_id => l_dpl_rec.location_id
1740 ,p_organization_id => l_dpl_rec.organization_id
1741 ,p_pay_basis_id => l_dpl_rec.pay_basis_id
1742 ,p_segment1 => l_pgp_segment1 --hr_api.g_varchar2
1743 ,p_segment2 => l_pgp_segment2 --hr_api.g_varchar2
1744 ,p_segment3 => l_pgp_segment3 --hr_api.g_varchar2
1745 ,p_segment4 => l_pgp_segment4 --hr_api.g_varchar2
1746 ,p_segment5 => l_pgp_segment5 --hr_api.g_varchar2
1747 ,p_segment6 => l_pgp_segment6 --hr_api.g_varchar2
1748 ,p_segment7 => l_pgp_segment7 --hr_api.g_varchar2
1749 ,p_segment8 => l_pgp_segment8 --hr_api.g_varchar2
1750 ,p_segment9 => l_pgp_segment9 --hr_api.g_varchar2
1751 ,p_segment10 => l_pgp_segment10 --hr_api.g_varchar2
1752 ,p_segment11 => l_pgp_segment11 --hr_api.g_varchar2
1753 ,p_segment12 => l_pgp_segment12 --hr_api.g_varchar2
1754 ,p_segment13 => l_pgp_segment13 --hr_api.g_varchar2
1755 ,p_segment14 => l_pgp_segment14 --hr_api.g_varchar2
1756 ,p_segment15 => l_pgp_segment15 --hr_api.g_varchar2
1757 ,p_segment16 => l_pgp_segment16 --hr_api.g_varchar2
1758 ,p_segment17 => l_pgp_segment17 --hr_api.g_varchar2
1759 ,p_segment18 => l_pgp_segment18 --hr_api.g_varchar2
1760 ,p_segment19 => l_pgp_segment19 --hr_api.g_varchar2
1761 ,p_segment20 => l_pgp_segment20 --hr_api.g_varchar2
1762 ,p_segment21 => l_pgp_segment21 --hr_api.g_varchar2
1763 ,p_segment22 => l_pgp_segment22 --hr_api.g_varchar2
1764 ,p_segment23 => l_pgp_segment23 --hr_api.g_varchar2
1765 ,p_segment24 => l_pgp_segment24 --hr_api.g_varchar2
1766 ,p_segment25 => l_pgp_segment25 --hr_api.g_varchar2
1767 ,p_segment26 => l_pgp_segment26 --hr_api.g_varchar2
1768 ,p_segment27 => l_pgp_segment27 --hr_api.g_varchar2
1769 ,p_segment28 => l_pgp_segment28 --hr_api.g_varchar2
1770 ,p_segment29 => l_pgp_segment29 --hr_api.g_varchar2
1771 ,p_segment30 => l_pgp_segment30 --hr_api.g_varchar2
1772 ,p_employment_category => l_dpl_rec.assignment_category
1773 ,p_concat_segments => hr_api.g_varchar2
1774 ,p_contract_id => hr_api.g_number
1775 ,p_establishment_id => hr_api.g_number
1776 ,p_scl_segment1 => hr_api.g_varchar2
1777 ,p_grade_ladder_pgm_id => hr_api.g_number
1778 ,p_supervisor_assignment_id => l_dpl_rec.supervisor_assignment_id
1779 ,p_object_version_number => l_host_asg_ovn
1780 ,p_special_ceiling_step_id => l_special_ceiling_step_id
1781 ,p_people_group_id => l_group_id --l_dpl_rec.people_group_id -- sturlapa
1782 ,p_soft_coding_keyflex_id => l_dpl_rec.soft_coding_keyflex_id
1783 ,p_group_name => l_group_name
1784 ,p_effective_start_date => l_host_asg_esd
1785 ,p_effective_end_date => l_host_asg_eed
1786 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1787 ,p_other_manager_warning => l_other_manager_warning
1788 ,p_spp_delete_warning => l_spp_delete_warning
1789 ,p_entries_changed_warning => l_entries_changed_warning
1790 ,p_tax_district_changed_warning => l_tax_district_changed_warning
1791 ,p_concatenated_segments => l_concatenated_segments
1792 ,p_gsp_post_process_warning => l_gsp_post_process_warning
1793 );
1794 -- sturlapa end
1795 --
1796 -- End of update new host assignment section
1797 --
1798 -- Create a salary proposal
1799 --
1800 if nvl(l_dpl_rec.permanent,'N') = 'Y'
1801 and l_dpl_rec.proposed_salary is not null then
1802 --
1803 hr_utility.set_location(l_proc,100);
1804 --
1805 hr_maintain_proposal_api.insert_salary_proposal
1806 (p_validate => p_validate
1807 ,p_pay_proposal_id => l_host_pyp_id
1808 ,p_assignment_id => l_host_assignment_id
1809 ,p_business_group_id => l_dpl_rec.to_business_group_id
1810 ,p_change_date => l_dpl_rec.start_date
1811 ,p_comments => null
1812 ,p_next_sal_review_date => null
1813 ,p_proposal_reason => null
1814 ,p_proposed_salary_n => l_dpl_rec.proposed_salary
1815 ,p_forced_ranking => null
1816 ,p_performance_review_id => null
1817 ,p_attribute_category => null
1818 ,p_attribute1 => null
1819 ,p_attribute2 => null
1820 ,p_attribute3 => null
1821 ,p_attribute4 => null
1822 ,p_attribute5 => null
1823 ,p_attribute6 => null
1824 ,p_attribute7 => null
1825 ,p_attribute8 => null
1826 ,p_attribute9 => null
1827 ,p_attribute10 => null
1828 ,p_attribute11 => null
1829 ,p_attribute12 => null
1830 ,p_attribute13 => null
1831 ,p_attribute14 => null
1832 ,p_attribute15 => null
1833 ,p_attribute16 => null
1834 ,p_attribute17 => null
1835 ,p_attribute18 => null
1836 ,p_attribute19 => null
1837 ,p_attribute20 => null
1838 ,p_object_version_number => l_host_pyp_ovn
1839 ,p_multiple_components => 'N'
1840 ,p_approved => 'Y'
1841 ,p_element_entry_id => l_pyp_element_entry_id
1842 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
1843 ,p_proposed_salary_warning => l_proposed_salary_warning
1844 ,p_approved_warning => l_approved_warning
1845 ,p_payroll_warning => l_payroll_warning
1846 );
1847 --
1848 end if;
1849 --
1850 -- Create contacts and relationships in host BG
1851 --
1852 for l_dpl_contact_rec in csr_dpl_contacts(p_person_deployment_id) loop
1853 <<contacts>>
1854 -- Fetch details from home BG
1855 --
1856 hr_utility.set_location(l_proc,110);
1857 --
1858 open csr_contact_rel_details(l_dpl_contact_rec.contact_relationship_id);
1859 fetch csr_contact_rel_details into l_contact_rel_details;
1860 close csr_contact_rel_details;
1861 --
1862 open csr_contact_person_details
1863 (l_contact_rel_details.contact_person_id,l_dpl_rec.start_date);
1864 fetch csr_contact_person_details into l_contact_person_details;
1865 close csr_contact_person_details;
1866 --
1867 --Loop around l_contacts_created to see if the contact already created in host
1868 --
1869 l_index_number := l_contacts_created.FIRST;
1870 l_contact_person_id := null;
1871 --
1872 WHILE l_index_number is not null loop
1873 <<created_contacts>>
1874 --
1875 hr_utility.set_location(l_proc,120);
1876 --
1877 if l_contact_rel_details.contact_person_id =
1878 l_contacts_created(l_index_number).home_contact_person_id then
1879 l_contact_person_id :=
1880 l_contacts_created(l_index_number).host_contact_person_id;
1881 --
1882 hr_utility.set_location(l_proc||' '||l_contact_person_id,123);
1883 --
1884 exit;
1885 else
1886 --
1887 hr_utility.set_location(l_proc,127);
1888 --
1889 l_contact_person_id := null;
1890 end if;
1891 l_index_number := l_contacts_created.NEXT(l_index_number);
1892 END LOOP created_contacts;
1893 --
1894 hr_utility.set_location(l_proc,130);
1895 hr_utility.set_location('host person id '||l_host_person_id,131);
1896 --
1897 hr_contact_rel_api.create_contact --use fetched details copying to host BG
1898 (p_validate => p_validate
1899 ,p_start_date => l_dpl_rec.start_date
1900 ,p_business_group_id => l_dpl_rec.to_business_group_id
1901 ,p_person_id => l_host_person_id
1902 ,p_contact_person_id => l_contact_person_id
1903 ,p_contact_type => l_contact_rel_details.contact_type
1904 ,p_primary_contact_flag => l_contact_rel_details.primary_contact_flag
1905 ,p_date_start => l_contact_rel_details.date_start
1906 ,p_date_end => l_contact_rel_details.date_end
1907 ,p_personal_flag => l_contact_rel_details.personal_flag
1908 ,p_last_name => l_contact_person_details.last_name
1909 ,p_sex => l_contact_person_details.sex
1910 ,p_date_of_birth => l_contact_person_details.date_of_birth
1911 ,p_email_address => l_contact_person_details.email_address
1912 ,p_first_name => l_contact_person_details.first_name
1913 ,p_known_as => l_contact_person_details.known_as
1914 ,p_marital_status => l_contact_person_details.marital_status
1915 ,p_middle_names => l_contact_person_details.middle_names
1916 ,p_nationality => l_contact_person_details.nationality
1917 ,p_national_identifier => l_contact_person_details.national_identifier
1918 ,p_previous_last_name => l_contact_person_details.previous_last_name
1919 ,p_title => l_contact_person_details.title
1920 ,p_work_telephone => l_contact_person_details.work_telephone
1921 ,p_correspondence_language => l_contact_person_details.correspondence_language
1922 ,p_honors => l_contact_person_details.honors
1923 ,p_pre_name_adjunct => l_contact_person_details.pre_name_adjunct
1924 ,p_suffix => l_contact_person_details.suffix
1925 ,p_create_mirror_flag => 'N'
1926 ,p_contact_relationship_id => l_host_ctr_id
1927 ,p_ctr_object_version_number => l_host_ctr_ovn
1928 ,p_per_person_id => l_host_contact_person_id
1929 ,p_per_object_version_number => l_host_contact_per_ovn
1930 ,p_per_effective_start_date => l_host_contact_per_esd
1931 ,p_per_effective_end_date => l_host_contact_per_eed
1932 ,p_full_name => l_host_contact_full_name
1933 ,p_per_comment_id => l_host_contact_per_comment_id
1934 ,p_name_combination_warning => l_name_combination_warning
1935 ,p_orig_hire_warning => l_orig_hire_warning
1936 );
1937 --
1938 -- Now store the contact person_ids from home and host for checking in next loop
1939 --
1940 --
1941 hr_utility.set_location(l_proc,140);
1942 --
1943 l_contact_created.home_contact_person_id := l_contact_rel_details.contact_person_id;
1944 l_contact_created.host_contact_person_id := l_host_contact_person_id;
1945 l_contacts_created.EXTEND;
1946 l_contacts_created(l_contacts_created.LAST) := l_contact_created;
1947 --
1948 end loop contacts;
1949 --
1950 -- Create EITs in host BG
1951 --
1952 for l_dpl_eit_rec in csr_dpl_eits(p_person_deployment_id) loop
1953 --
1954 hr_utility.set_location(l_proc,150);
1955 --
1956 -- Fetch details of EIT from home BG
1957 open csr_eit_details(l_dpl_eit_rec.person_extra_info_id);
1958 fetch csr_eit_details into l_eit_details;
1959 close csr_eit_details;
1960 --
1961 hr_person_extra_info_api.create_person_extra_info
1962 (p_validate => p_validate
1963 ,p_person_id => l_host_person_id
1964 ,p_information_type => l_eit_details.information_type
1965 ,p_pei_information_category => l_eit_details.pei_information_category
1966 ,p_pei_information1 => l_eit_details.pei_information1
1967 ,p_pei_information2 => l_eit_details.pei_information2
1968 ,p_pei_information3 => l_eit_details.pei_information3
1969 ,p_pei_information4 => l_eit_details.pei_information4
1970 ,p_pei_information5 => l_eit_details.pei_information5
1971 ,p_pei_information6 => l_eit_details.pei_information6
1972 ,p_pei_information7 => l_eit_details.pei_information7
1973 ,p_pei_information8 => l_eit_details.pei_information8
1974 ,p_pei_information9 => l_eit_details.pei_information9
1975 ,p_pei_information10 => l_eit_details.pei_information10
1976 ,p_pei_information11 => l_eit_details.pei_information11
1977 ,p_pei_information12 => l_eit_details.pei_information12
1978 ,p_pei_information13 => l_eit_details.pei_information13
1979 ,p_pei_information14 => l_eit_details.pei_information14
1980 ,p_pei_information15 => l_eit_details.pei_information15
1981 ,p_pei_information16 => l_eit_details.pei_information16
1982 ,p_pei_information17 => l_eit_details.pei_information17
1983 ,p_pei_information18 => l_eit_details.pei_information18
1984 ,p_pei_information19 => l_eit_details.pei_information19
1985 ,p_pei_information20 => l_eit_details.pei_information20
1986 ,p_pei_information21 => l_eit_details.pei_information21
1987 ,p_pei_information22 => l_eit_details.pei_information22
1988 ,p_pei_information23 => l_eit_details.pei_information23
1989 ,p_pei_information24 => l_eit_details.pei_information24
1990 ,p_pei_information25 => l_eit_details.pei_information25
1991 ,p_pei_information26 => l_eit_details.pei_information26
1992 ,p_pei_information27 => l_eit_details.pei_information27
1993 ,p_pei_information28 => l_eit_details.pei_information28
1994 ,p_pei_information29 => l_eit_details.pei_information29
1995 ,p_pei_information30 => l_eit_details.pei_information30
1996 ,p_person_extra_info_id => l_host_person_extra_info_id
1997 ,p_object_version_number => l_host_pei_ovn
1998 );
1999 end loop;
2000 hr_utility.set_location(l_proc,180);
2001 --
2002 -- update the proposal with the new details to keep it up to date
2003 --
2004 if nvl(l_dpl_rec.permanent,'N') = 'Y' then
2005 --
2006 hr_utility.set_location(l_proc,190);
2007 --
2008 hr_person_deployment_api.update_person_deployment
2009 (p_validate => p_validate
2010 ,p_person_deployment_id => p_person_deployment_id
2011 ,p_object_version_number => p_object_version_number
2012 ,p_to_person_id => l_host_person_id
2013 ,p_status => 'COMPLETE'
2014 ,p_policy_duration_warning => l_policy_duration_warning
2015 );
2016 else
2017 --
2018 hr_utility.set_location(l_proc,200);
2019 --
2020 hr_person_deployment_api.update_person_deployment
2021 (p_validate => p_validate
2022 ,p_person_deployment_id => p_person_deployment_id
2023 ,p_object_version_number => p_object_version_number
2024 ,p_to_person_id => l_host_person_id
2025 ,p_status => 'ACTIVE'
2026 ,p_policy_duration_warning => l_policy_duration_warning
2027 );
2028 end if;
2029 --
2030 hr_dflex_utility.remove_ignore_df_validation;
2031 --
2032 --
2033 -- Raise Workflow Business Event....to be implemented in a later phase
2034 --
2035
2036 --
2037 -- When in validation only mode raise the Validate_Enabled exception
2038 --
2039 if p_validate then
2040 raise hr_api.validate_enabled;
2041 end if;
2042 --
2043 -- Set all IN OUT and OUT parameters with out values
2044 --
2045 p_host_person_id := l_host_person_id;
2046 p_host_per_ovn := l_host_per_ovn;
2047 p_host_assignment_id := l_host_assignment_id;
2048 p_host_asg_ovn := l_host_asg_ovn;
2049 p_already_applicant_warning := l_already_applicant_warning;
2050 --
2051 --
2052 -- fix for bug 6593649
2053 open csr_get_attached_doc;
2054 fetch csr_get_attached_doc into l_attachments;
2055 if csr_get_attached_doc%found then
2056 close csr_get_attached_doc;
2057 hr_utility.set_location(l_host_person_id,200);
2058
2059 update fnd_attached_documents
2060 set ENTITY_NAME='PER_PEOPLE_F' ,PK1_VALUE =l_host_person_id
2061 WHERE PK1_VALUE=p_person_deployment_id
2062 and ENTITY_NAME ='HR_PERSON_DEPLOYMENTS' ;
2063
2064 else
2065 hr_utility.set_location(l_host_person_id,220);
2066 close csr_get_attached_doc;
2067 end if;
2068 --
2069 -- fix for bug 6593649
2070 --
2071 per_per_bus.g_global_transfer_in_process := false;
2072 --
2073 hr_utility.set_location(' Leaving:'||l_proc, 700);
2074 exception
2075 when hr_api.validate_enabled then
2076 --
2077 -- As the Validate_Enabled exception has been raised
2078 -- we must rollback to the savepoint
2079 --
2080 rollback to initiate_deployment;
2081 --
2082 hr_dflex_utility.remove_ignore_df_validation;
2083 --
2084 --
2085 -- Reset IN OUT parameters and set OUT parameters
2086 -- (Any key or derived arguments must be set to null
2087 -- when validation only mode is being used.)
2088 --
2089 p_object_version_number := l_object_version_number;
2090 p_host_person_id := null;
2091 p_host_per_ovn := null;
2092 p_host_assignment_id := null;
2093 p_host_asg_ovn := null;
2094 p_already_applicant_warning := null;
2095 --
2096 per_per_bus.g_global_transfer_in_process := false;
2097 --
2098 hr_utility.set_location(' Leaving:'||l_proc, 800);
2099 when others then
2100 --
2101 -- A validation or unexpected error has occured
2102 --
2103 rollback to initiate_deployment;
2104 --
2105 hr_dflex_utility.remove_ignore_df_validation;
2106 --
2107 --
2108 -- Reset IN OUT parameters and set all
2109 -- OUT parameters, including warnings, to null
2110 --
2111 p_object_version_number := l_object_version_number;
2112 p_host_person_id := null;
2113 p_host_per_ovn := null;
2114 p_host_assignment_id := null;
2115 p_host_asg_ovn := null;
2116 p_already_applicant_warning := null;
2117 --
2118 per_per_bus.g_global_transfer_in_process := false;
2119 --
2120 hr_utility.set_location(' Leaving:'||l_proc, 900);
2121 raise;
2122 end initiate_deployment;
2123 --
2124 --
2125 -- ----------------------------------------------------------------------------
2126 -- |--------------------------< change_deployment_dates >---------------------|
2127 -- ----------------------------------------------------------------------------
2128 --
2129 procedure change_deployment_dates
2130 (p_validate in boolean default false
2131 ,p_person_deployment_id in number
2132 ,p_object_version_number in out nocopy number
2133 ,p_start_date in date default hr_api.g_date
2134 ,p_end_date in date default hr_api.g_date
2135 ,p_deplymt_policy_id in number default hr_api.g_number
2136 ) is
2137 --
2138 -- Declare cursors and local variables
2139 --
2140 l_proc varchar2(72) := g_package||'change_deployment_dates';
2141 --
2142 l_object_version_number number;
2143 l_start_date_in date;
2144 l_end_date_in date;
2145 l_policy_duration_warning boolean;
2146 l_dummy number;
2147 l_warn_ee varchar2(1);
2148 --
2149 l_cagr_grade_def_id number;
2150 l_cagr_concatenated_segments varchar2(2000);
2151 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%type;
2152 l_soft_coding_keyflex_id number;
2153 l_comment_id number;
2154 l_host_asg_esd date;
2155 l_host_asg_eed date;
2156 l_no_managers_warning boolean;
2157 l_other_manager_warning boolean;
2158 l_hourly_salaried_warning boolean;
2159 l_gsp_post_process_warning varchar2(2000);
2160 --
2161 l_varray_d hr_dflex_utility.l_ignore_dfcode_varray
2162 := hr_dflex_utility.l_ignore_dfcode_varray();
2163
2164 cursor csr_person_deployment(p_person_deployment_id number) is
2165 select *
2166 from hr_person_deployments dpl
2167 where dpl.person_deployment_id = p_person_deployment_id;
2168 --
2169 l_dpl_rec csr_person_deployment%rowtype;
2170 --
2171 cursor csr_susp_home_asgs(p_person_deployment_id number) is
2172 select paaf.assignment_id, paaf.effective_start_date
2173 from per_all_assignments_f paaf,
2174 per_assignment_status_types past
2175 where paaf.person_id = L_DPL_REC.FROM_PERSON_ID
2176 and paaf.effective_start_date = L_DPL_REC.START_DATE
2177 and paaf.assignment_status_type_id = past.assignment_status_type_id
2178 and past.per_system_status = 'SUSP_ASSIGN';
2179 --
2180 cursor csr_overlap_asg_update
2181 (p_assignment_id number, p_old_date date, p_new_date date) is
2182 select 1
2183 from per_all_assignments_f paaf
2184 where paaf.assignment_id = p_assignment_id
2185 and paaf.effective_end_date = p_old_date-1
2186 and paaf.effective_start_date >= p_new_date;
2187 --
2188 cursor csr_host_assignments(p_start_date date) is
2189 select asg.assignment_id,asg.object_version_number,asg.effective_start_date
2190 from per_all_assignments_f asg,
2191 hr_person_deployments pdt,
2192 per_periods_of_service pds
2193 where asg.person_id = pdt.to_person_id
2194 and pdt.person_deployment_id = p_person_deployment_id
2195 and asg.period_of_service_id = pds.period_of_service_id
2196 and pds.date_start = p_start_date
2197 and trunc(sysdate) between asg.effective_start_date and
2198 asg.effective_end_date;
2199
2200 -- Commented for bug 5636625
2201 -- and asg.projected_assignment_end is not null;
2202 --
2203 begin
2204 --
2205 hr_utility.set_location('Entering:'|| l_proc, 10);
2206 --
2207 -- Issue a savepoint
2208 --
2209 savepoint change_deployment_dates;
2210 --
2211 -- Remember IN OUT parameter IN values
2212 --
2213 l_object_version_number := p_object_version_number;
2214 --
2215 -- Truncate the time portion from all IN date parameters
2216 --
2217 l_start_date_in := trunc(p_start_date);
2218 l_end_date_in := trunc(p_end_date);
2219 --
2220 hr_utility.set_location(l_proc,20);
2221 --
2222 -- Validation in addition to Row Handlers
2223 --
2224 open csr_person_deployment(p_person_deployment_id);
2225 fetch csr_person_deployment into l_dpl_rec;
2226 if csr_person_deployment%notfound then
2227 close csr_person_deployment;
2228 fnd_message.set_name('PER','HR_449609_DPL_NOT_EXIST');
2229 fnd_message.raise_error;
2230 else
2231 close csr_person_deployment;
2232 --
2233 hr_utility.set_location(l_proc,30);
2234 --
2235 end if;
2236 --
2237 if l_dpl_rec.status = 'DRAFT' then
2238 fnd_message.set_name('PER','HR_449619_PDT_DRAFT_NO_CHG');
2239 fnd_message.raise_error;
2240 elsif l_dpl_rec.status = 'COMPLETE' then
2241 fnd_message.set_name('PER','HR_449620_PDT_COMPLETE_NO_CHG');
2242 fnd_message.raise_error;
2243 end if;
2244 --
2245 -- Added Bipul
2246 if (l_dpl_rec.status = 'ACTIVE' and
2247 l_end_date_in is not null
2248 and l_end_date_in < trunc(sysdate)) then
2249 fnd_message.set_name('PER','HR_449772_PDT_INV_END_DATE');
2250 fnd_message.raise_error;
2251 end if;
2252
2253
2254 hr_utility.set_location(l_proc,40);
2255 --
2256 if nvl(l_start_date_in,l_dpl_rec.start_date) >=
2257 nvl(l_end_date_in,l_dpl_rec.end_date) then
2258 fnd_message.set_name('PER','HR_449621_PDT_CHG_DATES');
2259 fnd_message.raise_error;
2260 end if;
2261 --
2262 if nvl(l_dpl_rec.permanent,'N') = 'Y' then
2263 fnd_message.set_name('PER','HR_449622_PDT_PERM_NO_CHG');
2264 fnd_message.raise_error;
2265 end if;
2266 --
2267 -- Process Logic
2268 --
2269 if ((nvl(l_start_date_in,hr_api.g_date) <> hr_api.g_date)
2270 OR (l_end_date_in is null and l_dpl_rec.end_date is not null)
2271 OR (l_end_date_in <> hr_api.g_date)) then
2272 --
2273 -- At least one date has changed, proceed according to which one changed
2274 --
2275
2276 if nvl(l_start_date_in,hr_api.g_date) = hr_api.g_date then
2277 --
2278 hr_utility.set_location(l_proc,50);
2279 --
2280 --start date the same
2281 l_start_date_in := l_dpl_rec.start_date;
2282 else
2283 --
2284 hr_utility.set_location(l_proc,60);
2285 --
2286 --start date changed
2287 hr_change_start_date_api.update_start_date
2288 (p_validate => p_validate
2289 ,p_person_id => l_dpl_rec.to_person_id
2290 ,p_old_start_date => l_dpl_rec.start_date
2291 ,p_new_start_date => l_start_date_in
2292 ,p_update_type => 'E'
2293 ,p_applicant_number => null
2294 ,p_warn_ee => l_warn_ee
2295 );
2296 --
2297 for l_asg in csr_susp_home_asgs(p_person_deployment_id) loop
2298 --
2299 hr_utility.set_location(l_proc,70);
2300 hr_utility.set_location('assignment id '||l_asg.assignment_id,71);
2301 --
2302 open csr_overlap_asg_update
2303 (l_asg.assignment_id,l_dpl_rec.start_date,l_start_date_in);
2304 fetch csr_overlap_asg_update into l_dummy;
2305 if csr_overlap_asg_update%notfound then
2306 --
2307 update per_all_assignments_f paaf
2308 set paaf.effective_start_date = l_start_date_in
2309 where paaf.assignment_id = l_asg.assignment_id
2310 and paaf.effective_start_date = l_dpl_rec.start_date;
2311 --
2312 update per_all_assignments_f paaf
2313 set paaf.effective_end_date = l_start_date_in-1
2314 where paaf.assignment_id = l_asg.assignment_id
2315 and paaf.effective_end_date = l_dpl_rec.start_date-1;
2316 --
2317 close csr_overlap_asg_update;
2318 else
2319 close csr_overlap_asg_update;
2320 fnd_message.set_name('PER','HR_449623_PDT_CHG_ASG_OVERLAP');
2321 fnd_message.raise_error;
2322 end if;
2323 --
2324 end loop;
2325 end if;
2326 --
2327 -- Bug 5635350 modified the following if condition
2328 -- if l_end_date_in = hr_api.g_date then
2329 if l_end_date_in = hr_api.g_date or l_end_date_in = l_dpl_rec.end_date then
2330 --
2331 hr_utility.set_location(l_proc,80);
2332 --
2333 --end date the same
2334 l_end_date_in := l_dpl_rec.end_date;
2335 else
2336 --
2337 hr_utility.set_location(l_proc,90);
2338 --
2339 -- The end date has changed, this is simply an update to projected asg end in host
2340 -- If start_date also moved later than sysdate we have to do correction instead
2341
2342 l_varray_d.delete;
2343 l_varray_d.extend(1);
2344 l_varray_d(1):='PER_ASSIGNMENTS';
2345 hr_dflex_utility.create_ignore_df_validation(p_rec=>l_varray_d);
2346
2347 for l_host in csr_host_assignments(l_start_date_in) loop
2348 --
2349 -- if l_start_date_in < trunc(sysdate) then
2350 if l_host.effective_start_date < trunc(sysdate) then
2351 --
2352 -- Start date is
2353 --
2354 hr_assignment_api.update_emp_asg
2355 (p_validate => p_validate
2356 ,p_effective_date => trunc(sysdate)
2357 ,p_datetrack_update_mode => 'UPDATE'
2358 ,p_assignment_id => l_host.assignment_id
2359 ,p_object_version_number => l_host.object_version_number
2360 ,p_projected_assignment_end => l_end_date_in
2361 ,p_cagr_grade_def_id => l_cagr_grade_def_id
2362 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
2363 ,p_concatenated_segments => l_concatenated_segments
2364 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
2365 ,p_comment_id => l_comment_id
2366 ,p_effective_start_date => l_host_asg_esd
2367 ,p_effective_end_date => l_host_asg_eed
2368 ,p_no_managers_warning => l_no_managers_warning
2369 ,p_other_manager_warning => l_other_manager_warning
2370 ,p_hourly_salaried_warning => l_hourly_salaried_warning
2371 ,p_gsp_post_process_warning => l_gsp_post_process_warning
2372 );
2373 else
2374 hr_assignment_api.update_emp_asg
2375 (p_validate => p_validate
2376 ,p_effective_date => trunc(sysdate)
2377 ,p_datetrack_update_mode => 'CORRECTION'
2378 ,p_assignment_id => l_host.assignment_id
2379 ,p_object_version_number => l_host.object_version_number
2380 ,p_projected_assignment_end => l_end_date_in
2381 ,p_cagr_grade_def_id => l_cagr_grade_def_id
2382 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
2383 ,p_concatenated_segments => l_concatenated_segments
2384 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
2385 ,p_comment_id => l_comment_id
2386 ,p_effective_start_date => l_host_asg_esd
2387 ,p_effective_end_date => l_host_asg_eed
2388 ,p_no_managers_warning => l_no_managers_warning
2389 ,p_other_manager_warning => l_other_manager_warning
2390 ,p_hourly_salaried_warning => l_hourly_salaried_warning
2391 ,p_gsp_post_process_warning => l_gsp_post_process_warning
2392 );
2393 end if;
2394 --
2395 end loop;
2396 end if;
2397
2398 hr_dflex_utility.remove_ignore_df_validation;
2399 --
2400 -- update the proposal with the new details to keep it up to date
2401 --
2402 hr_utility.set_location(l_proc,100);
2403 --
2404 hr_person_deployment_api.update_person_deployment
2405 (p_validate => p_validate
2406 ,p_person_deployment_id => p_person_deployment_id
2407 ,p_object_version_number => p_object_version_number
2408 ,p_start_date => l_start_date_in
2409 ,p_end_date => l_end_date_in
2410 ,p_policy_duration_warning => l_policy_duration_warning
2411 );
2412 end if;
2413 --
2414 -- When in validation only mode raise the Validate_Enabled exception
2415 --
2416 if p_validate then
2417 raise hr_api.validate_enabled;
2418 end if;
2419 --
2420 -- Set all IN OUT and OUT parameters with out values
2421 --
2422
2423 --
2424 hr_utility.set_location(' Leaving:'||l_proc, 700);
2425 exception
2426 when hr_api.validate_enabled then
2427 --
2428 -- As the Validate_Enabled exception has been raised
2429 -- we must rollback to the savepoint
2430 --
2431 rollback to change_deployment_dates;
2432 hr_dflex_utility.remove_ignore_df_validation;
2433 --
2434 -- Reset IN OUT parameters and set OUT parameters
2435 -- (Any key or derived arguments must be set to null
2436 -- when validation only mode is being used.)
2437 --
2438 p_object_version_number := l_object_version_number;
2439 hr_utility.set_location(' Leaving:'||l_proc, 800);
2440 when others then
2441 --
2442 -- A validation or unexpected error has occured
2443 --
2444 rollback to change_deployment_dates;
2445 hr_dflex_utility.remove_ignore_df_validation;
2446 --
2447 -- Reset IN OUT parameters and set all
2448 -- OUT parameters, including warnings, to null
2449 --
2450 p_object_version_number := l_object_version_number;
2451 hr_utility.set_location(' Leaving:'||l_proc, 900);
2452 raise;
2453 end change_deployment_dates;
2454 --
2455 --
2456 -- ----------------------------------------------------------------------------
2457 -- |--------------------------< return_from_deployment >----------------------|
2458 -- ----------------------------------------------------------------------------
2459 --
2460 procedure return_from_deployment
2461 (p_validate in boolean default false
2462 ,p_person_deployment_id in number
2463 ,p_object_version_number in out nocopy number
2464 ,p_end_date in date default hr_api.g_date
2465 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
2466 ,p_leaving_person_type_id in number default hr_api.g_number
2467 ) is
2468 --
2469 -- Declare cursors and local variables
2470 --
2471 l_proc varchar2(72) := g_package||'return_from_deployment';
2472 l_object_version_number number;
2473 --
2474 l_end_date_in date;
2475 l_leaving_reason varchar2(30);
2476 l_leaving_person_type_id number;
2477 l_home_assignment_id number;
2478 l_home_asg_ovn number;
2479 l_home_asg_esd date;
2480 l_home_asg_eed date;
2481 l_host_pds_id number;
2482 l_host_pds_ovn number;
2483 --
2484 l_policy_duration_warning boolean;
2485 l_last_std_process_date_out date;
2486 l_supervisor_warning boolean;
2487 l_event_warning boolean;
2488 l_interview_warning boolean;
2489 l_review_warning boolean;
2490 l_recruiter_warning boolean;
2491 l_asg_future_changes_warning boolean;
2492 l_entries_changed_warning varchar2(1);
2493 l_pay_proposal_warning boolean;
2494 l_dod_warning boolean;
2495 --
2496 cursor csr_person_deployment(p_person_deployment_id number) is
2497 select *
2498 from hr_person_deployments dpl
2499 where dpl.person_deployment_id = p_person_deployment_id;
2500 --
2501 l_dpl_rec csr_person_deployment%rowtype;
2502 --
2503 cursor csr_host_pds(p_person_id number) is
2504 select pds.period_of_service_id, pds.object_version_number
2505 from per_periods_of_service pds
2506 where pds.person_id = p_person_id
2507 and pds.date_start = L_DPL_REC.START_DATE;
2508 --
2509 -- following cursor needs to get the assignments which were suspended in line with
2510 -- start of deployment, but ignore others
2511 --
2512 cursor csr_susp_home_asgs(p_person_id number) is
2513 select paaf.assignment_id, paaf.object_version_number
2514 from per_all_assignments_f paaf,
2515 per_assignment_status_types past
2516 where paaf.person_id = p_person_id
2517 and L_END_DATE_IN between
2518 paaf.effective_start_date and paaf.effective_end_date
2519 and paaf.assignment_type = 'E'
2520 and paaf.assignment_status_type_id = past.assignment_status_type_id
2521 and past.per_system_status = 'SUSP_ASSIGN'
2522 and exists
2523 (select 1
2524 from per_all_assignments_f paaf1,
2525 per_assignment_status_types past1
2526 where paaf1.assignment_id = paaf.assignment_id
2527 and paaf1.effective_start_date = L_DPL_REC.START_DATE
2528 and paaf1.assignment_status_type_id = past1.assignment_status_type_id
2529 and past1.per_system_status = 'SUSP_ASSIGN')
2530 and exists
2531 (select 1
2532 from per_all_assignments_f paaf2,
2533 per_assignment_status_types past2
2534 where paaf2.assignment_id = paaf.assignment_id
2535 and paaf2.effective_end_date = L_DPL_REC.START_DATE-1
2536 and paaf2.assignment_status_type_id = past2.assignment_status_type_id
2537 and past2.per_system_status = 'ACTIVE_ASSIGN');
2538 --
2539 begin
2540 hr_utility.set_location('Entering:'|| l_proc, 10);
2541 --
2542 -- Issue a savepoint
2543 --
2544 savepoint return_from_deployment;
2545 --
2546 -- Remember IN OUT parameter IN values
2547 --
2548 l_object_version_number := p_object_version_number;
2549 --
2550 -- Truncate the time portion from all IN date parameters
2551 --
2552 l_end_date_in := trunc(p_end_date);
2553 --
2554 -- Validation in addition to Row Handlers
2555 --
2556 open csr_person_deployment(p_person_deployment_id);
2557 fetch csr_person_deployment into l_dpl_rec;
2558 if csr_person_deployment%notfound then
2559 close csr_person_deployment;
2560 fnd_message.set_name('PER','HR_449609_DPL_NOT_EXIST');
2561 fnd_message.raise_error;
2562 else
2563 close csr_person_deployment;
2564 --
2565 hr_utility.set_location(l_proc,20);
2566 --
2567 end if;
2568 --
2569 if l_dpl_rec.status = 'DRAFT' then
2570 fnd_message.set_name('PER','HR_449624_PDT_DRAFT_NO_END');
2571 fnd_message.raise_error;
2572 elsif l_dpl_rec.status = 'COMPLETE' then
2573 fnd_message.set_name('PER','HR_449625_PDT_COMPLETE_NO_END');
2574 fnd_message.raise_error;
2575 end if;
2576 --
2577 if l_dpl_rec.start_date > nvl(l_end_date_in,l_dpl_rec.end_date) then
2578 fnd_message.set_name('PER','HR_449621_PDT_CHG_DATES');
2579 fnd_message.raise_error;
2580 end if;
2581 --
2582 if nvl(l_dpl_rec.permanent,'N') = 'Y' then
2583 fnd_message.set_name('PER','HR_449626_PDT_PERM_NO_END');
2584 fnd_message.raise_error;
2585 end if;
2586 --
2587 -- Process Logic
2588 --
2589 l_leaving_reason := p_leaving_reason;
2590 l_leaving_person_type_id := p_leaving_person_type_id;
2591 --
2592 if nvl(l_end_date_in,hr_api.g_date) = hr_api.g_date then
2593 hr_utility.set_location(l_proc,30);
2594 l_end_date_in := l_dpl_rec.end_date;
2595 end if;
2596 --
2597 if nvl(l_leaving_reason,hr_api.g_varchar2) = hr_api.g_varchar2 then
2598 hr_utility.set_location(l_proc,40);
2599 l_leaving_reason := l_dpl_rec.leaving_reason;
2600 end if;
2601 --
2602 if nvl(l_leaving_person_type_id,hr_api.g_number) = hr_api.g_number then
2603 hr_utility.set_location(l_proc,50);
2604 l_leaving_person_type_id := l_dpl_rec.leaving_person_type_id;
2605 end if;
2606
2607 hr_person_deployment_api.update_person_deployment
2608 (p_validate => p_validate
2609 ,p_person_deployment_id => p_person_deployment_id
2610 ,p_object_version_number => p_object_version_number
2611 ,p_end_date => l_end_date_in
2612 ,p_status => 'COMPLETE'
2613 ,p_leaving_reason => l_leaving_reason
2614 ,p_leaving_person_type_id => l_leaving_person_type_id
2615 ,p_policy_duration_warning => l_policy_duration_warning
2616 );
2617
2618 --
2619 -- Terminate the host employment
2620 --
2621 open csr_host_pds(l_dpl_rec.to_person_id);
2622 fetch csr_host_pds into l_host_pds_id,l_host_pds_ovn;
2623 close csr_host_pds;
2624 --
2625 hr_utility.set_location(l_proc,60);
2626 --
2627 hr_ex_employee_api.actual_termination_emp
2628 (p_validate => p_validate
2629 ,p_effective_date => l_end_date_in
2630 ,p_period_of_service_id => l_host_pds_id
2631 ,p_object_version_number => l_host_pds_ovn
2632 ,p_actual_termination_date => l_end_date_in
2633 ,p_person_type_id => l_leaving_person_type_id
2634 ,p_leaving_reason => l_leaving_reason
2635
2636 -- Changed for bug 5512320
2637 ,p_last_standard_process_date => null
2638 ,p_last_std_process_date_out => l_last_std_process_date_out
2639
2640 ,p_supervisor_warning => l_supervisor_warning
2641 ,p_event_warning => l_event_warning
2642 ,p_interview_warning => l_interview_warning
2643 ,p_review_warning => l_review_warning
2644 ,p_recruiter_warning => l_recruiter_warning
2645 ,p_asg_future_changes_warning => l_asg_future_changes_warning
2646 ,p_entries_changed_warning => l_entries_changed_warning
2647 ,p_pay_proposal_warning => l_pay_proposal_warning
2648 ,p_dod_warning => l_dod_warning
2649 );
2650 --
2651 -- Reactivate the suspended home assignments
2652 --
2653 for l_asg_rec in csr_susp_home_asgs(l_dpl_rec.from_person_id) loop
2654 --
2655 hr_utility.set_location(l_proc,70);
2656 hr_utility.set_location('assignment_id '||l_asg_rec.assignment_id,71);
2657 --
2658 hr_assignment_api.activate_emp_asg
2659 (p_validate => p_validate
2660 ,p_effective_date => l_end_date_in+1
2661 ,p_datetrack_update_mode => 'UPDATE'
2662 ,p_assignment_id => l_asg_rec.assignment_id
2663 ,p_change_reason => null
2664 ,p_object_version_number => l_asg_rec.object_version_number
2665 ,p_assignment_status_type_id => null --null causes it to be set to default
2666 ,p_effective_start_date => l_home_asg_esd
2667 ,p_effective_end_date => l_home_asg_eed
2668 );
2669 end loop;
2670 --
2671 -- update the proposal with the new details to keep it up to date
2672 --
2673 hr_utility.set_location(l_proc,80);
2674 --
2675
2676 --
2677 -- When in validation only mode raise the Validate_Enabled exception
2678 --
2679 if p_validate then
2680 raise hr_api.validate_enabled;
2681 end if;
2682 --
2683 -- Set all IN OUT and OUT parameters with out values
2684 --
2685 hr_utility.set_location(' Leaving:'||l_proc, 700);
2686 exception
2687 when hr_api.validate_enabled then
2688 --
2689 -- As the Validate_Enabled exception has been raised
2690 -- we must rollback to the savepoint
2691 --
2692 rollback to return_from_deployment;
2693 --
2694 -- Reset IN OUT parameters and set OUT parameters
2695 -- (Any key or derived arguments must be set to null
2696 -- when validation only mode is being used.)
2697 --
2698 p_object_version_number := l_object_version_number;
2699 hr_utility.set_location(' Leaving:'||l_proc, 800);
2700 when others then
2701 --
2702 -- A validation or unexpected error has occured
2703 --
2704 rollback to return_from_deployment;
2705 --
2706 -- Reset IN OUT parameters and set all
2707 -- OUT parameters, including warnings, to null
2708 --
2709 p_object_version_number := l_object_version_number;
2710 hr_utility.set_location(' Leaving:'||l_proc, 900);
2711 raise;
2712 end return_from_deployment;
2713 --
2714 end HR_PERSON_DEPLOYMENT_API;