[Home] [Help]
PACKAGE BODY: APPS.PER_ABV_SHD
Source
1 Package Body per_abv_shd as
2 /* $Header: peabvrhi.pkb 115.9 2002/12/03 14:36:09 raranjan noship $ */
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 p_object_version_number := l_object_version_number;
322 hr_utility.set_location(' Leaving:'||l_proc, 15);
323 --
324 End upd_effective_end_date;
325 --
326 -- ----------------------------------------------------------------------------
327 -- |---------------------------------< lck >----------------------------------|
328 -- ----------------------------------------------------------------------------
329 Procedure lck
330 (p_effective_date in date
331 ,p_datetrack_mode in varchar2
332 ,p_assignment_budget_value_id in number
333 ,p_object_version_number in number
334 ,p_validation_start_date out nocopy date
335 ,p_validation_end_date out nocopy date
336 ) is
337 --
338 l_proc varchar2(72) := g_package||'lck';
339 l_validation_start_date date;
340 l_validation_end_date date;
341 l_argument varchar2(30);
342 --
343 -- Cursor C_Sel1 selects the current locked row as of session date
344 -- ensuring that the object version numbers match.
345 --
346 Cursor C_Sel1 is
347 select
348 assignment_budget_value_id
349 ,business_group_id
350 ,assignment_id
351 ,unit
352 ,value
353 ,request_id
354 ,program_application_id
355 ,program_id
356 ,program_update_date
357 ,effective_start_date
358 ,effective_end_date
359 ,object_version_number
360 from per_assignment_budget_values_f
361 where assignment_budget_value_id = p_assignment_budget_value_id
362 and p_effective_date
363 between effective_start_date and effective_end_date
364 for update nowait;
365 --
366 --
367 --
368 Begin
369 hr_utility.set_location('Entering:'||l_proc, 5);
370 --
371 -- Ensure that all the mandatory arguments are not null
372 --
373 hr_api.mandatory_arg_error(p_api_name => l_proc
374 ,p_argument => 'effective_date'
375 ,p_argument_value => p_effective_date
376 );
377 --
378 hr_api.mandatory_arg_error(p_api_name => l_proc
379 ,p_argument => 'datetrack_mode'
380 ,p_argument_value => p_datetrack_mode
381 );
382 --
383 hr_api.mandatory_arg_error(p_api_name => l_proc
384 ,p_argument => 'assignment_budget_value_id'
385 ,p_argument_value => p_assignment_budget_value_id
386 );
387 --
388 hr_api.mandatory_arg_error(p_api_name => l_proc,
389 p_argument => 'object_version_number',
390 p_argument_value => p_object_version_number);
391 --
392 --
393 -- Check to ensure the datetrack mode is not INSERT.
394 --
395 If (p_datetrack_mode <> hr_api.g_insert) then
396 --
397 -- We must select and lock the current row.
398 --
399 Open C_Sel1;
400 Fetch C_Sel1 Into per_abv_shd.g_old_rec;
401 If C_Sel1%notfound then
402 Close C_Sel1;
403 --
404 -- The primary key is invalid therefore we must error
405 --
406 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
407 fnd_message.raise_error;
408 End If;
409 Close C_Sel1;
410 --
411 --
412 If (p_object_version_number <> g_old_rec.object_version_number) Then
413 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
414 hr_utility.raise_error;
415 End If;
416 --
417 -- Validate the datetrack mode mode getting the validation start
418 -- and end dates for the specified datetrack operation.
419 --
420 dt_api.validate_dt_mode
421 (p_effective_date => p_effective_date
422 ,p_datetrack_mode => p_datetrack_mode
423 ,p_base_table_name => 'per_assignment_budget_values_f'
424 ,p_base_key_column => 'assignment_budget_value_id'
425 ,p_base_key_value => p_assignment_budget_value_id
426 ,p_parent_table_name1 => 'per_all_assignments_f'
427 ,p_parent_key_column1 => 'assignment_id'
428 ,p_parent_key_value1 => per_abv_shd.g_old_rec.assignment_id
429 ,p_enforce_foreign_locking => true
430 ,p_validation_start_date => l_validation_start_date
431 ,p_validation_end_date => l_validation_end_date
432 );
433 Else
434 --
435 -- We are doing a datetrack 'INSERT' which is illegal within this
436 -- procedure therefore we must error (note: to lck on insert the
437 -- private procedure ins_lck should be called).
438 --
439 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
440 fnd_message.set_token('PROCEDURE', l_proc);
441 fnd_message.set_token('STEP','20');
442 fnd_message.raise_error;
443 End If;
444 --
445 -- Set the validation start and end date OUT arguments
446 --
447 p_validation_start_date := l_validation_start_date;
448 p_validation_end_date := l_validation_end_date;
449 --
450 hr_utility.set_location(' Leaving:'||l_proc, 30);
451 --
452 -- We need to trap the ORA LOCK exception
453 --
454 Exception
455 When HR_Api.Object_Locked then
456 --
457 -- The object is locked therefore we need to supply a meaningful
458 -- error message.
459 --
460 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
461 fnd_message.set_token('TABLE_NAME', 'per_assignment_budget_values_f');
462 fnd_message.raise_error;
463 End lck;
464 --
465 -- ----------------------------------------------------------------------------
466 -- |-----------------------------< convert_args >-----------------------------|
467 -- ----------------------------------------------------------------------------
468 Function convert_args
469 (p_assignment_budget_value_id in number
470 ,p_effective_start_date in date
471 ,p_effective_end_date in date
472 ,p_business_group_id in number
473 ,p_assignment_id in number
474 ,p_unit in varchar2
475 ,p_value in number
476 ,p_request_id in number
477 ,p_program_application_id in number
478 ,p_program_id in number
479 ,p_program_update_date in date
480 ,p_object_version_number in number
481 )
482 Return g_rec_type is
483 --
484 l_rec g_rec_type;
485 --
486 Begin
487 --
488 -- Convert arguments into local l_rec structure.
489 --
490 l_rec.assignment_budget_value_id := p_assignment_budget_value_id;
491 l_rec.effective_start_date := p_effective_start_date;
492 l_rec.effective_end_date := p_effective_end_date;
493 l_rec.business_group_id := p_business_group_id;
494 l_rec.assignment_id := p_assignment_id;
495 l_rec.unit := p_unit;
496 l_rec.value := p_value;
497 l_rec.request_id := p_request_id;
498 l_rec.program_application_id := p_program_application_id;
499 l_rec.program_id := p_program_id;
500 l_rec.program_update_date := p_program_update_date;
501 --
502 -- Return the plsql record structure.
503 --
504 Return(l_rec);
505 --
506 End convert_args;
507 --
508 end per_abv_shd;