1 PACKAGE BODY AME_CONDITIONS_API AS
2 /* $Header: amecoapi.pkb 120.3 2006/03/15 01:23 pvelugul noship $ */
3
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_CONDITION_ID in VARCHAR2,
19 X_ATTRIBUTE_NAME in VARCHAR2,
20 X_CONDITION_COUNT out nocopy NUMBER,
21 X_ATTRIBUTE_ID out nocopy NUMBER
22 ) is
23 cursor CSR_GET_ATTRIBUTE
24 (
25 X_ATTRIBUTE_NAME in VARCHAR2
26 ) is
27 select ATTRIBUTE_ID
28 from AME_ATTRIBUTES
29 where NAME = X_ATTRIBUTE_NAME
30 and sysdate between START_DATE
31 and nvl(END_DATE-(1/86400), sysdate);
32
33 cursor CSR_IS_ATTRIBUTE_SEED
34 (
35 X_ATTRIBUTE_ID in VARCHAR2
36 ) is
37 select C.CREATED_BY
38 from AME_ATTRIBUTES C
39 where C.START_DATE =
40 (select min(A.START_DATE) from AME_ATTRIBUTES A
41 where A.ATTRIBUTE_ID = C.ATTRIBUTE_ID)
42 and C.ATTRIBUTE_ID = X_ATTRIBUTE_ID;
43
44 cursor CSR_GET_CURRENT_CONDITION
45 (
46 X_CONDITION_ID in VARCHAR2
47 ) is
48 select COUNT(*)
49 from AME_CONDITIONS
50 where CONDITION_ID = X_CONDITION_ID;
51 X_CREATED_BY NUMBER;
52 begin
53 open CSR_GET_ATTRIBUTE(
54 X_ATTRIBUTE_NAME
55 );
56 fetch CSR_GET_ATTRIBUTE into X_ATTRIBUTE_ID;
57 if (CSR_GET_ATTRIBUTE%notfound) then
58 X_ATTRIBUTE_ID := null;
59 end if;
60 close CSR_GET_ATTRIBUTE;
61
62 if X_ATTRIBUTE_ID is not null then
63 open CSR_IS_ATTRIBUTE_SEED(
64 X_ATTRIBUTE_ID
65 );
66 fetch CSR_IS_ATTRIBUTE_SEED into X_CREATED_BY;
67 if (CSR_IS_ATTRIBUTE_SEED%notfound) OR (X_CREATED_BY <> AME_SEED_UTILITY.SEED_USER_ID)
68 then
69 X_CREATED_BY := null;
70 end if;
71 close CSR_IS_ATTRIBUTE_SEED;
72 end if;
73
74 if X_ATTRIBUTE_ID is not null
75 then
76 open CSR_GET_CURRENT_CONDITION (
77 X_CONDITION_ID
78 );
79 fetch CSR_GET_CURRENT_CONDITION into X_CONDITION_COUNT;
80 close CSR_GET_CURRENT_CONDITION;
81 end if;
82 end KEY_TO_IDS;
83
84 procedure VALIDATE_CONDITION(
85 X_CONDITION_TYPE in VARCHAR2,
86 X_ATTRIBUTE_NAME in VARCHAR2,
87 X_PARAMETER_ONE in VARCHAR2,
88 X_PARAMETER_TWO in VARCHAR2,
89 X_PARAMETER_THREE in VARCHAR2,
90 X_INCLUDE_UPPER_LIMIT in VARCHAR2,
91 X_INCLUDE_LOWER_LIMIT in VARCHAR2
92 ) is
93 invalidConditionTypeException exception;
94 invalidCondAttrTypeException exception;
95 errorCode integer;
96 errorMessage ame_util.longestStringType;
97 X_ATTRIBUTE_TYPE varchar2(20);
98 X_PARAMETER_ONE_DATE date;
99 X_PARAMETER_TWO_DATE date;
100 X_PARAMETER_ONE_NUMBER number;
101 X_PARAMETER_TWO_NUMBER number;
102 begin
103 select ATTRIBUTE_TYPE
104 into X_ATTRIBUTE_TYPE
105 from AME_ATTRIBUTES
106 where NAME = X_ATTRIBUTE_NAME
107 and sysdate between START_DATE
108 and nvl(END_DATE-(1/86400), sysdate);
109
110 if X_CONDITION_TYPE not in
111 (ame_util.ordinaryConditionType , ame_util.exceptionConditionType) then
112 raise invalidConditionTypeException;
113 end if;
114
115 if X_ATTRIBUTE_TYPE = ame_util.booleanAttributeType then
116 if (X_PARAMETER_ONE not in
117 (ame_util.booleanAttributeTrue, ame_util.booleanAttributeFalse))
118 or (X_PARAMETER_ONE is null)
119 or (X_PARAMETER_TWO is not null)
120 or (X_PARAMETER_THREE is not null)
121 or (X_INCLUDE_UPPER_LIMIT is not null)
122 or (X_INCLUDE_LOWER_LIMIT is not null) then
123 errorMessage :=
124 'OAM is attempting to upload an invalid boolean attribute condition.';
125 raise invalidCondAttrTypeException;
126 end if;
127 end if;
128
129 if X_ATTRIBUTE_TYPE = ame_util.numberAttributeType then
130 if (X_INCLUDE_LOWER_LIMIT is not null
131 and (X_INCLUDE_LOWER_LIMIT not in
132 (ame_util.booleanTrue,ame_util.booleanFalse)))
133 or (X_INCLUDE_UPPER_LIMIT is not null
134 and (X_INCLUDE_UPPER_LIMIT not in
135 (ame_util.booleanTrue,ame_util.booleanFalse))) then
136 errorMessage :=
137 'OAM is attempting to upload an invalid number attribute condition.';
138 raise invalidCondAttrTypeException;
139 end if;
140 if X_PARAMETER_ONE is not null then
141 select to_number(X_PARAMETER_ONE)
142 into X_PARAMETER_ONE_NUMBER
143 from dual;
144 end if;
145 if X_PARAMETER_TWO is not null then
146 select to_number(X_PARAMETER_TWO)
147 into X_PARAMETER_TWO_NUMBER
148 from dual;
149 end if;
150 end if;
151
152 if X_ATTRIBUTE_TYPE = ame_util.currencyAttributeType then
153 if (X_INCLUDE_LOWER_LIMIT is not null
154 and (X_INCLUDE_LOWER_LIMIT not in
155 (ame_util.booleanTrue,ame_util.booleanFalse)))
156 or (X_INCLUDE_UPPER_LIMIT is not null
157 and (X_INCLUDE_UPPER_LIMIT not in
158 (ame_util.booleanTrue,ame_util.booleanFalse)))
159 or X_PARAMETER_THREE is null then
160 errorMessage :=
161 'OAM is attempting to upload an invalid currency attribute condition.';
162 raise invalidCondAttrTypeException;
163 end if;
164 if X_PARAMETER_ONE is not null then
165 select to_number(X_PARAMETER_ONE)
166 into X_PARAMETER_ONE_NUMBER
167 from dual;
168 end if;
169 if X_PARAMETER_TWO is not null then
170 select to_number(X_PARAMETER_TWO)
171 into X_PARAMETER_TWO_NUMBER
172 from dual;
173 end if;
174 end if;
175
176 if X_ATTRIBUTE_TYPE = ame_util.dateAttributeType then
177 if (X_INCLUDE_LOWER_LIMIT is not null
178 and (X_INCLUDE_LOWER_LIMIT not in
179 (ame_util.booleanTrue,ame_util.booleanFalse)))
180 or (X_INCLUDE_UPPER_LIMIT is not null
181 and (X_INCLUDE_UPPER_LIMIT not in
182 (ame_util.booleanTrue,ame_util.booleanFalse))) then
183 errorMessage :=
184 'OAM is attempting to upload an invalid date attribute condition.';
185 raise invalidCondAttrTypeException;
186 end if;
187 if X_PARAMETER_ONE is not null then
188 select to_date(X_PARAMETER_ONE,'YYYY:MM:DD:HH24:MI:SS')
189 into X_PARAMETER_ONE_DATE
190 from dual;
191 end if;
192 if X_PARAMETER_TWO is not null then
193 select to_date(X_PARAMETER_TWO,'YYYY:MM:DD:HH24:MI:SS')
194 into X_PARAMETER_TWO_DATE
195 from dual;
196 end if;
197 end if;
198
199 if X_ATTRIBUTE_TYPE = ame_util.stringAttributeType then
200 if (X_PARAMETER_ONE is not null)
201 or (X_PARAMETER_TWO is not null)
202 or (X_PARAMETER_THREE is not null)
203 or (X_INCLUDE_LOWER_LIMIT is not null)
204 or (X_INCLUDE_UPPER_LIMIT is not null) then
205 errorMessage :=
206 'OAM is attempting to upload an invalid string attribute condition.';
207 raise invalidCondAttrTypeException;
208 end if;
209 end if;
210 exception
211 when invalidConditionTypeException then
212 errorMessage :=
213 'OAM is attempting to upload an invalid condition type.';
214 errorCode := -20001;
215 ame_util.runtimeException(packageNameIn => 'ame_conditions_api',
216 routineNameIn => 'validate_condition',
217 exceptionNumberIn => errorCode,
218 exceptionStringIn => errorMessage);
219 raise_application_error(errorCode,
220 errorMessage);
221 when invalidCondAttrTypeException then
222 errorCode := -20001;
223 ame_util.runtimeException(packageNameIn => 'ame_conditions_api',
224 routineNameIn => 'validate_condition',
225 exceptionNumberIn => errorCode,
226 exceptionStringIn => errorMessage);
227 raise_application_error(errorCode,
228 errorMessage);
229 when invalid_number then
230 errorMessage :=
231 'OAM is attempting to upload an invalid number or currency attribute condition.';
232 errorCode := -20001;
233 ame_util.runtimeException(packageNameIn => 'ame_conditions_api',
234 routineNameIn => 'validate_condition',
235 exceptionNumberIn => errorCode,
236 exceptionStringIn => errorMessage);
237 raise_application_error(errorCode,
238 errorMessage);
239
240 when others then
241 ame_util.runtimeException('ame_conditions_api',
242 'validate_condition',
243 sqlcode,
244 sqlerrm);
245 raise;
246 end VALIDATE_CONDITION;
247
248 procedure INSERT_ROW (
249 X_CONDITION_ID in NUMBER,
250 X_CONDITION_KEY in VARCHAR2,
251 X_CONDITION_TYPE in VARCHAR2,
252 X_ATTRIBUTE_ID in NUMBER,
253 X_PARAMETER_ONE in VARCHAR2,
254 X_PARAMETER_TWO in VARCHAR2,
255 X_PARAMETER_THREE in VARCHAR2,
256 X_INCLUDE_UPPER_LIMIT in VARCHAR2,
257 X_INCLUDE_LOWER_LIMIT in VARCHAR2,
258 X_CREATED_BY in NUMBER,
259 X_CREATION_DATE in DATE,
260 X_LAST_UPDATED_BY in NUMBER,
261 X_LAST_UPDATE_DATE in DATE,
262 X_LAST_UPDATE_LOGIN in NUMBER,
263 X_START_DATE in DATE,
264 X_OBJECT_VERSION_NUMBER in NUMBER)
265 is
266 lockHandle varchar2(500);
267 returnValue integer;
268
269 begin
270 if X_AME_INSTALLATION_LEVEL is not null then
271 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_CONDITIONS.'||X_CONDITION_ID,lockhandle => lockHandle);
272 else
273 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_CONDITIONS.'||X_CONDITION_ID,lockhandle => lockHandle);
274 end if;
275 returnValue := DBMS_LOCK.REQUEST(lockhandle => lockHandle,timeout => 0,release_on_commit => true);
276 if returnValue = 0 then
277 insert into AME_CONDITIONS
278 (
279 CONDITION_ID,
280 CONDITION_KEY,
281 CONDITION_TYPE,
282 ATTRIBUTE_ID,
283 PARAMETER_ONE,
284 PARAMETER_TWO,
285 PARAMETER_THREE,
286 INCLUDE_UPPER_LIMIT,
287 INCLUDE_LOWER_LIMIT,
288 CREATED_BY,
289 CREATION_DATE,
290 LAST_UPDATED_BY,
291 LAST_UPDATE_DATE,
292 LAST_UPDATE_LOGIN,
293 START_DATE,
294 END_DATE,
295 OBJECT_VERSION_NUMBER
296 ) select
297 X_CONDITION_ID,
298 X_CONDITION_KEY,
299 X_CONDITION_TYPE,
300 X_ATTRIBUTE_ID,
301 X_PARAMETER_ONE,
302 X_PARAMETER_TWO,
303 X_PARAMETER_THREE,
304 X_INCLUDE_UPPER_LIMIT,
305 X_INCLUDE_LOWER_LIMIT,
306 X_CREATED_BY,
307 X_CREATION_DATE,
308 X_LAST_UPDATED_BY,
309 X_LAST_UPDATE_DATE,
310 X_LAST_UPDATE_LOGIN,
311 X_START_DATE,
312 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
313 X_OBJECT_VERSION_NUMBER
314 from sys.dual
315 where not exists (select NULL
316 from AME_CONDITIONS
317 where ((CONDITION_ID = X_CONDITION_ID
318 and sysdate < nvl(END_DATE - (1/86400), sysdate + (1/86400)))
319 or (X_AME_INSTALLATION_LEVEL is not null and CONDITION_KEY = X_CONDITION_KEY))
320 );
321 end if;
322 end INSERT_ROW;
323
324 procedure DELETE_ROW (
325 X_CONDITION_ID in NUMBER
326 ) is
327 begin
328 delete from AME_CONDITIONS
329 where CONDITION_ID = X_CONDITION_ID;
330 if (sql%notfound) then
331 raise no_data_found;
332 end if;
333 end DELETE_ROW;
334
335 function CREATE_CONDITION_KEY return VARCHAR2 as
336 cursor GET_DBID_CURSOR is
337 select to_char(DB.DBID)
338 from V$DATABASE DB,
339 V$INSTANCE INSTANCE
340 where upper(DB.NAME) = upper(INSTANCE.INSTANCE_NAME);
341 X_DATABASE_ID VARCHAR2(50);
342 X_NEW_CONDITION_KEY AME_CONDITIONS.CONDITION_KEY%TYPE;
343 X_CONDITION_COUNT NUMBER;
344 X_CONDITION_KEY_ID NUMBER;
345 begin
346 open GET_DBID_CURSOR;
347 fetch GET_DBID_CURSOR
348 into X_DATABASE_ID;
349 if GET_DBID_CURSOR%NOTFOUND then
350 -- This case will never happen, since every instance must be linked to a DB
351 X_DATABASE_ID := NULL;
352 end if;
353 close GET_DBID_CURSOR;
354 loop
355 -- derive CONDITION_KEY value
356 select AME_CONDITION_KEYS_S.NEXTVAL into X_CONDITION_KEY_ID from dual;
357 X_NEW_CONDITION_KEY := X_DATABASE_ID ||':'|| X_CONDITION_KEY_ID;
358 select count(*)
359 into X_CONDITION_COUNT
360 from AME_CONDITIONS
361 where upper(CONDITION_KEY) = upper(X_NEW_CONDITION_KEY)
362 and rownum < 2;
363 if X_CONDITION_COUNT = 0 then
364 exit;
365 end if;
366 end loop;
367 return(X_NEW_CONDITION_KEY);
368 exception
369 when others then
370 ame_util.runtimeException('ame_conditions_api',
371 'create_condition_key',
372 sqlcode,
373 sqlerrm);
374 raise;
375 end CREATE_CONDITION_KEY;
376
377 procedure LOAD_ROW (
378 X_CONDITION_ID in VARCHAR2,
379 X_CONDITION_TYPE in VARCHAR2,
380 X_ATTRIBUTE_NAME in VARCHAR2,
381 X_PARAMETER_ONE in VARCHAR2,
382 X_PARAMETER_TWO in VARCHAR2,
383 X_PARAMETER_THREE in VARCHAR2,
384 X_INCLUDE_UPPER_LIMIT in VARCHAR2,
385 X_INCLUDE_LOWER_LIMIT in VARCHAR2,
386 X_OWNER in VARCHAR2,
387 X_LAST_UPDATE_DATE in VARCHAR2
388 )
389 is
390 X_ATTRIBUTE_ID NUMBER;
391 X_CONDITION_COUNT NUMBER :=0;
392 X_CONDITION_KEY VARCHAR2(100);
393 X_CREATED_BY NUMBER;
394 X_LAST_UPDATED_BY NUMBER;
395 X_LAST_UPDATE_LOGIN NUMBER;
396 begin
397 -- Check whether the target database is at AME11510 or not
398 X_AME_INSTALLATION_LEVEL:=fnd_profile.value('AME_INSTALLATION_LEVEL');
399 KEY_TO_IDS (
400 X_CONDITION_ID,
401 X_ATTRIBUTE_NAME,
402 X_CONDITION_COUNT,
403 X_ATTRIBUTE_ID
404 );
405 VALIDATE_CONDITION(
406 X_CONDITION_TYPE,
407 X_ATTRIBUTE_NAME,
408 X_PARAMETER_ONE,
409 X_PARAMETER_TWO,
410 X_PARAMETER_THREE,
411 X_INCLUDE_UPPER_LIMIT,
412 X_INCLUDE_LOWER_LIMIT
413 );
414 OWNER_TO_WHO (
415 X_OWNER,
416 X_CREATED_BY,
417 X_LAST_UPDATED_BY,
418 X_LAST_UPDATE_LOGIN
419 );
420 begin
421 -- the current row was not found insert a new row
422 if (X_ATTRIBUTE_ID is not null) and (X_CONDITION_COUNT = 0) then
423 if X_AME_INSTALLATION_LEVEL is not null then
424 X_CONDITION_KEY := CREATE_CONDITION_KEY;
425 else
426 X_CONDITION_KEY := 'CHANGE_ME';
427 end if;
428 INSERT_ROW (
429 X_CONDITION_ID,
430 X_CONDITION_KEY,
431 X_CONDITION_TYPE,
432 X_ATTRIBUTE_ID,
433 X_PARAMETER_ONE,
434 X_PARAMETER_TWO,
435 X_PARAMETER_THREE,
436 X_INCLUDE_UPPER_LIMIT,
437 X_INCLUDE_LOWER_LIMIT,
438 X_CREATED_BY,
439 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
440 X_LAST_UPDATED_BY,
441 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
442 X_LAST_UPDATE_LOGIN,
443 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
444 1);
445 end if;
446 -- the current row was found end date the current row
447 -- do not update or insert.
448 end;
449 exception
450 when others then
451 ame_util.runtimeException('ame_conditions_api',
452 'load_row',
453 sqlcode,
454 sqlerrm);
455 raise;
456 end LOAD_ROW;
457 --
458 END AME_CONDITIONS_API;
[Home] [Help]