[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.62.12020000.4 2012/11/20 10:54:08 skavutha 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 --2 way word sync with clause edit
1894 TYPE obj_text_wml_tab IS TABLE OF OKC_ARTICLE_VERSIONS.ARTICLE_TEXT_IN_WORD%TYPE INDEX BY BINARY_INTEGER;
1895
1896 rut_ids rut_id_tab;
1897 upld_levels upld_level_tab;
1898 obj_ids obj_id_tab;
1899 obj_types obj_type_tab;
1900 obj_texts obj_text_tab;
1901 pobj_types pobj_type_tab;
1902 pobj_ids pobj_id_tab;
1903 art_ids art_id_tab;
1904 art_vers art_ver_tab;
1905 ovns ovn_tab;
1906 labels label_tab;
1907 disp_seqs disp_seq_tab;
1908 actions action_tab;
1909 non_stds non_std_tab;
1910 mandatorys mandatory_tab;
1911 lock_texts lock_text_tab;
1912 new_parents new_parent_tab;
1913 obj_titles obj_title_tab;
1914 orut_ids orut_id_tab;
1915 curr_obj_ids curr_obj_id_tab;
1916 curr_disp_seqs curr_disp_seq_tab;
1917 --2 way word sync with clause edit
1918 obj_texts_wml obj_text_wml_tab;
1919
1920 CURSOR accepted_terms_csr IS
1921 SELECT review_upld_terms_id,
1922 level upload_level,
1923 object_id,
1924 object_type,
1925 object_text,
1926 parent_object_type,
1927 parent_id,
1928 article_id,
1929 article_version_id,
1930 object_version_number,
1931 label,
1932 display_seq,
1933 action,
1934 non_standard_flag,
1935 mandatory_flag,
1936 lock_text,
1937 new_parent_id,
1938 object_title,
1939 old_review_upld_terms_id
1940 FROM okc_review_upld_terms
1941 WHERE document_id = p_document_id
1942 AND document_type = p_document_type
1943 CONNECT BY PRIOR review_upld_terms_id = new_parent_id
1944 START WITH new_parent_id is null
1945 ORDER SIBLINGS BY review_upld_terms_id;
1946
1947 --2 way word sync with clause edit
1948
1949 CURSOR accepted_terms_with_wml_csr IS
1950 SELECT review_upld_terms_id,
1951 level upload_level,
1952 object_id,
1953 object_type,
1954 object_text,
1955 parent_object_type,
1956 parent_id,
1957 article_id,
1958 article_version_id,
1959 object_version_number,
1960 label,
1961 display_seq,
1962 action,
1963 non_standard_flag,
1964 mandatory_flag,
1965 lock_text,
1966 new_parent_id,
1967 object_title,
1968 old_review_upld_terms_id --,
1969 --OKC_WORD_DOWNLOAD_UPLOAD.UPLOAD_POST_PROCESSOR(p_document_id,p_document_type,object_id)
1970 --OKC_WORD_DOWNLOAD_UPLOAD.GET_LATEST_WMLBLOB(p_document_id,p_document_type, object_id)
1971 FROM okc_review_upld_terms
1972 WHERE document_id = p_document_id
1973 AND document_type = p_document_type
1974 CONNECT BY PRIOR review_upld_terms_id = new_parent_id
1975 START WITH new_parent_id is null
1976 ORDER SIBLINGS BY review_upld_terms_id;
1977
1978
1979 cursor current_num_scheme is
1980 select doc_numbering_scheme from okc_template_usages
1981 where document_type = p_document_type and
1982 document_id = p_document_id;
1983
1984 CURSOR is_article_ibr (p_review_upld_terms_id NUMBER) is
1985 SELECT 'Y'
1986 from okc_article_versions av, okc_review_upld_terms ar
1987 where av.article_version_id = ar.article_version_id
1988 and av.insert_by_reference = 'Y'
1989 and ar.review_upld_terms_id= p_review_upld_terms_id;
1990
1991 CURSOR is_article_mandatory (p_review_upld_terms_id NUMBER) is
1992 SELECT 'Y'
1993 from okc_k_articles_b akb, okc_review_upld_terms ar
1994 where akb.id = ar.object_id
1995 and akb.mandatory_yn = 'Y'
1996 and ar.review_upld_terms_id= p_review_upld_terms_id;
1997
1998 CURSOR is_article_text_locked (p_review_upld_terms_id NUMBER) is
1999 SELECT 'Y'
2000 from okc_article_versions av, okc_review_upld_terms ar
2001 where av.article_version_id = ar.article_version_id
2002 and av.lock_text = 'Y'
2003 and ar.review_upld_terms_id= p_review_upld_terms_id;
2004
2005 cursor get_clause_type_csr(p_review_upld_terms_id NUMBER) is
2006 SELECT
2007 aa.article_type
2008 from okc_articles_all aa, okc_review_upld_terms rev
2009 where
2010 rev.article_id = aa.article_id
2011 and rev.review_upld_terms_id = p_review_upld_terms_id;
2012
2013 is_ibr VARCHAR2(1) := 'N';
2014 is_lock_text VARCHAR2(1) := 'N';
2015 is_mandatory_text VARCHAR2(1) := 'N';
2016 l_sec_with_mandatory_clause VARCHAR2(1) := 'N';
2017 l_display_sequence NUMBER := 0;
2018 l_cat_id NUMBER;
2019 l_article_version_id NUMBER;
2020 l_api_name CONSTANT VARCHAR2(30) := 'Accept_Changes';
2021 l_api_version CONSTANT NUMBER := 1;
2022 l_ref_id NUMBER;
2023 l_ref_type VARCHAR2(30);
2024 l_scn_id NUMBER;
2025 l_user_access VARCHAR2(30);
2026 l_parent_id NUMBER;
2027 l_doc_num_scheme NUMBER;
2028 l_parent_object_title OKC_SECTIONS_B.HEADING%TYPE;
2029 l_parent_object_id OKC_REVIEW_UPLD_TERMS.OBJECT_ID%TYPE;
2030 l_article_type OKC_ARTICLES_ALL.ARTICLE_TYPE%TYPE;
2031 l_root_obj_type OKC_REVIEW_UPLD_TERMS.OBJECT_TYPE%TYPE;
2032 l_scn_code OKC_SECTIONS_B.SCN_CODE%TYPE;
2033 -- 2 way word sync with clause edit
2034 l_prof_value VARCHAR2(1);
2035 l_wml_i NUMBER;
2036
2037 BEGIN
2038
2039 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2040 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10000: Entering Accept_Changes');
2041 END IF;
2042
2043 -- Standard Start of API savepoint
2044 SAVEPOINT g_accept_changes;
2045
2046 -- Standard call to check for call compatibility.
2047 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2049 END IF;
2050
2051 -- Initialize message list if p_init_msg_list is set to TRUE.
2052 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2053 FND_MSG_PUB.initialize;
2054 END IF;
2055
2056 -- Initialize API return status to success
2057 x_return_status := FND_API.G_RET_STS_SUCCESS;
2058
2059 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_STD','N') THEN
2060 l_user_access := 'STD_AUTHOR';
2061 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_NON_STD','N') THEN
2062 l_user_access := 'NON_STD_AUTHOR';
2063 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_SUPERUSER','N') THEN
2064 l_user_access := 'SUPER_USER';
2065 END IF;
2066 END IF;
2067 ELSE
2068 l_user_access := 'NO_ACCESS';
2069 END IF;
2070
2071 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2072 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10200: After access test, l_user_access='||l_user_access);
2073 END IF;
2074
2075 IF l_user_access NOT IN ('NON_STD_AUTHOR','SUPER_USER') THEN
2076 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2077 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10300: User has no privileges to accept changes');
2078 END IF;
2079
2080 END IF;
2081 --2 way word sync with clause edit begins
2082 l_prof_value := OKC_WORD_DOWNLOAD_UPLOAD.GET_WORD_SYNC_PROFILE;
2083 IF l_prof_value = 'Y' THEN
2084 OPEN accepted_terms_with_wml_csr;
2085 UPDATE okc_word_sync_t
2086 SET cat_id = NULL
2087 WHERE doc_id = p_document_id
2088 AND doc_type = p_document_type
2089 AND action = 'ADDEDASSIGNED';
2090 FETCH accepted_terms_with_wml_csr BULK COLLECT INTO
2091 rut_ids,
2092 upld_levels,
2093 obj_ids,
2094 obj_types,
2095 obj_texts,
2096 pobj_types,
2097 pobj_ids,
2098 art_ids,
2099 art_vers,
2100 ovns,
2101 labels,
2102 disp_seqs,
2103 actions,
2104 non_stds,
2105 mandatorys,
2106 lock_texts,
2107 new_parents,
2108 obj_titles,
2109 orut_ids;
2110 --obj_texts_wml;
2111 CLOSE accepted_terms_with_wml_csr;
2112
2113 FOR i IN rut_ids.FIRST .. rut_ids.LAST
2114 LOOP
2115 IF obj_types(i) = 'ARTICLE' THEN
2116 obj_texts_wml(i) := OKC_WORD_DOWNLOAD_UPLOAD.GET_LATEST_WMLBLOB(p_document_id,p_document_type, obj_ids(i));
2117 ELSE
2118 obj_texts_wml(i) := NULL;
2119 END IF;
2120 END LOOP;
2121
2122 ELSE
2123 --2 way word sync with clause edit ends
2124 OPEN accepted_terms_csr;
2125 FETCH accepted_terms_csr BULK COLLECT INTO
2126 rut_ids,
2127 upld_levels,
2128 obj_ids,
2129 obj_types,
2130 obj_texts,
2131 pobj_types,
2132 pobj_ids,
2133 art_ids,
2134 art_vers,
2135 ovns,
2136 labels,
2137 disp_seqs,
2138 actions,
2139 non_stds,
2140 mandatorys,
2141 lock_texts,
2142 new_parents,
2143 obj_titles,
2144 orut_ids;
2145 CLOSE accepted_terms_csr;
2146 END IF;
2147 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2148 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10400: After bulk collect of accepted_terms_csr');
2149 END IF;
2150
2151 IF rut_ids.COUNT > 0 THEN
2152 FOR i IN rut_ids.FIRST .. rut_ids.LAST
2153 LOOP
2154 curr_obj_ids(rut_ids(i)) := obj_ids(i);
2155 curr_disp_seqs(rut_ids(i)) := 0;
2156 END LOOP;
2157
2158 END IF; --IF rut_ids.COUNT > 0 THEN
2159
2160 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2161 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10500: After looping thru new_parents_csr');
2162 END IF;
2163
2164 IF rut_ids.COUNT > 0 THEN
2165
2166 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2167 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10600: Review records exist before looping');
2168 END IF;
2169
2170 FOR i IN rut_ids.FIRST .. rut_ids.LAST
2171 LOOP
2172
2173 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2174 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10700: Looping thru review records');
2175 END IF;
2176
2177 l_ref_id := null;
2178 IF (new_parents(i) is not null and pobj_types(i) = 'SECTION') THEN
2179 l_ref_id := curr_obj_ids(new_parents(i));
2180 END IF;
2181
2182 pobj_ids(i) := l_ref_id;
2183
2184 IF new_parents(i) is not NULL THEN
2185 BEGIN
2186 l_display_sequence := curr_disp_seqs(new_parents(i))+10;
2187 EXCEPTION
2188 WHEN NO_DATA_FOUND THEN
2189 l_display_sequence := 10;
2190 END;
2191
2192 curr_disp_seqs(new_parents(i)) := l_display_sequence;
2193 disp_seqs(i) := l_display_sequence;
2194 END IF;
2195
2196 IF actions(i) = 'ADDED' THEN
2197
2198 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2199 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10900: Action is ADDED');
2200 END IF;
2201
2202 IF obj_types(i) = 'SECTION' THEN
2203
2204 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2205 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11000: Object Type is SECTION');
2206 END IF;
2207
2208 IF (to_char(obj_titles(i)) =
2209 Okc_Util.Decode_Lookup('OKC_ARTICLE_SECTION',G_UNASSIGNED_SECTION_CODE)) THEN
2210
2211 l_scn_code := G_UNASSIGNED_SECTION_CODE;
2212
2213 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2214 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11100: Section is Unassigned');
2215 END IF;
2216 ELSE
2217 l_scn_code := null;
2218 END IF;
2219
2220 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2221 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11200: Before calling OKC_TERMS_SECTIONS_GRP.add_section');
2222 END IF;
2223
2224 OKC_TERMS_SECTIONS_GRP.add_section(
2225 p_api_version => p_api_version,
2226 p_init_msg_list => p_init_msg_list,
2227 p_validation_level => p_validation_level,
2228 p_validate_commit => p_validate_commit,
2229 p_validation_string => p_validation_string,
2230 p_commit => p_commit,
2231 p_mode => p_mode,
2232 x_return_status => x_return_status,
2233 x_msg_count => x_msg_count,
2234 x_msg_data => x_msg_data,
2235 p_id => NULL,
2236 p_ref_scn_id => l_ref_id, -- Section ID fo section which was
2237 p_ref_point => 'S', --Possible values 'A'=After,'B'=Before,'S' = Subsection
2238 p_heading => substr(to_char(obj_titles(i)),1,80),
2239 p_description => NULL,
2240 p_document_type => p_document_type,
2241 p_document_id => p_document_id,
2242 p_scn_code => l_scn_code,
2243 p_print_yn => 'Y',
2244 x_id => l_scn_id);
2245
2246 curr_obj_ids(rut_ids(i)) := l_scn_id;
2247 obj_ids(i) := l_scn_id;
2248
2249 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2250 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11300: After calling OKC_TERMS_SECTIONS_GRP.add_section');
2251 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11400: lscn_id ='||l_scn_id);
2252 END IF;
2253
2254 ELSIF obj_types(i) = 'ARTICLE' THEN
2255 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2256 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11500: Object Type is ARTICLE');
2257 END IF;
2258
2259 IF l_prof_value = 'Y' THEN
2260 NULL;
2261 ELSE
2262 obj_texts(i) :=
2263 regexp_replace(regexp_replace(obj_texts(i),'<var name="[0-9,a-z,A-Z,_,$]*" type="[A-Z]*" meaning="','[@'),'"/>','@]');
2264 END IF;
2265
2266 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2267 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');
2268 END IF;
2269
2270 OKC_K_NON_STD_ART_GRP.create_non_std_article(
2271 p_api_version => p_api_version,
2272 p_init_msg_list => p_init_msg_list,
2273 p_validate_commit => p_validate_commit,
2274 p_validation_string => p_validation_string,
2275 p_commit => p_commit,
2276 p_mode => p_mode,
2277 x_return_status => x_return_status,
2278 x_msg_count => x_msg_count,
2279 x_msg_data => x_msg_data,
2280 p_article_title => substr(to_char(obj_titles(i)),1,450),
2281 p_article_type => 'IMPORTED', -- Article Version Attributes
2282 p_article_text => obj_texts(i),
2283 p_provision_yn => 'N',
2284 p_std_article_version_id => art_vers(i),
2285 p_display_name => null,
2286 p_article_description => null,
2287
2288 -- K Article Attributes
2289 p_ref_type => 'SECTION',
2290 p_ref_id => l_ref_id,
2291 p_doc_type => p_document_type,
2292 p_doc_id => p_document_id,
2293 p_cat_id => l_cat_id,
2294
2295 p_amendment_description => NULL,
2296 p_print_text_yn => NULL,
2297 x_cat_id => l_cat_id,
2298 x_article_version_id => l_article_version_id );
2299
2300 obj_ids(i) := l_cat_id;
2301 art_vers(i) := l_article_version_id;
2302
2303 IF l_prof_value = 'Y' THEN
2304 OKC_WORD_DOWNLOAD_UPLOAD.INSERT_WML_TEXT(l_article_version_id,obj_texts_wml(i));
2305 END IF;
2306
2307 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2308 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11700: After calling OKC_K_NON_STD_ART_GRP.create_non_std_article');
2309 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11800: lcat_id='||l_cat_id);
2310 END IF;
2311 END IF; --IF obj_types(i) = 'SECTION' THEN
2312 ELSIF actions(i) = 'UPDATED' THEN
2313
2314 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2315 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11900: Action is UPDATED');
2316 END IF;
2317
2318 IF obj_types(i) = 'SECTION' THEN
2319
2320 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2321 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12000: B4 call OKC_TERMS_SECTIONS_GRP.update_section');
2322 END IF;
2323
2324 OKC_TERMS_SECTIONS_GRP.update_section(
2325 p_api_version => p_api_version,
2326 p_init_msg_list => p_init_msg_list,
2327 p_validation_level => p_validation_level,
2328 p_validate_commit => p_validate_commit,
2329 p_validation_string => p_validation_string,
2330 p_commit => p_commit,
2331 p_mode => p_mode,
2332 x_return_status => x_return_status,
2333 x_msg_count => x_msg_count,
2334 x_msg_data => x_msg_data,
2335 p_id => obj_ids(i),
2336 p_section_sequence => l_display_sequence,
2337 p_label => NULL,
2338 p_scn_id => l_ref_id,
2339 p_heading => substr(to_char(obj_titles(i)),1,80),
2340 p_description => NULL,
2341 p_scn_code => FND_API.G_MISS_CHAR,
2342 p_object_version_number => NULL);
2343
2344 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2345 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12100: After OKC_TERMS_SECTIONS_GRP.update_section');
2346 END IF;
2347
2348 ELSIF obj_types(i) = 'ARTICLE' THEN
2349
2350 IF l_prof_value = 'Y' THEN
2351 NULL;
2352 ELSE
2353 obj_texts(i) :=
2354 regexp_replace(regexp_replace(obj_texts(i),'<var name="[0-9,a-z,A-Z,_,$]*" type="[A-Z]*" meaning="','[@'),'"/>','@]');
2355 END IF;
2356
2357 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2358 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12200: Object Type is ARTICLE');
2359 END IF;
2360
2361 IF non_stds(i) = 'Y' THEN
2362
2363 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2364 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12300: Non-Std article');
2365 END IF;
2366
2367 l_article_type := null;
2368
2369 open get_clause_type_csr(rut_ids(i));
2370 fetch get_clause_type_csr into l_article_type;
2371 close get_clause_type_csr;
2372
2373 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2374 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');
2375 END IF;
2376
2377 OKC_K_NON_STD_ART_GRP.update_non_std_article(
2378 p_api_version => p_api_version,
2379 p_init_msg_list => p_init_msg_list,
2380 p_validate_commit => p_validate_commit,
2381 p_validation_string => p_validation_string,
2382 p_commit => p_commit,
2383 p_mode => p_mode,
2384 x_return_status => x_return_status,
2385 x_msg_count => x_msg_count,
2386 x_msg_data => x_msg_data,
2387 p_article_title => substr(to_char(obj_titles(i)),1,80),
2388 p_article_type => l_article_type,
2389
2390 -- Article Version Attributes
2391 p_article_text => obj_texts(i),
2392 p_provision_yn => 'N',
2393 p_article_description => NULL,
2394 p_display_name => substr(to_char(obj_titles(i)),1,450),
2395
2396 -- K Article Attributes
2397 p_doc_type => p_document_type,
2398 p_doc_id => p_document_id,
2399 p_cat_id => obj_ids(i),
2400 p_amendment_description => NULL,
2401 p_print_text_yn => NULL,
2402 x_cat_id => l_cat_id,
2403 x_article_version_id => l_article_version_id ) ;
2404
2405 obj_ids(i) := l_cat_id;
2406 art_vers(i) := l_article_version_id;
2407 IF l_prof_value = 'Y' THEN
2408 OKC_WORD_DOWNLOAD_UPLOAD.INSERT_WML_TEXT(l_article_version_id,obj_texts_wml(i));
2409 END IF;
2410
2411
2412 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2413 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12500: After OKC_K_NON_STD_ART_GRP.update_non_std_article');
2414 END IF;
2415
2416 ELSE --IF non_stds(i) = 'Y' THEN
2417
2418 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2419 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12600: Std article');
2420 END IF;
2421
2422 is_ibr := 'N';
2423 is_lock_text := 'N';
2424
2425 open is_article_ibr(rut_ids(i));
2426 fetch is_article_ibr into is_ibr;
2427 close is_article_ibr;
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,'12700: is_ibr='||is_ibr);
2431 END IF;
2432
2433 IF (is_ibr <> 'Y') THEN
2434
2435 open is_article_text_locked(rut_ids(i));
2436 fetch is_article_text_locked into is_lock_text;
2437 close is_article_text_locked;
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,'12800: is_lock_text='||is_lock_text);
2441 END IF;
2442
2443
2444 IF((l_user_access = 'SUPER_USER' and is_lock_text = 'Y') OR
2445 is_lock_text <> 'Y') THEN
2446
2447 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2448 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12900: This is the case of Make Non-Standard');
2449 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');
2450 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13100: Clause Title=' || obj_titles(i));
2451 END IF;
2452
2453 UPDATE okc_k_articles_b
2454 SET scn_id = l_ref_id
2455 WHERE id = obj_ids(i);
2456
2457 OKC_K_NON_STD_ART_GRP.create_non_std_article(
2458 p_api_version => p_api_version,
2459 p_init_msg_list => p_init_msg_list,
2460 p_validate_commit => p_validate_commit,
2461 p_validation_string => p_validation_string,
2462 p_commit => p_commit,
2463 p_mode => p_mode,
2464 x_return_status => x_return_status,
2465 x_msg_count => x_msg_count,
2466 x_msg_data => x_msg_data,
2467 p_article_title => substr(to_char(obj_titles(i)),1,450),
2468 p_article_type => 'IMPORTED',
2469 -- Article Version Attributes
2470 p_article_text => obj_texts(i),
2471 p_provision_yn => 'N',
2472 p_std_article_version_id => art_vers(i),
2473 p_display_name => substr(to_char(obj_titles(i)),1,450),
2474 p_article_description => null,
2475
2476 -- K Article Attributes
2477 p_ref_type => 'SECTION',
2478 p_ref_id => l_ref_id,
2479 p_doc_type => p_document_type,
2480 p_doc_id => p_document_id,
2481 p_cat_id => obj_ids(i),
2482
2483 p_amendment_description => NULL,
2484 p_print_text_yn => NULL,
2485 x_cat_id => l_cat_id,
2486 x_article_version_id => l_article_version_id );
2487
2488
2489
2490 obj_ids(i) := l_cat_id;
2491 art_vers(i) := l_article_version_id;
2492 IF l_prof_value = 'Y' THEN
2493 OKC_WORD_DOWNLOAD_UPLOAD.INSERT_WML_TEXT(l_article_version_id,obj_texts_wml(i));
2494 END IF;
2495
2496 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2497 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13200: After OKC_K_NON_STD_ART_GRP.create_non_std_article');
2498 END IF;
2499 END IF; -- if l_user_access...
2500 END IF; -- if ibr_text <> 'Y'
2501 END IF; -- IF non_stds(i) = 'Y' THEN
2502 END IF;
2503
2504 ELSIF (actions(i) = 'DELETED' OR actions(i) = 'MERGED') THEN
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,'13300: Action='||actions(i));
2508 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13400: object Type='||obj_types(i));
2509 END IF;
2510
2511 IF obj_types(i) = 'SECTION' THEN
2512 IF p_mode = 'AMEND' THEN
2513 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2514 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13500: p_mode='||p_mode);
2515 END IF;
2516
2517 UPDATE okc_sections_b scn
2518 SET scn.last_updated_by = fnd_global.user_id,
2519 scn.last_update_date = sysdate,
2520 scn.amendment_operation_code = 'DELETED',
2521 scn.summary_amend_operation_code =
2522 okc_terms_util_pvt.get_summary_amend_code(
2523 scn.summary_amend_operation_code,
2524 scn.amendment_operation_code,
2525 'DELETED'),
2526 scn.last_amended_by = fnd_global.user_id,
2527 scn.last_amendment_date = sysdate
2528 WHERE scn.id = obj_ids(i);
2529 ELSE
2530
2531 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2532 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13500: p_mode='||p_mode);
2533 END IF;
2534
2535 DELETE FROM okc_sections_b
2536 WHERE id = obj_ids(i);
2537 END IF; --IF p_mode = 'AMEND' THEN
2538
2539 ELSIF obj_types(i) = 'ARTICLE' THEN
2540
2541 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2542 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13800: Clause Title=' || obj_titles(i));
2543 END IF;
2544
2545 is_mandatory_text := 'N';
2546
2547 open is_article_mandatory(rut_ids(i));
2548 fetch is_article_mandatory into is_mandatory_text;
2549 close is_article_mandatory;
2550
2551 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2552 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13900: is_article_mandatory='|| is_mandatory_text);
2553 END IF;
2554
2555 IF ((l_user_access = 'SUPER_USER' ) OR is_mandatory_text <> 'Y') then
2556 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2557 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14000: Before invoking delete on okc_k_articles_b');
2558 END IF;
2559 IF p_mode = 'AMEND' THEN
2560
2561 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2562 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14100: B4 delete k_art mode=AMEND, objectID='||obj_ids(i));
2563 END IF;
2564
2565 UPDATE okc_k_articles_b kart
2566 SET kart.last_updated_by = fnd_global.user_id,
2567 kart.last_update_date = sysdate,
2568 kart.amendment_operation_code = 'DELETED',
2569 kart.summary_amend_operation_code =
2570 okc_terms_util_pvt.get_summary_amend_code(
2571 kart.summary_amend_operation_code,
2572 kart.amendment_operation_code,
2573 'DELETED'),
2574 kart.last_amended_by = fnd_global.user_id,
2575 kart.last_amendment_date = sysdate
2576 WHERE kart.id = obj_ids(i);
2577 ELSE
2578
2579 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2580 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14200: after delete k_art, objectID='||obj_ids(i));
2581 END IF;
2582
2583 DELETE FROM okc_k_articles_b
2584 WHERE id = obj_ids(i);
2585 END IF;
2586
2587 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2588 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14300: After delete on okc_k_articles_b');
2589 END IF;
2590 END IF; --IF ((l_user_access = 'SUPER_USER' )
2591 END IF; --IF obj_types(i) = 'SECTION' THEN
2592 --ELSE
2593 END IF; --IF actions(i) = 'ADDED'
2594 END LOOP;
2595 END IF; --IF rut_ids.COUNT > 0 THEN
2596
2597 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2598 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14400: Before Bulk update of okc_sections_b');
2599 END IF;
2600
2601 FORALL i IN rut_ids.FIRST..rut_ids.LAST
2602 UPDATE okc_sections_b
2603 SET scn_id = pobj_ids(i),
2604 section_sequence = disp_seqs(i)
2605 WHERE id = obj_ids(i)
2606 AND obj_types(i) = 'SECTION';
2607
2608 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2609 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14500: Before Bulk update of okc_k_articles_b');
2610 END IF;
2611
2612 FORALL i IN rut_ids.FIRST..rut_ids.LAST
2613 UPDATE okc_k_articles_b
2614 SET scn_id = pobj_ids(i),
2615 display_sequence = disp_seqs(i)
2616 WHERE id = obj_ids(i)
2617 AND obj_types(i) = 'ARTICLE';
2618
2619 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2620 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14600: Before delete on Review Tables');
2621 END IF;
2622
2623 OKC_TEMPLATE_USAGES_GRP.update_template_usages(
2624 p_api_version => l_api_version,
2625 p_init_msg_list => p_init_msg_list ,
2626 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2627 p_commit => FND_API.G_FALSE,
2628 x_return_status => x_return_status,
2629 x_msg_count => x_msg_count,
2630 x_msg_data => x_msg_data,
2631 p_document_type => p_document_type,
2632 p_document_id => p_document_id,
2633 p_lock_terms_flag => 'N');
2634
2635 delete from okc_REVIEW_UPLD_TERMS where document_type = p_document_type and
2636 document_id = p_document_id;
2637
2638 delete from okc_review_upld_header where document_type = p_document_type and
2639 document_id = p_document_id;
2640
2641 delete from okc_review_messages where REVIEW_UPLD_TERMS_ID
2642 in (select REVIEW_UPLD_TERMS_ID from okc_REVIEW_UPLD_TERMS where document_type = p_document_type and
2643 document_id = p_document_id);
2644
2645 delete from OKC_REVIEW_VAR_VALUES where REVIEW_UPLD_TERMS_ID
2646 in (select REVIEW_UPLD_TERMS_ID from okc_REVIEW_UPLD_TERMS where document_type = p_document_type and
2647 document_id = p_document_id);
2648
2649 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2650 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14700: After delete on Review Tables');
2651 END IF;
2652
2653 open current_num_scheme;
2654 fetch current_num_scheme into l_doc_num_scheme;
2655 close current_num_scheme;
2656
2657 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2658 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14800: l_doc_num_scheme=' || l_doc_num_scheme);
2659 END IF;
2660
2661 IF (l_doc_num_scheme is NOT NULL) THEN
2662 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2663 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14900: Before invoking apply_numbering_scheme');
2664 END IF;
2665
2666 OKC_NUMBER_SCHEME_GRP.apply_numbering_scheme(
2667 p_api_version => p_api_version,
2668 p_init_msg_list => p_init_msg_list,
2669 x_return_status => x_return_status,
2670 x_msg_count => x_msg_count,
2671 x_msg_data => x_msg_data,
2672 p_commit => p_commit,
2673 p_validation_string => p_validation_string,
2674 p_doc_type => p_document_type,
2675 p_doc_id => p_document_id,
2676 p_num_scheme_id => l_doc_num_scheme);
2677
2678 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2679 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15000: After invoking apply_numbering_scheme');
2680 END IF;
2681 END IF; --IF (l_doc_num_scheme is NOT NULL) THEN
2682
2683 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2684 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15100: After Accept Changes');
2685 END IF;
2686
2687 -- Added for Performance after accepting the changes no longer required to retain data in this table.
2688 DELETE FROM okc_word_sync_t WHERE doc_id=p_document_id AND doc_type=p_document_type;
2689
2690 -- Standard check of p_commit
2691 IF FND_API.To_Boolean( p_commit ) THEN
2692 COMMIT WORK;
2693 END IF;
2694
2695 EXCEPTION
2696 WHEN FND_API.G_EXC_ERROR THEN
2697 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2698 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15200: Leaving accept_Changes: OKC_API.G_EXCEPTION_ERROR Exception');
2699 END IF;
2700 ROLLBACK TO g_accept_changes;
2701 x_return_status := G_RET_STS_ERROR ;
2702 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2703
2704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2705 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2706 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15300: Leaving accept_Changes: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2707 END IF;
2708 ROLLBACK TO g_accept_Changes;
2709 x_return_status := G_RET_STS_UNEXP_ERROR ;
2710 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2711
2712 WHEN OTHERS THEN
2713 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2714 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15400: Leaving accept_changes because of EXCEPTION: '||sqlerrm);
2715 END IF;
2716
2717 ROLLBACK TO g_accept_changes;
2718 x_return_status := G_RET_STS_UNEXP_ERROR ;
2719 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2720 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2721 END IF;
2722 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2723 END Accept_Changes;
2724
2725 PROCEDURE Reject_Changes (
2726 p_api_version IN NUMBER,
2727 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2728 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2729 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2730
2731 p_document_type IN VARCHAR2,
2732 p_document_id IN NUMBER,
2733
2734 x_return_status OUT NOCOPY VARCHAR2,
2735 x_msg_data OUT NOCOPY VARCHAR2,
2736 x_msg_count OUT NOCOPY NUMBER
2737 ) IS
2738 l_api_name CONSTANT VARCHAR2(30) := 'Reject_Changes';
2739 l_api_version CONSTANT NUMBER := 1;
2740
2741 BEGIN
2742 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2743 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering Reject_Changes');
2744 END IF;
2745
2746 -- Standard Start of API savepoint
2747 SAVEPOINT g_reject_changes;
2748 -- Standard call to check for call compatibility.
2749 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2751 END IF;
2752 -- Initialize message list if p_init_msg_list is set to TRUE.
2753 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2754 FND_MSG_PUB.initialize;
2755 END IF;
2756 -- Initialize API return status to success
2757 x_return_status := FND_API.G_RET_STS_SUCCESS;
2758
2759 OKC_REVIEW_UPLD_TERMS_PVT.delete_uploaded_terms(
2760 p_api_version => l_api_version,
2761 p_init_msg_list => p_init_msg_list ,
2762 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2763 p_commit => FND_API.G_FALSE,
2764 x_return_status => x_return_status,
2765 x_msg_count => x_msg_count,
2766 x_msg_data => x_msg_data,
2767 p_document_type => p_document_type,
2768 p_document_id => p_document_id
2769 );
2770
2771 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2772 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: After delete_uploaded_terms');
2773 END IF;
2774
2775 -- Added for Performance after accepting the changes no longer required to retain data in this table.
2776 DELETE FROM okc_word_sync_t WHERE doc_id=p_document_id AND doc_type=p_document_type;
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 Reject_Changes: OKC_API.G_EXCEPTION_ERROR Exception');
2787 END IF;
2788 ROLLBACK TO g_reject_changes;
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 Reject_Changes: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2795 END IF;
2796 ROLLBACK TO g_reject_Changes;
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 reject_changes because of EXCEPTION: '||sqlerrm);
2803 END IF;
2804
2805 ROLLBACK TO g_reject_changes;
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 Reject_Changes;
2813
2814
2815 PROCEDURE Delete_Uploaded_Terms (
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
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) := 'Delete_Uploaded_Terms';
2829 l_api_version CONSTANT NUMBER := 1;
2830
2831 BEGIN
2832 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2833 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering Delete_Uploaded_Terms');
2834 END IF;
2835
2836 -- Standard Start of API savepoint
2837 SAVEPOINT g_delete_uploaded_terms;
2838 -- Standard call to check for call compatibility.
2839 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2841 END IF;
2842 -- Initialize message list if p_init_msg_list is set to TRUE.
2843 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2844 FND_MSG_PUB.initialize;
2845 END IF;
2846 -- Initialize API return status to success
2847 x_return_status := FND_API.G_RET_STS_SUCCESS;
2848
2849 DELETE
2850 FROM OKC_REVIEW_MESSAGES m
2851 WHERE m.REVIEW_UPLD_TERMS_id IN
2852 (SELECT REVIEW_UPLD_TERMS_id
2853 FROM okc_REVIEW_UPLD_TERMS
2854 WHERE document_id = p_document_id
2855 AND document_type = p_document_type);
2856
2857 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2858 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: After Delete Review Messages');
2859 END IF;
2860
2861 DELETE
2862 FROM OKC_REVIEW_VAR_VALUES v
2863 WHERE v.REVIEW_UPLD_TERMS_id IN
2864 (SELECT REVIEW_UPLD_TERMS_id
2865 FROM okc_REVIEW_UPLD_TERMS
2866 WHERE document_id = p_document_id
2867 AND document_type = p_document_type);
2868
2869 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2870 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: After delete review variables');
2871 END IF;
2872
2873 DELETE
2874 FROM OKC_REVIEW_UPLD_TERMS
2875 WHERE document_id = p_document_id
2876 AND document_type = p_document_type;
2877
2878 DELETE
2879 FROM OKC_REVIEW_UPLD_HEADER
2880 WHERE document_id = p_document_id
2881 AND document_type = p_document_type;
2882
2883 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2884 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: After delete review terms');
2885 END IF;
2886
2887 -- Standard check of p_commit
2888 IF FND_API.To_Boolean( p_commit ) THEN
2889 COMMIT WORK;
2890 END IF;
2891
2892 EXCEPTION
2893 WHEN FND_API.G_EXC_ERROR THEN
2894 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2895 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Delete_Uploaded_Terms: OKC_API.G_EXCEPTION_ERROR Exception');
2896 END IF;
2897 ROLLBACK TO g_delete_uploaded_terms;
2898 x_return_status := G_RET_STS_ERROR ;
2899 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2900
2901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2902 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2903 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Delete_Uploaded_terms: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2904 END IF;
2905 ROLLBACK TO g_delete_uploaded_terms;
2906 x_return_status := G_RET_STS_UNEXP_ERROR ;
2907 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2908
2909 WHEN OTHERS THEN
2910 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2911 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving delete_uploaded_Terms because of EXCEPTION: '||sqlerrm);
2912 END IF;
2913
2914 ROLLBACK TO g_delete_uploaded_terms;
2915 x_return_status := G_RET_STS_UNEXP_ERROR ;
2916 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2917 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2918 END IF;
2919 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2920
2921 END delete_uploaded_terms;
2922
2923
2924 PROCEDURE Sync_Review_Tables (
2925 p_api_version IN NUMBER,
2926 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2927 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2928 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2929 p_validation_string IN VARCHAR2 := NULL,
2930 p_document_type IN VARCHAR2,
2931 p_document_id IN NUMBER,
2932
2933 x_return_status OUT NOCOPY VARCHAR2,
2934 x_msg_data OUT NOCOPY VARCHAR2,
2935 x_msg_count OUT NOCOPY NUMBER
2936 ) IS
2937 l_api_name CONSTANT VARCHAR2(30) := 'Sync_Review_Tables';
2938 l_api_version CONSTANT NUMBER := 1;
2939 l_doc_exists VARCHAR2(1);
2940 l_rev_id_for_doc OKC_REVIEW_UPLD_TERMS.REVIEW_UPLD_TERMS_ID%TYPE;
2941 l_unassigned_scn_id OKC_SECTIONS_B.ID%TYPE;
2942 l_clauses_no_parent_exist VARCHAR2(1);
2943 l_clause_title FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2944 l_section_title FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2945 l_clause_counter NUMBER;
2946 l_section_counter NUMBER;
2947 l_doc_num_scheme OKC_TEMPLATE_USAGES.DOC_NUMBERING_SCHEME%TYPE;
2948 l_user_access VARCHAR2(30);
2949 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2950 l_sequence OKC_K_ARTICLES_B.DISPLAY_SEQUENCE%TYPE;
2951 l_count NUMBER;
2952 l_prev_rev_id OKC_REVIEW_UPLD_TERMS.REVIEW_UPLD_TERMS_ID%TYPE;
2953 l_prev_new_parent_id OKC_REVIEW_UPLD_TERMS.NEW_PARENT_ID%TYPE;
2954 l_intent OKC_BUS_DOC_TYPES_B.INTENT%TYPE;
2955
2956 cursor check_document_row_exists IS
2957 select REVIEW_UPLD_TERMS_id from okc_REVIEW_UPLD_TERMS
2958 where document_type = p_document_type and document_id = p_document_id
2959 and object_id = p_document_id and object_type = p_document_type;
2960
2961 cursor unresolved_del_rec is
2962 select REVIEW_UPLD_TERMS_id, object_id, object_type, parent_id, parent_object_type,
2963 new_parent_id from okc_REVIEW_UPLD_TERMS
2964 where document_type = p_document_type
2965 and document_id = p_document_id
2966 and action = 'DELETED'
2967 and new_parent_id is null
2968 order by object_type ;
2969
2970 cursor clauses_without_parent_exist is
2971 select 'Y'
2972 from OKC_REVIEW_UPLD_TERMS
2973 where document_type = p_document_type
2974 and document_id = p_document_id
2975 and ( (object_type = 'ARTICLE'
2976 and new_parent_id IS NULL)
2977 OR (object_type = 'ARTICLE'
2978 and new_parent_id = (select review_upld_terms_id
2979 from okc_review_upld_terms
2980 where document_type = p_document_type
2981 and document_id = p_document_id
2982 and object_id = p_document_id
2983 and object_type = p_document_type))
2984 OR (object_type = 'SECTION' and new_parent_id IS NULL)
2985 );
2986
2987
2988 cursor clauses_without_parent_id is
2989 select REVIEW_UPLD_TERMS_id, object_id, object_type, parent_id, parent_object_type,
2990 new_parent_id from okc_REVIEW_UPLD_TERMS
2991 where document_type = p_document_type and document_id = p_document_id
2992 and object_type = 'ARTICLE' and new_parent_id IS NULL and article_id is null and article_version_id is null;
2993
2994 cursor clauses_no_parent_id_and_moved is
2995 select REVIEW_UPLD_TERMS_id, object_id, object_type, parent_id, parent_object_type,
2996 new_parent_id
2997 from okc_REVIEW_UPLD_TERMS
2998 where document_type = p_document_type
2999 and document_id = p_document_id
3000 and ((object_type = 'ARTICLE'
3001 and new_parent_id IS NULL
3002 and article_id is not null
3003 and article_version_id is not null)
3004 OR (object_type = 'SECTION' and new_parent_id IS NULL));
3005
3006 cursor unassigned_section_exists is
3007 select REVIEW_UPLD_TERMS_id from okc_REVIEW_UPLD_TERMS
3008 where document_type = p_document_type and document_id = p_document_id
3009 and object_type = 'SECTION' and to_char(object_title) = Okc_Util.Decode_Lookup('OKC_ARTICLE_SECTION',G_UNASSIGNED_SECTION_CODE);
3010
3011 cursor review_variable_values is
3012 select rev_var.variable_value_id, rev_var.REVIEW_UPLD_TERMS_id, rev_var.variable_name,
3013 rev_var.language, rev_var.variable_code, rev_var.variable_type, rev_var.attribute_value_set_id,
3014 rev_var.variable_value_id
3015 from OKC_REVIEW_VAR_VALUES rev_var, okc_REVIEW_UPLD_TERMS rev
3016 where rev_var.REVIEW_UPLD_TERMS_id = rev.REVIEW_UPLD_TERMS_id
3017 and rev.document_type = p_document_type and rev.document_id = p_document_id;
3018
3019
3020 cursor current_variable_values is
3021 select art_var.cat_id, art_var.variable_code, art_var.variable_type, art_var.external_yn,
3022 art_var.attribute_value_set_id,art_var.variable_value_id, art_var.variable_value
3023 from okc_k_art_variables art_var, okc_k_articles_b kart
3024 where art_var.cat_id = kart.id
3025 and kart.document_type = p_document_type and kart.document_id = p_document_id;
3026
3027 cursor variable_values_changed is
3028 SELECT rev_var.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,rev_var.variable_code variable_code,
3029 rev_var.variable_name variable_name,
3030 kart_var.variable_value kart_variable_value, rev_var.variable_value rev_variable_value,
3031 kart_var.variable_type, rev_var.variable_value_id
3032
3033 FROM OKC_REVIEW_VAR_VALUES rev_var, okc_k_art_variables kart_var,
3034 OKC_REVIEW_UPLD_TERMS rev, okc_article_versions av
3035 WHERE rev.object_id = kart_var.cat_id
3036 AND rev.REVIEW_UPLD_TERMS_id = rev_var.REVIEW_UPLD_TERMS_id
3037 AND kart_var.variable_code = rev_var.variable_code
3038 AND av.article_version_id = rev.article_version_id
3039 AND ((kart_var.variable_type='U' and kart_var.variable_value is null
3040 and (kart_var.variable_value is null
3041 and (rev_var.variable_value is not null and rev_var.variable_value <> '_________' )
3042 and exists(select 'x' from okc_bus_variables_tl bustl where bustl.variable_code = rev_var.variable_code)) OR
3043 (kart_var.variable_value is not null and rev_var.variable_value <> kart_var.variable_value))
3044 OR (kart_var.variable_type<>'U' and exists
3045 (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
3046 and var_doc.doc_Type = p_document_type))
3047 )
3048 and rev.document_type = p_document_type and rev.document_id = p_document_id
3049 and nvl(rev.action,'NOCHANGE') not in ('DELETED','MERGED')
3050 and nvl(av.insert_by_reference,'N') <> 'Y';
3051
3052 cursor variables_removed is
3053 select rev.review_upld_terms_id, kart_var.variable_code, variable_name, language, description
3054 from okc_k_art_variables kart_var, okc_review_upld_terms rev, okc_bus_variables_tl bustl, okc_article_versions av
3055 where rev.object_id = kart_var.cat_id
3056 and rev.document_type = p_document_type and rev.document_id = p_document_id
3057 and rev.article_version_id = av.article_version_id
3058 and kart_var.variable_code not in (select variable_code from okc_review_Var_values rev_var
3059 where rev_var.review_upld_terms_id = rev.review_upld_terms_id)
3060 and rev.action not in ('DELETED','MERGED')
3061 and bustl.variable_code = kart_var.variable_code
3062 and language = userenv('LANG')
3063 and nvl(av.insert_by_reference,'N') <> 'Y';
3064
3065 cursor valid_variable_added(p_intent VARCHAR2) is
3066 select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type,
3067 decode(rev_var.variable_value,
3068 NULL, 'N',
3069 'Y') modified
3070 from okc_review_upld_Terms rev, okc_review_var_values rev_var, okc_article_versions av
3071 where rev.document_type = p_document_type and rev.document_id = p_document_id
3072 and rev_var.review_upld_terms_id = rev.review_upld_terms_id
3073 and av.article_version_id = rev.article_version_id
3074 and not exists(
3075 select 'x' from okc_k_art_variables kart_var, okc_review_upld_Terms rev_upld where
3076 rev_upld.review_upld_Terms_id = rev_var.review_upld_terms_id
3077 and rev_upld.object_id = kart_var.cat_id
3078 and rev_var.variable_code = kart_var.variable_code
3079 and (
3080 (rev_var.variable_type = 'U'
3081 and exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
3082 and busb.variable_intent = p_intent)
3083 )
3084 OR
3085 (rev_var.variable_type <> 'U'
3086 and exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
3087 and var_doc.doc_type = p_document_type)
3088 )
3089 )
3090
3091 )
3092 and rev.action not in ('DELETED','MERGED')
3093 and nvl(av.insert_by_reference,'N') <> 'Y';
3094
3095 cursor invalid_variable_added(p_intent VARCHAR2) is
3096 select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type
3097 from okc_review_upld_terms rev, okc_review_var_values rev_var
3098 where document_type = p_document_type and rev.document_id = p_document_id
3099 and rev_var.review_upld_terms_id = rev.review_upld_terms_id
3100 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
3101 and var_doc.doc_type = p_document_type))
3102 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
3103 and busb.variable_intent = p_intent))
3104 )
3105 and rev.action not in ('DELETED','MERGED') ;
3106
3107 cursor valid_var_new_clause(p_intent VARCHAR2) is
3108 select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type,
3109 decode(rev_var.variable_value,
3110 NULL, 'N', 'Y') modified
3111 from okc_review_upld_Terms rev, okc_review_var_values rev_var
3112 where rev.document_type = p_document_type and rev.document_id = p_document_id
3113 and rev_var.review_upld_terms_id = rev.review_upld_terms_id
3114 and (
3115 (rev_var.variable_type = 'U'
3116 and exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
3117 and busb.variable_intent = p_intent)
3118 )
3119 OR
3120 (rev_var.variable_type <> 'U'
3121 and exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
3122 and var_doc.doc_type = p_document_type)
3123 )
3124 )
3125 and rev.action = 'ADDED' ;
3126
3127
3128 cursor valid_new_var_ibr(p_intent VARCHAR2) is
3129 select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type,
3130 decode(rev_var.variable_value,
3131 NULL, 'N', 'Y') modified
3132 from okc_review_upld_terms rev, okc_review_var_values rev_var, okc_article_versions av
3133 where rev.document_type = p_document_type and rev.document_id = p_document_id
3134 and rev_var.review_upld_terms_id = rev.review_upld_terms_id
3135 and av.article_version_id = rev.article_version_id
3136 and (
3137 (rev_var.variable_type = 'U'
3138 and exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
3139 and busb.variable_intent = p_intent)
3140 )
3141 OR
3142 (rev_var.variable_type <> 'U'
3143 and exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
3144 and var_doc.doc_type = p_document_type)
3145 )
3146 )
3147
3148 and nvl(av.insert_by_reference,'N') = 'Y';
3149
3150 cursor empty_title_csr is
3151 SELECT rev.object_title, rev.REVIEW_UPLD_TERMS_id, rev.object_type
3152 from okc_REVIEW_UPLD_TERMS rev
3153 where document_type = p_document_type and
3154 document_id = p_document_id and
3155 object_title is null
3156 and object_type IN ('ARTICLE', 'SECTION');
3157 cursor current_num_scheme is
3158 select doc_numbering_scheme from okc_template_usages
3159 where document_type = p_document_type and
3160 document_id = p_document_id;
3161
3162
3163 CURSOR is_article_ibr(p_user_access VARCHAR2) is
3164 SELECT 'Y', ACTION, review_upld_terms_id, object_title
3165 from okc_article_versions av, okc_review_upld_terms ar
3166 where av.article_version_id = ar.article_version_id
3167 and av.insert_by_reference = 'Y'
3168 and ar.document_type = p_document_type
3169 and ar.document_id = p_document_id
3170 and ar.object_type = 'ARTICLE'
3171 and ar.action = 'UPDATED'
3172 and p_user_access <> 'SUPER_USER';
3173
3174 CURSOR is_article_mandatory(p_user_access VARCHAR2) is
3175 SELECT 'Y', ACTION, review_upld_terms_id, object_title
3176 from okc_k_articles_b akb, okc_review_upld_terms ar
3177 where akb.id = ar.object_id
3178 and akb.mandatory_yn = 'Y'
3179 and ar.document_type = p_document_type
3180 and ar.document_id = p_document_id
3181 and ar.object_type = 'ARTICLE'
3182 and ar.action = 'DELETED'
3183 and p_user_access <> 'SUPER_USER';
3184
3185 CURSOR is_article_text_locked(p_user_access VARCHAR2) is
3186 SELECT 'Y', ACTION, review_upld_terms_id, object_title
3187 from okc_article_versions av, okc_review_upld_terms ar
3188 where av.article_version_id = ar.article_version_id
3189 and av.lock_text = 'Y'
3190 and ar.document_type = p_document_type
3191 and ar.document_id = p_document_id
3192 and ar.object_type = 'ARTICLE'
3193 and ar.action = 'UPDATED'
3194 and p_user_access <> 'SUPER_USER';
3195
3196 CURSOR update_err_warn_csr is
3197 select
3198 count(*) err_warn_count,
3199 rev_msg.review_upld_terms_id,
3200 rev_msg.error_severity
3201 from
3202 okc_review_messages rev_msg ,
3203 okc_review_upld_terms rev_trm
3204 where
3205 rev_msg.review_upld_terms_id = rev_trm.review_upld_terms_id
3206 and rev_trm.document_type = p_document_type
3207 and rev_trm.document_id = p_document_id
3208 group by rev_msg.review_upld_terms_id, rev_msg.error_severity;
3209
3210 /* This cursor is for debugging purposes. This cursor is invoked only when logging is enabled
3211 */
3212 cursor get_updated_articles_csr is
3213 select review_upld_terms_id,
3214 object_title,
3215 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ltrim(ut.object_text),'&NBSP;| ',' '),
3216 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>|
3217 <A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>|
3218 <STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3219 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3220 |<HR>|</HR>|<hr>|</hr>',''),unistr('\00a0'),' '),' ',''),'&|&|<Palign="justify">|<divalign="both">','') rev_text,
3221 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ver.article_text,'&NBSP;| ',' '),
3222 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>|<A>|<a>|</A>|</a>
3223 |<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>|
3224 <STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3225 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3226 |<HR>|</HR>|<hr>|</hr>',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">','') ver_text,
3227 NVL(dbms_lob.compare(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ltrim(ut.object_text),
3228 '&NBSP;| ',' '),'<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3229 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>
3230 |<STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3231 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3232 |<HR>|</HR>|<hr>|</hr>',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">','') ,
3233 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ver.article_text,'&NBSP;| ',' '),
3234 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3235 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>
3236 |<STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3237 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3238 |<HR>|</HR>|<hr>|</hr>',''),unistr('\00a0'),' '),' ',''), '&|&|<Palign="justify">|<divalign="both">','')),-1) diff
3239 from okc_review_upld_terms ut, okc_article_versions ver
3240 where ut.article_version_id = ver.article_version_id
3241 and ut.article_version_id is not null
3242 and ut.action = 'UPDATED'
3243 and ut.document_type = p_document_type
3244 and ut.document_id = p_document_id;
3245
3246 cursor check_sec_clause_title is
3247 select review_upld_terms_id,
3248 object_title, object_type
3249 from okc_review_upld_terms rev
3250 where rev.document_type = p_document_type
3251 and rev.document_id = p_document_id
3252 and rev.object_type in ('SECTION','ARTICLE')
3253 and rev.action in ('ADDED','UPDATED')
3254 and ((object_type = 'SECTION' and length(to_char(object_title)) > 80) OR (object_type = 'ARTICLE' and length(to_char(object_title)) > 450)) ;
3255
3256 cursor terms_disp_csr is
3257 select review_upld_terms_id, object_type, object_id, object_title, action, display_seq, new_parent_id
3258 from okc_review_upld_Terms
3259 where document_type = p_document_type
3260 and document_id = p_document_id
3261 and nvl(action,'XXX') <> 'DELETED'
3262 start with new_parent_id is null
3263 connect by prior review_upld_terms_id = new_parent_id
3264 order siblings by review_upld_terms_id;
3265
3266 cursor parent_ids_csr is
3267 select review_upld_terms_id
3268 from okc_review_upld_Terms
3269 where document_type = p_document_type
3270 and document_id = p_document_id ;
3271
3272 cursor deleted_terms_csr is
3273 select new_parent_id,
3274 display_seq
3275 from okc_review_upld_Terms
3276 where document_type = p_document_type
3277 and document_id = p_document_id
3278 and action = 'DELETED';
3279
3280 --TYPE disp_seq_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3281 TYPE new_parent_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.NEW_PARENT_ID%TYPE INDEX BY BINARY_INTEGER;
3282 TYPE disp_seq_tab IS TABLE OF OKC_REVIEW_UPLD_TERMS.DISPLAY_SEQ%TYPE INDEX BY BINARY_INTEGER;
3283
3284 curr_disp_seqs disp_seq_tab;
3285 del_disp_seqs disp_seq_tab;
3286 del_new_parents new_parent_tab;
3287 --disp_seqs disp_seq_tab;
3288
3289 cursor get_intent_csr is
3290 select intent from okc_bus_doc_types_b
3291 where document_type = p_document_type;
3292
3293 l_prof_value VARCHAR2(1);
3294 BEGIN
3295
3296 -- Standard Start of API savepoint
3297 SAVEPOINT g_reject_changes;
3298 -- Standard call to check for call compatibility.
3299 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3301 END IF;
3302 -- Initialize message list if p_init_msg_list is set to TRUE.
3303 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3304 FND_MSG_PUB.initialize;
3305 END IF;
3306 -- Initialize API return status to success
3307 x_return_status := FND_API.G_RET_STS_SUCCESS;
3308 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_STD','N') THEN
3309 l_user_access := 'STD_AUTHOR';
3310 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_NON_STD','N') THEN
3311 l_user_access := 'NON_STD_AUTHOR';
3312 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_SUPERUSER','N') THEN
3313 l_user_access := 'SUPER_USER';
3314 END IF;
3315 END IF;
3316 ELSE
3317 l_user_access := 'NO_ACCESS';
3318 END IF;
3319
3320 open get_intent_csr;
3321 fetch get_intent_csr into l_intent;
3322 close get_intent_csr;
3323
3324 open check_document_row_exists;
3325 fetch check_document_row_exists into l_doc_exists;
3326 if (check_document_row_exists%NOTFOUND) THEN
3327 INSERT into okc_REVIEW_UPLD_TERMS(
3328 REVIEW_UPLD_TERMS_ID,
3329 DOCUMENT_ID,
3330 DOCUMENT_TYPE,
3331 OBJECT_ID,
3332 OBJECT_TYPE,
3333 OBJECT_TITLE,
3334 OBJECT_TEXT,
3335 PARENT_OBJECT_TYPE,
3336 PARENT_ID,
3337 ARTICLE_ID,
3338 ARTICLE_VERSION_ID,
3339 OBJECT_VERSION_NUMBER,
3340 LABEL,
3341 DISPLAY_SEQ,
3342 ACTION,
3343 ERROR_MESSAGE_COUNT,
3344 WARNING_MESSAGE_COUNT,
3345 NEW_PARENT_ID,
3346 LAST_UPDATE_LOGIN,
3347 CREATED_BY,
3348 CREATION_DATE,
3349 LAST_UPDATED_BY,
3350 LAST_UPDATE_DATE)
3351 (
3352 SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL,
3353 P_DOCUMENT_ID,
3354 P_DOCUMENT_TYPE,
3355 p_document_id,
3356 P_DOCUMENT_TYPE,
3357 okc_terms_util_pvt.get_message('OKC','OKC_TERMS_CONTRACT_TERMS'),
3358 null,
3359 null,
3360 null,
3361 null,
3362 null,
3363 1,
3364 null,
3365 null,
3366 null,
3367 null,
3368 null,
3369 null,
3370 FND_GLOBAL.LOGIN_ID,
3371 FND_GLOBAL.USER_ID,
3372 SYSDATE,
3373 FND_GLOBAL.USER_ID,
3374 SYSDATE from dual);
3375
3376 END IF;
3377 CLOSE check_document_row_exists;
3378
3379 open check_document_row_exists;
3380 fetch check_document_row_exists into l_rev_id_for_doc;
3381 close check_document_row_exists;
3382
3383
3384 update okc_REVIEW_UPLD_TERMS
3385 set new_parent_id = (select REVIEW_UPLD_TERMS_id from okc_REVIEW_UPLD_TERMS where
3386 document_type = p_document_type and document_id = p_document_id
3387 and object_type = p_document_type and object_id = p_document_id)
3388 where document_type = p_document_type and document_id = p_document_id
3389 and object_type = 'SECTION' and new_parent_id = p_document_id;
3390
3391
3392 update okc_REVIEW_UPLD_TERMS rev
3393 set rev.article_id = (select sav_sae_id from okc_k_articles_b
3394 where document_type = p_document_type
3395 and document_id = p_document_id
3396 and id = rev.object_id)
3397
3398 ,rev.article_version_id = (select article_version_id from okc_k_articles_b
3399 where document_type = p_document_type
3400 and document_id = p_document_id
3401 and id = rev.object_id)
3402 where document_type = p_document_type
3403 and document_id = p_document_id
3404 and object_type = 'ARTICLE'
3405 and object_id is not null
3406 and article_id is null
3407 and article_version_id is null
3408 and exists (select 1 from okc_k_articles_b kart
3409 where document_type = p_document_type
3410 and document_id = p_document_id
3411 and id = rev.object_id);
3412
3413
3414 update okc_Review_upld_terms rev_terms
3415 set action = 'ADDED',
3416 article_id = null,
3417 article_version_id = null,
3418 object_id = null
3419 where rev_terms.review_upld_terms_id > (select min(review_upld_terms_id) from okc_Review_upld_terms
3420 where object_id = rev_terms.object_id
3421 and object_type = 'ARTICLE'
3422 and document_Type = p_document_type and document_id = p_document_id
3423 )
3424 and document_Type = p_document_type and document_id = p_document_id
3425 and object_type = 'ARTICLE' ;
3426
3427
3428 update okc_Review_upld_terms rev_terms
3429 set action = 'ADDED',
3430 article_id = null,
3431 article_version_id = null,
3432 object_id = null
3433 where rev_terms.review_upld_terms_id > (select min(review_upld_terms_id) from okc_Review_upld_terms
3434 where object_id = rev_terms.object_id
3435 and object_type = 'SECTION'
3436 and document_Type = p_document_type and document_id = p_document_id)
3437 and document_Type = p_document_type and document_id = p_document_id
3438 and object_type = 'SECTION';
3439
3440
3441
3442 UPDATE OKC_REVIEW_UPLD_TERMS
3443 SET ACTION='ADDED'
3444 WHERE OBJECT_ID IS NULL
3445 AND DOCUMENT_ID = P_DOCUMENT_ID
3446 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3447 AND ACTION IS NULL
3448 AND object_type IN ('ARTICLE','SECTION');
3449 --AND NEW_PARENT_ID IS NOT NULL;
3450
3451 UPDATE OKC_REVIEW_UPLD_TERMS UT
3452 SET ACTION='ADDED'
3453 WHERE ACTION IS NULL
3454 AND DOCUMENT_ID = P_DOCUMENT_ID
3455 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3456 AND NEW_PARENT_ID IS NOT NULL
3457 AND ((OBJECT_TYPE = 'ARTICLE'
3458 AND ( NOT EXISTS (SELECT 1
3459 FROM OKC_K_ARTICLES_B A
3460 WHERE A.ID = UT.OBJECT_ID
3461 AND A.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3462 AND A.DOCUMENT_ID = UT.DOCUMENT_ID) OR
3463 EXISTS (SELECT 1
3464 FROM OKC_K_ARTICLES_B A1
3465 WHERE A1.ID = UT.OBJECT_ID
3466 AND A1.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3467 AND A1.DOCUMENT_ID = UT.DOCUMENT_ID
3468 AND NVL(A1.AMENDMENT_OPERATION_CODE,'ZZZ') = 'DELETED') )
3469 ) OR
3470 (OBJECT_TYPE = 'SECTION'
3471 AND (NOT EXISTS (SELECT 1
3472 FROM OKC_SECTIONS_B S
3473 WHERE S.ID = UT.OBJECT_ID
3474 AND S.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3475 AND S.DOCUMENT_ID = UT.DOCUMENT_ID )
3476 OR
3477 EXISTS (SELECT 1
3478 FROM OKC_SECTIONS_B S1
3479 WHERE S1.ID = UT.OBJECT_ID
3480 AND S1.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3481 AND S1.DOCUMENT_ID = UT.DOCUMENT_ID
3482 AND NVL(S1.AMENDMENT_OPERATION_CODE,'ZZZ') = 'DELETED' ))
3483 ));
3484
3485 INSERT INTO OKC_REVIEW_UPLD_TERMS(
3486 REVIEW_UPLD_TERMS_ID,
3487 DOCUMENT_ID,
3488 DOCUMENT_TYPE,
3489 OBJECT_ID,
3490 OBJECT_TYPE,
3491 OBJECT_TITLE,
3492 OBJECT_TEXT,
3493 PARENT_OBJECT_TYPE,
3494 PARENT_ID,
3495 ARTICLE_ID,
3496 ARTICLE_VERSION_ID,
3497 OBJECT_VERSION_NUMBER,
3498 LABEL,
3499 DISPLAY_SEQ,
3500 ACTION,
3501 ERROR_MESSAGE_COUNT,
3502 WARNING_MESSAGE_COUNT,
3503 NEW_PARENT_ID,
3504 LAST_UPDATE_LOGIN,
3505 CREATED_BY,
3506 CREATION_DATE,
3507 LAST_UPDATED_BY,
3508 LAST_UPDATE_DATE)
3509 (SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL,
3510 KART.DOCUMENT_ID,
3511 KART.DOCUMENT_TYPE,
3512 KART.ID,
3513 'ARTICLE',
3514 nvl(VER.DISPLAY_NAME,ART.ARTICLE_TITLE),
3515 -- Fix for bug# 5223552. Fix for inserting Clause/Reference Text based on IBR flag in review tbl based for 'Deleted' Clause
3516 (SELECT to_clob(ver.reference_text) FROM dual WHERE Nvl(ver.insert_by_reference,'N') = 'Y'
3517 UNION ALL
3518 SELECT VER.ARTICLE_TEXT FROM dual WHERE Nvl(ver.insert_by_reference,'N') = 'N'
3519 ),
3520 'SECTION',
3521 KART.SCN_ID,
3522 KART.SAV_SAE_ID,
3523 KART.ARTICLE_VERSION_ID,
3524 1,
3525 KART.LABEL,
3526 KART.DISPLAY_SEQUENCE,
3527 'DELETED',
3528 NULL,
3529 NULL,
3530 (SELECT REVIEW_UPLD_TERMS_ID
3531 FROM OKC_REVIEW_UPLD_TERMS PARENT
3532 WHERE PARENT.OBJECT_ID = KART.SCN_ID
3533 AND PARENT.DOCUMENT_TYPE = p_document_type
3534 AND PARENT.DOCUMENT_ID = p_document_id),
3535 -- KART.SCN_ID,
3536 FND_GLOBAL.LOGIN_ID,
3537 FND_GLOBAL.USER_ID,
3538 SYSDATE,
3539 FND_GLOBAL.USER_ID,
3540 SYSDATE
3541 FROM OKC_K_ARTICLES_B KART,
3542 OKC_ARTICLES_ALL ART,
3543 OKC_ARTICLE_VERSIONS VER
3544 WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
3545 AND ART.ARTICLE_ID = VER.ARTICLE_ID
3546 AND KART.ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
3547 AND KART.DOCUMENT_ID = P_DOCUMENT_ID
3548 AND KART.DOCUMENT_TYPE = P_DOCUMENT_TYPE
3549 AND NOT EXISTS (SELECT 1
3550 FROM OKC_REVIEW_UPLD_TERMS UT
3551 WHERE UT.OBJECT_ID = KART.ID
3552 AND UT.OBJECT_TYPE = 'ARTICLE'
3553 AND UT.DOCUMENT_ID = P_DOCUMENT_ID
3554 AND UT.DOCUMENT_TYPE = P_DOCUMENT_TYPE));
3555
3556
3557 INSERT INTO OKC_REVIEW_UPLD_TERMS(
3558 REVIEW_UPLD_TERMS_ID,
3559 DOCUMENT_ID,
3560 DOCUMENT_TYPE,
3561 OBJECT_ID,
3562 OBJECT_TYPE,
3563 OBJECT_TITLE,
3564 OBJECT_TEXT,
3565 PARENT_OBJECT_TYPE,
3566 PARENT_ID,
3567 ARTICLE_ID,
3568 ARTICLE_VERSION_ID,
3569 OBJECT_VERSION_NUMBER,
3570 LABEL,
3571 DISPLAY_SEQ,
3572 ACTION,
3573 ERROR_MESSAGE_COUNT,
3574 WARNING_MESSAGE_COUNT,
3575 NEW_PARENT_ID,
3576 LAST_UPDATE_LOGIN,
3577 CREATED_BY,
3578 CREATION_DATE,
3579 LAST_UPDATED_BY,
3580 LAST_UPDATE_DATE)
3581 (SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL,
3582 SCN.DOCUMENT_ID,
3583 SCN.DOCUMENT_TYPE,
3584 SCN.ID,
3585 'SECTION',
3586 SCN.HEADING,
3587 NULL,
3588 DECODE(SCN.SCN_ID,NULL,p_document_type,'SECTION'),
3589 SCN.SCN_ID,
3590 NULL,
3591 NULL,
3592 1,
3593 SCN.LABEL,
3594 SCN.SECTION_SEQUENCE,
3595 'DELETED',
3596 NULL,
3597 NULL,
3598 -- SCN.SCN_ID,
3599 DECODE(SCN.SCN_ID, NULL,l_rev_id_for_doc,
3600 p_document_id, l_rev_id_for_doc,
3601 (SELECT REVIEW_UPLD_TERMS_ID
3602 FROM OKC_REVIEW_UPLD_TERMS PARENT
3603 WHERE PARENT.OBJECT_ID = SCN.SCN_ID
3604 AND PARENT.DOCUMENT_TYPE = p_document_type
3605 AND PARENT.DOCUMENT_ID = p_document_id)
3606 ),
3607 FND_GLOBAL.LOGIN_ID,
3608 FND_GLOBAL.USER_ID,
3609 SYSDATE,
3610 FND_GLOBAL.USER_ID,
3611 SYSDATE
3612 FROM OKC_SECTIONS_B SCN
3613 WHERE SCN.DOCUMENT_ID = P_DOCUMENT_ID
3614 AND SCN.DOCUMENT_TYPE = P_DOCUMENT_TYPE
3615 AND NOT EXISTS (SELECT 1
3616 FROM OKC_REVIEW_UPLD_TERMS UT
3617 WHERE UT.OBJECT_ID = SCN.ID
3618 AND UT.DOCUMENT_TYPE = p_document_type
3619 AND UT.DOCUMENT_ID = p_document_id
3620 AND UT.OBJECT_TYPE = 'SECTION'));
3621
3622
3623 for del_csr in unresolved_del_rec loop
3624 UPDATE OKC_REVIEW_UPLD_TERMS REV
3625 SET NEW_PARENT_ID = (SELECT REVIEW_UPLD_TERMS_ID
3626 FROM OKC_REVIEW_UPLD_TERMS PARENT
3627 WHERE PARENT.OBJECT_ID = REV.PARENT_ID
3628 AND REV.REVIEW_UPLD_TERMS_id = del_csr.REVIEW_UPLD_TERMS_id
3629 AND PARENT.DOCUMENT_TYPE = p_document_type
3630 AND PARENT.document_id = p_document_id
3631 )
3632 WHERE
3633 REV.REVIEW_UPLD_TERMS_id = del_csr.REVIEW_UPLD_TERMS_id;
3634 end loop;
3635
3636
3637 -- 2 way word sync with clause edit begins
3638 l_prof_value := OKC_WORD_DOWNLOAD_UPLOAD.GET_WORD_SYNC_PROFILE;
3639 IF l_prof_value = 'Y' THEN
3640
3641 UPDATE OKC_REVIEW_UPLD_TERMS UT
3642 SET ACTION='UPDATED',
3643 DISPLAY_SEQ = (select KART.display_sequence
3644 FROM OKC_K_ARTICLES_B KART
3645 WHERE KART.ID = UT.OBJECT_ID)
3646 WHERE ACTION IS NULL
3647 AND DOCUMENT_ID = P_DOCUMENT_ID
3648 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3649 AND OBJECT_TYPE = 'ARTICLE'
3650 AND EXISTS
3651 (
3652 SELECT 1
3653 FROM OKC_K_ARTICLES_B KART,
3654 OKC_ARTICLES_ALL ART,
3655 OKC_ARTICLE_VERSIONS VER
3656 WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
3657 AND ART.ARTICLE_ID = VER.ARTICLE_ID
3658 AND KART.ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
3659 AND KART.ID = UT.OBJECT_ID
3660 AND KART.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3661 AND KART.DOCUMENT_ID = UT.DOCUMENT_ID
3662
3663 AND (
3664 (NVL(VER.DISPLAY_NAME,ART.ARTICLE_TITLE) <> to_char(UT.OBJECT_TITLE)) OR
3665 (EXISTS (SELECT 1 FROM OKC_WORD_SYNC_T WHERE DOC_ID = P_DOCUMENT_ID AND DOC_TYPE = P_DOCUMENT_TYPE AND CAT_ID = UT.OBJECT_ID AND action = 'UPDATEDASSIGNED'))));
3666 -- ('UPDATED' = (SELECT ACTION FROM OKC_WORD_SYNC_T WHERE DOC_ID = P_DOCUMENT_ID AND DOC_TYPE = P_DOCUMENT_TYPE AND CAT_ID = UT.OBJECT_ID))));
3667
3668
3669 ELSE
3670
3671 UPDATE OKC_REVIEW_UPLD_TERMS UT
3672 SET ACTION='UPDATED',
3673 DISPLAY_SEQ = (select KART.display_sequence
3674 FROM OKC_K_ARTICLES_B KART
3675 WHERE KART.ID = UT.OBJECT_ID)
3676 WHERE ACTION IS NULL
3677 AND DOCUMENT_ID = P_DOCUMENT_ID
3678 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3679 AND OBJECT_TYPE = 'ARTICLE'
3680 AND EXISTS
3681 (
3682 SELECT 1
3683 FROM OKC_K_ARTICLES_B KART,
3684 OKC_ARTICLES_ALL ART,
3685 OKC_ARTICLE_VERSIONS VER
3686 WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
3687 AND ART.ARTICLE_ID = VER.ARTICLE_ID
3688 AND KART.ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
3689 AND KART.ID = UT.OBJECT_ID
3690 AND KART.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3691 AND KART.DOCUMENT_ID = UT.DOCUMENT_ID
3692
3693 AND (
3694 (NVL(VER.DISPLAY_NAME,ART.ARTICLE_TITLE) <> to_char(UT.OBJECT_TITLE)) OR
3695 (NVL(ver.insert_by_reference,'N') = 'Y' AND
3696 NVL(dbms_lob.compare(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ut.object_text,'&NBSP;| ',' '),
3697 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3698 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>|
3699 <STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3700 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3701 |<HR>|</HR>|<hr>|</hr>|<br>|</br>|<BR>|</BR>|<br/>|<BR/>|([[:cntrl:]])',''),unistr('\00a0'),' '),' ',''),
3702 '&|&|<Palign="justify">|<Palign="both">|<Palign="center">|<divalign="both">|<divalign="left">|<Palign="left">|<Palign="right">','') ,
3703 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ver.reference_text,'&NBSP;| ',' '),
3704 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3705 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>
3706 |<STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3707 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3708 |<HR>|</HR>|<hr>|</hr>|<br>|</br>|<BR>|</BR>|<br/>|<BR/>|([[:cntrl:]])',''),unistr('\00a0'),' '),' ',''),
3709 '&|&|<Palign="justify">|<Palign="both">|<Palign="center">|<divalign="both">|<Palign="left">|<Palign="right">|<ULtype="disc">||<ultype="disc">','')),-1) <> 0) OR
3710 (NVL(ver.insert_by_reference,'N') <> 'Y' AND
3711 NVL(dbms_lob.compare(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ut.object_text,'&NBSP;| ',' '),
3712 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3713 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>|
3714 <STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3715 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3716 |<HR>|</HR>|<hr>|</hr>|<br>|</br>|<BR>|</BR>|<br/>|<BR/>|([[:cntrl:]])',''),unistr('\00a0'),' '),' ',''),
3717 '&|&|<Palign="justify">|<Palign="both">|<Palign="center">|<divalign="both">|<divalign="left">|<Palign="left">|<Palign="right">','') ,
3718 regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ver.article_text,'&NBSP;| ',' '),
3719 '<DIV>|<div>|<P>|<p>|<B>|<b>|</DIV>|</div>|</P>|</p>|</B>|</b>|<STRONG>|<strong>|</STRONG>|</strong>
3720 |<A>|<a>|</A>|</a>|<H3>|<h3>|</H3>|</h3>|<H2>|<h2>|</H2>|</h2>|<H1>|<h1>|</H1>|</h1>|<S>|<s>|</S>|</s>
3721 |<STRIKE>|<strike>|</STRIKE>|</strike>|<I>|<i>|</I>|</i>|<EM>|<em>|</EM>|</em>|<U>|<u>|</U>|</u>
3722 |<BLOCKQUOTE>|</BLOCKQUOTE>|<blockquote>|</blockquote>|<OL>|</OL>|<ol>|</ol>|<LI>|</LI>|<li>|</li>|<UL>|</UL>|<ul>|</ul>
3723 |<HR>|</HR>|<hr>|</hr>|<br>|</br>|<BR>|</BR>|<br/>|<BR/>|([[:cntrl:]])',''),unistr('\00a0'),' '),' ',''),
3724 '&|&|<Palign="justify">|<Palign="both">|<Palign="center">|<divalign="both">|<Palign="left">|<Palign="right">|<ULtype="disc">||<ultype="disc">','')),-1) <> 0)));
3725
3726 END IF;
3727 -- 2 way word sync with clause edit ends
3728
3729 UPDATE OKC_REVIEW_UPLD_TERMS UT
3730 SET ACTION='UPDATED',
3731 DISPLAY_SEQ = (SELECT SECTION_SEQUENCE FROM OKC_SECTIONS_B SCN
3732 WHERE SCN.ID = UT.OBJECT_ID
3733 AND to_char(UT.OBJECT_TITLE) <> SCN.HEADING)
3734 WHERE ACTION IS NULL
3735 AND DOCUMENT_ID = P_DOCUMENT_ID
3736 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3737 AND OBJECT_TYPE = 'SECTION'
3738 AND EXISTS (SELECT 1
3739 FROM OKC_SECTIONS_B SCN
3740 WHERE SCN.ID = UT.OBJECT_ID
3741 AND SCN.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3742 AND SCN.DOCUMENT_ID = UT.DOCUMENT_ID
3743
3744 AND to_char(UT.OBJECT_TITLE) <> SCN.HEADING);
3745
3746 UPDATE OKC_REVIEW_UPLD_TERMS UT
3747 SET NON_STANDARD_FLAG = 'Y'
3748 WHERE DOCUMENT_ID = P_DOCUMENT_ID
3749 AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
3750 AND OBJECT_TYPE = 'ARTICLE'
3751 AND EXISTS (SELECT 1
3752 FROM OKC_K_ARTICLES_B KART,
3753 OKC_ARTICLES_ALL ART
3754 WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
3755 AND KART.ID = UT.OBJECT_ID
3756 AND KART.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
3757 AND KART.DOCUMENT_ID = UT.DOCUMENT_ID
3758
3759 AND ART.STANDARD_YN = 'N');
3760
3761 open clauses_without_parent_exist;
3762 fetch clauses_without_parent_exist into l_clauses_no_parent_exist;
3763 if (clauses_without_parent_exist%FOUND) THEN
3764 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3765 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'120: Creating Unassgined Section ');
3766 END IF;
3767 open unassigned_section_exists;
3768 fetch unassigned_section_exists into l_unassigned_scn_id;
3769 if(unassigned_section_exists%NOTFOUND)THEN
3770 create_unassigned_section(p_api_version => 1,
3771 p_commit => FND_API.G_FALSE,
3772 p_document_type => p_document_type,
3773 p_document_id => p_document_id,
3774 p_new_parent_id => l_rev_id_for_doc,
3775 x_scn_id => l_unassigned_scn_id,
3776 x_return_status => x_return_status,
3777 x_msg_count => x_msg_count,
3778 x_msg_data => x_msg_data
3779 );
3780 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3781 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'130: l_unassigned_scn_id : '||l_unassigned_scn_id);
3782 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'130: Cannot Create Unassgined Section : '||x_msg_data||' Status '||x_return_status);
3783 END IF;
3784
3785 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3787 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3788 RAISE FND_API.G_EXC_ERROR ;
3789 END IF;
3790 end if;
3791 close unassigned_section_exists;
3792 end if;
3793 close clauses_without_parent_exist;
3794 for clause_no_parent_csr in clauses_without_parent_id loop
3795 update okc_REVIEW_UPLD_TERMS
3796 set new_parent_id = l_unassigned_scn_id, action = 'ADDED'
3797 where REVIEW_UPLD_TERMS_id = clause_no_parent_csr.REVIEW_UPLD_TERMS_id;
3798 end loop;
3799
3800 for clauses_moved_no_section_csr in clauses_no_parent_id_and_moved loop
3801 update OKC_REVIEW_UPLD_TERMS
3802 set new_parent_id = l_unassigned_scn_id
3803 where REVIEW_UPLD_TERMS_id =clauses_moved_no_section_csr.REVIEW_UPLD_TERMS_id;
3804 end loop;
3805
3806
3807 l_message_name := 'OKC_REVIEW_VAR_VAL_CHG';
3808 for var_csr in variable_values_changed loop
3809 update OKC_REVIEW_VAR_VALUES
3810 set changed = 'Y'
3811 where REVIEW_UPLD_TERMS_id = var_csr.REVIEW_UPLD_TERMS_id;
3812
3813 if(var_csr.variable_type = 'U') then
3814 if(var_csr.variable_value_id is null) then
3815 l_message_name := 'OKC_REVIEW_VAR_VAL_KNW_CHG';
3816 else
3817 l_message_name := 'OKC_REVIEW_VAR_MAY_CHG';
3818 end if;
3819 else
3820 l_message_name := 'OKC_REVIEW_VAR_MAY_CHG';
3821 end if;
3822
3823 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3824 -- and variable_code
3825 insert into okc_review_messages (
3826 review_messages_id,
3827 REVIEW_UPLD_TERMS_id,
3828 error_severity,
3829 message_name,
3830 object_version_number,
3831 variable_code
3832 )
3833 (select okc_review_messages_s1.nextval review_messages_id,
3834 var_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3835 'W' error_severity,l_message_name ,1 object_version_number, var_csr.variable_code from dual
3836 where not exists (select 1 from okc_review_messages where review_upld_terms_id = var_csr.review_upld_terms_id
3837 and variable_code = var_csr.variable_code and message_name = l_message_name));
3838
3839 end loop;
3840
3841 l_message_name := 'OKC_REVIEW_VAR_VAL_CHG';
3842 for valid_var_added_csr in valid_variable_added(l_intent) loop
3843 -- Fix for bug# 5229387. Clauses should be marked as 'UPDATED' only if action is null
3844 update okc_review_upld_terms
3845 set action='UPDATED'
3846 where review_upld_terms_id = valid_var_added_csr.review_upld_terms_id and action is null;
3847
3848 update OKC_REVIEW_VAR_VALUES
3849 set changed = 'A'
3850 where REVIEW_UPLD_TERMS_id = valid_var_added_csr.REVIEW_UPLD_TERMS_id;
3851
3852 if(valid_var_added_csr.modified='Y') then
3853 l_message_name := 'OKC_REVIEW_VAR_ADDED';
3854 else
3855 l_message_name := 'OKC_REVIEW_VAR_ADD_NO_VAL';
3856 end if;
3857
3858 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3859 -- and variable_code
3860 insert into okc_review_messages (
3861 review_messages_id,
3862 REVIEW_UPLD_TERMS_id,
3863 error_severity,
3864 message_name,
3865 object_version_number,
3866 variable_code
3867 )
3868 (select okc_review_messages_s1.nextval review_messages_id,
3869 valid_var_added_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3870 'W' error_severity, l_message_name ,1 object_version_number, valid_var_added_csr.variable_code from dual
3871 where not exists (select 1 from okc_review_messages where review_upld_terms_id = valid_var_added_csr.review_upld_terms_id
3872 and variable_code = valid_var_added_csr.variable_code and message_name = l_message_name));
3873 end loop;
3874
3875 for valid_var_new_clause_csr in valid_var_new_clause(l_intent) loop
3876
3877 update OKC_REVIEW_VAR_VALUES
3878 set changed = 'A'
3879 where REVIEW_UPLD_TERMS_id = valid_var_new_clause_csr.REVIEW_UPLD_TERMS_id;
3880
3881 if(valid_var_new_clause_csr.modified='Y') then
3882 l_message_name := 'OKC_REVIEW_VAR_ADDED';
3883 else
3884 l_message_name := 'OKC_REVIEW_VAR_ADD_NO_VAL';
3885 end if;
3886
3887 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3888 -- and variable_code
3889 insert into okc_review_messages (
3890 review_messages_id,
3891 REVIEW_UPLD_TERMS_id,
3892 error_severity,
3893 message_name,
3894 object_version_number,
3895 variable_code
3896 )
3897 (select okc_review_messages_s1.nextval review_messages_id,
3898 valid_var_new_clause_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3899 'W' error_severity, l_message_name ,1 object_version_number, valid_var_new_clause_csr.variable_code from dual
3900 where not exists (select 1 from okc_review_messages where review_upld_terms_id = valid_var_new_clause_csr.review_upld_terms_id
3901 and variable_code = valid_var_new_clause_csr.variable_code and message_name = l_message_name));
3902 end loop;
3903
3904 for valid_new_var_ibr_csr in valid_new_var_ibr(l_intent) loop
3905
3906 update OKC_REVIEW_VAR_VALUES
3907 set changed = 'A'
3908 where REVIEW_UPLD_TERMS_id = valid_new_var_ibr_csr.REVIEW_UPLD_TERMS_id;
3909
3910 if(valid_new_var_ibr_csr.modified='Y') then
3911 l_message_name := 'OKC_REVIEW_VAR_ADDED';
3912 else
3913 l_message_name := 'OKC_REVIEW_VAR_ADD_NO_VAL';
3914 end if;
3915
3916 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3917 -- and variable_code
3918 insert into okc_review_messages (
3919 review_messages_id,
3920 REVIEW_UPLD_TERMS_id,
3921 error_severity,
3922 message_name,
3923 object_version_number,
3924 variable_code
3925 )
3926 (select okc_review_messages_s1.nextval review_messages_id,
3927 valid_new_var_ibr_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3928 'W' error_severity, l_message_name ,1 object_version_number, valid_new_var_ibr_csr.variable_code from dual
3929 where not exists (select 1 from okc_review_messages where review_upld_terms_id = valid_new_var_ibr_csr.review_upld_terms_id
3930 and variable_code = valid_new_var_ibr_csr.variable_code and message_name = l_message_name));
3931 end loop;
3932
3933 for removed_var_csr in variables_removed loop
3934 -- Fix for bug# 5229387. Clauses should be marked as 'UPDATED' only if action is null
3935 update okc_review_upld_terms
3936 set action='UPDATED'
3937 where review_upld_terms_id = removed_var_csr.review_upld_terms_id and action is null;
3938
3939 insert into okc_review_var_values(
3940 review_var_values_id,
3941 review_upld_terms_id,
3942 variable_name,
3943 language,
3944 variable_code,
3945 object_version_number,
3946 changed)
3947 (select okc_review_var_values_s1.nextval,
3948 removed_var_csr.review_upld_terms_id,
3949 removed_var_csr.variable_name,
3950 removed_var_csr.language,
3951 removed_var_csr.variable_code,
3952 1,
3953 'D' from dual);
3954
3955 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3956 -- and variable_code
3957 insert into okc_review_messages (
3958 review_messages_id,
3959 REVIEW_UPLD_TERMS_id,
3960 error_severity,
3961 message_name,
3962 object_version_number,
3963 variable_code
3964 )
3965 (select okc_review_messages_s1.nextval review_messages_id,
3966 removed_var_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3967 'W' error_severity,'OKC_REVIEW_VAR_REMOVED' message_name ,1 object_version_number, removed_var_csr.variable_code from dual
3968 where not exists (select 1 from okc_review_messages where review_upld_terms_id = removed_var_csr.review_upld_terms_id
3969 and variable_code = removed_var_csr.variable_code and message_name = 'OKC_REVIEW_VAR_REMOVED'));
3970 end loop;
3971
3972 l_message_name := 'OKC_REVIEW_INVALID_VARIABLE';
3973 for invalid_var_added_csr in invalid_variable_added(l_intent) loop
3974 -- Fix for bug# 5229387. Clauses should be marked as 'UPDATED' only if action is null
3975 update okc_review_upld_terms
3976 set action='UPDATED'
3977 where review_upld_terms_id = invalid_var_added_csr.review_upld_terms_id and action is null;
3978
3979 -- Insert the message only if the same message does not exist for that review_upld_terms_id
3980 -- and variable_code
3981 insert into okc_review_messages (
3982 review_messages_id,
3983 REVIEW_UPLD_TERMS_id,
3984 error_severity,
3985 message_name,
3986 object_version_number,
3987 variable_code
3988 )
3989 (select okc_review_messages_s1.nextval review_messages_id,
3990 invalid_var_added_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
3991 'W' error_severity, l_message_name ,1 object_version_number, invalid_var_added_csr.variable_code from dual
3992 where not exists (select 1 from okc_review_messages where review_upld_terms_id = invalid_var_added_csr.review_upld_terms_id
3993 and variable_code = invalid_var_added_csr.variable_code and message_name = l_message_name));
3994 end loop;
3995
3996 -- Delete the duplicate variable rows for the given document_type, document_id
3997 delete from okc_review_var_values revvar
3998 where revvar.rowid > (select min(rowid) from okc_review_var_values
3999 where review_upld_terms_id = revvar.review_upld_terms_id
4000 and revvar.variable_code = variable_code)
4001 and exists(select 1 from okc_Review_upld_terms rev
4002 where revvar.review_upld_terms_id = rev.review_upld_terms_id
4003 and rev.document_type = p_document_type
4004 and rev.document_id = p_document_id);
4005
4006
4007 -- 2 way word sync with clause edit begins
4008 l_prof_value := OKC_WORD_DOWNLOAD_UPLOAD.GET_WORD_SYNC_PROFILE;
4009 IF l_prof_value = 'Y' THEN
4010 NULL;
4011 ELSE
4012
4013 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4014 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5000: Begin: Logging the ones that were determined as UPDATED');
4015 for upd_csr in get_updated_articles_csr loop
4016 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5010: for review_upld_terms_id=' || upd_csr.review_upld_terms_id );
4017 end loop;
4018 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6000: End: Logging the ones that were determined as UPDATED');
4019 END IF;
4020
4021 END IF;
4022 -- 2 way word sync with clause edit ends
4023
4024 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4025 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: After delete review terms');
4026 END IF;
4027 l_clause_title := OKC_TERMS_UTIL_PVT.Get_Message('OKC','OKC_TERMS_DUMMY_CLAUSE');
4028 l_section_title := OKC_TERMS_UTIL_PVT.Get_Message('OKC','OKC_TERMS_DUMMY_SECTION');
4029 l_clause_counter:= 1;
4030 l_section_counter := 1;
4031 for emp_csr in empty_title_csr loop
4032 if(emp_csr.object_type = 'ARTICLE') then
4033 update okc_REVIEW_UPLD_TERMS
4034 set object_title = l_clause_title || l_clause_counter
4035 where REVIEW_UPLD_TERMS_id = emp_csr.REVIEW_UPLD_TERMS_id;
4036 l_clause_counter := l_clause_counter + 1;
4037 end if;
4038 if(emp_csr.object_type = 'SECTION') then
4039 update okc_REVIEW_UPLD_TERMS
4040 set object_title = l_section_title || l_section_counter
4041 where REVIEW_UPLD_TERMS_id = emp_csr.REVIEW_UPLD_TERMS_id;
4042 l_section_counter := l_section_counter + 1;
4043 end if;
4044 end loop;
4045
4046
4047 update okc_review_upld_terms rev
4048 set action = 'DELETED',
4049 object_title = (select nvl(ver.display_name,art. article_title)
4050 from OKC_K_ARTICLES_B kart, OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
4051 where kart.id = rev.object_id
4052 and kart.sav_sae_id = art.article_id
4053 and kart.article_version_id = ver.article_version_id
4054 and art.article_id = ver.article_id)
4055 ,object_text = (select ver.article_text from OKC_K_ARTICLES_B kart, OKC_ARTICLE_VERSIONS VER
4056 where kart.id = rev.object_id
4057 and kart.article_Version_id = ver.article_version_id)
4058
4059 where document_type = p_document_type
4060 and document_id = p_document_id
4061 and object_type = 'ARTICLE'
4062 and action = 'UPDATED'
4063 and (DBMS_LOB.getlength(object_text)=0 OR object_text is null);
4064
4065 for ibr_csr in is_article_ibr(l_user_access) loop
4066 insert into okc_review_messages (
4067 review_messages_id,
4068 REVIEW_UPLD_TERMS_id,
4069 error_severity,
4070 message_name,
4071 object_version_number
4072
4073 )
4074 (select okc_review_messages_s1.nextval review_messages_id,
4075 ibr_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
4076 'W' error_severity,'OKC_ARTICLE_UPDT_IBR' message_name ,1 object_version_number from dual);
4077 end loop;
4078
4079 for mandatory_csr in is_article_mandatory(l_user_access) loop
4080 insert into okc_review_messages (
4081 review_messages_id,
4082 REVIEW_UPLD_TERMS_id,
4083 error_severity,
4084 message_name,
4085 object_version_number
4086
4087 )
4088 (select okc_review_messages_s1.nextval review_messages_id,
4089 mandatory_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
4090 'W' error_severity,'OKC_ARTICLE_IS_MANDATORY' message_name ,1 object_version_number from dual);
4091 end loop;
4092
4093 for lock_csr in is_article_text_locked(l_user_access) loop
4094 insert into okc_review_messages (
4095 review_messages_id,
4096 REVIEW_UPLD_TERMS_id,
4097 error_severity,
4098 message_name,
4099 object_version_number
4100
4101 )
4102 (select okc_review_messages_s1.nextval review_messages_id,
4103 lock_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
4104 'W' error_severity,'OKC_ARTICLE_UPDT_LOCK' message_name ,1 object_version_number from dual);
4105 end loop;
4106
4107 l_message_name := 'OKC_UPLOAD_CLAUSE_TITLE_LONG';
4108 for title_csr in check_sec_clause_title loop
4109 if(title_csr.object_Type='SECTION') then
4110 l_message_name := 'OKC_UPLOAD_SECTION_NAME_LONG';
4111 end if;
4112 if(title_csr.object_type= 'ARTICLE') then
4113 l_message_name := 'OKC_UPLOAD_CLAUSE_TITLE_LONG';
4114 end if;
4115 insert into okc_review_messages (
4116 review_messages_id,
4117 REVIEW_UPLD_TERMS_id,
4118 error_severity,
4119 message_name,
4120 object_version_number
4121
4122 )
4123 (select okc_review_messages_s1.nextval review_messages_id,
4124 title_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
4125 'W' error_severity, l_message_name,1 object_version_number from dual);
4126
4127 end loop;
4128 for upd_csr in update_err_warn_csr loop
4129 if (upd_csr.error_severity = 'E') then
4130 update okc_review_upld_terms
4131 set error_message_count = upd_csr.err_warn_count
4132 where review_upld_terms_id = upd_csr.review_upld_terms_id;
4133 end if;
4134 if (upd_csr.error_severity = 'W') then
4135 update okc_review_upld_terms
4136 set warning_message_count = upd_csr.err_warn_count
4137 where review_upld_terms_id = upd_csr.review_upld_terms_id;
4138 end if;
4139 end loop;
4140 OKC_TEMPLATE_USAGES_GRP.update_template_usages(
4141 p_api_version => l_api_version,
4142 p_init_msg_list => p_init_msg_list ,
4143 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4144 p_commit => FND_API.G_FALSE,
4145 x_return_status => x_return_status,
4146 x_msg_count => x_msg_count,
4147 x_msg_data => x_msg_data,
4148 p_document_type => p_document_type,
4149 p_document_id => p_document_id,
4150 p_lock_terms_flag => 'Y',
4151 p_locked_by_user_id => FND_GLOBAL.user_id);
4152
4153 open current_num_scheme;
4154 fetch current_num_scheme into l_doc_num_scheme;
4155 close current_num_scheme;
4156
4157 curr_disp_seqs.DELETE;
4158 del_new_parents.DELETE;
4159 del_disp_seqs.DELETE;
4160
4161 FOR rec in parent_ids_csr LOOP
4162 curr_disp_seqs(rec.review_upld_terms_id) := 0;
4163 END LOOP;
4164
4165 IF curr_disp_seqs.COUNT > 0 THEN
4166 curr_disp_seqs(-999) := 0;
4167 END IF;
4168 OPEN deleted_terms_csr;
4169 FETCH deleted_terms_csr BULK COLLECT INTO
4170 del_new_parents,del_disp_seqs;
4171 CLOSE deleted_terms_csr;
4172
4173 l_sequence := 0;
4174 FOR disp_rec in terms_disp_csr LOOP
4175 IF disp_rec.object_type IN ('ARTICLE','SECTION') THEN
4176 l_sequence := curr_disp_seqs(NVL(disp_rec.new_parent_id,-999)) + 10;
4177
4178 IF del_new_parents.COUNT > 0 THEN
4179 FOR i in del_new_parents.FIRST .. del_new_parents.LAST LOOP
4180 IF (NVL(del_new_parents(i),-999) = NVL(disp_rec.new_parent_id,-999) AND
4181 del_disp_seqs(i) = l_sequence) THEN
4182 l_sequence := l_sequence + 10;
4183 EXIT;
4184 END IF;
4185 END LOOP;
4186 END IF;
4187
4188 UPDATE okc_review_upld_terms
4189 SET display_seq = l_sequence
4190 WHERE review_upld_terms_id = disp_rec.review_upld_terms_id;
4191
4192 curr_disp_seqs(NVL(disp_rec.new_parent_id,-999)) := l_sequence;
4193 END IF;
4194
4195 END LOOP;
4196
4197
4198 for upd_csr in update_err_warn_csr loop
4199 if (upd_csr.error_severity = 'E') then
4200 update okc_review_upld_terms
4201 set error_message_count = upd_csr.err_warn_count
4202 where review_upld_terms_id = upd_csr.review_upld_terms_id;
4203 end if;
4204 if (upd_csr.error_severity = 'W') then
4205 update okc_review_upld_terms
4206 set warning_message_count = upd_csr.err_warn_count
4207 where review_upld_terms_id = upd_csr.review_upld_terms_id;
4208 end if;
4209 end loop;
4210 if(l_doc_num_scheme is NOT NULL) then
4211 OKC_NUMBER_SCHEME_GRP.apply_num_scheme_4_review(
4212 p_api_version => p_api_version,
4213 p_init_msg_list => p_init_msg_list,
4214 x_return_status => x_return_status,
4215 x_msg_count => x_msg_count,
4216 x_msg_data => x_msg_data,
4217 p_commit => p_commit,
4218 p_validation_string => p_validation_string,
4219 p_doc_type => p_document_type,
4220 p_doc_id => p_document_id,
4221 p_num_scheme_id => l_doc_num_scheme);
4222 end if;
4223 -- Standard check of p_commit
4224 IF FND_API.To_Boolean( p_commit ) THEN
4225 COMMIT WORK;
4226 END IF;
4227
4228 EXCEPTION
4229 WHEN FND_API.G_EXC_ERROR THEN
4230 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4231 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Reject_Changes: OKC_API.G_EXCEPTION_ERROR Exception');
4232 END IF;
4233 ROLLBACK TO g_reject_changes;
4234 x_return_status := G_RET_STS_ERROR ;
4235 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
4236
4237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4238 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4239 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Reject_Changes: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4240 END IF;
4241 ROLLBACK TO g_reject_Changes;
4242 x_return_status := G_RET_STS_UNEXP_ERROR ;
4243 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
4244
4245 WHEN OTHERS THEN
4246 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4247 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving reject_changes because of EXCEPTION: '||sqlerrm);
4248 END IF;
4249
4250 ROLLBACK TO g_reject_changes;
4251 x_return_status := G_RET_STS_UNEXP_ERROR ;
4252 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4253 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4254 END IF;
4255 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
4256 END Sync_Review_Tables;
4257
4258 /*
4259 -- PROCEDURE Create_Unassigned_Section
4260 -- creating un-assigned sections in a document in okc_REVIEW_UPLD_TERMS table
4261 */
4262 PROCEDURE Create_Unassigned_Section (
4263 p_api_version IN NUMBER,
4264 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4265 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4266
4267 x_return_status OUT NOCOPY VARCHAR2,
4268 x_msg_data OUT NOCOPY VARCHAR2,
4269 x_msg_count OUT NOCOPY NUMBER,
4270
4271 p_document_type IN VARCHAR2,
4272 p_document_id IN NUMBER,
4273 p_new_parent_id IN NUMBER,
4274
4275 x_scn_id OUT NOCOPY NUMBER
4276 ) IS
4277 l_api_version CONSTANT NUMBER := 1;
4278 l_api_name CONSTANT VARCHAR2(30) := 'Create_Unassigned_Section';
4279 l_meaning VARCHAR2(100);
4280 l_sequence NUMBER;
4281 l_scn_id NUMBER;
4282 l_temp_id NUMBER;
4283 Cursor l_get_max_seq_csr IS
4284 SELECT nvl(max(display_seq),0)+10
4285 FROM OKC_REVIEW_UPLD_TERMS
4286 WHERE DOCUMENT_TYPE= p_document_type
4287 AND DOCUMENT_ID = p_document_id
4288 AND PARENT_ID IS NULL or new_parent_id = p_document_id;
4289
4290 BEGIN
4291 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4292 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7500: Entered Create_Unassigned_Section');
4293 END IF;
4294 -- Standard Start of API savepoint
4295 SAVEPOINT g_Create_Unassigned_Section;
4296 -- Standard call to check for call compatibility.
4297 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4299 END IF;
4300 -- Initialize message list if p_init_msg_list is set to TRUE.
4301 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4302 FND_MSG_PUB.initialize;
4303 END IF;
4304 -- Initialize API return status to success
4305 x_return_status := G_RET_STS_SUCCESS;
4306
4307 --------------------------------------------
4308 -- Call Simple API of okc_sections_b with following input
4309 -- doc_type=p_doc_type, doc_id=p_doc_id, scn_code=G_UNASSIGNED_SECTION_CODE,
4310 -- heading = < get meaning of G_UNASSIGNED_SECTION_CODE by quering fnd_lookups>.
4311 -- Set x_scn_id to id returned by simpel API.
4312 --------------------------------------------
4313 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4314 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7600: Calling Simple API to Create a Section');
4315 END IF;
4316 --------------------------------------------
4317 l_meaning := Okc_Util.Decode_Lookup('OKC_ARTICLE_SECTION',G_UNASSIGNED_SECTION_CODE);
4318
4319 --Bug 3669528 Unassigned section should always come at the bottom, so use a 'high' value
4320 /*
4321 OPEN l_get_max_seq_csr;
4322 FETCH l_get_max_seq_csr INTO l_sequence;
4323 CLOSE l_get_max_seq_csr;
4324 */
4325 l_sequence:= 9999;
4326 x_return_status := Get_Seq_Id(
4327 p_REVIEW_UPLD_TERMS_id => l_temp_id,
4328 x_REVIEW_UPLD_TERMS_id => l_scn_id
4329 );
4330 --- If any errors happen abort API
4331 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4333 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4334 RAISE FND_API.G_EXC_ERROR;
4335 END IF;
4336 x_scn_id := l_scn_id;
4337 INSERT INTO OKC_REVIEW_UPLD_TERMS(
4338 REVIEW_UPLD_TERMS_ID,
4339 DOCUMENT_ID,
4340 DOCUMENT_TYPE,
4341 OBJECT_ID,
4342 OBJECT_TYPE,
4343 OBJECT_TITLE,
4344 OBJECT_TEXT,
4345 PARENT_OBJECT_TYPE,
4346 PARENT_ID,
4347 ARTICLE_ID,
4348 ARTICLE_VERSION_ID,
4349 OBJECT_VERSION_NUMBER,
4350 LABEL,
4351 DISPLAY_SEQ,
4352 ACTION,
4353 ERROR_MESSAGE_COUNT,
4354 WARNING_MESSAGE_COUNT,
4355 NEW_PARENT_ID,
4356 LAST_UPDATE_LOGIN,
4357 CREATED_BY,
4358 CREATION_DATE,
4359 LAST_UPDATED_BY,
4360 LAST_UPDATE_DATE)
4361 (SELECT l_scn_id,
4362 P_DOCUMENT_ID,
4363 P_DOCUMENT_TYPE,
4364 NULL,
4365 'SECTION',
4366 l_meaning,
4367 NULL,
4368 p_document_type,
4369 p_document_id,
4370 NULL,
4371 NULL,
4372 1,
4373 NULL,
4374 l_sequence,
4375 'ADDED',
4376 NULL,
4377 NULL,
4378 p_new_parent_id,
4379 FND_GLOBAL.LOGIN_ID,
4380 FND_GLOBAL.USER_ID,
4381 SYSDATE,
4382 FND_GLOBAL.USER_ID,
4383 SYSDATE
4384 FROM dual);
4385
4386 --------------------------------------------
4387 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4389 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4390 RAISE FND_API.G_EXC_ERROR ;
4391 END IF;
4392 --------------------------------------------
4393
4394 IF FND_API.To_Boolean( p_commit ) THEN
4395 COMMIT WORK;
4396 END IF;
4397 -- Standard call to get message count and if count is 1, get message info.
4398 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4399 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4400 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7700: Leaving Create_Unassigned_Section');
4401 END IF;
4402 EXCEPTION
4403 WHEN FND_API.G_EXC_ERROR THEN
4404 ROLLBACK TO g_Create_Unassigned_Section;
4405 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4406 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Create_Unassigned_Section : OKC_API.G_EXCEPTION_ERROR Exception');
4407 END IF;
4408
4409 IF l_get_max_seq_csr%ISOPEN THEN
4410 CLOSE l_get_max_seq_csr;
4411 END IF;
4412
4413 x_return_status := G_RET_STS_ERROR ;
4414 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4415
4416 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4417 ROLLBACK TO g_Create_Unassigned_Section;
4418 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4419 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Create_Unassigned_Section : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4420 END IF;
4421
4422 IF l_get_max_seq_csr%ISOPEN THEN
4423 CLOSE l_get_max_seq_csr;
4424 END IF;
4425
4426 x_return_status := G_RET_STS_UNEXP_ERROR ;
4427 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4428
4429 WHEN OTHERS THEN
4430 ROLLBACK TO g_Create_Unassigned_Section;
4431 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4432 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Create_Unassigned_Section because of EXCEPTION: '||sqlerrm);
4433 END IF;
4434
4435 IF l_get_max_seq_csr%ISOPEN THEN
4436 CLOSE l_get_max_seq_csr;
4437 END IF;
4438
4439 x_return_status := G_RET_STS_UNEXP_ERROR ;
4440 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4441 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4442 END IF;
4443 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4444 END Create_Unassigned_Section ;
4445
4446
4447 END OKC_REVIEW_UPLD_TERMS_PVT;