[Home] [Help]
PACKAGE BODY: APPS.AME_RUL_SHD
Source
1 Package Body ame_rul_shd as
2 /* $Header: amrulrhi.pkb 120.6 2006/02/14 01:23 vboggava noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_rul_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_RULES_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_rule_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 rule_id
49 ,rule_type
50 ,action_id
51 ,start_date
52 ,end_date
53 ,description
54 ,security_group_id
55 ,rule_key
56 ,item_class_id
57 ,object_version_number
58 from ame_rules
59 where rule_id = p_rule_id
60 and ( p_effective_date
61 between start_date and nvl(end_date - ame_util.oneSecond,p_effective_date)
62 or
63 (p_effective_date < start_date and
64 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
65 --
66 l_fct_ret boolean;
67 --
68 Begin
69 --
70 If (p_effective_date is null or
71 p_rule_id is null or
72 p_object_version_number is null) Then
73 --
74 -- One of the primary key arguments is null therefore we must
75 -- set the returning function value to false
76 --
77 l_fct_ret := false;
78 Else
79 If (p_rule_id =
80 ame_rul_shd.g_old_rec.rule_id and
81 p_object_version_number =
82 ame_rul_shd.g_old_rec.object_version_number) Then
83 --
84 -- The g_old_rec is current therefore we must
85 -- set the returning function to true
86 --
87 l_fct_ret := true;
88 Else
89 --
90 -- Select the current row
91 --
92 Open C_Sel1;
93 Fetch C_Sel1 Into ame_rul_shd.g_old_rec;
94 If C_Sel1%notfound Then
95 Close C_Sel1;
96 --
97 -- The primary key is invalid therefore we must error
98 --
99 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
100 fnd_message.raise_error;
101 End If;
102 Close C_Sel1;
103 If (p_object_version_number
104 <> ame_rul_shd.g_old_rec.object_version_number) Then
105 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
106 fnd_message.raise_error;
107 End If;
108 l_fct_ret := true;
109 End If;
110 End If;
111 Return (l_fct_ret);
112 --
113 End api_updating;
114 --
115 -- ----------------------------------------------------------------------------
116 -- |----------------------------< upd_end_date >------------------------------|
117 -- ----------------------------------------------------------------------------
118 Procedure upd_end_date
119 (p_effective_date in date
120 ,p_rule_id in number
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_rul_shd.get_object_version_number
139 (p_rule_id => p_rule_id
140 );
141 --
142 hr_utility.set_location(l_proc, 10);
143 --
144 --
145 -- Update the specified datetrack row setting the effective
146 -- end date to the specified new effective end date. This query is modified to
147 -- account for rules with future rule dates
148 --
149 update ame_rules 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.rule_id = p_rule_id
156 and ( p_effective_date
157 between t.start_date and nvl(t.end_date - ame_util.oneSecond,p_effective_date)
158 or
159 (p_effective_date < t.start_date and
160 t.start_date < nvl(t.end_date,t.start_date + ame_util.oneSecond)))
161 ;
162 --
163 --
164 p_object_version_number := l_object_version_number;
165 hr_utility.set_location(' Leaving:'||l_proc, 15);
166 --
167 End upd_end_date;
168 --
169 -- ----------------------------------------------------------------------------
170 -- |---------------------------------< lck >----------------------------------|
171 -- ----------------------------------------------------------------------------
172 Procedure lck
173 (p_effective_date in date
174 ,p_datetrack_mode in varchar2
175 ,p_rule_id in number
176 ,p_object_version_number in number
177 ,p_validation_start_date out nocopy date
178 ,p_validation_end_date out nocopy date
179 ) is
180 --
181 l_proc varchar2(72) := g_package||'lck';
182 l_validation_start_date date;
183 l_validation_end_date date;
184 l_argument varchar2(30);
185 --
186 -- Cursor C_Sel1 selects the current locked row as of session date
187 -- ensuring that the object version numbers match.
188 --
189 Cursor C_Sel1 is
190 select
191 rule_id
192 ,rule_type
193 ,action_id
194 ,start_date
195 ,end_date
196 ,description
197 ,security_group_id
198 ,rule_key
199 ,item_class_id
200 ,object_version_number
201 from ame_rules
202 where rule_id = p_rule_id
203 and ( p_effective_date
204 between start_date and nvl(end_date - ame_util.oneSecond,p_effective_date)
205 or
206 (p_effective_date < start_date and
207 start_date < nvl(end_date,start_date + ame_util.oneSecond)))
208 for update 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 => 'rule_id'
229 ,p_argument_value => p_rule_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_rul_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_rul_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_rul_shd.g_old_rec.end_date;
279 elsif (p_datetrack_mode = hr_api.g_delete) then
280 p_validation_start_date := p_effective_date;
281 p_validation_end_date := p_effective_date;
282 end if;
283 --
284 hr_utility.set_location(' Leaving:'||l_proc, 30);
285 --
286 -- We need to trap the ORA LOCK exception
287 --
288 Exception
289 When HR_Api.Object_Locked then
290 --
291 -- The object is locked therefore we need to supply a meaningful
292 -- error message.
293 --
294 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
295 fnd_message.set_token('TABLE_NAME', 'ame_rules');
296 fnd_message.raise_error;
297 End lck;
298 --
299 -- ----------------------------------------------------------------------------
300 -- |-----------------------------< convert_args >-----------------------------|
301 -- ----------------------------------------------------------------------------
302 Function convert_args
303 (p_rule_id in number
304 ,p_rule_type in number
305 ,p_action_id in number
306 ,p_start_date in date
307 ,p_end_date in date
308 ,p_description in varchar2
309 ,p_security_group_id in number
310 ,p_rule_key in varchar2
311 ,p_item_class_id in number
312 ,p_object_version_number in number
313 )
314 Return g_rec_type is
315 --
316 l_rec g_rec_type;
317 --
318 Begin
319 --
320 -- Convert arguments into local l_rec structure.
321 --
322 l_rec.rule_id := p_rule_id;
323 l_rec.rule_type := p_rule_type;
324 l_rec.action_id := p_action_id;
325 l_rec.start_date := p_start_date;
326 l_rec.end_date := p_end_date;
327 l_rec.description := p_description;
328 l_rec.security_group_id := p_security_group_id;
329 l_rec.rule_key := p_rule_key;
330 l_rec.item_class_id := p_item_class_id;
331 l_rec.object_version_number := p_object_version_number;
332 --
333 -- Return the plsql record structure.
334 --
335 Return(l_rec);
336 --
337 End convert_args;
338 --
339 -- ----------------------------------------------------------------------------
340 -- |------------------------< get_object_version_number >----------------------|
341 -- ----------------------------------------------------------------------------
342 Function get_object_version_number
343 (p_rule_id in number
344 )
345 Return number is
346 --
347 l_ovn number;
348 --
349 Begin
350 --
351 -- get the next ovn
352 --
353 select nvl(max(t.object_version_number),0) + 1
354 into l_ovn
355 from ame_rules t
356 where t.rule_id = p_rule_id;
357 --
358 -- Return the new object_version_number.
359 --
360 Return(l_ovn);
361 --
362 End get_object_version_number;
363 --
364 -- ----------------------------------------------------------------------------
365 -- |-----------------------------< child_rows_exist >-------------------------|
366 -- ----------------------------------------------------------------------------
367 Procedure child_rows_exist
368 (p_rule_id in number
369 ,p_start_date in date
370 ,p_end_date in date
371 ) is
372 --
373 Cursor C_Sel1 is
374 select count(*)
375 from ame_rule_usages
376 where rule_id = p_rule_id
377 and p_start_date between start_date and
378 nvl(end_date - ame_util.oneSecond, p_start_date);
379 Cursor C_Sel2 is
380 select count(*)
381 from ame_action_usages
382 where rule_id = p_rule_id
383 and p_start_date between start_date and
384 nvl(end_date - ame_util.oneSecond, p_start_date);
385 Cursor C_Sel3 is
386 select count(*)
387 from ame_condition_usages
388 where rule_id = p_rule_id
389 and p_start_date between start_date and
390 nvl(end_date - ame_util.oneSecond, p_start_date);
391 --
392 --
393 l_child_count integer;
394 --
395 Begin
396 --
397 --
398 -- ame_rule_usages
399 Open C_Sel1;
400 Fetch C_Sel1 into l_child_count;
401 Close C_Sel1;
402 If l_child_count >0 then
403 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
404 fnd_message.set_token('TABLE_NAME','ame_rule_usages');
405 hr_multi_message.add;
406 End If;
407 --
408 -- ame_action_usages
409 Open C_Sel2;
410 Fetch C_Sel2 into l_child_count;
411 Close C_Sel2;
412 If l_child_count >0 then
413 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
414 fnd_message.set_token('TABLE_NAME','ame_action_usages');
415 hr_multi_message.add;
416 End If;
417 --
418 -- ame_condition_usages
419 Open C_Sel3;
420 Fetch C_Sel3 into l_child_count;
421 Close C_Sel3;
422 If l_child_count >0 then
423 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
424 fnd_message.set_token('TABLE_NAME','ame_condition_usages');
425 hr_multi_message.add;
426 End If;
427 --
428 --
429 --
430 End child_rows_exist;
431 --
432 end ame_rul_shd;