1 package body PER_MM_POSITIONS_PKG as
2 /* $Header: pemmv02t.pkb 115.4 99/10/18 20:38:09 porting shi $ */
3 --
4 --
5 procedure update_row
6 (p_select_position in varchar2,
7 p_default_from in varchar2,
8 p_deactivate_old_position in varchar2,
9 p_new_position_definition_id in number,
10 p_new_position_id in number,
11 p_target_job_id in number,
12 p_segment1 in varchar2,
13 p_segment2 in varchar2,
14 p_segment3 in varchar2,
15 p_segment4 in varchar2,
16 p_segment5 in varchar2,
17 p_segment6 in varchar2,
18 p_segment7 in varchar2,
19 p_segment8 in varchar2,
20 p_segment9 in varchar2,
21 p_segment10 in varchar2,
22 p_segment11 in varchar2,
23 p_segment12 in varchar2,
24 p_segment13 in varchar2,
25 p_segment14 in varchar2,
26 p_segment15 in varchar2,
27 p_segment16 in varchar2,
28 p_segment17 in varchar2,
29 p_segment18 in varchar2,
30 p_segment19 in varchar2,
31 p_segment20 in varchar2,
32 p_segment21 in varchar2,
33 p_segment22 in varchar2,
34 p_segment23 in varchar2,
35 p_segment24 in varchar2,
36 p_segment25 in varchar2,
37 p_segment26 in varchar2,
38 p_segment27 in varchar2,
39 p_segment28 in varchar2,
40 p_segment29 in varchar2,
41 p_segment30 in varchar2,
42 p_row_id in varchar2)
43
44 is
45 begin
46 update per_mm_positions
47 set select_position = p_select_position,
48 default_from = p_default_from,
49 deactivate_old_position = p_deactivate_old_position,
50 new_position_definition_id = p_new_position_definition_id,
51 new_position_id = p_new_position_id,
52 target_job_id = p_target_job_id,
53 segment1 = p_segment1,
54 segment2 = p_segment2,
55 segment3 = p_segment3,
56 segment4 = p_segment4,
57 segment5 = p_segment5,
58 segment6 = p_segment6,
59 segment7 = p_segment7,
60 segment8 = p_segment8,
61 segment9 = p_segment9,
62 segment10 = p_segment10,
63 segment11 = p_segment11,
64 segment12 = p_segment12,
65 segment13 = p_segment13,
66 segment14 = p_segment14,
67 segment15 = p_segment15,
68 segment16 = p_segment16,
69 segment17 = p_segment17,
70 segment18 = p_segment18,
71 segment19 = p_segment19,
72 segment20 = p_segment20,
73 segment21 = p_segment21,
74 segment22 = p_segment22,
75 segment23 = p_segment23,
76 segment24 = p_segment24,
77 segment25 = p_segment25,
78 segment26 = p_segment26,
79 segment27 = p_segment27,
80 segment28 = p_segment28,
81 segment29 = p_segment29,
82 segment30 = p_segment30
83 where rowid = p_row_id;
84 if (sql%notfound) then
85 raise no_data_found;
86 end if;
87
88 exception
89 when others then
90 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
91 hr_utility.set_message_token('PROCEDURE','per_mm_positions_pkg.update_rows');
92 hr_utility.set_message_token('STEP','1');
93 hr_utility.raise_error;
94 end update_row;
95 --
96 --
97 procedure load_rows
98 (p_mass_move_id in number,
99 p_business_group_id in number,
100 p_source_organization in varchar2,
101 p_session_date in date,
102 p_end_of_time in date,
103 p_position_name in varchar2,
104 p_job_name in varchar2,
105 p_attribute_category in varchar2,
106 p_attribute1 in varchar2,
107 p_attribute2 in varchar2,
108 p_attribute3 in varchar2,
109 p_attribute4 in varchar2,
110 p_attribute5 in varchar2,
111 p_attribute6 in varchar2,
112 p_attribute7 in varchar2,
113 p_attribute8 in varchar2,
114 p_attribute9 in varchar2,
115 p_attribute10 in varchar2,
116 p_attribute11 in varchar2,
117 p_attribute12 in varchar2,
118 p_attribute13 in varchar2,
119 p_attribute14 in varchar2,
120 p_attribute15 in varchar2,
121 p_attribute16 in varchar2,
122 p_attribute17 in varchar2,
123 p_attribute18 in varchar2,
124 p_attribute19 in varchar2,
125 p_attribute20 in varchar2)
126
127 is
128
129 l_dummy varchar2(1);
130
131 cursor c is
132 select 'x'
133 from
134 per_mm_positions mmpos
135 where mmpos.mass_move_id = p_mass_move_id;
136
137 begin
138 insert into per_mm_positions
139 (MASS_MOVE_ID,
140 POSITION_ID,
141 OBJECT_VERSION_NUMBER,
142 DEFAULT_FROM,
143 DEACTIVATE_OLD_POSITION,
144 SELECT_POSITION,
145 POSITION_MOVED)
146 select
147 p_mass_move_id,
148 pos.position_id,
149 pos.object_version_number,
150 'P',
151 'N',
152 'N',
153 'N'
154 from hr_positions pos,
155 per_organization_units org,
156 per_jobs job
157 where pos.job_id = job.job_id
158 and pos.organization_id = org.organization_id
159 and p_session_date between
160 pos.date_effective and
161 nvl(pos.date_end,p_end_of_time)
162 and pos.business_group_id = p_business_group_id
163 and org.name = p_source_organization
164 and job.name like nvl(p_job_name, job.name)
165 and pos.name like nvl(p_position_name, pos.name)
166 and ((p_attribute_category is not null and p_attribute_category = pos.attribute_category)
167 or
168 (p_attribute_category is null))
169 and ((p_attribute1 is not null and p_attribute1 = pos.attribute1)
170 or
171 (p_attribute1 is null))
172 and ((p_attribute2 is not null and p_attribute2 = pos.attribute2)
173 or
174 (p_attribute2 is null))
175 and ((p_attribute3 is not null and p_attribute3 = pos.attribute3)
176 or
177 (p_attribute3 is null))
178 and ((p_attribute4 is not null and p_attribute4 = pos.attribute4)
179 or
180 (p_attribute4 is null))
181 and ((p_attribute5 is not null and p_attribute5 = pos.attribute5)
182 or
183 (p_attribute5 is null))
184 and ((p_attribute6 is not null and p_attribute6 = pos.attribute6)
185 or
186 (p_attribute6 is null))
187 and ((p_attribute7 is not null and p_attribute7 = pos.attribute7)
188 or
189 (p_attribute7 is null))
190 and ((p_attribute8 is not null and p_attribute8 = pos.attribute8)
191 or
192 (p_attribute8 is null))
193 and ((p_attribute9 is not null and p_attribute9 = pos.attribute9)
194 or
195 (p_attribute9 is null))
196 and ((p_attribute10 is not null and p_attribute10 = pos.attribute10)
197 or
198 (p_attribute10 is null))
199 and ((p_attribute11 is not null and p_attribute11 = pos.attribute11)
200 or
201 (p_attribute11 is null))
202 and ((p_attribute12 is not null and p_attribute12 = pos.attribute12)
203 or
204 (p_attribute12 is null))
205 and ((p_attribute13 is not null and p_attribute13 = pos.attribute13)
206 or
207 (p_attribute13 is null))
208 and ((p_attribute14 is not null and p_attribute14 = pos.attribute14)
209 or
210 (p_attribute14 is null))
211 and ((p_attribute15 is not null and p_attribute15 = pos.attribute15)
212 or
213 (p_attribute15 is null))
214 and ((p_attribute16 is not null and p_attribute16 = pos.attribute16)
215 or
216 (p_attribute16 is null))
217 and ((p_attribute17 is not null and p_attribute17 = pos.attribute17)
218 or
219 (p_attribute17 is null))
220 and ((p_attribute18 is not null and p_attribute18 = pos.attribute18)
221 or
222 (p_attribute18 is null))
223 and ((p_attribute19 is not null and p_attribute19 = pos.attribute19)
224 or
225 (p_attribute19 is null))
226 and ((p_attribute20 is not null and p_attribute20 = pos.attribute20)
227 or
228 (p_attribute20 is null))
229 ;
230 -- Bug#885806. DBMS_OUTPUT.PUT_LINE calls were replaced with HR_UTILITY.TRACE calls
231 -- dbms_output.put_line(sqlcode);
232 -- dbms_output.put_line(sqlerrm);
233 hr_utility.trace(sqlcode);
234 hr_utility.trace(sqlerrm);
235 open c;
236 fetch c into l_dummy;
237 if (c%notfound) then
238 close c;
239 raise no_data_found;
240 end if;
241 close c;
242 -- Bug#885806. DBMS_OUTPUT.PUT_LINE calls were replaced with HR_UTILITY.TRACE calls
243 -- dbms_output.put_line(sqlcode);
244 -- dbms_output.put_line(sqlerrm);
245 hr_utility.trace(sqlcode);
246 hr_utility.trace(sqlerrm);
247 exception
248 when no_data_found then
249 hr_utility.set_message(801,'HR_51384_MMV_NO_POS_FOR_ORG');
250 hr_utility.raise_error;
251 when others then
252 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
253 hr_utility.set_message_token('PROCEDURE','per_mm_positions_pkg.load_rows');
254 hr_utility.set_message_token('STEP','1');
255 -- Bug#885806.
256 -- hr_utility.raise_error;
257 -- dbms_output.put_line(sqlcode);
258 -- dbms_output.put_line(sqlerrm);
259 raise_application_error(sqlcode,sqlerrm);
260 end load_rows;
261 --
262 --
263 procedure lock_row
264 (p_mass_move_id in number,
265 p_position_id in number,
266 p_select_position in varchar2,
267 p_default_from in varchar2,
268 p_deactivate_old_position in varchar2,
269 p_new_position_definition_id in number,
270 p_new_position_id in number,
271 p_target_job_id in number,
272 p_segment1 in varchar2,
273 p_segment2 in varchar2,
274 p_segment3 in varchar2,
275 p_segment4 in varchar2,
276 p_segment5 in varchar2,
277 p_segment6 in varchar2,
278 p_segment7 in varchar2,
279 p_segment8 in varchar2,
280 p_segment9 in varchar2,
281 p_segment10 in varchar2,
282 p_segment11 in varchar2,
283 p_segment12 in varchar2,
284 p_segment13 in varchar2,
285 p_segment14 in varchar2,
286 p_segment15 in varchar2,
287 p_segment16 in varchar2,
288 p_segment17 in varchar2,
289 p_segment18 in varchar2,
290 p_segment19 in varchar2,
291 p_segment20 in varchar2,
292 p_segment21 in varchar2,
293 p_segment22 in varchar2,
294 p_segment23 in varchar2,
295 p_segment24 in varchar2,
296 p_segment25 in varchar2,
297 p_segment26 in varchar2,
298 p_segment27 in varchar2,
299 p_segment28 in varchar2,
300 p_segment29 in varchar2,
301 p_segment30 in varchar2,
302 p_row_id in varchar2)
303
304 is
305 counter number;
306 cursor c is
307 select *
308 from per_mm_positions
309 where rowid = p_row_id
310 for update of select_position nowait;
311 recinfo c%rowtype;
312 begin
313 counter := 0;
314 loop
315 begin
316 counter := counter + 1;
317 open c;
318 fetch c into recinfo;
319 if (c%notfound) then
320 close c;
321 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
322 hr_utility.set_message_token('PROCEDURE','per_mm_positions_pkg.lock_rows');
323 hr_utility.set_message_token('STEP','1');
324 hr_utility.raise_error;
325 end if;
326 close c;
327 if (
328 (recinfo.mass_move_id = p_mass_move_id)
329 AND
330 (recinfo.position_id = p_position_id)
331 AND
332 (recinfo.default_from = p_default_from)
333 AND(
334 (recinfo.deactivate_old_position = p_deactivate_old_position)
335 OR ( (recinfo.deactivate_old_position is null)
336 AND (p_deactivate_old_position is null)))
337 AND(
338 (recinfo.select_position = p_select_position)
339 OR ( (recinfo.select_position is null)
340 AND (p_select_position is null)))
341 AND(
342 (recinfo.new_position_id = p_new_position_id)
343 OR ( (recinfo.new_position_id is null)
344 AND (p_new_position_id is null)))
345 AND(
346 (recinfo.new_position_definition_id = p_new_position_definition_id)
347 OR ( (recinfo.new_position_definition_id is null)
348 AND (p_new_position_definition_id is null)))
349 AND(
350 (recinfo.segment1 = p_segment1)
351 OR ( (recinfo.segment1 is null)
352 AND (p_segment1 is null)))
353 AND(
354 (recinfo.segment2 = p_segment2)
355 OR ( (recinfo.segment2 is null)
356 AND (p_segment2 is null)))
357 AND(
358 (recinfo.segment3 = p_segment3)
359 OR ( (recinfo.segment3 is null)
360 AND (p_segment3 is null)))
361 AND(
362 (recinfo.segment4 = p_segment4)
366 (recinfo.segment5 = p_segment5)
363 OR ( (recinfo.segment4 is null)
364 AND (p_segment4 is null)))
365 AND(
367 OR ( (recinfo.segment5 is null)
368 AND (p_segment5 is null)))
369 AND(
370 (recinfo.segment6 = p_segment6)
371 OR ( (recinfo.segment6 is null)
372 AND (p_segment6 is null)))
373 AND(
374 (recinfo.segment7 = p_segment7)
375 OR ( (recinfo.segment7 is null)
376 AND (p_segment7 is null)))
377 AND(
378 (recinfo.segment8 = p_segment8)
379 OR ( (recinfo.segment8 is null)
380 AND (p_segment8 is null)))
381 AND(
382 (recinfo.segment9 = p_segment9)
383 OR ( (recinfo.segment9 is null)
384 AND (p_segment9 is null)))
385 AND(
386 (recinfo.segment10 = p_segment10)
387 OR ( (recinfo.segment10 is null)
388 AND (p_segment10 is null)))
389 AND(
390 (recinfo.segment11 = p_segment11)
391 OR ( (recinfo.segment11 is null)
392 AND (p_segment11 is null)))
393 AND(
394 (recinfo.segment12 = p_segment12)
395 OR ( (recinfo.segment12 is null)
396 AND (p_segment12 is null)))
397 AND(
398 (recinfo.segment13 = p_segment13)
399 OR ( (recinfo.segment13 is null)
400 AND (p_segment13 is null)))
401 AND(
402 (recinfo.segment14 = p_segment14)
403 OR ( (recinfo.segment14 is null)
404 AND (p_segment14 is null)))
405 AND(
406 (recinfo.segment15 = p_segment15)
407 OR ( (recinfo.segment15 is null)
408 AND (p_segment15 is null)))
409 AND(
410 (recinfo.segment16 = p_segment16)
411 OR ( (recinfo.segment16 is null)
412 AND (p_segment16 is null)))
413 AND(
414 (recinfo.segment17 = p_segment17)
415 OR ( (recinfo.segment17 is null)
416 AND (p_segment17 is null)))
417 AND(
418 (recinfo.segment18 = p_segment18)
419 OR ( (recinfo.segment18 is null)
420 AND (p_segment18 is null)))
421 AND(
422 (recinfo.segment19 = p_segment19)
423 OR ( (recinfo.segment19 is null)
424 AND (p_segment19 is null)))
425 AND(
426 (recinfo.segment20 = p_segment20)
427 OR ( (recinfo.segment20 is null)
428 AND (p_segment20 is null)))
429 AND(
430 (recinfo.segment21 = p_segment21)
431 OR ( (recinfo.segment21 is null)
432 AND (p_segment21 is null)))
433 AND(
434 (recinfo.segment22 = p_segment22)
435 OR ( (recinfo.segment22 is null)
436 AND (p_segment22 is null)))
437 AND(
438 (recinfo.segment23 = p_segment23)
439 OR ( (recinfo.segment23 is null)
440 AND (p_segment23 is null)))
441 AND(
442 (recinfo.segment24 = p_segment24)
443 OR ( (recinfo.segment24 is null)
444 AND (p_segment24 is null)))
445 AND(
446 (recinfo.segment25 = p_segment25)
447 OR ( (recinfo.segment25 is null)
448 AND (p_segment25 is null)))
449 AND(
450 (recinfo.segment26 = p_segment26)
451 OR ( (recinfo.segment26 is null)
452 AND (p_segment26 is null)))
453 AND(
454 (recinfo.segment27 = p_segment27)
455 OR ( (recinfo.segment27 is null)
456 AND (p_segment27 is null)))
457 AND(
458 (recinfo.segment28 = p_segment28)
459 OR ( (recinfo.segment28 is null)
460 AND (p_segment28 is null)))
461 AND(
462 (recinfo.segment29 = p_segment29)
463 OR ( (recinfo.segment29 is null)
464 AND (p_segment29 is null)))
465 AND(
466 (recinfo.segment30 = p_segment30)
467 OR ( (recinfo.segment30 is null)
468 AND (p_segment30 is null)))
469 ) then
470 return;
471 else
472 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
473 hr_utility.set_message_token('PROCEDURE','per_mm_positions_pkg.lock_rows');
474 hr_utility.set_message_token('STEP','2');
475 hr_utility.raise_error;
476 end if;
477 exception
478 when app_exceptions.record_lock_exception then
479 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
480 hr_utility.set_message_token('PROCEDURE','per_mm_positions_pkg.lock_rows');
481 hr_utility.set_message_token('STEP','3');
482 hr_utility.raise_error;
483 end;
484 end loop;
485 end lock_row ;
486 --
487 --
488 procedure chk_org
489 (p_new_organization_id in number,
490 p_new_position_definition_id in number)
491
492 is
493
494 l_dummy number(15);
495
496 cursor c is
497 select pos.organization_id
498 from hr_positions pos
499 where p_new_position_definition_id = pos.position_definition_id
500 and pos.organization_id <> p_new_organization_id;
501
502 begin
503 open c;
504 fetch c into l_dummy;
505 if (c%found) then
506 close c;
507 hr_utility.set_message(801,'HR_51330_MMV_POS_EXISTS');
508 hr_utility.raise_error;
509 end if;
510 close c;
511
512 end chk_org;
513 --
514 --
515 procedure get_job
516 (p_new_position_definition_id in number,
517 p_organization_id out number,
518 p_new_position_id out number,
519 p_target_job_name out varchar2,
520 p_target_job_id out number,
521 p_target_job_definition_id out number)
522
523 is
524
525 cursor c is
526 select pos.organization_id,
527 pos.position_id,
528 job.name,
529 job.job_id,
530 job.job_definition_id
531 from hr_positions pos,
532 per_jobs job
533 where p_new_position_definition_id = pos.position_definition_id
534 and pos.job_id = job.job_id;
535
536 l_organization_id number(15);
537 l_new_position_id number(15);
538 l_target_job_name varchar2(240);
539 l_target_job_id number(15);
540 l_target_job_definition_id number(15);
541
542 begin
543 open c;
544 fetch c into l_organization_id,
545 l_new_position_id ,
546 l_target_job_name,
547 l_target_job_id,
548 l_target_job_definition_id;
549 if (c%found) then
550 close c;
551 p_organization_id := l_organization_id;
552 p_new_position_id := l_new_position_id;
553 p_target_job_name := l_target_job_name;
554 p_target_job_id := l_target_job_id;
555 p_target_job_definition_id := l_target_job_definition_id;
556 else
557 close c;
558 p_organization_id := null;
559 p_new_position_id := null;
560 p_target_job_name := null;
561 p_target_job_id := null;
562 p_target_job_definition_id := null;
563 end if;
564
565 end get_job;
566 --
567 procedure get_target_job
568 (p_new_job_id in number,
569 p_effective_date in date,
570 p_target_job_name out varchar2,
571 p_target_job_definition_id out number)
572
573 is
574
575 cursor csr_job is
576 select job.name,
577 job.job_definition_id
578 from per_jobs job
579 where p_new_job_id = job.job_id
580 and p_effective_date between job.date_from
581 and nvl(job.date_to, p_effective_date);
582
583
584 l_target_job_name varchar2(240);
585 l_target_job_definition_id number(15);
586
587 begin
588 open csr_job;
589 fetch csr_job into l_target_job_name,
590 l_target_job_definition_id;
591
592 if (csr_job%found) then
593 close csr_job;
594 p_target_job_name := l_target_job_name;
595 p_target_job_definition_id := l_target_job_definition_id;
596 else
597 close csr_job;
598 p_target_job_name := null;
599 p_target_job_definition_id := null;
600 raise no_data_found;
601 end if;
602
603 Exception
604 When NO_DATA_FOUND then
605 hr_utility.set_message(801,'HR_51358_POS_JOB_INVALID_DATE');
606 hr_utility.raise_error;
607
608 end get_target_job;
609 --
610 --
611 end PER_MM_POSITIONS_PKG;
612
613
[Home] [Help]