[Home] [Help]
PACKAGE BODY: APPS.AME_RULE_API
Source
1 Package Body ame_rule_api as
2 /* $Header: amrulapi.pkb 120.4 2005/10/21 06:52:00 tkolla noship $ */
3 --+
4 -- Package Variables
5 --+
6 g_package varchar2(33) := ' ame_rule_api.';
7 --+
8 -- getConditionType Function
9 --+
10 -- This is a private function which returns the condition type
11 --+
12 function getConditionType(p_condition_id in integer
13 ,p_effective_date in date) return varchar2 as
14 l_condition_type ame_conditions.condition_type%type;
15 begin
16 select condition_type
17 into l_condition_type
18 from ame_conditions
19 where ame_conditions.condition_id = p_condition_id
20 and p_effective_date between start_date
21 and nvl(end_date - ame_util.oneSecond, p_effective_date);
22 return(l_condition_type);
23 exception
24 when others then
25 fnd_message.set_name('PER','AME_400494_INVALID_CONDITION');
26 hr_multi_message.add (p_associated_column1 => 'CONDITION_ID');
27 raise;
28 return(null);
29 end getConditionType;
30 --+
31 -- This is a private function which checks if any overlapping rule usages exist
32 --+
33 Function checkRuleUsageExists(p_application_id in integer
34 ,p_rule_id in integer
35 ,p_rlu_start_date in date
36 ,p_rlu_end_date in date default null
37 ,p_effective_date in date
38 ,p_priority in varchar2 default null
39 ,p_old_start_date in date default null)
40 return number as
41 cursor ruleUsageCursor is
42 select start_date
43 ,end_date
44 ,priority
45 from ame_rule_usages
46 where rule_id =p_rule_id
47 and item_id = p_application_id
48 and (p_effective_date between start_date
49 and nvl(end_date - ame_util.oneSecond, p_effective_date)
50 or
51 (p_effective_date < start_date
52 and start_date < nvl(end_date, start_date + ame_util.oneSecond)))
53 order by start_date desc;
54 usagestartDateList ame_util.dateList;
55 usageEndDateList ame_util.dateList;
56 usagePriorityList ame_util.idList;
57 begin
58 for ruleUsage in ruleUsageCursor loop
59 if (p_old_start_date is null or
60 p_old_start_date <> ruleUsage.start_date) then
61 if ( trunc(p_rlu_start_date) = trunc(ruleUsage.start_date) and
62 trunc(p_rlu_end_date) = trunc(ruleUsage.end_date) and
63 p_priority = ruleUsage.priority
64 ) then
65 return(1);
66 elsif ( trunc(p_rlu_start_date) = trunc(ruleUsage.start_date) and
67 trunc(p_rlu_end_date) = trunc(ruleUsage.end_date)
68 ) then
69 return(2);
70 elsif (ruleUsage.end_date = ame_utility_pkg.endOfTime and p_rlu_end_date = ame_utility_pkg.endOfTime) then
71 return(3);
72 elsif ((p_rlu_end_date = ame_utility_pkg.endOfTime and p_rlu_start_date < ruleUsage.end_date)
73 or
74 ( ruleUsage.end_date = ame_utility_pkg.endOfTime and
75 (p_rlu_start_date >= ruleUsage.start_date
76 or p_rlu_end_date > ruleUsage.start_date))
77 ) then
78 return(3);
79 elsif ( (p_rlu_start_date between ruleUsage.start_date and
80 ruleUsage.end_date - ame_util.oneSecond)
81 or
82 (p_rlu_end_date between ruleUsage.start_date and
83 ruleUsage.end_date - ame_util.oneSecond)
84 or
85 (ruleUsage.start_date between p_rlu_start_date and
86 p_rlu_end_date - ame_util.oneSecond )
87 or
88 (ruleUsage.end_date - ame_util.oneSecond between p_rlu_start_date and
89 p_rlu_end_date - ame_util.oneSecond )
90 ) then
91 return(3);
92 end if;
93 end if;
94 end loop;
95 return(0);
96 exception
97 when others then
98 fnd_message.set_name('PER','AME_400329_RULE_USG_OVER_LIFE');
99 hr_multi_message.add(p_associated_column1 =>'RULE_ID'
100 ,p_associated_column2 =>'ITEM_ID');
101 raise;
102 return(3);
103 end checkRuleUsageExists;
104 --+
105 -- This is a private function which checks if the transaction type
106 -- can have a usage for a rule.
107 --+
108 function checkRuleAllowed(p_application_id in number
109 ,p_rule_id in number
110 ,p_effective_date in date
111 ) return boolean as
112 l_allowAllApproverTypes varchar2(30);
113 l_allowProduction varchar2(30);
114 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
115 l_count number;
116 actionTypeIds ame_util.idList;
117 l_rule_type ame_rules.rule_type%type;
118 applicationName ame_calling_apps.application_name%type;
119 --+
120 cursor getApplicationName(applicationIdIn in integer)is
121 select application_name
122 from ame_calling_apps
123 where application_id = applicationIdIn
124 and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
125 --+
126 cursor getActionTypeCursor is
127 select ame_actions.action_type_id
128 from ame_actions, ame_action_usages
129 where ame_action_usages.rule_id = p_rule_id
130 and ame_action_usages.action_id = ame_actions.action_id
131 and (p_effective_date between ame_action_usages.start_date
132 and nvl(ame_action_usages.end_date - ame_util.oneSecond, p_effective_date)
133 or
134 (p_effective_date < ame_action_usages.start_date
135 and ame_action_usages.start_date < nvl(ame_action_usages.end_date, ame_action_usages.start_date + ame_util.oneSecond)))
136 and p_effective_date between ame_actions.start_date
137 and nvl(ame_actions.end_date - ame_util.oneSecond, p_effective_date) ;
138 begin
139 -- Check the value of the config variable 'allowAllApproverTypes' and 'productionFunctionality'
140 -- to ensure that rules of this type type can be defined for this transaction type.
141 --
142 l_allowAllApproverTypes :=
143 ame_util.getConfigVar
144 (variableNameIn => ame_util.allowAllApproverTypesConfigVar
145 ,applicationIdIn => p_application_id);
146 l_allowProduction :=
147 ame_util.getConfigVar(variableNameIn => ame_util.productionConfigVar
148 ,applicationIdIn => p_application_id);
149 -- get that rule type
150 select rule_type
151 into l_rule_type
152 from ame_rules
153 where rule_id =p_rule_id
154 and (p_effective_date between start_date
155 and nvl(end_date - ame_util.oneSecond, p_effective_date )
156 or
157 (p_effective_date < start_date
158 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
159 --+
160 -- Transform the configuration-variable value into one of the
161 -- pseudo-boolean values used by configuration variables,
162 -- for ease of use in the cursor.
163 --+
164 if l_allowAllApproverTypes = ame_util.no or
165 l_allowProduction in (ame_util.noProductions, ame_util.perApproverProductions) then
166 --+
167 -- fetch the action_type_id's associated with this rule
168 --+
169 open getActionTypeCursor;
170 fetch getActionTypeCursor bulk collect into actionTypeIds;
171 if actionTypeIds.count = 0 then
172 --+
173 -- if this call is not made from an SWI package, raise an exception and return false
174 --+
175 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
176 fnd_message.set_name('PER','AME_400724_NO_ACTION_IN_RULE');
177 hr_multi_message.add (p_associated_column1 =>'RULE_ID');
178 return(false);
179 else
180 return(true);
181 end if;
182 end if;
183 close getActionTypeCursor;
184 --+
185 -- Check that the action types are allowed for this transaction_type
186 --+
187 if l_rule_type not in (ame_util.productionRuleType
188 ,ame_util.preListGroupRuleType
189 ,ame_util.postListGroupRuleType) then
190 if l_allowAllApproverTypes = ame_util.no then
191 -- check if the action types defined are allowed to use approver types
192 -- other than ame_util.perOrigSystem and ame_util.fndUserOrigSystem.
193 for i in 1..actionTypeIds.count loop
194 select count(*)
195 into l_count
196 from ame_approver_type_usages
197 where approver_type_id not in (
198 select approver_type_id
199 from ame_approver_types
200 where orig_system in (ame_util.perOrigSystem
201 ,ame_util.fndUserOrigSystem)
202 and sysdate between start_date
203 and nvl(end_date - ame_util.oneSecond, sysdate))
204 and action_type_id = actionTypeIds(i)
205 and sysdate between start_date
206 and nvl(end_date - ame_util.oneSecond, sysdate);
207 if l_count <> 0 then
208 return(false);
209 end if;
210 end loop;
211 end if;
212 end if;
213 if l_allowProduction in (ame_util.noProductions
214 ,ame_util.perApproverProductions) then
215 if l_rule_type = ame_util.productionRuleType then
216 return(false);
217 end if;
218 -- If no productions then check that no production
219 -- actions are defined for the rule.
220 if l_allowProduction = ame_util.noProductions then
221 for i in 1..actionTypeIds.count loop
222 select count(*)
223 into l_count
224 from ame_action_type_usages
225 where rule_type = ame_util.productionRuleType
226 and action_type_id = actionTypeIds(i)
227 and p_effective_date between start_date
228 and nvl(end_date - ame_util.oneSecond, p_effective_date);
229 if l_count <> 0 then
230 open getApplicationName(applicationIdIn => p_application_id);
231 fetch getApplicationName into applicationName;
232 close getApplicationName;
233 fnd_message.set_name('PER','AME_400640_TTY_NO_PROD_ACTIONS');
234 fnd_message.set_token('TXTYPENAME',applicationName);
235 hr_multi_message.add(p_associated_column1 =>'RULE_ID');
236 return(false);
237 end if;
238 end loop;
239 end if;
240 end if;
241 end if;
242 return(true);
243 end checkRuleAllowed;
244 --+
245 --+
246 --+
247 procedure fetchNewRuleDates2(p_rule_id in number
248 ,p_rul_start_date out nocopy date
249 ,p_rul_end_date out nocopy date) as
250 begin
251 select min(start_date)
252 into p_rul_start_date
253 from ame_rule_usages
254 where rule_id = p_rule_id
255 and (sysdate between start_date
256 and nvl(end_date - ame_util.oneSecond, sysdate )
257 or
258 (sysdate < start_date
259 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
260 select max(end_date)
261 into p_rul_end_date
262 from ame_rule_usages
263 where rule_id = p_rule_id
264 and (sysdate between start_date
265 and nvl(end_date - ame_util.oneSecond, sysdate )
266 or
267 (sysdate < start_date
268 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
269 end fetchNewRuleDates2;
270 --+
271 -- This is a private function which checks if the Rule start date and end date need to
272 -- be changed.
273 --+
274 Procedure fetchNewRuleDates(p_rule_id in number
275 ,p_rlu_start_date in date
276 ,p_rlu_end_date in date
277 ,p_rul_start_date in out nocopy date
278 ,p_rul_end_date in out nocopy date
279 ,p_date_changed out nocopy varchar2
280 ) as
281 begin
282 p_date_changed := 'N';
283 -- Check if the rule's start_date > new usage start_date or
284 -- rule's end_date < new usage end_date then
285 -- The rule start_date or end_date needs to be changed. Calculate new values
286 if(p_rul_start_date > p_rlu_start_date or
287 p_rul_end_date < p_rlu_end_date) then
288 p_date_changed := 'Y';
289 if p_rul_start_date > p_rlu_start_date then
290 p_rul_start_date := p_rlu_start_date;
291 end if;
292 if p_rul_end_date < p_rlu_end_date then
293 p_rul_end_date := p_rlu_end_date;
294 end if;
295 end if;
296 end fetchNewRuleDates;
297 --+
298 --+
299 --+
300 procedure getConditionIds(ruleIdIn in integer,
301 conditionIdListOut out nocopy ame_util.idList) as
302 cursor conditionCursor(ruleIdIn in integer) is
303 select ame_conditions.condition_id condition_id
304 ,ame_conditions.condition_type condition_type
305 from ame_conditions
306 ,ame_condition_usages
307 where ame_conditions.condition_id = ame_condition_usages.condition_id
308 and ame_condition_usages.rule_id = ruleIdIn
309 and (ame_conditions.start_date <= sysdate
310 and (ame_conditions.end_date is null or sysdate < ame_conditions.end_date))
311 and ((sysdate between ame_condition_usages.start_date
312 and nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate))
313 or
314 (sysdate < ame_condition_usages.start_date
315 and ame_condition_usages.start_date <
316 nvl(ame_condition_usages.end_date,
317 ame_condition_usages.start_date + ame_util.oneSecond)))
318 order by condition_type;
319 tempIndex integer;
320 begin
321 /*
322 Can't do a bulk collect here because we have to order by condition_type
323 (so that exception conditions, either pre or post, get displayed after
324 ordinary conditions), and we don't want to output condition_type.
325 */
326 tempIndex := 1;
327 for tempCondition in conditionCursor(ruleIdIn => ruleIdIn) loop
328 conditionIdListOut(tempIndex) := tempCondition.condition_id;
329 tempIndex := tempIndex + 1;
330 end loop;
331 if(tempIndex = 1) then
332 conditionIdListOut := ame_util.emptyIdList;
333 end if;
334 exception
335 when others then
336 conditionIdListOut := ame_util.emptyIdList;
337 end getConditionIds;
338 --+
339 --+
340 --+
341 procedure getActionIds(ruleIdIn in integer,
342 actionIdListOut out nocopy ame_util.idList) as
343 cursor actionCursor(ruleIdIn in integer) is
344 select ame_action_usages.action_id
345 from ame_action_usages
346 where rule_id = ruleIdIn
347 and ((sysdate between ame_action_usages.start_date
348 and nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate))
349 or
350 (sysdate < ame_action_usages.start_date
351 and ame_action_usages.start_date <
352 nvl(ame_action_usages.end_date, ame_action_usages.start_date + ame_util.oneSecond)));
353 actionId integer;
354 tempIndex integer;
355 begin
356 tempIndex := 1;
357 for tempAction in actionCursor(ruleIdIn => ruleIdIn) loop
358 actionIdListOut(tempIndex) := tempAction.action_id;
359 tempIndex := tempIndex + 1;
360 end loop;
361 exception
362 when others then
363 actionIdListOut := ame_util.emptyIdList;
364 end getActionIds;
365 --+
366 -- This is a private function which checks if a rule already exists with the same
367 -- combination of conditions and actions.
368 --+
369 Function ruleExists(p_rule_id in number
370 ,p_rule_type in varchar2
371 ,p_item_class_id in number
372 ,p_effective_date in date
373 ,p_conditions_list in ame_util.idList
374 ,p_actions_list in ame_util.idList
375 ) return boolean as
376 cursor ruleIdCursor(typeIn in varchar2
377 ,itemClassIdIn in integer default null) is
378 select rule_id
379 from ame_rules
380 where rule_type = typeIn
381 and (item_class_id is null or item_class_id = itemClassIdIn)
382 and ((sysdate between start_date
383 and nvl(end_date - ame_util.oneSecond, sysdate))
384 or
385 (sysdate < start_date
386 and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
387 actionIdList1 ame_util.idList;
388 actionIdList2 ame_util.idList;
389 actionIdMatch boolean;
390 conditionIdList1 ame_util.idList;
391 conditionIdList2 ame_util.idList;
392 conditionIdMatch boolean;
393 begin
394 for i in 1..p_conditions_list.count loop
395 conditionIdList1(i) := p_conditions_list(i);
396 end loop;
397 --+
398 ame_util.sortIdListInPlace(idListInOut => conditionIdList1);
399 for i in 1..p_actions_list.count loop
400 actionIdList1(i) := p_actions_list(i);
401 end loop;
402 --+
403 ame_util.sortIdListInPlace(idListInOut => actionIdList1);
404 conditionIdMatch := false;
405 actionIdMatch := false;
406 for tempRuleId in ruleIdCursor(typeIn => p_rule_type
407 ,itemClassIdIn => p_item_class_id) loop
408 getConditionIds(ruleIdIn => tempRuleId.rule_id,
409 conditionIdListOut => conditionIdList2);
410 ame_util.sortIdListInPlace(idListInOut => conditionIdList2);
411 if(ame_util.idListsMatch(idList1InOut => conditionIdList1
412 ,idList2InOut => conditionIdList2
413 ,sortList1In => false
414 ,sortList2In => false)) then
415 conditionIdMatch := true;
416 end if;
417 if conditionIdMatch then
418 getActionIds(ruleIdIn => tempRuleId.rule_id
419 ,actionIdListOut => actionIdList2);
420 ame_util.sortIdListInPlace(idListInOut => actionIdList2);
421 if(ame_util.idListsMatch(idList1InOut => actionIdList1
422 ,idList2InOut => actionIdList2
423 ,sortList1In => false
424 ,sortList2In => false)) then
425 actionIdMatch := true;
426 end if;
427 if(conditionIdMatch and actionIdMatch) then
428 return(true);
429 end if;
430 end if;
431 conditionIdList2.delete;
432 actionIdList2.delete;
433 conditionIdMatch := false;
434 actionIdMatch := false;
435 end loop;
436 return(false);
437 exception
438 when others then
439 return(true);
440 end ruleExists;
441 --
442 -- ----------------------------------------------------------------------------
443 -- |-------------------------< create_ame_rule >-----------------------------|
444 -- ----------------------------------------------------------------------------
445 --
446 procedure create_ame_rule
447 (p_validate in boolean default false
448 ,p_language_code in varchar2 default hr_api.userenv_lang
449 ,p_rule_key in varchar2
450 ,p_description in varchar2
451 ,p_rule_type in varchar2
452 ,p_item_class_id in number default null
453 ,p_condition_id in number default null
454 ,p_action_id in number default null
455 ,p_application_id in number default null
456 ,p_priority in number default null
457 ,p_approver_category in varchar2 default null
458 ,p_rul_start_date in out nocopy date
459 ,p_rul_end_date in out nocopy date
460 ,p_rule_id out nocopy number
461 ,p_rul_object_version_number out nocopy number
462 ,p_rlu_object_version_number out nocopy number
463 ,p_rlu_start_date out nocopy date
464 ,p_rlu_end_date out nocopy date
465 ,p_cnu_object_version_number out nocopy number
466 ,p_cnu_start_date out nocopy date
467 ,p_cnu_end_date out nocopy date
468 ,p_acu_object_version_number out nocopy number
469 ,p_acu_start_date out nocopy date
470 ,p_acu_end_date out nocopy date
471 ) is
472 --
473 -- Declare cursors and local variables
474 --
475 l_proc varchar2(72) := g_package||'create_ame_rule';
476 l_rule_id number;
477 l_rul_object_version_number number;
478 l_rlu_object_version_number number;
479 l_rul_start_date date;
480 l_rlu_start_date date;
481 l_rul_end_date date;
482 l_rlu_end_date date;
483 l_cnu_object_version_number number;
484 l_acu_object_version_number number;
485 l_cnu_start_date date;
486 l_acu_start_date date;
487 l_cnu_end_date date;
488 l_acu_end_date date;
489 l_swi_call boolean;
490 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
491 l_effective_date date;
492 l_use_count number := 0;
493 l_condition_type varchar2(10);
494 l_attribute_id number;
495 l_action_rule_type ame_rules.rule_type%type;
496 l_item_class_id ame_item_classes.item_class_id%type;
497 --+
498 cursor getActionRuleType is
499 select atyu.rule_type
500 from ame_action_type_usages atyu
501 ,ame_actions act
502 where act.action_id = p_action_id
503 and act.action_type_id = atyu.action_type_id
504 and sysdate between act.start_date and nvl(act.end_date-(1/86400),sysdate)
505 and sysdate between atyu.start_date and nvl(atyu.end_date-(1/86400),sysdate);
506 --+
507 cursor getConditionDetails is
508 select condition_type
509 ,attribute_id
510 from ame_conditions
511 where condition_id = p_condition_id
512 and l_effective_date between start_date
513 and nvl(end_date - ame_util.oneSecond,l_effective_date);
514
515 begin
516 hr_utility.set_location('Entering:'|| l_proc, 10);
517 --
518 -- Issue a savepoint
519 --
520 savepoint create_ame_rule;
521 l_swi_call := true;
522 l_item_class_id := p_item_class_id;
523 l_effective_date := sysdate;
524 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK, l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
525 --+ this procedure is not invoked from the UI.
526 l_swi_call := false;
527 --+
528 --+ Check the application id.
529 --+
530 ame_rule_utility_pkg.checkApplicationId(p_application_id => p_application_id);
531 --+
532 --+ condition id cannot be null for LCE and LM/SUB rule.
533 --+
534 if p_condition_id is null then
535 --+
536 if p_rule_type = ame_util.exceptionRuleType then
537 fnd_message.set_name('PER','AME_400709_NO_EXC_COND_LCE_RUL');
538 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
539 end if;
540 --+
541 if p_rule_type = ame_util.listModRuleType or p_rule_type = ame_util.substitutionRuleType then
542 fnd_message.set_name('PER','AME_400710_NO_LM_CON_LMSUB_RUL');
543 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
544 end if;
545 --+
546 else
547 --+
548 --+ Check the not null Condition Id.
549 --+
550 ame_rule_utility_pkg.checkConditionId(p_condition_id);
551 --+
552 --+ Fetch the condition details.
553 --+
554 open getConditionDetails;
555 fetch getConditionDetails
556 into l_condition_type
557 ,l_attribute_id ;
558 if getConditionDetails%notfound then
559 fnd_message.set_name('PER','AME_400494_INVALID_CONDITION');
560 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
561 end if;
562 close getConditionDetails;
563 --+
564 --+ Check if this condition can be added to this transaction type
565 --+
566 if not ame_rule_utility_pkg.is_condition_allowed(p_application_id => p_application_id
567 ,p_condition_id => p_condition_id) then
568 fnd_message.set_name('PER','AME_400738_COND_NOT_IN_APP');
569 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
570 end if;
571 --+
572 --+ Item class id should be null for LM Conditions
573 --+
574 if p_rule_type = 0 and l_condition_type = ame_util.listModConditionType then
575 l_item_class_id := null;
576 end if;
577 --+
578 end if;
579 --+
580 end if;
581 --+ End of if not swi block.
582 --
583 -- Call Before Process User Hook
584 --
585 begin
586 ame_rule_bk1.create_ame_rule_b
587 (p_rule_key => p_rule_key
588 ,p_description => p_description
589 ,p_rule_type => p_rule_type
590 ,p_item_class_id => l_item_class_id
591 ,p_condition_id => p_condition_id
592 ,p_action_id => p_action_id
593 ,p_application_id => p_application_id
594 ,p_priority => p_priority
595 ,p_approver_category => p_approver_category
596 ,p_rul_start_date => p_rul_start_date
597 ,p_rul_end_date => p_rul_end_date
598 );
599 exception
600 when hr_api.cannot_find_prog_unit then
601 hr_api.cannot_find_prog_unit_error
602 (p_module_name => 'create_ame_rule'
603 ,p_hook_type => 'BP'
604 );
605 end;
606 --
607 -- Process Logic
608 --
609 -- Set the effective date to the sysdate
610 l_effective_date := sysdate;
611 --
612 -- assign correct values for rule start and end dates
613 --
614 if p_rul_start_date is null then
615 l_rul_start_date := l_effective_date;
616 else
617 l_rul_start_date := p_rul_start_date;
618 end if;
619 if p_rul_end_date is null then
620 l_rul_end_date := ame_utility_pkg.endOfTime;
621 else
622 l_rul_end_date := p_rul_end_date;
623 end if;
624
625 if not l_swi_call then
626 if(l_rul_start_date < l_effective_date ) then
627 fnd_message.set_name('PER','AME_400208_RUL_STRT_PREC_TDY');
628 fnd_message.raise_error;
629 end if;
630 end if;
631
632 l_effective_date := l_rul_start_date;
633 --
634 -- insert the row in ame_rules.
635 --
636 ame_rul_ins.ins(p_effective_date => l_effective_date
637 ,p_rule_type => p_rule_type
638 ,p_description => p_description
639 ,p_rule_key => p_rule_key
640 ,p_item_class_id => l_item_class_id
641 ,p_start_date => l_rul_start_date
642 ,p_end_date => l_rul_end_date
643 ,p_rule_id => l_rule_id
644 ,p_object_version_number => l_rul_object_version_number
645 );
646 -- insert data into TL tables
647 ame_rtl_ins.ins_tl(p_language_code => p_language_code
648 ,p_rule_id => l_rule_id
649 ,p_description => p_description
650 );
651 --
652 -- Call DBMS_UTILITY.FORMAT_CALL_STACK to check if the call has been made
653 -- from the 'AME_RULE_SWI' package.
654 --
655 if not l_swi_call then
656 --
657 -- As the call is not from the SWI layer the following integrity checks need to be done.
658 -- a. If Rule type is Exception rule, then the condition being passed in is an exception condition
659 -- b. If Rule type is List-modification or substitution rules the condition being passed in is
660 -- a list-modification condition.
661 -- c. Check that action_id is not null.
662 --
663 --+ Verify the Rule Type and Condition Type Combination.
664 --+
665 if p_rule_type = ame_util.exceptionRuleType then
666 if (getConditionType(p_condition_id => p_condition_id
667 ,p_effective_date => l_effective_date)
668 <> ame_util.exceptionConditionType) then
669 fnd_message.set_name('PER','AME_400709_NO_EXC_COND_LCE_RUL');
670 hr_multi_message.add (p_associated_column1 =>'CONDITION_ID');
671 end if;
672 elsif (p_rule_type = ame_util.listModRuleType or
673 p_rule_type = ame_util.substitutionRuleType) then
674 if ( getConditionType(p_condition_id => p_condition_id
675 ,p_effective_date => l_effective_date)
676 <> ame_util.listModConditionType) then
677 fnd_message.set_name('PER','AME_400710_NO_LM_CON_LMSUB_RUL');
678 hr_multi_message.add (p_associated_column1 => 'CONDITION_ID');
679 end if;
680 end if;
681 --+
682 --+ Check Action Id.
683 --+
684 if p_action_id is null then
685 fnd_message.set_name('PER','AME_400725_NO_ACTION_DEFINED');
686 hr_multi_message.add(p_associated_column1 => 'ACTION_ID');
687 else
688 ame_rule_utility_pkg.checkActionId(p_action_id);
689 --+
690 --+ Check the action and condition combination for LM Rule.
691 --+
692 if p_rule_type = ame_util.listModRuleType then
693 ame_rule_utility_pkg.chk_LM_action_Condition(p_condition_id => p_condition_id
694 ,p_action_id => p_action_id
695 ,is_first_condition => true);
696 end if;
697 end if;
698 --+ Fetch Action details.
699 open getActionRuleType;
700 fetch getActionRuleType
701 into l_action_rule_type;
702 --
703 -- set the start date and end date for rule usages
704 --
705 l_rlu_start_date := l_rul_start_date;
706 l_rlu_end_date := l_rul_end_date;
707 --
708 -- Create Condition usage if condition id is not null.
709 --
710 if p_condition_id is not null then
711 create_ame_condition_to_rule
712 (p_validate => p_validate
713 ,p_rule_id => l_rule_id
714 ,p_condition_id => p_condition_id
715 ,p_object_version_number => l_cnu_object_version_number
716 ,p_start_date => l_cnu_start_date
717 ,p_end_date => l_cnu_end_date
718 );
719 end if;
720 --+
721 --+ Check the Rule Type and action combination.
722 --+
723 if not ame_rule_utility_pkg.chk_rule_type
724 (p_rule_id => l_rule_id
725 ,p_rule_type => p_rule_type
726 ,p_action_rule_type => l_action_rule_type
727 ,p_application_id => p_application_id
728 ,p_allow_production_action => false) then
729 fnd_message.set_name('PER','AME_400741_RULE_TYPE_MISMATCH');
730 hr_multi_message.add(p_associated_column1 => 'RULE_TYPE');
731 end if;
732 --
733 -- Create Action usage
734 --
735 create_ame_action_to_rule
736 (p_validate => p_validate
737 ,p_rule_id => l_rule_id
738 ,p_action_id => p_action_id
739 ,p_object_version_number => l_acu_object_version_number
740 ,p_start_date => l_acu_start_date
741 ,p_end_date => l_acu_end_date
742 );
743 --
744 -- Create rule usage
745 --
746 create_ame_rule_usage
747 (p_validate => p_validate
748 ,p_rule_id => l_rule_id
749 ,p_application_id => p_application_id
750 ,p_priority => p_priority
751 ,p_approver_category => p_approver_category
752 ,p_object_version_number => l_rlu_object_version_number
753 ,p_start_date => l_rlu_start_date
754 ,p_end_date => l_rlu_end_date
755 );
756 end if; -- Check that call is not from an SWI package
757 --
758 -- Call After Process User Hook
759 --
760 begin
761 ame_rule_bk1.create_ame_rule_a
762 (p_rule_key => p_rule_key
763 ,p_description => p_description
764 ,p_rule_type => p_rule_type
765 ,p_item_class_id => l_item_class_id
766 ,p_condition_id => p_condition_id
767 ,p_action_id => p_action_id
768 ,p_application_id => p_application_id
769 ,p_priority => p_priority
770 ,p_approver_category => p_approver_category
771 ,p_rul_start_date => p_rul_start_date
772 ,p_rul_end_date => p_rul_end_date
773 ,p_rule_id => p_rule_id
774 ,p_rul_object_version_number => p_rul_object_version_number
775 ,p_rlu_object_version_number => p_rlu_object_version_number
776 ,p_rlu_start_date => p_rlu_start_date
777 ,p_rlu_end_date => p_rlu_end_date
778 ,p_cnu_object_version_number => p_cnu_object_version_number
779 ,p_cnu_start_date => p_cnu_start_date
780 ,p_cnu_end_date => p_cnu_end_date
781 ,p_acu_object_version_number => p_acu_object_version_number
782 ,p_acu_start_date => p_acu_start_date
783 ,p_acu_end_date => p_acu_end_date
784 );
785 exception
786 when hr_api.cannot_find_prog_unit then
787 hr_api.cannot_find_prog_unit_error
788 (p_module_name => 'create_ame_rule'
789 ,p_hook_type => 'AP'
790 );
791 end;
792 --
793 -- When in validation only mode raise the Validate_Enabled exception
794 --
795 if p_validate then
796 raise hr_api.validate_enabled;
797 end if;
798 --
799 -- Set all IN OUT and OUT parameters with out values
800 --
801 p_rule_id := l_rule_id;
802 p_rul_object_version_number := l_rul_object_version_number;
803 p_rul_start_date := l_rul_start_date;
804 p_rul_end_date := l_rul_end_date;
805 if not l_swi_call then
806 p_rlu_object_version_number := l_rlu_object_version_number;
807 p_rlu_start_date := l_rlu_start_date;
808 p_rlu_end_date := l_rlu_end_date;
809 p_acu_object_version_number := l_acu_object_version_number;
810 p_acu_start_date := l_acu_start_date;
811 p_acu_end_date := l_acu_end_date;
812 p_cnu_object_version_number := l_cnu_object_version_number;
813 p_cnu_start_date := l_cnu_start_date;
814 p_cnu_end_date := l_cnu_end_date;
815 end if;
816 --
817 hr_utility.set_location(' Leaving:'||l_proc, 70);
818 exception
819 when hr_api.validate_enabled then
820 --
821 -- As the Validate_Enabled exception has been raised
822 -- we must rollback to the savepoint
823 --
824 rollback to create_ame_rule;
825 --
826 -- Reset IN OUT parameters and set OUT parameters
827 -- (Any key or derived arguments must be set to null
828 -- when validation only mode is being used.)
829 --
830 p_rule_id := null;
831 p_rul_object_version_number := null;
832 p_rul_start_date := null;
833 p_rul_end_date := null;
834 if not l_swi_call then
835 p_rlu_object_version_number := null;
836 p_rlu_start_date := null;
837 p_rlu_end_date := null;
838 p_acu_object_version_number := null;
839 p_acu_start_date := null;
840 p_acu_end_date := null;
841 p_cnu_object_version_number := null;
842 p_cnu_start_date := null;
843 p_cnu_end_date := null;
844 end if;
845 hr_utility.set_location(' Leaving:'||l_proc, 80);
846 when others then
847 --
848 -- A validation or unexpected error has occured
849 --
850 rollback to create_ame_rule;
851 --
852 -- Reset IN OUT parameters and set all
853 -- OUT parameters, including warnings, to null
854 --
855 p_rule_id := null;
856 p_rul_object_version_number := null;
857 p_rul_start_date := null;
858 p_rul_end_date := null;
859 if not l_swi_call then
860 p_rlu_object_version_number := null;
861 p_rlu_start_date := null;
862 p_rlu_end_date := null;
863 p_acu_object_version_number := null;
864 p_acu_start_date := null;
865 p_acu_end_date := null;
866 p_cnu_object_version_number := null;
867 p_cnu_start_date := null;
868 p_cnu_end_date := null;
869 end if;
870 hr_utility.set_location(' Leaving:'||l_proc, 90);
871 raise;
872 end create_ame_rule;
873 --
874 -- ----------------------------------------------------------------------------
875 -- |------------------< create_ame_rule_usage >--------------------------|
876 -- ----------------------------------------------------------------------------
877 --
878 procedure create_ame_rule_usage
879 (p_validate in boolean default false
880 ,p_rule_id in number
881 ,p_application_id in number
882 ,p_priority in number default null
883 ,p_approver_category in varchar2 default null
884 ,p_start_date in out nocopy date
885 ,p_end_date in out nocopy date
886 ,p_object_version_number out nocopy number
887 ) is
888 --
889 -- Declare cursors and local variables
890 --
891 l_proc varchar2(72) := g_package||'create_ame_rule_usage';
892 l_dummy varchar2(10);
893 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
894 l_effective_date date;
895 l_approver_category varchar2(1);
896 l_result number(2);
897 --+
898 cursor getAttributeUsages is
899 select attribute_id
900 ,use_count
901 ,start_date
902 ,end_date
903 ,object_version_number
904 from ame_attribute_usages
905 where attribute_id in (
906 select ame_conditions.attribute_id
907 from ame_conditions
908 ,ame_condition_usages
909 where ame_condition_usages.rule_id = p_rule_id
910 and (l_effective_date between ame_condition_usages.start_date
911 and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date )
912 or
913 (l_effective_date < ame_condition_usages.start_date
914 and ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
915 ame_condition_usages.start_date + ame_util.oneSecond)))
916 and ame_condition_usages.condition_id = ame_conditions.condition_id
917 and l_effective_date between ame_conditions.start_date
918 and nvl(ame_conditions.end_date - ame_util.oneSecond, l_effective_date)
919 )
920 and application_id = p_application_id
921 and l_effective_date between ame_attribute_usages.start_date
922 and nvl(ame_attribute_usages.end_date - ame_util.oneSecond, l_effective_date);
923 --+
924 cursor getAttributeUsages2(p_attribute_id in number) is
925 select attribute_id, use_count, start_date, end_date, object_version_number
926 from ame_attribute_usages
927 where attribute_id = p_attribute_id
928 and application_id = p_application_id
929 and l_effective_date between start_date and
930 nvl(end_date - ame_util.oneSecond,l_effective_date);
931 --+
932 cursor getReqAttributes is
933 select man.attribute_id
934 from ame_mandatory_attributes man
935 ,ame_action_usages acu
936 ,ame_actions act
937 where man.action_type_id = act.action_type_id
938 and acu.action_id = act.action_id
939 and acu.rule_id = p_rule_id
940 and l_effective_date between man.start_date and nvl(man.end_date - ame_util.oneSecond, l_effective_date)
941 and l_effective_date between act.start_date and nvl(act.end_date - ame_util.oneSecond, l_effective_date)
942 and ((l_effective_date between acu.start_date and nvl(acu.end_date - ame_util.oneSecond, l_effective_date))
943 or
944 (l_effective_date < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + ame_util.oneSecond))
945 );
946 --+
947 cursor getActions is
948 select action_id
949 ,start_date
950 ,end_date
951 ,object_version_number
952 from ame_action_usages
953 where rule_id = p_rule_id
954 and (l_effective_date between start_date
955 and nvl(end_date - ame_util.oneSecond,l_effective_date)
956 or
957 (l_effective_date < start_date and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
958 --+
959 cursor getConditions is
960 select condition_id
961 ,start_date
962 ,end_date
963 ,object_version_number
964 from ame_condition_usages
965 where rule_id = p_rule_id
966 and (l_effective_date between start_date
967 and nvl(end_date - ame_util.oneSecond, l_effective_date)
968 or
969 (l_effective_date < start_date and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
970 --+
971 cursor itemClassUsageCursor(p_application_id in number
972 ,p_item_class_id in number
973 ,l_effective_date in date) is
974 select null
975 from ame_item_class_usages
976 where application_id =p_application_id
977 and item_class_id = p_item_class_id
978 and l_effective_date between start_date
979 and nvl(end_date - ame_util.oneSecond, l_effective_date);
980 l_rul_object_version_number number;
981 l_rule_id number;
982 l_rule_type ame_rules.rule_type%type;
983 l_item_class_id number;
984 l_item_class_name ame_item_classes.name%type;
985 l_date_changed varchar2(10);
986 l_variable_value varchar2(200);
987 l_application_id number;
988 l_overlapping_usage number;
989 l_atu_object_version_number number;
990 l_atu_start_date date;
991 l_atu_end_date date;
992 l_rlu_object_version_number number;
993 l_rlu_start_date date;
994 l_rlu_end_date date;
995 l_rul_start_date date;
996 l_rul_end_date date;
997 l_cnu_object_version_number number;
998 l_cnu_start_date date;
999 l_cnu_end_date date;
1000 l_acu_object_version_number number;
1001 l_acu_start_date date;
1002 l_acu_end_date date;
1003 l_use_count number := 0;
1004 priority varchar2(100);
1005 l_swi_call boolean;
1006 l_create_ame_rule_call boolean;
1007 begin
1008 hr_utility.set_location('Entering:'|| l_proc, 10);
1009 --
1010 -- Issue a savepoint
1011 --
1012 savepoint create_ame_rule_usage;
1013 --
1014 -- Call Before Process User Hook
1015 --
1016 begin
1017 ame_rule_bk2.create_ame_rule_usage_b
1018 (p_rule_id => p_rule_id
1019 ,p_application_id => p_application_id
1020 ,p_priority => p_priority
1021 ,p_approver_category => p_approver_category
1022 ,p_start_date => p_start_date
1023 ,p_end_date => p_end_date
1024 );
1025 exception
1026 when hr_api.cannot_find_prog_unit then
1027 hr_api.cannot_find_prog_unit_error
1028 (p_module_name => 'create_ame_rule_usage'
1029 ,p_hook_type => 'BP'
1030 );
1031 end;
1032 --
1033 -- Process Logic
1034 --
1035 -- Set the effective date to the sysdate
1036 l_effective_date := sysdate;
1037 l_rlu_start_date := p_start_date;
1038 l_rlu_end_date := p_end_date;
1039 l_swi_call := true;
1040 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK, l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
1041 l_swi_call := false;
1042 end if;
1043
1044 -- If call is from the UI set the effective date to rule start date non future dated rules
1045 if l_swi_call then
1046 if l_effective_date > p_start_date then
1047 l_effective_date := p_start_date;
1048 end if;
1049 end if;
1050 --
1051 -- Check that the transaction type has a usage for the Item class of the rule.
1052 --
1053 -- get item class for rule
1054
1055 --+
1056 --+ Check Rule Id
1057 --+
1058 ame_rule_utility_pkg.checkRuleId(p_rule_id => p_rule_id);
1059 --+
1060 --+ Check Application Id
1061 --+
1062 ame_rule_utility_pkg.checkApplicationId(p_application_id => p_application_id);
1063 --+
1064 --+ Check If all the actions for this rule
1065 --+ are valid for this Transaction Type.
1066 --+
1067 l_result := ame_rule_utility_pkg.is_rule_usage_allowed(p_application_id => p_application_id
1068 ,p_rule_id => p_rule_id);
1069 if l_result = ame_rule_utility_pkg.ActionNotAllowedInTTY then
1070 fnd_message.set_name('PER','AME_400735_ACT_NOT_IN_APP');
1071 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
1072 elsif l_result = ame_rule_utility_pkg.GroupNotAllowedInTTY then
1073 fnd_message.set_name('PER','AME_400744_GRP_NOT_IN_APP');
1074 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
1075 elsif l_result = ame_rule_utility_pkg.PosActionNotAllowedInTTY then
1076 fnd_message.set_name('PER','AME_400770_POS_APR_NOT_IN_APP');
1077 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
1078 end if;
1079 --+
1080 --+ Check If all the conditions are
1081 --+ valid for this Transaction Type.
1082 --+
1083 if not ame_rule_utility_pkg.is_rule_usage_cond_allowed(p_application_id => p_application_id
1084 ,p_rule_id => p_rule_id) then
1085 fnd_message.set_name('PER','AME_400738_COND_NOT_IN_APP');
1086 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
1087 end if;
1088 --+
1089 select item_class_id
1090 ,rule_type
1091 ,start_date
1092 ,end_date
1093 ,object_version_number
1094 into l_item_class_id
1095 ,l_rule_type
1096 ,l_rul_start_date
1097 ,l_rul_end_date
1098 ,l_rul_object_version_number
1099 from ame_rules
1100 where rule_id = p_rule_id
1101 and ((l_effective_date between start_date
1102 and nvl(end_date - ame_util.oneSecond, l_effective_date))
1103 or
1104 (l_effective_date < start_date
1105 and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1106 if l_item_class_id is not null then
1107 -- check item class is valid
1108 open itemClassUsageCursor(p_application_id => p_application_id
1109 ,p_item_class_id => l_item_class_id
1110 ,l_effective_date => l_effective_date);
1111 fetch itemClassUsageCursor into l_dummy;
1112 if itemClassUsageCursor%NOTFOUND then
1113 fnd_message.set_name('PER','AME_400740_INV_ITEM_CLASS_ID');
1114 hr_multi_message.add(p_associated_column1 =>'RULE_ID');
1115 end if;
1116 close itemClassUsageCursor;
1117 --
1118 -- Check the value of the config variable 'allowAllItemClassRules' to ensure that rules for this
1119 -- item class can be defined for this application id.
1120 --
1121 l_variable_value := ame_util.getConfigVar
1122 (variableNameIn => ame_util.allowAllICRulesConfigVar
1123 ,applicationIdIn => p_application_id);
1124 if l_variable_value = ame_util.no then
1125 -- check that the rule item class is header
1126 select name
1127 into l_item_class_name
1128 from ame_item_classes
1129 where item_class_id = l_item_class_id
1130 and l_effective_date between start_date
1131 and nvl(end_date - ame_util.oneSecond, l_effective_date);
1132 if l_item_class_name not in (ame_util.headerItemClassName
1133 ,ame_util.lineItemItemClassName) then
1134 fnd_message.set_name('PER','AME_400743_ITC_NOT_ALLOWED');
1135 hr_multi_message.add(p_associated_column1 => 'RULE_ID');
1136 end if;
1137 end if;
1138 end if;
1139 --
1140 -- Check the value of the config variable 'allowAllApproverTypes' and 'productionFunctionality'
1141 -- to ensure that rules of this type type can be defined for this transaction type.
1142 --
1143 /*
1144 if l_rule_type not in (ame_util.substitutionRuleType) then
1145 if not checkRuleAllowed(p_application_id => p_application_id
1146 ,p_rule_id => p_rule_id
1147 ,p_effective_date => l_effective_date) then
1148 fnd_message.set_name('PER','AME_99999_RU_NOT_ALLOWED');
1149 fnd_message.raise_error;
1150 -- hr_multi_message.add(p_associated_column1 => 'RULE_ID');
1151 end if;
1152 end if;
1153 */
1154 --
1155 -- Check the value of the config variable 'allowFyiNotifications'
1156 -- to ensure that rules of this approver category are allowed.
1157 --
1158 if p_approver_category = ame_util.fyiApproverCategory then
1159 if (ame_util.getConfigVar(variableNameIn => ame_util.allowFyiNotificationsConfigVar
1160 ,applicationIdIn => p_application_id) = ame_util.no ) then
1161 fnd_message.set_name('PER','AME_400742_FYI_CAT_NO_ALLOWED');
1162 hr_multi_message.add(p_associated_column1 => 'APPROVER_CATEGORY');
1163 end if;
1164 end if;
1165 l_approver_category := p_approver_category;
1166 if not l_swi_call then
1167 if(l_rule_type not in (ame_util.listModRuleType
1168 ,ame_util.substitutionRuleType
1169 ,ame_util.productionRuleType
1170 ,ame_util.combinationRuleType)) then
1171 if l_approver_category is null then
1172 l_approver_category := ame_util.approvalApproverCategory;
1173 end if;
1174 elsif l_rule_type = ame_util.combinationRuleType and not ame_rule_utility_pkg.is_LM_comb_rule(p_rule_id) then
1175 if l_approver_category is null then
1176 l_approver_category := ame_util.approvalApproverCategory;
1177 end if;
1178 elsif l_approver_category is not null then
1179 fnd_message.set_name('PER','AME_400744_APPR_CAT_NOT_NULL');
1180 hr_multi_message.add(p_associated_column1 => 'APPROVER_CATEGORY');
1181 end if;
1182 end if;
1183
1184 --
1185 -- Check that priority is defined, if enabled for this rule type for this transaction type
1186 --
1187 l_variable_value := ame_util.getConfigVar(
1188 variableNameIn => ame_util.rulePriorityModesConfigVar
1189 ,applicationIdIn => p_application_id);
1190 if(l_rule_type = ame_util.combinationRuleType) then
1191 priority := substrb(l_variable_value, 1, (instr(l_variable_value,':',1,1) -1));
1192 elsif(l_rule_type = ame_util.authorityRuleType) then
1193 priority := substrb(l_variable_value,
1194 (instr(l_variable_value,':',1,1) +1),
1195 (instr(l_variable_value,':',1,2) -
1196 (instr(l_variable_value,':',1,1) +1)));
1197 elsif(l_rule_type = ame_util.exceptionRuleType) then
1198 priority := substrb(l_variable_value,
1199 (instr(l_variable_value,':',1,2) +1),
1200 (instr(l_variable_value,':',1,3) -
1201 (instr(l_variable_value,':',1,2) +1)));
1202 elsif(l_rule_type = ame_util.listModRuleType) then
1203 priority := substrb(l_variable_value,
1204 (instr(l_variable_value,':',1,3) +1),
1205 (instr(l_variable_value,':',1,4) -
1206 (instr(l_variable_value,':',1,3) +1)));
1207 elsif(l_rule_type = ame_util.substitutionRuleType) then
1208 priority := substrb(l_variable_value,
1209 (instr(l_variable_value,':',1,4) +1),
1210 (instr(l_variable_value,':',1,5) -
1211 (instr(l_variable_value,':',1,4) +1)));
1212 elsif(l_rule_type = ame_util.preListGroupRuleType) then
1213 priority := substrb(l_variable_value,
1214 (instr(l_variable_value,':',1,5) +1),
1215 (instr(l_variable_value,':',1,6) -
1216 (instr(l_variable_value,':',1,5) +1)));
1217 elsif(l_rule_type = ame_util.postListGroupRuleType) then
1218 priority := substrb(l_variable_value,
1219 (instr(l_variable_value,':',1,6) +1),
1220 (instr(l_variable_value,':',1,7) -
1221 (instr(l_variable_value,':',1,6) +1)));
1222 elsif(l_rule_type = ame_util.productionRuleType) then
1223 priority := substrb(l_variable_value,
1224 (instr(l_variable_value,':',1,7) +1));
1225 end if;
1226 if(priority <> ame_util.disabledRulePriority and p_priority is null) then
1227 fnd_message.set_name('PER','AME_400707_INVALID_PRIORITY');
1228 hr_multi_message.add(p_associated_column1 => 'PRIORITY');
1229 end if;
1230 --
1231 -- Check that the rule usage does not overlap with existing rule usage dates
1232 -- for the same application ID
1233 l_overlapping_usage := checkRuleUsageExists(p_application_id => p_application_id
1234 ,p_rule_id => p_rule_id
1235 ,p_rlu_start_date => p_start_date
1236 ,p_rlu_end_date => p_end_date
1237 ,p_effective_date => l_effective_date
1238 ,p_priority => p_priority );
1239 if l_overlapping_usage = 1 then
1240 fnd_message.set_name('PER','AME_400327_RULE_USG_EXST_LIFE');
1241 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
1242 elsif l_overlapping_usage = 2 then
1243 fnd_message.set_name('PER','AME_400328_RULE_USG_DIFF_PRIOR');
1244 hr_multi_message.add (p_associated_column1 =>'RULE_ID');
1245 elsif l_overlapping_usage = 3 then
1246 fnd_message.set_name('PER','AME_400329_RULE_USG_OVER_LIFE');
1247 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
1248 end if;
1249 -- insert the row in ame_rule_usages
1250 if l_rlu_start_date is null then
1251 l_rlu_start_date := l_effective_date;
1252 end if;
1253 if l_rlu_end_date is null then
1254 l_rlu_end_date := ame_utility_pkg.endOfTime;
1255 else
1256 l_rlu_end_date := trunc(l_rlu_end_date);
1257 end if;
1258 ame_rlu_ins.ins(p_rule_id => p_rule_id
1259 ,p_item_id => p_application_id
1260 ,p_effective_date => l_effective_date
1261 ,p_approver_category => l_approver_category
1262 ,p_priority => p_priority
1263 ,p_object_version_number => l_rlu_object_version_number
1264 ,p_start_date => l_rlu_start_date
1265 ,p_end_date => l_rlu_end_date
1266 );
1267 --
1268 -- Check if the start date and end date for the rule has changed
1269 --
1270
1271 -- if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) and
1272 l_create_ame_rule_call := true;
1273 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,'AME_RULE_API.CREATE_AME_RULE' ||fnd_global.local_chr(ascii_chr => 10)) = 0)then
1274 l_create_ame_rule_call := false;
1275 end if;
1276 if not l_create_ame_rule_call and not l_swi_call then
1277 fetchNewRuleDates(p_rule_id => p_rule_id
1278 ,p_rlu_start_date => l_rlu_start_date
1279 ,p_rlu_end_date => l_rlu_end_date
1280 ,p_rul_start_date => l_rul_start_date
1281 ,p_rul_end_date => l_rul_end_date
1282 ,p_date_changed => l_date_changed
1283 ) ;
1284 if l_date_changed = 'Y' then
1285 --
1286 -- date has changed, update the dates for rules, condition usages and action usages
1287 --
1288 -- actions usages
1289 for tempActions in getActions loop
1290 l_acu_object_version_number := tempActions.object_version_number;
1291 l_acu_start_date := tempActions.start_date;
1292 l_acu_end_date := tempActions.end_date;
1293 ame_acu_upd.upd(p_rule_id => p_rule_id
1294 ,p_datetrack_mode => hr_api.g_update
1295 ,p_action_id => tempActions.action_id
1296 ,p_effective_date => l_effective_date
1297 ,p_object_version_number => l_acu_object_version_number
1298 ,p_start_date => l_rul_start_date
1299 ,p_end_date => l_rul_end_date
1300 );
1301 end loop;
1302 -- condition usages
1303 for tempConditions in getConditions loop
1304 l_cnu_object_version_number := tempConditions.object_version_number;
1305 l_cnu_start_date := tempConditions.start_date;
1306 l_cnu_end_date := tempConditions.end_date;
1307 ame_cnu_upd.upd(p_rule_id => p_rule_id
1308 ,p_datetrack_mode => hr_api.g_update
1309 ,p_condition_id => tempConditions.condition_id
1310 ,p_effective_date => l_effective_date
1311 ,p_object_version_number => l_cnu_object_version_number
1312 ,p_start_date => l_rul_start_date
1313 ,p_end_date => l_rul_end_date
1314 );
1315 end loop;
1316 -- rules
1317 ame_rul_upd.upd(p_rule_id => p_rule_id
1318 ,p_datetrack_mode => hr_api.g_update
1319 ,p_effective_date => l_effective_date
1320 ,p_object_version_number => l_rul_object_version_number
1321 ,p_start_date => l_rul_start_date
1322 ,p_end_date => l_rul_end_date
1323 );
1324 end if;
1325 end if;
1326 --
1327 -- Update the attribute usage counts
1328 --
1329 for tempAttributeUsages in getAttributeUsages loop
1330 l_atu_object_version_number := tempAttributeUsages.object_version_number;
1331 l_atu_start_date := tempAttributeUsages.start_date;
1332 l_atu_end_date := tempAttributeUsages.end_date;
1333 ame_attribute_api.updateUseCount(p_attribute_id => tempAttributeUsages.attribute_id
1334 ,p_application_id => p_application_id
1335 ,p_atu_object_version_number => l_atu_object_version_number);
1336 end loop;
1337 -- update the use count of req attributes
1338 for tempAttribute in getReqAttributes loop
1339 for tempAttributeUsages in getAttributeUsages2(p_attribute_id => tempAttribute.attribute_id) loop
1340 l_atu_object_version_number := tempAttributeUsages.object_version_number;
1341 ame_attribute_api.updateUseCount(p_attribute_id => tempAttributeUsages.attribute_id
1342 ,p_application_id => p_application_id
1343 ,p_atu_object_version_number => l_atu_object_version_number);
1344 end loop;
1345 end loop;
1346 --
1347 -- Call After Process User Hook
1348 --
1349 begin
1350 ame_rule_bk2.create_ame_rule_usage_a
1351 (p_rule_id => p_rule_id
1352 ,p_application_id => p_application_id
1353 ,p_priority => p_priority
1354 ,p_approver_category => l_approver_category
1355 ,p_object_version_number => l_rlu_object_version_number
1356 ,p_start_date => l_rlu_start_date
1357 ,p_end_date => l_rlu_end_date
1358 );
1359 exception
1360 when hr_api.cannot_find_prog_unit then
1361 hr_api.cannot_find_prog_unit_error
1362 (p_module_name => 'create_ame_rule_usage'
1363 ,p_hook_type => 'AP'
1364 );
1365 end;
1366 --
1367 -- When in validation only mode raise the Validate_Enabled exception
1368 --
1369 if p_validate then
1370 raise hr_api.validate_enabled;
1371 end if;
1372 --
1373 -- Set all IN OUT and OUT parameters with out values
1374 --
1375 p_object_version_number := l_rlu_object_version_number;
1376 p_start_date := l_rlu_start_date;
1377 p_end_date := l_rlu_end_date;
1378 --
1379 hr_utility.set_location(' Leaving:'||l_proc, 70);
1380 exception
1381 when hr_api.validate_enabled then
1382 --
1383 -- As the Validate_Enabled exception has been raised
1384 -- we must rollback to the savepoint
1385 --
1386 rollback to create_ame_rule_usage;
1387 --
1388 -- Reset IN OUT parameters and set OUT parameters
1389 -- (Any key or derived arguments must be set to null
1390 -- when validation only mode is being used.)
1391 --
1392 p_object_version_number := null;
1393 p_start_date := null;
1394 p_end_date := null;
1395 hr_utility.set_location(' Leaving:'||l_proc, 80);
1396 when others then
1397 --
1398 -- A validation or unexpected error has occured
1399 --
1400 rollback to create_ame_rule_usage;
1401 --
1402 -- Reset IN OUT parameters and set all
1403 -- OUT parameters, including warnings, to null
1404 --
1405 p_object_version_number := null;
1406 p_start_date := null;
1407 p_end_date := null;
1408 hr_utility.set_location(' Leaving:'||l_proc, 90);
1409 raise;
1410 end create_ame_rule_usage;
1411 --
1412 -- ----------------------------------------------------------------------------
1413 -- |------------------<create_ame_condition_to_rule>--------------------------|
1414 -- ----------------------------------------------------------------------------
1415 --
1416 procedure create_ame_condition_to_rule
1417 (p_validate in boolean default false
1418 ,p_rule_id in number
1419 ,p_condition_id in number
1420 ,p_object_version_number out nocopy number
1421 ,p_start_date out nocopy date
1422 ,p_end_date out nocopy date
1423 ,p_effective_date in date default null
1424 ) is
1425 --
1426 -- Declare cursors and local variables
1427 --
1428 l_proc varchar2(72) := g_package||'create_ame_rule_usage';
1429 l_dummy varchar2(10);
1430 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
1431 l_effective_date date;
1432 l_condition_type ame_conditions.condition_type%type;
1433 l_attribute_id number;
1434 l_swi_call boolean;
1435 cursor getConditionDetails is
1436 select condition_type
1437 ,attribute_id
1438 from ame_conditions
1439 where condition_id = p_condition_id
1440 and l_effective_date between start_date
1441 and nvl(end_date - ame_util.oneSecond,l_effective_date);
1442 cursor getRuleDetails is
1443 select rule_type
1444 ,start_date
1445 ,end_date
1446 ,item_class_id
1447 from ame_rules
1448 where rule_id = p_rule_id
1449 and (l_effective_date between start_date
1450 and nvl(end_date - ame_util.oneSecond,l_effective_date) or
1451 (l_effective_date < start_date
1452 and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1453 cursor getApplications is
1454 select item_id
1455 from ame_rule_usages
1456 where rule_id = p_rule_id
1457 and (l_effective_date between start_date
1458 and nvl(end_date - ame_util.oneSecond,l_effective_date ) or
1459 (l_effective_date < start_date
1460 and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1461 cursor getAttributeUsages(p_application_id in integer) is
1462 select use_count
1463 ,start_date
1464 ,end_date
1465 ,object_version_number
1466 from ame_attribute_usages
1467 where attribute_id = l_attribute_id
1468 and l_effective_date between start_date
1469 and nvl(end_date - ame_util.oneSecond,l_effective_date )
1470 and application_id = p_application_id;
1471 cursor getApplicationName(applicationIdIn in integer)is
1472 select application_name
1473 from ame_calling_apps
1474 where application_id = applicationIdIn
1475 and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
1476 cursor getApplicationIds(p_rule_id in integer) is
1477 select distinct item_id
1478 from ame_rule_usages
1479 where rule_id = p_rule_id
1480 and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
1481 or
1482 (sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
1483 );
1484 --+
1485 l_condition_count number;
1486 l_rule_type ame_rules.rule_type%type;
1487 l_item_class_id number;
1488 l_application_id number;
1489 l_overlapping_usage number;
1490 l_rlu_object_version_number number;
1491 l_atu_object_version_number number;
1492 l_atu_start_date date;
1493 l_atu_end_date date;
1494 l_condition_found boolean;
1495 l_rul_object_version_number number;
1496 l_rul_start_date date;
1497 l_rul_end_date date;
1498 l_cnu_object_version_number number;
1499 l_cnu_start_date date;
1500 l_cnu_end_date date;
1501 l_use_count number := 0;
1502 actionIdList ame_util.idList;
1503 conditionIdList ame_util.idList;
1504 applicationName ame_calling_apps.application_name%type;
1505 attributeName ame_attributes.name%type;
1506 appIdList ame_util.idList;
1507 lm_count number;
1508 begin
1509 hr_utility.set_location('Entering:'|| l_proc, 10);
1510 --
1511 -- Issue a savepoint
1512 --
1513 savepoint create_ame_condition_to_rule;
1514 --
1515 -- Call Before Process User Hook
1516 --
1517 begin
1518 ame_rule_bk6.create_ame_condition_to_rule_b
1519 (p_rule_id => p_rule_id
1520 ,p_condition_id => p_condition_id
1521 );
1522 exception
1523 when hr_api.cannot_find_prog_unit then
1524 hr_api.cannot_find_prog_unit_error
1525 (p_module_name => 'create_ame_condition_to_rule'
1526 ,p_hook_type => 'BP'
1527 );
1528 end;
1529 --
1530 -- Process Logic
1531 --
1532 -- Set the effective date to the sysdate
1533 l_effective_date := sysdate;
1534
1535 l_swi_call := true;
1536 ame_rule_utility_pkg.checkRuleId(p_rule_id => p_rule_id);
1537 ame_rule_utility_pkg.checkConditionId(p_condition_id => p_condition_id);
1538 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
1539 ame_rule_utility_pkg.chk_rule_and_item_class(p_rule_id => p_rule_id
1540 ,p_condition_id => p_condition_id);
1541 l_swi_call := false;
1542 end if;
1543
1544 -- If the call is from UI, then set the l_effective_date to p_effective_date
1545 if l_swi_call and p_effective_date is not null then
1546 l_effective_date := p_effective_date;
1547 end if;
1548
1549 open getApplicationIds(p_rule_id => p_rule_id);
1550 fetch getApplicationIds
1551 bulk collect into appIdList;
1552 for i in 1..appIdList.count loop
1553 if not ame_rule_utility_pkg.is_condition_allowed(p_application_id => appIdList(i)
1554 ,p_condition_id => p_condition_id) then
1555 fnd_message.set_name('PER','AME_400738_COND_NOT_IN_APP');
1556 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
1557 end if;
1558 end loop;
1559 --
1560 -- Fetch the condition details
1561 --
1562 open getConditionDetails;
1563 fetch getConditionDetails
1564 into l_condition_type
1565 ,l_attribute_id ;
1566 if getConditionDetails%notfound then
1567 fnd_message.set_name('PER','AME_400494_INVALID_CONDITION');
1568 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
1569 end if;
1570 close getConditionDetails;
1571 --
1572 -- Fetch the rule details
1573 --
1574 open getRuleDetails;
1575 fetch getRuleDetails
1576 into l_rule_type
1577 ,l_rul_start_date
1578 ,l_rul_end_date
1579 ,l_item_class_id;
1580 if getRuleDetails%notfound then
1581 fnd_message.set_name('PER','AME_400480_INV_RULE_ID');
1582 hr_multi_message.add(p_associated_column1 => 'RULE_ID');
1583 end if;
1584 close getRuleDetails;
1585 --+
1586 -- Check that the condition type of the condition is allowed for the Rule Type.
1587 --+
1588 -- Condition Type ---> Ordinary Exception List Modification
1589 -- Rule Type
1590 -- | List Creation Rule Y N N
1591 -- | Exception Rule Y Y N
1592 -- V Pre-Approver Rule Y N N
1593 -- Post-Approver Rule Y N N
1594 -- List Modification Rule Y N Y
1595 -- Substitution Rule Y N Y
1596 -- Production Rule Y N N
1597 --+
1598 if l_condition_type = ame_util.exceptionConditionType then
1599 if l_rule_type <> ame_util.exceptionRuleType then
1600 fnd_message.set_name('PER','AME_400726_NO_EXC_CON_IN_RULE');
1601 hr_multi_message.add(p_associated_column1 =>'CONDITION_ID');
1602 end if;
1603 elsif l_condition_type = ame_util.listModConditionType then
1604 if l_rule_type not in (ame_util.listModRuleType
1605 ,ame_util.substitutionRuleType
1606 ,ame_util.combinationRuleType) then
1607 fnd_message.set_name('PER','AME_400727_NO_LM_CON_IN_RULE');
1608 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
1609 if ame_rule_utility_pkg.rule_conditions_count(p_rule_id => p_rule_id) > 0 then
1610 fnd_message.set_name('PER','AME_400733_EXTRA_LM_CON');
1611 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
1612 end if;
1613 end if;
1614 end if;
1615 --+
1616 --
1617 -- Check that there is no other rule with the same combination of actions and conditions
1618 -- existing.
1619 --
1620 -- Fetch conditions and actions for rule
1621
1622 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
1623 --+
1624 --+ Check if the rule is LM/SUB/LM COMB and error out if these rules already have an LM Condition.
1625 --+
1626 select count(*)
1627 into lm_count
1628 from ame_rules rul
1629 ,ame_condition_usages cnu
1630 ,ame_conditions cnd
1631 where rul.rule_id = p_rule_id
1632 and cnu.rule_id = rul.rule_id
1633 and cnd.condition_id = cnu.condition_id
1634 and cnd.condition_type = 'post'
1635 and sysdate between cnd.start_date and nvl(cnd.end_date - (1/86400),sysdate)
1636 and ((sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate))
1637 or
1638 (sysdate < rul.start_date and rul.start_date < nvl(rul.end_date, rul.start_date + (1/86400)))
1639 )
1640 and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
1641 or
1642 (sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
1643 );
1644 if lm_count > 0 then
1645 if(l_rule_type in(ame_util.listModRuleType
1646 ,ame_util.substitutionRuleType
1647 ,ame_util.combinationRuleType)
1648 and l_condition_type = ame_util.listModConditionType) then
1649 fnd_message.set_name('PER','AME_400385_RULE_LM');
1650 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
1651 end if;
1652 end if;
1653 getConditionIds(ruleIdIn => p_rule_id,
1654 conditionIdListOut => conditionIdList);
1655 getActionIds(ruleIdIn => p_rule_id,
1656 actionIdListOut => actionIdList);
1657 -- check condition does not exist in rule already
1658 l_condition_found := false;
1659 for i in 1..conditionIdList.count loop
1660 if p_condition_id = conditionIdList(i) then
1661 l_condition_found := true;
1662 end if;
1663 end loop;
1664 if l_condition_found then
1665 fnd_message.set_name('PER','AME_400728_DUP_CON_IN_RULE');
1666 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
1667 end if;
1668 -- add this condition_id to end of list
1669 l_condition_count := conditionIdList.count;
1670 conditionIdList(l_condition_count+1) := p_condition_id;
1671 /*
1672 if ruleExists(p_rule_id => p_rule_id
1673 ,p_rule_type => l_rule_type
1674 ,p_item_class_id => l_item_class_id
1675 ,p_effective_date => l_effective_date
1676 ,p_conditions_list => conditionIdList
1677 ,p_actions_list => actionIdList
1678 ) then
1679 fnd_message.set_name('PER','AME_400212_RUL_PROP_EXISTS');
1680 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
1681 end if;
1682 */
1683 end if;
1684
1685 --+
1686 ame_rule_utility_pkg.checkAllApplications(ruleIdIn => p_rule_id
1687 ,conditionIdIn => p_condition_id);
1688 --+
1689 --
1690 -- Calculate Condition Usage start and end date.
1691 --
1692 if l_effective_date > l_rul_start_date then
1693 l_cnu_start_date := l_effective_date;
1694 else
1695 l_cnu_start_date := l_rul_start_date;
1696 end if;
1697 l_cnu_end_date := l_rul_end_date;
1698 --
1699 -- insert the row in ame_condition_usages
1700 --
1701 ame_cnu_ins.ins(p_rule_id => p_rule_id
1702 ,p_condition_id => p_condition_id
1703 ,p_effective_date => l_effective_date
1704 ,p_object_version_number => l_cnu_object_version_number
1705 ,p_start_date => l_cnu_start_date
1706 ,p_end_date => l_cnu_end_date
1707 );
1708 --+
1709 -- For all conditions except List modification conditions,
1710 -- check that an attribute usage exists for the attribute this condition is based on
1711 -- for all the transaction type's using this rule.
1712 -- Update the attribute usage counts
1713 --+
1714 if l_condition_type <> ame_util.listModConditionType then
1715 for tempApplications in getApplications loop
1716 l_application_id := tempApplications.item_id;
1717 open getAttributeUsages(p_application_id => l_application_id) ;
1718 fetch getAttributeUsages
1719 into l_use_count
1720 ,l_atu_start_date
1721 ,l_atu_end_date
1722 ,l_atu_object_version_number;
1723 if getAttributeUsages%notfound then
1724 open getApplicationName(applicationIdIn => l_application_id);
1725 fetch getApplicationName into applicationName;
1726 close getApplicationName;
1727 ame_rule_utility_pkg.getAttributeName(p_attribute_id => l_attribute_id
1728 ,p_attribute_name_out => attributeName);
1729 fnd_message.set_name('PER','AME_400149_ATT_TTY_NO_USAGE');
1730 fnd_message.set_token('ATTRIBUTE',attributeName);
1731 fnd_message.set_token('APPLICATION',applicationName);
1732 hr_multi_message.add(p_associated_column1 => 'RULE_ID');
1733 end if;
1734 close getAttributeUsages;
1735 ame_attribute_api.updateUseCount(p_attribute_id => l_attribute_id
1736 ,p_application_id => tempApplications.item_id
1737 ,p_atu_object_version_number => l_atu_object_version_number);
1738 end loop;
1739 end if;
1740 --
1741 -- Call After Process User Hook
1742 --
1743 begin
1744 ame_rule_bk6.create_ame_condition_to_rule_a
1745 (p_rule_id => p_rule_id
1746 ,p_condition_id => p_condition_id
1747 ,p_object_version_number => l_cnu_object_version_number
1748 ,p_start_date => l_cnu_start_date
1749 ,p_end_date => l_cnu_end_date
1750 );
1751 exception
1752 when hr_api.cannot_find_prog_unit then
1753 hr_api.cannot_find_prog_unit_error
1754 (p_module_name => 'create_ame_condition_to_rule'
1755 ,p_hook_type => 'AP'
1756 );
1757 end;
1758 --
1759 -- When in validation only mode raise the Validate_Enabled exception
1760 --
1761 if p_validate then
1762 raise hr_api.validate_enabled;
1763 end if;
1764 --
1765 -- Set all IN OUT and OUT parameters with out values
1766 --
1767 p_object_version_number := l_cnu_object_version_number;
1768 p_start_date := l_cnu_start_date;
1769 p_end_date := l_cnu_end_date;
1770 --
1771 hr_utility.set_location(' Leaving:'||l_proc, 70);
1772 exception
1773 when hr_api.validate_enabled then
1774 --
1775 -- As the Validate_Enabled exception has been raised
1776 -- we must rollback to the savepoint
1777 --
1778 rollback to create_ame_condition_to_rule;
1779 --
1780 -- Reset IN OUT parameters and set OUT parameters
1781 -- (Any key or derived arguments must be set to null
1782 -- when validation only mode is being used.)
1783 --
1784 p_object_version_number := null;
1785 p_start_date := null;
1786 p_end_date := null;
1787 hr_utility.set_location(' Leaving:'||l_proc, 80);
1788 when others then
1789 --
1790 -- A validation or unexpected error has occured
1791 --
1792 rollback to create_ame_condition_to_rule;
1793 --
1794 -- Reset IN OUT parameters and set all
1795 -- OUT parameters, including warnings, to null
1796 --
1797 p_object_version_number := null;
1798 p_start_date := null;
1799 p_end_date := null;
1800 hr_utility.set_location(' Leaving:'||l_proc, 90);
1801 raise;
1802 end create_ame_condition_to_rule;
1803 --
1804 -- ----------------------------------------------------------------------------
1805 -- |------------------<create_ame_action_to_rule>--------------------------|
1806 -- ----------------------------------------------------------------------------
1807 --
1808 procedure create_ame_action_to_rule
1809 (p_validate in boolean default false
1810 ,p_rule_id in number
1811 ,p_action_id in number
1812 ,p_object_version_number out nocopy number
1813 ,p_start_date out nocopy date
1814 ,p_end_date out nocopy date
1815 ,p_effective_date in date default null
1816 ) is
1817 --
1818 -- Declare cursors and local variables
1819 --
1820 l_proc varchar2(72) := g_package||'create_ame_action_to_rule';
1821 l_dummy varchar2(10);
1822 l_action_type_id ame_action_types.action_type_id%type;
1823 l_attribute_id number;
1824 l_effective_date date;
1825 --+
1826 cursor getActionDetails is
1827 select aatu.action_type_id
1828 ,aatu.rule_type
1829 from ame_action_type_usages aatu
1830 ,ame_actions aa
1831 where aa.action_id = p_action_id
1832 and l_effective_date between aa.start_date
1833 and nvl(aa.end_date - ame_util.oneSecond, l_effective_date)
1834 and aa.action_type_id = aatu.action_type_id
1835 and l_effective_date between aatu.start_date
1836 and nvl(aatu.end_date - ame_util.oneSecond, l_effective_date);
1837 --+
1838 cursor getRuleDetails is
1839 select rule_type, start_date, end_date, item_class_id
1840 from ame_rules
1841 where rule_id = p_rule_id
1842 and ( l_effective_date between start_date and
1843 nvl(end_date - ame_util.oneSecond,l_effective_date ) or
1844 (l_effective_date < start_date and
1845 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
1846 --+
1847 cursor getReqAttributes(p_application_id in integer
1848 ,p_action_type_id in integer) is
1849 select attribute_id
1850 from ame_mandatory_attributes ama
1851 where ama.attribute_id not in (select attribute_id
1852 from ame_attribute_usages
1853 where application_id = p_application_id
1854 and l_effective_date between start_date
1855 and nvl(end_date - ame_util.oneSecond, l_effective_date))
1856 and l_effective_date between ama.start_date
1857 and nvl(ama.end_date - ame_util.oneSecond, l_effective_date)
1858 and action_type_id = p_action_type_id;
1859 --+
1860 cursor getReqAttributeIds(actionIdIn in integer
1861 ,ruleIdIn in integer) is
1862 select man.attribute_id
1863 from ame_mandatory_attributes man
1864 ,ame_action_usages acu
1865 ,ame_actions act
1866 where man.action_type_id = act.action_type_id
1867 and acu.action_id = act.action_id
1868 and acu.action_id = actionIdIn
1869 and acu.rule_id = ruleIdIn
1870 and l_effective_date between man.start_date and nvl(man.end_date - ame_util.oneSecond, l_effective_date)
1871 and l_effective_date between act.start_date and nvl(act.end_date - ame_util.oneSecond, l_effective_date)
1872 and ((l_effective_date between acu.start_date and nvl(acu.end_date - ame_util.oneSecond, l_effective_date))
1873 or
1874 (l_effective_date < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + ame_util.oneSecond))
1875 );
1876 --+
1877 cursor getApplications(ruleIdIn in integer)is
1878 select item_id
1879 from ame_rule_usages
1880 where rule_id = ruleIdIn
1881 and (l_effective_date between start_date
1882 and nvl(end_date - ame_util.oneSecond, l_effective_date)
1883 or
1884 (l_effective_date < start_date
1885 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
1886 --+
1887 cursor getAttributeUsages(attributeIdIn in integer
1888 ,applicationIdIn in integer) is
1889 select application_id
1890 ,use_count
1891 ,start_date
1892 ,end_date
1893 ,object_version_number
1894 from ame_attribute_usages
1895 where attribute_id = attributeIdIn
1896 and application_id = applicationIdIn
1897 and l_effective_date between start_date
1898 and nvl(end_date - ame_util.oneSecond,l_effective_date);
1899 --+
1900 cursor getApplicationIds(p_rule_id in integer) is
1901 select distinct item_id
1902 from ame_rule_usages
1903 where rule_id = p_rule_id
1904 and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
1905 or
1906 (sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
1907 );
1908 l_action_found boolean;
1909 l_allowAllApproverTypes varchar2(30);
1910 l_allowProduction varchar2(30);
1911 l_action_type_name ame_action_types.name%type;
1912 l_atu_object_version_number integer;
1913 l_count number;
1914 l_action_rule_type number;
1915 l_item_class_id number;
1916 l_application_id number;
1917 l_overlapping_usage number;
1918 l_rul_object_version_number number;
1919 l_rul_start_date date;
1920 l_rul_end_date date;
1921 l_rule_type ame_rules.rule_type%type;
1922 l_acu_object_version_number number;
1923 l_acu_start_date date;
1924 l_acu_end_date date;
1925 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
1926 actionIdList ame_util.idList;
1927 conditionIdList ame_util.idList;
1928 appIdList ame_util.idList;
1929 l_result number(2);
1930 l_head_item_class_id ame_item_classes.item_class_id%type;
1931 l_aty_name ame_action_types.name%type;
1932 l_swi_call boolean;
1933 begin
1934 hr_utility.set_location('Entering:'|| l_proc, 10);
1935 --
1936 -- Issue a savepoint
1937 --
1938 savepoint create_ame_action_to_rule;
1939 --
1940 -- Call Before Process User Hook
1941 --
1942 begin
1943 ame_rule_bk7.create_ame_action_to_rule_b
1944 (p_rule_id => p_rule_id
1945 ,p_action_id => p_action_id
1946 );
1947 exception
1948 when hr_api.cannot_find_prog_unit then
1949 hr_api.cannot_find_prog_unit_error
1950 (p_module_name => 'create_ame_action_to_rule'
1951 ,p_hook_type => 'BP'
1952 );
1953 end;
1954 --
1955 -- Process Logic
1956 --
1957 -- Set the effective date to the sysdate
1958 l_effective_date := sysdate;
1959
1960 -- Check the rule type of the rule. Ensure that this action is valid for this rule type.
1961 open getActionDetails;
1962 fetch getActionDetails
1963 into l_action_type_id
1964 ,l_action_rule_type;
1965 if getActionDetails%notfound then
1966 fnd_message.set_name('PER','AME_400736_INV_ACTION_ID');
1967 hr_multi_message.add(p_associated_column1 => 'ACTION_ID');
1968 end if;
1969 close getActionDetails;
1970 --
1971 -- Fetch the rule details
1972 --
1973 open getRuleDetails;
1974 fetch getRuleDetails
1975 into l_rule_type
1976 ,l_rul_start_date
1977 ,l_rul_end_date
1978 ,l_item_class_id;
1979 if getRuleDetails%notfound then
1980 fnd_message.set_name('PER','AME_400729_INV_RULE_ID');
1981 hr_multi_message.add(p_associated_column1 => 'RULE_ID');
1982 end if;
1983 close getRuleDetails;
1984 --+
1985 --+ Check if the action is of line-item job-level chains of authority action type.
1986 --+
1987 select name
1988 into l_aty_name
1989 from ame_action_types
1990 where action_type_id = l_action_type_id
1991 and sysdate between start_date and nvl(end_date - (1/84600),sysdate);
1992 --+
1993 if l_aty_name = 'line-item job-level chains of authority' then
1994 select item_class_id
1995 into l_head_item_class_id
1996 from ame_item_classes
1997 where name = 'header'
1998 and sysdate between start_date and nvl(end_date - (1/84600),sysdate);
1999 if l_head_item_class_id <> l_item_class_id then
2000 fnd_message.set_name('PER','AME_400449_INV_ACT_TYP_CHOSEN');
2001 hr_multi_message.add(p_associated_column1 => 'ITEM_CLASS_ID');
2002 end if;
2003 end if;
2004 --+
2005 --+ Checks to be done for all transaction types using this rule
2006 --+
2007 ame_rule_utility_pkg.chekActionForAllApplications(ruleIdIn => p_rule_id
2008 ,actionIdIn => p_action_id);
2009 --+
2010 -- Fetch conditions and actions for rule
2011 l_swi_call := true;
2012 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
2013 --+
2014 --+ Check Rule Id.
2015 --+
2016 l_swi_call := false;
2017 ame_rule_utility_pkg.checkRuleId(p_rule_id => p_rule_id);
2018 --+
2019 --+ Check Action Id.
2020 --+
2021 ame_rule_utility_pkg.checkActionId(p_action_id => p_action_id);
2022 --+
2023 --+ Get all the transaction types using this rule.
2024 --+
2025 open getApplicationIds(p_rule_id => p_rule_id);
2026 fetch getApplicationIds
2027 bulk collect into appIdList;
2028 --+
2029 --+ Check if this action is valid for all these transaction types.
2030 --+
2031 for i in 1..appIdList.count loop
2032 l_result := ame_rule_utility_pkg.is_action_allowed(p_application_id => appIdList(i)
2033 ,p_action_id => p_action_id);
2034 if l_result = ame_rule_utility_pkg.ActionNotAllowed then
2035 fnd_message.set_name('PER','AME_400735_ACT_NOT_IN_APP');
2036 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
2037 elsif l_result = ame_rule_utility_pkg.GroupNotAllowed then
2038 fnd_message.set_name('PER','AME_400744_GRP_NOT_IN_APP');
2039 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
2040 elsif l_result = ame_rule_utility_pkg.PosActionNotAllowed then
2041 fnd_message.set_name('PER','AME_400770_POS_APR_NOT_IN_APP');
2042 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
2043 end if;
2044 --+
2045 --+ Check the Rule Type and action combination.
2046 --+
2047 if not ame_rule_utility_pkg.chk_rule_type(p_rule_id => p_rule_id
2048 ,p_rule_type => l_rule_type
2049 ,p_action_rule_type => l_action_rule_type
2050 ,p_application_id => appIdList(i)
2051 ,p_allow_production_action => true) then
2052 fnd_message.set_name('PER','AME_400741_RULE_TYPE_MISMATCH');
2053 hr_multi_message.add(p_associated_column1 => 'RULE_TYPE');
2054 end if;
2055 if l_rule_type = ame_util.listModRuleType then
2056 if not ame_rule_utility_pkg.chk_lm_actions(p_rule_id => p_rule_id
2057 ,p_action_id => p_action_id) then
2058 fnd_message.set_name('PER','AME_400425_RULE_LM_RULE');
2059 hr_multi_message.add(p_associated_column1 => 'ACTION_ID');
2060 end if;
2061 end if;
2062 end loop;
2063 --added till here...
2064 getConditionIds(ruleIdIn => p_rule_id,
2065 conditionIdListOut => conditionIdList);
2066 getActionIds(ruleIdIn => p_rule_id,
2067 actionIdListOut => actionIdList);
2068 -- check action does not exist in rule already
2069 l_action_found := false;
2070 for i in 1..actionIdList.count loop
2071 if p_action_id = actionIdList(i) then
2072 l_action_found := true;
2073 end if;
2074 end loop;
2075 if l_action_found then
2076 fnd_message.set_name('PER','AME_400730_DUPLICATE_ACTION');
2077 hr_multi_message.add(p_associated_column1 => 'ACTION_ID');
2078 end if;
2079 -- add this condition_id to end of list
2080 l_count := actionIdList.count;
2081 actionIdList(l_count+1) := p_action_id;
2082 /*
2083 if ruleExists(p_rule_id => p_rule_id
2084 ,p_rule_type => l_rule_type
2085 ,p_item_class_id => l_item_class_id
2086 ,p_effective_date => l_effective_date
2087 ,p_conditions_list => conditionIdList
2088 ,p_actions_list => actionIdList
2089 ) then
2090 fnd_message.set_name('PER','AME_400212_RUL_PROP_EXISTS');
2091 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
2092 end if;
2093 */
2094 end if;
2095
2096 -- If the call is from UI, then set the l_effective_date to p_effective_date
2097 if l_swi_call and p_effective_date is not null then
2098 l_effective_date := p_effective_date;
2099 end if;
2100 --
2101 -- Calculate Action Usage start and end date.
2102 --
2103 if l_effective_date > l_rul_start_date then
2104 l_acu_start_date := l_effective_date;
2105 else
2106 l_acu_start_date := l_rul_start_date;
2107 end if;
2108 l_acu_end_date := l_rul_end_date;
2109 --
2110 -- insert the row in ame_action_usages
2111 --
2112 ame_acu_ins.ins(p_rule_id => p_rule_id
2113 ,p_action_id => p_action_id
2114 ,p_effective_date => l_effective_date
2115 ,p_object_version_number => l_acu_object_version_number
2116 ,p_start_date => l_acu_start_date
2117 ,p_end_date => l_acu_end_date
2118 );
2119 --
2120 for tempAttribute in getReqAttributeIds(actionIdIn => p_action_id
2121 ,ruleIdIn => p_rule_id) loop
2122 for tempApplication in getApplications(ruleIdIn => p_rule_id) loop
2123 for tempAttributeUsages in getAttributeUsages(attributeIdIn => tempAttribute.attribute_id
2124 ,applicationIdIn => tempApplication.item_id) loop
2125 l_atu_object_version_number := tempAttributeUsages.object_version_number;
2126 ame_attribute_api.updateUseCount(p_attribute_id => tempAttribute.attribute_id
2127 ,p_application_id => tempApplication.item_id
2128 ,p_atu_object_version_number => l_atu_object_version_number);
2129 end loop;
2130 end loop;
2131 end loop;
2132 --
2133 -- Call After Process User Hook
2134 --
2135 begin
2136 ame_rule_bk7.create_ame_action_to_rule_a
2137 (p_rule_id => p_rule_id
2138 ,p_action_id => p_action_id
2139 ,p_object_version_number => l_acu_object_version_number
2140 ,p_start_date => l_acu_start_date
2141 ,p_end_date => l_acu_end_date
2142 );
2143 exception
2144 when hr_api.cannot_find_prog_unit then
2145 hr_api.cannot_find_prog_unit_error
2146 (p_module_name => 'create_ame_action_to_rule'
2147 ,p_hook_type => 'AP'
2148 );
2149 end;
2150 --
2151 -- When in validation only mode raise the Validate_Enabled exception
2152 --
2153 if p_validate then
2154 raise hr_api.validate_enabled;
2155 end if;
2156 --
2157 -- Set all IN OUT and OUT parameters with out values
2158 --
2159 p_object_version_number := l_acu_object_version_number;
2160 p_start_date := l_acu_start_date;
2161 p_end_date := l_acu_end_date;
2162 --
2163 hr_utility.set_location(' Leaving:'||l_proc, 70);
2164 exception
2165 when hr_api.validate_enabled then
2166 --
2167 -- As the Validate_Enabled exception has been raised
2168 -- we must rollback to the savepoint
2169 --
2170 rollback to create_ame_action_to_rule;
2171 --
2172 -- Reset IN OUT parameters and set OUT parameters
2173 -- (Any key or derived arguments must be set to null
2174 -- when validation only mode is being used.)
2175 --
2176 p_object_version_number := null;
2177 p_start_date := null;
2178 p_end_date := null;
2179 hr_utility.set_location(' Leaving:'||l_proc, 80);
2180 when others then
2181 --
2182 -- A validation or unexpected error has occured
2183 --
2184 rollback to create_ame_action_to_rule;
2185 --
2186 -- Reset IN OUT parameters and set all
2187 -- OUT parameters, including warnings, to null
2188 --
2189 p_object_version_number := null;
2190 p_start_date := null;
2191 p_end_date := null;
2192 hr_utility.set_location(' Leaving:'||l_proc, 90);
2193 raise;
2194 end create_ame_action_to_rule;
2195
2196 --
2197 --
2198 -- ----------------------------------------------------------------------------
2199 -- |-----------------------< update_ame_rule >-------------------------------|
2200 -- ----------------------------------------------------------------------------
2201 --
2202 procedure update_ame_rule
2203 (p_validate in boolean default false
2204 ,p_language_code in varchar2 default hr_api.userenv_lang
2205 ,p_rule_id in number
2206 ,p_description in varchar2 default hr_api.g_varchar2
2207 ,p_object_version_number in out nocopy number
2208 ,p_start_date in out nocopy date
2209 ,p_end_date in out nocopy date
2210 ,p_effective_date in date default null
2211 ) is
2212 --
2213 -- Declare cursors and local variables
2214 --
2215 l_rul_object_version_number number;
2216 l_rul_start_date date;
2217 l_rul_end_date date;
2218 l_effective_date date;
2219 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
2220 l_proc varchar2(72) := g_package||'update_ame_rule';
2221 l_swi_call boolean;
2222 begin
2223 hr_utility.set_location('Entering:'|| l_proc, 10);
2224 --
2225 -- Issue a savepoint
2226 --
2227 savepoint update_ame_rule;
2228 --
2229 -- Call Before Process User Hook
2230 --
2231 begin
2232 ame_rule_bk3.update_ame_rule_b
2233 (p_rule_id => p_rule_id
2234 ,p_description => p_description
2235 ,p_object_version_number => p_object_version_number
2236 ,p_start_date => p_start_date
2237 ,p_end_date => p_end_date
2238 );
2239 exception
2240 when hr_api.cannot_find_prog_unit then
2241 hr_api.cannot_find_prog_unit_error
2242 (p_module_name => 'update_ame_rule'
2243 ,p_hook_type => 'BP'
2244 );
2245 end;
2246 --
2247 -- Process Logic
2248 --
2249 -- Set the effective date to the sysdate
2250
2251 l_effective_date := sysdate;
2252 l_rul_object_version_number := p_object_version_number;
2253 --+ Check Rule Id.
2254 ame_rule_utility_pkg.checkRuleId(p_rule_id => p_rule_id);
2255 if p_rule_id is null then
2256 fnd_message.set_name('PER', 'AME_400729_INV_RULE_ID');
2257 fnd_message.set_token('PROCEDURE', l_proc);
2258 fnd_message.set_token('STEP','5');
2259 fnd_message.raise_error;
2260 end if;
2261 --
2262 -- Check if the API is called from the SWI layer. If yes, update start and end dates for rules.
2263 -- Else only update Description.
2264 l_swi_call := true;
2265 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
2266 l_swi_call := false;
2267 -- If Description is null and call is not made from SWI layer, then raise an exception.
2268 if(p_description is null or
2269 p_description = hr_api.g_varchar2) then
2270 fnd_message.set_name('PER','AME_400731_NO_DESCRIPTION');
2271 hr_multi_message.add(p_associated_column1 => 'DESCRIPTION');
2272 end if;
2273 l_rul_start_date := null;
2274 l_rul_end_date := null;
2275 else
2276 l_rul_start_date := p_start_date;
2277 l_rul_end_date := p_end_date;
2278 end if;
2279 if l_swi_call and p_effective_date is not null then
2280 l_effective_date := p_effective_date;
2281 end if;
2282 ame_rul_upd.upd(p_effective_date => l_effective_date
2283 ,p_datetrack_mode => hr_api.g_update
2284 ,p_rule_id => p_rule_id
2285 ,p_object_version_number => l_rul_object_version_number
2286 ,p_description => p_description
2287 ,p_start_date => l_rul_start_date
2288 ,p_end_date => l_rul_end_date
2289 );
2290
2291 -- update data into TL tables
2292 if(p_description is not null or
2293 p_description <> hr_api.g_varchar2) then
2294 ame_rtl_upd.upd_tl(p_language_code => p_language_code
2295 ,p_rule_id => p_rule_id
2296 ,p_description => p_description
2297 );
2298 end if;
2299 --
2300 -- Call After Process User Hook
2301 --
2302 begin
2303 ame_rule_bk3.update_ame_rule_a
2304 (p_rule_id => p_rule_id
2305 ,p_description => p_description
2306 ,p_object_version_number => l_rul_object_version_number
2307 ,p_start_date => l_rul_start_date
2308 ,p_end_date => l_rul_end_date
2309 );
2310 exception
2311 when hr_api.cannot_find_prog_unit then
2312 hr_api.cannot_find_prog_unit_error
2313 (p_module_name => 'update_ame_rule'
2314 ,p_hook_type => 'AP'
2315 );
2316 end;
2317 --
2318 -- When in validation only mode raise the Validate_Enabled exception
2319 --
2320 if p_validate then
2321 raise hr_api.validate_enabled;
2322 end if;
2323 --
2324 -- Set all IN OUT and OUT parameters with out values
2325 --
2326 p_object_version_number := l_rul_object_version_number;
2327 p_start_date := l_rul_start_date;
2328 p_end_date := l_rul_end_date;
2329 --
2330 hr_utility.set_location(' Leaving:'||l_proc, 70);
2331 exception
2332 when app_exception.application_exception then
2333 if hr_multi_message.exception_add
2334 (p_associated_column1 => 'action_id') then
2335 hr_utility.set_location(' Leaving:'|| l_proc, 50);
2336 raise;
2337 end if;
2338 hr_utility.set_location(' Leaving:'|| l_proc, 60);
2339 when hr_api.validate_enabled then
2340 --
2341 -- As the Validate_Enabled exception has been raised
2342 -- we must rollback to the savepoint
2343 --
2344 rollback to update_ame_rule;
2345 --
2346 -- Reset IN OUT parameters and set OUT parameters
2347 -- (Any key or derived arguments must be set to null
2348 -- when validation only mode is being used.)
2349 --
2350 p_start_date := null;
2351 p_end_date := null;
2352 hr_utility.set_location(' Leaving:'||l_proc, 80);
2353 when others then
2354 --
2355 -- A validation or unexpected error has occured
2356 --
2357 rollback to update_ame_rule;
2358 --
2359 -- Reset IN OUT parameters and set all
2360 -- OUT parameters, including warnings, to null
2361 --
2362 p_start_date := null;
2363 p_end_date := null;
2364 hr_utility.set_location(' Leaving:'||l_proc, 90);
2365 raise;
2366 end update_ame_rule;
2367 --
2368 --
2369 -- ----------------------------------------------------------------------------
2370 -- |---------------------< update_ame_rule_usage >--------------------------|
2371 -- ----------------------------------------------------------------------------
2372 --
2373 procedure update_ame_rule_usage
2374 (p_validate in boolean default false
2375 ,p_rule_id in number
2376 ,p_application_id in number
2377 ,p_priority in number default hr_api.g_number
2378 ,p_approver_category in varchar2 default hr_api.g_varchar2
2379 ,p_old_start_date in date
2380 ,p_object_version_number in out nocopy number
2381 ,p_start_date in out nocopy date
2382 ,p_end_date in out nocopy date
2383 ,p_effective_date in date default null
2384 ) is
2385 --
2386 -- Declare cursors and local variables
2387 --
2388 l_date_changed varchar2(10);
2389 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
2390 l_overlapping_usage number;
2391 l_acu_object_version_number number;
2392 l_acu_start_date date;
2393 l_acu_end_date date;
2394 l_cnu_object_version_number number;
2395 l_cnu_start_date date;
2396 l_cnu_end_date date;
2397 l_rlu_object_version_number number;
2398 l_rlu_start_date date;
2399 l_rlu_end_date date;
2400 l_rul_object_version_number number;
2401 l_rul_start_date date;
2402 l_rul_end_date date;
2403 l_rul_start_date2 date;
2404 l_rul_end_date2 date;
2405 l_effective_date date;
2406 l_proc varchar2(72) := g_package||'update_ame_rule_usage';
2407 l_swi_call boolean;
2408 --+
2409 cursor getRuleDetails is
2410 select start_date
2411 ,end_date
2412 ,object_version_number
2413 from ame_rules
2414 where rule_id = p_rule_id
2415 and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
2416 or
2417 (l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
2418 --+
2419 cursor getActions is
2420 select action_id
2421 ,start_date
2422 ,end_date
2423 ,object_version_number
2424 from ame_action_usages
2425 where rule_id = p_rule_id
2426 and ((l_effective_date between start_date
2427 and nvl(end_date - ame_util.oneSecond, l_effective_date))
2428 or
2429 (l_effective_date < start_date
2430 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
2431 --+
2432 cursor getConditions is
2433 select condition_id
2434 ,start_date
2435 ,end_date
2436 ,object_version_number
2437 from ame_condition_usages
2438 where rule_id = p_rule_id
2439 and ((l_effective_date between start_date
2440 and nvl(end_date - ame_util.oneSecond, l_effective_date))
2441 or
2442 (l_effective_date < start_date
2443 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
2444 begin
2445 hr_utility.set_location('Entering:'|| l_proc, 10);
2446
2447 if(p_end_date is not null and TRUNC(p_end_date) = TRUNC(SYSDATE)) then
2448 delete_ame_rule_usage(p_validate => p_validate
2449 ,p_rule_id => p_rule_id
2450 ,p_application_id => p_application_id
2451 ,p_object_version_number => p_object_version_number
2452 ,p_start_date => p_start_date
2453 ,p_end_date => p_end_date
2454 );
2455 return;
2456 end if;
2457 --
2458 -- Issue a savepoint
2459 --
2460 savepoint update_ame_rule_usage;
2461 --
2462 -- Call Before Process User Hook
2463 --
2464 begin
2465 ame_rule_bk4.update_ame_rule_usage_b
2466 (p_rule_id => p_rule_id
2467 ,p_application_id => p_application_id
2468 ,p_priority => p_priority
2469 ,p_approver_category => p_approver_category
2470 ,p_old_start_date => p_old_start_date
2471 ,p_object_version_number => p_object_version_number
2472 ,p_start_date => p_start_date
2473 ,p_end_date => p_end_date
2474 );
2475 exception
2476 when hr_api.cannot_find_prog_unit then
2477 hr_api.cannot_find_prog_unit_error
2478 (p_module_name => 'update_ame_rule_usage'
2479 ,p_hook_type => 'BP'
2480 );
2481 end;
2482 --
2483 -- Process Logic
2484 --
2485 -- Set the effective date to the sysdate
2486 l_effective_date := sysdate;
2487 --+
2488 --+ Check the dates for the rule usage.
2489 --+
2490 l_swi_call := true;
2491 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
2492 l_swi_call := false ;
2493 if p_start_date < l_effective_date then
2494 fnd_message.set_name('PER','AME_400213_RUL_STRT_GRTR_CUR');
2495 hr_multi_message.add (p_associated_column1 => 'START_DATE');
2496 end if;
2497 if p_end_date < l_effective_date then
2498 fnd_message.set_name('PER','AME_400706_PAS_END_DATE');
2499 hr_multi_message.add (p_associated_column1 => 'END_DATE');
2500 end if;
2501 if p_start_date > p_end_date then
2502 fnd_message.set_name('PER','AME_400214_RUL_STRT_LESS_END');
2503 hr_multi_message.add (p_associated_column1 => 'START_DATE');
2504 end if;
2505 end if;
2506 --+
2507 --+ Check the Rule Id.
2508 --+
2509 ame_rule_utility_pkg.checkRuleId(p_rule_id => p_rule_id);
2510 --+
2511 --+ Check the Application Id.
2512 --+
2513 ame_rule_utility_pkg.checkApplicationId(p_application_id => p_application_id);
2514 --
2515 -- Check that there is no overlapping rule usage existing for the rule_id.
2516 -- Gaps between the rule usage dates is allowed.
2517 --
2518 l_overlapping_usage := checkRuleUsageExists(p_application_id => p_application_id
2519 ,p_rule_id => p_rule_id
2520 ,p_rlu_start_date => p_start_date
2521 ,p_rlu_end_date => p_end_date
2522 ,p_effective_date => l_effective_date
2523 ,p_priority => p_priority
2524 ,p_old_start_date => p_old_start_date);
2525 if l_overlapping_usage = 1 then
2526 fnd_message.set_name('PER','AME_400327_RULE_USG_EXST_LIFE');
2527 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
2528 elsif l_overlapping_usage = 2 then
2529 fnd_message.set_name('PER','AME_400328_RULE_USG_DIFF_PRIOR');
2530 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
2531 elsif l_overlapping_usage = 3 then
2532 fnd_message.set_name('PER','AME_400329_RULE_USG_OVER_LIFE');
2533 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
2534 end if;
2535 --
2536 l_rlu_object_version_number := p_object_version_number;
2537 l_rlu_start_date := p_start_date;
2538 l_rlu_end_date := p_end_date;
2539 -- update the row in ame_rule_usages. Parent row locking not needed.
2540 if l_swi_call and p_effective_date is not null then
2541 l_effective_date := p_effective_date;
2542 end if;
2543 ame_rlu_upd.upd(p_effective_date => l_effective_date
2544 ,p_datetrack_mode => hr_api.g_update
2545 ,p_rule_id => p_rule_id
2546 ,p_item_id => p_application_id
2547 ,p_old_start_date => p_old_start_date
2548 ,p_object_version_number=> l_rlu_object_version_number
2549 ,p_priority => p_priority
2550 ,p_approver_category => p_approver_category
2551 ,p_start_date => l_rlu_start_date
2552 ,p_end_date => l_rlu_end_date
2553 );
2554 --
2555 -- Perform check to see if rule dates have changed only if the usage dates are being changed and
2556 -- the call is not from an SWI package
2557 --
2558 /*if ((instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) and
2559 p_start_date is null and
2560 p_end_date is null) then
2561 fetchNewRuleDates(p_rule_id => p_rule_id
2562 ,p_rlu_start_date => l_rlu_start_date
2563 ,p_rlu_end_date => l_rlu_end_date
2564 ,p_rul_start_date => l_rul_start_date
2565 ,p_rul_end_date => l_rul_end_date
2566 ,p_date_changed => l_date_changed
2567 ) ;
2568 if l_date_changed = 'Y' then
2569 --
2570 -- date has changed, update the dates for rules, condition usages and action usages
2571 --
2572 -- rules
2573 ame_rul_upd.upd(p_rule_id => p_rule_id
2574 ,p_datetrack_mode => hr_api.g_update
2575 ,p_effective_date => l_effective_date
2576 ,p_object_version_number => l_rul_object_version_number
2577 ,p_start_date => l_rul_start_date
2578 ,p_end_date => l_rul_end_date
2579 );
2580 -- actions usages
2581 for tempActions in getActions loop
2582 l_acu_object_version_number := tempActions.object_version_number;
2583 l_acu_start_date := tempActions.start_date;
2584 l_acu_end_date := tempActions.end_date;
2585 ame_acu_upd.upd(p_rule_id => p_rule_id
2586 ,p_datetrack_mode => hr_api.g_update
2587 ,p_action_id => tempActions.action_id
2588 ,p_effective_date => l_effective_date
2589 ,p_object_version_number => l_acu_object_version_number
2590 ,p_start_date => l_acu_start_date
2591 ,p_end_date => l_acu_end_date
2592 );
2593 end loop;
2594 -- condition usages
2595 for tempConditions in getConditions loop
2596 l_cnu_object_version_number := tempConditions.object_version_number;
2597 l_cnu_start_date := tempConditions.start_date;
2598 l_cnu_end_date := tempConditions.end_date;
2599 ame_cnu_upd.upd(p_rule_id => p_rule_id
2600 ,p_datetrack_mode => hr_api.g_update
2601 ,p_condition_id => tempConditions.condition_id
2602 ,p_effective_date => l_effective_date
2603 ,p_object_version_number => l_cnu_object_version_number
2604 ,p_start_date => l_cnu_start_date
2605 ,p_end_date => l_cnu_end_date
2606 );
2607 end loop;
2608 end if;
2609 end if;*/
2610 --+
2611 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
2612 ame_rule_utility_pkg.syncRuleObjects(p_rule_id => p_rule_id);
2613 end if;
2614 --
2615 -- Call After Process User Hook
2616 --
2617 begin
2618 ame_rule_bk4.update_ame_rule_usage_a
2619 (p_rule_id => p_rule_id
2620 ,p_application_id => p_application_id
2621 ,p_priority => p_priority
2622 ,p_approver_category => p_approver_category
2623 ,p_old_start_date => p_old_start_date
2624 ,p_object_version_number => p_object_version_number
2625 ,p_start_date => p_start_date
2626 ,p_end_date => p_end_date
2627 );
2628 exception
2629 when hr_api.cannot_find_prog_unit then
2630 hr_api.cannot_find_prog_unit_error
2631 (p_module_name => 'update_ame_rule_usage'
2632 ,p_hook_type => 'AP'
2633 );
2634 end;
2635 --
2636 -- When in validation only mode raise the Validate_Enabled exception
2637 --
2638 if p_validate then
2639 raise hr_api.validate_enabled;
2640 end if;
2641 --
2642 -- Set all IN OUT and OUT parameters with out values
2643 --
2644 p_object_version_number := l_rlu_object_version_number;
2645 p_start_date := l_rlu_start_date;
2646 p_end_date := l_rlu_end_date;
2647 --
2648 hr_utility.set_location(' Leaving:'||l_proc, 70);
2649 exception
2650 when hr_api.validate_enabled then
2651 --
2652 -- As the Validate_Enabled exception has been raised
2653 -- we must rollback to the savepoint
2654 --
2655 rollback to update_ame_rule_usage;
2656 --
2657 -- Reset IN OUT parameters and set OUT parameters
2658 -- (Any key or derived arguments must be set to null
2659 -- when validation only mode is being used.)
2660 --
2661 p_start_date := null;
2662 p_end_date := null;
2663 hr_utility.set_location(' Leaving:'||l_proc, 80);
2664 when others then
2665 --
2666 -- A validation or unexpected error has occured
2667 --
2668 rollback to update_ame_rule_usage;
2669 --
2670 -- Reset IN OUT parameters and set all
2671 -- OUT parameters, including warnings, to null
2672 --
2673 p_start_date := null;
2674 p_end_date := null;
2675 --+
2676 hr_utility.set_location(' Leaving:'||l_proc, 90);
2677 raise;
2678 end update_ame_rule_usage;
2679 --
2680 -- ----------------------------------------------------------------------------
2681 -- |---------------------< delete_ame_rule_usage >--------------------------|
2682 -- ----------------------------------------------------------------------------
2683 --
2684 procedure delete_ame_rule_usage
2685 (p_validate in boolean default false
2686 ,p_rule_id in number
2687 ,p_application_id in number
2688 ,p_object_version_number in out nocopy number
2689 ,p_start_date in out nocopy date
2690 ,p_end_date in out nocopy date
2691 ) is
2692 --+
2693 --+ Declare cursors and local variables
2694 --+
2695 l_atu_object_version_number number;
2696 l_atu_start_date date;
2697 l_atu_end_date date;
2698 l_acu_object_version_number number;
2699 l_acu_start_date date;
2700 l_acu_end_date date;
2701 l_cnu_object_version_number number;
2702 l_cnu_start_date date;
2703 l_cnu_end_date date;
2704 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
2705 l_rul_object_version_number number;
2706 l_rlu_object_version_number number;
2707 l_rul_start_date date;
2708 l_rul_start_date2 date;
2709 l_rlu_start_date date;
2710 l_rul_end_date date;
2711 l_rul_end_date2 date;
2712 l_rlu_end_date date;
2713 l_effective_date date;
2714 l_exists number;
2715 l_proc varchar2(72) := g_package||'delete_ame_rule_usage';
2716 l_usage_count number;
2717 --+
2718 cursor getActions is
2719 select action_id, start_date, end_date, object_version_number
2720 from ame_action_usages
2721 where rule_id = p_rule_id
2722 and ( l_effective_date between start_date and
2723 nvl(end_date - ame_util.oneSecond,l_effective_date ) or
2724 (l_effective_date < start_date and
2725 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
2726 --+
2727 --+
2728 cursor getRuleDetails is
2729 select start_date
2730 ,end_date
2731 ,object_version_number
2732 from ame_rules
2733 where rule_id = p_rule_id
2734 and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
2735 or
2736 (l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
2737 --+
2738 cursor getConditions is
2739 select condition_id
2740 ,start_date
2741 ,end_date
2742 ,object_version_number
2743 from ame_condition_usages
2744 where rule_id = p_rule_id
2745 and ((l_effective_date between start_date
2746 and nvl(end_date - ame_util.oneSecond,l_effective_date))
2747 or
2748 (l_effective_date < start_date
2749 and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
2750 --+
2751 cursor getRuleConditions is
2752 select ame_conditions.attribute_id attribute_id
2753 from ame_conditions
2754 ,ame_condition_usages
2755 where ame_condition_usages.rule_id = p_rule_id
2756 and (l_effective_date between ame_condition_usages.start_date
2757 and nvl(ame_condition_usages.end_date - ame_util.oneSecond, l_effective_date)
2758 or
2759 (l_effective_date < ame_condition_usages.start_date
2760 and ame_condition_usages.start_date <
2761 nvl(ame_condition_usages.end_date, ame_condition_usages.start_date + ame_util.oneSecond)))
2762 and ame_condition_usages.condition_id = ame_conditions.condition_id
2763 and ame_conditions.condition_type <> ame_util.listModConditionType
2764 and l_effective_date between ame_conditions.start_date
2765 and nvl(ame_conditions.end_date - ame_util.oneSecond,l_effective_date);
2766 --+
2767 cursor getReqAttributes is
2768 select man.attribute_id
2769 from ame_mandatory_attributes man
2770 ,ame_action_usages acu
2771 ,ame_actions act
2772 where man.action_type_id = act.action_type_id
2773 and acu.action_id = act.action_id
2774 and acu.rule_id = p_rule_id
2775 and l_effective_date between man.start_date and nvl(man.end_date - ame_util.oneSecond, l_effective_date)
2776 and l_effective_date between act.start_date and nvl(act.end_date - ame_util.oneSecond, l_effective_date)
2777 and ((l_effective_date between acu.start_date and nvl(acu.end_date - ame_util.oneSecond, l_effective_date))
2778 or
2779 (l_effective_date < acu.start_date and acu.start_date < nvl(acu.end_date, acu.start_date + ame_util.oneSecond))
2780 );
2781 --+
2782 cursor getAttributeUsages(p_attribute_id in number) is
2783 select attribute_id, use_count, start_date, end_date, object_version_number
2784 from ame_attribute_usages
2785 where attribute_id = p_attribute_id
2786 and application_id = p_application_id
2787 and l_effective_date between start_date and
2788 nvl(end_date - ame_util.oneSecond,l_effective_date);
2789 begin
2790 hr_utility.set_location('Entering:'|| l_proc, 10);
2791 --
2792 -- Issue a savepoint
2793 --
2794 savepoint delete_ame_rule_usage;
2795 --
2796 -- Call Before Process User Hook
2797 --
2798 begin
2799 ame_rule_bk5.delete_ame_rule_usage_b
2800 (p_rule_id => p_rule_id
2801 ,p_application_id => p_application_id
2802 ,p_object_version_number => p_object_version_number
2803 ,p_start_date => p_start_date
2804 ,p_end_date => p_end_date
2805 );
2806 exception
2807 when hr_api.cannot_find_prog_unit then
2808 hr_api.cannot_find_prog_unit_error
2809 (p_module_name => 'delete_ame_rule_usage'
2810 ,p_hook_type => 'BP'
2811 );
2812 end;
2813 --+
2814 --+ Check Rule Id.
2815 --+
2816 ame_rule_utility_pkg.checkRuleId(p_rule_id => p_rule_id);
2817 --+
2818 --+ Check Application Id.
2819 --+
2820 ame_rule_utility_pkg.checkApplicationId(p_application_id => p_application_id);
2821 --
2822 -- Process Logic
2823 --
2824 -- Set the effective date to the sysdate
2825 l_effective_date := sysdate;
2826 l_rlu_object_version_number := p_object_version_number;
2827 l_rlu_start_date := p_start_date;
2828 l_rlu_end_date := p_end_date;
2829 -- delete the row in ame_rule_usages
2830 --
2831 ame_rlu_del.del(p_effective_date => l_effective_date
2832 ,p_datetrack_mode => hr_api.g_delete
2833 ,p_rule_id => p_rule_id
2834 ,p_item_id => p_application_id
2835 ,p_object_version_number => l_rlu_object_version_number
2836 ,p_start_date => l_rlu_start_date
2837 ,p_end_date => l_rlu_end_date
2838 );
2839 --
2840 --
2841 -- Update the attribute usage counts
2842 --
2843 for tempconditions in getRuleConditions loop
2844 for tempAttributeUsages in getAttributeUsages(p_attribute_id => tempconditions.attribute_id) loop
2845 l_atu_object_version_number := tempAttributeUsages.object_version_number;
2846 ame_attribute_api.updateUseCount(p_attribute_id => tempAttributeUsages.attribute_id
2847 ,p_application_id => p_application_id
2848 ,p_atu_object_version_number => l_atu_object_version_number);
2849 end loop;
2850 end loop;
2851 -- update the use count of req attributes
2852 for tempAttribute in getReqAttributes loop
2853 for tempAttributeUsages in getAttributeUsages(p_attribute_id => tempAttribute.attribute_id) loop
2854 l_atu_object_version_number := tempAttributeUsages.object_version_number;
2855 ame_attribute_api.updateUseCount(p_attribute_id => tempAttributeUsages.attribute_id
2856 ,p_application_id => p_application_id
2857 ,p_atu_object_version_number => l_atu_object_version_number);
2858 end loop;
2859 end loop;
2860 --
2861 --
2862 --
2863 open getRuleDetails;
2864 fetch getRuleDetails
2865 into l_rul_start_date
2866 ,l_rul_end_date
2867 ,l_rul_object_version_number;
2868 close getRuleDetails;
2869 -- If call is not made from an SWI package then
2870 -- Check number of usages which exist for this rule. If future and current usages at this
2871 -- point are = 0 delete the Condition Usage, Action Usage and Rule row too.
2872 --
2873 --if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
2874 select count(*)
2875 into l_usage_count
2876 from ame_rule_usages
2877 where rule_id = p_rule_id
2878 and ((l_effective_date between start_date
2879 and nvl(end_date - ame_util.oneSecond,l_effective_date))
2880 or
2881 (l_effective_date < start_date
2882 and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
2883 if l_usage_count = 0 then
2884 -- actions usages
2885 for tempActions in getActions loop
2886 l_acu_object_version_number := tempActions.object_version_number;
2887 l_acu_start_date := tempActions.start_date;
2888 l_acu_end_date := tempActions.end_date;
2889 ame_acu_del.del(p_effective_date => l_effective_date
2890 ,p_datetrack_mode => hr_api.g_delete
2891 ,p_rule_id => p_rule_id
2892 ,p_action_id => tempActions.action_id
2893 ,p_object_version_number => l_acu_object_version_number
2894 ,p_start_date => l_acu_start_date
2895 ,p_end_date => l_acu_end_date
2896 );
2897 end loop;
2898 -- condition usages
2899 for tempConditions in getConditions loop
2900 l_cnu_object_version_number := tempConditions.object_version_number;
2901 l_cnu_start_date := tempConditions.start_date;
2902 l_cnu_end_date := tempConditions.end_date;
2903 ame_cnu_del.del(p_effective_date => l_effective_date
2904 ,p_datetrack_mode => hr_api.g_delete
2905 ,p_rule_id => p_rule_id
2906 ,p_condition_id => tempConditions.condition_id
2907 ,p_object_version_number => l_cnu_object_version_number
2908 ,p_start_date => l_cnu_start_date
2909 ,p_end_date => l_cnu_end_date
2910 );
2911 end loop;
2912 -- rule
2913 ame_rul_del.del(p_effective_date => l_effective_date
2914 ,p_datetrack_mode => hr_api.g_delete
2915 ,p_rule_id => p_rule_id
2916 ,p_object_version_number => l_rul_object_version_number
2917 ,p_start_date => l_rul_start_date
2918 ,p_end_date => l_rul_end_date
2919 );
2920 else
2921 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
2922 ame_rule_utility_pkg.syncRuleObjects(p_rule_id => p_rule_id);
2923 end if;
2924 --+
2925 end if;
2926 --end if;
2927 --
2928 -- Call After Process User Hook
2929 --
2930 begin
2931 ame_rule_bk5.delete_ame_rule_usage_a
2932 (p_rule_id => p_rule_id
2933 ,p_application_id => p_application_id
2934 ,p_object_version_number => l_rlu_object_version_number
2935 ,p_start_date => l_rlu_start_date
2936 ,p_end_date => l_rlu_end_date
2937 );
2938 exception
2939 when hr_api.cannot_find_prog_unit then
2940 hr_api.cannot_find_prog_unit_error
2941 (p_module_name => 'delete_ame_rule_usage'
2942 ,p_hook_type => 'AP'
2943 );
2944 end;
2945 --
2946 -- When in validation only mode raise the Validate_Enabled exception
2947 --
2948 if p_validate then
2949 raise hr_api.validate_enabled;
2950 end if;
2951 --
2952 -- Set all IN OUT and OUT parameters with out values
2953 --
2954 p_object_version_number := l_rlu_object_version_number;
2955 p_start_date := l_rlu_start_date;
2956 p_end_date := l_rlu_end_date;
2957 --
2958 hr_utility.set_location(' Leaving:'||l_proc, 70);
2959 exception
2960 when hr_api.validate_enabled then
2961 --
2962 -- As the Validate_Enabled exception has been raised
2963 -- we must rollback to the savepoint
2964 --
2965 rollback to delete_ame_rule_usage;
2966 --
2967 -- Reset IN OUT parameters and set OUT parameters
2968 -- (Any key or derived arguments must be set to null
2969 -- when validation only mode is being used.)
2970 --
2971 p_start_date := null;
2972 p_end_date := null;
2973 --+
2974 hr_utility.set_location(' Leaving:'||l_proc, 80);
2975 when others then
2976 --
2977 -- A validation or unexpected error has occured
2978 --
2979 rollback to delete_ame_rule_usage;
2980 --
2981 -- Reset IN OUT parameters and set all
2982 -- OUT parameters, including warnings, to null
2983 --
2984 p_start_date := null;
2985 p_end_date := null;
2986 --+
2987 hr_utility.set_location(' Leaving:'||l_proc, 90);
2988 raise;
2989 end delete_ame_rule_usage;
2990 --
2991 -- ----------------------------------------------------------------------------
2992 -- |------------------<delete_ame_rule_condition >--------------------------|
2993 -- ----------------------------------------------------------------------------
2994 --
2995 procedure delete_ame_rule_condition
2996 (p_validate in boolean default false
2997 ,p_rule_id in number
2998 ,p_condition_id in number
2999 ,p_object_version_number in out nocopy number
3000 ,p_start_date out nocopy date
3001 ,p_end_date out nocopy date
3002 ,p_effective_date in date default null
3003 ) is
3004 --+
3005 --+ Declare cursors
3006 --+
3007 l_effective_date date;
3008 l_attribute_id number;
3009 --+
3010 cursor getApplicationName(applicationIdIn in integer)is
3011 select application_name
3012 from ame_calling_apps
3013 where application_id = applicationIdIn
3014 and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
3015 --+
3016 cursor getConditionCount(p_condition_type in varchar2
3017 ,p_condition_id in integer) is
3018 select count(*)
3019 from ame_conditions, ame_condition_usages
3020 where ame_condition_usages.condition_id <> p_condition_id
3021 and ame_condition_usages.rule_id = p_rule_id
3022 and ( l_effective_date between ame_condition_usages.start_date and
3023 nvl(ame_condition_usages.end_date - ame_util.oneSecond,l_effective_date ) or
3024 (l_effective_date < ame_condition_usages.start_date and
3025 ame_condition_usages.start_date <
3026 nvl(ame_condition_usages.end_date,ame_condition_usages.start_date + ame_util.oneSecond)))
3027 and ame_condition_usages.condition_id = ame_conditions.condition_id
3028 and ame_conditions.condition_type = p_condition_type
3029 and l_effective_date between ame_conditions.start_date and
3030 nvl(ame_conditions.end_date - ame_util.oneSecond,l_effective_date );
3031 --+
3032 cursor getRuleDetails is
3033 select rule_type, start_date, end_date, item_class_id
3034 from ame_rules
3035 where rule_id = p_rule_id
3036 and ( l_effective_date between start_date and
3037 nvl(end_date - ame_util.oneSecond,l_effective_date ) or
3038 (l_effective_date < start_date and
3039 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
3040 --+
3041 cursor getApplications is
3042 select item_id
3043 from ame_rule_usages
3044 where rule_id = p_rule_id
3045 and ( l_effective_date between start_date and
3046 nvl(end_date - ame_util.oneSecond,l_effective_date ) or
3047 (l_effective_date < start_date and
3048 start_date < nvl(end_date,start_date + ame_util.oneSecond)));
3049 --+
3050 cursor getConditionType(conditionIdIn in integer) is
3051 select condition_type
3052 from ame_conditions
3053 where condition_id = conditionIdIn
3054 and l_effective_date between start_date
3055 and nvl(end_date - ame_util.oneSecond,l_effective_date);
3056 --+
3057 cursor getAttributeUsages(p_application_id in integer) is
3058 select use_count, start_date, end_date, object_version_number
3059 from ame_attribute_usages
3060 where attribute_id = l_attribute_id
3061 and l_effective_date between start_date and
3062 nvl(end_date - ame_util.oneSecond,l_effective_date )
3063 and application_id = p_application_id;
3064 --+
3065 cursor getAttributeId is
3066 select attribute_id
3067 from ame_conditions
3068 where condition_id = p_condition_id
3069 and l_effective_date between start_date and
3070 nvl(end_date - ame_util.oneSecond,l_effective_date ) ;
3071 --+
3072 --+ Declare local variables
3073 --+
3074 l_proc varchar2(72) := g_package||'delete_ame_rule_condition ';
3075 l_dummy varchar2(10);
3076 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
3077 l_condition_type ame_conditions.condition_type%type;
3078 l_atu_object_version_number number;
3079 l_atu_start_date date;
3080 l_atu_end_date date;
3081 l_condition_count number;
3082 l_count number;
3083 l_rule_type ame_rules.rule_type%type;
3084 l_item_class_id number;
3085 l_application_id number;
3086 l_rul_object_version_number number;
3087 l_rul_start_date date;
3088 l_rul_end_date date;
3089 l_cnu_object_version_number number;
3090 l_cnu_start_date date;
3091 l_cnu_end_date date;
3092 l_acu_object_version_number number;
3093 l_acu_start_date date;
3094 l_acu_end_date date;
3095 l_use_count number := 0;
3096 actionIdList ame_util.idList;
3097 conditionIdList ame_util.idList;
3098 applicationName ame_calling_apps.application_name%type;
3099 attributeName ame_attributes.name%type;
3100 l_swi_call boolean;
3101 begin
3102 hr_utility.set_location('Entering:'|| l_proc, 10);
3103 --
3104 -- Issue a savepoint
3105 --
3106 savepoint delete_ame_rule_condition ;
3107 --
3108 -- Call Before Process User Hook
3109 --
3110 begin
3111 ame_rule_bk8.delete_ame_rule_condition_b
3112 (p_rule_id => p_rule_id
3113 ,p_condition_id => p_condition_id
3114 ,p_object_version_number => p_object_version_number
3115 );
3116 exception
3117 when hr_api.cannot_find_prog_unit then
3118 hr_api.cannot_find_prog_unit_error
3119 (p_module_name => 'delete_ame_rule_condition'
3120 ,p_hook_type => 'BP'
3121 );
3122 end;
3123 --
3124 -- Process Logic
3125 --
3126 -- Set the effective date to the sysdate
3127 l_effective_date := sysdate;
3128 l_cnu_object_version_number := p_object_version_number;
3129 -- Perform following Integrity checks.
3130 -- a. If Rule type is Exception rule, then atleast one exception type condition other
3131 -- than this one should exist in the rule.
3132 -- b. If Rule type is List-modification or substitution rules atleast one List Modification
3133 -- or Substitution type condition other than this one should exist in the rule
3134 --+
3135 --+ Check Rule Id.
3136 --+
3137 ame_rule_utility_pkg.checkRuleId(p_rule_id => p_rule_id);
3138 --+
3139 --+ Check Condition Id.
3140 --+
3141 ame_rule_utility_pkg.checkConditionId(p_condition_id => p_condition_id);
3142 --
3143 --
3144 -- Fetch the rule details
3145 --
3146 open getRuleDetails;
3147 fetch getRuleDetails into l_rule_type, l_rul_start_date, l_rul_end_date,
3148 l_item_class_id;
3149 if getRuleDetails%notfound then
3150 fnd_message.set_name('PER','AME_400729_INV_RULE_ID');
3151 hr_multi_message.add(p_associated_column1 => 'RULE_ID');
3152 end if;
3153 close getRuleDetails;
3154 l_swi_call := true;
3155 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
3156 l_swi_call := false;
3157 if not ame_rule_utility_pkg.is_cond_exist_in_rule(p_rule_id => p_rule_id
3158 ,p_condition_id => p_condition_id) then
3159 fnd_message.set_name('PER','AME_400772_CON_NOT_EXIST_RULE');
3160 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
3161 end if;
3162 if l_rule_type = ame_util.exceptionRuleType then
3163 l_condition_type := ame_util.exceptionConditionType;
3164 --
3165 -- Fetch the condition details
3166 --
3167 open getConditionCount(p_condition_type => l_condition_type
3168 ,p_condition_id => p_condition_id);
3169 fetch getConditionCount into l_count;
3170 close getConditionCount;
3171 if l_count = 0 then
3172 fnd_message.set_name('PER','AME_400709_NO_EXC_COND_LCE_RUL');
3173 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
3174 end if;
3175 elsif l_rule_type in (ame_util.listModRuleType, ame_util.substitutionRuleType) then
3176 l_condition_type := ame_util.listModConditionType ;
3177 --
3178 -- Fetch the condition details
3179 --
3180 open getConditionCount(p_condition_type => l_condition_type
3181 ,p_condition_id => p_condition_id);
3182 fetch getConditionCount into l_count;
3183 close getConditionCount;
3184 if l_count = 0 then
3185 fnd_message.set_name('PER','AME_400710_NO_LM_CON_LMSUB_RUL');
3186 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
3187 end if;
3188 end if;
3189 --
3190 -- Check that there is no other rule with the same combination of actions and conditions
3191 -- existing.
3192 --
3193 -- Fetch conditions and actions for rule
3194 getConditionIds(ruleIdIn => p_rule_id,
3195 conditionIdListOut => conditionIdList);
3196 getActionIds(ruleIdIn => p_rule_id,
3197 actionIdListOut => actionIdList);
3198 -- Remove this condition_id from list
3199 l_condition_count := conditionIdList.count;
3200 for i in 1.. l_condition_count loop
3201 if conditionIdList(i) = p_condition_id then
3202 if i = l_condition_count then
3203 conditionIdList.delete(i);
3204 else
3205 conditionIdList(i) := conditionIdList(l_condition_count);
3206 conditionIdList.delete(l_condition_count);
3207 end if;
3208 exit;
3209 end if;
3210 end loop;
3211 /*
3212 if ruleExists(p_rule_id => p_rule_id
3213 ,p_rule_type => l_rule_type
3214 ,p_item_class_id => l_item_class_id
3215 ,p_effective_date => l_effective_date
3216 ,p_conditions_list => conditionIdList
3217 ,p_actions_list => actionIdList
3218 ) then
3219 fnd_message.set_name('PER','AME_400212_RUL_PROP_EXISTS');
3220 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
3221 end if;
3222 */
3223 end if; -- end of if not swi call.
3224
3225 if l_swi_call and p_effective_date is not null then
3226 l_effective_date := p_effective_date;
3227 end if;
3228
3229 --
3230 -- Check that an attribute usage exists for the attribute this condition is based on
3231 -- for all the transaction type's using this rule.
3232 -- Update the attribute usage counts
3233 open getConditionType(conditionIdIn => p_condition_id);
3234 fetch getConditionType into l_condition_type;
3235 close getConditionType;
3236 --
3237 -- delete the row in ame_condition_usages
3238 --
3239 ame_cnu_del.del(p_effective_date => l_effective_date
3240 ,p_datetrack_mode => hr_api.g_delete
3241 ,p_rule_id => p_rule_id
3242 ,p_condition_id => p_condition_id
3243 ,p_object_version_number => l_cnu_object_version_number
3244 ,p_start_date => l_cnu_start_date
3245 ,p_end_date => l_cnu_end_date
3246 );
3247 --
3248 if(l_condition_type <> ame_util.listModConditionType) then
3249 open getAttributeId;
3250 fetch getAttributeId into l_attribute_id;
3251 close getAttributeId;
3252 --+
3253 for tempApplications in getApplications loop
3254 open getAttributeUsages(p_application_id => tempApplications.item_id) ;
3255 fetch getAttributeUsages into l_use_count, l_atu_start_date, l_atu_end_date,l_atu_object_version_number;
3256 if getAttributeUsages%notfound then
3257 open getApplicationName(applicationIdIn => tempApplications.item_id);
3258 fetch getApplicationName into applicationName;
3259 close getApplicationName;
3260 ame_rule_utility_pkg.getAttributeName(p_attribute_id => l_attribute_id
3261 ,p_attribute_name_out => attributeName);
3262 fnd_message.set_name('PER','AME_400149_ATT_TTY_NO_USAGE');
3263 fnd_message.set_token('ATTRIBUTE',attributeName);
3264 fnd_message.set_token('APPLICATION',applicationName);
3265 hr_multi_message.add(p_associated_column1 => 'RULE_ID');
3266 end if;
3267 close getAttributeUsages;
3268 ame_attribute_api.updateUseCount(p_attribute_id => l_attribute_id
3269 ,p_application_id => tempApplications.item_id
3270 ,p_atu_object_version_number => l_atu_object_version_number);
3271 end loop;
3272 end if;
3273 --
3274 -- Call After Process User Hook
3275 --
3276 begin
3277 ame_rule_bk8.delete_ame_rule_condition_a
3278 (p_rule_id => p_rule_id
3279 ,p_condition_id => p_condition_id
3280 ,p_object_version_number => l_cnu_object_version_number
3281 ,p_start_date => l_cnu_start_date
3282 ,p_end_date => l_cnu_end_date
3283 );
3284 exception
3285 when hr_api.cannot_find_prog_unit then
3286 hr_api.cannot_find_prog_unit_error
3287 (p_module_name => 'delete_ame_rule_condition'
3288 ,p_hook_type => 'AP'
3289 );
3290 end;
3291 --
3292 -- When in validation only mode raise the Validate_Enabled exception
3293 --
3294 if p_validate then
3295 raise hr_api.validate_enabled;
3296 end if;
3297 --
3298 -- Set all IN OUT and OUT parameters with out values
3299 --
3300 p_object_version_number := l_cnu_object_version_number;
3301 p_start_date := l_cnu_start_date;
3302 p_end_date := l_cnu_end_date;
3303 --
3304 hr_utility.set_location(' Leaving:'||l_proc, 70);
3305 exception
3306 when hr_api.validate_enabled then
3307 --
3308 -- As the Validate_Enabled exception has been raised
3309 -- we must rollback to the savepoint
3310 --
3311 rollback to delete_ame_rule_condition;
3312 --
3313 -- Reset IN OUT parameters and set OUT parameters
3314 -- (Any key or derived arguments must be set to null
3315 -- when validation only mode is being used.)
3316 --
3317 p_object_version_number := null;
3318 p_start_date := null;
3319 p_end_date := null;
3320 --+
3321 hr_utility.set_location(' Leaving:'||l_proc, 80);
3322 when others then
3323 --
3324 -- A validation or unexpected error has occured
3325 --
3326 rollback to delete_ame_rule_condition;
3327 --
3328 -- Reset IN OUT parameters and set all
3329 -- OUT parameters, including warnings, to null
3330 --
3331 p_object_version_number := null;
3332 p_start_date := null;
3333 p_end_date := null;
3334 --+
3335 hr_utility.set_location(' Leaving:'||l_proc, 90);
3336 raise;
3337 end delete_ame_rule_condition;
3338 --
3339 -- ----------------------------------------------------------------------------
3340 -- |------------------<delete_ame_rule_action >--------------------------|
3341 -- ----------------------------------------------------------------------------
3342 --
3343 procedure delete_ame_rule_action
3344 (p_validate in boolean default false
3345 ,p_rule_id in number
3346 ,p_action_id in number
3347 ,p_object_version_number in out nocopy number
3348 ,p_start_date out nocopy date
3349 ,p_end_date out nocopy date
3350 ,p_effective_date in date default null
3351 ) is
3352 --
3353 -- Declare cursors and local variables
3354 --
3355 l_proc varchar2(72) := g_package||'delete_ame_rule_action ';
3356 l_dummy varchar2(10);
3357 l_action_type_id ame_action_types.action_type_id%type;
3358 l_attribute_id number;
3359 l_effective_date date;
3360 --+
3361 cursor getActionCount is
3362 select count(*)
3363 from ame_action_usages acu
3364 where rule_id = p_rule_id
3365 and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
3366 or
3367 (l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)))
3368 and action_id <> p_action_id;
3369 --+
3370 cursor getRuleDetails is
3371 select rule_type
3372 ,start_date
3373 ,end_date
3374 ,item_class_id
3375 from ame_rules
3376 where rule_id = p_rule_id
3377 and ((l_effective_date between start_date and nvl(end_date - ame_util.oneSecond, l_effective_date))
3378 or
3379 (l_effective_date < start_date and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
3380 --+
3381 cursor getReqAttributeIds(actionIdIn in integer) is
3382 select man.attribute_id
3383 from ame_mandatory_attributes man
3384 ,ame_actions act
3385 where man.action_type_id = act.action_type_id
3386 and act.action_id = actionIdIn
3387 and l_effective_date between man.start_date and nvl(man.end_date - ame_util.oneSecond, l_effective_date)
3388 and l_effective_date between act.start_date and nvl(act.end_date - ame_util.oneSecond, l_effective_date);
3389 --+
3390 cursor getApplications(ruleIdIn in integer)is
3391 select item_id
3392 from ame_rule_usages
3393 where rule_id = ruleIdIn
3394 and (l_effective_date between start_date
3395 and nvl(end_date - ame_util.oneSecond, l_effective_date)
3396 or
3397 (l_effective_date < start_date
3398 and start_date < nvl(end_date, start_date + ame_util.oneSecond)));
3399 --+
3400 cursor getAttributeUsages(attributeIdIn in integer
3401 ,applicationIdIn in integer) is
3402 select application_id
3403 ,use_count
3404 ,start_date
3405 ,end_date
3406 ,object_version_number
3407 from ame_attribute_usages
3408 where attribute_id = attributeIdIn
3409 and application_id = applicationIdIn
3410 and l_effective_date between start_date
3411 and nvl(end_date - ame_util.oneSecond, l_effective_date);
3412 --+
3413 l_count number;
3414 l_allowAllApproverTypes varchar2(30);
3415 l_allowProduction varchar2(30);
3416 l_action_type_id number;
3417 l_atu_object_version_number integer;
3418 l_rule_type ame_rules.rule_type%type;
3419 l_item_class_id number;
3420 l_date_changed varchar2(10);
3421 l_application_id number;
3422 l_acu_object_version_number number;
3423 l_acu_start_date date;
3424 l_acu_end_date date;
3425 l_use_count number := 0;
3426 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
3427 actionIdList ame_util.idList;
3428 conditionIdList ame_util.idList;
3429 l_swi_call boolean;
3430 begin
3431 hr_utility.set_location('Entering:'|| l_proc, 10);
3432 --
3433 -- Issue a savepoint
3434 --
3435 savepoint delete_ame_rule_action ;
3436 --
3437 --
3438 l_swi_call := true;
3439 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(ascii_chr => 10)) = 0) then
3440 l_swi_call := false;
3441 end if;
3442 --
3443 -- Call Before Process User Hook
3444 --
3445 begin
3446 ame_rule_bk9.delete_ame_rule_action_b
3447 (p_rule_id => p_rule_id
3448 ,p_action_id => p_action_id
3449 ,p_object_version_number => p_object_version_number
3450 );
3451 exception
3452 when hr_api.cannot_find_prog_unit then
3453 hr_api.cannot_find_prog_unit_error
3454 (p_module_name => 'delete_ame_rule_action '
3455 ,p_hook_type => 'BP'
3456 );
3457 end;
3458 --
3459 -- Process Logic
3460 --
3461 -- Set the effective date to the sysdate
3462 l_effective_date := sysdate;
3463 l_acu_object_version_number := p_object_version_number;
3464 --+
3465 --+ Check that there is no other rule with the same combination of actions and conditions existing.
3466 --+
3467 --+ Fetch conditions and actions for rule
3468 if not l_swi_call then
3469 --+ verify the rule_id
3470 ame_rule_utility_pkg.checkRuleId(p_rule_id => p_rule_id);
3471 --+ verify the action_id
3472 ame_rule_utility_pkg.checkActionId(p_action_id => p_action_id);
3473 --+
3474 --+ Check that there is atleast one other action defined for this rule, besides this one.
3475 --+
3476 open getActionCount;
3477 fetch getActionCount into l_count;
3478 close getActionCount;
3479 if l_count = 0 then
3480 fnd_message.set_name('PER','AME_400724_NO_ACTION_IN_RULE');
3481 hr_multi_message.add(p_associated_column1 => 'ACTION_ID');
3482 end if;
3483 --+
3484 --+ verify the action can be deleted or not
3485 --+
3486 if not ame_rule_utility_pkg.is_action_deletion_allowed
3487 (p_rule_id => p_rule_id
3488 ,p_action_id => p_action_id) then
3489 fnd_message.set_name('PER','AME_400739_INV_ACT_DEL_RULE');
3490 hr_multi_message.add(p_associated_column1 => 'ACTION_ID');
3491 end if;
3492 --+
3493 getConditionIds(ruleIdIn => p_rule_id,
3494 conditionIdListOut => conditionIdList);
3495 getActionIds(ruleIdIn => p_rule_id,
3496 actionIdListOut => actionIdList);
3497 -- Remove this action_id from list
3498 l_count := conditionIdList.count;
3499 for i in 1.. l_count loop
3500 if actionIdList(i) = p_action_id then
3501 if i = l_count then
3502 actionIdList.delete(i);
3503 else
3504 actionIdList(i) := actionIdList(l_count);
3505 actionIdList.delete(l_count);
3506 end if;
3507 exit;
3508 end if;
3509 end loop;
3510 /*
3511 if ruleExists(p_rule_id => p_rule_id
3512 ,p_rule_type => l_rule_type
3513 ,p_item_class_id => l_item_class_id
3514 ,p_effective_date => l_effective_date
3515 ,p_conditions_list => conditionIdList
3516 ,p_actions_list => actionIdList
3517 ) then
3518 fnd_message.set_name('PER','AME_400212_RUL_PROP_EXISTS');
3519 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
3520 end if;
3521 */
3522 end if;
3523 if l_swi_call and p_effective_date is not null then
3524 l_effective_date := p_effective_date;
3525 end if;
3526 --+
3527 -- delete the row in ame_action_usages
3528 --+
3529 ame_acu_del.del(p_effective_date => l_effective_date
3530 ,p_datetrack_mode => hr_api.g_delete
3531 ,p_rule_id => p_rule_id
3532 ,p_action_id => p_action_id
3533 ,p_object_version_number => l_acu_object_version_number
3534 ,p_start_date => l_acu_start_date
3535 ,p_end_date => l_acu_end_date
3536 );
3537 --+
3538 -- update the use count of req attributes
3539 --+
3540 for tempAttribute in getReqAttributeIds(actionIdIn => p_action_id) loop
3541 for tempApplication in getApplications(ruleIdIn => p_rule_id) loop
3542 for tempAttributeUsages in getAttributeUsages(attributeIdIn => tempAttribute.attribute_id
3543 ,applicationIdIn => tempApplication.item_id) loop
3544 l_atu_object_version_number := tempAttributeUsages.object_version_number;
3545 ame_attribute_api.updateUseCount(p_attribute_id => tempAttribute.attribute_id
3546 ,p_application_id => tempApplication.item_id
3547 ,p_atu_object_version_number => l_atu_object_version_number);
3548 end loop;
3549 end loop;
3550 end loop;
3551 --+
3552 -- Call After Process User Hook
3553 --+
3554 begin
3555 ame_rule_bk9.delete_ame_rule_action_a
3556 (p_rule_id => p_rule_id
3557 ,p_action_id => p_action_id
3558 ,p_object_version_number => l_acu_object_version_number
3559 ,p_start_date => l_acu_start_date
3560 ,p_end_date => l_acu_end_date
3561 );
3562 exception
3563 when hr_api.cannot_find_prog_unit then
3564 hr_api.cannot_find_prog_unit_error
3565 (p_module_name => 'delete_ame_rule_action '
3566 ,p_hook_type => 'AP'
3567 );
3568 end;
3569 --+
3570 -- When in validation only mode raise the Validate_Enabled exception
3571 --+
3572 if p_validate then
3573 raise hr_api.validate_enabled;
3574 end if;
3575 --+
3576 -- Set all IN OUT and OUT parameters with out values
3577 --+
3578 p_object_version_number := l_acu_object_version_number;
3579 p_start_date := l_acu_start_date;
3580 p_end_date := l_acu_end_date;
3581 --+
3582 hr_utility.set_location(' Leaving:'||l_proc, 70);
3583 exception
3584 when hr_api.validate_enabled then
3585 --+
3586 -- As the Validate_Enabled exception has been raised
3587 -- we must rollback to the savepoint
3588 --+
3589 rollback to delete_ame_rule_action ;
3590 --+
3591 -- Reset IN OUT parameters and set OUT parameters
3592 -- (Any key or derived arguments must be set to null
3593 -- when validation only mode is being used.)
3594 --+
3595 p_object_version_number := null;
3596 p_start_date := null;
3597 p_end_date := null;
3598 --+
3599 hr_utility.set_location(' Leaving:'||l_proc, 80);
3600 when others then
3601 --+
3602 -- A validation or unexpected error has occured
3603 --+
3604 rollback to delete_ame_rule_action ;
3605 --+
3606 -- Reset IN OUT parameters and set all
3607 -- OUT parameters, including warnings, to null
3608 --+
3609 p_object_version_number := null;
3610 p_start_date := null;
3611 p_end_date := null;
3612 --+
3613 hr_utility.set_location(' Leaving:'||l_proc, 90);
3614 raise;
3615 end delete_ame_rule_action ;
3616 --
3617 -- ----------------------------------------------------------------------------
3618 -- |------------------<replace_lm_condition>----------------------------------|
3619 -- ----------------------------------------------------------------------------
3620 --
3621 procedure replace_lm_condition
3622 (p_validate in boolean default false
3623 ,p_rule_id in number
3624 ,p_condition_id in number
3625 ,p_object_version_number out nocopy number
3626 ,p_start_date out nocopy date
3627 ,p_end_date out nocopy date
3628 ) is
3629 --
3630 -- Declare cursors and local variables
3631 --
3632 l_proc varchar2(72) := g_package||'replace_lm_condition';
3633 l_dummy varchar2(10);
3634 l_swi_package_name varchar2(30) := 'AME_RULE_SWI';
3635 l_effective_date date;
3636 l_condition_type ame_conditions.condition_type%type;
3637 l_attribute_id number;
3638 --+
3639 cursor getConditionDetails is
3640 select condition_type
3641 ,attribute_id
3642 from ame_conditions
3643 where condition_id = p_condition_id
3644 and l_effective_date between start_date
3645 and nvl(end_date - ame_util.oneSecond,l_effective_date);
3646 --+
3647 cursor getRuleDetails is
3648 select rule_type
3649 ,start_date
3650 ,end_date
3651 ,item_class_id
3652 from ame_rules
3653 where rule_id = p_rule_id
3654 and (l_effective_date between start_date
3655 and nvl(end_date - ame_util.oneSecond,l_effective_date) or
3656 (l_effective_date < start_date
3657 and start_date < nvl(end_date,start_date + ame_util.oneSecond)));
3658 --+
3659 cursor getApplicationIds(p_rule_id in integer) is
3660 select distinct item_id
3661 from ame_rule_usages
3662 where rule_id = p_rule_id
3663 and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
3664 or
3665 (sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
3666 );
3667 --+
3668 l_rule_type ame_rules.rule_type%type;
3669 l_item_class_id number;
3670 l_rul_start_date date;
3671 l_rul_end_date date;
3672 l_count number;
3673 appIdList ame_util.idList;
3674 l_action_id_list ame_util.idList;
3675 l_old_condition_id number;
3676 l_cnu_object_version_number number;
3677 l_cnu_start_date date;
3678 l_cnu_end_date date;
3679 l_new_cnu_start_date date;
3680 begin
3681 hr_utility.set_location('Entering:'|| l_proc, 10);
3682 --
3683 -- Issue a savepoint
3684 --
3685 savepoint replace_lm_condition;
3686 --
3687 -- Call Before Process User Hook
3688 --
3689 begin
3690 ame_rule_bk10.replace_lm_condition_b
3691 (p_rule_id => p_rule_id
3692 ,p_condition_id => p_condition_id
3693 );
3694 exception
3695 when hr_api.cannot_find_prog_unit then
3696 hr_api.cannot_find_prog_unit_error
3697 (p_module_name => 'replace_lm_condition'
3698 ,p_hook_type => 'BP'
3699 );
3700 end;
3701 -- Process Logic
3702 --
3703 -- Set the effective date to the sysdate
3704 l_effective_date := sysdate;
3705 --+
3706 --+ Check Rule Id.
3707 --+
3708 ame_rule_utility_pkg.checkRuleId(p_rule_id => p_rule_id);
3709 --+
3710 --+ Check Condition Id.
3711 --+
3712 ame_rule_utility_pkg.checkConditionId(p_condition_id => p_condition_id);
3713 --
3714 -- Fetch the condition details
3715 --
3716 open getConditionDetails;
3717 fetch getConditionDetails
3718 into l_condition_type
3719 ,l_attribute_id ;
3720 if getConditionDetails%notfound then
3721 fnd_message.set_name('PER','AME_400494_INVALID_CONDITION');
3722 hr_multi_message.add(p_associated_column1 => 'CONDITION_ID');
3723 end if;
3724 close getConditionDetails;
3725 --
3726 -- Fetch the rule details
3727 --
3728 open getRuleDetails;
3729 fetch getRuleDetails
3730 into l_rule_type
3731 ,l_rul_start_date
3732 ,l_rul_end_date
3733 ,l_item_class_id;
3734 if getRuleDetails%notfound then
3735 fnd_message.set_name('PER','AME_400480_INV_RULE_ID');
3736 hr_multi_message.add(p_associated_column1 => 'RULE_ID');
3737 end if;
3738 close getRuleDetails;
3739 --+
3740 --+ Error out if condition is not LM.
3741 --+
3742 if l_condition_type <> ame_util.listModConditionType then
3743 fnd_message.set_name('PER','AME_400776_NON_LM_COND');
3744 hr_multi_message.add (p_associated_column1 => 'CONDITION_ID');
3745 end if;
3746 --+
3747 --+ Error out if rule is not LM/SUB/ LMCOMB.
3748 --+
3749 if (l_rule_type = ame_util.combinationRuleType and not ame_rule_utility_pkg.is_LM_comb_rule(p_rule_id)) then
3750 fnd_message.set_name('PER','AME_400777_NON_LM_SUB_RULE');
3751 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
3752 end if;
3753 if l_rule_type not in(ame_util.listModRuleType
3754 ,ame_util.substitutionRuleType) then
3755 fnd_message.set_name('PER','AME_400777_NON_LM_SUB_RULE');
3756 hr_multi_message.add (p_associated_column1 => 'RULE_ID');
3757 end if;
3758 --+
3759 --+ Get the Old Condition ID
3760 --+
3761 select cnu.condition_id
3762 into l_old_condition_id
3763 from ame_condition_usages cnu
3764 ,ame_conditions cnd
3765 where cnu.rule_id = p_rule_id
3766 and cnu.condition_id = cnd.condition_id
3767 and cnd.condition_type = ame_util.listModConditionType
3768 and sysdate between cnd.start_date and nvl(cnd.end_date - (1/86400),sysdate)
3769 and ((sysdate between cnu.start_date and nvl(cnu.end_date - (1/86400),sysdate))
3770 or
3771 (sysdate < cnu.start_date and cnu.start_date < nvl(cnu.end_date, cnu.start_date + (1/86400)))
3772 );
3773 --+
3774 --+ Error out if the new condition id is same as current condition id.
3775 --+
3776 if l_old_condition_id = p_condition_id then
3777 fnd_message.set_name('PER','AME_400778_DIFF_LM_COND');
3778 hr_multi_message.add (p_associated_column1 => 'CONDITION_ID');
3779 end if;
3780 --+
3781 --+ Check if this condition is valid in all the transaction tyes having this rule.
3782 --+
3783 open getApplicationIds(p_rule_id => p_rule_id);
3784 fetch getApplicationIds
3785 bulk collect into appIdList;
3786 for i in 1..appIdList.count loop
3787 if not ame_rule_utility_pkg.is_condition_allowed(p_application_id => appIdList(i)
3788 ,p_condition_id => p_condition_id) then
3789 fnd_message.set_name('PER','AME_400738_COND_NOT_IN_APP');
3790 hr_multi_message.add (p_associated_column1 => 'ITEM_ID');
3791 end if;
3792 end loop;
3793 --+
3794 --+ Verify the actions and conditions
3795 --+
3796 getActionIds(ruleIdIn => p_rule_id
3797 ,actionIdListOut => l_action_id_list);
3798 for i in 1..l_action_id_list.count loop
3799 ame_rule_utility_pkg.chk_LM_action_Condition(p_condition_id => p_condition_id
3800 ,p_action_id => l_action_id_list(i)
3801 ,is_first_condition => true);
3802 end loop;
3803 --+
3804 --+ Delete the old condition from the rule.
3805 --+
3806 select object_version_number
3807 ,start_date
3808 ,end_date
3809 into l_cnu_object_version_number
3810 ,l_cnu_start_date
3811 ,l_cnu_end_date
3812 from ame_condition_usages
3813 where condition_id = l_old_condition_id
3814 and rule_id = p_rule_id
3815 and ((sysdate between start_date and nvl(end_date - (1/86400),sysdate))
3816 or
3817 (sysdate < start_date and start_date < nvl(end_date, start_date + (1/86400)))
3818 );
3819 ame_cnu_del.del(p_effective_date => l_effective_date
3820 ,p_datetrack_mode => hr_api.g_delete
3821 ,p_rule_id => p_rule_id
3822 ,p_condition_id => l_old_condition_id
3823 ,p_object_version_number => l_cnu_object_version_number
3824 ,p_start_date => l_cnu_start_date
3825 ,p_end_date => l_new_cnu_start_date
3826 );
3827 l_cnu_start_date := l_new_cnu_start_date;
3828 --+
3829 --+ Insert the new condition for the rule.
3830 --+
3831 ame_cnu_ins.ins(p_rule_id => p_rule_id
3832 ,p_condition_id => p_condition_id
3833 ,p_effective_date => l_effective_date
3834 ,p_object_version_number => l_cnu_object_version_number
3835 ,p_start_date => l_cnu_start_date
3836 ,p_end_date => l_cnu_end_date
3837 );
3838 --
3839 -- Call After Process User Hook
3840 --
3841 begin
3842 ame_rule_bk10.replace_lm_condition_a
3843 (p_rule_id => p_rule_id
3844 ,p_condition_id => p_condition_id
3845 ,p_object_version_number => l_cnu_object_version_number
3846 ,p_start_date => l_cnu_start_date
3847 ,p_end_date => l_cnu_end_date
3848 );
3849 exception
3850 when hr_api.cannot_find_prog_unit then
3851 hr_api.cannot_find_prog_unit_error
3852 (p_module_name => 'replace_lm_condition'
3853 ,p_hook_type => 'AP'
3854 );
3855 end;
3856 --
3857 -- When in validation only mode raise the Validate_Enabled exception
3858 --
3859 if p_validate then
3860 raise hr_api.validate_enabled;
3861 end if;
3862 --
3863 -- Set all IN OUT and OUT parameters with out values
3864 --
3865 p_object_version_number := l_cnu_object_version_number;
3866 p_start_date := l_cnu_start_date;
3867 p_end_date := l_cnu_end_date;
3868 --
3869 hr_utility.set_location(' Leaving:'||l_proc, 70);
3870 exception
3871 when hr_api.validate_enabled then
3872 --
3873 -- As the Validate_Enabled exception has been raised
3874 -- we must rollback to the savepoint
3875 --
3876 rollback to replace_lm_condition;
3877 --
3878 -- Reset IN OUT parameters and set OUT parameters
3879 -- (Any key or derived arguments must be set to null
3880 -- when validation only mode is being used.)
3881 --
3882 p_object_version_number := null;
3883 p_start_date := null;
3884 p_end_date := null;
3885 hr_utility.set_location(' Leaving:'||l_proc, 80);
3886 when others then
3887 --
3888 -- A validation or unexpected error has occured
3889 --
3890 rollback to replace_lm_condition;
3891 --
3892 -- Reset IN OUT parameters and set all
3893 -- OUT parameters, including warnings, to null
3894 --
3895 p_object_version_number := null;
3896 p_start_date := null;
3897 p_end_date := null;
3898 hr_utility.set_location(' Leaving:'||l_proc, 90);
3899 raise;
3900 end replace_lm_condition;
3901 end ame_rule_api;