[Home] [Help]
PACKAGE BODY: APPS.BEN_XER_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_xer_shd as
2 /* $Header: bexerrhi.pkb 120.1 2006/03/22 13:57:32 tjesumic noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xer_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 cursor c1 is
33 select xfi.name
34 from ben_ext_file xfi,
35 ben_ext_rcd_in_file xrf,
36 ben_ext_data_elmt_in_rcd xdr
37 where xdr.ext_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id
38 and xdr.ext_rcd_id = xdr.ext_rcd_id
39 and xrf.sort1_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id
40 and xrf.ext_file_id = xfi.ext_file_id;
41
42 cursor c2 is
43 select xfi.name
47 where xdr.ext_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id
44 from ben_ext_file xfi,
45 ben_ext_rcd_in_file xrf,
46 ben_ext_data_elmt_in_rcd xdr
48 and xdr.ext_rcd_id = xdr.ext_rcd_id
49 and xrf.sort2_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id
50 and xrf.ext_file_id = xfi.ext_file_id;
51
52 cursor c3 is
53 select xfi.name
54 from ben_ext_file xfi,
55 ben_ext_rcd_in_file xrf,
56 ben_ext_data_elmt_in_rcd xdr
57 where xdr.ext_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id
58 and xdr.ext_rcd_id = xdr.ext_rcd_id
59 and xrf.sort3_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id
60 and xrf.ext_file_id = xfi.ext_file_id;
61
62 cursor c4 is
63 select xfi.name
64 from ben_ext_file xfi,
65 ben_ext_rcd_in_file xrf,
66 ben_ext_data_elmt_in_rcd xdr
67 where xdr.ext_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id
68 and xdr.ext_rcd_id = xdr.ext_rcd_id
69 and xrf.sort4_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id
70 and xrf.ext_file_id = xfi.ext_file_id;
71
72 cursor c5 is
73 select xfi.name
74 from ben_ext_file xfi,
75 ben_ext_rcd_in_file xrf,
76 ben_ext_where_clause xwc
77 where xwc.cond_ext_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id
78 and xwc.ext_rcd_in_file_id = xrf.ext_rcd_in_file_id
79 and xrf.ext_file_id = xfi.ext_file_id;
80
81 l_name ben_ext_file.name%TYPE; -- UTF8 varchar2(100);
82 --
83 Begin
84 hr_utility.set_location('Entering:'||l_proc, 5);
85 --
86 If (p_constraint_name = 'BEN_EXT_RCD_IN_FILE_FK1') Then
87 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
88 fnd_message.set_token('PROCEDURE', l_proc);
89 fnd_message.set_token('STEP','5');
90 fnd_message.raise_error;
91 ElsIf (p_constraint_name = 'BEN_EXT_DATA_ELMT_IN_RCD_FK2') Then
92 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
93 fnd_message.set_token('PROCEDURE', l_proc);
94 fnd_message.set_token('STEP','10');
95 fnd_message.raise_error;
96 ElsIf (p_constraint_name = 'BEN_EXT_DATA_ELMT_IN_RCD_FK3') Then
97 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
98 fnd_message.set_token('PROCEDURE', l_proc);
99 fnd_message.set_token('STEP','15');
100 fnd_message.raise_error;
101 ElsIf (p_constraint_name = 'BEN_EXT_DATA_ELMT_IN_RCD_PK') Then
102 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
103 fnd_message.set_token('PROCEDURE', l_proc);
104 fnd_message.set_token('STEP','20');
105 fnd_message.raise_error;
106 ElsIf (p_constraint_name = 'BEN_EXT_INCL_CHG_FK1') Then
107 ben_utility.child_exists_error(p_table_name => 'BEN_EXT_INCL_CHG');
108 ElsIf (p_constraint_name = 'BEN_EXT_WHERE_CLAUSE_FK3') Then
109 fnd_message.set_name('BEN', 'BEN_92479_XWC_EXISTS');
110 fnd_message.raise_error;
111 ElsIf (p_constraint_name = 'BEN_EXT_RCD_IN_FILE_FK4') Then
112 open c1;
113 fetch c1 into l_name;
114 close c1;
115 fnd_message.set_name('BEN', 'BEN_92478_ELMT_REF_IN_SORT');
116 fnd_message.set_token('FILE_NAME', l_name);
117 fnd_message.raise_error;
118 ElsIf (p_constraint_name = 'BEN_EXT_RCD_IN_FILE_FK5') Then
119 open c2;
120 fetch c2 into l_name;
121 close c2;
122 fnd_message.set_name('BEN', 'BEN_92478_ELMT_REF_IN_SORT');
123 fnd_message.set_token('FILE_NAME', l_name);
124 fnd_message.raise_error;
125 ElsIf (p_constraint_name = 'BEN_EXT_RCD_IN_FILE_FK6') Then
126 open c3;
127 fetch c3 into l_name;
128 close c3;
129 fnd_message.set_name('BEN', 'BEN_92478_ELMT_REF_IN_SORT');
130 fnd_message.set_token('FILE_NAME', l_name);
131 fnd_message.raise_error;
132 ElsIf (p_constraint_name = 'BEN_EXT_RCD_IN_FILE_FK7') Then
133 open c4;
134 fetch c4 into l_name;
135 close c4;
136 fnd_message.set_name('BEN', 'BEN_92478_ELMT_REF_IN_SORT');
137 fnd_message.set_token('FILE_NAME', l_name);
138 fnd_message.raise_error;
139 ElsIf (p_constraint_name = 'BEN_EXT_WHERE_CLAUSE_FK6') Then
140 open c5;
141 fetch c5 into l_name;
142 if c5%found then
143 close c5;
144 fnd_message.set_name('BEN', 'BEN_92480_XWC_EXISTS2');
145 fnd_message.set_token('FILE_NAME', l_name);
146 fnd_message.raise_error;
147 else
148 close c5;
149 fnd_message.set_name('BEN', 'BEN_92479_XWC_EXISTS');
150 fnd_message.raise_error;
151 end if;
152 Else
153 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
154 fnd_message.set_token('PROCEDURE', l_proc);
155 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
156 fnd_message.raise_error;
157 End If;
158 --
159 hr_utility.set_location(' Leaving:'||l_proc, 10);
160 End constraint_error;
161 --
162 -- ----------------------------------------------------------------------------
163 -- |-----------------------------< api_updating >-----------------------------|
164 -- ----------------------------------------------------------------------------
165 Function api_updating
166 (
167 p_ext_data_elmt_in_rcd_id in number,
168 p_object_version_number in number
169 ) Return Boolean Is
170 --
171 --
172 -- Cursor selects the 'current' row from the HR Schema
173 --
174 Cursor C_Sel1 is
175 select
176 ext_data_elmt_in_rcd_id,
177 seq_num,
178 strt_pos,
179 dlmtr_val,
180 rqd_flag,
184 ext_rcd_id,
181 sprs_cd,
182 any_or_all_cd,
183 ext_data_elmt_id,
185 business_group_id,
186 legislation_code,
187 last_update_date,
188 creation_date,
189 last_updated_by,
190 last_update_login,
191 created_by ,
192 object_version_number,
193 hide_flag
194 from ben_ext_data_elmt_in_rcd
195 where ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id;
196 --
197 l_proc varchar2(72) := g_package||'api_updating';
198 l_fct_ret boolean;
199 --
200 Begin
201 hr_utility.set_location('Entering:'||l_proc, 5);
202 --
203 If (
204 p_ext_data_elmt_in_rcd_id is null and
205 p_object_version_number is null
206 ) Then
207 --
208 -- One of the primary key arguments is null therefore we must
209 -- set the returning function value to false
210 --
211 l_fct_ret := false;
212 Else
213 If (
214 p_ext_data_elmt_in_rcd_id = g_old_rec.ext_data_elmt_in_rcd_id and
215 p_object_version_number = g_old_rec.object_version_number
216 ) Then
217 hr_utility.set_location(l_proc, 10);
218 --
219 -- The g_old_rec is current therefore we must
220 -- set the returning function to true
221 --
222 l_fct_ret := true;
223 Else
224 --
225 -- Select the current row into g_old_rec
226 --
227 Open C_Sel1;
228 Fetch C_Sel1 Into g_old_rec;
229 If C_Sel1%notfound Then
230 Close C_Sel1;
231 --
232 -- The primary key is invalid therefore we must error
233 --
234 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
235 fnd_message.raise_error;
236 End If;
237 Close C_Sel1;
238 If (p_object_version_number <> g_old_rec.object_version_number) Then
239 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
240 fnd_message.raise_error;
241 End If;
242 hr_utility.set_location(l_proc, 15);
243 l_fct_ret := true;
244 End If;
245 End If;
246 hr_utility.set_location(' Leaving:'||l_proc, 20);
247 Return (l_fct_ret);
248 --
249 End api_updating;
250 --
251 -- ----------------------------------------------------------------------------
252 -- |---------------------------------< lck >----------------------------------|
253 -- ----------------------------------------------------------------------------
254 Procedure lck
255 (
256 p_ext_data_elmt_in_rcd_id in number,
257 p_object_version_number in number
258 ) is
259 --
260 -- Cursor selects the 'current' row from the HR Schema
261 --
262 Cursor C_Sel1 is
263 select ext_data_elmt_in_rcd_id,
264 seq_num,
265 strt_pos,
266 dlmtr_val,
267 rqd_flag,
268 sprs_cd,
269 any_or_all_cd,
270 ext_data_elmt_id,
271 ext_rcd_id,
272 business_group_id,
273 legislation_code,
274 last_update_date,
275 creation_date,
276 last_updated_by,
277 last_update_login,
278 created_by ,
279 object_version_number,
280 hide_flag
281 from ben_ext_data_elmt_in_rcd
282 where ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
283 for update nowait;
284 --
285 l_proc varchar2(72) := g_package||'lck';
286 --
287 Begin
288 hr_utility.set_location('Entering:'||l_proc, 5);
289 --
290 -- Add any mandatory argument checking here:
291 -- Example:
292 -- hr_api.mandatory_arg_error
293 -- (p_api_name => l_proc,
294 -- p_argument => 'object_version_number',
295 -- p_argument_value => p_object_version_number);
296 --
297 Open C_Sel1;
298 Fetch C_Sel1 Into g_old_rec;
299 If C_Sel1%notfound then
300 Close C_Sel1;
301 --
302 -- The primary key is invalid therefore we must error
303 --
304 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
305 fnd_message.raise_error;
306 End If;
307 Close C_Sel1;
308 If (p_object_version_number <> g_old_rec.object_version_number) Then
309 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
310 fnd_message.raise_error;
311 End If;
312 --
313 hr_utility.set_location(' Leaving:'||l_proc, 10);
314 --
315 -- We need to trap the ORA LOCK exception
316 --
317 Exception
318 When HR_Api.Object_Locked then
319 --
320 -- The object is locked therefore we need to supply a meaningful
321 -- error message.
322 --
323 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
324 fnd_message.set_token('TABLE_NAME', 'ben_ext_data_elmt_in_rcd');
325 fnd_message.raise_error;
326 End lck;
327 --
328 -- ----------------------------------------------------------------------------
329 -- |-----------------------------< convert_args >-----------------------------|
330 -- ----------------------------------------------------------------------------
331 Function convert_args
332 (
333 p_ext_data_elmt_in_rcd_id in number,
334 p_seq_num in number,
335 p_strt_pos in number,
336 p_dlmtr_val in varchar2,
337 p_rqd_flag in varchar2,
338 p_sprs_cd in varchar2,
339 p_any_or_all_cd in varchar2,
340 p_ext_data_elmt_id in number,
344 p_last_update_date in date,
341 p_ext_rcd_id in number,
342 p_business_group_id in number,
343 p_legislation_code in varchar2,
345 p_creation_date in date,
346 p_last_updated_by in number,
347 p_last_update_login in number,
348 p_created_by in number,
349 p_object_version_number in number,
350 p_hide_flag in varchar2
351 )
352 Return g_rec_type is
353 --
354 l_rec g_rec_type;
355 l_proc varchar2(72) := g_package||'convert_args';
356 --
357 Begin
358 --
359 hr_utility.set_location('Entering:'||l_proc, 5);
360 --
361 -- Convert arguments into local l_rec structure.
362 --
363 l_rec.ext_data_elmt_in_rcd_id := p_ext_data_elmt_in_rcd_id;
364 l_rec.seq_num := p_seq_num;
365 l_rec.strt_pos := p_strt_pos;
366 l_rec.dlmtr_val := p_dlmtr_val;
367 l_rec.rqd_flag := p_rqd_flag;
368 l_rec.sprs_cd := p_sprs_cd;
369 l_rec.any_or_all_cd := p_any_or_all_cd;
370 l_rec.ext_data_elmt_id := p_ext_data_elmt_id;
371 l_rec.ext_rcd_id := p_ext_rcd_id;
372 l_rec.business_group_id := p_business_group_id;
373 l_rec.legislation_code := p_legislation_code;
374 l_rec.last_update_date := p_last_update_date;
375 l_rec.creation_date := p_creation_date;
376 l_rec.last_updated_by := p_last_updated_by;
377 l_rec.last_update_login := p_last_update_login;
378 l_rec.created_by := p_created_by;
379 l_rec.object_version_number := p_object_version_number;
380 l_rec.hide_flag := p_hide_flag;
381 --
382 -- Return the plsql record structure.
383 --
384 hr_utility.set_location(' Leaving:'||l_proc, 10);
385 Return(l_rec);
386 --
387 End convert_args;
388 --
389 end ben_xer_shd;