1 PACKAGE BODY IRC_DOC_UTIL AS
2 /* $Header: iridoutl.pkb 120.0.12010000.2 2008/08/05 10:48:46 ubhat ship $ */
3 --
4 g_index_name varchar2(80);
5 g_StartConcatenator CONSTANT VARCHAR2(3) := '...';
6 g_EndConcatenator CONSTANT VARCHAR2(4) := '... ';
7 --
8 -- **************************************************************************
9 -- getContentTeaser : Defaulted Index Version (full comments in Package spec
10 -- **************************************************************************
11 --
12 FUNCTION getContentTeaser(p_document_id INTEGER,
13 p_search_string VARCHAR2)
14 RETURN VARCHAR2 IS
15 --
16 -- Cursor to retrieve the schema name
17 --
18 cursor csr_user is
19 select oracle_username
20 from fnd_oracle_userid
21 where oracle_id = 800;
22 --
23 l_hr_username fnd_oracle_userid.oracle_username%TYPE :=null ;
24 BEGIN
25 --
26 if (g_index_name is null) then
27 open csr_user;
28 fetch csr_user into l_hr_username;
29 close csr_user;
30 g_index_name:=l_hr_username||'.'||IRC_DOC_UTIL.IRC_DEFAULT_INDEX;
31 end if;
32 --
33 RETURN IRC_DOC_UTIL.getContentTeaser(p_document_id
34 ,p_search_string
35 ,g_index_name );
36 END getContentTeaser;
37
38 --
39 -- **************************************************************************
40 -- getContentTeaser : Specific Index Version (full comments in Package spec)
41 -- **************************************************************************
42 --
43 FUNCTION getContentTeaser(p_document_id INTEGER,
44
45 p_search_string VARCHAR2,
46 p_ctx_index VARCHAR2)
47 RETURN VARCHAR2 IS
48 --
49 -- Cursor to get rowid value for the document_id
50 --
51 CURSOR csr_get_rowid(p_document_id INTEGER) IS
52 SELECT rowid,character_doc
53 FROM irc_documents
54 where document_id = p_document_id;
55 --
56 vOffsetTable CTX_DOC.HIGHLIGHT_TAB;
57 vTeaserLine VARCHAR2(1000);
58 l_procedure_name constant varchar2(80):='irc_doc_util.getcontentteaser';
59 l_rowid ROWID;
60 l_doc clob;
61 l_pos_start1 number;
62 l_pos_end1 number;
63 l_pos_start2 number;
64 l_pos_end2 number;
65 l_doc_length number;
66 l_text varchar2(2000);
67 vCounter number;
68 BEGIN
69 --
70 --
71 -- Run the Intermedia Text procedure to generate a PL/SQL table
72 -- holding the offsets of found terms based on the supplied query
73 -- criteria
74 --
75 --
76 if(fnd_log.g_current_runtime_level <= fnd_log.level_procedure) then
77 fnd_log.string(fnd_log.level_procedure,'per.plsql.'||l_procedure_name,'10');
78 end if;
79 --
80 OPEN csr_get_rowid(p_document_id);
81 FETCH csr_get_rowid into l_rowid,l_doc;
82 CLOSE csr_get_rowid;
83 ctx_doc.highlight(index_name => p_ctx_index,
84 textkey => l_rowid,
85 text_query => p_search_string,
86 restab => vOffsetTable,
87 plaintext => TRUE);
88 --
89 -- we are going to return 2 preview chunks of about 150 characters.
90 --
91 if vOffsetTable(1).offset<75 then
92 l_pos_start1:=1;
93 else
94 --find the next space after 75 characters before the hit
95 l_pos_start1:=vOffsetTable(1).offset-75;
96 vTeaserLine:=g_StartConcatenator;
97 end if;
98 --
99 -- find the end position
100 --
101 l_doc_length:=dbms_lob.getlength(l_doc);
102 if vOffsetTable(1).offset+75>l_doc_length then
103 l_pos_end1:=l_doc_length;
104 else
105 -- find the space after 75 characters after the hit
106 l_pos_end1:=vOffsetTable(1).offset+75;
107 end if;
108 --
109 l_text:=dbms_lob.substr(l_doc,(l_pos_end1-l_pos_start1),l_pos_start1);
110 --
111 vTeaserLine:=vTeaserLine||substr(l_text,1,(vOffsetTable(1).offset-l_pos_start1))||IRC_DOC_UTIL.MARKUP_START_TAG
112 ||substr(l_text,(vOffsetTable(1).offset-l_pos_start1+1),vOffsetTable(1).length)||IRC_DOC_UTIL.MARKUP_END_TAG;
113 --
114 -- look to see if the second tag is in the same chunk
115 --
116 vCounter:=2;
117 if vOffsetTable.COUNT>1 AND vOffsetTable(2).offset<l_pos_end1 then
118 --loop through the text highlighting it
119 WHILE vCounter<=vOffsetTable.COUNT LOOP
120 if(vOffsetTable(vCounter).offset>=l_pos_end1) then
121 exit;
122 end if;
123 vTeaserLine:=vTeaserLine||substr(l_text,(vOffsetTable(vCounter-1).offset+vOffsetTable(vCounter-1).length-l_pos_start1+1)
124 ,vOffsetTable(vCounter).offset-vOffsetTable(vCounter-1).offset-vOffsetTable(vCounter-1).length)||IRC_DOC_UTIL.MARKUP_START_TAG
125 ||substr(l_text,(vOffsetTable(vCounter).offset-l_pos_start1+1),vOffsetTable(vCounter).length)||IRC_DOC_UTIL.MARKUP_END_TAG;
126 vCounter:=vCounter+1;
127 END LOOP;
128 end if;
129 vTeaserLine:=vTeaserLine||substr(l_text,(vOffsetTable(vCounter-1).offset+vOffsetTable(vCounter-1).length-l_pos_start1+1))||g_EndConcatenator;
130 -- now look for the second teaser chunk
131 --
132 if (vOffsetTable.COUNT>=vCounter) then
133 --
134 if vOffsetTable(vCounter).offset-vOffsetTable(1).offset<=150 then
135 l_pos_start2:=vOffsetTable(1).offset+75;
136 vTeaserLine:=substr(vTeaserLine,1,length(vTeaserLine)-4);
137 else
138 --find the next space after 75 characters before the hit
139 l_pos_start2:=vOffsetTable(vCounter).offset-75;
140 vTeaserLine:=vTeaserLine||g_StartConcatenator;
141 end if;
142 --
143 -- find the end position
144 --
145 if vOffsetTable(vCounter).offset+75>l_doc_length then
146 l_pos_end2:=l_doc_length;
147 else
148 -- find the space after 75 characters after the hit
149 l_pos_end2:=vOffsetTable(vCounter).offset+75;
150 end if;
151 l_text:=dbms_lob.substr(l_doc,(l_pos_end2-l_pos_start2),l_pos_start2);
152 --
153 vTeaserLine:=vTeaserLine||substr(l_text,1,(vOffsetTable(vCounter).offset-l_pos_start2))||IRC_DOC_UTIL.MARKUP_START_TAG
154 ||substr(l_text,(vOffsetTable(vCounter).offset-l_pos_start2+1),vOffsetTable(vCounter).length)||IRC_DOC_UTIL.MARKUP_END_TAG;
155 --
156 -- look to see if there are more tags in the same chunk
157 --
158 vCounter:=vCounter+1;
159 if vOffsetTable.COUNT>=vCounter AND vOffsetTable(vCounter).offset<l_pos_end2 then
160 --loop through the text highlighting it
161 WHILE vCounter<=vOffsetTable.COUNT LOOP
162 if(vOffsetTable(vCounter).offset>=l_pos_end2) then
163 exit;
164 end if;
165 vTeaserLine:=vTeaserLine||substr(l_text,(vOffsetTable(vCounter-1).offset+vOffsetTable(vCounter-1).length-l_pos_start2+1)
166 ,vOffsetTable(vCounter).offset-vOffsetTable(vCounter-1).offset-vOffsetTable(vCounter-1).length)||IRC_DOC_UTIL.MARKUP_START_TAG
167 ||substr(l_text,(vOffsetTable(vCounter).offset-l_pos_start2+1),vOffsetTable(vCounter).length)||IRC_DOC_UTIL.MARKUP_END_TAG;
168 vCounter:=vCounter+1;
169 END LOOP;
170 end if;
171 vTeaserLine:=vTeaserLine||substr(l_text,(vOffsetTable(vCounter-1).offset+vOffsetTable(vCounter-1).length-l_pos_start2+1))||g_EndConcatenator;
172 end if;
173 --
174 -- Strip out tab characters and carriage returns
175 --
176 RETURN REPLACE(REPLACE(vTeaserLine,fnd_global.local_chr(13),' '),fnd_global.local_chr(10),' ');
177 --
178 END getContentTeaser;
179 END IRC_DOC_UTIL;
[Home] [Help]