[Home] [Help]
PACKAGE BODY: APPS.PER_EVENTS_PKG
Source
1 PACKAGE BODY PER_EVENTS_PKG as
2 /* $Header: peevt01t.pkb 120.2 2008/05/16 06:51:14 pchowdav ship $ */
3 -- *****************************************************************
4 -- Table Handler for per_events
5 -- *****************************************************************
6
7 g_dummy number(1); -- dummy variable for 'select 1...' statements
8
9 PROCEDURE CHECK_VALIDITY(X_INTERNAL_CONTACT_PERSON_ID NUMBER,
10 X_DATE_START DATE,
11 X_ORGANIZATION_RUN_BY_ID NUMBER,
12 X_BUSINESS_GROUP_ID NUMBER,
13 X_CTL_GLOBALS_END_OF_TIME DATE,
14 X_LOCATION_ID NUMBER,
15 X_EVENT_ID NUMBER) IS
16
17 L_TEMP1 NUMBER;
18 L_TEMP2 NUMBER;
19 L_TEMP3 NUMBER;
20 L_TEMP4 NUMBER;
21 L_TEMP5 NUMBER;
22
23
24 CURSOR CH1 IS
25 SELECT 1
26 FROM per_people_f
27 where person_id= X_INTERNAL_CONTACT_PERSON_ID
28 AND X_DATE_START BETWEEN
29 effective_Start_date and effective_end_Date;
30
31 CURSOR CH2 IS
32 SELECT 1
33 FROM hr_organization_units H
34 WHERE H.business_group_id + 0 = X_BUSINESS_GROUP_ID
35 AND X_DATE_START BETWEEN H.date_from
36 and NVL(H.date_to , X_CTL_GLOBALS_END_OF_TIME)
37 AND H.ORGANIZATION_ID = X_ORGANIZATION_RUN_BY_ID;
38
39 CURSOR CH3 IS
40 SELECT 1
41 FROM hr_locations l
42 WHERE X_DATE_START <= nvl(l.inactive_date,X_CTL_GLOBALS_END_OF_TIME)
43 AND LOCATION_ID = X_LOCATION_ID;
44 ---
45 --- commented to allow update of organization run by of events.
46 ---
47 /* CURSOR CH4 IS
48 SELECT 1
49 FROM PER_PEOPLE_F P,
50 PER_BOOKINGS B
51 WHERE P.PERSON_ID = B.PERSON_ID
52 AND B.EVENT_ID = X_EVENT_ID;
53
54 CURSOR CH5 IS
55 SELECT 1
56 FROM PER_PEOPLE_F P,
57 PER_BOOKINGS B
58 WHERE P.PERSON_ID = B.PERSON_ID
59 AND B.EVENT_ID = X_EVENT_ID
60 AND X_DATE_START BETWEEN
61 P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE; */
62
63 BEGIN
64
65 IF X_ORGANIZATION_RUN_BY_ID IS NOT NULL THEN
66 OPEN CH2;
67 FETCH CH2 INTO L_TEMP2;
68 IF CH2%NOTFOUND THEN
69 CLOSE CH2;
70 HR_UTILITY.SET_MESSAGE('801','HR_6627_EVENTS_ORG_NOT_VAL');
71 HR_UTILITY.RAISE_ERROR;
72 END IF;
73 CLOSE CH2;
74 END IF;
75 IF X_LOCATION_ID IS NOT NULL THEN
76 OPEN CH3;
77 FETCH CH3 INTO L_TEMP3;
78 IF CH3%NOTFOUND THEN
79 CLOSE CH3;
80 HR_UTILITY.SET_MESSAGE('801','HR_6628_EVENTS_LOC_NOT_VAL');
81 HR_UTILITY.RAISE_ERROR;
82 END IF;
83 CLOSE CH3;
84 END IF;
85 IF X_INTERNAL_CONTACT_PERSON_ID IS NOT NULL THEN
86 OPEN CH1;
87 FETCH CH1 INTO L_TEMP1;
88 IF CH1%NOTFOUND THEN
89 CLOSE CH1;
90 HR_UTILITY.SET_MESSAGE('801','HR_6660_EVENTS_INVAL_PERSON');
91 HR_UTILITY.RAISE_ERROR;
92 END IF;
93 CLOSE CH1;
94 END IF;
95
96 /* OPEN CH4;
97 FETCH CH4 INTO L_TEMP4;
98 IF CH4%FOUND THEN
99 CLOSE CH4;
100 OPEN CH5;
101 FETCH CH5 INTO L_TEMP5;
102 IF CH5%NOTFOUND THEN
103 CLOSE CH5;
104 HR_UTILITY.SET_MESSAGE('801','HR_6656_EVENTS_PERSON_INVALID');
105 HR_UTILITY.RAISE_ERROR;
106 ELSE
107 CLOSE CH5;
108 END IF;
109 ELSE
110 CLOSE CH4;
111 END IF; */
112
113 END CHECK_VALIDITY;
114 --------------------------------------------------------------------------------
115 function EVENT_CAUSES_ASSIGNMENT_CHANGE (
116 --
117 --******************************************************************************
118 --* Returns TRUE if there is a change to the assignment on the event date. This
119 --* may indicate that the event causes the assignment change.
120 --******************************************************************************
121 --
122 p_event_date date,
123 p_assignment_id number) return boolean is
124 --
125 v_change_exists boolean;
126 --
127 cursor csr_assignment is
128 select 1
129 from per_assignments_f
130 where assignment_id = p_assignment_id
131 and effective_start_date = p_event_date;
132 --
133 begin
134 --
135 hr_utility.set_location ('per_events_pkg.event_causes_assignment_change',1);
136 --
137 open csr_assignment;
138 fetch csr_assignment into g_dummy;
139 v_change_exists := csr_assignment%found;
140 close csr_assignment;
141 --
142 return v_change_exists;
143 --
144 end event_causes_assignment_change;
145 --------------------------------------------------------------------------------
146 --
147 -- Fix for bug 3270091 starts here.
148 -- Modified the function to pass time start and time end parameters.
149 -- The check is carried out on interview time start and time end.
150 --
151 function INTERVIEW_DOUBLE_BOOKED (
152 --
153 --******************************************************************************
154 --* Returns TRUE if the applicant already has an interview at the time required*
155 --******************************************************************************
156 --
157 p_person_id number,
158 p_interview_start_date date,
159 p_time_start varchar2,
160 p_time_end varchar2,
161 p_rowid varchar2 default null) return boolean is
162 --
163 v_interview_double_booked boolean;
164 l_time_start varchar2(5);
165 l_time_end varchar2(5);
166 l_same_time boolean;
167 --
168 cursor csr_double_booking is
169 select event.time_start, nvl(event.time_end,'24:00')
170 from per_events event, per_assignments assignment
171 where (p_rowid is null or p_rowid <> event.rowid)
172 and assignment.person_id = p_person_id
173 and event.assignment_id = assignment.assignment_id
174 and event.event_or_interview = 'I'
175 and p_interview_start_date between event.date_start
176 and nvl(event.date_end,
177 event.date_start);
178 --
179 begin
180 --
181 hr_utility.set_location ('per_events_pkg.interview_double_booked',1);
182 --
183 open csr_double_booking;
184 fetch csr_double_booking into l_time_start, l_time_end;
185 hr_utility.set_location ('per_events_pkg.interview_double_booked',2);
186 v_interview_double_booked := csr_double_booking%found;
187 close csr_double_booking;
188 --
189 hr_utility.set_location ('per_events_pkg.interview_double_booked',3);
190 l_same_time := v_interview_double_booked;
191 if v_interview_double_booked and p_time_start is not null and l_time_start is not null then
192 hr_utility.set_location ('per_events_pkg.interview_double_booked',4);
193 --
194 -- If interview has start time entered then compare on basis ofstart and end time.
195 -- The following code checks for the time overlap.
196 --
197 l_same_time :=
198 ( ( ((substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2)) >=
199 ((substr(l_time_start,1,2) * 60) + substr(l_time_start,4,2))
200 ) AND
201
202 ( ((substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2)) <=
203 ( (substr(l_time_end,1,2) * 60) + substr(l_time_end,4,2))
204 )
205 )
206 OR
207 ( ( ((substr(l_time_start,1,2) * 60) + substr(l_time_start,4,2)) >=
208 ((substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2))
209 ) AND
210
211 ( ((substr(l_time_start,1,2) * 60) + substr(l_time_start,4,2)) <=
212 ( (substr(p_time_end,1,2) * 60) + substr(p_time_end,4,2))
213 )
214 ) ;
215 --
216 hr_utility.set_location ('per_events_pkg.interview_double_booked',5);
217
218 end if;
219 hr_utility.set_location ('per_events_pkg.interview_double_booked',99);
220 --
221 return l_same_time;
222 --
223 end interview_double_booked;
224 --
225 -- Fix for bug 3270091 ends here.
226 --
227 --------------------------------------------------------------------------------
228 function INTERVIEWERS_ARE_BOOKED (
229 --
230 --******************************************************************************
231 --* Returns TRUE if there are interviewers booked for the interview passed in *
232 --******************************************************************************
233 --
234 p_event_id number,
235 p_error_if_true boolean default FALSE) return boolean is
236 --
237 cursor csr_booking is
238 select 1
239 from per_bookings
240 where event_id = p_event_id;
241 --
242 v_booking_exists boolean := FALSE;
243 --
244 begin
245 --
246 hr_utility.set_location ('per_events_pkg.interviewers_are_booked',1);
247 --
248 open csr_booking;
249 fetch csr_booking into g_dummy;
250 v_booking_exists := csr_booking%found;
251 close csr_booking;
252 --
253 if v_booking_exists and p_error_if_true then
254 hr_utility.set_message (801,'PER_7517_APP_INT_DELETE');
255 hr_utility.raise_error;
256 end if;
257 --
258 return v_booking_exists;
259 --
260 end interviewers_are_booked;
261 --------------------------------------------------------------------------------
262 procedure CHECK_CURRENT_INTERVIEWERS (
263 --
264 --******************************************************************************
265 --* Returns an error if an interviewer currently booked for an interview is *
266 --* unavailable on the updated interview date. *
267 --******************************************************************************
268 --
269 p_event_id number,
270 p_new_interview_start_date date) is
271 --
272 cursor csr_interview is
273 select interview.person_id
274 from per_bookings INTERVIEW
275 where interview.event_id = p_event_id;
276 --
277 /*cursor csr_person_start (p_person_id in number) is
278 select min (effective_start_date)
279 from per_all_people_f
280 where person_id = p_person_id;
281 --
282 cursor csr_person_end (p_person_id in number) is
283 select max (effective_start_date)
284 from per_all_people_f
285 where person_id = p_person_id;
286 --
287 --
288 interviewer_start date;
289 interviewer_end date;*/
290
291 -- bug fix 2708777
292 -- Cursor to check whether the interviewer is a valid
293 -- employee or contingent worker on new interview date.
294
295 cursor csr_person_exists(p_person_id in number) is
296 select 'Y'
297 from per_all_workforce_v
298 where person_id = p_person_id
299 and p_new_interview_start_date between effective_start_date
300 and effective_end_date;
301 l_dummy varchar2(1);
302 --
303 begin
304 --
305 hr_utility.set_location ('Entering per_events_pkg.check_current_interviewers',1);
306 --
307 for interview in csr_interview LOOP
308 -- bug fix 2708777 starts here.
309 open csr_person_exists(interview.person_id);
310 fetch csr_person_exists into l_dummy;
311
312 if csr_person_exists%notfound then
313
314 close csr_person_exists;
315 -- bug fix 2708777 ends here.
316 hr_utility.set_message (801,'HR_6752_EVENTS_DATE_INVALID');
317 hr_utility.raise_error;
318 --
319 end if;
320 --
321 close csr_person_exists;
322 --
323 end loop;
324 --
325 hr_utility.set_location ('Leaving per_events_pkg.check_current_interviewers',2);
326 --
327 end check_current_interviewers;
328 --------------------------------------------------------------------------------
329 procedure REQUEST_LETTER (
330 --
331 --******************************************************************************
332 --* Inserts a row in per_letter_requests for an interview, as long as there *
333 --* are no pending entries for the letter type and an automatic letter is *
334 --* required. *
335 --******************************************************************************
336 --
337 p_business_group_id number,
338 p_session_date date,
339 p_user number,
340 p_login_id number,
341 p_assignment_status_type_id number,
342 p_person_id number,
343 p_assignment_id number) is
344 --
345 cursor csr_check_letter is
346 select null
347 from per_letter_gen_statuses s
348 where s.business_group_id + 0 = p_business_group_id
349 and s.assignment_status_type_id = p_ASSIGNMENT_STATUS_TYPE_ID
350 and s.enabled_flag = 'Y';
351
352 cursor csr_vacancy_id is
353 Select vacancy_id
354 From per_all_assignments_f
355 Where assignment_id = p_assignment_id
356 And p_session_date between effective_start_date and effective_end_date;
357
358 l_vacancy_id number;
359 --
360 --fix for bug 7019343 starts here.
361 CURSOR csr_check_manual_or_auto IS
362 SELECT 1
363 FROM PER_LETTER_REQUESTS PLR,
364 PER_LETTER_GEN_STATUSES PLGS
365 WHERE PLGS.business_group_id + 0 = p_business_group_id
366 AND PLR.business_group_id +0 = p_business_group_id
367 AND PLGS.assignment_status_type_id = p_assignment_status_type_id
368 AND PLR.letter_type_id = PLGS.letter_type_id
369 AND PLR.auto_or_manual = 'MANUAL';
370 l_dummy_number number;
371 --fix for bug 7019343 ends here.
372
373 begin
374 --
375 hr_utility.set_location ('per_events_pkg.request_letter', 1);
376 --
377 open csr_check_letter;
378 fetch csr_check_letter into g_dummy;
379 if csr_check_letter%notfound then
380 return ;
381 end if ;
382 --
383 open csr_vacancy_id;
384 fetch csr_vacancy_id into l_vacancy_id;
385 if csr_vacancy_id%NOTFOUND then null;
386 end if;
387 close csr_vacancy_id;
388 --
389 --fix for bug 7019343 starts here.
390 open csr_check_manual_or_auto;
391 fetch csr_check_manual_or_auto into l_dummy_number;
392 if csr_check_manual_or_auto%found then
393 close csr_check_manual_or_auto;
394 return;
395 end if;
396 close csr_check_manual_or_auto;
397
398 if (nvl(fnd_profile.value('HR_LETTER_BY_VACANCY'),'N')='Y') then
399
400 hr_utility.set_location('HR_LETTER_BY_VACANCY = Y',10);
401 insert into per_letter_requests
402 ( letter_request_id
403 , business_group_id
404 , letter_type_id
405 , request_status
406 , auto_or_manual
407 , date_from
408 , last_update_date
409 , last_updated_by
410 , last_update_login
411 , created_by
412 , creation_date
413 , vacancy_id)
414 select per_letter_requests_s.nextval
415 , p_business_group_id
416 , s.letter_type_id
417 , 'PENDING'
418 , 'AUTO'
419 , p_session_date
420 , sysdate
421 , p_user
422 , p_login_id
423 , p_user
424 , sysdate
425 , l_vacancy_id
426 from per_letter_gen_statuses s
427 where s.business_group_id + 0 = p_business_group_id
428 and s.assignment_status_type_id = p_assignment_status_type_id
429 and s.enabled_flag = 'Y'
430 and not exists
431 (select null
432 from per_letter_requests r
433 where r.letter_type_id = s.letter_type_id
434 and r.business_group_id + 0 = p_business_group_id
435 and r.business_group_id + 0 = s.business_group_id
436 and r.request_status = 'PENDING'
437 and r.auto_or_manual = 'AUTO'
438 and r.vacancy_id = l_vacancy_id);
439 --
440 close csr_check_letter;
441 --
442
443 -- bug fix 3648618.
444 -- '+0' removed from where clause to improve performance.
445
446 insert into per_letter_request_lines
447 ( letter_request_line_id
448 , business_group_id
449 , letter_request_id
450 , person_id
451 , assignment_id
452 , assignment_status_type_id
453 , date_from
454 , last_update_date
455 , last_updated_by
456 , last_update_login
457 , created_by
458 , creation_date)
459 select per_letter_request_lines_s.nextval
460 , p_business_group_id
461 , r.letter_request_id
462 , p_person_id
463 , p_ASSIGNMENT_ID
464 , p_ASSIGNMENT_STATUS_TYPE_ID
465 , p_session_date
466 , sysdate
467 , p_user
468 , p_login_id
469 , p_user
470 , sysdate
471 from per_letter_requests r
472 where exists
473 (select null
474 from per_letter_gen_statuses s
475 where s.letter_type_id = r.letter_type_id
476 and s.business_group_id + 0 = p_business_group_id
477 and s.business_group_id + 0 = r.business_group_id + 0
478 and s.assignment_status_type_id =
479 p_ASSIGNMENT_STATUS_TYPE_ID
480 and s.enabled_flag = 'Y')
481 and not exists
482 (select l.assignment_id
483 from per_letter_request_lines l
484 where l.letter_request_id = r.letter_request_id
485 and l.business_group_id + 0 = p_business_group_id
486 and l.assignment_id = p_ASSIGNMENT_ID
487 and l.business_group_id +0 = r.business_group_id + 0)
488 and r.request_status = 'PENDING'
489 and r.business_group_id = p_business_group_id -- bug fix 3648618
490 and r.vacancy_id = l_vacancy_id;
491 else
492
493 -- Profile HR: Letter by Vacancy has not been set to Yes
494 insert into per_letter_requests
495 ( letter_request_id
496 , business_group_id
497 , letter_type_id
498 , request_status
499 , auto_or_manual
500 , date_from
501 , last_update_date
502 , last_updated_by
503 , last_update_login
504 , created_by
505 , creation_date
506 , vacancy_id)
507 select per_letter_requests_s.nextval
508 , p_business_group_id
509 , s.letter_type_id
510 , 'PENDING'
511 , 'AUTO'
512 , p_session_date
513 , sysdate
514 , p_user
515 , p_login_id
516 , p_user
517 , sysdate
518 , l_vacancy_id
519 from per_letter_gen_statuses s
520 where s.business_group_id + 0 = p_business_group_id
521 and s.assignment_status_type_id = p_assignment_status_type_id
522 and s.enabled_flag = 'Y'
523 and not exists
524 (select null
525 from per_letter_requests r
526 where r.letter_type_id = s.letter_type_id
527 and r.business_group_id + 0 = p_business_group_id
528 and r.business_group_id + 0 = s.business_group_id
529 and r.request_status = 'PENDING'
530 and r.auto_or_manual = 'AUTO'
531 );
532 --
533 close csr_check_letter;
534 --
535
536 -- bug fix 3648618.
537 -- '+0' removed from where clause to improve performance.
538
539 insert into per_letter_request_lines
540 ( letter_request_line_id
541 , business_group_id
542 , letter_request_id
543 , person_id
544 , assignment_id
545 , assignment_status_type_id
546 , date_from
547 , last_update_date
548 , last_updated_by
549 , last_update_login
550 , created_by
551 , creation_date)
552 select per_letter_request_lines_s.nextval
553 , p_business_group_id
554 , r.letter_request_id
555 , p_person_id
556 , p_ASSIGNMENT_ID
557 , p_ASSIGNMENT_STATUS_TYPE_ID
558 , p_session_date
559 , sysdate
560 , p_user
561 , p_login_id
562 , p_user
563 , sysdate
564 from per_letter_requests r
565 where exists
566 (select null
567 from per_letter_gen_statuses s
568 where s.letter_type_id = r.letter_type_id
569 and s.business_group_id + 0 = p_business_group_id
570 and s.business_group_id + 0 = r.business_group_id + 0
571 and s.assignment_status_type_id =
572 p_ASSIGNMENT_STATUS_TYPE_ID
573 and s.enabled_flag = 'Y')
574 and not exists
575 (select l.assignment_id
576 from per_letter_request_lines l
577 where l.letter_request_id = r.letter_request_id
578 and l.business_group_id + 0 = p_business_group_id
579 and l.assignment_id = p_ASSIGNMENT_ID
580 and l.business_group_id +0 = r.business_group_id + 0)
581 and r.request_status = 'PENDING'
582 and r.business_group_id = p_business_group_id -- bug fix 3648618
583 ;
584
585 end if;
586 --fix for bug 7019343 ends here.
587 end request_letter;
588 --------------------------------------------------------------------------------
589 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
590 X_Event_Id IN OUT NOCOPY NUMBER,
591 X_Business_Group_Id NUMBER,
592 X_Location_Id NUMBER,
593 X_Internal_Contact_Person_Id NUMBER,
594 X_Organization_Run_By_Id NUMBER,
595 X_Assignment_Id NUMBER,
596 X_Date_Start DATE,
597 X_Type VARCHAR2,
598 X_Comments VARCHAR2,
599 X_Contact_Telephone_Number VARCHAR2,
600 X_Date_End DATE,
601 X_Emp_Or_Apl VARCHAR2,
602 X_Event_Or_Interview VARCHAR2,
603 X_External_Contact VARCHAR2,
604 X_Time_End VARCHAR2,
605 X_Time_Start VARCHAR2,
606 X_Attribute_Category VARCHAR2,
607 X_Attribute1 VARCHAR2,
608 X_Attribute2 VARCHAR2,
609 X_Attribute3 VARCHAR2,
610 X_Attribute4 VARCHAR2,
611 X_Attribute5 VARCHAR2,
612 X_Attribute6 VARCHAR2,
613 X_Attribute7 VARCHAR2,
614 X_Attribute8 VARCHAR2,
615 X_Attribute9 VARCHAR2,
616 X_Attribute10 VARCHAR2,
617 X_Attribute11 VARCHAR2,
618 X_Attribute12 VARCHAR2,
619 X_Attribute13 VARCHAR2,
620 X_Attribute14 VARCHAR2,
621 X_Attribute15 VARCHAR2,
622 X_Attribute16 VARCHAR2,
623 X_Attribute17 VARCHAR2,
624 X_Attribute18 VARCHAR2,
625 X_Attribute19 VARCHAR2,
626 X_Attribute20 VARCHAR2,
627 X_ctl_globals_end_of_time DATE
628 ) IS
629
630 L_DUMMY NUMBER;
631 l_party_id number;
632
633 CURSOR C IS
634 SELECT rowid FROM PER_EVENTS
635 WHERE event_id = X_Event_Id;
636
637 CURSOR C2 IS
638 SELECT PER_EVENTS_S.NEXTVAL
639 FROM SYS.DUAL;
640
641 CURSOR LOCATION_CHECK IS
642 select 1
643 from hr_locations l
644 where l.location_id = X_Location_Id
645 and nvl(l.inactive_date,X_ctl_globals_end_of_time) >= X_date_start;
646
647 cursor csr_get_party_id is
648 select max(party_id) from per_all_people_f
649 where person_id = (select asg.person_id
650 from per_all_assignments_f asg
651 where asg.assignment_id = X_Assignment_Id
652 and X_Date_Start between asg.effective_start_date
653 and asg.effective_end_date);
654
655 BEGIN
656 -- As this package is used by PERWSERW and PERWSGEB, in case of
657 -- PERWSERW the X_Event_Or_Interview will always be 'I' ie.Interview
658 -- but in case of PERWSGEB, the X_Event_Or_Interview will always be 'E'
659 -- Therefore if it equals 'E' then it must be called from the PERWSGEB
660 -- form, hence call the procedure below
661
662 IF X_Event_Or_Interview = 'E' THEN
663 CHECK_VALIDITY(X_Internal_Contact_person_Id,
664 X_Date_Start,
665 X_Organization_Run_By_Id,
666 X_Business_group_Id,
667 X_ctl_globals_end_of_time,
668 X_Location_Id,
669 X_Event_Id);
670 END IF;
671
672
673 IF X_Event_Or_Interview <> 'E' THEN
674 IF X_Location_Id IS NOT NULL THEN
675 OPEN LOCATION_CHECK;
676 FETCH LOCATION_CHECK INTO L_DUMMY;
677 IF LOCATION_CHECK%NOTFOUND THEN
678 CLOSE LOCATION_CHECK;
679 HR_UTILITY.SET_MESSAGE('801','HR_6747_EVENTS_LOC_INACTIVE');
680 HR_UTILITY.RAISE_ERROR;
681 ELSE
682 CLOSE LOCATION_CHECK;
683 END IF;
684 END IF;
685 END IF;
686
687 OPEN C2;
688 FETCH C2 INTO X_Event_Id;
689 CLOSE C2;
690 --
691 -- Get party_id from per_all_people_f using assignment_id
692 --
693 open csr_get_party_id;
694 fetch csr_get_party_id into l_party_id;
695 close csr_get_party_id;
696 --
697 INSERT INTO PER_EVENTS(
698 event_id,
699 business_group_id,
700 location_id,
701 internal_contact_person_id,
702 organization_run_by_id,
703 assignment_id,
704 date_start,
705 type,
706 comments,
707 contact_telephone_number,
708 date_end,
709 emp_or_apl,
710 event_or_interview,
711 external_contact,
712 time_end,
713 time_start,
714 attribute_category,
715 attribute1,
716 attribute2,
717 attribute3,
718 attribute4,
719 attribute5,
720 attribute6,
721 attribute7,
722 attribute8,
723 attribute9,
724 attribute10,
725 attribute11,
726 attribute12,
727 attribute13,
728 attribute14,
729 attribute15,
730 attribute16,
731 attribute17,
732 attribute18,
733 attribute19,
734 attribute20,
735 party_id
736 ) VALUES (
737 X_Event_Id,
738 X_Business_Group_Id,
739 X_Location_Id,
740 X_Internal_Contact_Person_Id,
741 X_Organization_Run_By_Id,
742 X_Assignment_Id,
743 X_Date_Start,
744 X_Type,
745 X_Comments,
746 X_Contact_Telephone_Number,
747 X_Date_End,
748 X_Emp_Or_Apl,
749 X_Event_Or_Interview,
750 X_External_Contact,
751 X_Time_End,
752 X_Time_Start,
753 X_Attribute_Category,
754 X_Attribute1,
755 X_Attribute2,
756 X_Attribute3,
757 X_Attribute4,
758 X_Attribute5,
759 X_Attribute6,
760 X_Attribute7,
761 X_Attribute8,
762 X_Attribute9,
763 X_Attribute10,
764 X_Attribute11,
765 X_Attribute12,
766 X_Attribute13,
767 X_Attribute14,
768 X_Attribute15,
769 X_Attribute16,
770 X_Attribute17,
771 X_Attribute18,
772 X_Attribute19,
773 X_Attribute20,
774 l_party_id
775 );
776
777 OPEN C;
778 FETCH C INTO X_Rowid;
779 if (C%NOTFOUND) then
780 CLOSE C;
781 HR_UTILITY.SET_MESSAGE(801,'HR_6153_ALL_PROCEDURE_FAIL');
782 HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','INSERT_ROW');
783 HR_UTILITY.SET_MESSAGE_TOKEN('STEP','1');
784 HR_UTILITY.RAISE_ERROR;
785 end if;
786 CLOSE C;
787 END Insert_Row;
788 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
789 X_Event_Id NUMBER,
790 X_Business_Group_Id NUMBER,
791 X_Location_Id NUMBER,
792 X_Internal_Contact_Person_Id NUMBER,
793 X_Organization_Run_By_Id NUMBER,
794 X_Assignment_Id NUMBER,
795 X_Date_Start DATE,
796 X_Type VARCHAR2,
797 X_Comments VARCHAR2,
798 X_Contact_Telephone_Number VARCHAR2,
799 X_Date_End DATE,
800 X_Emp_Or_Apl VARCHAR2,
801 X_Event_Or_Interview VARCHAR2,
802 X_External_Contact VARCHAR2,
803 X_Time_End VARCHAR2,
804 X_Time_Start VARCHAR2,
805 X_Attribute_Category VARCHAR2,
806 X_Attribute1 VARCHAR2,
807 X_Attribute2 VARCHAR2,
808 X_Attribute3 VARCHAR2,
809 X_Attribute4 VARCHAR2,
810 X_Attribute5 VARCHAR2,
811 X_Attribute6 VARCHAR2,
812 X_Attribute7 VARCHAR2,
813 X_Attribute8 VARCHAR2,
814 X_Attribute9 VARCHAR2,
815 X_Attribute10 VARCHAR2,
816 X_Attribute11 VARCHAR2,
817 X_Attribute12 VARCHAR2,
818 X_Attribute13 VARCHAR2,
819 X_Attribute14 VARCHAR2,
820 X_Attribute15 VARCHAR2,
821 X_Attribute16 VARCHAR2,
822 X_Attribute17 VARCHAR2,
823 X_Attribute18 VARCHAR2,
824 X_Attribute19 VARCHAR2,
825 X_Attribute20 VARCHAR2
826 ) IS
827
828 CURSOR C IS
829 SELECT *
830 FROM PER_EVENTS
831 WHERE rowid = X_Rowid
832 FOR UPDATE of Event_Id NOWAIT;
833 Recinfo C%ROWTYPE;
834 BEGIN
835 OPEN C;
836 FETCH C INTO Recinfo;
837 if (C%NOTFOUND) then
838 CLOSE C;
839 HR_UTILITY.SET_MESSAGE(801,'HR_6153_ALL_PROCEDURE_FAIL');
840 HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','LOCK_ROW');
841 HR_UTILITY.SET_MESSAGE_TOKEN('STEP','1');
842 HR_UTILITY.RAISE_ERROR;
843 end if;
844 CLOSE C;
845 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
846 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
847 Recinfo.type := rtrim(Recinfo.type);
848 Recinfo.comments := rtrim(Recinfo.comments);
849 Recinfo.contact_telephone_number := rtrim(Recinfo.contact_telephone_number);
850 Recinfo.emp_or_apl := rtrim(Recinfo.emp_or_apl);
851 Recinfo.event_or_interview := rtrim(Recinfo.event_or_interview);
852 Recinfo.external_contact := rtrim(Recinfo.external_contact);
853 Recinfo.time_end := rtrim(Recinfo.time_end);
854 Recinfo.time_start := rtrim(Recinfo.time_start);
855 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
856 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
857 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
858 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
859 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
860 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
861 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
862 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
863 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
864 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
865 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
866 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
867 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
868 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
869 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
870 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
871 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
872 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
873 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
874 if (
875 ( (Recinfo.event_id = X_Event_Id)
876 OR ( (Recinfo.event_id IS NULL)
877 AND (X_Event_Id IS NULL)))
878 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
879 OR ( (Recinfo.business_group_id IS NULL)
880 AND (X_Business_Group_Id IS NULL)))
881 AND ( (Recinfo.location_id = X_Location_Id)
882 OR ( (Recinfo.location_id IS NULL)
883 AND (X_Location_Id IS NULL)))
884 AND ( (Recinfo.internal_contact_person_id = X_Internal_Contact_Person_Id)
885 OR ( (Recinfo.internal_contact_person_id IS NULL)
886 AND (X_Internal_Contact_Person_Id IS NULL)))
887 AND ( (Recinfo.organization_run_by_id = X_Organization_Run_By_Id)
888 OR ( (Recinfo.organization_run_by_id IS NULL)
889 AND (X_Organization_Run_By_Id IS NULL)))
890 AND ( (Recinfo.assignment_id = X_Assignment_Id)
891 OR ( (Recinfo.assignment_id IS NULL)
892 AND (X_Assignment_Id IS NULL)))
893 AND ( (Recinfo.date_start = X_Date_Start)
894 OR ( (Recinfo.date_start IS NULL)
895 AND (X_Date_Start IS NULL)))
896 AND ( (Recinfo.type = X_Type)
897 OR ( (Recinfo.type IS NULL)
898 AND (X_Type IS NULL)))
899 AND ( (Recinfo.comments = X_Comments)
900 OR ( (Recinfo.comments IS NULL)
901 AND (X_Comments IS NULL)))
902 AND ( (Recinfo.contact_telephone_number = X_Contact_Telephone_Number)
903 OR ( (Recinfo.contact_telephone_number IS NULL)
904 AND (X_Contact_Telephone_Number IS NULL)))
905 AND ( (Recinfo.date_end = X_Date_End)
906 OR ( (Recinfo.date_end IS NULL)
907 AND (X_Date_End IS NULL)))
908 AND ( (Recinfo.emp_or_apl = X_Emp_Or_Apl)
909 OR ( (Recinfo.emp_or_apl IS NULL)
910 AND (X_Emp_Or_Apl IS NULL)))
911 AND ( (Recinfo.event_or_interview = X_Event_Or_Interview)
912 OR ( (Recinfo.event_or_interview IS NULL)
913 AND (X_Event_Or_Interview IS NULL)))
914 AND ( (Recinfo.external_contact = X_External_Contact)
915 OR ( (Recinfo.external_contact IS NULL)
916 AND (X_External_Contact IS NULL)))
917 AND ( (Recinfo.time_end = X_Time_End)
918 OR ( (Recinfo.time_end IS NULL)
919 AND (X_Time_End IS NULL)))
920 AND ( (Recinfo.time_start = X_Time_Start)
921 OR ( (Recinfo.time_start IS NULL)
922 AND (X_Time_Start IS NULL)))
923 AND ( (Recinfo.attribute_category = X_Attribute_Category)
924 OR ( (Recinfo.attribute_category IS NULL)
925 AND (X_Attribute_Category IS NULL)))
926 AND ( (Recinfo.attribute1 = X_Attribute1)
927 OR ( (Recinfo.attribute1 IS NULL)
928 AND (X_Attribute1 IS NULL)))
929 AND ( (Recinfo.attribute2 = X_Attribute2)
930 OR ( (Recinfo.attribute2 IS NULL)
931 AND (X_Attribute2 IS NULL)))
932 AND ( (Recinfo.attribute3 = X_Attribute3)
933 OR ( (Recinfo.attribute3 IS NULL)
934 AND (X_Attribute3 IS NULL)))
935 AND ( (Recinfo.attribute4 = X_Attribute4)
936 OR ( (Recinfo.attribute4 IS NULL)
937 AND (X_Attribute4 IS NULL)))
938 AND ( (Recinfo.attribute5 = X_Attribute5)
939 OR ( (Recinfo.attribute5 IS NULL)
940 AND (X_Attribute5 IS NULL)))
941 AND ( (Recinfo.attribute6 = X_Attribute6)
942 OR ( (Recinfo.attribute6 IS NULL)
943 AND (X_Attribute6 IS NULL)))
944 AND ( (Recinfo.attribute7 = X_Attribute7)
945 OR ( (Recinfo.attribute7 IS NULL)
946 AND (X_Attribute7 IS NULL)))
947 AND ( (Recinfo.attribute8 = X_Attribute8)
948 OR ( (Recinfo.attribute8 IS NULL)
949 AND (X_Attribute8 IS NULL)))
950 AND ( (Recinfo.attribute9 = X_Attribute9)
951 OR ( (Recinfo.attribute9 IS NULL)
952 AND (X_Attribute9 IS NULL)))
953 AND ( (Recinfo.attribute10 = X_Attribute10)
954 OR ( (Recinfo.attribute10 IS NULL)
955 AND (X_Attribute10 IS NULL)))
956 AND ( (Recinfo.attribute11 = X_Attribute11)
957 OR ( (Recinfo.attribute11 IS NULL)
958 AND (X_Attribute11 IS NULL)))
959 AND ( (Recinfo.attribute12 = X_Attribute12)
960 OR ( (Recinfo.attribute12 IS NULL)
961 AND (X_Attribute12 IS NULL)))
962 AND ( (Recinfo.attribute13 = X_Attribute13)
963 OR ( (Recinfo.attribute13 IS NULL)
964 AND (X_Attribute13 IS NULL)))
965 AND ( (Recinfo.attribute14 = X_Attribute14)
966 OR ( (Recinfo.attribute14 IS NULL)
967 AND (X_Attribute14 IS NULL)))
968 AND ( (Recinfo.attribute15 = X_Attribute15)
969 OR ( (Recinfo.attribute15 IS NULL)
970 AND (X_Attribute15 IS NULL)))
971 AND ( (Recinfo.attribute16 = X_Attribute16)
972 OR ( (Recinfo.attribute16 IS NULL)
973 AND (X_Attribute16 IS NULL)))
974 AND ( (Recinfo.attribute17 = X_Attribute17)
975 OR ( (Recinfo.attribute17 IS NULL)
976 AND (X_Attribute17 IS NULL)))
977 AND ( (Recinfo.attribute18 = X_Attribute18)
978 OR ( (Recinfo.attribute18 IS NULL)
979 AND (X_Attribute18 IS NULL)))
980 AND ( (Recinfo.attribute19 = X_Attribute19)
981 OR ( (Recinfo.attribute19 IS NULL)
982 AND (X_Attribute19 IS NULL)))
983 AND ( (Recinfo.attribute20 = X_Attribute20)
984 OR ( (Recinfo.attribute20 IS NULL)
985 AND (X_Attribute20 IS NULL)))
986 ) then
987 return;
988 else
989 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
990 APP_EXCEPTION.RAISE_EXCEPTION;
991 end if;
992 END Lock_Row;
993
994 PROCEDURE Update_Row(X_Rowid VARCHAR2,
995 X_Event_Id NUMBER,
996 X_Business_Group_Id NUMBER,
997 X_Location_Id NUMBER,
998 X_Internal_Contact_Person_Id NUMBER,
999 X_Organization_Run_By_Id NUMBER,
1000 X_Assignment_Id NUMBER,
1001 X_Date_Start DATE,
1002 X_Type VARCHAR2,
1003 X_Comments VARCHAR2,
1004 X_Contact_Telephone_Number VARCHAR2,
1005 X_Date_End DATE,
1006 X_Emp_Or_Apl VARCHAR2,
1007 X_Event_Or_Interview VARCHAR2,
1008 X_External_Contact VARCHAR2,
1009 X_Time_End VARCHAR2,
1010 X_Time_Start VARCHAR2,
1011 X_Attribute_Category VARCHAR2,
1012 X_Attribute1 VARCHAR2,
1013 X_Attribute2 VARCHAR2,
1014 X_Attribute3 VARCHAR2,
1015 X_Attribute4 VARCHAR2,
1016 X_Attribute5 VARCHAR2,
1017 X_Attribute6 VARCHAR2,
1018 X_Attribute7 VARCHAR2,
1019 X_Attribute8 VARCHAR2,
1020 X_Attribute9 VARCHAR2,
1021 X_Attribute10 VARCHAR2,
1022 X_Attribute11 VARCHAR2,
1023 X_Attribute12 VARCHAR2,
1024 X_Attribute13 VARCHAR2,
1025 X_Attribute14 VARCHAR2,
1026 X_Attribute15 VARCHAR2,
1027 X_Attribute16 VARCHAR2,
1028 X_Attribute17 VARCHAR2,
1029 X_Attribute18 VARCHAR2,
1030 X_Attribute19 VARCHAR2,
1031 X_Attribute20 VARCHAR2,
1032 X_ctl_globals_end_of_time DATE
1033 ) IS
1034
1035 L_DUMMY NUMBER;
1036
1037 CURSOR LOCATION_CHECK IS
1038 select 1
1039 from hr_locations l
1040 where l.location_id = X_Location_Id
1041 and nvl(l.inactive_date,X_ctl_globals_end_of_time) >= X_Date_Start;
1042
1043 BEGIN
1044 -- As this package is used by PERWSERW and PERWSGEB, in case of
1045 -- PERWSERW the X_Event_Or_Interview will always be 'I' ie.Interview
1046 -- but in case of PERWSGEB, the X_Event_Or_Interview will always be 'E'
1047 -- Therefore if it equals 'E' then it must be called from the PERWSGEB
1048 -- form, hence call the procedure below
1049
1050 IF X_Event_Or_Interview = 'E' THEN
1051 CHECK_VALIDITY(X_Internal_Contact_person_Id,
1052 X_Date_Start,
1053 X_Organization_Run_By_Id,
1054 X_Business_group_Id,
1055 X_ctl_globals_end_of_time,
1056 X_Location_Id,
1057 X_Event_Id);
1058 END IF;
1059
1060
1061 IF X_Event_Or_Interview <> 'E' THEN
1062 IF X_Location_Id IS NOT NULL THEN
1063 OPEN LOCATION_CHECK;
1064 FETCH LOCATION_CHECK INTO L_DUMMY;
1065 IF LOCATION_CHECK%NOTFOUND THEN
1066 CLOSE LOCATION_CHECK;
1067 HR_UTILITY.SET_MESSAGE('801','HR_6747_EVENTS_LOC_INACTIVE');
1068 HR_UTILITY.RAISE_ERROR;
1069 ELSE
1070 CLOSE LOCATION_CHECK;
1071 END IF;
1072 END IF;
1073 END IF;
1074
1075 UPDATE PER_EVENTS
1076 SET
1077
1078 event_id = X_Event_Id,
1079 business_group_id = X_Business_Group_Id,
1080 location_id = X_Location_Id,
1081 internal_contact_person_id = X_Internal_Contact_Person_Id,
1082 organization_run_by_id = X_Organization_Run_By_Id,
1083 assignment_id = X_Assignment_Id,
1084 date_start = X_Date_Start,
1085 type = X_Type,
1086 comments = X_Comments,
1087 contact_telephone_number = X_Contact_Telephone_Number,
1088 date_end = X_Date_End,
1089 emp_or_apl = X_Emp_Or_Apl,
1090 event_or_interview = X_Event_Or_Interview,
1091 external_contact = X_External_Contact,
1092 time_end = X_Time_End,
1093 time_start = X_Time_Start,
1094 attribute_category = X_Attribute_Category,
1095 attribute1 = X_Attribute1,
1096 attribute2 = X_Attribute2,
1097 attribute3 = X_Attribute3,
1098 attribute4 = X_Attribute4,
1099 attribute5 = X_Attribute5,
1100 attribute6 = X_Attribute6,
1101 attribute7 = X_Attribute7,
1102 attribute8 = X_Attribute8,
1103 attribute9 = X_Attribute9,
1104 attribute10 = X_Attribute10,
1105 attribute11 = X_Attribute11,
1106 attribute12 = X_Attribute12,
1107 attribute13 = X_Attribute13,
1108 attribute14 = X_Attribute14,
1109 attribute15 = X_Attribute15,
1110 attribute16 = X_Attribute16,
1111 attribute17 = X_Attribute17,
1112 attribute18 = X_Attribute18,
1113 attribute19 = X_Attribute19,
1114 attribute20 = X_Attribute20
1115 WHERE rowid = X_rowid;
1116
1117 if (SQL%NOTFOUND) then
1118 HR_UTILITY.SET_MESSAGE(801,'HR_6153_ALL_PROCEDURE_FAIL');
1119 HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','UPDATE_ROW');
1120 HR_UTILITY.SET_MESSAGE_TOKEN('STEP','1');
1121 HR_UTILITY.RAISE_ERROR;
1122 end if;
1123
1124 END Update_Row;
1125
1126 -- X_Message is passed from PERWSERW and PERWSGEB
1127 -- and the procedure will display the right message
1128 --
1129 -- P1 refers to PERWSERW
1130 -- P2 refers to PERWSGEB
1131 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
1132 X_Event_Id NUMBER,
1133 X_Business_Group_Id NUMBER,
1134 X_Message VARCHAR2,
1135 X_Form VARCHAR2
1136 ) IS
1137 L_DUMMY NUMBER;
1138 L_DUMMY2 NUMBER;
1139
1140 CURSOR CHILD_CHECK IS
1141 SELECT 1
1142 FROM PER_BOOKINGS
1143 WHERE BUSINESS_GROUP_ID + 0 = X_Business_Group_Id
1144 AND EVENT_ID = X_Event_Id;
1145
1146 CURSOR PAY_CHANGE_CHECK IS
1147 SELECT 1
1148 FROM PER_PAY_PROPOSALS PP
1149 WHERE PP.EVENT_ID = X_Event_Id;
1150
1151 BEGIN
1152
1153 OPEN CHILD_CHECK;
1154 FETCH CHILD_CHECK INTO L_DUMMY;
1155 IF CHILD_CHECK%FOUND THEN
1156 CLOSE CHILD_CHECK;
1157 HR_UTILITY.SET_MESSAGE('801',X_Message);
1158 HR_UTILITY.RAISE_ERROR;
1159 ELSE
1160 CLOSE CHILD_CHECK;
1161 END IF;
1162
1163 -- If the procedure is called from PERWSGEB then skip this check
1164 -- as this check relates to PERWSERW
1165 IF X_Form = 'P1' then
1166 OPEN PAY_CHANGE_CHECK;
1167 FETCH PAY_CHANGE_CHECK INTO L_DUMMY2;
1168 IF PAY_CHANGE_CHECK%FOUND THEN
1169 CLOSE PAY_CHANGE_CHECK;
1170 HR_UTILITY.SET_MESSAGE('801','HR_7100_EVENTS_CHANGE_EXIST');
1171 HR_UTILITY.RAISE_ERROR;
1172 ELSE
1173 CLOSE PAY_CHANGE_CHECK;
1174 END IF;
1175 END IF;
1176
1177 DELETE FROM PER_EVENTS
1178 WHERE rowid = X_Rowid;
1179
1180 if (SQL%NOTFOUND) then
1181 HR_UTILITY.SET_MESSAGE(801,'HR_6153_ALL_PROCEDURE_FAIL');
1182 HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','DELETE_ROW');
1183 HR_UTILITY.SET_MESSAGE_TOKEN('STEP','1');
1184 HR_UTILITY.RAISE_ERROR;
1185 end if;
1186 END Delete_Row;
1187
1188 END PER_EVENTS_PKG;