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