1 Package Body hr_pde_bus as
2 /* $Header: hrpderhi.pkb 120.0 2005/09/23 06:44 adhunter noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_pde_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_person_deplymt_eit_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |---------------------< return_legislation_code >-------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Function return_legislation_code
21 (p_person_deplymt_eit_id in number
22 )
23 Return Varchar2 Is
24 --
25 -- Declare cursor
26 --
27 cursor csr_leg_code is
28 select pbg.legislation_code
29 from per_business_groups_perf pbg
30 , hr_person_deplymt_eits pde
31 , hr_person_deployments pdt
32 where pde.person_deplymt_eit_id = p_person_deplymt_eit_id
33 and pde.person_deployment_id = pdt.person_deployment_id
34 and pbg.business_group_id = pdt.to_business_group_id;
35 --
36 -- Declare local variables
37 --
38 l_legislation_code varchar2(150);
39 l_proc varchar2(72) := g_package||'return_legislation_code';
40 --
41 Begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'person_deplymt_eit_id'
50 ,p_argument_value => p_person_deplymt_eit_id
51 );
52 --
53 if ( nvl(hr_pde_bus.g_person_deplymt_eit_id, hr_api.g_number)
54 = p_person_deplymt_eit_id) then
55 --
56 -- The legislation code has already been found with a previous
57 -- call to this function. Just return the value in the global
58 -- variable.
59 --
60 l_legislation_code := hr_pde_bus.g_legislation_code;
61 hr_utility.set_location(l_proc, 20);
62 else
63 --
64 -- The ID is different to the last call to this function
65 -- or this is the first call to this function.
66 --
67 open csr_leg_code;
68 fetch csr_leg_code into l_legislation_code;
69 --
70 if csr_leg_code%notfound then
71 --
72 -- The primary key is invalid therefore we must error
73 --
74 close csr_leg_code;
75 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
76 fnd_message.raise_error;
77 end if;
78 hr_utility.set_location(l_proc,30);
79 --
80 -- Set the global variables so the values are
81 -- available for the next call to this function.
82 --
83 close csr_leg_code;
84 hr_pde_bus.g_person_deplymt_eit_id := p_person_deplymt_eit_id;
85 hr_pde_bus.g_legislation_code := l_legislation_code;
86 end if;
87 hr_utility.set_location(' Leaving:'|| l_proc, 40);
88 return l_legislation_code;
89 end return_legislation_code;
90 --
91 -- ----------------------------------------------------------------------------
92 -- |-----------------------< chk_non_updateable_args >------------------------|
93 -- ----------------------------------------------------------------------------
94 -- {Start Of Comments}
95 --
96 -- Description:
97 -- This procedure is used to ensure that non updateable attributes have
98 -- not been updated. If an attribute has been updated an error is generated.
99 --
100 -- Pre Conditions:
101 -- g_old_rec has been populated with details of the values currently in
102 -- the database.
103 --
104 -- In Arguments:
105 -- p_rec has been populated with the updated values the user would like the
106 -- record set to.
107 --
108 -- Post Success:
109 -- Processing continues if all the non updateable attributes have not
110 -- changed.
111 --
112 -- Post Failure:
113 -- An application error is raised if any of the non updatable attributes
114 -- have been altered.
115 --
116 -- {End Of Comments}
117 -- ----------------------------------------------------------------------------
118 Procedure chk_non_updateable_args
119 (p_rec in hr_pde_shd.g_rec_type
120 ) IS
121 --
122 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
123 l_error exception;
124 l_argument varchar2(30);
125 --
126 Begin
127 --
128 hr_utility.set_location('Entering:'|| l_proc,10);
129 --
130 -- Only proceed with the validation if a row exists for the current
131 -- record in the HR Schema.
132 --
133 IF NOT hr_pde_shd.api_updating
134 (p_person_deplymt_eit_id => p_rec.person_deplymt_eit_id
135 ,p_object_version_number => p_rec.object_version_number
136 ) THEN
137 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
138 fnd_message.set_token('PROCEDURE ', l_proc);
139 fnd_message.set_token('STEP ', '5');
140 fnd_message.raise_error;
141 END IF;
142 --
143 hr_utility.set_location (l_proc, 20);
144 --
145 if nvl(p_rec.person_deployment_id,hr_api.g_number)
146 <> nvl(hr_pde_shd.g_old_rec.person_deployment_id,hr_api.g_number) then
147 l_argument := 'person_deployment_id';
148 raise l_error;
149 end if;
150 --
151 if nvl(p_rec.person_extra_info_id,hr_api.g_number)
152 <> nvl(hr_pde_shd.g_old_rec.person_extra_info_id,hr_api.g_number) then
153 l_argument := 'person_extra_info_id';
154 raise l_error;
155 end if;
156 --
157 hr_utility.set_location(' Leaving : '|| l_proc, 30);
158 --
159 exception
160 when l_error then
161 hr_api.argument_changed_error
162 (p_api_name => l_proc
163 ,p_argument => l_argument);
164 when others then
165 raise;
166 End chk_non_updateable_args;
167 --
168 -- ---------------------------------------------------------------------------
169 -- |------------------------< chk_person_deployment_id >---------------------|
170 -- ---------------------------------------------------------------------------
171 --
172 -- Desciption:
173 -- This procedures validates that the person deployment id exists on the
174 -- HR_PERSON_DEPLOYMENTS table
175 --
176 -- Pre-conditions :
177 -- None
178 --
179 -- In Parameters :
180 -- p_person_deployment_id
181 --
182 -- Post Success :
183 -- Processing continues if the person deployment id is valid
184 --
185 -- Post Failure :
186 -- An application error will be raised and processing is terminated if the
187 -- person deployment id is invalid
188 --
189 -- Access Status :
190 -- Internal Row Handler Use only.
191 --
192 -- {End of Comments}
193 --
194 -- ---------------------------------------------------------------------------
195 Procedure chk_person_deployment_id
196 (p_person_deployment_id in hr_person_deplymt_eits.person_deployment_id%type
197 ) is
198 --
199 l_proc varchar2(72) := g_package||'chk_person_deployment_id';
200 l_dummy number;
201 --
202 cursor csr_deployment is
203 select 1
204 from hr_person_deployments
205 where person_deployment_id = p_person_deployment_id;
206 --
207 begin
208 hr_utility.set_location('Entering:'||l_proc, 10);
209 --
210 -- Check mandatory parameters have been set
211 --
212 hr_api.mandatory_arg_error
213 (
214 p_api_name => l_proc,
215 p_argument => 'person_deployment_id',
216 p_argument_value => p_person_deployment_id
217 );
218 --
219 open csr_deployment;
220 fetch csr_deployment into l_dummy;
221 if csr_deployment%notfound then
222 close csr_deployment;
223 fnd_message.set_name('PER','HR_449609_DPL_NOT_EXIST');
224 fnd_message.raise_error;
225 else
226 close csr_deployment;
227 end if;
228 --
229 hr_utility.set_location('Leaving:'||l_proc, 20);
230 end chk_person_deployment_id;
231 --
232 -- ---------------------------------------------------------------------------
233 -- |------------------------< chk_person_extra_info_id >---------------------|
234 -- ---------------------------------------------------------------------------
235 --
236 -- Desciption:
237 -- This procedures validates that the person extra info id exists on the
238 -- PER_PEOPLE_EXTRA_INFO table
239 --
240 -- Pre-conditions :
241 -- None
242 --
243 -- In Parameters :
244 -- p_person_extra_info_id
245 --
246 -- Post Success :
247 -- Processing continues if the person extra info id is valid
248 --
249 -- Post Failure :
250 -- An application error will be raised and processing is terminated if the
251 -- person extra info id is invalid
252 --
253 -- Access Status :
254 -- Internal Row Handler Use only.
255 --
256 -- {End of Comments}
257 --
258 -- ---------------------------------------------------------------------------
259 Procedure chk_person_extra_info_id
260 (p_person_extra_info_id in hr_person_deplymt_eits.person_extra_info_id%type
261 ,p_person_deployment_id in hr_person_deplymt_eits.person_deployment_id%type
262 ) is
263 --
264 l_proc varchar2(72) := g_package||'chk_person_extra_info_id';
265 l_dummy number;
266 l_eit_leg varchar2(30);
267 l_host_bg_leg varchar2(30);
268 --
269 cursor csr_extra_info is
270 select 1
271 from per_people_extra_info
272 where person_extra_info_id = p_person_extra_info_id;
273 --
274 cursor csr_check_person is
275 select pei.person_id
276 from per_people_extra_info pei,
277 hr_person_deployments pdt
278 where pdt.person_deployment_id = p_person_deployment_id
279 and pei.person_extra_info_id = p_person_extra_info_id
280 and pdt.from_person_id = pei.person_id;
281 --
282 cursor csr_get_eit_leg is
283 select pit.legislation_code
284 from per_people_info_types pit,
285 per_people_extra_info pei
286 where pei.person_extra_info_id = p_person_extra_info_id
287 and pei.information_type = pit.information_type;
288 --
289 cursor csr_get_host_bg_leg is
290 select legislation_code
291 from hr_person_deployments pdt,
292 per_business_groups_perf bg
293 where pdt.person_deployment_id = p_person_deployment_id
294 and pdt.to_business_group_id = bg.business_group_id;
295 --
296 begin
297 hr_utility.set_location('Entering:'||l_proc, 10);
298 --
299 -- Check mandatory parameters have been set
300 --
301 hr_api.mandatory_arg_error
302 (
303 p_api_name => l_proc,
304 p_argument => 'person_deployment_id',
305 p_argument_value => p_person_deployment_id
306 );
307 --
308 hr_api.mandatory_arg_error
309 (
310 p_api_name => l_proc,
311 p_argument => 'person_extra_info_id_id',
312 p_argument_value => p_person_extra_info_id
313 );
314 --
315 open csr_extra_info;
316 fetch csr_extra_info into l_dummy;
317 if csr_extra_info%found then
318 close csr_extra_info;
319 --
320 open csr_check_person;
321 fetch csr_check_person into l_dummy;
322 if csr_check_person%found then
323 close csr_check_person;
324 --
325 open csr_get_eit_leg;
326 fetch csr_get_eit_leg into l_eit_leg;
327 close csr_get_eit_leg;
328 --
329 if l_eit_leg is not null then
330 --
331 open csr_get_host_bg_leg;
332 fetch csr_get_host_bg_leg into l_host_bg_leg;
333 close csr_get_host_bg_leg;
334 --
335 if l_eit_leg <> l_host_bg_leg then
336 fnd_message.set_name('PER','HR_449653_PDE_EIT_WRONG_LEG');
337 fnd_message.raise_error;
338 end if;
339 end if;
340 else --EIT does not belong to right person
341 close csr_check_person;
342 fnd_message.set_name('PER','HR_449612_PDE_WRONG_PERSON');
343 fnd_message.raise_error;
344 end if;
345 else --EIT does not exist
346 close csr_extra_info;
347 fnd_message.set_name('PER','HR_449613_PDE_EIT_NOT_EXIST');
348 fnd_message.raise_error;
349 end if;
350 --
351 end chk_person_extra_info_id;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |---------------------------< insert_validate >----------------------------|
355 -- ----------------------------------------------------------------------------
356 Procedure insert_validate
357 (p_rec in hr_pde_shd.g_rec_type
358 ) is
359 --
360 l_proc varchar2(72) := g_package||'insert_validate';
361 --
362 Begin
363 hr_utility.set_location('Entering:'||l_proc, 5);
364 --
365 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS
366 --
367 -- Call all supporting business operations
368 --
369 -- Validate Dependent Attributes
370 --
371 hr_pde_bus.chk_person_deployment_id
372 (p_person_deployment_id => p_rec.person_deployment_id);
373 --
374 hr_pde_bus.chk_person_extra_info_id
375 (p_person_extra_info_id => p_rec.person_extra_info_id
376 ,p_person_deployment_id => p_rec.person_deployment_id);
377 --
378 hr_utility.set_location(' Leaving:'||l_proc, 10);
379 End insert_validate;
380 --
381 -- ----------------------------------------------------------------------------
382 -- |---------------------------< update_validate >----------------------------|
383 -- ----------------------------------------------------------------------------
384 Procedure update_validate
385 (p_rec in hr_pde_shd.g_rec_type
386 ) is
387 --
388 l_proc varchar2(72) := g_package||'update_validate';
389 --
390 Begin
391 hr_utility.set_location('Entering:'||l_proc, 5);
392 --
393 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS
394 --
395 -- Call all supporting business operations
396 --
397 -- Validate Dependent Attributes
398 --
399 chk_non_updateable_args
400 (p_rec => p_rec
401 );
402 --
403 -- None of the columns are user-updateable so no other checks are necessary
404 --
405 hr_utility.set_location(' Leaving:'||l_proc, 10);
406 End update_validate;
407 --
408 -- ----------------------------------------------------------------------------
409 -- |---------------------------< delete_validate >----------------------------|
410 -- ----------------------------------------------------------------------------
411 Procedure delete_validate
412 (p_rec in hr_pde_shd.g_rec_type
413 ) is
414 --
415 l_proc varchar2(72) := g_package||'delete_validate';
416 --
417 Begin
418 hr_utility.set_location('Entering:'||l_proc, 5);
419 --
420 -- Call all supporting business operations
421 --
422 hr_utility.set_location(' Leaving:'||l_proc, 10);
423 End delete_validate;
424 --
425 end hr_pde_bus;