[Home] [Help]
PACKAGE BODY: APPS.HR_LOT_SHD
Source
1 Package Body hr_lot_shd as
2 /* $Header: hrlotrhi.pkb 115.10 2002/12/04 05:45:04 hjonnala ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_lot_shd.'; -- Global package name
9 g_loc_bg_id number(15); -- Efficiency global
10 --
11 -- ----------------------------------------------------------------------------
12 -- |---------------------------< constraint_error >---------------------------|
13 -- ----------------------------------------------------------------------------
14 Procedure constraint_error
15 (p_constraint_name in all_constraints.constraint_name%type) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 hr_utility.set_location('Entering:'||l_proc, 5);
21 --
22 hr_utility.set_message(800, 'HR_7877_API_INVALID_CONSTRAINT');
23 hr_utility.set_message_token('PROCEDURE', l_proc);
24 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
25 hr_utility.raise_error;
26 --
27 hr_utility.set_location(' Leaving:'||l_proc, 10);
28 End constraint_error;
29 --
30 -- ----------------------------------------------------------------------------
31 -- |-----------------------------< api_updating >-----------------------------|
32 -- ----------------------------------------------------------------------------
33 --
34 Function api_updating
35 (
36 p_location_id in number,
37 p_language in varchar2
38 ) Return Boolean Is
39 --
40 --
41 -- Cursor selects the 'current' row from the HR Schema
42 --
43 Cursor C_Sel1 is
44 select
45 location_id,
46 language,
47 source_lang,
48 location_code,
49 description
50 from hr_locations_all_tl
51 where location_id = p_location_id
52 and language = p_language;
53 --
54 l_proc varchar2(72) := g_package||'api_updating';
55 l_fct_ret boolean;
56 --
57 Begin
58 hr_utility.set_location('Entering:'||l_proc, 5);
59 --
60 If (
61 p_location_id is null or
62 p_language is null
63 ) Then
64 --
65 -- One of the primary key arguments is null therefore we must
66 -- set the returning function value to false
67 --
68 l_fct_ret := false;
69 Else
70 If (
71 p_location_id = g_old_rec.location_id and
72 p_language = g_old_rec.language
73 ) Then
74 hr_utility.set_location(l_proc, 10);
75 --
76 -- The g_old_rec is current therefore we must
77 -- set the returning function to true
78 --
79 l_fct_ret := true;
80 Else
81 --
82 -- Select the current row into g_old_rec
83 --
84 Open C_Sel1;
85 Fetch C_Sel1 Into g_old_rec;
86 If C_Sel1%notfound Then
87 Close C_Sel1;
88 --
89 -- The primary key is invalid therefore we must error
90 --
91 hr_utility.set_message(800, 'HR_7220_INVALID_PRIMARY_KEY');
92 hr_utility.raise_error;
93 End If;
94 Close C_Sel1;
95 --
96 hr_utility.set_location(l_proc, 15);
97 l_fct_ret := true;
98 End If;
99 End If;
100 hr_utility.set_location(' Leaving:'||l_proc, 20);
101 Return (l_fct_ret);
102 --
103 End api_updating;
104 --
105 -- ----------------------------------------------------------------------------
106 -- |---------------------------------< lck >----------------------------------|
107 -- ----------------------------------------------------------------------------
108 Procedure lck
109 (
110 p_location_id in number,
111 p_language in varchar2
112 ) is
113 --
114 -- Cursor selects the 'current' row from the HR Schema
115 --
116 Cursor C_Sel1 is
117 select location_id,
118 language,
119 source_lang,
120 location_code,
121 description
122 from hr_locations_all_tl
123 where location_id = p_location_id
124 and language = p_language
125 for update nowait;
126 --
127 l_proc varchar2(72) := g_package||'lck';
128 --
129 Begin
130 hr_utility.set_location('Entering:'||l_proc, 5);
131 --
132 -- Add any mandatory argument checking here:
133 -- Example:
134 -- hr_api.mandatory_arg_error
135 -- (p_api_name => l_proc,
136 -- p_argument => 'object_version_number',
137 -- p_argument_value => p_object_version_number);
138 --
139 hr_api.mandatory_arg_error
140 (p_api_name => l_proc,
141 p_argument => 'location_id',
142 p_argument_value => p_location_id);
143 --
144 hr_api.mandatory_arg_error
145 (p_api_name => l_proc,
146 p_argument => 'language',
147 p_argument_value => p_language);
148 --
149 --
150 Open C_Sel1;
151 Fetch C_Sel1 Into g_old_rec;
152 If C_Sel1%notfound then
153 Close C_Sel1;
154 --
155 -- The primary key is invalid therefore we must error
156 --
157 hr_utility.set_message(800, 'HR_7220_INVALID_PRIMARY_KEY');
158 hr_utility.raise_error;
159 End If;
160 Close C_Sel1;
161 --
162 --
163 hr_utility.set_location(' Leaving:'||l_proc, 10);
164 --
165 -- We need to trap the ORA LOCK exception
166 --
167 Exception
168 When HR_Api.Object_Locked then
169 --
170 -- The object is locked therefore we need to supply a meaningful
171 -- error message.
172 --
173 hr_utility.set_message(800, 'HR_7165_OBJECT_LOCKED');
174 hr_utility.set_message_token('TABLE_NAME', 'hr_locations_all_tl');
175 hr_utility.raise_error;
176 End lck;
177 --
178 -- ----------------------------------------------------------------------------
179 -- |-----------------------------< add_language >-----------------------------|
180 -- ----------------------------------------------------------------------------
181 Procedure add_language Is
182 --
183 l_proc varchar2(72) := g_package||'add_language';
184 begin
185 delete from hr_locations_all_tl t
186 where not exists
187 ( select null
188 from hr_locations_all b
189 where b.location_id = t.location_id
190 );
191
192 update hr_locations_all_tl t
193 set ( location_code,
194 description ) =
195 ( select b.location_code,
196 b.description
197 from hr_locations_all_tl b
198 where b.location_id = t.location_id
199 and b.language = t.source_lang )
200 where ( t.location_id,
201 t.language
202 ) in
203 ( select subt.location_id,
204 subt.language
205 from hr_locations_all_tl subb, hr_locations_all_tl subt
206 where subb.location_id = subt.location_id
207 and subb.language = subt.source_lang
208 and ( subb.location_code <> subt.location_code
209 or subb.description <> subt.description
210 or (subb.description is null and subt.description is not null)
211 or (subb.description is not null and subt.description is null)
212 )
213 );
214
215 insert into hr_locations_all_tl
216 (
217 location_id,
218 location_code,
219 description,
220 last_update_date,
221 last_updated_by,
222 last_update_login,
223 created_by,
224 creation_date,
225 language,
226 source_lang
227 )
228 select b.location_id,
229 b.location_code,
230 b.description,
231 b.last_update_date,
232 b.last_updated_by,
233 b.last_update_login,
234 b.created_by,
235 b.creation_date,
236 l.language_code,
237 b.source_lang
238 from hr_locations_all_tl b, fnd_languages l
239 where l.installed_flag in ('I', 'B')
240 and b.language = userenv('LANG')
241 and not exists
242 (select null
243 from hr_locations_all_tl t
244 where t.location_id = b.location_id
245 and t.language = l.language_code);
246 -- Begin Bug: 2148847, Removed set_location to fix VALUE ERROR, this unknown
247 -- error is coming while running PERNLINS.sql script.
248
249 -- hr_utility.set_location(' Leaving:'||l_proc, 10);
250
251 -- End of Bug: 2148847.
252 End add_language;
253 --
254 -- ----------------------------------------------------------------------------
255 -- |-----------------------------< convert_args >-----------------------------|
256 -- ----------------------------------------------------------------------------
257 Function convert_args
258 (
259 p_location_id in number,
260 p_language in varchar2,
261 p_source_lang in varchar2,
262 p_location_code in varchar2,
263 p_description in varchar2
264 )
265 Return g_rec_type is
266 --
267 l_rec g_rec_type;
268 l_proc varchar2(72) := g_package||'convert_args';
269 --
270 Begin
271 --
272 hr_utility.set_location('Entering:'||l_proc, 5);
273 --
274 -- Convert arguments into local l_rec structure.
275 --
276 l_rec.location_id := p_location_id;
277 l_rec.language := p_language;
278 l_rec.source_lang := p_source_lang;
279 l_rec.location_code := p_location_code;
280 l_rec.description := p_description;
281 --
282 -- Return the plsql record structure.
283 --
284 hr_utility.set_location(' Leaving:'||l_proc, 10);
285 Return(l_rec);
286 --
287 End convert_args;
288 --
289 -- ----------------------------------------------------------------------------
290 -- |--------------------< return_value_business_group_id >--------------------|
291 -- ----------------------------------------------------------------------------
292 Function return_value_business_group_id (p_location_id number) Return number
293 Is
294 --
295 -- Cursor to obtain underlying business group id.
296 --
297 cursor csr_bg_id is
298 select business_group_id
299 from hr_locations_all
300 where location_id = p_location_id;
301 --
302 l_proc varchar2(72) := g_package||'return_value_business_group_id';
303 --
304 begin
305 --
306 --
307 hr_utility.set_location('Entering:'||l_proc, 5);
308 --
309 if (p_location_id <> nvl(g_old_rec.location_id, hr_api.g_number) ) then
310 --
311 -- Only fetch business_group_id from database if required - i.e.
312 -- the location_id has changed since last insert or update. Otherwise
313 -- use stored value in g_loc_bg_id. This value is set when
314 -- an insert takes place, or in this procedure if a database
315 -- fetch takes palce.
316 --
317 open csr_bg_id;
318 fetch csr_bg_id into g_loc_bg_id;
319 --
320 if csr_bg_id%notfound then
321 close csr_bg_id;
322 --
323 -- The primary key is invalid therefore we must error
324 --
325 hr_utility.set_message(800, 'HR_7220_INVALID_PRIMARY_KEY');
326 hr_utility.raise_error;
327 end if;
328 --
329 close csr_bg_id;
330 end if;
331 --
332 hr_utility.set_location('Leaving:'||l_proc, 10);
333 --
334 return g_loc_bg_id;
335 --
336 end return_value_business_group_id;
337 --
338 -- ----------------------------------------------------------------------------
339 -- |--------------------< set_value_business_group_id >--------------------|
340 -- ----------------------------------------------------------------------------
341 Procedure set_value_business_group_id (p_business_group_id number)
342 Is
343 --
344 l_proc varchar2(72) := g_package||'set_value_business_group_id';
345 begin
346 --
347 hr_utility.set_location('Entering:'||l_proc, 5);
348 --
349 g_loc_bg_id := p_business_group_id;
350 --
351 hr_utility.set_location(' Leaving:'||l_proc, 10);
352 --
353 end set_value_business_group_id;
354 --
355 end hr_lot_shd;