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