[Home] [Help]
PACKAGE BODY: APPS.AME_ATY_SHD
Source
1 Package Body ame_aty_shd as
2 /* $Header: amatyrhi.pkb 120.4 2005/11/22 03:14 santosin noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_aty_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_ACTION_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_action_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 action_type_id
49 ,name
50 ,procedure_name
51 ,start_date
52 ,end_date
53 ,description
54 ,security_group_id
55 ,dynamic_description
56 ,description_query
57 ,object_version_number
58 from ame_action_types
59 where action_type_id = p_action_type_id
60 and p_effective_date
61 between start_date and nvl(end_date - (ame_util.oneSecond), sysdate);
62 --
63 l_fct_ret boolean;
64 --
65 Begin
66 --
67 If (p_effective_date is null or
68 p_action_type_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_action_type_id =
77 ame_aty_shd.g_old_rec.action_type_id and
78 p_object_version_number =
79 ame_aty_shd.g_old_rec.object_version_number
80 ) Then
81 --
82 -- The g_old_rec is current therefore we must
83 -- set the returning function to true
84 --
85 l_fct_ret := true;
86 Else
87 --
88 -- Select the current row
89 --
90 Open C_Sel1;
91 Fetch C_Sel1 Into ame_aty_shd.g_old_rec;
92 If C_Sel1%notfound Then
93 Close C_Sel1;
94 --
95 -- The primary key is invalid therefore we must error
96 --
97 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
98 fnd_message.raise_error;
99 End If;
100 Close C_Sel1;
101 If (p_object_version_number
102 <> ame_aty_shd.g_old_rec.object_version_number) Then
103 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
104 fnd_message.raise_error;
105 End If;
106 l_fct_ret := true;
107 End If;
108 End If;
109 Return (l_fct_ret);
110 --
111 End api_updating;
112 --
113 -- ----------------------------------------------------------------------------
114 -- |-----------------------< upd_end_date >-------------------------|
115 -- ----------------------------------------------------------------------------
116 Procedure upd_end_date
117 (p_effective_date in date
118 ,p_action_type_id in number
119 ,p_new_end_date in date
120 ,p_validation_start_date in date
121 ,p_validation_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 -- Because we are updating a row we must get the next object
133 -- version number.
134 --
135 l_object_version_number :=
136 ame_aty_shd.get_object_version_number
137 (p_action_type_id => p_action_type_id
138 );
139 --
140 hr_utility.set_location(l_proc, 10);
141 --
142 l_current_user_id := fnd_global.user_id;
143 --
144 -- Update the specified datetrack row setting the effective
145 -- end date to the specified new effective end date.
146 --
147 update ame_action_types 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.action_type_id = p_action_type_id
154 and p_effective_date between t.start_date
155 and nvl(t.end_date - ame_util.oneSecond,sysdate);
156 --
157 --
158 p_object_version_number := l_object_version_number;
159 hr_utility.set_location(' Leaving:'||l_proc, 15);
160 --
161 End upd_end_date;
162 --
163 -- ----------------------------------------------------------------------------
164 -- |---------------------------------< lck >----------------------------------|
165 -- ----------------------------------------------------------------------------
166 Procedure lck
167 (p_effective_date in date
168 ,p_datetrack_mode in varchar2
169 ,p_action_type_id in number
170 ,p_object_version_number in number
171 ,p_validation_start_date out nocopy date
172 ,p_validation_end_date out nocopy date
173 ) is
174 --
175 l_proc varchar2(72) := g_package||'lck';
176 l_validation_start_date date;
177 l_validation_end_date date;
178 l_argument varchar2(30);
179 --
180 -- Cursor C_Sel1 selects the current locked row as of session date
181 -- ensuring that the object version numbers match.
182 --
183 Cursor C_Sel1 is
184 select
185 action_type_id
186 ,name
187 ,procedure_name
188 ,start_date
189 ,end_date
190 ,description
191 ,security_group_id
192 ,dynamic_description
193 ,description_query
194 ,object_version_number
195 from ame_action_types
196 where action_type_id = p_action_type_id
197 and p_effective_date
198 between start_date and nvl(end_date - ame_util.oneSecond, sysdate)
199 for update nowait;
200 --
201 --
202 --
203 Begin
204 hr_utility.set_location('Entering:'||l_proc, 5);
205 --
206 -- Ensure that all the mandatory arguments are not null
207 --
208 hr_api.mandatory_arg_error(p_api_name => l_proc
209 ,p_argument => 'effective_date'
210 ,p_argument_value => p_effective_date
211 );
212 --
213 hr_api.mandatory_arg_error(p_api_name => l_proc
214 ,p_argument => 'datetrack_mode'
215 ,p_argument_value => p_datetrack_mode
216 );
217 --
218 hr_api.mandatory_arg_error(p_api_name => l_proc
219 ,p_argument => 'action_type_id'
220 ,p_argument_value => p_action_type_id
221 );
222 --
223 hr_api.mandatory_arg_error(p_api_name => l_proc
224 ,p_argument => 'object_version_number'
225 ,p_argument_value => p_object_version_number
226 );
227 --
228 -- Check to ensure the datetrack mode is not INSERT.
229 --
230 If (p_datetrack_mode <> hr_api.g_insert) then
231 --
232 -- We must select and lock the current row.
233 --
234 Open C_Sel1;
235 Fetch C_Sel1 Into ame_aty_shd.g_old_rec;
236 If C_Sel1%notfound then
237 Close C_Sel1;
238 --
239 -- The primary key is invalid therefore we must error
240 --
241 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
242 fnd_message.raise_error;
243 End If;
244 Close C_Sel1;
245 If (p_object_version_number
246 <> ame_aty_shd.g_old_rec.object_version_number) Then
247 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
248 fnd_message.raise_error;
249 End If;
250 --
251 --
252 -- Validate the datetrack mode mode getting the validation start
253 -- and end dates for the specified datetrack operation.
254 --
255 /*dt_api.validate_dt_mode
256 (p_effective_date => p_effective_date
257 ,p_datetrack_mode => p_datetrack_mode
258 ,p_base_table_name => 'ame_action_types'
259 ,p_base_key_column => 'action_type_id'
260 ,p_base_key_value => p_action_type_id
261 ,p_child_table_name1 => 'ame_actions'
262 ,p_child_key_column1 => 'action_type_id'
263 ,p_child_alt_base_key_column1 => 'EDIT HERE: Add base key column for
264 child table'
265 ,p_enforce_foreign_locking => true
266 ,p_validation_start_date => l_validation_start_date
267 ,p_validation_end_date => l_validation_end_date
268 );*/
269 Else
270 --
271 -- We are doing a datetrack 'INSERT' which is illegal within this
272 -- procedure therefore we must error (note: to lck on insert the
273 -- private procedure ins_lck should be called).
274 --
275 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
276 fnd_message.set_token('PROCEDURE', l_proc);
277 fnd_message.set_token('STEP','20');
278 fnd_message.raise_error;
279 End If;
280 --
281 -- Set the validation start and end date OUT arguments
282 --
283 -- p_validation_start_date := l_validation_start_date;
284 -- p_validation_end_date := l_validation_end_date;
285 -- MURTHY_CHANGES
286 if (p_datetrack_mode = hr_api.g_update) then
287 p_validation_start_date := p_effective_date;
288 p_validation_end_date := ame_aty_shd.g_old_rec.end_date;
289 elsif (p_datetrack_mode = hr_api.g_delete) then
290 p_validation_start_date := p_effective_date;
291 p_validation_end_date := p_effective_date;
292 end if;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc, 30);
295 --
296 -- We need to trap the ORA LOCK exception
297 --
298 Exception
299 When HR_Api.Object_Locked then
300 --
301 -- The object is locked therefore we need to supply a meaningful
302 -- error message.
303 --
304 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
305 fnd_message.set_token('TABLE_NAME', 'ame_action_types');
306 fnd_message.raise_error;
307 End lck;
308 --
309 -- ----------------------------------------------------------------------------
310 -- |-----------------------------< convert_args >-----------------------------|
311 -- ----------------------------------------------------------------------------
312 Function convert_args
313 (p_action_type_id in number
314 ,p_name in varchar2
315 ,p_procedure_name in varchar2
316 ,p_start_date in date
317 ,p_end_date in date
318 ,p_description in varchar2
319 ,p_security_group_id in number
320 ,p_dynamic_description in varchar2
321 ,p_description_query in varchar2
322 ,p_object_version_number in number
323 )
324 Return g_rec_type is
325 --
326 l_rec g_rec_type;
327 --
328 Begin
329 --
330 -- Convert arguments into local l_rec structure.
331 --
332 l_rec.action_type_id := p_action_type_id;
333 l_rec.name := p_name;
334 l_rec.procedure_name := p_procedure_name;
335 l_rec.start_date := p_start_date;
336 l_rec.end_date := p_end_date;
337 l_rec.description := p_description;
338 l_rec.security_group_id := p_security_group_id;
339 l_rec.dynamic_description := p_dynamic_description;
340 l_rec.description_query := p_description_query;
341 l_rec.object_version_number := p_object_version_number;
342 --
343 -- Return the plsql record structure.
344 --
345 Return(l_rec);
346 --
347 End convert_args;
348 --
349 -- ----------------------------------------------------------------------------
350 -- |------------------------< get_object_version_number >----------------------|
351 -- ----------------------------------------------------------------------------
352 Function get_object_version_number
353 (p_action_type_id in number
354 )
355 Return number is
356 --
357 l_ovn number;
358 --
359 Begin
360 --
361 -- get the next ovn
362 --
363 select nvl(max(t.object_version_number),0) + 1
364 into l_ovn
365 from ame_action_types t
366 where t.action_type_id = p_action_type_id;
367 --
368 -- Return the new object_version_number.
369 --
370 Return(l_ovn);
371 --
372 End get_object_version_number;
373 --
374 -- ----------------------------------------------------------------------------
375 -- |-----------------------------< child_rows_exist >-------------------------|
376 -- ----------------------------------------------------------------------------
377 Procedure child_rows_exist
378 (p_action_type_id in number
379 ,p_start_date in date
380 ,p_end_date in date
381 ) is
382 --
383 Cursor C_Sel1 is
384 select count(*)
385 from ame_actions
386 where action_type_id = p_action_type_id and
387 p_start_date between start_date and
388 nvl(end_date - ame_util.oneSecond, p_start_date);
389 --
390 Cursor C_Sel2 is
391 select count(*)
392 from ame_action_type_usages
393 where action_type_id = p_action_type_id and
394 p_start_date between start_date and
395 nvl(end_date - ame_util.oneSecond, p_start_date);
396 --
397 Cursor C_Sel3 is
398 select count(*)
399 from ame_approver_type_usages
400 where action_type_id = p_action_type_id and
401 p_start_date between start_date and
402 nvl(end_date - ame_util.oneSecond, p_start_date);
403 --
404 Cursor C_Sel4 is
405 select count(*)
406 from ame_mandatory_attributes
407 where action_type_id = p_action_type_id and
408 p_start_date between start_date and
409 nvl(end_date - ame_util.oneSecond, p_start_date);
410 --
411 Cursor C_Sel5 is
412 select count(*)
413 from ame_action_type_config
414 where action_type_id = p_action_type_id and
415 p_start_date between start_date and
416 nvl(end_date - ame_util.oneSecond, p_start_date);
417 l_child_count integer;
418 --
419 Begin
420 --
421 --
422 -- ame_actions
423 Open C_Sel1;
424 Fetch C_Sel1 into l_child_count;
425 Close C_Sel1;
426 If l_child_count > 0 then
427 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
428 fnd_message.set_token('TABLE_NAME','ame_actions');
429 hr_multi_message.add;
430 End If;
431 -- ame_action_type_usages
432 Open C_Sel2;
433 Fetch C_Sel2 into l_child_count;
434 Close C_Sel2;
435 If l_child_count > 0 then
436 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
437 fnd_message.set_token('TABLE_NAME','ame_action_type_usages');
438 hr_multi_message.add;
439 End If;
440 -- ame_approver_type_usages
441 Open C_Sel3;
442 Fetch C_Sel3 into l_child_count;
443 Close C_Sel3;
444 If l_child_count > 0 then
445 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
446 fnd_message.set_token('TABLE_NAME','ame_approver_type_usages');
447 hr_multi_message.add;
448 End If;
449 -- ame_mandatory_attributes
450 Open C_Sel4;
451 Fetch C_Sel4 into l_child_count;
452 Close C_Sel4;
453 If l_child_count > 0 then
454 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
455 fnd_message.set_token('TABLE_NAME','ame_mandatory_attributes');
456 hr_multi_message.add;
457 End If;
458 -- ame_action_type_config
459 Open C_Sel5;
460 Fetch C_Sel5 into l_child_count;
461 Close C_Sel5;
462 If l_child_count > 0 then
463 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
464 fnd_message.set_token('TABLE_NAME','ame_action_type_config');
465 hr_multi_message.add;
466 End If;
467 --
468 End child_rows_exist;
469 --
470 end ame_aty_shd;