1 PACKAGE BODY HZ_WORD_ARRAY_PKG AS
2 /*$Header: ARHDQWAB.pls 120.1 2005/08/29 13:19:48 rchanamo noship $ */
3
4 /*************** Globals *****************************/
5 -- VJN created for implementing Associative arrays in DQM Word Replacements
6
7 PROCEDURE populate_word_arrays(wl_id NUMBER)
8 IS
9 flag varchar2(1) ;
10 BEGIN
11
12 -- populate word list only if it doesn't exist
13 IF word_list_exists(wl_id)
14 THEN
15 NULL;
16
17 -- if it doesn't exist do the following
18 -- populate non_delimited flag in flag lookup array
19 -- populate global replacements in global array
20 -- populate conditional replacements by storing -1 as replacement for original word
21 ELSE
22 select nvl(non_delimited_flag,'N') into flag
23 from hz_word_lists
24 where word_list_id = wl_id ;
25
26 -- Populate flag first
27 word_list_ndl_flag_lookup(wl_id) := flag ;
28
29 -- Populate word list array with global replacements first
30 FOR indx IN (
31 SELECT original_word, replacement_word
32 FROM HZ_WORD_REPLACEMENTS
33 WHERE word_list_id = wl_id and nvl(condition_id, 0) = 0
34 AND ((HZ_TRANS_PKG.staging_context = 'Y' AND DELETE_FLAG = 'N')
35 OR (nvl(HZ_TRANS_PKG.staging_context,'N') = 'N' AND STAGED_FLAG = 'Y')
36 )
37 )
38 LOOP
39 word_list_global_rep_lookup(wl_id)(indx.original_word) := indx.replacement_word ;
40
41 END LOOP;
42
43 -- Populate word list array with conditional replacements second
44 -- Here we just mark the original word as dirty
45
46 FOR indx IN (
47 SELECT distinct original_word
48 FROM HZ_WORD_REPLACEMENTS
49 WHERE word_list_id = wl_id and condition_id > 0
50 AND ((HZ_TRANS_PKG.staging_context = 'Y' AND DELETE_FLAG = 'N')
51 OR (nvl(HZ_TRANS_PKG.staging_context,'N') = 'N' AND STAGED_FLAG = 'Y')
52 )
53 order by original_word )
54 LOOP
55 word_list_global_rep_lookup(wl_id)(indx.original_word) := '-1' ;
56 END LOOP ;
57
58 END IF ;
59 END ;
60
61 FUNCTION get_global_repl_word(wl_id NUMBER, original_word VARCHAR2)
62 RETURN VARCHAR2
63 IS
64 BEGIN
65 -- Look in global replacement array only
66 IF word_list_global_rep_lookup(wl_id)(original_word) IS NULL or
67 word_list_global_rep_lookup(wl_id)(original_word) like '%'
68 THEN
69 RETURN word_list_global_rep_lookup(wl_id)(original_word) ;
70 END IF ;
71
72 -- if there is an exception
73 EXCEPTION WHEN NO_DATA_FOUND
74 THEN
75 RETURN '-2' ;
76 END ;
77
78 FUNCTION word_list_exists(wl_id NUMBER)
79 RETURN BOOLEAN
80 IS
81 BEGIN
82 -- Look into non delimited flag array only
83 IF word_list_ndl_flag_lookup(wl_id) in ('Y', 'N')
84 THEN
85 RETURN TRUE ;
86 END IF ;
87
88 EXCEPTION WHEN NO_DATA_FOUND
89 THEN
90 RETURN FALSE ;
91 END ;
92
93 END;
[Home] [Help]