1 PACKAGE BODY FND_OAM_DS_ALGOS_PKG as
2 /* $Header: AFOAMDSALGOB.pls 120.3 2006/01/17 11:43 ilawler noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(20) := 'DS_ALGOS_PKG.';
8
9 -- When algorithms are resolved, we cache the details of that resolution locally
10 -- because it's common to re-use algorithms many times in one configuration import.
11 -- Only cache the raw algo text to prevent the query, don't cache it with substitutions
12 -- because the substitutions shouldn't repeat.
13 TYPE b_algo_cache_entry_type IS RECORD
14 (
15 is_valid BOOLEAN := FALSE,
16 used_algo_id NUMBER := NULL,
17 datatype VARCHAR2(30) := NULL,
18 raw_algo_text VARCHAR2(4000) := NULL,
19 weight_modifier NUMBER := NULL
20 );
21
22 TYPE b_algo_cache_type IS TABLE OF b_algo_cache_entry_type INDEX BY BINARY_INTEGER;
23 b_algo_cache b_algo_cache_type;
24
25 --cache for queried default algorithms, datatype->algo_id
26 TYPE b_default_algo_cache_type IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
27 b_default_algo_cache b_default_algo_cache_type;
28
29 --#########################################
30 -- Substitition Token-related constants --
31 --#########################################
32
33 -- This is the token identifying delimiter, present before and after a token
34 B_TOK_DELIM CONSTANT VARCHAR2(3) := '%';
35
36 -- These are the substitution tokens we accept
37 B_TOKEN_TABLE_OWNER CONSTANT VARCHAR2(60) := B_TOK_DELIM||'table_owner'||B_TOK_DELIM;
38 B_TOKEN_TABLE_NAME CONSTANT VARCHAR2(60) := B_TOK_DELIM||'table_name'||B_TOK_DELIM;
39 B_TOKEN_COLUMN_NAME CONSTANT VARCHAR2(60) := B_TOK_DELIM||'column_name'||B_TOK_DELIM;
40
41 -- This is the substitution token state
42 --b_token_table_initialized BOOLEAN := FALSE;
43 --TYPE b_token_table_type IS TABLE OF VARCHAR2(60);
44 --b_token_table b_token_table_type;
45
46 ----------------------------------------
47 -- Public/Private Procedures/Functions
48 ----------------------------------------
49
50 -- Public
51 FUNCTION GET_ALGO_ID(p_display_name IN VARCHAR2)
52 RETURN NUMBER
53 IS
54 l_algo_id NUMBER;
55 BEGIN
56 --don't allow the name NULL
57 IF p_display_name IS NULL THEN
58 RAISE NO_DATA_FOUND;
59 END IF;
60
61 SELECT algo_id
62 INTO l_algo_id
63 FROM fnd_oam_ds_algos_tl
64 WHERE display_name = p_display_name
65 AND language = USERENV('LANG');
66
67 RETURN l_algo_id;
68 END;
69
70 -- Private
71 -- Given some raw algorithm text, perform substitutions on all known substitution tokens
72 PROCEDURE REPLACE_SUBSTITUTION_TOKENS(p_raw_algo_text IN VARCHAR2,
73 p_table_owner IN VARCHAR2,
74 p_table_name IN VARCHAR2,
75 p_column_name IN VARCHAR2,
76 x_new_algo_text OUT NOCOPY VARCHAR2)
77 IS
78 l_text VARCHAR2(4000) := p_raw_algo_text;
79 BEGIN
80 -- issue each of the replace statements, this needs to be changed when new tokens are added
81 -- if the new text is beyond the l_text max length, let the exception bubble up
82 l_text := REPLACE(l_text, B_TOKEN_TABLE_OWNER, p_table_owner);
83 l_text := REPLACE(l_text, B_TOKEN_TABLE_NAME, p_table_name);
84 l_text := REPLACE(l_text, B_TOKEN_COLUMN_NAME, p_column_name);
85
86 x_new_algo_text := l_text;
87 END;
88
89 -- Private
90 -- Creates and caches a new algo_cache_entry_type when we found a bad algo definition
91 PROCEDURE ADD_BAD_ALGO_CACHE_ENTRY(p_algo_id IN NUMBER)
92 IS
93 l_entry b_algo_cache_entry_type;
94 BEGIN
95 l_entry.is_valid := FALSE;
96
97 b_algo_cache(p_algo_id) := l_entry;
98 END;
99
100 -- Private
101 -- Creates and caches a new, valid algo_cache_entry_type
102 PROCEDURE ADD_ALGO_CACHE_ENTRY(p_algo_id IN NUMBER,
103 p_used_algo_id IN NUMBER,
104 p_datatype IN VARCHAR2,
105 p_raw_algo_text IN VARCHAR2,
106 p_weight_modifier IN NUMBER)
107 IS
108 l_entry b_algo_cache_entry_type;
109 BEGIN
110 l_entry.used_algo_id := p_used_algo_id;
111 l_entry.datatype := p_datatype;
112 l_entry.raw_algo_text := p_raw_algo_text;
113 l_entry.weight_modifier := p_weight_modifier;
114 l_entry.is_valid := TRUE;
115
116 b_algo_cache(p_algo_id) := l_entry;
117 END;
118
119 -- Public
120 PROCEDURE RESOLVE_ALGO_ID(p_algo_id IN NUMBER,
121 p_table_owner IN VARCHAR2 DEFAULT NULL,
122 p_table_name IN VARCHAR2 DEFAULT NULL,
123 p_column_name IN VARCHAR2 DEFAULT NULL,
124 x_new_column_value OUT NOCOPY VARCHAR2,
125 x_weight_modifier OUT NOCOPY NUMBER)
126 IS
127 l_ctxt VARCHAR2(60) := PKG_NAME||'RESOLVE_ALGO_ID';
128
129 l_current_algo_id NUMBER;
130 l_use_algo_id NUMBER;
131 l_datatype VARCHAR2(30);
132 l_raw_algo_text VARCHAR2(4000);
133 l_weight_modifier NUMBER;
134
135 BEGIN
136 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
137
138 --see if the algo misses the cache, if so find it's base definition and add it
139 IF NOT b_algo_cache.EXISTS(p_algo_id) THEN
140 fnd_oam_debug.log(1, l_ctxt, 'Uncached... finding root algo_id.');
141 --loop the fetch to resolve the chain of use_algo_id references
142 l_current_algo_id := p_algo_id;
143 WHILE TRUE LOOP
144 fnd_oam_debug.log(1, l_ctxt, 'Querying details for algo_id: '||l_current_algo_id);
145 --fetch attributes corresponding to the current algo id
146 BEGIN
147 SELECT use_algo_id, datatype, algo_text, weight_modifier
148 INTO l_use_algo_id, l_datatype, l_raw_algo_text, l_weight_modifier
149 FROM fnd_oam_ds_algos_b
150 WHERE algo_id = l_current_algo_id
151 AND SYSDATE BETWEEN NVL(START_DATE, SYSDATE) AND NVL(END_DATE, SYSDATE);
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 --if the lookup fails, store that failure in the cache
155 fnd_oam_debug.log(3, l_ctxt, 'Failed to query algo_id from ds_algos_b - no data found.');
156 ADD_BAD_ALGO_CACHE_ENTRY(p_algo_id => p_algo_id);
157 RAISE;
158 END;
159
160 --if there's no queried use_algo_id, we have our definition
161 IF l_use_algo_id IS NULL THEN
162 fnd_oam_debug.log(1, l_ctxt, 'Caching base algo_id: '||l_current_algo_id);
163 ADD_ALGO_CACHE_ENTRY(p_algo_id => p_algo_id,
164 p_used_algo_id => l_current_algo_id,
165 p_datatype => l_datatype,
166 p_raw_algo_text => l_raw_algo_text,
167 p_weight_modifier => l_weight_modifier);
168 EXIT;
169 ELSE
170 l_current_algo_id := l_use_algo_id;
171 END IF;
172 END LOOP;
173 END IF;
174
175 --at this point we should be guaranteed that the algo is in the cache, see if its valid
176 IF NOT b_algo_cache.EXISTS(p_algo_id) OR NOT b_algo_cache(p_algo_id).is_valid THEN
177 RAISE NO_DATA_FOUND;
178 END IF;
179
180 --we have a valid algo cache entry, make the new column value using any necessary substitutions
181 REPLACE_SUBSTITUTION_TOKENS(b_algo_cache(p_algo_id).raw_algo_text,
182 p_table_owner,
183 p_table_name,
184 p_column_name,
185 x_new_column_value);
186 x_weight_modifier := b_algo_cache(p_algo_id).weight_modifier;
187
188 -- Success
189 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
193 RAISE;
194 WHEN VALUE_ERROR THEN
195 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
196 RAISE;
197 WHEN OTHERS THEN
198 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
199 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
200 RAISE;
201 END;
202
203 -- Public
204 PROCEDURE GET_DEFAULT_ALGO_FOR_DATATYPE(p_datatype IN VARCHAR2,
205 x_algo_id OUT NOCOPY NUMBER)
206 IS
207 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_DEFAULT_ALGO_FOR_DATATYPE';
208
209 l_id NUMBER;
210 BEGIN
211 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
212
213 --see if we've already queried this datatype
214 IF b_default_algo_cache.EXISTS(p_datatype) THEN
215 --if we cached the algo_id NULL, we cached failure
216 l_id := b_default_algo_cache(p_datatype);
217 IF l_id IS NULL THEN
218 RAISE NO_DATA_FOUND;
219 END IF;
220 ELSE
221 fnd_oam_debug.log(1, l_ctxt, 'Querying default for datatype "'||p_datatype||'"...');
222
223 --do the query, automatically throws NO_DATA_FOUND/TOO_MANY_ROWS if less than or greater than one row
224 BEGIN
225 SELECT algo_id
226 INTO l_id
227 FROM fnd_oam_ds_algos_b
228 WHERE datatype = p_datatype
229 AND default_for_datatype_flag = FND_API.G_TRUE;
230
231 --cache the result
232 fnd_oam_debug.log(1, l_ctxt, 'Found id: '||l_id);
233 b_default_algo_cache(p_datatype) := l_id;
234 EXCEPTION
235 WHEN OTHERS THEN
236 --cache failure
237 fnd_oam_debug.log(3, l_ctxt, 'Failed to find a default: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
238 b_default_algo_cache(p_datatype) := NULL;
239 RAISE;
240 END;
241 END IF;
242
243 x_algo_id := l_id;
244 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
245 EXCEPTION
246 WHEN NO_DATA_FOUND THEN
247 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
248 RAISE;
249 WHEN TOO_MANY_ROWS THEN
250 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
251 RAISE;
252 WHEN OTHERS THEN
253 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
254 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
255 RAISE;
256 END;
257
258
259 --PROCEDURES REQUIRED BY FNDLOADER
260
261 procedure LOAD_ROW (
262 X_ALGO_ID in NUMBER,
263 X_START_DATE IN DATE,
264 X_END_DATE IN DATE,
265 X_USE_ALGO_ID IN NUMBER,
266 X_DEFAULT_FOR_DATATYPE_FLAG IN VARCHAR2,
267 X_DATATYPE IN VARCHAR2,
268 X_ALGO_TEXT IN VARCHAR2,
269 X_WEIGHT_MODIFIER IN NUMBER,
270 X_OWNER in VARCHAR2,
271 X_DISPLAY_NAME IN VARCHAR2,
272 X_DESCRIPTION IN VARCHAR2) IS
273 begin
274
275 FND_OAM_DS_ALGOS_PKG.LOAD_ROW (
276 X_ALGO_ID => X_ALGO_ID,
277 X_START_DATE => X_START_DATE,
278 X_END_DATE => X_END_DATE,
279 X_USE_ALGO_ID => X_USE_ALGO_ID,
280 X_DEFAULT_FOR_DATATYPE_FLAG => X_DEFAULT_FOR_DATATYPE_FLAG,
281 X_DATATYPE => X_DATATYPE,
282 X_ALGO_TEXT => X_ALGO_TEXT,
283 X_WEIGHT_MODIFIER => X_WEIGHT_MODIFIER,
284 X_OWNER => X_OWNER,
285 X_DISPLAY_NAME => X_DISPLAY_NAME,
286 X_DESCRIPTION => X_DESCRIPTION,
287 x_custom_mode => '',
288 X_LAST_UPDATE_DATE => '');
289
290 end LOAD_ROW;
291
292 procedure LOAD_ROW (
293 X_ALGO_ID in NUMBER,
294 X_START_DATE IN DATE,
295 X_END_DATE IN DATE,
296 X_USE_ALGO_ID IN NUMBER,
297 X_DEFAULT_FOR_DATATYPE_FLAG IN VARCHAR2,
298 X_DATATYPE IN VARCHAR2,
299 X_ALGO_TEXT IN VARCHAR2,
300 X_WEIGHT_MODIFIER IN NUMBER,
301 X_OWNER in VARCHAR2,
302 X_DISPLAY_NAME IN VARCHAR2,
303 X_DESCRIPTION IN VARCHAR2,
304 x_custom_mode in varchar2,
305 X_LAST_UPDATE_DATE in varchar2)
306 is
307 malgo_id number;
308 row_id varchar2(64);
309 f_luby number; -- entity owner in file
310 f_ludate date; -- entity update date in file
311 db_luby number; -- entity owner in db
312 db_ludate date; -- entity update date in db
313 begin
314
315 -- Translate owner to file_last_updated_by
316 f_luby := fnd_load_util.owner_id(x_owner);
317
318 -- Translate char last_update_date to date
319 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
320
321 begin
322 -- check if this algorithm id already exists.
323 select algo_id, LAST_UPDATED_BY, LAST_UPDATE_DATE
324 into malgo_id, db_luby, db_ludate
325 from fnd_oam_ds_algos_b
326 where algo_id = to_number(X_ALGO_ID);
327
328 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
329 db_ludate, X_CUSTOM_MODE)) then
330 FND_OAM_DS_ALGOS_PKG.UPDATE_ROW (
331 X_ALGO_ID => malgo_id,
332 X_START_DATE => X_START_DATE,
333 X_END_DATE => X_END_DATE,
334 X_USE_ALGO_ID => X_USE_ALGO_ID,
335 X_DEFAULT_FOR_DATATYPE_FLAG => X_DEFAULT_FOR_DATATYPE_FLAG,
336 X_DATATYPE => X_DATATYPE,
337 X_ALGO_TEXT => X_ALGO_TEXT,
338 X_WEIGHT_MODIFIER => X_WEIGHT_MODIFIER,
339 X_DISPLAY_NAME => X_DISPLAY_NAME,
340 X_DESCRIPTION => X_DESCRIPTION,
341 X_LAST_UPDATED_BY => f_luby,
342 X_LAST_UPDATE_DATE => f_ludate,
343 X_LAST_UPDATE_LOGIN => 0 );
344
345 end if;
346 exception
347 when NO_DATA_FOUND then
348
349 FND_OAM_DS_ALGOS_PKG.INSERT_ROW (
350 X_ROWID => row_id,
351 X_ALGO_ID => X_ALGO_ID,
352 X_START_DATE => X_START_DATE,
353 X_END_DATE => X_END_DATE,
354 X_USE_ALGO_ID => X_USE_ALGO_ID,
355 X_DEFAULT_FOR_DATATYPE_FLAG => X_DEFAULT_FOR_DATATYPE_FLAG,
356 X_DATATYPE => X_DATATYPE,
357 X_ALGO_TEXT => X_ALGO_TEXT,
358 X_WEIGHT_MODIFIER => X_WEIGHT_MODIFIER,
359 X_DISPLAY_NAME => X_DISPLAY_NAME,
360 X_DESCRIPTION => X_DESCRIPTION,
361 X_CREATION_DATE => f_ludate,
362 X_CREATED_BY => f_luby,
363 X_LAST_UPDATE_DATE => f_ludate,
364 X_LAST_UPDATED_BY => f_luby,
365 X_LAST_UPDATE_LOGIN => 0 );
366 end;
367
368 end LOAD_ROW;
369
370 --TRANSLATE ROW
371
372 procedure TRANSLATE_ROW (
373 X_ALGO_ID in NUMBER,
374 X_DISPLAY_NAME IN VARCHAR2,
375 X_DESCRIPTION IN VARCHAR2,
376 X_OWNER in VARCHAR2)
377 is
378 begin
379
380 FND_OAM_DS_ALGOS_PKG.translate_row(
381 X_ALGO_ID => X_ALGO_ID,
382 X_DISPLAY_NAME => X_DISPLAY_NAME,
383 X_DESCRIPTION => X_DESCRIPTION,
384 X_OWNER => X_OWNER,
385 X_CUSTOM_MODE => '',
386 X_LAST_UPDATE_DATE => '');
387
388 end TRANSLATE_ROW;
389
390 procedure TRANSLATE_ROW (
391 X_ALGO_ID in NUMBER,
392 X_DISPLAY_NAME IN VARCHAR2,
393 X_DESCRIPTION IN VARCHAR2,
394 X_OWNER in VARCHAR2,
395 X_CUSTOM_MODE in VARCHAR2,
396 X_LAST_UPDATE_DATE in VARCHAR2)
397 IS
398
399 f_luby number; -- entity owner in file
400 f_ludate date; -- entity update date in file
401 db_luby number; -- entity owner in db
402 db_ludate date; -- entity update date in db
403
404 begin
405
406 -- Translate owner to file_last_updated_by
407 f_luby := fnd_load_util.owner_id(x_owner);
408
409 -- Translate char last_update_date to date
410 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
411
412 begin
413 select LAST_UPDATED_BY, LAST_UPDATE_DATE
414 into db_luby, db_ludate
415 from fnd_oam_ds_algos_tl
416 where algo_id = to_number(X_ALGO_ID)
417 and LANGUAGE = userenv('LANG');
418
419 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
420 db_ludate, X_CUSTOM_MODE)) then
421 update fnd_oam_ds_algos_tl set
422 display_name = nvl(X_DISPLAY_NAME, display_name),
423 description = nvl(X_DESCRIPTION, description),
424 source_lang = userenv('LANG'),
425 last_update_date = f_ludate,
426 last_updated_by = f_luby,
427 last_update_login = 0
428 where algo_id = to_number(X_ALGO_ID)
429 and userenv('LANG') in (language, source_lang);
430 end if;
431 exception
432 when no_data_found then
433 null;
434 end;
435
436 end TRANSLATE_ROW;
437
438
439 --INSERT ROW
440 procedure INSERT_ROW (
441 X_ROWID in out nocopy VARCHAR2,
442 X_ALGO_ID in NUMBER,
443 X_DISPLAY_NAME IN VARCHAR2,
444 X_DESCRIPTION IN VARCHAR2,
445 X_START_DATE IN DATE,
446 X_END_DATE IN DATE,
447 X_USE_ALGO_ID IN NUMBER,
448 X_DEFAULT_FOR_DATATYPE_FLAG IN VARCHAR2,
449 X_DATATYPE IN VARCHAR2,
450 X_ALGO_TEXT IN VARCHAR2,
451 X_WEIGHT_MODIFIER IN NUMBER,
452 X_CREATED_BY in NUMBER,
453 X_CREATION_DATE in DATE,
454 X_LAST_UPDATED_BY in NUMBER,
455 X_LAST_UPDATE_DATE in DATE,
456 X_LAST_UPDATE_LOGIN in NUMBER)
457 is
458 cursor C is select ROWID from FND_OAM_DS_ALGOS_B
459 where ALGO_ID = X_ALGO_ID;
460 begin
461 insert into FND_OAM_DS_ALGOS_B (
462 ALGO_ID,
463 START_DATE,
464 END_DATE,
465 USE_ALGO_ID,
466 DEFAULT_FOR_DATATYPE_FLAG,
467 DATATYPE,
468 ALGO_TEXT,
469 WEIGHT_MODIFIER,
470 CREATED_BY,
471 CREATION_DATE,
472 LAST_UPDATED_BY,
473 LAST_UPDATE_DATE,
474 LAST_UPDATE_LOGIN
475 ) values (
476 X_ALGO_ID,
477 X_START_DATE,
478 X_END_DATE,
479 X_USE_ALGO_ID,
480 X_DEFAULT_FOR_DATATYPE_FLAG,
481 X_DATATYPE,
482 X_ALGO_TEXT,
483 X_WEIGHT_MODIFIER,
484 X_CREATED_BY,
485 X_CREATION_DATE,
486 X_LAST_UPDATED_BY,
487 X_LAST_UPDATE_DATE,
488 X_LAST_UPDATE_LOGIN
489 );
490
491 insert into FND_OAM_DS_ALGOS_TL (
492 ALGO_ID,
493 DISPLAY_NAME,
494 DESCRIPTION,
495 CREATED_BY,
496 CREATION_DATE,
497 LAST_UPDATED_BY,
498 LAST_UPDATE_DATE,
499 LAST_UPDATE_LOGIN,
500 LANGUAGE,
501 SOURCE_LANG
502 ) select
503 X_ALGO_ID,
504 X_DISPLAY_NAME,
505 X_DESCRIPTION,
506 X_CREATED_BY,
507 X_CREATION_DATE,
508 X_LAST_UPDATED_BY,
509 X_LAST_UPDATE_DATE,
510 X_LAST_UPDATE_LOGIN,
511 L.LANGUAGE_CODE,
512 userenv('LANG')
513 from FND_LANGUAGES L
514 where L.INSTALLED_FLAG in ('I', 'B')
515 and not exists
516 (select NULL
517 from FND_OAM_DS_ALGOS_TL T
518 where T.ALGO_ID = X_ALGO_ID
519 and T.LANGUAGE = L.LANGUAGE_CODE);
520
521 open c;
522 fetch c into X_ROWID;
523 if (c%notfound) then
524 close c;
525 raise no_data_found;
526 end if;
527 close c;
528
529 end INSERT_ROW;
530
531 --LOCK ROW
532
533 procedure LOCK_ROW (
534 X_ROWID in out nocopy VARCHAR2,
535 X_ALGO_ID in NUMBER,
536 X_DISPLAY_NAME IN VARCHAR2,
537 X_DESCRIPTION IN VARCHAR2,
538 X_START_DATE IN DATE,
539 X_END_DATE IN DATE,
540 X_USE_ALGO_ID IN NUMBER,
541 X_DEFAULT_FOR_DATATYPE_FLAG IN VARCHAR2,
542 X_DATATYPE IN VARCHAR2,
543 X_ALGO_TEXT IN VARCHAR2,
544 X_WEIGHT_MODIFIER IN NUMBER,
545 X_CREATED_BY in NUMBER,
546 X_CREATION_DATE in DATE,
547 X_LAST_UPDATED_BY in NUMBER,
548 X_LAST_UPDATE_DATE in DATE,
549 X_LAST_UPDATE_LOGIN in NUMBER
550 ) is
551 cursor c is select
552 ALGO_ID,
553 START_DATE,
554 END_DATE,
555 USE_ALGO_ID,
556 DEFAULT_FOR_DATATYPE_FLAG,
557 DATATYPE,
558 ALGO_TEXT,
559 WEIGHT_MODIFIER,
560 CREATED_BY,
561 CREATION_DATE,
562 LAST_UPDATED_BY,
563 LAST_UPDATE_DATE,
564 LAST_UPDATE_LOGIN
565 from FND_OAM_DS_ALGOS_B
566 where ALGO_ID = X_ALGO_ID
567 for update of ALGO_ID nowait;
568 recinfo c%rowtype;
569
570 cursor c1 is select
571 DISPLAY_NAME,
572 DESCRIPTION,
573 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
574 from FND_OAM_DS_ALGOS_TL
575 where ALGO_ID = X_ALGO_ID
576 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
577 for update of ALGO_ID nowait;
578 begin
579 open c;
580 fetch c into recinfo;
581 if (c%notfound) then
582 close c;
583 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
584 app_exception.raise_exception;
585 end if;
586 close c;
587 if ( ((recinfo.START_DATE = X_START_DATE)
588 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
589 AND ((recinfo.END_DATE = X_END_DATE)
590 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
591 AND ((recinfo.USE_ALGO_ID = X_USE_ALGO_ID)
592 OR ((recinfo.USE_ALGO_ID is null) AND (X_USE_ALGO_ID is null)))
593 AND ((recinfo.DEFAULT_FOR_DATATYPE_FLAG = X_DEFAULT_FOR_DATATYPE_FLAG)
594 OR ((recinfo.DEFAULT_FOR_DATATYPE_FLAG is null) AND (X_DEFAULT_FOR_DATATYPE_FLAG is null)))
595 AND ((recinfo.DATATYPE = X_DATATYPE)
596 OR ((recinfo.DATATYPE is null) AND (X_DATATYPE is null)))
597 AND ((recinfo.ALGO_TEXT = X_ALGO_TEXT)
598 OR ((recinfo.ALGO_TEXT is null) AND (X_ALGO_TEXT is null)))
599 AND ((recinfo.WEIGHT_MODIFIER = X_WEIGHT_MODIFIER)
600 OR ((recinfo.WEIGHT_MODIFIER is null) AND (X_WEIGHT_MODIFIER is null)))
601 ) then
602 null;
603 else
604 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
605 app_exception.raise_exception;
606 end if;
607
608 for tlinfo in c1 loop
609 if (tlinfo.BASELANG = 'Y') then
610 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
611 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
612 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
613 ) then
614 null;
615 else
616 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
617 app_exception.raise_exception;
618 end if;
619 end if;
620 end loop;
621 return;
622 end LOCK_ROW;
623
624 --UPDATE ROW
625
626 procedure UPDATE_ROW (
627 X_ALGO_ID in NUMBER,
628 X_DISPLAY_NAME IN VARCHAR2,
629 X_DESCRIPTION IN VARCHAR2,
630 X_START_DATE IN DATE,
631 X_END_DATE IN DATE,
632 X_USE_ALGO_ID IN NUMBER,
633 X_DEFAULT_FOR_DATATYPE_FLAG IN VARCHAR2,
634 X_DATATYPE IN VARCHAR2,
635 X_ALGO_TEXT IN VARCHAR2,
636 X_WEIGHT_MODIFIER IN NUMBER,
637 X_LAST_UPDATED_BY in NUMBER,
638 X_LAST_UPDATE_DATE in DATE,
639 X_LAST_UPDATE_LOGIN in NUMBER
640 ) is
641 begin
642 update FND_OAM_DS_ALGOS_B set
643 START_DATE = X_START_DATE,
644 END_DATE = X_END_DATE,
645 USE_ALGO_ID = X_USE_ALGO_ID,
646 DEFAULT_FOR_DATATYPE_FLAG = X_DEFAULT_FOR_DATATYPE_FLAG,
647 DATATYPE = X_DATATYPE,
648 ALGO_TEXT = X_ALGO_TEXT,
649 WEIGHT_MODIFIER = X_WEIGHT_MODIFIER,
650 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
651 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
652 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
653 where ALGO_ID = X_ALGO_ID;
654
655 if (sql%notfound) then
656 raise no_data_found;
657 end if;
658
659 update FND_OAM_DS_ALGOS_TL set
660 DISPLAY_NAME = X_DISPLAY_NAME,
661 DESCRIPTION = X_DESCRIPTION,
662 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
663 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
664 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
665 SOURCE_LANG = userenv('LANG')
666 where ALGO_ID = X_ALGO_ID
667 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
668
669 if (sql%notfound) then
670 raise no_data_found;
671 end if;
672 end UPDATE_ROW;
673
674
675 --DELETE ROW
676
677 procedure DELETE_ROW (
678 X_ALGO_ID in NUMBER
679 ) is
680 begin
681 delete from FND_OAM_DS_ALGOS_TL
682 where ALGO_ID = X_ALGO_ID;
683
684 if (sql%notfound) then
685 raise no_data_found;
686 end if;
687
688 delete from FND_OAM_DS_ALGOS_B
689 where ALGO_ID = X_ALGO_ID;
690
691 if (sql%notfound) then
692 raise no_data_found;
693 end if;
694 end DELETE_ROW;
695
696
697 --ADD LANGUAGE
698
699 procedure ADD_LANGUAGE
700 is
701 begin
702 delete from FND_OAM_DS_ALGOS_TL T
703 where not exists
704 (select NULL
705 from FND_OAM_DS_ALGOS_B B
706 where B.ALGO_ID = T.ALGO_ID
707 );
708
709 update FND_OAM_DS_ALGOS_TL T set (
710 DISPLAY_NAME,
711 DESCRIPTION
712 ) = (select
713 B.DISPLAY_NAME,
714 B.DESCRIPTION
715 from FND_OAM_DS_ALGOS_TL B
716 where B.ALGO_ID = T.ALGO_ID
717 and B.LANGUAGE = T.SOURCE_LANG)
718 where (
719 T.ALGO_ID,
720 T.LANGUAGE
721 ) in (select
722 SUBT.ALGO_ID,
723 SUBT.LANGUAGE
724 from FND_OAM_DS_ALGOS_TL SUBB, FND_OAM_DS_ALGOS_TL SUBT
725 where SUBB.ALGO_ID = SUBT.ALGO_ID
726 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
727 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
728 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
729 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
730 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
731 ));
732
733 insert into FND_OAM_DS_ALGOS_TL (
734 ALGO_ID,
735 DISPLAY_NAME,
736 DESCRIPTION,
737 CREATED_BY,
738 CREATION_DATE,
739 LAST_UPDATED_BY,
740 LAST_UPDATE_DATE,
741 LAST_UPDATE_LOGIN,
742 LANGUAGE,
743 SOURCE_LANG
744 ) select /*+ ORDERED */
745 B.ALGO_ID,
746 B.DISPLAY_NAME,
747 B.DESCRIPTION,
748 B.CREATED_BY,
749 B.CREATION_DATE,
750 B.LAST_UPDATED_BY,
751 B.LAST_UPDATE_DATE,
752 B.LAST_UPDATE_LOGIN,
753 L.LANGUAGE_CODE,
754 B.SOURCE_LANG
755 from FND_OAM_DS_ALGOS_TL B, FND_LANGUAGES L
756 where L.INSTALLED_FLAG in ('I', 'B')
757 and B.LANGUAGE = userenv('LANG')
758 and not exists
759 (select NULL
760 from FND_OAM_DS_ALGOS_TL T
761 where T.ALGO_ID = B.ALGO_ID
762 and T.LANGUAGE = L.LANGUAGE_CODE);
763 end ADD_LANGUAGE;
764
765
766 --TRANSLATE ROW
767
768 procedure TRANSLATE_ROW
769 (
770 x_ALGO_ID in NUMBER,
771 x_DISPLAY_NAME in varchar2,
772 X_LAST_UPDATED_BY in NUMBER,
773 X_LAST_UPDATE_DATE in DATE,
774 X_LAST_UPDATE_LOGIN in NUMBER)
775 is
776 begin
777
778 UPDATE FND_OAM_DS_ALGOS_TL SET
779 DISPLAY_NAME = nvl(x_DISPLAY_NAME,DISPLAY_NAME),
780 last_update_date = nvl(x_last_update_date,sysdate),
781 last_updated_by = x_last_updated_by,
782 last_update_login = 0,
783 source_lang = userenv('LANG')
784 WHERE ALGO_ID = x_ALGO_ID
785 AND userenv('LANG') in (LANGUAGE,SOURCE_LANG);
786
787 IF (sql%notfound) THEN
788 raise no_data_found;
789 END IF;
790
791 end TRANSLATE_ROW;
792 --END OF PROCEDURES REQUIRED BY FNDLOADER.
793
794 END FND_OAM_DS_ALGOS_PKG;
[Home] [Help]