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