[Home] [Help]
PACKAGE BODY: APPS.OKC_ARTWF_PVT
Source
1 package body okc_artwf_pvt as
2 /* $Header: OKCARTWFB.pls 120.11 2010/05/19 10:06:07 harchand ship $ */
3
4 -- get info from versions table
5 cursor cv(cp_org_id in number, cp_version_id in number) is
6 select
7 art.org_id,
8 art.article_id,
9 artv.article_version_id,
10 artv.article_status,
11 artv.adoption_type,
12 artv.global_yn,
13 'AP-'||
14 to_char(art.org_id)||'.'||
15 to_char(art.article_id)||'.'||
16 to_char(artv.article_version_id)||'.'||
17 to_char(artv.object_version_number) ikey,
18 substr(nvl(art.article_number, art.article_title),0,240) ukey,
19 artv.object_version_number+1,
20 art.article_title,
21 artv.article_version_number
22 from
23 okc_articles_all art,
24 okc_article_versions artv
25 where art.article_id = artv.article_id
26 and art.standard_yn = 'Y'
27 and artv.article_status in ('DRAFT','REJECTED')
28 and art.org_id = cp_org_id
29 and artv.article_version_id = cp_version_id
30 and greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)) <> trunc(sysdate); -- bug#3517002
31 /*
32 for update of
33 artv.article_status,
34 artv.object_version_number,
35 artv.last_update_date,
36 artv.last_updated_by
37 nowait;
38 */
39
40 -- get info from adoptions table
41 cursor ca(cp_org_id in number, cp_version_id in number) is
42 select
43 arta.local_org_id,
44 art.article_id,
45 arta.global_article_version_id,
46 arta.adoption_status,
47 arta.adoption_type,
48 artv.global_yn,
49 'AD-'||
50 to_char(arta.local_org_id)||'.'||
51 to_char(art.article_id)||'.'||
52 to_char(artv.article_version_id)||'.'||
53 to_char(arta.object_version_number) ikey,
54 substr(nvl(art.article_number,art.article_title),0,240) ukey,
55 arta.object_version_number+1,
56 art.article_title,
57 artv.article_version_number
58 from
59 okc_articles_all art,
60 okc_article_versions artv,
61 okc_article_adoptions arta
62 where art.article_id = artv.article_id
63 and art.standard_yn = 'Y'
64 and artv.global_yn = 'Y'
65 and arta.adoption_type = 'AVAILABLE'
66 and (arta.adoption_status = 'REJECTED' or arta.adoption_status is null)
67 and arta.local_org_id = cp_org_id
68 and artv.article_version_id = cp_version_id
69 and arta.global_article_version_id = artv.article_version_id
70 and artv.article_status = 'APPROVED'
71 and sysdate <= nvl(artv.end_date,sysdate+1)
72 and not exists
73 (
74 select 1
75 from okc_article_adoptions
76 where global_article_version_id in
77 (select article_version_id
78 from okc_article_versions
79 where article_id = art.article_id)
80 and local_org_id = arta.local_org_id
81 and adoption_type = 'LOCALIZED'
82 union
83 select 1
84 from okc_article_adoptions
85 where global_article_version_id in
86 (select article_version_id
87 from okc_article_versions
88 where article_id = art.article_id)
89 and local_org_id = arta.local_org_id
90 and adoption_type = 'ADOPTED'
91 and article_version_number > artv.article_version_number
92 );
93 /*
94 for update of
95 arta.adoption_type,
96 arta.adoption_status,
97 arta.object_version_number,
98 arta.last_update_date,
99 arta.last_updated_by
100 nowait;
101 */
102
103 type c_rec_type is record (
104 org_id okc_articles_all.org_id%type,
105 article_id okc_articles_all.article_id%type,
106 article_version_id okc_article_versions.article_version_id%type,
107 article_status okc_article_versions.article_status%type,
108 adoption_type okc_article_versions.adoption_type%type,
109 global_yn okc_article_versions.global_yn%type,
110 ikey wf_items.item_key%type,
111 ukey wf_items.user_key%type,
112 ovn number,
113 article_title okc_articles_all.article_title%type,
114 article_version_number okc_article_versions.article_version_number%type
115 );
116
117 type c_tab_type is table of c_rec_type index by binary_integer;
118 c_tab c_tab_type;
119
120 -- write pointer for success table
121 write_ptr binary_integer;
122 -- write pointer for errors table
123 error_ptr binary_integer;
124
125 --!!!
126 -- begin logging procedure declarations
127 g_level_procedure constant number := fnd_log.level_procedure;
128 g_module constant varchar2(250) := 'okc.plsql.okc_artwf_pvt.';
129 l_api_name varchar2(30);
130 -- end logging procedure declarations
131 -- begin logging procedures
132 procedure start_log(api_name in varchar2)
133 is
134 begin
135 l_api_name := api_name;
136 end;
137
138 procedure log(log_str in varchar2)
139 is
140 begin
141 if (g_level_procedure >= fnd_log.g_current_runtime_level) then
142 fnd_log.string( g_level_procedure, g_module||l_api_name, log_str);
143 end if;
144 end;
145 -- end logging procedures
146 --!!!
147
148 -- clean the tables
149 procedure clean
150 is
151 begin
152 write_ptr := 0;
153 error_ptr := 0;
154 rollback;
155 end;
156
157 -- get write pointer for success
158 function get_write_ptr return binary_integer
159 is
160 begin
161 return write_ptr;
162 end;
163
164 -- get write pointer for errors
165 function get_error_ptr return binary_integer
166 is
167 begin
168 return -error_ptr;
169 end;
170
171 -- get write pointer for success
172 procedure get_write_ptr(x_write_ptr out nocopy binary_integer)
173 is
174 begin
175 x_write_ptr := write_ptr;
176 end;
177
178 -- get write pointer for errors
179 procedure get_error_ptr(x_error_ptr out nocopy binary_integer) is
180 begin
181 x_error_ptr := -error_ptr;
182 end;
183
184 -- print success table (for testing - don't use in apps code)
185 procedure print_tab
186 is
187 begin
188 -- dbms_output.put_line('counter='||write_ptr);
189 for i in 1..write_ptr
190 loop
191 -- dbms_output.put_line('org_id('||i||')='||c_tab(i).org_id);
192 -- dbms_output.put_line('article_id('||i||')='||c_tab(i).article_id);
193 -- dbms_output.put_line('article_version_id('||i||')='||c_tab(i).article_version_id);
194 -- dbms_output.put_line('article_status('||i||')='||c_tab(i).article_status);
195 -- dbms_output.put_line('adoption_type('||i||')='||c_tab(i).adoption_type);
196 -- dbms_output.put_line('global_yn('||i||')='||c_tab(i).global_yn);
197 -- dbms_output.put_line('key('||i||')='||c_tab(i).key);
198 null;
199 end loop;
200 end;
201
202 -- print errors table (for testing - don't use in apps code)
203 procedure print_err
204 is
205 begin
206 -- dbms_output.put_line('counter='||-error_ptr);
207 for i in 1..-error_ptr
208 loop
209 -- dbms_output.put_line('org_id('||i||')='||c_tab(-i).org_id);
210 -- dbms_output.put_line('article_id('||i||')='||c_tab(-i).article_id);
211 -- dbms_output.put_line('article_version_id('||i||')='||c_tab(-i).article_version_id);
212 -- dbms_output.put_line('article_status('||i||')='||c_tab(-i).article_status);
213 -- dbms_output.put_line('adoption_type('||i||')='||c_tab(-i).adoption_type);
214 -- dbms_output.put_line('global_yn('||i||')='||c_tab(-i).global_yn);
215 -- dbms_output.put_line('key('||i||')='||c_tab(-i).key);
216 null;
217 end loop;
218 end;
219
220 function get_display_name(userid in varchar2)
221 return varchar2
222 is
223 result wf_users.display_name%type;
224 cursor disp_name(id in varchar2) is
225 select nvl(display_name, name)
226 from wf_users
227 where name = nvl(id, 'WFADMIN');
228 begin
229 open disp_name(userid);
230 fetch disp_name into result;
231 close disp_name;
232 return result;
233 exception when others then
234 return 'WFADMIN';
235 end;
236
237
238 procedure start_wf_after_import( p_req_id in number,
239 p_batch_number in varchar2,
240 p_org_id in varchar2)
241 is
242 --pragma autonomous_transaction;
243 -- get info from versions table after import
244 cursor cv_import(c_req_id in number, c_batch_num varchar2, c_org_id number) is
245 select
246 art.org_id,
247 art.article_id,
248 artv.article_version_id,
249 artv.article_status,
250 artv.adoption_type,
251 artv.global_yn,
252 'AI-'||
253 to_char(art.org_id)||'.'||
254 to_char(art.article_id)||'.'||
255 to_char(artv.article_version_id)||'.'||
256 to_char(artv.object_version_number) ikey,
257 substr(nvl(art.article_number,art.article_title),0,240) ukey,
258 artv.object_version_number
259 from
260 okc_articles_all art,
261 okc_article_versions artv,
262 okc_art_interface_all int
263 where art.article_id = artv.article_id
264 and art.standard_yn = 'Y'
265 and artv.article_status in ('PENDING_APPROVAL','APPROVED')
266 and art.article_title = int.article_title
267 and int.process_status in ('W', 'S') -- this process status tells
268 -- that the article has been imported
269 and int.request_id = c_req_id -- in order to find articles imported
270 -- in this process only
271 and int.batch_number = c_batch_num -- added for performance
272 and int.org_id = c_org_id -- added for performance
273 and int.article_status in ('PENDING_APPROVAL', 'APPROVED')
274 and artv.article_version_number = int.article_version_number;
275
276 save_threshold WF_ENGINE.threshold%TYPE;
277 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
278 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
279 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
280 j NUMBER ;
281
282 begin
283 clean;
284 j:=1;
285
286 save_threshold := WF_ENGINE.threshold;
287
288 WF_ENGINE.threshold := -1;
289 for cv_imp_rec in cv_import(p_req_id, p_batch_number, p_org_id)
290 loop
291 begin
292 wf_engine.CreateProcess( 'OKCARTAP', cv_imp_rec.ikey, 'ARTICLES_AFTER_IMPORT_PROC');
293 wf_engine.SetItemUserKey( 'OKCARTAP', cv_imp_rec.ikey, cv_imp_rec.ukey);
294 wf_engine.SetItemOwner( 'OKCARTAP', cv_imp_rec.ikey, fnd_global.user_name);
295 wf_engine.SetItemAttrNumber( 'OKCARTAP', cv_imp_rec.ikey, 'USER_ID', fnd_global.user_id);
296 wf_engine.SetItemAttrNumber( 'OKCARTAP', cv_imp_rec.ikey, 'RESP_ID', fnd_global.resp_id);
297 wf_engine.SetItemAttrNumber( 'OKCARTAP', cv_imp_rec.ikey, 'RESP_APPL_ID', fnd_global.resp_appl_id);
298 wf_engine.SetItemAttrNumber( 'OKCARTAP', cv_imp_rec.ikey, 'ORG_ID', cv_imp_rec.org_id);
299 wf_engine.SetItemAttrNumber( 'OKCARTAP', cv_imp_rec.ikey, 'ARTICLE_ID', cv_imp_rec.article_id);
300 wf_engine.SetItemAttrNumber( 'OKCARTAP', cv_imp_rec.ikey, 'ARTICLE_VERSION_ID', cv_imp_rec.article_version_id);
301 wf_engine.SetItemAttrText( 'OKCARTAP', cv_imp_rec.ikey, 'ARTICLE_STATUS', cv_imp_rec.article_status);
302 wf_engine.SetItemAttrText( 'OKCARTAP', cv_imp_rec.ikey, 'ADOPTION_TYPE', cv_imp_rec.adoption_type);
303 wf_engine.SetItemAttrText( 'OKCARTAP', cv_imp_rec.ikey, 'GLOBAL_YN', cv_imp_rec.global_yn);
304 wf_engine.SetItemAttrText( 'OKCARTAP', cv_imp_rec.ikey, 'REQUESTOR', fnd_global.user_name);
305 wf_engine.SetItemAttrText( 'OKCARTAP', cv_imp_rec.ikey, 'REQUESTOR_DISPLAY_NAME', get_display_name(fnd_global.user_name));
306 wf_engine.StartProcess('OKCARTAP' , cv_imp_rec.ikey);
307
308 IF j = 500 THEN
309 commit;
310 j:=0;
311 ELSE
312 j:=j+1;
313 END IF;
314 exception
315 when others then
316 c_tab(error_ptr-1).org_id := cv_imp_rec.org_id;
317 c_tab(error_ptr-1).article_id := cv_imp_rec.article_id;
318 c_tab(error_ptr-1).article_version_id := cv_imp_rec.article_version_id;
319 c_tab(error_ptr-1).article_status := cv_imp_rec.article_status;
320 c_tab(error_ptr-1).adoption_type := cv_imp_rec.adoption_type;
321 c_tab(error_ptr-1).global_yn := cv_imp_rec.global_yn;
322 c_tab(error_ptr-1).ikey := 'OKC_WF_AFTER_IMPORT_ERROR';
323 error_ptr := error_ptr-1;
324 Okc_Api.Set_Message(p_app_name => 'OKC',
325 p_msg_name => G_UNEXPECTED_ERROR,
326 p_token1 => G_SQLCODE_TOKEN,
327 p_token1_value => sqlcode,
328 p_token2 => G_SQLERRM_TOKEN,
329 p_token2_value => sqlerrm);
330 end;
331 end loop;
332 commit;
333 WF_ENGINE.threshold := save_threshold;
334 end;
335
336 -- -----------------------------------------------------------------------------
337 -- Function get_intent
338 -- reads article_id workflow attribute and returns article intent ('S' for Sell
339 -- or 'B' for Buy) for the given article_id
340 -- Input:
341 -- itemtype - workflow item type
342 -- item key - workflow item key
343 -- Output:
344 -- returns article_intent
345 -- -----------------------------------------------------------------------------
346 function get_intent(itemtype in varchar2, itemkey in varchar2) return varchar2
347 is
348 art_id okc_articles_all.article_id%type;
349 art_intent okc_articles_all.article_intent%type;
350 begin
351 art_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'ARTICLE_ID', false);
352 select article_intent into art_intent
353 from okc_articles_all
354 where article_id = art_id;
355 return art_intent;
356 exception
357 when others then
358 return null;
359 end;
360
361 -- -----------------------------------------------------------------------------
362 -- Function get_intent
363 -- returns article intent ('S' for Sell or 'B' for Buy) for the given article_id
364 -- Input:
365 -- art_id - article_id
366 -- Output:
367 -- returns article_intent
368 -- -----------------------------------------------------------------------------
369 function get_intent(art_id in number) return varchar2
370 is
371 art_intent okc_articles_all.article_intent%type;
372 begin
373 select article_intent into art_intent
374 from okc_articles_all
375 where article_id = art_id;
376 return art_intent;
377 exception
378 when others then
379 return null;
380 end;
381
382 -- -----------------------------------------------------------------------------
383 -- Function get_intent
384 -- returns article intent ('S' for Sell or 'B' for Buy) for the given article_version_id
385 -- Input:
386 -- art_ver_id - article_version_id
387 -- Output:
388 -- returns article_intent
389 -- -----------------------------------------------------------------------------
390 function get_intent_pub(art_ver_id in number) return varchar2
391 is
392 art_intent okc_articles_all.article_intent%type;
393 begin
394 select article_intent into art_intent
395 from okc_articles_all a, okc_article_versions v
396 where a.article_id = v.article_id
397 and v.article_version_id = art_ver_id;
398 return art_intent;
399 exception
400 when others then
401 return null;
402 end;
403
404 -- -----------------------------------------------------------------------------
405 -- Function get_approver
406 -- returns approver's username for the given organization id and article intent
407 -- Input:
408 -- org_id - organization id
409 -- art_intent - article intent ('S' or 'B')
410 -- Output:
411 -- returns approver's username
412 -- -----------------------------------------------------------------------------
413 function get_approver(org_id in number, art_intent in varchar2) return varchar2
414 is
415 cursor c_approvers(p_org_id in number, p_art_intent in varchar2) is
416 select decode(p_art_intent, 'S', org_information2, 'B', org_information6, 'SYSADMIN')
417 from hr_organization_information
418 where organization_id = p_org_id
419 and org_information_context = 'OKC_TERMS_LIBRARY_DETAILS';
420 result hr_organization_information.org_information2%type;
421 begin
422 open c_approvers(org_id, art_intent);
423 fetch c_approvers into result;
424 close c_approvers;
425 return result;
426 exception
427 when others then
428 close c_approvers;
429 return null;
430 end;
431
432 -- check status of the version and put result into success/errors table
433 procedure check_status( p_org_id in number,
434 p_article_version_id in number,
435 x_result out nocopy varchar2,
436 x_msg_count out nocopy number,
437 x_msg_data out nocopy varchar2
438 )
439 is
440 pragma autonomous_transaction;
441 e_check_1 exception;
442 e_check_2 exception;
443 function ikey_exists(p_ikey in varchar2) return boolean
444 as
445 result varchar2(1);
446 begin
447 select 'Y' into result FROM wf_items_v
448 where item_type = 'OKCARTAP'
449 and item_key = p_ikey;
450 return true; -- found
451 exception
452 when others then return false; -- not found
453 end;
454 begin
455 -- x_result := 'NOK';
456 begin
457 open cv(p_org_id, p_article_version_id);
458 fetch cv into c_tab(write_ptr+1);
459 if cv%notfound then raise e_check_1;
460 else write_ptr := write_ptr+1;
461 end if;
462 /*
463 update okc_article_versions
464 set
465 article_status = 'PENDING_APPROVAL',
466 object_version_number = c_tab(write_ptr).ovn,
467 last_update_date = sysdate,
468 last_updated_by = fnd_global.user_id
469 where current of cv;
470 */
471 close cv;
472 -- to be sure that there is no such item_key in workflow
473 if ikey_exists(c_tab(write_ptr).ikey) then
474 write_ptr := write_ptr-1;
475 raise e_check_1; -- raise exception if exists
476 end if;
477 --
478 --
479 if get_approver(c_tab(write_ptr).org_id, get_intent(c_tab(write_ptr).article_id)) is null then
480 write_ptr := write_ptr-1;
481 raise e_check_1;
482 end if;
483 --
484 OKC_ARTICLE_STATUS_CHANGE_PVT.pending_approval(
485 p_api_version => 1.0,
486 p_init_msg_list => FND_API.G_TRUE,
487 x_return_status => x_result,
488 x_msg_count => x_msg_count,
489 x_msg_data => x_msg_data,
490 p_current_org_id => p_org_id,
491 p_adopt_as_is_yn => 'N',
492 p_article_version_id => p_article_version_id,
493 p_article_title => c_tab(write_ptr).article_title,
494 p_article_version_number => c_tab(write_ptr).article_version_number
495 );
496 if x_result = fnd_api.G_RET_STS_UNEXP_ERROR
497 or
498 x_result = fnd_api.G_RET_STS_ERROR then
499 raise fnd_api.G_EXC_ERROR;
500 end if;
501 --
502 x_result := fnd_api.G_RET_STS_SUCCESS;
503 commit;
504 return;
505
506 exception
507 when e_check_1 then
508 c_tab(error_ptr-1).org_id := p_org_id;
509 c_tab(error_ptr-1).article_id := null;
510 c_tab(error_ptr-1).article_version_id := p_article_version_id;
511 c_tab(error_ptr-1).article_status := 'APPROVAL_ERROR';
512 c_tab(error_ptr-1).adoption_type := null;
513 c_tab(error_ptr-1).global_yn := null;
514 c_tab(error_ptr-1).ikey := 'OKC_ARTICLE_WRONG_STATUS4APPROVAL';
515 error_ptr := error_ptr-1;
516 close cv;
517 when fnd_api.G_EXC_ERROR then
518 c_tab(error_ptr-1).org_id := p_org_id;
519 c_tab(error_ptr-1).article_id := null;
520 c_tab(error_ptr-1).article_version_id := p_article_version_id;
521 c_tab(error_ptr-1).article_status := 'APPROVAL_ERROR';
522 c_tab(error_ptr-1).adoption_type := null;
523 c_tab(error_ptr-1).global_yn := null;
524 c_tab(error_ptr-1).ikey := 'OKC_ARTICLE_STATUS_PENDING_APPROVAL';
525 error_ptr := error_ptr-1;
526 rollback;
527 when others then
528 c_tab(error_ptr-1).org_id := p_org_id;
529 c_tab(error_ptr-1).article_id := null;
530 c_tab(error_ptr-1).article_version_id := p_article_version_id;
531 c_tab(error_ptr-1).article_status := 'APPROVAL_ERROR';
532 c_tab(error_ptr-1).adoption_type := null;
533 c_tab(error_ptr-1).global_yn := null;
534 c_tab(error_ptr-1).ikey := 'OKC_ARTICLE_UNEXPECTED';
535 error_ptr := error_ptr-1;
536 close cv;
537 end;
538 begin
539 open ca(p_org_id, p_article_version_id);
540 fetch ca into c_tab(write_ptr+1);
541 if ca%notfound then raise e_check_2;
542 else write_ptr := write_ptr+1;
543 end if;
544 /*
545 update okc_article_adoptions
546 set
547 adoption_type = 'ADOPTED',
548 adoption_status = 'PENDING_APPROVAL',
549 object_version_number = c_tab(write_ptr).ovn,
550 last_update_date = sysdate,
551 last_updated_by = fnd_global.user_id
552 where current of ca;
553 */
554 close ca;
555 -- to be sure that there is no such item_key in workflow
556 if ikey_exists(c_tab(write_ptr).ikey) then
557 write_ptr := write_ptr-1;
558 raise e_check_2; -- raise exception if exists
559 end if;
560 --
561 --
562 if get_approver(c_tab(write_ptr).org_id, get_intent(c_tab(write_ptr).article_id)) is null then
563 write_ptr := write_ptr-1;
564 raise e_check_2;
565 end if;
566 --
567 OKC_ARTICLE_STATUS_CHANGE_PVT.pending_approval(
568 p_api_version => 1.0,
569 p_init_msg_list => FND_API.G_TRUE,
570 x_return_status => x_result,
571 x_msg_count => x_msg_count,
572 x_msg_data => x_msg_data,
573 p_current_org_id => p_org_id,
574 p_adopt_as_is_yn => 'Y',
575 p_article_version_id => p_article_version_id,
576 p_article_title => c_tab(write_ptr).article_title,
577 p_article_version_number => c_tab(write_ptr).article_version_number
578 );
579 if x_result = fnd_api.G_RET_STS_UNEXP_ERROR
580 or
581 x_result = fnd_api.G_RET_STS_ERROR then
582 raise fnd_api.G_EXC_ERROR;
583 end if;
584 --
585 x_result := fnd_api.G_RET_STS_SUCCESS;
586 commit;
587 return;
588
589 exception
590 when e_check_2 then
591 c_tab(error_ptr-1).org_id := p_org_id;
592 c_tab(error_ptr-1).article_id := null;
593 c_tab(error_ptr-1).article_version_id := p_article_version_id;
594 c_tab(error_ptr-1).article_status := 'ADOPTION_ERROR';
595 c_tab(error_ptr-1).adoption_type := null;
596 c_tab(error_ptr-1).global_yn := null;
597 c_tab(error_ptr-1).ikey := 'OKC_ARTICLE_WRONG_STATUS4ADOPTION';
598 error_ptr := error_ptr-1;
599 close ca;
600 x_result := fnd_api.G_RET_STS_ERROR;
601 when fnd_api.G_EXC_ERROR then
602 c_tab(error_ptr-1).org_id := p_org_id;
603 c_tab(error_ptr-1).article_id := null;
604 c_tab(error_ptr-1).article_version_id := p_article_version_id;
605 c_tab(error_ptr-1).article_status := 'ADOPTION_ERROR';
606 c_tab(error_ptr-1).adoption_type := null;
607 c_tab(error_ptr-1).global_yn := null;
608 c_tab(error_ptr-1).ikey := 'OKC_ARTICLE_STATUS_PENDING_APPROVAL';
609 error_ptr := error_ptr-1;
610 rollback;
611 when others then
612 c_tab(error_ptr-1).org_id := p_org_id;
613 c_tab(error_ptr-1).article_id := null;
614 c_tab(error_ptr-1).article_version_id := p_article_version_id;
615 c_tab(error_ptr-1).article_status := 'ADOPTION_ERROR';
616 c_tab(error_ptr-1).adoption_type := null;
617 c_tab(error_ptr-1).global_yn := null;
618 c_tab(error_ptr-1).ikey := 'OKC_ARTICLE_UNEXPECTED_ERROR';
619 error_ptr := error_ptr-1;
620 close ca;
621 x_result := fnd_api.G_RET_STS_ERROR;
622 end;
623 end;
624
625 -- check the version and print result (for testing - don't use in apps code)
626 procedure test( p_org_id in number,
627 p_article_version_id in number)
628 is
629 x_result varchar(1);
630 x_msg_count number;
631 x_msg_data varchar2(2000);
632 begin
633 check_status(p_org_id, p_article_version_id, x_result, x_msg_count, x_msg_data);
634 -- dbms_output.put_line('result='||x_result);
635 rollback;
636 end;
637
638 -- get record (p_ptr) from success table
639 procedure get_tab( p_ptr in binary_integer,
640 x_article_id out nocopy okc_articles_all.article_id%type,
641 x_article_version_id out nocopy okc_article_versions.article_version_id%type,
642 x_article_status out nocopy okc_article_versions.article_status%type,
643 x_adoption_type out nocopy okc_article_versions.adoption_type%type,
644 x_global_yn out nocopy okc_article_versions.global_yn%type,
645 x_key out nocopy varchar2)
646 is
647 begin
648 if write_ptr > 0 then
649 if p_ptr > 0 and p_ptr <= write_ptr then
650 x_article_id := c_tab(p_ptr).article_id;
651 x_article_version_id := c_tab(p_ptr).article_version_id;
652 x_article_status := c_tab(p_ptr).article_status;
653 x_adoption_type := c_tab(p_ptr).adoption_type;
654 x_global_yn := c_tab(p_ptr).global_yn;
655 x_key := c_tab(p_ptr).ikey;
656 return;
657 end if;
658 end if;
659 x_article_id := null;
660 x_article_version_id := null;
661 end;
662
663 -- get record (p_ptr) from errors table
664 procedure get_err( p_ptr in binary_integer,
665 x_article_id out nocopy okc_articles_all.article_id%type,
666 x_article_version_id out nocopy okc_article_versions.article_version_id%type,
667 x_article_status out nocopy okc_article_versions.article_status%type,
668 x_adoption_type out nocopy okc_article_versions.adoption_type%type,
669 x_global_yn out nocopy okc_article_versions.global_yn%type,
670 x_key out nocopy varchar2)
671 is
672 begin
673 if error_ptr < 0 then
674 if p_ptr > 0 and p_ptr <= -error_ptr then
675 x_article_id := c_tab(-p_ptr).article_id;
676 x_article_version_id := c_tab(-p_ptr).article_version_id;
677 x_article_status := c_tab(-p_ptr).article_status;
678 x_adoption_type := c_tab(-p_ptr).adoption_type;
679 x_global_yn := c_tab(-p_ptr).global_yn;
680 x_key := c_tab(-p_ptr).ikey;
681 return;
682 end if;
683 end if;
684 x_article_id := null;
685 x_article_version_id := null;
686 end;
687
688 -- -----------------------------------------------------------------------------
689 -- Procedure set_notified_list
690 -- builds list of notified usernames (administrators) for sending notifications
691 -- about articles autoadoption or availability for adoption
692 -- Input:
693 -- itemtype - workflow item type
694 -- itemkey - workflow item key
695 -- actid - workflow action id
696 -- funcmode - workflow function mode
697 -- Output:
698 -- resultout - workflow result
699 -- list of usernames to notify
700 -- -----------------------------------------------------------------------------
701 procedure set_notified_list( itemtype in varchar2,
702 itemkey in varchar2,
703 actid in number,
704 funcmode in varchar2,
705 resultout out nocopy varchar2)
706 is
707 artv_id number;
708
709 cursor c_notified(p_artv_id in number, p_art_intent in varchar2) is
710 select
711 adoption_type,
712 hr.organization_id,
713 decode(p_art_intent, 'S', org_information3, 'B', org_information7,
714 'SYSADMIN') notified
715 from
716 okc_article_adoptions arta,
717 hr_organization_information hri,
718 hr_organization_units hr
719 where
720 global_article_version_id = p_artv_id
721 and
722 hri.organization_id = local_org_id
723 and
724 hr.organization_id = local_org_id
725 and
726 org_information_context = 'OKC_TERMS_LIBRARY_DETAILS';
727
728 operation Wf_Engine.NameTabTyp;
729 operation_list Wf_Engine.TextTabTyp;
730 organization Wf_Engine.NameTabTyp;
731 organization_list Wf_Engine.NumTabTyp;
732 notified Wf_Engine.NameTabTyp;
733 notified_list Wf_Engine.TextTabTyp;
734
735 art_intent varchar2(1);
736 counter number;
737 begin
738 counter := 0;
739 art_intent := get_intent(itemtype, itemkey);
740 artv_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'ARTICLE_VERSION_ID', false);
741 if ( funcmode = 'RUN' ) then
742 for c_rec in c_notified(artv_id, art_intent)
743 loop
744 counter := counter+1;
745 operation(counter):= 'OPERATION_LIST$'||counter;
746 operation_list(counter):= c_rec.adoption_type;
747 organization(counter):= 'ORGANIZATION_LIST$'||counter;
748 organization_list(counter):= c_rec.organization_id;
749 notified(counter):= 'NOTIFIED_LIST$'||counter;
750 notified_list(counter):= c_rec.notified;
751 end loop;
752 wf_engine.AddItemAttrTextArray( itemtype, itemkey, operation, operation_list);
753 wf_engine.AddItemAttrNumberArray( itemtype, itemkey, organization, organization_list);
754 wf_engine.AddItemAttrTextArray( itemtype, itemkey, notified, notified_list);
755 wf_engine.AddItemAttr(itemtype, itemkey, 'COUNTER$', null, counter, null);
756 resultout := 'COMPLETE';
757 return;
758 end if;
759 exception
760 when others then
761 WF_CORE.CONTEXT ( 'OKC_ARTWF_PVT', 'set_notified_list', itemtype,
762 itemkey, to_char(actid), funcmode);
763 raise;
764 end;
765
766 --
767 -- decrement counter
768 -- called from wf - decrements COUNTER$
769 --
770 procedure decrement_counter( itemtype in varchar2,
771 itemkey in varchar2,
772 actid in number,
773 funcmode in varchar2,
774 resultout out nocopy varchar2)
775 is
776 counter number;
777 begin
778 counter := wf_engine.getItemAttrNumber(itemtype, itemkey, 'COUNTER$', false) - 1;
779 if counter > 0 then
780 wf_engine.setItemAttrNumber(itemtype, itemkey, 'COUNTER$', counter);
781 resultout := 'COMPLETE:T';
782 else
783 resultout := 'COMPLETE:F';
784 end if;
785 return;
786 exception
787 when others then
788 WF_CORE.CONTEXT ( 'OKC_ARTWF_PVT', 'decrement_counter', itemtype,
789 itemkey, to_char(actid), funcmode);
790 raise;
791 end;
792
793 --
794 -- set notified's username
795 -- called from wf - sets notified's username to NOTIFIED$
796 --
797 procedure set_notified( itemtype in varchar2,
798 itemkey in varchar2,
799 actid in number,
800 funcmode in varchar2,
801 resultout out nocopy varchar2)
802 is
803 operation okc_article_adoptions.adoption_type%type;
804 organization hr_organization_units_v.organization_id%type;
805 notified hr_organization_information.org_information3%type;
806 counter number;
807
808 --Fix for 6237128.Set null for notified when user is end-dated
809
810 CURSOR c_usr (notified_usr in hr_organization_information.org_information3%TYPE) IS
811 SELECT user_name
812 FROM fnd_user
813 WHERE user_name = notified_usr
814 AND nvl(end_date, sysdate + 1) > sysdate;
815 valid_notified FND_USER.user_name%TYPE;
816 --end of fix
817
818 begin
819 counter := wf_engine.getItemAttrNumber(itemtype, itemkey, 'COUNTER$', false);
820 if counter > 0 then
821 operation := wf_engine.getItemAttrText(itemtype, itemkey, 'OPERATION_LIST$'||counter, false);
822 organization := wf_engine.getItemAttrNumber(itemtype, itemkey, 'ORGANIZATION_LIST$'||counter, false);
823 notified := wf_engine.getItemAttrText(itemtype, itemkey, 'NOTIFIED_LIST$'||counter, false);
824 wf_engine.setItemAttrText(itemtype, itemkey, 'OPERATION$', operation);
825 wf_engine.setItemAttrNumber(itemtype, itemkey, 'ORGANIZATION$', organization);
826
827 --Fix for 6237128.
828 valid_notified := NULL;
829 OPEN c_usr(notified);
830 FETCH c_usr INTO valid_notified;
831 IF c_usr%NOTFOUND THEN
832 notified := NULL;
833 END IF;
834 CLOSE c_usr;
835 --end of fix
836
837 wf_engine.setItemAttrText(itemtype, itemkey, 'NOTIFIED$', notified);
838 -- bug 3185684
839 fnd_message.set_name('OKC', 'OKC_ART_UNDEFINED_ADMIN');
840 fnd_message.set_token('ORGNAME', organization);
841 wf_engine.setItemAttrText(itemtype, itemkey, 'WARNING', fnd_message.get);
842 wf_engine.setItemAttrText(itemtype, itemkey, 'WF_ADMINISTRATOR', 'SYSADMIN');
843
844 resultout := 'COMPLETE:'||operation;
845 else
846 resultout := 'COMPLETE:UNDEFINED';
847 end if;
848 return;
849 exception
850 when others then
851 WF_CORE.CONTEXT ( 'OKC_ARTWF_PVT', 'set_notified', itemtype,
852 itemkey, to_char(actid), funcmode);
853 raise;
854 end;
855
856 -- -----------------------------------------------------------------------------
857 -- Procedure set_approver
858 -- sets approver's username for sending approval request
859 -- Input:
860 -- itemtype - workflow item type
861 -- itemkey - workflow item key
862 -- actid - workflow action id
863 -- funcmode - workflow function mode
864 -- Output:
865 -- resultout - workflow result
866 -- populates workflow attribute approver
867 -- -----------------------------------------------------------------------------
868 procedure set_approver( itemtype in varchar2,
869 itemkey in varchar2,
870 actid in number,
871 funcmode in varchar2,
872 resultout out nocopy varchar2)
873 is
874 org_id number;
875 art_intent varchar2(1);
876 approver_role wf_users.name%type;
877 approver_name wf_users.display_name%type;
878 begin
879
880 org_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'ORG_ID', false);
881 if ( funcmode = 'RUN' ) then
882 art_intent := get_intent(itemtype, itemkey);
883 approver_role := get_approver(org_id, art_intent);
884 begin
885 select nvl(display_name, name) into approver_name
886 from wf_users where name = approver_role;
887 exception
888 when others then
889 approver_name := null;
890 end;
891 wf_engine.SetItemAttrText( itemtype, itemkey, 'APPROVER_ROLE', approver_role);
892 wf_engine.SetItemAttrText( itemtype, itemkey, 'APPROVER_DISPLAY_NAME', approver_name);
893 resultout := 'COMPLETE';
894 return;
895 end if;
896 exception
897 when others then
898 WF_CORE.CONTEXT ('OKC_ARTWF_PVT', 'set_approver', itemtype,
899 itemkey, to_char(actid), funcmode);
900 raise;
901 end;
902
903 function get_org_name(p_org_id in number) return varchar2;
904 -- set notification attributes
905 procedure set_notification( itemtype in varchar2,
906 itemkey in varchar2,
907 actid in number,
908 funcmode in varchar2,
909 resultout out nocopy varchar2)
910 is
911 message_code fnd_new_messages.message_name%type;
912 org_id okc_articles_all.org_id%type;
913 article_id okc_articles_all.article_id%type;
914 article_version_id okc_article_versions.article_version_id%type;
915
916 cursor c_approve(cp_org_id in number, cp_art_ver_id in number) is
917 select
918 art.article_title,
919 art.article_number,
920 artv.article_version_number,
921 artv.article_description,
922 tm.meaning type_meaning,
923 im.meaning intent_meaning,
924 gm.meaning global_meaning,
925 pm.meaning provision_meaning,
926 artv.start_date,
927 artv.end_date
928 from
929 okc_articles_all art,
930 okc_article_versions artv,
931 okc_lookups_v tm,
932 okc_lookups_v im,
933 okc_lookups_v gm,
934 okc_lookups_v pm
935 where art.standard_yn = 'Y'
936 and art.org_id = cp_org_id
937 and article_version_id = cp_art_ver_id
938 and art.article_id = artv.article_id
939 and tm.lookup_type ='OKC_SUBJECT'
940 and im.lookup_type ='OKC_ARTICLE_INTENT'
941 and gm.lookup_type ='OKC_YN'
942 and pm.lookup_type ='OKC_YN'
943 and tm.lookup_code = art.article_type
944 and im.lookup_code = art.article_intent
945 and gm.lookup_code = artv.global_yn
946 and pm.lookup_code = artv.provision_yn;
947
948 cursor c_adopt(cp_org_id in number, cp_art_ver_id in number) is
949 select
950 art.article_title,
951 art.article_number,
952 artv.article_version_number,
953 artv.article_description,
954 tm.meaning type_meaning,
955 im.meaning intent_meaning,
956 gm.meaning global_meaning,
957 pm.meaning provision_meaning,
958 artv.start_date,
959 artv.end_date
960 from
961 okc_articles_all art,
962 okc_article_versions artv,
963 okc_article_adoptions arta,
964 okc_lookups_v tm,
965 okc_lookups_v im,
966 okc_lookups_v gm,
967 okc_lookups_v pm
968 where art.standard_yn = 'Y'
969 and artv.global_yn = 'Y'
970 and arta.local_org_id = cp_org_id
971 and article_version_id = cp_art_ver_id
972 and art.article_id = artv.article_id
973 and artv.article_version_id = arta.global_article_version_id
974 and tm.lookup_type ='OKC_SUBJECT'
975 and im.lookup_type ='OKC_ARTICLE_INTENT'
976 and gm.lookup_type ='OKC_YN'
977 and pm.lookup_type ='OKC_YN'
978 and tm.lookup_code = art.article_type
979 and im.lookup_code = art.article_intent
980 and gm.lookup_code = artv.global_yn
981 and pm.lookup_code = artv.provision_yn;
982
983 approver_name wf_users.display_name%type;
984
985 begin
986 if ( funcmode = 'RUN' ) then
987 message_code := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'MESSAGE_CODE', false);
988 org_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'ORG_ID', false);
989 article_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'ARTICLE_ID', false);
990 article_version_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'ARTICLE_VERSION_ID', false);
991 wf_engine.SetItemAttrText(itemtype, itemkey, 'SUBJECT', message_code);
992 wf_engine.SetItemAttrText(itemtype, itemkey, 'ORGANIZATION_NAME', get_org_name(org_id));
993
994 if message_code in (
995 'OKC_ART_ADOPTION_NTF_SUBJECT',
996 'OKC_ART_ADOPTION_NTF_SUBJECT_A',
997 'OKC_ART_ADOPTION_NTF_SUBJECT_R'
998 ) then
999 wf_engine.SetItemAttrText(itemtype, itemkey, 'FWK_FUNCTION_NAME', 'OKC_ART_ADOPTION_NTF_DETAILS');
1000 for c_adopt_rec in c_adopt(org_id, article_version_id)
1001 loop
1002 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_TITLE', c_adopt_rec.article_title);
1003 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_NUMBER', c_adopt_rec.article_number);
1004 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'ARTICLE_VERSION_NUMBER', c_adopt_rec.article_version_number);
1005 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_DESCRIPTION', c_adopt_rec.article_description);
1006 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_TYPE', c_adopt_rec.type_meaning);
1007 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_INTENT', c_adopt_rec.intent_meaning);
1008 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_GLOBAL_YN', c_adopt_rec.global_meaning);
1009 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_PROVISION_YN', c_adopt_rec.provision_meaning);
1010 wf_engine.SetItemAttrDate(itemtype, itemkey, 'ARTICLE_START_DATE', c_adopt_rec.start_date);
1011 wf_engine.SetItemAttrDate(itemtype, itemkey, 'ARTICLE_END_DATE', c_adopt_rec.end_date);
1012 end loop;
1013 resultout := 'COMPLETE';
1014
1015 elsif message_code in (
1016 'OKC_ART_APPROVAL_NTF_SUBJECT',
1017 'OKC_ART_APPROVAL_NTF_SUBJECT_A',
1018 'OKC_ART_APPROVAL_NTF_SUBJECT_R'
1019 ) then
1020 wf_engine.SetItemAttrText(itemtype, itemkey, 'FWK_FUNCTION_NAME', 'OKC_ART_APPROVAL_NTF_DETAILS');
1021 for c_approve_rec in c_approve(org_id, article_version_id)
1022 loop
1023 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_TITLE', c_approve_rec.article_title);
1024 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_NUMBER', c_approve_rec.article_number);
1025 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'ARTICLE_VERSION_NUMBER', c_approve_rec.article_version_number);
1026 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_DESCRIPTION', c_approve_rec.article_description);
1027 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_TYPE', c_approve_rec.type_meaning);
1028 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_INTENT', c_approve_rec.intent_meaning);
1029 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_GLOBAL_YN', c_approve_rec.global_meaning);
1030 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_PROVISION_YN', c_approve_rec.provision_meaning);
1031 wf_engine.SetItemAttrDate(itemtype, itemkey, 'ARTICLE_START_DATE', c_approve_rec.start_date);
1032 wf_engine.SetItemAttrDate(itemtype, itemkey, 'ARTICLE_END_DATE', c_approve_rec.end_date);
1033 end loop;
1034 resultout := 'COMPLETE';
1035 elsif message_code in (
1036 'OKC_ART_ADOPTED_NTF_SUBJECT',
1037 'OKC_ART_AVAILABLE_NTF_SUBJECT'
1038 ) then
1039 wf_engine.SetItemAttrText(itemtype, itemkey, 'FWK_FUNCTION_NAME', 'OKC_ART_APPROVAL_NTF_DETAILS');
1040 for c_approve_rec in c_approve(org_id, article_version_id)
1041 loop
1042 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_TITLE', c_approve_rec.article_title);
1043 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_NUMBER', c_approve_rec.article_number);
1044 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'ARTICLE_VERSION_NUMBER', c_approve_rec.article_version_number);
1045 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_DESCRIPTION', c_approve_rec.article_description);
1046 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_TYPE', c_approve_rec.type_meaning);
1047 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_INTENT', c_approve_rec.intent_meaning);
1048 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_GLOBAL_YN', c_approve_rec.global_meaning);
1049 wf_engine.SetItemAttrText(itemtype, itemkey, 'ARTICLE_PROVISION_YN', c_approve_rec.provision_meaning);
1050 wf_engine.SetItemAttrDate(itemtype, itemkey, 'ARTICLE_START_DATE', c_approve_rec.start_date);
1051 wf_engine.SetItemAttrDate(itemtype, itemkey, 'ARTICLE_END_DATE', c_approve_rec.end_date);
1052 end loop;
1053 resultout := 'COMPLETE';
1054 end if;
1055 return;
1056 end if;
1057 exception
1058 when others then
1059 WF_CORE.CONTEXT ('OKC_ARTWF_PVT', 'set_notification', itemtype,
1060 itemkey, to_char(actid), funcmode);
1061 raise;
1062 end;
1063
1064 function get_pending_meaning return varchar2
1065 is
1066 cursor c_pmeaning is
1067 select meaning from okc_lookups_v
1068 where lookup_type = 'OKC_ARTICLE_STATUS'
1069 and lookup_code = 'PENDING_APPROVAL';
1070 meaning okc_lookups_v.meaning%type;
1071 begin
1072 open c_pmeaning;
1073 fetch c_pmeaning into meaning;
1074 close c_pmeaning;
1075 return meaning;
1076 exception
1077 when others then
1078 close c_pmeaning;
1079 return null;
1080 end;
1081
1082 function get_adopted_meaning return varchar2
1083 is
1084 cursor c_ameaning is
1085 select meaning from okc_lookups_v
1086 where lookup_type = 'OKC_ARTICLE_ADOPTION_TYPE'
1087 and lookup_code = 'ADOPTED';
1088 meaning okc_lookups_v.meaning%type;
1089 begin
1090 open c_ameaning;
1091 fetch c_ameaning into meaning;
1092 close c_ameaning;
1093 return meaning;
1094 exception
1095 when others then
1096 close c_ameaning;
1097 return null;
1098 end;
1099
1100 procedure selector( itemtype in varchar2,
1101 itemkey in varchar2,
1102 actid in number,
1103 command in varchar2,
1104 resultout in out nocopy varchar2)
1105 is
1106 l_current_org_id number;
1107 l_current_user_id number;
1108 l_current_resp_id number;
1109 l_current_resp_appl_id number;
1110
1111 org_id number;
1112 user_id number;
1113 resp_id number;
1114 resp_appl_id number;
1115
1116 begin
1117 org_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'ORG_ID', true);
1118 -- get current apps context params
1119 l_current_user_id := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
1120 l_current_resp_id := TO_NUMBER(FND_PROFILE.VALUE('RESP_ID'));
1121 l_current_resp_appl_id := TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID'));
1122 -- get apps context params saved in wf
1123 user_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'USER_ID', true);
1124 resp_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'RESP_ID', true);
1125 resp_appl_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'RESP_APPL_ID', true);
1126 -- set apps context if needed
1127 if ( command = 'SET_CTX' ) then
1128 fnd_global.apps_initialize(user_id, resp_id, resp_appl_id);
1129 mo_global.set_policy_context('S', org_id);
1130 resultout := 'COMPLETE'; -- context is set up
1131 return;
1132 end if;
1133 -- check current apps context
1134 if ( command = 'TEST_CTX' ) then
1135 if (nvl(mo_global.get_access_mode, 'NULL') <> 'S') or
1136 (nvl(mo_global.get_current_org_id, -99) <> org_id) then
1137 resultout := 'FALSE'; -- org part of context is wrong - reset context
1138 return;
1139 end if;
1140 if (l_current_user_id <> user_id) or
1141 (l_current_resp_id <> resp_id) or
1142 (l_current_resp_appl_id <> resp_appl_id) then
1143 resultout := 'FALSE'; -- apps params part of context is wrong - reset context
1144 return;
1145 end if;
1146 resultout := 'TRUE'; -- apps context is allright - do not touch it
1147 return;
1148 end if;
1149 exception
1150 when others then
1151 WF_CORE.CONTEXT ('OKC_ARTWF_PVT', 'selector', itemtype,
1152 itemkey, to_char(actid), command);
1153 raise;
1154 end;
1155
1156 procedure select_process( itemtype in varchar2,
1157 itemkey in varchar2,
1158 actid in number,
1159 command in varchar2,
1160 resultout in out nocopy varchar2)
1161 is
1162 adoption_type okc_article_adoptions.adoption_type%type;
1163 global_yn okc_article_versions.global_yn%type;
1164 user_id number;
1165 resp_id number;
1166 resp_appl_id number;
1167 begin
1168 user_id := null;
1169 resp_id := null;
1170 resp_appl_id := null;
1171 if ( command = 'RUN' ) then
1172 adoption_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'ADOPTION_TYPE', false);
1173 global_yn := wf_engine.GetItemAttrText(itemtype, itemkey, 'GLOBAL_YN', false);
1174 if adoption_type in ('AVAILABLE', 'ADOPTED') then
1175 resultout := 'ARTICLES_ADOPTION_PROC'; -- start articles adoption process
1176 else
1177 if global_yn = 'Y' then
1178 resultout := 'GLOBAL_ARTICLES_APPROVAL_PROC'; -- start global articles approval
1179 elsif global_yn = 'N' then
1180 resultout := 'LOCAL_ARTICLES_APPROVAL_PROC'; -- start local articles approval
1181 end if;
1182 end if;
1183 return;
1184 end if;
1185 exception
1186 when others then
1187 WF_CORE.CONTEXT ('OKC_ARTWF_PVT', 'select_process', itemtype,
1188 itemkey, to_char(actid), command);
1189 raise;
1190 end;
1191
1192 --
1193 -- sets status to APPROVED
1194 --
1195 procedure set_approved( itemtype in varchar2,
1196 itemkey in varchar2,
1197 actid in number,
1198 funcmode in varchar2,
1199 resultout out nocopy varchar2)
1200 is
1201 --pragma autonomous_transaction;
1202 article_version_id okc_article_versions.article_version_id%type;
1203 adoption_flag varchar2(1);
1204 l_return_status varchar2(1);
1205 msg_count number;
1206 msg_data varchar2(250);
1207 change_status_x exception;
1208 org_id number;
1209 begin
1210 if ( funcmode = 'RUN' ) then
1211 article_version_id :=
1212 wf_engine.getItemAttrNumber(itemtype, itemkey, 'ARTICLE_VERSION_ID', false);
1213 org_id :=
1214 wf_engine.getItemAttrNumber(itemtype, itemkey, 'ORG_ID', false);
1215 adoption_flag :=
1216 wf_engine.getItemAttrText(itemtype, itemkey, 'ADOPTION_YN', false);
1217 OKC_ARTICLE_STATUS_CHANGE_PVT.approve(null,null,
1218 l_return_status, msg_count, msg_data, org_id, adoption_flag, article_version_id);
1219 resultout := 'COMPLETE';
1220 If l_return_status <> 'S' THEN
1221 resultout := 'ERROR';
1222 raise change_status_x;
1223 end if;
1224 return;
1225 end if;
1226 exception
1227 when change_status_x then
1228 WF_CORE.CONTEXT('OKC_ARTICLE_STATUS_CHANGE_PVT', 'approve', itemtype,
1229 itemkey, to_char(actid), funcmode);
1230 raise;
1231 when others then
1232 WF_CORE.CONTEXT ( 'OKC_ARTWF_PVT', 'set_approved', itemtype,
1233 itemkey, to_char(actid), funcmode);
1234 raise;
1235 end;
1236
1237 --
1238 -- sets status to REJECTED
1239 --
1240 procedure set_rejected( itemtype in varchar2,
1241 itemkey in varchar2,
1242 actid in number,
1243 funcmode in varchar2,
1244 resultout out nocopy varchar2)
1245 is
1246 --pragma autonomous_transaction;
1247 article_version_id okc_article_versions.article_version_id%type;
1248 adoption_flag varchar2(1);
1249 l_return_status varchar2(1);
1250 msg_count number;
1251 msg_data varchar2(250);
1252 change_status_x exception;
1253 org_id number;
1254 begin
1255 if ( funcmode = 'RUN' ) then
1256 article_version_id :=
1257 wf_engine.getItemAttrNumber(itemtype, itemkey, 'ARTICLE_VERSION_ID', false);
1258 org_id :=
1259 wf_engine.getItemAttrNumber(itemtype, itemkey, 'ORG_ID', false);
1260 adoption_flag :=
1261 wf_engine.getItemAttrText(itemtype, itemkey, 'ADOPTION_YN', false);
1262 OKC_ARTICLE_STATUS_CHANGE_PVT.reject(null,null,
1263 l_return_status, msg_count, msg_data, org_id, adoption_flag, article_version_id);
1264 resultout := 'COMPLETE';
1265 If l_return_status <> 'S' THEN
1266 resultout := 'ERROR';
1267 raise change_status_x;
1268 end if;
1269 return;
1270 end if;
1271 exception
1272 when change_status_x then
1273 WF_CORE.CONTEXT('OKC_ARTICLE_STATUS_CHANGE_PVT', 'reject', itemtype,
1274 itemkey, to_char(actid), funcmode);
1275 raise;
1276 when others then
1277 WF_CORE.CONTEXT ( 'OKC_ARTWF_PVT', 'set_rejected', itemtype,
1278 itemkey, to_char(actid), funcmode);
1279 raise;
1280 end;
1281
1282 -- somehow fnd_global.user_name doesn't work !!!
1283 function user_name return varchar2
1284 is
1285 cursor c_usr is
1286 select user_name
1287 from fnd_user_view
1288 where user_id = fnd_global.user_id;
1289 user_name fnd_user_view.user_name%type;
1290 begin
1291 user_name := null;
1292 open c_usr;
1293 fetch c_usr into user_name;
1294 close c_usr;
1295 return user_name;
1296 exception
1297 when others then
1298 close c_usr;
1299 user_name := 'UNDEFINED';
1300 return user_name;
1301 end;
1302
1303 procedure start_wf_processes(result out nocopy varchar2)
1304 is
1305 pragma autonomous_transaction;
1306 user_id number;
1307 resp_id number;
1308 resp_appl_id number;
1309 begin
1310 -- get current apps context params
1311 user_id := fnd_global.user_id;
1312 resp_id := fnd_global.resp_id;
1313 resp_appl_id := fnd_global.resp_appl_id;
1314
1315 result := 'OK';
1316 for i in 1..write_ptr
1317 loop
1318 begin
1319 wf_engine.CreateProcess( 'OKCARTAP', c_tab(i).ikey, 'ARTICLES_APPROVAL_ROOT_PROC');
1320 wf_engine.SetItemUserKey( 'OKCARTAP', c_tab(i).ikey, c_tab(i).ukey);
1321 wf_engine.SetItemOwner( 'OKCARTAP', c_tab(i).ikey, fnd_global.user_name);
1322 wf_engine.SetItemAttrNumber( 'OKCARTAP', c_tab(i).ikey, 'USER_ID', user_id);
1323 wf_engine.SetItemAttrNumber( 'OKCARTAP', c_tab(i).ikey, 'RESP_ID', resp_id);
1324 wf_engine.SetItemAttrNumber( 'OKCARTAP', c_tab(i).ikey, 'RESP_APPL_ID', resp_appl_id);
1325 wf_engine.SetItemAttrNumber( 'OKCARTAP', c_tab(i).ikey, 'ORG_ID', c_tab(i).org_id);
1326 wf_engine.SetItemAttrNumber( 'OKCARTAP', c_tab(i).ikey, 'ARTICLE_ID', c_tab(i).article_id);
1327 wf_engine.SetItemAttrNumber( 'OKCARTAP', c_tab(i).ikey, 'ARTICLE_VERSION_ID', c_tab(i).article_version_id);
1328 wf_engine.SetItemAttrText( 'OKCARTAP', c_tab(i).ikey, 'ARTICLE_STATUS', c_tab(i).article_status);
1329 wf_engine.SetItemAttrText( 'OKCARTAP', c_tab(i).ikey, 'ADOPTION_TYPE', c_tab(i).adoption_type);
1330 wf_engine.SetItemAttrText( 'OKCARTAP', c_tab(i).ikey, 'GLOBAL_YN', c_tab(i).global_yn);
1331 wf_engine.SetItemAttrText( 'OKCARTAP', c_tab(i).ikey, 'REQUESTOR', fnd_global.user_name);
1332 wf_engine.SetItemAttrText( 'OKCARTAP', c_tab(i).ikey, 'REQUESTOR_DISPLAY_NAME', get_display_name(fnd_global.user_name));
1333 wf_engine.StartProcess('OKCARTAP' , c_tab(i).ikey);
1334 commit;
1335 exception
1336 when others then
1337 result := 'NOK';
1338 rollback;
1339 end;
1340 end loop;
1341 end;
1342
1343 procedure start_wf_process(org_id in number, article_version_id in number, result out nocopy varchar2)
1344 is
1345 check_result varchar2(3);
1346 wf_result varchar2(3);
1347 x_result varchar(1);
1348 x_msg_count number;
1349 x_msg_data varchar2(2000);
1350 begin
1351 check_result := 'NOK';
1352 wf_result := 'NOK';
1353 fnd_msg_pub.initialize;
1354 clean;
1355 check_status(org_id, article_version_id, x_result, x_msg_count, x_msg_data);
1356 if x_result = fnd_api.G_RET_STS_SUCCESS then
1357 start_wf_processes(wf_result);
1358 end if;
1359 result := wf_result;
1360 exception
1361 when others then
1362 result := 'NOK';
1363 end;
1364
1365 function validate_article_version( p_search_flow in varchar2,
1366 p_article_version_id in number,
1367 p_article_status in varchar2,
1368 p_org_id in number)
1369 return varchar2
1370 is
1371 result varchar2(1);
1372 begin
1373 if upper(p_search_flow) = 'LOCAL' then
1374 if upper(p_article_status) = 'IGNORE' then
1375 /* bug 5008542
1376 select 'Y' into result
1377 from okc_articles_local_v
1378 where org_id = p_org_id
1379 and article_version_id = p_article_version_id;
1380 */
1381 select 'Y' into result
1382 from (
1383 select 'Y'
1384 from
1385 okc_articles_all art,
1386 okc_article_versions artv
1387 where art.standard_yn = 'Y'
1388 and art.article_id = artv.article_id
1389 and artv.article_version_id = p_article_version_id
1390 and art.org_id = p_org_id
1391 union all
1392 select 'Y'
1393 from
1394 okc_articles_all art,
1395 okc_article_versions artv,
1396 okc_article_adoptions arta
1397 where art.standard_yn = 'Y'
1398 and artv.global_yn = 'Y'
1399 and art.article_id = artv.article_id
1400 and artv.article_version_id = arta.global_article_version_id
1401 and artv.article_version_id = p_article_version_id
1402 and arta.local_org_id = p_org_id
1403 and arta.local_article_version_id is null
1404 )
1405 where rownum <= 1;
1406 else
1407 if p_article_status is null then
1408 /* bug 5008542
1409 select 'Y' into result
1410 from okc_articles_local_v
1411 where org_id = p_org_id
1412 and article_version_id = p_article_version_id
1413 and article_status is null;
1414 */
1415 select 'Y' into result
1416 from (
1417 select
1418 decode(artv.article_status, 'APPROVED', decode(greatest(nvl(trunc(end_date),
1419 trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
1420 artv.article_status) article_status
1421 from
1422 okc_articles_all art,
1423 okc_article_versions artv
1424 where art.standard_yn = 'Y'
1425 and art.article_id = artv.article_id
1426 and artv.article_version_id = p_article_version_id
1427 and art.org_id = p_org_id
1428 union all
1429 select
1430 decode(arta.adoption_type, 'AVAILABLE', decode( decode(greatest(nvl(trunc(end_date),
1431 trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
1432 'APPROVED', arta.adoption_status, 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'REJECTED',
1433 'REJECTED' ), 'ADOPTED', decode( decode(arta.adoption_status, 'PENDING_APPROVAL', 'PENDING_APPROVAL',
1434 decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED',
1435 artv.article_status) ), 'APPROVED', arta.adoption_status, 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD',
1436 'PENDING_APPROVAL', 'PENDING_APPROVAL' ) ) article_status
1437 from
1438 okc_articles_all art,
1439 okc_article_versions artv,
1440 okc_article_adoptions arta
1441 where art.standard_yn = 'Y'
1442 and artv.global_yn = 'Y'
1443 and art.article_id = artv.article_id
1444 and artv.article_version_id = arta.global_article_version_id
1445 and artv.article_version_id = p_article_version_id
1446 and arta.local_org_id = p_org_id
1447 and arta.local_article_version_id is null
1448 ) a
1449 where rownum <= 1
1450 and article_status is null;
1451 else
1452 /* bug 5008542
1453 select 'Y' into result
1454 from okc_articles_local_v
1455 where org_id = p_org_id
1456 and article_version_id = p_article_version_id
1457 and article_status = upper(p_article_status);
1458 */
1459 select 'Y' into result
1460 from (
1461 select
1462 decode(artv.article_status, 'APPROVED', decode(greatest(nvl(trunc(end_date),
1463 trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
1464 artv.article_status) article_status
1465 from
1466 okc_articles_all art,
1467 okc_article_versions artv
1468 where art.standard_yn = 'Y'
1469 and art.article_id = artv.article_id
1470 and artv.article_version_id = p_article_version_id
1471 and art.org_id = p_org_id
1472 union all
1473 select
1474 decode(arta.adoption_type, 'AVAILABLE', decode( decode(greatest(nvl(trunc(end_date),
1475 trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
1476 'APPROVED', arta.adoption_status, 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'REJECTED',
1477 'REJECTED' ), 'ADOPTED', decode( decode(arta.adoption_status, 'PENDING_APPROVAL',
1478 'PENDING_APPROVAL', decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)),
1479 trunc(sysdate), 'EXPIRED', artv.article_status) ), 'APPROVED', arta.adoption_status, 'EXPIRED',
1480 'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'PENDING_APPROVAL', 'PENDING_APPROVAL' ) ) article_status
1481 from
1482 okc_articles_all art,
1483 okc_article_versions artv,
1484 okc_article_adoptions arta
1485 where art.standard_yn = 'Y'
1486 and artv.global_yn = 'Y'
1487 and art.article_id = artv.article_id
1488 and artv.article_version_id = arta.global_article_version_id
1489 and artv.article_version_id = p_article_version_id
1490 and arta.local_org_id = p_org_id
1491 and arta.local_article_version_id is null
1492 ) a
1493 where rownum <= 1
1494 and article_status = upper(p_article_status);
1495 end if;
1496 end if;
1497 elsif upper(p_search_flow) = 'GLOBAL' then
1498 if upper(p_article_status) = 'IGNORE' then
1499 /* bug 5011435
1500 select 'Y' into result
1501 from okc_articles_global_v
1502 where org_id = p_org_id
1503 and article_version_id = p_article_version_id;
1504 */
1505 select 'Y' into result
1506 from (
1507 select 'Y'
1508 from
1509 okc_articles_all art,
1510 okc_article_versions artv
1511 where
1512 art.standard_yn = 'Y'
1513 and art.article_id = artv.article_id
1514 and org_id = p_org_id
1515 and article_version_id = p_article_version_id
1516 ) a
1517 where rownum <= 1;
1518 else
1519 if p_article_status is null then
1520 /* bug 5011435
1521 select 'Y' into result
1522 from okc_articles_global_v
1523 where org_id = p_org_id
1524 and article_version_id = p_article_version_id
1525 and article_status is null;
1526 */
1527 select 'Y' into result
1528 from (
1529 select
1530 decode(artv.article_status, 'APPROVED', decode(greatest(nvl(trunc(end_date),
1531 trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
1532 artv.article_status) article_status
1533 from
1534 okc_articles_all art,
1535 okc_article_versions artv
1536 where
1537 art.standard_yn = 'Y'
1538 and art.article_id = artv.article_id
1539 and org_id = p_org_id
1540 and article_version_id = p_article_version_id
1541 ) a
1542 where rownum <= 1
1543 and article_status is null;
1544 else
1545 /* bug 5011435
1546 select 'Y' into result
1547 from okc_articles_global_v
1548 where org_id = p_org_id
1549 and article_version_id = p_article_version_id
1550 and article_status = upper(p_article_status);
1551 */
1552 select 'Y' into result
1553 from (
1554 select
1555 decode(artv.article_status, 'APPROVED', decode(greatest(nvl(trunc(end_date),
1556 trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
1557 artv.article_status) article_status
1558 from
1559 okc_articles_all art,
1560 okc_article_versions artv
1561 where
1562 art.standard_yn = 'Y'
1563 and art.article_id = artv.article_id
1564 and org_id = p_org_id
1565 and article_version_id = p_article_version_id
1566 ) a
1567 where article_status = upper(p_article_status);
1568 end if;
1569 end if;
1570 else
1571 return null;
1572 end if;
1573 return 'FRESH';
1574 exception
1575 when others then
1576 return 'STALE';
1577 end;
1578
1579 function validate_article_version( p_article_version_id in number,
1580 p_article_status in varchar2,
1581 p_org_id in number)
1582 return varchar2
1583 is
1584 result1 varchar2(5);
1585 result2 varchar2(5);
1586 begin
1587 result1 := null;
1588 result2 := null;
1589 result1 := validate_article_version('Local',
1590 p_article_version_id,
1591 p_article_status,
1592 p_org_id);
1593 result2 := validate_article_version('Global',
1594 p_article_version_id,
1595 p_article_status,
1596 p_org_id);
1597 if(result1 = 'FRESH' or result2 = 'FRESH') then
1598 return 'FRESH';
1599 else
1600 return 'STALE';
1601 end if;
1602 end;
1603
1604 procedure generic_error(routine in varchar2,
1605 errcode in number,
1606 errmsg in varchar2) is
1607 begin
1608 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1609 fnd_message.set_token('ROUTINE', routine);
1610 fnd_message.set_token('ERRNO', errcode);
1611 fnd_message.set_token('REASON', dbms_utility.format_error_stack);
1612 fnd_msg_pub.add;
1613 end;
1614
1615 function get_org_name(p_org_id in number) return varchar2
1616 is
1617 org_name hr_organization_units.name%type;
1618 begin
1619 -- Fix for bug# 5010703, replaced hr_organization_units_v with hr_organization_units for Performance(shared memory)
1620 /* bug 5028066
1621 select name into org_name
1622 from hr_organization_units
1623 where organization_id = p_org_id;
1624 */
1625 select
1626 otl.name into org_name
1627 from
1628 hr_all_organization_units o,
1629 hr_all_organization_units_tl otl
1630 where
1631 o.organization_id = otl.organization_id
1632 and otl.language = userenv('LANG')
1633 and o.organization_id = p_org_id;
1634 return org_name;
1635 exception
1636 when others then return null;
1637 end;
1638
1639 -- -----------------------------------------------------------------------------
1640 -- Function pre_submit_validation
1641 -- validates approvers(Sell and Buy)/administrators(Sell and Buy) for the given organization id
1642 -- Input:
1643 -- p_org_id - organization id
1644 -- Output:
1645 -- result of validation
1646 -- 'OK' if valid
1647 -- 'NOK' if invalid
1648 -- -----------------------------------------------------------------------------
1649 function pre_submit_validation(p_org_id in number)
1650 return varchar2
1651 is
1652 undefined_approver1S exception;
1653 undefined_approver1B exception;
1654 undefined_approver2S exception;
1655 undefined_approver2B exception;
1656 result varchar2(1);
1657 approverS wf_users.name%type;
1658 approverB wf_users.name%type;
1659 begin
1660 result := 'N';
1661 fnd_msg_pub.initialize;
1662 approverS := get_approver(p_org_id, 'S');
1663 approverB := get_approver(p_org_id, 'B');
1664
1665 if approverS is null then
1666 raise undefined_approver1S;
1667 end if;
1668
1669 if approverB is null then
1670 raise undefined_approver1B;
1671 end if;
1672
1673 begin
1674 select 'Y' into result
1675 from wf_users
1676 where name = approverS;
1677 exception
1678 when others then
1679 raise undefined_approver2S;
1680 end;
1681 if result = 'N' then
1682 raise undefined_approver2S;
1683 end if;
1684
1685 begin
1686 select 'Y' into result
1687 from wf_users
1688 where name = approverB;
1689 exception
1690 when others then
1691 raise undefined_approver2B;
1692 end;
1693 if result = 'N' then
1694 raise undefined_approver2B;
1695 end if;
1696
1697 return 'OK';
1698 exception
1699 when undefined_approver1S then
1700 fnd_message.set_name('OKC', 'OKC_ART_UNDEF_SELL_APPROVER');
1701 fnd_message.set_token('ORGNAME', get_org_name(p_org_id));
1702 fnd_msg_pub.add;
1703 return 'NOK';
1704 when undefined_approver1B then
1705 fnd_message.set_name('OKC', 'OKC_ART_UNDEF_BUY_APPROVER');
1706 fnd_message.set_token('ORGNAME', get_org_name(p_org_id));
1707 fnd_msg_pub.add;
1708 return 'NOK';
1709 when undefined_approver2S then
1710 fnd_message.set_name('OKC', 'OKC_ART_UNKNOWN_SELL_APPROVER');
1711 fnd_message.set_token('ORGNAME', get_org_name(p_org_id));
1712 fnd_msg_pub.add;
1713 return 'NOK';
1714 when undefined_approver2B then
1715 fnd_message.set_name('OKC', 'OKC_ART_UNKNOWN_BUY_APPROVER');
1716 fnd_message.set_token('ORGNAME', get_org_name(p_org_id));
1717 fnd_msg_pub.add;
1718 return 'NOK';
1719 when others then
1720 generic_error('okc_artwf_pvt.pre_submit_validation', sqlcode, sqlerrm);
1721 return 'NOK';
1722 end;
1723
1724 -- -----------------------------------------------------------------------------
1725 -- Function pre_submit_validation
1726 -- validates approvers(Sell and Buy) for the given organization id
1727 -- and intent
1728 -- Input:
1729 -- p_org_id - organization id
1730 -- p_intent - intent
1731 -- Output:
1732 -- result of validation
1733 -- 'OK' if valid
1734 -- 'NOK' if invalid
1735 -- -----------------------------------------------------------------------------
1736 function pre_submit_validation(p_org_id in number, p_intent in varchar2)
1737 return varchar2
1738 is
1739 undefined_approver1S exception;
1740 undefined_approver1B exception;
1741 undefined_approver2S exception;
1742 undefined_approver2B exception;
1743 result varchar2(1);
1744 approverS wf_users.name%type;
1745 approverB wf_users.name%type;
1746 begin
1747 result := 'N';
1748 fnd_msg_pub.initialize;
1749 approverS := get_approver(p_org_id, 'S');
1750 approverB := get_approver(p_org_id, 'B');
1751
1752 if p_intent = 'S' then
1753 if approverS is null then
1754 raise undefined_approver1S;
1755 end if;
1756 begin
1757 select 'Y' into result
1758 from wf_users
1759 where name = approverS;
1760 exception
1761 when others then
1762 raise undefined_approver2S;
1763 end;
1764 if result = 'N' then
1765 raise undefined_approver2S;
1766 end if;
1767 end if;
1768
1769 if p_intent = 'B' then
1770 if approverB is null then
1771 raise undefined_approver1B;
1772 end if;
1773 begin
1774 select 'Y' into result
1775 from wf_users
1776 where name = approverB;
1777 exception
1778 when others then
1779 raise undefined_approver2B;
1780 end;
1781 if result = 'N' then
1782 raise undefined_approver2B;
1783 end if;
1784 end if;
1785
1786 return 'OK';
1787 exception
1788 when undefined_approver1S then
1789 fnd_message.set_name('OKC', 'OKC_ART_UNDEF_SELL_APPROVER');
1790 fnd_message.set_token('ORGNAME', get_org_name(p_org_id));
1791 fnd_msg_pub.add;
1792 return 'NOK';
1793 when undefined_approver1B then
1794 fnd_message.set_name('OKC', 'OKC_ART_UNDEF_BUY_APPROVER');
1795 fnd_message.set_token('ORGNAME', get_org_name(p_org_id));
1796 fnd_msg_pub.add;
1797 return 'NOK';
1798 when undefined_approver2S then
1799 fnd_message.set_name('OKC', 'OKC_ART_UNKNOWN_SELL_APPROVER');
1800 fnd_message.set_token('ORGNAME', get_org_name(p_org_id));
1801 fnd_msg_pub.add;
1802 return 'NOK';
1803 when undefined_approver2B then
1804 fnd_message.set_name('OKC', 'OKC_ART_UNKNOWN_BUY_APPROVER');
1805 fnd_message.set_token('ORGNAME', get_org_name(p_org_id));
1806 fnd_msg_pub.add;
1807 return 'NOK';
1808 when others then
1809 generic_error('okc_artwf_pvt.pre_submit_validation', sqlcode, sqlerrm);
1810 return 'NOK';
1811 end;
1812
1813 procedure transfer(
1814 itemtype in varchar2,
1815 itemkey in varchar2,
1816 actid in number,
1817 funcmode in varchar2,
1818 resultout out nocopy varchar2
1819 )
1820 is
1821 approver_name wf_users.display_name%type;
1822 function_name varchar2(250);
1823
1824 cursor approver(new_approver in varchar2) is
1825 select display_name
1826 from wf_users
1827 where name = new_approver;
1828
1829 begin
1830 if (funcmode = 'TRANSFER') then
1831
1832 open approver(wf_engine.CONTEXT_NEW_ROLE);
1833 fetch approver into approver_name;
1834 close approver;
1835
1836 wf_engine.SetItemAttrText( itemtype,
1837 itemkey,
1838 'APPROVER_ROLE',
1839 wf_engine.CONTEXT_NEW_ROLE);
1840 wf_engine.SetItemAttrText( itemtype,
1841 itemkey,
1842 'APPROVER_DISPLAY_NAME',
1843 approver_name);
1844 end if;
1845 exception when others then
1846 wf_core.context( 'OKC_ARTWF_PVT',
1847 'TRANSFER',
1848 itemtype,
1849 itemkey,
1850 to_char(actid),
1851 funcmode);
1852 raise;
1853 end transfer;
1854
1855 function itemtype(nid in number) return varchar2
1856 is
1857 str wf_notifications.context%type;
1858 s number;
1859 begin
1860 select context into str
1861 from wf_notifications
1862 where notification_id = nid;
1863 s := instr(str,':',1,1)-1;
1864 return SUBSTR(str,1,s);
1865 exception when others then
1866 return null;
1867 end;
1868
1869 function itemkey(nid in number) return varchar2
1870 is
1871 str wf_notifications.context%type;
1872 f number;
1873 s number;
1874 begin
1875 select context into str
1876 from wf_notifications
1877 where notification_id = nid;
1878 f := instr(str,':',1,1);
1879 s := instr(str,':',1,2)-f-1;
1880 return substr(str,f+1,s);
1881 exception when others then
1882 return null;
1883 end;
1884
1885 function code(str in varchar2) return varchar2
1886 is
1887 begin
1888 return rtrim(str, '0123456789');
1889 exception when others then
1890 return null;
1891 end;
1892
1893 function nid(str in varchar2) return varchar2
1894 is
1895 begin
1896 return ltrim(str, okc_artwf_pvt.code(str));
1897 exception when others then
1898 return null;
1899 end;
1900
1901 PROCEDURE orgname( document_id in varchar2,
1902 display_type in varchar2,
1903 document in out NOCOPY varchar2,
1904 document_type in out NOCOPY varchar2)
1905 is
1906 begin
1907 start_log('orgname');
1908 log('orgname entry');
1909 document := get_org_name(document_id);
1910 log('org_id='||document_id);
1911 log('org_name='||document);
1912 log('orgname exit');
1913 end;
1914
1915 PROCEDURE subject( document_id in varchar2,
1916 display_type in varchar2,
1917 document in out NOCOPY varchar2,
1918 document_type in out NOCOPY varchar2)
1919 is
1920 message_code fnd_new_messages.message_name%type;
1921 org_id okc_articles_all.org_id%type;
1922 article_id okc_articles_all.article_id%type;
1923 article_version_id okc_article_versions.article_version_id%type;
1924
1925 requestor_name wf_users.display_name%type;
1926 itemtype wf_engine.setctx_itemtype%type;
1927 itemkey wf_engine.setctx_itemkey%type;
1928 nid number;
1929 begin
1930 start_log('subject');
1931 log('subject entry');
1932 document := 'Undefined';
1933 nid := to_number(okc_artwf_pvt.nid(document_id));
1934 message_code := okc_artwf_pvt.code(document_id);
1935 itemtype := okc_artwf_pvt.itemtype(nid);
1936 itemkey := okc_artwf_pvt.itemkey(nid);
1937 fnd_message.clear;
1938 fnd_message.set_name(application => 'OKC', name => message_code);
1939 if message_code in ( 'OKC_ART_ADOPTION_NTF_SUBJECT',
1940 'OKC_ART_ADOPTION_NTF_SUBJECT_A',
1941 'OKC_ART_ADOPTION_NTF_SUBJECT_R',
1942 'OKC_ART_APPROVAL_NTF_SUBJECT',
1943 'OKC_ART_APPROVAL_NTF_SUBJECT_A',
1944 'OKC_ART_APPROVAL_NTF_SUBJECT_R',
1945 'OKC_ART_ADOPTED_NTF_SUBJECT',
1946 'OKC_ART_AVAILABLE_NTF_SUBJECT') then
1947 fnd_message.set_token( token => 'ARTICLENUMBER',
1948 value => wf_notification.getAttrText(nid, '#HDR_ARTICLE_NUMBER'));
1949 fnd_message.set_token( token => 'ARTICLETITLE',
1950 value => wf_notification.getAttrText(nid, '#HDR_ARTICLE_TITLE'));
1951 fnd_message.set_token( token => 'ARTICLEVERSIONNUMBER',
1952 value => wf_notification.getAttrText(nid, '#HDR_ARTICLE_VERSION_NUMBER'));
1953 if message_code in ( 'OKC_ART_ADOPTION_NTF_SUBJECT',
1954 'OKC_ART_APPROVAL_NTF_SUBJECT') then
1955 fnd_message.set_token( token => 'APPROVERNAME',
1956 value => wf_engine.GetItemAttrText( itemtype, itemkey, 'REQUESTOR_DISPLAY_NAME'));
1957 end if;
1958 if message_code in ( 'OKC_ART_ADOPTED_NTF_SUBJECT',
1959 'OKC_ART_AVAILABLE_NTF_SUBJECT') then
1960 fnd_message.set_token( token => 'ORGANIZATIONNAME',
1961 value => get_org_name(wf_notification.getAttrText(nid, 'ORGANIZATION$')));
1962 end if;
1963 document := fnd_message.get;
1964 end if;
1965 log('subject exit');
1966 exception
1967 when others then
1968 WF_CORE.CONTEXT ('OKC_ARTWF_PVT', 'subject', itemtype, itemkey, document_id);
1969 raise;
1970 end;
1971
1972 -- bug 5202585 start
1973 function get_terms_org_name(p_template_id in number) return varchar2
1974 is
1975 org_name hr_organization_units.name%type;
1976 begin
1977 select
1978 otl.name into org_name
1979 from
1980 hr_all_organization_units o,
1981 hr_all_organization_units_tl otl
1982 where
1983 o.organization_id = otl.organization_id
1984 and otl.language = userenv('LANG')
1985 and o.organization_id = (
1986 select org_id
1987 from okc_terms_templates_all
1988 where template_id = p_template_id
1989 );
1990 return org_name;
1991 exception
1992 when others then return null;
1993 end;
1994
1995 function get_terms_intent_meaning(p_template_id in number) return varchar2
1996 is
1997 intent_meaning okc_lookups_v.meaning%type;
1998 begin
1999 select meaning into intent_meaning
2000 from okc_lookups_v
2001 where lookup_type = 'OKC_TERMS_INTENT'
2002 and lookup_code = (
2003 select intent
2004 from okc_terms_templates_all
2005 where template_id = p_template_id
2006 );
2007 return intent_meaning;
2008 exception
2009 when others then return null;
2010 end;
2011
2012 procedure callback( document_id in varchar2,
2013 display_type in varchar2,
2014 document in out NOCOPY varchar2,
2015 document_type in out NOCOPY varchar2)
2016 is
2017 cursor ntf_attrs(nid in varchar2)
2018 is
2019 select name
2020 from wf_notification_attributes
2021 where notification_id = nid;
2022 begin
2023 for attr in ntf_attrs(document_id) loop
2024 if attr.name like '#HDR%' then
2025 if attr.name = '#HDR_TMPL_ORG_NAME' then
2026 wf_notification.SetAttrText( document_id, attr.name,
2027 get_terms_org_name( wf_notification.GetAttrNumber( document_id, 'TEMPLATE_ID')));
2028 elsif attr.name = '#HDR_ORG_NAME' then
2029 wf_notification.SetAttrText( document_id, attr.name,
2030 get_terms_org_name( wf_notification.GetAttrNumber( document_id, 'TEMPLATE_ID')));
2031 elsif attr.name = '#HDR_TMPL_INTENT' then
2032 wf_notification.SetAttrText( document_id, attr.name,
2033 get_terms_intent_meaning( wf_notification.GetAttrNumber( document_id, 'TEMPLATE_ID')));
2034 end if;
2035 end if;
2036 end loop;
2037 document := null;
2038 exception
2039 when others then document := null;
2040 end;
2041 -- bug 5202585 end
2042
2043 -- bug 5261848 - cr3 start
2044
2045 function get_g_article_text(p_org_id number, p_article_version_id number)
2046 return okc_article_versions.article_text%type
2047 is
2048 text okc_article_versions.article_text%type;
2049 begin
2050 text := empty_clob();
2051 select article_text into text
2052 from okc_article_versions
2053 where article_version_id = (
2054 select global_article_version_id
2055 from okc_article_adoptions
2056 where local_org_id = p_org_id
2057 and local_article_version_id = p_article_version_id
2058 );
2059 return text;
2060 exception when others then
2061 text := empty_clob();
2062 dbms_lob.createtemporary(text, TRUE); --Bug# 9676464
2063 return text;
2064 end;
2065
2066 function get_g_translated_yn(p_article_version_id number)
2067 return okc_article_versions.translated_yn%type
2068 is
2069 yn okc_article_versions.translated_yn%type;
2070 begin
2071 yn := 'N';
2072 select nvl(translated_yn, 'N') into yn
2073 from okc_article_versions
2074 where article_version_id = p_article_version_id;
2075 return yn;
2076 exception when others then
2077 return 'N';
2078 end;
2079
2080 function get_g_localized_yn(p_org_id number, p_article_version_id number)
2081 return varchar2
2082 is
2083 yn varchar2(1);
2084 begin
2085 yn := 'N';
2086 select 'Y' into yn
2087 from okc_article_adoptions
2088 where local_org_id = p_org_id
2089 and local_article_version_id = p_article_version_id;
2090 return yn;
2091 exception when others then
2092 return 'N';
2093 end;
2094
2095 function get_g_article_version_id(p_org_id number, p_article_version_id number)
2096 return okc_article_versions.article_version_id%type
2097 is
2098 id okc_article_versions.article_version_id%type;
2099 begin
2100 id := null;
2101 select global_article_version_id into id
2102 from okc_article_adoptions
2103 where local_org_id = p_org_id
2104 and local_article_version_id = p_article_version_id;
2105 return id;
2106 exception when others then
2107 return null;
2108 end;
2109
2110 -- bug 5261848 - cr3 end
2111
2112 end;