[Home] [Help]
PACKAGE BODY: APPS.IBC_CITEM_VERSIONS_PKG
Source
1 PACKAGE BODY Ibc_Citem_Versions_Pkg AS
2 /* $Header: ibctcivb.pls 120.7.12010000.1 2008/07/28 11:02:22 appldev ship $*/
3
4 -- Purpose: Table Handler for Ibc_Citem_Versions table.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Sri Rangarajan 01/06/2002 Created Package
10 -- shitij.vatsa 11/04/2002 Updated for FND_API.G_MISS_XXX
11 -- shitij.vatsa 02/11/2003 Added parameter p_subitem_version_id
12 -- to the APIs
13 -- Ed Nunez 08/14/2003 Content Item Name Uniqueness
14 -- Ed Nunez 09/19/2003 Content Item Name accross folder and items
15 -- Ed Nunez 03/09/2004 Gettid rid of enforcing unique name accross versions
16 -- shitij.vatsa 05/03/2004 Updated the Update_Row API to update the TL table
17 -- in two phases once for the installed language
18 -- and once for both the installed and the source language.
19 -- Bug Fix:3589057
20 -- shitij.vatsa 05/04/2004 Added a new API-
21 -- populate_all_attachments
22 -- Bug Fix:3597752
23 -- Subir Anshumali 03/11/2005 Added TEXTIDX = 'X' in update_row.
24 -- Also, added SYNC_INDEX.
25 -- Subir Anshumali 06/03/2005 Declared OUT and IN OUT arguments as references using the NOCOPY hint.
26 -- Also commented logic of TEXTIDX as of now.
27 -- To add this again once we do intermedia search using userdatastore.
28 -- Sri.Rangarajan 06/29/2005 Enhancement Bug 3664840 Content Item Name should be the same across
29 -- all versions for that language.
30 -- SHARMA 07/04/2005 Modified LOAD_ROW, TRANSLATE_ROW and created
31 -- LOAD_SEED_ROW for R12 LCT standards bug 4411674
32 -- Sri.Rangarajan 08/20/2005 Added TEXTIDX = 'X' in update_row.Added concurrent request
33 -- to sync Content Text indexes
34
35
36 G_PKG_NAME CONSTANT VARCHAR2(100) := 'IBC_CITEM_VERSIONS_PKG';
37
38
39 PROCEDURE INSERT_ROW (
40 x_rowid OUT NOCOPY VARCHAR2
41 ,px_citem_version_id IN OUT NOCOPY NUMBER
42 ,p_content_item_id IN NUMBER
43 ,p_version_number IN NUMBER
44 ,p_citem_version_status IN VARCHAR2
45 ,p_start_date IN DATE
46 ,p_end_date IN DATE
47 ,px_object_version_number IN OUT NOCOPY NUMBER
48 ,p_attribute_file_id IN NUMBER
49 ,p_attachment_attribute_code IN VARCHAR2
50 ,p_attachment_file_id IN NUMBER
51 ,p_content_item_name IN VARCHAR2
52 ,p_attachment_file_name IN VARCHAR2 --DEFAULT NULL
53 ,p_description IN VARCHAR2
54 ,p_default_rendition_mime_type IN VARCHAR2 --DEFAULT NULL
55 ,p_creation_date IN DATE --DEFAULT NULL
56 ,p_created_by IN NUMBER --DEFAULT NULL
57 ,p_last_update_date IN DATE --DEFAULT NULL
58 ,p_last_updated_by IN NUMBER --DEFAULT NULL
59 ,p_last_update_login IN NUMBER --DEFAULT NULL
60 ,p_citem_translation_status IN VARCHAR2 --DEFAULT NULL
61 )
62 IS
63 CURSOR C IS SELECT ROWID FROM IBC_CITEM_VERSIONS_B
64 WHERE CITEM_VERSION_ID = px_CITEM_VERSION_ID;
65
66 CURSOR c2 IS SELECT ibc_citem_versions_s1.NEXTVAL FROM dual;
67
68 CURSOR c_citem_dirnode(p_content_item_id NUMBER)
69 IS
70 SELECT directory_node_id
71 FROM ibc_content_items citems
72 WHERE content_item_id = p_content_item_id;
73
74
75 l_dirnode NUMBER;
76 G_API_NAME CONSTANT VARCHAR2(30) := 'INSERT_ROW';
77 l_object_type VARCHAR2(30);
78 l_object_id NUMBER;
79
80 BEGIN
81
82 -- Validation of Unique Name in a particular directory for a especific
83 -- Language.
84 OPEN c_citem_dirnode(p_content_item_id);
85 FETCH c_citem_dirnode INTO l_dirnode;
86 CLOSE c_citem_dirnode;
87
88 -- IF IBC_UTILITIES_PVT.is_name_already_used(p_dir_node_id => l_dirnode,
89 -- p_name => p_content_item_name,
90 -- p_language => USERENV('lang'),
91 -- p_chk_content_item_id => p_content_item_id,
92 -- x_object_type => l_object_type,
93 -- x_object_id => l_object_id)
94 -- THEN
95 -- IF l_object_type = 'DIRNODE' THEN
96 -- Fnd_Message.Set_Name('IBC', 'IBC_NAME_ALREADY_FOLDER');
97 -- ELSE
98 -- Fnd_Message.Set_Name('IBC', 'IBC_CITEM_NAME_UNIQUE');
99 -- Fnd_Message.Set_token('NEW_ITEM_NAME' , p_content_item_name);
100 -- Fnd_Message.Set_token('CONFLICTING_ITEM_NAME' , IBC_UTILITIES_PVT.get_citem_name(l_object_id));
101 -- END IF;
102 -- Fnd_Msg_Pub.ADD;
103 -- RAISE Fnd_Api.G_EXC_ERROR;
104 -- END IF;
105
106
107 Ibc_Content_Items_Pkg.UPDATE_ROW (
108 p_CONTENT_ITEM_ID =>p_CONTENT_ITEM_ID
109 ,px_OBJECT_VERSION_NUMBER =>px_object_version_number
110 ,p_last_updated_by =>p_last_updated_by);
111
112
113 -- Primary key validation check
114
115 IF ((px_CITEM_VERSION_ID IS NULL) OR
116 (px_CITEM_VERSION_ID = Fnd_Api.G_MISS_NUM))
117 THEN
118 OPEN c2;
119 FETCH c2 INTO px_CITEM_VERSION_ID;
120 CLOSE c2;
121 END IF;
122
123 INSERT INTO IBC_CITEM_VERSIONS_B (
124 CITEM_VERSION_ID,
125 CONTENT_ITEM_ID,
126 VERSION_NUMBER,
127 CITEM_VERSION_STATUS,
128 START_DATE,
129 END_DATE,
130 OBJECT_VERSION_NUMBER,
131 CREATION_DATE,
132 CREATED_BY,
133 LAST_UPDATE_DATE,
134 LAST_UPDATED_BY,
135 LAST_UPDATE_LOGIN
136 ) VALUES (
137 DECODE(px_citem_version_id,FND_API.G_MISS_NUM,NULL,px_citem_version_id)
138 ,DECODE(p_content_item_id,FND_API.G_MISS_NUM,NULL,p_content_item_id)
139 ,DECODE(p_version_number,FND_API.G_MISS_NUM,NULL,p_version_number)
140 ,DECODE(p_citem_version_status,FND_API.G_MISS_CHAR,NULL,p_citem_version_status)
141 ,DECODE(p_start_date,FND_API.G_MISS_DATE,NULL,p_start_date)
142 ,DECODE(p_end_date,FND_API.G_MISS_DATE,NULL,p_end_date)
143 ,DECODE(px_object_version_number,FND_API.G_MISS_NUM,1,NULL,1,px_object_version_number)
144 ,DECODE(p_creation_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_creation_date)
145 ,DECODE(p_created_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_created_by)
146 ,DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
147 ,DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
148 ,DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
149 );
150
151 INSERT INTO IBC_CITEM_VERSIONS_TL (
152 CITEM_VERSION_ID,
153 ATTRIBUTE_FILE_ID,
154 ATTACHMENT_ATTRIBUTE_CODE,
155 CONTENT_ITEM_NAME,
156 ATTACHMENT_FILE_ID,
157 ATTACHMENT_FILE_NAME,
158 DESCRIPTION,
159 DEFAULT_RENDITION_MIME_TYPE,
160 CREATION_DATE,
161 CREATED_BY,
162 LAST_UPDATE_DATE,
163 LAST_UPDATED_BY,
164 LAST_UPDATE_LOGIN,
165 LANGUAGE,
166 SOURCE_LANG,
167 CITEM_TRANSLATION_STATUS
168 ) SELECT
169 DECODE(px_citem_version_id,FND_API.G_MISS_NUM,NULL,px_citem_version_id)
170 ,DECODE(p_attribute_file_id,FND_API.G_MISS_NUM,NULL,p_attribute_file_id)
171 ,DECODE(p_attachment_attribute_code,FND_API.G_MISS_CHAR,NULL,p_attachment_attribute_code)
172 ,DECODE(p_content_item_name,FND_API.G_MISS_CHAR,NULL,p_content_item_name)
173 ,DECODE(p_attachment_file_id,FND_API.G_MISS_NUM,NULL,p_attachment_file_id)
174 ,DECODE(p_attachment_file_name,FND_API.G_MISS_CHAR,NULL,p_attachment_file_name)
175 ,DECODE(p_description,FND_API.G_MISS_CHAR,NULL,p_description)
176 ,DECODE(p_default_rendition_mime_type,FND_API.G_MISS_CHAR,NULL,p_default_rendition_mime_type)
177 ,DECODE(p_creation_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_creation_date)
178 ,DECODE(p_created_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_created_by)
179 ,DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
180 ,DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
181 ,DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
182 ,L.LANGUAGE_CODE
183 ,USERENV('LANG')
184 ,DECODE(p_citem_translation_status,FND_API.G_MISS_CHAR,NULL,p_citem_translation_status)
185 FROM FND_LANGUAGES L
186 WHERE L.INSTALLED_FLAG IN ('I', 'B')
187 AND NOT EXISTS
188 (SELECT NULL
189 FROM IBC_CITEM_VERSIONS_TL T
190 WHERE T.CITEM_VERSION_ID = px_CITEM_VERSION_ID
191 AND T.LANGUAGE = L.LANGUAGE_CODE);
192
193 OPEN c;
194 FETCH c INTO x_ROWID;
195 IF (c%NOTFOUND) THEN
196 CLOSE c;
197 RAISE NO_DATA_FOUND;
198 END IF;
199 CLOSE c;
200
201 /*
202 Due to new requirement (03-09-2004) Same name accross versions is
203 not being enforced anymore -- Reverted
204 */
205
206 -- Reverting back the previous change. In R12 Enhancement Bug 3664840 Content Item Name should be the same
207 -- across all versions for that language
208
209 -- Update Content Item Name for all versions of current language
210 UPDATE IBC_CITEM_VERSIONS_TL
211 SET CONTENT_ITEM_NAME = DECODE(p_content_item_name,Fnd_Api.G_MISS_CHAR,NULL,NULL,content_item_name,p_content_item_name)
212 WHERE citem_version_id IN (SELECT citem_version_id
213 FROM IBC_CITEM_VERSIONS_B verb
214 WHERE verb.content_item_id = p_content_item_id);
215
216
217 END INSERT_ROW;
218
219 PROCEDURE POPULATE_ALL_LANG (
220 p_CITEM_VERSION_ID IN NUMBER,
221 p_CONTENT_ITEM_ID IN NUMBER,
222 p_VERSION_NUMBER IN NUMBER,
223 p_CITEM_VERSION_STATUS IN VARCHAR2,
224 p_START_DATE IN DATE,
225 p_END_DATE IN DATE,
226 p_OBJECT_VERSION_NUMBER IN NUMBER,
227 p_ATTRIBUTE_FILE_ID IN NUMBER,
228 p_ATTACHMENT_ATTRIBUTE_CODE IN VARCHAR2,
229 P_SOURCE_LANG IN VARCHAR2 ,--DEFAULT USERENV('LANG'),
230 p_ATTACHMENT_FILE_ID IN NUMBER,
231 p_CONTENT_ITEM_NAME IN VARCHAR2,
232 p_ATTACHMENT_FILE_NAME IN VARCHAR2,
233 p_DESCRIPTION IN VARCHAR2,
234 p_DEFAULT_RENDITION_MIME_TYPE IN VARCHAR2 ,--DEFAULT NULL,
235 p_CREATION_DATE IN DATE ,--DEFAULT NULL,
236 p_CREATED_BY IN NUMBER ,--DEFAULT NULL,
237 p_LAST_UPDATE_DATE IN DATE ,--DEFAULT NULL,
238 p_LAST_UPDATED_BY IN NUMBER ,--DEFAULT NULL,
239 p_LAST_UPDATE_LOGIN IN NUMBER ,--DEFAULT NULL,
240 p_CITEM_TRANSLATION_STATUS IN VARCHAR2 --DEFAULT NULL
241 )
242 IS
243
244
245 CURSOR c_prev_version(p_content_item_id NUMBER,
246 p_citem_version_id NUMBER)
247 IS
248 SELECT citem_version_id
249 FROM ibc_citem_versions_b
250 WHERE content_item_id = p_content_item_id
251 AND version_number < (SELECT version_number
252 FROM ibc_citem_versions_b
253 WHERE content_item_id = p_content_item_id
254 AND citem_version_id = p_citem_version_id)
255 ORDER BY version_number desc;
256
257 CURSOR c_citem_dirnode(p_content_item_id NUMBER)
258 IS
259 SELECT directory_node_id
260 FROM ibc_content_items citems
261 WHERE content_item_id = p_content_item_id;
262
263 l_prev_version_id NUMBER;
264 l_dirnode NUMBER;
265 l_object_type VARCHAR2(30);
266 l_object_id NUMBER;
267
268 BEGIN
269
270 -- Validation of Unique Name in a particular directory for all languages
271 OPEN c_citem_dirnode(p_content_item_id);
272 FETCH c_citem_dirnode INTO l_dirnode;
273 CLOSE c_citem_dirnode;
274
275 IF IBC_UTILITIES_PVT.is_name_already_used(p_dir_node_id => l_dirnode,
276 p_name => p_content_item_name,
277 p_language => USERENV('lang'),
278 p_chk_content_item_id => p_content_item_id,
279 x_object_type => l_object_type,
280 x_object_id => l_object_id)
281 THEN
282 IF l_object_type = 'DIRNODE' THEN
283 Fnd_Message.Set_Name('IBC', 'IBC_NAME_ALREADY_FOLDER');
284 ELSE
285 Fnd_Message.Set_Name('IBC', 'IBC_CITEM_NAME_UNIQUE');
286 Fnd_Message.Set_token('NEW_ITEM_NAME' , p_content_item_name);
287 Fnd_Message.Set_token('CONFLICTING_ITEM_NAME' , IBC_UTILITIES_PVT.get_citem_name(l_object_id));
288 END IF;
289 Fnd_Msg_Pub.ADD;
290 RAISE Fnd_Api.G_EXC_ERROR;
291 END IF;
292
293
294 IF p_version_number > 1 THEN
295 -- Copy Translations from previous version
296 OPEN c_prev_version(p_content_item_id, p_citem_version_id);
297 FETCH c_prev_version INTO l_prev_version_id;
298 IF c_prev_version%FOUND THEN
299
300 INSERT INTO IBC_CITEM_VERSIONS_TL (
301 CITEM_VERSION_ID,
302 ATTRIBUTE_FILE_ID,
303 ATTACHMENT_ATTRIBUTE_CODE,
304 CONTENT_ITEM_NAME,
305 ATTACHMENT_FILE_ID,
306 ATTACHMENT_FILE_NAME,
307 DESCRIPTION,
308 DEFAULT_RENDITION_MIME_TYPE,
309 CREATION_DATE,
310 CREATED_BY,
311 LAST_UPDATE_DATE,
312 LAST_UPDATED_BY,
313 LAST_UPDATE_LOGIN,
314 LANGUAGE,
315 SOURCE_LANG,
316 CITEM_TRANSLATION_STATUS
317 ) SELECT DECODE(p_CITEM_VERSION_ID,Fnd_Api.G_MISS_NUM,NULL,p_CITEM_VERSION_ID),
318 L.attribute_file_id,
319 L.attachment_attribute_code,
320 L.content_item_name,
321 L.attachment_file_id,
322 L.attachment_file_name,
323 L.description,
324 L.default_rendition_mime_type,
325 DECODE(p_creation_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
326 p_creation_date) ,
327 DECODE(p_created_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
328 NULL, Fnd_Global.user_id, p_created_by),
329 DECODE(p_last_update_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
330 p_last_update_date),
331 DECODE(p_last_updated_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
332 NULL, Fnd_Global.user_id, p_last_updated_by),
333 DECODE(p_last_update_login, Fnd_Api.G_MISS_NUM, Fnd_Global.login_id,
334 NULL, Fnd_Global.login_id, p_last_update_login),
335 language,
336 DECODE(P_SOURCE_LANG, Fnd_Api.G_MISS_CHAR,USERENV('LANG'), NULL, USERENV('LANG'),P_SOURCE_LANG),
337 DECODE(p_CITEM_TRANSLATION_STATUS,Fnd_Api.G_MISS_CHAR,NULL,p_CITEM_TRANSLATION_STATUS)
338 FROM ibc_citem_versions_tl L
339 WHERE L.citem_version_id = l_prev_version_id
340 AND NOT EXISTS
341 (SELECT NULL
342 FROM IBC_CITEM_VERSIONS_TL T
343 WHERE T.CITEM_VERSION_ID = p_CITEM_VERSION_ID
344 AND T.LANGUAGE = L.LANGUAGE);
345
346 END IF;
347 CLOSE c_prev_version;
348 END IF;
349
350 INSERT INTO IBC_CITEM_VERSIONS_TL (
351 CITEM_VERSION_ID,
352 ATTRIBUTE_FILE_ID,
353 ATTACHMENT_ATTRIBUTE_CODE,
354 CONTENT_ITEM_NAME,
355 ATTACHMENT_FILE_ID,
356 ATTACHMENT_FILE_NAME,
357 DESCRIPTION,
358 DEFAULT_RENDITION_MIME_TYPE,
359 CREATION_DATE,
360 CREATED_BY,
361 LAST_UPDATE_DATE,
362 LAST_UPDATED_BY,
363 LAST_UPDATE_LOGIN,
364 LANGUAGE,
365 SOURCE_LANG,
366 CITEM_TRANSLATION_STATUS
367 ) SELECT
368 DECODE(p_CITEM_VERSION_ID,Fnd_Api.G_MISS_NUM,NULL,p_CITEM_VERSION_ID),
369 DECODE(p_ATTRIBUTE_FILE_ID,Fnd_Api.G_MISS_NUM,NULL,p_ATTRIBUTE_FILE_ID),
370 DECODE(p_ATTACHMENT_ATTRIBUTE_CODE,Fnd_Api.G_MISS_CHAR,NULL,p_ATTACHMENT_ATTRIBUTE_CODE),
371 DECODE(p_CONTENT_ITEM_NAME,Fnd_Api.G_MISS_CHAR,NULL,p_CONTENT_ITEM_NAME),
372 DECODE(p_ATTACHMENT_FILE_ID,Fnd_Api.G_MISS_NUM,NULL,p_ATTACHMENT_FILE_ID),
373 DECODE(p_ATTACHMENT_FILE_NAME,Fnd_Api.G_MISS_CHAR,NULL,p_ATTACHMENT_FILE_NAME),
374 DECODE(p_DESCRIPTION,Fnd_Api.G_MISS_CHAR,NULL,p_DESCRIPTION),
375 DECODE(p_DEFAULT_RENDITION_MIME_TYPE,Fnd_Api.G_MISS_CHAR,NULL,p_DEFAULT_RENDITION_MIME_TYPE),
376 DECODE(p_creation_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
377 p_creation_date) ,
378 DECODE(p_created_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
379 NULL, Fnd_Global.user_id, p_created_by),
380 DECODE(p_last_update_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
381 p_last_update_date),
382 DECODE(p_last_updated_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
383 NULL, Fnd_Global.user_id, p_last_updated_by),
384 DECODE(p_last_update_login, Fnd_Api.G_MISS_NUM, Fnd_Global.login_id,
385 NULL, Fnd_Global.login_id, p_last_update_login),
386 L.LANGUAGE_CODE,
387 DECODE(P_SOURCE_LANG, Fnd_Api.G_MISS_CHAR,USERENV('LANG'), NULL, USERENV('LANG'),P_SOURCE_LANG),
388 DECODE(p_CITEM_TRANSLATION_STATUS,Fnd_Api.G_MISS_CHAR,NULL,p_CITEM_TRANSLATION_STATUS)
389 FROM FND_LANGUAGES L
390 WHERE L.INSTALLED_FLAG IN ('I', 'B')
391 AND NOT EXISTS
392 (SELECT NULL
393 FROM IBC_CITEM_VERSIONS_TL T
394 WHERE T.CITEM_VERSION_ID = p_CITEM_VERSION_ID
395 AND T.LANGUAGE = L.LANGUAGE_CODE);
396
397 -- Added by svatsa
398 populate_attachments (p_citem_version_id => p_CITEM_VERSION_ID
399 ,p_base_lang => P_SOURCE_LANG
400 );
401
402 END POPULATE_ALL_LANG;
403
404
405 PROCEDURE INSERT_BASE_LANG (
406 x_ROWID OUT NOCOPY VARCHAR2,
407 px_CITEM_VERSION_ID IN OUT NOCOPY NUMBER,
408 p_CONTENT_ITEM_ID IN NUMBER,
409 p_VERSION_NUMBER IN NUMBER,
410 p_CITEM_VERSION_STATUS IN VARCHAR2,
411 p_START_DATE IN DATE,
412 p_END_DATE IN DATE,
413 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
414 p_ATTRIBUTE_FILE_ID IN NUMBER,
415 p_ATTACHMENT_ATTRIBUTE_CODE IN VARCHAR2,
416 P_SOURCE_LANG IN VARCHAR2 ,--DEFAULT USERENV('LANG'),
417 p_ATTACHMENT_FILE_ID IN NUMBER ,--DEFAULT NULL,
418 p_CONTENT_ITEM_NAME IN VARCHAR2,
419 p_ATTACHMENT_FILE_NAME IN VARCHAR2 ,--DEFAULT NULL,
420 p_DESCRIPTION IN VARCHAR2,
421 p_DEFAULT_RENDITION_MIME_TYPE IN VARCHAR2 ,--DEFAULT NULL,
422 p_CREATION_DATE IN DATE ,--DEFAULT NULL,
423 p_CREATED_BY IN NUMBER ,--DEFAULT NULL,
424 p_LAST_UPDATE_DATE IN DATE ,--DEFAULT NULL,
425 p_LAST_UPDATED_BY IN NUMBER ,--DEFAULT NULL,
426 p_LAST_UPDATE_LOGIN IN NUMBER ,--DEFAULT NULL,
427 p_CITEM_TRANSLATION_STATUS IN VARCHAR2 --DEFAULT NULL
428 )
429
430
431 IS
432 CURSOR C IS SELECT ROWID FROM IBC_CITEM_VERSIONS_B
433 WHERE CITEM_VERSION_ID = px_CITEM_VERSION_ID;
434 CURSOR c2 IS SELECT ibc_citem_versions_s1.NEXTVAL FROM dual;
435
436 CURSOR c_citem_dirnode(p_content_item_id NUMBER)
437 IS
438 SELECT directory_node_id
439 FROM ibc_content_items citems
440 WHERE content_item_id = p_content_item_id;
441
442 l_dirnode NUMBER;
443
444 l_object_type VARCHAR2(30);
445 l_object_id NUMBER;
446
447 G_API_NAME CONSTANT VARCHAR2(30) := 'INSERT_BASE_LANG';
448
449 BEGIN
450
451 OPEN c_citem_dirnode(p_content_item_id);
452 FETCH c_citem_dirnode INTO l_dirnode;
453 CLOSE c_citem_dirnode;
454
455 IF IBC_UTILITIES_PVT.is_name_already_used(p_dir_node_id => l_dirnode,
456 p_name => p_content_item_name,
457 p_language => USERENV('lang'),
458 p_chk_content_item_id => p_content_item_id,
459 x_object_type => l_object_type,
460 x_object_id => l_object_id)
461 THEN
462 IF l_object_type = 'DIRNODE' THEN
463 Fnd_Message.Set_Name('IBC', 'IBC_NAME_ALREADY_FOLDER');
464 ELSE
465 Fnd_Message.Set_Name('IBC', 'IBC_CITEM_NAME_UNIQUE');
466 Fnd_Message.Set_token('NEW_ITEM_NAME' , p_content_item_name);
467 Fnd_Message.Set_token('CONFLICTING_ITEM_NAME' , IBC_UTILITIES_PVT.get_citem_name(l_object_id));
468 END IF;
469 Fnd_Msg_Pub.ADD;
470 RAISE Fnd_Api.G_EXC_ERROR;
471 END IF;
472
473
474 Ibc_Content_Items_Pkg.UPDATE_ROW (
475 p_CONTENT_ITEM_ID =>p_CONTENT_ITEM_ID
476 ,px_OBJECT_VERSION_NUMBER =>px_object_version_number
477 ,p_last_updated_by =>p_last_updated_by);
478
479 -- Primary key validation check
480
481 IF ((px_CITEM_VERSION_ID IS NULL) OR
482 (px_CITEM_VERSION_ID = Fnd_Api.G_MISS_NUM))
483 THEN
484 OPEN c2;
485 FETCH c2 INTO px_CITEM_VERSION_ID;
486 CLOSE c2;
487 END IF;
488
489 INSERT INTO IBC_CITEM_VERSIONS_B (
490 CITEM_VERSION_ID,
491 CONTENT_ITEM_ID,
492 VERSION_NUMBER,
493 CITEM_VERSION_STATUS,
494 START_DATE,
495 END_DATE,
496 OBJECT_VERSION_NUMBER,
497 CREATION_DATE,
498 CREATED_BY,
499 LAST_UPDATE_DATE,
500 LAST_UPDATED_BY,
501 LAST_UPDATE_LOGIN
502 ) VALUES (
503 DECODE(px_CITEM_VERSION_ID,Fnd_Api.G_MISS_NUM,NULL,px_CITEM_VERSION_ID),
504 DECODE(p_CONTENT_ITEM_ID,Fnd_Api.G_MISS_NUM,NULL,p_CONTENT_ITEM_ID),
505 DECODE(p_VERSION_NUMBER,Fnd_Api.G_MISS_NUM,NULL,p_VERSION_NUMBER),
506 DECODE(p_CITEM_VERSION_STATUS,Fnd_Api.G_MISS_CHAR,NULL,p_CITEM_VERSION_STATUS),
507 DECODE(p_START_DATE,Fnd_Api.G_MISS_DATE,NULL,p_START_DATE),
508 DECODE(p_END_DATE,Fnd_Api.G_MISS_DATE,NULL,p_END_DATE),
509 DECODE(px_OBJECT_VERSION_NUMBER,Fnd_Api.G_MISS_NUM,1,px_OBJECT_VERSION_NUMBER),
510 DECODE(p_creation_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
511 p_creation_date),
512 DECODE(p_created_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
513 NULL, Fnd_Global.user_id, p_created_by),
514 DECODE(p_last_update_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
515 p_last_update_date),
516 DECODE(p_last_updated_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
517 NULL, Fnd_Global.user_id, p_last_updated_by),
518 DECODE(p_last_update_login, Fnd_Api.G_MISS_NUM, Fnd_Global.login_id,
519 NULL, Fnd_Global.login_id, p_last_update_login)
520 );
521
522 INSERT INTO IBC_CITEM_VERSIONS_TL (
523 CITEM_VERSION_ID,
524 ATTRIBUTE_FILE_ID,
525 ATTACHMENT_ATTRIBUTE_CODE,
526 CONTENT_ITEM_NAME,
527 ATTACHMENT_FILE_ID,
528 ATTACHMENT_FILE_NAME,
529 DESCRIPTION,
530 DEFAULT_RENDITION_MIME_TYPE,
531 CREATION_DATE,
532 CREATED_BY,
533 LAST_UPDATE_DATE,
534 LAST_UPDATED_BY,
535 LAST_UPDATE_LOGIN,
536 LANGUAGE,
537 SOURCE_LANG,
538 CITEM_TRANSLATION_STATUS
539 ) VALUES (
540 DECODE(px_CITEM_VERSION_ID,Fnd_Api.G_MISS_NUM,NULL,px_CITEM_VERSION_ID),
541 DECODE(p_ATTRIBUTE_FILE_ID,Fnd_Api.G_MISS_NUM,NULL,p_ATTRIBUTE_FILE_ID),
542 DECODE(p_ATTACHMENT_ATTRIBUTE_CODE,Fnd_Api.G_MISS_CHAR,NULL,p_ATTACHMENT_ATTRIBUTE_CODE),
543 DECODE(p_CONTENT_ITEM_NAME,Fnd_Api.G_MISS_CHAR,NULL,p_CONTENT_ITEM_NAME),
544 DECODE(p_ATTACHMENT_FILE_ID,Fnd_Api.G_MISS_NUM,NULL,p_ATTACHMENT_FILE_ID),
545 DECODE(p_ATTACHMENT_FILE_NAME,Fnd_Api.G_MISS_CHAR,NULL,p_ATTACHMENT_FILE_NAME),
546 DECODE(p_DESCRIPTION,Fnd_Api.G_MISS_CHAR,NULL,p_DESCRIPTION),
547 DECODE(p_DEFAULT_RENDITION_MIME_TYPE,Fnd_Api.G_MISS_CHAR,NULL,p_DEFAULT_RENDITION_MIME_TYPE),
548 DECODE(p_creation_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,p_creation_date) ,
549 DECODE(p_created_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
550 NULL, Fnd_Global.user_id, p_created_by),
551 DECODE(p_last_update_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
552 p_last_update_date),
553 DECODE(p_last_updated_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
554 NULL, Fnd_Global.user_id, p_last_updated_by),
555 DECODE(p_last_update_login, Fnd_Api.G_MISS_NUM, Fnd_Global.login_id,
556 NULL, Fnd_Global.login_id, p_last_update_login),
557 DECODE(P_SOURCE_LANG, Fnd_Api.G_MISS_CHAR,USERENV('LANG'), NULL, USERENV('LANG'),P_SOURCE_LANG),
558 DECODE(P_SOURCE_LANG, Fnd_Api.G_MISS_CHAR,USERENV('LANG'), NULL, USERENV('LANG'),P_SOURCE_LANG),
559 DECODE(p_CITEM_TRANSLATION_STATUS,Fnd_Api.G_MISS_CHAR,NULL,p_CITEM_TRANSLATION_STATUS)
560 );
561
562 OPEN c;
563 FETCH c INTO x_ROWID;
564 IF (c%NOTFOUND) THEN
565 CLOSE c;
566 Fnd_Message.Set_Name('IBC', 'IBC_ERROR_RETURNED');
567 Fnd_Message.Set_token('PKG_NAME' , G_pkg_name);
568 Fnd_Message.Set_token('API_NAME' , G_api_name);
569 Fnd_Msg_Pub.ADD;
570 RAISE Fnd_Api.G_EXC_ERROR;
571 --RAISE NO_DATA_FOUND;
572 END IF;
573 CLOSE c;
574
575 /*
576 Due to new requirement (03-09-2004) Same name accross versions is
577 not being enforced anymore -- Reverted
578 */
579
580 -- Reverting back the previous change. In R12 Enhancement Bug 3664840 Content Item Name should be the same
581 -- across all versions for that language
582
583 -- Update Content Item Name for all versions of current language
584 UPDATE IBC_CITEM_VERSIONS_TL
585 SET CONTENT_ITEM_NAME = DECODE(p_content_item_name,Fnd_Api.G_MISS_CHAR,NULL,NULL,content_item_name,p_content_item_name)
586 WHERE citem_version_id IN (SELECT citem_version_id
587 FROM IBC_CITEM_VERSIONS_B verb
588 WHERE verb.content_item_id = p_content_item_id);
589
590 END INSERT_BASE_LANG;
591
592 PROCEDURE LOCK_ROW (
593 p_CITEM_VERSION_ID IN NUMBER,
594 p_CONTENT_ITEM_ID IN NUMBER,
595 p_VERSION_NUMBER IN NUMBER,
596 p_CITEM_VERSION_STATUS IN VARCHAR2,
597 p_START_DATE IN DATE,
598 p_END_DATE IN DATE,
599 p_OBJECT_VERSION_NUMBER IN NUMBER,
600 p_ATTRIBUTE_FILE_ID IN NUMBER,
601 p_ATTACHMENT_FILE_ID IN NUMBER,
602 p_CONTENT_ITEM_NAME IN VARCHAR2,
603 p_ATTACHMENT_FILE_NAME IN VARCHAR2,
604 p_DESCRIPTION IN VARCHAR2
605 ) IS
606 CURSOR c IS SELECT
607 CONTENT_ITEM_ID,
608 VERSION_NUMBER,
609 CITEM_VERSION_STATUS,
610 START_DATE,
611 END_DATE,
612 OBJECT_VERSION_NUMBER
613 FROM IBC_CITEM_VERSIONS_B
614 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID
615 FOR UPDATE OF CITEM_VERSION_ID NOWAIT;
616 recinfo c%ROWTYPE;
617
618 CURSOR c1 IS SELECT
619 ATTRIBUTE_FILE_ID,
620 CONTENT_ITEM_NAME,
621 DESCRIPTION,
622 DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
623 FROM IBC_CITEM_VERSIONS_TL
624 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID
625 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
626 FOR UPDATE OF CITEM_VERSION_ID NOWAIT;
627 BEGIN
628 OPEN c;
629 FETCH c INTO recinfo;
630 IF (c%NOTFOUND) THEN
631 CLOSE c;
632 Fnd_Message.set_name('FND', 'FORM_RECORD_DELETED');
633 App_Exception.raise_exception;
634 END IF;
635 CLOSE c;
636 IF ( (recinfo.CONTENT_ITEM_ID = p_CONTENT_ITEM_ID)
637 AND (recinfo.VERSION_NUMBER = p_VERSION_NUMBER)
638 AND ((recinfo.CITEM_VERSION_STATUS = p_CITEM_VERSION_STATUS)
639 OR ((recinfo.CITEM_VERSION_STATUS IS NULL) AND (p_CITEM_VERSION_STATUS IS NULL)))
640 AND ((recinfo.START_DATE = p_START_DATE)
641 OR ((recinfo.START_DATE IS NULL) AND (p_START_DATE IS NULL)))
642 AND ((recinfo.END_DATE = p_END_DATE)
643 OR ((recinfo.END_DATE IS NULL) AND (p_END_DATE IS NULL)))
644 AND (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER))
645 THEN
646 NULL;
647 ELSE
648 Fnd_Message.set_name('FND', 'FORM_RECORD_CHANGED');
649 App_Exception.raise_exception;
650 END IF;
651
652 FOR tlinfo IN c1 LOOP
653 IF (tlinfo.BASELANG = 'Y') THEN
654 IF ( (tlinfo.ATTRIBUTE_FILE_ID = p_ATTRIBUTE_FILE_ID)
655 AND (tlinfo.CONTENT_ITEM_NAME = p_CONTENT_ITEM_NAME)
656 AND ((tlinfo.DESCRIPTION = p_DESCRIPTION)
657 OR ((tlinfo.DESCRIPTION IS NULL) AND (p_DESCRIPTION IS NULL)))
658 ) THEN
659 NULL;
660 ELSE
661 Fnd_Message.set_name('FND', 'FORM_RECORD_CHANGED');
662 App_Exception.raise_exception;
663 END IF;
664 END IF;
665 END LOOP;
666 RETURN;
667 END LOCK_ROW;
668
669 PROCEDURE UPDATE_ROW (
670 p_citem_version_id IN NUMBER
671 ,p_content_item_id IN NUMBER --DEFAULT NULL
672 ,p_source_lang IN VARCHAR2 --DEFAULT USERENV('LANG')
673 ,p_version_number IN NUMBER --DEFAULT NULL
674 ,p_citem_version_status IN VARCHAR2 --DEFAULT NULL
675 ,p_attachment_attribute_code IN VARCHAR2 --DEFAULT NULL
676 ,p_start_date IN DATE --DEFAULT NULL
677 ,p_end_date IN DATE --DEFAULT NULL
678 ,px_object_version_number IN OUT NOCOPY NUMBER
679 ,p_attribute_file_id IN NUMBER --DEFAULT NULL
680 ,p_attachment_file_id IN NUMBER --DEFAULT NULL
681 ,p_content_item_name IN VARCHAR2 --DEFAULT NULL
682 ,p_attachment_file_name IN VARCHAR2 --DEFAULT NULL
683 ,p_description IN VARCHAR2 --DEFAULT NULL
684 ,p_default_rendition_mime_type IN VARCHAR2 --DEFAULT NULL
685 ,p_last_update_date IN DATE --DEFAULT NULL
686 ,p_last_updated_by IN NUMBER --DEFAULT NULL
687 ,p_last_update_login IN NUMBER --DEFAULT NULL
688 ,p_citem_translation_status IN VARCHAR2 --DEFAULT NULL
689 )
690 IS
691
692 CURSOR c_citem_dirnode(p_content_item_id NUMBER)
693 IS
694 SELECT directory_node_id
695 FROM ibc_content_items citems
696 WHERE content_item_id = p_content_item_id;
697
698 l_dirnode NUMBER;
699
700 l_object_type VARCHAR2(30);
701 l_object_id NUMBER;
702
703 G_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_ROW';
704
705 BEGIN
706
707 -- Validation of Unique Name in a particular directory for a especific
708 -- Language.
709 OPEN c_citem_dirnode(p_content_item_id);
710 FETCH c_citem_dirnode INTO l_dirnode;
711 CLOSE c_citem_dirnode;
712
713 IF IBC_UTILITIES_PVT.is_name_already_used(p_dir_node_id => l_dirnode,
714 p_name => p_content_item_name,
715 p_language => USERENV('lang'),
716 p_chk_content_item_id => p_content_item_id,
717 x_object_type => l_object_type,
718 x_object_id => l_object_id)
719 THEN
720 IF l_object_type = 'DIRNODE' THEN
721 Fnd_Message.Set_Name('IBC', 'IBC_NAME_ALREADY_FOLDER');
722 ELSE
723 Fnd_Message.Set_Name('IBC', 'IBC_CITEM_NAME_UNIQUE');
724 Fnd_Message.Set_token('NEW_ITEM_NAME' , p_content_item_name);
725 Fnd_Message.Set_token('CONFLICTING_ITEM_NAME' , IBC_UTILITIES_PVT.get_citem_name(l_object_id));
726 END IF;
727 Fnd_Msg_Pub.ADD;
728 RAISE Fnd_Api.G_EXC_ERROR;
729 END IF;
730
731
732 Ibc_Content_Items_Pkg.UPDATE_ROW (
733 p_CONTENT_ITEM_ID =>p_CONTENT_ITEM_ID
734 ,px_OBJECT_VERSION_NUMBER =>px_object_version_number
735 ,p_last_updated_by =>p_last_updated_by);
736
737
738 UPDATE IBC_CITEM_VERSIONS_B SET
739 content_item_id = DECODE(p_content_item_id,FND_API.G_MISS_NUM,NULL,NULL,content_item_id,p_content_item_id)
740 ,version_number = DECODE(p_version_number,FND_API.G_MISS_NUM,NULL,NULL,version_number,p_version_number)
741 ,citem_version_status = DECODE(p_citem_version_status,FND_API.G_MISS_CHAR,NULL,NULL,citem_version_status,p_citem_version_status)
742 ,start_date = DECODE(p_start_date,FND_API.G_MISS_DATE,NULL,NULL,start_date,p_start_date)
743 ,end_date = DECODE(p_end_date,FND_API.G_MISS_DATE,NULL,NULL,end_date,p_end_date)
744 ,object_version_number = px_object_version_number
745 ,last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
746 ,last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
747 ,last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
748 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID;
749
750 /*AND object_version_number = DECODE(p_object_version_number,
751 FND_API.G_MISS_NUM,
752 object_version_number,
753 NULL,
754 object_version_number,
755 p_object_version_number);*/
756
757
758 IF (SQL%NOTFOUND) THEN
759 Fnd_Message.Set_Name('IBC', 'IBC_ERROR_RETURNED');
760 Fnd_Message.Set_token('PKG_NAME' , G_pkg_name);
761 Fnd_Message.Set_token('API_NAME' , G_api_name);
762 Fnd_Msg_Pub.ADD;
763 RAISE Fnd_Api.G_EXC_ERROR;
764 END IF;
765
766 --Bug Fix:3589057
767 UPDATE IBC_CITEM_VERSIONS_TL SET
768 content_item_name = DECODE(p_content_item_name,FND_API.G_MISS_CHAR,NULL,NULL,content_item_name,p_content_item_name)
769 ,description = DECODE(p_description,FND_API.G_MISS_CHAR,NULL,NULL,description,p_description)
770 ,last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
771 ,last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
772 ,last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
773 ,source_lang = DECODE(p_source_lang,FND_API.G_MISS_CHAR,USERENV('LANG'),NULL,USERENV('LANG'),p_source_lang)
774 ,TEXTIDX = 'X'
775 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID
776 AND P_SOURCE_LANG IN (LANGUAGE, SOURCE_LANG);
777
778 IF (SQL%NOTFOUND) THEN
779 Fnd_Message.Set_Name('IBC', 'IBC_ERROR_RETURNED');
780 Fnd_Message.Set_token('PKG_NAME' , G_pkg_name);
781 Fnd_Message.Set_token('API_NAME' , G_api_name);
782 Fnd_Msg_Pub.ADD;
783 RAISE Fnd_Api.G_EXC_ERROR;
784 END IF;
785
786 --Bug Fix:3589057
787 UPDATE IBC_CITEM_VERSIONS_TL SET
788 attribute_file_id = DECODE(p_attribute_file_id,FND_API.G_MISS_NUM,NULL,NULL,attribute_file_id,p_attribute_file_id)
789 ,attachment_file_id = DECODE(p_attachment_file_id,FND_API.G_MISS_NUM,NULL,NULL,attachment_file_id,p_attachment_file_id)
790 ,attachment_file_name = DECODE(p_attachment_file_name,FND_API.G_MISS_CHAR,NULL,NULL,attachment_file_name,p_attachment_file_name)
791 ,attachment_attribute_code = DECODE(p_attachment_attribute_code,FND_API.G_MISS_CHAR,NULL,NULL,attachment_attribute_code,p_attachment_attribute_code)
792 ,default_rendition_mime_type = DECODE(p_default_rendition_mime_type,FND_API.G_MISS_CHAR,NULL,NULL,default_rendition_mime_type,p_default_rendition_mime_type)
793 ,citem_translation_status = DECODE(p_citem_translation_status,FND_API.G_MISS_CHAR,NULL,NULL,citem_translation_status,p_citem_translation_status)
794 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID
795 AND P_SOURCE_LANG IN (LANGUAGE);
796
797 IF (SQL%NOTFOUND) THEN
798 Fnd_Message.Set_Name('IBC', 'IBC_ERROR_RETURNED');
799 Fnd_Message.Set_token('PKG_NAME' , G_pkg_name);
800 Fnd_Message.Set_token('API_NAME' , G_api_name);
801 Fnd_Msg_Pub.ADD;
802 RAISE Fnd_Api.G_EXC_ERROR;
803 END IF;
804
805
806 /*
807 Due to new requirement (03-09-2004) Same name accross versions is
808 not being enforced anymore -- Reverted
809 */
810
811 -- Reverting back the previous change. In R12 Enhancement Bug 3664840 Content Item Name should be the same
812 -- across all versions for that language
813
814 -- Update Content Item Name for all versions of current language
815 UPDATE IBC_CITEM_VERSIONS_TL
816 SET CONTENT_ITEM_NAME = DECODE(p_content_item_name,FND_API.G_MISS_CHAR,NULL,NULL,content_item_name,p_content_item_name)
817 WHERE P_SOURCE_LANG IN (LANGUAGE, SOURCE_LANG)
818 AND EXISTS (SELECT 'X'
819 FROM IBC_CITEM_VERSIONS_B verb
820 WHERE verb.content_item_id = p_content_item_id
821 AND IBC_CITEM_VERSIONS_TL.citem_version_id = verb.citem_version_id
822 );
823
824 --
825 -- submits a concurrent request
826 -- to sync Content Text indexes.
827 DECLARE
828 x_request_id NUMBER;
829 x_return_status VARCHAR2(100);
830 BEGIN
831
832 IBC_CONTENT_SYNC_INDEX_PKG.Request_Content_Sync_Index( x_request_id,x_return_status);
833
834 EXCEPTION WHEN OTHERS THEN
835 NULL;
836 END;
837 --
838 -- end of submission
839 --
840
841 END UPDATE_ROW;
842
843 PROCEDURE DELETE_ROW (
844 p_CITEM_VERSION_ID IN NUMBER
845 ) IS
846
847 G_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_ROW';
848
849 BEGIN
850 DELETE FROM IBC_CITEM_VERSIONS_TL
851 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID;
852
853 IF (SQL%NOTFOUND) THEN
854 Fnd_Message.Set_Name('IBC', 'IBC_ERROR_RETURNED');
855 Fnd_Message.Set_token('PKG_NAME' , G_pkg_name);
856 Fnd_Message.Set_token('API_NAME' , G_api_name);
857 Fnd_Msg_Pub.ADD;
858 RAISE Fnd_Api.G_EXC_ERROR;
859 --RAISE NO_DATA_FOUND;
860 END IF;
861
862 DELETE FROM IBC_CITEM_VERSIONS_B
863 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID;
864
865 IF (SQL%NOTFOUND) THEN
866 Fnd_Message.Set_Name('IBC', 'IBC_ERROR_RETURNED');
867 Fnd_Message.Set_token('PKG_NAME' , G_pkg_name);
868 Fnd_Message.Set_token('API_NAME' , G_api_name);
869 Fnd_Msg_Pub.ADD;
870 RAISE Fnd_Api.G_EXC_ERROR;
871 -- RAISE NO_DATA_FOUND;
872 END IF;
873 END DELETE_ROW;
874
875 PROCEDURE ADD_LANGUAGE
876 IS
877 BEGIN
878 DELETE FROM IBC_CITEM_VERSIONS_TL T
879 WHERE NOT EXISTS
880 (SELECT NULL
881 FROM IBC_CITEM_VERSIONS_B B
882 WHERE B.CITEM_VERSION_ID = T.CITEM_VERSION_ID
883 );
884
885 UPDATE IBC_CITEM_VERSIONS_TL T SET (
886 ATTACHMENT_ATTRIBUTE_CODE,
887 ATTRIBUTE_FILE_ID,
888 CONTENT_ITEM_NAME,
889 DESCRIPTION,
890 ATTACHMENT_FILE_NAME,
891 ATTACHMENT_FILE_ID,
892 default_rendition_mime_type,
893 citem_translation_status
894 ) = (SELECT
895 B.ATTACHMENT_ATTRIBUTE_CODE,
896 B.ATTRIBUTE_FILE_ID,
897 B.CONTENT_ITEM_NAME,
898 B.DESCRIPTION,
899 B.ATTACHMENT_FILE_NAME,
900 B.ATTACHMENT_FILE_ID,
901 B.default_rendition_mime_type,
902 B.citem_translation_status
903 FROM IBC_CITEM_VERSIONS_TL B
904 WHERE B.CITEM_VERSION_ID = T.CITEM_VERSION_ID
905 AND B.LANGUAGE = T.SOURCE_LANG)
906 WHERE (
907 T.CITEM_VERSION_ID,
908 T.LANGUAGE
909 ) IN (SELECT
910 SUBT.CITEM_VERSION_ID,
911 SUBT.LANGUAGE
912 FROM IBC_CITEM_VERSIONS_TL SUBB, IBC_CITEM_VERSIONS_TL SUBT
913 WHERE SUBB.CITEM_VERSION_ID = SUBT.CITEM_VERSION_ID
914 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
915 AND (SUBB.ATTACHMENT_ATTRIBUTE_CODE <> SUBT.ATTACHMENT_ATTRIBUTE_CODE
916 OR (SUBB.ATTACHMENT_ATTRIBUTE_CODE IS NULL AND SUBT.ATTACHMENT_ATTRIBUTE_CODE IS NOT NULL)
917 OR (SUBB.ATTACHMENT_ATTRIBUTE_CODE IS NOT NULL AND SUBT.ATTACHMENT_ATTRIBUTE_CODE IS NULL)
918 OR SUBB.ATTRIBUTE_FILE_ID <> SUBT.ATTRIBUTE_FILE_ID
919 OR (SUBB.ATTRIBUTE_FILE_ID IS NULL AND SUBT.ATTRIBUTE_FILE_ID IS NOT NULL)
920 OR (SUBB.ATTRIBUTE_FILE_ID IS NOT NULL AND SUBT.ATTRIBUTE_FILE_ID IS NULL)
921 OR SUBB.CONTENT_ITEM_NAME <> SUBT.CONTENT_ITEM_NAME
922 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
923 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
924 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
925 OR SUBB.ATTACHMENT_FILE_NAME <> SUBT.ATTACHMENT_FILE_NAME
926 OR (SUBB.ATTACHMENT_FILE_NAME IS NULL AND SUBT.ATTACHMENT_FILE_NAME IS NOT NULL)
927 OR (SUBB.ATTACHMENT_FILE_NAME IS NOT NULL AND SUBT.ATTACHMENT_FILE_NAME IS NULL)
928 OR SUBB.ATTACHMENT_FILE_ID <> SUBT.ATTACHMENT_FILE_ID
929 OR (SUBB.ATTACHMENT_FILE_ID IS NULL AND SUBT.ATTACHMENT_FILE_ID IS NOT NULL)
930 OR (SUBB.ATTACHMENT_FILE_ID IS NOT NULL AND SUBT.ATTACHMENT_FILE_ID IS NULL)
931 OR (SUBB.default_rendition_mime_type IS NULL AND SUBT.default_rendition_mime_type IS NOT NULL)
932 OR (SUBB.default_rendition_mime_type IS NOT NULL AND SUBT.default_rendition_mime_type IS NULL)
933 ));
934
935 INSERT INTO IBC_CITEM_VERSIONS_TL (
936 ATTACHMENT_FILE_ID,
937 ATTACHMENT_FILE_NAME,
938 CITEM_VERSION_ID,
939 ATTACHMENT_ATTRIBUTE_CODE,
940 ATTRIBUTE_FILE_ID,
941 CONTENT_ITEM_NAME,
942 default_rendition_mime_type,
943 DESCRIPTION,
944 CREATED_BY,
945 CREATION_DATE,
946 LAST_UPDATED_BY,
947 LAST_UPDATE_DATE,
948 LAST_UPDATE_LOGIN,
949 SECURITY_GROUP_ID,
950 LANGUAGE,
951 SOURCE_LANG,
952 citem_translation_status
953 ) SELECT /*+ ORDERED */
954 B.ATTACHMENT_FILE_ID,
955 B.ATTACHMENT_FILE_NAME,
956 B.CITEM_VERSION_ID,
957 B.ATTACHMENT_ATTRIBUTE_CODE,
958 B.ATTRIBUTE_FILE_ID,
959 B.CONTENT_ITEM_NAME,
960 B.default_rendition_mime_type,
961 B.DESCRIPTION,
962 B.CREATED_BY,
963 B.CREATION_DATE,
964 B.LAST_UPDATED_BY,
965 B.LAST_UPDATE_DATE,
966 B.LAST_UPDATE_LOGIN,
967 B.SECURITY_GROUP_ID,
968 L.LANGUAGE_CODE,
969 B.SOURCE_LANG,
970 B.citem_translation_status
971 FROM IBC_CITEM_VERSIONS_TL B, FND_LANGUAGES L
972 WHERE L.INSTALLED_FLAG IN ('I', 'B')
973 AND B.LANGUAGE = USERENV('LANG')
974 AND NOT EXISTS
975 (SELECT NULL
976 FROM IBC_CITEM_VERSIONS_TL T
977 WHERE T.CITEM_VERSION_ID = B.CITEM_VERSION_ID
978 AND T.LANGUAGE = L.LANGUAGE_CODE);
979 END ADD_LANGUAGE;
980
981
982 PROCEDURE LOAD_ROW (
983 p_UPLOAD_MODE IN VARCHAR2,
984 p_CITEM_VERSION_ID IN NUMBER,
985 p_CONTENT_ITEM_ID IN NUMBER,
986 p_VERSION_NUMBER IN NUMBER,
987 p_CITEM_VERSION_STATUS IN VARCHAR2,
988 p_START_DATE IN DATE,
989 p_END_DATE IN DATE,
990 p_ATTACHMENT_ATTRIBUTE_CODE IN VARCHAR2,
991 p_ATTRIBUTE_FILE_ID IN NUMBER ,
992 p_ATTACHMENT_FILE_ID IN NUMBER ,--DEFAULT NULL,
993 p_CONTENT_ITEM_NAME IN VARCHAR2,
994 p_ATTACHMENT_FILE_NAME IN VARCHAR2 ,--DEFAULT NULL,
995 p_DESCRIPTION IN VARCHAR2,
996 p_DEFAULT_RENDITION_MIME_TYPE IN VARCHAR2 ,--DEFAULT NULL,
997 p_OWNER IN VARCHAR2,
998 p_CITEM_TRANSLATION_STATUS IN VARCHAR2, --DEFAULT NULL
999 p_LAST_UPDATE_DATE IN VARCHAR2 ) IS
1000
1001 CURSOR c_citem_dirnode(p_content_item_id NUMBER)
1002 IS
1003 SELECT directory_node_id
1004 FROM ibc_content_items citems
1005 WHERE content_item_id = p_content_item_id;
1006
1007 l_dirnode NUMBER;
1008
1009 l_user_id NUMBER := 0;
1010 l_row_id VARCHAR2(64);
1011 lx_object_version_number NUMBER;
1012 lx_citem_version_id NUMBER := p_citem_version_id;
1013 l_object_type VARCHAR2(30);
1014 l_object_id NUMBER;
1015 l_last_update_date DATE;
1016
1017 db_user_id NUMBER := 0;
1018 db_last_update_date DATE;
1019
1020 BEGIN
1021
1022
1023 --get last updated by user id
1024 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
1025
1026 --translate data type VARCHAR2 to DATE for last_update_date
1027 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
1028
1029 -- get updatedby and update_date values if existing in db
1030 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
1031 FROM IBC_CITEM_VERSIONS_B
1032 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID;
1033
1034 -- Validation of Unique Name in a particular directory for a especific
1035 -- Language.
1036 OPEN c_citem_dirnode(p_content_item_id);
1037 FETCH c_citem_dirnode INTO l_dirnode;
1038 CLOSE c_citem_dirnode;
1039
1040 IF IBC_UTILITIES_PVT.is_name_already_used(p_dir_node_id => l_dirnode,
1041 p_name => p_content_item_name,
1042 p_language => USERENV('lang'),
1043 p_chk_content_item_id => p_content_item_id,
1044 x_object_type => l_object_type,
1045 x_object_id => l_object_id)
1046 THEN
1047 IF l_object_type = 'DIRNODE' THEN
1048 Fnd_Message.Set_Name('IBC', 'IBC_NAME_ALREADY_FOLDER');
1049 ELSE
1050 Fnd_Message.Set_Name('IBC', 'IBC_CITEM_NAME_UNIQUE');
1051 Fnd_Message.Set_token('NEW_ITEM_NAME' , p_content_item_name);
1052 Fnd_Message.Set_token('CONFLICTING_ITEM_NAME' , IBC_UTILITIES_PVT.get_citem_name(l_object_id));
1053 END IF;
1054 Fnd_Msg_Pub.ADD;
1055 RAISE Fnd_Api.G_EXC_ERROR;
1056 END IF;
1057
1058 SELECT MAX(object_version_number) INTO lx_object_version_number
1059 FROM IBC_CONTENT_ITEMS
1060 WHERE content_item_id = p_content_item_id;
1061
1062 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
1063 db_user_id, db_last_update_date, p_upload_mode )) THEN
1064
1065 Ibc_Citem_Versions_Pkg.UPDATE_ROW (
1066 p_citem_version_id => NVL(p_citem_version_id,FND_API.G_MISS_NUM)
1067 ,p_content_item_id => NVL(p_content_item_id,FND_API.G_MISS_NUM)
1068 ,p_version_number => NVL(p_version_number,FND_API.G_MISS_NUM)
1069 ,p_citem_version_status => NVL(p_citem_version_status,FND_API.G_MISS_CHAR)
1070 ,p_start_date => NVL(p_start_date,FND_API.G_MISS_DATE)
1071 ,p_end_date => NVL(p_end_date,FND_API.G_MISS_DATE)
1072 ,p_attachment_attribute_code => NVL(p_attachment_attribute_code,FND_API.G_MISS_CHAR)
1073 ,p_attribute_file_id => NVL(p_attribute_file_id,FND_API.G_MISS_NUM)
1074 ,p_attachment_file_id => NVL(p_attachment_file_id,FND_API.G_MISS_NUM)
1075 ,p_content_item_name => NVL(p_content_item_name,FND_API.G_MISS_CHAR)
1076 ,p_attachment_file_name => NVL(p_attachment_file_name,FND_API.G_MISS_CHAR)
1077 ,p_description => NVL(p_description,FND_API.G_MISS_CHAR)
1078 ,p_default_rendition_mime_type => NVL(p_default_rendition_mime_type,FND_API.G_MISS_CHAR)
1079 ,p_last_updated_by => l_user_id
1080 ,p_last_update_date => l_last_update_date
1081 ,p_last_update_login => 0
1082 ,px_object_version_number => lx_object_version_number
1083 ,p_citem_translation_status => NVL(p_citem_translation_status,FND_API.G_MISS_CHAR)
1084 );
1085 END IF;
1086
1087 EXCEPTION
1088 WHEN no_data_found THEN
1089
1090 SELECT MAX(object_version_number) INTO lx_object_version_number
1091 FROM IBC_CONTENT_ITEMS
1092 WHERE content_item_id = p_content_item_id;
1093
1094 Ibc_Citem_Versions_Pkg.INSERT_ROW (
1095 X_ROWID => l_row_id,
1096 px_CITEM_VERSION_ID => lx_CITEM_VERSION_ID,
1097 p_CONTENT_ITEM_ID => p_CONTENT_ITEM_ID,
1098 p_VERSION_NUMBER => p_VERSION_NUMBER,
1099 p_CITEM_VERSION_STATUS => p_CITEM_VERSION_STATUS,
1100 p_START_DATE => p_START_DATE,
1101 p_END_DATE => p_END_DATE,
1102 p_ATTACHMENT_ATTRIBUTE_CODE => p_ATTACHMENT_ATTRIBUTE_CODE,
1103 p_ATTRIBUTE_FILE_ID => p_ATTRIBUTE_FILE_ID,
1104 p_ATTACHMENT_FILE_ID => p_ATTACHMENT_FILE_ID,
1105 p_CONTENT_ITEM_NAME => p_CONTENT_ITEM_NAME,
1106 p_ATTACHMENT_FILE_NAME => p_ATTACHMENT_FILE_NAME,
1107 p_DESCRIPTION => p_DESCRIPTION,
1108 p_DEFAULT_RENDITION_MIME_TYPE => p_DEFAULT_RENDITION_MIME_TYPE,
1109 p_CREATION_DATE => l_last_update_date,
1110 p_CREATED_BY => l_user_id,
1111 p_LAST_UPDATE_DATE => l_last_update_date,
1112 p_LAST_UPDATED_BY => l_user_id,
1113 p_LAST_UPDATE_LOGIN => 0,
1114 px_OBJECT_VERSION_NUMBER => lx_object_version_number,
1115 p_citem_translation_status => p_citem_translation_status);
1116
1117 END LOAD_ROW;
1118
1119 PROCEDURE TRANSLATE_ROW (
1120 p_UPLOAD_MODE IN VARCHAR2,
1121 p_CITEM_VERSION_ID IN NUMBER,
1122 p_ATTACHMENT_ATTRIBUTE_CODE IN VARCHAR2,
1123 p_ATTRIBUTE_FILE_ID IN NUMBER,
1124 p_ATTACHMENT_FILE_ID IN NUMBER,
1125 p_CONTENT_ITEM_NAME IN VARCHAR2,
1126 p_ATTACHMENT_FILE_NAME IN VARCHAR2,
1127 p_DESCRIPTION IN VARCHAR2,
1128 p_DEFAULT_RENDITION_MIME_TYPE IN VARCHAR2 ,--DEFAULT NULL,
1129 p_OWNER IN VARCHAR2,
1130 p_CITEM_TRANSLATION_STATUS IN VARCHAR2, --DEFAULT NULL
1131 p_LAST_UPDATE_DATE IN VARCHAR2 ) IS
1132
1133 l_user_id NUMBER := 0;
1134 l_last_update_date DATE;
1135
1136 db_user_id NUMBER := 0;
1137 db_last_update_date DATE;
1138
1139
1140 CURSOR c_citem_dirnode(p_content_item_id NUMBER)
1141 IS
1142 SELECT directory_node_id
1143 FROM ibc_content_items citems
1144 WHERE content_item_id = p_content_item_id;
1145
1146 l_dirnode NUMBER;
1147 l_object_type VARCHAR2(30);
1148 l_object_id NUMBER;
1149
1150 BEGIN
1151
1152 --get last updated by user id
1153 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
1154
1155 --translate data type VARCHAR2 to DATE for last_update_date
1156 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
1157
1158 -- get updatedby and update_date values if existing in db
1159 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
1160 FROM IBC_CITEM_VERSIONS_TL
1161 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID
1162 AND USERENV('LANG') IN (LANGUAGE, source_lang);
1163
1164 -- Validation of Unique Name in a particular directory for a especific
1165 -- Language.
1166 FOR r_citem IN (SELECT content_item_id
1167 FROM IBC_CITEM_VERSIONS_B
1168 WHERE citem_version_id = p_citem_version_id)
1169 LOOP
1170 OPEN c_citem_dirnode(r_citem.content_item_id);
1171 FETCH c_citem_dirnode INTO l_dirnode;
1172 CLOSE c_citem_dirnode;
1173 /*
1174 IF IBC_UTILITIES_PVT.is_name_already_used(p_dir_node_id => l_dirnode,
1175 p_name => p_content_item_name,
1176 p_language => USERENV('lang'),
1177 p_chk_content_item_id => r_citem.content_item_id,
1178 x_object_type => l_object_type,
1179 x_object_id => l_object_id)
1180 THEN
1181 IF l_object_type = 'DIRNODE' THEN
1182 Fnd_Message.Set_Name('IBC', 'IBC_NAME_ALREADY_FOLDER');
1183 ELSE
1184 Fnd_Message.Set_Name('IBC', 'IBC_CITEM_NAME_UNIQUE');
1185 Fnd_Message.Set_token('NEW_ITEM_NAME' , p_content_item_name);
1186 Fnd_Message.Set_token('CONFLICTING_ITEM_NAME' , IBC_UTILITIES_PVT.get_citem_name(l_object_id));
1187 END IF;
1188 Fnd_Msg_Pub.ADD;
1189 RAISE Fnd_Api.G_EXC_ERROR;
1190 END IF;
1191 */
1192 END LOOP;
1193
1194 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
1195 db_user_id, db_last_update_date, p_upload_mode )) THEN
1196
1197 -- Only update rows which have not been altered by user
1198 UPDATE IBC_CITEM_VERSIONS_TL
1199 SET
1200 ATTRIBUTE_FILE_ID = (SELECT ATTRIBUTE_FILE_ID FROM IBC_CITEM_VERSIONS_TL
1201 WHERE citem_version_id = p_citem_version_id
1202 AND LANGUAGE = 'US'),
1203 ATTACHMENT_FILE_ID = (SELECT ATTACHMENT_FILE_ID FROM IBC_CITEM_VERSIONS_TL
1204 WHERE citem_version_id = p_citem_version_id
1205 AND LANGUAGE = 'US'),
1206 CONTENT_ITEM_NAME = p_CONTENT_ITEM_NAME,
1207 ATTACHMENT_FILE_NAME = p_ATTACHMENT_FILE_NAME,
1208 ATTACHMENT_ATTRIBUTE_CODE = p_ATTACHMENT_ATTRIBUTE_CODE,
1209 DESCRIPTION = p_DESCRIPTION,
1210 DEFAULT_RENDITION_MIME_TYPE = p_DEFAULT_RENDITION_MIME_TYPE,
1211 source_lang = USERENV('LANG'),
1212 last_update_date = l_last_update_date,
1213 last_updated_by = l_user_id,
1214 last_update_login = 0,
1215 CITEM_TRANSLATION_STATUS = p_CITEM_TRANSLATION_STATUS
1216 WHERE CITEM_VERSION_ID = p_CITEM_VERSION_ID
1217 AND USERENV('LANG') IN (LANGUAGE, source_lang);
1218
1219 END IF;
1220
1221 END TRANSLATE_ROW;
1222
1223
1224 -- Added by svatsa
1225 PROCEDURE populate_attachments (
1226 p_citem_version_id IN NUMBER
1227 ,p_base_lang IN VARCHAR2
1228 ) IS
1229
1230 CURSOR version_cur (cv_version_id NUMBER
1231 ,cv_language VARCHAR2) IS
1232 SELECT attachment_file_id
1233 FROM ibc_citem_versions_tl
1234 WHERE citem_version_id = cv_version_id
1235 AND language = cv_language
1236 AND attachment_file_id IS NOT NULL;
1237 base_ver_rec version_cur%ROWTYPE;
1238 base_file_id NUMBER := 0;
1239 trans_file_id NUMBER := null;
1240
1241 CURSOR trans_ver_cur (cv_version_id NUMBER
1242 ,cv_base_language VARCHAR2) IS
1243 SELECT citem_version_id, language, attachment_file_id
1244 FROM ibc_citem_versions_tl
1245 WHERE citem_version_id = cv_version_id
1246 AND language <> cv_base_language FOR UPDATE;
1247 trans_ver_rec trans_ver_cur%ROWTYPE ;
1248
1249 isFile boolean := false;
1250 seq NUMBER := null;
1251
1252 BEGIN
1253
1254 OPEN version_cur(p_citem_version_id,p_base_lang);
1255 FETCH version_cur INTO base_file_id;
1256 IF version_cur%FOUND THEN
1257 isFile := true;
1258 END IF;
1259 CLOSE version_cur;
1260
1261 IF isFile THEN
1262 OPEN trans_ver_cur(p_citem_version_id,p_base_lang);
1263 LOOP
1264 FETCH trans_ver_cur INTO trans_ver_rec;
1265 EXIT WHEN trans_ver_cur%NOTFOUND;
1266 INSERT INTO fnd_lobs (file_id
1267 ,file_name
1268 ,file_content_type
1269 ,file_data
1270 ,upload_date
1271 ,expiration_date
1272 ,program_name
1273 ,program_tag
1274 ,oracle_charset
1275 ,file_format
1276 )
1277 SELECT fnd_lobs_s.nextval
1278 ,file_name
1279 ,file_content_type
1280 ,file_data
1281 ,upload_date
1282 ,expiration_date
1283 ,program_name
1284 ,program_tag
1285 ,oracle_charset
1286 ,file_format
1287 FROM fnd_lobs
1288 WHERE file_id = trans_ver_rec.attachment_file_id;
1289
1290 -- Update the file_id in ibc_citem_versions_tl
1291 UPDATE ibc_citem_versions_tl
1292 SET attachment_file_id = fnd_lobs_s.currval
1293 WHERE citem_version_id = trans_ver_rec.citem_version_id
1294 AND language = trans_ver_rec.language;
1295 END LOOP;
1296 CLOSE trans_ver_cur;
1297 END IF;
1298 END populate_attachments;
1299
1300 --Bug Fix:3597752
1301 PROCEDURE populate_all_attachments (
1302 p_citem_version_id IN NUMBER
1303 ,p_base_lang IN VARCHAR2
1304 ) IS
1305
1306 CURSOR version_cur (cv_version_id NUMBER
1307 ,cv_language VARCHAR2) IS
1308 SELECT attachment_file_id
1309 FROM ibc_citem_versions_tl
1310 WHERE citem_version_id = cv_version_id
1311 AND LANGUAGE = cv_language
1312 AND attachment_file_id IS NOT NULL;
1313 base_ver_rec version_cur%ROWTYPE;
1314 base_file_id NUMBER := 0;
1315 trans_file_id NUMBER := NULL;
1316
1317 CURSOR trans_ver_cur (cv_version_id NUMBER
1318 ,cv_base_language VARCHAR2) IS
1319 SELECT citem_version_id, LANGUAGE, attachment_file_id
1320 FROM ibc_citem_versions_tl
1321 WHERE citem_version_id = cv_version_id;
1322 -- AND LANGUAGE <> cv_base_language FOR UPDATE;
1323 trans_ver_rec trans_ver_cur%ROWTYPE ;
1324
1325 isFile BOOLEAN := FALSE;
1326 seq NUMBER := NULL;
1327
1328 BEGIN
1329
1330 OPEN version_cur(p_citem_version_id,p_base_lang);
1331 FETCH version_cur INTO base_file_id;
1332 IF version_cur%FOUND THEN
1333 isFile := TRUE;
1334 END IF;
1335 CLOSE version_cur;
1336
1337 IF isFile THEN
1338 OPEN trans_ver_cur(p_citem_version_id,p_base_lang);
1339 LOOP
1340 FETCH trans_ver_cur INTO trans_ver_rec;
1341 EXIT WHEN trans_ver_cur%NOTFOUND;
1342 INSERT INTO fnd_lobs (file_id
1343 ,file_name
1344 ,file_content_type
1345 ,file_data
1346 ,upload_date
1347 ,expiration_date
1348 ,program_name
1349 ,program_tag
1350 ,oracle_charset
1351 ,file_format
1352 )
1353 SELECT fnd_lobs_s.NEXTVAL
1354 ,file_name
1355 ,file_content_type
1356 ,file_data
1357 ,upload_date
1358 ,expiration_date
1359 ,program_name
1360 ,program_tag
1361 ,oracle_charset
1362 ,file_format
1363 FROM fnd_lobs
1364 WHERE file_id = trans_ver_rec.attachment_file_id;
1365
1366 -- Update the file_id in ibc_citem_versions_tl
1367 UPDATE ibc_citem_versions_tl
1368 SET attachment_file_id = fnd_lobs_s.CURRVAL
1369 WHERE citem_version_id = trans_ver_rec.citem_version_id
1370 AND LANGUAGE = trans_ver_rec.LANGUAGE;
1371 END LOOP;
1372 CLOSE trans_ver_cur;
1373 END IF;
1374 END populate_all_attachments;
1375
1376 PROCEDURE LOAD_SEED_ROW (
1377 p_UPLOAD_MODE IN VARCHAR2,
1378 p_CITEM_VERSION_ID IN NUMBER,
1379 p_CONTENT_ITEM_ID IN NUMBER,
1380 p_VERSION_NUMBER IN NUMBER,
1381 p_CITEM_VERSION_STATUS IN VARCHAR2,
1382 p_START_DATE IN DATE,
1383 p_END_DATE IN DATE,
1384 p_ATTACHMENT_ATTRIBUTE_CODE IN VARCHAR2,
1385 p_ATTRIBUTE_FILE_ID IN NUMBER ,
1386 p_ATTACHMENT_FILE_ID IN NUMBER DEFAULT NULL,
1387 p_CONTENT_ITEM_NAME IN VARCHAR2,
1388 p_ATTACHMENT_FILE_NAME IN VARCHAR2 DEFAULT NULL,
1389 p_DESCRIPTION IN VARCHAR2,
1390 p_DEFAULT_RENDITION_MIME_TYPE IN VARCHAR2 DEFAULT NULL,
1391 p_OWNER IN VARCHAR2,
1392 p_CITEM_TRANSLATION_STATUS IN VARCHAR2 DEFAULT NULL,
1393 p_LAST_UPDATE_DATE IN VARCHAR2 ) IS
1394 BEGIN
1395 IF (p_UPLOAD_MODE = 'NLS') THEN
1396 Ibc_Citem_Versions_Pkg.TRANSLATE_ROW (
1397 p_UPLOAD_MODE => p_UPLOAD_MODE,
1398 p_CITEM_VERSION_ID => p_CITEM_VERSION_ID,
1399 p_ATTACHMENT_ATTRIBUTE_CODE => p_ATTACHMENT_ATTRIBUTE_CODE,
1400 p_ATTRIBUTE_FILE_ID => p_ATTRIBUTE_FILE_ID,
1401 p_ATTACHMENT_FILE_ID => p_ATTACHMENT_FILE_ID,
1402 p_CONTENT_ITEM_NAME => p_CONTENT_ITEM_NAME ,
1403 p_ATTACHMENT_FILE_NAME => p_ATTACHMENT_FILE_NAME,
1404 p_DESCRIPTION => p_DESCRIPTION,
1405 p_DEFAULT_RENDITION_MIME_TYPE => p_DEFAULT_RENDITION_MIME_TYPE,
1406 p_OWNER => p_OWNER,
1407 p_CITEM_TRANSLATION_STATUS => p_CITEM_TRANSLATION_STATUS,
1408 p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE );
1409
1410 ELSE
1411 Ibc_Citem_Versions_Pkg.LOAD_ROW (
1412 p_UPLOAD_MODE => p_UPLOAD_MODE,
1413 p_CITEM_VERSION_ID => p_CITEM_VERSION_ID,
1414 p_CONTENT_ITEM_ID => p_CONTENT_ITEM_ID,
1415 p_VERSION_NUMBER => p_VERSION_NUMBER,
1416 p_CITEM_VERSION_STATUS => p_CITEM_VERSION_STATUS,
1417 p_START_DATE => p_START_DATE,
1418 p_END_DATE => p_END_DATE,
1419 p_ATTACHMENT_ATTRIBUTE_CODE => p_ATTACHMENT_ATTRIBUTE_CODE,
1420 p_ATTRIBUTE_FILE_ID => p_ATTRIBUTE_FILE_ID,
1421 p_ATTACHMENT_FILE_ID => p_ATTACHMENT_FILE_ID,
1422 p_CONTENT_ITEM_NAME => p_CONTENT_ITEM_NAME ,
1423 p_ATTACHMENT_FILE_NAME => p_ATTACHMENT_FILE_NAME,
1424 p_DESCRIPTION => p_DESCRIPTION,
1425 p_DEFAULT_RENDITION_MIME_TYPE => p_DEFAULT_RENDITION_MIME_TYPE,
1426 p_OWNER => p_OWNER,
1427 p_CITEM_TRANSLATION_STATUS => p_CITEM_TRANSLATION_STATUS,
1428 p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE );
1429 END IF;
1430 END;
1431
1432 END Ibc_Citem_Versions_Pkg;