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