[Home] [Help]
PACKAGE BODY: APPS.PER_VACANCIES_PKG
Source
1 PACKAGE BODY PER_VACANCIES_PKG as
2 /* $Header: pevac01t.pkb 120.2.12000000.2 2007/02/13 09:36:27 sidsaxen ship $ */
3 /* +=======================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 Name
9 per_vacancies_pkg
10 Purpose
11 Supports the VACANCY block in the form PERWSVAC (Define Requistion and
12 Vacancy).
13 Notes
14 This package also contains one function which returns values for the
15 FOLDER block of the View Vacancies form. The Function is B_Counter.
16
17 History
18 13-APR-94 H.Minton 70.0 Date created.
19
20 23-MAY-94 H.Minton 70.1 Added new functions for View Vacancies
21 - PERWILVA, folder form.
22
23 28-JUN-94 D.Kerr 70.2 Fixed ref_int checks.
24
25 ?? A.Roussel 70.3 Removed Rems for 10G Install
26
27 25-AUG-94 H.Minton 70.4 Bug 824 - amended date format on csr
28 current folder.
29
30 70.5 Amended header info.
31
32 23-NOV-94 RFine 70.6 Suppressed index on business_group_id
33
34 19-JAN-95 D.Kerr 70.8 Removed WHO- columns
35
36 17-MAY-95 D.Kerr 70.9 1. Fixed usage check in
37 D_from_updt_rec_act_chk
38 Removed unncessary business group
39 parameter from this procedure.
40 2. Added check to per_assignments
41 to check_references and call
42 this procedure from delete_row.
43
44 05-MAR-97 J.Alloun 70.10 Changed all occurances of system.dual
45 to sys.dual for next release requirements.
46
47 26-JAN-98 I.Harding 110.2 Added vacancy_category parameter to
48 insert, update and lock procs.
49
50 22-APR-98 D.Kerr 110.3 658840: removed date conversions in
51 csr_current in folder_current.
52
53 25-FEB-98 B.Goodsell 115.2 Added Budget Measurement columns to
54 Table Handler procedures
55 21-MAY-99 C.Carter 115.3 Removed set_token call after error
56 message 6125.
57 05-Oct-99 SCNair 115.4 Date Track position related changes
58 12-Jun-00 hsajja 115.5 Changed HR_POSITIONS to HR_POSITIONS_F
59 and corresponding effective_date changes
60 26-Jun-00 C.Carter 115.6 Changed PER_JOBS to PER_JOBS_V.
61 07-SEP-01 A.Cowan 115.7-10 Cascade vacancy enhancement
62 bug # 1923803
63 26-Jun-03 vanantha 115.12 Added a procedure D_to_updt_org_chk
64 to validate end date for Vacancy
65 with respect to Org end date.
66 06-Jul-05 ghshanka 115.14 modified the cursor def in the procedure
67 D_from_updt_person so that it can
68 validate the cross business group
69 profile option also.
70 03-JUL-06 avarri 115.15 Modiifed the procedure Check_Unique_Name
71 for 4262036.
72 ============================================================================*/
73 ----------------------------------------------------------------------------
74 --
75 -- Name --
76 -- Check_References --
77 -- Purpose --
78 -- To ensure the referential integrity when a vacancy is deleted from the--
79 -- Define Requisition and Vacancy form. --
80 -- Checks that the vacancy is not used in a recruitment activity
81 -- or by an assignment.
82 -----------------------------------------------------------------------------
83 --
84 PROCEDURE Check_References(P_vacancy_id NUMBER ) is
85 CURSOR c_check_references1 IS
86 SELECT distinct(PV.NAME)
87 FROM PER_VACANCIES PV,
88 PER_ALL_ASSIGNMENTS_F PAF
89 WHERE PAF.VACANCY_ID = P_vacancy_id
90 AND PV.VACANCY_ID = P_vacancy_id
91 AND PAF.VACANCY_ID = PV.VACANCY_ID;
92
93 CURSOR c_check_ref_2 IS
94 SELECT PV.NAME
95 FROM PER_VACANCIES PV
96 , PER_RECRUITMENT_ACTIVITY_FOR PRAF
97 WHERE PRAF.VACANCY_ID = P_vacancy_id
98 AND PV.VACANCY_ID = P_vacancy_id ;
99
100 --
101 V_name VARCHAR2(30);
102 --
103
104 BEGIN
105 --
106 OPEN c_check_references1;
107 FETCH c_check_references1 into V_name;
108 IF c_check_references1%FOUND THEN
109 CLOSE c_check_references1;
110 fnd_message.set_name('PER','HR_6125_REQS_VACS_DEL_ASSIGN');
111 hr_utility.raise_error;
112 ELSE CLOSE c_check_references1;
113 END IF;
114 --
115 OPEN c_check_ref_2;
116 FETCH c_check_ref_2 into V_name;
117 IF c_check_ref_2%FOUND THEN
118 CLOSE c_check_ref_2;
119 fnd_message.set_name('PER','HR_6126_REQS_VACS_DEL_REC_ACTY');
120 fnd_message.set_token('VACANCY_NAME',V_name);
121 hr_utility.raise_error;
122 ELSE CLOSE c_check_ref_2;
123 END IF;
124 --
125 END Check_References;
126 ----------------------------------------------------------------------------
127 --
128 -- Name --
129 -- B_counter --
130 -- Purpose --
131 -- The purpose of this function is to return the values for the FOLDER
132 -- block of the forms VIEW VACANCIES.
133 -----------------------------------------------------------------------------
134 FUNCTION B_counter(P_Business_group_id NUMBER,
135 P_vacancy_id NUMBER,
136 P_legislation_code VARCHAR2,
137 P_vac_type VARCHAR2) return NUMBER IS
138
139 CURSOR csr_counter IS
140 SELECT COUNT(distinct ass.assignment_id)
141 FROM PER_ALL_ASSIGNMENTS ASS,
142 PER_ASSIGNMENT_STATUS_TYPES a
143 where nvl(A.BUSINESS_GROUP_ID,P_Business_group_id) =
144 P_Business_group_id
145 and ass.business_group_id + 0 = P_Business_group_id
146 and ass.ASSIGNMENT_TYPE = 'A'
147 and ass.ASSIGNMENT_STATUS_TYPE_ID = A.ASSIGNMENT_STATUS_TYPE_ID
148 and nvl(a.LEGISLATION_CODE,P_legislation_code) =
149 P_legislation_code
150 and A.PER_SYSTEM_STATUS = P_vac_type
151 and ass.vacancy_id = P_vacancy_id;
152
153 --
154 v_number_of_asgs NUMBER(15);
155 --
156 BEGIN
157 OPEN csr_counter;
158 FETCH csr_counter into v_number_of_asgs;
159 CLOSE csr_counter;
160 RETURN v_number_of_asgs;
161 END B_counter;
162 ----------------------------------------------------------------------------
163 --
164 -- Name --
165 -- folder_hires --
166 -- Purpose --
167 -- the purpose of this function is to return the number of applicants who
168 -- have been hired as employees as a result of being hired into a vacancy.
169 -- This function is used by the folder form PERWILVA - View Vacancies.
170 -----------------------------------------------------------------------------
171 FUNCTION folder_hires(P_Business_group_id NUMBER,
172 P_vacancy_id NUMBER
173 ) return NUMBER IS
174
175 CURSOR csr_hires IS
176 SELECT COUNT(*)
177 FROM PER_ALL_ASSIGNMENTS A
178 WHERE A.business_group_id + 0 = P_Business_group_id
179 AND A.VACANCY_ID = P_vacancy_id
180 AND A.ASSIGNMENT_TYPE = 'E';
181
182 --
183 v_vac_hires NUMBER(15);
184 --
185 BEGIN
186 OPEN csr_hires;
187 FETCH csr_hires into v_vac_hires;
188 CLOSE csr_hires;
189 RETURN v_vac_hires;
190 END folder_hires;
191
192 ----------------------------------------------------------------------------
193 -- Name --
194 -- folder_current --
195 -- Purpose --
196 -- the purpose of this function is to return the number of current openings
197 -- for the vacancy as of the session date i.e it is the initial number of
198 -- openings for the vacancy as when the vacancy was defined minus the
199 -- number of applicants who have been hired into the vacancy.
200 -----------------------------------------------------------------------------
201 FUNCTION folder_current(P_Business_group_id NUMBER,
202 P_vacancy_id NUMBER,
203 P_session_date DATE
204 ) return NUMBER IS
205
206 CURSOR csr_current IS
207 SELECT COUNT(DISTINCT A.ASSIGNMENT_ID)
208 FROM PER_ALL_ASSIGNMENTS A
209 WHERE A.VACANCY_ID = P_vacancy_id
210 AND A.business_group_id + 0 = P_Business_group_id
211 AND A.ASSIGNMENT_TYPE = 'E'
212 AND A.EFFECTIVE_START_DATE <= P_session_date ;
213
214
215 --
216 v_vac_current NUMBER(15);
217 --
218 BEGIN
219 OPEN csr_current;
220 FETCH csr_current into v_vac_current;
221 CLOSE csr_current;
222 RETURN v_vac_current;
223 END folder_current;
224 --
225 -----------------------------------------------------------------------------
226 -- Name --
227 -- Chk_appl_exists --
228 -- Purpose --
229 -- Verify the effective date, you cannot change the effective date of --
230 -- this vacancy to a future date as applications exist within the vacancy--
231 -- availability period. --
232 -- Called from WHEN-VALIDATE-ITEM in the vacancy block. --
233 -- --
234 -----------------------------------------------------------------------------
235 --
236 procedure chk_appl_exists (P_vacancy_id NUMBER,
237 P_vac_date_from DATE,
238 P_vac_date_to DATE,
239 P_end_of_time DATE
240 )
241 is
242 cursor csr_appl_exists
243 is
244 select '1'
245 from per_all_assignments_f
246 where vacancy_id =P_vacancy_id
247 and effective_start_date < P_vac_date_from
248 and assignment_type = 'A';
249
250 l_flag varchar2(1);
251
252 begin
253
254 open csr_appl_exists ;
255
256 fetch csr_appl_exists into l_flag;
257
258 if csr_appl_exists%found then
259 close csr_appl_exists;
260 fnd_message.set_name('PER','HR_449819_VACS_APL_ACTS');
261 hr_utility.raise_error;
262 else
263 close csr_appl_exists;
264 end if;
265
266 end chk_appl_exists;
267 ----------------------------------------------------------------------------_
268 -- Name --
269 -- Check_Unique_Name --
270 -- Purpose --
271 -- checks that the vacancy name is unique within the requisition. --
272 -- Called from the client side package VACANCY_ITEMS from the procedure --
273 -----------------------------------------------------------------------------
274 --
275 -- Modified for 4262036.
276 PROCEDURE Check_Unique_Name(P_Name VARCHAR2,
277 P_business_group_id NUMBER,
278 P_rowid VARCHAR2) IS
279
280 CURSOR name_exists IS
281 SELECT v.name
282 FROM PER_ALL_VACANCIES v
283 WHERE v.NAME = P_Name
284 AND v.business_group_id = P_business_group_id
285 AND (P_rowid <> v.rowid
286 or P_rowid is NULL);
287 v_req_name VARCHAR2(30);
288 --
289 BEGIN
290 --
291 OPEN name_exists;
292 FETCH name_exists into v_req_name;
293 IF name_exists%found THEN
294 CLOSE name_exists;
295 fnd_message.set_name('PER', 'HR_6638_VACS_UNIQUE_VAC_NAME');
296 fnd_message.set_token('REQ_NAME',v_req_name);
297 hr_utility.raise_error;
298 ELSE CLOSE name_exists;
299 END IF;
300 END Check_Unique_Name;
301 --
302 -----------------------------------------------------------------------------
303 -- Name --
304 -- Check_in_req_dates --
305 -- Purpose --
306 -- Ensure that the vacancy date from are witin the requisition dates. --
307 -- Called from WHEN-VALIDATE-ITEM in the vacancy block. --
308 -- --
309 -----------------------------------------------------------------------------
310 --
311 PROCEDURE Check_in_req_dates(P_requisition_id NUMBER,
312 P_Business_group_id NUMBER,
313 P_vac_date_from DATE) IS
314
315 CURSOR c_check_in_req_dates IS
316 SELECT 1
317 FROM PER_REQUISITIONS PR
318 WHERE PR.REQUISITION_ID = P_requisition_id
319 AND PR.business_group_id + 0 = P_Business_group_id
320 AND P_vac_date_from < PR.DATE_FROM;
321
322
323 v_dummy NUMBER(1);
324 --
325 BEGIN
326 --
327 OPEN c_check_in_req_dates;
328 FETCH c_check_in_req_dates into v_dummy;
329 IF c_check_in_req_dates%found THEN
330 CLOSE c_check_in_req_dates;
331 fnd_message.set_name('PER', 'HR_6640_VACS_IN_REQ_DATES');
332 hr_utility.raise_error;
333 ELSE CLOSE c_check_in_req_dates;
334 END IF;
335 END Check_in_req_dates;
336 --
337 -----------------------------------------------------------------------------
338 -- Name --
339 -- Chk_dt_to_in_req_dates --
340 -- Purpose --
341 -- Ensure that the vacancy date from are witin the requisition dates. --
342 -- Called from WHEN-VALIDATE-ITEM in the vacancy block. --
343 -- --
344 -----------------------------------------------------------------------------
345 --
346 PROCEDURE Chk_dt_to_in_req_dates(P_requisition_id NUMBER,
347 P_Business_group_id NUMBER,
348 P_vac_date_to DATE) IS
349
350 CURSOR c_in_req_dt IS
351 SELECT 1
352 FROM PER_REQUISITIONS PR
353 WHERE PR.REQUISITION_ID = P_requisition_id
354 AND PR.business_group_id + 0 = P_Business_group_id
355 AND P_vac_date_to > PR.DATE_TO;
356
357
358 v_dummy NUMBER(1);
359 --
360 BEGIN
361 --
362 OPEN c_in_req_dt;
363 FETCH c_in_req_dt into v_dummy;
364 IF c_in_req_dt%found THEN
365 CLOSE c_in_req_dt;
366 fnd_message.set_name('PER','HR_6843_VACS_DATE_TO_VAC');
367 hr_utility.raise_error;
368 ELSE CLOSE c_in_req_dt;
369 END IF;
370 END Chk_dt_to_in_req_dates;
371 --
372 ----------------------------------------------------------------------------
373 -- Name --
374 -- Date_from_upd_validation --
375 -- Purpose --
376 -- Ensure that the vacancy date_from does not invalidate any of the --
377 -- vacancy_for region.
378 -----------------------------------------------------------------------------
379 PROCEDURE Date_from_upd_validation(
380 Pz_vac_date_from DATE,
381 Pz_business_group_id NUMBER,
382 Pz_start_of_time DATE,
383 Pz_end_of_time DATE,
384 Pz_organization_id NUMBER,
385 Pz_position_id NUMBER,
386 Pz_people_group_id NUMBER,
387 Pz_job_id NUMBER,
388 Pz_grade_id NUMBER,
389 Pz_recruiter_id NUMBER,
390 Pz_location_id NUMBER
391 ) IS
392
393 BEGIN
394 IF Pz_organization_id IS NOT NULL THEN
395 PER_VACANCIES_PKG.D_from_updt_org_chk(
396 P_Business_group_id => Pz_business_group_id,
397 P_vac_date_from => Pz_vac_date_from,
398 P_organization_id => Pz_organization_id);
399 END IF;
400
401 IF Pz_position_id IS NOT NULL THEN
402 PER_VACANCIES_PKG.D_from_updt_pos_chk(
403 P_Business_group_id => Pz_business_group_id,
404 P_vac_date_from => Pz_vac_date_from,
405 P_position_id => Pz_position_id);
406 END IF;
407
408 IF Pz_people_group_id IS NOT NULL THEN
409 PER_VACANCIES_PKG.D_from_updt_grp_chk(
410 P_vac_date_from => Pz_vac_date_from,
411 P_start_of_time => Pz_start_of_time,
412 P_people_group_id => Pz_people_group_id);
413 END IF;
414
415 IF Pz_job_id IS NOT NULL THEN
416 PER_VACANCIES_PKG.D_from_updt_job_chk(
417 P_vac_date_from => Pz_vac_date_from,
418 P_Business_group_id => Pz_business_group_id,
419 P_job_id => Pz_job_id);
420 END IF;
421
422 IF Pz_grade_id IS NOT NULL THEN
423 PER_VACANCIES_PKG.D_from_updt_grd_chk
424 (P_vac_date_from => Pz_vac_date_from,
425 P_business_group_id => Pz_business_group_id,
426 P_grade_id => Pz_grade_id);
427 END IF;
428
429 IF Pz_location_id IS NOT NULL THEN
430 PER_VACANCIES_PKG.D_from_updt_loc_chk(
431 P_vac_date_from => Pz_vac_date_from,
432 P_end_of_time => Pz_end_of_time,
433 P_location_id => Pz_location_id);
434 END IF;
435
436
437 IF Pz_recruiter_id IS NOT NULL THEN
438 PER_VACANCIES_PKG.D_from_updt_person(
439 P_vac_date_from => Pz_vac_date_from,
440 P_recruiter_id => Pz_recruiter_id,
441 P_business_group_id => Pz_business_group_id);
442 END IF;
443
444 END Date_from_upd_validation;
445
446 -----------------------------------------------------------------------------
447 -- Name --
448 -- D_from_updt_rec_act_chk --
449 -- Purpose --
450 -- Ensure that the vacancy date_from does not invalidate any recruitment --
451 -- activity that may be using the vacancy. --
452 -- Arguments --
453 -- see below. --
454 -----------------------------------------------------------------------------
455 PROCEDURE D_from_updt_rec_act_chk(P_vacancy_id NUMBER,
456 P_vac_date_from DATE,
457 P_vac_date_to DATE,
458 P_end_of_time DATE) IS
459
460 -- This cursor retrieves a row if there is a recruitment activity
461 -- using the given vacancy where either of its start/end dates
462 -- are outside the vacancy dates.
463 CURSOR c_rec_act_chk IS
464 SELECT 1
465 FROM PER_RECRUITMENT_ACTIVITY_FOR F,
466 PER_RECRUITMENT_ACTIVITIES ACTS
467 WHERE F.VACANCY_ID = P_vacancy_id
468 AND F.RECRUITMENT_ACTIVITY_ID = ACTS.RECRUITMENT_ACTIVITY_ID
469 AND ( ACTS.DATE_START < P_vac_date_from
470 OR nvl(ACTS.DATE_END,p_end_of_time) > nvl(P_vac_date_to, P_end_of_time) ) ;
471
472 --
473 v_dummy NUMBER(1);
474 --
475 BEGIN
476 --
477 OPEN c_rec_act_chk;
478 FETCH c_rec_act_chk into v_dummy;
479 IF c_rec_act_chk%found THEN
480 CLOSE c_rec_act_chk;
481 fnd_message.set_name('PER','HR_6641_VACS_REC_ACTS');
482 hr_utility.raise_error;
483 ELSE CLOSE c_rec_act_chk;
484 END IF;
485 --
486 END D_from_updt_rec_act_chk;
487 --
488 -----------------------------------------------------------------------------
489 -- Name --
490 -- D_from_updt_org_chk --
491 -- Purpose --
492 -- Ensure that the vacancy date_from does not invalidate the organization--
493 -- part of the vacancy. --
494 -- Arguments --
495 -- see below. --
496 -----------------------------------------------------------------------------
497 PROCEDURE D_from_updt_org_chk(P_Business_group_id NUMBER,
498 P_vac_date_from DATE,
499 P_organization_id NUMBER) IS
500
501 CURSOR c_org_chk IS
502 SELECT 1
503 FROM HR_ORGANIZATION_UNITS HOU
504 WHERE HOU.ORGANIZATION_ID = P_organization_id
505 AND HOU.business_group_id + 0 = P_Business_group_id
506 AND P_vac_date_from < HOU.DATE_FROM;
507
508 --
509 v_dummy NUMBER(1);
510 --
511 BEGIN
512 --
513 OPEN c_org_chk;
514 FETCH c_org_chk into v_dummy;
515 IF c_org_chk%found THEN
516 CLOSE c_org_chk;
517 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
518 fnd_message.set_token('PART','organization');
519 hr_utility.raise_error;
520 ELSE CLOSE c_org_chk;
521 END IF;
522 --
523 END D_from_updt_org_chk;
524 --
525 -----------------------------------------------------------------------------
526 -- Name --
527 -- D_to_updt_org_chk --
528 -- Purpose --
529 -- Ensure that the vacancy date_to does not invalidate the organization --
530 -- part of the vacancy. --
531 -- Arguments --
532 -- see below.
533 -----------------------------------------------------------------------------
534 --
535 PROCEDURE D_to_updt_org_chk(P_Business_group_id NUMBER,
536 P_vac_date_to DATE,
537 P_organization_id NUMBER) IS
538
539 CURSOR c_org_chk IS
540 SELECT date_to
541 FROM HR_ORGANIZATION_UNITS HOU
542 WHERE HOU.ORGANIZATION_ID = P_organization_id
543 AND HOU.business_group_id + 0 = P_Business_group_id
544 AND P_vac_date_to > nvl(HOU.date_to, hr_api.g_eot);
545
546 --
547 v_dummy NUMBER(1);
548 v_date Date;
549 --
550 BEGIN
551 --
552 OPEN c_org_chk;
553 FETCH c_org_chk into v_date;
554
555 IF c_org_chk%found THEN
556 CLOSE c_org_chk;
557 fnd_message.set_name('PER',' HR_289199_ORG_VACS_DATE_TO');
558 fnd_message.set_token('DATE',v_date);
559 hr_utility.raise_error;
560 ELSE CLOSE c_org_chk;
561 END IF;
562 --
563 END D_to_updt_org_chk;
564 -----------------------------------------------------------------------------
565 -- Name --
566 -- D_from_updt_pos_chk --
567 -- Purpose --
568 -- Ensure that the vacancy date_from does not invalidate the position --
569 -- part of the vacancy. --
570 -- Arguments --
571 -- see below. --
572 -----------------------------------------------------------------------------
573 PROCEDURE D_from_updt_pos_chk(P_Business_group_id NUMBER,
574 P_vac_date_from DATE,
575 P_position_id NUMBER) IS
576 --
577 -- Changed 05-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked positions requirement
578 CURSOR c_pos_chk IS
579 SELECT 1
580 FROM HR_POSITIONS_F POS
581 WHERE POS.POSITION_ID = P_position_id
582 AND POS.business_group_id + 0 = P_Business_group_id
583 AND P_vac_date_from < POS.DATE_EFFECTIVE;
584
585 --
586 v_dummy NUMBER(1);
587 --
588 BEGIN
589 --
590 OPEN c_pos_chk;
591 FETCH c_pos_chk into v_dummy;
592 IF c_pos_chk%found THEN
593 CLOSE c_pos_chk;
594 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
595 fnd_message.set_token('PART','position');
596 hr_utility.raise_error;
597 ELSE CLOSE c_pos_chk;
598 END IF;
599 END D_from_updt_pos_chk;
600 --
601 -----------------------------------------------------------------------------
602 -- Name --
603 -- D_from_updt_grp_chk --
604 -- Purpose --
605 -- Ensure that the vacancy date_from does not invalidate the group --
606 -- part of the vacancy. --
607 -- Arguments --
608 -- see below. --
609 -----------------------------------------------------------------------------
610 PROCEDURE D_from_updt_grp_chk(P_vac_date_from DATE,
611 P_start_of_time DATE,
612 P_people_group_id NUMBER) IS
613
614 CURSOR c_grp_chk IS
615 SELECT 1
616 FROM PAY_PEOPLE_GROUPS PPG
617 WHERE PPG.PEOPLE_GROUP_ID = P_people_group_id
618 AND P_vac_date_from < nvl(PPG.START_DATE_ACTIVE,
619 P_start_of_time);
620
621 --
622 v_dummy NUMBER(1);
623 --
624 BEGIN
625 --
626 OPEN c_grp_chk;
627 FETCH c_grp_chk into v_dummy;
628 IF c_grp_chk%found THEN
629 CLOSE c_grp_chk;
630 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
631 fnd_message.set_token('PART','group');
632 hr_utility.raise_error;
633 ELSE CLOSE c_grp_chk;
634 END IF;
635 END D_from_updt_grp_chk;
636 --
637 -----------------------------------------------------------------------------
638 -- Name --
639 -- D_from_updt_job_chk --
640 -- Purpose --
641 -- Ensure that the vacancy date_from does not invalidate the job --
642 -- part of the vacancy. --
643 -- Arguments --
644 -- see below. --
645 -----------------------------------------------------------------------------
646 PROCEDURE D_from_updt_job_chk(P_vac_date_from DATE,
647 P_business_group_id NUMBER,
648 P_job_id NUMBER) IS
649
650 CURSOR c_job_chk IS
651 SELECT 1
652 FROM PER_JOBS_V PJ
653 WHERE PJ.JOB_ID = P_job_id
654 AND PJ.business_group_id + 0 = P_business_group_id
655 AND P_vac_date_from < PJ.DATE_FROM;
656
657 --
658 v_dummy NUMBER(1);
659 --
660 BEGIN
661 --
662 OPEN c_job_chk;
663 FETCH c_job_chk into v_dummy;
664 IF c_job_chk%found THEN
665 CLOSE c_job_chk;
666 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
667 fnd_message.set_token('PART','job');
668 hr_utility.raise_error;
669 ELSE CLOSE c_job_chk;
670 END IF;
671 END D_from_updt_job_chk;
672 --
673 -----------------------------------------------------------------------------
674 -- Name --
675 -- D_from_updt_grd_chk --
676 -- Purpose --
677 -- Ensure that the vacancy date_from does not invalidate the grade --
678 -- part of the vacancy. --
679 -- Arguments --
680 -- see below. --
681 -----------------------------------------------------------------------------
682 PROCEDURE D_from_updt_grd_chk(P_vac_date_from DATE,
683 P_business_group_id NUMBER,
684 P_grade_id NUMBER) IS
685
686 CURSOR c_grade_chk IS
687 SELECT 1
688 FROM PER_GRADES PG
689 WHERE PG.GRADE_ID = P_grade_id
690 AND PG.business_group_id + 0 = P_business_group_id
691 AND P_vac_date_from < PG.DATE_FROM;
692
693 --
694 v_dummy NUMBER(1);
695 --
696 BEGIN
697 --
698 OPEN c_grade_chk;
699 FETCH c_grade_chk into v_dummy;
700 IF c_grade_chk%found THEN
701 CLOSE c_grade_chk;
702 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
703 fnd_message.set_token('PART','grade');
704 hr_utility.raise_error;
705 ELSE CLOSE c_grade_chk;
706 END IF;
707 END D_from_updt_grd_chk;
708 --
709 -----------------------------------------------------------------------------
710 -- Name --
711 -- D_from_updt_loc_chk --
712 -- Purpose --
713 -- Ensure that the vacancy date_from does not invalidate the location --
714 -- part of the vacancy. --
715 -- Arguments --
716 -- see below. --
717 -----------------------------------------------------------------------------
718 PROCEDURE D_from_updt_loc_chk(P_vac_date_from DATE,
719 P_end_of_time DATE,
720 P_location_id NUMBER) IS
721
722 CURSOR c_loc_chk IS
723 SELECT 1
724 FROM HR_LOCATIONS HL
725 WHERE HL.LOCATION_ID = P_location_id
726 AND P_vac_date_from > nvl(HL.INACTIVE_DATE,P_end_of_time);
727
728 --
729 v_dummy NUMBER(1);
730 --
731 BEGIN
732 --
733 OPEN c_loc_chk;
734 FETCH c_loc_chk into v_dummy;
735 IF c_loc_chk%found THEN
736 CLOSE c_loc_chk;
737 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
738 fnd_message.set_token('PART','location');
739 hr_utility.raise_error;
740 ELSE CLOSE c_loc_chk;
741 END IF;
742 END D_from_updt_loc_chk;
743 --
744 -----------------------------------------------------------------------------
745 -- Name --
746 -- D_from_updt_person
747 -- Purpose --
748 -- Ensure that the vacancy date_from does not invalidate the recruiter --
749 -- part of the vacancy. --
750 -- Arguments --
751 -- see below. --
752 -----------------------------------------------------------------------------
753 PROCEDURE D_from_updt_person(P_vac_date_from DATE,
754 P_recruiter_id NUMBER,
755 P_business_group_id NUMBER) IS
756 -- bug 4475075 in the following cursor commented out the business group
757 -- validation condition and redifined .
758 CURSOR c_person IS
759 SELECT 1
760 FROM PER_ALL_PEOPLE_F P
761 WHERE P.PERSON_ID = P_recruiter_id
762 -- AND P.business_group_id + 0 = P_business_group_id
763 AND ( P.business_group_id = P_business_group_id or
764 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
765 AND P_vac_date_from BETWEEN p.effective_start_date
766 AND p.effective_end_date;
767 -- bug 447505 ends here
768 --
769 v_dummy NUMBER(1);
770 --
771 BEGIN
772 --
773 OPEN c_person;
774 FETCH c_person into v_dummy;
775 IF c_person%notfound THEN
776 CLOSE c_person;
777 fnd_message.set_name('PER','HR_6642_VACS_RECRUITER');
778 hr_utility.raise_error;
779 ELSE CLOSE c_person;
780 END IF;
781 END D_from_updt_person;
782 --
783 -----------------------------------------------------------------------------
784 -- Name --
785 -- get_people_group_id
786 -- Purpose --
787 -- to get the people_group_structure for the group key flexfield in the --
788 -- vacancy zone of PERWSVAC. --
789 -- Arguments --
790 -- see below. --
791 -----------------------------------------------------------------------------
792 FUNCTION get_people_group(P_Business_Group_id NUMBER) return VARCHAR2 IS
793
794 CURSOR c_pg IS
795 Select people_group_structure
796 From per_business_groups
797 Where business_group_id + 0 = P_Business_Group_id;
798
799 --
800 v_people_group_structure VARCHAR2(240);
801 --
802 BEGIN
803 OPEN c_pg;
804 FETCH c_pg into v_people_group_structure;
805 CLOSE c_pg;
806 RETURN v_people_group_structure;
807 END get_people_group;
808 -----------------------------------------------------------------------------
809
810 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
811 X_Vacancy_Id IN OUT NOCOPY NUMBER,
812 X_Business_Group_Id NUMBER,
813 X_Position_Id NUMBER,
814 X_Job_Id NUMBER,
815 X_Grade_Id NUMBER,
816 X_Organization_Id NUMBER,
817 X_Requisition_Id NUMBER,
818 X_People_Group_Id NUMBER,
819 X_People_Group_Name VARCHAR2,
820 X_Location_Id NUMBER,
821 X_Recruiter_Id NUMBER,
822 X_Date_From DATE,
823 X_Name VARCHAR2,
824 X_Comments VARCHAR2,
825 X_Date_To DATE,
826 X_Description VARCHAR2,
827 X_Vacancy_category varchar2,
828 X_Number_Of_Openings NUMBER,
829 X_Status VARCHAR2,
830 X_Budget_Measurement_Type VARCHAR2,
831 X_Budget_Measurement_Value NUMBER,
832 X_Attribute_Category VARCHAR2,
833 X_Attribute1 VARCHAR2,
834 X_Attribute2 VARCHAR2,
835 X_Attribute3 VARCHAR2,
836 X_Attribute4 VARCHAR2,
837 X_Attribute5 VARCHAR2,
838 X_Attribute6 VARCHAR2,
839 X_Attribute7 VARCHAR2,
840 X_Attribute8 VARCHAR2,
841 X_Attribute9 VARCHAR2,
842 X_Attribute10 VARCHAR2,
843 X_Attribute11 VARCHAR2,
844 X_Attribute12 VARCHAR2,
845 X_Attribute13 VARCHAR2,
846 X_Attribute14 VARCHAR2,
847 X_Attribute15 VARCHAR2,
848 X_Attribute16 VARCHAR2,
849 X_Attribute17 VARCHAR2,
850 X_Attribute18 VARCHAR2,
851 X_Attribute19 VARCHAR2,
852 X_Attribute20 VARCHAR2 )
853 IS
854 CURSOR C IS SELECT rowid
855 FROM PER_VACANCIES
856 WHERE vacancy_id = X_Vacancy_Id;
857
858
859 CURSOR C2 IS SELECT per_vacancies_s.nextval
860 FROM sys.dual;
861 BEGIN
862
863 if (X_Vacancy_Id is NULL) then
864 OPEN C2;
865 FETCH C2 INTO X_Vacancy_Id;
866 CLOSE C2;
867 end if;
868 INSERT INTO PER_VACANCIES(
869 vacancy_id,
870 business_group_id,
871 position_id,
872 job_id,
873 grade_id,
874 organization_id,
875 requisition_id,
876 people_group_id,
877 location_id,
878 recruiter_id,
879 date_from,
880 name,
881 comments,
882 date_to,
883 description,
884 vacancy_category,
885 number_of_openings,
886 status,
887 budget_measurement_type,
888 budget_measurement_value,
889 attribute_category,
890 attribute1,
891 attribute2,
892 attribute3,
893 attribute4,
894 attribute5,
895 attribute6,
896 attribute7,
897 attribute8,
898 attribute9,
899 attribute10,
900 attribute11,
901 attribute12,
902 attribute13,
903 attribute14,
904 attribute15,
905 attribute16,
906 attribute17,
907 attribute18,
908 attribute19,
909 attribute20
910 ) VALUES (
911 X_Vacancy_Id,
912 X_Business_Group_Id,
913 X_Position_Id,
914 X_Job_Id,
915 X_Grade_Id,
916 X_Organization_Id,
917 X_Requisition_Id,
918 X_People_Group_Id,
919 X_Location_Id,
920 X_Recruiter_Id,
921 X_Date_From,
922 X_Name,
923 X_Comments,
924 X_Date_To,
925 X_Description,
926 X_vacancy_category,
927 X_Number_Of_Openings,
928 X_Status,
929 X_Budget_Measurement_Type,
930 X_Budget_Measurement_Value,
931 X_Attribute_Category,
932 X_Attribute1,
933 X_Attribute2,
934 X_Attribute3,
935 X_Attribute4,
936 X_Attribute5,
937 X_Attribute6,
938 X_Attribute7,
939 X_Attribute8,
940 X_Attribute9,
941 X_Attribute10,
942 X_Attribute11,
943 X_Attribute12,
944 X_Attribute13,
945 X_Attribute14,
946 X_Attribute15,
947 X_Attribute16,
948 X_Attribute17,
949 X_Attribute18,
950 X_Attribute19,
951 X_Attribute20 );
952
953 OPEN C;
954 FETCH C INTO X_Rowid;
955 if (C%NOTFOUND) then
956 CLOSE C;
957 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
958 hr_utility.set_message_token('PROCEDURE',
959 'per_vacancies_pkg.insert_row');
960 hr_utility.set_message_token('STEP','1');
961 hr_utility.raise_error;
962 end if;
963 CLOSE C;
964 --
965 per_applicant_pkg.update_group ( x_people_group_id,
966 x_people_group_name ) ;
967 --
968 END Insert_Row;
969 --
970 -----------------------------------------------------------------------------
971 -- Name --
972 -- Lock_Row --
973 -- Purpose --
974 -- Table handler procedure that supports the insert , update and delete --
975 -- of a vacancy by applying a lock on a vacancy in the Define --
976 -- Requisition and Vacnacy form. --
977 -- Arguments --
978 -- Notes --
979 -----------------------------------------------------------------------------
980 --
981 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
982 X_Vacancy_Id NUMBER,
983 X_Business_Group_Id NUMBER,
984 X_Position_Id NUMBER,
985 X_Job_Id NUMBER,
986 X_Grade_Id NUMBER,
987 X_Organization_Id NUMBER,
988 X_Requisition_Id NUMBER,
989 X_People_Group_Id NUMBER,
990 X_Location_Id NUMBER,
991 X_Recruiter_Id NUMBER,
992 X_Date_From DATE,
993 X_Name VARCHAR2,
994 X_Comments VARCHAR2,
995 X_Date_To DATE,
996 X_Description VARCHAR2,
997 X_Vacancy_category varchar2,
998 X_Number_Of_Openings NUMBER,
999 X_Status VARCHAR2,
1000 X_Budget_Measurement_Type VARCHAR2,
1001 X_Budget_Measurement_Value NUMBER,
1002 X_Attribute_Category VARCHAR2,
1003 X_Attribute1 VARCHAR2,
1004 X_Attribute2 VARCHAR2,
1005 X_Attribute3 VARCHAR2,
1006 X_Attribute4 VARCHAR2,
1007 X_Attribute5 VARCHAR2,
1008 X_Attribute6 VARCHAR2,
1009 X_Attribute7 VARCHAR2,
1010 X_Attribute8 VARCHAR2,
1011 X_Attribute9 VARCHAR2,
1012 X_Attribute10 VARCHAR2,
1013 X_Attribute11 VARCHAR2,
1014 X_Attribute12 VARCHAR2,
1015 X_Attribute13 VARCHAR2,
1016 X_Attribute14 VARCHAR2,
1017 X_Attribute15 VARCHAR2,
1018 X_Attribute16 VARCHAR2,
1019 X_Attribute17 VARCHAR2,
1020 X_Attribute18 VARCHAR2,
1021 X_Attribute19 VARCHAR2,
1022 X_Attribute20 VARCHAR2)
1023 IS
1024 CURSOR C IS
1025 SELECT *
1026 FROM PER_VACANCIES
1027 WHERE rowid = X_Rowid
1028 FOR UPDATE of Vacancy_Id NOWAIT;
1029 BEGIN
1030 OPEN C;
1031 FETCH C INTO g_Recinfo;
1032 if (C%NOTFOUND) then
1033 CLOSE C;
1034 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1035 hr_utility.set_message_token('PROCEDURE',
1036 'per_vacancies_pkb.lock_row');
1037 hr_utility.set_message_token('STEP','1');
1038 hr_utility.raise_error;
1039 end if;
1040 CLOSE C;
1041 --
1042 g_Recinfo.attribute18 := rtrim(g_Recinfo.attribute18);
1043 g_Recinfo.attribute19 := rtrim(g_Recinfo.attribute19);
1044 g_Recinfo.attribute20 := rtrim(g_Recinfo.attribute20);
1045 g_Recinfo.name := rtrim(g_Recinfo.name);
1046 g_Recinfo.comments := rtrim(g_Recinfo.comments);
1047 g_Recinfo.description := rtrim(g_Recinfo.description);
1048 g_Recinfo.vacancy_category := rtrim(g_Recinfo.vacancy_category);
1049 g_Recinfo.status := rtrim(g_Recinfo.status);
1050 g_Recinfo.budget_measurement_type := rtrim(g_Recinfo.budget_measurement_type);
1051 g_Recinfo.attribute_category := rtrim(g_Recinfo.attribute_category);
1052 g_Recinfo.attribute1 := rtrim(g_Recinfo.attribute1);
1053 g_Recinfo.attribute2 := rtrim(g_Recinfo.attribute2);
1054 g_Recinfo.attribute3 := rtrim(g_Recinfo.attribute3);
1055 g_Recinfo.attribute4 := rtrim(g_Recinfo.attribute4);
1056 g_Recinfo.attribute5 := rtrim(g_Recinfo.attribute5);
1057 g_Recinfo.attribute6 := rtrim(g_Recinfo.attribute6);
1058 g_Recinfo.attribute7 := rtrim(g_Recinfo.attribute7);
1059 g_Recinfo.attribute8 := rtrim(g_Recinfo.attribute8);
1060 g_Recinfo.attribute9 := rtrim(g_Recinfo.attribute9);
1061 g_Recinfo.attribute10 := rtrim(g_Recinfo.attribute10);
1062 g_Recinfo.attribute11 := rtrim(g_Recinfo.attribute11);
1063 g_Recinfo.attribute12 := rtrim(g_Recinfo.attribute12);
1064 g_Recinfo.attribute13 := rtrim(g_Recinfo.attribute13);
1065 g_Recinfo.attribute14 := rtrim(g_Recinfo.attribute14);
1066 g_Recinfo.attribute15 := rtrim(g_Recinfo.attribute15);
1067 g_Recinfo.attribute16 := rtrim(g_Recinfo.attribute16);
1068 g_Recinfo.attribute17 := rtrim(g_Recinfo.attribute17);
1069 --
1070 if (
1071 ( (g_Recinfo.vacancy_id = X_Vacancy_Id)
1072 OR ( (g_Recinfo.vacancy_id IS NULL)
1073 AND (X_Vacancy_Id IS NULL)))
1074 AND ( (g_Recinfo.business_group_id = X_Business_Group_Id)
1075 OR ( (g_Recinfo.business_group_id IS NULL)
1076 AND (X_Business_Group_Id IS NULL)))
1077 AND ( (g_Recinfo.position_id = X_Position_Id)
1078 OR ( (g_Recinfo.position_id IS NULL)
1079 AND (X_Position_Id IS NULL)))
1080 AND ( (g_Recinfo.job_id = X_Job_Id)
1081 OR ( (g_Recinfo.job_id IS NULL)
1082 AND (X_Job_Id IS NULL)))
1083 AND ( (g_Recinfo.grade_id = X_Grade_Id)
1084 OR ( (g_Recinfo.grade_id IS NULL)
1085 AND (X_Grade_Id IS NULL)))
1086 AND ( (g_Recinfo.organization_id = X_Organization_Id)
1087 OR ( (g_Recinfo.organization_id IS NULL)
1088 AND (X_Organization_Id IS NULL)))
1089 AND ( (g_Recinfo.requisition_id = X_Requisition_Id)
1090 OR ( (g_Recinfo.requisition_id IS NULL)
1091 AND (X_Requisition_Id IS NULL)))
1092 AND ( (g_Recinfo.people_group_id = X_People_Group_Id)
1093 OR ( (g_Recinfo.people_group_id IS NULL)
1094 AND (X_People_Group_Id IS NULL)))
1095 AND ( (g_Recinfo.location_id = X_Location_Id)
1096 OR ( (g_Recinfo.location_id IS NULL)
1097 AND (X_Location_Id IS NULL)))
1098 AND ( (g_Recinfo.recruiter_id = X_Recruiter_Id)
1099 OR ( (g_Recinfo.recruiter_id IS NULL)
1100 AND (X_Recruiter_Id IS NULL)))
1101 AND ( (g_Recinfo.date_from = X_Date_From)
1102 OR ( (g_Recinfo.date_from IS NULL)
1103 AND (X_Date_From IS NULL)))
1104 AND ( (g_Recinfo.name = X_Name)
1105 OR ( (g_Recinfo.name IS NULL)
1106 AND (X_Name IS NULL)))
1107 AND ( (g_Recinfo.comments = X_Comments)
1108 OR ( (g_Recinfo.comments IS NULL)
1109 AND (X_Comments IS NULL)))
1110 AND ( (g_Recinfo.date_to = X_Date_To)
1111 OR ( (g_Recinfo.date_to IS NULL)
1112 AND (X_Date_To IS NULL)))
1113 AND ( (g_Recinfo.description = X_Description)
1114 OR ( (g_Recinfo.description IS NULL)
1115 AND (X_Description IS NULL)))
1116 AND ( (g_Recinfo.vacancy_category = X_vacancy_category)
1117 OR ( (g_Recinfo.vacancy_category IS NULL)
1118 AND (X_vacancy_category IS NULL)))
1119 AND ( (g_Recinfo.number_of_openings = X_Number_Of_Openings)
1120 OR ( (g_Recinfo.number_of_openings IS NULL)
1121 AND (X_Number_Of_Openings IS NULL)))
1122 AND ( (g_Recinfo.status = X_Status)
1123 OR ( (g_Recinfo.status IS NULL)
1124 AND (X_Status IS NULL)))
1125 AND ( (g_Recinfo.budget_measurement_type = X_Budget_Measurement_Type)
1126 OR ( (g_Recinfo.budget_measurement_type IS NULL)
1127 AND (X_Budget_Measurement_Type IS NULL)))
1128 AND ( (g_Recinfo.budget_measurement_value = X_Budget_Measurement_Value)
1129 OR ( (g_Recinfo.budget_measurement_value IS NULL)
1130 AND (X_Budget_Measurement_Value IS NULL)))
1131 AND ( (g_Recinfo.attribute_category = X_Attribute_Category)
1132 OR ( (g_Recinfo.attribute_category IS NULL)
1133 AND (X_Attribute_Category IS NULL)))
1134 AND ( (g_Recinfo.attribute1 = X_Attribute1)
1135 OR ( (g_Recinfo.attribute1 IS NULL)
1136 AND (X_Attribute1 IS NULL)))
1137 AND ( (g_Recinfo.attribute2 = X_Attribute2)
1138 OR ( (g_Recinfo.attribute2 IS NULL)
1139 AND (X_Attribute2 IS NULL)))
1140 AND ( (g_Recinfo.attribute3 = X_Attribute3)
1141 OR ( (g_Recinfo.attribute3 IS NULL)
1142 AND (X_Attribute3 IS NULL)))
1143 AND ( (g_Recinfo.attribute4 = X_Attribute4)
1144 OR ( (g_Recinfo.attribute4 IS NULL)
1145 AND (X_Attribute4 IS NULL)))
1146 AND ( (g_Recinfo.attribute5 = X_Attribute5)
1147 OR ( (g_Recinfo.attribute5 IS NULL)
1148 AND (X_Attribute5 IS NULL)))
1149 AND ( (g_Recinfo.attribute6 = X_Attribute6)
1150 OR ( (g_Recinfo.attribute6 IS NULL)
1151 AND (X_Attribute6 IS NULL)))
1152 AND ( (g_Recinfo.attribute7 = X_Attribute7)
1153 OR ( (g_Recinfo.attribute7 IS NULL)
1154 AND (X_Attribute7 IS NULL)))
1155 AND ( (g_Recinfo.attribute8 = X_Attribute8)
1156 OR ( (g_Recinfo.attribute8 IS NULL)
1157 AND (X_Attribute8 IS NULL)))
1158 AND ( (g_Recinfo.attribute9 = X_Attribute9)
1159 OR ( (g_Recinfo.attribute9 IS NULL)
1160 AND (X_Attribute9 IS NULL)))
1161 AND ( (g_Recinfo.attribute10 = X_Attribute10)
1162 OR ( (g_Recinfo.attribute10 IS NULL)
1163 AND (X_Attribute10 IS NULL)))
1164 AND ( (g_Recinfo.attribute11 = X_Attribute11)
1165 OR ( (g_Recinfo.attribute11 IS NULL)
1166 AND (X_Attribute11 IS NULL)))
1167 AND ( (g_Recinfo.attribute12 = X_Attribute12)
1168 OR ( (g_Recinfo.attribute12 IS NULL)
1169 AND (X_Attribute12 IS NULL)))
1170 AND ( (g_Recinfo.attribute13 = X_Attribute13)
1171 OR ( (g_Recinfo.attribute13 IS NULL)
1172 AND (X_Attribute13 IS NULL)))
1173 AND ( (g_Recinfo.attribute14 = X_Attribute14)
1174 OR ( (g_Recinfo.attribute14 IS NULL)
1175 AND (X_Attribute14 IS NULL)))
1176 AND ( (g_Recinfo.attribute15 = X_Attribute15)
1177 OR ( (g_Recinfo.attribute15 IS NULL)
1178 AND (X_Attribute15 IS NULL)))
1179 AND ( (g_Recinfo.attribute16 = X_Attribute16)
1180 OR ( (g_Recinfo.attribute16 IS NULL)
1181 AND (X_Attribute16 IS NULL)))
1182 AND ( (g_Recinfo.attribute17 = X_Attribute17)
1183 OR ( (g_Recinfo.attribute17 IS NULL)
1184 AND (X_Attribute17 IS NULL)))
1185 AND ( (g_Recinfo.attribute18 = X_Attribute18)
1186 OR ( (g_Recinfo.attribute18 IS NULL)
1187 AND (X_Attribute18 IS NULL)))
1188 AND ( (g_Recinfo.attribute19 = X_Attribute19)
1189 OR ( (g_Recinfo.attribute19 IS NULL)
1190 AND (X_Attribute19 IS NULL)))
1191 AND ( (g_Recinfo.attribute20 = X_Attribute20)
1192 OR ( (g_Recinfo.attribute20 IS NULL)
1193 AND (X_Attribute20 IS NULL)))
1194 ) then
1195 return;
1196 else
1197 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1198 APP_EXCEPTION.RAISE_EXCEPTION;
1199 end if;
1200 END Lock_Row;
1201 --
1202 -----------------------------------------------------------------------------
1203 -- Name --
1204 -- Update_Row --
1205 -- Purpose --
1206 -- Table handler procedure that supports the update of a VACANCY via --
1207 -- Define Requistion and Vacancy form. --
1208 -- Arguments --
1209 -- See below. --
1210 -- Notes --
1211 -- None. --
1212 -----------------------------------------------------------------------------
1213 --
1214 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1215 X_Vacancy_Id NUMBER,
1216 X_Business_Group_Id NUMBER,
1217 X_Position_Id NUMBER,
1218 X_Job_Id NUMBER,
1219 X_Grade_Id NUMBER,
1220 X_Organization_Id NUMBER,
1221 X_Requisition_Id NUMBER,
1222 X_People_Group_Id NUMBER,
1223 X_People_Group_Name VARCHAR2,
1224 X_Location_Id NUMBER,
1225 X_Recruiter_Id NUMBER,
1226 X_Date_From DATE,
1227 X_Name VARCHAR2,
1228 X_Comments VARCHAR2,
1229 X_Date_To DATE,
1230 X_Description VARCHAR2,
1231 X_Vacancy_category varchar2,
1232 X_Number_Of_Openings NUMBER,
1233 X_Status VARCHAR2,
1234 X_Budget_Measurement_Type VARCHAR2,
1235 X_Budget_Measurement_Value NUMBER,
1236 X_Attribute_Category VARCHAR2,
1237 X_Attribute1 VARCHAR2,
1238 X_Attribute2 VARCHAR2,
1239 X_Attribute3 VARCHAR2,
1240 X_Attribute4 VARCHAR2,
1241 X_Attribute5 VARCHAR2,
1242 X_Attribute6 VARCHAR2,
1243 X_Attribute7 VARCHAR2,
1244 X_Attribute8 VARCHAR2,
1245 X_Attribute9 VARCHAR2,
1246 X_Attribute10 VARCHAR2,
1247 X_Attribute11 VARCHAR2,
1248 X_Attribute12 VARCHAR2,
1249 X_Attribute13 VARCHAR2,
1250 X_Attribute14 VARCHAR2,
1251 X_Attribute15 VARCHAR2,
1252 X_Attribute16 VARCHAR2,
1253 X_Attribute17 VARCHAR2,
1254 X_Attribute18 VARCHAR2,
1255 X_Attribute19 VARCHAR2,
1256 X_Attribute20 VARCHAR2
1257 ) IS
1258 BEGIN
1259 --
1260 IF X_Organization_Id is not null then
1261 PER_VACANCIES_PKG.D_to_updt_org_chk(P_Business_group_id => X_Business_group_id
1262 ,P_vac_date_to => X_Date_To
1263 ,P_organization_id => X_Organization_Id);
1264 end if;
1265 --
1266 UPDATE PER_VACANCIES
1267 SET
1268 vacancy_id = X_Vacancy_Id,
1269 business_group_id = X_Business_Group_Id,
1270 position_id = X_Position_Id,
1271 job_id = X_Job_Id,
1272 grade_id = X_Grade_Id,
1273 organization_id = X_Organization_Id,
1274 requisition_id = X_Requisition_Id,
1275 people_group_id = X_People_Group_Id,
1276 location_id = X_Location_Id,
1277 recruiter_id = X_Recruiter_Id,
1278 date_from = X_Date_From,
1279 name = X_Name,
1280 comments = X_Comments,
1281 date_to = X_Date_To,
1282 description = X_Description,
1283 vacancy_category = X_Vacancy_category,
1284 number_of_openings = X_Number_Of_Openings,
1285 status = X_Status,
1286 budget_measurement_type = X_Budget_Measurement_Type,
1287 budget_measurement_value = X_Budget_Measurement_Value,
1288 attribute_category = X_Attribute_Category,
1289 attribute1 = X_Attribute1,
1290 attribute2 = X_Attribute2,
1291 attribute3 = X_Attribute3,
1292 attribute4 = X_Attribute4,
1293 attribute5 = X_Attribute5,
1294 attribute6 = X_Attribute6,
1295 attribute7 = X_Attribute7,
1296 attribute8 = X_Attribute8,
1297 attribute9 = X_Attribute9,
1298 attribute10 = X_Attribute10,
1299 attribute11 = X_Attribute11,
1300 attribute12 = X_Attribute12,
1301 attribute13 = X_Attribute13,
1302 attribute14 = X_Attribute14,
1303 attribute15 = X_Attribute15,
1304 attribute16 = X_Attribute16,
1305 attribute17 = X_Attribute17,
1306 attribute18 = X_Attribute18,
1307 attribute19 = X_Attribute19,
1308 attribute20 = X_Attribute20
1309 WHERE rowid = X_rowid;
1310
1311 if (SQL%NOTFOUND) then
1312 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1313 hr_utility.set_message_token('PROCEDURE',
1314 'per_vacancies_pkb.update_row');
1315 hr_utility.set_message_token('STEP','1');
1316 hr_utility.raise_error;
1317 end if;
1318
1319
1320 -- Cascade changes to applicants
1321
1322 -- Details are only cascaded to the applicant assignments when the value
1323 -- is not null, except for position, which updates the value if the
1324 -- organization and job have changed regardless of whether it is null.
1325 --
1326 update per_all_assignments_f asg
1327 set asg.organization_id = nvl(x_organization_id, asg.organization_id)
1328 ,asg.job_id = nvl(x_job_id, asg.job_id)
1329 ,asg.grade_id = nvl(x_grade_id, asg.grade_id)
1330 ,asg.people_group_id = nvl(x_people_group_id, asg.people_group_id)
1331 ,asg.location_id = nvl(x_location_id, asg.location_id)
1332 ,asg.recruiter_id = nvl(x_recruiter_id, asg.recruiter_id)
1333 ,asg.position_id = decode
1334 (x_organization_id||'.'||x_job_id,
1335 g_recinfo.organization_id||'.'||g_recinfo.job_id,
1336 nvl(x_position_id, asg.position_id),
1337 x_position_id)
1338 where asg.assignment_type = 'A'
1339 and asg.vacancy_id = x_vacancy_id
1340 and ( asg.organization_id <> nvl(x_organization_id,
1341 asg.organization_id)
1342 or nvl(asg.job_id, -1) <> nvl(x_job_id,
1343 nvl(asg.job_id, -1))
1344 or nvl(asg.grade_id, -1) <> nvl(x_grade_id,
1345 nvl(asg.grade_id, -1))
1346 or nvl(asg.people_group_id, -1) <> nvl(x_people_group_id,
1347 nvl(asg.people_group_id, -1))
1348 or nvl(asg.position_id, -1) <> nvl(x_position_id,
1349 nvl(asg.position_id, -1))
1350 or nvl(asg.location_id, -1) <> nvl(x_location_id,
1351 nvl(asg.location_id, -1))
1352 or nvl(asg.recruiter_id, -1) <> nvl(x_recruiter_id,
1353 nvl(asg.recruiter_id, -1))
1354 );
1355
1356 --
1357 per_applicant_pkg.update_group ( x_people_group_id,
1358 x_people_group_name ) ;
1359 --
1360 END Update_Row;
1361 --
1362 -----------------------------------------------------------------------------
1363 -- Name --
1364 -- Delete_Row --
1365 -- Purpose --
1366 -- Table handler procedure that supports the delete of a VACANCY via --
1367 -- the Define Requistion and Vacancy form. --
1368 -- Arguments --
1369 -- See below. --
1370 -- Notes --
1371 -- --
1372 -----------------------------------------------------------------------------
1373 --
1374 PROCEDURE Delete_Row(X_Rowid VARCHAR2 , x_vacancy_id in number ) IS
1375 BEGIN
1376 check_references( x_vacancy_id ) ;
1377 DELETE FROM PER_VACANCIES
1378 WHERE rowid = X_Rowid;
1379
1380 if (SQL%NOTFOUND) then
1381 RAISE NO_DATA_FOUND;
1382 end if;
1383 END Delete_Row;
1384
1385 END PER_VACANCIES_PKG;