[Home] [Help]
PACKAGE BODY: APPS.PER_ABV_SHD
Source
1 Package Body per_abv_shd as
2 /* $Header: peabvrhi.pkb 120.0.12010000.2 2009/05/27 06:36:51 brsinha ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_abv_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 Function return_api_dml_status Return Boolean Is
15 --
16 l_proc varchar2(72) := g_package||'return_api_dml_status';
17 --
18 Begin
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 Return (nvl(g_api_dml, false));
22 --
23 hr_utility.set_location(' Leaving:'||l_proc, 10);
24 End return_api_dml_status;
25 --
26 --
27 -- ----------------------------------------------------------------------------
28 -- |-----------------------------< convert_args >-----------------------------|
29 -- ----------------------------------------------------------------------------
30 --
31 Function convert_args
32 (p_assignment_budget_value_id number
33 ,p_business_group_id number
34 ,p_assignment_id number
35 ,p_unit varchar2
36 ,p_value number
37 ,p_request_id number
38 ,p_program_application_id number
39 ,p_program_id number
40 ,p_program_update_date date
41 ,p_effective_start_date date
42 ,p_effective_end_date date
43 ,p_object_version_number number
44 ) Return g_rec_type is
45 --
46 l_rec g_rec_type;
47 l_proc varchar2(72) := g_package||'convert_args';
48 --
49 Begin
50 --
51 hr_utility.set_location('Entering:'||l_proc, 5);
52 --
53 -- Convert arguments into local l_rec structure.
54 --
55 l_rec.assignment_budget_value_id := p_assignment_budget_value_id;
56 l_rec.business_group_id := p_business_group_id;
57 l_rec.assignment_id := p_assignment_id;
58 l_rec.unit := p_unit;
59 l_rec.value := p_value;
60 l_rec.request_id := p_request_id;
61 l_rec.program_application_id := p_program_application_id;
62 l_rec.program_id := p_program_id;
63 l_rec.program_update_date := p_program_update_date;
64 l_rec.effective_start_date := p_effective_start_date;
65 l_rec.effective_end_date := p_effective_end_date;
66 l_rec.object_version_number := p_object_version_number;
67 --
68 -- Return the plsql record structure.
69 --
70 hr_utility.set_location(' Leaving:'||l_proc, 10);
71 Return(l_rec);
72 --
73 end convert_args;
74
75
76 -- ----------------------------------------------------------------------------
77 -- |---------------------------< constraint_error >---------------------------|
78 -- ----------------------------------------------------------------------------
79 Procedure constraint_error
80 (p_constraint_name in all_constraints.constraint_name%TYPE
81 ) Is
82 --
83 l_proc varchar2(72) := g_package||'constraint_error';
84 --
85 Begin
86 --
87 If (p_constraint_name = 'PER_ASSIGNMENT_BUDGET_VALU_FK1') Then
88 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
89 fnd_message.set_token('PROCEDURE', l_proc);
90 fnd_message.set_token('STEP','5');
91 fnd_message.raise_error;
92 ElsIf (p_constraint_name = 'PER_ASSIGNMENT_BUDGET_VALU_PK') Then
93 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
94 fnd_message.set_token('PROCEDURE', l_proc);
95 fnd_message.set_token('STEP','10');
96 fnd_message.raise_error;
97 ElsIf (p_constraint_name = 'PER_ASSIGNMENT_BUDGET_VALU_UK2') Then
98 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
99 fnd_message.set_token('PROCEDURE', l_proc);
100 fnd_message.set_token('STEP','15');
101 fnd_message.raise_error;
102 Else
103 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
104 fnd_message.set_token('PROCEDURE', l_proc);
105 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
106 fnd_message.raise_error;
107 End If;
108 --
109 End constraint_error;
110 --
111 -- ----------------------------------------------------------------------------
112 -- |-----------------------------< api_updating >-----------------------------|
113 -- ----------------------------------------------------------------------------
114 --
115 Function api_updating
116 (p_effective_date in date
117 ,p_assignment_budget_value_id in number
118 ,p_object_version_number in number
119 ) Return Boolean Is
120 --
121 -- Cursor selects the 'current' row from the HR Schema
122 --
123 Cursor C_Sel1 is
124 select
125 assignment_budget_value_id
126 ,business_group_id
127 ,assignment_id
128 ,unit
129 ,value
130 ,request_id
131 ,program_application_id
132 ,program_id
133 ,program_update_date
134 ,effective_start_date
135 ,effective_end_date
136 ,object_version_number
137 from per_assignment_budget_values_f
138 where assignment_budget_value_id = p_assignment_budget_value_id
139 and p_effective_date
140 between effective_start_date and effective_end_date;
141 --
142 l_fct_ret boolean;
143 --
144 Begin
145 --
146 If (p_effective_date is null or
147 p_assignment_budget_value_id is null or
148 p_object_version_number is null ) Then
149 --
150 -- One of the primary key arguments is null therefore we must
151 -- set the returning function value to false
152 --
153 l_fct_ret := false;
154 Else
155 If (p_assignment_budget_value_id =
156 per_abv_shd.g_old_rec.assignment_budget_value_id and
157 p_object_version_number = g_old_rec.object_version_number) Then
158 --
159 -- The g_old_rec is current therefore we must
160 -- set the returning function to true
161 --
162 l_fct_ret := true;
163 Else
164 --
165 -- Select the current row
166 --
167 Open C_Sel1;
168 Fetch C_Sel1 Into per_abv_shd.g_old_rec;
169 If C_Sel1%notfound Then
170 Close C_Sel1;
171 --
172 -- The primary key is invalid therefore we must error
173 --
174 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
175 fnd_message.raise_error;
176 End If;
177 Close C_Sel1;
178 If (p_object_version_number
179 <> per_abs_shd.g_old_rec.object_version_number) Then
180 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
181 fnd_message.raise_error;
182 End If;
183 l_fct_ret := true;
184 End If;
185 End If;
186 Return (l_fct_ret);
187 --
188 End api_updating;
189 --
190 -- ----------------------------------------------------------------------------
191 -- |---------------------------< find_dt_upd_modes >--------------------------|
192 -- ----------------------------------------------------------------------------
193 Procedure find_dt_upd_modes
194 (p_effective_date in date
195 ,p_base_key_value in number
196 ,p_correction out nocopy boolean
197 ,p_update out nocopy boolean
198 ,p_update_override out nocopy boolean
199 ,p_update_change_insert out nocopy boolean
200 ) is
201 --
202 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
203 --
204 Begin
205 hr_utility.set_location('Entering:'||l_proc, 5);
206 --
207 -- Call the corresponding datetrack api
208 --
209 dt_api.find_dt_upd_modes
210 (p_effective_date => p_effective_date
211 ,p_base_table_name => 'per_assignment_budget_values_f'
212 ,p_base_key_column => 'assignment_budget_value_id'
213 ,p_base_key_value => p_base_key_value
214 ,p_correction => p_correction
215 ,p_update => p_update
216 ,p_update_override => p_update_override
217 ,p_update_change_insert => p_update_change_insert
218 );
219 --
220 hr_utility.set_location(' Leaving:'||l_proc, 10);
221 End find_dt_upd_modes;
222 --
223 -- ----------------------------------------------------------------------------
224 -- |---------------------------< find_dt_del_modes >--------------------------|
225 -- ----------------------------------------------------------------------------
226 Procedure find_dt_del_modes
227 (p_effective_date in date
228 ,p_base_key_value in number
229 ,p_zap out nocopy boolean
230 ,p_delete out nocopy boolean
231 ,p_future_change out nocopy boolean
232 ,p_delete_next_change out nocopy boolean
233 ) is
234 --
235 l_proc varchar2(72) := g_package||'find_dt_del_modes';
236 --
237 l_parent_key_value1 number;
238 --
239 Cursor C_Sel1 Is
240 select
241 t.assignment_id
242 from per_assignment_budget_values_f t
243 where t.assignment_budget_value_id = p_base_key_value
244 and p_effective_date
245 between t.effective_start_date and t.effective_end_date;
246 --
247 Begin
248 hr_utility.set_location('Entering:'||l_proc, 5);
249 Open C_sel1;
250 Fetch C_Sel1 Into
251 l_parent_key_value1;
252 If C_Sel1%NOTFOUND then
253 Close C_Sel1;
254 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
255 fnd_message.set_token('PROCEDURE',l_proc);
256 fnd_message.set_token('STEP','10');
257 fnd_message.raise_error;
258 End If;
259 Close C_Sel1;
260 --
261 -- Call the corresponding datetrack api
262 --
263 dt_api.find_dt_del_modes
264 (p_effective_date => p_effective_date
265 ,p_base_table_name => 'per_assignment_budget_values_f'
266 ,p_base_key_column => 'assignment_budget_value_id'
267 ,p_base_key_value => p_base_key_value
268 ,p_parent_table_name1 => 'per_all_assignments_f'
269 ,p_parent_key_column1 => 'assignment_id'
270 ,p_parent_key_value1 => l_parent_key_value1
271 ,p_zap => p_zap
272 ,p_delete => p_delete
273 ,p_future_change => p_future_change
274 ,p_delete_next_change => p_delete_next_change
275 );
276 --
277 hr_utility.set_location(' Leaving:'||l_proc, 10);
278 End find_dt_del_modes;
279 --
280 -- ----------------------------------------------------------------------------
281 -- |-----------------------< upd_effective_end_date >-------------------------|
282 -- ----------------------------------------------------------------------------
283 Procedure upd_effective_end_date
284 (p_effective_date in date
285 ,p_base_key_value in number
286 ,p_new_effective_end_date in date
287 ,p_validation_start_date in date
288 ,p_validation_end_date in date
289 ,p_object_version_number out nocopy number
290 ) is
291 --
292 l_proc varchar2(72) := g_package||'upd_effective_end_date';
293 l_object_version_number number(9);
294 --
295 Begin
296 hr_utility.set_location('Entering:'||l_proc, 5);
297 --
298 -- Because we are updating a row we must get the next object
299 -- version number.
300 --
301 l_object_version_number :=
302 dt_api.get_object_version_number
303 (p_base_table_name => 'per_assignment_budget_values_f',
304 p_base_key_column => 'assignment_budget_value_id',
305 p_base_key_value => p_base_key_value);
306 --
307 hr_utility.set_location(l_proc, 10);
308 g_api_dml := true; -- Set the api dml status
309 --
310 --
311 -- Update the specified datetrack row setting the effective
312 -- end date to the specified new effective end date.
313 --
314 update per_assignment_budget_values_f t
315 set t.effective_end_date = p_new_effective_end_date
316 where t.assignment_budget_value_id = p_base_key_value
317 and p_effective_date
318 between t.effective_start_date and t.effective_end_date;
319 --
320
321 g_api_dml := false; -- Unset the api dml status (bug 8506130)
322 p_object_version_number := l_object_version_number;
323 hr_utility.set_location(' Leaving:'||l_proc, 15);
324 --
325 End upd_effective_end_date;
326 --
327 -- ----------------------------------------------------------------------------
328 -- |---------------------------------< lck >----------------------------------|
329 -- ----------------------------------------------------------------------------
330 Procedure lck
331 (p_effective_date in date
332 ,p_datetrack_mode in varchar2
333 ,p_assignment_budget_value_id in number
334 ,p_object_version_number in number
335 ,p_validation_start_date out nocopy date
336 ,p_validation_end_date out nocopy date
337 ) is
338 --
339 l_proc varchar2(72) := g_package||'lck';
340 l_validation_start_date date;
341 l_validation_end_date date;
342 l_argument varchar2(30);
343 --
344 -- Cursor C_Sel1 selects the current locked row as of session date
345 -- ensuring that the object version numbers match.
346 --
347 Cursor C_Sel1 is
348 select
349 assignment_budget_value_id
350 ,business_group_id
351 ,assignment_id
352 ,unit
353 ,value
354 ,request_id
355 ,program_application_id
356 ,program_id
357 ,program_update_date
358 ,effective_start_date
359 ,effective_end_date
360 ,object_version_number
361 from per_assignment_budget_values_f
362 where assignment_budget_value_id = p_assignment_budget_value_id
363 and p_effective_date
364 between effective_start_date and effective_end_date
365 for update nowait;
366 --
367 --
368 --
369 Begin
370 hr_utility.set_location('Entering:'||l_proc, 5);
371 --
372 -- Ensure that all the mandatory arguments are not null
373 --
374 hr_api.mandatory_arg_error(p_api_name => l_proc
375 ,p_argument => 'effective_date'
376 ,p_argument_value => p_effective_date
377 );
378 --
379 hr_api.mandatory_arg_error(p_api_name => l_proc
380 ,p_argument => 'datetrack_mode'
381 ,p_argument_value => p_datetrack_mode
382 );
383 --
384 hr_api.mandatory_arg_error(p_api_name => l_proc
385 ,p_argument => 'assignment_budget_value_id'
386 ,p_argument_value => p_assignment_budget_value_id
387 );
388 --
389 hr_api.mandatory_arg_error(p_api_name => l_proc,
390 p_argument => 'object_version_number',
391 p_argument_value => p_object_version_number);
392 --
393 --
394 -- Check to ensure the datetrack mode is not INSERT.
395 --
396 If (p_datetrack_mode <> hr_api.g_insert) then
397 --
398 -- We must select and lock the current row.
399 --
400 Open C_Sel1;
401 Fetch C_Sel1 Into per_abv_shd.g_old_rec;
402 If C_Sel1%notfound then
403 Close C_Sel1;
404 --
405 -- The primary key is invalid therefore we must error
406 --
407 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
408 fnd_message.raise_error;
409 End If;
410 Close C_Sel1;
411 --
412 --
413 If (p_object_version_number <> g_old_rec.object_version_number) Then
414 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
415 hr_utility.raise_error;
416 End If;
417 --
418 -- Validate the datetrack mode mode getting the validation start
419 -- and end dates for the specified datetrack operation.
420 --
421 dt_api.validate_dt_mode
422 (p_effective_date => p_effective_date
423 ,p_datetrack_mode => p_datetrack_mode
424 ,p_base_table_name => 'per_assignment_budget_values_f'
425 ,p_base_key_column => 'assignment_budget_value_id'
426 ,p_base_key_value => p_assignment_budget_value_id
427 ,p_parent_table_name1 => 'per_all_assignments_f'
428 ,p_parent_key_column1 => 'assignment_id'
429 ,p_parent_key_value1 => per_abv_shd.g_old_rec.assignment_id
430 ,p_enforce_foreign_locking => true
431 ,p_validation_start_date => l_validation_start_date
432 ,p_validation_end_date => l_validation_end_date
433 );
434 Else
435 --
436 -- We are doing a datetrack 'INSERT' which is illegal within this
437 -- procedure therefore we must error (note: to lck on insert the
438 -- private procedure ins_lck should be called).
439 --
440 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
441 fnd_message.set_token('PROCEDURE', l_proc);
442 fnd_message.set_token('STEP','20');
443 fnd_message.raise_error;
444 End If;
445 --
446 -- Set the validation start and end date OUT arguments
447 --
448 p_validation_start_date := l_validation_start_date;
449 p_validation_end_date := l_validation_end_date;
450 --
451 hr_utility.set_location(' Leaving:'||l_proc, 30);
452 --
453 -- We need to trap the ORA LOCK exception
454 --
455 Exception
456 When HR_Api.Object_Locked then
457 --
458 -- The object is locked therefore we need to supply a meaningful
459 -- error message.
460 --
461 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
462 fnd_message.set_token('TABLE_NAME', 'per_assignment_budget_values_f');
463 fnd_message.raise_error;
464 End lck;
465 --
466 -- ----------------------------------------------------------------------------
467 -- |-----------------------------< convert_args >-----------------------------|
468 -- ----------------------------------------------------------------------------
469 Function convert_args
470 (p_assignment_budget_value_id in number
471 ,p_effective_start_date in date
472 ,p_effective_end_date in date
473 ,p_business_group_id in number
474 ,p_assignment_id in number
475 ,p_unit in varchar2
476 ,p_value in number
477 ,p_request_id in number
478 ,p_program_application_id in number
479 ,p_program_id in number
480 ,p_program_update_date in date
481 ,p_object_version_number in number
482 )
483 Return g_rec_type is
484 --
485 l_rec g_rec_type;
486 --
487 Begin
488 --
489 -- Convert arguments into local l_rec structure.
490 --
491 l_rec.assignment_budget_value_id := p_assignment_budget_value_id;
492 l_rec.effective_start_date := p_effective_start_date;
493 l_rec.effective_end_date := p_effective_end_date;
494 l_rec.business_group_id := p_business_group_id;
495 l_rec.assignment_id := p_assignment_id;
496 l_rec.unit := p_unit;
497 l_rec.value := p_value;
498 l_rec.request_id := p_request_id;
499 l_rec.program_application_id := p_program_application_id;
500 l_rec.program_id := p_program_id;
501 l_rec.program_update_date := p_program_update_date;
502 --
503 -- Return the plsql record structure.
504 --
505 Return(l_rec);
506 --
507 End convert_args;
508 --
509 end per_abv_shd;