[Home] [Help]
PACKAGE BODY: APPS.PQH_STR_BUS
Source
1 Package Body pqh_str_bus as
2 /* $Header: pqstrrhi.pkb 115.10 2004/04/06 05:49 svorugan noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_str_bus.'; -- Global package name
9
10 g_debug boolean := hr_utility.debug_enabled;
11
12 --
13 -- The following two global variables are only to be
14 -- used by the return_legislation_code function.
15 --
16 g_legislation_code varchar2(150) default null;
17 g_stat_situation_rule_id number default null;
18 --
19 -- ---------------------------------------------------------------------------
20 -- |----------------------< set_security_group_id >--------------------------|
21 -- ---------------------------------------------------------------------------
22 --
23 Procedure set_security_group_id
24 (p_stat_situation_rule_id in number
25 ,p_associated_column1 in varchar2 default null
26 ) is
27 --
28 -- Declare cursor
29 --
30 cursor csr_sec_grp is
31 select pbg.security_group_id,
32 pbg.legislation_code
33 from per_business_groups_perf pbg
34 , pqh_fr_stat_situation_rules str
35 , pqh_fr_stat_situations sts
36 where str.stat_situation_rule_id = p_stat_situation_rule_id
37 and str.statutory_situation_id = sts.statutory_situation_id
38 and pbg.business_group_id = sts.business_group_id;
39 --
40 -- Declare local variables
41 --
42 l_security_group_id number;
43 l_proc varchar2(72) := g_package||'set_security_group_id';
44 l_legislation_code varchar2(150);
45 --
46 begin
47 --
48 if g_debug then
49 --
50 hr_utility.set_location('Entering:'|| l_proc, 10);
51 --
52 End if;
53
54 --
55 -- Ensure that all the mandatory parameter are not null
56 --
57 hr_api.mandatory_arg_error
58 (p_api_name => l_proc
59 ,p_argument => 'stat_situation_rule_id'
60 ,p_argument_value => p_stat_situation_rule_id
61 );
62 --
63 open csr_sec_grp;
64 fetch csr_sec_grp into l_security_group_id
65 , l_legislation_code;
66 --
67 if csr_sec_grp%notfound then
68 --
69 close csr_sec_grp;
70 --
71 -- The primary key is invalid therefore we must error
72 --
73 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
74 hr_multi_message.add
75 (p_associated_column1
76 => nvl(p_associated_column1,'STAT_SITUATION_RULE_ID')
77 );
78 --
79 else
80 close csr_sec_grp;
81 --
82 -- Set the security_group_id in CLIENT_INFO
83 --
84 hr_api.set_security_group_id
85 (p_security_group_id => l_security_group_id
86 );
87 --
88 -- Set the sessions legislation context in HR_SESSION_DATA
89 --
90 hr_api.set_legislation_context(l_legislation_code);
91 end if;
92 --
93 if g_debug then
94 --
95 hr_utility.set_location(' Leaving:'|| l_proc, 20);
96 --
97 End if;
98
99 --
100 end set_security_group_id;
101 --
102 -- ---------------------------------------------------------------------------
103 -- |---------------------< return_legislation_code >-------------------------|
104 -- ---------------------------------------------------------------------------
105 --
106 Function return_legislation_code
107 (p_stat_situation_rule_id in number
108 )
109 Return Varchar2 Is
110 --
111 -- Declare cursor
112 --
113 cursor csr_leg_code is
114 select pbg.legislation_code
115 from per_business_groups_perf pbg
116 , pqh_fr_stat_situation_rules str
117 , pqh_fr_stat_situations sts
118 where str.stat_situation_rule_id = p_stat_situation_rule_id
119 and pbg.business_group_id = sts.business_group_id
120 and sts.statutory_situation_id = str.statutory_situation_id;
121 --
122 -- Declare local variables
123 --
124 l_legislation_code varchar2(150);
125 l_proc varchar2(72) := g_package||'return_legislation_code';
126 --
127 Begin
128 --
129 if g_debug then
130 --
131 hr_utility.set_location('Entering:'|| l_proc, 10);
132 --
133 End if;
134 --
135 -- Ensure that all the mandatory parameter are not null
136 --
137 hr_api.mandatory_arg_error
138 (p_api_name => l_proc
139 ,p_argument => 'stat_situation_rule_id'
140 ,p_argument_value => p_stat_situation_rule_id
141 );
142 --
143 if ( nvl(pqh_str_bus.g_stat_situation_rule_id, hr_api.g_number)
144 = p_stat_situation_rule_id) then
145 --
146 -- The legislation code has already been found with a previous
147 -- call to this function. Just return the value in the global
148 -- variable.
149 --
150 l_legislation_code := pqh_str_bus.g_legislation_code;
151
152 if g_debug then
153 --
154 hr_utility.set_location(l_proc, 20);
155 --
156 End if;
157
158 else
159 --
160 -- The ID is different to the last call to this function
161 -- or this is the first call to this function.
162 --
163 open csr_leg_code;
164 fetch csr_leg_code into l_legislation_code;
165 --
166 if csr_leg_code%notfound then
167 --
168 -- The primary key is invalid therefore we must error
169 --
170 close csr_leg_code;
171 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
172 fnd_message.raise_error;
173 end if;
174 if g_debug then
175 --
176 hr_utility.set_location(l_proc,30);
177 --
178 End if;
179 --
180 -- Set the global variables so the values are
181 -- available for the next call to this function.
182 --
183 close csr_leg_code;
184 pqh_str_bus.g_stat_situation_rule_id := p_stat_situation_rule_id;
185 pqh_str_bus.g_legislation_code := l_legislation_code;
186 end if;
187 if g_debug then
188 --
189 hr_utility.set_location(' Leaving:'|| l_proc, 40);
190 --
191 End if;
192 return l_legislation_code;
193 end return_legislation_code;
194 --
195 -- ----------------------------------------------------------------------------
196 -- |-----------------------< chk_non_updateable_args >------------------------|
197 -- ----------------------------------------------------------------------------
198 -- {Start Of Comments}
199 --
200 -- Description:
201 -- This procedure is used to ensure that non updateable attributes have
202 -- not been updated. If an attribute has been updated an error is generated.
203 --
204 -- Pre Conditions:
205 -- g_old_rec has been populated with details of the values currently in
206 -- the database.
207 --
208 -- In Arguments:
209 -- p_rec has been populated with the updated values the user would like the
210 -- record set to.
211 --
212 -- Post Success:
213 -- Processing continues if all the non updateable attributes have not
214 -- changed.
215 --
216 -- Post Failure:
217 -- An application error is raised if any of the non updatable attributes
218 -- have been altered.
219 --
220 -- {End Of Comments}
221 -- ----------------------------------------------------------------------------
222 Procedure chk_non_updateable_args
223 (p_effective_date in date
224 ,p_rec in pqh_str_shd.g_rec_type
225 ) IS
226 --
227 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
228 --
229 Begin
230 --
231 -- Only proceed with the validation if a row exists for the current
232 -- record in the HR Schema.
233 --
234 IF NOT pqh_str_shd.api_updating
235 (p_stat_situation_rule_id => p_rec.stat_situation_rule_id
236 ,p_object_version_number => p_rec.object_version_number
237 ) THEN
238 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
239 fnd_message.set_token('PROCEDURE ', l_proc);
240 fnd_message.set_token('STEP ', '5');
241 fnd_message.raise_error;
242 END IF;
243 --
244 -- No non-updateable args have
245 --
246 End chk_non_updateable_args;
247 --
248 -- ----------------------------------------------------------------------------
249 -- |---------------------------< validations >----------------------------|
250 -- ----------------------------------------------------------------------------
251
252 Procedure validations
253 (p_effective_date in date
254 ,p_rec in pqh_str_shd.g_rec_type
255 ) is
256 --
257 l_proc varchar2(72) := g_package||'validations';
258 l_value varchar2(10);
259
260
261
262
263
264 --
265 Begin
266
267 if g_debug then
268 --
269 hr_utility.set_location('Entering:'||l_proc, 5);
270 --
271 End if;
272 --
273
274
275
276
277 --
278 End validations;
279 --
280 procedure chk_validate_criteria(p_txn_category_attribute_id varchar2)
281 is
282 --
283 Cursor csr_validate_criteria IS
284 Select null
285 from pqh_txn_category_attributes txnAttrs,
286 pqh_transaction_categories txnCats
287 where txnAttrs.transaction_category_id = txnCats.transaction_category_id
288 and txnCats.short_name ='FR_PQH_STAT_SIT_TXN'
289 and txn_category_attribute_id = p_txn_category_attribute_id;
290 --
291 l_value varchar2(100) := null;
292 Begin
293
294 -- Validate Criteria (transaction category attribute)
295 Open csr_validate_criteria;
296 --
297 Fetch csr_validate_criteria into l_value;
298
299 IF csr_validate_criteria%NOTFOUND THEN
300 --
301 fnd_message.set_name('PQH','PQH_FR_INVALID_CRITERIA');
302
303 hr_multi_message.add(p_associated_column1=> 'TXN_CATEGORY_ATTRIBUTE_ID');
304 --
305 END If;
306 --
307 close csr_validate_criteria;
308
309 End chk_validate_criteria;
310 --
311 procedure chk_value_set_existence(p_txn_category_attribute_id varchar2)
312 is
313 Cursor chk_value_set_existence(p_value_set_id varchar2) IS
314 Select null
315 from FND_FLEX_VALUE_SETS
316 where flex_value_set_id = p_value_set_id;
317
318 Cursor csr_get_value_style IS
319 Select value_style_cd ,value_set_id
320 from pqh_txn_category_attributes
321 where txn_category_attribute_id = p_txn_category_attribute_id;
322 --
323 l_value varchar2(100) := null;
324 l_value_style_cd varchar2(100) := null;
325 l_value_set_id varchar2(100) := null;
326 Begin
327
328 Open csr_get_value_style ;
329
330 Fetch csr_get_value_style into l_value_style_cd, l_value_set_id;
331
332 Close csr_get_value_style;
333
334 if (l_value_style_cd ='EXACT' and l_value_set_id is not null) then
335 --
336 Open chk_value_set_existence(l_value_set_id);
337 --
338 Fetch chk_value_set_existence into l_value;
339
340 IF chk_value_set_existence%NOTFOUND THEN
341 --
342 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUESET');
343 fnd_message.set_token('FIELD','FROM_VALUE');
344
345 hr_multi_message.add(p_associated_column1=> 'FROM_VALUE');
346 --
347 END If;
348 --
349 close chk_value_set_existence;
350 --
351 End if;
352
353 End chk_value_set_existence;
354
355 Procedure chk_to_value(p_txn_category_attribute_id number, p_to_value varchar2)
356 is
357
358 Cursor csr_get_value_style IS
359 Select value_style_cd
360 from pqh_txn_category_attributes
361 where txn_category_attribute_id = p_txn_category_attribute_id;
362 --
363 l_value_style_cd varchar2(1000);
364 Begin
365
366 if p_to_value is not null then
367 --
368 Open csr_get_value_style ;
369
370 Fetch csr_get_value_style into l_value_style_cd;
371
372 Close csr_get_value_style;
373
374 if (l_value_style_cd = 'EXACT') then
375
376 --
377 fnd_message.set_name('PQH','PQH_FR_INVALID_TO_VALUE');
378 hr_multi_message.add
379 (p_associated_column1 => 'TO_VALUE');
380 --
381 end if;
382
383 --
384 end if;
385
386 --
387
388
389
390 End chk_to_value;
391
392
393 Procedure chk_from_value (p_txn_category_attribute_id number, p_from_value varchar2,p_to_value varchar2)
394 is
395 --
396 Cursor csr_get_txn_record IS
397 Select value_style_cd
398 from pqh_txn_category_attributes
399 where txn_category_attribute_id = p_txn_category_attribute_id;
400 --
401 l_value_style_cd pqh_txn_category_attributes.value_style_cd%type;
402 --
403 begin
404 --
405 Open csr_get_txn_record ;
406 --
407 Fetch csr_get_txn_record into l_value_style_cd;
408 --
409 Close csr_get_txn_record;
410
411
412 if pqh_fr_stat_sit_util.Is_input_is_valid
413 (p_txn_category_attribute_id, p_from_value) = 'N' then
414 --
415 fnd_message.set_name('PQH','PQH_FR_INVALID_VALUE');
416 fnd_message.set_token('ATTRIBUTE',p_from_value);
417 hr_multi_message.add
418 (p_associated_column1 => 'FROM_VALUE');
419 --
420 else
421 If (l_value_style_cd = 'RANGE') Then
422 --
423 if (to_number(p_from_value) <=0 ) then
424 --
425 fnd_message.set_name('PQH','PQH_FR_STAT_SIT_VALGRT_ZERO');
426 hr_multi_message.add(p_associated_column1=>'FROM_VALUE_MEANING');
427 --
428 end if;
429 --
430 end if;
431 end if;
432
433 end ;
434
435 Procedure chk_from_to_value (p_txn_category_attribute_id number, p_from_value varchar2, p_to_value varchar2)
436 is
437 l_from_value number;
438 l_to_value number;
439 l_field_name varchar2(100);
440 begin
441
442 if (p_from_value is not null and p_to_value is not null) then
443 --
444 if pqh_fr_stat_sit_util.Is_input_is_valid
445 (p_txn_category_attribute_id, p_from_value) = 'Y'
446 and pqh_fr_stat_sit_util.Is_input_is_valid
447 (p_txn_category_attribute_id, p_to_value) = 'Y' then
448 --
449 -- Check input values are number or not
450 Begin
451 --
452 l_field_name := 'FROM_VALUE';
453 l_from_value := to_number(p_from_value);
454 l_field_name := 'TO_VALUE';
455 l_to_value := to_number(p_to_value);
456
457 if ( to_number(p_from_value) > to_number(p_to_value) )
458 then
459 --
460 fnd_message.set_name('PQH','PQH_FR_STAT_CRIT_FROM_TO_ERR');
461 hr_multi_message.add (p_associated_column1 => 'TO_VALUE');
462 --
463 end if;
464 Exception
465 when others then
466 fnd_message.set_name('PQH','PQH_FR_STAT_CRIT_INVALID_FRMTO');
467 fnd_message.set_token('FIELD_NAME',hr_general.decode_lookup('FR_PQH_FORM_PROMPTS',l_field_name));
468 hr_multi_message.add (p_associated_column1 => 'TO_VALUE');
469 End;
470
471 --
472 End if;
473 --
474 End if;
475
476 end chk_from_to_value;
477 -- ----------------------------------------------------------------------------
478 -- |---------------------------< insert_validate >----------------------------|
479 -- ----------------------------------------------------------------------------
480 Procedure insert_validate
481 (p_effective_date in date
482 ,p_rec in pqh_str_shd.g_rec_type
483 ) is
484 --
485 l_proc varchar2(72) := g_package||'insert_validate';
486 --
487 Begin
488
489 if g_debug then
490 --
491 hr_utility.set_location('Entering:'||l_proc, 5);
492 --
493 End if;
494
495 --
496 -- Call all supporting business operations
497 pqh_sts_bus.set_security_group_id
498 (
499 p_statutory_situation_id => p_rec.statutory_situation_id
500 );
501 -- Validate Dependent Attributes
502
503 hr_multi_message.end_validation_set;
504 --
505 chk_value_set_existence(p_rec.txn_category_attribute_id);
506
507 chk_validate_criteria(p_rec.txn_category_attribute_id);
508
509
510 chk_from_value (p_rec.txn_category_attribute_id, p_rec.from_value,p_rec.to_value);
511
512
513 chk_to_value(p_rec.txn_category_attribute_id,p_rec.to_value);
514
515 hr_multi_message.end_validation_set;
516
517 chk_from_to_value(p_rec.txn_category_attribute_id, p_rec.from_value,p_rec.to_value);
518
519 hr_multi_message.end_validation_set;
520 --
521 if g_debug then
522 --
523 hr_utility.set_location(' Leaving:'||l_proc, 10);
524 --
525 End if;
526
527 End insert_validate;
528 --
529 -- ----------------------------------------------------------------------------
530 -- |---------------------------< update_validate >----------------------------|
531 -- ----------------------------------------------------------------------------
532 Procedure update_validate
533 (p_effective_date in date
534 ,p_rec in pqh_str_shd.g_rec_type
535 ) is
536 --
537 l_proc varchar2(72) := g_package||'update_validate';
538 --
539 Begin
540
541 if g_debug then
542 --
543 hr_utility.set_location('Entering:'||l_proc, 5);
544 --
545 End if;
546
547 --
548 -- Call all supporting business operations
549
550 pqh_sts_bus.set_security_group_id
551 (
552 p_statutory_situation_id => p_rec.statutory_situation_id
553 );
554
555 hr_multi_message.end_validation_set;
556
557 --
558
559 chk_validate_criteria(p_rec.txn_category_attribute_id);
560
561 chk_non_updateable_args
562 (p_effective_date => p_effective_date
563 ,p_rec => p_rec
564 );
565
566 hr_multi_message.end_validation_set;
567
568 chk_from_value (p_rec.txn_category_attribute_id, p_rec.from_value,p_rec.to_value);
569
570 chk_to_value(p_rec.txn_category_attribute_id,p_rec.to_value);
571
572 hr_multi_message.end_validation_set;
573
574 chk_from_to_value(p_rec.txn_category_attribute_id, p_rec.from_value,p_rec.to_value);
575
576 hr_multi_message.end_validation_set;
577 --
578 --
579 if g_debug then
580 --
581 hr_utility.set_location(' Leaving:'||l_proc, 10);
582 --
583 End if;
584
585 End update_validate;
586 --
587 -- ----------------------------------------------------------------------------
588 -- |---------------------------< delete_validate >----------------------------|
589 -- ----------------------------------------------------------------------------
590 Procedure delete_validate
591 (p_rec in pqh_str_shd.g_rec_type
592 ) is
593 --
594 l_proc varchar2(72) := g_package||'delete_validate';
595 --
596 Begin
597 if g_debug then
598 --
599 hr_utility.set_location('Entering:'||l_proc, 5);
600 --
601 End if;
602
603 --
604 -- Call all supporting business operations
605 --
606 if g_debug then
607 --
608 hr_utility.set_location(' Leaving:'||l_proc, 10);
609 --
610 End if;
611
612 End delete_validate;
613 --
614 end pqh_str_bus;