[Home] [Help]
PACKAGE BODY: APPS.BEN_EAT_SHD
Source
1 Package Body ben_eat_shd as
2 /* $Header: beeatrhi.pkb 115.11 2002/12/16 11:53:54 vsethi ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_eat_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 l_proc varchar2(72) := g_package||'return_api_dml_status';
16 --
17 Begin
18 hr_utility.set_location('Entering:'||l_proc, 5);
19 --
20 Return (nvl(g_api_dml, false));
21 --
22 hr_utility.set_location(' Leaving:'||l_proc, 10);
23 End return_api_dml_status;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |---------------------------< constraint_error >---------------------------|
27 -- ----------------------------------------------------------------------------
28 Procedure constraint_error
29 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
30 --
31 l_proc varchar2(72) := g_package||'constraint_error';
32 --
33 Begin
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 --
36 If (p_constraint_name = 'BEN_ACTN_TYP_PK') Then
37 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
38 hr_utility.set_message_token('PROCEDURE', l_proc);
39 hr_utility.set_message_token('STEP','5');
40 hr_utility.raise_error;
41 Elsif(p_constraint_name = 'BEN_CM_TYP_USG_F_FK1') Then
42 ben_utility.child_exists_error(p_table_name => 'BEN_CM_TYP_USG_F');
43 Elsif(p_constraint_name = 'BEN_POPL_ACTN_TYP_F_FK1') Then
44 ben_utility.child_exists_error(p_table_name => 'BEN_POPL_ACTN_TYP_F');
45 Elsif(p_constraint_name = 'BEN_PRTT_ENRT_ACTN_F_FK2') Then
46 ben_utility.child_exists_error(p_table_name => 'BEN_PRTT_ENRT_ACTN_F');
47 Else
48 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
49 hr_utility.set_message_token('PROCEDURE', l_proc);
50 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
51 hr_utility.raise_error;
52 End If;
53 --
54 hr_utility.set_location(' Leaving:'||l_proc, 10);
55 End constraint_error;
56 --
57 -- ----------------------------------------------------------------------------
58 -- |-----------------------------< api_updating >-----------------------------|
59 -- ----------------------------------------------------------------------------
60 Function api_updating
61 (
62 p_actn_typ_id in number,
63 p_object_version_number in number
64 ) Return Boolean Is
65 --
66 --
67 -- Cursor selects the 'current' row from the HR Schema
68 --
69 Cursor C_Sel1 is
70 select
71 actn_typ_id,
72 business_group_id,
73 type_cd,
74 name,
75 description,
76 eat_attribute_category,
77 eat_attribute1,
78 eat_attribute2,
79 eat_attribute3,
80 eat_attribute4,
81 eat_attribute5,
82 eat_attribute6,
83 eat_attribute7,
84 eat_attribute8,
85 eat_attribute9,
86 eat_attribute10,
87 eat_attribute11,
88 eat_attribute12,
89 eat_attribute13,
90 eat_attribute14,
91 eat_attribute15,
92 eat_attribute16,
93 eat_attribute17,
94 eat_attribute18,
95 eat_attribute19,
96 eat_attribute20,
97 eat_attribute21,
98 eat_attribute22,
99 eat_attribute23,
100 eat_attribute24,
101 eat_attribute25,
102 eat_attribute26,
103 eat_attribute27,
104 eat_attribute28,
105 eat_attribute29,
106 eat_attribute30,
107 object_version_number
108 from ben_actn_typ
109 where actn_typ_id = p_actn_typ_id;
110 --
111 l_proc varchar2(72) := g_package||'api_updating';
112 l_fct_ret boolean;
113 --
114 Begin
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 If (
118 p_actn_typ_id is null and
119 p_object_version_number is null
120 ) Then
121 --
122 -- One of the primary key arguments is null therefore we must
123 -- set the returning function value to false
124 --
125 l_fct_ret := false;
126 Else
127 If (
128 p_actn_typ_id = g_old_rec.actn_typ_id and
129 p_object_version_number = g_old_rec.object_version_number
130 ) Then
131 hr_utility.set_location(l_proc, 10);
132 --
133 -- The g_old_rec is current therefore we must
134 -- set the returning function to true
135 --
136 l_fct_ret := true;
137 Else
138 --
139 -- Select the current row into g_old_rec
140 --
141 Open C_Sel1;
142 Fetch C_Sel1 Into g_old_rec;
143 If C_Sel1%notfound Then
144 Close C_Sel1;
145 --
146 -- The primary key is invalid therefore we must error
147 --
148 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
149 hr_utility.raise_error;
150 End If;
151 Close C_Sel1;
152 If (p_object_version_number <> g_old_rec.object_version_number) Then
153 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
154 hr_utility.raise_error;
155 End If;
156 hr_utility.set_location(l_proc, 15);
157 l_fct_ret := true;
158 End If;
159 End If;
160 hr_utility.set_location(' Leaving:'||l_proc, 20);
161 Return (l_fct_ret);
162 --
163 End api_updating;
164 --
165 -- ----------------------------------------------------------------------------
166 -- |---------------------------------< lck >----------------------------------|
167 -- ----------------------------------------------------------------------------
168 Procedure lck
169 (
170 p_actn_typ_id in number,
171 p_object_version_number in number
172 ) is
173 --
174 -- Cursor selects the 'current' row from the HR Schema
175 --
176 Cursor C_Sel1 is
177 select actn_typ_id,
178 business_group_id,
179 type_cd,
180 name,
181 description,
182 eat_attribute_category,
183 eat_attribute1,
184 eat_attribute2,
185 eat_attribute3,
186 eat_attribute4,
187 eat_attribute5,
188 eat_attribute6,
189 eat_attribute7,
190 eat_attribute8,
191 eat_attribute9,
192 eat_attribute10,
193 eat_attribute11,
194 eat_attribute12,
195 eat_attribute13,
196 eat_attribute14,
197 eat_attribute15,
198 eat_attribute16,
199 eat_attribute17,
200 eat_attribute18,
201 eat_attribute19,
202 eat_attribute20,
203 eat_attribute21,
204 eat_attribute22,
205 eat_attribute23,
206 eat_attribute24,
207 eat_attribute25,
208 eat_attribute26,
209 eat_attribute27,
210 eat_attribute28,
211 eat_attribute29,
212 eat_attribute30,
213 object_version_number
214 from ben_actn_typ
215 where actn_typ_id = p_actn_typ_id
216 for update nowait;
217 --
218 l_proc varchar2(72) := g_package||'lck';
219 --
220 Begin
221 hr_utility.set_location('Entering:'||l_proc, 5);
222 --
223 -- Add any mandatory argument checking here:
224 -- Example:
225 -- hr_api.mandatory_arg_error
226 -- (p_api_name => l_proc,
227 -- p_argument => 'object_version_number',
228 -- p_argument_value => p_object_version_number);
229 --
230 Open C_Sel1;
231 Fetch C_Sel1 Into g_old_rec;
232 If C_Sel1%notfound then
233 Close C_Sel1;
234 --
235 -- The primary key is invalid therefore we must error
236 --
237 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
238 hr_utility.raise_error;
239 End If;
240 Close C_Sel1;
241 If (p_object_version_number <> g_old_rec.object_version_number) Then
242 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
243 hr_utility.raise_error;
244 End If;
245 --
246 hr_utility.set_location(' Leaving:'||l_proc, 10);
247 --
248 -- We need to trap the ORA LOCK exception
249 --
250 Exception
251 When HR_Api.Object_Locked then
252 --
253 -- The object is locked therefore we need to supply a meaningful
254 -- error message.
255 --
256 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
257 hr_utility.set_message_token('TABLE_NAME', 'ben_actn_typ');
258 hr_utility.raise_error;
259 End lck;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |-----------------------------< convert_args >-----------------------------|
263 -- ----------------------------------------------------------------------------
264 Function convert_args
265 (
266 p_actn_typ_id in number,
267 p_business_group_id in number,
268 p_type_cd in varchar2,
269 p_name in varchar2,
270 p_description in varchar2,
271 p_eat_attribute_category in varchar2,
272 p_eat_attribute1 in varchar2,
273 p_eat_attribute2 in varchar2,
274 p_eat_attribute3 in varchar2,
275 p_eat_attribute4 in varchar2,
276 p_eat_attribute5 in varchar2,
277 p_eat_attribute6 in varchar2,
278 p_eat_attribute7 in varchar2,
279 p_eat_attribute8 in varchar2,
280 p_eat_attribute9 in varchar2,
281 p_eat_attribute10 in varchar2,
282 p_eat_attribute11 in varchar2,
283 p_eat_attribute12 in varchar2,
284 p_eat_attribute13 in varchar2,
285 p_eat_attribute14 in varchar2,
286 p_eat_attribute15 in varchar2,
287 p_eat_attribute16 in varchar2,
288 p_eat_attribute17 in varchar2,
289 p_eat_attribute18 in varchar2,
290 p_eat_attribute19 in varchar2,
291 p_eat_attribute20 in varchar2,
292 p_eat_attribute21 in varchar2,
293 p_eat_attribute22 in varchar2,
294 p_eat_attribute23 in varchar2,
295 p_eat_attribute24 in varchar2,
296 p_eat_attribute25 in varchar2,
297 p_eat_attribute26 in varchar2,
298 p_eat_attribute27 in varchar2,
299 p_eat_attribute28 in varchar2,
300 p_eat_attribute29 in varchar2,
301 p_eat_attribute30 in varchar2,
302 p_object_version_number in number
303 )
304 Return g_rec_type is
305 --
306 l_rec g_rec_type;
307 l_proc varchar2(72) := g_package||'convert_args';
308 --
309 Begin
310 --
311 hr_utility.set_location('Entering:'||l_proc, 5);
312 --
313 -- Convert arguments into local l_rec structure.
314 --
315 l_rec.actn_typ_id := p_actn_typ_id;
316 l_rec.business_group_id := p_business_group_id;
317 l_rec.type_cd := p_type_cd;
318 l_rec.name := p_name;
319 l_rec.description := p_description;
320 l_rec.eat_attribute_category := p_eat_attribute_category;
321 l_rec.eat_attribute1 := p_eat_attribute1;
322 l_rec.eat_attribute2 := p_eat_attribute2;
323 l_rec.eat_attribute3 := p_eat_attribute3;
324 l_rec.eat_attribute4 := p_eat_attribute4;
325 l_rec.eat_attribute5 := p_eat_attribute5;
326 l_rec.eat_attribute6 := p_eat_attribute6;
327 l_rec.eat_attribute7 := p_eat_attribute7;
328 l_rec.eat_attribute8 := p_eat_attribute8;
329 l_rec.eat_attribute9 := p_eat_attribute9;
330 l_rec.eat_attribute10 := p_eat_attribute10;
331 l_rec.eat_attribute11 := p_eat_attribute11;
332 l_rec.eat_attribute12 := p_eat_attribute12;
333 l_rec.eat_attribute13 := p_eat_attribute13;
334 l_rec.eat_attribute14 := p_eat_attribute14;
335 l_rec.eat_attribute15 := p_eat_attribute15;
336 l_rec.eat_attribute16 := p_eat_attribute16;
337 l_rec.eat_attribute17 := p_eat_attribute17;
338 l_rec.eat_attribute18 := p_eat_attribute18;
339 l_rec.eat_attribute19 := p_eat_attribute19;
340 l_rec.eat_attribute20 := p_eat_attribute20;
341 l_rec.eat_attribute21 := p_eat_attribute21;
342 l_rec.eat_attribute22 := p_eat_attribute22;
343 l_rec.eat_attribute23 := p_eat_attribute23;
344 l_rec.eat_attribute24 := p_eat_attribute24;
345 l_rec.eat_attribute25 := p_eat_attribute25;
346 l_rec.eat_attribute26 := p_eat_attribute26;
347 l_rec.eat_attribute27 := p_eat_attribute27;
348 l_rec.eat_attribute28 := p_eat_attribute28;
349 l_rec.eat_attribute29 := p_eat_attribute29;
350 l_rec.eat_attribute30 := p_eat_attribute30;
351 l_rec.object_version_number := p_object_version_number;
352 --
353 -- Return the plsql record structure.
354 --
355 hr_utility.set_location(' Leaving:'||l_proc, 10);
356 Return(l_rec);
357 --
358 End convert_args;
359 --
360 -- ----------------------------------------------------------------------------
361 -- |--------------------------<add_language>----------------------------------|
362 -- ----------------------------------------------------------------------------
363 Procedure add_language
364 is
365 begin
366 delete from ben_actn_typ_tl t
367 where not exists
368 (select null
369 from ben_actn_typ_tl b
370 where b.actn_typ_id = t.actn_typ_id
371 );
372
373 update ben_actn_typ_tl t set (
374 name,
375 description,
376 type_cd
377 ) = (select
378 b.name,
379 b.description,
380 b.type_cd
381 from ben_actn_typ_tl b
382 where b.actn_typ_id = t.actn_typ_id
383 and b.language = t.source_lang)
384 where (
385 t.actn_typ_id,
386 t.language
387 ) in (select
388 subt.actn_typ_id,
389 subt.language
390 from ben_actn_typ_tl subb, ben_actn_typ_tl subt
391 where subb.actn_typ_id = subt.actn_typ_id
392 and subb.language = subt.source_lang
393 and (subb.name <> subt.name
394 or subb.description <> subt.description
395 or subb.type_cd <> subt.type_cd
396 ));
397 insert into ben_actn_typ_tl (
398 actn_typ_id,
399 name,
400 description,
401 language,
402 type_cd,
403 source_lang,
404 last_update_date,
405 last_updated_by,
406 last_update_login,
407 created_by,
408 creation_date
409 ) select
410 b.actn_typ_id,
411 b.name,
412 b.description,
413 l.language_code,
414 b.type_cd,
415 b.source_lang,
416 b.last_update_date,
417 b.last_updated_by,
418 b.last_update_login,
419 b.created_by,
420 b.creation_date
421 from ben_actn_typ_tl b, fnd_languages l
422 where l.installed_flag in ('I', 'B')
423 and b.language = userenv('LANG')
424 and not exists
425 (select null
426 from ben_actn_typ_tl t
427 where t.actn_typ_id = b.actn_typ_id
428 and t.language = l.language_code);
429 end add_language;
430 --
431 end ben_eat_shd;