[Home] [Help]
PACKAGE BODY: APPS.PER_JOBS_PKG
Source
1 PACKAGE BODY PER_JOBS_PKG as
2 /* $Header: pejbd01t.pkb 120.0 2005/05/31 10:32:42 appldev noship $ */
3 --
4 procedure get_next_sequence(p_job_id in out nocopy number) is
5 --
6 cursor c1 is select per_jobs_s.nextval
7 from sys.dual;
8 --
9 begin
10 --
11 -- Retrieve the next sequence number for job_id
12 --
13 if (p_job_id is null) then
14 open c1;
15 fetch c1 into p_job_id;
16 if (C1%NOTFOUND) then
17 CLOSE C1;
18 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
19 hr_utility.set_message_token('PROCEDURE','get_next_sequence');
20 hr_utility.set_message_token('STEP','1');
21 end if;
22 close c1;
23 end if;
24 --
25 hr_utility.set_location('PER_JOBS_PKG.get_next_sequence', 1);
26 --
27 end get_next_sequence;
28 --
29 procedure check_unique_name(p_job_id in number,
30 p_business_group_id in number,
31 p_name in varchar2) is
32 --
33 cursor csr_name is select null
34 from per_jobs j
35 where ((p_job_id is not null
36 and j.job_id <> p_job_id)
37 or p_job_id is null)
38 and j.business_group_id + 0 = p_business_group_id
39 and j.name = p_name;
40 --
41 g_dummy_number number;
42 v_not_unique boolean := FALSE;
43 --
44 -- Check the job name is unique
45 --
46 begin
47 --
48 open csr_name;
49 fetch csr_name into g_dummy_number;
50 v_not_unique := csr_name%FOUND;
51 close csr_name;
52 --
53 if v_not_unique then
54 hr_utility.set_message(801,'PER_7810_DEF_JOB_EXISTS');
55 hr_utility.raise_error;
56 end if;
57 --
58 hr_utility.set_location('PER_JOBS_PKG.check_unique_name', 1);
59 --
60 end check_unique_name;
61 --
62 procedure check_date_from(p_job_id in number,
63 p_date_from in date) is
64 --
65 cursor csr_date_from is select null
66 from per_valid_grades vg
67 where vg.job_id = p_job_id
68 and p_date_from > vg.date_from;
69 --
70 g_dummy_number number;
71 v_job_date_greater boolean := FALSE;
72 --
73 begin
74 hr_utility.set_location('check date',99);
75 --
76 -- If the date from item in the jobs block is greater than
77 -- the date from item in the grades block then raise an error
78 --
79 open csr_date_from;
80 fetch csr_date_from into g_dummy_number;
81 v_job_date_greater := csr_date_from%FOUND;
82 close csr_date_from;
83 --
84 if v_job_date_greater then
85 hr_utility.set_message(801,'PER_7825_DEF_GRD_JOB_START_JOB');
86 hr_utility.raise_error;
87 end if;
88 --
89 hr_utility.set_location('PER_JOBS_PKG.check_date_from', 1);
90 --
91 end check_date_from;
92 --
93 procedure get_job_flex_structure(p_structure_defining_column in out nocopy varchar2,
94 p_job_group_id in number) is
95 --
96 -- Get the job_flex_structure_id
97 --
98 l_struct varchar2(30);
99 --
100 cursor csr_job is select to_char(id_flex_num)
101 from per_job_groups_v
102 where p_job_group_id = job_group_id;
103 --
104 v_not_found boolean := FALSE;
105 --
106 -- Get job flex structure id
107 --
108 begin
109 --
110 open csr_job;
111 fetch csr_job into p_structure_defining_column;
112 v_not_found := csr_job%NOTFOUND;
113 close csr_job;
114 --
115 l_struct := p_structure_defining_column;
116 hr_utility.set_location('p_struct '||l_struct,99);
117 --
118 if v_not_found then
119 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
120 hr_utility.set_message_token('PROCEDURE','get_job_flex_structure');
121 hr_utility.set_message_token('STEP','1');
122 hr_utility.raise_error;
123 end if;
124 --
125 hr_utility.set_location('PER_JOBS_PKG.get_job_flex_structure', 1);
126 --
127 end get_job_flex_structure;
128 --
129 PROCEDURE check_altered_end_date(p_business_group_id number,
130 p_job_id number,
131 p_end_of_time date,
132 p_date_to date,
133 p_early_date_to in out nocopy boolean,
134 p_early_date_from in out nocopy boolean) is
135 --
136 cursor csr_date_to is select null
137 from per_valid_grades vg
138 where vg.business_group_id + 0 = p_business_group_id
139 and vg.job_id = p_job_id
140 and nvl(vg.date_to, p_end_of_time) > p_date_to;
141 --
142 cursor csr_date_from is select null
143 from per_valid_grades vg
144 where vg.business_group_id + 0 = p_business_group_id
145 and vg.job_id = p_job_id
146 and vg.date_from > p_date_to;
147 --
148 g_dummy_number number;
149 --
150 begin
151 --
152 open csr_date_to;
153 fetch csr_date_to into g_dummy_number;
154 p_early_date_to := csr_date_to%FOUND;
155 close csr_date_to;
156 --
157 hr_utility.set_location('PER_JOBS_PKG.check_altered_end_date', 1);
158 --
159 open csr_date_from;
160 fetch csr_date_from into g_dummy_number;
161 p_early_date_from := csr_date_from%FOUND;
162 close csr_date_from;
163 --
164 hr_utility.set_location('PER_JOBS_PKG.check_altered_end_date', 2);
165 --
166 end check_altered_end_date;
167 --
168 PROCEDURE update_valid_grades(p_business_group_id number,
169 p_job_id number,
170 p_date_to date,
171 p_end_of_time date) is
172 --
173 begin
174 --
175 -- Update valid grade end dates to match the end date of the
176 -- job where the end date of the job is earlier than the end
177 -- date of the valid grade.or the previous end dates matched.
178 --
179 --
180 update per_valid_grades vg
181 set vg.date_to =
182 (select least(nvl(p_date_to, p_end_of_time),
183 nvl(g.date_to, p_end_of_time))
184 from per_grades g
185 where g.grade_id = vg.grade_id
186 and g.business_group_id + 0 = p_business_group_id)
187 where vg.business_group_id + 0 = p_business_group_id
188 and vg.job_id = p_job_id
189 and nvl(vg.date_to, p_end_of_time) > p_date_to;
190 --
191 if (SQL%NOTFOUND) then
192 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
193 hr_utility.set_message_token('PROCEDURE','update_valid_grades');
194 hr_utility.set_message_token('STEP','1');
195 hr_utility.raise_error;
196 end if;
197 --
198 --
199 end update_valid_grades;
200 --
201 PROCEDURE delete_valid_grades(p_business_group_id number,
202 p_job_id number,
203 p_date_to date) is
204 --
205 begin
206 --
207 -- Valid grades are deleted if the end date of the job
208 -- has been made earlier than the start date of the
209 -- valid grade.
210 --
211 --
212 delete from per_valid_grades vg
213 where vg.business_group_id + 0 = p_business_group_id
214 and vg.job_id = p_job_id
215 and vg.date_from > p_date_to;
216 --
217 --
218 if (SQL%NOTFOUND) then
219 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
220 hr_utility.set_message_token('PROCEDURE','delete_valid_grades');
221 hr_utility.set_message_token('STEP','1');
222 hr_utility.raise_error;
223 end if;
224 --
225 end delete_valid_grades;
226 --
227 PROCEDURE check_delete_record(p_job_id number,
228 p_business_group_id number) is
229 --
230 -- Changed 01-Oct-99 SCNair (per_all_positions to hr_all_positions_f) date track requirement
231 --
232 cursor csr_position is select null
233 from hr_all_positions_f pst1
234 where pst1.job_id = p_job_id;
235 --
236 cursor csr_assignment is select null
237 from per_all_assignments_f a
238 where a.job_id = p_job_id
239 and a.job_id is not null;
240 --
241 cursor csr_grade is select null
242 from per_valid_grades vg1
243 where vg1.business_group_id + 0 = p_business_group_id
244 and vg1.job_id = p_job_id;
245 --
246 cursor csr_requirement is select null
247 from per_job_requirements jre1
248 where jre1.job_id = p_job_id;
249 --
250 cursor csr_evaluation is select null
251 from per_job_evaluations jev1
252 where jev1.job_id = p_job_id;
253 --
254 cursor csr_elementp is select null
255 from per_career_path_elements cpe1
256 where cpe1.parent_job_id = p_job_id;
257 --
258 cursor csr_elements is select null
259 from per_career_path_elements cpe1
260 where cpe1.subordinate_job_id = p_job_id;
261 --
262 cursor csr_budget is select null
263 from per_budget_elements bde1
264 where bde1.job_id = p_job_id
265 and bde1.job_id is not null;
266 --
267 cursor csr_vacancy is select null
268 from per_vacancies vac
269 where vac.job_id = p_job_id
270 and vac.job_id is not null;
271 --
272 cursor csr_link is select null
273 from pay_element_links_f eln
274 where eln.job_id = p_job_id
275 and eln.job_id is not null;
276 --
277 cursor csr_role is select null
278 from per_roles rol
279 where rol.job_id = p_job_id
280 and rol.job_id is not null;
281 --
282 g_dummy_number number;
283 v_record_exists boolean := FALSE;
284 v_dummy boolean := FALSE;
285 l_sql_text VARCHAR2(2000);
286 l_status VARCHAR2(1);
287 l_industry VARCHAR2(1);
288 l_oci_out VARCHAR2(1);
289 l_sql_cursor NUMBER;
290 l_rows_fetched NUMBER;
291 --
292 begin
293 --
294 -- Check there are no values in per_valid_grades, per_job_requirements,
295 -- per_job_evaluations, per_career_path_elements (check on parent and
296 -- subordinate id), hr_all_positions_f, per_budget_elements,
297 -- PER_all_assignments, per_vacancies_f, per_element_links_f
298 --
299 --
300 --
301 open csr_position;
302 fetch csr_position into g_dummy_number;
303 v_record_exists := csr_position%FOUND;
304 close csr_position;
305 --
306 if v_record_exists then
307 hr_utility.set_message(801,'PER_7813_DEF_JOB_DEL_POS');
308 hr_utility.raise_error;
309 end if;
310 --
311 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 1);
312 --
313 --
314 --
315 open csr_assignment;
316 fetch csr_assignment into g_dummy_number;
317 v_record_exists := csr_assignment%FOUND;
318 close csr_assignment;
319 --
320 if v_record_exists then
321 hr_utility.set_message(801,'PER_7817_DEF_JOB_DEL_EMP');
322 hr_utility.raise_error;
323 end if;
324 --
325 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 2);
326 --
327 --
328 --
329 open csr_grade;
330 fetch csr_grade into g_dummy_number;
331 v_record_exists := csr_grade%FOUND;
332 close csr_grade;
333 --
334 if v_record_exists then
335 hr_utility.set_message(801,'PER_7812_DEF_JOB_DEL_GRADE');
336 hr_utility.raise_error;
337 end if;
338 --
339 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 3);
340 --
341 --
342 --
343 open csr_requirement;
344 fetch csr_requirement into g_dummy_number;
345 v_record_exists := csr_requirement%FOUND;
346 close csr_requirement;
347 --
348 if v_record_exists then
349 hr_utility.set_message(801,'PER_7814_DEF_JOB_DEL_REQ');
350 hr_utility.raise_error;
351 end if;
352 --
353 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 4);
354 --
355 --
356 --
357 open csr_evaluation;
358 fetch csr_evaluation into g_dummy_number;
359 v_record_exists := csr_evaluation%FOUND;
360 close csr_evaluation;
361 --
362 if v_record_exists then
363 hr_utility.set_message(801,'PER_7815_DEF_JOB_DEL_EVAL');
364 hr_utility.raise_error;
365 end if;
366 --
367 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 5);
368 --
369 --
370 --
371 open csr_elementp;
372 fetch csr_elementp into g_dummy_number;
373 v_record_exists := csr_elementp%FOUND;
374 close csr_elementp;
375 --
376 if v_record_exists then
377 hr_utility.set_message(801,'PER_7811_DEF_JOB_DEL_PATH');
378 hr_utility.raise_error;
379 end if;
380 --
381 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 6);
382 --
383 --
384 --
385 open csr_elements;
386 fetch csr_elements into g_dummy_number;
387 v_record_exists := csr_elements%FOUND;
388 close csr_elements;
389 --
390 if v_record_exists then
391 hr_utility.set_message(801,'PER_7811_DEF_JOB_DEL_PATH');
392 hr_utility.raise_error;
393 end if;
394 --
395 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 7);
396 --
397 --
398 --
399 open csr_budget;
400 fetch csr_budget into g_dummy_number;
401 v_record_exists := csr_budget%FOUND;
402 close csr_budget;
403 --
404 if v_record_exists then
405 hr_utility.set_message(801,'PER_7816_DEF_JOB_DEL_BUD');
406 hr_utility.raise_error;
407 end if;
408 --
409 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 8);
410 --
411 --
412 --
413 open csr_vacancy;
414 fetch csr_vacancy into g_dummy_number;
415 v_record_exists := csr_vacancy%FOUND;
416 close csr_vacancy;
417 --
418 if v_record_exists then
419 hr_utility.set_message(801,'HR_6945_JOB_DEL_RAC');
420 hr_utility.raise_error;
421 end if;
422 --
423 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 9);
424 --
425 --
426 --
427 open csr_link;
428 fetch csr_link into g_dummy_number;
429 v_record_exists := csr_link%FOUND;
430 close csr_link;
431 --
432 if v_record_exists then
433 hr_utility.set_message(801,'HR_6946_JOB_DEL_LINK');
434 hr_utility.raise_error;
435 end if;
436 --
437 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 10);
438 --
439 --
440 open csr_role;
441 fetch csr_role into g_dummy_number;
442 v_record_exists := csr_role%FOUND;
443 close csr_role;
444 --
445 if v_record_exists then
446 hr_utility.set_message(800,'PER_52684_JOB_DEL_ROLE');
447 hr_utility.raise_error;
448 end if;
449 --
450 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 11);
451 --
452 -- is po installed?
453 --
454 if (fnd_installation.get(appl_id => 201
455 ,dep_appl_id => 201
456 ,status => l_status
457 ,industry => l_industry))
458 then
459 --
460 -- If fully installed (l_status = 'I')
461 --
462 if l_status = 'I'
463 then
464 -- Dynamic SQL cursor to get round the problem of Table not existing.
465 -- Shouldn't be a problem after 10.6, but better safe than sorry.
466 -- This uses a similar method to OCI but Via PL/SQL instead.
467 --
468 -- #358988 removed the table alias 'pcc' which didn't match the column
469 -- alias ppc. RMF 17-Apr-96.
470 --
471 begin
472 l_sql_text := 'select null '
473 ||'from sys.dual '
474 ||'where exists( select null '
475 ||' from po_position_controls '
476 ||' where job_id = '
477 ||to_char(p_job_id)
478 ||' ) ';
479 --
480 -- Open Cursor for Processing Sql statment.
481 --
482 l_sql_cursor := dbms_sql.open_cursor;
483 --
484 -- Parse SQL statement.
485 --
486 dbms_sql.parse(l_sql_cursor, l_sql_text, dbms_sql.v7);
487 --
488 -- Map the local variables to each returned Column
489 --
490 dbms_sql.define_column(l_sql_cursor, 1,l_oci_out,1);
491 --
492 -- Execute the SQL statement.
493 --
494 l_rows_fetched := dbms_sql.execute(l_sql_cursor);
495 --
496 if (dbms_sql.fetch_rows(l_sql_cursor) > 0)
497 then
498 fnd_message.set_name('PAY','HR_6048_PO_POS_DEL_POS_CONT');
499 fnd_message.raise_error;
500 end if;
501 --
502 -- Close cursor used for processing SQL statement.
503 --
504 dbms_sql.close_cursor(l_sql_cursor);
505 end;
506 end if;
507 end if;
508 --
509 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 11);
510 --
511 per_ota_predel_validation.ota_predel_job_validation(p_job_id);
512 --
513 hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 12);
514 --
515 pa_job.pa_predel_validation(p_job_id);
516 --
517 end check_delete_record;
518 --
519
520 procedure check_evaluation_dates(p_jobid in number,
521 p_job_date_from in date,
522 p_job_date_to in date) is
523
524
525 cursor csr_job_evaluations(p_job_id in number) is
526 select jbe.job_evaluation_id,
527 jbe.date_evaluated
528 from per_job_evaluations jbe
529 where jbe.job_id = csr_job_evaluations.p_job_id;
530
531 --
532 begin
533 --
534
535 if p_jobid is not null then
536 for l_job_evaluation in csr_job_evaluations(
537 p_job_id => p_jobid) loop
538 if l_job_evaluation.date_evaluated not between
539 nvl(p_job_date_from, hr_api.g_sot) and
540 nvl(p_job_date_to, hr_api.g_eot) then
541 fnd_message.set_name('PER', 'HR_52603_JOB_JBE_OUT_PERIOD');
542 hr_utility.raise_error;
543 end if;
544 end loop;
545 end if;
546
547 --
548 exception
549 --
550
551 when others then
552 if csr_job_evaluations%isopen then
553 close csr_job_evaluations;
554 end if;
555 raise;
556
557 --
558 end check_evaluation_dates;
559 --
560
561 END PER_JOBS_PKG;