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