[Home] [Help]
PACKAGE BODY: APPS.OKC_ARTICLES_MIGRATE_GRP
Source
1 PACKAGE BODY OKC_ARTICLES_MIGRATE_GRP AS
2 /* $Header: OKCGARTMIGB.pls 120.1 2005/12/06 14:18:26 rvohra noship $ */
3
4
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 ---------------------------------------------------------------------------
8 -- GLOBAL MESSAGE CONSTANTS
9 ---------------------------------------------------------------------------
10 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
11 ---------------------------------------------------------------------------
12 -- GLOBAL VARIABLES
13 ---------------------------------------------------------------------------
14 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_ARTICLES_MIGRATE_GRP';
15 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
16
17 ------------------------------------------------------------------------------
18 -- GLOBAL CONSTANTS
19 ------------------------------------------------------------------------------
20 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
21 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
22 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
23 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
24 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
25
26 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
27 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
28 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
29
30 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
31 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
32 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
33 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
34 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
35 G_EXC_PREREQ_SETUP_ERROR EXCEPTION;
36 G_GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
37 G_CURRENT_ORG_ID NUMBER := -99;
38 G_FETCHSIZE_LIMIT NUMBER := 300;
39 G_program_id OKC_ARTICLES_ALL.PROGRAM_ID%TYPE;
40 G_program_login_id OKC_ARTICLES_ALL.PROGRAM_LOGIN_ID%TYPE;
41 G_program_appl_id OKC_ARTICLES_ALL.PROGRAM_APPLICATION_ID%TYPE;
42 G_request_id OKC_ARTICLES_ALL.REQUEST_ID%TYPE;
43 G_context VARCHAR2(50) := NULL;
44 G_user_id NUMBER;
45 G_login_id NUMBER;
46
47
48 /*===================================================
49 | PROCEDURE get_print_msgs_stack
50 | This API will read the Fnd Message stack and print it in Concurrent Log
51 | This API will be called whenever an error is reported.
52 +==================================================*/
53
54 PROCEDURE get_print_msgs_stack IS
55 l_msg_data VARCHAR2(2000);
56 l_count NUMBER;
57 BEGIN
58 FND_MSG_PUB.Count_And_Get( p_count => l_count, p_encoded=> 'F', p_data => l_msg_data );
59
60 IF l_count > 1 Then
61 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
62 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
63 END LOOP;
64 ELSE
65 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
66 END IF;
67
68 --recycle it
69 FND_MSG_PUB.initialize;
70
71 END get_print_msgs_stack;
72
73 /*===================================================
74 | PROCEDURE conc_migrate_articles
75 | conc. program wrapper for migrate_articles
76 | This will internally call the main API.
77 | Parameters passed are
78 | 1. p_fetchsize is fetch and/or commit size for BULK operations
79 +==================================================*/
80 PROCEDURE conc_migrate_articles (errbuf OUT NOCOPY VARCHAR2,
81 retcode OUT NOCOPY VARCHAR2,
82 p_fetchsize IN NUMBER
83 ) IS
84 l_api_name CONSTANT VARCHAR2(30) := 'conc_migrate_articles';
85 l_api_version CONSTANT VARCHAR2(30) := 1.0;
86 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
87 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
88 l_msg_count NUMBER;
89 l_msg_data VARCHAR2(2000);
90 l_fetchsize NUMBER := p_fetchsize;
91 --
92 l_proc varchar2(72) := G_PKG_NAME||'conc_migrate_articles';
93 --
94
95 BEGIN
96
97 IF (l_debug = 'Y') THEN
98 okc_debug.Set_Indentation(l_proc);
99 okc_debug.Log('10: Entering ',2);
100 END IF;
101
102 --Initialize the return code
103 retcode := 0;
104
105 --Validate the parameters
106 IF p_fetchsize is NULL THEN
107 l_fetchsize := 100;
108
109 ELSIF p_fetchsize > G_FETCHSIZE_LIMIT THEN
110 x_return_status := G_RET_STS_ERROR;
111 l_return_status := G_RET_STS_ERROR;
112
113 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
114 p_msg_name => 'OKC_ART_IMP_LIM_FETCHSIZE');
115 RAISE FND_API.G_EXC_ERROR ;
116 END IF;
117
118 -- Call the Article Migration API
119 OKC_ARTICLES_MIGRATE_GRP.migrate_articles(
120 x_return_status => l_return_status,
121 x_msg_count => l_msg_count,
122 x_msg_data => l_msg_data,
123 p_fetchsize => l_fetchsize );
124
125
126 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
127 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
128 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
129 RAISE OKC_API.G_EXCEPTION_ERROR;
130 END IF;
131
132 IF (l_debug = 'Y') THEN
133 okc_debug.Log('1000: Leaving ',2);
134 okc_debug.Reset_Indentation;
135 END IF;
136 COMMIT;
137
138 EXCEPTION
139 WHEN OKC_API.G_EXCEPTION_ERROR THEN
140 retcode := 2;
141 errbuf := substr(sqlerrm,1,200);
142 IF FND_MSG_PUB.Count_Msg > 0 Then
143 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
144 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
145 END LOOP;
146 END IF;
147 FND_MSG_PUB.initialize;
148
149 IF (l_debug = 'Y') THEN
150 okc_debug.Log('3000: Leaving ',2);
151 okc_debug.Reset_Indentation;
152 END IF;
153 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
154 retcode := 2;
155 errbuf := substr(sqlerrm,1,200);
156 IF FND_MSG_PUB.Count_Msg > 0 Then
157 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
158 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
159 END LOOP;
160 END IF;
161 FND_MSG_PUB.initialize;
162 IF (l_debug = 'Y') THEN
163 okc_debug.Log('4000: Leaving ',2);
164 okc_debug.Reset_Indentation;
165 END IF;
166 WHEN OTHERS THEN
167 retcode := 2;
168 errbuf := substr(sqlerrm,1,200);
169
170 IF FND_MSG_PUB.Count_Msg > 0 Then
171 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
172 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
173 END LOOP;
174 END IF;
175 FND_MSG_PUB.initialize;
176 IF (l_debug = 'Y') THEN
177 okc_debug.Log('5000: Leaving ',2);
178 okc_debug.Reset_Indentation;
179 END IF;
180 END conc_migrate_articles;
181
182 /*===================================================
183 | FUNCTION Get_Seq_Id
184 | Get Sequence Number for New Article or New Version
185 +==================================================*/
186
187 FUNCTION Get_Seq_Id (
188 p_object_type IN VARCHAR2,
189 x_object_id OUT NOCOPY NUMBER
190 ) RETURN VARCHAR2 IS
191 CURSOR l_art_csr IS
192 SELECT OKC_ARTICLES_ALL_S1.NEXTVAL FROM DUAL;
193 CURSOR l_ver_csr IS
194 SELECT OKC_ARTICLE_VERSIONS_S1.NEXTVAL FROM DUAL;
195 BEGIN
196 IF (l_debug = 'Y') THEN
197 okc_debug.Log('100: Entered get_seq_id', 2);
198 END IF;
199
200 IF p_object_type = 'ART' THEN
201 OPEN l_art_csr;
202 FETCH l_art_csr INTO x_object_id ;
203 IF l_art_csr%NOTFOUND THEN
204 RAISE NO_DATA_FOUND;
205 END IF;
206 CLOSE l_art_csr;
207 ELSIF p_object_type = 'VER' THEN
208 OPEN l_ver_csr;
209 FETCH l_ver_csr INTO x_object_id ;
210 IF l_ver_csr%NOTFOUND THEN
211 RAISE NO_DATA_FOUND;
212 END IF;
213 CLOSE l_ver_csr;
214 END IF;
215
216 IF (l_debug = 'Y') THEN
217 okc_debug.Log('200: Leaving get_seq_id', 2);
218 END IF;
219 RETURN G_RET_STS_SUCCESS;
220 EXCEPTION
221 WHEN OTHERS THEN
222
223 IF (l_debug = 'Y') THEN
224 okc_debug.Log('300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm,2);
225 END IF;
226
227 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
228 p_msg_name => G_UNEXPECTED_ERROR,
229 p_token1 => G_SQLCODE_TOKEN,
230 p_token1_value => sqlcode,
231 p_token2 => G_SQLERRM_TOKEN,
232 p_token2_value => sqlerrm);
233
234 IF l_art_csr%ISOPEN THEN
235 CLOSE l_art_csr;
236 END IF;
237 IF l_ver_csr%ISOPEN THEN
238 CLOSE l_ver_csr;
239 END IF;
240
241 RETURN G_RET_STS_UNEXP_ERROR ;
242
243 END Get_Seq_Id;
244 /*===================================================
245 | PROCEDURE process_current_org_only
246 | This API will be called to run the migration of articles to a particular
247 | local org. This will be used for those articles that were skipped in the
248 | migration run as they may not have been set up properly.
249 | This assumes that the main migration is run to the global org.
250 ==================================================*/
251 PROCEDURE process_current_org_only(
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2
255 ) IS
256
257 -- Cursor to fetch current Org info
258
259 CURSOR l_current_org_info_csr IS
260 SELECT INF.ORGANIZATION_ID, UNIT.NAME
261 FROM HR_ORGANIZATION_INFORMATION INF, HR_ALL_ORGANIZATION_UNITS UNIT
262 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
263 AND INF.ORGANIZATION_ID = UNIT.ORGANIZATION_ID
264 AND INF.ORGANIZATION_ID = G_CURRENT_ORG_ID;
265
266 l_org_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE;
267 l_org_name HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
268 l_row_notfound BOOLEAN:=FALSE;
269
270 BEGIN
271
272 SAVEPOINT bulkdml;
273 -- Create Auto Adoption rows.
274 IF (l_debug = 'Y') THEN
275 okc_debug.Log('100: Entered process current org only', 2);
276 END IF;
277
278 OPEN l_current_org_info_csr;
279 FETCH l_current_org_info_csr INTO l_org_id, l_org_name;
280 l_row_notfound := l_current_org_info_csr%NOTFOUND;
281 CLOSE l_current_org_info_csr;
282
283 IF l_row_notfound THEN
284 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
285 p_msg_name => 'OKC_ART_INCMPLT_CUR_ORG_DEF');
286 RAISE FND_API.G_EXC_ERROR ;
287 END IF;
288
289
290 INSERT INTO OKC_ARTICLE_ADOPTIONS
291 (
292 GLOBAL_ARTICLE_VERSION_ID,
293 ADOPTION_TYPE,
294 LOCAL_ORG_ID,
295 ADOPTION_STATUS,
296 LOCAL_ARTICLE_VERSION_ID,
297 OBJECT_VERSION_NUMBER,
298 CREATED_BY,
299 CREATION_DATE,
300 LAST_UPDATED_BY,
301 LAST_UPDATE_LOGIN,
302 LAST_UPDATE_DATE
303 )
304 SELECT
305 VER.ARTICLE_VERSION_ID,
306 'ADOPTED',
307 G_CURRENT_ORG_ID,
308 'APPROVED',
309 NULL,
310 1.0,
311 G_User_Id,
312 sysdate,
313 G_User_Id,
314 G_Login_Id,
315 sysdate
316 FROM OKC_ARTICLE_VERSIONS VER, OKC_ARTICLES_ALL ART
317 WHERE ART.ORIG_SYSTEM_REFERENCE_CODE = 'OKCMIGORIG'
318 AND VER.ORIG_SYSTEM_REFERENCE_CODE = ART.ORIG_SYSTEM_REFERENCE_CODE
319 AND ART.ARTICLE_ID = VER.ARTICLE_ID
320 AND ART.ORG_ID = G_GLOBAL_ORG_ID
321 AND NOT EXISTS
322 (SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP
323 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
324 AND ADP.LOCAL_ORG_ID = G_CURRENT_ORG_ID);
325
326 -- Create Relationships for the org. as a copy of the global relationships
327
328 INSERT INTO OKC_ARTICLE_RELATNS_ALL(
329 SOURCE_ARTICLE_ID,
330 TARGET_ARTICLE_ID,
331 ORG_ID,
332 RELATIONSHIP_TYPE,
333 OBJECT_VERSION_NUMBER,
334 CREATED_BY,
335 CREATION_DATE,
336 LAST_UPDATED_BY,
337 LAST_UPDATE_LOGIN,
338 LAST_UPDATE_DATE)
339 SELECT
340 REL.SOURCE_ARTICLE_ID,
341 REL.TARGET_ARTICLE_ID,
342 G_CURRENT_ORG_ID,
343 'INCOMPATIBLE',
344 1.0,
345 G_User_Id,
346 sysdate,
347 G_User_Id,
348 G_Login_Id,
349 sysdate
350 FROM OKC_ARTICLE_RELATNS_ALL REL
351 WHERE ORG_ID = G_GLOBAL_ORG_ID
352 AND EXISTS
353 (SELECT 1 FROM OKC_ARTICLES_ALL SRC, OKC_ARTICLES_ALL TAR
354 WHERE SRC.orig_system_reference_code like 'OKCMIG%'
355 AND SRC.article_id = REL.source_article_id
356 AND SRC.org_id = G_GLOBAL_ORG_ID
357 AND TAR.org_id = G_GLOBAL_ORG_ID
358 AND TAR.article_id = REL.target_article_id
359 AND TAR.orig_system_reference_code = SRC.orig_system_reference_code)
360 AND NOT EXISTS
361 (SELECT 1 FROM OKC_ARTICLE_RELATNS_ALL REL1
362 WHERE REL1.source_article_id = REL.source_article_id AND
363 REL1.target_article_id = REL.target_article_id AND
364 REL1.org_id = G_CURRENT_ORG_ID);
365
366 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
367 p_msg_name => 'OKC_ART_MIG_OUTPUT');
368 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MSG_PUB.Get(1,p_encoded =>FND_API.G_FALSE ));
369 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_org_name);
370
371 x_return_status := G_RET_STS_SUCCESS;
372
373 EXCEPTION
374 WHEN FND_API.G_EXC_ERROR THEN
375 IF (l_debug = 'Y') THEN
376 okc_debug.Log('300: Error occurred in process_current_org: OKC_API.G_EXCEPTION_ERROR Exception', 2);
377 END IF;
378 get_print_msgs_stack;
379 x_return_status := G_RET_STS_ERROR ;
380
381 WHEN OTHERS THEN
382 IF (l_debug = 'Y') THEN
383 okc_debug.Log('500: Leaving current org processing because of EXCEPTION: '||sqlerrm, 2);
384 END IF;
385 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
386 p_msg_name => G_UNEXPECTED_ERROR,
387 p_token1 => G_SQLCODE_TOKEN,
388 p_token1_value => sqlcode,
389 p_token2 => G_SQLERRM_TOKEN,
390 p_token2_value => sqlerrm);
391
392 x_return_status := G_RET_STS_UNEXP_ERROR;
393
394 ROLLBACK TO SAVEPOINT bulkdml;
395 END;
396
397 /*===================================================
398 | PROCEDURE migrate articles
399 | The users can specify if this is to be run only for an org.
400 | The default behavior is to run for all orgs that are setup
401 | through HR Org EITs.
402 | If the user specifies that this is for the current org and that
403 | belongs to the Global Org, this will be run as a regular migration
404 | The users will need to specify a batch size or commit size.
405 |
406 | Parameters passed are
407 | 1. p_fetchsize is fetch and/or commit size for BULK operations
408 +==================================================*/
409 PROCEDURE migrate_articles(
410 x_return_status OUT NOCOPY VARCHAR2,
411 x_msg_count OUT NOCOPY NUMBER,
412 x_msg_data OUT NOCOPY VARCHAR2,
413 p_fetchsize IN NUMBER := 100
414 ) IS
415 l_api_version CONSTANT NUMBER := 1;
416 l_api_name CONSTANT VARCHAR2(30) := 'migrate_articles';
417 l_row_notfound BOOLEAN := FALSE;
418 l_userenv_lang VARCHAR2(10);
419
420 CURSOR get_languages IS
421 SELECT language_code
422 FROM FND_LANGUAGES
423 WHERE INSTALLED_FLAG = 'B';
424
425 -- Migration will
426 -- 1. Migrate all rows for the base language as is
427 -- 2. If there is a translation (article or version), a new article
428 -- and version will be created.
429
430 CURSOR l_orig_article_csr ( cp_base_language IN VARCHAR2) IS
431 SELECT -- translated both article and versions
432 decode(ARTL.LANGUAGE,cp_base_language,ARTL.NAME,
433 ARTL.NAME||'('||artl.language||')') ART_ARTICLE_TITLE ,
434 decode(ARTL.LANGUAGE,cp_base_language,ARTL.ID,
435 -99) ART_ARTICLE_ID,
436 ARTL.ID ART_SYSTEM_REFERENCE_ID1,
437 ARTL.LANGUAGE ART_ARTICLE_LANGUAGE,
438 ARTL.LANGUAGE ARTV_ARTICLE_LANGUAGE
439 FROM OKC_STD_ARTICLES_TL ARTL
440 WHERE
441 ARTL.LANGUAGE = ARTL.SOURCE_LANG
442 AND NOT EXISTS
443 (SELECT /*+ NO_UNNEST */
444 1 FROM OKC_ARTICLES_ALL
445 WHERE ORIG_SYSTEM_REFERENCE_ID1 = TO_CHAR(ARTL.ID)
446 AND ORIG_SYSTEM_REFERENCE_CODE in ('OKCMIGORIG', 'OKCMIGNEW')
447 AND ARTICLE_LANGUAGE = ARTL.LANGUAGE)
448 UNION ALL -- translated versions only but not articles - results in new article -- distinct is needed as there may be multiple translated versions.
449 SELECT DISTINCT
450 ARTL.NAME||'('||artvl.language||')' ART_ARTICLE_TITLE ,
451 -99 ART_ARTICLE_ID,
452 ARTL.ID ART_SYSTEM_REFERENCE_ID1,
453 ARTVL.LANGUAGE ART_ARTICLE_LANGUAGE,
454 ARTVL.LANGUAGE ARTV_ARTICLE_LANGUAGE
455 FROM OKC_STD_ART_VERSIONS_TL ARTVL,
456 OKC_STD_ARTICLES_TL ARTL
457 WHERE ARTL.LANGUAGE = ARTL.SOURCE_LANG
458 AND ARTL.LANGUAGE = cp_base_language
459 AND ARTVL.LANGUAGE = ARTVL.SOURCE_LANG
460 AND ARTVL.LANGUAGE <> cp_base_language
461 AND ARTVL.SAE_ID = ARTL.ID
462 AND NOT EXISTS
463 (SELECT /*+ NO_UNNEST */
464 1 FROM OKC_ARTICLES_ALL
465 WHERE ORIG_SYSTEM_REFERENCE_ID1 = TO_CHAR(ARTL.ID)
466 AND ORIG_SYSTEM_REFERENCE_CODE in ('OKCMIGORIG', 'OKCMIGNEW')
467 AND ARTICLE_LANGUAGE = ARTVL.LANGUAGE)
468 AND NOT EXISTS
469 (SELECT /*+ NO_UNNEST */
470 1 FROM OKC_STD_ARTICLES_TL ARTL1
471 WHERE ARTL1.LANGUAGE = ARTVL.LANGUAGE
472 AND ARTL1.SOURCE_LANG = ARTVL.SOURCE_LANG
473 AND ARTVL.SAE_ID = ARTL1.ID);
474
475 -- Version Details
476
477 CURSOR l_orig_ver_csr ( cp_language IN VARCHAR2,
478 cp_article_id IN NUMBER) IS
479 SELECT
480 ARTV.SAV_RELEASE,
481 ARTV.DATE_ACTIVE,
482 ARTVL.TEXT
483 FROM OKC_STD_ART_VERSIONS_B ARTV,
484 OKC_STD_ART_VERSIONS_TL ARTVL
485 WHERE ARTV.SAE_ID = cp_article_id
486 AND ARTVL.LANGUAGE = ARTVL.SOURCE_LANG
487 AND ARTVL.LANGUAGE = cp_language
488 AND ARTVL.SAE_ID = ARTV.SAE_ID
489 AND ARTVL.SAV_RELEASE = ARTV.SAV_RELEASE
490 ORDER BY DATE_ACTIVE;
491
492
493 -- Cursor to fetch ALL Orgs that are set up for Articles
494
495 CURSOR l_org_info_csr IS
496 SELECT INF.ORGANIZATION_ID, UNIT.NAME
497 FROM HR_ORGANIZATION_INFORMATION INF, HR_ALL_ORGANIZATION_UNITS UNIT
498 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
499 AND INF.ORGANIZATION_ID = UNIT.ORGANIZATION_ID;
500
501 -- Cursor to check setup: ALL Orgs used in contracts must be set up for Articles
502 /*
503 CURSOR l_missing_org_csr IS
504 SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS ORG
505 WHERE EXISTS
506 (
507 SELECT 1 FROM OKC_K_HEADERS_B K
508 WHERE NOT EXISTS
509 (
510 SELECT
511 '1'
512 FROM HR_ORGANIZATION_INFORMATION ORGINF
513 WHERE ORGINF.ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
514 AND ORGINF.ORGANIZATION_ID = K.AUTHORING_ORG_ID
515 )
516 AND K.AUTHORING_ORG_ID = ORG.ORGANIZATION_ID);
517 */
518 CURSOR l_missing_org_csr IS
519 SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS ORG
520 WHERE NOT EXISTS
521 (
522 SELECT /*+ NO_UNNEST */
523 '1'
524 FROM HR_ORGANIZATION_INFORMATION ORGINF
525 WHERE ORGINF.ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
526 AND ORGINF.ORGANIZATION_ID = ORG.ORGANIZATION_ID
527 )
528 AND ORG.ORGANIZATION_ID IN
529 (SELECT /*+ PARALLEL(K) */
530 AUTHORING_ORG_ID
531 FROM OKC_K_HEADERS_B K);
532
533 -- Cursor to fetch ALL org used in 11.5.9 okc
534 CURSOR l_k_org_csr IS
535 SELECT DISTINCT AUTHORING_ORG_ID
536 FROM OKC_K_HEADERS_B;
537
538 -- Cursor to check if same article title already exists in global org for 11.5.10
539 CURSOR l_unq_csr(p_article_title IN VARCHAR2) is
540 SELECT '1' FROM OKC_ARTICLES_ALL
541 WHERE article_title = p_article_title
542 AND org_id = G_GLOBAL_ORG_ID
543 AND standard_yn = 'Y'
544 AND rownum < 2;
545
546 -- All Tables and Datatypes for bulk associations
547
548 TYPE list_org_name IS TABLE OF HR_ORGANIZATION_UNITS.NAME%TYPE INDEX BY BINARY_INTEGER;
549 TYPE list_art_version_number IS TABLE OF OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_NUMBER%TYPE INDEX BY BINARY_INTEGER;
550 TYPE list_org_id IS TABLE OF HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
551 TYPE list_system_reference_code IS TABLE OF OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_CODE%TYPE INDEX BY BINARY_INTEGER;
552 TYPE list_article_title IS TABLE OF OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE INDEX BY BINARY_INTEGER ;
553 TYPE list_article_id IS TABLE OF OKC_ARTICLES_ALL.ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
554 TYPE list_article_number IS TABLE OF OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE INDEX BY BINARY_INTEGER ;
555 TYPE list_article_version_id IS TABLE OF OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER ;
556 TYPE list_article_language IS TABLE OF OKC_ARTICLES_ALL.ARTICLE_LANGUAGE%TYPE INDEX BY BINARY_INTEGER ;
557 TYPE list_art_system_reference_id1 IS TABLE OF OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_ID1%TYPE INDEX BY BINARY_INTEGER ;
558 TYPE list_start_date IS TABLE OF OKC_ARTICLE_VERSIONS.START_DATE%TYPE INDEX BY BINARY_INTEGER ;
559 TYPE list_article_text IS TABLE OF OKC_ARTICLE_VERSIONS.ARTICLE_TEXT%TYPE INDEX BY BINARY_INTEGER ;
560 TYPE list_sav_release IS TABLE OF OKC_ARTICLE_VERSIONS.SAV_RELEASE%TYPE INDEX BY BINARY_INTEGER ;
561 TYPE list_process_status IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER ;
562 TYPE list_display_name IS TABLE of OKC_ARTICLE_VERSIONS.DISPLAY_NAME%TYPE INDEX BY BINARY_INTEGER ;
563
564 l_org_id_tbl list_org_id;
565 l_org_name_tbl list_org_name;
566 article_title_tbl list_article_title ;
567 article_number_tbl list_article_number ;
568 article_id_tbl list_article_id ;
569 article_language_tbl list_article_language ;
570 ver_language_tbl list_article_language ;
571 system_reference_code_tbl list_system_reference_code ;
572 art_system_reference_id1_tbl list_art_system_reference_id1 ;
573 article_version_id_tbl list_article_version_id ;
574 start_date_tbl list_start_date ;
575 article_text_tbl list_article_text ;
576 artv_sav_release_tbl list_sav_release ;
577 t_article_id_tbl list_article_id ;
578 t_article_version_id_tbl list_article_version_id ;
579 t_start_date_tbl list_start_date ;
580 t_end_date_tbl list_start_date ;
581 t_article_text_tbl list_article_text ;
582 t_artv_sav_release_tbl list_sav_release ;
583 t_ver_language_tbl list_article_language ;
584 t_art_language_tbl list_article_language ;
585 t_system_reference_code_tbl list_system_reference_code ;
586 t_ver_system_reference_id1_tbl list_art_system_reference_id1 ;
587 t_art_version_number_tbl list_art_version_number;
588 art_process_status_tbl list_process_status ;
589 ver_process_status_tbl list_process_status ;
590 display_name_tbl list_display_name ;
591 t_display_name_tbl list_display_name ;
592
593
594 I NUMBER := 0;
595 j NUMBER := 0;
596 k NUMBER := 0;
597 l_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
598 l_return_status VARCHAR2(1);
599 l_doc_sequence_type CHAR(1);
600 l_language VARCHAR2(12);
601 l_migrated BOOLEAN :=FALSE;
602 l_dummy_var VARCHAR2(1) := '?';
603
604 ------------------------------------------------------------------------
605 -- PROCEDURE migrate_articles body starts
606 -------------------------------------------------------------------------
607
608 BEGIN
609 l_debug := 'Y';
610
611 IF (l_debug = 'Y') THEN
612 okc_debug.Log('100: Entered article_migrate', 2);
613 END IF;
614
615
616 ------------------------------------------------------------------------
617 -- Variable Initialization
618 -------------------------------------------------------------------------
619 -- Standard Start of API savepoint
620 FND_MSG_PUB.initialize;
621 -- Initialize API return status to success
622 x_return_status := FND_API.G_RET_STS_SUCCESS;
623 l_return_status := G_RET_STS_SUCCESS;
624 -- Cache user_id and login_id
625 G_user_id := Fnd_Global.user_id;
626 G_login_id := Fnd_Global.login_id;
627 l_userenv_lang := USERENV('LANG');
628
629 -- if global org is not defined then error out
630 IF G_GLOBAL_ORG_ID = '-99' Then
631 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
632 p_msg_name => 'OKC_ART_NO_GLOBAL_ORG');
633 RAISE FND_API.G_EXC_ERROR;
634 END IF;
635
636 -- Derive and cache the base language
637 OPEN get_languages;
638 FETCH get_languages INTO l_language;
639 l_row_notfound := get_languages%NOTFOUND;
640 CLOSE get_languages;
641 IF l_row_notfound THEN
642 Okc_Api.Set_Message(p_app_name => G_APP_NAME,p_msg_name => 'OKC_ART_NO_BASE_LANG');
643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
644 END IF;
645
646 -- Check for orgs in contracts that are not defined for Articles in HR_ORG
647
648 OPEN l_missing_org_csr;
649 FETCH l_missing_org_csr BULK COLLECT INTO l_org_name_tbl;
650 CLOSE l_missing_org_csr;
651 IF l_org_name_tbl.COUNT > 0 THEN
652 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
653 p_msg_name => 'OKC_ART_INCMPLT_ORG_DEF');
654 get_print_msgs_stack;
655 FOR i in l_org_name_tbl.FIRST..l_org_name_tbl.LAST LOOP
656 FND_FILE.PUT_LINE(FND_FILE.LOG,l_org_name_tbl(i));
657 -- dbms_output.put_line(l_org_name_tbl(i)||'ORG');
658 END LOOP;
659 l_org_name_tbl.DELETE;
660 RAISE FND_API.G_EXC_ERROR ;
661 END IF;
662
663 l_org_name_tbl.DELETE;
664
665 -- Fetch all orgs that are set up for articles
666 OPEN l_org_info_csr;
667 FETCH l_org_info_csr BULK COLLECT INTO l_org_id_tbl, l_org_name_tbl;
668 CLOSE l_org_info_csr;
669
670 IF l_org_id_tbl.COUNT <= 0 THEN
671 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
672 p_msg_name => 'OKC_ART_INCMPLT_ORG_DEF');
673 get_print_msgs_stack;
674 RAISE FND_API.G_EXC_ERROR ;
675 END IF;
676
677 -- Update Article Text with ' ' to be migrated to OKC_ARTICLE_VERSIONS
678 UPDATE OKC_STD_ART_VERSIONS_TL
679 SET TEXT = ' '
680 WHERE TEXT IS NULL;
681
682
683 -- Cache all CP parameters
684 IF FND_GLOBAL.CONC_PROGRAM_ID = -1 THEN
685 G_PROGRAM_ID := NULL;
686 G_PROGRAM_LOGIN_ID := NULL;
687 ELSE
688 G_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
689 G_PROGRAM_LOGIN_ID := FND_GLOBAL.CONC_LOGIN_ID;
690 END IF;
691 IF FND_GLOBAL.PROG_APPL_ID = -1 THEN
692 G_PROGRAM_APPL_ID := NULL;
693 ELSE
694 G_PROGRAM_APPL_ID := FND_GLOBAL.PROG_APPL_ID;
695 END IF;
696 IF FND_GLOBAL.CONC_REQUEST_ID = -1 THEN
697 G_REQUEST_ID := NULL;
698 ELSE
699 G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
700 END IF;
701
702 i := 0;
703 -- Migrate all folders from article sets
704 BEGIN
705 FORALL i in l_org_id_tbl.FIRST .. l_org_id_tbl.LAST
706 INSERT INTO OKC_FOLDERS_ALL_B (
707 FOLDER_ID,
708 OBJECT_VERSION_NUMBER,
709 ORG_ID,
710 SAT_CODE,
711 ATTRIBUTE_CATEGORY,
712 ATTRIBUTE1,
713 ATTRIBUTE2,
714 ATTRIBUTE3,
715 ATTRIBUTE4,
716 ATTRIBUTE5,
717 ATTRIBUTE6,
718 ATTRIBUTE7,
719 ATTRIBUTE8,
720 ATTRIBUTE9,
721 ATTRIBUTE10,
722 ATTRIBUTE11,
723 ATTRIBUTE12,
724 ATTRIBUTE13,
725 ATTRIBUTE14,
726 ATTRIBUTE15,
727 CREATION_DATE,
728 CREATED_BY,
729 LAST_UPDATE_DATE,
730 LAST_UPDATED_BY,
731 LAST_UPDATE_LOGIN
732 )
733 SELECT
734 OKC_FOLDERS_ALL_B_S1.NEXTVAL,
735 1,
736 l_org_id_tbl(i),
737 LOOKUP_CODE,
738 ATTRIBUTE_CATEGORY,
739 ATTRIBUTE1,
740 ATTRIBUTE2,
741 ATTRIBUTE3,
742 ATTRIBUTE4,
743 ATTRIBUTE5,
744 ATTRIBUTE6,
745 ATTRIBUTE7,
746 ATTRIBUTE8,
747 ATTRIBUTE9,
748 ATTRIBUTE10,
749 ATTRIBUTE11,
750 ATTRIBUTE12,
751 ATTRIBUTE13,
752 ATTRIBUTE14,
753 ATTRIBUTE15,
754 CREATION_DATE,
755 CREATED_BY,
756 LAST_UPDATE_DATE,
757 LAST_UPDATED_BY,
758 LAST_UPDATE_LOGIN
759 FROM FND_LOOKUP_VALUES LKU
760 WHERE LOOKUP_TYPE = 'OKC_ARTICLE_SET'
761 AND LANGUAGE = USERENV('LANG')
762 AND NOT EXISTS
763 (SELECT /*+ NO_UNNEST */
764 1 FROM OKC_FOLDERS_ALL_B FLD
765 WHERE FLD.SAT_CODE = LKU.LOOKUP_CODE
766 AND ORG_ID = l_org_id_tbl(i));
767 EXCEPTION
768 WHEN OTHERS THEN
769 IF (l_debug = 'Y') THEN
770 okc_debug.Log('500: Leaving Articles_Migrate because of EXCEPTION: '||sqlerrm, 2);
771 END IF;
772 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
773 p_msg_name => G_UNEXPECTED_ERROR,
774 p_token1 => G_SQLCODE_TOKEN,
775 p_token1_value => sqlcode,
776 p_token2 => G_SQLERRM_TOKEN,
777 p_token2_value => sqlerrm);
778
779 l_return_status := G_RET_STS_ERROR;
780 x_return_status := G_RET_STS_ERROR ;
781 RAISE FND_API.G_EXC_ERROR ;
782 END;
783
784 BEGIN
785 INSERT INTO OKC_FOLDERS_ALL_TL (
786 FOLDER_NAME,
787 DESCRIPTION,
788 CREATED_BY,
789 CREATION_DATE,
790 LAST_UPDATE_DATE,
791 LAST_UPDATED_BY,
792 LAST_UPDATE_LOGIN,
793 FOLDER_ID,
794 LANGUAGE,
795 SOURCE_LANG
796 ) SELECT
797 LKU.MEANING,
798 LKU.DESCRIPTION,
799 LKU.CREATED_BY,
800 LKU.CREATION_DATE,
801 LKU.LAST_UPDATE_DATE,
802 LKU.LAST_UPDATED_BY,
803 LKU.LAST_UPDATE_LOGIN,
804 FLD.FOLDER_ID,
805 LKU.LANGUAGE,
806 LKU.LANGUAGE
807 FROM FND_LOOKUP_VALUES LKU , OKC_FOLDERS_ALL_B FLD
808 WHERE LOOKUP_CODE = SAT_CODE
809 AND LOOKUP_TYPE = 'OKC_ARTICLE_SET'
810 AND NOT EXISTS
811 (SELECT /*+ NO_UNNEST */
812 1 FROM OKC_FOLDERS_ALL_TL FLDT
813 WHERE FLDT.FOLDER_ID = FLD.FOLDER_ID
814 AND FLDT.LANGUAGE = LKU.LANGUAGE) ;
815 EXCEPTION
816 WHEN OTHERS THEN
817 IF (l_debug = 'Y') THEN
818 okc_debug.Log('500: Leaving Articles_Migrate because of EXCEPTION: '||sqlerrm, 2);
819 END IF;
820 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
821 p_msg_name => G_UNEXPECTED_ERROR,
822 p_token1 => G_SQLCODE_TOKEN,
823 p_token1_value => sqlcode,
824 p_token2 => G_SQLERRM_TOKEN,
825 p_token2_value => sqlerrm);
826 l_return_status := G_RET_STS_ERROR;
827 x_return_status := G_RET_STS_ERROR;
828 RAISE FND_API.G_EXC_ERROR;
829 END;
830 ------------------------------------------------------------------------
831 -- Bulk fetch all articles rows based on the fetchsize passed by the user
832 -- the outermost loop of this procedure
833 -------------------------------------------------------------------------
834 OPEN l_orig_article_csr(l_language);
835 LOOP
836 BEGIN
837 k := 0;
838 j := 0;
839 FETCH l_orig_article_csr BULK COLLECT INTO
840 article_title_tbl ,
841 article_id_tbl ,
842 art_system_reference_id1_tbl,
843 article_language_tbl ,
844 ver_language_tbl
845 LIMIT p_fetchsize;
846
847 EXIT WHEN article_id_tbl.COUNT=0;
848 -- for each batch of articles
849
850 FOR i in article_id_tbl.FIRST ..article_id_tbl.LAST LOOP
851 BEGIN
852 G_CONTEXT := 'ART';
853 -- Initialization for each iteration
854 l_row_notfound := FALSE;
855 l_return_status := G_RET_STS_SUCCESS;
856 x_return_status := G_RET_STS_SUCCESS;
857 ver_process_status_tbl(i) := 'S';
858 art_process_status_tbl(i) := 'S';
859 system_reference_code_tbl(i) := 'OKCMIGORIG';
860 display_name_tbl(i) := '';
861 -- generate article number
862
863 OKC_ARTICLES_GRP.GET_ARTICLE_SEQ_NUMBER
864 (p_article_number => NULL,
865 p_seq_type_info_only => 'N',
866 p_org_id => G_GLOBAL_ORG_ID,
867 x_article_number => l_article_number,
868 x_doc_sequence_type => l_doc_sequence_type,
869 x_return_status => x_return_status
870 ) ;
871
872 IF x_return_status = G_RET_STS_SUCCESS Then
873 IF l_article_number is NULL Then
874 art_process_status_tbl(i) :='U';
875 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
876 p_msg_name => 'OKC_ART_MIG_NO_AUTO_NUMBER');
877 RAISE G_EXC_PREREQ_SETUP_ERROR;
878 ELSE
879 article_number_tbl(i) := l_article_number;
880 END IF;
881 ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
882 article_number_tbl(i) := NULL;
883 l_return_status := x_return_status;
884 art_process_status_tbl(i) := 'U';
885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
886 ELSE
887 article_number_tbl(i) := NULL;
888 art_process_status_tbl(i) := 'U';
889 l_return_status := x_return_status;
890 RAISE FND_API.G_EXC_ERROR;
891 END IF;
892
893 -- Check if the article title of this article already exists in 11.5.10
894 l_row_notfound := FALSE;
895 OPEN l_unq_csr(article_title_tbl(i));
896 FETCH l_unq_csr INTO l_dummy_var;
897 l_row_notfound := l_unq_csr%NOTFOUND;
898 CLOSE l_unq_csr;
899
900 IF not l_row_notfound THEN
901 article_title_tbl(i) := article_title_tbl(i)||' '||l_article_number;
902 display_name_tbl(i) := article_title_tbl(i);
903 END IF;
904
905 -- Fetch all versions for the article
906
907 OPEN l_orig_ver_csr(ver_language_tbl(i), art_system_reference_id1_tbl(i));
908 FETCH l_orig_ver_csr BULK COLLECT INTO
909 artv_sav_release_tbl,
910 start_date_tbl ,
911 article_text_tbl;
912 CLOSE l_orig_ver_csr;
913
914 IF start_date_tbl.COUNT <= 0 THEN
915 -- Article may have been translated and the version is not. Try the base language
916 -- This is not a frequent case.
917
918 OPEN l_orig_ver_csr(l_language, art_system_reference_id1_tbl(i));
919 FETCH l_orig_ver_csr BULK COLLECT INTO
920 artv_sav_release_tbl,
921 start_date_tbl ,
922 article_text_tbl;
923 CLOSE l_orig_ver_csr;
924 IF start_date_tbl.COUNT <= 0 THEN
925 art_process_status_tbl(i) := 'E';
926 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
927 p_msg_name => 'OKC_MIG_ERR_NO_VER',
928 p_token1 => 'ARTICLE_TITLE',
929 p_token1_value => article_title_tbl(i)
930 );
931 RAISE FND_API.G_EXC_ERROR ;
932 ELSE
933 ver_language_tbl(i) := l_language;
934 article_id_tbl(i) := -99; -- new article ..ids will be generated later
935 END IF;
936 END IF;
937
938 -- Generate article id only for new articles
939 IF article_id_tbl(i) = -99 Then
940 system_reference_code_tbl(i) := 'OKCMIGNEW';
941 END IF;
942 -- Generate article id for all articles as OA currently has problems handling
943 -- the old 39 digit Ids.
944
945 x_return_status := Get_Seq_Id (p_object_type => 'ART',
946 x_object_id => article_id_tbl(i));
947
948 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
949 art_process_status_tbl(i) := 'U';
950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
951 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
952 l_return_status := x_return_status;
953 art_process_status_tbl(i) := 'E';
954 RAISE FND_API.G_EXC_ERROR ;
955 END IF;
956 -- Message article versions data
957
958 FOR j in start_date_tbl.FIRST .. start_date_tbl.LAST LOOP
959 BEGIN
960 G_CONTEXT := 'AVN';
961 ver_process_status_tbl(j) := 'S';
962
963 /**
964 -- We will update article text with ' ' if they are null upfront
965 -- Basic validation: article text being NULL
966 IF article_text_tbl(j) IS NULL Then
967 art_process_status_tbl(i) := 'E';
968 ver_process_status_tbl(j) := 'E';
969 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
970 p_msg_name => 'OKC_MIG_ERR_NO_TEXT',
971 p_token1 => 'ARTICLE_NAME',
972 p_token1_value => article_title_tbl(i),
973 p_token2 => 'ARTICLE_VERSION',
974 p_token2_value => artv_sav_release_tbl(j)
975 );
976 RAISE FND_API.G_EXC_ERROR ;
977 END IF;
978 **/
979
980 -- Generate article version id only for all article versions
981
982 x_return_status := Get_Seq_Id (p_object_type => 'VER',
983 x_object_id => article_version_id_tbl(j));
984 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
985 ver_process_status_tbl(j) := 'U';
986 art_process_status_tbl(i) := 'U';
987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
988 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
989 l_return_status := x_return_status;
990 ver_process_status_tbl(j) := 'E';
991 art_process_status_tbl(i) := 'E';
992 RAISE FND_API.G_EXC_ERROR ;
993 END IF;
994
995
996 EXCEPTION
997 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
998 IF (l_debug = 'Y') THEN
999 okc_debug.Log('400: Leaving version processing loop due to OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1000 END IF;
1001 --
1002 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1003 p_msg_name => G_UNEXPECTED_ERROR,
1004 p_token1 => G_SQLCODE_TOKEN,
1005 p_token1_value => sqlcode,
1006 p_token2 => G_SQLERRM_TOKEN,
1007 p_token2_value => sqlerrm);
1008
1009 IF l_orig_article_csr%ISOPEN THEN
1010 CLOSE l_orig_article_csr;
1011 END IF;
1012 IF l_orig_ver_csr%ISOPEN THEN
1013 CLOSE l_orig_ver_csr;
1014 END IF;
1015 IF l_unq_csr%ISOPEN THEN
1016 CLOSE l_unq_csr;
1017 END IF;
1018 l_return_status := G_RET_STS_UNEXP_ERROR ;
1019 x_return_status := G_RET_STS_UNEXP_ERROR ;
1020 exit; -- exit this loop
1021
1022 WHEN FND_API.G_EXC_ERROR THEN
1023 IF (l_debug = 'Y') THEN
1024 okc_debug.Log('400: Error in this version: OKC_API.G_EXC_ERROR Exception', 2);
1025 END IF;
1026 --
1027
1028 l_return_status := G_RET_STS_ERROR ;
1029 x_return_status := G_RET_STS_ERROR ;
1030
1031 WHEN OTHERS THEN
1032 IF (l_debug = 'Y') THEN
1033 okc_debug.Log('500: Leaving version processing loop due to EXCEPTION: '||sqlerrm, 2);
1034 END IF;
1035 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1036 p_msg_name => G_UNEXPECTED_ERROR,
1037 p_token1 => G_SQLCODE_TOKEN,
1038 p_token1_value => sqlcode,
1039 p_token2 => G_SQLERRM_TOKEN,
1040 p_token2_value => sqlerrm);
1041
1042
1043 IF l_orig_article_csr%ISOPEN THEN
1044 CLOSE l_orig_article_csr;
1045 END IF;
1046 IF l_orig_ver_csr%ISOPEN THEN
1047 CLOSE l_orig_ver_csr;
1048 END IF;
1049 IF l_unq_csr%ISOPEN THEN
1050 CLOSE l_unq_csr;
1051 END IF;
1052
1053 l_return_status := G_RET_STS_UNEXP_ERROR ;
1054 x_return_status := G_RET_STS_UNEXP_ERROR ;
1055 exit;
1056 END;
1057
1058 END LOOP; -- end of FOR i in start_date_tbl.FIRST ..
1059 -------------------------------------------------------------------------
1060 -- initialize l_return_status to track status of DML execution
1061 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1062 art_process_status_tbl(i) := 'U';
1063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1064 exit;
1065 END IF;
1066
1067 -- For successful rows push to a larger collection for eventual bulk insert of versions
1068
1069 if art_process_status_tbl(i) = 'S' Then
1070 l_migrated := TRUE;
1071
1072 FOR j in start_date_tbl.FIRST..start_date_tbl.LAST LOOP
1073 t_article_id_tbl(k) := article_id_tbl(i);
1074 t_article_text_tbl(k) := article_text_tbl(j);
1075 t_art_version_number_tbl(k) := j;
1076 t_article_version_id_tbl(k) := article_version_id_tbl(j);
1077 t_start_date_tbl(k) := start_date_tbl(j);
1078 t_end_date_tbl(k) := NULL;
1079 if j > 1 Then
1080 t_end_date_tbl(k-1) := t_start_date_tbl(k)-1/86400;
1081 end if;
1082 t_art_language_tbl(k) := article_language_tbl(i);
1083 t_ver_language_tbl(k) := ver_language_tbl(i);
1084 t_system_reference_code_tbl(k) := system_reference_code_tbl(i);
1085 t_ver_system_reference_id1_tbl(k) := art_system_reference_id1_tbl(i);
1086 t_artv_sav_release_tbl(k) := artv_sav_release_tbl(j);
1087 t_display_name_tbl(k) := display_name_tbl(i);
1088 k := k+1;
1089 END LOOP;
1090 end if;
1091
1092 article_version_id_tbl.DELETE;
1093 article_text_tbl.DELETE;
1094 start_date_tbl.DELETE;
1095 artv_sav_release_tbl.DELETE;
1096 EXCEPTION
1097 WHEN G_EXC_PREREQ_SETUP_ERROR THEN
1098 IF (l_debug = 'Y') THEN
1099 okc_debug.Log('400: Leaving loop for individual article processing loop : Prereq is not properly set', 2);
1100 END IF;
1101 --
1102
1103 IF l_orig_article_csr%ISOPEN THEN
1104 CLOSE l_orig_article_csr;
1105 END IF;
1106 l_return_status := G_RET_STS_ERROR ;
1107 x_return_status := G_RET_STS_ERROR ;
1108 RAISE G_EXC_PREREQ_SETUP_ERROR;
1109 exit;-- exit this loop
1110
1111 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1112 IF (l_debug = 'Y') THEN
1113 okc_debug.Log('400: Leaving loop for individual article processing loop due to OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1114 END IF;
1115 --
1116 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1117 p_msg_name => G_UNEXPECTED_ERROR,
1118 p_token1 => G_SQLCODE_TOKEN,
1119 p_token1_value => sqlcode,
1120 p_token2 => G_SQLERRM_TOKEN,
1121 p_token2_value => sqlerrm);
1122
1123
1124 IF l_orig_article_csr%ISOPEN THEN
1125 CLOSE l_orig_article_csr;
1126 END IF;
1127 IF l_orig_ver_csr%ISOPEN THEN
1128 CLOSE l_orig_ver_csr;
1129 END IF;
1130 IF l_unq_csr%ISOPEN THEN
1131 CLOSE l_unq_csr;
1132 END IF;
1133 l_return_status := G_RET_STS_UNEXP_ERROR ;
1134 x_return_status := G_RET_STS_UNEXP_ERROR ;
1135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1136 exit; -- exit this loop
1137
1138 WHEN FND_API.G_EXC_ERROR THEN
1139 IF (l_debug = 'Y') THEN
1140 okc_debug.Log('400: Leaving Articles_Migration: OKC_API.G_EXC_ERROR Exception', 2);
1141 END IF;
1142 --
1143 l_return_status := G_RET_STS_ERROR ;
1144 x_return_status := G_RET_STS_ERROR ;
1145 -- exit; -- exit this loop
1146
1147 WHEN OTHERS THEN
1148 IF (l_debug = 'Y') THEN
1149 okc_debug.Log('500: Leaving loop for individual article processing loop due to EXCEPTION: '||sqlerrm, 2);
1150 END IF;
1151 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1152 p_msg_name => G_UNEXPECTED_ERROR,
1153 p_token1 => G_SQLCODE_TOKEN,
1154 p_token1_value => sqlcode,
1155 p_token2 => G_SQLERRM_TOKEN,
1156 p_token2_value => sqlerrm);
1157
1158
1159 IF l_orig_article_csr%ISOPEN THEN
1160 CLOSE l_orig_article_csr;
1161 END IF;
1162 IF l_orig_ver_csr%ISOPEN THEN
1163 CLOSE l_orig_ver_csr;
1164 END IF;
1165 IF l_unq_csr%ISOPEN THEN
1166 CLOSE l_unq_csr;
1167 END IF;
1168
1169 l_return_status := G_RET_STS_UNEXP_ERROR ;
1170 x_return_status := G_RET_STS_UNEXP_ERROR ;
1171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1172 exit; -- exit this loop
1173 END;
1174 END LOOP; -- end of FOR i in article_id_tbl.FIRST ..line 880 approx
1175
1176 l_return_status := G_RET_STS_SUCCESS;
1177 get_print_msgs_stack;
1178
1179 IF t_article_version_id_tbl.COUNT > 0 THEN
1180
1181 SAVEPOINT bulkdml;
1182 -- Bulk insert New Articles
1183
1184 BEGIN
1185 i := 0;
1186 G_context := 'ART';
1187 FORALL i in article_id_tbl.FIRST ..article_id_tbl.LAST
1188 INSERT INTO OKC_ARTICLES_ALL(
1189 ARTICLE_ID,
1190 ARTICLE_TITLE,
1191 ORG_ID,
1192 ARTICLE_NUMBER,
1193 STANDARD_YN,
1194 ARTICLE_INTENT,
1195 ARTICLE_LANGUAGE,
1196 ARTICLE_TYPE,
1197 ORIG_SYSTEM_REFERENCE_CODE,
1198 ORIG_SYSTEM_REFERENCE_ID1,
1199 ORIG_SYSTEM_REFERENCE_ID2,
1200 CZ_TRANSFER_STATUS_FLAG,
1201 ATTRIBUTE_CATEGORY,
1202 ATTRIBUTE1,
1203 ATTRIBUTE2,
1204 ATTRIBUTE3,
1205 ATTRIBUTE4,
1206 ATTRIBUTE5,
1207 ATTRIBUTE6,
1208 ATTRIBUTE7,
1209 ATTRIBUTE8,
1210 ATTRIBUTE9,
1211 ATTRIBUTE10,
1212 ATTRIBUTE11,
1213 ATTRIBUTE12,
1214 ATTRIBUTE13,
1215 ATTRIBUTE14,
1216 ATTRIBUTE15,
1217 PROGRAM_ID,
1218 PROGRAM_LOGIN_ID,
1219 PROGRAM_APPLICATION_ID,
1220 REQUEST_ID,
1221 OBJECT_VERSION_NUMBER,
1222 CREATED_BY,
1223 CREATION_DATE,
1224 LAST_UPDATED_BY,
1225 LAST_UPDATE_LOGIN,
1226 LAST_UPDATE_DATE)
1227 SELECT
1228 article_id_tbl(i),
1229 article_title_tbl(i),
1230 G_GLOBAL_ORG_ID,
1231 article_number_tbl(i),
1232 'Y', -- Standard YN
1233 'S', -- Article Intent
1234 article_language_tbl(i),
1235 sbt_code,
1236 system_reference_code_tbl(i), -- Orig System Reference Code
1237 id, -- Orig System Reference ID1
1238 NULL, -- Orig System Reference ID2
1239 'N', -- CZ Transfer Status Flag
1240 attribute_category,
1241 substrb(attribute1,1,150),
1242 substrb(attribute2,1,150),
1243 substrb(attribute3,1,150),
1244 substrb(attribute4,1,150),
1245 substrb(attribute5,1,150),
1246 substrb(attribute6,1,150),
1247 substrb(attribute7,1,150),
1248 substrb(attribute8,1,150),
1249 substrb(attribute9,1,150),
1250 substrb(attribute10,1,150),
1251 substrb(attribute11,1,150),
1252 substrb(attribute12,1,150),
1253 substrb(attribute13,1,150),
1254 substrb(attribute14,1,150),
1255 substrb(attribute15,1,150),
1256 G_PROGRAM_ID,
1257 G_PROGRAM_LOGIN_ID,
1258 G_PROGRAM_APPL_ID,
1259 G_REQUEST_ID,
1260 OBJECT_VERSION_NUMBER,
1261 CREATED_BY, -- Created By
1262 CREATION_DATE, -- Creation Date
1263 LAST_UPDATED_BY, -- Last Updated By
1264 LAST_UPDATE_LOGIN, -- Last Update Login
1265 sysdate -- Last Update Date
1266 FROM OKC_STD_ARTICLES_B
1267 WHERE art_process_status_tbl(i) = 'S'
1268 AND id = art_system_reference_id1_tbl(i);
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 IF (l_debug = 'Y') THEN
1272 okc_debug.Log('500: Leaving Articles_Migrate because of EXCEPTION: '||sqlerrm, 2);
1273 END IF;
1274 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1275 p_msg_name => G_UNEXPECTED_ERROR,
1276 p_token1 => G_SQLCODE_TOKEN,
1277 p_token1_value => sqlcode,
1278 p_token2 => G_SQLERRM_TOKEN,
1279 p_token2_value => sqlerrm);
1280 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1281 p_msg_name => 'OKC_ART_FETCH_FAILED',
1282 p_token1 => 'CONTEXT',
1283 p_token1_value => g_context);
1284 l_return_status := G_RET_STS_ERROR;
1285 x_return_status := G_RET_STS_ERROR;
1286 ROLLBACK TO SAVEPOINT bulkdml;
1287 RAISE FND_API.G_EXC_ERROR;
1288 END;
1289
1290 --
1291 -- End of Insert into OKC_ARTICLES_ALL
1292 --
1293 --
1294
1295
1296 BEGIN
1297 G_context := 'AVN';
1298 i := 0;
1299
1300 -- Bulk insert New Article Versions
1301
1302 FORALL i in t_article_version_id_tbl.FIRST ..t_article_version_id_tbl.LAST
1303 INSERT INTO OKC_ARTICLE_VERSIONS(
1304 ARTICLE_VERSION_ID,
1305 ARTICLE_ID,
1306 ARTICLE_VERSION_NUMBER,
1307 ARTICLE_TEXT,
1308 PROVISION_YN,
1309 INSERT_BY_REFERENCE,
1310 LOCK_TEXT,
1311 GLOBAL_YN,
1312 ARTICLE_LANGUAGE,
1313 ARTICLE_STATUS,
1314 SAV_RELEASE,
1315 START_DATE,
1316 END_DATE,
1317 STD_ARTICLE_VERSION_ID,
1318 DISPLAY_NAME,
1319 TRANSLATED_YN,
1320 ARTICLE_DESCRIPTION,
1321 DATE_APPROVED,
1322 DEFAULT_SECTION,
1323 REFERENCE_SOURCE,
1324 REFERENCE_TEXT,
1325 ORIG_SYSTEM_REFERENCE_CODE,
1326 ORIG_SYSTEM_REFERENCE_ID1,
1327 ORIG_SYSTEM_REFERENCE_ID2,
1328 ADDITIONAL_INSTRUCTIONS,
1329 VARIATION_DESCRIPTION,
1330 ADOPTION_TYPE,
1331 PROGRAM_ID,
1332 PROGRAM_LOGIN_ID,
1333 PROGRAM_APPLICATION_ID,
1334 REQUEST_ID,
1335 ATTRIBUTE_CATEGORY,
1336 ATTRIBUTE1,
1337 ATTRIBUTE2,
1338 ATTRIBUTE3,
1339 ATTRIBUTE4,
1340 ATTRIBUTE5,
1341 ATTRIBUTE6,
1342 ATTRIBUTE7,
1343 ATTRIBUTE8,
1344 ATTRIBUTE9,
1345 ATTRIBUTE10,
1346 ATTRIBUTE11,
1347 ATTRIBUTE12,
1348 ATTRIBUTE13,
1349 ATTRIBUTE14,
1350 ATTRIBUTE15,
1351 OBJECT_VERSION_NUMBER,
1352 CREATED_BY,
1353 CREATION_DATE,
1354 LAST_UPDATED_BY,
1355 LAST_UPDATE_LOGIN,
1356 LAST_UPDATE_DATE)
1357 SELECT
1358 t_article_version_id_tbl(i),
1359 t_article_id_tbl(i),
1360 t_art_version_number_tbl(i), -- Article Version Number
1361 text, -- Article Text
1362 'N', -- Provision Yn
1363 'N', -- Insert by Reference
1364 'N', -- Lock Text
1365 'Y', -- Global Yn
1366 t_art_language_tbl(i),
1367 'APPROVED', -- Article Status
1368 b.sav_release, -- Sav Release
1369 t_start_date_tbl(i), -- Start Date
1370 t_end_date_tbl(i), -- End Date
1371 NULL, -- Std Article Version Id
1372 t_display_name_tbl(i), -- Display Name
1373 NULL, -- Translated Yn
1374 t.short_description,
1375 sysdate, -- Date Approved
1376 NULL, -- Default Section
1377 'OKCMIGRATE', -- Reference Source
1378 NULL, -- Reference Text
1379 t_system_reference_code_tbl(i), -- Orig System Reference Code
1380 t_ver_system_reference_id1_tbl(i), -- System Reference ID1
1381 NULL, -- Orig System Reference Id2
1382 NULL, -- Additional Instructions
1383 NULL, -- Variation Description
1384 NULL, -- Adoption Type
1385 G_PROGRAM_ID,
1386 G_PROGRAM_LOGIN_ID,
1387 G_PROGRAM_APPL_ID,
1388 G_REQUEST_ID,
1389 b.attribute_category,
1390 substrb(b.attribute1,1,150),
1391 substrb(b.attribute2,1,150),
1392 substrb(b.attribute3,1,150),
1393 substrb(b.attribute4,1,150),
1394 substrb(b.attribute5,1,150),
1395 substrb(b.attribute6,1,150),
1396 substrb(b.attribute7,1,150),
1397 substrb(b.attribute8,1,150),
1398 substrb(b.attribute9,1,150),
1399 substrb(b.attribute10,1,150),
1400 substrb(b.attribute11,1,150),
1401 substrb(b.attribute12,1,150),
1402 substrb(b.attribute13,1,150),
1403 substrb(b.attribute14,1,150),
1404 substrb(b.attribute15,1,150), b.object_version_number, -- Object Version Number
1405 b.created_by, -- Created By
1406 b.creation_date, -- Creation Date
1407 b.last_updated_by, -- Last Updated By
1408 b.last_update_login, -- Last Update Login
1409 SYSDATE -- Last Update Date
1410 FROM OKC_STD_ART_VERSIONS_B B,
1411 OKC_STD_ART_VERSIONS_TL T
1412 WHERE
1413 B.SAE_ID = t_ver_system_reference_id1_tbl(i)
1414 AND B.SAV_RELEASE = t_artv_sav_release_tbl(i)
1415 AND T.LANGUAGE = t_ver_language_tbl(i)
1416 AND T.SAE_ID = B.SAE_ID
1417 AND T.SAV_RELEASE = B.SAV_RELEASE
1418 AND EXISTS
1419 (SELECT 1 FROM OKC_ARTICLES_ALL ART WHERE
1420 ART.ARTICLE_ID = t_article_id_tbl(i) AND
1421 ART.ORIG_SYSTEM_REFERENCE_ID1 = TO_CHAR(B.SAE_ID) AND
1422 ART.ORIG_SYSTEM_REFERENCE_ID1 = t_ver_system_reference_id1_tbl(i));
1423
1424 EXCEPTION
1425 WHEN OTHERS THEN
1426 IF (l_debug = 'Y') THEN
1427 okc_debug.Log('500: Leaving Article Versions because of EXCEPTION: '||sqlerrm, 2);
1428 END IF;
1429 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1430 p_msg_name => G_UNEXPECTED_ERROR,
1431 p_token1 => G_SQLCODE_TOKEN,
1432 p_token1_value => sqlcode,
1433 p_token2 => G_SQLERRM_TOKEN,
1434 p_token2_value => sqlerrm);
1435 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1436 p_msg_name => 'OKC_ART_FETCH_FAILED',
1437 p_token1 => 'CONTEXT',
1438 p_token1_value => g_context);
1439 l_return_status := G_RET_STS_ERROR;
1440 x_return_status := G_RET_STS_ERROR;
1441 ROLLBACK TO SAVEPOINT bulkdml;
1442 RAISE FND_API.G_EXC_ERROR;
1443 END;
1444
1445 --
1446 -- End of Insert into OKC_ARTICLE_VERSIONS
1447 --
1448 --
1449 -- Bulk insert Article relationships
1450 BEGIN
1451 G_context := 'ARL';
1452 FORALL i in article_id_tbl.FIRST ..article_id_tbl.LAST
1453 INSERT INTO OKC_ARTICLE_RELATNS_ALL(
1454 SOURCE_ARTICLE_ID,
1455 TARGET_ARTICLE_ID,
1456 ORG_ID,
1457 RELATIONSHIP_TYPE,
1458 OBJECT_VERSION_NUMBER,
1459 CREATED_BY,
1460 CREATION_DATE,
1461 LAST_UPDATED_BY,
1462 LAST_UPDATE_LOGIN,
1463 LAST_UPDATE_DATE)
1464 SELECT /*+ ORDERED USE_NL(INC,TAR) */
1465 SRC.ARTICLE_ID source_article_id,
1466 TAR.ARTICLE_ID target_article_id,
1467 ORG.ORGANIZATION_ID,
1468 'INCOMPATIBLE',
1469 INC.object_version_number,
1470 INC.created_by,
1471 INC.creation_date,
1472 INC.last_updated_by,
1473 INC.last_update_login,
1474 INC.last_update_date
1475 FROM OKC_ARTICLES_ALL SRC, OKC_STD_ART_INCMPTS INC, OKC_ARTICLES_ALL TAR,
1476 HR_ORGANIZATION_INFORMATION ORG
1477 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
1478 AND SRC.orig_system_reference_id1 = INC.SAE_ID
1479 AND SRC.orig_system_reference_code in ('OKCMIGNEW' , 'OKCMIGORIG')
1480 AND TAR.orig_system_reference_id1 = TO_CHAR(INC.SAE_ID_FOR)
1481 AND TAR.orig_system_reference_code = SRC.orig_system_reference_code
1482 AND SRC.article_id = article_id_tbl(i)
1483 AND art_process_status_tbl(i) = 'S'
1484 AND NOT EXISTS
1485 (SELECT /*+ NO_UNNEST */
1486 1 FROM OKC_ARTICLE_RELATNS_ALL REL1
1487 WHERE rel1.source_article_id = src.article_id and
1488 rel1.target_article_id = tar.article_id and
1489 rel1.org_id = org.organization_id);
1490
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 IF (l_debug = 'Y') THEN
1494 okc_debug.Log('500: Leaving Article Relations because of EXCEPTION: '||sqlerrm, 2);
1495 END IF;
1496 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1497 p_msg_name => G_UNEXPECTED_ERROR,
1498 p_token1 => G_SQLCODE_TOKEN,
1499 p_token1_value => sqlcode,
1500 p_token2 => G_SQLERRM_TOKEN,
1501 p_token2_value => sqlerrm);
1502 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1503 p_msg_name => 'OKC_ART_FETCH_FAILED',
1504 p_token1 => 'CONTEXT',
1505 p_token1_value => g_context);
1506 l_return_status := G_RET_STS_ERROR;
1507 x_return_status := G_RET_STS_ERROR;
1508 ROLLBACK TO SAVEPOINT bulkdml;
1509 RAISE FND_API.G_EXC_ERROR;
1510 END;
1511
1512 BEGIN
1513 G_context := 'ARL2';
1514 FORALL i in article_id_tbl.FIRST ..article_id_tbl.LAST
1515 INSERT INTO OKC_ARTICLE_RELATNS_ALL(
1516 SOURCE_ARTICLE_ID,
1517 TARGET_ARTICLE_ID,
1518 ORG_ID,
1519 RELATIONSHIP_TYPE,
1520 OBJECT_VERSION_NUMBER,
1521 CREATED_BY,
1522 CREATION_DATE,
1523 LAST_UPDATED_BY,
1524 LAST_UPDATE_LOGIN,
1525 LAST_UPDATE_DATE)
1526 SELECT /*+ ORDERED USE_NL(INC,SRC) */
1527 SRC.ARTICLE_ID source_article_id,
1528 TAR.ARTICLE_ID target_article_id,
1529 ORG.ORGANIZATION_ID,
1530 'INCOMPATIBLE',
1531 INC.object_version_number,
1532 INC.created_by,
1533 INC.creation_date,
1534 INC.last_updated_by,
1535 INC.last_update_login,
1536 INC.last_update_date
1537 FROM OKC_ARTICLES_ALL TAR, OKC_STD_ART_INCMPTS INC, OKC_ARTICLES_ALL SRC,
1538 HR_ORGANIZATION_INFORMATION ORG
1539 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
1540 AND SRC.orig_system_reference_id1 = TO_CHAR(INC.SAE_ID)
1541 AND SRC.orig_system_reference_code in ('OKCMIGNEW' , 'OKCMIGORIG')
1542 AND TAR.orig_system_reference_id1 = INC.SAE_ID_FOR
1543 AND TAR.orig_system_reference_code = SRC.orig_system_reference_code
1544 AND TAR.article_id = article_id_tbl(i)
1545 AND art_process_status_tbl(i) = 'S'
1546 AND NOT EXISTS
1547 (SELECT /*+ NO_UNNEST */
1548 1 FROM OKC_ARTICLE_RELATNS_ALL REL1
1549 WHERE rel1.source_article_id = src.article_id and
1550 rel1.target_article_id = tar.article_id and
1551 rel1.org_id = org.organization_id);
1552
1553 EXCEPTION
1554 WHEN OTHERS THEN
1555 IF (l_debug = 'Y') THEN
1556 okc_debug.Log('500: Leaving Article Relations because of EXCEPTION: '||sqlerrm, 2);
1557 END IF;
1558 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1559 p_msg_name => G_UNEXPECTED_ERROR,
1560 p_token1 => G_SQLCODE_TOKEN,
1561 p_token1_value => sqlcode,
1562 p_token2 => G_SQLERRM_TOKEN,
1563 p_token2_value => sqlerrm);
1564 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1565 p_msg_name => 'OKC_ART_FETCH_FAILED',
1566 p_token1 => 'CONTEXT',
1567 p_token1_value => g_context);
1568 l_return_status := G_RET_STS_ERROR;
1569 x_return_status := G_RET_STS_ERROR;
1570 ROLLBACK TO SAVEPOINT bulkdml;
1571 RAISE FND_API.G_EXC_ERROR;
1572 END;
1573
1574 BEGIN
1575 G_context := 'ARL3';
1576 FORALL i in article_id_tbl.FIRST ..article_id_tbl.LAST
1577 INSERT INTO OKC_ARTICLE_RELATNS_ALL(
1578 SOURCE_ARTICLE_ID,
1579 TARGET_ARTICLE_ID,
1580 ORG_ID,
1581 RELATIONSHIP_TYPE,
1582 OBJECT_VERSION_NUMBER,
1583 CREATED_BY,
1584 CREATION_DATE,
1585 LAST_UPDATED_BY,
1586 LAST_UPDATE_LOGIN,
1587 LAST_UPDATE_DATE)
1588 SELECT /*+ ORDERED USE_NL(INC,TAR) */
1589 TAR.ARTICLE_ID target_article_id,
1590 SRC.ARTICLE_ID source_article_id,
1591 ORG.ORGANIZATION_ID,
1592 'INCOMPATIBLE',
1593 INC.object_version_number,
1594 INC.created_by,
1595 INC.creation_date,
1596 INC.last_updated_by,
1597 INC.last_update_login,
1598 INC.last_update_date
1599 FROM OKC_ARTICLES_ALL SRC, OKC_STD_ART_INCMPTS INC, OKC_ARTICLES_ALL TAR,
1600 HR_ORGANIZATION_INFORMATION ORG
1601 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
1602 AND SRC.orig_system_reference_id1 = INC.SAE_ID
1603 AND SRC.orig_system_reference_code in ('OKCMIGNEW' , 'OKCMIGORIG')
1604 AND TAR.orig_system_reference_id1 = TO_CHAR(INC.SAE_ID_FOR)
1605 AND TAR.orig_system_reference_code = SRC.orig_system_reference_code
1606 AND SRC.article_id = article_id_tbl(i)
1607 AND art_process_status_tbl(i) = 'S'
1608 AND NOT EXISTS
1609 (SELECT /*+ NO_UNNEST */
1610 1 FROM OKC_ARTICLE_RELATNS_ALL REL1
1611 WHERE rel1.source_article_id = src.article_id and
1612 rel1.target_article_id = tar.article_id and
1613 rel1.org_id = org.organization_id);
1614
1615 EXCEPTION
1616 WHEN OTHERS THEN
1617 IF (l_debug = 'Y') THEN
1618 okc_debug.Log('500: Leaving Article Relations because of EXCEPTION: '||sqlerrm, 2);
1619 END IF;
1620 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1621 p_msg_name => G_UNEXPECTED_ERROR,
1622 p_token1 => G_SQLCODE_TOKEN,
1623 p_token1_value => sqlcode,
1624 p_token2 => G_SQLERRM_TOKEN,
1625 p_token2_value => sqlerrm);
1626 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1627 p_msg_name => 'OKC_ART_FETCH_FAILED',
1628 p_token1 => 'CONTEXT',
1629 p_token1_value => g_context);
1630 l_return_status := G_RET_STS_ERROR;
1631 x_return_status := G_RET_STS_ERROR;
1632 ROLLBACK TO SAVEPOINT bulkdml;
1633 RAISE FND_API.G_EXC_ERROR;
1634 END;
1635
1636 BEGIN
1637 G_context := 'ARL4';
1638 FORALL i in article_id_tbl.FIRST ..article_id_tbl.LAST
1639 INSERT INTO OKC_ARTICLE_RELATNS_ALL(
1640 SOURCE_ARTICLE_ID,
1641 TARGET_ARTICLE_ID,
1642 ORG_ID,
1643 RELATIONSHIP_TYPE,
1644 OBJECT_VERSION_NUMBER,
1645 CREATED_BY,
1646 CREATION_DATE,
1647 LAST_UPDATED_BY,
1648 LAST_UPDATE_LOGIN,
1649 LAST_UPDATE_DATE)
1650 SELECT /*+ ORDERED USE_NL(INC,SRC) */
1651 TAR.ARTICLE_ID target_article_id,
1652 SRC.ARTICLE_ID source_article_id,
1653 ORG.ORGANIZATION_ID,
1654 'INCOMPATIBLE',
1655 INC.object_version_number,
1656 INC.created_by,
1657 INC.creation_date,
1658 INC.last_updated_by,
1659 INC.last_update_login,
1660 INC.last_update_date
1661 FROM OKC_ARTICLES_ALL TAR, OKC_STD_ART_INCMPTS INC, OKC_ARTICLES_ALL SRC,
1662 HR_ORGANIZATION_INFORMATION ORG
1663 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
1664 AND SRC.orig_system_reference_id1 = TO_CHAR(INC.SAE_ID)
1665 AND SRC.orig_system_reference_code in ('OKCMIGNEW' , 'OKCMIGORIG')
1666 AND TAR.orig_system_reference_id1 = INC.SAE_ID_FOR
1667 AND TAR.orig_system_reference_code = SRC.orig_system_reference_code
1668 AND TAR.article_id = article_id_tbl(i)
1669 AND art_process_status_tbl(i) = 'S'
1670 AND NOT EXISTS
1671 (SELECT /*+ NO_UNNEST */
1672 1 FROM OKC_ARTICLE_RELATNS_ALL REL1
1673 WHERE rel1.source_article_id = src.article_id and
1674 rel1.target_article_id = tar.article_id and
1675 rel1.org_id = org.organization_id);
1676
1677 EXCEPTION
1678 WHEN OTHERS THEN
1679 IF (l_debug = 'Y') THEN
1680 okc_debug.Log('500: Leaving Article Relations because of EXCEPTION: '||sqlerrm, 2);
1681 END IF;
1682 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1683 p_msg_name => G_UNEXPECTED_ERROR,
1684 p_token1 => G_SQLCODE_TOKEN,
1685 p_token1_value => sqlcode,
1686 p_token2 => G_SQLERRM_TOKEN,
1687 p_token2_value => sqlerrm);
1688 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1689 p_msg_name => 'OKC_ART_FETCH_FAILED',
1690 p_token1 => 'CONTEXT',
1691 p_token1_value => g_context);
1692 l_return_status := G_RET_STS_ERROR;
1693 x_return_status := G_RET_STS_ERROR;
1694 ROLLBACK TO SAVEPOINT bulkdml;
1695 RAISE FND_API.G_EXC_ERROR;
1696 END;
1697
1698 BEGIN
1699 G_context := 'ADP';
1700
1701 -- Bulk insert Article Adoptions
1702 FORALL i in t_article_version_id_tbl.FIRST ..t_article_version_id_tbl.LAST
1703 INSERT INTO OKC_ARTICLE_ADOPTIONS
1704 (
1705 GLOBAL_ARTICLE_VERSION_ID,
1706 ADOPTION_TYPE,
1707 LOCAL_ORG_ID,
1708 ADOPTION_STATUS,
1709 LOCAL_ARTICLE_VERSION_ID,
1710 OBJECT_VERSION_NUMBER,
1711 CREATED_BY,
1712 CREATION_DATE,
1713 LAST_UPDATED_BY,
1714 LAST_UPDATE_LOGIN,
1715 LAST_UPDATE_DATE
1716 )
1717 SELECT
1718 t_article_version_id_tbl(i),
1719 'ADOPTED',
1720 organization_id,
1721 'APPROVED',
1722 NULL,
1723 1.0,
1724 G_User_Id,
1725 sysdate,
1726 G_User_Id,
1727 G_Login_Id,
1728 sysdate
1729 FROM HR_ORGANIZATION_INFORMATION
1730 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
1731 AND ORGANIZATION_ID <> G_GLOBAL_ORG_ID
1732 AND NOT EXISTS
1733 (SELECT /*+ NO_UNNEST */
1734 1 FROM OKC_ARTICLE_ADOPTIONS
1735 WHERE GLOBAL_ARTICLE_VERSION_ID = t_article_version_id_tbl(i)
1736 AND LOCAL_ORG_ID = ORGANIZATION_ID);
1737 EXCEPTION
1738 WHEN OTHERS THEN
1739 IF (l_debug = 'Y') THEN
1740 okc_debug.Log('500: Leaving Article Versions because of EXCEPTION: '||sqlerrm, 2);
1741 END IF;
1742 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1743 p_msg_name => G_UNEXPECTED_ERROR,
1744 p_token1 => G_SQLCODE_TOKEN,
1745 p_token1_value => sqlcode,
1746 p_token2 => G_SQLERRM_TOKEN,
1747 p_token2_value => sqlerrm);
1748 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1749 p_msg_name => 'OKC_ART_FETCH_FAILED',
1750 p_token1 => 'CONTEXT',
1751 p_token1_value => g_context);
1752 l_return_status := G_RET_STS_ERROR;
1753 x_return_status := G_RET_STS_ERROR;
1754 ROLLBACK TO SAVEPOINT bulkdml;
1755 RAISE FND_API.G_EXC_ERROR;
1756 END;
1757
1758 -- Update contract articles with newly generated ids.
1759 -- Newly Generated Ids has been moved to column Old_id of OKC_K_ARTICLES_B
1760
1761 BEGIN
1762 G_context := 'KART';
1763 FORALL i in t_article_version_id_tbl.FIRST ..t_article_version_id_tbl.LAST
1764 UPDATE OKC_K_ARTICLES_B B
1765 SET ARTICLE_VERSION_ID = t_article_version_id_tbl(i),
1766 SAV_SAE_ID = t_article_id_tbl(i),
1767 ORIG_ARTICLE_ID = t_article_id_tbl(i)
1768 WHERE OLD_ID IN
1769 (SELECT TL.ID FROM OKC_K_ARTICLES_TL TL
1770 WHERE sav_sav_release=t_artv_sav_release_tbl(i)
1771 AND language=l_language
1772 AND text is NULL )
1773 AND sav_sae_id = t_ver_system_reference_id1_tbl(i)
1774 AND l_language = t_art_language_tbl(i)
1775 AND ARTICLE_VERSION_ID IS NULL;
1776 EXCEPTION
1777 WHEN OTHERS THEN
1778 IF (l_debug = 'Y') THEN
1779 okc_debug.Log('500: Leaving Article Versions because of EXCEPTION: '||sqlerrm, 2);
1780 END IF;
1781 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1782 p_msg_name => G_UNEXPECTED_ERROR,
1783 p_token1 => G_SQLCODE_TOKEN,
1784 p_token1_value => sqlcode,
1785 p_token2 => G_SQLERRM_TOKEN,
1786 p_token2_value => sqlerrm);
1787 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1788 p_msg_name => 'OKC_ART_FETCH_FAILED');
1789
1790 l_return_status := G_RET_STS_ERROR;
1791 x_return_status := G_RET_STS_ERROR;
1792 ROLLBACK TO SAVEPOINT bulkdml;
1793 RAISE FND_API.G_EXC_ERROR;
1794 END;
1795
1796 -- Update contract articles history with newly generated ids.
1797 -- Newly Generated Ids has been moved to column Old_id of OKC_K_ARTICLES_BH
1798
1799 BEGIN
1800 G_context := 'KARTH';
1801 FORALL i in t_article_version_id_tbl.FIRST ..t_article_version_id_tbl.LAST
1802 UPDATE OKC_K_ARTICLES_BH B
1803 SET ARTICLE_VERSION_ID = t_article_version_id_tbl(i),
1804 SAV_SAE_ID = t_article_id_tbl(i),
1805 ORIG_ARTICLE_ID = t_article_id_tbl(i)
1806 WHERE (OLD_ID, MAJOR_VERSION) IN
1807 (SELECT TL.ID, TL.MAJOR_VERSION FROM OKC_K_ARTICLES_TLH TL
1808 WHERE sav_sav_release=t_artv_sav_release_tbl(i)
1809 AND TL.ID = B.OLD_ID
1810 AND TL.MAJOR_VERSION = B.MAJOR_VERSION
1811 AND language=l_language
1812 AND text is NULL )
1813 AND sav_sae_id = t_ver_system_reference_id1_tbl(i)
1814 AND l_language = t_art_language_tbl(i)
1815 AND ARTICLE_VERSION_ID IS NULL;
1816 EXCEPTION
1817 WHEN OTHERS THEN
1818 IF (l_debug = 'Y') THEN
1819 okc_debug.Log('500: Leaving Article Versions because of EXCEPTION: '||sqlerrm, 2);
1820 END IF;
1821 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1822 p_msg_name => G_UNEXPECTED_ERROR,
1823 p_token1 => G_SQLCODE_TOKEN,
1824 p_token1_value => sqlcode,
1825 p_token2 => G_SQLERRM_TOKEN,
1826 p_token2_value => sqlerrm);
1827 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1828 p_msg_name => 'OKC_ART_FETCH_FAILED',
1829 p_token1 => 'CONTEXT',
1830 p_token1_value => g_context);
1831
1832 l_return_status := G_RET_STS_ERROR;
1833 x_return_status := G_RET_STS_ERROR;
1834 ROLLBACK TO SAVEPOINT bulkdml;
1835 RAISE FND_API.G_EXC_ERROR;
1836 END;
1837
1838 BEGIN
1839 FORALL i in article_id_tbl.FIRST ..article_id_tbl.LAST
1840 INSERT INTO OKC_FOLDER_CONTENTS
1841 (
1842 FOLDER_ID,
1843 MEMBER_ID,
1844 OBJECT_VERSION_NUMBER,
1845 CREATED_BY,
1846 CREATION_DATE,
1847 LAST_UPDATED_BY,
1848 LAST_UPDATE_LOGIN,
1849 LAST_UPDATE_DATE
1850 )
1851 SELECT
1852 FOLDER_ID,
1853 article_id_tbl(i),
1854 1.0,
1855 SM.CREATED_BY,
1856 SM.CREATION_DATE,
1857 SM.LAST_UPDATED_BY,
1858 SM.LAST_UPDATE_LOGIN,
1859 SM.LAST_UPDATE_DATE
1860 FROM OKC_STD_ART_SET_MEMS SM, OKC_FOLDERS_ALL_B FLD
1861 WHERE FLD.SAT_CODE = SM.SAT_CODE
1862 AND SAE_ID = art_system_reference_id1_tbl(i)
1863 AND system_reference_code_tbl(i) = 'OKCMIGORIG'
1864 AND art_process_status_tbl(i) = 'S'
1865 AND NOT EXISTS
1866 (SELECT /*+ NO_UNNEST */
1867 1 FROM OKC_FOLDER_CONTENTS
1868 WHERE FOLDER_ID = FLD.FOLDER_ID
1869 AND MEMBER_ID = article_id_tbl(i));
1870 EXCEPTION
1871 WHEN OTHERS THEN
1872 IF (l_debug = 'Y') THEN
1873 okc_debug.Log('500: Leaving Article Versions because of EXCEPTION: '||sqlerrm, 2);
1874 END IF;
1875 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1876 p_msg_name => G_UNEXPECTED_ERROR,
1877 p_token1 => G_SQLCODE_TOKEN,
1878 p_token1_value => sqlcode,
1879 p_token2 => G_SQLERRM_TOKEN,
1880 p_token2_value => sqlerrm);
1881 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1882 p_msg_name => 'OKC_ART_FETCH_FAILED',
1883 p_token1 => 'CONTEXT',
1884 p_token1_value => g_context);
1885
1886 l_return_status := G_RET_STS_ERROR;
1887 x_return_status := G_RET_STS_ERROR;
1888 ROLLBACK TO SAVEPOINT bulkdml;
1889 RAISE FND_API.G_EXC_ERROR;
1890 END;
1891
1892 ---------------------------------------------------------------------
1893 /* Migrate Attachments, Attachments exist at the version level
1894 update records in FND_ATTACHED_DOCUMENTS table
1895 ENTITY_NAME[varchar2(40)] : old(STD_ARTICLE_VERSIONS_B), new (?)
1896 PK1_VALUE[varchar2(100)] : old (sae_id:number), new (article_version_id:number)
1897 PK2_VALUE[varchar2(100)] : old (sav_release:varchar), new ( NULL)
1898 LAST_UPDATE_DATE : sysdate
1899 LAST_UPDATE_LOGIN:
1900 LAST_UPDATED_BY:
1901 */
1902 ---------------------------------------------------------------------
1903
1904 BEGIN
1905 G_context := 'ATT';
1906 i := 0;
1907
1908 -- Update Article Version Attachments
1909 FORALL i in t_article_version_id_tbl.FIRST ..t_article_version_id_tbl.LAST
1910 UPDATE FND_ATTACHED_DOCUMENTS
1911 SET ENTITY_NAME = 'OKC_ARTICLE_VERSIONS',
1912 PK1_VALUE = to_char(t_article_version_id_tbl(i)),
1913 PK2_VALUE = NULL,
1914 LAST_UPDATE_DATE = SYSDATE,
1915 LAST_UPDATE_LOGIN = G_Login_Id,
1916 LAST_UPDATED_BY = G_User_Id
1917 WHERE ENTITY_NAME = 'OKC_STD_ARTICLES_B'
1918 AND PK1_VALUE = t_ver_system_reference_id1_tbl(i)
1919 AND PK2_VALUE = t_artv_sav_release_tbl(i)
1920 AND EXISTS
1921 (SELECT 1 FROM OKC_ARTICLES_ALL ART WHERE
1922 ART.ARTICLE_ID = t_article_id_tbl(i) AND
1923 ART.ORIG_SYSTEM_REFERENCE_ID1 = t_ver_system_reference_id1_tbl(i));
1924 EXCEPTION
1925 WHEN OTHERS THEN
1926 IF (l_debug = 'Y') THEN
1927 okc_debug.Log('500: Leaving Article Version Attachments because of EXCEPTION: '||sqlerrm, 2);
1928 END IF;
1929 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1930 p_msg_name => G_UNEXPECTED_ERROR,
1931 p_token1 => G_SQLCODE_TOKEN,
1932 p_token1_value => sqlcode,
1933 p_token2 => G_SQLERRM_TOKEN,
1934 p_token2_value => sqlerrm);
1935 Okc_API.Set_Message(p_app_name => G_APP_NAME,
1936 p_msg_name => 'OKC_ART_FETCH_FAILED',
1937 p_token1 => 'CONTEXT',
1938 p_token1_value => g_context);
1939 l_return_status := G_RET_STS_ERROR;
1940 x_return_status := G_RET_STS_ERROR;
1941 ROLLBACK TO SAVEPOINT bulkdml;
1942 RAISE FND_API.G_EXC_ERROR;
1943 END;
1944
1945
1946 /*
1947 UPDATE OKC_STD_ART_SET_MEMS MEM
1948 SET SAE_ID = article_id_tbl(i)
1949 WHERE SAE_ID = art_system_reference_id1_tbl(i)
1950 AND system_reference_code_tbl(i) = 'OKCMIGORIG'
1951 AND art_process_status_tbl(i) = 'S';
1952 */
1953
1954 END IF; -- end of (if t_article_version_tbl.COUNT > 0)
1955
1956 i := 0;
1957 ------------------------------------------------------------------------
1958 --------------- End of Do_DML for migrate related tables ------------
1959 -------------------------------------------------------------------------
1960 COMMIT;
1961 -- Now delete cache for next bulk fetch
1962
1963 ver_language_tbl.DELETE;
1964 article_title_tbl.DELETE;
1965 article_number_tbl.DELETE;
1966 article_id_tbl.DELETE;
1967 article_language_tbl.DELETE;
1968 system_reference_code_tbl.DELETE;
1969 art_system_reference_id1_tbl.DELETE;
1970 article_version_id_tbl.DELETE;
1971 start_date_tbl.DELETE;
1972 article_text_tbl.DELETE;
1973 artv_sav_release_tbl.DELETE;
1974 t_ver_language_tbl.DELETE;
1975 t_art_language_tbl.DELETE;
1976 t_article_id_tbl.DELETE;
1977 t_article_version_id_tbl.DELETE;
1978 t_art_version_number_tbl.DELETE;
1979 t_start_date_tbl.DELETE;
1980 t_end_date_tbl.DELETE;
1981 t_article_text_tbl.DELETE;
1982 t_artv_sav_release_tbl.DELETE;
1983 art_process_status_tbl.DELETE;
1984 ver_process_status_tbl.DELETE;
1985 t_ver_system_reference_id1_tbl.DELETE;
1986 t_system_reference_code_tbl.DELETE;
1987 t_display_name_tbl.DELETE;
1988 display_name_tbl.DELETE;
1989
1990 EXIT WHEN l_orig_article_csr%NOTFOUND;
1991 EXCEPTION -- from line 871 approx
1992
1993 WHEN FND_API.G_EXC_ERROR THEN
1994 IF (l_debug = 'Y') THEN
1995 okc_debug.Log('300: Error occurred in this fetch... Moving on to next fetch: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1996 END IF;
1997 l_return_status := G_RET_STS_ERROR ;
1998 x_return_status := G_RET_STS_ERROR ;
1999
2000 -- MKS 09/10 ... Table variables should be initialized for every batch run. This error still make the pgm continue for next batch.
2001 ver_language_tbl.DELETE;
2002 article_title_tbl.DELETE;
2003 article_number_tbl.DELETE;
2004 article_id_tbl.DELETE;
2005 article_language_tbl.DELETE;
2006 system_reference_code_tbl.DELETE;
2007 art_system_reference_id1_tbl.DELETE;
2008 article_version_id_tbl.DELETE;
2009 start_date_tbl.DELETE;
2010 article_text_tbl.DELETE;
2011 artv_sav_release_tbl.DELETE;
2012 t_ver_language_tbl.DELETE;
2013 t_art_language_tbl.DELETE;
2014 t_article_id_tbl.DELETE;
2015 t_article_version_id_tbl.DELETE;
2016 t_art_version_number_tbl.DELETE;
2017 t_start_date_tbl.DELETE;
2018 t_end_date_tbl.DELETE;
2019 t_article_text_tbl.DELETE;
2020 t_artv_sav_release_tbl.DELETE;
2021 art_process_status_tbl.DELETE;
2022 ver_process_status_tbl.DELETE;
2023 t_ver_system_reference_id1_tbl.DELETE;
2024 t_system_reference_code_tbl.DELETE;
2025 t_display_name_tbl.DELETE;
2026 display_name_tbl.DELETE;
2027
2028 WHEN G_EXC_PREREQ_SETUP_ERROR THEN
2029 IF (l_debug = 'Y') THEN
2030 okc_debug.Log('400: Leaving Fetch Loop: No more processing: Prereq is not properly set(AUTONUMBER)', 2);
2031 END IF;
2032 --
2033 l_return_status := G_RET_STS_ERROR ;
2034 x_return_status := G_RET_STS_ERROR ;
2035 get_print_msgs_stack;
2036 exit;-- exit this loop
2037
2038
2039 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2040 IF (l_debug = 'Y') THEN
2041 okc_debug.Log('400: Leaving Fetch Loop: No more processing: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2042 END IF;
2043
2044 IF l_orig_article_csr%ISOPEN THEN
2045 CLOSE l_orig_article_csr;
2046 END IF;
2047 IF l_orig_ver_csr%ISOPEN THEN
2048 CLOSE l_orig_ver_csr;
2049 END IF;
2050 IF l_unq_csr%ISOPEN THEN
2051 CLOSE l_unq_csr;
2052 END IF;
2053 l_return_status := G_RET_STS_UNEXP_ERROR ;
2054 x_return_status := G_RET_STS_UNEXP_ERROR ;
2055 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2056 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2057 END IF;
2058 get_print_msgs_stack;
2059 exit;
2060
2061 WHEN OTHERS THEN
2062 IF (l_debug = 'Y') THEN
2063 okc_debug.Log('500: Leaving Fetch Loop: No more processing: because of EXCEPTION: '||sqlerrm, 2);
2064 END IF;
2065
2066 IF l_orig_article_csr%ISOPEN THEN
2067 CLOSE l_orig_article_csr;
2068 END IF;
2069 IF l_orig_ver_csr%ISOPEN THEN
2070 CLOSE l_orig_ver_csr;
2071 END IF;
2072 IF l_unq_csr%ISOPEN THEN
2073 CLOSE l_unq_csr;
2074 END IF;
2075 l_return_status := G_RET_STS_UNEXP_ERROR ;
2076 x_return_status := G_RET_STS_UNEXP_ERROR ;
2077 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2078 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2079 END IF;
2080 get_print_msgs_stack;
2081 -- FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => G_FALSE );
2082 exit;
2083 END; -- line 871 approx
2084 END LOOP; -- line 870 approx
2085
2086
2087 -----------------------------------------------------------------------
2088 -- End of outermost loop for bulk fetch
2089 -----------------------------------------------------------------------
2090
2091 IF l_orig_article_csr%ISOPEN THEN
2092 CLOSE l_orig_article_csr;
2093 END IF;
2094
2095 IF not l_migrated THEN
2096 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2097 p_msg_name => 'OKC_ART_MIG_OUTPUT_NO_ART');
2098 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MSG_PUB.Get(1,p_encoded =>FND_API.G_FALSE ));
2099 ELSE
2100 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2101 p_msg_name => 'OKC_ART_MIG_OUTPUT');
2102 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MSG_PUB.Get(1,p_encoded =>FND_API.G_FALSE ));
2103 FOR i in l_org_name_tbl.FIRST..l_org_name_tbl.LAST LOOP
2104 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_org_name_tbl(i));
2105 END LOOP;
2106 END IF;
2107
2108 FND_MSG_PUB.initialize;
2109 l_org_id_tbl.DELETE;
2110 l_org_name_tbl.DELETE;
2111
2112 IF (l_debug = 'Y') THEN
2113 okc_debug.Log('200: Leaving articles migrate', 2);
2114 END IF;
2115 x_return_status := l_return_status;
2116
2117 EXCEPTION
2118
2119 WHEN FND_API.G_EXC_ERROR THEN
2120 IF (l_debug = 'Y') THEN
2121 okc_debug.Log('300: Leaving Articles_Migrate: OKC_API.G_EXCEPTION_ERROR Exception', 2);
2122 END IF;
2123 get_print_msgs_stack;
2124 x_return_status := G_RET_STS_ERROR ;
2125
2126 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2127 IF (l_debug = 'Y') THEN
2128 okc_debug.Log('400: Leaving Articles_Migrate: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2129 END IF;
2130
2131 IF l_orig_article_csr%ISOPEN THEN
2132 CLOSE l_orig_article_csr;
2133 END IF;
2134 x_return_status := G_RET_STS_UNEXP_ERROR ;
2135 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2136 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2137 END IF;
2138 get_print_msgs_stack;
2139
2140 WHEN OTHERS THEN
2141 IF (l_debug = 'Y') THEN
2142 okc_debug.Log('500: Leaving Articles_Migrate because of EXCEPTION: '||sqlerrm, 2);
2143 END IF;
2144
2145 IF l_orig_article_csr%ISOPEN THEN
2146 CLOSE l_orig_article_csr;
2147 END IF;
2148 x_return_status := G_RET_STS_UNEXP_ERROR ;
2149 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2150 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2151 END IF;
2152 get_print_msgs_stack;
2153 -- FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => G_FALSE );
2154 END migrate_articles;
2155
2156
2157 END OKC_ARTICLES_MIGRATE_GRP;