[Home] [Help]
PACKAGE BODY: APPS.IRC_IID_SHD
Source
1 Package Body irc_iid_shd as
2 /* $Header: iriidrhi.pkb 120.3.12010000.2 2008/11/06 13:49:47 mkjayara ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_iid_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_INTERVIEW_DETAILS_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_INTERVIEW_DETAILS_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_interview_details_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 interview_details_id
56 ,status
57 ,feedback
58 ,notes
59 ,notes_to_candidate
60 ,category
61 ,result
62 ,iid_information_category
63 ,iid_information1
64 ,iid_information2
65 ,iid_information3
66 ,iid_information4
67 ,iid_information5
68 ,iid_information6
69 ,iid_information7
70 ,iid_information8
71 ,iid_information9
72 ,iid_information10
73 ,iid_information11
74 ,iid_information12
75 ,iid_information13
76 ,iid_information14
77 ,iid_information15
78 ,iid_information16
79 ,iid_information17
80 ,iid_information18
81 ,iid_information19
82 ,iid_information20
83 ,start_date
84 ,end_date
85 ,event_id
86 ,object_version_number
87 ,created_by
88 from irc_interview_details
89 where interview_details_id = p_interview_details_id;
90 --
91 l_fct_ret boolean;
92 --
93 Begin
94 --
95 If (p_interview_details_id is null and
96 p_object_version_number is null
97 ) Then
98 --
99 -- One of the primary key arguments is null therefore we must
100 -- set the returning function value to false
101 --
102 l_fct_ret := false;
103 Else
104 If (p_interview_details_id
105 = irc_iid_shd.g_old_rec.interview_details_id and
106 p_object_version_number
107 = irc_iid_shd.g_old_rec.object_version_number
108 ) Then
109 --
110 -- The g_old_rec is current therefore we must
111 -- set the returning function to true
112 --
113 l_fct_ret := true;
114 Else
115 --
116 -- Select the current row into g_old_rec
117 --
118 Open C_Sel1;
119 Fetch C_Sel1 Into irc_iid_shd.g_old_rec;
120 If C_Sel1%notfound Then
121 Close C_Sel1;
122 --
123 -- The primary key is invalid therefore we must error
124 --
125 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
126 fnd_message.raise_error;
127 End If;
128 Close C_Sel1;
129 If (p_object_version_number
130 <> irc_iid_shd.g_old_rec.object_version_number) Then
131 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
132 fnd_message.raise_error;
133 End If;
134 l_fct_ret := true;
135 End If;
136 End If;
137 Return (l_fct_ret);
138 --
139 End api_updating;
140 --
141 -- ----------------------------------------------------------------------------
142 -- |----------------------------< upd_end_date >------------------------------|
143 -- ----------------------------------------------------------------------------
144 Procedure upd_end_date
145 (p_effective_date in date
146 ,p_interview_details_id in number
147 ,p_new_end_date in date
148 ,p_object_version_number out nocopy number
149 ) is
150 --
151 l_proc varchar2(72) := g_package||'upd_end_date';
152 l_object_version_number number;
153 --
154 Begin
155 hr_utility.set_location('Entering:'||l_proc, 5);
156 --
157 -- Because we are updating a row we must get the next object
158 -- version number.
159 --
160 l_object_version_number :=
161 irc_iid_shd.get_object_version_number
162 (p_interview_details_id => p_interview_details_id
163 );
164 --
165 hr_utility.set_location(l_proc, 10);
166 --
167 --
168 -- Update the specified datetrack row setting the effective
169 -- end date to the specified new effective end date.
170 --
171 update irc_interview_details t
172 set t.end_date = p_new_end_date
173 , t.object_version_number = l_object_version_number
174 where t.interview_details_id = p_interview_details_id
175 and p_effective_date
176 between t.start_date and t.end_date;
177 --
178 --
179 p_object_version_number := l_object_version_number;
180 hr_utility.set_location(' Leaving:'||l_proc, 15);
181 --
182 End upd_end_date;
183 --
184 -- ----------------------------------------------------------------------------
185 -- |---------------------------------< lck >----------------------------------|
186 -- ----------------------------------------------------------------------------
187 Procedure lck
188 (p_effective_date in date
189 ,p_datetrack_mode in varchar2
190 ,p_interview_details_id in number
191 ,p_object_version_number in number
192 ,p_validation_start_date out nocopy date
193 ,p_validation_end_date out nocopy date
194 ) is
195 --
196 l_proc varchar2(72) := g_package||'lck';
197 l_validation_start_date date;
198 l_validation_end_date date;
199 l_argument varchar2(30);
200 --
201 -- Cursor C_Sel1 selects the current locked row as of session date
202 -- ensuring that the object version numbers match.
203 --
204 Cursor C_Sel1 is
205 select
206 interview_details_id
207 ,status
208 ,feedback
209 ,notes
210 ,notes_to_candidate
211 ,category
212 ,result
213 ,iid_information_category
214 ,iid_information1
215 ,iid_information2
216 ,iid_information3
217 ,iid_information4
218 ,iid_information5
219 ,iid_information6
220 ,iid_information7
221 ,iid_information8
222 ,iid_information9
223 ,iid_information10
224 ,iid_information11
225 ,iid_information12
226 ,iid_information13
227 ,iid_information14
228 ,iid_information15
229 ,iid_information16
230 ,iid_information17
231 ,iid_information18
232 ,iid_information19
233 ,iid_information20
234 ,start_date
235 ,end_date
236 ,event_id
237 ,object_version_number
238 ,created_by
239 from irc_interview_details
240 where interview_details_id = p_interview_details_id
241 and sysdate between start_date and end_date
242 for update nowait;
243 --
244 --
245 --
246 Begin
247 hr_utility.set_location('Entering:'||l_proc, 5);
248 --
249 -- Ensure that all the mandatory arguments are not null
250 --
251 hr_api.mandatory_arg_error(p_api_name => l_proc
252 ,p_argument => 'effective_date'
253 ,p_argument_value => p_effective_date
254 );
255 --
256 hr_api.mandatory_arg_error(p_api_name => l_proc
257 ,p_argument => 'datetrack_mode'
258 ,p_argument_value => p_datetrack_mode
259 );
260 --
261 hr_api.mandatory_arg_error(p_api_name => l_proc
262 ,p_argument => 'interview_details_id'
263 ,p_argument_value => p_interview_details_id
264 );
265 --
266 hr_api.mandatory_arg_error(p_api_name => l_proc
267 ,p_argument => 'object_version_number'
268 ,p_argument_value => p_object_version_number
269 );
270 --
271 -- Check to ensure the datetrack mode is not INSERT.
272 --
273 If (p_datetrack_mode <> hr_api.g_insert) then
274 --
275 -- We must select and lock the current row.
276 --
277 Open C_Sel1;
278 Fetch C_Sel1 Into irc_iid_shd.g_old_rec;
279 If C_Sel1%notfound then
280 Close C_Sel1;
281 --
282 -- The primary key is invalid therefore we must error
283 --
284 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
285 fnd_message.raise_error;
286 End If;
287 Close C_Sel1;
288 If (p_object_version_number
289 <> irc_iid_shd.g_old_rec.object_version_number) Then
290 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
291 fnd_message.raise_error;
292 End If;
293 --
294 --
295 --
296 Else
297 --
298 -- We are doing a datetrack 'INSERT' which is illegal within this
299 -- procedure therefore we must error (note: to lck on insert the
300 -- private procedure ins_lck should be called).
301 --
302 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
303 fnd_message.set_token('PROCEDURE', l_proc);
304 fnd_message.set_token('STEP','20');
305 fnd_message.raise_error;
306 End If;
307 --
308 -- Set the validation start and end date OUT arguments
309 --
310 if (p_datetrack_mode = hr_api.g_update) then
311 p_validation_start_date := p_effective_date;
312 p_validation_end_date := irc_iid_shd.g_old_rec.end_date;
313 elsif (p_datetrack_mode = hr_api.g_delete) then
314 p_validation_start_date := p_effective_date;
315 p_validation_end_date := p_effective_date;
316 end if;
317 --
318 hr_utility.set_location(' Leaving:'||l_proc, 30);
319 --
320 -- We need to trap the ORA LOCK exception
321 --
322 Exception
323 When HR_Api.Object_Locked then
324 --
325 -- The object is locked therefore we need to supply a meaningful
326 -- error message.
327 --
328 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
329 fnd_message.set_token('TABLE_NAME', 'irc_interview_details');
330 fnd_message.raise_error;
331 End lck;
332 --
333 -- ----------------------------------------------------------------------------
334 -- |-----------------------------< convert_args >-----------------------------|
335 -- ----------------------------------------------------------------------------
336 Function convert_args
337 (p_interview_details_id in number
338 ,p_status in varchar2
339 ,p_feedback in varchar2
340 ,p_notes in varchar2
341 ,p_notes_to_candidate in varchar2
342 ,p_category in varchar2
343 ,p_result in varchar2
344 ,iid_information_category in varchar2
345 ,iid_information1 in varchar2
346 ,iid_information2 in varchar2
347 ,iid_information3 in varchar2
348 ,iid_information4 in varchar2
349 ,iid_information5 in varchar2
350 ,iid_information6 in varchar2
351 ,iid_information7 in varchar2
352 ,iid_information8 in varchar2
353 ,iid_information9 in varchar2
354 ,iid_information10 in varchar2
355 ,iid_information11 in varchar2
356 ,iid_information12 in varchar2
357 ,iid_information13 in varchar2
358 ,iid_information14 in varchar2
359 ,iid_information15 in varchar2
360 ,iid_information16 in varchar2
361 ,iid_information17 in varchar2
362 ,iid_information18 in varchar2
363 ,iid_information19 in varchar2
364 ,iid_information20 in varchar2
365 ,p_start_date in date
366 ,p_end_date in date
367 ,p_event_id in number
368 ,p_object_version_number in number
369 )
370 Return g_rec_type is
371 --
372 l_rec g_rec_type;
373 --
374 Begin
375 --
376 -- Convert arguments into local l_rec structure.
377 --
378 l_rec.interview_details_id := p_interview_details_id;
379 l_rec.status := p_status;
380 l_rec.feedback := p_feedback;
381 l_rec.notes := p_notes;
382 l_rec.notes_to_candidate := p_notes_to_candidate;
383 l_rec.category := p_category;
384 l_rec.result := p_result;
385 l_rec.iid_information_category := l_rec.iid_information_category;
386 l_rec.iid_information1 := l_rec.iid_information1;
387 l_rec.iid_information2 := l_rec.iid_information2;
388 l_rec.iid_information3 := l_rec.iid_information3;
389 l_rec.iid_information4 := l_rec.iid_information4;
390 l_rec.iid_information5 := l_rec.iid_information5;
391 l_rec.iid_information6 := l_rec.iid_information6;
392 l_rec.iid_information7 := l_rec.iid_information7;
393 l_rec.iid_information8 := l_rec.iid_information8;
394 l_rec.iid_information9 := l_rec.iid_information9;
395 l_rec.iid_information10 := l_rec.iid_information10;
396 l_rec.iid_information11 := l_rec.iid_information11;
397 l_rec.iid_information12 := l_rec.iid_information12;
398 l_rec.iid_information13 := l_rec.iid_information13;
399 l_rec.iid_information14 := l_rec.iid_information14;
400 l_rec.iid_information15 := l_rec.iid_information15;
401 l_rec.iid_information16 := l_rec.iid_information16;
402 l_rec.iid_information17 := l_rec.iid_information17;
403 l_rec.iid_information18 := l_rec.iid_information18;
404 l_rec.iid_information19 := l_rec.iid_information19;
405 l_rec.iid_information20 := l_rec.iid_information20;
406 l_rec.start_date := p_start_date;
407 l_rec.end_date := p_end_date;
408 l_rec.event_id := p_event_id;
409 l_rec.object_version_number := p_object_version_number;
410 --
411 -- Return the plsql record structure.
412 --
413 Return(l_rec);
414 --
415 End convert_args;
416 --
417 -- ----------------------------------------------------------------------------
418 -- |------------------------< get_object_version_number >----------------------|
419 -- ----------------------------------------------------------------------------
420 Function get_object_version_number
421 (p_interview_details_id in number
422 )
423 Return number is
424 --
425 l_ovn number;
426 --
427 Begin
428 --
429 -- get the next ovn
430 --
431 select nvl(max(t.object_version_number),0) + 1
432 into l_ovn
433 from irc_interview_details t
434 where t.interview_details_id = p_interview_details_id;
435 --
436 -- Return the new object_version_number.
437 --
438 Return(l_ovn);
439 --
440 End get_object_version_number;
441 --
442 end irc_iid_shd;