[Home] [Help]
PACKAGE BODY: APPS.OKC_K_ARTICLES_PVT
Source
1 PACKAGE BODY OKC_K_ARTICLES_PVT AS
2 /* $Header: OKCVCATB.pls 120.1 2006/02/23 12:46:17 ssivarap noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 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 G_LOCK_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_LOCK_RECORD_DELETED;
20 ---------------------------------------------------------------------------
21 -- VALIDATION LEVELS
22 ---------------------------------------------------------------------------
23 G_REQUIRED_VALUE_VALID_LVL CONSTANT NUMBER := 0; --OKC_API.G_REQUIRED_VALUE_VALID_LVL;
24 G_VALID_VALUE_VALID_LVL CONSTANT NUMBER := 0; --OKC_API.G_VALID_VALUE_VALID_LVL;
25 G_LOOKUP_CODE_VALID_LVL CONSTANT NUMBER := 0; --OKC_API.G_LOOKUP_CODE_VALID_LVL;
26 G_FOREIGN_KEY_VALID_LVL CONSTANT NUMBER := 0; --OKC_API.G_FOREIGN_KEY_VALID_LVL;
27 G_RECORD_VALID_LVL CONSTANT NUMBER := 0; --OKC_API.G_RECORD_VALID_LVL;
28 ---------------------------------------------------------------------------
29 -- GLOBAL VARIABLES
30 ---------------------------------------------------------------------------
31 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_K_ARTICLES_PVT';
32 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
33
34 ------------------------------------------------------------------------------
35 -- GLOBAL CONSTANTS
36 ------------------------------------------------------------------------------
37 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
38 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
39 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
40 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
41 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
42
43 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
44 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
45 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
46
47 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
48 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
49 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
50 G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
51 G_AMEND_CODE_ADDED CONSTANT VARCHAR2(30) := 'ADDED';
52 G_AMEND_CODE_UPDATED CONSTANT VARCHAR2(30) := 'UPDATED';
53 E_Resource_Busy EXCEPTION;
54 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
55
56 ---------------------------------------------------------------------------
57 -- FUNCTION get_seq_id
58 ---------------------------------------------------------------------------
59 FUNCTION Get_Seq_Id (
60 p_id IN NUMBER,
61 x_id OUT NOCOPY NUMBER
62 ) RETURN VARCHAR2 IS
63 CURSOR c_seq IS
64 SELECT OKC_K_ARTICLES_B_S.NEXTVAL FROM DUAL;
65 BEGIN
66 IF (l_debug = 'Y') THEN
67 Okc_Debug.Log('100: Entered get_seq_id', 2);
68 END IF;
69
70 IF( p_id IS NULL ) THEN
71 OPEN c_seq;
72 FETCH c_seq INTO x_id ;
73 IF c_seq%NOTFOUND THEN
74 RAISE NO_DATA_FOUND;
75 END IF;
76 CLOSE c_seq;
77 END IF;
78
79 IF (l_debug = 'Y') THEN
80 Okc_Debug.Log('200: Leaving get_seq_id', 2);
81 END IF;
82 RETURN G_RET_STS_SUCCESS;
83 EXCEPTION
84 WHEN OTHERS THEN
85
86 IF (l_debug = 'Y') THEN
87 Okc_Debug.Log('300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm, 2);
88 END IF;
89
90 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
91 p_msg_name => G_UNEXPECTED_ERROR,
92 p_token1 => G_SQLCODE_TOKEN,
93 p_token1_value => sqlcode,
94 p_token2 => G_SQLERRM_TOKEN,
95 p_token2_value => sqlerrm);
96
97 IF c_seq%ISOPEN THEN
98 CLOSE c_seq;
99 END IF;
100
101 RETURN G_RET_STS_UNEXP_ERROR ;
102
103 END Get_Seq_Id;
104
105 ---------------------------------------------------------------------------
106 -- FUNCTION get_rec for: OKC_K_ARTICLES_B
107 ---------------------------------------------------------------------------
108 FUNCTION Get_Rec (
109 p_id IN NUMBER,
110 p_major_version IN NUMBER := NULL,
111 x_sav_sae_id OUT NOCOPY NUMBER,
112 x_document_type OUT NOCOPY VARCHAR2,
113 x_document_id OUT NOCOPY NUMBER,
114 x_cle_id OUT NOCOPY NUMBER,
115 x_source_flag OUT NOCOPY VARCHAR2,
116 x_mandatory_yn OUT NOCOPY VARCHAR2,
117 x_scn_id OUT NOCOPY NUMBER,
118 x_label OUT NOCOPY VARCHAR2,
119 x_amendment_description OUT NOCOPY VARCHAR2,
120 x_amendment_operation_code OUT NOCOPY VARCHAR2,
121 x_article_version_id OUT NOCOPY NUMBER,
122 x_change_nonstd_yn OUT NOCOPY VARCHAR2,
123 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
124 x_orig_system_reference_id1 OUT NOCOPY NUMBER,
125 x_orig_system_reference_id2 OUT NOCOPY NUMBER,
126 x_display_sequence OUT NOCOPY NUMBER,
127 x_attribute_category OUT NOCOPY VARCHAR2,
128 x_attribute1 OUT NOCOPY VARCHAR2,
129 x_attribute2 OUT NOCOPY VARCHAR2,
130 x_attribute3 OUT NOCOPY VARCHAR2,
131 x_attribute4 OUT NOCOPY VARCHAR2,
132 x_attribute5 OUT NOCOPY VARCHAR2,
133 x_attribute6 OUT NOCOPY VARCHAR2,
134 x_attribute7 OUT NOCOPY VARCHAR2,
135 x_attribute8 OUT NOCOPY VARCHAR2,
136 x_attribute9 OUT NOCOPY VARCHAR2,
137 x_attribute10 OUT NOCOPY VARCHAR2,
138 x_attribute11 OUT NOCOPY VARCHAR2,
139 x_attribute12 OUT NOCOPY VARCHAR2,
140 x_attribute13 OUT NOCOPY VARCHAR2,
141 x_attribute14 OUT NOCOPY VARCHAR2,
142 x_attribute15 OUT NOCOPY VARCHAR2,
143 x_print_text_yn OUT NOCOPY VARCHAR2,
144 x_summary_amend_operation_code OUT NOCOPY VARCHAR2,
145 x_ref_article_id OUT NOCOPY NUMBER,
146 x_ref_article_version_id OUT NOCOPY NUMBER,
147 x_object_version_number OUT NOCOPY NUMBER,
148 x_created_by OUT NOCOPY NUMBER,
149 x_creation_date OUT NOCOPY DATE,
150 x_last_updated_by OUT NOCOPY NUMBER,
151 x_last_update_login OUT NOCOPY NUMBER,
152 x_last_update_date OUT NOCOPY DATE,
153 x_last_amended_by OUT NOCOPY NUMBER,
154 x_last_amendment_date OUT NOCOPY DATE
155
156 ) RETURN VARCHAR2 IS
157 CURSOR OKC_K_ARTICLES_B_pk_csr (cp_id IN NUMBER) IS
158 SELECT
159 SAV_SAE_ID,
160 DOCUMENT_TYPE,
161 DOCUMENT_ID,
162 CLE_ID,
163 SOURCE_FLAG,
164 MANDATORY_YN,
165 SCN_ID,
166 LABEL,
167 AMENDMENT_DESCRIPTION,
168 AMENDMENT_OPERATION_CODE,
169 ARTICLE_VERSION_ID,
170 CHANGE_NONSTD_YN,
171 ORIG_SYSTEM_REFERENCE_CODE,
172 ORIG_SYSTEM_REFERENCE_ID1,
173 ORIG_SYSTEM_REFERENCE_ID2,
174 DISPLAY_SEQUENCE,
175 ATTRIBUTE_CATEGORY,
176 ATTRIBUTE1,
177 ATTRIBUTE2,
178 ATTRIBUTE3,
179 ATTRIBUTE4,
180 ATTRIBUTE5,
181 ATTRIBUTE6,
182 ATTRIBUTE7,
183 ATTRIBUTE8,
184 ATTRIBUTE9,
185 ATTRIBUTE10,
186 ATTRIBUTE11,
187 ATTRIBUTE12,
188 ATTRIBUTE13,
189 ATTRIBUTE14,
190 ATTRIBUTE15,
191 PRINT_TEXT_YN,
192 SUMMARY_AMEND_OPERATION_CODE,
193 REF_ARTICLE_ID,
194 REF_ARTICLE_VERSION_ID,
195 OBJECT_VERSION_NUMBER,
196 CREATED_BY,
197 CREATION_DATE,
198 LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN,
200 LAST_UPDATE_DATE,
201 LAST_AMENDED_BY,
202 LAST_AMENDMENT_DATE
203 FROM OKC_K_ARTICLES_B t
204 WHERE t.ID = cp_id;
205 CURSOR OKC_K_ARTICLES_BH_pk_csr (cp_id IN NUMBER, cp_major_version VARCHAR2) IS
206 SELECT
207 SAV_SAE_ID,
208 DOCUMENT_TYPE,
209 DOCUMENT_ID,
210 CLE_ID,
211 SOURCE_FLAG,
212 MANDATORY_YN,
213 SCN_ID,
214 LABEL,
215 AMENDMENT_DESCRIPTION,
216 AMENDMENT_OPERATION_CODE,
217 ARTICLE_VERSION_ID,
218 CHANGE_NONSTD_YN,
219 ORIG_SYSTEM_REFERENCE_CODE,
220 ORIG_SYSTEM_REFERENCE_ID1,
221 ORIG_SYSTEM_REFERENCE_ID2,
222 DISPLAY_SEQUENCE,
223 ATTRIBUTE_CATEGORY,
224 ATTRIBUTE1,
225 ATTRIBUTE2,
226 ATTRIBUTE3,
227 ATTRIBUTE4,
228 ATTRIBUTE5,
229 ATTRIBUTE6,
230 ATTRIBUTE7,
231 ATTRIBUTE8,
232 ATTRIBUTE9,
233 ATTRIBUTE10,
234 ATTRIBUTE11,
235 ATTRIBUTE12,
236 ATTRIBUTE13,
237 ATTRIBUTE14,
238 ATTRIBUTE15,
239 PRINT_TEXT_YN,
240 SUMMARY_AMEND_OPERATION_CODE,
241 REF_ARTICLE_ID,
242 REF_ARTICLE_VERSION_ID,
243 OBJECT_VERSION_NUMBER,
244 CREATED_BY,
245 CREATION_DATE,
246 LAST_UPDATED_BY,
247 LAST_UPDATE_LOGIN,
248 LAST_UPDATE_DATE,
249 LAST_AMENDED_BY,
250 LAST_AMENDMENT_DATE
251 FROM OKC_K_ARTICLES_BH t
252 WHERE t.ID = cp_id and major_version=cp_major_version;
253 BEGIN
254
255 IF (l_debug = 'Y') THEN
256 Okc_Debug.Log('400: Entered get_rec', 2);
257 END IF;
258
259 IF p_major_version IS NULL THEN
260 IF (l_debug = 'Y') THEN
261 Okc_Debug.Log('401: Get current database values', 2);
262 END IF;
263
264 -- Get current database values
265 OPEN OKC_K_ARTICLES_B_pk_csr (p_id);
266 FETCH OKC_K_ARTICLES_B_pk_csr INTO
267 x_sav_sae_id,
268 x_document_type,
269 x_document_id,
270 x_cle_id,
271 x_source_flag,
272 x_mandatory_yn,
273 x_scn_id,
274 x_label,
275 x_amendment_description,
276 x_amendment_operation_code,
277 x_article_version_id,
278 x_change_nonstd_yn,
279 x_orig_system_reference_code,
280 x_orig_system_reference_id1,
281 x_orig_system_reference_id2,
282 x_display_sequence,
283 x_attribute_category,
284 x_attribute1,
285 x_attribute2,
286 x_attribute3,
287 x_attribute4,
288 x_attribute5,
289 x_attribute6,
290 x_attribute7,
291 x_attribute8,
292 x_attribute9,
293 x_attribute10,
294 x_attribute11,
295 x_attribute12,
296 x_attribute13,
297 x_attribute14,
298 x_attribute15,
299 x_print_text_yn,
300 x_summary_amend_operation_code,
301 x_ref_article_id,
302 x_ref_article_version_id,
303 x_object_version_number,
304 x_created_by,
305 x_creation_date,
306 x_last_updated_by,
307 x_last_update_login,
308 x_last_update_date,
309 x_last_amended_by,
310 x_last_amendment_date;
311 IF OKC_K_ARTICLES_B_pk_csr%NOTFOUND THEN
312 Okc_Api.Set_Message(G_APP_NAME,G_LOCK_RECORD_DELETED,
313 'ENTITYNAME','OKC_K_ARTICLES_B',
314 'PKEY',p_id,
315 'OVN',p_major_version
316 );
317 RAISE NO_DATA_FOUND;
318 END IF;
319 CLOSE OKC_K_ARTICLES_B_pk_csr;
320 ELSE
321 IF (l_debug = 'Y') THEN
322 Okc_Debug.Log('402: Get history database values', 2);
323 END IF;
324
325 -- Get history database values
326 OPEN OKC_K_ARTICLES_BH_pk_csr (p_id, p_major_version);
327 FETCH OKC_K_ARTICLES_BH_pk_csr INTO
328 x_sav_sae_id,
329 x_document_type,
330 x_document_id,
331 x_cle_id,
332 x_source_flag,
333 x_mandatory_yn,
334 x_scn_id,
335 x_label,
336 x_amendment_description,
337 x_amendment_operation_code,
338 x_article_version_id,
339 x_change_nonstd_yn,
340 x_orig_system_reference_code,
341 x_orig_system_reference_id1,
342 x_orig_system_reference_id2,
343 x_display_sequence,
344 x_attribute_category,
345 x_attribute1,
346 x_attribute2,
347 x_attribute3,
348 x_attribute4,
349 x_attribute5,
350 x_attribute6,
351 x_attribute7,
352 x_attribute8,
353 x_attribute9,
354 x_attribute10,
355 x_attribute11,
356 x_attribute12,
357 x_attribute13,
358 x_attribute14,
359 x_attribute15,
360 x_print_text_yn,
361 x_summary_amend_operation_code,
362 x_ref_article_id,
363 x_ref_article_version_id,
364 x_object_version_number,
365 x_created_by,
366 x_creation_date,
367 x_last_updated_by,
368 x_last_update_login,
369 x_last_update_date,
370 x_last_amended_by,
371 x_last_amendment_date;
372 IF OKC_K_ARTICLES_BH_pk_csr%NOTFOUND THEN
373 Okc_Api.Set_Message(G_APP_NAME,G_LOCK_RECORD_DELETED,
374 'ENTITYNAME','OKC_K_ARTICLES_BH',
375 'PKEY',p_id,
376 'OVN',p_major_version
377 );
378 RAISE NO_DATA_FOUND;
379 END IF;
380 CLOSE OKC_K_ARTICLES_BH_pk_csr;
381 END IF;
382
383 IF (l_debug = 'Y') THEN
384 Okc_Debug.Log('500: Leaving get_rec ', 2);
385 END IF;
386
387 RETURN G_RET_STS_SUCCESS ;
388
389 EXCEPTION
390 WHEN NO_DATA_FOUND THEN
391 IF (l_debug = 'Y') THEN
392 Okc_Debug.Log('600: Leaving get_rec because of NO_DATA_FOUND EXCEPTION.', 2);
393 END IF;
394
395 -- moved upper where an exception is raised
396 -- Okc_Api.Set_Message(G_APP_NAME,G_LOCK_RECORD_DELETED,
397 -- 'ENTITYNAME','OKC_K_ARTICLES_BH',
398 -- 'PKEY',p_id,
399 -- 'OVN',p_major_version
400 -- );
401
402 IF OKC_K_ARTICLES_B_pk_csr%ISOPEN THEN
403 CLOSE OKC_K_ARTICLES_B_pk_csr;
404 END IF;
405
406 IF OKC_K_ARTICLES_BH_pk_csr%ISOPEN THEN
407 CLOSE OKC_K_ARTICLES_BH_pk_csr;
408 END IF;
409
410 RETURN G_RET_STS_ERROR ;
411
412 WHEN OTHERS THEN
413
414 IF (l_debug = 'Y') THEN
415 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
416 END IF;
417
418 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
419 p_msg_name => G_UNEXPECTED_ERROR,
420 p_token1 => G_SQLCODE_TOKEN,
421 p_token1_value => sqlcode,
422 p_token2 => G_SQLERRM_TOKEN,
423 p_token2_value => sqlerrm);
424
425 IF OKC_K_ARTICLES_B_pk_csr%ISOPEN THEN
426 CLOSE OKC_K_ARTICLES_B_pk_csr;
427 END IF;
428
429 RETURN G_RET_STS_UNEXP_ERROR ;
430
431 END Get_Rec;
432
433 -----------------------------------------
434 -- Set_Attributes for:OKC_K_ARTICLES_B --
435 -----------------------------------------
436 FUNCTION Set_Attributes(
437 p_id IN NUMBER,
438 p_sav_sae_id IN NUMBER,
439 p_document_type IN VARCHAR2,
440 p_document_id IN NUMBER,
441 p_cle_id IN NUMBER,
442 p_source_flag IN VARCHAR2,
443 p_mandatory_yn IN VARCHAR2,
444 p_scn_id IN NUMBER,
445 p_label IN VARCHAR2,
446 p_amendment_description IN VARCHAR2,
447 p_amendment_operation_code IN VARCHAR2,
448 p_article_version_id IN NUMBER,
449 p_change_nonstd_yn IN VARCHAR2,
450 p_orig_system_reference_code IN VARCHAR2,
451 p_orig_system_reference_id1 IN NUMBER,
452 p_orig_system_reference_id2 IN NUMBER,
453 p_display_sequence IN NUMBER,
454 p_attribute_category IN VARCHAR2,
455 p_attribute1 IN VARCHAR2,
456 p_attribute2 IN VARCHAR2,
457 p_attribute3 IN VARCHAR2,
458 p_attribute4 IN VARCHAR2,
459 p_attribute5 IN VARCHAR2,
460 p_attribute6 IN VARCHAR2,
461 p_attribute7 IN VARCHAR2,
462 p_attribute8 IN VARCHAR2,
463 p_attribute9 IN VARCHAR2,
464 p_attribute10 IN VARCHAR2,
465 p_attribute11 IN VARCHAR2,
466 p_attribute12 IN VARCHAR2,
467 p_attribute13 IN VARCHAR2,
468 p_attribute14 IN VARCHAR2,
469 p_attribute15 IN VARCHAR2,
470 p_print_text_yn IN VARCHAR2,
471 p_summary_amend_operation_code IN VARCHAR2,
472 p_ref_article_id IN NUMBER,
473 p_ref_article_version_id IN NUMBER,
474 p_object_version_number IN NUMBER,
475 p_last_amended_by IN NUMBER,
476 p_last_amendment_date IN DATE,
477
478 x_sav_sae_id OUT NOCOPY NUMBER,
479 x_document_type OUT NOCOPY VARCHAR2,
480 x_document_id OUT NOCOPY NUMBER,
481 x_cle_id OUT NOCOPY NUMBER,
482 x_source_flag OUT NOCOPY VARCHAR2,
483 x_mandatory_yn OUT NOCOPY VARCHAR2,
484 x_scn_id OUT NOCOPY NUMBER,
485 x_label OUT NOCOPY VARCHAR2,
486 x_amendment_description OUT NOCOPY VARCHAR2,
487 x_object_version_number OUT NOCOPY VARCHAR2,
488 x_amendment_operation_code OUT NOCOPY VARCHAR2,
489 x_article_version_id OUT NOCOPY NUMBER,
490 x_change_nonstd_yn OUT NOCOPY VARCHAR2,
491 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
492 x_orig_system_reference_id1 OUT NOCOPY NUMBER,
493 x_orig_system_reference_id2 OUT NOCOPY NUMBER,
494 x_display_sequence OUT NOCOPY NUMBER,
495 x_attribute_category OUT NOCOPY VARCHAR2,
496 x_attribute1 OUT NOCOPY VARCHAR2,
497 x_attribute2 OUT NOCOPY VARCHAR2,
498 x_attribute3 OUT NOCOPY VARCHAR2,
499 x_attribute4 OUT NOCOPY VARCHAR2,
500 x_attribute5 OUT NOCOPY VARCHAR2,
501 x_attribute6 OUT NOCOPY VARCHAR2,
502 x_attribute7 OUT NOCOPY VARCHAR2,
503 x_attribute8 OUT NOCOPY VARCHAR2,
504 x_attribute9 OUT NOCOPY VARCHAR2,
505 x_attribute10 OUT NOCOPY VARCHAR2,
506 x_attribute11 OUT NOCOPY VARCHAR2,
507 x_attribute12 OUT NOCOPY VARCHAR2,
508 x_attribute13 OUT NOCOPY VARCHAR2,
509 x_attribute14 OUT NOCOPY VARCHAR2,
510 x_attribute15 OUT NOCOPY VARCHAR2,
511 x_print_text_yn OUT NOCOPY VARCHAR2,
512 x_summary_amend_operation_code OUT NOCOPY VARCHAR2,
513 x_ref_article_id OUT NOCOPY NUMBER,
514 x_ref_article_version_id OUT NOCOPY NUMBER,
515 x_last_amended_by OUT NOCOPY NUMBER,
516 x_last_amendment_date OUT NOCOPY DATE
517 ) RETURN VARCHAR2 IS
518 l_return_status VARCHAR2(1);
519 l_object_version_number OKC_K_ARTICLES_B.OBJECT_VERSION_NUMBER%TYPE;
520 l_created_by OKC_K_ARTICLES_B.CREATED_BY%TYPE;
521 l_creation_date OKC_K_ARTICLES_B.CREATION_DATE%TYPE;
522 l_last_updated_by OKC_K_ARTICLES_B.LAST_UPDATED_BY%TYPE;
523 l_last_update_login OKC_K_ARTICLES_B.LAST_UPDATE_LOGIN%TYPE;
524 l_last_update_date OKC_K_ARTICLES_B.LAST_UPDATE_DATE%TYPE;
525 BEGIN
526 IF( p_id IS NOT NULL ) THEN
527 -- Get current database values
528 l_return_status := Get_Rec(
529 p_id => p_id,
530 x_sav_sae_id => x_sav_sae_id,
531 x_document_type => x_document_type,
532 x_document_id => x_document_id,
533 x_cle_id => x_cle_id,
534 x_source_flag => x_source_flag,
535 x_mandatory_yn => x_mandatory_yn,
536 x_scn_id => x_scn_id,
537 x_label => x_label,
538 x_amendment_description => x_amendment_description,
539 x_amendment_operation_code => x_amendment_operation_code,
540 x_article_version_id => x_article_version_id,
541 x_change_nonstd_yn => x_change_nonstd_yn,
542 x_orig_system_reference_code => x_orig_system_reference_code,
543 x_orig_system_reference_id1 => x_orig_system_reference_id1,
544 x_orig_system_reference_id2 => x_orig_system_reference_id2,
545 x_display_sequence => x_display_sequence,
546 x_attribute_category => x_attribute_category,
547 x_attribute1 => x_attribute1,
548 x_attribute2 => x_attribute2,
549 x_attribute3 => x_attribute3,
550 x_attribute4 => x_attribute4,
551 x_attribute5 => x_attribute5,
552 x_attribute6 => x_attribute6,
553 x_attribute7 => x_attribute7,
554 x_attribute8 => x_attribute8,
555 x_attribute9 => x_attribute9,
556 x_attribute10 => x_attribute10,
557 x_attribute11 => x_attribute11,
558 x_attribute12 => x_attribute12,
559 x_attribute13 => x_attribute13,
560 x_attribute14 => x_attribute14,
561 x_attribute15 => x_attribute15,
562 x_print_text_yn => x_print_text_yn,
563 x_summary_amend_operation_code => x_summary_amend_operation_code,
564 x_ref_article_id => x_ref_article_id,
565 x_ref_article_version_id => x_ref_article_version_id,
566 x_object_version_number => x_object_version_number,
567 x_created_by => l_created_by,
568 x_creation_date => l_creation_date,
569 x_last_updated_by => l_last_updated_by,
570 x_last_update_login => l_last_update_login,
571 x_last_update_date => l_last_update_date,
572 x_last_amended_by => x_last_amended_by,
573 x_last_amendment_date => x_last_amendment_date
574 );
575 --- If any errors happen abort API
576 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
577 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
578 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
579 RAISE OKC_API.G_EXCEPTION_ERROR;
580 END IF;
581
582 --- Reversing G_MISS/NULL values logic
583
584 IF (p_sav_sae_id = G_MISS_NUM) THEN
585 x_sav_sae_id := NULL;
586 ELSIF (p_SAV_SAE_ID IS NOT NULL) THEN
587 x_sav_sae_id := p_sav_sae_id;
588 END IF;
589
590 IF (p_document_type = G_MISS_CHAR) THEN
591 x_document_type := NULL;
592 ELSIF (p_DOCUMENT_TYPE IS NOT NULL) THEN
593 x_document_type := p_document_type;
594 END IF;
595
596 IF (p_document_id = G_MISS_NUM) THEN
597 x_document_id := NULL;
598 ELSIF (p_DOCUMENT_ID IS NOT NULL) THEN
599 x_document_id := p_document_id;
600 END IF;
601
602 IF (p_cle_id = G_MISS_NUM) THEN
603 x_cle_id := NULL;
604 ELSIF (p_cle_id IS NOT NULL) THEN
605 x_cle_id := p_cle_id;
606 END IF;
607
608 IF (p_source_flag = G_MISS_CHAR) THEN
609 x_source_flag := NULL;
610 ELSIF (p_SOURCE_FLAG IS NOT NULL) THEN
611 x_source_flag := p_source_flag;
612 END IF;
613
614 IF (p_mandatory_yn = G_MISS_CHAR) THEN
615 x_mandatory_yn := NULL;
616 ELSIF (p_MANDATORY_YN IS NOT NULL) THEN
617 x_mandatory_yn := p_mandatory_yn;
618 x_mandatory_yn := Upper( x_mandatory_yn );
619 END IF;
620
621 IF (p_print_text_yn = G_MISS_CHAR) THEN
622 x_print_text_yn := 'N';
623 ELSIF (p_print_text_yn IS NOT NULL) THEN
624 x_print_text_yn := p_print_text_yn;
625 x_print_text_yn := Upper( x_print_text_yn );
626 END IF;
627
628 IF (p_summary_amend_operation_code = G_MISS_CHAR) THEN
629 x_summary_amend_operation_code := NULL;
630 ELSIF (p_summary_amend_operation_code IS NOT NULL) THEN
631 x_summary_amend_operation_code := p_summary_amend_operation_code;
632 END IF;
633
634 IF (p_ref_article_id = G_MISS_NUM) THEN
635 x_ref_article_id := NULL;
636 ELSIF (p_ref_article_id IS NOT NULL) THEN
637 x_ref_article_id := p_ref_article_id;
638 END IF;
639
640 IF (p_ref_article_version_id = G_MISS_NUM) THEN
641 x_ref_article_version_id := NULL;
642 ELSIF (p_ref_article_version_id IS NOT NULL) THEN
643 x_ref_article_version_id := p_ref_article_version_id;
644 END IF;
645
646 IF (p_scn_id = G_MISS_NUM) THEN
647 x_scn_id := NULL;
648 ELSIF (p_SCN_ID IS NOT NULL) THEN
649 x_scn_id := p_scn_id;
650 END IF;
651
652 IF (p_label = G_MISS_CHAR) THEN
653 x_label := NULL;
654 ELSIF (p_LABEL IS NOT NULL) THEN
655 x_label := p_label;
656 END IF;
657
658 IF (p_amendment_description = G_MISS_CHAR) THEN
659 x_amendment_description := NULL;
660 ELSIF (p_AMENDMENT_DESCRIPTION IS NOT NULL) THEN
661 x_amendment_description := p_amendment_description;
662 END IF;
663
664 IF (p_amendment_operation_code = G_MISS_CHAR) THEN
665 x_amendment_operation_code := NULL;
666 ELSIF (p_AMENDMENT_OPERATION_CODE IS NOT NULL) THEN
667 x_amendment_operation_code := p_amendment_operation_code;
668 END IF;
669
670 IF p_amendment_operation_code IS NOT NULL
671 AND p_amendment_operation_code <> G_MISS_CHAR
672 AND x_amendment_operation_code IS NOT NULL
673 THEN
674 x_last_amendment_date := Sysdate;
675 x_last_amended_by := Fnd_Global.User_Id;
676 END IF;
677
678 IF (p_last_amended_by = G_MISS_NUM) THEN
679 x_last_amended_by := NULL;
680 END IF;
681
682 IF (p_last_amendment_date = G_MISS_DATE) THEN
683 x_last_amendment_date := NULL;
684 END IF;
685
686 IF (p_article_version_id = G_MISS_NUM) THEN
687 x_article_version_id := NULL;
688 ELSIF (p_ARTICLE_VERSION_ID IS NOT NULL) THEN
689 x_article_version_id := p_article_version_id;
690 END IF;
691
692 IF (p_change_nonstd_yn = G_MISS_CHAR) THEN
693 x_change_nonstd_yn := 'N';
694 ELSIF (p_CHANGE_NONSTD_YN IS NOT NULL) THEN
695 x_change_nonstd_yn := p_change_nonstd_yn;
696 x_change_nonstd_yn := Upper( x_change_nonstd_yn );
697 END IF;
698
699 IF (p_orig_system_reference_code = G_MISS_CHAR) THEN
700 x_orig_system_reference_code := NULL;
701 ELSIF (p_ORIG_SYSTEM_REFERENCE_CODE IS NOT NULL) THEN
702 x_orig_system_reference_code := p_orig_system_reference_code;
703 END IF;
704
705 IF (p_orig_system_reference_id1 = G_MISS_NUM) THEN
706 x_orig_system_reference_id1 := NULL;
707 ELSIF (p_ORIG_SYSTEM_REFERENCE_ID1 IS NOT NULL) THEN
708 x_orig_system_reference_id1 := p_orig_system_reference_id1;
709 END IF;
710
711 IF (p_orig_system_reference_id2 = G_MISS_NUM) THEN
712 x_orig_system_reference_id2 := NULL;
713 ELSIF (p_ORIG_SYSTEM_REFERENCE_ID2 IS NOT NULL) THEN
714 x_orig_system_reference_id2 := p_orig_system_reference_id2;
715 END IF;
716
717 IF (p_display_sequence = G_MISS_NUM) THEN
718 x_display_sequence := NULL;
719 ELSIF (p_DISPLAY_SEQUENCE IS NOT NULL) THEN
720 x_display_sequence := p_display_sequence;
721 END IF;
722
723 IF (p_attribute_category = G_MISS_CHAR) THEN
724 x_attribute_category := NULL;
725 ELSIF (p_ATTRIBUTE_CATEGORY IS NOT NULL) THEN
726 x_attribute_category := p_attribute_category;
727 END IF;
728
729 IF (p_attribute1 = G_MISS_CHAR) THEN
730 x_attribute1 := NULL;
731 ELSIF (p_ATTRIBUTE1 IS NOT NULL) THEN
732 x_attribute1 := p_attribute1;
733 END IF;
734
735 IF (p_attribute2 = G_MISS_CHAR) THEN
736 x_attribute2 := NULL;
737 ELSIF (p_ATTRIBUTE2 IS NOT NULL) THEN
738 x_attribute2 := p_attribute2;
739 END IF;
740
741 IF (p_attribute3 = G_MISS_CHAR) THEN
742 x_attribute3 := NULL;
743 ELSIF (p_ATTRIBUTE3 IS NOT NULL) THEN
744 x_attribute3 := p_attribute3;
745 END IF;
746
747 IF (p_attribute4 = G_MISS_CHAR) THEN
748 x_attribute4 := NULL;
749 ELSIF (p_ATTRIBUTE4 IS NOT NULL) THEN
750 x_attribute4 := p_attribute4;
751 END IF;
752
753 IF (p_attribute5 = G_MISS_CHAR) THEN
754 x_attribute5 := NULL;
755 ELSIF (p_ATTRIBUTE5 IS NOT NULL) THEN
756 x_attribute5 := p_attribute5;
757 END IF;
758
759 IF (p_attribute6 = G_MISS_CHAR) THEN
760 x_attribute6 := NULL;
761 ELSIF (p_ATTRIBUTE6 IS NOT NULL) THEN
762 x_attribute6 := p_attribute6;
763 END IF;
764
765 IF (p_attribute7 = G_MISS_CHAR) THEN
766 x_attribute7 := NULL;
767 ELSIF (p_ATTRIBUTE7 IS NOT NULL) THEN
768 x_attribute7 := p_attribute7;
769 END IF;
770
771 IF (p_attribute8 = G_MISS_CHAR) THEN
772 x_attribute8 := NULL;
773 ELSIF (p_ATTRIBUTE8 IS NOT NULL) THEN
774 x_attribute8 := p_attribute8;
775 END IF;
776
777 IF (p_attribute9 = G_MISS_CHAR) THEN
778 x_attribute9 := NULL;
779 ELSIF (p_ATTRIBUTE9 IS NOT NULL) THEN
780 x_attribute9 := p_attribute9;
781 END IF;
782
783 IF (p_attribute10 = G_MISS_CHAR) THEN
784 x_attribute10 := NULL;
785 ELSIF (p_ATTRIBUTE10 IS NOT NULL) THEN
786 x_attribute10 := p_attribute10;
787 END IF;
788
789 IF (p_attribute11 = G_MISS_CHAR) THEN
790 x_attribute11 := NULL;
791 ELSIF (p_ATTRIBUTE11 IS NOT NULL) THEN
792 x_attribute11 := p_attribute11;
793 END IF;
794
795 IF (p_attribute12 = G_MISS_CHAR) THEN
796 x_attribute12 := NULL;
797 ELSIF (p_ATTRIBUTE12 IS NOT NULL) THEN
798 x_attribute12 := p_attribute12;
799 END IF;
800
801 IF (p_attribute13 = G_MISS_CHAR) THEN
802 x_attribute13 := NULL;
803 ELSIF (p_ATTRIBUTE13 IS NOT NULL) THEN
804 x_attribute13 := p_attribute13;
805 END IF;
806
807 IF (p_attribute14 = G_MISS_CHAR) THEN
808 x_attribute14 := NULL;
809 ELSIF (p_ATTRIBUTE14 IS NOT NULL) THEN
810 x_attribute14 := p_attribute14;
811 END IF;
812
813 IF (p_attribute15 = G_MISS_CHAR) THEN
814 x_attribute15 := NULL;
815 ELSIF (p_ATTRIBUTE15 IS NOT NULL) THEN
816 x_attribute15 := p_attribute15;
817 END IF;
818 END IF;
819 RETURN G_RET_STS_SUCCESS ;
820 END;
821 -----------------------------------------
822 -- End of Set_Attributes for:OKC_K_ARTICLES_B --
823 -----------------------------------------
824
825 ----------------------------------------------
826 -- Validate_Attributes for: OKC_K_ARTICLES_B --
827 ----------------------------------------------
828 FUNCTION Validate_Attributes (
829 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
830
831 p_id IN NUMBER,
832 p_sav_sae_id IN NUMBER,
833 p_document_type IN VARCHAR2,
834 p_document_id IN NUMBER,
835 p_cle_id IN NUMBER,
836 p_source_flag IN VARCHAR2,
837 p_mandatory_yn IN VARCHAR2,
838 p_scn_id IN NUMBER,
839 p_label IN VARCHAR2,
840 p_amendment_description IN VARCHAR2,
841 p_amendment_operation_code IN VARCHAR2,
842 p_article_version_id IN NUMBER,
843 p_change_nonstd_yn IN VARCHAR2,
844 p_orig_system_reference_code IN VARCHAR2,
845 p_orig_system_reference_id1 IN NUMBER,
846 p_orig_system_reference_id2 IN NUMBER,
847 p_display_sequence IN NUMBER,
848 p_attribute_category IN VARCHAR2,
849 p_attribute1 IN VARCHAR2,
850 p_attribute2 IN VARCHAR2,
851 p_attribute3 IN VARCHAR2,
852 p_attribute4 IN VARCHAR2,
853 p_attribute5 IN VARCHAR2,
854 p_attribute6 IN VARCHAR2,
855 p_attribute7 IN VARCHAR2,
856 p_attribute8 IN VARCHAR2,
857 p_attribute9 IN VARCHAR2,
858 p_attribute10 IN VARCHAR2,
859 p_attribute11 IN VARCHAR2,
860 p_attribute12 IN VARCHAR2,
861 p_attribute13 IN VARCHAR2,
862 p_attribute14 IN VARCHAR2,
863 p_attribute15 IN VARCHAR2,
864 p_print_text_yn IN VARCHAR2,
865 p_summary_amend_operation_code IN VARCHAR2,
866 p_ref_article_id IN NUMBER,
867 p_ref_article_version_id IN NUMBER
868 ) RETURN VARCHAR2 IS
869
870 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
871 l_dummy_var VARCHAR2(1) := '?';
872
873 CURSOR lc_sav_sae_id is
874 SELECT '!'
875 FROM okc_articles_all
876 WHERE article_id = p_sav_sae_id;
877
878 CURSOR lc_scn_id is
879 SELECT '!'
880 FROM okc_sections_b
881 WHERE ID = p_scn_id;
882
883 CURSOR lc_article_version_id is
884 SELECT '!'
885 FROM okc_article_versions
886 WHERE ARTICLE_VERSION_ID = p_article_version_id;
887
888 CURSOR l_doc_type_csr is
889 SELECT '!'
890 FROM OKC_BUS_DOC_TYPES_V
891 WHERE document_type = p_document_type;
892
893 CURSOR l_validate_amend_mode_csr IS
894 SELECT '!' FROM OKC_SECTIONS_B
895 WHERE ID = p_scn_id
896 AND AMENDMENT_OPERATION_CODE = G_AMEND_CODE_DELETED;
897
898 BEGIN
899
900 IF (l_debug = 'Y') THEN
901 Okc_Debug.Log('700: Entered Validate_Attributes', 2);
902 END IF;
903
904 IF p_validation_level > G_REQUIRED_VALUE_VALID_LVL THEN
905 IF (l_debug = 'Y') THEN
906 Okc_Debug.Log('800: required values validation', 2);
907 END IF;
908
909 IF (l_debug = 'Y') THEN
910 Okc_Debug.Log('900: - attribute ID ', 2);
911 END IF;
912 IF ( p_id IS NULL) THEN
913 IF (l_debug = 'Y') THEN
914 Okc_Debug.Log('1000: - attribute ID is invalid', 2);
915 END IF;
916 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ID');
917 l_return_status := G_RET_STS_ERROR;
918 END IF;
919
920 IF (l_debug = 'Y') THEN
921 Okc_Debug.Log('1400: - attribute DOCUMENT_TYPE ', 2);
922 END IF;
923 IF ( p_document_type IS NULL) THEN
924 IF (l_debug = 'Y') THEN
925 Okc_Debug.Log('1500: - attribute DOCUMENT_TYPE is invalid', 2);
926 END IF;
927 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'DOCUMENT_TYPE');
928 l_return_status := G_RET_STS_ERROR;
929 END IF;
930
931 IF (l_debug = 'Y') THEN
932 Okc_Debug.Log('1400: - attribute DOCUMENT_ID ', 2);
933 END IF;
934 IF ( p_document_id IS NULL) THEN
935 IF (l_debug = 'Y') THEN
936 Okc_Debug.Log('1500: - attribute DOCUMENT_ID is invalid', 2);
937 END IF;
938 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'DOCUMENT_ID');
939 l_return_status := G_RET_STS_ERROR;
940 END IF;
941
942 IF (l_debug = 'Y') THEN
943 Okc_Debug.Log('1400: - attribute SCN_ID ', 2);
944 END IF;
945 IF ( p_scn_id IS NULL) THEN
946 IF (l_debug = 'Y') THEN
947 Okc_Debug.Log('1500: - attribute SCN_ID is invalid', 2);
948 END IF;
949 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'SCN_ID');
950 l_return_status := G_RET_STS_ERROR;
951 END IF;
952
953 IF (l_debug = 'Y') THEN
954 Okc_Debug.Log('1400: - attribute DISPLAY_SEQUENCE ', 2);
955 END IF;
956 IF ( p_display_sequence IS NULL) THEN
957 IF (l_debug = 'Y') THEN
958 Okc_Debug.Log('1500: - attribute DISPLAY_SEQUENCE is invalid', 2);
959 END IF;
960 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'DISPLAY_SEQUENCE');
961 l_return_status := G_RET_STS_ERROR;
962 END IF;
963
964 IF (l_debug = 'Y') THEN
965 Okc_Debug.Log('1400: - attribute SAV_SAE_ID', 2);
966 END IF;
967 IF ( p_sav_sae_id IS NULL) THEN
968 IF (l_debug = 'Y') THEN
969 Okc_Debug.Log('1500: - attribute SAV_SAE_ID is invalid', 2);
970 END IF;
971 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'SAV_SAE_ID');
972 l_return_status := G_RET_STS_ERROR;
973 END IF;
974 END IF;
975
976 IF p_validation_level > G_VALID_VALUE_VALID_LVL THEN
977 IF (l_debug = 'Y') THEN
978 Okc_Debug.Log('1100: static values and range validation', 2);
979 END IF;
980
981
982 IF (l_debug = 'Y') THEN
983 Okc_Debug.Log('1200: - attribute MANDATORY_YN ', 2);
984 END IF;
985
986 IF ( p_mandatory_yn NOT IN ('Y','N') AND p_mandatory_yn IS NOT NULL) THEN
987 IF (l_debug = 'Y') THEN
988 Okc_Debug.Log('1300: - attribute MANDATORY_YN is invalid', 2);
989 END IF;
990 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'MANDATORY_YN');
991 l_return_status := G_RET_STS_ERROR;
992 END IF;
993
994 IF (l_debug = 'Y') THEN
995 Okc_Debug.Log('1200: - attribute PRINT_TEXT_YN ', 2);
996 END IF;
997
998 IF ( p_print_text_yn NOT IN ('Y','N') AND p_print_text_yn IS NOT NULL) THEN
999 IF (l_debug = 'Y') THEN
1000 Okc_Debug.Log('1300: - attribute PRINT_TEXT_YN is invalid', 2);
1001 END IF;
1002 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'PRINT_TEXT_YN');
1003 l_return_status := G_RET_STS_ERROR;
1004 END IF;
1005
1006 IF (l_debug = 'Y') THEN
1007 Okc_Debug.Log('1200: - attribute CHANGE_NONSTD_YN ', 2);
1008 END IF;
1009 IF ( p_change_nonstd_yn NOT IN ('Y','N') AND p_change_nonstd_yn IS NOT NULL) THEN
1010 IF (l_debug = 'Y') THEN
1011 Okc_Debug.Log('1300: - attribute CHANGE_NONSTD_YN is invalid', 2);
1012 END IF;
1013 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'CHANGE_NONSTD_YN');
1014 l_return_status := G_RET_STS_ERROR;
1015 END IF;
1016
1017 END IF;
1018
1019 IF p_validation_level > G_LOOKUP_CODE_VALID_LVL THEN
1020 IF (l_debug = 'Y') THEN
1021 Okc_Debug.Log('1400: lookup codes validation', 2);
1022 END IF;
1023
1024 IF p_amendment_operation_code is not null THEN
1025 IF (l_debug = 'Y') THEN
1026 Okc_Debug.Log('1500: - attribute AMENDMENT_OPERATION_CODE ', 2);
1027 END IF;
1028
1029 l_return_status := Okc_Util.Check_Lookup_Code('OKC_AMEND_OPN_CODE',p_amendment_operation_code);
1030
1031 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
1032 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'AMENDMENT_OPERATION_CODE');
1033 l_return_status := G_RET_STS_ERROR;
1034 END IF;
1035
1036 IF p_summary_amend_operation_code is not null THEN
1037 IF (l_debug = 'Y') THEN
1038 Okc_Debug.Log('1500: - attribute SUMMARY_AMEND_OPERATION_CODE ', 2);
1039 END IF;
1040
1041 l_return_status := Okc_Util.Check_Lookup_Code('OKC_AMEND_OPN_CODE',p_summary_amend_operation_code);
1042
1043 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
1044 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'SUMMARY_AMEND_OPERATION_CODE');
1045 l_return_status := G_RET_STS_ERROR;
1046 END IF;
1047 END IF;
1048
1049 IF p_amendment_operation_code IN (G_AMEND_CODE_UPDATED,G_AMEND_CODE_ADDED) THEN
1050
1051 l_dummy_var := '?';
1052
1053 OPEN l_validate_amend_mode_csr;
1054 FETCH l_validate_amend_mode_csr INTO l_dummy_var;
1055 CLOSE l_validate_amend_mode_csr;
1056 IF l_dummy_var <> '?' THEN
1057 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_AMEND_INVALID');
1058 l_return_status := G_RET_STS_ERROR;
1059 END IF;
1060
1061 END IF;
1062
1063 END IF;
1064
1065 END IF;
1066
1067 IF p_validation_level > G_FOREIGN_KEY_VALID_LVL THEN
1068 IF (l_debug = 'Y') THEN
1069 Okc_Debug.Log('1600: foreigh keys validation ', 2);
1070 END IF;
1071
1072 IF p_document_type IS NOT NULL THEN
1073 l_dummy_var := '?';
1074 OPEN l_doc_type_csr;
1075 FETCH l_doc_type_csr INTO l_dummy_var;
1076 CLOSE l_doc_type_csr;
1077 IF (l_dummy_var = '?') THEN
1078 IF (l_debug = 'Y') THEN
1079 Okc_Debug.Log('2300: - attribute DOCUMENT_TYPE is invalid', 2);
1080 END IF;
1081 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DOCUMENT_TYPE');
1082 l_return_status := G_RET_STS_ERROR;
1083 END IF;
1084 END IF;
1085
1086 IF (l_debug = 'Y') THEN
1087 Okc_Debug.Log('1700: - attribute SAV_SAE_ID ', 2);
1088 END IF;
1089 IF p_sav_sae_id IS NOT NULL THEN
1090 l_dummy_var := '?';
1091 OPEN lc_sav_sae_id;
1092 FETCH lc_sav_sae_id INTO l_dummy_var;
1093 CLOSE lc_sav_sae_id;
1094 IF (l_dummy_var = '?') THEN
1095 IF (l_debug = 'Y') THEN
1096 Okc_Debug.Log('1800: - attribute SAV_SAE_ID is invalid', 2);
1097 END IF;
1098 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SAV_SAE_ID');
1099 l_return_status := G_RET_STS_ERROR;
1100 END IF;
1101 END IF;
1102
1103
1104 IF (l_debug = 'Y') THEN
1105 Okc_Debug.Log('1700: - attribute SCN_ID ', 2);
1106 END IF;
1107 IF p_scn_id IS NOT NULL THEN
1108 l_dummy_var := '?';
1109 OPEN lc_scn_id;
1110 FETCH lc_scn_id INTO l_dummy_var;
1111 CLOSE lc_scn_id;
1112 IF (l_dummy_var = '?') THEN
1113 IF (l_debug = 'Y') THEN
1114 Okc_Debug.Log('1800: - attribute SCN_ID is invalid', 2);
1115 END IF;
1116 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SCN_ID');
1117 l_return_status := G_RET_STS_ERROR;
1118 END IF;
1119 END IF;
1120
1121 IF (l_debug = 'Y') THEN
1122 Okc_Debug.Log('1700: - attribute ARTICLE_VERSION_ID ', 2);
1123 END IF;
1124 IF p_article_version_id IS NOT NULL THEN
1125 l_dummy_var := '?';
1126 OPEN lc_article_version_id;
1127 FETCH lc_article_version_id INTO l_dummy_var;
1128 CLOSE lc_article_version_id;
1129 IF (l_dummy_var = '?') THEN
1130 IF (l_debug = 'Y') THEN
1131 Okc_Debug.Log('1800: - attribute ARTICLE_VERSION_ID is invalid', 2);
1132 END IF;
1133 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ARTICLE_VERSION_ID');
1134 l_return_status := G_RET_STS_ERROR;
1135 END IF;
1136 END IF;
1137
1138 END IF;
1139
1140
1141 IF (l_debug = 'Y') THEN
1142 Okc_Debug.Log('1900: Leaving Validate_Attributes ', 2);
1143 END IF;
1144
1145 RETURN l_return_status;
1146
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 Okc_Debug.Log('2000: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
1150 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1151 p_msg_name => G_UNEXPECTED_ERROR,
1152 p_token1 => G_SQLCODE_TOKEN,
1153 p_token1_value => sqlcode,
1154 p_token2 => G_SQLERRM_TOKEN,
1155 p_token2_value => sqlerrm);
1156
1157
1158 IF lc_sav_sae_id%ISOPEN THEN
1159 CLOSE lc_sav_sae_id;
1160 END IF;
1161
1162 IF lc_scn_id%ISOPEN THEN
1163 CLOSE lc_scn_id;
1164 END IF;
1165 /*
1166 IF l_validate_amend_mode_csr%ISOPEN THEN
1167 CLOSE l_validate_amend_mode_csr;
1168 END IF;
1169 */
1170
1171 IF lc_article_version_id%ISOPEN THEN
1172 CLOSE lc_article_version_id;
1173 END IF;
1174
1175 IF l_doc_type_csr%ISOPEN THEN
1176 CLOSE l_doc_type_csr;
1177 END IF;
1178
1179
1180 RETURN G_RET_STS_UNEXP_ERROR;
1181
1182 END Validate_Attributes;
1183
1184
1185 ---------------------------------------------------------------------------
1186 -- PROCEDURE Validate_Record
1187 -- It calls Item Level Validations and then makes Record Level Validations
1188 ---------------------------------------------------------------------------
1189 ------------------------------------------
1190 -- Validate_Record for:OKC_K_ARTICLES_B --
1191 ------------------------------------------
1192 FUNCTION Validate_Record (
1193 p_validation_level IN NUMBER,
1194 p_id IN NUMBER,
1195 p_sav_sae_id IN NUMBER,
1196 p_document_type IN VARCHAR2,
1197 p_document_id IN NUMBER,
1198 p_cle_id IN NUMBER,
1199 p_source_flag IN VARCHAR2,
1200 p_mandatory_yn IN VARCHAR2,
1201 p_scn_id IN NUMBER,
1202 p_label IN VARCHAR2,
1203 p_amendment_description IN VARCHAR2,
1204 p_amendment_operation_code IN VARCHAR2,
1205 p_article_version_id IN NUMBER,
1206 p_change_nonstd_yn IN VARCHAR2,
1207 p_orig_system_reference_code IN VARCHAR2,
1208 p_orig_system_reference_id1 IN NUMBER,
1209 p_orig_system_reference_id2 IN NUMBER,
1210 p_display_sequence IN NUMBER,
1211 p_attribute_category IN VARCHAR2,
1212 p_attribute1 IN VARCHAR2,
1213 p_attribute2 IN VARCHAR2,
1214 p_attribute3 IN VARCHAR2,
1215 p_attribute4 IN VARCHAR2,
1216 p_attribute5 IN VARCHAR2,
1217 p_attribute6 IN VARCHAR2,
1218 p_attribute7 IN VARCHAR2,
1219 p_attribute8 IN VARCHAR2,
1220 p_attribute9 IN VARCHAR2,
1221 p_attribute10 IN VARCHAR2,
1222 p_attribute11 IN VARCHAR2,
1223 p_attribute12 IN VARCHAR2,
1224 p_attribute13 IN VARCHAR2,
1225 p_attribute14 IN VARCHAR2,
1226 p_attribute15 IN VARCHAR2,
1227 p_print_text_yn IN VARCHAR2,
1228 p_summary_amend_operation_code IN VARCHAR2,
1229 p_ref_article_id IN NUMBER,
1230 p_ref_article_version_id IN NUMBER
1231 ) RETURN VARCHAR2 IS
1232 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1233 l_dummy_var VARCHAR2(1) := '?';
1234
1235 CURSOR l_validate_article_csr IS
1236 SELECT '!' FROM OKC_ARTICLE_VERSIONS
1237 WHERE ARTICLE_VERSION_ID = p_article_version_id
1238 AND ARTICLE_ID = p_sav_sae_id;
1239
1240
1241 BEGIN
1242
1243 IF (l_debug = 'Y') THEN
1244 Okc_Debug.Log('2100: Entered Validate_Record', 2);
1245 END IF;
1246
1247 --- Validate all non-missing attributes (Item Level Validation)
1248 l_return_status := Validate_Attributes(
1249 p_validation_level => p_validation_level,
1250
1251 p_id => p_id,
1252 p_sav_sae_id => p_sav_sae_id,
1253 p_document_type => p_document_type,
1254 p_document_id => p_document_id,
1255 p_cle_id => p_cle_id,
1256 p_source_flag => p_source_flag,
1257 p_mandatory_yn => p_mandatory_yn,
1258 p_scn_id => p_scn_id,
1259 p_label => p_label,
1260 p_amendment_description => p_amendment_description,
1261 p_amendment_operation_code => p_amendment_operation_code,
1262 p_article_version_id => p_article_version_id,
1263 p_change_nonstd_yn => p_change_nonstd_yn,
1264 p_orig_system_reference_code => p_orig_system_reference_code,
1265 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1266 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1267 p_display_sequence => p_display_sequence,
1268 p_attribute_category => p_attribute_category,
1269 p_attribute1 => p_attribute1,
1270 p_attribute2 => p_attribute2,
1271 p_attribute3 => p_attribute3,
1272 p_attribute4 => p_attribute4,
1273 p_attribute5 => p_attribute5,
1274 p_attribute6 => p_attribute6,
1275 p_attribute7 => p_attribute7,
1276 p_attribute8 => p_attribute8,
1277 p_attribute9 => p_attribute9,
1278 p_attribute10 => p_attribute10,
1279 p_attribute11 => p_attribute11,
1280 p_attribute12 => p_attribute12,
1281 p_attribute13 => p_attribute13,
1282 p_attribute14 => p_attribute14,
1283 p_attribute15 => p_attribute15,
1284 p_print_text_yn => p_print_text_yn,
1285 p_summary_amend_operation_code => p_summary_amend_operation_code,
1286 p_ref_article_id => p_ref_article_id,
1287 p_ref_article_version_id => p_ref_article_version_id
1288 );
1289
1290 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1291 IF (l_debug = 'Y') THEN
1292 Okc_Debug.Log('2200: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
1293 END IF;
1294 RETURN G_RET_STS_UNEXP_ERROR;
1295 END IF;
1296
1297 --- Record Level Validation
1298 IF p_validation_level > G_RECORD_VALID_LVL THEN
1299 IF (l_debug = 'Y') THEN
1300 Okc_Debug.Log('2300: Entered Record Level Validations', 2);
1301 END IF;
1302
1303 -- this validation is not required for templates
1304 IF p_sav_sae_id IS NOT NULL AND p_article_version_id IS NOT NULL
1305 AND p_document_type <> OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE
1306 THEN
1307 l_dummy_var := '?';
1308 OPEN l_validate_article_csr;
1309 FETCH l_validate_article_csr into l_dummy_var;
1310 CLOSE L_validate_article_csr;
1311 If l_dummy_var='?' THEN
1312
1313 IF (l_debug = 'Y') THEN
1314 Okc_Debug.Log('1500: - Wrong Combination of Article_version_id and sav_sae_id', 2);
1315 END IF;
1316 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_VERSION_ID');
1317 l_return_status := G_RET_STS_ERROR;
1318 END IF;
1319 END IF;
1320 END IF;
1321
1322 IF (l_debug = 'Y') THEN
1323 Okc_Debug.Log('2400: Leaving Validate_Record : '||sqlerrm, 2);
1324 END IF;
1325 RETURN l_return_status ;
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329
1330 IF (l_debug = 'Y') THEN
1331 Okc_Debug.Log('2500: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
1332 END IF;
1333
1334 IF l_validate_article_csr%ISOPEN THEN
1335 CLOSE l_validate_article_csr;
1336 END IF;
1337
1338 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1339 p_msg_name => G_UNEXPECTED_ERROR,
1340 p_token1 => G_SQLCODE_TOKEN,
1341 p_token1_value => sqlcode,
1342 p_token2 => G_SQLERRM_TOKEN,
1343 p_token2_value => sqlerrm);
1344 RETURN G_RET_STS_UNEXP_ERROR ;
1345
1346 END Validate_Record;
1347
1348 ---------------------------------------------------------------------------
1349 -- PROCEDURE validate_row
1350 ---------------------------------------------------------------------------
1351 ---------------------------------------
1352 -- validate_row for:OKC_K_ARTICLES_B --
1353 ---------------------------------------
1354 PROCEDURE validate_row(
1355 p_validation_level IN NUMBER,
1356 x_return_status OUT NOCOPY VARCHAR2,
1357 p_id IN NUMBER,
1358 p_sav_sae_id IN NUMBER,
1359 p_document_type IN VARCHAR2,
1360 p_document_id IN NUMBER,
1361 p_cle_id IN NUMBER,
1362 p_source_flag IN VARCHAR2,
1363 p_mandatory_yn IN VARCHAR2,
1364 p_scn_id IN NUMBER,
1365 p_label IN VARCHAR2,
1366 p_amendment_description IN VARCHAR2,
1367 p_amendment_operation_code IN VARCHAR2,
1368 p_article_version_id IN NUMBER,
1369 p_change_nonstd_yn IN VARCHAR2,
1370 p_orig_system_reference_code IN VARCHAR2,
1371 p_orig_system_reference_id1 IN NUMBER,
1372 p_orig_system_reference_id2 IN NUMBER,
1373 p_display_sequence IN NUMBER,
1374 p_attribute_category IN VARCHAR2,
1375 p_attribute1 IN VARCHAR2,
1376 p_attribute2 IN VARCHAR2,
1377 p_attribute3 IN VARCHAR2,
1378 p_attribute4 IN VARCHAR2,
1379 p_attribute5 IN VARCHAR2,
1380 p_attribute6 IN VARCHAR2,
1381 p_attribute7 IN VARCHAR2,
1382 p_attribute8 IN VARCHAR2,
1383 p_attribute9 IN VARCHAR2,
1384 p_attribute10 IN VARCHAR2,
1385 p_attribute11 IN VARCHAR2,
1386 p_attribute12 IN VARCHAR2,
1387 p_attribute13 IN VARCHAR2,
1388 p_attribute14 IN VARCHAR2,
1389 p_attribute15 IN VARCHAR2,
1390 p_print_text_yn IN VARCHAR2,
1391 p_summary_amend_operation_code IN VARCHAR2,
1392 p_ref_article_id IN NUMBER,
1393 p_ref_article_version_id IN NUMBER,
1394 p_object_version_number IN NUMBER
1395 ) IS
1396 l_sav_sae_id OKC_K_ARTICLES_B.SAV_SAE_ID%TYPE;
1397 l_document_type OKC_K_ARTICLES_B.DOCUMENT_TYPE%TYPE;
1398 l_document_id OKC_K_ARTICLES_B.DOCUMENT_ID%TYPE;
1399 l_cle_id OKC_K_ARTICLES_B.CLE_ID%TYPE;
1400 l_source_flag OKC_K_ARTICLES_B.SOURCE_FLAG%TYPE;
1401 l_mandatory_yn OKC_K_ARTICLES_B.MANDATORY_YN%TYPE;
1402 l_scn_id OKC_K_ARTICLES_B.SCN_ID%TYPE;
1403 l_label OKC_K_ARTICLES_B.LABEL%TYPE;
1404 l_amendment_description OKC_K_ARTICLES_B.AMENDMENT_DESCRIPTION%TYPE;
1405 l_amendment_operation_code OKC_K_ARTICLES_B.AMENDMENT_OPERATION_CODE%TYPE;
1406 l_article_version_id OKC_K_ARTICLES_B.ARTICLE_VERSION_ID%TYPE;
1407 l_change_nonstd_yn OKC_K_ARTICLES_B.CHANGE_NONSTD_YN%TYPE;
1408 l_orig_system_reference_code OKC_K_ARTICLES_B.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
1409 l_orig_system_reference_id1 OKC_K_ARTICLES_B.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
1410 l_orig_system_reference_id2 OKC_K_ARTICLES_B.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
1411 l_display_sequence OKC_K_ARTICLES_B.DISPLAY_SEQUENCE%TYPE;
1412 l_attribute_category OKC_K_ARTICLES_B.ATTRIBUTE_CATEGORY%TYPE;
1413 l_attribute1 OKC_K_ARTICLES_B.ATTRIBUTE1%TYPE;
1414 l_attribute2 OKC_K_ARTICLES_B.ATTRIBUTE2%TYPE;
1415 l_attribute3 OKC_K_ARTICLES_B.ATTRIBUTE3%TYPE;
1416 l_attribute4 OKC_K_ARTICLES_B.ATTRIBUTE4%TYPE;
1417 l_attribute5 OKC_K_ARTICLES_B.ATTRIBUTE5%TYPE;
1418 l_attribute6 OKC_K_ARTICLES_B.ATTRIBUTE6%TYPE;
1419 l_attribute7 OKC_K_ARTICLES_B.ATTRIBUTE7%TYPE;
1420 l_attribute8 OKC_K_ARTICLES_B.ATTRIBUTE8%TYPE;
1421 l_attribute9 OKC_K_ARTICLES_B.ATTRIBUTE9%TYPE;
1422 l_attribute10 OKC_K_ARTICLES_B.ATTRIBUTE10%TYPE;
1423 l_attribute11 OKC_K_ARTICLES_B.ATTRIBUTE11%TYPE;
1424 l_attribute12 OKC_K_ARTICLES_B.ATTRIBUTE12%TYPE;
1425 l_attribute13 OKC_K_ARTICLES_B.ATTRIBUTE13%TYPE;
1426 l_attribute14 OKC_K_ARTICLES_B.ATTRIBUTE14%TYPE;
1427 l_attribute15 OKC_K_ARTICLES_B.ATTRIBUTE15%TYPE;
1428 l_print_text_yn OKC_K_ARTICLES_B.print_text_yn%TYPE;
1429 l_summary_amend_operation_code OKC_K_ARTICLES_B.summary_amend_operation_code%TYPE;
1430 l_ref_article_id OKC_K_ARTICLES_B.ref_article_id%TYPE;
1431 l_ref_article_version_id OKC_K_ARTICLES_B.ref_article_version_id%TYPE;
1432 l_object_version_number OKC_K_ARTICLES_B.OBJECT_VERSION_NUMBER%TYPE;
1433 l_created_by OKC_K_ARTICLES_B.CREATED_BY%TYPE;
1434 l_creation_date OKC_K_ARTICLES_B.CREATION_DATE%TYPE;
1435 l_last_updated_by OKC_K_ARTICLES_B.LAST_UPDATED_BY%TYPE;
1436 l_last_update_login OKC_K_ARTICLES_B.LAST_UPDATE_LOGIN%TYPE;
1437 l_last_update_date OKC_K_ARTICLES_B.LAST_UPDATE_DATE%TYPE;
1438 l_last_amended_by OKC_K_ARTICLES_B.LAST_AMENDED_BY%TYPE;
1439 l_last_amendment_date OKC_K_ARTICLES_B.LAST_AMENDMENT_DATE%TYPE;
1440 BEGIN
1441
1442 IF (l_debug = 'Y') THEN
1443 Okc_Debug.Log('2600: Entered validate_row', 2);
1444 END IF;
1445
1446 -- Setting attributes
1447 x_return_status := Set_Attributes(
1448 p_id => p_id,
1449 p_sav_sae_id => p_sav_sae_id,
1450 p_document_type => p_document_type,
1451 p_document_id => p_document_id,
1452 p_cle_id => p_cle_id,
1453 p_source_flag => p_source_flag,
1454 p_mandatory_yn => p_mandatory_yn,
1455 p_scn_id => p_scn_id,
1456 p_label => p_label,
1457 p_amendment_description => p_amendment_description,
1458 p_amendment_operation_code => p_amendment_operation_code,
1459 p_article_version_id => p_article_version_id,
1460 p_change_nonstd_yn => p_change_nonstd_yn,
1461 p_orig_system_reference_code => p_orig_system_reference_code,
1462 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1463 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1464 p_display_sequence => p_display_sequence,
1465 p_attribute_category => p_attribute_category,
1466 p_attribute1 => p_attribute1,
1467 p_attribute2 => p_attribute2,
1468 p_attribute3 => p_attribute3,
1469 p_attribute4 => p_attribute4,
1470 p_attribute5 => p_attribute5,
1471 p_attribute6 => p_attribute6,
1472 p_attribute7 => p_attribute7,
1473 p_attribute8 => p_attribute8,
1474 p_attribute9 => p_attribute9,
1475 p_attribute10 => p_attribute10,
1476 p_attribute11 => p_attribute11,
1477 p_attribute12 => p_attribute12,
1478 p_attribute13 => p_attribute13,
1479 p_attribute14 => p_attribute14,
1480 p_attribute15 => p_attribute15,
1481 p_print_text_yn => p_print_text_yn,
1482 p_summary_amend_operation_code => p_summary_amend_operation_code,
1483 p_ref_article_id => p_ref_article_id,
1484 p_ref_article_version_id => p_ref_article_version_id,
1485 p_object_version_number => p_object_version_number,
1486 p_last_amended_by => NULL,
1487 p_last_amendment_date => NULL,
1488 x_sav_sae_id => l_sav_sae_id,
1489 x_document_type => l_document_type,
1490 x_document_id => l_document_id,
1491 x_cle_id => l_cle_id,
1492 x_source_flag => l_source_flag,
1493 x_mandatory_yn => l_mandatory_yn,
1494 x_scn_id => l_scn_id,
1495 x_label => l_label,
1496 x_amendment_description => l_amendment_description,
1497 x_amendment_operation_code => l_amendment_operation_code,
1498 x_article_version_id => l_article_version_id,
1499 x_change_nonstd_yn => l_change_nonstd_yn,
1500 x_orig_system_reference_code => l_orig_system_reference_code,
1501 x_orig_system_reference_id1 => l_orig_system_reference_id1,
1502 x_orig_system_reference_id2 => l_orig_system_reference_id2,
1503 x_display_sequence => l_display_sequence,
1504 x_attribute_category => l_attribute_category,
1505 x_attribute1 => l_attribute1,
1506 x_attribute2 => l_attribute2,
1507 x_attribute3 => l_attribute3,
1508 x_attribute4 => l_attribute4,
1509 x_attribute5 => l_attribute5,
1510 x_attribute6 => l_attribute6,
1511 x_attribute7 => l_attribute7,
1512 x_attribute8 => l_attribute8,
1513 x_attribute9 => l_attribute9,
1514 x_attribute10 => l_attribute10,
1515 x_attribute11 => l_attribute11,
1516 x_attribute12 => l_attribute12,
1517 x_attribute13 => l_attribute13,
1518 x_attribute14 => l_attribute14,
1519 x_attribute15 => l_attribute15,
1520 x_print_text_yn => l_print_text_yn,
1521 x_summary_amend_operation_code => l_summary_amend_operation_code,
1522 x_object_version_number => l_object_version_number,
1523 x_ref_article_id => l_ref_article_id,
1524 x_ref_article_version_id => l_ref_article_version_id,
1525 x_last_amended_by => l_last_amended_by,
1526 x_last_amendment_date => l_last_amendment_date
1527 );
1528 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1529 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1530 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1531 RAISE OKC_API.G_EXCEPTION_ERROR;
1532 END IF;
1533
1534 -- Validate all non-missing attributes (Item Level Validation)
1535 x_return_status := Validate_Record(
1536 p_validation_level => p_validation_level,
1537 p_id => p_id,
1538 p_sav_sae_id => l_sav_sae_id,
1539 p_document_type => l_document_type,
1540 p_document_id => l_document_id,
1541 p_cle_id => l_cle_id,
1542 p_source_flag => l_source_flag,
1543 p_mandatory_yn => l_mandatory_yn,
1544 p_scn_id => l_scn_id,
1545 p_label => l_label,
1546 p_amendment_description => l_amendment_description,
1547 p_amendment_operation_code => l_amendment_operation_code,
1548 p_article_version_id => l_article_version_id,
1549 p_change_nonstd_yn => l_change_nonstd_yn,
1550 p_orig_system_reference_code => l_orig_system_reference_code,
1551 p_orig_system_reference_id1 => l_orig_system_reference_id1,
1552 p_orig_system_reference_id2 => l_orig_system_reference_id2,
1553 p_display_sequence => l_display_sequence,
1554 p_attribute_category => l_attribute_category,
1555 p_attribute1 => l_attribute1,
1556 p_attribute2 => l_attribute2,
1557 p_attribute3 => l_attribute3,
1558 p_attribute4 => l_attribute4,
1559 p_attribute5 => l_attribute5,
1560 p_attribute6 => l_attribute6,
1561 p_attribute7 => l_attribute7,
1562 p_attribute8 => l_attribute8,
1563 p_attribute9 => l_attribute9,
1564 p_attribute10 => l_attribute10,
1565 p_attribute11 => l_attribute11,
1566 p_attribute12 => l_attribute12,
1567 p_attribute13 => l_attribute13,
1568 p_attribute14 => l_attribute14,
1569 p_attribute15 => l_attribute15,
1570 p_print_text_yn => l_print_text_yn,
1571 p_summary_amend_operation_code => l_summary_amend_operation_code,
1572 p_ref_article_id => l_ref_article_id,
1573 p_ref_article_version_id => l_ref_article_version_id
1574 );
1575
1576 IF (l_debug = 'Y') THEN
1577 Okc_Debug.Log('2700: Leaving validate_row', 2);
1578 END IF;
1579
1580 EXCEPTION
1581 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1582
1583 IF (l_debug = 'Y') THEN
1584 Okc_Debug.Log('2800: Leaving Validate_Row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1585 END IF;
1586
1587 x_return_status := G_RET_STS_ERROR;
1588
1589 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1590
1591 IF (l_debug = 'Y') THEN
1592 Okc_Debug.Log('2900: Leaving Validate_Row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1593 END IF;
1594
1595 x_return_status := G_RET_STS_UNEXP_ERROR;
1596 WHEN OTHERS THEN
1597
1598 IF (l_debug = 'Y') THEN
1599 Okc_Debug.Log('3000: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
1600 END IF;
1601
1602 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1603 p_msg_name => G_UNEXPECTED_ERROR,
1604 p_token1 => G_SQLCODE_TOKEN,
1605 p_token1_value => sqlcode,
1606 p_token2 => G_SQLERRM_TOKEN,
1607 p_token2_value => sqlerrm);
1608 x_return_status := G_RET_STS_UNEXP_ERROR;
1609
1610 END Validate_Row;
1611
1612 ---------------------------------------------------------------------------
1613 -- PROCEDURE Insert_Row
1614 ---------------------------------------------------------------------------
1615 -------------------------------------
1616 -- Insert_Row for:OKC_K_ARTICLES_B --
1617 -------------------------------------
1618 FUNCTION Insert_Row(
1619 p_id IN NUMBER,
1620 p_sav_sae_id IN NUMBER,
1621 p_cat_type IN VARCHAR2, -- Bug 3341342
1622 p_document_type IN VARCHAR2,
1623 p_document_id IN NUMBER,
1624 p_cle_id IN NUMBER,
1625 p_source_flag IN VARCHAR2,
1626 p_mandatory_yn IN VARCHAR2,
1627 p_scn_id IN NUMBER,
1628 p_label IN VARCHAR2,
1629 p_amendment_description IN VARCHAR2,
1630 p_amendment_operation_code IN VARCHAR2,
1631 p_article_version_id IN NUMBER,
1632 p_change_nonstd_yn IN VARCHAR2,
1633 p_orig_system_reference_code IN VARCHAR2,
1634 p_orig_system_reference_id1 IN NUMBER,
1635 p_orig_system_reference_id2 IN NUMBER,
1636 p_display_sequence IN NUMBER,
1637 p_attribute_category IN VARCHAR2,
1638 p_attribute1 IN VARCHAR2,
1639 p_attribute2 IN VARCHAR2,
1640 p_attribute3 IN VARCHAR2,
1641 p_attribute4 IN VARCHAR2,
1642 p_attribute5 IN VARCHAR2,
1643 p_attribute6 IN VARCHAR2,
1644 p_attribute7 IN VARCHAR2,
1645 p_attribute8 IN VARCHAR2,
1646 p_attribute9 IN VARCHAR2,
1647 p_attribute10 IN VARCHAR2,
1648 p_attribute11 IN VARCHAR2,
1649 p_attribute12 IN VARCHAR2,
1650 p_attribute13 IN VARCHAR2,
1651 p_attribute14 IN VARCHAR2,
1652 p_attribute15 IN VARCHAR2,
1653 p_print_text_yn IN VARCHAR2,
1654 p_summary_amend_operation_code IN VARCHAR2,
1655 p_ref_article_id IN NUMBER,
1656 p_ref_article_version_id IN NUMBER,
1657 p_object_version_number IN NUMBER,
1658 p_created_by IN NUMBER,
1659 p_creation_date IN DATE,
1660 p_last_updated_by IN NUMBER,
1661 p_last_update_login IN NUMBER,
1662 p_last_update_date IN DATE,
1663 p_last_amended_by IN NUMBER,
1664 p_last_amendment_date IN DATE
1665
1666 ) RETURN VARCHAR2 IS
1667 l_chr_id NUMBER;
1668
1669 BEGIN
1670
1671 IF (l_debug = 'Y') THEN
1672 Okc_Debug.Log('3100: Entered Insert_Row function', 2);
1673 END IF;
1674
1675 IF p_document_type IN ('OKC_SELL','OKC_BUY','OKO','OKS','OKE_SELL','OKE_BUY','OKL') THEN
1676 l_chr_id := okc_terms_util_pvt.get_chr_id_for_doc_id(p_document_id);
1677 END IF;
1678
1679 INSERT INTO OKC_K_ARTICLES_B(
1680 ID,
1681 SAV_SAE_ID,
1682 SAV_SAV_RELEASE,
1683 SBT_CODE,
1684 CAT_TYPE,
1685 CHR_ID,
1686 CAT_ID,
1687 DNZ_CHR_ID,
1688 FULLTEXT_YN,
1689 DOCUMENT_TYPE,
1690 DOCUMENT_ID,
1691 CLE_ID,
1692 SOURCE_FLAG,
1693 MANDATORY_YN,
1694 SCN_ID,
1695 LABEL,
1696 AMENDMENT_DESCRIPTION,
1697 AMENDMENT_OPERATION_CODE,
1698 ARTICLE_VERSION_ID,
1699 CHANGE_NONSTD_YN,
1700 ORIG_SYSTEM_REFERENCE_CODE,
1701 ORIG_SYSTEM_REFERENCE_ID1,
1702 ORIG_SYSTEM_REFERENCE_ID2,
1703 DISPLAY_SEQUENCE,
1704 ATTRIBUTE_CATEGORY,
1705 ATTRIBUTE1,
1706 ATTRIBUTE2,
1707 ATTRIBUTE3,
1708 ATTRIBUTE4,
1709 ATTRIBUTE5,
1710 ATTRIBUTE6,
1711 ATTRIBUTE7,
1712 ATTRIBUTE8,
1713 ATTRIBUTE9,
1714 ATTRIBUTE10,
1715 ATTRIBUTE11,
1716 ATTRIBUTE12,
1717 ATTRIBUTE13,
1718 ATTRIBUTE14,
1719 ATTRIBUTE15,
1720 PRINT_TEXT_YN,
1721 SUMMARY_AMEND_OPERATION_CODE,
1722 REF_ARTICLE_ID,
1723 REF_ARTICLE_VERSION_ID,
1724 OBJECT_VERSION_NUMBER,
1725 CREATED_BY,
1726 CREATION_DATE,
1727 LAST_UPDATED_BY,
1728 LAST_UPDATE_LOGIN,
1729 LAST_UPDATE_DATE,
1730 ORIG_ARTICLE_ID,
1731 LAST_AMENDED_BY,
1732 LAST_AMENDMENT_DATE)
1733 VALUES (
1734 p_id,
1735 p_sav_sae_id,
1736 Null,
1737 Null,
1738 p_cat_type,--Bug 3341342
1739 decode(p_cle_id,NULL,l_chr_id,NULL),
1740 Null,
1741 l_chr_id,
1742 Null,
1743 p_document_type,
1744 p_document_id,
1745 p_cle_id,
1746 p_source_flag,
1747 p_mandatory_yn,
1748 p_scn_id,
1749 p_label,
1750 p_amendment_description,
1751 p_amendment_operation_code,
1752 decode(p_document_type, OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE,NULL,p_article_version_id),
1753 p_change_nonstd_yn,
1754 p_orig_system_reference_code,
1755 p_orig_system_reference_id1,
1756 p_orig_system_reference_id2,
1757 p_display_sequence,
1758 p_attribute_category,
1759 p_attribute1,
1760 p_attribute2,
1761 p_attribute3,
1762 p_attribute4,
1763 p_attribute5,
1764 p_attribute6,
1765 p_attribute7,
1766 p_attribute8,
1767 p_attribute9,
1768 p_attribute10,
1769 p_attribute11,
1770 p_attribute12,
1771 p_attribute13,
1772 p_attribute14,
1773 p_attribute15,
1774 p_print_text_yn,
1775 p_summary_amend_operation_code,
1776 p_ref_article_id,
1777 p_ref_article_version_id,
1778 p_object_version_number,
1779 p_created_by,
1780 p_creation_date,
1781 p_last_updated_by,
1782 p_last_update_login,
1783 p_last_update_date,
1784 p_sav_sae_id,
1785 p_last_amended_by,
1786 p_last_amendment_date);
1787
1788 IF (l_debug = 'Y') THEN
1789 Okc_Debug.Log('3200: Leaving Insert_Row', 2);
1790 END IF;
1791
1792 RETURN( G_RET_STS_SUCCESS );
1793
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796
1797 IF (l_debug = 'Y') THEN
1798 Okc_Debug.Log('3300: Leaving Insert_Row:OTHERS Exception', 2);
1799 END IF;
1800
1801 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1802 p_msg_name => G_UNEXPECTED_ERROR,
1803 p_token1 => G_SQLCODE_TOKEN,
1804 p_token1_value => sqlcode,
1805 p_token2 => G_SQLERRM_TOKEN,
1806 p_token2_value => sqlerrm);
1807
1808 RETURN( G_RET_STS_UNEXP_ERROR );
1809
1810 END Insert_Row;
1811
1812 -------------------------------------
1813 -- Insert_Row for:OKC_K_ARTICLES_B --
1814 -------------------------------------
1815 PROCEDURE Insert_Row(
1816 p_validation_level IN NUMBER,
1817 x_return_status OUT NOCOPY VARCHAR2,
1818 p_id IN NUMBER,
1819 p_sav_sae_id IN NUMBER,
1820 p_cat_type IN VARCHAR2,--Bug 3341342
1821 p_document_type IN VARCHAR2,
1822 p_document_id IN NUMBER,
1823 p_cle_id IN NUMBER,
1824 p_source_flag IN VARCHAR2,
1825 p_mandatory_yn IN VARCHAR2,
1826 p_scn_id IN NUMBER,
1827 p_label IN VARCHAR2,
1828 p_amendment_description IN VARCHAR2,
1829 p_amendment_operation_code IN VARCHAR2,
1830 p_article_version_id IN NUMBER,
1831 p_change_nonstd_yn IN VARCHAR2,
1832 p_orig_system_reference_code IN VARCHAR2,
1833 p_orig_system_reference_id1 IN NUMBER,
1834 p_orig_system_reference_id2 IN NUMBER,
1835 p_display_sequence IN NUMBER,
1836 p_attribute_category IN VARCHAR2,
1837 p_attribute1 IN VARCHAR2,
1838 p_attribute2 IN VARCHAR2,
1839 p_attribute3 IN VARCHAR2,
1840 p_attribute4 IN VARCHAR2,
1841 p_attribute5 IN VARCHAR2,
1842 p_attribute6 IN VARCHAR2,
1843 p_attribute7 IN VARCHAR2,
1844 p_attribute8 IN VARCHAR2,
1845 p_attribute9 IN VARCHAR2,
1846 p_attribute10 IN VARCHAR2,
1847 p_attribute11 IN VARCHAR2,
1848 p_attribute12 IN VARCHAR2,
1849 p_attribute13 IN VARCHAR2,
1850 p_attribute14 IN VARCHAR2,
1851 p_attribute15 IN VARCHAR2,
1852 p_print_text_yn IN VARCHAR2,
1853 p_summary_amend_operation_code IN VARCHAR2,
1854 p_ref_article_id IN NUMBER,
1855 p_ref_article_version_id IN NUMBER,
1856 x_id OUT NOCOPY NUMBER
1857
1858 ) IS
1859
1860 l_object_version_number OKC_K_ARTICLES_B.OBJECT_VERSION_NUMBER%TYPE;
1861 l_created_by OKC_K_ARTICLES_B.CREATED_BY%TYPE;
1862 l_creation_date OKC_K_ARTICLES_B.CREATION_DATE%TYPE;
1863 l_last_updated_by OKC_K_ARTICLES_B.LAST_UPDATED_BY%TYPE;
1864 l_last_update_login OKC_K_ARTICLES_B.LAST_UPDATE_LOGIN%TYPE;
1865 l_last_update_date OKC_K_ARTICLES_B.LAST_UPDATE_DATE%TYPE;
1866 l_last_amended_by OKC_K_ARTICLES_B.LAST_AMENDED_BY%TYPE;
1867 l_last_amendment_date OKC_K_ARTICLES_B.LAST_AMENDMENT_DATE%TYPE;
1868 BEGIN
1869
1870 x_return_status := G_RET_STS_SUCCESS;
1871
1872 IF (l_debug = 'Y') THEN
1873 Okc_Debug.Log('3400: Entered Insert_Row', 2);
1874 END IF;
1875
1876 --- Setting item attributes
1877 -- Set primary key value
1878 IF( p_id IS NULL ) THEN
1879 x_return_status := Get_Seq_Id(
1880 p_id => p_id,
1881 x_id => x_id
1882 );
1883 --- If any errors happen abort API
1884 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1885 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1886 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1887 RAISE OKC_API.G_EXCEPTION_ERROR;
1888 END IF;
1889 ELSE
1890 x_id := p_id;
1891 END IF;
1892 -- Set Internal columns
1893 l_object_version_number := 1;
1894 l_creation_date := Sysdate;
1895 l_created_by := Fnd_Global.User_Id;
1896 l_last_update_date := l_creation_date;
1897 l_last_updated_by := l_created_by;
1898 l_last_update_login := Fnd_Global.Login_Id;
1899 IF p_amendment_operation_code IS NOT NULL THEN
1900 l_last_amendment_date := l_creation_date;
1901 l_last_amended_by := l_created_by;
1902 END IF;
1903
1904 --- Validate all non-missing attributes
1905 x_return_status := Validate_Record(
1906 p_validation_level => p_validation_level,
1907 p_id => x_id,
1908 p_sav_sae_id => p_sav_sae_id,
1909 p_document_type => p_document_type,
1910 p_document_id => p_document_id,
1911 p_cle_id => p_cle_id,
1912 p_source_flag => p_source_flag,
1913 p_mandatory_yn => p_mandatory_yn,
1914 p_scn_id => p_scn_id,
1915 p_label => p_label,
1916 p_amendment_description => p_amendment_description,
1917 p_amendment_operation_code => p_amendment_operation_code,
1918 p_article_version_id => p_article_version_id,
1919 p_change_nonstd_yn => p_change_nonstd_yn,
1920 p_orig_system_reference_code => p_orig_system_reference_code,
1921 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1922 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1923 p_display_sequence => p_display_sequence,
1924 p_attribute_category => p_attribute_category,
1925 p_attribute1 => p_attribute1,
1926 p_attribute2 => p_attribute2,
1927 p_attribute3 => p_attribute3,
1928 p_attribute4 => p_attribute4,
1929 p_attribute5 => p_attribute5,
1930 p_attribute6 => p_attribute6,
1931 p_attribute7 => p_attribute7,
1932 p_attribute8 => p_attribute8,
1933 p_attribute9 => p_attribute9,
1934 p_attribute10 => p_attribute10,
1935 p_attribute11 => p_attribute11,
1936 p_attribute12 => p_attribute12,
1937 p_attribute13 => p_attribute13,
1938 p_attribute14 => p_attribute14,
1939 p_attribute15 => p_attribute15,
1940 p_print_text_yn => p_print_text_yn,
1941 p_summary_amend_operation_code=> p_summary_amend_operation_code,
1942 p_ref_article_id => p_ref_article_id,
1943 p_ref_article_version_id => p_ref_article_version_id
1944 );
1945 --- If any errors happen abort API
1946 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1947 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1948 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1949 RAISE OKC_API.G_EXCEPTION_ERROR;
1950 END IF;
1951
1952 --------------------------------------------
1953 -- Call the internal Insert_Row for each child record
1954 --------------------------------------------
1955 x_return_status := Insert_Row(
1956 p_id => x_id,
1957 p_sav_sae_id => p_sav_sae_id,
1958 p_cat_type => p_cat_type, --Bug 3341342
1959 p_document_type => p_document_type,
1960 p_document_id => p_document_id,
1961 p_cle_id => p_cle_id,
1962 p_source_flag => p_source_flag,
1963 p_mandatory_yn => p_mandatory_yn,
1964 p_scn_id => p_scn_id,
1965 p_label => p_label,
1966 p_amendment_description => p_amendment_description,
1967 p_amendment_operation_code => p_amendment_operation_code,
1968 p_article_version_id => p_article_version_id,
1969 p_change_nonstd_yn => p_change_nonstd_yn,
1970 p_orig_system_reference_code => p_orig_system_reference_code,
1971 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1972 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1973 p_display_sequence => p_display_sequence,
1974 p_attribute_category => p_attribute_category,
1975 p_attribute1 => p_attribute1,
1976 p_attribute2 => p_attribute2,
1977 p_attribute3 => p_attribute3,
1978 p_attribute4 => p_attribute4,
1979 p_attribute5 => p_attribute5,
1980 p_attribute6 => p_attribute6,
1981 p_attribute7 => p_attribute7,
1982 p_attribute8 => p_attribute8,
1983 p_attribute9 => p_attribute9,
1984 p_attribute10 => p_attribute10,
1985 p_attribute11 => p_attribute11,
1986 p_attribute12 => p_attribute12,
1987 p_attribute13 => p_attribute13,
1988 p_attribute14 => p_attribute14,
1989 p_attribute15 => p_attribute15,
1990 p_print_text_yn => p_print_text_yn,
1991 p_summary_amend_operation_code=> p_summary_amend_operation_code,
1992 p_ref_article_id => p_ref_article_id,
1993 p_ref_article_version_id => p_ref_article_version_id,
1994 p_object_version_number => l_object_version_number,
1995 p_created_by => l_created_by,
1996 p_creation_date => l_creation_date,
1997 p_last_updated_by => l_last_updated_by,
1998 p_last_update_login => l_last_update_login,
1999 p_last_update_date => l_last_update_date,
2000 p_last_amended_by => l_last_amended_by,
2001 p_last_amendment_date => l_last_amendment_date
2002 );
2003 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2004 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2005 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2006 RAISE OKC_API.G_EXCEPTION_ERROR;
2007 END IF;
2008
2009 IF (l_debug = 'Y') THEN
2010 Okc_Debug.Log('3500: Leaving Insert_Row', 2);
2011 END IF;
2012
2013 EXCEPTION
2014 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2015
2016 IF (l_debug = 'Y') THEN
2017 Okc_Debug.Log('3600: Leaving Insert_Row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2018 END IF;
2019
2020 x_return_status := G_RET_STS_ERROR;
2021
2022 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2023
2024 IF (l_debug = 'Y') THEN
2025 Okc_Debug.Log('3700: Leaving Insert_Row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2026 END IF;
2027
2028 x_return_status := G_RET_STS_UNEXP_ERROR;
2029 WHEN OTHERS THEN
2030
2031 IF (l_debug = 'Y') THEN
2032 Okc_Debug.Log('3800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
2033 END IF;
2034
2035 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2036 p_msg_name => G_UNEXPECTED_ERROR,
2037 p_token1 => G_SQLCODE_TOKEN,
2038 p_token1_value => sqlcode,
2039 p_token2 => G_SQLERRM_TOKEN,
2040 p_token2_value => sqlerrm);
2041 x_return_status := G_RET_STS_UNEXP_ERROR;
2042
2043 END Insert_Row;
2044 ---------------------------------------------------------------------------
2045 -- PROCEDURE Lock_Row
2046 ---------------------------------------------------------------------------
2047 -----------------------------------
2048 -- Lock_Row for:OKC_K_ARTICLES_B --
2049 -----------------------------------
2050 FUNCTION Lock_Row(
2051 p_id IN NUMBER,
2052 p_object_version_number IN NUMBER
2053 ) RETURN VARCHAR2 IS
2054
2055
2056 CURSOR lock_csr (cp_id NUMBER, cp_object_version_number NUMBER) IS
2057 SELECT object_version_number
2058 FROM OKC_K_ARTICLES_B
2059 WHERE ID = cp_id
2060 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
2061 FOR UPDATE OF object_version_number NOWAIT;
2062
2063 CURSOR lchk_csr (cp_id NUMBER) IS
2064 SELECT object_version_number
2065 FROM OKC_K_ARTICLES_B
2066 WHERE ID = cp_id;
2067
2068 l_return_status VARCHAR2(1);
2069
2070 l_object_version_number OKC_K_ARTICLES_B.OBJECT_VERSION_NUMBER%TYPE;
2071
2072 l_row_notfound BOOLEAN := FALSE;
2073 BEGIN
2074
2075 IF (l_debug = 'Y') THEN
2076 Okc_Debug.Log('3900: Entered Lock_Row', 2);
2077 END IF;
2078
2079
2080 BEGIN
2081
2082 OPEN lock_csr( p_id, p_object_version_number );
2083 FETCH lock_csr INTO l_object_version_number;
2084 l_row_notfound := lock_csr%NOTFOUND;
2085 CLOSE lock_csr;
2086
2087 EXCEPTION
2088 WHEN E_Resource_Busy THEN
2089
2090 IF (l_debug = 'Y') THEN
2091 Okc_Debug.Log('4000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
2092 END IF;
2093
2094 IF (lock_csr%ISOPEN) THEN
2095 CLOSE lock_csr;
2096 END IF;
2097 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
2098 RETURN( G_RET_STS_ERROR );
2099 END;
2100
2101 IF ( l_row_notfound ) THEN
2102 l_return_status := G_RET_STS_ERROR;
2103
2104 OPEN lchk_csr(p_id);
2105 FETCH lchk_csr INTO l_object_version_number;
2106 l_row_notfound := lchk_csr%NOTFOUND;
2107 CLOSE lchk_csr;
2108
2109 IF (l_row_notfound) THEN
2110 Okc_Api.Set_Message(G_APP_NAME,G_LOCK_RECORD_DELETED,
2111 'ENTITYNAME','OKC_K_ARTICLES_B',
2112 'PKEY',p_id,
2113 'OVN',p_object_version_number
2114 );
2115 ELSIF l_object_version_number > p_object_version_number THEN
2116 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
2117 ELSIF l_object_version_number = -1 THEN
2118 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2119 ELSE -- it can be the only above condition. It can happen after restore version
2120 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
2121 END IF;
2122 ELSE
2123 l_return_status := G_RET_STS_SUCCESS;
2124 END IF;
2125
2126 IF (l_debug = 'Y') THEN
2127 Okc_Debug.Log('4100: Leaving Lock_Row', 2);
2128 END IF;
2129
2130 RETURN( l_return_status );
2131
2132 EXCEPTION
2133 WHEN OTHERS THEN
2134
2135 IF (lock_csr%ISOPEN) THEN
2136 CLOSE lock_csr;
2137 END IF;
2138 IF (lchk_csr%ISOPEN) THEN
2139 CLOSE lchk_csr;
2140 END IF;
2141
2142 IF (l_debug = 'Y') THEN
2143 Okc_Debug.Log('4200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
2144 END IF;
2145
2146 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2147 p_msg_name => G_UNEXPECTED_ERROR,
2148 p_token1 => G_SQLCODE_TOKEN,
2149 p_token1_value => sqlcode,
2150 p_token2 => G_SQLERRM_TOKEN,
2151 p_token2_value => sqlerrm);
2152
2153 RETURN( G_RET_STS_UNEXP_ERROR );
2154 END Lock_Row;
2155 -----------------------------------
2156 -- Lock_Row for:OKC_K_ARTICLES_B --
2157 -----------------------------------
2158 PROCEDURE Lock_Row(
2159 x_return_status OUT NOCOPY VARCHAR2,
2160 p_id IN NUMBER,
2161 p_object_version_number IN NUMBER
2162 ) IS
2163 BEGIN
2164
2165 IF (l_debug = 'Y') THEN
2166 Okc_Debug.Log('4300: Entered Lock_Row', 2);
2167 END IF;
2168
2169 --------------------------------------------
2170 -- Call the LOCK_ROW for each child record
2171 --------------------------------------------
2172 x_return_status := Lock_Row(
2173 p_id => p_id,
2174 p_object_version_number => p_object_version_number
2175 );
2176 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2177 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2178 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2179 RAISE OKC_API.G_EXCEPTION_ERROR;
2180 END IF;
2181
2182 IF (l_debug = 'Y') THEN
2183 Okc_Debug.Log('4400: Leaving Lock_Row', 2);
2184 END IF;
2185
2186 EXCEPTION
2187 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2188
2189 IF (l_debug = 'Y') THEN
2190 Okc_Debug.Log('4500: Leaving Lock_Row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2191 END IF;
2192
2193 x_return_status := G_RET_STS_ERROR;
2194 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2195
2196 IF (l_debug = 'Y') THEN
2197 Okc_Debug.Log('4600: Leaving Lock_Row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2198 END IF;
2199
2200 x_return_status := G_RET_STS_UNEXP_ERROR;
2201 WHEN OTHERS THEN
2202
2203 IF (l_debug = 'Y') THEN
2204 Okc_Debug.Log('4700: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
2205 END IF;
2206
2207 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2208 p_msg_name => G_UNEXPECTED_ERROR,
2209 p_token1 => G_SQLCODE_TOKEN,
2210 p_token1_value => sqlcode,
2211 p_token2 => G_SQLERRM_TOKEN,
2212 p_token2_value => sqlerrm);
2213
2214 x_return_status := G_RET_STS_UNEXP_ERROR;
2215
2216 END Lock_Row;
2217 ---------------------------------------------------------------------------
2218 -- PROCEDURE Update_Row
2219 ---------------------------------------------------------------------------
2220 -------------------------------------
2221 -- Update_Row for:OKC_K_ARTICLES_B --
2222 -------------------------------------
2223 FUNCTION Update_Row(
2224 p_id IN NUMBER,
2225 p_sav_sae_id IN NUMBER,
2226 p_document_type IN VARCHAR2,
2227 p_document_id IN NUMBER,
2228 p_cle_id IN NUMBER,
2229 p_source_flag IN VARCHAR2,
2230 p_mandatory_yn IN VARCHAR2,
2231 p_scn_id IN NUMBER,
2232 p_label IN VARCHAR2,
2233 p_amendment_description IN VARCHAR2,
2234 p_amendment_operation_code IN VARCHAR2,
2235 p_article_version_id IN NUMBER,
2236 p_change_nonstd_yn IN VARCHAR2,
2237 p_orig_system_reference_code IN VARCHAR2,
2238 p_orig_system_reference_id1 IN NUMBER,
2239 p_orig_system_reference_id2 IN NUMBER,
2240 p_display_sequence IN NUMBER,
2241 p_attribute_category IN VARCHAR2,
2242 p_attribute1 IN VARCHAR2,
2243 p_attribute2 IN VARCHAR2,
2244 p_attribute3 IN VARCHAR2,
2245 p_attribute4 IN VARCHAR2,
2246 p_attribute5 IN VARCHAR2,
2247 p_attribute6 IN VARCHAR2,
2248 p_attribute7 IN VARCHAR2,
2249 p_attribute8 IN VARCHAR2,
2250 p_attribute9 IN VARCHAR2,
2251 p_attribute10 IN VARCHAR2,
2252 p_attribute11 IN VARCHAR2,
2253 p_attribute12 IN VARCHAR2,
2254 p_attribute13 IN VARCHAR2,
2255 p_attribute14 IN VARCHAR2,
2256 p_attribute15 IN VARCHAR2,
2257 p_print_text_yn IN VARCHAR2,
2258 p_summary_amend_operation_code IN VARCHAR2,
2259 p_ref_article_id IN NUMBER,
2260 p_ref_article_version_id IN NUMBER,
2261 p_object_version_number IN NUMBER,
2262 p_created_by IN NUMBER,
2263 p_creation_date IN DATE,
2264 p_last_updated_by IN NUMBER,
2265 p_last_update_login IN NUMBER,
2266 p_last_update_date IN DATE,
2267 p_last_amended_by IN NUMBER,
2268 p_last_amendment_date IN DATE
2269 ) RETURN VARCHAR2 IS
2270
2271 l_chr_id NUMBER;
2272
2273 BEGIN
2274
2275 IF (l_debug = 'Y') THEN
2276 Okc_Debug.Log('4800: Entered Update_Row', 2);
2277 END IF;
2278 IF p_document_type IN ('OKC_SELL','OKC_BUY','OKO','OKS','OKE_SELL','OKE_BUY','OKL') THEN
2279 l_chr_id := okc_terms_util_pvt.get_chr_id_for_doc_id(p_document_id);
2280 END IF;
2281
2282 UPDATE OKC_K_ARTICLES_B
2283 SET SAV_SAE_ID = p_sav_sae_id,
2284 DOCUMENT_TYPE = p_document_type,
2285 DOCUMENT_ID = p_document_id,
2286 CLE_ID = p_cle_id,
2287 SOURCE_FLAG = p_source_flag,
2288 MANDATORY_YN = p_mandatory_yn,
2289 SCN_ID = p_scn_id,
2290 CHR_ID = decode(p_cle_id,NULL,l_chr_id,NULL),
2291 DNZ_CHR_ID = l_chr_id,
2292 LABEL = p_label,
2293 AMENDMENT_DESCRIPTION = p_amendment_description,
2294 AMENDMENT_OPERATION_CODE = p_amendment_operation_code,
2295 ARTICLE_VERSION_ID = decode(p_document_type,OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE,NULL,p_article_version_id),
2296 CHANGE_NONSTD_YN = p_change_nonstd_yn,
2297 ORIG_SYSTEM_REFERENCE_CODE = p_orig_system_reference_code,
2298 ORIG_SYSTEM_REFERENCE_ID1 = p_orig_system_reference_id1,
2299 ORIG_SYSTEM_REFERENCE_ID2 = p_orig_system_reference_id2,
2300 DISPLAY_SEQUENCE = p_display_sequence,
2301 ATTRIBUTE_CATEGORY = p_attribute_category,
2302 ATTRIBUTE1 = p_attribute1,
2303 ATTRIBUTE2 = p_attribute2,
2304 ATTRIBUTE3 = p_attribute3,
2305 ATTRIBUTE4 = p_attribute4,
2306 ATTRIBUTE5 = p_attribute5,
2307 ATTRIBUTE6 = p_attribute6,
2308 ATTRIBUTE7 = p_attribute7,
2309 ATTRIBUTE8 = p_attribute8,
2310 ATTRIBUTE9 = p_attribute9,
2311 ATTRIBUTE10 = p_attribute10,
2312 ATTRIBUTE11 = p_attribute11,
2313 ATTRIBUTE12 = p_attribute12,
2314 ATTRIBUTE13 = p_attribute13,
2315 ATTRIBUTE14 = p_attribute14,
2316 ATTRIBUTE15 = p_attribute15,
2317 PRINT_TEXT_YN = p_print_text_yn,
2318 SUMMARY_AMEND_OPERATION_CODE= p_summary_amend_operation_code,
2319 REF_ARTICLE_ID = p_ref_article_id,
2320 REF_ARTICLE_VERSION_ID = p_ref_article_version_id,
2321 OBJECT_VERSION_NUMBER = p_object_version_number,
2322 LAST_UPDATED_BY = p_last_updated_by,
2323 LAST_UPDATE_LOGIN = p_last_update_login,
2324 LAST_UPDATE_DATE = p_last_update_date,
2325 LAST_AMENDED_BY = p_last_amended_by,
2326 LAST_AMENDMENT_DATE = p_last_amendment_date
2327 WHERE ID = p_id;
2328
2329 IF (l_debug = 'Y') THEN
2330 Okc_Debug.Log('4900: Leaving Update_Row', 2);
2331 END IF;
2332
2333 RETURN G_RET_STS_SUCCESS ;
2334
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337
2338 IF (l_debug = 'Y') THEN
2339 Okc_Debug.Log('5000: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
2340 END IF;
2341
2342 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2343 p_msg_name => G_UNEXPECTED_ERROR,
2344 p_token1 => G_SQLCODE_TOKEN,
2345 p_token1_value => sqlcode,
2346 p_token2 => G_SQLERRM_TOKEN,
2347 p_token2_value => sqlerrm);
2348
2349 RETURN G_RET_STS_UNEXP_ERROR ;
2350
2351 END Update_Row;
2352 -------------------------------------
2353 -- Update_Row for:OKC_K_ARTICLES_B --
2354 -------------------------------------
2355 PROCEDURE Update_Row(
2356 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2357 x_return_status OUT NOCOPY VARCHAR2,
2358 p_id IN NUMBER,
2359 p_sav_sae_id IN NUMBER,
2360 p_document_type IN VARCHAR2,
2361 p_document_id IN NUMBER,
2362 p_cle_id IN NUMBER,
2363 p_source_flag IN VARCHAR2,
2364 p_mandatory_yn IN VARCHAR2,
2365 p_scn_id IN NUMBER,
2366 p_label IN VARCHAR2,
2367 p_amendment_description IN VARCHAR2,
2368 p_amendment_operation_code IN VARCHAR2,
2369 p_article_version_id IN NUMBER,
2370 p_change_nonstd_yn IN VARCHAR2,
2371 p_orig_system_reference_code IN VARCHAR2,
2372 p_orig_system_reference_id1 IN NUMBER,
2373 p_orig_system_reference_id2 IN NUMBER,
2374 p_display_sequence IN NUMBER,
2375 p_attribute_category IN VARCHAR2,
2376 p_attribute1 IN VARCHAR2,
2377 p_attribute2 IN VARCHAR2,
2378 p_attribute3 IN VARCHAR2,
2379 p_attribute4 IN VARCHAR2,
2380 p_attribute5 IN VARCHAR2,
2381 p_attribute6 IN VARCHAR2,
2382 p_attribute7 IN VARCHAR2,
2383 p_attribute8 IN VARCHAR2,
2384 p_attribute9 IN VARCHAR2,
2385 p_attribute10 IN VARCHAR2,
2386 p_attribute11 IN VARCHAR2,
2387 p_attribute12 IN VARCHAR2,
2388 p_attribute13 IN VARCHAR2,
2389 p_attribute14 IN VARCHAR2,
2390 p_attribute15 IN VARCHAR2,
2391 p_print_text_yn IN VARCHAR2,
2392 p_summary_amend_operation_code IN VARCHAR2,
2393 p_ref_article_id IN NUMBER,
2394 p_ref_article_version_id IN NUMBER,
2395 p_object_version_number IN NUMBER,
2396 p_last_amended_by IN NUMBER,
2397 p_last_amendment_date IN DATE
2398
2399 ) IS
2400
2401 l_sav_sae_id OKC_K_ARTICLES_B.SAV_SAE_ID%TYPE;
2402 l_document_type OKC_K_ARTICLES_B.DOCUMENT_TYPE%TYPE;
2403 l_document_id OKC_K_ARTICLES_B.DOCUMENT_ID%TYPE;
2404 l_cle_id OKC_K_ARTICLES_B.cle_id%TYPE;
2405 l_source_flag OKC_K_ARTICLES_B.SOURCE_FLAG%TYPE;
2406 l_mandatory_yn OKC_K_ARTICLES_B.MANDATORY_YN%TYPE;
2407 l_scn_id OKC_K_ARTICLES_B.SCN_ID%TYPE;
2408 l_label OKC_K_ARTICLES_B.LABEL%TYPE;
2409 l_amendment_description OKC_K_ARTICLES_B.AMENDMENT_DESCRIPTION%TYPE;
2410 l_amendment_operation_code OKC_K_ARTICLES_B.AMENDMENT_OPERATION_CODE%TYPE;
2411 l_article_version_id OKC_K_ARTICLES_B.ARTICLE_VERSION_ID%TYPE;
2412 l_change_nonstd_yn OKC_K_ARTICLES_B.CHANGE_NONSTD_YN%TYPE;
2413 l_orig_system_reference_code OKC_K_ARTICLES_B.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
2414 l_orig_system_reference_id1 OKC_K_ARTICLES_B.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
2415 l_orig_system_reference_id2 OKC_K_ARTICLES_B.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
2416 l_display_sequence OKC_K_ARTICLES_B.DISPLAY_SEQUENCE%TYPE;
2417 l_attribute_category OKC_K_ARTICLES_B.ATTRIBUTE_CATEGORY%TYPE;
2418 l_attribute1 OKC_K_ARTICLES_B.ATTRIBUTE1%TYPE;
2419 l_attribute2 OKC_K_ARTICLES_B.ATTRIBUTE2%TYPE;
2420 l_attribute3 OKC_K_ARTICLES_B.ATTRIBUTE3%TYPE;
2421 l_attribute4 OKC_K_ARTICLES_B.ATTRIBUTE4%TYPE;
2422 l_attribute5 OKC_K_ARTICLES_B.ATTRIBUTE5%TYPE;
2423 l_attribute6 OKC_K_ARTICLES_B.ATTRIBUTE6%TYPE;
2424 l_attribute7 OKC_K_ARTICLES_B.ATTRIBUTE7%TYPE;
2425 l_attribute8 OKC_K_ARTICLES_B.ATTRIBUTE8%TYPE;
2426 l_attribute9 OKC_K_ARTICLES_B.ATTRIBUTE9%TYPE;
2427 l_attribute10 OKC_K_ARTICLES_B.ATTRIBUTE10%TYPE;
2428 l_attribute11 OKC_K_ARTICLES_B.ATTRIBUTE11%TYPE;
2429 l_attribute12 OKC_K_ARTICLES_B.ATTRIBUTE12%TYPE;
2430 l_attribute13 OKC_K_ARTICLES_B.ATTRIBUTE13%TYPE;
2431 l_attribute14 OKC_K_ARTICLES_B.ATTRIBUTE14%TYPE;
2432 l_attribute15 OKC_K_ARTICLES_B.ATTRIBUTE15%TYPE;
2433 l_print_text_yn OKC_K_ARTICLES_B.print_text_yn%TYPE;
2434 l_summary_amend_operation_code OKC_K_ARTICLES_B.summary_amend_operation_code%TYPE;
2435 l_ref_article_id OKC_K_ARTICLES_B.ref_article_id%TYPE;
2436 l_ref_article_version_id OKC_K_ARTICLES_B.ref_article_version_id%TYPE;
2437 l_object_version_number OKC_K_ARTICLES_B.OBJECT_VERSION_NUMBER%TYPE;
2438 l_created_by OKC_K_ARTICLES_B.CREATED_BY%TYPE;
2439 l_creation_date OKC_K_ARTICLES_B.CREATION_DATE%TYPE;
2440 l_last_updated_by OKC_K_ARTICLES_B.LAST_UPDATED_BY%TYPE;
2441 l_last_update_login OKC_K_ARTICLES_B.LAST_UPDATE_LOGIN%TYPE;
2442 l_last_update_date OKC_K_ARTICLES_B.LAST_UPDATE_DATE%TYPE;
2443 l_last_amended_by OKC_K_ARTICLES_B.LAST_AMENDED_BY%TYPE;
2444 l_last_amendment_date OKC_K_ARTICLES_B.LAST_AMENDMENT_DATE%TYPE;
2445
2446 BEGIN
2447
2448 IF (l_debug = 'Y') THEN
2449 Okc_Debug.Log('5100: Entered Update_Row', 2);
2450 Okc_Debug.Log('5200: Locking row', 2);
2451 END IF;
2452
2453 x_return_status := Lock_row(
2454 p_id => p_id,
2455 p_object_version_number => p_object_version_number
2456 );
2457 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2458 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2459 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2460 RAISE OKC_API.G_EXCEPTION_ERROR;
2461 END IF;
2462
2463 IF (l_debug = 'Y') THEN
2464 Okc_Debug.Log('5300: Setting attributes', 2);
2465 END IF;
2466
2467 x_return_status := Set_Attributes(
2468 p_id => p_id,
2469 p_sav_sae_id => p_sav_sae_id,
2470 p_document_type => p_document_type,
2471 p_document_id => p_document_id,
2472 p_cle_id => p_cle_id,
2473 p_source_flag => p_source_flag,
2474 p_mandatory_yn => p_mandatory_yn,
2475 p_scn_id => p_scn_id,
2476 p_label => p_label,
2477 p_amendment_description => p_amendment_description,
2478 p_amendment_operation_code => p_amendment_operation_code,
2479 p_article_version_id => p_article_version_id,
2480 p_change_nonstd_yn => p_change_nonstd_yn,
2481 p_orig_system_reference_code => p_orig_system_reference_code,
2482 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2483 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2484 p_display_sequence => p_display_sequence,
2485 p_attribute_category => p_attribute_category,
2486 p_attribute1 => p_attribute1,
2487 p_attribute2 => p_attribute2,
2488 p_attribute3 => p_attribute3,
2489 p_attribute4 => p_attribute4,
2490 p_attribute5 => p_attribute5,
2491 p_attribute6 => p_attribute6,
2492 p_attribute7 => p_attribute7,
2493 p_attribute8 => p_attribute8,
2494 p_attribute9 => p_attribute9,
2495 p_attribute10 => p_attribute10,
2496 p_attribute11 => p_attribute11,
2497 p_attribute12 => p_attribute12,
2498 p_attribute13 => p_attribute13,
2499 p_attribute14 => p_attribute14,
2500 p_attribute15 => p_attribute15,
2501 p_print_text_yn => p_print_text_yn,
2502 p_summary_amend_operation_code => p_summary_amend_operation_code,
2503 p_ref_article_id => p_ref_article_id,
2504 p_ref_article_version_id => p_ref_article_version_id,
2505 p_object_version_number => p_object_version_number,
2506 p_last_amended_by => p_last_amended_by,
2507 p_last_amendment_date => p_last_amendment_date,
2508 x_sav_sae_id => l_sav_sae_id,
2509 x_document_type => l_document_type,
2510 x_document_id => l_document_id,
2511 x_cle_id => l_cle_id,
2512 x_source_flag => l_source_flag,
2513 x_mandatory_yn => l_mandatory_yn,
2514 x_scn_id => l_scn_id,
2515 x_label => l_label,
2516 x_amendment_description => l_amendment_description,
2517 x_amendment_operation_code => l_amendment_operation_code,
2518 x_article_version_id => l_article_version_id,
2519 x_change_nonstd_yn => l_change_nonstd_yn,
2520 x_orig_system_reference_code => l_orig_system_reference_code,
2521 x_orig_system_reference_id1 => l_orig_system_reference_id1,
2522 x_orig_system_reference_id2 => l_orig_system_reference_id2,
2523 x_display_sequence => l_display_sequence,
2524 x_attribute_category => l_attribute_category,
2525 x_attribute1 => l_attribute1,
2526 x_attribute2 => l_attribute2,
2527 x_attribute3 => l_attribute3,
2528 x_attribute4 => l_attribute4,
2529 x_attribute5 => l_attribute5,
2530 x_attribute6 => l_attribute6,
2531 x_attribute7 => l_attribute7,
2532 x_attribute8 => l_attribute8,
2533 x_attribute9 => l_attribute9,
2534 x_attribute10 => l_attribute10,
2535 x_attribute11 => l_attribute11,
2536 x_attribute12 => l_attribute12,
2537 x_attribute13 => l_attribute13,
2538 x_attribute14 => l_attribute14,
2539 x_attribute15 => l_attribute15,
2540 x_print_text_yn => l_print_text_yn,
2541 x_summary_amend_operation_code => l_summary_amend_operation_code,
2542 x_object_version_number => l_object_version_number,
2543 x_ref_article_id => l_ref_article_id,
2544 x_ref_article_version_id => l_ref_article_version_id,
2545 x_last_amended_by => l_last_amended_by,
2546 x_last_amendment_date => l_last_amendment_date
2547 );
2548
2549 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2550 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2551 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2552 RAISE OKC_API.G_EXCEPTION_ERROR;
2553 END IF;
2554
2555 IF (l_debug = 'Y') THEN
2556 Okc_Debug.Log('5400: Record Validation', 2);
2557 END IF;
2558
2559 --- Validate all non-missing attributes
2560 x_return_status := Validate_Record(
2561 p_validation_level => p_validation_level,
2562 p_id => p_id,
2563 p_sav_sae_id => l_sav_sae_id,
2564 p_document_type => l_document_type,
2565 p_document_id => l_document_id,
2566 p_cle_id => l_cle_id,
2567 p_source_flag => l_source_flag,
2568 p_mandatory_yn => l_mandatory_yn,
2569 p_scn_id => l_scn_id,
2570 p_label => l_label,
2571 p_amendment_description => l_amendment_description,
2572 p_amendment_operation_code => l_amendment_operation_code,
2573 p_article_version_id => l_article_version_id,
2574 p_change_nonstd_yn => l_change_nonstd_yn,
2575 p_orig_system_reference_code => l_orig_system_reference_code,
2576 p_orig_system_reference_id1 => l_orig_system_reference_id1,
2577 p_orig_system_reference_id2 => l_orig_system_reference_id2,
2578 p_display_sequence => l_display_sequence,
2579 p_attribute_category => l_attribute_category,
2580 p_attribute1 => l_attribute1,
2581 p_attribute2 => l_attribute2,
2582 p_attribute3 => l_attribute3,
2583 p_attribute4 => l_attribute4,
2584 p_attribute5 => l_attribute5,
2585 p_attribute6 => l_attribute6,
2586 p_attribute7 => l_attribute7,
2587 p_attribute8 => l_attribute8,
2588 p_attribute9 => l_attribute9,
2589 p_attribute10 => l_attribute10,
2590 p_attribute11 => l_attribute11,
2591 p_attribute12 => l_attribute12,
2592 p_attribute13 => l_attribute13,
2593 p_attribute14 => l_attribute14,
2594 p_attribute15 => l_attribute15,
2595 p_print_text_yn => l_print_text_yn,
2596 p_summary_amend_operation_code => l_summary_amend_operation_code,
2597 p_ref_article_id => l_ref_article_id,
2598 p_ref_article_version_id => l_ref_article_version_id
2599 );
2600 --- If any errors happen abort API
2601 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2602 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2603 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2604 RAISE OKC_API.G_EXCEPTION_ERROR;
2605 END IF;
2606
2607 IF (l_debug = 'Y') THEN
2608 Okc_Debug.Log('5500: Filling WHO columns', 2);
2609 END IF;
2610
2611 -- Filling who columns
2612 l_last_update_date := SYSDATE;
2613 l_last_updated_by := FND_GLOBAL.USER_ID;
2614 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2615
2616 -- Object version increment
2617 IF Nvl(l_object_version_number, 0) >= 0 THEN
2618 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
2619 END IF;
2620
2621 IF (l_debug = 'Y') THEN
2622 Okc_Debug.Log('5600: Updating Row', 2);
2623 END IF;
2624
2625 --------------------------------------------
2626 -- Call the Update_Row for each child record
2627 --------------------------------------------
2628 x_return_status := Update_Row(
2629 p_id => p_id,
2630 p_sav_sae_id => l_sav_sae_id,
2631 p_document_type => l_document_type,
2632 p_document_id => l_document_id,
2633 p_cle_id => l_cle_id,
2634 p_source_flag => l_source_flag,
2635 p_mandatory_yn => l_mandatory_yn,
2636 p_scn_id => l_scn_id,
2637 p_label => l_label,
2638 p_amendment_description => l_amendment_description,
2639 p_amendment_operation_code => l_amendment_operation_code,
2640 p_article_version_id => l_article_version_id,
2641 p_change_nonstd_yn => l_change_nonstd_yn,
2642 p_orig_system_reference_code => l_orig_system_reference_code,
2643 p_orig_system_reference_id1 => l_orig_system_reference_id1,
2644 p_orig_system_reference_id2 => l_orig_system_reference_id2,
2645 p_display_sequence => l_display_sequence,
2646 p_attribute_category => l_attribute_category,
2647 p_attribute1 => l_attribute1,
2648 p_attribute2 => l_attribute2,
2649 p_attribute3 => l_attribute3,
2650 p_attribute4 => l_attribute4,
2651 p_attribute5 => l_attribute5,
2652 p_attribute6 => l_attribute6,
2653 p_attribute7 => l_attribute7,
2654 p_attribute8 => l_attribute8,
2655 p_attribute9 => l_attribute9,
2656 p_attribute10 => l_attribute10,
2657 p_attribute11 => l_attribute11,
2658 p_attribute12 => l_attribute12,
2659 p_attribute13 => l_attribute13,
2660 p_attribute14 => l_attribute14,
2661 p_attribute15 => l_attribute15,
2662 p_print_text_yn => l_print_text_yn,
2663 p_summary_amend_operation_code => l_summary_amend_operation_code,
2664 p_ref_article_id => l_ref_article_id,
2665 p_ref_article_version_id => l_ref_article_version_id,
2666 p_object_version_number => l_object_version_number,
2667 p_created_by => l_created_by,
2668 p_creation_date => l_creation_date,
2669 p_last_updated_by => l_last_updated_by,
2670 p_last_update_login => l_last_update_login,
2671 p_last_update_date => l_last_update_date,
2672 p_last_amended_by => l_last_amended_by,
2673 p_last_amendment_date => l_last_amendment_date
2674 );
2675 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2676 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2677 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2678 RAISE OKC_API.G_EXCEPTION_ERROR;
2679 END IF;
2680
2681 IF (l_debug = 'Y') THEN
2682 Okc_Debug.Log('5700: Leaving Update_Row', 2);
2683 END IF;
2684
2685 EXCEPTION
2686 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2687
2688 IF (l_debug = 'Y') THEN
2689 Okc_Debug.Log('5800: Leaving Update_Row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2690 END IF;
2691
2692 x_return_status := G_RET_STS_ERROR;
2693 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2694
2695 IF (l_debug = 'Y') THEN
2696 Okc_Debug.Log('5900: Leaving Update_Row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2697 END IF;
2698
2699 x_return_status := G_RET_STS_UNEXP_ERROR;
2700 WHEN OTHERS THEN
2701
2702 IF (l_debug = 'Y') THEN
2703 Okc_Debug.Log('6000: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
2704 END IF;
2705
2706 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2707 p_msg_name => G_UNEXPECTED_ERROR,
2708 p_token1 => G_SQLCODE_TOKEN,
2709 p_token1_value => sqlcode,
2710 p_token2 => G_SQLERRM_TOKEN,
2711 p_token2_value => sqlerrm);
2712
2713 x_return_status := G_RET_STS_UNEXP_ERROR;
2714
2715 END Update_Row;
2716
2717 ---------------------------------------------------------------------------
2718 -- PROCEDURE Delete_Row
2719 ---------------------------------------------------------------------------
2720 -------------------------------------
2721 -- Delete_Row for:OKC_K_ARTICLES_B --
2722 -------------------------------------
2723 FUNCTION Delete_Row(
2724 p_id IN NUMBER
2725 ) RETURN VARCHAR2 IS
2726
2727 BEGIN
2728
2729 IF (l_debug = 'Y') THEN
2730 Okc_Debug.Log('6100: Entered Delete_Row', 2);
2731 END IF;
2732
2733 DELETE FROM OKC_K_ARTICLES_B WHERE ID = p_ID;
2734
2735 IF (l_debug = 'Y') THEN
2736 Okc_Debug.Log('6200: Leaving Delete_Row', 2);
2737 END IF;
2738
2739 RETURN( G_RET_STS_SUCCESS );
2740
2741 EXCEPTION
2742 WHEN OTHERS THEN
2743
2744 IF (l_debug = 'Y') THEN
2745 Okc_Debug.Log('6300: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
2746 END IF;
2747
2748 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2749 p_msg_name => G_UNEXPECTED_ERROR,
2750 p_token1 => G_SQLCODE_TOKEN,
2751 p_token1_value => sqlcode,
2752 p_token2 => G_SQLERRM_TOKEN,
2753 p_token2_value => sqlerrm);
2754
2755 RETURN( G_RET_STS_UNEXP_ERROR );
2756
2757 END Delete_Row;
2758 -------------------------------------
2759 -- Delete_Row for:OKC_K_ARTICLES_B --
2760 -------------------------------------
2761 PROCEDURE Delete_Row(
2762 x_return_status OUT NOCOPY VARCHAR2,
2763 p_id IN NUMBER,
2764 p_object_version_number IN NUMBER
2765 ) IS
2766
2767 l_standard_yn VARCHAR2(1) := 'Y';
2768 l_article_id NUMBER;
2769 l_article_version_id NUMBER;
2770 l_non_std_exists VARCHAR2(1) := 'N';
2771 l_return_status VARCHAR2(30);
2772 l_msg_count NUMBER;
2773 l_msg_data VARCHAR2(2000);
2774
2775 CURSOR l_art_csr (cp_id IN NUMBER) IS
2776 SELECT art.standard_yn,
2777 kart.sav_sae_id,
2778 kart.article_version_id
2779 FROM okc_k_articles_b kart,
2780 okc_articles_all art
2781 WHERE kart.id = cp_id
2782 AND art.article_id = kart.sav_sae_id;
2783
2784 CURSOR l_non_std_exists_csr(cp_article_version_id IN NUMBER) IS
2785 SELECT 'Y'
2786 FROM okc_k_articles_b
2787 WHERE article_version_id = cp_article_version_id
2788 UNION ALL
2789 SELECT 'Y'
2790 FROM OKC_K_ARTICLES_BH
2791 WHERE article_version_id = cp_article_version_id;
2792
2793 BEGIN
2794
2795 IF (l_debug = 'Y') THEN
2796 Okc_Debug.Log('6400: Entered Delete_Row', 2);
2797 END IF;
2798
2799 OPEN l_art_csr(p_id);
2800 FETCH l_art_csr INTO l_standard_yn,l_article_id,l_article_version_id;
2801 CLOSE l_art_csr;
2802
2803 x_return_status := Lock_row(
2804 p_id => p_id,
2805 p_object_version_number => p_object_version_number
2806 );
2807 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2808 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2809 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2810 RAISE OKC_API.G_EXCEPTION_ERROR;
2811 END IF;
2812
2813 x_return_status := Delete_Row( p_id => p_id );
2814 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2815 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2816 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2817 RAISE OKC_API.G_EXCEPTION_ERROR;
2818 END IF;
2819
2820 IF l_standard_yn = 'N' THEN
2821 OPEN l_non_std_exists_csr(l_article_version_id);
2822 FETCH l_non_std_exists_csr INTO l_non_std_exists;
2823 IF l_non_std_exists_csr%NOTFOUND THEN
2824 OKC_ARTICLES_GRP.delete_article(
2825 p_api_version => 1,
2826 p_init_msg_list => FND_API.G_FALSE,
2827
2828 x_return_status => l_return_status,
2829 x_msg_count => l_msg_count,
2830 x_msg_data => l_msg_data,
2831
2832 p_article_id => l_article_id,
2833 p_article_version_id => l_article_version_id);
2834
2835
2836 END IF;
2837 CLOSE l_non_std_exists_csr;
2838
2839 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2840 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2841 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2842 RAISE OKC_API.G_EXCEPTION_ERROR;
2843 END IF;
2844
2845 END IF;
2846
2847 IF (l_debug = 'Y') THEN
2848 Okc_Debug.Log('6500: Leaving Delete_Row', 2);
2849 END IF;
2850
2851 EXCEPTION
2852 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2853
2854 IF (l_debug = 'Y') THEN
2855 Okc_Debug.Log('6600: Leaving Delete_Row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2856 END IF;
2857
2858 x_return_status := G_RET_STS_ERROR;
2859
2860 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2861
2862 IF (l_debug = 'Y') THEN
2863 Okc_Debug.Log('6700: Leaving Delete_Row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2864 END IF;
2865
2866 x_return_status := G_RET_STS_UNEXP_ERROR;
2867
2868 WHEN OTHERS THEN
2869
2870 IF (l_debug = 'Y') THEN
2871 Okc_Debug.Log('6800: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
2872 END IF;
2873
2874 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2875 p_msg_name => G_UNEXPECTED_ERROR,
2876 p_token1 => G_SQLCODE_TOKEN,
2877 p_token1_value => sqlcode,
2878 p_token2 => G_SQLERRM_TOKEN,
2879 p_token2_value => sqlerrm);
2880
2881 x_return_status := G_RET_STS_UNEXP_ERROR;
2882
2883 END Delete_Row;
2884
2885 PROCEDURE delete_set(
2886 x_return_status OUT NOCOPY VARCHAR2,
2887 p_scn_id IN NUMBER
2888 ) IS
2889 CURSOR lock_csr IS
2890 SELECT rowid
2891 FROM OKC_K_ARTICLES_B
2892 WHERE SCN_ID=P_SCN_ID
2893 FOR UPDATE NOWAIT;
2894 BEGIN
2895 IF (l_debug = 'Y') THEN
2896 Okc_Debug.Log('9700: Entered Delete_Set', 2);
2897 Okc_Debug.Log('9710: Locking Record', 2);
2898 END IF;
2899 -- making OPEN/CLOSE cursor to lock records
2900 OPEN lock_csr;
2901 CLOSE lock_csr;
2902
2903 DELETE FROM OKC_K_ARTICLES_B
2904 WHERE SCN_ID=P_SCN_ID;
2905
2906 IF (l_debug = 'Y') THEN
2907 Okc_Debug.Log('11000: Leaving Delete_set', 2);
2908 END IF;
2909
2910 EXCEPTION
2911 WHEN E_Resource_Busy THEN
2912 IF (l_debug = 'Y') THEN
2913 Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
2914 END IF;
2915
2916 IF (lock_csr%ISOPEN) THEN
2917 CLOSE lock_csr;
2918 END IF;
2919 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2920 x_return_status := G_RET_STS_ERROR ;
2921
2922 WHEN FND_API.G_EXC_ERROR THEN
2923 IF (l_debug = 'Y') THEN
2924 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
2925 END IF;
2926
2927 IF (lock_csr%ISOPEN) THEN
2928 CLOSE lock_csr;
2929 END IF;
2930 x_return_status := G_RET_STS_ERROR;
2931
2932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2933 IF (l_debug = 'Y') THEN
2934 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2935 END IF;
2936
2937 IF (lock_csr%ISOPEN) THEN
2938 CLOSE lock_csr;
2939 END IF;
2940 x_return_status := G_RET_STS_UNEXP_ERROR;
2941
2942 WHEN OTHERS THEN
2943 IF (l_debug = 'Y') THEN
2944 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
2945 END IF;
2946
2947 IF (lock_csr%ISOPEN) THEN
2948 CLOSE lock_csr;
2949 END IF;
2950 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2951 p_msg_name => G_UNEXPECTED_ERROR,
2952 p_token1 => G_SQLCODE_TOKEN,
2953 p_token1_value => sqlcode,
2954 p_token2 => G_SQLERRM_TOKEN,
2955 p_token2_value => sqlerrm);
2956 x_return_status := G_RET_STS_UNEXP_ERROR;
2957
2958 END delete_set;
2959
2960 PROCEDURE delete_set(
2961 x_return_status OUT NOCOPY VARCHAR2,
2962 p_doc_type IN VARCHAR2,
2963 p_doc_id IN NUMBER
2964 ) IS
2965 CURSOR lock_csr IS
2966 SELECT rowid
2967 FROM OKC_K_ARTICLES_B
2968 WHERE DOCUMENT_TYPE=p_doc_type
2969 AND DOCUMENT_ID = p_doc_id
2970 FOR UPDATE NOWAIT;
2971
2972 BEGIN
2973 IF (l_debug = 'Y') THEN
2974 Okc_Debug.Log('9700: Entered Delete_Set', 2);
2975 Okc_Debug.Log('9710: Locking Records', 2);
2976 END IF;
2977
2978
2979 -- making OPEN/CLOSE cursor to lock records
2980 OPEN lock_csr;
2981 CLOSE lock_csr;
2982
2983 DELETE FROM OKC_K_ARTICLES_B
2984 WHERE DOCUMENT_TYPE=p_doc_type
2985 AND DOCUMENT_ID = p_doc_id;
2986
2987 IF (l_debug = 'Y') THEN
2988 Okc_Debug.Log('11000: Leaving Delete_set', 2);
2989 END IF;
2990
2991 EXCEPTION
2992 WHEN E_Resource_Busy THEN
2993 IF (l_debug = 'Y') THEN
2994 Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
2995 END IF;
2996
2997 IF (lock_csr%ISOPEN) THEN
2998 CLOSE lock_csr;
2999 END IF;
3000 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
3001 x_return_status := G_RET_STS_ERROR ;
3002
3003 WHEN FND_API.G_EXC_ERROR THEN
3004 IF (l_debug = 'Y') THEN
3005 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
3006 END IF;
3007
3008 IF (lock_csr%ISOPEN) THEN
3009 CLOSE lock_csr;
3010 END IF;
3011 x_return_status := G_RET_STS_ERROR;
3012
3013 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3014 IF (l_debug = 'Y') THEN
3015 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
3016 END IF;
3017
3018 IF (lock_csr%ISOPEN) THEN
3019 CLOSE lock_csr;
3020 END IF;
3021 x_return_status := G_RET_STS_UNEXP_ERROR;
3022
3023 WHEN OTHERS THEN
3024 IF (l_debug = 'Y') THEN
3025 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
3026 END IF;
3027
3028 IF (lock_csr%ISOPEN) THEN
3029 CLOSE lock_csr;
3030 END IF;
3031 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3032 p_msg_name => G_UNEXPECTED_ERROR,
3033 p_token1 => G_SQLCODE_TOKEN,
3034 p_token1_value => sqlcode,
3035 p_token2 => G_SQLERRM_TOKEN,
3036 p_token2_value => sqlerrm);
3037 x_return_status := G_RET_STS_UNEXP_ERROR;
3038
3039 END delete_set;
3040
3041 --This function is to be called from versioning API OKC_VERSION_PVT
3042 -- Location: Base Table API
3043 FUNCTION Create_Version(
3044 p_doc_type IN VARCHAR2,
3045 p_doc_id IN NUMBER,
3046 p_major_version IN NUMBER
3047 ) RETURN VARCHAR2 IS
3048
3049 l_article_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE;
3050 l_article_id OKC_ARTICLES_ALL.ARTICLE_ID%TYPE;
3051 l_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
3052 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
3053 l_msg_count NUMBER;
3054 l_msg_data VARCHAR2(4000);
3055
3056 CURSOR non_std_csr IS
3057 SELECT KART.ID,
3058 KART.SAV_SAE_ID,
3059 KART.ARTICLE_VERSION_ID
3060 FROM OKC_K_ARTICLES_B KART,
3061 OKC_ARTICLES_ALL ART
3062 WHERE KART.document_type = p_doc_type
3063 AND KART.document_id = p_doc_id
3064 AND KART.SAV_SAE_ID = ART.ARTICLE_ID
3065 AND ART.STANDARD_YN = 'N';
3066
3067 BEGIN
3068
3069 IF (l_debug = 'Y') THEN
3070 Okc_Debug.Log('6900: Entered create_version', 2);
3071 END IF;
3072
3073 -----------------------------------------
3074 -- Saving Base Table
3075 -----------------------------------------
3076 INSERT INTO OKC_K_ARTICLES_BH (
3077 major_version,
3078 ID,
3079 SAV_SAE_ID,
3080 SAV_SAV_RELEASE,
3081 SBT_CODE,
3082 CAT_TYPE,
3083 CHR_ID,
3084 CAT_ID,
3085 DNZ_CHR_ID,
3086 FULLTEXT_YN,
3087 DOCUMENT_TYPE,
3088 DOCUMENT_ID,
3089 CLE_ID,
3090 SOURCE_FLAG,
3091 MANDATORY_YN,
3092 SCN_ID,
3093 LABEL,
3094 AMENDMENT_DESCRIPTION,
3095 AMENDMENT_OPERATION_CODE,
3096 ARTICLE_VERSION_ID,
3097 CHANGE_NONSTD_YN,
3098 ORIG_SYSTEM_REFERENCE_CODE,
3099 ORIG_SYSTEM_REFERENCE_ID1,
3100 ORIG_SYSTEM_REFERENCE_ID2,
3101 DISPLAY_SEQUENCE,
3102 ATTRIBUTE_CATEGORY,
3103 ATTRIBUTE1,
3104 ATTRIBUTE2,
3105 ATTRIBUTE3,
3106 ATTRIBUTE4,
3107 ATTRIBUTE5,
3108 ATTRIBUTE6,
3109 ATTRIBUTE7,
3110 ATTRIBUTE8,
3111 ATTRIBUTE9,
3112 ATTRIBUTE10,
3113 ATTRIBUTE11,
3114 ATTRIBUTE12,
3115 ATTRIBUTE13,
3116 ATTRIBUTE14,
3117 ATTRIBUTE15,
3118 PRINT_TEXT_YN,
3119 SUMMARY_AMEND_OPERATION_CODE,
3120 REF_ARTICLE_ID,
3121 REF_ARTICLE_VERSION_ID,
3122 OBJECT_VERSION_NUMBER,
3123 CREATED_BY,
3124 CREATION_DATE,
3125 LAST_UPDATED_BY,
3126 LAST_UPDATE_LOGIN,
3127 LAST_UPDATE_DATE,
3128 ORIG_ARTICLE_ID,
3129 LAST_AMENDED_BY,
3130 LAST_AMENDMENT_DATE)
3131 SELECT
3132 p_major_version,
3133 ID,
3134 SAV_SAE_ID,
3135 SAV_SAV_RELEASE,
3136 SBT_CODE,
3137 CAT_TYPE,
3138 CHR_ID,
3139 CAT_ID,
3140 DNZ_CHR_ID,
3141 FULLTEXT_YN,
3142 DOCUMENT_TYPE,
3143 DOCUMENT_ID,
3144 CLE_ID,
3145 SOURCE_FLAG,
3146 MANDATORY_YN,
3147 SCN_ID,
3148 LABEL,
3149 AMENDMENT_DESCRIPTION,
3150 AMENDMENT_OPERATION_CODE,
3151 ARTICLE_VERSION_ID,
3152 CHANGE_NONSTD_YN,
3153 ORIG_SYSTEM_REFERENCE_CODE,
3154 ORIG_SYSTEM_REFERENCE_ID1,
3155 ORIG_SYSTEM_REFERENCE_ID2,
3156 DISPLAY_SEQUENCE,
3157 ATTRIBUTE_CATEGORY,
3158 ATTRIBUTE1,
3159 ATTRIBUTE2,
3160 ATTRIBUTE3,
3161 ATTRIBUTE4,
3162 ATTRIBUTE5,
3163 ATTRIBUTE6,
3164 ATTRIBUTE7,
3165 ATTRIBUTE8,
3166 ATTRIBUTE9,
3167 ATTRIBUTE10,
3168 ATTRIBUTE11,
3169 ATTRIBUTE12,
3170 ATTRIBUTE13,
3171 ATTRIBUTE14,
3172 ATTRIBUTE15,
3173 PRINT_TEXT_YN,
3174 SUMMARY_AMEND_OPERATION_CODE,
3175 REF_ARTICLE_ID,
3176 REF_ARTICLE_VERSION_ID,
3177 OBJECT_VERSION_NUMBER,
3178 CREATED_BY,
3179 CREATION_DATE,
3180 LAST_UPDATED_BY,
3181 LAST_UPDATE_LOGIN,
3182 LAST_UPDATE_DATE,
3183 ORIG_ARTICLE_ID,
3184 LAST_AMENDED_BY,
3185 LAST_AMENDMENT_DATE
3186 FROM OKC_K_ARTICLES_B
3187 WHERE document_type = p_doc_type
3188 AND document_id = p_doc_id;
3189
3190 IF (l_debug = 'Y') THEN
3191 Okc_Debug.Log('6910: Before Opening Non_std_csr', 2);
3192 END IF;
3193
3194 FOR rec in non_std_csr LOOP
3195
3196 IF (l_debug = 'Y') THEN
3197 Okc_Debug.Log('6920: Before calling OKC_ARTICLES_GRP.copy_article()', 2);
3198 END IF;
3199 OKC_ARTICLES_GRP.copy_article( p_api_version => 1,
3200 p_init_msg_list => FND_API.G_FALSE,
3201 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3202 p_commit => FND_API.G_FALSE,
3203 p_article_version_id => rec.article_version_id,
3204 p_new_article_title => NULL,
3205 p_create_standard_yn => 'N',
3206 x_article_version_id => l_article_version_id,
3207 x_article_id => l_article_id,
3208 x_article_number => l_article_number,
3209 x_return_status => l_return_status,
3210 x_msg_count => l_msg_count,
3211 x_msg_data => l_msg_data);
3212
3213 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3215 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3216 RAISE FND_API.G_EXC_ERROR;
3217 END IF;
3218
3219 IF (l_debug = 'Y') THEN
3220 Okc_Debug.Log('6920: Before updating new Non-Std in the OKC_K_ARTICLES_B', 2);
3221 END IF;
3222
3223
3224 -----------------------------------------
3225 -- Updating OKC_K_ARTICLES_B
3226 -----------------------------------------
3227 UPDATE OKC_K_ARTICLES_B
3228 SET SAV_SAE_ID = l_article_id,
3229 ARTICLE_VERSION_ID = l_article_version_id
3230 WHERE ID = rec.id;
3231
3232 IF (l_debug = 'Y') THEN
3233 Okc_Debug.Log('6930: After Updating Non-Std in OKC_K_ARTICLES_B', 2);
3234 END IF;
3235
3236 END LOOP;
3237
3238 IF (l_debug = 'Y') THEN
3239 Okc_Debug.Log('7000: Leaving create_version', 2);
3240 END IF;
3241
3242 RETURN( G_RET_STS_SUCCESS );
3243
3244 EXCEPTION
3245
3246 WHEN FND_API.G_EXC_ERROR THEN
3247 IF (l_debug = 'Y') THEN
3248 Okc_Debug.Log('7100: Leaving Create_Version:FND_API.G_EXC_ERROR Exception', 2);
3249 END IF;
3250
3251 IF (non_std_csr%ISOPEN) THEN
3252 CLOSE non_std_csr;
3253 END IF;
3254 RETURN G_RET_STS_ERROR;
3255
3256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3257 IF (l_debug = 'Y') THEN
3258 Okc_Debug.Log('7200: Leaving Create_Version:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
3259 END IF;
3260
3261 IF (non_std_csr%ISOPEN) THEN
3262 CLOSE non_std_csr;
3263 END IF;
3264 RETURN G_RET_STS_UNEXP_ERROR;
3265
3266 WHEN OTHERS THEN
3267
3268 IF (l_debug = 'Y') THEN
3269 Okc_Debug.Log('7300: Leaving create_version because of EXCEPTION: '||sqlerrm, 2);
3270 END IF;
3271
3272 IF (non_std_csr%ISOPEN) THEN
3273 CLOSE non_std_csr;
3274 END IF;
3275
3276 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3277 p_msg_name => G_UNEXPECTED_ERROR,
3278 p_token1 => G_SQLCODE_TOKEN,
3279 p_token1_value => sqlcode,
3280 p_token2 => G_SQLERRM_TOKEN,
3281 p_token2_value => sqlerrm);
3282
3283 RETURN G_RET_STS_UNEXP_ERROR ;
3284
3285 END create_version;
3286
3287 --This Function is called from Versioning API OKC_VERSION_PVT
3288 -- Location:Base Table API
3289
3290 FUNCTION Restore_Version(
3291 p_doc_type IN VARCHAR2,
3292 p_doc_id IN NUMBER,
3293 p_major_version IN NUMBER
3294 ) RETURN VARCHAR2 IS
3295
3296 BEGIN
3297
3298 IF (l_debug = 'Y') THEN
3299 Okc_Debug.Log('7200: Entered restore_version', 2);
3300 END IF;
3301
3302 -----------------------------------------
3303 -- Restoring Base Table
3304 -----------------------------------------
3305 INSERT INTO OKC_K_ARTICLES_B (
3306 ID,
3307 SAV_SAE_ID,
3308 SAV_SAV_RELEASE,
3309 SBT_CODE,
3310 CAT_TYPE,
3311 CHR_ID,
3312 CLE_ID,
3313 CAT_ID,
3314 DNZ_CHR_ID,
3315 FULLTEXT_YN,
3316 DOCUMENT_TYPE,
3317 DOCUMENT_ID,
3318 SOURCE_FLAG,
3319 MANDATORY_YN,
3320 SCN_ID,
3321 LABEL,
3322 AMENDMENT_DESCRIPTION,
3323 AMENDMENT_OPERATION_CODE,
3324 ARTICLE_VERSION_ID,
3325 CHANGE_NONSTD_YN,
3326 ORIG_SYSTEM_REFERENCE_CODE,
3327 ORIG_SYSTEM_REFERENCE_ID1,
3328 ORIG_SYSTEM_REFERENCE_ID2,
3329 DISPLAY_SEQUENCE,
3330 ATTRIBUTE_CATEGORY,
3331 ATTRIBUTE1,
3332 ATTRIBUTE2,
3333 ATTRIBUTE3,
3334 ATTRIBUTE4,
3335 ATTRIBUTE5,
3336 ATTRIBUTE6,
3337 ATTRIBUTE7,
3338 ATTRIBUTE8,
3339 ATTRIBUTE9,
3340 ATTRIBUTE10,
3341 ATTRIBUTE11,
3342 ATTRIBUTE12,
3343 ATTRIBUTE13,
3344 ATTRIBUTE14,
3345 ATTRIBUTE15,
3346 PRINT_TEXT_YN,
3347 SUMMARY_AMEND_OPERATION_CODE,
3348 REF_ARTICLE_ID,
3349 REF_ARTICLE_VERSION_ID,
3350 OBJECT_VERSION_NUMBER,
3351 CREATED_BY,
3352 CREATION_DATE,
3353 LAST_UPDATED_BY,
3354 LAST_UPDATE_LOGIN,
3355 LAST_UPDATE_DATE,
3356 ORIG_ARTICLE_ID,
3357 LAST_AMENDED_BY,
3358 LAST_AMENDMENT_DATE)
3359 SELECT
3360 ID,
3361 SAV_SAE_ID,
3362 SAV_SAV_RELEASE,
3363 SBT_CODE,
3364 CAT_TYPE,
3365 CHR_ID,
3366 CLE_ID,
3367 CAT_ID,
3368 DNZ_CHR_ID,
3369 FULLTEXT_YN,
3370 DOCUMENT_TYPE,
3371 DOCUMENT_ID,
3372 SOURCE_FLAG,
3373 MANDATORY_YN,
3374 SCN_ID,
3375 LABEL,
3376 AMENDMENT_DESCRIPTION,
3377 AMENDMENT_OPERATION_CODE,
3378 ARTICLE_VERSION_ID,
3379 CHANGE_NONSTD_YN,
3380 ORIG_SYSTEM_REFERENCE_CODE,
3381 ORIG_SYSTEM_REFERENCE_ID1,
3382 ORIG_SYSTEM_REFERENCE_ID2,
3383 DISPLAY_SEQUENCE,
3384 ATTRIBUTE_CATEGORY,
3385 ATTRIBUTE1,
3386 ATTRIBUTE2,
3387 ATTRIBUTE3,
3388 ATTRIBUTE4,
3389 ATTRIBUTE5,
3390 ATTRIBUTE6,
3391 ATTRIBUTE7,
3392 ATTRIBUTE8,
3393 ATTRIBUTE9,
3394 ATTRIBUTE10,
3395 ATTRIBUTE11,
3396 ATTRIBUTE12,
3397 ATTRIBUTE13,
3398 ATTRIBUTE14,
3399 ATTRIBUTE15,
3400 PRINT_TEXT_YN,
3401 SUMMARY_AMEND_OPERATION_CODE,
3402 REF_ARTICLE_ID,
3403 REF_ARTICLE_VERSION_ID,
3404 OBJECT_VERSION_NUMBER,
3405 CREATED_BY,
3406 CREATION_DATE,
3407 LAST_UPDATED_BY,
3408 LAST_UPDATE_LOGIN,
3409 LAST_UPDATE_DATE,
3410 ORIG_ARTICLE_ID,
3411 LAST_AMENDED_BY,
3412 LAST_AMENDMENT_DATE
3413 FROM OKC_K_ARTICLES_BH
3414 WHERE document_type = p_doc_type
3415 AND document_id = p_doc_id
3416 AND major_version = p_major_version;
3417
3418 IF (l_debug = 'Y') THEN
3419 Okc_Debug.Log('7300: Leaving restore_version', 2);
3420 END IF;
3421
3422 RETURN( G_RET_STS_SUCCESS );
3423
3424 EXCEPTION
3425 WHEN OTHERS THEN
3426
3427 IF (l_debug = 'Y') THEN
3428 Okc_Debug.Log('7400: Leaving restore_version because of EXCEPTION: '||sqlerrm, 2);
3429 END IF;
3430
3431 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3432 p_msg_name => G_UNEXPECTED_ERROR,
3433 p_token1 => G_SQLCODE_TOKEN,
3434 p_token1_value => sqlcode,
3435 p_token2 => G_SQLERRM_TOKEN,
3436 p_token2_value => sqlerrm);
3437
3438 RETURN G_RET_STS_UNEXP_ERROR ;
3439
3440 END restore_version;
3441
3442 --This Function is called from Versioning API OKC_VERSION_PVT
3443 -- to delete articles for specified version of document
3444
3445 FUNCTION Delete_Version(
3446 p_doc_type IN VARCHAR2,
3447 p_doc_id IN NUMBER,
3448 p_major_version IN NUMBER
3449 ) RETURN VARCHAR2 IS
3450
3451 BEGIN
3452
3453 IF (l_debug = 'Y') THEN
3454 Okc_Debug.Log('7200: Entered Delete_Version', 2);
3455 END IF;
3456
3457 -----------------------------------------
3458 -- Restoring Base Table
3459 -----------------------------------------
3460 DELETE
3461 FROM OKC_K_ARTICLES_BH
3462 WHERE document_type = p_doc_type
3463 AND document_id = p_doc_id
3464 AND major_version = p_major_version;
3465
3466 IF (l_debug = 'Y') THEN
3467 Okc_Debug.Log('7300: Leaving Delete_Version', 2);
3468 END IF;
3469
3470 RETURN( G_RET_STS_SUCCESS );
3471
3472 EXCEPTION
3473 WHEN OTHERS THEN
3474
3475 IF (l_debug = 'Y') THEN
3476 Okc_Debug.Log('7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm, 2);
3477 END IF;
3478
3479 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3480 p_msg_name => G_UNEXPECTED_ERROR,
3481 p_token1 => G_SQLCODE_TOKEN,
3482 p_token1_value => sqlcode,
3483 p_token2 => G_SQLERRM_TOKEN,
3484 p_token2_value => sqlerrm);
3485
3486 RETURN G_RET_STS_UNEXP_ERROR ;
3487
3488 END Delete_Version;
3489
3490 END OKC_K_ARTICLES_PVT;