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