[Home] [Help]
PACKAGE BODY: APPS.AME_UTILITY_PKG
Source
1 package BODY ame_utility_pkg as
2 /* $Header: ameutility.pkb 120.10 2008/04/11 05:30:41 prasashe noship $ */
3 --private method
4 procedure checkBindVariables(queryStringIn in varchar2
5 ,allowedBindVars in ame_util.stringList) as
6 tempstring1 varchar2(4000);
7 tempstring varchar2(4000);
8 col1Position number;
9 col2Position number;
10 comPos1 number := 0;
11 comPos2 number := 0;
12 commaPos number :=0;
13 paranPos number :=0;
14 minPos number:=0;
15 errInBindVar boolean := true;
16 invalidBindException exception;
17 begin
18 --+
19 tempstring := ' ' || replace(replace(replace(queryStringIn,fnd_global.local_chr(10),' '),
20 fnd_global.local_chr(13),' '),' ',' ') || ' ';
21 col1Position := instrb(tempstring, ':', 1) ;
22 --+
23 while col1Position > 0 loop
24 --+
25 errInBindVar := true;
26 col1Position := col1Position + 1;
27 comPos1:= instrb(tempstring, '''', 1) ;
28 if compos1 < col1Position and comPos1 > 0 then
29 comPos2:= instrb(tempstring, '''', 1,2) ;
30 if comPos2 > 0 then
31 tempstring := substr(tempstring,comPos2+1);
32 end if;
33 else
34 tempstring := substr(tempstring,col1Position);
35 commaPos:=instrb(tempstring, ',', 1) ;
36 paranPos:= instrb(tempstring, ')', 1) ;
37 col2Position := instrb(tempstring, ' ', 1);
38 minPos := col2Position;
39 if commaPos > 0 and commaPos < minPos then
40 minPos := commaPos;
41 end if;
42 if paranPos > 0 and paranPos < minPos then
43 minPos := paranPos;
44 end if;
45 tempstring1 := substr(tempstring,1,minPos-1);
46 for i in 1 .. allowedBindVars.count loop
47 if trim(tempstring1) = trim(allowedBindVars(i)) then
48 errInBindVar := false;
49 exit;
50 end if;
51 end loop;
52 if errInBindVar = true then
53 raise invalidBindException;
54 exit;
55 end if;
56 end if;
57 col1Position := instrb(tempstring, ':', 1);
58 end loop;
59 end checkBindVariables;
60 function truncate_cond_desc(p_description varchar2,
61 p_truncate varchar2) return varchar2;
62 function validate_query
63 (p_query_string varchar2
64 ,p_columns number default null
65 ,p_object varchar2 default null
66 ) return varchar2 as
67
68 query_cursor integer;
69 temp_query_string varchar2(4000);
70 columna varchar2(100);
71 columnb varchar2(100);
72 error_string varchar2(1000);
73 l_allowed_bind_vars ame_util.stringList;
74 begin
75 if (check_seeddb = 'Y') then
76 return 'Y';
77 end if;
78 /* Query String Cannot be null */
79 if p_query_string is null then
80 fnd_message.set_name('PER','AME_400629_EMPTY_QUERY_STRING');
81 return fnd_message.get;
82 end if;
83
84 /* Remove any new lines and replace with spaces */
85 temp_query_string := ' ' || replace(replace(replace(p_query_string,
86 fnd_global.local_chr(10),
87 ' '),
88 fnd_global.local_chr(13),
89 ' '),
90 ' ',
91 ' ') || ' ';
92
93 /* Following commands are not SQL queries */
94 /* insert,update,delete,commit,rollback */
95 /* truncate,drop,grant,execute,locl,alter */
96 if instr(lower(temp_query_string),' insert ',1,1) > 0 then
97 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
98 fnd_message.set_token('KEYWORD','INSERT');
99 return fnd_message.get;
100 end if;
101 if instr(lower(temp_query_string),' update ',1,1) > 0 then
102 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
103 fnd_message.set_token('KEYWORD','UPDATE');
104 return fnd_message.get;
105 end if;
106 if instr(lower(temp_query_string),' delete ',1,1) > 0 then
107 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
108 fnd_message.set_token('KEYWORD','DELETE');
109 return fnd_message.get;
110 end if;
111 if instr(lower(temp_query_string),' commit ',1,1) > 0 then
112 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
113 fnd_message.set_token('KEYWORD','COMMIT');
114 return fnd_message.get;
115 end if;
116 if instr(lower(temp_query_string),' rollback ',1,1) > 0 then
117 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
118 fnd_message.set_token('KEYWORD','ROLLBACK');
119 return fnd_message.get;
120 end if;
121 if instr(lower(temp_query_string),' truncate ',1,1) > 0 then
122 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
123 fnd_message.set_token('KEYWORD','TRUNCATE');
124 return fnd_message.get;
125 end if;
126 if instr(lower(temp_query_string),' drop ',1,1) > 0 then
127 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
128 fnd_message.set_token('KEYWORD','DROP');
129 return fnd_message.get;
130 end if;
131 if instr(lower(temp_query_string),' grant ',1,1) > 0 then
132 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
133 fnd_message.set_token('KEYWORD','GRANT');
134 return fnd_message.get;
135 end if;
136 if instr(lower(temp_query_string),'execute ',1,1) > 0 then
137 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
138 fnd_message.set_token('KEYWORD','EXECUTE');
139 return fnd_message.get;
140 end if;
141 if instr(lower(temp_query_string),' lock ',1,1) > 0 then
142 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
143 fnd_message.set_token('KEYWORD','LOCK');
144 return fnd_message.get;
145 end if;
146 if instr(lower(temp_query_string),' alter ',1,1) > 0 then
147 fnd_message.set_name('PER','AME_400630_PROHIBITED_KEYWORD');
148 fnd_message.set_token('KEYWORD','ALTER');
149 return fnd_message.get;
150 end if;
151
152 /* Comments and semicolons are not allowed */
153 if instr(lower(temp_query_string),';',1,1) > 0 then
154 fnd_message.set_name('PER','AME_400165_ATT_DYN_USG_COMM');
155 return fnd_message.get;
156 end if;
157 if instr(lower(temp_query_string),'/*',1,1) > 0 then
158 fnd_message.set_name('PER','AME_400165_ATT_DYN_USG_COMM');
159 return fnd_message.get;
160 end if;
161 if instr(lower(temp_query_string),'*/',1,1) > 0 then
162 fnd_message.set_name('PER','AME_400165_ATT_DYN_USG_COMM');
163 return fnd_message.get;
164 end if;
165 if instr(lower(temp_query_string),'--',1,1) > 0 then
166 fnd_message.set_name('PER','AME_400165_ATT_DYN_USG_COMM');
167 return fnd_message.get;
168 end if;
169
170 /* References to following packages is forbidden */
171 if instr(lower(temp_query_string),'dbms_output.',1,1) > 0 then
172 fnd_message.set_name('PER','AME_400625_PROHIBITED_PKG');
173 fnd_message.set_token('PACKAGE','DBMS_OUTPUT');
174 return fnd_message.get;
175 end if;
176 if instr(lower(temp_query_string),'dbms_sql.',1,1) > 0 then
177 fnd_message.set_name('PER','AME_400625_PROHIBITED_PKG');
178 fnd_message.set_token('PACKAGE','DBMS_SQL');
179 return fnd_message.get;
180 end if;
181 if instr(lower(temp_query_string),'ht'||'f.',1,1) > 0 then
182 fnd_message.set_name('PER','AME_400625_PROHIBITED_PKG');
183 fnd_message.set_token('PACKAGE','HTF');
184 return fnd_message.get;
185 end if;
186 if instr(lower(temp_query_string),'ht'||'p.',1,1) > 0 then
187 fnd_message.set_name('PER','AME_400625_PROHIBITED_PKG');
188 fnd_message.set_token('PACKAGE','HTP');
189 return fnd_message.get;
190 end if;
191 if instr(lower(temp_query_string),'owa_util.',1,1) > 0 then
192 fnd_message.set_name('PER','AME_400625_PROHIBITED_PKG');
193 fnd_message.set_token('PACKAGE','OWA_UTIL');
194 return fnd_message.get;
195 end if;
196 if instr(lower(temp_query_string),'owa_cookie.',1,1) > 0 then
197 fnd_message.set_name('PER','AME_400625_PROHIBITED_PKG ');
198 fnd_message.set_token('PACKAGE','OWA_COOKIE');
199 return fnd_message.get;
200 end if;
201
202 --Check for valid bind variable for the module
203 if(p_object is not null) then
204 begin
205 if(p_object in ( ame_util2.attributeObject, ame_util2.itemClassObject) ) then
206 l_allowed_bind_vars(1) := 'transactionId';
207 elsif (p_object = ame_util2.actionTypeObject) then
208 l_allowed_bind_vars(1) := 'parameterOne';
209 l_allowed_bind_vars(2) := 'parameterTwo';
210 elsif (p_object = ame_util2.approverGroupObject or
211 p_object = ame_util2.specialObject) then
212 l_allowed_bind_vars(1) := 'transactionId';
213 l_allowed_bind_vars(2) := 'itemId';
214 l_allowed_bind_vars(3) := 'itemClass';
215 end if;
216 checkBindVariables(queryStringIn => temp_query_string
217 ,allowedBindVars => l_allowed_bind_vars
218 );
219 exception
220 when others then
221 if (p_object = ame_util2.attributeObject) then
222 fnd_message.set_name('PER', 'AME_400794_INV_ATR_BIND_VAR');
223 elsif p_object = ame_util2.specialObject then
224 fnd_message.set_name('PER', 'AME_400799_INV_VATR_BIND_VAR');
225 elsif p_object = ame_util2.itemClassObject then
226 fnd_message.set_name('PER', 'AME_400795_INV_ITU_BIND_VAR');
227 elsif p_object = ame_util2.actionTypeObject then
228 fnd_message.set_name('PER', 'AME_400796_INV_ATY_BIND_VAR');
229 elsif p_object = ame_util2.approverGroupObject then
230 fnd_message.set_name('PER', 'AME_400797_INV_APG_BIND_VAR');
231 end if;
232 return fnd_message.get;
233 end;
234 end if;
235 query_cursor := dbms_sql.open_cursor;
236
237 dbms_sql.parse
238 (query_cursor
239 ,p_query_string
240 ,dbms_sql.native
241 );
242
243 if p_columns is null then
244 dbms_sql.close_cursor(query_cursor);
245 return 'Y';
246 else
247 begin
248 dbms_sql.define_column(query_cursor,p_columns,columna,100);
249 begin
250 dbms_sql.define_column(query_cursor,p_columns + 1,columnb,100);
251 dbms_sql.close_cursor(query_cursor);
252 if p_columns > 1 then
253 fnd_message.set_name('PER','AME_400626_INVALID_NUM_COLS');
254 fnd_message.set_token('NUMCOLS',p_columns);
255 else
256 fnd_message.set_name('PER','AME_400628_INVALID_SINGLE_COL');
257 end if;
258 return fnd_message.get;
259 exception
260 when others then
261 dbms_sql.close_cursor(query_cursor);
262 return 'Y';
263 end;
264 exception
265 when others then
266 dbms_sql.close_cursor(query_cursor);
267 if p_columns > 1 then
268 fnd_message.set_name('PER','AME_400626_INVALID_NUM_COLS');
269 fnd_message.set_token('NUMCOLS',p_columns);
270 else
271 fnd_message.set_name('PER','AME_400628_INVALID_SINGLE_COL');
272 end if;
273 return fnd_message.get;
274 end;
275 end if;
276 exception
277
278 when others then
279 dbms_sql.close_cursor(query_cursor);
280 fnd_message.set_name('PER','AME_400627_QUERY_SQL_INVALID');
281 error_string := sqlerrm;
282 if instrb(error_string,':',1,1) > 0 then
283 error_string := substrb(error_string,instrb(error_string,':',1,1) + 2);
284 end if;
285 fnd_message.set_token('EXPSTRING',error_string);
286 return fnd_message.get;
287 end validate_query;
288
289 function get_action_description(p_action_id in number
290 ,p_effective_date in date default sysdate) return varchar2 is
291 cursor getActionTypeDynamicDesc(actionIdIn in number
292 ,p_effective_date in date) is
293 select dynamic_description
294 from ame_action_types aty,
295 ame_actions act
296 where act.action_id = actionIdIn
297 and act.action_type_id = aty.action_type_id
298 and p_effective_date between act.start_date and nvl(act.end_date - (1/86400), p_effective_date)
299 and p_effective_date between aty.start_date and nvl(aty.end_date - (1/86400), p_effective_date);
300
301 cursor getActionDesc(actionIdIn in number
302 ,p_effective_date in date) is
303 select description
304 from ame_actions_vl
305 where action_id = actionIdIn
306 and p_effective_date between start_date and nvl(end_date - (1/86400), p_effective_date);
307
308 cursor getActionDescQueryAndParam(actionIdIn in number
309 ,p_effective_date in date) is
310 select description_query,
311 parameter,
312 parameter_two
313 from ame_action_types aty,
314 ame_actions act
315 where act.action_id = actionIdIn
316 and act.action_type_id = aty.action_type_id
317 and p_effective_date between act.start_date and nvl(act.end_date - (1/86400), p_effective_date)
318 and p_effective_date between aty.start_date and nvl(aty.end_date - (1/86400), p_effective_date);
319
320 l_query_string ame_action_types.description_query%type;
321 l_parameter_one ame_actions.parameter%type;
322 l_parameter_two ame_actions.parameter_two%type;
323 query_cursor integer;
324 dynamic_description varchar2(1);
325 action_description varchar2(500);
326 l_result integer;
327 begin
328
329 open getActionTypeDynamicDesc(actionIdIn => p_action_id,p_effective_date => p_effective_date);
330 fetch getActionTypeDynamicDesc into dynamic_description;
331 close getActionTypeDynamicDesc;
332 if dynamic_description = 'Y' then
333 open getActionDescQueryAndParam(actionIdIn => p_action_id,p_effective_date => p_effective_date);
334 fetch getActionDescQueryAndParam
335 into l_query_string,
336 l_parameter_one,
337 l_parameter_two;
338 close getActionDescQueryAndParam;
339 begin
340 query_cursor := dbms_sql.open_cursor;
341 dbms_sql.parse
342 (query_cursor
343 ,l_query_string
344 ,dbms_sql.native
345 );
346 if instrb(l_query_string,':parameterOne') > 0 then
347 dbms_sql.bind_variable
348 (query_cursor
349 ,':parameterOne'
350 ,l_parameter_one
351 ,320);
352 end if;
353 if instrb(l_query_string,':parameterTwo') > 0 then
354 dbms_sql.bind_variable
355 (query_cursor
356 ,':parameterTwo'
357 ,l_parameter_two
358 ,320);
359 end if;
360 dbms_sql.define_column(query_cursor,1,action_description,500);
361 l_result := dbms_sql.execute(query_cursor);
362 if dbms_sql.fetch_rows(query_cursor) > 0 then
363 dbms_sql.column_value(query_cursor,1,action_description);
364 end if;
365 dbms_sql.close_cursor(query_cursor);
366 return action_description;
367 exception
368 when others then
369 fnd_message.set_name('PER','AME_400636_INV_DYN_ACT_DESC');
370 fnd_message.set_token('PARAMETER_ONE',l_parameter_one);
371 fnd_message.set_token('PARAMETER_TWO',l_parameter_two);
372 return fnd_message.get;
373 end;
374 else
375 open getActionDesc(actionIdIn => p_action_id,p_effective_date => p_effective_date);
376 fetch getActionDesc into action_description;
377 close getActionDesc;
378 return action_description;
379 end if;
380 end get_action_description;
381
382 function is_approver_valid_in_action(p_action_type_id in number
383 ,p_action_id in number) return varchar2 is
384 l_return_value varchar2(1);
385 l_name ame_action_types.name%type;
386 begin
387 select name into l_name
388 from ame_action_types
389 where action_type_id = p_action_type_id
390 and sysdate between start_date and nvl(end_date-(1/86400),sysdate)
391 and rownum < 2;
392
393 l_return_value := 'N';
394
395 if l_name = ame_util.substitutionTypeName or l_name = ame_util.positionTypeName then
396 select 'Y' into l_return_value
397 from wf_roles wfroles
398 ,ame_actions act
399 where wfroles.name = act.parameter
400 and wfroles.status = 'ACTIVE'
401 and (wfroles.expiration_date is null or
402 sysdate < wfroles.expiration_date)
403 and act.action_type_id = p_action_type_id
404 and act.action_id = p_action_id
405 and sysdate between act.start_date and nvl(act.end_date-(1/86400),sysdate)
406 and rownum < 2;
407 else
408 l_return_value := 'Y';
409 end if;
410 return l_return_value;
411 exception
412 when others then
413 return 'N';
414 end;
415
416 procedure purge_log
417 (p_transaction_type in varchar2 default null
418 ,p_transaction_id in varchar2 default null
419 ,p_success out nocopy varchar2
420 ) is
421 l_application_id integer;
422 l_count integer;
423 begin
424 p_success := 'Y';
425
426 if p_transaction_type is not null then
427 select application_id into l_application_id
428 from ame_calling_apps_vl
429 where upper(trim(application_name)) = upper(trim(p_transaction_type))
430 and sysdate between start_date and nvl(end_date-(1/86400),sysdate);
431 end if;
432
433 if (p_transaction_id is null) and (p_transaction_type is not null ) then
434 select count(*)
435 into l_count
436 from ame_exceptions_log
437 where application_id = l_application_id ;
438 if l_count > 0 then
439 delete from ame_exceptions_log
440 where application_id = l_application_id ;
441 p_success :='Y';
442 else
443 p_success :='N';
444 end if;
445 elsif (p_transaction_id is not null) and (p_transaction_type is null) then
446 select count(*)
447 into l_count
448 from ame_exceptions_log
449 where transaction_id like (p_transaction_id || '%');
450 if l_count > 0 then
451 delete from ame_exceptions_log
452 where transaction_id like (p_transaction_id || '%');
453 p_success :='Y';
454 else
455 p_success :='N';
456 end if;
457 elsif (p_transaction_id is not null) and (p_transaction_type is not null) then
458 select count(*)
459 into l_count
460 from ame_exceptions_log
461 where transaction_id like (p_transaction_id || '%')
462 and application_id = l_application_id ;
463 if l_count > 0 then
464 delete from ame_exceptions_log
465 where application_id = l_application_id
466 and transaction_id like (p_transaction_id || '%');
467 p_success :='Y';
468 else
469 p_success :='N';
470 end if;
471 end if;
472 end purge_log;
473 function truncate_cond_desc(p_description varchar2,
474 p_truncate varchar2) return varchar2 is
475 begin
476 if p_truncate = 'Y' and length(p_description) > 200 then
477 return substr(p_description, 1, 197) || '...';
478 end if;
479 return p_description;
480 end truncate_cond_desc;
481 function get_condition_description(p_condition_id in varchar2,
482 p_truncate in varchar2 default 'Y',
483 p_effective_date in date default sysdate) return varchar2 is
484 cursor c_attr(p_condition_id in number
485 ,p_effective_date in date) is
486 select attr.name
487 ,attr.attribute_type
488 ,attr.approver_type_id
489 ,cond.parameter_one
490 ,cond.parameter_two
491 ,cond.parameter_three
492 ,cond.include_lower_limit
493 ,cond.include_upper_limit
494 ,cond.condition_type
495 from ame_conditions cond
496 ,(select name
497 ,attribute_id
498 ,attribute_type
499 ,approver_type_id
500 from ame_attributes
501 where p_effective_date between start_date
502 and nvl(end_date,p_effective_date)
503 ) attr
504 where cond.condition_id = p_condition_id
505 and cond.attribute_id = attr.attribute_id (+)
506 and p_effective_date between cond.start_date
507 and nvl(cond.end_date-(1/86400),p_effective_date);
508
509 cursor c_str_val(p_condition_id in number
510 ,p_effective_date in date) is
511 select strval.string_value
512 from ame_string_values strval
513 where strval.condition_id = p_condition_id
514 and p_effective_date between strval.start_date
515 and nvl(strval.end_date-(1/86400),p_effective_date)
516 order by strval.string_value;
517
518 l_attribute_name varchar2(200);
519 l_attribute_type varchar2(20);
520 l_approver_type_id number;
521 l_parameter_one varchar2(50);
522 l_parameter_two varchar2(320);
523 l_parameter_three varchar2(100);
524 l_include_lower_limit varchar2(1);
525 l_include_upper_limit varchar2(1);
526 l_condition_type varchar2(10);
527 l_stringvalues varchar2(32000);
528 l_string_value varchar2(4000);
529 l_flag varchar2(4);
530 l_expression varchar2(32000);
531 l_message_name varchar2(30);
532
533 begin
534
535 open c_attr(p_condition_id,p_effective_date);
536 fetch c_attr into l_attribute_name
537 ,l_attribute_type
538 ,l_approver_type_id
539 ,l_parameter_one
540 ,l_parameter_two
541 ,l_parameter_three
542 ,l_include_lower_limit
543 ,l_include_upper_limit
544 ,l_condition_type;
545 close c_attr;
546
547 if l_condition_type = 'post' then
548 l_string_value := ame_approver_type_pkg.getApproverDescription(l_parameter_two);
549 if l_parameter_one = 'any_approver' then
550 fnd_message.set_name('PER','AME_400479_LM_COND_ANY_APPR');
551 fnd_message.set_token('APPROVER',l_string_value);
552 elsif l_parameter_one = 'final_approver' then
553 fnd_message.set_name('PER','AME_400480_LM_COND_FINAL_APPR');
554 fnd_message.set_token('APPROVER',l_string_value);
555 end if;
556 l_expression := fnd_message.get;
557 return truncate_cond_desc(l_expression,
558 p_truncate);
559 end if;
560
561 if(l_attribute_type = 'boolean') then
562 if l_parameter_one = 'true' then
563 fnd_message.set_name('PER','AME_400481_BOOL_T_COND_DESC');
564 fnd_message.set_token('ATTR',l_attribute_name);
565 elsif l_parameter_one = 'false' then
566 fnd_message.set_name('PER','AME_400482_BOOL_F_COND_DESC');
567 fnd_message.set_token('ATTR',l_attribute_name);
568 end if;
569 l_expression := fnd_message.get;
570 return truncate_cond_desc(l_expression,
571 p_truncate);
572 end if;
573
574 if(l_attribute_type = 'string') then
575 l_string_value := null;
576 l_flag := null;
577 open c_str_val(p_condition_id,p_effective_date);
578 loop
579 if l_flag is not null then
580 if (length(l_stringvalues) + length(l_string_value)) > 32000 then
581 exit;
582 end if;
583 l_stringvalues := l_stringvalues ||l_string_value;
584 end if;
585 fetch c_str_val into l_string_value;
586 if c_str_val%notfound then
587 close c_str_val;
588 exit;
589 end if;
590 if l_flag is not null and length(l_stringvalues) < 31999 then
591 l_stringvalues := l_stringvalues || ', ';
592 else
593 l_flag := 'Y';
594 end if;
595 end loop;
596 fnd_message.set_name('PER','AME_400483_STRING_COND_DESC');
597 fnd_message.set_token('ATTR',l_attribute_name);
598 fnd_message.set_token('STRINGVALUES','('||substr(l_stringvalues,1,1900)||')');
599 l_expression := fnd_message.get;
600 return truncate_cond_desc(l_expression,
601 p_truncate);
602 end if;
603
604 if ((l_attribute_type = 'number' and l_approver_type_id is null) or
605 l_attribute_type = 'date' or
606 l_attribute_type = 'currency') then
607 if(l_parameter_one is not null and
608 l_parameter_two is not null and
609 l_include_lower_limit is not null and
610 l_include_upper_limit is not null and
611 l_parameter_one = l_parameter_two and
612 l_include_lower_limit = 'Y' and
613 l_include_lower_limit = 'Y') then
614 if l_attribute_type = 'date' then
615 l_parameter_one := to_char(to_date(l_parameter_one,'yyyy:mm:dd:hh24:mi:ss'),'DD-MON-YYYY');
616 end if;
617 fnd_message.set_name('PER','AME_400484_NUM_COND_EQ_DESC');
618 fnd_message.set_token('ATTR',l_attribute_name);
619 fnd_message.set_token('VALUE',l_parameter_one);
620 l_expression := fnd_message.get;
621 else
622 l_flag := '';
623 if l_parameter_one is not null then
624 if l_attribute_type = 'date' then
625 l_parameter_one := to_char(to_date(l_parameter_one,'yyyy:mm:dd:hh24:mi:ss'),'DD-MON-YYYY');
626 end if;
627 if(l_include_lower_limit = 'Y') then
628 l_flag := 'GE';
629 else
630 l_flag := 'GT';
631 end if;
632 end if;
633 if l_parameter_two is not null then
634 if l_attribute_type = 'date' then
635 l_parameter_two := to_char(to_date(l_parameter_two,'yyyy:mm:dd:hh24:mi:ss'),'DD-MON-YYYY');
636 end if;
637 if(l_include_upper_limit = 'Y') then
638 l_flag := l_flag || 'LE';
639 else
640 l_flag := l_flag || 'LT';
641 end if;
642 end if;
643 if(l_flag = 'GT') then
644 fnd_message.set_name('PER','AME_400485_NUM_COND_GT_DESC');
645 fnd_message.set_token('ATTR',l_attribute_name);
646 fnd_message.set_token('VALUE',l_parameter_one);
647 elsif (l_flag = 'GE') then
648 fnd_message.set_name('PER','AME_400486_NUM_COND_GE_DESC');
649 fnd_message.set_token('ATTR',l_attribute_name);
650 fnd_message.set_token('VALUE',l_parameter_one);
651 elsif (l_flag = 'LT') then
652 fnd_message.set_name('PER','AME_400487_NUM_COND_LT_DESC');
653 fnd_message.set_token('ATTR',l_attribute_name);
654 fnd_message.set_token('VALUE',l_parameter_two);
655 elsif (l_flag = 'LE') then
656 fnd_message.set_name('PER','AME_400488_NUM_COND_LE_DESC');
657 fnd_message.set_token('ATTR',l_attribute_name);
658 fnd_message.set_token('VALUE',l_parameter_two);
659 elsif (l_flag = 'GTLT') then
660 fnd_message.set_name('PER','AME_400489_NUM_COND_GTLT_DESC');
661 fnd_message.set_token('ATTR',l_attribute_name);
662 fnd_message.set_token('VALUE1',l_parameter_one);
663 fnd_message.set_token('VALUE2',l_parameter_two);
664 elsif (l_flag = 'GTLE') then
665 fnd_message.set_name('PER','AME_400490_NUM_COND_GTLE_DESC');
666 fnd_message.set_token('ATTR',l_attribute_name);
667 fnd_message.set_token('VALUE1',l_parameter_one);
668 fnd_message.set_token('VALUE2',l_parameter_two);
669 elsif (l_flag = 'GELE') then
670 fnd_message.set_name('PER','AME_400491_NUM_COND_GELE_DESC');
671 fnd_message.set_token('ATTR',l_attribute_name);
672 fnd_message.set_token('VALUE1',l_parameter_one);
673 fnd_message.set_token('VALUE2',l_parameter_two);
674 elsif (l_flag = 'GELT') then
675 fnd_message.set_name('PER','AME_400492_NUM_COND_GELT_DESC');
676 fnd_message.set_token('ATTR',l_attribute_name);
677 fnd_message.set_token('VALUE1',l_parameter_one);
678 fnd_message.set_token('VALUE2',l_parameter_two);
679 end if;
680 l_expression := fnd_message.get;
681 end if;
682 if(l_attribute_type = 'currency') then
683 l_expression := l_expression || ','||l_parameter_three;
684 end if;
685 return truncate_cond_desc(l_expression,
686 p_truncate);
687 end if;
688
689 if l_attribute_type = 'number' and l_approver_type_id is not null then
690 begin
691 select lookup.meaning ||': '||wfroles.display_name
692 into l_string_value
693 from ame_approver_types appr
694 ,fnd_lookups lookup
695 ,wf_roles wfroles
696 where appr.approver_type_id = l_approver_type_id
697 and lookup.lookup_type = 'FND_WF_ORIG_SYSTEMS'
698 and lookup.lookup_code = appr.orig_system
699 and p_effective_date between appr.start_date
700 and nvl(appr.end_date-(1/86400),p_effective_date)
701 and wfroles.orig_system = appr.orig_system
702 and to_char(wfroles.orig_system_id) = l_parameter_one
703 and wfroles.status = 'ACTIVE'
704 and (wfroles.expiration_date is null or
705 p_effective_date < wfroles.expiration_date);
706 exception
707 when others then
708 begin
709 select lookup.meaning ||': '||wfroles.display_name
710 into l_string_value
711 from ame_approver_types appr
712 ,fnd_lookups lookup
713 ,wf_local_roles wfroles
714 where appr.approver_type_id = l_approver_type_id
715 and lookup.lookup_type = 'FND_WF_ORIG_SYSTEMS'
716 and lookup.lookup_code = appr.orig_system
717 and p_effective_date between appr.start_date
718 and nvl(appr.end_date-(1/86400),p_effective_date)
719 and wfroles.orig_system = appr.orig_system
720 and to_char(wfroles.orig_system_id) = l_parameter_one
721 and rownum < 2;
722
723 fnd_message.set_name('PER','AME_400344_INVALID_APPROVER');
724 fnd_message.set_token('ATTR',l_attribute_name);
725 fnd_message.set_token('NAME',l_string_value);
726 l_string_value := fnd_message.get;
727 return truncate_cond_desc(l_string_value,
728 p_truncate);
729 exception
730 when others then
731 fnd_message.set_name('PER','AME_400344_INVALID_APPROVER');
732 fnd_message.set_token('ATTR',l_attribute_name);
733 select lookup.meaning ||': '|| l_parameter_one
734 into l_string_value
735 from ame_approver_types appr
736 ,fnd_lookups lookup
737 where appr.approver_type_id = l_approver_type_id
738 and lookup.lookup_type = 'FND_WF_ORIG_SYSTEMS'
739 and lookup.lookup_code = appr.orig_system
740 and p_effective_date between appr.start_date
741 and nvl(appr.end_date-(1/86400),p_effective_date)
742 and rownum < 2;
743 fnd_message.set_token('NAME', l_string_value);
744 l_string_value := fnd_message.get;
745 return truncate_cond_desc(l_string_value,
746 p_truncate);
747 end;
748 end;
749
750 fnd_message.set_name('PER','AME_400493_NUM_APPR_COND_DESC');
751 fnd_message.set_token('ATTR',l_attribute_name);
752 fnd_message.set_token('APPROVER',l_string_value);
753 l_expression := fnd_message.get;
754 return truncate_cond_desc(l_expression,
755 p_truncate);
756 end if;
757 return '';
758 end get_condition_description;
759
760 function get_action_types(p_attribute_id number) return varchar2 is
761
762 action_types_list ame_util.longStringList;
763 list varchar2(4000);
764
765 cursor action_types_cursor(l_attribute_id number)is
766 select act.name
767 from ame_action_types act,
768 ame_mandatory_attributes man
769 where Man.action_type_id = Act.action_type_id
770 and sysdate between act.start_date and nvl(act.end_date,sysdate)
771 and sysdate between man.start_date and nvl(man.end_date,sysdate)
772 and man.attribute_id = l_attribute_id
773 order by act.name;
774 begin
775
776 open action_types_cursor(l_attribute_id => p_attribute_id);
777 fetch action_types_cursor bulk collect into action_types_list;
778 close action_types_cursor;
779
780 if action_types_list.count = 0 then
781 fnd_message.set_name('PER','AME_400637_TEXT_NONE');
782 return fnd_message.get;
783 end if;
784
785 for i in 1 .. action_types_list.count loop
786 list := list || action_types_list(i);
787 if i <> action_types_list.count then
788 list := list || '<BR>';
789 end if;
790 end loop;
791 return list;
792
793 exception
794 when others then
795 return null;
796 end get_action_types;
797
798 function get_attribute_category(p_attribute_id number) return varchar2 as
799
800 action_type_id_list ame_util.idList;
801 man_category varchar2(25);
802 req_category varchar2(25);
803 oth_category varchar2(25);
804
805 cursor category_cursor(l_attribute_id number)is
806 select man.action_type_id
807 from ame_attributes atr,
808 ame_mandatory_attributes man
809 where atr.attribute_id = man.attribute_id
810 and atr.attribute_id = l_attribute_id
811 and sysdate between atr.start_date and nvl(atr.end_date-(1/84600),sysdate)
812 and sysdate between man.start_date and nvl(man.end_date-(1/84600),sysdate);
813
814 begin
815
816 man_category := 'MANDATORY_CATEGORY';
817 req_category := 'REQUIRED_CATEGORY';
818 oth_category := 'OTHER_CATEGORY';
819
820 open category_cursor(l_attribute_id => p_attribute_id);
821 fetch category_cursor bulk collect into action_type_id_list;
822
823 if action_type_id_list.count = 0 then
824 return oth_category;
825 end if;
826
827 if action_type_id_list(1) = -1 then
828 return man_category;
829 else
830 return req_category;
831 end if;
832 close category_cursor;
833
834 exception
835 when others then
836 return null;
837
838 end get_attribute_category;
839
840 procedure set_ame_savepoint is
841 begin
842 savepoint ame_savepoint;
843 end set_ame_savepoint;
844
845 procedure rollback_to_ame_savepoint is
846 begin
847 rollback to savepoint ame_savepoint;
848 end rollback_to_ame_savepoint;
849
850 procedure get_value_set_query
851 (p_value_set_id in number
852 ,p_select out nocopy varchar2) is
853 l_select varchar2(4000);
854 l_mapping_code varchar2(100);
855 l_success number;
856 l_validation_type varchar2(1);
857 l_before_from varchar2(4000);
858 l_after_from varchar2(4000);
859 l_column1 varchar2(200);
860 l_column2 varchar2(200);
861 l_v_r fnd_vset.valueset_r;
862 l_v_dr fnd_vset.valueset_dr;
863 l_whr varchar2(4000);
864 l_valid_number_col varchar2(100);
865 l_format_type fnd_flex_value_sets.format_type%TYPE;
866 l_value BOOLEAN;
867 l_out_status VARCHAR2(30);
868 l_out_industry VARCHAR2(30);
869 l_out_oracle_schema VARCHAR2(30);
870 cursor fnd_attr_data_type(TabNameIn in varchar2
871 ,ColumnNameIn in varchar2) is
872 select column_type
873 from fnd_columns fcol
874 ,fnd_tables ftab
875 where ftab.table_name = upper(TabNameIn)
876 and ftab.table_id = fcol.table_id
877 and fcol.column_name =upper(ColumnNameIn);
878 cursor valSetDetails(p_valuesetIdIn in number) is
879 select validation_type,format_type
880 from fnd_flex_value_sets
881 where flex_value_set_id = p_valuesetIdIn;
882 begin
883 --+
884 open valSetDetails(p_valuesetIdIn => p_value_set_id);
885 fetch valSetDetails into l_validation_type,l_format_type;
886 close valSetDetails;
887 --+
888 if (l_validation_type <> 'I' and l_validation_type <> 'F') then
889 p_select := 'AME_400818_INV_VALIDATION_TYP';
890 return;
891 end if;
892 --+
893 if(l_validation_type = 'I') then
894 fnd_flex_val_api.get_independent_vset_select
895 (p_value_set_id => p_value_set_id
896 ,x_select => l_select
897 ,x_mapping_code => l_mapping_code
898 ,x_success => l_success
899 );
900 l_before_from := substrb(l_select,1,instrb(lower(l_select),'from') - 1);
901 l_after_from := substrb(l_select,instrb(lower(l_select),'from')+4);
902 l_before_from := replace(trim(substrb(
903 l_before_from,
904 instrb(lower(l_before_from),'select')+6
905 )
906 ),
907 fnd_global.local_chr(10),
908 '');
909
910 l_column1 := substrb(l_before_from,1,instrb(l_before_from,',')-1);
911 l_before_from := substrb(l_before_from,instrb(l_before_from,',')+1);
912 l_before_from := substrb(l_before_from,instrb(l_before_from,',')+1);
913 if(instrb(l_before_from,',') = 0) then
914 l_column2 := trim(l_before_from);
915 else
916 l_column2 := trim(substrb(l_before_from,1,instrb(l_before_from,',')-1));
917 end if;
918 p_select := 'select '||l_column1||' VALUE, '||l_column2||' MEANING from'||l_after_from;
919 elsif (l_validation_type = 'F') then
920 fnd_vset.get_valueset(valueset_id => p_value_set_id ,
921 valueset => l_v_r,
922 format => l_v_dr);
923 l_whr := trim(l_v_r.table_info.where_clause) ;
924 if(l_whr is not null and
925 lower(substr(l_whr,1,5)) <> 'where') then
926 l_whr := ' where ' || l_whr;
927 end if;
928 l_column2 := trim(l_v_r.table_info.meaning_column_name);
929 if(l_column2 is null) then
930 l_column2 := l_v_r.table_info.value_column_name;
931 end if;
932 p_select := rtrim('select ' ||
933 l_v_r.table_info.value_column_name ||
934 ' Value, ' ||
935 l_column2 ||
936 ' Meaning from ' ||
937 l_v_r.table_info.table_name ||
938 ' ' ||
939 l_whr
940 );
941 end if;
942 if(validate_query(p_select) <> 'Y' ) then
943 p_select := 'AME_400779_INV_VALUE_SET';
944 end if;
945 if(p_select <> 'AME_400779_INV_VALUE_SET' and l_format_type = 'N' and l_validation_type = 'F') then
946 open fnd_attr_data_type( TabNameIn =>l_v_r.table_info.table_name
947 ,ColumnNameIn =>l_v_r.table_info.value_column_name);
948 fetch fnd_attr_data_type into l_valid_number_col;
949 close fnd_attr_data_type;
950 if(l_valid_number_col is not null and l_valid_number_col <> 'N') then
951 p_select := 'AME_400819_INV_VAL_COL_TYPE';
952 end if;
953 end if;
954 --+
955 exception
956 when others then
957 p_select := 'AME_400779_INV_VALUE_SET';
958 end get_value_set_query;
959
960 function get_rule_last_update_date
961 (p_rule_id integer
962 ,p_application_id integer
963 ,p_usage_start_date date
964 ) return date is
965
966 cursor c_last_update_date (c_rule_id integer,c_application_id integer,c_rule_usage_start_date date) is
967 select ar.last_update_date RULE_LUD,
968 ar.last_updated_by RULE_LUB,
969 null RULE_USAGE_LUD,
970 null RULE_USAGE_LUB,
971 null RULE_USAGE_ED,
972 null CONDITION_USAGE_LUD,
973 null CONDITION_USAGE_LUB,
974 null ACTION_USAGE_LUD,
975 null ACTION_USAGE_LUB
976 from ame_rules ar
977 where ar.rule_id = c_rule_id
978 and ar.last_update_date in (select max(last_update_date)
979 from ame_rules art
980 where art.rule_id = c_rule_id)
981 and rownum < 2
982 union
983 select null RULE_LUD,
984 null RULE_LUB,
985 aru.last_update_date RULE_USAGE_LUD,
986 aru.last_updated_by RULE_USAGE_LUB,
987 aru.end_date RULE_USAGE_ED,
988 null CONDITION_USAGE_LUD,
989 null CONDITION_USAGE_LUB,
990 null ACTION_USAGE_LUD,
991 null ACTION_USAGE_LUB
992 from ame_rule_usages aru
993 where aru.rule_id = c_rule_id
994 and aru.item_id = c_application_id
995 and aru.start_date = c_rule_usage_start_date
996 and aru.start_date < aru.end_date
997 and aru.last_update_date in (select max(last_update_date)
998 from ame_rule_usages arut
999 where arut.rule_id = c_rule_id
1000 and arut.item_id = c_application_id
1001 and arut.start_date = c_rule_usage_start_date
1002 and arut.start_date < arut.end_date)
1003 and rownum < 2
1004 union
1005 select null RULE_LUD,
1006 null RULE_LUB,
1007 null RULE_USAGE_LUD,
1008 null RULE_USAGE_LUB,
1009 null RULE_USAGE_ED,
1010 acu.last_update_date CONDITION_USAGE_LUD,
1011 acu.last_updated_by CONDITION_USAGE_LUB,
1012 null ACTION_USAGE_LUD,
1013 null ACTION_USAGE_LUB
1014 from ame_condition_usages acu
1015 where acu.rule_id = c_rule_id
1016 and acu.last_update_date in (select max(last_update_date)
1017 from ame_condition_usages acut
1018 where acut.rule_id = c_rule_id)
1019 and rownum < 2
1020 union
1021 select null RULE_LUD,
1022 null RULE_LUB,
1023 null RULE_USAGE_LUD,
1024 null RULE_USAGE_LUB,
1025 null RULE_USAGE_ED,
1026 null CONDITION_USAGE_LUD,
1027 null CONDITION_USAGE_LUB,
1028 aau.last_update_date ACTION_USAGE_LUD,
1029 aau.last_updated_by ACTION_USAGE_LUB
1030 from ame_action_usages aau
1031 where aau.rule_id = c_rule_id
1032 and aau.last_update_date in (select max(last_update_date)
1033 from ame_action_usages aaut
1034 where aaut.rule_id = c_rule_id)
1035 and rownum < 2;
1036
1037 rl_lud date;
1038 rl_lub integer;
1039 ru_lud date;
1040 ru_lub integer;
1041 cu_lud date;
1042 cu_lub integer;
1043 au_lud date;
1044 au_lub integer;
1045 ru_ed date;
1046
1047 rule_lud date;
1048 rule_lub integer;
1049 rule_usage_lud date;
1050 rule_usage_lub integer;
1051 condition_usage_lud date;
1052 condition_usage_lub integer;
1053 action_usage_lud date;
1054 action_usage_lub integer;
1055 rule_usage_ed date;
1056
1057 latest_update_date date;
1058 latest_update_by integer;
1059
1060 begin
1061
1062 open c_last_update_date(p_rule_id,p_application_id,p_usage_start_date);
1063 loop
1064 fetch c_last_update_date into rl_lud,
1065 rl_lub,
1066 ru_lud,
1067 ru_lub,
1068 ru_ed,
1069 cu_lud,
1070 cu_lub,
1071 au_lud,
1072 au_lub;
1073 exit when c_last_update_date%notfound;
1074 if rl_lud is not null then
1075 rule_lud := rl_lud;
1076 rule_lub := rl_lub;
1077 elsif ru_lud is not null then
1078 rule_usage_lud := ru_lud;
1079 rule_usage_lub := ru_lub;
1080 rule_usage_ed := ru_ed;
1081 elsif cu_lud is not null then
1082 condition_usage_lud := cu_lud;
1083 condition_usage_lub := cu_lub;
1084 elsif au_lud is not null then
1085 action_usage_lud := au_lud;
1086 action_usage_lub := au_lub;
1087 end if;
1088 end loop;
1089 close c_last_update_date;
1090
1091 latest_update_date := rule_lud;
1092 latest_update_by := rule_lub;
1093
1094 if condition_usage_lud > latest_update_date then
1095 latest_update_date := condition_usage_lud;
1096 latest_update_by := condition_usage_lub;
1097 end if;
1098
1099 if action_usage_lud > latest_update_date then
1100 latest_update_date := action_usage_lud;
1101 latest_update_by := action_usage_lub;
1102 end if;
1103
1104 if rule_usage_lud > latest_update_date then
1105 latest_update_date := rule_usage_lud;
1106 latest_update_by := rule_usage_lub;
1107 end if;
1108
1109 if rule_usage_ed < latest_update_date then
1110 latest_update_date := rule_usage_ed;
1111 latest_update_by := rule_usage_lub;
1112 end if;
1113
1114 return latest_update_date;
1115 end get_rule_last_update_date;
1116
1117 function get_rule_last_updated_by
1118 (p_rule_id integer
1119 ,p_application_id integer
1120 ,p_usage_start_date date
1121 ) return integer is
1122
1123 cursor c_last_update_date (c_rule_id integer,c_application_id integer,c_rule_usage_start_date date) is
1124 select ar.last_update_date RULE_LUD,
1125 ar.last_updated_by RULE_LUB,
1126 null RULE_USAGE_LUD,
1127 null RULE_USAGE_LUB,
1128 null RULE_USAGE_ED,
1129 null CONDITION_USAGE_LUD,
1130 null CONDITION_USAGE_LUB,
1131 null ACTION_USAGE_LUD,
1132 null ACTION_USAGE_LUB
1133 from ame_rules ar
1134 where ar.rule_id = c_rule_id
1135 and ar.last_update_date in (select max(last_update_date)
1136 from ame_rules art
1137 where art.rule_id = c_rule_id)
1138 and rownum < 2
1139 union
1140 select null RULE_LUD,
1141 null RULE_LUB,
1142 aru.last_update_date RULE_USAGE_LUD,
1143 aru.last_updated_by RULE_USAGE_LUB,
1144 aru.end_date RULE_USAGE_ED,
1145 null CONDITION_USAGE_LUD,
1146 null CONDITION_USAGE_LUB,
1147 null ACTION_USAGE_LUD,
1148 null ACTION_USAGE_LUB
1149 from ame_rule_usages aru
1150 where aru.rule_id = c_rule_id
1151 and aru.item_id = c_application_id
1152 and aru.start_date = c_rule_usage_start_date
1153 and aru.start_date < aru.end_date
1154 and aru.last_update_date in (select max(last_update_date)
1155 from ame_rule_usages arut
1156 where arut.rule_id = c_rule_id
1157 and arut.item_id = c_application_id
1158 and arut.start_date = c_rule_usage_start_date
1159 and arut.start_date < arut.end_date)
1160 and rownum < 2
1161 union
1162 select null RULE_LUD,
1163 null RULE_LUB,
1164 null RULE_USAGE_LUD,
1165 null RULE_USAGE_LUB,
1166 null RULE_USAGE_ED,
1167 acu.last_update_date CONDITION_USAGE_LUD,
1168 acu.last_updated_by CONDITION_USAGE_LUB,
1169 null ACTION_USAGE_LUD,
1170 null ACTION_USAGE_LUB
1171 from ame_condition_usages acu
1172 where acu.rule_id = c_rule_id
1173 and acu.last_update_date in (select max(last_update_date)
1174 from ame_condition_usages acut
1175 where acut.rule_id = c_rule_id)
1176 and rownum < 2
1177 union
1178 select null RULE_LUD,
1179 null RULE_LUB,
1180 null RULE_USAGE_LUD,
1181 null RULE_USAGE_LUB,
1182 null RULE_USAGE_ED,
1183 null CONDITION_USAGE_LUD,
1184 null CONDITION_USAGE_LUB,
1185 aau.last_update_date ACTION_USAGE_LUD,
1186 aau.last_updated_by ACTION_USAGE_LUB
1187 from ame_action_usages aau
1188 where aau.rule_id = c_rule_id
1189 and aau.last_update_date in (select max(last_update_date)
1190 from ame_action_usages aaut
1191 where aaut.rule_id = c_rule_id)
1192 and rownum < 2;
1193
1194 rl_lud date;
1195 rl_lub integer;
1196 ru_lud date;
1197 ru_lub integer;
1198 cu_lud date;
1199 cu_lub integer;
1200 au_lud date;
1201 au_lub integer;
1202 ru_ed date;
1203
1204 rule_lud date;
1205 rule_lub integer;
1206 rule_usage_lud date;
1207 rule_usage_lub integer;
1208 condition_usage_lud date;
1209 condition_usage_lub integer;
1210 action_usage_lud date;
1211 action_usage_lub integer;
1212 rule_usage_ed date;
1213
1214 latest_update_date date;
1215 latest_update_by integer;
1216
1217 begin
1218
1219 open c_last_update_date(p_rule_id,p_application_id,p_usage_start_date);
1220 loop
1221 fetch c_last_update_date into rl_lud,
1222 rl_lub,
1223 ru_lud,
1224 ru_lub,
1225 ru_ed,
1226 cu_lud,
1227 cu_lub,
1228 au_lud,
1229 au_lub;
1230 exit when c_last_update_date%notfound;
1231 if rl_lud is not null then
1232 rule_lud := rl_lud;
1233 rule_lub := rl_lub;
1234 elsif ru_lud is not null then
1235 rule_usage_lud := ru_lud;
1236 rule_usage_lub := ru_lub;
1237 rule_usage_ed := ru_ed;
1238 elsif cu_lud is not null then
1239 condition_usage_lud := cu_lud;
1240 condition_usage_lub := cu_lub;
1241 elsif au_lud is not null then
1242 action_usage_lud := au_lud;
1243 action_usage_lub := au_lub;
1244 end if;
1245 end loop;
1246 close c_last_update_date;
1247
1248 latest_update_date := rule_lud;
1249 latest_update_by := rule_lub;
1250
1251 if condition_usage_lud > latest_update_date then
1252 latest_update_date := condition_usage_lud;
1253 latest_update_by := condition_usage_lub;
1254 end if;
1255
1256 if action_usage_lud > latest_update_date then
1257 latest_update_date := action_usage_lud;
1258 latest_update_by := action_usage_lub;
1259 end if;
1260
1261 if rule_usage_lud > latest_update_date then
1262 latest_update_date := rule_usage_lud;
1263 latest_update_by := rule_usage_lub;
1264 end if;
1265
1266 if rule_usage_ed < latest_update_date then
1267 latest_update_date := rule_usage_ed;
1268 latest_update_by := rule_usage_lub;
1269 end if;
1270
1271 return latest_update_by;
1272 end get_rule_last_updated_by;
1273
1274 function is_rule_updatable
1275 (p_rule_id integer
1276 ,p_application_id integer
1277 ,p_usage_start_date date
1278 ) return varchar2 is
1279
1280 cursor active_rule_cursor (ruleId integer) is
1281 select rule_id
1282 from ame_rules
1283 where rule_id = ruleId
1284 and (sysdate between start_date and nvl(end_date - (1/86400),sysdate) or
1285 (start_date > sysdate and start_date < nvl(end_date,start_date + (1/86400)))
1286 );
1287
1288 cursor active_rule_usage_count_cursor (ruleId integer,applicationId integer,usageStartDate date) is
1289 select count(rule_id)
1290 from ame_rule_usages
1291 where rule_id = ruleId
1292 and item_id = applicationId
1293 and start_date = usageStartDate
1294 and start_date < end_date
1295 and (sysdate between start_date and nvl(end_date - (1/86400),sysdate) or
1296 (start_date > sysdate and start_date < nvl(end_date,start_date + (1/86400)))
1297 );
1298
1299 dummy_rule_id integer;
1300 dummy_rule_usage_count integer;
1301
1302 begin
1303
1304 open active_rule_cursor(p_rule_id);
1305 fetch active_rule_cursor into dummy_rule_id;
1306 if active_rule_cursor%notfound then
1307 close active_rule_cursor;
1308 return 'UpdateDisabled';
1309 end if;
1310 close active_rule_cursor;
1311
1312 open active_rule_usage_count_cursor(p_rule_id,p_application_id,p_usage_start_date);
1313 fetch active_rule_usage_count_cursor into dummy_rule_usage_count;
1314 close active_rule_usage_count_cursor;
1315 if dummy_rule_usage_count = 0 then
1316 return 'UpdateDisabled';
1317 else
1318 return 'UpdateEnabled';
1319 end if;
1320
1321 end is_rule_updatable;
1322
1323 function get_rule_last_update_action
1324 (p_rule_id integer
1325 ,p_application_id integer
1326 ,p_usage_start_date date
1327 ,p_usage_end_date date
1328 ) return varchar2 is
1329
1330 cursor c_row_count (c_rule_id integer,c_application_id integer,c_rule_usage_start_date date) is
1331 select count(ar.rule_id) RULE_COUNT,
1332 null CONDITION_USAGE_COUNT,
1333 null ACTION_USAGE_COUNT
1334 from ame_rules ar
1335 where ar.rule_id = c_rule_id
1336 and ar.last_update_date > (c_rule_usage_start_date + (1/86400))
1337 union
1338 select null RULE_COUNT,
1339 count(acu.rule_id) CONDITION_USAGE_COUNT,
1340 null ACTION_USAGE_COUNT
1341 from ame_condition_usages acu
1342 where acu.rule_id = c_rule_id
1343 and acu.last_update_date > (c_rule_usage_start_date + (1/86400))
1344 union
1345 select null RULE_COUNT,
1346 null CONDITION_USAGE_COUNT,
1347 count(aau.rule_id) ACTION_USAGE_COUNT
1348 from ame_action_usages aau
1349 where aau.rule_id = c_rule_id
1350 and aau.last_update_date > (c_rule_usage_start_date + (1/86400));
1351 rl_cnt integer;
1352 cu_cnt integer;
1353 au_cnt integer;
1354 rule_count integer;
1355 condition_usage_count integer;
1356 action_usage_count integer;
1357 latest_action varchar2(10);
1358 begin
1359 open c_row_count(p_rule_id,p_application_id,p_usage_start_date);
1360 loop
1361 fetch c_row_count into rl_cnt,
1362 cu_cnt,
1363 au_cnt;
1364 exit when c_row_count%notfound;
1365 if rl_cnt is not null then
1366 rule_count := rl_cnt;
1367 elsif cu_cnt is not null then
1368 condition_usage_count := cu_cnt;
1369 elsif au_cnt is not null then
1370 action_usage_count := au_cnt;
1371 end if;
1372 end loop;
1373 close c_row_count;
1374 if p_usage_end_date < sysdate then
1375 latest_action := 'DELETED';
1376 elsif (rule_count is not null and rule_count > 0) or
1377 (condition_usage_count is not null and condition_usage_count > 0) or
1378 (action_usage_count is not null and action_usage_count > 0) then
1379 latest_action := 'UPDATED';
1380 else
1381 latest_action := 'CREATED';
1382 end if;
1383 return latest_action;
1384 end get_rule_last_update_action;
1385
1386 function is_valid_attribute(p_attribute_id in varchar2
1387 ,p_application_id in varchar2
1388 ,p_allow_all in varchar2) return varchar2 as
1389 l_attribute_id integer;
1390 l_application_id integer;
1391 l_item_id integer;
1392 return_val varchar2(30);
1393 temp_count integer;
1394
1395 cursor c_sel1(attributeIdIn in integer
1396 ,applicationIdIn in integer) is
1397 select count(*)
1398 from ame_attribute_usages
1399 where attribute_id = attributeIdIn
1400 and application_id = applicationIdIn
1401 and sysdate between start_date
1402 and nvl(end_date - (1/86400), sysdate);
1403
1404 begin
1405
1406 l_attribute_id := to_number(p_attribute_id);
1407 l_application_id := to_number(p_application_id);
1408
1409 open c_sel1(l_attribute_id,l_application_id);
1410 fetch c_sel1 into temp_count;
1411 if c_sel1%found and
1412 temp_count > 0 then
1413 close c_sel1;
1414 return_val := 'AttributeExists';
1415 return(return_val);
1416 end if;
1417 close c_sel1;
1418
1419 select count(*)
1420 into temp_count
1421 from ame_item_class_usages itu,
1422 ame_attributes atr
1423 where itu.item_class_id = atr.item_class_id
1424 and itu.application_id = l_application_id
1425 and atr.attribute_id = l_attribute_id
1426 and sysdate between itu.start_date
1427 and nvl(itu.end_date - (1/86400), sysdate)
1428 and sysdate between atr.start_date
1429 and nvl(atr.end_date - (1/86400), sysdate);
1430
1431 if temp_count = 0 then
1432 return_val := 'ItemClassNotExists';
1433 return(return_val);
1434 end if;
1435
1436 if p_allow_all = 'yes' then
1437 return_val := 'NoIssues';
1438 return(return_val);
1439 end if;
1440
1441 select atr.approver_type_id
1442 into temp_count
1443 from ame_attributes atr
1444 where atr.attribute_id = l_attribute_id
1445 and sysdate between atr.start_date
1446 and nvl(atr.end_date - (1/86400), sysdate);
1447
1448 if temp_count is null then
1449 return_val := 'NoIssues';
1450 return(return_val);
1451 end if;
1452
1453 select count(*) into temp_count
1454 from ame_attributes atr,
1455 ame_approver_types apt
1456 where atr.approver_type_id = apt.approver_type_id
1457 and atr.attribute_id = l_attribute_id
1458 and sysdate between atr.start_date
1459 and nvl(atr.end_date - (1/86400), sysdate)
1460 and sysdate between apt.start_date
1461 and nvl(apt.end_date - (1/86400), sysdate)
1462 and apt.orig_system in ('FND_USR','PER');
1463
1464 if temp_count =0 then
1465 return_val := 'ApproverTypeNotExists';
1466 return(return_val);
1467 else
1468 return_val := 'NoIssues';
1469 return(return_val);
1470 end if;
1471
1472 exception
1473 when others then
1474 null;
1475 end is_valid_attribute;
1476
1477 function get_rule_end_date
1478 (p_rule_id integer
1479 ) return date as
1480 l_rule_end_date date;
1481 begin
1482 begin
1483 select max(ar.end_date)
1484 into l_rule_end_date
1485 from ame_rules ar
1486 where rule_id = p_rule_id;
1487 return l_rule_end_date;
1488 exception
1489 when no_data_found then
1490 return null;
1491 when too_many_rows then
1492 return null;
1493 end;
1494 end get_rule_end_date;
1495
1496 function check_seeddb return varchar2 as
1497 begin
1498 if fnd_global.resp_name = 'AME Developer' then
1499 return 'Y';
1500 end if;
1501 return 'N';
1502 end check_seeddb;
1503
1504 function get_rule_id return number is
1505 l_rule_id number;
1506 begin
1507 if fnd_global.resp_name = 'AME Developer' then
1508 select min(rule_id) - 1
1509 into l_rule_id
1510 from ame_rules;
1511 else
1512 select ame_rules_s.nextval
1513 into l_rule_id
1514 from sys.dual;
1515 end if;
1516 return l_rule_id;
1517 end get_rule_id;
1518
1519 function get_condition_id return number is
1520 l_condition_id number;
1521 begin
1522 if fnd_global.resp_name = 'AME Developer' then
1523 select min(condition_id) - 1
1524 into l_condition_id
1525 from ame_conditions;
1526 else
1527 select ame_conditions_s.nextval
1528 into l_condition_id
1529 from sys.dual;
1530 end if;
1531 return l_condition_id;
1532 end get_condition_id;
1533
1534 function get_item_class_id return number is
1535 l_item_class_id number;
1536 begin
1537 select max(item_class_id) + 1
1538 into l_item_class_id
1539 from ame_item_classes;
1540
1541 return l_item_class_id;
1542 end get_item_class_id;
1543
1544 function is_seed_user
1545 (p_user_id integer
1546 ) return number is
1547 begin
1548 if p_user_id in (1,2,120,121) then
1549 return ame_util.seededDataCreatedById;
1550 else
1551 return 0;
1552 end if;
1553 end is_seed_user;
1554 function getNextApproverTypeId return integer is
1555 nextSequence integer;
1556 countOfIds integer;
1557 begin
1558 while true loop
1559 select ame_approver_types_s.nextval into nextSequence from dual;
1560 select count(*)
1561 into countOfIds
1562 from ame_approver_types
1563 where approver_type_id = nextSequence;
1564 if countOfIds = 0 then
1565 return nextSequence;
1566 end if;
1567 end loop;
1568 end getNextApproverTypeId;
1569 end ame_utility_pkg;