[Home] [Help]
PACKAGE BODY: APPS.BEN_BEO_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_beo_shd as
2 /* $Header: bebeorhi.pkb 120.0.12010000.3 2008/08/05 14:07:36 ubhat ship $ */
3 --
5 -- | Private Global Definitions |
9 --
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_beo_shd.'; -- Global package name
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 = 'BEN_ELIG_OBJ_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_elig_obj_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 elig_obj_id
49 ,effective_start_date
50 ,effective_end_date
51 ,table_name
52 ,column_name
53 ,column_value
54 ,business_group_id
55 ,object_version_number
56 from ben_elig_obj_f
57 where elig_obj_id = p_elig_obj_id
58 and p_effective_date
59 between effective_start_date and effective_end_date;
60 --
61 l_fct_ret boolean;
62 --
63 Begin
64 --
65 If (p_effective_date is null or
66 p_elig_obj_id is null or
67 p_object_version_number is null) Then
68 --
69 -- One of the primary key arguments is null therefore we must
70 -- set the returning function value to false
71 --
72 l_fct_ret := false;
73 Else
74 If (p_elig_obj_id =
75 ben_beo_shd.g_old_rec.elig_obj_id and
76 p_object_version_number =
77 ben_beo_shd.g_old_rec.object_version_number
78 ) Then
79 --
80 -- The g_old_rec is current therefore we must
81 -- set the returning function to true
82 --
83 l_fct_ret := true;
84 Else
85 --
86 -- Select the current row
87 --
88 Open C_Sel1;
89 Fetch C_Sel1 Into ben_beo_shd.g_old_rec;
90 If C_Sel1%notfound Then
91 Close C_Sel1;
92 --
93 -- The primary key is invalid therefore we must error
94 --
95 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
96 fnd_message.raise_error;
97 End If;
98 Close C_Sel1;
99 If (p_object_version_number
100 <> ben_beo_shd.g_old_rec.object_version_number) Then
101 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
102 fnd_message.raise_error;
103 End If;
104 l_fct_ret := true;
105 End If;
106 End If;
107 Return (l_fct_ret);
108 --
109 End api_updating;
110 --
111 -- ----------------------------------------------------------------------------
112 -- |---------------------------< find_dt_upd_modes >--------------------------|
113 -- ----------------------------------------------------------------------------
114 Procedure find_dt_upd_modes
115 (p_effective_date in date
116 ,p_base_key_value in number
117 ,p_correction out nocopy boolean
118 ,p_update out nocopy boolean
119 ,p_update_override out nocopy boolean
120 ,p_update_change_insert out nocopy boolean
121 ) is
122 --
123 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
124 --
125 Begin
126 hr_utility.set_location('Entering:'||l_proc, 5);
127 --
128 -- Call the corresponding datetrack api
129 --
130 dt_api.find_dt_upd_modes
131 (p_effective_date => p_effective_date
132 ,p_base_table_name => 'ben_elig_obj_f'
133 ,p_base_key_column => 'elig_obj_id'
134 ,p_base_key_value => p_base_key_value
135 ,p_correction => p_correction
136 ,p_update => p_update
137 ,p_update_override => p_update_override
138 ,p_update_change_insert => p_update_change_insert
139 );
140 --
141 hr_utility.set_location(' Leaving:'||l_proc, 10);
142 End find_dt_upd_modes;
143 --
144 -- ----------------------------------------------------------------------------
145 -- |---------------------------< find_dt_del_modes >--------------------------|
146 -- ----------------------------------------------------------------------------
147 Procedure find_dt_del_modes
148 (p_effective_date in date
149 ,p_base_key_value in number
150 ,p_zap out nocopy boolean
151 ,p_delete out nocopy boolean
152 ,p_future_change out nocopy boolean
156 l_proc varchar2(72) := g_package||'find_dt_del_modes';
153 ,p_delete_next_change out nocopy boolean
154 ) is
155 --
157 --
158 l_parent_tab1 varchar2(30);
159 l_parent_column1 varchar2(30);
160 l_parent_value1 varchar2(30);
161 --
162 Cursor C_Sel1 Is
163 select
164 t.table_name,
165 t.column_name,
166 t.column_value
167 from ben_elig_obj_f t
168 where t.elig_obj_id = p_base_key_value
169 and p_effective_date
170 between t.effective_start_date and t.effective_end_date;
171 --
172 Begin
173 hr_utility.set_location('Entering:'||l_proc, 5);
174 Open C_sel1;
175 Fetch C_Sel1 Into
176 l_parent_tab1,
177 l_parent_column1,
178 l_parent_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 => 'ben_elig_obj_f'
193 ,p_base_key_column => 'elig_obj_id'
194 ,p_base_key_value => p_base_key_value
195 ,p_parent_table_name1 => l_parent_tab1
196 ,p_parent_key_column1 => l_parent_column1
197 ,p_parent_key_value1 => l_parent_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 => 'ben_elig_obj_f'
231 ,p_base_key_column => 'elig_obj_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 ben_elig_obj_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.elig_obj_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_elig_obj_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 elig_obj_id
277 ,effective_start_date
278 ,effective_end_date
279 ,table_name
280 ,column_name
281 ,column_value
282 ,business_group_id
283 ,object_version_number
284 from ben_elig_obj_f
285 where elig_obj_id = p_elig_obj_id
286 and p_effective_date
287 between effective_start_date and effective_end_date
288 for update nowait;
289 --
290 --
291 --
292 Begin
293 hr_utility.set_location('Entering:'||l_proc, 5);
294 --
295 -- Ensure that all the mandatory arguments are not null
296 --
297 hr_api.mandatory_arg_error(p_api_name => l_proc
298 ,p_argument => 'effective_date'
299 ,p_argument_value => p_effective_date
300 );
301 --
302 hr_api.mandatory_arg_error(p_api_name => l_proc
306 --
303 ,p_argument => 'datetrack_mode'
304 ,p_argument_value => p_datetrack_mode
305 );
307 hr_api.mandatory_arg_error(p_api_name => l_proc
308 ,p_argument => 'elig_obj_id'
309 ,p_argument_value => p_elig_obj_id
310 );
311 --
312 hr_api.mandatory_arg_error(p_api_name => l_proc
313 ,p_argument => 'object_version_number'
314 ,p_argument_value => p_object_version_number
315 );
316 --
317 -- Check to ensure the datetrack mode is not INSERT.
318 --
319 If (p_datetrack_mode <> hr_api.g_insert) then
320 --
321 -- We must select and lock the current row.
322 --
323 Open C_Sel1;
324 Fetch C_Sel1 Into ben_beo_shd.g_old_rec;
325 If C_Sel1%notfound then
326 Close C_Sel1;
327 --
328 -- The primary key is invalid therefore we must error
329 --
330 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
331 fnd_message.raise_error;
332 End If;
333 Close C_Sel1;
334 If (p_object_version_number
335 <> ben_beo_shd.g_old_rec.object_version_number) Then
336 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
337 fnd_message.raise_error;
338 End If;
339 --
340 --
341 -- Validate the datetrack mode mode getting the validation start
342 -- and end dates for the specified datetrack operation.
343 --
344 dt_api.validate_dt_mode
345 (p_effective_date => p_effective_date
346 ,p_datetrack_mode => p_datetrack_mode
347 ,p_base_table_name => 'ben_elig_obj_f'
348 ,p_base_key_column => 'elig_obj_id'
352 ,p_parent_key_value1 => ben_beo_shd.g_old_rec.column_value
349 ,p_base_key_value => p_elig_obj_id
350 ,p_parent_table_name1 => ben_beo_shd.g_old_rec.table_name
351 ,p_parent_key_column1 => ben_beo_shd.g_old_rec.column_name
353 ,p_enforce_foreign_locking => false --true
354 ,p_validation_start_date => l_validation_start_date
355 ,p_validation_end_date => l_validation_end_date
356 );
357 Else
358 --
359 -- We are doing a datetrack 'INSERT' which is illegal within this
360 -- procedure therefore we must error (note: to lck on insert the
361 -- private procedure ins_lck should be called).
362 --
363 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
364 fnd_message.set_token('PROCEDURE', l_proc);
365 fnd_message.set_token('STEP','20');
366 fnd_message.raise_error;
367 End If;
368 --
369 -- Set the validation start and end date OUT arguments
370 --
371 p_validation_start_date := l_validation_start_date;
372 p_validation_end_date := l_validation_end_date;
373 --
374 hr_utility.set_location(' Leaving:'||l_proc, 30);
375 --
376 -- We need to trap the ORA LOCK exception
377 --
378 Exception
379 When HR_Api.Object_Locked then
380 --
381 -- The object is locked therefore we need to supply a meaningful
382 -- error message.
383 --
384 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
385 fnd_message.set_token('TABLE_NAME', 'ben_elig_obj_f');
386 fnd_message.raise_error;
387 End lck;
388 --
389 -- ----------------------------------------------------------------------------
390 -- |-----------------------------< convert_args >-----------------------------|
391 -- ----------------------------------------------------------------------------
392 Function convert_args
393 (p_elig_obj_id in number
394 ,p_effective_start_date in date
395 ,p_effective_end_date in date
396 ,p_table_name in varchar2
397 ,p_column_name in varchar2
398 ,p_column_value in varchar2
399 ,p_business_group_id in number
400 ,p_object_version_number in number
401 )
402 Return g_rec_type is
403 --
404 l_rec g_rec_type;
405 --
406 Begin
407 --
408 -- Convert arguments into local l_rec structure.
409 --
410 l_rec.elig_obj_id := p_elig_obj_id;
411 l_rec.effective_start_date := p_effective_start_date;
412 l_rec.effective_end_date := p_effective_end_date;
413 l_rec.table_name := p_table_name;
414 l_rec.column_name := p_column_name;
415 l_rec.column_value := p_column_value;
416 l_rec.business_group_id := p_business_group_id;
420 --
417 l_rec.object_version_number := p_object_version_number;
418 --
419 -- Return the plsql record structure.
421 Return(l_rec);
422 --
423 End convert_args;
424 --
425 end ben_beo_shd;