[Home] [Help]
PACKAGE BODY: APPS.PER_PDS_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body per_pds_shd as
2 /* $Header: pepdsrhi.pkb 120.8.12020000.3 2013/01/30 09:27:33 srannama ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pds_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 l_proc varchar2(72) := g_package||'return_api_dml_status';
16 --
17 Begin
18 hr_utility.set_location('Entering:'||l_proc, 5);
19 --
20 Return (nvl(g_api_dml, false));
21 --
22 hr_utility.set_location(' Leaving:'||l_proc, 10);
23 End return_api_dml_status;
24 --
25 -- ----------------------------------------------------------------------------
29 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
26 -- |---------------------------< constraint_error >---------------------------|
27 -- ----------------------------------------------------------------------------
28 Procedure constraint_error
30 --
31 l_proc varchar2(72) := g_package||'constraint_error';
32 --
33 Begin
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 --
36 If (p_constraint_name = 'PER_PERIODS_OF_SERVICE_FK1') Then
37 --
38 -- The business_group_id is invalid
39 --
40 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
41 --
42 ElsIf (p_constraint_name = 'PER_PERIODS_OF_SERVICE_PK') Then
43 --
44 -- The primary key is not unique
45 --
46 hr_utility.set_message(801, 'HR_7391_ASG_INV_PERIOD_OF_SERV');
47 --
48 elsif (p_constraint_name = 'SSP_PDS_SSP_WEEKS_NATURAL_NUM') then
49 --
50 -- Prior_employment_SSP_weeks must be an integer greater than zero
51 --
52 hr_utility.set_message (801,'HR_51100_PDS_SSP_WEEKS_NATURAL');
53 --
54 elsif (p_constraint_name = 'SSP_PDS_SSP_PAID_TO_PRE_HIRE') then
55 --
56 -- Prior_employment_SSP_paid_to must be before date_start
57 --
58 hr_utility.set_message (801,'HR_51102_PDS_SSP_PAID_PRE_HIRE');
59 --
60 elsif (p_constraint_name = 'SSP_PDS_SSP1L_DETAILS') then
61 --
62 -- Prior_employment... fields are mutually inclusive
63 --
64 hr_utility.set_message (801,'HR_51101_PDS_SSP1L_DETAILS');
65 --
66 elsif (p_constraint_name = 'PER_ASSIGNMENTS_F_FK51') then
67 --
68 -- Assignment rows must be deleted before period of service
69 --
70 hr_utility.set_message (801,'HR_51392_PDS_DEL_ASG_FIRST');
71 --
72 Else
73 --
74 -- A constraint was added after this code was delivered and is not
75 -- handled by this procedure.
76 --
77 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
78 hr_utility.set_message_token('PROCEDURE', l_proc);
79 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
80 --
81 End If;
82 --
83 hr_utility.raise_error;
84 --
85 hr_utility.set_location(' Leaving:'||l_proc, 10);
86 End constraint_error;
87 --
88 -- ----------------------------------------------------------------------------
89 -- |-----------------------------< api_updating >-----------------------------|
90 -- ----------------------------------------------------------------------------
91 Function api_updating
92 (
93 p_period_of_service_id in number,
94 p_object_version_number in number
95 ) Return Boolean Is
96 --
97 --
98 -- Cursor selects the 'current' row from the HR Schema
99 --
100 Cursor C_Sel1 is
101 select
102 period_of_service_id,
103 business_group_id,
104 termination_accepted_person_id,
105 person_id,
106 date_start,
107 accepted_termination_date,
108 actual_termination_date,
109 comments,
110 final_process_date,
111 last_standard_process_date,
112 leaving_reason,
113 notified_termination_date,
114 projected_termination_date,
115 adjusted_svc_date,
116 request_id,
117 program_application_id,
118 program_id,
119 program_update_date,
120 attribute_category,
121 attribute1,
122 attribute2,
123 attribute3,
124 attribute4,
125 attribute5,
126 attribute6,
127 attribute7,
128 attribute8,
129 attribute9,
130 attribute10,
131 attribute11,
132 attribute12,
133 attribute13,
134 attribute14,
135 attribute15,
136 attribute16,
137 attribute17,
138 attribute18,
139 attribute19,
140 attribute20,
141 object_version_number,
142 prior_employment_ssp_weeks,
143 prior_employment_ssp_paid_to,
144 pds_information_category,
145 pds_information1,
146 pds_information2,
147 pds_information3,
148 pds_information4,
149 pds_information5,
150 pds_information6,
151 pds_information7,
152 pds_information8,
153 pds_information9,
154 pds_information10,
155 pds_information11,
156 pds_information12,
157 pds_information13,
158 pds_information14,
159 pds_information15,
160 pds_information16,
161 pds_information17,
162 pds_information18,
163 pds_information19,
164 pds_information20,
165 pds_information21,
166 pds_information22,
167 pds_information23,
168 pds_information24,
169 pds_information25,
170 pds_information26,
171 pds_information27,
172 pds_information28,
173 pds_information29,
174 pds_information30
175 from per_periods_of_service pds
176 where pds.period_of_service_id = p_period_of_service_id;
177 --
178 l_proc varchar2(72) := g_package||'api_updating';
179 l_fct_ret boolean;
180 --
181 Begin
182 hr_utility.set_location('Entering:'||l_proc, 5);
183 --
184 If (
185 p_period_of_service_id is null and
186 p_object_version_number is null
187 ) Then
188 --
189 -- One of the primary key arguments is null therefore we must
190 -- set the returning function value to false
191 --
192 l_fct_ret := false;
193 Else
194 If (
195 p_period_of_service_id = g_old_rec.period_of_service_id and
199 --
196 p_object_version_number = g_old_rec.object_version_number
197 ) Then
198 hr_utility.set_location(l_proc, 10);
200 -- The g_old_rec is current therefore we must
201 -- set the returning function to true
202 --
203 l_fct_ret := true;
204 Else
205 --
206 -- Select the current row into g_old_rec
207 --
208 Open C_Sel1;
209 Fetch C_Sel1 Into g_old_rec;
210 If C_Sel1%notfound Then
211 Close C_Sel1;
212 --
213 -- The primary key is invalid therefore we must error
214 --
215 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
216 hr_utility.raise_error;
217 End If;
218 Close C_Sel1;
219 If (p_object_version_number <> g_old_rec.object_version_number) Then
220 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
221 hr_utility.raise_error;
222 End If;
223 hr_utility.set_location(l_proc, 15);
224 l_fct_ret := true;
225 End If;
226 End If;
227 hr_utility.set_location(' Leaving:'||l_proc, 20);
228 Return (l_fct_ret);
229 --
230 End api_updating;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |---------------------------------< lck >----------------------------------|
234 -- ----------------------------------------------------------------------------
235 Procedure lck
236 (
237 p_period_of_service_id in number,
238 p_object_version_number in number
239 ) is
240 --
241 -- Cursor selects the 'current' row from the HR Schema
242 --
243 Cursor C_Sel1 is
244 select
245 period_of_service_id,
246 business_group_id,
247 termination_accepted_person_id,
248 person_id,
249 date_start,
250 accepted_termination_date,
251 actual_termination_date,
252 comments,
253 final_process_date,
254 last_standard_process_date,
255 leaving_reason,
256 notified_termination_date,
257 projected_termination_date,
258 adjusted_svc_date,
259 request_id,
260 program_application_id,
261 program_id,
262 program_update_date,
263 attribute_category,
264 attribute1,
265 attribute2,
266 attribute3,
267 attribute4,
268 attribute5,
269 attribute6,
270 attribute7,
271 attribute8,
272 attribute9,
273 attribute10,
274 attribute11,
275 attribute12,
276 attribute13,
277 attribute14,
278 attribute15,
279 attribute16,
280 attribute17,
281 attribute18,
282 attribute19,
283 attribute20,
284 object_version_number,
285 prior_employment_ssp_weeks,
286 prior_employment_ssp_paid_to,
287 pds_information_category,
288 pds_information1,
289 pds_information2,
290 pds_information3,
291 pds_information4,
292 pds_information5,
293 pds_information6,
294 pds_information7,
295 pds_information8,
296 pds_information9,
297 pds_information10,
298 pds_information11,
299 pds_information12,
300 pds_information13,
301 pds_information14,
302 pds_information15,
303 pds_information16,
304 pds_information17,
305 pds_information18,
306 pds_information19,
307 pds_information20,
308 pds_information21,
309 pds_information22,
310 pds_information23,
311 pds_information24,
312 pds_information25,
313 pds_information26,
314 pds_information27,
315 pds_information28,
316 pds_information29,
317 pds_information30
318 from per_periods_of_service pds
319 where pds.period_of_service_id = p_period_of_service_id
320 for update nowait;
321 --
322 l_proc varchar2(72) := g_package||'lck';
323 --
324 Begin
325 hr_utility.set_location('Entering:'||l_proc, 1);
326 --
327 hr_api.mandatory_arg_error
328 (p_api_name => l_proc,
329 p_argument => 'period_of_service_id',
330 p_argument_value => p_period_of_service_id);
331 --
332 hr_utility.set_location(l_proc, 3);
333 --
334 hr_api.mandatory_arg_error
335 (p_api_name => l_proc,
336 p_argument => 'object_version_number',
337 p_argument_value => p_object_version_number);
338 --
339 hr_utility.set_location(l_proc, 7);
340 --
341 Open C_Sel1;
342 --
343 hr_utility.set_location(l_proc, 10);
344 --
345 Fetch C_Sel1 Into g_old_rec;
346 --
347 hr_utility.set_location(l_proc, 15);
348 --
349 If C_Sel1%notfound then
350 --
351 hr_utility.set_location(l_proc, 20);
352 --
353 Close C_Sel1;
354 --
355 -- The primary key is invalid therefore we must error
356 --
357 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
358 hr_utility.raise_error;
359 End If;
360 --
361 hr_utility.set_location(l_proc, 25);
362 --
363 Close C_Sel1;
364 --
365 If (p_object_version_number <> g_old_rec.object_version_number) Then
366 hr_utility.set_location(l_proc, 30);
367 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
368 hr_utility.raise_error;
369 End If;
370 --
371 hr_utility.set_location(' Leaving:'||l_proc, 35);
372 --
373 -- We need to trap the ORA LOCK exception
374 --
375 Exception
376 When HR_Api.Object_Locked then
377 --
378 -- The object is locked therefore we need to supply a meaningful
379 -- error message.
380 --
381 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
382 hr_utility.set_message_token('TABLE_NAME', 'per_periods_of_service');
383 hr_utility.raise_error;
384 End lck;
385 --
386 -- ----------------------------------------------------------------------------
387 -- |-----------------------------< convert_args >-----------------------------|
388 -- ----------------------------------------------------------------------------
389 Function convert_args
390 (
391 p_period_of_service_id in number,
392 p_business_group_id in number,
393 p_termination_accepted_person in number,
394 p_person_id in number,
395 p_date_start in date,
396 p_accepted_termination_date in date,
397 p_actual_termination_date in date,
398 p_comments in varchar2,
399 p_final_process_date in date,
400 p_last_standard_process_date in date,
401 p_leaving_reason in varchar2,
402 p_notified_termination_date in date,
403 p_projected_termination_date in date,
404 p_adjusted_svc_date in date,
405 p_request_id in number,
406 p_program_application_id in number,
407 p_program_id in number,
408 p_program_update_date in date,
409 p_attribute_category in varchar2,
410 p_attribute1 in varchar2,
411 p_attribute2 in varchar2,
412 p_attribute3 in varchar2,
413 p_attribute4 in varchar2,
414 p_attribute5 in varchar2,
415 p_attribute6 in varchar2,
416 p_attribute7 in varchar2,
417 p_attribute8 in varchar2,
418 p_attribute9 in varchar2,
419 p_attribute10 in varchar2,
420 p_attribute11 in varchar2,
421 p_attribute12 in varchar2,
422 p_attribute13 in varchar2,
423 p_attribute14 in varchar2,
424 p_attribute15 in varchar2,
425 p_attribute16 in varchar2,
426 p_attribute17 in varchar2,
427 p_attribute18 in varchar2,
428 p_attribute19 in varchar2,
429 p_attribute20 in varchar2,
430 p_object_version_number in number,
431 p_prior_employment_ssp_weeks in number,
432 p_prior_employment_ssp_paid_to in date,
433 p_pds_information_category in varchar2,
434 p_pds_information1 in varchar2,
435 p_pds_information2 in varchar2,
436 p_pds_information3 in varchar2,
437 p_pds_information4 in varchar2,
438 p_pds_information5 in varchar2,
439 p_pds_information6 in varchar2,
440 p_pds_information7 in varchar2,
441 p_pds_information8 in varchar2,
442 p_pds_information9 in varchar2,
443 p_pds_information10 in varchar2,
444 p_pds_information11 in varchar2,
445 p_pds_information12 in varchar2,
446 p_pds_information13 in varchar2,
447 p_pds_information14 in varchar2,
448 p_pds_information15 in varchar2,
449 p_pds_information16 in varchar2,
450 p_pds_information17 in varchar2,
451 p_pds_information18 in varchar2,
452 p_pds_information19 in varchar2,
453 p_pds_information20 in varchar2,
454 p_pds_information21 in varchar2,
455 p_pds_information22 in varchar2,
456 p_pds_information23 in varchar2,
457 p_pds_information24 in varchar2,
458 p_pds_information25 in varchar2,
459 p_pds_information26 in varchar2,
460 p_pds_information27 in varchar2,
461 p_pds_information28 in varchar2,
462 p_pds_information29 in varchar2,
463 p_pds_information30 in varchar2
464 )
465 Return g_rec_type is
466 --
467 l_rec g_rec_type;
468 l_proc varchar2(72) := g_package||'convert_args';
469 --
470 Begin
471 --
472 hr_utility.set_location('Entering:'||l_proc, 5);
473 --
474 -- Convert arguments into local l_rec structure.
475 --
476 l_rec.period_of_service_id := p_period_of_service_id;
477 l_rec.business_group_id := p_business_group_id;
478 l_rec.termination_accepted_person_id := p_termination_accepted_person;
479 l_rec.person_id := p_person_id;
480 l_rec.date_start := p_date_start;
481 l_rec.accepted_termination_date := p_accepted_termination_date;
482 l_rec.actual_termination_date := p_actual_termination_date;
483 l_rec.comments := p_comments;
484 l_rec.final_process_date := p_final_process_date;
485 l_rec.last_standard_process_date := p_last_standard_process_date;
486 l_rec.leaving_reason := p_leaving_reason;
490 l_rec.request_id := p_request_id;
487 l_rec.notified_termination_date := p_notified_termination_date;
488 l_rec.projected_termination_date := p_projected_termination_date;
489 l_rec.adjusted_svc_date := p_adjusted_svc_date;
491 l_rec.program_application_id := p_program_application_id;
492 l_rec.program_id := p_program_id;
493 l_rec.program_update_date := p_program_update_date;
494 l_rec.attribute_category := p_attribute_category;
495 l_rec.attribute1 := p_attribute1;
496 l_rec.attribute2 := p_attribute2;
497 l_rec.attribute3 := p_attribute3;
498 l_rec.attribute4 := p_attribute4;
499 l_rec.attribute5 := p_attribute5;
500 l_rec.attribute6 := p_attribute6;
501 l_rec.attribute7 := p_attribute7;
502 l_rec.attribute8 := p_attribute8;
503 l_rec.attribute9 := p_attribute9;
504 l_rec.attribute10 := p_attribute10;
505 l_rec.attribute11 := p_attribute11;
506 l_rec.attribute12 := p_attribute12;
507 l_rec.attribute13 := p_attribute13;
508 l_rec.attribute14 := p_attribute14;
509 l_rec.attribute15 := p_attribute15;
510 l_rec.attribute16 := p_attribute16;
511 l_rec.attribute17 := p_attribute17;
512 l_rec.attribute18 := p_attribute18;
513 l_rec.attribute19 := p_attribute19;
514 l_rec.attribute20 := p_attribute20;
515 l_rec.object_version_number := p_object_version_number;
516 l_rec.prior_employment_ssp_weeks := p_prior_employment_ssp_weeks;
517 l_rec.prior_employment_ssp_paid_to := p_prior_employment_ssp_paid_to;
518 l_rec.pds_information_category := p_pds_information_category;
519 l_rec.pds_information1 := p_pds_information1;
520 l_rec.pds_information2 := p_pds_information2;
521 l_rec.pds_information3 := p_pds_information3;
522 l_rec.pds_information4 := p_pds_information4;
523 l_rec.pds_information5 := p_pds_information5;
524 l_rec.pds_information6 := p_pds_information6;
525 l_rec.pds_information7 := p_pds_information7;
526 l_rec.pds_information8 := p_pds_information8;
527 l_rec.pds_information9 := p_pds_information9;
528 l_rec.pds_information10 := p_pds_information10;
529 l_rec.pds_information11 := p_pds_information11;
530 l_rec.pds_information12 := p_pds_information12;
531 l_rec.pds_information13 := p_pds_information13;
532 l_rec.pds_information14 := p_pds_information14;
533 l_rec.pds_information15 := p_pds_information15;
534 l_rec.pds_information16 := p_pds_information16;
535 l_rec.pds_information17 := p_pds_information17;
536 l_rec.pds_information18 := p_pds_information18;
537 l_rec.pds_information19 := p_pds_information19;
538 l_rec.pds_information20 := p_pds_information20;
539 l_rec.pds_information21 := p_pds_information21;
540 l_rec.pds_information22 := p_pds_information22;
541 l_rec.pds_information23 := p_pds_information23;
542 l_rec.pds_information24 := p_pds_information24;
543 l_rec.pds_information25 := p_pds_information25;
544 l_rec.pds_information26 := p_pds_information26;
545 l_rec.pds_information27 := p_pds_information27;
546 l_rec.pds_information28 := p_pds_information28;
547 l_rec.pds_information29 := p_pds_information29;
548 l_rec.pds_information30 := p_pds_information30;
549 --
550 -- Return the plsql record structure.
551 --
552 hr_utility.set_location(' Leaving:'||l_proc, 10);
553 Return(l_rec);
554 --
555 End convert_args;
556 --
557 end per_pds_shd;