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