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