1 PACKAGE BODY OKC_ADOPTIONS_GRP AS
2 /* $Header: OKCGADPB.pls 120.0 2005/05/25 19:10:15 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 ---------------------------------------------------------------------------
11 -- GLOBAL VARIABLES
12 ---------------------------------------------------------------------------
13 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_ADOPTIONS_GRP';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
22 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
23 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
24
25 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
26 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
27 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
28
29 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
30 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
31 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
32 G_GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
33
34 ---------------------------------------
35 -- PROCEDURE check adoption details --
36 ---------------------------------------
37 -- Where Used : 'Submit for Approve', 'Localize'
38 -- Where Called : 'Localized' - ArticleVAM java
39 -- 'Submit for Approve' - OKC_ARTICLE_STATUS_CHANGE_PVT.pending_approval
40 PROCEDURE check_adoption_details(
41 p_api_version IN NUMBER,
42 p_init_msg_list IN VARCHAR2 ,
43 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2,
48
49 x_earlier_local_version_id OUT NOCOPY VARCHAR2,
50 p_global_article_version_id IN NUMBER,
51 p_adoption_type IN VARCHAR2,
52 p_local_org_id IN NUMBER
53 ) IS
54 l_api_version CONSTANT NUMBER := 1;
55 l_api_name CONSTANT VARCHAR2(30) := 'g_check_adoption';
56 l_lcz_article_id NUMBER;
57
58 l_global_org_id NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
59 l_local_article_title OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE;
60 l_adp_row_notfound BOOLEAN := FALSE;
61 l_lcz_row_notfound BOOLEAN := FALSE;
62 l_row_found BOOLEAN := FALSE;
63 l_row_notfound BOOLEAN := FALSE;
64 l_never_adopted BOOLEAN := FALSE;
65
66 CURSOR l_adoption_csr (cp_global_article_version_id IN NUMBER,
67 cp_local_org_id IN NUMBER) IS
68 SELECT article_id,
69 start_date,
70 article_status,
71 local_article_version_id,
72 okc_article_adoptions.adoption_type
73 FROM OKC_ARTICLE_ADOPTIONS, OKC_ARTICLE_VERSIONS
74 WHERE global_article_version_id = cp_global_article_version_id
75 AND article_version_id = global_article_version_id
76 AND global_yn = 'Y'
77 AND local_org_id = cp_local_org_id
78 AND article_status = 'APPROVED'
79 AND nvl(end_date, sysdate+1) >= trunc(sysdate)
80 AND okc_article_adoptions.adoption_type = 'AVAILABLE';
81
82 CURSOR l_latest_ver_adp_csr(cp_article_id IN NUMBER,
83 cp_article_version_id IN NUMBER,
84 cp_local_org_id IN NUMBER) IS
85 -- 8.1.7.4 compatibility
86 SELECT
87 av.start_date,
88 av.end_date,
89 ad.adoption_type,
90 ad.adoption_status,
91 av.article_version_number,
92 av.article_version_id
93 FROM
94 okc_article_versions av,
95 okc_article_adoptions ad
96 WHERE
97 av.article_id = cp_article_id
98 AND
99 ad.global_article_version_id = av.article_version_id
100 AND
101 ad.local_org_id = cp_local_org_id
102 AND
103 ad.adoption_type = 'ADOPTED'
104 AND
105 ad.global_article_version_id <> cp_article_version_id
106 AND
107 av.start_date = (SELECT
108 max(av1.start_date)
109 FROM
110 okc_article_versions av1,
111 okc_article_adoptions ad1
112 WHERE
113 av1.article_id = av.article_id
114 AND
115 ad1.global_article_version_id = av1.article_version_id
116 AND
117 ad1.local_org_id = ad.local_org_id
118 AND
119 ad1.adoption_type = 'ADOPTED'
120 AND
121 ad1.global_article_version_id <> cp_article_version_id
122 );
123
124 /*
125 SELECT S.GLOBAL_YN,
126 S.ARTICLE_STATUS,
127 S.START_DATE,
128 S.END_DATE,
129 S.MAX_START_DATE,
130 S.ADOPTION_TYPE,
131 S.ADOPTION_STATUS,
132 S.LOCAL_ARTICLE_VERSION_ID,
133 S.ARTICLE_VERSION_NUMBER,
134 S.ARTICLE_VERSION_ID
135 FROM (
136 SELECT
137 A.GLOBAL_YN,
138 A.ARTICLE_STATUS,
139 A.START_DATE, A.END_DATE,
140 MAX(A.START_DATE) OVER (PARTITION BY A.ARTICLE_ID) AS MAX_START_DATE,
141 AD.ADOPTION_TYPE,
142 AD.ADOPTION_STATUS,
143 AD.LOCAL_ARTICLE_VERSION_ID,
144 A.ARTICLE_VERSION_NUMBER,
145 A.ARTICLE_VERSION_ID
146 FROM OKC_ARTICLE_VERSIONS A, OKC_ARTICLE_ADOPTIONS AD
147 WHERE A.ARTICLE_ID = cp_article_id
148 AND AD.GLOBAL_ARTICLE_VERSION_ID = A.ARTICLE_VERSION_ID
149 AND AD.LOCAL_ORG_ID = cp_local_org_id
150 AND AD.ADOPTION_TYPE <> 'AVAILABLE'
151 AND AD.GLOBAL_ARTICLE_VERSION_ID <> cp_article_version_id
152 ) S
153 WHERE S.START_DATE = S.MAX_START_DATE;
154 */
155 -- Cursor to find article_id of localized articles if the global article is localized
156 CURSOR l_lcz_article_id_csr(cp_global_article_id IN NUMBER,
157 cp_local_org_id IN NUMBER) IS
158 SELECT distinct(ARVL.ARTICLE_ID)
159 FROM OKC_ARTICLE_ADOPTIONS ADP,
160 OKC_ARTICLE_VERSIONS ARVG,
161 OKC_ARTICLE_VERSIONS ARVL
162 WHERE ARVG.ARTICLE_ID = cp_global_article_id
163 AND ADP.GLOBAL_ARTICLE_VERSION_ID = ARVG.ARTICLE_VERSION_ID
164 AND ADP.LOCAL_ORG_ID = cp_local_org_id
165 AND ADP.ADOPTION_TYPE = 'LOCALIZED'
166 AND ADP.LOCAL_ARTICLE_VERSION_ID = ARVL.ARTICLE_VERSION_ID;
167
168 -- Cursor to find latest version for localized article
169 CURSOR l_latest_ver_lcz_csr(cp_lcz_article_id IN NUMBER) IS
170 SELECT
171 AV.START_DATE,
172 AV.END_DATE,
173 AV.ADOPTION_TYPE,
174 AV.ARTICLE_STATUS ADOPTION_STATUS,
175 AV.ARTICLE_VERSION_NUMBER,
176 AV.ARTICLE_VERSION_ID
177 FROM OKC_ARTICLE_VERSIONS AV
178 WHERE AV.ARTICLE_ID = cp_lcz_article_id
179 AND AV.ADOPTION_TYPE = 'LOCALIZED'
180 AND AV.START_DATE = ( SELECT MAX(V.START_DATE)
181 FROM OKC_ARTICLE_VERSIONS V
182 WHERE V.ARTICLE_ID = cp_lcz_article_id
183 AND V.ADOPTION_TYPE = 'LOCALIZED');
184
185 -- Cursor to check uniqueness of adopted article title in local
186 CURSOR l_unq_local_title_csr (cp_local_org_id IN NUMBER,
187 cp_global_article_id IN NUMBER,
188 cp_global_org_id IN NUMBER) IS
189 SELECT 1
190 FROM OKC_ARTICLES_ALL ARTL
191 WHERE ARTL.ORG_ID = cp_local_org_id
192 AND ARTL.STANDARD_YN = 'Y'
193 AND ARTL.ARTICLE_TITLE = ( SELECT ARTG.ARTICLE_TITLE
194 FROM OKC_ARTICLES_ALL ARTG
195 WHERE ARTG.ARTICLE_ID = cp_global_article_id
196 AND ARTG.ORG_ID = cp_global_org_id
197 AND ARTG.STANDARD_YN= 'Y');
198
199
200 l_latest_ver_adp_rec l_latest_ver_adp_csr%ROWTYPE;
201 l_latest_ver_lcz_rec l_latest_ver_lcz_csr%ROWTYPE;
202 l_adoption_rec l_adoption_csr%ROWTYPE;
203
204 BEGIN
205
206 IF (l_debug = 'Y') THEN
207 okc_debug.log('100: Entered check_adoption with parameters: ' || p_global_article_version_id ||'*'|| p_adoption_type ||'*'|| p_local_org_id, 2);
208 END IF;
209 x_earlier_local_version_id := NULL;
210
211 -- Standard Start of API savepoint
212 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 END IF;
215 -- Initialize message list if p_init_msg_list is set to TRUE.
216 IF FND_API.to_Boolean( p_init_msg_list ) THEN
217 FND_MSG_PUB.initialize;
218 END IF;
219
220 -- Initialize API return status to success
221 x_return_status := FND_API.G_RET_STS_SUCCESS;
222 IF (p_local_org_id = G_GLOBAL_ORG_ID OR
223 p_local_org_id = -99 OR
224 l_global_org_id = -99) THEN
225 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_NO_ADP_GLBL_ORG');
226 RAISE FND_API.G_EXC_ERROR ;
227 ELSE
228 IF p_global_article_version_id IS NULL Then
229 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_INV_GLOB_ADP');
230 RAISE FND_API.G_EXC_ERROR ;
231 END IF;
232 OPEN l_adoption_csr (p_global_article_version_id, p_local_org_id);
233 FETCH l_adoption_csr INTO l_adoption_rec;
234 l_row_notfound := l_adoption_csr%NOTFOUND;
235 CLOSE l_adoption_csr;
236
237 -- Global Article Version must be valid for adoption should be global, approved and must be avilable for adoption
238 -- to the local org.
239
240 IF (l_row_notfound) THEN
241 IF (l_debug = 'Y') THEN
242 okc_debug.log('200: Adoption cursor row not found', 2);
243 END IF;
244 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_INV_GLOB_ADP');
245 RAISE FND_API.G_EXC_ERROR ;
246 END IF;
247
248 IF (nvl(p_adoption_type, 'INVALID') NOT IN ('ADOPTED', 'LOCALIZED')) THEN
249 IF (l_debug = 'Y') THEN
250 okc_debug.log('300: Adoption type is wrong', 2);
251 END IF;
252 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_INV_GLOB_ADP');
253 RAISE FND_API.G_EXC_ERROR ;
254 END IF;
255
256 IF (l_adoption_rec.article_status = 'ON_HOLD') THEN
257 IF (l_debug = 'Y') THEN
258 okc_debug.log('350: Invalid status to be adopted', 2);
259 END IF;
260 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_INV_STATUS_TO_ADOPT');
261 RAISE FND_API.G_EXC_ERROR ;
262 END IF;
263
264
265 OPEN l_latest_ver_adp_csr(l_adoption_rec.article_id, p_global_article_version_id, p_local_org_id);
266 FETCH l_latest_ver_adp_csr INTO l_latest_ver_adp_rec ;
267 l_adp_row_notfound := l_latest_ver_adp_csr%NOTFOUND;
268 CLOSE l_latest_ver_adp_csr;
269
270 OPEN l_lcz_article_id_csr(l_adoption_rec.article_id, p_local_org_id);
271 FETCH l_lcz_article_id_csr INTO l_lcz_article_id;
272 l_lcz_row_notfound := l_lcz_article_id_csr%NOTFOUND;
273 CLOSE l_lcz_article_id_csr;
274
275 x_earlier_local_version_id := l_latest_ver_adp_rec.article_version_id;
276
277 -- Check that the global article version does not have any other later version already localized.
278 -- Check if the adoption type is not same as previous adoptions for the same global article
279 IF (l_adp_row_notfound AND l_lcz_row_notfound) THEN
280 IF (l_debug = 'Y') THEN
281 okc_debug.log('200: Other version row not found', 2);
282 END IF;
283 l_never_adopted := TRUE;
284 ELSIF ( ( NOT l_adp_row_notfound AND p_adoption_type <> 'ADOPTED')
285 OR ( NOT l_lcz_row_notfound AND p_adoption_type <> 'LOCALIZED')) THEN
286 IF (l_debug = 'Y') THEN
287 okc_debug.log('400: Other version row different adoption', 2);
288 END IF;
289 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_DIFF_ADP_TYPE');
290 RAISE FND_API.G_EXC_ERROR ;
291 ELSIF ( NOT l_lcz_row_notfound ) THEN
292 -- localization flow
293 l_lcz_row_notfound := FALSE;
294 OPEN l_latest_ver_lcz_csr(l_lcz_article_id);
295 FETCH l_latest_ver_lcz_csr INTO l_latest_ver_lcz_rec ;
296 l_lcz_row_notfound := l_latest_ver_lcz_csr%NOTFOUND;
297 CLOSE l_latest_ver_lcz_csr ;
298
299 x_earlier_local_version_id := l_latest_ver_lcz_rec.article_version_id;
300 IF (l_lcz_row_notfound) THEN
301 IF (l_debug = 'Y') THEN
302 okc_debug.log('500: Localization Latest version not found', 2);
303 END IF;
304 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_INV_GLOB_ADP');
305 RAISE FND_API.G_EXC_ERROR;
306 END IF;
307
308 IF nvl(l_latest_ver_lcz_rec.adoption_status, 'DRAFT')
309 IN ('DRAFT','REJECTED','PENDING_APPROVAL') THEN
310 IF (l_debug = 'Y') THEN
311 okc_debug.log('600: Other version has adoption in Rejected/Draft/Pending Approval', 2);
312 END IF;
313 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_EXIST_DRAFT_LOCALIZED');
314 RAISE FND_API.G_EXC_ERROR ;
315 END IF;
316
317 ELSIF ( NOT l_adp_row_notfound) THEN
318 -- Adoption flow
319 IF (l_latest_ver_adp_rec.start_date > l_adoption_rec.start_date) THEN
320 IF (l_debug = 'Y') THEN
321 okc_debug.log('700: Other version has a later adoption', 2);
322 END IF;
323 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_LATER_GLOB_ADP_EXIST');
324 RAISE FND_API.G_EXC_ERROR;
325 END IF;
326
327 IF nvl(l_latest_ver_adp_rec.adoption_status, 'REJECTED')
328 IN ('PENDING_APPROVAL') THEN
329 IF (l_debug = 'Y') THEN
330 okc_debug.log('800: Other version has adoption in Pending Approval', 2);
331 END IF;
332 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_EXIST_PENDING_ADOPTED');
333 RAISE FND_API.G_EXC_ERROR ;
334 END IF;
335 END IF; -- IF (l_adp_row_notfound AND l_lcz_row_notfound)
336
337 --Check whether adopted article title exists in local org
338 IF (l_never_adopted AND p_adoption_type = 'ADOPTED') THEN
339
340 OPEN l_unq_local_title_csr ( p_local_org_id,
341 l_adoption_rec.article_id,
342 l_global_org_id) ;
343 FETCH l_unq_local_title_csr INTO l_local_article_title;
344 l_row_found := l_unq_local_title_csr%FOUND;
345 CLOSE l_unq_local_title_csr;
346
347 IF (l_row_found) THEN
348 IF (l_debug = 'Y') THEN
349 okc_debug.log('1000: Duplicate title found in local org', 2);
350 END IF;
351 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_DUP_TITLE_ADP_ORG');
352 RAISE FND_API.G_EXC_ERROR ;
353 END IF;
354
355 END IF;
356
357 END IF; -- IF( p_local_org_id = G_GLOBAL_ORG_ID OR p_local_org_id = -99 OR l_global_org_id = -99 )
358
359 IF (l_debug = 'Y') THEN
360 okc_debug.log('2000: Leaving check adoption', 2);
361 END IF;
362 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
363
364 EXCEPTION
365 WHEN FND_API.G_EXC_ERROR THEN
366 IF (l_debug = 'Y') THEN
367 okc_debug.log('3000: Leaving Check_Adoption: OKC_API.G_EXCEPTION_ERROR Exception', 2);
368 END IF;
369 x_return_status := G_RET_STS_ERROR ;
370 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
371
372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
373 IF (l_debug = 'Y') THEN
374 okc_debug.log('4000: Leaving Check_Adoption: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
375 END IF;
376 IF l_latest_ver_adp_csr%ISOPEN THEN
377 CLOSE l_latest_ver_adp_csr;
378 END IF;
379 IF l_lcz_article_id_csr%ISOPEN THEN
380 CLOSE l_lcz_article_id_csr;
381 END IF;
382 IF l_latest_ver_lcz_csr%ISOPEN THEN
383 CLOSE l_latest_ver_lcz_csr;
384 END IF;
385 IF l_adoption_csr%ISOPEN THEN
386 CLOSE l_adoption_csr;
387 END IF;
388 IF l_unq_local_title_csr%ISOPEN THEN
389 CLOSE l_unq_local_title_csr;
390 END IF;
391 x_return_status := G_RET_STS_UNEXP_ERROR ;
392 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,p_encoded=> 'F', p_data => x_msg_data );
393
394 WHEN OTHERS THEN
395 IF (l_debug = 'Y') THEN
396 okc_debug.log('5000: Leaving Check_Adoption because of EXCEPTION: '||sqlerrm, 2);
397 END IF;
398 IF l_latest_ver_adp_csr%ISOPEN THEN
399 CLOSE l_latest_ver_adp_csr;
400 END IF;
401 IF l_lcz_article_id_csr%ISOPEN THEN
402 CLOSE l_lcz_article_id_csr;
403 END IF;
404 IF l_latest_ver_lcz_csr%ISOPEN THEN
405 CLOSE l_latest_ver_lcz_csr;
406 END IF;
407 IF l_adoption_csr%ISOPEN THEN
408 CLOSE l_adoption_csr;
409 END IF;
410 IF l_unq_local_title_csr%ISOPEN THEN
411 CLOSE l_unq_local_title_csr;
412 END IF;
413
414 x_return_status := G_RET_STS_UNEXP_ERROR ;
415 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,p_encoded=> 'F', p_data => x_msg_data );
416
417 END check_adoption_details;
418
419
420 PROCEDURE delete_local_adoption_details(
421 p_api_version IN NUMBER,
422 p_init_msg_list IN VARCHAR2 ,
423
424 x_return_status OUT NOCOPY VARCHAR2,
425 x_msg_count OUT NOCOPY NUMBER,
426 x_msg_data OUT NOCOPY VARCHAR2,
427
428 p_only_local_version IN VARCHAR2,
429 p_local_article_version_id IN NUMBER,
430 p_local_org_id IN NUMBER
431 ) IS
432 l_api_version CONSTANT NUMBER := 1;
433 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_adoption';
434 l_global_article_version_id NUMBER := NULL;
435 l_local_article_version_id NUMBER;
436 l_row_notfound BOOLEAN := FALSE;
437 l_delete_adoption VARCHAR2(1) := 'T';
438
439 -- Cursor to find out if the current version is created as a LOCALIZED from a
440 -- new global article version or if it is simply a new version being created
441 -- from an existing local version
442 -- This is because when we create a new version we inherit the adoption details -- of the earlier version resulting in
443 -- multiple occurences of the global version
444
445 CURSOR l_other_version_csr (cp_article_version_id IN NUMBER,
446 cp_local_org_id IN NUMBER) IS
447
448 SELECT 'T' , global_article_version_id FROM OKC_ARTICLE_ADOPTIONS A
449 WHERE local_article_version_id = cp_article_version_id
450 AND local_org_id = cp_local_org_id
451 AND EXISTS
452 (SELECT '1' FROM OKC_ARTICLE_ADOPTIONS B
453 WHERE B.GLOBAL_ARTICLE_VERSION_ID = A.GLOBAL_ARTICLE_VERSION_ID
454 AND B.LOCAL_ARTICLE_VERSION_ID <> A.LOCAL_ARTICLE_VERSION_ID
455 AND B.LOCAL_ORG_ID = A.LOCAL_ORG_ID);
456
457
458 BEGIN
459
460 IF (l_debug = 'Y') THEN
461 okc_debug.log('100: Entered delete_adoption', 2);
462 END IF;
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464
465 -- Standard Start of API savepoint
466 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
468 END IF;
469 -- Initialize message list if p_init_msg_list is set to TRUE.
470 IF FND_API.to_Boolean( p_init_msg_list ) THEN
471 FND_MSG_PUB.initialize;
472 END IF;
473 -- Initialize API return status to success
474 -- If a local article version is deleted the attached global article version
475 -- becomes available in the adoption table with adoption type as "AVAILABLE".
476 -- However this is only in case of LOCALIZATION of local article versions.
477 -- In the case of a local article version being created as a new version
478 -- from a prior version we delete the adoption row for this version.
479
480 IF (p_local_org_id = G_GLOBAL_ORG_ID OR
481 p_local_org_id = -99 OR
482 p_local_org_id = -99 ) THEN
483 NULL;
484 ELSE
485 l_delete_adoption := 'T';
486
487 IF p_only_local_version = 'F' Then
488
489 -- Cursor to find out if the current version is created as a LOCALIZED from a
490 -- new global article version or if it is simply a new version being created
491 -- from an existing local version
492
493 OPEN l_other_version_csr (p_local_article_version_id, p_local_org_id);
494 FETCH l_other_version_csr into l_delete_adoption, l_global_article_version_id;
495 l_row_notfound := l_other_version_csr%NOTFOUND;
496 CLOSE l_other_version_csr;
497 IF l_row_notfound THEN
498 l_delete_adoption := 'F';
499 l_global_article_version_id := NULL; -- since we could not figure it out yet.
500 END IF;
501 ELSE
502 l_delete_adoption := 'F';
503 END IF;
504
505 -- Do not know the global article version id..
506 -- Simple API will figure it out if we pass the global article version id as NULL
507 --dbms_output.put_line('In Adoption:' ||l_delete_adoption||'*'||p_only_local_version);
508
509 IF l_delete_adoption = 'F' Then
510 OKC_ARTICLE_ADOPTIONS_PVT.update_row(
511 x_return_status => x_return_status,
512 p_global_article_version_id => l_global_article_version_id,
513 p_adoption_type => 'AVAILABLE',
514 p_local_org_id => p_local_org_id,
515 p_orig_local_version_id => p_local_article_version_id,
516 p_new_local_version_id => NULL,
517 p_adoption_status => OKC_API.G_MISS_CHAR,
518 p_object_version_number => NULL
519 );
520 ELSIF l_delete_adoption = 'T' Then
521 OKC_ARTICLE_ADOPTIONS_PVT.delete_row(
522 x_return_status => x_return_status,
523 p_global_article_version_id => l_global_article_version_id,
524 p_local_org_id => p_local_org_id,
525 p_local_article_version_id => p_local_article_version_id,
526 p_object_version_number => NULL
527 );
528 ELSE
529 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_ADP_NOT_FOUND');
530 RAISE FND_API.G_EXC_ERROR ;
531 END IF;
532 END IF;
533
534 IF (l_debug = 'Y') THEN
535 okc_debug.log('200: Leaving check adoption', 2);
536 END IF;
537
538 EXCEPTION
539 WHEN FND_API.G_EXC_ERROR THEN
540 IF (l_debug = 'Y') THEN
541 okc_debug.log('300: Leaving delete_Adoption: OKC_API.G_EXCEPTION_ERROR Exception', 2);
542 END IF;
543 x_return_status := G_RET_STS_ERROR ;
544
545 WHEN OTHERS THEN
546 IF (l_debug = 'Y') THEN
547 okc_debug.log('500: Leaving delete_Adoption because of EXCEPTION: '||sqlerrm, 2);
548 END IF;
549 IF l_other_version_csr%ISOPEN THEN
550 CLOSE l_other_version_csr;
551 END IF;
552 x_return_status := G_RET_STS_UNEXP_ERROR ;
553
554 END delete_local_adoption_details;
555
556 PROCEDURE create_local_adoption_details(
557 p_api_version IN NUMBER,
558 p_init_msg_list IN VARCHAR2 ,
559 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
560
561 x_return_status OUT NOCOPY VARCHAR2,
562 x_msg_count OUT NOCOPY NUMBER,
563 x_msg_data OUT NOCOPY VARCHAR2,
564 x_adoption_type OUT NOCOPY VARCHAR2,
565
566 p_article_status IN VARCHAR2,
567 p_earlier_local_version_id IN NUMBER,
568 p_local_article_version_id IN NUMBER,
569 p_global_article_version_id IN NUMBER,
570 p_local_org_id IN NUMBER
571 ) IS
572 l_api_version CONSTANT NUMBER := 1;
573 l_api_name CONSTANT VARCHAR2(30) := 'g_create_adoption';
574 l_local_article_version_id NUMBER;
575 l_global_article_version_id NUMBER := -99;
576 l_global_version_id_out NUMBER;
577 l_article_id NUMBER;
578 l_local_org_id NUMBER;
579 l_adoption_type OKC_ARTICLE_ADOPTIONS.ADOPTION_TYPE%TYPE;
580 l_adoption_status OKC_ARTICLE_ADOPTIONS.ADOPTION_STATUS%TYPE;
581 l_start_date DATE;
582 l_end_date DATE;
583 l_row_notfound BOOLEAN := FALSE;
584 CURSOR l_adoption_csr (cp_local_article_version_id IN NUMBER,
585 cp_local_org_id IN NUMBER) IS
586 SELECT global_article_version_id, adoption_type FROM
587 OKC_ARTICLE_ADOPTIONS
588 WHERE local_article_version_id = cp_local_article_version_id
589 AND local_org_id = cp_local_org_id;
590
591 l_adoption_rec l_adoption_csr%ROWTYPE;
592
593 BEGIN
594
595 IF (l_debug = 'Y') THEN
596 okc_debug.log('100: Entered create_adoption', 2);
597 END IF;
598
599 -- Standard Start of API savepoint
600 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
602 END IF;
603 -- Initialize message list if p_init_msg_list is set to TRUE.
604 IF FND_API.to_Boolean( p_init_msg_list ) THEN
605 FND_MSG_PUB.initialize;
606 END IF;
607 -- Initialize API return status to success
608 x_return_status := FND_API.G_RET_STS_SUCCESS;
609 x_adoption_type := NULL;
610 -- dbms_output.put_line(p_earlier_local_version_id||'*'||p_global_article_version_id);
611 IF (p_local_org_id = G_GLOBAL_ORG_ID OR
612 p_local_org_id = -99 OR
613 G_GLOBAL_ORG_ID = -99) THEN
614 NULL;
615 ELSE
616 -- p_global_article_version_id should be passed for LOCALIZE option of creating
617 -- a new local article version.
618 IF p_earlier_local_version_id IS NOT NULL THEN
619 OPEN l_adoption_csr (p_earlier_local_version_id, p_local_org_id);
620 FETCH l_adoption_csr INTO l_adoption_rec;
621 l_row_notfound := l_adoption_csr%NOTFOUND;
622 CLOSE l_adoption_csr;
623
624 -- Earlier version of the local article exists but has not been adopted ...
625 -- ERROR
626
627 IF (l_row_notfound) THEN
628 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_EARLIER_NOT_ADPT');
629 RAISE FND_API.G_EXC_ERROR ;
630 END IF;
631 /*
632 -- For LOCALIZE option the same global article version for a local article
633 -- version should not be the same for the earlier version of the same local
634 -- article. In other words, one LOCALIZED version cannot have multiple global
635 -- article versions .
636 -- However, this is not true if the new article version of the local article
637 -- is created manually as a New Version - in that case we copy the global
638 -- article version_id from the previous adoption.
639
640 IF p_global_article_version_id IS NOT NULL AND
641 p_global_article_version_id = l_adoption_rec.global_article_version_id THEN
642 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_MULTI_GLOB_ADPT');
643 RAISE FND_API.G_EXC_ERROR ;
644 END IF;
645 */
646 -- For LOCALIZE option the earlier local article version id has a different
647 -- adoption type i.e. ADOPTED (as is) ... ERROR
648
649 IF l_adoption_rec.adoption_type <> 'LOCALIZED' THEN
650 OKC_API.SET_MESSAGE(G_APP_NAME, 'OKC_ART_DIFF_ADP_TYPE');
651 RAISE FND_API.G_EXC_ERROR ;
652 END IF;
653 l_global_article_version_id := l_adoption_rec.global_article_version_id;
654 ELSE -- earlier local version id IS NULL
655 l_global_article_version_id := p_global_article_version_id;
656 END IF;
657 -- Update the available adoption row from "AVAILABLE" to "LOCALIZED"
658 -- dbms_output.put_line('GLOBAL VERSION ID:'||l_global_article_version_id);
659 -- dbms_output.put_line('LOCAL VERSION ID:'||p_local_article_version_id);
660 -- dbms_output.put_line('EARLIER VERSION ID:'||p_earlier_local_version_id);
661 IF p_global_article_version_id IS NOT NULL THEN
662 OKC_ARTICLE_ADOPTIONS_PVT.update_row(
663 p_validation_level => p_validation_level,
664 x_return_status => x_return_status,
665 p_global_article_version_id => p_global_article_version_id,
666 p_adoption_type => 'LOCALIZED',
667 p_local_org_id => p_local_org_id,
668 p_orig_local_version_id => NULL,
669 p_new_local_version_id => p_local_article_version_id,
670 p_adoption_status => nvl(p_article_status,'DRAFT'),
671 p_object_version_number => NULL
672 );
673 -- dbms_output.put_line('RETURN:'||x_return_status);
674
675 --------------------------------------------
676 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
678 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
679 RAISE FND_API.G_EXC_ERROR ;
680 END IF;
681 --------------------------------------------
682 x_adoption_type := 'LOCALIZED';
683 return;
684 END IF;
685
686 IF l_global_article_version_id IS NOT NULL AND
687 l_global_article_version_id <> -99 THEN
688
689 IF p_earlier_local_version_id IS NULL Then
690
691 OKC_ARTICLE_ADOPTIONS_PVT.update_row(
692 p_validation_level => p_validation_level,
693 x_return_status => x_return_status,
694 p_global_article_version_id => l_global_article_version_id,
695 p_adoption_type => 'LOCALIZED',
696 p_local_org_id => p_local_org_id,
697 p_orig_local_version_id => NULL,
698 p_new_local_version_id => p_local_article_version_id,
699 p_adoption_status => nvl(p_article_status,'DRAFT'),
700 p_object_version_number => NULL
701 );
702 x_adoption_type := 'LOCALIZED';
703
704 ELSE
705
706 --dbms_output.put_line('Creating Adoptions');
707 -- Brand new Article/version being created from "Create New version" of an
708 -- existing article version which has been LOCALIZED. This will copy the
709 -- adoption row for this new version based on global and org details from
710 -- the previous version.
711
712
713 OKC_ARTICLE_ADOPTIONS_PVT.INSERT_ROW
714 (
715 p_validation_level => p_validation_level,
716 x_return_status => x_return_status,
717 p_global_article_version_id=> l_global_article_version_id,
718 p_adoption_type => 'LOCALIZED',
719 p_local_org_id => p_local_org_id,
720 p_local_article_version_id => p_local_article_version_id,
721 p_adoption_status => nvl(p_article_status,'DRAFT'),
722 x_global_article_version_id => l_global_version_id_out,
723 x_local_org_id => l_local_org_id,
724 x_local_article_version_id => l_local_article_version_id
725 );
726 x_adoption_type := 'LOCALIZED';
727 END IF; -- p_earlier local article version id is NULL
728 END IF; -- l_global_article_version_id is NULL
729 END IF; -- local org id = global org id
730 --------------------------------------------
731 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
732 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
733 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
734 RAISE FND_API.G_EXC_ERROR ;
735 END IF;
736 --------------------------------------------
737
738 IF (l_debug = 'Y') THEN
739 okc_debug.log('200: Leaving check adoption', 2);
740 END IF;
741
742 EXCEPTION
743 WHEN FND_API.G_EXC_ERROR THEN
744 IF (l_debug = 'Y') THEN
745 okc_debug.log('300: Leaving create_local_adoption_details: OKC_API.G_EXCEPTION_ERROR Exception', 2);
746 END IF;
747 x_return_status := G_RET_STS_ERROR ;
748
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 IF (l_debug = 'Y') THEN
751 okc_debug.log('400: Leaving create_local_adoption_details: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
752 END IF;
753 IF l_adoption_csr%ISOPEN THEN
754 CLOSE l_adoption_csr;
755 END IF;
756 x_return_status := G_RET_STS_UNEXP_ERROR ;
757
758 WHEN OTHERS THEN
759 IF (l_debug = 'Y') THEN
760 okc_debug.log('500: Leaving create_local_adoption_details because of EXCEPTION: '||sqlerrm, 2);
761 END IF;
762 IF l_adoption_csr%ISOPEN THEN
763 CLOSE l_adoption_csr;
764 END IF;
765
766 x_return_status := G_RET_STS_UNEXP_ERROR ;
767
768 END create_local_adoption_details;
769
770 PROCEDURE AUTO_ADOPT_ARTICLES
771 (
772 p_api_version IN NUMBER,
773 p_init_msg_list IN VARCHAR2 ,
774 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
775 p_commit IN VARCHAR2 := FND_API.G_FALSE,
776
777 x_return_status OUT NOCOPY VARCHAR2,
778 x_msg_count OUT NOCOPY NUMBER,
779 x_msg_data OUT NOCOPY VARCHAR2,
780 p_relationship_yn IN VARCHAR2 := 'N',
781 p_adoption_yn IN VARCHAR2 := 'N',
782 p_fetchsize IN NUMBER,
783 p_global_article_id IN NUMBER,
784 p_global_article_version_id IN NUMBER
785 ) IS
786 l_api_version CONSTANT NUMBER := 1;
787 l_api_name CONSTANT VARCHAR2(30) := 'g_auto_adoption';
788 l_dummy VARCHAR2(1) := '?';
789 l_rowfound BOOLEAN := FALSE;
790 l_local_article_version_id NUMBER;
791 i NUMBER := 0;
792 j NUMBER := 0;
793 l_global_version_id_out NUMBER;
794 l_article_id NUMBER;
795 l_local_org_id NUMBER;
796 l_return_status VARCHAR2(1);
797 l_local_article_title OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE;
798 l_adoption_type OKC_ARTICLE_ADOPTIONS.ADOPTION_TYPE%TYPE;
799 l_adoption_status OKC_ARTICLE_ADOPTIONS.ADOPTION_STATUS%TYPE;
800 l_GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
801 TYPE l_org_id_list IS TABLE OF HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
802 TYPE l_adoption_type_list IS TABLE OF FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE INDEX BY BINARY_INTEGER;
803 TYPE l_notifier_list IS TABLE OF HR_ORGANIZATION_INFORMATION.ORG_INFORMATION2%TYPE INDEX BY BINARY_INTEGER;
804 TYPE l_adoption_status_list IS TABLE OF OKC_ARTICLE_ADOPTIONS.ADOPTION_STATUS%TYPE INDEX BY BINARY_INTEGER;
805 TYPE l_adp_record_status_list IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
806 TYPE l_record_status_list IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
807 TYPE l_article_number_list IS TABLE OF OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE INDEX BY BINARY_INTEGER ;
808 TYPE l_source_article_id_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.SOURCE_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
809 TYPE l_target_article_id_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.TARGET_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
810 TYPE l_relationship_type_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.RELATIONSHIP_TYPE%TYPE INDEX BY BINARY_INTEGER ;
811
812 l_article_number_tbl l_article_number_list ;
813 l_source_article_id_tbl l_source_article_id_list ;
814 l_target_article_id_tbl l_target_article_id_list ;
815 l_relationship_type_tbl l_relationship_type_list ;
816
817 l_org_id_tbl l_org_id_list;
818 l_adoption_type_tbl l_adoption_type_list;
819 l_notifier_tbl l_notifier_list;
820 l_adoption_status_tbl l_adoption_status_list;
821 l_adp_record_status_tbl l_adp_record_status_list;
822
823 CURSOR l_org_info_csr (cp_global_org_id IN NUMBER) IS
824 SELECT ORGANIZATION_ID,
825 decode(nvl(ORG_INFORMATION1,'N'),'N','AVAILABLE','Y','ADOPTED') ADOPTION_TYPE ,
826 ORG_INFORMATION2
827
828 FROM HR_ORGANIZATION_INFORMATION
829 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
830 AND ORGANIZATION_ID <> cp_global_org_id;
831
832 CURSOR l_adoptions_csr (cp_global_version_id IN NUMBER, cp_local_org_id IN NUMBER) IS
833 SELECT '1'
834 FROM OKC_ARTICLE_ADOPTIONS
835 WHERE GLOBAL_ARTICLE_VERSION_ID = cp_global_version_id
836 AND LOCAL_ORG_ID = cp_local_org_id
837 AND rownum < 2;
838
839 CURSOR l_relationship_csr (cp_local_org_id IN NUMBER,
840 cp_global_article_id IN NUMBER,
841 cp_global_org_id IN NUMBER) IS
842 SELECT A.ARTICLE_NUMBER,
843 SOURCE_ARTICLE_ID,
844 TARGET_ARTICLE_ID,
845 RELATIONSHIP_TYPE
846 FROM OKC_ARTICLE_RELATNS_ALL R,
847 OKC_ARTICLES_ALL A
848 WHERE R.SOURCE_ARTICLE_ID = cp_global_article_id
849 AND R.TARGET_ARTICLE_ID = A.ARTICLE_ID
850 AND R.ORG_ID = cp_global_org_id
851 AND EXISTS
852 (SELECT 1 FROM OKC_ARTICLE_VERSIONS V
853 WHERE V.ARTICLE_ID = R.TARGET_ARTICLE_ID
854 AND V.GLOBAL_YN = 'Y'
855 AND V.ARTICLE_STATUS = 'APPROVED'
856 AND NVL(V.END_DATE,SYSDATE + 1) > SYSDATE
857 )
858 AND EXISTS
859 (SELECT 1 FROM OKC_ARTICLE_VERSIONS V1
860 WHERE V1.ARTICLE_ID = R.SOURCE_ARTICLE_ID
861 AND V1.GLOBAL_YN = 'Y'
862 AND V1.ARTICLE_STATUS = 'APPROVED'
863 AND NVL(V1.END_DATE,SYSDATE + 1) > SYSDATE
864 )
865 AND NOT EXISTS
866 (
867 SELECT '1'
868 FROM OKC_ARTICLE_RELATNS_ALL R1
869 WHERE R1.SOURCE_ARTICLE_ID = R.SOURCE_ARTICLE_ID AND
870 R1.TARGET_ARTICLE_ID = R.TARGET_ARTICLE_ID AND
871 R1.RELATIONSHIP_TYPE = R.RELATIONSHIP_TYPE AND
872 R1.ORG_ID = cp_local_org_id
873 );
874
875 CURSOR l_unq_local_title_csr (cp_local_org_id IN NUMBER,
876 cp_global_article_id IN NUMBER,
877 cp_global_org_id IN NUMBER) IS
878 SELECT 1
879 FROM OKC_ARTICLES_ALL ARTL
880 WHERE ARTL.ORG_ID = cp_local_org_id
881 AND ARTL.STANDARD_YN= 'Y'
882 AND ARTL.ARTICLE_TITLE = ( SELECT ARTG.ARTICLE_TITLE
883 FROM OKC_ARTICLES_ALL ARTG
884 WHERE ARTG.ARTICLE_ID = cp_global_article_id
885 AND ARTG.ORG_ID = cp_global_org_id
886 AND ARTG.STANDARD_YN='Y');
887 l_user_id NUMBER := FND_GLOBAL.USER_ID;
888 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
889
890 BEGIN
891 IF (l_debug = 'Y') THEN
892 okc_debug.log('100: Entered create_adoption', 2);
893 END IF;
894
895 -- Standard Start of API savepoint
896 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 END IF;
899 -- Initialize message list if p_init_msg_list is set to TRUE.
900 IF FND_API.to_Boolean( p_init_msg_list ) THEN
901 FND_MSG_PUB.initialize;
902 END IF;
903 -- Initialize API return status to success
904 x_return_status := FND_API.G_RET_STS_SUCCESS;
905 l_return_status := FND_API.G_RET_STS_SUCCESS;
906 --dbms_output.put_line('Global org is: '|| l_global_org_id);
907 if p_adoption_yn = 'N' and p_relationship_yn = 'N' Then
908 return;
909 end if;
910 OPEN l_org_info_csr (l_global_org_id);
911 LOOP
912 FETCH l_org_info_csr BULK COLLECT INTO l_org_id_tbl, l_adoption_type_tbl, l_notifier_tbl LIMIT p_fetchsize;
913 i := 0;
914 EXIT WHEN l_org_id_tbl.COUNT = 0;
915 --dbms_output.put_line('Cursor fetched rows: '||l_org_id_tbl.COUNT);
916
917 FOR i IN l_org_id_tbl.FIRST..l_org_id_tbl.LAST LOOP
918 BEGIN
919 l_adp_record_status_tbl(i) := 'S';
920 --dbms_output.put_line('For Org: '||p_relationship_yn||'*'||l_org_id_tbl(i)||'*'|| l_adoption_type_tbl(i));
921 if p_adoption_yn = 'Y' THEN
922 l_dummy := '?';
923 l_rowfound := FALSE;
924
925 -- Check adoption row already exists. Ususally this should not happen but will be if an org switches to
926 -- Auto Adoption
927
928 Open l_adoptions_csr (p_global_article_version_id, l_org_id_tbl(i));
929 FETCH l_adoptions_csr INTO l_dummy;
930 l_rowfound := l_adoptions_csr%FOUND;
931 CLOSE l_adoptions_csr;
932
933 if (l_rowfound) THEN
934 l_adp_record_status_tbl(i) := 'E';
935 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
936 p_msg_name => 'OKC_ART_ADP_ALREADY_EXIST',
937 p_token1 => 'ORG_ID',
938 p_token1_value => l_org_id_tbl(i));
939 l_return_status := G_RET_STS_ERROR;
940 else
941 -- ibyon 12/16/03 bug 3302792
942 -- Add duplicate check for adopted article
943 -- If article title is found in the local org then
944 -- adoption type becomes 'AVAILABLE'
945 IF l_adoption_type_tbl(i) = 'ADOPTED' THEN
946
947 OPEN l_unq_local_title_csr ( l_org_id_tbl(i),
948 p_global_article_id,
949 l_global_org_id);
950 FETCH l_unq_local_title_csr INTO l_local_article_title;
951 l_rowfound := l_unq_local_title_csr%FOUND;
952 CLOSE l_unq_local_title_csr;
953
954 IF (l_rowfound) THEN
955 IF (l_debug = 'Y') THEN
956 okc_debug.log('200: AUTO ADOPTION cannot be done because duplicate article found in the org '|| l_org_id_tbl(i), 2);
957 END IF;
958 l_adoption_type_tbl(i) := 'AVAILABLE';
959 l_adoption_status_tbl(i) := NULL;
960 ELSE
961 l_adoption_status_tbl(i) := 'APPROVED';
962 END IF;
963 ELSE
964 l_adoption_status_tbl(i) := NULL;
965 END IF;
966
967 l_adp_record_status_tbl(i) := 'S';
968
969 end if; -- end of if l_rowfound
970 end if; -- end of if p_adoption_yn =Y
971 if p_relationship_yn = 'Y' and
972 nvl(l_adp_record_status_tbl(i),'S') = 'S' and
973 l_adoption_type_tbl(i) = 'ADOPTED' Then
974 OPEN l_relationship_csr (l_org_id_tbl(i), p_global_article_id, l_global_org_id);
975 LOOP
976 FETCH l_relationship_csr BULK COLLECT INTO l_article_number_tbl,
977 l_source_article_id_tbl,
978 l_target_article_id_tbl,
979 l_relationship_type_tbl
980 LIMIT p_fetchsize;
981 EXIT WHEN l_article_number_tbl.COUNT = 0;
982
983 j := 0;
984 FORALL j IN l_article_number_tbl.FIRST..l_article_number_tbl.LAST
985 INSERT INTO OKC_ARTICLE_RELATNS_ALL
986 (
987 SOURCE_ARTICLE_ID,
988 TARGET_ARTICLE_ID,
989 ORG_ID,
990 RELATIONSHIP_TYPE,
991 OBJECT_VERSION_NUMBER,
992 CREATED_BY,
993 CREATION_DATE,
994 LAST_UPDATED_BY,
995 LAST_UPDATE_LOGIN,
996 LAST_UPDATE_DATE
997 )
998 VALUES
999 (
1000 l_source_article_id_tbl(j),
1001 l_target_article_id_tbl(j),
1002 l_org_id_tbl(i),
1003 l_relationship_type_tbl(j),
1004 1.0,
1005 l_User_Id,
1006 sysdate,
1007 l_User_Id,
1008 l_login_Id,
1009 sysdate
1010 );
1011
1012 -- Revert the target and source article ids.
1013
1014 j := 0;
1015 FORALL j IN l_article_number_tbl.FIRST..l_article_number_tbl.LAST
1016 INSERT INTO OKC_ARTICLE_RELATNS_ALL
1017 (
1018 SOURCE_ARTICLE_ID,
1019 TARGET_ARTICLE_ID,
1020 ORG_ID,
1021 RELATIONSHIP_TYPE,
1022 OBJECT_VERSION_NUMBER,
1023 CREATED_BY,
1024 CREATION_DATE,
1025 LAST_UPDATED_BY,
1026 LAST_UPDATE_LOGIN,
1027 LAST_UPDATE_DATE
1028 )
1029 VALUES
1030 (
1031 l_target_article_id_tbl(j),
1032 l_source_article_id_tbl(j),
1033 l_org_id_tbl(i),
1034 l_relationship_type_tbl(j),
1035 1.0,
1036 l_User_Id,
1037 sysdate,
1038 l_User_Id,
1039 l_Login_Id,
1040 sysdate);
1041 l_target_article_id_tbl.DELETE;
1042 l_source_article_id_tbl.DELETE;
1043 l_relationship_type_tbl.DELETE;
1044 EXIT WHEN l_relationship_csr%NOTFOUND;
1045 END LOOP; -- relationship csr fetch
1046 CLOSE l_relationship_csr;
1047 end if; -- relationship_yn = Y
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 IF (l_debug = 'Y') THEN
1051 okc_debug.log('500: Leaving Auto_Adoption because of EXCEPTION: '||sqlerrm, 2);
1052 END IF;
1053 IF l_org_info_csr%ISOPEN THEN
1054 CLOSE l_org_info_csr;
1055 END IF;
1056 IF l_relationship_csr%ISOPEN THEN
1057 CLOSE l_relationship_csr;
1058 END IF;
1059 IF l_unq_local_title_csr%ISOPEN THEN
1060 CLOSE l_unq_local_title_csr;
1061 END IF;
1062 l_adp_record_status_tbl(i) := 'U';
1063 l_return_status := G_RET_STS_UNEXP_ERROR ;
1064 x_return_status := G_RET_STS_UNEXP_ERROR ;
1065 exit;
1066 END;
1067 END LOOP; -- for i in l_org_id_tbl..
1068 i := 0;
1069 IF l_return_status = FND_API.G_RET_STS_SUCCESS Then
1070 IF p_adoption_yn = 'Y' AND l_org_id_tbl.COUNT > 0 Then
1071 FORALL i in l_org_id_tbl.FIRST .. l_org_id_tbl.LAST
1072 INSERT INTO OKC_ARTICLE_ADOPTIONS
1073 (
1074 GLOBAL_ARTICLE_VERSION_ID,
1075 ADOPTION_TYPE,
1076 LOCAL_ORG_ID,
1077 ADOPTION_STATUS,
1078 LOCAL_ARTICLE_VERSION_ID,
1079 OBJECT_VERSION_NUMBER,
1080 CREATED_BY,
1081 CREATION_DATE,
1082 LAST_UPDATED_BY,
1083 LAST_UPDATE_LOGIN,
1084 LAST_UPDATE_DATE
1085 )
1086 SELECT
1087 p_global_article_version_id,
1088 l_adoption_type_tbl(i),
1089 l_org_id_tbl(i),
1090 l_adoption_status_tbl(i),
1091 NULL,
1092 1.0,
1093 l_User_Id,
1094 sysdate,
1095 l_User_Id,
1096 l_Login_Id,
1097 sysdate
1098 FROM DUAL
1099 WHERE l_adp_record_status_tbl(i) = 'S';
1100
1101 l_org_id_tbl.DELETE;
1102 l_adoption_type_tbl.DELETE;
1103 l_notifier_tbl.DELETE;
1104 l_adoption_status_tbl.DELETE;
1105 l_adp_record_status_tbl.DELETE;
1106 END IF; -- p_adoption_yn = Y
1107 END IF; -- l_return_status = S
1108 EXIT WHEN l_org_info_csr%NOTFOUND;
1109 END LOOP; -- main cursor loop
1110 CLOSE l_org_info_csr;
1111 if l_return_status = 'E' THEN
1112 x_return_status := 'W';
1113 else
1114 x_return_status := l_return_status;
1115 end if;
1116 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1117 return;
1118 END IF;
1119 -- Standard check of p_commit
1120 IF FND_API.To_Boolean( p_commit ) THEN
1121 COMMIT WORK;
1122 END IF;
1123 -- Standard call to get message count and if count is 1, get message info.
1124 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,p_encoded=> 'F', p_data => x_msg_data );
1125
1126 IF (l_debug = 'Y') THEN
1127 okc_debug.log('200: Leaving check adoption', 2);
1128 END IF;
1129
1130 EXCEPTION
1131 WHEN FND_API.G_EXC_ERROR THEN
1132 IF (l_debug = 'Y') THEN
1133 okc_debug.log('300: Leaving Auto_Adoption: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1134 END IF;
1135 x_return_status := G_RET_STS_ERROR ;
1136
1137 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1138 IF (l_debug = 'Y') THEN
1139 okc_debug.log('400: Leaving Auto_Adoption: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1140 END IF;
1141 IF l_org_info_csr%ISOPEN THEN
1142 CLOSE l_org_info_csr;
1143 END IF;
1144 IF l_relationship_csr%ISOPEN THEN
1145 CLOSE l_relationship_csr;
1146 END IF;
1147 IF l_unq_local_title_csr%ISOPEN THEN
1148 CLOSE l_unq_local_title_csr;
1149 END IF;
1150 x_return_status := G_RET_STS_UNEXP_ERROR ;
1151
1152 WHEN OTHERS THEN
1153 IF (l_debug = 'Y') THEN
1154 okc_debug.log('500: Leaving Auto_Adoption because of EXCEPTION: '||sqlerrm, 2);
1155 END IF;
1156 IF l_org_info_csr%ISOPEN THEN
1157 CLOSE l_org_info_csr;
1158 END IF;
1159 IF l_relationship_csr%ISOPEN THEN
1160 CLOSE l_relationship_csr;
1161 END IF;
1162 IF l_unq_local_title_csr%ISOPEN THEN
1163 CLOSE l_unq_local_title_csr;
1164 END IF;
1165 x_return_status := G_RET_STS_UNEXP_ERROR ;
1166 END AUTO_ADOPT_ARTICLES;
1167
1168 -- The following procedure is a concurrent job that will be run if a new org is added and hence there is a
1169 -- need to create autoadoption rows for the currently active global article versions
1170
1171 PROCEDURE AUTO_ADOPT_NEWORG
1172 (errbuf OUT NOCOPY VARCHAR2,
1173 retcode OUT NOCOPY VARCHAR2,
1174 p_org_id IN NUMBER ,
1175 p_fetchsize IN NUMBER
1176 ) IS
1177
1178 l_api_name CONSTANT VARCHAR2(30) := 'g_auto_adoption_neworg';
1179 l_dummy VARCHAR2(1) := '?';
1180 l_row_notfound BOOLEAN := FALSE;
1181 l_local_article_version_id NUMBER;
1182 l_count_ver_adopted NUMBER;
1183 l_count_ver_available NUMBER;
1184
1185 i NUMBER := 0;
1186 j NUMBER := 0;
1187 l_article_id NUMBER;
1188 l_return_status VARCHAR2(1);
1189 l_adoption_type OKC_ARTICLE_ADOPTIONS.ADOPTION_TYPE%TYPE;
1190 l_prev_adoption_type OKC_ARTICLE_ADOPTIONS.ADOPTION_TYPE%TYPE;
1191 l_adoption_status OKC_ARTICLE_ADOPTIONS.ADOPTION_STATUS%TYPE;
1192 l_adoption_type_meaning FND_LOOKUPS.MEANING%TYPE;
1193 l_organization_name HR_ORGANIZATION_UNITS.NAME%TYPE;
1194
1195 l_prev_article_id OKC_ARTICLES_ALL.ARTICLE_ID%TYPE;
1196 l_GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
1197 TYPE l_adoption_type_list IS TABLE OF FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE INDEX BY BINARY_INTEGER;
1198 TYPE l_adoption_status_list IS TABLE OF OKC_ARTICLE_ADOPTIONS.ADOPTION_STATUS%TYPE INDEX BY BINARY_INTEGER;
1199 TYPE l_adp_record_status_list IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
1200 TYPE l_article_version_id_list IS TABLE OF OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER ;
1201 TYPE l_article_id_list IS TABLE OF OKC_ARTICLES_ALL.ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
1202 TYPE l_article_title_list IS TABLE OF OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE INDEX BY BINARY_INTEGER ;
1203 TYPE l_orig_system_ref_code_list IS TABLE of OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_CODE%TYPE INDEX BY BINARY_INTEGER ;
1204
1205 l_adoption_type_tbl l_adoption_type_list;
1206 l_adoption_status_tbl l_adoption_status_list;
1207 l_adp_record_status_tbl l_adp_record_status_list;
1208 l_article_version_id_tbl l_article_version_id_list;
1209 l_article_id_tbl l_article_id_list;
1210 l_article_title_tbl l_article_title_list;
1211 l_orig_sys_ref_code_tbl l_orig_system_ref_code_list;
1212
1213
1214 CURSOR l_org_info_csr (cp_org_id IN NUMBER, cp_global_org_id IN NUMBER) IS
1215 SELECT decode(nvl(ORG_INFORMATION1,'N'),'N','AVAILABLE','Y','ADOPTED') ADOPTION_TYPE, U.NAME
1216 FROM HR_ORGANIZATION_INFORMATION I,
1217 HR_ORGANIZATION_UNITS U
1218 WHERE I.ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
1219 AND I.ORGANIZATION_ID = cp_org_id
1220 AND I.ORGANIZATION_ID = U.ORGANIZATION_ID
1221 AND I.ORGANIZATION_ID <> cp_global_org_id;
1222
1223 -- Modified Cursor Where clause , Bug 3315511
1224 CURSOR l_adoptions_csr (cp_global_org_id IN NUMBER, cp_local_org_id IN NUMBER) IS
1225 SELECT article_version_id , article_title,
1226 art.article_id, art.orig_system_reference_code
1227 FROM OKC_ARTICLE_VERSIONS VER, OKC_ARTICLES_ALL ART
1228 WHERE global_yn = 'Y'
1229 AND org_id = cp_global_org_id
1230 AND VER.article_id = ART.article_id
1231 AND article_status in ('APPROVED', 'ON_HOLD')
1232 AND nvl(end_date, sysdate) >= trunc(sysdate)
1233 AND NOT EXISTS
1234 (SELECT 1 FROM OKC_ARTICLE_ADOPTIONS
1235 WHERE global_article_version_id = VER.article_version_id
1236 AND local_org_id = cp_local_org_id)
1237 UNION ALL
1238 SELECT article_version_id , article_title, article_id, orig_system_reference_code
1239 FROM
1240 (
1241 SELECT article_version_id , article_title , art.article_id ,
1242 start_date , end_date,
1243 global_yn , org_id , article_status,
1244 art.orig_system_reference_code
1245 FROM OKC_ARTICLE_VERSIONS VER, OKC_ARTICLES_ALL ART
1246 WHERE VER.article_id = ART.article_id
1247 AND global_yn = 'Y'
1248 AND org_id = cp_global_org_id
1249 AND start_date = ( SELECT max(start_date)
1250 FROM OKC_ARTICLE_VERSIONS VER1,OKC_ARTICLES_ALL ART1
1251 WHERE VER1.ARTICLE_ID = ART1.ARTICLE_ID
1252 AND VER1.ARTICLE_ID = VER.ARTICLE_ID )
1253 )
1254 WHERE
1255 article_status = 'APPROVED'
1256 AND end_date < trunc(sysdate)
1257 AND NOT EXISTS
1258 (SELECT 1 FROM OKC_ARTICLE_ADOPTIONS
1259 WHERE global_article_version_id = article_version_id
1260 AND local_org_id = cp_local_org_id);
1261
1262 CURSOR l_dup_title_csr (
1263 cp_local_org_id IN NUMBER,
1264 cp_article_title IN VARCHAR2) IS
1265 SELECT '1'
1266 FROM OKC_ARTICLES_ALL
1267 WHERE org_id = cp_local_org_id
1268 AND article_title = cp_article_title
1269 AND standard_yn = 'Y';
1270
1271 CURSOR l_adoption_type_meaning_csr (
1272 cp_adoption_type IN VARCHAR2) IS
1273 SELECT meaning
1274 FROM FND_LOOKUPS
1275 WHERE lookup_type = 'OKC_ARTICLE_ADOPTION_TYPE'
1276 AND lookup_code = cp_adoption_type;
1277
1278
1279 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1280 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
1281
1282 BEGIN
1283 IF (l_debug = 'Y') THEN
1284 okc_debug.log('100: Entered auto_adoption for new org.', 2);
1285 END IF;
1286
1287 FND_MSG_PUB.initialize;
1288 retcode := 0;
1289 l_count_ver_adopted := 0;
1290 l_count_ver_available := 0;
1291 -- The org being passed cannot be a global org.
1292
1293 l_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
1294 if p_org_id = l_GLOBAL_ORG_ID or p_org_id = -99 Then
1295 FND_MESSAGE.SET_NAME(G_APP_NAME, 'OKC_ADOPT_INVALID_ORG');
1296 FND_MSG_PUB.add;
1297 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(1, p_encoded =>FND_API.G_FALSE ));
1298 retcode := 2;
1299 return;
1300 end if;
1301
1302 l_return_status := FND_API.G_RET_STS_SUCCESS;
1303
1304 -- First determine whether the orgid passed is a valid org defined in HR Org definitions.
1305
1306 OPEN l_org_info_csr (p_org_id,l_global_org_id);
1307 FETCH l_org_info_csr INTO l_adoption_type, l_organization_name;
1308 l_row_notfound := l_org_info_csr%NOTFOUND;
1309 CLOSE l_org_info_csr ;
1310
1311 IF (l_row_notfound) THEN
1312 IF (l_debug = 'Y') THEN
1313 okc_debug.log('200: Org row not found', 2);
1314 END IF;
1315 FND_MESSAGE.SET_NAME(G_APP_NAME, 'OKC_ADOPT_INVALID_ORG');
1316 FND_MSG_PUB.add;
1317 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(1, p_encoded =>FND_API.G_FALSE ));
1318 retcode := 2;
1319 return;
1320 END IF;
1321
1322 -- If adoption type for the org is subscribing to autoadoption, all rows will be ADOPTED and APPROVED. However, if
1323 -- any duplicates were found, it will be AVAILABLE for Adoption.
1324
1325 if l_adoption_type = 'ADOPTED' Then
1326 l_adoption_status := 'APPROVED';
1327 else
1328 l_adoption_status := NULL;
1329 end if;
1330 i := 0;
1331 l_prev_article_id := -99;
1332 OPEN l_adoptions_csr (l_global_org_id , p_org_id ) ;
1333 LOOP
1334 FETCH l_adoptions_csr BULK COLLECT INTO
1335 l_article_version_id_tbl, l_article_title_tbl,
1336 l_article_id_tbl, l_orig_sys_ref_code_tbl LIMIT p_fetchsize;
1337 i := 0;
1338 EXIT WHEN l_article_version_id_tbl.COUNT = 0;
1339 IF l_adoption_type <> 'ADOPTED' Then
1340
1341 --if article is migrated then adoption_type becomes 'ADOPTED'
1342 --Otherwise, it is 'AVAILABLE'
1343 FOR i IN l_article_version_id_tbl.FIRST..l_article_version_id_tbl.LAST LOOP
1344
1345 l_adp_record_status_tbl(i) := 'S';
1346 if instr(nvl(l_orig_sys_ref_code_tbl(i),'*'),'OKCMIG')=1 then
1347 l_adoption_type_tbl(i) := 'ADOPTED';
1348 l_adoption_status_tbl(i) := 'APPROVED';
1349 l_count_ver_adopted := l_count_ver_adopted +1;
1350 ELSE
1351 l_adoption_type_tbl(i) := 'AVAILABLE';
1352 l_adoption_status_tbl(i) := NULL;
1353 l_count_ver_available := l_count_ver_available +1;
1354 end if;
1355
1356 l_prev_article_id := l_article_id_tbl(i);
1357 l_prev_adoption_type := l_adoption_type_tbl(i);
1358
1359 END LOOP;
1360 /**
1361 BEGIN
1362 savepoint adoption_dml;
1363 FORALL i IN l_article_version_id_tbl.FIRST..l_article_version_id_tbl.LAST
1364 INSERT INTO OKC_ARTICLE_ADOPTIONS
1365 (
1366 GLOBAL_ARTICLE_VERSION_ID,
1367 ADOPTION_TYPE,
1368 LOCAL_ORG_ID,
1369 ADOPTION_STATUS,
1370 LOCAL_ARTICLE_VERSION_ID,
1371 OBJECT_VERSION_NUMBER,
1372 CREATED_BY,
1373 CREATION_DATE,
1374 LAST_UPDATED_BY,
1375 LAST_UPDATE_LOGIN,
1376 LAST_UPDATE_DATE
1377 )
1378 VALUES
1379 (
1380 l_article_version_id_tbl(i),
1381 'AVAILABLE',
1382 p_org_id,
1383 NULL,
1384 NULL,
1385 1.0,
1386 l_User_Id,
1387 sysdate,
1388 l_User_Id,
1389 l_Login_Id,
1390 sysdate );
1391 EXCEPTION
1392 WHEN OTHERS THEN
1393 IF (l_debug = 'Y') THEN
1394 okc_debug.log('500: Leaving Auto_Adoption because of EXCEPTION: '||sqlerrm, 2);
1395 END IF;
1396 l_adp_record_status_tbl(i) := 'U';
1397 l_return_status := G_RET_STS_UNEXP_ERROR ;
1398 FND_FILE.PUT_LINE(FND_FILE.LOG,sqlerrm);
1399 errbuf := substr(sqlerrm,1,200);
1400 rollback to adoption_dml;
1401 END;
1402 **/
1403 else -- Org has subscribed to autoadoption. Autoadopted in all cases unless duplicates which will make it avialable for adoption
1404 BEGIN
1405 FOR i IN l_article_version_id_tbl.FIRST..l_article_version_id_tbl.LAST LOOP
1406 BEGIN
1407 l_adp_record_status_tbl(i) := 'S';
1408
1409 -- Perform dup. checks only if article changes - not for all versions.
1410 -- dbms_output.put_line('PREV:NEW'||l_prev_article_id||'*'|| l_article_id_tbl(i));
1411 if l_prev_article_id <> l_article_id_tbl(i) Then
1412 j := j+1;
1413 --dbms_output.put_line('DUP:' );
1414 --but if same article_title exists,
1415 --then do not adopt it
1416 OPEN l_dup_title_csr (p_org_id,l_article_title_tbl(i));
1417 FETCH l_dup_title_csr INTO l_dummy;
1418 l_row_notfound := l_dup_title_csr%NOTFOUND;
1419 CLOSE l_dup_title_csr ;
1420
1421 if l_row_notfound then
1422 l_adoption_type_tbl(i) := 'ADOPTED';
1423 else
1424 l_adoption_type_tbl(i) := 'AVAILABLE';
1425 end if;
1426
1427 else
1428 l_adoption_type_tbl(i) := l_prev_adoption_type;
1429 end if;
1430 --dbms_output.put_line('PREVADP:NEWADP'||l_prev_adoption_type||'*'|| l_adoption_type_tbl(i));
1431 l_prev_article_id := l_article_id_tbl(i);
1432 l_prev_adoption_type := l_adoption_type_tbl(i);
1433
1434 IF l_adoption_type_tbl(i) = 'ADOPTED' THEN
1435 l_adoption_status_tbl(i) := 'APPROVED';
1436 l_count_ver_adopted := l_count_ver_adopted +1;
1437 ELSE
1438 l_adoption_status_tbl(i) := NULL;
1439 l_count_ver_available := l_count_ver_available +1;
1440 END IF;
1441
1442
1443 EXCEPTION
1444 WHEN OTHERS THEN
1445 IF (l_debug = 'Y') THEN
1446 okc_debug.log('500: Auto_Adoption New Org EXCEPTION: ' ||sqlerrm, 2);
1447 END IF;
1448 IF l_dup_title_csr%ISOPEN THEN
1449 CLOSE l_dup_title_csr;
1450 END IF;
1451 IF l_adoptions_csr%ISOPEN THEN
1452 CLOSE l_adoptions_csr;
1453 END IF;
1454 l_return_status := G_RET_STS_UNEXP_ERROR ;
1455 FND_FILE.PUT_LINE(FND_FILE.LOG,sqlerrm);
1456 retcode := 2;
1457 errbuf := substr(sqlerrm,1,200);
1458 exit; -- Fatal error must exit....
1459 END;
1460 END LOOP;
1461 IF l_return_status <> G_RET_STS_SUCCESS THEN
1462 return;
1463 END IF;
1464 END;
1465 END IF;
1466 -- Transaction will be bulk rolledback for a batch if error observed. It's a pity we cannot save exceptions due to
1467 -- backward compatibility with 8.1.7.4
1468
1469 SAVEPOINT adoption_dml;
1470 BEGIN
1471 FORALL i IN l_article_version_id_tbl.FIRST..l_article_version_id_tbl.LAST
1472 INSERT INTO OKC_ARTICLE_ADOPTIONS
1473 (
1474 GLOBAL_ARTICLE_VERSION_ID,
1475 ADOPTION_TYPE,
1476 LOCAL_ORG_ID,
1477 ADOPTION_STATUS,
1478 LOCAL_ARTICLE_VERSION_ID,
1479 OBJECT_VERSION_NUMBER,
1480 CREATED_BY,
1481 CREATION_DATE,
1482 LAST_UPDATED_BY,
1483 LAST_UPDATE_LOGIN,
1484 LAST_UPDATE_DATE
1485 )
1486 SELECT
1487 l_article_version_id_tbl(i),
1488 l_adoption_type_tbl(i),
1489 p_org_id,
1490 l_adoption_status_tbl(i),
1491 NULL,
1492 1.0,
1493 l_User_Id,
1494 sysdate,
1495 l_User_Id,
1496 l_Login_Id,
1497 sysdate
1498 FROM DUAL
1499 WHERE l_adp_record_status_tbl(i) = 'S';
1500
1501 -- Insert all relationships for the global articles (will be applicable only if both source and targets are adopted as is)
1502
1503 INSERT INTO OKC_ARTICLE_RELATNS_ALL
1504 (
1505 SOURCE_ARTICLE_ID,
1506 TARGET_ARTICLE_ID,
1507 ORG_ID,
1508 RELATIONSHIP_TYPE,
1509 OBJECT_VERSION_NUMBER,
1510 CREATED_BY,
1511 CREATION_DATE,
1512 LAST_UPDATED_BY,
1513 LAST_UPDATE_LOGIN,
1514 LAST_UPDATE_DATE
1515 )
1516 SELECT SOURCE_ARTICLE_ID,
1517 TARGET_ARTICLE_ID,
1518 p_org_id,
1519 RELATIONSHIP_TYPE,
1520 1.0,
1521 l_User_Id,
1522 sysdate,
1523 l_User_Id,
1524 l_Login_Id,
1525 sysdate
1526 FROM OKC_ARTICLE_RELATNS_ALL R
1527 WHERE R.ORG_ID = l_global_org_id
1528 AND EXISTS
1529 (SELECT 1 FROM OKC_ARTICLE_VERSIONS V1, OKC_ARTICLE_ADOPTIONS A1
1530 WHERE V1.ARTICLE_ID = R.TARGET_ARTICLE_ID
1531 AND A1.GLOBAL_ARTICLE_VERSION_ID = V1.ARTICLE_VERSION_ID
1532 AND V1.GLOBAL_YN = 'Y'
1533 AND A1.ADOPTION_STATUS = 'APPROVED'
1534 AND A1.ADOPTION_TYPE = 'ADOPTED'
1535 AND A1.LOCAL_ORG_ID = p_org_id
1536 )
1537 AND EXISTS
1538 (SELECT 1 FROM OKC_ARTICLE_VERSIONS V2, OKC_ARTICLE_ADOPTIONS A2
1539 WHERE V2.ARTICLE_ID = R.SOURCE_ARTICLE_ID
1540 AND A2.GLOBAL_ARTICLE_VERSION_ID = V2.ARTICLE_VERSION_ID
1541 AND V2.GLOBAL_YN = 'Y'
1542 AND A2.ADOPTION_STATUS = 'APPROVED'
1543 AND A2.ADOPTION_TYPE = 'ADOPTED'
1544 AND A2.LOCAL_ORG_ID = p_org_id
1545 )
1546 AND NOT EXISTS
1547 (
1548 SELECT '1'
1549 FROM OKC_ARTICLE_RELATNS_ALL R1
1550 WHERE R1.SOURCE_ARTICLE_ID = R.SOURCE_ARTICLE_ID AND
1551 R1.TARGET_ARTICLE_ID = R.TARGET_ARTICLE_ID AND
1552 R1.RELATIONSHIP_TYPE = R.RELATIONSHIP_TYPE AND
1553 R1.ORG_ID = p_org_id
1554 );
1555
1556 EXCEPTION
1557 WHEN OTHERS THEN
1558 IF (l_debug = 'Y') THEN
1559 okc_debug.log('500: Auto_Adoption New Org EXCEPTION: ' ||sqlerrm, 2);
1560 END IF;
1561 l_adp_record_status_tbl(i) := 'U';
1562 l_return_status := G_RET_STS_UNEXP_ERROR ;
1563 FND_FILE.PUT_LINE(FND_FILE.LOG,sqlerrm);
1564 errbuf := substr(sqlerrm,1,200);
1565 rollback to adoption_dml;
1566 END;
1567 -- END;
1568 -- END IF;
1569
1570 l_adoption_type_tbl.DELETE;
1571 l_adoption_status_tbl.DELETE;
1572 l_adp_record_status_tbl.DELETE;
1573 l_article_version_id_tbl.DELETE;
1574 COMMIT;
1575 EXIT WHEN l_adoptions_csr%NOTFOUND;
1576 END LOOP;
1577 COMMIT;
1578
1579 --Get adoption type meaning
1580 OPEN l_adoption_type_meaning_csr (l_adoption_type);
1581 FETCH l_adoption_type_meaning_csr into l_adoption_type_meaning;
1582 CLOSE l_adoption_type_meaning_csr;
1583
1584 -- Write report into OUTPUT
1585 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1586 p_msg_name => 'OKC_ART_ADP_NEWORG_OUTPUT',
1587 p_token1 => 'ADOPTION_TYPE',
1588 p_token1_value => l_adoption_type_meaning,
1589 p_token2 => 'TOTAL_VER_ADOPTED',
1590 p_token2_value => l_count_ver_adopted,
1591 p_token3 => 'TOTAL_VER_AVAILABLE',
1592 p_token3_value => l_count_ver_available,
1593 p_token4 => 'ORG_NAME',
1594 p_token4_value => l_organization_name);
1595
1596 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MSG_PUB.Get(1,p_encoded=>FND_API.G_FALSE));
1597
1598 okc_debug.log('500: Leaving Auto_Adoption New Org' );
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 IF (l_debug = 'Y') THEN
1602 okc_debug.log('500: Leaving Auto_Adoption New Org because of EXCEPTION: '||sqlerrm, 2);
1603 END IF;
1604 l_return_status := G_RET_STS_UNEXP_ERROR ;
1605 IF l_adoptions_csr%ISOPEN Then
1606 close l_adoptions_csr;
1607 END IF;
1608 FND_FILE.PUT_LINE(FND_FILE.LOG,sqlerrm);
1609 errbuf := substr(sqlerrm,1,200);
1610 retcode := 2;
1611 END AUTO_ADOPT_NEWORG;
1612
1613 END OKC_ADOPTIONS_GRP;
[Home] [Help]