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