[Home] [Help]
PACKAGE BODY: APPS.AME_CONFIG_VARS_API
Source
1 package BODY AME_CONFIG_VARS_API as
2 /* $Header: amecvapi.pkb 120.6 2005/11/14 06:03 sbadiger noship $ */
3 procedure KEY_TO_IDS
4 (X_APPLICATION_SHORT_NAME in varchar2
5 ,X_TRANSACTION_TYPE_ID in varchar2
6 ,X_APPLICATION_ID out nocopy number
7 ) as
8 begin
9 if X_APPLICATION_SHORT_NAME is null then
10 if AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is not null and to_number(AME_SEED_UTILITY.AME_INSTALLATION_LEVEL) >= 2 then
11 X_APPLICATION_ID := 0;
12 else
13 X_APPLICATION_ID := null;
14 end if;
15 else
16 begin
17 select ACA.APPLICATION_ID
18 into X_APPLICATION_ID
19 from AME_CALLING_APPS ACA,
20 FND_APPLICATION_VL FAV
21 where FAV.APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
22 and FAV.APPLICATION_ID = ACA.FND_APPLICATION_ID
23 and ((ACA.TRANSACTION_TYPE_ID is null and X_TRANSACTION_TYPE_ID is null) or
24 ACA.TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID)
25 and sysdate between ACA.START_DATE and nvl(ACA.END_DATE - (1/86400),sysdate);
26 exception
27 when no_data_found then
28 raise_application_error(-20001,'AME Transaction Type ' || X_APPLICATION_SHORT_NAME || ',' || X_TRANSACTION_TYPE_ID || ' not found');
29 end;
30 end if;
31 end KEY_TO_IDS;
32
33 procedure FORMAT_ROW
34 (X_VARIABLE_NAME in varchar2
35 ,X_VARIABLE_VALUE in out nocopy varchar2
36 ) as
37 begin
38 if X_VARIABLE_NAME = 'rulePriorityModes' then
39 if AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is not null and
40 instrb(X_VARIABLE_VALUE,':',1,7) = 0 then
41 X_VARIABLE_VALUE := 'disabled:' || X_VARIABLE_VALUE || ':disabled';
42 elsif AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is null and instrb(X_VARIABLE_VALUE,':',1,7) > 0 then
43 X_VARIABLE_VALUE := substrb(X_VARIABLE_VALUE, instrb(X_VARIABLE_VALUE,':', 1, 1)+1);
44 X_VARIABLE_VALUE := substrb(X_VARIABLE_VALUE, 1, instrb(X_VARIABLE_VALUE,':',1,6)-1);
45 end if;
46 end if;
47 end FORMAT_ROW;
48
49 function CHK_UPDATE
50 (X_VARIABLE_NAME in varchar2
51 ,X_VARIABLE_VALUE in varchar2
52 ,X_APPLICATION_ID in number
53 ) return boolean as
54 X_CURRENT_VARIABLE_VALUE AME_CONFIG_VARS.VARIABLE_VALUE%TYPE;
55 begin
56 select ACV.VARIABLE_VALUE
57 into X_CURRENT_VARIABLE_VALUE
58 from AME_CONFIG_VARS ACV
59 where ACV.VARIABLE_NAME = X_VARIABLE_NAME
60 and ((ACV.APPLICATION_ID is null and X_APPLICATION_ID is null) or
61 ACV.APPLICATION_ID = X_APPLICATION_ID)
62 and sysdate between ACV.START_DATE and nvl(ACV.END_DATE - (1/86400),sysdate);
63 if X_VARIABLE_NAME = 'productionFunctionality' then
64 if X_CURRENT_VARIABLE_VALUE = X_VARIABLE_VALUE then
65 return true;
66 elsif X_CURRENT_VARIABLE_VALUE in ('approver','transaction') then
67 if X_VARIABLE_VALUE = 'all' then
68 return true;
69 else
70 return false;
71 end if;
72 else
73 return true;
74 end if;
75 elsif X_VARIABLE_NAME in ('allowAllApproverTypes'
76 ,'allowFyiNotifications'
77 ,'allowAllItemClassRules') then
78 if X_CURRENT_VARIABLE_VALUE = 'yes' and
79 X_VARIABLE_VALUE <> X_CURRENT_VARIABLE_VALUE then
80 return false;
81 else
82 return true;
83 end if;
84 end if;
85 return true;
86 end CHK_UPDATE;
87
88 procedure INSERT_ROW
89 (X_VARIABLE_NAME in varchar2
90 ,X_USER_CONFIG_VAR_NAME in varchar2
91 ,X_APPLICATION_ID in number
92 ,X_VARIABLE_VALUE in varchar2
93 ,X_DESCRIPTION in varchar2
94 ,X_START_DATE in date
95 ,X_END_DATE in date
96 ,X_CREATED_BY in number
97 ,X_CREATION_DATE in date
98 ,X_LAST_UPDATED_BY in number
99 ,X_LAST_UPDATE_DATE in date
100 ,X_LAST_UPDATE_LOGIN in number
101 ,X_OBJECT_VERSION_NUMBER in number
102 ) as
103 X_LOCK_HANDLE varchar2(500);
104 X_RETURN_VALUE number;
105 begin
106 DBMS_LOCK.ALLOCATE_UNIQUE
107 (LOCKNAME =>'AME_CONFIG_VARS.'||X_VARIABLE_NAME||X_APPLICATION_ID
108 ,LOCKHANDLE => X_LOCK_HANDLE
109 );
110 X_RETURN_VALUE := DBMS_LOCK.REQUEST
111 (LOCKHANDLE => X_LOCK_HANDLE
112 ,TIMEOUT => 0
113 ,RELEASE_ON_COMMIT => true);
114
115 if X_RETURN_VALUE = 0 then
116 insert into AME_CONFIG_VARS
117 (VARIABLE_NAME
118 ,VARIABLE_VALUE
119 ,DESCRIPTION
120 ,CREATED_BY
121 ,CREATION_DATE
122 ,LAST_UPDATED_BY
123 ,LAST_UPDATE_DATE
124 ,LAST_UPDATE_LOGIN
125 ,START_DATE
126 ,END_DATE
127 ,APPLICATION_ID
128 ,OBJECT_VERSION_NUMBER
129 ) select X_VARIABLE_NAME,
130 X_VARIABLE_VALUE,
131 X_DESCRIPTION,
132 X_CREATED_BY,
133 X_CREATION_DATE,
134 X_LAST_UPDATED_BY,
135 X_LAST_UPDATE_DATE,
136 X_LAST_UPDATE_LOGIN,
137 X_START_DATE,
138 X_END_DATE,
139 X_APPLICATION_ID,
140 X_OBJECT_VERSION_NUMBER
141 from dual where not exists (select null
142 from AME_CONFIG_VARS
143 where VARIABLE_NAME = X_VARIABLE_NAME
144 and (((APPLICATION_ID is null or APPLICATION_ID = 0)
145 and (X_APPLICATION_ID is null or X_APPLICATION_ID = 0))
146 or (APPLICATION_ID = X_APPLICATION_ID))
147 and sysdate between START_DATE and nvl(END_DATE - (1/86400), sysdate));
148
149 if not AME_SEED_UTILITY.MLS_ENABLED then
150 return;
151 end if;
152 if(X_APPLICATION_ID = 0 or X_APPLICATION_ID = null) then
153 insert into AME_CONFIG_VARS_TL
154 (VARIABLE_NAME
155 ,USER_CONFIG_VAR_NAME
156 ,DESCRIPTION
157 ,CREATED_BY
158 ,CREATION_DATE
159 ,LAST_UPDATED_BY
160 ,LAST_UPDATE_DATE
161 ,LAST_UPDATE_LOGIN
162 ,LANGUAGE
163 ,SOURCE_LANG
164 ) select X_VARIABLE_NAME,
165 nvl(X_USER_CONFIG_VAR_NAME,X_VARIABLE_NAME),
166 X_DESCRIPTION,
167 X_CREATED_BY,
168 X_CREATION_DATE,
169 X_LAST_UPDATED_BY,
170 X_LAST_UPDATE_DATE,
171 X_LAST_UPDATE_LOGIN,
172 L.LANGUAGE_CODE,
173 userenv('LANG')
174 from FND_LANGUAGES L
175 where L.INSTALLED_FLAG in ('I', 'B')
176 and not exists (select null
177 from AME_CONFIG_VARS_TL T
178 where T.VARIABLE_NAME = X_VARIABLE_NAME
179 and T.LANGUAGE = L.LANGUAGE_CODE);
180 end if;
181 end if;
182 end INSERT_ROW;
183
184 procedure UPDATE_ROW
185 (X_VARIABLE_NAME in varchar2
186 ,X_USER_CONFIG_VAR_NAME in varchar2
187 ,X_APPLICATION_ID in number
188 ,X_VARIABLE_VALUE in varchar2
189 ,X_DESCRIPTION in varchar2
190 ,X_START_DATE in date
191 ,X_END_DATE in date
192 ,X_CREATED_BY in number
193 ,X_CREATION_DATE in date
194 ,X_LAST_UPDATED_BY in number
195 ,X_LAST_UPDATE_DATE in date
196 ,X_LAST_UPDATE_LOGIN in number
197 ,X_OBJECT_VERSION_NUMBER in number
198 ) as
199 X_LOCK_HANDLE varchar2(500);
200 X_RETURN_VALUE number;
201 begin
202
203 DBMS_LOCK.ALLOCATE_UNIQUE
204 (LOCKNAME =>'AME_CONFIG_VARS.'||X_VARIABLE_NAME||to_char(nvl(X_APPLICATION_ID,0))
205 ,LOCKHANDLE => X_LOCK_HANDLE
206 );
207 X_RETURN_VALUE := DBMS_LOCK.REQUEST
208 (LOCKHANDLE => X_LOCK_HANDLE
209 ,TIMEOUT => 0
210 ,RELEASE_ON_COMMIT => true);
211
212 if X_RETURN_VALUE = 0 then
213 update AME_CONFIG_VARS ACV
214 set ACV.END_DATE = X_START_DATE
215 where ACV.VARIABLE_NAME = X_VARIABLE_NAME
216 and ((ACV.APPLICATION_ID is null and X_APPLICATION_ID is null) or
217 ACV.APPLICATION_ID = X_APPLICATION_ID)
218 and sysdate between ACV.START_DATE and nvl(ACV.END_DATE - (1/86400),sysdate);
219
220 insert into AME_CONFIG_VARS
221 (VARIABLE_NAME
222 ,APPLICATION_ID
223 ,VARIABLE_VALUE
224 ,DESCRIPTION
225 ,START_DATE
226 ,END_DATE
227 ,CREATED_BY
228 ,CREATION_DATE
229 ,LAST_UPDATED_BY
230 ,LAST_UPDATE_DATE
231 ,LAST_UPDATE_LOGIN
232 ,OBJECT_VERSION_NUMBER
233 ) values
234 (X_VARIABLE_NAME
235 ,X_APPLICATION_ID
236 ,X_VARIABLE_VALUE
237 ,X_DESCRIPTION
238 ,X_START_DATE
239 ,X_END_DATE
240 ,X_CREATED_BY
241 ,X_CREATION_DATE
242 ,X_LAST_UPDATED_BY
243 ,X_LAST_UPDATE_DATE
244 ,X_LAST_UPDATE_LOGIN
245 ,X_OBJECT_VERSION_NUMBER
246 );
247
248 if not AME_SEED_UTILITY.MLS_ENABLED then
249 return;
250 end if;
251 if(X_APPLICATION_ID = 0 or X_APPLICATION_ID = null) then
252 update AME_CONFIG_VARS_TL
253 set USER_CONFIG_VAR_NAME = nvl(X_USER_CONFIG_VAR_NAME,USER_CONFIG_VAR_NAME),
254 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
255 SOURCE_LANG = userenv('LANG'),
256 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
257 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
258 LAST_UPDATE_LOGIN = 0
259 where VARIABLE_NAME = X_VARIABLE_NAME
260 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
261 end if;
262 end if;
263 end UPDATE_ROW;
264
265 procedure FORCE_UPDATE_ROW (
266 X_ROWID in VARCHAR2,
267 X_VARIABLE_NAME in VARCHAR2,
268 X_USER_CONFIG_VAR_NAME in VARCHAR2,
269 X_APPLICATION_ID in Number,
270 X_VARIABLE_VALUE in VARCHAR2,
271 X_DESCRIPTION in VARCHAR2,
272 X_CREATED_BY in NUMBER,
273 X_CREATION_DATE in DATE,
274 X_LAST_UPDATED_BY in NUMBER,
275 X_LAST_UPDATE_DATE in DATE,
276 X_LAST_UPDATE_LOGIN in NUMBER,
277 X_START_DATE in DATE,
278 X_END_DATE in DATE,
279 X_OBJECT_VERSION_NUMBER in NUMBER
280 ) is
281 begin
282 update AME_CONFIG_VARS
283 set VARIABLE_VALUE = X_VARIABLE_VALUE,
284 DESCRIPTION = X_DESCRIPTION,
285 CREATED_BY = X_CREATED_BY,
286 CREATION_DATE = X_CREATION_DATE,
287 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
288 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
289 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
290 START_DATE = X_START_DATE,
291 END_DATE = X_END_DATE,
292 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
293 where ROWID = X_ROWID;
294
295 if not AME_SEED_UTILITY.MLS_ENABLED then
296 return;
297 end if;
298 if(X_APPLICATION_ID = 0 or X_APPLICATION_ID = null) then
299 update AME_CONFIG_VARS_TL
300 set USER_CONFIG_VAR_NAME = nvl(X_USER_CONFIG_VAR_NAME,USER_CONFIG_VAR_NAME),
301 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
302 SOURCE_LANG = userenv('LANG'),
303 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
304 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
305 LAST_UPDATE_LOGIN = 0
306 where VARIABLE_NAME = X_VARIABLE_NAME
307 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
308 end if;
309 end FORCE_UPDATE_ROW;
310
311 procedure LOAD_ROW
312 (X_VARIABLE_NAME in varchar2
313 ,X_APPLICATION_SHORT_NAME in varchar2
314 ,X_TRANSACTION_TYPE_ID in varchar2
315 ,X_USER_CONFIG_VAR_NAME in varchar2
316 ,X_DESCRIPTION in varchar2
317 ,X_VARIABLE_VALUE in varchar2
318 ,X_OWNER in varchar2
319 ,X_LAST_UPDATE_DATE in varchar2
320 ,X_CUSTOM_MODE in varchar2
321 ) as
322 L_VARIABLE_NAME AME_CONFIG_VARS.VARIABLE_NAME%TYPE;
323 L_APPLICATION_SHORT_NAME FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE;
324 L_TRANSACTION_TYPE_ID AME_CALLING_APPS.TRANSACTION_TYPE_ID%TYPE;
325 L_APPLICATION_ID number;
326 L_VARIABLE_VALUE AME_CONFIG_VARS.VARIABLE_VALUE%TYPE;
327 L_USER_CONFIG_VAR_NAME AME_CONFIG_VARS_TL.USER_CONFIG_VAR_NAME%TYPE;
328 L_DESCRIPTION AME_CONFIG_VARS_TL.DESCRIPTION%TYPE;
329 L_OWNER varchar2(100);
330 L_LAST_UPDATE_DATE varchar2(19);
331 L_END_DATE date;
332 L_DUMMY varchar2(1);
333 L_OBJECT_VERSION_NUMBER number;
334 L_ROWID ROWID;
335 begin
336 L_VARIABLE_NAME := X_VARIABLE_NAME;
337 L_APPLICATION_SHORT_NAME := X_APPLICATION_SHORT_NAME;
338 L_TRANSACTION_TYPE_ID := X_TRANSACTION_TYPE_ID;
339 L_VARIABLE_VALUE := X_VARIABLE_VALUE;
340 L_USER_CONFIG_VAR_NAME := X_USER_CONFIG_VAR_NAME;
341 L_DESCRIPTION := X_DESCRIPTION;
342 L_OWNER := X_OWNER;
343 L_LAST_UPDATE_DATE := X_LAST_UPDATE_DATE;
344 L_END_DATE := AME_SEED_UTILITY.GET_DEFAULT_END_DATE;
345
346 KEY_TO_IDS
347 (X_APPLICATION_SHORT_NAME => L_APPLICATION_SHORT_NAME
348 ,X_TRANSACTION_TYPE_ID => L_TRANSACTION_TYPE_ID
349 ,X_APPLICATION_ID => L_APPLICATION_ID
350 );
351 FORMAT_ROW
352 (X_VARIABLE_NAME => L_VARIABLE_NAME
353 ,X_VARIABLE_VALUE => L_VARIABLE_VALUE
354 );
355 begin
356 select nvl(ACV.OBJECT_VERSION_NUMBER,1),
357 ROWID
358 into L_OBJECT_VERSION_NUMBER,
359 L_ROWID
360 from AME_CONFIG_VARS ACV
361 where ACV.VARIABLE_NAME = L_VARIABLE_NAME
362 and ((ACV.APPLICATION_ID is null and L_APPLICATION_ID is null) or
363 ACV.APPLICATION_ID = L_APPLICATION_ID)
364 and sysdate between ACV.START_DATE and nvl(ACV.END_DATE - (1/86400),sysdate);
365
366 if CHK_UPDATE
367 (X_VARIABLE_NAME => L_VARIABLE_NAME
368 ,X_VARIABLE_VALUE => L_VARIABLE_VALUE
369 ,X_APPLICATION_ID => L_APPLICATION_ID) then
370 if X_CUSTOM_MODE = 'FORCE' then
371 FORCE_UPDATE_ROW
372 (X_ROWID => L_ROWID
373 ,X_VARIABLE_NAME => L_VARIABLE_NAME
374 ,X_USER_CONFIG_VAR_NAME => L_USER_CONFIG_VAR_NAME
375 ,X_APPLICATION_ID => L_APPLICATION_ID
376 ,X_VARIABLE_VALUE => L_VARIABLE_VALUE
377 ,X_DESCRIPTION => L_DESCRIPTION
378 ,X_CREATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
379 ,X_CREATION_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
380 ,X_LAST_UPDATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
381 ,X_LAST_UPDATE_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
382 ,X_LAST_UPDATE_LOGIN => 0
383 ,X_START_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
384 ,X_END_DATE => L_END_DATE
385 ,X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
386 );
387 else
388 UPDATE_ROW
389 (X_VARIABLE_NAME => L_VARIABLE_NAME
390 ,X_USER_CONFIG_VAR_NAME => L_USER_CONFIG_VAR_NAME
391 ,X_APPLICATION_ID => L_APPLICATION_ID
392 ,X_VARIABLE_VALUE => L_VARIABLE_VALUE
393 ,X_DESCRIPTION => L_DESCRIPTION
394 ,X_START_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
395 ,X_END_DATE => L_END_DATE
396 ,X_CREATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
397 ,X_CREATION_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
398 ,X_LAST_UPDATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
399 ,X_LAST_UPDATE_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
400 ,X_LAST_UPDATE_LOGIN => 0
401 ,X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
402 );
403 end if;
404 end if;
405 exception
406 when no_data_found then
407 INSERT_ROW
408 (X_VARIABLE_NAME => L_VARIABLE_NAME
409 ,X_USER_CONFIG_VAR_NAME => L_USER_CONFIG_VAR_NAME
410 ,X_APPLICATION_ID => L_APPLICATION_ID
411 ,X_VARIABLE_VALUE => L_VARIABLE_VALUE
412 ,X_DESCRIPTION => L_DESCRIPTION
413 ,X_START_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
414 ,X_END_DATE => L_END_DATE
415 ,X_CREATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
416 ,X_CREATION_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
417 ,X_LAST_UPDATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
418 ,X_LAST_UPDATE_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
419 ,X_LAST_UPDATE_LOGIN => 0
420 ,X_OBJECT_VERSION_NUMBER => 1
421 );
422 end;
423 end LOAD_ROW;
424
425 procedure TRANSLATE_ROW
426 (X_VARIABLE_NAME in varchar2
427 ,X_USER_CONFIG_VAR_NAME in varchar2
428 ,X_DESCRIPTION in varchar2
429 ,X_OWNER in varchar2
430 ,X_LAST_UPDATE_DATE in varchar2
431 ) as
432 L_DUMMY varchar2(1);
433 begin
434 if not AME_SEED_UTILITY.MLS_ENABLED then
435 return;
436 end if;
437 begin
438 select null
439 into L_DUMMY
440 from AME_CONFIG_VARS_TL ACVTL
441 where ACVTL.VARIABLE_NAME = X_VARIABLE_NAME
442 and ACVTL.LANGUAGE = userenv('LANG');
443
444 update AME_CONFIG_VARS_TL ACVTL
445 set USER_CONFIG_VAR_NAME = nvl(X_USER_CONFIG_VAR_NAME,ACVTL.USER_CONFIG_VAR_NAME),
446 DESCRIPTION = nvl(X_DESCRIPTION,ACVTL.DESCRIPTION),
447 SOURCE_LANG = userenv('LANG'),
448 LAST_UPDATE_DATE = AME_SEED_UTILITY.DATE_AS_DATE(X_LAST_UPDATE_DATE),
449 LAST_UPDATED_BY = AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
450 LAST_UPDATE_LOGIN = 0
451 where ACVTL.VARIABLE_NAME = X_VARIABLE_NAME
452 and userenv('LANG') in (ACVTL.LANGUAGE,ACVTL.SOURCE_LANG);
453 exception
454 when no_data_found then
455 null;
456 end;
457 end TRANSLATE_ROW;
458
459 function MERGE_ROW_TEST
460 (X_VARIABLE_NAME in varchar2
461 ,X_APPLICATION_SHORT_NAME in varchar2
462 ,X_TRANSACTION_TYPE_ID in varchar2
463 ,X_OWNER in varchar2
464 ,X_LAST_UPDATE_DATE in varchar2
465 ,X_UPLOAD_MODE in varchar2
466 ,X_CUSTOM_MODE in varchar2
467 ) return boolean as
468 X_CURRENT_OWNER NUMBER;
469 X_CREATED_BY varchar2(100);
470 X_CURRENT_LAST_UPDATE_DATE varchar2(19);
471 begin
472 if X_UPLOAD_MODE = 'NLS' then
473 begin
474 select ACVTL.LAST_UPDATED_BY,
475 AME_SEED_UTILITY.DATE_AS_STRING(ACVTL.LAST_UPDATE_DATE),
476 AME_SEED_UTILITY.OWNER_AS_STRING(ACVTL.CREATED_BY)
477 into X_CURRENT_OWNER,
478 X_CURRENT_LAST_UPDATE_DATE,
479 X_CREATED_BY
480 from AME_CONFIG_VARS_TL ACVTL
481 where ACVTL.VARIABLE_NAME = X_VARIABLE_NAME
482 and ACVTL.LANGUAGE = userenv('LANG');
483 if AME_SEED_UTILITY.IS_SEED_USER(X_CREATED_BY) then
484 return true;
485 else
486 return AME_SEED_UTILITY.TL_MERGE_ROW_TEST
487 (X_CURRENT_OWNER => X_CURRENT_OWNER
488 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
489 ,X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER)
490 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
491 ,X_CUSTOM_MODE => X_CUSTOM_MODE
492 );
493 end if;
494 exception
495 when no_data_found then
496 return true;
497 end;
498 else
499 begin
500 if X_APPLICATION_SHORT_NAME is not null then
501 select ACV.LAST_UPDATED_BY,
502 AME_SEED_UTILITY.DATE_AS_STRING(ACV.LAST_UPDATE_DATE)
503 into X_CURRENT_OWNER,
504 X_CURRENT_LAST_UPDATE_DATE
505 from AME_CONFIG_VARS ACV,
506 AME_CALLING_APPS ACA,
507 FND_APPLICATION_VL FAV
508 where FAV.APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
509 and FAV.APPLICATION_ID = ACA.FND_APPLICATION_ID
510 and ((ACA.TRANSACTION_TYPE_ID is null and X_TRANSACTION_TYPE_ID is null) or
511 ACA.TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID)
512 and ACV.APPLICATION_ID = ACA.APPLICATION_ID
513 and ACV.VARIABLE_NAME = X_VARIABLE_NAME
514 and sysdate between ACV.START_DATE and nvl(ACV.END_DATE - (1/86400),sysdate)
515 and sysdate between ACA.START_DATE and nvl(ACA.END_DATE - (1/86400),sysdate);
516 else
517 select ACV.LAST_UPDATED_BY,
518 AME_SEED_UTILITY.DATE_AS_STRING(ACV.LAST_UPDATE_DATE)
519 into X_CURRENT_OWNER,
520 X_CURRENT_LAST_UPDATE_DATE
521 from AME_CONFIG_VARS ACV
522 where (ACV.APPLICATION_ID is null or ACV.APPLICATION_ID = 0)
523 and ACV.VARIABLE_NAME = X_VARIABLE_NAME
524 and sysdate between ACV.START_DATE and nvl(ACV.END_DATE - (1/86400),sysdate);
525 end if;
526 return AME_SEED_UTILITY.MERGE_ROW_TEST
527 (X_CURRENT_OWNER => X_CURRENT_OWNER
528 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
529 ,X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER)
530 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
531 ,X_CUSTOM_MODE => X_CUSTOM_MODE
532 );
533 exception
534 when no_data_found then
535 return true;
536 end;
537 end if;
538 end MERGE_ROW_TEST;
539
540 procedure LOAD_SEED_ROW
541 (X_VARIABLE_NAME in varchar2
542 ,X_APPLICATION_SHORT_NAME in varchar2
543 ,X_TRANSACTION_TYPE_ID in varchar2
544 ,X_USER_CONFIG_VAR_NAME in varchar2
545 ,X_DESCRIPTION in varchar2
546 ,X_VARIABLE_VALUE in varchar2
547 ,X_OWNER in varchar2
548 ,X_LAST_UPDATE_DATE in varchar2
549 ,X_UPLOAD_MODE in varchar2
550 ,X_CUSTOM_MODE in varchar2
551 ) as
552 X_ATTRIBUTE_VALUE varchar2(10);
553 X_APPLICATION_NAME AME_CALLING_APPS.APPLICATION_NAME%TYPE;
554 begin
555 AME_SEED_UTILITY.INIT_AME_INSTALLATION_LEVEL;
556
557 if AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is not null and
558 X_VARIABLE_NAME = 'useWorkflow' then
559 if X_VARIABLE_VALUE = 'yes' then
560 X_ATTRIBUTE_VALUE := 'true';
561 else
562 X_ATTRIBUTE_VALUE := 'false';
563 end if;
564 begin
565 select ACA.APPLICATION_NAME
566 into X_APPLICATION_NAME
567 from AME_CALLING_APPS ACA,
568 FND_APPLICATION_VL FAV
569 where FAV.APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME
570 and FAV.APPLICATION_ID = ACA.FND_APPLICATION_ID
571 and ((X_TRANSACTION_TYPE_ID is null and ACA.TRANSACTION_TYPE_ID is null) or
572 X_TRANSACTION_TYPE_ID = ACA.TRANSACTION_TYPE_ID)
573 and sysdate between ACA.START_DATE and nvl(ACA.END_DATE - (1/86400),sysdate);
574 exception
575 when no_data_found then
576 X_APPLICATION_NAME := X_APPLICATION_SHORT_NAME;
577 end;
578 AME_ATTRIBUTE_USAGES_API.LOAD_SEED_ROW
579 (X_ATTRIBUTE_NAME => 'USE_WORKFLOW'
580 ,X_APPLICATION_NAME => X_APPLICATION_NAME
581 ,X_QUERY_STRING => X_ATTRIBUTE_VALUE
582 ,X_USER_EDITABLE => 'Y'
583 ,X_IS_STATIC => 'Y'
584 ,X_USE_COUNT => 0
585 ,X_VALUE_SET_NAME => null
586 ,X_OWNER => X_OWNER
587 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
588 ,X_UPLOAD_MODE => X_UPLOAD_MODE
589 ,X_CUSTOM_MODE => X_CUSTOM_MODE
590 );
591 return;
592 end if;
593
594 if AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is null then
595 if X_VARIABLE_NAME in ('allowAllApproverTypes'
596 ,'allowFyiNotifications'
597 ,'allowAllItemClassRules'
598 ,'productionFunctionality') then
599 return;
600 elsif X_VARIABLE_NAME = 'repeatedApprovers' and
601 X_VARIABLE_VALUE not in ('ONCE_PER_TRANSACTION'
602 ,'ONCE_PER_SUBLIST'
603 ,'ONCE_PER_GROUP_OR_CHAIN') then
604 return;
605 end if;
606 elsif AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is not null and to_number(AME_SEED_UTILITY.AME_INSTALLATION_LEVEL) >= 2 then
607 if X_VARIABLE_NAME in ('helpPath'
608 ,'htmlPath'
609 ,'imagePath'
610 ,'portalUrl') then
611 return;
612 end if;
613 end if;
614
615 if MERGE_ROW_TEST
616 (X_VARIABLE_NAME => X_VARIABLE_NAME
617 ,X_APPLICATION_SHORT_NAME => X_APPLICATION_SHORT_NAME
618 ,X_TRANSACTION_TYPE_ID => X_TRANSACTION_TYPE_ID
619 ,X_OWNER => X_OWNER
620 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
621 ,X_UPLOAD_MODE => X_UPLOAD_MODE
622 ,X_CUSTOM_MODE => X_CUSTOM_MODE
623 ) then
624 if X_UPLOAD_MODE = 'NLS' then
625 TRANSLATE_ROW
626 (X_VARIABLE_NAME => X_VARIABLE_NAME
627 ,X_USER_CONFIG_VAR_NAME => X_USER_CONFIG_VAR_NAME
628 ,X_DESCRIPTION => X_DESCRIPTION
629 ,X_OWNER => X_OWNER
630 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
631 );
632 else
633 LOAD_ROW
634 (X_VARIABLE_NAME => X_VARIABLE_NAME
635 ,X_APPLICATION_SHORT_NAME => X_APPLICATION_SHORT_NAME
636 ,X_TRANSACTION_TYPE_ID => X_TRANSACTION_TYPE_ID
637 ,X_USER_CONFIG_VAR_NAME => X_USER_CONFIG_VAR_NAME
638 ,X_VARIABLE_VALUE => X_VARIABLE_VALUE
639 ,X_DESCRIPTION => X_DESCRIPTION
640 ,X_OWNER => X_OWNER
641 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
642 ,X_CUSTOM_MODE => X_CUSTOM_MODE
643 );
644 end if;
645 end if;
646 end LOAD_SEED_ROW;
647
648 procedure DELETE_ROW
649 (X_VARIABLE_NAME in varchar2
650 ,X_APPLICATION_ID in number
651 ) as
652 begin
653 delete from AME_CONFIG_VARS
654 where VARIABLE_NAME = X_VARIABLE_NAME
655 and nvl(APPLICATION_ID,0) = nvl(X_APPLICATION_ID,0);
656 if sql%notfound then
657 raise no_data_found;
658 end if;
659 if AME_SEED_UTILITY.MLS_ENABLED then
660 delete from AME_CONFIG_VARS_TL
661 where VARIABLE_NAME = X_VARIABLE_NAME;
662 end if;
663 end DELETE_ROW;
664
665 end AME_CONFIG_VARS_API;