[Home] [Help]
PACKAGE BODY: APPS.IBC_UTILITIES_GRP
Source
1 Package Body IBC_UTILITIES_GRP as
2 /* $Header: ibcgutlb.pls 115.2 2003/08/21 23:12:48 enunez noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBC_UTITILIES_GRP';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ibcgutlb.pls';
6
7 /***********************************************************************************
8 *************************** Private Procedures ************************************
9 ***********************************************************************************/
10
11 FUNCTION get_mime_type(p_file_type IN VARCHAR2) RETURN VARCHAR2
12 IS
13 l_sc_position NUMBER;
14 l_result VARCHAR2(256);
15 BEGIN
16
17 l_sc_position := INSTR(p_file_type, ';');
18 IF l_sc_position > 0 THEN
19 l_result := UPPER(SUBSTR(p_file_type, 1, l_sc_position - 1));
20 ELSE
21 l_result := UPPER(p_file_type);
22 END IF;
23
24 RETURN l_result;
25 END get_mime_type;
26
27
28 /***********************************************************************************
29 *************************** Public Procedures *************************************
30 ***********************************************************************************/
31
32 -- --------------------------------------------------------------
33 -- Get_Rendition_File_Id
34 --
35 -- Valid content item id or citem version id must be given. If
36 -- version id is given, the rendition returned will be for that
37 -- particular version. If version id is not given and valid
38 -- content item id is given, the rendition returned will be for
39 -- the live version.
40 --
41 -- --------------------------------------------------------------
42 PROCEDURE Get_Rendition_File_Id (
43 p_api_version IN NUMBER DEFAULT 1.0,
44 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false,
45 p_content_item_id IN NUMBER DEFAULT NULL,
46 p_citem_ver_id IN NUMBER DEFAULT NULL,
47 p_language IN VARCHAR2 DEFAULT NULL,
48 p_mime_type IN VARCHAR2,
49 x_file_id OUT NOCOPY NUMBER,
50 x_return_status OUT NOCOPY VARCHAR2,
51 x_msg_count OUT NOCOPY NUMBER,
52 x_msg_data OUT NOCOPY VARCHAR2
53 ) AS
54 --******** local variable for standards **********
55 l_api_name CONSTANT VARCHAR2(40) := 'get_rendition_file_id';
56 l_api_version CONSTANT NUMBER := 1.0;
57 --
58 l_citem_ver_id NUMBER;
59 l_language VARCHAR2(30);
60 l_mime_type VARCHAR2(80);
61 l_file_id NUMBER;
62 --
63 CURSOR Get_file_Id(p_civid IN NUMBER,
64 p_language IN VARCHAR2,
65 p_mime_type IN VARCHAR2)
66 IS
67 SELECT file_id
68 FROM IBC_RENDITIONS
69 WHERE citem_version_id = p_civid
70 AND language = p_language
71 AND mime_type = p_mime_type;
72
73 CURSOR c_live_version(p_content_item_id IN NUMBER)
74 IS
75 SELECT live_citem_version_id
76 FROM IBC_CONTENT_ITEMS
77 WHERE content_item_id = p_content_item_id;
78
79 BEGIN
80 -- ******* Standard Begins ********
81 -- Standard call to check for call compatibility.
82 IF NOT FND_API.Compatible_API_Call (
83 l_api_version,
84 p_api_version,
85 l_api_name,
86 G_PKG_NAME)
87 THEN
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90 -- Initialize message list if p_init_msg_list is set to TRUE.
91 IF FND_API.to_Boolean( p_init_msg_list )
92 THEN
93 FND_MSG_PUB.initialize;
94 END IF;
95
96 -- Initialize API return status to success
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98 --******************* Real Logic Start *********************
99
100 IF p_language IS NULL THEN
101 l_language := USERENV('LANG');
102 ELSE
103 l_language := p_language;
104 END IF;
105
106 IF p_citem_ver_id IS NULL THEN
107 OPEN c_live_version(p_content_item_id);
108 FETCH c_live_version INTO l_citem_ver_id;
109 CLOSE c_live_version;
110 ELSE
111 l_citem_ver_id := p_citem_ver_id;
112 END IF;
113
114 IF p_mime_type IS NULL THEN
115 SELECT default_rendition_mime_type
116 INTO l_mime_type
117 FROM ibc_citem_versions_tl
118 WHERE citem_version_id = l_citem_ver_id
119 AND language = l_language;
120 ELSE
121 l_mime_type := p_mime_type;
122 END IF;
123
124 -- checking version id
125 IF l_citem_ver_id IS NULL OR
126 IBC_VALIDATE_PVT.isValidCitemVer(l_citem_ver_id) = FND_API.g_false OR
127 (p_content_item_id IS NOT NULL AND
128 IBC_VALIDATE_PVT.isValidCitemVerForCitem(p_content_item_id,l_citem_ver_id) = FND_API.g_false)
129 THEN
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 FND_MESSAGE.Set_Name('IBC', 'BAD_INPUT_VALUE');
132 FND_MESSAGE.Set_Token('INPUT', 'p_content_item_id/p_citem_ver_id', FALSE);
133 FND_MSG_PUB.ADD;
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136
137 IF IBC_VALIDATE_PVT.isvalidlanguage(l_language) = FND_API.g_false THEN
138 x_return_status := FND_API.G_RET_STS_ERROR;
139 FND_MESSAGE.Set_Name('IBC', 'BAD_INPUT_VALUE');
140 FND_MESSAGE.Set_Token('INPUT', 'p_language', FALSE);
141 FND_MSG_PUB.ADD;
142 RAISE FND_API.G_EXC_ERROR;
143 END IF;
144
145 OPEN Get_File_Id(l_citem_ver_id, l_language, l_mime_type);
146 FETCH Get_File_Id INTO l_file_id;
147 -- check if default style sheet exists
148 IF Get_File_Id%NOTFOUND THEN
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 FND_MESSAGE.Set_Name('IBC', 'BAD_INPUT_VALUE');
151 FND_MESSAGE.Set_Token('INPUT', 'p_language/p_mime_type', FALSE);
152 FND_MSG_PUB.ADD;
153 CLOSE Get_File_id;
154 RAISE FND_API.G_EXC_ERROR;
155 END IF;
156 CLOSE Get_File_Id;
157
158 x_file_id := l_file_id;
159
160 --******************* Real Logic End ***********************
161
162 -- Standard call to get message count and if count=1, get the message
163 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
164 p_data => x_msg_data);
165 EXCEPTION
166 WHEN FND_API.G_EXC_ERROR THEN
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
169 p_data => x_msg_data);
170 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
173 p_data => x_msg_data);
174 WHEN OTHERS THEN
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
177 THEN
178 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
179 END IF;
180 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
181 p_data => x_msg_data);
182 END Get_Rendition_File_Id;
183
184 -- --------------------------------------------------------------
185 -- Get_accessible_content_items
186 --
187 -- --------------------------------------------------------------
188 PROCEDURE Get_Accessible_Content_Items (
189 p_api_version IN NUMBER,
190 p_init_msg_list IN VARCHAR2,
191 p_user_id IN NUMBER,
192 p_language IN VARCHAR2,
193 p_permission_code IN VARCHAR2,
194 p_directory_node_id IN NUMBER,
195 p_path_pattern IN VARCHAR2,
196 p_include_subdirs IN VARCHAR2,
197 x_citem_ids OUT NOCOPY JTF_NUMBER_TABLE,
198 x_citem_names OUT NOCOPY JTF_VARCHAR2_TABLE_100,
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_msg_count OUT NOCOPY NUMBER,
201 x_msg_data OUT NOCOPY VARCHAR2
202 )
203 IS
204
205 CURSOR c_dirpath(p_directory_node_id NUMBER)
206 IS SELECT directory_path
207 FROM ibc_directory_nodes_b
208 WHERE directory_node_id = p_directory_node_id;
209
210 CURSOR c_items(p_language VARCHAR2,
211 p_security_flag VARCHAR2,
212 p_user_id NUMBER,
213 p_permission_code VARCHAR2,
214 p_path_pattern VARCHAR2)
215 IS
216 SELECT content_item_id
217 FROM ibc_citem_permissions_v citems,
218 ibc_directory_nodes_b dirnodes
219 WHERE citems.permission_code IN ('ALL_ALLOWED', p_permission_code)
220 AND citems.user_id = p_user_id
221 AND citems.security_flag = p_security_flag
222 AND citems.directory_node_id = dirnodes.directory_node_id
223 AND dirnodes.directory_path LIKE NVL(p_path_pattern, '%')
224 AND dirnodes.node_type = 'WD';
225
226 CURSOR c_item_name(p_content_item_id NUMBER,
227 p_language VARCHAR2)
228 IS
229 SELECT MAX(civtl.content_item_name)
230 FROM ibc_citem_versions_b civb,
231 ibc_citem_versions_tl civtl
232 WHERE civb.content_item_id = p_content_item_id
233 AND civb.citem_version_id = civtl.citem_version_id
234 AND language = p_language;
235
236 --******** local variable for standards **********
237 l_api_name CONSTANT VARCHAR2(40) := 'Get_Accessible_Content_Items';
238 l_api_version CONSTANT NUMBER := 1.0;
239 --
240 l_user_id NUMBER;
241 l_dirpath VARCHAR2(3000);
242 l_security_flag VARCHAR2(30);
243
244 TYPE t_num_tbl IS TABLE OF NUMBER
245 INDEX BY BINARY_INTEGER;
246
247 l_result t_num_tbl;
248 l_count NUMBER;
249
250 BEGIN
251 -- ******* Standard Begins ********
252 -- Standard call to check for call compatibility.
253 IF NOT FND_API.Compatible_API_Call (
254 l_api_version,
255 p_api_version,
256 l_api_name,
257 G_PKG_NAME)
258 THEN
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 END IF;
261 -- Initialize message list if p_init_msg_list is set to TRUE.
262 IF FND_API.to_Boolean( p_init_msg_list )
263 THEN
264 FND_MSG_PUB.initialize;
265 END IF;
266
267 -- Initialize API return status to success
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269 --******************* Real Logic Start *********************
270
271 IF p_user_id IS NOT NULL THEN
272 l_user_id := p_user_id;
273 ELSE
274 l_user_id := FND_GLOBAL.user_id;
275 END IF;
276
277 l_security_flag := Fnd_Profile.Value_specific('IBC_USE_ACCESS_CONTROL',-999,-999,-999);
278
279 IF p_directory_node_id IS NOT NULL THEN
280 OPEN c_dirpath(p_directory_node_id);
281 FETCH c_dirpath INTO l_dirpath;
282 IF c_dirpath%NOTFOUND THEN
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 FND_MESSAGE.Set_Name('IBC', 'BAD_INPUT_VALUE');
285 FND_MESSAGE.Set_Token('INPUT', 'p_content_item_id', FALSE);
286 FND_MSG_PUB.ADD;
287 RAISE FND_API.G_EXC_ERROR;
288 END IF;
289 CLOSE c_dirpath;
290 ELSE
291 l_dirpath := p_path_pattern;
292 END IF;
293
294 IF p_include_subdirs = FND_API.g_true THEN
295 l_dirpath := l_dirpath || '%';
296 END IF;
297
298 l_count := 0;
299 FOR r_items IN c_items(p_language => NVL(p_language, USERENV('lang')),
300 p_security_flag => l_security_flag,
301 p_user_id => l_user_id,
302 p_permission_code => p_permission_code,
303 p_path_pattern => l_dirpath)
304 LOOP
305 l_count := l_count + 1;
306 l_result(l_count) := r_items.content_item_id;
307 END LOOP;
308
309 IF l_count > 0 THEN
310 x_citem_ids := JTF_NUMBER_TABLE();
311 x_citem_names := JTF_VARCHAR2_TABLE_100();
312 x_citem_ids.extend(l_count);
313 x_citem_names.extend(l_count);
314 FOR I IN 1..l_count LOOP
315 x_citem_ids(I) := l_result(I);
316 OPEN c_item_name(l_result(I), NVL(p_language, USERENV('lang')));
317 FETCH c_item_name INTO x_citem_names(I);
318 CLOSE c_item_name;
319 END LOOP;
320 END IF;
321
322 --******************* Real Logic End ***********************
323 -- Standard call to get message count and if count=1, get the message
324 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
325 p_data => x_msg_data);
326 EXCEPTION
327 WHEN FND_API.G_EXC_ERROR THEN
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
330 p_data => x_msg_data);
331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
334 p_data => x_msg_data);
335 WHEN OTHERS THEN
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
338 THEN
339 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
340 END IF;
341 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
342 p_data => x_msg_data);
343 END Get_Accessible_Content_Items;
344
345 -- --------------------------------------------------------------
346 -- Get_citem_stylesheets
347 --
348 -- --------------------------------------------------------------
349 PROCEDURE Get_Citem_Stylesheets (
350 p_api_version IN NUMBER DEFAULT 1.0,
351 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false,
352 p_content_item_id IN NUMBER,
353 p_language IN VARCHAR2 DEFAULT NULL,
354 x_citem_ids OUT NOCOPY JTF_NUMBER_TABLE,
355 x_citem_names OUT NOCOPY JTF_VARCHAR2_TABLE_100,
356 x_return_status OUT NOCOPY VARCHAR2,
357 x_msg_count OUT NOCOPY NUMBER,
358 x_msg_data OUT NOCOPY VARCHAR2
359 )
360 IS
361 CURSOR c_items(p_content_item_id NUMBER)
362 IS
363 SELECT stlshts.content_item_id
364 FROM ibc_content_items citems,
365 ibc_stylesheets stlshts
366 WHERE citems.content_type_code = stlshts.content_type_code
367 AND citems.content_item_id = p_content_item_id;
368
369 CURSOR c_item_name(p_content_item_id NUMBER,
370 p_language VARCHAR2)
371 IS
372 SELECT MAX(civtl.content_item_name)
373 FROM ibc_citem_versions_b civb,
374 ibc_citem_versions_tl civtl
375 WHERE civb.content_item_id = p_content_item_id
376 AND civb.citem_version_id = civtl.citem_version_id
377 AND language = p_language;
378
379 --******** local variable for standards **********
380 l_api_name CONSTANT VARCHAR2(40) := 'Get_Citem_Stylesheets';
381 l_api_version CONSTANT NUMBER := 1.0;
382 --
383 l_user_id NUMBER;
384 l_dirpath VARCHAR2(3000);
385 l_security_flag VARCHAR2(30);
386
387 TYPE t_num_tbl IS TABLE OF NUMBER
388 INDEX BY BINARY_INTEGER;
389
390 l_result t_num_tbl;
391 l_count NUMBER;
392
393 BEGIN
394 -- ******* Standard Begins ********
395 -- Standard call to check for call compatibility.
396 IF NOT FND_API.Compatible_API_Call (
397 l_api_version,
398 p_api_version,
399 l_api_name,
400 G_PKG_NAME)
401 THEN
402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
403 END IF;
404 -- Initialize message list if p_init_msg_list is set to TRUE.
405 IF FND_API.to_Boolean( p_init_msg_list )
406 THEN
407 FND_MSG_PUB.initialize;
408 END IF;
409
410 -- Initialize API return status to success
411 x_return_status := FND_API.G_RET_STS_SUCCESS;
412 --******************* Real Logic Start *********************
413
414 l_count := 0;
415 FOR r_items IN c_items(p_content_item_id)
416 LOOP
417 l_count := l_count + 1;
418 l_result(l_count) := r_items.content_item_id;
419 END LOOP;
420
421 IF l_count > 0 THEN
422 x_citem_ids := JTF_NUMBER_TABLE();
423 x_citem_names := JTF_VARCHAR2_TABLE_100();
424 x_citem_ids.extend(l_count);
425 x_citem_names.extend(l_count);
426 FOR I IN 1..l_count LOOP
427 x_citem_ids(I) := l_result(I);
428 OPEN c_item_name(l_result(I), NVL(p_language, USERENV('lang')));
429 FETCH c_item_name INTO x_citem_names(I);
430 CLOSE c_item_name;
431 END LOOP;
432 END IF;
433
434 --******************* Real Logic End ***********************
435 -- Standard call to get message count and if count=1, get the message
436 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
437 p_data => x_msg_data);
438 EXCEPTION
439 WHEN FND_API.G_EXC_ERROR THEN
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
442 p_data => x_msg_data);
443 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
446 p_data => x_msg_data);
447 WHEN OTHERS THEN
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
450 THEN
451 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
452 END IF;
453 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
454 p_data => x_msg_data);
455 END Get_citem_Stylesheets;
456
457
458 END IBC_UTILITIES_GRP;