[Home] [Help]
PACKAGE BODY: APPS.OKC_REVIEW_UPLD_TERMS_PVT
Source
1 PACKAGE BODY OKC_REVIEW_UPLD_TERMS_PVT AS
2 /* $Header: OKCVRUTB.pls 120.55 2007/03/15 21:55:28 vnanjang ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
11 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
12 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
13 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
14 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
15 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
16 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
17 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
18 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
19 ---------------------------------------------------------------------------
20 -- VALIDATION LEVELS
21 ---------------------------------------------------------------------------
22 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
23 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
24 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
25 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
26 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
27 ---------------------------------------------------------------------------
28 -- GLOBAL VARIABLES
29 ---------------------------------------------------------------------------
30 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_REVIEW_UPLD_TERMS_PVT';
31 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
32 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_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_UNASSIGNED_SECTION_CODE CONSTANT VARCHAR2(30) := 'UNASSIGNED';
51 ------------------------------------------------------------------------------
52 -- GLOBAL EXCEPTION
53 ------------------------------------------------------------------------------
54 E_Resource_Busy EXCEPTION;
55 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
56 ---------------------------------------------------------------------------
57 -- FUNCTION get_seq_id
58 ---------------------------------------------------------------------------
59 FUNCTION Get_Seq_Id (
60 p_REVIEW_UPLD_TERMS_id IN NUMBER,
61 x_REVIEW_UPLD_TERMS_id OUT NOCOPY NUMBER
62 ) RETURN VARCHAR2 IS
63 CURSOR l_seq_csr IS
64 SELECT OKC_REVIEW_UPLD_TERMS_S1.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_REVIEW_UPLD_TERMS_id IS NULL ) THEN
71 OPEN l_seq_csr;
72 FETCH l_seq_csr INTO x_REVIEW_UPLD_TERMS_id;
73 IF l_seq_csr%NOTFOUND THEN
74 RAISE NO_DATA_FOUND;
75 END IF;
76 CLOSE l_seq_csr;
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 l_seq_csr%ISOPEN THEN
98 CLOSE l_seq_csr;
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_REVIEW_UPLD_TERMS
107 ---------------------------------------------------------------------------
108 FUNCTION Get_Rec (
109 p_REVIEW_UPLD_TERMS_id IN NUMBER,
110
111 x_document_id OUT NOCOPY NUMBER,
112 x_document_type OUT NOCOPY VARCHAR2,
113 x_object_id OUT NOCOPY NUMBER,
114 x_object_type OUT NOCOPY VARCHAR2,
115 x_object_title OUT NOCOPY CLOB,
116 x_object_text OUT NOCOPY CLOB,
117 x_parent_object_type OUT NOCOPY VARCHAR2,
118 x_parent_id OUT NOCOPY NUMBER,
119 x_article_id OUT NOCOPY NUMBER,
120 x_article_version_id OUT NOCOPY NUMBER,
121 x_label OUT NOCOPY VARCHAR2,
122 x_display_seq OUT NOCOPY NUMBER,
123 x_action OUT NOCOPY VARCHAR2,
124 x_error_message_count OUT NOCOPY NUMBER,
125 x_warning_message_count OUT NOCOPY NUMBER,
126 x_object_version_number OUT NOCOPY NUMBER,
127 x_new_parent_id OUT NOCOPY NUMBER,
128 x_upload_level OUT NOCOPY NUMBER,
129 x_created_by OUT NOCOPY NUMBER,
130 x_creation_date OUT NOCOPY DATE,
131 x_last_updated_by OUT NOCOPY NUMBER,
132 x_last_update_login OUT NOCOPY NUMBER,
133 x_last_update_date OUT NOCOPY DATE
134
135 ) RETURN VARCHAR2 IS
136 CURSOR OKC_REVIEW_TERMS_PK_CSR (cp_REVIEW_UPLD_TERMS_id IN NUMBER) IS
137 SELECT
138 DOCUMENT_ID,
139 DOCUMENT_TYPE,
140 OBJECT_ID,
141 OBJECT_TYPE,
142 OBJECT_TITLE,
143 OBJECT_TEXT,
144 PARENT_OBJECT_TYPE,
145 PARENT_ID,
146 ARTICLE_ID,
147 ARTICLE_VERSION_ID,
148 LABEL,
149 DISPLAY_SEQ,
150 ACTION,
151 ERROR_MESSAGE_COUNT,
152 WARNING_MESSAGE_COUNT,
153 OBJECT_VERSION_NUMBER,
154 NEW_PARENT_ID,
155 UPLOAD_LEVEL,
156 CREATED_BY,
157 CREATION_DATE,
158 LAST_UPDATED_BY,
159 LAST_UPDATE_LOGIN,
160 LAST_UPDATE_DATE
161 FROM OKC_REVIEW_UPLD_TERMS t
162 WHERE t.REVIEW_UPLD_TERMS_ID = cp_REVIEW_UPLD_TERMS_id;
163 BEGIN
164
165 IF (l_debug = 'Y') THEN
166 Okc_Debug.Log('400: Entered get_rec', 2);
167 END IF;
168
169 -- Get current database values
170 OPEN OKC_REVIEW_TERMS_PK_CSR (p_REVIEW_UPLD_TERMS_id);
171 FETCH OKC_REVIEW_TERMS_PK_CSR INTO
172 x_document_id,
173 x_document_type,
174 x_object_id,
175 x_object_type,
176 x_object_title,
177 x_object_text,
178 x_parent_object_type,
179 x_parent_id,
180 x_article_id,
181 x_article_version_id,
182 x_label,
183 x_display_seq,
184 x_action,
185 x_error_message_count,
186 x_warning_message_count,
187 x_object_version_number,
188 x_new_parent_id,
189 x_upload_level,
190 x_created_by,
191 x_creation_date,
192 x_last_updated_by,
193 x_last_update_login,
194 x_last_update_date;
195 IF OKC_REVIEW_TERMS_PK_CSR%NOTFOUND THEN
196 RAISE NO_DATA_FOUND;
197 END IF;
198 CLOSE OKC_REVIEW_TERMS_PK_CSR;
199
200 IF (l_debug = 'Y') THEN
201 Okc_Debug.Log('500: Leaving get_rec ', 2);
202 END IF;
203
204 RETURN G_RET_STS_SUCCESS ;
205
206 EXCEPTION
207 WHEN OTHERS THEN
208
209 IF (l_debug = 'Y') THEN
210 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
211 END IF;
212
213 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
214 p_msg_name => G_UNEXPECTED_ERROR,
215 p_token1 => G_SQLCODE_TOKEN,
216 p_token1_value => sqlcode,
217 p_token2 => G_SQLERRM_TOKEN,
218 p_token2_value => sqlerrm);
219
220 IF OKC_REVIEW_TERMS_PK_CSR%ISOPEN THEN
221 CLOSE OKC_REVIEW_TERMS_PK_CSR;
222 END IF;
223
224 RETURN G_RET_STS_UNEXP_ERROR ;
225
226 END Get_Rec;
227
228 -----------------------------------------
229 -- Set_Attributes for:OKC_REVIEW_UPLD_TERMS --
230 -----------------------------------------
231 FUNCTION Set_Attributes(
232 p_REVIEW_UPLD_TERMS_id IN NUMBER,
233 p_document_id IN NUMBER,
234 p_document_type IN VARCHAR2,
235 p_object_id IN NUMBER,
236 p_object_type IN VARCHAR2,
237 p_object_title IN CLOB,
238 p_object_text IN CLOB,
239 p_parent_object_type IN VARCHAR2,
240 p_parent_id IN NUMBER,
241 p_article_id IN NUMBER,
242 p_article_version_id IN NUMBER,
243 p_label IN VARCHAR2,
244 p_display_seq IN NUMBER,
245 p_action IN VARCHAR2,
246 p_error_message_count IN NUMBER,
247 p_warning_message_count IN NUMBER,
248 p_new_parent_id IN NUMBER,
249 p_upload_level IN NUMBER,
250 p_object_version_number IN OUT NOCOPY NUMBER,
251
252 x_document_id OUT NOCOPY NUMBER,
253 x_document_type OUT NOCOPY VARCHAR2,
254 x_object_id OUT NOCOPY NUMBER,
255 x_object_type OUT NOCOPY CLOB,
256 x_object_title OUT NOCOPY VARCHAR2,
257 x_object_text OUT NOCOPY CLOB,
258 x_parent_object_type OUT NOCOPY VARCHAR2,
259 x_parent_id OUT NOCOPY NUMBER,
260 x_article_id OUT NOCOPY NUMBER,
261 x_article_version_id OUT NOCOPY NUMBER,
262 x_label OUT NOCOPY VARCHAR2,
263 x_display_seq OUT NOCOPY NUMBER,
264 x_action OUT NOCOPY VARCHAR2,
265 x_error_message_count OUT NOCOPY NUMBER,
266 x_warning_message_count OUT NOCOPY NUMBER,
267 x_new_parent_id OUT NOCOPY NUMBER,
268 x_upload_level OUT NOCOPY NUMBER
269 ) RETURN VARCHAR2 IS
270 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
271 l_object_version_number OKC_REVIEW_UPLD_TERMS.OBJECT_VERSION_NUMBER%TYPE;
272 l_created_by OKC_REVIEW_UPLD_TERMS.CREATED_BY%TYPE;
273 l_creation_date OKC_REVIEW_UPLD_TERMS.CREATION_DATE%TYPE;
274 l_last_updated_by OKC_REVIEW_UPLD_TERMS.LAST_UPDATED_BY%TYPE;
275 l_last_update_login OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_LOGIN%TYPE;
276 l_last_update_date OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_DATE%TYPE;
277 BEGIN
278 IF (l_debug = 'Y') THEN
279 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
280 END IF;
281
282 IF( p_REVIEW_UPLD_TERMS_id IS NOT NULL ) THEN
283 -- Get current database values
284 l_return_status := Get_Rec(
285 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
286 x_document_id => x_document_id,
287 x_document_type => x_document_type,
288 x_object_id => x_object_id,
289 x_object_type => x_object_type,
290 x_object_title => x_object_title,
291 x_object_text => x_object_text,
292 x_parent_object_type => x_parent_object_type,
293 x_parent_id => x_parent_id,
294 x_article_id => x_article_id,
295 x_article_version_id => x_article_version_id,
296 x_label => x_label,
297 x_display_seq => x_display_seq,
298 x_action => x_action,
299 x_error_message_count => x_error_message_count,
300 x_warning_message_count => x_warning_message_count,
301 x_object_version_number => l_object_version_number,
302 x_new_parent_id => x_new_parent_id,
303 x_upload_level => x_upload_level,
304 x_created_by => l_created_by,
305 x_creation_date => l_creation_date,
306 x_last_updated_by => l_last_updated_by,
307 x_last_update_login => l_last_update_login,
308 x_last_update_date => l_last_update_date
309 );
310 --- If any errors happen abort API
311 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
313 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
314 RAISE FND_API.G_EXC_ERROR;
315 END IF;
316
317 --- Reversing G_MISS/NULL values logic
318
319 IF (p_document_id = G_MISS_NUM) THEN
320 x_document_id := NULL;
321 ELSIF (p_document_id IS NOT NULL) THEN
322 x_document_id := p_document_id;
323 END IF;
324
325 IF (p_document_type = G_MISS_CHAR) THEN
326 x_document_type := NULL;
327 ELSIF (p_document_type IS NOT NULL) THEN
328 x_document_type := p_document_type;
329 END IF;
330
331 IF (p_object_id = G_MISS_NUM) THEN
332 x_object_id := NULL;
333 ELSIF (p_object_id IS NOT NULL) THEN
334 x_object_id := p_object_id;
335 END IF;
336
337 IF (p_object_type = G_MISS_CHAR) THEN
338 x_object_type := NULL;
339 ELSIF (p_object_type IS NOT NULL) THEN
340 x_object_type := p_object_type;
341 END IF;
342
343 IF (p_object_title = G_MISS_CHAR) THEN
344 x_object_title := NULL;
345 ELSIF (p_object_title IS NOT NULL) THEN
346 x_object_title := p_object_title;
347 END IF;
348
349 IF (p_object_text = G_MISS_CHAR) THEN
350 x_object_text := NULL;
351 ELSIF (p_object_text IS NOT NULL) THEN
352 x_object_text := p_object_text;
353 END IF;
354
355 IF (p_parent_object_type = G_MISS_CHAR) THEN
356 x_parent_object_type := NULL;
357 ELSIF (p_parent_object_type IS NOT NULL) THEN
358 x_parent_object_type := p_parent_object_type;
359 END IF;
360
361 IF (p_parent_id = G_MISS_NUM) THEN
362 x_parent_id := NULL;
363 ELSIF (p_parent_id IS NOT NULL) THEN
364 x_parent_id := p_parent_id;
365 END IF;
366
367 IF (p_article_id = G_MISS_NUM) THEN
368 x_article_id := NULL;
369 ELSIF (p_article_id IS NOT NULL) THEN
370 x_article_id := p_article_id;
371 END IF;
372
373 IF (p_article_version_id = G_MISS_NUM) THEN
374 x_article_version_id := NULL;
375 ELSIF (p_article_version_id IS NOT NULL) THEN
376 x_article_version_id := p_article_version_id;
377 END IF;
378
379 IF (p_label = G_MISS_CHAR) THEN
380 x_label := NULL;
381 ELSIF (p_label IS NOT NULL) THEN
382 x_label := p_label;
383 END IF;
384
385 IF (p_display_seq = G_MISS_NUM) THEN
386 x_display_seq := NULL;
387 ELSIF (p_display_seq IS NOT NULL) THEN
388 x_display_seq := p_display_seq;
389 END IF;
390
391 IF (p_action = G_MISS_CHAR) THEN
392 x_action := NULL;
393 ELSIF (p_action IS NOT NULL) THEN
394 x_action := p_action;
395 END IF;
396
397 IF (p_error_message_count = G_MISS_NUM) THEN
398 x_error_message_count := NULL;
399 ELSIF (p_error_message_count IS NOT NULL) THEN
400 x_error_message_count := p_error_message_count;
401 END IF;
402
403 IF (p_warning_message_count = G_MISS_NUM) THEN
404 x_warning_message_count := NULL;
405 ELSIF (p_warning_message_count IS NOT NULL) THEN
406 x_warning_message_count := p_warning_message_count;
407 END IF;
408
409
410 IF (p_object_version_number IS NULL) THEN
411 p_object_version_number := l_object_version_number;
412 END IF;
413
414 IF (p_new_parent_id = G_MISS_NUM) THEN
415 x_new_parent_id := NULL;
416 ELSIF (p_new_parent_id IS NOT NULL) THEN
417 x_new_parent_id := p_new_parent_id;
418 END IF;
419
420
421
422
423 END IF;
424
425 IF (l_debug = 'Y') THEN
426 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
427 END IF;
428
429 RETURN G_RET_STS_SUCCESS ;
430 EXCEPTION
431 WHEN FND_API.G_EXC_ERROR THEN
432 IF (l_debug = 'Y') THEN
433 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
434 END IF;
435 RETURN G_RET_STS_ERROR;
436
437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
438 IF (l_debug = 'Y') THEN
439 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
440 END IF;
441 RETURN G_RET_STS_UNEXP_ERROR;
442
443 WHEN OTHERS THEN
444 IF (l_debug = 'Y') THEN
445 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
446 END IF;
447 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
448 p_msg_name => G_UNEXPECTED_ERROR,
449 p_token1 => G_SQLCODE_TOKEN,
450 p_token1_value => sqlcode,
451 p_token2 => G_SQLERRM_TOKEN,
452 p_token2_value => sqlerrm);
453 RETURN G_RET_STS_UNEXP_ERROR;
454
455 END Set_Attributes ;
456
457 ----------------------------------------------
458 -- Validate_Attributes for: OKC_REVIEW_UPLD_TERMS --
459 ----------------------------------------------
460 FUNCTION Validate_Attributes (
461 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
462
463 p_REVIEW_UPLD_TERMS_id IN NUMBER,
464 p_document_id IN NUMBER,
465 p_document_type IN VARCHAR2,
466 p_object_id IN NUMBER,
467 p_object_type IN VARCHAR2,
468 p_object_title IN CLOB,
469 p_object_text IN CLOB,
470 p_parent_object_type IN VARCHAR2,
471 p_parent_id IN NUMBER,
472 p_article_id IN NUMBER,
473 p_article_version_id IN NUMBER,
474 p_label IN VARCHAR2,
475 p_display_seq IN NUMBER,
476 p_action IN VARCHAR2,
477 p_error_message_count IN NUMBER,
478 p_warning_message_count IN NUMBER,
479 p_new_parent_id IN NUMBER,
480 p_upload_level IN NUMBER
481
482 ) RETURN VARCHAR2 IS
483 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
484 l_dummy_var VARCHAR2(1) := '?';
485 /* ?? uncomment next part after you check and change this foreign key validation
486
487 CURSOR l_REVIEW_UPLD_TERMS_id_csr is
488 SELECT '!'
489 FROM ??unknown_table??
490 WHERE ??REVIEW_UPLD_TERMS_ID?? = p_REVIEW_UPLD_TERMS_id;
491
492 CURSOR l_document_id_csr is
493 SELECT '!'
494 FROM ??unknown_table??
495 WHERE ??DOCUMENT_ID?? = p_document_id;
496
497 CURSOR l_object_id_csr is
498 SELECT '!'
499 FROM ??unknown_table??
500 WHERE ??OBJECT_ID?? = p_object_id;
501
502 CURSOR l_parent_id_csr is
503 SELECT '!'
504 FROM ??unknown_table??
505 WHERE ??PARENT_ID?? = p_parent_id;
506
507 CURSOR l_article_id_csr is
508 SELECT '!'
509 FROM ??unknown_table??
510 WHERE ??ARTICLE_ID?? = p_article_id;
511
512 CURSOR l_article_version_id_csr is
513 SELECT '!'
514 FROM ??unknown_table??
515 WHERE ??ARTICLE_VERSION_ID?? = p_article_version_id;
516
517 */
518 BEGIN
519
520 IF (l_debug = 'Y') THEN
521 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
522 END IF;
523
524 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
525 IF (l_debug = 'Y') THEN
526 Okc_Debug.Log('1300: required values validation', 2);
527 END IF;
528
529 END IF;
530
531 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
532 IF (l_debug = 'Y') THEN
533 Okc_Debug.Log('1600: static values and range validation', 2);
534 END IF;
535
536 END IF;
537
538 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
539 IF (l_debug = 'Y') THEN
540 Okc_Debug.Log('1900: lookup codes validation', 2);
541 END IF;
542 /* ?? uncomment next part after you check and change this lokkup codes validation
543
544 */
545 END IF;
546
547 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
548 IF (l_debug = 'Y') THEN
549 Okc_Debug.Log('2100: foreigh keys validation ', 2);
550 END IF;
551 /* ?? uncomment next part after you check and change this foreign key validation
552
553 IF (l_debug = 'Y') THEN
554 Okc_Debug.Log('2200: - attribute REVIEW_UPLD_TERMS_ID ', 2);
555 END IF;
556 IF p_REVIEW_UPLD_TERMS_id IS NOT NULL THEN
557 l_dummy_var := '?';
558 OPEN l_REVIEW_UPLD_TERMS_id_csr;
559 FETCH l_REVIEW_UPLD_TERMS_id_csr INTO l_dummy_var;
560 CLOSE l_REVIEW_UPLD_TERMS_id_csr;
561 IF (l_dummy_var = '?') THEN
562 IF (l_debug = 'Y') THEN
563 Okc_Debug.Log('2300: - attribute REVIEW_UPLD_TERMS_ID is invalid', 2);
564 END IF;
565 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'REVIEW_UPLD_TERMS_ID');
566 l_return_status := G_RET_STS_ERROR;
567 END IF;
568 END IF;
569
570 IF (l_debug = 'Y') THEN
571 Okc_Debug.Log('2200: - attribute DOCUMENT_ID ', 2);
572 END IF;
573 IF p_document_id IS NOT NULL THEN
574 l_dummy_var := '?';
575 OPEN l_document_id_csr;
576 FETCH l_document_id_csr INTO l_dummy_var;
577 CLOSE l_document_id_csr;
578 IF (l_dummy_var = '?') THEN
579 IF (l_debug = 'Y') THEN
580 Okc_Debug.Log('2300: - attribute DOCUMENT_ID is invalid', 2);
581 END IF;
582 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DOCUMENT_ID');
583 l_return_status := G_RET_STS_ERROR;
584 END IF;
585 END IF;
586
587 IF (l_debug = 'Y') THEN
588 Okc_Debug.Log('2200: - attribute OBJECT_ID ', 2);
589 END IF;
590 IF p_object_id IS NOT NULL THEN
591 l_dummy_var := '?';
592 OPEN l_object_id_csr;
593 FETCH l_object_id_csr INTO l_dummy_var;
594 CLOSE l_object_id_csr;
595 IF (l_dummy_var = '?') THEN
596 IF (l_debug = 'Y') THEN
597 Okc_Debug.Log('2300: - attribute OBJECT_ID is invalid', 2);
598 END IF;
599 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'OBJECT_ID');
600 l_return_status := G_RET_STS_ERROR;
601 END IF;
602 END IF;
603
604 IF (l_debug = 'Y') THEN
605 Okc_Debug.Log('2200: - attribute PARENT_ID ', 2);
606 END IF;
607 IF p_parent_id IS NOT NULL THEN
608 l_dummy_var := '?';
609 OPEN l_parent_id_csr;
610 FETCH l_parent_id_csr INTO l_dummy_var;
611 CLOSE l_parent_id_csr;
612 IF (l_dummy_var = '?') THEN
613 IF (l_debug = 'Y') THEN
614 Okc_Debug.Log('2300: - attribute PARENT_ID is invalid', 2);
615 END IF;
616 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PARENT_ID');
617 l_return_status := G_RET_STS_ERROR;
618 END IF;
619 END IF;
620
621 IF (l_debug = 'Y') THEN
622 Okc_Debug.Log('2200: - attribute ARTICLE_ID ', 2);
623 END IF;
624 IF p_article_id IS NOT NULL THEN
625 l_dummy_var := '?';
626 OPEN l_article_id_csr;
627 FETCH l_article_id_csr INTO l_dummy_var;
628 CLOSE l_article_id_csr;
629 IF (l_dummy_var = '?') THEN
630 IF (l_debug = 'Y') THEN
631 Okc_Debug.Log('2300: - attribute ARTICLE_ID is invalid', 2);
632 END IF;
633 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ARTICLE_ID');
634 l_return_status := G_RET_STS_ERROR;
635 END IF;
636 END IF;
637
638 IF (l_debug = 'Y') THEN
639 Okc_Debug.Log('2200: - attribute ARTICLE_VERSION_ID ', 2);
640 END IF;
641 IF p_article_version_id IS NOT NULL THEN
642 l_dummy_var := '?';
643 OPEN l_article_version_id_csr;
644 FETCH l_article_version_id_csr INTO l_dummy_var;
645 CLOSE l_article_version_id_csr;
646 IF (l_dummy_var = '?') THEN
647 IF (l_debug = 'Y') THEN
648 Okc_Debug.Log('2300: - attribute ARTICLE_VERSION_ID is invalid', 2);
649 END IF;
650 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ARTICLE_VERSION_ID');
651 l_return_status := G_RET_STS_ERROR;
652 END IF;
653 END IF;
654
655 */
656 END IF;
657
658
659 IF (l_debug = 'Y') THEN
660 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
661 END IF;
662
663 RETURN l_return_status;
664
665 EXCEPTION
666 WHEN OTHERS THEN
667 Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
668 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
669 p_msg_name => G_UNEXPECTED_ERROR,
670 p_token1 => G_SQLCODE_TOKEN,
671 p_token1_value => sqlcode,
672 p_token2 => G_SQLERRM_TOKEN,
673 p_token2_value => sqlerrm);
674
675 /* ?? uncomment next part after you check and change this foreign key validation
676
677 IF l_REVIEW_UPLD_TERMS_id_csr%ISOPEN THEN
678 CLOSE l_REVIEW_UPLD_TERMS_id_csr;
679 END IF;
680
681 IF l_document_id_csr%ISOPEN THEN
682 CLOSE l_document_id_csr;
683 END IF;
684
685 IF l_object_id_csr%ISOPEN THEN
686 CLOSE l_object_id_csr;
687 END IF;
688
689 IF l_parent_id_csr%ISOPEN THEN
690 CLOSE l_parent_id_csr;
691 END IF;
692
693 IF l_article_id_csr%ISOPEN THEN
694 CLOSE l_article_id_csr;
695 END IF;
696
697 IF l_article_version_id_csr%ISOPEN THEN
698 CLOSE l_article_version_id_csr;
699 END IF;
700
701 */
702 RETURN G_RET_STS_UNEXP_ERROR;
703
704 END Validate_Attributes;
705
706
707 ---------------------------------------------------------------------------
708 -- PROCEDURE Validate_Record
709 -- It calls Item Level Validations and then makes Record Level Validations
710 ---------------------------------------------------------------------------
711 ------------------------------------------
712 -- Validate_Record for:OKC_REVIEW_UPLD_TERMS --
713 ------------------------------------------
714 FUNCTION Validate_Record (
715 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
716
717 p_REVIEW_UPLD_TERMS_id IN NUMBER,
718 p_document_id IN NUMBER,
719 p_document_type IN VARCHAR2,
720 p_object_id IN NUMBER,
721 p_object_type IN VARCHAR2,
722 p_object_title IN CLOB,
723 p_object_text IN CLOB,
724 p_parent_object_type IN VARCHAR2,
725 p_parent_id IN NUMBER,
726 p_article_id IN NUMBER,
727 p_article_version_id IN NUMBER,
728 p_label IN VARCHAR2,
729 p_display_seq IN NUMBER,
730 p_action IN VARCHAR2,
731 p_error_message_count IN NUMBER,
732 p_warning_message_count IN NUMBER,
733 p_new_parent_id IN NUMBER,
734 p_upload_level IN NUMBER
735 ) RETURN VARCHAR2 IS
736 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
737 BEGIN
738
739 IF (l_debug = 'Y') THEN
740 Okc_Debug.Log('2600: Entered Validate_Record', 2);
741 END IF;
742
743 --- Validate all non-missing attributes (Item Level Validation)
744 l_return_status := Validate_Attributes(
745 p_validation_level => p_validation_level,
746
747 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
748 p_document_id => p_document_id,
749 p_document_type => p_document_type,
750 p_object_id => p_object_id,
751 p_object_type => p_object_type,
752 p_object_title => p_object_title,
753 p_object_text => p_object_text,
754 p_parent_object_type => p_parent_object_type,
755 p_parent_id => p_parent_id,
756 p_article_id => p_article_id,
757 p_article_version_id => p_article_version_id,
758 p_label => p_label,
759 p_display_seq => p_display_seq,
760 p_action => p_action,
761 p_error_message_count => p_error_message_count,
762 p_warning_message_count => p_warning_message_count,
763 p_new_parent_id => p_new_parent_id,
764 p_upload_level => p_upload_level
765
766 );
767 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
768 IF (l_debug = 'Y') THEN
769 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
770 END IF;
771 RETURN G_RET_STS_UNEXP_ERROR;
772 END IF;
773
774 --- Record Level Validation
775 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
776 IF (l_debug = 'Y') THEN
777 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
778 END IF;
779 /*+++++++++++++start of hand code +++++++++++++++++++*/
780 -- ?? manual coding for Record Level Validations if required ??
781 /*+++++++++++++End of hand code +++++++++++++++++++*/
782 END IF;
783
784 IF (l_debug = 'Y') THEN
785 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
786 END IF;
787 RETURN l_return_status ;
788
789 EXCEPTION
790 WHEN OTHERS THEN
791
792 IF (l_debug = 'Y') THEN
793 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
794 END IF;
795
796 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
797 p_msg_name => G_UNEXPECTED_ERROR,
798 p_token1 => G_SQLCODE_TOKEN,
799 p_token1_value => sqlcode,
800 p_token2 => G_SQLERRM_TOKEN,
801 p_token2_value => sqlerrm);
802 RETURN G_RET_STS_UNEXP_ERROR ;
803
804 END Validate_Record;
805
806 ---------------------------------------------------------------------------
807 -- PROCEDURE validate_row
808 ---------------------------------------------------------------------------
809 ---------------------------------------
810 -- validate_row for:OKC_REVIEW_UPLD_TERMS --
811 ---------------------------------------
812 PROCEDURE validate_row(
813 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
814
815 x_return_status OUT NOCOPY VARCHAR2,
816
817 p_REVIEW_UPLD_TERMS_id IN NUMBER,
818 p_document_id IN NUMBER,
819 p_document_type IN VARCHAR2,
820 p_object_id IN NUMBER,
821 p_object_type IN VARCHAR2,
822 p_object_title IN CLOB,
823 p_object_text IN CLOB,
824 p_parent_object_type IN VARCHAR2,
825 p_parent_id IN NUMBER,
826 p_article_id IN NUMBER,
827 p_article_version_id IN NUMBER,
828 p_label IN VARCHAR2,
829 p_display_seq IN NUMBER,
830 p_action IN VARCHAR2,
831 p_error_message_count IN NUMBER,
832 p_warning_message_count IN NUMBER,
833 p_new_parent_id IN NUMBER,
834 p_upload_level IN NUMBER,
835
836
837 p_object_version_number IN NUMBER
838 ) IS
839 l_document_id OKC_REVIEW_UPLD_TERMS.DOCUMENT_ID%TYPE;
840 l_document_type OKC_REVIEW_UPLD_TERMS.DOCUMENT_TYPE%TYPE;
841 l_object_id OKC_REVIEW_UPLD_TERMS.OBJECT_ID%TYPE;
842 l_object_type OKC_REVIEW_UPLD_TERMS.OBJECT_TYPE%TYPE;
843 l_object_title OKC_REVIEW_UPLD_TERMS.OBJECT_TITLE%TYPE;
844 l_object_text OKC_REVIEW_UPLD_TERMS.OBJECT_TEXT%TYPE;
845 l_parent_object_type OKC_REVIEW_UPLD_TERMS.PARENT_OBJECT_TYPE%TYPE;
846 l_parent_id OKC_REVIEW_UPLD_TERMS.PARENT_ID%TYPE;
847 l_article_id OKC_REVIEW_UPLD_TERMS.ARTICLE_ID%TYPE;
848 l_article_version_id OKC_REVIEW_UPLD_TERMS.ARTICLE_VERSION_ID%TYPE;
849 l_label OKC_REVIEW_UPLD_TERMS.LABEL%TYPE;
850 l_display_seq OKC_REVIEW_UPLD_TERMS.DISPLAY_SEQ%TYPE;
851 l_action OKC_REVIEW_UPLD_TERMS.ACTION%TYPE;
852 l_error_message_count OKC_REVIEW_UPLD_TERMS.ERROR_MESSAGE_COUNT%TYPE;
853 l_warning_message_count OKC_REVIEW_UPLD_TERMS.WARNING_MESSAGE_COUNT%TYPE;
854 l_object_version_number OKC_REVIEW_UPLD_TERMS.OBJECT_VERSION_NUMBER%TYPE;
855 l_new_parent_id OKC_REVIEW_UPLD_TERMS.NEW_PARENT_ID%TYPE;
856 l_upload_level OKC_REVIEW_UPLD_TERMS.UPLOAD_LEVEL%TYPE;
857 l_created_by OKC_REVIEW_UPLD_TERMS.CREATED_BY%TYPE;
858 l_creation_date OKC_REVIEW_UPLD_TERMS.CREATION_DATE%TYPE;
859 l_last_updated_by OKC_REVIEW_UPLD_TERMS.LAST_UPDATED_BY%TYPE;
860 l_last_update_login OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_LOGIN%TYPE;
861 l_last_update_date OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_DATE%TYPE;
862 BEGIN
863
864 IF (l_debug = 'Y') THEN
865 Okc_Debug.Log('3100: Entered validate_row', 2);
866 END IF;
867
868 -- Setting attributes
869 x_return_status := Set_Attributes(
870 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
871 p_document_id => p_document_id,
872 p_document_type => p_document_type,
873 p_object_id => p_object_id,
874 p_object_type => p_object_type,
875 p_object_title => p_object_title,
876 p_object_text => p_object_text,
877 p_parent_object_type => p_parent_object_type,
878 p_parent_id => p_parent_id,
879 p_article_id => p_article_id,
880 p_article_version_id => p_article_version_id,
881 p_label => p_label,
882 p_display_seq => p_display_seq,
883 p_action => p_action,
884 p_error_message_count => p_error_message_count,
885 p_warning_message_count => p_warning_message_count,
886 p_new_parent_id => p_new_parent_id,
887 p_upload_level => p_upload_level,
888 p_object_version_number => l_object_version_number,
889 x_document_id => l_document_id,
890 x_document_type => l_document_type,
891 x_object_id => l_object_id,
892 x_object_type => l_object_type,
893 x_object_title => l_object_title,
894 x_object_text => l_object_text,
895 x_parent_object_type => l_parent_object_type,
896 x_parent_id => l_parent_id,
897 x_article_id => l_article_id,
898 x_article_version_id => l_article_version_id,
899 x_label => l_label,
900 x_display_seq => l_display_seq,
901 x_action => l_action,
902 x_error_message_count => l_error_message_count,
903 x_warning_message_count => l_warning_message_count,
904 x_new_parent_id => l_new_parent_id,
905 x_upload_level => l_upload_level
906 );
907 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
910 RAISE FND_API.G_EXC_ERROR;
911 END IF;
912
913 -- Validate all non-missing attributes (Item Level Validation)
914 l_object_version_number := p_object_version_number ;
915 x_return_status := Validate_Record(
916 p_validation_level => p_validation_level,
917 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
918 p_document_id => l_document_id,
919 p_document_type => l_document_type,
920 p_object_id => l_object_id,
921 p_object_type => l_object_type,
922 p_object_title => l_object_title,
923 p_object_text => l_object_text,
924 p_parent_object_type => l_parent_object_type,
925 p_parent_id => l_parent_id,
926 p_article_id => l_article_id,
927 p_article_version_id => l_article_version_id,
928 p_label => l_label,
929 p_display_seq => l_display_seq,
930 p_action => l_action,
931 p_error_message_count => l_error_message_count,
932 p_warning_message_count => l_warning_message_count,
933 p_new_parent_id => l_new_parent_id,
934 p_upload_level => l_upload_level
935 );
936
937 IF (l_debug = 'Y') THEN
938 Okc_Debug.Log('3200: Leaving validate_row', 2);
939 END IF;
940
941 EXCEPTION
942 WHEN FND_API.G_EXC_ERROR THEN
943 IF (l_debug = 'Y') THEN
944 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
945 END IF;
946 x_return_status := G_RET_STS_ERROR;
947
948 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
949 IF (l_debug = 'Y') THEN
950 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
951 END IF;
952 x_return_status := G_RET_STS_UNEXP_ERROR;
953
954 WHEN OTHERS THEN
955 IF (l_debug = 'Y') THEN
956 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
957 END IF;
958 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
959 p_msg_name => G_UNEXPECTED_ERROR,
960 p_token1 => G_SQLCODE_TOKEN,
961 p_token1_value => sqlcode,
962 p_token2 => G_SQLERRM_TOKEN,
963 p_token2_value => sqlerrm);
964 x_return_status := G_RET_STS_UNEXP_ERROR;
965
966 END Validate_Row;
967
968 ---------------------------------------------------------------------------
969 -- PROCEDURE Insert_Row
970 ---------------------------------------------------------------------------
971 -------------------------------------
972 -- Insert_Row for:OKC_REVIEW_UPLD_TERMS --
973 -------------------------------------
974 FUNCTION Insert_Row(
975 p_REVIEW_UPLD_TERMS_id IN NUMBER,
976 p_document_id IN NUMBER,
977 p_document_type IN VARCHAR2,
978 p_object_id IN NUMBER,
979 p_object_type IN VARCHAR2,
980 p_object_title IN CLOB,
981 p_object_text IN CLOB,
982 p_parent_object_type IN VARCHAR2,
983 p_parent_id IN NUMBER,
984 p_article_id IN NUMBER,
985 p_article_version_id IN NUMBER,
986 p_label IN VARCHAR2,
987 p_display_seq IN NUMBER,
988 p_action IN VARCHAR2,
989 p_error_message_count IN NUMBER,
990 p_warning_message_count IN NUMBER,
991 p_new_parent_id IN NUMBER,
992 p_upload_level IN NUMBER,
993 p_object_version_number IN NUMBER,
994 p_created_by IN NUMBER,
995 p_creation_date IN DATE,
996 p_last_updated_by IN NUMBER,
997 p_last_update_login IN NUMBER,
998 p_last_update_date IN DATE
999
1000 ) RETURN VARCHAR2 IS
1001
1002 BEGIN
1003
1004 IF (l_debug = 'Y') THEN
1005 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
1006 END IF;
1007
1008 INSERT INTO OKC_REVIEW_UPLD_TERMS(
1009 REVIEW_UPLD_TERMS_ID,
1010 DOCUMENT_ID,
1011 DOCUMENT_TYPE,
1012 OBJECT_ID,
1013 OBJECT_TYPE,
1014 OBJECT_TITLE,
1015 OBJECT_TEXT,
1016 PARENT_OBJECT_TYPE,
1017 PARENT_ID,
1018 ARTICLE_ID,
1019 ARTICLE_VERSION_ID,
1020 LABEL,
1021 DISPLAY_SEQ,
1022 ACTION,
1023 ERROR_MESSAGE_COUNT,
1024 WARNING_MESSAGE_COUNT,
1025 OBJECT_VERSION_NUMBER,
1026 NEW_PARENT_ID,
1027 UPLOAD_LEVEL,
1028 CREATED_BY,
1029 CREATION_DATE,
1030 LAST_UPDATED_BY,
1031 LAST_UPDATE_LOGIN,
1032 LAST_UPDATE_DATE)
1033 VALUES (
1034 p_REVIEW_UPLD_TERMS_id,
1035 p_document_id,
1036 p_document_type,
1037 p_object_id,
1038 p_object_type,
1039 p_object_title,
1040 p_object_text,
1041 p_parent_object_type,
1042 p_parent_id,
1043 p_article_id,
1044 p_article_version_id,
1045 p_label,
1046 p_display_seq,
1047 p_action,
1048 p_error_message_count,
1049 p_warning_message_count,
1050 p_object_version_number,
1051 p_new_parent_id,
1052 p_upload_level,
1053 p_created_by,
1054 p_creation_date,
1055 p_last_updated_by,
1056 p_last_update_login,
1057 p_last_update_date);
1058
1059 IF (l_debug = 'Y') THEN
1060 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
1061 END IF;
1062
1063 RETURN( G_RET_STS_SUCCESS );
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067
1068 IF (l_debug = 'Y') THEN
1069 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
1070 END IF;
1071
1072 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1073 p_msg_name => G_UNEXPECTED_ERROR,
1074 p_token1 => G_SQLCODE_TOKEN,
1075 p_token1_value => sqlcode,
1076 p_token2 => G_SQLERRM_TOKEN,
1077 p_token2_value => sqlerrm);
1078
1079 RETURN( G_RET_STS_UNEXP_ERROR );
1080
1081 END Insert_Row;
1082
1083
1084 -------------------------------------
1085 -- Insert_Row for:OKC_REVIEW_UPLD_TERMS --
1086 -------------------------------------
1087 PROCEDURE Insert_Row(
1088 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1089 x_return_status OUT NOCOPY VARCHAR2,
1090
1091 p_REVIEW_UPLD_TERMS_id IN NUMBER,
1092 p_document_id IN NUMBER,
1093 p_document_type IN VARCHAR2,
1094 p_object_id IN NUMBER,
1095 p_object_type IN VARCHAR2,
1096 p_object_title IN CLOB,
1097 p_object_text IN CLOB,
1098 p_parent_object_type IN VARCHAR2,
1099 p_parent_id IN NUMBER,
1100 p_article_id IN NUMBER,
1101 p_article_version_id IN NUMBER,
1102 p_label IN VARCHAR2,
1103 p_display_seq IN NUMBER,
1104 p_action IN VARCHAR2,
1105 p_error_message_count IN NUMBER,
1106 p_warning_message_count IN NUMBER,
1107 p_new_parent_id IN NUMBER,
1108 p_upload_level IN NUMBER,
1109
1110 x_REVIEW_UPLD_TERMS_id OUT NOCOPY NUMBER
1111
1112 ) IS
1113
1114 l_object_version_number OKC_REVIEW_UPLD_TERMS.OBJECT_VERSION_NUMBER%TYPE;
1115 l_created_by OKC_REVIEW_UPLD_TERMS.CREATED_BY%TYPE;
1116 l_creation_date OKC_REVIEW_UPLD_TERMS.CREATION_DATE%TYPE;
1117 l_last_updated_by OKC_REVIEW_UPLD_TERMS.LAST_UPDATED_BY%TYPE;
1118 l_last_update_login OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_LOGIN%TYPE;
1119 l_last_update_date OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_DATE%TYPE;
1120 BEGIN
1121
1122 x_return_status := G_RET_STS_SUCCESS;
1123
1124 IF (l_debug = 'Y') THEN
1125 Okc_Debug.Log('4200: Entered Insert_Row', 2);
1126 END IF;
1127
1128 --- Setting item attributes
1129 -- Set primary key value
1130 IF( p_REVIEW_UPLD_TERMS_id IS NULL ) THEN
1131 x_return_status := Get_Seq_Id(
1132 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
1133 x_REVIEW_UPLD_TERMS_id => x_REVIEW_UPLD_TERMS_id
1134 );
1135 --- If any errors happen abort API
1136 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1138 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1139 RAISE FND_API.G_EXC_ERROR;
1140 END IF;
1141 ELSE
1142 x_REVIEW_UPLD_TERMS_id := p_REVIEW_UPLD_TERMS_id;
1143 END IF;
1144 -- Set Internal columns
1145 l_object_version_number := 1;
1146 l_creation_date := Sysdate;
1147 l_created_by := Fnd_Global.User_Id;
1148 l_last_update_date := l_creation_date;
1149 l_last_updated_by := l_created_by;
1150 l_last_update_login := Fnd_Global.Login_Id;
1151
1152
1153 --- Validate all non-missing attributes
1154 x_return_status := Validate_Record(
1155 p_validation_level => p_validation_level,
1156 p_REVIEW_UPLD_TERMS_id => x_REVIEW_UPLD_TERMS_id,
1157 p_document_id => p_document_id,
1158 p_document_type => p_document_type,
1159 p_object_id => p_object_id,
1160 p_object_type => p_object_type,
1161 p_object_title => p_object_title,
1162 p_object_text => p_object_text,
1163 p_parent_object_type => p_parent_object_type,
1164 p_parent_id => p_parent_id,
1165 p_article_id => p_article_id,
1166 p_article_version_id => p_article_version_id,
1167 p_label => p_label,
1168 p_display_seq => p_display_seq,
1169 p_action => p_action,
1170 p_error_message_count => p_error_message_count,
1171 p_warning_message_count => p_warning_message_count,
1172 p_new_parent_id => p_new_parent_id,
1173 p_upload_level => p_upload_level
1174 );
1175 --- If any errors happen abort API
1176 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1178 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1179 RAISE FND_API.G_EXC_ERROR;
1180 END IF;
1181
1182 --------------------------------------------
1183 -- Call the internal Insert_Row for each child record
1184 --------------------------------------------
1185 IF (l_debug = 'Y') THEN
1186 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
1187 END IF;
1188
1189 x_return_status := Insert_Row(
1190 p_REVIEW_UPLD_TERMS_id => x_REVIEW_UPLD_TERMS_id,
1191 p_document_id => p_document_id,
1192 p_document_type => p_document_type,
1193 p_object_id => p_object_id,
1194 p_object_type => p_object_type,
1195 p_object_title => p_object_title,
1196 p_object_text => p_object_text,
1197 p_parent_object_type => p_parent_object_type,
1198 p_parent_id => p_parent_id,
1199 p_article_id => p_article_id,
1200 p_article_version_id => p_article_version_id,
1201 p_label => p_label,
1202 p_display_seq => p_display_seq,
1203 p_action => p_action,
1204 p_error_message_count => p_error_message_count,
1205 p_warning_message_count => p_warning_message_count,
1206 p_object_version_number => l_object_version_number,
1207 p_new_parent_id => p_new_parent_id,
1208 p_upload_level => p_upload_level,
1209 p_created_by => l_created_by,
1210 p_creation_date => l_creation_date,
1211 p_last_updated_by => l_last_updated_by,
1212 p_last_update_login => l_last_update_login,
1213 p_last_update_date => l_last_update_date
1214 );
1215 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1218 RAISE FND_API.G_EXC_ERROR;
1219 END IF;
1220
1221
1222
1223 IF (l_debug = 'Y') THEN
1224 Okc_Debug.Log('4500: Leaving Insert_Row', 2);
1225 END IF;
1226
1227 EXCEPTION
1228 WHEN FND_API.G_EXC_ERROR THEN
1229 IF (l_debug = 'Y') THEN
1230 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
1231 END IF;
1232 x_return_status := G_RET_STS_ERROR;
1233
1234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1235 IF (l_debug = 'Y') THEN
1236 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1237 END IF;
1238 x_return_status := G_RET_STS_UNEXP_ERROR;
1239
1240 WHEN OTHERS THEN
1241 IF (l_debug = 'Y') THEN
1242 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
1243 END IF;
1244 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1245 p_msg_name => G_UNEXPECTED_ERROR,
1246 p_token1 => G_SQLCODE_TOKEN,
1247 p_token1_value => sqlcode,
1248 p_token2 => G_SQLERRM_TOKEN,
1249 p_token2_value => sqlerrm);
1250 x_return_status := G_RET_STS_UNEXP_ERROR;
1251
1252 END Insert_Row;
1253 ---------------------------------------------------------------------------
1254 -- PROCEDURE Lock_Row
1255 ---------------------------------------------------------------------------
1256 -----------------------------------
1257 -- Lock_Row for:OKC_REVIEW_UPLD_TERMS --
1258 -----------------------------------
1259 FUNCTION Lock_Row(
1260 p_REVIEW_UPLD_TERMS_id IN NUMBER,
1261 p_object_version_number IN NUMBER
1262 ) RETURN VARCHAR2 IS
1263
1264 l_return_status VARCHAR2(1);
1265 l_object_version_number OKC_REVIEW_UPLD_TERMS.OBJECT_VERSION_NUMBER%TYPE;
1266 l_row_notfound BOOLEAN := FALSE;
1267
1268 CURSOR lock_csr (cp_REVIEW_UPLD_TERMS_id NUMBER, cp_object_version_number NUMBER) IS
1269 SELECT object_version_number
1270 FROM OKC_REVIEW_UPLD_TERMS
1271 WHERE REVIEW_UPLD_TERMS_ID = cp_REVIEW_UPLD_TERMS_id
1272 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1273 FOR UPDATE OF object_version_number NOWAIT;
1274
1275 CURSOR lchk_csr (cp_REVIEW_UPLD_TERMS_id NUMBER) IS
1276 SELECT object_version_number
1277 FROM OKC_REVIEW_UPLD_TERMS
1278 WHERE REVIEW_UPLD_TERMS_ID = cp_REVIEW_UPLD_TERMS_id;
1279 BEGIN
1280
1281 IF (l_debug = 'Y') THEN
1282 Okc_Debug.Log('4900: Entered Lock_Row', 2);
1283 END IF;
1284
1285
1286 BEGIN
1287
1288 OPEN lock_csr( p_REVIEW_UPLD_TERMS_id, p_object_version_number );
1289 FETCH lock_csr INTO l_object_version_number;
1290 l_row_notfound := lock_csr%NOTFOUND;
1291 CLOSE lock_csr;
1292
1293 EXCEPTION
1294 WHEN E_Resource_Busy THEN
1295
1296 IF (l_debug = 'Y') THEN
1297 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
1298 END IF;
1299
1300 IF (lock_csr%ISOPEN) THEN
1301 CLOSE lock_csr;
1302 END IF;
1303 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1304 RETURN( G_RET_STS_ERROR );
1305 END;
1306
1307 IF ( l_row_notfound ) THEN
1308 l_return_status := G_RET_STS_ERROR;
1309
1310 OPEN lchk_csr(p_REVIEW_UPLD_TERMS_id);
1311 FETCH lchk_csr INTO l_object_version_number;
1312 l_row_notfound := lchk_csr%NOTFOUND;
1313 CLOSE lchk_csr;
1314
1315 IF (l_row_notfound) THEN
1316 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_DELETED);
1317 ELSIF l_object_version_number > p_object_version_number THEN
1318 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
1319 ELSIF l_object_version_number = -1 THEN
1320 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1321 ELSE -- it can be the only above condition. It can happen after restore version
1322 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
1323 END IF;
1324 ELSE
1325 l_return_status := G_RET_STS_SUCCESS;
1326 END IF;
1327
1328 IF (l_debug = 'Y') THEN
1329 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
1330 END IF;
1331
1332 RETURN( l_return_status );
1333
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336
1337 IF (lock_csr%ISOPEN) THEN
1338 CLOSE lock_csr;
1339 END IF;
1340 IF (lchk_csr%ISOPEN) THEN
1341 CLOSE lchk_csr;
1342 END IF;
1343
1344 IF (l_debug = 'Y') THEN
1345 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1346 END IF;
1347
1348 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1349 p_msg_name => G_UNEXPECTED_ERROR,
1350 p_token1 => G_SQLCODE_TOKEN,
1351 p_token1_value => sqlcode,
1352 p_token2 => G_SQLERRM_TOKEN,
1353 p_token2_value => sqlerrm);
1354
1355 RETURN( G_RET_STS_UNEXP_ERROR );
1356 END Lock_Row;
1357
1358 -----------------------------------
1359 -- Lock_Row for:OKC_REVIEW_UPLD_TERMS --
1360 -----------------------------------
1361 PROCEDURE Lock_Row(
1362 x_return_status OUT NOCOPY VARCHAR2,
1363
1364 p_REVIEW_UPLD_TERMS_id IN NUMBER,
1365 p_object_version_number IN NUMBER
1366 ) IS
1367 BEGIN
1368
1369 IF (l_debug = 'Y') THEN
1370 Okc_Debug.Log('5700: Entered Lock_Row', 2);
1371 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
1372 END IF;
1373
1374 --------------------------------------------
1375 -- Call the LOCK_ROW for each _B child record
1376 --------------------------------------------
1377 x_return_status := Lock_Row(
1378 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
1379 p_object_version_number => p_object_version_number
1380 );
1381 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1383 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1384 RAISE FND_API.G_EXC_ERROR;
1385 END IF;
1386
1387
1388
1389 IF (l_debug = 'Y') THEN
1390 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
1391 END IF;
1392
1393 EXCEPTION
1394 WHEN FND_API.G_EXC_ERROR THEN
1395 IF (l_debug = 'Y') THEN
1396 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
1397 END IF;
1398 x_return_status := G_RET_STS_ERROR;
1399
1400 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1401 IF (l_debug = 'Y') THEN
1402 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1403 END IF;
1404 x_return_status := G_RET_STS_UNEXP_ERROR;
1405
1406 WHEN OTHERS THEN
1407 IF (l_debug = 'Y') THEN
1408 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1409 END IF;
1410 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1411 p_msg_name => G_UNEXPECTED_ERROR,
1412 p_token1 => G_SQLCODE_TOKEN,
1413 p_token1_value => sqlcode,
1414 p_token2 => G_SQLERRM_TOKEN,
1415 p_token2_value => sqlerrm);
1416 x_return_status := G_RET_STS_UNEXP_ERROR;
1417
1418 END Lock_Row;
1419 ---------------------------------------------------------------------------
1420 -- PROCEDURE Update_Row
1421 ---------------------------------------------------------------------------
1422 -------------------------------------
1423 -- Update_Row for:OKC_REVIEW_UPLD_TERMS --
1424 -------------------------------------
1425 FUNCTION Update_Row(
1426 p_REVIEW_UPLD_TERMS_id IN NUMBER,
1427 p_document_id IN NUMBER,
1428 p_document_type IN VARCHAR2,
1429 p_object_id IN NUMBER,
1430 p_object_type IN VARCHAR2,
1431 p_object_title IN CLOB,
1432 p_object_text IN CLOB,
1433 p_parent_object_type IN VARCHAR2,
1434 p_parent_id IN NUMBER,
1435 p_article_id IN NUMBER,
1436 p_article_version_id IN NUMBER,
1437 p_label IN VARCHAR2,
1438 p_display_seq IN NUMBER,
1439 p_action IN VARCHAR2,
1440 p_error_message_count IN NUMBER,
1441 p_warning_message_count IN NUMBER,
1442 p_new_parent_id IN NUMBER,
1443 p_upload_level IN NUMBER,
1444 p_object_version_number IN NUMBER,
1445 p_last_updated_by IN NUMBER,
1446 p_last_update_login IN NUMBER,
1447 p_last_update_date IN DATE
1448 ) RETURN VARCHAR2 IS
1449
1450 BEGIN
1451
1452 IF (l_debug = 'Y') THEN
1453 Okc_Debug.Log('6400: Entered Update_Row', 2);
1454 END IF;
1455
1456 UPDATE OKC_REVIEW_UPLD_TERMS
1457 SET DOCUMENT_ID = p_document_id,
1458 DOCUMENT_TYPE = p_document_type,
1459 OBJECT_ID = p_object_id,
1460 OBJECT_TYPE = p_object_type,
1461 OBJECT_TITLE = p_object_title,
1462 OBJECT_TEXT = p_object_text,
1463 PARENT_OBJECT_TYPE = p_parent_object_type,
1464 PARENT_ID = p_parent_id,
1465 ARTICLE_ID = p_article_id,
1466 ARTICLE_VERSION_ID = p_article_version_id,
1467 LABEL = p_label,
1468 DISPLAY_SEQ = p_display_seq,
1469 ACTION = p_action,
1470 ERROR_MESSAGE_COUNT = p_error_message_count,
1471 WARNING_MESSAGE_COUNT = p_warning_message_count,
1472 OBJECT_VERSION_NUMBER = p_object_version_number,
1473 NEW_PARENT_ID = p_new_parent_id,
1474 UPLOAD_LEVEL = p_upload_level,
1475 LAST_UPDATED_BY = p_last_updated_by,
1476 LAST_UPDATE_LOGIN = p_last_update_login,
1477 LAST_UPDATE_DATE = p_last_update_date
1478 WHERE REVIEW_UPLD_TERMS_ID = p_REVIEW_UPLD_TERMS_id;
1479
1480 IF (l_debug = 'Y') THEN
1481 Okc_Debug.Log('6500: Leaving Update_Row', 2);
1482 END IF;
1483
1484 RETURN G_RET_STS_SUCCESS ;
1485
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488
1489 IF (l_debug = 'Y') THEN
1490 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1491 END IF;
1492
1493 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1494 p_msg_name => G_UNEXPECTED_ERROR,
1495 p_token1 => G_SQLCODE_TOKEN,
1496 p_token1_value => sqlcode,
1497 p_token2 => G_SQLERRM_TOKEN,
1498 p_token2_value => sqlerrm);
1499
1500 RETURN G_RET_STS_UNEXP_ERROR ;
1501
1502 END Update_Row;
1503
1504 -------------------------------------
1505 -- Update_Row for:OKC_REVIEW_UPLD_TERMS --
1506 -------------------------------------
1507 PROCEDURE Update_Row(
1508 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1509
1510 x_return_status OUT NOCOPY VARCHAR2,
1511
1512 p_REVIEW_UPLD_TERMS_id IN NUMBER,
1513
1514 p_document_id IN NUMBER := NULL,
1515 p_document_type IN VARCHAR2 := NULL,
1516 p_object_id IN NUMBER := NULL,
1517 p_object_type IN VARCHAR2 := NULL,
1518 p_object_title IN CLOB := NULL,
1519 p_object_text IN CLOB := NULL,
1520 p_parent_object_type IN VARCHAR2 := NULL,
1521 p_parent_id IN NUMBER := NULL,
1522 p_article_id IN NUMBER := NULL,
1523 p_article_version_id IN NUMBER := NULL,
1524 p_label IN VARCHAR2 := NULL,
1525 p_display_seq IN NUMBER := NULL,
1526 p_action IN VARCHAR2 := NULL,
1527 p_error_message_count IN NUMBER := NULL,
1528 p_warning_message_count IN NUMBER := NULL,
1529 p_new_parent_id IN NUMBER := NULL,
1530 p_upload_level IN NUMBER := NULL,
1531 p_object_version_number IN NUMBER
1532
1533 ) IS
1534
1535 l_document_id OKC_REVIEW_UPLD_TERMS.DOCUMENT_ID%TYPE;
1536 l_document_type OKC_REVIEW_UPLD_TERMS.DOCUMENT_TYPE%TYPE;
1537 l_object_id OKC_REVIEW_UPLD_TERMS.OBJECT_ID%TYPE;
1538 l_object_type OKC_REVIEW_UPLD_TERMS.OBJECT_TYPE%TYPE;
1539 l_object_title OKC_REVIEW_UPLD_TERMS.OBJECT_TITLE%TYPE;
1540 l_object_text OKC_REVIEW_UPLD_TERMS.OBJECT_TEXT%TYPE;
1541 l_parent_object_type OKC_REVIEW_UPLD_TERMS.PARENT_OBJECT_TYPE%TYPE;
1542 l_parent_id OKC_REVIEW_UPLD_TERMS.PARENT_ID%TYPE;
1543 l_article_id OKC_REVIEW_UPLD_TERMS.ARTICLE_ID%TYPE;
1544 l_article_version_id OKC_REVIEW_UPLD_TERMS.ARTICLE_VERSION_ID%TYPE;
1545 l_label OKC_REVIEW_UPLD_TERMS.LABEL%TYPE;
1546 l_display_seq OKC_REVIEW_UPLD_TERMS.DISPLAY_SEQ%TYPE;
1547 l_action OKC_REVIEW_UPLD_TERMS.ACTION%TYPE;
1548 l_error_message_count OKC_REVIEW_UPLD_TERMS.ERROR_MESSAGE_COUNT%TYPE;
1549 l_warning_message_count OKC_REVIEW_UPLD_TERMS.WARNING_MESSAGE_COUNT%TYPE;
1550 l_object_version_number OKC_REVIEW_UPLD_TERMS.OBJECT_VERSION_NUMBER%TYPE;
1551 l_new_parent_id OKC_REVIEW_UPLD_TERMS.NEW_PARENT_ID%TYPE;
1552 l_upload_level OKC_REVIEW_UPLD_TERMS.UPLOAD_LEVEL%TYPE;
1553 l_last_updated_by OKC_REVIEW_UPLD_TERMS.LAST_UPDATED_BY%TYPE;
1554 l_last_update_login OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_LOGIN%TYPE;
1555 l_last_update_date OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_DATE%TYPE;
1556
1557 BEGIN
1558
1559 IF (l_debug = 'Y') THEN
1560 Okc_Debug.Log('7000: Entered Update_Row', 2);
1561 Okc_Debug.Log('7100: Locking _B row', 2);
1562 END IF;
1563
1564 x_return_status := Lock_row(
1565 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
1566 p_object_version_number => p_object_version_number
1567 );
1568 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1570 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1571 RAISE FND_API.G_EXC_ERROR;
1572 END IF;
1573
1574
1575 IF (l_debug = 'Y') THEN
1576 Okc_Debug.Log('7300: Setting attributes', 2);
1577 END IF;
1578
1579 l_object_version_number := p_object_version_number;
1580 x_return_status := Set_Attributes(
1581 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
1582 p_document_id => p_document_id,
1583 p_document_type => p_document_type,
1584 p_object_id => p_object_id,
1585 p_object_type => p_object_type,
1586 p_object_title => p_object_title,
1587 p_object_text => p_object_text,
1588 p_parent_object_type => p_parent_object_type,
1589 p_parent_id => p_parent_id,
1590 p_article_id => p_article_id,
1591 p_article_version_id => p_article_version_id,
1592 p_label => p_label,
1593 p_display_seq => p_display_seq,
1594 p_action => p_action,
1595 p_error_message_count => p_error_message_count,
1596 p_warning_message_count => p_warning_message_count,
1597 p_object_version_number => l_object_version_number,
1598 p_new_parent_id => p_new_parent_id,
1599 p_upload_level => p_upload_level,
1600 x_document_id => l_document_id,
1601 x_document_type => l_document_type,
1602 x_object_id => l_object_id,
1603 x_object_type => l_object_type,
1604 x_object_title => l_object_title,
1605 x_object_text => l_object_text,
1606 x_parent_object_type => l_parent_object_type,
1607 x_parent_id => l_parent_id,
1608 x_article_id => l_article_id,
1609 x_article_version_id => l_article_version_id,
1610 x_label => l_label,
1611 x_display_seq => l_display_seq,
1612 x_action => l_action,
1613 x_error_message_count => l_error_message_count,
1614 x_warning_message_count => l_warning_message_count,
1615 x_new_parent_id => l_new_parent_id,
1616 x_upload_level => l_upload_level
1617 );
1618 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1620 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1621 RAISE FND_API.G_EXC_ERROR;
1622 END IF;
1623
1624 IF (l_debug = 'Y') THEN
1625 Okc_Debug.Log('7400: Record Validation', 2);
1626 END IF;
1627
1628 --- Validate all non-missing attributes
1629 x_return_status := Validate_Record(
1630 p_validation_level => p_validation_level,
1631 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
1632 p_document_id => l_document_id,
1633 p_document_type => l_document_type,
1634 p_object_id => l_object_id,
1635 p_object_type => l_object_type,
1636 p_object_title => l_object_title,
1637 p_object_text => l_object_text,
1638 p_parent_object_type => l_parent_object_type,
1639 p_parent_id => l_parent_id,
1640 p_article_id => l_article_id,
1641 p_article_version_id => l_article_version_id,
1642 p_label => l_label,
1643 p_display_seq => l_display_seq,
1644 p_action => l_action,
1645 p_error_message_count => l_error_message_count,
1646 p_warning_message_count => l_warning_message_count,
1647 p_new_parent_id => l_new_parent_id,
1648 p_upload_level => l_upload_level
1649 );
1650 --- If any errors happen abort API
1651 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1653 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1654 RAISE FND_API.G_EXC_ERROR;
1655 END IF;
1656
1657 IF (l_debug = 'Y') THEN
1658 Okc_Debug.Log('7500: Filling WHO columns', 2);
1659 END IF;
1660
1661 -- Filling who columns
1662 l_last_update_date := SYSDATE;
1663 l_last_updated_by := FND_GLOBAL.USER_ID;
1664 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1665
1666 -- Object version increment
1667 -- IF Nvl(p_object_version_number, 0) >= 0 THEN
1668 -- l_object_version_number := Nvl( p_object_version_number, 0) + 1;
1669 -- END IF;
1670 l_object_version_number := l_object_version_number + 1; -- l_object_version_number should not be NULL because of Set_Attribute
1671
1672 --------------------------------------------
1673 -- Call the Update_Row for each child record
1674 --------------------------------------------
1675 IF (l_debug = 'Y') THEN
1676 Okc_Debug.Log('7600: Updating Row', 2);
1677 END IF;
1678
1679 x_return_status := Update_Row(
1680 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
1681 p_document_id => l_document_id,
1682 p_document_type => l_document_type,
1683 p_object_id => l_object_id,
1684 p_object_type => l_object_type,
1685 p_object_title => l_object_title,
1686 p_object_text => l_object_text,
1687 p_parent_object_type => l_parent_object_type,
1688 p_parent_id => l_parent_id,
1689 p_article_id => l_article_id,
1690 p_article_version_id => l_article_version_id,
1691 p_label => l_label,
1692 p_display_seq => l_display_seq,
1693 p_action => l_action,
1694 p_error_message_count => l_error_message_count,
1695 p_warning_message_count => l_warning_message_count,
1696 p_object_version_number => l_object_version_number,
1697 p_new_parent_id => l_new_parent_id,
1698 p_upload_level => l_upload_level,
1699 p_last_updated_by => l_last_updated_by,
1700 p_last_update_login => l_last_update_login,
1701 p_last_update_date => l_last_update_date
1702 );
1703 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1705 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1706 RAISE FND_API.G_EXC_ERROR;
1707 END IF;
1708
1709
1710 IF (l_debug = 'Y') THEN
1711 Okc_Debug.Log('7800: Leaving Update_Row', 2);
1712 END IF;
1713
1714 EXCEPTION
1715 WHEN FND_API.G_EXC_ERROR THEN
1716 IF (l_debug = 'Y') THEN
1717 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
1718 END IF;
1719 x_return_status := G_RET_STS_ERROR;
1720
1721 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1722 IF (l_debug = 'Y') THEN
1723 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1724 END IF;
1725 x_return_status := G_RET_STS_UNEXP_ERROR;
1726
1727 WHEN OTHERS THEN
1728 IF (l_debug = 'Y') THEN
1729 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1730 END IF;
1731 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1732 p_msg_name => G_UNEXPECTED_ERROR,
1733 p_token1 => G_SQLCODE_TOKEN,
1734 p_token1_value => sqlcode,
1735 p_token2 => G_SQLERRM_TOKEN,
1736 p_token2_value => sqlerrm);
1737 x_return_status := G_RET_STS_UNEXP_ERROR;
1738
1739 END Update_Row;
1740
1741 ---------------------------------------------------------------------------
1742 -- PROCEDURE Delete_Row
1743 ---------------------------------------------------------------------------
1744 -------------------------------------
1745 -- Delete_Row for:OKC_REVIEW_UPLD_TERMS --
1746 -------------------------------------
1747 FUNCTION Delete_Row(
1748 p_REVIEW_UPLD_TERMS_id IN NUMBER
1749 ) RETURN VARCHAR2 IS
1750
1751 BEGIN
1752
1753 IF (l_debug = 'Y') THEN
1754 Okc_Debug.Log('8200: Entered Delete_Row', 2);
1755 END IF;
1756
1757 DELETE FROM OKC_REVIEW_UPLD_TERMS
1758 WHERE REVIEW_UPLD_TERMS_ID = p_REVIEW_UPLD_TERMS_ID;
1759
1760 IF (l_debug = 'Y') THEN
1761 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
1762 END IF;
1763
1764 RETURN( G_RET_STS_SUCCESS );
1765
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768
1769 IF (l_debug = 'Y') THEN
1770 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1771 END IF;
1772
1773 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1774 p_msg_name => G_UNEXPECTED_ERROR,
1775 p_token1 => G_SQLCODE_TOKEN,
1776 p_token1_value => sqlcode,
1777 p_token2 => G_SQLERRM_TOKEN,
1778 p_token2_value => sqlerrm);
1779
1780 RETURN( G_RET_STS_UNEXP_ERROR );
1781
1782 END Delete_Row;
1783
1784 -------------------------------------
1785 -- Delete_Row for:OKC_REVIEW_UPLD_TERMS --
1786 -------------------------------------
1787 PROCEDURE Delete_Row(
1788 x_return_status OUT NOCOPY VARCHAR2,
1789 p_REVIEW_UPLD_TERMS_id IN NUMBER,
1790 p_object_version_number IN NUMBER
1791 ) IS
1792 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
1793 BEGIN
1794
1795 IF (l_debug = 'Y') THEN
1796 Okc_Debug.Log('8800: Entered Delete_Row', 2);
1797 Okc_Debug.Log('8900: Locking _B row', 2);
1798 END IF;
1799
1800 x_return_status := Lock_row(
1801 p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
1802 p_object_version_number => p_object_version_number
1803 );
1804 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1806 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1807 RAISE FND_API.G_EXC_ERROR;
1808 END IF;
1809
1810
1811 IF (l_debug = 'Y') THEN
1812 Okc_Debug.Log('9100: Removing _B row', 2);
1813 END IF;
1814 x_return_status := Delete_Row( p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id );
1815 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1816 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1817 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1818 RAISE FND_API.G_EXC_ERROR;
1819 END IF;
1820
1821
1822 IF (l_debug = 'Y') THEN
1823 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
1824 END IF;
1825
1826 EXCEPTION
1827 WHEN FND_API.G_EXC_ERROR THEN
1828 IF (l_debug = 'Y') THEN
1829 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
1830 END IF;
1831 x_return_status := G_RET_STS_ERROR;
1832
1833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1834 IF (l_debug = 'Y') THEN
1835 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1836 END IF;
1837 x_return_status := G_RET_STS_UNEXP_ERROR;
1838
1839 WHEN OTHERS THEN
1840 IF (l_debug = 'Y') THEN
1841 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1842 END IF;
1843 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1844 p_msg_name => G_UNEXPECTED_ERROR,
1845 p_token1 => G_SQLCODE_TOKEN,
1846 p_token1_value => sqlcode,
1847 p_token2 => G_SQLERRM_TOKEN,
1848 p_token2_value => sqlerrm);
1849 x_return_status := G_RET_STS_UNEXP_ERROR;
1850
1851 END Delete_Row;
1852
1853
1854 PROCEDURE Accept_Changes (
1855 p_api_version IN NUMBER,
1856 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1857 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1858 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1859 p_mode IN VARCHAR2 := 'NORMAL',
1860
1861 p_document_type IN VARCHAR2,
1862 p_document_id IN NUMBER,
1863 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
1864 p_validation_string IN VARCHAR2 := NULL,
1865
1866 x_return_status OUT NOCOPY VARCHAR2,
1867 x_msg_data OUT NOCOPY VARCHAR2,
1868 x_msg_count OUT NOCOPY NUMBER
1869 ) IS
1870
1871
1872 TYPE rut_id_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.REVIEW_UPLD_TERMS_ID%TYPE INDEX BY BINARY_INTEGER;
1873 TYPE upld_level_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.UPLOAD_LEVEL%TYPE INDEX BY BINARY_INTEGER;
1874 TYPE obj_id_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.OBJECT_ID%TYPE INDEX BY BINARY_INTEGER;
1875 TYPE obj_type_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.OBJECT_TYPE%TYPE INDEX BY BINARY_INTEGER;
1876 TYPE obj_text_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.OBJECT_TEXT%TYPE INDEX BY BINARY_INTEGER;
1877 TYPE pobj_type_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.PARENT_OBJECT_TYPE%TYPE INDEX BY BINARY_INTEGER;
1878 TYPE pobj_id_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.PARENT_ID%TYPE INDEX BY BINARY_INTEGER;
1879 TYPE art_id_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER;
1880 TYPE art_ver_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER;
1881 TYPE ovn_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.OBJECT_VERSION_NUMBER%TYPE INDEX BY BINARY_INTEGER;
1882 TYPE label_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.LABEL%TYPE INDEX BY BINARY_INTEGER;
1883 TYPE disp_seq_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.DISPLAY_SEQ%TYPE INDEX BY BINARY_INTEGER;
1884 TYPE action_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.ACTION%TYPE INDEX BY BINARY_INTEGER;
1885 TYPE non_std_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.NON_STANDARD_FLAG%TYPE INDEX BY BINARY_INTEGER;
1886 TYPE mandatory_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.MANDATORY_FLAG%TYPE INDEX BY BINARY_INTEGER;
1887 TYPE lock_text_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.LOCK_TEXT%TYPE INDEX BY BINARY_INTEGER;
1888 TYPE new_parent_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.NEW_PARENT_ID%TYPE INDEX BY BINARY_INTEGER;
1889 TYPE obj_title_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.OBJECT_TITLE%TYPE INDEX BY BINARY_INTEGER;
1890 TYPE orut_id_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.OLD_REVIEW_UPLD_TERMS_ID%TYPE INDEX BY BINARY_INTEGER;
1891 TYPE curr_obj_id_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1892 TYPE curr_disp_seq_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1893
1894 rut_ids rut_id_tab;
1895 upld_levels upld_level_tab;
1896 obj_ids obj_id_tab;
1897 obj_types obj_type_tab;
1898 obj_texts obj_text_tab;
1899 pobj_types pobj_type_tab;
1900 pobj_ids pobj_id_tab;
1901 art_ids art_id_tab;
1902 art_vers art_ver_tab;
1903 ovns ovn_tab;
1904 labels label_tab;
1905 disp_seqs disp_seq_tab;
1906 actions action_tab;
1907 non_stds non_std_tab;
1908 mandatorys mandatory_tab;
1909 lock_texts lock_text_tab;
1910 new_parents new_parent_tab;
1911 obj_titles obj_title_tab;
1912 orut_ids orut_id_tab;
1913 curr_obj_ids curr_obj_id_tab;
1914 curr_disp_seqs curr_disp_seq_tab;
1915
1916 CURSOR accepted_terms_csr IS
1917 SELECT review_upld_terms_id,
1918 level upload_level,
1919 object_id,
1920 object_type,
1921 object_text,
1922 parent_object_type,
1923 parent_id,
1924 article_id,
1925 article_version_id,
1926 object_version_number,
1927 label,
1928 display_seq,
1929 action,
1930 non_standard_flag,
1931 mandatory_flag,
1932 lock_text,
1933 new_parent_id,
1934 object_title,
1935 old_review_upld_terms_id
1936 FROM okc_review_upld_terms
1937 WHERE document_id = p_document_id
1938 AND document_type = p_document_type
1939 CONNECT BY PRIOR review_upld_terms_id = new_parent_id
1940 START WITH new_parent_id is null
1941 ORDER SIBLINGS BY review_upld_terms_id;
1942
1943 cursor current_num_scheme is
1944 select doc_numbering_scheme from okc_template_usages
1945 where document_type = p_document_type and
1946 document_id = p_document_id;
1947
1948 CURSOR is_article_ibr (p_review_upld_terms_id NUMBER) is
1949 SELECT 'Y'
1950 from okc_article_versions av, okc_review_upld_terms ar
1951 where av.article_version_id = ar.article_version_id
1952 and av.insert_by_reference = 'Y'
1953 and ar.review_upld_terms_id= p_review_upld_terms_id;
1954
1955 CURSOR is_article_mandatory (p_review_upld_terms_id NUMBER) is
1956 SELECT 'Y'
1957 from okc_k_articles_b akb, okc_review_upld_terms ar
1958 where akb.id = ar.object_id
1959 and akb.mandatory_yn = 'Y'
1960 and ar.review_upld_terms_id= p_review_upld_terms_id;
1961
1962 CURSOR is_article_text_locked (p_review_upld_terms_id NUMBER) is
1963 SELECT 'Y'
1964 from okc_article_versions av, okc_review_upld_terms ar
1965 where av.article_version_id = ar.article_version_id
1966 and av.lock_text = 'Y'
1967 and ar.review_upld_terms_id= p_review_upld_terms_id;
1968
1969 cursor get_clause_type_csr(p_review_upld_terms_id NUMBER) is
1970 SELECT
1971 aa.article_type
1972 from okc_articles_all aa, okc_review_upld_terms rev
1973 where
1974 rev.article_id = aa.article_id
1975 and rev.review_upld_terms_id = p_review_upld_terms_id;
1976
1977 is_ibr VARCHAR2(1) := 'N';
1978 is_lock_text VARCHAR2(1) := 'N';
1979 is_mandatory_text VARCHAR2(1) := 'N';
1980 l_sec_with_mandatory_clause VARCHAR2(1) := 'N';
1981 l_display_sequence NUMBER := 0;
1982 l_cat_id NUMBER;
1983 l_article_version_id NUMBER;
1984 l_api_name CONSTANT VARCHAR2(30) := 'Accept_Changes';
1985 l_api_version CONSTANT NUMBER := 1;
1986 l_ref_id NUMBER;
1987 l_ref_type VARCHAR2(30);
1988 l_scn_id NUMBER;
1989 l_user_access VARCHAR2(30);
1990 l_parent_id NUMBER;
1991 l_doc_num_scheme NUMBER;
1992 l_parent_object_title OKC_SECTIONS_B.HEADING%TYPE;
1993 l_parent_object_id OKC_REVIEW_UPLD_TERMS.OBJECT_ID%TYPE;
1994 l_article_type OKC_ARTICLES_ALL.ARTICLE_TYPE%TYPE;
1995 l_root_obj_type OKC_REVIEW_UPLD_TERMS.OBJECT_TYPE%TYPE;
1996 l_scn_code OKC_SECTIONS_B.SCN_CODE%TYPE;
1997
1998
1999 BEGIN
2000
2001 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2002 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10000: Entering Accept_Changes');
2003 END IF;
2004
2005 -- Standard Start of API savepoint
2006 SAVEPOINT g_accept_changes;
2007
2008 -- Standard call to check for call compatibility.
2009 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2010 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2011 END IF;
2012
2013 -- Initialize message list if p_init_msg_list is set to TRUE.
2014 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2015 FND_MSG_PUB.initialize;
2016 END IF;
2017
2018 -- Initialize API return status to success
2019 x_return_status := FND_API.G_RET_STS_SUCCESS;
2020
2021 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_STD','N') THEN
2022 l_user_access := 'STD_AUTHOR';
2023 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_NON_STD','N') THEN
2024 l_user_access := 'NON_STD_AUTHOR';
2025 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_SUPERUSER','N') THEN
2026 l_user_access := 'SUPER_USER';
2027 END IF;
2028 END IF;
2029 ELSE
2030 l_user_access := 'NO_ACCESS';
2031 END IF;
2032
2033 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2034 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10200: After access test, l_user_access='||l_user_access);
2035 END IF;
2036
2037 IF l_user_access NOT IN ('NON_STD_AUTHOR','SUPER_USER') THEN
2038 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2039 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10300: User has no privileges to accept changes');
2040 END IF;
2041
2042 END IF;
2043
2044 OPEN accepted_terms_csr;
2045 FETCH accepted_terms_csr BULK COLLECT INTO
2046 rut_ids,
2047 upld_levels,
2048 obj_ids,
2049 obj_types,
2050 obj_texts,
2051 pobj_types,
2052 pobj_ids,
2053 art_ids,
2054 art_vers,
2055 ovns,
2056 labels,
2057 disp_seqs,
2058 actions,
2059 non_stds,
2060 mandatorys,
2061 lock_texts,
2062 new_parents,
2063 obj_titles,
2064 orut_ids;
2065 CLOSE accepted_terms_csr;
2066
2067 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2068 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10400: After bulk collect of accepted_terms_csr');
2069 END IF;
2070
2071 IF rut_ids.COUNT > 0 THEN
2072 FOR i IN rut_ids.FIRST .. rut_ids.LAST
2073 LOOP
2074 curr_obj_ids(rut_ids(i)) := obj_ids(i);
2075 curr_disp_seqs(rut_ids(i)) := 0;
2076 END LOOP;
2077
2078 END IF; --IF rut_ids.COUNT > 0 THEN
2079
2080 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2081 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10500: After looping thru new_parents_csr');
2082 END IF;
2083
2084 IF rut_ids.COUNT > 0 THEN
2085
2086 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2087 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10600: Review records exist before looping');
2088 END IF;
2089
2090 FOR i IN rut_ids.FIRST .. rut_ids.LAST
2091 LOOP
2092
2093 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2094 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10700: Looping thru review records');
2095 END IF;
2096
2097 l_ref_id := null;
2098 IF (new_parents(i) is not null and pobj_types(i) = 'SECTION') THEN
2099 l_ref_id := curr_obj_ids(new_parents(i));
2100 END IF;
2101
2102 pobj_ids(i) := l_ref_id;
2103
2104 IF new_parents(i) is not NULL THEN
2105 BEGIN
2106 l_display_sequence := curr_disp_seqs(new_parents(i))+10;
2107 EXCEPTION
2108 WHEN NO_DATA_FOUND THEN
2109 l_display_sequence := 10;
2110 END;
2111
2112 curr_disp_seqs(new_parents(i)) := l_display_sequence;
2113 disp_seqs(i) := l_display_sequence;
2114 END IF;
2115
2116 IF actions(i) = 'ADDED' THEN
2117
2118 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2119 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10900: Action is ADDED');
2120 END IF;
2121
2122 IF obj_types(i) = 'SECTION' THEN
2123
2124 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2125 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11000: Object Type is SECTION');
2126 END IF;
2127
2128 IF (to_char(obj_titles(i)) =
2129 Okc_Util.Decode_Lookup('OKC_ARTICLE_SECTION',G_UNASSIGNED_SECTION_CODE)) THEN
2130
2131 l_scn_code := G_UNASSIGNED_SECTION_CODE;
2132
2133 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2134 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11100: Section is Unassigned');
2135 END IF;
2136 ELSE
2137 l_scn_code := null;
2138 END IF;
2139
2140 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2141 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11200: Before calling OKC_TERMS_SECTIONS_GRP.add_section');
2142 END IF;
2143
2144 OKC_TERMS_SECTIONS_GRP.add_section(
2145 p_api_version => p_api_version,
2146 p_init_msg_list => p_init_msg_list,
2147 p_validation_level => p_validation_level,
2148 p_validate_commit => p_validate_commit,
2149 p_validation_string => p_validation_string,
2150 p_commit => p_commit,
2151 p_mode => p_mode,
2152 x_return_status => x_return_status,
2153 x_msg_count => x_msg_count,
2154 x_msg_data => x_msg_data,
2155 p_id => NULL,
2156 p_ref_scn_id => l_ref_id, -- Section ID fo section which was
2157 p_ref_point => 'S', --Possible values 'A'=After,'B'=Before,'S' = Subsection
2158 p_heading => substr(to_char(obj_titles(i)),1,80),
2159 p_description => NULL,
2160 p_document_type => p_document_type,
2161 p_document_id => p_document_id,
2162 p_scn_code => l_scn_code,
2163 p_print_yn => 'Y',
2164 x_id => l_scn_id);
2165
2166 curr_obj_ids(rut_ids(i)) := l_scn_id;
2167 obj_ids(i) := l_scn_id;
2168
2169 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2170 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11300: After calling OKC_TERMS_SECTIONS_GRP.add_section');
2171 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11400: lscn_id ='||l_scn_id);
2172 END IF;
2173
2174 ELSIF obj_types(i) = 'ARTICLE' THEN
2175 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2176 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11500: Object Type is ARTICLE');
2177 END IF;
2178
2179 obj_texts(i) :=
2180 regexp_replace(regexp_replace(obj_texts(i),'<var name="[0-9,a-z,A-Z,_,$]*" type="[A-Z]*" meaning="','[@'),'"/>','@]');
2181
2182 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2183 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11600: Before calling OKC_K_NON_STD_ART_GRP.create_non_std_article');
2184 END IF;
2185
2186 OKC_K_NON_STD_ART_GRP.create_non_std_article(
2187 p_api_version => p_api_version,
2188 p_init_msg_list => p_init_msg_list,
2189 p_validate_commit => p_validate_commit,
2190 p_validation_string => p_validation_string,
2191 p_commit => p_commit,
2192 p_mode => p_mode,
2193 x_return_status => x_return_status,
2194 x_msg_count => x_msg_count,
2195 x_msg_data => x_msg_data,
2196 p_article_title => substr(to_char(obj_titles(i)),1,450),
2197 p_article_type => 'IMPORTED', -- Article Version Attributes
2198 p_article_text => obj_texts(i),
2199 p_provision_yn => 'N',
2200 p_std_article_version_id => art_vers(i),
2201 p_display_name => null,
2202 p_article_description => null,
2203
2204 -- K Article Attributes
2205 p_ref_type => 'SECTION',
2206 p_ref_id => l_ref_id,
2207 p_doc_type => p_document_type,
2208 p_doc_id => p_document_id,
2209 p_cat_id => l_cat_id,
2210
2211 p_amendment_description => NULL,
2212 p_print_text_yn => NULL,
2213 x_cat_id => l_cat_id,
2214 x_article_version_id => l_article_version_id );
2215
2216 obj_ids(i) := l_cat_id;
2217 art_vers(i) := l_article_version_id;
2218
2219 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2220 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11700: Before calling OKC_K_NON_STD_ART_GRP.create_non_std_article');
2221 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11800: lcat_id='||l_cat_id);
2222 END IF;
2223 END IF; --IF obj_types(i) = 'SECTION' THEN
2224 ELSIF actions(i) = 'UPDATED' THEN
2225
2226 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2227 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11900: Action is UPDATED');
2228 END IF;
2229
2230 IF obj_types(i) = 'SECTION' THEN
2231
2232 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2233 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12000: B4 call OKC_TERMS_SECTIONS_GRP.update_section');
2234 END IF;
2235
2236 OKC_TERMS_SECTIONS_GRP.update_section(
2237 p_api_version => p_api_version,
2238 p_init_msg_list => p_init_msg_list,
2239 p_validation_level => p_validation_level,
2240 p_validate_commit => p_validate_commit,
2241 p_validation_string => p_validation_string,
2242 p_commit => p_commit,
2243 p_mode => p_mode,
2244 x_return_status => x_return_status,
2245 x_msg_count => x_msg_count,
2246 x_msg_data => x_msg_data,
2247 p_id => obj_ids(i),
2248 p_section_sequence => l_display_sequence,
2249 p_label => NULL,
2250 p_scn_id => l_ref_id,
2251 p_heading => substr(to_char(obj_titles(i)),1,80),
2252 p_description => NULL,
2253 p_scn_code => FND_API.G_MISS_CHAR,
2254 p_object_version_number => NULL);
2255
2256 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2257 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12100: After OKC_TERMS_SECTIONS_GRP.update_section');
2258 END IF;
2259
2260 ELSIF obj_types(i) = 'ARTICLE' THEN
2261 obj_texts(i) :=
2262 regexp_replace(regexp_replace(obj_texts(i),'<var name="[0-9,a-z,A-Z,_,$]*" type="[A-Z]*" meaning="','[@'),'"/>','@]');
2263
2264 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2265 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12200: Object Type is ARTICLE');
2266 END IF;
2267
2268 IF non_stds(i) = 'Y' THEN
2269
2270 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2271 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12300: Non-Std article');
2272 END IF;
2273
2274 l_article_type := null;
2275
2276 open get_clause_type_csr(rut_ids(i));
2277 fetch get_clause_type_csr into l_article_type;
2278 close get_clause_type_csr;
2279
2280 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2281 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12400: B4 call OKC_K_NON_STD_ART_GRP.update_non_std_article');
2282 END IF;
2283
2284 OKC_K_NON_STD_ART_GRP.update_non_std_article(
2285 p_api_version => p_api_version,
2286 p_init_msg_list => p_init_msg_list,
2287 p_validate_commit => p_validate_commit,
2288 p_validation_string => p_validation_string,
2289 p_commit => p_commit,
2290 p_mode => p_mode,
2291 x_return_status => x_return_status,
2292 x_msg_count => x_msg_count,
2293 x_msg_data => x_msg_data,
2294 p_article_title => substr(to_char(obj_titles(i)),1,80),
2295 p_article_type => l_article_type,
2296
2297 -- Article Version Attributes
2298 p_article_text => obj_texts(i),
2299 p_provision_yn => 'N',
2300 p_article_description => NULL,
2301 p_display_name => substr(to_char(obj_titles(i)),1,450),
2302
2303 -- K Article Attributes
2304 p_doc_type => p_document_type,
2305 p_doc_id => p_document_id,
2306 p_cat_id => obj_ids(i),
2307 p_amendment_description => NULL,
2308 p_print_text_yn => NULL,
2309 x_cat_id => l_cat_id,
2310 x_article_version_id => l_article_version_id ) ;
2311
2312 obj_ids(i) := l_cat_id;
2313 art_vers(i) := l_article_version_id;
2314
2315 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2316 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12500: After OKC_K_NON_STD_ART_GRP.update_non_std_article');
2317 END IF;
2318
2319 ELSE --IF non_stds(i) = 'Y' THEN
2320
2321 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2322 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12600: Std article');
2323 END IF;
2324
2325 is_ibr := 'N';
2326 is_lock_text := 'N';
2327
2328 open is_article_ibr(rut_ids(i));
2329 fetch is_article_ibr into is_ibr;
2330 close is_article_ibr;
2331
2332 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2333 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12700: is_ibr='||is_ibr);
2334 END IF;
2335
2336 IF (is_ibr <> 'Y') THEN
2337
2338 open is_article_text_locked(rut_ids(i));
2339 fetch is_article_text_locked into is_lock_text;
2340 close is_article_text_locked;
2341
2342 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2343 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12800: is_lock_text='||is_lock_text);
2344 END IF;
2345
2346
2347 IF((l_user_access = 'SUPER_USER' and is_lock_text = 'Y') OR
2348 is_lock_text <> 'Y') THEN
2349
2350 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2351 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12900: This is the case of Make Non-Standard');
2352 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13000: Before invoking OKC_K_NON_STD_ART_GRP.create_non_std_article');
2353 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13100: Clause Title=' || obj_titles(i));
2354 END IF;
2355
2356 UPDATE okc_k_articles_b
2357 SET scn_id = l_ref_id
2358 WHERE id = obj_ids(i);
2359
2360 OKC_K_NON_STD_ART_GRP.create_non_std_article(
2361 p_api_version => p_api_version,
2362 p_init_msg_list => p_init_msg_list,
2363 p_validate_commit => p_validate_commit,
2364 p_validation_string => p_validation_string,
2365 p_commit => p_commit,
2366 p_mode => p_mode,
2367 x_return_status => x_return_status,
2368 x_msg_count => x_msg_count,
2369 x_msg_data => x_msg_data,
2370 p_article_title => substr(to_char(obj_titles(i)),1,450),
2371 p_article_type => 'IMPORTED',
2372 -- Article Version Attributes
2373 p_article_text => obj_texts(i),
2374 p_provision_yn => 'N',
2375 p_std_article_version_id => art_vers(i),
2376 p_display_name => substr(to_char(obj_titles(i)),1,450),
2377 p_article_description => null,
2378
2379 -- K Article Attributes
2380 p_ref_type => 'SECTION',
2381 p_ref_id => l_ref_id,
2382 p_doc_type => p_document_type,
2383 p_doc_id => p_document_id,
2384 p_cat_id => obj_ids(i),
2385
2386 p_amendment_description => NULL,
2387 p_print_text_yn => NULL,
2388 x_cat_id => l_cat_id,
2389 x_article_version_id => l_article_version_id );
2390
2391 obj_ids(i) := l_cat_id;
2392 art_vers(i) := l_article_version_id;
2393
2394 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2395 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13200: After OKC_K_NON_STD_ART_GRP.create_non_std_article');
2396 END IF;
2397 END IF; -- if l_user_access...
2398 END IF; -- if ibr_text <> 'Y'
2399 END IF; -- IF non_stds(i) = 'Y' THEN
2400 END IF;
2401
2402 ELSIF (actions(i) = 'DELETED' OR actions(i) = 'MERGED') THEN
2403
2404 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2405 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13300: Action='||actions(i));
2406 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13400: object Type='||obj_types(i));
2407 END IF;
2408
2409 IF obj_types(i) = 'SECTION' THEN
2410 IF p_mode = 'AMEND' THEN
2411 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2412 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13500: p_mode='||p_mode);
2413 END IF;
2414
2415 UPDATE okc_sections_b scn
2416 SET scn.last_updated_by = fnd_global.user_id,
2417 scn.last_update_date = sysdate,
2418 scn.amendment_operation_code = 'DELETED',
2419 scn.summary_amend_operation_code =
2420 okc_terms_util_pvt.get_summary_amend_code(
2421 scn.summary_amend_operation_code,
2422 scn.amendment_operation_code,
2423 'DELETED'),
2424 scn.last_amended_by = fnd_global.user_id,
2425 scn.last_amendment_date = sysdate
2426 WHERE scn.id = obj_ids(i);
2427 ELSE
2428
2429 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2430 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13500: p_mode='||p_mode);
2431 END IF;
2432
2433 DELETE FROM okc_sections_b
2434 WHERE id = obj_ids(i);
2435 END IF; --IF p_mode = 'AMEND' THEN
2436
2437 ELSIF obj_types(i) = 'ARTICLE' THEN
2438
2439 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2440 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13800: Clause Title=' || obj_titles(i));
2441 END IF;
2442
2443 is_mandatory_text := 'N';
2444
2445 open is_article_mandatory(rut_ids(i));
2446 fetch is_article_mandatory into is_mandatory_text;
2447 close is_article_mandatory;
2448
2449 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2450 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13900: is_article_mandatory='|| is_mandatory_text);
2451 END IF;
2452
2453 IF ((l_user_access = 'SUPER_USER' ) OR is_mandatory_text <> 'Y') then
2454 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2455 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14000: Before invoking delete on okc_k_articles_b');
2456 END IF;
2457 IF p_mode = 'AMEND' THEN
2458
2459 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2460 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14100: B4 delete k_art mode=AMEND, objectID='||obj_ids(i));
2461 END IF;
2462
2463 UPDATE okc_k_articles_b kart
2464 SET kart.last_updated_by = fnd_global.user_id,
2465 kart.last_update_date = sysdate,
2466 kart.amendment_operation_code = 'DELETED',
2467 kart.summary_amend_operation_code =
2468 okc_terms_util_pvt.get_summary_amend_code(
2469 kart.summary_amend_operation_code,
2470 kart.amendment_operation_code,
2471 'DELETED'),
2472 kart.last_amended_by = fnd_global.user_id,
2473 kart.last_amendment_date = sysdate
2474 WHERE kart.id = obj_ids(i);
2475 ELSE
2476
2477 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2478 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14200: after delete k_art, objectID='||obj_ids(i));
2479 END IF;
2480
2481 DELETE FROM okc_k_articles_b
2482 WHERE id = obj_ids(i);
2483 END IF;
2484
2485 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2486 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14300: After delete on okc_k_articles_b');
2487 END IF;
2488 END IF; --IF ((l_user_access = 'SUPER_USER' )
2489 END IF; --IF obj_types(i) = 'SECTION' THEN
2490 --ELSE
2491 END IF; --IF actions(i) = 'ADDED'
2492 END LOOP;
2493 END IF; --IF rut_ids.COUNT > 0 THEN
2494
2495 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2496 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14400: Before Bulk update of okc_sections_b');
2497 END IF;
2498
2499 FORALL i IN rut_ids.FIRST..rut_ids.LAST
2500 UPDATE okc_sections_b
2501 SET scn_id = pobj_ids(i),
2502 section_sequence = disp_seqs(i)
2503 WHERE id = obj_ids(i)
2504 AND obj_types(i) = 'SECTION';
2505
2506 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2507 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14500: Before Bulk update of okc_k_articles_b');
2508 END IF;
2509
2510 FORALL i IN rut_ids.FIRST..rut_ids.LAST
2511 UPDATE okc_k_articles_b
2512 SET scn_id = pobj_ids(i),
2513 display_sequence = disp_seqs(i)
2514 WHERE id = obj_ids(i)
2515 AND obj_types(i) = 'ARTICLE';
2516
2517 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2518 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14600: Before delete on Review Tables');
2519 END IF;
2520
2521 OKC_TEMPLATE_USAGES_GRP.update_template_usages(
2522 p_api_version => l_api_version,
2523 p_init_msg_list => p_init_msg_list ,
2524 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2525 p_commit => FND_API.G_FALSE,
2526 x_return_status => x_return_status,
2527 x_msg_count => x_msg_count,
2528 x_msg_data => x_msg_data,
2529 p_document_type => p_document_type,
2530 p_document_id => p_document_id,
2531 p_lock_terms_flag => 'N');
2532
2533 delete from okc_REVIEW_UPLD_TERMS where document_type = p_document_type and
2534 document_id = p_document_id;
2535
2536 delete from okc_review_upld_header where document_type = p_document_type and
2537 document_id = p_document_id;
2538
2539 delete from okc_review_messages where REVIEW_UPLD_TERMS_ID
2540 in (select REVIEW_UPLD_TERMS_ID from okc_REVIEW_UPLD_TERMS where document_type = p_document_type and
2541 document_id = p_document_id);
2542
2543 delete from OKC_REVIEW_VAR_VALUES where REVIEW_UPLD_TERMS_ID
2544 in (select REVIEW_UPLD_TERMS_ID from okc_REVIEW_UPLD_TERMS where document_type = p_document_type and
2545 document_id = p_document_id);
2546
2547 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2548 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14700: After delete on Review Tables');
2549 END IF;
2550
2551 open current_num_scheme;
2552 fetch current_num_scheme into l_doc_num_scheme;
2553 close current_num_scheme;
2554
2555 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2556 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14800: l_doc_num_scheme=' || l_doc_num_scheme);
2557 END IF;
2558
2559 IF (l_doc_num_scheme is NOT NULL) THEN
2560 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2561 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14900: Before invoking apply_numbering_scheme');
2562 END IF;
2563
2564 OKC_NUMBER_SCHEME_GRP.apply_numbering_scheme(
2565 p_api_version => p_api_version,
2566 p_init_msg_list => p_init_msg_list,
2567 x_return_status => x_return_status,
2568 x_msg_count => x_msg_count,
2569 x_msg_data => x_msg_data,
2570 p_commit => p_commit,
2571 p_validation_string => p_validation_string,
2572 p_doc_type => p_document_type,
2573 p_doc_id => p_document_id,
2574 p_num_scheme_id => l_doc_num_scheme);
2575
2576 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15000: After invoking apply_numbering_scheme');
2578 END IF;
2579 END IF; --IF (l_doc_num_scheme is NOT NULL) THEN
2580
2581 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2582 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15100: After Accept Changes');
2583 END IF;
2584
2585 -- Standard check of p_commit
2586 IF FND_API.To_Boolean( p_commit ) THEN
2587 COMMIT WORK;
2588 END IF;
2589
2590 EXCEPTION
2591 WHEN FND_API.G_EXC_ERROR THEN
2592 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2593 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15200: Leaving accept_Changes: OKC_API.G_EXCEPTION_ERROR Exception');
2594 END IF;
2595 ROLLBACK TO g_accept_changes;
2596 x_return_status := G_RET_STS_ERROR ;
2597 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2598
2599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2600 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2601 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15300: Leaving accept_Changes: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2602 END IF;
2603 ROLLBACK TO g_accept_Changes;
2604 x_return_status := G_RET_STS_UNEXP_ERROR ;
2605 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2606
2607 WHEN OTHERS THEN
2608 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2609 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15400: Leaving accept_changes because of EXCEPTION: '||sqlerrm);
2610 END IF;
2611
2612 ROLLBACK TO g_accept_changes;
2613 x_return_status := G_RET_STS_UNEXP_ERROR ;
2614 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2615 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2616 END IF;
2617 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2618 END Accept_Changes;
2619
2620 PROCEDURE Reject_Changes (
2621 p_api_version IN NUMBER,
2622 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2623 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2624 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2625
2626 p_document_type IN VARCHAR2,
2627 p_document_id IN NUMBER,
2628
2629 x_return_status OUT NOCOPY VARCHAR2,
2630 x_msg_data OUT NOCOPY VARCHAR2,
2631 x_msg_count OUT NOCOPY NUMBER
2632 ) IS
2633 l_api_name CONSTANT VARCHAR2(30) := 'Reject_Changes';
2634 l_api_version CONSTANT NUMBER := 1;
2635
2636 BEGIN
2637 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2638 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering Reject_Changes');
2639 END IF;
2640
2641 -- Standard Start of API savepoint
2642 SAVEPOINT g_reject_changes;
2643 -- Standard call to check for call compatibility.
2644 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2646 END IF;
2647 -- Initialize message list if p_init_msg_list is set to TRUE.
2648 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2649 FND_MSG_PUB.initialize;
2650 END IF;
2651 -- Initialize API return status to success
2652 x_return_status := FND_API.G_RET_STS_SUCCESS;
2653
2654 OKC_REVIEW_UPLD_TERMS_PVT.delete_uploaded_terms(
2655 p_api_version => l_api_version,
2656 p_init_msg_list => p_init_msg_list ,
2657 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2658 p_commit => FND_API.G_FALSE,
2659 x_return_status => x_return_status,
2660 x_msg_count => x_msg_count,
2661 x_msg_data => x_msg_data,
2662 p_document_type => p_document_type,
2663 p_document_id => p_document_id
2664 );
2665
2666 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2667 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: After delete_uploaded_terms');
2668 END IF;
2669 -- Standard check of p_commit
2670 IF FND_API.To_Boolean( p_commit ) THEN
2671 COMMIT WORK;
2672 END IF;
2673
2674 EXCEPTION
2675 WHEN FND_API.G_EXC_ERROR THEN
2676 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2677 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Reject_Changes: OKC_API.G_EXCEPTION_ERROR Exception');
2678 END IF;
2679 ROLLBACK TO g_reject_changes;
2680 x_return_status := G_RET_STS_ERROR ;
2681 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2682
2683 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2684 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2685 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Reject_Changes: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2686 END IF;
2687 ROLLBACK TO g_reject_Changes;
2688 x_return_status := G_RET_STS_UNEXP_ERROR ;
2689 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2690
2691 WHEN OTHERS THEN
2692 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2693 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving reject_changes because of EXCEPTION: '||sqlerrm);
2694 END IF;
2695
2696 ROLLBACK TO g_reject_changes;
2697 x_return_status := G_RET_STS_UNEXP_ERROR ;
2698 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2699 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2700 END IF;
2701 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2702
2703 END Reject_Changes;
2704
2705
2706 PROCEDURE Delete_Uploaded_Terms (
2707 p_api_version IN NUMBER,
2708 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2709 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2710 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2711
2712 p_document_type IN VARCHAR2,
2713 p_document_id IN NUMBER,
2714
2715 x_return_status OUT NOCOPY VARCHAR2,
2716 x_msg_data OUT NOCOPY VARCHAR2,
2717 x_msg_count OUT NOCOPY NUMBER
2718 ) IS
2719 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Uploaded_Terms';
2720 l_api_version CONSTANT NUMBER := 1;
2721
2722 BEGIN
2723 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2724 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering Delete_Uploaded_Terms');
2725 END IF;
2726
2727 -- Standard Start of API savepoint
2728 SAVEPOINT g_delete_uploaded_terms;
2729 -- Standard call to check for call compatibility.
2730 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2732 END IF;
2733 -- Initialize message list if p_init_msg_list is set to TRUE.
2734 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2735 FND_MSG_PUB.initialize;
2736 END IF;
2737 -- Initialize API return status to success
2738 x_return_status := FND_API.G_RET_STS_SUCCESS;
2739
2740 DELETE
2741 FROM OKC_REVIEW_MESSAGES m
2742 WHERE m.REVIEW_UPLD_TERMS_id IN
2743 (SELECT REVIEW_UPLD_TERMS_id
2744 FROM okc_REVIEW_UPLD_TERMS
2745 WHERE document_id = p_document_id
2746 AND document_type = p_document_type);
2747
2748 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2749 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: After Delete Review Messages');
2750 END IF;
2751
2752 DELETE
2753 FROM OKC_REVIEW_VAR_VALUES v
2754 WHERE v.REVIEW_UPLD_TERMS_id IN
2755 (SELECT REVIEW_UPLD_TERMS_id
2756 FROM okc_REVIEW_UPLD_TERMS
2757 WHERE document_id = p_document_id
2758 AND document_type = p_document_type);
2759
2760 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2761 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: After delete review variables');
2762 END IF;
2763
2764 DELETE
2765 FROM OKC_REVIEW_UPLD_TERMS
2766 WHERE document_id = p_document_id
2767 AND document_type = p_document_type;
2768
2769 DELETE
2770 FROM OKC_REVIEW_UPLD_HEADER
2771 WHERE document_id = p_document_id
2772 AND document_type = p_document_type;
2773
2774 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2775 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: After delete review terms');
2776 END IF;
2777
2778 -- Standard check of p_commit
2779 IF FND_API.To_Boolean( p_commit ) THEN
2780 COMMIT WORK;
2781 END IF;
2782
2783 EXCEPTION
2784 WHEN FND_API.G_EXC_ERROR THEN
2785 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2786 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Delete_Uploaded_Terms: OKC_API.G_EXCEPTION_ERROR Exception');
2787 END IF;
2788 ROLLBACK TO g_delete_uploaded_terms;
2789 x_return_status := G_RET_STS_ERROR ;
2790 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2791
2792 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2793 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2794 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Delete_Uploaded_terms: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2795 END IF;
2796 ROLLBACK TO g_delete_uploaded_terms;
2797 x_return_status := G_RET_STS_UNEXP_ERROR ;
2798 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2799
2800 WHEN OTHERS THEN
2801 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2802 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving delete_uploaded_Terms because of EXCEPTION: '||sqlerrm);
2803 END IF;
2804
2805 ROLLBACK TO g_delete_uploaded_terms;
2806 x_return_status := G_RET_STS_UNEXP_ERROR ;
2807 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2808 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2809 END IF;
2810 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2811
2812 END delete_uploaded_terms;
2813
2814
2815 PROCEDURE Sync_Review_Tables (
2816 p_api_version IN NUMBER,
2817 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2818 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2819 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2820 p_validation_string IN VARCHAR2 := NULL,
2821 p_document_type IN VARCHAR2,
2822 p_document_id IN NUMBER,
2823
2824 x_return_status OUT NOCOPY VARCHAR2,
2825 x_msg_data OUT NOCOPY VARCHAR2,
2826 x_msg_count OUT NOCOPY NUMBER
2827 ) IS
2828 l_api_name CONSTANT VARCHAR2(30) := 'Sync_Review_Tables';
2829 l_api_version CONSTANT NUMBER := 1;
2830 l_doc_exists VARCHAR2(1);
2831 l_rev_id_for_doc OKC_REVIEW_UPLD_TERMS.REVIEW_UPLD_TERMS_ID%TYPE;
2832 l_unassigned_scn_id OKC_SECTIONS_B.ID%TYPE;
2833 l_clauses_no_parent_exist VARCHAR2(1);
2834 l_clause_title FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2835 l_section_title FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2836 l_clause_counter NUMBER;
2837 l_section_counter NUMBER;
2838 l_doc_num_scheme OKC_TEMPLATE_USAGES.DOC_NUMBERING_SCHEME%TYPE;
2839 l_user_access VARCHAR2(30);
2840 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2841 l_sequence OKC_K_ARTICLES_B.DISPLAY_SEQUENCE%TYPE;
2842 l_count NUMBER;
2843 l_prev_rev_id OKC_REVIEW_UPLD_TERMS.REVIEW_UPLD_TERMS_ID%TYPE;
2844 l_prev_new_parent_id OKC_REVIEW_UPLD_TERMS.NEW_PARENT_ID%TYPE;
2845 l_intent OKC_BUS_DOC_TYPES_B.INTENT%TYPE;
2846
2847 cursor check_document_row_exists IS
2848 select REVIEW_UPLD_TERMS_id from okc_REVIEW_UPLD_TERMS
2849 where document_type = p_document_type and document_id = p_document_id
2850 and object_id = p_document_id and object_type = p_document_type;
2851
2852 cursor unresolved_del_rec is
2853 select REVIEW_UPLD_TERMS_id, object_id, object_type, parent_id, parent_object_type,
2854 new_parent_id from okc_REVIEW_UPLD_TERMS
2855 where document_type = p_document_type
2856 and document_id = p_document_id
2857 and action = 'DELETED'
2858 and new_parent_id is null
2859 order by object_type ;
2860
2861 cursor clauses_without_parent_exist is
2862 select 'Y'
2863 from OKC_REVIEW_UPLD_TERMS
2864 where document_type = p_document_type
2865 and document_id = p_document_id
2866 and ( (object_type = 'ARTICLE'
2867 and new_parent_id IS NULL)
2868 OR (object_type = 'ARTICLE'
2869 and new_parent_id = (select review_upld_terms_id
2870 from okc_review_upld_terms
2871 where document_type = p_document_type
2872 and document_id = p_document_id
2873 and object_id = p_document_id
2874 and object_type = p_document_type))
2875 OR (object_type = 'SECTION' and new_parent_id IS NULL)
2876 );
2877
2878
2879 cursor clauses_without_parent_id is
2880 select REVIEW_UPLD_TERMS_id, object_id, object_type, parent_id, parent_object_type,
2881 new_parent_id from okc_REVIEW_UPLD_TERMS
2882 where document_type = p_document_type and document_id = p_document_id
2883 and object_type = 'ARTICLE' and new_parent_id IS NULL and article_id is null and article_version_id is null;
2884
2885 cursor clauses_no_parent_id_and_moved is
2886 select REVIEW_UPLD_TERMS_id, object_id, object_type, parent_id, parent_object_type,
2887 new_parent_id
2888 from okc_REVIEW_UPLD_TERMS
2889 where document_type = p_document_type
2890 and document_id = p_document_id
2891 and ((object_type = 'ARTICLE'
2892 and new_parent_id IS NULL
2893 and article_id is not null
2894 and article_version_id is not null)
2895 OR (object_type = 'SECTION' and new_parent_id IS NULL));
2896
2897 cursor unassigned_section_exists is
2898 select REVIEW_UPLD_TERMS_id from okc_REVIEW_UPLD_TERMS
2899 where document_type = p_document_type and document_id = p_document_id
2900 and object_type = 'SECTION' and to_char(object_title) = Okc_Util.Decode_Lookup('OKC_ARTICLE_SECTION',G_UNASSIGNED_SECTION_CODE);
2901
2902 cursor review_variable_values is
2903 select rev_var.variable_value_id, rev_var.REVIEW_UPLD_TERMS_id, rev_var.variable_name,
2904 rev_var.language, rev_var.variable_code, rev_var.variable_type, rev_var.attribute_value_set_id,
2905 rev_var.variable_value_id
2906 from OKC_REVIEW_VAR_VALUES rev_var, okc_REVIEW_UPLD_TERMS rev
2907 where rev_var.REVIEW_UPLD_TERMS_id = rev.REVIEW_UPLD_TERMS_id
2908 and rev.document_type = p_document_type and rev.document_id = p_document_id;
2909
2910
2911 cursor current_variable_values is
2912 select art_var.cat_id, art_var.variable_code, art_var.variable_type, art_var.external_yn,
2913 art_var.attribute_value_set_id,art_var.variable_value_id, art_var.variable_value
2914 from okc_k_art_variables art_var, okc_k_articles_b kart
2915 where art_var.cat_id = kart.id
2916 and kart.document_type = p_document_type and kart.document_id = p_document_id;
2917
2918 cursor variable_values_changed is
2919 SELECT rev_var.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,rev_var.variable_code variable_code,
2920 rev_var.variable_name variable_name,
2921 kart_var.variable_value kart_variable_value, rev_var.variable_value rev_variable_value,
2922 kart_var.variable_type, rev_var.variable_value_id
2923
2924 FROM OKC_REVIEW_VAR_VALUES rev_var, okc_k_art_variables kart_var,
2925 OKC_REVIEW_UPLD_TERMS rev, okc_article_versions av
2926 WHERE rev.object_id = kart_var.cat_id
2927 AND rev.REVIEW_UPLD_TERMS_id = rev_var.REVIEW_UPLD_TERMS_id
2928 AND kart_var.variable_code = rev_var.variable_code
2929 AND av.article_version_id = rev.article_version_id
2930 AND ((kart_var.variable_type='U' and kart_var.variable_value is null
2931 and (kart_var.variable_value is null
2932 and (rev_var.variable_value is not null and rev_var.variable_value <> '_________' )
2933 and exists(select 'x' from okc_bus_variables_tl bustl where bustl.variable_code = rev_var.variable_code)) OR
2934 (kart_var.variable_value is not null and rev_var.variable_value <> kart_var.variable_value))
2935 OR (kart_var.variable_type<>'U' and exists
2936 (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
2937 and var_doc.doc_Type = p_document_type))
2938 )
2939 and rev.document_type = p_document_type and rev.document_id = p_document_id
2940 and nvl(rev.action,'NOCHANGE') not in ('DELETED','MERGED')
2941 and nvl(av.insert_by_reference,'N') <> 'Y';
2942
2943 cursor variables_removed is
2944 select rev.review_upld_terms_id, kart_var.variable_code, variable_name, language, description
2945 from okc_k_art_variables kart_var, okc_review_upld_terms rev, okc_bus_variables_tl bustl, okc_article_versions av
2946 where rev.object_id = kart_var.cat_id
2947 and rev.document_type = p_document_type and rev.document_id = p_document_id
2948 and rev.article_version_id = av.article_version_id
2949 and kart_var.variable_code not in (select variable_code from okc_review_Var_values rev_var
2950 where rev_var.review_upld_terms_id = rev.review_upld_terms_id)
2951 and rev.action not in ('DELETED','MERGED')
2952 and bustl.variable_code = kart_var.variable_code
2953 and language = userenv('LANG')
2954 and nvl(av.insert_by_reference,'N') <> 'Y';
2955
2956 cursor valid_variable_added(p_intent VARCHAR2) is
2957 select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type,
2958 decode(rev_var.variable_value,
2959 NULL, 'N',
2960 'Y') modified
2961 from okc_review_upld_Terms rev, okc_review_var_values rev_var, okc_article_versions av
2962 where rev.document_type = p_document_type and rev.document_id = p_document_id
2963 and rev_var.review_upld_terms_id = rev.review_upld_terms_id
2964 and av.article_version_id = rev.article_version_id
2965 and not exists(
2966 select 'x' from okc_k_art_variables kart_var, okc_review_upld_Terms rev_upld where
2967 rev_upld.review_upld_Terms_id = rev_var.review_upld_terms_id
2968 and rev_upld.object_id = kart_var.cat_id
2969 and rev_var.variable_code = kart_var.variable_code
2970 and (
2971 (rev_var.variable_type = 'U'
2972 and exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
2973 and busb.variable_intent = p_intent)
2974 )
2975 OR
2976 (rev_var.variable_type <> 'U'
2977 and exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
2978 and var_doc.doc_type = p_document_type)
2979 )
2980 )
2981
2982 )
2983 and rev.action not in ('DELETED','MERGED')
2984 and nvl(av.insert_by_reference,'N') <> 'Y';
2985
2986 cursor invalid_variable_added(p_intent VARCHAR2) is
2987 select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type
2988 from okc_review_upld_terms rev, okc_review_var_values rev_var
2989 where document_type = p_document_type and rev.document_id = p_document_id
2990 and rev_var.review_upld_terms_id = rev.review_upld_terms_id
2991 and ((rev_var.variable_type in ('S','D') and not exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
2992 and var_doc.doc_type = p_document_type))
2993 OR (rev_var.variable_type = 'U' and not exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
2994 and busb.variable_intent = p_intent))
2995 )
2996 and rev.action not in ('DELETED','MERGED') ;
2997
2998 cursor valid_var_new_clause(p_intent VARCHAR2) is
2999 select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type,
3000 decode(rev_var.variable_value,
3001 NULL, 'N', 'Y') modified
3002 from okc_review_upld_Terms rev, okc_review_var_values rev_var
3003 where rev.document_type = p_document_type and rev.document_id = p_document_id
3004 and rev_var.review_upld_terms_id = rev.review_upld_terms_id
3005 and (
3006 (rev_var.variable_type = 'U'
3007 and exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
3008 and busb.variable_intent = p_intent)
3009 )
3010 OR
3011 (rev_var.variable_type <> 'U'
3012 and exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
3013 and var_doc.doc_type = p_document_type)
3014 )
3015 )
3016 and rev.action = 'ADDED' ;
3017
3018
3019 cursor valid_new_var_ibr(p_intent VARCHAR2) is
3020 select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type,
3021 decode(rev_var.variable_value,
3022 NULL, 'N', 'Y') modified
3023 from okc_review_upld_terms rev, okc_review_var_values rev_var, okc_article_versions av
3024 where rev.document_type = p_document_type and rev.document_id = p_document_id
3025 and rev_var.review_upld_terms_id = rev.review_upld_terms_id
3026 and av.article_version_id = rev.article_version_id
3027 and (
3028 (rev_var.variable_type = 'U'
3029 and exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
3030 and busb.variable_intent = p_intent)
3031 )
3032 OR
3033 (rev_var.variable_type <> 'U'
3034 and exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
3035 and var_doc.doc_type = p_document_type)
3036 )
3037 )
3038
3039 and nvl(av.insert_by_reference,'N') = 'Y';
3040
3041 cursor empty_title_csr is
3042 SELECT rev.object_title, rev.REVIEW_UPLD_TERMS_id, rev.object_type
3043 from okc_REVIEW_UPLD_TERMS rev
3044 where document_type = p_document_type and
3045 document_id = p_document_id and
3046 object_title is null
3047 and object_type IN ('ARTICLE', 'SECTION');
3048 cursor current_num_scheme is
3049 select doc_numbering_scheme from okc_template_usages
3050 where document_type = p_document_type and
3051 document_id = p_document_id;
3052
3053
3054 CURSOR is_article_ibr(p_user_access VARCHAR2) is
3055 SELECT 'Y', ACTION, review_upld_terms_id, object_title
3056 from okc_article_versions av, okc_review_upld_terms ar
3057 where av.article_version_id = ar.article_version_id
3058 and av.insert_by_reference = 'Y'
3059 and ar.document_type = p_document_type
3060 and ar.document_id = p_document_id
3061 and ar.object_type = 'ARTICLE'
3062 and ar.action = 'UPDATED'
3063 and p_user_access <> 'SUPER_USER';
3064
3065 CURSOR is_article_mandatory(p_user_access VARCHAR2) is
3066 SELECT 'Y', ACTION, review_upld_terms_id, object_title
3067 from okc_k_articles_b akb, okc_review_upld_terms ar
3068 where akb.id = ar.object_id
3069 and akb.mandatory_yn = 'Y'
3070 and ar.document_type = p_document_type
3071 and ar.document_id = p_document_id
3072 and ar.object_type = 'ARTICLE'
3073 and ar.action = 'DELETED'
3074 and p_user_access <> 'SUPER_USER';
3075
3076 CURSOR is_article_text_locked(p_user_access VARCHAR2) is
3077 SELECT 'Y', ACTION, review_upld_terms_id, object_title
3078 from okc_article_versions av, okc_review_upld_terms ar
3079 where av.article_version_id = ar.article_version_id
3080 and av.lock_text = 'Y'
3081 and ar.document_type = p_document_type
3082 and ar.document_id = p_document_id
3083 and ar.object_type = 'ARTICLE'
3084 and ar.action = 'UPDATED'
3085 and p_user_access <> 'SUPER_USER';
3086
3087 CURSOR update_err_warn_csr is
3088 select
3089 count(*) err_warn_count,
3090 rev_msg.review_upld_terms_id,
3091 rev_msg.error_severity
3092 from
3093 okc_review_messages rev_msg ,
3094 okc_review_upld_terms rev_trm
3095 where
3096 rev_msg.review_upld_terms_id = rev_trm.review_upld_terms_id
3097 and rev_trm.document_type = p_document_type
3098 and rev_trm.document_id = p_document_id
3099 group by rev_msg.review_upld_terms_id, rev_msg.error_severity;
3100
3101 /* This cursor is for debugging purposes. This cursor is invoked only when logging is enabled
3102 */
3103 cursor get_updated_articles_csr is
3104 select review_upld_terms_id,
3105 object_title,
3106 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ltrim(ut.object_text),'&NBSP;| ',' '),
3107 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>|
3108 <A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>|
3109 <STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3110 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3111 |<HR>|</HR>|<hr>|</hr>',''),unistr('\00a0'),' '),' ',''),'&|&|<Palign="justify">|<divalign="both">','') rev_text,
3112 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ver.article_text,'&NBSP;| ',' '),
3113 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>|<A>|<a>|</A>|</a>
3114 |<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>|
3115 <STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3116 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3117 |<HR>|</HR>|<hr>|</hr>',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">','') ver_text,
3118 NVL(dbms_lob.compare(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ltrim(ut.object_text),
3119 '&NBSP;| ',' '),'<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3120 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>
3121 |<STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3122 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3123 |<HR>|</HR>|<hr>|</hr>',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">','') ,
3124 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ver.article_text,'&NBSP;| ',' '),
3125 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3126 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>
3127 |<STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3128 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3129 |<HR>|</HR>|<hr>|</hr>',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">','')),-1) diff
3130 from okc_review_upld_terms ut, okc_article_versions ver
3131 where ut.article_version_id = ver.article_version_id
3132 and ut.article_version_id is not null
3133 and ut.action = 'UPDATED'
3134 and ut.document_type = p_document_type
3135 and ut.document_id = p_document_id;
3136
3137 cursor check_sec_clause_title is
3138 select review_upld_terms_id,
3139 object_title, object_type
3140 from okc_review_upld_terms rev
3141 where rev.document_type = p_document_type
3142 and rev.document_id = p_document_id
3143 and rev.object_type in ('SECTION','ARTICLE')
3144 and rev.action in ('ADDED','UPDATED')
3145 and ((object_type = 'SECTION' and length(to_char(object_title)) > 80) OR (object_type = 'ARTICLE' and length(to_char(object_title)) > 450)) ;
3146
3147 cursor terms_disp_csr is
3148 select review_upld_terms_id, object_type, object_id, object_title, action, display_seq, new_parent_id
3149 from okc_review_upld_Terms
3150 where document_type = p_document_type
3151 and document_id = p_document_id
3152 and nvl(action,'XXX') <> 'DELETED'
3153 start with new_parent_id is null
3154 connect by prior review_upld_terms_id = new_parent_id
3155 order siblings by review_upld_terms_id;
3156
3157 cursor parent_ids_csr is
3158 select review_upld_terms_id
3159 from okc_review_upld_Terms
3160 where document_type = p_document_type
3161 and document_id = p_document_id ;
3162
3163 cursor deleted_terms_csr is
3164 select new_parent_id,
3165 display_seq
3166 from okc_review_upld_Terms
3167 where document_type = p_document_type
3168 and document_id = p_document_id
3169 and action = 'DELETED';
3170
3171 --TYPE disp_seq_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3172 TYPE new_parent_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.NEW_PARENT_ID%TYPE INDEX BY BINARY_INTEGER;
3173 TYPE disp_seq_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.DISPLAY_SEQ%TYPE INDEX BY BINARY_INTEGER;
3174
3175 curr_disp_seqs disp_seq_tab;
3176 del_disp_seqs disp_seq_tab;
3177 del_new_parents new_parent_tab;
3178 --disp_seqs disp_seq_tab;
3179
3180 cursor get_intent_csr is
3181 select intent from okc_bus_doc_types_b
3182 where document_type = p_document_type;
3183 BEGIN
3184
3185 -- Standard Start of API savepoint
3186 SAVEPOINT g_reject_changes;
3187 -- Standard call to check for call compatibility.
3188 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3190 END IF;
3191 -- Initialize message list if p_init_msg_list is set to TRUE.
3192 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3193 FND_MSG_PUB.initialize;
3194 END IF;
3195 -- Initialize API return status to success
3196 x_return_status := FND_API.G_RET_STS_SUCCESS;
3197 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_STD','N') THEN
3198 l_user_access := 'STD_AUTHOR';
3199 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_NON_STD','N') THEN
3200 l_user_access := 'NON_STD_AUTHOR';
3201 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_SUPERUSER','N') THEN
3202 l_user_access := 'SUPER_USER';
3203 END IF;
3204 END IF;
3205 ELSE
3206 l_user_access := 'NO_ACCESS';
3207 END IF;
3208
3209 open get_intent_csr;
3210 fetch get_intent_csr into l_intent;
3211 close get_intent_csr;
3212
3213 open check_document_row_exists;
3214 fetch check_document_row_exists into l_doc_exists;
3215 if (check_document_row_exists%NOTFOUND) THEN
3216 INSERT into okc_REVIEW_UPLD_TERMS(
3217 REVIEW_UPLD_TERMS_ID,
3218 DOCUMENT_ID,
3219 DOCUMENT_TYPE,
3220 OBJECT_ID,
3221 OBJECT_TYPE,
3222 OBJECT_TITLE,
3223 OBJECT_TEXT,
3224 PARENT_OBJECT_TYPE,
3225 PARENT_ID,
3226 ARTICLE_ID,
3227 ARTICLE_VERSION_ID,
3228 OBJECT_VERSION_NUMBER,
3229 LABEL,
3230 DISPLAY_SEQ,
3231 ACTION,
3232 ERROR_MESSAGE_COUNT,
3233 WARNING_MESSAGE_COUNT,
3234 NEW_PARENT_ID,
3235 LAST_UPDATE_LOGIN,
3236 CREATED_BY,
3237 CREATION_DATE,
3238 LAST_UPDATED_BY,
3239 LAST_UPDATE_DATE)
3240 (
3241 SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL,
3242 P_DOCUMENT_ID,
3243 P_DOCUMENT_TYPE,
3244 p_document_id,
3245 P_DOCUMENT_TYPE,
3246 okc_terms_util_pvt.get_message('OKC','OKC_TERMS_CONTRACT_TERMS'),
3247 null,
3248 null,
3249 null,
3250 null,
3251 null,
3252 1,
3253 null,
3254 null,
3255 null,
3256 null,
3257 null,
3258 null,
3259 FND_GLOBAL.LOGIN_ID,
3260 FND_GLOBAL.USER_ID,
3261 SYSDATE,
3262 FND_GLOBAL.USER_ID,
3263 SYSDATE from dual);
3264
3265 END IF;
3266 CLOSE check_document_row_exists;
3267
3268 open check_document_row_exists;
3269 fetch check_document_row_exists into l_rev_id_for_doc;
3270 close check_document_row_exists;
3271
3272
3273 update okc_REVIEW_UPLD_TERMS
3274 set new_parent_id = (select REVIEW_UPLD_TERMS_id from okc_REVIEW_UPLD_TERMS where
3275 document_type = p_document_type and document_id = p_document_id
3276 and object_type = p_document_type and object_id = p_document_id)
3277 where document_type = p_document_type and document_id = p_document_id
3278 and object_type = 'SECTION' and new_parent_id = p_document_id;
3279
3280
3281 update okc_REVIEW_UPLD_TERMS rev
3282 set rev.article_id = (select sav_sae_id from okc_k_articles_b
3283 where document_type = p_document_type
3284 and document_id = p_document_id
3285 and id = rev.object_id)
3286
3287 ,rev.article_version_id = (select article_version_id from okc_k_articles_b
3288 where document_type = p_document_type
3289 and document_id = p_document_id
3290 and id = rev.object_id)
3291 where document_type = p_document_type
3292 and document_id = p_document_id
3293 and object_type = 'ARTICLE'
3294 and object_id is not null
3295 and article_id is null
3296 and article_version_id is null
3297 and exists (select 1 from okc_k_articles_b kart
3298 where document_type = p_document_type
3299 and document_id = p_document_id
3300 and id = rev.object_id);
3301
3302
3303 update okc_Review_upld_terms rev_terms
3304 set action = 'ADDED',
3305 article_id = null,
3306 article_version_id = null,
3307 object_id = null
3308 where rev_terms.review_upld_terms_id > (select min(review_upld_terms_id) from okc_Review_upld_terms
3309 where object_id = rev_terms.object_id
3310 and object_type = 'ARTICLE'
3311 and document_Type = p_document_type and document_id = p_document_id
3312 )
3313 and document_Type = p_document_type and document_id = p_document_id
3314 and object_type = 'ARTICLE' ;
3315
3316
3317 update okc_Review_upld_terms rev_terms
3318 set action = 'ADDED',
3319 article_id = null,
3320 article_version_id = null,
3321 object_id = null
3322 where rev_terms.review_upld_terms_id > (select min(review_upld_terms_id) from okc_Review_upld_terms
3323 where object_id = rev_terms.object_id
3324 and object_type = 'SECTION'
3325 and document_Type = p_document_type and document_id = p_document_id)
3326 and document_Type = p_document_type and document_id = p_document_id
3327 and object_type = 'SECTION';
3328
3329
3330
3331 UPDATE OKC_REVIEW_UPLD_TERMS
3332 SET ACTION='ADDED'
3333 WHERE OBJECT_ID IS NULL
3334 AND DOCUMENT_ID = P_DOCUMENT_ID
3335 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3336 AND ACTION IS NULL
3337 AND object_type IN ('ARTICLE','SECTION');
3338 --AND NEW_PARENT_ID IS NOT NULL;
3339
3340 UPDATE OKC_REVIEW_UPLD_TERMS UT
3341 SET ACTION='ADDED'
3342 WHERE ACTION IS NULL
3343 AND DOCUMENT_ID = P_DOCUMENT_ID
3344 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3345 AND NEW_PARENT_ID IS NOT NULL
3346 AND ((OBJECT_TYPE = 'ARTICLE'
3347 AND ( NOT EXISTS (SELECT 1
3348 FROM OKC_K_ARTICLES_B A
3349 WHERE A.ID = UT.OBJECT_ID
3350 AND A.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3351 AND A.DOCUMENT_ID = UT.DOCUMENT_ID) OR
3352 EXISTS (SELECT 1
3353 FROM OKC_K_ARTICLES_B A1
3354 WHERE A1.ID = UT.OBJECT_ID
3355 AND A1.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3356 AND A1.DOCUMENT_ID = UT.DOCUMENT_ID
3357 AND NVL(A1.AMENDMENT_OPERATION_CODE,'ZZZ') = 'DELETED') )
3358 ) OR
3359 (OBJECT_TYPE = 'SECTION'
3360 AND (NOT EXISTS (SELECT 1
3361 FROM OKC_SECTIONS_B S
3362 WHERE S.ID = UT.OBJECT_ID
3363 AND S.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3364 AND S.DOCUMENT_ID = UT.DOCUMENT_ID )
3365 OR
3366 EXISTS (SELECT 1
3367 FROM OKC_SECTIONS_B S1
3368 WHERE S1.ID = UT.OBJECT_ID
3369 AND S1.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3370 AND S1.DOCUMENT_ID = UT.DOCUMENT_ID
3371 AND NVL(S1.AMENDMENT_OPERATION_CODE,'ZZZ') = 'DELETED' ))
3372 ));
3373
3374 INSERT INTO OKC_REVIEW_UPLD_TERMS(
3375 REVIEW_UPLD_TERMS_ID,
3376 DOCUMENT_ID,
3377 DOCUMENT_TYPE,
3378 OBJECT_ID,
3379 OBJECT_TYPE,
3380 OBJECT_TITLE,
3381 OBJECT_TEXT,
3382 PARENT_OBJECT_TYPE,
3383 PARENT_ID,
3384 ARTICLE_ID,
3385 ARTICLE_VERSION_ID,
3386 OBJECT_VERSION_NUMBER,
3387 LABEL,
3388 DISPLAY_SEQ,
3389 ACTION,
3390 ERROR_MESSAGE_COUNT,
3391 WARNING_MESSAGE_COUNT,
3392 NEW_PARENT_ID,
3393 LAST_UPDATE_LOGIN,
3394 CREATED_BY,
3395 CREATION_DATE,
3396 LAST_UPDATED_BY,
3397 LAST_UPDATE_DATE)
3398 (SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL,
3399 KART.DOCUMENT_ID,
3400 KART.DOCUMENT_TYPE,
3401 KART.ID,
3402 'ARTICLE',
3403 nvl(VER.DISPLAY_NAME,ART.ARTICLE_TITLE),
3404 -- Fix for bug# 5223552. Fix for inserting Clause/Reference Text based on IBR flag in review tbl based for 'Deleted' Clause
3405 decode(ver.insert_by_reference,
3406 'Y', ver.reference_text,
3407 VER.ARTICLE_TEXT),
3408 'SECTION',
3409 KART.SCN_ID,
3410 KART.SAV_SAE_ID,
3411 KART.ARTICLE_VERSION_ID,
3412 1,
3413 KART.LABEL,
3414 KART.DISPLAY_SEQUENCE,
3415 'DELETED',
3416 NULL,
3417 NULL,
3418 (SELECT REVIEW_UPLD_TERMS_ID
3419 FROM OKC_REVIEW_UPLD_TERMS PARENT
3420 WHERE PARENT.OBJECT_ID = KART.SCN_ID
3421 AND PARENT.DOCUMENT_TYPE = p_document_type
3422 AND PARENT.DOCUMENT_ID = p_document_id),
3423 -- KART.SCN_ID,
3424 FND_GLOBAL.LOGIN_ID,
3425 FND_GLOBAL.USER_ID,
3426 SYSDATE,
3427 FND_GLOBAL.USER_ID,
3428 SYSDATE
3429 FROM OKC_K_ARTICLES_B KART,
3430 OKC_ARTICLES_ALL ART,
3431 OKC_ARTICLE_VERSIONS VER
3432 WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
3433 AND ART.ARTICLE_ID = VER.ARTICLE_ID
3434 AND KART.ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
3435 AND KART.DOCUMENT_ID = P_DOCUMENT_ID
3436 AND KART.DOCUMENT_TYPE = P_DOCUMENT_TYPE
3437 AND NOT EXISTS (SELECT 1
3438 FROM OKC_REVIEW_UPLD_TERMS UT
3439 WHERE UT.OBJECT_ID = KART.ID
3440 AND UT.OBJECT_TYPE = 'ARTICLE'
3441 AND UT.DOCUMENT_ID = P_DOCUMENT_ID
3442 AND UT.DOCUMENT_TYPE = P_DOCUMENT_TYPE));
3443
3444
3445 INSERT INTO OKC_REVIEW_UPLD_TERMS(
3446 REVIEW_UPLD_TERMS_ID,
3447 DOCUMENT_ID,
3448 DOCUMENT_TYPE,
3449 OBJECT_ID,
3450 OBJECT_TYPE,
3451 OBJECT_TITLE,
3452 OBJECT_TEXT,
3453 PARENT_OBJECT_TYPE,
3454 PARENT_ID,
3455 ARTICLE_ID,
3456 ARTICLE_VERSION_ID,
3457 OBJECT_VERSION_NUMBER,
3458 LABEL,
3459 DISPLAY_SEQ,
3460 ACTION,
3461 ERROR_MESSAGE_COUNT,
3462 WARNING_MESSAGE_COUNT,
3463 NEW_PARENT_ID,
3464 LAST_UPDATE_LOGIN,
3465 CREATED_BY,
3466 CREATION_DATE,
3467 LAST_UPDATED_BY,
3468 LAST_UPDATE_DATE)
3469 (SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL,
3470 SCN.DOCUMENT_ID,
3471 SCN.DOCUMENT_TYPE,
3472 SCN.ID,
3473 'SECTION',
3474 SCN.HEADING,
3475 NULL,
3476 DECODE(SCN.SCN_ID,NULL,p_document_type,'SECTION'),
3477 SCN.SCN_ID,
3478 NULL,
3479 NULL,
3480 1,
3481 SCN.LABEL,
3482 SCN.SECTION_SEQUENCE,
3483 'DELETED',
3484 NULL,
3485 NULL,
3486 -- SCN.SCN_ID,
3487 DECODE(SCN.SCN_ID, NULL,l_rev_id_for_doc,
3488 p_document_id, l_rev_id_for_doc,
3489 (SELECT REVIEW_UPLD_TERMS_ID
3490 FROM OKC_REVIEW_UPLD_TERMS PARENT
3491 WHERE PARENT.OBJECT_ID = SCN.SCN_ID
3492 AND PARENT.DOCUMENT_TYPE = p_document_type
3493 AND PARENT.DOCUMENT_ID = p_document_id)
3494 ),
3495 FND_GLOBAL.LOGIN_ID,
3496 FND_GLOBAL.USER_ID,
3497 SYSDATE,
3498 FND_GLOBAL.USER_ID,
3499 SYSDATE
3500 FROM OKC_SECTIONS_B SCN
3501 WHERE SCN.DOCUMENT_ID = P_DOCUMENT_ID
3502 AND SCN.DOCUMENT_TYPE = P_DOCUMENT_TYPE
3503 AND NOT EXISTS (SELECT 1
3504 FROM OKC_REVIEW_UPLD_TERMS UT
3505 WHERE UT.OBJECT_ID = SCN.ID
3506 AND UT.DOCUMENT_TYPE = p_document_type
3507 AND UT.DOCUMENT_ID = p_document_id
3508 AND UT.OBJECT_TYPE = 'SECTION'));
3509
3510
3511 for del_csr in unresolved_del_rec loop
3512 UPDATE OKC_REVIEW_UPLD_TERMS REV
3513 SET NEW_PARENT_ID = (SELECT REVIEW_UPLD_TERMS_ID
3514 FROM OKC_REVIEW_UPLD_TERMS PARENT
3515 WHERE PARENT.OBJECT_ID = REV.PARENT_ID
3516 AND REV.REVIEW_UPLD_TERMS_id = del_csr.REVIEW_UPLD_TERMS_id
3517 AND PARENT.DOCUMENT_TYPE = p_document_type
3518 AND PARENT.document_id = p_document_id
3519 )
3520 WHERE
3521 REV.REVIEW_UPLD_TERMS_id = del_csr.REVIEW_UPLD_TERMS_id;
3522 end loop;
3523
3524
3525 UPDATE OKC_REVIEW_UPLD_TERMS UT
3526 SET ACTION='UPDATED',
3527 DISPLAY_SEQ = (select KART.display_sequence
3528 FROM OKC_K_ARTICLES_B KART
3529 WHERE KART.ID = UT.OBJECT_ID)
3530 WHERE ACTION IS NULL
3531 AND DOCUMENT_ID = P_DOCUMENT_ID
3532 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3533 AND OBJECT_TYPE = 'ARTICLE'
3534 AND EXISTS
3535 (
3536 SELECT 1
3537 FROM OKC_K_ARTICLES_B KART,
3538 OKC_ARTICLES_ALL ART,
3539 OKC_ARTICLE_VERSIONS VER
3540 WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
3541 AND ART.ARTICLE_ID = VER.ARTICLE_ID
3542 AND KART.ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
3543 AND KART.ID = UT.OBJECT_ID
3544 AND KART.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3545 AND KART.DOCUMENT_ID = UT.DOCUMENT_ID
3546
3547 AND (
3548 (NVL(VER.DISPLAY_NAME,ART.ARTICLE_TITLE) <> to_char(UT.OBJECT_TITLE)) OR
3549 (NVL(ver.insert_by_reference,'N') = 'Y' AND
3550 NVL(dbms_lob.compare(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ut.object_text,'&NBSP;| ',' '),
3551 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3552 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>|
3553 <STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3554 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3555 |<HR>|</HR>|<hr>|</hr>|<br>|</br>|<BR>|</BR>|<br/>|<BR/>|([[:cntrl:]])',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">|<divalign="left">|<Palign="left">','') ,
3556 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ver.reference_text,'&NBSP;| ',' '),
3557 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3558 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>
3559 |<STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3560 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3561 |<HR>|</HR>|<hr>|</hr>|<br>|</br>|<BR>|</BR>|<br/>|<BR/>|([[:cntrl:]])',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">|<Palign="left">|<ULtype="disc">||<ultype="disc">','')),-1) <> 0) OR
3562 (NVL(ver.insert_by_reference,'N') <> 'Y' AND
3563 NVL(dbms_lob.compare(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ut.object_text,'&NBSP;| ',' '),
3564 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3565 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>|
3566 <STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3567 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3568 |<HR>|</HR>|<hr>|</hr>|<br>|</br>|<BR>|</BR>|<br/>|<BR/>|([[:cntrl:]])',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">|<divalign="left">|<Palign="left">','') ,
3569 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ver.article_text,'&NBSP;| ',' '),
3570 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3571 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>
3572 |<STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3573 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3574 |<HR>|</HR>|<hr>|</hr>|<br>|</br>|<BR>|</BR>|<br/>|<BR/>|([[:cntrl:]])',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">|<Palign="left">|<ULtype="disc">||<ultype="disc">','')),-1) <> 0)));
3575
3576 UPDATE OKC_REVIEW_UPLD_TERMS UT
3577 SET ACTION='UPDATED',
3578 DISPLAY_SEQ = (SELECT SECTION_SEQUENCE FROM OKC_SECTIONS_B SCN
3579 WHERE SCN.ID = UT.OBJECT_ID
3580 AND to_char(UT.OBJECT_TITLE) <> SCN.HEADING)
3581 WHERE ACTION IS NULL
3582 AND DOCUMENT_ID = P_DOCUMENT_ID
3583 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3584 AND OBJECT_TYPE = 'SECTION'
3585 AND EXISTS (SELECT 1
3586 FROM OKC_SECTIONS_B SCN
3587 WHERE SCN.ID = UT.OBJECT_ID
3588 AND SCN.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3589 AND SCN.DOCUMENT_ID = UT.DOCUMENT_ID
3590
3591 AND to_char(UT.OBJECT_TITLE) <> SCN.HEADING);
3592
3593 UPDATE OKC_REVIEW_UPLD_TERMS UT
3594 SET NON_STANDARD_FLAG = 'Y'
3595 WHERE DOCUMENT_ID = P_DOCUMENT_ID
3596 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3597 AND OBJECT_TYPE = 'ARTICLE'
3598 AND EXISTS (SELECT 1
3599 FROM OKC_K_ARTICLES_B KART,
3600 OKC_ARTICLES_ALL ART
3601 WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
3602 AND KART.ID = UT.OBJECT_ID
3603 AND KART.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3604 AND KART.DOCUMENT_ID = UT.DOCUMENT_ID
3605
3606 AND ART.STANDARD_YN = 'N');
3607
3608 open clauses_without_parent_exist;
3609 fetch clauses_without_parent_exist into l_clauses_no_parent_exist;
3610 if (clauses_without_parent_exist%FOUND) THEN
3611 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3612 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'120: Creating Unassgined Section ');
3613 END IF;
3614 open unassigned_section_exists;
3615 fetch unassigned_section_exists into l_unassigned_scn_id;
3616 if(unassigned_section_exists%NOTFOUND)THEN
3617 create_unassigned_section(p_api_version => 1,
3618 p_commit => FND_API.G_FALSE,
3619 p_document_type => p_document_type,
3620 p_document_id => p_document_id,
3621 p_new_parent_id => l_rev_id_for_doc,
3622 x_scn_id => l_unassigned_scn_id,
3623 x_return_status => x_return_status,
3624 x_msg_count => x_msg_count,
3625 x_msg_data => x_msg_data
3626 );
3627 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3628 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'130: l_unassigned_scn_id : '||l_unassigned_scn_id);
3629 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'130: Cannot Create Unassgined Section : '||x_msg_data||' Status '||x_return_status);
3630 END IF;
3631
3632 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3634 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3635 RAISE FND_API.G_EXC_ERROR ;
3636 END IF;
3637 end if;
3638 close unassigned_section_exists;
3639 end if;
3640 close clauses_without_parent_exist;
3641 for clause_no_parent_csr in clauses_without_parent_id loop
3642 update okc_REVIEW_UPLD_TERMS
3643 set new_parent_id = l_unassigned_scn_id, action = 'ADDED'
3644 where REVIEW_UPLD_TERMS_id = clause_no_parent_csr.REVIEW_UPLD_TERMS_id;
3645 end loop;
3646
3647 for clauses_moved_no_section_csr in clauses_no_parent_id_and_moved loop
3648 update OKC_REVIEW_UPLD_TERMS
3649 set new_parent_id = l_unassigned_scn_id
3650 where REVIEW_UPLD_TERMS_id =clauses_moved_no_section_csr.REVIEW_UPLD_TERMS_id;
3651 end loop;
3652
3653
3654 l_message_name := 'OKC_REVIEW_VAR_VAL_CHG';
3655 for var_csr in variable_values_changed loop
3656 update OKC_REVIEW_VAR_VALUES
3657 set changed = 'Y'
3658 where REVIEW_UPLD_TERMS_id = var_csr.REVIEW_UPLD_TERMS_id;
3659
3660 if(var_csr.variable_type = 'U') then
3661 if(var_csr.variable_value_id is null) then
3662 l_message_name := 'OKC_REVIEW_VAR_VAL_KNW_CHG';
3663 else
3664 l_message_name := 'OKC_REVIEW_VAR_MAY_CHG';
3665 end if;
3666 else
3667 l_message_name := 'OKC_REVIEW_VAR_MAY_CHG';
3668 end if;
3669
3670 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3671 -- and variable_code
3672 insert into okc_review_messages (
3673 review_messages_id,
3674 REVIEW_UPLD_TERMS_id,
3675 error_severity,
3676 message_name,
3677 object_version_number,
3678 variable_code
3679 )
3680 (select okc_review_messages_s1.nextval review_messages_id,
3681 var_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3682 'W' error_severity,l_message_name ,1 object_version_number, var_csr.variable_code from dual
3683 where not exists (select 1 from okc_review_messages where review_upld_terms_id = var_csr.review_upld_terms_id
3684 and variable_code = var_csr.variable_code and message_name = l_message_name));
3685
3686 end loop;
3687
3688 l_message_name := 'OKC_REVIEW_VAR_VAL_CHG';
3689 for valid_var_added_csr in valid_variable_added(l_intent) loop
3690 -- Fix for bug# 5229387. Clauses should be marked as 'UPDATED' only if action is null
3691 update okc_review_upld_terms
3692 set action='UPDATED'
3693 where review_upld_terms_id = valid_var_added_csr.review_upld_terms_id and action is null;
3694
3695 update OKC_REVIEW_VAR_VALUES
3696 set changed = 'A'
3697 where REVIEW_UPLD_TERMS_id = valid_var_added_csr.REVIEW_UPLD_TERMS_id;
3698
3699 if(valid_var_added_csr.modified='Y') then
3700 l_message_name := 'OKC_REVIEW_VAR_ADDED';
3701 else
3702 l_message_name := 'OKC_REVIEW_VAR_ADD_NO_VAL';
3703 end if;
3704
3705 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3706 -- and variable_code
3707 insert into okc_review_messages (
3708 review_messages_id,
3709 REVIEW_UPLD_TERMS_id,
3710 error_severity,
3711 message_name,
3712 object_version_number,
3713 variable_code
3714 )
3715 (select okc_review_messages_s1.nextval review_messages_id,
3716 valid_var_added_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3717 'W' error_severity, l_message_name ,1 object_version_number, valid_var_added_csr.variable_code from dual
3718 where not exists (select 1 from okc_review_messages where review_upld_terms_id = valid_var_added_csr.review_upld_terms_id
3719 and variable_code = valid_var_added_csr.variable_code and message_name = l_message_name));
3720 end loop;
3721
3722 for valid_var_new_clause_csr in valid_var_new_clause(l_intent) loop
3723
3724 update OKC_REVIEW_VAR_VALUES
3725 set changed = 'A'
3726 where REVIEW_UPLD_TERMS_id = valid_var_new_clause_csr.REVIEW_UPLD_TERMS_id;
3727
3728 if(valid_var_new_clause_csr.modified='Y') then
3729 l_message_name := 'OKC_REVIEW_VAR_ADDED';
3730 else
3731 l_message_name := 'OKC_REVIEW_VAR_ADD_NO_VAL';
3732 end if;
3733
3734 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3735 -- and variable_code
3736 insert into okc_review_messages (
3737 review_messages_id,
3738 REVIEW_UPLD_TERMS_id,
3739 error_severity,
3740 message_name,
3741 object_version_number,
3742 variable_code
3743 )
3744 (select okc_review_messages_s1.nextval review_messages_id,
3745 valid_var_new_clause_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3746 'W' error_severity, l_message_name ,1 object_version_number, valid_var_new_clause_csr.variable_code from dual
3747 where not exists (select 1 from okc_review_messages where review_upld_terms_id = valid_var_new_clause_csr.review_upld_terms_id
3748 and variable_code = valid_var_new_clause_csr.variable_code and message_name = l_message_name));
3749 end loop;
3750
3751 for valid_new_var_ibr_csr in valid_new_var_ibr(l_intent) loop
3752
3753 update OKC_REVIEW_VAR_VALUES
3754 set changed = 'A'
3755 where REVIEW_UPLD_TERMS_id = valid_new_var_ibr_csr.REVIEW_UPLD_TERMS_id;
3756
3757 if(valid_new_var_ibr_csr.modified='Y') then
3758 l_message_name := 'OKC_REVIEW_VAR_ADDED';
3759 else
3760 l_message_name := 'OKC_REVIEW_VAR_ADD_NO_VAL';
3761 end if;
3762
3763 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3764 -- and variable_code
3765 insert into okc_review_messages (
3766 review_messages_id,
3767 REVIEW_UPLD_TERMS_id,
3768 error_severity,
3769 message_name,
3770 object_version_number,
3771 variable_code
3772 )
3773 (select okc_review_messages_s1.nextval review_messages_id,
3774 valid_new_var_ibr_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3775 'W' error_severity, l_message_name ,1 object_version_number, valid_new_var_ibr_csr.variable_code from dual
3776 where not exists (select 1 from okc_review_messages where review_upld_terms_id = valid_new_var_ibr_csr.review_upld_terms_id
3777 and variable_code = valid_new_var_ibr_csr.variable_code and message_name = l_message_name));
3778 end loop;
3779
3780 for removed_var_csr in variables_removed loop
3781 -- Fix for bug# 5229387. Clauses should be marked as 'UPDATED' only if action is null
3782 update okc_review_upld_terms
3783 set action='UPDATED'
3784 where review_upld_terms_id = removed_var_csr.review_upld_terms_id and action is null;
3785
3786 insert into okc_review_var_values(
3787 review_var_values_id,
3788 review_upld_terms_id,
3789 variable_name,
3790 language,
3791 variable_code,
3792 object_version_number,
3793 changed)
3794 (select okc_review_var_values_s1.nextval,
3795 removed_var_csr.review_upld_terms_id,
3796 removed_var_csr.variable_name,
3797 removed_var_csr.language,
3798 removed_var_csr.variable_code,
3799 1,
3800 'D' from dual);
3801
3802 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3803 -- and variable_code
3804 insert into okc_review_messages (
3805 review_messages_id,
3806 REVIEW_UPLD_TERMS_id,
3807 error_severity,
3808 message_name,
3809 object_version_number,
3810 variable_code
3811 )
3812 (select okc_review_messages_s1.nextval review_messages_id,
3813 removed_var_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3814 'W' error_severity,'OKC_REVIEW_VAR_REMOVED' message_name ,1 object_version_number, removed_var_csr.variable_code from dual
3815 where not exists (select 1 from okc_review_messages where review_upld_terms_id = removed_var_csr.review_upld_terms_id
3816 and variable_code = removed_var_csr.variable_code and message_name = 'OKC_REVIEW_VAR_REMOVED'));
3817 end loop;
3818
3819 l_message_name := 'OKC_REVIEW_INVALID_VARIABLE';
3820 for invalid_var_added_csr in invalid_variable_added(l_intent) loop
3821 -- Fix for bug# 5229387. Clauses should be marked as 'UPDATED' only if action is null
3822 update okc_review_upld_terms
3823 set action='UPDATED'
3824 where review_upld_terms_id = invalid_var_added_csr.review_upld_terms_id and action is null;
3825
3826 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3827 -- and variable_code
3828 insert into okc_review_messages (
3829 review_messages_id,
3830 REVIEW_UPLD_TERMS_id,
3831 error_severity,
3832 message_name,
3833 object_version_number,
3834 variable_code
3835 )
3836 (select okc_review_messages_s1.nextval review_messages_id,
3837 invalid_var_added_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3838 'W' error_severity, l_message_name ,1 object_version_number, invalid_var_added_csr.variable_code from dual
3839 where not exists (select 1 from okc_review_messages where review_upld_terms_id = invalid_var_added_csr.review_upld_terms_id
3840 and variable_code = invalid_var_added_csr.variable_code and message_name = l_message_name));
3841 end loop;
3842
3843 -- Delete the duplicate variable rows for the given document_type, document_id
3844 delete from okc_review_var_values revvar
3845 where revvar.rowid > (select min(rowid) from okc_review_var_values
3846 where review_upld_terms_id = revvar.review_upld_terms_id
3847 and revvar.variable_code = variable_code)
3848 and exists(select 1 from okc_Review_upld_terms rev
3849 where revvar.review_upld_terms_id = rev.review_upld_terms_id
3850 and rev.document_type = p_document_type
3851 and rev.document_id = p_document_id);
3852
3853 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3854 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5000: Begin: Logging the ones that were determined as UPDATED');
3855 for upd_csr in get_updated_articles_csr loop
3856 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5010: for review_upld_terms_id=' || upd_csr.review_upld_terms_id );
3857 end loop;
3858 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6000: End: Logging the ones that were determined as UPDATED');
3859 END IF;
3860
3861
3862
3863 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3864 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: After delete review terms');
3865 END IF;
3866 l_clause_title := OKC_TERMS_UTIL_PVT.Get_Message('OKC','OKC_TERMS_DUMMY_CLAUSE');
3867 l_section_title := OKC_TERMS_UTIL_PVT.Get_Message('OKC','OKC_TERMS_DUMMY_SECTION');
3868 l_clause_counter:= 1;
3869 l_section_counter := 1;
3870 for emp_csr in empty_title_csr loop
3871 if(emp_csr.object_type = 'ARTICLE') then
3872 update okc_REVIEW_UPLD_TERMS
3873 set object_title = l_clause_title || l_clause_counter
3874 where REVIEW_UPLD_TERMS_id = emp_csr.REVIEW_UPLD_TERMS_id;
3875 l_clause_counter := l_clause_counter + 1;
3876 end if;
3877 if(emp_csr.object_type = 'SECTION') then
3878 update okc_REVIEW_UPLD_TERMS
3879 set object_title = l_section_title || l_section_counter
3880 where REVIEW_UPLD_TERMS_id = emp_csr.REVIEW_UPLD_TERMS_id;
3881 l_section_counter := l_section_counter + 1;
3882 end if;
3883 end loop;
3884
3885
3886 update okc_review_upld_terms rev
3887 set action = 'DELETED',
3888 object_title = (select nvl(ver.display_name,art. article_title)
3889 from OKC_K_ARTICLES_B kart, OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
3890 where kart.id = rev.object_id
3891 and kart.sav_sae_id = art.article_id
3892 and kart.article_version_id = ver.article_version_id
3893 and art.article_id = ver.article_id)
3894 ,object_text = (select ver.article_text from OKC_K_ARTICLES_B kart, OKC_ARTICLE_VERSIONS VER
3895 where kart.id = rev.object_id
3896 and kart.article_Version_id = ver.article_version_id)
3897
3898 where document_type = p_document_type
3899 and document_id = p_document_id
3900 and object_type = 'ARTICLE'
3901 and action = 'UPDATED'
3902 and (DBMS_LOB.getlength(object_text)=0 OR object_text is null);
3903
3904 for ibr_csr in is_article_ibr(l_user_access) loop
3905 insert into okc_review_messages (
3906 review_messages_id,
3907 REVIEW_UPLD_TERMS_id,
3908 error_severity,
3909 message_name,
3910 object_version_number
3911
3912 )
3913 (select okc_review_messages_s1.nextval review_messages_id,
3914 ibr_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3915 'W' error_severity,'OKC_ARTICLE_UPDT_IBR' message_name ,1 object_version_number from dual);
3916 end loop;
3917
3918 for mandatory_csr in is_article_mandatory(l_user_access) loop
3919 insert into okc_review_messages (
3920 review_messages_id,
3921 REVIEW_UPLD_TERMS_id,
3922 error_severity,
3923 message_name,
3924 object_version_number
3925
3926 )
3927 (select okc_review_messages_s1.nextval review_messages_id,
3928 mandatory_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3929 'W' error_severity,'OKC_ARTICLE_IS_MANDATORY' message_name ,1 object_version_number from dual);
3930 end loop;
3931
3932 for lock_csr in is_article_text_locked(l_user_access) loop
3933 insert into okc_review_messages (
3934 review_messages_id,
3935 REVIEW_UPLD_TERMS_id,
3936 error_severity,
3937 message_name,
3938 object_version_number
3939
3940 )
3941 (select okc_review_messages_s1.nextval review_messages_id,
3942 lock_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3943 'W' error_severity,'OKC_ARTICLE_UPDT_LOCK' message_name ,1 object_version_number from dual);
3944 end loop;
3945
3946 l_message_name := 'OKC_UPLOAD_CLAUSE_TITLE_LONG';
3947 for title_csr in check_sec_clause_title loop
3948 if(title_csr.object_Type='SECTION') then
3949 l_message_name := 'OKC_UPLOAD_SECTION_NAME_LONG';
3950 end if;
3951 if(title_csr.object_type= 'ARTICLE') then
3952 l_message_name := 'OKC_UPLOAD_CLAUSE_TITLE_LONG';
3953 end if;
3954 insert into okc_review_messages (
3955 review_messages_id,
3956 REVIEW_UPLD_TERMS_id,
3957 error_severity,
3958 message_name,
3959 object_version_number
3960
3961 )
3962 (select okc_review_messages_s1.nextval review_messages_id,
3963 title_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3964 'W' error_severity, l_message_name,1 object_version_number from dual);
3965
3966 end loop;
3967 for upd_csr in update_err_warn_csr loop
3968 if (upd_csr.error_severity = 'E') then
3969 update okc_review_upld_terms
3970 set error_message_count = upd_csr.err_warn_count
3971 where review_upld_terms_id = upd_csr.review_upld_terms_id;
3972 end if;
3973 if (upd_csr.error_severity = 'W') then
3974 update okc_review_upld_terms
3975 set warning_message_count = upd_csr.err_warn_count
3976 where review_upld_terms_id = upd_csr.review_upld_terms_id;
3977 end if;
3978 end loop;
3979 OKC_TEMPLATE_USAGES_GRP.update_template_usages(
3980 p_api_version => l_api_version,
3981 p_init_msg_list => p_init_msg_list ,
3982 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3983 p_commit => FND_API.G_FALSE,
3984 x_return_status => x_return_status,
3985 x_msg_count => x_msg_count,
3986 x_msg_data => x_msg_data,
3987 p_document_type => p_document_type,
3988 p_document_id => p_document_id,
3989 p_lock_terms_flag => 'Y',
3990 p_locked_by_user_id => FND_GLOBAL.user_id);
3991
3992 open current_num_scheme;
3993 fetch current_num_scheme into l_doc_num_scheme;
3994 close current_num_scheme;
3995
3996 curr_disp_seqs.DELETE;
3997 del_new_parents.DELETE;
3998 del_disp_seqs.DELETE;
3999
4000 FOR rec in parent_ids_csr LOOP
4001 curr_disp_seqs(rec.review_upld_terms_id) := 0;
4002 END LOOP;
4003
4004 IF curr_disp_seqs.COUNT > 0 THEN
4005 curr_disp_seqs(-999) := 0;
4006 END IF;
4007 OPEN deleted_terms_csr;
4008 FETCH deleted_terms_csr BULK COLLECT INTO
4009 del_new_parents,del_disp_seqs;
4010 CLOSE deleted_terms_csr;
4011
4012 l_sequence := 0;
4013 FOR disp_rec in terms_disp_csr LOOP
4014 IF disp_rec.object_type IN ('ARTICLE','SECTION') THEN
4015 l_sequence := curr_disp_seqs(NVL(disp_rec.new_parent_id,-999)) + 10;
4016
4017 IF del_new_parents.COUNT > 0 THEN
4018 FOR i in del_new_parents.FIRST .. del_new_parents.LAST LOOP
4019 IF (NVL(del_new_parents(i),-999) = NVL(disp_rec.new_parent_id,-999) AND
4020 del_disp_seqs(i) = l_sequence) THEN
4021 l_sequence := l_sequence + 10;
4022 EXIT;
4023 END IF;
4024 END LOOP;
4025 END IF;
4026
4027 UPDATE okc_review_upld_terms
4028 SET display_seq = l_sequence
4029 WHERE review_upld_terms_id = disp_rec.review_upld_terms_id;
4030
4031 curr_disp_seqs(NVL(disp_rec.new_parent_id,-999)) := l_sequence;
4032 END IF;
4033
4034 END LOOP;
4035
4036
4037 for upd_csr in update_err_warn_csr loop
4038 if (upd_csr.error_severity = 'E') then
4039 update okc_review_upld_terms
4040 set error_message_count = upd_csr.err_warn_count
4041 where review_upld_terms_id = upd_csr.review_upld_terms_id;
4042 end if;
4043 if (upd_csr.error_severity = 'W') then
4044 update okc_review_upld_terms
4045 set warning_message_count = upd_csr.err_warn_count
4046 where review_upld_terms_id = upd_csr.review_upld_terms_id;
4047 end if;
4048 end loop;
4049 if(l_doc_num_scheme is NOT NULL) then
4050 OKC_NUMBER_SCHEME_GRP.apply_num_scheme_4_review(
4051 p_api_version => p_api_version,
4052 p_init_msg_list => p_init_msg_list,
4053 x_return_status => x_return_status,
4054 x_msg_count => x_msg_count,
4055 x_msg_data => x_msg_data,
4056 p_commit => p_commit,
4057 p_validation_string => p_validation_string,
4058 p_doc_type => p_document_type,
4059 p_doc_id => p_document_id,
4060 p_num_scheme_id => l_doc_num_scheme);
4061 end if;
4062 -- Standard check of p_commit
4063 IF FND_API.To_Boolean( p_commit ) THEN
4064 COMMIT WORK;
4065 END IF;
4066
4067 EXCEPTION
4068 WHEN FND_API.G_EXC_ERROR THEN
4069 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4070 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Reject_Changes: OKC_API.G_EXCEPTION_ERROR Exception');
4071 END IF;
4072 ROLLBACK TO g_reject_changes;
4073 x_return_status := G_RET_STS_ERROR ;
4074 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
4075
4076 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4077 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4078 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Reject_Changes: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4079 END IF;
4080 ROLLBACK TO g_reject_Changes;
4081 x_return_status := G_RET_STS_UNEXP_ERROR ;
4082 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
4083
4084 WHEN OTHERS THEN
4085 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4086 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving reject_changes because of EXCEPTION: '||sqlerrm);
4087 END IF;
4088
4089 ROLLBACK TO g_reject_changes;
4090 x_return_status := G_RET_STS_UNEXP_ERROR ;
4091 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4092 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4093 END IF;
4094 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
4095 END Sync_Review_Tables;
4096
4097 /*
4098 -- PROCEDURE Create_Unassigned_Section
4099 -- creating un-assigned sections in a document in okc_REVIEW_UPLD_TERMS table
4100 */
4101 PROCEDURE Create_Unassigned_Section (
4102 p_api_version IN NUMBER,
4103 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4104 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4105
4106 x_return_status OUT NOCOPY VARCHAR2,
4107 x_msg_data OUT NOCOPY VARCHAR2,
4108 x_msg_count OUT NOCOPY NUMBER,
4109
4110 p_document_type IN VARCHAR2,
4111 p_document_id IN NUMBER,
4112 p_new_parent_id IN NUMBER,
4113
4114 x_scn_id OUT NOCOPY NUMBER
4115 ) IS
4116 l_api_version CONSTANT NUMBER := 1;
4117 l_api_name CONSTANT VARCHAR2(30) := 'Create_Unassigned_Section';
4118 l_meaning VARCHAR2(100);
4119 l_sequence NUMBER;
4120 l_scn_id NUMBER;
4121 l_temp_id NUMBER;
4122 Cursor l_get_max_seq_csr IS
4123 SELECT nvl(max(display_seq),0)+10
4124 FROM OKC_REVIEW_UPLD_TERMS
4125 WHERE DOCUMENT_TYPE= p_document_type
4126 AND DOCUMENT_ID = p_document_id
4127 AND PARENT_ID IS NULL or new_parent_id = p_document_id;
4128
4129 BEGIN
4130 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4131 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7500: Entered Create_Unassigned_Section');
4132 END IF;
4133 -- Standard Start of API savepoint
4134 SAVEPOINT g_Create_Unassigned_Section;
4135 -- Standard call to check for call compatibility.
4136 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4138 END IF;
4139 -- Initialize message list if p_init_msg_list is set to TRUE.
4140 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4141 FND_MSG_PUB.initialize;
4142 END IF;
4143 -- Initialize API return status to success
4144 x_return_status := G_RET_STS_SUCCESS;
4145
4146 --------------------------------------------
4147 -- Call Simple API of okc_sections_b with following input
4148 -- doc_type=p_doc_type, doc_id=p_doc_id, scn_code=G_UNASSIGNED_SECTION_CODE,
4149 -- heading = < get meaning of G_UNASSIGNED_SECTION_CODE by quering fnd_lookups>.
4150 -- Set x_scn_id to id returned by simpel API.
4151 --------------------------------------------
4152 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4153 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7600: Calling Simple API to Create a Section');
4154 END IF;
4155 --------------------------------------------
4156 l_meaning := Okc_Util.Decode_Lookup('OKC_ARTICLE_SECTION',G_UNASSIGNED_SECTION_CODE);
4157
4158 --Bug 3669528 Unassigned section should always come at the bottom, so use a 'high' value
4159 /*
4160 OPEN l_get_max_seq_csr;
4161 FETCH l_get_max_seq_csr INTO l_sequence;
4162 CLOSE l_get_max_seq_csr;
4163 */
4164 l_sequence:= 9999;
4165 x_return_status := Get_Seq_Id(
4166 p_REVIEW_UPLD_TERMS_id => l_temp_id,
4167 x_REVIEW_UPLD_TERMS_id => l_scn_id
4168 );
4169 --- If any errors happen abort API
4170 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4172 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4173 RAISE FND_API.G_EXC_ERROR;
4174 END IF;
4175 x_scn_id := l_scn_id;
4176 INSERT INTO OKC_REVIEW_UPLD_TERMS(
4177 REVIEW_UPLD_TERMS_ID,
4178 DOCUMENT_ID,
4179 DOCUMENT_TYPE,
4180 OBJECT_ID,
4181 OBJECT_TYPE,
4182 OBJECT_TITLE,
4183 OBJECT_TEXT,
4184 PARENT_OBJECT_TYPE,
4185 PARENT_ID,
4186 ARTICLE_ID,
4187 ARTICLE_VERSION_ID,
4188 OBJECT_VERSION_NUMBER,
4189 LABEL,
4190 DISPLAY_SEQ,
4191 ACTION,
4192 ERROR_MESSAGE_COUNT,
4193 WARNING_MESSAGE_COUNT,
4194 NEW_PARENT_ID,
4195 LAST_UPDATE_LOGIN,
4196 CREATED_BY,
4197 CREATION_DATE,
4198 LAST_UPDATED_BY,
4199 LAST_UPDATE_DATE)
4200 (SELECT l_scn_id,
4201 P_DOCUMENT_ID,
4202 P_DOCUMENT_TYPE,
4203 NULL,
4204 'SECTION',
4205 l_meaning,
4206 NULL,
4207 p_document_type,
4208 p_document_id,
4209 NULL,
4210 NULL,
4211 1,
4212 NULL,
4213 l_sequence,
4214 'ADDED',
4215 NULL,
4216 NULL,
4217 p_new_parent_id,
4218 FND_GLOBAL.LOGIN_ID,
4219 FND_GLOBAL.USER_ID,
4220 SYSDATE,
4221 FND_GLOBAL.USER_ID,
4222 SYSDATE
4223 FROM dual);
4224
4225 --------------------------------------------
4226 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4228 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4229 RAISE FND_API.G_EXC_ERROR ;
4230 END IF;
4231 --------------------------------------------
4232
4233 IF FND_API.To_Boolean( p_commit ) THEN
4234 COMMIT WORK;
4235 END IF;
4236 -- Standard call to get message count and if count is 1, get message info.
4237 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4238 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4239 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7700: Leaving Create_Unassigned_Section');
4240 END IF;
4241 EXCEPTION
4242 WHEN FND_API.G_EXC_ERROR THEN
4243 ROLLBACK TO g_Create_Unassigned_Section;
4244 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4245 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Create_Unassigned_Section : OKC_API.G_EXCEPTION_ERROR Exception');
4246 END IF;
4247
4248 IF l_get_max_seq_csr%ISOPEN THEN
4249 CLOSE l_get_max_seq_csr;
4250 END IF;
4251
4252 x_return_status := G_RET_STS_ERROR ;
4253 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4254
4255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4256 ROLLBACK TO g_Create_Unassigned_Section;
4257 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4258 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Create_Unassigned_Section : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4259 END IF;
4260
4261 IF l_get_max_seq_csr%ISOPEN THEN
4262 CLOSE l_get_max_seq_csr;
4263 END IF;
4264
4265 x_return_status := G_RET_STS_UNEXP_ERROR ;
4266 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4267
4268 WHEN OTHERS THEN
4269 ROLLBACK TO g_Create_Unassigned_Section;
4270 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4271 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Create_Unassigned_Section because of EXCEPTION: '||sqlerrm);
4272 END IF;
4273
4274 IF l_get_max_seq_csr%ISOPEN THEN
4275 CLOSE l_get_max_seq_csr;
4276 END IF;
4277
4278 x_return_status := G_RET_STS_UNEXP_ERROR ;
4279 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4280 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4281 END IF;
4282 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4283 END Create_Unassigned_Section ;
4284
4285
4286 END OKC_REVIEW_UPLD_TERMS_PVT;