[Home] [Help]
PACKAGE BODY: APPS.CSF_REMOTE_VIEWS_PUB
Source
1 PACKAGE BODY CSF_REMOTE_VIEWS_PUB AS
2 /* $Header: CSFPRVWB.pls 115.7.11510.2 2004/06/24 05:20:52 srengana ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSF_REMOTE_VIEWS_PUB';
5
6 procedure Parse_Query
7 ( p_api_version IN NUMBER
8 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
9 , x_return_status OUT VARCHAR2
10 , x_msg_count OUT NUMBER
11 , x_msg_data OUT VARCHAR2
12 , p_sql_query IN VARCHAR2
13 , x_query_correct OUT VARCHAR2
14 )
15 is
16
17 l_api_name CONSTANT VARCHAR2(30) := 'PARSE_QUERY';
18 l_api_version CONSTANT NUMBER := 1.0;
19
20 t_cursor_name integer;
21
22 begin
23 -- Standard call to check for call compatibility.
24 IF NOT FND_API.Compatible_API_Call
25 ( l_api_version
26 , p_api_version
27 , l_api_name
28 , G_PKG_NAME
29 )
30 THEN
31 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
32 END IF;
33 -- Initialize message list if p_init_msg_list is set to TRUE.
34 IF FND_API.to_Boolean
35 ( p_init_msg_list
36 )
37 THEN
38 FND_MSG_PUB.initialize;
39 END IF;
40 -- Initialize API return status to success
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42 -- All statements wich do not begin with 'select' are to be rejected.
43 -- This way modifications with statements like update ... or
44 -- delete ... can be prevented.
45 if Upper
46 ( SubStr
47 ( LTrim
48 ( p_sql_query
49 )
50 , 1
51 , 6
52 )
53 ) <> 'SELECT'
54 then
55 x_query_correct := FND_API.G_FALSE;
56 end if;
57 t_cursor_name := dbms_sql.open_cursor;
58 dbms_sql.parse
59 ( t_cursor_name
60 , p_sql_query
61 , dbms_sql.native
62 );
63 dbms_sql.close_cursor
64 ( t_cursor_name
65 );
66 x_query_correct := FND_API.G_TRUE;
67 -- Standard call to get message count and if count is 1, get message info.
68 FND_MSG_PUB.Count_And_Get
69 ( p_count => x_msg_count
70 , p_data => x_msg_data
71 );
72 EXCEPTION
73 WHEN FND_API.G_EXC_ERROR
74 THEN
75 x_return_status := FND_API.G_RET_STS_ERROR ;
76 FND_MSG_PUB.Count_And_Get
77 ( p_count => x_msg_count
78 , p_data => x_msg_data
79 );
80 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
81 THEN
82 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
83 FND_MSG_PUB.Count_And_Get
84 ( p_count => x_msg_count
85 , p_data => x_msg_data
86 );
87 WHEN OTHERS
88 THEN
89 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
90 IF FND_MSG_PUB.Check_Msg_Level
91 ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
92 )
93 THEN
94 FND_MSG_PUB.Add_Exc_Msg
95 ( G_PKG_NAME
96 , l_api_name
97 );
98 END IF;
99 FND_MSG_PUB.Count_And_Get
100 ( p_count => x_msg_count
101 , p_data => x_msg_data
102 );
103 if dbms_sql.is_open
104 (t_cursor_name
105 )
106 then
107 dbms_sql.close_cursor
108 (t_cursor_name
109 );
110 end if;
111 x_query_correct := FND_API.G_FALSE;
112 end Parse_Query;
113
114 procedure Execute_Remote_View
115 ( p_api_version IN NUMBER
116 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
117 , p_commit IN VARCHAR2 := FND_API.G_FALSE
118 , x_return_status OUT VARCHAR2
119 , x_msg_count OUT NUMBER
120 , x_msg_data OUT VARCHAR2
121 , p_sqlstring IN VARCHAR2
122 , p_parameter_string IN VARCHAR2
123 , p_sqltitle IN VARCHAR2
124 , p_role IN VARCHAR2
125 , p_requestdate IN DATE
126 , p_query_id IN NUMBER
127 , p_queryrequest_id IN NUMBER := FND_API.G_MISS_NUM
128 , x_queryrequest_id OUT NUMBER
129 , x_notification_id OUT NUMBER
130 )
131 is
132
133 l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE_REMOTE_VIEW';
134 l_api_version CONSTANT NUMBER := 1.0;
135
136 c_query number;
137 t_dummy number;
138 col_count integer;
139 rec_tab dbms_sql.desc_tab;
140 col_num number;
141 t_header varchar2(2000);
142 t_header_line varchar2(2000);
143 t_result varchar2(32767);
144 t_msg_text varchar2(32767);
145 t_rec dbms_sql.desc_rec;
146 t_col_var varchar2(2000);
147 t_col_num number;
148 t_col_dat date;
149 t_col_row rowid;
150 t_sqlstring varchar2(2000);
151 t_input varchar2(2000);
152 t_NewLine varchar2(1) := fnd_global.local_chr(10);
153 t_length number;
154 t_first_line varchar2(2000);
155 t_err_string varchar2(2000);
156 t_not_id number;
157 t_request_id number;
158
159 --MAIL should get fnd username i.s.o resource_number
160 cursor c_user
161 ( b_res_num varchar2
162 )
163 is
164 select fnd.USER_NAME
165 from FND_USER fnd
166 , JTF_RS_RESOURCE_EXTNS jtf
167 where jtf.USER_ID = fnd.USER_ID
168 and jtf.RESOURCE_NUMBER = b_res_num;
169
170 r_user c_user%ROWTYPE;
171 t_user varchar2(100);
172
173
174
175
176 -- Private function!
177 function replace_vars
178 ( i_sqlstring in varchar2
179 , i_queryinput in varchar2
180 , o_replacements out varchar2
181 )
182 return varchar2
183 is
184
185 t_sql varchar2(2000) := '';
186 t_par_offset number(4) := 1;
187 t_par_offset2 number(4) := 1;
188 t_sql_offset number(4) := 1;
189 t_sql_offset2 number(4) := 1;
190 t_str_copy_offset number(4) := 1;
191 t_par_len number(4) := Length
192 ( p_parameter_string
193 );
194 t_sep varchar2(1) := fnd_global.local_chr(2);
195 t_par varchar2(2000);
196 t_quote varchar2(1) := '''';
197 t_input varchar2(2000) := '';
198
199 -- Private function!
200 function SQL_quotes
201 ( i_single_quotes_string varchar2
202 ) return varchar2
203 is
204 begin
205 -- Every string has to start and end with a quote => 2 quotes.
206 -- A quote in a string has to have an extra quote to distingish
207 -- it from the end-of-string-quote.
208 -- Single Quote => 4 consecutive quotes
209 -- Double Quote => 6 consecutive quotes
210 return replace
211 ( i_single_quotes_string
212 , ''''
213 , ''''''
214 );
215 end SQL_quotes;
216
217 --start replace_vars
218 begin
219 t_par_offset2 := InStr
220 ( i_queryinput
221 , t_sep
222 , t_par_offset
223 );
224 while t_par_offset2 <> 0 loop
225 t_par := SubStr
226 ( i_queryinput
227 , t_par_offset
228 , t_par_offset2 - t_par_offset
229 );
230 t_par_offset := t_par_offset2 + 1;
231 t_par_offset2 := InStr
232 ( i_queryinput
233 , t_sep
234 , t_par_offset
235 );
236 t_sql_offset := InStr
237 ( i_sqlstring
238 , '['
239 , t_sql_offset
240 );
241 t_sql_offset2 := InStr
242 ( i_sqlstring
243 , ']'
244 , t_sql_offset
245 );
246 -- Forward the parameters to the message.
247 if ( t_sql_offset > 0
248 and t_sql_offset2 > 0
249 and t_par is not null
250 )
251 then
252 t_input := t_input
253 || SubStr
254 ( i_sqlstring
255 , t_sql_offset
256 , t_sql_offset2 - t_sql_offset + 1
257 )
258 || ' = '
259 || t_par
260 || t_NewLine;
261 -- check for required quotes around parameter.
262 if SubStr
263 ( i_sqlstring
264 , t_sql_offset + 1
265 , 1
266 ) = '#'
267 then
268 -- Dates are taken literally. It is up to the maker of the query
269 -- to provide a query that will accept a date the way engineers
270 -- will input it.
271 null;
272 elsif SubStr
273 ( i_sqlstring
274 , t_sql_offset + 1
275 , 1
276 ) = '$'
277 then
278 t_par := t_quote
279 || SQL_quotes
280 ( Replace
281 ( t_par
282 , '*'
283 , '%'
284 )
285 )
286 || t_quote;
287 else
288 -- numbers are to be taken literally.
289 null;
290 end if;
291 t_sql := t_sql
292 || SubStr
293 ( i_sqlstring
294 , t_str_copy_offset
295 , t_sql_offset - t_str_copy_offset
296 )
297 || t_par;
298 t_str_copy_offset := t_sql_offset2 + 1;
299 t_sql_offset := t_sql_offset2 + 1;
300 else
301 o_replacements := 'Parameter-mapping error detected!';
302 return 'PARAMETER ERROR';
303 end if;
304 end loop;
305 t_sql := t_sql
306 || SubStr
307 ( p_sqlstring
308 , t_str_copy_offset
309 , Length
310 ( p_sqlstring
311 )
312 );
313 o_replacements := t_input;
314 return t_sql;
315 end replace_vars;
316
317 -- start Execute_Remote_View
318 begin
319 -- Standard Start of API savepoint
320 SAVEPOINT EXECUTE_REMOTE_VIEW_PUB;
321 -- Standard call to check for call compatibility.
322 IF NOT FND_API.Compatible_API_Call
323 ( l_api_version
324 , p_api_version
325 , l_api_name
326 , G_PKG_NAME
327 )
328 THEN
329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
330 END IF;
331 -- Initialize message list if p_init_msg_list is set to TRUE.
332 IF FND_API.to_Boolean
333 ( p_init_msg_list
334 )
335 THEN
336 FND_MSG_PUB.initialize;
337 END IF;
338 -- Initialize API return status to success
339 x_return_status := FND_API.G_RET_STS_SUCCESS;
340
341 -- Convert resource_number to apps user name
342 open c_user
343 ( b_res_num => p_role
344 );
345 fetch c_user
346 into r_user;
347 if c_user%found
348 then
349 t_user := r_user.user_name;
350 else
351 t_user := 'Unknown user';
352 end if;
353 close c_user;
354
355 -- Runtime initializations
356 fnd_message.set_name
357 ( 'CSF'
358 , 'CSF_RMT_VWS_EXECUTE_REQUEST'
359 );
360 fnd_message.set_token
361 ( 'REMOTE_VIEW'
362 , p_sqltitle
363 );
364 fnd_message.set_token
365 ( 'REQUEST_DATE'
366 , fnd_date.date_to_displaydt
367 ( sysdate
368 )
369 );
370 fnd_message.set_token
371 ( 'ENGINEER'
372 , t_user
373 );
374 t_first_line := fnd_message.get;
375 -- Check if parameters were supplied.
376 t_sqlstring := replace_vars
377 ( p_sqlstring
378 , p_parameter_string
379 , t_input
380 );
381 if t_sqlstring = 'PARAMETER ERROR'
382 then
383 fnd_message.Set_Name
384 ( 'CSF'
385 , 'CSF_RMT_VWS_PAR_MAPPING'
386 );
387 fnd_msg_pub.add;
388 raise FND_API.G_EXC_UNEXPECTED_ERROR;
389 else
390 c_query := dbms_sql.open_cursor;
391 -- Start a new block to be able to trap exceptions
392 -- when parsing it fails.
393 begin
394 dbms_sql.parse
395 ( c_query
396 , t_sqlstring
397 , dbms_sql.native
398 );
399 t_dummy := dbms_sql.execute
400 ( c_query
401 );
402 dbms_sql.describe_columns
403 ( c_query
404 , col_count
405 , rec_tab
406 );
407 t_header := '';
408 t_header_line := '';
409 for col_num in 1 .. col_count loop
410 t_rec := rec_tab
411 ( col_num
412 );
413 if t_rec.col_type in ( 1
414 , 96
415 ) --char
416 then
417 t_header := t_header
418 || RPad
419 ( t_rec.col_name
420 , t_rec.col_max_len
421 , ' '
422 )
423 || ' ';
424 t_header_line := t_header_line
425 || RPad
426 ( '-'
427 , t_rec.col_max_len
428 , '-'
429 )
430 || ' ';
431 dbms_sql.define_column
432 ( c_query
433 , col_num
434 , t_col_var
435 , t_rec.col_max_len
436 );
437 elsif t_rec.col_type = 2 --number
438 then
439 if t_rec.col_precision > 0
440 then
441 t_length := t_rec.col_precision;
442 else
443 t_length := t_rec.col_max_len;
444 end if;
445 t_header := t_header
446 || LPad
447 ( t_rec.col_name
448 , t_length
449 , ' '
450 )
451 || ' ';
452 t_header_line := t_header_line
453 || RPad
454 ( '-'
455 , t_length
456 , '-'
457 )
458 || ' ';
459 dbms_sql.define_column
460 ( c_query
461 , col_num
462 , t_col_num
463 );
464 elsif t_rec.col_type = 11 --ROWID
465 then
466 t_header := t_header
467 || LPad
468 ( t_rec.col_name
469 , 18
470 , ' '
471 )
472 || ' ';
473 t_header_line := t_header_line
474 || RPad
475 ( '-'
476 , 18
477 , '-'
478 )
479 || ' ';
480 dbms_sql.define_column_rowid
481 ( c_query
482 , col_num
483 , t_col_row
484 );
485 elsif t_rec.col_type = 12 --date
486 then
487 t_header := t_header
488 || LPad
489 ( t_rec.col_name
490 , 9
491 , ' '
492 )
493 || ' ';
494 t_header_line := t_header_line
495 || RPad
496 ( '-'
497 , 9
498 , '-'
499 )
500 || ' ';
501 dbms_sql.define_column
502 ( c_query
503 , col_num
507 t_header := t_header
504 , t_col_dat
505 );
506 else
508 || '?'
509 || ' ';
510 t_header_line := t_header_line
511 || '-'
512 || ' ';
513 end if;
514 end loop;
515 t_result := '';
516 loop
517 if dbms_sql.fetch_rows
518 ( c_query
519 ) > 0
520 then
521 -- get column values of the row
522 for col_num in 1 .. col_count loop
523 t_rec := rec_tab
524 ( col_num
525 );
526 if t_rec.col_type in ( 1
527 , 96
528 ) --char
529 then
530 dbms_sql.column_value
531 ( c_query
532 , col_num
533 , t_col_var
534 );
535 t_result := t_result
536 || RPad
537 ( t_col_var
538 , t_rec.col_max_len
539 , ' '
540 )
541 || ' ';
542 elsif t_rec.col_type = 2 --number
543 then
544 dbms_sql.column_value
545 ( c_query
546 , col_num
547 , t_col_num
548 );
549 if t_rec.col_precision > 0
550 then
551 t_length := t_rec.col_precision;
552 else
553 -- numeric expressions don't have a precision.
554 t_length := t_rec.col_max_len;
555 end if;
556 t_result := t_result
557 || LPad
558 ( To_Char
559 ( t_col_num
560 )
561 , t_length
562 , ' '
563 )
564 || ' ';
565 elsif t_rec.col_type = 11 --ROWID
566 then
567 dbms_sql.column_value_rowid
568 ( c_query
569 , col_num
570 , t_col_row
571 );
572 t_result := t_result
573 || t_col_row
574 || ' ';
575 elsif t_rec.col_type = 12 --date
576 then
577 dbms_sql.column_value
578 ( c_query
579 , col_num
580 , t_col_dat
581 );
582 t_result := t_result
583 || LPad
584 ( To_Char
585 ( t_col_dat
586 )
587 , 9
588 , ' '
589 )
590 || ' ';
591 else
592 t_result := t_result
593 || '?'
594 || ' ';
595 end if;
596 end loop;
597 t_result := t_result
598 || t_NewLine ;
599 else
600 exit;
601 end if;
602 end loop;
603
604 -- Combine various results to from the msg_text.
605 t_msg_text := t_first_line
606 || t_NewLine
607 || p_sqlstring
608 || t_NewLine
609 || t_input
610 || t_NewLine
611 || t_header
612 || t_NewLine
613 || t_header_line
614 || t_NewLine
615 || t_result;
616 -- Ensure the data is not too long.
617 if Length
618 ( t_msg_text
619 ) > 2000
620 then
621 -- Shorten the message, so at least something will show up.
622 fnd_message.set_name
623 ( 'CSF'
624 , 'CSF_RMT_VWS_RESULT_TOO_LONG'
625 );
626 t_err_string := fnd_message.get;
627 t_msg_text := SubStr
628 ( t_msg_text
629 , 1
630 , 1950
631 )
632 || t_NewLine
633 || '...'
634 || t_NewLine
635 || t_err_string
636 || t_NewLine;
637 end if;
638 exception
639 when others
640 then
641 dbms_sql.close_cursor
642 ( c_query
643 );
644 fnd_message.set_name
645 ( 'CSF'
646 , 'CSF_RMT_VWS_PARSE_ERROR'
647 );
648 fnd_msg_pub.add;
649 raise FND_API.G_EXC_UNEXPECTED_ERROR;
650 end;
651 dbms_sql.close_cursor
652 ( c_query
653 );
654 end if;
655 -- Executing of the query is finished here.
656 --
657 -- support for the subject has been dropped, as notifications will not
658 -- allow it. Place the subject as the first line in the body-text.
662 ( role => t_user
659 -- Place the results in a notification and table csf_l_queryrequests.
660
661 t_not_id := wf_notification.send
663 , msg_type => 'CS_MSGS'
664 , msg_name => 'FYI_MESSAGE'
665 );
666 wf_notification.SetAttrText
667 ( t_not_id
668 , 'SENDER'
669 , 'Remote Query Daemon'
670 );
671 wf_notification.SetAttrText
672 ( t_not_id
673 , 'MESSAGE_TEXT'
674 , t_msg_text
675 );
676 -- Handle the PK.
677 if p_queryrequest_id is null
678 then
679 -- Generate one.
680 select csf_l_queryrequests_s.nextval
681 into t_request_id
682 from dual;
683 else
684 -- Reuse the one provided.
685 t_request_id := p_queryrequest_id;
686 end if;
687 insert into csf_l_queryrequests
688 ( QUERYREQUEST_ID
689 , EMPLID
690 , REQUESTDATE
691 , QUERY_ID
692 , QUERYINPUT
693 , LAST_UPDATE_DATE
694 , LAST_UPDATED_BY
695 , CREATION_DATE
696 , CREATED_BY
697 , LAST_UPDATE_LOGIN
698 )
699 values( t_request_id
700 , p_role
701 , p_requestdate
702 , p_query_id
703 , p_parameter_string
704 , sysdate
705 , fnd_global.user_id
706 , sysdate
707 , fnd_global.user_id
708 , 0
709 );
710 x_queryrequest_id := t_request_id;
711
712 -- Standard check of p_commit.
713 IF FND_API.To_Boolean
714 ( p_commit
715 )
716 THEN
717 COMMIT WORK;
718 END IF;
719 -- Ensure out parameters are correctly set.
720 x_notification_id := t_not_id;
721 FND_MSG_PUB.Count_And_Get
722 ( p_count => x_msg_count
723 , p_data => x_msg_data
724 );
725 EXCEPTION
726 WHEN FND_API.G_EXC_ERROR
727 THEN
728 ROLLBACK TO EXECUTE_REMOTE_VIEW_PUB;
729 x_return_status := FND_API.G_RET_STS_ERROR ;
730 FND_MSG_PUB.Count_And_Get
731 ( p_count => x_msg_count
732 , p_data => x_msg_data
733 );
734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
735 THEN
736 ROLLBACK TO EXECUTE_REMOTE_VIEW_PUB;
737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
738 FND_MSG_PUB.Count_And_Get
739 ( p_count => x_msg_count
740 , p_data => x_msg_data
741 );
742 WHEN OTHERS
743 THEN
744 ROLLBACK TO EXECUTE_REMOTE_VIEW_PUB;
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
746 IF FND_MSG_PUB.Check_Msg_Level
747 ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
748 )
749 THEN
750 FND_MSG_PUB.Add_Exc_Msg
751 ( G_PKG_NAME
752 , l_api_name
753 );
754 END IF;
755 FND_MSG_PUB.Count_And_Get
756 ( p_count => x_msg_count
757 , p_data => x_msg_data
758 );
759 end execute_remote_view;
760
761
762 -- package body CSF_REMOTE_VIEWS_PUB
763 begin
764 null;
765 end CSF_REMOTE_VIEWS_PUB;