[Home] [Help]
PACKAGE BODY: APPS.HR_MASS_MOVE_API
Source
1 Package Body hr_mass_move_api as
2 /* $Header: pemmvapi.pkb 120.2 2006/06/27 00:18:04 hsajja ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_mass_move_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< MOVE_CWK_ASG >-------------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 PROCEDURE move_cwk_asg
13 (p_validate IN BOOLEAN DEFAULT FALSE
14 ,p_effective_date IN DATE
15 ,p_datetrack_update_mode IN VARCHAR2
16 ,p_assignment_id IN NUMBER
17 ,p_object_version_number IN OUT NOCOPY NUMBER
18 ,p_grade_id IN NUMBER DEFAULT HR_API.G_NUMBER
19 ,p_position_id IN NUMBER DEFAULT HR_API.G_NUMBER
20 ,p_job_id IN NUMBER DEFAULT HR_API.G_NUMBER
21 ,p_location_id IN NUMBER DEFAULT HR_API.G_NUMBER
22 ,p_organization_id IN NUMBER DEFAULT HR_API.G_NUMBER
23 ,p_people_group_name OUT NOCOPY VARCHAR2
24 ,p_effective_start_date OUT NOCOPY DATE
25 ,p_effective_end_date OUT NOCOPY DATE
26 ,p_people_group_id OUT NOCOPY NUMBER
27 ,p_org_now_no_manager_warning OUT NOCOPY BOOLEAN
28 ,p_other_manager_warning OUT NOCOPY BOOLEAN
29 ,p_spp_delete_warning OUT NOCOPY BOOLEAN
30 ,p_entries_changed_warning OUT NOCOPY VARCHAR2
31 ,p_tax_district_changed_warning OUT NOCOPY BOOLEAN) IS
32 --
33 -- Declare Local Variables
34 --
35 l_proc VARCHAR2(72) := g_package||'upd_cwk_asg ';
36 l_ovn NUMBER := p_object_version_number;
37 --
38 -- Out Parameters
39 --
40 l_people_group_name VARCHAR2(240);
41 l_effective_start_date DATE;
42 l_effective_end_date DATE;
43 l_people_group_id NUMBER;
44 l_org_now_no_manager_warning BOOLEAN;
45 l_other_manager_warning BOOLEAN;
46 l_spp_delete_warning BOOLEAN;
47 l_entries_changed_warning VARCHAR2(1);
48 l_tax_district_changed_warning BOOLEAN;
49 --
50 BEGIN
51 --
52 hr_utility.set_location('Entering : '||l_proc,10);
53 --
54 -- Issue a savepoint for rollbacks
55 --
56 SAVEPOINT move_cwk_asg;
57 --
58 hr_assignment_api.update_cwk_asg_criteria
59 (p_validate => p_validate
60 ,p_effective_date => p_effective_date
61 ,p_datetrack_update_mode => p_datetrack_update_mode
62 ,p_assignment_id => p_assignment_Id
63 ,p_object_version_number => l_ovn
64 ,p_grade_id => p_grade_id
65 ,p_position_id => p_position_id
66 ,p_job_id => p_job_id
67 ,p_location_id => p_location_id
68 ,p_organization_id => p_organization_id
69 ,p_people_group_name => l_people_group_name
70 ,p_effective_start_date => l_effective_start_date
71 ,p_effective_end_date => l_effective_end_date
72 ,p_people_group_id => l_people_group_Id
73 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
74 ,p_other_manager_warning => l_other_manager_warning
75 ,p_spp_delete_warning => l_spp_delete_warning
76 ,p_entries_changed_warning => l_entries_changed_warning
77 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
78 --
79 hr_utility.set_location(' Leaving:'||l_proc, 997);
80 --
81 EXCEPTION
82 --
83 WHEN hr_api.validate_enabled THEN
84 --
85 -- As the Validate_Enabled exception has been raised
86 -- we must rollback to the savepoint
87 --
88 ROLLBACK TO move_cwk_asg;
89 --
90 -- Only set output warning arguments
91 -- (Any key or derived arguments must be set to null
92 -- when validation only mode is being used.)
93 --
94 p_object_version_number := NULL;
95 p_effective_start_date := NULL;
96 p_effective_end_date := NULL;
97 p_people_group_name := l_people_group_name;
98 p_people_group_id := l_people_group_id;
99 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
100 p_other_manager_warning := l_other_manager_warning;
101 p_spp_delete_warning := l_spp_delete_warning;
102 p_entries_changed_warning := l_entries_changed_warning;
103 p_tax_district_changed_warning := l_tax_district_changed_warning;
104 --
105 hr_utility.set_location(' Leaving:'||l_proc, 998);
106 --
107 WHEN OTHERS THEN
108 --
109 -- A validation or unexpected error has occurred
110 --
111 ROLLBACK TO move_cwk_asg;
112 --
113 -- set in out parameters and set out parameters
114 --
115 p_object_version_number := NULL;
116 p_effective_start_date := NULL;
117 p_effective_end_date := NULL;
118 p_people_group_name := NULL;
119 p_people_group_id := NULL;
120 p_org_now_no_manager_warning := FALSE;
121 p_other_manager_warning := FALSE;
122 p_spp_delete_warning := FALSE;
123 p_entries_changed_warning := NULL;
124 p_tax_district_changed_warning := FALSE;
125 --
126 hr_utility.set_location(' Leaving:'||l_proc, 999);
127 --
128 RAISE;
129 --
130 END move_cwk_asg;
131 --
132 -- ----------------------------------------------------------------------------
133 -- |------------------------< move_emp_asg >---------------------------|
134 -- ----------------------------------------------------------------------------
135 --
136 procedure move_emp_asg
137 (p_validate in boolean default false
138 ,p_mass_move_id in number default hr_api.g_number
139 ,p_effective_date in date
140 ,p_assignment_id in number
141 ,p_object_version_number in out nocopy number
142 ,p_grade_id in number default hr_api.g_number
143 ,p_position_id in number default hr_api.g_number
144 ,p_job_id in number default hr_api.g_number
145 ,p_organization_id in number default hr_api.g_number
146 ,p_location_id in number default hr_api.g_number
147 ,p_frequency in varchar2 default hr_api.g_varchar2
148 ,p_normal_hours in number default hr_api.g_number
149 ,p_time_normal_finish in varchar2 default hr_api.g_varchar2
150 ,p_time_normal_start in varchar2 default hr_api.g_varchar2
151 ,p_segment1 in varchar2 default hr_api.g_varchar2
152 ,p_segment2 in varchar2 default hr_api.g_varchar2
153 ,p_segment3 in varchar2 default hr_api.g_varchar2
154 ,p_segment4 in varchar2 default hr_api.g_varchar2
155 ,p_segment5 in varchar2 default hr_api.g_varchar2
156 ,p_segment6 in varchar2 default hr_api.g_varchar2
157 ,p_segment7 in varchar2 default hr_api.g_varchar2
158 ,p_segment8 in varchar2 default hr_api.g_varchar2
159 ,p_segment9 in varchar2 default hr_api.g_varchar2
160 ,p_segment10 in varchar2 default hr_api.g_varchar2
161 ,p_segment11 in varchar2 default hr_api.g_varchar2
162 ,p_segment12 in varchar2 default hr_api.g_varchar2
163 ,p_segment13 in varchar2 default hr_api.g_varchar2
164 ,p_segment14 in varchar2 default hr_api.g_varchar2
165 ,p_segment15 in varchar2 default hr_api.g_varchar2
166 ,p_segment16 in varchar2 default hr_api.g_varchar2
167 ,p_segment17 in varchar2 default hr_api.g_varchar2
168 ,p_segment18 in varchar2 default hr_api.g_varchar2
169 ,p_segment19 in varchar2 default hr_api.g_varchar2
170 ,p_segment20 in varchar2 default hr_api.g_varchar2
171 ,p_segment21 in varchar2 default hr_api.g_varchar2
172 ,p_segment22 in varchar2 default hr_api.g_varchar2
173 ,p_segment23 in varchar2 default hr_api.g_varchar2
174 ,p_segment24 in varchar2 default hr_api.g_varchar2
175 ,p_segment25 in varchar2 default hr_api.g_varchar2
176 ,p_segment26 in varchar2 default hr_api.g_varchar2
177 ,p_segment27 in varchar2 default hr_api.g_varchar2
178 ,p_segment28 in varchar2 default hr_api.g_varchar2
179 ,p_segment29 in varchar2 default hr_api.g_varchar2
180 ,p_segment30 in varchar2 default hr_api.g_varchar2
181 ,p_soft_coding_keyflex_id out nocopy number
182 ,p_effective_start_date out nocopy date
183 ,p_effective_end_date out nocopy date
184 ,p_concatenated_segments out nocopy varchar2
185 ,p_org_now_no_manager_warning out nocopy boolean
186 ,p_other_manager_warning out nocopy boolean
187 ,p_entries_changed_warning out nocopy varchar2
188 ,p_spp_delete_warning out nocopy boolean
189 ) is
190 --
191 -- Declare cursors and local variables
192 --
193 l_proc varchar2(72) := g_package||'move_emp_asg';
194 l_api_updating boolean;
195 l_scl_api_updating boolean;
196 l_object_version_number number(15);
197 l_ovn number := p_object_version_number;
198 l_special_ceiling_step_id number(15) default hr_api.g_number;
199 l_people_group_id number(15);
200 l_group_name varchar2(240);
201 l_spp_delete_warning boolean;
202 l_tax_district_changed_warning boolean;
203 l_no_managers_warning boolean;
204 l_org_now_no_manager_warning boolean;
205 l_other_manager_warning boolean;
206 l_entries_changed_warning varchar2(1);
207 l_comment_id number(15);
208 l_effective_start_date date;
209 l_scl_updated_flag boolean default FALSE;
210 l_effective_end_date date;
211 l_soft_coding_keyflex_id number(15) default null;
212 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE default null;
213 l_exists varchar2(1);
214 l_old_asg_eff_start_date date default null;
215 l_old_asg_object_version_num number(15);
216 --
217 --
218 cursor csr_get_old_asg_data is
219 select asg1.effective_start_date
220 , asg1.object_version_number
221 from per_assignments_f asg1
222 where asg1.assignment_id = p_assignment_id
223 and asg1.effective_start_date =
224 (select max(asg2.effective_start_date)
225 from per_assignments_f asg2
226 where asg1.assignment_id = asg2.assignment_id
227 and asg2.effective_start_date < p_effective_date);
228 --
229 begin
230 --
231 hr_utility.set_location('Entering:'|| l_proc, 10);
232 --
233 -- Issue a savepoint
234 --
235 savepoint move_emp_asg;
236 --
237 l_object_version_number := p_object_version_number;
238 --
239 -- Retrieve current Assignment details
240 --
241 l_api_updating := per_asg_shd.api_updating
242 (p_assignment_id => p_assignment_id
243 ,p_effective_date => p_effective_date
244 ,p_object_version_number => l_object_version_number);
245 --
246 hr_utility.set_location(l_proc, 20);
247 --
248 -- Validate that at least one of P_POSITION_ID, P_ORGANIZATION_ID,P_GRADE or
249 -- P_LOCATION_ID is being updated.
250 --
251 if ( ( (nvl(p_organization_id, hr_api.g_number) <> hr_api.g_number )
252 and nvl(per_asg_shd.g_old_rec.organization_id, hr_api.g_number) <>
253 nvl(p_organization_id, hr_api.g_number) )
254 or ( (nvl(p_position_id, hr_api.g_number) <> hr_api.g_number )
255 and nvl(per_asg_shd.g_old_rec.position_id, hr_api.g_number) <> nvl(p_position_id,hr_api.g_number) )
256 or ( (nvl(p_grade_id, hr_api.g_number) <> hr_api.g_number )
257 and nvl(per_asg_shd.g_old_rec.grade_id, hr_api.g_number) <>
258 nvl(p_grade_id,hr_api.g_number) )
259 or ( ( nvl(p_location_id, hr_api.g_number) <> hr_api.g_number )
260 and nvl(per_asg_shd.g_old_rec.location_id, hr_api.g_number) <>
261 nvl(p_location_id, hr_api.g_number) ) ) then
262 null;
263 else
264 hr_utility.set_message(801, 'HR_51104_MMV_MUST_UPD_ASG');
265 hr_utility.raise_error;
266 end if;
267 --
268 hr_utility.set_location( l_proc, 30 );
269 --
270 -- Call the pre-core move assignment customer stub
271 --
272 hr_mass_move_cus.pre_move_emp_asg
273 (p_effective_date => p_effective_date
274 ,p_assignment_id => p_assignment_id
275 ,p_object_version_number => p_object_version_number
276 ,p_mass_move_id => p_mass_move_id
277 ,p_position_id => p_position_id
278 ,p_organization_id => p_organization_id
279 ,p_location_id => p_location_id
280 ,p_frequency => p_frequency
281 ,p_normal_hours => p_normal_hours
282 ,p_time_normal_finish => p_time_normal_finish
283 ,p_time_normal_start => p_time_normal_start
284 ,p_segment1 => p_segment1
285 ,p_segment2 => p_segment2
286 ,p_segment3 => p_segment3
287 ,p_segment4 => p_segment4
288 ,p_segment5 => p_segment5
289 ,p_segment6 => p_segment6
290 ,p_segment7 => p_segment7
291 ,p_segment8 => p_segment8
292 ,p_segment9 => p_segment9
293 ,p_segment10 => p_segment10
294 ,p_segment11 => p_segment11
295 ,p_segment12 => p_segment12
296 ,p_segment13 => p_segment13
297 ,p_segment14 => p_segment14
298 ,p_segment15 => p_segment15
299 ,p_segment16 => p_segment16
300 ,p_segment17 => p_segment17
301 ,p_segment18 => p_segment18
302 ,p_segment19 => p_segment19
303 ,p_segment20 => p_segment20
304 ,p_segment21 => p_segment21
305 ,p_segment22 => p_segment22
306 ,p_segment23 => p_segment23
307 ,p_segment24 => p_segment24
308 ,p_segment25 => p_segment25
309 ,p_segment26 => p_segment26
310 ,p_segment27 => p_segment27
311 ,p_segment28 => p_segment28
312 ,p_segment29 => p_segment29
313 ,p_segment30 => p_segment30
314 );
315 --
316 hr_utility.set_location( l_proc, 40 );
317 --
318 hr_assignment_api.update_emp_asg_criteria
319 (p_validate => FALSE
320 ,p_effective_date => p_effective_date
321 ,p_datetrack_update_mode => 'UPDATE'
322 ,p_assignment_id => p_assignment_id
323 ,p_object_version_number => l_object_version_number
324 ,p_position_id => p_position_id
325 ,p_job_id => p_job_id
326 ,p_location_id => p_location_id
327 ,p_organization_id => p_organization_id
328 ,p_grade_id => p_grade_id
329 ,p_effective_start_date => l_effective_start_date
330 ,p_effective_end_date => l_effective_end_date
331 ,p_special_ceiling_step_id => l_special_ceiling_step_id
332 ,p_people_group_id => l_people_group_id
333 ,p_group_name => l_group_name
334 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
335 ,p_other_manager_warning => l_other_manager_warning
336 ,p_spp_delete_warning => l_spp_delete_warning
337 ,p_entries_changed_warning => l_entries_changed_warning
338 ,p_tax_district_changed_warning => l_tax_district_changed_warning
339 );
340 --
341 hr_utility.set_location( l_proc, 45 );
342 --
343 -- Save the object version number and effective start date as of this update
344 -- so that they may be passed to the post_move_emp_asg stub package, where
345 -- the customer can do further corrections if necessary.
346 --
347 hr_utility.trace('p_effective_date '|| p_effective_date);
348 --
349 open csr_get_old_asg_data;
350 fetch csr_get_old_asg_data
351 into l_old_asg_eff_start_date
352 , l_old_asg_object_version_num;
353 if csr_get_old_asg_data%notfound then
354 hr_utility.set_location( l_proc, 47 );
355 l_old_asg_eff_start_date := p_effective_date;
356 l_old_asg_object_version_num := l_object_version_number;
357 end if;
358 close csr_get_old_asg_data;
359 --
360 hr_utility.set_location( l_proc, 50 );
361 --
362 -- If the assignment currently has SCL information :
363 --
364 if per_asg_shd.g_old_rec.soft_coding_keyflex_id is not null then
365 hr_utility.set_location( l_proc, 55 );
366 --
367 l_scl_api_updating := hr_scl_shd.api_updating
368 (p_soft_coding_keyflex_id =>
369 per_asg_shd.g_old_rec.soft_coding_keyflex_id);
370 --
371 -- Check whether any of the SCL segments are being updated.
372 --
373 if ( ( (nvl(p_segment1, hr_api.g_varchar2) <> hr_api.g_varchar2)
374 and nvl(hr_scl_shd.g_old_rec.segment1, hr_api.g_varchar2) <>
375 nvl(p_segment1, hr_api.g_varchar2) )
376 or ( (nvl(p_segment2, hr_api.g_varchar2) <> hr_api.g_varchar2)
377 and nvl(hr_scl_shd.g_old_rec.segment2, hr_api.g_varchar2) <>
378 nvl(p_segment2, hr_api.g_varchar2) )
379 or ( (nvl(p_segment3, hr_api.g_varchar2) <> hr_api.g_varchar2)
380 and nvl(hr_scl_shd.g_old_rec.segment3, hr_api.g_varchar2) <>
381 nvl(p_segment3, hr_api.g_varchar2) )
382 or ( (nvl(p_segment4, hr_api.g_varchar2) <> hr_api.g_varchar2)
383 and nvl(hr_scl_shd.g_old_rec.segment4, hr_api.g_varchar2) <>
384 nvl(p_segment4, hr_api.g_varchar2) )
385 or ( (nvl(p_segment5, hr_api.g_varchar2) <> hr_api.g_varchar2)
386 and nvl(hr_scl_shd.g_old_rec.segment5, hr_api.g_varchar2) <>
387 nvl(p_segment5, hr_api.g_varchar2) )
388 or ( (nvl(p_segment6, hr_api.g_varchar2) <> hr_api.g_varchar2)
389 and nvl(hr_scl_shd.g_old_rec.segment6, hr_api.g_varchar2) <>
390 nvl(p_segment6, hr_api.g_varchar2) )
391 or ( (nvl(p_segment7, hr_api.g_varchar2) <> hr_api.g_varchar2)
392 and nvl(hr_scl_shd.g_old_rec.segment7, hr_api.g_varchar2) <>
393 nvl(p_segment7, hr_api.g_varchar2) )
394 or ( (nvl(p_segment8, hr_api.g_varchar2) <> hr_api.g_varchar2)
395 and nvl(hr_scl_shd.g_old_rec.segment8, hr_api.g_varchar2) <>
396 nvl(p_segment8, hr_api.g_varchar2) )
397 or ( (nvl(p_segment9, hr_api.g_varchar2) <> hr_api.g_varchar2)
398 and nvl(hr_scl_shd.g_old_rec.segment9, hr_api.g_varchar2) <>
399 nvl(p_segment9, hr_api.g_varchar2) )
400 or ( (nvl(p_segment10, hr_api.g_varchar2) <> hr_api.g_varchar2)
401 and nvl(hr_scl_shd.g_old_rec.segment10, hr_api.g_varchar2) <>
402 nvl(p_segment10, hr_api.g_varchar2) )
403 or ( (nvl(p_segment11, hr_api.g_varchar2) <> hr_api.g_varchar2)
404 and nvl(hr_scl_shd.g_old_rec.segment11, hr_api.g_varchar2) <>
405 nvl(p_segment11, hr_api.g_varchar2) )
406 or ( (nvl(p_segment12, hr_api.g_varchar2) <> hr_api.g_varchar2)
407 and nvl(hr_scl_shd.g_old_rec.segment12, hr_api.g_varchar2) <>
408 nvl(p_segment12, hr_api.g_varchar2) )
409 or ( (nvl(p_segment13, hr_api.g_varchar2) <> hr_api.g_varchar2)
410 and nvl(hr_scl_shd.g_old_rec.segment13, hr_api.g_varchar2) <>
411 nvl(p_segment13, hr_api.g_varchar2) )
412 or ( (nvl(p_segment14, hr_api.g_varchar2) <> hr_api.g_varchar2)
413 and nvl(hr_scl_shd.g_old_rec.segment14, hr_api.g_varchar2) <>
414 nvl(p_segment14, hr_api.g_varchar2) )
415 or ( (nvl(p_segment15, hr_api.g_varchar2) <> hr_api.g_varchar2)
416 and nvl(hr_scl_shd.g_old_rec.segment15, hr_api.g_varchar2) <>
417 nvl(p_segment15, hr_api.g_varchar2) )
418 or ( (nvl(p_segment16, hr_api.g_varchar2) <> hr_api.g_varchar2)
419 and nvl(hr_scl_shd.g_old_rec.segment16, hr_api.g_varchar2) <>
420 nvl(p_segment16, hr_api.g_varchar2) )
421 or ( (nvl(p_segment17, hr_api.g_varchar2) <> hr_api.g_varchar2)
422 and nvl(hr_scl_shd.g_old_rec.segment17, hr_api.g_varchar2) <>
423 nvl(p_segment17, hr_api.g_varchar2) )
424 or ( (nvl(p_segment18, hr_api.g_varchar2) <> hr_api.g_varchar2)
425 and nvl(hr_scl_shd.g_old_rec.segment18, hr_api.g_varchar2) <>
426 nvl(p_segment18, hr_api.g_varchar2) )
427 or ( (nvl(p_segment19, hr_api.g_varchar2) <> hr_api.g_varchar2)
428 and nvl(hr_scl_shd.g_old_rec.segment19, hr_api.g_varchar2) <>
429 nvl(p_segment19, hr_api.g_varchar2) )
430 or ( (nvl(p_segment20, hr_api.g_varchar2) <> hr_api.g_varchar2)
431 and nvl(hr_scl_shd.g_old_rec.segment20, hr_api.g_varchar2) <>
432 nvl(p_segment20, hr_api.g_varchar2) )
433 or ( (nvl(p_segment21, hr_api.g_varchar2) <> hr_api.g_varchar2)
434 and nvl(hr_scl_shd.g_old_rec.segment21, hr_api.g_varchar2) <>
435 nvl(p_segment21, hr_api.g_varchar2) )
436 or ( (nvl(p_segment22, hr_api.g_varchar2) <> hr_api.g_varchar2)
437 and nvl(hr_scl_shd.g_old_rec.segment22, hr_api.g_varchar2) <>
438 nvl(p_segment22, hr_api.g_varchar2) )
439 or ( (nvl(p_segment23, hr_api.g_varchar2) <> hr_api.g_varchar2)
440 and nvl(hr_scl_shd.g_old_rec.segment23, hr_api.g_varchar2) <>
441 nvl(p_segment23, hr_api.g_varchar2) )
442 or ( (nvl(p_segment24, hr_api.g_varchar2) <> hr_api.g_varchar2)
443 and nvl(hr_scl_shd.g_old_rec.segment24, hr_api.g_varchar2) <>
444 nvl(p_segment24, hr_api.g_varchar2) )
445 or ( (nvl(p_segment25, hr_api.g_varchar2) <> hr_api.g_varchar2)
446 and nvl(hr_scl_shd.g_old_rec.segment25, hr_api.g_varchar2) <>
447 nvl(p_segment25, hr_api.g_varchar2) )
448 or ( (nvl(p_segment26, hr_api.g_varchar2) <> hr_api.g_varchar2)
449 and nvl(hr_scl_shd.g_old_rec.segment26, hr_api.g_varchar2) <>
450 nvl(p_segment26, hr_api.g_varchar2) )
451 or ( (nvl(p_segment27, hr_api.g_varchar2) <> hr_api.g_varchar2)
452 and nvl(hr_scl_shd.g_old_rec.segment27, hr_api.g_varchar2) <>
453 nvl(p_segment27, hr_api.g_varchar2) )
454 or ( (nvl(p_segment28, hr_api.g_varchar2) <> hr_api.g_varchar2)
455 and nvl(hr_scl_shd.g_old_rec.segment28, hr_api.g_varchar2) <>
456 nvl(p_segment28, hr_api.g_varchar2) )
457 or ( (nvl(p_segment29, hr_api.g_varchar2) <> hr_api.g_varchar2)
458 and nvl(hr_scl_shd.g_old_rec.segment29, hr_api.g_varchar2) <>
459 nvl(p_segment29, hr_api.g_varchar2) )
460 or ( (nvl(p_segment30, hr_api.g_varchar2) <> hr_api.g_varchar2)
461 and nvl(hr_scl_shd.g_old_rec.segment30, hr_api.g_varchar2) <>
462 nvl(p_segment30, hr_api.g_varchar2) )
463 ) then
464 --
465 hr_utility.set_location( l_proc, 60 );
466 --
467 l_scl_updated_flag := TRUE;
468 --
469 else
470 --
471 hr_utility.set_location( l_proc, 65 );
472 --
473 l_scl_updated_flag := FALSE;
474 --
475 end if;
476 elsif per_asg_shd.g_old_rec.soft_coding_keyflex_id is null then
477 --
478 -- Otherwise, if the assignment does not currently have SCL information
479 -- check whether any of the SCL segments are being set for the first time
480 --
481 hr_utility.set_location( l_proc, 70 );
482 --
483 if nvl(p_segment1, hr_api.g_varchar2) <> hr_api.g_varchar2
484 or nvl(p_segment2, hr_api.g_varchar2) <> hr_api.g_varchar2
485 or nvl(p_segment3, hr_api.g_varchar2) <> hr_api.g_varchar2
486 or nvl(p_segment4, hr_api.g_varchar2) <> hr_api.g_varchar2
487 or nvl(p_segment5, hr_api.g_varchar2) <> hr_api.g_varchar2
488 or nvl(p_segment6, hr_api.g_varchar2) <> hr_api.g_varchar2
489 or nvl(p_segment7, hr_api.g_varchar2) <> hr_api.g_varchar2
490 or nvl(p_segment8, hr_api.g_varchar2) <> hr_api.g_varchar2
491 or nvl(p_segment9, hr_api.g_varchar2) <> hr_api.g_varchar2
492 or nvl(p_segment10, hr_api.g_varchar2) <> hr_api.g_varchar2
493 or nvl(p_segment11, hr_api.g_varchar2) <> hr_api.g_varchar2
494 or nvl(p_segment12, hr_api.g_varchar2) <> hr_api.g_varchar2
495 or nvl(p_segment13, hr_api.g_varchar2) <> hr_api.g_varchar2
496 or nvl(p_segment14, hr_api.g_varchar2) <> hr_api.g_varchar2
497 or nvl(p_segment15, hr_api.g_varchar2) <> hr_api.g_varchar2
498 or nvl(p_segment16, hr_api.g_varchar2) <> hr_api.g_varchar2
499 or nvl(p_segment17, hr_api.g_varchar2) <> hr_api.g_varchar2
500 or nvl(p_segment18, hr_api.g_varchar2) <> hr_api.g_varchar2
501 or nvl(p_segment19, hr_api.g_varchar2) <> hr_api.g_varchar2
502 or nvl(p_segment20, hr_api.g_varchar2) <> hr_api.g_varchar2
503 or nvl(p_segment21, hr_api.g_varchar2) <> hr_api.g_varchar2
504 or nvl(p_segment22, hr_api.g_varchar2) <> hr_api.g_varchar2
505 or nvl(p_segment23, hr_api.g_varchar2) <> hr_api.g_varchar2
506 or nvl(p_segment24, hr_api.g_varchar2) <> hr_api.g_varchar2
507 or nvl(p_segment25, hr_api.g_varchar2) <> hr_api.g_varchar2
508 or nvl(p_segment26, hr_api.g_varchar2) <> hr_api.g_varchar2
509 or nvl(p_segment27, hr_api.g_varchar2) <> hr_api.g_varchar2
510 or nvl(p_segment28, hr_api.g_varchar2) <> hr_api.g_varchar2
511 or nvl(p_segment29, hr_api.g_varchar2) <> hr_api.g_varchar2
512 or nvl(p_segment30, hr_api.g_varchar2) <> hr_api.g_varchar2 then
513 --
514 hr_utility.set_location( l_proc, 75 );
515 --
516 l_scl_updated_flag := TRUE;
517 --
518 else
519 --
520 hr_utility.set_location( l_proc, 80 );
521 --
522 l_scl_updated_flag := FALSE;
523 --
524 end if;
525 end if;
526 --
527 if ( l_scl_updated_flag
528 or ((nvl(p_frequency, hr_api.g_varchar2) <> hr_api.g_varchar2)
529 and nvl(per_asg_shd.g_old_rec.frequency, hr_api.g_varchar2) <>
530 nvl(p_frequency, hr_api.g_varchar2) )
531 or ((nvl(p_normal_hours, hr_api.g_number) <> hr_api.g_number)
532 and nvl(per_asg_shd.g_old_rec.normal_hours, hr_api.g_number) <>
533 nvl(p_normal_hours, hr_api.g_number) )
534 or ((nvl(p_time_normal_finish, hr_api.g_varchar2) <> hr_api.g_varchar2)
535 and nvl(per_asg_shd.g_old_rec.time_normal_finish,hr_api.g_varchar2) <>
536 nvl(p_time_normal_finish,hr_api.g_varchar2) )
537 or ((nvl(p_time_normal_start, hr_api.g_varchar2) <> hr_api.g_varchar2)
538 and nvl(per_asg_shd.g_old_rec.time_normal_start, hr_api.g_varchar2) <>
539 nvl(p_time_normal_start, hr_api.g_varchar2) )
540 ) then
541 --
542 hr_utility.set_location( l_proc, 85 );
543 --
544 hr_assignment_api.update_emp_asg
545 (p_validate => FALSE
546 ,p_effective_date => p_effective_date
547 ,p_datetrack_update_mode => 'CORRECTION'
548 ,p_assignment_id => p_assignment_id
549 ,p_object_version_number => l_object_version_number
550 ,p_frequency => p_frequency
551 ,p_normal_hours => p_normal_hours
552 ,p_time_normal_finish => p_time_normal_finish
553 ,p_time_normal_start => p_time_normal_start
554 ,p_segment1 => p_segment1
555 ,p_segment2 => p_segment2
556 ,p_segment3 => p_segment3
557 ,p_segment4 => p_segment4
558 ,p_segment5 => p_segment5
559 ,p_segment6 => p_segment6
560 ,p_segment7 => p_segment7
561 ,p_segment8 => p_segment8
562 ,p_segment9 => p_segment9
563 ,p_segment10 => p_segment10
564 ,p_segment11 => p_segment11
565 ,p_segment12 => p_segment12
566 ,p_segment13 => p_segment13
567 ,p_segment14 => p_segment14
568 ,p_segment15 => p_segment15
569 ,p_segment16 => p_segment16
570 ,p_segment17 => p_segment17
571 ,p_segment18 => p_segment18
572 ,p_segment19 => p_segment19
573 ,p_segment20 => p_segment20
574 ,p_segment21 => p_segment21
575 ,p_segment22 => p_segment22
576 ,p_segment23 => p_segment23
577 ,p_segment24 => p_segment24
578 ,p_segment25 => p_segment25
579 ,p_segment26 => p_segment26
580 ,p_segment27 => p_segment27
581 ,p_segment28 => p_segment28
582 ,p_segment29 => p_segment29
583 ,p_segment30 => p_segment30
584 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
585 ,p_comment_id => l_comment_id
586 ,p_effective_start_date => l_effective_start_date
587 ,p_effective_end_date => l_effective_end_date
588 ,p_concatenated_segments => l_concatenated_segments
589 ,p_no_managers_warning => l_no_managers_warning
590 ,p_other_manager_warning => l_other_manager_warning );
591 end if;
592 --
593 hr_utility.set_location( l_proc, 87 );
594 --
595 -- Call the post-core move assignment customer stub
596 -- NOTE : if l_effective_start_date is null assign l_old_asg_eff_start_date
597 -- to the p_new_asg_eff_start_date. l_effective_start_date will be null,
598 -- when p_validate = TRUE.
599 --
600 l_effective_start_date :=
601 nvl(l_effective_start_date,l_old_asg_eff_start_date);
602 --
603 hr_mass_move_cus.post_move_emp_asg
604 (p_validate => FALSE
605 ,p_old_asg_eff_start_date => l_old_asg_eff_start_date
606 ,p_new_asg_eff_start_date => l_effective_start_date
607 ,p_assignment_id => p_assignment_id
608 ,p_old_asg_object_version_num => l_old_asg_object_version_num
609 ,p_new_asg_object_version_num => l_object_version_number
610 ,p_mass_move_id => p_mass_move_id
611 );
612 --
613 -- When in validation only mode raise the Validate_Enabled exception
614 --
615 if p_validate then
616 raise hr_api.validate_enabled;
617 end if;
618 --
619 p_object_version_number := l_object_version_number;
620 p_soft_coding_keyflex_id := l_soft_coding_keyflex_id;
621 p_effective_start_date := l_effective_start_date;
622 p_effective_end_date := l_effective_end_date;
623 p_concatenated_segments := l_concatenated_segments;
624 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
625 p_other_manager_warning := l_other_manager_warning;
626 p_entries_changed_warning := l_entries_changed_warning;
627 p_spp_delete_warning := l_spp_delete_warning;
628 --
629 hr_utility.set_location(' Leaving:'||l_proc, 997);
630 --
631 exception
632 --
633 when hr_api.validate_enabled then
634 --
635 -- As the Validate_Enabled exception has been raised
636 -- we must rollback to the savepoint
637 --
638 ROLLBACK TO move_emp_asg;
639 --
640 -- Only set output warning arguments
641 -- (Any key or derived arguments must be set to null
642 -- when validation only mode is being used.)
643 --
644 p_object_version_number := p_object_version_number;
645 p_soft_coding_keyflex_id := null;
646 p_effective_start_date := null;
647 p_effective_end_date := null;
648 p_concatenated_segments := null;
649 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
650 p_other_manager_warning := l_other_manager_warning;
651 p_entries_changed_warning := l_entries_changed_warning;
652 p_spp_delete_warning := l_spp_delete_warning;
653 --
654 hr_utility.set_location(' Leaving:'||l_proc, 998);
655 --
656 when others then
657 --
658 -- A validation or unexpected error has occurred
659 --
660 -- Added as part of the fix to bug 632479
661 --
662 ROLLBACK TO move_emp_asg;
663 --
664 -- set in out parameters and set out parameters
665 --
666 p_object_version_number := l_ovn;
667 p_soft_coding_keyflex_id := null;
668 p_effective_start_date := null;
669 p_effective_end_date := null;
670 p_concatenated_segments := null;
671 p_org_now_no_manager_warning := false;
672 p_other_manager_warning := false;
673 p_entries_changed_warning := null;
674 p_spp_delete_warning := false;
675 --
676 hr_utility.set_location(' Leaving:'||l_proc, 999);
677 --
678 raise;
679 --
680 end move_emp_asg;
681 --
682 -- ----------------------------------------------------------------------------
683 -- |----------------------< move_position >-----------------------------------|
684 -- ----------------------------------------------------------------------------
685 --
686 procedure move_position
687 (p_validate in boolean default false
688 ,p_batch_run_number in number
689 ,p_position_id in number
690 ,p_job_id in number
691 ,p_object_version_number in out nocopy number
692 ,p_date_effective in date
693 ,p_organization_id in number
694 ,p_segment1 in varchar2 default null
695 ,p_segment2 in varchar2 default null
696 ,p_segment3 in varchar2 default null
697 ,p_segment4 in varchar2 default null
698 ,p_segment5 in varchar2 default null
699 ,p_segment6 in varchar2 default null
700 ,p_segment7 in varchar2 default null
701 ,p_segment8 in varchar2 default null
702 ,p_segment9 in varchar2 default null
703 ,p_segment10 in varchar2 default null
704 ,p_segment11 in varchar2 default null
705 ,p_segment12 in varchar2 default null
706 ,p_segment13 in varchar2 default null
707 ,p_segment14 in varchar2 default null
708 ,p_segment15 in varchar2 default null
709 ,p_segment16 in varchar2 default null
710 ,p_segment17 in varchar2 default null
711 ,p_segment18 in varchar2 default null
712 ,p_segment19 in varchar2 default null
713 ,p_segment20 in varchar2 default null
714 ,p_segment21 in varchar2 default null
715 ,p_segment22 in varchar2 default null
716 ,p_segment23 in varchar2 default null
717 ,p_segment24 in varchar2 default null
718 ,p_segment25 in varchar2 default null
719 ,p_segment26 in varchar2 default null
720 ,p_segment27 in varchar2 default null
721 ,p_segment28 in varchar2 default null
722 ,p_segment29 in varchar2 default null
723 ,p_segment30 in varchar2 default null
724 ,p_concat_segments in varchar2 default null
725 ,p_deactivate_old_position in boolean default false
726 ,p_mass_move_id in number default null
727 ,p_time_normal_start in varchar2
728 ,p_time_normal_finish in varchar2
729 ,p_normal_hours in number
730 ,p_frequency in varchar2
731 ,p_location_id in number
732 ,p_new_position_id out nocopy number
733 ,p_new_job_id out nocopy number
734 ,p_new_object_version_number out nocopy number
735 ,p_valid_grades_changed_warning out nocopy boolean
736 ,p_pos_exists_warning out nocopy boolean
737 ,p_pos_date_range_warning out nocopy boolean
738 ,p_pos_pending_close_warning out nocopy boolean
739 ,p_pos_jbe_not_moved_warning out nocopy boolean
740 ,p_pos_vac_not_moved_warning out nocopy boolean
741 ) is
742 --
743 -- Declare cursors and local variables
744 --
745
746 l_proc varchar2(72) := g_package||'move_position';
747 l_effective_start_date date;
748 l_ovn number := p_object_version_number;
749 l_effective_end_date date;
750 l_business_group_id number;
751 l_position_definition_id number(15);
752 l_name varchar2(240);
753 l_successor_position_id number(15);
754 l_relief_position_id number(15);
755 l_comments varchar2(2000);
756 l_probation_period number(22,2);
757 l_probation_period_units varchar2(30);
758 l_replacement_required_flag varchar2(30);
759 l_attribute_category varchar2(30);
760 l_attribute1 varchar2(150);
761 l_attribute2 varchar2(150);
762 l_attribute3 varchar2(150);
763 l_attribute4 varchar2(150);
764 l_attribute5 varchar2(150);
765 l_attribute6 varchar2(150);
766 l_attribute7 varchar2(150);
767 l_attribute8 varchar2(150);
768 l_attribute9 varchar2(150);
769 l_attribute10 varchar2(150);
770 l_attribute11 varchar2(150);
771 l_attribute12 varchar2(150);
772 l_attribute13 varchar2(150);
773 l_attribute14 varchar2(150);
774 l_attribute15 varchar2(150);
775 l_attribute16 varchar2(150);
776 l_attribute17 varchar2(150);
777 l_attribute18 varchar2(150);
778 l_attribute19 varchar2(150);
779 l_attribute20 varchar2(150);
780 l_date_end date;
781 l_valid_grades_changed_warning boolean;
782 l_pos_exists_warning boolean;
783 l_pos_date_range_warning boolean;
784 l_pos_pending_close_warning boolean;
785 l_pos_jbe_not_moved_warning boolean;
786 l_pos_vac_not_moved_warning boolean;
787 l_new_position_id number(15);
788 l_new_job_id number(15);
789 l_new_object_version_number number(15);
790 l_analysis_criteria_id number(15);
791 l_job_requirement_id number(15);
792 l_jbr_object_version_number number(15);
793 l_old_position_def number(15);
794 l_old_name varchar2(240);
795 l_valid_grade_id number(15);
796 l_vgr_object_version_number number(15);
797 l_object_version_number number(15);
798 l_new_pos_date_effective date;
799 l_new_position_date_end date;
800 l_new_pos_org number(15);
801 l_exists varchar2(1);
802 l_dummy_line_id number(15);
803 l_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
804
805 p_effective_date date;
806 p_availability_status_id number(15);
807 p_entry_step_id number(15);
808 p_entry_grade_rule_id number(15);
809 p_pay_freq_payroll_id number(15);
810 p_position_transaction_id number(15);
811 p_prior_position_id number(15);
812 p_entry_grade_id number(15);
813 p_supervisor_position_id number(15);
814 p_amendment_date date;
815 p_amendment_recommendation varchar2(240);
816 p_amendment_ref_number varchar2(30);
817 p_bargaining_unit_cd varchar2(80);
818 p_current_job_prop_end_date date;
819 p_current_org_prop_end_date date;
820 p_avail_status_prop_end_date date;
821 p_earliest_hire_date date;
822 p_fill_by_date date;
823 p_fte number(9,2);
824 p_max_persons number(9,2);
825 p_overlap_period number(15);
826 p_overlap_unit_cd varchar2(80);
827 p_pay_term_end_day_cd varchar2(80);
828 p_pay_term_end_month_cd varchar2(80);
829 p_permanent_temporary_flag varchar2(80);
830 p_permit_recruitment_flag varchar2(80);
831 p_position_type varchar2(80);
832 p_posting_description varchar2(240);
833 p_review_flag varchar2(80);
834 p_seasonal_flag varchar2(80);
835 p_security_requirements varchar2(240);
836 p_status varchar2(80);
837 p_term_start_day_cd varchar2(80);
838 p_term_start_month_cd varchar2(80);
839 p_update_source_cd varchar2(80);
840 p_works_council_approval_flag varchar2(80);
841 p_work_period_type_cd varchar2(80);
842 p_work_term_end_day_cd varchar2(80);
843 p_work_term_end_month_cd varchar2(80);
844 p_proposed_fte_for_layoff number(9,2);
845 p_proposed_date_for_layoff date;
846 p_pay_basis_id number(15);
847 p_supervisor_id number(15);
848 p_copied_to_old_table_flag varchar2(80);
849 --
850 -- Added as part of fix for bug 2943725
851 --
852 l_system_type_cd per_shared_types.system_type_cd%TYPE;
853 l_current_system_type_cd per_shared_types.system_type_cd%TYPE;
854 --
855 cursor csr_old_pos is
856 select
857 position_definition_id
858 ,name
859 ,business_group_id
860 ,successor_position_id
861 ,relief_position_id
862 ,comments
863 ,probation_period
864 ,probation_period_unit_cd
865 ,replacement_required_flag
866 ,attribute_category
867 ,attribute1
868 ,attribute2
869 ,attribute3
870 ,attribute4
871 ,attribute5
872 ,attribute6
873 ,attribute7
874 ,attribute8
875 ,attribute9
876 ,attribute10
877 ,attribute11
878 ,attribute12
879 ,attribute13
880 ,attribute14
881 ,attribute15
882 ,attribute16
883 ,attribute17
884 ,attribute18
885 ,attribute19
886 ,attribute20
887 --,effective_date
888 ,availability_status_id
889 ,entry_step_id
890 ,entry_grade_rule_id
891 ,pay_freq_payroll_id
892 ,position_transaction_id
893 ,prior_position_id
894 ,entry_grade_id
895 ,p_supervisor_position_id
896 ,amendment_date
897 ,amendment_recommendation
898 ,amendment_ref_number
899 ,bargaining_unit_cd
900 ,current_job_prop_end_date
901 ,current_org_prop_end_date
902 ,avail_status_prop_end_date
903 ,earliest_hire_date
904 ,fill_by_date
905 ,fte
906 ,max_persons
907 ,overlap_period
908 ,overlap_unit_cd
909 ,pay_term_end_day_cd
910 ,pay_term_end_month_cd
911 ,permanent_temporary_flag
912 ,permit_recruitment_flag
913 ,position_type
914 ,posting_description
915 ,review_flag
916 ,seasonal_flag
917 ,security_requirements
918 ,status
919 ,term_start_day_cd
920 ,term_start_month_cd
921 ,update_source_cd
922 ,works_council_approval_flag
923 ,work_period_type_cd
924 ,work_term_end_day_cd
925 ,work_term_end_month_cd
926 ,proposed_fte_for_layoff
927 ,proposed_date_for_layoff
928 ,pay_basis_id
929 ,supervisor_id
930 --,copied_to_old_table_flag
931 from hr_positions_f
932 where position_id = p_position_id
933 and p_date_effective
934 between effective_start_date
935 and effective_end_date;
936 --
937 cursor csr_new_pos is
938 select position_id
939 ,job_id
940 ,object_version_number
941 ,date_effective
942 ,date_end
943 ,organization_id
944 from hr_positions_f
945 where position_definition_id = l_position_definition_id
946 and business_group_id = l_business_group_id
947 and p_date_effective
948 between effective_start_date
949 and effective_end_date;
950 --
951 cursor csr_jbr is
952 select pjr.analysis_criteria_id
953 ,business_group_id
954 ,attribute_category
955 ,attribute1
956 ,attribute2
957 ,attribute3
958 ,attribute4
959 ,attribute5
960 ,attribute6
961 ,attribute7
962 ,attribute8
963 ,attribute9
964 ,attribute10
965 ,attribute11
966 ,attribute12
967 ,attribute13
968 ,attribute14
969 ,attribute15
970 ,attribute16
971 ,attribute17
972 ,attribute18
973 ,attribute19
974 ,attribute20
975 ,comments
976 ,essential
977 ,job_id
978 ,position_id
979 ,id_flex_num
980 ,segment1
981 ,segment2
982 ,segment3
983 ,segment4
984 ,segment5
985 ,segment6
986 ,segment7
987 ,segment8
988 ,segment9
989 ,segment10
990 ,segment11
991 ,segment12
992 ,segment13
993 ,segment14
994 ,segment15
995 ,segment16
996 ,segment17
997 ,segment18
998 ,segment19
999 ,segment20
1000 ,segment21
1001 ,segment22
1002 ,segment23
1003 ,segment24
1004 ,segment25
1005 ,segment26
1006 ,segment27
1007 ,segment28
1008 ,segment29
1009 ,segment30
1010 from per_job_requirements pjr, per_analysis_criteria pac
1011 where pjr.position_id = p_position_id
1012 and pjr.analysis_criteria_id = pac.analysis_criteria_id
1013 and not exists
1014 (select 'x'
1015 from per_job_requirements jbr2
1016 where jbr2.analysis_criteria_id = pjr.analysis_criteria_id
1017 and jbr2.position_id = l_new_position_id
1018 and jbr2.business_group_id = l_business_group_id);
1019 --
1020 cursor csr_vgr is
1021 select mmvgr.target_grade_id
1022 ,mmvgr.position_id
1023 ,mmvgr.attribute_category
1024 ,mmvgr.attribute1
1025 ,mmvgr.attribute2
1026 ,mmvgr.attribute3
1027 ,mmvgr.attribute4
1028 ,mmvgr.attribute5
1029 ,mmvgr.attribute6
1030 ,mmvgr.attribute7
1031 ,mmvgr.attribute8
1032 ,mmvgr.attribute9
1033 ,mmvgr.attribute10
1034 ,mmvgr.attribute11
1035 ,mmvgr.attribute12
1036 ,mmvgr.attribute13
1037 ,mmvgr.attribute14
1038 ,mmvgr.attribute15
1039 ,mmvgr.attribute16
1040 ,mmvgr.attribute17
1041 ,mmvgr.attribute18
1042 ,mmvgr.attribute19
1043 ,mmvgr.attribute20
1044 from per_mm_valid_grades mmvgr
1045 where mmvgr.mass_move_id = p_mass_move_id
1046 and mmvgr.position_id = p_position_id
1047 and not exists
1048 (select vgr.grade_id
1049 from per_valid_grades vgr
1050 where vgr.position_id = l_new_position_id
1051 and vgr.grade_id = mmvgr.target_grade_id
1052 and vgr.business_group_id = l_business_group_id);
1053 --
1054 cursor csr_pos_job_eval is
1055 select null
1056 from per_job_evaluations jbe
1057 where jbe.position_id = p_position_id;
1058 --
1059 cursor csr_pos_vacancy is
1060 select null
1061 from per_vacancies vac
1062 where vac.position_id = p_position_id
1063 and p_date_effective between vac.date_from
1064 and nvl(vac.date_to, hr_api.g_eot);
1065 --
1066 cursor isdel is
1067 select pbg.position_structure
1068 from per_business_groups pbg
1069 where pbg.business_group_id = l_business_group_id;
1070 --
1071 -- Moved and modified as part of fix for bug 2943725
1072 --
1073 cursor csr_avail_status(p_system_type_cd IN per_shared_types.system_type_cd%TYPE) is
1074 select shared_type_id
1075 from per_shared_types
1076 where lookup_type = 'POSITION_AVAILABILITY_STATUS'
1077 and system_type_cd = p_system_type_cd
1078 and business_group_id IS NULL;
1079 --
1080 -- Add as part of fix for bug 2943725
1081 --
1082 cursor csr_current_status is
1083 select pst.system_type_cd
1084 from hr_positions_f hpf,
1085 per_shared_types pst
1086 where lookup_type = 'POSITION_AVAILABILITY_STATUS'
1087 and pst.shared_type_id = hpf.availability_status_id
1088 and hpf.position_id = p_position_id
1089 and p_date_effective between hpf.effective_start_date
1090 and hpf.effective_end_date;
1091 --
1092 cursor csr_pos_current_esd(p_position_id number, p_effective_date date) is
1093 select 'CORRECTION'
1094 from hr_all_positions_f
1095 where position_id = p_position_id
1096 and p_effective_date = effective_start_date
1097 and effective_end_date = hr_general.end_of_time;
1098 --
1099 l_pos_dt_mode varchar2(30);
1100 --
1101 begin
1102 --
1103 hr_utility.set_location('Entering:'|| l_proc, 5);
1104 --
1105 -- Issue a savepoint
1106 --
1107 savepoint move_position;
1108 --
1109 l_object_version_number := p_object_version_number;
1110 --
1111 -- Make sure p_position_id is not null
1112 --
1113 hr_api.mandatory_arg_error(p_api_name => l_proc,
1114 p_argument => 'p_position_id',
1115 p_argument_value => p_position_id);
1116 --
1117 -- Get business_group_id, and all attributes of 'old' position.
1118 --
1119 hr_utility.set_location(l_proc, 10);
1120 --
1121 open csr_old_pos;
1122 fetch csr_old_pos
1123 into
1124 l_old_position_def
1125 ,l_old_name
1126 ,l_business_group_id
1127 ,l_successor_position_id
1128 ,l_relief_position_id
1129 ,l_comments
1130 ,l_probation_period
1131 ,l_probation_period_units
1132 ,l_replacement_required_flag
1133 ,l_attribute_category
1134 ,l_attribute1
1135 ,l_attribute2
1136 ,l_attribute3
1137 ,l_attribute4
1138 ,l_attribute5
1139 ,l_attribute6
1140 ,l_attribute7
1141 ,l_attribute8
1142 ,l_attribute9
1143 ,l_attribute10
1144 ,l_attribute11
1145 ,l_attribute12
1146 ,l_attribute13
1147 ,l_attribute14
1148 ,l_attribute15
1149 ,l_attribute16
1150 ,l_attribute17
1151 ,l_attribute18
1152 ,l_attribute19
1153 ,l_attribute20
1154 ,p_availability_status_id
1155 ,p_entry_step_id
1156 ,p_entry_grade_rule_id
1157 ,p_pay_freq_payroll_id
1158 ,p_position_transaction_id
1159 ,p_prior_position_id
1160 ,p_entry_grade_id
1161 ,p_supervisor_position_id
1162 ,p_amendment_date
1163 ,p_amendment_recommendation
1164 ,p_amendment_ref_number
1165 ,p_bargaining_unit_cd
1166 ,p_current_job_prop_end_date
1167 ,p_current_org_prop_end_date
1168 ,p_avail_status_prop_end_date
1169 ,p_earliest_hire_date
1170 ,p_fill_by_date
1171 ,p_fte
1172 ,p_max_persons
1173 ,p_overlap_period
1174 ,p_overlap_unit_cd
1175 ,p_pay_term_end_day_cd
1176 ,p_pay_term_end_month_cd
1177 ,p_permanent_temporary_flag
1178 ,p_permit_recruitment_flag
1179 ,p_position_type
1180 ,p_posting_description
1181 ,p_review_flag
1182 ,p_seasonal_flag
1183 ,p_security_requirements
1184 ,p_status
1185 ,p_term_start_day_cd
1186 ,p_term_start_month_cd
1187 ,p_update_source_cd
1188 ,p_works_council_approval_flag
1189 ,p_work_period_type_cd
1190 ,p_work_term_end_day_cd
1191 ,p_work_term_end_month_cd
1192 ,p_proposed_fte_for_layoff
1193 ,p_proposed_date_for_layoff
1194 ,p_pay_basis_id
1195 ,p_supervisor_id;
1196 --
1197 if csr_old_pos%notfound then
1198 --
1199 hr_utility.set_location(l_proc, 20);
1200 --
1201 close csr_old_pos;
1202 hr_utility.set_message(801, 'HR_51093_POS_NOT_EXIST');
1203 hr_utility.raise_error;
1204 --
1205 else
1206 close csr_old_pos;
1207 end if;
1208 --
1209 hr_utility.set_location(l_proc, 30);
1210
1211 open isdel;
1212 fetch isdel into l_flex_num;
1213 if isdel%notfound then
1214 close isdel;
1215 --
1216 -- the flex structure has not been found
1217 --
1218 hr_utility.set_message(801, 'HR_7471_FLEX_POS_INVLALID_ID');
1219 hr_utility.raise_error;
1220 end if;
1221 close isdel;
1222 hr_utility.set_location(l_proc, 40);
1223 --
1224 -- Determine if the 'new' position exists by calling ins_or_sel
1225 -- to get position definition id
1226 --
1227 hr_kflex_utility.ins_or_sel_keyflex_comb
1228 (p_appl_short_name => 'PER'
1229 ,p_flex_code => 'POS'
1230 ,p_flex_num => l_flex_num
1231 ,p_segment1 => p_segment1
1232 ,p_segment2 => p_segment2
1233 ,p_segment3 => p_segment3
1234 ,p_segment4 => p_segment4
1235 ,p_segment5 => p_segment5
1236 ,p_segment6 => p_segment6
1237 ,p_segment7 => p_segment7
1238 ,p_segment8 => p_segment8
1239 ,p_segment9 => p_segment9
1240 ,p_segment10 => p_segment10
1241 ,p_segment11 => p_segment11
1242 ,p_segment12 => p_segment12
1243 ,p_segment13 => p_segment13
1244 ,p_segment14 => p_segment14
1245 ,p_segment15 => p_segment15
1246 ,p_segment16 => p_segment16
1247 ,p_segment17 => p_segment17
1248 ,p_segment18 => p_segment18
1249 ,p_segment19 => p_segment19
1250 ,p_segment20 => p_segment20
1251 ,p_segment21 => p_segment21
1252 ,p_segment22 => p_segment22
1253 ,p_segment23 => p_segment23
1254 ,p_segment24 => p_segment24
1255 ,p_segment25 => p_segment25
1256 ,p_segment26 => p_segment26
1257 ,p_segment27 => p_segment27
1258 ,p_segment28 => p_segment28
1259 ,p_segment29 => p_segment29
1260 ,p_segment30 => p_segment30
1261 ,p_concat_segments_in => p_concat_segments
1262 ,p_ccid => l_position_definition_id
1263 ,p_concat_segments_out => l_name
1264 );
1265 --
1266 -- Check if 'new' position already exists within date range
1267 --
1268 hr_utility.set_location(l_proc, 50);
1269 --
1270 open csr_new_pos;
1271 fetch csr_new_pos into
1272 l_new_position_id
1273 ,l_new_job_id
1274 ,l_new_object_version_number
1275 ,l_new_pos_date_effective
1276 ,l_new_position_date_end
1277 ,l_new_pos_org;
1278 --
1279 if csr_new_pos%found then
1280 -- Check if 'new' position exists but in a different organization
1281 hr_utility.set_location(l_proc, 60);
1282 --
1283 -- Warn if 'new' position exists but in a different organization
1284 --
1285 if l_new_pos_org <> p_organization_id then
1286 l_pos_exists_warning := TRUE;
1287 --
1288 -- Warn if mass_move_effective date is outside range of target position.
1289 --
1290 elsif (p_date_effective < l_new_pos_date_effective or
1291 p_date_effective > l_new_position_date_end) then
1292 l_pos_date_range_warning := TRUE;
1293 --
1294 -- Warn if the target position date has a future end date.
1295 --
1296 elsif l_new_position_date_end is not null then
1297 l_pos_pending_close_warning := TRUE;
1298 --
1299 end if; --l_new_pos_org <> p_organization_id
1300 end if; --csr_new_pos%found
1301 close csr_new_pos;
1302 --
1303 --
1304 open csr_pos_job_eval;
1305 fetch csr_pos_job_eval
1306 into l_exists;
1307 if csr_pos_job_eval%found then
1308 l_pos_jbe_not_moved_warning := TRUE;
1309 end if;
1310 close csr_pos_job_eval;
1311 --
1312 open csr_pos_vacancy;
1313 fetch csr_pos_vacancy
1314 into l_exists;
1315 if csr_pos_vacancy%found then
1316 l_pos_vac_not_moved_warning := TRUE;
1317 end if;
1318 close csr_pos_vacancy;
1319 --
1320 -- Call the pre_move_position stub for any customer specific processing
1321 --
1322 hr_utility.set_location(l_proc, 70);
1323 --
1324 hr_mass_move_cus.pre_move_position
1325 (p_position_id => p_position_id
1326 ,p_object_version_number => l_object_version_number
1327 ,p_date_effective => p_date_effective
1328 ,p_organization_id => p_organization_id
1329 ,p_business_group_id => l_business_group_id
1330 ,p_segment1 => p_segment1
1331 ,p_segment2 => p_segment2
1332 ,p_segment3 => p_segment3
1333 ,p_segment4 => p_segment4
1334 ,p_segment5 => p_segment5
1335 ,p_segment6 => p_segment6
1336 ,p_segment7 => p_segment7
1337 ,p_segment8 => p_segment8
1338 ,p_segment9 => p_segment9
1339 ,p_segment10 => p_segment10
1340 ,p_segment11 => p_segment11
1341 ,p_segment12 => p_segment12
1342 ,p_segment13 => p_segment13
1343 ,p_segment14 => p_segment14
1344 ,p_segment15 => p_segment15
1345 ,p_segment16 => p_segment16
1346 ,p_segment17 => p_segment17
1347 ,p_segment18 => p_segment18
1348 ,p_segment19 => p_segment19
1349 ,p_segment20 => p_segment20
1350 ,p_segment21 => p_segment21
1351 ,p_segment22 => p_segment22
1352 ,p_segment23 => p_segment23
1353 ,p_segment24 => p_segment24
1354 ,p_segment25 => p_segment25
1355 ,p_segment26 => p_segment26
1356 ,p_segment27 => p_segment27
1357 ,p_segment28 => p_segment28
1358 ,p_segment29 => p_segment29
1359 ,p_segment30 => p_segment30
1360 ,p_deactivate_old_position => p_deactivate_old_position
1361 ,p_mass_move_id => p_mass_move_id
1362 );
1363 --
1364 -- If the 'new' position did not exist then create the 'new' position
1365 --
1366 if l_new_position_id is null then
1367 --
1368 hr_utility.set_location(l_proc, 80);
1369 --
1370 hr_position_api.create_position
1371 (p_validate => FALSE
1372 ,p_position_id => l_new_position_id
1373 ,p_effective_start_date => l_effective_start_date
1374 ,p_effective_end_date => l_effective_end_date
1375 ,p_position_definition_id => l_position_definition_id
1376 ,p_name => l_name
1377 ,p_object_version_number => l_new_object_version_number
1378 ,p_job_id => p_job_id
1379 ,p_organization_id => p_organization_id
1380 ,p_effective_date => p_date_effective --p_effective_date
1381 ,p_date_effective => p_date_effective
1382 ,p_availability_status_id => p_availability_status_id
1383 ,p_business_group_id => l_business_group_id
1384 ,p_entry_step_id => p_entry_step_id
1385 ,p_entry_grade_rule_id => p_entry_grade_rule_id
1386 ,p_location_id => p_location_id
1387 ,p_pay_freq_payroll_id => p_pay_freq_payroll_id
1388 ,p_position_transaction_id => p_position_transaction_id
1389 ,p_prior_position_id => p_prior_position_id
1390 ,p_relief_position_id => l_relief_position_id
1391 ,p_entry_grade_id => p_entry_grade_id
1392 ,p_successor_position_id => l_successor_position_id
1393 ,p_supervisor_position_id => p_supervisor_position_id
1394 ,p_amendment_date => p_amendment_date
1395 ,p_amendment_recommendation => p_amendment_recommendation
1396 ,p_amendment_ref_number => p_amendment_ref_number
1397 ,p_bargaining_unit_cd => p_bargaining_unit_cd
1398 ,p_comments => l_comments
1399 ,p_current_job_prop_end_date => p_current_job_prop_end_date
1400 ,p_current_org_prop_end_date => p_current_org_prop_end_date
1401 ,p_avail_status_prop_end_date => p_avail_status_prop_end_date
1402 ,p_date_end => l_date_end
1403 ,p_earliest_hire_date => p_earliest_hire_date
1404 ,p_fill_by_date => p_fill_by_date
1405 ,p_frequency => p_frequency
1406 ,p_fte => p_fte
1407 ,p_max_persons => p_max_persons
1408 ,p_overlap_period => p_overlap_period
1409 ,p_overlap_unit_cd => p_overlap_unit_cd
1410 ,p_pay_term_end_day_cd => p_pay_term_end_day_cd
1411 ,p_pay_term_end_month_cd => p_pay_term_end_month_cd
1412 ,p_permanent_temporary_flag => p_permanent_temporary_flag
1413 ,p_permit_recruitment_flag => p_permit_recruitment_flag
1414 ,p_position_type => p_position_type
1415 ,p_posting_description => p_posting_description
1416 ,p_probation_period => l_probation_period
1417 ,p_probation_period_unit_cd => l_probation_period_units
1418 ,p_replacement_required_flag => l_replacement_required_flag
1419 ,p_review_flag => p_review_flag
1420 ,p_seasonal_flag => p_seasonal_flag
1421 ,p_security_requirements => p_security_requirements
1422 ,p_status => p_status
1423 ,p_term_start_day_cd => p_term_start_day_cd
1424 ,p_term_start_month_cd => p_term_start_month_cd
1425 ,p_time_normal_finish => p_time_normal_finish
1426 ,p_time_normal_start => p_time_normal_start
1427 ,p_update_source_cd => p_update_source_cd
1428 ,p_working_hours => p_normal_hours
1429 ,p_works_council_approval_flag=> p_works_council_approval_flag
1430 ,p_work_period_type_cd => p_work_period_type_cd
1431 ,p_work_term_end_day_cd => p_work_term_end_day_cd
1432 ,p_work_term_end_month_cd => p_work_term_end_month_cd
1433 ,p_proposed_fte_for_layoff => p_proposed_fte_for_layoff
1434 ,p_proposed_date_for_layoff => p_proposed_date_for_layoff
1435 ,p_pay_basis_id => p_pay_basis_id
1436 ,p_supervisor_id => p_supervisor_id
1437 -- ,p_copied_to_old_table_flag => p_copied_to_old_table_flag
1438 /*
1439 ,p_information_category => l_information_category
1440 ,p_information1 => l_information1
1441 ,p_information2 => l_information2
1442 ,p_information3 => l_information3
1443 ,p_information4 => l_information4
1444 ,p_information5 => l_information5
1445 ,p_information6 => l_information6
1446 ,p_information7 => l_information7
1447 ,p_information8 => l_information8
1448 ,p_information9 => l_information9
1449 ,p_information10 => l_information10
1450 ,p_information11 => l_information11
1451 ,p_information12 => l_information12
1452 ,p_information13 => l_information13
1453 ,p_information14 => l_information14
1454 ,p_information15 => l_information15
1455 ,p_information16 => l_information16
1456 ,p_information17 => l_information17
1457 ,p_information18 => l_information18
1458 ,p_information19 => l_information19
1459 ,p_information20 => l_information20
1460 ,p_information21 => l_information21
1461 ,p_information22 => l_information22
1462 ,p_information23 => l_information23
1463 ,p_information24 => l_information24
1464 ,p_information25 => l_information25
1465 ,p_information26 => l_information26
1466 ,p_information27 => l_information27
1467 ,p_information28 => l_information28
1468 ,p_information29 => l_information29
1469 ,p_information30 => l_information30
1470 */
1471 ,p_attribute_category => l_attribute_category
1472 ,p_attribute1 => l_attribute1
1473 ,p_attribute2 => l_attribute2
1474 ,p_attribute3 => l_attribute3
1475 ,p_attribute4 => l_attribute4
1476 ,p_attribute5 => l_attribute5
1477 ,p_attribute6 => l_attribute6
1478 ,p_attribute7 => l_attribute7
1479 ,p_attribute8 => l_attribute8
1480 ,p_attribute9 => l_attribute9
1481 ,p_attribute10 => l_attribute10
1482 ,p_attribute11 => l_attribute11
1483 ,p_attribute12 => l_attribute12
1484 ,p_attribute13 => l_attribute13
1485 ,p_attribute14 => l_attribute14
1486 ,p_attribute15 => l_attribute15
1487 ,p_attribute16 => l_attribute16
1488 ,p_attribute17 => l_attribute17
1489 ,p_attribute18 => l_attribute18
1490 ,p_attribute19 => l_attribute19
1491 ,p_attribute20 => l_attribute20
1492 /*
1493 ,p_attribute21 => l_attribute21
1494 ,p_attribute22 => l_attribute22
1495 ,p_attribute23 => l_attribute23
1496 ,p_attribute24 => l_attribute24
1497 ,p_attribute25 => l_attribute25
1498 ,p_attribute26 => l_attribute26
1499 ,p_attribute27 => l_attribute27
1500 ,p_attribute28 => l_attribute28
1501 ,p_attribute29 => l_attribute29
1502 ,p_attribute30 => l_attribute30
1503 */
1504 ,p_segment1 => p_segment1
1505 ,p_segment2 => p_segment2
1506 ,p_segment3 => p_segment3
1507 ,p_segment4 => p_segment4
1508 ,p_segment5 => p_segment5
1509 ,p_segment6 => p_segment6
1510 ,p_segment7 => p_segment7
1511 ,p_segment8 => p_segment8
1512 ,p_segment9 => p_segment9
1513 ,p_segment10 => p_segment10
1514 ,p_segment11 => p_segment11
1515 ,p_segment12 => p_segment12
1516 ,p_segment13 => p_segment13
1517 ,p_segment14 => p_segment14
1518 ,p_segment15 => p_segment15
1519 ,p_segment16 => p_segment16
1520 ,p_segment17 => p_segment17
1521 ,p_segment18 => p_segment18
1522 ,p_segment19 => p_segment19
1523 ,p_segment20 => p_segment20
1524 ,p_segment21 => p_segment21
1525 ,p_segment22 => p_segment22
1526 ,p_segment23 => p_segment23
1527 ,p_segment24 => p_segment24
1528 ,p_segment25 => p_segment25
1529 ,p_segment26 => p_segment26
1530 ,p_segment27 => p_segment27
1531 ,p_segment28 => p_segment28
1532 ,p_segment29 => p_segment29
1533 ,p_segment30 => p_segment30
1534 ,p_concat_segments => p_concat_segments
1535 );
1536 /*
1537 hr_position_api.create_position
1538 (p_validate => FALSE
1539 ,p_job_id => p_job_id
1540 ,p_organization_id => p_organization_id
1541 ,p_date_effective => p_date_effective
1542 ,p_successor_position_id => l_successor_position_id
1543 ,p_relief_position_id => l_relief_position_id
1544 ,p_location_id => p_location_id
1545 ,p_comments => l_comments
1546 ,p_frequency => p_frequency
1547 ,p_probation_period => l_probation_period
1548 ,p_probation_period_units => l_probation_period_units
1549 ,p_replacement_required_flag => l_replacement_required_flag
1550 ,p_time_normal_finish => p_time_normal_finish
1551 ,p_time_normal_start => p_time_normal_start
1552 ,p_working_hours => p_normal_hours
1553 ,p_attribute_category => l_attribute_category
1554 ,p_attribute1 => l_attribute1
1555 ,p_attribute2 => l_attribute2
1556 ,p_attribute3 => l_attribute3
1557 ,p_attribute4 => l_attribute4
1558 ,p_attribute5 => l_attribute5
1559 ,p_attribute6 => l_attribute6
1560 ,p_attribute7 => l_attribute7
1561 ,p_attribute8 => l_attribute8
1562 ,p_attribute9 => l_attribute9
1563 ,p_attribute10 => l_attribute10
1564 ,p_attribute11 => l_attribute11
1565 ,p_attribute12 => l_attribute12
1566 ,p_attribute13 => l_attribute13
1567 ,p_attribute14 => l_attribute14
1568 ,p_attribute15 => l_attribute15
1569 ,p_attribute16 => l_attribute16
1570 ,p_attribute17 => l_attribute17
1571 ,p_attribute18 => l_attribute18
1572 ,p_attribute19 => l_attribute19
1573 ,p_attribute20 => l_attribute20
1574 ,p_segment1 => p_segment1
1575 ,p_segment2 => p_segment2
1576 ,p_segment3 => p_segment3
1577 ,p_segment4 => p_segment4
1578 ,p_segment5 => p_segment5
1579 ,p_segment6 => p_segment6
1580 ,p_segment7 => p_segment7
1581 ,p_segment8 => p_segment8
1582 ,p_segment9 => p_segment9
1583 ,p_segment10 => p_segment10
1584 ,p_segment11 => p_segment11
1585 ,p_segment12 => p_segment12
1586 ,p_segment13 => p_segment13
1587 ,p_segment14 => p_segment14
1588 ,p_segment15 => p_segment15
1589 ,p_segment16 => p_segment16
1590 ,p_segment17 => p_segment17
1591 ,p_segment18 => p_segment18
1592 ,p_segment19 => p_segment19
1593 ,p_segment20 => p_segment20
1594 ,p_segment21 => p_segment21
1595 ,p_segment22 => p_segment22
1596 ,p_segment23 => p_segment23
1597 ,p_segment24 => p_segment24
1598 ,p_segment25 => p_segment25
1599 ,p_segment26 => p_segment26
1600 ,p_segment27 => p_segment27
1601 ,p_segment28 => p_segment28
1602 ,p_segment29 => p_segment29
1603 ,p_segment30 => p_segment30
1604 ,p_position_id => l_new_position_id
1605 ,p_object_version_number => l_new_object_version_number
1606 ,p_position_definition_id => l_position_definition_id
1607 ,p_name => l_name
1608 );
1609 */
1610 --
1611 l_new_position_date_end := null;
1612 l_new_job_id := p_job_id;
1613 end if;
1614
1615 --
1616 -- Create job requirements for the 'new' position based on the
1617 -- job requirements from the 'old' position. If the 'new' position
1618 -- already existed, only create job requirements from the old position
1619 -- that the 'new' position doesn't already have defined.
1620 --
1621 hr_utility.set_location(l_proc, 90);
1622 --
1623 for csr_jbr_rec in csr_jbr loop
1624 --
1625 -- fixed error in call: changed call from
1626 -- hr_position_requirements_api.create_position_requirements to
1627 -- hr_position_requirement_api.create_position_requirement
1628 -- Rod Fine 24-Jul-96.
1629 --
1630 hr_position_requirement_api.create_position_requirement
1631 (p_validate => FALSE
1632 ,p_id_flex_num => csr_jbr_rec.id_flex_num
1633 ,p_position_id => l_new_position_id
1634 ,p_comments => csr_jbr_rec.comments
1635 ,p_essential => csr_jbr_rec.essential
1636 ,p_attribute_category => csr_jbr_rec.attribute_category
1637 ,p_attribute1 => csr_jbr_rec.attribute1
1638 ,p_attribute2 => csr_jbr_rec.attribute2
1639 ,p_attribute3 => csr_jbr_rec.attribute3
1640 ,p_attribute4 => csr_jbr_rec.attribute4
1641 ,p_attribute5 => csr_jbr_rec.attribute5
1642 ,p_attribute6 => csr_jbr_rec.attribute6
1643 ,p_attribute7 => csr_jbr_rec.attribute7
1644 ,p_attribute8 => csr_jbr_rec.attribute8
1645 ,p_attribute9 => csr_jbr_rec.attribute9
1646 ,p_attribute10 => csr_jbr_rec.attribute10
1647 ,p_attribute11 => csr_jbr_rec.attribute11
1648 ,p_attribute12 => csr_jbr_rec.attribute12
1649 ,p_attribute13 => csr_jbr_rec.attribute13
1650 ,p_attribute14 => csr_jbr_rec.attribute14
1651 ,p_attribute15 => csr_jbr_rec.attribute15
1652 ,p_attribute16 => csr_jbr_rec.attribute16
1653 ,p_attribute17 => csr_jbr_rec.attribute17
1654 ,p_attribute18 => csr_jbr_rec.attribute18
1655 ,p_attribute19 => csr_jbr_rec.attribute19
1656 ,p_attribute20 => csr_jbr_rec.attribute20
1657 ,p_segment1 => csr_jbr_rec.segment1
1658 ,p_segment2 => csr_jbr_rec.segment2
1659 ,p_segment3 => csr_jbr_rec.segment3
1660 ,p_segment4 => csr_jbr_rec.segment4
1661 ,p_segment5 => csr_jbr_rec.segment5
1662 ,p_segment6 => csr_jbr_rec.segment6
1663 ,p_segment7 => csr_jbr_rec.segment7
1664 ,p_segment8 => csr_jbr_rec.segment8
1665 ,p_segment9 => csr_jbr_rec.segment9
1666 ,p_segment10 => csr_jbr_rec.segment10
1667 ,p_segment11 => csr_jbr_rec.segment11
1668 ,p_segment12 => csr_jbr_rec.segment12
1669 ,p_segment13 => csr_jbr_rec.segment13
1670 ,p_segment14 => csr_jbr_rec.segment14
1671 ,p_segment15 => csr_jbr_rec.segment15
1672 ,p_segment16 => csr_jbr_rec.segment16
1673 ,p_segment17 => csr_jbr_rec.segment17
1674 ,p_segment18 => csr_jbr_rec.segment18
1675 ,p_segment19 => csr_jbr_rec.segment19
1676 ,p_segment20 => csr_jbr_rec.segment20
1677 ,p_segment21 => csr_jbr_rec.segment21
1678 ,p_segment22 => csr_jbr_rec.segment22
1679 ,p_segment23 => csr_jbr_rec.segment23
1680 ,p_segment24 => csr_jbr_rec.segment24
1681 ,p_segment25 => csr_jbr_rec.segment25
1682 ,p_segment26 => csr_jbr_rec.segment26
1683 ,p_segment27 => csr_jbr_rec.segment27
1684 ,p_segment28 => csr_jbr_rec.segment28
1685 ,p_segment29 => csr_jbr_rec.segment29
1686 ,p_segment30 => csr_jbr_rec.segment30
1687 ,p_job_requirement_id => l_job_requirement_id
1688 ,p_object_version_number => l_jbr_object_version_number
1689 ,p_analysis_criteria_id => l_analysis_criteria_id
1690 );
1691 end loop;
1692 --
1693 -- For each valid grade that is in mm valid grades but not in per
1694 -- valid grades will be inserted into per valid grades.
1695 --
1696 for csr_vgr_rec in csr_vgr loop
1697 --
1698 hr_utility.set_location(l_proc, 100);
1699 --
1700 hr_valid_grade_api.create_valid_grade
1701 (p_grade_id => csr_vgr_rec.target_grade_id
1702 ,p_date_from => p_date_effective
1703 ,p_effective_date => p_date_effective --Added for Bug# 1760707
1704 ,p_position_id => l_new_position_id
1705 ,p_attribute_category => csr_vgr_rec.attribute_category
1706 ,p_attribute1 => csr_vgr_rec.attribute1
1707 ,p_attribute2 => csr_vgr_rec.attribute2
1708 ,p_attribute3 => csr_vgr_rec.attribute3
1709 ,p_attribute4 => csr_vgr_rec.attribute4
1710 ,p_attribute5 => csr_vgr_rec.attribute5
1711 ,p_attribute6 => csr_vgr_rec.attribute6
1712 ,p_attribute7 => csr_vgr_rec.attribute7
1713 ,p_attribute8 => csr_vgr_rec.attribute8
1714 ,p_attribute9 => csr_vgr_rec.attribute9
1715 ,p_attribute10 => csr_vgr_rec.attribute10
1716 ,p_attribute11 => csr_vgr_rec.attribute11
1717 ,p_attribute12 => csr_vgr_rec.attribute12
1718 ,p_attribute13 => csr_vgr_rec.attribute13
1719 ,p_attribute14 => csr_vgr_rec.attribute14
1720 ,p_attribute15 => csr_vgr_rec.attribute15
1721 ,p_attribute16 => csr_vgr_rec.attribute16
1722 ,p_attribute17 => csr_vgr_rec.attribute17
1723 ,p_attribute18 => csr_vgr_rec.attribute18
1724 ,p_attribute19 => csr_vgr_rec.attribute19
1725 ,p_attribute20 => csr_vgr_rec.attribute20
1726 ,p_valid_grade_id => l_valid_grade_id
1727 ,p_object_version_number => l_vgr_object_version_number
1728 );
1729 end loop;
1730 --
1731 -- If deactivate_old_position is true then close down the 'old'
1732 -- position
1733 --
1734 hr_utility.set_location(l_proc, 110);
1735 --
1736 -- If the old position is to be deactivated then
1737 -- determine the availability status
1738 --
1739 if p_deactivate_old_position then
1740 --
1741 hr_utility.set_location(l_proc, 120);
1742 --
1743 l_date_end := p_date_effective - 1;
1744 --
1745 -- Added as part of fix for bug 2943725
1746 --
1747 --
1748 -- Get the current availability status of the position
1749 --
1750 open csr_current_status;
1751 fetch csr_current_status into l_current_system_type_cd;
1752 close csr_current_status;
1753 --
1754 hr_utility.set_location(l_proc||'/'||l_current_system_type_cd, 130);
1755 hr_utility.set_location(l_proc||'/'||p_position_id, 131);
1756 hr_utility.set_location(l_proc||'/'||p_date_effective, 132);
1757 --
1758 -- If the status is Active then change the status to Eliminated
1759 --
1760 if l_current_system_type_cd = 'ACTIVE' then
1761 --
1762 hr_utility.set_location(l_proc, 140);
1763 --
1764 l_system_type_cd := 'ELIMINATED';
1765 --
1766 -- Else if the status is Proposed then the status will
1767 -- become Deleted.
1768 --
1769 elsif l_current_system_type_cd = 'PROPOSED' then
1770 --
1771 hr_utility.set_location(l_proc, 150);
1772 --
1773 l_system_type_cd := 'DELETED';
1774 --
1775 end if;
1776 --
1777 -- Get the appropriate avialability status
1778 --
1779 open csr_avail_status(p_system_type_cd => l_system_type_cd);
1780 fetch csr_avail_status into p_availability_status_id;
1781 close csr_avail_status;
1782 --
1783 hr_utility.set_location(l_proc||'/'||l_system_type_cd, 160);
1784 --
1785 -- End of fix for bug 2943725
1786 --
1787 l_pos_dt_mode := 'UPDATE';
1788 --
1789 open csr_pos_current_esd(p_position_id , p_date_effective);
1790 fetch csr_pos_current_esd into l_pos_dt_mode;
1791 close csr_pos_current_esd;
1792 --
1793 hr_position_api.update_position
1794 (p_position_id => p_position_id
1795 ,p_object_version_number => l_object_version_number
1796 ,p_effective_start_date =>l_effective_start_date
1797 ,p_effective_end_date =>l_effective_end_date
1798 --,p_date_end => l_date_end
1799 ,p_position_definition_id => l_old_position_def
1800 ,p_name => l_old_name
1801 ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
1802 ,p_availability_status_id => p_availability_status_id
1803 ,p_effective_date => p_date_effective --p_effective_date
1804 ,p_datetrack_mode => l_pos_dt_mode
1805 );
1806 /*
1807 hr_position_api.update_position
1808 (p_position_id => p_position_id
1809 ,p_object_version_number => l_object_version_number
1810 ,p_date_end => l_date_end
1811 ,p_position_definition_id => l_old_position_def
1812 ,p_name => l_old_name
1813 ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
1814 );
1815 */
1816 --
1817 hr_utility.set_location(l_proc, 170);
1818 --
1819 end if;
1820 --
1821 -- This is customer specific processing
1822 --
1823 hr_mass_move_cus.post_move_position
1824 (p_validate => FALSE
1825 ,p_position_id => p_position_id
1826 ,p_object_version_number => l_object_version_number
1827 ,p_date_effective => p_date_effective
1828 ,p_business_group_id => l_business_group_id
1829 ,p_organization_id => p_organization_id
1830 ,p_deactivate_old_position => p_deactivate_old_position
1831 ,p_mass_move_id => p_mass_move_id
1832 ,p_new_position_id => l_new_position_id
1833 ,p_new_object_version_number => l_new_object_version_number
1834 );
1835 --
1836 -- When in validation only mode raise the Validate_Enabled exception
1837 --
1838 if p_validate then
1839 raise hr_api.validate_enabled;
1840 end if;
1841 --
1842 -- Set all out arguments
1843 --
1844 p_object_version_number := l_object_version_number;
1845 p_new_position_id := l_new_position_id;
1846 p_new_job_id := l_new_job_id;
1847 p_new_object_version_number := l_new_object_version_number;
1848 p_valid_grades_changed_warning := l_valid_grades_changed_warning;
1849 p_pos_exists_warning := l_pos_exists_warning;
1850 p_pos_date_range_warning := l_pos_date_range_warning;
1851 p_pos_pending_close_warning := l_pos_pending_close_warning;
1852 p_pos_jbe_not_moved_warning := l_pos_jbe_not_moved_warning;
1853 p_pos_vac_not_moved_warning := l_pos_vac_not_moved_warning;
1854 --
1855 hr_utility.set_location(' Leaving:'||l_proc, 997);
1856 --
1857 exception
1858 --
1859 when hr_api.validate_enabled then
1860 --
1861 -- As the Validate_Enabled exception has been raised
1862 -- we must rollback to the savepoint
1863 --
1864 --
1865 ROLLBACK TO move_position;
1866 --
1867 -- Only set output warning arguments
1868 -- (Any key or derived arguments must be set to null
1869 -- when validation only mode is being used.)
1870 --
1871 p_object_version_number := p_object_version_number;
1872 p_new_position_id := null;
1873 p_new_job_id := null;
1874 p_new_object_version_number := null;
1875 p_valid_grades_changed_warning := l_valid_grades_changed_warning;
1876 p_pos_exists_warning := l_pos_exists_warning;
1877 p_pos_date_range_warning := l_pos_date_range_warning;
1878 p_pos_pending_close_warning := l_pos_pending_close_warning;
1879 p_pos_jbe_not_moved_warning := l_pos_jbe_not_moved_warning;
1880 p_pos_vac_not_moved_warning := l_pos_vac_not_moved_warning;
1881 --
1882 hr_utility.set_location(' Leaving:'||l_proc, 998);
1883 --
1884 when others then
1885 --
1886 hr_utility.set_location(' Leaving:'||l_proc, 999);
1887 --
1888 -- if it is an business rule violation error then only rollback
1889 -- this positon record.
1890 --
1891 if sqlcode = -20001 then
1892 --
1893 rollback to move_position;
1894 --
1895 hr_batch_message_line_api.create_message_line
1896 (p_validate => FALSE
1897 ,p_batch_run_number => p_batch_run_number
1898 ,p_api_name => 'hr_mass_move_api.mass_move'
1899 ,p_status => 'S'
1900 ,p_error_number => sqlcode
1901 ,p_error_message => sqlerrm
1902 ,p_extended_error_message => fnd_message.get
1903 ,p_source_row_information => l_name
1904 ,p_line_id => l_dummy_line_id);
1905 --
1906 -- set in out parameters and set out parameters
1907 --
1908 p_object_version_number := l_ovn;
1909 p_new_position_id := null;
1910 p_new_job_id := null;
1911 p_new_object_version_number := null;
1912 p_valid_grades_changed_warning := false;
1913 p_pos_exists_warning := false;
1914 p_pos_date_range_warning := false;
1915 p_pos_pending_close_warning := false;
1916 p_pos_jbe_not_moved_warning := false;
1917 p_pos_vac_not_moved_warning := false;
1918 --
1919 else
1920 --
1921 raise;
1922 --
1923 end if;
1924 --
1925 end move_position;
1926 --
1927 -- ----------------------------------------------------------------------------
1928 -- |--------------------------< move_assignments >----------------------------|
1929 -- ----------------------------------------------------------------------------
1930 procedure move_assignments
1931 (p_validate in boolean default false
1932 ,p_mass_move_id in number
1933 ,p_old_position_id in number
1934 ,p_new_position_id in number
1935 ,p_new_job_id in number
1936 ,p_effective_date in date
1937 ,p_new_organization_id in number
1938 ,p_pos_time_normal_start in varchar2
1939 ,p_pos_time_normal_finish in varchar2
1940 ,p_pos_normal_hours in number
1941 ,p_pos_frequency in varchar2
1942 ,p_pos_location_id in number
1943 ,p_org_time_normal_start in varchar2
1944 ,p_org_time_normal_finish in varchar2
1945 ,p_org_normal_hours in number
1946 ,p_org_frequency in varchar2
1947 ,p_org_location_id in number
1948 ,p_legislation_code in varchar2
1949 ,p_bg_time_normal_start in varchar2
1950 ,p_bg_time_normal_finish in varchar2
1951 ,p_bg_normal_hours in number
1952 ,p_bg_frequency in varchar2
1953 ,p_bg_location_id in number
1954 ,p_batch_run_number in number
1955 )
1956 is
1957 --
1958 l_proc varchar2(72) := g_package||'move_assignments';
1959 l_vacancies_exist varchar2(1);
1960 l_apl_asg_new_ovn number;
1961 l_out_comment_id number;
1962 l_out_people_group_id number;
1963 l_new_job_id number;
1964 l_out_group_name varchar2(240);
1965 -- Added new dummy var
1966 -- Bug 944911
1967 l_out_concatenated_segments varchar2(240);
1968 l_out_soft_coding_keyflx_id number;
1969 l_out_effective_start_date date;
1970 l_out_effective_end_date date;
1971 l_apl_asg_pos_vacancy_warning boolean;
1972 l_org_now_no_manager_warning boolean;
1973 l_other_manager_warning boolean;
1974 l_soft_coding_keyflex_id number(15);
1975 l_entries_changed_warning varchar2(1);
1976 l_time_normal_start varchar2(150);
1977 l_time_normal_finish varchar2(150);
1978 l_normal_hours number(22,3);
1979 l_frequency varchar2(1);
1980 l_location_id number(15);
1981 l_mm_assignment_id number(15);
1982 l_effective_start_date date;
1983 l_effective_end_date date;
1984 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
1985 l_batch_run_number number(15);
1986 l_dummy_line_id number(15);
1987 l_segment1 varchar2(150);
1988 l_cagr_concatenated_segments varchar2(240);
1989 l_cagr_grade_def_id number(15);
1990 asg_vacancy_warning exception;
1991 future_asg_warning exception;
1992 l_exists varchar2(1);
1993 --
1994 -- Out Parameters for CWK Assignments
1995 --
1996 l_people_group_name VARCHAR2(240);
1997 l_people_group_id NUMBER;
1998 --l_org_now_no_manager_warning BOOLEAN;
1999 --l_other_manager_warning BOOLEAN;
2000 l_spp_delete_warning BOOLEAN;
2001 --l_entries_changed_warning VARCHAR2(1);
2002 l_tax_district_changed_warning BOOLEAN;
2003 --
2004 cursor csr_future_asg_changes is
2005 select null
2006 from per_assignments_f asg,
2007 per_mass_moves mm,
2008 per_mm_assignments mmasg
2009 where mmasg.assignment_id = l_mm_assignment_id
2010 and asg.assignment_id = mmasg.assignment_id
2011 and mmasg.mass_move_id = p_mass_move_id
2012 and mmasg.mass_move_id = mm.mass_move_id
2013 and mmasg.position_id = p_old_position_id
2014 and asg.effective_start_date > p_effective_date;
2015 --
2016 cursor csr_get_mm_asg is
2017 select mm_asg.assignment_id,
2018 mm_asg.default_from,
2019 mm_asg.grade_id,
2020 mm_asg.tax_unit_id,
2021 asg.location_id,
2022 asg.frequency,
2023 asg.normal_hours,
2024 asg.time_normal_start,
2025 asg.time_normal_finish,
2026 asg.assignment_type,
2027 asg.object_version_number,
2028 asg.vacancy_id,
2029 per.full_name,
2030 org.name orgname,
2031 neworg.name neworgname
2032 from per_mm_assignments mm_asg,
2033 per_assignments_f asg,
2034 per_people_f per,
2035 per_organization_units org,
2036 per_mass_moves mm,
2037 per_organization_units neworg
2038 where mm_asg.position_id = p_old_position_id
2039 and mm_asg.assignment_id = asg.assignment_id
2040 and mm_asg.mass_move_id = p_mass_move_id
2041 and mm_asg.mass_move_id = mm.mass_move_id
2042 and mm.new_organization_id = neworg.organization_id
2043 and asg.organization_id = org.organization_id
2044 and p_effective_date between asg.effective_start_date
2045 and asg.effective_end_date
2046 and p_effective_date between per.effective_start_date
2047 and per.effective_end_date
2048 and asg.person_id = per.person_id
2049 and assignment_moved = 'N'
2050 and select_assignment = 'Y';
2051 --
2052 begin
2053 --
2054 hr_utility.set_location('Entering:'|| l_proc, 10);
2055 --
2056 -- Issue a savepoint
2057 --
2058 savepoint move_assignment;
2059 --
2060 --
2061 for mmasgrec in csr_get_mm_asg loop
2062 --
2063 begin
2064 --
2065 savepoint move_asg;
2066 --
2067 hr_utility.set_location(l_proc||'/'||mmasgrec.assignment_id, 20);
2068 --
2069 -- GRE/Legal Entity will only move for US legislation.
2070 --
2071 if p_legislation_code = 'US' then
2072 --
2073 l_segment1 := to_char(mmasgrec.tax_unit_id);
2074 --
2075 else
2076 --
2077 l_segment1 := hr_api.g_varchar2;
2078 --
2079 end if;
2080 --
2081 if mmasgrec.default_from = 'B' then
2082 --
2083 hr_utility.set_location(l_proc, 30);
2084 --
2085 l_location_id := p_bg_location_id;
2086 l_frequency := p_bg_frequency;
2087 l_normal_hours := p_bg_normal_hours;
2088 l_time_normal_finish := p_bg_time_normal_finish;
2089 l_time_normal_start := p_bg_time_normal_start;
2090 --
2091 elsif mmasgrec.default_from = 'O' then
2092 --
2093 hr_utility.set_location(l_proc, 40);
2094 --
2095 l_location_id := p_org_location_id;
2096 l_frequency := p_org_frequency;
2097 l_normal_hours := p_org_normal_hours;
2098 l_time_normal_finish := p_org_time_normal_finish;
2099 l_time_normal_start := p_org_time_normal_start;
2100 --
2101 elsif mmasgrec.default_from = 'P' then
2102 --
2103 hr_utility.set_location(l_proc, 50);
2104 --
2105 l_location_id := p_pos_location_id;
2106 l_frequency := p_pos_frequency;
2107 l_normal_hours := p_pos_normal_hours;
2108 l_time_normal_finish := p_pos_time_normal_finish;
2109 l_time_normal_start := p_pos_time_normal_start;
2110 --
2111 elsif mmasgrec.default_from = 'A' then
2112 --
2113 hr_utility.set_location(l_proc, 60);
2114 --
2115 l_location_id := mmasgrec.location_id;
2116 l_frequency := mmasgrec.frequency;
2117 l_normal_hours := mmasgrec.normal_hours;
2118 l_time_normal_finish := mmasgrec.time_normal_finish;
2119 l_time_normal_start := mmasgrec.time_normal_start;
2120 --
2121 end if;
2122 --
2123 if mmasgrec.vacancy_id is not null then
2124 --
2125 hr_utility.set_location(l_proc, 70);
2126 --
2127 raise asg_vacancy_warning;
2128 --
2129 end if;
2130 --
2131 open csr_future_asg_changes;
2132 fetch csr_future_asg_changes into l_exists;
2133 --
2134 l_mm_assignment_id := mmasgrec.assignment_id;
2135 --
2136 if csr_future_asg_changes%found then
2137 --
2138 hr_utility.set_location(l_proc, 80);
2139 close csr_future_asg_changes;
2140 raise future_asg_warning;
2141 --
2142 end if;
2143 --
2144 close csr_future_asg_changes;
2145 --
2146 if mmasgrec.assignment_type = 'A' then
2147 --
2148 hr_utility.set_location(l_proc, 90);
2149 --
2150 hr_assignment_api.update_apl_asg
2151 (p_validate => FALSE
2152 ,p_effective_date => p_effective_date
2153 ,p_datetrack_update_mode => 'UPDATE'
2154 ,p_assignment_id => mmasgrec.assignment_id
2155 ,p_object_version_number => mmasgrec.object_version_number
2156 ,p_grade_id => mmasgrec.grade_id
2157 ,p_position_id => p_new_position_id
2158 ,p_job_id => p_new_job_id
2159 ,p_organization_id => p_new_organization_id
2160 ,p_location_id => l_location_id
2161 ,p_frequency => l_frequency
2162 ,p_normal_hours => l_normal_hours
2163 ,p_time_normal_finish => l_time_normal_finish
2164 ,p_time_normal_start => l_time_normal_start
2165 ,p_comment_id => l_out_comment_id
2166 ,p_people_group_id => l_out_people_group_id
2167 ,p_group_name => l_out_group_name
2168 ,p_effective_start_date => l_out_effective_start_date
2169 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
2170 ,p_cagr_grade_def_id => l_cagr_grade_def_id
2171 --
2172 -- Bug 944911 Added out param into a dummy var
2173 --
2174 ,p_concatenated_segments => l_out_concatenated_segments
2175 ,p_soft_coding_keyflex_id => l_out_soft_coding_keyflx_id
2176 ,p_effective_end_date => l_out_effective_end_date);
2177 --
2178 -- Assignment is for a Contingent Worker
2179 --
2180 elsif mmasgrec.assignment_type = 'C' then
2181 --
2182 hr_utility.set_location(l_proc, 95);
2183 --
2184 hr_mass_move_api.move_cwk_asg
2185 (p_validate => p_validate
2186 ,p_effective_date => p_effective_date
2187 ,p_datetrack_update_mode => 'UPDATE'
2188 ,p_assignment_id => mmasgrec.assignment_id
2189 ,p_object_version_number => mmasgrec.object_version_number
2190 ,p_grade_id => mmasgrec.grade_id
2191 ,p_position_id => p_new_position_id
2192 ,p_job_id => p_new_job_id
2193 ,p_location_id => l_location_id
2194 ,p_organization_id => p_new_organization_id
2195 ,p_people_group_name => l_people_group_name
2196 ,p_effective_start_date => l_effective_start_date
2197 ,p_effective_end_date => l_effective_end_date
2198 ,p_people_group_id => l_people_group_id
2199 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2200 ,p_other_manager_warning => l_other_manager_warning
2201 ,p_spp_delete_warning => l_spp_delete_warning
2202 ,p_entries_changed_warning => l_entries_changed_warning
2203 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
2204 --
2205 else -- assignment_type = 'E'
2206 --
2207 hr_utility.set_location(l_proc, 100);
2208 --
2209 hr_mass_move_api.move_emp_asg
2210 (p_validate => FALSE
2211 ,p_mass_move_id => p_mass_move_id
2212 ,p_effective_date => p_effective_date
2213 ,p_assignment_id => mmasgrec.assignment_id
2214 ,p_object_version_number => mmasgrec.object_version_number
2215 ,p_grade_id => mmasgrec.grade_id
2216 ,p_position_id => p_new_position_id
2217 ,p_job_id => p_new_job_id
2218 ,p_organization_id => p_new_organization_id
2219 ,p_location_id => l_location_id
2220 ,p_frequency => l_frequency
2221 ,p_normal_hours => l_normal_hours
2222 ,p_time_normal_finish => l_time_normal_finish
2223 ,p_time_normal_start => l_time_normal_start
2224 ,p_segment1 => l_segment1
2225 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id --out
2226 ,p_effective_start_date => l_effective_start_date --out
2227 ,p_effective_end_date => l_effective_end_date --out
2228 ,p_concatenated_segments => l_concatenated_segments --out
2229 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning --out
2230 ,p_other_manager_warning => l_other_manager_warning --out
2231 ,p_entries_changed_warning => l_entries_changed_warning --out
2232 ,p_spp_delete_warning => l_spp_delete_warning); --out
2233 --
2234 end if; /* if assignment type = 'E' */
2235 --
2236 if l_spp_delete_warning then
2237 --
2238 hr_utility.set_message(800,'HR_289826_SPP_DELETE_WARN_API');
2239 --
2240 hr_batch_message_line_api.create_message_line
2241 (p_validate => FALSE
2242 ,p_batch_run_number => p_batch_run_number
2243 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2244 ,p_status => 'S'
2245 ,p_error_number => '289286'
2246 ,p_error_message => hr_utility.get_message
2247 ,p_extended_error_message => fnd_message.get
2248 ,p_source_row_information => mmasgrec.full_name
2249 ,p_line_id => l_dummy_line_id);
2250 --
2251 end if;
2252 --
2253 if l_org_now_no_manager_warning then
2254 --
2255 hr_utility.set_message(801,'HR_51124_MMV_NO_MGR_EXIST_ORG');
2256 --
2257 hr_batch_message_line_api.create_message_line
2258 (p_validate => FALSE
2259 ,p_batch_run_number => p_batch_run_number
2260 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2261 ,p_status => 'S'
2262 ,p_error_number => '51124'
2263 ,p_error_message => hr_utility.get_message
2264 ,p_extended_error_message => fnd_message.get
2265 ,p_source_row_information => mmasgrec.full_name
2266 ,p_line_id => l_dummy_line_id);
2267 --
2268 end if;
2269 --
2270 if l_other_manager_warning then
2271 --
2272 hr_utility.set_message(801,'HR_51125_MMV_MRE_MGR_EXIST_ORG');
2273 --
2274 hr_batch_message_line_api.create_message_line
2275 (p_validate => FALSE
2276 ,p_batch_run_number => p_batch_run_number
2277 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2278 ,p_status => 'S'
2279 ,p_error_number => '51125'
2280 ,p_error_message => hr_utility.get_message
2281 ,p_extended_error_message => fnd_message.get
2282 ,p_source_row_information => mmasgrec.full_name
2283 ,p_line_id => l_dummy_line_id);
2284 --
2285 end if;
2286 --
2287 if l_entries_changed_warning = 'S' then
2288 --
2289 hr_utility.set_message(801,'HR_51126_MMV_SAL_ENT_ALTERED');
2290 --
2291 hr_batch_message_line_api.create_message_line
2292 (p_validate => FALSE
2293 ,p_batch_run_number => p_batch_run_number
2294 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2295 ,p_status => 'S'
2296 ,p_error_number => '51126'
2297 ,p_error_message => hr_utility.get_message
2298 ,p_extended_error_message => fnd_message.get
2299 ,p_source_row_information => mmasgrec.full_name
2300 ,p_line_id => l_dummy_line_id);
2301 --
2302 end if;
2303 --
2304 if l_entries_changed_warning = 'Y' then
2305 --
2306 hr_utility.set_message(801,'HR_51127_MMV_NON_SAL_ENT_ALR');
2307 --
2308 hr_batch_message_line_api.create_message_line
2309 (p_validate => FALSE
2310 ,p_batch_run_number => p_batch_run_number
2311 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2312 ,p_status => 'S'
2313 ,p_error_number => '51127'
2314 ,p_error_message => hr_utility.get_message
2315 ,p_extended_error_message => fnd_message.get
2316 ,p_source_row_information => mmasgrec.full_name
2317 ,p_line_id => l_dummy_line_id);
2318 --
2319 end if;
2320 --
2321 hr_utility.set_location(l_proc, 110);
2322 --
2323 --
2324 -- if it has made it this far, the applicant/employee assignment was moved
2325 --
2326 update per_mm_assignments
2327 set assignment_moved = 'Y'
2328 where assignment_id = mmasgrec.assignment_id
2329 and mass_move_id = p_mass_move_id;
2330 --
2331 exception
2332 when asg_vacancy_warning then
2333 --
2334 rollback to move_asg;
2335 --
2336 hr_utility.set_message(801,'HR_51129_MMV_ASG_NOT_MOVED');
2337 --
2338 hr_batch_message_line_api.create_message_line
2339 (p_validate => FALSE
2340 ,p_batch_run_number => p_batch_run_number
2341 ,p_api_name => 'hr_mass_move_api.move_assignment'
2342 ,p_status => 'S'
2343 ,p_error_number => '51129'
2344 ,p_error_message => hr_utility.get_message
2345 ,p_extended_error_message => fnd_message.get
2346 ,p_source_row_information => mmasgrec.full_name
2347 ,p_line_id => l_dummy_line_id);
2348 --
2349 when future_asg_warning then
2350 --
2351 rollback to move_asg;
2352 --
2353 hr_utility.set_message(801,'HR_51103_MMV_ASG_FUTURE_CHG');
2354 --
2355 hr_batch_message_line_api.create_message_line
2356 (p_validate => FALSE
2357 ,p_batch_run_number => p_batch_run_number
2358 ,p_api_name => 'hr_mass_move_api.move_assignment'
2359 ,p_status => 'S'
2360 ,p_error_number => '51103'
2361 ,p_error_message => hr_utility.get_message
2362 ,p_extended_error_message => fnd_message.get
2363 ,p_source_row_information => mmasgrec.full_name
2364 ,p_line_id => l_dummy_line_id);
2365 --
2366 when others then
2367 --
2368 hr_utility.set_location(l_proc, 120);
2369 --
2370 if sqlcode = -20001 then
2371 --
2372 rollback to move_asg;
2373 --
2374 hr_batch_message_line_api.create_message_line
2375 (p_validate => FALSE
2376 ,p_batch_run_number => p_batch_run_number
2377 ,p_api_name => 'hr_mass_move_api.mass_move'
2378 ,p_status => 'S'
2379 ,p_error_number => sqlcode
2380 ,p_error_message => sqlerrm
2381 ,p_extended_error_message => fnd_message.get
2382 ,p_source_row_information => mmasgrec.full_name
2383 ,p_line_id => l_dummy_line_id);
2384 --
2385 else
2386 --
2387 raise;
2388 --
2389 end if;
2390 --
2391 end; --begin
2392 --
2393 end loop; -- for mmasgrec in cursor
2394 --
2395 if p_validate then
2396 raise hr_api.validate_enabled;
2397 end if;
2398 --
2399 hr_utility.set_location(' Leaving:'||l_proc, 997);
2400 --
2401 exception
2402 --
2403 when hr_api.validate_enabled then
2404 --
2405 -- As the Validate_Enabled exception has been raised
2406 -- we must rollback to the savepoint
2407 --
2408 ROLLBACK TO move_assignment;
2409 --
2410 hr_utility.set_location(' Leaving:'||l_proc, 998);
2411 --
2412 when others then
2413 --
2414 -- A validation or unexpected error has occurred
2415 --
2416 -- Added as part of the fix to bug 632479
2417 --
2418 ROLLBACK TO move_assignment;
2419 --
2420 hr_utility.set_location(' Leaving:'||l_proc, 999);
2421 --
2422 raise;
2423 --
2424 end move_assignments;
2425 --
2426 -- ----------------------------------------------------------------------------
2427 -- |------------------------------< mass_move >-------------------------------|
2428 -- ----------------------------------------------------------------------------
2429 --
2430 procedure mass_move
2431 ( p_validate in boolean default false,
2432 p_mass_move_id in number
2433 ) is
2434 --
2435 -- Declare cursors and local variables
2436 --
2437 l_proc varchar2(72) := g_package||'mass_move ';
2438 l_api_updatin boolean;
2439 l_new_organization_id number(15);
2440 l_effective_date date;
2441 l_business_group_id number(15);
2442 l_time_normal_start varchar2(150);
2443 l_time_normal_finish varchar2(150);
2444 l_normal_hours number(22,3);
2445 l_frequency varchar2(1);
2446 l_location_id number(15);
2447 l_org_time_normal_start varchar2(150);
2448 l_org_time_normal_finish varchar2(150);
2449 l_org_normal_hours number(22,3);
2450 l_org_frequency varchar2(30);
2451 l_org_location_id number(15);
2452 l_legislation_code varchar2(150);
2453 l_bg_time_normal_start varchar2(150);
2454 l_bg_time_normal_finish varchar2(150);
2455 l_bg_normal_hours number(22,3);
2456 l_bg_frequency varchar2(30);
2457 l_bg_location_id number(15);
2458 l_new_pos_time_normal_start varchar2(150);
2459 l_new_pos_time_normal_finish varchar2(150);
2460 l_new_pos_normal_hours number(22,3);
2461 l_new_pos_frequency varchar2(30);
2462 l_new_pos_location_id number(15);
2463 l_old_pos_time_normal_start varchar2(150);
2464 l_old_pos_time_normal_finish varchar2(150);
2465 l_old_pos_normal_hours number(22,3);
2466 l_old_pos_frequency varchar2(30);
2467 l_old_pos_location_id number(15);
2468 l_deactivate_boolean boolean;
2469 l_new_position_id number(15);
2470 l_new_job_id number(15);
2471 l_new_pos_ovn number(15);
2472 l_batch_run_number number(15);
2473 l_dummy_line_id number(15);
2474 l_apl_asg_updated_warning boolean;
2475 l_apl_asg_pos_vacancy_warning boolean;
2476 l_errors_exist varchar2(1);
2477 l_warnings_exist varchar2(1);
2478 l_valid_grade_id number(15);
2479 l_object_version_number number(15);
2480 l_position_name varchar2(240);
2481 l_position_id number(15);
2482 l_valid_grades_changed_warning boolean;
2483 l_pos_exists_warning boolean;
2484 l_pos_date_range_warning boolean;
2485 l_pos_pending_close_warning boolean;
2486 l_pos_jbe_not_moved_warning boolean;
2487 l_pos_vac_not_moved_warning boolean;
2488
2489 --
2490 cursor csr_batch_run_number is
2491 select hr_api_batch_message_lines_s.nextval
2492 from dual;
2493 --
2494 cursor csr_lock_per_mass_moves is
2495 select null
2496 from per_mass_moves
2497 where mass_move_id = p_mass_move_id
2498 for update nowait;
2499 --
2500 cursor csr_lock_per_mm_positions is
2501 select null
2502 from per_mm_positions
2503 where mass_move_id = p_mass_move_id
2504 for update nowait;
2505 --
2506 cursor csr_lock_per_mm_assignments is
2507 select null
2508 from per_mm_assignments
2509 where mass_move_id = p_mass_move_id
2510 for update nowait;
2511 --
2512 cursor csr_lock_per_mm_valid_grades is
2513 select null
2514 from per_mm_valid_grades
2515 where mass_move_id = p_mass_move_id
2516 for update nowait;
2517 --
2518 cursor csr_lock_per_mm_job_reqts is
2519 select null
2520 from per_mm_job_requirements
2521 where mass_move_id = p_mass_move_id
2522 for update nowait;
2523 --
2524 cursor csr_get_mass_move_info is
2525 select new_organization_id,
2526 effective_date
2527 from per_mass_moves
2528 where mass_move_id = p_mass_move_id;
2529 --
2530 cursor csr_get_org_defaults is
2531 select business_group_id,
2532 default_start_time,
2533 default_end_time,
2534 fnd_number.canonical_to_number(working_hours),
2535 frequency,
2536 location_id
2537 from per_organization_units
2538 where organization_id = l_new_organization_id;
2539 --
2540 cursor csr_get_busgrp_defaults is
2541 select legislation_code,
2542 default_start_time,
2543 default_end_time,
2544 fnd_number.canonical_to_number(working_hours),
2545 frequency,
2546 location_id
2547 from per_business_groups
2548 where business_group_id = l_business_group_id;
2549 --
2550 cursor csr_get_old_pos_defaults is
2551 select pos.time_normal_start,
2552 pos.time_normal_finish,
2553 fnd_number.canonical_to_number(pos.working_hours),
2554 pos.frequency,
2555 pos.location_id
2556 from hr_positions_f pos
2557 where position_id = l_position_id
2558 and l_effective_date
2559 between effective_start_date
2560 and effective_end_date;
2561 --
2562 cursor csr_get_new_pos_defaults is
2563 select pos.time_normal_start,
2564 pos.time_normal_finish,
2565 fnd_number.canonical_to_number(pos.working_hours),
2566 pos.frequency,
2567 pos.location_id
2568 from hr_positions_f pos
2569 where position_id = l_new_position_id
2570 and l_effective_date
2571 between effective_start_date
2572 and effective_end_date;
2573 --
2574 cursor csr_get_mm_valid_grades is
2575 select mmvgr.position_id,
2576 mmvgr.target_grade_id,
2577 mmvgr.attribute_category,
2578 mmvgr.attribute1,
2579 mmvgr.attribute2,
2580 mmvgr.attribute3,
2581 mmvgr.attribute4,
2582 mmvgr.attribute5,
2583 mmvgr.attribute6,
2584 mmvgr.attribute7,
2585 mmvgr.attribute8,
2586 mmvgr.attribute9,
2587 mmvgr.attribute10,
2588 mmvgr.attribute11,
2589 mmvgr.attribute12,
2590 mmvgr.attribute13,
2591 mmvgr.attribute14,
2592 mmvgr.attribute15,
2593 mmvgr.attribute16,
2594 mmvgr.attribute17,
2595 mmvgr.attribute18,
2596 mmvgr.attribute19,
2597 mmvgr.attribute20
2598 from per_mm_valid_grades mmvgr
2599 where mmvgr.mass_move_id = p_mass_move_id;
2600
2601 --
2602 cursor csr_get_mm_positions is
2603 select pos.name,
2604 mmpos.position_id,
2605 mmpos.object_version_number,
2606 mmpos.deactivate_old_position,
2607 mmpos.new_position_id,
2608 mmpos.target_job_id,
2609 mmpos.position_moved,
2610 mmpos.default_from,
2611 mmpos.segment1,
2612 mmpos.segment2,
2613 mmpos.segment3,
2614 mmpos.segment4,
2615 mmpos.segment5,
2616 mmpos.segment6,
2617 mmpos.segment7,
2618 mmpos.segment8,
2619 mmpos.segment9,
2620 mmpos.segment10,
2621 mmpos.segment11,
2622 mmpos.segment12,
2623 mmpos.segment13,
2624 mmpos.segment14,
2625 mmpos.segment15,
2626 mmpos.segment16,
2627 mmpos.segment17,
2628 mmpos.segment18,
2629 mmpos.segment19,
2630 mmpos.segment20,
2631 mmpos.segment21,
2632 mmpos.segment22,
2633 mmpos.segment23,
2634 mmpos.segment24,
2635 mmpos.segment25,
2636 mmpos.segment26,
2637 mmpos.segment27,
2638 mmpos.segment28,
2639 mmpos.segment29,
2640 mmpos.segment30
2641 from per_mm_positions mmpos,
2642 hr_positions_f pos
2643 where mmpos.mass_move_id = p_mass_move_id
2644 and mmpos.position_id = pos.position_id
2645 and mmpos.select_position = 'Y'
2646 and l_effective_date BETWEEN pos.effective_start_date
2647 AND pos.effective_end_date;
2648 --
2649 -- Check for errors at the end of the mass move
2650 --
2651 cursor csr_chk_errors is
2652 select 'Y'
2653 from hr_api_batch_message_lines
2654 where batch_run_number = l_batch_run_number
2655 and status = 'F';
2656 --
2657 cursor csr_chk_warnings is
2658 select 'Y'
2659 from hr_api_batch_message_lines
2660 where batch_run_number = l_batch_run_number
2661 and status = 'S';
2662 --
2663 begin
2664 --
2665 hr_utility.set_location('Entering:'|| l_proc, 10);
2666 --
2667 -- Issue a savepoint.
2668 --
2669 savepoint activate_mass_move;
2670 --
2671 -- Get a unique run number to identify errors
2672 --
2673 open csr_batch_run_number;
2674 fetch csr_batch_run_number into
2675 l_batch_run_number;
2676 close csr_batch_run_number;
2677 --
2678 -- Get information about the mass move if ID is not null
2679 -- and is valid.
2680 --
2681 if p_mass_move_id is not null then
2682 --
2683 hr_utility.set_location(l_proc, 20);
2684 --
2685 open csr_get_mass_move_info;
2686 fetch csr_get_mass_move_info into l_new_organization_id,
2687 l_effective_date;
2688 --
2689 if csr_get_mass_move_info%notfound then
2690 --
2691 close csr_get_mass_move_info;
2692 --
2693 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2694 hr_utility.raise_error;
2695 --
2696 end if;
2697 --
2698 close csr_get_mass_move_info;
2699 --
2700 else
2701 --
2702 hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
2703 hr_utility.raise_error;
2704 --
2705 end if;
2706 --
2707 -- Issue a savepoint
2708 --
2709 savepoint massmove;
2710 --
2711 -- Lock all tables holding mass move information
2712 --
2713 open csr_lock_per_mass_moves;
2714 close csr_lock_per_mass_moves;
2715 open csr_lock_per_mm_positions;
2716 close csr_lock_per_mm_positions;
2717 open csr_lock_per_mm_assignments;
2718 close csr_lock_per_mm_assignments;
2719 open csr_lock_per_mm_valid_grades;
2720 close csr_lock_per_mm_valid_grades;
2721 open csr_lock_per_mm_job_reqts;
2722 close csr_lock_per_mm_job_reqts;
2723 --
2724 hr_utility.set_location(l_proc, 30);
2725 --
2726 -- Get default location and working conditions from the new
2727 -- organization
2728 --
2729 open csr_get_org_defaults;
2730 fetch csr_get_org_defaults into
2731 l_business_group_id,
2732 l_org_time_normal_start,
2733 l_org_time_normal_finish,
2734 l_org_normal_hours,
2735 l_org_frequency,
2736 l_org_location_id;
2737 --
2738 close csr_get_org_defaults;
2739 --
2740 hr_utility.set_location(l_proc, 40);
2741 --
2742 -- Get default location and working conditions from the
2743 -- business group
2744 --
2745 open csr_get_busgrp_defaults;
2746 fetch csr_get_busgrp_defaults into
2747 l_legislation_code,
2748 l_bg_time_normal_start,
2749 l_bg_time_normal_finish,
2750 l_bg_normal_hours,
2751 l_bg_frequency,
2752 l_bg_location_id;
2753 --
2754 close csr_get_busgrp_defaults;
2755 --
2756 hr_utility.set_location(l_proc, 50);
2757 --
2758 begin
2759 --
2760 -- Start of API User Hook for the before hook of mass_move
2761 -- Specifically placed after the business_group_id is retrieved, based on
2762 -- the new organization.
2763 --
2764 hr_mass_move_bk1.mass_move_b
2765 (p_mass_move_id => p_mass_move_id
2766 ,p_business_group_id => l_business_group_id
2767 );
2768 exception
2769 when hr_api.cannot_find_prog_unit then
2770 hr_api.cannot_find_prog_unit_error
2771 (p_module_name => 'MASS_MOVE'
2772 ,p_hook_type => 'BP'
2773 );
2774 --
2775 -- End of API User Hook for the before hook of mass_move
2776 --
2777 end;
2778 --
2779 -- Call Customer-Specific stub to handle pre-mass move validation
2780 --
2781 hr_mass_move_cus.pre_mass_move
2782 (p_mass_move_id => p_mass_move_id);
2783 --
2784 hr_utility.set_location(l_proc, 60);
2785 --
2786 -- Loop through the position records in per_mm_positions, and
2787 -- process the position, applicant assignments, and employee
2788 -- assignment records.
2789 --
2790 for mmposrec in csr_get_mm_positions loop
2791 --
2792 begin
2793 --
2794 savepoint position;
2795 --
2796 l_position_name := mmposrec.name;
2797 l_position_id := mmposrec.position_id;
2798 --
2799 hr_utility.set_location(l_proc||'/'||
2800 l_position_name||'/'||
2801 l_position_id, 70);
2802 --
2803 if mmposrec.deactivate_old_position = 'Y' then
2804 --
2805 l_deactivate_boolean := TRUE;
2806 --
2807 else
2808 --
2809 l_deactivate_boolean := FALSE;
2810 --
2811 end if;
2812 --
2813 hr_utility.set_location(l_proc, 80);
2814 --
2815 -- Get default location and working conditions from the
2816 -- old position business group
2817 --
2818 open csr_get_old_pos_defaults;
2819 fetch csr_get_old_pos_defaults into
2820 l_old_pos_time_normal_start,
2821 l_old_pos_time_normal_finish,
2822 l_old_pos_normal_hours,
2823 l_old_pos_frequency,
2824 l_old_pos_location_id;
2825 --
2826 close csr_get_old_pos_defaults;
2827 --
2828 hr_utility.set_location(l_proc, 90);
2829 --
2830 if mmposrec.default_from = 'B' then
2831 --
2832 hr_utility.set_location(l_proc, 100);
2833 --
2834 l_location_id := l_bg_location_id;
2835 l_frequency := l_bg_frequency;
2836 l_normal_hours := l_bg_normal_hours;
2837 l_time_normal_finish := l_bg_time_normal_finish;
2838 l_time_normal_start := l_bg_time_normal_start;
2839 --
2840 elsif mmposrec.default_from = 'O' then
2841 --
2842 hr_utility.set_location(l_proc, 110);
2843 --
2844 l_location_id := l_org_location_id;
2845 l_frequency := l_org_frequency;
2846 l_normal_hours := l_org_normal_hours;
2847 l_time_normal_finish := l_org_time_normal_finish;
2848 l_time_normal_start := l_org_time_normal_start;
2849 --
2850 elsif mmposrec.default_from = 'P' then
2851 --
2852 hr_utility.set_location(l_proc, 120);
2853 --
2854 l_location_id := l_old_pos_location_id;
2855 l_frequency := l_old_pos_frequency;
2856 l_normal_hours := l_old_pos_normal_hours;
2857 l_time_normal_finish := l_old_pos_time_normal_finish;
2858 l_time_normal_start := l_old_pos_time_normal_start;
2859 --
2860 end if;
2861 --
2862 -- Call the move position process to create the new position
2863 --
2864 if mmposrec.position_moved = 'N' then
2865 --
2866 hr_utility.set_location(l_proc, 130);
2867 --
2868 hr_mass_move_api.move_position
2869 (p_validate => FALSE
2870 ,p_batch_run_number => l_batch_run_number
2871 ,p_position_id => mmposrec.position_id
2872 ,p_job_id => mmposrec.target_job_id
2873 ,p_object_version_number => mmposrec.object_version_number
2874 ,p_date_effective => l_effective_date
2875 ,p_organization_id => l_new_organization_id
2876 ,p_deactivate_old_position => l_deactivate_boolean
2877 ,p_mass_move_id => p_mass_move_id
2878 ,p_time_normal_start => l_time_normal_start
2879 ,p_time_normal_finish => l_time_normal_finish
2880 ,p_normal_hours => l_normal_hours
2881 ,p_frequency => l_frequency
2882 ,p_location_id => l_location_id
2883 ,p_segment1 => mmposrec.segment1
2884 ,p_segment2 => mmposrec.segment2
2885 ,p_segment3 => mmposrec.segment3
2886 ,p_segment4 => mmposrec.segment4
2887 ,p_segment5 => mmposrec.segment5
2888 ,p_segment6 => mmposrec.segment6
2889 ,p_segment7 => mmposrec.segment7
2890 ,p_segment8 => mmposrec.segment8
2891 ,p_segment9 => mmposrec.segment9
2892 ,p_segment10 => mmposrec.segment10
2893 ,p_segment11 => mmposrec.segment11
2894 ,p_segment12 => mmposrec.segment12
2895 ,p_segment13 => mmposrec.segment13
2896 ,p_segment14 => mmposrec.segment14
2897 ,p_segment15 => mmposrec.segment15
2898 ,p_segment16 => mmposrec.segment16
2899 ,p_segment17 => mmposrec.segment17
2900 ,p_segment18 => mmposrec.segment18
2901 ,p_segment19 => mmposrec.segment19
2902 ,p_segment20 => mmposrec.segment20
2903 ,p_segment21 => mmposrec.segment21
2904 ,p_segment22 => mmposrec.segment22
2905 ,p_segment23 => mmposrec.segment23
2906 ,p_segment24 => mmposrec.segment24
2907 ,p_segment25 => mmposrec.segment25
2908 ,p_segment26 => mmposrec.segment26
2909 ,p_segment27 => mmposrec.segment27
2910 ,p_segment28 => mmposrec.segment28
2911 ,p_segment29 => mmposrec.segment29
2912 ,p_segment30 => mmposrec.segment30
2913 ,p_new_position_id => l_new_position_id --out
2914 ,p_new_job_id => l_new_job_id --out
2915 ,p_new_object_version_number => l_new_pos_ovn --out
2916 ,p_valid_grades_changed_warning => l_valid_grades_changed_warning --out
2917 ,p_pos_exists_warning => l_pos_exists_warning --out
2918 ,p_pos_date_range_warning => l_pos_date_range_warning --out
2919 ,p_pos_pending_close_warning => l_pos_pending_close_warning --out
2920 ,p_pos_jbe_not_moved_warning => l_pos_jbe_not_moved_warning --out
2921 ,p_pos_vac_not_moved_warning => l_pos_vac_not_moved_warning --out
2922 );
2923 --
2924 hr_utility.set_location(l_proc, 140);
2925 --
2926 if (l_new_position_id is not null) then
2927 --
2928 -- If it made it this far, update the position to 'moved'
2929 --
2930 update per_mm_positions
2931 set position_moved = 'Y',
2932 target_job_id = l_new_job_id,
2933 new_position_id = l_new_position_id
2934 where position_id = mmposrec.position_id
2935 and mass_move_id = p_mass_move_id;
2936 --
2937 end if;
2938 --
2939 hr_utility.set_location(l_proc, 150);
2940 --
2941 -- in the case of a reexecution, set the vals from the table.
2942 --
2943 else
2944 --
2945 hr_utility.set_location(l_proc, 160);
2946 --
2947 l_new_position_id := mmposrec.new_position_id;
2948 l_new_job_id := mmposrec.target_job_id;
2949 --
2950 end if; /* end if position_moved = 'N' */
2951 --
2952 -- Get the location and working condition information from the new
2953 -- position.
2954 --
2955 open csr_get_new_pos_defaults;
2956 fetch csr_get_new_pos_defaults into
2957 l_new_pos_time_normal_start,
2958 l_new_pos_time_normal_finish,
2959 l_new_pos_normal_hours,
2960 l_new_pos_frequency,
2961 l_new_pos_location_id;
2962 --
2963 close csr_get_new_pos_defaults;
2964 --
2965 hr_utility.set_location(l_proc, 170);
2966 --
2967 move_assignments
2968 (p_mass_move_id => p_mass_move_id
2969 ,p_old_position_id => mmposrec.position_id
2970 ,p_new_position_id => l_new_position_id
2971 ,p_new_job_id => l_new_job_id
2972 ,p_effective_date => l_effective_date
2973 ,p_new_organization_id => l_new_organization_id
2974 ,p_pos_time_normal_start => l_new_pos_time_normal_start
2975 ,p_pos_time_normal_finish => l_new_pos_time_normal_finish
2976 ,p_pos_normal_hours => l_new_pos_normal_hours
2977 ,p_pos_frequency => l_new_pos_frequency
2978 ,p_pos_location_id => l_new_pos_location_id
2979 ,p_org_time_normal_start => l_org_time_normal_start
2980 ,p_org_time_normal_finish => l_org_time_normal_finish
2981 ,p_org_normal_hours => l_org_normal_hours
2982 ,p_org_frequency => l_org_frequency
2983 ,p_org_location_id => l_org_location_id
2984 ,p_legislation_code => l_legislation_code
2985 ,p_bg_time_normal_start => l_bg_time_normal_start
2986 ,p_bg_time_normal_finish => l_bg_time_normal_finish
2987 ,p_bg_normal_hours => l_bg_normal_hours
2988 ,p_bg_frequency => l_bg_frequency
2989 ,p_bg_location_id => l_bg_location_id
2990 ,p_batch_run_number => l_batch_run_number);
2991 --
2992 hr_utility.set_location(l_proc, 180);
2993 --
2994 -- Check for position warning messages
2995 --
2996 if l_pos_exists_warning = TRUE then
2997 --
2998 hr_utility.set_location(l_proc, 190);
2999 --
3000 ROLLBACK TO move_position;
3001 --
3002 hr_utility.set_message(801,'HR_51330_MMV_POS_EXISTS');
3003 --
3004 hr_batch_message_line_api.create_message_line
3005 (p_validate => FALSE
3006 ,p_batch_run_number => l_batch_run_number
3007 ,p_api_name => 'hr_mass_move_api.move_position'
3008 ,p_status => 'S'
3009 ,p_error_number => '51130'
3010 ,p_error_message => hr_utility.get_message
3011 ,p_extended_error_message => fnd_message.get
3012 ,p_source_row_information => l_position_name
3013 ,p_line_id => l_dummy_line_id);
3014 --
3015 end if;
3016 --
3017 if l_pos_date_range_warning = TRUE then
3018 --
3019 hr_utility.set_location(l_proc, 200);
3020 --
3021 ROLLBACK TO move_position;
3022 --
3023 hr_utility.set_message(801,'HR_51331_MMV_POS_INVALID_DATE');
3024 --
3025 hr_batch_message_line_api.create_message_line
3026 (p_validate => FALSE
3027 ,p_batch_run_number => l_batch_run_number
3028 ,p_api_name => 'hr_mass_move_api.move_position'
3029 ,p_status => 'S'
3030 ,p_error_number => '51331'
3031 ,p_error_message => hr_utility.get_message
3032 ,p_extended_error_message => fnd_message.get
3033 ,p_source_row_information => l_position_name
3034 ,p_line_id => l_dummy_line_id);
3035 --
3036 end if;
3037 --
3038 if l_pos_pending_close_warning = TRUE then
3039 --
3040 hr_utility.set_location(l_proc, 210);
3041 --
3042 hr_utility.set_message(801,'HR_51335_MMV_POS_CLOSED');
3043 --
3044 hr_batch_message_line_api.create_message_line
3045 (p_validate => FALSE
3046 ,p_batch_run_number => l_batch_run_number
3047 ,p_api_name => 'hr_mass_move_api.move_position'
3048 ,p_status => 'S'
3049 ,p_error_number => '51335'
3050 ,p_error_message => hr_utility.get_message
3051 ,p_extended_error_message => fnd_message.get
3052 ,p_source_row_information => l_position_name
3053 ,p_line_id => l_dummy_line_id);
3054 --
3055 end if;
3056 --
3057 if l_valid_grades_changed_warning = TRUE then
3058 --
3059 hr_utility.set_location(l_proc, 220);
3060 --
3061 hr_utility.set_message(801,'HR_51128_MMV_VGR_ALTERED');
3062 --
3063 hr_batch_message_line_api.create_message_line
3064 (p_validate => FALSE
3065 ,p_batch_run_number => l_batch_run_number
3066 ,p_api_name => 'hr_mass_move_api.move_position'
3067 ,p_status => 'S'
3068 ,p_error_number => '51128'
3069 ,p_error_message => hr_utility.get_message
3070 ,p_extended_error_message => fnd_message.get
3071 ,p_source_row_information => l_position_name
3072 ,p_line_id => l_dummy_line_id);
3073 --
3074 end if;
3075 --
3076 if l_pos_jbe_not_moved_warning = TRUE then
3077 --
3078 hr_utility.set_location(l_proc, 230);
3079 --
3080 hr_utility.set_message(801,'HR_51334_MMV_POS_JBE_NOT_MOVED');
3081 --
3082 hr_batch_message_line_api.create_message_line
3083 (p_validate => FALSE
3084 ,p_batch_run_number => l_batch_run_number
3085 ,p_api_name => 'hr_mass_move_api.move_position'
3086 ,p_status => 'S'
3087 ,p_error_number => '51334'
3088 ,p_error_message => hr_utility.get_message
3089 ,p_extended_error_message => fnd_message.get
3090 ,p_source_row_information => l_position_name
3091 ,p_line_id => l_dummy_line_id);
3092 --
3093 end if;
3094 --
3095 if l_pos_vac_not_moved_warning = TRUE then
3096 --
3097 hr_utility.set_location(l_proc, 240);
3098 --
3099 hr_utility.set_message(801,'HR_51333_MMV_POS_VAC_NOT_MOVED');
3100 --
3101 hr_batch_message_line_api.create_message_line
3102 (p_validate => FALSE
3103 ,p_batch_run_number => l_batch_run_number
3104 ,p_api_name => 'hr_mass_move_api.move_position'
3105 ,p_status => 'S'
3106 ,p_error_number => '51333'
3107 ,p_error_message => hr_utility.get_message
3108 ,p_extended_error_message => fnd_message.get
3109 ,p_source_row_information => l_position_name
3110 ,p_line_id => l_dummy_line_id);
3111 --
3112 end if;
3113 --
3114 end; --begin
3115 --
3116 end loop; /* end loop through positions in per_mm_positions */
3117 --
3118 hr_utility.set_location(l_proc, 250);
3119 --
3120 -- Call Customer-Specific stub to handle post-mass move validation
3121 --
3122 hr_mass_move_cus.post_mass_move
3123 (p_mass_move_id => p_mass_move_id);
3124 --
3125 -- If everything has completed successfully, update the massmove status
3126 --
3127 l_errors_exist := 'N';
3128 open csr_chk_errors;
3129 fetch csr_chk_errors into l_errors_exist;
3130 close csr_chk_errors;
3131 --
3132 hr_utility.set_location(l_proc, 260);
3133 --
3134 l_warnings_exist := 'N';
3135 open csr_chk_warnings;
3136 fetch csr_chk_warnings into l_warnings_exist;
3137 close csr_chk_warnings;
3138 --
3139 if l_errors_exist = 'Y' then
3140 --
3141 hr_utility.set_location(l_proc, 270);
3142 --
3143 rollback to massmove;
3144 --
3145 update per_mass_moves
3146 set batch_run_number = l_batch_run_number,
3147 status = 'ER'
3148 where mass_move_id = p_mass_move_id;
3149 --
3150 elsif l_warnings_exist = 'Y' then
3151 --
3152 hr_utility.set_location(l_proc, 280);
3153 --
3154 update per_mass_moves
3155 set batch_run_number = l_batch_run_number,
3156 status = 'WA'
3157 where mass_move_id = p_mass_move_id;
3158 --
3159 else
3160 --
3161 hr_utility.set_location(l_proc, 290);
3162 --
3163 update per_mass_moves
3164 set batch_run_number = l_batch_run_number,
3165 status = 'CO'
3166 where mass_move_id = p_mass_move_id;
3167 --
3168 end if;
3169 --
3170 begin
3171 --
3172 -- Start of API User Hook for the after hook of mass_move
3173 --
3174 hr_mass_move_bk1.mass_move_a
3175 (p_mass_move_id => p_mass_move_id
3176 ,p_business_group_id => l_business_group_id
3177 );
3178 exception
3179 when hr_api.cannot_find_prog_unit then
3180 hr_api.cannot_find_prog_unit_error
3181 (p_module_name => 'MASS_MOVE'
3182 ,p_hook_type => 'AP'
3183 );
3184 --
3185 -- End of API User Hook for the after hook of mass_move
3186 --
3187 end;
3188 --
3189 --
3190 -- When in validation only mode raise the Validate_Enabled exception
3191 --
3192 if p_validate then
3193 --
3194 raise hr_api.validate_enabled;
3195 --
3196 end if;
3197 --
3198 hr_utility.set_location(' Leaving:'||l_proc, 997);
3199 --
3200 exception
3201 --
3202 when hr_api.validate_enabled then
3203 --
3204 hr_utility.set_location(' Leaving:'||l_proc, 998);
3205 --
3206 -- As the Validate_Enabled exception has been raised
3207 -- we must rollback to the savepoint
3208 --
3209 ROLLBACK TO activate_mass_move;
3210 --
3211 when others then
3212 --
3213 hr_utility.set_location(' Leaving:'||l_proc, 999);
3214 --
3215 rollback to massmove;
3216 --
3217 hr_batch_message_line_api.create_message_line
3218 (p_validate => FALSE
3219 ,p_batch_run_number => l_batch_run_number
3220 ,p_api_name => 'hr_mass_move_api.mass_move'
3221 ,p_status => 'F'
3222 ,p_error_number => sqlcode
3223 ,p_error_message => sqlerrm
3224 ,p_extended_error_message => fnd_message.get
3225 ,p_source_row_information => l_position_name
3226 ,p_line_id => l_dummy_line_id);
3227 --
3228 update per_mass_moves
3229 set batch_run_number = l_batch_run_number,
3230 status = 'ER'
3231 where mass_move_id = p_mass_move_id;
3232 --
3233 end mass_move;
3234 --
3235 end hr_mass_move_api;