[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_QUERY_UTILITIES
Source
1 PACKAGE BODY MSD_DEM_QUERY_UTILITIES AS
2 /* $Header: msddemqutlb.pls 120.6 2011/12/08 09:08:08 rissingh ship $ */
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(1000);
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(1000) := 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
415 EXCEPTION
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;
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
438 /*
439 * Given an identifier for the query to be executed and a list of key value pairs.
440 * This procedure generates the query, replaces the constants and executes the query.
441 */
442 PROCEDURE EXECUTE_QUERY (
443 errbuf OUT NOCOPY VARCHAR2,
444 retcode OUT NOCOPY VARCHAR2,
445 p_entity_name IN VARCHAR2,
446 p_sr_instance_id IN NUMBER,
447 p_key_values IN VARCHAR2 )
448 IS
449
450 TYPE C_GET_CURSOR IS REF CURSOR;
451 xc_get_parts C_GET_CURSOR;
452
453 x_query_part1 VARCHAR2(5000) := NULL;
454 x_query_part2 VARCHAR2(5000) := NULL;
455 x_query_part3 VARCHAR2(5000) := NULL;
456 x_query VARCHAR2(32000) := NULL;
457
458 i NUMBER := NULL;
459 x_pos1 NUMBER := NULL;
460 x_pos2 NUMBER := NULL;
461 x_pos3 NUMBER := NULL;
462
463 x_key VARCHAR2(50) := NULL;
464 x_value VARCHAR2(500) := NULL;
465
466 BEGIN
467
468 msd_dem_common_utilities.log_debug ('Entering: msd_dem_query_utilities.execute_query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
469
470 /* Log the parameters */
471 msd_dem_common_utilities.log_debug (' Entity Name - ' || p_entity_name);
472 msd_dem_common_utilities.log_debug (' Instance Id - ' || to_char(p_sr_instance_id));
473 msd_dem_common_utilities.log_debug (' Key Values Pairs - ' || p_key_values);
474
475
476 OPEN xc_get_parts FOR SELECT query
477 FROM msd_dem_entity_queries mdeq,
478 msd_dem_queries mdq
479 WHERE mdeq.entity_name = p_entity_name
480 AND mdq.query_id = mdeq.part1;
481 FETCH xc_get_parts INTO x_query_part1;
482 CLOSE xc_get_parts;
483
484 OPEN xc_get_parts FOR SELECT query
485 FROM msd_dem_entity_queries mdeq,
486 msd_dem_queries mdq
487 WHERE mdeq.entity_name = p_entity_name
488 AND mdq.query_id = mdeq.part2;
489 FETCH xc_get_parts INTO x_query_part2;
490 CLOSE xc_get_parts;
491
492 OPEN xc_get_parts FOR SELECT query
493 FROM msd_dem_entity_queries mdeq,
494 msd_dem_queries mdq
495 WHERE mdeq.entity_name = p_entity_name
496 AND mdq.query_id = mdeq.part3;
497 FETCH xc_get_parts INTO x_query_part3;
498 CLOSE xc_get_parts;
499
500 x_query := x_query_part1 || x_query_part2 || x_query_part3;
501
502 msd_dem_common_utilities.log_debug (' The query v1 is - ');
503 msd_dem_common_utilities.log_debug (x_query);
504
505
506 /* Replace the constants */
507 IF (p_key_values IS NOT NULL)
508 THEN
509 i:= 0;
510 LOOP
511 i := i + 1;
512
513 x_pos1 := instr (p_key_values, '$', 1, i);
514 x_pos2 := instr (p_key_values, '#', 1, i);
515
516 x_key := substr (p_key_values, x_pos1 + 1, x_pos2 - x_pos1 - 1);
517
518 x_pos3 := instr (p_key_values, '$', 1, i + 1);
519
520 x_value := substr (p_key_values, x_pos2 + 1, x_pos3 - x_pos2 - 1);
521
522 EXIT WHEN (instr ( p_key_values, '$', 1, i + 1) = 0);
523
524 x_query := replace (x_query, x_key, x_value);
525 msd_dem_common_utilities.log_debug ('KEY = ' || x_key || ', VALUE = ' || x_value || ', i = ' || i);
526
527 END LOOP;
528 END IF;
529
530 x_query := replace (x_query, 'C_SR_INSTANCE_ID', to_char(p_sr_instance_id));
531 x_query := replace (x_query, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
532
533 msd_dem_common_utilities.log_debug (' The query v2 is - ');
534 msd_dem_common_utilities.log_debug (x_query);
535
536 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
537 EXECUTE IMMEDIATE x_query;
538 COMMIT;
539 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
540
541 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_query_utilities.execute_query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
542
543 retcode := 0;
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 errbuf := substr(SQLERRM,1,150);
548 retcode := -1;
549
550 msd_dem_common_utilities.log_message ('Exception(1): msd_dem_query_utilities.execute_query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
551 msd_dem_common_utilities.log_message (errbuf);
552 RETURN;
553
554 END EXECUTE_QUERY;
555
556
557 /*
558 * Given a table name, location (MSD(2) or Demantra(1)), this procedure truncates(1) or deletes(2)
559 * all data from the table.
560 */
561 PROCEDURE TRUNCATE_TABLE (
562 errbuf OUT NOCOPY VARCHAR2,
563 retcode OUT NOCOPY VARCHAR2,
564 p_table_name IN VARCHAR2,
565 p_owner IN NUMBER DEFAULT 1,
566 p_truncate IN NUMBER DEFAULT 1 )
567 IS
568
569 x_schema VARCHAR2(100) := NULL;
570 x_sql VARCHAR2(500) := NULL;
571
572 x_dummy1 VARCHAR2(32);
573 x_dummy2 VARCHAR2(32);
574 x_retval BOOLEAN;
575
576
577 BEGIN
578
579 msd_dem_common_utilities.log_debug ('Entering: msd_dem_query_utilities.truncate_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
580
581 /* Log the parameters */
582 msd_dem_common_utilities.log_debug (' Table Name - ' || p_table_name);
583 msd_dem_common_utilities.log_debug (' Owner - ' || to_char(p_owner));
584 msd_dem_common_utilities.log_debug (' Truncate - ' || to_char(p_truncate));
585
586 IF (p_owner = 1)
587 THEN
588 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
589 ELSE
590 x_retval := fnd_installation.get_app_info ( 'MSD', x_dummy1, x_dummy2, x_schema );
591 END IF;
592
593 msd_dem_common_utilities.log_debug (' Schema - ' || x_schema);
594
595 IF (p_truncate = 1)
596 THEN
597 x_sql := 'TRUNCATE TABLE ' || x_schema || '.' || p_table_name;
598 ELSE
599 x_sql := 'DELETE FROM ' || x_schema || '.' || p_table_name;
600 END IF;
601
602 msd_dem_common_utilities.log_debug ('Query - ');
603 msd_dem_common_utilities.log_debug (x_sql);
604
605 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
606 IF (p_truncate = 1)
607 THEN
608 EXECUTE IMMEDIATE x_sql;
609 ELSE
610 EXECUTE IMMEDIATE x_sql;
611 COMMIT;
612 END IF;
613 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
614
615 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_query_utilities.truncate_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
616
617 retcode := 0;
618
619 EXCEPTION
620 WHEN OTHERS THEN
621 errbuf := substr(SQLERRM,1,150);
622 retcode := -1;
623
624 msd_dem_common_utilities.log_message ('Exception(1): msd_dem_query_utilities.truncate_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
625 msd_dem_common_utilities.log_message (errbuf);
626 RETURN;
627
628 END TRUNCATE_TABLE;
629
630
631 END MSD_DEM_QUERY_UTILITIES;
632