[Home] [Help]
PACKAGE BODY: APPS.HR_JOB_REQUIREMENT_API
Source
1 Package Body hr_job_requirement_api as
2 /* $Header: pejbrapi.pkb 115.8 2002/12/11 11:21:19 pkakar ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_job_requirement_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------------< create_job_requirement >-------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_job_requirement
13 (p_validate in boolean default false
14 ,p_id_flex_num in number
15 ,p_job_id in number
16 ,p_comments in varchar2 default null
17 ,p_essential in varchar2 default 'N'
18 ,p_attribute_category in varchar2 default null
19 ,p_attribute1 in varchar2 default null
20 ,p_attribute2 in varchar2 default null
21 ,p_attribute3 in varchar2 default null
22 ,p_attribute4 in varchar2 default null
23 ,p_attribute5 in varchar2 default null
24 ,p_attribute6 in varchar2 default null
25 ,p_attribute7 in varchar2 default null
26 ,p_attribute8 in varchar2 default null
27 ,p_attribute9 in varchar2 default null
28 ,p_attribute10 in varchar2 default null
29 ,p_attribute11 in varchar2 default null
30 ,p_attribute12 in varchar2 default null
31 ,p_attribute13 in varchar2 default null
32 ,p_attribute14 in varchar2 default null
33 ,p_attribute15 in varchar2 default null
34 ,p_attribute16 in varchar2 default null
35 ,p_attribute17 in varchar2 default null
36 ,p_attribute18 in varchar2 default null
37 ,p_attribute19 in varchar2 default null
38 ,p_attribute20 in varchar2 default null
39 ,p_segment1 in varchar2 default null
40 ,p_segment2 in varchar2 default null
41 ,p_segment3 in varchar2 default null
42 ,p_segment4 in varchar2 default null
43 ,p_segment5 in varchar2 default null
44 ,p_segment6 in varchar2 default null
45 ,p_segment7 in varchar2 default null
46 ,p_segment8 in varchar2 default null
47 ,p_segment9 in varchar2 default null
48 ,p_segment10 in varchar2 default null
49 ,p_segment11 in varchar2 default null
50 ,p_segment12 in varchar2 default null
51 ,p_segment13 in varchar2 default null
52 ,p_segment14 in varchar2 default null
53 ,p_segment15 in varchar2 default null
54 ,p_segment16 in varchar2 default null
55 ,p_segment17 in varchar2 default null
56 ,p_segment18 in varchar2 default null
57 ,p_segment19 in varchar2 default null
58 ,p_segment20 in varchar2 default null
59 ,p_segment21 in varchar2 default null
60 ,p_segment22 in varchar2 default null
61 ,p_segment23 in varchar2 default null
62 ,p_segment24 in varchar2 default null
63 ,p_segment25 in varchar2 default null
64 ,p_segment26 in varchar2 default null
65 ,p_segment27 in varchar2 default null
66 ,p_segment28 in varchar2 default null
67 ,p_segment29 in varchar2 default null
68 ,p_segment30 in varchar2 default null
69 ,p_concat_segments in varchar2 default null
70 ,p_job_requirement_id out nocopy number
71 ,p_object_version_number out nocopy number
72 ,p_analysis_criteria_id in out nocopy number
73 ) is
74 --
75 -- Declare cursors and local variables
76 --
77 l_proc varchar2(72) := g_package||'create_job_requirement';
78 --
79 -- bug 2298832 initialize l_analysis_criteria_id and segment variables with
80 -- values where these are passed into program.
81 --
82 l_analysis_criteria_id number := p_analysis_criteria_id;
83 l_temp_analysis number := p_analysis_criteria_id;
84 l_segment1 varchar2(150) := p_segment1;
85 l_segment2 varchar2(150) := p_segment2;
86 l_segment3 varchar2(150) := p_segment3;
87 l_segment4 varchar2(150) := p_segment4;
91 l_segment8 varchar2(150) := p_segment8;
88 l_segment5 varchar2(150) := p_segment5;
89 l_segment6 varchar2(150) := p_segment6;
90 l_segment7 varchar2(150) := p_segment7;
92 l_segment9 varchar2(150) := p_segment9;
93 l_segment10 varchar2(150) := p_segment10;
94 l_segment11 varchar2(150) := p_segment11;
95 l_segment12 varchar2(150) := p_segment12;
96 l_segment13 varchar2(150) := p_segment13;
97 l_segment14 varchar2(150) := p_segment14;
98 l_segment15 varchar2(150) := p_segment15;
99 l_segment16 varchar2(150) := p_segment16;
100 l_segment17 varchar2(150) := p_segment17;
101 l_segment18 varchar2(150) := p_segment18;
102 l_segment19 varchar2(150) := p_segment19;
103 l_segment20 varchar2(150) := p_segment20;
104 l_segment21 varchar2(150) := p_segment21;
105 l_segment22 varchar2(150) := p_segment22;
106 l_segment23 varchar2(150) := p_segment23;
107 l_segment24 varchar2(150) := p_segment24;
108 l_segment25 varchar2(150) := p_segment25;
109 l_segment26 varchar2(150) := p_segment26;
110 l_segment27 varchar2(150) := p_segment27;
111 l_segment28 varchar2(150) := p_segment28;
112 l_segment29 varchar2(150) := p_segment29;
113 l_segment30 varchar2(150) := p_segment30;
114 --
115 -- bug 2298832 new variable to indicate whether key flex id parameter
116 -- enters the program with a value.
117 --
118 l_null_ind number(1) := 0;
119 --
120 -- Declare additional OUT variables
121 --
122 l_job_requirement_id number;
123 l_object_version_number number;
124 l_business_group_id number;
125 l_anc_name varchar2(1000);
126 --
127 cursor csr_job_bg is
128 select business_group_id
129 from per_jobs_v
130 where job_id = p_job_id;
131 --
132 -- bug 2298832 get segment values where analysis criteria id is known
133 --
134 cursor c_segments is
135 select segment1,
136 segment2,
137 segment3,
138 segment4,
139 segment5,
140 segment6,
141 segment7,
142 segment8,
143 segment9,
144 segment10,
145 segment11,
146 segment12,
147 segment13,
148 segment14,
149 segment15,
150 segment16,
151 segment17,
152 segment18,
153 segment19,
154 segment20,
155 segment21,
156 segment22,
157 segment23,
158 segment24,
159 segment25,
160 segment26,
161 segment27,
162 segment28,
163 segment29,
164 segment30
165 from per_analysis_criteria
166 where analysis_criteria_id = l_analysis_criteria_id;
167 --
168 begin
169 --
170 hr_utility.set_location('Entering:'|| l_proc, 5);
171 --
172 -- Issue a savepoint.
173 --
174 savepoint create_job_requirement;
175 --
176 -- Get business_group_id using position or job.
177 --
178 hr_api.mandatory_arg_error
179 (p_api_name => l_proc,
180 p_argument => 'job_id',
181 p_argument_value => p_job_id);
182 --
183 hr_utility.set_location(l_proc, 15);
184 --
185 open csr_job_bg;
186 fetch csr_job_bg
187 into l_business_group_id;
188 --
189 if csr_job_bg%notfound
190 then
191 close csr_job_bg;
192 hr_utility.set_message(801, 'HR_51090_JOB_NOT_EXIST');
193 hr_utility.raise_error;
194 else
195 close csr_job_bg;
196 end if;
197 --
198 -- 2298832 get segment values if p_analysis_criteria_id entered with a value
199 --
200 if l_analysis_criteria_id is not null
201 --
202 then
203 --
204 hr_utility.set_location(l_proc, 15);
205 --
206 -- set indicator to show p_criteria_analysis_id did not enter program null
207 --
208 l_null_ind := 1;
209 --
210 open c_segments;
211 fetch c_segments into
212 l_segment1,
213 l_segment2,
214 l_segment3,
215 l_segment4,
216 l_segment5,
217 l_segment6,
218 l_segment7,
219 l_segment8,
220 l_segment9,
221 l_segment10,
222 l_segment11,
223 l_segment12,
224 l_segment13,
225 l_segment14,
226 l_segment15,
227 l_segment16,
228 l_segment17,
229 l_segment18,
230 l_segment19,
231 l_segment20,
232 l_segment21,
233 l_segment22,
234 l_segment23,
235 l_segment24,
236 l_segment25,
237 l_segment26,
238 l_segment27,
239 l_segment28,
240 l_segment29,
241 l_segment30;
242 if c_segments%NOTFOUND OR c_segments%NOTFOUND IS NULL
243 then
244 l_analysis_criteria_id := NULL;
245 l_null_ind := 0;
246 hr_utility.set_location(l_proc, 27);
247 end if;
248 close c_segments;
249 else
250 l_null_ind := 0;
251 end if;
252 --
253 hr_utility.set_location(l_proc, 20);
254 --
255 begin
256 --
257 -- Start of API User Hook for the before hook of create_job_requirement
258 -- Specifically placed after the business_group_id is retrieved from
259 -- the job.
260 --
261 hr_job_requirement_bk1.create_job_requirement_b
262 (p_id_flex_num => p_id_flex_num
263 ,p_job_id => p_job_id
264 ,p_business_group_id => l_business_group_id
265 ,p_comments => p_comments
266 ,p_essential => p_essential
267 ,p_attribute_category => p_attribute_category
268 ,p_attribute1 => p_attribute1
269 ,p_attribute2 => p_attribute2
270 ,p_attribute3 => p_attribute3
271 ,p_attribute4 => p_attribute4
272 ,p_attribute5 => p_attribute5
273 ,p_attribute6 => p_attribute6
274 ,p_attribute7 => p_attribute7
275 ,p_attribute8 => p_attribute8
276 ,p_attribute9 => p_attribute9
277 ,p_attribute10 => p_attribute10
278 ,p_attribute11 => p_attribute11
279 ,p_attribute12 => p_attribute12
280 ,p_attribute13 => p_attribute13
281 ,p_attribute14 => p_attribute14
282 ,p_attribute15 => p_attribute15
283 ,p_attribute16 => p_attribute16
284 ,p_attribute17 => p_attribute17
285 ,p_attribute18 => p_attribute18
286 ,p_attribute19 => p_attribute19
287 ,p_attribute20 => p_attribute20
288 ,p_segment1 => l_segment1
289 ,p_segment2 => l_segment2
290 ,p_segment3 => l_segment3
291 ,p_segment4 => l_segment4
292 ,p_segment5 => l_segment5
293 ,p_segment6 => l_segment6
294 ,p_segment7 => l_segment7
295 ,p_segment8 => l_segment8
296 ,p_segment9 => l_segment9
297 ,p_segment10 => l_segment10
298 ,p_segment11 => l_segment11
299 ,p_segment12 => l_segment12
300 ,p_segment13 => l_segment13
301 ,p_segment14 => l_segment14
302 ,p_segment15 => l_segment15
303 ,p_segment16 => l_segment16
304 ,p_segment17 => l_segment17
305 ,p_segment18 => l_segment18
306 ,p_segment19 => l_segment19
307 ,p_segment20 => l_segment20
308 ,p_segment21 => l_segment21
309 ,p_segment22 => l_segment22
310 ,p_segment23 => l_segment23
311 ,p_segment24 => l_segment24
312 ,p_segment25 => l_segment25
313 ,p_segment26 => l_segment26
314 ,p_segment27 => l_segment27
315 ,p_segment28 => l_segment28
316 ,p_segment29 => l_segment29
317 ,p_segment30 => l_segment30
318 ,p_concat_segments => p_concat_segments
319 );
320 exception
321 when hr_api.cannot_find_prog_unit then
322 hr_api.cannot_find_prog_unit_error
323 (p_module_name => 'CREATE_JOB_REQUIREMENT'
324 ,p_hook_type => 'BP'
325 );
326 --
327 -- End of API User Hook for the before hook of create_job_requirement
328 --
329 end;
330 --
331 -- bug 2298832: Only call this if p_analysis_criteria_id has no value
332 --
333 if l_null_ind = 0
334 then
335 --
336 -- Insert or select the analysis criteria id
337 --
338 hr_kflex_utility.ins_or_sel_keyflex_comb
339 (p_appl_short_name => 'PER'
340 ,p_flex_code => 'PEA'
341 ,p_flex_num => p_id_flex_num
342 ,p_segment1 => l_segment1
343 ,p_segment2 => l_segment2
344 ,p_segment3 => l_segment3
345 ,p_segment4 => l_segment4
346 ,p_segment5 => l_segment5
347 ,p_segment6 => l_segment6
348 ,p_segment7 => l_segment7
349 ,p_segment8 => l_segment8
350 ,p_segment9 => l_segment9
351 ,p_segment10 => l_segment10
352 ,p_segment11 => l_segment11
353 ,p_segment12 => l_segment12
354 ,p_segment13 => l_segment13
355 ,p_segment14 => l_segment14
356 ,p_segment15 => l_segment15
357 ,p_segment16 => l_segment16
358 ,p_segment17 => l_segment17
359 ,p_segment18 => l_segment18
360 ,p_segment19 => l_segment19
361 ,p_segment20 => l_segment20
362 ,p_segment21 => l_segment21
363 ,p_segment22 => l_segment22
364 ,p_segment23 => l_segment23
365 ,p_segment24 => l_segment24
366 ,p_segment25 => l_segment25
367 ,p_segment26 => l_segment26
368 ,p_segment27 => l_segment27
372 ,p_concat_segments_in => p_concat_segments
369 ,p_segment28 => l_segment28
370 ,p_segment29 => l_segment29
371 ,p_segment30 => l_segment30
373 ,p_ccid => l_analysis_criteria_id
374 ,p_concat_segments_out => l_anc_name
375 );
376 end if;
377 --
378 -- Insert Job Requirements.
379 --
380 -- Set local analysis criteria to pass to ins jbr
381 --
382 hr_utility.set_location(l_proc, 25);
383 --
384 per_jbr_ins.ins
385 (p_job_requirement_id => l_job_requirement_id
386 ,p_business_group_id => l_business_group_id
387 ,p_analysis_criteria_id => l_analysis_criteria_id
388 ,p_comments => p_comments
389 ,p_essential => p_essential
390 ,p_job_id => p_job_id
391 ,p_attribute_category => p_attribute_category
392 ,p_attribute1 => p_attribute1
393 ,p_attribute2 => p_attribute2
394 ,p_attribute3 => p_attribute3
395 ,p_attribute4 => p_attribute4
396 ,p_attribute5 => p_attribute5
397 ,p_attribute6 => p_attribute6
398 ,p_attribute7 => p_attribute7
399 ,p_attribute8 => p_attribute8
400 ,p_attribute9 => p_attribute9
401 ,p_attribute10 => p_attribute10
402 ,p_attribute11 => p_attribute11
403 ,p_attribute12 => p_attribute12
404 ,p_attribute13 => p_attribute13
405 ,p_attribute14 => p_attribute14
406 ,p_attribute15 => p_attribute15
407 ,p_attribute16 => p_attribute16
408 ,p_attribute17 => p_attribute17
409 ,p_attribute18 => p_attribute18
410 ,p_attribute19 => p_attribute19
411 ,p_attribute20 => p_attribute20
412 ,p_object_version_number => l_object_version_number
413 ,p_validate => FALSE
414 );
415 --
416 hr_utility.set_location(l_proc, 30);
417 begin
418 --
419 -- Start of API User Hook for the after hook of create_job_requirement
420 --
421 hr_job_requirement_bk1.create_job_requirement_a
422 (p_id_flex_num => p_id_flex_num
423 ,p_job_id => p_job_id
424 ,p_business_group_id => l_business_group_id
425 ,p_comments => p_comments
426 ,p_essential => p_essential
427 ,p_attribute_category => p_attribute_category
428 ,p_attribute1 => p_attribute1
429 ,p_attribute2 => p_attribute2
430 ,p_attribute3 => p_attribute3
431 ,p_attribute4 => p_attribute4
432 ,p_attribute5 => p_attribute5
433 ,p_attribute6 => p_attribute6
434 ,p_attribute7 => p_attribute7
435 ,p_attribute8 => p_attribute8
436 ,p_attribute9 => p_attribute9
437 ,p_attribute10 => p_attribute10
438 ,p_attribute11 => p_attribute11
439 ,p_attribute12 => p_attribute12
440 ,p_attribute13 => p_attribute13
441 ,p_attribute14 => p_attribute14
442 ,p_attribute15 => p_attribute15
443 ,p_attribute16 => p_attribute16
444 ,p_attribute17 => p_attribute17
445 ,p_attribute18 => p_attribute18
446 ,p_attribute19 => p_attribute19
447 ,p_attribute20 => p_attribute20
448 ,p_segment1 => l_segment1
449 ,p_segment2 => l_segment2
450 ,p_segment3 => l_segment3
451 ,p_segment4 => l_segment4
452 ,p_segment5 => l_segment5
453 ,p_segment6 => l_segment6
454 ,p_segment7 => l_segment7
455 ,p_segment8 => l_segment8
456 ,p_segment9 => l_segment9
457 ,p_segment10 => l_segment10
458 ,p_segment11 => l_segment11
459 ,p_segment12 => l_segment12
460 ,p_segment13 => l_segment13
461 ,p_segment14 => l_segment14
462 ,p_segment15 => l_segment15
463 ,p_segment16 => l_segment16
464 ,p_segment17 => l_segment17
465 ,p_segment18 => l_segment18
466 ,p_segment19 => l_segment19
467 ,p_segment20 => l_segment20
468 ,p_segment21 => l_segment21
469 ,p_segment22 => l_segment22
470 ,p_segment23 => l_segment23
471 ,p_segment24 => l_segment24
472 ,p_segment25 => l_segment25
473 ,p_segment26 => l_segment26
474 ,p_segment27 => l_segment27
475 ,p_segment28 => l_segment28
476 ,p_segment29 => l_segment29
477 ,p_segment30 => l_segment30
478 ,p_concat_segments => p_concat_segments
479 ,p_job_requirement_id => l_job_requirement_id
480 ,p_object_version_number => l_object_version_number
481 ,p_analysis_criteria_id => l_analysis_criteria_id
482 );
483 exception
484 when hr_api.cannot_find_prog_unit then
485 hr_api.cannot_find_prog_unit_error
486 (p_module_name => 'CREATE_JOB_REQUIREMENT'
487 ,p_hook_type => 'AP'
488 );
489 --
490 -- End of API User Hook for the after hook of create_job_requirement
491 --
492 end;
493 --
494 -- When in validation only mode raise the Validate_Enabled exception
495 --
496 if p_validate then
497 raise hr_api.validate_enabled;
498 end if;
499 --
500 -- Set remaining output arguments
501 --
502 p_job_requirement_id := l_job_requirement_id;
503 p_object_version_number := l_object_version_number;
504 p_analysis_criteria_id := l_analysis_criteria_id;
505 --
506 hr_utility.set_location('Leaving:'||l_proc, 35);
507 exception
508 when hr_api.validate_enabled then
509 --
510 -- As the Validate_Enabled exception has been raised
511 -- we must rollback to the savepoint
512 --
513 ROLLBACK TO create_job_requirement;
514 --
515 -- Only set output warning arguments
516 -- (Any key or derived arguments must be set to null
517 -- when validation only mode is being used.)
518 --
519 p_job_requirement_id := null;
520 p_object_version_number := null;
521 if l_null_ind = 0
522 then
523 p_analysis_criteria_id := null;
524 end if;
525 --
526 when others then
527 --
528 -- A validation or unexpected error has occurred
529 --
530 -- Added as part of fix to bug 632479
531 --
532 ROLLBACK TO create_job_requirement;
533 --
534 -- set in out parameters and set out parameters
535 --
536 p_job_requirement_id := null;
537 p_object_version_number := null;
538 p_analysis_criteria_id := l_temp_analysis;
539 raise;
540 --
541 -- End of fix.
542 --
543 --
544 end create_job_requirement;
545 --
546 end hr_job_requirement_api;
547 --