[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_SECTIONS_GRP
Source
1 PACKAGE BODY OKC_TERMS_SECTIONS_GRP AS
2 /* $Header: OKCGSCNB.pls 120.1 2005/10/11 02:44:44 ndoddi noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 ---------------------------------------------------------------------------
11 -- GLOBAL VARIABLES
12 ---------------------------------------------------------------------------
13 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_SECTIONS_GRP';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21
22 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
23 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
24 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
25
26 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
27 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
28 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
29 G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
30 G_AMEND_CODE_ADDED CONSTANT VARCHAR2(30) := 'ADDED';
31 G_AMEND_CODE_UPDATED CONSTANT VARCHAR2(30) := 'UPDATED';
32
33 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
34 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
35 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
36
37 ---------------------------------------
38 -- PROCEDURE validate_row --
39 ---------------------------------------
40 PROCEDURE validate_row(
41 p_api_version IN NUMBER,
42 p_init_msg_list IN VARCHAR2 ,
43 p_validation_level IN NUMBER ,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2,
47 p_id IN NUMBER,
48 p_section_sequence IN NUMBER,
49 p_label IN VARCHAR2,
50 p_scn_id IN NUMBER,
51 p_heading IN VARCHAR2,
52 p_description IN VARCHAR2,
53 p_document_type IN VARCHAR2,
54 p_document_id IN NUMBER,
55 p_scn_code IN VARCHAR2,
56 p_amendment_description IN VARCHAR2,
57 p_amendment_operation_code IN VARCHAR2,
58 p_orig_system_reference_code IN VARCHAR2,
59 p_orig_system_reference_id1 IN NUMBER,
60 p_orig_system_reference_id2 IN NUMBER,
61 p_print_yn IN VARCHAR2,
62 p_attribute_category IN VARCHAR2,
63 p_attribute1 IN VARCHAR2,
64 p_attribute2 IN VARCHAR2,
65 p_attribute3 IN VARCHAR2,
66 p_attribute4 IN VARCHAR2,
67 p_attribute5 IN VARCHAR2,
68 p_attribute6 IN VARCHAR2,
69 p_attribute7 IN VARCHAR2,
70 p_attribute8 IN VARCHAR2,
71 p_attribute9 IN VARCHAR2,
72 p_attribute10 IN VARCHAR2,
73 p_attribute11 IN VARCHAR2,
74 p_attribute12 IN VARCHAR2,
75 p_attribute13 IN VARCHAR2,
76 p_attribute14 IN VARCHAR2,
77 p_attribute15 IN VARCHAR2,
78 p_object_version_number IN NUMBER
79 ) IS
80 l_api_version CONSTANT NUMBER := 1;
81 l_api_name CONSTANT VARCHAR2(30) := 'g_validate_row';
82
83 BEGIN
84
85 /*IF (l_debug = 'Y') THEN
86 okc_debug.log('100: Entered validate_row', 2);
87 END IF;*/
88
89 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
90 FND_LOG.STRING(G_PROC_LEVEL,
91 G_PKG_NAME, '100: Entered validate_row' );
92 END IF;
93
94 -- Standard Start of API savepoint
95 SAVEPOINT g_validate_row_GRP;
96 -- Standard call to check for call compatibility.
97 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
98 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 END IF;
100 -- Initialize message list if p_init_msg_list is set to TRUE.
101 IF FND_API.to_Boolean( p_init_msg_list ) THEN
102 FND_MSG_PUB.initialize;
103 END IF;
104 -- Initialize API return status to success
105 x_return_status := FND_API.G_RET_STS_SUCCESS;
106
107 --------------------------------------------
108 -- Calling Simple API for Validation
109 --------------------------------------------
110 OKC_TERMS_SECTIONS_PVT.Validate_Row(
111 p_validation_level => p_validation_level,
112 x_return_status => x_return_status,
113 p_id => p_id,
114 p_section_sequence => p_section_sequence,
115 p_label => p_label,
116 p_scn_id => p_scn_id,
117 p_heading => p_heading,
118 p_description => p_description,
119 p_document_type => p_document_type,
120 p_document_id => p_document_id,
121 p_scn_code => p_scn_code,
122 p_amendment_description => p_amendment_description,
123 p_amendment_operation_code => p_amendment_operation_code,
124 p_orig_system_reference_code => p_orig_system_reference_code,
125 p_orig_system_reference_id1 => p_orig_system_reference_id1,
126 p_orig_system_reference_id2 => p_orig_system_reference_id2,
127 p_print_yn => p_print_yn,
128 p_attribute_category => p_attribute_category,
129 p_attribute1 => p_attribute1,
130 p_attribute2 => p_attribute2,
131 p_attribute3 => p_attribute3,
132 p_attribute4 => p_attribute4,
133 p_attribute5 => p_attribute5,
134 p_attribute6 => p_attribute6,
135 p_attribute7 => p_attribute7,
136 p_attribute8 => p_attribute8,
137 p_attribute9 => p_attribute9,
138 p_attribute10 => p_attribute10,
139 p_attribute11 => p_attribute11,
140 p_attribute12 => p_attribute12,
141 p_attribute13 => p_attribute13,
142 p_attribute14 => p_attribute14,
143 p_attribute15 => p_attribute15,
144 p_object_version_number => p_object_version_number
145 );
146 --------------------------------------------
147 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
149 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
150 RAISE FND_API.G_EXC_ERROR ;
151 END IF;
152 --------------------------------------------
153
154 -- Standard call to get message count and if count is 1, get message info.
155 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
156
157 /*IF (l_debug = 'Y') THEN
158 okc_debug.log('200: Leaving validate_row', 2);
159 END IF;*/
160
161 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
162 FND_LOG.STRING(G_PROC_LEVEL,
163 G_PKG_NAME, '200: Leaving validate_row' );
164 END IF;
165
166 EXCEPTION
167 WHEN FND_API.G_EXC_ERROR THEN
168 /*IF (l_debug = 'Y') THEN
169 okc_debug.log('300: Leaving Validate_Row: OKC_API.G_EXCEPTION_ERROR Exception', 2);
170 END IF;*/
171
172 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
173 FND_LOG.STRING(G_EXCP_LEVEL,
174 G_PKG_NAME, '300: Leaving Validate_Row: OKC_API.G_EXCEPTION_ERROR Exception' );
175 END IF;
176 ROLLBACK TO g_validate_row_GRP;
177 x_return_status := G_RET_STS_ERROR ;
178 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
179
180 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
181 /*IF (l_debug = 'Y') THEN
182 okc_debug.log('400: Leaving Validate_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
183 END IF;*/
184
185 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
186 FND_LOG.STRING(G_EXCP_LEVEL,
187 G_PKG_NAME, '400: Leaving Validate_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
188 END IF;
189 ROLLBACK TO g_validate_row_GRP;
190 x_return_status := G_RET_STS_UNEXP_ERROR ;
191 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
192
193 WHEN OTHERS THEN
194 /*IF (l_debug = 'Y') THEN
195 okc_debug.log('500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
196 END IF;*/
197
198 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
199 FND_LOG.STRING(G_EXCP_LEVEL,
200 G_PKG_NAME, '500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm);
201 END IF;
202 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
203 p_msg_name => G_UNEXPECTED_ERROR,
204 p_token1 => G_SQLCODE_TOKEN,
205 p_token1_value => sqlcode,
206 p_token2 => G_SQLERRM_TOKEN,
207 p_token2_value => sqlerrm);
208
209 ROLLBACK TO g_validate_row_GRP;
210 x_return_status := G_RET_STS_UNEXP_ERROR ;
211 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
212 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
213 END IF;
214 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
215
216 END validate_row;
217
218 -------------------------------------
219 -- PROCEDURE create_section
220 -------------------------------------
221 PROCEDURE create_section(
222 p_api_version IN NUMBER,
223 p_init_msg_list IN VARCHAR2 ,
224 p_validation_level IN NUMBER,
225 p_commit IN VARCHAR2,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229 p_mode IN VARCHAR2 , --'AMEND' or 'NORMAL'
230 p_id IN NUMBER,
231 p_section_sequence IN NUMBER,
232 p_label IN VARCHAR2,
233 p_scn_id IN NUMBER,
234 p_heading IN VARCHAR2,
235 p_description IN VARCHAR2,
236 p_document_type IN VARCHAR2,
237 p_document_id IN NUMBER,
238 p_scn_code IN VARCHAR2,
239 p_amendment_description IN VARCHAR2,
240 p_orig_system_reference_code IN VARCHAR2,
241 p_orig_system_reference_id1 IN NUMBER,
242 p_orig_system_reference_id2 IN NUMBER,
243 p_print_yn IN VARCHAR2,
244 p_attribute_category IN VARCHAR2,
245 p_attribute1 IN VARCHAR2,
246 p_attribute2 IN VARCHAR2,
247 p_attribute3 IN VARCHAR2,
248 p_attribute4 IN VARCHAR2,
249 p_attribute5 IN VARCHAR2,
250 p_attribute6 IN VARCHAR2,
251 p_attribute7 IN VARCHAR2,
252 p_attribute8 IN VARCHAR2,
253 p_attribute9 IN VARCHAR2,
254 p_attribute10 IN VARCHAR2,
255 p_attribute11 IN VARCHAR2,
256 p_attribute12 IN VARCHAR2,
257 p_attribute13 IN VARCHAR2,
258 p_attribute14 IN VARCHAR2,
259 p_attribute15 IN VARCHAR2,
260 x_id OUT NOCOPY NUMBER
261
262 ) IS
263
264 l_api_version CONSTANT NUMBER := 1;
265 l_api_name CONSTANT VARCHAR2(30) := 'g_create_section';
266 l_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE := 1;
267 l_amendment_operation_code OKC_SECTIONS_B.AMENDMENT_OPERATION_CODE%TYPE;
268 l_summary_amend_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
269 l_amendment_description OKC_SECTIONS_B.AMENDMENT_DESCRIPTION%TYPE;
270
271 BEGIN
272
273 /*IF (l_debug = 'Y') THEN
274 okc_debug.log('600: Entered create_section', 2);
275 END IF;*/
276
277 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
278 FND_LOG.STRING(G_PROC_LEVEL,
279 G_PKG_NAME, '600: Entered create_section' );
280 END IF;
281
282 -- Standard Start of API savepoint
283 SAVEPOINT g_create_section_GRP;
284 -- Standard call to check for call compatibility.
285 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288 -- Initialize message list if p_init_msg_list is set to TRUE.
289 IF FND_API.to_Boolean( p_init_msg_list ) THEN
290 FND_MSG_PUB.initialize;
291 END IF;
292
293 -- Initialize API return status to success
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295
296 IF p_mode='AMEND' THEN
297 l_amendment_description := p_amendment_description;
298 l_amendment_operation_code := G_AMEND_CODE_ADDED;
299 l_summary_amend_operation_code := OKC_TERMS_UTIL_PVT.get_summary_amend_code(p_existing_summary_code =>NULL,
300 p_existing_operation_code=>NULL,
301 p_amend_operation_code=>G_AMEND_CODE_ADDED);
302 ELSE
303 l_amendment_description := NULL;
304 l_amendment_operation_code := NULL;
305 l_summary_amend_operation_code := NULL;
306 END IF;
307 --------------------------------------------
308 -- Calling Simple API for Creating A Row
309 --------------------------------------------
310 OKC_TERMS_SECTIONS_PVT.insert_row(
311 p_validation_level => p_validation_level,
312 x_return_status => x_return_status,
313 p_id => p_id,
314 p_section_sequence => p_section_sequence,
315 p_label => p_label,
316 p_scn_id => p_scn_id,
317 p_heading => p_heading,
318 p_description => p_description,
319 p_document_type => p_document_type,
320 p_document_id => p_document_id,
321 p_scn_code => p_scn_code,
322 p_amendment_description => l_amendment_description,
323 p_amendment_operation_code => l_amendment_operation_code,
324 p_summary_amend_operation_code => l_summary_amend_operation_code,
325 p_orig_system_reference_code => p_orig_system_reference_code,
326 p_orig_system_reference_id1 => p_orig_system_reference_id1,
327 p_orig_system_reference_id2 => p_orig_system_reference_id2,
328 p_print_yn => p_print_yn,
329 p_attribute_category => p_attribute_category,
330 p_attribute1 => p_attribute1,
331 p_attribute2 => p_attribute2,
332 p_attribute3 => p_attribute3,
333 p_attribute4 => p_attribute4,
334 p_attribute5 => p_attribute5,
335 p_attribute6 => p_attribute6,
336 p_attribute7 => p_attribute7,
337 p_attribute8 => p_attribute8,
338 p_attribute9 => p_attribute9,
339 p_attribute10 => p_attribute10,
340 p_attribute11 => p_attribute11,
341 p_attribute12 => p_attribute12,
342 p_attribute13 => p_attribute13,
343 p_attribute14 => p_attribute14,
344 p_attribute15 => p_attribute15,
345 x_id => x_id
346 );
347 --------------------------------------------
348 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
350 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
351 RAISE FND_API.G_EXC_ERROR ;
352 END IF;
353 --------------------------------------------
354
355 -- Standard check of p_commit
356 IF FND_API.To_Boolean( p_commit ) THEN
357 COMMIT WORK;
358 END IF;
359 -- Standard call to get message count and if count is 1, get message info.
360 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
361
362 /*IF (l_debug = 'Y') THEN
363 okc_debug.log('700: Leaving create_section', 2);
364 END IF;*/
365
366 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
367 FND_LOG.STRING(G_PROC_LEVEL,
368 G_PKG_NAME, '700: Leaving create_section' );
369 END IF;
370
371 EXCEPTION
372 WHEN FND_API.G_EXC_ERROR THEN
373 /*IF (l_debug = 'Y') THEN
374 okc_debug.log('800: Leaving create_section: OKC_API.G_EXCEPTION_ERROR Exception', 2);
375 END IF;*/
376
377 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
378 FND_LOG.STRING(G_EXCP_LEVEL,
379 G_PKG_NAME, '800: Leaving create_section: OKC_API.G_EXCEPTION_ERROR Exception');
380 END IF;
381 ROLLBACK TO g_create_section_GRP;
382 x_return_status := G_RET_STS_ERROR ;
383 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
384
385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
386 /*IF (l_debug = 'Y') THEN
387 okc_debug.log('900: Leaving create_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
388 END IF;*/
389
390 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
391 FND_LOG.STRING(G_EXCP_LEVEL,
392 G_PKG_NAME, '900: Leaving create_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
393 END IF;
394 ROLLBACK TO g_create_section_GRP;
395 x_return_status := G_RET_STS_UNEXP_ERROR ;
396 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
397
398 WHEN OTHERS THEN
399 /*IF (l_debug = 'Y') THEN
400 okc_debug.log('1000: Leaving create_section because of EXCEPTION: '||sqlerrm, 2);
401 END IF;*/
402
403 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
404 FND_LOG.STRING(G_EXCP_LEVEL,
405 G_PKG_NAME, '1000: Leaving create_section because of EXCEPTION: '||sqlerrm);
406 END IF;
407 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
408 p_msg_name => G_UNEXPECTED_ERROR,
409 p_token1 => G_SQLCODE_TOKEN,
410 p_token1_value => sqlcode,
411 p_token2 => G_SQLERRM_TOKEN,
412 p_token2_value => sqlerrm);
413
414 ROLLBACK TO g_create_section_GRP;
415 x_return_status := G_RET_STS_UNEXP_ERROR ;
416 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
417 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
418 END IF;
419 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
420
421 END create_section;
422
423 --------------------------------------------------------------------------
424 -- PROCEDURE update_section
425 ---------------------------------------------------------------------------
426 PROCEDURE update_section(
427 p_api_version IN NUMBER,
428 p_init_msg_list IN VARCHAR2 ,
429 p_validation_level IN NUMBER,
430 p_validate_commit IN VARCHAR2,
431 p_validation_string IN VARCHAR2,
432 p_commit IN VARCHAR2,
433 x_return_status OUT NOCOPY VARCHAR2,
434 x_msg_count OUT NOCOPY NUMBER,
435 x_msg_data OUT NOCOPY VARCHAR2,
436 p_mode IN VARCHAR2 , --'AMEND' or 'NORMAL'
437 p_id IN NUMBER,
438 p_section_sequence IN NUMBER,
439 p_label IN VARCHAR2,
440 p_scn_id IN NUMBER,
441 p_heading IN VARCHAR2,
442 p_description IN VARCHAR2,
443 p_document_type IN VARCHAR2,
444 p_document_id IN NUMBER,
445 p_scn_code IN VARCHAR2,
446 p_amendment_description IN VARCHAR2,
447 p_orig_system_reference_code IN VARCHAR2,
448 p_orig_system_reference_id1 IN NUMBER,
449 p_orig_system_reference_id2 IN NUMBER,
450 p_print_yn IN VARCHAR2,
451 p_attribute_category IN VARCHAR2,
452 p_attribute1 IN VARCHAR2,
453 p_attribute2 IN VARCHAR2,
454 p_attribute3 IN VARCHAR2,
455 p_attribute4 IN VARCHAR2,
456 p_attribute5 IN VARCHAR2,
457 p_attribute6 IN VARCHAR2,
458 p_attribute7 IN VARCHAR2,
459 p_attribute8 IN VARCHAR2,
460 p_attribute9 IN VARCHAR2,
461 p_attribute10 IN VARCHAR2,
462 p_attribute11 IN VARCHAR2,
463 p_attribute12 IN VARCHAR2,
464 p_attribute13 IN VARCHAR2,
465 p_attribute14 IN VARCHAR2,
466 p_attribute15 IN VARCHAR2,
467 p_object_version_number IN NUMBER
468
469 ) IS
470
471 l_api_version CONSTANT NUMBER := 1;
472 l_api_name CONSTANT VARCHAR2(30) := 'g_update_section';
473 l_ok_to_commit VARCHAR2(1);
474 l_document_id NUMBER;
475 l_document_type VARCHAR2(30);
476 l_scn_id okc_sections_b.id%type;
477 l_cat_id okc_k_articles_b.id%type;
478 l_ovn okc_sections_b.object_version_number%type;
479 l_amendment_operation_code OKC_SECTIONS_B.AMENDMENT_OPERATION_CODE%TYPE;
480 l_summary_amend_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
481 l_existing_summary_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
482 l_existing_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
483 l_amendment_description OKC_SECTIONS_B.AMENDMENT_DESCRIPTION%TYPE;
484
485 CURSOR l_document_id_csr IS
486 SELECT DOCUMENT_ID,DOCUMENT_TYPE FROM OKC_SECTIONS_B
487 WHERE ID=P_ID;
488
489 CURSOR l_get_summary_code_csr IS
490 SELECT SUMMARY_AMEND_OPERATION_CODE ,amendment_operation_code
491 FROM OKC_SECTIONS_B
492 WHERE ID=P_ID;
493
494 BEGIN
495
496 /*IF (l_debug = 'Y') THEN
497 okc_debug.log('1600: Entered update_section', 2);
498 END IF;*/
499
500 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
501 FND_LOG.STRING(G_PROC_LEVEL,
502 G_PKG_NAME, '1600: Entered update_section' );
503 END IF;
504
505 -- Standard Start of API savepoint
506 SAVEPOINT g_update_section_GRP;
507
508 -- Standard call to check for call compatibility.
509 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511 END IF;
512
513 -- Initialize message list if p_init_msg_list is set to TRUE.
514 IF FND_API.to_Boolean( p_init_msg_list ) THEN
515 FND_MSG_PUB.initialize;
516 END IF;
517
518 IF FND_API.To_Boolean( p_validate_commit ) THEN
519
520 IF p_document_id IS NULL OR p_document_type IS NULL THEN
521 OPEN l_document_id_csr;
522 FETCH l_document_id_csr into l_document_id,l_document_type;
523 CLOSE l_document_id_csr;
524 END IF;
525
526 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
527 p_api_version => l_api_version,
528 p_init_msg_list => FND_API.G_FALSE,
529 p_doc_type => l_document_type,
530 p_doc_id => l_document_id,
531 p_validation_string => p_validation_string,
532 x_return_status => x_return_status,
533 x_msg_data => x_msg_data,
534 x_msg_count => x_msg_count) ) THEN
535
536 /*IF (l_debug = 'Y') THEN
537 okc_debug.log('700: Issue with document header Record.Cannot commit', 2);
538 END IF;*/
539
540 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
541 FND_LOG.STRING(G_PROC_LEVEL,
542 G_PKG_NAME, '700: Issue with document header Record.Cannot commit' );
543 END IF;
544 RAISE FND_API.G_EXC_ERROR ;
545 END IF;
546 END IF;
547
548
549 -- Initialize API return status to success
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551
552 IF p_mode='AMEND' THEN
553 l_amendment_description := p_amendment_description;
554
555 OPEN l_get_summary_code_csr;
556 FETCH l_get_summary_code_csr INTO l_existing_summary_code,l_existing_operation_code;
557 CLOSE l_get_summary_code_csr;
558
559 l_amendment_operation_code := nvl(l_existing_operation_code,G_AMEND_CODE_UPDATED);
560 l_summary_amend_operation_code := OKC_TERMS_UTIL_PVT.get_summary_amend_code(p_existing_summary_code =>l_existing_summary_code,
561 p_existing_operation_code=>l_existing_operation_code,
562 p_amend_operation_code=>G_AMEND_CODE_UPDATED);
563
564 ELSE
565 l_amendment_description := NULL;
566 l_amendment_operation_code := NULL;
567 l_summary_amend_operation_code := NULL;
568 END IF;
569
570 --------------------------------------------
571 -- Calling Simple API for Updating A Row
572 --------------------------------------------
573
574 OKC_TERMS_SECTIONS_PVT.update_row(
575 p_validation_level => p_validation_level,
576 x_return_status => x_return_status,
577 p_id => p_id,
578 p_section_sequence => p_section_sequence,
579 p_label => p_label,
580 p_scn_id => p_scn_id,
581 p_heading => p_heading,
582 p_description => p_description,
583 p_document_type => p_document_type,
584 p_document_id => p_document_id,
585 p_scn_code => p_scn_code,
586 p_amendment_description => l_amendment_description,
587 p_amendment_operation_code => l_amendment_operation_code,
588 p_summary_amend_operation_code => l_summary_amend_operation_code,
589 p_orig_system_reference_code => p_orig_system_reference_code,
590 p_orig_system_reference_id1 => p_orig_system_reference_id1,
591 p_orig_system_reference_id2 => p_orig_system_reference_id2,
592 p_print_yn => p_print_yn,
593 p_attribute_category => p_attribute_category,
594 p_attribute1 => p_attribute1,
595 p_attribute2 => p_attribute2,
596 p_attribute3 => p_attribute3,
597 p_attribute4 => p_attribute4,
598 p_attribute5 => p_attribute5,
599 p_attribute6 => p_attribute6,
600 p_attribute7 => p_attribute7,
601 p_attribute8 => p_attribute8,
602 p_attribute9 => p_attribute9,
603 p_attribute10 => p_attribute10,
604 p_attribute11 => p_attribute11,
605 p_attribute12 => p_attribute12,
606 p_attribute13 => p_attribute13,
607 p_attribute14 => p_attribute14,
608 p_attribute15 => p_attribute15,
609 p_object_version_number => p_object_version_number
610 );
611 --------------------------------------------
612 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
614 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
615 RAISE FND_API.G_EXC_ERROR ;
616 END IF;
617 --------------------------------------------
618
619 -- Standard check of p_commit
620 IF FND_API.To_Boolean( p_commit ) THEN
621 COMMIT WORK;
622 END IF;
623 -- Standard call to get message count and if count is 1, get message info.
624 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
625
626 /*IF (l_debug = 'Y') THEN
627 okc_debug.log('1800: Leaving update_section', 2);
628 END IF;*/
629
630 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
631 FND_LOG.STRING(G_PROC_LEVEL,
632 G_PKG_NAME, '1800: Leaving update_section' );
633 END IF;
634
635 EXCEPTION
636 WHEN FND_API.G_EXC_ERROR THEN
637 /*IF (l_debug = 'Y') THEN
638 okc_debug.log('1900: Leaving update_section: OKC_API.G_EXCEPTION_ERROR Exception', 2);
639 END IF;*/
640
641 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
642 FND_LOG.STRING(G_EXCP_LEVEL,
643 G_PKG_NAME, '1900: Leaving update_section: OKC_API.G_EXCEPTION_ERROR Exception' );
644 END IF;
645
646 IF l_document_id_csr%ISOPEN THEN
647 CLOSE l_document_id_csr;
648 END IF;
649
650 ROLLBACK TO g_update_section_GRP;
651 x_return_status := G_RET_STS_ERROR ;
652 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
653
654 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
655 /*IF (l_debug = 'Y') THEN
656 okc_debug.log('2000: Leaving update_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
657 END IF;*/
658
659 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
660 FND_LOG.STRING(G_EXCP_LEVEL,
661 G_PKG_NAME, '2000: Leaving update_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
662 END IF;
663
664 IF l_document_id_csr%ISOPEN THEN
665 CLOSE l_document_id_csr;
666 END IF;
667
668 ROLLBACK TO g_update_section_GRP;
669 x_return_status := G_RET_STS_UNEXP_ERROR ;
670 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
671
672 WHEN OTHERS THEN
673 /*IF (l_debug = 'Y') THEN
674 okc_debug.log('2100: Leaving update_section because of EXCEPTION: '||sqlerrm, 2);
675 END IF;*/
676
677 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
678 FND_LOG.STRING(G_EXCP_LEVEL,
679 G_PKG_NAME, '2100: Leaving update_section because of EXCEPTION: '||sqlerrm );
680 END IF;
681 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
682 p_msg_name => G_UNEXPECTED_ERROR,
683 p_token1 => G_SQLCODE_TOKEN,
684 p_token1_value => sqlcode,
685 p_token2 => G_SQLERRM_TOKEN,
686 p_token2_value => sqlerrm);
687
688 IF l_document_id_csr%ISOPEN THEN
689 CLOSE l_document_id_csr;
690 END IF;
691
692 ROLLBACK TO g_update_section_GRP;
693
694 x_return_status := G_RET_STS_UNEXP_ERROR ;
695 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
696 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
697 END IF;
698 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
699
700 END update_section;
701
702 -------------------------------------------------------
703 -- PROCEDURE add_section :: To be called from UI
704 -------------------------------------------------------
705 PROCEDURE add_section(
706 p_api_version IN NUMBER,
707 p_init_msg_list IN VARCHAR2 ,
708 p_validation_level IN NUMBER ,
709 p_validate_commit IN VARCHAR2 ,
710 p_validation_string IN VARCHAR2,
711 p_commit IN VARCHAR2 ,
712 p_mode IN VARCHAR2 , -- 'NORMAL' or 'AMEND'
713 x_return_status OUT NOCOPY VARCHAR2,
714 x_msg_count OUT NOCOPY NUMBER,
715 x_msg_data OUT NOCOPY VARCHAR2,
716 p_id IN NUMBER,
717 p_ref_scn_id IN NUMBER ,
718 p_ref_point IN VARCHAR2 ,
719 p_heading IN VARCHAR2,
720 p_description IN VARCHAR2,
721 p_document_type IN VARCHAR2,
722 p_document_id IN NUMBER,
723 p_scn_code IN VARCHAR2,
724 p_print_yn IN VARCHAR2 ,
725 p_amendment_description IN VARCHAR2 ,
726 p_attribute_category IN VARCHAR2 ,
727 p_attribute1 IN VARCHAR2 ,
728 p_attribute2 IN VARCHAR2 ,
729 p_attribute3 IN VARCHAR2 ,
730 p_attribute4 IN VARCHAR2 ,
731 p_attribute5 IN VARCHAR2,
732 p_attribute6 IN VARCHAR2 ,
733 p_attribute7 IN VARCHAR2 ,
734 p_attribute8 IN VARCHAR2 ,
735 p_attribute9 IN VARCHAR2 ,
736 p_attribute10 IN VARCHAR2 ,
737 p_attribute11 IN VARCHAR2 ,
738 p_attribute12 IN VARCHAR2 ,
739 p_attribute13 IN VARCHAR2 ,
740 p_attribute14 IN VARCHAR2 ,
741 p_attribute15 IN VARCHAR2 ,
742 x_id OUT NOCOPY NUMBER
743 ) IS
744
745 l_api_version CONSTANT NUMBER := 1;
746 l_api_name CONSTANT VARCHAR2(30) := 'g_add_section';
747 l_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE := 1;
748 l_ok_to_commit VARCHAR2(1);
749 i NUMBER := 0;
750 l_ref_count NUMBER := 0;
751 l_ref_sequence NUMBER := 0;
752 l_ref_sequence1 NUMBER := 0;
753 l_sequence NUMBER := 0;
754 l_ref_is_set BOOLEAN := FALSE;
755 l_scn_id NUMBER ;
756
757 --Fix for bug 3735048, added object_type
758 TYPE scn_rec_type IS RECORD (
759 id OKC_SECTIONS_B.id%type,
760 display_sequence OKC_SECTIONS_B.section_sequence%type,
761 object_version_number OKC_SECTIONS_B.object_version_number%type,
762 object_type VARCHAR2(30)
763 );
764
765 TYPE scn_tbl_type IS TABLE OF scn_rec_type INDEX BY BINARY_INTEGER;
766 scn_tbl scn_tbl_type;
767
768 --Fix for bug 3735048, added UNION ALL to add articles
769 --Also added object_type
770 -- Cursor to get sequence of all child section of a section
771 CURSOR l_get_scn_csr(b_scn_id NUMBER) IS
772 SELECT id,
773 'SECTION' object_type,
774 object_version_number,
775 section_sequence display_sequence,
776 scn_id
777 FROM okc_sections_b
778 WHERE document_type=p_document_type
779 AND document_id = p_document_id
780 AND ( (b_scn_id IS NOT NULL AND scn_id=b_scn_id) OR
781 (b_scn_id IS NULL AND scn_id IS NULL)
782 )
783 UNION ALL
784 SELECT id,
785 'ARTICLE' object_type,
786 object_version_number,
787 display_sequence,
788 scn_id
789 FROM okc_k_articles_b
790 WHERE document_type=p_document_type
791 AND document_id = p_document_id
792 AND ( (b_scn_id IS NOT NULL AND scn_id=b_scn_id) OR
793 (b_scn_id IS NULL AND scn_id IS NULL)
794 )
795 ORDER BY display_sequence;
796
797 -- cursor to get paret of reference scn_id
798 CURSOR l_get_parent_csr IS
799 select scn_id from okc_sections_b
800 where document_type=p_document_type
801 AND document_id = p_document_id
802 AND id=p_ref_scn_id;
803
804
805 -- Cursor to get sequence of last section in a hierarchy
806 cursor l_get_max_seq_csr(b_scn_id NUMBER) is
807 SELECT nvl(max(SECTION_SEQUENCE),0) FROM OKC_SECTIONS_B
808 WHERE document_type=p_document_type
809 AND document_id=p_document_id
810 and (
811 (b_scn_id is Null and scn_id is Null)
812 OR
813 (b_scn_id is Not Null and scn_id=b_scn_id));
814 CURSOR l_get_max_art_seq(b_scn_id NUMBER) IS
815 SELECT nvl(max(DISPLAY_SEQUENCE),0) FROM OKC_K_ARTICLES_B
816 WHERE document_type=p_document_type
817 AND document_id=p_document_id
818 and scn_id =b_scn_id;
819
820 CURSOR l_get_child_art_crs(b_scn_id NUMBER,b_ref_sequence NUMBER) IS
821 SELECT id,object_version_number
822 FROM OKC_K_ARTICLES_B
823 WHERE SCN_ID=b_scn_id
824 AND display_sequence >= b_ref_sequence
825 Order by display_sequence;
826
827
828
829 BEGIN
830
831
832 /*IF (l_debug = 'Y') THEN
833 okc_debug.log('600: Entered add_section', 2);
834 END IF;*/
835
836 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
837 FND_LOG.STRING(G_PROC_LEVEL,
838 G_PKG_NAME, '600: Entered add_section' );
839 END IF;
840
841 -- Standard Start of API savepoint
842 SAVEPOINT g_add_section_GRP;
843 -- Standard call to check for call compatibility.
844 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846 END IF;
847 -- Initialize message list if p_init_msg_list is set to TRUE.
848 IF FND_API.to_Boolean( p_init_msg_list ) THEN
849 FND_MSG_PUB.initialize;
850 END IF;
851
852 IF FND_API.To_Boolean( p_validate_commit ) AND
853 NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
854 p_api_version => l_api_version,
855 p_init_msg_list => FND_API.G_FALSE,
856 p_doc_type => p_document_type,
857 p_doc_id => p_document_id,
858 p_validation_string => p_validation_string,
859 x_return_status => x_return_status,
860 x_msg_data => x_msg_data,
861 x_msg_count => x_msg_count) ) THEN
862
863 /*IF (l_debug = 'Y') THEN
864 okc_debug.log('700: Issue with document header Record.Cannot commit', 2);
865 END IF;*/
866
867 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
868 FND_LOG.STRING(G_PROC_LEVEL,
869 G_PKG_NAME, '700: Issue with document header Record.Cannot commit' );
870 END IF;
871 RAISE FND_API.G_EXC_ERROR ;
872 END IF;
873
874
875 -- Initialize API return status to success
876 x_return_status := FND_API.G_RET_STS_SUCCESS;
877
878 IF p_ref_point NOT IN ('A','B','S') THEN
879 /*IF (l_debug = 'Y') THEN
880 okc_debug.log('800: Error: Ref point should be either A,B or S', 2);
881 END IF;*/
882
883 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
884 FND_LOG.STRING(G_PROC_LEVEL,
885 G_PKG_NAME, '800: Error: Ref point should be either A,B or S' );
886 END IF;
887
888 Okc_Api.Set_Message( G_FND_APP, 'OKC_WRONG_REF_POINT');
889 RAISE FND_API.G_EXC_ERROR ;
890
891 END IF;
892
893
894 IF p_ref_scn_id is NULL THEN
895 -- Case of Adding a section at TOP Level.This will be last section.
896
897 OPEN l_get_max_seq_csr(Null);
898 FETCH l_get_max_seq_csr into l_ref_sequence;
899 CLOSE l_get_max_seq_csr;
900 l_ref_is_set := TRUE;
901 l_scn_id := Null;
902 END IF;
903
904 IF p_ref_scn_id IS NOT NULL AND p_ref_point='S' THEN
905 -- Case of Adding a subsection at TOP Level.This will be last section in the heirarchy.
906 OPEN l_get_max_seq_csr(p_ref_scn_id);
907 FETCH l_get_max_seq_csr INTO l_ref_sequence;
908 CLOSE l_get_max_seq_csr;
909
910 OPEN l_get_max_art_seq(p_ref_scn_id);
911 FETCH l_get_max_art_seq INTO l_ref_sequence1;
912 CLOSE l_get_max_art_seq;
913
914 l_ref_is_set := TRUE;
915 l_scn_id := p_ref_scn_id;
916
917 IF l_ref_sequence1 > l_ref_sequence THEN
918 l_ref_sequence := l_ref_sequence1;
919 END IF;
920
921 END IF;
922
923 scn_tbl.delete;
924
925 IF NOT l_ref_is_set THEN
926 OPEN l_get_parent_csr;
927 FETCH l_get_parent_csr into l_scn_id;
928 CLOSE l_get_parent_csr ;
929
930 FOR l_scn_rec IN l_get_scn_csr(l_scn_id) LOOP
931
932 i := i +1;
933
934 scn_tbl(i).id := l_scn_rec.id;
935 scn_tbl(i).object_version_number := l_scn_rec.object_version_number;
936 scn_tbl(i).display_sequence := l_scn_rec.display_sequence;
937 scn_tbl(i).object_type := l_scn_rec.object_type;
938
939 -- Finding out reference below which subsection will be create
940
941 IF scn_tbl(i).id = p_ref_scn_id THEN
942 l_ref_count := i;
943 l_ref_sequence := scn_tbl(i).display_sequence;
944 l_ref_is_set := TRUE;
945 END IF;
946
947 END LOOP;
948 IF l_ref_is_set THEN
949 IF p_ref_point='B' THEN
950 l_ref_count := l_ref_count - 1 ;
951 IF l_ref_count=0 THEN
952 l_ref_sequence := 0;
953 ELSE
954 l_ref_sequence := nvl(scn_tbl(l_ref_count).display_sequence,0);
955 END IF;
956 END IF;
957 ELSE
958 /*IF (l_debug = 'Y') THEN
959 okc_debug.log('900: Error: Reference not found', 2);
960 END IF;*/
961
962 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
963 FND_LOG.STRING(G_PROC_LEVEL,
964 G_PKG_NAME, '900: Error: Reference not found' );
965 END IF;
966 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
967 END IF;
968 END IF;
969
970
971 l_sequence := l_ref_sequence + 10;
972 --------------------------------------------
973 -- Calling API for Creating A Section
974 --------------------------------------------
975 OKC_TERMS_SECTIONS_GRP.create_section(
976 p_api_version => 1,
977 p_init_msg_list => FND_API.G_FALSE,
978 p_commit => FND_API.G_FALSE,
979 p_mode => p_mode,
980 p_validation_level => p_validation_level,
981 p_id => p_id,
982 p_section_sequence => l_sequence,
983 p_scn_id => l_scn_id,
984 p_heading => p_heading,
985 p_description => p_description,
986 p_document_type => p_document_type,
987 p_document_id => p_document_id,
988 p_scn_code => p_scn_code,
989 p_print_yn => p_print_yn,
990 p_attribute_category => p_attribute_category,
991 p_amendment_description => p_amendment_description,
992 p_attribute1 => p_attribute1,
993 p_attribute2 => p_attribute2,
994 p_attribute3 => p_attribute3,
995 p_attribute4 => p_attribute4,
996 p_attribute5 => p_attribute5,
997 p_attribute6 => p_attribute6,
998 p_attribute7 => p_attribute7,
999 p_attribute8 => p_attribute8,
1000 p_attribute9 => p_attribute9,
1001 p_attribute10 => p_attribute10,
1002 p_attribute11 => p_attribute11,
1003 p_attribute12 => p_attribute12,
1004 p_attribute13 => p_attribute13,
1005 p_attribute14 => p_attribute14,
1006 p_attribute15 => p_attribute15,
1007 x_return_status => x_return_status,
1008 x_msg_count => x_msg_count,
1009 x_msg_data => x_msg_data,
1010 x_id => x_id
1011 );
1012
1013 --------------------------------------------
1014 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1016 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1017 RAISE FND_API.G_EXC_ERROR ;
1018 END IF;
1019 --------------------------------------------
1020 IF scn_tbl.COUNT > 0 THEN
1021 FOR k IN scn_tbl.FIRST..scn_tbl.LAST LOOP
1022 IF k > l_ref_count THEN
1023
1024 -- Fix for bug 3735048, Added Update Articles
1025 l_sequence := l_sequence + 10;
1026 IF scn_tbl(k).object_type = 'SECTION' THEN
1027
1028 /*IF (l_debug = 'Y') THEN
1029 okc_debug.log('1000: Updating Display Sequence of scn_id '||scn_tbl(k).id, 2);
1030 END IF;*/
1031
1032 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1033 FND_LOG.STRING(G_PROC_LEVEL,
1034 G_PKG_NAME, '1000: Updating Display Sequence of scn_id '||scn_tbl(k).id );
1035 END IF;
1036
1037 OKC_TERMS_SECTIONS_GRP.update_section(
1038 p_api_version =>1,
1039 p_init_msg_list => OKC_API.G_FALSE,
1040 x_return_status => x_return_status,
1041 x_msg_count => x_msg_count,
1042 x_msg_data => x_msg_data,
1043 p_mode => 'NORMAL',
1044 p_id => scn_tbl(k).id,
1045 p_section_sequence => l_sequence,
1046 p_object_version_number => scn_tbl(k).object_version_number
1047 );
1048 ELSIF scn_tbl(k).object_type = 'ARTICLE' THEN
1049
1050
1051 /*IF (l_debug = 'Y') THEN
1052 okc_debug.log('1200: Updating Display Sequence of cat_id '||scn_tbl(k).id, 2);
1053 END IF;*/
1054
1055 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1056 FND_LOG.STRING(G_PROC_LEVEL,
1057 G_PKG_NAME, '1200: Updating Display Sequence of cat_id '||scn_tbl(k).id );
1058 END IF;
1059
1060 OKC_K_ARTICLES_GRP.update_article(
1061 p_api_version =>1,
1062 p_init_msg_list => OKC_API.G_FALSE,
1063 x_return_status => x_return_status,
1064 x_msg_count => x_msg_count,
1065 x_msg_data => x_msg_data,
1066 p_mode => 'NORMAL',
1067 p_id => scn_tbl(k).id,
1068 p_display_sequence => l_sequence,
1069 p_object_version_number => scn_tbl(k).object_version_number
1070 );
1071 END IF;
1072
1073
1074
1075
1076 --------------------------------------------
1077 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1078 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1079 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1080 RAISE FND_API.G_EXC_ERROR ;
1081 END IF;
1082 --------------------------------------------
1083 END IF;
1084 END LOOP;
1085 END IF;
1086
1087 -- Standard check of p_commit
1088 IF FND_API.To_Boolean( p_commit ) THEN
1089 COMMIT WORK;
1090 END IF;
1091 -- Standard call to get message count and if count is 1, get message info.
1092 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1093
1094 /*IF (l_debug = 'Y') THEN
1095 okc_debug.log('1100: Leaving add_section', 2);
1096 END IF;*/
1097
1098 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1099 FND_LOG.STRING(G_PROC_LEVEL,
1100 G_PKG_NAME, '1100: Leaving add_section' );
1101 END IF;
1102
1103 EXCEPTION
1104 WHEN FND_API.G_EXC_ERROR THEN
1105 /*IF (l_debug = 'Y') THEN
1106 okc_debug.log('1200: Leaving add_section: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1107 END IF;*/
1108
1109 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1110 FND_LOG.STRING(G_EXCP_LEVEL,
1111 G_PKG_NAME, '1200: Leaving add_section: OKC_API.G_EXCEPTION_ERROR Exception' );
1112 END IF;
1113
1114 IF l_get_scn_csr%ISOPEN THEN
1115 CLOSE l_get_scn_csr;
1116 END IF;
1117
1118 IF l_get_max_seq_csr%ISOPEN THEN
1119 CLOSE l_get_max_seq_csr;
1120 END IF;
1121
1122 IF l_get_max_art_seq%ISOPEN THEN
1123 CLOSE l_get_max_art_seq;
1124 END IF;
1125
1126 IF l_get_child_art_crs%ISOPEN THEN
1127 CLOSE l_get_child_art_crs;
1128 END IF;
1129
1130 ROLLBACK TO g_add_section_GRP;
1131 x_return_status := G_RET_STS_ERROR ;
1132 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1133
1134 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1135 /*IF (l_debug = 'Y') THEN
1136 okc_debug.log('1300: Leaving add_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1137 END IF;*/
1138
1139 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1140 FND_LOG.STRING(G_EXCP_LEVEL,
1141 G_PKG_NAME, '1300: Leaving add_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
1142 END IF;
1143
1144 IF l_get_scn_csr%ISOPEN THEN
1145 CLOSE l_get_scn_csr;
1146 END IF;
1147
1148 IF l_get_max_seq_csr%ISOPEN THEN
1149 CLOSE l_get_max_seq_csr;
1150 END IF;
1151
1152
1153 IF l_get_max_art_seq%ISOPEN THEN
1154 CLOSE l_get_max_art_seq;
1155 END IF;
1156
1157 IF l_get_child_art_crs%ISOPEN THEN
1158 CLOSE l_get_child_art_crs;
1159 END IF;
1160 ROLLBACK TO g_add_section_GRP;
1161 x_return_status := G_RET_STS_UNEXP_ERROR ;
1162 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1163
1164 WHEN OTHERS THEN
1165 /*IF (l_debug = 'Y') THEN
1166 okc_debug.log('1400: Leaving add_section because of EXCEPTION: '||sqlerrm, 2);
1167 END IF;*/
1168
1169 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1170 FND_LOG.STRING(G_EXCP_LEVEL,
1171 G_PKG_NAME, '1400: Leaving add_section because of EXCEPTION: '||sqlerrm );
1172 END IF;
1173
1174 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1175 p_msg_name => G_UNEXPECTED_ERROR,
1176 p_token1 => G_SQLCODE_TOKEN,
1177 p_token1_value => sqlcode,
1178 p_token2 => G_SQLERRM_TOKEN,
1179 p_token2_value => sqlerrm);
1180
1181
1182 IF l_get_scn_csr%ISOPEN THEN
1183 CLOSE l_get_scn_csr;
1184 END IF;
1185
1186 IF l_get_max_seq_csr%ISOPEN THEN
1187 CLOSE l_get_max_seq_csr;
1188 END IF;
1189
1190
1191 IF l_get_max_art_seq%ISOPEN THEN
1192 CLOSE l_get_max_art_seq;
1193 END IF;
1194
1195 IF l_get_child_art_crs%ISOPEN THEN
1196 CLOSE l_get_child_art_crs;
1197 END IF;
1198 ROLLBACK TO g_add_section_GRP;
1199 x_return_status := G_RET_STS_UNEXP_ERROR ;
1200 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1201 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1202 END IF;
1203 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1204
1205 END add_section;
1206
1207 ---------------------------------------------------------------------------
1208 -- PROCEDURE lock_row
1209 ---------------------------------------------------------------------------
1210 PROCEDURE lock_row(
1211 p_api_version IN NUMBER,
1212 p_init_msg_list IN VARCHAR2 ,
1213 x_return_status OUT NOCOPY VARCHAR2,
1214 x_msg_count OUT NOCOPY NUMBER,
1215 x_msg_data OUT NOCOPY VARCHAR2,
1216 p_id IN NUMBER,
1217 p_object_version_number IN NUMBER
1218 ) IS
1219 l_api_version CONSTANT NUMBER := 1;
1220 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_row';
1221 BEGIN
1222
1223 /*IF (l_debug = 'Y') THEN
1224 okc_debug.log('1100: Entered lock_row', 2);
1225 END IF;*/
1226
1227 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1228 FND_LOG.STRING(G_PROC_LEVEL,
1229 G_PKG_NAME, '1100: Entered lock_row' );
1230 END IF;
1231
1232 -- Standard Start of API savepoint
1233 SAVEPOINT g_lock_row_GRP;
1234 -- Standard call to check for call compatibility.
1235 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1237 END IF;
1238 -- Initialize message list if p_init_msg_list is set to TRUE.
1239 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1240 FND_MSG_PUB.initialize;
1241 END IF;
1242 -- Initialize API return status to success
1243 x_return_status := FND_API.G_RET_STS_SUCCESS;
1244
1245 --------------------------------------------
1246 -- Calling Simple API for Locking A Row
1247 --------------------------------------------
1248 OKC_TERMS_SECTIONS_PVT.lock_row(
1249 x_return_status => x_return_status,
1250 p_id => p_id,
1251 p_object_version_number => p_object_version_number
1252 );
1253 --------------------------------------------
1254 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1256 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1257 RAISE FND_API.G_EXC_ERROR ;
1258 END IF;
1259 --------------------------------------------
1260
1261 -- Standard call to get message count and if count is 1, get message info.
1262 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1263
1264 /*IF (l_debug = 'Y') THEN
1265 okc_debug.log('1200: Leaving lock_row', 2);
1266 END IF;*/
1267
1268 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1269 FND_LOG.STRING(G_PROC_LEVEL,
1270 G_PKG_NAME, '1200: Leaving lock_row' );
1271 END IF;
1272
1273 EXCEPTION
1274 WHEN FND_API.G_EXC_ERROR THEN
1275 /*IF (l_debug = 'Y') THEN
1276 okc_debug.log('1300: Leaving lock_Row: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1277 END IF;*/
1278
1279 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1280 FND_LOG.STRING(G_EXCP_LEVEL,
1281 G_PKG_NAME, '1300: Leaving lock_Row: OKC_API.G_EXCEPTION_ERROR Exception' );
1282 END IF;
1283 ROLLBACK TO g_lock_row_GRP;
1284 x_return_status := G_RET_STS_ERROR ;
1285 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1286
1287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1288 /*IF (l_debug = 'Y') THEN
1289 okc_debug.log('1400: Leaving lock_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1290 END IF;*/
1291
1292 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1293 FND_LOG.STRING(G_EXCP_LEVEL,
1294 G_PKG_NAME, '1400: Leaving lock_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
1295 END IF;
1296 ROLLBACK TO g_lock_row_GRP;
1297 x_return_status := G_RET_STS_UNEXP_ERROR ;
1298 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1299
1300 WHEN OTHERS THEN
1301 /*IF (l_debug = 'Y') THEN
1302 okc_debug.log('1500: Leaving lock_Row because of EXCEPTION: '||sqlerrm, 2);
1303 END IF;*/
1304
1305 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1306 FND_LOG.STRING(G_EXCP_LEVEL,
1307 G_PKG_NAME, '1500: Leaving lock_Row because of EXCEPTION: '||sqlerrm );
1308 END IF;
1309 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1310 p_msg_name => G_UNEXPECTED_ERROR,
1311 p_token1 => G_SQLCODE_TOKEN,
1312 p_token1_value => sqlcode,
1313 p_token2 => G_SQLERRM_TOKEN,
1314 p_token2_value => sqlerrm);
1315
1316 ROLLBACK TO g_lock_row_GRP;
1317 x_return_status := G_RET_STS_UNEXP_ERROR ;
1318 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1319 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1320 END IF;
1321 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1322
1323 END lock_row;
1324
1325 ---------------------------------------------------------------------------
1326 -- PROCEDURE delete_section
1327 ---------------------------------------------------------------------------
1328 PROCEDURE delete_section(
1329 p_api_version IN NUMBER,
1330 p_init_msg_list IN VARCHAR2,
1331 p_validate_commit IN VARCHAR2,
1332 p_validation_string IN VARCHAR2,
1333 p_commit IN VARCHAR2,
1334 x_return_status OUT NOCOPY VARCHAR2,
1335 x_msg_count OUT NOCOPY NUMBER,
1336 x_msg_data OUT NOCOPY VARCHAR2,
1337 p_mode IN VARCHAR2,
1338 p_super_user_yn IN VARCHAR2,
1339 p_amendment_description IN VARCHAR2,
1340 p_id IN NUMBER,
1341 p_object_version_number IN NUMBER
1342 ) IS
1343
1344 l_api_version CONSTANT NUMBER := 1;
1345 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_section';
1346 l_ok_to_commit VARCHAR2(1);
1347 l_document_id NUMBER;
1348 l_document_type VARCHAR2(30);
1349 l_scn_id okc_sections_b.id%type;
1350 l_cat_id okc_k_articles_b.id%type;
1351 l_ovn okc_sections_b.object_version_number%type;
1352 l_summary_amend_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
1353 l_existing_summary_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
1354 l_existing_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
1355 l_delete_rec BOOLEAN :=FALSE;
1356 l_super_user_yn VARCHAR2(1) ;
1357
1358 CURSOR l_document_id_csr IS
1359 SELECT DOCUMENT_ID,DOCUMENT_TYPE FROM OKC_SECTIONS_B
1360 WHERE ID=P_ID;
1361
1362 CURSOR l_subsection_csr(b_scn_id Number) IS
1363 SELECT ID,OBJECT_VERSION_NUMBER FROM OKC_SECTIONS_B
1364 WHERE SCN_ID=b_scn_id;
1365
1366 CURSOR l_get_article_csr(b_scn_id Number) IS
1367 SELECT ID,OBJECT_VERSION_NUMBER FROM OKC_K_ARTICLES_B
1368 WHERE SCN_ID=b_scn_id;
1369
1370 CURSOR l_get_summary_code_csr IS
1371 SELECT SUMMARY_AMEND_OPERATION_CODE,AMENDMENT_OPERATION_CODE FROM OKC_SECTIONS_B
1372 WHERE ID=P_ID;
1373
1374
1375 BEGIN
1376
1377 /*IF (l_debug = 'Y') THEN
1378 okc_debug.log('2200: Entered delete_section', 2);
1379 END IF;*/
1380
1381 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1382 FND_LOG.STRING(G_PROC_LEVEL,
1383 G_PKG_NAME, '2200: Entered delete_section' );
1384 END IF;
1385
1386 -- Standard Start of API savepoint
1387 SAVEPOINT g_delete_section_GRP;
1388 -- Standard call to check for call compatibility.
1389 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1391 END IF;
1392 -- Initialize message list if p_init_msg_list is set to TRUE.
1393 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1394 FND_MSG_PUB.initialize;
1395 END IF;
1396
1397 IF FND_API.To_Boolean( p_validate_commit ) THEN
1398
1399 OPEN l_document_id_csr;
1400 FETCH l_document_id_csr into l_document_id,l_document_type;
1401 CLOSE l_document_id_csr;
1402
1403 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
1404 p_api_version => l_api_version,
1405 p_init_msg_list => FND_API.G_FALSE,
1406 p_doc_type => l_document_type,
1407 p_doc_id => l_document_id,
1408 p_validation_string => p_validation_string,
1409 x_return_status => x_return_status,
1410 x_msg_data => x_msg_data,
1411 x_msg_count => x_msg_count) ) THEN
1412
1413 /*IF (l_debug = 'Y') THEN
1414 okc_debug.log('2210: Issue with document header Record.Cannot commit', 2);
1415 END IF;*/
1416
1417 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1418 FND_LOG.STRING(G_PROC_LEVEL,
1419 G_PKG_NAME, '2210: Issue with document header Record.Cannot commit' );
1420 END IF;
1421 RAISE FND_API.G_EXC_ERROR ;
1422 END IF;
1423 END IF;
1424
1425 --Bug 3669528 Refresh settings to prevent function security caching problems
1426 IF fnd_function.test('OKC_TERMS_AUTHOR_SUPERUSER','N') AND fnd_function.test('OKC_TERMS_AUTHOR_NON_STD','N') THEN
1427 l_super_user_yn := 'Y';
1428 ELSE
1429 l_super_user_yn := 'N';
1430 END IF;
1431
1432 -- Initialize API return status to success
1433 x_return_status := FND_API.G_RET_STS_SUCCESS;
1434
1435 -- Following code will be deleteing all Subsection and its article.
1436
1437 FOR cr IN l_subsection_csr(p_id) LOOP
1438 l_scn_id := cr.id;
1439 l_ovn := cr.object_version_number;
1440
1441 /*IF (l_debug = 'Y') THEN
1442 okc_debug.log('2220: Calling Delete API to delete Section '|| l_scn_id , 2);
1443 END IF;*/
1444
1445 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1446 FND_LOG.STRING(G_PROC_LEVEL,
1447 G_PKG_NAME, '2220: Calling Delete API to delete Section '|| l_scn_id );
1448 END IF;
1449
1450 OKC_TERMS_SECTIONS_GRP.delete_section(
1451 p_api_version => p_api_version,
1452 p_init_msg_list => FND_API.G_FALSE,
1453 p_commit => FND_API.G_FALSE,
1454 x_return_status => x_return_status,
1455 x_msg_count => x_msg_count,
1456 x_msg_data => x_msg_data,
1457 p_mode => p_mode,
1458 p_id => l_scn_id,
1459 p_amendment_description => NULL,
1460 p_object_version_number => l_ovn
1461 );
1462 --------------------------------------------
1463 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1465 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1466 RAISE FND_API.G_EXC_ERROR ;
1467 END IF;
1468 --------------------------------------------
1469
1470 /*IF (l_debug = 'Y') THEN
1471 okc_debug.log('2230: Section '|| l_scn_id||' deleted' , 2);
1472 END IF;*/
1473
1474 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1475 FND_LOG.STRING(G_PROC_LEVEL,
1476 G_PKG_NAME, '2230: Section '|| l_scn_id||' deleted' );
1477 END IF;
1478 END LOOP;
1479 IF l_subsection_csr%ISOPEN then
1480 CLOSE l_subsection_csr;
1481 END If;
1482
1483 -- Following code will be deleteing all articles of section.
1484 /*IF (l_debug = 'Y') THEN
1485 okc_debug.log('2230: Going to delete Articles of section '|| p_id , 2);
1486 END IF;*/
1487
1488 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1489 FND_LOG.STRING(G_PROC_LEVEL,
1490 G_PKG_NAME, '2230: Going to delete Articles of section '|| p_id );
1491 END IF;
1492
1493 FOR l_article_rec IN l_get_article_csr(p_id) LOOP
1494
1495
1496 OKC_K_ARTICLES_GRP.delete_article(
1497 p_api_version => l_api_version,
1498 p_init_msg_list => FND_API.G_FALSE,
1499 p_validate_commit => FND_API.G_FALSE,
1500 p_validation_string => Null,
1501 p_commit => FND_API.G_FALSE,
1502 x_return_status => x_return_status,
1503 x_msg_count => x_msg_count,
1504 x_msg_data => x_msg_data,
1505 p_mode => p_mode,
1506 p_super_user_yn => l_super_user_yn,
1507 p_id => l_article_rec.id,
1508 p_amendment_description => NULL,
1509 p_object_version_number => l_article_rec.object_version_number );
1510
1511
1512 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1514 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1515 RAISE FND_API.G_EXC_ERROR ;
1516 END IF;
1517 END LOOP;
1518
1519 /*IF (l_debug = 'Y') THEN
1520 okc_debug.log('2240: Articles of section '|| p_id||' deleted' , 2);
1521 END IF;*/
1522
1523 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1524 FND_LOG.STRING(G_PROC_LEVEL,
1525 G_PKG_NAME, '2240: Articles of section '|| p_id||' deleted' );
1526 END IF;
1527
1528 IF p_mode='AMEND' THEN
1529
1530 OPEN l_get_summary_code_csr;
1531 FETCH l_get_summary_code_csr INTO l_existing_summary_code,l_existing_operation_code;
1532 CLOSE l_get_summary_code_csr;
1533
1534 IF nvl(l_existing_operation_code,'?') <> G_AMEND_CODE_ADDED THEN
1535
1536 l_summary_amend_operation_code := OKC_TERMS_UTIL_PVT.get_summary_amend_code(p_existing_summary_code =>l_existing_summary_code,
1537 p_existing_operation_code=>l_existing_operation_code,
1538 p_amend_operation_code=>G_AMEND_CODE_DELETED);
1539
1540 --------------------------------------------
1541 -- Calling Simple API for Updating A Row
1542 --------------------------------------------
1543
1544 OKC_TERMS_SECTIONS_PVT.update_row(
1545 x_return_status => x_return_status,
1546 p_id => p_id,
1547 p_amendment_description => p_amendment_description,
1548 p_amendment_operation_code => G_AMEND_CODE_DELETED,
1549 p_summary_amend_operation_code => l_summary_amend_operation_code,
1550 p_object_version_number => p_object_version_number
1551 );
1552
1553 --------------------------------------------
1554 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1556 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1557 RAISE FND_API.G_EXC_ERROR ;
1558 END IF;
1559 --------------------------------------------
1560 ELSE
1561 l_delete_rec := TRUE;
1562 END IF;
1563 END IF;
1564
1565 IF p_mode<>'AMEND' or l_delete_rec THEN
1566 -- Following Code will delete the section
1567
1568 OKC_TERMS_SECTIONS_PVT.delete_row(
1569 x_return_status => x_return_status,
1570 p_id => p_id,
1571 p_object_version_number => p_object_version_number
1572 );
1573 --------------------------------------------
1574 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1576 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1577 RAISE FND_API.G_EXC_ERROR ;
1578 END IF;
1579 --------------------------------------------
1580 END IF;
1581
1582 -- Standard check of p_commit
1583 IF FND_API.To_Boolean( p_commit ) THEN
1584 COMMIT WORK;
1585 END IF;
1586 -- Standard call to get message count and if count is 1, get message info.
1587 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1588
1589 /*IF (l_debug = 'Y') THEN
1590 okc_debug.log('2300: Leaving delete_section', 2);
1591 END IF;*/
1592
1593 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1594 FND_LOG.STRING(G_PROC_LEVEL,
1595 G_PKG_NAME, '2300: Leaving delete_section' );
1596 END IF;
1597
1598 EXCEPTION
1599 WHEN FND_API.G_EXC_ERROR THEN
1600 /*IF (l_debug = 'Y') THEN
1601 okc_debug.log('2400: Leaving delete_section: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1602 END IF;*/
1603
1604 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1605 FND_LOG.STRING(G_EXCP_LEVEL,
1606 G_PKG_NAME, '2400: Leaving delete_section: OKC_API.G_EXCEPTION_ERROR Exception' );
1607 END IF;
1608
1609 IF l_document_id_csr%ISOPEN THEN
1610 CLOSE l_document_id_csr;
1611 END IF;
1612
1613 IF l_subsection_csr%ISOPEN then
1614 CLOSE l_subsection_csr;
1615 END If;
1616
1617 IF l_get_article_csr%ISOPEN THEN
1618 CLOSE l_get_article_csr;
1619 END IF;
1620
1621 ROLLBACK TO g_delete_section_GRP;
1622 x_return_status := G_RET_STS_ERROR ;
1623 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1624
1625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626 /*IF (l_debug = 'Y') THEN
1627 okc_debug.log('2500: Leaving delete_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1628 END IF;*/
1629
1630 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1631 FND_LOG.STRING(G_EXCP_LEVEL,
1632 G_PKG_NAME, '2500: Leaving delete_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
1633 END IF;
1634
1635 IF l_document_id_csr%ISOPEN THEN
1636 CLOSE l_document_id_csr;
1637 END IF;
1638
1639 IF l_subsection_csr%ISOPEN then
1640 CLOSE l_subsection_csr;
1641 END If;
1642
1643 IF l_get_article_csr%ISOPEN THEN
1644 CLOSE l_get_article_csr;
1645 END IF;
1646
1647 ROLLBACK TO g_delete_section_GRP;
1648 x_return_status := G_RET_STS_UNEXP_ERROR ;
1649 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1650
1651 WHEN OTHERS THEN
1652 /*IF (l_debug = 'Y') THEN
1653 okc_debug.log('2600: Leaving delete_section because of EXCEPTION: '||sqlerrm, 2);
1654 END IF;*/
1655
1656 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1657 FND_LOG.STRING(G_EXCP_LEVEL,
1658 G_PKG_NAME, '2600: Leaving delete_section because of EXCEPTION: '||sqlerrm );
1659 END IF;
1660
1661 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1662 p_msg_name => G_UNEXPECTED_ERROR,
1663 p_token1 => G_SQLCODE_TOKEN,
1664 p_token1_value => sqlcode,
1665 p_token2 => G_SQLERRM_TOKEN,
1666 p_token2_value => sqlerrm);
1667
1668 IF l_document_id_csr%ISOPEN THEN
1669 CLOSE l_document_id_csr;
1670 END IF;
1671
1672 IF l_subsection_csr%ISOPEN then
1673 CLOSE l_subsection_csr;
1674 END If;
1675
1676 IF l_get_article_csr%ISOPEN THEN
1677 CLOSE l_get_article_csr;
1678 END IF;
1679
1680 ROLLBACK TO g_delete_section_GRP;
1681 x_return_status := G_RET_STS_UNEXP_ERROR ;
1682 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1683 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1684 END IF;
1685 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1686
1687 END delete_section;
1688
1689 END OKC_TERMS_SECTIONS_GRP;