[Home] [Help]
PACKAGE BODY: APPS.IBE_M_MIGRATION_PVT
Source
1 PACKAGE BODY IBE_M_MIGRATION_PVT AS
2 /* $Header: IBEVMMGB.pls 120.0 2005/05/30 02:37:07 appldev noship $ */
3 g_image_type CONSTANT VARCHAR2(30) := 'IBC_IMAGE';
4 g_html_type CONSTANT VARCHAR2(30) := 'IBE_HTML';
5 g_media_type CONSTANT VARCHAR2(30) := 'IBE_MEDIA';
6
7 g_label_code CONSTANT VARCHAR2(100) := 'IBE';
8 g_association_type CONSTANT VARCHAR2(100) := 'IBE_MEDIA_OBJECT';
9
10 g_mode VARCHAR2(30);
11 g_language VARCHAR2(255);
12 g_idx_conflict NUMBER := 0;
13 g_idx_created NUMBER := 0;
14 g_item_conflict NUMBER := 0;
15 g_item_created NUMBER := 0;
16 g_html_items NUMBER := 0;
17 g_image_items NUMBER := 0;
18 g_media_items NUMBER := 0;
19 g_start_time DATE;
20 g_end_time DATE;
21
22 unreg_attachments STRING_240_TBL_TYPE;
23 conflict_content CONTENT_TBL_TYPE;
24 migrated_content CONTENT_TBL_TYPE;
25
26 installed_languages STRING_TBL_TYPE;
27 installed_langdesc STRING_255_TBL_TYPE;
28
29 l_attachment_tbl ATTACHMENT_TBL_TYPE;
30 l_trans_attachment_tbl TRANS_ATTACHMENT_TBL_TYPE;
31
32 FUNCTION check_log(p_code IN VARCHAR2,
33 p_status IN VARCHAR2) RETURN NUMBER
34 IS
35 CURSOR c_check_log_csr(c_code VARCHAR2,
36 c_status VARCHAR2) IS
37 SELECT 1
38 FROM IBE_MIGRATION_HISTORY
39 WHERE migration_code = c_code
40 AND status = c_status;
41
42 CURSOR c_check_item_migrated IS
43 SELECT lgl_phys_map_id
44 FROM ibe_dsp_lgl_phys_map
45 WHERE content_item_key IS NOT NULL;
46
47 l_temp NUMBER;
48 BEGIN
49 OPEN c_check_log_csr(p_code, p_status);
50 FETCH c_check_log_csr INTO l_temp;
51 IF (c_check_log_csr%NOTFOUND) THEN
52 l_temp := 0;
53 ELSE
54 OPEN c_check_item_migrated;
55 FETCH c_check_item_migrated INTO l_temp;
56 IF (c_check_item_migrated%NOTFOUND) THEN
57 l_temp := 0;
58 ELSE
59 l_temp := 1;
60 END IF;
61 CLOSE c_check_item_migrated;
62 END IF;
63 CLOSE c_check_log_csr;
64 RETURN l_temp;
65 END check_log;
66
67 PROCEDURE create_log(p_code IN VARCHAR2,
68 p_status IN VARCHAR2,
69 x_status OUT NOCOPY VARCHAR2)
70 IS
71 BEGIN
72 DELETE FROM IBE_MIGRATION_HISTORY
73 WHERE MIGRATION_CODE = p_code;
74 INSERT INTO IBE_MIGRATION_HISTORY(MIGRATION_CODE,
75 OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE,
76 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,STATUS)
77 VALUES(p_code,0, FND_GLOBAL.user_id, SYSDATE,
78 FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.user_id, p_status);
79 x_status := FND_API.G_RET_STS_SUCCESS;
80 EXCEPTION
81 WHEN OTHERS THEN
82 x_status := FND_API.G_RET_STS_ERROR;
83 END create_log;
84
85 PROCEDURE update_log(p_code IN VARCHAR2,
86 p_old_status IN VARCHAR2,
87 p_new_status IN VARCHAR2,
88 x_status OUT NOCOPY VARCHAR2)
89 IS
90 BEGIN
91 UPDATE IBE_MIGRATION_HISTORY
92 SET STATUS = p_new_status,
93 LAST_UPDATE_DATE = SYSDATE
94 WHERE MIGRATION_CODE = p_code
95 AND STATUS = p_old_status;
96 x_status := FND_API.G_RET_STS_SUCCESS;
97 EXCEPTION
98 WHEN OTHERS THEN
99 x_status := FND_API.G_RET_STS_ERROR;
100 END update_log;
101
102 -- Migration for context
103 -- p_mode: 'EVALUATION', 'EXECUTION', 'ROLLBACK'
104 -- update ibe_migration_history
105 PROCEDURE context_mig(p_mode IN VARCHAR2,
106 x_status OUT NOCOPY VARCHAR2,
107 x_content_component_tbl OUT NOCOPY CONTENT_COMPONENT_TBL_TYPE)
108 IS
109 -- Migrate the seed data too
110 CURSOR c_get_context_csr IS
111 SELECT CONTEXT_ID, ACCESS_NAME, COMPONENT_TYPE_CODE
112 FROM IBE_DSP_CONTEXT_B
113 WHERE CONTEXT_TYPE_CODE = 'MEDIA'
114 AND (COMPONENT_TYPE_CODE IS NULL
115 OR CONTEXT_ID < 10000);
116 -- AND CONTEXT_ID >= 10000
117
118 CURSOR c_get_component_type_csr(c_context_id NUMBER) IS
119 SELECT DISTINCT OBJECT_TYPE
120 FROM IBE_DSP_OBJ_LGL_CTNT
121 WHERE CONTEXT_ID = c_context_id;
122
123 CURSOR c_get_rollback_csr IS
124 SELECT creation_date, last_update_date
125 FROM IBE_MIGRATION_HISTORY
126 WHERE MIGRATION_CODE = 'IBE_CONTENT_COMPONENT'
127 AND STATUS = 'SUCCESS';
128
129 l_content_component_tbl CONTENT_COMPONENT_TBL_TYPE;
130
131 l_context_id NUMBER;
132 l_access_name VARCHAR2(40);
133 l_object_temp VARCHAR2(2);
134 l_component_type VARCHAR2(30) := NULL;
135 l_i NUMBER;
136 l_j NUMBER;
137
138 l_start_date DATE;
139 l_end_date DATE;
140 l_migcode VARCHAR2(30);
141 l_module VARCHAR2(30);
142
143 CURSOR c_get_code(c_migcode VARCHAR2) IS
144 SELECT 1
145 FROM IBE_MIGRATION_HISTORY
146 WHERE MIGRATION_CODE = c_migcode;
147 BEGIN
148 SAVEPOINT context_mig;
149 l_migcode := 'IBE_CONTENT_COMPONENT';
150 l_module := 'ibe.plsql.migration.context';
151
152 IF p_mode = 'EXECUTION' THEN
153 l_migcode := l_migcode || to_char(SYSDATE,'DDMMRRRR');
154 OPEN c_get_code(l_migcode);
155 FETCH c_get_code INTO l_i;
156 IF (c_get_code%NOTFOUND) THEN
157 CLOSE c_get_code;
158 INSERT INTO IBE_MIGRATION_HISTORY(MIGRATION_CODE,
159 OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE,
160 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,STATUS)
161 VALUES(l_migcode, 0, FND_GLOBAL.user_id, SYSDATE,
162 FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.user_id, 'START');
163 ELSE
164 CLOSE c_get_code;
165 END IF;
166 END IF;
167 l_i := 0;
168 OPEN c_get_context_csr;
169 LOOP
170 FETCH c_get_context_csr INTO l_context_id, l_access_name,
171 l_component_type;
172 EXIT WHEN c_get_context_csr%NOTFOUND;
173 l_i := l_i + 1;
174 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
175 FND_LOG.string(FND_LOG.LEVEL_EVENT, l_module,
176 'Key:'||to_char(l_context_id)||
177 ' Old component_type_code:'||l_component_type);
178 END IF;
179 OPEN c_get_component_type_csr(l_context_id);
180 -- possible value from this cursor is 'C':category, 'I':item
181 -- 'S':section
182 -- l_component_type := NULL;
183 LOOP
184 FETCH c_get_component_type_csr INTO l_object_temp;
185 EXIT WHEN c_get_component_type_csr%NOTFOUND;
186 IF l_object_temp = 'S' THEN
187 IF l_component_type IS NULL THEN
188 l_component_type := 'SECTION';
189 ELSIF l_component_type = 'PRODUCT' THEN
190 l_component_type := 'GENERIC';
191 END IF;
192 ELSIF l_object_temp = 'C' OR l_object_temp = 'I' THEN
193 IF l_component_type IS NULL THEN
194 l_component_type := 'PRODUCT';
195 ELSIF l_component_type = 'SECTION' THEN
196 l_component_type := 'GENERIC';
197 END IF;
198 END IF;
199 END LOOP;
200 CLOSE c_get_component_type_csr;
201 l_content_component_tbl(l_i).context_id := l_context_id;
202 l_content_component_tbl(l_i).access_name := l_access_name;
203 IF (l_context_id < 10000) THEN
204 l_content_component_tbl(l_i).component_type_code := l_component_type;
205 ELSE
206 IF (l_component_type = NULL) THEN
207 l_component_type := 'GENERIC';
208 END IF;
209 l_content_component_tbl(l_i).component_type_code := l_component_type;
210 END IF;
211 END LOOP;
212 CLOSE c_get_context_csr;
213 IF p_mode = 'EXECUTION' THEN
214 IF (l_i > 0) THEN
215 FOR l_j IN 1..l_i LOOP
216 -- No update on last_updated_by based on the review feedback
217 UPDATE IBE_DSP_CONTEXT_B
218 SET COMPONENT_TYPE_CODE
219 = NVL(l_content_component_tbl(l_j).component_type_code,
220 COMPONENT_TYPE_CODE),
221 LAST_UPDATE_DATE = SYSDATE
222 WHERE context_id = l_content_component_tbl(l_j).context_id;
223 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
224 FND_LOG.string(FND_LOG.LEVEL_EVENT, l_module,
225 'Key:'||to_char(l_content_component_tbl(l_j).context_id)
226 ||' New component_type_code:'
227 ||l_content_component_tbl(l_j).component_type_code);
228 END IF;
229 END LOOP;
230 END IF;
231 UPDATE IBE_MIGRATION_HISTORY
232 SET STATUS = 'SUCCESS',
233 LAST_UPDATE_DATE = SYSDATE
234 WHERE MIGRATION_CODE = l_migcode
235 AND STATUS = 'START';
236 END IF;
237 IF p_mode = 'EXECUTION' OR p_mode = 'EVALUATION' THEN
238 x_content_component_tbl := l_content_component_tbl;
239 ELSE
240 x_content_component_tbl := NULL_CONTENT_COMPONENT_TBL;
241 END IF;
242 x_status := FND_API.g_ret_sts_success;
243 COMMIT;
244 EXCEPTION
245 WHEN OTHERS THEN
246 ROLLBACK TO context_mig;
247 x_status := FND_API.g_ret_sts_error;
248 END context_mig;
249
250 PROCEDURE media_template_mig(p_mode IN VARCHAR2,
251 x_status OUT NOCOPY VARCHAR2,
252 x_media_tbl OUT NOCOPY MEDIA_TEMPLATE_TBL_TYPE,
253 x_template_tbl OUT NOCOPY MEDIA_TEMPLATE_TBL_TYPE)
254 IS
255 BEGIN
256 x_status := FND_API.g_ret_sts_success;
257 END media_template_mig;
258
259 -------------------------------------------------
260 -- Debug Information Pring Procedure
261 -- Y : Display Debug in the Conc. Program Log.
262 -- N: No Debug Statement Printout
263 PROCEDURE printDebuglog(p_debug_str IN VARCHAR2)
264 IS
265 BEGIN
266 -- dbms_output.put_line(p_debug_str);
267 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
268 FND_FILE.PUT_LINE(FND_FILE.LOG,p_debug_str);
269 END IF;
270 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
271 IBE_UTIL.debug(p_debug_str);
272 END IF;
273 END printDebugLog;
274
275 PROCEDURE printOutput(p_message IN VARCHAR2)
276 IS
277 BEGIN
278 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
279 -- dbms_output.put_line(p_message);
280 END printOutput;
281
282 PROCEDURE printReport
283 IS
284 l_i NUMBER;
285 l_access_name VARCHAR2(40) := NULL;
286 l_store VARCHAR2(40) := NULL;
287 l_item_ref_code VARCHAR2(40) := NULL;
288 l_seed_flag VARCHAR2(6) := NULL;
289 l_all_store VARCHAR2(40) := NULL;
290 l_mode VARCHAR2(80);
291 l_def_lang VARCHAR2(255);
292
293 l_temp_msg VARCHAR2(2000);
294 l_title1 VARCHAR2(2000);
295
296 CURSOR c_get_content_type(c_type_code VARCHAR2) IS
297 SELECT content_type_name
298 FROM ibc_content_types_vl
299 WHERE content_type_code = c_type_code;
300
301 CURSOR c_get_mode(c_mode VARCHAR2) IS
302 SELECT meaning
303 FROM fnd_lookups
304 WHERE lookup_type='IBE_M_AUTOPLACEMENT_MODE'
305 and Lookup_code=c_mode;
306
307 CURSOR c_get_lang(c_lang_code VARCHAR2) IS
308 SELECT description
309 FROM fnd_languages_vl
310 WHERE language_code=c_lang_code;
311
312 BEGIN
313 -- For all store
314 fnd_message.set_name('IBE','IBE_PRMT_ALL_G');
315 l_temp_msg := fnd_message.get;
316 l_all_store := substr(l_temp_msg,1,40);
317
318 OPEN c_get_mode(g_mode);
319 FETCH c_get_mode INTO l_mode;
320 IF c_get_mode%FOUND THEN
321 g_mode := l_mode;
322 END IF;
323 CLOSE c_get_mode;
324 OPEN c_get_lang(g_language);
325 FETCH c_get_lang INTO l_def_lang;
326 IF c_get_lang%FOUND THEN
327 g_language := l_def_lang;
328 END IF;
329 CLOSE c_get_lang;
330 fnd_message.set_name('IBE','IBE_M_ATTACHMENT_MIG_DESC');
331 l_temp_msg := fnd_message.get;
332 printOutput(RPAD('=',LENGTH(l_temp_msg)+4,'='));
333 printOutput('| '||l_temp_msg||' |');
334 printOutput(RPAD('=',LENGTH(l_temp_msg)+4,'='));
335 printOutput('');
336 -- Concurrent program parameters
337 fnd_message.set_name('IBE','IBE_M_CONCURRENT_PARAM_DESC');
338 l_temp_msg := fnd_message.get;
339 printOutput(l_temp_msg);
340 printOutput(RPAD('',LENGTHB(l_temp_msg),'='));
341 -- Running Mode
342 fnd_message.set_name('IBE', 'IBE_M_RUNNING_MODE_DESC');
343 l_temp_msg := fnd_message.get;
344 printOutput(l_temp_msg||': '||g_mode);
345 -- Default Language
346 fnd_message.set_name('IBE', 'IBE_MSITE_PRMT_SP_ST_DEF_LANG');
347 l_temp_msg := fnd_message.get;
348 printOutput(l_temp_msg||': '||g_language);
349 -- printOutput('Running Time:'||to_char(g_start_time,'MM/DD/RRRR HH24:MI:SS')
350 -- ||'-'||to_char(g_end_time,'MM/DD/RRRR HH24:MI:SS'));
351 printOutput('');
352 printOutput('=================================');
353 -- Migration summary
354 fnd_message.set_name('IBE', 'IBE_MSG_MGRT_SUMMRY');
355 l_temp_msg := fnd_message.get;
356 printOutput('1. '||l_temp_msg);
357 printOutput('=================================');
358 -- Number of content items created
359 fnd_message.set_name('IBE', 'IBE_M_NUM_CONTENT_ITEM_DESC');
360 l_temp_msg := fnd_message.get;
361 printOutput(l_temp_msg||': '||to_char(g_item_created));
362 printOutput('');
363 -- Content Items created by content type
364 fnd_message.set_name('IBE', 'IBE_M_ITEM_BY_TYPE_DESC');
365 l_temp_msg := fnd_message.get;
366 printOutput(l_temp_msg||':');
367 OPEN c_get_content_type('IBE_HTML');
368 FETCH c_get_content_type INTO l_temp_msg;
369 IF c_get_content_type%NOTFOUND THEN
370 l_temp_msg := 'HTML';
371 END IF;
372 CLOSE c_get_content_type;
373 printOutput(l_temp_msg||' :'||to_char(g_html_items));
374 OPEN c_get_content_type('IBC_IMAGE');
375 FETCH c_get_content_type INTO l_temp_msg;
376 IF c_get_content_type%NOTFOUND THEN
377 l_temp_msg := 'Image';
378 END IF;
379 CLOSE c_get_content_type;
380 printOutput(l_temp_msg||' :'||to_char(g_image_items));
381 OPEN c_get_content_type('IBE_MEDIA');
382 FETCH c_get_content_type INTO l_temp_msg;
383 IF c_get_content_type%NOTFOUND THEN
384 l_temp_msg := 'Media';
385 END IF;
386 CLOSE c_get_content_type;
387 printOutput(l_temp_msg||' :'||to_char(g_media_items));
388 printOutput('');
389 printOutput('===============================');
390 fnd_message.set_name('IBE', 'IBE_M_EXCEPTION_REPORT_LBL');
391 l_temp_msg := fnd_message.get;
392 printOutput('2. '||l_temp_msg);
393 printOutput('===============================');
394 fnd_message.set_name('IBE', 'IBE_M_EXCEPTION_REPORT_DESC');
395 l_temp_msg := fnd_message.get;
396 printOutput(l_temp_msg);
397 printOutput('');
398 fnd_message.set_name('IBE', 'IBE_M_UNIDENTIFIED_ATTCH_LBL');
399 l_temp_msg := fnd_message.get;
400 printOutput('2.1 '||l_temp_msg);
401 printOutput('===========================================');
402 fnd_message.set_name('IBE', 'IBE_M_UNIDENTIFIED_ATTCH_DESC');
403 l_temp_msg := fnd_message.get;
404 printOutput(l_temp_msg);
405 fnd_message.set_name('IBE', 'IBE_PRMT_SRC_FILE_NAME');
406 l_temp_msg := fnd_message.get;
407 printOutput(l_temp_msg);
408 printOutput('--------------------');
409 IF (unreg_attachments.COUNT > 0) THEN
410 FOR l_i IN 1..unreg_attachments.COUNT LOOP
411 printOutput(unreg_attachments(l_i));
412 END LOOP;
413 END IF;
414 printOutput('');
415 fnd_message.set_name('IBE', 'IBE_M_DIFF_ATTACHMENT_LBL');
416 l_temp_msg := fnd_message.get;
417 printOutput('2.2 '||l_temp_msg);
418 printOutput('=====================================================');
419 fnd_message.set_name('IBE', 'IBE_M_DIFF_ATTACHMENT_DESC');
420 l_temp_msg := fnd_message.get;
421 printOutput(l_temp_msg);
422 printOutput('');
423 fnd_message.set_name('IBE', 'IBE_M_MEDIA_ACCESS_NAME_PRMT');
424 l_temp_msg := fnd_message.get;
425 IF (length(l_temp_msg) >= 40) THEN
426 l_title1 := substr(l_temp_msg,1,40)||' ';
427 ELSE
428 l_title1 := RPAD(l_temp_msg,40,' ')||' ';
429 END IF;
430 fnd_message.set_name('IBE', 'IBE_M_SEEDED_PRMT');
431 l_temp_msg := fnd_message.get;
432 IF (length(l_temp_msg) >= 6) THEN
433 l_title1 := l_title1 || substr(l_temp_msg,1,6) || ' ';
434 ELSE
435 l_title1 := l_title1 || RPAD(l_temp_msg,6,' ')||' ';
436 END IF;
437 fnd_message.set_name('IBE', 'IBE_M_STORE_NAME_PRMT');
438 l_temp_msg := fnd_message.get;
439 IF (length(l_temp_msg) >= 40) THEN
440 l_title1 := l_title1 || substr(l_temp_msg,1,40);
441 ELSE
442 l_title1 := l_title1 || RPAD(l_temp_msg,40,' ')||' ';
443 END IF;
444 fnd_message.set_name('IBE', 'IBE_PRMT_LANG_G');
445 l_temp_msg := fnd_message.get;
446 IF (length(l_temp_msg) >= 30) THEN
447 l_title1 := l_title1 || substr(l_temp_msg,1,30);
448 ELSE
449 l_title1 := l_title1 || RPAD(l_temp_msg,30,' ')||' ';
450 END IF;
451 fnd_message.set_name('IBE', 'IBE_PRMT_SRC_FILE_NAME');
452 l_temp_msg := fnd_message.get;
453 l_title1 := l_title1 || l_temp_msg;
454 FOR l_i IN 1..CONFLICT_CONTENT.count LOOP
455 IF MOD(l_i-1,25) = 0 THEN
456 printOutput('');
457 printOutput(l_title1);
458 printOutput('----------------- ------ ----------' ||
459 ' -------- ----------------');
460 END IF;
461 IF (CONFLICT_CONTENT(l_i).access_name IS NULL) THEN
462 l_access_name := RPAD(' ',40,' ');
463 ELSE
464 l_access_name := RPAD(CONFLICT_CONTENT(l_i).access_name,40,' ');
465 END IF;
466 IF (CONFLICT_CONTENT(l_i).store_code IS NULL) THEN
467 l_store := RPAD(' ',40,' ');
468 ELSE
469 IF (CONFLICT_CONTENT(l_i).store_code = 'ALL') THEN
470 l_store := RPAD(SUBSTR(l_all_store,1,40),40,' ');
471 ELSE
472 l_store := RPAD(SUBSTR(CONFLICT_CONTENT(l_i).store_code,1,40),40,' ');
473 END IF;
474 END IF;
475 IF (CONFLICT_CONTENT(l_i).seed_flag IS NULL) THEN
476 l_seed_flag := RPAD(' ',6,' ');
477 ELSE
478 l_seed_flag := RPAD(CONFLICT_CONTENT(l_i).seed_flag,6,' ');
479 END IF;
480 printOutput(l_access_name||' '
481 ||l_seed_flag||' '
482 ||l_store||' '
483 ||RPAD(CONFLICT_CONTENT(l_i).language,30,' ')||' '
484 ||CONFLICT_CONTENT(l_i).file_name);
485 END LOOP;
486 printOutput('');
487 printOutput('===============================');
488 fnd_message.set_name('IBE', 'IBE_M_MIG_DETAIL_REPORT_PRMT');
489 l_temp_msg := fnd_message.get;
490 printOutput('3. '||l_temp_msg);
491 printOutput('===============================');
492 -- Fix bug 2710858
493 fnd_message.set_name('IBE', 'IBE_M_MIG_DETAIL_REPORT_DESC');
494 l_temp_msg := fnd_message.get;
495 printOutput(l_temp_msg);
496 printOutput('');
497 fnd_message.set_name('IBE', 'IBE_M_MEDIA_ACCESS_NAME_PRMT');
498 l_temp_msg := fnd_message.get;
499 IF (length(l_temp_msg) >= 40) THEN
500 l_title1 := substr(l_temp_msg,1,40)||' ';
501 ELSE
502 l_title1 := RPAD(l_temp_msg,40,' ')||' ';
503 END IF;
504 fnd_message.set_name('IBE', 'IBE_M_CONTENT_ITEM_CODE_PRMT');
505 l_temp_msg := fnd_message.get;
506 IF (length(l_temp_msg) >= 40) THEN
507 l_title1 := l_title1 || substr(l_temp_msg,1,40);
508 ELSE
509 l_title1 := l_title1 || RPAD(l_temp_msg,40,' ')||' ';
510 END IF;
511 fnd_message.set_name('IBE', 'IBE_M_STORE_NAME_PRMT');
512 l_temp_msg := fnd_message.get;
513 IF (length(l_temp_msg) >= 40) THEN
514 l_title1 := l_title1 || substr(l_temp_msg,1,40);
515 ELSE
516 l_title1 := l_title1 || RPAD(l_temp_msg,40,' ')||' ';
517 END IF;
518 fnd_message.set_name('IBE', 'IBE_PRMT_LANG_G');
519 l_temp_msg := fnd_message.get;
520 IF (length(l_temp_msg) >= 30) THEN
521 l_title1 := l_title1 || substr(l_temp_msg,1,30);
522 ELSE
523 l_title1 := l_title1 || RPAD(l_temp_msg,30,' ')||' ';
524 END IF;
525 fnd_message.set_name('IBE', 'IBE_PRMT_SRC_FILE_NAME');
526 l_temp_msg := fnd_message.get;
527 l_title1 := l_title1 || l_temp_msg;
528 FOR l_i IN 1..MIGRATED_CONTENT.count LOOP
529 IF MOD(l_i-1,25) = 0 THEN
530 printOutput('');
531 printOutput(l_title1);
532 printOutput('----------------- ----------------- '||
533 ' ---------- -------- ' ||
534 ' ----------------');
535 END IF;
536 IF (MIGRATED_CONTENT(l_i).access_name IS NULL) THEN
537 l_access_name := RPAD(' ',40,' ');
538 ELSE
539 l_access_name := RPAD(MIGRATED_CONTENT(l_i).access_name,40,' ');
540 END IF;
541 IF (MIGRATED_CONTENT(l_i).store_code IS NULL) THEN
542 l_store := RPAD(' ',40,' ');
543 ELSE
544 IF l_store = 'ALL' THEN
545 l_store := RPAD(SUBSTR(l_all_store,1,40),40,' ');
546 ELSE
547 l_store := RPAD(SUBSTR(MIGRATED_CONTENT(l_i).store_code,1,40),40,' ');
548 END IF;
549 END IF;
550 IF (MIGRATED_CONTENT(l_i).content_item_code IS NULL) THEN
551 l_item_ref_code := RPAD(' ',40,' ');
552 ELSE
553 l_item_ref_code
554 := RPAD(SUBSTR(MIGRATED_CONTENT(l_i).content_item_code,1,40),40,' ');
555 END IF;
556 printOutput(l_access_name||' '||l_item_ref_code||' '||l_store||' '
557 ||RPAD(MIGRATED_CONTENT(l_i).language,30,' ')||' '
558 ||MIGRATED_CONTENT(l_i).file_name);
559 END LOOP;
560 END printReport;
561
562 -- This procedure checks the migrated attachment
563 -- can be recognized by the program
564 -- Fix for perf bug 2854588, sql id 5044302
565 PROCEDURE attachType IS
566 CURSOR c_get_attachment_csr IS
567 SELECT DISTINCT file_name
568 FROM jtf_amv_attachments a
569 WHERE application_id = 671
570 AND file_id IS NOT NULL
571 AND attachment_used_by = 'ITEM'
572 AND NOT EXISTS (
573 SELECT NULL
574 FROM ibe_dsp_lgl_phys_map b, jtf_amv_items_b c
575 WHERE a.attachment_id = b.attachment_id
576 AND c.item_id = b.item_id
577 AND c.deliverable_type_code = 'TEMPLATE');
578
579 l_file_name VARCHAR2(240);
580 l_i NUMBER;
581 l_seperator NUMBER;
582 l_ext VARCHAR2(240);
583 l_unreg_flag VARCHAR2(1);
584 BEGIN
585 l_i := 0;
586 OPEN c_get_attachment_csr;
587 LOOP
588 FETCH c_get_attachment_csr INTO l_file_name;
589 EXIT WHEN c_get_attachment_csr%NOTFOUND;
590 l_seperator := INSTR(l_file_name, '.', -1);
591 l_unreg_flag := 'N';
592 IF (l_seperator <> 0) THEN
593 l_ext := UPPER(substr(l_file_name,l_seperator+1));
594 IF (l_ext IN ('JPG', 'JPEG', 'JFIF', 'JPE', 'PNG', 'GIF', 'GFA',
595 'HTML', 'HTM')) THEN
596 l_unreg_flag := 'Y';
597 END IF;
598 END IF;
599 IF (l_unreg_flag = 'N') THEN
600 l_i := l_i + 1;
601 unreg_attachments(l_i) := l_file_name;
602 END IF;
603 END LOOP;
604 CLOSE c_get_attachment_csr;
605 END attachType;
606
607 -- This procedure is to get the attachment for content item
608 -- base language
609 PROCEDURE get_base_content(
610 p_item_id IN NUMBER,
611 p_msite_id IN NUMBER,
612 p_default_msite IN VARCHAR2,
613 p_language_code IN VARCHAR2,
614 x_file_id OUT NOCOPY NUMBER,
615 x_file_name OUT NOCOPY VARCHAR2,
616 x_height OUT NOCOPY NUMBER,
617 x_width OUT NOCOPY NUMBER,
618 x_translate_flag OUT NOCOPY VARCHAR2)
619 IS
620 -- Fix the height and width issue
621 CURSOR c_get_mapping_csr(c_item_id NUMBER,
622 c_msite_id NUMBER, c_default_msite VARCHAR2,
623 c_language_code VARCHAR2, c_default_lang VARCHAR2) IS
624 -- SELECT b.file_id, b.display_width, b.display_height, b.file_name
625 SELECT b.file_id, b.display_height, b.display_width, b.file_name
626 FROM jtf_amv_attachments b, ibe_dsp_lgl_phys_map a
627 WHERE a.attachment_id = b.attachment_id
628 AND a.default_language = c_default_lang
629 AND a.language_code = c_language_code
630 AND a.default_site = c_default_msite
631 AND a.msite_id = c_msite_id
632 AND a.item_id = c_item_id;
633
634 CURSOR c_get_any_mapping(c_item_id NUMBER,
635 c_msite_id NUMBER, c_default_msite VARCHAR2) IS
636 -- SELECT b.file_id, b.display_width, b.display_height, b.file_name
637 SELECT b.file_id, b.display_height, b.display_width, b.file_name
638 FROM jtf_amv_attachments b, ibe_dsp_lgl_phys_map a
639 WHERE a.attachment_id = b.attachment_id
640 AND a.default_site = c_default_msite
641 AND a.msite_id = c_msite_id
642 AND a.item_id = c_item_id;
643
644 l_height NUMBER;
645 l_width NUMBER;
646 l_file_id NUMBER;
647 l_file_name VARCHAR2(240);
648 l_translate_flag VARCHAR2(1);
649 l_continue_flag VARCHAR2(1);
650 BEGIN
651 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
652 printDebuglog(' get_base_content begin');
653 printDebuglog(' p_item_id='||p_item_id||' p_msite_id='||p_msite_id||
654 ' p_default_msite='||p_default_msite||' p_language_code='||
655 p_language_code);
656 END IF;
657 -- Check if the base language has the mapping
658 l_continue_flag := 'Y';
659 OPEN c_get_mapping_csr(p_item_id, p_msite_id,
660 p_default_msite, p_language_code, 'N');
661 FETCH c_get_mapping_csr INTO l_file_id, l_height, l_width, l_file_name;
662 IF (c_get_mapping_csr%FOUND) THEN
663 -- If the base language has mapping, then need to
664 -- translate the other languages during merging
665 l_continue_flag := 'N';
666 l_translate_flag := 'Y';
667 END IF;
668 CLOSE c_get_mapping_csr;
669 IF (l_continue_flag = 'Y') THEN
670 -- Check if there is mapping for all language of the minisite
671 OPEN c_get_mapping_csr(p_item_id, p_msite_id,
672 p_default_msite, 'US', 'Y');
673 FETCH c_get_mapping_csr INTO l_file_id, l_height, l_width, l_file_name;
674 IF (c_get_mapping_csr%FOUND) THEN
675 -- If the all language is defined, use all language
676 -- mapping as the base language content, and no need
677 -- to translate the other languages
678 l_continue_flag := 'N';
679 l_translate_flag := 'N';
680 END IF;
681 CLOSE c_get_mapping_csr;
682 END IF;
683 IF (l_continue_flag = 'Y') AND (p_default_msite = 'N') THEN
684 -- If this is for a specific minisite, then check if there
685 -- is all minisite mapping for the base language
686 OPEN c_get_mapping_csr(p_item_id, 1, 'Y', p_language_code, 'N');
687 FETCH c_get_mapping_csr INTO l_file_id, l_height, l_width, l_file_name;
688 IF (c_get_mapping_csr%FOUND) THEN
689 -- use all minisite and specific language mapping as
690 -- base language content, but need translate the other
691 -- languages
692 l_continue_flag := 'N';
693 l_translate_flag := 'Y';
694 END IF;
695 CLOSE c_get_mapping_csr;
696 IF (l_continue_flag = 'Y') THEN
697 OPEN c_get_mapping_csr(p_item_id, 1, 'Y', 'US', 'Y');
698 FETCH c_get_mapping_csr INTO l_file_id, l_height, l_width, l_file_name;
699 IF (c_get_mapping_csr%FOUND) THEN
700 -- use all minisite and all language mapping as the
701 -- base language content, indicate the all language
702 -- mapping is used for base language
703 l_continue_flag := 'N';
704 l_translate_flag := 'A';
705 END IF;
706 END IF;
707 END IF;
708 -- If cannot find mapping for the base language
709 -- pick up first one of the mapping as the base
710 -- language attachment
711 IF (l_continue_flag = 'Y') THEN
712 OPEN c_get_any_mapping(p_item_id, p_msite_id, p_default_msite);
713 FETCH c_get_any_mapping INTO l_file_id, l_height, l_width, l_file_name;
714 IF (c_get_any_mapping%FOUND) THEN
715 l_continue_flag := 'N';
716 l_translate_flag := 'N';
717 END IF;
718 END IF;
719 IF (l_continue_flag = 'Y') THEN
720 x_file_id := -1;
721 x_height := -1;
722 x_width := -1;
723 x_translate_flag := null;
724 x_file_name := null;
725 ELSE
726 x_file_id := l_file_id;
727 x_height := l_height;
728 x_width := l_width;
729 x_translate_flag := l_translate_flag;
730 x_file_name := l_file_name;
731 END IF;
732 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
733 printDebuglog(' x_file_id='||x_file_id||' x_height='||x_height||
734 ' x_width='||x_width||' x_translate_flag='||x_translate_flag||
735 ' x_file_name='||x_file_name);
736 printDebuglog(' get_base_content end');
737 END IF;
738 EXCEPTION
739 WHEN OTHERS THEN
740 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
741 printDebuglog('get exception in get_base_content');
742 printDebuglog('sqlcode:'||SQLCODE||' sqlerr;'||SQLERRM);
743 END IF;
744 END get_base_content;
745
746 -- This procedure is to get the attachment for content item
747 -- translated languages
748 PROCEDURE get_trans_content(
749 p_item_id IN NUMBER,
750 p_msite_id IN NUMBER,
751 p_default_msite IN VARCHAR2,
752 p_language_code IN VARCHAR2,
753 p_translate_flag IN VARCHAR2,
754 x_file_id OUT NOCOPY NUMBER,
755 x_file_name OUT NOCOPY VARCHAR2,
756 x_height OUT NOCOPY NUMBER,
757 x_width OUT NOCOPY NUMBER)
758 IS
759 -- Fix the height and width issue
760 CURSOR c_get_mapping_csr(c_item_id NUMBER,
761 c_msite_id NUMBER, c_default_msite VARCHAR2,
762 c_language_code VARCHAR2, c_default_lang VARCHAR2) IS
763 -- SELECT b.file_id, b.display_width, b.display_height, b.file_name
764 SELECT b.file_id, b.display_height, b.display_width, b.file_name
765 FROM jtf_amv_attachments b, ibe_dsp_lgl_phys_map a
766 WHERE a.attachment_id = b.attachment_id
767 AND a.default_language = c_default_lang
768 AND a.language_code = c_language_code
769 AND a.default_site = c_default_msite
770 AND a.msite_id = c_msite_id
771 AND a.item_id = c_item_id;
772
773 l_continue_flag VARCHAR2(1);
774 l_height NUMBER;
775 l_width NUMBER;
776 l_file_id NUMBER;
777 l_file_name VARCHAR2(240);
778 BEGIN
779 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
780 printDebuglog(' get_trans_content begin');
781 printDebuglog(' p_item_id='||p_item_id||' p_msite_id='||p_msite_id||
782 ' p_default_msite='||p_default_msite||' p_language_code='||p_language_code||
783 ' p_translate_flag='||p_translate_flag);
784 END IF;
785 -- Check if the specific language has the mapping
786 l_continue_flag := 'Y';
787 OPEN c_get_mapping_csr(p_item_id, p_msite_id,
788 p_default_msite, p_language_code, 'N');
789 FETCH c_get_mapping_csr INTO l_file_id, l_height, l_width, l_file_name;
790 IF (c_get_mapping_csr%FOUND) THEN
791 l_continue_flag := 'N';
792 END IF;
793 CLOSE c_get_mapping_csr;
794 IF (l_continue_flag = 'Y') AND
795 (p_translate_flag = 'Y' OR p_translate_flag = 'A') THEN
796 OPEN c_get_mapping_csr(p_item_id, p_msite_id,
797 p_default_msite, 'US', 'Y');
798 FETCH c_get_mapping_csr INTO l_file_id, l_height, l_width, l_file_name;
799 IF (c_get_mapping_csr%FOUND) THEN
800 l_continue_flag := 'N';
801 END IF;
802 CLOSE c_get_mapping_csr;
803 IF (l_continue_flag = 'Y') AND (p_translate_flag = 'Y') THEN
804 OPEN c_get_mapping_csr(p_item_id, 1, 'Y', 'US', 'Y');
805 FETCH c_get_mapping_csr INTO l_file_id, l_height, l_width, l_file_name;
806 IF (c_get_mapping_csr%FOUND) THEN
807 l_continue_flag := 'N';
808 END IF;
809 CLOSE c_get_mapping_csr;
810 END IF;
811 END IF;
812 IF (l_continue_flag = 'Y') THEN
813 x_file_id := -1;
814 x_height := -1;
815 x_width := -1;
816 x_file_name := null;
817 ELSE
818 x_file_id := l_file_id;
819 x_height := l_height;
820 x_width := l_width;
821 x_file_name := l_file_name;
822 END IF;
823 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
824 printDebuglog(' x_file_id='||x_file_id||' x_height='||x_height||
825 ' x_width='||x_width||' x_file_name='||x_file_name);
826 printDebuglog(' get_trans_content end');
827 END IF;
828 EXCEPTION
829 WHEN OTHERS THEN
830 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
831 printDebuglog('get exception in get_trans_content');
832 printDebuglog('sqlcode:'||SQLCODE||' sqlerr;'||SQLERRM);
833 END IF;
834 END get_trans_content;
835
836 -- This procedure is used to decide content type of the
837 -- content item
838 PROCEDURE contentItemType(
839 p_attachment_rec IN OUT NOCOPY ATTACHMENT_REC_TYPE)
840 IS
841 l_i NUMBER;
842 l_j NUMBER := 0;
843 l_seperator NUMBER;
844 l_ext VARCHAR2(240);
845 l_unreg_flag VARCHAR2(1);
846 l_file_name VARCHAR2(240);
847 BEGIN
848 p_attachment_rec.conflict_flag := 'N';
849 IF (p_attachment_rec.start_trans <> -1) THEN
850 l_j := p_attachment_rec.end_trans-p_attachment_rec.start_trans+1;
851 END IF;
852 p_attachment_rec.content_type_code := NULL;
853 FOR l_i IN 1..(l_j+1) LOOP
854 IF (l_i=1) THEN
855 l_file_name := p_attachment_rec.file_name;
856 ELSE
857 l_file_name
858 := l_trans_attachment_tbl(l_i-2+p_attachment_rec.start_trans).file_names;
859 END IF;
860 l_seperator := INSTR(l_file_name, '.', -1);
861 IF (l_seperator <> 0) THEN
862 l_ext := UPPER(substr(l_file_name,l_seperator+1));
863 IF (l_ext IN ('JPG','JPEG','JFIF','JPE','PNG','GIF','GFA')) THEN
864 IF p_attachment_rec.content_type_code IS NULL THEN
865 p_attachment_rec.content_type_code := g_image_type;
866 ELSIF p_attachment_rec.content_type_code = g_html_type THEN
867 p_attachment_rec.content_type_code := g_media_type;
868 p_attachment_rec.conflict_flag := 'Y';
869 ELSIF p_attachment_rec.content_type_code = g_media_type THEN
870 p_attachment_rec.conflict_flag := 'Y';
871 END IF;
872 ELSIF (l_ext IN ('HTML', 'HTM')) THEN
873 IF p_attachment_rec.content_type_code IS NULL THEN
874 p_attachment_rec.content_type_code := g_html_type;
875 ELSIF p_attachment_rec.content_type_code = g_image_type THEN
876 p_attachment_rec.content_type_code := g_media_type;
877 ELSIF p_attachment_rec.content_type_code = g_media_type THEN
878 p_attachment_rec.conflict_flag := 'Y';
879 END IF;
880 ELSE
881 p_attachment_rec.content_type_code := g_media_type;
882 END IF;
883 END IF;
884 END LOOP;
885 IF (p_attachment_rec.content_type_code IS NULL) THEN
886 p_attachment_rec.content_type_code := g_media_type;
887 END IF;
888 END contentItemType;
889
890 -- This function is used to compare two content items to
891 -- see if the migration can reuse content item for one
892 -- logical item
893 FUNCTION compareContentItem(
894 l_src_att IN ATTACHMENT_REC_TYPE,
895 l_tar_att IN ATTACHMENT_REC_TYPE) RETURN VARCHAR2
896 IS
897 l_i NUMBER;
898 BEGIN
899 IF (l_src_att.language<>l_tar_att.language) THEN
900 RETURN 'N';
901 END IF;
902 IF (l_src_att.file_id<>l_tar_att.file_id) THEN
903 RETURN 'N';
904 END IF;
905 IF (l_src_att.file_name<>l_tar_att.file_name) THEN
906 RETURN 'N';
907 END IF;
908 IF (l_src_att.END_TRANS-l_src_att.START_TRANS)<>
909 (l_tar_att.END_TRANS-l_tar_att.START_TRANS) THEN
910 RETURN 'N';
911 END IF;
912 IF ((l_src_att.START_TRANS=-1) AND (l_tar_att.START_TRANS<>-1))
913 OR ((l_src_att.START_TRANS<>-1) AND (l_tar_att.START_TRANS=-1)) THEN
914 RETURN 'N';
915 END IF;
916 IF (l_src_att.START_TRANS <> -1) THEN
917 FOR l_i IN 1..(l_tar_att.END_TRANS-l_src_att.START_TRANS+1) LOOP
918 IF l_trans_attachment_tbl(l_src_att.START_TRANS+l_i-1).TRANS_LANGUAGES
919 <> l_trans_attachment_tbl(l_tar_att.START_TRANS+l_i-1).TRANS_LANGUAGES THEN
920 RETURN 'N';
921 END IF;
922 IF l_trans_attachment_tbl(l_src_att.START_TRANS+l_i-1).FILE_IDS
923 <> l_trans_attachment_tbl(l_tar_att.START_TRANS+l_i-1).FILE_IDS THEN
924 RETURN 'N';
925 END IF;
926 END LOOP;
927 END IF;
928 RETURN 'Y';
929 EXCEPTION
930 WHEN OTHERS THEN
931 RETURN 'N';
932 END compareContentItem;
933
934 -- This procedure is to handle new content item
935 -- and put it into IBC tables
936 PROCEDURE process_content_item(
937 p_label_flag IN VARCHAR2,
938 px_attachment_rec IN OUT NOCOPY ATTACHMENT_REC_TYPE,
939 x_return_status OUT NOCOPY VARCHAR2,
940 x_msg_count OUT NOCOPY NUMBER,
941 x_msg_data OUT NOCOPY VARCHAR2)
942 IS
943 l_i NUMBER;
944 l_j NUMBER;
945 l_return_status VARCHAR2(1);
946 l_msg_count NUMBER;
947 l_msg_data VARCHAR2(2000);
948
949 l_type_code VARCHAR2(100) := g_media_type;
950 l_label_code VARCHAR2(100) := g_label_code;
951 l_association_type VARCHAR2(100) := g_association_type;
952 l_status VARCHAR2(30) := IBC_UTILITIES_PUB.G_STV_APPROVED;
953 l_cv_label_rec Ibc_Cv_Label_Grp.CV_Label_Rec_Type;
954 x_cv_label_rec Ibc_Cv_Label_Grp.CV_Label_Rec_Type;
955 l_attribute_type_codes JTF_VARCHAR2_TABLE_100;
956 l_attributes JTF_VARCHAR2_TABLE_4000;
957 l_attidx NUMBER;
958
959 -- l_assoc_type_codes JTF_VARCHAR2_TABLE_100;
960 -- l_assoc_objects JTF_VARCHAR2_TABLE_300;
961
962 l_object_version_number NUMBER := 0;
963
964 l_content_item_id NUMBER;
965 l_citem_version_id NUMBER;
966 l_lgl_phys_map_id NUMBER;
967 l_version_number NUMBER;
968 l_move_label NUMBER;
969 l_old_version NUMBER;
970 l_old_ver_num NUMBER;
971 l_old_item NUMBER;
972
973 CURSOR c_get_content_item_csr(c_item_ref_code VARCHAR2,
974 c_label_code VARCHAR2) IS
975 SELECT a.content_item_id, b.citem_version_id
976 FROM ibc_content_items a, ibc_citem_version_labels b
977 WHERE a.item_reference_code = c_item_ref_code
978 AND a.content_item_id = b.content_item_id
979 AND b.label_code = c_label_code;
980
981 CURSOR c_get_item_version_csr(c_item_ref_code VARCHAR2) IS
982 SELECT a.content_item_id, b.citem_version_id
983 FROM ibc_content_items a, ibc_citem_versions_b b
984 WHERE a.item_reference_code = c_item_ref_code
985 AND a.content_item_id = b.citem_version_id
986 AND b.citem_version_status = IBC_UTILITIES_PUB.G_STV_APPROVED;
987
988 CURSOR c_get_content_item(c_item_ref_code VARCHAR2) IS
989 SELECT content_item_id
990 FROM ibc_content_items
991 WHERE item_reference_code = c_item_ref_code;
992
993 CURSOR c_get_lgl_phys_map_id_csr IS
994 SELECT IBE_DSP_LGL_PHYS_MAP_S1.nextval
995 FROM dual;
996
997 CURSOR c_check_map(c_item_id NUMBER, c_msite_id NUMBER,
998 c_lang_code VARCHAR2, c_def_msite VARCHAR2, c_def_lang VARCHAR2) IS
999 SELECT 1
1000 FROM IBE_DSP_LGL_PHYS_MAP
1001 WHERE item_id = c_item_id
1002 AND msite_id = c_msite_id
1003 AND language_code = c_lang_code
1004 AND default_site = c_def_msite
1005 AND default_language = c_def_lang
1006 AND attachment_id = -1;
1007
1008 CURSOR c_get_version_number(c_citem_version_id NUMBER) IS
1009 SELECT version_number
1010 FROM ibc_citem_versions_b
1011 WHERE citem_version_id = c_citem_version_id;
1012
1013 l_upsert_item VARCHAR2(1);
1014 l_label_associate_item VARCHAR2(1);
1015
1016 BEGIN
1017 SAVEPOINT process_content_item;
1018 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1019 printDebugLog('Start processing item');
1020 END IF;
1021 l_upsert_item := 'Y';
1022 l_label_associate_item := 'Y';
1023
1024 l_move_label := 0;
1025 /*
1026 IF px_attachment_rec.seed_data_flag = 'Y' THEN
1027 -- OPEN c_get_content_item_csr(px_attachment_rec.access_name, l_label_code);
1028 OPEN c_get_content_item_csr(px_attachment_rec.content_item_code,
1029 l_label_code);
1030 FETCH c_get_content_item_csr INTO l_content_item_id, l_citem_version_id;
1031 IF (c_get_content_item_csr%NOTFOUND) THEN
1032 l_citem_version_id := NULL;
1033 -- OPEN c_get_content_item(px_attachment_rec.access_name);
1034 OPEN c_get_content_item(px_attachment_rec.content_item_code);
1035 FETCH c_get_content_item INTO l_content_item_id;
1036 IF (c_get_content_item%NOTFOUND) THEN
1037 l_content_item_id := NULL;
1038 END IF;
1039 CLOSE c_get_content_item;
1040 ELSE
1041 l_old_version := l_citem_version_id;
1042 l_citem_version_id := NULL;
1043 l_move_label := 1;
1044 END IF;
1045 CLOSE c_get_content_item_csr;
1046 ELSIF px_attachment_rec.duplicate_flag = 'Y' THEN
1047 */
1048 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1049 printDebuglog(' duplicate flag='||px_attachment_rec.duplicate_flag);
1050 END IF;
1051 IF px_attachment_rec.duplicate_flag = 'Y' THEN
1052 l_upsert_item := 'N';
1053 l_label_associate_item := 'N';
1054 l_content_item_id := px_attachment_rec.content_item_id;
1055 l_citem_version_id := px_attachment_rec.citem_version_id;
1056 l_move_label := -1;
1057 ELSE
1058 OPEN c_get_content_item_csr(px_attachment_rec.content_item_code,
1059 l_label_code);
1060 FETCH c_get_content_item_csr INTO l_content_item_id, l_citem_version_id;
1061 IF (c_get_content_item_csr%NOTFOUND) THEN
1062 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1063 printDebuglog(' labeled version is not found for '
1064 ||px_attachment_rec.content_item_code||' label '||l_label_code);
1065 END IF;
1066 OPEN c_get_item_version_csr(px_attachment_rec.content_item_code);
1067 FETCH c_get_item_version_csr INTO l_content_item_id, l_citem_version_id;
1068 IF (c_get_item_version_csr%NOTFOUND) THEN
1069 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1070 printDebuglog(' approved version is not found for '
1071 ||px_attachment_rec.content_item_code);
1072 END IF;
1073 l_upsert_item := 'Y';
1074 l_label_associate_item := 'Y';
1075 l_citem_version_id := NULL;
1076 OPEN c_get_content_item(px_attachment_rec.content_item_code);
1077 FETCH c_get_content_item INTO l_content_item_id;
1078 IF (c_get_content_item%NOTFOUND) THEN
1079 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1080 printDebuglog(' content item not found for '
1081 ||px_attachment_rec.content_item_code);
1082 END IF;
1083 l_content_item_id := NULL;
1084 END IF;
1085 CLOSE c_get_content_item;
1086 ELSE
1087 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1088 printDebuglog(' approved version is found for '
1089 ||px_attachment_rec.content_item_code);
1090 END IF;
1091 l_upsert_item := 'N';
1092 l_label_associate_item := 'Y';
1093 END IF;
1094 CLOSE c_get_item_version_csr;
1095 ELSE
1096 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1097 printDebuglog(' labeled version is found for '
1098 ||px_attachment_rec.content_item_code||' label '||l_label_code);
1099 END IF;
1100 l_upsert_item := 'N';
1101 l_label_associate_item := 'N';
1102 END IF;
1103 CLOSE c_get_content_item_csr;
1104 END IF;
1105 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1106 printDebugLog(' l_upsert_item='||l_upsert_item);
1107 printDebugLog(' l_label_associate_item='||l_label_associate_item);
1108 printDebugLog(' After finding content item id and citem version id');
1109 printDebugLog(' content item id:'||l_content_item_id
1110 ||' Citem version id:'||l_citem_version_id);
1111 END IF;
1112
1113 -- IF (l_content_item_id IS NULL) THEN
1114 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1115 printDebugLog(' upsert content item flag:'||l_upsert_item);
1116 END IF;
1117 IF (px_attachment_rec.duplicate_flag <> 'Y')
1118 AND (l_upsert_item = 'Y') THEN
1119 l_attribute_type_codes := JTF_VARCHAR2_TABLE_100();
1120 l_attributes := JTF_VARCHAR2_TABLE_4000();
1121 l_attidx := 0;
1122 IF (px_attachment_rec.width IS NOT NULL) AND (px_attachment_rec.width<>0) THEN
1123 l_attidx := l_attidx + 1;
1124 l_attribute_type_codes.extend(1);
1125 l_attributes.extend(1);
1126 l_attribute_type_codes(l_attidx) := 'WIDTH';
1127 l_attributes(l_attidx) := px_attachment_rec.width;
1128 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1129 printDebugLog(' attribute index:'||l_attidx);
1130 printDebugLog(' attribute_type_code:'||l_attribute_type_codes(l_attidx));
1131 printDebugLog(' attribute_value:'||l_attributes(l_attidx));
1132 END IF;
1133 END IF;
1134 IF (px_attachment_rec.height IS NOT NULL) AND (px_attachment_rec.height<>0) THEN
1135 l_attidx := l_attidx + 1;
1136 l_attribute_type_codes.extend(1);
1137 l_attributes.extend(1);
1138 l_attribute_type_codes(l_attidx) := 'HEIGHT';
1139 l_attributes(l_attidx) := px_attachment_rec.height;
1140 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1141 printDebugLog(' attribute index:'||l_attidx);
1142 printDebugLog(' attribute_type_code:'||l_attribute_type_codes(l_attidx));
1143 printDebugLog(' attribute_value:'||l_attributes(l_attidx));
1144 END IF;
1145 END IF;
1146 IF l_attidx = 0 THEN
1147 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1148 printDebugLog(' attribute type is null');
1149 printDebugLog(' attribute value is null');
1150 END IF;
1151 l_attribute_type_codes := NULL;
1152 l_attributes := NULL;
1153 END IF;
1154
1155 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1156 printDebugLog(' Start upserting content item');
1157 printDebugLog(' type code:'||px_attachment_rec.content_type_code);
1158 printDebugLog(' name:'||px_attachment_rec.FILE_NAME);
1159 printDebugLog(' description:'||px_attachment_rec.FILE_NAME);
1160 printDebugLog(' file id:'||px_attachment_rec.file_id);
1161 printDebugLog(' status:'||l_status);
1162 printDebugLog(' language:'||px_attachment_rec.language);
1163 printDebugLog(' content_item_id:'||l_content_item_id);
1164 printDebugLog(' citem_version_id:'||l_citem_version_id);
1165 printDebugLog(' object_version_num:'||l_object_version_number);
1166 END IF;
1167 IBC_CITEM_ADMIN_GRP.upsert_item(
1168 p_ctype_code => px_attachment_rec.content_type_code,
1169 p_citem_name => px_attachment_rec.FILE_NAME,
1170 p_citem_description => px_attachment_rec.FILE_NAME,
1171 p_dir_node_id => 9, -- IBE directory node
1172 p_reference_code => px_attachment_rec.content_item_code,
1173 p_trans_required => FND_API.G_FALSE,
1174 p_wd_restricted => FND_API.G_FALSE,
1175 p_start_date => NULL,
1176 p_end_date => NULL,
1177 p_attribute_type_codes => l_attribute_type_codes,
1178 p_attributes => l_attributes,
1179 p_attach_file_id => px_attachment_rec.file_id,
1180 p_status => l_status,
1181 p_language => px_attachment_rec.language,
1182 p_commit => FND_API.G_FALSE,
1183 px_content_item_id => l_content_item_id,
1184 px_citem_ver_id => l_citem_version_id,
1185 px_object_version_number => l_object_version_number,
1186 x_return_status => l_return_status,
1187 x_msg_count => l_msg_count,
1188 x_msg_data => l_msg_data);
1189 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1190 printDebugLog(' OCM base content item creation status:'||l_return_status);
1191 END IF;
1192 IF l_return_status <> FND_API.g_ret_sts_success THEN
1193 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1194 printDebugLog(' Error in base content item creation:');
1195 END IF;
1196 for i in 1..l_msg_count loop
1197 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1198 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1199 printDebugLog(' '||l_msg_data);
1200 END IF;
1201 end loop;
1202 x_msg_data := l_msg_data;
1203 RAISE FND_API.g_exc_error;
1204 END IF;
1205 px_attachment_rec.content_item_id := l_content_item_id;
1206 px_attachment_rec.citem_version_id := l_citem_version_id;
1207 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1208 printDebugLog(' End upserting content item');
1209 printDebugLog(' content_item_id:'||l_content_item_id);
1210 printDebugLog(' citem_version_id:'||l_citem_version_id);
1211 printDebugLog(' object_version_num:'||l_object_version_number);
1212 END IF;
1213 IF (px_attachment_rec.start_trans <> -1) THEN
1214 FOR l_j IN
1215 px_attachment_rec.start_trans..px_attachment_rec.end_trans LOOP
1216 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1217 printDebugLog(' Start upserting translation of content item');
1218 printDebugLog(' language:'||px_attachment_rec.language);
1219 END IF;
1220 IBC_CITEM_ADMIN_GRP.upsert_item(
1221 p_ctype_code => px_attachment_rec.content_type_code,
1222 p_citem_name => l_trans_attachment_tbl(l_j).file_names,
1223 p_citem_description => l_trans_attachment_tbl(l_j).FILE_NAMES,
1224 p_dir_node_id => 9, -- IBE directory node
1225 p_reference_code => px_attachment_rec.content_item_code,
1226 p_trans_required => FND_API.G_FALSE,
1227 p_wd_restricted => FND_API.G_FALSE,
1228 p_start_date => NULL,
1229 p_end_date => NULL,
1230 p_attribute_type_codes => l_attribute_type_codes,
1231 p_attributes => l_attributes,
1232 p_attach_file_id => l_trans_attachment_tbl(l_j).file_ids,
1233 p_status => l_status,
1234 p_language => l_trans_attachment_tbl(l_j).trans_languages,
1235 p_commit => FND_API.G_FALSE,
1236 px_content_item_id => l_content_item_id,
1237 px_citem_ver_id => l_citem_version_id,
1238 px_object_version_number => l_object_version_number,
1239 x_return_status => l_return_status,
1240 x_msg_count => l_msg_count,
1241 x_msg_data => l_msg_data);
1242 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1243 printDebugLog(' OCM translating content item status:'||l_return_status);
1244 END IF;
1245 IF l_return_status <> FND_API.g_ret_sts_success THEN
1246 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1247 printDebugLog(' Error in translating content item!');
1248 END IF;
1249 for i in 1..l_msg_count loop
1250 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1251 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1252 printDebugLog(' '||l_msg_data);
1253 END IF;
1254 end loop;
1255 x_msg_data := l_msg_data;
1256 RAISE FND_API.g_exc_error;
1257 END IF;
1258 END LOOP;
1259 END IF;
1260 END IF;
1261
1262 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1263 printDebugLog('Label association flag:'|| l_label_associate_item);
1264 printDebugLog('Label flag:'|| p_label_flag);
1265 END IF;
1266 IF (p_label_flag = 'Y') AND (l_label_associate_item = 'Y') THEN
1267 -- l_assoc_type_codes := JTF_VARCHAR2_TABLE_100();
1268 -- l_assoc_objects := JTF_VARCHAR2_TABLE_300();
1269 -- l_assoc_type_codes.extend(1);
1270 -- l_assoc_objects.extend(1);
1271 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1272 printDebugLog('Labeling starts');
1273 printDebugLog('p_new_content_item_id:'||l_content_item_id);
1274 printDebugLog('p_new_version_number:'||l_citem_version_id);
1275 printDebugLog('p_media_object_id:'||px_attachment_rec.item_id);
1276 printDebugLog('p_association_type_code:'||l_association_type);
1277 END IF;
1278 IF (l_content_item_id IS NOT NULL) AND (l_citem_version_id IS NOT NULL) THEN
1279 OPEN c_get_version_number(l_citem_version_id);
1280 FETCH c_get_version_number INTO l_version_number;
1281 CLOSE c_get_version_number;
1282 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1283 printDebugLog('p_new_version_number:'||l_version_number);
1284 END IF;
1285 IF (l_move_label = 1) THEN
1286 l_old_item := l_content_item_id;
1287 OPEN c_get_version_number(l_old_version);
1288 FETCH c_get_version_number INTO l_old_ver_num;
1289 CLOSE c_get_version_number;
1290 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1291 printDebugLog('p_old_version_number:'||l_old_ver_num);
1292 END IF;
1293 ELSE
1294 l_old_item := NULL;
1295 l_old_ver_num := NULL;
1296 END IF;
1297 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1298 printDebugLog('Update_Label_Association Starts');
1299 printDebugLog('p_old_content_item_id:'||l_old_item);
1300 printDebugLog('p_old_version_number:'||l_old_ver_num);
1301 printDebugLog('p_new_content_item_id:'||l_content_item_id);
1302 printDebugLog('p_new_version_number:'||l_version_number);
1303 printDebugLog('p_media_object_id:'||px_attachment_rec.item_id);
1304 printDebugLog('p_association_type_code:'||l_association_type);
1305 END IF;
1306 IF (l_version_number IS NOT NULL) THEN
1307 IBE_M_IBC_INT_PVT.Update_Label_Association(
1308 p_api_version => 1.0,
1309 p_init_msg_list => FND_API.G_FALSE,
1310 p_commit => FND_API.G_FALSE,
1311 p_old_content_item_id => l_old_item,
1312 p_old_version_number => l_old_ver_num,
1313 p_new_content_item_id => l_content_item_id,
1314 p_new_version_number => l_version_number,
1315 p_media_object_id => px_attachment_rec.item_id,
1316 p_association_type_code => l_association_type,
1317 x_return_status => l_return_status,
1318 x_msg_count => l_msg_count,
1319 x_msg_data => l_msg_data);
1320 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1321 printDebugLog(' Labeling ends:'||l_return_status);
1322 END IF;
1323 IF l_return_status <> FND_API.g_ret_sts_success THEN
1324 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1325 printDebugLog(' Error in labeling and associating content item!');
1326 END IF;
1327 for i in 1..l_msg_count loop
1328 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1329 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1330 printDebugLog(' '||l_msg_data);
1331 END IF;
1332 end loop;
1333 x_msg_data := l_msg_data;
1334 RAISE FND_API.g_exc_error;
1335 END IF;
1336 END IF;
1337 END IF;
1338 END IF;
1339
1340 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1341 printDebugLog('id:'||l_lgl_phys_map_id);
1342 printDebugLog('msite:'||px_attachment_rec.msite_id);
1343 printDebugLog('item_id:'||px_attachment_rec.item_id);
1344 printDebugLog('def site:'||px_attachment_rec.default_site);
1345 printDebugLog('content key:'||TO_CHAR(l_content_item_id));
1346 END IF;
1347 IF (l_content_item_id IS NOT NULL) AND (px_attachment_rec.item_id IS NOT NULL)
1348 AND (px_attachment_rec.msite_id IS NOT NULL)
1349 AND (px_attachment_rec.default_site IS NOT NULL) THEN
1350 OPEN c_check_map(px_attachment_rec.item_id,px_attachment_rec.msite_id,
1351 'OCM', px_attachment_rec.default_site, 'Y');
1352 FETCH c_check_map INTO l_j;
1353 IF (c_check_map%NOTFOUND) THEN
1354 l_j := 0;
1355 END IF;
1356 CLOSE c_check_map;
1357 IF (l_j = 0) THEN
1358 OPEN c_get_lgl_phys_map_id_csr;
1359 FETCH c_get_lgl_phys_map_id_csr INTO l_lgl_phys_map_id;
1360 CLOSE c_get_lgl_phys_map_id_csr;
1361 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1362 printDebugLog('id:'||l_lgl_phys_map_id);
1363 printDebugLog('msite:'||px_attachment_rec.msite_id);
1364 printDebugLog('item_id:'||px_attachment_rec.item_id);
1365 printDebugLog('def site:'||px_attachment_rec.default_site);
1366 printDebugLog('content key:'||TO_CHAR(l_content_item_id));
1367 END IF;
1368 x_msg_data := 'Error when creating mapping';
1369 INSERT INTO ibe_dsp_lgl_phys_map
1370 (LGL_PHYS_MAP_ID,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1371 LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,MSITE_ID,
1372 LANGUAGE_CODE, ATTACHMENT_ID, ITEM_ID, DEFAULT_LANGUAGE,
1373 DEFAULT_SITE, CONTENT_ITEM_KEY)
1374 VALUES
1375 (l_lgl_phys_map_id, 1, FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.user_id,
1376 SYSDATE, FND_GLOBAL.user_id, px_attachment_rec.msite_id,
1377 'OCM', -1, px_attachment_rec.item_id,
1378 'Y', px_attachment_rec.default_site, TO_CHAR(l_content_item_id));
1379 END IF;
1380 END IF;
1381 x_msg_data := '';
1382 x_return_status := FND_API.g_ret_sts_success;
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 ROLLBACK TO process_content_item;
1386 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1387 printDebugLog(' Exception in process_content_item:');
1388 printDebugLog(' '||SQLCODE||'-'||SQLERRM);
1389 END IF;
1390 x_return_status := FND_API.g_ret_sts_error;
1391 END process_content_item;
1392
1393 PROCEDURE process_content_items(
1394 p_label_flag IN VARCHAR2,
1395 px_attachment_tbl IN OUT NOCOPY ATTACHMENT_TBL_TYPE,
1396 x_return_status OUT NOCOPY VARCHAR2,
1397 x_msg_count OUT NOCOPY NUMBER,
1398 x_msg_data OUT NOCOPY VARCHAR2)
1399 IS
1400 l_i NUMBER;
1401 l_j NUMBER;
1402 l_duplicate VARCHAR2(1);
1403 l_return_status VARCHAR2(1);
1404 l_msg_count NUMBER;
1405 l_msg_data VARCHAR2(2000);
1406
1407 l_date DATE;
1408 l_list_flag VARCHAR2(1);
1409 l_item_id NUMBER;
1410 CURSOR c_get_content_item(c_item_ref_code VARCHAR2) IS
1411 SELECT content_item_id, creation_date
1412 FROM ibc_content_items
1413 WHERE item_reference_code = c_item_ref_code;
1414
1415 BEGIN
1416 SAVEPOINT process_content_items;
1417 l_list_flag := 'Y';
1418 x_return_status := FND_API.g_ret_sts_success;
1419 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1420 printDebugLog(' process_content_items begin');
1421 END IF;
1422 IF px_attachment_tbl.count > 0 THEN
1423 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1424 printDebugLog(' content item number='||px_attachment_tbl.count);
1425 END IF;
1426 FOR l_i IN 1..px_attachment_tbl.count LOOP
1427 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1428 printDebugLog(' Content item '||l_i
1429 ||' item code:'||px_attachment_tbl(l_i).content_item_code
1430 ||' file id:'||px_attachment_tbl(l_i).file_id
1431 ||' file name:'||px_attachment_tbl(l_i).file_name);
1432 END IF;
1433 IF (px_attachment_tbl(l_i).file_id <> -1) AND
1434 (px_attachment_tbl(l_i).file_id IS NOT NULL) THEN
1435 px_attachment_tbl(l_i).duplicate_flag := 'N';
1436 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1437 printDebugLog('Check duplicate');
1438 END IF;
1439 FOR l_j IN 1..(l_i-1) LOOP
1440 l_duplicate := compareContentItem(l_src_att => px_attachment_tbl(l_i),
1441 l_tar_att => px_attachment_tbl(l_j));
1442 IF l_duplicate = 'Y' THEN
1443 px_attachment_tbl(l_i).duplicate_flag := l_duplicate;
1444 px_attachment_tbl(l_i).CONTENT_ITEM_ID
1445 := px_attachment_tbl(l_j).content_item_id;
1446 px_attachment_tbl(l_i).CITEM_VERSION_ID
1447 := px_attachment_tbl(l_j).citem_version_id;
1448 px_attachment_tbl(l_i).CONTENT_ITEM_CODE
1449 := px_attachment_tbl(l_j).content_item_code;
1450 END IF;
1451 END LOOP;
1452 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1453 printDebugLog(' Duplicate flag='
1454 || px_attachment_tbl(l_i).duplicate_flag);
1455 END IF;
1456 contentItemType(p_attachment_rec => px_attachment_tbl(l_i));
1457 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1458 printDebugLog(' Content type='
1459 || px_attachment_tbl(l_i).content_type_code);
1460 END IF;
1461 -- Check list flag
1462 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1463 printDebugLog(' Check list flag');
1464 END IF;
1465 OPEN c_get_content_item(px_attachment_tbl(l_i).CONTENT_ITEM_CODE);
1466 FETCH c_get_content_item INTO l_item_id, l_date;
1467 IF c_get_content_item%FOUND THEN
1468 IF (l_date < g_start_time) THEN
1469 l_list_flag := 'N';
1470 END IF;
1471 END IF;
1472 CLOSE c_get_content_item;
1473 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1474 printDebugLog(' List flag='||l_list_flag);
1475 END IF;
1476 l_return_status := FND_API.g_ret_sts_success;
1477 IF g_mode = 'EXECUTION' THEN
1478 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1479 printDebugLog(' process_content_item start');
1480 printDebugLog(' p_label_flag='||p_label_flag);
1481 END IF;
1482 process_content_item(
1483 p_label_flag => p_label_flag,
1484 px_attachment_rec => px_attachment_tbl(l_i),
1485 x_return_status => l_return_status,
1486 x_msg_count => l_msg_count,
1487 x_msg_data => l_msg_data);
1488 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1489 printDebugLog(' process_content_item return:'||l_return_status);
1490 END IF;
1491 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
1492 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1493 printDebugLog(' Error in processing content item');
1494 printDebugLog(' '||l_msg_data);
1495 END IF;
1496 x_msg_data := l_msg_data;
1497 RAISE FND_API.g_exc_error;
1498 END IF;
1499 END IF;
1500 IF (l_return_status = FND_API.g_ret_sts_success) THEN
1501 IF (l_list_flag = 'Y') THEN
1502 IF (px_attachment_tbl(l_i).duplicate_flag = 'N') THEN
1503 g_item_created := g_item_created + 1;
1504 END IF;
1505 g_idx_created := g_idx_created + 1;
1506 MIGRATED_CONTENT(g_idx_created).access_name
1507 := px_attachment_tbl(l_i).ACCESS_NAME;
1508 MIGRATED_CONTENT(g_idx_created).seed_flag
1509 := px_attachment_tbl(l_i).SEED_DATA_FLAG;
1510 MIGRATED_CONTENT(g_idx_created).store_code
1511 := px_attachment_tbl(l_i).STORE_CODE;
1512 MIGRATED_CONTENT(g_idx_created).content_item_code
1513 := px_attachment_tbl(l_i).CONTENT_ITEM_CODE;
1514 MIGRATED_CONTENT(g_idx_created).language
1515 := SUBSTR(px_attachment_tbl(l_i).LANGDESC,1,30);
1516 MIGRATED_CONTENT(g_idx_created).file_name
1517 := px_attachment_tbl(l_i).FILE_NAME;
1518 IF (px_attachment_tbl(l_i).start_trans <> -1) THEN
1519 FOR l_j IN px_attachment_tbl(l_i).start_trans..px_attachment_tbl(l_i).end_trans LOOP
1520 g_idx_created := g_idx_created + 1;
1521 MIGRATED_CONTENT(g_idx_created).access_name
1522 -- := NULL;
1523 := px_attachment_tbl(l_i).ACCESS_NAME;
1524 MIGRATED_CONTENT(g_idx_created).seed_flag
1525 -- := NULL;
1526 := px_attachment_tbl(l_i).SEED_DATA_FLAG;
1527 MIGRATED_CONTENT(g_idx_created).store_code
1528 -- := NULL;
1529 := px_attachment_tbl(l_i).STORE_CODE;
1530 MIGRATED_CONTENT(g_idx_created).content_item_code
1531 -- := NULL;
1532 := px_attachment_tbl(l_i).CONTENT_ITEM_CODE;
1533 MIGRATED_CONTENT(g_idx_created).language
1534 := SUBSTR(l_trans_attachment_tbl(l_j).TRANS_LANGDESC,1,30);
1535 MIGRATED_CONTENT(g_idx_created).file_name
1536 := l_trans_attachment_tbl(l_j).FILE_NAMES;
1537 END LOOP;
1538 END IF;
1539 IF (px_attachment_tbl(l_i).duplicate_flag = 'N') THEN
1540 IF px_attachment_tbl(l_i).content_type_code = g_image_type THEN
1541 g_image_items := g_image_items + 1;
1542 ELSIF px_attachment_tbl(l_i).content_type_code = g_media_type THEN
1543 g_media_items := g_media_items + 1;
1544 ELSIF px_attachment_tbl(l_i).content_type_code = g_html_type THEN
1545 g_html_items := g_html_items + 1;
1546 END IF;
1547 END IF;
1548 IF (px_attachment_tbl(l_i).conflict_flag = 'Y')
1549 AND (px_attachment_tbl(l_i).duplicate_flag = 'N') THEN
1550 g_item_conflict := g_item_conflict + 1;
1551 g_idx_conflict := g_idx_conflict + 1;
1552 CONFLICT_CONTENT(g_idx_conflict).access_name
1553 := px_attachment_tbl(l_i).ACCESS_NAME;
1554 CONFLICT_CONTENT(g_idx_conflict).seed_flag
1555 := px_attachment_tbl(l_i).SEED_DATA_FLAG;
1556 CONFLICT_CONTENT(g_idx_conflict).store_code
1557 := px_attachment_tbl(l_i).STORE_CODE;
1558 CONFLICT_CONTENT(g_idx_conflict).content_item_code
1559 := px_attachment_tbl(l_i).CONTENT_ITEM_CODE;
1560 CONFLICT_CONTENT(g_idx_conflict).language
1561 := SUBSTR(px_attachment_tbl(l_i).LANGDESC,1,30);
1562 CONFLICT_CONTENT(g_idx_conflict).file_name
1563 := px_attachment_tbl(l_i).FILE_NAME;
1564 IF (px_attachment_tbl(l_i).start_trans <> -1) THEN
1565 FOR l_j IN px_attachment_tbl(l_i).start_trans..px_attachment_tbl(l_i).end_trans LOOP
1566 g_idx_conflict := g_idx_conflict + 1;
1567 CONFLICT_CONTENT(g_idx_conflict).access_name
1568 -- := NULL;
1569 := px_attachment_tbl(l_i).ACCESS_NAME;
1570 CONFLICT_CONTENT(g_idx_conflict).seed_flag
1571 -- := NULL;
1572 := px_attachment_tbl(l_i).SEED_DATA_FLAG;
1573 CONFLICT_CONTENT(g_idx_conflict).store_code
1574 -- := NULL;
1575 := px_attachment_tbl(l_i).STORE_CODE;
1576 CONFLICT_CONTENT(g_idx_conflict).content_item_code
1577 -- := NULL;
1578 := px_attachment_tbl(l_i).CONTENT_ITEM_CODE;
1579 CONFLICT_CONTENT(g_idx_conflict).language
1580 := SUBSTR(l_trans_attachment_tbl(l_j).TRANS_LANGDESC,1,30);
1581 CONFLICT_CONTENT(g_idx_conflict).file_name
1582 := l_trans_attachment_tbl(l_j).FILE_NAMES;
1583 END LOOP;
1584 END IF; -- translation end if
1585 END IF; -- conflict end if
1586 END IF; -- Check if the item should be list or not
1587 END IF; -- end of statistics
1588 END IF;
1589 END LOOP;
1590 COMMIT;
1591 END IF;
1592 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1593 printDebuglog(' process_content_items end');
1594 END IF;
1595 x_msg_data := '';
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 ROLLBACK TO process_content_items;
1599 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1600 printDebuglog(' exception in process_content_items end');
1601 printDebuglog(' '||SQLCODE||'-'||SQLERRM);
1602 END IF;
1603 x_return_status := FND_API.g_ret_sts_error;
1604 END process_content_items;
1605
1606 PROCEDURE clean_data(x_return_status OUT NOCOPY VARCHAR2)
1607 IS
1608 CURSOR c_get_date_csr(c_code VARCHAR2,
1609 c_status VARCHAR2) IS
1610 SELECT last_update_date
1611 FROM IBE_MIGRATION_HISTORY
1612 WHERE migration_code = c_code
1613 AND status = c_status;
1614 CURSOR c1(l_date IN DATE) IS
1615 SELECT attachment_id,object_version_number FROM jtf_amv_attachments a
1616 WHERE file_id IS NOT NULL
1617 AND application_id = 671
1618 AND last_update_date < l_date
1619 AND attachment_used_by = 'ITEM'
1620 AND NOT EXISTS (
1621 SELECT NULL
1622 FROM ibe_dsp_lgl_phys_map b, jtf_amv_items_b c
1623 WHERE a.attachment_id = b.attachment_id
1624 AND b.item_id = c.item_id
1625 AND c.deliverable_type_code = 'TEMPLATE');
1626
1627
1628
1629 l_date DATE;
1630 l_api_version NUMBER := 1.0;
1631 x_msg_count NUMBER;
1632 x_msg_data VARCHAR2(2000);
1633
1634 BEGIN
1635 SAVEPOINT clean_data;
1636 OPEN c_get_date_csr('IBE_OCM_MIG','SUCCESS');
1637 FETCH c_get_date_csr INTO l_date;
1638 IF c_get_date_csr%FOUND THEN
1639 CLOSE c_get_date_csr;
1640 -- Add type checking for media mapping only
1641 -- Fix perf bug 2854588, sql id 5044423
1642 DELETE FROM ibe_dsp_lgl_phys_map a
1643 WHERE lgl_phys_map_id >= 10000
1644 AND content_item_key IS NULL
1645 AND last_update_date <= l_date
1646 AND attachment_id <> -1
1647 AND EXISTS (
1648 SELECT NULL
1649 FROM jtf_amv_items_b c
1650 WHERE a.item_id = c.item_id
1651 AND c.deliverable_type_code = 'MEDIA'
1652 AND c.application_id = 671);
1653
1654
1655 -- Fix bug 2854588, sql id 5044426
1656 FOR r1 IN c1(l_date) LOOP
1657 JTF_AMV_ATTACHMENT_PUB.delete_act_attachment(
1658 p_api_version => l_api_version,
1659 x_return_status => x_return_status,
1660 x_msg_count => x_msg_count,
1661 x_msg_data => x_msg_data,
1662 p_act_attachment_id =>r1.attachment_id,
1663 p_object_version =>r1.object_version_number);
1664 END LOOP;
1665
1666 -- DELETE FROM jtf_amv_attachments a
1667 -- WHERE file_id IS NOT NULL
1668 -- AND application_id = 671
1669 -- AND last_update_date < l_date
1670 -- AND attachment_used_by = 'ITEM'
1671 -- AND NOT EXISTS (
1672 -- SELECT NULL
1673 -- FROM ibe_dsp_lgl_phys_map b, jtf_amv_items_b c
1674 -- WHERE a.attachment_id = b.attachment_id
1675 -- AND b.item_id = c.item_id
1676 -- AND c.deliverable_type_code = 'TEMPLATE');
1677 COMMIT;
1678 ELSE
1679 CLOSE c_get_date_csr;
1680 END IF;
1681 x_return_status := FND_API.g_ret_sts_success;
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 ROLLBACK TO clean_data;
1685 x_return_status := FND_API.g_ret_sts_error;
1686 END clean_data;
1687
1688 PROCEDURE attachment_mig(p_mode IN VARCHAR2,
1689 p_default_mig_lang IN VARCHAR2,
1690 x_status OUT NOCOPY VARCHAR2)
1691 IS
1692 -- This cursor is to get installed languages
1693 -- in current database instance
1694 CURSOR c_get_installed_lang_csr IS
1695 SELECT language_code, description
1696 FROM fnd_languages_vl
1697 WHERE installed_flag in ('I','B');
1698
1699 -- This cursor is to get all customer media objects
1700 -- It is using last_updated_by instead of lgl_phys_map_id
1701 -- to make sure the data owner is 'SEED'.
1702 CURSOR c_get_item_csr IS
1703 select b.item_id, b.access_name
1704 from jtf_amv_items_b b
1705 where b.deliverable_type_code <> 'TEMPLATE'
1706 and b.application_id = 671
1707 ORDER BY b.item_id;
1708 -- CURSOR c_get_item_csr IS
1709 -- select b.item_id, b.access_name
1710 -- from jtf_amv_items_b b
1711 -- where b.deliverable_type_code <> 'TEMPLATE'
1712 -- and b.application_id = 671
1713 -- and exists (select null
1714 -- from ibe_dsp_lgl_phys_map a
1715 -- where a.item_id = b.item_id
1716 -- and a.last_updated_by <> 1)
1717 -- ORDER BY b.item_id;
1718
1719 -- This cursor is to get all defined msite mapping
1720 -- for a media object
1721 CURSOR c_get_item_sites_csr(c_item_id NUMBER) IS
1722 SELECT b.msite_id, b.msite_name, decode(b.msite_id,1,'Y','N'),
1723 b.default_language_code
1724 from ibe_msites_vl b
1725 where EXISTS (select NULL
1726 FROM ibe_dsp_lgl_phys_map a
1727 WHERE a.msite_id = b.msite_id
1728 AND a.item_id = c_item_id
1729 AND b.site_type = 'I'
1730 AND a.content_item_key IS NULL)
1731 ORDER BY b.msite_id ASC;
1732
1733 -- This cursor is to check if a seeded media object
1734 -- has default seeded mapping
1735 CURSOR c_get_seed_flag_csr(c_item_id NUMBER) IS
1736 SELECT 'Y'
1737 FROM ibe_dsp_lgl_phys_map
1738 WHERE content_item_key IS NULL
1739 AND default_site = 'Y'
1740 AND item_id = c_item_id
1741 AND lgl_phys_map_id < 10000;
1742
1743 -- This cursor is to find all attachments defined
1744 -- in content repository and are not mapped to media objects
1745 -- yet
1746 -- Fix bug 2854588, sql id 5044448
1747 CURSOR c_get_content_repository_csr IS
1748 SELECT distinct file_id, file_name, display_height, display_width
1749 FROM jtf_amv_attachments a
1750 WHERE application_id = 671
1751 AND file_id IS NOT NULL
1752 AND attachment_used_by = 'ITEM'
1753 AND NOT EXISTS (
1754 SELECT NULL
1755 FROM ibe_dsp_lgl_phys_map b
1756 WHERE b.attachment_id = a.attachment_id);
1757
1758 l_lang VARCHAR2(4);
1759 l_langdesc VARCHAR2(255);
1760 l_default_langdesc VARCHAR2(255);
1761 l_i NUMBER;
1762 l_j NUMBER;
1763 l_k NUMBER;
1764 l_index NUMBER;
1765 l_file_id NUMBER;
1766 l_file_name VARCHAR2(240);
1767 l_height NUMBER;
1768 l_width NUMBER;
1769
1770 l_return_status VARCHAR2(1);
1771 l_msg_count NUMBER;
1772 l_msg_data VARCHAR2(2000);
1773
1774 l_store_code VARCHAR2(240) := NULL;
1775 l_store_lang VARCHAR2(4);
1776 l_item_id NUMBER;
1777 l_access_name VARCHAR2(40);
1778 l_msite_id NUMBER;
1779 l_default_site VARCHAR2(3);
1780 l_language_code VARCHAR2(4);
1781 l_default_language VARCHAR2(4);
1782 l_translate_flag VARCHAR2(1);
1783 BEGIN
1784 l_i := 0;
1785 OPEN c_get_installed_lang_csr;
1786 LOOP
1787 FETCH c_get_installed_lang_csr INTO l_lang, l_langdesc;
1788 EXIT WHEN c_get_installed_lang_csr%NOTFOUND;
1789 l_i := l_i + 1;
1790 INSTALLED_LANGUAGES(l_i) := l_lang;
1791 INSTALLED_LANGDESC(l_i) := l_langdesc;
1792 IF (l_lang = p_default_mig_lang) THEN
1793 l_default_langdesc := l_langdesc;
1794 END IF;
1795 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1796 printDebuglog('Installed language '||l_i||':'||l_lang);
1797 END IF;
1798 END LOOP;
1799 CLOSE c_get_installed_lang_csr;
1800 l_i := 0;
1801 l_k := 0;
1802
1803 -- Migration will based on media objects
1804 OPEN c_get_item_csr;
1805 LOOP
1806 FETCH c_get_item_csr INTO l_item_id, l_access_name;
1807 EXIT WHEN c_get_item_csr%NOTFOUND;
1808 l_i := 0;
1809 l_k := 0;
1810 l_index := 0;
1811 l_attachment_tbl.delete;
1812 l_trans_attachment_tbl.delete;
1813
1814 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1815 printDebuglog('Item id:'||l_item_id||' Access name:'||l_access_name);
1816 END IF;
1817 -- Find all minisites this media object linked to
1818 OPEN c_get_item_sites_csr(l_item_id);
1819 LOOP
1820 FETCH c_get_item_sites_csr INTO l_msite_id, l_store_code, l_default_site,
1821 l_store_lang;
1822 EXIT WHEN c_get_item_sites_csr%NOTFOUND;
1823 l_i := l_i + 1;
1824 l_index := l_index + 1;
1825 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1826 printDebuglog(' Msite id:'||l_msite_id||' Store code:'||l_store_code||
1827 ' default site:'||l_default_site||' Store lang:'||l_store_lang);
1828 END IF;
1829 l_attachment_tbl(l_i).item_id := l_item_id;
1830 l_attachment_tbl(l_i).access_name := l_access_name;
1831 l_attachment_tbl(l_i).msite_id := l_msite_id;
1832 l_attachment_tbl(l_i).store_code := l_store_code;
1833 l_attachment_tbl(l_i).default_site := l_default_site;
1834 l_attachment_tbl(l_i).seed_data_flag := 'N';
1835 -- For item code validation
1836 l_attachment_tbl(l_i).CONTENT_ITEM_CODE
1837 := NLS_UPPER('IBEMGR_'||l_access_name||'_'||l_index);
1838 IF (l_default_site = 'Y') THEN
1839 l_attachment_tbl(l_i).store_code := 'ALL';
1840 l_attachment_tbl(l_i).language := p_default_mig_lang;
1841 -- Seeded logical item
1842 IF (l_item_id < 10000) THEN
1843 OPEN c_get_seed_flag_csr(l_item_id);
1844 FETCH c_get_seed_flag_csr INTO l_attachment_tbl(l_i).seed_data_flag;
1845 IF (c_get_seed_flag_csr%NOTFOUND) THEN
1846 l_attachment_tbl(l_i).seed_data_flag := 'N';
1847 END IF;
1848 CLOSE c_get_seed_flag_csr;
1849 END IF;
1850 ELSE
1851 l_attachment_tbl(l_i).language := l_store_lang;
1852 END IF;
1853 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1854 printDebuglog(' Seed data:'||l_attachment_tbl(l_i).seed_data_flag);
1855 printDebuglog(' Before get_base_content:');
1856 printDebuglog(' p_item_id='||l_attachment_tbl(l_i).item_id);
1857 printDebuglog(' p_msite_id='||l_attachment_tbl(l_i).msite_id);
1858 printDebuglog(' p_default_msite='||l_attachment_tbl(l_i).default_site);
1859 printDebuglog(' p_language_code='||l_attachment_tbl(l_i).language);
1860 END IF;
1861 get_base_content(p_item_id => l_attachment_tbl(l_i).item_id,
1862 p_msite_id => l_attachment_tbl(l_i).msite_id,
1863 p_default_msite => l_attachment_tbl(l_i).default_site,
1864 p_language_code => l_attachment_tbl(l_i).language,
1865 x_file_id => l_attachment_tbl(l_i).file_id,
1866 x_file_name => l_attachment_tbl(l_i).file_name,
1867 x_height => l_attachment_tbl(l_i).height,
1868 x_width => l_attachment_tbl(l_i).width,
1869 x_translate_flag => l_translate_flag);
1870 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1871 printDebuglog(' After get_base_content:');
1872 printDebuglog(' x_file_id='||l_attachment_tbl(l_i).file_id);
1873 printDebuglog(' x_file_name='||l_attachment_tbl(l_i).file_name);
1874 printDebuglog(' x_height='||l_attachment_tbl(l_i).height);
1875 printDebuglog(' x_width='||l_attachment_tbl(l_i).width);
1876 printDebuglog(' x_translate_flag='||l_translate_flag);
1877 END IF;
1878 -- Get all mapping for this media object and the specific minisite
1879 l_attachment_tbl(l_i).start_trans := -1;
1880 l_attachment_tbl(l_i).end_trans := -1;
1881 FOR l_j IN 1..installed_languages.COUNT LOOP
1882 IF installed_languages(l_j)=l_attachment_tbl(l_i).language THEN
1883 l_attachment_tbl(l_i).langdesc := installed_langdesc(l_j);
1884 ELSE
1885 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1886 printDebuglog(' Before get_trans_content:');
1887 printDebuglog(' p_item_id='||l_attachment_tbl(l_i).item_id);
1888 printDebuglog(' p_msite_id='||l_attachment_tbl(l_i).msite_id);
1889 printDebuglog(' p_default_msite='||l_attachment_tbl(l_i).default_site);
1890 printDebuglog(' p_language_code='||l_attachment_tbl(l_i).language);
1891 printDebuglog(' p_translate_flag='||l_translate_flag);
1892 END IF;
1893 get_trans_content(p_item_id => l_attachment_tbl(l_i).item_id,
1894 p_msite_id => l_attachment_tbl(l_i).msite_id,
1895 p_default_msite => l_attachment_tbl(l_i).default_site,
1896 p_language_code => installed_languages(l_j),
1897 p_translate_flag => l_translate_flag,
1898 x_file_id => l_file_id,
1899 x_file_name => l_file_name,
1900 x_height => l_height,
1901 x_width => l_width);
1902 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1903 printDebuglog(' After get_trans_content:');
1904 printDebuglog(' x_file_id='||l_file_id);
1905 printDebuglog(' x_file_name='||l_file_name);
1906 printDebuglog(' x_height='||l_height);
1907 printDebuglog(' x_width='||l_width);
1908 END IF;
1909 IF (l_file_id IS NOT NULL AND l_file_id <> -1) THEN
1910 l_k := l_k + 1;
1911 IF (l_attachment_tbl(l_i).start_trans = -1) THEN
1912 l_attachment_tbl(l_i).start_trans := l_k;
1913 END IF;
1914 l_trans_attachment_tbl(l_k).TRANS_LANGUAGES
1915 := installed_languages(l_j);
1916 l_trans_attachment_tbl(l_k).TRANS_LANGDESC
1917 := installed_langdesc(l_j);
1918 l_trans_attachment_tbl(l_k).FILE_IDS := l_file_id;
1919 l_trans_attachment_tbl(l_k).FILE_NAMES := l_file_name;
1920 l_trans_attachment_tbl(l_k).HIGHTS := l_height;
1921 l_trans_attachment_tbl(l_k).WIDTHS := l_width;
1922 l_attachment_tbl(l_i).end_trans := l_k;
1923 END IF;
1924 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1925 printDebuglog(' tran start='||l_attachment_tbl(l_i).start_trans);
1926 printDebuglog(' tran end='||l_attachment_tbl(l_i).end_trans);
1927 END IF;
1928 END IF;
1929 END LOOP;
1930 END LOOP;
1931 CLOSE c_get_item_sites_csr;
1932 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1933 printDebuglog(' Before process_content_items');
1934 END IF;
1935 process_content_items(
1936 p_label_flag => 'Y',
1937 px_attachment_tbl => l_attachment_tbl,
1938 x_return_status => l_return_status,
1939 x_msg_count => l_msg_count,
1940 x_msg_data => l_msg_data);
1941 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1942 printDebuglog(' process_content_items return:'||l_return_status);
1943 END IF;
1944 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1945 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1946 printDebuglog(' exception in process_content_items');
1947 END IF;
1948 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1949 END IF;
1950 END LOOP;
1951 CLOSE c_get_item_csr;
1952 -- Migrate all unused attachments in content repository
1953 l_i := 0;
1954 l_k := 0;
1955 l_index := 0;
1956 l_attachment_tbl.delete;
1957 l_trans_attachment_tbl.delete;
1958 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1959 printDebuglog('Content repository migration');
1960 END IF;
1961 OPEN c_get_content_repository_csr;
1962 LOOP
1963 FETCH c_get_content_repository_csr INTO l_file_id,l_file_name,l_height,l_width;
1964 EXIT WHEN c_get_content_repository_csr%NOTFOUND;
1965 l_i := l_i + 1;
1966 l_index := l_index + 1;
1967 l_attachment_tbl(l_i).item_id := NULL;
1968 l_attachment_tbl(l_i).access_name := '';
1969 l_attachment_tbl(l_i).msite_id := NULL;
1970 l_attachment_tbl(l_i).store_code := '';
1971 l_attachment_tbl(l_i).default_site := NULL;
1972 l_attachment_tbl(l_i).seed_data_flag := 'N';
1973 l_attachment_tbl(l_i).CONTENT_ITEM_CODE
1974 := 'IBEMGR_CONTENT_REPOSITORY'||'_'||l_index;
1975 l_attachment_tbl(l_i).language := p_default_mig_lang;
1976 l_attachment_tbl(l_i).langdesc := l_default_langdesc;
1977 l_attachment_tbl(l_i).start_trans := -1;
1978 l_attachment_tbl(l_i).end_trans := -1;
1979 l_attachment_tbl(l_i).file_id := l_file_id;
1980 l_attachment_tbl(l_i).file_name := l_file_name;
1981 l_attachment_tbl(l_i).height := l_height;
1982 l_attachment_tbl(l_i).width := l_width;
1983 END LOOP;
1984 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1985 printDebuglog(' Before process_content_items for content repository');
1986 END IF;
1987 process_content_items(
1988 p_label_flag => 'N',
1989 px_attachment_tbl => l_attachment_tbl,
1990 x_return_status => l_return_status,
1991 x_msg_count => l_msg_count,
1992 x_msg_data => l_msg_data);
1993 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1994 printDebuglog(' After process_content_items for content repository:'||l_return_status);
1995 END IF;
1996 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1997 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
1998 printDebuglog(' exception in process_content_items for content repository:');
1999 printDebuglog(' '||l_msg_data);
2000 END IF;
2001 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2002 END IF;
2003 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2004 printDebuglog('Content repository migration End');
2005 END IF;
2006 x_status := FND_API.g_ret_sts_success;
2007 EXCEPTION
2008 WHEN OTHERS THEN
2009 x_status := FND_API.g_ret_sts_error||l_msg_data;
2010 END attachment_mig;
2011
2012 PROCEDURE ocmMigration(errbuf OUT NOCOPY VARCHAR2,
2013 retcode OUT NOCOPY VARCHAR2,
2014 p_mode IN VARCHAR2,
2015 p_default_lang IN VARCHAR2,
2016 p_debug_flag IN VARCHAR2,
2017 p_clean_flag IN VARCHAR2)
2018 IS
2019 l_status VARCHAR2(1);
2020 l_set_prof BOOLEAN;
2021 l_msg_data VARCHAR2(2000);
2022 BEGIN
2023 IF (check_log(p_code => 'IBE_OCM_MIG',
2024 p_status => 'SUCCESS') = 0) THEN
2025 IF (p_mode = 'EXECUTION') THEN
2026 create_log(p_code => 'IBE_OCM_MIG',
2027 p_status => 'START',
2028 x_status => l_status);
2029 IF (l_status <> FND_API.G_RET_STS_SUCCESS) THEN
2030 l_msg_data := 'Error when creating log file';
2031 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2032 END IF;
2033 END IF;
2034 IF p_debug_flag = 'Y' THEN
2035 IBE_M_MIGRATION_PVT.g_debug := p_debug_flag;
2036 END IF;
2037 g_mode := p_mode;
2038 g_language := p_default_lang;
2039 g_start_time := SYSDATE;
2040 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2041 printDebuglog('Parameter list:');
2042 printDebuglog(' p_mode = '||p_mode);
2043 printDebuglog(' p_default_lang = '||p_default_lang);
2044 printDebuglog(' p_debug_flag = '||p_debug_flag);
2045 printDebuglog(' p_clean_flag = '||p_clean_flag);
2046 printDebuglog(' g_start_time = '
2047 ||to_char(g_start_time,'DD-MON-RRRR HH24:MI:SS'));
2048 END IF;
2049
2050 IF (p_mode = 'EXECUTION') THEN
2051 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2052 printDebuglog('Set IBE directory node to be hidden');
2053 END IF;
2054 -- Ibc_Directory_Nodes_Pkg.UPDATE_ROW (
2055 -- p_DIRECTORY_NODE_ID => 9 ,
2056 -- p_DIRECTORY_NODE_CODE => NULL,
2057 -- p_HIDDEN_FLAG => 'Y');
2058 BEGIN
2059 EXECUTE IMMEDIATE 'begin Ibc_Directory_Nodes_Pkg.UPDATE_ROW '||
2060 '(p_DIRECTORY_NODE_ID => 9 ,p_DIRECTORY_NODE_CODE => NULL, '||
2061 'p_HIDDEN_FLAG => ''Y''); END;';
2062 END;
2063 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2064 printDebuglog('After setring IBE directory node to be hidden');
2065 END IF;
2066 END IF;
2067
2068 -- Check all attachment files to be migrated
2069 -- can be recognized by the program
2070 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2071 printDebuglog('Check the attachment file type');
2072 END IF;
2073 attachType;
2074 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2075 printDebuglog('After checking the attachment file type');
2076 END IF;
2077 -- Migrate the attachments to OCM content item
2078 -- based on the logical items
2079 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2080 printDebuglog('Migrate attachment based on the mapping');
2081 printDebuglog(' p_mode = '||p_mode);
2082 printDebuglog(' p_default_mig_lang = '||p_default_lang);
2083 END IF;
2084 attachment_mig(p_mode => p_mode,
2085 p_default_mig_lang => p_default_lang,
2086 x_status => l_msg_data);
2087 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2088 printDebuglog('After migrating attachment:');
2089 printDebuglog(' status_message='||l_msg_data);
2090 END IF;
2091 l_status := substr(l_msg_data,1,length(FND_API.G_RET_STS_SUCCESS));
2092 IF (l_status <> FND_API.G_RET_STS_SUCCESS) THEN
2093 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2094 END IF;
2095 IF (p_mode = 'EXECUTION') THEN
2096 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2097 printDebuglog('Set the profile for iStore-OCM integration');
2098 END IF;
2099 l_msg_data := 'Error when setting up the profile';
2100 -- Set the integration profile to 'Y'
2101 -- l_set_prof := FND_PROFILE.save('IBE_M_USE_CONTENT_INTEGRATION','Y', 'APPL', '671');
2102
2103 --bug# 3407125-setting the profile value at site level
2104 l_set_prof := FND_PROFILE.save('IBE_M_USE_CONTENT_INTEGRATION','Y','SITE');
2105
2106 -- FND_PROFILE.put('IBE_M_USE_CONTENT_INTEGRATION', 'Y');
2107 l_msg_data := '';
2108 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2109 printDebuglog('After setting the profile for iStore-OCM integration');
2110 END IF;
2111 END IF;
2112 g_end_time := SYSDATE;
2113 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2114 printDebuglog('Print migration report');
2115 END IF;
2116 printReport;
2117 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2118 printDebuglog('After printing migration report');
2119 END IF;
2120 IF (p_mode = 'EXECUTION') THEN
2121 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2122 printDebuglog('Set IBE directory node to be visible');
2123 END IF;
2124 --Ibc_Directory_Nodes_Pkg.UPDATE_ROW (
2125 -- p_DIRECTORY_NODE_ID => 9 , -- This is the Folder id of iStore
2126 -- p_DIRECTORY_NODE_CODE => NULL,
2127 -- p_HIDDEN_FLAG => 'N');
2128 BEGIN
2129 EXECUTE IMMEDIATE 'begin Ibc_Directory_Nodes_Pkg.UPDATE_ROW '||
2130 '(p_DIRECTORY_NODE_ID => 9 ,p_DIRECTORY_NODE_CODE => NULL, '||
2131 'p_HIDDEN_FLAG => ''N''); END;';
2132 END;
2133 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2134 printDebuglog('After setting IBE directory node to be visible');
2135 END IF;
2136
2137 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2138 printDebuglog('Update log for migration');
2139 END IF;
2140 update_log(p_code => 'IBE_OCM_MIG',
2141 p_old_status => 'START',
2142 p_new_status => 'SUCCESS',
2143 x_status => l_status);
2144 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2145 printDebuglog('After updating log for migration:'||l_status);
2146 END IF;
2147 IF (l_status <> FND_API.G_RET_STS_SUCCESS) THEN
2148 l_msg_data := 'Error when updating log file';
2149 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2150 END IF;
2151 END IF;
2152 END IF;
2153 -- Insert the log into IBE_MIGRATION_HISTORY
2154 IF (p_clean_flag = 'Y') THEN
2155 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2156 printDebuglog('Clean old data');
2157 END IF;
2158 clean_data(x_return_status => l_status);
2159 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2160 printDebuglog('After cleaning old data:'||l_status);
2161 END IF;
2162 IF (l_status <> FND_API.g_ret_sts_success) THEN
2163 l_msg_data := 'Error when cleaning old data';
2164 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2165 END IF;
2166 END IF;
2167 retcode := 0;
2168 errbuf := 'SUCCESS';
2169 COMMIT;
2170 EXCEPTION
2171 WHEN OTHERS THEN
2172 IF (p_mode = 'EXECUTION') THEN
2173 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2174 printDebuglog('Exception occurs, need to set the folder visible!');
2175 printDebuglog('Set IBE directory node to be visible');
2176 END IF;
2177 --Ibc_Directory_Nodes_Pkg.UPDATE_ROW (
2178 -- p_DIRECTORY_NODE_ID => 9 , -- This is the Folder id of iStore
2179 -- p_DIRECTORY_NODE_CODE => NULL,
2180 -- p_HIDDEN_FLAG => 'N');
2181 BEGIN
2182 EXECUTE IMMEDIATE 'begin Ibc_Directory_Nodes_Pkg.UPDATE_ROW '||
2183 '(p_DIRECTORY_NODE_ID => 9 ,p_DIRECTORY_NODE_CODE => NULL, '||
2184 'p_HIDDEN_FLAG => ''N''); END;';
2185 COMMIT;
2186 END;
2187 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2188 printDebuglog('After setting IBE directory node to be visible');
2189 END IF;
2190 END IF;
2191 printOutput(l_msg_data||' '||SQLCODE||'-'||SQLERRM);
2192 IF IBE_M_MIGRATION_PVT.g_debug = 'Y' THEN
2193 printDebuglog(l_msg_data||' '||SQLCODE||'-'||SQLERRM);
2194 END IF;
2195 retcode := -1;
2196 errbuf := l_msg_data||' '||SQLCODE||'-'||SQLERRM;
2197 END ocmMigration;
2198
2199 END IBE_M_MIGRATION_PVT;