[Home] [Help]
PACKAGE BODY: APPS.AME_CON_SHD
Source
1 Package Body ame_con_shd as
2 /* $Header: amconrhi.pkb 120.6 2006/01/12 22:43 pvelugul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_con_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 = 'AME_CONDITIONS_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_condition_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 condition_id
49 ,condition_type
50 ,attribute_id
51 ,parameter_one
52 ,parameter_two
53 ,parameter_three
54 ,include_lower_limit
55 ,include_upper_limit
56 ,start_date
57 ,end_date
58 ,security_group_id
59 ,condition_key
60 ,object_version_number
61 from ame_conditions
62 where condition_id = p_condition_id
63 and p_effective_date
64 between start_date
65 and nvl(end_date - ame_util.oneSecond,p_effective_date);
66 --
67 l_fct_ret boolean;
68 --
69 Begin
70 --
71 If (p_effective_date is null or
72 p_condition_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_condition_id =
81 ame_con_shd.g_old_rec.condition_id and
82 p_object_version_number =
83 ame_con_shd.g_old_rec.object_version_number) Then
84 --
85 -- The g_old_rec is current therefore we must
86 -- set the returning function to true
87 --
88 l_fct_ret := true;
89 Else
90 --
91 -- Select the current row
92 --
93 Open C_Sel1;
94 Fetch C_Sel1 Into ame_con_shd.g_old_rec;
95 If C_Sel1%notfound Then
96 Close C_Sel1;
97 --
98 -- The primary key is invalid therefore we must error
99 --
100 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
101 fnd_message.raise_error;
102 End If;
103 Close C_Sel1;
104 If (p_object_version_number
105 <> ame_con_shd.g_old_rec.object_version_number) Then
106 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
107 fnd_message.raise_error;
108 End If;
109 l_fct_ret := true;
110 End If;
111 End If;
112 Return (l_fct_ret);
113 --
114 End api_updating;
115 --
116 -- ----------------------------------------------------------------------------
117 -- |----------------------------< upd_end_date >------------------------------|
118 -- ----------------------------------------------------------------------------
119 Procedure upd_end_date
120 (p_effective_date in date
121 ,p_condition_id in number
122 ,p_new_end_date in date
123 ,p_object_version_number out nocopy number
124 ) is
125 --
126 l_proc varchar2(72) := g_package||'upd_end_date';
127 l_current_user_id integer;
128 l_object_version_number number;
129 --
130 Begin
131 hr_utility.set_location('Entering:'||l_proc, 5);
132 --
133 l_current_user_id := fnd_global.user_id;
134 --
135 -- Because we are updating a row we must get the next object
136 -- version number.
137 --
138 l_object_version_number :=
139 ame_con_shd.get_object_version_number
140 (p_condition_id => p_condition_id
141 );
142 --
143 hr_utility.set_location(l_proc, 10);
144 --
145 --
146 -- Update the specified datetrack row setting the effective
147 -- end date to the specified new effective end date.
148 --
152 ,t.last_update_date = p_new_end_date
149 update ame_conditions t
150 set t.end_date = p_new_end_date
151 ,t.last_updated_by = l_current_user_id
153 ,t.last_update_login = l_current_user_id
154 , t.object_version_number = l_object_version_number
155 where t.condition_id = p_condition_id
156 and p_effective_date
157 between t.start_date and nvl(t.end_date - ame_util.oneSecond,p_effective_date)
158 ;
159 --
160 --
161 p_object_version_number := l_object_version_number;
162 hr_utility.set_location(' Leaving:'||l_proc, 15);
163 --
164 End upd_end_date;
165 --
166 -- ----------------------------------------------------------------------------
167 -- |---------------------------------< lck >----------------------------------|
168 -- ----------------------------------------------------------------------------
169 Procedure lck
170 (p_effective_date in date
171 ,p_datetrack_mode in varchar2
172 ,p_condition_id in number
173 ,p_object_version_number in number
174 ,p_validation_start_date out nocopy date
175 ,p_validation_end_date out nocopy date
176 ) is
177 --
178 l_proc varchar2(72) := g_package||'lck';
179 l_validation_start_date date;
180 l_validation_end_date date;
181 l_argument varchar2(30);
182 --
183 -- Cursor C_Sel1 selects the current locked row as of session date
184 -- ensuring that the object version numbers match.
185 --
186 Cursor C_Sel1 is
187 select
188 condition_id
189 ,condition_type
190 ,attribute_id
191 ,parameter_one
192 ,parameter_two
193 ,parameter_three
194 ,include_lower_limit
195 ,include_upper_limit
196 ,start_date
197 ,end_date
198 ,security_group_id
199 ,condition_key
200 ,object_version_number
201 from ame_conditions
202 where condition_id = p_condition_id
203 and p_effective_date
204 between start_date and nvl(end_date - ame_util.oneSecond, p_effective_date)
205 for update nowait;
206 --
207 --
208 --
209 Begin
210 hr_utility.set_location('Entering:'||l_proc, 5);
211 --
212 -- Ensure that all the mandatory arguments are not null
213 --
214 hr_api.mandatory_arg_error(p_api_name => l_proc
215 ,p_argument => 'effective_date'
216 ,p_argument_value => p_effective_date
217 );
218 --
219 hr_api.mandatory_arg_error(p_api_name => l_proc
220 ,p_argument => 'datetrack_mode'
221 ,p_argument_value => p_datetrack_mode
222 );
223 --
224 hr_api.mandatory_arg_error(p_api_name => l_proc
225 ,p_argument => 'condition_id'
226 ,p_argument_value => p_condition_id
227 );
228 --
229 hr_api.mandatory_arg_error(p_api_name => l_proc
230 ,p_argument => 'object_version_number'
231 ,p_argument_value => p_object_version_number
232 );
233 --
234 -- Check to ensure the datetrack mode is not INSERT.
235 --
236 If (p_datetrack_mode <> hr_api.g_insert) then
237 --
238 -- We must select and lock the current row.
239 --
240 Open C_Sel1;
241 Fetch C_Sel1 Into ame_con_shd.g_old_rec;
242 If C_Sel1%notfound then
243 Close C_Sel1;
244 --
245 -- The primary key is invalid therefore we must error
246 --
247 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
248 fnd_message.raise_error;
249 End If;
250 Close C_Sel1;
251 If (p_object_version_number
252 <> ame_con_shd.g_old_rec.object_version_number) Then
253 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
254 fnd_message.raise_error;
255 End If;
256 --
257 --
258 --
259 Else
260 --
261 -- We are doing a datetrack 'INSERT' which is illegal within this
262 -- procedure therefore we must error (note: to lck on insert the
263 -- private procedure ins_lck should be called).
264 --
265 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
266 fnd_message.set_token('PROCEDURE', l_proc);
267 fnd_message.set_token('STEP','20');
268 fnd_message.raise_error;
269 End If;
270 --
271 -- Set the validation start and end date OUT arguments
272 --
273 if (p_datetrack_mode = hr_api.g_update) then
274 p_validation_start_date := p_effective_date;
275 p_validation_end_date := ame_con_shd.g_old_rec.end_date;
276 elsif (p_datetrack_mode = hr_api.g_delete) then
277 p_validation_start_date := p_effective_date;
278 p_validation_end_date := p_effective_date;
279 end if;
280 --
281 hr_utility.set_location(' Leaving:'||l_proc, 30);
282 --
283 -- We need to trap the ORA LOCK exception
284 --
285 Exception
286 When HR_Api.Object_Locked then
287 --
288 -- The object is locked therefore we need to supply a meaningful
289 -- error message.
290 --
291 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
292 fnd_message.set_token('TABLE_NAME', 'ame_conditions');
293 fnd_message.raise_error;
294 End lck;
295 --
296 -- ----------------------------------------------------------------------------
297 -- |-----------------------------< convert_args >-----------------------------|
298 -- ----------------------------------------------------------------------------
299 Function convert_args
300 (p_condition_id in number
301 ,p_condition_type in varchar2
302 ,p_attribute_id in number
303 ,p_parameter_one in varchar2
304 ,p_parameter_two in varchar2
305 ,p_parameter_three in varchar2
306 ,p_include_lower_limit in varchar2
307 ,p_include_upper_limit in varchar2
308 ,p_start_date in date
309 ,p_end_date in date
310 ,p_security_group_id in number
311 ,p_condition_key in varchar2
312 ,p_object_version_number in number
313 )
314 Return g_rec_type is
315 --
316 l_rec g_rec_type;
317 --
318 Begin
319 --
320 -- Convert arguments into local l_rec structure.
321 --
322 l_rec.condition_id := p_condition_id;
323 l_rec.condition_type := p_condition_type;
324 l_rec.attribute_id := p_attribute_id;
325 l_rec.parameter_one := p_parameter_one;
326 l_rec.parameter_two := p_parameter_two;
327 l_rec.parameter_three := p_parameter_three;
328 l_rec.include_lower_limit := p_include_lower_limit;
329 l_rec.include_upper_limit := p_include_upper_limit;
330 l_rec.start_date := p_start_date;
331 l_rec.end_date := p_end_date;
332 l_rec.security_group_id := p_security_group_id;
333 l_rec.condition_key := p_condition_key;
334 l_rec.object_version_number := p_object_version_number;
335 --
336 -- Return the plsql record structure.
337 --
338 Return(l_rec);
339 --
340 End convert_args;
341 --
342 -- ----------------------------------------------------------------------------
343 -- |------------------------< get_object_version_number >----------------------|
344 -- ----------------------------------------------------------------------------
345 Function get_object_version_number
346 (p_condition_id in number
347 )
348 Return number is
349 --
350 l_ovn number;
351 --
352 Begin
353 --
354 -- get the next ovn
355 --
356 select nvl(max(t.object_version_number),0) + 1
357 into l_ovn
358 from ame_conditions t
359 where t.condition_id = p_condition_id;
360 --
361 -- Return the new object_version_number.
362 --
363 Return(l_ovn);
364 --
365 End get_object_version_number;
366 --
367 --
368 end ame_con_shd;