[Home] [Help]
PACKAGE BODY: APPS.CS_INCIDENT_CTX_PKG
Source
1 PACKAGE BODY CS_INCIDENT_CTX_PKG AS
2 /* $Header: cssrctxb.pls 115.1 99/07/16 09:02:08 porting ship $ */
3
4 PROCEDURE Execute_Query_Contains(str1 IN VARCHAR2,str2 IN VARCHAR2,
5 str3 IN VARCHAR2,str4 IN VARCHAR2,result_table IN VARCHAR2)
6 IS
7 cursor_name INTEGER;
8 rows_processed INTEGER; -- Not used currently
9 BEGIN
10
11 IF result_table is not NULL THEN
12 cursor_name := dbms_sql.open_cursor;
13 dbms_sql.parse(cursor_name,'TRUNCATE TABLE '||result_table,dbms_sql.v7);
14 rows_processed := dbms_sql.execute(cursor_name);
15 dbms_sql.close_cursor(cursor_name);
16 END IF;
17
18 IF str1 is NOT NULL THEN
19 cursor_name := dbms_sql.open_cursor;
20 dbms_sql.parse(cursor_name,str1,dbms_sql.v7);
21 rows_processed := dbms_sql.execute(cursor_name);
22 dbms_sql.close_cursor(cursor_name);
23 END IF;
24
25 IF str2 is NOT NULL THEN
26 cursor_name := dbms_sql.open_cursor;
27 dbms_sql.parse(cursor_name,str2,dbms_sql.v7);
28 rows_processed := dbms_sql.execute(cursor_name);
29 dbms_sql.close_cursor(cursor_name);
30 END IF;
31
32 IF str3 is NOT NULL THEN
33 cursor_name := dbms_sql.open_cursor;
34 dbms_sql.parse(cursor_name,str3,dbms_sql.v7);
35 rows_processed := dbms_sql.execute(cursor_name);
36 dbms_sql.close_cursor(cursor_name);
37 END IF;
38
39 IF str4 is NOT NULL THEN
40 cursor_name := dbms_sql.open_cursor;
41 dbms_sql.parse(cursor_name,str4,dbms_sql.v7);
42 rows_processed := dbms_sql.execute(cursor_name);
43 dbms_sql.close_cursor(cursor_name);
44 END IF;
45
46 EXCEPTION
47 WHEN OTHERS THEN
48 -- IF instr(sqlerrm,'DRG-10817') <>0 THEN (This errror is returned if
49 -- query string contains any stop words. Not required as we are
50 -- now filterung off stop words.)
51
52 IF instr(sqlerrm,'DRG-10308') <>0 THEN
53 IF dbms_sql.is_open(cursor_name) THEN
54 dbms_sql.close_cursor(cursor_name);
55 END IF;
56 fnd_message.set_name('CS','CS_INC_CTX_NO_SERVER');
57 app_exception.raise_exception;
58 ELSE
59 dbms_sql.close_cursor(cursor_name);
60 fnd_message.set_name('CS','CS_SR_CONTEXT_ERROR');
61 fnd_message.set_token('ERRORM',sqlerrm);
62 app_exception.raise_exception;
63 --raise;
64 END IF;
65 END; -- Execute_query_contains.
66
67
68
69 PROCEDURE Update_Context_Index(policy_name IN VARCHAR2,primary_key IN VARCHAR2)
70 IS
71 cursor_name INTEGER;
72 string VARCHAR2(200);
73 rows_processed INTEGER; -- Not used currently
74 BEGIN
75 string := 'BEGIN ctx_dml.reindex('''||policy_name||''','''||primary_key||'''); END;';
76 cursor_name := dbms_sql.open_cursor;
77 dbms_sql.parse(cursor_name,string,dbms_sql.v7);
78 rows_processed := dbms_sql.execute(cursor_name);
79 dbms_sql.close_cursor(cursor_name);
80 EXCEPTION
81 WHEN OTHERS THEN
82 IF dbms_sql.is_open(cursor_name) THEN
83 dbms_sql.close_cursor(cursor_name);
84 END IF;
85 raise;
86 END; -- update_context_index.
87
88 --
89 -- Get_stop_words
90 --
91 PROCEDURE Get_Context_Stop_Words(stop_word_list OUT VARCHAR2)
92 IS
93 cursor_name INTEGER;
94 string VARCHAR2(500);
95 rows_processed INTEGER;
96 stop_word VARCHAR2(100);
97 all_stop_words VARCHAR2(2000) := NULL;
98 BEGIN
99
100 string := 'Select unique pa.pat_value
101 From ctxsys.dr$preference_attribute pa,
102 ctxsys.dr$preference pr,
103 ctxsys.dr$policy po,
104 ctxsys.dr$preference_usage pu
105 Where pa.pat_name = ''STOP_WORD''
106 AND pa.pat_pre_id = pu.pus_pre_id
107 AND pu.pus_pol_id = po.pol_id
108 AND po.pol_name in
109 (''CS_INCIDENTS_SUMMARY'',''CS_INCIDENTS_COMMENTS'',
110 ''CS_INCIDENTS_PROBLEM_DESC'', ''CS_INCIDENTS_RESOLUTION_DESC'')';
111
112 cursor_name := dbms_sql.open_cursor;
113 dbms_sql.parse(cursor_name,string,dbms_sql.v7);
114 dbms_sql.define_column(cursor_name,1,stop_word,100);
115 rows_processed := dbms_sql.execute(cursor_name);
116 LOOP
117 IF dbms_sql.fetch_rows(cursor_name) >0 THEN
118 -- get column values of the row.
119 dbms_sql.column_value(cursor_name,1,stop_word);
120 all_stop_words := all_stop_words || stop_word || ' ';
121 ELSE
122 EXIT;
123 END IF;
124 END LOOP;
125 dbms_sql.close_cursor(cursor_name);
126 stop_word_list:=all_stop_words;
127 EXCEPTION
128 WHEN OTHERS THEN
129 IF dbms_sql.is_open(cursor_name) THEN
130 dbms_sql.close_cursor(cursor_name);
131 END IF;
132 raise;
133 END; -- Get_Stop_Words.
134
135 /***********************************************************************
136 Get_Result_Table: Gets a results table for context search from the pool.
137 ***********************************************************************/
138
139 PROCEDURE Get_Result_Table(result_table OUT VARCHAR2) IS
140 str VARCHAR2(2000) := NULL ;
141 cursor_name INTEGER;
142 rows_processed INTEGER; -- Not used currently
143 BEGIN
144 str := 'BEGIN CTX_QUERY.GETTAB(CTX_QUERY.hittab,:res_tab); END;';
145
146 cursor_name := dbms_sql.open_cursor;
147 dbms_sql.parse(cursor_name,str,dbms_sql.v7);
148 dbms_sql.bind_variable(cursor_name,':res_tab',null,100);
149 rows_processed := dbms_sql.execute(cursor_name);
150 dbms_sql.variable_value(cursor_name,':res_tab',result_table);
151 dbms_sql.close_cursor(cursor_name);
152
153 END Get_Result_Table;
154
155 /***********************************************************************
156 Release_Result_Table: Releases the passed results table from the pool.
157 ***********************************************************************/
158
159 PROCEDURE Release_Result_Table(result_table IN VARCHAR2) IS
160 str VARCHAR2(2000) := NULL ;
161 cursor_name INTEGER;
162 rows_processed INTEGER; -- Not used currently
163 BEGIN
164 str := 'BEGIN CTX_QUERY.RELTAB('''|| result_table || '''); END;';
165
166 cursor_name := dbms_sql.open_cursor;
167 dbms_sql.parse(cursor_name,str,dbms_sql.v7);
168 rows_processed := dbms_sql.execute(cursor_name);
169 dbms_sql.close_cursor(cursor_name);
170 END Release_Result_Table;
171
172
173 END;