[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_SECTIONS_GRP
Source
1 PACKAGE BODY OKC_TERMS_SECTIONS_GRP AS
2 /* $Header: OKCGSCNB.pls 120.2 2011/12/09 13:37:46 serukull 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 ---------------------------------------------------------------------------
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 p_lock_terms_yn IN VARCHAR2
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 l_orig_system_reference_id1 NUMBER;
485
486 CURSOR l_document_id_csr IS
487 SELECT DOCUMENT_ID,DOCUMENT_TYPE,orig_system_reference_id1 FROM OKC_SECTIONS_B
488 WHERE ID=P_ID;
489
490 CURSOR l_get_summary_code_csr IS
491 SELECT SUMMARY_AMEND_OPERATION_CODE ,amendment_operation_code
492 FROM OKC_SECTIONS_B
493 WHERE ID=P_ID;
494
495 BEGIN
496
497 /*IF (l_debug = 'Y') THEN
498 okc_debug.log('1600: Entered update_section', 2);
499 END IF;*/
500
501 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
502 FND_LOG.STRING(G_PROC_LEVEL,
503 G_PKG_NAME, '1600: Entered update_section' );
504 END IF;
505
506 -- Standard Start of API savepoint
507 SAVEPOINT g_update_section_GRP;
508
509 -- Standard call to check for call compatibility.
510 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
512 END IF;
513
514 -- Initialize message list if p_init_msg_list is set to TRUE.
515 IF FND_API.to_Boolean( p_init_msg_list ) THEN
516 FND_MSG_PUB.initialize;
517 END IF;
518
519 IF p_document_id IS NULL OR p_document_type IS NULL OR p_orig_system_reference_id1 IS NULL THEN
520 OPEN l_document_id_csr;
521 FETCH l_document_id_csr into l_document_id,l_document_type,l_orig_system_reference_id1;
522 CLOSE l_document_id_csr;
523 ELSE
524 l_document_id := p_document_id;
525 l_document_type := p_document_type;
526 l_orig_system_reference_id1 := p_orig_system_reference_id1;
527 END IF;
528
529 IF FND_API.To_Boolean( p_validate_commit ) THEN
530 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
531 p_api_version => l_api_version,
532 p_init_msg_list => FND_API.G_FALSE,
533 p_doc_type => l_document_type,
534 p_doc_id => l_document_id,
535 p_validation_string => p_validation_string,
536 x_return_status => x_return_status,
537 x_msg_data => x_msg_data,
538 x_msg_count => x_msg_count) ) THEN
539
540 /*IF (l_debug = 'Y') THEN
541 okc_debug.log('700: Issue with document header Record.Cannot commit', 2);
542 END IF;*/
543
544 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
545 FND_LOG.STRING(G_PROC_LEVEL,
546 G_PKG_NAME, '700: Issue with document header Record.Cannot commit' );
547 END IF;
548 RAISE FND_API.G_EXC_ERROR ;
549 END IF;
550 END IF;
551
552
553 -- Initialize API return status to success
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555
556 IF p_mode='AMEND' THEN
557 l_amendment_description := p_amendment_description;
558
559 OPEN l_get_summary_code_csr;
560 FETCH l_get_summary_code_csr INTO l_existing_summary_code,l_existing_operation_code;
561 CLOSE l_get_summary_code_csr;
562
563 l_amendment_operation_code := nvl(l_existing_operation_code,G_AMEND_CODE_UPDATED);
564 l_summary_amend_operation_code := OKC_TERMS_UTIL_PVT.get_summary_amend_code(p_existing_summary_code =>l_existing_summary_code,
565 p_existing_operation_code=>l_existing_operation_code,
566 p_amend_operation_code=>G_AMEND_CODE_UPDATED);
567
568 ELSE
569 l_amendment_description := NULL;
570 l_amendment_operation_code := NULL;
571 l_summary_amend_operation_code := NULL;
572 END IF;
573
574 -----------------------------------------------------------------
575 -- Concurrent Mod Changes
576 -- Call the Lock entity API only in AMEND mode
577 -- and when p_lock_terms_yn is 'Y' .
578 -----------------------------------------------------------------
579 IF ( p_mode='AMEND'
580 AND l_amendment_operation_code = G_AMEND_CODE_UPDATED
581 AND p_lock_terms_yn = 'Y'
582 AND l_orig_system_reference_id1 IS NOT null
583 )
584 THEN
585
586 okc_k_entity_locks_grp.lock_entity
587 ( p_api_version => 1,
588 p_init_msg_list => FND_API.G_FALSE ,
589 p_commit => FND_API.G_FALSE,
590 p_entity_name => okc_k_entity_locks_grp.G_SECTION_ENTITY,
591 p_entity_pk1 => To_Char(l_orig_system_reference_id1),
592 P_LOCK_BY_ENTITY_ID => p_id,
593 p_LOCK_BY_DOCUMENT_TYPE => l_document_type,
594 p_LOCK_BY_DOCUMENT_ID => l_document_id,
595 X_RETURN_STATUS => X_RETURN_STATUS,
596 X_MSG_COUNT => X_MSG_COUNT,
597 X_MSG_DATA => X_MSG_DATA
598 );
599 --------------------------------------------
600 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
602 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
603 RAISE FND_API.G_EXC_ERROR ;
604 END IF;
605 --------------------------------------------
606 END IF;
607
608 --------------------------------------------
609 -- Calling Simple API for Updating A Row
610 --------------------------------------------
611
612 OKC_TERMS_SECTIONS_PVT.update_row(
613 p_validation_level => p_validation_level,
614 x_return_status => x_return_status,
615 p_id => p_id,
616 p_section_sequence => p_section_sequence,
617 p_label => p_label,
618 p_scn_id => p_scn_id,
619 p_heading => p_heading,
620 p_description => p_description,
621 p_document_type => p_document_type,
622 p_document_id => p_document_id,
623 p_scn_code => p_scn_code,
624 p_amendment_description => l_amendment_description,
625 p_amendment_operation_code => l_amendment_operation_code,
626 p_summary_amend_operation_code => l_summary_amend_operation_code,
627 p_orig_system_reference_code => p_orig_system_reference_code,
628 p_orig_system_reference_id1 => p_orig_system_reference_id1,
629 p_orig_system_reference_id2 => p_orig_system_reference_id2,
630 p_print_yn => p_print_yn,
631 p_attribute_category => p_attribute_category,
632 p_attribute1 => p_attribute1,
633 p_attribute2 => p_attribute2,
634 p_attribute3 => p_attribute3,
635 p_attribute4 => p_attribute4,
636 p_attribute5 => p_attribute5,
637 p_attribute6 => p_attribute6,
638 p_attribute7 => p_attribute7,
639 p_attribute8 => p_attribute8,
640 p_attribute9 => p_attribute9,
641 p_attribute10 => p_attribute10,
642 p_attribute11 => p_attribute11,
643 p_attribute12 => p_attribute12,
644 p_attribute13 => p_attribute13,
645 p_attribute14 => p_attribute14,
646 p_attribute15 => p_attribute15,
647 p_object_version_number => p_object_version_number
648 );
649 --------------------------------------------
650 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
652 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
653 RAISE FND_API.G_EXC_ERROR ;
654 END IF;
655 --------------------------------------------
656
657 -- Standard check of p_commit
658 IF FND_API.To_Boolean( p_commit ) THEN
659 COMMIT WORK;
660 END IF;
661 -- Standard call to get message count and if count is 1, get message info.
662 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
663
664 /*IF (l_debug = 'Y') THEN
665 okc_debug.log('1800: Leaving update_section', 2);
666 END IF;*/
667
668 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
669 FND_LOG.STRING(G_PROC_LEVEL,
670 G_PKG_NAME, '1800: Leaving update_section' );
671 END IF;
672
673 EXCEPTION
674 WHEN FND_API.G_EXC_ERROR THEN
675 /*IF (l_debug = 'Y') THEN
676 okc_debug.log('1900: Leaving update_section: OKC_API.G_EXCEPTION_ERROR Exception', 2);
677 END IF;*/
678
679 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
680 FND_LOG.STRING(G_EXCP_LEVEL,
681 G_PKG_NAME, '1900: Leaving update_section: OKC_API.G_EXCEPTION_ERROR Exception' );
682 END IF;
683
684 IF l_document_id_csr%ISOPEN THEN
685 CLOSE l_document_id_csr;
686 END IF;
687
688 ROLLBACK TO g_update_section_GRP;
689 x_return_status := G_RET_STS_ERROR ;
690 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
691
692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
693 /*IF (l_debug = 'Y') THEN
694 okc_debug.log('2000: Leaving update_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
695 END IF;*/
696
697 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
698 FND_LOG.STRING(G_EXCP_LEVEL,
699 G_PKG_NAME, '2000: Leaving update_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
700 END IF;
701
702 IF l_document_id_csr%ISOPEN THEN
703 CLOSE l_document_id_csr;
704 END IF;
705
706 ROLLBACK TO g_update_section_GRP;
707 x_return_status := G_RET_STS_UNEXP_ERROR ;
708 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
709
710 WHEN OTHERS THEN
711 /*IF (l_debug = 'Y') THEN
712 okc_debug.log('2100: Leaving update_section because of EXCEPTION: '||sqlerrm, 2);
713 END IF;*/
714
715 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
716 FND_LOG.STRING(G_EXCP_LEVEL,
717 G_PKG_NAME, '2100: Leaving update_section because of EXCEPTION: '||sqlerrm );
718 END IF;
719 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
720 p_msg_name => G_UNEXPECTED_ERROR,
721 p_token1 => G_SQLCODE_TOKEN,
722 p_token1_value => sqlcode,
723 p_token2 => G_SQLERRM_TOKEN,
724 p_token2_value => sqlerrm);
725
726 IF l_document_id_csr%ISOPEN THEN
727 CLOSE l_document_id_csr;
728 END IF;
729
730 ROLLBACK TO g_update_section_GRP;
731
732 x_return_status := G_RET_STS_UNEXP_ERROR ;
733 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
734 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
735 END IF;
736 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
737
738 END update_section;
739
740 -------------------------------------------------------
741 -- PROCEDURE add_section :: To be called from UI
742 -------------------------------------------------------
743 PROCEDURE add_section(
744 p_api_version IN NUMBER,
745 p_init_msg_list IN VARCHAR2 ,
746 p_validation_level IN NUMBER ,
747 p_validate_commit IN VARCHAR2 ,
748 p_validation_string IN VARCHAR2,
749 p_commit IN VARCHAR2 ,
750 p_mode IN VARCHAR2 , -- 'NORMAL' or 'AMEND'
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_msg_count OUT NOCOPY NUMBER,
753 x_msg_data OUT NOCOPY VARCHAR2,
754 p_id IN NUMBER,
755 p_ref_scn_id IN NUMBER ,
756 p_ref_point IN VARCHAR2 ,
757 p_heading IN VARCHAR2,
758 p_description IN VARCHAR2,
759 p_document_type IN VARCHAR2,
760 p_document_id IN NUMBER,
761 p_scn_code IN VARCHAR2,
762 p_print_yn IN VARCHAR2 ,
763 p_amendment_description IN VARCHAR2 ,
764 p_attribute_category IN VARCHAR2 ,
765 p_attribute1 IN VARCHAR2 ,
766 p_attribute2 IN VARCHAR2 ,
767 p_attribute3 IN VARCHAR2 ,
768 p_attribute4 IN VARCHAR2 ,
769 p_attribute5 IN VARCHAR2,
770 p_attribute6 IN VARCHAR2 ,
771 p_attribute7 IN VARCHAR2 ,
772 p_attribute8 IN VARCHAR2 ,
773 p_attribute9 IN VARCHAR2 ,
774 p_attribute10 IN VARCHAR2 ,
775 p_attribute11 IN VARCHAR2 ,
776 p_attribute12 IN VARCHAR2 ,
777 p_attribute13 IN VARCHAR2 ,
778 p_attribute14 IN VARCHAR2 ,
779 p_attribute15 IN VARCHAR2 ,
780 x_id OUT NOCOPY NUMBER
781 ) IS
782
783 l_api_version CONSTANT NUMBER := 1;
784 l_api_name CONSTANT VARCHAR2(30) := 'g_add_section';
785 l_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE := 1;
786 l_ok_to_commit VARCHAR2(1);
787 i NUMBER := 0;
788 l_ref_count NUMBER := 0;
789 l_ref_sequence NUMBER := 0;
790 l_ref_sequence1 NUMBER := 0;
791 l_sequence NUMBER := 0;
792 l_ref_is_set BOOLEAN := FALSE;
793 l_scn_id NUMBER ;
794
795 --Fix for bug 3735048, added object_type
796 TYPE scn_rec_type IS RECORD (
797 id OKC_SECTIONS_B.id%type,
798 display_sequence OKC_SECTIONS_B.section_sequence%type,
799 object_version_number OKC_SECTIONS_B.object_version_number%type,
800 object_type VARCHAR2(30)
801 );
802
803 TYPE scn_tbl_type IS TABLE OF scn_rec_type INDEX BY BINARY_INTEGER;
804 scn_tbl scn_tbl_type;
805
806 --Fix for bug 3735048, added UNION ALL to add articles
807 --Also added object_type
808 -- Cursor to get sequence of all child section of a section
809 CURSOR l_get_scn_csr(b_scn_id NUMBER) IS
810 SELECT id,
811 'SECTION' object_type,
812 object_version_number,
813 section_sequence display_sequence,
814 scn_id
815 FROM okc_sections_b
816 WHERE document_type=p_document_type
817 AND document_id = p_document_id
818 AND ( (b_scn_id IS NOT NULL AND scn_id=b_scn_id) OR
819 (b_scn_id IS NULL AND scn_id IS NULL)
820 )
821 UNION ALL
822 SELECT id,
823 'ARTICLE' object_type,
824 object_version_number,
825 display_sequence,
826 scn_id
827 FROM okc_k_articles_b
828 WHERE document_type=p_document_type
829 AND document_id = p_document_id
830 AND ( (b_scn_id IS NOT NULL AND scn_id=b_scn_id) OR
831 (b_scn_id IS NULL AND scn_id IS NULL)
832 )
833 ORDER BY display_sequence;
834
835 -- cursor to get paret of reference scn_id
836 CURSOR l_get_parent_csr IS
837 select scn_id from okc_sections_b
838 where document_type=p_document_type
839 AND document_id = p_document_id
840 AND id=p_ref_scn_id;
841
842
843 -- Cursor to get sequence of last section in a hierarchy
844 cursor l_get_max_seq_csr(b_scn_id NUMBER) is
845 SELECT nvl(max(SECTION_SEQUENCE),0) FROM OKC_SECTIONS_B
846 WHERE document_type=p_document_type
847 AND document_id=p_document_id
848 and (
849 (b_scn_id is Null and scn_id is Null)
850 OR
851 (b_scn_id is Not Null and scn_id=b_scn_id));
852 CURSOR l_get_max_art_seq(b_scn_id NUMBER) IS
853 SELECT nvl(max(DISPLAY_SEQUENCE),0) FROM OKC_K_ARTICLES_B
854 WHERE document_type=p_document_type
855 AND document_id=p_document_id
856 and scn_id =b_scn_id;
857
858 CURSOR l_get_child_art_crs(b_scn_id NUMBER,b_ref_sequence NUMBER) IS
859 SELECT id,object_version_number
860 FROM OKC_K_ARTICLES_B
861 WHERE SCN_ID=b_scn_id
862 AND display_sequence >= b_ref_sequence
863 Order by display_sequence;
864
865
866
867 BEGIN
868
869
870 /*IF (l_debug = 'Y') THEN
871 okc_debug.log('600: Entered add_section', 2);
872 END IF;*/
873
874 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
875 FND_LOG.STRING(G_PROC_LEVEL,
876 G_PKG_NAME, '600: Entered add_section' );
877 END IF;
878
879 -- Standard Start of API savepoint
880 SAVEPOINT g_add_section_GRP;
881 -- Standard call to check for call compatibility.
882 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
884 END IF;
885 -- Initialize message list if p_init_msg_list is set to TRUE.
886 IF FND_API.to_Boolean( p_init_msg_list ) THEN
887 FND_MSG_PUB.initialize;
888 END IF;
889
890 IF FND_API.To_Boolean( p_validate_commit ) AND
891 NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
892 p_api_version => l_api_version,
893 p_init_msg_list => FND_API.G_FALSE,
894 p_doc_type => p_document_type,
895 p_doc_id => p_document_id,
896 p_validation_string => p_validation_string,
897 x_return_status => x_return_status,
898 x_msg_data => x_msg_data,
899 x_msg_count => x_msg_count) ) THEN
900
901 /*IF (l_debug = 'Y') THEN
902 okc_debug.log('700: Issue with document header Record.Cannot commit', 2);
903 END IF;*/
904
905 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
906 FND_LOG.STRING(G_PROC_LEVEL,
907 G_PKG_NAME, '700: Issue with document header Record.Cannot commit' );
908 END IF;
909 RAISE FND_API.G_EXC_ERROR ;
910 END IF;
911
912
913 -- Initialize API return status to success
914 x_return_status := FND_API.G_RET_STS_SUCCESS;
915
916 IF p_ref_point NOT IN ('A','B','S') THEN
917 /*IF (l_debug = 'Y') THEN
918 okc_debug.log('800: Error: Ref point should be either A,B or S', 2);
919 END IF;*/
920
921 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
922 FND_LOG.STRING(G_PROC_LEVEL,
923 G_PKG_NAME, '800: Error: Ref point should be either A,B or S' );
924 END IF;
925
926 Okc_Api.Set_Message( G_FND_APP, 'OKC_WRONG_REF_POINT');
927 RAISE FND_API.G_EXC_ERROR ;
928
929 END IF;
930
931
932 IF p_ref_scn_id is NULL THEN
933 -- Case of Adding a section at TOP Level.This will be last section.
934
935 OPEN l_get_max_seq_csr(Null);
936 FETCH l_get_max_seq_csr into l_ref_sequence;
937 CLOSE l_get_max_seq_csr;
938 l_ref_is_set := TRUE;
939 l_scn_id := Null;
940 END IF;
941
942 IF p_ref_scn_id IS NOT NULL AND p_ref_point='S' THEN
943 -- Case of Adding a subsection at TOP Level.This will be last section in the heirarchy.
944 OPEN l_get_max_seq_csr(p_ref_scn_id);
945 FETCH l_get_max_seq_csr INTO l_ref_sequence;
946 CLOSE l_get_max_seq_csr;
947
948 OPEN l_get_max_art_seq(p_ref_scn_id);
949 FETCH l_get_max_art_seq INTO l_ref_sequence1;
950 CLOSE l_get_max_art_seq;
951
952 l_ref_is_set := TRUE;
953 l_scn_id := p_ref_scn_id;
954
955 IF l_ref_sequence1 > l_ref_sequence THEN
956 l_ref_sequence := l_ref_sequence1;
957 END IF;
958
959 END IF;
960
961 scn_tbl.delete;
962
963 IF NOT l_ref_is_set THEN
964 OPEN l_get_parent_csr;
965 FETCH l_get_parent_csr into l_scn_id;
966 CLOSE l_get_parent_csr ;
967
968 FOR l_scn_rec IN l_get_scn_csr(l_scn_id) LOOP
969
970 i := i +1;
971
972 scn_tbl(i).id := l_scn_rec.id;
973 scn_tbl(i).object_version_number := l_scn_rec.object_version_number;
974 scn_tbl(i).display_sequence := l_scn_rec.display_sequence;
975 scn_tbl(i).object_type := l_scn_rec.object_type;
976
977 -- Finding out reference below which subsection will be create
978
979 IF scn_tbl(i).id = p_ref_scn_id THEN
980 l_ref_count := i;
981 l_ref_sequence := scn_tbl(i).display_sequence;
982 l_ref_is_set := TRUE;
983 END IF;
984
985 END LOOP;
986 IF l_ref_is_set THEN
987 IF p_ref_point='B' THEN
988 l_ref_count := l_ref_count - 1 ;
989 IF l_ref_count=0 THEN
990 l_ref_sequence := 0;
991 ELSE
992 l_ref_sequence := nvl(scn_tbl(l_ref_count).display_sequence,0);
993 END IF;
994 END IF;
995 ELSE
996 /*IF (l_debug = 'Y') THEN
997 okc_debug.log('900: Error: Reference not found', 2);
998 END IF;*/
999
1000 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1001 FND_LOG.STRING(G_PROC_LEVEL,
1002 G_PKG_NAME, '900: Error: Reference not found' );
1003 END IF;
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1005 END IF;
1006 END IF;
1007
1008
1009 l_sequence := l_ref_sequence + 10;
1010 --------------------------------------------
1011 -- Calling API for Creating A Section
1012 --------------------------------------------
1013 OKC_TERMS_SECTIONS_GRP.create_section(
1014 p_api_version => 1,
1015 p_init_msg_list => FND_API.G_FALSE,
1016 p_commit => FND_API.G_FALSE,
1017 p_mode => p_mode,
1018 p_validation_level => p_validation_level,
1019 p_id => p_id,
1020 p_section_sequence => l_sequence,
1021 p_scn_id => l_scn_id,
1022 p_heading => p_heading,
1023 p_description => p_description,
1024 p_document_type => p_document_type,
1025 p_document_id => p_document_id,
1026 p_scn_code => p_scn_code,
1027 p_print_yn => p_print_yn,
1028 p_attribute_category => p_attribute_category,
1029 p_amendment_description => p_amendment_description,
1030 p_attribute1 => p_attribute1,
1031 p_attribute2 => p_attribute2,
1032 p_attribute3 => p_attribute3,
1033 p_attribute4 => p_attribute4,
1034 p_attribute5 => p_attribute5,
1035 p_attribute6 => p_attribute6,
1036 p_attribute7 => p_attribute7,
1037 p_attribute8 => p_attribute8,
1038 p_attribute9 => p_attribute9,
1039 p_attribute10 => p_attribute10,
1040 p_attribute11 => p_attribute11,
1041 p_attribute12 => p_attribute12,
1042 p_attribute13 => p_attribute13,
1043 p_attribute14 => p_attribute14,
1044 p_attribute15 => p_attribute15,
1045 x_return_status => x_return_status,
1046 x_msg_count => x_msg_count,
1047 x_msg_data => x_msg_data,
1048 x_id => x_id
1049 );
1050
1051 --------------------------------------------
1052 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1054 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1055 RAISE FND_API.G_EXC_ERROR ;
1056 END IF;
1057 --------------------------------------------
1058 IF scn_tbl.COUNT > 0 THEN
1059 FOR k IN scn_tbl.FIRST..scn_tbl.LAST LOOP
1060 IF k > l_ref_count THEN
1061
1062 -- Fix for bug 3735048, Added Update Articles
1063 l_sequence := l_sequence + 10;
1064 IF scn_tbl(k).object_type = 'SECTION' THEN
1065
1066 /*IF (l_debug = 'Y') THEN
1067 okc_debug.log('1000: Updating Display Sequence of scn_id '||scn_tbl(k).id, 2);
1068 END IF;*/
1069
1070 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1071 FND_LOG.STRING(G_PROC_LEVEL,
1072 G_PKG_NAME, '1000: Updating Display Sequence of scn_id '||scn_tbl(k).id );
1073 END IF;
1074
1075 OKC_TERMS_SECTIONS_GRP.update_section(
1076 p_api_version =>1,
1077 p_init_msg_list => OKC_API.G_FALSE,
1078 x_return_status => x_return_status,
1079 x_msg_count => x_msg_count,
1080 x_msg_data => x_msg_data,
1081 p_mode => 'NORMAL',
1082 p_id => scn_tbl(k).id,
1083 p_section_sequence => l_sequence,
1084 p_object_version_number => scn_tbl(k).object_version_number
1085 );
1086 ELSIF scn_tbl(k).object_type = 'ARTICLE' THEN
1087
1088
1089 /*IF (l_debug = 'Y') THEN
1090 okc_debug.log('1200: Updating Display Sequence of cat_id '||scn_tbl(k).id, 2);
1091 END IF;*/
1092
1093 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1094 FND_LOG.STRING(G_PROC_LEVEL,
1095 G_PKG_NAME, '1200: Updating Display Sequence of cat_id '||scn_tbl(k).id );
1096 END IF;
1097
1098 OKC_K_ARTICLES_GRP.update_article(
1099 p_api_version =>1,
1100 p_init_msg_list => OKC_API.G_FALSE,
1101 x_return_status => x_return_status,
1102 x_msg_count => x_msg_count,
1103 x_msg_data => x_msg_data,
1104 p_mode => 'NORMAL',
1105 p_id => scn_tbl(k).id,
1106 p_display_sequence => l_sequence,
1107 p_object_version_number => scn_tbl(k).object_version_number
1108 );
1109 END IF;
1110
1111
1112
1113
1114 --------------------------------------------
1115 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1117 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1118 RAISE FND_API.G_EXC_ERROR ;
1119 END IF;
1120 --------------------------------------------
1121 END IF;
1122 END LOOP;
1123 END IF;
1124
1125 -- Standard check of p_commit
1126 IF FND_API.To_Boolean( p_commit ) THEN
1127 COMMIT WORK;
1128 END IF;
1129 -- Standard call to get message count and if count is 1, get message info.
1130 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1131
1132 /*IF (l_debug = 'Y') THEN
1133 okc_debug.log('1100: Leaving add_section', 2);
1134 END IF;*/
1135
1136 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1137 FND_LOG.STRING(G_PROC_LEVEL,
1138 G_PKG_NAME, '1100: Leaving add_section' );
1139 END IF;
1140
1141 EXCEPTION
1142 WHEN FND_API.G_EXC_ERROR THEN
1143 /*IF (l_debug = 'Y') THEN
1144 okc_debug.log('1200: Leaving add_section: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1145 END IF;*/
1146
1147 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1148 FND_LOG.STRING(G_EXCP_LEVEL,
1149 G_PKG_NAME, '1200: Leaving add_section: OKC_API.G_EXCEPTION_ERROR Exception' );
1150 END IF;
1151
1152 IF l_get_scn_csr%ISOPEN THEN
1153 CLOSE l_get_scn_csr;
1154 END IF;
1155
1156 IF l_get_max_seq_csr%ISOPEN THEN
1157 CLOSE l_get_max_seq_csr;
1158 END IF;
1159
1160 IF l_get_max_art_seq%ISOPEN THEN
1161 CLOSE l_get_max_art_seq;
1162 END IF;
1163
1164 IF l_get_child_art_crs%ISOPEN THEN
1165 CLOSE l_get_child_art_crs;
1166 END IF;
1167
1168 ROLLBACK TO g_add_section_GRP;
1169 x_return_status := G_RET_STS_ERROR ;
1170 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1171
1172 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1173 /*IF (l_debug = 'Y') THEN
1174 okc_debug.log('1300: Leaving add_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1175 END IF;*/
1176
1177 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1178 FND_LOG.STRING(G_EXCP_LEVEL,
1179 G_PKG_NAME, '1300: Leaving add_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
1180 END IF;
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 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1201
1202 WHEN OTHERS THEN
1203 /*IF (l_debug = 'Y') THEN
1204 okc_debug.log('1400: Leaving add_section because of EXCEPTION: '||sqlerrm, 2);
1205 END IF;*/
1206
1207 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1208 FND_LOG.STRING(G_EXCP_LEVEL,
1209 G_PKG_NAME, '1400: Leaving add_section because of EXCEPTION: '||sqlerrm );
1210 END IF;
1211
1212 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1213 p_msg_name => G_UNEXPECTED_ERROR,
1214 p_token1 => G_SQLCODE_TOKEN,
1215 p_token1_value => sqlcode,
1216 p_token2 => G_SQLERRM_TOKEN,
1217 p_token2_value => sqlerrm);
1218
1219
1220 IF l_get_scn_csr%ISOPEN THEN
1221 CLOSE l_get_scn_csr;
1222 END IF;
1223
1224 IF l_get_max_seq_csr%ISOPEN THEN
1225 CLOSE l_get_max_seq_csr;
1226 END IF;
1227
1228
1229 IF l_get_max_art_seq%ISOPEN THEN
1230 CLOSE l_get_max_art_seq;
1231 END IF;
1232
1233 IF l_get_child_art_crs%ISOPEN THEN
1234 CLOSE l_get_child_art_crs;
1235 END IF;
1236 ROLLBACK TO g_add_section_GRP;
1237 x_return_status := G_RET_STS_UNEXP_ERROR ;
1238 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1239 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1240 END IF;
1241 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1242
1243 END add_section;
1244
1245 ---------------------------------------------------------------------------
1246 -- PROCEDURE lock_row
1247 ---------------------------------------------------------------------------
1248 PROCEDURE lock_row(
1249 p_api_version IN NUMBER,
1250 p_init_msg_list IN VARCHAR2 ,
1251 x_return_status OUT NOCOPY VARCHAR2,
1252 x_msg_count OUT NOCOPY NUMBER,
1253 x_msg_data OUT NOCOPY VARCHAR2,
1254 p_id IN NUMBER,
1255 p_object_version_number IN NUMBER
1256 ) IS
1257 l_api_version CONSTANT NUMBER := 1;
1258 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_row';
1259 BEGIN
1260
1261 /*IF (l_debug = 'Y') THEN
1262 okc_debug.log('1100: Entered lock_row', 2);
1263 END IF;*/
1264
1265 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1266 FND_LOG.STRING(G_PROC_LEVEL,
1267 G_PKG_NAME, '1100: Entered lock_row' );
1268 END IF;
1269
1270 -- Standard Start of API savepoint
1271 SAVEPOINT g_lock_row_GRP;
1272 -- Standard call to check for call compatibility.
1273 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275 END IF;
1276 -- Initialize message list if p_init_msg_list is set to TRUE.
1277 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1278 FND_MSG_PUB.initialize;
1279 END IF;
1280 -- Initialize API return status to success
1281 x_return_status := FND_API.G_RET_STS_SUCCESS;
1282
1283 --------------------------------------------
1284 -- Calling Simple API for Locking A Row
1285 --------------------------------------------
1286 OKC_TERMS_SECTIONS_PVT.lock_row(
1287 x_return_status => x_return_status,
1288 p_id => p_id,
1289 p_object_version_number => p_object_version_number
1290 );
1291 --------------------------------------------
1292 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1294 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1295 RAISE FND_API.G_EXC_ERROR ;
1296 END IF;
1297 --------------------------------------------
1298
1299 -- Standard call to get message count and if count is 1, get message info.
1300 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1301
1302 /*IF (l_debug = 'Y') THEN
1303 okc_debug.log('1200: Leaving lock_row', 2);
1304 END IF;*/
1305
1306 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1307 FND_LOG.STRING(G_PROC_LEVEL,
1308 G_PKG_NAME, '1200: Leaving lock_row' );
1309 END IF;
1310
1311 EXCEPTION
1312 WHEN FND_API.G_EXC_ERROR THEN
1313 /*IF (l_debug = 'Y') THEN
1314 okc_debug.log('1300: Leaving lock_Row: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1315 END IF;*/
1316
1317 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1318 FND_LOG.STRING(G_EXCP_LEVEL,
1319 G_PKG_NAME, '1300: Leaving lock_Row: OKC_API.G_EXCEPTION_ERROR Exception' );
1320 END IF;
1321 ROLLBACK TO g_lock_row_GRP;
1322 x_return_status := G_RET_STS_ERROR ;
1323 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1324
1325 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1326 /*IF (l_debug = 'Y') THEN
1327 okc_debug.log('1400: Leaving lock_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1328 END IF;*/
1329
1330 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1331 FND_LOG.STRING(G_EXCP_LEVEL,
1332 G_PKG_NAME, '1400: Leaving lock_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
1333 END IF;
1334 ROLLBACK TO g_lock_row_GRP;
1335 x_return_status := G_RET_STS_UNEXP_ERROR ;
1336 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1337
1338 WHEN OTHERS THEN
1339 /*IF (l_debug = 'Y') THEN
1340 okc_debug.log('1500: Leaving lock_Row because of EXCEPTION: '||sqlerrm, 2);
1341 END IF;*/
1342
1343 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1344 FND_LOG.STRING(G_EXCP_LEVEL,
1345 G_PKG_NAME, '1500: Leaving lock_Row because of EXCEPTION: '||sqlerrm );
1346 END IF;
1347 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1348 p_msg_name => G_UNEXPECTED_ERROR,
1349 p_token1 => G_SQLCODE_TOKEN,
1350 p_token1_value => sqlcode,
1351 p_token2 => G_SQLERRM_TOKEN,
1352 p_token2_value => sqlerrm);
1353
1354 ROLLBACK TO g_lock_row_GRP;
1355 x_return_status := G_RET_STS_UNEXP_ERROR ;
1356 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1357 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1358 END IF;
1359 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1360
1361 END lock_row;
1362
1363 ---------------------------------------------------------------------------
1364 -- PROCEDURE delete_section
1365 ---------------------------------------------------------------------------
1366 PROCEDURE delete_section(
1367 p_api_version IN NUMBER,
1368 p_init_msg_list IN VARCHAR2,
1369 p_validate_commit IN VARCHAR2,
1370 p_validation_string IN VARCHAR2,
1371 p_commit IN VARCHAR2,
1372 x_return_status OUT NOCOPY VARCHAR2,
1373 x_msg_count OUT NOCOPY NUMBER,
1374 x_msg_data OUT NOCOPY VARCHAR2,
1375 p_mode IN VARCHAR2,
1376 p_super_user_yn IN VARCHAR2,
1377 p_amendment_description IN VARCHAR2,
1378 p_id IN NUMBER,
1379 p_object_version_number IN NUMBER,
1380 p_lock_terms_yn IN VARCHAR2
1381 ) IS
1382
1383 l_api_version CONSTANT NUMBER := 1;
1384 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_section';
1385 l_ok_to_commit VARCHAR2(1);
1386 l_document_id NUMBER;
1387 l_document_type VARCHAR2(30);
1388 l_scn_id okc_sections_b.id%type;
1389 l_cat_id okc_k_articles_b.id%type;
1390 l_ovn okc_sections_b.object_version_number%type;
1391 l_summary_amend_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
1392 l_existing_summary_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
1393 l_existing_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
1394 l_delete_rec BOOLEAN :=FALSE;
1395 l_super_user_yn VARCHAR2(1) ;
1396
1397 l_orig_system_reference_id1 NUMBER;
1398
1399 CURSOR l_document_id_csr IS
1400 SELECT DOCUMENT_ID,DOCUMENT_TYPE,orig_system_reference_id1 FROM OKC_SECTIONS_B
1401 WHERE ID=P_ID;
1402
1403 CURSOR l_subsection_csr(b_scn_id Number) IS
1404 SELECT ID,OBJECT_VERSION_NUMBER FROM OKC_SECTIONS_B
1405 WHERE SCN_ID=b_scn_id;
1406
1407 CURSOR l_get_article_csr(b_scn_id Number) IS
1408 SELECT ID,OBJECT_VERSION_NUMBER FROM OKC_K_ARTICLES_B
1409 WHERE SCN_ID=b_scn_id;
1410
1411 CURSOR l_get_summary_code_csr IS
1412 SELECT SUMMARY_AMEND_OPERATION_CODE,AMENDMENT_OPERATION_CODE FROM OKC_SECTIONS_B
1413 WHERE ID=P_ID;
1414
1415
1416 BEGIN
1417
1418 /*IF (l_debug = 'Y') THEN
1419 okc_debug.log('2200: Entered delete_section', 2);
1420 END IF;*/
1421
1422 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1423 FND_LOG.STRING(G_PROC_LEVEL,
1424 G_PKG_NAME, '2200: Entered delete_section' );
1425 END IF;
1426
1427 -- Standard Start of API savepoint
1428 SAVEPOINT g_delete_section_GRP;
1429 -- Standard call to check for call compatibility.
1430 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1432 END IF;
1433 -- Initialize message list if p_init_msg_list is set to TRUE.
1434 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1435 FND_MSG_PUB.initialize;
1436 END IF;
1437
1438 OPEN l_document_id_csr;
1439 FETCH l_document_id_csr into l_document_id,l_document_type,l_orig_system_reference_id1;
1440 CLOSE l_document_id_csr;
1441
1442
1443 IF FND_API.To_Boolean( p_validate_commit ) THEN
1444
1445
1446
1447 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
1448 p_api_version => l_api_version,
1449 p_init_msg_list => FND_API.G_FALSE,
1450 p_doc_type => l_document_type,
1451 p_doc_id => l_document_id,
1452 p_validation_string => p_validation_string,
1453 x_return_status => x_return_status,
1454 x_msg_data => x_msg_data,
1455 x_msg_count => x_msg_count) ) THEN
1456
1457 /*IF (l_debug = 'Y') THEN
1458 okc_debug.log('2210: Issue with document header Record.Cannot commit', 2);
1459 END IF;*/
1460
1461 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1462 FND_LOG.STRING(G_PROC_LEVEL,
1463 G_PKG_NAME, '2210: Issue with document header Record.Cannot commit' );
1464 END IF;
1465 RAISE FND_API.G_EXC_ERROR ;
1466 END IF;
1467 END IF;
1468
1469 --Bug 3669528 Refresh settings to prevent function security caching problems
1470 IF fnd_function.test('OKC_TERMS_AUTHOR_SUPERUSER','N') AND fnd_function.test('OKC_TERMS_AUTHOR_NON_STD','N') THEN
1471 l_super_user_yn := 'Y';
1472 ELSE
1473 l_super_user_yn := 'N';
1474 END IF;
1475
1476 -- Initialize API return status to success
1477 x_return_status := FND_API.G_RET_STS_SUCCESS;
1478
1479 -- Following code will be deleteing all Subsection and its article.
1480
1481 FOR cr IN l_subsection_csr(p_id) LOOP
1482 l_scn_id := cr.id;
1483 l_ovn := cr.object_version_number;
1484
1485 /*IF (l_debug = 'Y') THEN
1486 okc_debug.log('2220: Calling Delete API to delete Section '|| l_scn_id , 2);
1487 END IF;*/
1488
1489 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1490 FND_LOG.STRING(G_PROC_LEVEL,
1491 G_PKG_NAME, '2220: Calling Delete API to delete Section '|| l_scn_id );
1492 END IF;
1493
1494 OKC_TERMS_SECTIONS_GRP.delete_section(
1495 p_api_version => p_api_version,
1496 p_init_msg_list => FND_API.G_FALSE,
1497 p_commit => FND_API.G_FALSE,
1498 x_return_status => x_return_status,
1499 x_msg_count => x_msg_count,
1500 x_msg_data => x_msg_data,
1501 p_mode => p_mode,
1502 p_id => l_scn_id,
1503 p_amendment_description => NULL,
1504 p_object_version_number => l_ovn
1505 );
1506 --------------------------------------------
1507 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1509 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1510 RAISE FND_API.G_EXC_ERROR ;
1511 END IF;
1512 --------------------------------------------
1513
1514 /*IF (l_debug = 'Y') THEN
1515 okc_debug.log('2230: Section '|| l_scn_id||' deleted' , 2);
1516 END IF;*/
1517
1518 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1519 FND_LOG.STRING(G_PROC_LEVEL,
1520 G_PKG_NAME, '2230: Section '|| l_scn_id||' deleted' );
1521 END IF;
1522 END LOOP;
1523 IF l_subsection_csr%ISOPEN then
1524 CLOSE l_subsection_csr;
1525 END If;
1526
1527 -- Following code will be deleteing all articles of section.
1528 /*IF (l_debug = 'Y') THEN
1529 okc_debug.log('2230: Going to delete Articles of section '|| p_id , 2);
1530 END IF;*/
1531
1532 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1533 FND_LOG.STRING(G_PROC_LEVEL,
1534 G_PKG_NAME, '2230: Going to delete Articles of section '|| p_id );
1535 END IF;
1536
1537 FOR l_article_rec IN l_get_article_csr(p_id) LOOP
1538
1539
1540 OKC_K_ARTICLES_GRP.delete_article(
1541 p_api_version => l_api_version,
1542 p_init_msg_list => FND_API.G_FALSE,
1543 p_validate_commit => FND_API.G_FALSE,
1544 p_validation_string => Null,
1545 p_commit => FND_API.G_FALSE,
1546 x_return_status => x_return_status,
1547 x_msg_count => x_msg_count,
1548 x_msg_data => x_msg_data,
1549 p_mode => p_mode,
1550 p_super_user_yn => l_super_user_yn,
1551 p_id => l_article_rec.id,
1552 p_amendment_description => NULL,
1553 p_object_version_number => l_article_rec.object_version_number,
1554 p_lock_terms_yn => p_lock_terms_yn );
1555
1556
1557 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1559 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1560 RAISE FND_API.G_EXC_ERROR ;
1561 END IF;
1562 END LOOP;
1563
1564 /*IF (l_debug = 'Y') THEN
1565 okc_debug.log('2240: Articles of section '|| p_id||' deleted' , 2);
1566 END IF;*/
1567
1568 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1569 FND_LOG.STRING(G_PROC_LEVEL,
1570 G_PKG_NAME, '2240: Articles of section '|| p_id||' deleted' );
1571 END IF;
1572
1573 IF p_mode='AMEND' THEN
1574
1575 OPEN l_get_summary_code_csr;
1576 FETCH l_get_summary_code_csr INTO l_existing_summary_code,l_existing_operation_code;
1577 CLOSE l_get_summary_code_csr;
1578
1579 IF nvl(l_existing_operation_code,'?') <> G_AMEND_CODE_ADDED THEN
1580
1581 l_summary_amend_operation_code := OKC_TERMS_UTIL_PVT.get_summary_amend_code(p_existing_summary_code =>l_existing_summary_code,
1582 p_existing_operation_code=>l_existing_operation_code,
1583 p_amend_operation_code=>G_AMEND_CODE_DELETED);
1584
1585 -----------------------------------------------------------------
1586 -- Concurrent Mod Changes
1587 -- Call the Lock entity API only in AMEND mode
1588 -- and when p_lock_terms_yn is 'Y' .
1589 -----------------------------------------------------------------
1590 IF ( p_mode='AMEND'
1591 AND p_lock_terms_yn = 'Y'
1592 AND l_orig_system_reference_id1 IS NOT null
1593 )
1594 THEN
1595
1596 okc_k_entity_locks_grp.lock_entity
1597 ( p_api_version => 1,
1598 p_init_msg_list => FND_API.G_FALSE ,
1599 p_commit => FND_API.G_FALSE,
1600 p_entity_name => okc_k_entity_locks_grp.G_SECTION_ENTITY,
1601 p_entity_pk1 => To_Char(l_orig_system_reference_id1),
1602 P_LOCK_BY_ENTITY_ID => p_id,
1603 p_LOCK_BY_DOCUMENT_TYPE => l_document_type,
1604 p_LOCK_BY_DOCUMENT_ID => l_document_id,
1605 X_RETURN_STATUS => X_RETURN_STATUS,
1606 X_MSG_COUNT => X_MSG_COUNT,
1607 X_MSG_DATA => X_MSG_DATA
1608 );
1609 --------------------------------------------
1610 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1612 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1613 RAISE FND_API.G_EXC_ERROR ;
1614 END IF;
1615 --------------------------------------------
1616 END IF;
1617
1618
1619
1620 --------------------------------------------
1621 -- Calling Simple API for Updating A Row
1622 --------------------------------------------
1623
1624 OKC_TERMS_SECTIONS_PVT.update_row(
1625 x_return_status => x_return_status,
1626 p_id => p_id,
1627 p_amendment_description => p_amendment_description,
1628 p_amendment_operation_code => G_AMEND_CODE_DELETED,
1629 p_summary_amend_operation_code => l_summary_amend_operation_code,
1630 p_object_version_number => p_object_version_number
1631 );
1632
1633 --------------------------------------------
1634 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1636 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1637 RAISE FND_API.G_EXC_ERROR ;
1638 END IF;
1639 --------------------------------------------
1640 ELSE
1641 l_delete_rec := TRUE;
1642 END IF;
1643 END IF;
1644
1645 IF p_mode<>'AMEND' or l_delete_rec THEN
1646 -- Following Code will delete the section
1647
1648 OKC_TERMS_SECTIONS_PVT.delete_row(
1649 x_return_status => x_return_status,
1650 p_id => p_id,
1651 p_object_version_number => p_object_version_number
1652 );
1653 --------------------------------------------
1654 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1656 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1657 RAISE FND_API.G_EXC_ERROR ;
1658 END IF;
1659 --------------------------------------------
1660 END IF;
1661
1662 -- Standard check of p_commit
1663 IF FND_API.To_Boolean( p_commit ) THEN
1664 COMMIT WORK;
1665 END IF;
1666 -- Standard call to get message count and if count is 1, get message info.
1667 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1668
1669 /*IF (l_debug = 'Y') THEN
1670 okc_debug.log('2300: Leaving delete_section', 2);
1671 END IF;*/
1672
1673 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1674 FND_LOG.STRING(G_PROC_LEVEL,
1675 G_PKG_NAME, '2300: Leaving delete_section' );
1676 END IF;
1677
1678 EXCEPTION
1679 WHEN FND_API.G_EXC_ERROR THEN
1680 /*IF (l_debug = 'Y') THEN
1681 okc_debug.log('2400: Leaving delete_section: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1682 END IF;*/
1683
1684 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1685 FND_LOG.STRING(G_EXCP_LEVEL,
1686 G_PKG_NAME, '2400: Leaving delete_section: OKC_API.G_EXCEPTION_ERROR Exception' );
1687 END IF;
1688
1689 IF l_document_id_csr%ISOPEN THEN
1690 CLOSE l_document_id_csr;
1691 END IF;
1692
1693 IF l_subsection_csr%ISOPEN then
1694 CLOSE l_subsection_csr;
1695 END If;
1696
1697 IF l_get_article_csr%ISOPEN THEN
1698 CLOSE l_get_article_csr;
1699 END IF;
1700
1701 ROLLBACK TO g_delete_section_GRP;
1702 x_return_status := G_RET_STS_ERROR ;
1703 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1704
1705 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1706 /*IF (l_debug = 'Y') THEN
1707 okc_debug.log('2500: Leaving delete_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1708 END IF;*/
1709
1710 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1711 FND_LOG.STRING(G_EXCP_LEVEL,
1712 G_PKG_NAME, '2500: Leaving delete_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
1713 END IF;
1714
1715 IF l_document_id_csr%ISOPEN THEN
1716 CLOSE l_document_id_csr;
1717 END IF;
1718
1719 IF l_subsection_csr%ISOPEN then
1720 CLOSE l_subsection_csr;
1721 END If;
1722
1723 IF l_get_article_csr%ISOPEN THEN
1724 CLOSE l_get_article_csr;
1725 END IF;
1726
1727 ROLLBACK TO g_delete_section_GRP;
1728 x_return_status := G_RET_STS_UNEXP_ERROR ;
1729 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1730
1731 WHEN OTHERS THEN
1732 /*IF (l_debug = 'Y') THEN
1733 okc_debug.log('2600: Leaving delete_section because of EXCEPTION: '||sqlerrm, 2);
1734 END IF;*/
1735
1736 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1737 FND_LOG.STRING(G_EXCP_LEVEL,
1738 G_PKG_NAME, '2600: Leaving delete_section because of EXCEPTION: '||sqlerrm );
1739 END IF;
1740
1741 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1742 p_msg_name => G_UNEXPECTED_ERROR,
1743 p_token1 => G_SQLCODE_TOKEN,
1744 p_token1_value => sqlcode,
1745 p_token2 => G_SQLERRM_TOKEN,
1746 p_token2_value => sqlerrm);
1747
1748 IF l_document_id_csr%ISOPEN THEN
1749 CLOSE l_document_id_csr;
1750 END IF;
1751
1752 IF l_subsection_csr%ISOPEN then
1753 CLOSE l_subsection_csr;
1754 END If;
1755
1756 IF l_get_article_csr%ISOPEN THEN
1757 CLOSE l_get_article_csr;
1758 END IF;
1759
1760 ROLLBACK TO g_delete_section_GRP;
1761 x_return_status := G_RET_STS_UNEXP_ERROR ;
1762 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1763 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1764 END IF;
1765 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1766
1767 END delete_section;
1768
1769 END OKC_TERMS_SECTIONS_GRP;