[Home] [Help]
PACKAGE BODY: APPS.PER_POD_SHD
Source
1 Package Body per_pod_shd as
2 /* $Header: pepodrhi.pkb 115.6 2002/12/04 10:56:05 eumenyio ship $ */
3 --
4 -- ---------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ---------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pod_shd.'; -- Global package name
9 -- ----------------------------------------------------------------------------
10 -- |------------------------< segment_combination_check >---------------------|
11 -- ----------------------------------------------------------------------------
12 procedure segment_combination_check
13 (p_segment1 in varchar2 default null,
14 p_segment2 in varchar2 default null,
15 p_segment3 in varchar2 default null,
16 p_segment4 in varchar2 default null,
17 p_segment5 in varchar2 default null,
18 p_segment6 in varchar2 default null,
19 p_segment7 in varchar2 default null,
20 p_segment8 in varchar2 default null,
21 p_segment9 in varchar2 default null,
22 p_segment10 in varchar2 default null,
23 p_segment11 in varchar2 default null,
24 p_segment12 in varchar2 default null,
25 p_segment13 in varchar2 default null,
26 p_segment14 in varchar2 default null,
27 p_segment15 in varchar2 default null,
28 p_segment16 in varchar2 default null,
29 p_segment17 in varchar2 default null,
30 p_segment18 in varchar2 default null,
31 p_segment19 in varchar2 default null,
32 p_segment20 in varchar2 default null,
33 p_segment21 in varchar2 default null,
34 p_segment22 in varchar2 default null,
35 p_segment23 in varchar2 default null,
36 p_segment24 in varchar2 default null,
37 p_segment25 in varchar2 default null,
38 p_segment26 in varchar2 default null,
39 p_segment27 in varchar2 default null,
40 p_segment28 in varchar2 default null,
41 p_segment29 in varchar2 default null,
42 p_segment30 in varchar2 default null,
43 p_business_group_id in number,
44 p_position_definition_id out nocopy number,
45 p_name out nocopy varchar2,
46 p_id_flex_num out nocopy number) is
47 --
48 l_id_flex_num number;
49 l_proc varchar2(72) := g_package||'segment_combination_check';
50
51 --
52 -- the cursor orgsel selects the valid id_flex_num (position def kf)
53 -- for the specified business group
54 --
55 cursor idsel is
56 select pbg.position_structure
57 from per_business_groups pbg
58 where pbg.business_group_id = p_business_group_id;
59 --
60 -- Modified cursor definition Bug 2559439
61 cursor pgsel is
62 select pod.position_definition_id
63 from per_positions pod
64 where name = p_name
65 and exists (select 'x'
66 from per_position_definitions ppd
67 where ppd.position_definition_id = pod.position_definition_id
68 and ppd.id_flex_num = l_id_flex_num
69 and ppd.enabled_flag = 'Y');
70
71 -- the cursor pgsel selects the position_definition_id
72 --
73 -- cursor pgsel is
74 -- select pod.position_definition_id
75 -- from per_position_definitions pod
76 -- where pod.id_flex_num = l_id_flex_num
77 -- and pod.enabled_flag = 'Y'
78 -- and (pod.segment1 = p_segment1
79 -- or (pod.segment1 is null
80 -- and p_segment1 is null))
81 -- and (pod.segment2 = p_segment2
82 -- or (pod.segment2 is null
83 -- and p_segment2 is null))
84 -- and (pod.segment3 = p_segment3
85 -- or (pod.segment3 is null
86 -- and p_segment3 is null))
87 -- and (pod.segment4 = p_segment4
88 -- or (pod.segment4 is null
89 -- and p_segment4 is null))
90 -- and (pod.segment5 = p_segment5
91 -- or (pod.segment5 is null
92 -- and p_segment5 is null))
93 -- and (pod.segment6 = p_segment6
94 -- or (pod.segment6 is null
95 -- and p_segment6 is null))
96 -- and (pod.segment7 = p_segment7
97 -- or (pod.segment7 is null
98 -- and p_segment7 is null))
99 -- and (pod.segment8 = p_segment8
100 -- or (pod.segment8 is null
101 -- and p_segment8 is null))
102 -- and (pod.segment9 = p_segment9
103 -- or (pod.segment9 is null
104 -- and p_segment9 is null))
105 -- and (pod.segment10 = p_segment10
106 -- or (pod.segment10 is null
107 -- and p_segment10 is null))
108 -- and (pod.segment11 = p_segment11
109 -- or (pod.segment11 is null
110 -- and p_segment11 is null))
111 -- and (pod.segment12 = p_segment12
112 -- or (pod.segment12 is null
113 -- and p_segment12 is null))
114 -- and (pod.segment13 = p_segment13
115 -- or (pod.segment13 is null
116 -- and p_segment13 is null))
117 -- and (pod.segment14 = p_segment14
118 -- or (pod.segment14 is null
119 -- and p_segment14 is null))
120 -- and (pod.segment15 = p_segment15
121 -- or (pod.segment15 is null
122 -- and p_segment15 is null))
123 -- and (pod.segment16 = p_segment16
124 -- or (pod.segment16 is null
125 -- and p_segment16 is null))
126 -- and (pod.segment17 = p_segment17
127 -- or (pod.segment17 is null
128 -- and p_segment17 is null))
129 -- and (pod.segment18 = p_segment18
130 -- or (pod.segment18 is null
131 -- and p_segment18 is null))
132 -- and (pod.segment19 = p_segment19
133 -- or (pod.segment19 is null
134 -- and p_segment19 is null))
135 -- and (pod.segment20 = p_segment20
136 -- or (pod.segment20 is null
137 -- and p_segment20 is null))
138 -- and (pod.segment21 = p_segment21
139 -- or (pod.segment21 is null
140 -- and p_segment21 is null))
141 -- and (pod.segment22 = p_segment22
142 -- or (pod.segment22 is null
143 -- and p_segment22 is null))
144 -- and (pod.segment23 = p_segment23
145 -- or (pod.segment23 is null
146 -- and p_segment23 is null))
147 -- and (pod.segment24 = p_segment24
148 -- or (pod.segment24 is null
149 -- and p_segment24 is null))
150 -- and (pod.segment25 = p_segment25
151 -- or (pod.segment25 is null
152 -- and p_segment25 is null))
153 -- and (pod.segment26 = p_segment26
154 -- or (pod.segment26 is null
155 -- and p_segment26 is null))
156 -- and (pod.segment27 = p_segment27
157 -- or (pod.segment27 is null
158 -- and p_segment27 is null))
159 -- and (pod.segment28 = p_segment28
160 -- or (pod.segment28 is null
161 -- and p_segment28 is null))
162 -- and (pod.segment29 = p_segment29
163 -- or (pod.segment29 is null
164 -- and p_segment29 is null))
165 -- and (pod.segment30 = p_segment30
166 -- or (pod.segment30 is null
167 -- and p_segment30 is null));
168 --
169 begin
170 hr_utility.set_location('Entering:'||l_proc, 5);
171 hr_api.validate_bus_grp_id(p_business_group_id);
172 --
173 -- Determine if all the segments are null
174 --
175 if (p_segment1 is null and
176 p_segment2 is null and
177 p_segment3 is null and
178 p_segment4 is null and
179 p_segment5 is null and
180 p_segment6 is null and
181 p_segment7 is null and
182 p_segment8 is null and
183 p_segment9 is null and
184 p_segment10 is null and
185 p_segment11 is null and
186 p_segment12 is null and
187 p_segment13 is null and
188 p_segment14 is null and
189 p_segment15 is null and
190 p_segment16 is null and
191 p_segment17 is null and
192 p_segment18 is null and
193 p_segment19 is null and
194 p_segment20 is null and
195 p_segment21 is null and
196 p_segment22 is null and
197 p_segment23 is null and
198 p_segment24 is null and
199 p_segment25 is null and
200 p_segment26 is null and
201 p_segment27 is null and
202 p_segment28 is null and
203 p_segment29 is null and
204 p_segment30 is null) then
205 --
206 -- as the segments are null set the p_id_flex_num
207 -- explicitly to null.
208 --
209 hr_utility.set_location(l_proc, 10);
210 p_id_flex_num := null;
211 p_name := null;
212 else
213 --
214 -- segments exists therefore select the id_flex_num
215 --
216 hr_utility.set_location(l_proc, 15);
217 open idsel;
218 fetch idsel into l_id_flex_num;
219 if idsel%notfound then
220 close idsel;
221 --
222 -- the flex structure has not been found therefore we must error
223 --
224 hr_utility.set_message(801, 'HR_51054_FLEX_POD_INVALID_ID');
225 hr_utility.raise_error;
226 end if;
227 close idsel;
228 hr_utility.set_location(l_proc, 10);
229 p_id_flex_num := l_id_flex_num;
230 --
231 -- open and execute the partial segment cursor. if no rows are returned
232 -- then p_position_definition_id must be set to -1 (indicating a
233 -- new combination needs to be inserted.
234 --
235 hr_utility.set_location(l_proc, 20);
236 --
237 -- we must derive the position name
238 --
239 p_name :=
240 hr_api.return_concat_kf_segments
241 (p_id_flex_num => l_id_flex_num,
242 p_segment1 => p_segment1,
243 p_segment2 => p_segment2,
244 p_segment3 => p_segment3,
245 p_segment4 => p_segment4,
246 p_segment5 => p_segment5,
247 p_segment6 => p_segment6,
248 p_segment7 => p_segment7,
249 p_segment8 => p_segment8,
250 p_segment9 => p_segment9,
251 p_segment10 => p_segment10,
252 p_segment11 => p_segment11,
253 p_segment12 => p_segment12,
254 p_segment13 => p_segment13,
255 p_segment14 => p_segment14,
256 p_segment15 => p_segment15,
257 p_segment16 => p_segment16,
258 p_segment17 => p_segment17,
259 p_segment18 => p_segment18,
260 p_segment19 => p_segment19,
261 p_segment20 => p_segment20,
262 p_segment21 => p_segment21,
263 p_segment22 => p_segment22,
264 p_segment23 => p_segment23,
265 p_segment24 => p_segment24,
266 p_segment25 => p_segment25,
267 p_segment26 => p_segment26,
268 p_segment27 => p_segment27,
269 p_segment28 => p_segment28,
270 p_segment29 => p_segment29,
271 p_segment30 => p_segment30,
272 p_application_id => 800,
273 p_id_flex_code => 'POS');
274
275 open pgsel;
276 fetch pgsel into p_position_definition_id;
277 if pgsel%notfound then
278 hr_utility.set_location(l_proc, 25);
279 p_position_definition_id := -1;
280 end if;
281 close pgsel;
282
283 end if;
284 --
285 hr_utility.set_location(' Leaving:'||l_proc, 30);
286 end segment_combination_check;
287 --
288 -- ----------------------------------------------------------------------------
289 -- |------------------------< return_api_dml_status >-------------------------|
290 -- ----------------------------------------------------------------------------
291 Function return_api_dml_status Return Boolean Is
292 --
293 l_proc varchar2(72) := g_package||'return_api_dml_status';
294 --
295 Begin
296 hr_utility.set_location('Entering:'||l_proc, 5);
297 --
298 Return (nvl(g_api_dml, false));
299 --
300 hr_utility.set_location(' Leaving:'||l_proc, 10);
301 End return_api_dml_status;
302 --
303 -- ----------------------------------------------------------------------------
304 -- |---------------------------< constraint_error >---------------------------|
305 -- ----------------------------------------------------------------------------
306 Procedure constraint_error
307 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
308 --
309 l_proc varchar2(72) := g_package||'constraint_error';
310 --
311 Begin
312 hr_utility.set_location('Entering:'||l_proc, 5);
313 --
314 If (p_constraint_name = 'PER_POSITION_DEFINITIONS_PK') Then
315 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
316 hr_utility.raise_error;
317 Else
318 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
319 hr_utility.set_message_token('PROCEDURE', l_proc);
320 hr_utility.set_message_token('CONSTRAINT_NAME',p_constraint_name);
321 hr_utility.raise_error;
322 End If;
323 --
324 hr_utility.set_location(' Leaving:'||l_proc, 10);
325 End constraint_error;
326 --
327 -- ----------------------------------------------------------------------------
328 -- |-----------------------------< api_updating >-----------------------------|
329 -- ----------------------------------------------------------------------------
330 Function api_updating(p_position_definition_id in number) Return Boolean Is
331 --
332 --
333 -- Cursor selects the 'current' row from the HR Schema
334 --
335 Cursor C_Sel1 is
336 select
337 position_definition_id,
338 id_flex_num,
339 summary_flag,
340 enabled_flag,
341 start_date_active,
342 end_date_active,
343 segment1,
344 segment2,
345 segment3,
346 segment4,
347 segment5,
348 segment6,
349 segment7,
350 segment8,
351 segment9,
352 segment10,
353 segment11,
354 segment12,
355 segment13,
356 segment14,
357 segment15,
358 segment16,
359 segment17,
360 segment18,
361 segment19,
362 segment20,
363 segment21,
364 segment22,
365 segment23,
366 segment24,
367 segment25,
368 segment26,
369 segment27,
370 segment28,
371 segment29,
372 segment30
373 from per_position_definitions
374 where position_definition_id = p_position_definition_id;
375 --
376 l_proc varchar2(72) := g_package||'api_updating';
377 l_fct_ret boolean;
378 --
379 Begin
380 hr_utility.set_location('Entering:'||l_proc, 5);
381 --
382 If (p_position_definition_id is null) Then
383 --
384 -- One of the primary key arguments is null therefore we must
385 -- set the returning function value to false
386 --
387 l_fct_ret := false;
388 Else
389 If (p_position_definition_id = g_old_rec.position_definition_id) Then
390 hr_utility.set_location(l_proc, 10);
391 --
395 l_fct_ret := true;
392 -- The g_old_rec is current therefore we must
393 -- set the returning function to true
394 --
396 Else
397 --
398 -- Select the current row into g_old_rec
399 --
400 Open C_Sel1;
401 Fetch C_Sel1 Into g_old_rec;
402 If C_Sel1%notfound Then
403 Close C_Sel1;
404 --
405 -- The primary key is invalid therefore we must error
406 --
407 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
408 hr_utility.raise_error;
409 End If;
410 Close C_Sel1;
411 hr_utility.set_location(l_proc, 15);
412 l_fct_ret := true;
413 End If;
414 End If;
415 hr_utility.set_location(' Leaving:'||l_proc, 20);
416 Return (l_fct_ret);
417 --
418 End api_updating;
419 --
420 -- ----------------------------------------------------------------------------
421 -- |-----------------------------< convert_args >------------------------------
422 -- ----------------------------------------------------------------------------
423 Function convert_args
424 (
425 p_position_definition_id in number,
426 p_id_flex_num in number,
427 p_summary_flag in varchar2,
428 p_enabled_flag in varchar2,
429 p_start_date_active in date,
430 p_end_date_active in date,
431 p_segment1 in varchar2,
432 p_segment2 in varchar2,
433 p_segment3 in varchar2,
434 p_segment4 in varchar2,
435 p_segment5 in varchar2,
436 p_segment6 in varchar2,
437 p_segment7 in varchar2,
438 p_segment8 in varchar2,
439 p_segment9 in varchar2,
440 p_segment10 in varchar2,
441 p_segment11 in varchar2,
442 p_segment12 in varchar2,
443 p_segment13 in varchar2,
444 p_segment14 in varchar2,
445 p_segment15 in varchar2,
446 p_segment16 in varchar2,
447 p_segment17 in varchar2,
448 p_segment18 in varchar2,
449 p_segment19 in varchar2,
450 p_segment20 in varchar2,
451 p_segment21 in varchar2,
452 p_segment22 in varchar2,
453 p_segment23 in varchar2,
454 p_segment24 in varchar2,
455 p_segment25 in varchar2,
456 p_segment26 in varchar2,
457 p_segment27 in varchar2,
458 p_segment28 in varchar2,
459 p_segment29 in varchar2,
460 p_segment30 in varchar2
461 )
462 Return g_rec_type is
463 --
464 l_rec g_rec_type;
465 l_proc varchar2(72) := g_package||'convert_args';
466 --
467 Begin
468 --
469 hr_utility.set_location('Entering:'||l_proc, 5);
470 --
471 -- Convert arguments into local l_rec structure.
472 --
473 l_rec.position_definition_id := p_position_definition_id;
474 l_rec.id_flex_num := p_id_flex_num;
475 l_rec.summary_flag := p_summary_flag;
476 l_rec.enabled_flag := p_enabled_flag;
477 l_rec.start_date_active := p_start_date_active;
478 l_rec.end_date_active := p_end_date_active;
479 l_rec.segment1 := p_segment1;
480 l_rec.segment2 := p_segment2;
481 l_rec.segment3 := p_segment3;
482 l_rec.segment4 := p_segment4;
483 l_rec.segment5 := p_segment5;
484 l_rec.segment6 := p_segment6;
485 l_rec.segment7 := p_segment7;
486 l_rec.segment8 := p_segment8;
487 l_rec.segment9 := p_segment9;
488 l_rec.segment10 := p_segment10;
489 l_rec.segment11 := p_segment11;
490 l_rec.segment12 := p_segment12;
491 l_rec.segment13 := p_segment13;
492 l_rec.segment14 := p_segment14;
493 l_rec.segment15 := p_segment15;
494 l_rec.segment16 := p_segment16;
495 l_rec.segment17 := p_segment17;
496 l_rec.segment18 := p_segment18;
497 l_rec.segment19 := p_segment19;
498 l_rec.segment20 := p_segment20;
499 l_rec.segment21 := p_segment21;
500 l_rec.segment22 := p_segment22;
501 l_rec.segment23 := p_segment23;
502 l_rec.segment24 := p_segment24;
503 l_rec.segment25 := p_segment25;
504 l_rec.segment26 := p_segment26;
505 l_rec.segment27 := p_segment27;
506 l_rec.segment28 := p_segment28;
507 l_rec.segment29 := p_segment29;
508 l_rec.segment30 := p_segment30;
509 --
510 -- Return the plsql record structure.
511 --
512 hr_utility.set_location(' Leaving:'||l_proc, 10);
513 Return(l_rec);
514 --
515 End convert_args;
516 --
517 end per_pod_shd;