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