1 PACKAGE BODY AMS_DiscovererSQL_PVT AS
2 /* $Header: amsvldcb.pls 115.13 2003/03/09 10:17:50 gjoby ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_DiscovererSQL_PVT';
4 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvldcb.pls';
5 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
6 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
7 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
8
9 PROCEDURE get_source_type_code (
10 p_workbook_name IN VARCHAR2,
11 p_worksheet_name IN VARCHAR2,
12 x_source_type_code OUT NOCOPY VARCHAR2
13 );
14
15 -- Start of Comments
16 --
17 -- NAME
18 -- EUL_TRIGGER$POST_SAVE_DOCUMENT
19 --
20 -- PURPOSE
21 -- 1. This Function if registered by discoverer will save any sql from any worksheet which has been
22 -- saved to the database into the AMS_DISCOVERER_SQL table.
23
24 -- 2. If the SQL string is under 2000 bytes in length then the sequence number will be Zero.
25
26 -- 3. If the SQL string is greater than 2000 bytes then the sequence number will start from One
27 -- and subsequenct insertions will have the sequence number incremented by one.
28
29 -- 4. A check is also performed to see if the same workbook name and worksheet name combination
30 -- already exists in the AMS_DISCOVERER_SQL table. If this is the case all entries for this
31 -- workbook and sheet will be deleted from the table first.
32
33
34 -- Called By:
35 -- Oracle Discoverer.
36
37
38 -- NOTES
39 --
40 --
41 -- HISTORY
42 -- 06/21/1999 tdonohoe created
43 -- 01/15/2002 yxliu changed logic
44 -- End of Comments
45 Function EUL_TRIGGER$POST_SAVE_DOCUMENT
46 ( P_WorkBookOwner IN varchar2,
47 P_WorkBookName IN varchar2,
48 P_WorkSheetName IN varchar2,
49 P_Sequence IN number,
50 P_SQLSegment IN varchar2) RETURN NUMBER IS
51
52 l_max_sequence_number number;
53
54 -- Declare program variables
55 l_sqlerrm varchar2(600);
56 l_sqlcode varchar2(100);
57
58 CURSOR c_get_seq IS
59 SELECT ams_discoverer_sql_s.NEXTVAL
60 FROM DUAL;
61
62 l_discoverer_sql_id NUMBER;
63
64 -- choang - 24-jun-2002
65 -- get the start id for this workbook+worksheet
66 -- get the largest sequence_order and source_type_code
67 -- to determine if the requested worksheet is a replacement
68 -- or a continuation, and if source type code is defined
69 CURSOR c_disco (p_workbook_name IN VARCHAR2, p_worksheet_name IN VARCHAR2) IS
70 SELECT MIN (discoverer_sql_id), MAX (sequence_order), MAX (source_type_code)
71 FROM ams_discoverer_sql
72 WHERE workbook_name = p_workbook_name
73 AND worksheet_name = p_worksheet_name
74 ;
75
76 l_source_type_code ams_discoverer_sql.source_type_code%TYPE;
77 BEGIN
78 OPEN c_disco (p_workbookname, p_worksheetname);
79 FETCH c_disco INTO l_discoverer_sql_id, l_max_sequence_number, l_source_type_code;
80 CLOSE c_disco;
81
82 /* choang - 24-jun-2002 - replaced with new logic
83 --checking for an existing set of entries for this workbook and worksheet combination.
84 select max(sequence_order)
85 into l_max_sequence_number
86 from ams_discoverer_sql
87 where workbook_name = P_WorkBookName
88 and WORKSHEET_NAME = P_WorkSheetName;
89 */
90
91 /* choang - 24-jun-2002 - replaced to use one cursor for performance
92 -- checking the start discoverer_sql_id for this workbook and worksheet
93 -- combination
94 select min(discoverer_sql_id)
95 into l_start_id
96 from ams_discoverer_sql
97 where workbook_name = P_WorkBookName
98 and worksheet_name = P_WorkSheetName;
99 */
100
101 --if the workbook and worksheet combination exists in the AMS_DISCOVERER_SQL table
102 --then delete all entries , then we can insert the new records for the newest version
103 --of this workbook - worksheet.
104 IF ((l_max_sequence_number >= p_sequence) OR (l_max_sequence_number = 0)) THEN
105 DELETE FROM ams_discoverer_sql
106 WHERE workbook_name = p_workbookname
107 AND WORKSHEET_NAME = p_worksheetname;
108 END IF;
109
110 -- start new logic to update source type code
111
112 -- if updating the same workbook + worksheet, we
113 -- need to preserve the disco sql id which is
114 -- associated to marketing objects. the logic:
115 -- - new worksheet = p_sequence in 0,1 and disco sql id is null
116 -- - updated worksheet = p_sequence in 0,1 and disco sql id not null
117 -- - wrapped long sql = p_sequence > 1
118 IF p_sequence IN (0, 1) THEN
119 IF l_discoverer_sql_id IS NULL THEN
120 OPEN c_get_seq;
121 FETCH c_get_seq INTO l_discoverer_sql_id;
122 CLOSE c_get_seq;
123 END IF;
124 ELSE
125 OPEN c_get_seq;
126 FETCH c_get_seq INTO l_discoverer_sql_id;
127 CLOSE c_get_seq;
128 END IF;
129
130 INSERT INTO ams_discoverer_sql (
131 discoverer_sql_id,
132 workbook_owner_name,
133 workbook_name,
134 worksheet_name,
135 sequence_order,
136 last_update_date,
137 last_updated_by,
138 creation_date,
139 created_by,
140 sql_string,
141 source_type_code
142 )
143 VALUES (
144 l_discoverer_sql_id,
145 p_workbookowner,
146 p_workbookname,
147 p_worksheetname,
148 p_sequence,
149 SYSDATE,
150 FND_GLOBAL.user_id,
151 SYSDATE,
152 FND_GLOBAL.user_id,
153 p_sqlsegment,
154 l_source_type_code
155 );
156
157 -- update needed when SQL spans multiple
158 -- segments.
159 get_source_type_code (
160 p_workbook_name => p_workbookname,
161 p_worksheet_name => p_worksheetname,
162 x_source_type_code => l_source_type_code
163 );
164
165 IF l_source_type_code IS NOT NULL THEN
166 UPDATE ams_discoverer_sql
167 SET source_type_code = l_source_type_code
168 WHERE workbook_name = p_workbookname
169 AND worksheet_name = p_worksheetname
170 AND (source_type_code <> l_source_type_code OR source_type_code IS NULL)
171 ;
172 END IF;
173
174 RETURN (0);
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 l_sqlerrm := SQLERRM;
179 l_sqlcode := SQLCODE;
180 RETURN (1);
181 END EUL_TRIGGER$POST_SAVE_DOCUMENT;
182
183 -- Start of Comments
184 --
185 -- NAME
186 -- Search SQL string
187 --
188 -- PURPOSE
189 -- 1. Will search for the p_search_string variable in the set of strings which compose a workbook
190 -- SQL statement and are stored in the AMS_DISCOVERER_SQL table.
191
192 -- 2. x_found will return FND_API.G_TRUE if the string has been found.
193
194 -- 3. x_found_in_str returns the number of the sql string in which the search string was found.
195 -- each SQL string is 2000 characters in length.
196
197 -- 4. p_position returns the position in the string where the first character in the search string
198 -- was found.
199
200 -- 5. x_overflow wil return the number of characters which contain part of the search string
201 -- in the overflow string if the searched for string spans two SQL strings.
202
203 -- 6. p_max_search_len restricts the number of characters to search on from the set of sql strings.
204
205 -- 7. A pl\sql table is returned which contains all of the sql strings that have been processed for
206 -- the specified workbook.
207
208
209 -- HISTORY
210 -- 06/28/1999 tdonohoe created
211 -- 02/08/2000 tdonohoe modified increment variable "l_str_pos" by one position after each search.
212 -- put nvl(l_str_len,0) , it was evaluating to NULL in some cases causing
213 -- the search to fail.
214 -- End of Comments
215
216 Procedure Search_SQL_string(p_search_string in varchar2,
217 p_workbook_name in varchar2,
218 p_worksheet_name in varchar2,
219 p_max_search_len in number ,--default NULL,
220 x_found OUT NOCOPY varchar2 ,
221 x_found_in_str OUT NOCOPY number,
222 x_position OUT NOCOPY number,
223 x_overflow OUT NOCOPY number) IS
224
225 l_sql_table t_SQLtable;
226
227 --the total number of strings for a sql statement.
228 l_str_count number :=0;
229
230 --the current search position in the string.
231 l_str_pos number :=0;
232
233 --the length of the current sql string.
234 l_str_len number :=0;
235
236 --the total number of characters read from the set of sql strings so far.
237 l_total_str_len number :=0;
238
239 --this flag indicates that no more fetched of sql strings are to be made
240 --because the max. number of characters have been read.
241 l_last_fetch varchar2(1);
242
243 --this flag is set to 'Y' when processing on the last sql string has started.
244 l_last_str varchar2(1) := 'N';
245
246 --temporary substring holder used if a p_max_search_len has been reached.
247 l_tmp_str varchar2(2000);
248
249 --if a sub string has to be constructed because of overflow into a second string then it
250 --is stored in this variable.
251 l_substr varchar2(1000);
252
253 --the length of the sub string.
254 l_substr_len number :=0;
255
256 --set to FND_API.G_TRUE when the first character of the searched for string has been found.
257 l_first_char_found varchar2(1);
258
259
260 --the first character being searched for.
261 l_first_char varchar2(1);
262
263 --the position that the first character was found in the current sql string.
264 l_first_char_pos number :=0;
265
266 --the length of the string being searched for.
267 l_search_str_len number :=0;
268
269 --the search string without its first character.
270 l_search_sub_str varchar2(2000);
271
272
273
274 l_sqlerrm varchar2(600);
275 l_sqlcode varchar2(100);
276
277 Cursor C_SQL_string IS Select Sql_String
278 From Ams_Discoverer_SQL
279 Where Workbook_name = p_workbook_name
280 And Worksheet_name = p_worksheet_name
281 Order by Sequence_Order;
282
283 Begin
284
285 l_first_char := substr(p_search_string,1,1);
286 l_search_str_len := length(p_search_string);
287 l_search_sub_str := substr(p_search_string,2,l_search_str_len);
288
289
290 x_found := FND_API.G_FALSE;
291
292 --getting the total number of strings that compose the sql statement.
293 Select Count(*)
294 into l_str_count
295 From ams_discoverer_sql
296 Where Workbook_name = p_workbook_name
297 And Worksheet_name = p_worksheet_name;
298
299 if(l_str_count = 0)then
300 x_found := FND_API.G_FALSE;
301 RETURN;
302 end if;
303
304
305 --Getting the SQL strings.
306 Open C_SQL_string;
307
308 --starting from Zero because the sequence in which the strings are stored
309 --start from Zero.
310
311
312 <<l_fetch_string>>
313 For l_iterator in 0 .. (l_str_count - 1) Loop
314
315
316 --Fetching the next string for the discoverer workbook.
317 Fetch C_SQL_string into l_sql_table(l_iterator + 1);
318
319
320
321 --initializing the current search position for the string.
322 l_str_pos := 0;
323
324 --checking for the last string.
325 if( l_iterator = (l_str_count -1) )then
326 l_last_str := 'Y';
327 end if;
328
329 --getting the length of the current sql string.
330 l_str_len := Length(l_sql_table(l_iterator + 1));
331
332 --updating the total number of characters read from the set of sql strings.
333 l_total_str_len := l_total_str_len + l_str_len;
334
335
336 --if the max. number of characters have been read then set this flag to break
337 --out of the fetch string loop.
338 if(l_total_str_len >= p_max_search_len)then
339
340
341 l_last_fetch := FND_API.G_TRUE;
342 l_tmp_str := substr(l_sql_table(l_iterator + 1),1,p_max_search_len - 1);
343
344
345 l_sql_table(l_iterator + 1) := NULL;
346 l_sql_table(l_iterator + 1) := l_tmp_str;
347
348 end if;
349
350
351 --If First character of search string has been found in the previous string but
352 --the remainder of previous string is too short to contain the rest of the search string.
353 if(l_first_char_found = FND_API.G_TRUE)then
354
355 --getting the substring length.
356 l_substr_len := length(l_substr);
357
358 --concatenating the substring and the necessary number of characters in the current
359 --string to search for search string minus the first character of it.
360
361
362 if( l_substr||substr(l_sql_table(l_iterator + 1),1,((l_search_str_len-1)-nvl(l_substr_len,0)) ) = l_search_sub_str)then
363
364
365 x_found := FND_API.G_TRUE;
366 x_overflow := (l_search_str_len-1)- l_substr_len;
367
368
369 exit l_fetch_string;
370 else
371
372 x_found := FND_API.G_FALSE;
373 --calculating the new search position.
374 l_str_pos := l_str_pos + 1;
375
376
377 end if;
378 end if;
379
380
381 --Loop through the current sql string searching for l_first_char,
382 --If this is found then check that the next length(l_search_sub_str - 1)
383 --characters are part of the search string.
384
385 --If when l_first_char is found there are not enough characters to perform a match, copy the
386 --remaining characters into l_sub_str, read the next string(this is done before this loop,
387 --after the string is fetched) and then perform a check for a match.
388
389 --If l_first_char is not found then read in the next string and continue searching.
390
391 <<l_search_string>>
392 Loop
393
394
395 --getting the position of l_first_char in the current string.
396 l_first_char_pos := Instr(l_sql_table(l_iterator + 1),l_first_char,l_str_pos+1);
397
398
399 --the first character of the search string has been found.
400 if( l_first_char_pos <> 0 )then
401
402 --setting current position in sql string.
403 l_str_pos := l_first_char_pos;
404
405 --indicating that the first character of the searched for string has been found.
406 l_first_char_found := FND_API.G_TRUE;
407
408
409 if(l_str_count = 1) then
410 x_found_in_str := l_iterator;
411 else
412 x_found_in_str := l_iterator + 1;
413 end if;
414
415
416 x_position := l_first_char_pos;
417
418 if(l_search_str_len = 1 )then
419 x_found := FND_API.G_TRUE;
420 exit l_search_string;
421 --there are enough characters left in the current string to find a match.
425 l_substr := substr(l_sql_table(l_iterator + 1),l_str_pos + 1,l_search_str_len - 1);
422 elsif ( (l_str_len - (l_str_pos + 1)) >= (l_search_str_len - 1) )then
423
424
426
427
428 --match has been found.
429 if(l_substr = l_search_sub_str )then
430
431 x_found := FND_API.G_TRUE;
432 exit l_search_string;
433
434 --no match found, update string search position.
435 else
436 x_found := FND_API.G_FALSE;
437 l_str_pos := l_str_pos + 1;
438 end if;
439
440 --not enough characters remaining in current sql string to perform a match.
441 --creating a sub string of the remaining characters, which are used with
442 --the next string fetched to perform a check.
443 elsif((l_str_len - l_str_pos) < (l_search_str_len-1))then
444
445 l_substr := substr(l_sql_table(l_iterator + 1),l_str_pos + 1,l_str_len) ;
446
447 --exit the search string loop and fetch another sql string.
448 exit l_search_string;
449 end if;
450
451 else
452 --l_first_char has not been been found in the current string.
453
454
455 --indicating that the first character of the searched for string has not been found.
456 l_first_char_found := FND_API.G_FALSE;
457
458 --indicating that the search string has not been found;
459 x_found := FND_API.G_FALSE;
460
461 --exit the search string loop and fetch another sql string.
462 exit l_search_string;
463 end if;
464
465
466 End Loop l_search_string;--Searching the current strings.
467
468 --the string has been found , no need to fetch any more sql strings.
469 --OR the max. number of characters have been read.
470 If(x_found = FND_API.G_TRUE or l_last_fetch = FND_API.G_TRUE )then
471
472 exit l_fetch_string;
473 end If;
474
475 End Loop l_fetch_string;--Fetching the SQL strings.
476 Close C_SQL_string;
477
478 EXCEPTION
479 WHEN OTHERS THEN
480
481 if(c_sql_string%ISOPEN)then
482 Close C_SQL_string;
486 l_sqlcode := SQLCODE;
483 end if;
484
485 l_sqlerrm := SQLERRM;
487
488
489 End Search_SQL_string;
490
491
492 PROCEDURE get_source_type_code (
493 p_workbook_name IN VARCHAR2,
494 p_worksheet_name IN VARCHAR2,
495 x_source_type_code OUT NOCOPY VARCHAR2
496 )
497 IS
498 l_source_type_code VARCHAR2(30);
499 l_search_code VARCHAR2(30);
500
501 -- variables used to call search_sql_string
502 l_found VARCHAR2(1);
503 l_found_in_str NUMBER;
504 l_position NUMBER;
505 l_overflow NUMBER;
506
507 CURSOR c_master_types IS
508 SELECT source_type_code
509 FROM ams_list_src_types
510 WHERE master_source_type_flag = 'Y'
511 AND enabled_flag = 'Y'
512 ;
513 BEGIN
514 OPEN c_master_types;
515 LOOP
516 l_source_type_code := NULL;
517
518 FETCH c_master_types INTO l_source_type_code;
519
520 -- Need to encapsulate the source type code
521 -- with single quotes because the saved
522 -- SQL has the search string within quotes.
523 -- Without the quotes, any string with the
524 -- searched string will return regardless if it
525 -- is designated as the source type code.
526 l_search_code := '''' || l_source_type_code || '''';
527
528 search_sql_string (
529 p_search_string => l_search_code,
530 p_workbook_name => p_workbook_name,
531 p_worksheet_name => p_worksheet_name,
532 x_found => l_found,
533 x_found_in_str => l_found_in_str,
534 x_position => l_position,
535 x_overflow => l_overflow
536 );
537
538 EXIT WHEN c_master_types%NOTFOUND OR l_found = FND_API.G_TRUE;
539 END LOOP;
540 CLOSE c_master_types;
541
542 x_source_type_code := l_source_type_code;
543 END get_source_type_code;
544
545
546 END AMS_DiscovererSQL_PVT;
547
548
549
[Home] [Help]