[Home] [Help]
PACKAGE BODY: APPS.HR_JOB_API
Source
1 Package Body hr_job_api as
2 /* $Header: pejobapi.pkb 120.0.12010000.1 2008/07/28 04:55:26 appldev ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_job_api.';
7 --
8 c_date_from constant varchar2(4) := 'FROM';
9 c_date_to constant varchar2(4) := 'TO';
10 --
11 -- ------------------------- Private Procedures -------------------------------
12 -- ----------------------------------------------------------------------------
13 -- |------------------------< maintain_valid_grades >-------------------------|
14 -- ----------------------------------------------------------------------------
15 -- {Start Of Comments}
16 --
17 -- Description:
18 -- This internal procedure maintains valid grades for a job when the
19 -- date from or date to of a job is updated.
20
21 -- If the job Date Effective is being updated, then valid grades with
22 -- a date to which is earlier than that Date Effective are deleted.
23 -- Valid Grades with a date from which is earlier than the Job Date
24 -- Effective and a date to which is later than the Job Date Effective
25 -- or null are update with their Date From set to the job Date
26 -- Effective.
27 --
28 -- If the Job Date End is being updated, valid grades with a date from
29 -- which is later than the end date of the job are deleted. Valid
30 -- Grades with a date from which is earlier then the job end date and a
31 -- date to which is later than the job end date or null are updated with
32 -- their date to set to the job end date.
33 --
34 -- Prerequisites:
35 -- A valid job (p_job_id) must exist.
36 --
37 -- In Parameters:
38 -- Name Reqd Type Description
39 -- p_validate Y boolean Default False
40 -- p_job_id Y number
41 -- p_maintenance_mode Y varchar2 Indicates whether the
42 -- job date effective or
43 -- the job date end has
44 -- been updated. Valid values
45 -- are 'DATE_EFFECTIVE' and
46 -- 'DATE_END'.
47 -- p_date_from N date job date effective
48 -- p_date_to N date job date end
49 -- p_approval_authority N number approval authority for OAM
50 --
51 --
52 -- Post Success:
53 --
54 -- Name Type Description
55 -- p_valid_grades_changed boolean Only set to true if any valid
56 -- grade rows have been updated
57 -- or deleted.
58 --
59 -- Post Failure:
60 --
61 -- Access Status:
62 -- Internal Development Use Only.
63 --
64 -- {End Of Comments}
65 -- ---------------------------------------------------------------------------
66 procedure maintain_valid_grades
67 (
68 p_validate in boolean default false
69 ,p_job_id in number
70 ,p_maintenance_mode in varchar2
71 ,p_date_from in date
72 ,p_date_to in date
73 ,p_valid_grades_changed out nocopy boolean
74 ,p_effective_date in date -- Added for Bug# 1760707
75 )
76 is
77 --
78 l_proc varchar2(72) := g_package||'maintain_valid_grades';
79 l_valid_grade_changed boolean default FALSE;
80 --
81 cursor csr_valid_grades is
82 select
83 vgr.valid_grade_id valid_grade_id
84 ,vgr.object_version_number object_version_number
85 ,vgr.date_from date_from
86 ,vgr.date_to date_to
87 from per_valid_grades vgr
88 where vgr.job_id = p_job_id;
89 --
90 begin
91 hr_utility.set_location('Entering:'|| l_proc, 5);
92 --
93 IF p_maintenance_mode = c_date_from THEN
94 --
95 -- When maintain_valid_grades has been called to maintain the valid grades
96 -- for a job in accordance with that Jobs's new Date from,
97 -- (ie. p_maintenance_mode = 'DATE_EFFECTIVE') then the p_date_from
98 -- parameter should have been set.
99 --
100 hr_api.mandatory_arg_error
101 (p_api_name => l_proc
102 ,p_argument => 'date_effective'
103 ,p_argument_value => p_date_from);
104 --
105 hr_utility.set_location(l_proc, 10);
106 --
107 FOR c_vgr_rec IN csr_valid_grades LOOP
108 --
109 -- If a valid grade for the job has a Date From that is earlier
110 -- than the new Date from of the job and a Date To that is
111 -- later than that new Date from or is null, then update that
112 -- valid grade's Date From to that new Date from.
113 --
114 hr_utility.set_location(l_proc, 15);
115 if (c_vgr_rec.date_from < p_date_from and
116 nvl(c_vgr_rec.date_to, hr_api.g_eot) > p_date_from ) then
117 --
118 hr_utility.set_location(l_proc, 20);
119 --
120 per_vgr_upd.upd
121 (p_valid_grade_id => c_vgr_rec.valid_grade_id
122 ,p_object_version_number => c_vgr_rec.object_version_number
123 ,p_date_from => p_date_from
124 ,p_validate => p_validate
125 ,p_effective_date => p_effective_date); --Added for bug# 1760707
126 --
127 l_valid_grade_changed := TRUE;
128 --
129 -- Else if valid grades exist for the job which have a date to that
130 -- is earlier than the new Date from for the job then delete
131 -- those valid grades.
132 --
133 --
134 elsif (c_vgr_rec.date_to < p_date_from) then
135 --
136 hr_utility.set_location(l_proc, 25);
137 --
138 per_vgr_del.del
139 (p_valid_grade_id => c_vgr_rec.valid_grade_id
140 ,p_object_version_number => c_vgr_rec.object_version_number
141 ,p_validate => p_validate);
142 --
143 l_valid_grade_changed := TRUE;
144 --
145 --
146 end if;
147 --
148 END LOOP;
149 --
150 ELSE
151 --
152 -- When maintain_valid_grades has been called to maintain the valid grades
153 -- for a job in accordance with that job's new Date to,
154 -- (ie. p_maintenance_mode = 'DATE_TO') then the p_date_to parameter
155 -- should have been set.
156 --
157 hr_api.mandatory_arg_error
158 (p_api_name => l_proc
159 ,p_argument => 'date_to'
160 ,p_argument_value => p_date_to);
161 --
162 hr_utility.set_location(l_proc, 30);
163 --
164 for c_vgr_rec in csr_valid_grades loop
165 --
166 -- If a valid grade for the job has a Date From that is earlier
167 -- than the new To Date of the job and a Date To that is later than
168 -- that new To Date or is null, then update that valid grade's Date To
169 -- to that new To Date.
170 --
171 hr_utility.set_location(l_proc, 35);
172 --
173 if (c_vgr_rec.date_from < p_date_to and
174 nvl(c_vgr_rec.date_to, hr_api.g_eot) > p_date_to ) then
175 --
176 hr_utility.set_location(l_proc, 40);
177 --
178 per_vgr_upd.upd
179 (p_valid_grade_id => c_vgr_rec.valid_grade_id
180 ,p_object_version_number => c_vgr_rec.object_version_number
181 ,p_date_to => p_date_to
182 ,p_validate => p_validate
183 ,p_effective_date => p_effective_date); -- Added for Bug# 1760707
184 --
185 l_valid_grade_changed := TRUE;
186 --
187 -- Else if valid grades exist for the job which have a date from that
188 -- is later than the new End Date for the job then delete those
189 -- valid grades.
190 --
191 elsif (c_vgr_rec.date_from > p_date_to) then
192 --
193 hr_utility.set_location(l_proc, 45);
194 --
195 per_vgr_del.del
196 (p_valid_grade_id => c_vgr_rec.valid_grade_id
197 ,p_object_version_number => c_vgr_rec.object_version_number
198 ,p_validate => p_validate);
199 --
200 l_valid_grade_changed := TRUE;
201 --
202 end if;
203 --
204 END LOOP;
205 --
206 END IF;
207 --
208 hr_utility.set_location(l_proc, 50);
209 --
210 p_valid_grades_changed := l_valid_grade_changed;
211 hr_utility.set_location('Leaving: '||l_proc, 55);
212 --
213 end maintain_valid_grades;
214 --
215 -- ----------------------------------------------------------------------------
216 -- |------------------------< create_job >------------------------------------|
217 -- ----------------------------------------------------------------------------
218 procedure create_job
219 (p_validate in boolean default false
220 ,p_business_group_id in number
221 ,p_date_from in date
222 ,p_comments in varchar2 default null
223 ,p_date_to in date default null
224 ,p_approval_authority in number default null
225 ,p_benchmark_job_flag in varchar2 default 'N'
226 ,p_benchmark_job_id in number default null
227 ,p_emp_rights_flag in varchar2 default 'N'
228 ,p_job_group_id in number
229 ,p_attribute_category in varchar2 default null
230 ,p_attribute1 in varchar2 default null
231 ,p_attribute2 in varchar2 default null
232 ,p_attribute3 in varchar2 default null
233 ,p_attribute4 in varchar2 default null
234 ,p_attribute5 in varchar2 default null
235 ,p_attribute6 in varchar2 default null
236 ,p_attribute7 in varchar2 default null
237 ,p_attribute8 in varchar2 default null
238 ,p_attribute9 in varchar2 default null
239 ,p_attribute10 in varchar2 default null
240 ,p_attribute11 in varchar2 default null
241 ,p_attribute12 in varchar2 default null
242 ,p_attribute13 in varchar2 default null
243 ,p_attribute14 in varchar2 default null
244 ,p_attribute15 in varchar2 default null
245 ,p_attribute16 in varchar2 default null
246 ,p_attribute17 in varchar2 default null
247 ,p_attribute18 in varchar2 default null
248 ,p_attribute19 in varchar2 default null
249 ,p_attribute20 in varchar2 default null
250 ,p_job_information_category in varchar2 default null
251 ,p_job_information1 in varchar2 default null
252 ,p_job_information2 in varchar2 default null
253 ,p_job_information3 in varchar2 default null
254 ,p_job_information4 in varchar2 default null
255 ,p_job_information5 in varchar2 default null
256 ,p_job_information6 in varchar2 default null
257 ,p_job_information7 in varchar2 default null
258 ,p_job_information8 in varchar2 default null
259 ,p_job_information9 in varchar2 default null
260 ,p_job_information10 in varchar2 default null
261 ,p_job_information11 in varchar2 default null
262 ,p_job_information12 in varchar2 default null
263 ,p_job_information13 in varchar2 default null
264 ,p_job_information14 in varchar2 default null
265 ,p_job_information15 in varchar2 default null
266 ,p_job_information16 in varchar2 default null
267 ,p_job_information17 in varchar2 default null
268 ,p_job_information18 in varchar2 default null
269 ,p_job_information19 in varchar2 default null
270 ,p_job_information20 in varchar2 default null
271 ,p_segment1 in varchar2 default null
272 ,p_segment2 in varchar2 default null
273 ,p_segment3 in varchar2 default null
274 ,p_segment4 in varchar2 default null
275 ,p_segment5 in varchar2 default null
276 ,p_segment6 in varchar2 default null
277 ,p_segment7 in varchar2 default null
278 ,p_segment8 in varchar2 default null
279 ,p_segment9 in varchar2 default null
280 ,p_segment10 in varchar2 default null
281 ,p_segment11 in varchar2 default null
282 ,p_segment12 in varchar2 default null
283 ,p_segment13 in varchar2 default null
284 ,p_segment14 in varchar2 default null
285 ,p_segment15 in varchar2 default null
286 ,p_segment16 in varchar2 default null
287 ,p_segment17 in varchar2 default null
288 ,p_segment18 in varchar2 default null
289 ,p_segment19 in varchar2 default null
290 ,p_segment20 in varchar2 default null
291 ,p_segment21 in varchar2 default null
292 ,p_segment22 in varchar2 default null
293 ,p_segment23 in varchar2 default null
294 ,p_segment24 in varchar2 default null
295 ,p_segment25 in varchar2 default null
296 ,p_segment26 in varchar2 default null
297 ,p_segment27 in varchar2 default null
298 ,p_segment28 in varchar2 default null
299 ,p_segment29 in varchar2 default null
300 ,p_segment30 in varchar2 default null
301 ,p_concat_segments in varchar2 default null
302 ,p_language_code in varchar2 default hr_api.userenv_lang
303 ,p_job_id out nocopy number
304 ,p_object_version_number out nocopy number
305 --
306 -- bug 2242339 change p_job_definition_id from an out to an in/out parameter -- to enable value to be passed into program when known and required.
307 --
308 ,p_job_definition_id in out nocopy number
309 ,p_name out nocopy varchar2
310 ) is
311 --
312 -- Declare cursors and local variables
313 --
314 l_job_id per_jobs.job_id%TYPE;
315 l_job_definition_id per_jobs.job_definition_id%TYPE := p_job_definition_id;
316 l_business_group_id per_jobs.business_group_id%TYPE;
317 l_name per_jobs.name%TYPE;
318 l_proc varchar2(72) := g_package||'create_job';
319 l_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
320 l_object_version_number per_jobs.object_version_number%TYPE;
321 l_date_from per_jobs.date_from%TYPE;
322 l_date_to per_jobs.date_to%TYPE;
323 l_segment1 varchar2(150) := p_segment1;
324 l_segment2 varchar2(150) := p_segment2;
325 l_segment3 varchar2(150) := p_segment3;
326 l_segment4 varchar2(150) := p_segment4;
327 l_segment5 varchar2(150) := p_segment5;
328 l_segment6 varchar2(150) := p_segment6;
329 l_segment7 varchar2(150) := p_segment7;
330 l_segment8 varchar2(150) := p_segment8;
331 l_segment9 varchar2(150) := p_segment9;
332 l_segment10 varchar2(150) := p_segment10;
333 l_segment11 varchar2(150) := p_segment11;
334 l_segment12 varchar2(150) := p_segment12;
335 l_segment13 varchar2(150) := p_segment13;
336 l_segment14 varchar2(150) := p_segment14;
337 l_segment15 varchar2(150) := p_segment15;
338 l_segment16 varchar2(150) := p_segment16;
339 l_segment17 varchar2(150) := p_segment17;
340 l_segment18 varchar2(150) := p_segment18;
341 l_segment19 varchar2(150) := p_segment19;
342 l_segment20 varchar2(150) := p_segment20;
343 l_segment21 varchar2(150) := p_segment21;
344 l_segment22 varchar2(150) := p_segment22;
345 l_segment23 varchar2(150) := p_segment23;
346 l_segment24 varchar2(150) := p_segment24;
347 l_segment25 varchar2(150) := p_segment25;
348 l_segment26 varchar2(150) := p_segment26;
349 l_segment27 varchar2(150) := p_segment27;
350 l_segment28 varchar2(150) := p_segment28;
351 l_segment29 varchar2(150) := p_segment29;
352 l_segment30 varchar2(150) := p_segment30;
353 l_language_code varchar2(30) := p_language_code;
354 --
355 -- bug 2242339 new variable to indicate whether key flex id parameter
356 -- enters the program with a value.
357 --
358 l_null_ind number(1) := 0;
359 --
360 --
361 -- bug 2436606 Use job_group job KFF structure
362 cursor idsel is
363 select pjg.ID_FLEX_NUM
364 from per_job_groups pjg
365 where pjg.job_group_id = p_job_group_id;
366
367 --
368 --
369 -- bug 2242339 get per_job_definition segment values where
370 -- job_definition_id is known
371 --
372 cursor c_segments is
373 select segment1,
374 segment2,
375 segment3,
376 segment4,
377 segment5,
378 segment6,
379 segment7,
380 segment8,
381 segment9,
382 segment10,
383 segment11,
384 segment12,
385 segment13,
386 segment14,
387 segment15,
388 segment16,
389 segment17,
390 segment18,
391 segment19,
392 segment20,
393 segment21,
394 segment22,
395 segment23,
396 segment24,
397 segment25,
398 segment26,
399 segment27,
400 segment28,
401 segment29,
402 segment30
403 from per_job_definitions
404 where job_definition_id = l_job_definition_id;
405 --
406 begin
407 --
408 hr_utility.set_location('Entering:'|| l_proc, 10);
409 --
410 -- Issue a savepoint
411 --
412 savepoint create_job;
413 --
414 -- Validate the language parameter. l_language_code should be passed
415 -- instead of p_language_code from now on, to allow an IN OUT parameter to
416 -- be passed through.
417 --
418 hr_api.validate_language_code(p_language_code => l_language_code);
419 --
420 -- Truncate date_from and date_to
421 --
422 l_date_from := trunc(p_date_from);
423 l_date_to := trunc(p_date_to);
424 --
425 -- check that job group id is valid
426 --
427 per_job_bus.chk_job_group_id
428 (p_job_group_id => p_job_group_id
429 ,p_business_group_id => p_business_group_id);
430 --
431 -- check that flex structure is valid
432 --
433 open idsel;
434 fetch idsel into l_flex_num;
435 if idsel%notfound
436 then
437 close idsel;
438 --
439 -- the flex structure has not been found
440 --
441 hr_utility.set_message(801, 'HR_6039_ALL_CANT_GET_FFIELD');
442 hr_utility.raise_error;
443 end if;
444 close idsel;
445 --
446 -- 2242339 get segment values if p_job_definition_id entered with a value
447 --
448 if l_job_definition_id is not null
449 --
450 then
451 --
452 hr_utility.set_location(l_proc, 15);
453 --
454 -- set indicator to show p_job_definition_id did not enter program null
455 --
456 l_null_ind := 1;
457 --
458 open c_segments;
459 fetch c_segments into
460 l_segment1,
461 l_segment2,
462 l_segment3,
463 l_segment4,
464 l_segment5,
465 l_segment6,
466 l_segment7,
467 l_segment8,
468 l_segment9,
469 l_segment10,
470 l_segment11,
471 l_segment12,
472 l_segment13,
473 l_segment14,
474 l_segment15,
475 l_segment16,
476 l_segment17,
477 l_segment18,
478 l_segment19,
479 l_segment20,
480 l_segment21,
481 l_segment22,
482 l_segment23,
483 l_segment24,
484 l_segment25,
485 l_segment26,
486 l_segment27,
487 l_segment28,
488 l_segment29,
489 l_segment30;
490 close c_segments;
491 end if;
492 --
493 begin
494 --
495 -- Call Before Process User hook for create_job
496 --
497 hr_job_bk1.create_job_b
498 (p_business_group_id => p_business_group_id
499 ,p_date_from => l_date_from
500 ,p_comments => p_comments
501 ,p_date_to => l_date_to
502 ,p_approval_authority => p_approval_authority
503 ,p_benchmark_job_flag => p_benchmark_job_flag
504 ,p_benchmark_job_id => p_benchmark_job_id
505 ,p_emp_rights_flag => p_emp_rights_flag
506 ,p_job_group_id => p_job_group_id
507 ,p_attribute_category => p_attribute_category
508 ,p_attribute1 => p_attribute1
509 ,p_attribute2 => p_attribute2
510 ,p_attribute3 => p_attribute3
511 ,p_attribute4 => p_attribute4
512 ,p_attribute5 => p_attribute5
513 ,p_attribute6 => p_attribute6
514 ,p_attribute7 => p_attribute7
515 ,p_attribute8 => p_attribute8
516 ,p_attribute9 => p_attribute9
517 ,p_attribute10 => p_attribute10
518 ,p_attribute11 => p_attribute11
519 ,p_attribute12 => p_attribute12
520 ,p_attribute13 => p_attribute13
521 ,p_attribute14 => p_attribute14
522 ,p_attribute15 => p_attribute15
523 ,p_attribute16 => p_attribute16
524 ,p_attribute17 => p_attribute17
525 ,p_attribute18 => p_attribute18
526 ,p_attribute19 => p_attribute19
527 ,p_attribute20 => p_attribute20
528 ,p_job_information_category => p_job_information_category
529 ,p_job_information1 => p_job_information1
530 ,p_job_information2 => p_job_information2
531 ,p_job_information3 => p_job_information3
532 ,p_job_information4 => p_job_information4
533 ,p_job_information5 => p_job_information5
534 ,p_job_information6 => p_job_information6
535 ,p_job_information7 => p_job_information7
536 ,p_job_information8 => p_job_information8
537 ,p_job_information9 => p_job_information9
538 ,p_job_information10 => p_job_information10
539 ,p_job_information11 => p_job_information11
540 ,p_job_information12 => p_job_information12
541 ,p_job_information13 => p_job_information13
542 ,p_job_information14 => p_job_information14
543 ,p_job_information15 => p_job_information15
544 ,p_job_information16 => p_job_information16
545 ,p_job_information17 => p_job_information17
546 ,p_job_information18 => p_job_information18
547 ,p_job_information19 => p_job_information19
548 ,p_job_information20 => p_job_information20
549 ,p_segment1 => l_segment1
550 ,p_segment2 => l_segment2
551 ,p_segment3 => l_segment3
552 ,p_segment4 => l_segment4
553 ,p_segment5 => l_segment5
554 ,p_segment6 => l_segment6
555 ,p_segment7 => l_segment7
556 ,p_segment8 => l_segment8
557 ,p_segment9 => l_segment9
558 ,p_segment10 => l_segment10
559 ,p_segment11 => l_segment11
560 ,p_segment12 => l_segment12
561 ,p_segment13 => l_segment13
562 ,p_segment14 => l_segment14
563 ,p_segment15 => l_segment15
564 ,p_segment16 => l_segment16
565 ,p_segment17 => l_segment17
566 ,p_segment18 => l_segment18
567 ,p_segment19 => l_segment19
568 ,p_segment20 => l_segment20
569 ,p_segment21 => l_segment21
570 ,p_segment22 => l_segment22
571 ,p_segment23 => l_segment23
572 ,p_segment24 => l_segment24
573 ,p_segment25 => l_segment25
574 ,p_segment26 => l_segment26
575 ,p_segment27 => l_segment27
576 ,p_segment28 => l_segment28
577 ,p_segment29 => l_segment29
578 ,p_segment30 => l_segment30
579 ,p_concat_segments => p_concat_segments
580 );
581 exception
582 when hr_api.cannot_find_prog_unit then
583 hr_api.cannot_find_prog_unit_error
584 (p_module_name => 'CREATE_JOB'
585 ,p_hook_type => 'BP'
586 );
587 --
588 -- End of before hook process (create_job)
589 --
590 end;
591 --
592 -- Process Logic
593 --
594 --
595 -- Determine the Grade defintion by calling ins_or_sel
596 --
597 hr_utility.set_location(l_proc, 20);
598 --
599 hr_kflex_utility.ins_or_sel_keyflex_comb
600 (p_appl_short_name => 'PER'
601 ,p_flex_code => 'JOB'
602 ,p_flex_num => l_flex_num
603 ,p_segment1 => l_segment1
604 ,p_segment2 => l_segment2
605 ,p_segment3 => l_segment3
606 ,p_segment4 => l_segment4
607 ,p_segment5 => l_segment5
608 ,p_segment6 => l_segment6
609 ,p_segment7 => l_segment7
610 ,p_segment8 => l_segment8
611 ,p_segment9 => l_segment9
612 ,p_segment10 => l_segment10
613 ,p_segment11 => l_segment11
614 ,p_segment12 => l_segment12
615 ,p_segment13 => l_segment13
616 ,p_segment14 => l_segment14
617 ,p_segment15 => l_segment15
618 ,p_segment16 => l_segment16
619 ,p_segment17 => l_segment17
620 ,p_segment18 => l_segment18
621 ,p_segment19 => l_segment19
622 ,p_segment20 => l_segment20
623 ,p_segment21 => l_segment21
624 ,p_segment22 => l_segment22
625 ,p_segment23 => l_segment23
626 ,p_segment24 => l_segment24
627 ,p_segment25 => l_segment25
628 ,p_segment26 => l_segment26
629 ,p_segment27 => l_segment27
630 ,p_segment28 => l_segment28
631 ,p_segment29 => l_segment29
632 ,p_segment30 => l_segment30
633 ,p_concat_segments_in => p_concat_segments
634 ,p_ccid => l_job_definition_id
635 ,p_concat_segments_out => l_name
636 );
637 --
638 if l_job_definition_id is not null
639 then
640 --
641 -- Insert Job.
642 --
643 hr_utility.set_location(l_proc, 30);
644 --
645 per_job_ins.ins
646 (p_job_id => l_job_id
647 ,p_business_group_id => p_business_group_id
648 ,p_job_definition_id => l_job_definition_id
649 ,p_date_from => l_date_from
650 ,p_comments => p_comments
651 ,p_date_to => l_date_to
652 ,p_approval_authority => p_approval_authority
653 ,p_name => l_name
654 ,p_attribute_category => p_attribute_category
655 ,p_attribute1 => p_attribute1
656 ,p_attribute2 => p_attribute2
657 ,p_attribute3 => p_attribute3
658 ,p_attribute4 => p_attribute4
659 ,p_attribute5 => p_attribute5
660 ,p_attribute6 => p_attribute6
661 ,p_attribute7 => p_attribute7
662 ,p_attribute8 => p_attribute8
663 ,p_attribute9 => p_attribute9
664 ,p_attribute10 => p_attribute10
665 ,p_attribute11 => p_attribute11
666 ,p_attribute12 => p_attribute12
667 ,p_attribute13 => p_attribute13
668 ,p_attribute14 => p_attribute14
669 ,p_attribute15 => p_attribute15
670 ,p_attribute16 => p_attribute16
671 ,p_attribute17 => p_attribute17
672 ,p_attribute18 => p_attribute18
673 ,p_attribute19 => p_attribute19
674 ,p_attribute20 => p_attribute20
675 ,p_job_information_category => p_job_information_category
676 ,p_job_information1 => p_job_information1
677 ,p_job_information2 => p_job_information2
678 ,p_job_information3 => p_job_information3
679 ,p_job_information4 => p_job_information4
680 ,p_job_information5 => p_job_information5
681 ,p_job_information6 => p_job_information6
682 ,p_job_information7 => p_job_information7
683 ,p_job_information8 => p_job_information8
684 ,p_job_information9 => p_job_information9
685 ,p_job_information10 => p_job_information10
686 ,p_job_information11 => p_job_information11
687 ,p_job_information12 => p_job_information12
688 ,p_job_information13 => p_job_information13
689 ,p_job_information14 => p_job_information14
690 ,p_job_information15 => p_job_information15
691 ,p_job_information16 => p_job_information16
692 ,p_job_information17 => p_job_information17
693 ,p_job_information18 => p_job_information18
694 ,p_job_information19 => p_job_information19
695 ,p_job_information20 => p_job_information20
696 ,p_benchmark_job_flag => p_benchmark_job_flag
697 ,p_benchmark_job_id => p_benchmark_job_id
698 ,p_emp_rights_flag => p_emp_rights_flag
699 ,p_job_group_id => p_job_group_id
700 ,p_object_version_number => l_object_version_number
701 ,p_validate => FALSE
702 );
703 --
704 --
705 -- MLS Processing
706 --
707 per_jbt_ins.ins_tl( p_language_code => l_language_code
708 ,p_job_id => l_job_id
709 ,p_name => p_name);
710 --
711 hr_utility.set_location(l_proc, 40);
712 --
713 end if;
714 --
715 --
716 -- Call After Process hook for create_job
717 --
718 begin
719 hr_job_bk1.create_job_a
720 (p_business_group_id => p_business_group_id
721 ,p_date_from => l_date_from
722 ,p_comments => p_comments
723 ,p_date_to => l_date_to
724 ,p_approval_authority => p_approval_authority
725 ,p_benchmark_job_flag => p_benchmark_job_flag
726 ,p_benchmark_job_id => p_benchmark_job_id
727 ,p_emp_rights_flag => p_emp_rights_flag
728 ,p_job_group_id => p_job_group_id
729 ,p_attribute_category => p_attribute_category
730 ,p_attribute1 => p_attribute1
731 ,p_attribute2 => p_attribute2
732 ,p_attribute3 => p_attribute3
733 ,p_attribute4 => p_attribute4
734 ,p_attribute5 => p_attribute5
735 ,p_attribute6 => p_attribute6
736 ,p_attribute7 => p_attribute7
737 ,p_attribute8 => p_attribute8
738 ,p_attribute9 => p_attribute9
739 ,p_attribute10 => p_attribute10
740 ,p_attribute11 => p_attribute11
741 ,p_attribute12 => p_attribute12
742 ,p_attribute13 => p_attribute13
743 ,p_attribute14 => p_attribute14
744 ,p_attribute15 => p_attribute15
745 ,p_attribute16 => p_attribute16
746 ,p_attribute17 => p_attribute17
747 ,p_attribute18 => p_attribute18
748 ,p_attribute19 => p_attribute19
749 ,p_attribute20 => p_attribute20
750 ,p_job_information_category => p_job_information_category
751 ,p_job_information1 => p_job_information1
752 ,p_job_information2 => p_job_information2
753 ,p_job_information3 => p_job_information3
754 ,p_job_information4 => p_job_information4
755 ,p_job_information5 => p_job_information5
756 ,p_job_information6 => p_job_information6
757 ,p_job_information7 => p_job_information7
758 ,p_job_information8 => p_job_information8
759 ,p_job_information9 => p_job_information9
760 ,p_job_information10 => p_job_information10
761 ,p_job_information11 => p_job_information11
762 ,p_job_information12 => p_job_information12
763 ,p_job_information13 => p_job_information13
764 ,p_job_information14 => p_job_information14
765 ,p_job_information15 => p_job_information15
766 ,p_job_information16 => p_job_information16
767 ,p_job_information17 => p_job_information17
768 ,p_job_information18 => p_job_information18
769 ,p_job_information19 => p_job_information19
770 ,p_job_information20 => p_job_information20
771 ,p_segment1 => l_segment1
772 ,p_segment2 => l_segment2
773 ,p_segment3 => l_segment3
774 ,p_segment4 => l_segment4
775 ,p_segment5 => l_segment5
776 ,p_segment6 => l_segment6
777 ,p_segment7 => l_segment7
778 ,p_segment8 => l_segment8
779 ,p_segment9 => l_segment9
780 ,p_segment10 => l_segment10
781 ,p_segment11 => l_segment11
782 ,p_segment12 => l_segment12
783 ,p_segment13 => l_segment13
784 ,p_segment14 => l_segment14
785 ,p_segment15 => l_segment15
786 ,p_segment16 => l_segment16
787 ,p_segment17 => l_segment17
788 ,p_segment18 => l_segment18
789 ,p_segment19 => l_segment19
790 ,p_segment20 => l_segment20
791 ,p_segment21 => l_segment21
792 ,p_segment22 => l_segment22
793 ,p_segment23 => l_segment23
794 ,p_segment24 => l_segment24
795 ,p_segment25 => l_segment25
796 ,p_segment26 => l_segment26
797 ,p_segment27 => l_segment27
798 ,p_segment28 => l_segment28
799 ,p_segment29 => l_segment29
800 ,p_segment30 => l_segment30
801 ,p_concat_segments => p_concat_segments
802 ,p_job_id => l_job_id
803 ,p_object_version_number => l_object_version_number
804 ,p_job_definition_id => l_job_definition_id
805 ,p_name => l_name
806 );
807 exception
808 when hr_api.cannot_find_prog_unit then
809 hr_api.cannot_find_prog_unit_error
810 (p_module_name => 'CREATE_JOB'
811 ,p_hook_type => 'AP'
812 );
813 --
814 -- End of after hook process (create_job)
815 --
816 end;
817 --
818 -- When in validation only mode raise the Validate_Enabled exception
819 --
820 if p_validate
821 then
822 raise hr_api.validate_enabled;
823 end if;
824 --
825 hr_utility.set_location(' Leaving:'||l_proc, 50);
826 --
827 -- Set OUT parameters
828 --
829 p_job_id := l_job_id;
830 p_object_version_number := l_object_version_number;
831 p_job_definition_id := l_job_definition_id;
832 p_name := l_name;
833 --
834 hr_utility.set_location(' Leaving:'||l_proc, 60);
835 --
836 exception
837 --
838 when hr_api.validate_enabled then
839 --
840 -- As the Validate_Enabled exception has been raised
841 -- we must rollback to the savepoint
842 --
843 ROLLBACK TO create_job;
844 --
845 -- Set OUT parameters to null
846 -- Only set output warning arguments
847 -- (Any key or derived arguments must be set to null
848 -- when validation only mode is being used.)
849 --
850 if l_null_ind = 0
851 then
852 p_job_definition_id := null;
853 end if;
854 p_job_id := null;
855 p_object_version_number := null;
856 p_job_definition_id := null;
857 p_name := null;
858 --
859 hr_utility.set_location(' Leaving:'||l_proc, 70);
860 --
861 when others then
862 --
863 -- A validation or unexpected error has occurred
864 --
865 -- Added as part of the fix to bug 632479
866 --
867 ROLLBACK TO create_job;
868 --
869 -- set in out parameters and set out parameters
870 --
871 p_job_id := null;
872 p_object_version_number := null;
873 p_name := null;
874 p_job_definition_id := l_job_definition_id;
875 --
876 hr_utility.set_location(' Leaving:'||l_proc, 80);
877 --
878 raise;
879 --
880 end create_job;
881 --
882 -- ----------------------------------------------------------------------------
883 -- |------------------------< update_job >-------------------------------------|
884 -- ----------------------------------------------------------------------------
885 --
886 procedure update_job
887 (p_validate in boolean default false
888 ,p_job_id in number
889 ,p_object_version_number in out nocopy number
890 ,p_date_from in date default hr_api.g_date
891 ,p_comments in varchar2 default hr_api.g_varchar2
892 ,p_date_to in date default hr_api.g_date
893 ,p_benchmark_job_flag in varchar2 default hr_api.g_varchar2
894 ,p_benchmark_job_id in number default hr_api.g_number
895 ,p_emp_rights_flag in varchar2 default hr_api.g_varchar2
896 ,p_attribute_category in varchar2 default hr_api.g_varchar2
897 ,p_attribute1 in varchar2 default hr_api.g_varchar2
898 ,p_attribute2 in varchar2 default hr_api.g_varchar2
899 ,p_attribute3 in varchar2 default hr_api.g_varchar2
900 ,p_attribute4 in varchar2 default hr_api.g_varchar2
901 ,p_attribute5 in varchar2 default hr_api.g_varchar2
902 ,p_attribute6 in varchar2 default hr_api.g_varchar2
903 ,p_attribute7 in varchar2 default hr_api.g_varchar2
904 ,p_attribute8 in varchar2 default hr_api.g_varchar2
905 ,p_attribute9 in varchar2 default hr_api.g_varchar2
906 ,p_attribute10 in varchar2 default hr_api.g_varchar2
907 ,p_attribute11 in varchar2 default hr_api.g_varchar2
908 ,p_attribute12 in varchar2 default hr_api.g_varchar2
909 ,p_attribute13 in varchar2 default hr_api.g_varchar2
910 ,p_attribute14 in varchar2 default hr_api.g_varchar2
911 ,p_attribute15 in varchar2 default hr_api.g_varchar2
912 ,p_attribute16 in varchar2 default hr_api.g_varchar2
913 ,p_attribute17 in varchar2 default hr_api.g_varchar2
914 ,p_attribute18 in varchar2 default hr_api.g_varchar2
915 ,p_attribute19 in varchar2 default hr_api.g_varchar2
916 ,p_attribute20 in varchar2 default hr_api.g_varchar2
917 ,p_job_information_category in varchar2 default hr_api.g_varchar2
918 ,p_job_information1 in varchar2 default hr_api.g_varchar2
919 ,p_job_information2 in varchar2 default hr_api.g_varchar2
920 ,p_job_information3 in varchar2 default hr_api.g_varchar2
921 ,p_job_information4 in varchar2 default hr_api.g_varchar2
922 ,p_job_information5 in varchar2 default hr_api.g_varchar2
923 ,p_job_information6 in varchar2 default hr_api.g_varchar2
924 ,p_job_information7 in varchar2 default hr_api.g_varchar2
925 ,p_job_information8 in varchar2 default hr_api.g_varchar2
926 ,p_job_information9 in varchar2 default hr_api.g_varchar2
927 ,p_job_information10 in varchar2 default hr_api.g_varchar2
928 ,p_job_information11 in varchar2 default hr_api.g_varchar2
929 ,p_job_information12 in varchar2 default hr_api.g_varchar2
930 ,p_job_information13 in varchar2 default hr_api.g_varchar2
931 ,p_job_information14 in varchar2 default hr_api.g_varchar2
932 ,p_job_information15 in varchar2 default hr_api.g_varchar2
933 ,p_job_information16 in varchar2 default hr_api.g_varchar2
934 ,p_job_information17 in varchar2 default hr_api.g_varchar2
935 ,p_job_information18 in varchar2 default hr_api.g_varchar2
936 ,p_job_information19 in varchar2 default hr_api.g_varchar2
937 ,p_job_information20 in varchar2 default hr_api.g_varchar2
938 ,p_segment1 in varchar2 default hr_api.g_varchar2
939 ,p_segment2 in varchar2 default hr_api.g_varchar2
940 ,p_segment3 in varchar2 default hr_api.g_varchar2
941 ,p_segment4 in varchar2 default hr_api.g_varchar2
942 ,p_segment5 in varchar2 default hr_api.g_varchar2
943 ,p_segment6 in varchar2 default hr_api.g_varchar2
944 ,p_segment7 in varchar2 default hr_api.g_varchar2
945 ,p_segment8 in varchar2 default hr_api.g_varchar2
946 ,p_segment9 in varchar2 default hr_api.g_varchar2
947 ,p_segment10 in varchar2 default hr_api.g_varchar2
948 ,p_segment11 in varchar2 default hr_api.g_varchar2
949 ,p_segment12 in varchar2 default hr_api.g_varchar2
950 ,p_segment13 in varchar2 default hr_api.g_varchar2
951 ,p_segment14 in varchar2 default hr_api.g_varchar2
952 ,p_segment15 in varchar2 default hr_api.g_varchar2
953 ,p_segment16 in varchar2 default hr_api.g_varchar2
954 ,p_segment17 in varchar2 default hr_api.g_varchar2
955 ,p_segment18 in varchar2 default hr_api.g_varchar2
956 ,p_segment19 in varchar2 default hr_api.g_varchar2
957 ,p_segment20 in varchar2 default hr_api.g_varchar2
958 ,p_segment21 in varchar2 default hr_api.g_varchar2
959 ,p_segment22 in varchar2 default hr_api.g_varchar2
960 ,p_segment23 in varchar2 default hr_api.g_varchar2
961 ,p_segment24 in varchar2 default hr_api.g_varchar2
962 ,p_segment25 in varchar2 default hr_api.g_varchar2
963 ,p_segment26 in varchar2 default hr_api.g_varchar2
964 ,p_segment27 in varchar2 default hr_api.g_varchar2
965 ,p_segment28 in varchar2 default hr_api.g_varchar2
966 ,p_segment29 in varchar2 default hr_api.g_varchar2
967 ,p_segment30 in varchar2 default hr_api.g_varchar2
968 ,p_concat_segments in varchar2 default hr_api.g_varchar2
969 ,p_approval_authority in number default hr_api.g_number
970 ,p_language_code in varchar2 default hr_api.userenv_lang
971 --
972 -- bug 2242339 change p_job_definition_id from an out to an in/out parameter
973 -- to enable value to be passed into program when known and required.
974 --
975 ,p_job_definition_id in out nocopy number
976 ,p_name out nocopy varchar2
977 ,p_valid_grades_changed_warning out nocopy boolean
978 -- Defaulting it for fix 3138252
979 ,p_effective_date in date default hr_api.g_date --Added for Bug# 1760707
980 ) is
981 --
982 -- Declare cursors and local variables
983 --
984 -- bug 2242339 initialize l_job_definition_id and segment variables with
985 -- values where these are passed into program.
986 --
987 l_job_id per_jobs.job_id%TYPE;
988 l_job_definition_id per_jobs.job_definition_id%TYPE := p_job_definition_id;
989 l_business_group_id per_jobs.business_group_id%TYPE;
990 l_name varchar2(700);
991 l_proc varchar2(72) := g_package||'update_job';
992 l_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
993 l_object_version_number per_jobs.object_version_number%TYPE;
994 l_valid_grades_changed1 boolean default FALSE;
995 l_valid_grades_changed2 boolean default FALSE;
996 l_api_updating boolean;
997 l_date_from per_jobs.date_from%TYPE;
998 l_date_to per_jobs.date_to%TYPE;
999 l_segment1 varchar2(150) := p_segment1;
1000 l_segment2 varchar2(150) := p_segment2;
1001 l_segment3 varchar2(150) := p_segment3;
1002 l_segment4 varchar2(150) := p_segment4;
1003 l_segment5 varchar2(150) := p_segment5;
1004 l_segment6 varchar2(150) := p_segment6;
1005 l_segment7 varchar2(150) := p_segment7;
1006 l_segment8 varchar2(150) := p_segment8;
1007 l_segment9 varchar2(150) := p_segment9;
1008 l_segment10 varchar2(150) := p_segment10;
1009 l_segment11 varchar2(150) := p_segment11;
1010 l_segment12 varchar2(150) := p_segment12;
1011 l_segment13 varchar2(150) := p_segment13;
1012 l_segment14 varchar2(150) := p_segment14;
1013 l_segment15 varchar2(150) := p_segment15;
1014 l_segment16 varchar2(150) := p_segment16;
1015 l_segment17 varchar2(150) := p_segment17;
1016 l_segment18 varchar2(150) := p_segment18;
1017 l_segment19 varchar2(150) := p_segment19;
1018 l_segment20 varchar2(150) := p_segment20;
1019 l_segment21 varchar2(150) := p_segment21;
1020 l_segment22 varchar2(150) := p_segment22;
1021 l_segment23 varchar2(150) := p_segment23;
1022 l_segment24 varchar2(150) := p_segment24;
1023 l_segment25 varchar2(150) := p_segment25;
1024 l_segment26 varchar2(150) := p_segment26;
1025 l_segment27 varchar2(150) := p_segment27;
1026 l_segment28 varchar2(150) := p_segment28;
1027 l_segment29 varchar2(150) := p_segment29;
1028 l_segment30 varchar2(150) := p_segment30;
1029 l_null_ind number(1) := 0;
1030 l_language_code varchar2(30) := p_language_code;
1031 --
1032 -- Declare cursors
1033 --
1034 -- bug 2436606 Use job_group job KFF structure
1035 --
1036 cursor idsel is
1037 select pjg.ID_FLEX_NUM
1038 from per_job_groups pjg
1039 where pjg.JOB_GROUP_ID = (select job_group_id
1040 from per_jobs
1041 where JOB_ID = p_job_id);
1042
1043 --
1044 -- bug 2242339 get per_job_definition segment values where
1045 -- job_definition_id is known
1046 --
1047 cursor c_segments is
1048 select segment1,
1049 segment2,
1050 segment3,
1051 segment4,
1052 segment5,
1053 segment6,
1054 segment7,
1055 segment8,
1056 segment9,
1057 segment10,
1058 segment11,
1059 segment12,
1060 segment13,
1061 segment14,
1062 segment15,
1063 segment16,
1064 segment17,
1065 segment18,
1066 segment19,
1067 segment20,
1068 segment21,
1069 segment22,
1070 segment23,
1071 segment24,
1072 segment25,
1073 segment26,
1074 segment27,
1075 segment28,
1076 segment29,
1077 segment30
1078 from per_job_definitions
1079 where job_definition_id = l_job_definition_id;
1080 --
1081 begin
1082 --
1083 hr_utility.set_location('Entering:'|| l_proc, 5);
1084 --
1085 -- Issue a savepoint
1086 --
1087 savepoint update_job;
1088 --
1089 -- Validate the language parameter. l_language_code should be passed
1090 -- instead of p_language_code from now on, to allow an IN OUT parameter to
1091 -- be passed through.
1092 --
1093 hr_api.validate_language_code(p_language_code => l_language_code);
1094 --
1095 hr_utility.set_location(l_proc, 10);
1096 --
1097 l_object_version_number := p_object_version_number;
1098 --
1099 -- Validation in addition to Table Handlers
1100 --
1101 -- Retrieve current position details from position
1102 --
1103 l_api_updating := per_job_shd.api_updating
1104 (p_job_id => p_job_id
1105 ,p_object_version_number => p_object_version_number);
1106 --
1107 hr_utility.set_location(l_proc, 15);
1108 --
1109 if not l_api_updating
1110 then
1111 hr_utility.set_location(l_proc, 20);
1112 --
1113 -- As this an updating API, the position should already exist.
1114 --
1115 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1116 hr_utility.raise_error;
1117 else
1118 if l_job_definition_id is null
1119 then
1120 l_null_ind := 0;
1121 l_job_definition_id := per_job_shd.g_old_rec.job_definition_id;
1122 hr_utility.set_location(l_proc, 24);
1123 else
1124 -- 2242339
1125 -- get segment values if p_job_definition_id entered with a value
1126 -- set indicator to show p_job_definition_id did not enter program null
1127 --
1128 l_null_ind := 1;
1129 --
1130 open c_segments;
1131 fetch c_segments into
1132 l_segment1,
1133 l_segment2,
1134 l_segment3,
1135 l_segment4,
1136 l_segment5,
1137 l_segment6,
1138 l_segment7,
1139 l_segment8,
1140 l_segment9,
1141 l_segment10,
1142 l_segment11,
1143 l_segment12,
1144 l_segment13,
1145 l_segment14,
1146 l_segment15,
1147 l_segment16,
1148 l_segment17,
1149 l_segment18,
1150 l_segment19,
1151 l_segment20,
1152 l_segment21,
1153 l_segment22,
1154 l_segment23,
1155 l_segment24,
1156 l_segment25,
1157 l_segment26,
1158 l_segment27,
1159 l_segment28,
1160 l_segment29,
1161 l_segment30;
1162 close c_segments;
1163 hr_utility.set_location(l_proc, 27);
1164 --
1165 end if;
1166 --
1167 end if;
1168 --
1169 hr_utility.set_location('Entering: call - update_job_b',35);
1170 --
1171 --
1172 -- Call Before Process User Hook
1173 --
1174 begin
1175 --
1176 hr_job_api_bk2.update_job_b
1177 (p_job_id => p_job_id
1178 ,p_date_from => l_date_from
1179 ,p_comments => p_comments
1180 ,p_date_to => l_date_to
1181 ,p_approval_authority => p_approval_authority
1182 ,p_benchmark_job_flag => p_benchmark_job_flag
1183 ,p_benchmark_job_id => p_benchmark_job_id
1184 ,p_emp_rights_flag => p_emp_rights_flag
1185 ,p_attribute_category => p_attribute_category
1186 ,p_attribute1 => p_attribute1
1187 ,p_attribute2 => p_attribute2
1188 ,p_attribute3 => p_attribute3
1189 ,p_attribute4 => p_attribute4
1190 ,p_attribute5 => p_attribute5
1191 ,p_attribute6 => p_attribute6
1192 ,p_attribute7 => p_attribute7
1193 ,p_attribute8 => p_attribute8
1194 ,p_attribute9 => p_attribute9
1195 ,p_attribute10 => p_attribute10
1196 ,p_attribute11 => p_attribute11
1197 ,p_attribute12 => p_attribute12
1198 ,p_attribute13 => p_attribute13
1199 ,p_attribute14 => p_attribute14
1200 ,p_attribute15 => p_attribute15
1201 ,p_attribute16 => p_attribute16
1202 ,p_attribute17 => p_attribute17
1203 ,p_attribute18 => p_attribute18
1204 ,p_attribute19 => p_attribute19
1205 ,p_attribute20 => p_attribute20
1206 ,p_job_information_category => p_job_information_category
1207 ,p_job_information1 => p_job_information1
1208 ,p_job_information2 => p_job_information2
1209 ,p_job_information3 => p_job_information3
1210 ,p_job_information4 => p_job_information4
1211 ,p_job_information5 => p_job_information5
1212 ,p_job_information6 => p_job_information6
1213 ,p_job_information7 => p_job_information7
1214 ,p_job_information8 => p_job_information8
1215 ,p_job_information9 => p_job_information9
1216 ,p_job_information10 => p_job_information10
1217 ,p_job_information11 => p_job_information11
1218 ,p_job_information12 => p_job_information12
1219 ,p_job_information13 => p_job_information13
1220 ,p_job_information14 => p_job_information14
1221 ,p_job_information15 => p_job_information15
1222 ,p_job_information16 => p_job_information16
1223 ,p_job_information17 => p_job_information17
1224 ,p_job_information18 => p_job_information18
1225 ,p_job_information19 => p_job_information19
1226 ,p_job_information20 => p_job_information20
1227 ,p_segment1 => l_segment1
1228 ,p_segment2 => l_segment2
1229 ,p_segment3 => l_segment3
1230 ,p_segment4 => l_segment4
1231 ,p_segment5 => l_segment5
1232 ,p_segment6 => l_segment6
1233 ,p_segment7 => l_segment7
1234 ,p_segment8 => l_segment8
1235 ,p_segment9 => l_segment9
1236 ,p_segment10 => l_segment10
1237 ,p_segment11 => l_segment11
1238 ,p_segment12 => l_segment12
1239 ,p_segment13 => l_segment13
1240 ,p_segment14 => l_segment14
1241 ,p_segment15 => l_segment15
1242 ,p_segment16 => l_segment16
1243 ,p_segment17 => l_segment17
1244 ,p_segment18 => l_segment18
1245 ,p_segment19 => l_segment19
1246 ,p_segment20 => l_segment20
1247 ,p_segment21 => l_segment21
1248 ,p_segment22 => l_segment22
1249 ,p_segment23 => l_segment23
1250 ,p_segment24 => l_segment24
1251 ,p_segment25 => l_segment25
1252 ,p_segment26 => l_segment26
1253 ,p_segment27 => l_segment27
1254 ,p_segment28 => l_segment28
1255 ,p_segment29 => l_segment29
1256 ,p_segment30 => l_segment30
1257 ,p_concat_segments => p_concat_segments
1258 ,p_name => p_name
1259 ,p_object_version_number => p_object_version_number
1260 ,p_job_definition_id => p_job_definition_id
1261 ,p_effective_date => p_effective_date --Added for bug# 1760707
1262 );
1263 exception
1264 when hr_api.cannot_find_prog_unit
1265 then
1266 hr_api.cannot_find_prog_unit_error
1267 (p_module_name => 'UPDATE_JOB'
1268 ,p_hook_type => 'BP'
1269 );
1270 end; -- End of API User Hook for the before hook of update_job
1271 --
1272 hr_utility.set_location('Entering:'|| l_proc, 30);
1273 --
1274 -- Because we may need to maintain the valid grade dates, need to
1275 -- explicitly lock the per_jobs row.
1276 --
1277 -- per_job_shd.lck(p_job_id => p_job_id
1278 -- ,p_object_version_number => l_object_version_number);
1279 --
1280 hr_utility.set_location('Entering:'|| l_proc, 36);
1281 --
1282 open idsel;
1283 fetch idsel into l_flex_num;
1284 if idsel%notfound
1285 then
1286 hr_utility.set_location('Entering:'|| l_proc, 38);
1287 close idsel;
1288 --
1289 -- the flex structure has not been found
1290 --
1291 hr_utility.set_message(801, 'HR_6039_ALL_CANT_GET_FFIELD');
1292 hr_utility.raise_error;
1293 end if;
1294 close idsel;
1295 hr_utility.set_location(l_proc, 40);
1296 l_date_from := trunc(p_date_from);
1297 l_date_to := trunc(p_date_to);
1298 --
1299 --
1300 -- update job definitions in per_job_definitions if p_job_definition_id had
1301 -- no value when passed into program
1302
1303 hr_utility.set_location(l_proc, 50);
1304 hr_utility.trace('JBD before upd_or_sel '||l_job_definition_id);
1305 --
1306 hr_kflex_utility.upd_or_sel_keyflex_comb
1307 (p_appl_short_name => 'PER'
1308 ,p_flex_code => 'JOB'
1309 ,p_flex_num => l_flex_num
1310 ,p_segment1 => l_segment1
1311 ,p_segment2 => l_segment2
1312 ,p_segment3 => l_segment3
1313 ,p_segment4 => l_segment4
1314 ,p_segment5 => l_segment5
1315 ,p_segment6 => l_segment6
1316 ,p_segment7 => l_segment7
1317 ,p_segment8 => l_segment8
1318 ,p_segment9 => l_segment9
1319 ,p_segment10 => l_segment10 -- #4163409 added
1320 ,p_segment11 => l_segment11 -- #4163409 changed from l_segment10 to l_segment11
1321 ,p_segment12 => l_segment12
1322 ,p_segment13 => l_segment13
1323 ,p_segment14 => l_segment14
1324 ,p_segment15 => l_segment15
1325 ,p_segment16 => l_segment16
1326 ,p_segment17 => l_segment17
1327 ,p_segment18 => l_segment18
1328 ,p_segment19 => l_segment19
1329 ,p_segment20 => l_segment20
1330 ,p_segment21 => l_segment21
1331 ,p_segment22 => l_segment22
1332 ,p_segment23 => l_segment23
1333 ,p_segment24 => l_segment24
1334 ,p_segment25 => l_segment25
1335 ,p_segment26 => l_segment26
1336 ,p_segment27 => l_segment27
1337 ,p_segment28 => l_segment28
1338 ,p_segment29 => l_segment29
1339 ,p_segment30 => l_segment30
1340 ,p_concat_segments_in => p_concat_segments
1341 ,p_ccid => l_job_definition_id
1342 ,p_concat_segments_out => l_name
1343 );
1344 --
1345 hr_utility.set_location(l_proc, 60);
1346 --
1347 -- Because we may need to maintain the valid grade dates, need to
1348 -- explicitly lock the per_jobs row.
1349 --
1350 -- per_job_shd.lck(p_job_id => p_job_id
1351 -- ,p_object_version_number => l_object_version_number);
1352 --
1353 hr_utility.set_location(l_proc, 65);
1354 --
1355 -- If date_from is being updated , then need to maintain valid grades
1356 -- accordingly for that job.
1357 --
1358 if ((nvl(p_date_from, hr_api.g_date) <> hr_api.g_date) and
1359 per_job_shd.g_old_rec.date_from <>
1360 nvl(p_date_from, hr_api.g_date))
1361 then
1362 --
1363 maintain_valid_grades
1364 (p_validate => p_validate
1365 ,p_job_id => p_job_id
1366 ,p_maintenance_mode => c_date_from
1367 ,p_date_to => p_date_to
1368 ,p_date_from => p_date_from
1369 ,p_valid_grades_changed => l_valid_grades_changed1
1370 ,p_effective_date => p_effective_date); --Added for Bug# 1760707
1371 end if;
1372 --
1373 hr_utility.set_location(l_proc, 70);
1374 --
1375 -- If date_end is being updated , then need to maintain valid grades
1376 -- accordingly for that job.
1377 --
1378 if ((nvl(p_date_to, hr_api.g_date) <> hr_api.g_date) and
1379 nvl(per_job_shd.g_old_rec.date_to, hr_api.g_date) <>
1380 nvl(p_date_to, hr_api.g_date))
1381 then
1382 maintain_valid_grades
1383 (p_validate => p_validate
1384 ,p_job_id => p_job_id
1385 ,p_maintenance_mode => c_date_to
1386 ,p_date_to => p_date_to
1387 ,p_date_from => p_date_from
1388 ,p_valid_grades_changed => l_valid_grades_changed2
1389 ,p_effective_date => p_effective_date); --Added for Bug# 1760707
1390 --
1391 end if;
1392 --
1393 --
1394 -- Update Job details.
1395 --
1396 hr_utility.set_location('Entering per_job_upd.upd'||l_proc,80);
1397 --
1398 --
1399 per_job_upd.upd
1400 (p_job_id => p_job_id
1401 ,p_job_definition_id => l_job_definition_id
1402 ,p_date_from => p_date_from
1403 ,p_comments => p_comments
1404 ,p_date_to => p_date_to
1405 ,p_approval_authority => p_approval_authority
1406 ,p_name => l_name
1407 ,p_attribute_category => p_attribute_category
1408 ,p_attribute1 => p_attribute1
1409 ,p_attribute2 => p_attribute2
1410 ,p_attribute3 => p_attribute3
1411 ,p_attribute4 => p_attribute4
1412 ,p_attribute5 => p_attribute5
1413 ,p_attribute6 => p_attribute6
1414 ,p_attribute7 => p_attribute7
1415 ,p_attribute8 => p_attribute8
1416 ,p_attribute9 => p_attribute9
1417 ,p_attribute10 => p_attribute10
1418 ,p_attribute11 => p_attribute11
1419 ,p_attribute12 => p_attribute12
1420 ,p_attribute13 => p_attribute13
1421 ,p_attribute14 => p_attribute14
1422 ,p_attribute15 => p_attribute15
1423 ,p_attribute16 => p_attribute16
1424 ,p_attribute17 => p_attribute17
1425 ,p_attribute18 => p_attribute18
1426 ,p_attribute19 => p_attribute19
1427 ,p_attribute20 => p_attribute20
1428 ,p_job_information_category => p_job_information_category
1429 ,p_job_information1 => p_job_information1
1430 ,p_job_information2 => p_job_information2
1431 ,p_job_information3 => p_job_information3
1432 ,p_job_information4 => p_job_information4
1433 ,p_job_information5 => p_job_information5
1434 ,p_job_information6 => p_job_information6
1435 ,p_job_information7 => p_job_information7
1436 ,p_job_information8 => p_job_information8
1437 ,p_job_information9 => p_job_information9
1438 ,p_job_information10 => p_job_information10
1439 ,p_job_information11 => p_job_information11
1440 ,p_job_information12 => p_job_information12
1441 ,p_job_information13 => p_job_information13
1442 ,p_job_information14 => p_job_information14
1443 ,p_job_information15 => p_job_information15
1444 ,p_job_information16 => p_job_information16
1445 ,p_job_information17 => p_job_information17
1446 ,p_job_information18 => p_job_information18
1447 ,p_job_information19 => p_job_information19
1448 ,p_job_information20 => p_job_information20
1449 ,p_benchmark_job_flag => p_benchmark_job_flag
1450 ,p_benchmark_job_id => p_benchmark_job_id
1451 ,p_emp_rights_flag => p_emp_rights_flag
1452 ,p_object_version_number => l_object_version_number
1453 ,p_validate => p_validate
1454 );
1455 --
1456 -- MLS Processing
1457 --
1458 per_jbt_upd.upd_tl( p_language_code => l_language_code
1459 ,p_job_id => p_job_id
1460 ,p_name => p_name);
1461 --
1462
1463 hr_utility.set_location('Entering: call - update_job_a',55);
1464 --
1465 begin
1466 --
1467 hr_job_api_bk2.update_job_a
1468 (p_job_id => p_job_id
1469 ,p_date_from => l_date_from
1470 ,p_comments => p_comments
1471 ,p_date_to => l_date_to
1472 ,p_approval_authority => p_approval_authority
1473 ,p_benchmark_job_flag => p_benchmark_job_flag
1474 ,p_benchmark_job_id => p_benchmark_job_id
1475 ,p_emp_rights_flag => p_emp_rights_flag
1476 ,p_attribute_category => p_attribute_category
1477 ,p_attribute1 => p_attribute1
1478 ,p_attribute2 => p_attribute2
1479 ,p_attribute3 => p_attribute3
1480 ,p_attribute4 => p_attribute4
1481 ,p_attribute5 => p_attribute5
1482 ,p_attribute6 => p_attribute6
1483 ,p_attribute7 => p_attribute7
1484 ,p_attribute8 => p_attribute8
1485 ,p_attribute9 => p_attribute9
1486 ,p_attribute10 => p_attribute10
1487 ,p_attribute11 => p_attribute11
1488 ,p_attribute12 => p_attribute12
1489 ,p_attribute13 => p_attribute13
1490 ,p_attribute14 => p_attribute14
1491 ,p_attribute15 => p_attribute15
1492 ,p_attribute16 => p_attribute16
1493 ,p_attribute17 => p_attribute17
1494 ,p_attribute18 => p_attribute18
1495 ,p_attribute19 => p_attribute19
1496 ,p_attribute20 => p_attribute20
1497 ,p_job_information_category => p_job_information_category
1498 ,p_job_information1 => p_job_information1
1499 ,p_job_information2 => p_job_information2
1500 ,p_job_information3 => p_job_information3
1501 ,p_job_information4 => p_job_information4
1502 ,p_job_information5 => p_job_information5
1503 ,p_job_information6 => p_job_information6
1504 ,p_job_information7 => p_job_information7
1505 ,p_job_information8 => p_job_information8
1506 ,p_job_information9 => p_job_information9
1507 ,p_job_information10 => p_job_information10
1508 ,p_job_information11 => p_job_information11
1509 ,p_job_information12 => p_job_information12
1510 ,p_job_information13 => p_job_information13
1511 ,p_job_information14 => p_job_information14
1512 ,p_job_information15 => p_job_information15
1513 ,p_job_information16 => p_job_information16
1514 ,p_job_information17 => p_job_information17
1515 ,p_job_information18 => p_job_information18
1516 ,p_job_information19 => p_job_information19
1517 ,p_job_information20 => p_job_information20
1518 ,p_segment1 => l_segment1
1519 ,p_segment2 => l_segment2
1520 ,p_segment3 => l_segment3
1521 ,p_segment4 => l_segment4
1522 ,p_segment5 => l_segment5
1523 ,p_segment6 => l_segment6
1524 ,p_segment7 => l_segment7
1525 ,p_segment8 => l_segment8
1526 ,p_segment9 => l_segment9
1527 ,p_segment10 => l_segment10
1528 ,p_segment11 => l_segment11
1529 ,p_segment12 => l_segment12
1530 ,p_segment13 => l_segment13
1531 ,p_segment14 => l_segment14
1532 ,p_segment15 => l_segment15
1533 ,p_segment16 => l_segment16
1534 ,p_segment17 => l_segment17
1535 ,p_segment18 => l_segment18
1536 ,p_segment19 => l_segment19
1537 ,p_segment20 => l_segment20
1538 ,p_segment21 => l_segment21
1539 ,p_segment22 => l_segment22
1540 ,p_segment23 => l_segment23
1541 ,p_segment24 => l_segment24
1542 ,p_segment25 => l_segment25
1543 ,p_segment26 => l_segment26
1544 ,p_segment27 => l_segment27
1545 ,p_segment28 => l_segment28
1546 ,p_segment29 => l_segment29
1547 ,p_segment30 => l_segment30
1548 ,p_concat_segments => p_concat_segments
1549 ,p_name => p_name
1550 ,p_object_version_number => p_object_version_number
1551 ,p_job_definition_id => p_job_definition_id
1552 ,p_effective_date => p_effective_date --Added for Bug# 1760707
1553 );
1554 --
1555 exception
1556 when hr_api.cannot_find_prog_unit then
1557 hr_api.cannot_find_prog_unit_error
1558 (p_module_name => 'UPDATE_JOB'
1559 ,p_hook_type => 'AP'
1560 );
1561 end; -- End of API User Hook for the after hook of update_job
1562 --
1563 hr_utility.set_location(l_proc, 90);
1564 --
1565 -- When in validation only mode raise the Validate_Enabled exception
1566 --
1567 if p_validate
1568 then
1569 raise hr_api.validate_enabled;
1570 end if;
1571 --
1572 p_object_version_number := l_object_version_number;
1573 if l_valid_grades_changed1 or l_valid_grades_changed2
1574 then
1575 p_valid_grades_changed_warning := TRUE;
1576 else
1577 p_valid_grades_changed_warning := FALSE;
1578 end if;
1579 p_job_definition_id := l_job_definition_id;
1580 p_name := l_name;
1581 --
1582 hr_utility.set_location(' Leaving:'||l_proc, 100);
1583 exception
1584 when hr_api.validate_enabled then
1585 --
1586 -- As the Validate_Enabled exception has been raised
1587 -- we must rollback to the savepoint
1588 --
1589 ROLLBACK TO update_job;
1590 --
1591 -- Only set output warning arguments
1592 -- (Any key or derived arguments must be set to null
1593 -- when validation only mode is being used.)
1594 --
1595 p_object_version_number := p_object_version_number;
1596 if l_valid_grades_changed1 or l_valid_grades_changed2
1597 then
1598 p_valid_grades_changed_warning := TRUE;
1599 else
1600 p_valid_grades_changed_warning := FALSE;
1601 end if;
1602 if l_null_ind = 0
1603 then
1604 p_job_definition_id := null;
1605 end if;
1606 p_name := null;
1607 when others then
1608 --
1609 --
1610 -- A validation or unexpected error has occured
1611 --
1612 rollback to update_job;
1613 --
1614 -- set in out parameters and set out parameters
1615 --
1616 p_name := null;
1617 p_valid_grades_changed_warning := FALSE;
1618 p_object_version_number := l_object_version_number;
1619 p_job_definition_id := l_job_definition_id;
1620 hr_utility.set_location(' Leaving:'||l_proc, 120);
1621 raise;
1622 end update_job;
1623 --
1624 --
1625 -- ----------------------------------------------------------------------------
1626 -- |----------------------------< delete_job >--------------------------------|
1627 -- ----------------------------------------------------------------------------
1628 --
1629
1630 procedure delete_job
1631 (p_validate in boolean
1632 ,p_job_id in number
1633 ,p_object_version_number in out nocopy number) IS
1634
1635 l_object_version_number number(9);
1636 l_proc varchar2(72) := g_package||'DELETE_JOB';
1637 begin
1638 hr_utility.set_location('Entering:'|| l_proc, 10);
1639
1640 --
1641 -- Issue a savepoint
1642 --
1643 savepoint DELETE_JOB;
1644
1645 --
1646 -- Call Before Process User Hook
1647 --
1648 begin
1649 hr_job_api_bk3.delete_job_b
1650 (p_validate => p_validate
1651 ,p_job_id => p_job_id
1652 ,p_object_version_number => p_object_version_number);
1653 exception
1654 when hr_api.cannot_find_prog_unit then
1655 hr_api.cannot_find_prog_unit_error
1656 (p_module_name => 'DELETE_JOB'
1657 ,p_hook_type => 'BP'
1658 );
1659 end;
1660 --
1661 -- MLS Processing
1662 --
1663 per_jbt_del.del_tl(p_job_id => p_job_id);
1664
1665 -- Process Logic
1666 --
1667 l_object_version_number := p_object_version_number;
1668 --
1669 per_job_del.del
1670 (p_job_id => p_job_id
1671 ,p_object_version_number => l_object_version_number);
1672 --
1673 -- Call After Process User Hook
1674 --
1675 begin
1676 hr_job_api_bk3.delete_job_a
1677 (p_validate => p_validate
1678 ,p_job_id => p_job_id
1679 ,p_object_version_number => l_object_version_number);
1680 exception
1681 when hr_api.cannot_find_prog_unit then
1682 hr_api.cannot_find_prog_unit_error
1683 (p_module_name => 'DELETE_JOB'
1684 ,p_hook_type => 'AP'
1685 );
1686 end;
1687 --
1688 -- When in validation only mode raise the Validate_Enabled exception
1689 --
1690 if p_validate then
1691 raise hr_api.validate_enabled;
1692 end if;
1693 --
1694 -- Set all output arguments
1695 --
1696 --
1697 p_object_version_number := l_object_version_number;
1698
1699 hr_utility.set_location(' Leaving:'||l_proc, 70);
1700 exception
1701 when hr_api.validate_enabled then
1702 --
1703 -- As the Validate_Enabled exception has been raised
1704 -- we must rollback to the savepoint
1705 --
1706 rollback to DELETE_JOB;
1707 --
1708 -- Only set output warning arguments
1709 -- (Any key or derived arguments must be set to null
1710 -- when validation only mode is being used.)
1711 --
1712 hr_utility.set_location(' Leaving:'||l_proc, 80);
1713 when others then
1714 --
1715 -- A validation or unexpected error has occured
1716 --
1717 rollback to DELETE_JOB;
1718 --
1719 -- set in out parameters and set out parameters
1720 --
1721 p_object_version_number := l_object_version_number;
1722 hr_utility.set_location(' Leaving:'||l_proc, 90);
1723 raise;
1724 end delete_job;
1725 --
1726 -- ----------------------------------------------------------------------------
1727 -- |----------------------------< get_next_sequence >-------------------------|
1728 -- ----------------------------------------------------------------------------
1729 procedure get_next_sequence(p_job_id in out nocopy number) is
1730 --
1731 cursor c1 is select per_jobs_s.nextval
1732 from sys.dual;
1733 l_proc varchar2(72) := g_package||'get_next_sequence';
1734 --
1735 begin
1736 --
1737 -- Retrieve the next sequence number for job_id
1738 --
1739 if (p_job_id is null) then
1740 open c1;
1741 fetch c1 into p_job_id;
1742 if (C1%NOTFOUND) then
1743 CLOSE C1;
1744 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1745 hr_utility.set_message_token('PROCEDURE','get_next_sequence');
1746 hr_utility.set_message_token('STEP','1');
1747 end if;
1748 close c1;
1749 end if;
1750 --
1751 hr_utility.set_location(l_proc, 10);
1752 --
1753 end get_next_sequence;
1754 --
1755 -- ----------------------------------------------------------------------------
1756 -- |----------------------------< get_job_flex_structure >--------------------|
1757 -- ----------------------------------------------------------------------------
1758 procedure get_job_flex_structure(
1759 p_structure_defining_column in out nocopy varchar2,
1760 p_job_group_id in number) is
1761 --
1762 -- Get the job_flex_structure_id
1763 --
1764 l_struct varchar2(30);
1765 l_proc varchar2(72) := g_package||'get_job_flex_structure';
1766 --
1767 cursor csr_job is select to_char(id_flex_num)
1768 from per_job_groups_v
1769 where p_job_group_id = job_group_id;
1770 --
1771 v_not_found boolean := FALSE;
1772 --
1773 -- Get job flex structure id
1774 --
1775 begin
1776 --
1777 open csr_job;
1778 fetch csr_job into p_structure_defining_column;
1779 v_not_found := csr_job%NOTFOUND;
1780 close csr_job;
1781 --
1782 l_struct := p_structure_defining_column;
1783 hr_utility.set_location('p_struct '||l_struct, 99);
1784 --
1785 if v_not_found then
1786 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1787 hr_utility.set_message_token('PROCEDURE','get_job_flex_structure');
1788 hr_utility.set_message_token('STEP','1');
1789 hr_utility.raise_error;
1790 end if;
1791 --
1792 hr_utility.set_location(l_proc, 10);
1793 --
1794 end get_job_flex_structure;
1795 --
1796 -- ----------------------------------------------------------------------------
1797 -- |----------------------------< update_valid_grades >-----------------------|
1798 -- ----------------------------------------------------------------------------
1799 procedure update_valid_grades(p_business_group_id number,
1800 p_job_id number,
1801 p_date_to date,
1802 p_end_of_time date) is
1803 --
1804 begin
1805 --
1806 -- Update valid grade end dates to match the end date of the
1807 -- job where the end date of the job is earlier than the end
1808 -- date of the valid grade.or the previous end dates matched.
1809 --
1810 --
1811 update per_valid_grades vg
1812 set vg.date_to =
1813 (select least(nvl(p_date_to, p_end_of_time),
1814 nvl(g.date_to, p_end_of_time))
1815 from per_grades g
1816 where g.grade_id = vg.grade_id
1817 and g.business_group_id + 0 = p_business_group_id)
1818 where vg.business_group_id + 0 = p_business_group_id
1819 and vg.job_id = p_job_id
1820 and nvl(vg.date_to, p_end_of_time) > p_date_to;
1821 --
1822 if (SQL%NOTFOUND) then
1823 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1824 hr_utility.set_message_token('PROCEDURE','update_valid_grades');
1825 hr_utility.set_message_token('STEP','1');
1826 hr_utility.raise_error;
1827 end if;
1828 --
1829 --
1830 end update_valid_grades;
1831 --
1832 -- ----------------------------------------------------------------------------
1833 -- |----------------------------< delete_valid_grades >-----------------------|
1834 -- ----------------------------------------------------------------------------
1835 procedure delete_valid_grades(p_business_group_id number,
1836 p_job_id number,
1837 p_date_to date) is
1838 --
1839 begin
1840 --
1841 -- Valid grades are deleted if the end date of the job
1842 -- has been made earlier than the start date of the
1843 -- valid grade.
1844 --
1845 --
1846 delete from per_valid_grades vg
1847 where vg.business_group_id + 0 = p_business_group_id
1848 and vg.job_id = p_job_id
1849 and vg.date_from > p_date_to;
1850 --
1851 --
1852 if (SQL%NOTFOUND) then
1853 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1854 hr_utility.set_message_token('PROCEDURE','delete_valid_grades');
1855 hr_utility.set_message_token('STEP','1');
1856 hr_utility.raise_error;
1857 end if;
1858 --
1859 end delete_valid_grades;
1860 --
1861 end hr_job_api;