[Home] [Help]
PACKAGE BODY: APPS.IRC_IRF_SHD
Source
1 Package Body irc_irf_shd as
2 /* $Header: irirfrhi.pkb 120.1 2008/04/16 07:34:32 vmummidi noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_irf_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE
15 ) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 --
21 If (p_constraint_name = 'IRC_REFERRAL_INFO_FK1') Then
22 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23 fnd_message.set_token('PROCEDURE', l_proc);
24 fnd_message.set_token('STEP','5');
25 fnd_message.raise_error;
26 ElsIf (p_constraint_name = 'IRC_REFERRAL_INFO_PK') Then
27 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
28 fnd_message.set_token('PROCEDURE', l_proc);
29 fnd_message.set_token('STEP','10');
30 fnd_message.raise_error;
31 Else
32 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
33 fnd_message.set_token('PROCEDURE', l_proc);
34 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
35 fnd_message.raise_error;
36 End If;
37 --
38 End constraint_error;
39 --
40 -- ----------------------------------------------------------------------------
41 -- |-----------------------------< api_updating >-----------------------------|
42 -- ----------------------------------------------------------------------------
43 Function api_updating
44 (p_effective_date in date
45 ,p_referral_info_id in number
46 ,p_object_version_number in number
47 )
48 Return Boolean Is
49 --
50 --
51 -- Cursor selects the 'current' row from the HR Schema
52 --
53 Cursor C_Sel1 is
54 select
55 referral_info_id
56 ,object_id
57 ,object_type
58 ,start_date
59 ,end_date
60 ,source_type
61 ,source_name
62 ,source_criteria1
63 ,source_value1
64 ,source_criteria2
65 ,source_value2
66 ,source_criteria3
67 ,source_value3
68 ,source_criteria4
69 ,source_value4
70 ,source_criteria5
71 ,source_value5
72 ,source_person_id
73 ,candidate_comment
74 ,employee_comment
75 ,irf_attribute_category
76 ,irf_attribute1
77 ,irf_attribute2
78 ,irf_attribute3
79 ,irf_attribute4
80 ,irf_attribute5
81 ,irf_attribute6
82 ,irf_attribute7
83 ,irf_attribute8
84 ,irf_attribute9
85 ,irf_attribute10
86 ,irf_information_category
87 ,irf_information1
88 ,irf_information2
89 ,irf_information3
90 ,irf_information4
91 ,irf_information5
92 ,irf_information6
93 ,irf_information7
94 ,irf_information8
95 ,irf_information9
96 ,irf_information10
97 ,object_created_by
98 ,created_by
99 ,object_version_number
100 from irc_referral_info
101 where referral_info_id = p_referral_info_id;
102 --
103 l_fct_ret boolean;
104 --
105 Begin
106 --
107 If (p_referral_info_id is null and
108 p_object_version_number is null
109 ) Then
110 --
111 -- One of the primary key arguments is null therefore we must
112 -- set the returning function value to false
113 --
114 l_fct_ret := false;
115 Else
116 If (p_referral_info_id
117 = irc_irf_shd.g_old_rec.referral_info_id and
118 p_object_version_number
119 = irc_irf_shd.g_old_rec.object_version_number
120 ) Then
121 --
122 -- The g_old_rec is current therefore we must
123 -- set the returning function to true
124 --
125 l_fct_ret := true;
126 Else
127 --
128 -- Select the current row into g_old_rec
129 --
130 Open C_Sel1;
131 Fetch C_Sel1 Into irc_irf_shd.g_old_rec;
132 If C_Sel1%notfound Then
133 Close C_Sel1;
134 --
135 -- The primary key is invalid therefore we must error
136 --
137 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 End If;
140 Close C_Sel1;
141 If (p_object_version_number
142 <> irc_irf_shd.g_old_rec.object_version_number) Then
143 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
144 fnd_message.raise_error;
145 End If;
146 l_fct_ret := true;
147 End If;
148 End If;
149 Return (l_fct_ret);
150 --
151 End api_updating;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |----------------------------< upd_end_date >------------------------------|
155 -- ----------------------------------------------------------------------------
156 Procedure upd_end_date
157 (p_effective_date in date
158 ,p_referral_info_id in number
159 ,p_new_end_date in date
160 ,p_object_version_number out nocopy number
161 ) is
162 --
163 l_proc varchar2(72) := g_package||'upd_end_date';
164 l_object_version_number number;
165 --
166 Begin
167 hr_utility.set_location('Entering:'||l_proc, 5);
168 --
169 -- Because we are updating a row we must get the next object
170 -- version number.
171 --
172 l_object_version_number :=
173 irc_irf_shd.get_object_version_number
174 (p_referral_info_id => p_referral_info_id
175 );
176 --
177 hr_utility.set_location(l_proc, 10);
178 --
179 --
180 -- Update the specified datetrack row setting the effective
181 -- end date to the specified new effective end date.
182 --
183 update irc_referral_info t
184 set t.end_date = p_new_end_date
185 , t.object_version_number = l_object_version_number
186 where t.referral_info_id = p_referral_info_id
187 and p_effective_date
188 between t.start_date and t.end_date;
189 --
190 --
191 p_object_version_number := l_object_version_number;
192 hr_utility.set_location(' Leaving:'||l_proc, 15);
193 --
194 End upd_end_date;
195 --
196 -- ----------------------------------------------------------------------------
197 -- |---------------------------------< lck >----------------------------------|
198 -- ----------------------------------------------------------------------------
199 Procedure lck
200 (p_effective_date in date
201 ,p_datetrack_mode in varchar2
202 ,p_referral_info_id in number
203 ,p_object_version_number in number
204 ,p_validation_start_date out nocopy date
205 ,p_validation_end_date out nocopy date
206 ) is
207 --
208 l_proc varchar2(72) := g_package||'lck';
209 l_validation_start_date date;
210 l_validation_end_date date;
211 l_argument varchar2(30);
212 --
213 -- Cursor C_Sel1 selects the current locked row as of session date
214 -- ensuring that the object version numbers match.
215 --
216 Cursor C_Sel1 is
217 select
218 referral_info_id
219 ,object_id
220 ,object_type
221 ,start_date
222 ,end_date
223 ,source_type
224 ,source_name
225 ,source_criteria1
226 ,source_value1
227 ,source_criteria2
228 ,source_value2
229 ,source_criteria3
230 ,source_value3
231 ,source_criteria4
232 ,source_value4
233 ,source_criteria5
234 ,source_value5
235 ,source_person_id
236 ,candidate_comment
237 ,employee_comment
238 ,irf_attribute_category
239 ,irf_attribute1
240 ,irf_attribute2
241 ,irf_attribute3
242 ,irf_attribute4
243 ,irf_attribute5
244 ,irf_attribute6
245 ,irf_attribute7
246 ,irf_attribute8
247 ,irf_attribute9
248 ,irf_attribute10
249 ,irf_information_category
250 ,irf_information1
251 ,irf_information2
252 ,irf_information3
253 ,irf_information4
254 ,irf_information5
255 ,irf_information6
256 ,irf_information7
257 ,irf_information8
258 ,irf_information9
259 ,irf_information10
260 ,object_created_by
261 ,created_by
262 ,object_version_number
263 from irc_referral_info
264 where referral_info_id = p_referral_info_id
265 and sysdate between start_date and end_date
266 for update nowait;
267 --
268 --
269 --
270 Begin
271 hr_utility.set_location('Entering:'||l_proc, 5);
272 --
273 -- Ensure that all the mandatory arguments are not null
274 --
275 hr_api.mandatory_arg_error(p_api_name => l_proc
276 ,p_argument => 'effective_date'
277 ,p_argument_value => p_effective_date
278 );
279 --
280 hr_api.mandatory_arg_error(p_api_name => l_proc
281 ,p_argument => 'datetrack_mode'
282 ,p_argument_value => p_datetrack_mode
283 );
284 --
285 hr_api.mandatory_arg_error(p_api_name => l_proc
286 ,p_argument => 'referral_info_id'
287 ,p_argument_value => p_referral_info_id
288 );
289 --
290 hr_api.mandatory_arg_error(p_api_name => l_proc
291 ,p_argument => 'object_version_number'
292 ,p_argument_value => p_object_version_number
293 );
294 --
295 -- Check to ensure the datetrack mode is not INSERT.
296 --
297 If (p_datetrack_mode <> hr_api.g_insert) then
298 --
299 -- We must select and lock the current row.
300 --
301 Open C_Sel1;
302 Fetch C_Sel1 Into irc_irf_shd.g_old_rec;
303 If C_Sel1%notfound then
304 Close C_Sel1;
305 --
306 -- The primary key is invalid therefore we must error
307 --
308 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
309 fnd_message.raise_error;
310 End If;
311 Close C_Sel1;
312 If (p_object_version_number
313 <> irc_irf_shd.g_old_rec.object_version_number) Then
314 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
315 fnd_message.raise_error;
316 End If;
317 --
318 --
319 --
320 Else
321 --
322 -- We are doing a datetrack 'INSERT' which is illegal within this
323 -- procedure therefore we must error (note: to lck on insert the
324 -- private procedure ins_lck should be called).
325 --
326 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
327 fnd_message.set_token('PROCEDURE', l_proc);
328 fnd_message.set_token('STEP','20');
329 fnd_message.raise_error;
330 End If;
331 --
332 -- Set the validation start and end date OUT arguments
333 --
334 if (p_datetrack_mode = hr_api.g_update) then
335 p_validation_start_date := p_effective_date;
336 p_validation_end_date := irc_irf_shd.g_old_rec.end_date;
337 elsif (p_datetrack_mode = hr_api.g_delete) then
338 p_validation_start_date := p_effective_date;
339 p_validation_end_date := p_effective_date;
340 end if;
341 --
342 hr_utility.set_location(' Leaving:'||l_proc, 30);
343 --
344 -- We need to trap the ORA LOCK exception
345 --
346 Exception
347 When HR_Api.Object_Locked then
348 --
349 -- The object is locked therefore we need to supply a meaningful
350 -- error message.
351 --
352 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
353 fnd_message.set_token('TABLE_NAME', 'irc_referral_info');
354 fnd_message.raise_error;
355 End lck;
356 --
357 -- ----------------------------------------------------------------------------
358 -- |-----------------------------< convert_args >-----------------------------|
359 -- ----------------------------------------------------------------------------
360 Function convert_args
361 (p_referral_info_id in number
362 ,p_object_id in number
363 ,p_object_type in varchar2
364 ,p_start_date in date
365 ,p_end_date in date
366 ,p_source_type in varchar2
367 ,p_source_name in varchar2
368 ,p_source_criteria1 in varchar2
369 ,p_source_value1 in varchar2
370 ,p_source_criteria2 in varchar2
371 ,p_source_value2 in varchar2
372 ,p_source_criteria3 in varchar2
373 ,p_source_value3 in varchar2
374 ,p_source_criteria4 in varchar2
375 ,p_source_value4 in varchar2
376 ,p_source_criteria5 in varchar2
377 ,p_source_value5 in varchar2
378 ,p_source_person_id in number
379 ,p_candidate_comment in varchar2
380 ,p_employee_comment in varchar2
381 ,p_irf_attribute_category in varchar2
382 ,p_irf_attribute1 in varchar2
383 ,p_irf_attribute2 in varchar2
384 ,p_irf_attribute3 in varchar2
385 ,p_irf_attribute4 in varchar2
386 ,p_irf_attribute5 in varchar2
387 ,p_irf_attribute6 in varchar2
388 ,p_irf_attribute7 in varchar2
389 ,p_irf_attribute8 in varchar2
390 ,p_irf_attribute9 in varchar2
391 ,p_irf_attribute10 in varchar2
392 ,p_irf_information_category in varchar2
393 ,p_irf_information1 in varchar2
394 ,p_irf_information2 in varchar2
395 ,p_irf_information3 in varchar2
396 ,p_irf_information4 in varchar2
397 ,p_irf_information5 in varchar2
398 ,p_irf_information6 in varchar2
399 ,p_irf_information7 in varchar2
400 ,p_irf_information8 in varchar2
401 ,p_irf_information9 in varchar2
402 ,p_irf_information10 in varchar2
403 ,p_object_created_by in varchar2
404 ,p_object_version_number in number
405 )
406 Return g_rec_type is
407 --
408 l_rec g_rec_type;
409 --
410 Begin
411 --
412 -- Convert arguments into local l_rec structure.
413 --
414 l_rec.referral_info_id := p_referral_info_id;
415 l_rec.object_id := p_object_id;
416 l_rec.object_type := p_object_type;
417 l_rec.start_date := p_start_date;
418 l_rec.end_date := p_end_date;
419 l_rec.source_type := p_source_type;
420 l_rec.source_name := p_source_name;
421 l_rec.source_criteria1 := p_source_criteria1;
422 l_rec.source_value1 := p_source_value1;
423 l_rec.source_criteria2 := p_source_criteria2;
424 l_rec.source_value2 := p_source_value2;
425 l_rec.source_criteria3 := p_source_criteria3;
426 l_rec.source_value3 := p_source_value3;
427 l_rec.source_criteria4 := p_source_criteria4;
428 l_rec.source_value4 := p_source_value4;
429 l_rec.source_criteria5 := p_source_criteria5;
430 l_rec.source_value5 := p_source_value5;
431 l_rec.source_person_id := p_source_person_id;
432 l_rec.candidate_comment := p_candidate_comment;
433 l_rec.employee_comment := p_employee_comment;
434 l_rec.irf_attribute_category := p_irf_attribute_category;
435 l_rec.irf_attribute1 := p_irf_attribute1;
436 l_rec.irf_attribute2 := p_irf_attribute2;
437 l_rec.irf_attribute3 := p_irf_attribute3;
438 l_rec.irf_attribute4 := p_irf_attribute4;
439 l_rec.irf_attribute5 := p_irf_attribute5;
440 l_rec.irf_attribute6 := p_irf_attribute6;
441 l_rec.irf_attribute7 := p_irf_attribute7;
442 l_rec.irf_attribute8 := p_irf_attribute8;
443 l_rec.irf_attribute9 := p_irf_attribute9;
444 l_rec.irf_attribute10 := p_irf_attribute10;
445 l_rec.irf_information_category := p_irf_information_category;
446 l_rec.irf_information1 := p_irf_information1;
447 l_rec.irf_information2 := p_irf_information2;
448 l_rec.irf_information3 := p_irf_information3;
449 l_rec.irf_information4 := p_irf_information4;
450 l_rec.irf_information5 := p_irf_information5;
451 l_rec.irf_information6 := p_irf_information6;
452 l_rec.irf_information7 := p_irf_information7;
453 l_rec.irf_information8 := p_irf_information8;
454 l_rec.irf_information9 := p_irf_information9;
455 l_rec.irf_information10 := p_irf_information10;
456 l_rec.object_created_by := p_object_created_by;
457 l_rec.object_version_number := p_object_version_number;
458 --
459 -- Return the plsql record structure.
460 --
461 Return(l_rec);
462 --
463 End convert_args;
464 --
465 -- ----------------------------------------------------------------------------
466 -- |------------------------< get_object_version_number >----------------------|
467 -- ----------------------------------------------------------------------------
468 Function get_object_version_number
469 (p_referral_info_id in number
470 )
471 Return number is
472 --
473 l_ovn number;
474 --
475 Begin
476 --
477 -- get the next ovn
478 --
479 select nvl(max(t.object_version_number),0) + 1
480 into l_ovn
481 from irc_referral_info t
482 where t.referral_info_id = p_referral_info_id;
483 --
484 -- Return the new object_version_number.
485 --
486 Return(l_ovn);
487 --
488 End get_object_version_number;
489 --
490 end irc_irf_shd;