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