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