[Home] [Help]
PACKAGE BODY: APPS.PER_RECRUITMENT_ACTIVITIES_PKG
Source
1 PACKAGE BODY PER_RECRUITMENT_ACTIVITIES_PKG as
2 /* $Header: perca01t.pkb 115.4 2003/02/11 11:55:00 eumenyio ship $ */
3 --
4 /* +=======================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +=======================================================================+
9 Name
10 per_recruitment_activities_pkg
11 Purpose
12 Supports the ACTIVITY block in the form PERWSDRA (Define Recruitment
13 Activity).
14 Notes
15 Changed X_Parent_Recruitment_Activity_Id to X_Parent_Rec_Activity_Id
16 because was too long otherwise.
17
18 History
19 21-Feb-94 H.Minton 40.0 Date created.
20 01-JUL-94 H.Minton 40.1 Added procedure chk_auth_date
21 23-NOV-94 rfine 70.4 Suppressed index on business_group_id
22 29-JAN-95 D.Kerr 70.6 Removed WHO-columns for Set8 changes
23 24-JUL-95 AForte 70.6 Changed tokenised message
24 AMills 'HR_6695_RAC_PER_NOT_VALID' for
25 'HR_7696_RAC_PER_NOT_VALID',
26 'HR_7697_RAC_PER_NOT_VALID'
27 17-NOV-95 JThuringer 70.9 Removed ampersand from change history -
28 this was causing an
29 "expected symbol name is missing" error
30 22-MAR-96 A.Mills 70.10 Altered procedure chk_vacancy_dates to
31 accept and test on p_rec_activity_id.
32 Cursor changed.
33 05-MAR-97 J.Alloun 70.11 Changed all occurances of system.dual
34 to sys.dual for next release requirements.
35 ============================================================================*/
36 --
37 -----------------------------------------------------------------------------
38 -- Name --
39 -- Check_Unique_Name --
40 -- Purpose --
41 -- checks that the recruitment activity name is unique. Called from the --
42 -- client side package ACTIVITY_ITEMS from the procedure 'name'. Called --
43 -- on WHEN-VALIDATE-ITEM from Name. --
44 -- --
45 -----------------------------------------------------------------------------
46 --
47 PROCEDURE Check_Unique_Name(P_Name VARCHAR2,
48 P_Business_group_id NUMBER,
49 P_rowid VARCHAR2) IS
50
51 CURSOR name_exists IS
52 SELECT 1
53 FROM per_recruitment_activities rec
54 WHERE upper(rec.NAME) = upper(p_Name)
55 AND rec.business_group_id + 0 = P_business_group_id
56 AND (P_rowid <> rec.rowid
57 or P_rowid is NULL);
58 v_dummy number;
59 --
60 BEGIN
61 --
62 OPEN name_exists;
63 FETCH name_exists INTO v_dummy;
64 IF name_exists%found THEN
65 CLOSE name_exists;
66 hr_utility.set_message(801, 'HR_6113_RAC_EXISTS');
67 hr_utility.raise_error;
68 ELSe CLOSE name_exists;
69 END IF;
70 END Check_Unique_Name;
71 --
72 -----------------------------------------------------------------------------
73 -- Name --
74 -- Check_references, --
75 -- Purpose --
76 -- checks that deletes cannot take place of a recruitment activity if --
77 -- there are vacancies i.e recruitment_activities_for the recruitment- --
78 -- activity, or if the recruitment activity is being used in an --
79 -- assignment or if the recruitment activity is a parent.
80 -- Arguments --
81 -- See below. --
82 -- Notes --
83 -- --
84
85 -----------------------------------------------------------------------------
86 --
87 PROCEDURE Check_references(P_recruitment_activity_id NUMBER,
88 P_Business_group_id NUMBER) IS
89 --
90 --
91
92 CURSOR csr_asg
93 (
94 P_recruitment_activity_id NUMBER,
95 P_Business_group_id NUMBER
96 ) IS
97 SELECT asg.recruitment_activity_id
98 FROM per_assignments_F asg
99 WHERE asg.recruitment_activity_id = P_recruitment_activity_id
100 AND asg.business_group_id + 0 = P_Business_group_id;
101 --
102 --
103 CURSOR csr_rec_acts
104 (
105 P_recruitment_activity_id NUMBER,
106 P_Business_group_id NUMBER
107 ) IS
108 SELECT acts.recruitment_activity_id
109 FROM per_recruitment_activities acts
110 WHERE acts.parent_recruitment_activity_id = P_recruitment_activity_id
111 AND acts.business_group_id + 0 = P_Business_group_id;
112 --
113 --
114 v_dummy_id number;
115 --
116 --
117
118 BEGIN
119 --
120 OPEN csr_asg(P_recruitment_activity_id,
121 P_Business_group_id);
122 FETCH csr_asg into v_dummy_id;
123 IF csr_asg%found then
124 CLOSE csr_asg;
125 hr_utility.set_message(800,'HR_6682_RAC_RECRUIT_ASG_EXIST');
126 hr_utility.raise_error;
127 ELSE
128 CLOSE csr_asg;
129 END IF;
130 --
131 --
132 OPEN csr_rec_acts(P_recruitment_activity_id,
133 P_Business_group_id);
134 FETCH csr_rec_acts into v_dummy_id;
135 IF csr_rec_acts%found then
136 CLOSE csr_rec_acts;
137 hr_utility.set_message(800,'HR_6111_RAC_RECRUIT_SUB_ACTS');
138 hr_utility.raise_error;
139 ELSE
140 CLOSE csr_rec_acts;
141 END IF;
142 --
143 --
144 END check_References;
145 --
146 -----------------------------------------------------------------------------
147 -- Name --
148 -- chk_org_date --
149 -- Purpose --
150 -- Checks that on update of the Activity Start that the organization is --
151 -- not invalidated. --
152 -- Arguments --
153 -- See below. --
154 -- Notes --
155 -- Called from the client side the WVI for the date_start --
156 -----------------------------------------------------------------------------
157 --
158 --
159 PROCEDURE chk_org_date(P_date_start DATE,
160 P_org_run_by_Id NUMBER,
161 P_Business_Group_id NUMBER) IS
162 --
163 CURSOR csr_org_dates IS
164 SELECT organization_id
165 FROM hr_organization_units
166 WHERE organization_id = P_org_run_by_Id
167 AND business_group_id + 0 = P_Business_Group_id
168 AND date_from <= P_date_start
169 AND ((date_to is null) or
170 (date_to is not null and date_to >= P_date_start));
171 --
172 --
173 v_dummy_id NUMBER;
174 --
175 --
176
177 BEGIN
178 OPEN csr_org_dates;
179 FETCH csr_org_dates into v_dummy_id;
180 IF csr_org_dates%notfound then
181 CLOSE csr_org_dates;
182 hr_utility.set_message(800,'HR_6122_RAC_ORG_NOT_VALID');
183 hr_utility.raise_error;
184 ELSE
185 CLOSE csr_org_dates;
186 END IF;
187 --
188 --
189
190 END chk_org_date;
191 -----------------------------------------------------------------------------
192 -- Name --
193 -- chk_auth_date --
194 -- Purpose --
195 -- Checks that on update of the Activity Start that the authoriser is --
196 -- not invalidated. --
197 -- Arguments --
198 -- See below. --
199 -- Notes --
200 -- Called from the client side the WVI for the date_start --
201 -----------------------------------------------------------------------------
202 --
203 --
204 PROCEDURE chk_auth_date(P_date_start DATE,
205 P_authorising_person_id NUMBER,
206 P_Business_Group_id NUMBER) IS
207 --
208 CURSOR csr_auth_date IS
209 SELECT p.person_id
210 FROM per_people_f p
211 WHERE p.person_id = P_authorising_person_id
212 AND p.current_employee_flag = 'Y'
213 AND (p.business_group_id = P_Business_Group_id OR
214 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
215 AND P_date_start
216 between p.effective_start_date and p.effective_end_date;
217 --
218 --
219 v_dummy_id NUMBER;
220 --
221 --
222
223 BEGIN
224 OPEN csr_auth_date;
225 FETCH csr_auth_date into v_dummy_id;
226 IF csr_auth_date%notfound then
227 CLOSE csr_auth_date;
228 hr_utility.set_message(800,'HR_7697_RAC_PER_NOT_VALID');
229 hr_utility.raise_error;
230 ELSE
231 CLOSE csr_auth_date;
232 END IF;
233 --
234 --
235
236 END chk_auth_date;
237 -----------------------------------------------------------------------------
238 -- Name --
239 -- chk_int_cont_date --
240 -- Purpose --
241 -- Checks that on update of the Activity Start that the internal contact--
242 -- is not invalidated. --
243 -- Arguments --
244 -- See below. --
245 -- Notes --
246 -- Called from the client side the WVI for the date_start --
247 -----------------------------------------------------------------------------
248 --
249 --
250 PROCEDURE chk_int_cont_date(P_date_start DATE,
251 P_internal_contact_person_id NUMBER,
252 P_Business_Group_id NUMBER) IS
253 --
254 CURSOR csr_int_con_date IS
255 SELECT p.person_id
256 FROM per_people_f p
257 WHERE p.person_id = P_internal_contact_person_id
258 AND p.current_employee_flag = 'Y'
259 AND p.employee_number is not null
260 AND (nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y'
261 OR p.business_group_id = P_Business_Group_id)
262 AND P_date_start
263 between p.effective_start_date and p.effective_end_date;
264 --
265 --
266 v_dummy_id NUMBER;
267 --
268 --
269 BEGIN
270 OPEN csr_int_con_date;
271 FETCH csr_int_con_date into v_dummy_id;
272 IF csr_int_con_date%notfound then
273 CLOSE csr_int_con_date;
274 hr_utility.set_message(800,'HR_7696_RAC_PER_NOT_VALID');
275 hr_utility.raise_error;
276 ELSE
277 CLOSE csr_int_con_date;
278 END IF;
279 --
280 --
281 END chk_int_cont_date;
282 -----------------------------------------------------------------------------
283 -- Name --
284 -- chk_parent_dates --
285 -- Purpose --
286 -- Item handler procedure. Supports the validation on Date Start in the --
287 -- ACTIVITY block --
288 -- Arguments --
289 -- See below. --
290 -- Notes --
291 -- --
292 -----------------------------------------------------------------------------
293 PROCEDURE chk_parent_dates(P_date_start DATE,
294 P_Business_Group_id NUMBER,
295 P_parent_rec_id NUMBER) IS
296 --
297 CURSOR csr_par_rec_dates IS
298
299 SELECT recruitment_activity_id
300 FROM per_recruitment_activities
301 WHERE recruitment_activity_id = P_parent_rec_id
302 AND business_group_id + 0 = P_Business_Group_id
303 AND date_start <= P_date_start
304 AND ((date_end is null) or
305 (date_end is not null and date_end >= P_date_start));
306 --
307 --
308 v_dummy_id NUMBER;
309 --
310 --
311 BEGIN
312 OPEN csr_par_rec_dates;
313 FETCH csr_par_rec_dates into v_dummy_id;
314 IF csr_par_rec_dates%notfound then
315 CLOSE csr_par_rec_dates;
316 hr_utility.set_message(800,'HR_6461_RAC_PARENT_RAC_INVALID');
317 hr_utility.raise_error;
318 ELSE
319 CLOSE csr_par_rec_dates;
320 END IF;
321 --
322 --
323 END chk_parent_dates;
324 -----------------------------------------------------------------------------
325 -- Name --
326 -- chk_vacancy_dates --
327 -- Purpose --
328 -- Item handler procedure. Supports the validation on Date Start in the --
329 -- ACTIVITY block --
330 -- Arguments --
331 -- See below. --
332 -- Notes --
333 -- --
334 -----------------------------------------------------------------------------
335 PROCEDURE chk_vacancy_dates(P_date_start DATE,
336 P_Business_Group_id NUMBER,
337 P_rec_activity_id NUMBER) IS
338 --
339 CURSOR csr_vac_dates IS
340 SELECT raf.vacancy_id
341 FROM per_vacancies v,
342 per_recruitment_activity_for raf
343 WHERE raf.recruitment_activity_id = P_rec_activity_id
344 AND v.business_group_id + 0 = P_Business_Group_id
345 AND raf.vacancy_id = v.vacancy_id
346 AND (v.date_from > P_date_start
347 OR (v.date_to is not null and
348 v.date_to < P_date_start));
349 --
350 --
351 v_dummy_id NUMBER;
352 --
353 --
354 BEGIN
355 OPEN csr_vac_dates;
356 FETCH csr_vac_dates into v_dummy_id;
360 hr_utility.raise_error;
357 IF csr_vac_dates%found then
358 CLOSE csr_vac_dates;
359 hr_utility.set_message(800,'HR_6121_RAC_VACANCY_NOT_VALID');
361 ELSE
362 CLOSE csr_vac_dates;
363 END IF;
364 --
365 --
366 END chk_vacancy_dates;
367 -----------------------------------------------------------------------------
368 -- Name --
369 -- chk_child_rec_dates --
370 -- Purpose --
371 -- Item handler procedure. Supports the validation on Date Start in the --
372 -- ACTIVITY block --
373 -- Arguments --
374 -- See below. --
375 -- Notes --
376 -- --
377 -----------------------------------------------------------------------------
378 PROCEDURE chk_child_rec_dates(P_date_start DATE,
379 P_Business_Group_id NUMBER,
380 P_rec_act_id NUMBER) IS
381 --
382 CURSOR csr_chk_exist_child IS
383 SELECT 1
384 FROM per_recruitment_activities
385 WHERE parent_recruitment_activity_id = P_rec_act_id
386 AND business_group_id + 0 = P_Business_Group_id;
387 --
388
389 CURSOR csr_child_dates IS
390 SELECT parent_recruitment_activity_id
391 FROM per_recruitment_activities
392 WHERE parent_recruitment_activity_id = P_rec_act_id
393 AND business_group_id + 0 = P_Business_Group_id
394 AND P_date_start > date_start;
395 --
396 --
397 v_dummy_id NUMBER;
398 --
399 --
400 BEGIN
401 v_dummy_id := null;
402
403 OPEN csr_chk_exist_child;
404 FETCH csr_chk_exist_child into v_dummy_id;
405 IF csr_chk_exist_child%found then
406 v_dummy_id := null;
407 CLOSE csr_chk_exist_child;
408 OPEN csr_child_dates;
409 FETCH csr_child_dates into v_dummy_id;
410 IF csr_child_dates%found then
411 CLOSE csr_child_dates;
412 hr_utility.set_message(800,'HR_6621_RAC_CHILD_BEFORE_RAC');
413 hr_utility.raise_error;
414 ELSE
415 CLOSE csr_child_dates;
416 END IF;
417 END IF;
418 --
419 --
420 END chk_child_rec_dates;
421 -----------------------------------------------------------------------------
422 -- Name --
423 -- chk_child_end_dates --
424 -- Purpose --
425 -- Item handler procedure. Supports the validation on Date Start in the --
426 -- ACTIVITY block --
427 -- Arguments --
428 -- See below. --
429 -- Notes --
430 -- --
431 -----------------------------------------------------------------------------
432 PROCEDURE chk_child_end_dates(P_date_end DATE,
433 P_Business_Group_id NUMBER,
434 P_rec_act_id NUMBER) IS
435 --
436 CURSOR csr_chk_exist_child IS
437 SELECT 1
438 FROM per_recruitment_activities
439 WHERE parent_recruitment_activity_id = P_rec_act_id
440 AND business_group_id + 0 = P_Business_Group_id;
441 --
442
443 CURSOR csr_child_dates IS
444 SELECT parent_recruitment_activity_id
445 FROM per_recruitment_activities
446 WHERE parent_recruitment_activity_id = P_rec_act_id
447 AND business_group_id + 0 = P_Business_Group_id
448 AND P_date_end < date_start;
449 --
450 --
451 v_dummy_id NUMBER;
452 --
453 --
454 BEGIN
455 v_dummy_id := null;
456
457 OPEN csr_chk_exist_child;
458 FETCH csr_chk_exist_child into v_dummy_id;
459 IF csr_chk_exist_child%found then
460 v_dummy_id := null;
461 CLOSE csr_chk_exist_child;
462 OPEN csr_child_dates;
463 FETCH csr_child_dates into v_dummy_id;
464 IF csr_child_dates%found then
465 CLOSE csr_child_dates;
466 hr_utility.set_message(800,'HR_6622_RAC_CHILD_AFTER_RAC');
467 hr_utility.raise_error;
468 ELSE
469 CLOSE csr_child_dates;
470 END IF;
471 END IF;
472 --
473 --
474 END chk_child_end_dates;
478 -- Purpose --
475 -----------------------------------------------------------------------------
476 -- Name --
477 -- default_currency_code
479 -- to find the currency code for the Business Group of the Recrutiment --
480 -- activity.
481 -- Arguments --
482 -- See below. --
483 -- Notes --
484 -- --
485 -----------------------------------------------------------------------------
486 --
487 FUNCTION default_currency_code(P_Business_Group_id NUMBER) return VARCHAR2 IS
488 --
489 CURSOR csr_currency IS
490 SELECT currency_code
491 FROM per_business_groups
492 WHERE business_group_id = P_Business_Group_Id;
493 --
494 v_default_currency VARCHAR2(100);
495 --
496 BEGIN
497 v_default_currency := null;
498 OPEN csr_currency;
499 FETCH csr_currency into v_default_currency;
500 CLOSE csr_currency;
501 RETURN v_default_currency;
502 --
503 END default_currency_code;
504 --
505 -----------------------------------------------------------------------------
506 -- Name --
507 -- Insert_Row --
508 -- Purpose --
509 -- Table handler procedure. Supports the insert of an ACTIVITY via the --
510 -- Define Recruitment Activity form. --
511 -- Arguments --
512 -- See below. --
513 -- Notes --
514 -- --
515 -----------------------------------------------------------------------------
516 --
517
518 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
519 X_Recruitment_Activity_Id IN OUT NOCOPY NUMBER,
520 X_Business_Group_Id NUMBER,
521 X_Authorising_Person_Id NUMBER,
522 X_Run_By_Organization_Id NUMBER,
523 X_Internal_Contact_Person_Id NUMBER,
524 X_Parent_Rec_Activity_Id NUMBER,
525 X_Currency_Code VARCHAR2,
526 X_Date_Start DATE,
527 X_Name VARCHAR2,
528 X_Actual_Cost VARCHAR2,
529 X_Comments varchar2,
530 X_Contact_Telephone_Number VARCHAR2,
531 X_Date_Closing DATE,
532 X_Date_End DATE,
533 X_External_Contact VARCHAR2,
534 X_Planned_Cost VARCHAR2,
535 X_Type VARCHAR2,
536 X_Attribute_Category VARCHAR2,
537 X_Attribute1 VARCHAR2,
538 X_Attribute2 VARCHAR2,
539 X_Attribute3 VARCHAR2,
540 X_Attribute4 VARCHAR2,
541 X_Attribute5 VARCHAR2,
542 X_Attribute6 VARCHAR2,
543 X_Attribute7 VARCHAR2,
544 X_Attribute8 VARCHAR2,
545 X_Attribute9 VARCHAR2,
546 X_Attribute10 VARCHAR2,
547 X_Attribute11 VARCHAR2,
548 X_Attribute12 VARCHAR2,
549 X_Attribute13 VARCHAR2,
550 X_Attribute14 VARCHAR2,
551 X_Attribute15 VARCHAR2,
552 X_Attribute16 VARCHAR2,
553 X_Attribute17 VARCHAR2,
554 X_Attribute18 VARCHAR2,
555 X_Attribute19 VARCHAR2,
556 X_Attribute20 VARCHAR2
557 ) IS
558 CURSOR C IS
559 SELECT rowid
560 FROM PER_RECRUITMENT_ACTIVITIES
561 WHERE recruitment_activity_id = X_Recruitment_Activity_Id;
562
563
564
565 CURSOR C2 IS
566 SELECT per_recruitment_activities_s.nextval
567 FROM sys.dual;
568
569 BEGIN
570 IF (X_Recruitment_Activity_Id is NULL) then
571 OPEN C2;
572 FETCH C2 INTO X_Recruitment_Activity_Id;
573 CLOSE C2;
574 end IF;
578 authorising_person_id,
575 INSERT INTO PER_RECRUITMENT_ACTIVITIES(
576 recruitment_activity_id,
577 business_group_id,
579 run_by_organization_id,
580 internal_contact_person_id,
581 parent_recruitment_activity_id,
582 currency_code,
583 date_start,
584 name,
585 actual_cost,
586 comments,
587 contact_telephone_number,
588 date_closing,
589 date_end,
590 external_contact,
591 planned_cost,
592 type,
593 attribute_category,
594 attribute1,
595 attribute2,
596 attribute3,
597 attribute4,
598 attribute5,
599 attribute6,
600 attribute7,
601 attribute8,
602 attribute9,
603 attribute10,
604 attribute11,
605 attribute12,
606 attribute13,
607 attribute14,
608 attribute15,
609 attribute16,
610 attribute17,
611 attribute18,
612 attribute19,
613 attribute20
614 ) VALUES (
615 X_Recruitment_Activity_Id,
616 X_Business_Group_Id,
617 X_Authorising_Person_Id,
618 X_Run_By_Organization_Id,
619 X_Internal_Contact_Person_Id,
620 X_Parent_Rec_Activity_Id,
621 X_Currency_Code,
622 X_Date_Start,
623 X_Name,
624 X_Actual_Cost,
625 X_Comments,
626 X_Contact_Telephone_Number,
627 X_Date_Closing,
628 X_Date_End,
629 X_External_Contact,
630 X_Planned_Cost,
631 X_Type,
632 X_Attribute_Category,
633 X_Attribute1,
634 X_Attribute2,
635 X_Attribute3,
636 X_Attribute4,
637 X_Attribute5,
638 X_Attribute6,
639 X_Attribute7,
640 X_Attribute8,
641 X_Attribute9,
642 X_Attribute10,
643 X_Attribute11,
644 X_Attribute12,
645 X_Attribute13,
646 X_Attribute14,
647 X_Attribute15,
648 X_Attribute16,
649 X_Attribute17,
650 X_Attribute18,
651 X_Attribute19,
652 X_Attribute20);
653
654 OPEN C;
655 FETCH C INTO X_Rowid;
656 IF (C%NOTFOUND) then
657 CLOSE C;
658 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
659 hr_utility.set_message_token('PROCEDURE',
660 'per_recruitment_activity_pkg.insert_row');
661 hr_utility.set_message_token('STEP','1');
662 hr_utility.raise_error;
663 end IF;
664 CLOSE C;
665
666 END Insert_Row;
667 --
668 -----------------------------------------------------------------------------
669 -- Name --
670 -- Lock_Row --
671 -- Purpose --
672 -- Table handler procedure that supports the insert , update and delete --
673 -- of an activity by applying a lock on an activity in the Define --
674 -- Recruitment Activity form. --
675 -- Arguments --
676 -- Notes --
677 -- None. --
678 -----------------------------------------------------------------------------
679 --
680 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
681
682 X_Recruitment_Activity_Id NUMBER,
683 X_Business_Group_Id NUMBER,
684 X_Authorising_Person_Id NUMBER,
685 X_Run_By_Organization_Id NUMBER,
686 X_Internal_Contact_Person_Id NUMBER,
687 X_Parent_Rec_Activity_Id NUMBER,
688 X_Currency_Code VARCHAR2,
689 X_Date_Start DATE,
690 X_Name VARCHAR2,
691 X_Actual_Cost VARCHAR2,
692 X_Comments varchar2,
693 X_Contact_Telephone_Number VARCHAR2,
694 X_Date_Closing DATE,
695 X_Date_End DATE,
696 X_External_Contact VARCHAR2,
697 X_Planned_Cost VARCHAR2,
698 X_Type VARCHAR2,
699 X_Attribute_Category VARCHAR2,
700 X_Attribute1 VARCHAR2,
701 X_Attribute2 VARCHAR2,
702 X_Attribute3 VARCHAR2,
703 X_Attribute4 VARCHAR2,
704 X_Attribute5 VARCHAR2,
708 X_Attribute9 VARCHAR2,
705 X_Attribute6 VARCHAR2,
706 X_Attribute7 VARCHAR2,
707 X_Attribute8 VARCHAR2,
709 X_Attribute10 VARCHAR2,
710 X_Attribute11 VARCHAR2,
711 X_Attribute12 VARCHAR2,
712 X_Attribute13 VARCHAR2,
713 X_Attribute14 VARCHAR2,
714 X_Attribute15 VARCHAR2,
715 X_Attribute16 VARCHAR2,
716 X_Attribute17 VARCHAR2,
717 X_Attribute18 VARCHAR2,
718 X_Attribute19 VARCHAR2,
719 X_Attribute20 VARCHAR2
720 ) IS
721 CURSOR C IS
722 SELECT *
723 FROM PER_RECRUITMENT_ACTIVITIES
724 WHERE rowid = X_Rowid
725 FOR UPDATE of Recruitment_Activity_Id NOWAIT;
726 Recinfo C%ROWTYPE;
727 BEGIN
728 OPEN C;
729 FETCH C INTO Recinfo;
730 IF (C%NOTFOUND) then
731 CLOSE C;
732 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
733 hr_utility.set_message_token('PROCEDURE',
734 'per_recruitment_activity_pkg.lock_row');
735 hr_utility.set_message_token('STEP','1');
736 hr_utility.raise_error;
737 end IF;
738 CLOSE C;
739 --
740 -- Change needed to remove trailing spaces.
741 --
742 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
743 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
744 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
745 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
746 Recinfo.currency_code := rtrim(Recinfo.currency_code);
747 Recinfo.name := rtrim(Recinfo.name);
748 Recinfo.actual_cost := rtrim(Recinfo.actual_cost);
749 Recinfo.comments := rtrim(Recinfo.comments);
750 Recinfo.contact_telephone_number := rtrim(Recinfo.contact_telephone_number);
751 Recinfo.external_contact := rtrim(Recinfo.external_contact);
752 Recinfo.planned_cost := rtrim(Recinfo.planned_cost);
753 Recinfo.type := rtrim(Recinfo.type);
754 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
755 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
756 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
757 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
758 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
759 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
760 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
761 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
762 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
763 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
764 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
765 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
766 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
767 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
768 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
769 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
770 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
771 --
772 IF (
773 ( (Recinfo.recruitment_activity_id = X_Recruitment_Activity_Id)
774 OR ( (Recinfo.recruitment_activity_id IS NULL)
775 AND (X_Recruitment_Activity_Id IS NULL)))
776 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
777 OR ( (Recinfo.business_group_id IS NULL)
778 AND (X_Business_Group_Id IS NULL)))
779 AND ( (Recinfo.authorising_person_id = X_Authorising_Person_Id)
780 OR ( (Recinfo.authorising_person_id IS NULL)
781 AND (X_Authorising_Person_Id IS NULL)))
782 AND ( (Recinfo.run_by_organization_id = X_Run_By_Organization_Id)
783 OR ( (Recinfo.run_by_organization_id IS NULL)
784 AND (X_Run_By_Organization_Id IS NULL)))
785 AND ( (Recinfo.internal_contact_person_id = X_Internal_Contact_Person_Id)
786 OR ( (Recinfo.internal_contact_person_id IS NULL)
787 AND (X_Internal_Contact_Person_Id IS NULL)))
788 AND ( (Recinfo.parent_recruitment_activity_id =
789 X_Parent_Rec_Activity_Id)
790 OR ( (Recinfo.parent_recruitment_activity_id IS NULL)
791 AND (X_Parent_Rec_Activity_Id IS NULL)))
792 AND ( (Recinfo.currency_code = X_Currency_Code)
793 OR ( (Recinfo.currency_code IS NULL)
794 AND (X_Currency_Code IS NULL)))
795 AND ( (Recinfo.date_start = X_Date_Start)
796 OR ( (Recinfo.date_start IS NULL)
797 AND (X_Date_Start IS NULL)))
798 AND ( (Recinfo.name = X_Name)
799 OR ( (Recinfo.name IS NULL)
800 AND (X_Name IS NULL)))
801 AND ( (Recinfo.actual_cost = X_Actual_Cost)
802 OR ( (Recinfo.actual_cost IS NULL)
803 AND (X_Actual_Cost IS NULL)))
804 AND ( (Recinfo.comments = X_Comments)
805 OR ( (Recinfo.comments IS NULL)
806 AND (X_Comments IS NULL)))
807 AND ( (Recinfo.contact_telephone_number = X_Contact_Telephone_Number)
808 OR ( (Recinfo.contact_telephone_number IS NULL)
809 AND (X_Contact_Telephone_Number IS NULL)))
813 AND ( (Recinfo.date_end = X_Date_End)
810 AND ( (Recinfo.date_closing = X_Date_Closing)
811 OR ( (Recinfo.date_closing IS NULL)
812 AND (X_Date_Closing IS NULL)))
814 OR ( (Recinfo.date_end IS NULL)
815 AND (X_Date_End IS NULL)))
816 AND ( (Recinfo.external_contact = X_External_Contact)
817 OR ( (Recinfo.external_contact IS NULL)
818 AND (X_External_Contact IS NULL)))
819 AND ( (Recinfo.planned_cost = X_Planned_Cost)
820 OR ( (Recinfo.planned_cost IS NULL)
821 AND (X_Planned_Cost IS NULL)))
822 AND ( (Recinfo.type = X_Type)
823 OR ( (Recinfo.type IS NULL)
824 AND (X_Type IS NULL)))
825 AND ( (Recinfo.attribute_category = X_Attribute_Category)
826 OR ( (Recinfo.attribute_category IS NULL)
827 AND (X_Attribute_Category IS NULL)))
828 AND ( (Recinfo.attribute1 = X_Attribute1)
829 OR ( (Recinfo.attribute1 IS NULL)
830 AND (X_Attribute1 IS NULL)))
831 AND ( (Recinfo.attribute2 = X_Attribute2)
832 OR ( (Recinfo.attribute2 IS NULL)
833 AND (X_Attribute2 IS NULL)))
834 AND ( (Recinfo.attribute3 = X_Attribute3)
835 OR ( (Recinfo.attribute3 IS NULL)
836 AND (X_Attribute3 IS NULL)))
837 AND ( (Recinfo.attribute4 = X_Attribute4)
838 OR ( (Recinfo.attribute4 IS NULL)
839 AND (X_Attribute4 IS NULL)))
840 AND ( (Recinfo.attribute5 = X_Attribute5)
841 OR ( (Recinfo.attribute5 IS NULL)
842 AND (X_Attribute5 IS NULL)))
843 AND ( (Recinfo.attribute6 = X_Attribute6)
844 OR ( (Recinfo.attribute6 IS NULL)
845 AND (X_Attribute6 IS NULL)))
846 AND ( (Recinfo.attribute7 = X_Attribute7)
847 OR ( (Recinfo.attribute7 IS NULL)
848 AND (X_Attribute7 IS NULL)))
849 AND ( (Recinfo.attribute8 = X_Attribute8)
850 OR ( (Recinfo.attribute8 IS NULL)
851 AND (X_Attribute8 IS NULL)))
852 AND ( (Recinfo.attribute9 = X_Attribute9)
853 OR ( (Recinfo.attribute9 IS NULL)
854 AND (X_Attribute9 IS NULL)))
855 AND ( (Recinfo.attribute10 = X_Attribute10)
856 OR ( (Recinfo.attribute10 IS NULL)
857 AND (X_Attribute10 IS NULL)))
858 AND ( (Recinfo.attribute11 = X_Attribute11)
859 OR ( (Recinfo.attribute11 IS NULL)
860 AND (X_Attribute11 IS NULL)))
861 AND ( (Recinfo.attribute12 = X_Attribute12)
862 OR ( (Recinfo.attribute12 IS NULL)
863 AND (X_Attribute12 IS NULL)))
864 AND ( (Recinfo.attribute13 = X_Attribute13)
865 OR ( (Recinfo.attribute13 IS NULL)
866 AND (X_Attribute13 IS NULL)))
867 AND ( (Recinfo.attribute14 = X_Attribute14)
868 OR ( (Recinfo.attribute14 IS NULL)
869 AND (X_Attribute14 IS NULL)))
870 AND ( (Recinfo.attribute15 = X_Attribute15)
871 OR ( (Recinfo.attribute15 IS NULL)
872 AND (X_Attribute15 IS NULL)))
873 AND ( (Recinfo.attribute16 = X_Attribute16)
874 OR ( (Recinfo.attribute16 IS NULL)
875 AND (X_Attribute16 IS NULL)))
876 AND ( (Recinfo.attribute17 = X_Attribute17)
877 OR ( (Recinfo.attribute17 IS NULL)
878 AND (X_Attribute17 IS NULL)))
879 AND ( (Recinfo.attribute18 = X_Attribute18)
880 OR ( (Recinfo.attribute18 IS NULL)
881 AND (X_Attribute18 IS NULL)))
882 AND ( (Recinfo.attribute19 = X_Attribute19)
883 OR ( (Recinfo.attribute19 IS NULL)
884 AND (X_Attribute19 IS NULL)))
885 AND ( (Recinfo.attribute20 = X_Attribute20)
886 OR ( (Recinfo.attribute20 IS NULL)
887 AND (X_Attribute20 IS NULL)))
888 ) then
889 return;
890 else
891 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
892 APP_EXCEPTION.RAISE_EXCEPTION;
893 end IF;
894
895 END Lock_Row;
896 --
897 -----------------------------------------------------------------------------
898 -- Name --
899 -- Update_Row --
900 -- Purpose --
901 -- Table handler procedure that supports the update of an ACTIVITY via --
902 -- Define Recruitment Activity form. --
903 -- Arguments --
904 -- See below. --
905 -- Notes --
906 -- None. --
907 -----------------------------------------------------------------------------
908 --
909 PROCEDURE Update_Row(X_Rowid VARCHAR2,
910 X_Recruitment_Activity_Id NUMBER,
911 X_Business_Group_Id NUMBER,
912 X_Authorising_Person_Id NUMBER,
913 X_Run_By_Organization_Id NUMBER,
914 X_Internal_Contact_Person_Id NUMBER,
915 X_Parent_Rec_Activity_Id NUMBER,
919 X_Actual_Cost VARCHAR2,
916 X_Currency_Code VARCHAR2,
917 X_Date_Start DATE,
918 X_Name VARCHAR2,
920 X_Comments varchar2,
921 X_Contact_Telephone_Number VARCHAR2,
922 X_Date_Closing DATE,
923 X_Date_End DATE,
924 X_External_Contact VARCHAR2,
925 X_Planned_Cost VARCHAR2,
926 X_Type VARCHAR2,
927 X_Attribute_Category VARCHAR2,
928 X_Attribute1 VARCHAR2,
929 X_Attribute2 VARCHAR2,
930 X_Attribute3 VARCHAR2,
931 X_Attribute4 VARCHAR2,
932 X_Attribute5 VARCHAR2,
933 X_Attribute6 VARCHAR2,
934 X_Attribute7 VARCHAR2,
935 X_Attribute8 VARCHAR2,
936 X_Attribute9 VARCHAR2,
937 X_Attribute10 VARCHAR2,
938 X_Attribute11 VARCHAR2,
939 X_Attribute12 VARCHAR2,
940 X_Attribute13 VARCHAR2,
941 X_Attribute14 VARCHAR2,
942 X_Attribute15 VARCHAR2,
943 X_Attribute16 VARCHAR2,
944 X_Attribute17 VARCHAR2,
945 X_Attribute18 VARCHAR2,
946 X_Attribute19 VARCHAR2,
947 X_Attribute20 VARCHAR2
948 ) IS
949 BEGIN
950 UPDATE PER_RECRUITMENT_ACTIVITIES
951 SET
952
953 recruitment_activity_id = X_Recruitment_Activity_Id,
954 business_group_id = X_Business_Group_Id,
955 authorising_person_id = X_Authorising_Person_Id,
956 run_by_organization_id = X_Run_By_Organization_Id,
957 internal_contact_person_id = X_Internal_Contact_Person_Id,
958 parent_recruitment_activity_id = X_Parent_Rec_Activity_Id,
959 currency_code = X_Currency_Code,
960 date_start = X_Date_Start,
961 name = X_Name,
962 actual_cost = X_Actual_Cost,
963 comments = X_Comments,
964 contact_telephone_number = X_Contact_Telephone_Number,
965 date_closing = X_Date_Closing,
966 date_end = X_Date_End,
967 external_contact = X_External_Contact,
968 planned_cost = X_Planned_Cost,
969 type = X_Type,
970 attribute_category = X_Attribute_Category,
971 attribute1 = X_Attribute1,
972 attribute2 = X_Attribute2,
973 attribute3 = X_Attribute3,
974 attribute4 = X_Attribute4,
975 attribute5 = X_Attribute5,
976 attribute6 = X_Attribute6,
977 attribute7 = X_Attribute7,
978 attribute8 = X_Attribute8,
979 attribute9 = X_Attribute9,
980 attribute10 = X_Attribute10,
981 attribute11 = X_Attribute11,
982 attribute12 = X_Attribute12,
983 attribute13 = X_Attribute13,
984 attribute14 = X_Attribute14,
985 attribute15 = X_Attribute15,
986 attribute16 = X_Attribute16,
987 attribute17 = X_Attribute17,
988 attribute18 = X_Attribute18,
989 attribute19 = X_Attribute19,
990 attribute20 = X_Attribute20
991 WHERE rowid = X_rowid;
992
993 IF (SQL%NOTFOUND) then
994 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
995 hr_utility.set_message_token('PROCEDURE',
996 'per_recruitment_activities_pkg.update_row');
997 hr_utility.set_message_token('STEP','1');
998 hr_utility.raise_error;
999 end IF;
1000
1001 END Update_Row;
1002 --
1003 -----------------------------------------------------------------------------
1004 -- Name --
1005 -- Delete_Row --
1006 -- Purpose --
1007 -- Table handler procedure that supports the delete of an ACTIVITY via --
1008 -- the Define Recruitment Activity form. --
1009 -- Arguments --
1010 -- See below. --
1011 -- Notes --
1012 -- --
1013 -----------------------------------------------------------------------------
1014 --
1015 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
1016 BEGIN
1017 DELETE FROM PER_RECRUITMENT_ACTIVITIES
1018 WHERE rowid = X_Rowid;
1019
1020 IF (SQL%NOTFOUND) then
1021 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1022 hr_utility.set_message_token('PROCEDURE',
1023 'per_recruitment_activites_pkg.delete_row');
1024 hr_utility.set_message_token('STEP','1');
1025 hr_utility.raise_error;
1026 end IF;
1027 END Delete_Row;
1028
1029 END PER_RECRUITMENT_ACTIVITIES_PKG;