[Home] [Help]
Skip to content
PACKAGE BODY: APPS.OKC_ARTICLE_VERSIONS_PVT
Source
1 PACKAGE BODY okc_article_versions_pvt AS
2 /* $Header: OKCVAVNB.pls 120.12.12020000.2 2012/07/16 16:16:58 harchand 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 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
11 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
12 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
13 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
14 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
15 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
16 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
17 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
18 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
19 ---------------------------------------------------------------------------
20 -- VALIDATION LEVELS
21 ---------------------------------------------------------------------------
22 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
23 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
24 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
25 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
26 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
27 ---------------------------------------------------------------------------
28 -- GLOBAL VARIABLES
29 ---------------------------------------------------------------------------
30 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_ARTICLE_VERSIONS_PVT';
31 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
32
33 ------------------------------------------------------------------------------
34 -- GLOBAL CONSTANTS
35 ------------------------------------------------------------------------------
36 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
37 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
38 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
39 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
40 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
41
42 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
43 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
44 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
45
46 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
47 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
48 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
49 -- MOAC
50 -- G_CURRENT_ORG_ID NUMBER := -99;
51 G_CURRENT_ORG_ID NUMBER ;
52
53 G_GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
54 -- MOAC
55 -- One Time fetch the current Org.
56 /*
57 CURSOR CUR_ORG_CSR IS
58 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
59 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
60 FROM DUAL;
61 */
62
63 ---------------------------------------------------------------------------
64 -- FUNCTION get_seq_id
65 ---------------------------------------------------------------------------
66 FUNCTION Get_Seq_Id (
67 p_article_version_id IN NUMBER,
68 x_article_version_id OUT NOCOPY NUMBER
69 ) RETURN VARCHAR2 IS
70 CURSOR l_seq_csr IS
71 SELECT OKC_ARTICLE_VERSIONS_S1.NEXTVAL FROM DUAL;
72 BEGIN
73 IF (l_debug = 'Y') THEN
74 Okc_Debug.Log('100: Entered get_seq_id', 2);
75 END IF;
76
77 IF( p_article_version_id IS NULL ) THEN
78 OPEN l_seq_csr;
79 FETCH l_seq_csr INTO x_article_version_id ;
80 IF l_seq_csr%NOTFOUND THEN
81 RAISE NO_DATA_FOUND;
82 END IF;
83 CLOSE l_seq_csr;
84 END IF;
85
86 IF (l_debug = 'Y') THEN
87 Okc_Debug.Log('200: Leaving get_seq_id', 2);
88 END IF;
89 RETURN G_RET_STS_SUCCESS;
90 EXCEPTION
91 WHEN OTHERS THEN
92
93 IF (l_debug = 'Y') THEN
94 Okc_Debug.Log('300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm, 2);
95 END IF;
96
97 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
98 p_msg_name => G_UNEXPECTED_ERROR,
99 p_token1 => G_SQLCODE_TOKEN,
100 p_token1_value => sqlcode,
101 p_token2 => G_SQLERRM_TOKEN,
102 p_token2_value => sqlerrm);
103
104 IF l_seq_csr%ISOPEN THEN
105 CLOSE l_seq_csr;
106 END IF;
107
108 RETURN G_RET_STS_UNEXP_ERROR ;
109
110 END Get_Seq_Id;
111
112 ---------------------------------------------------------------------------
113 -- FUNCTION get_rec for: OKC_ARTICLE_VERSIONS
114 ---------------------------------------------------------------------------
115 FUNCTION Get_Rec (
116 p_article_version_id IN NUMBER,
117
118 x_article_id OUT NOCOPY NUMBER,
119 x_article_version_number OUT NOCOPY NUMBER,
120 x_article_text OUT NOCOPY CLOB,
121 x_provision_yn OUT NOCOPY VARCHAR2,
122 x_insert_by_reference OUT NOCOPY VARCHAR2,
123 x_lock_text OUT NOCOPY VARCHAR2,
124 x_global_yn OUT NOCOPY VARCHAR2,
125 x_article_language OUT NOCOPY VARCHAR2,
126 x_article_status OUT NOCOPY VARCHAR2,
127 x_sav_release OUT NOCOPY VARCHAR2,
128 x_start_date OUT NOCOPY DATE,
129 x_end_date OUT NOCOPY DATE,
130 x_std_article_version_id OUT NOCOPY NUMBER,
131 x_display_name OUT NOCOPY VARCHAR2,
132 x_translated_yn OUT NOCOPY VARCHAR2,
133 x_article_description OUT NOCOPY VARCHAR2,
134 x_date_approved OUT NOCOPY DATE,
135 x_default_section OUT NOCOPY VARCHAR2,
136 x_reference_source OUT NOCOPY VARCHAR2,
137 x_reference_text OUT NOCOPY VARCHAR2,
138 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
139 x_orig_system_reference_id1 OUT NOCOPY VARCHAR2,
140 x_orig_system_reference_id2 OUT NOCOPY VARCHAR2,
141 x_additional_instructions OUT NOCOPY VARCHAR2,
142 x_variation_description OUT NOCOPY VARCHAR2,
143 x_date_published OUT NOCOPY DATE,
144 x_program_id OUT NOCOPY NUMBER,
145 x_program_login_id OUT NOCOPY NUMBER,
146 x_program_application_id OUT NOCOPY NUMBER,
147 x_request_id OUT NOCOPY NUMBER,
148 x_attribute_category OUT NOCOPY VARCHAR2,
149 x_attribute1 OUT NOCOPY VARCHAR2,
150 x_attribute2 OUT NOCOPY VARCHAR2,
151 x_attribute3 OUT NOCOPY VARCHAR2,
152 x_attribute4 OUT NOCOPY VARCHAR2,
153 x_attribute5 OUT NOCOPY VARCHAR2,
154 x_attribute6 OUT NOCOPY VARCHAR2,
155 x_attribute7 OUT NOCOPY VARCHAR2,
156 x_attribute8 OUT NOCOPY VARCHAR2,
157 x_attribute9 OUT NOCOPY VARCHAR2,
158 x_attribute10 OUT NOCOPY VARCHAR2,
159 x_attribute11 OUT NOCOPY VARCHAR2,
160 x_attribute12 OUT NOCOPY VARCHAR2,
161 x_attribute13 OUT NOCOPY VARCHAR2,
162 x_attribute14 OUT NOCOPY VARCHAR2,
163 x_attribute15 OUT NOCOPY VARCHAR2,
164 x_object_version_number OUT NOCOPY NUMBER,
165 x_edited_in_word OUT NOCOPY VARCHAR2,
166 x_article_text_in_word OUT NOCOPY BLOB,
167 x_created_by OUT NOCOPY NUMBER,
168 x_creation_date OUT NOCOPY DATE,
169 x_last_updated_by OUT NOCOPY NUMBER,
170 x_last_update_login OUT NOCOPY NUMBER,
171 x_last_update_date OUT NOCOPY DATE,
172 x_variable_code OUT NOCOPY VARCHAR2 --CLM
173
174 ) RETURN VARCHAR2 IS
175 CURSOR OKC_ARTICLE_VERSIONS_pk_csr (cp_article_version_id IN NUMBER) IS
176 SELECT
177 ARTICLE_ID,
178 ARTICLE_VERSION_NUMBER,
179 ARTICLE_TEXT,
180 PROVISION_YN,
181 INSERT_BY_REFERENCE,
182 LOCK_TEXT,
183 GLOBAL_YN,
184 ARTICLE_LANGUAGE,
185 ARTICLE_STATUS,
186 SAV_RELEASE,
187 START_DATE,
188 END_DATE,
189 STD_ARTICLE_VERSION_ID,
190 DISPLAY_NAME,
191 TRANSLATED_YN,
192 ARTICLE_DESCRIPTION,
193 DATE_APPROVED,
194 DEFAULT_SECTION,
195 REFERENCE_SOURCE,
196 REFERENCE_TEXT,
197 ORIG_SYSTEM_REFERENCE_CODE,
198 ORIG_SYSTEM_REFERENCE_ID1,
199 ORIG_SYSTEM_REFERENCE_ID2,
200 ADDITIONAL_INSTRUCTIONS,
201 VARIATION_DESCRIPTION,
202 DATE_PUBLISHED,
203 PROGRAM_ID,
204 PROGRAM_LOGIN_ID,
205 PROGRAM_APPLICATION_ID,
206 REQUEST_ID,
207 ATTRIBUTE_CATEGORY,
208 ATTRIBUTE1,
209 ATTRIBUTE2,
210 ATTRIBUTE3,
211 ATTRIBUTE4,
212 ATTRIBUTE5,
213 ATTRIBUTE6,
214 ATTRIBUTE7,
215 ATTRIBUTE8,
216 ATTRIBUTE9,
217 ATTRIBUTE10,
218 ATTRIBUTE11,
219 ATTRIBUTE12,
220 ATTRIBUTE13,
221 ATTRIBUTE14,
222 ATTRIBUTE15,
223 OBJECT_VERSION_NUMBER,
224 EDITED_IN_WORD,
225 ARTICLE_TEXT_IN_WORD,
226 CREATED_BY,
227 CREATION_DATE,
228 LAST_UPDATED_BY,
229 LAST_UPDATE_LOGIN,
230 LAST_UPDATE_DATE,
231 VARIABLE_CODE
232 FROM OKC_ARTICLE_VERSIONS t
233 WHERE t.ARTICLE_VERSION_ID = cp_article_version_id;
234 BEGIN
235
236 IF (l_debug = 'Y') THEN
237 Okc_Debug.Log('400: Entered get_rec', 2);
238 END IF;
239
240 -- Get current database values
241 OPEN OKC_ARTICLE_VERSIONS_pk_csr (p_article_version_id);
242 FETCH OKC_ARTICLE_VERSIONS_pk_csr INTO
243 x_article_id,
244 x_article_version_number,
245 x_article_text,
246 x_provision_yn,
247 x_insert_by_reference,
248 x_lock_text,
249 x_global_yn,
250 x_article_language,
251 x_article_status,
252 x_sav_release,
253 x_start_date,
254 x_end_date,
255 x_std_article_version_id,
256 x_display_name,
257 x_translated_yn,
258 x_article_description,
259 x_date_approved,
260 x_default_section,
261 x_reference_source,
262 x_reference_text,
263 x_orig_system_reference_code,
264 x_orig_system_reference_id1,
265 x_orig_system_reference_id2,
266 x_additional_instructions,
267 x_variation_description,
268 x_date_published,
269 x_program_id,
270 x_program_login_id,
271 x_program_application_id,
272 x_request_id,
273 x_attribute_category,
274 x_attribute1,
275 x_attribute2,
276 x_attribute3,
277 x_attribute4,
278 x_attribute5,
279 x_attribute6,
280 x_attribute7,
281 x_attribute8,
282 x_attribute9,
283 x_attribute10,
284 x_attribute11,
285 x_attribute12,
286 x_attribute13,
287 x_attribute14,
288 x_attribute15,
289 x_object_version_number,
290 x_edited_in_word,
291 x_article_text_in_word,
292 x_created_by,
293 x_creation_date,
294 x_last_updated_by,
295 x_last_update_login,
296 x_last_update_date,
297 x_variable_code;
298 IF OKC_ARTICLE_VERSIONS_pk_csr%NOTFOUND THEN
299 RAISE NO_DATA_FOUND;
300 END IF;
301 CLOSE OKC_ARTICLE_VERSIONS_pk_csr;
302
303 IF (l_debug = 'Y') THEN
304 Okc_Debug.Log('500: Leaving get_rec ', 2);
305 END IF;
306
307 RETURN G_RET_STS_SUCCESS ;
308
309 EXCEPTION
310 WHEN OTHERS THEN
311
312 IF (l_debug = 'Y') THEN
313 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
314 END IF;
315
316 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
317 p_msg_name => G_UNEXPECTED_ERROR,
318 p_token1 => G_SQLCODE_TOKEN,
319 p_token1_value => sqlcode,
320 p_token2 => G_SQLERRM_TOKEN,
321 p_token2_value => sqlerrm);
322
323 IF OKC_ARTICLE_VERSIONS_pk_csr%ISOPEN THEN
324 CLOSE OKC_ARTICLE_VERSIONS_pk_csr;
325 END IF;
326
327 RETURN G_RET_STS_UNEXP_ERROR ;
328
329 END Get_Rec;
330
331 -----------------------------------------
332 -- Set_Attributes for:OKC_ARTICLE_VERSIONS --
333 -----------------------------------------
334 FUNCTION Set_Attributes(
335 p_article_version_id IN NUMBER,
336 p_article_id IN NUMBER,
337 p_article_version_number IN NUMBER,
338 p_article_text IN CLOB,
339 p_provision_yn IN VARCHAR2,
340 p_insert_by_reference IN VARCHAR2,
341 p_lock_text IN VARCHAR2,
342 p_global_yn IN VARCHAR2,
343 p_article_language IN VARCHAR2,
344 p_article_status IN VARCHAR2,
345 p_sav_release IN VARCHAR2,
346 p_start_date IN DATE,
347 p_end_date IN DATE,
348 p_std_article_version_id IN NUMBER,
349 p_display_name IN VARCHAR2,
350 p_translated_yn IN VARCHAR2,
351 p_article_description IN VARCHAR2,
352 p_date_approved IN DATE,
353 p_default_section IN VARCHAR2,
354 p_reference_source IN VARCHAR2,
355 p_reference_text IN VARCHAR2,
356 p_orig_system_reference_code IN VARCHAR2,
357 p_orig_system_reference_id1 IN VARCHAR2,
358 p_orig_system_reference_id2 IN VARCHAR2,
359 p_additional_instructions IN VARCHAR2,
360 p_variation_description IN VARCHAR2,
361 p_date_published IN DATE,
362 p_program_id IN NUMBER,
363 p_program_login_id IN NUMBER,
364 p_program_application_id IN NUMBER,
365 p_request_id IN NUMBER,
366 p_attribute_category IN VARCHAR2,
367 p_attribute1 IN VARCHAR2,
368 p_attribute2 IN VARCHAR2,
369 p_attribute3 IN VARCHAR2,
370 p_attribute4 IN VARCHAR2,
371 p_attribute5 IN VARCHAR2,
372 p_attribute6 IN VARCHAR2,
373 p_attribute7 IN VARCHAR2,
374 p_attribute8 IN VARCHAR2,
375 p_attribute9 IN VARCHAR2,
376 p_attribute10 IN VARCHAR2,
377 p_attribute11 IN VARCHAR2,
378 p_attribute12 IN VARCHAR2,
379 p_attribute13 IN VARCHAR2,
380 p_attribute14 IN VARCHAR2,
381 p_attribute15 IN VARCHAR2,
382 p_object_version_number IN NUMBER,
383 p_edited_in_word IN VARCHAR2,
384 p_article_text_in_word IN BLOB,
385 p_variable_code IN VARCHAR2, --clm
386
387 x_article_id OUT NOCOPY NUMBER,
388 x_article_version_number OUT NOCOPY NUMBER,
389 x_article_text OUT NOCOPY CLOB,
390 x_provision_yn OUT NOCOPY VARCHAR2,
391 x_insert_by_reference OUT NOCOPY VARCHAR2,
392 x_lock_text OUT NOCOPY VARCHAR2,
393 x_global_yn OUT NOCOPY VARCHAR2,
394 x_article_language OUT NOCOPY VARCHAR2,
395 x_article_status OUT NOCOPY VARCHAR2,
396 x_sav_release OUT NOCOPY VARCHAR2,
397 x_start_date OUT NOCOPY DATE,
398 x_end_date OUT NOCOPY DATE,
399 x_std_article_version_id OUT NOCOPY NUMBER,
400 x_display_name OUT NOCOPY VARCHAR2,
401 x_translated_yn OUT NOCOPY VARCHAR2,
402 x_article_description OUT NOCOPY VARCHAR2,
403 x_date_approved OUT NOCOPY DATE,
404 x_default_section OUT NOCOPY VARCHAR2,
405 x_reference_source OUT NOCOPY VARCHAR2,
406 x_reference_text OUT NOCOPY VARCHAR2,
407 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
408 x_orig_system_reference_id1 OUT NOCOPY VARCHAR2,
409 x_orig_system_reference_id2 OUT NOCOPY VARCHAR2,
410 x_additional_instructions OUT NOCOPY VARCHAR2,
411 x_variation_description OUT NOCOPY VARCHAR2,
412 x_date_published OUT NOCOPY DATE,
413 x_program_id OUT NOCOPY NUMBER,
414 x_program_login_id OUT NOCOPY NUMBER,
415 x_program_application_id OUT NOCOPY NUMBER,
416 x_request_id OUT NOCOPY NUMBER,
417 x_attribute_category OUT NOCOPY VARCHAR2,
418 x_attribute1 OUT NOCOPY VARCHAR2,
419 x_attribute2 OUT NOCOPY VARCHAR2,
420 x_attribute3 OUT NOCOPY VARCHAR2,
421 x_attribute4 OUT NOCOPY VARCHAR2,
422 x_attribute5 OUT NOCOPY VARCHAR2,
423 x_attribute6 OUT NOCOPY VARCHAR2,
424 x_attribute7 OUT NOCOPY VARCHAR2,
425 x_attribute8 OUT NOCOPY VARCHAR2,
426 x_attribute9 OUT NOCOPY VARCHAR2,
427 x_attribute10 OUT NOCOPY VARCHAR2,
428 x_attribute11 OUT NOCOPY VARCHAR2,
429 x_attribute12 OUT NOCOPY VARCHAR2,
430 x_attribute13 OUT NOCOPY VARCHAR2,
431 x_attribute14 OUT NOCOPY VARCHAR2,
432 x_attribute15 OUT NOCOPY VARCHAR2,
433 x_edited_in_word OUT NOCOPY VARCHAR2,
434 x_article_text_in_word OUT NOCOPY BLOB,
435 x_variable_code OUT NOCOPY VARCHAR2 --clm
436 ) RETURN VARCHAR2 IS
437 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
438 l_object_version_number OKC_ARTICLE_VERSIONS.OBJECT_VERSION_NUMBER%TYPE;
439 l_created_by OKC_ARTICLE_VERSIONS.CREATED_BY%TYPE;
440 l_creation_date OKC_ARTICLE_VERSIONS.CREATION_DATE%TYPE;
441 l_last_updated_by OKC_ARTICLE_VERSIONS.LAST_UPDATED_BY%TYPE;
442 l_last_update_login OKC_ARTICLE_VERSIONS.LAST_UPDATE_LOGIN%TYPE;
443 l_last_update_date OKC_ARTICLE_VERSIONS.LAST_UPDATE_DATE%TYPE;
444 BEGIN
445 IF (l_debug = 'Y') THEN
446 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
447 END IF;
448
449 IF( p_article_version_id IS NOT NULL ) THEN
450 -- Get current database values
451 l_return_status := Get_Rec(
452 p_article_version_id => p_article_version_id,
453 x_article_id => x_article_id,
454 x_article_version_number => x_article_version_number,
455 x_article_text => x_article_text,
456 x_provision_yn => x_provision_yn,
457 x_insert_by_reference => x_insert_by_reference,
458 x_lock_text => x_lock_text,
459 x_global_yn => x_global_yn,
460 x_article_language => x_article_language,
461 x_article_status => x_article_status,
462 x_sav_release => x_sav_release,
463 x_start_date => x_start_date,
464 x_end_date => x_end_date,
465 x_std_article_version_id => x_std_article_version_id,
466 x_display_name => x_display_name,
467 x_translated_yn => x_translated_yn,
468 x_article_description => x_article_description,
469 x_date_approved => x_date_approved,
470 x_default_section => x_default_section,
471 x_reference_source => x_reference_source,
472 x_reference_text => x_reference_text,
473 x_orig_system_reference_code => x_orig_system_reference_code,
474 x_orig_system_reference_id1 => x_orig_system_reference_id1,
475 x_orig_system_reference_id2 => x_orig_system_reference_id2,
476 x_additional_instructions => x_additional_instructions,
477 x_variation_description => x_variation_description,
478 x_date_published => x_date_published,
479 x_program_id => x_program_id,
480 x_program_login_id => x_program_login_id,
481 x_program_application_id => x_program_application_id,
482 x_request_id => x_request_id,
483 x_attribute_category => x_attribute_category,
484 x_attribute1 => x_attribute1,
485 x_attribute2 => x_attribute2,
486 x_attribute3 => x_attribute3,
487 x_attribute4 => x_attribute4,
488 x_attribute5 => x_attribute5,
489 x_attribute6 => x_attribute6,
490 x_attribute7 => x_attribute7,
491 x_attribute8 => x_attribute8,
492 x_attribute9 => x_attribute9,
493 x_attribute10 => x_attribute10,
494 x_attribute11 => x_attribute11,
495 x_attribute12 => x_attribute12,
496 x_attribute13 => x_attribute13,
497 x_attribute14 => x_attribute14,
498 x_attribute15 => x_attribute15,
499 x_object_version_number => l_object_version_number,
500 x_edited_in_word => x_edited_in_word,
501 x_article_text_in_word => x_article_text_in_word,
502 x_created_by => l_created_by,
503 x_creation_date => l_creation_date,
504 x_last_updated_by => l_last_updated_by,
505 x_last_update_login => l_last_update_login,
506 x_last_update_date => l_last_update_date,
507 x_variable_code => x_variable_code --clm
508 );
509 --- If any errors happen abort API
510 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
512 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
513 RAISE FND_API.G_EXC_ERROR;
514 END IF;
515
516 --- Reversing G_MISS/NULL values logic
517
518 IF (p_article_id = G_MISS_NUM) THEN
519 x_article_id := NULL;
520 ELSIF (p_ARTICLE_ID IS NOT NULL) THEN
521 x_article_id := p_article_id;
522 END IF;
523
524 IF (p_article_version_number = G_MISS_NUM) THEN
525 x_article_version_number := NULL;
526 ELSIF (P_ARTICLE_VERSION_NUMBER IS NOT NULL) THEN
527 x_article_version_number := p_article_version_number;
528 END IF;
529
530 IF dbms_lob.getlength(p_article_text) = length(G_MISS_CHAR) THEN
531 IF (dbms_lob.substr(p_article_text,dbms_lob.getlength(p_article_text)) = G_MISS_CHAR) THEN
532 x_article_text := NULL;
533 END IF;
534 ELSIF (p_ARTICLE_TEXT IS NOT NULL) THEN
535 x_article_text := p_article_text;
536 END IF;
537
538 IF (p_provision_yn = G_MISS_CHAR) THEN
539 x_provision_yn := NULL;
540 ELSIF (p_PROVISION_YN IS NOT NULL) THEN
541 -- x_provision_yn := p_provision_yn; -- Modified
542 x_provision_yn := UPPER(p_provision_yn);
543 END IF;
544
545 IF (p_insert_by_reference = G_MISS_CHAR) THEN
546 x_insert_by_reference := NULL;
547 ELSIF (p_INSERT_BY_REFERENCE IS NOT NULL) THEN
548 -- x_insert_by_reference := p_insert_by_reference; -- Modified
549 x_insert_by_reference := UPPER(p_insert_by_reference);
550 END IF;
551
552 IF (p_lock_text = G_MISS_CHAR) THEN
553 x_lock_text := NULL;
554 ELSIF (p_LOCK_TEXT IS NOT NULL) THEN
555 -- x_lock_text := p_lock_text; -- Modified
556 x_lock_text := UPPER(p_lock_text);
557 END IF;
558
559 IF (p_global_yn = G_MISS_CHAR) THEN
560 x_global_yn := NULL;
561 ELSIF (p_GLOBAL_YN IS NOT NULL) THEN
562 -- x_global_yn := p_global_yn; -- Modified
563 x_global_yn := UPPER(p_global_yn);
564 END IF;
565
566 IF (p_article_language = G_MISS_CHAR) THEN
567 x_article_language := NULL;
568 ELSIF (p_ARTICLE_LANGUAGE IS NOT NULL) THEN
569 x_article_language := p_article_language;
570 END IF;
571
572 IF (p_article_status = G_MISS_CHAR) THEN
573 x_article_status := NULL;
574 ELSIF (p_ARTICLE_STATUS IS NOT NULL) THEN
575 x_article_status := p_article_status;
576 END IF;
577
578 IF (p_sav_release = G_MISS_CHAR) THEN
579 x_sav_release := NULL;
580 ELSIF (p_SAV_RELEASE IS NOT NULL) THEN
581 x_sav_release := p_sav_release;
582 END IF;
583
584 IF (p_start_date = G_MISS_DATE) THEN
585 x_start_date := NULL;
586 ELSIF (p_START_DATE IS NOT NULL) THEN
587 x_start_date := p_start_date;
588 END IF;
589
590 IF (p_end_date = G_MISS_DATE) THEN
591 x_end_date := NULL;
592 ELSIF (p_END_DATE IS NOT NULL) THEN
593 x_end_date := p_end_date;
594 END IF;
595
596 IF (p_std_article_version_id = G_MISS_NUM) THEN
597 x_std_article_version_id := NULL;
598 ELSIF (p_STD_ARTICLE_VERSION_ID IS NOT NULL) THEN
599 x_std_article_version_id := p_std_article_version_id;
600 END IF;
601
602 IF (p_display_name = G_MISS_CHAR) THEN
603 x_display_name := NULL;
604 ELSIF (p_DISPLAY_NAME IS NOT NULL) THEN
605 x_display_name := p_display_name;
606 END IF;
607
608 IF (p_translated_yn = G_MISS_CHAR) THEN
609 x_translated_yn := NULL;
610 ELSIF (p_TRANSLATED_YN IS NOT NULL) THEN
611 -- x_translated_yn := p_translated_yn; -- Modified
612 x_translated_yn := UPPER(p_translated_yn);
613 END IF;
614
615 IF (p_article_description = G_MISS_CHAR) THEN
616 x_article_description := NULL;
617 ELSIF (p_ARTICLE_DESCRIPTION IS NOT NULL) THEN
618 x_article_description := p_article_description;
619 END IF;
620
621 IF (p_date_approved = G_MISS_DATE) THEN
622 x_date_approved := NULL;
623 ELSIF (p_DATE_APPROVED IS NOT NULL) THEN
624 x_date_approved := p_date_approved;
625 END IF;
626
627 IF (p_default_section = G_MISS_CHAR) THEN
628 x_default_section := NULL;
629 ELSIF (p_DEFAULT_SECTION IS NOT NULL) THEN
630 x_default_section := p_default_section;
631 END IF;
632
633 IF (p_reference_source = G_MISS_CHAR) THEN
634 x_reference_source := NULL;
635 ELSIF (p_REFERENCE_SOURCE IS NOT NULL) THEN
636 x_reference_source := p_reference_source;
637 END IF;
638
639 IF (p_reference_text = G_MISS_CHAR) THEN
640 x_reference_text := NULL;
641 ELSIF (p_REFERENCE_TEXT IS NOT NULL) THEN
642 x_reference_text := p_reference_text;
643 END IF;
644
645 IF (p_orig_system_reference_code = G_MISS_CHAR) THEN
646 x_orig_system_reference_code := NULL;
647 ELSIF (p_ORIG_SYSTEM_REFERENCE_CODE IS NOT NULL) THEN
648 x_orig_system_reference_code := p_orig_system_reference_code;
649 END IF;
650
651 IF (p_orig_system_reference_id1 = G_MISS_CHAR) THEN
652 x_orig_system_reference_id1 := NULL;
653 ELSIF (p_ORIG_SYSTEM_REFERENCE_ID1 IS NOT NULL) THEN
654 x_orig_system_reference_id1 := p_orig_system_reference_id1;
655 END IF;
656
657 IF (p_orig_system_reference_id2 = G_MISS_CHAR) THEN
658 x_orig_system_reference_id2 := NULL;
659 ELSIF (p_ORIG_SYSTEM_REFERENCE_ID2 IS NOT NULL) THEN
660 x_orig_system_reference_id2 := p_orig_system_reference_id2;
661 END IF;
662
663 IF (p_additional_instructions = G_MISS_CHAR) THEN
664 x_additional_instructions := NULL;
665 ELSIF (p_ADDITIONAL_INSTRUCTIONS IS NOT NULL) THEN
666 x_additional_instructions := p_additional_instructions;
667 END IF;
668
669 IF (p_variation_description = G_MISS_CHAR) THEN
670 x_variation_description := NULL;
671 ELSIF (p_VARIATION_DESCRIPTION IS NOT NULL) THEN
672 x_variation_description := p_variation_description;
673 END IF;
674
675 IF (p_date_published = G_MISS_DATE) THEN
676 x_date_published := NULL;
677 ELSIF (p_DATE_PUBLISHED IS NOT NULL) THEN
678 x_date_published := p_date_published;
679 END IF;
680
681 IF (p_program_id = G_MISS_NUM) THEN
682 x_program_id := NULL;
683 ELSIF (p_PROGRAM_ID IS NOT NULL) THEN
684 x_program_id := p_program_id;
685 END IF;
686
687 IF (p_program_login_id = G_MISS_NUM) THEN
688 x_program_login_id := NULL;
689 ELSIF (p_PROGRAM_LOGIN_ID IS NOT NULL) THEN
690 x_program_login_id := p_program_login_id;
691 END IF;
692
693 IF (p_program_application_id = G_MISS_NUM) THEN
694 x_program_application_id := NULL;
695 ELSIF (p_PROGRAM_APPLICATION_ID IS NOT NULL) THEN
696 x_program_application_id := p_program_application_id;
697 END IF;
698
699 IF (p_request_id = G_MISS_NUM) THEN
700 x_request_id := NULL;
701 ELSIF (p_REQUEST_ID IS NOT NULL) THEN
702 x_request_id := p_request_id;
703 END IF;
704
705 IF (p_attribute_category = G_MISS_CHAR) THEN
706 x_attribute_category := NULL;
707 ELSIF (p_ATTRIBUTE_CATEGORY IS NOT NULL) THEN
708 x_attribute_category := p_attribute_category;
709 END IF;
710
711 IF (p_attribute1 = G_MISS_CHAR) THEN
712 x_attribute1 := NULL;
713 ELSIF (p_ATTRIBUTE1 IS NOT NULL) THEN
714 x_attribute1 := p_attribute1;
715 END IF;
716
717 IF (p_attribute2 = G_MISS_CHAR) THEN
718 x_attribute2 := NULL;
719 ELSIF (p_ATTRIBUTE2 IS NOT NULL) THEN
720 x_attribute2 := p_attribute2;
721 END IF;
722
723 IF (p_attribute3 = G_MISS_CHAR) THEN
724 x_attribute3 := NULL;
725 ELSIF (p_ATTRIBUTE3 IS NOT NULL) THEN
726 x_attribute3 := p_attribute3;
727 END IF;
728
729 IF (p_attribute4 = G_MISS_CHAR) THEN
730 x_attribute4 := NULL;
731 ELSIF (p_ATTRIBUTE4 IS NOT NULL) THEN
732 x_attribute4 := p_attribute4;
733 END IF;
734
735 IF (p_attribute5 = G_MISS_CHAR) THEN
736 x_attribute5 := NULL;
737 ELSIF (p_ATTRIBUTE5 IS NOT NULL) THEN
738 x_attribute5 := p_attribute5;
739 END IF;
740
741 IF (p_attribute6 = G_MISS_CHAR) THEN
742 x_attribute6 := NULL;
743 ELSIF (p_ATTRIBUTE6 IS NOT NULL) THEN
744 x_attribute6 := p_attribute6;
745 END IF;
746
747 IF (p_attribute7 = G_MISS_CHAR) THEN
748 x_attribute7 := NULL;
749 ELSIF (p_ATTRIBUTE7 IS NOT NULL) THEN
750 x_attribute7 := p_attribute7;
751 END IF;
752
753 IF (p_attribute8 = G_MISS_CHAR) THEN
754 x_attribute8 := NULL;
755 ELSIF (p_ATTRIBUTE8 IS NOT NULL) THEN
756 x_attribute8 := p_attribute8;
757 END IF;
758
759 IF (p_attribute9 = G_MISS_CHAR) THEN
760 x_attribute9 := NULL;
761 ELSIF (p_ATTRIBUTE9 IS NOT NULL) THEN
762 x_attribute9 := p_attribute9;
763 END IF;
764
765 IF (p_attribute10 = G_MISS_CHAR) THEN
766 x_attribute10 := NULL;
767 ELSIF (p_ATTRIBUTE10 IS NOT NULL) THEN
768 x_attribute10 := p_attribute10;
769 END IF;
770
771 IF (p_attribute11 = G_MISS_CHAR) THEN
772 x_attribute11 := NULL;
773 ELSIF (p_ATTRIBUTE11 IS NOT NULL) THEN
774 x_attribute11 := p_attribute11;
775 END IF;
776
777 IF (p_attribute12 = G_MISS_CHAR) THEN
778 x_attribute12 := NULL;
779 ELSIF (p_ATTRIBUTE12 IS NOT NULL) THEN
780 x_attribute12 := p_attribute12;
781 END IF;
782
783 IF (p_attribute13 = G_MISS_CHAR) THEN
784 x_attribute13 := NULL;
785 ELSIF (p_ATTRIBUTE13 IS NOT NULL) THEN
786 x_attribute13 := p_attribute13;
787 END IF;
788
789 IF (p_attribute14 = G_MISS_CHAR) THEN
790 x_attribute14 := NULL;
791 ELSIF (p_ATTRIBUTE14 IS NOT NULL) THEN
792 x_attribute14 := p_attribute14;
793 END IF;
794
795 IF (p_attribute15 = G_MISS_CHAR) THEN
796 x_attribute15 := NULL;
797 ELSIF (p_ATTRIBUTE15 IS NOT NULL) THEN
798 x_attribute15 := p_attribute15;
799 END IF;
800
801 IF (p_edited_in_word = G_MISS_CHAR) THEN
802 x_edited_in_word := NULL;
803 ELSIF (p_edited_in_word IS NOT NULL) THEN
804 x_edited_in_word := UPPER(p_edited_in_word);
805 END IF;
806
807 IF dbms_lob.getlength(p_article_text_in_word) = length(G_MISS_CHAR) THEN
808 IF (dbms_lob.substr(p_article_text_in_word,dbms_lob.getlength(p_article_text_in_word)) = G_MISS_CHAR) THEN
809 x_article_text_in_word := NULL;
810 END IF;
811 ELSIF (p_article_text_in_word IS NOT NULL) THEN
812 x_article_text_in_word := p_article_text_in_word;
813 END IF;
814
815 --CLM
816 IF (p_variable_code = G_MISS_CHAR) THEN
817 x_variable_code := NULL;
818 ELSIF (p_variable_code IS NOT NULL) THEN
819 x_variable_code := UPPER(p_variable_code);
820 END IF;
821
822 ELSE
823 x_article_id := p_article_id;
824 x_article_version_number := p_article_version_number;
825 x_article_text := p_article_text;
826 x_provision_yn := p_provision_yn;
827 x_insert_by_reference := p_insert_by_reference;
828 x_lock_text := p_lock_text;
829 x_global_yn := p_global_yn;
830 x_article_language := p_article_language;
831 x_article_status := p_article_status;
832 x_sav_release := p_sav_release;
833 x_start_date := p_start_date;
834 x_end_date := p_end_date;
835 x_std_article_version_id := p_std_article_version_id;
836 x_display_name := p_display_name;
837 x_translated_yn := p_translated_yn;
838 x_article_description := p_article_description;
839 x_date_approved := p_date_approved;
840 x_default_section := p_default_section;
841 x_reference_source := p_reference_source;
842 x_reference_text := p_reference_text;
843 x_orig_system_reference_code := p_orig_system_reference_code;
844 x_orig_system_reference_id1 := p_orig_system_reference_id1;
845 x_orig_system_reference_id2 := p_orig_system_reference_id2;
846 x_additional_instructions := p_additional_instructions;
847 x_variation_description := p_variation_description;
848 x_date_published := p_date_published;
849 x_program_id := p_program_id;
850 x_program_login_id := p_program_login_id;
851 x_program_application_id := p_program_application_id;
852 x_request_id := p_request_id;
853 x_attribute_category := p_attribute_category;
854 x_attribute1 := p_attribute1;
855 x_attribute2 := p_attribute2;
856 x_attribute3 := p_attribute3;
857 x_attribute4 := p_attribute4;
858 x_attribute5 := p_attribute5;
859 x_attribute6 := p_attribute6;
860 x_attribute7 := p_attribute7;
861 x_attribute8 := p_attribute8;
862 x_attribute9 := p_attribute9;
863 x_attribute10 := p_attribute10;
864 x_attribute11 := p_attribute11;
865 x_attribute12 := p_attribute12;
866 x_attribute13 := p_attribute13;
867 x_attribute14 := p_attribute14;
868 x_attribute15 := p_attribute15;
869 x_edited_in_word := p_edited_in_word;
870 x_article_text_in_word := p_article_text_in_word;
871 x_variable_code := p_variable_code; --clm
872
873 END IF;
874
875 IF (l_debug = 'Y') THEN
876 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
877 END IF;
878
879 RETURN G_RET_STS_SUCCESS ;
880 EXCEPTION
881 WHEN FND_API.G_EXC_ERROR THEN
882 IF (l_debug = 'Y') THEN
883 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
884 END IF;
885 RETURN G_RET_STS_ERROR;
886
887 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
888 IF (l_debug = 'Y') THEN
889 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
890 END IF;
891 RETURN G_RET_STS_UNEXP_ERROR;
892
893 WHEN OTHERS THEN
894 IF (l_debug = 'Y') THEN
895 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
896 END IF;
897 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
898 p_msg_name => G_UNEXPECTED_ERROR,
899 p_token1 => G_SQLCODE_TOKEN,
900 p_token1_value => sqlcode,
901 p_token2 => G_SQLERRM_TOKEN,
902 p_token2_value => sqlerrm);
903 RETURN G_RET_STS_UNEXP_ERROR;
904
905 END Set_Attributes ;
906
907 ----------------------------------------------
908 -- Validate_Attributes for: OKC_ARTICLE_VERSIONS --
909 ----------------------------------------------
910 FUNCTION Validate_Attributes (
911 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
912 p_import_action IN VARCHAR2 := NULL,
913 p_standard_yn IN VARCHAR2,
914
915 p_article_version_id IN NUMBER,
916 p_article_id IN NUMBER,
917 p_article_version_number IN NUMBER,
918 p_article_text IN CLOB,
919 p_provision_yn IN VARCHAR2,
920 p_insert_by_reference IN VARCHAR2,
921 p_lock_text IN VARCHAR2,
922 p_global_yn IN VARCHAR2,
923 p_article_language IN VARCHAR2,
924 p_article_status IN VARCHAR2,
925 p_sav_release IN VARCHAR2,
926 p_start_date IN DATE,
927 p_end_date IN DATE,
928 p_std_article_version_id IN NUMBER,
929 p_display_name IN VARCHAR2,
930 p_translated_yn IN VARCHAR2,
931 p_article_description IN VARCHAR2,
932 p_date_approved IN DATE,
933 p_default_section IN VARCHAR2,
934 p_reference_source IN VARCHAR2,
935 p_reference_text IN VARCHAR2,
936 p_orig_system_reference_code IN VARCHAR2,
937 p_orig_system_reference_id1 IN VARCHAR2,
938 p_orig_system_reference_id2 IN VARCHAR2,
939 p_additional_instructions IN VARCHAR2,
940 p_variation_description IN VARCHAR2,
941 p_date_published IN DATE,
942 p_program_id IN NUMBER,
943 p_program_login_id IN NUMBER,
944 p_program_application_id IN NUMBER,
945 p_request_id IN NUMBER,
946 p_attribute_category IN VARCHAR2,
947 p_attribute1 IN VARCHAR2,
948 p_attribute2 IN VARCHAR2,
949 p_attribute3 IN VARCHAR2,
950 p_attribute4 IN VARCHAR2,
951 p_attribute5 IN VARCHAR2,
952 p_attribute6 IN VARCHAR2,
953 p_attribute7 IN VARCHAR2,
954 p_attribute8 IN VARCHAR2,
955 p_attribute9 IN VARCHAR2,
956 p_attribute10 IN VARCHAR2,
957 p_attribute11 IN VARCHAR2,
958 p_attribute12 IN VARCHAR2,
959 p_attribute13 IN VARCHAR2,
960 p_attribute14 IN VARCHAR2,
961 p_attribute15 IN VARCHAR2,
962 p_edited_in_word IN VARCHAR2,
963 p_article_text_in_word IN BLOB,
964 p_variable_code IN VARCHAR2 --clm
965 ) RETURN VARCHAR2 IS
966 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
967 l_tmp_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
968 l_dummy_var VARCHAR2(1) := '?';
969 l_dummy_date OKC_ARTICLE_VERSIONS.DATE_PUBLISHED%TYPE;
970 l_art_XML XMLType;
971
972
973 CURSOR l_article_language_csr(p_article_id IN NUMBER, p_article_language IN VARCHAR2) is
974 SELECT '!'
975 FROM OKC_ARTICLES_ALL
976 WHERE ARTICLE_LANGUAGE = p_article_language
977 AND ARTICLE_ID = p_article_id;
978
979 CURSOR l_std_article_version_id_csr is
980 SELECT '!'
981 FROM OKC_ARTICLES_ALL AA,OKC_ARTICLE_VERSIONS AV
982 WHERE AA.ARTICLE_ID = AV.ARTICLE_ID
983 AND AV.ARTICLE_VERSION_ID = p_std_article_version_id
984 AND AA.STANDARD_YN = 'Y';
985
986 -- Below Added for FAR/DFAR Import
987 CURSOR l_date_published_csr is
988 SELECT av.date_published
989 FROM OKC_ARTICLE_VERSIONS AV
990 WHERE AV.ARTICLE_VERSION_ID = p_article_version_id;
991
992 BEGIN
993
994 IF (l_debug = 'Y') THEN
995 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
996 END IF;
997
998 -- Article row will always be needed to test if this is a standard or not even for not null or FK checks
999
1000 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
1001 IF (l_debug = 'Y') THEN
1002 Okc_Debug.Log('1300: required values validation', 2);
1003 END IF;
1004
1005 IF (l_debug = 'Y') THEN
1006 Okc_Debug.Log('1400: - attribute ARTICLE_ID ', 2);
1007 END IF;
1008 IF nvl(p_import_Action,'*') <> 'N' Then -- bypass this check for validation of new article being imported
1009 IF ( p_article_id IS NULL) THEN
1010 IF (l_debug = 'Y') THEN
1011 Okc_Debug.Log('1500: - attribute ARTICLE_ID is invalid', 2);
1012 END IF;
1013 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_ID');
1014 l_return_status := G_RET_STS_ERROR;
1015 END IF;
1016 END IF;
1017
1018 -- As of now validation of article version number is not needed as it is generated by the API.
1019 /*
1020 IF (l_debug = 'Y') THEN
1021 Okc_Debug.Log('1400: - attribute ARTICLE_VERSION_NUMBER ', 2);
1022 END IF;
1023 IF ( p_article_version_number IS NULL) THEN
1024 IF (l_debug = 'Y') THEN
1025 Okc_Debug.Log('1500: - attribute ARTICLE_VERSION_NUMBER is invalid', 2);
1026 END IF;
1027 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_VERSION_NUMBER');
1028 l_return_status := G_RET_STS_ERROR;
1029 END IF;
1030 */
1031
1032 IF (l_debug = 'Y') THEN
1033 Okc_Debug.Log('1400: - attribute ARTICLE_TEXT ', 2);
1034 END IF;
1035 IF ( p_article_text IS NULL) THEN
1036 IF (l_debug = 'Y') THEN
1037 Okc_Debug.Log('1500: - attribute ARTICLE_TEXT is invalid', 2);
1038 END IF;
1039 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ARTICLE_TEXT_REQD');
1040 --Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_TEXT');
1041 l_return_status := G_RET_STS_ERROR;
1042 END IF;
1043
1044 IF (l_debug = 'Y') THEN
1045 Okc_Debug.Log('1400: - attribute PROVISION_YN ', 2);
1046 END IF;
1047 IF ( p_provision_yn IS NULL) THEN
1048 IF (l_debug = 'Y') THEN
1049 Okc_Debug.Log('1500: - attribute PROVISION_YN is invalid', 2);
1050 END IF;
1051 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'PROVISION_YN');
1052 l_return_status := G_RET_STS_ERROR;
1053 END IF;
1054
1055 IF (l_debug = 'Y') THEN
1056 Okc_Debug.Log('1400: - attribute INSERT_BY_REFERENCE ', 2);
1057 END IF;
1058 IF ( p_insert_by_reference IS NULL) THEN
1059 IF (l_debug = 'Y') THEN
1060 Okc_Debug.Log('1500: - attribute INSERT_BY_REFERENCE is invalid', 2);
1061 END IF;
1062 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'INSERT_BY_REFERENCE');
1063 l_return_status := G_RET_STS_ERROR;
1064 END IF;
1065
1066 IF (l_debug = 'Y') THEN
1067 Okc_Debug.Log('1400: - attribute LOCK_TEXT ', 2);
1068 END IF;
1069 IF ( p_lock_text IS NULL) THEN
1070 IF (l_debug = 'Y') THEN
1071 Okc_Debug.Log('1500: - attribute LOCK_TEXT is invalid', 2);
1072 END IF;
1073 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'LOCK_TEXT');
1074 l_return_status := G_RET_STS_ERROR;
1075 END IF;
1076
1077 IF (l_debug = 'Y') THEN
1078 Okc_Debug.Log('1400: - attribute GLOBAL_YN ', 2);
1079 END IF;
1080 IF ( p_global_yn IS NULL) THEN
1081 IF (l_debug = 'Y') THEN
1082 Okc_Debug.Log('1500: - attribute GLOBAL_YN is invalid', 2);
1083 END IF;
1084 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'GLOBAL_YN');
1085 l_return_status := G_RET_STS_ERROR;
1086 END IF;
1087
1088 IF (l_debug = 'Y') THEN
1089 Okc_Debug.Log('1400: - attribute ARTICLE_LANGUAGE ', 2);
1090 END IF;
1091 IF ( p_article_language IS NULL) THEN
1092 IF (l_debug = 'Y') THEN
1093 Okc_Debug.Log('1500: - attribute ARTICLE_LANGUAGE is invalid', 2);
1094 END IF;
1095 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_LANGUAGE');
1096 l_return_status := G_RET_STS_ERROR;
1097 END IF;
1098
1099 IF p_standard_yn = 'Y' OR p_import_action IS NOT NULL Then
1100 IF (l_debug = 'Y') THEN
1101 Okc_Debug.Log('1400: - attribute START_DATE ', 2);
1102 END IF;
1103 IF ( p_start_date IS NULL) THEN
1104 IF (l_debug = 'Y') THEN
1105 Okc_Debug.Log('1500: - attribute START_DATE is invalid', 2);
1106 END IF;
1107 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'START_DATE');
1108 l_return_status := G_RET_STS_ERROR;
1109 END IF;
1110 END IF;
1111
1112 /* IF (l_debug = 'Y') THEN
1113 Okc_Debug.Log('1400: - attribute EDITED_IN_WORD ', 2);
1114 END IF;
1115 IF ( p_edited_in_word IS NULL) THEN
1116 IF (l_debug = 'Y') THEN
1117 Okc_Debug.Log('1500: - attribute EDITED_IN_WORD is invalid', 2);
1118 END IF;
1119 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'EDITED_IN_WORD');
1120 l_return_status := G_RET_STS_ERROR;
1121 END IF; */
1122
1123 IF p_edited_in_word = 'Y' Then
1124 IF (l_debug = 'Y') THEN
1125 Okc_Debug.Log('1400: - attribute ARTICLE_TEXT_IN_WORD ', 2);
1126 END IF;
1127 IF ( p_article_text_in_word IS NULL OR Dbms_Lob.SubStr(p_article_text_in_word,1,1) = '00') THEN
1128 IF (l_debug = 'Y') THEN
1129 Okc_Debug.Log('1500: - attribute ARTICLE_TEXT_IN_WORD is invalid', 2);
1130 END IF;
1131 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_ERROR_IMPORT_WORD');
1132 l_return_status := G_RET_STS_ERROR;
1133 ELSE
1134
1135 BEGIN
1136
1137 l_art_XML := XMLType(okc_word_download_upload.blob_to_clob(p_article_text_in_word));
1138
1139
1140 EXCEPTION WHEN OTHERS THEN
1141
1142 IF (l_debug = 'Y') THEN
1143 Okc_Debug.Log('3000: - attribute ARTICLE_TEXT_IN_WORD is not a valid XML', 2);
1144 END IF;
1145 Okc_Api.Set_Message(G_APP_NAME, 'OKC_INV_ART_TXT_WML');
1146 l_return_status := G_RET_STS_ERROR;
1147
1148 END;
1149
1150 END IF;
1151 END IF;
1152 END IF;
1153
1154 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
1155 IF (l_debug = 'Y') THEN
1156 Okc_Debug.Log('1600: static values and range validation', 2);
1157 END IF;
1158
1159 IF (l_debug = 'Y') THEN
1160 Okc_Debug.Log('1700: - attribute PROVISION_YN ', 2);
1161 END IF;
1162 IF p_provision_yn NOT IN ('Y','N') THEN
1163 IF (l_debug = 'Y') THEN
1164 Okc_Debug.Log('1800: - attribute PROVISION_YN is invalid', 2);
1165 END IF;
1166 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'PROVISION_YN');
1167 l_return_status := G_RET_STS_ERROR;
1168 END IF;
1169
1170 IF p_insert_by_reference NOT IN ('Y','N') THEN
1171 IF (l_debug = 'Y') THEN
1172 Okc_Debug.Log('1800: - attribute INSERT_BY_REFERENCE is invalid', 2);
1173 END IF;
1174 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'INSERT_BY_REFERENCE');
1175 l_return_status := G_RET_STS_ERROR;
1176 END IF;
1177
1178 IF p_lock_text NOT IN ('Y','N') THEN
1179 IF (l_debug = 'Y') THEN
1180 Okc_Debug.Log('1800: - attribute LOCK_TEXT is invalid', 2);
1181 END IF;
1182 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'LOCK_TEXT');
1183 l_return_status := G_RET_STS_ERROR;
1184 END IF;
1185
1186 IF (l_debug = 'Y') THEN
1187 Okc_Debug.Log('1700: - attribute GLOBAL_YN ', 2);
1188 END IF;
1189 IF p_global_yn NOT IN ('Y','N') THEN
1190 IF (l_debug = 'Y') THEN
1191 Okc_Debug.Log('1800: - attribute GLOBAL_YN is invalid', 2);
1192 END IF;
1193 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'GLOBAL_YN');
1194 l_return_status := G_RET_STS_ERROR;
1195 END IF;
1196
1197 IF (l_debug = 'Y') THEN
1198 Okc_Debug.Log('1700: - attribute TRANSLATED_YN ', 2);
1199 END IF;
1200 IF p_translated_yn NOT IN ('Y','N') THEN
1201 IF (l_debug = 'Y') THEN
1202 Okc_Debug.Log('1800: - attribute TRANSLATED_YN is invalid', 2);
1203 END IF;
1204 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'TRANSLATED_YN');
1205 l_return_status := G_RET_STS_ERROR;
1206 END IF;
1207
1208 END IF;
1209
1210 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
1211 IF (l_debug = 'Y') THEN
1212 Okc_Debug.Log('1900: lookup codes validation', 2);
1213 END IF;
1214
1215 /* Status Check is not needed as it is always set by the UI.. For Articles Import this is pre-checked for certain statuses only
1216
1217 IF (l_debug = 'Y') THEN
1218 Okc_Debug.Log('2000: - attribute ARTICLE_STATUS ', 2);
1219 END IF;
1220 IF p_article_status IS NOT NULL THEN
1221 l_tmp_return_status := Okc_Util.Check_Lookup_Code('OKC_ARTICLE_STATUS',p_article_status);
1222 IF (l_tmp_return_status <> G_RET_STS_SUCCESS) THEN
1223 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_STATUS');
1224 l_return_status := G_RET_STS_ERROR;
1225 END IF;
1226 END IF;
1227 */
1228
1229 IF (l_debug = 'Y') THEN
1230 Okc_Debug.Log('1500: - attribute DEFAULT_SECTION ', 2);
1231 END IF;
1232 IF p_default_section IS NOT NULL THEN
1233 l_tmp_return_status := Okc_Util.Check_Lookup_Code('OKC_ARTICLE_SECTION',p_default_section);
1234 IF (l_tmp_return_status <> G_RET_STS_SUCCESS) THEN
1235 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_INVALID_SECTION');
1236 l_return_status := G_RET_STS_ERROR;
1237 END IF;
1238 END IF;
1239
1240 END IF;
1241
1242 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
1243 /*
1244 IF (l_debug = 'Y') THEN
1245 Okc_Debug.Log('2100: foreign keys validation ', 2);
1246 Okc_Debug.Log('2200: - attribute ARTICLE_LANGAUGE ', 2);
1247 END IF;
1248
1249 -- This check is required only for article import .. For UI this is not required as it will be derived as USERENV(LANG)
1250 -- This is a denormalized attribute and will be validated only from creating new version from Articles Import.
1251
1252
1253 IF p_article_language IS NOT NULL THEN
1254 l_dummy_var := '?';
1255 OPEN l_article_language_csr;
1256 FETCH l_article_language_csr INTO l_dummy_var;
1257 CLOSE l_article_language_csr;
1258 IF (l_dummy_var = '?') THEN
1259 IF (l_debug = 'Y') THEN
1260 Okc_Debug.Log('2300: - attribute ARTICLE_LANGAUGE is invalid', 2);
1261 END IF;
1262 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ARTICLE_LANGAUGE');
1263 l_return_status := G_RET_STS_ERROR;
1264 END IF;
1265 END IF;
1266 */
1267 IF (l_debug = 'Y') THEN
1268 Okc_Debug.Log('2200: - attribute STD_ARTICLE_VERSION_ID ', 2);
1269 END IF;
1270 IF p_std_article_version_id IS NOT NULL THEN
1271 l_dummy_var := '?';
1272 OPEN l_std_article_version_id_csr;
1273 FETCH l_std_article_version_id_csr INTO l_dummy_var;
1274 CLOSE l_std_article_version_id_csr;
1275 IF (l_dummy_var = '?') THEN
1276 IF (l_debug = 'Y') THEN
1277 Okc_Debug.Log('2300: - attribute STD_ARTICLE_VERSION_ID is invalid', 2);
1278 END IF;
1279 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'STD_ARTICLE_VERSION_ID');
1280 l_return_status := G_RET_STS_ERROR;
1281 END IF;
1282 END IF;
1283
1284 /*
1285 IF (l_debug = 'Y') THEN
1286 Okc_Debug.Log('2200: - attribute DATE_PUBLISHED ', 2);
1287 END IF;
1288 IF p_date_published IS NOT NULL THEN
1289 OPEN l_date_published_csr;
1290 FETCH l_date_published_csr INTO l_dummy_date;
1291 CLOSE l_date_published_csr;
1292 IF (l_dummy_date >= p_date_published) THEN
1293 IF (l_debug = 'Y') THEN
1294 Okc_Debug.Log('2300: - attribute DATE_PUBLISHED is invalid', 2);
1295 END IF;
1296 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DATE_PUBLISHED');
1297 l_return_status := G_RET_STS_ERROR;
1298 END IF;
1299 END IF;
1300 */
1301
1302 END IF;
1303
1304 IF (l_debug = 'Y') THEN
1305 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
1306 END IF;
1307
1308 RETURN l_return_status;
1309
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
1313 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1314 p_msg_name => G_UNEXPECTED_ERROR,
1315 p_token1 => G_SQLCODE_TOKEN,
1316 p_token1_value => sqlcode,
1317 p_token2 => G_SQLERRM_TOKEN,
1318 p_token2_value => sqlerrm);
1319
1320
1321 IF l_article_language_csr%ISOPEN THEN
1322 CLOSE l_article_language_csr;
1323 END IF;
1324
1325 IF l_std_article_version_id_csr%ISOPEN THEN
1326 CLOSE l_std_article_version_id_csr;
1327 END IF;
1328
1329 RETURN G_RET_STS_UNEXP_ERROR;
1330
1331 END Validate_Attributes;
1332
1333 -- fix for bug#4006749 start
1334 -- muteshev
1335 /*
1336 this function should be used in validate_record for
1337 validation provision_yn flag setting in source article
1338 and target articles that take part in articles relationship
1339 (see bug#4006749)
1340 */
1341 function provision_flag_in_relations(
1342 p_article_id in number,
1343 p_org_id in number,
1344 p_provision_yn in varchar2
1345 )
1346 return varchar2
1347 is
1348 result varchar2(1) := 'S'; -- success
1349 cursor l_rel_prov_csr(
1350 c_source_article_id in number,
1351 c_org_id in number,
1352 c_provision_yn in varchar2)
1353 IS
1354 select 'E' result
1355 from
1356 okc_article_versions v,
1357 okc_article_relatns_all r
1358 where
1359 r.source_article_id = c_source_article_id
1360 and
1361 r.org_id = c_org_id
1362 and
1363 v.article_id = r.target_article_id
1364 and
1365 v.article_version_number = 1
1366 and
1367 v.provision_yn <> c_provision_yn;
1368 begin
1369 FOR err IN l_rel_prov_csr(p_article_id, p_org_id, p_provision_yn)
1370 LOOP
1371 result := err.result; -- failure
1372 END LOOP;
1373 return result;
1374 end;
1375 -- fix for bug#4006749 end
1376
1377 ---------------------------------------------------------------------------
1378 -- PROCEDURE Validate_Record
1379 -- It calls Item Level Validations and then makes Record Level Validations
1380 ---------------------------------------------------------------------------
1381 ------------------------------------------
1382 -- Validate_Record for:OKC_ARTICLE_VERSIONS --
1383 ------------------------------------------
1384 FUNCTION Validate_Record (
1385 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1386 p_import_action IN VARCHAR2 := NULL,
1387
1388 p_article_version_id IN NUMBER,
1389 p_article_id IN NUMBER,
1390 p_article_version_number IN NUMBER,
1391 p_article_text IN CLOB,
1392 p_provision_yn IN VARCHAR2,
1393 p_insert_by_reference IN VARCHAR2,
1394 p_lock_text IN VARCHAR2,
1395 p_global_yn IN VARCHAR2,
1396 p_article_language IN VARCHAR2,
1397 p_article_status IN VARCHAR2,
1398 p_sav_release IN VARCHAR2,
1399 p_start_date IN DATE,
1400 p_end_date IN DATE,
1401 p_std_article_version_id IN NUMBER,
1402 p_display_name IN VARCHAR2,
1403 p_translated_yn IN VARCHAR2,
1404 p_article_description IN VARCHAR2,
1405 p_date_approved IN DATE,
1406 p_default_section IN VARCHAR2,
1407 p_reference_source IN VARCHAR2,
1408 p_reference_text IN VARCHAR2,
1409 p_orig_system_reference_code IN VARCHAR2,
1410 p_orig_system_reference_id1 IN VARCHAR2,
1411 p_orig_system_reference_id2 IN VARCHAR2,
1412 p_additional_instructions IN VARCHAR2,
1413 p_variation_description IN VARCHAR2,
1414 p_program_id IN NUMBER := NULL,
1415 p_program_login_id IN NUMBER := NULL,
1416 p_program_application_id IN NUMBER := NULL,
1417 p_request_id IN NUMBER := NULL,
1418 p_current_org_id IN NUMBER := NULL,
1419 p_date_published IN DATE,
1420 p_attribute_category IN VARCHAR2,
1421 p_attribute1 IN VARCHAR2,
1422 p_attribute2 IN VARCHAR2,
1423 p_attribute3 IN VARCHAR2,
1424 p_attribute4 IN VARCHAR2,
1425 p_attribute5 IN VARCHAR2,
1426 p_attribute6 IN VARCHAR2,
1427 p_attribute7 IN VARCHAR2,
1428 p_attribute8 IN VARCHAR2,
1429 p_attribute9 IN VARCHAR2,
1430 p_attribute10 IN VARCHAR2,
1431 p_attribute11 IN VARCHAR2,
1432 p_attribute12 IN VARCHAR2,
1433 p_attribute13 IN VARCHAR2,
1434 p_attribute14 IN VARCHAR2,
1435 p_attribute15 IN VARCHAR2,
1436 p_edited_in_word IN VARCHAR2 DEFAULT 'N',
1437 p_article_text_in_word IN BLOB DEFAULT NULL,
1438 p_variable_code IN VARCHAR2 DEFAULT NULL,
1439 x_earlier_adoption_type OUT NOCOPY VARCHAR2,
1440 x_earlier_version_number OUT NOCOPY NUMBER,
1441 x_earlier_version_id OUT NOCOPY NUMBER,
1442 x_article_language OUT NOCOPY VARCHAR2
1443 ) RETURN VARCHAR2 IS
1444 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1445 l_dummy_var VARCHAR2(1) := '?';
1446 l_dummy_var1 VARCHAR2(1) ;
1447 l_dummy_var2 VARCHAR2(1) ;
1448
1449 l_standard_yn VARCHAR2(1) ;
1450 l_article_language OKC_ARTICLES_ALL.ARTICLE_LANGUAGE%TYPE;
1451 l_global_start_date DATE := TO_DATE('12313999','MMDDYYYY');
1452 l_rownotfound BOOLEAN := FALSE;
1453
1454
1455 -- Cursor to derive highest version excluding the current one.
1456
1457 CURSOR l_highest_version_csr(p_article_id IN NUMBER, p_article_version_id IN NUMBER) IS
1458 -- 8.1.7.4 compatibility
1459 select
1460 av.global_yn,
1461 av.article_status,
1462 av.adoption_type,
1463 av.start_date,
1464 av.end_date,
1465 av.provision_yn,
1466 av.article_version_number,
1467 av.article_version_id,
1468 av.date_published
1469 from
1470 okc_article_versions av
1471 where
1472 av.article_id = p_article_id
1473 and
1474 av.start_date = (select
1475 max(av1.start_date)
1476 from
1477 okc_article_versions av1
1478 where
1479 av1.article_id = av.article_id
1480 and
1481 av1.article_version_id <> p_article_version_id
1482 );
1483 /*
1484 SELECT S.GLOBAL_YN,
1485 S.ARTICLE_STATUS,
1486 S.ADOPTION_TYPE,
1487 S.START_DATE,
1488 S.END_DATE,
1489 S.MAX_START_DATE,
1490 S.ARTICLE_VERSION_NUMBER,
1491 S.ARTICLE_VERSION_ID
1492 FROM (
1493 SELECT
1494 A.GLOBAL_YN,
1495 A.ARTICLE_STATUS,
1496 A.ADOPTION_TYPE,
1497 A.START_DATE, A.END_DATE,
1498 MAX(A.START_DATE) OVER (PARTITION BY A.ARTICLE_ID) AS MAX_START_DATE,
1499 A.ARTICLE_VERSION_NUMBER,
1500 A.ARTICLE_VERSION_ID
1501 FROM OKC_ARTICLE_VERSIONS A
1502 WHERE A.ARTICLE_ID = p_article_id
1503 AND ARTICLE_VERSION_ID <> p_article_version_id
1504 ) S
1505 WHERE S.START_DATE = S.MAX_START_DATE;
1506 */
1507
1508 CURSOR l_article_id_csr(p_article_id IN NUMBER) is
1509 SELECT standard_yn, article_language
1510 FROM OKC_ARTICLES_ALL
1511 WHERE ARTICLE_ID = p_article_id;
1512
1513 -- Bug#3672511: Validation of start date of a localized clause should be >= start date of global clause.
1514
1515 CURSOR l_global_csr(p_article_version_id IN NUMBER, p_local_org_id IN NUMBER) is
1516 SELECT start_date
1517 FROM OKC_ARTICLE_VERSIONS AVN, OKC_ARTICLE_ADOPTIONS ADP
1518 WHERE ARTICLE_VERSION_ID = GLOBAL_ARTICLE_VERSION_ID
1519 AND LOCAL_ARTICLE_VERSION_ID = p_article_version_id
1520 AND LOCAL_ORG_ID = p_local_org_id
1521 AND ADP.ADOPTION_TYPE = 'LOCALIZED' ;
1522
1523 l_highest_version_rec l_highest_version_csr%ROWTYPE;
1524
1525 BEGIN
1526
1527 IF (l_debug = 'Y') THEN
1528 Okc_Debug.Log('2600: Entered Validate_Record', 2);
1529 Okc_Debug.Log('1400: - attribute ARTICLE_VERSION_ID ', 2);
1530 END IF;
1531 x_earlier_version_number := NULL;
1532 l_article_language := p_article_language;
1533 -- MOAC
1534 G_CURRENT_ORG_ID := mo_global.get_current_org_id();
1535 if p_current_org_id IS NOT NULL Then
1536 G_CURRENT_ORG_ID := p_current_org_id;
1537 else
1538 if G_CURRENT_ORG_ID IS NULL Then
1539 IF (l_debug = 'Y') THEN
1540 Okc_Debug.Log('2300: - attribute G_CURRENT_ORG_ID is invalid', 2);
1541 END IF;
1542 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
1543 l_return_status := G_RET_STS_ERROR;
1544 RETURN l_return_status ;
1545 end if;
1546
1547 end if;
1548 /*
1549 if p_current_org_id IS NULL Then
1550 OPEN cur_org_csr;
1551 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
1552 CLOSE cur_org_csr;
1553 else
1554 G_CURRENT_ORG_ID := p_current_org_id;
1555 end if;
1556 */
1557
1558 IF p_import_action in ('N', 'V', 'U') THEN
1559 l_standard_yn := 'Y';
1560 l_article_language := p_article_language;
1561 ELSIF p_article_id IS NOT NULL THEN
1562 l_standard_yn := '?';
1563 OPEN l_article_id_csr(p_article_id);
1564 FETCH l_article_id_csr INTO l_standard_yn, l_article_language;
1565 CLOSE l_article_id_csr;
1566 IF (l_standard_yn = '?') THEN
1567 IF (l_debug = 'Y') THEN
1568 Okc_Debug.Log('2300: - attribute ARTICLE_ID is invalid', 2);
1569 END IF;
1570 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ARTICLE_ID');
1571 l_return_status := G_RET_STS_ERROR;
1572 RETURN l_return_status ;
1573 END IF;
1574 END IF;
1575
1576 --- Validate all non-missing attributes (Item Level Validation)
1577 l_return_status := Validate_Attributes(
1578 p_validation_level => p_validation_level,
1579 p_import_action => p_import_action,
1580 p_standard_yn => l_standard_yn, -- Introduced for checks related to Std version only.
1581 p_article_version_id => p_article_version_id,
1582 p_article_id => p_article_id,
1583 p_article_version_number => p_article_version_number,
1584 p_article_text => p_article_text,
1585 p_provision_yn => p_provision_yn,
1586 p_insert_by_reference => p_insert_by_reference,
1587 p_lock_text => p_lock_text,
1588 p_global_yn => p_global_yn,
1589 p_article_language => p_article_language,
1590 p_article_status => p_article_status,
1591 p_sav_release => p_sav_release,
1592 p_start_date => p_start_date,
1593 p_end_date => p_end_date,
1594 p_std_article_version_id => p_std_article_version_id,
1595 p_display_name => p_display_name,
1596 p_translated_yn => p_translated_yn,
1597 p_article_description => p_article_description,
1598 p_date_approved => p_date_approved,
1599 p_default_section => p_default_section,
1600 p_reference_source => p_reference_source,
1601 p_reference_text => p_reference_text,
1602 p_orig_system_reference_code => p_orig_system_reference_code,
1603 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1604 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1605 p_additional_instructions => p_additional_instructions,
1606 p_variation_description => p_variation_description,
1607 p_date_published => p_date_published,
1608 p_program_id => p_program_id,
1609 p_program_login_id => p_program_application_id,
1610 p_program_application_id => p_program_application_id,
1611 p_request_id => p_request_id,
1612 p_attribute_category => p_attribute_category,
1613 p_attribute1 => p_attribute1,
1614 p_attribute2 => p_attribute2,
1615 p_attribute3 => p_attribute3,
1616 p_attribute4 => p_attribute4,
1617 p_attribute5 => p_attribute5,
1618 p_attribute6 => p_attribute6,
1619 p_attribute7 => p_attribute7,
1620 p_attribute8 => p_attribute8,
1621 p_attribute9 => p_attribute9,
1622 p_attribute10 => p_attribute10,
1623 p_attribute11 => p_attribute11,
1624 p_attribute12 => p_attribute12,
1625 p_attribute13 => p_attribute13,
1626 p_attribute14 => p_attribute14,
1627 p_attribute15 => p_attribute15,
1628 p_edited_in_word => p_edited_in_word,
1629 p_article_text_in_word => p_article_text_in_word,
1630 p_variable_code => p_variable_code --clm
1631 );
1632 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1633 IF (l_debug = 'Y') THEN
1634 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
1635 END IF;
1636 RETURN G_RET_STS_UNEXP_ERROR;
1637 END IF;
1638
1639 -- The following lines will be used to:
1640 -- 1. Validate the current article attributes in the case of Standard Article from a previous version (if record level
1641 -- validation is desired).
1642 -- 2. Generate the version number later in the case of inserts of a article version in insert_row API using the
1643 -- x_previous_version_number parameter (even if record level validation is not desired).
1644 -- 3. Validate the effectivity even in case of approved.
1645 -- Pls note additional checks are provided because start_date may not be passed
1646 -- by an erroneous transaction during validation.
1647
1648 -- Also this will be called from import API in which case article versions
1649 -- can be imported as approved or pending approval.
1650 -- We would still need to check that an article cannot be imported as approved/
1651 -- pending approval if earlier version is draft/pending approval/rejected.
1652
1653
1654 IF (l_standard_yn = 'Y') AND -- All cases of Standard Articles Only (except first version of Import)
1655 (nvl(p_import_action,'X') <> 'N') THEN
1656 IF p_article_status in ('DRAFT','REJECTED','PENDING_APPROVAL') OR -- All Non Approved or Local or Start Date as null
1657 p_global_yn = 'N' OR
1658 p_import_action = 'V' OR -- Added by MSENGUPT on 05/24 Bug#3648236 as earlier version check is needed for new version of import
1659 p_start_date IS NOT NULL Then
1660
1661 -- For new versions while importing, the article version id will be null, the
1662 -- API is expected to return back the highest article version in that case
1663
1664 IF p_import_Action = 'V' THEN
1665 OPEN l_highest_version_csr(p_article_id, -99);
1666 ELSE
1667 OPEN l_highest_version_csr(p_article_id, p_article_version_id);
1668 END IF;
1669 FETCH l_highest_version_csr INTO l_highest_version_rec ;
1670 CLOSE l_highest_version_csr;
1671 END IF;
1672 END IF;
1673
1674 --- Record Level Validation
1675 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
1676 IF (l_debug = 'Y') THEN
1677 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
1678 END IF;
1679 /*+++++++++++++start of hand code +++++++++++++++++++*/
1680 -- manual coding for Record Level Validations if required
1681
1682 IF ( p_end_date is NOT NULL ) THEN
1683 IF ( p_end_date < p_start_date ) THEN
1684 IF (l_debug = 'Y') THEN
1685 Okc_Debug.Log('1300: attribute START_DATE IS greater then end date for standard article', 2);
1686 END IF;
1687 OKC_API.Set_Message(G_APP_NAME, 'OKC_ART_START_GT_END_DATE');
1688 l_return_status := G_RET_STS_ERROR;
1689 ELSIF p_article_status in ('DRAFT','REJECTED','PENDING_APPROVAL') AND
1690 p_end_date < trunc(sysdate) THEN -- Added for Bug 3517002
1691 IF (l_debug = 'Y') THEN
1692 Okc_Debug.Log('1300: attribute END_DATE IS less then system date for standard article', 2);
1693 END IF;
1694 OKC_API.Set_Message(G_APP_NAME, 'OKC_ART_END_LT_SYS_DATE');
1695 l_return_status := G_RET_STS_ERROR;
1696 END IF;
1697 END IF;
1698
1699 -- Bug 3696909
1700
1701 IF ( p_insert_by_reference = 'Y' ) THEN
1702 IF ( p_reference_text IS NULL ) THEN
1703 IF (l_debug = 'Y') THEN
1704 Okc_Debug.Log('1300: attribute REFERENCE_TEXT cannot be null if INSERT_BY_REFERENCE is YES', 2);
1705 END IF;
1706 OKC_API.Set_Message(G_APP_NAME, 'OKC_ART_REF_TEXT_NULL');
1707 l_return_status := G_RET_STS_ERROR;
1708 END IF;
1709 END IF;
1710
1711
1712 -- The following is not required.
1713 /*
1714 IF ( p_date_approved is NOT NULL ) THEN
1715 IF ( p_date_approved > nvl(p_end_date,p_date_approved + 1) ) THEN
1716 IF (l_debug = 'Y') THEN
1717 Okc_Debug.Log('1300: attribute END_DATE IS less then approved date for standard article', 2);
1718 END IF;
1719 OKC_API.Set_Message(G_APP_NAME, 'OKC_ART_APPROVED_GT_END_DATE');
1720 l_return_status := G_RET_STS_ERROR;
1721 END IF;
1722 END IF;
1723 */
1724 -- In case of Standard Articles ONLY
1725 -- Validate that a global standard article cannot be created at a local org
1726 -- From the earlier version check
1727 -- 1. If global, current version cannot be local
1728 -- 2. earlier version cannot be status = DRAFT, REJECTED, PENDING APPROVAL'
1729 -- 3. Date Overlap
1730
1731 IF l_standard_yn = 'Y' THEN
1732 IF (G_CURRENT_ORG_ID <> G_GLOBAL_ORG_ID AND
1733 p_global_yn = 'Y') Then
1734 IF (l_debug = 'Y') THEN
1735 Okc_Debug.Log('2300: - attribute Global Article cannot be created at a local org', 2);
1736 END IF;
1737 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_GLOBAL_ART_LOCAL_ORG' );
1738 l_return_status := G_RET_STS_ERROR;
1739 END IF;
1740 -- Only first version exists or first version is imported
1741 -- skip comparison part with previous version
1742 IF nvl(p_import_action,'X') = 'N'
1743 OR l_highest_version_rec.article_version_number IS NULL THEN
1744 -- do not perform the following checks - only possible in the case of the first version being inserted
1745 NULL;
1746 ELSE
1747
1748 IF p_article_status in ('DRAFT','REJECTED','PENDING_APPROVAL') OR
1749 p_global_yn = 'N' OR
1750 -- done in above IF statement nvl(p_import_action, 'N') <> 'N' OR -- Added by MSENGUPT on 05/24 Bug#3648236 as earlier version check is needed for new version of import or update of an existing version
1751 p_start_date IS NOT NULL Then
1752
1753 /** moved outside of the IF right above
1754 IF l_highest_version_rec.article_version_number IS NULL Then
1755 -- do not perform the following checks - only possible in the case of the first version being inserted
1756 NULL;
1757 ELSE
1758 **/
1759
1760 -- Added by MSENGUPT on 05/26 that a provision -> clause change cannot happen unless it is in the first version in draft status
1761 -- New Version check should catch this exception (UIs already have this check/articles import will definitely validate this)
1762
1763 IF nvl(l_highest_version_rec.provision_yn, 'N') <> nvl(p_provision_yn, 'N') THEN
1764 IF (l_debug = 'Y') THEN
1765 Okc_Debug.Log('1800: - Earlier version is of Provison/Clause is different from current one.', 2);
1766 END IF;
1767 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_EARLIER_VER_PROVISION' );
1768 l_return_status := G_RET_STS_ERROR;
1769 END IF;
1770 -- End MSENGUPT 05/24
1771 -- Begin MSENGUPT 06/24 Global flag check is needed only for draft status clauses and import of a NEW VERSION.
1772 -- UI prohibits this change on approved clauses
1773
1774 IF p_article_status IN ('DRAFT', 'REJECTED', 'PENDING_APPROVAL') THEN
1775 --No need for this. p_import_action will never be 'N' OR (nvl(p_import_action, 'N') <> 'N')) THEN
1776
1777 IF (l_highest_version_rec.global_yn = 'Y' and p_global_yn = 'N') THEN
1778 IF (l_debug = 'Y') THEN
1779 Okc_Debug.Log('1800: - Earlier version is GLOBAL this version cannot be LOCAL', 2);
1780 END IF;
1781 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_EARLIER_VER_GLOBAL' );
1782 l_return_status := G_RET_STS_ERROR;
1783 END IF;
1784 END IF;
1785 -- End MSENGUPT 06/25
1786 IF (l_highest_version_rec.article_status IN ('DRAFT', 'REJECTED',
1787 'PENDING_APPROVAL')) AND
1788 (p_article_status IN ('DRAFT', 'REJECTED', 'PENDING_APPROVAL')) THEN
1789 --No need for this. p_import_action will never be 'N' OR (nvl(p_import_action, 'N') <> 'N')) THEN
1790
1791 IF (l_debug = 'Y') THEN
1792 Okc_Debug.Log('1800: - Already have one version available in DRAFT,REJECTED OR PENDING_APPROVAL', 2);
1793 END IF;
1794 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_EXIST_DRAFT_REJ');
1795 l_return_status := G_RET_STS_ERROR;
1796 END IF;
1797
1798 -- Date Published Check
1799 IF (l_highest_version_rec.date_published) >= p_date_published AND p_import_action IS NOT NULL THEN
1800 IF (l_debug = 'Y') THEN
1801 Okc_Debug.Log('1800: - Date Published of existing version is greater than date published provided for new version',2);
1802 END IF;
1803 --Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DATE_PUBLISHED');
1804 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_EXIST_DATE_PUBLISHED');
1805 l_return_status := G_RET_STS_ERROR;
1806 END IF;
1807
1808 -- Bug#3722445: Overlapping check - not required if later versions are APPROVED. UI will take care of that.
1809 -- The following scenario will occur only if the future (highest) version is DRAFT
1810 -- UI does not allow date updates if there is an approved version after the current version.
1811
1812 IF p_article_status IN ('HOLD','APPROVED') AND
1813 p_start_date < l_highest_version_rec.start_date THEN
1814
1815 -- Trying to manually update an approved article end date when another higher version exists
1816 -- if subsequent version is approved or on hold, then the end date of the current version cannot be set to NULL: UI will enforce too
1817 -- if subsequent version is in draft/rejected or pending approval status, the end date should not overlap.
1818
1819 if p_end_date IS NULL AND l_highest_version_rec.article_status in ('APPROVED', 'HOLD') THEN
1820 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_END_DATE_NULL');
1821 l_return_status := G_RET_STS_ERROR;
1822 elsif nvl(p_end_date, l_highest_version_rec.start_date-1) >= l_highest_version_rec.start_date then
1823 IF (l_debug = 'Y') THEN
1824 Okc_Debug.Log('1800: - Date overlap with earlier version', 2);
1825 END IF;
1826 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_DATE_OVRLP_EARLR_VER');
1827 l_return_status := G_RET_STS_ERROR;
1828 end if;
1829 ELSIF (l_highest_version_rec.start_date >= p_start_date) OR
1830 (nvl(l_highest_version_rec.end_date, p_start_date-1) >= p_start_date) THEN
1831 IF (l_debug = 'Y') THEN
1832 Okc_Debug.Log('1800: - Date overlap with earlier version', 2);
1833 END IF;
1834 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_DATE_OVRLP_EARLR_VER');
1835 l_return_status := G_RET_STS_ERROR;
1836 END IF; -- (l_highest_version_rec.start_date >= p_start_date) OR..
1837 END IF; -- IF (l_highest_version_rec.global_yn = 'Y' and p_global_yn = 'N')
1838 --END IF; --IF l_highest_version_rec.article_version_number IS NULL
1839 END IF; -- p_article_status in ('DRAFT','REJECTED','PENDING_APPROVAL') OR..
1840 END IF; --IF p_import_action = 'N', l_highest_version_rec.article_version_number IS NULL
1841
1842 IF p_article_status IN ('HOLD', 'APPROVED') AND
1843 p_date_approved IS NULL THEN
1844 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'DATE_APPROVED');
1845 l_return_status := G_RET_STS_ERROR;
1846 END IF;
1847
1848 -- Bug#3672511: Validation of start date of a localized clause should be >= start date of global clause.
1849 -- This is done only for localized articles i.e. at a local org.
1850 -- This can be done only for all draft/rejected clauses as well as all imported clauses (unless new)
1851
1852 IF G_CURRENT_ORG_ID <> G_GLOBAL_ORG_ID AND
1853 p_start_date IS NOT NULL AND
1854 nvl(l_highest_version_rec.adoption_type, 'LOCALIZED') = 'LOCALIZED' AND
1855 ((nvl(p_import_action,'N') <> 'N') OR p_article_status in ('DRAFT','REJECTED')) THEN
1856 OPEN l_global_csr(p_article_version_id, G_CURRENT_ORG_ID);
1857 FETCH l_global_csr INTO l_global_start_date;
1858 l_rownotfound := l_global_csr%NOTFOUND;
1859 CLOSE l_global_csr;
1860 IF l_rownotfound THEN
1861 IF (l_debug = 'Y') THEN
1862 Okc_Debug.Log('2300: - No Data found to check Start Date is less than Global Start Date', 2);
1863 END IF;
1864 ELSIF (l_global_start_date > p_start_date) THEN
1865 IF (l_debug = 'Y') THEN
1866 Okc_Debug.Log('2300: - Start Date is greater than Global Start Date', 2);
1867 END IF;
1868 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_CMP_START_DATE');
1869 l_return_status := G_RET_STS_ERROR;
1870 RETURN l_return_status ;
1871 END IF;
1872 END IF; -- end of if current org id <> global org id
1873
1874 -- End of fix for Bug#3672511
1875
1876 -- fix for bug#4006749 start
1877 -- muteshev
1878 if provision_flag_in_relations(p_article_id, G_CURRENT_ORG_ID, p_provision_yn)='E' then
1879 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_INV_RELATION_PROVISION');
1880 l_return_status := G_RET_STS_ERROR;
1881 RETURN l_return_status ;
1882 end if;
1883 -- fix for bug#4006749 end
1884
1885 END IF; -- if l_standard_yn = Y
1886 x_earlier_version_number := l_highest_version_rec.article_version_number;
1887 x_article_language := l_article_language;
1888 x_earlier_version_id := l_highest_version_rec.article_version_id;
1889 x_earlier_adoption_type := l_highest_version_rec.adoption_type;
1890
1891 /*+++++++++++++End of hand code +++++++++++++++++++*/
1892
1893 IF (l_debug = 'Y') THEN
1894 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
1895 END IF;
1896 RETURN l_return_status ;
1897
1898 EXCEPTION
1899 WHEN OTHERS THEN
1900
1901 IF (l_debug = 'Y') THEN
1902 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
1903 END IF;
1904
1905 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1906 p_msg_name => G_UNEXPECTED_ERROR,
1907 p_token1 => G_SQLCODE_TOKEN,
1908 p_token1_value => sqlcode,
1909 p_token2 => G_SQLERRM_TOKEN,
1910 p_token2_value => sqlerrm);
1911
1912
1913 IF l_article_id_csr%ISOPEN THEN
1914 CLOSE l_article_id_csr;
1915 END IF;
1916 IF l_global_csr%ISOPEN THEN
1917 CLOSE l_global_csr;
1918 END IF;
1919 IF l_highest_version_csr%ISOPEN THEN
1920 CLOSE l_highest_version_csr;
1921 END IF;
1922
1923 RETURN G_RET_STS_UNEXP_ERROR ;
1924
1925 END Validate_Record;
1926
1927 ---------------------------------------------------------------------------
1928 -- PROCEDURE validate_row
1929 ---------------------------------------------------------------------------
1930 ---------------------------------------
1931 -- validate_row for:OKC_ARTICLE_VERSIONS --
1932 ---------------------------------------
1933 PROCEDURE validate_row(
1934 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1935 p_import_action IN VARCHAR2 := NULL,
1936
1937 x_return_status OUT NOCOPY VARCHAR2,
1938 x_earlier_adoption_type OUT NOCOPY VARCHAR2,
1939 x_earlier_version_id OUT NOCOPY NUMBER,
1940 x_earlier_version_number OUT NOCOPY NUMBER,
1941
1942 p_article_version_id IN NUMBER,
1943 p_article_id IN NUMBER,
1944 p_article_version_number IN NUMBER,
1945 p_article_text IN CLOB,
1946 p_provision_yn IN VARCHAR2,
1947 p_insert_by_reference IN VARCHAR2,
1948 p_lock_text IN VARCHAR2,
1949 p_global_yn IN VARCHAR2,
1950 p_article_language IN VARCHAR2,
1951 p_article_status IN VARCHAR2,
1952 p_sav_release IN VARCHAR2,
1953 p_start_date IN DATE,
1954 p_end_date IN DATE,
1955 p_std_article_version_id IN NUMBER,
1956 p_display_name IN VARCHAR2,
1957 p_translated_yn IN VARCHAR2,
1958 p_article_description IN VARCHAR2,
1959 p_date_approved IN DATE,
1960 p_default_section IN VARCHAR2,
1961 p_reference_source IN VARCHAR2,
1962 p_reference_text IN VARCHAR2,
1963 p_orig_system_reference_code IN VARCHAR2,
1964 p_orig_system_reference_id1 IN VARCHAR2,
1965 p_orig_system_reference_id2 IN VARCHAR2,
1966 p_additional_instructions IN VARCHAR2,
1967 p_variation_description IN VARCHAR2,
1968 p_program_id IN NUMBER := NULL,
1969 p_program_login_id IN NUMBER := NULL,
1970 p_program_application_id IN NUMBER := NULL,
1971 p_request_id IN NUMBER := NULL,
1972 p_current_org_id IN NUMBER := NULL,
1973 p_date_published IN DATE ,
1974
1975 p_attribute_category IN VARCHAR2 := NULL,
1976 p_attribute1 IN VARCHAR2 := NULL,
1977 p_attribute2 IN VARCHAR2 := NULL,
1978 p_attribute3 IN VARCHAR2 := NULL,
1979 p_attribute4 IN VARCHAR2 := NULL,
1980 p_attribute5 IN VARCHAR2 := NULL,
1981 p_attribute6 IN VARCHAR2 := NULL,
1982 p_attribute7 IN VARCHAR2 := NULL,
1983 p_attribute8 IN VARCHAR2 := NULL,
1984 p_attribute9 IN VARCHAR2 := NULL,
1985 p_attribute10 IN VARCHAR2 := NULL,
1986 p_attribute11 IN VARCHAR2 := NULL,
1987 p_attribute12 IN VARCHAR2 := NULL,
1988 p_attribute13 IN VARCHAR2 := NULL,
1989 p_attribute14 IN VARCHAR2 := NULL,
1990 p_attribute15 IN VARCHAR2 := NULL,
1991
1992 p_object_version_number IN NUMBER,
1993 p_edited_in_word IN VARCHAR2,
1994 p_article_text_in_word IN BLOB,
1995 p_variable_code IN VARCHAR2 DEFAULT NULL --clm
1996 ) IS
1997 l_article_id OKC_ARTICLE_VERSIONS.ARTICLE_ID%TYPE;
1998 l_article_version_number OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_NUMBER%TYPE;
1999 l_article_text OKC_ARTICLE_VERSIONS.ARTICLE_TEXT%TYPE;
2000 l_provision_yn OKC_ARTICLE_VERSIONS.PROVISION_YN%TYPE;
2001 l_insert_by_reference OKC_ARTICLE_VERSIONS.INSERT_BY_REFERENCE%TYPE;
2002 l_lock_text OKC_ARTICLE_VERSIONS.LOCK_TEXT%TYPE;
2003 l_global_yn OKC_ARTICLE_VERSIONS.GLOBAL_YN%TYPE;
2004 l_article_language OKC_ARTICLE_VERSIONS.ARTICLE_LANGUAGE%TYPE;
2005 l_article_language_out OKC_ARTICLE_VERSIONS.ARTICLE_LANGUAGE%TYPE;
2006 l_article_status OKC_ARTICLE_VERSIONS.ARTICLE_STATUS%TYPE;
2007 l_sav_release OKC_ARTICLE_VERSIONS.SAV_RELEASE%TYPE;
2008 l_start_date OKC_ARTICLE_VERSIONS.START_DATE%TYPE;
2009 l_end_date OKC_ARTICLE_VERSIONS.END_DATE%TYPE;
2010 l_std_article_version_id OKC_ARTICLE_VERSIONS.STD_ARTICLE_VERSION_ID%TYPE;
2011 l_display_name OKC_ARTICLE_VERSIONS.DISPLAY_NAME%TYPE;
2012 l_translated_yn OKC_ARTICLE_VERSIONS.TRANSLATED_YN%TYPE;
2013 l_article_description OKC_ARTICLE_VERSIONS.ARTICLE_DESCRIPTION%TYPE;
2014 l_date_approved OKC_ARTICLE_VERSIONS.DATE_APPROVED%TYPE;
2015 l_default_section OKC_ARTICLE_VERSIONS.DEFAULT_SECTION%TYPE;
2016 l_reference_source OKC_ARTICLE_VERSIONS.REFERENCE_SOURCE%TYPE;
2017 l_reference_text OKC_ARTICLE_VERSIONS.REFERENCE_TEXT%TYPE;
2018 l_orig_system_reference_code OKC_ARTICLE_VERSIONS.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
2019 l_orig_system_reference_id1 OKC_ARTICLE_VERSIONS.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
2020 l_orig_system_reference_id2 OKC_ARTICLE_VERSIONS.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
2021 l_additional_instructions OKC_ARTICLE_VERSIONS.ADDITIONAL_INSTRUCTIONS%TYPE;
2022 l_variation_description OKC_ARTICLE_VERSIONS.VARIATION_DESCRIPTION%TYPE;
2023 l_date_published OKC_ARTICLE_VERSIONS.DATE_PUBLISHED%TYPE;
2024 l_program_id OKC_ARTICLE_VERSIONS.PROGRAM_ID%TYPE;
2025 l_program_login_id OKC_ARTICLE_VERSIONS.PROGRAM_LOGIN_ID%TYPE;
2026 l_program_application_id OKC_ARTICLE_VERSIONS.PROGRAM_APPLICATION_ID%TYPE;
2027 l_request_id OKC_ARTICLE_VERSIONS.REQUEST_ID%TYPE;
2028 l_attribute_category OKC_ARTICLE_VERSIONS.ATTRIBUTE_CATEGORY%TYPE;
2029 l_attribute1 OKC_ARTICLE_VERSIONS.ATTRIBUTE1%TYPE;
2030 l_attribute2 OKC_ARTICLE_VERSIONS.ATTRIBUTE2%TYPE;
2031 l_attribute3 OKC_ARTICLE_VERSIONS.ATTRIBUTE3%TYPE;
2032 l_attribute4 OKC_ARTICLE_VERSIONS.ATTRIBUTE4%TYPE;
2033 l_attribute5 OKC_ARTICLE_VERSIONS.ATTRIBUTE5%TYPE;
2034 l_attribute6 OKC_ARTICLE_VERSIONS.ATTRIBUTE6%TYPE;
2035 l_attribute7 OKC_ARTICLE_VERSIONS.ATTRIBUTE7%TYPE;
2036 l_attribute8 OKC_ARTICLE_VERSIONS.ATTRIBUTE8%TYPE;
2037 l_attribute9 OKC_ARTICLE_VERSIONS.ATTRIBUTE9%TYPE;
2038 l_attribute10 OKC_ARTICLE_VERSIONS.ATTRIBUTE10%TYPE;
2039 l_attribute11 OKC_ARTICLE_VERSIONS.ATTRIBUTE11%TYPE;
2040 l_attribute12 OKC_ARTICLE_VERSIONS.ATTRIBUTE12%TYPE;
2041 l_attribute13 OKC_ARTICLE_VERSIONS.ATTRIBUTE13%TYPE;
2042 l_attribute14 OKC_ARTICLE_VERSIONS.ATTRIBUTE14%TYPE;
2043 l_attribute15 OKC_ARTICLE_VERSIONS.ATTRIBUTE15%TYPE;
2044 l_object_version_number OKC_ARTICLE_VERSIONS.OBJECT_VERSION_NUMBER%TYPE;
2045 l_edited_in_word OKC_ARTICLE_VERSIONS.EDITED_IN_WORD%TYPE;
2046 l_article_text_in_word OKC_ARTICLE_VERSIONS.ARTICLE_TEXT_IN_WORD%TYPE;
2047 l_created_by OKC_ARTICLE_VERSIONS.CREATED_BY%TYPE;
2048 l_creation_date OKC_ARTICLE_VERSIONS.CREATION_DATE%TYPE;
2049 l_last_updated_by OKC_ARTICLE_VERSIONS.LAST_UPDATED_BY%TYPE;
2050 l_last_update_login OKC_ARTICLE_VERSIONS.LAST_UPDATE_LOGIN%TYPE;
2051 l_last_update_date OKC_ARTICLE_VERSIONS.LAST_UPDATE_DATE%TYPE;
2052 l_earlier_version_number OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_NUMBER%TYPE;
2053 l_earlier_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE;
2054 l_variable_code OKC_ARTICLE_VERSIONS.VARIABLE_CODE%TYPE; --clm
2055
2056 BEGIN
2057
2058 IF (l_debug = 'Y') THEN
2059 Okc_Debug.Log('3100: Entered validate_row', 2);
2060 END IF;
2061
2062 -- Setting attributes
2063 IF p_import_action IS NOT NULL Then
2064 l_article_id := p_article_id;
2065 l_article_version_number := p_article_version_number;
2066 l_article_text := p_article_text;
2067 l_provision_yn := p_provision_yn;
2068 l_insert_by_reference := p_insert_by_reference;
2069 l_lock_text := p_lock_text;
2070 l_global_yn := p_global_yn;
2071 l_article_language := p_article_language;
2072 l_article_status := p_article_status;
2073 l_sav_release := p_sav_release;
2074 l_start_date := p_start_date;
2075 l_end_date := p_end_date;
2076 l_std_article_version_id := p_std_article_version_id;
2077 l_display_name := p_display_name;
2078 l_translated_yn := p_translated_yn;
2079 l_article_description := p_article_description;
2080 l_date_approved := p_date_approved;
2081 l_default_section := p_default_section;
2082 l_reference_source := p_reference_source;
2083 l_reference_text := p_reference_text;
2084 l_orig_system_reference_code := p_orig_system_reference_code;
2085 l_orig_system_reference_id1 := p_orig_system_reference_id1;
2086 l_orig_system_reference_id2 := p_orig_system_reference_id2;
2087 l_additional_instructions := p_additional_instructions;
2088 l_variation_description := p_variation_description;
2089 l_date_published := p_date_published;
2090 l_program_id := p_program_id;
2091 l_program_login_id := p_program_login_id;
2092 l_program_application_id := p_program_application_id;
2093 l_request_id := p_request_id;
2094 l_attribute_category := p_attribute_category;
2095 l_attribute1 := p_attribute1;
2096 l_attribute2 := p_attribute2;
2097 l_attribute3 := p_attribute3;
2098 l_attribute4 := p_attribute4;
2099 l_attribute5 := p_attribute5;
2100 l_attribute6 := p_attribute6;
2101 l_attribute7 := p_attribute7;
2102 l_attribute8 := p_attribute8;
2103 l_attribute9 := p_attribute9;
2104 l_attribute10 := p_attribute10;
2105 l_attribute11 := p_attribute11;
2106 l_attribute12 := p_attribute12;
2107 l_attribute13 := p_attribute13;
2108 l_attribute14 := p_attribute14;
2109 l_attribute15 := p_attribute15;
2110 l_edited_in_word := p_edited_in_word;
2111 l_article_text_in_word := p_article_text_in_word;
2112 l_variable_code := p_variable_code; --clm
2113 ELSE
2114 x_return_status := Set_Attributes(
2115 p_article_version_id => p_article_version_id,
2116 p_article_id => p_article_id,
2117 p_article_version_number => p_article_version_number,
2118 p_article_text => p_article_text,
2119 p_provision_yn => p_provision_yn,
2120 p_insert_by_reference => p_insert_by_reference,
2121 p_lock_text => p_lock_text,
2122 p_global_yn => p_global_yn,
2123 p_article_language => p_article_language,
2124 p_article_status => p_article_status,
2125 p_sav_release => p_sav_release,
2126 p_start_date => p_start_date,
2127 p_end_date => p_end_date,
2128 p_std_article_version_id => p_std_article_version_id,
2129 p_display_name => p_display_name,
2130 p_translated_yn => p_translated_yn,
2131 p_article_description => p_article_description,
2132 p_date_approved => p_date_approved,
2133 p_default_section => p_default_section,
2134 p_reference_source => p_reference_source,
2135 p_reference_text => p_reference_text,
2136 p_orig_system_reference_code => p_orig_system_reference_code,
2137 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2138 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2139 p_additional_instructions => p_additional_instructions,
2140 p_variation_description => p_variation_description,
2141 p_date_published => p_date_published,
2142 p_program_id => p_program_id,
2143 p_program_login_id => p_program_login_id,
2144 p_program_application_id => p_program_application_id,
2145 p_request_id => p_request_id,
2146 p_attribute_category => p_attribute_category,
2147 p_attribute1 => p_attribute1,
2148 p_attribute2 => p_attribute2,
2149 p_attribute3 => p_attribute3,
2150 p_attribute4 => p_attribute4,
2151 p_attribute5 => p_attribute5,
2152 p_attribute6 => p_attribute6,
2153 p_attribute7 => p_attribute7,
2154 p_attribute8 => p_attribute8,
2155 p_attribute9 => p_attribute9,
2156 p_attribute10 => p_attribute10,
2157 p_attribute11 => p_attribute11,
2158 p_attribute12 => p_attribute12,
2159 p_attribute13 => p_attribute13,
2160 p_attribute14 => p_attribute14,
2161 p_attribute15 => p_attribute15,
2162 p_object_version_number => p_object_version_number,
2163 p_edited_in_word => p_edited_in_word,
2164 p_article_text_in_word => p_article_text_in_word,
2165 p_variable_code => p_variable_code, --clm
2166 x_article_id => l_article_id,
2167 x_article_version_number => l_article_version_number,
2168 x_article_text => l_article_text,
2169 x_provision_yn => l_provision_yn,
2170 x_insert_by_reference => l_insert_by_reference,
2171 x_lock_text => l_lock_text,
2172 x_global_yn => l_global_yn,
2173 x_article_language => l_article_language,
2174 x_article_status => l_article_status,
2175 x_sav_release => l_sav_release,
2176 x_start_date => l_start_date,
2177 x_end_date => l_end_date,
2178 x_std_article_version_id => l_std_article_version_id,
2179 x_display_name => l_display_name,
2180 x_translated_yn => l_translated_yn,
2181 x_article_description => l_article_description,
2182 x_date_approved => l_date_approved,
2183 x_default_section => l_default_section,
2184 x_reference_source => l_reference_source,
2185 x_reference_text => l_reference_text,
2186 x_orig_system_reference_code => l_orig_system_reference_code,
2187 x_orig_system_reference_id1 => l_orig_system_reference_id1,
2188 x_orig_system_reference_id2 => l_orig_system_reference_id2,
2189 x_additional_instructions => l_additional_instructions,
2190 x_variation_description => l_variation_description,
2191 x_date_published => l_date_published,
2192 x_program_id => l_program_id,
2193 x_program_login_id => l_program_login_id,
2194 x_program_application_id => l_program_application_id,
2195 x_request_id => l_request_id,
2196 x_attribute_category => l_attribute_category,
2197 x_attribute1 => l_attribute1,
2198 x_attribute2 => l_attribute2,
2199 x_attribute3 => l_attribute3,
2200 x_attribute4 => l_attribute4,
2201 x_attribute5 => l_attribute5,
2202 x_attribute6 => l_attribute6,
2203 x_attribute7 => l_attribute7,
2204 x_attribute8 => l_attribute8,
2205 x_attribute9 => l_attribute9,
2206 x_attribute10 => l_attribute10,
2207 x_attribute11 => l_attribute11,
2208 x_attribute12 => l_attribute12,
2209 x_attribute13 => l_attribute13,
2210 x_attribute14 => l_attribute14,
2211 x_attribute15 => l_attribute15,
2212 x_edited_in_word => l_edited_in_word,
2213 x_article_text_in_word => l_article_text_in_word,
2214 x_variable_code => l_variable_code --clm
2215 );
2216 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2218 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2219 RAISE FND_API.G_EXC_ERROR;
2220 END IF;
2221 END IF;
2222 -- MOAC
2223 G_CURRENT_ORG_ID := mo_global.get_current_org_id();
2224 if p_current_org_id IS NOT NULL Then
2225 G_CURRENT_ORG_ID := p_current_org_id;
2226 else
2227 if G_CURRENT_ORG_ID IS NULL Then
2228 IF (l_debug = 'Y') THEN
2229 Okc_Debug.Log('2300: - attribute G_CURRENT_ORG_ID is invalid', 2);
2230 END IF;
2231 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
2232 x_return_status := G_RET_STS_ERROR;
2233 RAISE FND_API.G_EXC_ERROR;
2234 end if;
2235
2236 end if;
2237 /*
2238 if p_current_org_id IS NULL Then
2239 OPEN cur_org_csr;
2240 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
2241 CLOSE cur_org_csr;
2242 else
2243 G_CURRENT_ORG_ID := p_current_org_id;
2244 end if;
2245 */
2246 -- Validate all non-missing attributes (Item Level Validation)
2247 x_return_status := Validate_Record(
2248 p_validation_level => p_validation_level,
2249 p_import_action => p_import_action,
2250 p_article_version_id => p_article_version_id,
2251 p_article_id => l_article_id,
2252 p_article_version_number => p_article_version_number,
2253 p_article_text => l_article_text,
2254 p_provision_yn => l_provision_yn,
2255 p_insert_by_reference => l_insert_by_reference,
2256 p_lock_text => l_lock_text,
2257 p_global_yn => l_global_yn,
2258 p_article_language => l_article_language,
2259 p_article_status => l_article_status,
2260 p_sav_release => l_sav_release,
2261 p_start_date => l_start_date,
2262 p_end_date => l_end_date,
2263 p_std_article_version_id => l_std_article_version_id,
2264 p_display_name => l_display_name,
2265 p_translated_yn => l_translated_yn,
2266 p_article_description => l_article_description,
2267 p_date_approved => l_date_approved,
2268 p_default_section => l_default_section,
2269 p_reference_source => l_reference_source,
2270 p_reference_text => l_reference_text,
2271 p_orig_system_reference_code => l_orig_system_reference_code,
2272 p_orig_system_reference_id1 => l_orig_system_reference_id1,
2273 p_orig_system_reference_id2 => l_orig_system_reference_id2,
2274 p_additional_instructions => l_additional_instructions,
2275 p_variation_description => l_variation_description,
2276 p_program_id => l_program_id,
2277 p_program_login_id => l_program_login_id,
2278 p_program_application_id => l_program_application_id,
2279 p_request_id => l_request_id,
2280 p_current_org_id => G_CURRENT_ORG_ID,
2281 p_date_published => l_date_published,
2282 p_attribute_category => l_attribute_category,
2283 p_attribute1 => l_attribute1,
2284 p_attribute2 => l_attribute2,
2285 p_attribute3 => l_attribute3,
2286 p_attribute4 => l_attribute4,
2287 p_attribute5 => l_attribute5,
2288 p_attribute6 => l_attribute6,
2289 p_attribute7 => l_attribute7,
2290 p_attribute8 => l_attribute8,
2291 p_attribute9 => l_attribute9,
2292 p_attribute10 => l_attribute10,
2293 p_attribute11 => l_attribute11,
2294 p_attribute12 => l_attribute12,
2295 p_attribute13 => l_attribute13,
2296 p_attribute14 => l_attribute14,
2297 p_attribute15 => l_attribute15,
2298 p_edited_in_word => l_edited_in_word,
2299 p_article_text_in_word => l_article_text_in_word,
2300 p_variable_code => l_variable_code, --clm
2301 x_earlier_adoption_type => x_earlier_adoption_type,
2302 x_article_language => l_article_language_out,
2303 x_earlier_version_id => x_earlier_version_id,
2304 x_earlier_version_number => x_earlier_version_number
2305 );
2306
2307 IF (l_debug = 'Y') THEN
2308 Okc_Debug.Log('3200: Leaving validate_row', 2);
2309 END IF;
2310
2311 EXCEPTION
2312 WHEN FND_API.G_EXC_ERROR THEN
2313 IF (l_debug = 'Y') THEN
2314 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
2315 END IF;
2316 x_return_status := G_RET_STS_ERROR;
2317
2318 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2319 IF (l_debug = 'Y') THEN
2320 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2321 END IF;
2322 x_return_status := G_RET_STS_UNEXP_ERROR;
2323
2324 WHEN OTHERS THEN
2325 IF (l_debug = 'Y') THEN
2326 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
2327 END IF;
2328 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2329 p_msg_name => G_UNEXPECTED_ERROR,
2330 p_token1 => G_SQLCODE_TOKEN,
2331 p_token1_value => sqlcode,
2332 p_token2 => G_SQLERRM_TOKEN,
2333 p_token2_value => sqlerrm);
2334 x_return_status := G_RET_STS_UNEXP_ERROR;
2335
2336 END Validate_Row;
2337
2338 ---------------------------------------------------------------------------
2339 -- PROCEDURE Insert_Row
2340 ---------------------------------------------------------------------------
2341 -------------------------------------
2342 -- Insert_Row for:OKC_ARTICLE_VERSIONS --
2343 -------------------------------------
2344 FUNCTION Insert_Row(
2345 p_article_version_id IN NUMBER,
2346 p_article_id IN NUMBER,
2347 p_article_version_number IN NUMBER,
2348 p_article_text IN CLOB,
2349 p_provision_yn IN VARCHAR2,
2350 p_insert_by_reference IN VARCHAR2,
2351 p_lock_text IN VARCHAR2,
2352 p_global_yn IN VARCHAR2,
2353 p_article_language IN VARCHAR2,
2354 p_article_status IN VARCHAR2,
2355 p_sav_release IN VARCHAR2,
2356 p_start_date IN DATE,
2357 p_end_date IN DATE,
2358 p_std_article_version_id IN NUMBER,
2359 p_display_name IN VARCHAR2,
2360 p_translated_yn IN VARCHAR2,
2361 p_article_description IN VARCHAR2,
2362 p_date_approved IN DATE,
2363 p_default_section IN VARCHAR2,
2364 p_adoption_type IN VARCHAR2,
2365 p_reference_source IN VARCHAR2,
2366 p_reference_text IN VARCHAR2,
2367 p_orig_system_reference_code IN VARCHAR2,
2368 p_orig_system_reference_id1 IN VARCHAR2,
2369 p_orig_system_reference_id2 IN VARCHAR2,
2370 p_additional_instructions IN VARCHAR2,
2371 p_variation_description IN VARCHAR2,
2372 p_date_published IN DATE,
2373 p_program_id IN NUMBER,
2374 p_program_login_id IN NUMBER,
2375 p_program_application_id IN NUMBER,
2376 p_request_id IN NUMBER,
2377 p_attribute_category IN VARCHAR2,
2378 p_attribute1 IN VARCHAR2,
2379 p_attribute2 IN VARCHAR2,
2380 p_attribute3 IN VARCHAR2,
2381 p_attribute4 IN VARCHAR2,
2382 p_attribute5 IN VARCHAR2,
2383 p_attribute6 IN VARCHAR2,
2384 p_attribute7 IN VARCHAR2,
2385 p_attribute8 IN VARCHAR2,
2386 p_attribute9 IN VARCHAR2,
2387 p_attribute10 IN VARCHAR2,
2388 p_attribute11 IN VARCHAR2,
2389 p_attribute12 IN VARCHAR2,
2390 p_attribute13 IN VARCHAR2,
2391 p_attribute14 IN VARCHAR2,
2392 p_attribute15 IN VARCHAR2,
2393 p_object_version_number IN NUMBER,
2394 p_edited_in_word IN VARCHAR2,
2395 p_article_text_in_word IN BLOB,
2396 p_created_by IN NUMBER,
2397 p_creation_date IN DATE,
2398 p_last_updated_by IN NUMBER,
2399 p_last_update_login IN NUMBER,
2400 p_last_update_date IN DATE,
2401 p_variable_code IN VARCHAR2 DEFAULT NULL --clm
2402
2403 ) RETURN VARCHAR2 IS
2404
2405
2406 l_program_id OKC_ARTICLE_VERSIONS.PROGRAM_ID%TYPE;
2407 l_program_login_id OKC_ARTICLE_VERSIONS.PROGRAM_LOGIN_ID%TYPE;
2408 l_program_appl_id OKC_ARTICLE_VERSIONS.PROGRAM_APPLICATION_ID%TYPE;
2409 l_request_id OKC_ARTICLE_VERSIONS.REQUEST_ID%TYPE;
2410
2411
2412 BEGIN
2413
2414 IF (l_debug = 'Y') THEN
2415 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
2416 END IF;
2417
2418
2419 IF FND_GLOBAL.CONC_PROGRAM_ID = -1 THEN
2420 l_program_id := NULL;
2421 l_program_login_id := NULL;
2422 ELSE
2423 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
2424 l_program_login_id := FND_GLOBAL.CONC_LOGIN_ID;
2425 END IF;
2426 IF FND_GLOBAL.PROG_APPL_ID = -1 THEN
2427 l_program_appl_id := NULL;
2428 ELSE
2429 l_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
2430 END IF;
2431 IF FND_GLOBAL.CONC_REQUEST_ID = -1 THEN
2432 l_request_id := NULL;
2433 ELSE
2434 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2435 END IF;
2436
2437 INSERT INTO OKC_ARTICLE_VERSIONS(
2438 ARTICLE_VERSION_ID,
2439 ARTICLE_ID,
2440 ARTICLE_VERSION_NUMBER,
2441 ARTICLE_TEXT,
2442 PROVISION_YN,
2443 INSERT_BY_REFERENCE,
2444 LOCK_TEXT,
2445 GLOBAL_YN,
2446 ARTICLE_LANGUAGE,
2447 ARTICLE_STATUS,
2448 SAV_RELEASE,
2449 START_DATE,
2450 END_DATE,
2451 STD_ARTICLE_VERSION_ID,
2452 DISPLAY_NAME,
2453 TRANSLATED_YN,
2454 ARTICLE_DESCRIPTION,
2455 DATE_APPROVED,
2456 DEFAULT_SECTION,
2457 ADOPTION_TYPE,
2458 REFERENCE_SOURCE,
2459 REFERENCE_TEXT,
2460 ORIG_SYSTEM_REFERENCE_CODE,
2461 ORIG_SYSTEM_REFERENCE_ID1,
2462 ORIG_SYSTEM_REFERENCE_ID2,
2463 ADDITIONAL_INSTRUCTIONS,
2464 VARIATION_DESCRIPTION,
2465 DATE_PUBLISHED,
2466 PROGRAM_ID,
2467 PROGRAM_LOGIN_ID,
2468 PROGRAM_APPLICATION_ID,
2469 REQUEST_ID,
2470 ATTRIBUTE_CATEGORY,
2471 ATTRIBUTE1,
2472 ATTRIBUTE2,
2473 ATTRIBUTE3,
2474 ATTRIBUTE4,
2475 ATTRIBUTE5,
2476 ATTRIBUTE6,
2477 ATTRIBUTE7,
2478 ATTRIBUTE8,
2479 ATTRIBUTE9,
2480 ATTRIBUTE10,
2481 ATTRIBUTE11,
2482 ATTRIBUTE12,
2483 ATTRIBUTE13,
2484 ATTRIBUTE14,
2485 ATTRIBUTE15,
2486 OBJECT_VERSION_NUMBER,
2487 EDITED_IN_WORD,
2488 ARTICLE_TEXT_IN_WORD,
2489 CREATED_BY,
2490 CREATION_DATE,
2491 LAST_UPDATED_BY,
2492 LAST_UPDATE_LOGIN,
2493 LAST_UPDATE_DATE,
2494 VARIABLE_CODE)
2495 VALUES (
2496 p_article_version_id,
2497 p_article_id,
2498 p_article_version_number,
2499 p_article_text,
2500 p_provision_yn,
2501 p_insert_by_reference,
2502 p_lock_text,
2503 p_global_yn,
2504 p_article_language,
2505 p_article_status,
2506 p_sav_release,
2507 p_start_date,
2508 p_end_date,
2509 p_std_article_version_id,
2510 p_display_name,
2511 p_translated_yn,
2512 p_article_description,
2513 p_date_approved,
2514 p_default_section,
2515 p_adoption_type,
2516 p_reference_source,
2517 p_reference_text,
2518 p_orig_system_reference_code,
2519 p_orig_system_reference_id1,
2520 p_orig_system_reference_id2,
2521 p_additional_instructions,
2522 p_variation_description,
2523 p_date_published,
2524 l_program_id,
2525 l_program_login_id,
2526 l_program_appl_id,
2527 l_request_id,
2528 p_attribute_category,
2529 p_attribute1,
2530 p_attribute2,
2531 p_attribute3,
2532 p_attribute4,
2533 p_attribute5,
2534 p_attribute6,
2535 p_attribute7,
2536 p_attribute8,
2537 p_attribute9,
2538 p_attribute10,
2539 p_attribute11,
2540 p_attribute12,
2541 p_attribute13,
2542 p_attribute14,
2543 p_attribute15,
2544 p_object_version_number,
2545 p_edited_in_word,
2546 p_article_text_in_word,
2547 p_created_by,
2548 p_creation_date,
2549 p_last_updated_by,
2550 p_last_update_login,
2551 p_last_update_date,
2552 p_variable_code);
2553
2554 IF (l_debug = 'Y') THEN
2555 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
2556 END IF;
2557
2558 RETURN( G_RET_STS_SUCCESS );
2559
2560 EXCEPTION
2561 WHEN OTHERS THEN
2562
2563 IF (l_debug = 'Y') THEN
2564 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
2565 END IF;
2566
2567 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2568 p_msg_name => G_UNEXPECTED_ERROR,
2569 p_token1 => G_SQLCODE_TOKEN,
2570 p_token1_value => sqlcode,
2571 p_token2 => G_SQLERRM_TOKEN,
2572 p_token2_value => sqlerrm);
2573
2574 RETURN( G_RET_STS_UNEXP_ERROR );
2575
2576 END Insert_Row;
2577
2578
2579 -------------------------------------
2580 -- Insert_Row for:OKC_ARTICLE_VERSIONS --
2581 -------------------------------------
2582 PROCEDURE Insert_Row(
2583 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2584 x_return_status OUT NOCOPY VARCHAR2,
2585
2586 p_article_version_id IN NUMBER,
2587 p_article_id IN NUMBER,
2588 p_article_version_number IN NUMBER,
2589 p_article_text IN CLOB,
2590 p_provision_yn IN VARCHAR2,
2591 p_insert_by_reference IN VARCHAR2,
2592 p_lock_text IN VARCHAR2,
2593 p_global_yn IN VARCHAR2,
2594 p_article_language IN VARCHAR2,
2595 p_article_status IN VARCHAR2,
2596 p_sav_release IN VARCHAR2,
2597 p_start_date IN DATE,
2598 p_end_date IN DATE,
2599 p_std_article_version_id IN NUMBER,
2600 p_display_name IN VARCHAR2,
2601 p_translated_yn IN VARCHAR2,
2602 p_article_description IN VARCHAR2,
2603 p_date_approved IN DATE,
2604 p_default_section IN VARCHAR2,
2605 p_reference_source IN VARCHAR2,
2606 p_reference_text IN VARCHAR2,
2607 p_orig_system_reference_code IN VARCHAR2,
2608 p_orig_system_reference_id1 IN VARCHAR2,
2609 p_orig_system_reference_id2 IN VARCHAR2,
2610 p_additional_instructions IN VARCHAR2,
2611 p_variation_description IN VARCHAR2,
2612 p_program_id IN NUMBER := NULL,
2613 p_program_login_id IN NUMBER := NULL,
2614 p_program_application_id IN NUMBER := NULL,
2615 p_request_id IN NUMBER := NULL,
2616 p_current_org_id IN NUMBER := NULL,
2617 p_date_published IN DATE ,
2618
2619 p_attribute_category IN VARCHAR2 := NULL,
2620 p_attribute1 IN VARCHAR2 := NULL,
2621 p_attribute2 IN VARCHAR2 := NULL,
2622 p_attribute3 IN VARCHAR2 := NULL,
2623 p_attribute4 IN VARCHAR2 := NULL,
2624 p_attribute5 IN VARCHAR2 := NULL,
2625 p_attribute6 IN VARCHAR2 := NULL,
2626 p_attribute7 IN VARCHAR2 := NULL,
2627 p_attribute8 IN VARCHAR2 := NULL,
2628 p_attribute9 IN VARCHAR2 := NULL,
2629 p_attribute10 IN VARCHAR2 := NULL,
2630 p_attribute11 IN VARCHAR2 := NULL,
2631 p_attribute12 IN VARCHAR2 := NULL,
2632 p_attribute13 IN VARCHAR2 := NULL,
2633 p_attribute14 IN VARCHAR2 := NULL,
2634 p_attribute15 IN VARCHAR2 := NULL,
2635 p_edited_in_word IN VARCHAR2,
2636 p_article_text_in_word IN BLOB,
2637 p_variable_code IN VARCHAR2 DEFAULT NULL, --clm
2638 x_earlier_adoption_type OUT NOCOPY VARCHAR2,
2639 x_earlier_version_id OUT NOCOPY NUMBER,
2640 x_article_version_id OUT NOCOPY NUMBER
2641
2642 ) IS
2643
2644 l_object_version_number OKC_ARTICLE_VERSIONS.OBJECT_VERSION_NUMBER%TYPE;
2645 l_article_version_number OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_NUMBER%TYPE;
2646 l_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;
2647 l_created_by OKC_ARTICLE_VERSIONS.CREATED_BY%TYPE;
2648 l_creation_date OKC_ARTICLE_VERSIONS.CREATION_DATE%TYPE;
2649 l_last_updated_by OKC_ARTICLE_VERSIONS.LAST_UPDATED_BY%TYPE;
2650 l_last_update_login OKC_ARTICLE_VERSIONS.LAST_UPDATE_LOGIN%TYPE;
2651 l_last_update_date OKC_ARTICLE_VERSIONS.LAST_UPDATE_DATE%TYPE;
2652 l_article_language OKC_ARTICLE_VERSIONS.ARTICLE_LANGUAGE%TYPE;
2653
2654 BEGIN
2655
2656 x_return_status := G_RET_STS_SUCCESS;
2657
2658 IF (l_debug = 'Y') THEN
2659 Okc_Debug.Log('4200: Entered Insert_Row', 2);
2660 END IF;
2661 --- Setting item attributes
2662 -- Set primary key value
2663 IF( p_article_version_id IS NULL ) THEN
2664 x_return_status := Get_Seq_Id(
2665 p_article_version_id => p_article_version_id,
2666 x_article_version_id => x_article_version_id
2667 );
2668 --- If any errors happen abort API
2669 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2671 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2672 RAISE FND_API.G_EXC_ERROR;
2673 END IF;
2674 ELSE
2675 x_article_version_id := p_article_version_id;
2676 END IF;
2677
2678 -- Set Internal columns
2679 l_object_version_number := 1;
2680 l_creation_date := Sysdate;
2681 l_created_by := Fnd_Global.User_Id;
2682 l_last_update_date := l_creation_date;
2683 l_last_updated_by := l_created_by;
2684 l_last_update_login := Fnd_Global.Login_Id;
2685
2686 -- MOAC
2687 G_CURRENT_ORG_ID := mo_global.get_current_org_id();
2688 if p_current_org_id IS NOT NULL Then
2689 G_CURRENT_ORG_ID := p_current_org_id;
2690 else
2691 if G_CURRENT_ORG_ID IS NULL Then
2692 IF (l_debug = 'Y') THEN
2693 Okc_Debug.Log('2300: - attribute G_CURRENT_ORG_ID is invalid', 2);
2694 END IF;
2695 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
2696 x_return_status := G_RET_STS_ERROR;
2697 RAISE FND_API.G_EXC_ERROR;
2698 end if;
2699
2700 end if;
2701 /*
2702 if p_current_org_id IS NULL Then
2703 OPEN cur_org_csr;
2704 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
2705 CLOSE cur_org_csr;
2706 else
2707 G_CURRENT_ORG_ID := p_current_org_id;
2708 end if;
2709 */
2710 --- Validate all non-missing attributes
2711 x_return_status := Validate_Record(
2712 p_validation_level => p_validation_level,
2713 p_article_version_id => x_article_version_id,
2714 p_article_id => p_article_id,
2715 p_article_version_number => p_article_version_number,
2716 p_article_text => p_article_text,
2717 p_provision_yn => p_provision_yn,
2718 p_insert_by_reference => p_insert_by_reference,
2719 p_lock_text => p_lock_text,
2720 p_global_yn => p_global_yn,
2721 p_article_language => p_article_language,
2722 p_article_status => p_article_status,
2723 p_sav_release => p_sav_release,
2724 p_start_date => p_start_date,
2725 p_end_date => p_end_date,
2726 p_std_article_version_id => p_std_article_version_id,
2727 p_display_name => p_display_name,
2728 p_translated_yn => p_translated_yn,
2729 p_article_description => p_article_description,
2730 p_date_approved => p_date_approved,
2731 p_default_section => p_default_section,
2732 p_reference_source => p_reference_source,
2733 p_reference_text => p_reference_text,
2734 p_orig_system_reference_code => p_orig_system_reference_code,
2735 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2736 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2737 p_additional_instructions => p_additional_instructions,
2738 p_variation_description => p_variation_description,
2739 p_program_id => p_program_id,
2740 p_program_login_id => p_program_login_id,
2741 p_program_application_id => p_program_application_id,
2742 p_request_id => p_request_id,
2743 p_current_org_id => G_CURRENT_ORG_ID,
2744 p_date_published => p_date_published,
2745 p_attribute_category => p_attribute_category,
2746 p_attribute1 => p_attribute1,
2747 p_attribute2 => p_attribute2,
2748 p_attribute3 => p_attribute3,
2749 p_attribute4 => p_attribute4,
2750 p_attribute5 => p_attribute5,
2751 p_attribute6 => p_attribute6,
2752 p_attribute7 => p_attribute7,
2753 p_attribute8 => p_attribute8,
2754 p_attribute9 => p_attribute9,
2755 p_attribute10 => p_attribute10,
2756 p_attribute11 => p_attribute11,
2757 p_attribute12 => p_attribute12,
2758 p_attribute13 => p_attribute13,
2759 p_attribute14 => p_attribute14,
2760 p_attribute15 => p_attribute15,
2761 p_edited_in_word => p_edited_in_word,
2762 p_article_text_in_word => p_article_text_in_word,
2763 p_variable_code => p_variable_code, --clm
2764 x_article_language => l_article_language,
2765 x_earlier_adoption_type => x_earlier_adoption_type,
2766 x_earlier_version_id => x_earlier_version_id,
2767 x_earlier_version_number => l_article_version_number
2768 );
2769 --- If any errors happen abort API
2770 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2772 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2773 RAISE FND_API.G_EXC_ERROR;
2774 END IF;
2775
2776 --------------------------------------------
2777 -- Call the internal Insert_Row for each child record
2778 --------------------------------------------
2779 IF (l_debug = 'Y') THEN
2780 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
2781 END IF;
2782 -- Generate the highest article version number for the article in the case of insert. This was obtained from the validate -- row while validating a number of attributes based on earlier version.
2783 -- Though this approach is not pretty but it does save an expensive SQL.
2784
2785 l_article_version_number := nvl(l_article_version_number,0) + 1;
2786 l_adoption_type := x_earlier_adoption_type;
2787 if l_adoption_type IS NULL and
2788 G_CURRENT_ORG_ID <> G_GLOBAL_ORG_ID THEN
2789 l_adoption_type := 'LOCAL';
2790 end if;
2791 x_return_status := Insert_Row(
2792 p_article_version_id => x_article_version_id,
2793 p_article_id => p_article_id,
2794 p_article_version_number => l_article_version_number,
2795 p_article_text => p_article_text,
2796 p_provision_yn => p_provision_yn,
2797 p_insert_by_reference => p_insert_by_reference,
2798 p_lock_text => p_lock_text,
2799 p_global_yn => p_global_yn,
2800 p_article_language => l_article_language,
2801 p_article_status => p_article_status,
2802 p_sav_release => p_sav_release,
2803 p_start_date => p_start_date,
2804 p_end_date => p_end_date,
2805 p_std_article_version_id => p_std_article_version_id,
2806 p_display_name => p_display_name,
2807 p_translated_yn => p_translated_yn,
2808 p_article_description => p_article_description,
2809 p_date_approved => p_date_approved,
2810 p_default_section => p_default_section,
2811 p_adoption_type => l_adoption_type,
2812 p_reference_source => p_reference_source,
2813 p_reference_text => p_reference_text,
2814 p_orig_system_reference_code => p_orig_system_reference_code,
2815 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2816 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2817 p_additional_instructions => p_additional_instructions,
2818 p_variation_description => p_variation_description,
2819 p_date_published => p_date_published,
2820 p_program_id => p_program_id,
2821 p_program_login_id => p_program_login_id,
2822 p_program_application_id => p_program_application_id,
2823 p_request_id => p_request_id,
2824 p_attribute_category => p_attribute_category,
2825 p_attribute1 => p_attribute1,
2826 p_attribute2 => p_attribute2,
2827 p_attribute3 => p_attribute3,
2828 p_attribute4 => p_attribute4,
2829 p_attribute5 => p_attribute5,
2830 p_attribute6 => p_attribute6,
2831 p_attribute7 => p_attribute7,
2832 p_attribute8 => p_attribute8,
2833 p_attribute9 => p_attribute9,
2834 p_attribute10 => p_attribute10,
2835 p_attribute11 => p_attribute11,
2836 p_attribute12 => p_attribute12,
2837 p_attribute13 => p_attribute13,
2838 p_attribute14 => p_attribute14,
2839 p_attribute15 => p_attribute15,
2840 p_object_version_number => l_object_version_number,
2841 p_edited_in_word => p_edited_in_word,
2842 p_article_text_in_word => p_article_text_in_word,
2843 p_created_by => l_created_by,
2844 p_creation_date => l_creation_date,
2845 p_last_updated_by => l_last_updated_by,
2846 p_last_update_login => l_last_update_login,
2847 p_last_update_date => l_last_update_date,
2848 p_variable_code => p_variable_code --clm
2849 );
2850 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2852 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2853 RAISE FND_API.G_EXC_ERROR;
2854 END IF;
2855
2856
2857
2858 IF (l_debug = 'Y') THEN
2859 Okc_Debug.Log('4500: Leaving Insert_Row', 2);
2860 END IF;
2861
2862 EXCEPTION
2863 WHEN FND_API.G_EXC_ERROR THEN
2864 IF (l_debug = 'Y') THEN
2865 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
2866 END IF;
2867 x_return_status := G_RET_STS_ERROR;
2868
2869 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2870 IF (l_debug = 'Y') THEN
2871 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2872 END IF;
2873 x_return_status := G_RET_STS_UNEXP_ERROR;
2874
2875 WHEN OTHERS THEN
2876 IF (l_debug = 'Y') THEN
2877 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
2878 END IF;
2879 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2880 p_msg_name => G_UNEXPECTED_ERROR,
2881 p_token1 => G_SQLCODE_TOKEN,
2882 p_token1_value => sqlcode,
2883 p_token2 => G_SQLERRM_TOKEN,
2884 p_token2_value => sqlerrm);
2885 x_return_status := G_RET_STS_UNEXP_ERROR;
2886
2887 END Insert_Row;
2888 ---------------------------------------------------------------------------
2889 -- PROCEDURE Lock_Row
2890 ---------------------------------------------------------------------------
2891 -----------------------------------
2892 -- Lock_Row for:OKC_ARTICLE_VERSIONS --
2893 -----------------------------------
2894 FUNCTION Lock_Row(
2895 p_article_version_id IN NUMBER,
2896 p_object_version_number IN NUMBER
2897 ) RETURN VARCHAR2 IS
2898
2899 E_Resource_Busy EXCEPTION;
2900 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2901
2902 CURSOR lock_csr (cp_article_version_id NUMBER, cp_object_version_number NUMBER) IS
2903 SELECT object_version_number
2904 FROM OKC_ARTICLE_VERSIONS
2905 WHERE ARTICLE_VERSION_ID = cp_article_version_id
2906 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
2907 FOR UPDATE OF object_version_number NOWAIT;
2908
2909 CURSOR lchk_csr (cp_article_version_id NUMBER) IS
2910 SELECT object_version_number
2911 FROM OKC_ARTICLE_VERSIONS
2912 WHERE ARTICLE_VERSION_ID = cp_article_version_id;
2913
2914 l_return_status VARCHAR2(1);
2915
2916 l_object_version_number OKC_ARTICLE_VERSIONS.OBJECT_VERSION_NUMBER%TYPE;
2917
2918 l_row_notfound BOOLEAN := FALSE;
2919 BEGIN
2920
2921 IF (l_debug = 'Y') THEN
2922 Okc_Debug.Log('4900: Entered Lock_Row', 2);
2923 END IF;
2924
2925
2926 BEGIN
2927 OPEN lock_csr( p_article_version_id, p_object_version_number );
2928 FETCH lock_csr INTO l_object_version_number;
2929 l_row_notfound := lock_csr%NOTFOUND;
2930 CLOSE lock_csr;
2931
2932 EXCEPTION
2933 WHEN E_Resource_Busy THEN
2934
2935 IF (l_debug = 'Y') THEN
2936 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
2937 END IF;
2938
2939 IF (lock_csr%ISOPEN) THEN
2940 CLOSE lock_csr;
2941 END IF;
2942 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
2943 RETURN( G_RET_STS_ERROR );
2944 END;
2945
2946 IF ( l_row_notfound ) THEN
2947 l_return_status := G_RET_STS_ERROR;
2948
2949 OPEN lchk_csr(p_article_version_id);
2950 FETCH lchk_csr INTO l_object_version_number;
2951 l_row_notfound := lchk_csr%NOTFOUND;
2952 CLOSE lchk_csr;
2953
2954 IF (l_row_notfound) THEN
2955 Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
2956 ELSIF l_object_version_number > p_object_version_number THEN
2957 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
2958 ELSIF l_object_version_number = -1 THEN
2959 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2960 ELSE -- it can be the only above condition. It can happen after restore version
2961 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
2962 END IF;
2963 ELSE
2964 l_return_status := G_RET_STS_SUCCESS;
2965 END IF;
2966
2967 IF (l_debug = 'Y') THEN
2968 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
2969 END IF;
2970
2971 RETURN( l_return_status );
2972
2973 EXCEPTION
2974 WHEN OTHERS THEN
2975
2976 IF (lock_csr%ISOPEN) THEN
2977 CLOSE lock_csr;
2978 END IF;
2979 IF (lchk_csr%ISOPEN) THEN
2980 CLOSE lchk_csr;
2981 END IF;
2982
2983 IF (l_debug = 'Y') THEN
2984 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
2985 END IF;
2986
2987 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2988 p_msg_name => G_UNEXPECTED_ERROR,
2989 p_token1 => G_SQLCODE_TOKEN,
2990 p_token1_value => sqlcode,
2991 p_token2 => G_SQLERRM_TOKEN,
2992 p_token2_value => sqlerrm);
2993
2994 RETURN( G_RET_STS_UNEXP_ERROR );
2995 END Lock_Row;
2996
2997 -----------------------------------
2998 -- Lock_Row for:OKC_ARTICLE_VERSIONS --
2999 -----------------------------------
3000 PROCEDURE Lock_Row(
3001 x_return_status OUT NOCOPY VARCHAR2,
3002
3003 p_article_version_id IN NUMBER,
3004 p_object_version_number IN NUMBER
3005 ) IS
3006 BEGIN
3007
3008 IF (l_debug = 'Y') THEN
3009 Okc_Debug.Log('5700: Entered Lock_Row', 2);
3010 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
3011 END IF;
3012
3013 --------------------------------------------
3014 -- Call the LOCK_ROW for each _B child record
3015 --------------------------------------------
3016 x_return_status := Lock_Row(
3017 p_article_version_id => p_article_version_id,
3018 p_object_version_number => p_object_version_number
3019 );
3020 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3022 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3023 RAISE FND_API.G_EXC_ERROR;
3024 END IF;
3025
3026
3027
3028 IF (l_debug = 'Y') THEN
3029 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
3030 END IF;
3031
3032 EXCEPTION
3033 WHEN FND_API.G_EXC_ERROR THEN
3034 IF (l_debug = 'Y') THEN
3035 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
3036 END IF;
3037 x_return_status := G_RET_STS_ERROR;
3038
3039 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3040 IF (l_debug = 'Y') THEN
3041 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
3042 END IF;
3043 x_return_status := G_RET_STS_UNEXP_ERROR;
3044
3045 WHEN OTHERS THEN
3046 IF (l_debug = 'Y') THEN
3047 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
3048 END IF;
3049 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3050 p_msg_name => G_UNEXPECTED_ERROR,
3051 p_token1 => G_SQLCODE_TOKEN,
3052 p_token1_value => sqlcode,
3053 p_token2 => G_SQLERRM_TOKEN,
3054 p_token2_value => sqlerrm);
3055 x_return_status := G_RET_STS_UNEXP_ERROR;
3056
3057 END Lock_Row;
3058 ---------------------------------------------------------------------------
3059 -- PROCEDURE Update_Row
3060 ---------------------------------------------------------------------------
3061 -------------------------------------
3062 -- Update_Row for:OKC_ARTICLE_VERSIONS --
3063 -------------------------------------
3064 FUNCTION Update_Row(
3065 p_article_version_id IN NUMBER,
3066 p_article_id IN NUMBER,
3067 p_article_version_number IN NUMBER,
3068 p_article_text IN CLOB,
3069 p_provision_yn IN VARCHAR2,
3070 p_insert_by_reference IN VARCHAR2,
3071 p_lock_text IN VARCHAR2,
3072 p_global_yn IN VARCHAR2,
3073 p_article_language IN VARCHAR2,
3074 p_article_status IN VARCHAR2,
3075 p_sav_release IN VARCHAR2,
3076 p_start_date IN DATE,
3077 p_end_date IN DATE,
3078 p_std_article_version_id IN NUMBER,
3079 p_display_name IN VARCHAR2,
3080 p_translated_yn IN VARCHAR2,
3081 p_article_description IN VARCHAR2,
3082 p_date_approved IN DATE,
3083 p_default_section IN VARCHAR2,
3084 p_reference_source IN VARCHAR2,
3085 p_reference_text IN VARCHAR2,
3086 p_orig_system_reference_code IN VARCHAR2,
3087 p_orig_system_reference_id1 IN VARCHAR2,
3088 p_orig_system_reference_id2 IN VARCHAR2,
3089 p_additional_instructions IN VARCHAR2,
3090 p_variation_description IN VARCHAR2,
3091 p_date_published IN DATE,
3092 p_program_id IN NUMBER,
3093 p_program_login_id IN NUMBER,
3094 p_program_application_id IN NUMBER,
3095 p_request_id IN NUMBER,
3096 p_attribute_category IN VARCHAR2,
3097 p_attribute1 IN VARCHAR2,
3098 p_attribute2 IN VARCHAR2,
3099 p_attribute3 IN VARCHAR2,
3100 p_attribute4 IN VARCHAR2,
3101 p_attribute5 IN VARCHAR2,
3102 p_attribute6 IN VARCHAR2,
3103 p_attribute7 IN VARCHAR2,
3104 p_attribute8 IN VARCHAR2,
3105 p_attribute9 IN VARCHAR2,
3106 p_attribute10 IN VARCHAR2,
3107 p_attribute11 IN VARCHAR2,
3108 p_attribute12 IN VARCHAR2,
3109 p_attribute13 IN VARCHAR2,
3110 p_attribute14 IN VARCHAR2,
3111 p_attribute15 IN VARCHAR2,
3112 p_object_version_number IN NUMBER,
3113 p_edited_in_word IN VARCHAR2,
3114 p_article_text_in_word IN BLOB,
3115 p_created_by IN NUMBER,
3116 p_creation_date IN DATE,
3117 p_last_updated_by IN NUMBER,
3118 p_last_update_login IN NUMBER,
3119 p_last_update_date IN DATE,
3120 p_variable_code IN VARCHAR2 --clm
3121 ) RETURN VARCHAR2 IS
3122
3123
3124 l_program_id OKC_ARTICLE_VERSIONS.PROGRAM_ID%TYPE;
3125 l_program_login_id OKC_ARTICLE_VERSIONS.PROGRAM_LOGIN_ID%TYPE;
3126 l_program_appl_id OKC_ARTICLE_VERSIONS.PROGRAM_APPLICATION_ID%TYPE;
3127 l_request_id OKC_ARTICLE_VERSIONS.REQUEST_ID%TYPE;
3128
3129 BEGIN
3130
3131 IF (l_debug = 'Y') THEN
3132 Okc_Debug.Log('6400: Entered Update_Row', 2);
3133 END IF;
3134
3135 IF FND_GLOBAL.CONC_PROGRAM_ID = -1 THEN
3136 l_program_id := NULL;
3137 l_program_login_id := NULL;
3138 ELSE
3139 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
3140 l_program_login_id := FND_GLOBAL.CONC_LOGIN_ID;
3141 END IF;
3142 IF FND_GLOBAL.PROG_APPL_ID = -1 THEN
3143 l_program_appl_id := NULL;
3144 ELSE
3145 l_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
3146 END IF;
3147 IF FND_GLOBAL.CONC_REQUEST_ID = -1 THEN
3148 l_request_id := NULL;
3149 ELSE
3150 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3151 END IF;
3152
3153
3154 UPDATE OKC_ARTICLE_VERSIONS
3155 SET ARTICLE_ID = p_article_id,
3156 -- ARTICLE_VERSION_NUMBER = p_article_version_number,
3157 ARTICLE_TEXT = p_article_text,
3158 PROVISION_YN = p_provision_yn,
3159 INSERT_BY_REFERENCE = p_insert_by_reference,
3160 LOCK_TEXT = p_lock_text,
3161 GLOBAL_YN = p_global_yn,
3162 -- ARTICLE_LANGUAGE = p_article_language,
3163 ARTICLE_STATUS = p_article_status,
3164 SAV_RELEASE = p_sav_release,
3165 START_DATE = p_start_date,
3166 END_DATE = p_end_date,
3167 STD_ARTICLE_VERSION_ID = p_std_article_version_id,
3168 DISPLAY_NAME = p_display_name,
3169 TRANSLATED_YN = p_translated_yn,
3170 ARTICLE_DESCRIPTION = p_article_description,
3171 DATE_APPROVED = p_date_approved,
3172 DEFAULT_SECTION = p_default_section,
3173 REFERENCE_SOURCE = p_reference_source,
3174 REFERENCE_TEXT = p_reference_text,
3175 ORIG_SYSTEM_REFERENCE_CODE = p_orig_system_reference_code,
3176 ORIG_SYSTEM_REFERENCE_ID1 = p_orig_system_reference_id1,
3177 ORIG_SYSTEM_REFERENCE_ID2 = p_orig_system_reference_id2,
3178 ADDITIONAL_INSTRUCTIONS = p_additional_instructions,
3179 VARIATION_DESCRIPTION = p_variation_description,
3180 -- DATE_PUBLISHED = p_date_published,
3181 PROGRAM_ID = l_program_id,
3182 REQUEST_ID = l_request_id,
3183 PROGRAM_LOGIN_ID = l_program_login_id,
3184 PROGRAM_APPLICATION_ID = l_program_appl_id,
3185 ATTRIBUTE_CATEGORY = p_attribute_category,
3186 ATTRIBUTE1 = p_attribute1,
3187 ATTRIBUTE2 = p_attribute2,
3188 ATTRIBUTE3 = p_attribute3,
3189 ATTRIBUTE4 = p_attribute4,
3190 ATTRIBUTE5 = p_attribute5,
3191 ATTRIBUTE6 = p_attribute6,
3192 ATTRIBUTE7 = p_attribute7,
3193 ATTRIBUTE8 = p_attribute8,
3194 ATTRIBUTE9 = p_attribute9,
3195 ATTRIBUTE10 = p_attribute10,
3196 ATTRIBUTE11 = p_attribute11,
3197 ATTRIBUTE12 = p_attribute12,
3198 ATTRIBUTE13 = p_attribute13,
3199 ATTRIBUTE14 = p_attribute14,
3200 ATTRIBUTE15 = p_attribute15,
3201 -- muteshev
3202 OBJECT_VERSION_NUMBER = object_version_number+1,
3203 EDITED_IN_WORD = p_edited_in_word,
3204 ARTICLE_TEXT_IN_WORD = p_article_text_in_word,
3205 LAST_UPDATED_BY = p_last_updated_by,
3206 LAST_UPDATE_LOGIN = p_last_update_login,
3207 LAST_UPDATE_DATE = p_last_update_date,
3208 --clm
3209 VARIABLE_CODE = p_variable_code
3210 WHERE ARTICLE_VERSION_ID = p_article_version_id;
3211
3212 IF (l_debug = 'Y') THEN
3213 Okc_Debug.Log('6500: Leaving Update_Row', 2);
3214 END IF;
3215
3216 RETURN G_RET_STS_SUCCESS ;
3217
3218 EXCEPTION
3219 WHEN OTHERS THEN
3220
3221 IF (l_debug = 'Y') THEN
3222 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
3223 END IF;
3224
3225 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3226 p_msg_name => G_UNEXPECTED_ERROR,
3227 p_token1 => G_SQLCODE_TOKEN,
3228 p_token1_value => sqlcode,
3229 p_token2 => G_SQLERRM_TOKEN,
3230 p_token2_value => sqlerrm);
3231
3232 RETURN G_RET_STS_UNEXP_ERROR ;
3233
3234 END Update_Row;
3235
3236 -------------------------------------
3237 -- Update_Row for:OKC_ARTICLE_VERSIONS --
3238 -------------------------------------
3239 PROCEDURE Update_Row(
3240 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3241
3242 x_return_status OUT NOCOPY VARCHAR2,
3243
3244 p_article_version_id IN NUMBER,
3245 p_article_id IN NUMBER,
3246 p_article_version_number IN NUMBER,
3247 p_article_text IN CLOB,
3248 p_provision_yn IN VARCHAR2,
3249 p_insert_by_reference IN VARCHAR2,
3250 p_lock_text IN VARCHAR2,
3251 p_global_yn IN VARCHAR2,
3252 p_article_language IN VARCHAR2,
3253 p_article_status IN VARCHAR2,
3254 p_sav_release IN VARCHAR2,
3255 p_start_date IN DATE,
3256 p_end_date IN DATE,
3257 p_std_article_version_id IN NUMBER,
3258 p_display_name IN VARCHAR2,
3259 p_translated_yn IN VARCHAR2,
3260 p_article_description IN VARCHAR2,
3261 p_date_approved IN DATE,
3262 p_default_section IN VARCHAR2,
3263 p_reference_source IN VARCHAR2,
3264 p_reference_text IN VARCHAR2,
3265 p_orig_system_reference_code IN VARCHAR2,
3266 p_orig_system_reference_id1 IN VARCHAR2,
3267 p_orig_system_reference_id2 IN VARCHAR2,
3268 p_additional_instructions IN VARCHAR2,
3269 p_variation_description IN VARCHAR2,
3270 p_program_id IN NUMBER := NULL,
3271 p_program_login_id IN NUMBER := NULL,
3272 p_program_application_id IN NUMBER := NULL,
3273 p_request_id IN NUMBER := NULL,
3274 p_current_org_id IN NUMBER := NULL,
3275 p_date_published IN DATE ,
3276
3277 p_attribute_category IN VARCHAR2 := NULL,
3278 p_attribute1 IN VARCHAR2 := NULL,
3279 p_attribute2 IN VARCHAR2 := NULL,
3280 p_attribute3 IN VARCHAR2 := NULL,
3281 p_attribute4 IN VARCHAR2 := NULL,
3282 p_attribute5 IN VARCHAR2 := NULL,
3283 p_attribute6 IN VARCHAR2 := NULL,
3284 p_attribute7 IN VARCHAR2 := NULL,
3285 p_attribute8 IN VARCHAR2 := NULL,
3286 p_attribute9 IN VARCHAR2 := NULL,
3287 p_attribute10 IN VARCHAR2 := NULL,
3288 p_attribute11 IN VARCHAR2 := NULL,
3289 p_attribute12 IN VARCHAR2 := NULL,
3290 p_attribute13 IN VARCHAR2 := NULL,
3291 p_attribute14 IN VARCHAR2 := NULL,
3292 p_attribute15 IN VARCHAR2 := NULL,
3293 p_object_version_number IN NUMBER,
3294 p_edited_in_word IN VARCHAR2,
3295 p_article_text_in_word IN BLOB,
3296 p_variable_code IN VARCHAR2 DEFAULT NULL, ---clm
3297 x_article_status IN VARCHAR2,
3298 x_article_id OUT NOCOPY NUMBER,
3299 x_earlier_version_id OUT NOCOPY NUMBER
3300 ) IS
3301
3302 l_article_id OKC_ARTICLE_VERSIONS.ARTICLE_ID%TYPE;
3303 l_article_version_number OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_NUMBER%TYPE;
3304 l_article_text OKC_ARTICLE_VERSIONS.ARTICLE_TEXT%TYPE;
3305 l_provision_yn OKC_ARTICLE_VERSIONS.PROVISION_YN%TYPE;
3306 l_insert_by_reference OKC_ARTICLE_VERSIONS.INSERT_BY_REFERENCE%TYPE;
3307 l_lock_text OKC_ARTICLE_VERSIONS.LOCK_TEXT%TYPE;
3308 l_global_yn OKC_ARTICLE_VERSIONS.GLOBAL_YN%TYPE;
3309 l_article_language OKC_ARTICLE_VERSIONS.ARTICLE_LANGUAGE%TYPE;
3310 l_article_language_out OKC_ARTICLE_VERSIONS.ARTICLE_LANGUAGE%TYPE;
3311 l_article_status OKC_ARTICLE_VERSIONS.ARTICLE_STATUS%TYPE;
3312 l_sav_release OKC_ARTICLE_VERSIONS.SAV_RELEASE%TYPE;
3313 l_start_date OKC_ARTICLE_VERSIONS.START_DATE%TYPE;
3314 l_end_date OKC_ARTICLE_VERSIONS.END_DATE%TYPE;
3315 l_std_article_version_id OKC_ARTICLE_VERSIONS.STD_ARTICLE_VERSION_ID%TYPE;
3316 l_display_name OKC_ARTICLE_VERSIONS.DISPLAY_NAME%TYPE;
3317 l_translated_yn OKC_ARTICLE_VERSIONS.TRANSLATED_YN%TYPE;
3318 l_article_description OKC_ARTICLE_VERSIONS.ARTICLE_DESCRIPTION%TYPE;
3319 l_date_approved OKC_ARTICLE_VERSIONS.DATE_APPROVED%TYPE;
3320 l_default_section OKC_ARTICLE_VERSIONS.DEFAULT_SECTION%TYPE;
3321 l_reference_source OKC_ARTICLE_VERSIONS.REFERENCE_SOURCE%TYPE;
3322 l_reference_text OKC_ARTICLE_VERSIONS.REFERENCE_TEXT%TYPE;
3323 l_orig_system_reference_code OKC_ARTICLE_VERSIONS.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
3324 l_orig_system_reference_id1 OKC_ARTICLE_VERSIONS.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
3325 l_orig_system_reference_id2 OKC_ARTICLE_VERSIONS.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
3326 l_additional_instructions OKC_ARTICLE_VERSIONS.ADDITIONAL_INSTRUCTIONS%TYPE;
3327 l_variation_description OKC_ARTICLE_VERSIONS.VARIATION_DESCRIPTION%TYPE;
3328 l_date_published OKC_ARTICLE_VERSIONS.DATE_PUBLISHED%TYPE;
3329 l_program_id OKC_ARTICLE_VERSIONS.PROGRAM_ID%TYPE;
3330 l_program_login_id OKC_ARTICLE_VERSIONS.PROGRAM_LOGIN_ID%TYPE;
3331 l_program_application_id OKC_ARTICLE_VERSIONS.PROGRAM_APPLICATION_ID%TYPE;
3332 l_request_id OKC_ARTICLE_VERSIONS.REQUEST_ID%TYPE;
3333 l_attribute_category OKC_ARTICLE_VERSIONS.ATTRIBUTE_CATEGORY%TYPE;
3334 l_attribute1 OKC_ARTICLE_VERSIONS.ATTRIBUTE1%TYPE;
3335 l_attribute2 OKC_ARTICLE_VERSIONS.ATTRIBUTE2%TYPE;
3336 l_attribute3 OKC_ARTICLE_VERSIONS.ATTRIBUTE3%TYPE;
3337 l_attribute4 OKC_ARTICLE_VERSIONS.ATTRIBUTE4%TYPE;
3338 l_attribute5 OKC_ARTICLE_VERSIONS.ATTRIBUTE5%TYPE;
3339 l_attribute6 OKC_ARTICLE_VERSIONS.ATTRIBUTE6%TYPE;
3340 l_attribute7 OKC_ARTICLE_VERSIONS.ATTRIBUTE7%TYPE;
3341 l_attribute8 OKC_ARTICLE_VERSIONS.ATTRIBUTE8%TYPE;
3342 l_attribute9 OKC_ARTICLE_VERSIONS.ATTRIBUTE9%TYPE;
3343 l_attribute10 OKC_ARTICLE_VERSIONS.ATTRIBUTE10%TYPE;
3344 l_attribute11 OKC_ARTICLE_VERSIONS.ATTRIBUTE11%TYPE;
3345 l_attribute12 OKC_ARTICLE_VERSIONS.ATTRIBUTE12%TYPE;
3346 l_attribute13 OKC_ARTICLE_VERSIONS.ATTRIBUTE13%TYPE;
3347 l_attribute14 OKC_ARTICLE_VERSIONS.ATTRIBUTE14%TYPE;
3348 l_attribute15 OKC_ARTICLE_VERSIONS.ATTRIBUTE15%TYPE;
3349 l_object_version_number OKC_ARTICLE_VERSIONS.OBJECT_VERSION_NUMBER%TYPE;
3350 l_edited_in_word OKC_ARTICLE_VERSIONS.EDITED_IN_WORD%TYPE;
3351 l_article_text_in_word OKC_ARTICLE_VERSIONS.ARTICLE_TEXT_IN_WORD%TYPE;
3352 l_created_by OKC_ARTICLE_VERSIONS.CREATED_BY%TYPE;
3353 l_creation_date OKC_ARTICLE_VERSIONS.CREATION_DATE%TYPE;
3354 l_last_updated_by OKC_ARTICLE_VERSIONS.LAST_UPDATED_BY%TYPE;
3355 l_last_update_login OKC_ARTICLE_VERSIONS.LAST_UPDATE_LOGIN%TYPE;
3356 l_last_update_date OKC_ARTICLE_VERSIONS.LAST_UPDATE_DATE%TYPE;
3357 l_earlier_version_number OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_NUMBER%TYPE;
3358 l_earlier_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;
3359 l_variable_code OKC_ARTICLE_VERSIONS.VARIABLE_CODE%TYPE; ---clm
3360
3361 BEGIN
3362
3363 IF (l_debug = 'Y') THEN
3364 Okc_Debug.Log('7000: Entered Update_Row', 2);
3365 Okc_Debug.Log('7100: Locking _B row', 2);
3366 END IF;
3367
3368 x_return_status := Lock_row(
3369 p_article_version_id => p_article_version_id,
3370 p_object_version_number => p_object_version_number
3371 );
3372 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3374 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3375 RAISE FND_API.G_EXC_ERROR;
3376 END IF;
3377
3378
3379 IF (l_debug = 'Y') THEN
3380 Okc_Debug.Log('7300: Setting attributes', 2);
3381 END IF;
3382
3383 x_return_status := Set_Attributes(
3384 p_article_version_id => p_article_version_id,
3385 p_article_id => p_article_id,
3386 p_article_version_number => p_article_version_number,
3387 p_article_text => p_article_text,
3388 p_provision_yn => p_provision_yn,
3389 p_insert_by_reference => p_insert_by_reference,
3390 p_lock_text => p_lock_text,
3391 p_global_yn => p_global_yn,
3392 p_article_language => p_article_language,
3393 p_article_status => p_article_status,
3394 p_sav_release => p_sav_release,
3395 p_start_date => p_start_date,
3396 p_end_date => p_end_date,
3397 p_std_article_version_id => p_std_article_version_id,
3398 p_display_name => p_display_name,
3399 p_translated_yn => p_translated_yn,
3400 p_article_description => p_article_description,
3401 p_date_approved => p_date_approved,
3402 p_default_section => p_default_section,
3403 p_reference_source => p_reference_source,
3404 p_reference_text => p_reference_text,
3405 p_orig_system_reference_code => p_orig_system_reference_code,
3406 p_orig_system_reference_id1 => p_orig_system_reference_id1,
3407 p_orig_system_reference_id2 => p_orig_system_reference_id2,
3408 p_additional_instructions => p_additional_instructions,
3409 p_variation_description => p_variation_description,
3410 p_date_published => p_date_published,
3411 p_program_id => p_program_id,
3412 p_program_login_id => p_program_login_id,
3413 p_program_application_id => p_program_application_id,
3414 p_request_id => p_request_id,
3415 p_attribute_category => p_attribute_category,
3416 p_attribute1 => p_attribute1,
3417 p_attribute2 => p_attribute2,
3418 p_attribute3 => p_attribute3,
3419 p_attribute4 => p_attribute4,
3420 p_attribute5 => p_attribute5,
3421 p_attribute6 => p_attribute6,
3422 p_attribute7 => p_attribute7,
3423 p_attribute8 => p_attribute8,
3424 p_attribute9 => p_attribute9,
3425 p_attribute10 => p_attribute10,
3426 p_attribute11 => p_attribute11,
3427 p_attribute12 => p_attribute12,
3428 p_attribute13 => p_attribute13,
3429 p_attribute14 => p_attribute14,
3430 p_attribute15 => p_attribute15,
3431 p_object_version_number => p_object_version_number,
3432 p_edited_in_word => p_edited_in_word,
3433 p_article_text_in_word => p_article_text_in_word,
3434 p_variable_code => p_variable_code, --clm
3435 x_article_id => l_article_id,
3436 x_article_version_number => l_article_version_number,
3437 x_article_text => l_article_text,
3438 x_provision_yn => l_provision_yn,
3439 x_insert_by_reference => l_insert_by_reference,
3440 x_lock_text => l_lock_text,
3441 x_global_yn => l_global_yn,
3442 x_article_language => l_article_language,
3443 x_article_status => l_article_status,
3444 x_sav_release => l_sav_release,
3445 x_start_date => l_start_date,
3446 x_end_date => l_end_date,
3447 x_std_article_version_id => l_std_article_version_id,
3448 x_display_name => l_display_name,
3449 x_translated_yn => l_translated_yn,
3450 x_article_description => l_article_description,
3451 x_date_approved => l_date_approved,
3452 x_default_section => l_default_section,
3453 x_reference_source => l_reference_source,
3454 x_reference_text => l_reference_text,
3455 x_orig_system_reference_code => l_orig_system_reference_code,
3456 x_orig_system_reference_id1 => l_orig_system_reference_id1,
3457 x_orig_system_reference_id2 => l_orig_system_reference_id2,
3458 x_additional_instructions => l_additional_instructions,
3459 x_variation_description => l_variation_description,
3460 x_date_published => l_date_published,
3461 x_program_id => l_program_id,
3462 x_program_login_id => l_program_login_id,
3463 x_program_application_id => l_program_application_id,
3464 x_request_id => l_request_id,
3465 x_attribute_category => l_attribute_category,
3466 x_attribute1 => l_attribute1,
3467 x_attribute2 => l_attribute2,
3468 x_attribute3 => l_attribute3,
3469 x_attribute4 => l_attribute4,
3470 x_attribute5 => l_attribute5,
3471 x_attribute6 => l_attribute6,
3472 x_attribute7 => l_attribute7,
3473 x_attribute8 => l_attribute8,
3474 x_attribute9 => l_attribute9,
3475 x_attribute10 => l_attribute10,
3476 x_attribute11 => l_attribute11,
3477 x_attribute12 => l_attribute12,
3478 x_attribute13 => l_attribute13,
3479 x_attribute14 => l_attribute14,
3480 x_attribute15 => l_attribute15,
3481 x_edited_in_word => l_edited_in_word,
3482 x_article_text_in_word => l_article_text_in_word,
3483 x_variable_code => l_variable_code --clm
3484 );
3485 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3487 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3488 RAISE FND_API.G_EXC_ERROR;
3489 END IF;
3490
3491 IF (l_debug = 'Y') THEN
3492 Okc_Debug.Log('7400: Record Validation', 2);
3493 END IF;
3494
3495 -- MOAC
3496 G_CURRENT_ORG_ID := mo_global.get_current_org_id();
3497 if p_current_org_id IS NOT NULL Then
3498 G_CURRENT_ORG_ID := p_current_org_id;
3499 else
3500 if G_CURRENT_ORG_ID IS NULL Then
3501 IF (l_debug = 'Y') THEN
3502 Okc_Debug.Log('2300: - attribute G_CURRENT_ORG_ID is invalid', 2);
3503 END IF;
3504 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
3505 x_return_status := G_RET_STS_ERROR;
3506 RAISE FND_API.G_EXC_ERROR;
3507 end if;
3508
3509 end if;
3510 /*
3511 if p_current_org_id IS NULL Then
3512 OPEN cur_org_csr;
3513 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
3514 CLOSE cur_org_csr;
3515 else
3516 G_CURRENT_ORG_ID := p_current_org_id;
3517 end if;
3518 */
3519 --- Validate all non-missing attributes
3520 x_return_status := Validate_Record(
3521 p_validation_level => p_validation_level,
3522 p_article_version_id => p_article_version_id,
3523 p_article_id => l_article_id,
3524 p_article_version_number => l_article_version_number,
3525 p_article_text => l_article_text,
3526 p_provision_yn => l_provision_yn,
3527 p_insert_by_reference => l_insert_by_reference,
3528 p_lock_text => l_lock_text,
3529 p_global_yn => l_global_yn,
3530 p_article_language => l_article_language,
3531 p_article_status => l_article_status,
3532 p_sav_release => l_sav_release,
3533 p_start_date => l_start_date,
3534 p_end_date => l_end_date,
3535 p_std_article_version_id => l_std_article_version_id,
3536 p_display_name => l_display_name,
3537 p_translated_yn => l_translated_yn,
3538 p_article_description => l_article_description,
3539 p_date_approved => l_date_approved,
3540 p_default_section => l_default_section,
3541 p_reference_source => l_reference_source,
3542 p_reference_text => l_reference_text,
3543 p_orig_system_reference_code => l_orig_system_reference_code,
3544 p_orig_system_reference_id1 => l_orig_system_reference_id1,
3545 p_orig_system_reference_id2 => l_orig_system_reference_id2,
3546 p_additional_instructions => l_additional_instructions,
3547 p_variation_description => l_variation_description,
3548 p_date_published => l_date_published,
3549 p_program_id => l_program_id,
3550 p_program_login_id => l_program_login_id,
3551 p_program_application_id => l_program_application_id,
3552 p_request_id => l_request_id,
3553 p_current_org_id => G_CURRENT_ORG_ID,
3554 p_attribute_category => l_attribute_category,
3555 p_attribute1 => l_attribute1,
3556 p_attribute2 => l_attribute2,
3557 p_attribute3 => l_attribute3,
3558 p_attribute4 => l_attribute4,
3559 p_attribute5 => l_attribute5,
3560 p_attribute6 => l_attribute6,
3561 p_attribute7 => l_attribute7,
3562 p_attribute8 => l_attribute8,
3563 p_attribute9 => l_attribute9,
3564 p_attribute10 => l_attribute10,
3565 p_attribute11 => l_attribute11,
3566 p_attribute12 => l_attribute12,
3567 p_attribute13 => l_attribute13,
3568 p_attribute14 => l_attribute14,
3569 p_attribute15 => l_attribute15,
3570 p_edited_in_word => l_edited_in_word,
3571 p_article_text_in_word => l_article_text_in_word,
3572 p_variable_code => l_variable_code, --clm
3573 x_article_language => l_article_language_out,
3574 x_earlier_adoption_type => l_earlier_adoption_type,
3575 x_earlier_version_id => x_earlier_version_id,
3576 x_earlier_version_number => l_earlier_version_number
3577 );
3578 --- If any errors happen abort API
3579 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3581 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3582 RAISE FND_API.G_EXC_ERROR;
3583 END IF;
3584
3585 IF (l_debug = 'Y') THEN
3586 Okc_Debug.Log('7500: Filling WHO columns', 2);
3587 END IF;
3588 x_article_id := l_article_id;
3589
3590 -- Filling who columns
3591 l_last_update_date := SYSDATE;
3592 l_last_updated_by := FND_GLOBAL.USER_ID;
3593 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3594
3595 -- Object version increment
3596 IF Nvl(l_object_version_number, 0) >= 0 THEN
3597 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
3598 END IF;
3599 --------------------------------------------
3600 -- Call the Update_Row for each child record
3601 --------------------------------------------
3602 IF (l_debug = 'Y') THEN
3603 Okc_Debug.Log('7600: Updating Row', 2);
3604 END IF;
3605
3606 x_return_status := Update_Row(
3607 p_article_version_id => p_article_version_id,
3608 p_article_id => l_article_id,
3609 p_article_version_number => l_article_version_number,
3610 p_article_text => l_article_text,
3611 p_provision_yn => l_provision_yn,
3612 p_insert_by_reference => l_insert_by_reference,
3613 p_lock_text => l_lock_text,
3614 p_global_yn => l_global_yn,
3615 p_article_language => l_article_language,
3616 p_article_status => l_article_status,
3617 p_sav_release => l_sav_release,
3618 p_start_date => l_start_date,
3619 p_end_date => l_end_date,
3620 p_std_article_version_id => l_std_article_version_id,
3621 p_display_name => l_display_name,
3622 p_translated_yn => l_translated_yn,
3623 p_article_description => l_article_description,
3624 p_date_approved => l_date_approved,
3625 p_default_section => l_default_section,
3626 p_reference_source => l_reference_source,
3627 p_reference_text => l_reference_text,
3628 p_orig_system_reference_code => l_orig_system_reference_code,
3629 p_orig_system_reference_id1 => l_orig_system_reference_id1,
3630 p_orig_system_reference_id2 => l_orig_system_reference_id2,
3631 p_additional_instructions => l_additional_instructions,
3632 p_variation_description => l_variation_description,
3633 p_date_published => l_date_published,
3634 p_program_id => l_program_id,
3635 p_program_login_id => l_program_login_id,
3636 p_program_application_id => l_program_application_id,
3637 p_request_id => l_request_id,
3638 p_attribute_category => l_attribute_category,
3639 p_attribute1 => l_attribute1,
3640 p_attribute2 => l_attribute2,
3641 p_attribute3 => l_attribute3,
3642 p_attribute4 => l_attribute4,
3643 p_attribute5 => l_attribute5,
3644 p_attribute6 => l_attribute6,
3645 p_attribute7 => l_attribute7,
3646 p_attribute8 => l_attribute8,
3647 p_attribute9 => l_attribute9,
3648 p_attribute10 => l_attribute10,
3649 p_attribute11 => l_attribute11,
3650 p_attribute12 => l_attribute12,
3651 p_attribute13 => l_attribute13,
3652 p_attribute14 => l_attribute14,
3653 p_attribute15 => l_attribute15,
3654 p_object_version_number => l_object_version_number,
3655 p_edited_in_word => l_edited_in_word,
3656 p_article_text_in_word => l_article_text_in_word,
3657 p_created_by => l_created_by,
3658 p_creation_date => l_creation_date,
3659 p_last_updated_by => l_last_updated_by,
3660 p_last_update_login => l_last_update_login,
3661 p_last_update_date => l_last_update_date,
3662 p_variable_code => l_variable_code --clm
3663 );
3664 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3666 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3667 RAISE FND_API.G_EXC_ERROR;
3668 END IF;
3669
3670
3671 IF (l_debug = 'Y') THEN
3672 Okc_Debug.Log('7800: Leaving Update_Row', 2);
3673 END IF;
3674
3675 EXCEPTION
3676 WHEN FND_API.G_EXC_ERROR THEN
3677 IF (l_debug = 'Y') THEN
3678 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
3679 END IF;
3680 x_return_status := G_RET_STS_ERROR;
3681
3682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3683 IF (l_debug = 'Y') THEN
3684 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
3685 END IF;
3686 x_return_status := G_RET_STS_UNEXP_ERROR;
3687
3688 WHEN OTHERS THEN
3689 IF (l_debug = 'Y') THEN
3690 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
3691 END IF;
3692 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3693 p_msg_name => G_UNEXPECTED_ERROR,
3694 p_token1 => G_SQLCODE_TOKEN,
3695 p_token1_value => sqlcode,
3696 p_token2 => G_SQLERRM_TOKEN,
3697 p_token2_value => sqlerrm);
3698 x_return_status := G_RET_STS_UNEXP_ERROR;
3699
3700 END Update_Row;
3701
3702 ---------------------------------------------------------------------------
3703 -- PROCEDURE Delete_Row
3704 ---------------------------------------------------------------------------
3705 -------------------------------------
3706 -- Delete_Row for:OKC_ARTICLE_VERSIONS --
3707 -------------------------------------
3708 FUNCTION Delete_Row(
3709 p_article_version_id IN NUMBER
3710 ) RETURN VARCHAR2 IS
3711
3712 BEGIN
3713
3714 IF (l_debug = 'Y') THEN
3715 Okc_Debug.Log('8200: Entered Delete_Row', 2);
3716 END IF;
3717
3718 DELETE FROM OKC_ARTICLE_VERSIONS WHERE ARTICLE_VERSION_ID = P_ARTICLE_VERSION_ID;
3719
3720 IF (l_debug = 'Y') THEN
3721 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
3722 END IF;
3723
3724 RETURN( G_RET_STS_SUCCESS );
3725
3726 EXCEPTION
3727 WHEN OTHERS THEN
3728
3729 IF (l_debug = 'Y') THEN
3730 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
3731 END IF;
3732
3733 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3734 p_msg_name => G_UNEXPECTED_ERROR,
3735 p_token1 => G_SQLCODE_TOKEN,
3736 p_token1_value => sqlcode,
3737 p_token2 => G_SQLERRM_TOKEN,
3738 p_token2_value => sqlerrm);
3739
3740 RETURN( G_RET_STS_UNEXP_ERROR );
3741
3742 END Delete_Row;
3743
3744 -------------------------------------
3745 -- Delete_Row for:OKC_ARTICLE_VERSIONS --
3746 -------------------------------------
3747 PROCEDURE Delete_Row(
3748 x_return_status OUT NOCOPY VARCHAR2,
3749 p_article_version_id IN NUMBER,
3750 p_object_version_number IN NUMBER
3751 ) IS
3752 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
3753 BEGIN
3754
3755 IF (l_debug = 'Y') THEN
3756 Okc_Debug.Log('8800: Entered Delete_Row', 2);
3757 Okc_Debug.Log('8900: Locking _B row', 2);
3758 END IF;
3759
3760 x_return_status := Lock_row(
3761 p_article_version_id => p_article_version_id,
3762 p_object_version_number => p_object_version_number
3763 );
3764 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3766 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3767 RAISE FND_API.G_EXC_ERROR;
3768 END IF;
3769
3770
3771 IF (l_debug = 'Y') THEN
3772 Okc_Debug.Log('9100: Removing _B row', 2);
3773 END IF;
3774 x_return_status := Delete_Row( p_article_version_id => p_article_version_id );
3775 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3777 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3778 RAISE FND_API.G_EXC_ERROR;
3779 END IF;
3780
3781
3782 IF (l_debug = 'Y') THEN
3783 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
3784 END IF;
3785
3786 EXCEPTION
3787 WHEN FND_API.G_EXC_ERROR THEN
3788 IF (l_debug = 'Y') THEN
3789 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
3790 END IF;
3791 x_return_status := G_RET_STS_ERROR;
3792
3793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3794 IF (l_debug = 'Y') THEN
3795 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
3796 END IF;
3797 x_return_status := G_RET_STS_UNEXP_ERROR;
3798
3799 WHEN OTHERS THEN
3800 IF (l_debug = 'Y') THEN
3801 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
3802 END IF;
3803 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3804 p_msg_name => G_UNEXPECTED_ERROR,
3805 p_token1 => G_SQLCODE_TOKEN,
3806 p_token1_value => sqlcode,
3807 p_token2 => G_SQLERRM_TOKEN,
3808 p_token2_value => sqlerrm);
3809 x_return_status := G_RET_STS_UNEXP_ERROR;
3810
3811 END Delete_Row;
3812
3813 -------------------------------------
3814 -- ArticleClob for creating a temporary clob
3815 -- in the database
3816 -- It is required to be used in Create Article, Create New Version
3817 -- and Update Article in Java Classes
3818 -------------------------------------
3819 FUNCTION ArticleClob
3820 RETURN CLOB IS
3821
3822 c1 CLOB;
3823
3824 BEGIN
3825
3826 DBMS_LOB.CREATETEMPORARY(c1,true);
3827 DBMS_LOB.OPEN(c1,dbms_lob.lob_readwrite);
3828 DBMS_LOB.WRITE(c1,1,1,' ');
3829 RETURN c1;
3830 END ArticleClob;
3831
3832 -------------------------------------
3833 -- ArticleBlob for creating a temporary blob
3834 -- in the database
3835 -- It is required to be used in Create Article, Create New Version
3836 -- and Update Article in Java Classes
3837 -------------------------------------
3838 FUNCTION ArticleBlob
3839 RETURN BLOB IS
3840
3841 b1 BLOB;
3842
3843 BEGIN
3844
3845 DBMS_LOB.CREATETEMPORARY(b1,true);
3846 DBMS_LOB.OPEN(b1,dbms_lob.lob_readwrite);
3847 RETURN b1;
3848 END ArticleBlob;
3849
3850
3851 END OKC_ARTICLE_VERSIONS_PVT;