[Home] [Help]
PACKAGE: APPS.PER_VACANCIES_PKG
Source
1 PACKAGE PER_VACANCIES_PKG AUTHID CURRENT_USER as
2 /* $Header: pevac01t.pkh 120.2.12010000.3 2010/04/08 10:16:09 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 form).
13 Notes
14
15 History
16 13-APR-94 H.Minton 40.0 Date created.
17
18 23-MAY-94 H.Minton 40.1 Added new functions for folder form.
19
20 19-JAN-95 D.Kerr 70.5 Removed WHO- columns
21
22 17-MAY-95 D.Kerr 70.6 1.Removed p_business_group_id
23 parameter from D_from_updt_rec_act_chk
24 2.Removed check_references1 and
25 renamed check_references2 to
26 check_references.
27 3.Added vacancy_id parameter to
28 delete_row to make ref. int check
29 easier.
30
31 26-JAN-98 I.Harding 110.1 Added extra vacancy_category parameter
32 to insert, update and lock procs.
33
34 25-FEB-98 B.Goodsell 115.1 Added Budget Measurement columns to
35 Table Handler procedures
36 03-JUL-06 avarri 115.4 Modiifed the procedure Check_Unique_Name
37 for 4262036.
38 04-Nov-08 sidsaxen 115.6 Added procedure end_date_irec_RA, end_date_PER_RA
39 and stubbed procedure D_from_updt_rec_act_chk
40 for bug 6497289
41 08-APR-10 karthmoh 120.2.12010000.2 Modified/Added Procedures for ER#8530112
42
43 ============================================================================*/
44 --
45 -----------------------------------------------------------------------------
46 -- Name --
47 -- Check_References --
48 -- Purpose --
49 -- To ensure the referential integrity when a vacancy is deleted from the--
50 -- Define Requisition and Vacancy form. --
51 -----------------------------------------------------------------------------
52 --
53 PROCEDURE Check_References(P_vacancy_id NUMBER);
54
55 ----------------------------------------------------------------------------
56 --
57 -- Name --
58 -- B_counter --
59 -- Purpose --
60 -- The purpose of this function is to return the values for the FOLDER
61 -- block of the forms VIEW VACANCIES.
62 -----------------------------------------------------------------------------
63 FUNCTION B_counter(P_Business_group_id NUMBER,
64 P_vacancy_id NUMBER,
65 P_legislation_code VARCHAR2,
66 P_vac_type VARCHAR2) return NUMBER;
67 ----------------------------------------------------------------------------
68 --
69 -- Name --
70 -- folder_hires --
71 -- Purpose --
72 -- the purpose of this function is to return the number of applicants who
73 -- have been hired as employees as a result of being hired into a vacancy.
74 -- This function is used by the folder form PERWILVA - View Vacancies.
75 -----------------------------------------------------------------------------
76 FUNCTION folder_hires(P_Business_group_id NUMBER,
77 P_vacancy_id NUMBER
78 ) return NUMBER;
79 ----------------------------------------------------------------------------
80 -- Name --
81 -- folder_current --
82 -- Purpose --
83 -- the purpose of this function is to return the number of current openings
84 -- for the vacancy as of the session date i.e it is the initial number of
85 -- openings for the vacancy as when the vacancy was defined minus the
86 -- number of applicants who have been hired into the vacancy.
87 -----------------------------------------------------------------------------
88 FUNCTION folder_current(P_Business_group_id NUMBER,
89 P_vacancy_id NUMBER,
90 P_session_date DATE
91 ) return NUMBER;
92 ----------------------------------------------------------------------------
93 -- Name --
94 -- Check_Unique_Name --
95 -- Purpose --
96 -- checks that the vacancy name is unique. Called from the --
97 -- client side package VACANCY_ITEM from the procedure 'name'. Called --
98 -- on WHEN-VALIDATE-ITEM from Name. --
99 -- --
100 -----------------------------------------------------------------------------
101 --
102 PROCEDURE Check_Unique_Name(P_Name VARCHAR2,
103 P_business_group_id NUMBER,
104 P_rowid VARCHAR2);
105 --
106 -----------------------------------------------------------------------------
107 -- Name --
108 -- Chk_appl_exists --
109 -- Purpose --
110 -- Verify the effective date, you cannot change the effective date of --
111 -- this vacancy to a future date as applications exist within the vacancy--
112 -- availability period. --
113 -- Called from WHEN-VALIDATE-ITEM in the vacancy block. --
114 -- --
115 -----------------------------------------------------------------------------
116 --
117 procedure chk_appl_exists (P_vacancy_id NUMBER,
118 P_vac_date_from DATE,
119 P_vac_date_to DATE,
120 P_end_of_time DATE
121 );
122 --
123 -----------------------------------------------------------------------------
124 -- Name --
125 -- Check_in_req_dates --
126 -- Purpose --
127 -- to check that the vacancy date_from is not < than the requisition date--
131 P_Business_group_id NUMBER,
128 -- date_from. --
129 -----------------------------------------------------------------------------
130 PROCEDURE Check_in_req_dates(P_requisition_id NUMBER,
132 P_vac_date_from DATE);
133 --
134 -----------------------------------------------------------------------------
135 -- Name --
136 -- Chk_dt_to_in_req_dates --
137 -- Purpose --
138 -- Ensure that the vacancy date to is witin the requisition dates. --
139 -- Called from WHEN-VALIDATE-ITEM in the vacancy block. --
140 -- --
141 -----------------------------------------------------------------------------
142 --
143 PROCEDURE Chk_dt_to_in_req_dates(P_requisition_id NUMBER,
144 P_Business_group_id NUMBER,
145 P_vac_date_to DATE);
146 ----------------------------------------------------------------------------
147 -- Name --
148 -- Date_from_upd_validation --
149 -- Purpose --
150 -- Ensure that the vacancy date_from does not invalidate any of the --
151 -- vacancy_for region.
152 -----------------------------------------------------------------------------
153 PROCEDURE Date_from_upd_validation(
154 Pz_vac_date_from DATE,
155 Pz_business_group_id NUMBER,
156 Pz_start_of_time DATE,
157 Pz_end_of_time DATE,
158 Pz_organization_id NUMBER,
159 Pz_position_id NUMBER,
160 Pz_people_group_id NUMBER,
161 Pz_job_id NUMBER,
162 Pz_grade_id NUMBER,
163 Pz_recruiter_id NUMBER,
164 Pz_location_id NUMBER);
165 ----------------------------------------------------------------------------
166 -- Name --
167 -- D_from_updt_rec_act_chk --
168 -- Purpose --
169 -- to check that the vacancy date_from does not invalidate any recruitment
170 -- activity which uses this vacancy. --
171 -----------------------------------------------------------------------------
172 PROCEDURE D_from_updt_rec_act_chk(P_vacancy_id NUMBER,
173 P_vac_date_from DATE,
174 P_vac_date_to DATE,
175 P_end_of_time DATE);
176 -----------------------------------------------------------------------------
177 -- Name --
178 -- D_from_updt_org_chk --
179 -- Purpose --
180 -- Ensure that the vacancy date_from does not invalidate the organization--
181 -- part of the vacancy. --
182 -- Arguments --
183 -- see below. --
184 -----------------------------------------------------------------------------
185 PROCEDURE D_from_updt_org_chk(P_Business_group_id NUMBER,
186 P_vac_date_from DATE,
187 P_organization_id NUMBER);
188 -----------------------------------------------------------------------------
189 -- Name --
190 -- D_from_updt_pos_chk --
191 -- Purpose --
192 -- Ensure that the vacancy date_from does not invalidate the position --
193 -- part of the vacancy. --
194 -- Arguments --
195 -- see below. --
196 -----------------------------------------------------------------------------
197 PROCEDURE D_from_updt_pos_chk(P_Business_group_id NUMBER,
198 P_vac_date_from DATE,
199 P_position_id NUMBER);
200
201 -----------------------------------------------------------------------------
202 -- Name --
203 -- D_from_updt_grp_chk --
204 -- Purpose --
205 -- Ensure that the vacancy date_from does not invalidate the group --
206 -- part of the vacancy. --
207 -- Arguments --
208 -- see below. --
209 -----------------------------------------------------------------------------
210 PROCEDURE D_from_updt_grp_chk(P_vac_date_from DATE,
211 P_start_of_time DATE,
212 P_people_group_id NUMBER);
213 -----------------------------------------------------------------------------
214 -- Name --
215 -- D_from_updt_job_chk --
216 -- Purpose --
217 -- Ensure that the vacancy date_from does not invalidate the job --
218 -- part of the vacancy. --
219 -- Arguments --
220 -- see below. --
221 -----------------------------------------------------------------------------
222 PROCEDURE D_from_updt_job_chk(P_vac_date_from DATE,
223 P_business_group_id NUMBER,
224 P_job_id NUMBER);
225 -----------------------------------------------------------------------------
226 -- Name --
227 -- D_from_updt_grd_chk --
228 -- Purpose --
229 -- Ensure that the vacancy date_from does not invalidate the grade --
230 -- part of the vacancy. --
231 -- Arguments --
232 -- see below. --
233 -----------------------------------------------------------------------------
234 PROCEDURE D_from_updt_grd_chk(P_vac_date_from DATE,
235 P_business_group_id NUMBER,
236 P_grade_id NUMBER);
237
238 -----------------------------------------------------------------------------
239 -- Name --
240 -- D_from_updt_loc_chk --
241 -- Purpose --
242 -- Ensure that the vacancy date_from does not invalidate the location --
243 -- part of the vacancy. --
244 -- Arguments --
245 -- see below. --
246 -----------------------------------------------------------------------------
247 PROCEDURE D_from_updt_loc_chk(P_vac_date_from DATE,
248 P_end_of_time DATE,
249 P_location_id NUMBER);
250 -----------------------------------------------------------------------------
251 -- Name --
252 -- D_from_updt_person
256 -- Arguments --
253 -- Purpose --
254 -- Ensure that the vacancy date_from does not invalidate the recruiter --
255 -- part of the vacancy. --
257 -- see below. --
258 -----------------------------------------------------------------------------
259 PROCEDURE D_from_updt_person(P_vac_date_from DATE,
260 P_recruiter_id NUMBER,
261 P_business_group_id NUMBER);
262
263 -----------------------------------------------------------------------------
264 -- Name --
265 -- FUNCTION get_people_group
266 -- Purpose --
267 -- to get the people_group_structure for the group key flexfield in the --
268 -- vacancy block of PERWSVAC.l
269 -- Arguments --
270 -- see below. --
271 -----------------------------------------------------------------------------
272 FUNCTION get_people_group(P_Business_Group_Id NUMBER) return VARCHAR2;
273
274 -----------------------------------------------------------------------------
275 -- Name --
276 -- Insert_Row --
277 -- Purpose --
278 -- Table handler procedure. Supports the insert of a VACANCY via the --
279 -- Define Requistion and Vacancy form. --
280 -- Arguments --
281 -- See below. --
282 -- Notes --
283 -- --
284 -----------------------------------------------------------------------------
285 --
286 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
287 X_Vacancy_Id IN OUT NOCOPY NUMBER,
288 X_Business_Group_Id NUMBER,
289 X_Position_Id NUMBER,
290 X_Job_Id NUMBER,
291 X_Grade_Id NUMBER,
292 X_Organization_Id NUMBER,
293 X_Requisition_Id NUMBER,
294 X_People_Group_Id NUMBER,
295 X_People_Group_Name VARCHAR2,
296 X_Location_Id NUMBER,
297 X_Recruiter_Id NUMBER,
298 X_Date_From DATE,
299 X_Name VARCHAR2,
300 X_Comments VARCHAR2,
301 X_Date_To DATE,
302 X_Description VARCHAR2,
303 X_Vacancy_category varchar2,
304 X_Number_Of_Openings NUMBER,
305 X_Status VARCHAR2,
306 X_Budget_Measurement_Type VARCHAR2,
307 X_Budget_Measurement_Value NUMBER,
308 X_Attribute_Category VARCHAR2,
309 X_Attribute1 VARCHAR2,
310 X_Attribute2 VARCHAR2,
311 X_Attribute3 VARCHAR2,
312 X_Attribute4 VARCHAR2,
313 X_Attribute5 VARCHAR2,
314 X_Attribute6 VARCHAR2,
315 X_Attribute7 VARCHAR2,
316 X_Attribute8 VARCHAR2,
317 X_Attribute9 VARCHAR2,
318 X_Attribute10 VARCHAR2,
319 X_Attribute11 VARCHAR2,
320 X_Attribute12 VARCHAR2,
321 X_Attribute13 VARCHAR2,
322 X_Attribute14 VARCHAR2,
323 X_Attribute15 VARCHAR2,
324 X_Attribute16 VARCHAR2,
325 X_Attribute17 VARCHAR2,
326 X_Attribute18 VARCHAR2,
327 X_Attribute19 VARCHAR2,
328 X_Attribute20 VARCHAR2
329 );
330 --
331 -----------------------------------------------------------------------------
332 -- Name --
333 -- Lock_Row --
334 -- Purpose --
335 -- Table handler procedure that supports the insert , update and delete --
336 -- of an activity by applying a lock on a vacancy in the Define --
337 -- Requistion and Vacancy form. --
338 -- Arguments --
342 --
339 -- Notes --
340 -- None. --
341 -----------------------------------------------------------------------------
343 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
344 X_Vacancy_Id NUMBER,
345 X_Business_Group_Id NUMBER,
346 X_Position_Id NUMBER,
347 X_Job_Id NUMBER,
348 X_Grade_Id NUMBER,
349 X_Organization_Id NUMBER,
350 X_Requisition_Id NUMBER,
351 X_People_Group_Id NUMBER,
352 X_Location_Id NUMBER,
353 X_Recruiter_Id NUMBER,
354 X_Date_From DATE,
355 X_Name VARCHAR2,
356 X_Comments VARCHAR2,
357 X_Date_To DATE,
358 X_Description VARCHAR2,
359 X_Vacancy_category VARCHAR2,
360 X_Number_Of_Openings NUMBER,
361 X_Status VARCHAR2,
362 X_Budget_Measurement_Type VARCHAR2,
363 X_Budget_Measurement_Value NUMBER,
364 X_Attribute_Category VARCHAR2,
365 X_Attribute1 VARCHAR2,
366 X_Attribute2 VARCHAR2,
367 X_Attribute3 VARCHAR2,
368 X_Attribute4 VARCHAR2,
369 X_Attribute5 VARCHAR2,
370 X_Attribute6 VARCHAR2,
371 X_Attribute7 VARCHAR2,
372 X_Attribute8 VARCHAR2,
373 X_Attribute9 VARCHAR2,
374 X_Attribute10 VARCHAR2,
375 X_Attribute11 VARCHAR2,
376 X_Attribute12 VARCHAR2,
377 X_Attribute13 VARCHAR2,
378 X_Attribute14 VARCHAR2,
379 X_Attribute15 VARCHAR2,
380 X_Attribute16 VARCHAR2,
381 X_Attribute17 VARCHAR2,
382 X_Attribute18 VARCHAR2,
383 X_Attribute19 VARCHAR2,
384 X_Attribute20 VARCHAR2
385 );
386 --
387 -----------------------------------------------------------------------------
388 -- Name --
389 -- Update_Row --
390 -- Purpose --
391 -- Table handler procedure that supports the update of a VACANCY via --
392 -- Define Requistion and Vacancy form. --
393 -- Arguments --
394 -- See below. --
395 -- Notes --
396 -- None. --
397 -----------------------------------------------------------------------------
398 --
399 PROCEDURE Update_Row(X_Rowid VARCHAR2,
400 X_Vacancy_Id NUMBER,
401 X_Business_Group_Id NUMBER,
402 X_Position_Id NUMBER,
403 X_Job_Id NUMBER,
404 X_Grade_Id NUMBER,
405 X_Organization_Id NUMBER,
406 X_Requisition_Id NUMBER,
407 X_People_Group_Id NUMBER,
408 X_People_Group_Name VARCHAR2,
409 X_Location_Id NUMBER,
410 X_Recruiter_Id NUMBER,
411 X_Date_From DATE,
412 X_Name VARCHAR2,
413 X_Comments VARCHAR2,
414 X_Date_To DATE,
415 X_Description VARCHAR2,
416 X_Vacancy_category varchar2,
417 X_Number_Of_Openings NUMBER,
418 X_Status VARCHAR2,
419 X_Budget_Measurement_Type VARCHAR2,
420 X_Budget_Measurement_Value NUMBER,
421 X_Attribute_Category VARCHAR2,
422 X_Attribute1 VARCHAR2,
423 X_Attribute2 VARCHAR2,
424 X_Attribute3 VARCHAR2,
425 X_Attribute4 VARCHAR2,
429 X_Attribute8 VARCHAR2,
426 X_Attribute5 VARCHAR2,
427 X_Attribute6 VARCHAR2,
428 X_Attribute7 VARCHAR2,
430 X_Attribute9 VARCHAR2,
431 X_Attribute10 VARCHAR2,
432 X_Attribute11 VARCHAR2,
433 X_Attribute12 VARCHAR2,
434 X_Attribute13 VARCHAR2,
435 X_Attribute14 VARCHAR2,
436 X_Attribute15 VARCHAR2,
437 X_Attribute16 VARCHAR2,
438 X_Attribute17 VARCHAR2,
439 X_Attribute18 VARCHAR2,
440 X_Attribute19 VARCHAR2,
441 X_Attribute20 VARCHAR2
442 );
443 --
444 -----------------------------------------------------------------------------
445 -- Name --
446 -- Delete_Row --
447 -- Purpose --
448 -- Table handler procedure that supports the delete of a VACANCY via --
449 -- the Define Requistion and Vacancy form. --
450 -- Arguments --
451 -- See below. --
452 -- Notes --
453 -- --
454 -----------------------------------------------------------------------------
455 --
456 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
457 X_vacancy_id in number );
458
459 g_Recinfo per_vacancies%ROWTYPE;
460
461 -- start changes for bug 6497289
462 --
463 -----------------------------------------------------------------------------
464 -- Name --
465 -- end_date_iRec_RA --
466 -- Purpose --
467 -- To End-Date the i-Rec Site Recruitment Activity --
468 -- Arguments --
469 -- See below. --
470 -- Notes --
471 -- --
472 -----------------------------------------------------------------------------
473 --
474 PROCEDURE end_date_iRec_RA(P_vacancy_id IN NUMBER,
475 P_vac_date_from IN DATE,
476 P_vac_date_to IN DATE);
477
478 --
479 -----------------------------------------------------------------------------
480 -- Name --
481 -- end_date_per_RA --
482 -- Purpose --
483 -- To End-Date the PER Recruitment Activity --
484 -- Arguments --
485 -- See below. --
486 -- Notes --
487 -- --
488 -----------------------------------------------------------------------------
489 --
490 PROCEDURE end_date_per_RA(P_vacancy_id IN NUMBER,
491 P_recruitment_activity_id IN NUMBER,
492 P_vac_date_from IN DATE,
493 P_vac_date_to IN DATE);
494
495 -- end changes for bug 6497289
496 -- Begin - Changes for ER#8530112
497 -----------------------------------------------------------------------------
498 -- Name --
499 -- GET_POS_HC_BUDGET_VAL --
500 -- Purpose --
501 -- To get the Position Headcount Budget value --
502 -- Arguments --
503 -- See below. --
504 -- Notes --
505 -- --
506 -----------------------------------------------------------------------------
507 function GET_POS_HC_BUDGET_VAL(p_position_id in number default null,
508 p_effective_date in date) return number;
509 -----------------------------------------------------------------------------
510 -- Name --
511 -- GET_ASGND_HC_BUDGET_VAL --
512 -- Purpose --
513 -- To get the Assigned HeadCount for that position --
514 -- Arguments --
515 -- See below. --
516 -- Notes --
517 -- --
518 -----------------------------------------------------------------------------
519 function GET_ASGND_HC_BUDGET_VAL(p_position_id in number default null,
520 p_effective_date in date) return number;
521 -----------------------------------------------------------------------------
522 -- Name --
523 -- GET_NUM_OF_VAC --
524 -- Purpose --
525 -- To get the number of vacancies for that position --
526 -- Arguments --
527 -- See below. --
528 -- Notes --
529 -- --
530 -----------------------------------------------------------------------------
531 function GET_NUM_OF_VAC(p_position_id in number,p_effective_date in date,p_vacancy_id in number) return number;
532 -----------------------------------------------------------------------------
533 -- Name --
534 -- CHK_POS_BUDGET_VAL --
535 -- Purpose --
536 -- To check whether vancancy is available for that position --
537 -- Arguments --
538 -- See below. --
539 -- Notes --
540 -- --
541 -----------------------------------------------------------------------------
542 procedure CHK_POS_BUDGET_VAL(p_position_id in number,
543 p_effective_date in date,p_org_id in number,p_number_of_openings in number,p_vacancy_id in number);
544
545 -- End - Changes for ER#8530112
546
547 END PER_VACANCIES_PKG;