[Home] [Help]
PACKAGE BODY: APPS.IBC_CITEM_RUNTIME_PVT
Source
1 Package Body IBC_CITEM_RUNTIME_PVT as
2 /* $Header: ibcvcirb.pls 120.1.12000000.2 2007/06/21 05:23:36 rsatyava ship $ */
3
4
5 -- ******************************************************************************
6 -- ***** Validation APIs
7 -- ******************************************************************************
8
9 -- --------------------------------------------------------------
10 -- 1) Validates content item id being valid
11 -- 2) Validates content item is APPROVED
12 -- 3) Validates wd_restricted_flag is NOT TRUE
13 -- Return along the way:
14 -- * content type code
15 -- * item reference code
16 -- * live version id
17 -- --------------------------------------------------------------
18 PROCEDURE Validate_Citem (
19 p_init_msg_list IN VARCHAR2,
20 p_content_item_id IN NUMBER,
21 x_content_type_code OUT NOCOPY VARCHAR2,
22 x_item_reference_code OUT NOCOPY VARCHAR2,
23 x_live_citem_version_id OUT NOCOPY NUMBER,
24 x_encrypt_flag OUT NOCOPY VARCHAR2,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2
28 ) AS
29 l_wd_restricted_flag IBC_CONTENT_ITEMS.WD_RESTRICTED_FLAG%TYPE;
30 l_content_item_status IBC_CONTENT_ITEMS.CONTENT_ITEM_STATUS%TYPE;
31 l_parent_item_id NUMBER;
32 --
33 CURSOR Get_Citem IS
34 select CONTENT_TYPE_CODE, ITEM_REFERENCE_CODE, WD_RESTRICTED_FLAG, LIVE_CITEM_VERSION_ID,
35 CONTENT_ITEM_STATUS, ENCRYPT_FLAG, PARENT_ITEM_ID
36 from IBC_CONTENT_ITEMS
37 where content_item_id = p_content_item_id;
38 BEGIN
39 -- Initialize message list if p_init_msg_list is set to TRUE.
40 IF FND_API.to_Boolean( p_init_msg_list ) THEN
41 FND_MSG_PUB.initialize;
42 END IF;
43 -- Initialize API return status to success
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45
46 OPEN Get_Citem;
47 FETCH Get_Citem INTO x_content_type_code, x_item_reference_code,
48 l_wd_restricted_flag, x_live_citem_version_id,
49 l_content_item_status, x_encrypt_flag,
50 l_parent_item_id;
51 -- check if p_content_item_id is valid
52 IF Get_Citem%NOTFOUND THEN
53 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
54 FND_MESSAGE.Set_Name('IBC', 'INVALID_CITEM_ID');
55 FND_MESSAGE.Set_token('CITEM_ID', p_content_item_id);
56 FND_MSG_PUB.ADD;
57 END IF;
58 RAISE FND_API.G_EXC_ERROR;
59 END IF;
60 CLOSE Get_Citem;
61
62 -- check if content_item_status is APPROVED
63 IF (l_content_item_status IS NULL OR
64 l_content_item_status <> IBC_UTILITIES_PUB.G_STI_APPROVED) THEN
65 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
66 FND_MESSAGE.Set_Name('IBC', 'CITEM_NOT_PUBLISHED');
67 -- FND_MESSAGE.Set_token('CITEM_ID', p_content_item_id);
68 FND_MESSAGE.Set_token('CITEM_NAME',IBC_UTILITIES_PVT.get_citem_name(p_content_item_id));
69 FND_MSG_PUB.ADD;
70 END IF;
71 RAISE FND_API.G_EXC_ERROR;
72 END IF;
73
74 -- check if wd_restricted_flag is true
75 IF (l_wd_restricted_flag = FND_API.G_TRUE) THEN
76 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
77 FND_MESSAGE.Set_Name('IBC', 'CITEM_NOT_PUBLISHED');
78 -- FND_MESSAGE.Set_token('CITEM_ID', p_content_item_id);
79 FND_MESSAGE.Set_token('CITEM_NAME',IBC_UTILITIES_PVT.get_citem_name(p_content_item_id));
80 FND_MSG_PUB.ADD;
81 END IF;
82 RAISE FND_API.G_EXC_ERROR;
83 END IF;
84
85 -- check if parent_item_id is not null (DECIDED NOT REQUIRED)
86 -- if (p_check_parent_item_id = FND_API.G_TRUE AND
87 -- l_parent_item_id is NOT NULL) THEN
88 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
89 -- FND_MESSAGE.Set_Name('IBC', 'CITEM_IS_ONLY_COMPONENT');
90 -- FND_MESSAGE.Set_token('CITEM_ID', p_content_item_id);
91 -- FND_MSG_PUB.ADD;
92 -- END IF;
93 -- RAISE FND_API.G_EXC_ERROR;
94 -- END IF;
95
96 EXCEPTION
97 WHEN FND_API.G_EXC_ERROR THEN
98 x_return_status := FND_API.G_RET_STS_ERROR;
99 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
100 p_data => x_msg_data);
101 END Validate_Citem;
102
103
104 -- --------------------------------------------------------------
105 -- 1) Validates content item version start date if enforced by
106 -- profile option, IBC_ENFORCE_AVAILABLE_DATE
107 -- 2) Validates content item version end date if enforced by
108 -- profile option, IBC_ENFORCE_EXPIRATION_DATE
109 --
110 -- Return along the way:
111 -- * item version number
112 -- * item version start date
113 -- * item version end date
114 -- --------------------------------------------------------------
115 PROCEDURE Validate_Start_End_Date (
116 p_init_msg_list IN VARCHAR2,
117 p_content_item_id IN NUMBER,
118 p_citem_version_id IN NUMBER,
119 x_version_number OUT NOCOPY NUMBER,
120 x_start_date OUT NOCOPY DATE,
121 x_end_date OUT NOCOPY DATE,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2
125 ) AS
126 CURSOR Get_Citem_Version IS
127 select START_DATE, END_DATE, VERSION_NUMBER
128 from IBC_CITEM_VERSIONS_B
129 where CITEM_VERSION_ID = p_citem_version_id;
130 BEGIN
131 -- Initialize message list if p_init_msg_list is set to TRUE.
132 IF FND_API.to_Boolean( p_init_msg_list ) THEN
133 FND_MSG_PUB.initialize;
134 END IF;
135 -- Initialize API return status to success
136 x_return_status := FND_API.G_RET_STS_SUCCESS;
137
138 OPEN Get_Citem_Version;
139 FETCH Get_Citem_Version INTO x_start_date, x_end_date, x_version_number;
140 CLOSE Get_Citem_Version;
141
142 -- Check Profile if availabe date is enforced
143 IF (FND_PROFILE.Value('IBC_ENFORCE_AVAILABLE_DATE') IS NULL) OR
144 (FND_PROFILE.Value('IBC_ENFORCE_AVAILABLE_DATE') = 'Y') THEN
145 IF (NVL(x_start_date, SYSDATE) > SYSDATE) THEN
146 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
147 FND_MESSAGE.Set_Name('IBC', 'CITEM_NOT_YET_AVAILABLE');
148 FND_MESSAGE.Set_token('CITEM_ID', p_content_item_id);
149 FND_MESSAGE.Set_token('START_DATE', x_start_date);
150 FND_MSG_PUB.ADD;
151 END IF;
152 RAISE FND_API.G_EXC_ERROR;
153 END IF;
154 END IF;
155
156 -- Check Profile if expiration date is enforced
157 IF (FND_PROFILE.Value('IBC_ENFORCE_EXPIRATION_DATE') IS NULL) OR
158 (FND_PROFILE.Value('IBC_ENFORCE_EXPIRATION_DATE') = 'Y') THEN
159 IF (NVL(x_end_date, SYSDATE) < SYSDATE) THEN
160 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
161 FND_MESSAGE.Set_Name('IBC', 'CITEM_EXPIRED');
162 FND_MESSAGE.Set_token('CITEM_ID', p_content_item_id);
163 FND_MESSAGE.Set_token('END_DATE', x_end_date);
164 FND_MSG_PUB.ADD;
165 END IF;
166 RAISE FND_API.G_EXC_ERROR;
167 END IF;
168 END IF;
169
170 EXCEPTION
171 WHEN FND_API.G_EXC_ERROR THEN
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
174 p_data => x_msg_data);
175 END Validate_Start_End_Date;
176
177
178
179
180 -- ******************************************************************************
181 -- ***** Retrival APIs
182 -- ******************************************************************************
183
184 -- --------------------------------------------------------------
185 -- Get Content Item Meta data.
186 -- * If label is NOT NULL and there is not label-version mapping,
187 -- x_content_item_meta will be NULL and
188 -- x_item_found will be FALSE.
189 -- --------------------------------------------------------------
190 PROCEDURE Get_Citem_Meta (
191 p_init_msg_list IN VARCHAR2,
192 p_content_item_id IN NUMBER,
193 p_label_code IN VARCHAR2,
194 x_content_item_meta OUT NOCOPY IBC_CITEM_RUNTIME_PUB.CONTENT_ITEM_META_REC,
195 x_item_found OUT NOCOPY VARCHAR2,
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2
199 ) AS
200 l_live_citem_version_id NUMBER;
201 l_citem_version_id NUMBER;
202 l_default_mime_type IBC_CITEM_VERSIONS_TL.DEFAULT_RENDITION_MIME_TYPE%TYPE;
203 l_translation_status IBC_CITEM_VERSIONS_TL.CITEM_TRANSLATION_STATUS%TYPE;
204 --
205 -- // retrieve the labeled version id
206 CURSOR Get_Citem_Ver_By_Label IS
207 select citem_version_id
208 from IBC_CITEM_VERSION_LABELS
209 where label_code = p_label_code and
210 content_item_id = p_content_item_id;
211
212 -- // retrieve the session language version translation
213 CURSOR Get_Citem_Meta_Csr IS
214 select CITEM_TRANSLATION_STATUS, CONTENT_ITEM_NAME, DESCRIPTION, DEFAULT_RENDITION_MIME_TYPE,
215 ATTACHMENT_FILE_NAME, ATTACHMENT_FILE_ID
216 from IBC_CITEM_VERSIONS_TL
217 where citem_version_id = l_citem_version_id
218 and language = userenv('LANG');
219
220 -- // retrieve the based language version translation
221 CURSOR Get_Citem_Meta_BLang IS
222 select t.CONTENT_ITEM_NAME, t.DESCRIPTION, t.DEFAULT_RENDITION_MIME_TYPE,
223 t.ATTACHMENT_FILE_NAME, t.ATTACHMENT_FILE_ID
224 from IBC_CONTENT_ITEMS i, IBC_CITEM_VERSIONS_TL t
225 where i.CONTENT_ITEM_ID = p_content_item_id
226 and t.citem_version_id = l_citem_version_id
227 and t.language = i.BASE_LANGUAGE;
228
229 CURSOR Get_Rendition_Name IS
230 SELECT NVL(DESCRIPTION, MEANING)
231 FROM FND_LOOKUP_VALUES
232 WHERE LOOKUP_TYPE = IBC_UTILITIES_PVT.G_REND_LOOKUP_TYPE
233 AND LANGUAGE = userenv('LANG')
234 AND LOOKUP_CODE = l_default_mime_type;
235
236 BEGIN
237 -- Initialize message list if p_init_msg_list is set to TRUE.
238 IF FND_API.to_Boolean( p_init_msg_list ) THEN
239 FND_MSG_PUB.initialize;
240 END IF;
241 -- Initialize API return status to success
242 x_return_status := FND_API.G_RET_STS_SUCCESS;
243 x_item_found := FND_API.G_TRUE;
244
245 -- ******************** Real Logic *****************************
246 Validate_Citem (
247 p_init_msg_list => p_init_msg_list,
248 p_content_item_id => p_content_item_id,
249 x_content_type_code => x_content_item_meta.content_type_code,
250 x_item_reference_code => x_content_item_meta.item_reference_code,
251 x_live_citem_version_id => l_live_citem_version_id,
252 x_encrypt_flag => x_content_item_meta.encrypt_flag,
253 x_return_status => x_return_status,
254 x_msg_count => x_msg_count,
255 x_msg_data => x_msg_data
256 );
257 -- Content Item requested is not valid
258 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
259 RAISE FND_API.G_EXC_ERROR;
260 END IF;
261
262 x_content_item_meta.content_item_id := p_content_item_id;
263
264 -- Check if there is a label for this content item
265 IF (p_label_code is NULL) THEN
266 l_citem_version_id := l_live_citem_version_id;
267 ELSE
268 OPEN Get_Citem_Ver_By_Label;
269 FETCH Get_Citem_Ver_By_Label INTO l_citem_version_id;
270 -- Label doesn't exist for this content item id
271 IF (Get_Citem_Ver_By_Label%NOTFOUND) THEN
272 -- Validate Label
273 IF (Ibc_Validate_Pvt.isValidLabel(p_label_code) = FND_API.g_false) THEN
274 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
275 FND_MESSAGE.Set_Name('IBC', 'INVALID_LABEL_CODE');
276 FND_MESSAGE.Set_token('LABEL_CODE', p_label_code);
277 FND_MSG_PUB.ADD;
278 END IF;
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281 x_item_found := FND_API.G_FALSE;
282 x_content_item_meta := NULL;
283 return;
284 END IF;
285 CLOSE Get_Citem_Ver_By_Label;
286 END IF;
287
288 -- check start/end date
289 Validate_Start_End_Date (
290 p_init_msg_list => p_init_msg_list,
291 p_content_item_id => p_content_item_id,
292 p_citem_version_id => l_citem_version_id,
293 x_version_number => x_content_item_meta.version_number,
294 x_start_date => x_content_item_meta.available_date,
295 x_end_date => x_content_item_meta.expiration_date,
296 x_return_status => x_return_status,
297 x_msg_count => x_msg_count,
298 x_msg_data => x_msg_data
299 );
300 -- Start/End date not valid
301 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
302 RAISE FND_API.G_EXC_ERROR;
303 END IF;
304
305 -- // Retrieve content item data (session language)
306 OPEN Get_Citem_Meta_Csr;
307 FETCH Get_Citem_Meta_Csr INTO l_translation_status,
308 x_content_item_meta.content_item_name,
309 x_content_item_meta.description,
310 l_default_mime_type,
311 x_content_item_meta.attachment_file_name,
312 x_content_item_meta.attachment_file_id;
313 CLOSE Get_Citem_Meta_Csr;
314
315 -- // If translation status of session language is NOT APPROVED,
316 -- // retrieve the based language.
317 IF (l_translation_status <> 'APPROVED') THEN
318 OPEN Get_Citem_Meta_BLang;
319 FETCH Get_Citem_Meta_BLang INTO x_content_item_meta.content_item_name,
320 x_content_item_meta.description,
321 l_default_mime_type,
322 x_content_item_meta.attachment_file_name,
323 x_content_item_meta.attachment_file_id;
324 CLOSE Get_Citem_Meta_BLang;
325 END IF;
326
327 x_content_item_meta.default_mime_type := LOWER(l_default_mime_type);
328
329 IF (l_default_mime_type IS NOT NULL) THEN
330 OPEN Get_Rendition_Name;
331 FETCH Get_Rendition_Name INTO x_content_item_meta.default_rendition_name;
332 IF Get_Rendition_Name%NOTFOUND THEN
333 CLOSE Get_Rendition_Name;
334 l_default_mime_type := IBC_UTILITIES_PVT.G_REND_UNKNOWN_MIME;
335 OPEN Get_Rendition_Name;
336 FETCH Get_Rendition_Name INTO x_content_item_meta.default_rendition_name;
337 CLOSE Get_Rendition_Name;
338 ELSE
339 CLOSE Get_Rendition_Name;
340 END IF;
341 ELSE
342 x_content_item_meta.default_rendition_name := NULL;
343 END IF;
344
345 EXCEPTION
346 WHEN FND_API.G_EXC_ERROR THEN
347 x_return_status := FND_API.G_RET_STS_ERROR;
348 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
349 p_data => x_msg_data);
350 END Get_Citem_Meta;
351
352
353 -- --------------------------------------------------------------
354 -- Get Content Item in XML.
355 -- * If label is NOT NULL and there is not label-version mapping,
356 -- p_xml_clob_loc will be NULL and
357 -- x_num_levels_loaded will be -1
358 -- --------------------------------------------------------------
359 PROCEDURE Get_Citem_Xml (
360 p_init_msg_list IN VARCHAR2,
361 p_content_item_id IN NUMBER,
362 p_xml_clob_loc IN OUT NOCOPY CLOB,
363 p_num_levels IN NUMBER,
364 p_label_code IN VARCHAR2,
365 p_lang_code IN VARCHAR2,
366 p_validate_dates IN VARCHAR2,
367 x_num_levels_loaded OUT NOCOPY NUMBER,
368 x_return_status OUT NOCOPY VARCHAR2,
369 x_msg_count OUT NOCOPY NUMBER,
370 x_msg_data OUT NOCOPY VARCHAR2
371 ) AS
372 l_content_type_code IBC_CONTENT_ITEMS.CONTENT_TYPE_CODE%TYPE;
373 l_item_reference_code IBC_CONTENT_ITEMS.ITEM_REFERENCE_CODE%TYPE;
374 l_live_citem_version_id NUMBER;
375 l_citem_version_id NUMBER;
376 l_encrypt_flag VARCHAR2(1);
377
378 l_version_number NUMBER;
379 l_start_date DATE;
380 l_end_date DATE;
381
382 l_translation_status IBC_CITEM_VERSIONS_TL.CITEM_TRANSLATION_STATUS%TYPE;
383 l_lang_code VARCHAR2(4);
384
385 l_content_item_name IBC_CITEM_VERSIONS_TL.CONTENT_ITEM_NAME%TYPE;
386 l_description IBC_CITEM_VERSIONS_TL.DESCRIPTION%TYPE;
387 l_attachment_attribute_code IBC_CITEM_VERSIONS_TL.ATTACHMENT_ATTRIBUTE_CODE%TYPE;
388 l_attachment_file_id NUMBER;
389 l_attachment_file_name IBC_CITEM_VERSIONS_TL.ATTACHMENT_FILE_NAME%TYPE;
390 l_attribute_file_id NUMBER;
391 l_default_mime_type IBC_CITEM_VERSIONS_TL.DEFAULT_RENDITION_MIME_TYPE%TYPE;
392
393 l_comp_content_item_id NUMBER;
394 l_tmp_num_levels NUMBER;
395 l_has_component_items VARCHAR2(1) := FND_API.g_false;
396 l_max_num_levels_loaded NUMBER := 0;
397 --
398 -- // Retrieve label version
399 CURSOR Get_Citem_Ver_By_Label IS
400 select citem_version_id
401 from IBC_CITEM_VERSION_LABELS
402 where label_code = p_label_code and
403 content_item_id = p_content_item_id;
404
405 CURSOR Get_Citem_Version IS
406 select START_DATE, END_DATE, VERSION_NUMBER
407 from IBC_CITEM_VERSIONS_B
408 where CITEM_VERSION_ID = l_citem_version_id;
409
410 -- // retrieve citem version in the specified language
411 CURSOR Get_Citem_Meta_Csr IS
412 select CITEM_TRANSLATION_STATUS, ATTRIBUTE_FILE_ID, CONTENT_ITEM_NAME, DESCRIPTION, ATTACHMENT_ATTRIBUTE_CODE,
413 ATTACHMENT_FILE_ID, ATTACHMENT_FILE_NAME, DEFAULT_RENDITION_MIME_TYPE
414 from IBC_CITEM_VERSIONS_TL
415 where citem_version_id = l_citem_version_id
416 and language = nvl(p_lang_code, userenv('LANG'));
417
418 -- // retrieve the based language version translation
419 CURSOR Get_Citem_Meta_BLang IS
420 select i.BASE_LANGUAGE, t.ATTRIBUTE_FILE_ID, t.CONTENT_ITEM_NAME, t.DESCRIPTION, t.ATTACHMENT_ATTRIBUTE_CODE,
421 t.ATTACHMENT_FILE_ID, t.ATTACHMENT_FILE_NAME, t.DEFAULT_RENDITION_MIME_TYPE
422 from IBC_CONTENT_ITEMS i, IBC_CITEM_VERSIONS_TL t
423 where i.CONTENT_ITEM_ID = p_content_item_id
424 and t.citem_version_id = l_citem_version_id
425 and t.language = i.BASE_LANGUAGE;
426
427 CURSOR Get_Compound_Item_Ref IS
428 select r.ATTRIBUTE_TYPE_CODE, r.CONTENT_ITEM_ID, c.ENCRYPT_FLAG
429 from IBC_COMPOUND_RELATIONS r, IBC_CONTENT_ITEMS c
430 where r.CITEM_VERSION_ID = l_citem_version_id
431 and r.CONTENT_ITEM_ID = c.CONTENT_ITEM_ID
432 order by r.SORT_ORDER;
433
434 BEGIN
435
436 -- Initialize message list if p_init_msg_list is set to TRUE.
437 IF FND_API.to_Boolean( p_init_msg_list ) THEN
438 FND_MSG_PUB.initialize;
439 END IF;
440 -- Initialize API return status to success
441 x_return_status := FND_API.G_RET_STS_SUCCESS;
442 -- **************** Real Logic Starts *****************
443
444 Validate_Citem (
445 p_init_msg_list => p_init_msg_list,
446 p_content_item_id => p_content_item_id,
447 x_content_type_code => l_content_type_code,
448 x_item_reference_code => l_item_reference_code,
449 x_live_citem_version_id => l_live_citem_version_id,
450 x_encrypt_flag => l_encrypt_flag,
451 x_return_status => x_return_status,
452 x_msg_count => x_msg_count,
453 x_msg_data => x_msg_data
454 );
455 -- Content Item requested is not valid
456 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
457 RAISE FND_API.G_EXC_ERROR;
458 END IF;
459 -- Check if there is a label for this content item
460 IF (p_label_code is NULL) THEN
461 l_citem_version_id := l_live_citem_version_id;
462 ELSE
463 OPEN Get_Citem_Ver_By_Label;
464 FETCH Get_Citem_Ver_By_Label INTO l_citem_version_id;
465 -- Label doesn't exist for this content item id
466 IF (Get_Citem_Ver_By_Label%NOTFOUND) THEN
467 -- Validate Label
468 IF (Ibc_Validate_Pvt.isValidLabel(p_label_code) = FND_API.g_false) THEN
469 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
470 FND_MESSAGE.Set_Name('IBC', 'INVALID_LABEL_CODE');
471 FND_MESSAGE.Set_token('LABEL_CODE', p_label_code);
472 FND_MSG_PUB.ADD;
473 END IF;
474 RAISE FND_API.G_EXC_ERROR;
475 END IF;
476 -- Label is valid, but there is no mapping to this item, return NULL
477 p_xml_clob_loc := NULL;
478 x_num_levels_loaded := -1;
479 return;
480 END IF;
481 CLOSE Get_Citem_Ver_By_Label;
482 END IF;
483
484 -- This is preloading, DO NOT validate DATES
485 IF (p_validate_dates = FND_API.G_FALSE) THEN
486 OPEN Get_Citem_Version;
487 FETCH Get_Citem_Version INTO l_start_date,l_end_date,l_version_number;
488 CLOSE Get_Citem_Version;
489
490 -- Not preloading, NEED to validate DATES
491 ELSE
492 Validate_Start_End_Date (
493 p_init_msg_list => p_init_msg_list,
494 p_content_item_id => p_content_item_id,
495 p_citem_version_id => l_citem_version_id,
496 x_version_number => l_version_number,
497 x_start_date => l_start_date,
498 x_end_date => l_end_date,
499 x_return_status => x_return_status,
500 x_msg_count => x_msg_count,
501 x_msg_data => x_msg_data
502 );
503 -- Start/End date not valid
504 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
505 RAISE FND_API.G_EXC_ERROR;
506 END IF;
507 END IF;
508
509 -- // Retrieve Meta Data in specified language
510 l_lang_code := p_label_code;
511 OPEN Get_Citem_Meta_Csr;
512 FETCH Get_Citem_Meta_Csr INTO l_translation_status,
513 l_attribute_file_id, l_content_item_name, l_description,
514 l_attachment_attribute_code,
515 l_attachment_file_id,
516 l_attachment_file_name,
517 l_default_mime_type;
518 -- Not found, input LANGUAGE must be invalid (b/c version id already verified)
519 IF Get_Citem_Meta_Csr%NOTFOUND THEN
520 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
521 FND_MESSAGE.Set_Name('IBC', 'INVALID_LANG_CODE');
522 FND_MESSAGE.Set_token('LANG_CODE', p_lang_code);
523 FND_MSG_PUB.ADD;
524 END IF;
525 RAISE FND_API.G_EXC_ERROR;
526 END IF;
527 CLOSE Get_Citem_Meta_Csr;
528
529 -- // If translation status is not APPROVED, retrieve based language translation
530 IF (l_translation_status <> 'APPROVED') THEN
531 OPEN Get_Citem_Meta_BLang;
532 -- // bring in base language
533 FETCH Get_Citem_Meta_BLang INTO l_lang_code,
534 l_attribute_file_id, l_content_item_name, l_description,
535 l_attachment_attribute_code,
536 l_attachment_file_id,
537 l_attachment_file_name,
538 l_default_mime_type;
539 CLOSE Get_Citem_Meta_BLang;
540 END IF;
541
542 -- // Openning Tags (Root + Renditions + Name + Description + Attachment)
543 IBC_UTILITIES_PVT.Build_Citem_Open_Tags (
544 p_content_type_code => l_content_type_code
545 ,p_content_item_id => p_content_item_id
546 ,p_citem_version_id => l_citem_version_id
547 ,p_item_label => p_label_code
548 ,p_lang_code => l_lang_code -- Renditions based on language used to retrieve item
549 ,p_version_number => l_version_number
550 ,p_start_date => l_start_date
551 ,p_end_date => l_end_date
552 ,p_item_reference_code => l_item_reference_code
553 ,p_encrypt_flag => l_encrypt_flag -- parent item encrypt flag
554 ,p_content_item_name => l_content_item_name
555 ,p_description => l_description
556 ,p_attachment_attribute_code => l_attachment_attribute_code
557 ,p_attachment_file_id => l_attachment_file_id
558 ,p_attachment_file_name => l_attachment_file_name
559 ,p_default_mime_type => l_default_mime_type
560 ,p_is_preview => FND_API.G_FALSE
561 ,p_xml_clob_loc => p_xml_clob_loc
562 );
563 -- User defined primitive attributes
564 IBC_UTILITIES_PVT.Build_Attribute_Bundle (
565 p_file_id => l_attribute_file_id,
566 p_xml_clob_loc => p_xml_clob_loc
567 );
568
569 -- Compound Item attributes
570 IF (p_num_levels IS NULL OR p_num_levels > 0) THEN
571
572 -- compound items expanded
573 FOR compound_item_rec IN Get_Compound_Item_Ref LOOP
574 l_has_component_items := FND_API.g_true;
575 l_comp_content_item_id := compound_item_rec.content_item_id;
576
577 IBC_UTILITIES_PVT.Build_Compound_Item_Open_Tag (
578 p_attribute_type_code => compound_item_rec.attribute_type_code,
579 p_content_item_id => l_comp_content_item_id,
580 p_item_label => p_label_code,
581 p_encrypt_flag => compound_item_rec.encrypt_flag,
582 p_xml_clob_loc => p_xml_clob_loc
583 );
584
585 l_tmp_num_levels := p_num_levels - 1;
586
587 Get_Citem_Xml (
588 p_init_msg_list => p_init_msg_list,
589 p_content_item_id => l_comp_content_item_id,
590 p_xml_clob_loc => p_xml_clob_loc,
591 p_num_levels => l_tmp_num_levels,
592 p_label_code => p_label_code,
593 p_lang_code => p_lang_code, -- components retrieved with requested language
594 p_validate_dates => p_validate_dates,
595 x_num_levels_loaded => x_num_levels_loaded,
596 x_return_status => x_return_status,
597 x_msg_count => x_msg_count,
598 x_msg_data => x_msg_data
599 );
600 -- nested compounded items not valid
601 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
602 RAISE FND_API.G_EXC_ERROR;
603 END IF;
604
605 -- check if component item is matched with label
606 IF (p_xml_clob_loc is NULL) THEN
607 x_num_levels_loaded := -1;
608 return;
609 END IF;
610
611 IBC_UTILITIES_PVT.Build_Close_Tag (
612 compound_item_rec.attribute_type_code,
613 p_xml_clob_loc
614 );
615
616 -- update max num levels
617 IF (x_num_levels_loaded > l_max_num_levels_loaded) THEN
618 l_max_num_levels_loaded := x_num_levels_loaded;
619 END IF;
620 END LOOP;
621
622 -- set number of levels loaded
623 IF (l_has_component_items = FND_API.g_false) THEN
624 x_num_levels_loaded := 0;
625 ELSE
626 x_num_levels_loaded := l_max_num_levels_loaded + 1;
627 END IF;
628
629 -- p_num_levels = 0
630 ELSE
631 -- compound item references
632 IBC_UTILITIES_PVT.Build_Compound_Item_References (
633 p_citem_version_id => l_citem_version_id,
634 p_item_label => p_label_code,
635 p_xml_clob_loc => p_xml_clob_loc
636 );
637 -- set number of levels loaded
638 x_num_levels_loaded := 0;
639
640 END IF;
641
642 -- Close Root Tag
643 IBC_UTILITIES_PVT.Build_Close_Tag (
644 l_content_type_code, -- p_content_type_code IN VARCHAR2
645 p_xml_clob_loc -- p_xml_clob_loc IN OUT CLOB
646 );
647 -- **************** Real Logic Ends *****************
648 EXCEPTION
649 WHEN FND_API.G_EXC_ERROR THEN
650 x_return_status := FND_API.G_RET_STS_ERROR;
651 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
652 p_data => x_msg_data);
653 END Get_Citem_Xml;
654
655
656
657
658 -- ******************************************************************************
659 -- ***** Cache Loading APIs
660 -- ******************************************************************************
661
662 PROCEDURE Bulk_Load (
663 p_init_msg_list IN VARCHAR2,
664 x_clobs OUT NOCOPY JTF_CLOB_TABLE,
665 x_content_item_ids OUT NOCOPY JTF_NUMBER_TABLE,
666 x_label_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
667 x_lang_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_msg_count OUT NOCOPY NUMBER,
670 x_msg_data OUT NOCOPY VARCHAR2
671 ) AS
672 l_citem_ids JTF_NUMBER_TABLE;
673 l_citem_ver_ids JTF_NUMBER_TABLE;
674 l_labels JTF_VARCHAR2_TABLE_100;
675 --
676 l_final_citem_ids JTF_NUMBER_TABLE;
677 l_final_labels JTF_VARCHAR2_TABLE_100;
678 l_final_lang_codes JTF_VARCHAR2_TABLE_100;
679 --
680 l_count NUMBER := 1;
681 l_final_count NUMBER := 1;
682 l_citem_version_id NUMBER;
683 --
684 CURSOR Get_Translated_Langs IS
685 select LANGUAGE
686 from IBC_CITEM_VERSIONS_TL
687 where CITEM_VERSION_ID = l_citem_version_id
688 and LANGUAGE = SOURCE_LANG;
689
690 BEGIN
691 -- Initialize message list if p_init_msg_list is set to TRUE.
692 IF FND_API.to_Boolean( p_init_msg_list ) THEN
693 FND_MSG_PUB.initialize;
694 END IF;
695 -- Initialize API return status to success
696 x_return_status := FND_API.G_RET_STS_SUCCESS;
697 --******************* Real Logic Start *********************
698
699 -- Call to get content items and versions to be loaded
700 IBC_LOAD_CITEMS_PVT.Get_Citems_To_Be_Loaded (
701 x_content_item_ids => l_citem_ids
702 ,x_citem_version_ids => l_citem_ver_ids
703 ,x_label_codes => l_labels
704 );
705
706 l_final_citem_ids := JTF_NUMBER_TABLE();
707 l_final_labels := JTF_VARCHAR2_TABLE_100();
708 l_final_lang_codes := JTF_VARCHAR2_TABLE_100();
709
710
711 -- Find out what translated languages each version has
712 WHILE (l_count <= l_citem_ver_ids.COUNT) LOOP
713 l_citem_version_id := l_citem_ver_ids(l_count);
714
715 FOR lang_rec IN Get_Translated_Langs LOOP
716 l_final_citem_ids.EXTEND();
717 l_final_labels.EXTEND();
718 l_final_lang_codes.EXTEND();
719
720 l_final_citem_ids(l_final_count) := l_citem_ids(l_count);
721 l_final_labels(l_final_count) := l_labels(l_count);
722 l_final_lang_codes(l_final_count) := lang_rec.LANGUAGE;
723
724 l_final_count := l_final_count + 1;
725 END LOOP;
726
727 l_count := l_count + 1;
728 END LOOP;
729
730 -- Call Load_Translated_Content_Items (DOES NOT validate dates)
731 Load_Translated_Content_Items (
732 p_init_msg_list => p_init_msg_list
733 ,p_content_item_ids => l_final_citem_ids
734 ,p_label_codes => l_final_labels
735 ,p_lang_codes => l_final_lang_codes
736 ,p_validate_dates => FND_API.G_FALSE
737 ,x_clobs => x_clobs
738 ,x_content_item_ids => x_content_item_ids
739 ,x_label_codes => x_label_codes
740 ,x_lang_codes => x_lang_codes
741 ,x_return_status => x_return_status
742 ,x_msg_count => x_msg_count
743 ,x_msg_data => x_msg_data
744 );
745 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
746 RAISE FND_API.G_EXC_ERROR;
747 END IF;
748
749 --******************* Real Logic End ***********************
750 EXCEPTION
751 WHEN FND_API.G_EXC_ERROR THEN
752 x_return_status := FND_API.G_RET_STS_ERROR;
753 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
754 p_data => x_msg_data);
755 END Bulk_Load;
756
757
758
759 PROCEDURE Load_Translated_Content_Items (
760 p_init_msg_list IN VARCHAR2,
761 p_content_item_ids IN JTF_NUMBER_TABLE,
762 p_label_codes IN JTF_VARCHAR2_TABLE_100,
763 p_lang_codes IN JTF_VARCHAR2_TABLE_100,
764 p_validate_dates IN VARCHAR2,
765 x_clobs OUT NOCOPY JTF_CLOB_TABLE,
766 x_content_item_ids OUT NOCOPY JTF_NUMBER_TABLE,
767 x_label_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
768 x_lang_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
769 x_return_status OUT NOCOPY VARCHAR2,
770 x_msg_count OUT NOCOPY NUMBER,
771 x_msg_data OUT NOCOPY VARCHAR2
772 ) AS
773 l_citem_id_count NUMBER := 1;
774 l_output_count NUMBER := 1;
775 l_tmp_clob CLOB;
776 l_num_levels_loaded NUMBER;
777 --
778 l_id NUMBER;
779 BEGIN
780 -- Initialize message list if p_init_msg_list is set to TRUE.
781 IF FND_API.to_Boolean( p_init_msg_list ) THEN
782 FND_MSG_PUB.initialize;
783 END IF;
784 -- Initialize API return status to success
785 x_return_status := FND_API.G_RET_STS_SUCCESS;
786
787 --******************* Real Logic Start *********************
788 x_clobs := JTF_CLOB_TABLE();
789 x_content_item_ids := JTF_NUMBER_TABLE();
790 x_label_codes := JTF_VARCHAR2_TABLE_100();
791 x_lang_codes := JTF_VARCHAR2_TABLE_100();
792
793 WHILE (l_citem_id_count <= p_content_item_ids.COUNT) LOOP
794 ------------------------------------------------------
795 DBMS_LOB.CREATETEMPORARY(l_tmp_clob, TRUE);
796
797 --select IBC_TEST_CLOB2_S.NEXTVAL INTO l_id from DUAL;
798
799 --SELECT DATA INTO l_tmp_clob
800 --FROM IBC_TEST_CLOB WHERE ID = l_id for update;
801 ------------------------------------------------------
802
803 Get_Citem_Xml (
804 p_init_msg_list => p_init_msg_list
805 ,p_content_item_id => p_content_item_ids(l_citem_id_count)
806 ,p_xml_clob_loc => l_tmp_clob
807 ,p_num_levels => NULL -- // Load DEEP
808 ,p_label_code => p_label_codes(l_citem_id_count)
809 ,p_lang_code => p_lang_codes(l_citem_id_count)
810 ,p_validate_dates => p_validate_dates
811 ,x_num_levels_loaded => l_num_levels_loaded
812 ,x_return_status => x_return_status
813 ,x_msg_count => x_msg_count
814 ,x_msg_data => x_msg_data
815 );
816 -- if NO Error AND Label does point to a version, add to result list
817 IF (x_return_status <> FND_API.G_RET_STS_ERROR AND
818 l_tmp_clob IS NOT NULL) THEN
819 x_clobs.EXTEND();
820 x_clobs(l_output_count) := l_tmp_clob;
821 x_content_item_ids.EXTEND();
822 x_content_item_ids(l_output_count) := p_content_item_ids(l_citem_id_count);
823 x_label_codes.EXTEND();
824 x_label_codes(l_output_count) := p_label_codes(l_citem_id_count);
825 x_lang_codes.EXTEND();
826 x_lang_codes(l_output_count) := p_lang_codes(l_citem_id_count);
827 l_output_count := l_output_count +1;
828 END IF;
829
830 l_citem_id_count := l_citem_id_count + 1;
831 END LOOP;
832
833 --******************* Real Logic End *********************
834 EXCEPTION
835 WHEN FND_API.G_EXC_ERROR THEN
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
838 p_data => x_msg_data);
839 END Load_Translated_Content_Items;
840
841
842
843
844 PROCEDURE Load_Citem_Version_Number (
845 p_init_msg_list IN VARCHAR2,
846 p_content_item_id IN NUMBER,
847 p_label_code IN VARCHAR2,
848 x_version_number OUT NOCOPY NUMBER,
849 x_return_status OUT NOCOPY VARCHAR2,
850 x_msg_count OUT NOCOPY NUMBER,
851 x_msg_data OUT NOCOPY VARCHAR2
852 ) AS
853 l_content_type_code IBC_CONTENT_ITEMS.CONTENT_TYPE_CODE%TYPE;
854 l_item_reference_code IBC_CONTENT_ITEMS.ITEM_REFERENCE_CODE%TYPE;
855 l_live_citem_version_id NUMBER;
856 l_citem_version_id NUMBER;
857 l_encrypt_flag VARCHAR2(1);
858 -----
859 CURSOR Get_Citem_Ver_By_Label IS
860 select citem_version_id
861 from IBC_CITEM_VERSION_LABELS
862 where label_code = p_label_code and
863 content_item_id = p_content_item_id;
864
865 CURSOR Get_Version_Number_Csr IS
866 select VERSION_NUMBER
867 from IBC_CITEM_VERSIONS_B
868 where citem_version_id = l_citem_version_id;
869
870 BEGIN
871 -- Initialize message list if p_init_msg_list is set to TRUE.
872 IF FND_API.to_Boolean( p_init_msg_list ) THEN
873 FND_MSG_PUB.initialize;
874 END IF;
875 -- Initialize API return status to success
876 x_return_status := FND_API.G_RET_STS_SUCCESS;
877
878 --******************* Real Logic Start *********************
879 Validate_Citem (
880 p_init_msg_list => p_init_msg_list,
881 p_content_item_id => p_content_item_id,
882 x_content_type_code => l_content_type_code,
883 x_item_reference_code => l_item_reference_code,
884 x_live_citem_version_id => l_live_citem_version_id,
885 x_encrypt_flag => l_encrypt_flag,
886 x_return_status => x_return_status,
887 x_msg_count => x_msg_count,
888 x_msg_data => x_msg_data
889 );
890 -- Content Item requested is not valid
891 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
892 RAISE FND_API.G_EXC_ERROR;
893 END IF;
894
895 -- Check if there is a label for this content item
896 IF (p_label_code is NULL) THEN
897 l_citem_version_id := l_live_citem_version_id;
898 ELSE
899 OPEN Get_Citem_Ver_By_Label;
900 FETCH Get_Citem_Ver_By_Label INTO l_citem_version_id;
901 -- Label doesn't exist for this content item id
902 IF (Get_Citem_Ver_By_Label%NOTFOUND) THEN
903 -- Validate Label
904 IF (Ibc_Validate_Pvt.isValidLabel(p_label_code) = FND_API.g_false) THEN
905 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
906 FND_MESSAGE.Set_Name('IBC', 'INVALID_LABEL_CODE');
907 FND_MESSAGE.Set_token('LABEL_CODE', p_label_code);
908 FND_MSG_PUB.ADD;
909 END IF;
910 RAISE FND_API.G_EXC_ERROR;
911 END IF;
912 x_version_number := -1;
913 return;
914 END IF;
915 CLOSE Get_Citem_Ver_By_Label;
916 END IF;
917
918 OPEN Get_Version_Number_Csr;
919 FETCH Get_Version_Number_Csr INTO x_version_number;
920 CLOSE Get_Version_Number_Csr;
921
922 --******************* Real Logic End *********************
923
924 -- Standard call to get message count and if count=1, get the message
925 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
926 p_data => x_msg_data);
927 EXCEPTION
928 WHEN FND_API.G_EXC_ERROR THEN
929 x_return_status := FND_API.G_RET_STS_ERROR;
930 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
931 p_data => x_msg_data);
932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
935 p_data => x_msg_data);
936 WHEN OTHERS THEN
937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
938 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
939 p_data => x_msg_data);
940 END Load_Citem_Version_Number;
941
942
943
944
945 PROCEDURE Load_Associations (
946 p_init_msg_list IN VARCHAR2,
947 p_association_type_code IN VARCHAR2,
948 p_associated_object_val1 IN VARCHAR2,
949 p_associated_object_val2 IN VARCHAR2,
950 p_associated_object_val3 IN VARCHAR2,
951 p_associated_object_val4 IN VARCHAR2,
952 p_associated_object_val5 IN VARCHAR2,
953 x_content_item_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
954 x_return_status OUT NOCOPY VARCHAR2,
955 x_msg_count OUT NOCOPY NUMBER,
956 x_msg_data OUT NOCOPY VARCHAR2
957 ) AS
958 l_citem_count NUMBER := 1;
959 -----
960 CURSOR Get_Citems_By_Assoc IS
961 select CONTENT_ITEM_ID
962 from IBC_ASSOCIATIONS
963 where ASSOCIATION_TYPE_CODE = p_association_type_code
964 and ASSOCIATED_OBJECT_VAL1 = p_associated_object_val1
965 and NVL(ASSOCIATED_OBJECT_VAL2, '0') = NVL(p_associated_object_val2, '0')
966 and NVL(ASSOCIATED_OBJECT_VAL3, '0') = NVL(p_associated_object_val3, '0')
967 and NVL(ASSOCIATED_OBJECT_VAL4, '0') = NVL(p_associated_object_val4, '0')
968 and NVL(ASSOCIATED_OBJECT_VAL5, '0') = NVL(p_associated_object_val5, '0');
969
970 BEGIN
971 -- Initialize message list if p_init_msg_list is set to TRUE.
972 IF FND_API.to_Boolean( p_init_msg_list ) THEN
973 FND_MSG_PUB.initialize;
974 END IF;
975 -- Initialize API return status to success
976 x_return_status := FND_API.G_RET_STS_SUCCESS;
977
978 --******************* Real Logic Start *********************
979
980 x_content_item_id_tbl := JTF_NUMBER_TABLE();
981
982 FOR citem_id_rec IN Get_Citems_By_Assoc LOOP
983 x_content_item_id_tbl.EXTEND();
984 x_content_item_id_tbl(l_citem_count) := citem_id_rec.content_item_id;
985 l_citem_count := l_citem_count + 1;
986 END LOOP;
987
988 -- If no matches, check if p_association_type_code is valid
989 IF (l_citem_count = 1) THEN
990 IF (Ibc_Validate_Pvt.isValidAssocType(p_association_type_code) = FND_API.g_false) THEN
991 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
992 FND_MESSAGE.Set_Name('IBC', 'INVALID_ASSOC_TYPE_CODE');
993 FND_MESSAGE.Set_token('ASSOC_TYPE_CODE', p_association_type_code);
994 FND_MSG_PUB.ADD;
995 END IF;
996 RAISE FND_API.G_EXC_ERROR;
997 END IF;
998 END IF;
999
1000 --******************* Real Logic End *********************
1001
1002 -- Standard call to get message count and if count=1, get the message
1003 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1004 p_data => x_msg_data);
1005 EXCEPTION
1006 WHEN FND_API.G_EXC_ERROR THEN
1007 x_return_status := FND_API.G_RET_STS_ERROR;
1008 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1009 p_data => x_msg_data);
1010 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1012 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1013 p_data => x_msg_data);
1014 WHEN OTHERS THEN
1015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1016 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1017 p_data => x_msg_data);
1018 END Load_Associations;
1019
1020
1021 -- --------------------------------------------------------------
1022 -- Get a specific Content Item Version in XML
1023 -- p_xml_clob_loc will be NULL and
1024 -- x_num_levels_loaded will be -1
1025 -- --------------------------------------------------------------
1026
1027 PROCEDURE Get_Citem_Xml (
1028 p_init_msg_list IN VARCHAR2,
1029 p_content_item_id IN NUMBER,
1030 p_xml_clob_loc IN OUT NOCOPY CLOB,
1031 p_num_levels IN NUMBER,
1032 p_citem_version_id IN NUMBER,
1033 p_lang_code IN VARCHAR2,
1034 p_validate_dates IN VARCHAR2,
1035 x_num_levels_loaded OUT NOCOPY NUMBER,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2
1039 ) AS
1040 l_content_type_code IBC_CONTENT_ITEMS.CONTENT_TYPE_CODE%TYPE;
1041 l_item_reference_code IBC_CONTENT_ITEMS.ITEM_REFERENCE_CODE%TYPE;
1042 l_live_citem_version_id NUMBER;
1043 l_citem_version_id NUMBER;
1044 l_encrypt_flag VARCHAR2(1);
1045
1046 l_version_number NUMBER;
1047 l_start_date DATE;
1048 l_end_date DATE;
1049
1050 l_translation_status IBC_CITEM_VERSIONS_TL.CITEM_TRANSLATION_STATUS%TYPE;
1051 l_lang_code VARCHAR2(4);
1052
1053 l_content_item_name IBC_CITEM_VERSIONS_TL.CONTENT_ITEM_NAME%TYPE;
1054 l_description IBC_CITEM_VERSIONS_TL.DESCRIPTION%TYPE;
1055 l_attachment_attribute_code IBC_CITEM_VERSIONS_TL.ATTACHMENT_ATTRIBUTE_CODE%TYPE;
1056 l_attachment_file_id NUMBER;
1057 l_attachment_file_name IBC_CITEM_VERSIONS_TL.ATTACHMENT_FILE_NAME%TYPE;
1058 l_attribute_file_id NUMBER;
1059 l_default_mime_type IBC_CITEM_VERSIONS_TL.DEFAULT_RENDITION_MIME_TYPE%TYPE;
1060
1061 l_comp_content_item_id NUMBER;
1062 l_tmp_num_levels NUMBER;
1063 l_has_component_items VARCHAR2(1) := FND_API.g_false;
1064 l_max_num_levels_loaded NUMBER := 0;
1065
1066 -- Retrive the Content Item Version details
1067
1068 CURSOR Get_Citem_Version IS
1069 select START_DATE, END_DATE, VERSION_NUMBER
1070 from IBC_CITEM_VERSIONS_B
1071 where CITEM_VERSION_ID =p_citem_version_id;
1072
1073 -- // retrieve citem version in the specified language
1074 CURSOR Get_Citem_Meta_Csr IS
1075 select CITEM_TRANSLATION_STATUS, ATTRIBUTE_FILE_ID, CONTENT_ITEM_NAME, DESCRIPTION, ATTACHMENT_ATTRIBUTE_CODE,
1076 ATTACHMENT_FILE_ID, ATTACHMENT_FILE_NAME, DEFAULT_RENDITION_MIME_TYPE
1077 from IBC_CITEM_VERSIONS_TL
1078 where citem_version_id =p_citem_version_id
1079 and language = nvl(p_lang_code, userenv('LANG'));
1080
1081 -- // retrieve the based language version translation
1082 CURSOR Get_Citem_Meta_BLang IS
1083 select i.BASE_LANGUAGE, t.ATTRIBUTE_FILE_ID, t.CONTENT_ITEM_NAME, t.DESCRIPTION, t.ATTACHMENT_ATTRIBUTE_CODE,
1084 t.ATTACHMENT_FILE_ID, t.ATTACHMENT_FILE_NAME, t.DEFAULT_RENDITION_MIME_TYPE
1085 from IBC_CONTENT_ITEMS i, IBC_CITEM_VERSIONS_TL t
1086 where i.CONTENT_ITEM_ID = p_content_item_id
1087 and t.citem_version_id = p_citem_version_id
1088 and t.language = i.BASE_LANGUAGE;
1089
1090 CURSOR Get_Compound_Item_Ref IS
1091 select r.ATTRIBUTE_TYPE_CODE, r.CONTENT_ITEM_ID, c.ENCRYPT_FLAG,
1092 nvl(SUBITEM_VERSION_ID, nvl(LIVE_CITEM_VERSION_ID, (select max(VERSION_NUMBER) from IBC_CITEM_VERSIONS_B where CONTENT_ITEM_ID = r.CONTENT_ITEM_ID))) child_version_id
1093 from IBC_COMPOUND_RELATIONS r, IBC_CONTENT_ITEMS c
1094 where r.CITEM_VERSION_ID =p_citem_version_id
1095 and r.CONTENT_ITEM_ID = c.CONTENT_ITEM_ID
1096 order by r.SORT_ORDER;
1097
1098 BEGIN
1099
1100 -- Initialize message list if p_init_msg_list is set to TRUE.
1101 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1102 FND_MSG_PUB.initialize;
1103 END IF;
1104 -- Initialize API return status to success
1105 x_return_status := FND_API.G_RET_STS_SUCCESS;
1106 -- **************** Real Logic Starts *****************
1107
1108 Validate_Citem (
1109 p_init_msg_list => p_init_msg_list,
1110 p_content_item_id => p_content_item_id,
1111 x_content_type_code => l_content_type_code,
1112 x_item_reference_code => l_item_reference_code,
1113 x_live_citem_version_id => l_live_citem_version_id,
1114 x_encrypt_flag => l_encrypt_flag,
1115 x_return_status => x_return_status,
1116 x_msg_count => x_msg_count,
1117 x_msg_data => x_msg_data
1118 );
1119 -- Content Item requested is not valid
1120 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1121 RAISE FND_API.G_EXC_ERROR;
1122 END IF;
1123
1124 -- This is preloading, DO NOT validate DATES
1125 IF (p_validate_dates = FND_API.G_FALSE) THEN
1126 OPEN Get_Citem_Version;
1127 FETCH Get_Citem_Version INTO l_start_date,l_end_date,l_version_number;
1128 CLOSE Get_Citem_Version;
1129
1130 -- Not preloading, NEED to validate DATES
1131 ELSE
1132 Validate_Start_End_Date (
1133 p_init_msg_list => p_init_msg_list,
1134 p_content_item_id => p_content_item_id,
1135 p_citem_version_id => p_citem_version_id,
1136 x_version_number => l_version_number,
1137 x_start_date => l_start_date,
1138 x_end_date => l_end_date,
1139 x_return_status => x_return_status,
1140 x_msg_count => x_msg_count,
1141 x_msg_data => x_msg_data
1142 );
1143 -- Start/End date not valid
1144 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1145 RAISE FND_API.G_EXC_ERROR;
1146 END IF;
1147 END IF;
1148
1149 -- // Retrieve Meta Data in specified language
1150 -- // l_lang_code := p_label_code;
1151 OPEN Get_Citem_Meta_Csr;
1152 FETCH Get_Citem_Meta_Csr INTO l_translation_status,
1153 l_attribute_file_id, l_content_item_name, l_description,
1154 l_attachment_attribute_code,
1155 l_attachment_file_id,
1156 l_attachment_file_name,
1157 l_default_mime_type;
1158 -- Not found, input LANGUAGE must be invalid (b/c version id already verified)
1159 IF Get_Citem_Meta_Csr%NOTFOUND THEN
1160 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1161 FND_MESSAGE.Set_Name('IBC', 'INVALID_LANG_CODE');
1162 FND_MESSAGE.Set_token('LANG_CODE', p_lang_code);
1163 FND_MSG_PUB.ADD;
1164 END IF;
1165 RAISE FND_API.G_EXC_ERROR;
1166 END IF;
1167 CLOSE Get_Citem_Meta_Csr;
1168
1169 -- // If translation status is not APPROVED, retrieve based language translation
1170 IF (l_translation_status <> 'APPROVED') THEN
1171 OPEN Get_Citem_Meta_BLang;
1172 -- // bring in base language
1173 FETCH Get_Citem_Meta_BLang INTO l_lang_code,
1174 l_attribute_file_id, l_content_item_name, l_description,
1175 l_attachment_attribute_code,
1176 l_attachment_file_id,
1177 l_attachment_file_name,
1178 l_default_mime_type;
1179 CLOSE Get_Citem_Meta_BLang;
1180 END IF;
1181
1182 -- // Openning Tags (Root + Renditions + Name + Description + Attachment)
1183 IBC_UTILITIES_PVT.Build_Citem_Open_Tags (
1184 p_content_type_code => l_content_type_code
1185 ,p_content_item_id => p_content_item_id
1186 ,p_citem_version_id => p_citem_version_id
1187 ,p_item_label => NULL
1188 ,p_lang_code => l_lang_code -- Renditions based on language used to retrieve item
1189 ,p_version_number => l_version_number
1190 ,p_start_date => l_start_date
1191 ,p_end_date => l_end_date
1192 ,p_item_reference_code => l_item_reference_code
1193 ,p_encrypt_flag => l_encrypt_flag -- parent item encrypt flag
1194 ,p_content_item_name => l_content_item_name
1195 ,p_description => l_description
1196 ,p_attachment_attribute_code => l_attachment_attribute_code
1197 ,p_attachment_file_id => l_attachment_file_id
1198 ,p_attachment_file_name => l_attachment_file_name
1199 ,p_default_mime_type => l_default_mime_type
1200 ,p_is_preview => FND_API.G_FALSE
1201 ,p_xml_clob_loc => p_xml_clob_loc
1202 );
1203 -- User defined primitive attributes
1204 IBC_UTILITIES_PVT.Build_Attribute_Bundle (
1205 p_file_id => l_attribute_file_id,
1206 p_xml_clob_loc => p_xml_clob_loc
1207 );
1208
1209 -- Compound Item attributes
1210 IF (p_num_levels IS NULL OR p_num_levels > 0) THEN
1211
1212 -- compound items expanded
1213 FOR compound_item_rec IN Get_Compound_Item_Ref LOOP
1214 l_has_component_items := FND_API.g_true;
1215 l_comp_content_item_id := compound_item_rec.content_item_id;
1216
1217 IBC_UTILITIES_PVT.Build_Compound_Item_Open_Tag (
1218 p_attribute_type_code => compound_item_rec.attribute_type_code,
1219 p_content_item_id => l_comp_content_item_id,
1220 p_item_label => NULL,
1221 p_encrypt_flag => compound_item_rec.encrypt_flag,
1222 p_xml_clob_loc => p_xml_clob_loc
1223 );
1224
1225 l_tmp_num_levels := p_num_levels - 1;
1226
1227 Get_Citem_Xml (
1228 p_init_msg_list => p_init_msg_list,
1229 p_content_item_id => l_comp_content_item_id,
1230 p_xml_clob_loc => p_xml_clob_loc,
1231 p_num_levels => l_tmp_num_levels,
1232 p_citem_version_id => compound_item_rec.child_version_id,
1233 p_lang_code => p_lang_code, -- components retrieved with requested language
1234 p_validate_dates => p_validate_dates,
1235 x_num_levels_loaded => x_num_levels_loaded,
1236 x_return_status => x_return_status,
1237 x_msg_count => x_msg_count,
1238 x_msg_data => x_msg_data
1239 );
1240
1241 -- nested compounded items not valid
1242 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1243 RAISE FND_API.G_EXC_ERROR;
1244 END IF;
1245
1246 -- check if component item is matched with label
1247 IF (p_xml_clob_loc is NULL) THEN
1248 x_num_levels_loaded := -1;
1249 return;
1250 END IF;
1251
1252 IBC_UTILITIES_PVT.Build_Close_Tag (
1253 compound_item_rec.attribute_type_code,
1254 p_xml_clob_loc
1255 );
1256
1257 -- update max num levels
1258 IF (x_num_levels_loaded > l_max_num_levels_loaded) THEN
1259 l_max_num_levels_loaded := x_num_levels_loaded;
1260 END IF;
1261
1262 END LOOP;
1263
1264 -- set number of levels loaded
1265 IF (l_has_component_items = FND_API.g_false) THEN
1266 x_num_levels_loaded := 0;
1267 ELSE
1268 x_num_levels_loaded := l_max_num_levels_loaded + 1;
1269 END IF;
1270
1271 -- p_num_levels = 0
1272 ELSE
1273 -- compound item references
1274 IBC_UTILITIES_PVT.Build_Compound_Item_References (
1275 p_citem_version_id => p_citem_version_id,
1276 p_item_label => NULL,
1277 p_xml_clob_loc => p_xml_clob_loc
1278 );
1279 -- set number of levels loaded
1280 x_num_levels_loaded := 0;
1281
1282 END IF;
1283
1284 -- Close Root Tag
1285 IBC_UTILITIES_PVT.Build_Close_Tag (
1286 l_content_type_code, -- p_content_type_code IN VARCHAR2
1287 p_xml_clob_loc -- p_xml_clob_loc IN OUT CLOB
1288 );
1289 -- **************** Real Logic Ends *****************
1290 EXCEPTION
1291 WHEN FND_API.G_EXC_ERROR THEN
1292 x_return_status := FND_API.G_RET_STS_ERROR;
1293 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1294 p_data => x_msg_data);
1295 END Get_Citem_Xml;
1296
1297
1298
1299 END IBC_CITEM_RUNTIME_PVT;