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