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