[Home] [Help]
PACKAGE BODY: APPS.OKC_ARTICLES_ALL_PVT
Source
1 PACKAGE BODY OKC_ARTICLES_ALL_PVT AS
2 /* $Header: OKCVARTB.pls 120.3 2011/03/23 08:34:40 kkolukul 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;
22 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
19 ---------------------------------------------------------------------------
20 -- VALIDATION LEVELS
21 ---------------------------------------------------------------------------
23 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
24 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
25 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
26 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
27 ---------------------------------------------------------------------------
28 -- GLOBAL VARIABLES
29 ---------------------------------------------------------------------------
30 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_ARTICLES_ALL_PVT';
31 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
32
33 ------------------------------------------------------------------------------
34 -- GLOBAL CONSTANTS
35 ------------------------------------------------------------------------------
36 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
37 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
38 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
39 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
40 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
41
42 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
43 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
44 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
45
46 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
47 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
48 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
49
50 pending boolean := false; -- pending flag
51
52 ---------------------------------------------------------------------------
53 -- FUNCTION get_seq_id
54 ---------------------------------------------------------------------------
55 FUNCTION Get_Seq_Id (
56 p_article_id IN NUMBER,
57 x_article_id OUT NOCOPY NUMBER
58 ) RETURN VARCHAR2 IS
59 CURSOR l_seq_csr IS
60 SELECT OKC_ARTICLES_ALL_S1.NEXTVAL FROM DUAL;
61 BEGIN
62 IF (l_debug = 'Y') THEN
63 Okc_Debug.Log('100: Entered get_seq_id', 2);
64 END IF;
65
66 IF( p_article_id IS NULL ) THEN
67 OPEN l_seq_csr;
68 FETCH l_seq_csr INTO x_article_id ;
69 IF l_seq_csr%NOTFOUND THEN
70 RAISE NO_DATA_FOUND;
71 END IF;
72 CLOSE l_seq_csr;
73 END IF;
74
75 IF (l_debug = 'Y') THEN
76 Okc_Debug.Log('200: Leaving get_seq_id', 2);
77 END IF;
78 RETURN G_RET_STS_SUCCESS;
79 EXCEPTION
80 WHEN OTHERS THEN
81
82 IF (l_debug = 'Y') THEN
83 Okc_Debug.Log('300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm, 2);
84 END IF;
85
86 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
87 p_msg_name => G_UNEXPECTED_ERROR,
88 p_token1 => G_SQLCODE_TOKEN,
89 p_token1_value => sqlcode,
90 p_token2 => G_SQLERRM_TOKEN,
91 p_token2_value => sqlerrm);
92
93 IF l_seq_csr%ISOPEN THEN
94 CLOSE l_seq_csr;
95 END IF;
96
97 RETURN G_RET_STS_UNEXP_ERROR ;
98
99 END Get_Seq_Id;
100
101 ---------------------------------------------------------------------------
102 -- FUNCTION get_rec for: OKC_ARTICLES_ALL
103 ---------------------------------------------------------------------------
104 FUNCTION Get_Rec (
105 p_article_id IN NUMBER,
106
107 x_article_title OUT NOCOPY VARCHAR2,
108 x_org_id OUT NOCOPY NUMBER,
109 x_article_number OUT NOCOPY VARCHAR2,
110 x_standard_yn OUT NOCOPY VARCHAR2,
111 x_article_intent OUT NOCOPY VARCHAR2,
112 x_article_language OUT NOCOPY VARCHAR2,
113 x_article_type OUT NOCOPY VARCHAR2,
114 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
115 x_orig_system_reference_id1 OUT NOCOPY VARCHAR2,
116 x_orig_system_reference_id2 OUT NOCOPY VARCHAR2,
117 x_cz_transfer_status_flag OUT NOCOPY VARCHAR2,
118 x_program_id OUT NOCOPY NUMBER,
119 x_program_login_id OUT NOCOPY NUMBER,
120 x_program_application_id OUT NOCOPY NUMBER,
121 x_request_id OUT NOCOPY NUMBER,
122 x_attribute_category OUT NOCOPY VARCHAR2,
123 x_attribute1 OUT NOCOPY VARCHAR2,
124 x_attribute2 OUT NOCOPY VARCHAR2,
125 x_attribute3 OUT NOCOPY VARCHAR2,
126 x_attribute4 OUT NOCOPY VARCHAR2,
127 x_attribute5 OUT NOCOPY VARCHAR2,
128 x_attribute6 OUT NOCOPY VARCHAR2,
129 x_attribute7 OUT NOCOPY VARCHAR2,
130 x_attribute8 OUT NOCOPY VARCHAR2,
131 x_attribute9 OUT NOCOPY VARCHAR2,
132 x_attribute10 OUT NOCOPY VARCHAR2,
133 x_attribute11 OUT NOCOPY VARCHAR2,
134 x_attribute12 OUT NOCOPY VARCHAR2,
135 x_attribute13 OUT NOCOPY VARCHAR2,
136 x_attribute14 OUT NOCOPY VARCHAR2,
137 x_attribute15 OUT NOCOPY VARCHAR2,
138 x_object_version_number OUT NOCOPY NUMBER,
142 x_last_update_login OUT NOCOPY NUMBER,
139 x_created_by OUT NOCOPY NUMBER,
140 x_creation_date OUT NOCOPY DATE,
141 x_last_updated_by OUT NOCOPY NUMBER,
143 x_last_update_date OUT NOCOPY DATE
144
145 ) RETURN VARCHAR2 IS
146 CURSOR OKC_ARTICLES_ALL_pk_csr (cp_article_id IN NUMBER) IS
147 SELECT
148 ARTICLE_TITLE,
149 ORG_ID,
150 ARTICLE_NUMBER,
151 STANDARD_YN,
152 ARTICLE_INTENT,
153 ARTICLE_LANGUAGE,
154 ARTICLE_TYPE,
155 ORIG_SYSTEM_REFERENCE_CODE,
156 ORIG_SYSTEM_REFERENCE_ID1,
157 ORIG_SYSTEM_REFERENCE_ID2,
158 CZ_TRANSFER_STATUS_FLAG,
159 PROGRAM_ID,
160 PROGRAM_LOGIN_ID,
161 PROGRAM_APPLICATION_ID,
162 REQUEST_ID,
163 ATTRIBUTE_CATEGORY,
164 ATTRIBUTE1,
165 ATTRIBUTE2,
166 ATTRIBUTE3,
167 ATTRIBUTE4,
168 ATTRIBUTE5,
169 ATTRIBUTE6,
170 ATTRIBUTE7,
171 ATTRIBUTE8,
172 ATTRIBUTE9,
173 ATTRIBUTE10,
174 ATTRIBUTE11,
175 ATTRIBUTE12,
176 ATTRIBUTE13,
177 ATTRIBUTE14,
178 ATTRIBUTE15,
179 OBJECT_VERSION_NUMBER,
180 CREATED_BY,
181 CREATION_DATE,
182 LAST_UPDATED_BY,
183 LAST_UPDATE_LOGIN,
184 LAST_UPDATE_DATE
185 FROM OKC_ARTICLES_ALL t
186 WHERE t.ARTICLE_ID = cp_article_id;
187 BEGIN
188
189 IF (l_debug = 'Y') THEN
190 Okc_Debug.Log('400: Entered get_rec', 2);
191 END IF;
192
193 -- Get current database values
194 OPEN OKC_ARTICLES_ALL_pk_csr (p_article_id);
195 FETCH OKC_ARTICLES_ALL_pk_csr INTO
196 x_article_title,
197 x_org_id,
198 x_article_number,
199 x_standard_yn,
200 x_article_intent,
201 x_article_language,
202 x_article_type,
203 x_orig_system_reference_code,
204 x_orig_system_reference_id1,
205 x_orig_system_reference_id2,
206 x_cz_transfer_status_flag,
207 x_program_id,
208 x_program_login_id,
209 x_program_application_id,
210 x_request_id,
211 x_attribute_category,
212 x_attribute1,
213 x_attribute2,
214 x_attribute3,
215 x_attribute4,
216 x_attribute5,
217 x_attribute6,
218 x_attribute7,
219 x_attribute8,
220 x_attribute9,
221 x_attribute10,
222 x_attribute11,
223 x_attribute12,
224 x_attribute13,
225 x_attribute14,
226 x_attribute15,
227 x_object_version_number,
228 x_created_by,
229 x_creation_date,
230 x_last_updated_by,
231 x_last_update_login,
232 x_last_update_date;
233 IF OKC_ARTICLES_ALL_pk_csr%NOTFOUND THEN
234 RAISE NO_DATA_FOUND;
235 END IF;
236 CLOSE OKC_ARTICLES_ALL_pk_csr;
237
238 IF (l_debug = 'Y') THEN
239 Okc_Debug.Log('500: Leaving get_rec ', 2);
240 END IF;
241
242 RETURN G_RET_STS_SUCCESS ;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246
247 IF (l_debug = 'Y') THEN
248 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
249 END IF;
250
251 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
252 p_msg_name => G_UNEXPECTED_ERROR,
253 p_token1 => G_SQLCODE_TOKEN,
254 p_token1_value => sqlcode,
255 p_token2 => G_SQLERRM_TOKEN,
256 p_token2_value => sqlerrm);
257
258 IF OKC_ARTICLES_ALL_pk_csr%ISOPEN THEN
259 CLOSE OKC_ARTICLES_ALL_pk_csr;
260 END IF;
261
262 RETURN G_RET_STS_UNEXP_ERROR ;
263
264 END Get_Rec;
265
266 -----------------------------------------
267 -- Set_Attributes for:OKC_ARTICLES_ALL --
268 -----------------------------------------
269 FUNCTION Set_Attributes(
270 p_article_id IN NUMBER,
271 p_article_title IN VARCHAR2,
272 p_org_id IN NUMBER,
273 p_article_number IN VARCHAR2,
274 p_standard_yn IN VARCHAR2,
275 p_article_intent IN VARCHAR2,
276 p_article_language IN VARCHAR2,
277 p_article_type IN VARCHAR2,
278 p_orig_system_reference_code IN VARCHAR2,
279 p_orig_system_reference_id1 IN VARCHAR2,
280 p_orig_system_reference_id2 IN VARCHAR2,
281 p_cz_transfer_status_flag IN VARCHAR2,
282 p_program_id IN NUMBER,
283 p_program_login_id IN NUMBER,
284 p_program_application_id IN NUMBER,
285 p_request_id IN NUMBER,
286 p_attribute_category IN VARCHAR2,
287 p_attribute1 IN VARCHAR2,
288 p_attribute2 IN VARCHAR2,
289 p_attribute3 IN VARCHAR2,
290 p_attribute4 IN VARCHAR2,
291 p_attribute5 IN VARCHAR2,
295 p_attribute9 IN VARCHAR2,
292 p_attribute6 IN VARCHAR2,
293 p_attribute7 IN VARCHAR2,
294 p_attribute8 IN VARCHAR2,
296 p_attribute10 IN VARCHAR2,
297 p_attribute11 IN VARCHAR2,
298 p_attribute12 IN VARCHAR2,
299 p_attribute13 IN VARCHAR2,
300 p_attribute14 IN VARCHAR2,
301 p_attribute15 IN VARCHAR2,
302 p_object_version_number IN NUMBER,
303
304 x_article_title OUT NOCOPY VARCHAR2,
305 x_org_id OUT NOCOPY NUMBER,
306 x_article_number OUT NOCOPY VARCHAR2,
307 x_standard_yn OUT NOCOPY VARCHAR2,
308 x_article_intent OUT NOCOPY VARCHAR2,
309 x_article_language OUT NOCOPY VARCHAR2,
310 x_article_type OUT NOCOPY VARCHAR2,
311 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
312 x_orig_system_reference_id1 OUT NOCOPY VARCHAR2,
313 x_orig_system_reference_id2 OUT NOCOPY VARCHAR2,
314 x_cz_transfer_status_flag OUT NOCOPY VARCHAR2,
315 x_program_id OUT NOCOPY NUMBER,
316 x_program_login_id OUT NOCOPY NUMBER,
317 x_program_application_id OUT NOCOPY NUMBER,
318 x_request_id OUT NOCOPY NUMBER,
319 x_attribute_category OUT NOCOPY VARCHAR2,
320 x_attribute1 OUT NOCOPY VARCHAR2,
321 x_attribute2 OUT NOCOPY VARCHAR2,
322 x_attribute3 OUT NOCOPY VARCHAR2,
323 x_attribute4 OUT NOCOPY VARCHAR2,
324 x_attribute5 OUT NOCOPY VARCHAR2,
325 x_attribute6 OUT NOCOPY VARCHAR2,
326 x_attribute7 OUT NOCOPY VARCHAR2,
327 x_attribute8 OUT NOCOPY VARCHAR2,
328 x_attribute9 OUT NOCOPY VARCHAR2,
329 x_attribute10 OUT NOCOPY VARCHAR2,
330 x_attribute11 OUT NOCOPY VARCHAR2,
331 x_attribute12 OUT NOCOPY VARCHAR2,
332 x_attribute13 OUT NOCOPY VARCHAR2,
333 x_attribute14 OUT NOCOPY VARCHAR2,
334 x_attribute15 OUT NOCOPY VARCHAR2
335 ) RETURN VARCHAR2 IS
336 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
337 l_object_version_number OKC_ARTICLES_ALL.OBJECT_VERSION_NUMBER%TYPE;
338 l_created_by OKC_ARTICLES_ALL.CREATED_BY%TYPE;
339 l_creation_date OKC_ARTICLES_ALL.CREATION_DATE%TYPE;
340 l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
341 l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
342 l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
343 BEGIN
344 IF (l_debug = 'Y') THEN
345 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
346 END IF;
347
348 IF( p_article_id IS NOT NULL ) THEN
349 -- Get current database values
350 l_return_status := Get_Rec(
351 p_article_id => p_article_id,
352 x_article_title => x_article_title,
353 x_org_id => x_org_id,
354 x_article_number => x_article_number,
355 x_standard_yn => x_standard_yn,
356 x_article_intent => x_article_intent,
357 x_article_language => x_article_language,
358 x_article_type => x_article_type,
359 x_orig_system_reference_code => x_orig_system_reference_code,
360 x_orig_system_reference_id1 => x_orig_system_reference_id1,
361 x_orig_system_reference_id2 => x_orig_system_reference_id2,
362 x_cz_transfer_status_flag => x_cz_transfer_status_flag,
363 x_program_id => x_program_id,
364 x_program_login_id => x_program_login_id,
365 x_program_application_id => x_program_application_id,
366 x_request_id => x_request_id,
367 x_attribute_category => x_attribute_category,
368 x_attribute1 => x_attribute1,
369 x_attribute2 => x_attribute2,
370 x_attribute3 => x_attribute3,
371 x_attribute4 => x_attribute4,
372 x_attribute5 => x_attribute5,
373 x_attribute6 => x_attribute6,
374 x_attribute7 => x_attribute7,
375 x_attribute8 => x_attribute8,
376 x_attribute9 => x_attribute9,
377 x_attribute10 => x_attribute10,
378 x_attribute11 => x_attribute11,
379 x_attribute12 => x_attribute12,
380 x_attribute13 => x_attribute13,
381 x_attribute14 => x_attribute14,
382 x_attribute15 => x_attribute15,
383 x_object_version_number => l_object_version_number,
384 x_created_by => l_created_by,
385 x_creation_date => l_creation_date,
386 x_last_updated_by => l_last_updated_by,
387 x_last_update_login => l_last_update_login,
388 x_last_update_date => l_last_update_date
389 );
390 --- If any errors happen abort API
391 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
393 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
394 RAISE FND_API.G_EXC_ERROR;
395 END IF;
396
397 --- Reversing G_MISS/NULL values logic
398 pending := false; -- reset pending flag
399 IF (p_article_title = G_MISS_CHAR) THEN
400 x_article_title := NULL;
401 pending := true; -- updating article_title set pending flag
402 ELSIF (p_ARTICLE_TITLE IS NOT NULL) THEN
406
403 x_article_title := p_article_title;
404 pending := true; -- updating article_title set pending flag
405 END IF;
407 IF (p_org_id = G_MISS_NUM) THEN
408 x_org_id := NULL;
409 ELSIF (p_ORG_ID IS NOT NULL) THEN
410 x_org_id := p_org_id;
411 END IF;
412
413 IF (p_article_number = G_MISS_CHAR) THEN
414 x_article_number := NULL;
415 ELSIF (p_ARTICLE_NUMBER IS NOT NULL) THEN
416 x_article_number := p_article_number;
417 END IF;
418
419 IF (p_standard_yn = G_MISS_CHAR) THEN
420 x_standard_yn := NULL;
421 ELSIF (p_STANDARD_YN IS NOT NULL) THEN
422 -- x_standard_yn := p_standard_yn; -- Modified
423 x_standard_yn := UPPER(p_standard_yn);
424 END IF;
425
426 IF (p_article_intent = G_MISS_CHAR) THEN
427 x_article_intent := NULL;
428 ELSIF (p_ARTICLE_INTENT IS NOT NULL) THEN
429 -- x_article_intent := p_article_intent; -- Modified
430 x_article_intent := UPPER(p_article_intent);
431 END IF;
432
433 IF (p_article_language = G_MISS_CHAR) THEN
434 x_article_language := NULL;
435 ELSIF (p_ARTICLE_LANGUAGE IS NOT NULL) THEN
436 x_article_language := p_article_language;
437 END IF;
438
439 IF (p_article_type = G_MISS_CHAR) THEN
440 x_article_type := NULL;
441 ELSIF (p_ARTICLE_TYPE IS NOT NULL) THEN
442 x_article_type := p_article_type;
443 END IF;
444
445 IF (p_orig_system_reference_code = G_MISS_CHAR) THEN
446 x_orig_system_reference_code := NULL;
447 ELSIF (p_ORIG_SYSTEM_REFERENCE_CODE IS NOT NULL) THEN
448 x_orig_system_reference_code := p_orig_system_reference_code;
449 END IF;
450
451 IF (p_orig_system_reference_id1 = G_MISS_CHAR) THEN
452 x_orig_system_reference_id1 := NULL;
453 ELSIF (p_ORIG_SYSTEM_REFERENCE_ID1 IS NOT NULL) THEN
454 x_orig_system_reference_id1 := p_orig_system_reference_id1;
455 END IF;
456
457 IF (p_orig_system_reference_id2 = G_MISS_CHAR) THEN
458 x_orig_system_reference_id2 := NULL;
459 ELSIF (p_ORIG_SYSTEM_REFERENCE_ID2 IS NOT NULL) THEN
460 x_orig_system_reference_id2 := p_orig_system_reference_id2;
461 END IF;
462
463 IF (p_cz_transfer_status_flag = G_MISS_CHAR) THEN
464 x_cz_transfer_status_flag := NULL;
465 ELSIF (p_CZ_TRANSFER_STATUS_FLAG IS NOT NULL) THEN
466 -- x_cz_transfer_status_flag := p_cz_transfer_status_flag; -- Modified
467 x_cz_transfer_status_flag := UPPER(p_cz_transfer_status_flag);
468 END IF;
469
470 IF (p_program_id = G_MISS_NUM) THEN
471 x_program_id := NULL;
472 ELSIF (p_PROGRAM_ID IS NOT NULL) THEN
473 x_program_id := p_program_id;
474 END IF;
475
476 IF (p_program_login_id = G_MISS_NUM) THEN
477 x_program_login_id := NULL;
478 ELSIF (p_PROGRAM_LOGIN_ID IS NOT NULL) THEN
479 x_program_login_id := p_program_login_id;
480 END IF;
481
482 IF (p_program_application_id = G_MISS_NUM) THEN
483 x_program_application_id := NULL;
484 ELSIF (p_PROGRAM_APPLICATION_ID IS NOT NULL) THEN
485 x_program_application_id := p_program_application_id;
486 END IF;
487
488 IF (p_request_id = G_MISS_NUM) THEN
489 x_request_id := NULL;
490 ELSIF (p_REQUEST_ID IS NOT NULL) THEN
491 x_request_id := p_request_id;
492 END IF;
493
494 IF (p_attribute_category = G_MISS_CHAR) THEN
495 x_attribute_category := NULL;
496 ELSIF (p_ATTRIBUTE_CATEGORY IS NOT NULL) THEN
497 x_attribute_category := p_attribute_category;
498 END IF;
499
500 IF (p_attribute1 = G_MISS_CHAR) THEN
501 x_attribute1 := NULL;
502 ELSIF (p_ATTRIBUTE1 IS NOT NULL) THEN
503 x_attribute1 := p_attribute1;
504 END IF;
505
506 IF (p_attribute2 = G_MISS_CHAR) THEN
507 x_attribute2 := NULL;
508 ELSIF (p_ATTRIBUTE2 IS NOT NULL) THEN
509 x_attribute2 := p_attribute2;
510 END IF;
511
512 IF (p_attribute3 = G_MISS_CHAR) THEN
513 x_attribute3 := NULL;
514 ELSIF (p_ATTRIBUTE3 IS NOT NULL) THEN
515 x_attribute3 := p_attribute3;
516 END IF;
517
518 IF (p_attribute4 = G_MISS_CHAR) THEN
519 x_attribute4 := NULL;
520 ELSIF (p_ATTRIBUTE4 IS NOT NULL) THEN
521 x_attribute4 := p_attribute4;
522 END IF;
523
524 IF (p_attribute5 = G_MISS_CHAR) THEN
525 x_attribute5 := NULL;
526 ELSIF (p_ATTRIBUTE5 IS NOT NULL) THEN
527 x_attribute5 := p_attribute5;
528 END IF;
529
530 IF (p_attribute6 = G_MISS_CHAR) THEN
531 x_attribute6 := NULL;
532 ELSIF (p_ATTRIBUTE6 IS NOT NULL) THEN
533 x_attribute6 := p_attribute6;
534 END IF;
535
536 IF (p_attribute7 = G_MISS_CHAR) THEN
537 x_attribute7 := NULL;
538 ELSIF (p_ATTRIBUTE7 IS NOT NULL) THEN
539 x_attribute7 := p_attribute7;
540 END IF;
541
542 IF (p_attribute8 = G_MISS_CHAR) THEN
543 x_attribute8 := NULL;
544 ELSIF (p_ATTRIBUTE8 IS NOT NULL) THEN
545 x_attribute8 := p_attribute8;
546 END IF;
547
548 IF (p_attribute9 = G_MISS_CHAR) THEN
549 x_attribute9 := NULL;
550 ELSIF (p_ATTRIBUTE9 IS NOT NULL) THEN
551 x_attribute9 := p_attribute9;
552 END IF;
553
554 IF (p_attribute10 = G_MISS_CHAR) THEN
558 END IF;
555 x_attribute10 := NULL;
556 ELSIF (p_ATTRIBUTE10 IS NOT NULL) THEN
557 x_attribute10 := p_attribute10;
559
560 IF (p_attribute11 = G_MISS_CHAR) THEN
561 x_attribute11 := NULL;
562 ELSIF (p_ATTRIBUTE11 IS NOT NULL) THEN
563 x_attribute11 := p_attribute11;
564 END IF;
565
566 IF (p_attribute12 = G_MISS_CHAR) THEN
567 x_attribute12 := NULL;
568 ELSIF (p_ATTRIBUTE12 IS NOT NULL) THEN
569 x_attribute12 := p_attribute12;
570 END IF;
571
572 IF (p_attribute13 = G_MISS_CHAR) THEN
573 x_attribute13 := NULL;
574 ELSIF (p_ATTRIBUTE13 IS NOT NULL) THEN
575 x_attribute13 := p_attribute13;
576 END IF;
577
578 IF (p_attribute14 = G_MISS_CHAR) THEN
579 x_attribute14 := NULL;
580 ELSIF (p_ATTRIBUTE14 IS NOT NULL) THEN
581 x_attribute14 := p_attribute14;
582 END IF;
583
584 IF (p_attribute15 = G_MISS_CHAR) THEN
585 x_attribute15 := NULL;
586 ELSIF (p_ATTRIBUTE15 IS NOT NULL) THEN
587 x_attribute15 := p_attribute15;
588 END IF;
589
590
591 -- ?? converting to uppercase all _YN columns
592 -- ?? per performance reason it can be moved into corresponding
593 -- ?? ELSIF( column IS NOT NULL) section above
594 -- ?? x_standard_yn := Upper( x_standard_yn );
595 ELSE
596 x_article_title := p_article_title;
597 x_org_id := p_org_id;
598 x_article_number := p_article_number;
599 x_standard_yn := p_standard_yn;
600 x_article_intent := p_article_intent;
601 x_article_language := p_article_language;
602 x_article_type := p_article_type;
603 x_orig_system_reference_code := p_orig_system_reference_code;
604 x_orig_system_reference_id1 := p_orig_system_reference_id1;
605 x_orig_system_reference_id2 := p_orig_system_reference_id2;
606 x_cz_transfer_status_flag := p_cz_transfer_status_flag;
607 x_program_id := p_program_id;
608 x_program_login_id := p_program_login_id;
609 x_program_application_id := p_program_application_id;
610 x_request_id := p_request_id;
611 x_attribute_category := p_attribute_category;
612 x_attribute1 := p_attribute1;
613 x_attribute2 := p_attribute2;
614 x_attribute3 := p_attribute3;
615 x_attribute4 := p_attribute4;
616 x_attribute5 := p_attribute5;
617 x_attribute6 := p_attribute6;
618 x_attribute7 := p_attribute7;
619 x_attribute8 := p_attribute8;
620 x_attribute9 := p_attribute9;
621 x_attribute10 := p_attribute10;
622 x_attribute11 := p_attribute11;
623 x_attribute12 := p_attribute12;
624 x_attribute13 := p_attribute13;
625 x_attribute14 := p_attribute14;
626 x_attribute15 := p_attribute15;
627
628 END IF;
629
630 IF (l_debug = 'Y') THEN
631 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
632 END IF;
633
634 RETURN G_RET_STS_SUCCESS ;
635 EXCEPTION
636 WHEN FND_API.G_EXC_ERROR THEN
637 IF (l_debug = 'Y') THEN
638 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
639 END IF;
640 RETURN G_RET_STS_ERROR;
641
642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
643 IF (l_debug = 'Y') THEN
644 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
645 END IF;
646 RETURN G_RET_STS_UNEXP_ERROR;
647
648 WHEN OTHERS THEN
649 IF (l_debug = 'Y') THEN
650 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
651 END IF;
652 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
653 p_msg_name => G_UNEXPECTED_ERROR,
654 p_token1 => G_SQLCODE_TOKEN,
655 p_token1_value => sqlcode,
656 p_token2 => G_SQLERRM_TOKEN,
657 p_token2_value => sqlerrm);
658 RETURN G_RET_STS_UNEXP_ERROR;
659
660 END Set_Attributes ;
661
662 ----------------------------------------------
663 -- Validate_Attributes for: OKC_ARTICLES_ALL --
664 ----------------------------------------------
665 FUNCTION Validate_Attributes (
666 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
667 p_import_action IN VARCHAR2 := NULL,
668
669 p_article_id IN NUMBER,
670 p_article_title IN VARCHAR2,
671 p_org_id IN NUMBER,
672 p_article_number IN VARCHAR2,
673 p_standard_yn IN VARCHAR2,
674 p_article_intent IN VARCHAR2,
675 p_article_language IN VARCHAR2,
676 p_article_type IN VARCHAR2,
677 p_orig_system_reference_code IN VARCHAR2,
678 p_orig_system_reference_id1 IN VARCHAR2,
679 p_orig_system_reference_id2 IN VARCHAR2,
680 p_cz_transfer_status_flag IN VARCHAR2,
681 p_program_id IN NUMBER,
682 p_program_login_id IN NUMBER,
683 p_program_application_id IN NUMBER,
684 p_request_id IN NUMBER,
685 p_attribute_category IN VARCHAR2,
686 p_attribute1 IN VARCHAR2,
687 p_attribute2 IN VARCHAR2,
688 p_attribute3 IN VARCHAR2,
689 p_attribute4 IN VARCHAR2,
690 p_attribute5 IN VARCHAR2,
691 p_attribute6 IN VARCHAR2,
692 p_attribute7 IN VARCHAR2,
693 p_attribute8 IN VARCHAR2,
694 p_attribute9 IN VARCHAR2,
695 p_attribute10 IN VARCHAR2,
696 p_attribute11 IN VARCHAR2,
697 p_attribute12 IN VARCHAR2,
698 p_attribute13 IN VARCHAR2,
699 p_attribute14 IN VARCHAR2,
700 p_attribute15 IN VARCHAR2
701 ) RETURN VARCHAR2 IS
702 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
703 l_tmp_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
704 l_dummy_var VARCHAR2(1) := '?';
705 l_doc_sequence_type VARCHAR2(1);
706 l_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
707
708 CURSOR l_org_id_csr is
709 SELECT '!'
710 FROM HR_ALL_ORGANIZATION_UNITS
711 WHERE ORGANIZATION_ID = p_org_id;
712
713 CURSOR l_article_language_csr is
714 SELECT '!'
715 FROM FND_LANGUAGES
716 WHERE LANGUAGE_CODE = p_article_language
717 AND INSTALLED_FLAG in ('B','I');
718
719 -- Added Cursor for Bug 3673484 - For Non-Std Clauses, no need to check effectivity
720 CURSOR l_article_type_csr is
721 SELECT '!'
722 FROM FND_LOOKUPS FNDLKUP
723 WHERE FNDLKUP.LOOKUP_TYPE = 'OKC_SUBJECT'
724 AND FNDLKUP.LOOKUP_CODE = p_article_type;
725
726 BEGIN
727
728 IF (l_debug = 'Y') THEN
729 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
730 END IF;
731
732 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
733 IF (l_debug = 'Y') THEN
734 Okc_Debug.Log('1300: required values validation', 2);
735 END IF;
736
737 IF (l_debug = 'Y') THEN
738 Okc_Debug.Log('1400: - attribute ARTICLE_ID ', 2);
739 END IF;
740 IF nvl(p_import_action,'X') <> 'N' THEN -- do not validate if this is called from import while validating NEW articles
741 IF ( p_article_id IS NULL) THEN
742 IF (l_debug = 'Y') THEN
743 Okc_Debug.Log('1500: - attribute ARTICLE_ID is invalid', 2);
744 END IF;
745 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_ID');
746 l_return_status := G_RET_STS_ERROR;
747 END IF;
748 ELSIF p_import_action = 'N' AND p_standard_yn = 'Y' AND p_article_number IS NULL THEN
749 -- Check Sequence is defined or not
750 OKC_ARTICLES_GRP.GET_ARTICLE_SEQ_NUMBER
751 (p_article_number => p_article_number,
752 p_seq_type_info_only => 'Y',
753 p_org_id => p_org_id,
754 x_article_number => l_article_number,
755 x_doc_sequence_type => l_doc_sequence_type,
756 x_return_status => l_return_status);
757
758 IF l_return_status <> G_RET_STS_SUCCESS THEN
759 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NUMBER_NOT_PROVIDED');
760 l_return_status := G_RET_STS_ERROR;
761 ELSIF l_doc_sequence_type <> 'A' THEN
762 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NUMBER_NOT_PROVIDED');
763 l_return_status := G_RET_STS_ERROR;
764 END IF;
765 END IF;
766
767 IF (l_debug = 'Y') THEN
768 Okc_Debug.Log('1400: - attribute ARTICLE_TITLE ', 2);
769 END IF;
770 IF ( p_article_title IS NULL) THEN
771 IF (l_debug = 'Y') THEN
772 Okc_Debug.Log('1500: - attribute ARTICLE_TITLE is invalid', 2);
773 END IF;
774 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_TITLE');
775 l_return_status := G_RET_STS_ERROR;
776 END IF;
777
778 IF (l_debug = 'Y') THEN
779 Okc_Debug.Log('1400: - attribute ORG_ID ', 2);
780 END IF;
781 IF ( p_org_id IS NULL) THEN
782 IF (l_debug = 'Y') THEN
783 Okc_Debug.Log('1500: - attribute ORG_ID is invalid', 2);
784 END IF;
785 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ORG_ID');
786 l_return_status := G_RET_STS_ERROR;
787 END IF;
788
789 IF (l_debug = 'Y') THEN
790 Okc_Debug.Log('1400: - attribute ARTICLE_NUMBER ', 2);
791 END IF;
792 IF nvl(p_import_action,'X') <> 'N' AND p_standard_yn = 'Y' THEN
793 IF ( p_article_number IS NULL) THEN
794 IF (l_debug = 'Y') THEN
795 Okc_Debug.Log('1500: - attribute ARTICLE_NUMBER is invalid', 2);
796 END IF;
797 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_NUMBER');
798 l_return_status := G_RET_STS_ERROR;
799 END IF;
800 END IF;
801
802 IF (l_debug = 'Y') THEN
803 Okc_Debug.Log('1400: - attribute STANDARD_YN ', 2);
804 END IF;
805 IF ( p_standard_yn IS NULL) THEN
806 IF (l_debug = 'Y') THEN
807 Okc_Debug.Log('1500: - attribute STANDARD_YN is invalid', 2);
808 END IF;
809 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'STANDARD_YN');
810 l_return_status := G_RET_STS_ERROR;
811 END IF;
812
813 IF (l_debug = 'Y') THEN
814 Okc_Debug.Log('1400: - attribute ARTICLE_INTENT ', 2);
815 END IF;
816 IF ( p_article_intent IS NULL) THEN
817 IF (l_debug = 'Y') THEN
818 Okc_Debug.Log('1500: - attribute ARTICLE_INTENT is invalid', 2);
819 END IF;
820 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_INTENT');
821 l_return_status := G_RET_STS_ERROR;
822 END IF;
823
824 IF (l_debug = 'Y') THEN
825 Okc_Debug.Log('1400: - attribute ARTICLE_LANGUAGE ', 2);
826 END IF;
827 IF ( p_article_language IS NULL) THEN
828 IF (l_debug = 'Y') THEN
829 Okc_Debug.Log('1500: - attribute ARTICLE_LANGUAGE is invalid', 2);
830 END IF;
831 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_LANGUAGE');
832 l_return_status := G_RET_STS_ERROR;
833 END IF;
834
835 END IF;
836
837 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
838 IF (l_debug = 'Y') THEN
839 Okc_Debug.Log('1600: static values and range validation', 2);
840 END IF;
841 -- Modified
842 IF (l_debug = 'Y') THEN
843 Okc_Debug.Log('1700: - attribute STANDARD_YN ', 2);
844 END IF;
845 IF p_standard_yn NOT IN ('Y','N') THEN
846 IF (l_debug = 'Y') THEN
847 Okc_Debug.Log('1800: - attribute STANDARD_YN is invalid', 2);
848 END IF;
849 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'STANDARD_YN');
850 l_return_status := G_RET_STS_ERROR;
851 END IF;
852
853 IF (l_debug = 'Y') THEN
854 Okc_Debug.Log('1700: - attribute ARTICLE_INTENT ', 2);
855 END IF;
856 IF p_article_intent NOT IN ('B','S') THEN
857 IF (l_debug = 'Y') THEN
858 Okc_Debug.Log('1800: - attribute ARTICLE_INTENT is invalid', 2);
859 END IF;
860 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_INTENT');
861 l_return_status := G_RET_STS_ERROR;
862 END IF;
863
864 -- Modified
865 END IF;
866
867 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
868 IF (l_debug = 'Y') THEN
869 Okc_Debug.Log('1900: lookup codes validation', 2);
870 END IF;
871
872 -- Modified
873 IF (l_debug = 'Y') THEN
877 -- Standard clauses will be checked for valid lookup code the way it is now
874 Okc_Debug.Log('2000: - attribute ARTICLE_TYPE ', 2);
875 END IF;
876
878 -- Non Standard clauses - just check for existence of the lookup code and do not
879 -- care whether it is effective or not.
880 -- Use a new message suggested by PMs
881
882 IF p_article_type IS NOT NULL THEN
883 l_dummy_var := '?';
884 IF p_standard_yn = 'N' THEN
885 OPEN l_article_type_csr;
886 FETCH l_article_type_csr INTO l_dummy_var;
887 CLOSE l_article_type_csr;
888 IF (l_dummy_var = '?') THEN
889 IF (l_debug = 'Y') THEN
890 Okc_Debug.Log('2300: - attribute ARTICLE_TYPE is invalid for non-std clause', 2);
891 END IF;
892 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_INVALID_TYPE');
893 l_return_status := G_RET_STS_ERROR;
894 END IF;
895 ELSIF p_standard_yn = 'Y' THEN
896 l_tmp_return_status := Okc_Util.Check_Lookup_Code('OKC_SUBJECT',p_article_type);
897 IF (l_tmp_return_status <> G_RET_STS_SUCCESS) THEN
898 IF (l_debug = 'Y') THEN
899 Okc_Debug.Log('2300: - attribute ARTICLE_TYPE is invalid for std clause', 2);
900 END IF;
901 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_INVALID_TYPE');
902 l_return_status := G_RET_STS_ERROR;
903 END IF;
904 END IF;
905
906 END IF;
907
908 -- Modified
909 END IF;
910
911 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
912 IF (l_debug = 'Y') THEN
913 Okc_Debug.Log('2100: foreign keys validation ', 2);
914 END IF;
915
916 -- org id will always be set. You cannot create articles for a different org. Therefore no need to validate
917 /*
918 IF (l_debug = 'Y') THEN
919 Okc_Debug.Log('2200: - attribute ORG_ID ', 2);
920 END IF;
921 IF p_org_id IS NOT NULL AND p_org_id <> -99 THEN
922 l_dummy_var := '?';
923 OPEN l_org_id_csr;
924 FETCH l_org_id_csr INTO l_dummy_var;
925 CLOSE l_org_id_csr;
926 IF (l_dummy_var = '?') THEN
927 IF (l_debug = 'Y') THEN
928 Okc_Debug.Log('2300: - attribute ORG_ID is invalid', 2);
929 END IF;
930 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ORG_ID');
931 l_return_status := G_RET_STS_ERROR;
932 END IF;
933 END IF;
934 IF (l_debug = 'Y') THEN
935 Okc_Debug.Log('2200: - attribute ARTICLE_LANGAUGE ', 2);
936 END IF;
937 */
938 IF p_article_language IS NOT NULL THEN
939 l_dummy_var := '?';
940 OPEN l_article_language_csr;
941 FETCH l_article_language_csr INTO l_dummy_var;
942 CLOSE l_article_language_csr;
943 IF (l_dummy_var = '?') THEN
944 IF (l_debug = 'Y') THEN
945 Okc_Debug.Log('2300: - attribute ARTICLE_LANGAUGE is invalid', 2);
946 END IF;
947 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ARTICLE_LANGAUGE');
948 l_return_status := G_RET_STS_ERROR;
949 END IF;
950 END IF;
951
952 -- Modified
953 END IF;
954
955
956 IF (l_debug = 'Y') THEN
957 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
958 END IF;
959
960 RETURN l_return_status;
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
965 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
966 p_msg_name => G_UNEXPECTED_ERROR,
967 p_token1 => G_SQLCODE_TOKEN,
968 p_token1_value => sqlcode,
969 p_token2 => G_SQLERRM_TOKEN,
970 p_token2_value => sqlerrm);
971
972 IF l_org_id_csr%ISOPEN THEN
973 CLOSE l_org_id_csr;
974 END IF;
975
976 IF l_article_language_csr%ISOPEN THEN
977 CLOSE l_article_language_csr;
978 END IF;
979
980 IF l_article_type_csr%ISOPEN THEN
981 CLOSE l_article_type_csr;
982 END IF;
983
984 RETURN G_RET_STS_UNEXP_ERROR;
985
986 END Validate_Attributes;
987
988
989 ---------------------------------------------------------------------------
990 -- PROCEDURE Validate_Record
991 -- It calls Item Level Validations and then makes Record Level Validations
992 ---------------------------------------------------------------------------
993 ------------------------------------------
994 -- Validate_Record for:OKC_ARTICLES_ALL --
995 ------------------------------------------
996 FUNCTION Validate_Record (
997 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
998 p_import_action IN VARCHAR2 := NULL,
999
1000 p_article_id IN NUMBER,
1001 p_article_title IN VARCHAR2,
1002 p_org_id IN NUMBER,
1003 p_article_number IN VARCHAR2,
1004 p_standard_yn IN VARCHAR2,
1005 p_article_intent IN VARCHAR2,
1006 p_article_language IN VARCHAR2,
1007 p_article_type IN VARCHAR2,
1008 p_orig_system_reference_code IN VARCHAR2,
1009 p_orig_system_reference_id1 IN VARCHAR2,
1010 p_orig_system_reference_id2 IN VARCHAR2,
1011 p_cz_transfer_status_flag IN VARCHAR2 := NULL,
1012 p_program_id IN NUMBER := NULL,
1016 p_attribute_category IN VARCHAR2,
1013 p_program_login_id IN NUMBER := NULL,
1014 p_program_application_id IN NUMBER := NULL,
1015 p_request_id IN NUMBER := NULL,
1017 p_attribute1 IN VARCHAR2,
1018 p_attribute2 IN VARCHAR2,
1019 p_attribute3 IN VARCHAR2,
1020 p_attribute4 IN VARCHAR2,
1021 p_attribute5 IN VARCHAR2,
1022 p_attribute6 IN VARCHAR2,
1023 p_attribute7 IN VARCHAR2,
1024 p_attribute8 IN VARCHAR2,
1025 p_attribute9 IN VARCHAR2,
1026 p_attribute10 IN VARCHAR2,
1027 p_attribute11 IN VARCHAR2,
1028 p_attribute12 IN VARCHAR2,
1029 p_attribute13 IN VARCHAR2,
1030 p_attribute14 IN VARCHAR2,
1031 p_attribute15 IN VARCHAR2
1032 ) RETURN VARCHAR2 IS
1033 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1034
1035 -- Modified
1036 l_dummy_var VARCHAR2(1) := '?';
1037 l_row_found BOOLEAN := TRUE;
1038
1039 -- The following cursor will check for duplicates of org_id and article_title
1040 CURSOR l_unq_csr(p_org_id IN NUMBER, p_article_id IN NUMBER, p_article_title IN VARCHAR2) is
1041 SELECT '1' FROM OKC_ARTICLES_ALL
1042 WHERE article_title = p_article_title
1043 AND org_id = p_org_id
1044 AND standard_yn = 'Y'
1045 AND article_id <> nvl(p_article_id,-99)
1046 AND rownum < 2
1047 UNION ALL
1048 SELECT '1'
1049 FROM okc_articles_all art, okc_article_adoptions adp, okc_article_versions artv
1050 WHERE adp.local_org_id = p_org_id
1051 AND art.article_title = p_article_title
1052 AND adp.adoption_type = 'ADOPTED'
1053 AND adp.global_article_version_id = artv.article_version_id
1054 AND art.article_id = artv.article_id
1055 AND art.article_id <> nvl(p_article_id,-99)
1056 AND rownum < 2;
1057 -- Modified
1058 BEGIN
1059
1060 IF (l_debug = 'Y') THEN
1061 Okc_Debug.Log('2600: Entered Validate_Record', 2);
1062 END IF;
1063
1064 --- Validate all non-missing attributes (Item Level Validation)
1065 l_return_status := Validate_Attributes(
1066 p_validation_level => p_validation_level,
1067 p_import_action => p_import_action,
1068 p_article_id => p_article_id,
1069 p_article_title => p_article_title,
1070 p_org_id => p_org_id,
1071 p_article_number => p_article_number,
1072 p_standard_yn => p_standard_yn,
1073 p_article_intent => p_article_intent,
1074 p_article_language => p_article_language,
1075 p_article_type => p_article_type,
1076 p_orig_system_reference_code => p_orig_system_reference_code,
1077 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1078 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1079 p_cz_transfer_status_flag => p_cz_transfer_status_flag,
1080 p_program_id => p_program_id,
1081 p_program_login_id => p_program_login_id,
1082 p_program_application_id => p_program_application_id,
1083 p_request_id => p_request_id,
1084 p_attribute_category => p_attribute_category,
1085 p_attribute1 => p_attribute1,
1086 p_attribute2 => p_attribute2,
1087 p_attribute3 => p_attribute3,
1088 p_attribute4 => p_attribute4,
1089 p_attribute5 => p_attribute5,
1090 p_attribute6 => p_attribute6,
1091 p_attribute7 => p_attribute7,
1092 p_attribute8 => p_attribute8,
1093 p_attribute9 => p_attribute9,
1094 p_attribute10 => p_attribute10,
1095 p_attribute11 => p_attribute11,
1096 p_attribute12 => p_attribute12,
1097 p_attribute13 => p_attribute13,
1098 p_attribute14 => p_attribute14,
1099 p_attribute15 => p_attribute15
1100 );
1101 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1102 IF (l_debug = 'Y') THEN
1103 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
1104 END IF;
1105 RETURN G_RET_STS_UNEXP_ERROR;
1106 END IF;
1107
1108 --- Record Level Validation
1109 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
1110 IF (l_debug = 'Y') THEN
1111 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
1112 END IF;
1113 /*+++++++++++++start of hand code +++++++++++++++++++*/
1114 -- manual coding for Record Level Validations if required
1115 --dbms_output.put_line('checking for p_article_type2: '||p_article_type||'*'||length(p_article_type));
1116 IF ( p_standard_yn = 'Y' AND p_article_type IS NULL) THEN
1117 IF (l_debug = 'Y') THEN
1118 Okc_Debug.Log('1300: - attribute ARTICLE_TYPE is null for standard article', 2);
1119 END IF;
1120 --dbms_output.put_line('attribute ARTICLE_TYPE is null for standard article');
1121 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ARTICLE_TYPE');
1122 l_return_status := G_RET_STS_ERROR;
1123 END IF;
1124 l_dummy_var := '?';
1125
1126 --dbms_output.put_line('Checking for uniqueness '||p_org_id||'*'||p_article_title||'*'|| p_article_id);
1127 l_row_found := TRUE;
1128 IF ( p_standard_yn = 'Y' ) THEN
1132 l_row_found := l_unq_csr%FOUND;
1129 IF p_org_id is NOT NULL and p_article_title is NOT NULL THEN
1130 OPEN l_unq_csr (p_org_id, p_article_id, p_article_title);
1131 FETCH l_unq_csr INTO l_dummy_var;
1133 CLOSE l_unq_csr;
1134 IF (l_row_found) THEN -- Duplicates exist
1135 IF (l_debug = 'Y') THEN
1136 Okc_Debug.Log('1300: - attribute ARTICLE_TITLE is not unique for org ', 2);
1137 END IF;
1138 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_DUP_TITLE_ORG');
1139 l_return_status := G_RET_STS_ERROR;
1140 END IF;
1141 END IF;
1142 END IF;
1143
1144 /*+++++++++++++End of hand code +++++++++++++++++++*/
1145 END IF;
1146
1147 IF (l_debug = 'Y') THEN
1148 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
1149 END IF;
1150 RETURN l_return_status ;
1151
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154
1155 IF (l_debug = 'Y') THEN
1156 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
1157 END IF;
1158
1159 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1160 p_msg_name => G_UNEXPECTED_ERROR,
1161 p_token1 => G_SQLCODE_TOKEN,
1162 p_token1_value => sqlcode,
1163 p_token2 => G_SQLERRM_TOKEN,
1164 p_token2_value => sqlerrm);
1165 -- Modified
1166 IF l_unq_csr%ISOPEN THEN
1167 CLOSE l_unq_csr;
1168 END IF;
1169 -- Modified
1170 RETURN G_RET_STS_UNEXP_ERROR ;
1171
1172 END Validate_Record;
1173
1174 ---------------------------------------------------------------------------
1175 -- PROCEDURE validate_row
1176 ---------------------------------------------------------------------------
1177 ---------------------------------------
1178 -- validate_row for:OKC_ARTICLES_ALL --
1179 ---------------------------------------
1180 PROCEDURE validate_row(
1181 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1182 p_import_action IN VARCHAR2 := NULL,
1183
1184
1185 x_return_status OUT NOCOPY VARCHAR2,
1186
1187 p_article_id IN NUMBER,
1188 p_article_title IN VARCHAR2,
1189 p_org_id IN NUMBER,
1190 p_article_number IN VARCHAR2,
1191 p_standard_yn IN VARCHAR2,
1192 p_article_intent IN VARCHAR2,
1193 p_article_language IN VARCHAR2,
1194 p_article_type IN VARCHAR2,
1195 p_orig_system_reference_code IN VARCHAR2,
1196 p_orig_system_reference_id1 IN VARCHAR2,
1197 p_orig_system_reference_id2 IN VARCHAR2,
1198 p_cz_transfer_status_flag IN VARCHAR2 := NULL,
1199 p_program_id IN NUMBER := NULL,
1200 p_program_login_id IN NUMBER := NULL,
1201 p_program_application_id IN NUMBER := NULL,
1202 p_request_id IN NUMBER := NULL,
1203
1204 p_attribute_category IN VARCHAR2 := NULL,
1205 p_attribute1 IN VARCHAR2 := NULL,
1206 p_attribute2 IN VARCHAR2 := NULL,
1207 p_attribute3 IN VARCHAR2 := NULL,
1208 p_attribute4 IN VARCHAR2 := NULL,
1209 p_attribute5 IN VARCHAR2 := NULL,
1210 p_attribute6 IN VARCHAR2 := NULL,
1211 p_attribute7 IN VARCHAR2 := NULL,
1212 p_attribute8 IN VARCHAR2 := NULL,
1213 p_attribute9 IN VARCHAR2 := NULL,
1214 p_attribute10 IN VARCHAR2 := NULL,
1215 p_attribute11 IN VARCHAR2 := NULL,
1216 p_attribute12 IN VARCHAR2 := NULL,
1217 p_attribute13 IN VARCHAR2 := NULL,
1218 p_attribute14 IN VARCHAR2 := NULL,
1219 p_attribute15 IN VARCHAR2 := NULL,
1220 p_object_version_number IN NUMBER := NULL
1221 ) IS
1222 l_article_title OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE;
1223 l_org_id OKC_ARTICLES_ALL.ORG_ID%TYPE;
1224 l_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
1225 l_standard_yn OKC_ARTICLES_ALL.STANDARD_YN%TYPE;
1226 l_article_intent OKC_ARTICLES_ALL.ARTICLE_INTENT%TYPE;
1227 l_article_language OKC_ARTICLES_ALL.ARTICLE_LANGUAGE%TYPE;
1228 l_article_type OKC_ARTICLES_ALL.ARTICLE_TYPE%TYPE;
1229 l_orig_system_reference_code OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
1230 l_orig_system_reference_id1 OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
1231 l_orig_system_reference_id2 OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
1232 l_cz_transfer_status_flag OKC_ARTICLES_ALL.CZ_TRANSFER_STATUS_FLAG%TYPE;
1233 l_program_id OKC_ARTICLES_ALL.PROGRAM_ID%TYPE;
1234 l_program_login_id OKC_ARTICLES_ALL.PROGRAM_LOGIN_ID%TYPE;
1235 l_program_application_id OKC_ARTICLES_ALL.PROGRAM_APPLICATION_ID%TYPE;
1236 l_request_id OKC_ARTICLES_ALL.REQUEST_ID%TYPE;
1237 l_attribute_category OKC_ARTICLES_ALL.ATTRIBUTE_CATEGORY%TYPE;
1238 l_attribute1 OKC_ARTICLES_ALL.ATTRIBUTE1%TYPE;
1239 l_attribute2 OKC_ARTICLES_ALL.ATTRIBUTE2%TYPE;
1240 l_attribute3 OKC_ARTICLES_ALL.ATTRIBUTE3%TYPE;
1241 l_attribute4 OKC_ARTICLES_ALL.ATTRIBUTE4%TYPE;
1242 l_attribute5 OKC_ARTICLES_ALL.ATTRIBUTE5%TYPE;
1243 l_attribute6 OKC_ARTICLES_ALL.ATTRIBUTE6%TYPE;
1244 l_attribute7 OKC_ARTICLES_ALL.ATTRIBUTE7%TYPE;
1245 l_attribute8 OKC_ARTICLES_ALL.ATTRIBUTE8%TYPE;
1249 l_attribute12 OKC_ARTICLES_ALL.ATTRIBUTE12%TYPE;
1246 l_attribute9 OKC_ARTICLES_ALL.ATTRIBUTE9%TYPE;
1247 l_attribute10 OKC_ARTICLES_ALL.ATTRIBUTE10%TYPE;
1248 l_attribute11 OKC_ARTICLES_ALL.ATTRIBUTE11%TYPE;
1250 l_attribute13 OKC_ARTICLES_ALL.ATTRIBUTE13%TYPE;
1251 l_attribute14 OKC_ARTICLES_ALL.ATTRIBUTE14%TYPE;
1252 l_attribute15 OKC_ARTICLES_ALL.ATTRIBUTE15%TYPE;
1253 l_object_version_number OKC_ARTICLES_ALL.OBJECT_VERSION_NUMBER%TYPE;
1254 l_created_by OKC_ARTICLES_ALL.CREATED_BY%TYPE;
1255 l_creation_date OKC_ARTICLES_ALL.CREATION_DATE%TYPE;
1256 l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
1257 l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
1258 l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
1259 BEGIN
1260
1261 IF (l_debug = 'Y') THEN
1262 Okc_Debug.Log('3100: Entered validate_row', 2);
1263 END IF;
1264
1265 -- Setting attributes
1266 IF p_import_action IS NOT NULL THEN
1267 l_article_title := p_article_title;
1268 l_org_id := p_org_id;
1269 l_article_number := p_article_number;
1270 l_standard_yn := p_standard_yn;
1271 l_article_intent := p_article_intent;
1272 l_article_language := p_article_language;
1273 l_article_type := p_article_type;
1274 l_orig_system_reference_code := p_orig_system_reference_code;
1275 l_orig_system_reference_id1 := p_orig_system_reference_id1;
1276 l_orig_system_reference_id2 := p_orig_system_reference_id2;
1277 l_cz_transfer_status_flag := p_cz_transfer_status_flag;
1278 l_program_id := p_program_id;
1279 l_program_login_id := p_program_login_id;
1280 l_program_application_id := p_program_application_id;
1281 l_request_id := p_request_id;
1282 l_attribute_category := p_attribute_category;
1283 l_attribute1 := p_attribute1;
1284 l_attribute2 := p_attribute2;
1285 l_attribute3 := p_attribute3;
1286 l_attribute4 := p_attribute4;
1287 l_attribute5 := p_attribute5;
1288 l_attribute6 := p_attribute6;
1289 l_attribute7 := p_attribute7;
1290 l_attribute8 := p_attribute8;
1291 l_attribute9 := p_attribute9;
1292 l_attribute10 := p_attribute10;
1293 l_attribute11 := p_attribute11;
1294 l_attribute12 := p_attribute12;
1295 l_attribute13 := p_attribute13;
1296 l_attribute14 := p_attribute14;
1297 l_attribute15 := p_attribute15;
1298 ELSE
1299 x_return_status := Set_Attributes(
1300 p_article_id => p_article_id,
1301 p_article_title => p_article_title,
1302 p_org_id => p_org_id,
1303 p_article_number => p_article_number,
1304 p_standard_yn => p_standard_yn,
1305 p_article_intent => p_article_intent,
1306 p_article_language => p_article_language,
1307 p_article_type => p_article_type,
1308 p_orig_system_reference_code => p_orig_system_reference_code,
1309 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1310 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1311 p_cz_transfer_status_flag => p_cz_transfer_status_flag,
1312 p_program_id => p_program_id,
1313 p_program_login_id => p_program_login_id,
1314 p_program_application_id => p_program_application_id,
1315 p_request_id => p_request_id,
1316 p_attribute_category => p_attribute_category,
1317 p_attribute1 => p_attribute1,
1318 p_attribute2 => p_attribute2,
1319 p_attribute3 => p_attribute3,
1320 p_attribute4 => p_attribute4,
1321 p_attribute5 => p_attribute5,
1322 p_attribute6 => p_attribute6,
1323 p_attribute7 => p_attribute7,
1324 p_attribute8 => p_attribute8,
1325 p_attribute9 => p_attribute9,
1326 p_attribute10 => p_attribute10,
1327 p_attribute11 => p_attribute11,
1328 p_attribute12 => p_attribute12,
1329 p_attribute13 => p_attribute13,
1330 p_attribute14 => p_attribute14,
1331 p_attribute15 => p_attribute15,
1332 p_object_version_number => p_object_version_number,
1333 x_article_title => l_article_title,
1334 x_org_id => l_org_id,
1335 x_article_number => l_article_number,
1336 x_standard_yn => l_standard_yn,
1337 x_article_intent => l_article_intent,
1338 x_article_language => l_article_language,
1339 x_article_type => l_article_type,
1340 x_orig_system_reference_code => l_orig_system_reference_code,
1341 x_orig_system_reference_id1 => l_orig_system_reference_id1,
1342 x_orig_system_reference_id2 => l_orig_system_reference_id2,
1343 x_cz_transfer_status_flag => l_cz_transfer_status_flag,
1344 x_program_id => l_program_id,
1345 x_program_login_id => l_program_login_id,
1346 x_program_application_id => l_program_application_id,
1347 x_request_id => l_request_id,
1351 x_attribute3 => l_attribute3,
1348 x_attribute_category => l_attribute_category,
1349 x_attribute1 => l_attribute1,
1350 x_attribute2 => l_attribute2,
1352 x_attribute4 => l_attribute4,
1353 x_attribute5 => l_attribute5,
1354 x_attribute6 => l_attribute6,
1355 x_attribute7 => l_attribute7,
1356 x_attribute8 => l_attribute8,
1357 x_attribute9 => l_attribute9,
1358 x_attribute10 => l_attribute10,
1359 x_attribute11 => l_attribute11,
1360 x_attribute12 => l_attribute12,
1361 x_attribute13 => l_attribute13,
1362 x_attribute14 => l_attribute14,
1363 x_attribute15 => l_attribute15
1364 );
1365 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1367 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1368 RAISE FND_API.G_EXC_ERROR;
1369 END IF;
1370 END IF;
1371
1372 -- Validate all non-missing attributes (Item Level Validation)
1373 x_return_status := Validate_Record(
1374 p_validation_level => p_validation_level,
1375 p_import_action => p_import_action,
1376 p_article_id => p_article_id,
1377 p_article_title => l_article_title,
1378 p_org_id => l_org_id,
1379 p_article_number => l_article_number,
1380 p_standard_yn => l_standard_yn,
1381 p_article_intent => l_article_intent,
1382 p_article_language => l_article_language,
1383 p_article_type => l_article_type,
1384 p_orig_system_reference_code => l_orig_system_reference_code,
1385 p_orig_system_reference_id1 => l_orig_system_reference_id1,
1386 p_orig_system_reference_id2 => l_orig_system_reference_id2,
1387 p_cz_transfer_status_flag => l_cz_transfer_status_flag,
1388 p_program_id => l_program_id,
1389 p_program_login_id => l_program_login_id,
1390 p_program_application_id => l_program_application_id,
1391 p_request_id => l_request_id,
1392 p_attribute_category => l_attribute_category,
1393 p_attribute1 => l_attribute1,
1394 p_attribute2 => l_attribute2,
1395 p_attribute3 => l_attribute3,
1396 p_attribute4 => l_attribute4,
1397 p_attribute5 => l_attribute5,
1398 p_attribute6 => l_attribute6,
1399 p_attribute7 => l_attribute7,
1400 p_attribute8 => l_attribute8,
1401 p_attribute9 => l_attribute9,
1402 p_attribute10 => l_attribute10,
1403 p_attribute11 => l_attribute11,
1404 p_attribute12 => l_attribute12,
1405 p_attribute13 => l_attribute13,
1406 p_attribute14 => l_attribute14,
1407 p_attribute15 => l_attribute15
1408 );
1409
1410 IF (l_debug = 'Y') THEN
1411 Okc_Debug.Log('3200: Leaving validate_row', 2);
1412 END IF;
1413
1414 EXCEPTION
1415 WHEN FND_API.G_EXC_ERROR THEN
1416 IF (l_debug = 'Y') THEN
1417 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
1418 END IF;
1419 x_return_status := G_RET_STS_ERROR;
1420
1421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1422 IF (l_debug = 'Y') THEN
1423 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1424 END IF;
1425 x_return_status := G_RET_STS_UNEXP_ERROR;
1426
1427 WHEN OTHERS THEN
1428 IF (l_debug = 'Y') THEN
1429 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
1430 END IF;
1431 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1432 p_msg_name => G_UNEXPECTED_ERROR,
1433 p_token1 => G_SQLCODE_TOKEN,
1434 p_token1_value => sqlcode,
1435 p_token2 => G_SQLERRM_TOKEN,
1436 p_token2_value => sqlerrm);
1437 x_return_status := G_RET_STS_UNEXP_ERROR;
1438
1439 END Validate_Row;
1440
1441 ---------------------------------------------------------------------------
1442 -- PROCEDURE Insert_Row
1443 ---------------------------------------------------------------------------
1444 -------------------------------------
1445 -- Insert_Row for:OKC_ARTICLES_ALL --
1446 -------------------------------------
1447 FUNCTION Insert_Row(
1448 p_article_id IN NUMBER,
1449 p_article_title IN VARCHAR2,
1450 p_org_id IN NUMBER,
1451 p_article_number IN VARCHAR2,
1452 p_standard_yn IN VARCHAR2,
1453 p_article_intent IN VARCHAR2,
1454 p_article_language IN VARCHAR2,
1455 p_article_type IN VARCHAR2,
1456 p_orig_system_reference_code IN VARCHAR2,
1457 p_orig_system_reference_id1 IN VARCHAR2,
1458 p_orig_system_reference_id2 IN VARCHAR2,
1459 p_cz_transfer_status_flag IN VARCHAR2,
1460 p_program_id IN NUMBER,
1461 p_program_login_id IN NUMBER,
1462 p_program_application_id IN NUMBER,
1463 p_request_id IN NUMBER,
1464 p_attribute_category IN VARCHAR2,
1465 p_attribute1 IN VARCHAR2,
1466 p_attribute2 IN VARCHAR2,
1467 p_attribute3 IN VARCHAR2,
1471 p_attribute7 IN VARCHAR2,
1468 p_attribute4 IN VARCHAR2,
1469 p_attribute5 IN VARCHAR2,
1470 p_attribute6 IN VARCHAR2,
1472 p_attribute8 IN VARCHAR2,
1473 p_attribute9 IN VARCHAR2,
1474 p_attribute10 IN VARCHAR2,
1475 p_attribute11 IN VARCHAR2,
1476 p_attribute12 IN VARCHAR2,
1477 p_attribute13 IN VARCHAR2,
1478 p_attribute14 IN VARCHAR2,
1479 p_attribute15 IN VARCHAR2,
1480 p_object_version_number IN NUMBER,
1481 p_created_by IN NUMBER,
1482 p_creation_date IN DATE,
1483 p_last_updated_by IN NUMBER,
1484 p_last_update_login IN NUMBER,
1485 p_last_update_date IN DATE
1486
1487 ) RETURN VARCHAR2 IS
1488
1489
1490 l_program_id OKC_ARTICLES_ALL.PROGRAM_ID%TYPE;
1491 l_program_login_id OKC_ARTICLES_ALL.PROGRAM_LOGIN_ID%TYPE;
1492 l_program_appl_id OKC_ARTICLES_ALL.PROGRAM_APPLICATION_ID%TYPE;
1493 l_request_id OKC_ARTICLES_ALL.REQUEST_ID%TYPE;
1494
1495 BEGIN
1496
1497 IF (l_debug = 'Y') THEN
1498 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
1499 END IF;
1500
1501
1502 IF FND_GLOBAL.CONC_PROGRAM_ID = -1 THEN
1503 l_program_id := NULL;
1504 l_program_login_id := NULL;
1505 ELSE
1506 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1507 l_program_login_id := FND_GLOBAL.CONC_LOGIN_ID;
1508 END IF;
1509 IF FND_GLOBAL.PROG_APPL_ID = -1 THEN
1510 l_program_appl_id := NULL;
1511 ELSE
1512 l_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
1513 END IF;
1514 IF FND_GLOBAL.CONC_REQUEST_ID = -1 THEN
1515 l_request_id := NULL;
1516 ELSE
1517 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1518 END IF;
1519
1520
1521 INSERT INTO OKC_ARTICLES_ALL(
1522 ARTICLE_ID,
1523 ARTICLE_TITLE,
1524 ORG_ID,
1525 ARTICLE_NUMBER,
1526 STANDARD_YN,
1527 ARTICLE_INTENT,
1528 ARTICLE_LANGUAGE,
1529 ARTICLE_TYPE,
1530 ORIG_SYSTEM_REFERENCE_CODE,
1531 ORIG_SYSTEM_REFERENCE_ID1,
1532 ORIG_SYSTEM_REFERENCE_ID2,
1533 CZ_TRANSFER_STATUS_FLAG,
1534 PROGRAM_ID,
1535 PROGRAM_LOGIN_ID,
1536 PROGRAM_APPLICATION_ID,
1537 REQUEST_ID,
1538 ATTRIBUTE_CATEGORY,
1539 ATTRIBUTE1,
1540 ATTRIBUTE2,
1541 ATTRIBUTE3,
1542 ATTRIBUTE4,
1543 ATTRIBUTE5,
1544 ATTRIBUTE6,
1545 ATTRIBUTE7,
1546 ATTRIBUTE8,
1547 ATTRIBUTE9,
1548 ATTRIBUTE10,
1549 ATTRIBUTE11,
1550 ATTRIBUTE12,
1551 ATTRIBUTE13,
1552 ATTRIBUTE14,
1553 ATTRIBUTE15,
1554 OBJECT_VERSION_NUMBER,
1555 CREATED_BY,
1556 CREATION_DATE,
1557 LAST_UPDATED_BY,
1558 LAST_UPDATE_LOGIN,
1559 LAST_UPDATE_DATE)
1560 VALUES (
1561 p_article_id,
1562 p_article_title,
1563 p_org_id,
1564 p_article_number,
1565 p_standard_yn,
1566 p_article_intent,
1567 nvl(p_article_language,USERENV('LANG')),
1568 p_article_type,
1569 p_orig_system_reference_code,
1570 p_orig_system_reference_id1,
1571 p_orig_system_reference_id2,
1572 'N', -- Default value for cz_transfer_status_flag
1573 l_program_id,
1574 l_program_login_id,
1575 l_program_appl_id,
1576 l_request_id,
1577 p_attribute_category,
1578 p_attribute1,
1579 p_attribute2,
1580 p_attribute3,
1581 p_attribute4,
1582 p_attribute5,
1583 p_attribute6,
1584 p_attribute7,
1585 p_attribute8,
1586 p_attribute9,
1587 p_attribute10,
1588 p_attribute11,
1589 p_attribute12,
1590 p_attribute13,
1591 p_attribute14,
1592 p_attribute15,
1593 p_object_version_number,
1594 p_created_by,
1595 p_creation_date,
1596 p_last_updated_by,
1597 p_last_update_login,
1598 p_last_update_date);
1599
1600 IF (l_debug = 'Y') THEN
1601 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
1602 END IF;
1603
1604 RETURN( G_RET_STS_SUCCESS );
1605
1606 EXCEPTION
1607 WHEN OTHERS THEN
1608
1609 IF (l_debug = 'Y') THEN
1610 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
1611 END IF;
1612
1613 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1614 p_msg_name => G_UNEXPECTED_ERROR,
1615 p_token1 => G_SQLCODE_TOKEN,
1616 p_token1_value => sqlcode,
1617 p_token2 => G_SQLERRM_TOKEN,
1618 p_token2_value => sqlerrm);
1619
1620 RETURN( G_RET_STS_UNEXP_ERROR );
1621
1622 END Insert_Row;
1623
1624
1625 -------------------------------------
1626 -- Insert_Row for:OKC_ARTICLES_ALL --
1627 -------------------------------------
1628 PROCEDURE Insert_Row(
1629 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1630 x_return_status OUT NOCOPY VARCHAR2,
1631
1632 p_article_id IN NUMBER := NULL,
1633 p_article_title IN VARCHAR2,
1637 p_article_intent IN VARCHAR2,
1634 p_org_id IN NUMBER,
1635 p_article_number IN VARCHAR2,
1636 p_standard_yn IN VARCHAR2,
1638 p_article_language IN VARCHAR2,
1639 p_article_type IN VARCHAR2,
1640 p_orig_system_reference_code IN VARCHAR2,
1641 p_orig_system_reference_id1 IN VARCHAR2,
1642 p_orig_system_reference_id2 IN VARCHAR2,
1643 p_cz_transfer_status_flag IN VARCHAR2,
1644 p_program_id IN NUMBER := NULL,
1645 p_program_login_id IN NUMBER := NULL,
1646 p_program_application_id IN NUMBER := NULL,
1647 p_request_id IN NUMBER := NULL,
1648
1649 p_attribute_category IN VARCHAR2 := NULL,
1650 p_attribute1 IN VARCHAR2 := NULL,
1651 p_attribute2 IN VARCHAR2 := NULL,
1652 p_attribute3 IN VARCHAR2 := NULL,
1653 p_attribute4 IN VARCHAR2 := NULL,
1654 p_attribute5 IN VARCHAR2 := NULL,
1655 p_attribute6 IN VARCHAR2 := NULL,
1656 p_attribute7 IN VARCHAR2 := NULL,
1657 p_attribute8 IN VARCHAR2 := NULL,
1658 p_attribute9 IN VARCHAR2 := NULL,
1659 p_attribute10 IN VARCHAR2 := NULL,
1660 p_attribute11 IN VARCHAR2 := NULL,
1661 p_attribute12 IN VARCHAR2 := NULL,
1662 p_attribute13 IN VARCHAR2 := NULL,
1663 p_attribute14 IN VARCHAR2 := NULL,
1664 p_attribute15 IN VARCHAR2 := NULL,
1665
1666 x_article_number OUT NOCOPY VARCHAR2,
1667 x_article_id OUT NOCOPY NUMBER
1668
1669 ) IS
1670
1671 l_object_version_number OKC_ARTICLES_ALL.OBJECT_VERSION_NUMBER%TYPE;
1672 l_created_by OKC_ARTICLES_ALL.CREATED_BY%TYPE;
1673 l_creation_date OKC_ARTICLES_ALL.CREATION_DATE%TYPE;
1674 l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
1675 l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
1676 l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
1677
1678
1679 BEGIN
1680
1681 x_return_status := G_RET_STS_SUCCESS;
1682
1683 IF (l_debug = 'Y') THEN
1684 Okc_Debug.Log('4200: Entered Insert_Row', 2);
1685 END IF;
1686
1687 --- Setting item attributes
1688 -- Set primary key value
1689 IF( p_article_id IS NULL ) THEN
1690 x_return_status := Get_Seq_Id(
1691 p_article_id => p_article_id,
1692 x_article_id => x_article_id
1693 );
1694 --- If any errors happen abort API
1695 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1698 RAISE FND_API.G_EXC_ERROR;
1699 END IF;
1700 ELSE
1701 x_article_id := p_article_id;
1702 END IF;
1703 -- Set Internal columns
1704 l_object_version_number := 1;
1705 l_creation_date := Sysdate;
1706 l_created_by := Fnd_Global.User_Id;
1707 l_last_update_date := l_creation_date;
1708 l_last_updated_by := l_created_by;
1709 l_last_update_login := Fnd_Global.Login_Id;
1710 x_article_number := p_article_number;
1711
1712 --- Validate all non-missing attributes
1713 x_return_status := Validate_Record(
1714 p_validation_level => p_validation_level,
1715 p_article_id => x_article_id,
1716 p_article_title => p_article_title,
1717 p_org_id => p_org_id,
1718 p_article_number => p_article_number,
1719 p_standard_yn => p_standard_yn,
1720 p_article_intent => p_article_intent,
1721 p_article_language => p_article_language,
1722 p_article_type => p_article_type,
1723 p_orig_system_reference_code => p_orig_system_reference_code,
1724 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1725 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1726 p_cz_transfer_status_flag => p_cz_transfer_status_flag,
1727 p_program_id => p_program_id,
1728 p_program_login_id => p_program_login_id,
1729 p_program_application_id => p_program_application_id,
1730 p_request_id => p_request_id,
1731 p_attribute_category => p_attribute_category,
1732 p_attribute1 => p_attribute1,
1733 p_attribute2 => p_attribute2,
1734 p_attribute3 => p_attribute3,
1735 p_attribute4 => p_attribute4,
1736 p_attribute5 => p_attribute5,
1737 p_attribute6 => p_attribute6,
1738 p_attribute7 => p_attribute7,
1739 p_attribute8 => p_attribute8,
1740 p_attribute9 => p_attribute9,
1741 p_attribute10 => p_attribute10,
1742 p_attribute11 => p_attribute11,
1743 p_attribute12 => p_attribute12,
1744 p_attribute13 => p_attribute13,
1745 p_attribute14 => p_attribute14,
1746 p_attribute15 => p_attribute15
1747 );
1748 --- If any errors happen abort API
1749 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1751 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1752 RAISE FND_API.G_EXC_ERROR;
1753 END IF;
1754
1755 --------------------------------------------
1756 -- Call the internal Insert_Row for each child record
1757 --------------------------------------------
1758 IF (l_debug = 'Y') THEN
1762 x_return_status := Insert_Row(
1759 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
1760 END IF;
1761
1763 p_article_id => x_article_id,
1764 p_article_title => p_article_title,
1765 p_org_id => p_org_id,
1766 p_article_number => p_article_number,
1767 p_standard_yn => p_standard_yn,
1768 p_article_intent => p_article_intent,
1769 p_article_language => p_article_language,
1770 p_article_type => p_article_type,
1771 p_orig_system_reference_code => p_orig_system_reference_code,
1772 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1773 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1774 p_cz_transfer_status_flag => p_cz_transfer_status_flag,
1775 p_program_id => p_program_id,
1776 p_program_login_id => p_program_login_id,
1777 p_program_application_id => p_program_application_id,
1778 p_request_id => p_request_id,
1779 p_attribute_category => p_attribute_category,
1780 p_attribute1 => p_attribute1,
1781 p_attribute2 => p_attribute2,
1782 p_attribute3 => p_attribute3,
1783 p_attribute4 => p_attribute4,
1784 p_attribute5 => p_attribute5,
1785 p_attribute6 => p_attribute6,
1786 p_attribute7 => p_attribute7,
1787 p_attribute8 => p_attribute8,
1788 p_attribute9 => p_attribute9,
1789 p_attribute10 => p_attribute10,
1790 p_attribute11 => p_attribute11,
1791 p_attribute12 => p_attribute12,
1792 p_attribute13 => p_attribute13,
1793 p_attribute14 => p_attribute14,
1794 p_attribute15 => p_attribute15,
1795 p_object_version_number => l_object_version_number,
1796 p_created_by => l_created_by,
1797 p_creation_date => l_creation_date,
1798 p_last_updated_by => l_last_updated_by,
1799 p_last_update_login => l_last_update_login,
1800 p_last_update_date => l_last_update_date
1801 );
1802 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1804 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1805 RAISE FND_API.G_EXC_ERROR;
1806 END IF;
1807
1808
1809
1810 IF (l_debug = 'Y') THEN
1811 Okc_Debug.Log('4500: Leaving Insert_Row', 2);
1812 END IF;
1813
1814 EXCEPTION
1815 WHEN FND_API.G_EXC_ERROR THEN
1816 IF (l_debug = 'Y') THEN
1817 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
1818 END IF;
1819 x_return_status := G_RET_STS_ERROR;
1820
1821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1822 IF (l_debug = 'Y') THEN
1823 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1824 END IF;
1825 x_return_status := G_RET_STS_UNEXP_ERROR;
1826
1827 WHEN OTHERS THEN
1828 IF (l_debug = 'Y') THEN
1829 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
1830 END IF;
1831 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1832 p_msg_name => G_UNEXPECTED_ERROR,
1833 p_token1 => G_SQLCODE_TOKEN,
1834 p_token1_value => sqlcode,
1835 p_token2 => G_SQLERRM_TOKEN,
1836 p_token2_value => sqlerrm);
1837 x_return_status := G_RET_STS_UNEXP_ERROR;
1838
1839 END Insert_Row;
1840 ---------------------------------------------------------------------------
1841 -- PROCEDURE Lock_Row
1842 ---------------------------------------------------------------------------
1843 -----------------------------------
1844 -- Lock_Row for:OKC_ARTICLES_ALL --
1845 -----------------------------------
1846 FUNCTION Lock_Row(
1847 p_article_id IN NUMBER,
1848 p_object_version_number IN NUMBER
1849 ) RETURN VARCHAR2 IS
1850
1851 E_Resource_Busy EXCEPTION;
1852 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1853
1854 CURSOR lock_csr (cp_article_id NUMBER, cp_object_version_number NUMBER) IS
1855 SELECT object_version_number
1856 FROM OKC_ARTICLES_ALL
1857 WHERE ARTICLE_ID = cp_article_id
1858 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1859 FOR UPDATE OF object_version_number NOWAIT;
1860
1861 CURSOR lchk_csr (cp_article_id NUMBER) IS
1862 SELECT object_version_number
1863 FROM OKC_ARTICLES_ALL
1864 WHERE ARTICLE_ID = cp_article_id;
1865
1866 l_return_status VARCHAR2(1);
1867
1868 l_object_version_number OKC_ARTICLES_ALL.OBJECT_VERSION_NUMBER%TYPE;
1869
1870 l_row_notfound BOOLEAN := FALSE;
1871 BEGIN
1872
1873 IF (l_debug = 'Y') THEN
1874 Okc_Debug.Log('4900: Entered Lock_Row', 2);
1875 END IF;
1876
1877
1878 BEGIN
1879
1880 OPEN lock_csr( p_article_id, p_object_version_number );
1881 FETCH lock_csr INTO l_object_version_number;
1882 l_row_notfound := lock_csr%NOTFOUND;
1883 CLOSE lock_csr;
1884
1885 EXCEPTION
1886 WHEN E_Resource_Busy THEN
1887
1888 IF (l_debug = 'Y') THEN
1889 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
1890 END IF;
1891
1895 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1892 IF (lock_csr%ISOPEN) THEN
1893 CLOSE lock_csr;
1894 END IF;
1896 RETURN( G_RET_STS_ERROR );
1897 END;
1898
1899 IF ( l_row_notfound ) THEN
1900 l_return_status := G_RET_STS_ERROR;
1901
1902 OPEN lchk_csr(p_article_id);
1903 FETCH lchk_csr INTO l_object_version_number;
1904 l_row_notfound := lchk_csr%NOTFOUND;
1905 CLOSE lchk_csr;
1906
1907 IF (l_row_notfound) THEN
1908 Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
1909 ELSIF l_object_version_number > p_object_version_number THEN
1910 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1911 ELSIF l_object_version_number = -1 THEN
1912 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1913 ELSE -- it can be the only above condition. It can happen after restore version
1914 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1915 END IF;
1916 ELSE
1917 l_return_status := G_RET_STS_SUCCESS;
1918 END IF;
1919
1920 IF (l_debug = 'Y') THEN
1921 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
1922 END IF;
1923
1924 RETURN( l_return_status );
1925
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928
1929 IF (lock_csr%ISOPEN) THEN
1930 CLOSE lock_csr;
1931 END IF;
1932 IF (lchk_csr%ISOPEN) THEN
1933 CLOSE lchk_csr;
1934 END IF;
1935
1936 IF (l_debug = 'Y') THEN
1937 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1938 END IF;
1939
1940 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1941 p_msg_name => G_UNEXPECTED_ERROR,
1942 p_token1 => G_SQLCODE_TOKEN,
1943 p_token1_value => sqlcode,
1944 p_token2 => G_SQLERRM_TOKEN,
1945 p_token2_value => sqlerrm);
1946
1947 RETURN( G_RET_STS_UNEXP_ERROR );
1948 END Lock_Row;
1949
1950 -----------------------------------
1951 -- Lock_Row for:OKC_ARTICLES_ALL --
1952 -----------------------------------
1953 PROCEDURE Lock_Row(
1954 x_return_status OUT NOCOPY VARCHAR2,
1955
1956 p_article_id IN NUMBER,
1957 p_object_version_number IN NUMBER
1958 ) IS
1959 BEGIN
1960
1961 IF (l_debug = 'Y') THEN
1962 Okc_Debug.Log('5700: Entered Lock_Row', 2);
1963 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
1964 END IF;
1965
1966 --------------------------------------------
1967 -- Call the LOCK_ROW for each _B child record
1968 --------------------------------------------
1969 x_return_status := Lock_Row(
1970 p_article_id => p_article_id,
1971 p_object_version_number => p_object_version_number
1972 );
1973 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1975 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1976 RAISE FND_API.G_EXC_ERROR;
1977 END IF;
1978
1979
1980
1981 IF (l_debug = 'Y') THEN
1982 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
1983 END IF;
1984
1985 EXCEPTION
1986 WHEN FND_API.G_EXC_ERROR THEN
1987 IF (l_debug = 'Y') THEN
1988 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
1989 END IF;
1990 x_return_status := G_RET_STS_ERROR;
1991
1992 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1993 IF (l_debug = 'Y') THEN
1994 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1995 END IF;
1996 x_return_status := G_RET_STS_UNEXP_ERROR;
1997
1998 WHEN OTHERS THEN
1999 IF (l_debug = 'Y') THEN
2000 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
2001 END IF;
2002 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2003 p_msg_name => G_UNEXPECTED_ERROR,
2004 p_token1 => G_SQLCODE_TOKEN,
2005 p_token1_value => sqlcode,
2006 p_token2 => G_SQLERRM_TOKEN,
2007 p_token2_value => sqlerrm);
2008 x_return_status := G_RET_STS_UNEXP_ERROR;
2009
2010 END Lock_Row;
2011 ---------------------------------------------------------------------------
2012 -- PROCEDURE Update_Row
2013 ---------------------------------------------------------------------------
2014 -------------------------------------
2015 -- Update_Row for:OKC_ARTICLES_ALL --
2016 -------------------------------------
2017 FUNCTION Update_Row(
2018 p_article_id IN NUMBER,
2019 p_article_title IN VARCHAR2,
2020 p_org_id IN NUMBER,
2021 p_article_number IN VARCHAR2,
2022 p_standard_yn IN VARCHAR2,
2023 p_article_intent IN VARCHAR2,
2024 p_article_language IN VARCHAR2,
2025 p_article_type IN VARCHAR2,
2026 p_orig_system_reference_code IN VARCHAR2,
2027 p_orig_system_reference_id1 IN VARCHAR2,
2028 p_orig_system_reference_id2 IN VARCHAR2,
2029 p_cz_transfer_status_flag IN VARCHAR2,
2030 p_program_id IN NUMBER,
2031 p_program_login_id IN NUMBER,
2032 p_program_application_id IN NUMBER,
2033 p_request_id IN NUMBER,
2034 p_attribute_category IN VARCHAR2,
2035 p_attribute1 IN VARCHAR2,
2036 p_attribute2 IN VARCHAR2,
2040 p_attribute6 IN VARCHAR2,
2037 p_attribute3 IN VARCHAR2,
2038 p_attribute4 IN VARCHAR2,
2039 p_attribute5 IN VARCHAR2,
2041 p_attribute7 IN VARCHAR2,
2042 p_attribute8 IN VARCHAR2,
2043 p_attribute9 IN VARCHAR2,
2044 p_attribute10 IN VARCHAR2,
2045 p_attribute11 IN VARCHAR2,
2046 p_attribute12 IN VARCHAR2,
2047 p_attribute13 IN VARCHAR2,
2048 p_attribute14 IN VARCHAR2,
2049 p_attribute15 IN VARCHAR2,
2050 p_object_version_number IN NUMBER,
2051 p_created_by IN NUMBER,
2052 p_creation_date IN DATE,
2053 p_last_updated_by IN NUMBER,
2054 p_last_update_login IN NUMBER,
2055 p_last_update_date IN DATE
2056 ) RETURN VARCHAR2 IS
2057
2058
2059 l_program_id OKC_ARTICLES_ALL.PROGRAM_ID%TYPE;
2060 l_program_login_id OKC_ARTICLES_ALL.PROGRAM_LOGIN_ID%TYPE;
2061 l_program_appl_id OKC_ARTICLES_ALL.PROGRAM_APPLICATION_ID%TYPE;
2062 l_request_id OKC_ARTICLES_ALL.REQUEST_ID%TYPE;
2063
2064 BEGIN
2065
2066 IF (l_debug = 'Y') THEN
2067 Okc_Debug.Log('6400: Entered Update_Row', 2);
2068 END IF;
2069
2070
2071 IF FND_GLOBAL.CONC_PROGRAM_ID = -1 THEN
2072 l_program_id := NULL;
2073 l_program_login_id := NULL;
2074 ELSE
2075 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
2076 l_program_login_id := FND_GLOBAL.CONC_LOGIN_ID;
2077 END IF;
2078 IF FND_GLOBAL.PROG_APPL_ID = -1 THEN
2079 l_program_appl_id := NULL;
2080 ELSE
2081 l_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
2082 END IF;
2083 IF FND_GLOBAL.CONC_REQUEST_ID = -1 THEN
2084 l_request_id := NULL;
2085 ELSE
2086 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2087 END IF;
2088
2089 UPDATE OKC_ARTICLES_ALL
2090 SET ARTICLE_TITLE = p_article_title,
2091 -- ORG_ID = p_org_id,
2092 ARTICLE_NUMBER = p_article_number,
2093 STANDARD_YN = p_standard_yn,
2094 ARTICLE_INTENT = p_article_intent,
2095 -- ARTICLE_LANGUAGE = p_article_language,
2096 ARTICLE_TYPE = p_article_type,
2097 ORIG_SYSTEM_REFERENCE_CODE = p_orig_system_reference_code,
2098 ORIG_SYSTEM_REFERENCE_ID1 = p_orig_system_reference_id1,
2099 ORIG_SYSTEM_REFERENCE_ID2 = p_orig_system_reference_id2,
2100 CZ_TRANSFER_STATUS_FLAG = p_cz_transfer_status_flag,
2101 PROGRAM_ID = l_program_id,
2102 REQUEST_ID = l_request_id,
2103 PROGRAM_LOGIN_ID = l_program_login_id,
2104 PROGRAM_APPLICATION_ID = l_program_appl_id,
2105 ATTRIBUTE_CATEGORY = p_attribute_category,
2106 ATTRIBUTE1 = p_attribute1,
2107 ATTRIBUTE2 = p_attribute2,
2108 ATTRIBUTE3 = p_attribute3,
2109 ATTRIBUTE4 = p_attribute4,
2110 ATTRIBUTE5 = p_attribute5,
2111 ATTRIBUTE6 = p_attribute6,
2112 ATTRIBUTE7 = p_attribute7,
2113 ATTRIBUTE8 = p_attribute8,
2114 ATTRIBUTE9 = p_attribute9,
2115 ATTRIBUTE10 = p_attribute10,
2116 ATTRIBUTE11 = p_attribute11,
2117 ATTRIBUTE12 = p_attribute12,
2118 ATTRIBUTE13 = p_attribute13,
2119 ATTRIBUTE14 = p_attribute14,
2120 ATTRIBUTE15 = p_attribute15,
2121 OBJECT_VERSION_NUMBER = p_object_version_number,
2122 LAST_UPDATED_BY = p_last_updated_by,
2123 LAST_UPDATE_LOGIN = p_last_update_login,
2124 LAST_UPDATE_DATE = p_last_update_date
2125 WHERE ARTICLE_ID = p_article_id;
2126
2127 IF (l_debug = 'Y') THEN
2128 Okc_Debug.Log('6500: Leaving Update_Row', 2);
2129 END IF;
2130
2131 -- if article_title updated - notify context index
2132 if pending then
2133 update OKC_ARTICLE_VERSIONS
2134 set article_text = article_text
2135 where article_id = p_article_id;
2136 end if;
2137
2138 RETURN G_RET_STS_SUCCESS ;
2139
2140 EXCEPTION
2141 WHEN OTHERS THEN
2142
2143 IF (l_debug = 'Y') THEN
2144 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
2145 END IF;
2146
2147 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2148 p_msg_name => G_UNEXPECTED_ERROR,
2149 p_token1 => G_SQLCODE_TOKEN,
2150 p_token1_value => sqlcode,
2151 p_token2 => G_SQLERRM_TOKEN,
2152 p_token2_value => sqlerrm);
2153
2154 RETURN G_RET_STS_UNEXP_ERROR ;
2155
2156 END Update_Row;
2157
2158 -------------------------------------
2159 -- Update_Row for:OKC_ARTICLES_ALL --
2160 -------------------------------------
2161 PROCEDURE Update_Row(
2162 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2163
2164 x_return_status OUT NOCOPY VARCHAR2,
2165 x_article_intent OUT NOCOPY VARCHAR2,
2166
2167 p_article_id IN NUMBER,
2168 p_article_title IN VARCHAR2,
2169 p_org_id IN NUMBER,
2170 p_article_number IN VARCHAR2,
2174 p_article_type IN VARCHAR2,
2171 p_standard_yn IN VARCHAR2,
2172 p_article_intent IN VARCHAR2,
2173 p_article_language IN VARCHAR2,
2175 p_orig_system_reference_code IN VARCHAR2,
2176 p_orig_system_reference_id1 IN VARCHAR2,
2177 p_orig_system_reference_id2 IN VARCHAR2,
2178 p_cz_transfer_status_flag IN VARCHAR2,
2179 p_program_id IN NUMBER := NULL,
2180 p_program_login_id IN NUMBER := NULL,
2181 p_program_application_id IN NUMBER := NULL,
2182 p_request_id IN NUMBER := NULL,
2183
2184 p_attribute_category IN VARCHAR2 := NULL,
2185 p_attribute1 IN VARCHAR2 := NULL,
2186 p_attribute2 IN VARCHAR2 := NULL,
2187 p_attribute3 IN VARCHAR2 := NULL,
2188 p_attribute4 IN VARCHAR2 := NULL,
2189 p_attribute5 IN VARCHAR2 := NULL,
2190 p_attribute6 IN VARCHAR2 := NULL,
2191 p_attribute7 IN VARCHAR2 := NULL,
2192 p_attribute8 IN VARCHAR2 := NULL,
2193 p_attribute9 IN VARCHAR2 := NULL,
2194 p_attribute10 IN VARCHAR2 := NULL,
2195 p_attribute11 IN VARCHAR2 := NULL,
2196 p_attribute12 IN VARCHAR2 := NULL,
2197 p_attribute13 IN VARCHAR2 := NULL,
2198 p_attribute14 IN VARCHAR2 := NULL,
2199 p_attribute15 IN VARCHAR2 := NULL,
2200
2201 p_object_version_number IN NUMBER
2202
2203 ) IS
2204
2205 l_article_title OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE;
2206 l_org_id OKC_ARTICLES_ALL.ORG_ID%TYPE;
2207 l_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
2208 l_standard_yn OKC_ARTICLES_ALL.STANDARD_YN%TYPE;
2209 l_article_intent OKC_ARTICLES_ALL.ARTICLE_INTENT%TYPE;
2210 l_article_language OKC_ARTICLES_ALL.ARTICLE_LANGUAGE%TYPE;
2211 l_article_type OKC_ARTICLES_ALL.ARTICLE_TYPE%TYPE;
2212 l_orig_system_reference_code OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
2213 l_orig_system_reference_id1 OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
2214 l_orig_system_reference_id2 OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
2215 l_cz_transfer_status_flag OKC_ARTICLES_ALL.CZ_TRANSFER_STATUS_FLAG%TYPE;
2216 l_program_id OKC_ARTICLES_ALL.PROGRAM_ID%TYPE;
2217 l_program_login_id OKC_ARTICLES_ALL.PROGRAM_LOGIN_ID%TYPE;
2218 l_program_application_id OKC_ARTICLES_ALL.PROGRAM_APPLICATION_ID%TYPE;
2219 l_request_id OKC_ARTICLES_ALL.REQUEST_ID%TYPE;
2220 l_attribute_category OKC_ARTICLES_ALL.ATTRIBUTE_CATEGORY%TYPE;
2221 l_attribute1 OKC_ARTICLES_ALL.ATTRIBUTE1%TYPE;
2222 l_attribute2 OKC_ARTICLES_ALL.ATTRIBUTE2%TYPE;
2223 l_attribute3 OKC_ARTICLES_ALL.ATTRIBUTE3%TYPE;
2224 l_attribute4 OKC_ARTICLES_ALL.ATTRIBUTE4%TYPE;
2225 l_attribute5 OKC_ARTICLES_ALL.ATTRIBUTE5%TYPE;
2226 l_attribute6 OKC_ARTICLES_ALL.ATTRIBUTE6%TYPE;
2227 l_attribute7 OKC_ARTICLES_ALL.ATTRIBUTE7%TYPE;
2228 l_attribute8 OKC_ARTICLES_ALL.ATTRIBUTE8%TYPE;
2229 l_attribute9 OKC_ARTICLES_ALL.ATTRIBUTE9%TYPE;
2230 l_attribute10 OKC_ARTICLES_ALL.ATTRIBUTE10%TYPE;
2231 l_attribute11 OKC_ARTICLES_ALL.ATTRIBUTE11%TYPE;
2232 l_attribute12 OKC_ARTICLES_ALL.ATTRIBUTE12%TYPE;
2233 l_attribute13 OKC_ARTICLES_ALL.ATTRIBUTE13%TYPE;
2234 l_attribute14 OKC_ARTICLES_ALL.ATTRIBUTE14%TYPE;
2235 l_attribute15 OKC_ARTICLES_ALL.ATTRIBUTE15%TYPE;
2236 l_object_version_number OKC_ARTICLES_ALL.OBJECT_VERSION_NUMBER%TYPE;
2237 l_created_by OKC_ARTICLES_ALL.CREATED_BY%TYPE;
2238 l_creation_date OKC_ARTICLES_ALL.CREATION_DATE%TYPE;
2239 l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
2240 l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
2241 l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
2242
2243 BEGIN
2244
2245 IF (l_debug = 'Y') THEN
2246 Okc_Debug.Log('7000: Entered Update_Row', 2);
2247 Okc_Debug.Log('7100: Locking _B row', 2);
2248 END IF;
2249
2250 x_return_status := Lock_row(
2251 p_article_id => p_article_id,
2252 p_object_version_number => p_object_version_number
2253 );
2254 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2256 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2257 RAISE FND_API.G_EXC_ERROR;
2258 END IF;
2259
2260
2261 IF (l_debug = 'Y') THEN
2262 Okc_Debug.Log('7300: Setting attributes', 2);
2263 END IF;
2264
2265 x_return_status := Set_Attributes(
2266 p_article_id => p_article_id,
2267 p_article_title => p_article_title,
2268 p_org_id => p_org_id,
2269 p_article_number => p_article_number,
2270 p_standard_yn => p_standard_yn,
2271 p_article_intent => p_article_intent,
2272 p_article_language => p_article_language,
2273 p_article_type => p_article_type,
2274 p_orig_system_reference_code => p_orig_system_reference_code,
2275 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2276 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2277 p_cz_transfer_status_flag => p_cz_transfer_status_flag,
2281 p_request_id => p_request_id,
2278 p_program_id => p_program_id,
2279 p_program_login_id => p_program_login_id,
2280 p_program_application_id => p_program_application_id,
2282 p_attribute_category => p_attribute_category,
2283 p_attribute1 => p_attribute1,
2284 p_attribute2 => p_attribute2,
2285 p_attribute3 => p_attribute3,
2286 p_attribute4 => p_attribute4,
2287 p_attribute5 => p_attribute5,
2288 p_attribute6 => p_attribute6,
2289 p_attribute7 => p_attribute7,
2290 p_attribute8 => p_attribute8,
2291 p_attribute9 => p_attribute9,
2292 p_attribute10 => p_attribute10,
2293 p_attribute11 => p_attribute11,
2294 p_attribute12 => p_attribute12,
2295 p_attribute13 => p_attribute13,
2296 p_attribute14 => p_attribute14,
2297 p_attribute15 => p_attribute15,
2298 p_object_version_number => p_object_version_number,
2299 x_article_title => l_article_title,
2300 x_org_id => l_org_id,
2301 x_article_number => l_article_number,
2302 x_standard_yn => l_standard_yn,
2303 x_article_intent => l_article_intent,
2304 x_article_language => l_article_language,
2305 x_article_type => l_article_type,
2306 x_orig_system_reference_code => l_orig_system_reference_code,
2307 x_orig_system_reference_id1 => l_orig_system_reference_id1,
2308 x_orig_system_reference_id2 => l_orig_system_reference_id2,
2309 x_cz_transfer_status_flag => l_cz_transfer_status_flag,
2310 x_program_id => l_program_id,
2311 x_program_login_id => l_program_login_id,
2312 x_program_application_id => l_program_application_id,
2313 x_request_id => l_request_id,
2314 x_attribute_category => l_attribute_category,
2315 x_attribute1 => l_attribute1,
2316 x_attribute2 => l_attribute2,
2317 x_attribute3 => l_attribute3,
2318 x_attribute4 => l_attribute4,
2319 x_attribute5 => l_attribute5,
2320 x_attribute6 => l_attribute6,
2321 x_attribute7 => l_attribute7,
2322 x_attribute8 => l_attribute8,
2323 x_attribute9 => l_attribute9,
2324 x_attribute10 => l_attribute10,
2325 x_attribute11 => l_attribute11,
2326 x_attribute12 => l_attribute12,
2327 x_attribute13 => l_attribute13,
2328 x_attribute14 => l_attribute14,
2329 x_attribute15 => l_attribute15
2330 );
2331 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2333 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2334 RAISE FND_API.G_EXC_ERROR;
2335 END IF;
2336
2337 IF (l_debug = 'Y') THEN
2338 Okc_Debug.Log('7400: Record Validation', 2);
2339 END IF;
2340
2341 --- Validate all non-missing attributes
2342 x_return_status := Validate_Record(
2343 p_validation_level => p_validation_level,
2344 p_article_id => p_article_id,
2345 p_article_title => l_article_title,
2346 p_org_id => l_org_id,
2347 p_article_number => l_article_number,
2348 p_standard_yn => l_standard_yn,
2349 p_article_intent => l_article_intent,
2350 p_article_language => l_article_language,
2351 p_article_type => l_article_type,
2352 p_orig_system_reference_code => l_orig_system_reference_code,
2353 p_orig_system_reference_id1 => l_orig_system_reference_id1,
2354 p_orig_system_reference_id2 => l_orig_system_reference_id2,
2355 p_cz_transfer_status_flag => l_cz_transfer_status_flag,
2356 p_program_id => l_program_id,
2357 p_program_login_id => l_program_login_id,
2358 p_program_application_id => l_program_application_id,
2359 p_request_id => l_request_id,
2360 p_attribute_category => l_attribute_category,
2361 p_attribute1 => l_attribute1,
2362 p_attribute2 => l_attribute2,
2363 p_attribute3 => l_attribute3,
2364 p_attribute4 => l_attribute4,
2365 p_attribute5 => l_attribute5,
2366 p_attribute6 => l_attribute6,
2367 p_attribute7 => l_attribute7,
2368 p_attribute8 => l_attribute8,
2369 p_attribute9 => l_attribute9,
2370 p_attribute10 => l_attribute10,
2371 p_attribute11 => l_attribute11,
2372 p_attribute12 => l_attribute12,
2373 p_attribute13 => l_attribute13,
2374 p_attribute14 => l_attribute14,
2375 p_attribute15 => l_attribute15
2376 );
2377 --- If any errors happen abort API
2378 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2380 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2381 RAISE FND_API.G_EXC_ERROR;
2382 END IF;
2383
2384 IF (l_debug = 'Y') THEN
2385 Okc_Debug.Log('7500: Filling WHO columns', 2);
2386 END IF;
2387
2388 -- Filling who columns
2389 l_last_update_date := SYSDATE;
2390 l_last_updated_by := FND_GLOBAL.USER_ID;
2391 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2392
2396 END IF;
2393 -- Object version increment
2394 IF Nvl(l_object_version_number, 0) >= 0 THEN
2395 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
2397
2398 --------------------------------------------
2399 -- Call the Update_Row for each child record
2400 --------------------------------------------
2401 IF (l_debug = 'Y') THEN
2402 Okc_Debug.Log('7600: Updating Row', 2);
2403 END IF;
2404
2405 x_return_status := Update_Row(
2406 p_article_id => p_article_id,
2407 p_article_title => l_article_title,
2408 p_org_id => l_org_id,
2409 p_article_number => l_article_number,
2410 p_standard_yn => l_standard_yn,
2411 p_article_intent => l_article_intent,
2412 p_article_language => l_article_language,
2413 p_article_type => l_article_type,
2414 p_orig_system_reference_code => l_orig_system_reference_code,
2415 p_orig_system_reference_id1 => l_orig_system_reference_id1,
2416 p_orig_system_reference_id2 => l_orig_system_reference_id2,
2417 p_cz_transfer_status_flag => l_cz_transfer_status_flag,
2418 p_program_id => l_program_id,
2419 p_program_login_id => l_program_login_id,
2420 p_program_application_id => l_program_application_id,
2421 p_request_id => l_request_id,
2422 p_attribute_category => l_attribute_category,
2423 p_attribute1 => l_attribute1,
2424 p_attribute2 => l_attribute2,
2425 p_attribute3 => l_attribute3,
2426 p_attribute4 => l_attribute4,
2427 p_attribute5 => l_attribute5,
2428 p_attribute6 => l_attribute6,
2429 p_attribute7 => l_attribute7,
2430 p_attribute8 => l_attribute8,
2431 p_attribute9 => l_attribute9,
2432 p_attribute10 => l_attribute10,
2433 p_attribute11 => l_attribute11,
2434 p_attribute12 => l_attribute12,
2435 p_attribute13 => l_attribute13,
2436 p_attribute14 => l_attribute14,
2437 p_attribute15 => l_attribute15,
2438 p_object_version_number => l_object_version_number,
2439 p_created_by => l_created_by,
2440 p_creation_date => l_creation_date,
2441 p_last_updated_by => l_last_updated_by,
2442 p_last_update_login => l_last_update_login,
2443 p_last_update_date => l_last_update_date
2444 );
2445 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2447 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2448 RAISE FND_API.G_EXC_ERROR;
2449 END IF;
2450 x_article_intent := l_article_intent;
2451
2452
2453 IF (l_debug = 'Y') THEN
2454 Okc_Debug.Log('7800: Leaving Update_Row', 2);
2455 END IF;
2456
2457 EXCEPTION
2458 WHEN FND_API.G_EXC_ERROR THEN
2459 IF (l_debug = 'Y') THEN
2460 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
2461 END IF;
2462 x_return_status := G_RET_STS_ERROR;
2463
2464 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2465 IF (l_debug = 'Y') THEN
2466 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2467 END IF;
2468 x_return_status := G_RET_STS_UNEXP_ERROR;
2469
2470 WHEN OTHERS THEN
2471 IF (l_debug = 'Y') THEN
2472 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
2473 END IF;
2474 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2475 p_msg_name => G_UNEXPECTED_ERROR,
2476 p_token1 => G_SQLCODE_TOKEN,
2477 p_token1_value => sqlcode,
2478 p_token2 => G_SQLERRM_TOKEN,
2479 p_token2_value => sqlerrm);
2480 x_return_status := G_RET_STS_UNEXP_ERROR;
2481
2482 END Update_Row;
2483
2484 ---------------------------------------------------------------------------
2485 -- PROCEDURE Delete_Row
2486 ---------------------------------------------------------------------------
2487 -------------------------------------
2488 -- Delete_Row for:OKC_ARTICLES_ALL --
2489 -------------------------------------
2490 FUNCTION Delete_Row(
2491 p_article_id IN NUMBER
2492 ) RETURN VARCHAR2 IS
2493
2494 BEGIN
2495
2496 IF (l_debug = 'Y') THEN
2497 Okc_Debug.Log('8200: Entered Delete_Row', 2);
2498 END IF;
2499
2500 DELETE FROM OKC_ARTICLES_ALL WHERE ARTICLE_ID = p_ARTICLE_ID;
2501
2502 IF (l_debug = 'Y') THEN
2503 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
2504 END IF;
2505
2506 RETURN( G_RET_STS_SUCCESS );
2507
2508 EXCEPTION
2509 WHEN OTHERS THEN
2510
2511 IF (l_debug = 'Y') THEN
2512 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
2513 END IF;
2514
2515 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2516 p_msg_name => G_UNEXPECTED_ERROR,
2517 p_token1 => G_SQLCODE_TOKEN,
2518 p_token1_value => sqlcode,
2519 p_token2 => G_SQLERRM_TOKEN,
2520 p_token2_value => sqlerrm);
2521
2522 RETURN( G_RET_STS_UNEXP_ERROR );
2523
2524 END Delete_Row;
2525
2526 -------------------------------------
2527 -- Delete_Row for:OKC_ARTICLES_ALL --
2531 p_article_id IN NUMBER,
2528 -------------------------------------
2529 PROCEDURE Delete_Row(
2530 x_return_status OUT NOCOPY VARCHAR2,
2532 p_object_version_number IN NUMBER
2533 ) IS
2534 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
2535 BEGIN
2536
2537 IF (l_debug = 'Y') THEN
2538 Okc_Debug.Log('8800: Entered Delete_Row', 2);
2539 Okc_Debug.Log('8900: Locking _B row', 2);
2540 END IF;
2541
2542 x_return_status := Lock_row(
2543 p_article_id => p_article_id,
2544 p_object_version_number => p_object_version_number
2545 );
2546 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2548 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2549 RAISE FND_API.G_EXC_ERROR;
2550 END IF;
2551
2552
2553 IF (l_debug = 'Y') THEN
2554 Okc_Debug.Log('9100: Removing _B row', 2);
2555 END IF;
2556 x_return_status := Delete_Row( p_article_id => p_article_id );
2557 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2559 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2560 RAISE FND_API.G_EXC_ERROR;
2561 END IF;
2562
2563
2564 IF (l_debug = 'Y') THEN
2565 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
2566 END IF;
2567
2568 EXCEPTION
2569 WHEN FND_API.G_EXC_ERROR THEN
2570 IF (l_debug = 'Y') THEN
2571 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
2572 END IF;
2573 x_return_status := G_RET_STS_ERROR;
2574
2575 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2576 IF (l_debug = 'Y') THEN
2577 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2578 END IF;
2579 x_return_status := G_RET_STS_UNEXP_ERROR;
2580
2581 WHEN OTHERS THEN
2582 IF (l_debug = 'Y') THEN
2583 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
2584 END IF;
2585 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2586 p_msg_name => G_UNEXPECTED_ERROR,
2587 p_token1 => G_SQLCODE_TOKEN,
2588 p_token1_value => sqlcode,
2589 p_token2 => G_SQLERRM_TOKEN,
2590 p_token2_value => sqlerrm);
2591 x_return_status := G_RET_STS_UNEXP_ERROR;
2592
2593 END Delete_Row;
2594 END OKC_ARTICLES_ALL_PVT;