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