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