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