1 Package Body HR_ADI_DOCUMENT_API as
2 /* $Header: hrlobapi.pkb 115.3 2004/03/31 06:21:37 menderby noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'HR_ADI_DOCUMENT_API.';
7
8 -- ----------------------------------------------------------------------------
9 -- |----------------------------< CREATE_DOCUMENT >---------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure CREATE_DOCUMENT
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_mime_type in varchar2
16 ,p_file_name in varchar2
17 ,p_type in varchar2
18 ,p_file_id out nocopy number
19 ) is
20 --
21 -- Declare cursors and local variables
22 --
23 l_proc varchar2(72) := g_package||'CREATE_DOCUMENT';
24 l_effective_date date;
25 l_file_id number(15);
26 begin
27 hr_utility.set_location('Entering:'|| l_proc, 10);
28 --
29 -- Issue a savepoint
30 --
31 savepoint CREATE_DOCUMENT;
32 --
33 -- Truncate the time portion from all IN date parameters
34 --
35 l_effective_date := TRUNC(p_effective_date);
36 --
37 -- Process Logic
38 --
39 hr_adi_lob_ins.ins
40 (p_effective_date => l_effective_date
41 ,p_file_content_type => p_mime_type
42 ,p_file_name => p_file_name
43 ,p_file_type => p_type
44 ,p_file_id => l_file_id
45 );
46 --
47 -- When in validation only mode raise the Validate_Enabled exception
48 --
49 if p_validate then
50 raise hr_api.validate_enabled;
51 end if;
52 --
53 -- Set all output arguments
54 --
55 p_file_id := l_file_id;
56 hr_utility.set_location(' Leaving:'||l_proc, 70);
57 exception
58 when hr_api.validate_enabled then
59 --
60 -- As the Validate_Enabled exception has been raised
61 -- we must rollback to the savepoint
62 --
63 rollback to CREATE_DOCUMENT;
64 --
65 -- Only set output warning arguments
66 -- (Any key or derived arguments must be set to null
67 -- when validation only mode is being used.)
68 --
69 p_file_id := null;
70 hr_utility.set_location(' Leaving:'||l_proc, 80);
71 when others then
72 --
73 -- A validation or unexpected error has occured
74 --
75 rollback to CREATE_DOCUMENT;
76 --
77 -- Reset IN OUT parameters and set OUT parameters
78 --
79 p_file_id := null;
80
81 hr_utility.set_location(' Leaving:'||l_proc, 90);
82 raise;
83 end CREATE_DOCUMENT;
84 --
85 --
86 -- ----------------------------------------------------------------------------
87 -- |----------------------------< UPDATE_DOCUMENT >---------------------------|
88 -- ----------------------------------------------------------------------------
89 --
90 procedure UPDATE_DOCUMENT
91 (p_validate in boolean default false
92 ,p_effective_date in date
93 ,p_file_id in number
94 ,p_mime_type in varchar2
95 ,p_file_name in varchar2
96 ) is
97 --
98 -- Declare cursors and local variables
99 --
103 hr_utility.set_location('Entering:'|| l_proc, 10);
100 l_proc varchar2(72) := g_package||'UPDATE_DOCUMENT';
101 l_effective_date date;
102 begin
104 --
105 -- Issue a savepoint
106 --
107 savepoint UPDATE_DOCUMENT;
108 --
109 -- Truncate the time portion from all IN date parameters
110 --
111 l_effective_date := TRUNC(p_effective_date);
112
113 --
114 -- Process Logic
115 --
116 hr_adi_lob_upd.upd
117 (p_effective_date => l_effective_date
118 ,p_file_id => p_file_id
119 ,p_file_content_type => p_mime_type
120 ,p_file_name => p_file_name
121 );
122 --
123 -- When in validation only mode raise the Validate_Enabled exception
124 --
125 if p_validate then
126 raise hr_api.validate_enabled;
127 end if;
128 --
129 -- Set all output arguments
130 --
131 exception
132 when hr_api.validate_enabled then
133 --
134 -- As the Validate_Enabled exception has been raised
135 -- we must rollback to the savepoint
136 --
137 rollback to UPDATE_DOCUMENT;
138 --
139 -- Only set output warning arguments
140 -- (Any key or derived arguments must be set to null
141 -- when validation only mode is being used.)
142 --
143 hr_utility.set_location(' Leaving:'||l_proc, 80);
144 when others then
145 --
146 -- A validation or unexpected error has occured
147 --
148 rollback to UPDATE_DOCUMENT;
149 hr_utility.set_location(' Leaving:'||l_proc, 90);
150 raise;
151 end UPDATE_DOCUMENT;
152 --
153 --
154 -- ----------------------------------------------------------------------------
155 -- |----------------------------< DELETE_DOCUMENT >---------------------------|
156 -- ----------------------------------------------------------------------------
157 --
158 procedure DELETE_DOCUMENT
159 (p_validate in boolean default false
160 ,p_file_id in number
161 ) is
162 --
163 -- Declare cursors and local variables
164 --
165 l_proc varchar2(72) := g_package||'DELETE_DOCUMENT';
166 begin
167 hr_utility.set_location('Entering:'|| l_proc, 10);
168 --
169 -- Issue a savepoint
170 --
171 savepoint DELETE_DOCUMENT;
172
173 --
174 -- Process Logic
175 --
176 hr_adi_lob_del.del
177 (p_file_id => p_file_id
178 );
179 --
180 -- When in validation only mode raise the Validate_Enabled exception
181 --
182 if p_validate then
183 raise hr_api.validate_enabled;
184 end if;
185 hr_utility.set_location(' Leaving:'||l_proc, 70);
186 exception
187 when hr_api.validate_enabled then
188 --
189 -- As the Validate_Enabled exception has been raised
190 -- we must rollback to the savepoint
191 --
192 rollback to DELETE_DOCUMENT;
193 hr_utility.set_location(' Leaving:'||l_proc, 80);
194 when others then
195 --
196 -- A validation or unexpected error has occured
197 --
198 rollback to DELETE_DOCUMENT;
199 hr_utility.set_location(' Leaving:'||l_proc, 90);
200 raise;
201 end DELETE_DOCUMENT;
202
203 -- ----------------------------------------------------------------------------
204 -- |---------------------------< DOCUMENT_TO_TEXT >---------------------------|
205 -- ----------------------------------------------------------------------------
206
207 -- function to convert a specified binary file in the fnd_lobs
208 -- table to be converted into a clob
209 --
210 function document_to_text(p_file_id in varchar2, p_text_or_html varchar2) return clob is
211 --
212 l_clob clob;
213 --
214 cursor csr_file_exists(p_file_id varchar2) is
215 select rowid
216 from fnd_lobs
217 where file_id = p_file_id;
218 --
219 l_rowid rowid;
220 text boolean;
221 begin
222 -- First check that document exists.
223 open csr_file_exists(p_file_id);
224 fetch csr_file_exists into l_rowid;
225 if (csr_file_exists%found) then
226 if (upper(p_text_or_html)='TEXT') then
227 text := true;
228 else
229 text := false;
230 end if;
231 --
232 -- Convert document
233 --
234 -- bug 3544676
235 -- disabled due to schema naming used directly
236 -- search feature not implemented via UI
237 --
238 --ctx_doc.filter(index_name => 'APPLSYS.FND_LOBS_CTX',
239 -- textkey => p_file_id,
240 -- restab => l_clob,
241 -- plaintext => text);
242 end if;
243 close csr_file_exists;
244 return l_clob;
245 end;
246
247 -- ----------------------------------------------------------------------------
248 -- |----------------------------< SEARCH_FOR_TERM >---------------------------|
249 -- ----------------------------------------------------------------------------
250 --
251 -- Procedure to search for term in binary files stored in FND_LOBS
252 -- the return value is a comma delimited string of the file_ids of the files
253 -- which contain the search term.
254 --
255 -- If no file type is specified a wildcard value is assumed.
256 --
257 function search_for_term(p_search_term in varchar2,
258 p_document_type in varchar2 default null)
259 return varchar2 is
260 --
261 TYPE file_id IS TABLE OF fnd_lobs.file_id%type index by binary_integer;
262 l_file_id file_id;
263
267 from fnd_lobs
264 -- Cursors
265 cursor csr_get_files(p_file_type varchar2) is
266 select file_id
268 where program_name = 'HRMS_ADI'
269 and program_tag = upper(nvl(p_file_type, program_tag));
270
271 -- Local variables
272 l_rowcount number;
273 l_foundlist varchar2(1000);
274 l_clob clob;
275 --
276 begin
277
278 -- first find files relating to specified type
279 open csr_get_files(p_document_type);
280 fetch csr_get_files bulk collect into l_file_id;
281 l_rowcount := csr_get_files%rowcount;
282 close csr_get_files;
283
284 -- for each file of the designated type, convert the binary file into
285 -- plain text, then search for term.
286 for i in 1..l_rowcount loop
287 l_clob := hr_adi_document_api.document_to_text(l_file_id(i), 'text');
288 if (dbms_lob.instr(l_clob, p_search_term, 1, 1) > 0) then
289 l_foundlist := l_foundlist || l_file_id(i) || ', ';
290 end if;
291 end loop;
292
293 -- trim any trailing comma and space from l_found_list
294 if (length(l_foundlist)>0) then
295 l_foundlist := substr(l_foundlist, 0, (length(l_foundlist)-2));
296 end if;
297
298 return l_foundlist;
299 end;
300
301 end HR_ADI_DOCUMENT_API;
[Home] [Help]