[Home] [Help]
PACKAGE BODY: APPS.OKC_ARTICLES_GRP
Source
1 PACKAGE BODY okc_articles_grp AS
2 /* $Header: OKCGARTB.pls 120.14 2011/07/12 10:13:56 serukull ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 G_GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
6 G_profile_doc_seq VARCHAR2(1) := fnd_profile.value('UNIQUE:SEQ_NUMBERS');
7 G_doc_category_code VARCHAR2(30) := substr(Fnd_Profile.Value('OKC_ARTICLE_DOC_SEQ_CATEGORY'),1,30) ;
8
9 ---------------------------------------------------------------------------
10 -- GLOBAL MESSAGE CONSTANTS
11 ---------------------------------------------------------------------------
12 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
13 ---------------------------------------------------------------------------
14 -- GLOBAL VARIABLES
15 ---------------------------------------------------------------------------
16 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_ARTICLES_GRP';
17 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
18
19 ------------------------------------------------------------------------------
20 -- GLOBAL CONSTANTS
21 ------------------------------------------------------------------------------
22 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
23 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
24 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
25 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
26 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
27
28 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
29 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
30 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
31
32 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
33 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
34 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
35
36 G_INVALID_VALUE CONSTANT varchar2(200) := OKC_API.G_INVALID_VALUE;
37 G_COL_NAME_TOKEN CONSTANT varchar2(200) := OKC_API.G_COL_NAME_TOKEN;
38 -- MOAC
39 --G_CURRENT_ORG_ID NUMBER := -99;
40 G_CURRENT_ORG_ID NUMBER ;
41 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
42 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
43 G_VAR_NOT_CLOSED EXCEPTION;
44 G_VAR_NOT_FOUND EXCEPTION;
45 G_VAR_NOT_FOUND_RET_STS CONSTANT varchar2(1) := 'V';
46 -- MOAC
47 /*
48 -- One Time fetch and cache the current Org.
49 CURSOR CUR_ORG_CSR IS
50 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
51 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
52 FROM DUAL;
53 */
54
55 ---------------------------------------
56 -- PROCEDURE This API restores the variable tags from the clause text overwritten by the JAVA Parser.
57 -- e.g. ;@ will be restored back to <@
58 ---------------------------------------
59
60 PROCEDURE special_char_decoders(
61 p_temp_article_text IN OUT NOCOPY Varchar2
62 ) IS
63
64 Begin
65 if p_temp_article_text is not null then
66 p_temp_article_text :=
67 replace(replace
68 (p_temp_article_text,'@;','@>'),';@','<@');
69
70 end if;
71 end special_char_decoders;
72
73 procedure check_length_n_append(
74 p_append_str IN VARCHAR2,
75 p_xml_tag IN VARCHAR2,
76 p_art_text_with_tags IN OUT NOCOPY VARCHAR2,
77 p_clob_large IN OUT NOCOPY CLOB,
78 x_return_status OUT NOCOPY VARCHAR2
79 ) IS
80
81 l_check_length INTEGER;
82 l_temp_article_text VARCHAR2(10000);
83 p_art_str_w_tags_length INTEGER := 0;
84 p_append_str_length INTEGER := 0;
85 p_xml_tag_length INTEGER := 0;
86
87
88 Begin
89
90 if p_append_str is not null then
91 -- changed the method call from length() to lengthb() to handle mutli byte chars
92 -- bug 3697706
93 p_append_str_length := lengthb(p_append_str);
94 --dbms_output.put_line(' ***IN CHECK LENGTH 2** ' || 'p_append_str_length = ' || p_append_str_length);
95
96 end if;
97 if p_xml_tag is not null
98 then
99 -- changed the method call from length() to lengthb() to handle mutli byte chars
100 -- bug 3697706
101 p_xml_tag_length := lengthb(p_xml_tag);
102 end if;
103 if p_art_text_with_tags is not null then
104 -- changed the method call from length() to lengthb() to handle mutli byte chars
105 -- bug 3697706
106 p_art_str_w_tags_length := lengthb(p_art_text_with_tags);
107 end if;
108 l_check_length := p_art_str_w_tags_length + p_append_str_length + p_xml_tag_length ;
109
110 -- dbms_output.put_line(' ***IN CHECK LENGTH 3** ' || 'l_check_length = --' || l_check_length);
111
112 if (l_check_length < 10000) -- comparison in bytes
113 then
114 --dbms_output.put_line(' ***IN CHECK LENGTH if combined sting is less than 4000 **');
115
116 p_art_text_with_tags := p_art_text_with_tags || p_append_str || p_xml_tag;
117
118
119 else
120 l_check_length := p_art_str_w_tags_length + p_append_str_length;
121 --dbms_output.put_line(' ***IN CHECK LENGTH length without xmltag **' ||
122 --l_check_length );
123
124 if (l_check_length >= 10000) -- comparison in bytes
125 then
126 if (p_art_str_w_tags_length > 0)
127 then
128
129 -- changed for Bug 3697706 using length() to get length in chars instead of
130 -- using the already calculated p_art_str_w_tags_length which is in bytes
131 --dbms_lob.writeappend ( p_clob_large, p_art_str_w_tags_length, p_art_text_with_tags);
132 dbms_lob.writeappend ( p_clob_large, length(p_art_text_with_tags), p_art_text_with_tags);
133
134 end if;
135 if (p_append_str_length > 0)
136 then
137 -- dbms_output.put_line(' ***IN CHECK LENGTH writing into clob 111**');
138
139 -- changed for Bug 3697706 using length() to get length in chars instead of
140 -- using the already calculated p_append_str_length which is in bytes
141 -- dbms_lob.writeappend ( p_clob_large, p_append_str_length, p_append_str);
142 dbms_lob.writeappend ( p_clob_large, length(p_append_str), p_append_str);
143
144 end if;
145 else
146 --dbms_output.put_line(' ***IN CHECK LENGTH writing into clob 222**');
147
148 -- changed for Bug 3697706 using length() to get length in chars instead of
149 -- using the already calculated l_check_length which is in bytes
150 --dbms_lob.writeappend ( p_clob_large,l_check_length , (p_art_text_with_tags || p_append_str));
151 dbms_lob.writeappend ( p_clob_large,length(p_art_text_with_tags || p_append_str) , (p_art_text_with_tags || p_append_str));
152
153 -- dbms_output.put_line(' ***IN CHECK LENGTH writing into clob 222**');
154
155 end if ;
156 p_art_text_with_tags := p_xml_tag;
157 end if;
158
159 EXCEPTION
160 when OTHERS
161 then
162 begin
163 x_return_status := G_RET_STS_UNEXP_ERROR;
164 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
165 p_msg_name => G_UNEXPECTED_ERROR,
166 p_token1 => G_SQLCODE_TOKEN,
167 p_token1_value => sqlcode,
168 p_token2 => G_SQLERRM_TOKEN,
169 p_token2_value => sqlerrm);
170
171 end;
172
173 end check_length_n_append;
174
175
176
177
178 procedure find_variables(
179 p_extracted_var_name IN VARCHAR2,
180 p_intent IN VARCHAR2,
181 p_language IN VARCHAR2,
182 p_batch_number IN VARCHAR2 DEFAULT NULL,
183 x_variables_tbl IN OUT NOCOPY variable_code_tbl_type,
184 p_xml_tag OUT NOCOPY VARCHAR,
185 x_return_status OUT NOCOPY VARCHAR
186 ) IS
187
188 G_VAR_NOT_CLOSED EXCEPTION;
189 G_VAR_NOT_FOUND EXCEPTION;
190 l_variable_code Varchar2(30);
191 l_variable_type Varchar2(1);
192 l_variable_name Varchar2(150);
193 l_row_index INTEGER;
194 l_var_in_table BOOLEAN := FALSE;
195 l_variable_count INTEGER := 1;
196 l_rownotfound BOOLEAN := FALSE;
197
198 CURSOR get_variable_metadata_csr (cp_intent IN VARCHAR2,
199 cp_language IN VARCHAR2 ,
200 cp_extracted_var_name IN VARCHAR2) IS
201 SELECT B.VARIABLE_CODE, B.variable_type, TL.variable_name
202 FROM OKC_BUS_VARIABLES_TL TL, OKC_BUS_VARIABLES_B B
203 WHERE B.VARIABLE_INTENT = cp_intent
204 AND TL.LANGUAGE = cp_language
205 AND TL.VARIABLE_NAME = cp_extracted_var_name
206 AND DISABLED_YN <> 'Y'
207 AND B.VARIABLE_CODE = TL.VARIABLE_CODE;
208
209 -- Defined cursor for 4659659
210 -- If p_batch_number is passed then for Import, variable validation should done from variable Interface table
211 -- also if it does not exist in the production tables
212 CURSOR get_var_imp_metadata_csr (cp_intent IN VARCHAR2,
213 cp_language IN VARCHAR2 ,
214 cp_extracted_var_name IN VARCHAR2,
215 cp_batch_number IN VARCHAR2) IS
216 SELECT VARIABLE_CODE, variable_type, variable_name
217 FROM OKC_VARIABLES_INTERFACE
218 WHERE batch_number = cp_batch_number
219 AND VARIABLE_INTENT = cp_intent
220 AND LANGUAGE = cp_language
221 AND VARIABLE_NAME = cp_extracted_var_name
222 AND DISABLED_YN <> 'Y'
223 AND nvl(process_status,'X') not in ('E');
224
225 Begin
226 if p_extracted_var_name is null
227 then
228 --dbms_output.put_line(' how did it get into the loop ?? '|| p_extracted_var_name);
229
230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
231 end if;
232 if p_extracted_var_name is not null
233 then
234 --dbms_output.put_line(' l_extracted_var_name '|| p_extracted_var_name) ;
235 okc_debug.log('100: In find variables : variable_name ' || p_extracted_var_name
236 || ' intent = ' || p_intent
237 || ' language = ' || p_language , 2);
238 OPEN get_variable_metadata_csr (p_intent,
239 p_language,
240 p_extracted_var_name);
241
242 FETCH get_variable_metadata_csr INTO l_variable_code,l_variable_type,l_variable_name;
243
244 l_rownotfound := get_variable_metadata_csr%NOTFOUND;
245 CLOSE get_variable_metadata_csr;
246
247 if l_rownotfound THEN
248 if p_batch_number is not NULL THEN
249 OPEN get_var_imp_metadata_csr (p_intent,
250 p_language,
251 p_extracted_var_name,
252 p_batch_number);
253 FETCH get_var_imp_metadata_csr INTO l_variable_code,l_variable_type,l_variable_name;
254 l_rownotfound := get_var_imp_metadata_csr%NOTFOUND;
255 CLOSE get_var_imp_metadata_csr;
256 if l_rownotfound THEN
257 RAISE G_VAR_NOT_FOUND;
258 end if;
259 else
260 RAISE G_VAR_NOT_FOUND;
261 end if;
262 end if;
263 p_xml_tag := '<var name="';
264 p_xml_tag := p_xml_tag || l_variable_code;
265 p_xml_tag := p_xml_tag || '" type="';
266 p_xml_tag := p_xml_tag || l_variable_type;
267 p_xml_tag := p_xml_tag || '" meaning="';
268 p_xml_tag := p_xml_tag || l_variable_name;
269 p_xml_tag := p_xml_tag || '"/>';
270 --dbms_output.put_line(' p_xml_tag '|| p_xml_tag || ' length = ' ||
271 -- length(p_xml_tag));
272
273 if ( x_variables_tbl.Count = 0)
274 then
275 -- dbms_output.put_line(' when table count is 0 ' || l_variable_code);
276
277 x_variables_tbl(l_variable_count) := l_variable_code;
278
279
280 else
281 l_row_index := x_variables_tbl.FIRST;
282 LOOP
283 l_var_in_table := false ;
284 if ( (x_variables_tbl(l_row_index)) = l_variable_code ) then
285
286 l_var_in_table := true;
287 exit;
288 end if;
289 EXIT WHEN (l_row_index = x_variables_tbl.LAST);
290 l_row_index := x_variables_tbl.NEXT(l_row_index);
291 END LOOP;
292
293 if (l_var_in_table = false ) then
294 x_variables_tbl((x_variables_tbl.count) + 1) := l_variable_code;
295
296 -- dbms_output.put_line(' when variable not found in table ' || l_variable_code || ' table count ' || x_variables_tbl.count);
297
298 end if;
299 end if;
300 end if;
301 x_return_status := G_RET_STS_SUCCESS ;
302 EXCEPTION
303 when G_VAR_NOT_FOUND then
304 --dbms_output.put_line(' VAR NOT FOUND 1 ');
305 x_return_status := G_VAR_NOT_FOUND_RET_STS;
306 OKC_API.set_message(p_app_name => OKC_API.G_APP_NAME,
307 p_msg_name => 'OKC_ART_VAR_NOT_FOUND',
308 p_token1 => 'VARIABLE',
309 p_token1_value => p_extracted_var_name
310 );
311
312
313 WHEN OTHERS THEN
314 --dbms_output.put_line(' VAR NOT FOUND when others');
315 x_return_status := G_RET_STS_UNEXP_ERROR;
316 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
317 p_msg_name => G_UNEXPECTED_ERROR,
318 p_token1 => G_SQLCODE_TOKEN,
319 p_token1_value => sqlcode,
320 p_token2 => G_SQLERRM_TOKEN,
321 p_token2_value => sqlerrm);
322
323 end find_variables;
324
325
326 PROCEDURE parse_n_replace_text(
327 p_api_version IN NUMBER,
328 p_init_msg_list IN VARCHAR2 ,
329 p_article_text IN OUT NOCOPY CLOB,
330 p_dest_clob IN OUT NOCOPY CLOB,
331 p_calling_mode IN VARCHAR2 ,
332 p_batch_number IN VARCHAR2 DEFAULT NULL, --Bug 4659659
333 p_replace_text IN VARCHAR2 := 'N',
334 p_article_intent IN VARCHAR2,
335 p_language IN VARCHAR2,
336 x_return_status OUT NOCOPY VARCHAR2,
337 x_msg_count OUT NOCOPY NUMBER,
338 x_msg_data OUT NOCOPY VARCHAR2,
339 x_variables_tbl OUT NOCOPY variable_code_tbl_type
340 ) IS
341
342
343 -- made changes for bug3697706 june 17th 2004
344 -- changed l_max_read_amount to 3333 from 8000 to handle mutli byte chars
345 -- l_xml_tag to 660 chars , l_temp_tag_unclosed to 450 chars
346
347 l_art_text_with_tags VARCHAR2(10000);
348 --l_temp_art_text VARCHAR2(10000) CHARACTER SET p_article_text%CHARSET;
349 l_temp_art_text VARCHAR2(10000);
350 l_max_read_amount BINARY_INTEGER := 3333;
351 l_article_text_length INTEGER;
352 l_read_start_position INTEGER:= 1;
353 --l_clob CLOB;
354 --p_dest_clob CLOB;
355 l_xml_tag Varchar2(660);
356 l_xml_tag_length INTEGER;
357 l_extracted_var_name VARCHAR2(450);
358 l_tag_start_postion NUMBER := -99;
359 l_tag_end_postion NUMBER := -99;
360 l_temp_tag_unclosed Varchar2(450) := null;
361 l_check_length INTEGER;
362 l_amount_left_to_read Number := 0;
363 l_tag_not_closed_read_more BOOLEAN := FALSE;
364 p_dest_clob_length INTEGER;
365 l_append_str Varchar2(10000);
366 l_return_status Varchar2(1) := null;
367 l_invalid_var_intent Varchar2(1) := null;
368
369 l_chk_split VARCHAR2(2);
370 l_chk_max_read_amt BINARY_INTEGER := 2;
371
372
373 BEGIN
374 okc_debug.log('100: Entering parse_n_replace_text', 2);
375 l_article_text_length := DBMS_LOB.GETLENGTH(p_article_text);
376 l_amount_left_to_read := l_article_text_length;
377 --dbms_output.put_line('the lenght of the article text clob' || l_article_text_length);
378
379 --DBMS_LOB.CREATETEMPORARY(l_clob,true);
380 --DBMS_LOB.CREATETEMPORARY(p_dest_clob,true);
381 --DBMS_LOB.COPY(l_clob, p_article_text, l_article_text_length, 1, 1);
382
383 while (l_amount_left_to_read > 0 ) loop
384 -- made changes for bug3697706 june 17th 2004 l_max_read_amount is set to 3333 instead of 8000
385 -- the max size we are putting in our varchar2 is 10000 bytes i.e. max of 3333 chars in a tri-byte
386 -- character set.
387 l_max_read_amount := 3333;
388
389 if NOT (l_tag_not_closed_read_more)
390 then
391 -- made changes for bug3697706 june 17th 2004 checking for 3333 instead of 8000
392 if l_amount_left_to_read > 3333
393 then
394
395 -- bug 12593967 starts
396 dbms_lob.read (p_article_text, l_chk_max_read_amt, (l_read_start_position+l_max_read_amount-1), l_chk_split);-- dbms_lob returns in chars
397 IF l_chk_split='[@' THEN
398 l_max_read_amount := l_max_read_amount-1;
399 END IF;
400 -- bug 12593967 Ends
401
402 dbms_lob.read (p_article_text, l_max_read_amount, l_read_start_position, l_temp_art_text);-- dbms_lob returns in chars
403 --special_char_decoders(l_temp_art_text);
404 --dbms_output.put_line(' read clob amount = ' || l_max_read_amount|| 'l_read_start_position = ' || l_read_start_position);
405
406 l_read_start_position := l_read_start_position + l_max_read_amount; -- in chars
407
408 l_amount_left_to_read := l_amount_left_to_read - l_max_read_amount; -- in chars
409 -- made changes for bug3697706 june 17th 2004 checking for 3333 instead of 8000
410 elsif l_amount_left_to_read < 3333 then
411 --dbms_output.put_line(' if length to read is less than 4000');
412 dbms_lob.read(p_article_text, l_amount_left_to_read, l_read_start_position, l_temp_art_text); -- in chars.
413 --special_char_decoders(l_temp_art_text);
414 l_amount_left_to_read := 0;
415 end if;
416 elsif l_tag_not_closed_read_more
417 then
418 --dbms_output.put_line(' if tag not closed');
419 -- made changes for bug3697706 june 17th 2004 checking for 3333 instead of 8000
420 if( (length(l_temp_tag_unclosed) + l_amount_left_to_read)< 3333) -- in chars
421 then
422 --dbms_output.put_line(' if tag not ***1 ');
423 dbms_lob.read(p_article_text, l_amount_left_to_read, l_read_start_position, l_temp_art_text);
424 --special_char_decoders(l_temp_art_text);
425 l_temp_art_text := l_temp_tag_unclosed || l_temp_art_text;
426 --dbms_output.put_line(' if tag not ***1 -- ' || l_amount_left_to_read ||'readstart' || l_read_start_position );
427
428 l_amount_left_to_read := 0;
429 elsif( (length(l_temp_tag_unclosed) + l_amount_left_to_read)> 3333)
430 then
431 l_max_read_amount := l_max_read_amount - length(l_temp_tag_unclosed);
432
433 --dbms_output.put_line(' if tag not ***2 ');
434 dbms_lob.read(p_article_text, l_max_read_amount , l_read_start_position, l_temp_art_text);
435 --special_char_decoders(l_temp_art_text);
436 l_temp_art_text := l_temp_tag_unclosed || l_temp_art_text;
437 l_read_start_position := l_read_start_position + l_max_read_amount;
438
439 l_amount_left_to_read := l_amount_left_to_read - l_max_read_amount;
440
441 end if;
442 end if;
443
444 WHILE INSTR(l_temp_art_text,'[@',1,1) <> 0 LOOP
445 l_tag_start_postion := INSTR(l_temp_art_text,'[@',1,1);
446 l_tag_end_postion := INSTR(l_temp_art_text,'@]',1,1);
447
448 if l_tag_end_postion = 0 and l_amount_left_to_read > 0 then
449 l_tag_not_closed_read_more := true;
450 exit;
451 end if;
452 if l_tag_end_postion = 0 and l_amount_left_to_read = 0 then
453 raise G_VAR_NOT_CLOSED;
454 end if;
455 if l_tag_end_postion <> 0 then
456 l_tag_not_closed_read_more := false;
457 if (l_tag_end_postion - l_tag_start_postion) > 150 then
458 l_extracted_var_name := SUBSTR(l_temp_art_text,(l_tag_start_postion+2),150 );
459 RAISE G_VAR_NOT_FOUND;
460 end if;
461 l_extracted_var_name := SUBSTR(l_temp_art_text,(l_tag_start_postion+2),(l_tag_end_postion - (l_tag_start_postion+2)) );
462
463 find_variables(
464 p_extracted_var_name => l_extracted_var_name,
465 p_intent => p_article_intent,
466 p_language => p_language,
467 p_batch_number => p_batch_number,
468 x_variables_tbl => x_variables_tbl,
469 p_xml_tag => l_xml_tag,
470 x_return_status => l_return_status) ;
471 --dbms_output.put_line(' Calling module ret sts ' || l_return_status);
472
473 IF (l_return_status = G_VAR_NOT_FOUND_RET_STS) THEN
474 --dbms_output.put_line(' VAR NOT FOUND 2 ');
475 l_xml_tag := SUBSTR(l_temp_art_text,l_tag_start_postion,((l_tag_end_postion+ 1 )- l_tag_start_postion));
476 l_invalid_var_intent := G_VAR_NOT_FOUND_RET_STS;
477 ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
479 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
480 RAISE FND_API.G_EXC_ERROR ;
481 END IF;
482
483 end if;
484 if (l_art_text_with_tags is null and dbms_lob.getlength(p_dest_clob)
485 = 0 )then
486
487 l_art_text_with_tags := SUBSTR(l_temp_art_text,1,(l_tag_start_postion - 1 ));
488
489 else
490 --dbms_output.put_line(' art text is not null 22');
491 l_append_str := SUBSTR(l_temp_art_text,1,(l_tag_start_postion -1))
492 ;
493
494 end if;
495 check_length_n_append(
496 p_append_str => l_append_str,
497 p_xml_tag => l_xml_tag,
498 p_art_text_with_tags => l_art_text_with_tags,
499 p_clob_large => p_dest_clob,
500 x_return_status => x_return_status);
501
502 l_temp_art_text := SUBSTR(l_temp_art_text,(l_tag_end_postion + 2));
503 END LOOP ;
504
505 if (l_tag_not_closed_read_more) then
506 l_append_str := SUBSTR(l_temp_art_text,1,(l_tag_start_postion -1));
507 l_xml_tag := null;
508 l_temp_tag_unclosed := SUBSTR(l_temp_art_text,l_tag_start_postion)
509 ;
510
511 else
512 l_append_str := l_temp_art_text;
513 l_xml_tag := null;
514
515 end if;
516 check_length_n_append(
517 p_append_str => l_append_str,
518 p_xml_tag => l_xml_tag,
519 p_art_text_with_tags => l_art_text_with_tags,
520 p_clob_large => p_dest_clob,
521 x_return_status => x_return_status);
522 End Loop;
523
524 l_check_length := length(l_art_text_with_tags);
525 --dbms_output.put_line(' lessthat 4000 - 66');
526 if (l_check_length > 0) then
527 --dbms_output.put_line(' flushing into clob ');
528 dbms_lob.writeappend ( p_dest_clob, l_check_length, l_art_text_with_tags);
529
530 end if;
531 --dbms_output.put_line(' here 99');
532
533
534 if (l_invalid_var_intent = G_VAR_NOT_FOUND_RET_STS) then
535 --dbms_output.put_line(' VAR NOT FOUND 3 ');
536 x_return_status := G_RET_STS_ERROR;
537 elsif ((not (nvl(l_return_status,'S') = G_VAR_NOT_FOUND_RET_STS) )
538 and (p_replace_text = 'Y') ) then
539 p_dest_clob_length := DBMS_LOB.GETLENGTH(p_dest_clob);
540 if (p_calling_mode = 'CALLED_FROM_CREATE_UPDATE') then
541 -- DBMS_LOB.COPY(p_article_text, p_dest_clob, p_dest_clob_length, 1, 1);
542 -- DBMS_LOB.TRIM(p_article_text,p_dest_clob_length);
543 x_return_status := G_RET_STS_SUCCESS;
544 else
545 x_return_status := G_RET_STS_SUCCESS;
546 end if;
547 else
548 x_return_status := G_RET_STS_SUCCESS;
549 end if;
550
551 --dbms_lob.freetemporary(l_clob);
552 --dbms_lob.freetemporary(p_dest_clob);
553 okc_debug.log('100: Leaving parse_n_replace_text', 2);
554 EXCEPTION
555 WHEN G_VAR_NOT_FOUND then
556 --dbms_output.put_line(' VAR NOT FOUND 1 ');
557 x_return_status := G_VAR_NOT_FOUND_RET_STS;
558 OKC_API.set_message(p_app_name => OKC_API.G_APP_NAME,
559 p_msg_name => 'OKC_ART_VAR_NOT_FOUND',
560 p_token1 => 'VARIABLE',
561 p_token1_value => l_extracted_var_name
562 );
563 WHEN NO_DATA_FOUND
564 THEN
565 BEGIN
566 x_return_status := G_RET_STS_ERROR;
567 OKC_API.set_message(p_app_name => OKC_API.G_APP_NAME,
568 p_msg_name => 'OKC_ART_VAR_NOT_FOUND'
569 );
570
571 END;
572 when G_VAR_NOT_CLOSED
573 then
574 x_return_status := G_RET_STS_ERROR;
575 OKC_API.set_message(p_app_name => OKC_API.G_APP_NAME,
576 p_msg_name => 'OKC_ART_VAR_NOT_CLOSED'
577 );
578 WHEN OTHERS
579 THEN
580 x_return_status := G_RET_STS_UNEXP_ERROR;
581 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
582 p_msg_name => G_UNEXPECTED_ERROR,
583 p_token1 => G_SQLCODE_TOKEN,
584 p_token1_value => sqlcode,
585 p_token2 => G_SQLERRM_TOKEN,
586 p_token2_value => sqlerrm);
587 END parse_n_replace_text;
588
589
590 ---------------------------------------
591 -- PROCEDURE This is the Autogeneration API --
592 ---------------------------------------
593 -- If the profile option should be refreshed then
594 -- please set p_seq_type_info_only as 'Y'
595 PROCEDURE GET_ARTICLE_SEQ_NUMBER
596 (p_article_number IN VARCHAR2 := NULL,
597 p_seq_type_info_only IN VARCHAR2 := 'N',
598 p_org_id IN NUMBER,
599 x_article_number OUT NOCOPY VARCHAR2,
600 x_doc_sequence_type OUT NOCOPY VARCHAR2,
601 x_return_status OUT NOCOPY VARCHAR2
602 ) IS
603 l_doc_sequence_value NUMBER;
604 l_doc_sequence_id NUMBER;
605 l_set_Of_Books_id NUMBER;
606 l_org_id NUMBER;
607 l_doc_category_code FND_DOC_SEQUENCE_CATEGORIES.CODE%TYPE;
608 l_seqassid FND_DOC_SEQUENCE_ASSIGNMENTS.DOC_SEQUENCE_ASSIGNMENT_ID%TYPE;
609 l_Trx_Date DATE;
610 l_db_sequence_name FND_DOCUMENT_SEQUENCES.DB_SEQUENCE_NAME%TYPE;
611 l_doc_sequence_type FND_DOCUMENT_SEQUENCES.TYPE%TYPE;
612 l_doc_sequence_name FND_DOCUMENT_SEQUENCES.NAME%TYPE;
613 l_Prd_Tbl_Name FND_DOCUMENT_SEQUENCES.TABLE_NAME%TYPE;
614 l_Aud_Tbl_Name FND_DOCUMENT_SEQUENCES.AUDIT_TABLE_NAME%TYPE;
615 l_Msg_Flag FND_DOCUMENT_SEQUENCES.MESSAGE_FLAG%TYPE;
616 l_result NUMBER;
617
618
619
620 CURSOR l_get_sob_csr (cp_org_id IN NUMBER ) IS
621 SELECT OI2.ORG_INFORMATION3 SET_OF_BOOKS_ID
622 FROM HR_ORGANIZATION_INFORMATION OI1,
623 HR_ORGANIZATION_INFORMATION OI2,
624 HR_ALL_ORGANIZATION_UNITS OU
625 WHERE OI1.ORGANIZATION_ID = OU.ORGANIZATION_ID AND
626 OI2.ORGANIZATION_ID = OU.ORGANIZATION_ID AND
627 OI1.ORG_INFORMATION_CONTEXT = 'CLASS' AND
628 OI2.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND
629 OI1.ORG_INFORMATION1 = 'OPERATING_UNIT'AND
630 OI1.ORGANIZATION_ID = cp_org_id
631 ;
632
633 CURSOR l_ensure_unique_csr (cp_article_number IN VARCHAR2, cp_org_id IN NUMBER ) IS
634 SELECT ARTICLE_NUMBER
635 FROM OKC_ARTICLES_ALL
636 WHERE ARTICLE_NUMBER = cp_article_number
637 AND ORG_ID = cp_org_id
638 AND ROWNUM < 2;
639
640 -- Added for Bug 4643332
641 -- Need to get the Seq Category from EIT instead from Profile
642 CURSOR l_clause_seq_csr (cp_org_id IN NUMBER) IS
643 SELECT ORG_INFORMATION8 CLAUSE_SEQ
644 --nvl(ORG_INFORMATION8,'-99') CLAUSE_SEQ
645 FROM HR_ORGANIZATION_INFORMATION
646 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
647 AND ORGANIZATION_ID = cp_org_id
648 ;
649
650 l_row_notfound BOOLEAN := FALSE;
651 l_row_notfound1 BOOLEAN := FALSE;
652 l_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE := NULL;
653
654 BEGIN
655
656 -- Initialize API return status to success
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 --Bug 3832007: p_req_type_info_only is 'Y' only when it's called from UI
659 --for the rest of case (migration, import, create_article api), cached value will match
660 --with session context that API is called
661 --For performance, following steps are executed only when this is called from UI
662 --as user may change responsibility or org frequently during same session
663 IF p_seq_type_info_only = 'Y' THEN
664 G_profile_doc_seq := fnd_profile.value('UNIQUE:SEQ_NUMBERS');
665 G_doc_category_code := substr(Fnd_Profile.Value('OKC_ARTICLE_DOC_SEQ_CATEGORY'),1,30) ;
666 -- MOAC
667 G_CURRENT_ORG_ID := mo_global.get_current_org_id();
668 /*
669 OPEN cur_org_csr;
670 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
671 CLOSE cur_org_csr;
672 */
673 END IF;
674
675 IF G_profile_doc_seq = 'N' Then
676 x_article_number := p_article_number;
677 x_doc_sequence_type := G_profile_doc_seq;
678 return;
679 END IF;
680
681 -- This is for other cases
682 -- Need to derive the set of books for the org in order to derive the sequence
683 --
684 -- MOAC
685 if p_org_id IS NOT NULL Then
686 l_org_id := p_org_id;
687 else
688 if G_CURRENT_ORG_ID IS NULL Then
689 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
690 RAISE FND_API.G_EXC_ERROR ;
691 else
692 l_org_id := G_CURRENT_ORG_ID;
693 end if;
694 end if;
695 /*
696 IF p_org_id is NULL THEN
697 l_org_id := G_CURRENT_ORG_ID;
698 ELSE
699 l_org_id := p_org_id;
700 END IF;
701 */
702 --dbms_output.put_line('P Org Id:'|| p_org_id);
703 --dbms_output.put_line('Current Org Id:'|| G_CURRENT_ORG_ID);
704 OPEN l_get_sob_csr (l_org_id);
705 FETCH l_get_sob_csr into l_set_of_books_id;
706 l_row_notfound := l_get_sob_csr%NOTFOUND;
707 CLOSE l_get_sob_csr;
708 IF l_row_notfound THEN
709 Okc_Api.Set_Message(G_APP_NAME,'OKC_ART_INV_SOB_ID');
710 RAISE FND_API.G_EXC_ERROR ;
711 END IF;
712 l_row_notfound := FALSE;
713 -- New changes for MOAC Bug 4643332
714 -- get seq id from EIT
715 OPEN l_clause_seq_csr (l_org_id);
716 FETCH l_clause_seq_csr into l_doc_category_code;
717 l_row_notfound1 := l_clause_seq_csr%NOTFOUND;
718 CLOSE l_clause_seq_csr;
719 IF l_row_notfound1 THEN
720 Okc_Api.Set_Message(G_APP_NAME,'OKC_ART_SEQ_CAT_NOT_DEFINED');
721 RAISE FND_API.G_EXC_ERROR ;
722 END IF;
723 l_row_notfound1 := FALSE;
724 --l_doc_category_code := G_DOC_CATEGORY_CODE; -- Commented for new changes for MOAC
725 --dbms_output.put_line('Org Id:'||p_org_id||'CAT: '||l_doc_category_code||'SOB: '||l_set_of_books_id);
726 l_result := fnd_seqnum.get_seq_info(
727 app_id => 510 ,
728 cat_code => l_doc_category_code,
729 sob_id => l_set_of_books_id,
730 met_code => NULL,
731 trx_date => sysdate,
732 docseq_id => l_doc_sequence_id,
733 docseq_type => l_doc_sequence_type,
734 docseq_name => l_doc_sequence_name,
735 db_seq_name => l_db_sequence_name,
736 seq_ass_id => l_seqassid,
737 prd_tab_name => l_Prd_Tbl_Name,
738 aud_tab_name => l_Aud_Tbl_Name,
739 msg_flag => l_msg_flag,
740 suppress_error => 'N' ,
741 suppress_warn => 'Y'
742 );
743 x_doc_sequence_type := l_doc_sequence_type;
744 --dbms_output.put_line('Info result is: '|| l_result ||'*'|| l_doc_sequence_type);
745 IF l_result <> FND_SEQNUM.SEQSUCC THEN
746 /* Commented following IF - Bug 3542035 , This IF is not reqd */
747 --IF l_result = FND_SEQNUM.NOTUSED THEN
748 OKC_API.set_message(G_APP_NAME,'OKC_ART_MISS_DOC_SEQ');
749 --END IF;
750 RAISE FND_API.G_EXC_ERROR;
751 END IF;
752
753 IF p_seq_type_info_only = 'Y' THEN
754 return;
755 END IF;
756 -- Continue with the rest if article number is also desired
757
758
759 IF ( x_doc_sequence_type <> 'M') THEN
760 l_result := fnd_seqnum.get_seq_val(
761 app_id => 510,
762 cat_code => l_doc_category_code,
763 sob_id => l_set_of_books_id,
764 met_code => null,
765 trx_date => sysdate,
766 seq_val => l_doc_sequence_value,
767 docseq_id => l_doc_sequence_id);
768 --dbms_output.put_line('Number result is: '|| to_char(l_result) ||'*'|| l_doc_sequence_value);
769 IF l_result <> 0 THEN
770 RAISE FND_API.G_EXC_ERROR;
771 ELSE
772 x_article_number := TO_CHAR(l_doc_sequence_value);
773 END IF;
774 OPEN l_ensure_unique_csr (x_article_number, l_org_id);
775 FETCH l_ensure_unique_csr into l_article_number;
776 l_row_notfound := l_ensure_unique_csr%NOTFOUND;
777 CLOSE l_ensure_unique_csr;
778 IF l_row_notfound THEN
779 NULL; -- dups do not exist.
780 ELSE
781 Okc_Api.Set_Message(G_APP_NAME,'OKC_ART_ART_NUM_EXIST');
782 RAISE FND_API.G_EXC_ERROR ;
783 END IF;
784 ELSIF (x_doc_sequence_type = 'M') THEN
785 x_article_number := p_article_number;
786 END IF;
787 EXCEPTION
788 WHEN FND_API.G_EXC_ERROR THEN
789 IF (l_debug = 'Y') THEN
790 okc_debug.log('2400: Leaving get article number: OKC_API.G_EXCEPTION_ERROR Exception', 2);
791 --dbms_output.put_line('2400: Leaving get article number: OKC_API.G_EXCEPTION_ERROR Exception');
792 END IF;
793 x_return_status := G_RET_STS_ERROR ;
794 WHEN OTHERS THEN
795 /*------------------------------------------+
796 | No document assignment was found. |
797 | Generate an error if document numbering |
798 | is mandatory. |
799 +------------------------------------------*/
800 --dbms_output.put_line('Get_Doc_Seq: ' || 'no_data_found raised'||G_profile_doc_seq);
801 IF (G_profile_doc_seq = 'A' ) THEN
802 OKC_API.Set_Message( 'FND','UNIQUE-ALWAYS USED');
803 END IF;
804 x_return_status := G_RET_STS_ERROR;
805 IF l_get_sob_csr%ISOPEN THEN
806 CLOSE l_get_sob_csr;
807 END IF;
808 IF l_ensure_unique_csr%ISOPEN THEN
809 CLOSE l_ensure_unique_csr;
810 END IF;
811
812 END GET_ARTICLE_SEQ_NUMBER;
813
814 -- The following is a private API and should not be called by itself.
815 ---------------------------------------------------------------------------
816 -- PROCEDURE delete_article_version
817 ---------------------------------------------------------------------------
818 PROCEDURE delete_article_version(
819 p_api_version IN NUMBER,
820 p_init_msg_list IN VARCHAR2 ,
821 p_commit IN VARCHAR2 := FND_API.G_FALSE,
822
823 x_return_status OUT NOCOPY VARCHAR2,
824 x_msg_count OUT NOCOPY NUMBER,
825 x_msg_data OUT NOCOPY VARCHAR2,
826
827 p_article_version_id IN NUMBER,
828 p_standard_yn IN VARCHAR2 := 'Y',
829 p_only_version IN VARCHAR2 := 'T',
830 p_adoption_type IN VARCHAR2 := NULL,
831 p_object_version_number IN NUMBER := NULL
832 ) IS
833 l_api_version CONSTANT NUMBER := 1;
834 l_object_version_number NUMBER := NULL;
835 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_article_version';
836 l_row_notfound BOOLEAN := FALSE;
837
838
839 BEGIN
840
841 IF (l_debug = 'Y') THEN
842 okc_debug.log('2200: Entered delete_article_version', 2);
843 END IF;
844 -- MOAC
845 G_CURRENT_ORG_ID := mo_global.get_current_org_id() ;
846 /*
847 OPEN cur_org_csr;
848 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
849 CLOSE cur_org_csr;
850 */
851
852 -- Standard Start of API savepoint
853 SAVEPOINT g_delete_article_version_GRP;
854 -- Standard call to check for call compatibility.
855 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
856 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
857 END IF;
858 -- Initialize message list if p_init_msg_list is set to TRUE.
859 IF FND_API.to_Boolean( p_init_msg_list ) THEN
860 FND_MSG_PUB.initialize;
861 END IF;
862 -- Initialize API return status to success
863 x_return_status := FND_API.G_RET_STS_SUCCESS;
864
865 --------------------------------------------
866 -- Calling Simple API for Deleting A Row
867 --------------------------------------------
868 OKC_ARTICLE_VERSIONS_PVT.Delete_Row(
869 x_return_status => x_return_status,
870 p_article_version_id => p_article_version_id,
871 p_object_version_number => p_object_version_number
872 );
873 --------------------------------------------
874 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
876 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
877 RAISE FND_API.G_EXC_ERROR ;
878 END IF;
879 --------------------------------------------
880
881 -- Do not support Simple API for variable deletion or update. This is the only
882 -- API from which this will be called.
883
884
885 DELETE FROM OKC_ARTICLE_VARIABLES
886 WHERE ARTICLE_VERSION_ID = p_article_version_id;
887
888 -- If a local article version is deleted the attached global article version
889 -- becomes available in the adoption table with adoption type as "AVAILABLE".
890 -- However this is only in case of LOCALIZATION of local article versions.
891 -- In the case of a local article version being created as a new version
892 -- from a prior version we delete the adoption row for this version.
893
894 -- MOAC
895 IF G_CURRENT_ORG_ID IS NULL Then
896 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
897 RAISE FND_API.G_EXC_ERROR ;
898 END IF;
899
900 IF (G_CURRENT_ORG_ID = G_GLOBAL_ORG_ID OR
901 G_CURRENT_ORG_ID = -99 OR
902 G_GLOBAL_ORG_ID = -99 ) THEN
903 NULL;
904 ELSIF p_standard_yn = 'Y' AND
905 p_adoption_type <> 'LOCAL' Then
906 OKC_ADOPTIONS_GRP.DELETE_LOCAL_ADOPTION_DETAILS(
907 p_api_version => p_api_version,
908 p_init_msg_list => p_init_msg_list,
909 x_return_status => x_return_status,
910 x_msg_count => x_msg_count,
911 x_msg_data => x_msg_data,
912 p_only_local_version => p_only_version,
913 p_local_article_version_id => p_article_version_id,
914 p_local_org_id => G_CURRENT_ORG_ID
915 );
916 END IF;
917 --------------------------------------------
918 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
920 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
921 RAISE FND_API.G_EXC_ERROR ;
922 END IF;
923 --------------------------------------------
924
925 -- Standard check of p_commit
926 IF FND_API.To_Boolean( p_commit ) THEN
927 COMMIT WORK;
928 END IF;
929 -- Standard call to get message count and if count is 1, get message info.
930 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
931
932 IF (l_debug = 'Y') THEN
933 okc_debug.log('2300: Leaving delete_article_version', 2);
934 END IF;
935
936 EXCEPTION
937 WHEN FND_API.G_EXC_ERROR THEN
938 IF (l_debug = 'Y') THEN
939 okc_debug.log('2400: Leaving delete_article_version: OKC_API.G_EXCEPTION_ERROR Exception', 2);
940 END IF;
941 ROLLBACK TO g_delete_article_version_GRP;
942 x_return_status := G_RET_STS_ERROR ;
943 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
944
945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
946 IF (l_debug = 'Y') THEN
947 okc_debug.log('2500: Leaving delete_article_version: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
948 END IF;
949 ROLLBACK TO g_delete_article_version_GRP;
950 x_return_status := G_RET_STS_UNEXP_ERROR ;
951 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
952
953 WHEN OTHERS THEN
954 IF (l_debug = 'Y') THEN
955 okc_debug.log('2600: Leaving delete_article_version because of EXCEPTION: '||sqlerrm, 2);
956 END IF;
957
958 ROLLBACK TO g_delete_article_version_GRP;
959 x_return_status := G_RET_STS_UNEXP_ERROR ;
960 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
961 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
962 END IF;
963 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
964
965 END delete_article_version;
966
967 PROCEDURE validate_article(
968 p_api_version IN NUMBER,
969 p_init_msg_list IN VARCHAR2 ,
970 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
971 p_import_action IN VARCHAR2 := NULL,
972 x_return_status OUT NOCOPY VARCHAR2,
973 x_msg_count OUT NOCOPY NUMBER,
974 x_msg_data OUT NOCOPY VARCHAR2,
975 x_earlier_adoption_type OUT NOCOPY VARCHAR2,
976 x_earlier_version_id OUT NOCOPY NUMBER,
977 x_earlier_version_number OUT NOCOPY NUMBER,
978
979 -- Article Attributes
980 p_article_id IN NUMBER,
981 p_article_title IN VARCHAR2,
982 p_org_id IN NUMBER,
983 p_article_number IN VARCHAR2,
984 p_standard_yn IN VARCHAR2,
985 p_article_intent IN VARCHAR2,
986 p_article_language IN VARCHAR2,
987 p_article_type IN VARCHAR2,
988 p_orig_system_reference_code IN VARCHAR2,
989 p_orig_system_reference_id1 IN VARCHAR2,
990 p_orig_system_reference_id2 IN VARCHAR2,
991 -- Article Version Attributes
992 p_article_version_id IN NUMBER,
993 p_article_version_number IN NUMBER,
994 p_article_text IN CLOB,
995 p_provision_yn IN VARCHAR2,
996 p_insert_by_reference IN VARCHAR2,
997 p_lock_text IN VARCHAR2,
998 p_global_yn IN VARCHAR2,
999 p_article_status IN VARCHAR2,
1000 p_sav_release IN VARCHAR2,
1001 p_start_date IN DATE,
1002 p_end_date IN DATE,
1003 p_std_article_version_id IN NUMBER,
1004 p_display_name IN VARCHAR2,
1005 p_translated_yn IN VARCHAR2,
1006 p_article_description IN VARCHAR2,
1007 p_date_approved IN DATE,
1008 p_default_section IN VARCHAR2,
1009 p_reference_source IN VARCHAR2,
1010 p_reference_text IN VARCHAR2,
1011 p_additional_instructions IN VARCHAR2,
1012 p_variation_description IN VARCHAR2,
1013 p_date_published IN DATE,
1014 p_attribute_category IN VARCHAR2 := NULL,
1015 p_attribute1 IN VARCHAR2 := NULL,
1016 p_attribute2 IN VARCHAR2 := NULL,
1017 p_attribute3 IN VARCHAR2 := NULL,
1018 p_attribute4 IN VARCHAR2 := NULL,
1019 p_attribute5 IN VARCHAR2 := NULL,
1020 p_attribute6 IN VARCHAR2 := NULL,
1021 p_attribute7 IN VARCHAR2 := NULL,
1022 p_attribute8 IN VARCHAR2 := NULL,
1023 p_attribute9 IN VARCHAR2 := NULL,
1024 p_attribute10 IN VARCHAR2 := NULL,
1025 p_attribute11 IN VARCHAR2 := NULL,
1026 p_attribute12 IN VARCHAR2 := NULL,
1027 p_attribute13 IN VARCHAR2 := NULL,
1028 p_attribute14 IN VARCHAR2 := NULL,
1029 p_attribute15 IN VARCHAR2 := NULL,
1030 --Clause Editing
1031 p_edited_in_word IN VARCHAR2 DEFAULT 'N',
1032 p_article_text_in_word IN BLOB DEFAULT NULL,
1033 --CLM
1034 p_variable_code IN VARCHAR2 DEFAULT NULL
1035 ) IS
1036 l_api_version CONSTANT NUMBER := 1;
1037 l_api_name CONSTANT VARCHAR2(30) := 'g_validate_article';
1038 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1039 l_article_status OKC_ARTICLE_VERSIONS.ARTICLE_STATUS%TYPE;
1040
1041 BEGIN
1042
1043 IF (l_debug = 'Y') THEN
1044 okc_debug.log('100: Entered validate_article', 2);
1045 END IF;
1046
1047 -- Standard Start of API savepoint
1048 SAVEPOINT g_validate_article_GRP;
1049 -- Standard call to check for call compatibility.
1050 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1052 END IF;
1053 -- Initialize message list if p_init_msg_list is set to TRUE.
1054 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1055 FND_MSG_PUB.initialize;
1056 END IF;
1057 -- Initialize API return status to success
1058 x_return_status := FND_API.G_RET_STS_SUCCESS;
1059
1060 --------------------------------------------
1061 -- Calling Simple API for Article Validation
1062 --------------------------------------------
1063 OKC_ARTICLES_ALL_PVT.Validate_Row(
1064 p_validation_level => p_validation_level,
1065 p_import_action => p_import_action,
1066 x_return_status => l_return_status,
1067 p_article_id => p_article_id,
1068 p_article_title => p_article_title,
1069 p_org_id => p_org_id,
1070 p_article_number => p_article_number,
1071 p_standard_yn => p_standard_yn,
1072 p_article_intent => p_article_intent,
1073 p_article_language => p_article_language,
1074 p_article_type => p_article_type,
1075 p_orig_system_reference_code => p_orig_system_reference_code,
1076 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1077 p_orig_system_reference_id2 => p_orig_system_reference_id2
1078
1079 );
1080 --dbms_output.put_line('API Art Msg data:' || x_msg_data);
1081 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1083 END IF;
1084
1085 --------------------------------------------
1086 -- Calling Simple API for Article Version Validation
1087 --------------------------------------------
1088 -- UIs may be returning EXPIRED status (derived) from VOs.
1089 IF p_article_status = 'EXPIRED' THEN
1090 l_article_status := 'APPROVED';
1091 ELSE
1092 l_article_status := p_article_status;
1093 END IF;
1094 OKC_ARTICLE_VERSIONS_PVT.Validate_Row(
1095 p_validation_level => p_validation_level,
1096 p_import_action => p_import_action,
1097 x_return_status => x_return_status,
1098 x_earlier_adoption_type => x_earlier_adoption_type,
1099 x_earlier_version_id => x_earlier_version_id,
1100 x_earlier_version_number => x_earlier_version_number,
1101 p_article_version_id => p_article_version_id,
1102 p_article_id => p_article_id,
1103 p_article_version_number => p_article_version_number,
1104 p_article_text => p_article_text,
1105 p_provision_yn => p_provision_yn,
1106 p_insert_by_reference => p_insert_by_reference,
1107 p_lock_text => p_lock_text,
1108 p_global_yn => p_global_yn,
1109 p_article_language => p_article_language,
1110 p_article_status => l_article_status,
1111 p_sav_release => p_sav_release,
1112 p_start_date => p_start_date,
1113 p_end_date => p_end_date,
1114 p_std_article_version_id => p_std_article_version_id,
1115 p_display_name => p_display_name,
1116 p_translated_yn => p_translated_yn,
1117 p_article_description => p_article_description,
1118 p_date_approved => p_date_approved,
1119 p_default_section => p_default_section,
1120 p_reference_source => p_reference_source,
1121 p_reference_text => p_reference_text,
1122 p_orig_system_reference_code => p_orig_system_reference_code,
1123 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1124 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1125 p_additional_instructions => p_additional_instructions,
1126 p_variation_description => p_variation_description,
1127 p_date_published => p_date_published,
1128 p_attribute_category => p_attribute_category,
1129 p_attribute1 => p_attribute1,
1130 p_attribute2 => p_attribute2,
1131 p_attribute3 => p_attribute3,
1132 p_attribute4 => p_attribute4,
1133 p_attribute5 => p_attribute5,
1134 p_attribute6 => p_attribute6,
1135 p_attribute7 => p_attribute7,
1136 p_attribute8 => p_attribute8,
1137 p_attribute9 => p_attribute9,
1138 p_attribute10 => p_attribute10,
1139 p_attribute11 => p_attribute11,
1140 p_attribute12 => p_attribute12,
1141 p_attribute13 => p_attribute13,
1142 p_attribute14 => p_attribute14,
1143 p_attribute15 => p_attribute15,
1144 --Clause Editing
1145 p_edited_in_word => p_edited_in_word,
1146 p_article_text_in_word => p_article_text_in_word,
1147 --clm
1148 p_variable_code => p_variable_code
1149 );
1150 --dbms_output.put_line('API Ver Msg data:' || x_msg_data);
1151 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1153 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1154 RAISE FND_API.G_EXC_ERROR ;
1155 END IF;
1156 --------------------------------------------
1157 x_return_status := l_return_status;
1158
1159 -- Standard call to get message count and if count is 1, get message info.
1160 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1161
1162 IF (l_debug = 'Y') THEN
1163 okc_debug.log('200: Leaving validate_article', 2);
1164 END IF;
1165
1166 EXCEPTION
1167 WHEN FND_API.G_EXC_ERROR THEN
1168 IF (l_debug = 'Y') THEN
1169 okc_debug.log('300: Leaving Validate_Article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1170 END IF;
1171 ROLLBACK TO g_validate_article_GRP;
1172 l_return_status := G_RET_STS_ERROR ;
1173 x_return_status := G_RET_STS_ERROR ;
1174 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1175 --dbms_output.put_line('Msg data2:' || x_msg_data);
1176
1177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1178 IF (l_debug = 'Y') THEN
1179 okc_debug.log('400: Leaving Validate_Article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1180 END IF;
1181 ROLLBACK TO g_validate_article_GRP;
1182 l_return_status := G_RET_STS_UNEXP_ERROR ;
1183 x_return_status := G_RET_STS_UNEXP_ERROR ;
1184 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1185
1186 WHEN OTHERS THEN
1187 IF (l_debug = 'Y') THEN
1188 okc_debug.log('500: Leaving Validate_Article because of EXCEPTION: '||sqlerrm, 2);
1189 END IF;
1190
1191 ROLLBACK TO g_validate_article_GRP;
1192 l_return_status := G_RET_STS_UNEXP_ERROR ;
1193 x_return_status := G_RET_STS_UNEXP_ERROR ;
1194 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1195 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1196 END IF;
1197 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1198
1199 END validate_article;
1200
1201 -------------------------------------
1202 -- PROCEDURE create_article
1203 -------------------------------------
1204 PROCEDURE create_article(
1205 p_api_version IN NUMBER,
1206 p_init_msg_list IN VARCHAR2 ,
1207 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1208 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1209 x_return_status OUT NOCOPY VARCHAR2,
1210 x_msg_count OUT NOCOPY NUMBER,
1211 x_msg_data OUT NOCOPY VARCHAR2,
1212 p_article_title IN VARCHAR2,
1213 p_article_number IN VARCHAR2,
1214 p_standard_yn IN VARCHAR2,
1215 p_article_intent IN VARCHAR2,
1216 p_article_language IN VARCHAR2,
1217 p_article_type IN VARCHAR2,
1218 p_orig_system_reference_code IN VARCHAR2,
1219 p_orig_system_reference_id1 IN VARCHAR2,
1220 p_orig_system_reference_id2 IN VARCHAR2,
1221 p_cz_transfer_status_flag IN VARCHAR2,
1222 x_article_id OUT NOCOPY NUMBER,
1223 x_article_number OUT NOCOPY VARCHAR2,
1224 -- Article Version Attributes
1225 p_article_text IN CLOB,
1226 p_provision_yn IN VARCHAR2,
1227 p_insert_by_reference IN VARCHAR2,
1228 p_lock_text IN VARCHAR2,
1229 p_global_yn IN VARCHAR2,
1230 p_article_status IN VARCHAR2,
1231 p_sav_release IN VARCHAR2,
1232 p_start_date IN DATE,
1233 p_end_date IN DATE,
1234 p_std_article_version_id IN NUMBER,
1235 p_display_name IN VARCHAR2,
1236 p_translated_yn IN VARCHAR2,
1237 p_article_description IN VARCHAR2,
1238 p_date_approved IN DATE,
1239 p_default_section IN VARCHAR2,
1240 p_reference_source IN VARCHAR2,
1241 p_reference_text IN VARCHAR2,
1242 p_additional_instructions IN VARCHAR2,
1243 p_variation_description IN VARCHAR2,
1244 p_date_published IN DATE,
1245 p_attribute_category IN VARCHAR2 := NULL,
1246 p_attribute1 IN VARCHAR2 := NULL,
1247 p_attribute2 IN VARCHAR2 := NULL,
1248 p_attribute3 IN VARCHAR2 := NULL,
1249 p_attribute4 IN VARCHAR2 := NULL,
1250 p_attribute5 IN VARCHAR2 := NULL,
1251 p_attribute6 IN VARCHAR2 := NULL,
1252 p_attribute7 IN VARCHAR2 := NULL,
1253 p_attribute8 IN VARCHAR2 := NULL,
1254 p_attribute9 IN VARCHAR2 := NULL,
1255 p_attribute10 IN VARCHAR2 := NULL,
1256 p_attribute11 IN VARCHAR2 := NULL,
1257 p_attribute12 IN VARCHAR2 := NULL,
1258 p_attribute13 IN VARCHAR2 := NULL,
1259 p_attribute14 IN VARCHAR2 := NULL,
1260 p_attribute15 IN VARCHAR2 := NULL,
1261 p_v_orig_system_reference_code IN VARCHAR2,
1262 p_v_orig_system_reference_id1 IN VARCHAR2,
1263 p_v_orig_system_reference_id2 IN VARCHAR2,
1264 p_global_article_version_id IN NUMBER := NULL,
1265 --Clause Editing
1266 p_edited_in_word IN VARCHAR2 DEFAULT 'N',
1267 p_article_text_in_word IN BLOB DEFAULT NULL,
1268 --CLM
1269 p_variable_code IN VARCHAR2 DEFAULT NULL,
1270 x_article_version_id OUT NOCOPY NUMBER
1271 ) IS
1272
1273 l_api_version CONSTANT NUMBER := 1;
1274 l_api_name CONSTANT VARCHAR2(30) := 'g_create_article';
1275 l_object_version_number OKC_ARTICLES_ALL.OBJECT_VERSION_NUMBER%TYPE := 1;
1276 l_doc_sequence_type VARCHAR2(1);
1277 l_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
1278 l_created_by OKC_ARTICLES_ALL.CREATED_BY%TYPE;
1279 l_creation_date OKC_ARTICLES_ALL.CREATION_DATE%TYPE;
1280 l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
1281 l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
1282 l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
1283 BEGIN
1284
1285 --dbms_output.put_line('600: Entered create_article from copy');
1286 IF (l_debug = 'Y') THEN
1287 okc_debug.log('600: Entered create_article', 2);
1288 END IF;
1289
1290 -- Standard Start of API savepoint
1291 SAVEPOINT g_create_article_GRP;
1292 -- Standard call to check for call compatibility.
1293 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1294 --dbms_output.put_line('600: Entered create_article NOT Compatible');
1295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1296 END IF;
1297 -- MOAC
1298 G_CURRENT_ORG_ID := mo_global.get_current_org_id();
1299 /*
1300 OPEN cur_org_csr;
1301 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
1302 CLOSE cur_org_csr;
1303 */
1304
1305 -- Initialize message list if p_init_msg_list is set to TRUE.
1306 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1307 --dbms_output.put_line('600: Entered create_article message init');
1308 FND_MSG_PUB.initialize;
1309 END IF;
1310 -- Initialize API return status to success
1311 x_return_status := FND_API.G_RET_STS_SUCCESS;
1312 --dbms_output.put_line('600: Entered create_article from copy status is'||x_return_status);
1313 -- MOAC
1314 IF G_CURRENT_ORG_ID IS NULL Then
1315 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
1316 RAISE FND_API.G_EXC_ERROR ;
1317 END IF;
1318
1319 -- Fix for bug# 5158268. The variable l_article_number should be initialized with p_article_number
1320 l_article_number := p_article_number;
1321 -- Generate article_number for articles based on autonumbering is required or not
1322 IF p_standard_yn = 'Y' Then
1323 G_doc_category_code := substr(Fnd_Profile.Value('OKC_ARTICLE_DOC_SEQ_CATEGORY'),1,30) ;
1324 G_profile_doc_seq := fnd_profile.value('UNIQUE:SEQ_NUMBERS');
1325 GET_ARTICLE_SEQ_NUMBER
1326 (p_article_number => p_article_number,
1327 p_seq_type_info_only => 'N',
1328 p_org_id => G_CURRENT_ORG_ID,
1329 x_article_number => l_article_number,
1330 x_doc_sequence_type => l_doc_sequence_type,
1331 x_return_status => x_return_status);
1332
1333 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1335 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1336 RAISE FND_API.G_EXC_ERROR ;
1337 END IF;
1338 END IF;
1339 --------------------------------------------
1340 -- Calling Simple API for Creating Article Row
1341 --------------------------------------------
1342 OKC_ARTICLES_ALL_PVT.Insert_Row(
1343 p_validation_level => p_validation_level,
1344 x_return_status => x_return_status,
1345 p_article_title => p_article_title,
1346 p_org_id => G_CURRENT_ORG_ID,
1347 p_article_number => l_article_number,
1348 p_standard_yn => p_standard_yn,
1349 p_article_intent => p_article_intent,
1350 p_article_language => p_article_language,
1351 p_article_type => p_article_type,
1352 p_orig_system_reference_code => p_orig_system_reference_code,
1353 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1354 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1355 p_cz_transfer_status_flag => p_cz_transfer_status_flag,
1356 x_article_number => x_article_number,
1357 x_article_id => x_article_id
1358 );
1359 --dbms_output.put_line('In Create - x_article_id is '||x_article_id);
1360 --dbms_output.put_line('In Create - x_return_status is '||x_return_status);
1361 --------------------------------------------
1362 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1364 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1365 RAISE FND_API.G_EXC_ERROR ;
1366 END IF;
1367 --------------------------------------------
1368 --------------------------------------------
1369 -- Calling Group API for Creating Article Version Row
1370 --------------------------------------------
1371 Create_Article_Version(
1372 p_api_version => p_api_version,
1373 p_init_msg_list => p_init_msg_list,
1374 p_validation_level => p_validation_level,
1375 p_commit => p_commit,
1376 p_global_article_version_id => p_global_article_version_id,
1377 p_article_intent => p_article_intent,
1378 p_standard_yn => p_standard_yn,
1379
1380 x_return_status => x_return_status,
1381 x_msg_count => x_msg_count,
1382 x_msg_data => x_msg_data,
1383
1384 p_article_id => x_article_id,
1385 p_article_text => p_article_text,
1386 p_provision_yn => p_provision_yn,
1387 p_insert_by_reference => p_insert_by_reference,
1388 p_lock_text => p_lock_text,
1389 p_global_yn => p_global_yn,
1390 p_article_language => p_article_language,
1391 p_orig_system_reference_code => p_v_orig_system_reference_code,
1392 p_orig_system_reference_id1 => p_v_orig_system_reference_id1,
1393 p_orig_system_reference_id2 => p_v_orig_system_reference_id2,
1394 p_article_status => p_article_status,
1395 p_sav_release => p_sav_release,
1396 p_start_date => p_start_date,
1397 p_end_date => p_end_date,
1398 p_std_article_version_id => p_std_article_version_id,
1399 p_display_name => p_display_name,
1400 p_translated_yn => p_translated_yn,
1401 p_article_description => p_article_description,
1402 p_date_approved => p_date_approved,
1403 p_default_section => p_default_section,
1404 p_reference_source => p_reference_source,
1405 p_reference_text => p_reference_text,
1406 p_additional_instructions => p_additional_instructions,
1407 p_variation_description => p_variation_description,
1408 p_date_published => p_date_published,
1409 p_attribute_category => p_attribute_category,
1410 p_attribute1 => p_attribute1,
1411 p_attribute2 => p_attribute2,
1412 p_attribute3 => p_attribute3,
1413 p_attribute4 => p_attribute4,
1414 p_attribute5 => p_attribute5,
1415 p_attribute6 => p_attribute6,
1416 p_attribute7 => p_attribute7,
1417 p_attribute8 => p_attribute8,
1418 p_attribute9 => p_attribute9,
1419 p_attribute10 => p_attribute10,
1420 p_attribute11 => p_attribute11,
1421 p_attribute12 => p_attribute12,
1422 p_attribute13 => p_attribute13,
1423 p_attribute14 => p_attribute14,
1424 p_attribute15 => p_attribute15,
1425 --Clause Editing
1426 p_edited_in_word => p_edited_in_word,
1427 p_article_text_in_word => p_article_text_in_word,
1428 --clm
1429 p_variable_code => p_variable_code,
1430
1431 x_article_version_id => x_article_version_id
1432
1433 ) ;
1434 --dbms_output.put_line('In Create - x_article_version_id is '||x_article_version_id);
1435 --------------------------------------------
1436 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1438 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1439 RAISE FND_API.G_EXC_ERROR ;
1440 END IF;
1441 --------------------------------------------
1442
1443 -- Standard check of p_commit
1444 IF FND_API.To_Boolean( p_commit ) THEN
1445 COMMIT WORK;
1446 END IF;
1447 -- Standard call to get message count and if count is 1, get message info.
1448 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1449
1450 IF (l_debug = 'Y') THEN
1451 okc_debug.log('700: Leaving create_article', 2);
1452 END IF;
1453
1454 EXCEPTION
1455 WHEN FND_API.G_EXC_ERROR THEN
1456 IF (l_debug = 'Y') THEN
1457 okc_debug.log('800: Leaving create_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1458 END IF;
1459 ROLLBACK TO g_create_article_GRP;
1460 x_return_status := G_RET_STS_ERROR ;
1461 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1462
1463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1464 IF (l_debug = 'Y') THEN
1465 okc_debug.log('900: Leaving create_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1466 END IF;
1467 ROLLBACK TO g_create_article_GRP;
1468 x_return_status := G_RET_STS_UNEXP_ERROR ;
1469 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1470
1471 WHEN OTHERS THEN
1472 IF (l_debug = 'Y') THEN
1473 okc_debug.log('1000: Leaving create_article because of EXCEPTION: '||sqlerrm, 2);
1474 END IF;
1475
1476 ROLLBACK TO g_create_article_GRP;
1477 x_return_status := G_RET_STS_UNEXP_ERROR ;
1478 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1479 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1480 END IF;
1481 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1482
1483 END create_article;
1484 ---------------------------------------------------------------------------
1485 -- PROCEDURE lock_article
1486 ---------------------------------------------------------------------------
1487 PROCEDURE lock_article(
1488 p_api_version IN NUMBER,
1489 p_init_msg_list IN VARCHAR2 ,
1490
1491 x_return_status OUT NOCOPY VARCHAR2,
1492 x_msg_count OUT NOCOPY NUMBER,
1493 x_msg_data OUT NOCOPY VARCHAR2,
1494
1495 p_article_id IN NUMBER,
1496 p_article_version_id IN NUMBER,
1497 p_object_version_number IN NUMBER := NULL
1498 ) IS
1499 l_api_version CONSTANT NUMBER := 1;
1500 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_article';
1501 BEGIN
1502
1503 IF (l_debug = 'Y') THEN
1504 okc_debug.log('1100: Entered lock_article', 2);
1505 END IF;
1506
1507 -- Standard Start of API savepoint
1508 SAVEPOINT g_lock_article_GRP;
1509 -- Standard call to check for call compatibility.
1510 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1512 END IF;
1513 -- Initialize message list if p_init_msg_list is set to TRUE.
1514 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1515 FND_MSG_PUB.initialize;
1516 END IF;
1517 -- Initialize API return status to success
1518 x_return_status := FND_API.G_RET_STS_SUCCESS;
1519
1520 --------------------------------------------
1521 -- Calling Simple API for Locking A Row
1522 --------------------------------------------
1523 OKC_ARTICLES_ALL_PVT.lock_row(
1524 x_return_status => x_return_status,
1525 p_article_id => p_article_id,
1526 p_object_version_number => p_object_version_number
1527 );
1528 --------------------------------------------
1529 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1531 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1532 RAISE FND_API.G_EXC_ERROR ;
1533 END IF;
1534 --------------------------------------------
1535
1536 Lock_Article_Version(
1537 p_api_version => p_api_version ,
1538 p_init_msg_list => p_init_msg_list,
1539
1540 x_return_status => x_return_status,
1541 x_msg_count => x_msg_count,
1542 x_msg_data => x_msg_data,
1543
1544 p_article_version_id => p_article_version_id,
1545 p_object_version_number => p_object_version_number
1546 );
1547
1548 --------------------------------------------
1549 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1551 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1552 RAISE FND_API.G_EXC_ERROR ;
1553 END IF;
1554 --------------------------------------------
1555
1556 -- Standard call to get message count and if count is 1, get message info.
1557 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1558
1559 IF (l_debug = 'Y') THEN
1560 okc_debug.log('1200: Leaving lock_article', 2);
1561 END IF;
1562
1563 EXCEPTION
1564 WHEN FND_API.G_EXC_ERROR THEN
1565 IF (l_debug = 'Y') THEN
1566 okc_debug.log('1300: Leaving lock_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1567 END IF;
1568 ROLLBACK TO g_lock_article_GRP;
1569 x_return_status := G_RET_STS_ERROR ;
1570 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1571
1572 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1573 IF (l_debug = 'Y') THEN
1574 okc_debug.log('1400: Leaving lock_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1575 END IF;
1576 ROLLBACK TO g_lock_article_GRP;
1577 x_return_status := G_RET_STS_UNEXP_ERROR ;
1578 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1579
1580 WHEN OTHERS THEN
1581 IF (l_debug = 'Y') THEN
1582 okc_debug.log('1500: Leaving lock_article because of EXCEPTION: '||sqlerrm, 2);
1583 END IF;
1584
1585 ROLLBACK TO g_lock_article_GRP;
1586 x_return_status := G_RET_STS_UNEXP_ERROR ;
1587 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1588 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1589 END IF;
1590 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1591
1592 END lock_article;
1593 ---------------------------------------------------------------------------
1594 -- PROCEDURE update_article
1595 ---------------------------------------------------------------------------
1596 PROCEDURE update_article(
1597 p_api_version IN NUMBER,
1598 p_init_msg_list IN VARCHAR2 ,
1599 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1600 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1601
1602 x_return_status OUT NOCOPY VARCHAR2,
1603 x_msg_count OUT NOCOPY NUMBER,
1604 x_msg_data OUT NOCOPY VARCHAR2,
1605
1606 p_article_id IN NUMBER,
1607 p_article_title IN VARCHAR2,
1608 p_article_number IN VARCHAR2,
1609 p_standard_yn IN VARCHAR2,
1610 p_article_intent IN VARCHAR2,
1611 p_article_language IN VARCHAR2,
1612 p_article_type IN VARCHAR2,
1613 p_orig_system_reference_code IN VARCHAR2,
1614 p_orig_system_reference_id1 IN VARCHAR2,
1615 p_orig_system_reference_id2 IN VARCHAR2,
1616 p_cz_transfer_status_flag IN VARCHAR2,
1617 p_object_version_number IN NUMBER := NULL,
1618 -- Article Version Attributes
1619 p_article_version_id IN NUMBER,
1620 p_article_text IN CLOB,
1621 p_provision_yn IN VARCHAR2,
1622 p_insert_by_reference IN VARCHAR2,
1623 p_lock_text IN VARCHAR2,
1624 p_global_yn IN VARCHAR2,
1625 p_article_status IN VARCHAR2,
1626 p_sav_release IN VARCHAR2,
1627 p_start_date IN DATE,
1628 p_end_date IN DATE,
1629 p_std_article_version_id IN NUMBER,
1630 p_display_name IN VARCHAR2,
1631 p_translated_yn IN VARCHAR2,
1632 p_article_description IN VARCHAR2,
1633 p_date_approved IN DATE,
1634 p_default_section IN VARCHAR2,
1635 p_reference_source IN VARCHAR2,
1636 p_reference_text IN VARCHAR2,
1637 p_additional_instructions IN VARCHAR2,
1638 p_variation_description IN VARCHAR2,
1639 p_date_published IN DATE,
1640 p_v_orig_system_reference_code IN VARCHAR2,
1641 p_v_orig_system_reference_id1 IN VARCHAR2,
1642 p_v_orig_system_reference_id2 IN VARCHAR2,
1643 p_attribute_category IN VARCHAR2 := NULL,
1644 p_attribute1 IN VARCHAR2 := NULL,
1645 p_attribute2 IN VARCHAR2 := NULL,
1646 p_attribute3 IN VARCHAR2 := NULL,
1647 p_attribute4 IN VARCHAR2 := NULL,
1648 p_attribute5 IN VARCHAR2 := NULL,
1649 p_attribute6 IN VARCHAR2 := NULL,
1650 p_attribute7 IN VARCHAR2 := NULL,
1651 p_attribute8 IN VARCHAR2 := NULL,
1652 p_attribute9 IN VARCHAR2 := NULL,
1653 p_attribute10 IN VARCHAR2 := NULL,
1654 p_attribute11 IN VARCHAR2 := NULL,
1655 p_attribute12 IN VARCHAR2 := NULL,
1656 p_attribute13 IN VARCHAR2 := NULL,
1657 p_attribute14 IN VARCHAR2 := NULL,
1658 p_attribute15 IN VARCHAR2 := NULL,
1659 p_v_object_version_number IN NUMBER := NULL,
1660 --Clause Editing
1661 p_edited_in_word IN VARCHAR2 DEFAULT 'N',
1662 p_article_text_in_word IN BLOB DEFAULT NULL,
1663 --CLM
1664 p_variable_code IN VARCHAR2 DEFAULT NULL
1665 ) IS
1666
1667 l_api_version CONSTANT NUMBER := 1;
1668 l_api_name CONSTANT VARCHAR2(30) := 'g_update_article';
1669 l_article_intent OKC_ARTICLES_ALL.ARTICLE_INTENT%TYPE;
1670
1671 BEGIN
1672
1673 IF (l_debug = 'Y') THEN
1674 okc_debug.log('1600: Entered update_article', 2);
1675 okc_debug.log('1700: Locking row', 2);
1676 END IF;
1677
1678 -- Standard Start of API savepoint
1679 SAVEPOINT g_update_article_GRP;
1680 -- Standard call to check for call compatibility.
1681 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1683 END IF;
1684 -- Initialize message list if p_init_msg_list is set to TRUE.
1685 -- MOAC
1686 G_CURRENT_ORG_ID := mo_global.get_current_org_id() ;
1687 /*
1688 OPEN cur_org_csr;
1689 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
1690 CLOSE cur_org_csr;
1691 */
1692 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1693 FND_MSG_PUB.initialize;
1694 END IF;
1695 -- Initialize API return status to success
1696 x_return_status := FND_API.G_RET_STS_SUCCESS;
1697
1698 -- MOAC
1699 IF G_CURRENT_ORG_ID IS NULL Then
1700 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
1701 RAISE FND_API.G_EXC_ERROR ;
1702 END IF;
1703
1704 --------------------------------------------
1705 -- Calling Simple API for Updating A Row
1706 --------------------------------------------
1707 OKC_ARTICLES_ALL_PVT.Update_Row(
1708 p_validation_level => p_validation_level,
1709 x_return_status => x_return_status,
1710 x_article_intent => l_article_intent,
1711 p_article_id => p_article_id,
1712 p_article_title => p_article_title,
1713 p_org_id => G_CURRENT_ORG_ID,
1714 p_article_number => p_article_number,
1715 p_standard_yn => p_standard_yn,
1716 p_article_intent => p_article_intent,
1717 p_article_language => p_article_language,
1718 p_article_type => p_article_type,
1719 p_orig_system_reference_code => p_orig_system_reference_code,
1720 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1721 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1722 p_cz_transfer_status_flag => p_cz_transfer_status_flag,
1723 p_object_version_number => p_object_version_number
1724 );
1725 --------------------------------------------
1726 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1728 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1729 RAISE FND_API.G_EXC_ERROR ;
1730 END IF;
1731 --------------------------------------------
1732 --dbms_output.put_line('After:'||p_article_language);
1733
1734 Update_Article_Version(
1735 p_api_version => p_api_version ,
1736 p_init_msg_list => p_init_msg_list,
1737 p_validation_level => p_validation_level,
1738 p_commit => p_commit,
1739 p_article_intent => l_article_intent,
1740
1741 x_return_status => x_return_status,
1742 x_msg_count => x_msg_count,
1743 x_msg_data => x_msg_data,
1744
1745 p_article_version_id => p_article_version_id,
1746 p_article_id => p_article_id,
1747 p_orig_system_reference_code => p_v_orig_system_reference_code,
1748 p_orig_system_reference_id1 => p_v_orig_system_reference_id1,
1749 p_orig_system_reference_id2 => p_v_orig_system_reference_id2,
1750 p_article_text => p_article_text,
1751 p_provision_yn => p_provision_yn,
1752 p_insert_by_reference => p_insert_by_reference,
1753 p_lock_text => p_lock_text,
1754 p_global_yn => p_global_yn,
1755 p_article_language => p_article_language,
1756 p_article_status => p_article_status,
1757 p_sav_release => p_sav_release,
1758 p_start_date => p_start_date,
1759 p_end_date => p_end_date,
1760 p_std_article_version_id => p_std_article_version_id,
1761 p_display_name => p_display_name,
1762 p_translated_yn => p_translated_yn,
1763 p_article_description => p_article_description,
1764 p_date_approved => p_date_approved,
1765 p_default_section => p_default_section,
1766 p_reference_source => p_reference_source,
1767 p_reference_text => p_reference_text,
1768 p_additional_instructions => p_additional_instructions,
1769 p_variation_description => p_variation_description,
1770 p_date_published => p_date_published,
1771 p_attribute_category => p_attribute_category,
1772 p_attribute1 => p_attribute1,
1773 p_attribute2 => p_attribute2,
1774 p_attribute3 => p_attribute3,
1775 p_attribute4 => p_attribute4,
1776 p_attribute5 => p_attribute5,
1777 p_attribute6 => p_attribute6,
1778 p_attribute7 => p_attribute7,
1779 p_attribute8 => p_attribute8,
1780 p_attribute9 => p_attribute9,
1781 p_attribute10 => p_attribute10,
1782 p_attribute11 => p_attribute11,
1783 p_attribute12 => p_attribute12,
1784 p_attribute13 => p_attribute13,
1785 p_attribute14 => p_attribute14,
1786 p_attribute15 => p_attribute15,
1787 p_object_version_number => p_v_object_version_number,
1788 --Clause Editing
1789 p_edited_in_word => p_edited_in_word,
1790 p_article_text_in_word => p_article_text_in_word,
1791 --clm
1792 p_variable_code => p_variable_code
1793 );
1794
1795 --------------------------------------------
1796 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1798 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1799 RAISE FND_API.G_EXC_ERROR ;
1800 END IF;
1801 --------------------------------------------
1802 -- Standard check of p_commit
1803 IF FND_API.To_Boolean( p_commit ) THEN
1804 COMMIT WORK;
1805 END IF;
1806 -- Standard call to get message count and if count is 1, get message info.
1807 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1808
1809 IF (l_debug = 'Y') THEN
1810 okc_debug.log('1800: Leaving update_article', 2);
1811 END IF;
1812
1813 EXCEPTION
1814 WHEN FND_API.G_EXC_ERROR THEN
1815 IF (l_debug = 'Y') THEN
1816 okc_debug.log('1900: Leaving update_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1817 END IF;
1818 ROLLBACK TO g_update_article_GRP;
1819 x_return_status := G_RET_STS_ERROR ;
1820 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1821
1822 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1823 IF (l_debug = 'Y') THEN
1824 okc_debug.log('2000: Leaving update_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1825 END IF;
1826 ROLLBACK TO g_update_article_GRP;
1827 x_return_status := G_RET_STS_UNEXP_ERROR ;
1828 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1829
1830 WHEN OTHERS THEN
1831 IF (l_debug = 'Y') THEN
1832 okc_debug.log('2100: Leaving update_article because of EXCEPTION: '||sqlerrm, 2);
1833 END IF;
1834
1835 ROLLBACK TO g_update_article_GRP;
1836 x_return_status := G_RET_STS_UNEXP_ERROR ;
1837 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1838 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1839 END IF;
1840 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
1841
1842 END update_article;
1843
1844 ---------------------------------------------------------------------------
1845 -- PROCEDURE delete_article
1846 ---------------------------------------------------------------------------
1847 PROCEDURE delete_article(
1848 p_api_version IN NUMBER,
1849 p_init_msg_list IN VARCHAR2 ,
1850 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1851
1852 x_return_status OUT NOCOPY VARCHAR2,
1853 x_msg_count OUT NOCOPY NUMBER,
1854 x_msg_data OUT NOCOPY VARCHAR2,
1855
1856 p_article_id IN NUMBER,
1857 p_article_version_id IN NUMBER,
1858 p_object_version_number IN NUMBER := NULL
1859 ) IS
1860 l_api_version CONSTANT NUMBER := 1;
1861 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_article';
1862 l_status VARCHAR2(30) ;
1863 l_standard_yn VARCHAR2(1) ;
1864 l_only_version VARCHAR2(1) := 'T';
1865 l_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;
1866 l_dummy1 VARCHAR2(1) := '?';
1867
1868 Cursor l_status_csr (cp_article_id IN NUMBER,
1869 cp_article_version_id IN NUMBER) IS
1870 SELECT article_status,standard_yn, adoption_type
1871 FROM okc_article_versions av,okc_articles_all aa
1872 WHERE aa.article_id = av.article_id
1873 AND av.article_id = cp_article_id
1874 AND av.article_version_id = cp_article_version_id;
1875
1876
1877 CURSOR l_only_version_csr(cp_article_id IN NUMBER,
1878 cp_article_version_id IN NUMBER) IS
1879 SELECT 'F'
1880 FROM OKC_ARTICLE_VERSIONS A
1881 WHERE A.ARTICLE_ID = cp_article_id
1882 AND A.ARTICLE_VERSION_ID <> cp_article_version_id
1883 AND rownum < 2 ;
1884
1885 CURSOR l_template_csr (cp_article_id IN NUMBER) is
1886 SELECT '1' from OKC_K_ARTICLES_B
1887 WHERE sav_sae_id = cp_article_id
1888 AND document_type = 'TEMPLATE';
1889 BEGIN
1890
1891 IF (l_debug = 'Y') THEN
1892 okc_debug.log('2200: Entered delete_article', 2);
1893 END IF;
1894
1895 -- Standard Start of API savepoint
1896 SAVEPOINT g_delete_article_GRP;
1897 -- Standard call to check for call compatibility.
1898 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1900 END IF;
1901 -- Initialize message list if p_init_msg_list is set to TRUE.
1902 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1903 FND_MSG_PUB.initialize;
1904 END IF;
1905 -- Initialize API return status to success
1906 x_return_status := FND_API.G_RET_STS_SUCCESS;
1907
1908
1909 OPEN l_status_csr (p_article_id, p_article_version_id);
1910 FETCH l_status_csr INTO l_status,l_standard_yn, l_adoption_type;
1911 CLOSE l_status_csr;
1912
1913 IF l_standard_yn = 'Y' THEN
1914 IF l_status NOT IN ( 'DRAFT','REJECTED') THEN
1915 IF (l_debug = 'Y') THEN
1916 Okc_Debug.Log('2200: - Article Status is not Draft or Rejected,It cannot be deleted',2);
1917 END IF;
1918 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_DEL_INV_STATUS');
1919 x_return_status := G_RET_STS_ERROR;
1920 RAISE FND_API.G_EXC_ERROR;
1921 ELSE
1922 OPEN l_only_version_csr (p_article_id, p_article_version_id);
1923 FETCH l_only_version_csr INTO l_only_version;
1924 CLOSE l_only_version_csr;
1925 --dbms_output.put_line('Only version available:' || l_only_version ||
1926 -- p_article_id ||'*'||p_article_version_id);
1927 IF l_only_version = 'T' THEN
1928 IF (l_debug = 'Y') THEN
1929 Okc_Debug.Log('2200: - Article Version is the only version',2);
1930 END IF;
1931 OPEN l_template_csr (p_article_id);
1932 FETCH l_template_csr INTO l_dummy1;
1933 CLOSE l_template_csr;
1934 --dbms_output.put_line('Used in Template:' || l_dummy1);
1935 IF l_dummy1 = '1' THEN
1936 IF (l_debug = 'Y') THEN
1937 Okc_Debug.Log('2200: - Article Version is already Used in the Templates',2);
1938 END IF;
1939 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_USED_IN_TEMPL');
1940 x_return_status := G_RET_STS_ERROR;
1941 RAISE FND_API.G_EXC_ERROR;
1942 ELSE -- Check if it used in any of the Rules (Bug 3971186)
1943 IF (OKC_XPRT_UTIL_PVT.ok_to_delete_clause(p_article_id) = 'N') THEN
1944 IF (l_debug = 'Y') THEN
1945 Okc_Debug.Log('2200: - Article Version is already Used in the Rules',2);
1946 END IF;
1947 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_USED_IN_RULES');
1948 x_return_status := G_RET_STS_ERROR;
1949 RAISE FND_API.G_EXC_ERROR;
1950 ELSE
1951 OKC_ARTICLES_ALL_PVT.Delete_Row(
1952 x_return_status => x_return_status,
1953 p_article_id => p_article_id,
1954 p_object_version_number => NULL
1955 );
1956 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1958 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1959 RAISE FND_API.G_EXC_ERROR ;
1960 END IF;
1961 DELETE FROM OKC_ARTICLE_RELATNS_ALL -- delete all relationships
1962 WHERE source_article_id = p_article_id OR
1963 target_article_id = p_article_id;
1964 DELETE FROM OKC_FOLDER_CONTENTS -- delete all folder contents
1965 WHERE member_id = p_article_id ;
1966 END IF;
1967 END IF;
1968 END IF;
1969 END IF;
1970 ELSE -- in the case of non standard articles
1971 OKC_ARTICLES_ALL_PVT.Delete_Row(
1972 x_return_status => x_return_status,
1973 p_article_id => p_article_id,
1974 p_object_version_number => NULL
1975 );
1976 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1977 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1978 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1979 RAISE FND_API.G_EXC_ERROR ;
1980 END IF;
1981 END IF;
1982 Delete_Article_Version(
1983 p_api_version => p_api_version ,
1984 p_init_msg_list => p_init_msg_list,
1985 p_commit => p_commit,
1986 p_standard_yn => l_standard_yn,
1987 p_adoption_type => l_adoption_type,
1988 x_return_status => x_return_status,
1989 x_msg_count => x_msg_count,
1990 x_msg_data => x_msg_data,
1991 p_article_version_id => p_article_version_id,
1992 p_only_version => l_only_version,
1993 p_object_version_number => p_object_version_number
1994 );
1995
1996 --------------------------------------------
1997 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1999 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2000 RAISE FND_API.G_EXC_ERROR ;
2001 END IF;
2002 --------------------------------------------
2003 -- Standard check of p_commit
2004 IF FND_API.To_Boolean( p_commit ) THEN
2005 COMMIT WORK;
2006 END IF;
2007 -- Standard call to get message count and if count is 1, get message info.
2008 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2009
2010 IF (l_debug = 'Y') THEN
2011 okc_debug.log('2300: Leaving delete_article', 2);
2012 END IF;
2013
2014 EXCEPTION
2015 WHEN FND_API.G_EXC_ERROR THEN
2016 IF (l_debug = 'Y') THEN
2017 okc_debug.log('2400: Leaving delete_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
2018 END IF;
2019 IF l_status_csr%ISOPEN THEN
2020 CLOSE l_status_csr;
2021 END IF;
2022 IF l_only_version_csr%ISOPEN THEN
2023 CLOSE l_only_version_csr;
2024 END IF;
2025 IF l_template_csr%ISOPEN THEN
2026 CLOSE l_template_csr;
2027 END IF;
2028 ROLLBACK TO g_delete_article_GRP;
2029 x_return_status := G_RET_STS_ERROR ;
2030 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2031
2032 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2033 IF (l_debug = 'Y') THEN
2034 okc_debug.log('2500: Leaving delete_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2035 END IF;
2036 IF l_status_csr%ISOPEN THEN
2037 CLOSE l_status_csr;
2038 END IF;
2039 IF l_only_version_csr%ISOPEN THEN
2040 CLOSE l_only_version_csr;
2041 END IF;
2042 IF l_template_csr%ISOPEN THEN
2043 CLOSE l_template_csr;
2044 END IF;
2045 ROLLBACK TO g_delete_article_GRP;
2046 x_return_status := G_RET_STS_UNEXP_ERROR ;
2047 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2048
2049 WHEN OTHERS THEN
2050 IF (l_debug = 'Y') THEN
2051 okc_debug.log('2600: Leaving delete_article because of EXCEPTION: '||sqlerrm, 2);
2052 END IF;
2053 IF l_status_csr%ISOPEN THEN
2054 CLOSE l_status_csr;
2055 END IF;
2056 IF l_only_version_csr%ISOPEN THEN
2057 CLOSE l_only_version_csr;
2058 END IF;
2059 IF l_template_csr%ISOPEN THEN
2060 CLOSE l_template_csr;
2061 END IF;
2062 ROLLBACK TO g_delete_article_GRP;
2063 x_return_status := G_RET_STS_UNEXP_ERROR ;
2064 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2065 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2066 END IF;
2067 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2068
2069 END delete_article;
2070
2071 ---------------------------------------
2072 -- PROCEDURE validate_article_version --
2073 ---------------------------------------
2074 PROCEDURE validate_article_version(
2075 p_api_version IN NUMBER,
2076 p_init_msg_list IN VARCHAR2 ,
2077 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2078 p_import_action IN VARCHAR2 := NULL,
2079 x_return_status OUT NOCOPY VARCHAR2,
2080 x_msg_count OUT NOCOPY NUMBER,
2081 x_msg_data OUT NOCOPY VARCHAR2,
2082 x_earlier_version_id OUT NOCOPY NUMBER,
2083 x_earlier_version_number OUT NOCOPY NUMBER,
2084 x_earlier_adoption_type OUT NOCOPY VARCHAR2,
2085 p_article_version_id IN NUMBER,
2086 p_article_id IN NUMBER,
2087 p_article_version_number IN NUMBER,
2088 p_article_text IN CLOB,
2089 p_provision_yn IN VARCHAR2,
2090 p_insert_by_reference IN VARCHAR2,
2091 p_lock_text IN VARCHAR2,
2092 p_global_yn IN VARCHAR2,
2093 p_article_language IN VARCHAR2,
2094 p_article_status IN VARCHAR2,
2095 p_sav_release IN VARCHAR2,
2096 p_start_date IN DATE,
2097 p_end_date IN DATE,
2098 p_std_article_version_id IN NUMBER,
2099 p_display_name IN VARCHAR2,
2100 p_translated_yn IN VARCHAR2,
2101 p_article_description IN VARCHAR2,
2102 p_date_approved IN DATE,
2103 p_default_section IN VARCHAR2,
2104 p_reference_source IN VARCHAR2,
2105 p_reference_text IN VARCHAR2,
2106 p_orig_system_reference_code IN VARCHAR2 ,
2107 p_orig_system_reference_id1 IN VARCHAR2 ,
2108 p_orig_system_reference_id2 IN VARCHAR2 ,
2109 p_additional_instructions IN VARCHAR2 ,
2110 p_variation_description IN VARCHAR2,
2111 p_date_published IN DATE,
2112 p_attribute_category IN VARCHAR2 := NULL,
2113 p_attribute1 IN VARCHAR2 := NULL,
2114 p_attribute2 IN VARCHAR2 := NULL,
2115 p_attribute3 IN VARCHAR2 := NULL,
2116 p_attribute4 IN VARCHAR2 := NULL,
2117 p_attribute5 IN VARCHAR2 := NULL,
2118 p_attribute6 IN VARCHAR2 := NULL,
2119 p_attribute7 IN VARCHAR2 := NULL,
2120 p_attribute8 IN VARCHAR2 := NULL,
2121 p_attribute9 IN VARCHAR2 := NULL,
2122 p_attribute10 IN VARCHAR2 := NULL,
2123 p_attribute11 IN VARCHAR2 := NULL,
2124 p_attribute12 IN VARCHAR2 := NULL,
2125 p_attribute13 IN VARCHAR2 := NULL,
2126 p_attribute14 IN VARCHAR2 := NULL,
2127 p_attribute15 IN VARCHAR2 := NULL,
2128 --Clause Editing
2129 p_edited_in_word IN VARCHAR2 DEFAULT 'N',
2130 p_article_text_in_word IN BLOB DEFAULT NULL,
2131 p_variable_code IN VARCHAR2 DEFAULT NULL
2132 ) IS
2133 l_api_version CONSTANT NUMBER := 1;
2134 l_api_name CONSTANT VARCHAR2(30) := 'g_validate_article_version';
2135 l_earlier_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;
2136
2137 BEGIN
2138
2139 IF (l_debug = 'Y') THEN
2140 okc_debug.log('100: Entered validate_article_version', 2);
2141 END IF;
2142
2143 -- Standard Start of API savepoint
2144 SAVEPOINT g_validate_article_version_GRP;
2145 -- Standard call to check for call compatibility.
2146 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2148 END IF;
2149 -- Initialize message list if p_init_msg_list is set to TRUE.
2150 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2151 FND_MSG_PUB.initialize;
2152 END IF;
2153 -- Initialize API return status to success
2154 x_return_status := FND_API.G_RET_STS_SUCCESS;
2155
2156 --------------------------------------------
2157 -- Calling Simple API for Article Version Validation
2158 --------------------------------------------
2159 OKC_ARTICLE_VERSIONS_PVT.Validate_Row(
2160 p_validation_level => p_validation_level,
2161 p_import_action => p_import_action,
2162 x_return_status => x_return_status,
2163 x_earlier_adoption_type => x_earlier_adoption_type,
2164 x_earlier_version_id => x_earlier_version_id,
2165 x_earlier_version_number => x_earlier_version_number,
2166 p_article_version_id => p_article_version_id,
2167 p_article_id => p_article_id,
2168 p_article_version_number => p_article_version_number,
2169 p_article_text => p_article_text,
2170 p_provision_yn => p_provision_yn,
2171 p_insert_by_reference => p_insert_by_reference,
2172 p_lock_text => p_lock_text,
2173 p_global_yn => p_global_yn,
2174 p_article_language => p_article_language,
2175 p_article_status => p_article_status,
2176 p_sav_release => p_sav_release,
2177 p_start_date => p_start_date,
2178 p_end_date => p_end_date,
2179 p_std_article_version_id => p_std_article_version_id,
2180 p_display_name => p_display_name,
2181 p_translated_yn => p_translated_yn,
2182 p_article_description => p_article_description,
2183 p_date_approved => p_date_approved,
2184 p_default_section => p_default_section,
2185 p_reference_source => p_reference_source,
2186 p_reference_text => p_reference_text,
2187 p_orig_system_reference_code => p_orig_system_reference_code,
2188 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2189 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2190 p_additional_instructions => p_additional_instructions,
2191 p_variation_description => p_variation_description,
2192 p_date_published => p_date_published,
2193 p_attribute_category => p_attribute_category,
2194 p_attribute1 => p_attribute1,
2195 p_attribute2 => p_attribute2,
2196 p_attribute3 => p_attribute3,
2197 p_attribute4 => p_attribute4,
2198 p_attribute5 => p_attribute5,
2199 p_attribute6 => p_attribute6,
2200 p_attribute7 => p_attribute7,
2201 p_attribute8 => p_attribute8,
2202 p_attribute9 => p_attribute9,
2203 p_attribute10 => p_attribute10,
2204 p_attribute11 => p_attribute11,
2205 p_attribute12 => p_attribute12,
2206 p_attribute13 => p_attribute13,
2207 p_attribute14 => p_attribute14,
2208 p_attribute15 => p_attribute15,
2209 p_object_version_number => NULL,
2210 --Clause Editing
2211 p_edited_in_word => p_edited_in_word,
2212 p_article_text_in_word => p_article_text_in_word,
2213 --clm
2214 p_variable_code => p_variable_code
2215 );
2216 --------------------------------------------
2217 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2219 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2220 RAISE FND_API.G_EXC_ERROR ;
2221 END IF;
2222 --------------------------------------------
2223
2224 -- Standard call to get message count and if count is 1, get message info.
2225 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2226
2227 IF (l_debug = 'Y') THEN
2228 okc_debug.log('200: Leaving validate_article_version', 2);
2229 END IF;
2230
2231 EXCEPTION
2232 WHEN FND_API.G_EXC_ERROR THEN
2233 IF (l_debug = 'Y') THEN
2234 okc_debug.log('300: Leaving Validate_article_version: OKC_API.G_EXCEPTION_ERROR Exception', 2);
2235 END IF;
2236 ROLLBACK TO g_validate_article_version_GRP;
2237 x_return_status := G_RET_STS_ERROR ;
2238 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2239
2240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2241 IF (l_debug = 'Y') THEN
2242 okc_debug.log('400: Leaving Validate_article_version: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2243 END IF;
2244 ROLLBACK TO g_validate_article_version_GRP;
2245 x_return_status := G_RET_STS_UNEXP_ERROR ;
2246 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2247
2248 WHEN OTHERS THEN
2249 IF (l_debug = 'Y') THEN
2250 okc_debug.log('500: Leaving Validate_article_version because of EXCEPTION: '||sqlerrm, 2);
2251 END IF;
2252
2253 ROLLBACK TO g_validate_article_version_GRP;
2254 x_return_status := G_RET_STS_UNEXP_ERROR ;
2255 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2256 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2257 END IF;
2258 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2259
2260 END validate_article_version;
2261
2262 -------------------------------------
2263 -- PROCEDURE Create_article_version
2264 -------------------------------------
2265 PROCEDURE create_article_version(
2266 p_api_version IN NUMBER,
2267 p_init_msg_list IN VARCHAR2 ,
2268 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2269 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2270 p_article_intent IN VARCHAR2 := NULL,
2271 p_standard_yn IN VARCHAR2 := 'Y',
2272 p_global_article_version_id IN NUMBER := NULL,
2273
2274 x_return_status OUT NOCOPY VARCHAR2,
2275 x_msg_count OUT NOCOPY NUMBER,
2276 x_msg_data OUT NOCOPY VARCHAR2,
2277
2278 p_article_id IN NUMBER,
2279 p_article_text IN CLOB,
2280 p_provision_yn IN VARCHAR2,
2281 p_insert_by_reference IN VARCHAR2,
2282 p_lock_text IN VARCHAR2,
2283 p_global_yn IN VARCHAR2,
2284 p_article_language IN VARCHAR2,
2285 p_article_status IN VARCHAR2,
2286 p_sav_release IN VARCHAR2,
2287 p_start_date IN DATE,
2288 p_end_date IN DATE,
2289 p_std_article_version_id IN NUMBER,
2290 p_display_name IN VARCHAR2,
2291 p_translated_yn IN VARCHAR2,
2292 p_article_description IN VARCHAR2,
2293 p_date_approved IN DATE,
2294 p_default_section IN VARCHAR2,
2295 p_reference_source IN VARCHAR2,
2296 p_reference_text IN VARCHAR2,
2297 p_orig_system_reference_code IN VARCHAR2,
2298 p_orig_system_reference_id1 IN VARCHAR2,
2299 p_orig_system_reference_id2 IN VARCHAR2,
2300 p_additional_instructions IN VARCHAR2,
2301 p_variation_description IN VARCHAR2,
2302 p_date_published IN DATE,
2303
2304 p_attribute_category IN VARCHAR2 := NULL,
2305 p_attribute1 IN VARCHAR2 := NULL,
2306 p_attribute2 IN VARCHAR2 := NULL,
2307 p_attribute3 IN VARCHAR2 := NULL,
2308 p_attribute4 IN VARCHAR2 := NULL,
2309 p_attribute5 IN VARCHAR2 := NULL,
2310 p_attribute6 IN VARCHAR2 := NULL,
2311 p_attribute7 IN VARCHAR2 := NULL,
2312 p_attribute8 IN VARCHAR2 := NULL,
2313 p_attribute9 IN VARCHAR2 := NULL,
2314 p_attribute10 IN VARCHAR2 := NULL,
2315 p_attribute11 IN VARCHAR2 := NULL,
2316 p_attribute12 IN VARCHAR2 := NULL,
2317 p_attribute13 IN VARCHAR2 := NULL,
2318 p_attribute14 IN VARCHAR2 := NULL,
2319 p_attribute15 IN VARCHAR2 := NULL,
2320 --Clause Editing
2321 p_edited_in_word IN VARCHAR2 DEFAULT 'N',
2322 p_article_text_in_word IN BLOB DEFAULT NULL,
2323 --clm
2324 p_variable_code IN VARCHAR2 DEFAULT NULL,
2325
2326 x_article_version_id OUT NOCOPY NUMBER
2327
2328 ) IS
2329
2330 l_api_version CONSTANT NUMBER := 1;
2331 l_api_name CONSTANT VARCHAR2(30) := 'g_create_article_version';
2332 l_object_version_number OKC_ARTICLE_VERSIONS.OBJECT_VERSION_NUMBER%TYPE := 1;
2333 l_created_by OKC_ARTICLE_VERSIONS.CREATED_BY%TYPE;
2334 l_creation_date OKC_ARTICLE_VERSIONS.CREATION_DATE%TYPE;
2335 l_last_updated_by OKC_ARTICLE_VERSIONS.LAST_UPDATED_BY%TYPE;
2336 l_last_update_login OKC_ARTICLE_VERSIONS.LAST_UPDATE_LOGIN%TYPE;
2337 l_last_update_date OKC_ARTICLE_VERSIONS.LAST_UPDATE_DATE%TYPE;
2338 l_adoption_type OKC_ARTICLE_ADOPTIONS.ADOPTION_TYPE%TYPE;
2339 l_earlier_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;
2340 l_variable_code_tbl variable_code_tbl_type;
2341 l_org_id NUMBER;
2342 l_global_article_version_id NUMBER;
2343 l_global_version_id_out NUMBER;
2344 l_local_org_id NUMBER;
2345 l_local_article_version_id NUMBER;
2346 l_earlier_version_id NUMBER;
2347 l_row_notfound BOOLEAN := TRUE;
2348 l_rowid ROWID;
2349 l_article_text CLOB;
2350 l_article_status OKC_ARTICLE_VERSIONS.ARTICLE_STATUS%TYPE;
2351 i NUMBER := 0;
2352 l_clob CLOB;
2353 -- Bug 3917777
2354 l_user_id NUMBER := FND_GLOBAL.USER_ID;
2355 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
2356 BEGIN
2357
2358 IF (l_debug = 'Y') THEN
2359 okc_debug.log('600: Entered create_article_version', 2);
2360 END IF;
2361
2362 -- Standard Start of API savepoint
2363 SAVEPOINT g_create_article_version_GRP;
2364 -- Standard call to check for call compatibility.
2365 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2367 END IF;
2368 -- MOAC
2369 G_CURRENT_ORG_ID := mo_global.get_current_org_id() ;
2370 /*
2371 OPEN cur_org_csr;
2372 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
2373 CLOSE cur_org_csr;
2374 */
2375 -- Initialize message list if p_init_msg_list is set to TRUE.
2376 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2377 FND_MSG_PUB.initialize;
2378 END IF;
2379 -- Initialize API return status to success
2380 x_return_status := FND_API.G_RET_STS_SUCCESS;
2381
2382 -- MOAC
2383 IF G_CURRENT_ORG_ID IS NULL Then
2384 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
2385 RAISE FND_API.G_EXC_ERROR ;
2386 END IF;
2387
2388 l_article_text := p_article_text;
2389 DBMS_LOB.CREATETEMPORARY(l_clob,true);
2390 parse_n_replace_text(
2391 p_api_version => p_api_version,
2392 p_init_msg_list => p_init_msg_list,
2393 p_article_text => l_article_text,
2394 p_dest_clob => l_clob,
2395 p_calling_mode => 'CALLED_FROM_CREATE_UPDATE',
2396 p_replace_text => 'Y',
2397 p_article_intent => p_article_intent,
2398 p_language => USERENV('LANG'),
2399 x_return_status => x_return_status,
2400 x_msg_count => x_msg_count,
2401 x_msg_data => x_msg_data,
2402 x_variables_tbl => l_variable_code_tbl
2403 ) ;
2404 --dbms_lob.freetemporary(l_clob);
2405 --------------------------------------------
2406 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2408 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2409 RAISE FND_API.G_EXC_ERROR ;
2410 END IF;
2411 --------------------------------------------
2412 -- UIs may be returning EXPIRED status (derived) from VOs.
2413 IF p_article_status = 'EXPIRED' THEN
2414 l_article_status := 'APPROVED';
2415 ELSE
2416 l_article_status := p_article_status;
2417 END IF;
2418
2419 --------------------------------------------
2420 -- Calling Simple API for Creating A Row
2421 --------------------------------------------
2422 OKC_ARTICLE_VERSIONS_PVT.Insert_Row(
2423 p_validation_level => p_validation_level,
2424 x_return_status => x_return_status,
2425 p_article_id => p_article_id,
2426 p_article_text => l_clob,
2427 p_provision_yn => p_provision_yn,
2428 p_insert_by_reference => p_insert_by_reference,
2429 p_lock_text => p_lock_text,
2430 p_global_yn => p_global_yn,
2431 p_article_language => p_article_language,
2432 p_article_status => l_article_status,
2433 p_sav_release => p_sav_release,
2434 p_start_date => p_start_date,
2435 p_end_date => p_end_date,
2436 p_std_article_version_id => p_std_article_version_id,
2437 p_display_name => p_display_name,
2438 p_translated_yn => p_translated_yn,
2439 p_article_description => p_article_description,
2440 p_date_approved => p_date_approved,
2441 p_default_section => p_default_section,
2442 p_reference_source => p_reference_source,
2443 p_reference_text => p_reference_text,
2444 p_orig_system_reference_code => p_orig_system_reference_code,
2445 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2446 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2447 p_additional_instructions => p_additional_instructions,
2448 p_variation_description => p_variation_description,
2449 p_date_published => p_date_published,
2450 p_current_org_id => G_CURRENT_ORG_ID,
2451 p_attribute_category => p_attribute_category,
2452 p_attribute1 => p_attribute1,
2453 p_attribute2 => p_attribute2,
2454 p_attribute3 => p_attribute3,
2455 p_attribute4 => p_attribute4,
2456 p_attribute5 => p_attribute5,
2457 p_attribute6 => p_attribute6,
2458 p_attribute7 => p_attribute7,
2459 p_attribute8 => p_attribute8,
2460 p_attribute9 => p_attribute9,
2461 p_attribute10 => p_attribute10,
2462 p_attribute11 => p_attribute11,
2463 p_attribute12 => p_attribute12,
2464 p_attribute13 => p_attribute13,
2465 p_attribute14 => p_attribute14,
2466 p_attribute15 => p_attribute15,
2467 --Clause Editing
2468 p_edited_in_word => p_edited_in_word,
2469 p_article_text_in_word => p_article_text_in_word,
2470 --clm
2471 p_variable_code => p_variable_code,
2472 x_earlier_adoption_type => l_earlier_adoption_type,
2473 x_earlier_version_id => l_earlier_version_id,
2474 x_article_version_id => x_article_version_id
2475 );
2476 --------------------------------------------
2477 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2479 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2480 RAISE FND_API.G_EXC_ERROR ;
2481 END IF;
2482 --------------------------------------------
2483
2484 -- Parse through the Article text, replace and extract the variables
2485
2486 -- This is the only API that does DML of article variables. No need for
2487 -- a simple API.
2488
2489 i := 0;
2490 IF (l_variable_code_tbl.COUNT > 0) THEN
2491 FORALL i IN l_variable_code_tbl.FIRST..l_variable_code_tbl.LAST
2492 INSERT INTO OKC_ARTICLE_VARIABLES
2493 (
2494 ARTICLE_VERSION_ID ,
2495 VARIABLE_CODE ,
2496 OBJECT_VERSION_NUMBER ,
2497 CREATED_BY ,
2498 CREATION_DATE ,
2499 LAST_UPDATE_DATE ,
2500 LAST_UPDATED_BY ,
2501 LAST_UPDATE_LOGIN
2502 )
2503 VALUES
2504 (
2505 x_article_version_id,
2506 l_variable_code_tbl(i),
2507 1.0,
2508 l_user_id,
2509 sysdate,
2510 sysdate,
2511 l_user_id,
2512 l_login_id
2513 );
2514 END IF;
2515
2516 -- Adoption row should not be created if the global/local org is -99 and
2517 -- if the global org = local org
2518 -- Adoption rows are not applicable for NON Standard Articles
2519 -- Global Article Version Id will be passed for "Localize" cases
2520 -- In the case of new version creation, global article version id will not be
2521 -- passed. The system will evaluate the creation of adoption based on the
2522 -- adoption row created from an earlier version.
2523
2524 --dbms_output.put_line('Global Org Id: '||G_GLOBAL_ORG_ID ||'* Current Org Id: '||G_CURRENT_ORG_ID);
2525 -- MOAC
2526 IF G_CURRENT_ORG_ID IS NULL Then
2527 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
2528 RAISE FND_API.G_EXC_ERROR ;
2529 END IF;
2530
2531 IF (G_CURRENT_ORG_ID = G_GLOBAL_ORG_ID OR
2532 G_CURRENT_ORG_ID = -99 OR
2533 G_GLOBAL_ORG_ID = -99 ) THEN
2534 NULL;
2535 ELSIF p_standard_yn = 'Y' AND
2536 nvl(l_earlier_adoption_type,'X') <> 'LOCAL' THEN
2537 OKC_ADOPTIONS_GRP.CREATE_LOCAL_ADOPTION_DETAILS
2538 (
2539 p_api_version => p_api_version,
2540 p_init_msg_list => p_init_msg_list,
2541 p_validation_level => p_validation_level,
2542 x_adoption_type => l_adoption_type,
2543 x_return_status => x_return_status,
2544 x_msg_count => x_msg_count,
2545 x_msg_data => x_msg_data,
2546 p_article_status => p_article_status,
2547 p_earlier_local_version_id => l_earlier_version_id,
2548 p_local_article_version_id => x_article_version_id,
2549 p_global_article_version_id => p_global_article_version_id,
2550 p_local_org_id => G_CURRENT_ORG_ID
2551 );
2552 --------------------------------------------
2553 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2555 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2556 RAISE FND_API.G_EXC_ERROR ;
2557 END IF;
2558 --------------------------------------------
2559
2560 -- Update denormalized adoption type in article versions table
2561 -- No need to call the update API as the row is still locked and it will be an overhead
2562
2563 UPDATE OKC_ARTICLE_VERSIONS
2564 SET adoption_type = nvl(l_adoption_type, 'LOCAL')
2565 WHERE article_version_id = x_article_version_id;
2566 END IF;
2567
2568 /*kkolukul: CLM changes*/
2569 -- Create Article section mappings based on variable name
2570 -- Insert at one shot ..
2571
2572 INSERT INTO OKC_ART_VAR_SECTIONS
2573 (
2574 VARIABLE_CODE,
2575 VARIABLE_VALUE_ID,
2576 VARIABLE_VALUE,
2577 ARTICLE_ID,
2578 SCN_CODE,
2579 ARTICLE_VERSION_ID,
2580 CREATED_BY ,
2581 CREATION_DATE ,
2582 LAST_UPDATE_DATE ,
2583 LAST_UPDATED_BY ,
2584 LAST_UPDATE_LOGIN
2585 )
2586 SELECT
2587 VARIABLE_CODE,
2588 VARIABLE_VALUE_ID,
2589 VARIABLE_VALUE,
2590 p_article_id,
2591 SCN_CODE,
2592 x_article_version_id,
2593 l_User_Id,
2594 sysdate,
2595 sysdate,
2596 l_User_Id,
2597 l_login_Id
2598 FROM OKC_ART_VAR_SECTIONS
2599 WHERE ARTICLE_VERSION_ID = l_earlier_version_id;
2600 --------------------------------------------
2601 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2603 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2604 RAISE FND_API.G_EXC_ERROR ;
2605 END IF;
2606 ---------------------------------------------
2607
2608 --end CLM Changes.
2609
2610 -- Standard check of p_commit
2611 IF FND_API.To_Boolean( p_commit ) THEN
2612 COMMIT WORK;
2613 END IF;
2614 -- Standard call to get message count and if count is 1, get message info.
2615 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2616
2617 IF (l_debug = 'Y') THEN
2618 okc_debug.log('700: Leaving create_article_version', 2);
2619 END IF;
2620
2621 EXCEPTION
2622 WHEN FND_API.G_EXC_ERROR THEN
2623 IF (l_debug = 'Y') THEN
2624 okc_debug.log('800: Leaving create_article_version: OKC_API.G_EXCEPTION_ERROR Exception', 2);
2625 END IF;
2626 ROLLBACK TO g_create_article_version_GRP;
2627 x_return_status := G_RET_STS_ERROR ;
2628 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2629
2630 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2631 IF (l_debug = 'Y') THEN
2632 okc_debug.log('900: Leaving create_article_version: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2633 END IF;
2634 ROLLBACK TO g_create_article_version_GRP;
2635 x_return_status := G_RET_STS_UNEXP_ERROR ;
2636 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2637
2638 WHEN OTHERS THEN
2639 IF (l_debug = 'Y') THEN
2640 okc_debug.log('1000: Leaving create_article_version because of EXCEPTION: '||sqlerrm, 2);
2641 END IF;
2642
2643 ROLLBACK TO g_create_article_version_GRP;
2644 x_return_status := G_RET_STS_UNEXP_ERROR ;
2645 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2646 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2647 END IF;
2648 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2649
2650 END create_article_version;
2651 ---------------------------------------------------------------------------
2652 -- PROCEDURE lock_article_version
2653 ---------------------------------------------------------------------------
2654 PROCEDURE lock_article_version(
2655 p_api_version IN NUMBER,
2656 p_init_msg_list IN VARCHAR2 ,
2657
2658 x_return_status OUT NOCOPY VARCHAR2,
2659 x_msg_count OUT NOCOPY NUMBER,
2660 x_msg_data OUT NOCOPY VARCHAR2,
2661
2662 p_article_version_id IN NUMBER,
2663 p_object_version_number IN NUMBER := NULL
2664 ) IS
2665 l_api_version CONSTANT NUMBER := 1;
2666 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_article_version';
2667 BEGIN
2668
2669 IF (l_debug = 'Y') THEN
2670 okc_debug.log('1100: Entered lock_article_version', 2);
2671 END IF;
2672
2673 -- Standard Start of API savepoint
2674 SAVEPOINT g_lock_article_version_GRP;
2675 -- Standard call to check for call compatibility.
2676 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2678 END IF;
2679 -- Initialize message list if p_init_msg_list is set to TRUE.
2680 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2681 FND_MSG_PUB.initialize;
2682 END IF;
2683 -- Initialize API return status to success
2684 x_return_status := FND_API.G_RET_STS_SUCCESS;
2685
2686 --------------------------------------------
2687 -- Calling Simple API for Locking A Row
2688 --------------------------------------------
2689 OKC_ARTICLE_VERSIONS_PVT.lock_row(
2690 x_return_status => x_return_status,
2691 p_article_version_id => p_article_version_id,
2692 p_object_version_number => p_object_version_number
2693 );
2694 --------------------------------------------
2695 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2697 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2698 RAISE FND_API.G_EXC_ERROR ;
2699 END IF;
2700 --------------------------------------------
2701
2702 -- Standard call to get message count and if count is 1, get message info.
2703 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2704
2705 IF (l_debug = 'Y') THEN
2706 okc_debug.log('1200: Leaving lock_article_version', 2);
2707 END IF;
2708
2709 EXCEPTION
2710 WHEN FND_API.G_EXC_ERROR THEN
2711 IF (l_debug = 'Y') THEN
2712 okc_debug.log('1300: Leaving lock_article_version: OKC_API.G_EXCEPTION_ERROR Exception', 2);
2713 END IF;
2714 ROLLBACK TO g_lock_article_version_GRP;
2715 x_return_status := G_RET_STS_ERROR ;
2716 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2717
2718 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2719 IF (l_debug = 'Y') THEN
2720 okc_debug.log('1400: Leaving lock_article_version: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2721 END IF;
2722 ROLLBACK TO g_lock_article_version_GRP;
2723 x_return_status := G_RET_STS_UNEXP_ERROR ;
2724 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2725
2726 WHEN OTHERS THEN
2727 IF (l_debug = 'Y') THEN
2728 okc_debug.log('1500: Leaving lock_article_version because of EXCEPTION: '||sqlerrm, 2);
2729 END IF;
2730
2731 ROLLBACK TO g_lock_article_version_GRP;
2732 x_return_status := G_RET_STS_UNEXP_ERROR ;
2733 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2734 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2735 END IF;
2736 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
2737
2738 END lock_article_version;
2739
2740 ---------------------------------------------------------------------------
2741 -- PROCEDURE update_article_variables ---- will be called in update article versions
2742 ---------------------------------------------------------------------------
2743
2744 PROCEDURE update_article_variables (
2745 p_article_version_id IN NUMBER,
2746 p_variable_code_tbl IN variable_code_tbl_type,
2747 p_do_dml IN VARCHAR2 := 'Y',
2748 x_variables_to_insert_tbl OUT NOCOPY variable_code_tbl_type,
2749 x_variables_to_delete_tbl OUT NOCOPY variable_code_tbl_type,
2750 x_return_status OUT NOCOPY VARCHAR2) IS
2751
2752 i number := 0;
2753 j number := 0;
2754 l number := 0;
2755 k number := 0;
2756 l_existing_variables_tbl variable_code_tbl_type;
2757 l_variable_found VARCHAR2(1) := 'F';
2758 -- Bug 3917777
2759 l_user_id NUMBER := FND_GLOBAL.USER_ID;
2760 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
2761
2762 CURSOR existing_art_variable_csr (cp_article_version_id IN NUMBER) IS
2763 SELECT VARIABLE_CODE FROM OKC_ARTICLE_VARIABLES
2764 WHERE ARTICLE_VERSION_ID = cp_article_version_id;
2765 BEGIN
2766 IF (l_debug = 'Y') THEN
2767 okc_debug.log('1750: Entered update_article_variables', 2);
2768 END IF;
2769 OPEN existing_art_variable_csr(p_article_version_id);
2770 FETCH existing_art_variable_csr BULK COLLECT INTO l_existing_variables_tbl;
2771 CLOSE existing_art_variable_csr;
2772 IF p_variable_code_tbl.COUNT = 0 AND
2773 l_existing_variables_tbl.COUNT = 0 Then
2774 NULL;
2775 ELSIF p_variable_code_tbl.COUNT > 0 THEN
2776 FOR i IN p_variable_code_tbl.FIRST..p_variable_code_tbl.LAST LOOP
2777 l_variable_found := 'F';
2778 IF l_existing_variables_tbl.COUNT > 0 THEN
2779 FOR j IN l_existing_variables_tbl.FIRST..l_existing_variables_tbl.LAST LOOP
2780 IF p_variable_code_tbl(i) = l_existing_variables_tbl(j) then
2781 l_variable_found := 'T';
2782 l_existing_variables_tbl(j) := NULL;
2783 exit;
2784 END IF;
2785 END LOOP;
2786 END IF;
2787 IF l_variable_found = 'F' Then
2788 x_variables_to_insert_tbl(k) := p_variable_code_tbl(i);
2789 k := k + 1;
2790 END IF;
2791 END LOOP;
2792 END IF;
2793 k := 0;
2794 IF l_existing_variables_tbl.COUNT > 0 THEN
2795 FOR j IN l_existing_variables_tbl.FIRST..l_existing_variables_tbl.LAST LOOP
2796 IF l_existing_variables_tbl(j) IS NOT NULL then
2797 x_variables_to_delete_tbl(k) := l_existing_variables_tbl(j);
2798 k := k + 1;
2799 END IF;
2800 END LOOP;
2801 ELSE
2802 x_variables_to_delete_tbl := l_existing_variables_tbl;
2803 END IF;
2804 IF p_do_dml = 'Y' THEN
2805 IF x_variables_to_insert_tbl.COUNT > 0 Then
2806 FORALL i in x_variables_to_insert_tbl.FIRST .. x_variables_to_insert_tbl.LAST
2807 INSERT INTO OKC_ARTICLE_VARIABLES
2808 (
2809 ARTICLE_VERSION_ID ,
2810 VARIABLE_CODE ,
2811 OBJECT_VERSION_NUMBER ,
2812 CREATED_BY ,
2813 CREATION_DATE ,
2814 LAST_UPDATE_DATE ,
2815 LAST_UPDATED_BY ,
2816 LAST_UPDATE_LOGIN
2817 )
2818 VALUES
2819 (
2820 p_article_version_id,
2821 x_variables_to_insert_tbl(i),
2822 1.0,
2823 l_user_id,
2824 sysdate,
2825 sysdate,
2826 l_user_id,
2827 l_login_id
2828 );
2829 END IF;
2830
2831 IF x_variables_to_delete_tbl.COUNT > 0 Then
2832 FORALL i in x_variables_to_delete_tbl.FIRST .. x_variables_to_delete_tbl.LAST
2833 DELETE FROM OKC_ARTICLE_VARIABLES
2834 WHERE VARIABLE_CODE = x_variables_to_delete_tbl(i)
2835 AND ARTICLE_VERSION_ID = p_article_version_id;
2836 END IF;
2837 END IF;
2838
2839 IF (l_debug = 'Y') THEN
2840 Okc_Debug.Log('1750: Leaving Update Article variables successfully', 2);
2841 END IF;
2842 x_return_status := G_RET_STS_SUCCESS;
2843
2844 EXCEPTION
2845
2846 WHEN OTHERS THEN
2847 IF (l_debug = 'Y') THEN
2848 okc_debug.log('1750: Leaving update_article_variables because of EXCEPTION: '||sqlerrm, 2);
2849 END IF;
2850 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2851 p_msg_name => G_UNEXPECTED_ERROR,
2852 p_token1 => G_SQLCODE_TOKEN,
2853 p_token1_value => sqlcode,
2854 p_token2 => G_SQLERRM_TOKEN,
2855 p_token2_value => sqlerrm);
2856
2857 IF existing_art_variable_csr%ISOPEN THEN
2858 CLOSE existing_art_variable_csr;
2859 END IF;
2860
2861 x_return_status := G_RET_STS_UNEXP_ERROR ;
2862
2863 END;
2864 ---------------------------------------------------------------------------
2865 -- PROCEDURE update_article_version
2866 ---------------------------------------------------------------------------
2867 PROCEDURE update_article_version(
2868 p_api_version IN NUMBER,
2869 p_init_msg_list IN VARCHAR2 ,
2870 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2871 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2872 x_return_status OUT NOCOPY VARCHAR2,
2873 x_msg_count OUT NOCOPY NUMBER,
2874 x_msg_data OUT NOCOPY VARCHAR2,
2875 p_article_intent IN VARCHAR2,
2876 p_article_version_id IN NUMBER,
2877 p_article_id IN NUMBER,
2878 p_article_text IN CLOB,
2879 p_provision_yn IN VARCHAR2,
2880 p_insert_by_reference IN VARCHAR2,
2881 p_lock_text IN VARCHAR2,
2882 p_global_yn IN VARCHAR2,
2883 p_article_language IN VARCHAR2,
2884 p_article_status IN VARCHAR2,
2885 p_sav_release IN VARCHAR2,
2886 p_start_date IN DATE,
2887 p_end_date IN DATE,
2888 p_std_article_version_id IN NUMBER,
2889 p_display_name IN VARCHAR2,
2890 p_translated_yn IN VARCHAR2,
2891 p_article_description IN VARCHAR2,
2892 p_date_approved IN DATE,
2893 p_default_section IN VARCHAR2,
2894 p_reference_source IN VARCHAR2,
2895 p_reference_text IN VARCHAR2,
2896 p_orig_system_reference_code IN VARCHAR2 := NULL,
2897 p_orig_system_reference_id1 IN VARCHAR2 := NULL,
2898 p_orig_system_reference_id2 IN VARCHAR2 := NULL,
2899 p_additional_instructions IN VARCHAR2 := NULL,
2900 p_variation_description IN VARCHAR2,
2901 p_date_published IN DATE,
2902 p_attribute_category IN VARCHAR2 := NULL,
2903 p_attribute1 IN VARCHAR2 := NULL,
2904 p_attribute2 IN VARCHAR2 := NULL,
2905 p_attribute3 IN VARCHAR2 := NULL,
2906 p_attribute4 IN VARCHAR2 := NULL,
2907 p_attribute5 IN VARCHAR2 := NULL,
2908 p_attribute6 IN VARCHAR2 := NULL,
2909 p_attribute7 IN VARCHAR2 := NULL,
2910 p_attribute8 IN VARCHAR2 := NULL,
2911 p_attribute9 IN VARCHAR2 := NULL,
2912 p_attribute10 IN VARCHAR2 := NULL,
2913 p_attribute11 IN VARCHAR2 := NULL,
2914 p_attribute12 IN VARCHAR2 := NULL,
2915 p_attribute13 IN VARCHAR2 := NULL,
2916 p_attribute14 IN VARCHAR2 := NULL,
2917 p_attribute15 IN VARCHAR2 := NULL,
2918 p_object_version_number IN NUMBER := NULL,
2919 --Clause Editing
2920 p_edited_in_word IN VARCHAR2 DEFAULT 'N',
2921 p_article_text_in_word IN BLOB DEFAULT NULL,
2922 --clm
2923 p_variable_code IN VARCHAR2 DEFAULT NULL
2924 ) IS
2925
2926 l_api_version CONSTANT NUMBER := 1;
2927 l_api_name CONSTANT VARCHAR2(30) := 'g_update_article_version';
2928 l_earlier_version_id NUMBER;
2929 l_article_id NUMBER;
2930 l_article_text CLOB;
2931 l_article_status OKC_ARTICLE_VERSIONS.ARTICLE_STATUS%TYPE;
2932 l_variable_code_tbl variable_code_tbl_type;
2933 l_variables_to_insert_tbl variable_code_tbl_type;
2934 l_variables_to_delete_tbl variable_code_tbl_type;
2935 l_clob CLOB;
2936 -- The following procedure is private to this API and will be used to update the variables for the articles
2937 -- It will perform the following:
2938 -- 1. Delete the existing variables no longer used
2939 -- 2. Create any new variables that do not exist in OKC_ARTICLE_VARIABLES
2940 -- BULK approach was considered for better performance with manipulation using PL/SQL arrays
2941 -- Other approach could be use of temp tables
2942 -- Easiest approach would have been is to bulk delete and bulk insert all article variables but maynot perform
2943 -- as efficiently
2944
2945
2946 BEGIN
2947
2948 IF (l_debug = 'Y') THEN
2949 okc_debug.log('1600: Entered update_article_version', 2);
2950 okc_debug.log('1700: Locking row', 2);
2951 END IF;
2952
2953 -- Standard Start of API savepoint
2954 SAVEPOINT g_update_article_version_GRP;
2955 -- Standard call to check for call compatibility.
2956 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2958 END IF;
2959 -- Initialize message list if p_init_msg_list is set to TRUE.
2960 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2961 FND_MSG_PUB.initialize;
2962 END IF;
2963 -- Initialize API return status to success
2964 x_return_status := FND_API.G_RET_STS_SUCCESS;
2965
2966 -- Parse through the Article text, replace and extract the variables
2967 -- Parsing is an expensive process and should be done only if the text has changed and if the article is in Draft or
2968 -- Rejected Status
2969 -- Expecting UIs to detect a change in text and setting p_article_text only if it has changed. Else pass this as NULL
2970 -- Parsing is DONE only if the status is = DRAFT, REJECTED as article text
2971 -- cannot be updated in any other status
2972
2973 l_article_status := p_article_status;
2974 l_article_text := p_article_text;
2975
2976 -- nvl added to cater to non-standard articles. Std Articles will never be of NULL status
2977
2978 IF p_article_text is NOT NULL AND
2979 nvl(l_article_status,'DRAFT') IN ('DRAFT','REJECTED') THEN
2980 DBMS_LOB.CREATETEMPORARY(l_clob,true);
2981 parse_n_replace_text(
2982 p_api_version => p_api_version,
2983 p_init_msg_list => p_init_msg_list,
2984 p_article_text => l_article_text,
2985 p_dest_clob => l_clob,
2986 p_calling_mode => 'CALLED_FROM_CREATE_UPDATE',
2987 p_replace_text => 'Y',
2988 p_article_intent => p_article_intent,
2989 p_language => USERENV('LANG'),
2990 x_return_status => x_return_status,
2991 x_msg_count => x_msg_count,
2992 x_msg_data => x_msg_data,
2993 x_variables_tbl => l_variable_code_tbl
2994 ) ;
2995 --dbms_lob.freetemporary(l_clob);
2996 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2998 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2999 RAISE FND_API.G_EXC_ERROR ;
3000 END IF;
3001 END IF;
3002 -- MOAC
3003 G_CURRENT_ORG_ID := mo_global.get_current_org_id() ;
3004 /*
3005 OPEN cur_org_csr;
3006 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
3007 CLOSE cur_org_csr;
3008 */
3009 --------------------------------------------
3010 -- Calling Simple API for Updating A Row
3011 --------------------------------------------
3012 -- UIs may be returning EXPIRED status (derived) from VOs.
3013 IF p_article_status = 'EXPIRED' THEN
3014 l_article_status := 'APPROVED';
3015 ELSE
3016 l_article_status := p_article_status;
3017 END IF;
3018
3019 -- MOAC
3020 IF G_CURRENT_ORG_ID IS NULL Then
3021 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
3022 RAISE FND_API.G_EXC_ERROR ;
3023 END IF;
3024
3025 OKC_ARTICLE_VERSIONS_PVT.Update_Row(
3026 p_validation_level => p_validation_level,
3027 x_return_status => x_return_status,
3028 p_article_version_id => p_article_version_id,
3029 p_article_id => p_article_id,
3030 p_article_version_number => NULL,
3031 p_article_text => l_clob,
3032 p_provision_yn => p_provision_yn,
3033 p_insert_by_reference => p_insert_by_reference,
3034 p_lock_text => p_lock_text,
3035 p_global_yn => p_global_yn,
3036 p_article_language => p_article_language,
3037 p_article_status => l_article_status,
3038 p_sav_release => p_sav_release,
3039 p_start_date => p_start_date,
3040 p_end_date => p_end_date,
3041 p_std_article_version_id => p_std_article_version_id,
3042 p_display_name => p_display_name,
3043 p_translated_yn => p_translated_yn,
3044 p_article_description => p_article_description,
3045 p_date_approved => p_date_approved,
3046 p_default_section => p_default_section,
3047 p_reference_source => p_reference_source,
3048 p_reference_text => p_reference_text,
3049 p_orig_system_reference_code => p_orig_system_reference_code,
3050 p_orig_system_reference_id1 => p_orig_system_reference_id1,
3051 p_orig_system_reference_id2 => p_orig_system_reference_id2,
3052 p_additional_instructions => p_additional_instructions,
3053 p_variation_description => p_variation_description,
3054 p_date_published => p_date_published,
3055 p_current_org_id => G_CURRENT_ORG_ID,
3056 p_attribute_category => p_attribute_category,
3057 p_attribute1 => p_attribute1,
3058 p_attribute2 => p_attribute2,
3059 p_attribute3 => p_attribute3,
3060 p_attribute4 => p_attribute4,
3061 p_attribute5 => p_attribute5,
3062 p_attribute6 => p_attribute6,
3063 p_attribute7 => p_attribute7,
3064 p_attribute8 => p_attribute8,
3065 p_attribute9 => p_attribute9,
3066 p_attribute10 => p_attribute10,
3067 p_attribute11 => p_attribute11,
3068 p_attribute12 => p_attribute12,
3069 p_attribute13 => p_attribute13,
3070 p_attribute14 => p_attribute14,
3071 p_attribute15 => p_attribute15,
3072 p_object_version_number => p_object_version_number,
3073 --Clause Editing
3074 p_edited_in_word => p_edited_in_word,
3075 p_article_text_in_word => p_article_text_in_word,
3076 --clm
3077 p_variable_code => p_variable_code,
3078 x_article_status => l_article_status,
3079 x_article_id => l_article_id,
3080 x_earlier_version_id => l_earlier_version_id
3081 );
3082 --------------------------------------------
3083 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3084 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3085 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3086 RAISE FND_API.G_EXC_ERROR ;
3087 END IF;
3088 --------------------------------------------
3089 IF p_article_text is NOT NULL AND
3090 nvl(l_article_status,'DRAFT') IN ('DRAFT','REJECTED') THEN
3091 update_article_variables (p_article_version_id => p_article_version_id,
3092 p_variable_code_tbl => l_variable_code_tbl,
3093 p_do_dml => 'Y',
3094 x_variables_to_insert_tbl => l_variables_to_insert_tbl,
3095 x_variables_to_delete_tbl => l_variables_to_delete_tbl,
3096 x_return_status => x_return_status);
3097 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3098 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3099 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3100 RAISE FND_API.G_EXC_ERROR ;
3101 END IF;
3102 END IF;
3103 IF l_article_status = 'APPROVED' THEN
3104 UPDATE OKC_ARTICLES_ALL
3105 SET cz_transfer_status_flag = 'R'
3106 WHERE ARTICLE_ID = l_article_id;
3107 END IF;
3108
3109 -- Standard check of p_commit
3110 IF FND_API.To_Boolean( p_commit ) THEN
3111 COMMIT WORK;
3112 END IF;
3113 -- Standard call to get message count and if count is 1, get message info.
3114 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
3115
3116 IF (l_debug = 'Y') THEN
3117 okc_debug.log('1800: Leaving update_article_version', 2);
3118 END IF;
3119
3120 EXCEPTION
3121 WHEN FND_API.G_EXC_ERROR THEN
3122 IF (l_debug = 'Y') THEN
3123 okc_debug.log('1900: Leaving update_article_version: OKC_API.G_EXCEPTION_ERROR Exception', 2);
3124 END IF;
3125 ROLLBACK TO g_update_article_version_GRP;
3126 x_return_status := G_RET_STS_ERROR ;
3127 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
3128
3129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3130 IF (l_debug = 'Y') THEN
3131 okc_debug.log('2000: Leaving update_article_version: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3132 END IF;
3133 ROLLBACK TO g_update_article_version_GRP;
3134 x_return_status := G_RET_STS_UNEXP_ERROR ;
3135 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
3136
3137 WHEN OTHERS THEN
3138 IF (l_debug = 'Y') THEN
3139 okc_debug.log('2100: Leaving update_article_version because of EXCEPTION: '||sqlerrm, 2);
3140 END IF;
3141
3142 ROLLBACK TO g_update_article_version_GRP;
3143 x_return_status := G_RET_STS_UNEXP_ERROR ;
3144 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3145 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3146 END IF;
3147 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
3148
3149 END update_article_version;
3150
3151
3152 ---------------------------------------------------------------------------
3153 -- PROCEDURE copy_article
3154 ---------------------------------------------------------------------------
3155 PROCEDURE copy_article(
3156 p_api_version IN NUMBER,
3157 p_init_msg_list IN VARCHAR2 ,
3158 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3159 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3160
3161 p_article_version_id IN NUMBER,
3162 p_new_article_title IN VARCHAR2 := NULL,
3163 p_new_article_number IN VARCHAR2 := NULL,
3164 p_create_standard_yn IN VARCHAR2 := 'N',
3165 p_copy_relationship_yn IN VARCHAR2 := 'N',
3166 p_copy_folder_assoc_yn IN VARCHAR2 := 'N',
3167
3168 x_article_version_id OUT NOCOPY NUMBER,
3169 x_article_id OUT NOCOPY NUMBER,
3170 x_article_number OUT NOCOPY VARCHAR2,
3171 x_return_status OUT NOCOPY VARCHAR2,
3172 x_msg_count OUT NOCOPY NUMBER,
3173 x_msg_data OUT NOCOPY VARCHAR2
3174 ) IS
3175 -- Article Attributes
3176 l_article_id OKC_ARTICLES_ALL.ARTICLE_ID%TYPE ;
3177 l_org_id OKC_ARTICLES_ALL.ORG_ID%TYPE ;
3178 l_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE ;
3179 l_old_article_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE ;
3180 l_article_title OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE ;
3181 l_object_version_number OKC_ARTICLES_ALL.OBJECT_VERSION_NUMBER%TYPE ;
3182 l_standard_yn OKC_ARTICLES_ALL.STANDARD_YN%TYPE;
3183 l_article_intent OKC_ARTICLES_ALL.ARTICLE_INTENT%TYPE;
3184 l_article_language OKC_ARTICLES_ALL.ARTICLE_LANGUAGE%TYPE;
3185 l_article_type OKC_ARTICLES_ALL.ARTICLE_TYPE%TYPE;
3186 l_doc_sequence_type VARCHAR2(1);
3187 l_orig_system_reference_code OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
3188 l_orig_system_reference_id1 OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
3189 l_orig_system_reference_id2 OKC_ARTICLES_ALL.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
3190 l_cz_transfer_status_flag OKC_ARTICLES_ALL.CZ_TRANSFER_STATUS_FLAG%TYPE;
3191 l_program_id OKC_ARTICLES_ALL.PROGRAM_ID%TYPE ;
3192 l_program_login_id OKC_ARTICLES_ALL.PROGRAM_LOGIN_ID%TYPE ;
3193 l_program_application_id OKC_ARTICLES_ALL.PROGRAM_APPLICATION_ID%TYPE ;
3194 l_request_id OKC_ARTICLES_ALL.REQUEST_ID%TYPE ;
3195 l_attribute_category OKC_ARTICLES_ALL.ATTRIBUTE_CATEGORY%TYPE;
3196 l_attribute1 OKC_ARTICLES_ALL.ATTRIBUTE1%TYPE;
3197 l_attribute2 OKC_ARTICLES_ALL.ATTRIBUTE2%TYPE;
3198 l_attribute3 OKC_ARTICLES_ALL.ATTRIBUTE3%TYPE;
3199 l_attribute4 OKC_ARTICLES_ALL.ATTRIBUTE4%TYPE;
3200 l_attribute5 OKC_ARTICLES_ALL.ATTRIBUTE5%TYPE;
3201 l_attribute6 OKC_ARTICLES_ALL.ATTRIBUTE6%TYPE;
3202 l_attribute7 OKC_ARTICLES_ALL.ATTRIBUTE7%TYPE;
3203 l_attribute8 OKC_ARTICLES_ALL.ATTRIBUTE8%TYPE;
3204 l_attribute9 OKC_ARTICLES_ALL.ATTRIBUTE9%TYPE;
3205 l_attribute10 OKC_ARTICLES_ALL.ATTRIBUTE10%TYPE;
3206 l_attribute11 OKC_ARTICLES_ALL.ATTRIBUTE11%TYPE;
3207 l_attribute12 OKC_ARTICLES_ALL.ATTRIBUTE12%TYPE;
3208 l_attribute13 OKC_ARTICLES_ALL.ATTRIBUTE13%TYPE;
3209 l_attribute14 OKC_ARTICLES_ALL.ATTRIBUTE14%TYPE;
3210 l_attribute15 OKC_ARTICLES_ALL.ATTRIBUTE15%TYPE;
3211 l_created_by OKC_ARTICLES_ALL.CREATED_BY%TYPE;
3212 l_creation_date OKC_ARTICLES_ALL.CREATION_DATE%TYPE;
3213 l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
3214 l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
3215 l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
3216
3217 -- Article Version Attributes
3218 lv_article_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE ;
3219 lv_article_version_number OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_NUMBER%TYPE ;
3220 lv_object_version_number OKC_ARTICLE_VERSIONS.OBJECT_VERSION_NUMBER%TYPE ;
3221 lv_article_id OKC_ARTICLE_VERSIONS.ARTICLE_ID%TYPE ;
3222 lv_article_text OKC_ARTICLE_VERSIONS.ARTICLE_TEXT%TYPE;
3223 lv_provision_yn OKC_ARTICLE_VERSIONS.PROVISION_YN%TYPE;
3224 lv_insert_by_reference OKC_ARTICLE_VERSIONS.INSERT_BY_REFERENCE%TYPE;
3225 lv_lock_text OKC_ARTICLE_VERSIONS.LOCK_TEXT%TYPE;
3226 lv_global_yn OKC_ARTICLE_VERSIONS.GLOBAL_YN%TYPE;
3227 lv_article_language OKC_ARTICLE_VERSIONS.ARTICLE_LANGUAGE%TYPE ;
3228 lv_article_status OKC_ARTICLE_VERSIONS.ARTICLE_STATUS%TYPE;
3229 lv_sav_release OKC_ARTICLE_VERSIONS.SAV_RELEASE%TYPE;
3230 lv_start_date OKC_ARTICLE_VERSIONS.START_DATE%TYPE ;
3231 lv_end_date OKC_ARTICLE_VERSIONS.END_DATE%TYPE;
3232 lv_std_article_version_id OKC_ARTICLE_VERSIONS.STD_ARTICLE_VERSION_ID%TYPE ;
3233 lv_display_name OKC_ARTICLE_VERSIONS.DISPLAY_NAME%TYPE;
3234 lv_translated_yn OKC_ARTICLE_VERSIONS.TRANSLATED_YN%TYPE;
3235 lv_article_description OKC_ARTICLE_VERSIONS.ARTICLE_DESCRIPTION%TYPE;
3236 lv_date_approved OKC_ARTICLE_VERSIONS.DATE_APPROVED%TYPE;
3237 lv_default_section OKC_ARTICLE_VERSIONS.DEFAULT_SECTION%TYPE;
3238 lv_reference_source OKC_ARTICLE_VERSIONS.REFERENCE_SOURCE%TYPE;
3239 lv_reference_text OKC_ARTICLE_VERSIONS.REFERENCE_TEXT%TYPE;
3240 lv_additional_instructions OKC_ARTICLE_VERSIONS.ADDITIONAL_INSTRUCTIONS%TYPE;
3241 lv_variation_description OKC_ARTICLE_VERSIONS.VARIATION_DESCRIPTION%TYPE;
3242 lv_date_published OKC_ARTICLE_VERSIONS.DATE_PUBLISHED%TYPE;
3243 lv_orig_system_reference_code OKC_ARTICLE_VERSIONS.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
3244 lv_orig_system_reference_id1 OKC_ARTICLE_VERSIONS.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
3245 lv_orig_system_reference_id2 OKC_ARTICLE_VERSIONS.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
3246 lv_program_id OKC_ARTICLE_VERSIONS.PROGRAM_ID%TYPE ;
3247 lv_program_login_id OKC_ARTICLE_VERSIONS.PROGRAM_LOGIN_ID%TYPE ;
3248 lv_program_application_id OKC_ARTICLE_VERSIONS.PROGRAM_APPLICATION_ID%TYPE ;
3249 lv_request_id OKC_ARTICLE_VERSIONS.REQUEST_ID%TYPE ;
3250 lv_attribute_category OKC_ARTICLE_VERSIONS.ATTRIBUTE_CATEGORY%TYPE;
3251 lv_attribute1 OKC_ARTICLE_VERSIONS.ATTRIBUTE1%TYPE;
3252 lv_attribute2 OKC_ARTICLE_VERSIONS.ATTRIBUTE2%TYPE;
3253 lv_attribute3 OKC_ARTICLE_VERSIONS.ATTRIBUTE3%TYPE;
3254 lv_attribute4 OKC_ARTICLE_VERSIONS.ATTRIBUTE4%TYPE;
3255 lv_attribute5 OKC_ARTICLE_VERSIONS.ATTRIBUTE5%TYPE;
3256 lv_attribute6 OKC_ARTICLE_VERSIONS.ATTRIBUTE6%TYPE;
3257 lv_attribute7 OKC_ARTICLE_VERSIONS.ATTRIBUTE7%TYPE;
3258 lv_attribute8 OKC_ARTICLE_VERSIONS.ATTRIBUTE8%TYPE;
3259 lv_attribute9 OKC_ARTICLE_VERSIONS.ATTRIBUTE9%TYPE;
3260 lv_attribute10 OKC_ARTICLE_VERSIONS.ATTRIBUTE10%TYPE;
3261 lv_attribute11 OKC_ARTICLE_VERSIONS.ATTRIBUTE11%TYPE;
3262 lv_attribute12 OKC_ARTICLE_VERSIONS.ATTRIBUTE12%TYPE;
3263 lv_attribute13 OKC_ARTICLE_VERSIONS.ATTRIBUTE13%TYPE;
3264 lv_attribute14 OKC_ARTICLE_VERSIONS.ATTRIBUTE14%TYPE;
3265 lv_attribute15 OKC_ARTICLE_VERSIONS.ATTRIBUTE15%TYPE;
3266 lv_created_by OKC_ARTICLE_VERSIONS.CREATED_BY%TYPE;
3267 lv_creation_date OKC_ARTICLE_VERSIONS.CREATION_DATE%TYPE;
3268 lv_last_update_date OKC_ARTICLE_VERSIONS.LAST_UPDATE_DATE%TYPE;
3269 lv_last_updated_by OKC_ARTICLE_VERSIONS.LAST_UPDATED_BY%TYPE;
3270 lv_last_update_login OKC_ARTICLE_VERSIONS.LAST_UPDATE_LOGIN%TYPE;
3271 --Clause Editing
3272 lv_edited_in_word OKC_ARTICLE_VERSIONS.EDITED_IN_WORD%TYPE;
3273 lv_article_text_in_word OKC_ARTICLE_VERSIONS.ARTICLE_TEXT_IN_WORD%TYPE;
3274 --CLM
3275 lv_variable_code OKC_ARTICLE_VERSIONS.VARIABLE_CODE%TYPE;
3276
3277 l_p_standard_yn VARCHAR2(1);
3278 l_p_article_id NUMBER;
3279 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
3280 l_api_version CONSTANT NUMBER := 1;
3281 l_api_name CONSTANT VARCHAR2(30) := 'g_copy_article';
3282 l_p_org_id NUMBER;
3283 l_earlier_version_id NUMBER;
3284 l_earlier_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;
3285 -- TYPE l_source_article_id_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.SOURCE_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
3286 TYPE l_target_article_id_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.TARGET_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
3287 TYPE l_relationship_type_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.RELATIONSHIP_TYPE%TYPE INDEX BY BINARY_INTEGER ;
3288
3289 -- l_source_article_id_tbl l_source_article_id_list ;
3290 l_target_article_id_tbl l_target_article_id_list ;
3291 l_relationship_type_tbl l_relationship_type_list ;
3292 i NUMBER := 0;
3293
3294 CURSOR l_article_csr (cp_article_version_id IN NUMBER) is
3295 SELECT aa.standard_yn,av.article_id
3296 FROM OKC_ARTICLES_ALL aa,OKC_ARTICLE_VERSIONS av
3297 WHERE aa.ARTICLE_ID = av.ARTICLE_ID
3298 AND av.ARTICLE_VERSION_ID = cp_article_version_id;
3299
3300 CURSOR l_relationship_csr (cp_article_id IN NUMBER,
3301 cp_org_id IN NUMBER) IS
3302 SELECT
3303 TARGET_ARTICLE_ID,
3304 RELATIONSHIP_TYPE
3305 FROM OKC_ARTICLE_RELATNS_ALL
3306 WHERE source_article_id = cp_article_id
3307 AND org_id = cp_org_id;
3308
3309 l_user_id NUMBER := FND_GLOBAL.USER_ID;
3310 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
3311 BEGIN
3312 IF (l_debug = 'Y') THEN
3313 Okc_Debug.Log('2700: Entered Copy_Articles ', 2);
3314 END IF;
3315 --dbms_output.put_line('2700: Entered Copy_Articles ');
3316
3317 -- Standard Start of API savepoint
3318 SAVEPOINT g_copy_article_GRP;
3319 -- Standard call to check for call compatibility.
3320 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3322 END IF;
3323 -- MOAC
3324 G_CURRENT_ORG_ID := mo_global.get_current_org_id() ;
3325 /*
3326 OPEN cur_org_csr;
3327 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
3328 CLOSE cur_org_csr;
3329 */
3330 -- Initialize message list if p_init_msg_list is set to TRUE.
3331 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3332 FND_MSG_PUB.initialize;
3333 END IF;
3334 -- Initialize API return status to success
3335 x_return_status := FND_API.G_RET_STS_SUCCESS;
3336 --dbms_output.put_line('2700: Before Cursor Entered Copy_Articles ');
3337
3338 -- Insert into ... select would have been faster but we need to validate as well
3339
3340
3341 OPEN l_article_csr(p_article_version_id);
3342 FETCH l_article_csr INTO l_p_standard_yn, l_p_article_id;
3343 CLOSE l_article_csr;
3344
3345 IF l_p_article_id IS NOT NULL THEN
3346 -- Get Current Database values for Article
3347
3348 --dbms_output.put_line('2700: Before Get_Rec1 ');
3349 l_return_status := OKC_ARTICLES_ALL_PVT.Get_Rec(
3350 p_article_id => l_p_article_id,
3351 x_article_title => l_article_title,
3352 x_org_id => l_org_id,
3353 x_article_number => l_old_article_number,
3354 x_standard_yn => l_standard_yn,
3355 x_article_intent => l_article_intent,
3356 x_article_language => l_article_language,
3357 x_article_type => l_article_type,
3358 x_orig_system_reference_code => l_orig_system_reference_code,
3359 x_orig_system_reference_id1 => l_orig_system_reference_id1,
3360 x_orig_system_reference_id2 => l_orig_system_reference_id2,
3361 x_cz_transfer_status_flag => l_cz_transfer_status_flag,
3362 x_program_id => l_program_id,
3363 x_program_login_id => l_program_login_id,
3364 x_program_application_id => l_program_application_id,
3365 x_request_id => l_request_id,
3366 x_attribute_category => l_attribute_category,
3367 x_attribute1 => l_attribute1,
3368 x_attribute2 => l_attribute2,
3369 x_attribute3 => l_attribute3,
3370 x_attribute4 => l_attribute4,
3371 x_attribute5 => l_attribute5,
3372 x_attribute6 => l_attribute6,
3373 x_attribute7 => l_attribute7,
3374 x_attribute8 => l_attribute8,
3375 x_attribute9 => l_attribute9,
3376 x_attribute10 => l_attribute10,
3377 x_attribute11 => l_attribute11,
3378 x_attribute12 => l_attribute12,
3379 x_attribute13 => l_attribute13,
3380 x_attribute14 => l_attribute14,
3381 x_attribute15 => l_attribute15,
3382 x_object_version_number => l_object_version_number,
3383 x_created_by => l_created_by,
3384 x_creation_date => l_creation_date,
3385 x_last_updated_by => l_last_updated_by,
3386 x_last_update_login => l_last_update_login,
3387 x_last_update_date => l_last_update_date
3388 );
3389 --------------------------------------------
3390 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3392 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3393 RAISE FND_API.G_EXC_ERROR ;
3394 END IF;
3395 --------------------------------------------
3396 END IF;
3397
3398 IF( p_article_version_id IS NOT NULL ) THEN
3399 -- Get current database values for article version
3400 --dbms_output.put_line('l_return_status is2 '||l_return_status);
3401 l_return_status := OKC_ARTICLE_VERSIONS_PVT.Get_Rec(
3402 p_article_version_id => p_article_version_id,
3403 x_article_id => lv_article_id,
3404 x_article_version_number => lv_article_version_number,
3405 x_article_text => lv_article_text,
3406 x_provision_yn => lv_provision_yn,
3407 x_insert_by_reference => lv_insert_by_reference,
3408 x_lock_text => lv_lock_text,
3409 x_global_yn => lv_global_yn,
3410 x_article_language => lv_article_language,
3411 x_article_status => lv_article_status,
3412 x_sav_release => lv_sav_release,
3413 x_start_date => lv_start_date,
3414 x_end_date => lv_end_date,
3415 x_std_article_version_id => lv_std_article_version_id,
3416 x_display_name => lv_display_name,
3417 x_translated_yn => lv_translated_yn,
3418 x_article_description => lv_article_description,
3419 x_date_approved => lv_date_approved,
3420 x_default_section => lv_default_section,
3421 x_reference_source => lv_reference_source,
3422 x_reference_text => lv_reference_text,
3423 x_orig_system_reference_code => lv_orig_system_reference_code,
3424 x_orig_system_reference_id1 => lv_orig_system_reference_id1,
3425 x_orig_system_reference_id2 => lv_orig_system_reference_id2,
3426 x_additional_instructions => lv_additional_instructions,
3427 x_variation_description => lv_variation_description,
3428 x_date_published => lv_date_published,
3429 x_program_id => lv_program_id,
3430 x_program_login_id => lv_program_login_id,
3431 x_program_application_id => lv_program_application_id,
3432 x_request_id => lv_request_id,
3433 x_attribute_category => lv_attribute_category,
3434 x_attribute1 => lv_attribute1,
3435 x_attribute2 => lv_attribute2,
3436 x_attribute3 => lv_attribute3,
3437 x_attribute4 => lv_attribute4,
3438 x_attribute5 => lv_attribute5,
3439 x_attribute6 => lv_attribute6,
3440 x_attribute7 => lv_attribute7,
3441 x_attribute8 => lv_attribute8,
3442 x_attribute9 => lv_attribute9,
3443 x_attribute10 => lv_attribute10,
3444 x_attribute11 => lv_attribute11,
3445 x_attribute12 => lv_attribute12,
3446 x_attribute13 => lv_attribute13,
3447 x_attribute14 => lv_attribute14,
3448 x_attribute15 => lv_attribute15,
3449 x_object_version_number => lv_object_version_number,
3450 --Clause Editing
3451 x_edited_in_word => lv_edited_in_word,
3452 x_article_text_in_word => lv_article_text_in_word,
3453 x_created_by => lv_created_by,
3454 x_creation_date => lv_creation_date,
3455 x_last_updated_by => lv_last_updated_by,
3456 x_last_update_login => lv_last_update_login,
3457 x_last_update_date => lv_last_update_date,
3458 x_variable_code => lv_variable_code --clm
3459 );
3460 --------------------------------------------
3461 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3463 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3464 RAISE FND_API.G_EXC_ERROR ;
3465 END IF;
3466 --------------------------------------------
3467 END IF;
3468 -- Copying Articles
3469 -- Setting the Attributes depending upon whether it is standard or non-standard article
3470 IF l_p_standard_yn = 'Y' THEN
3471
3472 -- Bug#3680325 i.e. start date is greatest of sysdate, start date and end date is always null
3473 -- Bug#3826123 i.e. start date should be truncated
3474
3475 lv_start_date := trunc(greatest(SYSDATE, lv_start_date));
3476 lv_end_date := NULL;
3477
3478 -- end bug fix#3680325
3479 lv_article_status := 'DRAFT';
3480 l_cz_transfer_status_flag := 'N';
3481 G_profile_doc_seq:=fnd_profile.value('UNIQUE:SEQ_NUMBERS');
3482 G_doc_category_code := substr(Fnd_Profile.Value('OKC_ARTICLE_DOC_SEQ_CATEGORY'),1,30) ;
3483
3484 -- MOAC
3485 IF G_CURRENT_ORG_ID IS NULL Then
3486 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ART_NULL_ORG_ID');
3487 RAISE FND_API.G_EXC_ERROR ;
3488 END IF;
3489
3490 GET_ARTICLE_SEQ_NUMBER
3491 (p_article_number => p_new_article_number,
3492 p_seq_type_info_only => 'N',
3493 p_org_id => G_CURRENT_ORG_ID,
3494 x_article_number => l_article_number,
3495 x_doc_sequence_type => l_doc_sequence_type,
3496 x_return_status => x_return_status);
3497
3498 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3500 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3501 RAISE FND_API.G_EXC_ERROR ;
3502 END IF;
3503 IF l_article_number IS NULL THEN
3504 l_article_number := nvl(p_new_article_number, l_old_article_number);
3505 END IF;
3506 IF G_CURRENT_ORG_ID <> G_GLOBAL_ORG_ID THEN
3507 lv_global_yn := 'N';
3508 END IF;
3509 ELSE --IF l_p_standard_yn = 'Y' THEN
3510 lv_start_date := NULL;
3511 lv_article_status := NULL;
3512 l_cz_transfer_status_flag := NULL;
3513 lv_end_date := NULL;
3514 -- Bug 5506276 - Added below logic to retain the article number for non-std articles
3515 l_article_number := l_old_article_number;
3516 END IF;
3517
3518 lv_date_approved := NULL;
3519 l_orig_system_reference_code := 'OKCART';
3520 l_orig_system_reference_id1 := to_char(l_p_article_id);
3521 lv_orig_system_reference_code := 'OKCARTV';
3522 lv_orig_system_reference_id1 := to_char(p_article_version_id);
3523
3524 --dbms_output.put_line('Before Create Article ');
3525 OKC_ARTICLES_ALL_PVT.Insert_Row(
3526 p_validation_level => p_validation_level,
3527 x_return_status => x_return_status,
3528 p_article_title => nvl(p_new_article_title,l_article_title),
3529 p_org_id => G_CURRENT_ORG_ID,
3530 p_article_number => l_article_number,
3531 p_standard_yn => p_create_standard_yn,
3532 p_article_intent => l_article_intent,
3533 p_article_language => l_article_language,
3534 p_article_type => l_article_type,
3535 p_orig_system_reference_code => l_orig_system_reference_code,
3536 p_orig_system_reference_id1 => l_orig_system_reference_id1,
3537 p_orig_system_reference_id2 => l_orig_system_reference_id2,
3538 p_cz_transfer_status_flag => l_cz_transfer_status_flag,
3539 p_attribute_category => l_attribute_category,
3540 p_attribute1 => l_attribute1,
3541 p_attribute2 => l_attribute2,
3542 p_attribute3 => l_attribute3,
3543 p_attribute4 => l_attribute4,
3544 p_attribute5 => l_attribute5,
3545 p_attribute6 => l_attribute6,
3546 p_attribute7 => l_attribute7,
3547 p_attribute8 => l_attribute8,
3548 p_attribute9 => l_attribute9,
3549 p_attribute10 => l_attribute10,
3550 p_attribute11 => l_attribute11,
3551 p_attribute12 => l_attribute12,
3552 p_attribute13 => l_attribute13,
3553 p_attribute14 => l_attribute14,
3554 p_attribute15 => l_attribute15,
3555 x_article_number => x_article_number,
3556 x_article_id => x_article_id
3557 );
3558 --dbms_output.put_line('After Create Article status is '||x_return_status);
3559 --dbms_output.put_line('x_article_id is '||x_article_id);
3560 --dbms_output.put_line('x_article_version_id is '||x_article_version_id);
3561 --------------------------------------------
3562 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3564 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3565 RAISE FND_API.G_EXC_ERROR ;
3566 END IF;
3567 --------------------------------------------
3568 OKC_ARTICLE_VERSIONS_PVT.Insert_Row(
3569 p_validation_level => p_validation_level,
3570 x_return_status => x_return_status,
3571 p_article_id => x_article_id,
3572 p_article_text => lv_article_text,
3573 p_provision_yn => lv_provision_yn,
3574 p_insert_by_reference => lv_insert_by_reference,
3575 p_lock_text => lv_lock_text,
3576 p_global_yn => lv_global_yn,
3577 p_article_language => lv_article_language,
3578 p_article_status => lv_article_status,
3579 p_sav_release => lv_sav_release,
3580 p_start_date => lv_start_date,
3581 p_end_date => lv_end_date,
3582 p_std_article_version_id => lv_std_article_version_id,
3583 p_display_name => lv_display_name,
3584 p_translated_yn => lv_translated_yn,
3585 p_article_description => lv_article_description,
3586 p_date_approved => NULL,
3587 p_default_section => lv_default_section,
3588 p_reference_source => lv_reference_source,
3589 p_reference_text => lv_reference_text,
3590 p_orig_system_reference_code => lv_orig_system_reference_code,
3591 p_orig_system_reference_id1 => lv_orig_system_reference_id1,
3592 p_orig_system_reference_id2 => lv_orig_system_reference_id2,
3593 p_additional_instructions => lv_additional_instructions,
3594 p_variation_description => lv_variation_description,
3595 p_date_published => NULL,
3596 p_current_org_id => G_CURRENT_ORG_ID,
3597 p_attribute_category => lv_attribute_category,
3598 p_attribute1 => lv_attribute1,
3599 p_attribute2 => lv_attribute2,
3600 p_attribute3 => lv_attribute3,
3601 p_attribute4 => lv_attribute4,
3602 p_attribute5 => lv_attribute5,
3603 p_attribute6 => lv_attribute6,
3604 p_attribute7 => lv_attribute7,
3605 p_attribute8 => lv_attribute8,
3606 p_attribute9 => lv_attribute9,
3607 p_attribute10 => lv_attribute10,
3608 p_attribute11 => lv_attribute11,
3609 p_attribute12 => lv_attribute12,
3610 p_attribute13 => lv_attribute13,
3611 p_attribute14 => lv_attribute14,
3612 p_attribute15 => lv_attribute15,
3613 --Clause Editing
3614 p_edited_in_word => lv_edited_in_word,
3615 p_article_text_in_word => lv_article_text_in_word,
3616 --clm
3617 p_variable_code => lv_variable_code,
3618 x_earlier_adoption_type => l_earlier_adoption_type,
3619 x_earlier_version_id => l_earlier_version_id,
3620 x_article_version_id => x_article_version_id
3621 );
3622 --------------------------------------------
3623 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3625 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3626 RAISE FND_API.G_EXC_ERROR ;
3627 END IF;
3628 --------------------------------------------
3629 -- Create Article Variables
3630 -- Insert at one shot .. This is much efficient than calling the create article version and parse out the variables
3631
3632 INSERT INTO OKC_ARTICLE_VARIABLES
3633 (
3634 ARTICLE_VERSION_ID ,
3635 VARIABLE_CODE ,
3636 OBJECT_VERSION_NUMBER ,
3637 CREATED_BY ,
3638 CREATION_DATE ,
3639 LAST_UPDATE_DATE ,
3640 LAST_UPDATED_BY ,
3641 LAST_UPDATE_LOGIN
3642 )
3643 SELECT
3644 x_article_version_id,
3645 VARIABLE_CODE,
3646 1.0,
3647 l_user_id,
3648 sysdate,
3649 sysdate,
3650 l_user_id,
3651 l_login_id
3652 FROM OKC_ARTICLE_VARIABLES
3653 WHERE ARTICLE_VERSION_ID = p_article_version_id;
3654 --------------------------------------------
3655 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3657 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3658 RAISE FND_API.G_EXC_ERROR ;
3659 END IF;
3660 --------------------------------------------
3661 if p_copy_relationship_yn = 'Y' THEN
3662 OPEN l_relationship_csr(l_p_article_id, G_CURRENT_ORG_ID);
3663 LOOP
3664 BEGIN
3665 FETCH l_relationship_csr BULK COLLECT INTO
3666 l_target_article_id_tbl,
3667 l_relationship_type_tbl;
3668 i := 0;
3669 EXIT WHEN l_target_article_id_tbl.COUNT = 0;
3670 -- dbms_output.put_line('Total Rel Found: '||l_target_article_id_tbl.cOUNT);
3671 FORALL i IN l_target_article_id_tbl.FIRST..l_target_article_id_tbl.LAST
3672 INSERT INTO OKC_ARTICLE_RELATNS_ALL
3673 (
3674 SOURCE_ARTICLE_ID,
3675 TARGET_ARTICLE_ID,
3676 ORG_ID,
3677 RELATIONSHIP_TYPE,
3678 OBJECT_VERSION_NUMBER,
3679 CREATED_BY,
3680 CREATION_DATE,
3681 LAST_UPDATED_BY,
3682 LAST_UPDATE_LOGIN,
3683 LAST_UPDATE_DATE
3684 )
3685 VALUES
3686 (
3687 x_article_id,
3688 l_target_article_id_tbl(i),
3689 G_CURRENT_ORG_ID,
3690 l_relationship_type_tbl(i),
3691 1.0,
3692 l_User_Id,
3693 sysdate,
3694 l_User_Id,
3695 l_login_Id,
3696 sysdate
3697 );
3698
3699 -- Revert the target and source article ids.
3700 FORALL i IN l_target_article_id_tbl.FIRST..l_target_article_id_tbl.LAST
3701 INSERT INTO OKC_ARTICLE_RELATNS_ALL
3702 (
3703 SOURCE_ARTICLE_ID,
3704 TARGET_ARTICLE_ID,
3705 ORG_ID,
3706 RELATIONSHIP_TYPE,
3707 OBJECT_VERSION_NUMBER,
3708 CREATED_BY,
3709 CREATION_DATE,
3710 LAST_UPDATED_BY,
3711 LAST_UPDATE_LOGIN,
3712 LAST_UPDATE_DATE
3713 )
3714 VALUES
3715 (
3716 l_target_article_id_tbl(i),
3717 x_article_id,
3718 G_CURRENT_ORG_ID,
3719 l_relationship_type_tbl(i),
3720 1.0,
3721 l_User_Id,
3722 sysdate,
3723 l_User_Id,
3724 l_login_Id,
3725 sysdate
3726 );
3727
3728 l_target_article_id_tbl.DELETE;
3729 l_relationship_type_tbl.DELETE;
3730 EXIT WHEN l_relationship_csr%NOTFOUND;
3731 EXCEPTION
3732 WHEN OTHERS THEN
3733 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3734 p_msg_name => G_UNEXPECTED_ERROR,
3735 p_token1 => G_SQLCODE_TOKEN,
3736 p_token1_value => sqlcode,
3737 p_token2 => G_SQLERRM_TOKEN,
3738 p_token2_value => sqlerrm);
3739 x_return_status := G_RET_STS_UNEXP_ERROR ;
3740 exit;
3741 END;
3742 END LOOP; -- main cursor loop
3743 CLOSE l_relationship_csr;
3744 --------------------------------------------
3745 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3747 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3748 RAISE FND_API.G_EXC_ERROR ;
3749 END IF;
3750 --------------------------------------------
3751 END IF;
3752 IF p_copy_folder_assoc_yn = 'Y' THEN
3753 INSERT INTO OKC_FOLDER_CONTENTS
3754 (
3755 MEMBER_ID ,
3756 FOLDER_ID ,
3757 OBJECT_VERSION_NUMBER,
3758 CREATED_BY ,
3759 CREATION_DATE ,
3760 LAST_UPDATE_DATE ,
3761 LAST_UPDATED_BY ,
3762 LAST_UPDATE_LOGIN
3763 )
3764 SELECT
3765 x_article_id,
3766 folder_id,
3767 1.0,
3768 l_User_Id,
3769 sysdate,
3770 sysdate,
3771 l_User_Id,
3772 l_login_Id
3773 FROM OKC_FOLDER_CONTENTS mem
3774 WHERE MEMBER_ID = l_p_article_id
3775 AND exists
3776 (select 1 from okc_folders_all_b fold where
3777 fold.org_id = G_CURRENT_ORG_ID
3778 and fold.folder_id = mem.folder_id);
3779
3780 END IF;
3781
3782 /*kkolukul: CLM changes*/
3783 -- Create Article section mappings based on variable name
3784 -- Insert at one shot ..
3785
3786 INSERT INTO OKC_ART_VAR_SECTIONS
3787 (
3788 VARIABLE_CODE,
3789 VARIABLE_VALUE_ID,
3790 VARIABLE_VALUE,
3791 ARTICLE_ID,
3792 SCN_CODE,
3793 ARTICLE_VERSION_ID,
3794 CREATED_BY ,
3795 CREATION_DATE ,
3796 LAST_UPDATE_DATE ,
3797 LAST_UPDATED_BY ,
3798 LAST_UPDATE_LOGIN
3799 )
3800 SELECT
3801 VARIABLE_CODE,
3802 VARIABLE_VALUE_ID,
3803 VARIABLE_VALUE,
3804 x_article_id,
3805 SCN_CODE,
3806 x_article_version_id,
3807 l_User_Id,
3808 sysdate,
3809 sysdate,
3810 l_User_Id,
3811 l_login_Id
3812
3813 FROM OKC_ART_VAR_SECTIONS
3814 WHERE ARTICLE_VERSION_ID = p_article_version_id;
3815 --------------------------------------------
3816 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3818 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3819 RAISE FND_API.G_EXC_ERROR ;
3820 END IF;
3821 ----------------------------------------------
3822
3823 -- Standard check of p_commit
3824 IF FND_API.To_Boolean( p_commit ) THEN
3825 COMMIT WORK;
3826 END IF;
3827 -- Standard call to get message count and if count is 1, get message info.
3828 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
3829
3830 IF (l_debug = 'Y') THEN
3831 okc_debug.log('2800: Leaving copy_article', 2);
3832 END IF;
3833
3834 EXCEPTION
3835 WHEN FND_API.G_EXC_ERROR THEN
3836 IF (l_debug = 'Y') THEN
3837 okc_debug.log('2900: Leaving copy_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
3838 END IF;
3839 ROLLBACK TO g_copy_article_GRP;
3840 x_return_status := G_RET_STS_ERROR ;
3841 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
3842
3843 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3844 IF (l_debug = 'Y') THEN
3845 okc_debug.log('3000: Leaving copy_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3846 END IF;
3847 ROLLBACK TO g_copy_article_GRP;
3848 x_return_status := G_RET_STS_UNEXP_ERROR ;
3849 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
3850
3851 WHEN OTHERS THEN
3852 IF (l_debug = 'Y') THEN
3853 okc_debug.log('3100: Leaving copy_article because of EXCEPTION: '||sqlerrm, 2);
3854 END IF;
3855
3856 ROLLBACK TO g_copy_article_GRP;
3857 x_return_status := G_RET_STS_UNEXP_ERROR ;
3858 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3859 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3860 END IF;
3861 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
3862
3863 END copy_article;
3864
3865 PROCEDURE get_local_article_id
3866 (
3867 x_return_status OUT NOCOPY VARCHAR2,
3868 x_msg_count OUT NOCOPY NUMBER,
3869 x_msg_data OUT NOCOPY VARCHAR2,
3870 p_local_org_id IN NUMBER,
3871 p_src_article_id IN OUT NOCOPY NUMBER,
3872 p_tar_article_id IN OUT NOCOPY NUMBER
3873 ) IS
3874 CURSOR l_local_article_csr(cp_article_id IN NUMBER,
3875 cp_local_org_id IN NUMBER) IS
3876 SELECT V2.article_id FROM OKC_ARTICLE_ADOPTIONS AA,
3877 OKC_ARTICLE_VERSIONS V1 ,
3878 OKC_ARTICLE_VERSIONS V2
3879 WHERE V1.ARTICLE_VERSION_ID = AA.GLOBAL_ARTICLE_VERSION_ID
3880 AND V2.ARTICLE_VERSION_ID = AA.LOCAL_ARTICLE_VERSION_ID
3881 AND V1.ARTICLE_ID = cp_article_id
3882 AND AA.LOCAL_ORG_ID = cp_local_org_id
3883 AND AA.ADOPTION_TYPE = 'LOCALIZED'
3884 AND V2.ARTICLE_STATUS = 'APPROVED'
3885 AND NVL(V2.END_DATE, SYSDATE+1) > SYSDATE
3886 AND rownum < 2
3887 UNION ALL
3888 SELECT V1.article_id FROM OKC_ARTICLE_ADOPTIONS AA,
3889 OKC_ARTICLE_VERSIONS V1
3890 WHERE V1.ARTICLE_VERSION_ID = AA.GLOBAL_ARTICLE_VERSION_ID
3891 AND V1.ARTICLE_ID = cp_article_id
3892 AND AA.LOCAL_ORG_ID = cp_local_org_id
3893 AND AA.ADOPTION_TYPE = 'ADOPTED'
3894 AND AA.ADOPTION_STATUS = 'APPROVED'
3895 AND rownum < 2;
3896
3897 cursor l_rel_exist_csr (cp_src_article_id IN NUMBER,
3898 cp_tar_article_id IN NUMBER,
3899 cp_local_org_id IN NUMBER) IS
3900 SELECT '1' FROM OKC_ARTICLE_RELATNS_ALL
3901 WHERE source_article_id = cp_src_article_id
3902 AND target_article_id = cp_tar_article_id
3903 AND org_id = cp_local_org_id;
3904 l_src_local_article_id NUMBER;
3905 l_tar_local_article_id NUMBER;
3906 l_rownotfound BOOLEAN := FALSE;
3907 l_dummy VARCHAR2(1) := '?';
3908 BEGIN
3909 x_return_status := G_RET_STS_SUCCESS;
3910 OPEN l_local_article_csr(p_src_article_id, p_local_org_id);
3911 FETCH l_local_article_csr INTO l_src_local_article_id;
3912 l_rownotfound := l_local_article_csr%NOTFOUND;
3913 CLOSE l_local_article_csr;
3914 IF l_rownotfound THEN
3915 p_src_article_id := NULL;
3916 p_tar_article_id := NULL;
3917 return;
3918 END IF;
3919 l_rownotfound := FALSE;
3920 OPEN l_local_article_csr(p_tar_article_id, p_local_org_id);
3921 FETCH l_local_article_csr INTO l_tar_local_article_id;
3922 l_rownotfound := l_local_article_csr%NOTFOUND;
3923 CLOSE l_local_article_csr;
3924 IF l_rownotfound THEN
3925 p_src_article_id := NULL;
3926 p_tar_article_id := NULL;
3927 return;
3928 END IF;
3929 -- The following check ensures that the source and target are adopted as is
3930 -- i.e. no localization done. In that case we do not need to check if relationsh-- ip exists as the main pgm already does this.
3931 if l_tar_local_article_id = p_tar_article_id AND
3932 l_src_local_article_id = p_src_article_id THEN
3933 return;
3934 end if;
3935 l_rownotfound := FALSE;
3936 OPEN l_rel_exist_csr(l_src_local_article_id,
3937 l_tar_local_article_id,
3938 p_local_org_id);
3939 FETCH l_rel_exist_csr INTO l_dummy;
3940 l_rownotfound := l_rel_exist_csr%NOTFOUND;
3941 CLOSE l_rel_exist_csr;
3942
3943 IF l_rownotfound THEN
3944 p_src_article_id := l_src_local_article_id;
3945 p_tar_article_id := l_tar_local_article_id;
3946 ELSE
3947 p_src_article_id := NULL;
3948 p_tar_article_id := NULL;
3949 END IF;
3950 EXCEPTION
3951 WHEN OTHERS THEN
3952 IF (l_debug = 'Y') THEN
3953 okc_debug.log('500: Leaving get local article id EXCEPTION: '||sqlerrm, 2);
3954 END IF;
3955 IF l_local_article_csr%ISOPEN THEN
3956 CLOSE l_local_article_csr;
3957 END IF;
3958 IF l_rel_exist_csr%ISOPEN THEN
3959 CLOSE l_rel_exist_csr;
3960 END IF;
3961 x_return_status := G_RET_STS_UNEXP_ERROR ;
3962
3963 END get_local_article_id;
3964
3965 PROCEDURE check_adopted
3966 (
3967 x_return_status OUT NOCOPY VARCHAR2,
3968 x_msg_count OUT NOCOPY NUMBER,
3969 x_msg_data OUT NOCOPY VARCHAR2,
3970 p_local_org_id IN NUMBER,
3971 p_src_article_id IN OUT NOCOPY NUMBER,
3972 p_tar_article_id IN OUT NOCOPY NUMBER
3973 ) IS
3974 CURSOR l_local_article_csr(cp_src_article_id IN NUMBER,
3975 cp_tar_article_id IN NUMBER,
3976 cp_local_org_id IN NUMBER) IS
3977 SELECT '1' FROM OKC_ARTICLE_ADOPTIONS AA1,
3978 OKC_ARTICLE_VERSIONS V1,
3979 OKC_ARTICLE_ADOPTIONS AA2,
3980 OKC_ARTICLE_VERSIONS V2
3981 WHERE V1.ARTICLE_VERSION_ID = AA1.GLOBAL_ARTICLE_VERSION_ID
3982 AND V1.ARTICLE_ID = cp_src_article_id
3983 AND AA1.LOCAL_ORG_ID = cp_local_org_id
3984 AND AA1.ADOPTION_TYPE = 'ADOPTED'
3985 AND V2.ARTICLE_VERSION_ID = AA2.GLOBAL_ARTICLE_VERSION_ID
3986 AND V2.ARTICLE_ID = cp_tar_article_id
3987 AND AA2.LOCAL_ORG_ID = AA1.LOCAL_ORG_ID
3988 AND AA2.ADOPTION_TYPE = 'ADOPTED'
3989 AND NOT EXISTS
3990 (
3991 SELECT '1' FROM OKC_ARTICLE_RELATNS_ALL
3992 WHERE source_article_id = V1.ARTICLE_ID
3993 AND target_article_id = V2.ARTICLE_ID
3994 AND org_id = AA1.LOCAL_ORG_ID
3995 );
3996
3997 l_src_local_article_id NUMBER;
3998 l_tar_local_article_id NUMBER;
3999 l_rownotfound BOOLEAN := FALSE;
4000 l_dummy VARCHAR2(1) := '?';
4001 BEGIN
4002 x_return_status := G_RET_STS_SUCCESS;
4003 OPEN l_local_article_csr(p_src_article_id, p_tar_article_id, p_local_org_id);
4004 FETCH l_local_article_csr INTO l_dummy;
4005 l_rownotfound := l_local_article_csr%NOTFOUND;
4006 CLOSE l_local_article_csr;
4007 IF l_rownotfound THEN
4008 p_src_article_id := NULL;
4009 p_tar_article_id := NULL;
4010 return;
4011 END IF;
4012 EXCEPTION
4013 WHEN OTHERS THEN
4014 IF (l_debug = 'Y') THEN
4015 okc_debug.log('500: Leaving check adopted.. EXCEPTION: '||sqlerrm, 2);
4016 END IF;
4017 IF l_local_article_csr%ISOPEN THEN
4018 CLOSE l_local_article_csr;
4019 END IF;
4020 x_return_status := G_RET_STS_UNEXP_ERROR ;
4021
4022 END check_adopted;
4023
4024 PROCEDURE AUTO_ADOPT_RELATIONSHIPS
4025 (
4026 x_return_status OUT NOCOPY VARCHAR2,
4027 x_msg_count OUT NOCOPY NUMBER,
4028 x_msg_data OUT NOCOPY VARCHAR2,
4029 p_fetchsize IN NUMBER,
4030 p_relationship_type IN VARCHAR2,
4031 p_src_global_article_id IN NUMBER,
4032 p_tar_global_article_id IN NUMBER
4033 ) IS
4034 l_api_version CONSTANT NUMBER := 1;
4035 l_api_name CONSTANT VARCHAR2(30) := 'g_auto_adopt_relationship';
4036 l_dummy VARCHAR2(1) := '?';
4037 l_rowfound BOOLEAN := FALSE;
4038 l_local_article_version_id NUMBER;
4039 i NUMBER := 0;
4040 j NUMBER := 0;
4041 l_return_status VARCHAR2(1);
4042 l_GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
4043 TYPE l_org_id_list IS TABLE OF HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
4044 TYPE l_notifier_list IS TABLE OF HR_ORGANIZATION_INFORMATION.ORG_INFORMATION2%TYPE INDEX BY BINARY_INTEGER;
4045 TYPE l_adoption_status_list IS TABLE OF OKC_ARTICLE_ADOPTIONS.ADOPTION_STATUS%TYPE INDEX BY BINARY_INTEGER;
4046 TYPE l_source_article_id_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.SOURCE_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
4047 TYPE l_target_article_id_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.TARGET_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
4048 TYPE l_adoption_type_list IS TABLE OF OKC_ARTICLE_ADOPTIONS.ADOPTION_TYPE%TYPE INDEX BY BINARY_INTEGER;
4049
4050 l_source_article_id_tbl l_source_article_id_list ;
4051 l_target_article_id_tbl l_target_article_id_list ;
4052
4053
4054 l_org_id_tbl l_org_id_list;
4055 l_adoption_type_tbl l_adoption_type_list;
4056 l_notifier_tbl l_notifier_list;
4057
4058 CURSOR l_org_info_csr (cp_src_global_article_id IN NUMBER,
4059 cp_tar_global_article_id IN NUMBER,
4060 cp_relationship_type IN VARCHAR2) IS
4061 SELECT ORGANIZATION_ID,
4062 decode(nvl(ORG_INFORMATION1,'N'),'N','AVAILABLE','Y','ADOPTED') ADOPTION_TYPE ,
4063 ORG_INFORMATION2
4064
4065 FROM HR_ORGANIZATION_INFORMATION
4066 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
4067 AND ORGANIZATION_ID <> G_GLOBAL_ORG_ID
4068 AND NOT EXISTS
4069 (
4070 SELECT '1'
4071 FROM OKC_ARTICLE_RELATNS_ALL R1
4072 WHERE R1.SOURCE_ARTICLE_ID = cp_src_global_article_id AND
4073 R1.TARGET_ARTICLE_ID = cp_tar_global_article_id AND
4074 R1.RELATIONSHIP_TYPE = cp_relationship_type AND
4075 R1.ORG_ID = ORGANIZATION_ID
4076 );
4077
4078 CURSOR l_approved_csr (cp_src_global_article_id IN NUMBER,
4079 cp_tar_global_article_id IN NUMBER) IS
4080 SELECT '1'
4081 FROM OKC_ARTICLES_ALL A, OKC_ARTICLES_ALL B
4082 WHERE A.ARTICLE_ID = cp_src_global_article_id
4083 AND B.ARTICLE_ID = cp_tar_global_article_id
4084 AND EXISTS
4085 (SELECT 1 FROM OKC_ARTICLE_VERSIONS V
4086 WHERE V.ARTICLE_ID = B.ARTICLE_ID
4087 AND V.GLOBAL_YN = 'Y'
4088 AND V.ARTICLE_STATUS = 'APPROVED'
4089 AND NVL(V.END_DATE,SYSDATE + 1) > SYSDATE
4090 )
4091 AND EXISTS
4092 (SELECT 1 FROM OKC_ARTICLE_VERSIONS V1
4093 WHERE V1.ARTICLE_ID = A.ARTICLE_ID
4094 AND V1.GLOBAL_YN = 'Y'
4095 AND V1.ARTICLE_STATUS = 'APPROVED'
4096 AND NVL(V1.END_DATE,SYSDATE + 1) > SYSDATE
4097 );
4098
4099 l_user_id NUMBER := FND_GLOBAL.USER_ID;
4100 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
4101 l_src_local_article_id NUMBER;
4102 l_tar_local_article_id NUMBER;
4103 l_rownotfound BOOLEAN := FALSE;
4104
4105 BEGIN
4106 x_return_status := G_RET_STS_SUCCESS;
4107 IF (l_debug = 'Y') THEN
4108 okc_debug.log('100: Entered auto adopt relationship', 2);
4109 END IF;
4110
4111 x_return_status := FND_API.G_RET_STS_SUCCESS;
4112 --dbms_output.put_line('Global org is: '|| l_global_org_id);
4113 OPEN l_approved_csr (p_src_global_article_id, p_tar_global_article_id);
4114 FETCH l_approved_csr INTO l_dummy;
4115 l_rownotfound := l_approved_csr%NOTFOUND;
4116 CLOSE l_approved_csr;
4117 IF l_rownotfound THEN
4118 return;
4119 END IF;
4120 OPEN l_org_info_csr (p_src_global_article_id,
4121 p_tar_global_article_id,
4122 p_relationship_type);
4123 LOOP
4124 BEGIN
4125 FETCH l_org_info_csr BULK COLLECT INTO l_org_id_tbl, l_adoption_type_tbl, l_notifier_tbl LIMIT p_fetchsize;
4126 i := 0;
4127 --dbms_output.put_line('Cursor fetched rows: '||l_org_id_tbl.COUNT);
4128 EXIT WHEN l_org_id_tbl.COUNT = 0;
4129 FOR i in 1..l_org_id_tbl.COUNT LOOP
4130 l_source_article_id_tbl(i) := p_src_global_article_id;
4131 l_target_article_id_tbl(i) := p_tar_global_article_id;
4132 --dbms_output.put_line('Cursor fetched adoption type: '|| l_adoption_type_tbl(i)||'*'||l_org_id_tbl(i)||'*'|| l_source_article_id_tbl(i)|| '*'||l_target_article_id_tbl(i));
4133 IF l_adoption_type_tbl(i) <> 'ADOPTED' Then
4134 check_adopted
4135 (
4136 x_return_status => x_return_status,
4137 x_msg_count => x_msg_count,
4138 x_msg_data => x_msg_data,
4139 p_local_org_id => l_org_id_tbl(i),
4140 p_src_article_id => l_source_article_id_tbl(i),
4141 p_tar_article_id => l_target_article_id_tbl(i)
4142 );
4143 -- Removed after discusssion between dev and PM on 10/30 to adopte relationships only for adopted and not localized.
4144 /*
4145 get_local_article_id
4146 (
4147 x_return_status => x_return_status,
4148 x_msg_count => x_msg_count,
4149 x_msg_data => x_msg_data,
4150 p_local_org_id => l_org_id_tbl(i),
4151 p_src_article_id => l_source_article_id_tbl(i),
4152 p_tar_article_id => l_target_article_id_tbl(i)
4153 );
4154 */
4155 --dbms_output.put_line('After getlocal article id: '|| l_adoption_type_tbl(i)||'*'||l_org_id_tbl(i)||'*'|| l_source_article_id_tbl(i)|| '*'||l_target_article_id_tbl(i));
4156 IF x_return_status <> G_RET_STS_SUCCESS THEN
4157 exit;
4158 END IF;
4159 END IF;
4160 END LOOP;
4161 IF x_return_status <> G_RET_STS_SUCCESS THEN
4162 exit;
4163 END IF;
4164
4165 FORALL j IN l_org_id_tbl.FIRST..l_org_id_tbl.LAST
4166 INSERT INTO OKC_ARTICLE_RELATNS_ALL
4167 (
4168 SOURCE_ARTICLE_ID,
4169 TARGET_ARTICLE_ID,
4170 ORG_ID,
4171 RELATIONSHIP_TYPE,
4172 OBJECT_VERSION_NUMBER,
4173 CREATED_BY,
4174 CREATION_DATE,
4175 LAST_UPDATED_BY,
4176 LAST_UPDATE_LOGIN,
4177 LAST_UPDATE_DATE
4178 )
4179 SELECT
4180 l_source_article_id_tbl(j),
4181 l_target_article_id_tbl(j),
4182 l_org_id_tbl(j),
4183 p_relationship_type,
4184 1.0,
4185 l_User_Id,
4186 sysdate,
4187 l_User_Id,
4188 l_login_Id,
4189 sysdate
4190 FROM DUAL
4191 WHERE l_source_article_id_tbl(j) IS NOT NULL;
4192
4193 -- Revert the target and source article ids.
4194
4195 FORALL j IN l_org_id_tbl.FIRST..l_org_id_tbl.LAST
4196 INSERT INTO OKC_ARTICLE_RELATNS_ALL
4197 (
4198 SOURCE_ARTICLE_ID,
4199 TARGET_ARTICLE_ID,
4200 ORG_ID,
4201 RELATIONSHIP_TYPE,
4202 OBJECT_VERSION_NUMBER,
4203 CREATED_BY,
4204 CREATION_DATE,
4205 LAST_UPDATED_BY,
4206 LAST_UPDATE_LOGIN,
4207 LAST_UPDATE_DATE
4208 )
4209 SELECT
4210 l_target_article_id_tbl(j),
4211 l_source_article_id_tbl(j),
4212 l_org_id_tbl(j),
4213 p_relationship_type,
4214 1.0,
4215 l_User_Id,
4216 sysdate,
4217 l_User_Id,
4218 l_Login_Id,
4219 sysdate
4220 FROM DUAL
4221 WHERE l_source_article_id_tbl(j) IS NOT NULL;
4222 l_target_article_id_tbl.DELETE;
4223 l_source_article_id_tbl.DELETE;
4224 l_org_id_tbl.DELETE;
4225 l_adoption_type_tbl.DELETE;
4226 l_notifier_tbl.DELETE;
4227 EXIT WHEN l_org_info_csr%NOTFOUND;
4228 EXCEPTION
4229 WHEN OTHERS THEN
4230 --dbms_output.put_line(sqlerrm);
4231 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
4232 p_msg_name => G_UNEXPECTED_ERROR,
4233 p_token1 => G_SQLCODE_TOKEN,
4234 p_token1_value => sqlcode,
4235 p_token2 => G_SQLERRM_TOKEN,
4236 p_token2_value => sqlerrm);
4237 x_return_status := G_RET_STS_UNEXP_ERROR ;
4238 exit;
4239 END;
4240 END LOOP; -- main cursor loop
4241 CLOSE l_org_info_csr;
4242 EXCEPTION
4243 WHEN FND_API.G_EXC_ERROR THEN
4244 IF (l_debug = 'Y') THEN
4245 okc_debug.log('300: Leaving Auto_Adoption: OKC_API.G_EXCEPTION_ERROR Exception', 2);
4246 END IF;
4247 x_return_status := G_RET_STS_ERROR ;
4248
4249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4250 IF (l_debug = 'Y') THEN
4251 okc_debug.log('400: Leaving Auto_Adoption: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4252 END IF;
4253 IF l_org_info_csr%ISOPEN THEN
4254 CLOSE l_org_info_csr;
4255 END IF;
4256 IF l_approved_csr%ISOPEN THEN
4257 CLOSE l_approved_csr;
4258 END IF;
4259 x_return_status := G_RET_STS_UNEXP_ERROR ;
4260
4261 WHEN OTHERS THEN
4262 IF (l_debug = 'Y') THEN
4263 okc_debug.log('500: Leaving Auto_Adoption because of EXCEPTION: '||sqlerrm, 2);
4264 END IF;
4265 IF l_org_info_csr%ISOPEN THEN
4266 CLOSE l_org_info_csr;
4267 END IF;
4268 IF l_approved_csr%ISOPEN THEN
4269 CLOSE l_approved_csr;
4270 END IF;
4271 x_return_status := G_RET_STS_UNEXP_ERROR ;
4272
4273 END AUTO_ADOPT_RELATIONSHIPS;
4274 -------------------------------------
4275 -- PROCEDURE create article relationship
4276 -------------------------------------
4277 PROCEDURE create_article_relationship(
4278 p_api_version IN NUMBER,
4279 p_init_msg_list IN VARCHAR2 ,
4280 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4281 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4282 x_return_status OUT NOCOPY VARCHAR2,
4283 x_msg_count OUT NOCOPY NUMBER,
4284 x_msg_data OUT NOCOPY VARCHAR2,
4285 p_source_article_id IN NUMBER,
4286 p_target_article_id IN NUMBER,
4287 p_org_id IN NUMBER,
4288 p_relationship_type IN VARCHAR2
4289 ) IS
4290
4291 l_api_version CONSTANT NUMBER := 1;
4292 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_row';
4293 l_object_version_number OKC_ARTICLE_RELATNS_ALL.OBJECT_VERSION_NUMBER%TYPE := 1;
4294 l_created_by OKC_ARTICLE_RELATNS_ALL.CREATED_BY%TYPE;
4295 l_creation_date OKC_ARTICLE_RELATNS_ALL.CREATION_DATE%TYPE;
4296 l_last_updated_by OKC_ARTICLE_RELATNS_ALL.LAST_UPDATED_BY%TYPE;
4297 l_last_update_login OKC_ARTICLE_RELATNS_ALL.LAST_UPDATE_LOGIN%TYPE;
4298 l_last_update_date OKC_ARTICLE_RELATNS_ALL.LAST_UPDATE_DATE%TYPE;
4299 l_source_article_id NUMBER;
4300 l_target_article_id NUMBER;
4301 l_org_id NUMBER;
4302 BEGIN
4303
4304 IF (l_debug = 'Y') THEN
4305 okc_debug.log('600: Entered insert_row', 2);
4306 END IF;
4307
4308 -- Standard Start of API savepoint
4309 SAVEPOINT g_insert_row_GRP;
4310 -- Standard call to check for call compatibility.
4311 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4313 END IF;
4314 -- Initialize message list if p_init_msg_list is set to TRUE.
4315 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4316 FND_MSG_PUB.initialize;
4317 END IF;
4318 -- Initialize API return status to success
4319 x_return_status := FND_API.G_RET_STS_SUCCESS;
4320
4321 --------------------------------------------
4322 -- Calling Simple API for Creating A Row
4323 --------------------------------------------
4324 --dbms_output.put_line('Insrting...with src/target: '||p_source_article_id||'*'||p_target_article_id);
4325 OKC_ARTICLE_RELATIONSHIPS_PVT.Insert_Row(
4326 p_validation_level => p_validation_level,
4327 x_return_status => x_return_status,
4328 p_source_article_id => p_source_article_id,
4329 p_target_article_id => p_target_article_id,
4330 p_org_id => p_org_id,
4331 p_relationship_type => p_relationship_type,
4332 x_source_article_id => l_source_article_id,
4333 x_target_article_id => l_target_article_id,
4334 x_org_id => l_org_id
4335 );
4336 --------------------------------------------
4337 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4339 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4340 RAISE FND_API.G_EXC_ERROR ;
4341 END IF;
4342 --------------------------------------------
4343 -- Create opposite row by flipping the source and target for the relationship
4344 --------------------------------------------
4345 --dbms_output.put_line('Insrting. Reverse ..with src/target: '||p_target_article_id||'*'||p_source_article_id);
4346 OKC_ARTICLE_RELATIONSHIPS_PVT.Insert_Row(
4347 p_validation_level => p_validation_level,
4348 x_return_status => x_return_status,
4349 p_source_article_id => p_target_article_id,
4350 p_target_article_id => p_source_article_id,
4351 p_org_id => p_org_id,
4352 p_relationship_type => p_relationship_type,
4353 x_source_article_id => l_source_article_id,
4354 x_target_article_id => l_target_article_id,
4355 x_org_id => l_org_id
4356 );
4357 --------------------------------------------
4358 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4360 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4361 RAISE FND_API.G_EXC_ERROR ;
4362 END IF;
4363 --------------------------------------------
4364
4365 -- Trigger autoadoption of relationships for all orgs if a global org has created a relationship between two
4366 -- global articles.
4367
4368 IF p_org_id = G_GLOBAL_ORG_ID AND p_org_id <> -99 THEN
4369 --dbms_output.put_line('Calling....Adopt Rel');
4370 AUTO_ADOPT_RELATIONSHIPS
4371 (
4372 x_return_status => x_return_status,
4373 x_msg_count => x_msg_count,
4374 x_msg_data => x_msg_data,
4375 p_fetchsize => 100,
4376 p_src_global_article_id => p_source_article_id,
4377 p_tar_global_article_id => p_target_article_id,
4378 p_relationship_type => p_relationship_type);
4379 --------------------------------------------
4380 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4382 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4383 RAISE FND_API.G_EXC_ERROR ;
4384 END IF;
4385 --------------------------------------------
4386 END IF;
4387
4388 -- Standard check of p_commit
4389 IF FND_API.To_Boolean( p_commit ) THEN
4390 COMMIT WORK;
4391 END IF;
4392 -- Standard call to get message count and if count is 1, get message info.
4393 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
4394
4395 IF (l_debug = 'Y') THEN
4396 okc_debug.log('700: Leaving insert_row', 2);
4397 END IF;
4398
4399 EXCEPTION
4400 WHEN FND_API.G_EXC_ERROR THEN
4401 IF (l_debug = 'Y') THEN
4402 okc_debug.log('800: Leaving insert_row: OKC_API.G_EXCEPTION_ERROR Exception', 2);
4403 END IF;
4404 ROLLBACK TO g_insert_row_GRP;
4405 x_return_status := G_RET_STS_ERROR ;
4406 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
4407
4408 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4409 IF (l_debug = 'Y') THEN
4410 okc_debug.log('900: Leaving insert_row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4411 END IF;
4412 ROLLBACK TO g_insert_row_GRP;
4413 x_return_status := G_RET_STS_UNEXP_ERROR ;
4414 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
4415
4416 WHEN OTHERS THEN
4417 IF (l_debug = 'Y') THEN
4418 okc_debug.log('1000: Leaving insert_row because of EXCEPTION: '||sqlerrm, 2);
4419 END IF;
4420
4421 ROLLBACK TO g_insert_row_GRP;
4422 x_return_status := G_RET_STS_UNEXP_ERROR ;
4423 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4424 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4425 END IF;
4426 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
4427
4428 END create_article_relationship;
4429
4430 ---------------------------------------------------------------------------
4431 -- PROCEDURE delete_article_relationship
4432 ---------------------------------------------------------------------------
4433 PROCEDURE DELETE_AUTO_ADOPTED_RELATIONS
4434 (
4435 x_return_status OUT NOCOPY VARCHAR2,
4436 x_msg_count OUT NOCOPY NUMBER,
4437 x_msg_data OUT NOCOPY VARCHAR2,
4438 p_fetchsize IN NUMBER,
4439 p_source_global_article_id IN NUMBER,
4440 p_target_global_article_id IN NUMBER
4441 ) IS
4442 l_api_version CONSTANT NUMBER := 1;
4443 l_api_name CONSTANT VARCHAR2(30) := 'g_auto_adoption';
4444 j NUMBER := 0;
4445 l_GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
4446 TYPE l_org_id_list IS TABLE OF HR_ORGANIZATION_INFORMATION.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
4447 TYPE l_article_number_list IS TABLE OF OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE INDEX BY BINARY_INTEGER ;
4448 TYPE l_source_article_id_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.SOURCE_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
4449 TYPE l_target_article_id_list IS TABLE OF OKC_ARTICLE_RELATNS_ALL.TARGET_ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
4450
4451 l_article_number_tbl l_article_number_list ;
4452 l_source_article_id_tbl l_source_article_id_list ;
4453 l_target_article_id_tbl l_target_article_id_list ;
4454 l_org_id_tbl l_org_id_list;
4455 l_firsttime BOOLEAN := TRUE;
4456
4457 CURSOR l_relationship_csr (cp_source_global_article_id IN NUMBER,
4458 cp_target_global_article_id IN NUMBER) IS
4459 SELECT source_article_id, target_article_id, org_id
4460 FROM OKC_ARTICLE_RELATNS_ALL REL
4461 WHERE source_article_id = cp_source_global_article_id
4462 AND target_article_id = cp_target_global_article_id;
4463 /*
4464 AND exists
4465 ( SELECT '1'
4466 FROM HR_ORGANIZATION_INFORMATION
4467 WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
4468 AND ORGANIZATION_ID = rel.org_id
4469 AND ORG_INFORMATION1 = 'Y');
4470 */
4471
4472 BEGIN
4473 IF (l_debug = 'Y') THEN
4474 okc_debug.log('100: Entered create_adoption', 2);
4475 END IF;
4476
4477 -- Standard Start of API savepoint
4478 -- Initialize API return status to success
4479 x_return_status := FND_API.G_RET_STS_SUCCESS;
4480 --dbms_output.put_line('Global org is: '|| l_global_org_id);
4481 OPEN l_relationship_csr (p_source_global_article_id, p_target_global_article_id);
4482 LOOP
4483 FETCH l_relationship_csr BULK COLLECT INTO l_source_article_id_tbl,
4484 l_target_article_id_tbl,
4485 l_org_id_tbl
4486 LIMIT p_fetchsize;
4487
4488 -- Also include the global article data
4489
4490 if l_firsttime THEN
4491 l_source_article_id_tbl(l_source_article_id_tbl.COUNT+1) := p_source_global_article_id;
4492 l_target_article_id_tbl(l_target_article_id_tbl.COUNT+1) := p_target_global_article_id;
4493 l_org_id_tbl(l_org_id_tbl.COUNT+1) := G_GLOBAL_ORG_ID;
4494 l_firsttime := FALSE;
4495 end if;
4496 EXIT WHEN l_source_article_id_tbl.COUNT = 0;
4497 j := 0;
4498 FORALL j IN l_source_article_id_tbl.FIRST..l_source_article_id_tbl.LAST
4499 DELETE FROM OKC_ARTICLE_RELATNS_ALL
4500 WHERE source_article_id = l_source_article_id_tbl(j) AND
4501 target_article_id = l_target_article_id_tbl(j) AND
4502 org_id = l_org_id_tbl(j);
4503
4504 -- Revert the target and source article ids.
4505 j := 0;
4506 FORALL j IN l_source_article_id_tbl.FIRST..l_source_article_id_tbl.LAST
4507 DELETE FROM OKC_ARTICLE_RELATNS_ALL
4508 WHERE target_article_id = l_source_article_id_tbl(j) AND
4509 source_article_id = l_target_article_id_tbl(j) AND
4510 org_id = l_org_id_tbl(j);
4511
4512
4513 l_org_id_tbl.DELETE;
4514 l_source_article_id_tbl.DELETE;
4515 l_target_article_id_tbl.DELETE;
4516 EXIT WHEN l_relationship_csr%NOTFOUND;
4517 END LOOP; -- relationship csr fetch
4518 CLOSE l_relationship_csr;
4519 EXCEPTION
4520 WHEN OTHERS THEN
4521 IF (l_debug = 'Y') THEN
4522 okc_debug.log('500: Leaving Auto_Adoption because of EXCEPTION: '||sqlerrm, 2);
4523 END IF;
4524 IF l_relationship_csr%ISOPEN THEN
4525 CLOSE l_relationship_csr;
4526 END IF;
4527
4528 x_return_status := G_RET_STS_UNEXP_ERROR ;
4529 END DELETE_AUTO_ADOPTED_RELATIONS;
4530
4531 ---------------------------------------------------------------------------
4532 -- PROCEDURE delete_article_relationship
4533 ---------------------------------------------------------------------------
4534
4535 PROCEDURE delete_article_relationship(
4536 p_api_version IN NUMBER,
4537 p_init_msg_list IN VARCHAR2 ,
4538 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4539
4540 x_return_status OUT NOCOPY VARCHAR2,
4541 x_msg_count OUT NOCOPY NUMBER,
4542 x_msg_data OUT NOCOPY VARCHAR2,
4543
4544 p_source_article_id IN NUMBER,
4545 p_target_article_id IN NUMBER,
4546 p_org_id IN NUMBER,
4547 p_object_version_number IN NUMBER := NULL
4548 ) IS
4549 l_api_version CONSTANT NUMBER := 1;
4550 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_row';
4551 BEGIN
4552
4553 IF (l_debug = 'Y') THEN
4554 okc_debug.log('2200: Entered delete_row', 2);
4555 END IF;
4556
4557 -- Standard Start of API savepoint
4558 SAVEPOINT g_delete_row_GRP;
4559 -- Standard call to check for call compatibility.
4560 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4562 END IF;
4563 -- Initialize message list if p_init_msg_list is set to TRUE.
4564 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4565 FND_MSG_PUB.initialize;
4566 END IF;
4567 -- Initialize API return status to success
4568 x_return_status := FND_API.G_RET_STS_SUCCESS;
4569
4570 --------------------------------------------
4571 -- Calling Simple API for Deleting A Row
4572 --------------------------------------------
4573 IF p_org_id <> G_GLOBAL_ORG_ID THEN
4574 OKC_ARTICLE_RELATIONSHIPS_PVT.Delete_Row(
4575 x_return_status => x_return_status,
4576 p_source_article_id => p_source_article_id,
4577 p_target_article_id => p_target_article_id,
4578 p_org_id => p_org_id,
4579 p_object_version_number => p_object_version_number
4580 );
4581 --------------------------------------------
4582 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4584 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4585 RAISE FND_API.G_EXC_ERROR ;
4586 END IF;
4587 --------------------------------------------
4588 --------------------------------------------
4589 -- Delete the opposite row by flipping the source and target for the relationship
4590 --------------------------------------------
4591 OKC_ARTICLE_RELATIONSHIPS_PVT.Delete_Row(
4592 x_return_status => x_return_status,
4593 p_source_article_id => p_target_article_id,
4594 p_target_article_id => p_source_article_id,
4595 p_org_id => p_org_id,
4596 p_object_version_number => p_object_version_number
4597 );
4598 --------------------------------------------
4599 ELSE
4600 -- For global article relationship deletion delete all similar relationships for all orgs
4601 -- including those adopted naturally or not.
4602
4603 DELETE FROM OKC_ARTICLE_RELATNS_ALL
4604 WHERE SOURCE_ARTICLE_ID = p_source_article_id
4605 AND TARGET_ARTICLE_ID = p_target_article_id;
4606
4607 DELETE FROM OKC_ARTICLE_RELATNS_ALL
4608 WHERE SOURCE_ARTICLE_ID = p_target_article_id
4609 AND TARGET_ARTICLE_ID = p_source_article_id;
4610 /*
4611 DELETE_AUTO_ADOPTED_RELATIONS
4612 (
4613 x_return_status => x_return_status,
4614 x_msg_count => x_msg_data,
4615 x_msg_data => x_msg_data,
4616 p_fetchsize => 100,
4617 p_source_global_article_id => p_source_article_id,
4618 p_target_global_article_id => p_target_article_id
4619 ) ;
4620 */
4621 END IF;
4622 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4624 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4625 RAISE FND_API.G_EXC_ERROR ;
4626 END IF;
4627 -- Standard check of p_commit
4628 IF FND_API.To_Boolean( p_commit ) THEN
4629 COMMIT WORK;
4630 END IF;
4631 -- Standard call to get message count and if count is 1, get message info.
4632 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
4633
4634 IF (l_debug = 'Y') THEN
4635 okc_debug.log('2300: Leaving delete_row', 2);
4636 END IF;
4637
4638 EXCEPTION
4639 WHEN FND_API.G_EXC_ERROR THEN
4640 IF (l_debug = 'Y') THEN
4641 okc_debug.log('2400: Leaving delete_Row: OKC_API.G_EXCEPTION_ERROR Exception', 2);
4642 END IF;
4643 ROLLBACK TO g_delete_row_GRP;
4644 x_return_status := G_RET_STS_ERROR ;
4645 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
4646
4647 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4648 IF (l_debug = 'Y') THEN
4649 okc_debug.log('2500: Leaving delete_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4650 END IF;
4651 ROLLBACK TO g_delete_row_GRP;
4652 x_return_status := G_RET_STS_UNEXP_ERROR ;
4653 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
4654
4655 WHEN OTHERS THEN
4656 IF (l_debug = 'Y') THEN
4657 okc_debug.log('2600: Leaving delete_Row because of EXCEPTION: '||sqlerrm, 2);
4658 END IF;
4659
4660 ROLLBACK TO g_delete_row_GRP;
4661 x_return_status := G_RET_STS_UNEXP_ERROR ;
4662 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4663 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4664 END IF;
4665 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
4666
4667 END delete_article_relationship;
4668
4669 -- Bug#3722445: The following API will be used by the Update Article UI to check if future approved versions exist
4670 -- in which case, the UI will prevent further update to end date.
4671
4672 PROCEDURE later_approved_exists
4673 (
4674 p_api_version IN NUMBER,
4675 p_init_msg_list IN VARCHAR2 ,
4676 p_article_id IN NUMBER,
4677 p_start_date IN DATE,
4678 x_return_status OUT NOCOPY VARCHAR2,
4679 x_msg_count OUT NOCOPY NUMBER,
4680 x_msg_data OUT NOCOPY VARCHAR2,
4681 x_yes_no OUT NOCOPY VARCHAR2)
4682 IS
4683 l_yes_no VARCHAR2(1) := 'N';
4684 l_api_version CONSTANT NUMBER := 1;
4685 l_api_name CONSTANT VARCHAR2(30) := 'g_later_approved_exists';
4686
4687 CURSOR l_highest_version_csr(cp_article_id IN NUMBER,
4688 cp_start_date IN DATE) IS
4689 select
4690 'Y'
4691 from
4692 okc_article_versions av
4693 where
4694 av.article_id = cp_article_id and
4695 av.start_date > cp_start_date and
4696 av.article_status in ( 'APPROVED', 'HOLD') and
4697 rownum < 2;
4698 BEGIN
4699 x_yes_no := 'N';
4700 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4702 END IF;
4703 -- Initialize message list if p_init_msg_list is set to TRUE.
4704 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4705 FND_MSG_PUB.initialize;
4706 END IF;
4707
4708 -- Initialize API return status to success
4709 x_return_status := FND_API.G_RET_STS_SUCCESS;
4710 OPEN l_highest_version_csr(p_article_id, p_start_date);
4711 FETCH l_highest_version_csr INTO l_yes_no ;
4712 IF l_highest_version_csr%NOTFOUND THEN
4713 l_yes_no := 'N';
4714 END IF;
4715 CLOSE l_highest_version_csr;
4716 x_yes_no := l_yes_no;
4717 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_encoded=> 'F', p_data => x_msg_data );
4718 EXCEPTION
4719 WHEN OTHERS THEN
4720 IF l_highest_version_csr%ISOPEN Then
4721 close l_highest_version_csr;
4722 END IF;
4723 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
4724 p_msg_name => G_UNEXPECTED_ERROR,
4725 p_token1 => G_SQLCODE_TOKEN,
4726 p_token1_value => sqlcode,
4727 p_token2 => G_SQLERRM_TOKEN,
4728 p_token2_value => sqlerrm);
4729
4730 x_return_status := G_RET_STS_UNEXP_ERROR ;
4731 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,p_encoded=> 'F', p_data => x_msg_data );
4732
4733 END later_approved_exists;
4734 -- MOAC
4735 /*
4736 BEGIN
4737 OPEN cur_org_csr;
4738 FETCH cur_org_csr INTO G_CURRENT_ORG_ID;
4739 CLOSE cur_org_csr;
4740 */
4741
4742 END OKC_ARTICLES_GRP;