1 Package Body pqh_bre_bus as
2 /* $Header: pqbrerhi.pkb 115.6 2003/06/04 08:19:51 ggnanagu noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_bre_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_reallocation_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_pool_id in number
22 ,p_transaction_type in varchar2
23 ,p_txn_detail_id in number
24 ,p_associated_column1 in varchar2
25 ) is
26 --
27 -- Declare cursor
28 --
29 cursor csr_trnx_dtl_sec_grp is
30 select pbg.security_group_id
31 from per_business_groups pbg
32 , pqh_budget_pools bpl
33 where bpl.pool_id = p_pool_id
34 and pbg.business_group_id = bpl.business_group_id;
35
36 cursor csr_trnx_amt_sec_grp is
37 select pbg.security_group_id
38 from per_business_groups pbg
39 , pqh_bdgt_pool_realloctions bre
40 , pqh_budget_pools bpl
41 where bre.reallocation_id = p_txn_detail_id
42 and bre.pool_id = bpl.pool_id
43 and pbg.business_group_id = bpl.business_group_id;
44 --
45 -- Declare local variables
46 --
47 l_security_group_id number;
48 l_proc varchar2(72) := g_package||'set_security_group_id';
49 --
50 begin
51 --
52 hr_utility.set_location('Entering:'|| l_proc, 10);
53 --
54 -- Ensure that all the mandatory parameter are not null
55 --
56 hr_api.mandatory_arg_error
57 (p_api_name => l_proc
58 ,p_argument => 'transaction_type'
59 ,p_argument_value => p_transaction_type );
60 if p_transaction_type IN ('D','R') then
61 hr_api.mandatory_arg_error
62 (p_api_name => l_proc
63 ,p_argument => 'pool_id'
64 ,p_argument_value => p_pool_id
65 );
66 elsif p_transaction_type IN ('DD','RD') then
67 hr_api.mandatory_arg_error
68 (p_api_name => l_proc
69 ,p_argument => 'txn_detail_id'
70 ,p_argument_value => p_txn_detail_id);
71 end if;
72 --
73 if p_transaction_type IN ('D','R') then
74 open csr_trnx_dtl_sec_grp;
75 fetch csr_trnx_dtl_sec_grp into l_security_group_id;
76 --
77 if csr_trnx_dtl_sec_grp%notfound then
78 --
79 close csr_trnx_dtl_sec_grp;
80 --
81 -- The primary key is invalid therefore we must error
82 --
83 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
84 hr_multi_message.add
85 (p_associated_column1
86 => nvl(p_associated_column1,'POOL_ID')
87 );
88 --
89 else
90 close csr_trnx_dtl_sec_grp;
91 --
92 -- Set the security_group_id in CLIENT_INFO
93 --
94 hr_api.set_security_group_id
95 (p_security_group_id => l_security_group_id
96 );
97 end if;
98 elsif p_transaction_type IN ('DD','RD') then
99 open csr_trnx_amt_sec_grp;
100 fetch csr_trnx_amt_sec_grp into l_security_group_id;
101 --
102 if csr_trnx_amt_sec_grp%notfound then
103 --
104 close csr_trnx_amt_sec_grp;
105 --
106 -- The primary key is invalid therefore we must error
107 --
108 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
109 hr_multi_message.add
110 (p_associated_column1
111 => nvl(p_associated_column1,'txn_detail_id')
112 );
113 --
114 else
115 close csr_trnx_amt_sec_grp;
116 --
117 -- Set the security_group_id in CLIENT_INFO
118 --
119 hr_api.set_security_group_id
120 (p_security_group_id => l_security_group_id
121 );
122 end if;
123 end if;
124 --
125 hr_utility.set_location(' Leaving:'|| l_proc, 20);
126 --
127 end set_security_group_id;
128 --
129 -- ---------------------------------------------------------------------------
130 -- |---------------------< return_legislation_code >-------------------------|
131 -- ---------------------------------------------------------------------------
132 --
133 Function return_legislation_code
134 (p_reallocation_id in number
135 )
136 Return Varchar2 Is
137 --
138 -- Declare cursor
139 --
140 cursor csr_leg_code is
141 select pbg.legislation_code
142 from per_business_groups pbg
143 , pqh_bdgt_pool_realloctions bre
144 , pqh_budget_pools bpl
145 where bre.reallocation_id = p_reallocation_id
146 and ( (bre.transaction_type in ('D','R')
147 and bre.pool_id = bpl.pool_id)
148 OR (bre.transaction_type in ('DD','RD')
149 and bpl.pool_id = (select txn_detail_id
150 from pqh_bdgt_pool_realloctions
151 where reallocation_id = p_reallocation_id)) )
152 and pbg.business_group_id = bpl.business_group_id;
153 --
154 -- Declare local variables
155 --
156 l_legislation_code varchar2(150);
157 l_proc varchar2(72) := g_package||'return_legislation_code';
158 --
159 Begin
160 --
161 hr_utility.set_location('Entering:'|| l_proc, 10);
162 --
163 -- Ensure that all the mandatory parameter are not null
164 --
165 hr_api.mandatory_arg_error
166 (p_api_name => l_proc
167 ,p_argument => 'reallocation_id'
168 ,p_argument_value => p_reallocation_id
169 );
170 --
171 if ( nvl(pqh_bre_bus.g_reallocation_id, hr_api.g_number)
172 = p_reallocation_id) then
173 --
174 -- The legislation code has already been found with a previous
175 -- call to this function. Just return the value in the global
176 -- variable.
177 --
178 l_legislation_code := pqh_bre_bus.g_legislation_code;
179 hr_utility.set_location(l_proc, 20);
180 else
181 --
182 -- The ID is different to the last call to this function
183 -- or this is the first call to this function.
184 --
185 open csr_leg_code;
186 fetch csr_leg_code into l_legislation_code;
187 --
188 if csr_leg_code%notfound then
189 --
190 -- The primary key is invalid therefore we must error
191 --
192 close csr_leg_code;
193 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
194 fnd_message.raise_error;
195 end if;
196 hr_utility.set_location(l_proc,30);
197 --
198 -- Set the global variables so the values are
199 -- available for the next call to this function.
200 --
201 close csr_leg_code;
202 pqh_bre_bus.g_reallocation_id := p_reallocation_id;
203 pqh_bre_bus.g_legislation_code := l_legislation_code;
204 end if;
205 hr_utility.set_location(' Leaving:'|| l_proc, 40);
206 return l_legislation_code;
207 end return_legislation_code;
208 --
209 -- ----------------------------------------------------------------------------
210 -- |-----------------------< chk_non_updateable_args >------------------------|
211 -- ----------------------------------------------------------------------------
212 -- {Start Of Comments}
213 --
214 -- Description:
215 -- This procedure is used to ensure that non updateable attributes have
216 -- not been updated. If an attribute has been updated an error is generated.
217 --
218 -- Pre Conditions:
219 -- g_old_rec has been populated with details of the values currently in
220 -- the database.
221 --
222 -- In Arguments:
223 -- p_rec has been populated with the updated values the user would like the
224 -- record set to.
225 --
226 -- Post Success:
227 -- Processing continues if all the non updateable attributes have not
228 -- changed.
229 --
230 -- Post Failure:
231 -- An application error is raised if any of the non updatable attributes
232 -- have been altered.
233 --
234 -- {End Of Comments}
235 -- ----------------------------------------------------------------------------
236 Procedure chk_non_updateable_args
237 (p_effective_date in date
238 ,p_rec in pqh_bre_shd.g_rec_type
239 ) IS
240 --
241 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
242 --
243 Begin
244 --
245 -- Only proceed with the validation if a row exists for the current
246 -- record in the HR Schema.
247 --
248 IF NOT pqh_bre_shd.api_updating
249 (p_reallocation_id => p_rec.reallocation_id
250 ,p_object_version_number => p_rec.object_version_number
251 ) THEN
252 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
253 fnd_message.set_token('PROCEDURE ', l_proc);
254 fnd_message.set_token('STEP ', '5');
255 fnd_message.raise_error;
256 END IF;
257 --
258 -- Add checks to ensure non-updateable args have
259 -- not been updated.
260 --
261 End chk_non_updateable_args;
262 --
263 --
264 -- ----------------------------------------------------------------------------
265 -- |------< chk_reallocation_id >------|
266 -- ----------------------------------------------------------------------------
267 --
268 -- Description
269 -- This procedure is used to check that the primary key for the table
270 -- is created properly. It should be null on insert and
271 -- should not be able to be updated.
272 --
273 -- Pre Conditions
274 -- None.
275 --
276 -- In Parameters
277 -- reallocation_id PK of record being inserted or updated.
278 -- object_version_number Object version number of record being
279 -- inserted or updated.
280 --
281 -- Post Success
282 -- Processing continues
283 --
284 -- Post Failure
285 -- Errors handled by the procedure
286 --
287 -- Access Status
288 -- Internal table handler use only.
289 --
290 Procedure chk_reallocation_id(p_reallocation_id in number,
291 p_object_version_number in number) is
292 --
293 l_proc varchar2(72) := g_package||'chk_reallocation_id';
294 l_api_updating boolean;
295 --
296 Begin
297 --
298 hr_utility.set_location('Entering:'||l_proc, 5);
299 --
300 l_api_updating := pqh_bre_shd.api_updating
301 (p_reallocation_id => p_reallocation_id,
302 p_object_version_number => p_object_version_number);
303 --
304 if (l_api_updating
305 and nvl(p_reallocation_id,hr_api.g_number)
306 <> pqh_bre_shd.g_old_rec.reallocation_id) then
307 --
308 -- raise error as PK has changed
309 --
310 pqh_bre_shd.constraint_error('PQH_BDGT_POOL_REALLOCTIONS');
311 --
312 elsif not l_api_updating then
313 --
314 -- check if PK is null
315 --
316 if p_reallocation_id is not null then
317 --
318 -- raise error as PK is not null
319 --
320 pqh_bre_shd.constraint_error('PQH_BDGT_POOL_REALLOCTIONS');
321 --
322 end if;
323 --
324 end if;
325 --
326 hr_utility.set_location('Leaving:'||l_proc, 10);
327 --
328 End chk_reallocation_id;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |------< chk_pool_id >------|
332 -- ----------------------------------------------------------------------------
333 --
334 -- Description
335 -- This procedure checks that a referenced foreign key actually exists
336 -- in the referenced table.
337 --
338 -- Pre-Conditions
339 -- None.
340 --
341 -- In Parameters
342 -- p_reallocation_id PK
343 -- p_pool_id ID of FK column
344 -- p_object_version_number object version number
345 --
346 -- Post Success
347 -- Processing continues
348 --
349 -- Post Failure
350 -- Error raised.
351 --
352 -- Access Status
353 -- Internal table handler use only.
354 --
355 Procedure chk_pool_id (p_reallocation_id in number,
356 p_pool_id in number default null,
357 p_transaction_type in varchar2,
358 p_txn_detail_id in number default null,
359 p_object_version_number in number) is
360 --
361 l_proc varchar2(72) := g_package||'chk_pool_id';
362 l_api_updating boolean;
363 l_dummy varchar2(1);
364 --
365 cursor c1 is
366 select null
367 from pqh_budget_pools a
368 where a.pool_id = p_pool_id;
369 --
370 cursor c2 is
371 select null
372 from pqh_bdgt_pool_realloctions a
373 where a.reallocation_id = p_txn_detail_id;
374 Begin
375 --
376 hr_utility.set_location('Entering:'||l_proc,5);
377 --
378 IF p_transaction_type IN ('D', 'R') THEN
382 p_object_version_number => p_object_version_number);
379 IF p_pool_id IS NOT NULL THEN
380 l_api_updating := pqh_bre_shd.api_updating
381 (p_reallocation_id => p_reallocation_id,
383 --
384 if (l_api_updating
385 and nvl(p_pool_id,hr_api.g_number)
386 <> nvl(pqh_bre_shd.g_old_rec.pool_id,hr_api.g_number)
387 or not l_api_updating) then
388 --
389 -- check if pool_id value exists in pqh_budget_pools table
390 --
391 open c1;
392 --
393 fetch c1 into l_dummy;
394 if c1%notfound then
395 --
396 close c1;
397 --
398 -- raise error as FK does not relate to PK in pqh_budget_pools
399 -- table.
400 --
401 pqh_bre_shd.constraint_error('PQH_BDGT_POOL_REALLOCTIONS_FK1');
402 --
403 end if;
404 --
405 close c1;
406 --
407 end if;
408 END IF;
409 --
410 END IF;
411 --
412 IF p_transaction_type IN ('DD', 'RD') THEN
413 IF p_txn_detail_id IS NOT NULL THEN
414 l_api_updating := pqh_bre_shd.api_updating
415 (p_reallocation_id => p_reallocation_id,
416 p_object_version_number => p_object_version_number);
417 --
418 if (l_api_updating
419 and nvl(p_txn_detail_id,hr_api.g_number)
420 <> nvl(pqh_bre_shd.g_old_rec.txn_detail_id,hr_api.g_number)
421 or not l_api_updating) then
422 --
423 -- check if txn_detail_id value exists in pqh_bdgt_pool_realloctions table
424 --
425 open c2;
426 --
427 fetch c2 into l_dummy;
428 if c2%notfound then
429 --
430 close c2;
431 --
432 -- raise error as FK does not relate to PK in pqh_bdgt_pool_realloctions
433 -- table.
434 --
435 pqh_bre_shd.constraint_error('PQH_BDGT_POOL_REALLOCTIONS_FK1');
436 --
437 end if;
438 --
439 close c2;
440 --
441 end if;
442 END IF;
443 --
444 END IF;
445 hr_utility.set_location('Leaving:'||l_proc,10);
446 --
447 End chk_pool_id;
448 --
449 -- ----------------------------------------------------------------------------
450 -- |---------------------------< chk_period_amount >----------------------------|
451 -- ----------------------------------------------------------------------------
452 Procedure chk_period_amount(p_reallocation_id IN number,
453 p_object_version_number IN number,
454 p_transaction_type IN varchar2,
455 p_reallocation_amt IN NUMBER,
456 p_reserved_amt IN number) IS
457 --
458 l_proc varchar2(72) := g_package||'chk_period_amount';
459 l_api_updating boolean;
460 --
461 BEGIN
462 hr_utility.set_location('Entering:'||l_proc, 5);
463 l_api_updating := pqh_bre_shd.api_updating
464 (p_reallocation_id => p_reallocation_id,
465 p_object_version_number => p_object_version_number);
466 --
467 if (l_api_updating
468 and nvl(p_reserved_amt,hr_api.g_number)
469 <> nvl(pqh_bre_shd.g_old_rec.reserved_amt,hr_api.g_number)
470 or not l_api_updating) then
471 --
472 -- check if reserved_amt is greater than 0.
473 --
474 If p_reserved_amt < 0 then
475 hr_utility.set_message(8302,'PQH_BGT_REALLOC_DONOR_RES_NEG');
476 hr_utility.raise_error;
477 End if;
478 If p_reallocation_amt < 0 THEN
479 hr_utility.set_message(8302,'PQH_BGT_REALLOC_DONOR_REA_NEG');
480 hr_utility.raise_error;
481 End if;
482
483 END IF;
484 --
485 IF p_transaction_type = 'DD' THEN
486 IF NVL(p_reallocation_amt,0) <= 0 AND NVL(p_reserved_amt,0) <= 0 THEN
487 hr_utility.set_message(8302,'PQH_BGT_DNR_PRD_AMOUNT');
488 hr_utility.raise_error;
489 END IF;
490 END IF;
491 hr_utility.set_location('Leaving:'||l_proc, 10);
492 End chk_period_amount;
493 -- ----------------------------------------------------------------------------
494 -- |---------------------------< insert_validate >----------------------------|
495 -- ----------------------------------------------------------------------------
496 Procedure insert_validate
497 (p_effective_date in date
498 ,p_rec in pqh_bre_shd.g_rec_type
499 ) is
500 --
501 l_proc varchar2(72) := g_package||'insert_validate';
502 --
503 Begin
504 hr_utility.set_location('Entering:'||l_proc, 5);
505 --
506 -- As this table does not have a mandatory business_group_id
507 -- column, ensure client_info is populated by calling a suitable
508 -- pqh_bre_bus.set_security_group_id procedure
509 --
510 pqh_bre_bus.set_security_group_id(p_rec.pool_id,
511 p_rec.transaction_type,
512 p_rec.txn_detail_id);
513 --
514 -- Call all supporting business operations
515 --
516 --
517 chk_reallocation_id
518 (p_reallocation_id => p_rec.reallocation_id,
519 p_object_version_number => p_rec.object_version_number);
520 --
521 chk_pool_id
522 (p_reallocation_id => p_rec.reallocation_id,
523 p_pool_id => p_rec.pool_id,
524 p_transaction_type => p_rec.transaction_type,
525 p_txn_detail_id => p_rec.txn_detail_id,
526 p_object_version_number => p_rec.object_version_number);
527 --
528 chk_period_amount
529 (p_reallocation_id => p_rec.reallocation_id,
530 p_transaction_type => p_rec.transaction_type,
531 p_reserved_amt => p_rec.reserved_amt,
532 p_reallocation_amt => p_rec.reallocation_amt,
533 p_object_version_number => p_rec.object_version_number);
534 --
535 -- Validate Dependent Attributes
536 --
537 --
538 hr_utility.set_location(' Leaving:'||l_proc, 10);
539 End insert_validate;
540 --
541 -- ----------------------------------------------------------------------------
542 -- |---------------------------< update_validate >----------------------------|
543 -- ----------------------------------------------------------------------------
544 Procedure update_validate
545 (p_effective_date in date
546 ,p_rec in pqh_bre_shd.g_rec_type
547 ) is
548 --
549 l_proc varchar2(72) := g_package||'update_validate';
550 --
551 Begin
552 hr_utility.set_location('Entering:'||l_proc, 5);
553 --
554 -- Call all supporting business operations
555 --
556 --
557 chk_reallocation_id
558 (p_reallocation_id => p_rec.reallocation_id,
559 p_object_version_number => p_rec.object_version_number);
560 --
561 --
562 chk_pool_id
563 (p_reallocation_id => p_rec.reallocation_id,
564 p_pool_id => p_rec.pool_id,
565 p_transaction_type => p_rec.transaction_type,
566 p_txn_detail_id => p_rec.txn_detail_id,
567 p_object_version_number => p_rec.object_version_number);
568 --
569 chk_period_amount
570 (p_reallocation_id => p_rec.reallocation_id,
571 p_transaction_type => p_rec.transaction_type,
572 p_reserved_amt => p_rec.reserved_amt,
573 p_reallocation_amt => p_rec.reallocation_amt,
574 p_object_version_number => p_rec.object_version_number);
575 --
576 pqh_bre_bus.set_security_group_id(p_rec.pool_id,
577 p_rec.transaction_type,
578 p_rec.txn_detail_id);
579 --
580 -- Validate Dependent Attributes
581 --
582 chk_non_updateable_args
583 (p_effective_date => p_effective_date
584 ,p_rec => p_rec
585 );
586 --
587 --
588 hr_utility.set_location(' Leaving:'||l_proc, 10);
589 End update_validate;
590 --
591 -- ----------------------------------------------------------------------------
592 -- |---------------------------< delete_validate >----------------------------|
593 -- ----------------------------------------------------------------------------
594 Procedure delete_validate
595 (p_rec in pqh_bre_shd.g_rec_type
596 ) is
597 --
598 l_proc varchar2(72) := g_package||'delete_validate';
599 --
600 Begin
601 hr_utility.set_location('Entering:'||l_proc, 5);
602 --
603 -- Call all supporting business operations
604 --
605 hr_utility.set_location(' Leaving:'||l_proc, 10);
606 End delete_validate;
607 --
608 end pqh_bre_bus;
[Home] [Help]