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