[Home] [Help]
PACKAGE BODY: APPS.PER_POSITIONS_PKG
Source
1 PACKAGE BODY PER_POSITIONS_PKG as
2 /* $Header: pepos01t.pkb 120.0 2005/05/31 14:51:24 appldev noship $ */
3
4 function exists_in_hierarchy(X_Pos_Structure_Version_Id NUMBER,
5 X_Position_Id NUMBER) return VARCHAR2 IS
6 -- Local Variable.
7 l_exists VARCHAR2(1) := 'N';
8 --
9 -- return Y if a particular position exists in a hierarchy version
10 -- or if any elements exist in a hierarchy version
11 -- else return N.
12 --
13 begin
14 select 'Y'
15 into l_exists
16 from sys.dual
17 where exists (select null
18 from per_pos_structure_elements pse
19 where pse.pos_structure_VERSION_id = X_Pos_Structure_Version_Id
20 and ((pse.subordinate_position_id = X_Position_Id
21 or pse.parent_position_id = X_Position_Id)
22 or X_Position_Id is null)
23 );
24 --
25 return l_exists;
26 --
27 exception
28 when no_data_found then
29 return l_exists;
30 end exists_in_hierarchy;
31
32 PROCEDURE Check_Unique_Row(p_Rowid varchar2,
33 p_Position_Id number) is
34 l_dummy varchar2(1);
35 l_bool boolean;
36 cursor csr_p is
37 SELECT '1'
38 FROM PER_ALL_POSITIONS PST
39 WHERE (PST.ROWID <> p_Rowid
40 OR p_Rowid IS NULL)
41 AND PST.POSITION_ID = p_Position_Id;
42 begin
43 open csr_p;
44 fetch csr_p into l_dummy;
45 l_bool := csr_p%found;
46 close csr_p;
47 if l_bool then
48 hr_utility.set_message(801,'HR_6012_ROW_INSERTED');
49 hr_utility.raise_error;
50 end if;
51
52 end Check_Unique_Row;
53
54 PROCEDURE Check_Descriptor(p_Rowid varchar2,
55 p_Position_Definition_Id number,
56 p_Business_Group_Id number) is
57 l_dummy varchar2(1);
58 l_bool boolean;
59 cursor csr_pd is
60 SELECT '1'
61 FROM PER_ALL_POSITIONS PST
62 WHERE (PST.ROWID <> p_Rowid
63 OR p_Rowid IS NULL)
64 AND PST.POSITION_DEFINITION_ID = p_Position_Definition_Id
65 AND PST.business_group_id + 0 = p_Business_Group_Id;
66 begin
67 open csr_pd;
68 fetch csr_pd into l_dummy;
69 l_bool := csr_pd%found;
70 close csr_pd;
71 if l_bool then
72 hr_utility.set_message(801,'PER_7415_POS_EXISTS');
73 hr_utility.raise_error;
74 end if;
75
76 end Check_Descriptor;
77
78 PROCEDURE pre_delete_checks(p_rowid varchar2,
79 p_position_id number,
80 p_business_group_id number,
81 p_hr_ins varchar2,
82 p_po_ins varchar2,
83 p_delete_row out nocopy boolean
84 ) is
85 l_exists varchar2(1);
86 l_pos_structure_element_id number;
87 l_sql_text VARCHAR2(2000);
88 l_oci_out VARCHAR2(1);
89 l_sql_cursor NUMBER;
90 l_rows_fetched NUMBER;
91 begin
92 p_delete_row := false;
93 begin
94 select '1'
95 into l_exists
96 from sys.dual
97 where exists (select null
98 from per_ALL_assignments_f a
99 where a.position_id = p_position_id);
100 exception when no_data_found then
101 null;
102 end;
103 if l_exists = '1' then
104 hr_utility.set_message(801,'PER_7417_POS_ASSIGNMENT');
105 hr_utility.raise_error;
106 end if;
107
108 l_exists := NULL;
109
110 if p_hr_ins = 'Y' then
111 begin
112 select '1'
113 into l_exists
114 from sys.dual
115 where exists (SELECT NULL
116 FROM PAY_ELEMENT_LINKS_F EL
117 WHERE EL.POSITION_ID = p_position_id);
118 exception when no_data_found then
119 null;
120 end;
121
122 if l_exists = '1' then
123 hr_utility.set_message(801,'PER_7863_DEL_POS_LINK');
124 hr_utility.set_message_token('FORM','PERWSDPO');
125 hr_utility.set_message_token('BLOCK','PST1');
126 hr_utility.set_message_token('TRIGGER','on-delete');
127 hr_utility.set_message_token('STEP','4');
128 hr_utility.raise_error;
129 end if;
130
131 l_exists := NULL;
132
133 begin
134 select '1'
135 into l_exists
136 from sys.dual
137 where exists(SELECT NULL
138 from PER_BUDGET_ELEMENTS BE
139 where BE.POSITION_ID = p_position_id);
140 exception when no_data_found then
141 null;
142 end;
143 if l_exists = '1' then
144 hr_utility.set_message(801,'PER_7862_DEL_POS_BUD');
145 hr_utility.set_message_token('FORM','PERWSDPO');
146 hr_utility.set_message_token('BLOCK','PST1');
147 hr_utility.set_message_token('TRIGGER','on-delete');
148 hr_utility.set_message_token('STEP','6');
149 hr_utility.raise_error;
150 end if;
151 l_exists := NULL;
152 begin
153 select '1'
154 into l_exists
155 from sys.dual
156 where exists(SELECT NULL
157 from PER_VACANCIES VAC
158 where VAC.POSITION_ID = p_position_id);
159 exception when no_data_found then
160 null;
161 end;
162 if l_exists = '1' then
163 hr_utility.set_message(801,'PER_7861_DEL_POS_REC_ACT');
164 hr_utility.set_message_token('FORM','PERWSDPO');
165 hr_utility.set_message_token('BLOCK','PST1');
166 hr_utility.set_message_token('STEP','8');
167 hr_utility.raise_error;
168 end if;
169
170 begin
171 select e.pos_structure_element_id
172 into l_pos_structure_element_id
173 from per_pos_structure_elements e
174 where e.parent_position_id = p_position_id
175 and not exists (
176 select null
177 from per_pos_structure_elements e2
178 where e2.subordinate_position_id = p_position_id)
179 and 1 = (
180 select count(e3.pos_structure_element_id)
181 from per_pos_structure_elements e3
182 where e3.parent_position_id = p_position_id);
183 exception when no_data_found then
184 null;
185 end;
186
187 l_exists := NULL;
188
189 if l_pos_structure_element_id is null then
190 begin
191 select '1'
192 into l_exists
193 from sys.dual
194 where exists(SELECT NULL
195 FROM PER_POS_STRUCTURE_ELEMENTS PSE
196 WHERE PSE.PARENT_POSITION_ID = p_position_id
197 OR PSE.SUBORDINATE_POSITION_ID = p_position_id) ;
198 exception when no_data_found then
199 null;
200 end;
201
202 if l_exists = '1' then
203 hr_utility.set_message(801,'PER_7416_POS_IN_POS_HIER');
204 hr_utility.raise_error;
205 end if;
206 end if;
207
208 l_exists := NULL;
209
210 begin
211 select '1'
212 into l_exists
213 from sys.dual
214 where exists(SELECT NULL
215 FROM PER_VALID_GRADES VG1
216 WHERE business_group_id + 0 = p_business_group_id
217 AND VG1.POSITION_ID = p_position_id);
218 exception when no_data_found then
219 null;
220 end;
221
222 if l_exists = '1' then
223 hr_utility.set_message(801,'PER_7865_DEF_POS_DEL_GRADE');
224 hr_utility.raise_error;
225 end if;
226
227 l_exists := NULL;
228
229 begin
230 select '1'
231 into l_exists
232 from sys.dual
233 where exists(select null
234 from per_job_requirements jre1
235 where jre1.position_id = p_position_id);
236 exception when no_data_found then
237 null;
238 end;
239
240 if l_exists = '1' then
241 hr_utility.set_message(801,'PER_7866_DEF_POS_DEL_REQ');
242 hr_utility.raise_error;
243 end if;
244
245
246 l_exists := NULL;
247
248 begin
249 select '1'
250 into l_exists
251 from sys.dual
252 where exists(select null
253 from per_job_evaluations jev1
254 where jev1.position_id = p_position_id);
255 exception when no_data_found then
256 null;
257 end;
258
259 if l_exists = '1' then
260 hr_utility.set_message(801,'PER_7867_DEF_POS_DEL_EVAL');
261 hr_utility.raise_error;
262 end if;
263
264 l_exists := NULL;
265
266 begin
267 select '1'
268 into l_exists
269 from sys.dual
270 where exists(select null
271 from per_positions
272 where successor_position_id = p_position_id);
273 exception when no_data_found then
274 null;
275 end;
276
277 if l_exists = '1' then
278 hr_utility.set_message(801,'PER_7996_POS_SUCCESSOR_REF');
279 hr_utility.raise_error;
280 end if;
281
282 l_exists := NULL;
283
284 begin
285 select '1'
286 into l_exists
287 from sys.dual
288 where exists(select null
289 from per_positions
290 where relief_position_id = p_position_id);
291 exception when no_data_found then
292 null;
293 end;
294
295 if l_exists = '1' then
296 hr_utility.set_message(801,'PER_7997_POS_RELIEF_REF');
297 hr_utility.raise_error;
298 end if;
299
300 l_exists := NULL;
301
302 begin
303 select '1'
304 into l_exists
305 from sys.dual
306 where exists(select null
307 from per_mm_positions
308 where new_position_id = p_position_id);
309 exception when no_data_found then
310 null;
311 end;
312
313 if l_exists = '1' then
314 hr_utility.set_message(800,'HR_52776_NOT_DEL_MM_POSITIONS');
315 hr_utility.raise_error;
316 end if;
317
318 end if;
319
320 --
321 -- is po installed?
322 --
323 if p_po_ins = 'Y' then
324 begin
325 l_sql_text := 'select null '
326 ||'from sys.dual '
327 ||'where exists( select null '
328 ||' from po_system_parameters '
329 ||' where security_position_structure_id = '
330 ||to_char(p_position_id)
331 ||' ) '
332 ||'or exists( select null '
333 ||' from po_employee_hierarchies '
334 ||' where employee_position_id = '
335 ||to_char(p_position_id)
336 ||'or superior_position_id = '
337 ||to_char(p_position_id)
338 ||' ) ';
339 --
340 -- Open Cursor for Processing Sql statment.
341 --
342 l_sql_cursor := dbms_sql.open_cursor;
343 --
344 -- Parse SQL statement.
345 --
346 dbms_sql.parse(l_sql_cursor, l_sql_text, dbms_sql.v7);
347 --
348 -- Map the local variables to each returned Column
349 --
350 dbms_sql.define_column(l_sql_cursor, 1,l_oci_out,1);
351 --
352 -- Execute the SQL statement.
353 --
354 l_rows_fetched := dbms_sql.execute(l_sql_cursor);
355 --
356 if (dbms_sql.fetch_rows(l_sql_cursor) > 0)
357 then
358 fnd_message.set_name('PER','HR_6048_PO_POS_DEL_POS_CONT');
359 fnd_message.raise_error;
360 end if;
361 --
362 -- Close cursor used for processing SQL statement.
363 --
364 dbms_sql.close_cursor(l_sql_cursor);
365 end;
366 end if;
367 --
368 -- Ref Int check for OTA.
369 --
370 per_ota_predel_validation.ota_predel_pos_validation(p_position_id);
371 --
372 p_delete_row := true;
373 end pre_delete_checks;
374
375 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
376 X_Position_Id IN OUT NOCOPY NUMBER,
377 X_Business_Group_Id NUMBER,
378 X_Job_Id NUMBER,
379 X_Organization_Id NUMBER,
380 X_Successor_Position_Id NUMBER,
381 X_Relief_Position_Id NUMBER,
382 X_Location_Id NUMBER,
383 X_Position_Definition_Id NUMBER,
384 X_Date_Effective DATE,
385 X_Comments VARCHAR2,
386 X_Date_End DATE,
387 X_Frequency VARCHAR2,
388 X_Name VARCHAR2,
389 X_Probation_Period NUMBER,
390 X_Probation_Period_Units VARCHAR2,
391 X_Replacement_Required_Flag VARCHAR2,
392 X_Time_Normal_Finish VARCHAR2,
393 X_Time_Normal_Start VARCHAR2,
394 X_Working_Hours NUMBER,
395 X_Status VARCHAR2,
396 X_Attribute_Category VARCHAR2,
397 X_Attribute1 VARCHAR2,
398 X_Attribute2 VARCHAR2,
399 X_Attribute3 VARCHAR2,
400 X_Attribute4 VARCHAR2,
401 X_Attribute5 VARCHAR2,
402 X_Attribute6 VARCHAR2,
403 X_Attribute7 VARCHAR2,
404 X_Attribute8 VARCHAR2,
405 X_Attribute9 VARCHAR2,
406 X_Attribute10 VARCHAR2,
407 X_Attribute11 VARCHAR2,
408 X_Attribute12 VARCHAR2,
409 X_Attribute13 VARCHAR2,
410 X_Attribute14 VARCHAR2,
411 X_Attribute15 VARCHAR2,
412 X_Attribute16 VARCHAR2,
413 X_Attribute17 VARCHAR2,
414 X_Attribute18 VARCHAR2,
415 X_Attribute19 VARCHAR2,
416 X_Attribute20 VARCHAR2,
417 X_View_All_Psts VARCHAR2,
418 X_Security_Profile_id NUMBER
419 ) IS
420 CURSOR C IS SELECT rowid FROM PER_ALL_POSITIONS
421
422 WHERE position_id = X_Position_Id;
423
424
425
426
427
428 CURSOR C2 IS SELECT per_positions_s.nextval FROM sys.dual;
429 BEGIN
430 null;
431 /*
432 if (X_Position_Id is NULL) then
433 OPEN C2;
434 FETCH C2 INTO X_Position_Id;
435 CLOSE C2;
436 end if;
437
438 Check_Unique_Row(X_Rowid,X_position_id);
439 Check_Descriptor(X_Rowid,X_position_definition_id,x_business_group_id);
440
441 INSERT INTO PER_POSITIONS(
442 position_id,
443 business_group_id,
444 job_id,
445 organization_id,
446 successor_position_id,
447 relief_position_id,
448 location_id,
449 position_definition_id,
450 date_effective,
451 comments,
452 date_end,
453 frequency,
454 name,
455 probation_period,
456 probation_period_units,
457 replacement_required_flag,
458 time_normal_finish,
459 time_normal_start,
460 working_hours,
461 status,
462 attribute_category,
463 attribute1,
464 attribute2,
465 attribute3,
466 attribute4,
467 attribute5,
468 attribute6,
469 attribute7,
470 attribute8,
471 attribute9,
472 attribute10,
473 attribute11,
474 attribute12,
475 attribute13,
476 attribute14,
477 attribute15,
478 attribute16,
479 attribute17,
480 attribute18,
481 attribute19,
482 attribute20
483 ) VALUES (
484 X_Position_Id,
485 X_Business_Group_Id,
486 X_Job_Id,
487 X_Organization_Id,
488 X_Successor_Position_Id,
489 X_Relief_Position_Id,
490 X_Location_Id,
491 X_Position_Definition_Id,
492 X_Date_Effective,
493 X_Comments,
494 X_Date_End,
495 X_Frequency,
496 X_Name,
497 X_Probation_Period,
498 X_Probation_Period_Units,
499 X_Replacement_Required_Flag,
500 X_Time_Normal_Finish,
501 X_Time_Normal_Start,
502 X_Working_Hours,
503 X_Status,
504 X_Attribute_Category,
505 X_Attribute1,
506 X_Attribute2,
507 X_Attribute3,
508 X_Attribute4,
509 X_Attribute5,
510 X_Attribute6,
511 X_Attribute7,
512 X_Attribute8,
513 X_Attribute9,
514 X_Attribute10,
515 X_Attribute11,
516 X_Attribute12,
517 X_Attribute13,
518 X_Attribute14,
519 X_Attribute15,
520 X_Attribute16,
521 X_Attribute17,
522 X_Attribute18,
523 X_Attribute19,
524 X_Attribute20
525 );
526
527 OPEN C;
528 FETCH C INTO X_Rowid;
529 if (C%NOTFOUND) then
530 CLOSE C;
531 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
532 hr_utility.set_message_token('PROCEDURE','Insert_row');
533 hr_utility.set_message_token('STEP','1');
534 hr_utility.raise_error;
535 end if;
536 CLOSE C;
537
538 if X_View_All_Psts <> 'Y' then
539
540 hr_security.add_position(X_Position_Id,
541 X_Security_Profile_id);
542 end if;
543 */
544 END Insert_Row;
545
546 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
547 X_Position_Id NUMBER,
548 X_Business_Group_Id NUMBER,
549 X_Job_Id NUMBER,
550 X_Organization_Id NUMBER,
551 X_Successor_Position_Id NUMBER,
552 X_Relief_Position_Id NUMBER,
553 X_Location_Id NUMBER,
554 X_Position_Definition_Id NUMBER,
555 X_Date_Effective DATE,
556 X_Comments VARCHAR2,
557 X_Date_End DATE,
558 X_Frequency VARCHAR2,
559 X_Name VARCHAR2,
560 X_Probation_Period NUMBER,
561 X_Probation_Period_Units VARCHAR2,
562 X_Replacement_Required_Flag VARCHAR2,
563 X_Time_Normal_Finish VARCHAR2,
564 X_Time_Normal_Start VARCHAR2,
565 X_Working_Hours NUMBER,
566 X_Attribute_Category VARCHAR2,
567 X_Attribute1 VARCHAR2,
568 X_Attribute2 VARCHAR2,
569 X_Attribute3 VARCHAR2,
570 X_Attribute4 VARCHAR2,
571 X_Attribute5 VARCHAR2,
572 X_Attribute6 VARCHAR2,
573 X_Attribute7 VARCHAR2,
574 X_Attribute8 VARCHAR2,
575 X_Attribute9 VARCHAR2,
576 X_Attribute10 VARCHAR2,
577 X_Attribute11 VARCHAR2,
578 X_Attribute12 VARCHAR2,
579 X_Attribute13 VARCHAR2,
580 X_Attribute14 VARCHAR2,
581 X_Attribute15 VARCHAR2,
582 X_Attribute16 VARCHAR2,
583 X_Attribute17 VARCHAR2,
584 X_Attribute18 VARCHAR2,
585 X_Attribute19 VARCHAR2,
586 X_Attribute20 VARCHAR2,
587 X_Status VARCHAR2
588 ) IS
589 -- 21st May 1997 Sue Grant bug no 474585: This used to do a select * from per_positions but it kept
590 -- giving value error or saying something had changed when the comments field was null, it worked fine
591 -- when the comments field had something in it! This seems to be another long problem so I have commented
592 -- out the retrival of the comments (which is a long) for the moment !!
593 -- This means that if somebody changes just the coments and then we use this procedure to try and lock the record
594 -- we will not notice a change has been made and will overwrite the changes - This is the best solution we could
595 -- come up with at the moment!
596 CURSOR C IS
597 SELECT Position_id
598 ,Business_Group_Id
599 ,Job_Id
600 ,Organization_Id
601 ,Successor_Position_Id
602 ,Relief_Position_Id
603 ,Location_Id
604 ,Position_Definition_Id
605 ,Date_Effective
606 -- ,Comments
607 ,Date_End
608 ,Frequency
609 ,Name
610 ,Probation_Period
611 ,Probation_Period_Units
612 ,Replacement_Required_Flag
613 ,Time_Normal_Finish
614 ,Time_Normal_Start
615 ,Working_Hours
616 ,Attribute_Category
617 ,Attribute1
618 ,Attribute2
619 ,Attribute3
620 ,Attribute4
621 ,Attribute5
622 ,Attribute6
623 ,Attribute7
624 ,Attribute8
625 ,Attribute9
626 ,Attribute10
627 ,Attribute11
628 ,Attribute12
629 ,Attribute13
630 ,Attribute14
631 ,Attribute15
632 ,Attribute16
633 ,Attribute17
634 ,Attribute18
635 ,Attribute19
636 ,Attribute20
637 ,Status
638 FROM PER_POSITIONS
639 WHERE rowid = chartorowid(X_Rowid)
640 FOR UPDATE of Position_Id NOWAIT;
641 Recinfo C%ROWTYPE;
642
643 BEGIN
644 null;
645 /*
646 OPEN C;
647 FETCH C INTO Recinfo;
648 if (C%NOTFOUND) then
649 CLOSE C;
650 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
651 hr_utility.set_message_token('PROCEDURE','Lock_row');
652 hr_utility.set_message_token('STEP','1');
653 hr_utility.raise_error;
654 end if;
655 CLOSE C;
656 --
657 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
658 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
659 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
660 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
661 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
662 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
663 --Recinfo.comments := rtrim(Recinfo.comments);
664 Recinfo.frequency := rtrim(Recinfo.frequency);
665 Recinfo.name := rtrim(Recinfo.name);
666 Recinfo.probation_period_units := rtrim(Recinfo.probation_period_units);
667 Recinfo.replacement_required_flag := rtrim(Recinfo.replacement_required_flag);
668 Recinfo.time_normal_finish := rtrim(Recinfo.time_normal_finish);
669 Recinfo.time_normal_start := rtrim(Recinfo.time_normal_start);
670 Recinfo.status := rtrim(Recinfo.status);
671 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
672 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
673 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
674 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
675 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
676 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
677 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
678 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
679 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
680 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
681 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
682 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
683 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
684 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
685 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
686
687 --
688 if (
689 ( (Recinfo.position_id = X_Position_Id)
690 OR ( (Recinfo.position_id IS NULL)
691 AND (X_Position_Id IS NULL)))
692 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
693 OR ( (Recinfo.business_group_id IS NULL)
694 AND (X_Business_Group_Id IS NULL)))
695 AND ( (Recinfo.job_id = X_Job_Id)
696 OR ( (Recinfo.job_id IS NULL)
697 AND (X_Job_Id IS NULL)))
698 AND ( (Recinfo.organization_id = X_Organization_Id)
699 OR ( (Recinfo.organization_id IS NULL)
700 AND (X_Organization_Id IS NULL)))
701 AND ( (Recinfo.successor_position_id = X_Successor_Position_Id)
702 OR ( (Recinfo.successor_position_id IS NULL)
703 AND (X_Successor_Position_Id IS NULL)))
704 AND ( (Recinfo.relief_position_id = X_Relief_Position_Id)
705 OR ( (Recinfo.relief_position_id IS NULL)
706 AND (X_Relief_Position_Id IS NULL)))
707 AND ( (Recinfo.location_id = X_Location_Id)
708 OR ( (Recinfo.location_id IS NULL)
709 AND (X_Location_Id IS NULL)))
710 AND ( (Recinfo.position_definition_id = X_Position_Definition_Id)
711 OR ( (Recinfo.position_definition_id IS NULL)
712 AND (X_Position_Definition_Id IS NULL)))
713 AND ( (Recinfo.date_effective = X_Date_Effective)
714 OR ( (Recinfo.date_effective IS NULL)
715 AND (X_Date_Effective IS NULL)))
716 -- AND ( (Recinfo.comments = X_Comments)
717 -- OR ( (Recinfo.comments IS NULL)
718 -- AND (X_Comments IS NULL)))
719 AND ( (Recinfo.date_end = X_Date_End)
720 OR ( (Recinfo.date_end IS NULL)
721 AND (X_Date_End IS NULL)))
722 AND ( (Recinfo.frequency = X_Frequency)
723 OR ( (Recinfo.frequency IS NULL)
724 AND (X_Frequency IS NULL)))
725 AND ( (Recinfo.name = X_Name)
726 OR ( (Recinfo.name IS NULL)
727 AND (X_Name IS NULL)))
728 AND ( (Recinfo.probation_period = X_Probation_Period)
729 OR ( (Recinfo.probation_period IS NULL)
730 AND (X_Probation_Period IS NULL)))
731 AND ( (Recinfo.probation_period_units = X_Probation_Period_Units)
732 OR ( (Recinfo.probation_period_units IS NULL)
733 AND (X_Probation_Period_Units IS NULL)))
734 AND ( (Recinfo.replacement_required_flag = X_Replacement_Required_Flag)
735 OR ( (Recinfo.replacement_required_flag IS NULL)
736 AND (X_Replacement_Required_Flag IS NULL)))
737 AND ( (Recinfo.time_normal_finish = X_Time_Normal_Finish)
738 OR ( (Recinfo.time_normal_finish IS NULL)
739 AND (X_Time_Normal_Finish IS NULL)))
740 AND ( (Recinfo.time_normal_start = X_Time_Normal_Start)
741 OR ( (Recinfo.time_normal_start IS NULL)
742 AND (X_Time_Normal_Start IS NULL)))
743 AND ( (Recinfo.working_hours = X_Working_Hours)
744 OR ( (Recinfo.working_hours IS NULL)
745 AND (X_Working_Hours IS NULL)))
746 AND ( (Recinfo.attribute_category = X_Attribute_Category)
747 OR ( (Recinfo.attribute_category IS NULL)
748 AND (X_Attribute_Category IS NULL)))
749 AND ( (Recinfo.attribute1 = X_Attribute1)
750 OR ( (Recinfo.attribute1 IS NULL)
751 AND (X_Attribute1 IS NULL)))
752 AND ( (Recinfo.attribute2 = X_Attribute2)
753 OR ( (Recinfo.attribute2 IS NULL)
754 AND (X_Attribute2 IS NULL)))
755 AND ( (Recinfo.attribute3 = X_Attribute3)
756 OR ( (Recinfo.attribute3 IS NULL)
757 AND (X_Attribute3 IS NULL)))
758 AND ( (Recinfo.attribute4 = X_Attribute4)
759 OR ( (Recinfo.attribute4 IS NULL)
760 AND (X_Attribute4 IS NULL)))
761 AND ( (Recinfo.attribute5 = X_Attribute5)
762 OR ( (Recinfo.attribute5 IS NULL)
763 AND (X_Attribute5 IS NULL)))
764 AND ( (Recinfo.attribute6 = X_Attribute6)
765 OR ( (Recinfo.attribute6 IS NULL)
766 AND (X_Attribute6 IS NULL)))
767 AND ( (Recinfo.attribute7 = X_Attribute7)
768 OR ( (Recinfo.attribute7 IS NULL)
769 AND (X_Attribute7 IS NULL)))
770 AND ( (Recinfo.attribute8 = X_Attribute8)
771 OR ( (Recinfo.attribute8 IS NULL)
772 AND (X_Attribute8 IS NULL)))
773 AND ( (Recinfo.attribute9 = X_Attribute9)
774 OR ( (Recinfo.attribute9 IS NULL)
775 AND (X_Attribute9 IS NULL)))
776 AND ( (Recinfo.attribute10 = X_Attribute10)
777 OR ( (Recinfo.attribute10 IS NULL)
778 AND (X_Attribute10 IS NULL)))
779 AND ( (Recinfo.attribute11 = X_Attribute11)
780 OR ( (Recinfo.attribute11 IS NULL)
781 AND (X_Attribute11 IS NULL)))
782 AND ( (Recinfo.attribute12 = X_Attribute12)
783 OR ( (Recinfo.attribute12 IS NULL)
784 AND (X_Attribute12 IS NULL)))
785 AND ( (Recinfo.attribute13 = X_Attribute13)
786 OR ( (Recinfo.attribute13 IS NULL)
787 AND (X_Attribute13 IS NULL)))
788 AND ( (Recinfo.attribute14 = X_Attribute14)
789 OR ( (Recinfo.attribute14 IS NULL)
790 AND (X_Attribute14 IS NULL)))
791 AND ( (Recinfo.attribute15 = X_Attribute15)
792 OR ( (Recinfo.attribute15 IS NULL)
793 AND (X_Attribute15 IS NULL)))
794 AND ( (Recinfo.attribute16 = X_Attribute16)
795 OR ( (Recinfo.attribute16 IS NULL)
796 AND (X_Attribute16 IS NULL)))
797 AND ( (Recinfo.attribute17 = X_Attribute17)
798 OR ( (Recinfo.attribute17 IS NULL)
799 AND (X_Attribute17 IS NULL)))
800 AND ( (Recinfo.attribute18 = X_Attribute18)
801 OR ( (Recinfo.attribute18 IS NULL)
802 AND (X_Attribute18 IS NULL)))
803 AND ( (Recinfo.attribute19 = X_Attribute19)
804 OR ( (Recinfo.attribute19 IS NULL)
805 AND (X_Attribute19 IS NULL)))
806 AND ( (Recinfo.attribute20 = X_Attribute20)
807 OR ( (Recinfo.attribute20 IS NULL)
808 AND (X_Attribute20 IS NULL)))
809 AND ( (Recinfo.status = X_Status)
810 OR ( (Recinfo.Status IS NULL)
811 AND (X_Status IS NULL)))
812 ) then
813 return;
814 else
815 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
816 APP_EXCEPTION.RAISE_EXCEPTION;
817 end if;
818 */
819 END Lock_Row;
820
821 PROCEDURE Update_Row(X_Rowid VARCHAR2,
822 X_Position_Id NUMBER,
823 X_Business_Group_Id NUMBER,
824 X_Job_Id NUMBER,
825 X_Organization_Id NUMBER,
826 X_Successor_Position_Id NUMBER,
827 X_Relief_Position_Id NUMBER,
828 X_Location_Id NUMBER,
829 X_Position_Definition_Id NUMBER,
830 X_Date_Effective DATE,
831 X_Comments VARCHAR2,
832 X_Date_End DATE,
833 X_Frequency VARCHAR2,
834 X_Name VARCHAR2,
835 X_Probation_Period NUMBER,
836 X_Probation_Period_Units VARCHAR2,
837 X_Replacement_Required_Flag VARCHAR2,
838 X_Time_Normal_Finish VARCHAR2,
839 X_Time_Normal_Start VARCHAR2,
840 X_Working_Hours NUMBER,
841 X_Status VARCHAR2,
842 X_Attribute_Category VARCHAR2,
843 X_Attribute1 VARCHAR2,
844 X_Attribute2 VARCHAR2,
845 X_Attribute3 VARCHAR2,
846 X_Attribute4 VARCHAR2,
847 X_Attribute5 VARCHAR2,
848 X_Attribute6 VARCHAR2,
849 X_Attribute7 VARCHAR2,
850 X_Attribute8 VARCHAR2,
851 X_Attribute9 VARCHAR2,
852 X_Attribute10 VARCHAR2,
853 X_Attribute11 VARCHAR2,
854 X_Attribute12 VARCHAR2,
855 X_Attribute13 VARCHAR2,
856 X_Attribute14 VARCHAR2,
857 X_Attribute15 VARCHAR2,
858 X_Attribute16 VARCHAR2,
859 X_Attribute17 VARCHAR2,
860 X_Attribute18 VARCHAR2,
861 X_Attribute19 VARCHAR2,
862 X_Attribute20 VARCHAR2
863 ) IS
864 BEGIN
865 null;
866 /*
867 Check_Unique_Row(X_Rowid,X_position_id);
868 Check_Descriptor(X_Rowid,X_position_definition_id,x_business_group_id);
869
870 UPDATE PER_POSITIONS
871 SET
872 position_id = X_Position_Id,
873 business_group_id = X_Business_Group_Id,
874 job_id = X_Job_Id,
875 organization_id = X_Organization_Id,
876 successor_position_id = X_Successor_Position_Id,
877 relief_position_id = X_Relief_Position_Id,
878 location_id = X_Location_Id,
879 position_definition_id = X_Position_Definition_Id,
880 date_effective = X_Date_Effective,
881 comments = X_Comments,
882 date_end = X_Date_End,
883 frequency = X_Frequency,
884 name = X_Name,
885 probation_period = X_Probation_Period,
886 probation_period_units = X_Probation_Period_Units,
887 replacement_required_flag = X_Replacement_Required_Flag,
888 time_normal_finish = X_Time_Normal_Finish,
889 time_normal_start = X_Time_Normal_Start,
890 working_hours = X_Working_Hours,
891 status = X_Status,
892 attribute_category = X_Attribute_Category,
893 attribute1 = X_Attribute1,
894 attribute2 = X_Attribute2,
895 attribute3 = X_Attribute3,
896 attribute4 = X_Attribute4,
897 attribute5 = X_Attribute5,
898 attribute6 = X_Attribute6,
899 attribute7 = X_Attribute7,
900 attribute8 = X_Attribute8,
901 attribute9 = X_Attribute9,
902 attribute10 = X_Attribute10,
903 attribute11 = X_Attribute11,
904 attribute12 = X_Attribute12,
905 attribute13 = X_Attribute13,
906 attribute14 = X_Attribute14,
907 attribute15 = X_Attribute15,
908 attribute16 = X_Attribute16,
909 attribute17 = X_Attribute17,
910 attribute18 = X_Attribute18,
911 attribute19 = X_Attribute19,
912 attribute20 = X_Attribute20
913 WHERE rowid = X_rowid;
914
915 if (SQL%NOTFOUND) then
916 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
917 hr_utility.set_message_token('PROCEDURE','Update_Row');
918 hr_utility.set_message_token('STEP','1');
919 hr_utility.raise_error;
920 end if;
921 */
922 END Update_Row;
923
924 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
925 X_Position_id number,
926 X_business_group_id number,
927 X_Hr_Ins varchar2,
928 X_Po_Ins varchar2,
929 X_View_All_Psts varchar2) IS
930 l_delete_row boolean;
931 BEGIN
932 null;
933 /*
934 pre_delete_checks(X_Rowid,
935 X_Position_id,
936 X_business_group_id,
937 X_Hr_Ins ,
938 X_Po_Ins,
939 l_delete_row );
940
941 if X_View_All_Psts <> 'Y' then
942 hr_security.delete_pos_from_list(X_Position_Id);
943 end if;
944
945 DELETE FROM PER_POSITIONS
946 WHERE rowid = X_Rowid;
947
948 if (SQL%NOTFOUND) then
949 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
950 hr_utility.set_message_token('PROCEDURE','Delete_Row');
951 hr_utility.set_message_token('STEP','1');
952 hr_utility.raise_error;
953 end if;
954 */
955 END Delete_Row;
956 --
957 -- return false if compiled flex struct does NOT exist
958 --
959 FUNCTION check_id_flex_struct ( p_id_flex_code VARCHAR2,
960 p_id_flex_num NUMBER ) RETURN BOOLEAN IS
961 --
962 -- declare cursor
963 --
964 CURSOR get_flex_struct IS
965 SELECT
966 'Y'
967 FROM
968 FND_COMPILED_ID_FLEX_STRUCTS FCF,
969 FND_ID_FLEX_STRUCTURES FS
970 WHERE
971 FCF.ID_FLEX_CODE = p_id_flex_code AND
972 FCF.ID_FLEX_NUM = p_id_flex_num
973 AND
974 FS.ID_FLEX_CODE = FCF.ID_FLEX_CODE AND
975 FS.ID_FLEX_NUM = FCF.ID_FLEX_NUM AND
976 FS.DYNAMIC_INSERTS_ALLOWED_FLAG = 'Y';
977 --
978 l_struct_exists VARCHAR2(1) := 'N';
979 --
980 BEGIN
981 --
982 -- get flex struct
983 --
984 OPEN get_flex_struct;
985 FETCH get_flex_struct INTO l_struct_exists;
986 CLOSE get_flex_struct;
987 --
988 -- check flex struct
989 --
990 IF (l_struct_exists = 'Y')
991 THEN
992 RETURN TRUE;
993 ELSE
994 RETURN FALSE;
995 END IF;
996 --
997 END check_id_flex_struct;
998 --
999 PROCEDURE check_date_effective ( p_position_id NUMBER,
1000 p_date_effective DATE) IS
1001 --
1002 -- declare cursor
1003 --
1004 CURSOR get_grades IS
1005 SELECT 'Y'
1006 FROM per_valid_grades
1007 WHERE position_id = p_position_id
1008 AND date_from < p_date_effective;
1009 --
1010 --declare local variables
1011 --
1012 l_grades_exist VARCHAR2(1) := 'N';
1013 --
1014 BEGIN
1015 --
1016 -- check that no valid grades exist before the start
1017 -- date of the position
1018 --
1019 OPEN get_grades;
1020 FETCH get_grades INTO l_grades_exist;
1021 CLOSE get_grades;
1022 --
1023 IF ( l_grades_exist = 'Y')
1024 THEN
1025 --
1026 -- error and abort
1027 --
1028 hr_utility.set_message(801,'PER_7995_POS_GRADE_BEFORE_POS');
1029 hr_utility.raise_error;
1030 --
1031 END IF;
1032 --
1033 END check_date_effective;
1034 --
1035 PROCEDURE check_valid_grades ( p_position_id NUMBER,
1036 p_end_of_time DATE,
1037 p_date_end DATE,
1038 p_before_date_to IN OUT NOCOPY BOOLEAN,
1039 p_before_date_from IN OUT NOCOPY BOOLEAN,
1040 p_end_date_blank IN OUT NOCOPY BOOLEAN,
1041 p_after_date_to IN OUT NOCOPY BOOLEAN) IS
1042 --
1043 cursor csr_date_to is select null
1044 from per_valid_grades
1045 where position_id = p_position_id
1046 and nvl(date_to, p_end_of_time) > p_date_end;
1047 --
1048 cursor csr_date_from is select null
1049 from per_valid_grades vg
1050 where vg.position_id = p_position_id
1051 and vg.date_from > p_date_end;
1052 --
1053 --
1054 -- BUG : 622996. Added csr_end_date_blank to return null if new
1055 -- position end date is null and old position end date matches
1056 -- currently valid grade end date. PASHUN. 24-FEB-1998.
1057 --
1058 --
1059 cursor csr_end_date_blank is select null
1060 from per_valid_grades vg , per_positions p
1061 where vg.position_id = p_position_id
1062 and p.position_id = p_position_id
1063 and nvl(vg.date_to, p_end_of_time) =
1064 nvl(p.date_end, p_end_of_time)
1065 and p_date_end is null;
1066 --
1067 --
1068 -- BUG : 622996. Added csr_after_date_to to return null if new
1069 -- position end date is later than valid grade end date
1070 -- and old position end date matches currently valid grade end
1071 -- date. PASHUN. 24-FEB-1998.
1072 --
1073 --
1074 cursor csr_after_date_to is select null
1075 from per_valid_grades vg , per_positions p
1076 where vg.position_id = p_position_id
1077 and p.position_id = p_position_id
1078 and nvl(vg.date_to,p_end_of_time) <
1079 nvl(p_date_end,p_end_of_time)
1080 and nvl(vg.date_to,p_end_of_time) =
1081 nvl(p.date_end,p_end_of_time);
1082 --
1083 g_dummy_number number;
1084 --
1085 begin
1086 --
1087 open csr_date_to;
1088 fetch csr_date_to into g_dummy_number;
1089 p_before_date_to := csr_date_to%FOUND;
1090 close csr_date_to;
1091 --
1092 hr_utility.set_location('per_positions_pkg.check_valid_grades', 1);
1093 --
1094 open csr_date_from;
1095 fetch csr_date_from into g_dummy_number;
1096 p_before_date_from := csr_date_from%FOUND;
1097 close csr_date_from;
1098 --
1099 hr_utility.set_location('per_positions_pkg.check_valid_grades', 2);
1100 --
1101 open csr_end_date_blank;
1102 fetch csr_end_date_blank into g_dummy_number;
1103 p_end_date_blank := csr_end_date_blank%FOUND;
1104 close csr_end_date_blank;
1105 --
1106 hr_utility.set_location('per_positions_pkg.check_valid_grades', 3);
1107 --
1108 open csr_after_date_to;
1109 fetch csr_after_date_to into g_dummy_number;
1110 p_after_date_to := csr_after_date_to%FOUND;
1111 close csr_after_date_to;
1112 --
1113 hr_utility.set_location('per_positions_pkg.check_valid_grades', 4);
1114 --
1115 end check_valid_grades;
1116 --
1117 PROCEDURE maintain_valid_grades(p_position_id NUMBER,
1118 p_date_end DATE,
1119 p_end_of_time DATE,
1120 p_before_date_to BOOLEAN,
1121 p_before_date_from BOOLEAN,
1122 p_end_date_blank BOOLEAN,
1123 p_after_date_to BOOLEAN) IS
1124 begin
1125 --
1126 IF ( p_before_date_to )
1127 THEN
1128 --
1129 -- Update valid grade end dates to match the end date of the
1130 -- position where the end date of the position is earlier than the end
1131 -- date of the valid grade or the previous end dates matched.
1132 --
1133 update per_valid_grades vg
1134 set vg.date_to =
1135 (select least(nvl(p_date_end, p_end_of_time),
1136 nvl(g.date_to, p_end_of_time))
1137 from per_grades g
1138 where g.grade_id = vg.grade_id)
1139 where
1140 vg.position_id = p_position_id
1141 and nvl(vg.date_to, p_end_of_time) > p_date_end;
1142 --
1143 if (SQL%NOTFOUND) then
1144 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1145 hr_utility.set_message_token('PROCEDURE','maintain_valid_grades');
1146 hr_utility.set_message_token('STEP','1');
1147 hr_utility.raise_error;
1148 end if;
1149 --
1150 END IF;
1151 --
1152 IF ( p_before_date_from )
1153 THEN
1154 --
1155 -- Valid grades are deleted if the end date of the position
1156 -- has been made earlier than the start date of the
1157 -- valid grade.
1158 --
1159 delete from per_valid_grades
1160 where position_id = p_position_id
1161 and date_from > p_date_end;
1162 --
1163 if (SQL%NOTFOUND) then
1164 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1165 hr_utility.set_message_token('PROCEDURE','maintain_valid_grades');
1166 hr_utility.set_message_token('STEP','2');
1167 hr_utility.raise_error;
1168 end if;
1169 --
1170 END IF;
1171 --
1172 IF ( p_end_date_blank or p_after_date_to )
1173 THEN
1174 --
1175 -- BUG : 622996. Update valid grade end dates to match, the least of the
1176 -- new end date of the position and the end date for the grade, where the old
1177 -- end date of the position matches the end date of the valid grade.
1178 -- PASHUN. 24-FEB-1998.
1179 --
1180 update per_valid_grades vg
1181 set vg.date_to =
1182 (select decode(least(nvl(p_date_end, p_end_of_time),
1183 nvl(g.date_to,p_end_of_time)),
1184 p_date_end,
1185 p_date_end,
1186 g.date_to,
1187 g.date_to,
1188 p_end_of_time,
1189 null,
1190 p_date_end)
1191 from per_grades g
1192 where g.grade_id = vg.grade_id)
1193 where
1194 vg.position_id = p_position_id
1195 and nvl(vg.date_to, p_end_of_time) = (select nvl(p.date_end,p_end_of_time)
1196 from per_positions p
1197 where p.position_id = p_position_id);
1198 --
1199 if (SQL%NOTFOUND) then
1200 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1201 hr_utility.set_message_token('PROCEDURE','maintain_valid_grades');
1202 hr_utility.set_message_token('STEP','3');
1203 hr_utility.raise_error;
1204 end if;
1205 END IF;
1206 --
1207 end maintain_valid_grades;
1208 --
1209 --
1210 FUNCTION GET_SHARED_POS_WARN_FLAG(p_user_id number) RETURN varchar2 is
1211 --
1212 l_show_again_flag varchar2(10) := 'Y';
1213 --
1214 cursor c_show_again(p_user_id number) is
1215 select information12
1216 from ben_copy_entity_results
1217 where copy_entity_txn_id = 0
1218 and table_alias = 'NONE'
1219 and INFORMATION11 = 'PERWSDPO'
1220 and information1 = p_user_id;
1221 --
1222 BEGIN
1223 --
1224 OPEN c_show_again(p_user_id);
1225 FETCH c_show_again INTO l_show_again_flag;
1226 CLOSE c_show_again;
1227 --
1228 RETURN nvl(l_show_again_flag,'Y');
1229 --
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 RETURN 'Y';
1233 END;
1234 --
1235 PROCEDURE SET_SHARED_POS_WARN_FLAG(p_user_id number, p_show_again_flag varchar2) is
1236 --
1237 PRAGMA AUTONOMOUS_TRANSACTION;
1238 --
1239 l_old_show_again_flag varchar2(10);
1240 --
1241 cursor c_show_again(p_user_id number) is
1242 select information12
1243 from ben_copy_entity_results
1244 where copy_entity_txn_id = 0
1245 and table_alias = 'NONE'
1246 and INFORMATION11 = 'PERWSDPO'
1247 and information1 = p_user_id;
1248 --
1249 BEGIN
1250 --
1251 OPEN c_show_again(p_user_id);
1252 FETCH c_show_again INTO l_old_show_again_flag;
1253 --
1254 IF (c_show_again%found) then
1255 --
1256 CLOSE c_show_again;
1257 --
1258 UPDATE ben_copy_entity_results
1259 SET information12 = 'Y',
1260 object_version_number = object_version_number + 1
1261 WHERE copy_entity_txn_id = 0
1262 and table_alias = 'NONE'
1263 and INFORMATION11 = 'PERWSDPO'
1264 and information1 = p_user_id;
1265 --
1266 ELSE
1267 --
1268 CLOSE c_show_again;
1269 --
1270 INSERT INTO ben_copy_entity_results
1271 (COPY_ENTITY_RESULT_ID, COPY_ENTITY_TXN_ID, RESULT_TYPE_CD,
1272 NUMBER_OF_COPIES, TABLE_ALIAS, STATUS,
1273 DML_OPERATION, DATETRACK_MODE,
1274 INFORMATION1, INFORMATION11, INFORMATION12,
1275 OBJECT_VERSION_NUMBER
1276 )
1277 values
1278 (
1279 ben_copy_entity_results_s.nextval, 0, 'DISPLAY',
1280 0, 'NONE', 'VALID',
1281 'INSERT', 'INSERT',
1282 p_user_id, 'PERWSDPO', p_show_again_flag,
1283 1
1284 );
1285 --
1286 END IF;
1287 COMMIT;
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290 rollback;
1291 END;
1292 --
1293 --
1294 END PER_POSITIONS_PKG;