[Home] [Help]
PACKAGE BODY: APPS.JTF_FM_MD_MIGRATION_PVT
Source
1 PACKAGE BODY JTF_FM_MD_MIGRATION_PVT AS
2 /* $Header: JTFFMVMB.pls 120.0 2005/05/11 09:06:47 appldev noship $ */
3
4
5 g_idx_created NUMBER := 0;
6 g_item_created NUMBER := 0;
7 g_text_file_id NUMBER := NULL;
8
9 migrated_content MASTERDOC_QRY_TBL_TYPE;
10
11 l_query_tbl MASTERDOC_QRY_TBL_TYPE;
12 l_masterdoc_qry_tbl MASTERDOC_QRY_TBL_TYPE;
13
14 FUNCTION check_log(p_code IN VARCHAR2,
15 p_status IN VARCHAR2) RETURN NUMBER
16 IS
17 CURSOR c_check_log_csr(c_code VARCHAR2,
18 c_status VARCHAR2) IS
19 SELECT 1
20 FROM JTF_FM_MIGRATION_HISTORY
21 WHERE migration_code = c_code
22 AND status = c_status;
23 l_temp NUMBER;
24 BEGIN
25 OPEN c_check_log_csr(p_code, p_status);
26 FETCH c_check_log_csr INTO l_temp;
27 IF (c_check_log_csr%NOTFOUND) THEN
28 l_temp := 0;
29 END IF;
30 CLOSE c_check_log_csr;
31 RETURN l_temp;
32 END check_log;
33
34 PROCEDURE create_log(p_code IN VARCHAR2,
35 p_status IN VARCHAR2,
36 x_status OUT NOCOPY VARCHAR2)
37 IS
38 BEGIN
39 DELETE FROM JTF_FM_MIGRATION_HISTORY
40 WHERE MIGRATION_CODE = p_code;
41 INSERT INTO JTF_FM_MIGRATION_HISTORY(MIGRATION_CODE,
42 OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE,
43 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,STATUS)
44 VALUES(p_code,0, FND_GLOBAL.user_id, SYSDATE,
45 FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.user_id, p_status);
46 x_status := FND_API.G_RET_STS_SUCCESS;
47 EXCEPTION
48 WHEN OTHERS THEN
49 x_status := FND_API.G_RET_STS_ERROR;
50 END create_log;
51
52 PROCEDURE update_log(p_code IN VARCHAR2,
53 p_old_status IN VARCHAR2,
54 p_new_status IN VARCHAR2,
55 p_number IN NUMBER,
56 x_status OUT NOCOPY VARCHAR2)
57 IS
58 l_i NUMBER;
59 BEGIN
60 UPDATE JTF_FM_MIGRATION_HISTORY
61 SET STATUS = p_new_status,
62 LAST_UPDATE_DATE = SYSDATE
63 WHERE MIGRATION_CODE = p_code
64 AND STATUS = p_old_status;
65
66 FOR l_i IN p_number..MIGRATED_CONTENT.count LOOP
67 INSERT INTO JTF_FM_MIG_HISTORY_DETAILS
68 (MIGRATION_CODE,
69 ITEM_ID,
70 CONTENT_TYPE_CODE,
71 CONTENT_ITEM_ID,
72 CITEM_VERSION_ID,
73 FILE_ID,
74 FILE_NAME,
75 DESCRIPTION,
76 QUERY_CITEM_ID,
77 CREATED_BY,
78 CREATION_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_DATE,
81 LAST_UPDATE_LOGIN)
82 VALUES(MIGRATED_CONTENT(l_i).MIGRATION_CODE,
83 MIGRATED_CONTENT(l_i).ITEM_ID,
84 MIGRATED_CONTENT(l_i).CONTENT_TYPE_CODE,
85 MIGRATED_CONTENT(l_i).CONTENT_ITEM_ID,
86 MIGRATED_CONTENT(l_i).CITEM_VERSION_ID,
87 MIGRATED_CONTENT(l_i).FILE_ID,
88 MIGRATED_CONTENT(l_i).FILE_NAME,
89 MIGRATED_CONTENT(l_i).DESCRIPTION,
90 MIGRATED_CONTENT(l_i).QUERY_CITEM_ID,
91 FND_GLOBAL.user_id,
92 SYSDATE,
93 FND_GLOBAL.user_id,
94 SYSDATE,
95 FND_GLOBAL.user_id);
96 END LOOP;
97
98
99
100 x_status := FND_API.G_RET_STS_SUCCESS;
101 EXCEPTION
102 WHEN OTHERS THEN
103 x_status := FND_API.G_RET_STS_ERROR;
104 END update_log;
105
106
107
108 -------------------------------------------------
109 -- Debug Information Pring Procedure
110 -- Y : Display Debug
111 -- N: No Debug Statement Printout
112 PROCEDURE printDebuglog(p_debug_str IN VARCHAR2)
113 IS
114 BEGIN
115 -- printDebugLog(p_debug_str);
116 IF JTF_FM_MD_MIGRATION_PVT.g_debug = 'Y' THEN
117 --FND_FILE.PUT_LINE(FND_FILE.LOG,p_debug_str);
118 --DBMS_OUTPUT.PUT_LINE(p_debug_str);
119 NULL;
120 END IF;
121
122 END printDebugLog;
123
124 PROCEDURE printOutput(p_message IN VARCHAR2)
125 IS
126 BEGIN
127 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
128 --DBMS_OUTPUT.PUT_LINE(p_message);
129 NULL;
130 END printOutput;
131
132
133
134 PROCEDURE printReport
135 IS
136 l_i NUMBER;
137 l_name VARCHAR2(50) := NULL;
138 l_desc VARCHAR2(255) := NULL;
139 l_item_id NUMBER := NULL;
140 l_file_id NUMBER := NULL;
141 l_query_id NUMBER := NULL;
142 l_content_item_id NUMBER;
143 l_citem_ver_id NUMBER;
144 l_content_type_code VARCHAR2(100) := NULL;
145
146 l_temp_msg VARCHAR2(2000);
147 l_title1 VARCHAR2(2000);
148
149
150 BEGIN
151 -- printOutput('Running Time:'||to_char(g_start_time,'MM/DD/RRRR HH24:MI:SS')
152 -- ||'-'||to_char(g_end_time,'MM/DD/RRRR HH24:MI:SS'));
153 printOutput('');
154 printOutput('=================================');
155 -- Migration summary
156 fnd_message.set_name('JTF', 'JTF_FM_MSG_MGRT_SUMMRY');
157 l_temp_msg := fnd_message.get;
158 printOutput('1. '||l_temp_msg);
159 printOutput('=================================');
160 -- Number of content items created
161 fnd_message.set_name('JTF', 'JTF_FM_M_NUM_CONT_ITEM_DESC');
162 l_temp_msg := fnd_message.get;
163 printOutput(l_temp_msg||': '||to_char(g_item_created));
164 printOutput('');
165 FOR l_i IN 1..MIGRATED_CONTENT.count LOOP
166 IF MOD(l_i-1,25) = 0 THEN
167 printOutput('');
168 printOutput(l_title1);
169 printOutput('----------------- ----------------- '||
170 ' ---------- -------- ' ||
171 ' ----------------');
172 END IF;
173 IF (MIGRATED_CONTENT(l_i).ITEM_ID IS NULL) THEN
174 l_item_id := RPAD(' ',40,' ');
175 ELSE
176 l_item_id := RPAD(MIGRATED_CONTENT(l_i).ITEM_ID,40,' ');
177 END IF;
178 IF (MIGRATED_CONTENT(l_i).CONTENT_TYPE_CODE IS NULL) THEN
179 l_content_type_code := RPAD(' ',40,' ');
180 ELSE
181 l_content_type_code := RPAD(SUBSTR(MIGRATED_CONTENT(l_i).CONTENT_TYPE_CODE,1,40),40,' ');
182 END IF;
183
184 IF (MIGRATED_CONTENT(l_i).content_item_id IS NULL) THEN
185 l_content_item_id := RPAD(' ',40,' ');
186 ELSE
187 l_content_item_id
188 := RPAD(SUBSTR(MIGRATED_CONTENT(l_i).content_item_id,1,40),40,' ');
189 END IF;
190 IF (MIGRATED_CONTENT(l_i).CITEM_VERSION_ID IS NULL) THEN
191 l_CITEM_VER_ID:= RPAD(' ',40,' ');
192 ELSE
193 l_CITEM_VER_ID
194 := RPAD(SUBSTR(MIGRATED_CONTENT(l_i).CITEM_VERSION_ID,1,40),40,' ');
195 END IF;
196 IF (MIGRATED_CONTENT(l_i).FILE_ID IS NULL) THEN
197 l_FILE_ID:= RPAD(' ',40,' ');
198 ELSE
199 l_FILE_ID
200 := RPAD(SUBSTR(MIGRATED_CONTENT(l_i).FILE_ID,1,40),40,' ');
201 END IF;
202 IF (MIGRATED_CONTENT(l_i).FILE_NAME IS NULL) THEN
203 l_name:= RPAD(' ',40,' ');
204 ELSE
205 l_name
206 := RPAD(SUBSTR(MIGRATED_CONTENT(l_i).FILE_NAME,1,40),40,' ');
207 END IF;
208 IF (MIGRATED_CONTENT(l_i).DESCRIPTION IS NULL) THEN
209 l_desc:= RPAD(' ',40,' ');
210 ELSE
211 l_desc
212 := RPAD(SUBSTR(MIGRATED_CONTENT(l_i).DESCRIPTION,1,40),40,' ');
213 END IF;
214 IF (MIGRATED_CONTENT(l_i).QUERY_CITEM_ID IS NULL) THEN
215 l_query_id:= RPAD(' ',40,' ');
216 ELSE
217 l_query_id
218 := RPAD(SUBSTR(MIGRATED_CONTENT(l_i).QUERY_CITEM_ID,1,40),40,' ');
219 END IF;
220 printOutput(l_item_id||' '||l_content_type_code||' '||l_content_item_id||' '
221 ||l_citem_ver_id||' '
222 ||l_file_id||' ' || l_name ||' ' || l_desc ||' ' || l_query_id);
223
224 END LOOP;
225 END printReport;
226
227
228
229 -- This procedure is to handle new content item
230 -- and put it into IBC tables
231 PROCEDURE process_content_item(
232 px_masterdoc_qry_rec IN OUT NOCOPY MASTERDOC_QRY_REC_TYPE,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2)
236 IS
237 l_i NUMBER;
238 l_j NUMBER;
239 l_return_status VARCHAR2(1);
240 l_msg_count NUMBER;
241 l_msg_data VARCHAR2(2000);
242
243 l_status VARCHAR2(30) ;
244 l_cv_label_rec Ibc_Cv_Label_Grp.CV_Label_Rec_Type;
245 x_cv_label_rec Ibc_Cv_Label_Grp.CV_Label_Rec_Type;
246 l_attribute_type_codes JTF_VARCHAR2_TABLE_100;
247 l_attributes JTF_VARCHAR2_TABLE_4000;
248 l_attidx NUMBER;
249 l_directory NUMBER;
250
251
252 l_compound_content_items JTF_NUMBER_TABLE ;
253 l_compound_attribute_types JTF_VARCHAR2_TABLE_100;
254
255
256 l_object_version_number NUMBER := 0;
257
258 l_content_item_id NUMBER;
259 l_citem_version_id NUMBER;
260 l_lgl_phys_map_id NUMBER;
261 l_version_number NUMBER;
262 l_move_label NUMBER;
263 l_old_version NUMBER;
264 l_old_ver_num NUMBER;
265 l_old_item NUMBER;
266 l_query NUMBER := NULL;
267
268 CURSOR c_get_content_item_csr(c_item_id VARCHAR2,c_content_type_code VARCHAR2) IS
269 SELECT content_item_id, citem_version_id
270 FROM jtf_fm_mig_history_details
271 WHERE item_id = c_item_id
272 and content_type_code = c_content_type_code;
273
274
275 BEGIN
276 SAVEPOINT process_content_item;
277 printDebugLog('Start processing item');
278 l_move_label := 0;
279 OPEN c_get_content_item_csr(px_masterdoc_qry_rec.item_id,px_masterdoc_qry_rec.content_type_code);
280 FETCH c_get_content_item_csr INTO l_content_item_id, l_citem_version_id;
281 IF (c_get_content_item_csr%NOTFOUND) THEN
282 l_citem_version_id := NULL;
283 ELSE
284 printDebugLog('Item is already present in OCM ' ||px_masterdoc_qry_rec.item_id);
285 l_old_version := l_citem_version_id;
286 --l_citem_version_id := NULL;
287 --l_move_label := 1;
288 END IF;
289 CLOSE c_get_content_item_csr;
290
291 printDebugLog(' After finding content item id and citem version id');
292 printDebugLog(' content item id:'||l_content_item_id
293 ||' Citem version id:'||l_citem_version_id);
294 l_attribute_type_codes := JTF_VARCHAR2_TABLE_100();
295 l_attributes := JTF_VARCHAR2_TABLE_4000();
296
297 IF px_masterdoc_qry_rec.content_type_code = 'AMF_TEMPLATE'
298 THEN
299 l_query := NULL;
300 l_directory := 4;
301 printDebugLog('Assinged l_query NULL value');
302 l_attribute_type_codes.extend(6);
303 l_attributes.extend(6);
304 l_attribute_type_codes(1) := 'APPLICATION_ID';
305 l_attributes(1) := '690';
306 l_attribute_type_codes(2) := 'DEFAULT_MODE';
307 l_attributes(2) := 'HTML';
308 l_attribute_type_codes(3) := 'HTML_DATA_FND_ID';
309 l_attributes(3) := px_masterdoc_qry_rec.FILE_ID;
310 l_attribute_type_codes(4) := 'TEXT_DATA_FND_ID';
311 l_attributes(4) := g_text_file_id;
312 l_attribute_type_codes(5) := 'OWNER';
313 l_attributes(5) := FND_GLOBAL.USER_ID;
314 printDebugLog('Query Id is ' || px_masterdoc_qry_rec.QUERY_CITEM_ID);
315 l_attribute_type_codes(6) := 'QUERY_ID';
316 l_attributes(6) := px_masterdoc_qry_rec.QUERY_CITEM_ID;
317 l_status := IBC_UTILITIES_PUB.G_STV_WORK_IN_PROGRESS;
318 IF (px_masterdoc_qry_rec.QUERY_CITEM_ID is NOT NULL) THEN
319 printDebugLog('Query Id is not NULL, so component created');
320 l_compound_content_items := JTF_NUMBER_TABLE(px_masterdoc_qry_rec.QUERY_CITEM_ID) ;
321 l_compound_attribute_types := JTF_VARCHAR2_TABLE_100('AMF_QUERY');
322
323 END IF;
324
325 ELSIF px_masterdoc_qry_rec.content_type_code = 'AMF_QUERY'
326 THEN
327 l_query := px_masterdoc_qry_rec.FILE_ID;
328 l_directory := 11;
329 printDebugLog('Assinged l_query ' || l_query);
330 l_attribute_type_codes.extend(1);
331 l_attributes.extend(1);
332 l_attribute_type_codes(1) := 'IS_DATA_QUERY';
333 l_attributes(1) := 'F';
334 l_status := IBC_UTILITIES_PUB.G_STV_APPROVED;
335 ELSE
336 null;
337 END IF;
338
339 IF (l_content_item_id IS NULL) THEN
340
341 printDebugLog(' Start upserting content item');
342 IBC_CITEM_ADMIN_GRP.upsert_item(
343 p_ctype_code => px_masterdoc_qry_rec.content_type_code,
344 p_citem_name => px_masterdoc_qry_rec.FILE_NAME,
345 p_citem_description => px_masterdoc_qry_rec.DESCRIPTION,
346 p_dir_node_id => l_directory, -- TEMPLATE -4 , Query -11 directory node
347 p_reference_code => NULL,
348 p_trans_required => FND_API.G_FALSE,
349 p_wd_restricted => FND_API.G_FALSE,
350 p_start_date => NULL,
351 p_end_date => NULL,
352 p_attribute_type_codes => l_attribute_type_codes,
353 p_attributes => l_attributes,
354 p_attach_file_id => l_query,
355 p_component_citems => l_compound_content_items,
356 p_component_atypes => l_compound_attribute_types,
357 p_status => l_status,
358 p_language => USERENV('LANG'),
359 p_commit => FND_API.G_FALSE,
360 px_content_item_id => px_masterdoc_qry_rec.content_item_id,
361 px_citem_ver_id => px_masterdoc_qry_rec.citem_version_id,
362 px_object_version_number => l_object_version_number,
363 x_return_status => l_return_status,
364 x_msg_count => l_msg_count,
365 x_msg_data => l_msg_data);
366 IF l_return_status <> FND_API.g_ret_sts_success THEN
367 printDebugLog(' Error in base content item creation:'||l_msg_data);
368 RAISE FND_API.g_exc_error;
369 END IF;
370
371 printDebugLog(' After Upsert content Item ID and CITEM_VERID is: ' ||px_masterdoc_qry_rec.content_item_id || ':' ||px_masterdoc_qry_rec.citem_version_id);
372
373 END IF;
374 x_return_status := FND_API.g_ret_sts_success;
375 EXCEPTION
376 WHEN OTHERS THEN
377 ROLLBACK TO process_content_item;
378 x_return_status := FND_API.g_ret_sts_error;
379 END process_content_item;
380
381 PROCEDURE process_content_items(
382 px_masterdoc_qry_tbl IN OUT NOCOPY MASTERDOC_QRY_TBL_TYPE,
383 x_return_status OUT NOCOPY VARCHAR2,
384 x_msg_count OUT NOCOPY NUMBER,
385 x_msg_data OUT NOCOPY VARCHAR2)
386 IS
387 l_i NUMBER;
388 l_j NUMBER;
389 l_duplicate VARCHAR2(1);
390 l_return_status VARCHAR2(1);
391 l_msg_count NUMBER;
392 l_msg_data VARCHAR2(2000);
393
394 BEGIN
395 SAVEPOINT process_content_items;
396 x_return_status := FND_API.g_ret_sts_success;
397 printDebugLog(' process_content_items begin');
398 IF px_masterdoc_qry_tbl.count > 0 THEN
399 printDebugLog(' content item number='||px_masterdoc_qry_tbl.count);
400 FOR l_i IN 1..px_masterdoc_qry_tbl.count LOOP
401 printDebugLog(' Content item '||l_i
402 ||' item code:'||px_masterdoc_qry_tbl(l_i).content_type_code
403 ||' file id:'||px_masterdoc_qry_tbl(l_i).file_id
404 ||' file name:'||px_masterdoc_qry_tbl(l_i).file_name);
405
406
407 l_return_status := FND_API.g_ret_sts_success;
408
409 process_content_item(
410 px_masterdoc_qry_rec => px_masterdoc_qry_tbl(l_i),
411 x_return_status => l_return_status,
412 x_msg_count => l_msg_count,
413 x_msg_data => l_msg_data);
414 printDebugLog(' process_content_item return:'||l_return_status);
415
416 IF (l_return_status = FND_API.g_ret_sts_success)
417 THEN
418
419 g_idx_created := g_idx_created + 1;
420
421 MIGRATED_CONTENT(g_idx_created).content_type_code
422 := px_masterdoc_qry_tbl(l_i).CONTENT_TYPE_CODE;
423 MIGRATED_CONTENT(g_idx_created).FILE_ID
424 := px_masterdoc_qry_tbl(l_i).FILE_ID;
425 MIGRATED_CONTENT(g_idx_created).file_name
426 := px_masterdoc_qry_tbl(l_i).FILE_NAME;
427 MIGRATED_CONTENT(g_idx_created).ITEM_ID
428 := px_masterdoc_qry_tbl(l_i).ITEM_ID;
429 MIGRATED_CONTENT(g_idx_created).CONTENT_ITEM_ID
430 := px_masterdoc_qry_tbl(l_i).CONTENT_ITEM_ID ;
431 printDebugLog(' Content ID in process content items is :' || px_masterdoc_qry_tbl(l_i).CONTENT_ITEM_ID );
432 MIGRATED_CONTENT(g_idx_created).CITEM_VERSION_ID
433 := px_masterdoc_qry_tbl(l_i).CITEM_VERSION_ID;
434 printDebugLog(' Content VERSION ID in process content items is :' || px_masterdoc_qry_tbl(l_i).CITEM_VERSION_ID );
435 MIGRATED_CONTENT(g_idx_created).DESCRIPTION
436 := px_masterdoc_qry_tbl(l_i).DESCRIPTION;
437 MIGRATED_CONTENT(g_idx_created).QUERY_CITEM_ID
438 := px_masterdoc_qry_tbl(l_i).QUERY_CITEM_ID;
439 MIGRATED_CONTENT(g_idx_created).MIGRATION_CODE
440 := px_masterdoc_qry_tbl(l_i).MIGRATION_CODE;
441
442 END IF; -- end of statistics
443
444 END LOOP;
445 COMMIT;
446 END IF;
447 printDebuglog(' process_content_items end');
448 EXCEPTION
449 WHEN OTHERS THEN
450 ROLLBACK TO process_content_items;
451 x_return_status := FND_API.g_ret_sts_error;
452 END process_content_items;
453
454
455
456
457
458
459
460 PROCEDURE query_mig(x_status OUT NOCOPY VARCHAR2)
461 IS
462
463 CURSOR c_get_query_details_csr IS
464 SELECT QUERY_ID,QUERY_NAME, QUERY_DESC, FILE_ID from JTF_FM_QUERIES_ALL ;
465
466 l_query_id NUMBER;
467 l_query_name VARCHAR2(50);
468 l_query_desc VARCHAR2(255);
469 l_query_file_id NUMBER;
470 l_query_content_item_id NUMBER;
471
472 l_index NUMBER := 0;
473 l_return_status VARCHAR2(1);
474 l_msg_count NUMBER;
475 l_msg_data VARCHAR2(2000);
476
477
478
479
480 BEGIN
481
482 -- Check to see if any Query was attached to this doc
483 -- If query is present, first Migrate the query into OCM
484 -- Get the content_item_id in OCM for that Query
485 -- Add that as an attribute of the template
486 OPEN c_get_query_details_csr();
487 LOOP
488
489 FETCH c_get_query_details_csr INTO l_query_id,l_query_name,l_query_desc,l_query_file_id;
490 EXIT WHEN c_get_query_details_csr%NOTFOUND ;
491
492 l_index := l_index + 1;
493 l_query_tbl(l_index).MIGRATION_CODE := 'JTF_FM_QUERY_MIG';
494 l_query_tbl(l_index).item_id := l_query_id;
495 l_query_tbl(l_index).CONTENT_TYPE_CODE := 'AMF_QUERY';
496 l_query_tbl(l_index).file_id := l_query_file_id;
497 l_query_tbl(l_index).file_name := l_query_name;
498 l_query_tbl(l_index).DESCRIPTION := l_query_desc;
499 l_query_tbl(l_index).CONTENT_ITEM_ID := null;
500 l_query_tbl(l_index).CITEM_VERSION_ID := null;
501
502 END LOOP;
503 CLOSE c_get_query_details_csr;
504
505
506 process_content_items(
507 px_masterdoc_qry_tbl => l_query_tbl,
508 x_return_status => l_return_status,
509 x_msg_count => l_msg_count,
510 x_msg_data => l_msg_data);
511 printDebuglog(' process_content_items return:'||l_return_status);
512 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
513 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
514 END IF;
515 EXCEPTION
516
517 WHEN OTHERS THEN
518 FND_MSG_PUB.Count_And_Get (
519 p_encoded => FND_API.g_false,
520 p_count => l_msg_count,
521 p_data => l_msg_data
522 );
523 printDebugLog('x_message: '||l_msg_data);
524
525 END query_mig;
526
527 PROCEDURE CREATE_EMPTY_TEXT_BLOB(l_file_id OUT NOCOPY NUMBER)
528 IS
529 l_set_prof BOOLEAN;
530 BEGIN
531 select fnd_lobs_s.nextval into l_file_id from dual;
532 INSERT INTO fnd_lobs(file_id,file_name,file_content_type,file_data,upload_date, file_format)
533 VALUES (l_file_id,'Dummy FM TEXT file','text/plain',empty_blob(),sysdate, 'text');
534 g_text_file_id := l_file_id;
535 printDebugLog('DUmmy TEXTFILE ID IS :' || l_file_id);
536 l_set_prof := FND_PROFILE.save('JTF_FM_TEXT_FND_ID', l_file_id, 'APPL', '690');
537
538 END CREATE_EMPTY_TEXT_BLOB;
539
540
541 PROCEDURE master_document_mig(
542 x_status OUT NOCOPY VARCHAR2)
543 IS
544
545 -- This cursor is to get all documents uploaded by fulfillment & marketing
546 CURSOR c_get_item_csr IS
547 select b.item_id
548 from jtf_amv_items_b b
549 where b.content_type_id = 20
550 and b.application_id IN(690,530)
551 ORDER BY b.item_id;
552
553 -- This cursor is to find all attachments defined
554 -- in content repository for the items in c_get_item_cur
555 CURSOR c_get_content_repository_csr(c_item_id NUMBER) IS
556 SELECT distinct file_id, file_name,description
557 FROM jtf_amv_attachments a
558 WHERE attachment_used_by_id = c_item_id
559 AND file_id IS NOT NULL;
560
561 -- Check if this document has an associated query
562 CURSOR c_get_query_csr(c_item_id NUMBER) IS
563 SELECT QUERY_ID from JTF_FM_QUERY_MES where MES_DOC_ID = c_item_id;
564
565 -- Get the CITEM_ID for the associated QUERY, assuming it has already been migrated to OCM
566 CURSOR c_get_query_details_csr(c_query_id NUMBER) IS
567 SELECT CONTENT_ITEM_ID from JTF_FM_MIG_HISTORY_DETAILS where item_id = c_query_id;
568
569
570 l_lang VARCHAR2(4);
571 l_langdesc VARCHAR2(255);
572 l_default_langdesc VARCHAR2(255);
573 l_description VARCHAR2(2000);
574 l_i NUMBER;
575 l_j NUMBER;
576 l_k NUMBER;
577 l_index NUMBER;
578 l_file_id NUMBER;
579 l_file_name VARCHAR2(240);
580
581 l_return_status VARCHAR2(1);
582 l_msg_count NUMBER;
583 l_msg_data VARCHAR2(2000);
584
585 l_item_id NUMBER;
586 l_default_language VARCHAR2(4);
587
588 l_query_id NUMBER;
589 l_query_name VARCHAR2(50);
590 l_query_desc VARCHAR2(255);
591 l_query_file_id NUMBER;
592 l_query_content_item_id NUMBER;
593 l_seperator NUMBER;
594 l_ext VARCHAR2(240);
595
596
597 BEGIN
598 l_i := 0;
599 l_k := 0;
600 l_index := 0;
601
602 OPEN c_get_item_csr;
603 LOOP
604 FETCH c_get_item_csr INTO l_item_id;
605 EXIT WHEN c_get_item_csr%NOTFOUND;
606
607 printDebuglog('Item id:'||l_item_id);
608
609 -- Check to see if any Query was attached to this doc
610 -- If query is present, first Migrate the query into OCM
611 -- Get the content_item_id in OCM for that Query
612 -- Add that as an attribute of the template
613 OPEN c_get_query_csr(l_item_id);
614 FETCH c_get_query_csr INTO l_query_id;
615 printDebugLog('Query Id is:' || l_query_id);
616 IF (c_get_query_csr%NOTFOUND)
617 THEN
618 l_query_id := NULL;
619 ELSE
620 OPEN c_get_query_details_csr(l_query_id);
621 FETCH c_get_query_details_csr INTO l_query_content_item_id;
622 EXIT WHEN c_get_query_details_csr%NOTFOUND;
623 printDebugLog('citemid of Query is :' ||l_query_content_item_id);
624 CLOSE c_get_query_details_csr;
625 END IF;
626 CLOSE c_get_query_csr;
627
628
629 -- Find all minisites this media object linked to
630 OPEN c_get_content_repository_csr(l_item_id);
631 FETCH c_get_content_repository_csr INTO l_file_id, l_file_name,l_description;
632 EXIT WHEN c_get_content_repository_csr%NOTFOUND;
633
634
635 printDebuglog(' l file id:'||l_file_id||' File Name:'||l_file_name);
636 l_seperator := INSTR(l_file_name, '.', -1);
637
638 IF (l_seperator <> 0) THEN
639 l_ext := UPPER(substr(l_file_name,l_seperator+1));
640 IF (l_ext IN ('HTML', 'HTM')) THEN
641
642 l_i := l_i + 1;
643 l_masterdoc_qry_tbl(l_i).item_id := l_item_id;
644 l_masterdoc_qry_tbl(l_i).CONTENT_TYPE_CODE := 'AMF_TEMPLATE';
645 l_masterdoc_qry_tbl(l_i).file_id := l_file_id;
646 l_masterdoc_qry_tbl(l_i).file_name := l_file_name;
647 l_masterdoc_qry_tbl(l_i).DESCRIPTION := l_description;
648 l_masterdoc_qry_tbl(l_i).MIGRATION_CODE:= 'JTF_FM_OCM_MIG';
649 IF l_query_id IS NOT NULL
650 THEN
651 l_masterdoc_qry_tbl(l_i).QUERY_CITEM_ID := l_query_content_item_id ;
652 printDebuglog('Query attached, citem id is :' ||l_query_content_item_id );
653
654 END IF;
655 ELSE
656 printDebuglog('File is not of type htm or html, so not migrating' || l_file_name);
657
658 END IF; -- End IF (l_ext IN ('HTML', 'HTM')) THEN
659 END IF; -- End IF (l_seperator <> 0) THEN
660
661
662 CLOSE c_get_content_repository_csr;
663
664 END LOOP;
665 CLOSE c_get_item_csr;
666 process_content_items(
667 px_masterdoc_qry_tbl => l_masterdoc_qry_tbl,
668 x_return_status => l_return_status,
669 x_msg_count => l_msg_count,
670 x_msg_data => l_msg_data);
671 printDebuglog(' process_content_items return:'||l_return_status);
672 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
673 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
674 END IF;
675 printDebuglog('Content repository migration End');
676 x_status := FND_API.g_ret_sts_success;
677 EXCEPTION
678 WHEN OTHERS THEN
679 x_status := FND_API.g_ret_sts_error;
680 END master_document_mig;
681
682 PROCEDURE ocmMigration(errbuf OUT NOCOPY VARCHAR2,
683 retcode OUT NOCOPY VARCHAR2,
684 p_debug_flag IN VARCHAR2)
685 IS
686 l_status VARCHAR2(1);
687 l_set_prof BOOLEAN;
688 l_num NUMBER :=1;
689 BEGIN
690
691 IF (check_log(p_code => 'JTF_FM_OCM_MIG',
692 p_status => 'SUCCESS') = 0) THEN
693
694 create_log(p_code => 'JTF_FM_OCM_MIG',
695 p_status => 'START',
696 x_status => l_status);
697
698 IF p_debug_flag = 'Y' THEN
699 JTF_FM_MD_MIGRATION_PVT.g_debug := p_debug_flag;
700 END IF;
701
702 --g_start_time := SYSDATE;
703
704
705 IF (check_log(p_code => 'JTF_FM_QUERY_MIG',p_status => 'SUCCESS') = 0)
706 THEN
707
708 create_log(p_code => 'JTF_FM_QUERY_MIG',
709 p_status => 'START',
710 x_status => l_status);
711
712
713 query_mig(
714 x_status => l_status);
715 IF (l_status <> FND_API.G_RET_STS_SUCCESS) THEN
716 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
717 END IF;
718
719 update_log(p_code => 'JTF_FM_QUERY_MIG',
720 p_old_status => 'START',
721 p_new_status => 'SUCCESS',
722 p_number => 1,
723 x_status => l_status);
724
725 l_num := g_idx_created + 1;
726 ELSE
727 printDEBUGLOG('Queries have already been Migrated, so proceeding with content');
728 END IF;
729
730 g_text_file_id := FND_PROFILE.VALUE('JTF_FM_TEXT_FND_ID');
731
732 IF g_text_file_id IS NULL THEN
733 CREATE_EMPTY_TEXT_BLOB(g_text_file_id);
734 END IF;
735
736 -- Migrate the attachments to OCM content item
737 -- based on the logical items
738 master_document_mig(
739 x_status => l_status);
740 IF (l_status <> FND_API.G_RET_STS_SUCCESS) THEN
741 raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
742 END IF;
743
744 --g_end_time := SYSDATE;
745 --printReport;
746
747 update_log(p_code => 'JTF_FM_OCM_MIG',
748 p_old_status => 'START',
749 p_new_status => 'SUCCESS',
750 p_number =>l_num,
751 x_status => l_status);
752
753 END IF;
754
755 retcode := 0;
756 errbuf := 'SUCCESS';
757 COMMIT;
758 EXCEPTION
759 WHEN OTHERS THEN
760 printOutput(SQLCODE||'-'||SQLERRM);
761 printDebuglog(SQLCODE||'-'||SQLERRM);
762 retcode := -1;
763 errbuf := SQLCODE||'-'||SQLERRM;
764 END ocmMigration;
765
766 END JTF_FM_MD_MIGRATION_PVT;