1 package body ame_rule_utility_pkg as
2 /* $Header: ameruleutility.pkb 120.3 2006/08/24 13:08:39 prasashe noship $ */
3 --+
4 function isDescriptionExists(ruleIdIn in integer
5 ,endDateIn in date) return boolean is
6 dummy ame_rules.description%type;
7 begin
8 select rule1.description into dummy
9 from ame_rules rule1
10 ,ame_rules rule2
11 where rule1.description = rule2.description
12 and rule1.end_date = endDateIn
13 and ((sysdate between rule2.start_date and nvl(rule2.end_date - (1/86400),sysdate))
14 or
15 (sysdate < rule2.start_date and rule2.start_date < nvl(rule2.end_date, rule2.start_date + (1/86400)))
16 );
17 return true;
18 exception
19 when no_data_found then
20 return false;
21 when others then
22 return true;
23 end isDescriptionExists;
24 --+
25 function isProductionAction(actionIdIn in integer) return boolean is
26 --+
27 dummy number;
28 --+
29 begin
30 select axu.rule_type into dummy
31 from ame_actions act
32 ,ame_action_type_usages axu
33 where act.action_id = actionIdIn
34 and act.action_type_id = axu.action_type_id
35 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
36 and sysdate between axu.start_date and nvl(axu.end_date - (1/86400), sysdate);
37 --+
38 if dummy = 7 then
39 return true;
40 else
41 return false;
42 end if;
43 exception
44 when no_data_found then
45 return false;
46 when others then
47 return false;
48 end isProductionAction;
49 --+
50 function isProdRule(ruleIdIn in integer)return boolean is
51 dummy number;
52 begin
53 select rule_type into dummy from ame_rules
54 where rule_id = ruleIdIn
55 and rownum < 2;
56 if dummy = 7 then
57 return true;
58 else
59 return false;
60 end if;
61 exception
62 when no_data_found then
63 return false;
64 when others then
65 return false;
66 end isProdRule;
67 --+
68 function hasProductionActions(ruleIdIn in integer
69 ,endDateIn in date)return boolean is
70 --+
71 cursor getActions(ruleIdIn in integer) is
72 select 'Y'
73 from ame_action_types aty
74 ,ame_actions act
75 ,ame_action_usages acu
76 ,ame_rules rul
77 ,ame_action_type_usages axu
78 where rul.rule_id = ruleIdIn
79 and rul.rule_id = acu.rule_id
80 and acu.action_id = act.action_id
81 and act.action_type_id = aty.action_type_id
82 and aty.action_type_id = axu.action_type_id
83 and axu.rule_type = 7
84 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
85 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
86 and sysdate between axu.start_date and nvl(axu.end_date - (1/86400), sysdate)
87 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
88 or
89 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
90 )
91 and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
92 or
93 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
94 );
95 --+
96 cursor getActions2(ruleIdIn in integer
97 ,endDateIn in date) is
98 select 'Y'
99 from ame_action_types aty
100 ,ame_actions act
101 ,ame_action_usages acu
102 ,ame_rules rul
103 ,ame_action_type_usages axu
104 where rul.rule_id = ruleIdIn
105 and rul.rule_id = acu.rule_id
106 and acu.action_id = act.action_id
107 and act.action_type_id = aty.action_type_id
108 and aty.action_type_id = axu.action_type_id
109 and axu.rule_type = 7
110 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
111 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
112 and sysdate between axu.start_date and nvl(axu.end_date - (1/86400), sysdate)
113 and rul.end_date = endDateIn
114 and acu.end_date = rul.end_date;
115 --+
116 dummy varchar2(1);
117 result boolean;
118 begin
119 result := false;
120 if endDateIn is null then
121 open getActions(ruleIdIn => ruleIdIn);
122 fetch getActions into dummy;
123 if(getActions%found) then
124 result := true;
125 end if;
126 close getActions;
127 else
128 open getActions2(ruleIdIn => ruleIdIn
129 ,endDateIn => endDateIn);
130 fetch getActions2 into dummy;
131 if(getActions2%found) then
132 result := true;
133 end if;
134 close getActions2;
135 end if;
136 return result;
137 exception
138 when no_data_found then
139 return false;
140 when others then
141 return false;
142 end hasProductionActions;
143 --+
144 --+
145 --+
146 procedure checkRuleForUsage2(ruleIdIn in integer
147 ,applicationIdIn in integer
148 ,endDateIn in date
149 ,resultOut out nocopy varchar2) as
150 --+
151 cursor checkAttributeUsagesCursor(ruleIdIn in integer
152 ,applicationIdIn in integer
153 ,endDateIn in date ) is
154 select distinct atr.attribute_id, atr.name
155 from ame_attributes atr
156 ,ame_conditions cond
157 ,ame_condition_usages condu
158 ,ame_rules rules
159 where cond.attribute_id = atr.attribute_id
160 and condu.condition_id = cond.condition_id
161 and condu.rule_id = rules.rule_id
162 and rules.rule_id = ruleIdIn
163 and cond.condition_type <> ame_util.listModConditionType
164 and atr.attribute_id not in (select attribute_id
165 from ame_attribute_usages
166 where application_id = applicationIdIn
167 and sysdate between start_date
168 and nvl(end_date - (1/86400), sysdate))
169 and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
170 and sysdate between cond.start_date and nvl(cond.end_date - (1/86400), sysdate)
171 and rules.end_date = endDateIn
172 and condu.end_date = rules.end_date
173 union
174 select distinct atr.attribute_id, atr.name
175 from ame_attributes atr
176 ,ame_action_usages acu
177 ,ame_actions act
178 ,ame_mandatory_attributes ama
179 ,ame_rules rules
180 where ama.attribute_id = atr.attribute_id
181 and act.action_id = acu.action_id
182 and act.action_type_id = ama.action_type_id
183 and acu.rule_id = rules.rule_id
184 and rules.rule_id = ruleIdIn
185 and ama.attribute_id not in (select attribute_id
186 from ame_attribute_usages
187 where application_id = applicationIdIn
188 and sysdate between start_date
189 and nvl(end_date - (1/86400), sysdate))
190 and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
191 and sysdate between ama.start_date and nvl(ama.end_date - (1/86400), sysdate)
192 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
193 and rules.end_date = endDateIn
194 and acu.end_date = rules.end_date;
195 --+
196 cursor checkItemClass(ruleIdIn in integer
197 ,applicationIdIn in integer
198 ,endDatein in date) is
199 select name
200 from ame_item_classes itc
201 ,ame_rules rul
202 where rul.item_class_id = itc.item_class_id
203 and rul.rule_id = ruleIdIn
204 and sysdate between itc.start_date and nvl(itc.end_date - (1/86400), sysdate)
205 and rul.end_date = endDateIn
206 and itc.item_class_id not in (select item_class_id
207 from ame_item_class_usages itu
208 where itu.application_id = applicationIdIn
209 and sysdate between itu.start_date
210 and nvl(itu.end_date - (1/86400), sysdate));
211 --+
212 cursor getApplicationName(applicationIdIn in integer)is
213 select application_name
214 from ame_calling_apps
215 where application_id = applicationIdIn
216 and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
217 --+
218 cursor getItemClass(ruleIdIn in integer
219 ,endDateIn in date) is
220 select 'Y'
221 from ame_rules rul
222 ,ame_item_classes itc
223 where rul.rule_id = ruleIdIn
224 and rul.item_class_id = itc.item_class_id
225 and itc.name <> ame_util.headerItemClassName
226 and sysdate between itc.start_date and nvl(itc.end_date - (1/86400), sysdate)
227 and rul.end_date = endDateIn;
228 --+
229 cursor checkActionTypes(ruleIdIn in integer
230 ,applicationIdIn in integer
231 ,endDateIn in date) is
232 select distinct aty.action_type_id, aty.name
233 from ame_action_types aty
234 ,ame_actions act
235 ,ame_action_usages acu
236 ,ame_rules rul
237 where rul.rule_id = ruleIdIn
238 and rul.rule_id = acu.rule_id
239 and acu.action_id = act.action_id
240 and act.action_type_id = aty.action_type_id
241 and aty.action_type_id not in (select atf.action_type_id
242 from ame_action_type_config atf
243 where atf.application_id = applicationIdIn
244 and sysdate between atf.start_date
245 and nvl(atf.end_date - (1/86400), sysdate))
246 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
247 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
248 and rul.end_date = endDateIn
249 and acu.end_date = rul.end_date;
250 --+
251 cursor checkApproverGroups(ruleIdIn in integer
252 ,applicationIdIn in integer
253 ,endDateIn in date) is
254 select apg.approval_group_id
255 ,apg.name
256 from ame_action_types aty
257 ,ame_actions act
258 ,ame_action_usages acu
259 ,ame_rules rul
260 ,ame_approval_groups apg
261 where rul.rule_id = ruleIdIn
262 and rul.end_date = endDateIn
263 and rul.start_date < endDateIn
264 and rul.rule_id = acu.rule_id
265 and acu.end_date = rul.end_date
266 and acu.start_date >= rul.start_date
267 and acu.action_id = act.action_id
268 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
269 and act.action_type_id = aty.action_type_id
270 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
271 and to_char(apg.approval_group_id) = act.parameter
272 and sysdate between apg.start_date and nvl(apg.end_date - (1/86400), sysdate)
273 and not exists (select null
274 from ame_approval_group_config agf
275 where agf.application_id = applicationIdIn
276 and agf.approval_group_id = apg.approval_group_id
277 and sysdate between agf.start_date and nvl(agf.end_date - (1/86400), sysdate))
278 and aty.name in ('pre-chain-of-authority approvals'
279 ,'post-chain-of-authority approvals'
280 ,'approval-group chain of authority');
281 --+
282 cursor checkLMConditions(ruleIdIn in integer
283 ,endDateIn in date) is
284 select 'Y'
285 from ame_rules rul
286 ,ame_conditions con
287 ,ame_condition_usages cnu
288 ,wf_roles wf
289 where rul.rule_id = ruleIdIn
290 and con.condition_id = cnu.condition_id
291 and con.condition_type = ame_util.listModConditionType
292 and cnu.rule_id = rul.rule_id
293 and con.parameter_two = wf.name
294 and wf.orig_system = 'POS'
295 and wf.status = 'ACTIVE'
296 and rul.end_date = endDateIn
297 and cnu.end_date = rul.end_date;
298 --+
299 dummy varchar2(1);
300 errorExists boolean;
301 attributeIdList ame_util.idList;
302 attributeNamesList ame_util.stringList;
303 itemClassName ame_item_classes.name%type;
304 applicationName ame_calling_apps.application_name%type;
305 tempValue ame_config_vars.variable_value%type;
306 actionTypeIdList ame_util.idList;
307 actionTypeNameList ame_util.stringList;
308 endDate date;
309 --+
310 begin
311 errorExists := false;
312 endDate := endDateIn;
313 resultOut := 'Y';
314 hr_multi_message.enable_message_list;
315 --+
316 --+ get application name
317 --+
318 open getApplicationName(applicationIdIn => applicationIdIn);
319 fetch getApplicationName into applicationName;
320 close getApplicationName;
321 --+
322 --+
323 --+
324 tempValue := ame_util.getConfigVar
325 (variableNameIn => ame_util.allowAllICRulesConfigVar
326 ,applicationIdIn => applicationIdIn);
327 if(tempValue = ame_util.no) then
328 open getItemClass(ruleIdIn => ruleIdIn
329 ,endDateIn => endDate);
330 fetch getItemClass into dummy;
331 if(getItemClass%found) then
332 fnd_message.set_name('PER','AME_400633_SUBITC_TTY_NO_USAGE');
333 fnd_message.set_token('TXTYPENAME',applicationName);
334 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
335 close getItemClass;
336 resultOut := 'N';
337 fnd_message.raise_error;
338 return;
339 end if;
340 close getItemClass;
341 end if;
342 --+
343 --+
344 --+
345 tempValue := ame_util.getConfigVar
346 (variableNameIn => ame_util.allowAllApproverTypesConfigVar
347 ,applicationIdIn => applicationIdIn);
348 if(tempValue = ame_util.no) then
349 open checkLMConditions(ruleIdIn => ruleIdIn
350 ,endDateIn => endDate);
351 fetch checkLMConditions into dummy;
352 if(checkLMConditions%found) then
353 fnd_message.set_name('PER','AME_400641_TTY_INV_APPR_TYPE');
354 fnd_message.set_token('TXTYPENAME',applicationName);
355 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
356 close checkLMConditions;
357 resultOut := 'N';
358 fnd_message.raise_error;
359 return;
360 end if;
361 close checkLMConditions;
362 end if;
363 --+
364 --+ check prodution functionality
365 --+
366 tempValue := ame_util.getConfigVar
367 (variableNameIn => ame_util.productionConfigVar
368 ,applicationIdIn => applicationIdIn);
369
370 if(tempValue <> ame_util.allProductions) then
371 if(tempValue <> ame_util.perTransactionProductions and isProdRule(ruleIdIn)) then
372 fnd_message.set_name('PER','AME_400639_TTY_NO_PROD_RULES');
373 fnd_message.set_token('TXTYPENAME',applicationName);
374 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
375 resultOut := 'N';
376 fnd_message.raise_error;
377 return;
378 elsif(tempValue <> ame_util.perApproverProductions and hasProductionActions(ruleIdIn,endDate) and not isProdRule(ruleIdIn)) then
379 fnd_message.set_name('PER','AME_400640_TTY_NO_PROD_ACTIONS');
380 fnd_message.set_token('TXTYPENAME',applicationName);
381 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
382 resultOut := 'N';
383 fnd_message.raise_error;
384 return;
385 end if;
386 end if;
387 --+
388 --+ check item_class
389 --+
390 open checkItemClass(ruleIdIn => ruleIdIn
391 ,applicationIdIn => applicationIdIn
392 ,endDateIn => endDate);
393 fetch checkItemClass into itemClassName;
394 if(checkItemClass%found)then
395 fnd_message.set_name('PER','AME_400632_ITC_TTY_NO_USAGE');
396 fnd_message.set_token('ITEM_CLASS',itemClassName);
397 fnd_message.set_token('TXTYPENAME',applicationName);
398 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
399 errorExists := true;
400 end if;
401 close checkItemClass;
402 --+
403 --+ check for attributes
404 --+
405 open checkAttributeUsagesCursor(ruleIdIn => ruleIdIn
406 ,applicationIdIn => applicationIdIn
407 ,endDateIn => endDate);
408 fetch checkAttributeUsagesCursor
409 bulk collect into attributeIdList
410 ,attributeNamesList;
411 close checkAttributeUsagesCursor;
412 if attributeIdList.count > 0 then
413 errorExists := true;
414 end if;
415 for i in 1 .. attributeIdList.count loop
416 fnd_message.set_name('PER','AME_400149_ATT_TTY_NO_USAGE');
417 fnd_message.set_token('ATTRIBUTE',attributeNamesList(i));
418 fnd_message.set_token('APPLICATION',applicationName);
419 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
420 end loop;
421 --+
422 --+ check for action types
423 --+
424 open checkActionTypes(ruleIdIn => ruleIdIn
425 ,applicationIdIn => applicationIdIn
426 ,endDateIn => endDate);
427 fetch checkActionTypes
428 bulk collect into actionTypeIdList
429 ,actionTypeNameList;
430 close checkActionTypes;
431 if actionTypeIdList.count > 0 then
432 errorExists := true;
433 end if;
434 for i in 1 .. actionTypeIdList.count loop
435 fnd_message.set_name('PER','AME_400634_ATY_TTY_NO_USAGE');
436 fnd_message.set_token('ACTION_TYPE',actionTypeNameList(i));
437 fnd_message.set_token('TXTYPENAME',applicationName);
438 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
439 end loop;
440 --+
441 --+ check for approval groups
442 --+
443 actionTypeIdList.delete;
444 open checkApproverGroups(ruleIdIn => ruleIdIn
445 ,applicationIdIn => applicationIdIn
446 ,endDateIn => endDate);
447 fetch checkApproverGroups
448 bulk collect into actionTypeIdList
449 ,actionTypeNameList;
450 close checkApproverGroups;
451 if actionTypeIdList.count > 0 then
452 errorExists := true;
453 end if;
454 for i in 1 .. actionTypeIdList.count loop
455 fnd_message.set_name('PER','AME_400643_APG_TTY_NO_USAGE');
456 fnd_message.set_token('GROUP',actionTypeNameList(i));
457 fnd_message.set_token('TXTYPENAME',applicationName);
458 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
459 end loop;
460 --+
461 if errorExists then
462 resultOut := 'N';
463 fnd_message.raise_error;
464 end if;
465 exception
466 when others then
467 null;
468 end checkRuleForUsage2;
469 --+
470 --+
471 --+
472 procedure checkRuleForUsage(ruleIdIn in integer
473 ,applicationIdIn in integer
474 ,endDateIn in varchar2
475 ,resultOut out nocopy varchar2) as
476 --+
477 cursor checkRuleCursor(ruleIdIn in integer
478 ,applicationIdIn in integer) is
479 select 'Y'
480 from ame_rule_usages
481 where rule_id = ruleIdIn
482 and item_id = applicationIdIn
483 and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
484 or
485 (sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
486 );
487 --+
488 cursor checkAttributeUsagesCursor(ruleIdIn in integer
489 ,applicationIdIn in integer) is
490 select distinct atr.attribute_id, atr.name
491 from ame_attributes atr
492 ,ame_conditions cond
493 ,ame_condition_usages condu
494 ,ame_rules rules
495 where cond.attribute_id = atr.attribute_id
496 and condu.condition_id = cond.condition_id
497 and condu.rule_id = rules.rule_id
498 and rules.rule_id = ruleIdIn
499 and cond.condition_type <> ame_util.listModConditionType
500 and atr.attribute_id not in (select attribute_id
501 from ame_attribute_usages
502 where application_id = applicationIdIn
503 and sysdate between start_date
504 and nvl(end_date - (1/86400), sysdate))
505 and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
506 and sysdate between cond.start_date and nvl(cond.end_date - (1/86400), sysdate)
507 and ((sysdate between rules.start_date and nvl(rules.end_date - (1/86400),sysdate))
508 or
509 (sysdate < rules.start_date and rules.start_date < nvl(rules.end_date, rules.start_date + (1/86400)))
510 )
511 and ((sysdate between condu.start_date and nvl(condu.end_date - (1/86400),sysdate))
512 or
513 (sysdate < condu.start_date and condu.start_date < nvl(condu.end_date, condu.start_date + (1/86400)))
514 )
515 union
516 select distinct atr.attribute_id, atr.name
517 from ame_attributes atr
518 ,ame_action_usages acu
519 ,ame_actions act
520 ,ame_mandatory_attributes ama
521 ,ame_rules rules
522 where ama.attribute_id = atr.attribute_id
523 and act.action_id = acu.action_id
524 and act.action_type_id = ama.action_type_id
525 and acu.rule_id = rules.rule_id
526 and rules.rule_id = ruleIdIn
527 and ama.attribute_id not in (select attribute_id
528 from ame_attribute_usages
529 where application_id = applicationIdIn
530 and sysdate between start_date
531 and nvl(end_date - (1/86400), sysdate))
532 and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
533 and sysdate between ama.start_date and nvl(ama.end_date - (1/86400), sysdate)
534 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
535 and ((sysdate between rules.start_date and nvl(rules.end_date - (1/86400),sysdate))
536 or
537 (sysdate < rules.start_date and rules.start_date < nvl(rules.end_date, rules.start_date + (1/86400)))
538 )
539 and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
540 or
541 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
542 );
543
544 --+
545 cursor checkItemClass(ruleIdIn in integer
546 ,applicationIdIn in integer) is
547 select name
548 from ame_item_classes itc
549 ,ame_rules rul
550 where rul.item_class_id = itc.item_class_id
551 and rul.rule_id = ruleIdIn
552 and sysdate between itc.start_date and nvl(itc.end_date - (1/86400), sysdate)
553 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
554 or
555 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
556 )
557 and itc.item_class_id not in (select item_class_id
558 from ame_item_class_usages itu
559 where itu.application_id = applicationIdIn
560 and sysdate between itu.start_date
561 and nvl(itu.end_date - (1/86400), sysdate));
562 --+
563 cursor getApplicationName(applicationIdIn in integer)is
564 select application_name
565 from ame_calling_apps
566 where application_id = applicationIdIn
567 and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
568 --+
569 cursor getItemClass(ruleIdIn in integer) is
570 select 'Y'
571 from ame_rules rul
572 ,ame_item_classes itc
573 where rul.rule_id = ruleIdIn
574 and rul.item_class_id = itc.item_class_id
575 and itc.name <> ame_util.headerItemClassName
576 and sysdate between itc.start_date and nvl(itc.end_date - (1/86400), sysdate)
577 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
578 or
579 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
580 );
581 --+
582 cursor checkActionTypes(ruleIdIn in integer
583 ,applicationIdIn in integer) is
584 select distinct aty.action_type_id, aty.name
585 from ame_action_types aty
586 ,ame_actions act
587 ,ame_action_usages acu
588 ,ame_rules rul
589 where rul.rule_id = ruleIdIn
590 and rul.rule_id = acu.rule_id
591 and acu.action_id = act.action_id
592 and act.action_type_id = aty.action_type_id
593 and aty.action_type_id not in (select atf.action_type_id
594 from ame_action_type_config atf
595 where atf.application_id = applicationIdIn
596 and sysdate between atf.start_date
597 and nvl(atf.end_date - (1/86400), sysdate))
598 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
599 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
600 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
601 or
602 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
603 )
604 and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
605 or
606 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
607 );
608 --+
609 cursor checkApproverGroups(ruleIdIn in integer
610 ,applicationIdIn in integer) is
611 select apg.approval_group_id, apg.name
612 from ame_action_types aty
613 ,ame_actions act
614 ,ame_action_usages acu
615 ,ame_rules rul
616 ,ame_approval_groups apg
617 where rul.rule_id = ruleIdIn
618 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
619 or
620 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
621 )
622 and rul.rule_id = acu.rule_id
623 and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
624 or
625 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
626 )
627 and acu.action_id = act.action_id
628 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
629 and act.action_type_id = aty.action_type_id
630 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
631 and to_char(apg.approval_group_id) = act.parameter
632 and sysdate between apg.start_date and nvl(apg.end_date - (1/86400), sysdate)
633 and not exists (select null
634 from ame_approval_group_config agf
635 where agf.application_id = applicationIdIn
636 and agf.approval_group_id = apg.approval_group_id
637 and sysdate between agf.start_date and nvl(agf.end_date - (1/86400), sysdate))
638 and aty.name in ('pre-chain-of-authority approvals'
639 ,'post-chain-of-authority approvals'
640 ,'approval-group chain of authority');
641 --+
642 cursor checkLMConditions(ruleIdIn in integer) is
643 select 'Y'
644 from ame_rules rul
645 ,ame_conditions con
646 ,ame_condition_usages cnu
647 ,wf_roles wf
648 where rul.rule_id = ruleIdIn
649 and con.condition_id = cnu.condition_id
650 and cnu.rule_id = rul.rule_id
651 and con.parameter_two = wf.name
652 and wf.orig_system = 'POS'
653 and wf.status = 'ACTIVE'
654 and sysdate between con.start_date and nvl(con.end_date - (1/86400), sysdate)
655 and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
656 or
657 (sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
658 )
659 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
660 or
661 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
662 );
663 --+
664 dummy varchar2(1);
665 errorExists boolean;
666 attributeIdList ame_util.idList;
667 attributeNamesList ame_util.stringList;
668 itemClassName ame_item_classes.name%type;
669 applicationName ame_calling_apps.application_name%type;
670 tempValue ame_config_vars.variable_value%type;
671 actionTypeIdList ame_util.idList;
672 actionTypeNameList ame_util.stringList;
673 endDate date;
674 --+
675 begin
676 errorExists := false;
677 open checkRuleCursor(ruleIdIn => ruleIdIn
678 ,applicationIdIn => applicationIdIn);
679 fetch checkRuleCursor into dummy;
680 if(checkRuleCursor%found) then
681 close checkRuleCursor;
682 resultOut := 'Y';
683 return;
684 end if;
685 close checkRuleCursor;
686 --+
687 endDate := null;
688 if(endDateIn is not null) then
689 endDate := to_date(endDateIn,'YYYY:MM:DD:HH24:MI:SS');
690 end if;
691 if (endDate is not null and endDate < sysdate) then
692 checkRuleForUsage2(ruleIdIn => ruleIdIn
693 ,applicationIdIn => applicationIdIn
694 ,endDateIn => endDate
695 ,resultOut => resultOut);
696 return;
697 end if;
698 --+
699 resultOut := 'Y';
700 hr_multi_message.enable_message_list;
701 --+
702 --+ get application name
703 --+
704 open getApplicationName(applicationIdIn => applicationIdIn);
705 fetch getApplicationName into applicationName;
706 close getApplicationName;
707 --+
708 --+
709 --+
710 tempValue := ame_util.getConfigVar
711 (variableNameIn => ame_util.allowAllICRulesConfigVar
712 ,applicationIdIn => applicationIdIn);
713 if(tempValue = ame_util.no) then
714 open getItemClass(ruleIdIn => ruleIdIn);
715 fetch getItemClass into dummy;
716 if(getItemClass%found) then
717 fnd_message.set_name('PER','AME_400633_SUBITC_TTY_NO_USAGE');
718 fnd_message.set_token('TXTYPENAME',applicationName);
719 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
720 close getItemClass;
721 resultOut := 'N';
722 fnd_message.raise_error;
723 return;
724 end if;
725 close getItemClass;
726 end if;
727 --+
728 --+ check LM Conditions
729 --+
730 tempValue := ame_util.getConfigVar
731 (variableNameIn => ame_util.allowAllApproverTypesConfigVar
732 ,applicationIdIn => applicationIdIn);
733 if(tempValue = ame_util.no) then
734 open checkLMConditions(ruleIdIn => ruleIdIn);
735 fetch checkLMConditions into dummy;
736 if(checkLMConditions%found) then
737 fnd_message.set_name('PER','AME_400641_TTY_INV_APPR_TYPE');
738 fnd_message.set_token('TXTYPENAME',applicationName);
739 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
740 close getItemClass;
741 resultOut := 'N';
742 fnd_message.raise_error;
743 return;
744 end if;
745 close checkLMConditions;
746 end if;
747 --+
748 --+ check prodution functionality
749 --+
750 tempValue := ame_util.getConfigVar
751 (variableNameIn => ame_util.productionConfigVar
752 ,applicationIdIn => applicationIdIn);
753 if(tempValue <> ame_util.allProductions) then
754 if(tempValue <> ame_util.perTransactionProductions and isProdRule(ruleIdIn)) then
755 fnd_message.set_name('PER','AME_400639_TTY_NO_PROD_RULES');
756 fnd_message.set_token('TXTYPENAME',applicationName);
757 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
758 resultOut := 'N';
759 fnd_message.raise_error;
760 return;
761 elsif(tempValue <> ame_util.perApproverProductions and hasProductionActions(ruleIdIn,endDate) and not isProdRule(ruleIdIn)) then
762 fnd_message.set_name('PER','AME_400640_TTY_NO_PROD_ACTIONS');
763 fnd_message.set_token('TXTYPENAME',applicationName);
764 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
765 resultOut := 'N';
766 fnd_message.raise_error;
767 return;
768 end if;
769
770 end if;
771 --+
772 --+ check item_class
773 --+
774 open checkItemClass(ruleIdIn => ruleIdIn
775 ,applicationIdIn => applicationIdIn);
776 fetch checkItemClass into itemClassName;
777 if(checkItemClass%found)then
778 fnd_message.set_name('PER','AME_400632_ITC_TTY_NO_USAGE');
779 fnd_message.set_token('ITEM_CLASS',itemClassName);
780 fnd_message.set_token('TXTYPENAME',applicationName);
781 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
782 errorExists := true;
783 end if;
784 close checkItemClass;
785 --+
786 --+ check for attributes
787 --+
788 open checkAttributeUsagesCursor(ruleIdIn => ruleIdIn
789 ,applicationIdIn => applicationIdIn);
790 fetch checkAttributeUsagesCursor
791 bulk collect into attributeIdList
792 ,attributeNamesList;
793 close checkAttributeUsagesCursor;
794 if attributeIdList.count > 0 then
795 errorExists := true;
796 end if;
797 for i in 1 .. attributeIdList.count loop
798 fnd_message.set_name('PER','AME_400149_ATT_TTY_NO_USAGE');
799 fnd_message.set_token('ATTRIBUTE',attributeNamesList(i));
800 fnd_message.set_token('APPLICATION',applicationName);
801 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
802 end loop;
803 --+
804 --+ check for action types
805 --+
806 open checkActionTypes(ruleIdIn => ruleIdIn
807 ,applicationIdIn => applicationIdIn);
808 fetch checkActionTypes
809 bulk collect into actionTypeIdList
810 ,actionTypeNameList;
811 close checkActionTypes;
812 if actionTypeIdList.count > 0 then
813 errorExists := true;
814 end if;
815 for i in 1 .. actionTypeIdList.count loop
816 fnd_message.set_name('PER','AME_400634_ATY_TTY_NO_USAGE');
817 fnd_message.set_token('ACTION_TYPE',actionTypeNameList(i));
818 fnd_message.set_token('TXTYPENAME',applicationName);
819 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
820 end loop;
821 --+
822 --+ check for approval groups
823 --+
824 actionTypeIdList.delete;
825 open checkApproverGroups(ruleIdIn => ruleIdIn
826 ,applicationIdIn => applicationIdIn);
827 fetch checkApproverGroups
828 bulk collect into actionTypeIdList
829 ,actionTypeNameList;
830 close checkApproverGroups;
831 if actionTypeIdList.count > 0 then
832 errorExists := true;
833 end if;
834 for i in 1 .. actionTypeIdList.count loop
835 fnd_message.set_name('PER','AME_400643_APG_TTY_NO_USAGE');
836 fnd_message.set_token('GROUP',actionTypeNameList(i));
837 fnd_message.set_token('TXTYPENAME',applicationName);
838 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
839 end loop;
840 --+
841 if errorExists then
842 resultOut := 'N';
843 fnd_message.raise_error;
844 end if;
845 exception
846 when others then
847 null;
848 end checkRuleForUsage;
849 --+
850 --+
851 --+
852 function isRuleReenabled(ruleIdIn in integer
853 ,applicationIdIn in integer
854 ,endDateIn in varchar2) return integer is
855 --+
856 cursor checkConditions(ruleIdIn in integer
857 ,endDateIn in date
858 ) is
859 select count(*)
860 from ame_conditions con
861 ,ame_condition_usages cnu
862 ,ame_rules rul
863 where rul.rule_id = ruleIdIn
864 and rul.end_date = endDateIn
865 and cnu.rule_id = rul.rule_id
866 and cnu.end_date = rul.end_date
867 and con.condition_id = cnu.condition_id
868 and sysdate between con.start_date and nvl(con.end_date - (1/86400), sysdate);
869 --+
870 cursor getActions(ruleIdIn in integer
871 ,endDateIn in date) is
872 select count(*)
873 from ame_action_usages acu
874 ,ame_rules rul
875 where rul.rule_id = ruleIdIn
876 and rul.end_date = endDateIn
877 and acu.rule_id = rul.rule_id
878 and acu.end_date = rul.end_date;
879 --+
880 cursor checkActions(ruleIdIn in integer
881 ,endDateIn in date
882 ) is
883 select count(*)
884 from ame_actions act
885 ,ame_action_usages acu
886 ,ame_rules rul
887 where rul.rule_id = ruleIdIn
888 and rul.end_date = endDateIn
889 and acu.rule_id = rul.rule_id
890 and acu.end_date = rul.end_date
891 and act.action_id = acu.action_id
892 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate);
893 --+
894 cursor checkActionTypes(ruleIdIn in integer
895 ,endDateIn in date) is
896 select 'Y'
897 from ame_actions act
898 ,ame_action_usages acu
899 ,ame_rules rul
900 ,ame_action_types aty
901 where rul.rule_id = ruleIdIn
902 and rul.end_date = endDateIn
903 and acu.rule_id = rul.rule_id
904 and acu.end_date = rul.end_date
905 and act.action_id = acu.action_id
906 and act.action_type_id = aty.action_type_id
907 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate);
908 --+
909 cursor getConditions(ruleIdIn in integer
910 ,endDateIn in date) is
911 select count(*)
912 from ame_condition_usages cnu
913 ,ame_rules rul
914 where rul.rule_id = ruleIdIn
915 and rul.end_date = endDateIn
916 and cnu.rule_id = rul.rule_id
917 and cnu.end_date = rul.end_date;
918 --+
919 endDate date;
920 dummy varchar2(1);
921 tempBoolean boolean;
922 checkActionCount number:=null;
923 getActionCount number:=null;
924 getConditionCount number:=null;
925 checkConditionCount number:=null;
926 begin
927 endDate := to_date(endDateIn,'YYYY:MM:DD:HH24:MI:SS');
928 tempBoolean := false;
929 if endDate > sysdate then
930 return 0;
931 end if;
932 --+
933 if(isDescriptionExists (ruleIdIn => ruleIdIn
934 ,endDateIn => endDate))then
935 return 1;
936 end if;
937 --+
938 open getConditions(ruleIdIn => ruleIdIn
939 ,endDateIn => endDate);
940 fetch getConditions into getConditionCount;
941 close getConditions;
942 --+
943 if getConditionCount > 0 then
944 open checkConditions(ruleIdIn => ruleIdIn
945 ,endDateIn => endDate
946 );
947 fetch checkConditions into checkConditionCount;
948 close checkConditions;
949 if checkConditionCount <> getConditionCount then
950 return 1;
951 end if;
952 end if;
953 --+
954 open getActions(ruleIdIn => ruleIdIn
955 ,endDateIn => endDate);
956 fetch getActions into getActionCount;
957 close getActions;
958
959 if getActionCount > 0 then
960 open checkActions(ruleIdIn => ruleIdIn
961 ,endDateIn => endDate
962 );
963 fetch checkActions into checkActionCount;
964 close checkActions;
965 if getActionCount <> checkActionCount then
966 return 1;
967 end if;
968 end if;
969 --+
970 open checkActionTypes(ruleIdIn => ruleIdIn
971 ,endDateIn => endDate);
972 fetch checkActionTypes into dummy;
973 if(checkActionTypes%notfound)then
974 tempBoolean := true;
975 end if;
976 close checkActionTypes;
977 if tempBoolean then
978 return 1;
979 end if;
980 --+
981 return 0;
982 exception
983 when others then
984 return 1;
985 end isRuleReenabled;
986 --+
987 --+
988 --+
989 procedure enableRule(ruleIdIn in integer
990 ,ruleEndDateIn in date
991 ,startDateIn in date
992 ,endDateIn in date
993 ,resultOut out nocopy varchar2) is
994 --+
995 cursor getConditions(ruleIdIn in integer
996 ,endDateIn in date)is
997 select condition_id,created_by,creation_date
998 from ame_condition_usages
999 where rule_id = ruleIdIn
1000 and end_date = endDateIn;
1001 --+
1002 cursor getACtions(ruleIdIn in integer
1003 ,endDateIn in date)is
1004 select action_id,created_by,creation_date
1005 from ame_action_usages
1006 where rule_id = ruleIdIn
1007 and end_date = endDateIn;
1008 --+
1009 tempOVN integer;
1010 endDate date;
1011 startDate date;
1012 begin
1013
1014 startDate := startDateIn;
1015 endDate := endDateIn;
1016 --+ rule
1017 insert into ame_rules
1018 (rule_id
1019 ,rule_type
1020 ,created_by
1021 ,creation_date
1022 ,last_updated_by
1023 ,last_update_date
1024 ,last_update_login
1025 ,start_date
1026 ,end_date
1027 ,description
1028 ,rule_key
1029 ,item_class_id
1030 ,object_version_number)
1031 select rule_id
1032 ,rule_type
1033 ,created_by
1034 ,creation_date
1035 ,fnd_global.user_id
1036 ,sysdate
1037 ,fnd_global.user_id
1038 ,startDate
1039 ,endDate
1040 ,description
1041 ,rule_key
1042 ,item_class_id
1043 ,object_version_number+1 from ame_rules
1044 where rule_id = ruleIdIn
1045 and end_date = ruleEndDateIn;
1046 --+ conditions
1047 for condRec in getConditions(ruleIdIn,ruleEndDateIn) loop
1048 insert into ame_condition_usages
1049 (rule_id
1050 ,condition_id
1051 ,created_by
1052 ,creation_date
1053 ,last_updated_by
1054 ,last_update_date
1055 ,last_update_login
1056 ,start_date
1057 ,end_date
1058 ,object_version_number)
1059 values(ruleIdIn
1060 ,condRec.condition_id
1061 ,condRec.created_by
1062 ,condRec.creation_date
1063 ,fnd_global.user_id
1064 ,sysdate
1065 ,fnd_global.user_id
1066 ,startDate
1067 ,endDate
1068 ,1);
1069 end loop;
1070 --+ actions
1071 for actionRec in getActions(ruleIdIn,ruleEndDateIn) loop
1072 insert into ame_action_usages
1073 (rule_id
1074 ,action_id
1075 ,created_by
1076 ,creation_date
1077 ,last_updated_by
1078 ,last_update_date
1079 ,last_update_login
1080 ,start_date
1081 ,end_date
1082 ,object_version_number)
1083 values(ruleIdIn
1084 ,actionRec.action_id
1085 ,actionRec.created_by
1086 ,actionRec.creation_date
1087 ,fnd_global.user_id
1088 ,sysdate
1089 ,fnd_global.user_id
1090 ,startDate
1091 ,endDate
1092 ,1);
1093 end loop;
1094 --+
1095 resultOut := 'Y';
1096 end enableRule;
1097 --+
1098 --+
1099 --+
1100 procedure checkAllApplications(ruleIdIn in integer
1101 ,conditionIdIn in integer) is
1102 --+
1103 cursor getApplications is
1104 select aca.application_id, aca.application_name
1105 from ame_rule_usages rlu
1106 ,ame_calling_apps aca
1107 where rlu.rule_id = ruleIdIn
1108 and aca.application_id = rlu.item_id
1109 and sysdate between aca.start_date and nvl(aca.end_date - (1/86400), sysdate)
1110 and (sysdate between rlu.start_date
1111 and nvl(rlu.end_date - ame_util.oneSecond, sysdate) or
1112 (sysdate < rlu.start_date
1113 and rlu.start_date < nvl(rlu.end_date, rlu.start_date + ame_util.oneSecond)));
1114 --+
1115 cursor getInvalidAttributes(applicationIdIn in integer
1116 ,conditionIdIn in integer) is
1117 select atr.attribute_id, atr.name
1118 from ame_attributes atr
1119 ,ame_conditions con
1120 where atr.attribute_id = con.attribute_id
1121 and con.condition_id = conditionIdIn
1122 and con.condition_type <> ame_util.listModConditionType
1123 and sysdate between atr.start_date and nvl(atr.end_date - (1/86400), sysdate)
1124 and sysdate between con.start_date and nvl(con.end_date - (1/86400), sysdate)
1125 and not exists (select attribute_id
1126 from ame_attribute_usages atu
1127 where atu.application_id = applicationIdIn
1128 and atu.attribute_id = atr.attribute_id
1129 and sysdate between atu.start_date
1130 and nvl(atu.end_date - (1/86400), sysdate));
1131 --+
1132 cursor checkLMCondition(conditionIdIn in integer) is
1133 select 'Y'
1134 from ame_conditions con
1135 ,wf_roles wf
1136 where con.condition_id = conditionIdIn
1137 and con.condition_type = ame_util.listModConditionType
1138 and con.parameter_two = wf.name
1139 and wf.orig_system = 'POS'
1140 and wf.status = 'ACTIVE'
1141 and sysdate between con.start_date and nvl(con.end_date - (1/86400), sysdate);
1142 --+
1143 attributeIdList ame_util.idList;
1144 attributeNameList ame_util.stringList;
1145 tempValue ame_config_vars.variable_value%type;
1146 dummy varchar2(1);
1147 errorExists boolean;
1148 begin
1149 --+
1150 errorExists := false;
1151 for rec in getApplications loop
1152 attributeIdList.delete;
1153 attributeNameList.delete;
1154 open getInvalidAttributes(applicationIdIn => rec.application_id
1155 ,conditionIdIn => conditionIdIn);
1156 fetch getInvalidAttributes bulk collect into attributeIdList, attributeNameList;
1157 for i in 1 .. attributeIdList.count loop
1158 fnd_message.set_name('PER','AME_400149_ATT_TTY_NO_USAGE');
1159 fnd_message.set_token('ATTRIBUTE',attributeNameList(i));
1160 fnd_message.set_token('APPLICATION',rec.application_name);
1161 hr_multi_message.add (p_associated_column1 => 'CONDITION_ID');
1162 errorExists := true;
1163 end loop;
1164 close getInvalidAttributes;
1165 end loop;
1166 --+
1167 for rec in getApplications loop
1168 tempValue := ame_util.getConfigVar
1169 (variableNameIn => ame_util.allowAllApproverTypesConfigVar
1170 ,applicationIdIn => rec.application_id);
1171 if(tempValue = ame_util.no) then
1172 open checkLMCondition(conditionIdIn => conditionIdIn);
1173 fetch checkLMCondition into dummy;
1174 if(checkLMCondition%found) then
1175 fnd_message.set_name('PER','AME_400641_TTY_INV_APPR_TYPE');
1176 fnd_message.set_token('TXTYPENAME',rec.application_name);
1177 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
1178 end if;
1179 close checkLMCondition;
1180 end if;
1181 end loop;
1182 --+
1183 end checkAllApplications;
1184 --+
1185 --+
1186 --+
1187 procedure chekActionForAllApplications(ruleIdIn in integer
1188 ,actionIdIn in integer) is
1189 --+
1190 cursor getApplications is
1191 select aca.application_id, aca.application_name
1192 from ame_rule_usages rlu
1193 ,ame_calling_apps aca
1194 where rlu.rule_id = ruleIdIn
1195 and aca.application_id = rlu.item_id
1196 and sysdate between aca.start_date and nvl(aca.end_date - (1/86400), sysdate)
1197 and (sysdate between rlu.start_date
1198 and nvl(rlu.end_date - ame_util.oneSecond, sysdate) or
1199 (sysdate < rlu.start_date
1200 and rlu.start_date < nvl(rlu.end_date, rlu.start_date + ame_util.oneSecond)));
1201 --+
1202 cursor getInvalidActionTypes(applicationIdIn in integer
1203 ,actionIdIn in integer) is
1204 select aty.action_type_id, aty.name
1205 from ame_action_types aty
1206 ,ame_actions act
1207 where act.action_id = actionIdIn
1208 and act.action_type_id = aty.action_type_id
1209 and aty.action_type_id not in (select atf.action_type_id
1210 from ame_action_type_config atf
1211 where atf.application_id = applicationIdIn
1212 and sysdate between atf.start_date
1213 and nvl(atf.end_date - (1/86400), sysdate))
1214 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
1215 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate);
1216 --+
1217 cursor getInvalidRequiredAttributes(applicationIdIn in integer
1218 ,actionIdIn in integer) is
1219 select distinct atr.attribute_id, atr.name
1220 from ame_attributes atr
1221 ,ame_mandatory_attributes man
1222 ,ame_actions act
1223 where act.action_id = actionIdIn
1224 and act.action_type_id = man.action_type_id
1225 and atr.attribute_id = man.attribute_id
1226 and atr.attribute_id not in (select attribute_id
1227 from ame_attribute_usages atu
1228 where atu.application_id = applicationIdIn
1229 and atu.attribute_id = atr.attribute_id
1230 and sysdate between atu.start_date
1231 and nvl(atu.end_date - (1/86400), sysdate))
1232 and sysdate between man.start_date and nvl(man.end_date - (1/86400), sysdate)
1233 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate);
1234 --+
1235 cursor getInvalidGroups(applicationIdIn in integer
1236 ,actionIdIn in integer) is
1237 select apg.approval_group_id, apg.name
1238 from ame_action_types aty
1239 ,ame_actions act
1240 ,ame_approval_groups apg
1241 where act.action_id = actionIdIn
1242 and act.action_type_id = aty.action_type_id
1243 and act.parameter = to_char(apg.approval_group_id)
1244 and apg.approval_group_id not in (select agf.approval_group_id
1245 from ame_approval_group_config agf
1246 where agf.application_id = applicationIdIn
1247 and sysdate between agf.start_date
1248 and nvl(agf.end_date - (1/86400), sysdate))
1249 and aty.name in (ame_util.preApprovalTypeName
1250 ,ame_util.postApprovalTypeName
1251 ,ame_util.groupChainApprovalTypeName)
1252 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400), sysdate)
1253 and sysdate between act.start_date and nvl(act.end_date - (1/86400), sysdate)
1254 and sysdate between apg.start_date and nvl(apg.end_date - (1/86400), sysdate);
1255 --+
1256 actionTypeIdList ame_util.idList;
1257 actionTypeNameList ame_util.stringList;
1258 attributeIdList ame_util.idList;
1259 attributeNameList ame_util.stringList;
1260 tempValue ame_config_vars.variable_value%type;
1261 errorExists boolean;
1262 begin
1263 errorExists := false;
1264 --+
1265 for rec in getApplications loop
1266 actionTypeIdList.delete;
1267 actionTypeNameList.delete;
1268 --+
1269 open getInvalidActionTypes(applicationIdIn => rec.application_id
1270 ,actionIdIn => actionIdIn);
1271 fetch getInvalidActionTypes bulk collect into actionTypeIdList, actionTypeNameList;
1272 for i in 1 .. actionTypeIdList.count loop
1273 fnd_message.set_name('PER','AME_400634_ATY_TTY_NO_USAGE');
1274 fnd_message.set_token('ACTION_TYPE',actionTypeNameList(i));
1275 fnd_message.set_token('TXTYPENAME',rec.application_name);
1276 hr_multi_message.add (p_associated_column1 => 'CONDITION_ID');
1277 errorExists := true;
1278 end loop;
1279 close getInvalidActionTypes;
1280 end loop;
1281 if errorExists then
1282 return;
1283 end if;
1284 --+
1285 for rec in getApplications loop
1286 attributeIdList.delete;
1287 attributeNameList.delete;
1288 open getInvalidRequiredAttributes(applicationIdIn => rec.application_id
1289 ,actionIdIn => actionIdIn);
1290 fetch getInvalidRequiredAttributes bulk collect into attributeIdList, attributeNameList;
1291 for i in 1 .. attributeIdList.count loop
1292 fnd_message.set_name('PER','AME_400149_ATT_TTY_NO_USAGE');
1293 fnd_message.set_token('ATTRIBUTE',attributeNameList(i));
1294 fnd_message.set_token('APPLICATION',rec.application_name);
1295 hr_multi_message.add (p_associated_column1 => 'ACTION_ID');
1296 errorExists := true;
1297 end loop;
1298 close getInvalidRequiredAttributes;
1299 end loop;
1300 --+
1301 if errorExists then
1302 return;
1303 end if;
1304 --+
1305 for rec in getApplications loop
1306 tempValue := ame_util.getConfigVar
1307 (variableNameIn => ame_util.productionConfigVar
1308 ,applicationIdIn => rec.application_id);
1309 if(tempValue = ame_util.noProductions and isProductionAction(actionIdIn)) then
1310 fnd_message.set_name('PER','AME_400640_TTY_NO_PROD_ACTIONS');
1311 fnd_message.set_token('TXTYPENAME',rec.application_name);
1312 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
1313 end if;
1314 end loop;
1315 --+
1316 for rec in getApplications loop
1317 actionTypeIdList.delete;
1318 actionTypeNameList.delete;
1319 --+
1320 open getInvalidGroups(applicationIdIn => rec.application_id
1321 ,actionIdIn => actionIdIn);
1322 fetch getInvalidGroups bulk collect into actionTypeIdList, actionTypeNameList;
1323 for i in 1 .. actionTypeIdList.count loop
1324 fnd_message.set_name('PER','AME_400643_APG_TTY_NO_USAGE');
1325 fnd_message.set_token('GROUP',actionTypeNameList(i));
1326 fnd_message.set_token('TXTYPENAME',rec.application_name);
1327 hr_multi_message.add (p_associated_column1 => 'CONDITION_ID');
1328 errorExists := true;
1329 end loop;
1330 close getInvalidGroups;
1331 end loop;
1332 --+
1333 end chekActionForAllApplications;
1334 --+
1335
1336 procedure fetchNewRuleDates2(p_rule_id in number
1337 ,p_rul_start_date out nocopy date
1338 ,p_rul_end_date out nocopy date) as
1339 begin
1340 select min(start_date)
1341 into p_rul_start_date
1342 from ame_rule_usages
1343 where rule_id = p_rule_id
1344 and (sysdate between start_date
1345 and nvl(end_date - ame_util.oneSecond, sysdate )
1346 or
1347 (sysdate < start_date
1348 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
1349 select max(end_date)
1350 into p_rul_end_date
1351 from ame_rule_usages
1352 where rule_id = p_rule_id
1353 and (sysdate between start_date
1354 and nvl(end_date - ame_util.oneSecond, sysdate )
1355 or
1356 (sysdate < start_date
1357 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
1358 end fetchNewRuleDates2;
1359
1360 --+
1361 procedure syncRuleObjects(p_rule_id in number
1362 ,p_effective_date in date default null) is
1363
1364 l_rul_start_date date;
1365 l_rul_end_date date;
1366 l_rul_start_date2 date;
1367 l_rul_end_date2 date;
1368 l_effective_date date;
1369 l_rul_object_version_number number;
1370 l_acu_object_version_number number;
1371 l_acu_start_date date;
1372 l_acu_end_date date;
1373 l_cnu_object_version_number number;
1374 l_cnu_start_date date;
1375 l_cnu_end_date date;
1376 l_update_rule boolean;
1377 --+
1378 cursor getActions(l_effective_date in date) is
1379 select action_id
1380 ,start_date
1381 ,end_date
1382 ,object_version_number
1383 from ame_action_usages
1384 where rule_id = p_rule_id
1385 and ((l_effective_date between start_date
1386 and nvl(end_date - ame_util.oneSecond, l_effective_date))
1387 or
1388 (l_effective_date < start_date
1389 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
1390 --+
1391 cursor getConditions(l_effective_date in date) is
1392 select condition_id
1393 ,start_date
1394 ,end_date
1395 ,object_version_number
1396 from ame_condition_usages
1397 where rule_id = p_rule_id
1398 and ((l_effective_date between start_date
1399 and nvl(end_date - ame_util.oneSecond, l_effective_date))
1400 or
1401 (l_effective_date < start_date
1402 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
1403 --+
1404
1405 cursor getRuleDetails(l_effective_date in date) is
1406 select start_date
1407 ,end_date
1408 ,object_version_number
1409 from ame_rules
1410 where rule_id = p_rule_id
1411 and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
1412 or
1413 (l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1414 --+
1415
1416 begin
1417
1418 if p_effective_date is not null and p_effective_date <= sysdate then
1419 l_effective_date := p_effective_date;
1420 else
1421 l_effective_date := sysdate;
1422 end if;
1423
1424 open getRuleDetails(l_effective_date);
1425 fetch getRuleDetails
1426 into l_rul_start_date
1427 ,l_rul_end_date
1428 ,l_rul_object_version_number;
1429 close getRuleDetails;
1430 --+
1431
1432 fetchNewRuleDates2(p_rule_id => p_rule_id
1433 ,p_rul_start_date => l_rul_start_date2
1434 ,p_rul_end_date => l_rul_end_date2);
1435 --+
1436 l_update_rule := false;
1437 if l_rul_start_date < l_effective_date then
1438 if l_rul_start_date2 > l_rul_start_date then
1439 l_update_rule := true;
1440 l_rul_start_date := l_rul_start_date2;
1441 end if;
1442 elsif l_rul_start_date = l_effective_date then
1443 null;
1444 else
1445 if l_rul_start_date2 <> l_rul_start_date then
1446 l_update_rule := true;
1447 l_rul_start_date := l_rul_start_date2;
1448 end if;
1449 end if;
1450 --+
1451 if l_rul_end_date2 <> l_rul_end_date then
1452 l_update_rule := true;
1453 l_rul_end_date := l_rul_end_date2;
1454 end if;
1455 --+
1456 if l_update_rule then
1457 --+
1458 for tempActions in getActions(l_effective_date) loop
1459 l_acu_object_version_number := tempActions.object_version_number;
1460 l_acu_start_date := tempActions.start_date;
1461 l_acu_end_date := tempActions.end_date;
1462 ame_acu_upd.upd(p_effective_date => l_effective_date
1463 ,p_datetrack_mode => hr_api.g_update
1464 ,p_rule_id => p_rule_id
1465 ,p_action_id => tempActions.action_id
1466 ,p_object_version_number => l_acu_object_version_number
1467 ,p_start_date => l_rul_start_date
1468 ,p_end_date => l_rul_end_date
1469 );
1470 end loop;
1471 -- condition usages
1472 for tempConditions in getConditions(l_effective_date) loop
1473 l_cnu_object_version_number := tempConditions.object_version_number;
1474 l_cnu_start_date := tempConditions.start_date;
1475 l_cnu_end_date := tempConditions.end_date;
1476 ame_cnu_upd.upd(p_effective_date => l_effective_date
1477 ,p_datetrack_mode => hr_api.g_update
1478 ,p_rule_id => p_rule_id
1479 ,p_condition_id => tempConditions.condition_id
1480 ,p_object_version_number => l_cnu_object_version_number
1481 ,p_start_date => l_rul_start_date
1482 ,p_end_date => l_rul_end_date
1483 );
1484 end loop;
1485 -- rule
1486 ame_rul_upd.upd(p_effective_date => l_effective_date
1487 ,p_datetrack_mode => hr_api.g_update
1488 ,p_rule_id => p_rule_id
1489 ,p_object_version_number => l_rul_object_version_number
1490 ,p_start_date => l_rul_start_date
1491 ,p_end_date => l_rul_end_date
1492 );
1493 --+
1494 end if;
1495 end syncRuleObjects;
1496 --+
1497
1498 procedure getAttributeName(p_attribute_id in number
1499 ,p_attribute_name_out out nocopy varchar2) is
1500 cursor getAtrName(p_attribute_id in number) is
1501 select name
1502 from ame_attributes
1503 where attribute_id = p_attribute_id
1504 and sysdate between start_date and nvl(end_date-(1/84600),sysdate);
1505 begin
1506 open getAtrName(p_attribute_id => p_attribute_id);
1507 fetch getAtrName
1508 into p_attribute_name_out;
1509 close getAtrName;
1510 exception
1511 when others then
1512 null;
1513 end getAttributeName;
1514 --+
1515 --+ validates the rule id.
1516 --+ Invoked from all the public callable api except create_ame_rule
1517 --+
1518 procedure checkRuleId(p_rule_id in number) is
1519 --+
1520 cursor checkRule(p_rule_id in number) is
1521 select count(*)
1522 from ame_rules
1523 where rule_id = p_rule_id
1524 and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
1525 or
1526 (sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
1527 );
1528 l_count number(3);
1529 --+
1530 begin
1531 open checkRule(p_rule_id);
1532 fetch checkRule
1533 into l_count;
1534 --+
1535 close checkRule;
1536 if l_count = 0 then
1537 fnd_message.set_name('PER','AME_400729_INV_RULE_ID');
1538 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
1539 end if;
1540 end checkRuleId;
1541 --+
1542 --+ validates the action id.
1543 --+ Invoked from create_ame_rule, create_ame_action_to_rule,
1544 --+ update_ame_rule_action and delete_ame_rule_action.
1545 --+
1546 procedure checkActionId(p_action_id in number) is
1547 --+
1548 cursor checkAction(p_action_id in number) is
1549 select count(*)
1550 from ame_actions
1551 where action_id = p_action_id
1552 and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
1553 l_count number(3);
1554 --+
1555 begin
1556 open checkAction(p_action_id);
1557 fetch checkAction
1558 into l_count;
1559 close checkAction;
1560 if l_count = 0 then
1561 fnd_message.set_name('PER','AME_400736_INV_ACTION_ID');
1562 hr_multi_message.add (p_associated_column1 => 'ACTION_ID');
1563 end if;
1564 end checkActionId;
1565 --+
1566 --+ validates the condition id.
1567 --+ Invoked from create_ame_rule, create_ame_condition_to_rule,
1568 --+ update_ame_rule_condition and delete_ame_rule_condition.
1569 --+
1570 procedure checkConditionId(p_condition_id in number) is
1571 --+
1572 cursor checkCondition(p_condition_id in number) is
1573 select count(*)
1574 from ame_conditions
1575 where condition_id = p_condition_id
1576 and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
1577 l_count number(3);
1578 --+
1579 begin
1580 open checkCondition(p_condition_id);
1581 fetch checkCondition
1582 into l_count;
1583 close checkCondition;
1584 if l_count = 0 then
1585 fnd_message.set_name('PER','AME_400737_INV_COND_ID');
1586 hr_multi_message.add (p_associated_column1 => 'CONDITION_ID');
1587 end if;
1588 end checkConditionId;
1589 --+
1590 --+ Validates the Application Id
1591 --+ Invoked from create_ame_rule, create_ame_rule_usage,
1592 --+ update_ame_rule_usage and delete_ame_rule_usage.
1593 --+
1594 procedure checkApplicationId(p_application_id in number)is
1595 --+
1596 cursor checkApplication(p_application_id in number) is
1597 select count(*)
1598 from ame_calling_apps
1599 where application_id = p_application_id
1600 and sysdate between start_date and nvl(end_date-(1/84600),sysdate);
1601 l_count number(3);
1602 --+
1603 begin
1604 open checkApplication(p_application_id);
1605 fetch checkApplication
1606 into l_count;
1607 close checkApplication;
1608 if l_count = 0 then
1609 fnd_message.set_name('PER','AME_400732_INV_APPLICATION_ID');
1610 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
1611 end if;
1612 end checkApplicationId;
1613 --+
1614 --+ Invoked from create_ame_rule
1615 --+ returns the no. of conditions attached to the rule
1616 --+
1617 function rule_conditions_count(p_rule_id in integer) return integer is
1618 --+
1619 cursor rulCndCnt(p_rule_id in number) is
1620 select count(*)
1621 from ame_rules rul
1622 ,ame_condition_usages cnu
1623 where rul.rule_id = p_rule_id
1624 and cnu.rule_id = rul.rule_id
1625 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
1626 or
1627 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
1628 )
1629 and sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate);
1630 l_count number(3);
1631 --+
1632 begin
1633 open rulCndCnt(p_rule_id);
1634 fetch rulCndCnt
1635 into l_count;
1636 close rulCndCnt;
1637 return l_count;
1638 end rule_conditions_count;
1639 --+
1640 --+ Invoked from create_ame_rule and is_rule_usage_allowed.
1641 --+ Validates if this action's action type has a config in this application.
1642 --+
1643 function is_action_allowed(p_application_id in integer
1644 ,p_action_id in integer) return number is
1645 --+ get All non-group actions.
1646 cursor getActions(p_application_id in integer
1647 ,p_action_id in integer) is
1648 select count(act.action_id)
1649 from ame_actions act
1650 ,ame_action_type_config atf
1651 ,ame_action_types aty
1652 where act.action_id = p_action_id
1653 and atf.application_id = p_application_id
1654 and act.action_type_id = atf.action_type_id
1655 and act.action_type_id = aty.action_type_id
1656 and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
1657 and sysdate between atf.start_date and nvl(atf.end_date - (1/86400),sysdate)
1658 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
1659 and aty.name not in ('approval-group chain of authority'
1660 ,'pre-chain-of-authority approvals'
1661 ,'post-chain-of-authority approvals');
1662 --+ get all position actions.
1663 cursor getPosActions(p_application_id in integer
1664 ,p_action_id in integer) is
1665 select count(*)
1666 from ame_actions act
1667 ,ame_action_type_config atf
1668 ,ame_action_types aty
1669 where act.action_id = p_action_id
1670 and atf.application_id = p_application_id
1671 and act.action_type_id = atf.action_type_id
1672 and act.action_type_id = aty.action_type_id
1673 and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
1674 and sysdate between atf.start_date and nvl(atf.end_date - (1/86400),sysdate)
1675 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
1676 and aty.name in ('hr position'
1677 ,'hr position level');
1678 --+ get all group actions.
1679 cursor getGroupActions(p_application_id in integer
1680 ,p_action_id in integer) is
1681 select act.parameter
1682 from ame_actions act
1683 ,ame_action_type_config atf
1684 ,ame_action_types aty
1685 where act.action_id = p_action_id
1686 and atf.application_id = p_application_id
1687 and act.action_type_id = atf.action_type_id
1688 and aty.action_type_id = act.action_type_id
1689 and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
1690 and sysdate between atf.start_date and nvl(atf.end_date - (1/86400),sysdate)
1691 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
1692 and aty.name in ('approval-group chain of authority'
1693 ,'pre-chain-of-authority approvals'
1694 ,'post-chain-of-authority approvals');
1695 --+
1696 l_group_param ame_actions.parameter%type;
1697 l_count number(3);
1698 l_pos_count number(3);
1699 --+
1700 begin
1701 --+
1702 open getPosActions(p_application_id => p_application_id
1703 ,p_action_id => p_action_id);
1704 fetch getPosActions
1705 into l_pos_count;
1706 close getPosActions;
1707 --+
1708 if l_pos_count > 0 then
1709 --+
1710 if is_all_approver_types_allowed(p_application_id => p_application_id) then
1711 return NoErrors;
1712 else
1713 return PosActionNotAllowed;
1714 end if;
1715 --+
1716 end if;
1717 --+
1718 open getGroupActions(p_application_id => p_application_id
1719 ,p_action_id => p_action_id);
1720 fetch getGroupActions
1721 into l_group_param;
1722 --+
1723 if getGroupActions%NOTFOUND then
1724 --+
1725 open getActions(p_application_id => p_application_id
1726 ,p_action_id => p_action_id);
1727 fetch getActions
1728 into l_count;
1729 close getActions;
1730 --+
1731 if l_count = 0 then
1732 return ActionNotAllowed;
1733 else
1734 return NoErrors;
1735 end if;
1736 --+
1737 else
1738 --+
1739 if not is_group_allowed(p_application_id => p_application_id
1740 ,p_approval_group_id => l_group_param) then
1741 return GroupNotAllowed;
1742 else
1743 return NoErrors;
1744 end if;
1745 --+
1746 end if;
1747 --+
1748 close getGroupActions;
1749 end is_action_allowed;
1750 --+
1751 --+ Invoked from create_ame_action_to_rule and create_ame_rule
1752 --+ checks if all the actions for this rule have config
1753 --+ in this transaction type.
1754 --+
1755 function is_rule_usage_allowed(p_application_id in integer
1756 ,p_rule_id in integer) return number is
1757 --+
1758 cursor getRuleActions(p_rule_id in integer) is
1759 select acu.action_id
1760 from ame_action_usages acu
1761 ,ame_rules rul
1762 where rul.rule_id = acu.rule_id
1763 and rul.rule_id = p_rule_id
1764 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
1765 or
1766 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
1767 )
1768 and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
1769 or
1770 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
1771 );
1772 --+
1773 actionIdList ame_util.idList;
1774 l_result number(2);
1775 begin
1776 --+
1777 open getRuleActions(p_rule_id => p_rule_id);
1778 fetch getRuleActions
1779 bulk collect into actionIdList;
1780 --+
1781 for i in 1..actionIdList.count loop
1782 --+
1783 l_result := is_action_allowed(p_application_id => p_application_id
1784 ,p_action_id => actionIdList(i));
1785 --+
1786 if l_result = ActionNotAllowed then
1787 close getRuleActions;
1788 return ActionNotAllowedInTTY;
1789 elsif l_result = GroupNotAllowed then
1790 close getRuleActions;
1791 return GroupNotAllowedInTTY;
1792 elsif l_result = PosActionNotAllowed then
1793 close getRuleActions;
1794 return PosActionNotAllowedInTTY;
1795 end if;
1796 --+
1797 end loop;
1798 --+
1799 close getRuleActions;
1800 return NoErrors;
1801 end is_rule_usage_allowed;
1802 --+
1803 --+ Invoked from create_ame_rule and create_ame_condition_to_rule.
1804 --+ Validates if the rule can be added to the transaction type.
1805 --+
1806 function is_rule_usage_cond_allowed(p_application_id in integer
1807 ,p_rule_id in integer) return boolean is
1808 --+
1809 cursor getRuleConditions(p_rule_id in integer) is
1810 select cnu.condition_id
1811 from ame_condition_usages cnu
1812 ,ame_rules rul
1813 where rul.rule_id = cnu.rule_id
1814 and rul.rule_id = p_rule_id
1815 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
1816 or
1817 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
1818 )
1819 and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
1820 or
1821 (sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
1822 );
1823 --+
1824 conditionIdList ame_util.idList;
1825 begin
1826 --+
1827 open getRuleConditions(p_rule_id => p_rule_id);
1828 fetch getRuleConditions
1829 bulk collect into conditionIdList;
1830 --+
1831 for i in 1..conditionIdList.count loop
1832 --+
1833 if not is_condition_allowed(p_application_id => p_application_id
1834 ,p_condition_id => conditionIdList(i)) then
1835 close getRuleConditions;
1836 return false;
1837 end if;
1838 --+
1839 end loop;
1840 --+
1841 close getRuleConditions;
1842 return true;
1843 end is_rule_usage_cond_allowed;
1844 --+
1845 --+ Validates the rule type and action combination.
1846 --+
1847 function chk_rule_type(p_rule_id in integer
1848 ,p_rule_type in integer
1849 ,p_action_rule_type in integer
1850 ,p_application_id in integer
1851 ,p_allow_production_action in boolean) return boolean is
1852 l_rule_type ame_rules.rule_type%type;
1853 --+
1854 begin
1855 --+
1856 l_rule_type := p_rule_type;
1857 --+
1858 if not p_allow_production_action then
1859 if p_rule_type <> 7 and p_action_rule_type = 7 then
1860 return false;
1861 end if;
1862 end if;
1863 --+
1864 if l_rule_type = 2 then
1865 l_rule_type := 1;
1866 end if;
1867 --+
1868 if P_action_rule_type = 7 then
1869 --+
1870 if p_allow_production_action then
1871 --+
1872 if (not is_prod_action_allowed(p_application_id))then
1873 return false;
1874 else
1875 return true;
1876 end if;
1877 --+
1878 end if;
1879 --+
1880 elsif l_rule_type <> 0 then
1881 --+
1882 if l_rule_type <> p_action_rule_type then
1883 return false;
1884 end if;
1885 --+
1886 else
1887 --+
1888 if is_LM_comb_rule(p_rule_id) then
1889 --+
1890 if p_action_rule_type = 3 or p_action_rule_type = 4 then
1891 return true;
1892 else
1893 return false;
1894 end if;
1895 --+
1896 else
1897 --+_
1898 if p_action_rule_type = 3 or p_action_rule_type = 4 then
1899 return false;
1900 else
1901 return true;
1902 end if;
1903 --+
1904 end if;
1905 --+
1906 end if;
1907 --+
1908 return true;
1909 end chk_rule_type;
1910 --+
1911 --+ Invoked from chkRuleType and create_ame_rule.
1912 --+
1913 function is_prod_action_allowed(p_application_id in integer) return boolean is
1914 temp ame_config_vars.variable_value%type;
1915 begin
1916 --+
1917 temp := ame_util.getConfigVar
1918 (variableNameIn => ame_util.productionConfigVar
1919 ,applicationIdIn => p_application_id);
1920 --+
1921 if temp = 'all' or temp = 'approver' then
1922 return true;
1923 else
1924 return false;
1925 end if;
1926 --+
1927 end is_prod_action_allowed;
1928 --+
1929 --+ Invoked from chkRuleType and create_ame_rule
1930 --+ Determines whether the given rule is Combination LM rule or not.
1931 --+
1932 function is_LM_comb_rule(p_rule_id in integer) return boolean is
1933 --+
1934 cursor getLMConditions(p_rule_id in integer) is
1935 select count(*)
1936 from ame_rules rul
1937 ,ame_condition_usages cnu
1938 ,ame_conditions cnd
1939 where rul.rule_id = p_rule_id
1940 and cnu.rule_id = rul.rule_id
1941 and cnd.condition_id = cnu.condition_id
1942 and cnd.condition_type = ame_util.listModConditionType
1943 and rul.rule_type = 0
1944 and sysdate between cnd.start_date and nvl(cnd.end_date - (1/86400),sysdate)
1945 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
1946 or
1947 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
1948 )
1949 and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
1950 or
1951 (sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
1952 );
1953 --+
1954 l_count number(5);
1955 begin
1956 --+
1957 open getLMConditions(p_rule_id => p_rule_id);
1958 fetch getLMConditions
1959 into l_count;
1960 close getLMConditions;
1961 --+
1962 if l_count = 0 then
1963 return false;
1964 else
1965 return true;
1966 end if;
1967 --+
1968 end is_LM_comb_rule;
1969 --+
1970 --+ Invoked from create_ame_rule and create_ame_rule_usage.
1971 --+
1972 function is_condition_allowed(p_application_id in integer
1973 ,p_condition_id in integer) return boolean is
1974 --+ Check if this application has a usage for the attribute on which the condition is based.
1975 cursor getConditions(p_application_id in integer
1976 ,p_condition_id in integer) is
1977 select count(*)
1978 from ame_conditions cnd
1979 ,ame_attribute_usages atu
1980 where cnd.condition_id = p_condition_id
1981 and cnd.condition_type <> ame_util.listModConditionType
1982 and atu.application_id = p_application_id
1983 and cnd.attribute_id = atu.attribute_id
1984 and sysdate between cnd.start_date and nvl(cnd.end_date - (1/86400),sysdate)
1985 and sysdate between atu.start_date and nvl(atu.end_date - (1/86400),sysdate);
1986 --+
1987 l_count number(3);
1988 lm_count number(3);
1989 lm_param2 ame_conditions.parameter_two%type;
1990 begin
1991 --+
1992 select count(*)
1993 into lm_count
1994 from ame_conditions
1995 where condition_type = ame_util.listModConditionType
1996 and condition_id = p_condition_id
1997 and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
1998 --+
1999 if lm_count = 0 then
2000 --+
2001 open getConditions(p_application_id => p_application_id
2002 ,p_condition_id => p_condition_id);
2003 fetch getConditions
2004 into l_count;
2005 close getConditions;
2006 --+
2007 if l_count = 0 then
2008 return false;
2009 else
2010 return true;
2011 end if;
2012 --+
2013 else
2014 --+
2015 select parameter_two
2016 into lm_param2
2017 from ame_conditions
2018 where condition_id = p_condition_id
2019 and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
2020 --+
2021 if is_pos_approver(p_name => lm_param2) then
2022 --+
2023 if is_all_approver_types_allowed(p_application_id => p_application_id) then
2024 return true;
2025 else
2026 return false;
2027 end if;
2028 --+
2029 end if;
2030 --+
2031 return true;
2032 end if;
2033 --+
2034 end is_condition_allowed;
2035 --+
2036 --+ invoked from delete_ame_rule_action
2037 --+
2038 function is_action_deletion_allowed(p_rule_id in integer
2039 ,p_action_id in integer) return boolean is
2040 --+
2041 --+ getNonProdActionCnt will return the number of non-production
2042 --+ actions exist in a non-production rule.
2043 --+ For a prod rule, the number of actions would be returned
2044 --+
2045 cursor getNonProdActionCnt(p_rule_id in integer
2046 ,p_action_id in integer) is
2047 select count(*)
2048 from ame_rules rul
2049 ,ame_action_usages acu
2050 ,ame_action_type_usages atyu
2051 ,ame_actions act
2052 where act.action_id <> p_action_id
2053 and rul.rule_id = p_rule_id
2054 and rul.rule_id = acu.rule_id
2055 and acu.action_id = act.action_id
2056 and act.action_type_id = atyu.action_type_id
2057 and (atyu.rule_type <> ame_util.productionRuleType
2058 or rul.rule_type <> ame_util.productionRuleType)
2059 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
2060 or
2061 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
2062 )
2063 and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
2064 or
2065 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
2066 )
2067 and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
2068 and sysdate between atyu.start_date and nvl(atyu.end_date - (1/86400),sysdate);
2069 --+
2070 l_count number(3);
2071 --+
2072 begin
2073 --+
2074 open getNonProdActionCnt(p_rule_id => p_rule_id
2075 ,p_action_id => p_action_id);
2076 fetch getNonProdActionCnt
2077 into l_count;
2078 close getNonProdActionCnt;
2079 --+
2080 if l_count = 0 then
2081 return false;
2082 else
2083 return true;
2084 end if;
2085 --+
2086 end is_action_deletion_allowed;
2087 --+
2088 --+ create_ame_rule
2089 --+ only for api [not for ui]
2090 --+ to verify the action and LM condition combination
2091 --+
2092 procedure chk_LM_action_Condition(p_condition_id in integer
2093 ,p_action_id in integer
2094 ,is_first_condition in boolean) is
2095 --+
2096 cursor getConditionParam(p_condition_id in integer) is
2097 select parameter_one
2098 ,parameter_two
2099 from ame_conditions
2100 where condition_id = p_condition_id
2101 and sysdate between start_date and nvl(end_date - (1/86400),sysdate);
2102 --+
2103 cursor getActionType(p_action_id in integer) is
2104 select aty.name
2105 from ame_actions act
2106 ,ame_action_types aty
2107 where act.action_id = p_action_id
2108 and aty.action_type_id = act.action_type_id
2109 and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
2110 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate);
2111 --+
2112 l_param ame_conditions.parameter_one%type;
2113 l_param_two ame_conditions.parameter_two%type;
2114 l_action_type ame_action_types.name%type;
2115 --+
2116 begin
2117 open getConditionParam(p_condition_id => p_condition_id);
2118 fetch getConditionParam
2119 into l_param
2120 ,l_param_two;
2121 close getConditionParam;
2122 --+
2123 open getActionType(p_action_id => p_action_id);
2124 fetch getActionType
2125 into l_action_type;
2126 close getActionType;
2127 --+
2128 if l_param = 'any_approver' and l_action_type = 'nonfinal authority' then
2129 fnd_message.set_name('PER','AME_400702_INV_LM_ATY_COMB_1');
2130 hr_multi_message.add(p_associated_column1 => 'ACTION_ID');
2131 elsif l_param = 'final_approver' and l_action_type = 'final authority' then
2132 fnd_message.set_name('PER','AME_400703_INV_LM_ATY_COMB_2');
2133 hr_multi_message.add(p_associated_column1 => 'ACTION_ID');
2134 end if;
2135 --+
2136 if l_param = 'final_approver' and not is_per_approver(p_name => l_param_two) and is_first_condition then
2137 fnd_message.set_name('PER','AME_400703_INV_LM_ATY_COMB_2');
2138 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
2139 end if;
2140 end chk_LM_action_Condition;
2141 --+
2142 --+ used in is_action_allowed
2143 --+
2144 function is_group_allowed(p_application_id in integer
2145 ,p_approval_group_id in integer) return boolean is
2146 --+
2147 cursor get_groups(p_application_id in integer
2148 ,p_approval_group_id in integer) is
2149 select count(*)
2150 from ame_approval_group_config gpc
2151 where gpc.approval_group_id = p_approval_group_id
2152 and gpc.application_id = p_application_id
2153 and sysdate between gpc.start_date and nvl(gpc.end_date - (1/86400),sysdate);
2154 --+
2155 l_count number(3);
2156 begin
2157 --+
2158 open get_groups(p_application_id => p_application_id
2159 ,p_approval_group_id => p_approval_group_id);
2160 fetch get_groups
2161 into l_count;
2162 close get_groups;
2163 --+
2164 if l_count = 0 then
2165 return false;
2166 else
2167 return true;
2168 end if;
2169 --+
2170 end is_group_allowed;
2171 --+
2172 --+ used in is_action_allowed.
2173 --+
2174 function is_all_approver_types_allowed(p_application_id in integer) return boolean is
2175 tempValue ame_config_vars.variable_value%type;
2176 begin
2177 --+
2178 tempValue := ame_util.getConfigVar
2179 (variableNameIn => ame_util.allowAllApproverTypesConfigVar
2180 ,applicationIdIn => p_application_id);
2181 --+
2182 if(tempValue = ame_util.no) then
2183 return false;
2184 else
2185 return true;
2186 end if;
2187 --+
2188 end is_all_approver_types_allowed;
2189 --+
2190 --+ used in chk_LM_action_Condition.
2191 --+
2192 function is_per_approver(p_name in varchar2) return boolean is
2193 --+
2194 cursor get_per_approver(p_name in varchar2) is
2195 select count(*)
2196 from wf_roles
2197 where status = 'ACTIVE'
2198 and nvl(expiration_date,sysdate) >= sysdate
2199 and orig_system = 'PER'
2200 and name = p_name;
2201 --+
2202 l_count number(3);
2203 --+
2204 begin
2205 --+
2206 open get_per_approver(p_name => p_name);
2207 fetch get_per_approver
2208 into l_count;
2209 close get_per_approver;
2210 --+
2211 if l_count = 0 then
2212 return false;
2213 else
2214 return true;
2215 end if;
2216 --+
2217 end is_per_approver;
2218 --+
2219 --+ Used in is_condition_allowed.
2220 --+
2221 function is_pos_approver(p_name in varchar2) return boolean is
2222 --+
2223 cursor get_pos_approver(p_name in varchar2) is
2224 select count(*)
2225 from wf_roles
2226 where status = 'ACTIVE'
2227 and nvl(expiration_date,sysdate) >= sysdate
2228 and orig_system = 'POS'
2229 and name = p_name;
2230 --+
2231 l_count number(3);
2232 --+
2233 begin
2234 open get_pos_approver(p_name => p_name);
2235 fetch get_pos_approver
2236 into l_count;
2237 close get_pos_approver;
2238 if l_count = 0 then
2239 return false;
2240 else
2241 return true;
2242 end if;
2243 end is_pos_approver;
2244 --+
2245 --+ Used in create_ame_condition_to_rule.
2246 --+
2247 procedure chk_rule_and_item_class(p_rule_id in integer
2248 ,p_condition_id in integer) is
2249 --+
2250 cursor get_sub_ic_cond(p_rule_id in integer) is
2251 select distinct atr.item_class_id
2252 from ame_rules rul
2253 ,ame_condition_usages cnu
2254 ,ame_attributes atr
2255 ,ame_conditions con
2256 ,ame_item_classes itc
2257 where rul.rule_id = p_rule_id
2258 and rul.rule_id = cnu.rule_id
2259 and cnu.condition_id = con.condition_id
2260 and con.attribute_id = atr.attribute_id
2261 and atr.item_class_id = itc.item_class_id
2262 and itc.name <> ame_util.headerItemClassName
2263 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
2264 or
2265 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
2266 )
2267 and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
2268 or
2269 (sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
2270 )
2271 and sysdate between con.start_date and nvl(con.end_date - (1/86400),sysdate)
2272 and sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate)
2273 and sysdate between itc.start_date and nvl(itc.end_date - (1/86400),sysdate);
2274 --+
2275 l_item_class_id ame_rules.item_class_id%type;
2276 l_header_item_class_id ame_rules.item_class_id%type;
2277 l_sub_ic_cond_list ame_util.idList;
2278 l_con_item_class_id ame_rules.item_class_id%type;
2279 l_con_type ame_conditions.condition_type%type;
2280 begin
2281 --+
2282 select condition_type
2283 into l_con_type
2284 from ame_conditions
2285 where condition_id = p_condition_id
2286 and sysdate between start_date and nvl(end_date - (1/84600),sysdate);
2287 if l_con_type = ame_util.listModConditionType then
2288 return;
2289 end if;
2290 --+
2291 select item_class_id
2292 into l_header_item_class_id
2293 from ame_item_classes
2294 where name = ame_util.headerItemClassName
2295 and sysdate between start_date and nvl(end_date - (1/84600),sysdate);
2296 --+
2297 select atr.item_class_id
2298 into l_con_item_class_id
2299 from ame_conditions con
2300 ,ame_attributes atr
2301 where con.attribute_id = atr.attribute_id
2302 and con.condition_id = p_condition_id
2303 and sysdate between con.start_date and nvl(con.end_date - (1/86400),sysdate)
2304 and sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate);
2305 if l_con_item_class_id = l_header_item_class_id then
2306 return;
2307 end if;
2308 --+
2309 select nvl(item_class_id,l_header_item_class_id)
2310 into l_item_class_id
2311 from ame_rules
2312 where rule_id = p_rule_id
2313 and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
2314 or
2315 (sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
2316 );
2317 --+
2318 if l_item_class_id = l_header_item_class_id then
2319 open get_sub_ic_cond(p_rule_id => p_rule_id);
2320 fetch get_sub_ic_cond
2321 bulk collect into l_sub_ic_cond_list;
2322 if l_sub_ic_cond_list.count <> 0 then
2323 if l_sub_ic_cond_list(1) <> l_con_item_class_id then
2324 fnd_message.set_name('PER','AME_400695_RULE_SUB_ITC_COND');
2325 hr_multi_message.add(p_associated_column1 => 'ITEM_CLASS_ID');
2326 end if;
2327 end if;
2328 close get_sub_ic_cond;
2329 else
2330 if l_con_item_class_id <> l_item_class_id then
2331 fnd_message.set_name('PER','AME_400708_NH_RULE_SUB_ITC_CON');
2332 hr_multi_message.add(p_associated_column1 => 'ITEM_CLASS_ID');
2333 end if;
2334 end if;
2335 --+
2336 end chk_rule_and_item_class;
2337 --+
2338 function is_cond_exist_in_rule(p_rule_id in integer
2339 ,p_condition_id in integer) return boolean is
2340 l_count number(2);
2341 begin
2342 --+
2343 select count(*)
2344 into l_count
2345 from ame_condition_usages
2346 where rule_id = p_rule_id
2347 and condition_id = p_condition_id
2348 and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
2349 or
2350 (sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
2351 );
2352 if l_count = 0 then
2353 return(false);
2354 else
2355 return(true);
2356 end if;
2357 end is_cond_exist_in_rule;
2358 --+
2359 function chk_lm_actions(p_rule_id in integer
2360 ,p_action_id in integer) return boolean is
2361 l_count number(2);
2362 l_aty_name ame_action_types.name%type;
2363 begin
2364 select aty.name
2365 into l_aty_name
2366 from ame_actions act
2367 ,ame_action_types aty
2368 where act.action_id = p_action_id
2369 and act.action_type_id = aty.action_type_id
2370 and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
2371 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate);
2372 if l_aty_name = 'final authority' then
2373 select count(*)
2374 into l_count
2375 from ame_action_usages acu
2376 ,ame_action_types aty
2377 ,ame_actions act
2378 where acu.rule_id = p_rule_id
2379 and aty.name = 'nonfinal authority'
2380 and acu.action_id = act.action_id
2381 and act.action_type_id = aty.action_type_id
2382 and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
2383 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
2384 and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
2385 or
2386 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
2387 );
2388 if l_count > 0 then
2389 return false;
2390 end if;
2391 elsif l_aty_name = 'nonfinal authority' then
2392 select count(*)
2393 into l_count
2394 from ame_action_usages acu
2395 ,ame_action_types aty
2396 ,ame_actions act
2397 where acu.rule_id = p_rule_id
2398 and aty.name = 'final authority'
2399 and acu.action_id = act.action_id
2400 and act.action_type_id = aty.action_type_id
2401 and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
2402 and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
2403 and ((sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate))
2404 or
2405 (sysdate < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + (1/86400)))
2406 );
2407 if l_count > 0 then
2408 return false;
2409 end if;
2410 end if;
2411 return true;
2412 end chk_lm_actions;
2413 --+
2414 end ame_rule_utility_pkg;
[Home] [Help]