1 PACKAGE BODY JTA_NOTES_IMT_PUB AS
2 /* $Header: jtfpntib.pls 115.5 2004/09/14 22:59:38 akaran ship $ */
3
4 FUNCTION get_index_owner
5 --------------------------------------------------------------------------
6 -- Get the schema name for CRM Foundation schema
7 --------------------------------------------------------------------------
8 RETURN VARCHAR2
9 AS
10 l_owner varchar2(30);
11
12 BEGIN
13
14 SELECT oracle_username INTO l_owner
15 FROM fnd_oracle_userid
16 WHERE oracle_id = 690;
17
18 RETURN l_owner;
19
20 END get_index_owner;
21
22 PROCEDURE check_notes_index
23 (
24 p_index_owner VARCHAR2
25 )
26 AS
27 CURSOR check_index
28 (
29 p_owner VARCHAR2
30 )
31 IS
32 /**
33 SELECT 1
34 FROM ALL_OBJECTS
35 WHERE OBJECT_NAME = 'JTF_NOTES_TL_C1'
36 AND OBJECT_TYPE = 'INDEX'
37 AND OWNER = p_owner;
38 **/
39 SELECT IDX_LANGUAGE_COLUMN
40 FROM CTXSYS.ctx_indexes
41 WHERE idx_name = 'JTF_NOTES_TL_C1';
42
43 l_language VARCHAR2(256);
44
45 BEGIN
46
47 --
48 -- First check if the index alreay exists. If it does then return from here
49 --
50 OPEN check_index(p_index_owner);
51 FETCH check_index INTO l_language;
52 IF (check_index%FOUND)
53 THEN
54 IF (l_language = 'SOURCE_LANG')
55 THEN
56 CLOSE check_index;
57 -- Index is valid and correct, so quit
58 RETURN;
59 ELSE
60 -- The index was created with wrong language parameters, so drop it
61 EXECUTE IMMEDIATE 'DROP INDEX '||p_index_owner||'.JTF_NOTES_TL_C1';
62 END IF;
63 END IF;
64 CLOSE check_index;
65
66 BEGIN
67 --
68 -- In any case we will want to update the constraints and (re-)create the index
69 --
70 EXECUTE IMMEDIATE 'ALTER TABLE '||p_index_owner||'.JTF_NOTES_TL DROP PRIMARY KEY CASCADE';
71 EXCEPTION
72 WHEN OTHERS
73 THEN
74 --
75 -- In case the primary key doesn't exist the drop will fail, which we will ignore
76 --
77 NULL;
78 -- End Add
79 END;
80
81 EXECUTE IMMEDIATE 'ALTER TABLE '||p_index_owner||'.JTF_NOTES_TL ADD CONSTRAINT jtf_notes_tl_pk PRIMARY KEY (JTF_NOTE_ID,LANGUAGE)';
82 EXECUTE IMMEDIATE 'CREATE INDEX '||p_index_owner||'.JTF_NOTES_TL_C1 ON '||p_index_owner||'.JTF_NOTES_TL(notes) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (''LANGUAGE COLUMN SOURCE_LANG'')';
83
84 END check_notes_index;
85
86 PROCEDURE optimize_notes_index
87 --------------------------------------------------------------------------
88 -- Start of comments
89 -- API name : optimize_notes_index
90 -- Type : Public
91 -- Function : optimize the JTF_NOTES_TL.JTF_NOTES_TL_C1 index
92 -- Pre-reqs : None.
93 -- Parameters :
94 -- name direction type required?
95 -- ---- --------- ---- ---------
96 -- errbuf out varchar2 Yes
97 -- retcode out number Yes
98 -- p_optimize_level in varchar2 No
99 -- p_runtime in number No
100 --
101 -- Version : Current version 1.0
102 -- Previous version 1.0
103 -- Initial version 1.0
104 --
105 -- End of comments
106 --------------------------------------------------------------------------
107 ( errbuf OUT NOCOPY VARCHAR2
108 , retcode OUT NOCOPY NUMBER
109 , p_optimize_level IN VARCHAR2 := ctx_ddl.optlevel_full
110 , p_runtime IN NUMBER := ctx_ddl.maxtime_unlimited
111 )
112 AS
113 l_maxtime VARCHAR2(30);
114 l_stmt VARCHAR2(256);
115 l_owner VARCHAR2(30);
116
117 BEGIN
118
119 IF UPPER(p_optimize_level) NOT IN ('FAST','FULL')
120 THEN
121 fnd_message.set_name('JTF', 'JTF_NOTES_BAD_PARAMETER_VALUE');
122 fnd_message.set_token('VALUE', NVL(p_optimize_level,'NULL'));
123 fnd_message.set_token('PARAMETER', 'p_optimize_level');
124 RAISE_APPLICATION_ERROR(-20000,fnd_message.get);
125 END IF;
126
127 IF (p_runtime IS NOT NULL)
128 THEN
129 IF ( (trunc(p_runtime) <> p_runtime)
130 or (p_runtime < 0)
131 or (p_runtime > ctx_ddl.maxtime_unlimited)
132 )
133 THEN
134 fnd_message.set_name('JTF', 'JTF_NOTES_BAD_PARAMETER_VALUE');
135 fnd_message.set_token('VALUE', NVL(p_runtime,'NULL'));
136 fnd_message.set_token('PARAMETER', 'p_runtime');
137 RAISE_APPLICATION_ERROR(-20000,fnd_message.get);
138 END IF;
139 END IF;
140
141 --
142 -- Get the index owner
143 --
144 l_owner := get_index_owner;
145
146 --
147 -- Call to check if the index exists
148 --
149 check_notes_index(l_owner);
150
151 -----------------
152 -- set maxtime --
153 -----------------
154 IF (UPPER(p_optimize_level) = 'FAST')
155 THEN
156 l_maxtime := NULL;
157 ELSE
158 IF ( (p_runtime IS NULL)
159 OR (p_runtime = ctx_ddl.maxtime_unlimited)
160 )
161 THEN
162 l_maxtime := ' maxtime unlimited';
163 ELSE
164 l_maxtime := ' maxtime '||TRUNC(p_runtime);
165 END IF;
166 END IF;
167
168 -----------------------
169 -- rebuild the index --
170 -----------------------
171 l_stmt := 'alter index '||l_owner||'.JTF_NOTES_TL_C1 ' ||
172 'rebuild online '||
173 'parameters(''optimize '|| p_optimize_level || l_maxtime ||''')';
174
175 EXECUTE IMMEDIATE l_stmt;
176
177 EXCEPTION
178 WHEN OTHERS
179 THEN
180 errbuf := SUBSTR(SQLERRM, 1, 80);
181 retcode := 2;
182
183 END optimize_notes_index;
184
185
186 PROCEDURE sync_notes_index
187 --------------------------------------------------------------------------
188 -- Start of comments
189 -- API name : sync_notes_index
190 -- Type : Public
191 -- Function : synchronize the JTF_NOTES_TL.JTF_NOTES_TL_C1 index
192 -- Pre-reqs : None.
193 -- Parameters :
194 -- name direction type required?
195 -- ---- --------- ---- ---------
196 -- errbuf out varchar2 Yes
197 -- retcode out number Yes
198 --
199 -- Version : Current version 1.0
200 -- Previous version 1.0
201 -- Initial version 1.0
202 --
203 -- End of comments
204 --------------------------------------------------------------------------
205 ( errbuf OUT NOCOPY VARCHAR2
206 , retcode OUT NOCOPY NUMBER
207 )
208 AS
209 l_stmt VARCHAR2(256);
210 l_owner VARCHAR2(30);
211
212 BEGIN
213 --
214 -- Get the index owner
215 --
216 l_owner := get_index_owner;
217
218 --
219 -- Call to check if the index exists
220 --
221 check_notes_index(l_owner);
222
223 -----------------------
224 -- rebuild the index --
225 -----------------------
226 l_stmt := 'alter index '||l_owner||'.JTF_NOTES_TL_C1 ' ||
227 'rebuild online '||
228 'parameters(''sync'')';
229
230 EXECUTE IMMEDIATE l_stmt;
231
232 EXCEPTION
233 WHEN OTHERS
234 THEN
235 errbuf := SUBSTR(SQLERRM, 1, 80);
236 retcode := 2;
237
238 END SYNC_NOTES_INDEX;
239
240 END JTA_NOTES_IMT_PUB;
[Home] [Help]