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