[Home] [Help]
PACKAGE BODY: APPS.OKC_K_ARTICLES_GRP
Source
1 PACKAGE BODY OKC_K_ARTICLES_GRP AS
2 /* $Header: OKCGCATB.pls 120.0 2005/05/26 09:31:31 appldev 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_K_ARTICLES_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_UPDATED CONSTANT varchar2(30) := 'UPDATED';
30 G_AMEND_CODE_ADDED CONSTANT varchar2(30) := 'ADDED';
31 G_AMEND_CODE_DELETED CONSTANT varchar2(30) := 'DELETED';
32
33 -------------------------------------
34 -- PROCEDURE create_article
35 -------------------------------------
36 PROCEDURE create_article(
37 p_api_version IN NUMBER,
38 p_init_msg_list IN VARCHAR2 ,
39 p_validation_level IN NUMBER ,
40 p_commit IN VARCHAR2,
41 p_mode IN VARCHAR2,
42
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2,
46 p_id IN NUMBER,
47 p_sav_sae_id IN NUMBER,
48 p_cat_type IN VARCHAR2, --Bug 3341342
49 p_document_type IN VARCHAR2,
50 p_document_id IN NUMBER,
51 p_cle_id IN NUMBER,
52 p_source_flag IN VARCHAR2,
53 p_mandatory_yn IN VARCHAR2,
54 p_scn_id IN NUMBER,
55 p_label IN VARCHAR2,
56 p_amendment_description IN VARCHAR2,
57 p_article_version_id IN NUMBER,
58 p_change_nonstd_yn IN VARCHAR2,
59 p_orig_system_reference_code IN VARCHAR2,
60 p_orig_system_reference_id1 IN NUMBER,
61 p_orig_system_reference_id2 IN NUMBER,
62 p_display_sequence IN NUMBER,
63
64 p_attribute_category IN VARCHAR2 ,
65 p_attribute1 IN VARCHAR2 ,
66 p_attribute2 IN VARCHAR2 ,
67 p_attribute3 IN VARCHAR2 ,
68 p_attribute4 IN VARCHAR2 ,
69 p_attribute5 IN VARCHAR2 ,
70 p_attribute6 IN VARCHAR2 ,
71 p_attribute7 IN VARCHAR2 ,
72 p_attribute8 IN VARCHAR2 ,
73 p_attribute9 IN VARCHAR2 ,
74 p_attribute10 IN VARCHAR2 ,
75 p_attribute11 IN VARCHAR2 ,
76 p_attribute12 IN VARCHAR2 ,
77 p_attribute13 IN VARCHAR2 ,
78 p_attribute14 IN VARCHAR2 ,
79 p_attribute15 IN VARCHAR2 ,
80 p_print_text_yn IN VARCHAR2 ,
81 p_ref_article_id IN NUMBER,
82 p_ref_article_version_id IN NUMBER,
83
84 x_id OUT NOCOPY NUMBER
85
86 ) IS
87
88 l_api_version CONSTANT NUMBER := 1;
89 l_api_name CONSTANT VARCHAR2(30) := 'g_create_article';
90 l_cat_id NUMBER;
91 l_del_cat_id NUMBER;
92 l_variable_code OKC_K_ART_VARIABLES.VARIABLE_CODE%TYPE;
93 l_amendment_operation_code OKC_K_ARTICLES_B.AMENDMENT_OPERATION_CODE%TYPE;
94 l_summary_amend_operation_code OKC_K_ARTICLES_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
95 l_amendment_description OKC_K_ARTICLES_B.AMENDMENT_DESCRIPTION%TYPE;
96
97 CURSOR l_article_var_csr(b_article_version_id NUMBER) IS
98 SELECT VAR.VARIABLE_CODE, BUSVAR.VARIABLE_TYPE, BUSVAR.EXTERNAL_YN, BUSVAR.VALUE_SET_ID
99 FROM OKC_ARTICLE_VARIABLES VAR,
100 OKC_BUS_VARIABLES_B BUSVAR
101 WHERE VAR.ARTICLE_VERSION_ID = b_article_version_id
102 AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE;
103
104 CURSOR l_get_deleted_rec_csr IS
105 SELECT id FROM okc_k_articles_b
106 WHERE document_type=p_document_type
107 AND document_id =p_document_id
108 AND ( sav_sae_id = p_sav_sae_id
109 OR sav_sae_id=p_ref_article_id
110 OR ref_article_id=p_sav_sae_id
111 OR ref_article_id = p_ref_article_id)
112 AND ( amendment_operation_code=G_AMEND_CODE_DELETED OR summary_amend_operation_code=G_AMEND_CODE_DELETED) ;
113
114
115 BEGIN
116
117 IF (l_debug = 'Y') THEN
118 okc_debug.log('600: Entered create_article', 2);
119 END IF;
120
121 -- Standard Start of API savepoint
122 SAVEPOINT g_create_article_GRP;
123 -- Standard call to check for call compatibility.
124 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END IF;
127 -- Initialize message list if p_init_msg_list is set to TRUE.
128 IF FND_API.to_Boolean( p_init_msg_list ) THEN
129 FND_MSG_PUB.initialize;
130 END IF;
131 -- Initialize API return status to success
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 IF p_mode='AMEND' THEN
135
136 OPEN l_get_deleted_rec_csr;
137 FETCH l_get_deleted_rec_csr INTO l_del_cat_id;
138 CLOSE l_get_deleted_rec_csr;
139
140 IF l_del_cat_id IS NOT NULL THEN
141 l_amendment_operation_code:=G_AMEND_CODE_UPDATED;
142 l_amendment_description := p_amendment_description;
143 l_summary_amend_operation_code:=G_AMEND_CODE_UPDATED;
144 ELSE
145 l_amendment_operation_code:= G_AMEND_CODE_ADDED;
146 l_amendment_description := p_amendment_description;
147 l_summary_amend_operation_code:=OKC_TERMS_UTIL_PVT.get_summary_amend_code(p_existing_summary_code =>NULL,
148 p_existing_operation_code=>NULL,
149 p_amend_operation_code=> G_AMEND_CODE_ADDED);
150
151 END IF;
152 ELSE
153 l_amendment_operation_code:= NULL;
154 l_amendment_description := NULL;
155 l_summary_amend_operation_code:=NULL;
156 END IF;
157
158 IF l_del_cat_id IS NOT NULL THEN
159 OKC_K_ARTICLES_GRP.delete_article(
160 p_api_version => 1,
161 p_init_msg_list => FND_API.G_FALSE,
162 p_validate_commit => FND_API.G_FALSE,
163 p_validation_string => NULL,
164 p_commit => FND_API.G_FALSE,
165 p_mode => 'NORMAL',
166 x_return_status => x_return_status,
167 x_msg_count => x_msg_count,
168 x_msg_data => x_msg_data,
169 p_super_user_yn => 'N',
170 p_amendment_description => NULL,
171 p_id => l_del_cat_id,
172 p_object_version_number => NULL
173 );
174 --------------------------------------------
175 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
177 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
178 RAISE FND_API.G_EXC_ERROR ;
179 END IF;
180 --------------------------------------------
181 END IF;
182
183
184 --------------------------------------------
185 -- Calling Simple API for Creating A Row
186 --------------------------------------------
187 OKC_K_ARTICLES_PVT.insert_row(
188 p_validation_level => p_validation_level,
189 x_return_status => x_return_status,
190 p_id => p_id,
191 p_sav_sae_id => p_sav_sae_id,
192 p_cat_type => p_cat_type, --Bug 3341342
193 p_document_type => p_document_type,
194 p_document_id => p_document_id,
195 p_cle_id => p_cle_id,
196 p_source_flag => p_source_flag,
197 p_mandatory_yn => p_mandatory_yn,
198 p_scn_id => p_scn_id,
199 p_label => p_label,
200 p_amendment_description => l_amendment_description,
201 p_amendment_operation_code => l_amendment_operation_code,
202 p_summary_amend_operation_code => l_summary_amend_operation_code,
203 p_article_version_id => p_article_version_id,
204 p_change_nonstd_yn => p_change_nonstd_yn,
205 p_orig_system_reference_code => p_orig_system_reference_code,
206 p_orig_system_reference_id1 => p_orig_system_reference_id1,
207 p_orig_system_reference_id2 => p_orig_system_reference_id2,
208 p_display_sequence => p_display_sequence,
209 p_attribute_category => p_attribute_category,
210 p_attribute1 => p_attribute1,
211 p_attribute2 => p_attribute2,
212 p_attribute3 => p_attribute3,
213 p_attribute4 => p_attribute4,
214 p_attribute5 => p_attribute5,
215 p_attribute6 => p_attribute6,
216 p_attribute7 => p_attribute7,
217 p_attribute8 => p_attribute8,
218 p_attribute9 => p_attribute9,
219 p_attribute10 => p_attribute10,
220 p_attribute11 => p_attribute11,
221 p_attribute12 => p_attribute12,
222 p_attribute13 => p_attribute13,
223 p_attribute14 => p_attribute14,
224 p_attribute15 => p_attribute15,
225 p_print_text_yn => p_print_text_yn,
226 p_ref_article_id => p_ref_article_id,
227 p_ref_article_version_id => p_ref_article_version_id,
228 x_id => x_id
229 );
230 --------------------------------------------
231 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
233 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
234 RAISE FND_API.G_EXC_ERROR ;
235 END IF;
236 --------------------------------------------
237 IF p_document_type <> 'TEMPLATE' THEN
238
239 IF (l_debug = 'Y') THEN
240 okc_debug.log('120: Variables of article '||p_sav_sae_id||' is being created',2);
241 END IF;
242
243 FOR l_article_var_rec IN l_article_var_csr(p_article_version_id) LOOP
244 OKC_K_ART_VARIABLES_PVT.insert_row(
245 p_validation_level => p_validation_level,
246 x_return_status => x_return_status,
247 p_cat_id => x_id,
248 p_variable_code => l_article_var_rec.variable_code,
249 p_variable_type => l_article_var_rec.variable_type,
250 p_external_yn => l_article_var_rec.external_yn,
251 p_variable_value_id => NULL,
252 p_variable_value => NULL,
253 p_attribute_value_set_id => l_article_var_rec.value_set_id,
254 x_cat_id => l_cat_id,
255 x_variable_code => l_variable_code
256 );
257 --------------------------------------------
258 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
260 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
261 RAISE FND_API.G_EXC_ERROR ;
262 END IF;
263 --------------------------------------------
264 IF (l_debug = 'Y') THEN
265 okc_debug.log('130: Variables '||l_variable_code||' for cat_id '||l_cat_id||' is created',2);
266 END IF;
267 END LOOP;
268 END IF;
269
270 -- Standard check of p_commit
271 IF FND_API.To_Boolean( p_commit ) THEN
272 COMMIT WORK;
273 END IF;
274 -- Standard call to get message count and if count is 1, get message info.
275 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
276
277 IF (l_debug = 'Y') THEN
278 okc_debug.log('700: Leaving create_article', 2);
279 END IF;
280
281 EXCEPTION
282 WHEN FND_API.G_EXC_ERROR THEN
283 IF (l_debug = 'Y') THEN
284 okc_debug.log('800: Leaving create_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
285 END IF;
286
287 IF l_article_var_csr%ISOPEN THEN
288 CLOSE l_article_var_csr;
289 END IF;
290
291 ROLLBACK TO g_create_article_GRP;
292 x_return_status := G_RET_STS_ERROR ;
293 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
294
295 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
296 IF (l_debug = 'Y') THEN
297 okc_debug.log('900: Leaving create_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
298 END IF;
299
300 IF l_article_var_csr%ISOPEN THEN
301 CLOSE l_article_var_csr;
302 END IF;
303
304
305 ROLLBACK TO g_create_article_GRP;
306 x_return_status := G_RET_STS_UNEXP_ERROR ;
307 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
308
309 WHEN OTHERS THEN
310 IF (l_debug = 'Y') THEN
311 okc_debug.log('1000: Leaving create_article because of EXCEPTION: '||sqlerrm, 2);
312 END IF;
313
314 IF l_article_var_csr%ISOPEN THEN
315 CLOSE l_article_var_csr;
316 END IF;
317
318 ROLLBACK TO g_create_article_GRP;
319 x_return_status := G_RET_STS_UNEXP_ERROR ;
320 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
321 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
322 END IF;
323 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
324
325 END create_article;
326 ---------------------------------------
327 -- PROCEDURE validate_row --
328 ---------------------------------------
329 PROCEDURE validate_row(
330 p_api_version IN NUMBER,
331 p_init_msg_list IN VARCHAR2 ,
332 p_validation_level IN NUMBER,
333 x_return_status OUT NOCOPY VARCHAR2,
334 x_msg_count OUT NOCOPY NUMBER,
335 x_msg_data OUT NOCOPY VARCHAR2,
336 p_id IN NUMBER,
337 p_sav_sae_id IN NUMBER,
338 p_document_type IN VARCHAR2,
339 p_document_id IN NUMBER,
340 p_cle_id IN NUMBER,
341 p_source_flag IN VARCHAR2,
342 p_mandatory_yn IN VARCHAR2,
343 p_scn_id IN NUMBER,
344 p_label IN VARCHAR2,
345 p_amendment_description IN VARCHAR2,
346 p_amendment_operation_code IN VARCHAR2,
347 p_article_version_id IN NUMBER,
348 p_change_nonstd_yn IN VARCHAR2,
349 p_orig_system_reference_code IN VARCHAR2,
350 p_orig_system_reference_id1 IN NUMBER,
351 p_orig_system_reference_id2 IN NUMBER,
352 p_display_sequence IN NUMBER,
353 p_attribute_category IN VARCHAR2 := NULL,
354 p_attribute1 IN VARCHAR2 := NULL,
355 p_attribute2 IN VARCHAR2 := NULL,
356 p_attribute3 IN VARCHAR2 := NULL,
357 p_attribute4 IN VARCHAR2 := NULL,
358 p_attribute5 IN VARCHAR2 := NULL,
359 p_attribute6 IN VARCHAR2 := NULL,
360 p_attribute7 IN VARCHAR2 := NULL,
361 p_attribute8 IN VARCHAR2 := NULL,
362 p_attribute9 IN VARCHAR2 := NULL,
363 p_attribute10 IN VARCHAR2 := NULL,
364 p_attribute11 IN VARCHAR2 := NULL,
365 p_attribute12 IN VARCHAR2 := NULL,
366 p_attribute13 IN VARCHAR2 := NULL,
367 p_attribute14 IN VARCHAR2 := NULL,
368 p_attribute15 IN VARCHAR2 := NULL,
369 p_print_text_yn IN VARCHAR2 := 'N',
370 p_summary_amend_operation_code IN VARCHAR2 := NULL,
371 p_ref_article_id IN NUMBER := NULL,
372 p_ref_article_version_id IN NUMBER := NULL,
373 p_object_version_number IN NUMBER
374 ) IS
375 l_api_version CONSTANT NUMBER := 1;
376 l_api_name CONSTANT VARCHAR2(30) := 'g_validate_row';
377
378 BEGIN
379
380 IF (l_debug = 'Y') THEN
381 okc_debug.log('100: Entered validate_row', 2);
382 END IF;
383
384 -- Standard Start of API savepoint
385 SAVEPOINT g_validate_row_GRP;
386 -- Standard call to check for call compatibility.
387 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
389 END IF;
390 -- Initialize message list if p_init_msg_list is set to TRUE.
391 IF FND_API.to_Boolean( p_init_msg_list ) THEN
392 FND_MSG_PUB.initialize;
393 END IF;
394 -- Initialize API return status to success
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 --------------------------------------------
398 -- Calling Simple API for Validation
399 --------------------------------------------
400 OKC_K_ARTICLES_PVT.Validate_Row(
401 p_validation_level => p_validation_level,
402 x_return_status => x_return_status,
403 p_id => p_id,
404 p_sav_sae_id => p_sav_sae_id,
405 p_document_type => p_document_type,
406 p_document_id => p_document_id,
407 p_cle_id => p_cle_id,
408 p_source_flag => p_source_flag,
409 p_mandatory_yn => p_mandatory_yn,
410 p_scn_id => p_scn_id,
411 p_label => p_label,
412 p_amendment_description => p_amendment_description,
413 p_amendment_operation_code => p_amendment_operation_code,
414 p_article_version_id => p_article_version_id,
415 p_change_nonstd_yn => p_change_nonstd_yn,
416 p_orig_system_reference_code => p_orig_system_reference_code,
417 p_orig_system_reference_id1 => p_orig_system_reference_id1,
418 p_orig_system_reference_id2 => p_orig_system_reference_id2,
419 p_display_sequence => p_display_sequence,
420 p_attribute_category => p_attribute_category,
421 p_attribute1 => p_attribute1,
422 p_attribute2 => p_attribute2,
423 p_attribute3 => p_attribute3,
424 p_attribute4 => p_attribute4,
425 p_attribute5 => p_attribute5,
426 p_attribute6 => p_attribute6,
427 p_attribute7 => p_attribute7,
428 p_attribute8 => p_attribute8,
429 p_attribute9 => p_attribute9,
430 p_attribute10 => p_attribute10,
431 p_attribute11 => p_attribute11,
432 p_attribute12 => p_attribute12,
433 p_attribute13 => p_attribute13,
434 p_attribute14 => p_attribute14,
435 p_attribute15 => p_attribute15,
436 p_print_text_yn => p_print_text_yn,
437 p_summary_amend_operation_code => p_summary_amend_operation_code,
438 p_ref_article_id => p_ref_article_id,
439 p_ref_article_version_id => p_ref_article_version_id,
440 p_object_version_number => p_object_version_number
441 );
442 --------------------------------------------
443 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
445 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
446 RAISE FND_API.G_EXC_ERROR ;
447 END IF;
448 --------------------------------------------
449
450 -- Standard call to get message count and if count is 1, get message info.
451 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
452
453 IF (l_debug = 'Y') THEN
454 okc_debug.log('200: Leaving validate_row', 2);
455 END IF;
456
457 EXCEPTION
458 WHEN FND_API.G_EXC_ERROR THEN
459 IF (l_debug = 'Y') THEN
460 okc_debug.log('300: Leaving Validate_Row: OKC_API.G_EXCEPTION_ERROR Exception', 2);
461 END IF;
462 ROLLBACK TO g_validate_row_GRP;
463 x_return_status := G_RET_STS_ERROR ;
464 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
465
466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
467 IF (l_debug = 'Y') THEN
468 okc_debug.log('400: Leaving Validate_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
469 END IF;
470 ROLLBACK TO g_validate_row_GRP;
471 x_return_status := G_RET_STS_UNEXP_ERROR ;
472 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
473
474 WHEN OTHERS THEN
475 IF (l_debug = 'Y') THEN
476 okc_debug.log('500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
477 END IF;
478
479 ROLLBACK TO g_validate_row_GRP;
480 x_return_status := G_RET_STS_UNEXP_ERROR ;
481 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
482 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
483 END IF;
484 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
485
486 END validate_row;
487
488 ---------------------------------------------------------------------------
489 -- PROCEDURE lock_row
490 ---------------------------------------------------------------------------
491 PROCEDURE lock_row(
492 p_api_version IN NUMBER,
493 p_init_msg_list IN VARCHAR2 ,
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2,
497 p_id IN NUMBER,
498 p_object_version_number IN NUMBER
499 ) IS
500 l_api_version CONSTANT NUMBER := 1;
501 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_row';
502 BEGIN
503
504 IF (l_debug = 'Y') THEN
505 okc_debug.log('1100: Entered lock_row', 2);
506 END IF;
507
508 -- Standard Start of API savepoint
509 SAVEPOINT g_lock_row_GRP;
510 -- Standard call to check for call compatibility.
511 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
513 END IF;
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 -- Initialize API return status to success
519 x_return_status := FND_API.G_RET_STS_SUCCESS;
520
521 --------------------------------------------
522 -- Calling Simple API for Locking A Row
523 --------------------------------------------
524 OKC_K_ARTICLES_PVT.lock_row(
525 x_return_status => x_return_status,
526 p_id => p_id,
527 p_object_version_number => p_object_version_number
528 );
529 --------------------------------------------
530 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
532 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
533 RAISE FND_API.G_EXC_ERROR ;
534 END IF;
535 --------------------------------------------
536
537 -- Standard call to get message count and if count is 1, get message info.
538 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
539
540 IF (l_debug = 'Y') THEN
541 okc_debug.log('1200: Leaving lock_row', 2);
542 END IF;
543
544 EXCEPTION
545 WHEN FND_API.G_EXC_ERROR THEN
546 IF (l_debug = 'Y') THEN
547 okc_debug.log('1300: Leaving lock_Row: OKC_API.G_EXCEPTION_ERROR Exception', 2);
548 END IF;
549 ROLLBACK TO g_lock_row_GRP;
550 x_return_status := G_RET_STS_ERROR ;
551 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
552
553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
554 IF (l_debug = 'Y') THEN
555 okc_debug.log('1400: Leaving lock_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
556 END IF;
557 ROLLBACK TO g_lock_row_GRP;
558 x_return_status := G_RET_STS_UNEXP_ERROR ;
559 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
560
561 WHEN OTHERS THEN
562 IF (l_debug = 'Y') THEN
563 okc_debug.log('1500: Leaving lock_Row because of EXCEPTION: '||sqlerrm, 2);
564 END IF;
565
566 ROLLBACK TO g_lock_row_GRP;
567 x_return_status := G_RET_STS_UNEXP_ERROR ;
568 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
569 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
570 END IF;
571 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
572
573 END lock_row;
574 ---------------------------------------------------------------------------
575 -- PROCEDURE update_article
576 ---------------------------------------------------------------------------
577 PROCEDURE update_article(
578 p_api_version IN NUMBER,
579 p_init_msg_list IN VARCHAR2 ,
580 p_validation_level IN NUMBER,
581 p_validate_commit IN VARCHAR2,
582 p_validation_string IN VARCHAR2,
583 p_commit IN VARCHAR2,
584 p_mode IN VARCHAR2,
585 x_return_status OUT NOCOPY VARCHAR2,
586 x_msg_count OUT NOCOPY NUMBER,
587 x_msg_data OUT NOCOPY VARCHAR2,
588 p_id IN NUMBER,
589 p_sav_sae_id IN NUMBER,
590 p_document_type IN VARCHAR2,
591 p_document_id IN NUMBER,
592 p_cle_id IN NUMBER,
593 p_source_flag IN VARCHAR2,
594 p_mandatory_yn IN VARCHAR2,
595 p_scn_id IN NUMBER,
596 p_label IN VARCHAR2,
597 p_amendment_description IN VARCHAR2,
598 p_article_version_id IN NUMBER,
599 p_change_nonstd_yn IN VARCHAR2,
600 p_orig_system_reference_code IN VARCHAR2,
601 p_orig_system_reference_id1 IN NUMBER,
602 p_orig_system_reference_id2 IN NUMBER,
603 p_display_sequence IN NUMBER,
604 p_attribute_category IN VARCHAR2 ,
605 p_attribute1 IN VARCHAR2 ,
606 p_attribute2 IN VARCHAR2 ,
607 p_attribute3 IN VARCHAR2 ,
608 p_attribute4 IN VARCHAR2 ,
609 p_attribute5 IN VARCHAR2 ,
610 p_attribute6 IN VARCHAR2 ,
611 p_attribute7 IN VARCHAR2 ,
612 p_attribute8 IN VARCHAR2 ,
613 p_attribute9 IN VARCHAR2 ,
614 p_attribute10 IN VARCHAR2 ,
615 p_attribute11 IN VARCHAR2 ,
616 p_attribute12 IN VARCHAR2 ,
617 p_attribute13 IN VARCHAR2 ,
618 p_attribute14 IN VARCHAR2 ,
619 p_attribute15 IN VARCHAR2 ,
620 p_print_text_yn IN VARCHAR2 ,
621 p_ref_article_id IN NUMBER ,
622 p_ref_article_version_id IN NUMBER ,
623 p_object_version_number IN NUMBER
624
625 ) IS
626
627 l_api_version CONSTANT NUMBER := 1;
628 l_api_name CONSTANT VARCHAR2(30) := 'g_update_article';
629 l_ok_to_commit VARCHAR2(1);
630 l_document_id NUMBER;
631 l_cle_id NUMBER;
632 l_document_type VARCHAR2(30);
633 l_article_version_id NUMBER;
634 l_standard_yn VARCHAR2(1);
635 l_sync_variable VARCHAR2(1) := FND_API.G_FALSE;
636 l_amendment_operation_code OKC_K_ARTICLES_B.AMENDMENT_OPERATION_CODE%TYPE;
637 l_summary_amend_operation_code OKC_K_ARTICLES_B.AMENDMENT_OPERATION_CODE%TYPE;
638 l_existing_summary_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
639 l_existing_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
640 l_amendment_description OKC_K_ARTICLES_B.AMENDMENT_DESCRIPTION%TYPE;
641 x_cat_id okc_k_art_variables.cat_id%type;
642 x_variable_code okc_k_art_variables.variable_code%type;
643
644 CURSOR l_document_id_csr IS
645 SELECT DOCUMENT_ID,DOCUMENT_TYPE
646 FROM OKC_K_ARTIClES_B
647 WHERE ID=P_ID;
648
649 CURSOR l_art_version_csr IS
650 SELECT ARTICLE_VERSION_ID,standard_yn
651 FROM OKC_K_ARTIClES_B kart,okc_articles_all art
652 WHERE ID=P_ID
653 AND art.article_id=kart.sav_sae_id;
654
655 CURSOR l_create_variable_csr is
656 SELECT KART.ID CAT_ID,
657 VAR.VARIABLE_CODE,
658 BUSVAR.VARIABLE_TYPE,
659 BUSVAR.EXTERNAL_YN,
660 BUSVAR.VALUE_SET_ID,
661 NULL VARIABLE_VALUE,
662 NULL VARIABLE_VALUE_ID
663 FROM OKC_ARTICLE_VARIABLES VAR,
664 OKC_K_ARTICLES_B KART,
665 OKC_BUS_VARIABLES_B BUSVAR
666 WHERE KART.ARTICLE_VERSION_ID=VAR.ARTICLE_VERSION_ID
667 AND KART.ID = P_ID
668 AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE AND NOT EXISTS
669 ( SELECT 'X' FROM OKC_K_ART_VARIABLES
670 WHERE CAT_ID=KART.ID
671 AND VARIABLE_CODE = BUSVAR.VARIABLE_CODE);
672
673 CURSOR l_update_variable_csr is
674 SELECT KART.ID CAT_ID,
675 VAR.VARIABLE_CODE,
676 BUSVAR.VARIABLE_TYPE,
677 BUSVAR.EXTERNAL_YN,
678 BUSVAR.VALUE_SET_ID,
679 ARTVAR.VARIABLE_VALUE,
680 ARTVAR.VARIABLE_VALUE_ID,
681 ARTVAR.OBJECT_VERSION_NUMBER
682 FROM OKC_ARTICLE_VARIABLES VAR,
683 OKC_K_ARTICLES_B KART,
684 OKC_BUS_VARIABLES_B BUSVAR,
685 OKC_K_ART_VARIABLES ARTVAR
686 WHERE KART.ARTICLE_VERSION_ID=VAR.ARTICLE_VERSION_ID
687 AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE
688 AND ARTVAR.CAT_ID = KART.ID
689 AND ARTVAR.VARIABLE_CODE = VAR.VARIABLE_CODE
690 AND KART.ID = P_ID
691 AND ARTVAR.VARIABLE_VALUE IS NULL;
692
693 CURSOR l_delete_variable_csr is
694 SELECT ARTVAR.CAT_ID,
695 ARTVAR.VARIABLE_CODE,
696 ARTVAR.OBJECT_VERSION_NUMBER
697 FROM OKC_K_ART_VARIABLES ARTVAR,
698 OKC_K_ARTICLES_B KART
699 WHERE KART.ID = ARTVAR.CAT_ID
700 AND KART.ID = P_ID
701 AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VARIABLES VAR
702 WHERE VAR.ARTICLE_VERSION_ID = KART.ARTICLE_VERSION_ID
703 AND VAR.VARIABLE_CODE = ARTVAR.VARIABLE_CODE );
704
705 CURSOR l_get_summary_code_csr IS
706 SELECT SUMMARY_AMEND_OPERATION_CODE ,amendment_operation_code
707 FROM OKC_K_ARTICLES_B
708 WHERE ID=P_ID;
709
710
711 BEGIN
712
713 IF (l_debug = 'Y') THEN
714 okc_debug.log('1600: Entered update_article', 2);
715 END IF;
716
717 -- Standard Start of API savepoint
718 SAVEPOINT g_update_article_grp;
719 -- Standard call to check for call compatibility.
720 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
722 END IF;
723 -- Initialize message list if p_init_msg_list is set to TRUE.
724 IF FND_API.to_Boolean( p_init_msg_list ) THEN
725 FND_MSG_PUB.initialize;
726 END IF;
727
728 IF FND_API.To_Boolean( p_validate_commit ) THEN
729
730 IF p_document_id IS NULL OR p_document_type IS NULL THEN
731 OPEN l_document_id_csr;
732 FETCH l_document_id_csr into l_document_id,l_document_type;
733 CLOSE l_document_id_csr;
734 END IF;
735
736 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
737 p_api_version => l_api_version,
738 p_init_msg_list => FND_API.G_FALSE,
739 p_doc_type => l_document_type,
740 p_doc_id => l_document_id,
741 p_validation_string => p_validation_string,
742 x_return_status => x_return_status,
743 x_msg_data => x_msg_data,
744 x_msg_count => x_msg_count) ) THEN
745
746 IF (l_debug = 'Y') THEN
747 okc_debug.log('700: Issue with document header Record.Cannot commit', 2);
748 END IF;
749 RAISE FND_API.G_EXC_ERROR ;
750 END IF;
751 END IF;
752 -- Initialize API return status to success
753 x_return_status := FND_API.G_RET_STS_SUCCESS;
754
755 -- Check if this update going to update article version also.If yes then article variables will be created.
756
757 OPEN l_art_version_csr;
758 FETCH l_art_version_csr into l_article_version_id,l_standard_yn;
759 CLOSE l_art_version_csr;
760
761 IF (p_article_version_id IS NOT NULL AND
762 p_article_version_id <>FND_API.G_MISS_NUM AND
763 l_article_version_id <> p_article_version_id ) OR l_standard_yn='N' THEN
764
765 l_sync_variable := FND_API.G_TRUE;
766
767 END IF;
768
769 IF p_mode='AMEND' THEN
770
771 l_amendment_description := p_amendment_description;
772 OPEN l_get_summary_code_csr;
773 FETCH l_get_summary_code_csr INTO l_existing_summary_code,l_existing_operation_code;
774 CLOSE l_get_summary_code_csr;
775 l_amendment_operation_code := nvl(l_existing_operation_code,G_AMEND_CODE_UPDATED);
776
777 l_summary_amend_operation_code := OKC_TERMS_UTIL_PVT.get_summary_amend_code(p_existing_summary_code =>l_existing_summary_code,
778 p_existing_operation_code=>l_existing_operation_code,
779 p_amend_operation_code=>G_AMEND_CODE_UPDATED);
780
781 ELSE
782 l_amendment_operation_code:= NULL;
783 l_amendment_description := NULL;
784 l_summary_amend_operation_code := NULL;
785 END IF;
786 --------------------------------------------
787 -- Calling Simple API for Updating A Row
788 --------------------------------------------
789 OKC_K_ARTICLES_PVT.Update_Row(
790 p_validation_level => p_validation_level,
791 x_return_status => x_return_status,
792 p_id => p_id,
793 p_sav_sae_id => p_sav_sae_id,
794 p_document_type => p_document_type,
795 p_document_id => p_document_id,
796 p_cle_id => p_cle_id,
797 p_source_flag => p_source_flag,
798 p_mandatory_yn => p_mandatory_yn,
799 p_scn_id => p_scn_id,
800 p_label => p_label,
801 p_amendment_description => l_amendment_description,
802 p_amendment_operation_code => l_amendment_operation_code,
803 p_summary_amend_operation_code => l_summary_amend_operation_code,
804 p_article_version_id => p_article_version_id,
805 p_change_nonstd_yn => p_change_nonstd_yn,
806 p_orig_system_reference_code => p_orig_system_reference_code,
807 p_orig_system_reference_id1 => p_orig_system_reference_id1,
808 p_orig_system_reference_id2 => p_orig_system_reference_id2,
809 p_display_sequence => p_display_sequence,
810 p_attribute_category => p_attribute_category,
811 p_attribute1 => p_attribute1,
812 p_attribute2 => p_attribute2,
813 p_attribute3 => p_attribute3,
814 p_attribute4 => p_attribute4,
815 p_attribute5 => p_attribute5,
816 p_attribute6 => p_attribute6,
817 p_attribute7 => p_attribute7,
818 p_attribute8 => p_attribute8,
819 p_attribute9 => p_attribute9,
820 p_attribute10 => p_attribute10,
821 p_attribute11 => p_attribute11,
822 p_attribute12 => p_attribute12,
823 p_attribute13 => p_attribute13,
824 p_attribute14 => p_attribute14,
825 p_attribute15 => p_attribute15,
826 p_print_text_yn => p_print_text_yn,
827 p_ref_article_id => p_ref_article_id,
828 p_ref_article_version_id => p_ref_article_version_id,
829 p_object_version_number => p_object_version_number
830 );
831 --------------------------------------------
832 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
834 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
835 RAISE FND_API.G_EXC_ERROR ;
836 END IF;
837 --------------------------------------------
838 IF FND_API.To_Boolean(l_sync_variable) THEN
839
840 -- If Article is changed then
841 -- 1.Create record in okc_k_art_variables for those variables which are present in new article version but were absent in old article version.
842 -- 2.Delete record in okc_k_art_variables for those variables which are present in old article version but absent in new article version.
843 -- 3.Update record in okc_k_art_variables for those variables which are present in both version and for which value has been entered previously.
844
845
846 -- creating new article variables
847 IF (l_debug = 'Y') THEN
848 okc_debug.log('1710: Creating New article variables for article version '|| l_article_version_id, 2);
849 END IF;
850
851 FOR l_create_variable_rec IN l_create_variable_csr LOOP
852 OKC_K_ART_VARIABLES_PVT.insert_row(
853 x_return_status => x_return_status,
854 p_cat_id => p_id,
855 p_variable_code => l_create_variable_rec.variable_code,
856 p_variable_type => l_create_variable_rec.variable_type,
857 p_external_yn => l_create_variable_rec.external_yn,
858 p_variable_value_id => l_create_variable_rec.variable_value_id,
859 p_variable_value => l_create_variable_rec.variable_value,
860 p_attribute_value_set_id => l_create_variable_rec.value_set_id,
861 x_cat_id => x_cat_id,
862 x_variable_code => x_variable_code
863 );
864
865 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
867 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
868 RAISE FND_API.G_EXC_ERROR ;
869 END IF;
870 END LOOP;
871
872 -- deleting old article variables
873 IF (l_debug = 'Y') THEN
874 okc_debug.log('1710: Deleing old article variables for article version '|| p_article_version_id||' which are not present in new article version', 2);
875 END IF;
876
877 FOR l_delete_variable_rec IN l_delete_variable_csr LOOP
878 OKC_K_ART_VARIABLES_PVT.delete_row(
879 x_return_status => x_return_status,
880 p_cat_id => l_delete_variable_rec.cat_id,
881 p_variable_code => l_delete_variable_rec.variable_code,
882 p_object_version_number => l_delete_variable_rec.object_version_number
883 );
884
885 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
887 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
888 RAISE FND_API.G_EXC_ERROR ;
889 END IF;
890 END LOOP;
891
892
893 -- Update old article variable values
894 IF (l_debug = 'Y') THEN
895 okc_debug.log('1710: Updating article variables values for article version '|| p_article_version_id, 2);
896 END IF;
897
898 FOR l_update_variable_rec IN l_update_variable_csr LOOP
899
900 OKC_K_ART_VARIABLES_PVT.update_row(
901 x_return_status => x_return_status,
902 p_cat_id => p_id,
903 p_variable_code => l_update_variable_rec.variable_code,
904 p_variable_type => l_update_variable_rec.variable_type,
905 p_external_yn => l_update_variable_rec.external_yn,
906 p_variable_value_id => l_update_variable_rec.variable_value_id,
907 p_variable_value => l_update_variable_rec.variable_value,
908 p_attribute_value_set_id => l_update_variable_rec.value_set_id,
909 p_object_version_number => l_update_variable_rec.object_version_number
910 );
911
912 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
913 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
914 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
915 RAISE FND_API.G_EXC_ERROR ;
916 END IF;
917 END LOOP;
918
919 END IF;
920
921 -- Standard check of p_commit
922 IF FND_API.To_Boolean( p_commit ) THEN
923 COMMIT WORK;
924 END IF;
925 -- Standard call to get message count and if count is 1, get message info.
926 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
927
928 IF (l_debug = 'Y') THEN
929 okc_debug.log('1800: Leaving update_article', 2);
930 END IF;
931
932 EXCEPTION
933 WHEN FND_API.G_EXC_ERROR THEN
934 IF (l_debug = 'Y') THEN
935 okc_debug.log('1900: Leaving update_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
936 END IF;
937
938 IF l_document_id_csr%ISOPEN THEN
939 CLOSE l_document_id_csr;
940 END IF;
941
942 IF l_art_version_csr%ISOPEN THEN
943 CLOSE l_art_version_csr;
944 END IF;
945
946 IF l_update_variable_csr%ISOPEN THEN
947 CLOSE l_update_variable_csr;
948 END IF;
949
950 IF l_delete_variable_csr%ISOPEN THEN
951 CLOSE l_delete_variable_csr;
952 END IF;
953
954 IF l_create_variable_csr%ISOPEN THEN
955 CLOSE l_create_variable_csr;
956 END IF;
957
958 ROLLBACK TO g_update_article_grp;
959 x_return_status := G_RET_STS_ERROR ;
960 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
961
962 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
963 IF (l_debug = 'Y') THEN
964 okc_debug.log('2000: Leaving update_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
965 END IF;
966
967 IF l_document_id_csr%ISOPEN THEN
968 CLOSE l_document_id_csr;
969 END IF;
970
971 IF l_art_version_csr%ISOPEN THEN
972 CLOSE l_art_version_csr;
973 END IF;
974
975 IF l_update_variable_csr%ISOPEN THEN
976 CLOSE l_update_variable_csr;
977 END IF;
978
979 IF l_delete_variable_csr%ISOPEN THEN
980 CLOSE l_delete_variable_csr;
981 END IF;
982
983 IF l_create_variable_csr%ISOPEN THEN
984 CLOSE l_create_variable_csr;
985 END IF;
986
987 ROLLBACK TO g_update_article_grp;
988 x_return_status := G_RET_STS_UNEXP_ERROR ;
989 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
990
991 WHEN OTHERS THEN
992 IF (l_debug = 'Y') THEN
993 okc_debug.log('2100: Leaving update_article because of EXCEPTION: '||sqlerrm, 2);
994 END IF;
995
996 IF l_document_id_csr%ISOPEN THEN
997 CLOSE l_document_id_csr;
998 END IF;
999
1000 IF l_art_version_csr%ISOPEN THEN
1001 CLOSE l_art_version_csr;
1002 END IF;
1003
1004 IF l_update_variable_csr%ISOPEN THEN
1005 CLOSE l_update_variable_csr;
1006 END IF;
1007
1008 IF l_delete_variable_csr%ISOPEN THEN
1009 CLOSE l_delete_variable_csr;
1010 END IF;
1011
1012 IF l_create_variable_csr%ISOPEN THEN
1013 CLOSE l_create_variable_csr;
1014 END IF;
1015
1016 ROLLBACK TO g_update_article_grp;
1017 x_return_status := G_RET_STS_UNEXP_ERROR ;
1018 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1019 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1020 END IF;
1021 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1022
1023 END update_article;
1024
1025 ---------------------------------------------------------------------------
1026 -- PROCEDURE delete_article
1027 ---------------------------------------------------------------------------
1028 PROCEDURE delete_article(
1029 p_api_version IN NUMBER,
1030 p_init_msg_list IN VARCHAR2,
1031 p_validate_commit IN VARCHAR2,
1032 p_validation_string IN VARCHAR2,
1033 p_commit IN VARCHAR2,
1034 p_mode IN VARCHAR2,
1035
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2,
1039 p_super_user_yn IN VARCHAR2,
1040 p_amendment_description IN VARCHAR2,
1041 p_print_text_yn IN VARCHAR2,
1042 p_id IN NUMBER,
1043 p_object_version_number IN NUMBER,
1044 p_mandatory_clause_delete IN VARCHAR2
1045 ) IS
1046 l_api_version CONSTANT NUMBER := 1;
1047 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_article';
1048 l_ok_to_commit VARCHAR2(1);
1049 l_document_id NUMBER;
1050 l_cle_id NUMBER;
1051 l_document_type VARCHAR2(30);
1052 l_mandatory_yn VARCHAR2(1);
1053 l_standard_yn VARCHAR2(1);
1054 l_amendment_operation_code OKC_K_ARTICLES_B.AMENDMENT_OPERATION_CODE%TYPE;
1055 l_amendment_description OKC_K_ARTICLES_B.AMENDMENT_DESCRIPTION%TYPE;
1056
1057 l_summary_amend_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
1058 l_existing_summary_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
1059 l_existing_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
1060 l_delete_rec BOOLEAN := FALSE;
1061
1062 CURSOR l_document_id_csr IS
1063 SELECT kart.DOCUMENT_ID,kart.DOCUMENT_TYPE,kart.mandatory_yn, art.standard_yn
1064 FROM OKC_K_ARTIClES_B kart, OKC_ARTICLES_ALL art
1065 WHERE kart.sav_sae_id = art.article_id
1066 AND ID=P_ID;
1067
1068 CURSOR l_get_summary_code_csr IS
1069 SELECT SUMMARY_AMEND_OPERATION_CODE,AMENDMENT_OPERATION_CODE
1070 FROM OKC_K_ARTICLES_B
1071 WHERE ID=P_ID;
1072
1073
1074 BEGIN
1075
1076 IF (l_debug = 'Y') THEN
1077 okc_debug.log('2200: Entered delete_article', 2);
1078 END IF;
1079
1080 -- Standard Start of API savepoint
1081 SAVEPOINT g_delete_article_grp;
1082 -- Standard call to check for call compatibility.
1083 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1084 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1085 END IF;
1086 -- Initialize message list if p_init_msg_list is set to TRUE.
1087 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1088 FND_MSG_PUB.initialize;
1089 END IF;
1090
1091 -- Initialize API return status to success
1092 x_return_status := FND_API.G_RET_STS_SUCCESS;
1093
1094
1095 OPEN l_document_id_csr;
1096 FETCH l_document_id_csr INTO l_document_id,l_document_type,l_mandatory_yn, l_standard_yn;
1097 CLOSE l_document_id_csr;
1098
1099 IF l_mandatory_yn='Y' and l_document_type<>'TEMPLATE' and p_super_user_yn='N' and p_mandatory_clause_delete='N' THEN
1100
1101 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1102 p_msg_name => 'OKC_DEL_MAND_ARTICLE');
1103
1104 raise FND_API.G_EXC_ERROR;
1105
1106 END IF;
1107
1108 -- if user does NOT have non-std access, don't allow delete of non-std articles
1109 IF (NOT fnd_function.test('OKC_TERMS_AUTHOR_NON_STD','N')) AND
1110 l_standard_yn ='N' THEN
1111 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1112 p_msg_name => 'OKC_DEL_NON_STD_ARTICLE');
1113
1114 raise FND_API.G_EXC_ERROR;
1115 END IF;
1116
1117
1118 IF FND_API.To_Boolean( p_validate_commit ) THEN
1119
1120
1121 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
1122 p_api_version => l_api_version,
1123 p_init_msg_list => FND_API.G_FALSE,
1124 p_doc_type => l_document_type,
1125 p_doc_id => l_document_id,
1126 p_validation_string => p_validation_string,
1127 x_return_status => x_return_status,
1128 x_msg_data => x_msg_data,
1129 x_msg_count => x_msg_count)
1130 ) THEN
1131
1132 IF (l_debug = 'Y') THEN
1133 okc_debug.log('2250: Issue with document header Record.Cannot commit', 2) ;
1134 END IF;
1135 RAISE FND_API.G_EXC_ERROR ;
1136 END IF;
1137 END IF;
1138
1139
1140 IF p_mode='AMEND' THEN
1141
1142 OPEN l_get_summary_code_csr;
1143 FETCH l_get_summary_code_csr INTO l_existing_summary_code,l_existing_operation_code;
1144 CLOSE l_get_summary_code_csr;
1145
1146 l_amendment_operation_code:=G_AMEND_CODE_DELETED;
1147 l_amendment_description := p_amendment_description;
1148
1149 IF nvl(l_existing_operation_code,'?') <> G_AMEND_CODE_ADDED THEN
1150
1151 l_summary_amend_operation_code := OKC_TERMS_UTIL_PVT.get_summary_amend_code(p_existing_summary_code =>l_existing_summary_code,
1152 p_existing_operation_code=>l_existing_operation_code,
1153 p_amend_operation_code=>G_AMEND_CODE_DELETED);
1154
1155 OKC_K_ARTICLES_PVT.Update_Row(
1156 x_return_status => x_return_status,
1157 p_id => p_id,
1158 p_amendment_operation_code => l_amendment_operation_code,
1159 p_amendment_description => l_amendment_description,
1160 p_print_text_yn => p_print_text_yn,
1161 p_summary_amend_operation_code => l_summary_amend_operation_code,
1162 p_object_version_number => NULL
1163 );
1164
1165 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1167 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1168 RAISE FND_API.G_EXC_ERROR ;
1169 END IF;
1170 ELSE
1171 l_delete_rec := TRUE;
1172 END IF;
1173 END IF;
1174
1175 IF p_mode<>'AMEND' or l_delete_rec THEN
1176 -- Delete Child records from OKC_K_ART_VARIABLES_TABLE
1177 IF (l_debug = 'Y') THEN
1178 okc_debug.log('2260: Deleting Child record from okc_k_art_varibles', 2) ;
1179 END IF;
1180
1181 OKC_K_ART_VARIABLES_PVT.delete_set(
1182 x_return_status => x_return_status,
1183 p_cat_id => p_id
1184 );
1185
1186 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1188 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1189 RAISE FND_API.G_EXC_ERROR ;
1190 END IF;
1191
1192 IF (l_debug = 'Y') THEN
1193 okc_debug.log('760: Deleting Record from okc_k_articles', 2) ;
1194 END IF;
1195 --------------------------------------------
1196 -- Calling Simple API for Deleting A Row
1197 --------------------------------------------
1198 OKC_K_ARTICLES_PVT.Delete_Row(
1199 x_return_status => x_return_status,
1200 p_id => p_id,
1201 p_object_version_number => p_object_version_number
1202 );
1203 --------------------------------------------
1204 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1206 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1207 RAISE FND_API.G_EXC_ERROR ;
1208 END IF;
1209 --------------------------------------------
1210 END IF;
1211 -- Standard check of p_commit
1212 IF FND_API.To_Boolean( p_commit ) THEN
1213 COMMIT WORK;
1214 END IF;
1215 -- Standard call to get message count and if count is 1, get message info.
1216 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1217
1218 IF (l_debug = 'Y') THEN
1219 okc_debug.log('2300: Leaving delete_article', 2);
1220 END IF;
1221
1222 EXCEPTION
1223 WHEN FND_API.G_EXC_ERROR THEN
1224 IF (l_debug = 'Y') THEN
1225 okc_debug.log('2400: Leaving delete_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1226 END IF;
1227
1228 IF l_document_id_csr%ISOPEN THEN
1229 CLOSE l_document_id_csr;
1230 END IF;
1231
1232
1233 ROLLBACK TO g_delete_article_grp;
1234 x_return_status := G_RET_STS_ERROR ;
1235 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1236
1237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1238 IF (l_debug = 'Y') THEN
1239 okc_debug.log('2500: Leaving delete_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1240 END IF;
1241
1242 IF l_document_id_csr%ISOPEN THEN
1243 CLOSE l_document_id_csr;
1244 END IF;
1245
1246
1247 ROLLBACK TO g_delete_article_grp;
1248 x_return_status := G_RET_STS_UNEXP_ERROR ;
1249 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1250
1251 WHEN OTHERS THEN
1252 IF (l_debug = 'Y') THEN
1253 okc_debug.log('2600: Leaving delete_article because of EXCEPTION: '||sqlerrm, 2);
1254 END IF;
1255
1256 IF l_document_id_csr%ISOPEN THEN
1257 CLOSE l_document_id_csr;
1258 END IF;
1259
1260
1261 ROLLBACK TO g_delete_article_grp;
1262 x_return_status := G_RET_STS_UNEXP_ERROR ;
1263 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1264 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1265 END IF;
1266 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1267
1268 END delete_article;
1269
1270
1271 END OKC_K_ARTICLES_GRP;