[Home] [Help]
PACKAGE BODY: APPS.PER_VACANCIES_PKG
Source
1 PACKAGE BODY PER_VACANCIES_PKG as
2 /* $Header: pevac01t.pkb 120.3.12010000.9 2010/04/08 10:23:05 karthmoh 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 04-Nov-08 sidsaxen 115.17 Added procedure end_date_irec_RA, end_date_PER_RA
73 and stubbed procedure D_from_updt_rec_act_chk
74 for bug 6497289
75 24-Feb-09 lbodired 115.19 Modified the procedure UPDATE_ROW
76 for the bug 7592739
77 01-Jun-09 sidsaxen 115.23 bug 8518955, handled NULL while updating
78 per_all_assignments_f in per_vacancies_pkg.update_row
79 08-APR-10 karthmoh 120.3.12010000.9 Modified/Added Procedures for ER#8530112
80 ============================================================================*/
81 ----------------------------------------------------------------------------
82 --
83 -- Name --
84 -- Check_References --
85 -- Purpose --
86 -- To ensure the referential integrity when a vacancy is deleted from the--
87 -- Define Requisition and Vacancy form. --
88 -- Checks that the vacancy is not used in a recruitment activity
89 -- or by an assignment.
90 -----------------------------------------------------------------------------
91 --
92 PROCEDURE Check_References(P_vacancy_id NUMBER ) is
93 CURSOR c_check_references1 IS
94 SELECT distinct(PV.NAME)
95 FROM PER_VACANCIES PV,
96 PER_ALL_ASSIGNMENTS_F PAF
97 WHERE PAF.VACANCY_ID = P_vacancy_id
98 AND PV.VACANCY_ID = P_vacancy_id
99 AND PAF.VACANCY_ID = PV.VACANCY_ID;
100
101 CURSOR c_check_ref_2 IS
102 SELECT PV.NAME
103 FROM PER_VACANCIES PV
104 , PER_RECRUITMENT_ACTIVITY_FOR PRAF
105 WHERE PRAF.VACANCY_ID = P_vacancy_id
106 AND PV.VACANCY_ID = P_vacancy_id ;
107
108 --
109 V_name VARCHAR2(30);
110 --
111
112 BEGIN
113 --
114 OPEN c_check_references1;
115 FETCH c_check_references1 into V_name;
116 IF c_check_references1%FOUND THEN
117 CLOSE c_check_references1;
118 fnd_message.set_name('PER','HR_6125_REQS_VACS_DEL_ASSIGN');
119 hr_utility.raise_error;
120 ELSE CLOSE c_check_references1;
121 END IF;
122 --
123 OPEN c_check_ref_2;
124 FETCH c_check_ref_2 into V_name;
125 IF c_check_ref_2%FOUND THEN
126 CLOSE c_check_ref_2;
127 fnd_message.set_name('PER','HR_6126_REQS_VACS_DEL_REC_ACTY');
128 fnd_message.set_token('VACANCY_NAME',V_name);
129 hr_utility.raise_error;
130 ELSE CLOSE c_check_ref_2;
131 END IF;
132 --
133 END Check_References;
134 ----------------------------------------------------------------------------
135 --
136 -- Name --
137 -- B_counter --
138 -- Purpose --
139 -- The purpose of this function is to return the values for the FOLDER
140 -- block of the forms VIEW VACANCIES.
141 -----------------------------------------------------------------------------
142 FUNCTION B_counter(P_Business_group_id NUMBER,
143 P_vacancy_id NUMBER,
144 P_legislation_code VARCHAR2,
145 P_vac_type VARCHAR2) return NUMBER IS
146
147 CURSOR csr_counter IS
148 SELECT COUNT(distinct ass.assignment_id)
149 FROM PER_ALL_ASSIGNMENTS ASS,
150 PER_ASSIGNMENT_STATUS_TYPES a
151 where nvl(A.BUSINESS_GROUP_ID,P_Business_group_id) =
152 P_Business_group_id
153 and ass.business_group_id + 0 = P_Business_group_id
154 and ass.ASSIGNMENT_TYPE = 'A'
155 and ass.ASSIGNMENT_STATUS_TYPE_ID = A.ASSIGNMENT_STATUS_TYPE_ID
156 and nvl(a.LEGISLATION_CODE,P_legislation_code) =
157 P_legislation_code
158 and A.PER_SYSTEM_STATUS = P_vac_type
159 and ass.vacancy_id = P_vacancy_id;
160
161 --
162 v_number_of_asgs NUMBER(15);
163 --
164 BEGIN
165 OPEN csr_counter;
166 FETCH csr_counter into v_number_of_asgs;
167 CLOSE csr_counter;
168 RETURN v_number_of_asgs;
169 END B_counter;
170 ----------------------------------------------------------------------------
171 --
172 -- Name --
173 -- folder_hires --
174 -- Purpose --
175 -- the purpose of this function is to return the number of applicants who
176 -- have been hired as employees as a result of being hired into a vacancy.
177 -- This function is used by the folder form PERWILVA - View Vacancies.
178 -----------------------------------------------------------------------------
179 FUNCTION folder_hires(P_Business_group_id NUMBER,
180 P_vacancy_id NUMBER
181 ) return NUMBER IS
182
183 CURSOR csr_hires IS
184 SELECT COUNT(*)
185 FROM PER_ALL_ASSIGNMENTS A
186 WHERE A.business_group_id + 0 = P_Business_group_id
187 AND A.VACANCY_ID = P_vacancy_id
188 AND A.ASSIGNMENT_TYPE = 'E';
189
190 --
191 v_vac_hires NUMBER(15);
192 --
193 BEGIN
194 OPEN csr_hires;
195 FETCH csr_hires into v_vac_hires;
196 CLOSE csr_hires;
197 RETURN v_vac_hires;
198 END folder_hires;
199
200 ----------------------------------------------------------------------------
201 -- Name --
202 -- folder_current --
203 -- Purpose --
204 -- the purpose of this function is to return the number of current openings
205 -- for the vacancy as of the session date i.e it is the initial number of
206 -- openings for the vacancy as when the vacancy was defined minus the
207 -- number of applicants who have been hired into the vacancy.
208 -----------------------------------------------------------------------------
209 FUNCTION folder_current(P_Business_group_id NUMBER,
210 P_vacancy_id NUMBER,
211 P_session_date DATE
212 ) return NUMBER IS
213
214 CURSOR csr_current IS
215 SELECT COUNT(DISTINCT A.ASSIGNMENT_ID)
216 FROM PER_ALL_ASSIGNMENTS A
217 WHERE A.VACANCY_ID = P_vacancy_id
218 AND A.business_group_id + 0 = P_Business_group_id
219 AND A.ASSIGNMENT_TYPE = 'E'
220 AND A.EFFECTIVE_START_DATE <= P_session_date ;
221
222
223 --
224 v_vac_current NUMBER(15);
225 --
226 BEGIN
227 OPEN csr_current;
228 FETCH csr_current into v_vac_current;
229 CLOSE csr_current;
230 RETURN v_vac_current;
231 END folder_current;
232 --
233 -----------------------------------------------------------------------------
234 -- Name --
235 -- Chk_appl_exists --
236 -- Purpose --
237 -- Verify the effective date, you cannot change the effective date of --
238 -- this vacancy to a future date as applications exist within the vacancy--
239 -- availability period. --
240 -- Called from WHEN-VALIDATE-ITEM in the vacancy block. --
241 -- --
242 -----------------------------------------------------------------------------
243 --
244 procedure chk_appl_exists (P_vacancy_id NUMBER,
245 P_vac_date_from DATE,
246 P_vac_date_to DATE,
247 P_end_of_time DATE
248 )
249 is
250 cursor csr_appl_exists
251 is
252 select '1'
253 from per_all_assignments_f
254 where vacancy_id =P_vacancy_id
255 and effective_start_date < P_vac_date_from
256 and assignment_type = 'A';
257
258 l_flag varchar2(1);
259
260 begin
261
262 open csr_appl_exists ;
263
264 fetch csr_appl_exists into l_flag;
265
266 if csr_appl_exists%found then
267 close csr_appl_exists;
268 fnd_message.set_name('PER','HR_449819_VACS_APL_ACTS');
269 hr_utility.raise_error;
270 else
271 close csr_appl_exists;
272 end if;
273
274 end chk_appl_exists;
275 ----------------------------------------------------------------------------_
276 -- Name --
277 -- Check_Unique_Name --
278 -- Purpose --
279 -- checks that the vacancy name is unique within the requisition. --
280 -- Called from the client side package VACANCY_ITEMS from the procedure --
281 -----------------------------------------------------------------------------
282 --
283 -- Modified for 4262036.
284 PROCEDURE Check_Unique_Name(P_Name VARCHAR2,
285 P_business_group_id NUMBER,
286 P_rowid VARCHAR2) IS
287
288 CURSOR name_exists IS
289 SELECT v.name
290 FROM PER_ALL_VACANCIES v
291 WHERE v.NAME = P_Name
292 AND v.business_group_id = P_business_group_id
293 AND (P_rowid <> v.rowid
294 or P_rowid is NULL);
295 v_req_name VARCHAR2(30);
296 --
297 BEGIN
298 --
299 OPEN name_exists;
300 FETCH name_exists into v_req_name;
301 IF name_exists%found THEN
302 CLOSE name_exists;
303 fnd_message.set_name('PER', 'HR_6638_VACS_UNIQUE_VAC_NAME');
304 fnd_message.set_token('REQ_NAME',v_req_name);
305 hr_utility.raise_error;
306 ELSE CLOSE name_exists;
307 END IF;
308 END Check_Unique_Name;
309 --
310 -----------------------------------------------------------------------------
311 -- Name --
312 -- Check_in_req_dates --
313 -- Purpose --
314 -- Ensure that the vacancy date from are witin the requisition dates. --
315 -- Called from WHEN-VALIDATE-ITEM in the vacancy block. --
316 -- --
317 -----------------------------------------------------------------------------
318 --
319 PROCEDURE Check_in_req_dates(P_requisition_id NUMBER,
320 P_Business_group_id NUMBER,
321 P_vac_date_from DATE) IS
322
323 CURSOR c_check_in_req_dates IS
324 SELECT 1
325 FROM PER_REQUISITIONS PR
326 WHERE PR.REQUISITION_ID = P_requisition_id
327 AND PR.business_group_id + 0 = P_Business_group_id
328 AND P_vac_date_from < PR.DATE_FROM;
329
330
331 v_dummy NUMBER(1);
332 --
333 BEGIN
334 --
335 OPEN c_check_in_req_dates;
336 FETCH c_check_in_req_dates into v_dummy;
337 IF c_check_in_req_dates%found THEN
338 CLOSE c_check_in_req_dates;
339 fnd_message.set_name('PER', 'HR_6640_VACS_IN_REQ_DATES');
340 hr_utility.raise_error;
341 ELSE CLOSE c_check_in_req_dates;
342 END IF;
343 END Check_in_req_dates;
344 --
345 -----------------------------------------------------------------------------
346 -- Name --
347 -- Chk_dt_to_in_req_dates --
348 -- Purpose --
349 -- Ensure that the vacancy date from are witin the requisition dates. --
350 -- Called from WHEN-VALIDATE-ITEM in the vacancy block. --
351 -- --
352 -----------------------------------------------------------------------------
353 --
354 PROCEDURE Chk_dt_to_in_req_dates(P_requisition_id NUMBER,
355 P_Business_group_id NUMBER,
356 P_vac_date_to DATE) IS
357
358 CURSOR c_in_req_dt IS
359 SELECT 1
360 FROM PER_REQUISITIONS PR
361 WHERE PR.REQUISITION_ID = P_requisition_id
362 AND PR.business_group_id + 0 = P_Business_group_id
363 AND NVL(P_vac_date_to,to_date('31-12-4712','DD-MM-YYYY')) > PR.DATE_TO;
364
365
366 v_dummy NUMBER(1);
367 --
368 BEGIN
369 --
370 OPEN c_in_req_dt;
371 FETCH c_in_req_dt into v_dummy;
372 IF c_in_req_dt%found THEN
373 CLOSE c_in_req_dt;
374 fnd_message.set_name('PER','HR_6843_VACS_DATE_TO_VAC');
375 hr_utility.raise_error;
376 ELSE CLOSE c_in_req_dt;
377 END IF;
378 END Chk_dt_to_in_req_dates;
379 --
380 ----------------------------------------------------------------------------
381 -- Name --
382 -- Date_from_upd_validation --
383 -- Purpose --
384 -- Ensure that the vacancy date_from does not invalidate any of the --
385 -- vacancy_for region.
386 -----------------------------------------------------------------------------
387 PROCEDURE Date_from_upd_validation(
388 Pz_vac_date_from DATE,
389 Pz_business_group_id NUMBER,
390 Pz_start_of_time DATE,
391 Pz_end_of_time DATE,
392 Pz_organization_id NUMBER,
393 Pz_position_id NUMBER,
394 Pz_people_group_id NUMBER,
395 Pz_job_id NUMBER,
396 Pz_grade_id NUMBER,
397 Pz_recruiter_id NUMBER,
398 Pz_location_id NUMBER
399 ) IS
400
401 BEGIN
402 IF Pz_organization_id IS NOT NULL THEN
403 PER_VACANCIES_PKG.D_from_updt_org_chk(
404 P_Business_group_id => Pz_business_group_id,
405 P_vac_date_from => Pz_vac_date_from,
406 P_organization_id => Pz_organization_id);
407 END IF;
408
409 IF Pz_position_id IS NOT NULL THEN
410 PER_VACANCIES_PKG.D_from_updt_pos_chk(
411 P_Business_group_id => Pz_business_group_id,
412 P_vac_date_from => Pz_vac_date_from,
413 P_position_id => Pz_position_id);
414 END IF;
415
416 IF Pz_people_group_id IS NOT NULL THEN
417 PER_VACANCIES_PKG.D_from_updt_grp_chk(
418 P_vac_date_from => Pz_vac_date_from,
419 P_start_of_time => Pz_start_of_time,
420 P_people_group_id => Pz_people_group_id);
421 END IF;
422
423 IF Pz_job_id IS NOT NULL THEN
424 PER_VACANCIES_PKG.D_from_updt_job_chk(
425 P_vac_date_from => Pz_vac_date_from,
426 P_Business_group_id => Pz_business_group_id,
427 P_job_id => Pz_job_id);
428 END IF;
429
430 IF Pz_grade_id IS NOT NULL THEN
431 PER_VACANCIES_PKG.D_from_updt_grd_chk
432 (P_vac_date_from => Pz_vac_date_from,
433 P_business_group_id => Pz_business_group_id,
434 P_grade_id => Pz_grade_id);
435 END IF;
436
437 IF Pz_location_id IS NOT NULL THEN
438 PER_VACANCIES_PKG.D_from_updt_loc_chk(
439 P_vac_date_from => Pz_vac_date_from,
440 P_end_of_time => Pz_end_of_time,
441 P_location_id => Pz_location_id);
442 END IF;
443
444
445 IF Pz_recruiter_id IS NOT NULL THEN
446 PER_VACANCIES_PKG.D_from_updt_person(
447 P_vac_date_from => Pz_vac_date_from,
448 P_recruiter_id => Pz_recruiter_id,
449 P_business_group_id => Pz_business_group_id);
450 END IF;
451
452 END Date_from_upd_validation;
453
454 -----------------------------------------------------------------------------
455 -- Name --
456 -- D_from_updt_rec_act_chk --
457 -- Purpose --
458 -- Ensure that the vacancy date_from does not invalidate any recruitment --
459 -- activity that may be using the vacancy. --
460 -- Arguments --
461 -- see below. --
462 -----------------------------------------------------------------------------
463 PROCEDURE D_from_updt_rec_act_chk(P_vacancy_id NUMBER,
464 P_vac_date_from DATE,
465 P_vac_date_to DATE,
466 P_end_of_time DATE) IS
467
468 -- This cursor retrieves a row if there is a recruitment activity
469 -- using the given vacancy where either of its start/end dates
470 -- are outside the vacancy dates.
471 CURSOR c_rec_act_chk IS
472 SELECT 1
473 FROM PER_RECRUITMENT_ACTIVITY_FOR F,
474 PER_RECRUITMENT_ACTIVITIES ACTS
475 WHERE F.VACANCY_ID = P_vacancy_id
476 AND F.RECRUITMENT_ACTIVITY_ID = ACTS.RECRUITMENT_ACTIVITY_ID
477 AND ( ACTS.DATE_START < P_vac_date_from
478 OR nvl(ACTS.DATE_END,p_end_of_time) > nvl(P_vac_date_to, P_end_of_time) ) ;
479
480 --
481 v_dummy NUMBER(1);
482 --
483 BEGIN
484 --
485 -- stubbed for bug 6497289
486 hr_utility.set_location('Entering: D_from_updt_rec_act_chk',10);
487 /*
488 OPEN c_rec_act_chk;
489 FETCH c_rec_act_chk into v_dummy;
490 IF c_rec_act_chk%found THEN
491 CLOSE c_rec_act_chk;
492 fnd_message.set_name('PER','HR_6641_VACS_REC_ACTS');
493 hr_utility.raise_error;
494 ELSE CLOSE c_rec_act_chk;
495 END IF;
496 */
497 hr_utility.set_location('Leaving: D_from_updt_rec_act_chk',100);
498 --
499 END D_from_updt_rec_act_chk;
500 --
501 -----------------------------------------------------------------------------
502 -- Name --
503 -- D_from_updt_org_chk --
504 -- Purpose --
505 -- Ensure that the vacancy date_from does not invalidate the organization--
506 -- part of the vacancy. --
507 -- Arguments --
508 -- see below. --
509 -----------------------------------------------------------------------------
510 PROCEDURE D_from_updt_org_chk(P_Business_group_id NUMBER,
511 P_vac_date_from DATE,
512 P_organization_id NUMBER) IS
513
514 CURSOR c_org_chk IS
515 SELECT 1
516 FROM HR_ORGANIZATION_UNITS HOU
517 WHERE HOU.ORGANIZATION_ID = P_organization_id
518 AND HOU.business_group_id + 0 = P_Business_group_id
519 AND P_vac_date_from < HOU.DATE_FROM;
520
521 --
522 v_dummy NUMBER(1);
523 --
524 BEGIN
525 --
526 OPEN c_org_chk;
527 FETCH c_org_chk into v_dummy;
528 IF c_org_chk%found THEN
529 CLOSE c_org_chk;
530 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
531 fnd_message.set_token('PART','organization');
532 hr_utility.raise_error;
533 ELSE CLOSE c_org_chk;
534 END IF;
535 --
536 END D_from_updt_org_chk;
537 --
538 -----------------------------------------------------------------------------
539 -- Name --
540 -- D_to_updt_org_chk --
541 -- Purpose --
542 -- Ensure that the vacancy date_to does not invalidate the organization --
543 -- part of the vacancy. --
544 -- Arguments --
545 -- see below.
546 -----------------------------------------------------------------------------
547 --
548 PROCEDURE D_to_updt_org_chk(P_Business_group_id NUMBER,
549 P_vac_date_to DATE,
550 P_organization_id NUMBER) IS
551
552 CURSOR c_org_chk IS
553 SELECT date_to
554 FROM HR_ORGANIZATION_UNITS HOU
555 WHERE HOU.ORGANIZATION_ID = P_organization_id
556 AND HOU.business_group_id + 0 = P_Business_group_id
557 AND P_vac_date_to > nvl(HOU.date_to, hr_api.g_eot);
558
559 --
560 v_dummy NUMBER(1);
561 v_date Date;
562 --
563 BEGIN
564 --
565 OPEN c_org_chk;
566 FETCH c_org_chk into v_date;
567
568 IF c_org_chk%found THEN
569 CLOSE c_org_chk;
570 fnd_message.set_name('PER',' HR_289199_ORG_VACS_DATE_TO');
571 fnd_message.set_token('DATE',v_date);
572 hr_utility.raise_error;
573 ELSE CLOSE c_org_chk;
574 END IF;
575 --
576 END D_to_updt_org_chk;
577 -----------------------------------------------------------------------------
578 -- Name --
579 -- D_from_updt_pos_chk --
580 -- Purpose --
581 -- Ensure that the vacancy date_from does not invalidate the position --
582 -- part of the vacancy. --
583 -- Arguments --
584 -- see below. --
585 -----------------------------------------------------------------------------
586 PROCEDURE D_from_updt_pos_chk(P_Business_group_id NUMBER,
587 P_vac_date_from DATE,
588 P_position_id NUMBER) IS
589 --
590 -- Changed 05-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked positions requirement
591 CURSOR c_pos_chk IS
592 SELECT 1
593 FROM HR_POSITIONS_F POS
594 WHERE POS.POSITION_ID = P_position_id
595 AND POS.business_group_id + 0 = P_Business_group_id
596 AND P_vac_date_from < POS.DATE_EFFECTIVE;
597
598 --
599 v_dummy NUMBER(1);
600 --
601 BEGIN
602 --
603 OPEN c_pos_chk;
604 FETCH c_pos_chk into v_dummy;
605 IF c_pos_chk%found THEN
606 CLOSE c_pos_chk;
607 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
608 fnd_message.set_token('PART','position');
609 hr_utility.raise_error;
610 ELSE CLOSE c_pos_chk;
611 END IF;
612 END D_from_updt_pos_chk;
613 --
614 -----------------------------------------------------------------------------
615 -- Name --
616 -- D_from_updt_grp_chk --
617 -- Purpose --
618 -- Ensure that the vacancy date_from does not invalidate the group --
619 -- part of the vacancy. --
620 -- Arguments --
621 -- see below. --
622 -----------------------------------------------------------------------------
623 PROCEDURE D_from_updt_grp_chk(P_vac_date_from DATE,
624 P_start_of_time DATE,
625 P_people_group_id NUMBER) IS
626
627 CURSOR c_grp_chk IS
628 SELECT 1
629 FROM PAY_PEOPLE_GROUPS PPG
630 WHERE PPG.PEOPLE_GROUP_ID = P_people_group_id
631 AND P_vac_date_from < nvl(PPG.START_DATE_ACTIVE,
632 P_start_of_time);
633
634 --
635 v_dummy NUMBER(1);
636 --
637 BEGIN
638 --
639 OPEN c_grp_chk;
640 FETCH c_grp_chk into v_dummy;
641 IF c_grp_chk%found THEN
642 CLOSE c_grp_chk;
643 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
644 fnd_message.set_token('PART','group');
645 hr_utility.raise_error;
646 ELSE CLOSE c_grp_chk;
647 END IF;
648 END D_from_updt_grp_chk;
649 --
650 -----------------------------------------------------------------------------
651 -- Name --
652 -- D_from_updt_job_chk --
653 -- Purpose --
654 -- Ensure that the vacancy date_from does not invalidate the job --
655 -- part of the vacancy. --
656 -- Arguments --
657 -- see below. --
658 -----------------------------------------------------------------------------
659 PROCEDURE D_from_updt_job_chk(P_vac_date_from DATE,
660 P_business_group_id NUMBER,
661 P_job_id NUMBER) IS
662
663 CURSOR c_job_chk IS
664 SELECT 1
665 FROM PER_JOBS_V PJ
666 WHERE PJ.JOB_ID = P_job_id
667 AND PJ.business_group_id + 0 = P_business_group_id
668 AND P_vac_date_from < PJ.DATE_FROM;
669
670 --
671 v_dummy NUMBER(1);
672 --
673 BEGIN
674 --
675 OPEN c_job_chk;
676 FETCH c_job_chk into v_dummy;
677 IF c_job_chk%found THEN
678 CLOSE c_job_chk;
679 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
680 fnd_message.set_token('PART','job');
681 hr_utility.raise_error;
682 ELSE CLOSE c_job_chk;
683 END IF;
684 END D_from_updt_job_chk;
685 --
686 -----------------------------------------------------------------------------
687 -- Name --
688 -- D_from_updt_grd_chk --
689 -- Purpose --
690 -- Ensure that the vacancy date_from does not invalidate the grade --
691 -- part of the vacancy. --
692 -- Arguments --
693 -- see below. --
694 -----------------------------------------------------------------------------
695 PROCEDURE D_from_updt_grd_chk(P_vac_date_from DATE,
696 P_business_group_id NUMBER,
697 P_grade_id NUMBER) IS
698
699 CURSOR c_grade_chk IS
700 SELECT 1
701 FROM PER_GRADES PG
702 WHERE PG.GRADE_ID = P_grade_id
703 AND PG.business_group_id + 0 = P_business_group_id
704 AND P_vac_date_from < PG.DATE_FROM;
705
706 --
707 v_dummy NUMBER(1);
708 --
709 BEGIN
710 --
711 OPEN c_grade_chk;
712 FETCH c_grade_chk into v_dummy;
713 IF c_grade_chk%found THEN
714 CLOSE c_grade_chk;
715 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
716 fnd_message.set_token('PART','grade');
717 hr_utility.raise_error;
718 ELSE CLOSE c_grade_chk;
719 END IF;
720 END D_from_updt_grd_chk;
721 --
722 -----------------------------------------------------------------------------
723 -- Name --
724 -- D_from_updt_loc_chk --
725 -- Purpose --
726 -- Ensure that the vacancy date_from does not invalidate the location --
727 -- part of the vacancy. --
728 -- Arguments --
729 -- see below. --
730 -----------------------------------------------------------------------------
731 PROCEDURE D_from_updt_loc_chk(P_vac_date_from DATE,
732 P_end_of_time DATE,
733 P_location_id NUMBER) IS
734
735 CURSOR c_loc_chk IS
736 SELECT 1
737 FROM HR_LOCATIONS HL
738 WHERE HL.LOCATION_ID = P_location_id
739 AND P_vac_date_from > nvl(HL.INACTIVE_DATE,P_end_of_time);
740
741 --
742 v_dummy NUMBER(1);
743 --
744 BEGIN
745 --
746 OPEN c_loc_chk;
747 FETCH c_loc_chk into v_dummy;
748 IF c_loc_chk%found THEN
749 CLOSE c_loc_chk;
750 fnd_message.set_name('PER','HR_6188_REQS_VACS_DATE_FROM');
751 fnd_message.set_token('PART','location');
752 hr_utility.raise_error;
753 ELSE CLOSE c_loc_chk;
754 END IF;
755 END D_from_updt_loc_chk;
756 --
757 -----------------------------------------------------------------------------
758 -- Name --
759 -- D_from_updt_person
760 -- Purpose --
761 -- Ensure that the vacancy date_from does not invalidate the recruiter --
762 -- part of the vacancy. --
763 -- Arguments --
764 -- see below. --
765 -----------------------------------------------------------------------------
766 PROCEDURE D_from_updt_person(P_vac_date_from DATE,
767 P_recruiter_id NUMBER,
768 P_business_group_id NUMBER) IS
769 -- bug 4475075 in the following cursor commented out the business group
770 -- validation condition and redifined .
771 CURSOR c_person IS
772 SELECT 1
773 FROM PER_ALL_PEOPLE_F P
774 WHERE P.PERSON_ID = P_recruiter_id
775 -- AND P.business_group_id + 0 = P_business_group_id
776 AND ( P.business_group_id = P_business_group_id or
777 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
778 AND P_vac_date_from BETWEEN p.effective_start_date
779 AND p.effective_end_date;
780 -- bug 447505 ends here
781 --
782 v_dummy NUMBER(1);
783 --
784 BEGIN
785 --
786 OPEN c_person;
787 FETCH c_person into v_dummy;
788 IF c_person%notfound THEN
789 CLOSE c_person;
790 fnd_message.set_name('PER','HR_6642_VACS_RECRUITER');
791 hr_utility.raise_error;
792 ELSE CLOSE c_person;
793 END IF;
794 END D_from_updt_person;
795 --
796 -----------------------------------------------------------------------------
797 -- Name --
798 -- get_people_group_id
799 -- Purpose --
800 -- to get the people_group_structure for the group key flexfield in the --
801 -- vacancy zone of PERWSVAC. --
802 -- Arguments --
803 -- see below. --
804 -----------------------------------------------------------------------------
805 FUNCTION get_people_group(P_Business_Group_id NUMBER) return VARCHAR2 IS
806
807 CURSOR c_pg IS
808 Select people_group_structure
809 From per_business_groups
810 Where business_group_id + 0 = P_Business_Group_id;
811
812 --
813 v_people_group_structure VARCHAR2(240);
814 --
815 BEGIN
816 OPEN c_pg;
817 FETCH c_pg into v_people_group_structure;
818 CLOSE c_pg;
819 RETURN v_people_group_structure;
820 END get_people_group;
821 -----------------------------------------------------------------------------
822
823 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
824 X_Vacancy_Id IN OUT NOCOPY NUMBER,
825 X_Business_Group_Id NUMBER,
826 X_Position_Id NUMBER,
827 X_Job_Id NUMBER,
828 X_Grade_Id NUMBER,
829 X_Organization_Id NUMBER,
830 X_Requisition_Id NUMBER,
831 X_People_Group_Id NUMBER,
832 X_People_Group_Name VARCHAR2,
833 X_Location_Id NUMBER,
834 X_Recruiter_Id NUMBER,
835 X_Date_From DATE,
836 X_Name VARCHAR2,
837 X_Comments VARCHAR2,
838 X_Date_To DATE,
839 X_Description VARCHAR2,
840 X_Vacancy_category varchar2,
841 X_Number_Of_Openings NUMBER,
842 X_Status VARCHAR2,
843 X_Budget_Measurement_Type VARCHAR2,
844 X_Budget_Measurement_Value NUMBER,
845 X_Attribute_Category VARCHAR2,
846 X_Attribute1 VARCHAR2,
847 X_Attribute2 VARCHAR2,
848 X_Attribute3 VARCHAR2,
849 X_Attribute4 VARCHAR2,
850 X_Attribute5 VARCHAR2,
851 X_Attribute6 VARCHAR2,
852 X_Attribute7 VARCHAR2,
853 X_Attribute8 VARCHAR2,
854 X_Attribute9 VARCHAR2,
855 X_Attribute10 VARCHAR2,
856 X_Attribute11 VARCHAR2,
857 X_Attribute12 VARCHAR2,
858 X_Attribute13 VARCHAR2,
859 X_Attribute14 VARCHAR2,
860 X_Attribute15 VARCHAR2,
861 X_Attribute16 VARCHAR2,
862 X_Attribute17 VARCHAR2,
863 X_Attribute18 VARCHAR2,
864 X_Attribute19 VARCHAR2,
865 X_Attribute20 VARCHAR2 )
866 IS
867 CURSOR C IS SELECT rowid
868 FROM PER_VACANCIES
869 WHERE vacancy_id = X_Vacancy_Id;
870
871
872 CURSOR C2 IS SELECT per_vacancies_s.nextval
873 FROM sys.dual;
874 BEGIN
875
876 if (X_Vacancy_Id is NULL) then
877 OPEN C2;
878 FETCH C2 INTO X_Vacancy_Id;
879 CLOSE C2;
880 end if;
881 CHK_POS_BUDGET_VAL(X_Position_Id,X_Date_From,X_Organization_Id,X_Number_Of_Openings,X_Vacancy_Id);
882 INSERT INTO PER_VACANCIES(
883 vacancy_id,
884 business_group_id,
885 position_id,
886 job_id,
887 grade_id,
888 organization_id,
889 requisition_id,
890 people_group_id,
891 location_id,
892 recruiter_id,
893 date_from,
894 name,
895 comments,
896 date_to,
897 description,
898 vacancy_category,
899 number_of_openings,
900 status,
901 budget_measurement_type,
902 budget_measurement_value,
903 attribute_category,
904 attribute1,
905 attribute2,
906 attribute3,
907 attribute4,
908 attribute5,
909 attribute6,
910 attribute7,
911 attribute8,
912 attribute9,
913 attribute10,
914 attribute11,
915 attribute12,
916 attribute13,
917 attribute14,
918 attribute15,
919 attribute16,
920 attribute17,
921 attribute18,
922 attribute19,
923 attribute20
924 ) VALUES (
925 X_Vacancy_Id,
926 X_Business_Group_Id,
927 X_Position_Id,
928 X_Job_Id,
929 X_Grade_Id,
930 X_Organization_Id,
931 X_Requisition_Id,
932 X_People_Group_Id,
933 X_Location_Id,
934 X_Recruiter_Id,
935 X_Date_From,
936 X_Name,
937 X_Comments,
938 X_Date_To,
939 X_Description,
940 X_vacancy_category,
941 X_Number_Of_Openings,
942 X_Status,
943 X_Budget_Measurement_Type,
944 X_Budget_Measurement_Value,
945 X_Attribute_Category,
946 X_Attribute1,
947 X_Attribute2,
948 X_Attribute3,
949 X_Attribute4,
950 X_Attribute5,
951 X_Attribute6,
952 X_Attribute7,
953 X_Attribute8,
954 X_Attribute9,
955 X_Attribute10,
956 X_Attribute11,
957 X_Attribute12,
958 X_Attribute13,
959 X_Attribute14,
960 X_Attribute15,
961 X_Attribute16,
962 X_Attribute17,
963 X_Attribute18,
964 X_Attribute19,
965 X_Attribute20 );
966
967 OPEN C;
968 FETCH C INTO X_Rowid;
969 if (C%NOTFOUND) then
970 CLOSE C;
971 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
972 hr_utility.set_message_token('PROCEDURE',
973 'per_vacancies_pkg.insert_row');
974 hr_utility.set_message_token('STEP','1');
975 hr_utility.raise_error;
976 end if;
977 CLOSE C;
978 --
979 per_applicant_pkg.update_group ( x_people_group_id,
980 x_people_group_name ) ;
981 --
982 END Insert_Row;
983 --
984 -----------------------------------------------------------------------------
985 -- Name --
986 -- Lock_Row --
987 -- Purpose --
988 -- Table handler procedure that supports the insert , update and delete --
989 -- of a vacancy by applying a lock on a vacancy in the Define --
990 -- Requisition and Vacnacy form. --
991 -- Arguments --
992 -- Notes --
993 -----------------------------------------------------------------------------
994 --
995 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
996 X_Vacancy_Id NUMBER,
997 X_Business_Group_Id NUMBER,
998 X_Position_Id NUMBER,
999 X_Job_Id NUMBER,
1000 X_Grade_Id NUMBER,
1001 X_Organization_Id NUMBER,
1002 X_Requisition_Id NUMBER,
1003 X_People_Group_Id NUMBER,
1004 X_Location_Id NUMBER,
1005 X_Recruiter_Id NUMBER,
1006 X_Date_From DATE,
1007 X_Name VARCHAR2,
1008 X_Comments VARCHAR2,
1009 X_Date_To DATE,
1010 X_Description VARCHAR2,
1011 X_Vacancy_category varchar2,
1012 X_Number_Of_Openings NUMBER,
1013 X_Status VARCHAR2,
1014 X_Budget_Measurement_Type VARCHAR2,
1015 X_Budget_Measurement_Value NUMBER,
1016 X_Attribute_Category VARCHAR2,
1017 X_Attribute1 VARCHAR2,
1018 X_Attribute2 VARCHAR2,
1019 X_Attribute3 VARCHAR2,
1020 X_Attribute4 VARCHAR2,
1021 X_Attribute5 VARCHAR2,
1022 X_Attribute6 VARCHAR2,
1023 X_Attribute7 VARCHAR2,
1024 X_Attribute8 VARCHAR2,
1025 X_Attribute9 VARCHAR2,
1026 X_Attribute10 VARCHAR2,
1027 X_Attribute11 VARCHAR2,
1028 X_Attribute12 VARCHAR2,
1029 X_Attribute13 VARCHAR2,
1030 X_Attribute14 VARCHAR2,
1031 X_Attribute15 VARCHAR2,
1032 X_Attribute16 VARCHAR2,
1033 X_Attribute17 VARCHAR2,
1034 X_Attribute18 VARCHAR2,
1035 X_Attribute19 VARCHAR2,
1036 X_Attribute20 VARCHAR2)
1037 IS
1038 CURSOR C IS
1039 SELECT *
1040 FROM PER_VACANCIES
1041 WHERE rowid = X_Rowid
1042 FOR UPDATE of Vacancy_Id NOWAIT;
1043 BEGIN
1044 OPEN C;
1045 FETCH C INTO g_Recinfo;
1046 if (C%NOTFOUND) then
1047 CLOSE C;
1048 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1049 hr_utility.set_message_token('PROCEDURE',
1050 'per_vacancies_pkb.lock_row');
1051 hr_utility.set_message_token('STEP','1');
1052 hr_utility.raise_error;
1053 end if;
1054 CLOSE C;
1055 --
1056 g_Recinfo.attribute18 := rtrim(g_Recinfo.attribute18);
1057 g_Recinfo.attribute19 := rtrim(g_Recinfo.attribute19);
1058 g_Recinfo.attribute20 := rtrim(g_Recinfo.attribute20);
1059 g_Recinfo.name := rtrim(g_Recinfo.name);
1060 g_Recinfo.comments := rtrim(g_Recinfo.comments);
1061 g_Recinfo.description := rtrim(g_Recinfo.description);
1062 g_Recinfo.vacancy_category := rtrim(g_Recinfo.vacancy_category);
1063 g_Recinfo.status := rtrim(g_Recinfo.status);
1064 g_Recinfo.budget_measurement_type := rtrim(g_Recinfo.budget_measurement_type);
1065 g_Recinfo.attribute_category := rtrim(g_Recinfo.attribute_category);
1066 g_Recinfo.attribute1 := rtrim(g_Recinfo.attribute1);
1067 g_Recinfo.attribute2 := rtrim(g_Recinfo.attribute2);
1068 g_Recinfo.attribute3 := rtrim(g_Recinfo.attribute3);
1069 g_Recinfo.attribute4 := rtrim(g_Recinfo.attribute4);
1070 g_Recinfo.attribute5 := rtrim(g_Recinfo.attribute5);
1071 g_Recinfo.attribute6 := rtrim(g_Recinfo.attribute6);
1072 g_Recinfo.attribute7 := rtrim(g_Recinfo.attribute7);
1073 g_Recinfo.attribute8 := rtrim(g_Recinfo.attribute8);
1074 g_Recinfo.attribute9 := rtrim(g_Recinfo.attribute9);
1075 g_Recinfo.attribute10 := rtrim(g_Recinfo.attribute10);
1076 g_Recinfo.attribute11 := rtrim(g_Recinfo.attribute11);
1077 g_Recinfo.attribute12 := rtrim(g_Recinfo.attribute12);
1078 g_Recinfo.attribute13 := rtrim(g_Recinfo.attribute13);
1079 g_Recinfo.attribute14 := rtrim(g_Recinfo.attribute14);
1080 g_Recinfo.attribute15 := rtrim(g_Recinfo.attribute15);
1081 g_Recinfo.attribute16 := rtrim(g_Recinfo.attribute16);
1082 g_Recinfo.attribute17 := rtrim(g_Recinfo.attribute17);
1083 --
1084 if (
1085 ( (g_Recinfo.vacancy_id = X_Vacancy_Id)
1086 OR ( (g_Recinfo.vacancy_id IS NULL)
1087 AND (X_Vacancy_Id IS NULL)))
1088 AND ( (g_Recinfo.business_group_id = X_Business_Group_Id)
1089 OR ( (g_Recinfo.business_group_id IS NULL)
1090 AND (X_Business_Group_Id IS NULL)))
1091 AND ( (g_Recinfo.position_id = X_Position_Id)
1092 OR ( (g_Recinfo.position_id IS NULL)
1093 AND (X_Position_Id IS NULL)))
1094 AND ( (g_Recinfo.job_id = X_Job_Id)
1095 OR ( (g_Recinfo.job_id IS NULL)
1096 AND (X_Job_Id IS NULL)))
1097 AND ( (g_Recinfo.grade_id = X_Grade_Id)
1098 OR ( (g_Recinfo.grade_id IS NULL)
1099 AND (X_Grade_Id IS NULL)))
1100 AND ( (g_Recinfo.organization_id = X_Organization_Id)
1101 OR ( (g_Recinfo.organization_id IS NULL)
1102 AND (X_Organization_Id IS NULL)))
1103 AND ( (g_Recinfo.requisition_id = X_Requisition_Id)
1104 OR ( (g_Recinfo.requisition_id IS NULL)
1105 AND (X_Requisition_Id IS NULL)))
1106 AND ( (g_Recinfo.people_group_id = X_People_Group_Id)
1107 OR ( (g_Recinfo.people_group_id IS NULL)
1108 AND (X_People_Group_Id IS NULL)))
1109 AND ( (g_Recinfo.location_id = X_Location_Id)
1110 OR ( (g_Recinfo.location_id IS NULL)
1111 AND (X_Location_Id IS NULL)))
1112 AND ( (g_Recinfo.recruiter_id = X_Recruiter_Id)
1113 OR ( (g_Recinfo.recruiter_id IS NULL)
1114 AND (X_Recruiter_Id IS NULL)))
1115 AND ( (g_Recinfo.date_from = X_Date_From)
1116 OR ( (g_Recinfo.date_from IS NULL)
1117 AND (X_Date_From IS NULL)))
1118 AND ( (g_Recinfo.name = X_Name)
1119 OR ( (g_Recinfo.name IS NULL)
1120 AND (X_Name IS NULL)))
1121 AND ( (g_Recinfo.comments = X_Comments)
1122 OR ( (g_Recinfo.comments IS NULL)
1123 AND (X_Comments IS NULL)))
1124 AND ( (g_Recinfo.date_to = X_Date_To)
1125 OR ( (g_Recinfo.date_to IS NULL)
1126 AND (X_Date_To IS NULL)))
1127 AND ( (g_Recinfo.description = X_Description)
1128 OR ( (g_Recinfo.description IS NULL)
1129 AND (X_Description IS NULL)))
1130 AND ( (g_Recinfo.vacancy_category = X_vacancy_category)
1131 OR ( (g_Recinfo.vacancy_category IS NULL)
1132 AND (X_vacancy_category IS NULL)))
1133 AND ( (g_Recinfo.number_of_openings = X_Number_Of_Openings)
1134 OR ( (g_Recinfo.number_of_openings IS NULL)
1135 AND (X_Number_Of_Openings IS NULL)))
1136 AND ( (g_Recinfo.status = X_Status)
1137 OR ( (g_Recinfo.status IS NULL)
1138 AND (X_Status IS NULL)))
1139 AND ( (g_Recinfo.budget_measurement_type = X_Budget_Measurement_Type)
1140 OR ( (g_Recinfo.budget_measurement_type IS NULL)
1141 AND (X_Budget_Measurement_Type IS NULL)))
1142 AND ( (g_Recinfo.budget_measurement_value = X_Budget_Measurement_Value)
1143 OR ( (g_Recinfo.budget_measurement_value IS NULL)
1144 AND (X_Budget_Measurement_Value IS NULL)))
1145 AND ( (g_Recinfo.attribute_category = X_Attribute_Category)
1146 OR ( (g_Recinfo.attribute_category IS NULL)
1147 AND (X_Attribute_Category IS NULL)))
1148 AND ( (g_Recinfo.attribute1 = X_Attribute1)
1149 OR ( (g_Recinfo.attribute1 IS NULL)
1150 AND (X_Attribute1 IS NULL)))
1151 AND ( (g_Recinfo.attribute2 = X_Attribute2)
1152 OR ( (g_Recinfo.attribute2 IS NULL)
1153 AND (X_Attribute2 IS NULL)))
1154 AND ( (g_Recinfo.attribute3 = X_Attribute3)
1155 OR ( (g_Recinfo.attribute3 IS NULL)
1156 AND (X_Attribute3 IS NULL)))
1157 AND ( (g_Recinfo.attribute4 = X_Attribute4)
1158 OR ( (g_Recinfo.attribute4 IS NULL)
1159 AND (X_Attribute4 IS NULL)))
1160 AND ( (g_Recinfo.attribute5 = X_Attribute5)
1161 OR ( (g_Recinfo.attribute5 IS NULL)
1162 AND (X_Attribute5 IS NULL)))
1163 AND ( (g_Recinfo.attribute6 = X_Attribute6)
1164 OR ( (g_Recinfo.attribute6 IS NULL)
1165 AND (X_Attribute6 IS NULL)))
1166 AND ( (g_Recinfo.attribute7 = X_Attribute7)
1167 OR ( (g_Recinfo.attribute7 IS NULL)
1168 AND (X_Attribute7 IS NULL)))
1169 AND ( (g_Recinfo.attribute8 = X_Attribute8)
1170 OR ( (g_Recinfo.attribute8 IS NULL)
1171 AND (X_Attribute8 IS NULL)))
1172 AND ( (g_Recinfo.attribute9 = X_Attribute9)
1173 OR ( (g_Recinfo.attribute9 IS NULL)
1174 AND (X_Attribute9 IS NULL)))
1175 AND ( (g_Recinfo.attribute10 = X_Attribute10)
1176 OR ( (g_Recinfo.attribute10 IS NULL)
1177 AND (X_Attribute10 IS NULL)))
1178 AND ( (g_Recinfo.attribute11 = X_Attribute11)
1179 OR ( (g_Recinfo.attribute11 IS NULL)
1180 AND (X_Attribute11 IS NULL)))
1181 AND ( (g_Recinfo.attribute12 = X_Attribute12)
1182 OR ( (g_Recinfo.attribute12 IS NULL)
1183 AND (X_Attribute12 IS NULL)))
1184 AND ( (g_Recinfo.attribute13 = X_Attribute13)
1185 OR ( (g_Recinfo.attribute13 IS NULL)
1186 AND (X_Attribute13 IS NULL)))
1187 AND ( (g_Recinfo.attribute14 = X_Attribute14)
1188 OR ( (g_Recinfo.attribute14 IS NULL)
1189 AND (X_Attribute14 IS NULL)))
1190 AND ( (g_Recinfo.attribute15 = X_Attribute15)
1191 OR ( (g_Recinfo.attribute15 IS NULL)
1192 AND (X_Attribute15 IS NULL)))
1193 AND ( (g_Recinfo.attribute16 = X_Attribute16)
1194 OR ( (g_Recinfo.attribute16 IS NULL)
1195 AND (X_Attribute16 IS NULL)))
1196 AND ( (g_Recinfo.attribute17 = X_Attribute17)
1197 OR ( (g_Recinfo.attribute17 IS NULL)
1198 AND (X_Attribute17 IS NULL)))
1199 AND ( (g_Recinfo.attribute18 = X_Attribute18)
1200 OR ( (g_Recinfo.attribute18 IS NULL)
1201 AND (X_Attribute18 IS NULL)))
1202 AND ( (g_Recinfo.attribute19 = X_Attribute19)
1203 OR ( (g_Recinfo.attribute19 IS NULL)
1204 AND (X_Attribute19 IS NULL)))
1205 AND ( (g_Recinfo.attribute20 = X_Attribute20)
1206 OR ( (g_Recinfo.attribute20 IS NULL)
1207 AND (X_Attribute20 IS NULL)))
1208 ) then
1209 return;
1210 else
1211 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1212 APP_EXCEPTION.RAISE_EXCEPTION;
1213 end if;
1214 END Lock_Row;
1215 --
1216 -----------------------------------------------------------------------------
1217 -- Name --
1218 -- Update_Row --
1219 -- Purpose --
1220 -- Table handler procedure that supports the update of a VACANCY via --
1221 -- Define Requistion and Vacancy form. --
1222 -- Arguments --
1223 -- See below. --
1224 -- Notes --
1225 -- None. --
1226 -----------------------------------------------------------------------------
1227 --
1228 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1229 X_Vacancy_Id NUMBER,
1230 X_Business_Group_Id NUMBER,
1231 X_Position_Id NUMBER,
1232 X_Job_Id NUMBER,
1233 X_Grade_Id NUMBER,
1234 X_Organization_Id NUMBER,
1235 X_Requisition_Id NUMBER,
1236 X_People_Group_Id NUMBER,
1237 X_People_Group_Name VARCHAR2,
1238 X_Location_Id NUMBER,
1239 X_Recruiter_Id NUMBER,
1240 X_Date_From DATE,
1241 X_Name VARCHAR2,
1242 X_Comments VARCHAR2,
1243 X_Date_To DATE,
1244 X_Description VARCHAR2,
1245 X_Vacancy_category varchar2,
1246 X_Number_Of_Openings NUMBER,
1247 X_Status VARCHAR2,
1248 X_Budget_Measurement_Type VARCHAR2,
1249 X_Budget_Measurement_Value NUMBER,
1250 X_Attribute_Category VARCHAR2,
1251 X_Attribute1 VARCHAR2,
1252 X_Attribute2 VARCHAR2,
1253 X_Attribute3 VARCHAR2,
1254 X_Attribute4 VARCHAR2,
1255 X_Attribute5 VARCHAR2,
1256 X_Attribute6 VARCHAR2,
1257 X_Attribute7 VARCHAR2,
1258 X_Attribute8 VARCHAR2,
1259 X_Attribute9 VARCHAR2,
1260 X_Attribute10 VARCHAR2,
1261 X_Attribute11 VARCHAR2,
1262 X_Attribute12 VARCHAR2,
1263 X_Attribute13 VARCHAR2,
1264 X_Attribute14 VARCHAR2,
1265 X_Attribute15 VARCHAR2,
1266 X_Attribute16 VARCHAR2,
1267 X_Attribute17 VARCHAR2,
1268 X_Attribute18 VARCHAR2,
1269 X_Attribute19 VARCHAR2,
1270 X_Attribute20 VARCHAR2
1271 ) IS
1272 l_end_of_time date := hr_api.g_eot;
1273 BEGIN
1274 --
1275 IF X_Organization_Id is not null then
1276 PER_VACANCIES_PKG.D_to_updt_org_chk(P_Business_group_id => X_Business_group_id
1277 ,P_vac_date_to => X_Date_To
1278 ,P_organization_id => X_Organization_Id);
1279 end if;
1280 --
1281 CHK_POS_BUDGET_VAL(X_Position_Id,X_Date_From,X_Organization_Id,X_Number_Of_Openings,X_Vacancy_Id);
1282 UPDATE PER_VACANCIES
1283 SET
1284 vacancy_id = X_Vacancy_Id,
1285 business_group_id = X_Business_Group_Id,
1286 position_id = X_Position_Id,
1287 job_id = X_Job_Id,
1288 grade_id = X_Grade_Id,
1289 organization_id = X_Organization_Id,
1290 requisition_id = X_Requisition_Id,
1291 people_group_id = X_People_Group_Id,
1292 location_id = X_Location_Id,
1293 recruiter_id = X_Recruiter_Id,
1294 date_from = X_Date_From,
1295 name = X_Name,
1296 comments = X_Comments,
1297 date_to = X_Date_To,
1298 description = X_Description,
1299 vacancy_category = X_Vacancy_category,
1300 number_of_openings = X_Number_Of_Openings,
1301 status = X_Status,
1302 budget_measurement_type = X_Budget_Measurement_Type,
1303 budget_measurement_value = X_Budget_Measurement_Value,
1304 attribute_category = X_Attribute_Category,
1305 attribute1 = X_Attribute1,
1306 attribute2 = X_Attribute2,
1307 attribute3 = X_Attribute3,
1308 attribute4 = X_Attribute4,
1309 attribute5 = X_Attribute5,
1310 attribute6 = X_Attribute6,
1311 attribute7 = X_Attribute7,
1312 attribute8 = X_Attribute8,
1313 attribute9 = X_Attribute9,
1314 attribute10 = X_Attribute10,
1315 attribute11 = X_Attribute11,
1316 attribute12 = X_Attribute12,
1317 attribute13 = X_Attribute13,
1318 attribute14 = X_Attribute14,
1319 attribute15 = X_Attribute15,
1320 attribute16 = X_Attribute16,
1321 attribute17 = X_Attribute17,
1322 attribute18 = X_Attribute18,
1323 attribute19 = X_Attribute19,
1324 attribute20 = X_Attribute20
1325 WHERE rowid = X_rowid;
1326
1327 if (SQL%NOTFOUND) then
1328 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1329 hr_utility.set_message_token('PROCEDURE',
1330 'per_vacancies_pkb.update_row');
1331 hr_utility.set_message_token('STEP','1');
1332 hr_utility.raise_error;
1333 end if;
1334
1335
1336 -- Cascade changes to applicants
1337
1338 -- Details are only cascaded to the applicant assignments when the value
1339 -- is not null, except for position, which updates the value if the
1340 -- organization and job have changed regardless of whether it is null.
1341 --
1342
1343 -- Start changes for bug 8518955
1344 update per_all_assignments_f asg
1345 set asg.organization_id = nvl(x_organization_id, asg.organization_id)
1346 ,asg.job_id = x_job_id
1347 ,asg.grade_id = x_grade_id
1348 ,asg.people_group_id = x_people_group_id
1349 ,asg.location_id = x_location_id
1350 ,asg.recruiter_id = x_recruiter_id
1351 ,asg.position_id = x_position_id
1352 /*,asg.job_id = nvl(x_job_id, asg.job_id)
1353 ,asg.grade_id = nvl(x_grade_id, asg.grade_id)
1354 ,asg.people_group_id = nvl(x_people_group_id, asg.people_group_id)
1355 ,asg.location_id = nvl(x_location_id, asg.location_id)
1356 ,asg.recruiter_id = nvl(x_recruiter_id, asg.recruiter_id)
1357 ,asg.position_id = decode
1358 (x_organization_id||'.'||x_job_id,
1359 g_recinfo.organization_id||'.'||g_recinfo.job_id,
1360 nvl(x_position_id, asg.position_id),
1361 x_position_id)*/
1362 where asg.assignment_type = 'A'
1363 and exists ( select 1
1364 from per_all_assignments_f f2
1365 where asg.assignment_id = f2.assignment_id
1366 and f2.effective_end_date = l_end_of_time )
1367 and not exists ( select 1
1368 from per_all_assignments_f f2
1369 where asg.assignment_id = f2.assignment_id
1370 and f2.assignment_status_type_id in ( select assignment_status_type_id
1371 from per_assignment_status_types
1372 where per_system_status in ('ACCEPTED')))
1373
1374 and asg.vacancy_id = x_vacancy_id
1375 and ( asg.organization_id <> nvl(x_organization_id,
1376 asg.organization_id)
1377 or nvl(asg.job_id, -1) <> nvl(x_job_id, -1)
1378 or nvl(asg.grade_id, -1) <> nvl(x_grade_id,-1)
1379 or nvl(asg.people_group_id, -1) <> nvl(x_people_group_id,-1)
1380 or nvl(asg.position_id, -1) <> nvl(x_position_id,-1)
1381 or nvl(asg.location_id, -1) <> nvl(x_location_id, -1)
1382 or nvl(asg.recruiter_id, -1) <> nvl(x_recruiter_id,-1)
1383 );
1384
1385 -- End changes for bug 8518955
1386 --
1387 per_applicant_pkg.update_group ( x_people_group_id,
1388 x_people_group_name ) ;
1389 --
1390 END Update_Row;
1391 --
1392 -----------------------------------------------------------------------------
1393 -- Name --
1394 -- Delete_Row --
1395 -- Purpose --
1396 -- Table handler procedure that supports the delete of a VACANCY via --
1397 -- the Define Requistion and Vacancy form. --
1398 -- Arguments --
1399 -- See below. --
1400 -- Notes --
1401 -- --
1402 -----------------------------------------------------------------------------
1403 --
1404 PROCEDURE Delete_Row(X_Rowid VARCHAR2 , x_vacancy_id in number ) IS
1405 BEGIN
1406 check_references( x_vacancy_id ) ;
1407 DELETE FROM PER_VACANCIES
1408 WHERE rowid = X_Rowid;
1409
1410 if (SQL%NOTFOUND) then
1411 RAISE NO_DATA_FOUND;
1412 end if;
1413 END Delete_Row;
1414
1415 -- start changes for bug 6497289
1416 --
1417 -----------------------------------------------------------------------------
1418 -- Name --
1419 -- end_date_iRec_RA --
1420 -- Purpose --
1421 -- End-Date the i-Rec Site Recruitment Activity --
1422 -- Arguments --
1423 -- See below. --
1424 -- Notes --
1425 -- --
1426 -----------------------------------------------------------------------------
1427 --
1428 PROCEDURE end_date_irec_RA(P_vacancy_id IN NUMBER,
1429 P_vac_date_from IN DATE,
1430 P_vac_date_to IN DATE) IS
1431
1432 l_object_version_number number;
1433 l_return_status varchar2(20);
1434
1435 --
1436 CURSOR c_get_irec_site_RA is
1437 select pra.recruitment_activity_id, pra.object_version_number
1438 from per_recruitment_activities pra, per_recruitment_activity_for praf
1439 where pra.recruitment_activity_id = praf.recruitment_activity_id
1440 and praf.vacancy_id = P_vacancy_id
1441 and pra.posting_content_id is not NULL
1442 and pra.recruiting_site_id is not NULL
1443 AND nvl(pra.date_end,to_date('31/12/4712','dd/mm/yyyy')) > p_vac_date_to;
1444 --
1445
1446 begin
1447
1448 hr_utility.set_location('Entering: end_date_irec_RA',10);
1449
1450 hr_utility.set_location(' P_vacancy_id:'||P_vacancy_id,11);
1451 hr_utility.set_location(' P_vac_date_from:'||P_vac_date_from,12);
1452 hr_utility.set_location(' P_vac_date_to:'||P_vac_date_to,13);
1453
1454 FOR Irec_site_RA IN c_get_irec_site_RA
1455 LOOP
1456 --
1457 hr_utility.set_location(' i-recruitment_activity_id:'||Irec_site_RA.recruitment_activity_id,15);
1458
1459 l_object_version_number := Irec_site_RA.object_version_number;
1460
1461 per_recruitment_activity_swi.update_recruitment_activity
1462 (p_recruitment_activity_id => Irec_site_RA.recruitment_activity_id
1463 ,p_date_end => P_vac_date_to
1464 ,p_object_version_number => l_object_version_number
1465 ,p_return_status => l_return_status
1466 );
1467
1468 hr_utility.set_location(' l_return_status:'||l_return_status,16);
1469 --
1470 END loop;
1471
1472 hr_utility.set_location('Leaving: end_date_irec_RA',100);
1473
1474 END end_date_irec_RA;
1475 --
1476
1477 --
1478 -----------------------------------------------------------------------------
1479 -- Name --
1480 -- end_date_per_RA --
1481 -- Purpose --
1482 -- End-Date the PER Recruitment Activity --
1483 -- Arguments --
1484 -- See below. --
1485 -- Notes --
1486 -- --
1487 -----------------------------------------------------------------------------
1488 --
1489 PROCEDURE end_date_PER_RA(P_vacancy_id IN NUMBER,
1490 P_recruitment_activity_id IN NUMBER,
1491 P_vac_date_from IN DATE,
1492 P_vac_date_to IN DATE) IS
1493
1494 l_object_version_number NUMBER ;
1495 l_recruitment_activity_id NUMBER ;
1496 l_return_status VARCHAR2(20);
1497
1498 --
1499 CURSOR c_per_vac_RA IS
1500 SELECT pra.recruitment_activity_id,pra.object_version_number
1501 FROM per_recruitment_activities pra, per_recruitment_activity_for praf
1502 WHERE pra.recruitment_activity_id = praf.recruitment_activity_id
1503 AND praf.vacancy_id = P_vacancy_id
1504 AND pra.recruitment_activity_id = P_recruitment_activity_id
1505 AND pra.posting_content_id is null
1506 AND pra.recruiting_site_id is null;
1507 --
1508
1509 BEGIN
1510
1511 hr_utility.set_location('Entering: end_date_per_RA',10);
1512
1513 hr_utility.set_location(' P_vacancy_id: '||P_vacancy_id,11);
1514 hr_utility.set_location(' P_recruitment_activity_id: '||P_recruitment_activity_id,12);
1515 hr_utility.set_location(' P_vac_date_from: '||P_vac_date_from,13);
1516 hr_utility.set_location(' P_vac_date_to: '||P_vac_date_to,14);
1517
1518 OPEN c_per_vac_RA;
1519 FETCH c_per_vac_RA INTO l_recruitment_activity_id, l_object_version_number;
1520
1521 IF c_per_vac_RA%FOUND THEN
1522 CLOSE c_per_vac_RA;
1523 per_recruitment_activity_swi.update_recruitment_activity
1524 (p_recruitment_activity_id => l_recruitment_activity_id
1525 ,p_date_end => P_vac_date_to
1526 ,p_object_version_number => l_object_version_number
1527 ,p_return_status => l_return_status
1528 );
1529 hr_utility.set_location(' l_return_status: '||l_return_status,15);
1530 ELSE
1531 CLOSE c_per_vac_RA;
1532 END if;
1533
1534 --
1535 hr_utility.set_location('Leaving: end_date_per_RA',100);
1536
1537 END end_date_per_RA;
1538 --end changes for bug 6497289
1539 -- Begin - Changes for ER#8530112
1540
1541 function GET_POS_HC_BUDGET_VAL(p_position_id in number default null,
1542 p_effective_date in date) return number is
1543
1544 --
1545 l_calendar varchar2(200);
1546 l_budget_id number;
1547 l_budget_unit1_id number;
1548 l_budget_unit2_id number;
1549 l_budget_unit3_id number;
1550 l_unit1_name varchar2(200);
1551 l_unit2_name varchar2(200);
1552 l_unit3_name varchar2(200);
1553 l_budgeted_hc number;
1554 l_business_group_id number;
1555
1556 --get the business_group_id
1557 cursor c_bus_grp_id(p_position_id number) is
1558 select business_group_id
1559 from hr_all_positions_f
1560 where position_id = p_position_id;
1561
1562 --get the budget_id and budget_unit_id
1563 cursor c_budget_id(p_business_group_id number) is
1564 select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
1565 from pqh_budgets
1566 where position_control_flag = 'Y'
1567 and budgeted_entity_cd = 'POSITION'
1568 and business_group_id = l_business_group_id
1569 and p_effective_date between budget_start_date and budget_end_date
1570 and (
1571 hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'HEAD'
1572 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'HEAD'
1573 or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'HEAD'
1574 );
1575
1576 --get the system type based on budget_nuit_id
1577 cursor c1(p_unit_id number) is
1578 select system_type_cd from
1579 per_shared_types where shared_type_id = p_unit_id;
1580
1581 --get the budget_detail_id
1582 cursor c2(p_budget_id number) is
1583 select bdt.budget_detail_id
1584 from pqh_budget_details bdt,pqh_budget_versions bvr
1585 where bvr.budget_id = p_budget_id
1586 and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date)
1587 and bdt.budget_version_id = bvr.budget_version_id
1588 and bdt.position_id = p_position_id;
1589
1590 --get the budget_unit_values
1591 cursor c3(p_budget_detail_id number) is
1592 select bpr.budget_unit1_value, bpr.budget_unit2_value, bpr.budget_unit3_value
1593 from pqh_budget_periods bpr, per_time_periods tp_s,
1594 per_time_periods tp_e
1595 where bpr.budget_detail_id = p_budget_detail_id
1596 and tp_s.time_period_id = bpr.start_time_period_id
1597 and tp_e.time_period_id = bpr.end_time_period_id
1598 and tp_s.period_set_name = l_calendar
1599 and tp_e.period_set_name = l_calendar
1600 and p_effective_date between tp_s.start_date and tp_e.end_date;
1601
1602 BEGIN
1603 BEGIN
1604 OPEN c_bus_grp_id(p_position_id);
1605 FETCH c_bus_grp_id into l_business_group_id;
1606 CLOSE c_bus_grp_id;
1607
1608 hr_utility.set_location('l_business_group_id:' || l_business_group_id, 550);
1609
1610
1611 FOR l_budget_details_rec in c_budget_id(l_business_group_id)
1612 LOOP
1613 l_budget_id := l_budget_details_rec.budget_id;
1614 l_budget_unit1_id := l_budget_details_rec.budget_unit1_id;
1615 l_budget_unit2_id := l_budget_details_rec.budget_unit2_id;
1616 l_budget_unit3_id := l_budget_details_rec.budget_unit3_id;
1617 l_calendar := l_budget_details_rec.period_set_name;
1618 END LOOP;
1619
1620 hr_utility.set_location('l_budget_id:' || l_budget_id, 600);
1621 hr_utility.set_location('l_calendar:' || l_calendar, 600);
1622 hr_utility.set_location('l_budget_unit1_id:' || l_budget_unit1_id, 600);
1623 hr_utility.set_location('l_budget_unit2_id:' || l_budget_unit2_id, 600);
1624 hr_utility.set_location('l_budget_unit3_id:' || l_budget_unit3_id, 600);
1625 OPEN c1(l_budget_unit1_id);
1626 FETCH c1 into l_unit1_name;
1627 CLOSE c1;
1628 OPEN c1(l_budget_unit2_id);
1629 FETCH c1 into l_unit2_name;
1630 CLOSE c1;
1631 OPEN c1(l_budget_unit3_id);
1632 FETCH c1 into l_unit3_name;
1633 CLOSE c1;
1634 hr_utility.set_location('l_unit1_name:' || l_unit1_name, 601);
1635 hr_utility.set_location('l_unit2_name:' || l_unit2_name, 601);
1636 hr_utility.set_location('l_unit3_name:' || l_unit3_name, 601);
1637 EXCEPTION
1638 WHEN others THEN
1639 hr_utility.set_location('Error: ' || SQLERRM, 602);
1640 RETURN l_budgeted_hc;
1641 END;
1642 hr_utility.set_location('l_budget_id:' || l_budget_id, 602);
1643 for i in c2(l_budget_id) loop
1644 -- row corresponding to the position is picked up
1645 hr_utility.set_location('budget_detail_id:' || i.budget_detail_id, 603);
1646 --
1647 for j in c3(i.budget_detail_id) loop
1648 hr_utility.set_location('budget_unit1_value:' || j.budget_unit1_value, 604);
1649 if l_unit1_name ='HEAD' then
1650 l_budgeted_hc := nvl(l_budgeted_hc,0) + nvl(j.budget_unit1_value,0);
1651 elsif l_unit2_name ='HEAD' then
1652 l_budgeted_hc := nvl(l_budgeted_hc,0) + nvl(j.budget_unit2_value,0);
1653 elsif l_unit3_name ='HEAD' then
1654 l_budgeted_hc := nvl(l_budgeted_hc,0) + nvl(j.budget_unit3_value,0);
1655 end if;
1656 end loop;
1657 end loop;
1658 hr_utility.set_location('l_budgeted_hc:' || l_budgeted_hc, 605);
1659 return l_budgeted_hc;
1660 end;
1661
1662 function GET_ASGND_HC_BUDGET_VAL(p_position_id in number default null,
1663 p_effective_date in date) return number is
1664 l_assignment_hc number;
1665 CURSOR c_budgeted_hc(p_position_id number) is
1666 select sum(nvl(value,1))
1667 from per_assignment_budget_values_f abv, per_all_assignments_f asn,
1668 per_assignment_status_types ast
1669 where abv.assignment_id(+) = asn.assignment_id
1670 and p_effective_date between asn.effective_start_date and asn.effective_end_date
1671 and p_effective_date between abv.effective_start_date and abv.effective_end_date
1672 and asn.position_id = p_position_id
1673 and asn.assignment_type in ('E', 'C')
1674 and abv.unit(+) = 'HEAD'
1675 and asn.assignment_status_type_id = ast.assignment_status_type_id
1676 and ast.per_system_status <> 'TERM_ASSIGN';
1677 --
1678 begin
1679 if p_position_id is not null then
1680 open c_budgeted_hc(p_position_id);
1681 fetch c_budgeted_hc into l_assignment_hc;
1682 close c_budgeted_hc;
1683 else
1684 l_assignment_hc := 0;
1685 end if;
1686 hr_utility.set_location ('l_assignment_hc GET_ASGND_HC_BUDGET_VAL '||l_assignment_hc,1);
1687 return(nvl(l_assignment_hc,0));
1688 end;
1689
1690 function GET_NUM_OF_VAC(p_position_id in number,
1691 p_effective_date in date,
1692 p_vacancy_id in number) return number IS
1693
1694 CURSOR csr_get_sum_of_open is
1695 Select sum(number_of_openings) from
1696 Per_vacancies
1697 WHERE position_id = p_position_id
1698 and vacancy_id <> p_vacancy_id
1699 AND p_effective_date BETWEEN date_from AND
1700 nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'))
1701 AND status in ('FILL','APPROVED','CLOSED')
1702 GROUP BY position_id;
1703
1704 l_no_of_vacancy number;
1705 Begin
1706 OPEN csr_get_sum_of_open;
1707 FETCH csr_get_sum_of_open into l_no_of_vacancy;
1708 CLOSE csr_get_sum_of_open;
1709 RETURN (nvl(l_no_of_vacancy,0));
1710 END;
1711
1712 procedure CHK_POS_BUDGET_VAL(p_position_id in number,
1713 p_effective_date in date,
1714 p_org_id in number,
1715 p_number_of_openings in number,
1716 p_vacancy_id in number) IS
1717 l_pos_bud_fte number;
1718 l_pos_bud_hc number;
1719 l_pos_asg_fte number;
1720 l_pos_asg_hc number;
1721 l_pos_vac_opn number;
1722 Begin
1723
1724 hr_utility.set_location ('coming CHK_POS_BUDGET_VAL',1);
1725
1726 hr_utility.set_location ('p_position_id CHK_POS_BUDGET_VAL'||p_position_id,1);
1727 hr_utility.set_location ('p_vacancy_id CHK_POS_BUDGET_VAL'||p_vacancy_id,1);
1728 hr_utility.set_location ('p_effective_date CHK_POS_BUDGET_VAL'||p_effective_date,1);
1729
1730 l_pos_bud_hc := GET_POS_HC_BUDGET_VAL(p_position_id,p_effective_date);
1731 hr_utility.set_location ('l_pos_bud_hc CHK_POS_BUDGET_VAL'||l_pos_bud_hc,1);
1732
1733 l_pos_asg_hc := GET_ASGND_HC_BUDGET_VAL(p_position_id,p_effective_date);
1734 hr_utility.set_location ('l_pos_asg_hc CHK_POS_BUDGET_VAL'||l_pos_asg_hc,1);
1735
1736 l_pos_vac_opn := GET_NUM_OF_VAC(p_position_id,p_effective_date,p_vacancy_id);
1737 hr_utility.set_location ('l_pos_vac_opn CHK_POS_BUDGET_VAL'||l_pos_vac_opn,1);
1738
1739 hr_utility.set_location ('p_number_of_openings CHK_POS_BUDGET_VAL'||p_number_of_openings,1);
1740
1741 If ((l_pos_asg_hc + l_pos_vac_opn + p_number_of_openings) > L_pos_bud_hc) then
1742 hr_utility.set_location ('p_number_of_openings CHK_POS_BUDGET_VAL'||p_number_of_openings,1);
1743 pqh_utility.set_message(8302,'PQH_SUM_POS_BGT_HC', p_org_id);
1744 pqh_utility.raise_error;
1745 End If;
1746
1747 End;
1748 -- End - Changes for ER#8530112
1749
1750 END PER_VACANCIES_PKG;