[Home] [Help]
PACKAGE BODY: APPS.AME_APT_SHD
Source
1 Package Body ame_apt_shd as
2 /* $Header: amaptrhi.pkb 120.1 2006/04/21 08:44 avarri noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := 'ame_apt_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_APPROVER_TYPES_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_approver_type_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 approver_type_id
49 ,orig_system
50 ,query_variable_1_label
51 ,query_variable_2_label
52 ,query_variable_3_label
53 ,query_variable_4_label
54 ,query_variable_5_label
55 ,variable_1_lov_query
56 ,variable_2_lov_query
57 ,variable_3_lov_query
58 ,variable_4_lov_query
59 ,variable_5_lov_query
60 ,query_procedure
61 ,start_date
62 ,end_date
63 ,object_version_number
64 from ame_approver_types
65 where approver_type_id = p_approver_type_id
66 and p_effective_date between start_date
67 and nvl(end_date - (1/86400),p_effective_date);
68 --
69 l_fct_ret boolean;
70 --
71 Begin
72 --
73 If (p_effective_date is null or
74 p_approver_type_id is null or
75 p_object_version_number is null) Then
76 --
77 -- One of the primary key arguments is null therefore we must
78 -- set the returning function value to false
79 --
80 l_fct_ret := false;
81 Else
82 If (p_approver_type_id =
83 ame_apt_shd.g_old_rec.approver_type_id and
84 p_object_version_number =
85 ame_apt_shd.g_old_rec.object_version_number)
86 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_apt_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_apt_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 -- |-----------------------< upd_effective_end_date >-------------------------|
121 -- ----------------------------------------------------------------------------
122 Procedure upd_effective_end_date
123 (p_effective_date in date
124 ,p_approver_type_id in number
125 ,p_new_effective_end_date in date
126 ,p_validation_start_date in date
127 ,p_validation_end_date in date
128 ,p_object_version_number out nocopy number
129 ) is
130 --
131 l_proc varchar2(72) := g_package||'upd_effective_end_date';
132 l_current_user_id integer;
133 l_object_version_number number;
134 --
135 Begin
136 hr_utility.set_location('Entering:'||l_proc, 5);
137 --
138 -- Because we are updating a row we must get the next object
139 -- version number.
140 --
141 l_object_version_number :=
142 ame_apt_shd.get_object_version_number
146 hr_utility.set_location(l_proc, 10);
143 (p_approver_type_id => p_approver_type_id
144 );
145 --
147 --
148 l_current_user_id := fnd_global.user_id;
149 --
150 -- Update the specified datetrack row setting the effective
151 -- end date to the specified new effective end date.
152 --
153 update ame_approver_types t
154 set t.end_date = p_new_effective_end_date
155 ,t.last_updated_by = l_current_user_id
156 ,t.last_update_date = p_new_effective_end_date
157 ,t.last_update_login = l_current_user_id
158 ,t.object_version_number = l_object_version_number
159 where t.approver_type_id = p_approver_type_id
160 and p_effective_date between t.start_date
161 and nvl(t.end_date - (1/86400),sysdate);
162 --
163 --
164 p_object_version_number := l_object_version_number;
165 hr_utility.set_location(' Leaving:'||l_proc, 15);
166 --
167 End upd_effective_end_date;
168 --
169 -- ----------------------------------------------------------------------------
170 -- |---------------------------------< lck >----------------------------------|
171 -- ----------------------------------------------------------------------------
172 Procedure lck
173 (p_effective_date in date
174 ,p_datetrack_mode in varchar2
175 ,p_approver_type_id in number
176 ,p_orig_system in varchar2
177 ,p_object_version_number in number
178 ,p_validation_start_date out nocopy date
179 ,p_validation_end_date out nocopy date
180 ) is
181 --
182 l_proc varchar2(72) := g_package||'lck';
183 l_validation_start_date date;
184 l_validation_end_date date;
188 -- ensuring that the object version numbers match.
185 l_argument varchar2(30);
186 --
187 -- Cursor C_Sel1 selects the current locked row as of session date
189 --
190 Cursor C_Sel1 is
191 select
192 approver_type_id
193 ,orig_system
194 ,query_variable_1_label
195 ,query_variable_2_label
196 ,query_variable_3_label
197 ,query_variable_4_label
198 ,query_variable_5_label
199 ,variable_1_lov_query
203 ,variable_5_lov_query
200 ,variable_2_lov_query
201 ,variable_3_lov_query
202 ,variable_4_lov_query
204 ,query_procedure
205 ,start_date
206 ,end_date
207 ,object_version_number
208 from ame_approver_types
209 where approver_type_id = p_approver_type_id
210 and p_effective_date between start_date
211 and nvl(end_date - (1/86400), sysdate)
212 for update nowait;
213 --
214 --
215 --
216 Begin
217 hr_utility.set_location('Entering:'||l_proc, 5);
218 --
219 -- Ensure that all the mandatory arguments are not null
220 --
221
222 hr_api.mandatory_arg_error(p_api_name => l_proc
223 ,p_argument => 'effective_date'
224 ,p_argument_value => p_effective_date
225 );
226 --
227 hr_api.mandatory_arg_error(p_api_name => l_proc
231 --
228 ,p_argument => 'datetrack_mode'
229 ,p_argument_value => p_datetrack_mode
230 );
232 hr_api.mandatory_arg_error(p_api_name => l_proc
233 ,p_argument => 'approver_type_id'
234 ,p_argument_value => p_approver_type_id
235 );
236 --
237 hr_api.mandatory_arg_error(p_api_name => l_proc
238 ,p_argument => 'object_version_number'
239 ,p_argument_value => p_object_version_number
240 );
241 --
242 -- Check to ensure the datetrack mode is not INSERT.
243 --
244 If (p_datetrack_mode <> hr_api.g_insert) then
245 --
246 -- We must select and lock the current row.
247 --
248 Open C_Sel1;
249 Fetch C_Sel1 Into ame_apt_shd.g_old_rec;
250 If C_Sel1%notfound then
251 Close C_Sel1;
252 --
253 -- The primary key is invalid therefore we must error
254 --
255 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
256 fnd_message.raise_error;
257 End If;
258 Close C_Sel1;
259 If (p_object_version_number
260 <> ame_apt_shd.g_old_rec.object_version_number) Then
261 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
262 fnd_message.raise_error;
263 End If;
264 --
265 --
266 --
267 Else
268 --
269 -- We are doing a datetrack 'INSERT' which is illegal within this
270 -- procedure therefore we must error (note: to lck on insert the
274 fnd_message.set_token('PROCEDURE', l_proc);
271 -- private procedure ins_lck should be called).
272 --
273 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
275 fnd_message.set_token('STEP','20');
276 fnd_message.raise_error;
277 End If;
278 --
279 -- Set the validation start and end date OUT arguments
280 --
281 if (p_datetrack_mode = hr_api.g_update) then
282 p_validation_start_date := p_effective_date;
283 p_validation_end_date := ame_apt_shd.g_old_rec.end_date;
284 elsif (p_datetrack_mode = hr_api.g_delete) then
285 p_validation_start_date := p_effective_date;
286 p_validation_end_date := p_effective_date;
287 end if;
288 --
289 hr_utility.set_location(' Leaving:'||l_proc, 30);
290 --
291 -- We need to trap the ORA LOCK exception
292 --
293 Exception
294 When HR_Api.Object_Locked then
295 --
296 -- The object is locked therefore we need to supply a meaningful
297 -- error message.
298 --
299 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
300 fnd_message.set_token('TABLE_NAME', 'ame_approver_types');
301 fnd_message.raise_error;
302 End lck;
303 --
304 -- ----------------------------------------------------------------------------
305 -- |-----------------------------< convert_args >-----------------------------|
306 -- ----------------------------------------------------------------------------
307 Function convert_args
308 (p_approver_type_id in number
309 ,p_orig_system in varchar2
310 ,p_query_variable_1_label in varchar2
311 ,p_query_variable_2_label in varchar2
312 ,p_query_variable_3_label in varchar2
313 ,p_query_variable_4_label in varchar2
314 ,p_query_variable_5_label in varchar2
315 ,p_variable_1_lov_query in varchar2
316 ,p_variable_2_lov_query in varchar2
317 ,p_variable_3_lov_query in varchar2
318 ,p_variable_4_lov_query in varchar2
319 ,p_variable_5_lov_query in varchar2
320 ,p_query_procedure in varchar2
321 ,p_start_date in date
322 ,p_end_date in date
323 ,p_object_version_number in number
324 )
325 Return g_rec_type is
329 Begin
326 --
327 l_rec g_rec_type;
328 --
330 --
331 -- Convert arguments into local l_rec structure.
332 --
333 l_rec.approver_type_id := p_approver_type_id;
334 l_rec.orig_system := p_orig_system;
335 l_rec.start_date := p_start_date;
336 l_rec.end_date := p_end_date;
337 l_rec.object_version_number := p_object_version_number;
338 --
339 -- Return the plsql record structure.
340 --
341 Return(l_rec);
342 --
343 End convert_args;
344 --
345 -- ----------------------------------------------------------------------------
349 (p_approver_type_id in number
346 -- |------------------------< get_object_version_number >---------------------|
347 -- ----------------------------------------------------------------------------
348 Function get_object_version_number
350 )
351 Return number is
352 --
353 l_ovn number;
354 --
355 Begin
356 --
357 -- get the next ovn
358 --
359 select nvl(max(t.object_version_number),0) + 1
360 into l_ovn
361 from ame_approver_types t
362 where t.approver_type_id = p_approver_type_id;
363 --
364 -- Return the new object_version_number.
365 --
366 Return(l_ovn);
367 --
368 End get_object_version_number;
369 --
370 /*
371 -- ----------------------------------------------------------------------------
372 -- |-----------------------------< child_rows_exist >-------------------------|
373 -- ----------------------------------------------------------------------------
374 Procedure child_rows_exist
375 (p_approver_type_id in number
376 ,p_orig_system in varchar2
377 ,p_start_date in date
378 ,p_end_date in date
379 ) is
380
381 l_child_count integer;
382 --
383 Begin
384 --
385 --
386 null;
387 End child_rows_exist;
388 --
389 */
390 --
391 end ame_apt_shd;