1 Package Body ame_itu_shd as
2 /* $Header: amiturhi.pkb 120.6 2006/10/05 16:11:40 pvelugul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_itu_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_ITEM_CLASS_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_application_id in number
41 ,p_item_class_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 application_id
50 ,item_class_id
51 ,item_id_query
52 ,item_class_order_number
53 ,item_class_par_mode
54 ,item_class_sublist_mode
55 ,start_date
56 ,end_date
57 ,object_version_number
58 from ame_item_class_usages
59 where item_class_id = p_item_class_id
60 and application_id = p_application_id
61 and p_effective_date
62 between start_date and nvl(end_date - (1/86400),p_effective_date);
63 --
64 l_fct_ret boolean;
65 --
66 Begin
67 --
68 If (p_effective_date is null or
69 p_item_class_id is null or
70 p_application_id is null or
71 p_object_version_number is null) Then
72 --
73 -- One of the primary key arguments is null therefore we must
74 -- set the returning function value to false
75 --
76 l_fct_ret := false;
77 Else
78 If (p_item_class_id =
79 ame_itu_shd.g_old_rec.item_class_id and
80 p_application_id =
81 ame_itu_shd.g_old_rec.application_id and
82 p_object_version_number =
83 ame_itu_shd.g_old_rec.object_version_number
84 ) Then
85 --
86 -- The g_old_rec is current therefore we must
87 -- set the returning function to true
88 --
89 l_fct_ret := true;
90 Else
91 --
92 -- Select the current row
93 --
94 Open C_Sel1;
95 Fetch C_Sel1 Into ame_itu_shd.g_old_rec;
96 If C_Sel1%notfound Then
97 Close C_Sel1;
98 --
99 -- The primary key is invalid therefore we must error
100 --
101 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
102 fnd_message.raise_error;
103 End If;
104 Close C_Sel1;
105 If (p_object_version_number
106 <> ame_itu_shd.g_old_rec.object_version_number) Then
107 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
108 fnd_message.raise_error;
109 End If;
110 l_fct_ret := true;
111 End If;
112 End If;
113 Return (l_fct_ret);
114 --
115 End api_updating;
116 --
117 -- ----------------------------------------------------------------------------
118 -- |-----------------------< upd_effective_end_date >-------------------------|
119 -- ----------------------------------------------------------------------------
120 Procedure upd_effective_end_date
121 (p_effective_date in date
122 ,p_application_id in number
123 ,p_item_class_id in number
124 ,p_new_effective_end_date in date
125 ,p_validation_start_date in date
126 ,p_validation_end_date in date
127 ,p_object_version_number out nocopy number
128 ) is
129 --
130 l_proc varchar2(72) := g_package||'upd_effective_end_date';
131 l_current_user_id integer;
132 l_object_version_number number;
133 --
134 Begin
135 hr_utility.set_location('Entering:'||l_proc, 5);
136 --
137 -- Because we are updating a row we must get the next object
138 -- version number.
139 --
140 l_object_version_number :=
141 ame_itu_shd.get_object_version_number
142 (p_item_class_id => p_item_class_id
143 ,p_application_id => p_application_id
144 );
145 --
146 hr_utility.set_location(l_proc, 10);
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_item_class_usages 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.item_class_id = p_item_class_id
160 and t.application_id = p_application_id
161 and p_effective_date between t.start_date
162 and nvl(t.end_date - (1/86400),sysdate);
163 --
164 --
165 p_object_version_number := l_object_version_number;
166 hr_utility.set_location(' Leaving:'||l_proc, 15);
167 --
168 End upd_effective_end_date;
169 --
170 -- ----------------------------------------------------------------------------
171 -- |---------------------------------< lck >----------------------------------|
172 -- ----------------------------------------------------------------------------
173 Procedure lck
174 (p_effective_date in date
175 ,p_datetrack_mode in varchar2
176 ,p_application_id in number
177 ,p_item_class_id in number
178 ,p_object_version_number in number
179 ,p_validation_start_date out nocopy date
180 ,p_validation_end_date out nocopy date
181 ) is
182 --
183 l_proc varchar2(72) := g_package||'lck';
184 l_validation_start_date date;
185 l_validation_end_date date;
186 l_argument varchar2(30);
187 --
188 -- Cursor C_Sel1 selects the current locked row as of session date
189 -- ensuring that the object version numbers match.
190 --
191 Cursor C_Sel1 is
192 select
193 application_id
194 ,item_class_id
195 ,item_id_query
196 ,item_class_order_number
197 ,item_class_par_mode
198 ,item_class_sublist_mode
199 ,start_date
200 ,end_date
201 ,object_version_number
202 from ame_item_class_usages
203 where item_class_id = p_item_class_id
204 and application_id = p_application_id
205 and p_effective_date
206 between start_date and nvl(end_date - (1/86400), sysdate)
207 for update nowait;
208 --
209 --
210 --
211 Begin
212 hr_utility.set_location('Entering:'||l_proc, 5);
213 --
214 -- Ensure that all the mandatory arguments are not null
215 --
216 hr_api.mandatory_arg_error(p_api_name => l_proc
217 ,p_argument => 'effective_date'
218 ,p_argument_value => p_effective_date
219 );
220 --
221 hr_api.mandatory_arg_error(p_api_name => l_proc
222 ,p_argument => 'datetrack_mode'
223 ,p_argument_value => p_datetrack_mode
224 );
225 --
226 hr_api.mandatory_arg_error(p_api_name => l_proc
227 ,p_argument => 'item_class_id'
228 ,p_argument_value => p_item_class_id
229 );
230 --
231 hr_api.mandatory_arg_error(p_api_name => l_proc
232 ,p_argument => 'object_version_number'
233 ,p_argument_value => p_object_version_number
234 );
235 --
236 -- Check to ensure the datetrack mode is not INSERT.
237 --
238 If (p_datetrack_mode <> hr_api.g_insert) then
239 --
240 -- We must select and lock the current row.
241 --
242 Open C_Sel1;
243 Fetch C_Sel1 Into ame_itu_shd.g_old_rec;
244 If C_Sel1%notfound then
245 Close C_Sel1;
246 --
247 -- The primary key is invalid therefore we must error
248 --
249 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
250 fnd_message.raise_error;
251 End If;
252 Close C_Sel1;
253 If (p_object_version_number
254 <> ame_itu_shd.g_old_rec.object_version_number) Then
255 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
256 fnd_message.raise_error;
257 End If;
258 --
259 Else
260 --
261 -- We are doing a datetrack 'INSERT' which is illegal within this
262 -- procedure therefore we must error (note: to lck on insert the
263 -- private procedure ins_lck should be called).
264 --
265 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
266 fnd_message.set_token('PROCEDURE', l_proc);
267 fnd_message.set_token('STEP','20');
268 fnd_message.raise_error;
269 End If;
270 --
271 -- Set the validation start and end date OUT arguments
272 --
273 if (p_datetrack_mode = hr_api.g_update) then
274 p_validation_start_date := p_effective_date;
275 p_validation_end_date := ame_itu_shd.g_old_rec.end_date;
276 elsif (p_datetrack_mode = hr_api.g_delete) then
277 p_validation_start_date := p_effective_date;
278 p_validation_end_date := p_effective_date;
279 end if;
280 --
281 hr_utility.set_location(' Leaving:'||l_proc, 30);
282 --
283 -- We need to trap the ORA LOCK exception
284 --
285 Exception
286 When HR_Api.Object_Locked then
287 --
288 -- The object is locked therefore we need to supply a meaningful
289 -- error message.
290 --
291 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
292 fnd_message.set_token('TABLE_NAME', 'ame_item_class_usages');
293 fnd_message.raise_error;
294 End lck;
295 --
296 -- ----------------------------------------------------------------------------
297 -- |-----------------------------< convert_args >-----------------------------|
298 -- ----------------------------------------------------------------------------
299 Function convert_args
300 (p_application_id in number
301 ,p_item_class_id in number
302 ,p_item_id_query in varchar2
303 ,p_item_class_order_number in number
304 ,p_item_class_par_mode in varchar2
305 ,p_item_class_sublist_mode in varchar2
306 ,p_start_date in date
307 ,p_end_date in date
308 ,p_object_version_number in number
309 )
310 Return g_rec_type is
311 --
312 l_rec g_rec_type;
313 --
314 Begin
315 --
316 -- Convert arguments into local l_rec structure.
317 --
318 l_rec.application_id := p_application_id;
319 l_rec.item_class_id := p_item_class_id;
320 l_rec.item_id_query := p_item_id_query;
321 l_rec.item_class_order_number := p_item_class_order_number;
322 l_rec.item_class_par_mode := p_item_class_par_mode;
323 l_rec.item_class_sublist_mode := p_item_class_sublist_mode;
324 l_rec.start_date := p_start_date;
325 l_rec.end_date := p_end_date;
326 l_rec.object_version_number := p_object_version_number;
327 --
328 -- Return the plsql record structure.
329 --
330 Return(l_rec);
331 --
332 End convert_args;
333 --
334 -- ----------------------------------------------------------------------------
335 -- |------------------------< get_object_version_number >----------------------|
336 -- ----------------------------------------------------------------------------
337 Function get_object_version_number
338 (p_application_id in number
339 ,p_item_class_id in number
340 )
341 Return number is
342 --
343 l_ovn number;
344 --
345 Begin
346 --
347 -- get the next ovn
348 --
349 select nvl(max(t.object_version_number),0) + 1
350 into l_ovn
351 from ame_item_class_usages t
352 where t.item_class_id = p_item_class_id
353 and t.application_id = p_application_id;
354 --
355 -- Return the new object_version_number.
356 --
357 Return(l_ovn);
358 --
359 End get_object_version_number;
360 --
361 --
362 -- ----------------------------------------------------------------------------
363 -- |-----------------------------< child_rows_exist >-------------------------|
364 -- ----------------------------------------------------------------------------
365 Procedure child_rows_exist
366 (p_item_class_id in number
367 ,p_application_id in number
368 ,p_start_date in date
369 ,p_end_date in date
370 ) is
371 --
372 cursor csr_in_use_by_attribute_usages is
373 select count(*)
374 from
375 ame_attribute_usages attru,
376 ame_attributes attr
377 where attru.attribute_id = attr.attribute_id
378 and attru.application_id = p_application_id
379 and attr.item_class_id = p_item_class_id
380 and p_start_date BETWEEN attru.start_date
381 and nvl(attru.end_date -(1/86400),p_start_date)
382 and p_start_date BETWEEN attr.start_date
383 and nvl(attr.end_date - (1/86400),p_start_date);
384 --
385 cursor csr_in_use_by_rule_usages is
386 select count(*)
387 from
388 ame_rule_usages rlu,
389 ame_rules rl
390 where rlu.rule_id = rl.rule_id
391 and rlu.item_id = p_application_id
392 and rl.item_class_id = p_item_class_id
393 and ((p_start_date between rl.start_date and nvl(rl.end_date - ame_util.oneSecond, p_start_date)) or
394 (p_start_date < rl.start_date and rl.start_date < nvl(rl.end_date,rl.start_date + ame_util.oneSecond)))
395 and ((p_start_date between rlu.start_date and nvl(rlu.end_date - ame_util.oneSecond, p_start_date)) or
396 (p_start_date < rlu.start_date and rlu.start_date < nvl(rlu.end_date,rlu.start_date + ame_util.oneSecond)));
397 --
398 l_attribute_count integer;
399 l_rule_count integer;
400 --
401 Begin
402 --
403 --
404 --
405 open csr_in_use_by_attribute_usages;
406 fetch csr_in_use_by_attribute_usages
407 into l_attribute_count;
408 close csr_in_use_by_attribute_usages;
409 --
410 If l_attribute_count > 0 then
411 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
412 fnd_message.set_token('TABLE_NAME','ame_attribute_usages');
413 hr_multi_message.add;
414 End If;
415
416 open csr_in_use_by_rule_usages;
417 fetch csr_in_use_by_rule_usages
418 into l_rule_count;
419 close csr_in_use_by_rule_usages;
420 --
421 If l_rule_count > 0 then
422 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
423 fnd_message.set_token('TABLE_NAME','ame_rule_usages');
424 hr_multi_message.add;
425 End If;
426 --
427 --
428 End child_rows_exist;
429 --
430 end ame_itu_shd;
[Home] [Help]