1 PACKAGE BODY AME_RULES_API2 AS
2 /* $Header: amereapi.pkb 120.4 2006/07/07 10:14:05 pvelugul noship $ */
3 duplicateRuleKeyException exception;
4 X_AME_INSTALLATION_LEVEL varchar2(255);
5 procedure OWNER_TO_WHO (
6 X_OWNER in VARCHAR2,
7 X_CREATED_BY out nocopy NUMBER,
8 X_LAST_UPDATED_BY out nocopy NUMBER,
9 X_LAST_UPDATE_LOGIN out nocopy NUMBER
10 ) is
11 begin
12 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
13 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
14 X_LAST_UPDATE_LOGIN := 0;
15 end OWNER_TO_WHO;
16
17 procedure KEY_TO_IDS (
18 X_RULE_KEY in VARCHAR2,
19 X_ITEM_CLASS_NAME in VARCHAR2,
20 X_RULE_ID out nocopy NUMBER,
21 X_ITEM_CLASS_ID out nocopy NUMBER,
22 X_CUSTOM_DETECT out nocopy BOOLEAN
23 ) is
24 cursor CSR_GET_ITEM_CLASS_ID
25 (
26 X_ITEM_CLASS_NAME in VARCHAR2
27 ) is
28 select ITEM_CLASS_ID
29 from AME_ITEM_CLASSES
30 where NAME = X_ITEM_CLASS_NAME
31 and sysdate between START_DATE
32 and nvl(END_DATE - (1/86400), sysdate);
33 cursor CSR_GET_RULE_ID
34 (
35 X_RULE_KEY in VARCHAR2
36 ) is
37 select RULE_ID, CREATED_BY
38 from AME_RULES
39 where RULE_KEY = X_RULE_KEY;
40 X_CREATED_BY number;
41 X_RULE_ID_2 number;
42 begin
43 open CSR_GET_ITEM_CLASS_ID (
44 X_ITEM_CLASS_NAME
45 );
46 fetch CSR_GET_ITEM_CLASS_ID into X_ITEM_CLASS_ID;
47 if (CSR_GET_ITEM_CLASS_ID%notfound) then
48 X_ITEM_CLASS_ID := null;
49 end if;
50 close CSR_GET_ITEM_CLASS_ID;
51
52 open CSR_GET_RULE_ID (
53 X_RULE_KEY
54 );
55 -- fetch RULE if there is a match on RULE_KEY
56 fetch CSR_GET_RULE_ID into X_RULE_ID,
57 X_CREATED_BY;
58 if (CSR_GET_RULE_ID%notfound) then
59 X_RULE_ID := null;
60 else
61 loop
62 -- detect for custom data only
63 if X_CREATED_BY = 1 then
64 X_CUSTOM_DETECT := false;
65 exit;
66 else
67 X_CUSTOM_DETECT := true;
68 end if;
69 fetch CSR_GET_RULE_ID into X_RULE_ID_2,
70 X_CREATED_BY;
71 if (CSR_GET_RULE_ID%notfound) then
72 exit;
73 end if;
74 end loop;
75 end if;
76 close CSR_GET_RULE_ID;
77 end KEY_TO_IDS;
78
79 function DO_UPDATE_INSERT(X_OWNER in NUMBER,
80 X_CURRENT_OWNER in NUMBER,
81 X_LAST_UPDATE_DATE in VARCHAR2,
82 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
83 X_CUSTOM_MODE in VARCHAR2)
84 return boolean as
85 begin
86 return AME_SEED_UTILITY.MERGE_ROW_TEST
87 (X_OWNER => X_OWNER
88 ,X_CURRENT_OWNER => X_CURRENT_OWNER
89 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
90 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
91 ,X_CUSTOM_MODE => X_CUSTOM_MODE
92 );
93 end DO_UPDATE_INSERT;
94
95 function DO_TL_UPDATE_INSERT(X_OWNER in NUMBER,
96 X_CURRENT_OWNER in NUMBER,
97 X_LAST_UPDATE_DATE in VARCHAR2,
98 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
99 X_CREATED_BY in VARCHAR2,
100 X_CUSTOM_MODE in VARCHAR2)
101 return boolean as
102 begin
103 return AME_SEED_UTILITY.TL_MERGE_ROW_TEST
104 (X_OWNER => X_OWNER
105 ,X_CURRENT_OWNER => X_CURRENT_OWNER
106 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
107 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
108 ,X_CUSTOM_MODE => X_CUSTOM_MODE
109 );
110 end DO_TL_UPDATE_INSERT;
111
112 procedure KEY_TO_IDS_2 (
113 X_RULE_ID in NUMBER,
114 X_RULE_KEY in VARCHAR2,
115 X_ACTION_TYPE_NAME in VARCHAR2,
116 X_PARAMETER in VARCHAR2,
117 X_ITEM_CLASS_NAME in VARCHAR2,
118 X_ACTION_ID out nocopy NUMBER,
119 X_EXISTING_RULE_KEY out nocopy VARCHAR2,
120 X_RULE_COUNT out nocopy NUMBER,
121 X_ITEM_CLASS_ID out nocopy NUMBER
122 ) is
123 cursor CSR_CHECK_RULE_KEY
124 (
125 X_RULE_KEY in VARCHAR2
126 ) is
127 select RULE_ID
128 from AME_RULES
129 where RULE_KEY = X_RULE_KEY;
130 cursor CSR_RULE_KEY
131 (
132 X_RULE_ID in NUMBER
133 ) is
134 select RULE_KEY
135 from AME_RULES
136 where RULE_ID = X_RULE_ID;
137 cursor CSR_GET_ITEM_CLASS_ID
138 (
139 X_ITEM_CLASS_NAME in VARCHAR2
140 ) is
141 select ITEM_CLASS_ID
142 from AME_ITEM_CLASSES
143 where NAME = X_ITEM_CLASS_NAME
144 and sysdate between START_DATE
145 and nvl(END_DATE - (1/86400), sysdate);
146 cursor CSR_GET_RULE_COUNT
147 (
148 X_RULE_ID in NUMBER
149 ) is
150 select COUNT(*)
151 from AME_RULES
152 where RULE_ID = X_RULE_ID;
153 cursor CSR_GET_ACTION_ID is
154 select action_id
155 from ame_actions aa,
156 ame_action_types aat
157 where aa.parameter = X_PARAMETER
158 and aat.name = X_ACTION_TYPE_NAME
159 and aat.action_type_id = aa.action_type_id
160 and sysdate between aa.start_date and
161 nvl(aa.end_date - (1/86400),sysdate)
162 and sysdate between aat.start_date and
163 nvl(aat.end_date - (1/86400),sysdate);
164 X_RULE_KEY_CHECK NUMBER;
165 errorCode integer;
166 errorMessage ame_util.longestStringType;
167 begin
168 if X_AME_INSTALLATION_LEVEL is null then
169 X_EXISTING_RULE_KEY:= null;
170 X_ITEM_CLASS_ID:= null;
171 open CSR_GET_ACTION_ID;
172 fetch CSR_GET_ACTION_ID into X_ACTION_ID;
173 close CSR_GET_ACTION_ID;
174 else
175 open CSR_CHECK_RULE_KEY (
176 X_RULE_KEY
177 );
178 -- fetch RULE if there is a match on RULE_KEY
179 -- raise an exception
180 fetch CSR_CHECK_RULE_KEY into X_RULE_KEY_CHECK;
181 if (CSR_CHECK_RULE_KEY%found) then
182 raise duplicateRuleKeyException;
183 end if;
184 close CSR_CHECK_RULE_KEY;
185 open CSR_RULE_KEY (
186 X_RULE_ID
187 );
188 -- fetch EXISTING RULE KEY if there is a match on RULE_ID
189 fetch CSR_RULE_KEY into X_EXISTING_RULE_KEY;
190 if (CSR_RULE_KEY%notfound) then
191 X_EXISTING_RULE_KEY := null;
192 end if;
193 close CSR_RULE_KEY;
194 -- get item_class_id
195 open CSR_GET_ITEM_CLASS_ID (
196 X_ITEM_CLASS_NAME
197 );
198 fetch CSR_GET_ITEM_CLASS_ID into X_ITEM_CLASS_ID;
199 if (CSR_GET_ITEM_CLASS_ID%notfound) then
200 X_ITEM_CLASS_ID := null;
201 end if;
202 close CSR_GET_ITEM_CLASS_ID;
203 end if;
204 open CSR_GET_RULE_COUNT (
205 X_RULE_ID
206 );
207 fetch CSR_GET_RULE_COUNT
208 into X_RULE_COUNT;
209 close CSR_GET_RULE_COUNT;
210 exception
211 when duplicateRuleKeyException then
212 errorCode := -20001;
213 errorMessage := 'OAM is attempting to upload a duplicate rule key. ';
214 ame_util.runtimeException(packageNameIn => 'ame_rules_api2',
215 routineNameIn => 'key_to_ids_2',
216 exceptionNumberIn => errorCode,
217 exceptionStringIn => errorMessage);
218 raise;
219 when others then
220 ame_util.runtimeException('ame_rules_api2',
221 'key_to_ids_2',
222 sqlcode,
223 sqlerrm);
224 raise;
225 end KEY_TO_IDS_2;
226
227 procedure VALIDATE_RULE_TYPE (
228 X_RULE_TYPE in NUMBER
229 ) is
230 invalidRuleTypeException exception;
231 errorCode integer;
232 errorMessage ame_util.longestStringType;
233 begin
234 X_AME_INSTALLATION_LEVEL:= fnd_profile.value('AME_INSTALLATION_LEVEL');
235 if X_AME_INSTALLATION_LEVEL is null then
236 if (X_RULE_TYPE not in (ame_util.authorityRuleType
237 ,ame_util.exceptionRuleType
238 ,ame_util.preListGroupRuleType
239 ,ame_util.postListGroupRuleType
240 )) then
241 raise invalidRuleTypeException;
242 end if;
243 else
244 if (X_RULE_TYPE not in (ame_util.authorityRuleType
245 ,ame_util.exceptionRuleType
246 ,ame_util.preListGroupRuleType
247 ,ame_util.postListGroupRuleType
248 ,ame_util.productionRuleType
249 )) then
250 raise invalidRuleTypeException;
251 end if;
252 end if;
253 exception
254 when invalidRuleTypeException then
255 errorCode := -20001;
256 errorMessage := 'OAM is attempting to upload an invalid rule type. ';
257 ame_util.runtimeException(packageNameIn => 'ame_rules_api2',
258 routineNameIn => 'validate_rule_type',
259 exceptionNumberIn => errorCode,
260 exceptionStringIn => errorMessage);
261 raise_application_error(errorCode,
262 errorMessage);
263 when others then
264 ame_util.runtimeException('ame_rules_api2',
265 'validate_rule_type',
266 sqlcode,
267 sqlerrm);
268 raise;
269 end VALIDATE_RULE_TYPE;
270
271 procedure INSERT_ROW (
272 X_RULE_KEY in VARCHAR2,
273 X_RULE_TYPE in NUMBER,
274 X_ACTION_ID in NUMBER,
275 X_CREATED_BY in NUMBER,
276 X_CREATION_DATE in DATE,
277 X_LAST_UPDATED_BY in NUMBER,
278 X_LAST_UPDATE_DATE in DATE,
279 X_LAST_UPDATE_LOGIN in NUMBER,
280 X_START_DATE in DATE,
281 X_DESCRIPTION in VARCHAR2,
282 X_ITEM_CLASS_ID in NUMBER,
283 X_OBJECT_VERSION_NUMBER in NUMBER)
284 is
285 lockHandle varchar2(500);
286 returnValue integer;
287 X_RULE_ID number;
288 begin
289
290 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_RULES.'||X_RULE_KEY,lockhandle => lockHandle);
291 returnValue := DBMS_LOCK.REQUEST(lockhandle => lockHandle,timeout => 0, release_on_commit=>true);
292 if returnValue = 0 then
293 select ame_rules_s.nextval into X_RULE_ID from dual;
294 insert into AME_RULES
295 (
296 RULE_ID,
297 RULE_KEY,
298 RULE_TYPE,
299 ACTION_ID,
300 CREATED_BY,
301 CREATION_DATE,
302 LAST_UPDATED_BY,
303 LAST_UPDATE_DATE,
304 LAST_UPDATE_LOGIN,
305 START_DATE,
306 END_DATE,
307 DESCRIPTION,
308 ITEM_CLASS_ID,
309 OBJECT_VERSION_NUMBER
310 ) select
311 X_RULE_ID,
312 x_RULE_KEY,
313 X_RULE_TYPE,
314 X_ACTION_ID,
315 X_CREATED_BY,
316 X_CREATION_DATE,
317 X_LAST_UPDATED_BY,
318 X_LAST_UPDATE_DATE,
319 X_LAST_UPDATE_LOGIN,
320 X_START_DATE,
321 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
322 X_DESCRIPTION,
323 X_ITEM_CLASS_ID,
324 X_OBJECT_VERSION_NUMBER
325 from sys.dual
326 where not exists (select NULL
327 from AME_RULES
328 where RULE_KEY = X_RULE_KEY
329 and sysdate between START_DATE
330 and nvl(END_DATE - (1/86400), sysdate));
331 if sql%found then
332 if not AME_SEED_UTILITY.MLS_ENABLED then
333 return;
334 end if;
335 insert into AME_RULES_TL
336 (RULE_ID
337 ,DESCRIPTION
338 ,CREATED_BY
339 ,CREATION_DATE
340 ,LAST_UPDATED_BY
341 ,LAST_UPDATE_DATE
342 ,LAST_UPDATE_LOGIN
343 ,LANGUAGE
344 ,SOURCE_LANG
345 ) select X_RULE_ID,
346 X_DESCRIPTION,
347 X_CREATED_BY,
348 X_CREATION_DATE,
349 X_LAST_UPDATED_BY,
350 X_LAST_UPDATE_DATE,
351 X_LAST_UPDATE_LOGIN,
352 L.LANGUAGE_CODE,
353 userenv('LANG')
354 from FND_LANGUAGES L
355 where L.INSTALLED_FLAG in ('I', 'B')
356 and not exists (select null
357 from AME_RULES_TL T
358 where T.RULE_ID = X_RULE_ID
359 and T.LANGUAGE = L.LANGUAGE_CODE);
360 end if;
361 end if;
362 end INSERT_ROW;
363
364 procedure INSERT_ROW_2 (
365 X_RULE_ID in NUMBER,
366 X_RULE_KEY in VARCHAR2,
367 X_RULE_TYPE in NUMBER,
368 X_ACTION_ID in NUMBER,
369 X_CREATED_BY in NUMBER,
370 X_CREATION_DATE in DATE,
371 X_LAST_UPDATED_BY in NUMBER,
372 X_LAST_UPDATE_DATE in DATE,
373 X_LAST_UPDATE_LOGIN in NUMBER,
374 X_START_DATE in DATE,
375 X_DESCRIPTION in VARCHAR2,
376 X_ITEM_CLASS_ID in NUMBER,
377 X_OBJECT_VERSION_NUMBER in NUMBER)
378 is
379 lockHandle varchar2(500);
380 returnValue integer;
381 begin
382 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_RULES.'||X_RULE_ID,lockhandle => lockHandle);
383 returnValue := DBMS_LOCK.REQUEST(lockhandle => lockHandle,timeout => 0, release_on_commit=>true);
384 if returnValue = 0 then
385 insert into AME_RULES
386 (
387 RULE_ID,
388 RULE_KEY,
389 RULE_TYPE,
390 ACTION_ID,
391 CREATED_BY,
392 CREATION_DATE,
393 LAST_UPDATED_BY,
394 LAST_UPDATE_DATE,
395 LAST_UPDATE_LOGIN,
396 START_DATE,
397 END_DATE,
398 DESCRIPTION,
399 ITEM_CLASS_ID,
400 OBJECT_VERSION_NUMBER
401 ) select
402 X_RULE_ID,
403 X_RULE_KEY,
404 X_RULE_TYPE,
405 X_ACTION_ID,
406 X_CREATED_BY,
407 X_CREATION_DATE,
408 X_LAST_UPDATED_BY,
409 X_LAST_UPDATE_DATE,
410 X_LAST_UPDATE_LOGIN,
411 X_START_DATE,
412 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
413 X_DESCRIPTION,
414 X_ITEM_CLASS_ID,
415 X_OBJECT_VERSION_NUMBER
416 from sys.dual
417 where not exists (select NULL
418 from AME_RULES
419 where RULE_ID = X_RULE_ID
420 and ((START_DATE - (1/86400)) <= sysdate)
421 and (((END_DATE - (1/86400)) >= sysdate)
422 or (END_DATE is null)));
423
424 if sql%found then
425 if not AME_SEED_UTILITY.MLS_ENABLED then
426 return;
427 end if;
431 ,CREATED_BY
428 insert into AME_RULES_TL
429 (RULE_ID
430 ,DESCRIPTION
432 ,CREATION_DATE
433 ,LAST_UPDATED_BY
434 ,LAST_UPDATE_DATE
435 ,LAST_UPDATE_LOGIN
436 ,LANGUAGE
437 ,SOURCE_LANG
438 ) select X_RULE_ID,
439 X_DESCRIPTION,
440 X_CREATED_BY,
441 X_CREATION_DATE,
442 X_LAST_UPDATED_BY,
443 X_LAST_UPDATE_DATE,
444 X_LAST_UPDATE_LOGIN,
445 L.LANGUAGE_CODE,
446 userenv('LANG')
447 from FND_LANGUAGES L
448 where L.INSTALLED_FLAG in ('I', 'B')
449 and not exists (select null
450 from AME_RULES_TL T
451 where T.RULE_ID = X_RULE_ID
452 and T.LANGUAGE = L.LANGUAGE_CODE);
453 end if;
454 end if;
455 end INSERT_ROW_2;
456
457 procedure DELETE_ROW (
458 X_RULE_KEY in VARCHAR2
459 ) is
460 begin
461 if AME_SEED_UTILITY.MLS_ENABLED then
462 delete from AME_RULES_TL
463 where RULE_ID in (select RULE_ID
464 from AME_RULES
465 where RULE_KEY = X_RULE_KEY);
466 end if;
467 delete from AME_RULES
468 where RULE_KEY = X_RULE_KEY;
469 if (sql%notfound) then
470 raise no_data_found;
471 end if;
472 end DELETE_ROW;
473
474 procedure LOAD_ROW (
475 X_RULE_KEY in VARCHAR2,
476 X_RULE_ID in VARCHAR2,
477 X_ACTION_TYPE_NAME in VARCHAR2,
478 X_PARAMETER in VARCHAR2,
479 X_RULE_TYPE in VARCHAR2,
480 X_DESCRIPTION in VARCHAR2,
481 X_ITEM_CLASS_NAME in VARCHAR2,
482 X_OWNER in VARCHAR2,
483 X_LAST_UPDATE_DATE in VARCHAR2,
484 X_CUSTOM_MODE in VARCHAR2
485 )
486 is
487 X_ACTION_ID NUMBER :=null;
488 X_CREATED_BY NUMBER;
489 X_CUSTOM_DETECT BOOLEAN := false;
490 X_EXISTING_RULE_KEY ame_rules.rule_key%type:=null;
491 X_ITEM_CLASS_ID NUMBER;
492 X_LAST_UPDATED_BY NUMBER;
493 X_LAST_UPDATE_LOGIN NUMBER;
494 X_RULE_ID_LOAD NUMBER;
495 X_RULE_COUNT NUMBER:=0;
496 X_RULE_KEY2 VARCHAR2(100);
497 begin
498 X_AME_INSTALLATION_LEVEL:= fnd_profile.value('AME_INSTALLATION_LEVEL');
499 --for pre-AME 11510 do not upload rules if item class does not belong
500 --to header or lineitem item class
501 if X_AME_INSTALLATION_LEVEL is null then
502 if X_ITEM_CLASS_NAME is not null
503 and X_ITEM_CLASS_NAME not in(
504 ame_util.headerItemClassName,
505 ame_util.lineItemItemClassName
506 ) then
507 return;
508 elsif X_ACTION_TYPE_NAME in ('pre-chain-of-authority approvals',
509 'post-chain-of-authority approvals',
510 'approval-group chain of authority'
511 ) then
512 return;
513 end if;
514 end if;
515 VALIDATE_RULE_TYPE (
516 X_RULE_TYPE
517 );
518 OWNER_TO_WHO (
519 X_OWNER,
520 X_CREATED_BY,
521 X_LAST_UPDATED_BY,
522 X_LAST_UPDATE_LOGIN
523 );
524 --the if part of the code executes only for AME 11510
525 if X_RULE_ID > 0 then
526 if X_AME_INSTALLATION_LEVEL is not null then
527 X_RULE_ID_LOAD := null;
528 -- RULE_ID is POSITIVE - DRIVE OFF RULE_KEY
529 KEY_TO_IDS (
530 X_RULE_KEY,
531 X_ITEM_CLASS_NAME,
532 X_RULE_ID_LOAD,
533 X_ITEM_CLASS_ID,
534 X_CUSTOM_DETECT
535 );
536 -- the rule row was found matching the RULE_KEY
537 -- however it is custom created and will have an '@' sign prepended
538 -- insert a new row
539 --the following insert does not occur for prior versions of AME 11510
540 if AME_SEED_UTILITY.IS_SEED_USER(X_OWNER) and
541 (X_RULE_ID_LOAD is not null) and
542 X_CUSTOM_DETECT then
543 update AME_RULES
544 set RULE_KEY = '@' || X_RULE_KEY
545 where RULE_KEY = X_RULE_KEY;
546 INSERT_ROW (
547 X_RULE_KEY,
548 X_RULE_TYPE,
549 X_ACTION_ID,
550 X_CREATED_BY,
551 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
552 X_LAST_UPDATED_BY,
553 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
554 X_LAST_UPDATE_LOGIN,
555 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
556 X_DESCRIPTION,
557 X_ITEM_CLASS_ID,
558 1);
559 end if;
560 -- the rule row was not found insert a new row
561 if (X_RULE_ID_LOAD is null) then
562 INSERT_ROW (
563 X_RULE_KEY,
564 X_RULE_TYPE,
565 X_ACTION_ID,
566 X_CREATED_BY,
567 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
568 X_LAST_UPDATED_BY,
569 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
570 X_LAST_UPDATE_LOGIN,
571 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
572 X_DESCRIPTION,
573 X_ITEM_CLASS_ID,
574 1);
575 -- the current row was found end date the current row
576 -- do not update or insert
580 -- RULE_ID is NEGATIVE - DRIVE OFF RULE_ID
577 end if;
578 end if;
579 else --ldt is prior version of AME 11510
581 KEY_TO_IDS_2 (
582 X_RULE_ID,
583 X_RULE_KEY,
584 X_ACTION_TYPE_NAME,
585 X_PARAMETER,
586 X_ITEM_CLASS_NAME,
587 X_ACTION_ID,
588 X_EXISTING_RULE_KEY,
589 X_RULE_COUNT,
590 X_ITEM_CLASS_ID
591 );
592 -- Populate the Rule Key
593 X_RULE_KEY2:= X_RULE_KEY;
594 -- the current row was not found insert a new row
595 if (X_RULE_COUNT = 0) then
596 INSERT_ROW_2 (
597 X_RULE_ID,
598 X_RULE_KEY2,
599 X_RULE_TYPE,
600 X_ACTION_ID,
601 X_CREATED_BY,
602 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
603 X_LAST_UPDATED_BY,
604 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
605 X_LAST_UPDATE_LOGIN,
606 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
607 X_DESCRIPTION,
608 X_ITEM_CLASS_ID,
609 1);
610 end if;
611 -- a current row is found.
612 -- the rule key is from an older ldt and
613 -- must be synchronized with the newer ldt extract from SEED
617 update AME_RULES
614 if (X_RULE_COUNT > 0) and
615 (X_EXISTING_RULE_KEY is not null) and
616 (X_EXISTING_RULE_KEY <> X_RULE_KEY) then
618 set RULE_KEY = X_RULE_KEY
619 where RULE_KEY = X_EXISTING_RULE_KEY
620 and RULE_ID = X_RULE_ID;
621 end if;
622 end if;
623 exception
624 when duplicateRuleKeyException then
625 null;
626 when others then
627 ame_util.runtimeException('ame_rules_api2',
628 'load_row',
629 sqlcode,
630 sqlerrm);
631 raise;
632 end LOAD_ROW;
633
634 procedure TRANSLATE_ROW
635 (X_RULE_KEY in varchar2
636 ,X_DESCRIPTION in varchar2
637 ,X_OWNER in varchar2
638 ,X_LAST_UPDATE_DATE in varchar2
639 ,X_CUSTOM_MODE in varchar2
640 ) as
641 X_CURRENT_OWNER number;
642 X_CURRENT_LAST_UPDATE_DATE varchar2(19);
643 X_CREATED_BY varchar2(100);
644 X_RULE_ID number;
645 begin
646 if not AME_SEED_UTILITY.MLS_ENABLED then
647 return;
648 end if;
649 begin
653 ARU.RULE_ID
650 select ARUTL.LAST_UPDATED_BY,
651 AME_SEED_UTILITY.DATE_AS_STRING(ARUTL.LAST_UPDATE_DATE),
652 AME_SEED_UTILITY.OWNER_AS_STRING(ARUTL.CREATED_BY),
654 into X_CURRENT_OWNER,
655 X_CURRENT_LAST_UPDATE_DATE,
656 X_CREATED_BY,
657 X_RULE_ID
658 from AME_RULES ARU,
659 AME_RULES_TL ARUTL
660 where ARU.RULE_KEY = X_RULE_KEY
661 and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate)
662 and ARUTL.RULE_ID = ARU.RULE_ID
663 and ARUTL.LANGUAGE = userenv('LANG');
664 if DO_TL_UPDATE_INSERT
665 (X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
666 X_CURRENT_OWNER => X_CURRENT_OWNER,
667 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
668 X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE,
669 X_CREATED_BY => X_CREATED_BY,
670 X_CUSTOM_MODE => X_CUSTOM_MODE) then
671 update AME_RULES_TL ARUTL
672 set DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
673 SOURCE_LANG = userenv('LANG'),
674 LAST_UPDATE_DATE = AME_SEED_UTILITY.DATE_AS_DATE(X_LAST_UPDATE_DATE),
675 LAST_UPDATED_BY = AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
676 LAST_UPDATE_LOGIN = 0
677 where ARUTL.RULE_ID = X_RULE_ID
678 and userenv('LANG') in (ARUTL.LANGUAGE,ARUTL.SOURCE_LANG);
679 end if;
680 exception
681 when no_data_found then
682 null;
683 end;
684 end TRANSLATE_ROW;
685
686 END AME_RULES_API2;
[Home] [Help]