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