[Home] [Help]
PACKAGE BODY: APPS.IRC_IAD_SHD
Source
1 Package Body irc_iad_shd as
2 /* $Header: iriadrhi.pkb 120.5.12010000.2 2010/01/11 10:41:25 uuddavol ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_iad_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_ASSIGNMENT_DETAILS_F_PK') 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_ASSIGNMENT_DETAILS_F_U1') 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_assignment_details_id in number
46 ,p_object_version_number in number
47 ) Return Boolean Is
48 --
49 -- Cursor selects the 'current' row from the HR Schema
50 --
51 Cursor C_Sel1 is
52 select
53 assignment_details_id
54 ,assignment_id
55 ,effective_start_date
56 ,effective_end_date
57 ,details_version
58 ,latest_details
59 ,attempt_id
60 ,qualified
61 ,considered
62 ,object_version_number
63 from irc_assignment_details_f
64 where assignment_details_id = p_assignment_details_id;
65 --
66 l_fct_ret boolean;
67 --
68 Begin
69 --
70 If (p_assignment_details_id is null or
71 p_object_version_number is null) Then
72 --
73 -- One of the primary key arguments is null therefore we must
74 -- set the returning function value to false
75 --
76 l_fct_ret := false;
77 Else
78 If (p_assignment_details_id =
79 irc_iad_shd.g_old_rec.assignment_details_id and
80 p_object_version_number =
81 irc_iad_shd.g_old_rec.object_version_number
82 ) Then
83 --
84 -- The g_old_rec is current therefore we must
85 -- set the returning function to true
86 --
87 l_fct_ret := true;
88 Else
89 --
90 -- Select the current row
91 --
92 Open C_Sel1;
93 Fetch C_Sel1 Into irc_iad_shd.g_old_rec;
94 If C_Sel1%notfound Then
95 Close C_Sel1;
96 --
97 -- The primary key is invalid therefore we must error
98 --
99 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
100 fnd_message.raise_error;
101 End If;
102 Close C_Sel1;
103 If (p_object_version_number
104 <> irc_iad_shd.g_old_rec.object_version_number) Then
105 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
106 fnd_message.raise_error;
107 End If;
108 l_fct_ret := true;
109 End If;
110 End If;
111 Return (l_fct_ret);
112 --
113 End api_updating;
114 --
115 -- ----------------------------------------------------------------------------
116 -- |---------------------------< find_dt_upd_modes >--------------------------|
117 -- ----------------------------------------------------------------------------
118 Procedure find_dt_upd_modes
119 (p_effective_date in date
120 ,p_base_key_value in number
121 ,p_correction out nocopy boolean
122 ,p_update out nocopy boolean
123 ,p_update_override out nocopy boolean
124 ,p_update_change_insert out nocopy boolean
125 ) is
126 --
127 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
128 --
129 Begin
130 hr_utility.set_location('Entering:'||l_proc, 5);
131 --
132 -- Call the corresponding datetrack api
133 --
134 dt_api.find_dt_upd_modes
135 (p_effective_date => p_effective_date
136 ,p_base_table_name => 'irc_assignment_details_f'
137 ,p_base_key_column => 'assignment_id'
138 ,p_base_key_value => p_base_key_value
139 ,p_correction => p_correction
140 ,p_update => p_update
141 ,p_update_override => p_update_override
142 ,p_update_change_insert => p_update_change_insert
143 );
144 --
145 hr_utility.set_location(' Leaving:'||l_proc, 10);
146 End find_dt_upd_modes;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |---------------------------< find_dt_del_modes >--------------------------|
150 -- ----------------------------------------------------------------------------
151 Procedure find_dt_del_modes
152 (p_effective_date in date
153 ,p_base_key_value in number
154 ,p_zap out nocopy boolean
155 ,p_delete out nocopy boolean
156 ,p_future_change out nocopy boolean
157 ,p_delete_next_change out nocopy boolean
158 ) is
159 --
160 l_proc varchar2(72) := g_package||'find_dt_del_modes';
161 --
162 l_parent_key_value1 number;
163 --
164 Begin
165 hr_utility.set_location('Entering:'||l_proc, 5);
166 --
167 -- Base key value is also the parent key value in this case
168 --
169 l_parent_key_value1 := p_base_key_value;
170 --
171 -- Call the corresponding datetrack api
172 --
173 dt_api.find_dt_del_modes
174 (p_effective_date => p_effective_date
175 ,p_base_table_name => 'irc_assignment_details_f'
176 ,p_base_key_column => 'assignment_id'
177 ,p_base_key_value => p_base_key_value
178 ,p_parent_table_name1 => 'per_all_assignments_f'
179 ,p_parent_key_column1 => 'assignment_id'
180 ,p_parent_key_value1 => l_parent_key_value1
181 ,p_zap => p_zap
182 ,p_delete => p_delete
183 ,p_future_change => p_future_change
184 ,p_delete_next_change => p_delete_next_change
185 );
186 --
187 hr_utility.set_location(' Leaving:'||l_proc, 10);
188 End find_dt_del_modes;
189 --
190 -- ----------------------------------------------------------------------------
191 -- |-----------------------< upd_effective_end_date >-------------------------|
192 -- ----------------------------------------------------------------------------
193 Procedure upd_effective_end_date
194 (p_effective_date in date
195 ,p_base_key_value in number
196 ,p_new_effective_end_date in date
197 ,p_validation_start_date in date
198 ,p_validation_end_date in date
199 ,p_object_version_number out nocopy number
200 ) is
201 --
202 l_proc varchar2(72) := g_package||'upd_effective_end_date';
203 l_object_version_number number;
204 --
205 Begin
206 hr_utility.set_location('Entering:'||l_proc, 5);
207 --
208 -- Because we are updating a row we must get the next object
209 -- version number.
210 --
211 l_object_version_number :=
212 dt_api.get_object_version_number
213 (p_base_table_name => 'irc_assignment_details_f'
214 ,p_base_key_column => 'assignment_id'
215 ,p_base_key_value => p_base_key_value
216 );
217 --
218 hr_utility.set_location(l_proc, 10);
219 --
220 --
221 -- Update the specified datetrack row setting the effective
222 -- end date to the specified new effective end date.
223 --
224 update irc_assignment_details_f t
225 set t.effective_end_date = p_new_effective_end_date
226 , t.object_version_number = l_object_version_number
227 where t.assignment_id = p_base_key_value
231 --
228 and p_effective_date
229 between t.effective_start_date and t.effective_end_date;
230 --
232 p_object_version_number := l_object_version_number;
233 hr_utility.set_location(' Leaving:'||l_proc, 15);
234 --
235 End upd_effective_end_date;
236 --
237 -- ----------------------------------------------------------------------------
238 -- |---------------------------------< lck >----------------------------------|
239 -- ----------------------------------------------------------------------------
240 Procedure lck
241 (p_effective_date in date
242 ,p_datetrack_mode in varchar2
243 ,p_assignment_details_id in number
244 ,p_object_version_number in number
245 ,p_validation_start_date out nocopy date
246 ,p_validation_end_date out nocopy date
247 ) is
248 --
249 l_proc varchar2(72) := g_package||'lck';
250 l_validation_start_date date;
251 l_validation_end_date date;
252 l_argument varchar2(30);
253 --
254 -- Cursor C_Sel1 selects the current locked row as of session date
255 -- ensuring that the object version numbers match.
256 --
257 Cursor C_Sel1 is
258 select
259 assignment_details_id
260 ,assignment_id
261 ,effective_start_date
262 ,effective_end_date
263 ,details_version
264 ,latest_details
265 ,attempt_id
266 ,qualified
267 ,considered
268 ,object_version_number
269 from irc_assignment_details_f
270 where assignment_id = (select assignment_id
271 from irc_assignment_details_f
272 where assignment_details_id = p_assignment_details_id)
273 and p_effective_date between effective_start_date
274 and effective_end_date
275 order by details_version desc
276 for update nowait;
277 --
278 Begin
279 hr_utility.set_location('Entering:'||l_proc, 5);
280 --
281 -- Ensure that all the mandatory arguments are not null
282 --
283 hr_api.mandatory_arg_error(p_api_name => l_proc
284 ,p_argument => 'effective_date'
285 ,p_argument_value => p_effective_date
286 );
287 --
288 hr_api.mandatory_arg_error(p_api_name => l_proc
289 ,p_argument => 'datetrack_mode'
290 ,p_argument_value => p_datetrack_mode
291 );
292 --
293 hr_api.mandatory_arg_error(p_api_name => l_proc
294 ,p_argument => 'assignment_details_id'
295 ,p_argument_value => p_assignment_details_id
296 );
297 --
298 hr_api.mandatory_arg_error(p_api_name => l_proc
299 ,p_argument => 'object_version_number'
300 ,p_argument_value => p_object_version_number
301 );
302 --
303 --
304 -- Check to ensure the datetrack mode is not INSERT.
305 --
306 If (p_datetrack_mode <> hr_api.g_insert) then
307 --
308 -- We must select and lock the current row.
309 --
310 Open C_Sel1;
311 Fetch C_Sel1 Into irc_iad_shd.g_old_rec;
312 If C_Sel1%notfound then
313 Close C_Sel1;
314 --
315 -- The primary key is invalid therefore we must error
316 --
317 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
318 fnd_message.raise_error;
319 End If;
320 Close C_Sel1;
321 If (p_object_version_number
322 <> irc_iad_shd.g_old_rec.object_version_number) Then
323 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
324 fnd_message.raise_error;
325 End If;
326 --
327 --
328 -- Validate the datetrack mode mode getting the validation start
329 -- and end dates for the specified datetrack operation.
330 --
331 If (p_datetrack_mode = hr_api.g_correction) then
332 dt_api.validate_dt_mode
333 (p_effective_date => p_effective_date
334 ,p_datetrack_mode => p_datetrack_mode
335 ,p_base_table_name => 'irc_assignment_details_f'
336 ,p_base_key_column => 'assignment_details_id'
337 ,p_base_key_value => p_assignment_details_id
338 ,p_parent_table_name1 => 'per_all_assignments_f'
339 ,p_parent_key_column1 => 'assignment_id'
340 ,p_parent_key_value1 => irc_iad_shd.g_old_rec.assignment_id
341 ,p_enforce_foreign_locking => true
342 ,p_validation_start_date => l_validation_start_date
343 ,p_validation_end_date => l_validation_end_date
344 );
345 Else
346 dt_api.validate_dt_mode
347 (p_effective_date => p_effective_date
348 ,p_datetrack_mode => p_datetrack_mode
349 ,p_base_table_name => 'irc_assignment_details_f'
350 ,p_base_key_column => 'assignment_id'
351 ,p_base_key_value => irc_iad_shd.g_old_rec.assignment_id
352 ,p_parent_table_name1 => 'per_all_assignments_f'
353 ,p_parent_key_column1 => 'assignment_id'
354 ,p_parent_key_value1 => irc_iad_shd.g_old_rec.assignment_id
355 ,p_enforce_foreign_locking => true
356 ,p_validation_start_date => l_validation_start_date
357 ,p_validation_end_date => l_validation_end_date
358 );
359 End If;
360 Else
361 --
362 -- We are doing a datetrack 'INSERT' which is illegal within this
363 -- procedure therefore we must error (note: to lck on insert the
364 -- private procedure ins_lck should be called).
365 --
366 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
367 fnd_message.set_token('PROCEDURE', l_proc);
368 fnd_message.set_token('STEP','20');
369 fnd_message.raise_error;
370 End If;
371 --
372 -- Set the validation start and end date OUT arguments
373 --
374 p_validation_start_date := l_validation_start_date;
375 p_validation_end_date := l_validation_end_date;
376 --
377 hr_utility.set_location(' Leaving:'||l_proc, 30);
378 --
379 -- We need to trap the ORA LOCK exception
380 --
381 Exception
382 When HR_Api.Object_Locked then
383 --
384 -- The object is locked therefore we need to supply a meaningful
385 -- error message.
386 --
387 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
388 fnd_message.set_token('TABLE_NAME', 'irc_assignment_details_f');
389 fnd_message.raise_error;
390 End lck;
391 --
392 -- ----------------------------------------------------------------------------
393 -- |-----------------------------< convert_args >-----------------------------|
394 -- ----------------------------------------------------------------------------
395 Function convert_args
396 (p_assignment_details_id in number
397 ,p_assignment_id in number
398 ,p_effective_start_date in date
399 ,p_effective_end_date in date
400 ,p_details_version in number
401 ,p_latest_details in varchar2
402 ,p_attempt_id in number
403 ,p_qualified in varchar2
404 ,p_considered in varchar2
405 ,p_object_version_number in number
406 )
407 Return g_rec_type is
408 --
409 l_rec g_rec_type;
410 --
411 Begin
412 --
413 -- Convert arguments into local l_rec structure.
414 --
415 l_rec.assignment_details_id := p_assignment_details_id;
416 l_rec.assignment_id := p_assignment_id;
417 l_rec.effective_start_date := p_effective_start_date;
418 l_rec.effective_end_date := p_effective_end_date;
419 l_rec.details_version := p_details_version;
420 l_rec.latest_details := p_latest_details;
421 l_rec.attempt_id := p_attempt_id;
422 l_rec.qualified := p_qualified;
423 l_rec.considered := p_considered;
424 l_rec.object_version_number := p_object_version_number;
425 --
426 -- Return the plsql record structure.
427 --
428 Return(l_rec);
429 --
430 End convert_args;
431 --
432 end irc_iad_shd;