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