[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_QUERY_UTILITIES
Source
1 PACKAGE BODY MSD_DEM_QUERY_UTILITIES AS
2 /* $Header: msddemqutlb.pls 120.2.12000000.2 2007/09/24 11:14:56 nallkuma noship $ */
3
4 v_srdblink varchar2(100);
5
6
7 /*
8 * This procedure logs a given debug message text in ???
9 * only if the profile MSD_DEM_DEBUG is set to 'Yes'.
10 * param: p_buff - debug message text to be logged.
11 */
12 PROCEDURE LOG_DEBUG ( p_buff IN VARCHAR2)
13 IS
14 BEGIN
15 IF (C_MSD_DEM_DEBUG = 'Y') THEN
16 NULL;
17 END IF;
18 END LOG_DEBUG;
19
20
21
22 /*
23 * This procedure logs a given message text in ???
24 * param: p_buff - message text to be logged.
25 */
26 PROCEDURE LOG_MESSAGE ( p_buff IN VARCHAR2)
27 IS
28 BEGIN
29 NULL;
30 END LOG_MESSAGE;
31
32
33 /* Should be changed to msd_dem_common_utilites*/
34
35 procedure get_db_link(p_instance_id IN NUMBER,
36 p_dblink IN OUT NOCOPY VARCHAR2,
37 p_retcode IN OUT NOCOPY NUMBER)
38
39 as
40
41 Begin
42
43 SELECT decode( m2a_dblink,
44 null, '',
45 '@'||m2a_dblink)
46 INTO p_dblink
47 FROM MSC_APPS_INSTANCES
48 WHERE instance_id = p_instance_id;
49
50
51 -- zia: changed retcode to 0, since 1 means warning
52 --p_retcode := 1 ;
53 p_retcode := 0; /* Should be changed to proper constants from msd_dem_common_utilities */
54
55 Exception
56 when others then
57 p_dblink := null ;
58 p_retcode := -1 ; /* Should be changed to proper constants from msd_dem_common_utilities */
59
60
61 End get_db_link ;
62
63 procedure get_query(retcode OUT NOCOPY NUMBER,
64 query OUT NOCOPY VARCHAR2,
65 p_entity_name IN VARCHAR2,
66 p_instance_id IN NUMBER,
67 p_dest_table IN VARCHAR2 DEFAULT NULL,
68 p_add_where_clause IN VARCHAR2 DEFAULT NULL)
69
70 as
71
72 l_part1_query varchar2(32000);
73 l_part2_query varchar2(32000);
74 l_part3_query varchar2(32000);
75 l_final_query varchar2(32000);
76
77 cursor c_get_part1_query is
78 select query
79 from msd_dem_queries mdq,
80 msd_dem_entity_queries mdeq
81 where mdeq.part1 = mdq.query_id
82 and mdeq.entity_name = p_entity_name;
83
84 cursor c_get_part2_query is
85 select query
86 from msd_dem_queries mdq,
87 msd_dem_entity_queries mdeq
88 where mdeq.part2 = mdq.query_id
89 and mdeq.entity_name = p_entity_name;
90
91 cursor c_get_part3_query is
92 select query
93 from msd_dem_queries mdq,
94 msd_dem_entity_queries mdeq
95 where mdeq.part3 = mdq.query_id
96 and mdeq.entity_name = p_entity_name;
97
98 begin
99
100 msd_dem_common_utilities.log_debug('In procedure: get_query');
101 get_db_link(p_instance_id, v_srdblink, retcode);
102
103 /*msd_dem_common_utilities.log_message();*/
104 msd_dem_common_utilities.log_debug('The instance dblink for this query will be: '|| v_srdblink );
105
106
107 open c_get_part1_query;
108 fetch c_get_part1_query into l_part1_query;
109 close c_get_part1_query;
110
111 open c_get_part2_query;
112 fetch c_get_part2_query into l_part2_query;
113 close c_get_part2_query;
114
115 open c_get_part3_query;
116 fetch c_get_part3_query into l_part3_query;
117 close c_get_part3_query;
118
119 l_final_query := replace(l_part1_query || l_part2_query || l_part3_query, 'C_DEST_TABLE', p_dest_table);
120
121 l_final_query := replace(l_final_query, 'C_ADD_WHERE_CLAUSE', nvl(p_add_where_clause, ' 1 = 1 '));
122
123 l_final_query := replace(l_final_query, 'DBLINK', v_srdblink);
124
125 /*msd_dem_common_utilities.log_debug('The final query is: ' || l_final_query ); */
126
127 query := l_final_query;
128
129 exception
130 when others then
131 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
132 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
133 retcode := -1;
134
135 end get_query;
136
137
138 procedure get_query2(retcode OUT NOCOPY NUMBER,
139 query OUT NOCOPY VARCHAR2,
140 p_entity_name IN VARCHAR2,
141 p_instance_id IN NUMBER,
142 keys_values IN VARCHAR2,
143 flag IN NUMBER,
144 view_name VARCHAR2 default null
145 )
146 as
147
148 l_part1_query varchar2(32000);
149 l_part2_query varchar2(32000);
150 l_part3_query varchar2(32000);
151 l_final_query varchar2(32000);
152 pos1 number;
153 pos2 number;
154 pos3 number;
155 i number;
156 key varchar2(30);
157 value varchar2(300);
158 cv_name varchar2(300);
159
160 x_is_view_valid NUMBER := NULL;
161
162 TYPE c_get_cursor is ref cursor;
163 c_get_parts c_get_cursor;
164
165 begin
166
167 msd_dem_common_utilities.log_debug('In procedure: msd_dem_query_utilities.get_query2');
168 get_db_link(p_instance_id, v_srdblink, retcode);
169
170 /*msd_dem_common_utilities.log_message();*/
171 msd_dem_common_utilities.log_debug('The instance dblink for this query will be: '|| v_srdblink );
172
173 open c_get_parts for select query from msd_dem_queries mdq,msd_dem_entity_queries mdeq where mdeq.part1 = mdq.query_id and mdeq.entity_name = p_entity_name;
174 fetch c_get_parts into l_part1_query;
175 close c_get_parts;
176
177 open c_get_parts for select query from msd_dem_queries mdq,msd_dem_entity_queries mdeq where mdeq.part2 = mdq.query_id and mdeq.entity_name = p_entity_name;
178 fetch c_get_parts into l_part2_query;
179 close c_get_parts;
180
181 open c_get_parts for select query from msd_dem_queries mdq,msd_dem_entity_queries mdeq where mdeq.part3 = mdq.query_id and mdeq.entity_name = p_entity_name;
182 fetch c_get_parts into l_part3_query;
183 close c_get_parts;
184 query := l_part1_query || l_part2_query || l_part3_query;
185 cv_name:=p_entity_name||'_V';
186
187 if(flag=1) then
188 cv_name:=view_name;
189 end if;
190 -- Custom view does not exists. Create a new view.
191 open c_get_parts for select query from msd_dem_queries mdq,msd_dem_entity_queries mdeq where mdeq.part1 = mdq.query_id and mdeq.entity_name = p_entity_name||'_V';
192 fetch c_get_parts into l_part1_query;
193 close c_get_parts;
194
195 open c_get_parts for select query from msd_dem_queries mdq,msd_dem_entity_queries mdeq where mdeq.part2 = mdq.query_id and mdeq.entity_name = p_entity_name||'_V';
196 fetch c_get_parts into l_part2_query;
197 close c_get_parts;
198
199 open c_get_parts for select query from msd_dem_queries mdq,msd_dem_entity_queries mdeq where mdeq.part3 = mdq.query_id and mdeq.entity_name = p_entity_name||'_V';
200 fetch c_get_parts into l_part3_query;
201 close c_get_parts;
202
203 i:=0;
204 l_final_query := l_part1_query || l_part2_query || l_part3_query;
205 loop
206 i:=i+1;
207 pos1:= instr(keys_values,'$',1,i);
208 pos2:=instr(keys_values,'#',1,i);
209 key:=substr(keys_values,pos1+1,pos2-pos1-1);
210 pos3:= instr(keys_values,'$',1,i+1);
211 value:=substr(keys_values,pos2+1,pos3-pos2-1);
212 exit when (instr(keys_values,'$',1,i+1)=0);
213 l_final_query := replace(l_final_query, key,value);
214 query := replace(query, key,value);
215 msd_dem_common_utilities.log_debug('key=' || key ||' value='||value || ' i = ' ||i);
216 end loop;
217 l_final_query := replace(l_final_query,'C_SOURCE_VIEW_NAME',cv_name);
218 query := replace(query,'C_DBLINK',v_srdblink);
219 query := replace(query,'C_SOURCE_VIEW_NAME',cv_name);
220 --execute create view query by passing it to the source procedure
221 if(flag=0) then
222 l_final_query := replace(l_final_query,'''','''''');
223 l_final_query:='
224 begin
225 MSD_DEM_SR_UTIL.EXECUTE_REMOTE_QUERY'||v_srdblink||'('''||l_final_query||''');
226 end;';
227 msd_dem_common_utilities.log_debug('The query is: ' || l_final_query );
228 execute immediate l_final_query;
229
230 /* Check if the view created is VALID or NOT */
231 begin
232 execute immediate 'SELECT 1 FROM ' || cv_name || v_srdblink || ' WHERE 1 = 2 ' INTO x_is_view_valid;
233 exception
234 when no_data_found then
235 null;
236 when others then
237 retcode := -1;
238 msd_dem_common_utilities.log_message ('Error: msd_dem_query_utilities.get_query2 - ');
239 msd_dem_common_utilities.log_message ('The source view ' || cv_name || ' was not created sucessfully');
240 end;
241 else
242 msd_dem_common_utilities.log_message('In msd_dem_query_utilities.get_query - ');
243 msd_dem_common_utilities.log_message('Custom View ' || cv_name || ' used.');
244 end if;
245 exception
246 when others then
247 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
248 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
249 retcode := -1;
250
251 end get_query2;
252
253
254 PROCEDURE GET_QUERY3 (
255 retcode OUT NOCOPY NUMBER,
256 query OUT NOCOPY VARCHAR2,
257 p_entity_name IN VARCHAR2,
258 p_instance_id IN NUMBER,
259 p_key_values IN VARCHAR2,
260 p_custom_view_flag IN NUMBER,
261 p_custom_view_name IN VARCHAR2 DEFAULT NULL,
262 p_series_type IN NUMBER DEFAULT 1,
263 p_ps_view_name IN VARCHAR2 DEFAULT NULL )
264 AS
265
266 TYPE C_GET_CURSOR IS REF CURSOR;
267 xc_get_parts C_GET_CURSOR;
268
269 x_query_part1 VARCHAR2(5000) := NULL;
270 x_query_part2 VARCHAR2(5000) := NULL;
271 x_query_part3 VARCHAR2(5000) := NULL;
272 x_query_final VARCHAR2(32000) := NULL;
273
274 i NUMBER := NULL;
275 x_pos1 NUMBER := NULL;
276 x_pos2 NUMBER := NULL;
277 x_pos3 NUMBER := NULL;
278
279 x_key VARCHAR2(50) := NULL;
280 x_value VARCHAR2(500) := NULL;
281
282 x_view_name VARCHAR2(100) := NULL;
283 x_is_view_valid NUMBER := NULL;
284
285 BEGIN
286 log_debug ('Entering: msd_dem_query_utilities.get_query2 - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
287
288 IF (p_series_type = 1)
289 THEN
290 get_db_link(p_instance_id, v_srdblink, retcode);
291 IF (retcode = -1)
292 THEN
293 log_message ('Error(1): msd_dem_query_utilities.get_query2 - Unable to get db_link' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
294 query := NULL;
295 RETURN;
296 END IF;
297 ELSE
298 v_srdblink := NULL;
299 END IF;
300
301 log_debug ('The instance dblink for this query will be: ' || v_srdblink);
302
303 OPEN xc_get_parts FOR SELECT query
304 FROM msd_dem_entity_queries mdeq,
305 msd_dem_queries mdq
306 WHERE mdeq.entity_name = p_entity_name
307 AND mdq.query_id = mdeq.part1;
308 FETCH xc_get_parts INTO x_query_part1;
309 CLOSE xc_get_parts;
310
311 OPEN xc_get_parts FOR SELECT query
312 FROM msd_dem_entity_queries mdeq,
313 msd_dem_queries mdq
314 WHERE mdeq.entity_name = p_entity_name
315 AND mdq.query_id = mdeq.part2;
316 FETCH xc_get_parts INTO x_query_part2;
317 CLOSE xc_get_parts;
318
319 OPEN xc_get_parts FOR SELECT query
320 FROM msd_dem_entity_queries mdeq,
321 msd_dem_queries mdq
322 WHERE mdeq.entity_name = p_entity_name
323 AND mdq.query_id = mdeq.part3;
324 FETCH xc_get_parts INTO x_query_part3;
325 CLOSE xc_get_parts;
326
327 query := x_query_part1 || x_query_part2 || x_query_part3;
328
329 x_view_name := p_entity_name || '_V';
330 IF (p_series_type = 2)
331 THEN
332 x_view_name := p_ps_view_name;
333 END IF;
334
335 IF (p_custom_view_flag = 1)
336 THEN
337 x_view_name := p_custom_view_name;
338 END IF;
339
340 IF ( p_series_type = 1
341 AND nvl(p_custom_view_flag, 0) <> 1)
342 THEN
343
344 x_query_part1 := NULL;
345 x_query_part2 := NULL;
346 x_query_part3 := NULL;
347
348 OPEN xc_get_parts FOR SELECT query
349 FROM msd_dem_entity_queries mdeq,
350 msd_dem_queries mdq
351 WHERE mdeq.entity_name = x_view_name
352 AND mdq.query_id = mdeq.part1;
353 FETCH xc_get_parts INTO x_query_part1;
354 CLOSE xc_get_parts;
355
356 OPEN xc_get_parts FOR SELECT query
357 FROM msd_dem_entity_queries mdeq,
358 msd_dem_queries mdq
359 WHERE mdeq.entity_name = x_view_name
360 AND mdq.query_id = mdeq.part2;
361 FETCH xc_get_parts INTO x_query_part2;
362 CLOSE xc_get_parts;
363
364 OPEN xc_get_parts FOR SELECT query
365 FROM msd_dem_entity_queries mdeq,
366 msd_dem_queries mdq
367 WHERE mdeq.entity_name = x_view_name
368 AND mdq.query_id = mdeq.part3;
369 FETCH xc_get_parts INTO x_query_part3;
370 CLOSE xc_get_parts;
371
372 x_query_final := x_query_part1 || x_query_part2 || x_query_part3;
373
374 END IF;
375
376 i:= 0;
377 LOOP
378 i := i + 1;
379
380 x_pos1 := instr (p_key_values, '$', 1, i);
381 x_pos2 := instr (p_key_values, '#', 1, i);
382
383 x_key := substr (p_key_values, x_pos1 + 1, x_pos2 - x_pos1 - 1);
384
385 x_pos3 := instr (p_key_values, '$', 1, i + 1);
386
387 x_value := substr (p_key_values, x_pos2 + 1, x_pos3 - x_pos2 - 1);
388
389 EXIT WHEN (instr ( p_key_values, '$', 1, i + 1) = 0);
390
391 x_query_final := replace (x_query_final, x_key, x_value);
392 query := replace (query, x_key, x_value);
393 log_debug('KEY = ' || x_key || ', VALUE = ' || x_value || ', i = ' || i);
394
395 END LOOP;
396
397 x_query_final := replace (x_query_final, 'C_SOURCE_VIEW_NAME', x_view_name);
398 query := replace(query, 'C_DBLINK', v_srdblink);
399 query := replace(query, 'C_SOURCE_VIEW_NAME', x_view_name);
400
401
402 IF ( p_series_type = 1
403 AND p_custom_view_flag = 0)
404 THEN
405
406 x_query_final := replace (x_query_final, '''', '''''');
407 x_query_final:=' BEGIN msd_dem_sr_util.execute_remote_query' || v_srdblink || '(''' || x_query_final || '''); END;';
408
409 log_debug ('The source query is : ' || x_query_final);
410 EXECUTE IMMEDIATE x_query_final;
411
412 /* Check if the view created is VALID or NOT */
413 BEGIN
414 EXECUTE IMMEDIATE 'SELECT 1 FROM ' || x_view_name || v_srdblink || ' WHERE 1 = 2 ' INTO x_is_view_valid;
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 NULL;
418 WHEN OTHERS THEN
419 retcode := -1;
420 log_message ('Error(2): msd_dem_query_utilities.get_query2 - ');
421 log_message ('The source view ' || x_view_name || ' was not created sucessfully');
422 END;
423 ELSE
424 log_message('Custom View ' || x_view_name || ' used.');
425 END IF;
426
427 log_debug ('Exiting: msd_dem_query_utilities.get_query2 - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
428
429 EXCEPTION
430 WHEN OTHERS THEN
431 log_message (substr(SQLERRM,1,150));
432 retcode := -1;
433
434 END GET_QUERY3;
435
436
437 END MSD_DEM_QUERY_UTILITIES;
438