[Home] [Help]
PACKAGE BODY: APPS.HR_MASS_MOVE_API
Source
1 Package Body hr_mass_move_api as
2 /* $Header: pemmvapi.pkb 120.4.12020000.3 2012/09/12 09:31:11 swrajapa 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 => null -- fix for bug#10311425
1404 ,p_fill_by_date => null -- fix for bug#10311425
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
1692 l_analysis_criteria_id :=null; -- setting the value to null for bug14590707
1693 end loop;
1694 --
1695 -- For each valid grade that is in mm valid grades but not in per
1696 -- valid grades will be inserted into per valid grades.
1697 --
1698 for csr_vgr_rec in csr_vgr loop
1699 --
1700 hr_utility.set_location(l_proc, 100);
1701 --
1702 hr_valid_grade_api.create_valid_grade
1703 (p_grade_id => csr_vgr_rec.target_grade_id
1704 ,p_date_from => p_date_effective
1705 ,p_effective_date => p_date_effective --Added for Bug# 1760707
1706 ,p_position_id => l_new_position_id
1707 ,p_attribute_category => csr_vgr_rec.attribute_category
1708 ,p_attribute1 => csr_vgr_rec.attribute1
1709 ,p_attribute2 => csr_vgr_rec.attribute2
1710 ,p_attribute3 => csr_vgr_rec.attribute3
1711 ,p_attribute4 => csr_vgr_rec.attribute4
1712 ,p_attribute5 => csr_vgr_rec.attribute5
1713 ,p_attribute6 => csr_vgr_rec.attribute6
1714 ,p_attribute7 => csr_vgr_rec.attribute7
1715 ,p_attribute8 => csr_vgr_rec.attribute8
1716 ,p_attribute9 => csr_vgr_rec.attribute9
1717 ,p_attribute10 => csr_vgr_rec.attribute10
1718 ,p_attribute11 => csr_vgr_rec.attribute11
1719 ,p_attribute12 => csr_vgr_rec.attribute12
1720 ,p_attribute13 => csr_vgr_rec.attribute13
1721 ,p_attribute14 => csr_vgr_rec.attribute14
1722 ,p_attribute15 => csr_vgr_rec.attribute15
1723 ,p_attribute16 => csr_vgr_rec.attribute16
1724 ,p_attribute17 => csr_vgr_rec.attribute17
1725 ,p_attribute18 => csr_vgr_rec.attribute18
1726 ,p_attribute19 => csr_vgr_rec.attribute19
1727 ,p_attribute20 => csr_vgr_rec.attribute20
1728 ,p_valid_grade_id => l_valid_grade_id
1729 ,p_object_version_number => l_vgr_object_version_number
1730 );
1731 end loop;
1732 --
1733 -- If deactivate_old_position is true then close down the 'old'
1734 -- position
1735 --
1736 hr_utility.set_location(l_proc, 110);
1737 --
1738 -- If the old position is to be deactivated then
1739 -- determine the availability status
1740 --
1741 if p_deactivate_old_position then
1742 --
1743 hr_utility.set_location(l_proc, 120);
1744 --
1745 l_date_end := p_date_effective - 1;
1746 --
1747 -- Added as part of fix for bug 2943725
1748 --
1749 --
1750 -- Get the current availability status of the position
1751 --
1752 open csr_current_status;
1753 fetch csr_current_status into l_current_system_type_cd;
1754 close csr_current_status;
1755 --
1756 hr_utility.set_location(l_proc||'/'||l_current_system_type_cd, 130);
1757 hr_utility.set_location(l_proc||'/'||p_position_id, 131);
1758 hr_utility.set_location(l_proc||'/'||p_date_effective, 132);
1759 --
1760 -- If the status is Active then change the status to Eliminated
1761 --
1762 if l_current_system_type_cd = 'ACTIVE' then
1763 --
1764 hr_utility.set_location(l_proc, 140);
1765 --
1766 l_system_type_cd := 'ELIMINATED';
1767 --
1768 -- Else if the status is Proposed then the status will
1769 -- become Deleted.
1770 --
1771 elsif l_current_system_type_cd = 'PROPOSED' then
1772 --
1773 hr_utility.set_location(l_proc, 150);
1774 --
1775 l_system_type_cd := 'DELETED';
1776 --
1777 end if;
1778 --
1779 -- Get the appropriate avialability status
1780 --
1781 open csr_avail_status(p_system_type_cd => l_system_type_cd);
1782 fetch csr_avail_status into p_availability_status_id;
1783 close csr_avail_status;
1784 --
1785 hr_utility.set_location(l_proc||'/'||l_system_type_cd, 160);
1786 --
1787 -- End of fix for bug 2943725
1788 --
1789 l_pos_dt_mode := 'UPDATE';
1790 --
1791 open csr_pos_current_esd(p_position_id , p_date_effective);
1792 fetch csr_pos_current_esd into l_pos_dt_mode;
1793 close csr_pos_current_esd;
1794 --
1795 hr_position_api.update_position
1796 (p_position_id => p_position_id
1797 ,p_object_version_number => l_object_version_number
1798 ,p_effective_start_date =>l_effective_start_date
1799 ,p_effective_end_date =>l_effective_end_date
1800 --,p_date_end => l_date_end
1801 ,p_position_definition_id => l_old_position_def
1802 ,p_name => l_old_name
1803 ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
1804 ,p_availability_status_id => p_availability_status_id
1805 ,p_effective_date => p_date_effective --p_effective_date
1806 ,p_datetrack_mode => l_pos_dt_mode
1807 );
1808 /*
1809 hr_position_api.update_position
1810 (p_position_id => p_position_id
1811 ,p_object_version_number => l_object_version_number
1812 ,p_date_end => l_date_end
1813 ,p_position_definition_id => l_old_position_def
1814 ,p_name => l_old_name
1815 ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
1816 );
1817 */
1818 --
1819 hr_utility.set_location(l_proc, 170);
1820 --
1821 end if;
1822 --
1823 -- This is customer specific processing
1824 --
1825 hr_mass_move_cus.post_move_position
1826 (p_validate => FALSE
1827 ,p_position_id => p_position_id
1828 ,p_object_version_number => l_object_version_number
1829 ,p_date_effective => p_date_effective
1830 ,p_business_group_id => l_business_group_id
1831 ,p_organization_id => p_organization_id
1832 ,p_deactivate_old_position => p_deactivate_old_position
1833 ,p_mass_move_id => p_mass_move_id
1834 ,p_new_position_id => l_new_position_id
1835 ,p_new_object_version_number => l_new_object_version_number
1836 );
1837 --
1838 -- When in validation only mode raise the Validate_Enabled exception
1839 --
1840 if p_validate then
1841 raise hr_api.validate_enabled;
1842 end if;
1843 --
1844 -- Set all out arguments
1845 --
1846 p_object_version_number := l_object_version_number;
1847 p_new_position_id := l_new_position_id;
1848 p_new_job_id := l_new_job_id;
1849 p_new_object_version_number := l_new_object_version_number;
1850 p_valid_grades_changed_warning := l_valid_grades_changed_warning;
1851 p_pos_exists_warning := l_pos_exists_warning;
1852 p_pos_date_range_warning := l_pos_date_range_warning;
1853 p_pos_pending_close_warning := l_pos_pending_close_warning;
1854 p_pos_jbe_not_moved_warning := l_pos_jbe_not_moved_warning;
1855 p_pos_vac_not_moved_warning := l_pos_vac_not_moved_warning;
1856 --
1857 hr_utility.set_location(' Leaving:'||l_proc, 997);
1858 --
1859 exception
1860 --
1861 when hr_api.validate_enabled then
1862 --
1863 -- As the Validate_Enabled exception has been raised
1864 -- we must rollback to the savepoint
1865 --
1866 --
1867 ROLLBACK TO move_position;
1868 --
1869 -- Only set output warning arguments
1870 -- (Any key or derived arguments must be set to null
1871 -- when validation only mode is being used.)
1872 --
1873 p_object_version_number := p_object_version_number;
1874 p_new_position_id := null;
1875 p_new_job_id := null;
1876 p_new_object_version_number := null;
1877 p_valid_grades_changed_warning := l_valid_grades_changed_warning;
1878 p_pos_exists_warning := l_pos_exists_warning;
1879 p_pos_date_range_warning := l_pos_date_range_warning;
1880 p_pos_pending_close_warning := l_pos_pending_close_warning;
1881 p_pos_jbe_not_moved_warning := l_pos_jbe_not_moved_warning;
1882 p_pos_vac_not_moved_warning := l_pos_vac_not_moved_warning;
1883 --
1884 hr_utility.set_location(' Leaving:'||l_proc, 998);
1885 --
1886 when others then
1887 --
1888 hr_utility.set_location(' Leaving:'||l_proc, 999);
1889 --
1890 -- if it is an business rule violation error then only rollback
1891 -- this positon record.
1892 --
1893 if sqlcode = -20001 then
1894 --
1895 rollback to move_position;
1896 --
1897 hr_batch_message_line_api.create_message_line
1898 (p_validate => FALSE
1899 ,p_batch_run_number => p_batch_run_number
1900 ,p_api_name => 'hr_mass_move_api.mass_move'
1901 ,p_status => 'S'
1902 ,p_error_number => sqlcode
1903 ,p_error_message => sqlerrm
1904 ,p_extended_error_message => fnd_message.get
1905 ,p_source_row_information => l_name
1906 ,p_line_id => l_dummy_line_id);
1907 --
1908 -- set in out parameters and set out parameters
1909 --
1910 p_object_version_number := l_ovn;
1911 p_new_position_id := null;
1912 p_new_job_id := null;
1913 p_new_object_version_number := null;
1914 p_valid_grades_changed_warning := false;
1915 p_pos_exists_warning := false;
1916 p_pos_date_range_warning := false;
1917 p_pos_pending_close_warning := false;
1918 p_pos_jbe_not_moved_warning := false;
1919 p_pos_vac_not_moved_warning := false;
1920 --
1921 else
1922 --
1923 raise;
1924 --
1925 end if;
1926 --
1927 end move_position;
1928 --
1929 -- ----------------------------------------------------------------------------
1930 -- |--------------------------< move_assignments >----------------------------|
1931 -- ----------------------------------------------------------------------------
1932 procedure move_assignments
1933 (p_validate in boolean default false
1934 ,p_mass_move_id in number
1935 ,p_old_position_id in number
1936 ,p_new_position_id in number
1937 ,p_new_job_id in number
1938 ,p_effective_date in date
1939 ,p_new_organization_id in number
1940 ,p_pos_time_normal_start in varchar2
1941 ,p_pos_time_normal_finish in varchar2
1942 ,p_pos_normal_hours in number
1943 ,p_pos_frequency in varchar2
1944 ,p_pos_location_id in number
1945 ,p_org_time_normal_start in varchar2
1946 ,p_org_time_normal_finish in varchar2
1947 ,p_org_normal_hours in number
1948 ,p_org_frequency in varchar2
1949 ,p_org_location_id in number
1950 ,p_legislation_code in varchar2
1951 ,p_bg_time_normal_start in varchar2
1952 ,p_bg_time_normal_finish in varchar2
1953 ,p_bg_normal_hours in number
1954 ,p_bg_frequency in varchar2
1955 ,p_bg_location_id in number
1956 ,p_batch_run_number in number
1957 )
1958 is
1959 --
1960 l_proc varchar2(72) := g_package||'move_assignments';
1961 l_vacancies_exist varchar2(1);
1962 l_apl_asg_new_ovn number;
1963 l_out_comment_id number;
1964 l_out_people_group_id number;
1965 l_new_job_id number;
1966 l_out_group_name varchar2(240);
1967 -- Added new dummy var
1968 -- Bug 944911
1969 l_out_concatenated_segments varchar2(240);
1970 l_out_soft_coding_keyflx_id number;
1971 l_out_effective_start_date date;
1972 l_out_effective_end_date date;
1973 l_apl_asg_pos_vacancy_warning boolean;
1974 l_org_now_no_manager_warning boolean;
1975 l_other_manager_warning boolean;
1976 l_soft_coding_keyflex_id number(15);
1977 l_entries_changed_warning varchar2(1);
1978 l_time_normal_start varchar2(150);
1979 l_time_normal_finish varchar2(150);
1980 l_normal_hours number(22,3);
1981 l_frequency varchar2(1);
1982 l_location_id number(15);
1983 l_mm_assignment_id number(15);
1984 l_effective_start_date date;
1985 l_effective_end_date date;
1986 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
1987 l_batch_run_number number(15);
1988 l_dummy_line_id number(15);
1989 l_segment1 varchar2(150);
1990 l_cagr_concatenated_segments varchar2(240);
1991 l_cagr_grade_def_id number(15);
1992 asg_vacancy_warning exception;
1993 future_asg_warning exception;
1994 l_exists varchar2(1);
1995 --
1996 -- Out Parameters for CWK Assignments
1997 --
1998 l_people_group_name VARCHAR2(240);
1999 l_people_group_id NUMBER;
2000 --l_org_now_no_manager_warning BOOLEAN;
2001 --l_other_manager_warning BOOLEAN;
2002 l_spp_delete_warning BOOLEAN;
2003 --l_entries_changed_warning VARCHAR2(1);
2004 l_tax_district_changed_warning BOOLEAN;
2005 --
2006 cursor csr_future_asg_changes is
2007 select null
2008 from per_assignments_f asg,
2009 per_mass_moves mm,
2010 per_mm_assignments mmasg
2011 where mmasg.assignment_id = l_mm_assignment_id
2012 and asg.assignment_id = mmasg.assignment_id
2013 and mmasg.mass_move_id = p_mass_move_id
2014 and mmasg.mass_move_id = mm.mass_move_id
2015 and mmasg.position_id = p_old_position_id
2016 and asg.effective_start_date > p_effective_date;
2017 --
2018 cursor csr_get_mm_asg is
2019 select mm_asg.assignment_id,
2020 mm_asg.default_from,
2021 mm_asg.grade_id,
2022 mm_asg.tax_unit_id,
2023 asg.location_id,
2024 asg.frequency,
2025 asg.normal_hours,
2026 asg.time_normal_start,
2027 asg.time_normal_finish,
2028 asg.assignment_type,
2029 asg.object_version_number,
2030 asg.vacancy_id,
2031 per.full_name,
2032 org.name orgname,
2033 neworg.name neworgname
2034 from per_mm_assignments mm_asg,
2035 per_assignments_f asg,
2036 per_people_f per,
2037 per_organization_units org,
2038 per_mass_moves mm,
2039 per_organization_units neworg
2040 where mm_asg.position_id = p_old_position_id
2041 and mm_asg.assignment_id = asg.assignment_id
2042 and mm_asg.mass_move_id = p_mass_move_id
2043 and mm_asg.mass_move_id = mm.mass_move_id
2044 and mm.new_organization_id = neworg.organization_id
2045 and asg.organization_id = org.organization_id
2046 and p_effective_date between asg.effective_start_date
2047 and asg.effective_end_date
2048 and p_effective_date between per.effective_start_date
2049 and per.effective_end_date
2050 and asg.person_id = per.person_id
2051 and assignment_moved = 'N'
2052 and select_assignment = 'Y';
2053 --
2054 begin
2055 --
2056 hr_utility.set_location('Entering:'|| l_proc, 10);
2057 --
2058 -- Issue a savepoint
2059 --
2060 savepoint move_assignment;
2061 --
2062 --
2063 for mmasgrec in csr_get_mm_asg loop
2064 --
2065 begin
2066 --
2067 savepoint move_asg;
2068 --
2069 hr_utility.set_location(l_proc||'/'||mmasgrec.assignment_id, 20);
2070 --
2071 -- GRE/Legal Entity will only move for US legislation.
2072 --
2073 if p_legislation_code = 'US' then
2074 --
2075 l_segment1 := to_char(mmasgrec.tax_unit_id);
2076 --
2077 else
2078 --
2079 l_segment1 := hr_api.g_varchar2;
2080 --
2081 end if;
2082 --
2083 if mmasgrec.default_from = 'B' then
2084 --
2085 hr_utility.set_location(l_proc, 30);
2086 --
2087 l_location_id := p_bg_location_id;
2088 l_frequency := p_bg_frequency;
2089 l_normal_hours := p_bg_normal_hours;
2090 l_time_normal_finish := p_bg_time_normal_finish;
2091 l_time_normal_start := p_bg_time_normal_start;
2092 --
2093 elsif mmasgrec.default_from = 'O' then
2094 --
2095 hr_utility.set_location(l_proc, 40);
2096 --
2097 l_location_id := p_org_location_id;
2098 l_frequency := p_org_frequency;
2099 l_normal_hours := p_org_normal_hours;
2100 l_time_normal_finish := p_org_time_normal_finish;
2101 l_time_normal_start := p_org_time_normal_start;
2102 --
2103 elsif mmasgrec.default_from = 'P' then
2104 --
2105 hr_utility.set_location(l_proc, 50);
2106 --
2107 l_location_id := p_pos_location_id;
2108 l_frequency := p_pos_frequency;
2109 l_normal_hours := p_pos_normal_hours;
2110 l_time_normal_finish := p_pos_time_normal_finish;
2111 l_time_normal_start := p_pos_time_normal_start;
2112 --
2113 elsif mmasgrec.default_from = 'A' then
2114 --
2115 hr_utility.set_location(l_proc, 60);
2116 --
2117 l_location_id := mmasgrec.location_id;
2118 l_frequency := mmasgrec.frequency;
2119 l_normal_hours := mmasgrec.normal_hours;
2120 l_time_normal_finish := mmasgrec.time_normal_finish;
2121 l_time_normal_start := mmasgrec.time_normal_start;
2122 --
2123 end if;
2124 --
2125 if mmasgrec.vacancy_id is not null then
2126 --
2127 hr_utility.set_location(l_proc, 70);
2128 --
2129 raise asg_vacancy_warning;
2130 --
2131 end if;
2132 --
2133 open csr_future_asg_changes;
2134 fetch csr_future_asg_changes into l_exists;
2135 --
2136 l_mm_assignment_id := mmasgrec.assignment_id;
2137 --
2138 if csr_future_asg_changes%found then
2139 --
2140 hr_utility.set_location(l_proc, 80);
2141 close csr_future_asg_changes;
2142 raise future_asg_warning;
2143 --
2144 end if;
2145 --
2146 close csr_future_asg_changes;
2147 --
2148 if mmasgrec.assignment_type = 'A' then
2149 --
2150 hr_utility.set_location(l_proc, 90);
2151 --
2152 hr_assignment_api.update_apl_asg
2153 (p_validate => FALSE
2154 ,p_effective_date => p_effective_date
2155 ,p_datetrack_update_mode => 'UPDATE'
2156 ,p_assignment_id => mmasgrec.assignment_id
2157 ,p_object_version_number => mmasgrec.object_version_number
2158 ,p_grade_id => mmasgrec.grade_id
2159 ,p_position_id => p_new_position_id
2160 ,p_job_id => p_new_job_id
2161 ,p_organization_id => p_new_organization_id
2162 ,p_location_id => l_location_id
2163 ,p_frequency => l_frequency
2164 ,p_normal_hours => l_normal_hours
2165 ,p_time_normal_finish => l_time_normal_finish
2166 ,p_time_normal_start => l_time_normal_start
2167 ,p_comment_id => l_out_comment_id
2168 ,p_people_group_id => l_out_people_group_id
2169 ,p_group_name => l_out_group_name
2170 ,p_effective_start_date => l_out_effective_start_date
2171 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
2172 ,p_cagr_grade_def_id => l_cagr_grade_def_id
2173 --
2174 -- Bug 944911 Added out param into a dummy var
2175 --
2176 ,p_concatenated_segments => l_out_concatenated_segments
2177 ,p_soft_coding_keyflex_id => l_out_soft_coding_keyflx_id
2178 ,p_effective_end_date => l_out_effective_end_date);
2179 --
2180 -- Assignment is for a Contingent Worker
2181 --
2182 elsif mmasgrec.assignment_type = 'C' then
2183 --
2184 hr_utility.set_location(l_proc, 95);
2185 --
2186 hr_mass_move_api.move_cwk_asg
2187 (p_validate => p_validate
2188 ,p_effective_date => p_effective_date
2189 ,p_datetrack_update_mode => 'UPDATE'
2190 ,p_assignment_id => mmasgrec.assignment_id
2191 ,p_object_version_number => mmasgrec.object_version_number
2192 ,p_grade_id => mmasgrec.grade_id
2193 ,p_position_id => p_new_position_id
2194 ,p_job_id => p_new_job_id
2195 ,p_location_id => l_location_id
2196 ,p_organization_id => p_new_organization_id
2197 ,p_people_group_name => l_people_group_name
2198 ,p_effective_start_date => l_effective_start_date
2199 ,p_effective_end_date => l_effective_end_date
2200 ,p_people_group_id => l_people_group_id
2201 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2202 ,p_other_manager_warning => l_other_manager_warning
2203 ,p_spp_delete_warning => l_spp_delete_warning
2204 ,p_entries_changed_warning => l_entries_changed_warning
2205 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
2206 --
2207 else -- assignment_type = 'E'
2208 --
2209 hr_utility.set_location(l_proc, 100);
2210 --
2211 hr_mass_move_api.move_emp_asg
2212 (p_validate => FALSE
2213 ,p_mass_move_id => p_mass_move_id
2214 ,p_effective_date => p_effective_date
2215 ,p_assignment_id => mmasgrec.assignment_id
2216 ,p_object_version_number => mmasgrec.object_version_number
2217 ,p_grade_id => mmasgrec.grade_id
2218 ,p_position_id => p_new_position_id
2219 ,p_job_id => p_new_job_id
2220 ,p_organization_id => p_new_organization_id
2221 ,p_location_id => l_location_id
2222 ,p_frequency => l_frequency
2223 ,p_normal_hours => l_normal_hours
2224 ,p_time_normal_finish => l_time_normal_finish
2225 ,p_time_normal_start => l_time_normal_start
2226 ,p_segment1 => l_segment1
2227 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id --out
2228 ,p_effective_start_date => l_effective_start_date --out
2229 ,p_effective_end_date => l_effective_end_date --out
2230 ,p_concatenated_segments => l_concatenated_segments --out
2231 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning --out
2232 ,p_other_manager_warning => l_other_manager_warning --out
2233 ,p_entries_changed_warning => l_entries_changed_warning --out
2234 ,p_spp_delete_warning => l_spp_delete_warning); --out
2235 --
2236 end if; /* if assignment type = 'E' */
2237 --
2238 if l_spp_delete_warning then
2239 --
2240 hr_utility.set_message(800,'HR_289826_SPP_DELETE_WARN_API');
2241 --
2242 hr_batch_message_line_api.create_message_line
2243 (p_validate => FALSE
2244 ,p_batch_run_number => p_batch_run_number
2245 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2246 ,p_status => 'S'
2247 ,p_error_number => '289286'
2248 ,p_error_message => hr_utility.get_message
2249 ,p_extended_error_message => fnd_message.get
2250 ,p_source_row_information => mmasgrec.full_name
2251 ,p_line_id => l_dummy_line_id);
2252 --
2253 end if;
2254 --
2255 if l_org_now_no_manager_warning then
2256 --
2257 hr_utility.set_message(801,'HR_51124_MMV_NO_MGR_EXIST_ORG');
2258 --
2259 hr_batch_message_line_api.create_message_line
2260 (p_validate => FALSE
2261 ,p_batch_run_number => p_batch_run_number
2262 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2263 ,p_status => 'S'
2264 ,p_error_number => '51124'
2265 ,p_error_message => hr_utility.get_message
2266 ,p_extended_error_message => fnd_message.get
2267 ,p_source_row_information => mmasgrec.full_name
2268 ,p_line_id => l_dummy_line_id);
2269 --
2270 end if;
2271 --
2272 if l_other_manager_warning then
2273 --
2274 hr_utility.set_message(801,'HR_51125_MMV_MRE_MGR_EXIST_ORG');
2275 --
2276 hr_batch_message_line_api.create_message_line
2277 (p_validate => FALSE
2278 ,p_batch_run_number => p_batch_run_number
2279 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2280 ,p_status => 'S'
2281 ,p_error_number => '51125'
2282 ,p_error_message => hr_utility.get_message
2283 ,p_extended_error_message => fnd_message.get
2284 ,p_source_row_information => mmasgrec.full_name
2285 ,p_line_id => l_dummy_line_id);
2286 --
2287 end if;
2288 --
2289 if l_entries_changed_warning = 'S' then
2290 --
2291 hr_utility.set_message(801,'HR_51126_MMV_SAL_ENT_ALTERED');
2292 --
2293 hr_batch_message_line_api.create_message_line
2294 (p_validate => FALSE
2295 ,p_batch_run_number => p_batch_run_number
2296 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2297 ,p_status => 'S'
2298 ,p_error_number => '51126'
2299 ,p_error_message => hr_utility.get_message
2300 ,p_extended_error_message => fnd_message.get
2301 ,p_source_row_information => mmasgrec.full_name
2302 ,p_line_id => l_dummy_line_id);
2303 --
2304 end if;
2305 --
2306 if l_entries_changed_warning = 'Y' then
2307 --
2308 hr_utility.set_message(801,'HR_51127_MMV_NON_SAL_ENT_ALR');
2309 --
2310 hr_batch_message_line_api.create_message_line
2311 (p_validate => FALSE
2312 ,p_batch_run_number => p_batch_run_number
2313 ,p_api_name => 'hr_mass_move_api.move_emp_asg'
2314 ,p_status => 'S'
2315 ,p_error_number => '51127'
2316 ,p_error_message => hr_utility.get_message
2317 ,p_extended_error_message => fnd_message.get
2318 ,p_source_row_information => mmasgrec.full_name
2319 ,p_line_id => l_dummy_line_id);
2320 --
2321 end if;
2322 --
2323 hr_utility.set_location(l_proc, 110);
2324 --
2325 --
2326 -- if it has made it this far, the applicant/employee assignment was moved
2327 --
2328 update per_mm_assignments
2329 set assignment_moved = 'Y'
2330 where assignment_id = mmasgrec.assignment_id
2331 and mass_move_id = p_mass_move_id;
2332 --
2333 exception
2334 when asg_vacancy_warning then
2335 --
2336 rollback to move_asg;
2337 --
2338 hr_utility.set_message(801,'HR_51129_MMV_ASG_NOT_MOVED');
2339 --
2340 hr_batch_message_line_api.create_message_line
2341 (p_validate => FALSE
2342 ,p_batch_run_number => p_batch_run_number
2343 ,p_api_name => 'hr_mass_move_api.move_assignment'
2344 ,p_status => 'S'
2345 ,p_error_number => '51129'
2346 ,p_error_message => hr_utility.get_message
2347 ,p_extended_error_message => fnd_message.get
2348 ,p_source_row_information => mmasgrec.full_name
2349 ,p_line_id => l_dummy_line_id);
2350 --
2351 when future_asg_warning then
2352 --
2353 rollback to move_asg;
2354 --
2355 hr_utility.set_message(801,'HR_51103_MMV_ASG_FUTURE_CHG');
2356 --
2357 hr_batch_message_line_api.create_message_line
2358 (p_validate => FALSE
2359 ,p_batch_run_number => p_batch_run_number
2360 ,p_api_name => 'hr_mass_move_api.move_assignment'
2361 ,p_status => 'S'
2362 ,p_error_number => '51103'
2363 ,p_error_message => hr_utility.get_message
2364 ,p_extended_error_message => fnd_message.get
2365 ,p_source_row_information => mmasgrec.full_name
2366 ,p_line_id => l_dummy_line_id);
2367 --
2368 when others then
2369 --
2370 hr_utility.set_location(l_proc, 120);
2371 --
2372 if sqlcode = -20001 then
2373 --
2374 rollback to move_asg;
2375 --
2376 hr_batch_message_line_api.create_message_line
2377 (p_validate => FALSE
2378 ,p_batch_run_number => p_batch_run_number
2379 ,p_api_name => 'hr_mass_move_api.mass_move'
2380 ,p_status => 'S'
2381 ,p_error_number => sqlcode
2382 ,p_error_message => sqlerrm
2383 ,p_extended_error_message => fnd_message.get
2384 ,p_source_row_information => mmasgrec.full_name
2385 ,p_line_id => l_dummy_line_id);
2386 --
2387 else
2388 --
2389 raise;
2390 --
2391 end if;
2392 --
2393 end; --begin
2394 --
2395 end loop; -- for mmasgrec in cursor
2396 --
2397 if p_validate then
2398 raise hr_api.validate_enabled;
2399 end if;
2400 --
2401 hr_utility.set_location(' Leaving:'||l_proc, 997);
2402 --
2403 exception
2404 --
2405 when hr_api.validate_enabled then
2406 --
2407 -- As the Validate_Enabled exception has been raised
2408 -- we must rollback to the savepoint
2409 --
2410 ROLLBACK TO move_assignment;
2411 --
2412 hr_utility.set_location(' Leaving:'||l_proc, 998);
2413 --
2414 when others then
2415 --
2416 -- A validation or unexpected error has occurred
2417 --
2418 -- Added as part of the fix to bug 632479
2419 --
2420 ROLLBACK TO move_assignment;
2421 --
2422 hr_utility.set_location(' Leaving:'||l_proc, 999);
2423 --
2424 raise;
2425 --
2426 end move_assignments;
2427 --
2428 -- ----------------------------------------------------------------------------
2429 -- |------------------------------< mass_move >-------------------------------|
2430 -- ----------------------------------------------------------------------------
2431 --
2432 procedure mass_move
2433 ( p_validate in boolean default false,
2434 p_mass_move_id in number
2435 ) is
2436 --
2437 -- Declare cursors and local variables
2438 --
2439 l_proc varchar2(72) := g_package||'mass_move ';
2440 l_api_updatin boolean;
2441 l_new_organization_id number(15);
2442 l_effective_date date;
2443 l_business_group_id number(15);
2444 l_time_normal_start varchar2(150);
2445 l_time_normal_finish varchar2(150);
2446 l_normal_hours number(22,3);
2447 l_frequency varchar2(1);
2448 l_location_id number(15);
2449 l_org_time_normal_start varchar2(150);
2450 l_org_time_normal_finish varchar2(150);
2451 l_org_normal_hours number(22,3);
2452 l_org_frequency varchar2(30);
2453 l_org_location_id number(15);
2454 l_legislation_code varchar2(150);
2455 l_bg_time_normal_start varchar2(150);
2456 l_bg_time_normal_finish varchar2(150);
2457 l_bg_normal_hours number(22,3);
2458 l_bg_frequency varchar2(30);
2459 l_bg_location_id number(15);
2460 l_new_pos_time_normal_start varchar2(150);
2461 l_new_pos_time_normal_finish varchar2(150);
2462 l_new_pos_normal_hours number(22,3);
2463 l_new_pos_frequency varchar2(30);
2464 l_new_pos_location_id number(15);
2465 l_old_pos_time_normal_start varchar2(150);
2466 l_old_pos_time_normal_finish varchar2(150);
2467 l_old_pos_normal_hours number(22,3);
2468 l_old_pos_frequency varchar2(30);
2469 l_old_pos_location_id number(15);
2470 l_deactivate_boolean boolean;
2471 l_new_position_id number(15);
2472 l_new_job_id number(15);
2473 l_new_pos_ovn number(15);
2474 l_batch_run_number number(15);
2475 l_dummy_line_id number(15);
2476 l_apl_asg_updated_warning boolean;
2477 l_apl_asg_pos_vacancy_warning boolean;
2478 l_errors_exist varchar2(1);
2479 l_warnings_exist varchar2(1);
2480 l_valid_grade_id number(15);
2481 l_object_version_number number(15);
2482 l_position_name varchar2(240);
2483 l_position_id number(15);
2484 l_valid_grades_changed_warning boolean;
2485 l_pos_exists_warning boolean;
2486 l_pos_date_range_warning boolean;
2487 l_pos_pending_close_warning boolean;
2488 l_pos_jbe_not_moved_warning boolean;
2489 l_pos_vac_not_moved_warning boolean;
2490
2491 --
2492 cursor csr_batch_run_number is
2493 select hr_api_batch_message_lines_s.nextval
2494 from dual;
2495 --
2496 cursor csr_lock_per_mass_moves is
2497 select null
2498 from per_mass_moves
2499 where mass_move_id = p_mass_move_id
2500 for update nowait;
2501 --
2502 cursor csr_lock_per_mm_positions is
2503 select null
2504 from per_mm_positions
2505 where mass_move_id = p_mass_move_id
2506 for update nowait;
2507 --
2508 cursor csr_lock_per_mm_assignments is
2509 select null
2510 from per_mm_assignments
2511 where mass_move_id = p_mass_move_id
2512 for update nowait;
2513 --
2514 cursor csr_lock_per_mm_valid_grades is
2515 select null
2516 from per_mm_valid_grades
2517 where mass_move_id = p_mass_move_id
2518 for update nowait;
2519 --
2520 cursor csr_lock_per_mm_job_reqts is
2521 select null
2522 from per_mm_job_requirements
2523 where mass_move_id = p_mass_move_id
2524 for update nowait;
2525 --
2526 cursor csr_get_mass_move_info is
2527 select new_organization_id,
2528 effective_date
2529 from per_mass_moves
2530 where mass_move_id = p_mass_move_id;
2531 --
2532 cursor csr_get_org_defaults is
2533 select business_group_id,
2534 default_start_time,
2535 default_end_time,
2536 fnd_number.canonical_to_number(working_hours),
2537 frequency,
2538 location_id
2539 from per_organization_units
2540 where organization_id = l_new_organization_id;
2541 --
2542 cursor csr_get_busgrp_defaults is
2543 select legislation_code,
2544 default_start_time,
2545 default_end_time,
2546 fnd_number.canonical_to_number(working_hours),
2547 frequency,
2548 location_id
2549 from per_business_groups
2550 where business_group_id = l_business_group_id;
2551 --
2552 cursor csr_get_old_pos_defaults is
2553 select pos.time_normal_start,
2554 pos.time_normal_finish,
2555 fnd_number.canonical_to_number(pos.working_hours),
2556 pos.frequency,
2557 pos.location_id
2558 from hr_positions_f pos
2559 where position_id = l_position_id
2560 and l_effective_date
2561 between effective_start_date
2562 and effective_end_date;
2563 --
2564 cursor csr_get_new_pos_defaults is
2565 select pos.time_normal_start,
2566 pos.time_normal_finish,
2567 fnd_number.canonical_to_number(pos.working_hours),
2568 pos.frequency,
2569 pos.location_id
2570 from hr_positions_f pos
2571 where position_id = l_new_position_id
2572 and l_effective_date
2573 between effective_start_date
2574 and effective_end_date;
2575 --
2576 cursor csr_get_mm_valid_grades is
2577 select mmvgr.position_id,
2578 mmvgr.target_grade_id,
2579 mmvgr.attribute_category,
2580 mmvgr.attribute1,
2581 mmvgr.attribute2,
2582 mmvgr.attribute3,
2583 mmvgr.attribute4,
2584 mmvgr.attribute5,
2585 mmvgr.attribute6,
2586 mmvgr.attribute7,
2587 mmvgr.attribute8,
2588 mmvgr.attribute9,
2589 mmvgr.attribute10,
2590 mmvgr.attribute11,
2591 mmvgr.attribute12,
2592 mmvgr.attribute13,
2593 mmvgr.attribute14,
2594 mmvgr.attribute15,
2595 mmvgr.attribute16,
2596 mmvgr.attribute17,
2597 mmvgr.attribute18,
2598 mmvgr.attribute19,
2599 mmvgr.attribute20
2600 from per_mm_valid_grades mmvgr
2601 where mmvgr.mass_move_id = p_mass_move_id;
2602
2603 --
2604 cursor csr_get_mm_positions is
2605 select pos.name,
2606 mmpos.position_id,
2607 mmpos.object_version_number,
2608 mmpos.deactivate_old_position,
2609 mmpos.new_position_id,
2610 mmpos.target_job_id,
2611 mmpos.position_moved,
2612 mmpos.default_from,
2613 mmpos.segment1,
2614 mmpos.segment2,
2615 mmpos.segment3,
2616 mmpos.segment4,
2617 mmpos.segment5,
2618 mmpos.segment6,
2619 mmpos.segment7,
2620 mmpos.segment8,
2621 mmpos.segment9,
2622 mmpos.segment10,
2623 mmpos.segment11,
2624 mmpos.segment12,
2625 mmpos.segment13,
2626 mmpos.segment14,
2627 mmpos.segment15,
2628 mmpos.segment16,
2629 mmpos.segment17,
2630 mmpos.segment18,
2631 mmpos.segment19,
2632 mmpos.segment20,
2633 mmpos.segment21,
2634 mmpos.segment22,
2635 mmpos.segment23,
2636 mmpos.segment24,
2637 mmpos.segment25,
2638 mmpos.segment26,
2639 mmpos.segment27,
2640 mmpos.segment28,
2641 mmpos.segment29,
2642 mmpos.segment30
2643 from per_mm_positions mmpos,
2644 hr_positions_f pos
2645 where mmpos.mass_move_id = p_mass_move_id
2646 and mmpos.position_id = pos.position_id
2647 and mmpos.select_position = 'Y'
2648 and l_effective_date BETWEEN pos.effective_start_date
2649 AND pos.effective_end_date;
2650 --
2651 -- Check for errors at the end of the mass move
2652 --
2653 cursor csr_chk_errors is
2654 select 'Y'
2655 from hr_api_batch_message_lines
2656 where batch_run_number = l_batch_run_number
2657 and status = 'F';
2658 --
2659 cursor csr_chk_warnings is
2660 select 'Y'
2661 from hr_api_batch_message_lines
2662 where batch_run_number = l_batch_run_number
2663 and status = 'S';
2664 --
2665 begin
2666 --
2667 hr_utility.set_location('Entering:'|| l_proc, 10);
2668 --
2669 -- Issue a savepoint.
2670 --
2671 savepoint activate_mass_move;
2672 --
2673 -- Get a unique run number to identify errors
2674 --
2675 open csr_batch_run_number;
2676 fetch csr_batch_run_number into
2677 l_batch_run_number;
2678 close csr_batch_run_number;
2679 --
2680 -- Get information about the mass move if ID is not null
2681 -- and is valid.
2682 --
2683 if p_mass_move_id is not null then
2684 --
2685 hr_utility.set_location(l_proc, 20);
2686 --
2687 open csr_get_mass_move_info;
2688 fetch csr_get_mass_move_info into l_new_organization_id,
2689 l_effective_date;
2690 --
2691 if csr_get_mass_move_info%notfound then
2692 --
2693 close csr_get_mass_move_info;
2694 --
2695 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2696 hr_utility.raise_error;
2697 --
2698 end if;
2699 --
2700 close csr_get_mass_move_info;
2701 --
2702 else
2703 --
2704 hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
2705 hr_utility.raise_error;
2706 --
2707 end if;
2708 --
2709 -- Issue a savepoint
2710 --
2711 savepoint massmove;
2712 --
2713 -- Lock all tables holding mass move information
2714 --
2715 open csr_lock_per_mass_moves;
2716 close csr_lock_per_mass_moves;
2717 open csr_lock_per_mm_positions;
2718 close csr_lock_per_mm_positions;
2719 open csr_lock_per_mm_assignments;
2720 close csr_lock_per_mm_assignments;
2721 open csr_lock_per_mm_valid_grades;
2722 close csr_lock_per_mm_valid_grades;
2723 open csr_lock_per_mm_job_reqts;
2724 close csr_lock_per_mm_job_reqts;
2725 --
2726 hr_utility.set_location(l_proc, 30);
2727 --
2728 -- Get default location and working conditions from the new
2729 -- organization
2730 --
2731 open csr_get_org_defaults;
2732 fetch csr_get_org_defaults into
2733 l_business_group_id,
2734 l_org_time_normal_start,
2735 l_org_time_normal_finish,
2736 l_org_normal_hours,
2737 l_org_frequency,
2738 l_org_location_id;
2739 --
2740 close csr_get_org_defaults;
2741 --
2742 hr_utility.set_location(l_proc, 40);
2743 --
2744 -- Get default location and working conditions from the
2745 -- business group
2746 --
2747 open csr_get_busgrp_defaults;
2748 fetch csr_get_busgrp_defaults into
2749 l_legislation_code,
2750 l_bg_time_normal_start,
2751 l_bg_time_normal_finish,
2752 l_bg_normal_hours,
2753 l_bg_frequency,
2754 l_bg_location_id;
2755 --
2756 close csr_get_busgrp_defaults;
2757 --
2758 hr_utility.set_location(l_proc, 50);
2759 --
2760 begin
2761 --
2762 -- Start of API User Hook for the before hook of mass_move
2763 -- Specifically placed after the business_group_id is retrieved, based on
2764 -- the new organization.
2765 --
2766 hr_mass_move_bk1.mass_move_b
2767 (p_mass_move_id => p_mass_move_id
2768 ,p_business_group_id => l_business_group_id
2769 );
2770 exception
2771 when hr_api.cannot_find_prog_unit then
2772 hr_api.cannot_find_prog_unit_error
2773 (p_module_name => 'MASS_MOVE'
2774 ,p_hook_type => 'BP'
2775 );
2776 --
2777 -- End of API User Hook for the before hook of mass_move
2778 --
2779 end;
2780 --
2781 -- Call Customer-Specific stub to handle pre-mass move validation
2782 --
2783 hr_mass_move_cus.pre_mass_move
2784 (p_mass_move_id => p_mass_move_id);
2785 --
2786 hr_utility.set_location(l_proc, 60);
2787 --
2788 -- Loop through the position records in per_mm_positions, and
2789 -- process the position, applicant assignments, and employee
2790 -- assignment records.
2791 --
2792 for mmposrec in csr_get_mm_positions loop
2793 --
2794 begin
2795 --
2796 savepoint position;
2797 --
2798 l_position_name := mmposrec.name;
2799 l_position_id := mmposrec.position_id;
2800 --
2801 hr_utility.set_location(l_proc||'/'||
2802 l_position_name||'/'||
2803 l_position_id, 70);
2804 --
2805 if mmposrec.deactivate_old_position = 'Y' then
2806 --
2807 l_deactivate_boolean := TRUE;
2808 --
2809 else
2810 --
2811 l_deactivate_boolean := FALSE;
2812 --
2813 end if;
2814 --
2815 hr_utility.set_location(l_proc, 80);
2816 --
2817 -- Get default location and working conditions from the
2818 -- old position business group
2819 --
2820 open csr_get_old_pos_defaults;
2821 fetch csr_get_old_pos_defaults into
2822 l_old_pos_time_normal_start,
2823 l_old_pos_time_normal_finish,
2824 l_old_pos_normal_hours,
2825 l_old_pos_frequency,
2826 l_old_pos_location_id;
2827 --
2828 close csr_get_old_pos_defaults;
2829 --
2830 hr_utility.set_location(l_proc, 90);
2831 --
2832 if mmposrec.default_from = 'B' then
2833 --
2834 hr_utility.set_location(l_proc, 100);
2835 --
2836 l_location_id := l_bg_location_id;
2837 l_frequency := l_bg_frequency;
2838 l_normal_hours := l_bg_normal_hours;
2839 l_time_normal_finish := l_bg_time_normal_finish;
2840 l_time_normal_start := l_bg_time_normal_start;
2841 --
2842 elsif mmposrec.default_from = 'O' then
2843 --
2844 hr_utility.set_location(l_proc, 110);
2845 --
2846 l_location_id := l_org_location_id;
2847 l_frequency := l_org_frequency;
2848 l_normal_hours := l_org_normal_hours;
2849 l_time_normal_finish := l_org_time_normal_finish;
2850 l_time_normal_start := l_org_time_normal_start;
2851 --
2852 elsif mmposrec.default_from = 'P' then
2853 --
2854 hr_utility.set_location(l_proc, 120);
2855 --
2856 l_location_id := l_old_pos_location_id;
2857 l_frequency := l_old_pos_frequency;
2858 l_normal_hours := l_old_pos_normal_hours;
2859 l_time_normal_finish := l_old_pos_time_normal_finish;
2860 l_time_normal_start := l_old_pos_time_normal_start;
2861 --
2862 end if;
2863 --
2864 -- Call the move position process to create the new position
2865 --
2866 if mmposrec.position_moved = 'N' then
2867 --
2868 hr_utility.set_location(l_proc, 130);
2869 --
2870 hr_mass_move_api.move_position
2871 (p_validate => FALSE
2872 ,p_batch_run_number => l_batch_run_number
2873 ,p_position_id => mmposrec.position_id
2874 ,p_job_id => mmposrec.target_job_id
2875 ,p_object_version_number => mmposrec.object_version_number
2876 ,p_date_effective => l_effective_date
2877 ,p_organization_id => l_new_organization_id
2878 ,p_deactivate_old_position => l_deactivate_boolean
2879 ,p_mass_move_id => p_mass_move_id
2880 ,p_time_normal_start => l_time_normal_start
2881 ,p_time_normal_finish => l_time_normal_finish
2882 ,p_normal_hours => l_normal_hours
2883 ,p_frequency => l_frequency
2884 ,p_location_id => l_location_id
2885 ,p_segment1 => mmposrec.segment1
2886 ,p_segment2 => mmposrec.segment2
2887 ,p_segment3 => mmposrec.segment3
2888 ,p_segment4 => mmposrec.segment4
2889 ,p_segment5 => mmposrec.segment5
2890 ,p_segment6 => mmposrec.segment6
2891 ,p_segment7 => mmposrec.segment7
2892 ,p_segment8 => mmposrec.segment8
2893 ,p_segment9 => mmposrec.segment9
2894 ,p_segment10 => mmposrec.segment10
2895 ,p_segment11 => mmposrec.segment11
2896 ,p_segment12 => mmposrec.segment12
2897 ,p_segment13 => mmposrec.segment13
2898 ,p_segment14 => mmposrec.segment14
2899 ,p_segment15 => mmposrec.segment15
2900 ,p_segment16 => mmposrec.segment16
2901 ,p_segment17 => mmposrec.segment17
2902 ,p_segment18 => mmposrec.segment18
2903 ,p_segment19 => mmposrec.segment19
2904 ,p_segment20 => mmposrec.segment20
2905 ,p_segment21 => mmposrec.segment21
2906 ,p_segment22 => mmposrec.segment22
2907 ,p_segment23 => mmposrec.segment23
2908 ,p_segment24 => mmposrec.segment24
2909 ,p_segment25 => mmposrec.segment25
2910 ,p_segment26 => mmposrec.segment26
2911 ,p_segment27 => mmposrec.segment27
2912 ,p_segment28 => mmposrec.segment28
2913 ,p_segment29 => mmposrec.segment29
2914 ,p_segment30 => mmposrec.segment30
2915 ,p_new_position_id => l_new_position_id --out
2916 ,p_new_job_id => l_new_job_id --out
2917 ,p_new_object_version_number => l_new_pos_ovn --out
2918 ,p_valid_grades_changed_warning => l_valid_grades_changed_warning --out
2919 ,p_pos_exists_warning => l_pos_exists_warning --out
2920 ,p_pos_date_range_warning => l_pos_date_range_warning --out
2921 ,p_pos_pending_close_warning => l_pos_pending_close_warning --out
2922 ,p_pos_jbe_not_moved_warning => l_pos_jbe_not_moved_warning --out
2923 ,p_pos_vac_not_moved_warning => l_pos_vac_not_moved_warning --out
2924 );
2925 --
2926 hr_utility.set_location(l_proc, 140);
2927 --
2928 if (l_new_position_id is not null) then
2929 --
2930 -- If it made it this far, update the position to 'moved'
2931 --
2932 update per_mm_positions
2933 set position_moved = 'Y',
2934 target_job_id = l_new_job_id,
2935 new_position_id = l_new_position_id
2936 where position_id = mmposrec.position_id
2937 and mass_move_id = p_mass_move_id;
2938 --
2939 end if;
2940 --
2941 hr_utility.set_location(l_proc, 150);
2942 --
2943 -- in the case of a reexecution, set the vals from the table.
2944 --
2945 else
2946 --
2947 hr_utility.set_location(l_proc, 160);
2948 --
2949 l_new_position_id := mmposrec.new_position_id;
2950 l_new_job_id := mmposrec.target_job_id;
2951 --
2952 end if; /* end if position_moved = 'N' */
2953 --
2954 -- Get the location and working condition information from the new
2955 -- position.
2956 --
2957 open csr_get_new_pos_defaults;
2958 fetch csr_get_new_pos_defaults into
2959 l_new_pos_time_normal_start,
2960 l_new_pos_time_normal_finish,
2961 l_new_pos_normal_hours,
2962 l_new_pos_frequency,
2963 l_new_pos_location_id;
2964 --
2965 close csr_get_new_pos_defaults;
2966 --
2967 hr_utility.set_location(l_proc, 170);
2968 --
2969
2970 if (l_new_position_id is not null) then -- fix for bug#10311425
2971
2972 move_assignments
2973 (p_mass_move_id => p_mass_move_id
2974 ,p_old_position_id => mmposrec.position_id
2975 ,p_new_position_id => l_new_position_id
2976 ,p_new_job_id => l_new_job_id
2977 ,p_effective_date => l_effective_date
2978 ,p_new_organization_id => l_new_organization_id
2979 ,p_pos_time_normal_start => l_new_pos_time_normal_start
2980 ,p_pos_time_normal_finish => l_new_pos_time_normal_finish
2981 ,p_pos_normal_hours => l_new_pos_normal_hours
2982 ,p_pos_frequency => l_new_pos_frequency
2983 ,p_pos_location_id => l_new_pos_location_id
2984 ,p_org_time_normal_start => l_org_time_normal_start
2985 ,p_org_time_normal_finish => l_org_time_normal_finish
2986 ,p_org_normal_hours => l_org_normal_hours
2987 ,p_org_frequency => l_org_frequency
2988 ,p_org_location_id => l_org_location_id
2989 ,p_legislation_code => l_legislation_code
2990 ,p_bg_time_normal_start => l_bg_time_normal_start
2991 ,p_bg_time_normal_finish => l_bg_time_normal_finish
2992 ,p_bg_normal_hours => l_bg_normal_hours
2993 ,p_bg_frequency => l_bg_frequency
2994 ,p_bg_location_id => l_bg_location_id
2995 ,p_batch_run_number => l_batch_run_number);
2996
2997 end if; -- fix for bug#10311425
2998
2999 --
3000 hr_utility.set_location(l_proc, 180);
3001 --
3002 -- Check for position warning messages
3003 --
3004 if l_pos_exists_warning = TRUE then
3005 --
3006 hr_utility.set_location(l_proc, 190);
3007 --
3008 ROLLBACK TO move_position;
3009 --
3010 hr_utility.set_message(801,'HR_51330_MMV_POS_EXISTS');
3011 --
3012 hr_batch_message_line_api.create_message_line
3013 (p_validate => FALSE
3014 ,p_batch_run_number => l_batch_run_number
3015 ,p_api_name => 'hr_mass_move_api.move_position'
3016 ,p_status => 'S'
3017 ,p_error_number => '51130'
3018 ,p_error_message => hr_utility.get_message
3019 ,p_extended_error_message => fnd_message.get
3020 ,p_source_row_information => l_position_name
3021 ,p_line_id => l_dummy_line_id);
3022 --
3023 end if;
3024 --
3025 if l_pos_date_range_warning = TRUE then
3026 --
3027 hr_utility.set_location(l_proc, 200);
3028 --
3029 ROLLBACK TO move_position;
3030 --
3031 hr_utility.set_message(801,'HR_51331_MMV_POS_INVALID_DATE');
3032 --
3033 hr_batch_message_line_api.create_message_line
3034 (p_validate => FALSE
3035 ,p_batch_run_number => l_batch_run_number
3036 ,p_api_name => 'hr_mass_move_api.move_position'
3037 ,p_status => 'S'
3038 ,p_error_number => '51331'
3039 ,p_error_message => hr_utility.get_message
3040 ,p_extended_error_message => fnd_message.get
3041 ,p_source_row_information => l_position_name
3042 ,p_line_id => l_dummy_line_id);
3043 --
3044 end if;
3045 --
3046 if l_pos_pending_close_warning = TRUE then
3047 --
3048 hr_utility.set_location(l_proc, 210);
3049 --
3050 hr_utility.set_message(801,'HR_51335_MMV_POS_CLOSED');
3051 --
3052 hr_batch_message_line_api.create_message_line
3053 (p_validate => FALSE
3054 ,p_batch_run_number => l_batch_run_number
3055 ,p_api_name => 'hr_mass_move_api.move_position'
3056 ,p_status => 'S'
3057 ,p_error_number => '51335'
3058 ,p_error_message => hr_utility.get_message
3059 ,p_extended_error_message => fnd_message.get
3060 ,p_source_row_information => l_position_name
3061 ,p_line_id => l_dummy_line_id);
3062 --
3063 end if;
3064 --
3065 if l_valid_grades_changed_warning = TRUE then
3066 --
3067 hr_utility.set_location(l_proc, 220);
3068 --
3069 hr_utility.set_message(801,'HR_51128_MMV_VGR_ALTERED');
3070 --
3071 hr_batch_message_line_api.create_message_line
3072 (p_validate => FALSE
3073 ,p_batch_run_number => l_batch_run_number
3074 ,p_api_name => 'hr_mass_move_api.move_position'
3075 ,p_status => 'S'
3076 ,p_error_number => '51128'
3077 ,p_error_message => hr_utility.get_message
3078 ,p_extended_error_message => fnd_message.get
3079 ,p_source_row_information => l_position_name
3080 ,p_line_id => l_dummy_line_id);
3081 --
3082 end if;
3083 --
3084 if l_pos_jbe_not_moved_warning = TRUE then
3085 --
3086 hr_utility.set_location(l_proc, 230);
3087 --
3088 hr_utility.set_message(801,'HR_51334_MMV_POS_JBE_NOT_MOVED');
3089 --
3090 hr_batch_message_line_api.create_message_line
3091 (p_validate => FALSE
3092 ,p_batch_run_number => l_batch_run_number
3093 ,p_api_name => 'hr_mass_move_api.move_position'
3094 ,p_status => 'S'
3095 ,p_error_number => '51334'
3096 ,p_error_message => hr_utility.get_message
3097 ,p_extended_error_message => fnd_message.get
3098 ,p_source_row_information => l_position_name
3099 ,p_line_id => l_dummy_line_id);
3100 --
3101 end if;
3102 --
3103 if l_pos_vac_not_moved_warning = TRUE then
3104 --
3105 hr_utility.set_location(l_proc, 240);
3106 --
3107 hr_utility.set_message(801,'HR_51333_MMV_POS_VAC_NOT_MOVED');
3108 --
3109 hr_batch_message_line_api.create_message_line
3110 (p_validate => FALSE
3111 ,p_batch_run_number => l_batch_run_number
3112 ,p_api_name => 'hr_mass_move_api.move_position'
3113 ,p_status => 'S'
3114 ,p_error_number => '51333'
3115 ,p_error_message => hr_utility.get_message
3116 ,p_extended_error_message => fnd_message.get
3117 ,p_source_row_information => l_position_name
3118 ,p_line_id => l_dummy_line_id);
3119 --
3120 end if;
3121 --
3122 end; --begin
3123 --
3124 end loop; /* end loop through positions in per_mm_positions */
3125 --
3126 hr_utility.set_location(l_proc, 250);
3127 --
3128 -- Call Customer-Specific stub to handle post-mass move validation
3129 --
3130 hr_mass_move_cus.post_mass_move
3131 (p_mass_move_id => p_mass_move_id);
3132 --
3133 -- If everything has completed successfully, update the massmove status
3134 --
3135 l_errors_exist := 'N';
3136 open csr_chk_errors;
3137 fetch csr_chk_errors into l_errors_exist;
3138 close csr_chk_errors;
3139 --
3140 hr_utility.set_location(l_proc, 260);
3141 --
3142 l_warnings_exist := 'N';
3143 open csr_chk_warnings;
3144 fetch csr_chk_warnings into l_warnings_exist;
3145 close csr_chk_warnings;
3146 --
3147 if l_errors_exist = 'Y' then
3148 --
3149 hr_utility.set_location(l_proc, 270);
3150 --
3151 rollback to massmove;
3152 --
3153 update per_mass_moves
3154 set batch_run_number = l_batch_run_number,
3155 status = 'ER'
3156 where mass_move_id = p_mass_move_id;
3157 --
3158 elsif l_warnings_exist = 'Y' then
3159 --
3160 hr_utility.set_location(l_proc, 280);
3161 --
3162 update per_mass_moves
3163 set batch_run_number = l_batch_run_number,
3164 status = 'WA'
3165 where mass_move_id = p_mass_move_id;
3166 --
3167 else
3168 --
3169 hr_utility.set_location(l_proc, 290);
3170 --
3171 update per_mass_moves
3172 set batch_run_number = l_batch_run_number,
3173 status = 'CO'
3174 where mass_move_id = p_mass_move_id;
3175 --
3176 end if;
3177 --
3178 begin
3179 --
3180 -- Start of API User Hook for the after hook of mass_move
3181 --
3182 hr_mass_move_bk1.mass_move_a
3183 (p_mass_move_id => p_mass_move_id
3184 ,p_business_group_id => l_business_group_id
3185 );
3186 exception
3187 when hr_api.cannot_find_prog_unit then
3188 hr_api.cannot_find_prog_unit_error
3189 (p_module_name => 'MASS_MOVE'
3190 ,p_hook_type => 'AP'
3191 );
3192 --
3193 -- End of API User Hook for the after hook of mass_move
3194 --
3195 end;
3196 --
3197 --
3198 -- When in validation only mode raise the Validate_Enabled exception
3199 --
3200 if p_validate then
3201 --
3202 raise hr_api.validate_enabled;
3203 --
3204 end if;
3205 --
3206 hr_utility.set_location(' Leaving:'||l_proc, 997);
3207 --
3208 exception
3209 --
3210 when hr_api.validate_enabled then
3211 --
3212 hr_utility.set_location(' Leaving:'||l_proc, 998);
3213 --
3214 -- As the Validate_Enabled exception has been raised
3215 -- we must rollback to the savepoint
3216 --
3217 ROLLBACK TO activate_mass_move;
3218 --
3219 when others then
3220 --
3221 hr_utility.set_location(' Leaving:'||l_proc, 999);
3222 --
3223 rollback to massmove;
3224 --
3225 hr_batch_message_line_api.create_message_line
3226 (p_validate => FALSE
3227 ,p_batch_run_number => l_batch_run_number
3228 ,p_api_name => 'hr_mass_move_api.mass_move'
3229 ,p_status => 'F'
3230 ,p_error_number => sqlcode
3231 ,p_error_message => sqlerrm
3232 ,p_extended_error_message => fnd_message.get
3233 ,p_source_row_information => l_position_name
3234 ,p_line_id => l_dummy_line_id);
3235 --
3236 update per_mass_moves
3237 set batch_run_number = l_batch_run_number,
3238 status = 'ER'
3239 where mass_move_id = p_mass_move_id;
3240 --
3241 end mass_move;
3242 --
3243 end hr_mass_move_api;