[Home] [Help]
PACKAGE BODY: APPS.EDR_RULE_TEMP
Source
1 PACKAGE BODY EDR_RULE_TEMP AS
2 /* $Header: EDRTEMPB.pls 120.4.12000000.1 2007/01/18 05:55:41 appldev ship $ */
3 -- Bug 5167817 : start
4
5 procedure getRuleDetails( ruleIdIn in number,
6 ruleTypeOut out nocopy varchar2,
7 ruleDescriptionOut out nocopy varchar2,
8 conditionIdsOut out nocopy ame_util.idList,
9 conditionDescriptionsOut out nocopy ame_util.longestStringList,
10 conditionHasLOVsOut out nocopy ame_util.charList,
11 approvalTypeNameOut out nocopy varchar2,
12 approvalTypeDescriptionOut out nocopy varchar2,
13 approvalDescriptionOut out nocopy varchar2) IS
14
15 l_approvalTypeNames ame_util.stringList;
16 l_approvalTypeDescriptions ame_util.stringList;
17 l_approvalDescriptions ame_util.stringList;
18 Begin
19 ame_api3.getRuleDetails3( ruleIdIn => ruleIdIn,
20 ruleTypeOut => ruleTypeOut,
21 ruleDescriptionOut =>ruleDescriptionOut,
22 conditionIdsOut => conditionIdsOut,
23 conditionDescriptionsOut =>conditionDescriptionsOut,
24 conditionHasLOVsOut =>conditionHasLOVsOut,
25 actionTypeNamesOut => l_approvalTypeNames,
26 actionTypeDescriptionsOut => l_approvalTypeDescriptions,
27 actionDescriptionsOut => l_approvalDescriptions);
28
29 if l_approvalTypeNames.count = 0 then
30 approvalTypeNameOut := null;
31 approvalTypeDescriptionOut := null;
32 approvalDescriptionOut := null;
33 else
34 approvalTypeNameOut := l_approvalTypeNames(1);
35 approvalTypeDescriptionOut := l_approvalTypeDescriptions(1);
36 approvalDescriptionOut := l_approvalDescriptions(1);
37 end if;
38
39 END getRuleDetails;
40
41
42
43 -- Bug 5167817 : end
44 /* Obtain rule details for certain trasaction, and insert into temp tables */
45 PROCEDURE GET_DETAILS_TRANS ( p_trans_id IN VARCHAR2, p_input_var IN VARCHAR2
46 ) IS
47 l_ssnid NUMBER;
48 ith NUMBER;
49 l_apps_id NUMBER;
50 l_apps_name VARCHAR2(240);
51
52 l_rule_ids ame_util.idList;
53 l_rule_id_t ame_util.idList;
54 l_date_0s edr_rule_temp.edr_array_date;
55 l_date_1s edr_rule_temp.edr_array_date;
56
57 l_rule_name VARCHAR2(100);
58 l_count_num NUMBER;
59 jth NUMBER;
60 l_deft_use VARCHAR2(1);
61
62 l_rule_type VARCHAR2(100);
63 l_rule_desc VARCHAR2(100);
64 l_cond_ids ame_util.idList;
65 l_cond_desc ame_util.longestStringList;
66 l_cond_lov ame_util.charList;
67 l_appr_name VARCHAR2(100);
68 l_appr_type VARCHAR2(100);
69 l_appr_desc VARCHAR2(100);
70
71 BEGIN
72
73 select USERENV('SESSIONID') into l_ssnid from dual;
74
75 -- 3171627 note: this procedure is obsolete due to new ConfigVar ERES key.
76 select distinct application_id, application_name into l_apps_id, l_apps_name
77 from ame_calling_apps
78 where transaction_type_id = p_trans_id
79 --Bug 4652277: Start
80 --and end_Date is null;
81 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
82 --Bug 4652277: End
83
84 -- 3171627 end: restrict end_date for valid transaction with new description
85
86 /* bulk collect use implicit cursor to pull out rule details from rule_usages */
87
88 select distinct rule_id, start_date, end_date bulk collect
89 into l_rule_ids, l_date_0s, l_date_1s from ame_rule_usages
90 --Bug 4652277: Start
91 --where item_id = l_apps_id and (end_date is null OR end_date > sysdate);
92 where item_id = l_apps_id and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
93 --Bug 4652277: End
94
95 FOR ith IN 1..l_rule_ids.count LOOP
96
97 BEGIN
98
99 /* determine if the rule defines its own value for the variable */
100
101 -- 3171627 note: deleted rule doesn't count for transVar default usage
102 select count(*) into l_count_num from ame_rules where rule_id = l_rule_ids(ith)
103 and description in ( select distinct rule_name from edr_amerule_input_var
104 where ame_trans_name = l_apps_name and input_name = p_input_var )
105 --Bug 4652277: Start
106 -- and (end_date is null or end_date > sysdate);
107 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
108 --Bug 4652277: End
109
110
111 -- 3171627 end: shared rule may be valid in rule_usages but deleted for particular trans
112
113 IF l_count_num > 0 THEN
114 l_deft_use := 'N';
115 ELSE
116 l_deft_use := 'Y';
117 END IF;
118 -- Bug 5167817 : start
119 getRuleDetails ( RULEIDIN => l_rule_ids(ith),
120 RULETYPEOUT => l_rule_type, RULEDESCRIPTIONOUT => l_rule_desc,
121 CONDITIONIDSOUT => l_cond_ids, CONDITIONDESCRIPTIONSOUT => l_cond_desc,
122 CONDITIONHASLOVSOUT => l_cond_lov, APPROVALTYPENAMEOUT => l_appr_name,
123 APPROVALTYPEDESCRIPTIONOUT => l_appr_type, APPROVALDESCRIPTIONOUT => l_appr_desc );
124 -- Bug 5167817 : end
125 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
126 rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
127 ( l_ssnid, p_trans_id, l_rule_ids(ith), l_rule_desc, l_rule_type, l_appr_type,
128 l_appr_desc, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
129
130 FOR jth IN 1..l_cond_ids.count LOOP
131 insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id,
132 condition_id, condition_desc ) values ( l_ssnid, p_trans_id, l_rule_ids(ith),
133 l_cond_ids(jth), l_cond_desc(jth) );
134 END LOOP;
135
136 EXCEPTION
137 WHEN NO_DATA_FOUND THEN
138 select distinct description into l_rule_name from ame_rules where rule_id = l_rule_ids(ith);
139
140 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
141 default_var, start_date, end_date) values ( l_ssnid, p_trans_id, l_rule_ids(ith),
142 l_rule_name, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
143 /* insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id,
144 condition_id, condition_desc )
145 values ( l_ssnid, p_trans_id, l_rule_ids(ith), null, null ); */
146 END;
147
148 END LOOP;
149
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_TRANS') );
153 WHEN TOO_MANY_ROWS THEN
154 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_UNIQUE') );
155
156 END GET_DETAILS_TRANS;
157
158
159
160 PROCEDURE GET_DETAILS_RULE ( p_trans_name IN VARCHAR2, p_rule_name IN VARCHAR2
161 ) IS
162 l_ssnid NUMBER;
163 l_trans_id VARCHAR2(100);
164 l_apps_id NUMBER;
165 jth NUMBER;
166 l_rule_id NUMBER;
167 l_date_frm DATE;
168 l_date_end DATE;
169
170 l_rule_type VARCHAR2(100);
171 l_rule_desc VARCHAR2(100);
172 l_cond_ids ame_util.idList;
173 l_cond_desc ame_util.longestStringList;
174 l_cond_lov ame_util.charList;
175 l_appr_name VARCHAR2(100);
176 l_appr_type VARCHAR2(100);
177 l_appr_desc VARCHAR2(100);
178
179 BEGIN
180 select USERENV('SESSIONID') into l_ssnid from dual;
181
182 -- 3171627 note: this procedure is obsolete. ame_calling_apps.end_date cannot be in future.
183 select transaction_type_id, application_id into l_trans_id, l_apps_id
184 from ame_calling_apps
185 where application_name = p_trans_name
186 --Bug 4652277: Start
187 --and end_Date is null;
188 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
189 --Bug 4652277: End
190
191 -- 3171627 end: restrict end_date for valid transaction with new description
192
193 -- 3171627 note: this procedure is obsolete due to new ConfigVar ERES key.
194 select distinct rule_id into l_rule_id from ame_rules where description = p_rule_name
195 and rule_id in ( select distinct rule_id from ame_rule_usages where item_id = l_apps_id )
196 --Bug 4652277: Start
197 --and (end_date is null or end_date > sysdate);
198 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
199 --Bug 4652277: End
200
201 -- 3171627 end: to avoid the deleted rule being recreated for the same transaction
202
203 select start_date, end_date into l_date_frm, l_date_end
204 from ame_rule_usages
205 where rule_id = l_rule_id
206 --Bug 4652277: Start
207 --and (end_date is null or end_date > sysdate);
208 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
209 --Bug 4652277: End
210
211 -- 3171627 end: the usage information will be the same for all transaction if the rule is shared
212
213 BEGIN
214 -- Bug 5167817 : start
215 getRuleDetails ( RULEIDIN => l_rule_id,
216 RULETYPEOUT => l_rule_type, RULEDESCRIPTIONOUT => l_rule_desc,
217 CONDITIONIDSOUT => l_cond_ids, CONDITIONDESCRIPTIONSOUT => l_cond_desc,
218 CONDITIONHASLOVSOUT => l_cond_lov, APPROVALTYPENAMEOUT => l_appr_name,
219 APPROVALTYPEDESCRIPTIONOUT => l_appr_type,
220 APPROVALDESCRIPTIONOUT => l_appr_desc );
221 -- Bug 5167817 : end
222
223 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
224 rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
225 ( l_ssnid, l_trans_id, l_rule_id, l_rule_desc, l_rule_type, l_appr_type,
226 l_appr_desc, 'N', l_date_frm, l_date_end );
227
228 FOR jth IN 1..l_cond_ids.count LOOP
229 insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
230 condition_desc ) values ( l_ssnid, l_trans_id, l_rule_id, l_cond_ids(jth), l_cond_desc(jth) );
231 END LOOP;
232
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name, default_var,
236 start_date, end_date) values
237 ( l_ssnid, l_trans_id, l_rule_id, p_rule_name, 'N', l_date_frm, l_date_end );
238 END;
239
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_FOUND') );
243 WHEN OTHERS THEN
244 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_UNIQUE') );
245
246 END GET_DETAILS_RULE;
247
248
249 PROCEDURE CLEAR_DETAILS_TRANS ( p_trans_id IN VARCHAR2
250 ) IS
251 l_ssnid NUMBER;
252 BEGIN
253 select USERENV('SESSIONID') into l_ssnid from dual;
254
255 delete edr_rule_detail_temp where transaction_type_id = p_trans_id and SESSION_ID = l_ssnid;
256
257 delete edr_rule_condition_temp where transaction_type_id = p_trans_id and SESSION_ID = l_ssnid;
258
259 END CLEAR_DETAILS_TRANS;
260
261
262 PROCEDURE CLEAR_DETAILS_RULE ( p_trans_id IN VARCHAR2,
263 p_rule_id IN VARCHAR2
264 ) IS
265 l_ssnid NUMBER;
266 l_apps_id NUMBER;
267 BEGIN
268 select USERENV('SESSIONID') into l_ssnid from dual;
269
270 delete edr_rule_detail_temp where transaction_type_id = p_trans_id
271 and RULE_ID = p_rule_id and SESSION_ID = l_ssnid;
272
273 delete edr_rule_condition_temp where transaction_type_id = p_trans_id
274 and RULE_ID = p_rule_id and SESSION_ID = l_ssnid;
275
276 END CLEAR_DETAILS_RULE;
277
278
279 PROCEDURE GET_TVAR_RULE_DETAIL ( p_trans_var IN VARCHAR2 ) IS
280 p_trans_id VARCHAR2(100);
281 p_input_var VARCHAR2(100);
282 l_ssnid NUMBER;
283 ith NUMBER;
284 l_apps_id NUMBER;
285 l_apps_name VARCHAR2(240);
286
287 l_rule_ids ame_util.idList;
288 l_rule_id_t ame_util.idList;
289 l_date_0s edr_rule_temp.edr_array_date;
290 l_date_1s edr_rule_temp.edr_array_date;
291
292 l_rule_name VARCHAR2(100);
293 l_count_num NUMBER;
294 jth NUMBER;
295 l_deft_use VARCHAR2(1);
296
297 l_rule_type VARCHAR2(100);
298 l_rule_desc VARCHAR2(100);
299 l_cond_ids ame_util.idList;
300 l_cond_desc ame_util.longestStringList;
301 l_cond_lov ame_util.charList;
302 l_appr_name VARCHAR2(100);
303 l_appr_type VARCHAR2(100);
304 l_appr_desc VARCHAR2(100);
305
306 BEGIN
307 select substr( p_trans_var, 1, instr(p_trans_var,'-')-1 ) into p_trans_id from dual;
308 select substr( p_trans_var, 1+instr(p_trans_var,'-') ) into p_input_var from dual;
309
310 select USERENV('SESSIONID') into l_ssnid from dual;
311
312 -- ame_calling_apps' end_date can only be null or outdated, no end_date>sysdate
313 select distinct application_id, application_name into l_apps_id, l_apps_name
314 from ame_calling_apps
315 where transaction_type_id = p_trans_id
316 --Bug 4652277: Start
317 --AND end_date is null ;
318 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
319 --Bug 4652277: End
320
321
322 select distinct rule_id, start_date, end_date bulk collect
323 into l_rule_ids, l_date_0s, l_date_1s from ame_rule_usages
324 where item_id = l_apps_id
325 --Bug 4652277: Start
326 --and (end_date is null OR end_date > sysdate);
327 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
328 --Bug 4652277: End
329
330 FOR ith IN 1..l_rule_ids.count LOOP
331
332 BEGIN
333
334 select count(*) into l_count_num from ame_rules where rule_id = l_rule_ids(ith)
335 and description in ( select distinct rule_name from edr_amerule_input_var
336 where ame_trans_name = l_apps_name and input_name = p_input_var );
337
338 IF l_count_num > 0 THEN
339 l_deft_use := 'N';
340 ELSE
341 l_deft_use := 'Y';
342 END IF;
343
344 -- Bug 5167817 : start
345
346 getRuleDetails ( RULEIDIN => l_rule_ids(ith),
347 RULETYPEOUT => l_rule_type, RULEDESCRIPTIONOUT => l_rule_desc,
348 CONDITIONIDSOUT => l_cond_ids, CONDITIONDESCRIPTIONSOUT => l_cond_desc,
349 CONDITIONHASLOVSOUT => l_cond_lov, APPROVALTYPENAMEOUT => l_appr_name,
350 APPROVALTYPEDESCRIPTIONOUT => l_appr_type, APPROVALDESCRIPTIONOUT => l_appr_desc );
351 -- Bug 5167817 : end
352 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
353 rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
354 ( l_ssnid, p_trans_id, l_rule_ids(ith), l_rule_desc, l_rule_type, l_appr_type,
355 l_appr_desc, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
356
357 FOR jth IN 1..l_cond_ids.count LOOP
358 insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
359 condition_desc ) values
360 ( l_ssnid, p_trans_id, l_rule_ids(ith), l_cond_ids(jth), l_cond_desc(jth) );
361 END LOOP;
362
363 EXCEPTION
364 WHEN NO_DATA_FOUND THEN
365 select distinct description into l_rule_name from ame_rules where rule_id = l_rule_ids(ith);
366 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name, default_var,
367 start_date, end_date) values ( l_ssnid, p_trans_id, l_rule_ids(ith),
368 l_rule_name, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
369 /* insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
370 condition_desc ) values ( l_ssnid, p_trans_id, l_rule_ids(ith), null, null ); */
371 END;
372
373 END LOOP;
374
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_TRANS') );
378 WHEN TOO_MANY_ROWS THEN
379 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_UNIQUE') );
380
381 END GET_TVAR_RULE_DETAIL;
382
383
384 PROCEDURE GET_RVAR_RULE_DETAIL ( p_trans_rule IN VARCHAR2 ) IS
385 p_trans_name VARCHAR2(240);
386 p_rule_id NUMBER;
387 p_rule_name VARCHAR2(100);
388 l_position NUMBER;
389 l_ssnid NUMBER;
390 l_trans_id VARCHAR2(100);
391 l_apps_id NUMBER;
392 jth NUMBER;
393 l_rule_id NUMBER;
394 l_date_frm DATE;
395 l_date_end DATE;
396
397 l_rule_type VARCHAR2(100);
398 l_rule_desc VARCHAR2(100);
399 l_cond_ids ame_util.idList;
400 l_cond_desc ame_util.longestStringList;
401 l_cond_lov ame_util.charList;
402 l_appr_name VARCHAR2(100);
403 l_appr_type VARCHAR2(100);
404 l_appr_desc VARCHAR2(100);
405
406 BEGIN
407 select substr( p_trans_rule, 1, instr(p_trans_rule,'-')-1 ) into p_trans_name from dual;
408 select instr(p_trans_rule, '-', 1+instr(p_trans_rule,'-')) into l_position from dual;
409
410 --Bug : 3499311 : Start - Specified Number Format in call TO_NUMBER
411 select TO_NUMBER( substr( p_trans_rule, 1+instr(p_trans_rule,'-'),
412 l_position - instr(p_trans_rule,'-') - 1 ),'999999999999.999999' ) into p_rule_id from dual;
413 --Bug : 3499311 : End
414
415 select USERENV('SESSIONID') into l_ssnid from dual;
416
417 -- ame_calling_apps' end_date can only be null or outdated, transName/transId must be unique
418 select transaction_type_id, application_id into l_trans_id, l_apps_id
419 from ame_calling_apps
420 where application_name = p_trans_name
421 --Bug 4652277: Start
422 --and end_date is null;
423 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
424 --Bug 4652277: End
425
426
427 l_rule_id := p_rule_id;
428
429 -- 3171627 start: rule name can be changed in AME
430 select distinct description into p_rule_name from ame_rules
431 where rule_id = l_rule_id
432 --Bug 4652277: Start
433 --and (end_date is null or end_date > sysdate);
434 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
435 --Bug 4652277: End
436
437 -- 3171627 end: add constraint on end_date
438
439 select start_date, end_date into l_date_frm, l_date_end
440 from ame_rule_usages where rule_id = l_rule_id and item_id = l_apps_id
441 --Bug 4652277: Start
442 --and (end_date is null OR end_date > sysdate);
443 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
444 --Bug 4652277: End
445
446 BEGIN
447 -- Bug 5167817 : start
448 getRuleDetails ( RULEIDIN => l_rule_id,
449 RULETYPEOUT => l_rule_type, RULEDESCRIPTIONOUT => l_rule_desc,
450 CONDITIONIDSOUT => l_cond_ids, CONDITIONDESCRIPTIONSOUT => l_cond_desc,
451 CONDITIONHASLOVSOUT => l_cond_lov, APPROVALTYPENAMEOUT => l_appr_name,
452 APPROVALTYPEDESCRIPTIONOUT => l_appr_type,
453 APPROVALDESCRIPTIONOUT => l_appr_desc );
454 -- Bug 5167817 : end
455 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
456 rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
457 ( l_ssnid, l_trans_id, l_rule_id, l_rule_desc, l_rule_type, l_appr_type,
458 l_appr_desc, 'N', l_date_frm, l_date_end );
459
460 FOR jth IN 1..l_cond_ids.count LOOP
461 insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
462 condition_desc ) values ( l_ssnid, l_trans_id, l_rule_id, l_cond_ids(jth), l_cond_desc(jth) );
463 END LOOP;
464
465 EXCEPTION
466 WHEN NO_DATA_FOUND THEN
467 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
468 default_var, start_date, end_date) values
469 ( l_ssnid, l_trans_id, l_rule_id, p_rule_name, 'N', l_date_frm, l_date_end );
470 END;
471
472 EXCEPTION
473 WHEN NO_DATA_FOUND THEN
474 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_FOUND') );
475 WHEN OTHERS THEN
476 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_UNIQUE') );
477
478 END GET_RVAR_RULE_DETAIL;
479
480
481 /* 3016075: event key changed to instance id, use it to find input var */
482 PROCEDURE GET_TRANS_RULES ( p_trans_config_id IN VARCHAR2 ) IS
483 p_trans_id VARCHAR2(100);
484 p_input_var VARCHAR2(100);
485 l_ssnid NUMBER;
486 ith NUMBER;
487 l_apps_id NUMBER;
488 l_apps_name VARCHAR2(240);
489
490 l_rule_ids ame_util.idList;
491 l_rule_id_t ame_util.idList;
492 l_date_0s edr_rule_temp.edr_array_date;
493 l_date_1s edr_rule_temp.edr_array_date;
494
495 l_rule_name VARCHAR2(100);
496 l_count_num NUMBER;
497 jth NUMBER;
498 l_deft_use VARCHAR2(1);
499
500 l_rule_type VARCHAR2(100);
501 l_rule_desc VARCHAR2(100);
502 l_cond_ids ame_util.idList;
503 l_cond_desc ame_util.longestStringList;
504 l_cond_lov ame_util.charList;
505 l_appr_name VARCHAR2(100);
506 l_appr_type VARCHAR2(100);
507 l_appr_desc VARCHAR2(100);
508
509 BEGIN
510
511 --Bug : 3499311 : Start - Specified Number Format in call TO_NUMBER
512 select distinct ame_trans_id, input_name into p_trans_id, p_input_var
513 from edr_ametran_input_var where tran_config_id = TO_NUMBER(p_trans_config_id,'999999999999.999999');
514 --Bug : 3499311 : End
515
516 select USERENV('SESSIONID') into l_ssnid from dual;
517
518 select distinct application_id, application_name into l_apps_id, l_apps_name
519 from ame_calling_apps
520 where transaction_type_id = p_trans_id
521 --Bug 4652277: Start
522 --and end_date is null;
523 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
524 --Bug 4652277: End
525
526 select distinct rule_id, start_date, end_date bulk collect
527 into l_rule_ids, l_date_0s, l_date_1s from ame_rule_usages
528 where item_id = l_apps_id
529 --Bug 4652277: Start
530 --and (end_date is null OR end_date > sysdate);
531 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
532 --Bug 4652277: End
533
534 FOR ith IN 1..l_rule_ids.count LOOP
535
536 BEGIN
537
538 -- 3075902 note: trans/ruleName may be changed and names in ruleVar table become old
539 -- no need for end_date constraint because it's validated above
540 select count(*) into l_count_num from ame_rules where rule_id = l_rule_ids(ith)
541 and description in ( select distinct rule_name from edr_amerule_input_var
542 where ame_trans_name in (select distinct application_name from ame_calling_apps
543 where transaction_type_id = p_trans_id) and input_name = p_input_var );
544 -- 3075902 fix doesn't help here if ruleVar not touched but need correct default usage
545 -- 3075902 end: change equal test on transName to IN operation based on transId
546
547 IF l_count_num > 0 THEN
548 l_deft_use := 'N';
549 ELSE
550 l_deft_use := 'Y';
551 END IF;
552
553 -- Bug 5167817 : start
554 getRuleDetails ( RULEIDIN => l_rule_ids(ith),
555 RULETYPEOUT => l_rule_type, RULEDESCRIPTIONOUT => l_rule_desc,
556 CONDITIONIDSOUT => l_cond_ids, CONDITIONDESCRIPTIONSOUT => l_cond_desc,
557 CONDITIONHASLOVSOUT => l_cond_lov, APPROVALTYPENAMEOUT => l_appr_name,
558 APPROVALTYPEDESCRIPTIONOUT => l_appr_type, APPROVALDESCRIPTIONOUT => l_appr_desc );
559 -- Bug 5167817 : end
560 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
561 rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
562 ( l_ssnid, p_trans_id, l_rule_ids(ith), l_rule_desc, l_rule_type, l_appr_type,
563 l_appr_desc, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
564
565 FOR jth IN 1..l_cond_ids.count LOOP
566 insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
567 condition_desc ) values
568 ( l_ssnid, p_trans_id, l_rule_ids(ith), l_cond_ids(jth), l_cond_desc(jth) );
569 END LOOP;
570
571 EXCEPTION
572 WHEN NO_DATA_FOUND THEN
573 -- 3171627 start: rule description can be changed in AME, rule_usages validation doesn't help here
574 select distinct description into l_rule_name from ame_rules
575 where rule_id = l_rule_ids(ith) and (end_date is null or end_date > sysdate);
576 -- 3171627 end: add end_date constraint to prevent multiple matches
577 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name, default_var,
578 start_date, end_date) values ( l_ssnid, p_trans_id, l_rule_ids(ith),
579 l_rule_name, l_deft_use, l_date_0s(ith), l_date_1s(ith) );
580 /* insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
581 condition_desc ) values ( l_ssnid, p_trans_id, l_rule_ids(ith), null, null ); */
582 END;
583
584 END LOOP;
585
586 EXCEPTION
587 WHEN NO_DATA_FOUND THEN
588 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_TRANS') );
589 WHEN OTHERS THEN
590 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_UNIQUE') );
591
592 END GET_TRANS_RULES;
593
594
595 /* 3016075: event key changed to instance id, use it to find input var */
596 PROCEDURE GET_RULE_DETAIL ( p_rule_config_id IN VARCHAR2 ) IS
597 p_trans_name VARCHAR2(240);
598 p_rule_id NUMBER;
599 p_rule_name VARCHAR2(100);
600 l_position NUMBER;
601 l_ssnid NUMBER;
602 l_trans_id VARCHAR2(100);
603 l_apps_id NUMBER;
604 jth NUMBER;
605 l_rule_id NUMBER;
606 l_date_frm DATE;
607 l_date_end DATE;
608
609 l_rule_type VARCHAR2(100);
610 l_rule_desc VARCHAR2(100);
611 l_cond_ids ame_util.idList;
612 l_cond_desc ame_util.longestStringList;
613 l_cond_lov ame_util.charList;
614 l_appr_name VARCHAR2(100);
615 l_appr_type VARCHAR2(100);
616 l_appr_desc VARCHAR2(100);
617
618 BEGIN
619
620 --Bug : 3499311 : Start - Specified Number Format in call TO_NUMBER
621 select distinct ame_trans_name, rule_id into p_trans_name, p_rule_id
622 from edr_amerule_input_var where rule_config_id = TO_NUMBER(p_rule_config_id,'999999999999.999999');
623 --Bug : 3499311 : End
624
625 select USERENV('SESSIONID') into l_ssnid from dual;
626
627 -- 3075902 note: shall be ok below, b'coz ame_trans_name has been updated to the new name
628 select transaction_type_id, application_id into l_trans_id, l_apps_id
629 from ame_calling_apps
630 where application_name = p_trans_name
631 --Bug 4652277: Start
632 --and end_date is null;
633 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
634 --Bug 4652277: End
635
636 l_rule_id := p_rule_id;
637 -- 3171627 start: rule description can be changed in AME, rule_usages validation doesn't help here
638 select distinct description into p_rule_name from ame_rules
639 where rule_id = l_rule_id
640 --Bug 4652277: Start
641 --and (end_date is null or end_date > sysdate);
642 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
643 --Bug 4652277: End
644
645 -- 3171627 end: add end_date constraint to prevent multiple matches
646
647 select distinct start_date, end_date into l_date_frm, l_date_end
648 from ame_rule_usages where rule_id = l_rule_id and item_id = l_apps_id
649 --Bug 4652277: Start
650 --and (end_date is null OR end_date > sysdate);
651 and (sysdate between START_DATE AND NVL(END_DATE, SYSDATE));
652 --Bug 4652277: End
653
654 BEGIN
655 -- Bug 5167817 : start
656 getRuleDetails ( RULEIDIN => l_rule_id,
657 RULETYPEOUT => l_rule_type, RULEDESCRIPTIONOUT => l_rule_desc,
658 CONDITIONIDSOUT => l_cond_ids, CONDITIONDESCRIPTIONSOUT => l_cond_desc,
659 CONDITIONHASLOVSOUT => l_cond_lov, APPROVALTYPENAMEOUT => l_appr_name,
660 APPROVALTYPEDESCRIPTIONOUT => l_appr_type,
661 APPROVALDESCRIPTIONOUT => l_appr_desc );
662 -- Bug 5167817 : end
663 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
664 rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
665 ( l_ssnid, l_trans_id, l_rule_id, l_rule_desc, l_rule_type, l_appr_type,
666 l_appr_desc, 'N', l_date_frm, l_date_end );
667
668 FOR jth IN 1..l_cond_ids.count LOOP
669 insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
670 condition_desc ) values ( l_ssnid, l_trans_id, l_rule_id, l_cond_ids(jth), l_cond_desc(jth) );
671 END LOOP;
672
673 EXCEPTION
674 WHEN NO_DATA_FOUND THEN
675 insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
676 default_var, start_date, end_date) values
677 ( l_ssnid, l_trans_id, l_rule_id, p_rule_name, 'N', l_date_frm, l_date_end );
678 END;
679
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_FOUND') );
683 WHEN OTHERS THEN
684 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_PLS_RULE_NOT_UNIQUE') );
685
686 END GET_RULE_DETAIL;
687
688
689 --This API is used to set the transaction details in the temp tables
690 --for the specified transaction ID and variable name.
691 PROCEDURE SET_TRANSACTION_DETAILS(P_TRANSACTION_ID IN VARCHAR2,
692 P_VARIABLE_NAME IN VARCHAR2)
693
694 IS
695
696 L_SSNID NUMBER;
697 ITH NUMBER;
698 L_APPS_ID NUMBER;
699 L_APPS_NAME VARCHAR2(240);
700
701 L_RULE_IDS AME_UTIL.IDLIST;
702 L_RULE_ID_T AME_UTIL.IDLIST;
703 L_DATE_0S EDR_RULE_TEMP.EDR_ARRAY_DATE;
704 L_DATE_1S EDR_RULE_TEMP.EDR_ARRAY_DATE;
705
706 L_RULE_NAME VARCHAR2(100);
707 L_COUNT_NUM NUMBER;
708 jth NUMBER;
709 L_DEFT_USE VARCHAR2(1);
710
711 L_RULE_TYPE VARCHAR2(100);
712 L_RULE_DESC VARCHAR2(100);
713 L_COND_IDS AME_UTIL.IDLIST;
714 L_COND_DESC AME_UTIL.LONGESTSTRINGLIST;
715 L_COND_LOV AME_UTIL.CHARLIST;
716 L_APPR_NAME VARCHAR2(100);
717 L_APPR_TYPE VARCHAR2(100);
718 L_APPR_DESC VARCHAR2(100);
719
720 BEGIN
721
722 --Set the session ID value.
723 SELECT USERENV('SESSIONID') INTO L_SSNID FROM DUAL;
724
725 --Obtain the application ID and application Name from the AME tables.
726 SELECT DISTINCT APPLICATION_ID, APPLICATION_NAME INTO L_APPS_ID, L_APPS_NAME
727 FROM AME_CALLING_APPS_VL
728 WHERE TRANSACTION_TYPE_ID = P_TRANSACTION_ID
729 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE);
730
731 --Obtain all the applicable AME rules in one shot.
732 SELECT DISTINCT RULE_ID, START_DATE, END_DATE BULK COLLECT
733 INTO L_RULE_IDS, L_DATE_0S, L_DATE_1S FROM AME_RULE_USAGES
734 WHERE ITEM_ID = L_APPS_ID
735 AND SYSDATE <= NVL(END_DATE,SYSDATE);
736
737
738 --Obtain and set the details of each rule associated with the transaction.
739 FOR ith IN 1..L_RULE_IDS.COUNT LOOP
740
741 BEGIN
742
743 SELECT COUNT(*)
744 INTO L_COUNT_NUM
745 FROM AME_RULES_VL AME_RULES,
746 EDR_AMERULE_INPUT_VAR RULE_VAR
747 WHERE AME_RULES.RULE_ID = L_RULE_IDS(ith)
748 AND AME_RULES.RULE_ID = RULE_VAR.RULE_ID
749 AND RULE_VAR.AME_TRANS_ID = P_TRANSACTION_ID
750 AND RULE_VAR.INPUT_NAME = P_VARIABLE_NAME
751 AND SYSDATE <= NVL(AME_RULES.END_DATE,SYSDATE);
752
753 IF L_COUNT_NUM > 0 THEN
754 L_DEFT_USE := 'N';
755 ELSE
756 L_DEFT_USE := 'Y';
757 END IF;
758
759 -- Bug 5167817 : start
760 GETRULEDETAILS (RULEIDIN => L_RULE_IDS(ith),
761 RULETYPEOUT => L_RULE_TYPE,
762 RULEDESCRIPTIONOUT => L_RULE_DESC,
763 CONDITIONIDSOUT => L_COND_IDS,
764 CONDITIONDESCRIPTIONSOUT => L_COND_DESC,
765 CONDITIONHASLOVSOUT => L_COND_LOV,
766 APPROVALTYPENAMEOUT => L_APPR_NAME,
767 APPROVALTYPEDESCRIPTIONOUT => L_APPR_TYPE,
768 APPROVALDESCRIPTIONOUT => L_APPR_DESC );
769 -- Bug 5167817 : end
770 INSERT INTO EDR_RULE_DETAIL_TEMP (SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME,
771 RULE_TYPE, APPR_TYPE, APPR_DESC, DEFAULT_VAR, START_DATE, END_DATE )
772 VALUES (L_SSNID, P_TRANSACTION_ID, L_RULE_IDS(ith), L_RULE_DESC, L_RULE_TYPE, L_APPR_TYPE,
773 L_APPR_DESC, L_DEFT_USE, L_DATE_0S(ith), L_DATE_1S(ith) );
774
775 FOR jth IN 1..l_cond_ids.count LOOP
776 INSERT INTO EDR_RULE_CONDITION_TEMP( SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, CONDITION_ID,CONDITION_DESC )
777 VALUES ( L_SSNID, P_TRANSACTION_ID, L_RULE_IDS(ith), L_COND_IDS(jth), L_COND_DESC(jth) );
778 END LOOP;
779
780 EXCEPTION
781 WHEN NO_DATA_FOUND THEN
782
783 SELECT DISTINCT DESCRIPTION
784 INTO L_RULE_NAME FROM AME_RULES_VL
785 WHERE RULE_ID = L_RULE_IDS(ith) AND (END_DATE IS NULL OR END_DATE > SYSDATE);
786
787 INSERT INTO EDR_RULE_DETAIL_TEMP ( SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME,
788 DEFAULT_VAR, START_DATE, END_DATE)
789 VALUES ( L_SSNID, P_TRANSACTION_ID, L_RULE_IDS(ith),L_RULE_NAME, L_DEFT_USE, L_DATE_0S(ith), L_DATE_1S(ith));
790 END;
791 END LOOP;
792
793 EXCEPTION
794 WHEN NO_DATA_FOUND THEN
795 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET_STRING('EDR', 'EDR_PLS_RULE_NOT_TRANS') );
796 WHEN OTHERS THEN
797 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET_STRING('EDR', 'EDR_PLS_RULE_NOT_UNIQUE') );
798
799 END SET_TRANSACTION_DETAILS;
800
801
802 --This API is used to set the rule details in the temp tables
803 --for the specified transaction ID and rule ID.
804 PROCEDURE SET_RULE_DETAILS(P_TRANSACTION_ID IN VARCHAR2,
805 P_RULE_ID IN VARCHAR2)
806 IS
807
808 L_RULE_NAME VARCHAR2(100);
809 L_POSITION NUMBER;
810 L_SSNID NUMBER;
811 L_TRANS_ID VARCHAR2(100);
812 L_APPS_ID NUMBER;
813 JTH NUMBER;
814 L_RULE_ID NUMBER;
815 L_DATE_FRM DATE;
816 L_DATE_END DATE;
817
818 L_RULE_TYPE VARCHAR2(100);
819 L_RULE_DESC VARCHAR2(100);
820 L_COND_IDS AME_UTIL.IDLIST;
821 L_COND_DESC AME_UTIL.LONGESTSTRINGLIST;
822 L_COND_LOV AME_UTIL.CHARLIST;
823 L_APPR_NAME VARCHAR2(100);
824 L_APPR_TYPE VARCHAR2(100);
825 L_APPR_DESC VARCHAR2(100);
826
827 BEGIN
828
829
830 SELECT USERENV('SESSIONID') INTO L_SSNID FROM DUAL;
831
832 SELECT APPLICATION_ID INTO L_APPS_ID
833 FROM AME_CALLING_APPS_VL
834 WHERE TRANSACTION_TYPE_ID = P_TRANSACTION_ID
835 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE);
836
837
838 SELECT DISTINCT DESCRIPTION
839 INTO L_RULE_NAME FROM AME_RULES_VL
840 WHERE RULE_ID = P_RULE_ID
841 AND SYSDATE <= NVL(END_DATE,SYSDATE);
842
843
844 SELECT DISTINCT START_DATE, END_DATE INTO L_DATE_FRM, L_DATE_END
845 FROM AME_RULE_USAGES
846 WHERE RULE_ID = P_RULE_ID
847 AND ITEM_ID = L_APPS_ID
848 AND SYSDATE <= NVL(END_DATE,SYSDATE);
849
850
851 BEGIN
852 -- Bug 5167817 : start
853 GETRULEDETAILS ( RULEIDIN => P_RULE_ID,
854 RULETYPEOUT => L_RULE_TYPE,
855 RULEDESCRIPTIONOUT => L_RULE_DESC,
856 CONDITIONIDSOUT => L_COND_IDS,
857 CONDITIONDESCRIPTIONSOUT => L_COND_DESC,
858 CONDITIONHASLOVSOUT => L_COND_LOV,
859 APPROVALTYPENAMEOUT => L_APPR_NAME,
860 APPROVALTYPEDESCRIPTIONOUT => L_APPR_TYPE,
861 APPROVALDESCRIPTIONOUT => L_APPR_DESC );
862 -- Bug 5167817 : end
863 INSERT INTO EDR_RULE_DETAIL_TEMP (SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME, RULE_TYPE, APPR_TYPE,
864 APPR_DESC, DEFAULT_VAR, START_DATE, END_DATE )
865 VALUES (L_SSNID, P_TRANSACTION_ID, P_RULE_ID, L_RULE_DESC, L_RULE_TYPE, L_APPR_TYPE,
866 L_APPR_DESC, 'N', L_DATE_FRM, L_DATE_END );
867
868 FOR JTH IN 1..L_COND_IDS.COUNT LOOP
869 INSERT INTO EDR_RULE_CONDITION_TEMP ( SESSION_ID, TRANSACTION_TYPE_ID,
870 RULE_ID, CONDITION_ID,CONDITION_DESC)
871 VALUES (L_SSNID, P_TRANSACTION_ID, P_RULE_ID, L_COND_IDS(JTH), L_COND_DESC(JTH) );
872
873 END LOOP;
874
875 EXCEPTION
876 WHEN NO_DATA_FOUND THEN
877 INSERT INTO EDR_RULE_DETAIL_TEMP (SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME,
878 DEFAULT_VAR, START_DATE, END_DATE)
879 VALUES(L_SSNID, P_TRANSACTION_ID, P_RULE_ID, L_RULE_NAME, 'N', L_DATE_FRM, L_DATE_END );
880 END;
881
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET_STRING('EDR', 'EDR_PLS_RULE_NOT_FOUND') );
885 WHEN OTHERS THEN
886 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET_STRING('EDR', 'EDR_PLS_RULE_NOT_UNIQUE') );
887
888 END SET_RULE_DETAILS;
889
890
891 --This function returns the specified display date in String format.
892 FUNCTION DISPLAY_DATE(P_DATE IN DATE)
893
894 RETURN VARCHAR2
895
896 IS
897
898 L_DATE VARCHAR2(4000);
899
900 BEGIN
901
902 EDR_STANDARD.DISPLAY_DATE(P_DATE_IN => P_DATE,
903 P_DATE_OUT => L_DATE);
904 RETURN L_DATE;
905
906 END DISPLAY_DATE;
907
908
909 PROCEDURE SYNC_RULE_TABLE(P_TRANSACTION_ID IN VARCHAR2)
910
911 IS
912
913 PRAGMA AUTONOMOUS_TRANSACTION;
914
915 BEGIN
916
917 DELETE FROM EDR_AMERULE_INPUT_VAR RULE_VAR
918 WHERE RULE_VAR.AME_TRANS_ID = P_TRANSACTION_ID
919 AND RULE_VAR.RULE_ID NOT IN (SELECT AME_USAGES.RULE_ID
920 FROM AME_CALLING_APPS_VL AME_APPS,
921 AME_RULE_USAGES AME_USAGES
922 WHERE AME_APPS.TRANSACTION_TYPE_ID = P_TRANSACTION_ID
923 AND AME_APPS.APPLICATION_ID = AME_USAGES.ITEM_ID
924 AND SYSDATE <= NVL(AME_USAGES.END_DATE,SYSDATE));
925
926 COMMIT;
927
928 EXCEPTION
929 WHEN OTHERS THEN
930
931 ROLLBACK;
932 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
933 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
934 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_RULE_TEMP');
935 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','SYNC_RULE_TABLE');
936 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
937 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
938 'edr.plsql.EDR_RULE_TEMP.SYNC_RULE_TABLE',
939 FALSE
940 );
941 END IF;
942 END SYNC_RULE_TABLE;
943
944
945 PROCEDURE CHECK_AND_SYNC_RULE_TABLE(P_TRANSACTION_ID IN VARCHAR2)
946
947 IS
948
949 L_COUNT NUMBER;
950
951 BEGIN
952
953 SELECT COUNT(*) INTO L_COUNT FROM EDR_AMERULE_INPUT_VAR RULE_VAR
954 WHERE RULE_VAR.AME_TRANS_ID = P_TRANSACTION_ID
955 AND RULE_VAR.RULE_ID NOT IN (SELECT AME_USAGES.RULE_ID
956 FROM AME_CALLING_APPS_VL AME_APPS,
957 AME_RULE_USAGES AME_USAGES
958 WHERE AME_APPS.TRANSACTION_TYPE_ID = P_TRANSACTION_ID
959 AND AME_APPS.APPLICATION_ID = AME_USAGES.ITEM_ID
960 AND SYSDATE <= NVL(AME_USAGES.END_DATE,SYSDATE));
961
962 IF L_COUNT > 0 THEN
963 SYNC_RULE_TABLE(P_TRANSACTION_ID);
964 END IF;
965
966 END CHECK_AND_SYNC_RULE_TABLE;
967
968 END EDR_RULE_TEMP;