[Home] [Help]
PACKAGE BODY: APPS.AME_RULES_API
Source
1 PACKAGE BODY AME_RULES_API AS
2 /* $Header: amerlapi.pkb 120.1 2005/10/14 04:13 ubhat noship $ */
3 X_AME_INSTALLATION_LEVEL varchar2(255);
4 procedure OWNER_TO_WHO (
5 X_OWNER in VARCHAR2,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATED_BY out nocopy NUMBER,
8 X_LAST_UPDATE_LOGIN out nocopy NUMBER
9 ) is
10 begin
11 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
13 X_LAST_UPDATE_LOGIN := 0;
14 end OWNER_TO_WHO;
15
16 procedure KEY_TO_IDS (
17 X_RULE_ID in VARCHAR2,
18 X_ACTION_PARAMETER in VARCHAR2,
19 X_ACTION_TYPE_NAME in VARCHAR2,
20 X_ACTION_ID out nocopy NUMBER,
21 X_ACTION_TYPE_ID out nocopy NUMBER,
22 X_USAGE_ROWID out nocopy VARCHAR2,
23 X_RULE_COUNT out nocopy NUMBER,
24 X_CURRENT_USAGE_OWNER out nocopy NUMBER,
25 X_CURRENT_USAGE_LUD out nocopy VARCHAR2,
26 X_CURRENT_USAGE_OVN out nocopy NUMBER
27 ) is
28 cursor CSR_GET_ACTION_TYPE_ID
29 (
30 X_ACTION_TYPE_NAME in VARCHAR2
31 ) is
32 select ACTION_TYPE_ID
33 from AME_ACTION_TYPES
34 where NAME = X_ACTION_TYPE_NAME
35 and sysdate between START_DATE
36 and nvl(END_DATE - (1/86400), sysdate);
37 cursor CSR_GET_ACTION
38 (
39 X_ACTION_TYPE_ID in NUMBER,
40 X_PARAMETER in VARCHAR2
41 ) is
42 select ACTION_ID from AME_ACTIONS
43 where ACTION_TYPE_ID = X_ACTION_TYPE_ID
44 and nvl(PARAMETER,'NULL') = nvl(X_PARAMETER,'NULL')
45 and sysdate between START_DATE
46 and nvl(END_DATE - (1/86400), sysdate);
47 cursor CSR_GET_CURRENT_USAGE
48 (
49 X_RULE_ID in NUMBER,
50 X_ACTION_ID in NUMBER
51 ) is
52 select ROWID,
53 LAST_UPDATED_BY,
54 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
55 nvl(OBJECT_VERSION_NUMBER,1)
56 from AME_ACTION_USAGES
57 where RULE_ID = X_RULE_ID
58 and ACTION_ID = X_ACTION_ID
59 and sysdate between START_DATE
60 and nvl(END_DATE - (1/86400), sysdate);
61 cursor CSR_GET_RULE_COUNT
62 (
63 X_RULE_ID in NUMBER
64 ) is
65 select COUNT(*)
66 from AME_RULES
67 where RULE_ID = X_RULE_ID;
68
69 begin
70 X_CURRENT_USAGE_OVN := 1;
71 open CSR_GET_ACTION_TYPE_ID (
72 X_ACTION_TYPE_NAME
73 );
74 fetch CSR_GET_ACTION_TYPE_ID into X_ACTION_TYPE_ID;
75 if (CSR_GET_ACTION_TYPE_ID%notfound) then
76 X_ACTION_TYPE_ID := null;
77 end if;
78 close CSR_GET_ACTION_TYPE_ID;
79 if X_ACTION_TYPE_ID is not null then
80 open CSR_GET_ACTION (
81 X_ACTION_TYPE_ID,
82 X_ACTION_PARAMETER
83 );
84 fetch CSR_GET_ACTION into X_ACTION_ID;
85 if (CSR_GET_ACTION%notfound) then
86 X_ACTION_ID := null;
87 end if;
88 close CSR_GET_ACTION;
89 end if;
90 if X_ACTION_ID is not null then
91 open CSR_GET_RULE_COUNT (
92 X_RULE_ID
93 );
94 fetch CSR_GET_RULE_COUNT into X_RULE_COUNT;
95 close CSR_GET_RULE_COUNT;
96 if X_AME_INSTALLATION_LEVEL is not null then
97 open CSR_GET_CURRENT_USAGE (
98 X_RULE_ID,
99 X_ACTION_ID
100 );
101 fetch CSR_GET_CURRENT_USAGE into X_USAGE_ROWID,
102 X_CURRENT_USAGE_OWNER,
103 X_CURRENT_USAGE_LUD,
104 X_CURRENT_USAGE_OVN;
105 close CSR_GET_CURRENT_USAGE;
106 end if;
107 end if;
108 end KEY_TO_IDS;
109
110 procedure VALIDATE_RULE_TYPE (
111 X_RULE_TYPE in NUMBER
112 ) is
113 invalidRuleTypeException exception;
114 errorCode integer;
115 errorMessage ame_util.longestStringType;
116 begin
117 if (X_RULE_TYPE <> ame_util.authorityRuleType)
118 and (X_RULE_TYPE <> ame_util.exceptionRuleType) then
119 raise invalidRuleTypeException;
120 end if;
121 exception
122 when invalidRuleTypeException then
123 errorCode := -20001;
124 errorMessage := 'OAM is attempting to upload an invalid rule type. ';
125 ame_util.runtimeException(packageNameIn => 'ame_rules_api2',
126 routineNameIn => 'validate_rule_type',
127 exceptionNumberIn => errorCode,
128 exceptionStringIn => errorMessage);
129 raise_application_error(errorCode,
130 errorMessage);
131 when others then
132 ame_util.runtimeException('ame_rules_api2',
133 'validate_rule_type',
134 sqlcode,
135 sqlerrm);
136 raise;
137 end VALIDATE_RULE_TYPE;
138
139 procedure INSERT_ROW (
140 X_RULE_ID in NUMBER,
141 X_RULE_KEY in VARCHAR2,
142 X_RULE_TYPE in NUMBER,
143 X_ACTION_ID in NUMBER,
144 X_CREATED_BY in NUMBER,
145 X_CREATION_DATE in DATE,
146 X_LAST_UPDATED_BY in NUMBER,
147 X_LAST_UPDATE_DATE in DATE,
148 X_LAST_UPDATE_LOGIN in NUMBER,
149 X_START_DATE in DATE,
150 X_DESCRIPTION in VARCHAR2,
151 X_ITEM_CLASS_ID in NUMBER,
152 X_OBJECT_VERSION_NUMBER in NUMBER)
153 is
154 lockHandle varchar2(500);
155 returnValue integer;
156 begin
157 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_RULES.'||X_RULE_ID,lockhandle => lockHandle);
158 returnValue := DBMS_LOCK.REQUEST(lockhandle => lockHandle,timeout => 0
159 ,release_on_commit => true);
160 if returnValue = 0 then
161 insert into AME_RULES
162 (
163 RULE_ID,
164 RULE_KEY,
165 RULE_TYPE,
166 ACTION_ID,
167 CREATED_BY,
168 CREATION_DATE,
169 LAST_UPDATED_BY,
170 LAST_UPDATE_DATE,
171 LAST_UPDATE_LOGIN,
172 START_DATE,
173 END_DATE,
174 DESCRIPTION,
175 ITEM_CLASS_ID,
176 OBJECT_VERSION_NUMBER
177 ) select
178 X_RULE_ID,
179 X_RULE_KEY,
180 X_RULE_TYPE,
181 X_ACTION_ID,
182 X_CREATED_BY,
183 X_CREATION_DATE,
184 X_LAST_UPDATED_BY,
185 X_LAST_UPDATE_DATE,
186 X_LAST_UPDATE_LOGIN,
187 X_START_DATE,
188 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
189 X_DESCRIPTION,
190 X_ITEM_CLASS_ID,
191 X_OBJECT_VERSION_NUMBER
192 from sys.dual
193 where not exists (select NULL
194 from AME_RULES
195 where ((RULE_ID = X_RULE_ID
196 and sysdate < nvl(END_DATE - (1/86400), sysdate + (1/86400)))
197 or (X_AME_INSTALLATION_LEVEL is not null and RULE_KEY = X_RULE_KEY))
198 );
199 if sql%found then
200 insert into AME_RULES_TL
201 (RULE_ID
202 ,DESCRIPTION
203 ,CREATED_BY
204 ,CREATION_DATE
205 ,LAST_UPDATED_BY
206 ,LAST_UPDATE_DATE
207 ,LAST_UPDATE_LOGIN
208 ,LANGUAGE
209 ,SOURCE_LANG
210 ) select X_RULE_ID,
211 X_DESCRIPTION,
212 X_CREATED_BY,
213 X_CREATION_DATE,
214 X_LAST_UPDATED_BY,
215 X_LAST_UPDATE_DATE,
216 X_LAST_UPDATE_LOGIN,
217 L.LANGUAGE_CODE,
218 userenv('LANG')
219 from FND_LANGUAGES L
220 where L.INSTALLED_FLAG in ('I', 'B')
221 and not exists (select null
222 from AME_RULES_TL T
223 where T.RULE_ID = X_RULE_ID
224 and T.LANGUAGE = L.LANGUAGE_CODE);
225 end if;
226 end if;
227 end INSERT_ROW;
228 function DO_USAGE_UPDATE_INSERT(X_OWNER in NUMBER,
229 X_CURRENT_OWNER in NUMBER,
230 X_LAST_UPDATE_DATE in VARCHAR2,
231 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2)
232 return boolean as
233 begin
234 return AME_SEED_UTILITY.MERGE_ROW_TEST
235 (X_OWNER => X_OWNER
236 ,X_CURRENT_OWNER => X_CURRENT_OWNER
237 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
238 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
239 ,X_CUSTOM_MODE => null
240 );
241 end DO_USAGE_UPDATE_INSERT;
242
243 procedure INSERT_USAGE_ROW (
244 X_RULE_ID in NUMBER,
245 X_ACTION_ID in NUMBER,
246 X_CREATED_BY in NUMBER,
247 X_CREATION_DATE in DATE,
248 X_LAST_UPDATED_BY in NUMBER,
249 X_LAST_UPDATE_DATE in DATE,
250 X_LAST_UPDATE_LOGIN in NUMBER,
251 X_START_DATE in DATE,
252 X_OBJECT_VERSION_NUMBER in NUMBER)
253 is
254 lockHandle varchar2(500);
255 returnValue integer;
256 begin
257 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_ACTION_USAGES.'||X_RULE_ID||X_ACTION_ID
258 ,lockhandle => lockHandle);
259 returnValue := DBMS_LOCK.REQUEST(lockhandle => lockHandle,timeout => 0
260 ,release_on_commit => true);
261 if returnValue = 0 then
262 insert into AME_ACTION_USAGES
263 (
264 RULE_ID,
265 ACTION_ID,
266 CREATED_BY,
267 CREATION_DATE,
268 LAST_UPDATED_BY,
269 LAST_UPDATE_DATE,
270 LAST_UPDATE_LOGIN,
271 START_DATE,
272 END_DATE,
273 OBJECT_VERSION_NUMBER
274 ) select
275 X_RULE_ID,
276 X_ACTION_ID,
277 X_CREATED_BY,
278 X_CREATION_DATE,
279 X_LAST_UPDATED_BY,
280 X_LAST_UPDATE_DATE,
281 X_LAST_UPDATE_LOGIN,
282 X_START_DATE,
283 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
284 X_OBJECT_VERSION_NUMBER
285 from sys.dual
286 where not exists (select NULL
287 from AME_ACTION_USAGES
288 where RULE_ID = X_RULE_ID
289 and ACTION_ID = X_ACTION_ID
290 and sysdate between START_DATE
291 and nvl(END_DATE - (1/86400), sysdate));
292 end if;
293
294 end INSERT_USAGE_ROW;
295
296 procedure DELETE_ROW (
297 X_RULE_ID in VARCHAR2
298 ) is
299 begin
300 delete from AME_RULES
301 where RULE_ID = X_RULE_ID;
302 if (sql%notfound) then
303 raise no_data_found;
304 end if;
305 end DELETE_ROW;
306
307 procedure UPDATE_USAGE_ROW (
308 X_ACTION_USAGE_ROWID in VARCHAR2,
309 X_END_DATE in DATE)
310 is
311 begin
312 update AME_ACTION_USAGES set
313 END_DATE = X_END_DATE
314 where ROWID = X_ACTION_USAGE_ROWID;
315 end UPDATE_USAGE_ROW;
316
317 function CREATE_RULE_KEY return VARCHAR2 as
318 cursor GET_DBID_CURSOR is
319 select to_char(DB.DBID)
320 from V$DATABASE DB, V$INSTANCE INSTANCE
321 where upper(DB.NAME) = upper(INSTANCE.INSTANCE_NAME);
322 X_DATABASE_ID VARCHAR2(50);
323 X_NEW_RULE_KEY AME_RULES.RULE_KEY%TYPE;
324 X_RULE_COUNT NUMBER;
325 X_RULE_KEY_ID NUMBER;
326 begin
327 open GET_DBID_CURSOR;
328 fetch GET_DBID_CURSOR
329 into X_DATABASE_ID;
330 if GET_DBID_CURSOR%NOTFOUND then
331 -- This case will never happen, since every instance must be linked to a DB
332 X_DATABASE_ID := NULL;
333 end if;
334 close GET_DBID_CURSOR;
335 loop
336 -- derive RULE_KEY value
337 select AME_RULE_KEYS_S.NEXTVAL into X_RULE_KEY_ID from dual;
338 X_NEW_RULE_KEY := X_DATABASE_ID ||':'|| X_RULE_KEY_ID;
339 select count(*)
340 into X_RULE_COUNT
341 from AME_RULES
342 where upper(RULE_KEY) = upper(X_NEW_RULE_KEY)
343 and rownum < 2;
344 if X_RULE_COUNT = 0 then
345 exit;
346 end if;
347 end loop;
348 return(X_NEW_RULE_KEY);
349 exception
350 when others then
351 ame_util.runtimeException('ame_rules_api',
352 'create_rule_key',
353 sqlcode,
354 sqlerrm);
355 raise;
356 end CREATE_RULE_KEY;
357
358 procedure LOAD_ROW (
359 X_RULE_ID in VARCHAR2,
360 X_RULE_TYPE in VARCHAR2,
361 X_ACTION_PARAMETER in VARCHAR2,
362 X_ACTION_TYPE_NAME in VARCHAR2,
363 X_DESCRIPTION in VARCHAR2,
364 X_OWNER in VARCHAR2,
365 X_LAST_UPDATE_DATE in VARCHAR2
366 )
367 is
368 X_ACTION_TYPE_ID NUMBER;
369 X_ACTION_ID NUMBER;
370 X_ACTION_ID2 NUMBER;
371
372 X_CREATED_BY NUMBER;
373 X_CURRENT_USAGE_LUD VARCHAR2(19);
374 X_CURRENT_USAGE_OWNER NUMBER;
375 X_CURRENT_USAGE_OVN NUMBER;
376 X_ITEM_CLASS_ID NUMBER;
377 X_LAST_UPDATED_BY NUMBER;
378 X_LAST_UPDATE_LOGIN NUMBER;
379 X_RULE_COUNT NUMBER:= 0;
380 X_RULE_KEY ame_rules.rule_key%type;
381 X_USAGE_ROWID ROWID;
382 begin
383 X_AME_INSTALLATION_LEVEL := fnd_profile.value('AME_INSTALLATION_LEVEL');
384 KEY_TO_IDS (
385 X_RULE_ID,
386 X_ACTION_PARAMETER,
387 X_ACTION_TYPE_NAME,
388 X_ACTION_ID,
389 X_ACTION_TYPE_ID,
390 X_USAGE_ROWID,
391 X_RULE_COUNT,
392 X_CURRENT_USAGE_OWNER,
393 X_CURRENT_USAGE_LUD,
394 X_CURRENT_USAGE_OVN
395 );
396
397 VALIDATE_RULE_TYPE (
398 X_RULE_TYPE
399 );
400
401 OWNER_TO_WHO (
402 X_OWNER,
403 X_CREATED_BY,
404 X_LAST_UPDATED_BY,
405 X_LAST_UPDATE_LOGIN
406 );
407
408 begin
409 -- the current row was not found insert a new row
410 if (X_ACTION_ID is not null) and (X_RULE_COUNT = 0) then
411 -- Initialize X_ITEM_CLASS_ID and X_ACTION_ID2
412 X_ACTION_ID2 := X_ACTION_ID;
413 X_ITEM_CLASS_ID := null;
414 X_RULE_KEY := 'CHANGE_ME';
415 -- when target database is at AME11510, populate X_RULE_KEY,
416 -- nullify action_id and set X_ITEM_CLASS_ID = 1
417 if X_AME_INSTALLATION_LEVEL is not null then
418 X_RULE_KEY := CREATE_RULE_KEY;
419 X_ACTION_ID2 := null;
420 X_ITEM_CLASS_ID := 1;
421 end if;
422 INSERT_ROW (
423 X_RULE_ID,
424 X_RULE_KEY,
425 X_RULE_TYPE,
426 X_ACTION_ID2,
427 X_CREATED_BY,
428 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
429 X_LAST_UPDATED_BY,
430 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
431 X_LAST_UPDATE_LOGIN,
432 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
433 X_DESCRIPTION,
434 X_ITEM_CLASS_ID,
435 1);
436 -- when target database is at AME11510, populate ame_action_usages table
437 if X_AME_INSTALLATION_LEVEL is not null then
438 -- insert an ame_action_usages row
439 if X_USAGE_ROWID is null then
440 INSERT_USAGE_ROW (
441 X_RULE_ID,
442 X_ACTION_ID,
443 X_CREATED_BY,
444 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
445 X_LAST_UPDATED_BY,
446 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
450 else
447 X_LAST_UPDATE_LOGIN,
448 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
449 1);
451 if DO_USAGE_UPDATE_INSERT
452 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
453 X_CURRENT_USAGE_OWNER,
454 X_LAST_UPDATE_DATE,
455 X_CURRENT_USAGE_LUD) then
456 UPDATE_USAGE_ROW (
457 X_USAGE_ROWID,
458 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));
459 INSERT_USAGE_ROW (
460 X_RULE_ID,
461 X_ACTION_ID,
462 X_CREATED_BY,
463 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
464 X_LAST_UPDATED_BY,
465 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
466 X_LAST_UPDATE_LOGIN,
467 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
468 X_CURRENT_USAGE_OVN + 1);
469 end if;
470 end if;
471 end if;
472 end if;
473 end;
474 exception
475 when others then
476 ame_util.runtimeException('ame_rules_api',
477 'load_row',
478 sqlcode,
479 sqlerrm);
480 raise;
481 end LOAD_ROW;
482 --
483
484 END AME_RULES_API;