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