[Home] [Help]
PACKAGE BODY: APPS.OKC_ARTICLE_STATUS_CHANGE_PVT
Source
1 PACKAGE BODY OKC_ARTICLE_STATUS_CHANGE_PVT AS
2 /* $Header: OKCVARTSTSB.pls 120.2 2007/04/02 23:04:00 arsundar ship $ */
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_ARTICLE_STATUS_CHANGE_PVT';
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 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
25 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
26
27 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
28 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
29 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
30
31 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
32 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
33 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
34 G_GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
35 -- MOAC
36 G_CURRENT_ORG_ID NUMBER ;
37 /*
38 G_CURRENT_ORG_ID NUMBER := -99;
39 -- One Time fetch and cache the current Org.
40 CURSOR CUR_ORG_CSR IS
41 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
42 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
43 FROM DUAL;
44 */
45
46 ---------------------------------------
47 -- PROCEDURE update_adoption_ifexists --
48 ---------------------------------------
49 -- Local Procedure to update article adoptions...
50 -- This will lock and update if exists
51 -- This is more applicable in this case than Simple API which will throw an
52 -- error if row is not found
53 -- Parameters: org_id, article_version_id and article_status (APPROVED,REJECTED
54 -- ,PENDING_APPROVAL)
55
56 PROCEDURE update_adoption_ifexists (p_article_version_id IN NUMBER,
57 p_org_id IN NUMBER,
58 p_article_status IN VARCHAR2,
59 x_return_status OUT NOCOPY VARCHAR2) IS
60
61 CURSOR l_article_adoption_csr(cp_article_version_id IN NUMBER,
62 cp_local_org_id IN NUMBER) is
63 SELECT rowid from OKC_ARTICLE_ADOPTIONS
64 WHERE LOCAL_ARTICLE_VERSION_ID = cp_article_version_id
65 AND LOCAL_ORG_ID = cp_local_org_id
66 AND ADOPTION_TYPE = 'LOCALIZED'
67 FOR UPDATE OF object_version_number ;
68
69 l_rowid ROWID;
70
71 BEGIN
72 IF (l_debug = 'Y') THEN
73 okc_debug.log('500: Entering update adoption if exists ', 2);
74 END IF;
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 OPEN l_article_adoption_csr(p_article_version_id,
78 p_org_id) ;
79 FETCH l_article_adoption_csr INTO l_rowid;
80 IF l_article_adoption_csr%FOUND THEN
81 UPDATE OKC_ARTICLE_ADOPTIONS
82 SET ADOPTION_STATUS = p_article_status,
83 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
84 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
85 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
86 LAST_UPDATE_DATE = SYSDATE
87 WHERE CURRENT OF l_article_adoption_csr;
88 END IF;
89 CLOSE l_article_adoption_csr;
90 IF (l_debug = 'Y') THEN
91 okc_debug.log('500: Leaving update adoption if exists successfully', 2);
92 END IF;
93 EXCEPTION
94 WHEN OTHERS THEN
95 IF (l_debug = 'Y') THEN
96 okc_debug.log('500: Leaving update adoption if exists because of EXCEPTION: '||sqlerrm, 2);
97 END IF;
98 IF l_article_adoption_csr%ISOPEN THEN
99 CLOSE l_article_adoption_csr;
100 END IF;
101 x_return_status := G_RET_STS_UNEXP_ERROR ;
102 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
103 p_msg_name => G_UNEXPECTED_ERROR,
104 p_token1 => G_SQLCODE_TOKEN,
105 p_token1_value => sqlcode,
106 p_token2 => G_SQLERRM_TOKEN,
107 p_token2_value => sqlerrm);
108
109 END update_adoption_ifexists;
110 ---------------------------------------
111 -- PROCEDURE update_prev_vers_enddate
112 ---------------------------------------
113 -- Local Procedure to update end date of previous article version on approval
114 -- Parameters: article_id, article_version_id and end_date
115
116 PROCEDURE update_prev_vers_enddate (p_article_version_id IN NUMBER,
117 p_article_id IN NUMBER,
118 p_end_date IN DATE,
119 p_current_org_id IN NUMBER,
120 x_return_status OUT NOCOPY VARCHAR2) IS
121
122 CURSOR l_earlier_version_csr(p_article_id IN NUMBER, p_article_version_id IN NUMBER) IS
123 -- 8.1.7.4 compatibility
124 select
125 av.global_yn,
126 av.article_status,
127 av.adoption_type,
128 av.start_date,
129 av.end_date,
130 av.article_version_number,
131 av.article_version_id
132 from
133 okc_article_versions av
134 where
135 av.article_id = p_article_id
136 and
137 av.start_date = (select
138 max(av1.start_date)
139 from
140 okc_article_versions av1
141 where
142 av1.article_id = av.article_id
143 and
144 av1.end_date is null
145 and
146 av1.article_version_id <> p_article_version_id
147 );
148 /*
149 SELECT S.GLOBAL_YN,
150 S.ARTICLE_STATUS,
151 S.ADOPTION_TYPE,
152 S.START_DATE,
153 S.END_DATE,
154 S.MAX_START_DATE,
155 S.ARTICLE_VERSION_NUMBER,
156 S.ARTICLE_VERSION_ID
157 FROM (
158 SELECT
159 A.GLOBAL_YN,
160 A.ARTICLE_STATUS,
161 A.ADOPTION_TYPE,
162 A.START_DATE, A.END_DATE,
163 MAX(A.START_DATE) OVER (PARTITION BY A.ARTICLE_ID) AS MAX_START_DATE,
164 A.ARTICLE_VERSION_NUMBER,
165 A.ARTICLE_VERSION_ID
166 FROM OKC_ARTICLE_VERSIONS A
167 WHERE A.ARTICLE_ID = p_article_id
168 AND A.END_DATE IS NULL
169 AND ARTICLE_VERSION_ID <> p_article_version_id
170 ) S
171 WHERE S.START_DATE = S.MAX_START_DATE;
172 */
173 l_earlier_version_id NUMBER := NULL;
174 l_article_id NUMBER := NULL;
175 l_article_status VARCHAR2(30) := NULL;
176 l_new_article_status VARCHAR2(30) := NULL;
177 l_earlier_version_rec l_earlier_version_csr%ROWTYPE;
178
179
180 BEGIN
181 IF (l_debug = 'Y') THEN
182 okc_debug.log('500: Entering update prev vers end date ', 2);
183 END IF;
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185 OPEN l_earlier_version_csr(p_article_id, p_article_version_id);
186 FETCH l_earlier_version_csr INTO l_earlier_version_rec ;
187 IF l_earlier_version_csr%FOUND THEN
188 OKC_ARTICLE_VERSIONS_PVT.Update_Row(
189 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
190 x_return_status => x_return_status,
191 p_article_version_id => l_earlier_version_rec.article_version_id,
192 p_article_id => NULL,
193 p_article_version_number => NULL,
194 p_article_text => NULL,
195 p_provision_yn => NULL,
196 p_insert_by_reference => NULL,
197 p_lock_text => NULL,
198 p_global_yn => NULL,
199 p_article_language => NULL,
200 p_article_status => NULL,
201 p_sav_release => NULL,
202 p_start_date => NULL,
203 p_end_date => p_end_date,
204 p_std_article_version_id => NULL,
205 p_display_name => NULL,
206 p_translated_yn => NULL,
207 p_article_description => NULL,
208 p_date_approved => NULL,
209 p_default_section => NULL,
210 p_reference_source => NULL,
211 p_reference_text => NULL,
212 p_orig_system_reference_code => NULL,
213 p_orig_system_reference_id1 => NULL,
214 p_orig_system_reference_id2 => NULL,
215 p_additional_instructions => NULL,
216 p_variation_description => NULL,
217 p_current_org_id => p_current_org_id,
218 p_attribute_category => NULL,
219 p_attribute1 => NULL,
220 p_attribute2 => NULL,
221 p_attribute3 => NULL,
222 p_attribute4 => NULL,
223 p_attribute5 => NULL,
224 p_attribute6 => NULL,
225 p_attribute7 => NULL,
226 p_attribute8 => NULL,
227 p_attribute9 => NULL,
228 p_attribute10 => NULL,
229 p_attribute11 => NULL,
230 p_attribute12 => NULL,
231 p_attribute13 => NULL,
232 p_attribute14 => NULL,
233 p_attribute15 => NULL,
234 p_object_version_number => NULL,
235 x_article_status => l_article_status,
236 x_article_id => l_article_id,
237 x_earlier_version_id => l_earlier_version_id
238 );
239 END IF;
240 CLOSE l_earlier_version_csr;
241 IF (l_debug = 'Y') THEN
242 okc_debug.log('500: Leaving update prev end date successfully', 2);
243 END IF;
244
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 IF (l_debug = 'Y') THEN
249 okc_debug.log('500: Leaving update prev vers end date because of EXCEPTION: '||sqlerrm, 2);
250 END IF;
251 IF l_earlier_version_csr%ISOPEN THEN
252 CLOSE l_earlier_version_csr;
253 END IF;
254 x_return_status := G_RET_STS_UNEXP_ERROR ;
255 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
256 p_msg_name => G_UNEXPECTED_ERROR,
257 p_token1 => G_SQLCODE_TOKEN,
258 p_token1_value => sqlcode,
259 p_token2 => G_SQLERRM_TOKEN,
260 p_token2_value => sqlerrm);
261
262 END update_prev_vers_enddate;
263 ---------------------------------------
264 -- PROCEDURE hold_unhold --
265 ---------------------------------------
266 -- Procedure to set an article version status from hold to unhold (Approved)
267 -- and vice-versa.
268 -- Parameters: article_version_id , p_hold_yn => Y means Hold and N means Unhold
269 -- (Approved).
270 -- This will be called from the UI only. So we can save db access to check
271 -- if article version is global or Not.
272
273 PROCEDURE hold_unhold(
274 p_api_version IN NUMBER,
275 p_init_msg_list IN VARCHAR2 ,
276 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
277
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 x_msg_data OUT NOCOPY VARCHAR2,
281 p_hold_yn IN VARCHAR2 := 'Y',
282 p_article_version_id IN NUMBER
283 ) IS
284 l_api_version CONSTANT NUMBER := 1;
285 l_api_name CONSTANT VARCHAR2(30) := 'hold_unhold';
286 l_rownotfound BOOLEAN := FALSE;
287 l_earlier_version_id NUMBER := NULL;
288 l_article_id NUMBER := NULL;
289 l_article_status VARCHAR2(30) := NULL;
290 l_new_article_status VARCHAR2(30) := NULL;
291
292 CURSOR l_article_version_csr (cp_article_version_id IN NUMBER) IS
293 SELECT org_id, article_status, global_yn, art.article_id
294 FROM okc_articles_all art, okc_article_versions ver
295 WHERE ver.article_id = art.article_id
296 AND ver.article_version_id = cp_article_version_id
297 AND art.standard_yn = 'Y'
298 FOR UPDATE OF start_date;
299 l_article_version_rec l_article_version_csr%ROWTYPE;
300 BEGIN
301
302 IF (l_debug = 'Y') THEN
303 okc_debug.log('100: Entered hold_unhold with '||p_hold_yn||'*'||p_article_version_id, 2);
304 END IF;
305
306 IF FND_API.to_Boolean( p_init_msg_list ) THEN
307 FND_MSG_PUB.initialize;
308 END IF;
309 -- MOAC
310 G_CURRENT_ORG_ID := mo_global.get_current_org_id();
311 /*
312 OPEN cur_org_csr;
313 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
314 CLOSE cur_org_csr;
315 */
316 -- Initialize API return status to success
317 x_return_status := G_RET_STS_SUCCESS;
318 -- MOAC
319 if G_CURRENT_ORG_ID IS NULL Then
320 IF (l_debug = 'Y') THEN
321 Okc_Debug.Log('130: - attribute G_CURRENT_ORG_ID is invalid', 2);
322 END IF;
323 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
324 x_return_status := G_RET_STS_ERROR;
325 RAISE FND_API.G_EXC_ERROR ;
326 end if;
327
328 OPEN l_article_version_csr (p_article_version_id);
329 FETCH l_article_version_csr INTO l_article_version_rec;
330 l_rownotfound := l_article_version_csr%NOTFOUND;
331 IF l_rownotfound THEN
332 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_VERSION_ID');
333 x_return_status := G_RET_STS_ERROR;
334 ELSIF p_hold_yn = 'Y' THEN
335 IF l_article_version_rec.article_status <> 'APPROVED' Then
336 OKC_API.SET_MESSAGE(G_APP_NAME,'OKC_ART_NOT_APPROVD');
337 x_return_status := G_RET_STS_ERROR;
338 ELSIF l_article_version_rec.org_id <> G_CURRENT_ORG_ID Then
339 OKC_API.SET_MESSAGE(G_APP_NAME,'OKC_ART_DIFF_ORG');
340 x_return_status := G_RET_STS_ERROR;
341 ELSE
342 l_new_article_status := 'ON_HOLD';
343 END IF;
344 ELSE
345 IF l_article_version_rec.article_status <> 'ON_HOLD' Then
346 OKC_API.SET_MESSAGE(G_APP_NAME,'OKC_ART_NOT_HOLD');
347 x_return_status := G_RET_STS_ERROR;
348 ELSIF l_article_version_rec.org_id <> G_CURRENT_ORG_ID Then
349 OKC_API.SET_MESSAGE(G_APP_NAME,'OKC_ART_DIFF_ORG');
350 x_return_status := G_RET_STS_ERROR;
351 ELSE
352 l_new_article_status := 'APPROVED';
353 END IF;
354 END IF;
355
356 IF x_return_status = G_RET_STS_SUCCESS THEN
357
358 -- start update adoption table if work with global article (muteshev)
359 /* commented out because of bug#3251484
360 IF (l_debug = 'Y') THEN
361 okc_debug.log('120: Starting Update Adoption Statuses if Global Article ', 2);
362 END IF;
363 begin
364 if l_article_version_rec.global_yn = 'Y' then
365 update okc_article_adoptions
366 set adoption_status = l_new_article_status,
367 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
368 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
369 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
370 LAST_UPDATE_DATE = SYSDATE
371 where global_article_version_id = p_article_version_id
372 and adoption_type = 'ADOPTED';
373 if l_new_article_status = 'ON_HOLD' then
374 update okc_article_adoptions
375 set adoption_status = l_new_article_status,
376 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
377 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
378 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
379 LAST_UPDATE_DATE = SYSDATE
380 where global_article_version_id = p_article_version_id
381 and adoption_type = 'AVAILABLE';
382 elsif l_new_article_status = 'APPROVED' then
383 update okc_article_adoptions
384 set adoption_status = null,
385 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
386 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
387 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
388 LAST_UPDATE_DATE = SYSDATE
389 where global_article_version_id = p_article_version_id
390 and adoption_type = 'AVAILABLE';
391 end if;
392 end if;
393 exception
394 when others then
395 x_return_status := G_RET_STS_UNEXP_ERROR;
396 end;
397
398 IF (l_debug = 'Y') THEN
399 okc_debug.log('140: Finishing Update Adoption Statuses if Global Article ', 2);
400 END IF;
401 */
402 -- end update adoption table if work with global article (muteshev)
403
404 IF x_return_status = G_RET_STS_SUCCESS THEN
405
406 IF (l_debug = 'Y') THEN
407 okc_debug.log('160: Starting Update Article Status ', 2);
408 END IF;
409
410 UPDATE OKC_ARTICLE_VERSIONS
411 SET ARTICLE_STATUS = l_new_article_status,
412 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
413 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
414 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
415 LAST_UPDATE_DATE = SYSDATE
416 WHERE current of l_article_version_csr;
417 end if;
418
419 IF (l_debug = 'Y') THEN
420 okc_debug.log('180: Finishing Update Article Status ', 2);
421 END IF;
422
423 END IF;
424 CLOSE l_article_version_csr;
425 --------------------------------------------
426 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
428 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
429 RAISE FND_API.G_EXC_ERROR ;
430 END IF;
431 --------------------------------------------
432 COMMIT;
433 IF (l_debug = 'Y') THEN
434 okc_debug.log('200: Leaving Hold Unhold successfully ', 2);
435 END IF;
436 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
437
438 EXCEPTION
439 WHEN FND_API.G_EXC_ERROR THEN
440 IF (l_debug = 'Y') THEN
441 okc_debug.log('300: Leaving Hold_Unhold: OKC_API.G_EXCEPTION_ERROR Exception', 2);
442 END IF;
443 x_return_status := G_RET_STS_ERROR ;
444 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
445
446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447 IF (l_debug = 'Y') THEN
448 okc_debug.log('400: Leaving Hold_Unhold: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
449 END IF;
450 x_return_status := G_RET_STS_UNEXP_ERROR ;
451 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,p_encoded=> 'F', p_data => x_msg_data );
452
453 WHEN OTHERS THEN
454 IF (l_debug = 'Y') THEN
455 okc_debug.log('500: Leaving Hold_Unhold because of EXCEPTION: '||sqlerrm, 2);
456 END IF;
457
458 x_return_status := G_RET_STS_UNEXP_ERROR ;
459 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,p_encoded=> 'F', p_data => x_msg_data );
460
461 END hold_unhold;
462
463 ---------------------------------------
464 -- PROCEDURE pending-approval --
465 ---------------------------------------
466 -- Procedure to set an article version status from draft to pending approval
467 -- Parameters: article_version_id , p_adopt_as_is_yn => Y means Adoption at a
468 -- Local Org as is and N means Local version
469 -- This will be called from the UI only. So we can save db access to check
470 -- if article version is global or Not.
471
472
473
474 PROCEDURE pending_approval(
475 p_api_version IN NUMBER,
476 p_init_msg_list IN VARCHAR2 ,
477
478 x_return_status OUT NOCOPY VARCHAR2,
479 x_msg_count OUT NOCOPY NUMBER,
480 x_msg_data OUT NOCOPY VARCHAR2,
481 p_current_org_id IN NUMBER,
482 p_adopt_as_is_yn IN VARCHAR2,
483 p_article_version_id IN NUMBER,
484 p_article_title IN VARCHAR,
485 p_article_version_number IN VARCHAR
486 ) IS
487 l_api_version CONSTANT NUMBER := 1;
488 l_api_name CONSTANT VARCHAR2(30) := 'pending_approval';
489 l_global_article_version_id NUMBER := NULL;
490 l_local_article_version_id NUMBER;
491 l_rownotfound BOOLEAN := FALSE;
492 l_tmp_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
493 l_delete_adoption VARCHAR2(1) := 'T';
494 l_rowid ROWID;
495 l_earlier_version_id NUMBER := NULL;
496 l_article_id NUMBER := NULL;
497 l_article_status VARCHAR2(30) := NULL;
498 l_new_article_status VARCHAR2(30) := NULL;
499 l_earlier_local_version_id NUMBER := 0; -- not used just for check_adoption_details
500 l_dummy_num NUMBER := 0;
501 l_dummy_char VARCHAR2(1) := '?';
502
503 TYPE l_variable_name IS TABLE OF OKC_BUS_VARIABLES_TL.variable_name%TYPE INDEX BY BINARY_INTEGER ;
504 TYPE l_disabled_yn IS TABLE OF OKC_BUS_VARIABLES_B.disabled_yn%TYPE INDEX BY BINARY_INTEGER ;
505 TYPE l_value_set_id IS TABLE OF OKC_BUS_VARIABLES_B.value_set_id%TYPE INDEX BY BINARY_INTEGER ;
506 TYPE l_variable_type IS TABLE OF OKC_BUS_VARIABLES_B.variable_type%TYPE INDEX BY BINARY_INTEGER ;
507 -- Below Added for bug 5958643
508 TYPE l_variable_source IS TABLE OF OKC_BUS_VARIABLES_B.variable_source%TYPE INDEX BY BINARY_INTEGER ;
509 TYPE l_procedure_name IS TABLE OF OKC_BUS_VARIABLES_B.procedure_name%TYPE INDEX BY BINARY_INTEGER ;
510 -- Above Added for bug 5958643
511
512 l_variable_tbl l_variable_name;
513 l_disabled_yn_tbl l_disabled_yn;
514 l_value_set_id_tbl l_value_set_id;
515 l_variable_type_tbl l_variable_type;
516
517 -- Below Added for bug 5958643
518 l_variable_source_tbl l_variable_source;
519 l_procedure_name_tbl l_procedure_name;
520
521 l_procedure_spec_status ALL_OBJECTS.status%TYPE;
522 l_procedure_body_status ALL_OBJECTS.status%TYPE;
523
524 l_dummy VARCHAR2(1);
525 -- Above Added for bug 5958643
526
527 CURSOR l_check_valid_var_csr (cp_article_version_id IN NUMBER) IS
528 SELECT BVT.VARIABLE_NAME,
529 nvl(BVB.DISABLED_YN,'N'),
530 nvl(BVB.VALUE_SET_ID, -99),
531 BVB.VARIABLE_TYPE,
532 BVB.VARIABLE_SOURCE, -- Added for Bug 5958643
533 BVB.PROCEDURE_NAME -- Added for Bug 5958643
534 FROM
535 OKC_BUS_VARIABLES_TL BVT,
536 OKC_BUS_VARIABLES_B BVB,
537 OKC_ARTICLE_VARIABLES AAV
538 WHERE BVB.VARIABLE_CODE = BVT.VARIABLE_CODE
539 AND BVB.VARIABLE_CODE = AAV.VARIABLE_CODE
540 AND BVT.LANGUAGE = USERENV('LANG')
541 --AND BVB.DISABLED_YN = 'Y'
542 AND AAV.ARTICLE_VERSION_ID = cp_article_version_id;
543
544 CURSOR l_check_valid_valueset_csr (bvb_value_set_id IN NUMBER) IS
545 select '1'
546 from
547 fnd_flex_value_sets
548 where flex_value_set_id = bvb_value_set_id;
549
550 -- Below added for Bug 3737158
551 CURSOR l_article_lkup_csr(cp_article_version_id IN NUMBER) is
552 SELECT DEFAULT_SECTION, ARTICLE_TYPE
553 FROM OKC_ARTICLE_VERSIONS VER,OKC_ARTICLES_ALL ART
554 WHERE
555 ART.ARTICLE_ID = VER.ARTICLE_ID
556 AND VER.ARTICLE_VERSION_ID = cp_article_version_id;
557
558
559 l_default_section OKC_ARTICLE_VERSIONS.DEFAULT_SECTION%TYPE;
560 l_article_type OKC_ARTICLES_ALL.ARTICLE_TYPE%TYPE;
561
562 -- Above added for Bug 3737158
563
564 l_user_id NUMBER := FND_GLOBAL.USER_ID;
565 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
566
567 -- Below added for Bug 5958643
568 -- Expected procedure name is SCHEMA.PACKAGENAME.PROCEDURENAME
569
570 CURSOR csr_check_proc_spec_status (p_procedure_name VARCHAR2) IS
571 SELECT status
572 FROM all_objects
573 WHERE object_name = SUBSTR(p_procedure_name,
574 INSTR(p_procedure_name,'.')+1,
575 (INSTR(p_procedure_name,'.',1,2) -
576 INSTR(p_procedure_name,'.') - 1))
577 AND object_type = 'PACKAGE'
578 AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
579
580
581 CURSOR csr_check_proc_body_status (p_procedure_name VARCHAR2) IS
582 SELECT status
583 FROM all_objects
584 WHERE object_name = SUBSTR(p_procedure_name,
585 INSTR(p_procedure_name,'.')+1,
586 (INSTR(p_procedure_name,'.',1,2) -
587 INSTR(p_procedure_name,'.') - 1))
588 AND object_type = 'PACKAGE BODY'
589 AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
590
591 CURSOR csr_check_proc_exists (p_procedure_name VARCHAR2) IS
592 SELECT 'X'
593 FROM all_source
594 WHERE name = SUBSTR(p_procedure_name,
595 INSTR(p_procedure_name,'.')+1,
596 (INSTR(p_procedure_name,'.',1,2) -
597 INSTR(p_procedure_name,'.') - 1))
598 AND type = 'PACKAGE'
599 AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1)
600 AND text LIKE '%' || SUBSTR(p_procedure_name,INSTR(p_procedure_name,'.',1,2)+1) || '%';
601
602 -- Above added for Bug 5958643
603
604 BEGIN
605 IF (l_debug = 'Y') THEN
606 okc_debug.log('100: Entered pending approval with '||p_current_org_id ||'*'||p_adopt_as_is_yn||'*'||p_article_version_id, 2);
607 END IF;
608 -- Initialize API return status to success
609 x_return_status := FND_API.G_RET_STS_SUCCESS;
610
611 IF FND_API.to_Boolean( p_init_msg_list ) THEN
612 FND_MSG_PUB.initialize;
613 END IF;
614
615
616 IF (p_current_org_id = G_GLOBAL_ORG_ID ) OR
617 p_adopt_as_is_yn = 'N' THEN
618 IF (l_debug = 'Y') THEN
619 okc_debug.log('100: Validating article type and default section', 2);
620 END IF;
621
622 -- Below Added for Bug 3737158
623 -- Standard clauses will be checked for valid lookup code
624
625 OPEN l_article_lkup_csr (p_article_version_id);
626 FETCH l_article_lkup_csr INTO l_default_section, l_article_type;
627 IF l_article_lkup_csr%NOTFOUND THEN
628 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'STD_ARTICLE_VERSION_ID');
629 x_return_status := G_RET_STS_ERROR;
630 END IF;
631 CLOSE l_article_lkup_csr;
632 if x_return_status = G_RET_STS_ERROR then
633 RAISE FND_API.G_EXC_ERROR ;
634 end if;
635
636 -- Check for Default Section Effectivity
637 IF l_default_section IS NOT NULL THEN
638 l_tmp_return_status := Okc_Util.Check_Lookup_Code('OKC_ARTICLE_SECTION',l_default_section);
639 IF (l_tmp_return_status <> G_RET_STS_SUCCESS) THEN
640 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_INVALID_SECTION');
641 x_return_status := G_RET_STS_ERROR;
642 END IF;
643 END IF;
644
645 -- Check for Article Type Effectivity
646 l_tmp_return_status := Okc_Util.Check_Lookup_Code('OKC_SUBJECT',l_article_type);
647 IF (l_tmp_return_status <> G_RET_STS_SUCCESS) THEN
648 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_INVALID_TYPE');
649 x_return_status := G_RET_STS_ERROR;
650 END IF;
651 if x_return_status = G_RET_STS_ERROR then
652 RAISE FND_API.G_EXC_ERROR ;
653 end if;
654
655 -- Above Added for Bug 3737158
656
657 -- All the variables used should not have been inactivated since the article
658 -- cersion was created.
659
660 IF (l_debug = 'Y') THEN
661 okc_debug.log('100: Validating variables and assoc. with intent', 2);
662 END IF;
663 OPEN l_check_valid_var_csr (p_article_version_id);
664 FETCH l_check_valid_var_csr BULK COLLECT INTO
665 l_variable_tbl,
666 l_disabled_yn_tbl,
667 l_value_set_id_tbl,
668 l_variable_type_tbl,
669 l_variable_source_tbl, -- Added for bug 5958643
670 l_procedure_name_tbl; -- Added for bug 5958643
671 CLOSE l_check_valid_var_csr;
672 IF l_variable_tbl.COUNT > 0 THEN
673 FOR i in l_variable_tbl.FIRST..l_variable_tbl.LAST LOOP
674 if l_disabled_yn_tbl(i) = 'Y' then
675 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
676 p_msg_name => 'OKC_ART_INACTV_VARBL',
677 p_token1 => 'VARIABLE_NAME',
678 p_token1_value => l_variable_tbl(i),
679 p_token2 => 'ARTICLE_TITLE',
680 p_token2_value => p_article_title,
681 p_token3 => 'ARTICLE_VERSION',
682 p_token3_value => p_article_version_number);
683 x_return_status := G_RET_STS_ERROR;
684 end if;
685 if l_variable_type_tbl(i) = 'U' then
686 OPEN l_check_valid_valueset_csr (l_value_set_id_tbl(i));
687 FETCH l_check_valid_valueset_csr INTO l_dummy_char;
688 l_rownotfound := l_check_valid_valueset_csr%NOTFOUND;
689 CLOSE l_check_valid_valueset_csr;
690 if l_rownotfound then
691 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
692 p_msg_name => 'OKC_ART_INV_VALUESET',
693 p_token1 => 'VARIABLE_NAME',
694 p_token1_value => l_variable_tbl(i),
695 p_token2 => 'ARTICLE_TITLE',
696 p_token2_value => p_article_title,
697 p_token3 => 'ARTICLE_VERSION',
698 p_token3_value => p_article_version_number);
699 x_return_status := G_RET_STS_ERROR;
700 end if;
701 end if;
702
703 -- Below added for Bug 5958643
704 if l_variable_source_tbl(i) = 'P' then
705 OPEN csr_check_proc_spec_status(p_procedure_name => l_procedure_name_tbl(i));
706 FETCH csr_check_proc_spec_status INTO l_procedure_spec_status;
707
708 OPEN csr_check_proc_body_status(p_procedure_name => l_procedure_name_tbl(i));
709 FETCH csr_check_proc_body_status INTO l_procedure_body_status;
710
711 OPEN csr_check_proc_exists(p_procedure_name => l_procedure_name_tbl(i));
712 FETCH csr_check_proc_exists INTO l_dummy;
713
714 -- If Procedure Spec/Body status is INVALID then return error
715 IF l_procedure_spec_status = 'INVALID' OR l_procedure_body_status = 'INVALID' THEN
716 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
717 p_msg_name => 'OKC_XPRT_INV_PROCEDURE_VAR',
718 p_token1 => 'VARIABLE',
719 p_token1_value => l_variable_tbl(i),
720 p_token2 => 'PROCEDURE',
721 p_token2_value => l_procedure_name_tbl(i));
722 x_return_status := G_RET_STS_ERROR;
723 END IF;
724
725 -- If Procedure Spec/Body/API not found in DB then return error
726 IF csr_check_proc_spec_status%NOTFOUND OR csr_check_proc_body_status%NOTFOUND
727 OR csr_check_proc_exists%NOTFOUND THEN
728 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
729 p_msg_name => 'OKC_XPRT_NO_PROCEDURE_VAR',
730 p_token1 => 'VARIABLE',
731 p_token1_value => l_variable_tbl(i),
732 p_token2 => 'PROCEDURE',
733 p_token2_value => l_procedure_name_tbl(i));
734 x_return_status := G_RET_STS_ERROR;
735 END IF;
736
737 CLOSE csr_check_proc_spec_status;
738 CLOSE csr_check_proc_body_status;
739 CLOSE csr_check_proc_exists;
740
741 end if;
742 -- Above added for Bug 5958643
743
744 END LOOP;
745 l_variable_tbl.DELETE;
746 if x_return_status = G_RET_STS_ERROR then
747 RAISE FND_API.G_EXC_ERROR ;
748 end if;
749
750 END IF;
751 END IF;
752
753
754 -- An Article (being created at a global org) is being send for Approval at a
755 -- global org. Updates the status of the version
756
757 IF (p_current_org_id = G_GLOBAL_ORG_ID ) THEN
758 IF (l_debug = 'Y') THEN
759 okc_debug.log('100: Entered pending approval for global article at a global org', 2);
760 END IF;
761 OKC_ARTICLE_VERSIONS_PVT.Update_Row(
762 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
763 x_return_status => x_return_status,
764 p_article_version_id => p_article_version_id,
765 p_article_id => NULL,
766 p_article_version_number => NULL,
767 p_article_text => NULL,
768 p_provision_yn => NULL,
769 p_insert_by_reference => NULL,
770 p_lock_text => NULL,
771 p_global_yn => NULL,
772 p_article_language => NULL,
773 p_article_status => 'PENDING_APPROVAL',
774 p_sav_release => NULL,
775 p_start_date => NULL,
776 p_end_date => NULL,
777 p_std_article_version_id => NULL,
778 p_display_name => NULL,
779 p_translated_yn => NULL,
780 p_article_description => NULL,
781 p_date_approved => NULL,
782 p_default_section => NULL,
783 p_reference_source => NULL,
784 p_reference_text => NULL,
785 p_orig_system_reference_code => NULL,
786 p_orig_system_reference_id1 => NULL,
787 p_orig_system_reference_id2 => NULL,
788 p_additional_instructions => NULL,
789 p_variation_description => NULL,
790 p_current_org_id => p_current_org_id,
791 p_attribute_category => NULL,
792 p_attribute1 => NULL,
793 p_attribute2 => NULL,
794 p_attribute3 => NULL,
795 p_attribute4 => NULL,
796 p_attribute5 => NULL,
797 p_attribute6 => NULL,
798 p_attribute7 => NULL,
799 p_attribute8 => NULL,
800 p_attribute9 => NULL,
801 p_attribute10 => NULL,
802 p_attribute11 => NULL,
803 p_attribute12 => NULL,
804 p_attribute13 => NULL,
805 p_attribute14 => NULL,
806 p_attribute15 => NULL,
807 p_object_version_number => NULL,
808 x_article_status => l_article_status,
809 x_article_id => l_article_id,
810 x_earlier_version_id => l_earlier_version_id
811 );
812 ELSE
813 IF p_adopt_as_is_yn = 'Y' THEN
814
815 -- An Article (being created at a global org) is being send for Approval for
816 -- adoption as is at a local org. Updates the status of the adoption
817 -- Also inherits all relationships between this article and other global articles adopted in this org if this
818 -- is the first version being adopted.
819
820 IF (l_debug = 'Y') THEN
821 okc_debug.log('100: Entered pending approval for global article at a local org adopted as is', 2);
822 END IF;
823
824 OKC_ADOPTIONS_GRP.check_adoption_details(
825 p_api_version => p_api_version,
826 p_init_msg_list => p_init_msg_list,
827 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
828 x_return_status => x_return_status,
829 x_msg_count => x_msg_count,
830 x_msg_data => x_msg_data,
831 x_earlier_local_version_id => l_earlier_local_version_id,
832 p_global_article_version_id => p_article_version_id,
833 p_adoption_type => 'ADOPTED',
834 p_local_org_id => p_current_org_id);
835
836 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
838 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
839 RAISE FND_API.G_EXC_ERROR ;
840 END IF;
841
842 OKC_ARTICLE_ADOPTIONS_PVT.update_row(
843 x_return_status => x_return_status,
844 p_global_article_version_id => p_article_version_id,
845 p_adoption_type => 'ADOPTED',
846 p_local_org_id => p_current_org_id,
847 p_orig_local_version_id => NULL,
848 p_new_local_version_id => NULL,
849 p_adoption_status => 'PENDING_APPROVAL',
850 p_object_version_number => NULL
851 );
852 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
854 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
855 RAISE FND_API.G_EXC_ERROR ;
856 END IF;
857
858 INSERT INTO OKC_ARTICLE_RELATNS_ALL
859 (
860 SOURCE_ARTICLE_ID,
861 TARGET_ARTICLE_ID,
862 ORG_ID,
863 RELATIONSHIP_TYPE,
864 OBJECT_VERSION_NUMBER,
865 CREATED_BY,
866 CREATION_DATE,
867 LAST_UPDATED_BY,
868 LAST_UPDATE_LOGIN,
869 LAST_UPDATE_DATE
870 )
871 SELECT source_article_id,
872 target_article_id,
873 p_current_org_id,
874 RELATIONSHIP_TYPE,
875 1.0,
876 l_user_id,
877 sysdate,
878 l_user_id,
879 l_login_id,
880 sysdate
881 from OKC_ARTICLE_RELATNS_ALL ARL1
882 WHERE org_id = G_GLOBAL_ORG_ID
883 AND exists
884 (select 1 from okc_article_versions, okc_article_adoptions adp
885 where article_id = target_article_id
886 and adp.global_article_version_id = article_version_id
887 and adp.local_org_id = p_current_org_id
888 and adp.adoption_type = 'ADOPTED')
889 AND EXISTS
890 (select 1 from okc_article_versions
891 where article_version_id = p_article_version_id
892 and source_article_id = article_id)
893 AND NOT EXISTS
894 (select 1 from okc_article_relatns_all ARL2
895 where arl1.source_article_id = arl2.source_article_id
896 and arl1.target_article_id = arl2.target_article_id
897 and arl1.relationship_type = arl2.relationship_type
898 and arl2.org_id = p_current_org_id);
899
900 INSERT INTO OKC_ARTICLE_RELATNS_ALL
901 (
902 SOURCE_ARTICLE_ID,
903 TARGET_ARTICLE_ID,
904 ORG_ID,
905 RELATIONSHIP_TYPE,
906 OBJECT_VERSION_NUMBER,
907 CREATED_BY,
908 CREATION_DATE,
909 LAST_UPDATED_BY,
910 LAST_UPDATE_LOGIN,
911 LAST_UPDATE_DATE
912 )
913 SELECT source_article_id,
914 target_article_id,
915 p_current_org_id,
916 relationship_type,
917 1.0,
918 l_user_id,
919 sysdate,
920 l_user_id,
921 l_login_id,
922 sysdate
923 from OKC_ARTICLE_RELATNS_ALL ARL1
924 WHERE org_id = G_GLOBAL_ORG_ID
925 AND exists
926 (select 1 from okc_article_versions, okc_article_adoptions adp
927 where article_id = source_article_id
928 and adp.global_article_version_id = article_version_id
929 and adp.local_org_id = p_current_org_id
930 and adp.adoption_type = 'ADOPTED')
931 AND EXISTS
932 (select 1 from okc_article_versions
933 where article_version_id = p_article_version_id
934 and target_article_id = article_id)
935 AND NOT EXISTS
936 (select 1 from okc_article_relatns_all ARL2
937 where arl1.source_article_id = arl2.source_article_id
938 and arl1.target_article_id = arl2.target_article_id
939 and arl1.relationship_type = arl2.relationship_type
940 and arl2.org_id = p_current_org_id);
941 ELSE
942
943 -- An Article (being created at a local org) is being send for Approval at a
944 -- local org. Updates the status of the version.
945 IF (l_debug = 'Y') THEN
946 okc_debug.log('100: Entered pending approval for local article at a local org ', 2);
947 END IF;
948
949 OKC_ARTICLE_VERSIONS_PVT.Update_Row(
950 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
951 x_return_status => x_return_status,
952 p_article_version_id => p_article_version_id,
953 p_article_id => NULL,
954 p_article_version_number => NULL,
955 p_article_text => NULL,
956 p_provision_yn => NULL,
957 p_insert_by_reference => NULL,
958 p_lock_text => NULL,
959 p_global_yn => NULL,
960 p_article_language => NULL,
961 p_article_status => 'PENDING_APPROVAL',
962 p_sav_release => NULL,
963 p_start_date => NULL,
964 p_end_date => NULL,
965 p_std_article_version_id => NULL,
966 p_display_name => NULL,
967 p_translated_yn => NULL,
968 p_article_description => NULL,
969 p_date_approved => NULL,
970 p_default_section => NULL,
971 p_reference_source => NULL,
972 p_reference_text => NULL,
973 p_orig_system_reference_code => NULL,
974 p_orig_system_reference_id1 => NULL,
975 p_orig_system_reference_id2 => NULL,
976 p_additional_instructions => NULL,
977 p_variation_description => NULL,
978 p_current_org_id => p_current_org_id,
979 p_attribute_category => NULL,
980 p_attribute1 => NULL,
981 p_attribute2 => NULL,
982 p_attribute3 => NULL,
983 p_attribute4 => NULL,
984 p_attribute5 => NULL,
985 p_attribute6 => NULL,
986 p_attribute7 => NULL,
987 p_attribute8 => NULL,
988 p_attribute9 => NULL,
989 p_attribute10 => NULL,
990 p_attribute11 => NULL,
991 p_attribute12 => NULL,
992 p_attribute13 => NULL,
993 p_attribute14 => NULL,
994 p_attribute15 => NULL,
995 p_object_version_number => NULL,
996 x_article_status => l_article_status,
997 x_article_id => l_article_id,
998 x_earlier_version_id => l_earlier_version_id
999 );
1000 --------------------------------------------
1001 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1003 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1004 RAISE FND_API.G_EXC_ERROR ;
1005 END IF;
1006 --------------------------------------------
1007 -- check if the local article being approved is created as a localizaion from
1008 -- a global article. In that case update the adoption row.
1009 -- More efficient to use dirct DML than Simple API.
1010
1011 update_adoption_ifexists (p_article_version_id,
1012 p_current_org_id,
1013 'PENDING_APPROVAL',
1014 x_return_status );
1015
1016 END IF;
1017 END IF;
1018 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1020 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1021 RAISE FND_API.G_EXC_ERROR ;
1022 END IF;
1023
1024 IF (l_debug = 'Y') THEN
1025 okc_debug.log('200: Leaving pending approval successfully', 2);
1026 END IF;
1027
1028 EXCEPTION
1029 WHEN FND_API.G_EXC_ERROR THEN
1030 IF (l_debug = 'Y') THEN
1031 okc_debug.log('300: Leaving pending approval OKC_API.G_EXCEPTION_ERROR Exception', 2);
1032 END IF;
1033 x_return_status := G_RET_STS_ERROR ;
1034
1035 WHEN OTHERS THEN
1036 IF (l_debug = 'Y') THEN
1037 okc_debug.log('500: Leaving pending approval of EXCEPTION: '||sqlerrm, 2);
1038 END IF;
1039 x_return_status := G_RET_STS_UNEXP_ERROR ;
1040
1041 END pending_approval;
1042
1043 ---------------------------------------
1044 -- PROCEDURE approve
1045 ---------------------------------------
1046 -- Procedure to set an article version status from pending approval to approved.
1047 -- Parameters: article_version_id , p_adopt_as_is_yn => Y means Adoption at a
1048 -- Local Org as is and N means Local version
1049 -- This will be called from the UI only. So we can save db access to check
1050 -- if article version is global or Not.
1051
1052 PROCEDURE approve(
1053 p_api_version IN NUMBER,
1054 p_init_msg_list IN VARCHAR2 ,
1055
1056 x_return_status OUT NOCOPY VARCHAR2,
1057 x_msg_count OUT NOCOPY NUMBER,
1058 x_msg_data OUT NOCOPY VARCHAR2,
1059 p_current_org_id IN NUMBER,
1060 p_adopt_as_is_yn IN VARCHAR2,
1061 p_article_version_id IN NUMBER
1062 ) IS
1063 l_api_version CONSTANT NUMBER := 1;
1064 l_api_name CONSTANT VARCHAR2(30) := 'approve';
1065 l_global_article_version_id NUMBER := NULL;
1066 l_local_article_version_id NUMBER;
1067 l_rownotfound BOOLEAN := FALSE;
1068 l_delete_adoption VARCHAR2(1) := 'T';
1069 l_rowid ROWID;
1070 CURSOR l_article_version_csr (cp_article_version_id IN NUMBER) IS
1071 SELECT global_yn, org_id, art.article_id, start_date
1072 FROM okc_articles_all art, okc_article_versions ver
1073 WHERE ver.article_id = art.article_id
1074 AND ver.article_version_id = cp_article_version_id;
1075 l_article_version_rec l_article_version_csr%ROWTYPE;
1076 l_earlier_version_id NUMBER := NULL;
1077 l_article_id NUMBER := NULL;
1078 l_article_status VARCHAR2(30) := NULL;
1079 l_new_article_status VARCHAR2(30) := NULL;
1080 BEGIN
1081
1082 IF (l_debug = 'Y') THEN
1083 okc_debug.log('100: Entered approved with '||p_current_org_id ||'*'||p_adopt_as_is_yn||'*'||p_article_version_id, 2);
1084 END IF;
1085 x_return_status := G_RET_STS_SUCCESS;
1086
1087 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1088 FND_MSG_PUB.initialize;
1089 END IF;
1090 OPEN l_article_version_csr (p_article_version_id);
1091 FETCH l_article_version_csr INTO l_article_version_rec;
1092 l_rownotfound := l_article_version_csr%NOTFOUND;
1093 CLOSE l_article_version_csr;
1094 IF l_rownotfound THEN
1095 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_VERSION_ID');
1096 x_return_status := G_RET_STS_ERROR;
1097 return;
1098 END IF;
1099 -- Initialize API return status to success
1100 IF (p_current_org_id = G_GLOBAL_ORG_ID ) THEN
1101
1102 -- An Article (being created at a global org) is being Approved at a
1103 --global org. Updates the status of the version
1104 IF (l_debug = 'Y') THEN
1105 okc_debug.log('100: Entered approve of global article at a global org', 2);
1106 END IF;
1107
1108 OKC_ARTICLE_VERSIONS_PVT.Update_Row(
1109 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1110 x_return_status => x_return_status,
1111 p_article_version_id => p_article_version_id,
1112 p_article_id => NULL,
1113 p_article_version_number => NULL,
1114 p_article_text => NULL,
1115 p_provision_yn => NULL,
1116 p_insert_by_reference => NULL,
1117 p_lock_text => NULL,
1118 p_global_yn => NULL,
1119 p_article_language => NULL,
1120 p_article_status => 'APPROVED',
1121 p_sav_release => NULL,
1122 p_start_date => NULL,
1123 p_end_date => NULL,
1124 p_std_article_version_id => NULL,
1125 p_display_name => NULL,
1126 p_translated_yn => NULL,
1127 p_article_description => NULL,
1128 p_date_approved => sysdate,
1129 p_default_section => NULL,
1130 p_reference_source => NULL,
1131 p_reference_text => NULL,
1132 p_orig_system_reference_code => NULL,
1133 p_orig_system_reference_id1 => NULL,
1134 p_orig_system_reference_id2 => NULL,
1135 p_additional_instructions => NULL,
1136 p_variation_description => NULL,
1137 p_current_org_id => p_current_org_id ,
1138 p_attribute_category => NULL,
1139 p_attribute1 => NULL,
1140 p_attribute2 => NULL,
1141 p_attribute3 => NULL,
1142 p_attribute4 => NULL,
1143 p_attribute5 => NULL,
1144 p_attribute6 => NULL,
1145 p_attribute7 => NULL,
1146 p_attribute8 => NULL,
1147 p_attribute9 => NULL,
1148 p_attribute10 => NULL,
1149 p_attribute11 => NULL,
1150 p_attribute12 => NULL,
1151 p_attribute13 => NULL,
1152 p_attribute14 => NULL,
1153 p_attribute15 => NULL,
1154 p_object_version_number => NULL,
1155 x_article_status => l_article_status,
1156 x_article_id => l_article_id,
1157 x_earlier_version_id => l_earlier_version_id
1158 );
1159 --------------------------------------------
1160 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1162 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1163 RAISE FND_API.G_EXC_ERROR ;
1164 END IF;
1165 --------------------------------------------
1166 -- Update the end date of the previous version as a second less than the current
1167 -- version unless there is an existing end date
1168
1169 update_prev_vers_enddate (p_article_version_id ,
1170 l_article_version_rec.article_id,
1171 l_article_version_rec.start_date - 1/86400,
1172 p_current_org_id,
1173 x_return_status );
1174 --------------------------------------------
1175 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1177 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1178 RAISE FND_API.G_EXC_ERROR ;
1179 END IF;
1180 --------------------------------------------
1181
1182 -- If a global article is approved at a global org it will trigger
1183 -- autoadoption of the article for all orgs that have subscribed to Articles.
1184 -- Adoption row will be created as ADOPTED or PENDING APPROVAL depending upon
1185 -- how the org has been set up in HR Orgs for auto-adoption
1186
1187 IF l_article_version_rec.global_YN = 'Y' THEN
1188 IF (l_debug = 'Y') THEN
1189 okc_debug.log('100: Calling Auto Adoption ', 2);
1190 END IF;
1191 OKC_ADOPTIONS_GRP.AUTO_ADOPT_ARTICLES
1192 (
1193 p_api_version => 1.0,
1194 p_init_msg_list => p_init_msg_list,
1195 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1196 p_commit => FND_API.G_FALSE,
1197 x_return_status => x_return_status,
1198 x_msg_count => x_msg_count,
1199 x_msg_data => x_msg_data,
1200 p_relationship_yn => 'Y',
1201 p_adoption_yn => 'Y',
1202 p_fetchsize => 100,
1203 p_global_article_id => l_article_version_rec.article_id,
1204 p_global_article_version_id => p_article_version_id
1205 );
1206 END IF;
1207 ELSIF (l_article_version_rec.org_id = G_GLOBAL_ORG_ID ) THEN
1208
1209 -- An Article (being created at a global org) is being Approved for
1210 -- "adopted as is" at a local org. Updates the status of the adoption
1211
1212 IF (l_debug = 'Y') THEN
1213 okc_debug.log('100: Entered approve of global article adopted as is at a local org', 2);
1214 END IF;
1215
1216 OKC_ARTICLE_ADOPTIONS_PVT.update_row(
1217 x_return_status => x_return_status,
1218 p_global_article_version_id => p_article_version_id,
1219 p_adoption_type => 'ADOPTED',
1220 p_local_org_id => p_current_org_id ,
1221 p_orig_local_version_id => NULL,
1222 p_new_local_version_id => NULL,
1223 p_adoption_status => 'APPROVED',
1224 p_object_version_number => NULL
1225 );
1226 ELSE
1227
1228 -- An Article (being created at a local org) is being Approved at a local org.
1229 -- Updates the status of the version
1230
1231 IF (l_debug = 'Y') THEN
1232 okc_debug.log('100: Entered approve of local article at a local org', 2);
1233 END IF;
1234
1235 OKC_ARTICLE_VERSIONS_PVT.Update_Row(
1236 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1237 x_return_status => x_return_status,
1238 p_article_version_id => p_article_version_id,
1239 p_article_id => NULL,
1240 p_article_version_number => NULL,
1241 p_article_text => NULL,
1242 p_provision_yn => NULL,
1243 p_insert_by_reference => NULL,
1244 p_lock_text => NULL,
1245 p_global_yn => NULL,
1246 p_article_language => NULL,
1247 p_article_status => 'APPROVED',
1248 p_sav_release => NULL,
1249 p_start_date => NULL,
1250 p_end_date => NULL,
1251 p_std_article_version_id => NULL,
1252 p_display_name => NULL,
1253 p_translated_yn => NULL,
1254 p_article_description => NULL,
1255 p_date_approved => sysdate,
1256 p_default_section => NULL,
1257 p_reference_source => NULL,
1258 p_reference_text => NULL,
1259 p_orig_system_reference_code => NULL,
1260 p_orig_system_reference_id1 => NULL,
1261 p_orig_system_reference_id2 => NULL,
1262 p_additional_instructions => NULL,
1263 p_variation_description => NULL,
1264 p_current_org_id => p_current_org_id ,
1265 p_attribute_category => NULL,
1266 p_attribute1 => NULL,
1267 p_attribute2 => NULL,
1268 p_attribute3 => NULL,
1269 p_attribute4 => NULL,
1270 p_attribute5 => NULL,
1271 p_attribute6 => NULL,
1272 p_attribute7 => NULL,
1273 p_attribute8 => NULL,
1274 p_attribute9 => NULL,
1275 p_attribute10 => NULL,
1276 p_attribute11 => NULL,
1277 p_attribute12 => NULL,
1278 p_attribute13 => NULL,
1279 p_attribute14 => NULL,
1280 p_attribute15 => NULL,
1281 p_object_version_number => NULL,
1282 x_article_status => l_article_status,
1283 x_article_id => l_article_id,
1284 x_earlier_version_id => l_earlier_version_id
1285 );
1286 --------------------------------------------
1287 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1289 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1290 RAISE FND_API.G_EXC_ERROR ;
1291 END IF;
1292 --------------------------------------------
1293
1294 -- Update the end date of the previous version as a second less than the current
1295 -- version unless there is an existing end date
1296
1297 update_prev_vers_enddate (p_article_version_id ,
1298 l_article_version_rec.article_id,
1299 l_article_version_rec.start_date - 1/86400,
1300 p_current_org_id,
1301 x_return_status );
1302 --------------------------------------------
1303 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1305 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1306 RAISE FND_API.G_EXC_ERROR ;
1307 END IF;
1308 --------------------------------------------
1309 -- check if the local article being approved is created as a localizaion from
1310 -- a global article. In that case update the adoption row.
1311 -- More efficient to use dirct DML than Simple API.
1312
1313 update_adoption_ifexists (p_article_version_id,
1314 p_current_org_id ,
1315 'APPROVED',
1316 x_return_status );
1317 --------------------------------------------
1318 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1320 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1321 RAISE FND_API.G_EXC_ERROR ;
1322 END IF;
1323 --------------------------------------------
1324
1325 END IF;
1326 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1328 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1329 RAISE FND_API.G_EXC_ERROR ;
1330 END IF;
1331
1332 IF (l_debug = 'Y') THEN
1333 okc_debug.log('200: Leaving approve successfully', 2);
1334 END IF;
1335
1336 EXCEPTION
1337 WHEN FND_API.G_EXC_ERROR THEN
1338 IF (l_debug = 'Y') THEN
1339 okc_debug.log('300: Leaving approve because of OKC_API.G_EXCEPTION_ERROR Exception', 2);
1340 END IF;
1341 x_return_status := G_RET_STS_ERROR ;
1342
1343 WHEN OTHERS THEN
1344 IF (l_debug = 'Y') THEN
1345 okc_debug.log('500: Leaving approve because of EXCEPTION: '||sqlerrm, 2);
1346 END IF;
1347 IF l_article_version_csr%ISOPEN THEN
1348 CLOSE l_article_version_csr;
1349 END IF;
1350 x_return_status := G_RET_STS_UNEXP_ERROR ;
1351
1352 END approve;
1353 ---------------------------------------
1354 -- PROCEDURE reject
1355 ---------------------------------------
1356 -- Procedure to set an article version status from pending approval to rejected.
1357 -- Parameters: article_version_id , p_adopt_as_is_yn => Y means Adoption at a
1358 -- Local Org as is and N means Local version
1359 -- This will be called from the UI only. So we can save db access to check
1360 -- if article version is global or Not.
1361
1362 PROCEDURE reject(
1363 p_api_version IN NUMBER,
1364 p_init_msg_list IN VARCHAR2 ,
1365
1366 x_return_status OUT NOCOPY VARCHAR2,
1367 x_msg_count OUT NOCOPY NUMBER,
1368 x_msg_data OUT NOCOPY VARCHAR2,
1369 p_current_org_id IN NUMBER,
1370 p_adopt_as_is_yn IN VARCHAR2,
1371 p_article_version_id IN NUMBER
1372 ) IS
1373 l_api_version CONSTANT NUMBER := 1;
1374 l_api_name CONSTANT VARCHAR2(30) := 'reject';
1375 l_global_article_version_id NUMBER := NULL;
1376 l_local_article_version_id NUMBER;
1377 l_rownotfound BOOLEAN := FALSE;
1378 l_delete_adoption VARCHAR2(1) := 'T';
1379 l_rowid ROWID;
1380 CURSOR l_article_version_csr (cp_article_version_id IN NUMBER) IS
1381 SELECT global_yn, org_id, art.article_id, start_date
1382 FROM okc_articles_all art, okc_article_versions ver
1383 WHERE ver.article_id = art.article_id
1384 AND ver.article_version_id = cp_article_version_id;
1385 l_article_version_rec l_article_version_csr%ROWTYPE;
1386 l_earlier_version_id NUMBER := NULL;
1387 l_article_id NUMBER := NULL;
1388 l_article_status VARCHAR2(30) := NULL;
1389 l_new_article_status VARCHAR2(30) := NULL;
1390 BEGIN
1391
1392 IF (l_debug = 'Y') THEN
1393 okc_debug.log('100: Entered reject with '||p_current_org_id ||'*'||p_adopt_as_is_yn||'*'||p_article_version_id, 2);
1394 END IF;
1395 x_return_status := G_RET_STS_SUCCESS;
1396
1397 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1398 FND_MSG_PUB.initialize;
1399 END IF;
1400 OPEN l_article_version_csr (p_article_version_id);
1401 FETCH l_article_version_csr INTO l_article_version_rec;
1402 l_rownotfound := l_article_version_csr%NOTFOUND;
1403 CLOSE l_article_version_csr;
1404 IF l_rownotfound THEN
1405 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_VERSION_ID');
1406 x_return_status := G_RET_STS_ERROR;
1407 return;
1408 END IF;
1409 -- Initialize API return status to success
1410
1411 -- An Article (being created at a global org) is being Rejected at a
1412 --global org. Updates the status of the version
1413
1414 IF (p_current_org_id = G_GLOBAL_ORG_ID ) THEN
1415 IF (l_debug = 'Y') THEN
1416 okc_debug.log('100: Entered reject of global article at a global org', 2);
1417 END IF;
1418 OKC_ARTICLE_VERSIONS_PVT.Update_Row(
1419 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1420 x_return_status => x_return_status,
1421 p_article_version_id => p_article_version_id,
1422 p_article_id => NULL,
1423 p_article_version_number => NULL,
1424 p_article_text => NULL,
1425 p_provision_yn => NULL,
1426 p_insert_by_reference => NULL,
1427 p_lock_text => NULL,
1428 p_global_yn => NULL,
1429 p_article_language => NULL,
1430 p_article_status => 'REJECTED',
1431 p_sav_release => NULL,
1432 p_start_date => NULL,
1433 p_end_date => NULL,
1434 p_std_article_version_id => NULL,
1435 p_display_name => NULL,
1436 p_translated_yn => NULL,
1437 p_article_description => NULL,
1438 p_date_approved => NULL,
1439 p_default_section => NULL,
1440 p_reference_source => NULL,
1441 p_reference_text => NULL,
1442 p_orig_system_reference_code => NULL,
1443 p_orig_system_reference_id1 => NULL,
1444 p_orig_system_reference_id2 => NULL,
1445 p_additional_instructions => NULL,
1446 p_variation_description => NULL,
1447 p_current_org_id => p_current_org_id ,
1448 p_attribute_category => NULL,
1449 p_attribute1 => NULL,
1450 p_attribute2 => NULL,
1451 p_attribute3 => NULL,
1452 p_attribute4 => NULL,
1453 p_attribute5 => NULL,
1454 p_attribute6 => NULL,
1455 p_attribute7 => NULL,
1456 p_attribute8 => NULL,
1457 p_attribute9 => NULL,
1458 p_attribute10 => NULL,
1459 p_attribute11 => NULL,
1460 p_attribute12 => NULL,
1461 p_attribute13 => NULL,
1462 p_attribute14 => NULL,
1463 p_attribute15 => NULL,
1464 p_object_version_number => NULL,
1465 x_article_status => l_article_status,
1466 x_article_id => l_article_id,
1467 x_earlier_version_id => l_earlier_version_id
1468 );
1469 --------------------------------------------
1470 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1472 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1473 RAISE FND_API.G_EXC_ERROR ;
1474 END IF;
1475 --------------------------------------------
1476 ELSIF (l_article_version_rec.org_id = G_GLOBAL_ORG_ID ) THEN
1477
1478 -- An Article (being created at a global org) is being Rejected for
1479 -- "Adopted As Is" at a local org. Updates the status of the adoption.
1480 -- The Article becomes Pending Adoption and all global relationships are deleted if this is the first version being adopted.
1481
1482 IF (l_debug = 'Y') THEN
1483 okc_debug.log('100: Entered reject of global article adopted as is at a local org', 2);
1484 END IF;
1485
1486 OKC_ARTICLE_ADOPTIONS_PVT.update_row(
1487 x_return_status => x_return_status,
1488 p_global_article_version_id => p_article_version_id,
1489 p_adoption_type => 'AVAILABLE',
1490 p_local_org_id => p_current_org_id ,
1491 p_orig_local_version_id => NULL,
1492 p_new_local_version_id => NULL,
1493 p_adoption_status => 'REJECTED',
1494 p_object_version_number => NULL
1495 );
1496 --------------------------------------------
1497 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1499 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1500 RAISE FND_API.G_EXC_ERROR ;
1501 END IF;
1502 --------------------------------------------
1503 DELETE FROM OKC_ARTICLE_RELATNS_ALL
1504 WHERE source_article_id = l_article_version_rec.article_id
1505 AND org_id = p_current_org_id
1506 AND NOT EXISTS
1507 (SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP, OKC_ARTICLE_VERSIONS AV
1508 WHERE ARTICLE_ID = l_article_version_rec.article_id
1509 AND ARTICLE_VERSION_ID <> p_article_version_id
1510 AND ADP.ADOPTION_TYPE = 'ADOPTED'
1511 AND global_article_version_id = article_version_id
1512 );
1513
1514 DELETE FROM OKC_ARTICLE_RELATNS_ALL
1515 WHERE target_article_id = l_article_version_rec.article_id
1516 AND org_id = p_current_org_id
1517 AND NOT EXISTS
1518 (SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP, OKC_ARTICLE_VERSIONS AV
1519 WHERE ARTICLE_ID = l_article_version_rec.article_id
1520 AND ARTICLE_VERSION_ID <> p_article_version_id
1521 AND ADP.ADOPTION_TYPE = 'ADOPTED'
1522 AND global_article_version_id = article_version_id
1523 );
1524
1525 ELSE
1526
1527 -- An Article (being created at a local org) is being Rejected at a
1528 --local org. Updates the status of the version
1529 IF (l_debug = 'Y') THEN
1530 okc_debug.log('100: Entered reject of local article at a local org', 2);
1531 END IF;
1532
1533 OKC_ARTICLE_VERSIONS_PVT.Update_Row(
1534 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1535 x_return_status => x_return_status,
1536 p_article_version_id => p_article_version_id,
1537 p_article_id => NULL,
1538 p_article_version_number => NULL,
1539 p_article_text => NULL,
1540 p_provision_yn => NULL,
1541 p_insert_by_reference => NULL,
1542 p_lock_text => NULL,
1543 p_global_yn => NULL,
1544 p_article_language => NULL,
1545 p_article_status => 'REJECTED',
1546 p_sav_release => NULL,
1547 p_start_date => NULL,
1548 p_end_date => NULL,
1549 p_std_article_version_id => NULL,
1550 p_display_name => NULL,
1551 p_translated_yn => NULL,
1552 p_article_description => NULL,
1553 p_date_approved => NULL,
1554 p_default_section => NULL,
1555 p_reference_source => NULL,
1556 p_reference_text => NULL,
1557 p_orig_system_reference_code => NULL,
1558 p_orig_system_reference_id1 => NULL,
1559 p_orig_system_reference_id2 => NULL,
1560 p_additional_instructions => NULL,
1561 p_variation_description => NULL,
1562 p_current_org_id => p_current_org_id ,
1563 p_attribute_category => NULL,
1564 p_attribute1 => NULL,
1565 p_attribute2 => NULL,
1566 p_attribute3 => NULL,
1567 p_attribute4 => NULL,
1568 p_attribute5 => NULL,
1569 p_attribute6 => NULL,
1570 p_attribute7 => NULL,
1571 p_attribute8 => NULL,
1572 p_attribute9 => NULL,
1573 p_attribute10 => NULL,
1574 p_attribute11 => NULL,
1575 p_attribute12 => NULL,
1576 p_attribute13 => NULL,
1577 p_attribute14 => NULL,
1578 p_attribute15 => NULL,
1579 p_object_version_number => NULL,
1580 x_article_status => l_article_status,
1581 x_article_id => l_article_id,
1582 x_earlier_version_id => l_earlier_version_id
1583 );
1584 --------------------------------------------
1585 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1587 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1588 RAISE FND_API.G_EXC_ERROR ;
1589 END IF;
1590 --------------------------------------------
1591
1592 -- check if the local article being rejected is created as a localizaion from
1593 -- a global article. In that case update the adoption row.
1594 -- More efficient to use direct DML than Simple API.
1595
1596 update_adoption_ifexists (p_article_version_id,
1597 p_current_org_id ,
1598 'REJECTED',
1599 x_return_status );
1600 --------------------------------------------
1601 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1603 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1604 RAISE FND_API.G_EXC_ERROR ;
1605 END IF;
1606
1607 END IF;
1608 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1610 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1611 RAISE FND_API.G_EXC_ERROR ;
1612 END IF;
1613
1614 IF (l_debug = 'Y') THEN
1615 okc_debug.log('200: Leaving check adoption', 2);
1616 END IF;
1617
1618 EXCEPTION
1619 WHEN FND_API.G_EXC_ERROR THEN
1620 IF (l_debug = 'Y') THEN
1621 okc_debug.log('300: Leaving delete_Adoption: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1622 END IF;
1623 x_return_status := G_RET_STS_ERROR ;
1624
1625 WHEN OTHERS THEN
1626 IF (l_debug = 'Y') THEN
1627 okc_debug.log('500: Leaving delete_Adoption because of EXCEPTION: '||sqlerrm, 2);
1628 END IF;
1629 IF l_article_version_csr%ISOPEN THEN
1630 CLOSE l_article_version_csr;
1631 END IF;
1632 x_return_status := G_RET_STS_UNEXP_ERROR ;
1633
1634 END reject;
1635 -- MOAC
1636 /*
1637 BEGIN
1638 OPEN cur_org_csr;
1639 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
1640 CLOSE cur_org_csr;
1641 */
1642
1643 END OKC_ARTICLE_STATUS_CHANGE_PVT;