[Home] [Help]
PACKAGE BODY: APPS.AME_ATU_BUS
Source
1 Package Body ame_atu_bus as
2 /* $Header: amaturhi.pkb 120.6 2006/02/15 04:04 prasashe noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_atu_bus.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |----------------------< inputToCanonStaticCurUsage >----------------------|
13 -- ----------------------------------------------------------------------------
14 -- {Start Of Comments}
15 --
16 -- Description:
17 -- This procedure is used to convert the input attribute usage to canonical
18 --
19 -- Pre Conditions:
20 --
21 -- In Arguments:
22 --
23 -- Post Success:
24 --
25 -- Post Failure:
26 --
27 -- {End Of Comments}
28 -- ----------------------------------------------------------------------------
29 function inputToCanonStaticCurUsage(p_attribute_id in integer,
30 p_application_id in integer,
31 p_query_string varchar2) return varchar2 as
32 l_proc varchar2(72) := g_package||'inputToCanonStaticCurUsage';
33 amount ame_util.attributeValueType;
34 conversionType ame_util.attributeValueType;
35 convTypeException exception;
36 curCodeException exception;
37 currencyCode ame_util.attributeValueType;
38 errorCode integer;
39 errorMessage ame_util.longestStringType;
40 begin
41 /*
42 The ame_util.parseStaticCurAttValue procedure parses the usage, if it is parse-able;
43 but it doesn't validate the individual values, or convert the amount to canonical format.
44 */
45 ame_util.parseStaticCurAttValue(applicationIdIn => p_application_id,
46 attributeIdIn => p_attribute_id,
47 attributeValueIn => p_query_string,
48 amountOut => amount,
49 localErrorIn => true,
50 currencyOut => currencyCode,
51 conversionTypeOut => conversionType);
52 /* ame_util.inputNumStringToCanonNumString validates and formats the amount. */
53 amount := ame_util.inputNumStringToCanonNumString(inputNumberStringIn => amount,
54 currencyCodeIn => currencyCode);
55 if not ame_util.isCurrencyCodeValid(currencyCodeIn => currencyCode) then
56 fnd_message.set_name('PER', 'AME_400151_ATT_STA_CURR_INV');
57 fnd_message.raise_error;
58 end if;
59 if not ame_util.isConversionTypeValid(conversionTypeIn => conversionType) then
60 fnd_message.set_name('PER', 'AME_400150_ATT_STA_CONV_INV');
61 fnd_message.raise_error;
62 end if;
63 return(amount || ',' || currencyCode || ',' || conversionType);
64 exception
65 when app_exception.application_exception then
66 if hr_multi_message.exception_add
67 (p_associated_column1 => 'QUERY_STRING') then
68 hr_utility.set_location(' Leaving:'|| l_proc, 50);
69 raise;
70 end if;
71 hr_utility.set_location(' Leaving:'|| l_proc, 60);
72 return(null);
73 end inputToCanonStaticCurUsage;
74 -- ---------------------------------------------------------------------
75 -- |---------------------------< isNumber >----------------------------|
76 -- ---------------------------------------------------------------------
77 function isNumber
78 (p_string varchar2) return Boolean Is
79
80 l_num number;
81 l_number_exception exception;
82 PRAGMA EXCEPTION_INIT (l_number_exception, -6502);
83
84 begin
85 --
86 l_num := to_number(p_string);
87 return (true);
88 exception
89 when l_number_exception then
90 return (false);
91 when others then
92 return (false);
93 end isNumber;
94 --
95 --
96 -- ---------------------------------------------------------------------
97 -- |---------------------------< chk_ame_date_format >----------------------------|
98 -- ---------------------------------------------------------------------
99 function chk_ame_date_format
100 (p_string varchar2) return Boolean Is
101 l_date date;
102 begin
103 begin
104 l_date := to_date(p_string,'YYYY:MM:DD:HH24:MI:SS');
105 if instrb(upper(p_string),'JAN',1,1) <> 0 or
106 instrb(upper(p_string),'FEB',1,1) <> 0 or
107 instrb(upper(p_string),'MAR',1,1) <> 0 or
108 instrb(upper(p_string),'APR',1,1) <> 0 or
109 instrb(upper(p_string),'MAY',1,1) <> 0 or
110 instrb(upper(p_string),'JUN',1,1) <> 0 or
111 instrb(upper(p_string),'JUL',1,1) <> 0 or
112 instrb(upper(p_string),'AUG',1,1) <> 0 or
113 instrb(upper(p_string),'SEP',1,1) <> 0 or
114 instrb(upper(p_string),'OCT',1,1) <> 0 or
115 instrb(upper(p_string),'NOV',1,1) <> 0 or
116 instrb(upper(p_string),'DEC',1,1) <> 0 then
117 return (false);
118 else
119 return (true);
120 end if;
121 exception
122 when others then
123 return(false);
124 end;
125 end chk_ame_date_format;
126 --
127 --
128
129 -- ---------------------------------------------------------------------------
130 -- |----------------------< chk_attribute_item_class >--------------------------|
131 -- ---------------------------------------------------------------------------
132 --
133 procedure chk_attribute_item_class
134 (p_application_id in number,
135 p_attribute_id in number
136 ) is
137 l_item_class_id integer;
138 l_exists varchar2(1);
139 l_proc varchar2(72) := g_package||'chk_attribute_item_class';
140 cursor c_sel1 is
141 select null
142 from ame_item_class_usages
143 where application_id = p_application_id and item_class_id =
144 ( select item_class_id from ame_attributes
145 where attribute_id = p_attribute_id and sysdate between start_date
146 and nvl(end_date - ame_util.oneSecond,sysdate))
147 and sysdate between start_date and nvl(end_date - ame_util.oneSecond,sysdate);
148 begin
149 open c_sel1;
150 fetch c_sel1 into l_exists;
151 if c_sel1%notfound then
152 close c_sel1;
153 fnd_message.set_name('PER','AME_400521_ATT_NO_IC_USG_EXIST');
154 fnd_message.raise_error;
155 end if;
156 close c_sel1;
157
158 exception
159 when app_exception.application_exception then
160 if hr_multi_message.exception_add
161 (p_associated_column1 => 'APPLICATION_ID') then
162 hr_utility.set_location(' Leaving:'|| l_proc, 50);
163 raise;
164 end if;
165 hr_utility.set_location(' Leaving:'|| l_proc, 60);
166 end chk_attribute_item_class;
167 --
168 --
169 -- ---------------------------------------------------------------------------
170 -- |----------------------< chk_attribute_approver_type >--------------------------|
171 -- ---------------------------------------------------------------------------
172 --
173 procedure chk_attribute_approver_type
174 (p_application_id in number,
175 p_attribute_id in number
176 ) is
177 l_proc varchar2(72) := g_package||'chk_attribute_approver_type';
178 l_item_class_id integer;
179 l_approver_type_id integer;
180 l_exists varchar2(1);
181 l_variable_value varchar2(200);
182 l_orig_system varchar2(48);
183 begin
184
185 select approver_type_id
186 into l_approver_type_id
187 from ame_attributes
188 where attribute_id = p_attribute_id and
189 sysdate between start_date and
190 nvl(end_date - ame_util.oneSecond, sysdate) ;
191
192 if l_approver_type_id is not null then
193 begin
194 select variable_value
195 into l_variable_Value
196 from ame_config_vars
197 where
198 variable_name = 'allowAllApproverTypes' and
199 application_id = p_application_id and
200 sysdate between start_date and
201 nvl(end_date - ame_util.oneSecond, sysdate);
202
203 --If no transaction-type-specific config var exists, revert to the application-wide value. */
204
205 exception
206 when no_data_found then
207 select variable_value
208 into l_variable_Value
209 from ame_config_vars
210 where
211 variable_name = 'allowAllApproverTypes' and
212 application_id = 0 and
213 sysdate between start_date and
214 nvl(end_date - ame_util.oneSecond, sysdate) ;
215 end ;
216 if l_variable_Value = 'no' then
217 select orig_system into l_orig_system from ame_approver_types
218 where approver_type_id = l_approver_type_id and
219 sysdate between start_date and
220 nvl(end_date - ame_util.oneSecond, sysdate) ;
221 if l_orig_system = 'POS' then
222 fnd_message.set_name('PER','AME_400522_ATT_NO_APPR_TYP_USG');
223 fnd_message.raise_error;
224 end if;
225 end if;
226 end if;
227
228 exception
229 when app_exception.application_exception then
230 if hr_multi_message.exception_add
234 end if;
231 (p_associated_column1 => 'APPLICATION_ID') then
232 hr_utility.set_location(' Leaving:'|| l_proc, 50);
233 raise;
235 hr_utility.set_location(' Leaving:'|| l_proc, 60);
236
237 end chk_attribute_approver_type;
238 --
239 --
240
241 --
242 -- ---------------------------------------------------------------------------
243 -- |----------------------< chk_value_set_id >--------------------------|
244 -- ---------------------------------------------------------------------------
245 --
246 procedure chk_value_set_id
247 (p_value_set_id in number)is
248 l_count number;
249 l_proc varchar2(72) := g_package||'chk_value_set_id';
250 begin
251 if p_value_set_id is not null then
252 select count(*) into l_count
253 from fnd_flex_value_sets
254 where flex_value_set_id = p_value_set_id and
255 instr(flex_value_set_name,'$') = 0 ;
256 if l_count = 0 then
257 fnd_message.set_name('PER','AME_400553_VAL_SET_ID_NOT_EX');
258 fnd_message.raise_error;
259 end if;
260 end if;
261
262 exception
263 when app_exception.application_exception then
264 if hr_multi_message.exception_add
265 (p_associated_column1 => 'VALUE_SET_ID') then
266 hr_utility.set_location(' Leaving:'|| l_proc, 50);
267 raise;
268 end if;
269 hr_utility.set_location(' Leaving:'|| l_proc, 60);
270 end chk_value_set_id;
271
272 --
273
274 --
275
276 ----------------------------------------------------------------------------
277 -- |----------------------< chk_attr_type_value_set_id_comb >--------------------------|
278 -- ---------------------------------------------------------------------------
279 --
280 --
281 procedure chk_attr_type_val_set_id_comb
282 (p_attribute_id in number,
283 p_value_set_id in number
284 ) is
285 l_attribute_type ame_attributes.attribute_type%type;
286 l_format_type varchar2(1);
287 l_proc varchar2(72) := g_package||'chk_attr_type_value_set_id_comb';
288 begin
289 select attribute_type
290 into l_attribute_type
291 from ame_attributes
292 where attribute_id = p_attribute_id
293 and sysdate between start_date and
294 nvl(end_date - ame_util.oneSecond, sysdate) ;
295 if (l_attribute_type = ame_util.booleanAttributeType)
296 or (l_attribute_type = ame_util.dateAttributeType) then
297 if p_value_set_id is not null then
298 fnd_message.set_name('PER','AME_400554_VAL_SET_ID_NULL');
299 fnd_message.raise_error;
300 end if;
301 elsif p_value_set_id is not null then
302 select format_type into l_format_type
303 from fnd_flex_value_sets
304 where flex_value_set_id = p_value_set_id and
305 instr(flex_value_set_name,'$') = 0 ;
306
307 if ((l_attribute_type = ame_util.currencyAttributeType)
308 or (l_attribute_type = ame_util.numberAttributeType)) and
309 l_format_type <> 'N' then
310 fnd_message.set_name('PER','AME_400555_INV_VAL_SET_FORMAT');
311 fnd_message.raise_error;
312 elsif (l_attribute_type = ame_util.stringAttributeType) and
313 l_format_type <> 'C' then
314 fnd_message.set_name('PER','AME_400555_INV_VAL_SET_FORMAT');
315 fnd_message.raise_error;
316 end if;
317 end if;
318 exception
319 when app_exception.application_exception then
320 if hr_multi_message.exception_add
321 (p_associated_column1 => 'ATTRIBUTE_ID') then
322 hr_utility.set_location(' Leaving:'|| l_proc, 50);
323 raise;
324 end if;
325 hr_utility.set_location(' Leaving:'|| l_proc, 60);
326 end chk_attr_type_val_set_id_comb;
327 --
328 --
329
330
331
332 --
333 -- ---------------------------------------------------------------------------
334 -- |----------------------<chk_attribute_id >--------------------------|
335 -- ---------------------------------------------------------------------------
336 --
337 -- {Start Of Comments}
338 --
339 -- Description:
340 -- Validates that the attribute_id is a foreign key to ame_attributes.attribute_id.
341 --
342 -- Prerequisites:
343 -- None.
344 --
345 -- In Parameters:
346 -- p_attribute_id
347 -- p_effective_date
348 --
349 -- Post Success:
350 -- Processing continues.
351 --
352 -- Post Failure:
353 -- Log the error message.
354 --
355 -- Developer Implementation Notes:
356 -- None.
357 --
358 -- Access Status:
359 -- Internal Row Handler Use Only.
360 --
361 -- {End Of Comments}
362 -- ----------------------------------------------------------------------------
363 procedure chk_attribute_id
364 (p_attribute_id in number,
365 p_effective_date in date) is
366 l_proc varchar2(72) := g_package||'chk_attribute_id';
367 tempCount integer;
368 cursor c_sel1 is
369 select null
370 from ame_attributes
371 where
372 attribute_id = p_attribute_id and
373 p_effective_date between start_date and
374 nvl(end_date - ame_util.oneSecond, p_effective_date) ;
375 l_exists varchar2(1);
376 begin
377 open c_sel1;
378 fetch c_sel1 into l_exists;
379 if c_sel1%notfound then
380 close c_sel1;
381 fnd_message.set_name('PER','AME_400473_INV_ATTRIBUTE_ID');
382 fnd_message.raise_error;
383 end if;
384 close c_sel1;
385 exception
389 hr_utility.set_location(' Leaving:'|| l_proc, 50);
386 when app_exception.application_exception then
387 if hr_multi_message.exception_add
388 (p_associated_column1 => 'ATTRIBUTE_ID') then
390 raise;
391 end if;
392 hr_utility.set_location(' Leaving:'|| l_proc, 60);
393 end chk_attribute_id;
394 -- ---------------------------------------------------------------------------
395 -- |----------------------<chk_application_id >--------------------------|
396 -- ---------------------------------------------------------------------------
397 --
398 -- {Start Of Comments}
399 --
400 -- Description:
401 -- Validates that the application_id is a foreign key to ame_calling_apps.application_id.
402 --
403 -- Prerequisites:
404 -- None.
405 --
406 -- In Parameters:
407 -- p_application_id
408 -- p_effective_date
409 --
410 -- Post Success:
411 -- Processing continues.
412 --
413 -- Post Failure:
414 -- Log the error message.
415 --
416 -- Developer Implementation Notes:
417 -- None.
418 --
419 -- Access Status:
420 -- Internal Row Handler Use Only.
421 --
422 -- {End Of Comments}
423 -- ----------------------------------------------------------------------------
424 procedure chk_application_id
425 (p_application_id in number,
426 p_effective_date in date) is
427 l_proc varchar2(72) := g_package||'chk_application_id';
428 tempCount integer;
429 cursor c_sel1 is
430 select null
431 from ame_calling_apps
432 where
433 application_id = p_application_id and
434 p_effective_date between start_date and
435 nvl(end_date - ame_util.oneSecond, p_effective_date) ;
436 l_exists varchar2(1);
437 begin
438 open c_sel1;
439 fetch c_sel1 into l_exists;
440 if c_sel1%notfound then
441 close c_sel1;
442 fnd_message.set_name('PER','AME_400474_INV_APPLICATION_ID');
443 fnd_message.raise_error;
444 end if;
445 close c_sel1;
446 exception
447 when app_exception.application_exception then
448 if hr_multi_message.exception_add
449 (p_associated_column1 => 'APPLICATION_ID') then
450 hr_utility.set_location(' Leaving:'|| l_proc, 50);
451 raise;
452 end if;
453 hr_utility.set_location(' Leaving:'|| l_proc, 60);
454 end chk_application_id;
455 --
456 -- ---------------------------------------------------------------------------
457 -- |----------------------<chk_primary_key >--------------------------|
458 -- ---------------------------------------------------------------------------
459 --
460 -- {Start Of Comments}
461 --
462 -- Description:
463 -- Validates that the combination of attribute_id and application_id is unique.
464 --
465 -- Prerequisites:
466 -- None.
467 --
468 -- In Parameters:
469 -- p_attribute_id
470 -- p_effective_date
471 -- p_application_id
472 --
473 -- Post Success:
474 -- Processing continues.
475 --
476 -- Post Failure:
477 -- Log the error message.
478 --
479 -- Developer Implementation Notes:
480 -- None.
481 --
482 -- Access Status:
483 -- Internal Row Handler Use Only.
484 --
485 -- {End Of Comments}
486 -- ----------------------------------------------------------------------------
487 procedure chk_primary_key
488 (p_attribute_id in number,
489 p_effective_date in date,
490 p_application_id in ame_attribute_usages.application_id%type) is
491 l_proc varchar2(72) := g_package||'chk_primary_key';
492 tempCount integer;
493 cursor c_sel1 is
494 select count(*)
495 from ame_attribute_usages
496 where
497 attribute_id = p_attribute_id and
498 application_id = p_application_id and
499 p_effective_date between start_date and
500 nvl(end_date - ame_util.oneSecond, p_effective_date) ;
501 begin
502 hr_api.mandatory_arg_error
503 (p_api_name => l_proc
504 ,p_argument => 'application_id'
505 ,p_argument_value => p_application_id
506 );
507 open c_sel1;
508 fetch c_sel1 into tempCount;
509 if c_sel1%found and
510 tempCount > 0 then
511 close c_sel1;
512 fnd_message.set_name('PER','AME_400031_ATT_EXISTS_HD');
513 fnd_message.raise_error;
514 end if;
515 close c_sel1;
516 exception
517 when app_exception.application_exception then
518 if hr_multi_message.exception_add
519 (p_associated_column1 => 'APPLICATION_ID') then
520 hr_utility.set_location(' Leaving:'|| l_proc, 50);
521 raise;
522 end if;
523 hr_utility.set_location(' Leaving:'|| l_proc, 60);
524 end chk_primary_key;
525 -- ---------------------------------------------------------------------------
526 -- |----------------------<chk_is_static >--------------------------|
527 -- ---------------------------------------------------------------------------
528 --
529 -- {Start Of Comments}
530 --
531 -- Description:
532 -- Validates that the is_static field has a value of either ame_util.booleanTrue
533 -- or ame_util.booleanFalse
534 --
535 -- Prerequisites:
536 -- None.
537 --
538 -- In Parameters:
539 -- p_is_static
540 --
541 -- Post Success:
542 -- Processing continues.
543 --
544 -- Post Failure:
545 -- Log the error message.
546 --
547 -- Developer Implementation Notes:
548 -- None.
549 --
550 -- Access Status:
551 -- Internal Row Handler Use Only.
555 procedure chk_is_static
552 --
553 -- {End Of Comments}
554 -- ----------------------------------------------------------------------------
556 ( p_is_static in ame_attribute_usages.is_static%type) is
557 l_proc varchar2(72) := g_package||'chk_is_static';
558 begin
559 if NOT (p_is_static = ame_util.booleanTrue or
560 (p_is_static = ame_util.booleanFalse )) then
561 fnd_message.set_name('PER','AME_400475_INV_USAGE_TYPE');
562 fnd_message.raise_error;
563 end if;
564 exception
565 when app_exception.application_exception then
566 if hr_multi_message.exception_add
567 (p_associated_column1 => 'IS_STATIC') then
568 hr_utility.set_location(' Leaving:'|| l_proc, 50);
569 raise;
570 end if;
571 hr_utility.set_location(' Leaving:'|| l_proc, 60);
572 end chk_is_static;
573 -- ---------------------------------------------------------------------------
574 -- |----------------------<chk_qry_string_is_static_comb >---------|
575 -- ---------------------------------------------------------------------------
576 --
577 -- {Start Of Comments}
578 --
579 -- Description:
580 -- Validates that the Query string is valid for the value of is_static specified.
581 --
582 -- Prerequisites:
583 -- None.
584 --
585 -- In Parameters:
586 -- p_is_static
587 -- p_query_string
588 --
589 -- Post Success:
590 -- Processing continues.
591 --
592 -- Post Failure:
593 -- Log the error message.
594 --
595 -- Developer Implementation Notes:
596 -- None.
597 --
598 -- Access Status:
599 -- Internal Row Handler Use Only.
600 --
601 -- {End Of Comments}
602 -- ----------------------------------------------------------------------------
603 procedure chk_qry_string_is_static_comb
604 ( p_is_static in ame_attribute_usages.is_static%type,
605 p_query_string in ame_attribute_usages.query_string%type ) is
606
607 l_proc varchar2(72) := g_package||'chk_qry_string_is_static_comb';
608 queryString ame_attribute_usages.query_string%type;
609 queryString1 ame_attribute_usages.query_string%type;
610 tempInt integer;
611 transIdPlaceholderPosition integer;
612 transIdPlaceholderPosition2 integer;
613 upperTransIdPlaceholder varchar2(100);
614 begin
615 if (p_is_static = ame_util.booleanTrue ) then /* static usage */
616 -- Check that the query string does not contain any transaction id placeholder
617 queryString := ame_util.removeReturns(stringIn => p_query_string,
618 replaceWithSpaces => false);
619 if(instrb(upper(p_query_string), upper(ame_util.transactionIdPlaceholder))) > 0 then
620 fnd_message.set_name('PER','AME_400159_ATT_STAT_NOT_PLC');
621 fnd_message.raise_error;
622 end if;
623 else /* dynamic usage (actual query string) */
624 if(p_query_string is null) then
625 fnd_message.set_name('PER','AME_400671_DYN_ATT_EMP_USAGE');
626 fnd_message.raise_error;
627 end if;
628 if(instrb(p_query_string, ';', 1, 1) > 0) or
629 (instrb(p_query_string, '--', 1, 1) > 0) or
630 (instrb(p_query_string, '/*', 1, 1) > 0) or
631 (instrb(p_query_string, '*/', 1, 1) > 0) then
632 fnd_message.set_name('PER','AME_400165_ATT_DYN_USG_COMM');
633 fnd_message.raise_error;
634 end if;
635 tempInt := 1;
636 queryString := upper(p_query_string);
637 upperTransIdPlaceholder := upper(ame_util.transactionIdPlaceholder);
638 loop
639 transIdPlaceholderPosition :=
640 instrb(queryString, upperTransIdPlaceholder, 1, tempInt);
641 if(transIdPlaceholderPosition = 0) then
642 exit;
643 end if;
644 transIdPlaceholderPosition2 :=
645 instrb(p_query_string, ame_util.transactionIdPlaceholder, 1, tempInt);
646 if(transIdPlaceholderPosition <> transIdPlaceholderPosition2) then
647 fnd_message.set_name('PER','AME_400414_DYNAMIC_ATTR_USAGES');
648 fnd_message.raise_error;
649 end if;
650 tempInt := tempInt + 1;
651 end loop;
652 if(ame_util.isArgumentTooLong(tableNameIn => 'ame_attribute_usages',
653 columnNameIn => 'query_string',
654 argumentIn => p_query_string)) then
655 fnd_message.set_name('PER','AME_400163_ATT_USAGE_LONG');
656 fnd_message.raise_error;
657 end if;
658 /* The following utility handles the error. So nothing needs to be done here */
659 ame_util.checkForSqlInjection(queryStringIn => queryString);
660 end if;
661 exception
662 when app_exception.application_exception then
663 if hr_multi_message.exception_add
664 (p_associated_column1 => 'QUERY_STRING_IS_STATIC') then
665 hr_utility.set_location(' Leaving:'|| l_proc, 50);
666 raise;
667 end if;
668 hr_utility.set_location(' Leaving:'|| l_proc, 60);
669 end chk_qry_string_is_static_comb;
670 -- ---------------------------------------------------------------------------
671 -- |----------------------< chk_qry_str_static_attr_comb >--------------------|
672 -- ---------------------------------------------------------------------------
673 --
674 -- {Start Of Comments}
675 --
676 -- Description:
677 -- Validates the Query string for the attribute type and the value of is_static.
678 --
679 -- Prerequisites:
680 -- None.
681 --
682 -- In Parameters:
683 -- p_attribute_id
684 -- p_application_id
685 -- p_is_static
686 -- p_query_string
687 -- p_effective_date
688 --
689 -- Post Success:
690 -- Processing continues.
691 --
695 -- Developer Implementation Notes:
692 -- Post Failure:
693 -- Log the error message.
694 --
696 -- None.
697 --
698 -- Access Status:
699 -- Internal Row Handler Use Only.
700 --
701 -- {End Of Comments}
702 -- ----------------------------------------------------------------------------
703 procedure chk_qry_str_static_attr_comb
704 ( p_attribute_id in ame_attribute_usages.attribute_id%type,
705 p_application_id in ame_attribute_usages.application_id%type,
706 p_is_static in ame_attribute_usages.is_static%type,
707 p_query_string in ame_attribute_usages.query_string%type,
708 p_effective_date in date ) is
709 --
710 l_proc varchar2(72) := g_package||'chk_qry_str_static_attr_comb';
711 charMonths ame_util.stringList;
712 comma1Location integer;
713 comma2Location integer;
714 queryString ame_attribute_usages.query_string%type;
715 queryString1 ame_attribute_usages.query_string%type;
716 l_tmpQueryString ame_attribute_usages.query_string%type;
717 l_amount ame_attribute_usages.query_string%type;
718 l_currencyCode ame_attribute_usages.query_string%type;
719 l_conversionType ame_attribute_usages.query_string%type;
720 l_tmpAmount ame_attribute_usages.query_string%type;
721 l_attribute_type ame_attributes.attribute_type%type;
722 l_attribute_name ame_attributes.name%type;
723 numMonths ame_util.stringList;
724 substitutionString ame_util.stringType;
725 transIdPlaceholderPosition integer;
726 transIdPlaceholderPosition2 integer;
727 l_comma1Location integer;
728 l_comma2Location integer;
729 l_rgeflg varchar2(30);
730 upperTransIdPlaceholder varchar2(100);
731 l_attr_name varchar2(50);
732 l_columns number;
733 l_valid varchar2(1000);
734 l_object varchar2(20);
735 begin
736 -- get attribute type
737 select name,attribute_type
738 into l_attribute_name,l_attribute_type
739 from ame_attributes
740 where attribute_id = p_attribute_id
741 and p_effective_date between start_date and
742 nvl(end_date - ame_util.oneSecond, p_effective_date) ;
743 if (p_is_static = ame_util.booleanTrue ) then /* static usage */
744 /* Check that the format of the static usage is correct. */
745 if(l_attribute_type = ame_util.currencyAttributeType) then
746 if p_query_string is not null then
747 l_comma1Location := instrb(p_query_string, ',', -1, 2);
748 l_comma2Location := instrb(p_query_string, ',', -1, 1);
749 if(l_comma1Location = 0 or
750 l_comma2Location = 0 or
751 l_comma1Location < 2 or
752 l_comma2Location < 4) then
753 fnd_message.set_name('PER', 'AME_400670_BAD_STAT_CURR_USG');
754 fnd_message.set_token('ATTRIBUTE',l_attribute_name);
755 fnd_message.raise_error;
756 end if;
757 l_amount := substrb(p_query_string, 1, l_comma1Location - 1);
758 l_currencyCode := substrb(p_query_string, l_comma1Location + 1, l_comma2Location - l_comma1Location - 1);
759 l_conversionType := substrb(p_query_string, l_comma2Location + 1, lengthb(p_query_string) - l_comma2Location);
760 if not ame_util.isCurrencyCodeValid(currencyCodeIn => l_currencyCode) then
761 fnd_message.set_name('PER', 'AME_400151_ATT_STA_CURR_INV');
762 fnd_message.raise_error;
763 end if;
764 l_tmpAmount := replace(l_amount,',','.');
765 hr_chkfmt.checkformat(value => l_tmpAmount,
766 format => 'M',
767 output => l_amount,
768 minimum => null,
769 maximum => null,
770 nullok => 'Y',
771 rgeflg => l_rgeflg,
772 curcode => l_currencyCode);
773 if not ame_util.isConversionTypeValid(conversionTypeIn => l_conversionType) then
774 fnd_message.set_name('PER', 'AME_400150_ATT_STA_CONV_INV');
775 fnd_message.raise_error;
776 end if;
777 else
778 fnd_message.set_name('PER', 'AME_400670_BAD_STAT_CURR_USG');
779 fnd_message.set_token('ATTRIBUTE',l_attribute_name);
780 fnd_message.raise_error;
781 end if;
782 elsif(l_attribute_type = ame_util.numberAttributeType) then
783 if p_query_string is not null and not isNumber(p_query_string) then
784 fnd_message.set_name('PER','AME_400516_ATT_STAT_USG_NUM');
785 fnd_message.raise_error;
786 elsif p_query_string is not null then
787 l_tmpQueryString := replace(p_query_string,',','.');
788 hr_chkfmt.checkformat(value => l_tmpQueryString,
789 format => 'N',
790 output => queryString,
791 minimum => null,
792 maximum => null,
793 nullok => 'Y',
794 rgeflg => l_rgeflg,
795 curcode => null);
796 end if;
797 elsif(l_attribute_type = ame_util.stringAttributeType) then
798 if(instrb(p_query_string, '''') > 0) or length(p_query_string) > ame_util.stringTypeLength then
799 fnd_message.set_name('PER','AME_400166_ATT_STAT_USG_STRING');
800 fnd_message.raise_error;
801 end if;
802 begin
803 select name
804 into l_attr_name
805 from ame_attributes
806 where attribute_id = p_attribute_id
807 and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
811 ,'CONTINUE_OTHER_SUBORDINATE_ITEMS') then
808 if l_attr_name = 'REJECTION_RESPONSE' and p_query_string is not null then
809 if p_query_string not in ('STOP_ALL_ITEMS'
810 ,'CONTINUE_ALL_OTHER_ITEMS'
812 fnd_message.set_name('PER','AME_400785_REJ_RESP_USG_INV');
813 fnd_message.raise_error;
814 end if;
815 end if;
816 exception
817 when no_data_found then
818 null;
819 end;
820 elsif(l_attribute_type = ame_util.booleanAttributeType) then
821 if trim(p_query_string) IN ('true','false') then
822 querystring :=lower(trim( p_query_string));
823 else
824 fnd_message.set_name('PER','AME_400167_ATT_STAT_USG_BOOL');
825 fnd_message.raise_error;
826 end if;
827 elsif(l_attribute_type = ame_util.dateAttributeType) then
828 /* check to make sure the user entered the date in the correct format */
829 begin
830 if(p_query_string is not null) then
831 if instrb(p_query_string, ':', 1, 5) = 0 or
832 not chk_ame_date_format(p_query_string) then
833 fnd_message.set_name('PER','AME_400168_ATT_STAT_USG_DATE');
834 fnd_message.raise_error;
835 end if;
836 end if;
837 exception
838 when app_exception.application_exception then
839 if hr_multi_message.exception_add
840 (p_associated_column1 => 'chk_qry_str_static_attr_comb') then
841 hr_utility.set_location(' Leaving:'|| l_proc, 50);
842 raise;
843 end if;
844 hr_utility.set_location(' Leaving:'|| l_proc, 60);
845 end;
846 end if;
847 else /* dynamic usage (actual query string) */
848 l_columns := 1;
849 if(l_attribute_type = ame_util.currencyAttributeType) then
850 l_columns := 3;
851 comma1Location := instrb(queryString, ',', -1, 2);
852 comma2Location := instrb(queryString, ',', -1, 1);
853 if(comma1Location = 0 or
854 comma2Location = 0 or
855 comma1Location < 2 or
856 comma2Location < 4) then
857 fnd_message.set_name('PER','AME_400515_QUERY_INVALID');
858 fnd_message.raise_error;
859 end if;
860 end if;
861 begin
862 select ame_util2.specialObject
863 into l_object
864 from ame_attributes
865 where attribute_id = p_attribute_id
866 and name in (ame_util.jobLevelStartingPointAttribute
867 ,ame_util.nonDefStartingPointPosAttr
868 ,ame_util.nonDefPosStructureAttr
869 ,ame_util.supStartingPointAttribute
870 ,ame_util.firstStartingPointAttribute
871 ,ame_util.secondStartingPointAttribute
872 )
873 and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
874 exception
875 when no_data_found then
876 l_object := ame_util2.attributeObject;
877 end;
878 l_valid := ame_utility_pkg.validate_query(p_query_string => p_query_string
879 ,p_columns => l_columns
880 ,p_object => l_object
881 );
882 if l_valid <> 'Y' then
883 fnd_message.set_name('PER','AME_400817_INV_ATTR_QRY');
884 fnd_message.set_token('ATTR_NAME', l_attribute_name);
885 fnd_message.raise_error;
886 end if;
887 end if;
888 exception
889 when app_exception.application_exception then
890 if hr_multi_message.exception_add
891 (p_associated_column1 => 'chk_qry_str_static_attr_comb') then
892 hr_utility.set_location(' Leaving:'|| l_proc, 50);
893 raise;
894 end if;
895 hr_utility.set_location(' Leaving:'|| l_proc, 60);
896 end chk_qry_str_static_attr_comb;
897 -- ---------------------------------------------------------------------------
898 -- |----------------------<chk_use_count >--------------------------|
899 -- ---------------------------------------------------------------------------
900 --
901 -- {Start Of Comments}
902 --
903 -- Description:
904 -- Validates that the use_count field has a value of '0' on insert.
905 --
906 -- Prerequisites:
907 -- None--
908 -- In Parameters:
909 -- p_use_count
910 --
911 -- Post Success:
912 -- Processing continues.
913 --
914 -- Post Failure:
915 -- Log the error message.
916 --
917 -- Developer Implementation Notes:
918 -- None.
919 --
920 -- Access Status:
921 -- Internal Row Handler Use Only.
922 --
923 -- {End Of Comments}
924 -- ----------------------------------------------------------------------------
925 procedure chk_use_count
926 ( p_use_count in ame_attribute_usages.use_count%type) is
927 l_proc varchar2(72) := g_package||'chk_use_count';
928 begin
929 if p_use_count <> 0 then
930 fnd_message.set_name('PER','AME_400039_ATT_TTYPE_USES');
931 -- Create a new error message to indicate that the value of use_count is invalid
932 fnd_message.raise_error;
933 end if;
934 exception
935 when app_exception.application_exception then
936 if hr_multi_message.exception_add
937 (p_associated_column1 => 'USE_COUNT') then
938 hr_utility.set_location(' Leaving:'|| l_proc, 50);
939 raise;
940 end if;
941 hr_utility.set_location(' Leaving:'|| l_proc, 60);
942 end chk_use_count;
943 -- ---------------------------------------------------------------------------
947 -- {Start Of Comments}
944 -- |----------------------<chk_user_editable >--------------------------|
945 -- ---------------------------------------------------------------------------
946 --
948 --
949 -- Description:
950 -- Validates that the user_editable field has a value of either ame_util.booleanTrue
951 -- or ame_util.booleanFalse
952 --
953 -- Prerequisites:
954 -- None.
955 --
956 -- In Parameters:
957 -- p_user_editable
958 --
959 -- Post Success:
960 -- Processing continues.
961 --
962 -- Post Failure:
963 -- Log the error message.
964 --
965 -- Developer Implementation Notes:
966 -- None.
967 --
968 -- Access Status:
969 -- Internal Row Handler Use Only.
970 --
971 -- {End Of Comments}
972 -- ----------------------------------------------------------------------------
973 procedure chk_user_editable
974 ( p_user_editable in ame_attribute_usages.user_editable%type) is
975 l_proc varchar2(72) := g_package||'chk_user_editable';
976 begin
977 if NOT (p_user_editable = ame_util.booleanTrue or
978 (p_user_editable = ame_util.booleanFalse )) then
979 fnd_message.set_name('PER','AME_400476_INV_USER_EDITABLE');
980 fnd_message.raise_error;
981 end if;
982 exception
983 when app_exception.application_exception then
984 if hr_multi_message.exception_add
985 (p_associated_column1 => 'USER_EDITABLE') then
986 hr_utility.set_location(' Leaving:'|| l_proc, 50);
987 raise;
988 end if;
989 hr_utility.set_location(' Leaving:'|| l_proc, 60);
990 end chk_user_editable;
991 --
992 -- ----------------------------------------------------------------------------
993 -- |-----------------------< chk_non_updateable_args >------------------------|
994 -- ----------------------------------------------------------------------------
995 -- {Start Of Comments}
996 --
997 -- Description:
998 -- This procedure is used to ensure that non updateable attributes have
999 -- not been updated. If an attribute has been updated an error is generated.
1000 --
1001 -- Pre Conditions:
1002 -- g_old_rec has been populated with details of the values currently in
1003 -- the database.
1004 --
1005 -- In Arguments:
1006 -- p_rec has been populated with the updated values the user would like the
1007 -- record set to.
1008 --
1009 -- Post Success:
1010 -- Processing continues if all the non updateable attributes have not
1011 -- changed.
1012 --
1013 -- Post Failure:
1014 -- An application error is raised if any of the non updatable attributes
1015 -- have been altered.
1016 --
1017 -- {End Of Comments}
1018 -- ----------------------------------------------------------------------------
1019 Procedure chk_non_updateable_args
1020 (p_effective_date in date
1021 ,p_rec in ame_atu_shd.g_rec_type
1022 ) IS
1023 --
1024 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1025 --
1026 Begin
1027 --
1028 -- Only proceed with the validation if a row exists for the current
1029 -- record in the HR Schema.
1030 --
1031 IF NOT ame_atu_shd.api_updating
1032 (p_attribute_id => p_rec.attribute_id
1033 ,p_application_id => p_rec.application_id
1034 ,p_effective_date => p_effective_date
1035 ,p_object_version_number => p_rec.object_version_number
1036 ) THEN
1037 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1038 fnd_message.set_token('PROCEDURE ', l_proc);
1039 fnd_message.set_token('STEP ', '5');
1040 fnd_message.raise_error;
1041 END IF;
1042 --
1043 if (ame_atu_shd.g_old_rec.user_editable <> p_rec.user_editable) then
1044 fnd_message.set_name('PER', 'AME_400467_NON_UPDATEABLE_FIELD');
1045 fnd_message.set_token('FIELD_NAME ', 'USER_EDITABLE');
1046 fnd_message.set_token('PROCEDURE ', l_proc);
1047 fnd_message.set_token('STEP ', '5');
1048 fnd_message.raise_error;
1049 end if;
1050 if (ame_atu_shd.g_old_rec.user_editable = ame_util.booleanFalse) then
1051 if (ame_atu_shd.g_old_rec.is_static <> p_rec.is_static) then
1052 fnd_message.set_name('PER', 'AME_400467_NON_UPDATEABLE_FIELD');
1053 fnd_message.set_token('FIELD_NAME ', 'IS_STATIC');
1054 fnd_message.set_token('PROCEDURE ', l_proc);
1055 fnd_message.set_token('STEP ', '5');
1056 fnd_message.raise_error;
1057 end if;
1058 if (ame_atu_shd.g_old_rec.query_string <> p_rec.query_string) then
1059 fnd_message.set_name('PER', 'AME_400467_NON_UPDATEABLE_FIELD');
1060 fnd_message.set_token('FIELD_NAME ', 'QUERY_STRING');
1061 fnd_message.set_token('PROCEDURE ', l_proc);
1062 fnd_message.set_token('STEP ', '5');
1063 fnd_message.raise_error;
1064 end if;
1065 end if;
1066 --
1067 End chk_non_updateable_args;
1068 --
1069 -- ---------------------------------------------------------------------------
1070 -- |----------------------< chk_delete >--------------------------|
1071 -- ---------------------------------------------------------------------------
1072 --
1073 -- {sTARt Of Comments}
1074 --
1075 -- Description:
1076 -- check that 1. No condition based on this attribute exist
1077 -- 2. Attribute is not an existing Mandatory attribute
1078 -- 3. USER_EDITABLE is not ame_util.booleanFalse
1079 --
1080 -- Prerequisites:
1081 -- None.
1082 --
1083 -- In Parameters:
1084 -- p_attribute_id
1085 -- p_application_id
1086 -- p_object_version_number
1087 -- p_effective_date
1088 --
1089 -- Post Success:
1090 -- Processing continues.
1091 --
1092 -- Post Failure:
1096 -- None.
1093 -- Log the error message.
1094 --
1095 -- Developer Implementation Notes:
1097 --
1098 -- Access Status:
1099 -- Internal Row Handler Use Only.
1100 --
1101 -- {End Of Comments}
1102 -- ----------------------------------------------------------------------------
1103 procedure chk_delete
1104 (p_attribute_id in number,
1105 p_application_id in integer,
1106 p_object_version_number in number,
1107 p_effective_date in date) is
1108
1109 l_proc varchar2(72) := g_package||'chk_delete';
1110 tempCount integer;
1111 l_use_count number ;
1112 l_exists varchar2(1);
1113 l_seededDb varchar2(1);
1114
1115 cursor c_sel1 is
1116 select use_count
1117 from ame_attribute_usages
1118 where
1119 attribute_id = p_attribute_id and
1120 application_id = p_application_id and
1121 sysdate between start_date and
1122 nvl(end_date-(ame_util.oneSecond),sysdate);
1123
1124 cursor c_sel2 is
1125 select null
1126 from ame_mandatory_attributes
1127 where
1128 attribute_id = p_attribute_id and
1129 action_type_id = -1 and
1130 p_effective_date between start_date and
1131 nvl(end_date - ame_util.oneSecond, p_effective_date) ;
1132
1133 -- cursor c_sel3 is
1134 -- select null
1135 -- from ame_attribute_usages
1136 -- where ame_utility_pkg.check_seeddb = 'N'
1137 -- and ame_utility_pkg.is_seed_user(created_by) = ame_util.seededDataCreatedById
1138 -- and attribute_id = p_attribute_id
1139 -- and application_id = p_application_id
1140 -- and p_effective_date between start_date
1141 -- and nvl(end_date - (1/86400), sysdate);
1142 begin
1143 l_use_count := 0;
1144 if (instr(DBMS_UTILITY.FORMAT_CALL_STACK,'AME_TRANS_TYPE_API'||fnd_global.local_chr(10)) <> 0) then
1145 return;
1146 end if;
1147 if (ame_atu_shd.g_old_rec.user_editable = ame_util.booleanFalse) then
1148 fnd_message.set_name('PER', 'AME_400477_CANNOT_DEL_SEEDED');
1149 fnd_message.raise_error;
1150 end if;
1151
1152 open c_sel1;
1153 fetch c_sel1 into l_use_count;
1154 close c_sel1;
1155
1156 if l_use_count > 0 then
1157 fnd_message.set_name('PER','AME_400171_ATT_IS_IN_USE');
1158 fnd_message.raise_error;
1159 else
1160 open c_sel2;
1161 fetch c_sel2 into l_exists;
1162 l_seededDb := ame_utility_pkg.check_seeddb;
1163 if c_sel2%found and l_seededDb = 'N' then
1164 close c_sel2;
1165 fnd_message.set_name('PER','AME_400170_ATT_MAND_CANT_DEL');
1166 fnd_message.raise_error;
1167 end if;
1168 close c_sel2;
1169 end if;
1170
1171 -- open c_sel3;
1172 -- fetch c_sel3 into l_use_count;
1173 -- if c_sel3%found then
1174 -- close c_sel3;
1175 -- fnd_message.set_name('PER', 'AME_400477_CANNOT_DEL_SEEDED');
1176 -- fnd_message.set_token('OBJECT', 'ATTRIBUTE USAGE');
1177 -- fnd_message.raise_error;
1178 -- end if;
1179 -- close c_sel3;
1180
1181 exception
1182 when app_exception.application_exception then
1183 if hr_multi_message.exception_add
1184 (p_associated_column1 => 'ATTRIBUTE_ID') then
1185 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1186 raise;
1187 end if;
1188 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1189 end chk_delete;
1190 -- ----------------------------------------------------------------------------
1191 -- |--------------------------< dt_update_validate >--------------------------|
1192 -- ----------------------------------------------------------------------------
1193 -- {Start Of Comments}
1194 --
1195 -- Description:
1196 -- This procedure is used for referential integrity of datetracked
1197 -- parent entities when a datetrack update operation is taking place
1198 -- and where there is no cascading of update defined for this entity.
1199 --
1200 -- Prerequisites:
1201 -- This procedure is called from the update_validate.
1202 --
1203 -- In Parameters:
1204 --
1205 -- Post Success:
1206 -- Processing continues.
1207 --
1208 -- Post Failure:
1209 --
1210 -- Developer Implementation Notes:
1211 -- This procedure should not need maintenance unless the HR Schema model
1212 -- changes.
1213 --
1214 -- Access Status:
1215 -- Internal Row Handler Use Only.
1216 --
1217 -- {End Of Comments}
1218 -- ----------------------------------------------------------------------------
1219 Procedure dt_update_validate
1220 (p_attribute_id in number default hr_api.g_number
1221 ,p_datetrack_mode in varchar2
1222 ,p_validation_start_date in date
1223 ,p_validation_end_date in date
1224 ) Is
1225 --
1226 l_proc varchar2(72) := g_package||'dt_update_validate';
1227 --
1228 Begin
1229 --
1230 -- Ensure that the p_datetrack_mode argument is not null
1231 --
1232 hr_api.mandatory_arg_error
1233 (p_api_name => l_proc
1234 ,p_argument => 'datetrack_mode'
1235 ,p_argument_value => p_datetrack_mode
1236 );
1237 --
1238 -- Mode will be valid, as this is checked at the start of the upd.
1239 --
1240 -- Ensure the arguments are not null
1241 --
1242 hr_api.mandatory_arg_error
1243 (p_api_name => l_proc
1244 ,p_argument => 'validation_start_date'
1245 ,p_argument_value => p_validation_start_date
1246 );
1247 --
1248 /*hr_api.mandatory_arg_error
1249 (p_api_name => l_proc
1250 ,p_argument => 'validation_end_date'
1251 ,p_argument_value => p_validation_end_date
1252 );*/
1253 --
1257 -- An unhandled or unexpected error has occurred which
1254 Exception
1255 When Others Then
1256 --
1258 -- we must report
1259 --
1260 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1261 fnd_message.set_token('PROCEDURE', l_proc);
1262 fnd_message.set_token('STEP','15');
1263 fnd_message.raise_error;
1264 End dt_update_validate;
1265 --
1266 -- ----------------------------------------------------------------------------
1267 -- |--------------------------< dt_delete_validate >--------------------------|
1268 -- ----------------------------------------------------------------------------
1269 -- {Start Of Comments}
1270 --
1271 -- Description:
1272 -- This procedure is used for referential integrity of datetracked
1273 -- child entities when either a datetrack DELETE or ZAP is in operation
1274 -- and where there is no cascading of delete defined for this entity.
1275 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1276 -- datetracked child rows exist between the validation start and end
1277 -- dates.
1278 --
1279 -- Prerequisites:
1280 -- This procedure is called from the delete_validate.
1281 --
1282 -- In Parameters:
1283 --
1284 -- Post Success:
1285 -- Processing continues.
1286 --
1287 -- Post Failure:
1288 -- If a row exists by determining the returning Boolean value from the
1289 -- generic dt_api.rows_exist function then we must supply an error via
1290 -- the use of the local exception handler l_rows_exist.
1291 --
1292 -- Developer Implementation Notes:
1293 -- This procedure should not need maintenance unless the HR Schema model
1294 -- changes.
1295 --
1296 -- Access Status:
1297 -- Internal Row Handler Use Only.
1298 --
1299 -- {End Of Comments}
1300 -- ----------------------------------------------------------------------------
1301 Procedure dt_delete_validate
1302 (p_attribute_id in number
1303 ,p_application_id in number
1304 ,p_datetrack_mode in varchar2
1305 ,p_validation_start_date in date
1306 ,p_validation_end_date in date
1307 ) Is
1308 --
1309 l_proc varchar2(72) := g_package||'dt_delete_validate';
1310 --
1311 Begin
1312 --
1313 -- Ensure that the p_datetrack_mode argument is not null
1314 --
1315 hr_api.mandatory_arg_error
1316 (p_api_name => l_proc
1317 ,p_argument => 'datetrack_mode'
1318 ,p_argument_value => p_datetrack_mode
1319 );
1320 --
1321 -- Only perform the validation if the datetrack mode is either
1322 -- DELETE or ZAP
1323 --
1324 If (p_datetrack_mode = hr_api.g_delete or
1325 p_datetrack_mode = hr_api.g_zap) then
1326 --
1327 --
1328 -- Ensure the arguments are not null
1329 --
1330 hr_api.mandatory_arg_error
1331 (p_api_name => l_proc
1332 ,p_argument => 'validation_start_date'
1333 ,p_argument_value => p_validation_start_date
1334 );
1335 --
1336 /*hr_api.mandatory_arg_error
1337 (p_api_name => l_proc
1338 ,p_argument => 'validation_end_date'
1339 ,p_argument_value => p_validation_end_date
1340 );*/
1341 --
1342 hr_api.mandatory_arg_error
1343 (p_api_name => l_proc
1344 ,p_argument => 'attribute_id'
1345 ,p_argument_value => p_attribute_id
1346 );
1347 --
1348 --
1349 --
1350 End If;
1351 --
1352 Exception
1353 When Others Then
1354 --
1355 -- An unhandled or unexpected error has occurred which
1356 -- we must report
1357 --
1358 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1359 fnd_message.set_token('PROCEDURE', l_proc);
1360 fnd_message.set_token('STEP','15');
1361 fnd_message.raise_error;
1362 --
1363 End dt_delete_validate;
1364 --
1365 -- ----------------------------------------------------------------------------
1366 -- |---------------------------< insert_validate >----------------------------|
1367 -- ----------------------------------------------------------------------------
1368 Procedure insert_validate
1369 (p_rec in ame_atu_shd.g_rec_type
1370 ,p_effective_date in date
1371 ,p_datetrack_mode in varchar2
1372 ,p_validation_start_date in date
1373 ,p_validation_end_date in date
1374 ) is
1375 --
1376 l_proc varchar2(72) := g_package||'insert_validate';
1377 --
1378 Begin
1379 hr_utility.set_location('Entering:'||l_proc, 5);
1380
1381 chk_attribute_id (p_attribute_id => p_rec.attribute_id,
1382 p_effective_date => p_effective_date);
1383
1384 chk_application_id(p_application_id => p_rec.application_id,
1385 p_effective_date => p_effective_date);
1386
1387 chk_primary_key(p_attribute_id => p_rec.attribute_id,
1388 p_effective_date => p_effective_date,
1389 p_application_id => p_rec.application_id);
1390
1391 chk_is_static (p_is_static => p_rec.is_static);
1392
1393 chk_attribute_item_class(p_application_id => p_rec.application_id,
1394 p_attribute_id => p_rec.attribute_id);
1395
1396 chk_attribute_approver_type(p_application_id => p_rec.application_id,
1397 p_attribute_id => p_rec.attribute_id);
1398
1399 chk_value_set_id(p_value_set_id => p_rec.value_set_id);
1400
1401 chk_attr_type_val_set_id_comb(p_attribute_id => p_rec.attribute_id,
1402 p_value_set_id => p_rec.value_set_id);
1403
1404 chk_qry_string_is_static_comb(p_is_static => p_rec.is_static,
1408 p_application_id => p_rec.application_id,
1405 p_query_string => p_rec.query_string);
1406
1407 chk_qry_str_static_attr_comb(p_attribute_id => p_rec.attribute_id,
1409 p_is_static => p_rec.is_static,
1410 p_query_string => p_rec.query_string,
1411 p_effective_date => p_effective_date);
1412 chk_user_editable (p_user_editable => p_rec.user_editable);
1413
1414 chk_use_count (p_use_count => p_rec.use_count);
1415
1416 hr_utility.set_location(' Leaving:'||l_proc, 10);
1417 End insert_validate;
1418 --
1419 -- ----------------------------------------------------------------------------
1420 -- |---------------------------< update_validate >----------------------------|
1421 -- ----------------------------------------------------------------------------
1422 Procedure update_validate
1423 (p_rec in ame_atu_shd.g_rec_type
1424 ,p_effective_date in date
1425 ,p_datetrack_mode in varchar2
1426 ,p_validation_start_date in date
1427 ,p_validation_end_date in date
1428 ) is
1429 --
1430 l_proc varchar2(72) := g_package||'update_validate';
1431 --
1432 Begin
1433 hr_utility.set_location('Entering:'||l_proc, 5);
1434 --
1435 -- Validate Dependent Attributes
1436 --
1437 -- Call the datetrack update integrity operation
1438 --
1439 dt_update_validate
1440 (p_attribute_id => p_rec.attribute_id
1441 ,p_datetrack_mode => p_datetrack_mode
1442 ,p_validation_start_date => p_validation_start_date
1443 ,p_validation_end_date => p_validation_end_date
1444 );
1445 --
1446 chk_non_updateable_args
1447 (p_effective_date => p_effective_date
1448 ,p_rec => p_rec
1449 );
1450
1451 chk_is_static (p_is_static => p_rec.is_static);
1452
1453 chk_value_set_id(p_value_set_id => p_rec.value_set_id);
1454
1455 chk_attr_type_val_set_id_comb(p_attribute_id => p_rec.attribute_id,
1456 p_value_set_id => p_rec.value_set_id);
1457
1458 chk_qry_string_is_static_comb(p_is_static => p_rec.is_static,
1459 p_query_string => p_rec.query_string);
1460
1461 chk_qry_str_static_attr_comb(p_attribute_id => p_rec.attribute_id,
1462 p_application_id => p_rec.application_id,
1463 p_is_static => p_rec.is_static,
1464 p_query_string => p_rec.query_string,
1465 p_effective_date => p_effective_date);
1466
1467
1468 --
1469 hr_utility.set_location(' Leaving:'||l_proc, 10);
1470 End update_validate;
1471 --
1472 -- ----------------------------------------------------------------------------
1473 -- |---------------------------< delete_validate >----------------------------|
1474 -- ----------------------------------------------------------------------------
1475 Procedure delete_validate
1476 (p_rec in ame_atu_shd.g_rec_type
1477 ,p_effective_date in date
1478 ,p_datetrack_mode in varchar2
1479 ,p_validation_start_date in date
1480 ,p_validation_end_date in date
1481 ) is
1482 --
1483 l_proc varchar2(72) := g_package||'delete_validate';
1484 --
1485 Begin
1486 hr_utility.set_location('Entering:'||l_proc, 5);
1487 --
1488 -- Call all supporting business operations
1489 --
1490 dt_delete_validate
1491 (p_datetrack_mode => p_datetrack_mode
1492 ,p_validation_start_date => p_validation_start_date
1493 ,p_validation_end_date => p_validation_end_date
1494 ,p_attribute_id => p_rec.attribute_id
1495 ,p_application_id => p_rec.application_id
1496 );
1497 chk_delete
1498 (p_attribute_id => p_rec.attribute_id,
1499 p_application_id => p_rec.application_id,
1500 p_object_version_number => p_rec.object_version_number,
1501 p_effective_date => p_effective_date);
1502 --
1503 hr_utility.set_location(' Leaving:'||l_proc, 10);
1504 End delete_validate;
1505 --
1506 end ame_atu_bus;