1 Package Body hxc_hat_bus as
2 /* $Header: hxchatrhi.pkb 120.2 2005/09/23 10:41:13 sechandr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_hat_bus.'; -- Global package name
9 g_debug boolean :=hr_utility.debug_enabled;
10 --
11 -- The following two global variables are only to be
12 -- used by the return_legislation_code function.
13 --
14 g_legislation_code varchar2(150) default null;
15 g_alias_type_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_alias_type_id in number
23 ,p_associated_column1 in varchar2 default null
24 ) is
25 --
26 -- Declare cursor
27 --
28 cursor csr_sec_grp is
29 select pbg.security_group_id
30 from per_business_groups pbg
31 , hxc_alias_types hat
32 where hat.alias_type_id = p_alias_type_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72);
38 --
39 begin
40 --
41 g_debug:=hr_utility.debug_enabled;
42 if g_debug then
43 l_proc := g_package||'set_security_group_id';
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 end if;
46 --
47 -- Ensure that all the mandatory parameter are not null
48 --
49 hr_api.mandatory_arg_error
50 (p_api_name => l_proc
51 ,p_argument => 'alias_type_id'
52 ,p_argument_value => p_alias_type_id
53 );
54 --
55 open csr_sec_grp;
56 fetch csr_sec_grp into l_security_group_id;
57 --
58 if csr_sec_grp%notfound then
59 --
60 close csr_sec_grp;
61 --
62 -- The primary key is invalid therefore we must error
63 --
64 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
65 hr_multi_message.add
66 (p_associated_column1
67 => nvl(p_associated_column1,'ALIAS_TYPE_ID')
68 );
69 --
70 else
71 close csr_sec_grp;
72 --
73 -- Set the security_group_id in CLIENT_INFO
74 --
75 hr_api.set_security_group_id
76 (p_security_group_id => l_security_group_id
77 );
78 end if;
79 --
80 if g_debug then
81 hr_utility.set_location(' Leaving:'|| l_proc, 20);
82 end if;
83 --
84 end set_security_group_id;
85 --
86 -- ---------------------------------------------------------------------------
87 -- |---------------------< return_legislation_code >-------------------------|
88 -- ---------------------------------------------------------------------------
89 --
90 Function return_legislation_code
91 (p_alias_type_id in number
92 )
93 Return Varchar2 Is
94 --
95 -- Declare cursor
96 --
97 cursor csr_leg_code is
98 select pbg.legislation_code
99 from per_business_groups pbg
100 , hxc_alias_types hat
101 where hat.alias_type_id = p_alias_type_id;
102 --
103 -- Declare local variables
104 --
105 l_legislation_code varchar2(150);
106 l_proc varchar2(72);
107 --
108 Begin
109 --
110 g_debug:=hr_utility.debug_enabled;
111 if g_debug then
112 l_proc := g_package||'return_legislation_code';
113 hr_utility.set_location('Entering:'|| l_proc, 10);
114 end if;
115 --
116 -- Ensure that all the mandatory parameter are not null
117 --
118 hr_api.mandatory_arg_error
119 (p_api_name => l_proc
120 ,p_argument => 'alias_type_id'
121 ,p_argument_value => p_alias_type_id
122 );
123 --
124 if ( nvl(hxc_hat_bus.g_alias_type_id, hr_api.g_number)
125 = p_alias_type_id) then
126 --
127 -- The legislation code has already been found with a previous
128 -- call to this function. Just return the value in the global
129 -- variable.
130 --
131 l_legislation_code := hxc_hat_bus.g_legislation_code;
132 if g_debug then
133 hr_utility.set_location(l_proc, 20);
134 end if;
135 else
136 --
137 -- The ID is different to the last call to this function
138 -- or this is the first call to this function.
139 --
140 open csr_leg_code;
141 fetch csr_leg_code into l_legislation_code;
142 --
143 if csr_leg_code%notfound then
144 --
145 -- The primary key is invalid therefore we must error
146 --
147 close csr_leg_code;
148 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
149 fnd_message.raise_error;
150 end if;
151 if g_debug then
152 hr_utility.set_location(l_proc,30);
153 end if;
154 --
155 -- Set the global variables so the values are
156 -- available for the next call to this function.
157 --
158 close csr_leg_code;
159 hxc_hat_bus.g_alias_type_id := p_alias_type_id;
160 hxc_hat_bus.g_legislation_code := l_legislation_code;
161 end if;
162 if g_debug then
163 hr_utility.set_location(' Leaving:'|| l_proc, 40);
164 end if;
165 return l_legislation_code;
166 end return_legislation_code;
167 --
168 -- ----------------------------------------------------------------------------
169 -- |-----------------------< chk_non_updateable_args >------------------------|
170 -- ----------------------------------------------------------------------------
171 -- {Start Of Comments}
172 --
173 -- Description:
174 -- This procedure is used to ensure that non updateable attributes have
175 -- not been updated. If an attribute has been updated an error is generated.
176 --
177 -- Pre Conditions:
178 -- g_old_rec has been populated with details of the values currently in
179 -- the database.
180 --
181 -- In Arguments:
182 -- p_rec has been populated with the updated values the user would like the
183 -- record set to.
184 --
185 -- Post Success:
186 -- Processing continues if all the non updateable attributes have not
187 -- changed.
188 --
189 -- Post Failure:
190 -- An application error is raised if any of the non updatable attributes
191 -- have been altered.
192 --
193 -- {End Of Comments}
194 -- ----------------------------------------------------------------------------
195 Procedure chk_non_updateable_args
196 (p_rec in hxc_hat_shd.g_rec_type
197 ) IS
198 --
199 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
200 --
201 Begin
202 --
203 -- Only proceed with the validation if a row exists for the current
204 -- record in the HR Schema.
205 --
206 IF NOT hxc_hat_shd.api_updating
207 (p_alias_type_id => p_rec.alias_type_id
208 ,p_object_version_number => p_rec.object_version_number
209 ) THEN
210 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
211 fnd_message.set_token('PROCEDURE ', l_proc);
212 fnd_message.set_token('STEP ', '5');
213 fnd_message.raise_error;
214 END IF;
215 --
216 --
217 End chk_non_updateable_args;
218
219 -------------------------------------------------------------------------------
220 -- |-------------------------<chk_name>---------------------------------------|
221 -------------------------------------------------------------------------------
222 Procedure chk_name (p_type hxc_alias_types.ALIAS_TYPE%TYPE,
223 p_reference_object hxc_alias_types.REFERENCE_OBJECT%TYPE
224 ) is
225
226 CURSOR csr_chk_alias_type IS
227 SELECT 'error'
228 FROM sys.dual
229 WHERE EXISTS (
230 SELECT 'x'
231 from hxc_alias_types
232 where alias_type = p_type and
233 reference_object = p_reference_object);
234
235 l_error varchar2(5) := NULL;
236
237 Begin
238
239 if g_debug then
240 hr_utility.trace('Entering chk_name');
241 end if;
242
243 --
244 -- Raise an error if the alias type and reference object are already defined.
245 --
246 OPEN csr_chk_alias_type;
247 FETCH csr_chk_alias_type INTO l_error;
248 CLOSE csr_chk_alias_type;
249 --
250 IF l_error IS NOT NULL
251 THEN
252 --
253 hr_utility.set_message(809,'HXC_ALT_EXISTS'); -- 'Alias Type already exists');
254 hr_utility.raise_error;
255 --
256 END IF;
257 if g_debug then
258 hr_utility.trace('Leaving chk_name');
259 end if;
260
261 end chk_name;
262 --
263 -------------------------------------------------------------------------------
264 -- |-------------------------<chk_fk_realation>---------------------------------------|
265 -------------------------------------------------------------------------------
266 Procedure chk_fk_relation (p_alias_type_id hxc_alias_types.alias_type_id%TYPE
267 ) is
268 Cursor csr_chk_aldef IS
269 select 'error'
270 from sys.dual
271 where exists(
272 select 'x'
273 from hxc_alias_definitions had,
274 hxc_alias_types hat
275 where hat.alias_type_id = p_alias_type_id and
276 hat.alias_type_id = had.alias_type_id
277 );
278 l_error varchar2(5) := NULL;
279 begin
280
281 g_debug:=hr_utility.debug_enabled;
282 if g_debug then
283 hr_utility.trace('Entering chk_fk_relation');
284 end if;
285 --
286 OPEN csr_chk_aldef;
287 FETCH csr_chk_aldef into l_error;
288 close csr_chk_aldef;
289
290 if g_debug then
291 hr_utility.trace('1st Validation'|| l_error);
292 end if;
293
294 IF l_error IS NOT NULL
295 THEN
296 --
297 hr_utility.set_message(809,'HXC_ALT_CANT_DEL'); -- 'Alias type is used in altenate name definition');
298 hr_utility.trace('------77777');
299 hr_utility.raise_error;
300 --
301 END IF;
302
303 if g_debug then
304 hr_utility.trace('Leaving chk_fk_relation');
305 end if;
306
307 end chk_fk_relation;
308
309 -- ----------------------------------------------------------------------------
310 -- |---------------------------< insert_validate >----------------------------|
311 -- ----------------------------------------------------------------------------
312 Procedure insert_validate
313 (p_rec in hxc_hat_shd.g_rec_type
314 ) is
315 --
316 l_proc varchar2(72);
317 --
318 Begin
319 g_debug:=hr_utility.debug_enabled;
320 if g_debug then
321 l_proc := g_package||'insert_validate';
322 hr_utility.set_location('Entering:'||l_proc, 5);
323 end if;
324 --
325 -- Call all supporting business operations
326 --
327 -- Validate Dependent Attributes
328 --
329 -- Do check for duplicate pref hierarchy name
330 --
331 chk_name ( p_type => p_rec.alias_type,
332 p_reference_object => p_rec.reference_object);
333 --
334 if g_debug then
335 hr_utility.set_location(' Leaving:'||l_proc, 10);
336 end if;
337 End insert_validate;
338 --
339 -- ----------------------------------------------------------------------------
340 -- |---------------------------< update_validate >----------------------------|
341 -- ----------------------------------------------------------------------------
342 Procedure update_validate
343 (p_rec in hxc_hat_shd.g_rec_type
344 ) is
345 --
346 l_proc varchar2(72);
347 --
348 Begin
349 g_debug:=hr_utility.debug_enabled;
350 if g_debug then
351 l_proc := g_package||'update_validate';
352 hr_utility.set_location('Entering:'||l_proc, 5);
353 end if;
354 --
355 -- Call all supporting business operations
356 -- Validate Dependent Attributes
357 --
358 chk_non_updateable_args
359 (p_rec => p_rec
360 );
361 --
362 --
363 -- Do check for duplicate pref hierarchy name
364 --
365 chk_name ( p_type => p_rec.alias_type,
366 p_reference_object =>p_rec.reference_object);
367 --
368 if g_debug then
369 hr_utility.set_location(' Leaving:'||l_proc, 10);
370 end if;
371 End update_validate;
372 --
373 -- ----------------------------------------------------------------------------
374 -- |---------------------------< delete_validate >----------------------------|
375 -- ----------------------------------------------------------------------------
376 Procedure delete_validate
377 (p_rec in hxc_hat_shd.g_rec_type
378 ) is
379 --
380 l_proc varchar2(72);
381 --
382 Begin
383 g_debug:=hr_utility.debug_enabled;
384 if g_debug then
385 l_proc := g_package||'delete_validate';
386 hr_utility.set_location('Entering:'||l_proc, 5);
387 end if;
388 --
389 -- Call all supporting business operations
390 --
391 -- Check if the alternate type definition is not used by alternate name
392 chk_fk_relation(p_rec.alias_type_id);
393
394 if g_debug then
395 hr_utility.set_location(' Leaving:'||l_proc, 10);
396 end if;
397 End delete_validate;
398 --
399 end hxc_hat_bus;