[Home] [Help]
PACKAGE BODY: APPS.OKC_REPORT_PVT
Source
1 package body OKC_REPORT_PVT as
2 /* $Header: OKCRXPKB.pls 120.3 2005/08/16 16:35:18 jkodiyan noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --
7 -- Print Contract Process API methods
8 --
9 -- get_xsl_help
10 -- get_art_help
11 -- get_xml
12 -- get_htm
13 -- set_env
14 -- get_contract_url
15 -- check_access
16 -- transformation_error
17 -- check_transf_path
18 -- exec_OKC_WEB_PRERUN
19 -- prerun
20 -- exec_OKC_WEB_LOG_RUN
21 -- noop
22 -- free_temp_clob
23 -- get_k_version
24 --
25
26 --
27 -- Dependencies:
28 --
29 -- okc_tree_index (.set_root_id)
30 -- wf_mail (.urlencode)
31 --
32 -- okcFormsXmlPrint.jsp (former okcxmlkf.jsp) - print xml contract
33 --
34 -- Profile values used:
35 --
36 -- OKC_WEB_ENVPROC
37 -- OKC_WEB_LOG_RUN
38 -- OKC_WEB_REPORT
39 -- OKC_WEB_PRERUN
40 --
41
42 --
43 -- Messages used with tokens
44 --
45 -- OKC_PROFILE_CHECK PROFILE
46 -- OKC_NOT_CACHED_TRANSF METHOD
47 -- OKC_CACHE_LOCKED CACHE_TYPE
48 -- OKC_TRANSF_ERROR TRANSF_TYPE, TRANSF_NAME
49 -- OKC_INACTIVE_TRANSF TRANSF_TYPE, TRANSF_NAME, PROFILE
50 --
51
52 G_kid number;
53 G_vid number;
54 G_mid number;
55 G_xid number;
56
57 G_sql_id number;
58
59
60 G_ignore_cache varchar2(1);
61
62 --
63 -- PROCEDURE/Function
64 -- private EncodeString
65 --
66 -- PURPOSE
67 -- escapes '&' and '<'
68 --
69 function EncodeString(p_string varchar2) return varchar2
70 is
71 encoded_string varchar2(4000);
72 begin
73 encoded_string := p_string;
74 encoded_string := replace(encoded_string, '&', '&'||'amp;' );
75 encoded_string := replace(encoded_string, '<', '&'||'lt;' );
76 return(encoded_string);
77 end EncodeString;
78
79 --
80 -- PROCEDURE/Function
81 -- private EncodeClob
82 --
83 -- PURPOSE
84 -- escapes '&' and '<'
85 --
86 function EncodeClob(r_clob in out nocopy clob, p_title varchar2) return clob
87 is
88 r_len integer;
89 w_clob CLOB;
90 w_len integer;
91 offset integer := 1;
92 buff varchar2(4000);
93 begin
94 DBMS_LOB.OPEN(lob_loc => r_clob, open_mode => DBMS_LOB.LOB_READONLY);
95 r_len := DBMS_LOB.getlength(lob_loc => r_clob);
96 DBMS_LOB.CREATETEMPORARY(lob_loc => w_clob, cache => FALSE, dur => DBMS_LOB.CALL);
97 DBMS_LOB.OPEN(lob_loc => w_clob,open_mode => DBMS_LOB.LOB_READWRITE);
98 buff := '<html><head><title>"?" -> '||p_title||'</title></head><body><pre>';
99 DBMS_LOB.writeappend(lob_loc => w_clob, amount => length(buff),buffer => buff);
100 buff := NULL;
101 WHILE (r_len > 0) LOOP
102 buff := DBMS_LOB.substr(lob_loc => r_clob,amount => least(r_len,800),offset => offset);
103 buff := EncodeString(buff);
104 DBMS_LOB.writeappend(lob_loc => w_clob,amount => length(buff),buffer => buff);
105 buff := NULL;
106 r_len := r_len-least(r_len,800);
107 offset := offset+800;
108 END LOOP;
109 buff := '</pre></body></html>';
110 DBMS_LOB.writeappend(lob_loc => w_clob,amount =>length(buff),buffer =>buff);
111 DBMS_LOB.CLOSE(lob_loc => r_clob);
112 DBMS_LOB.CLOSE(lob_loc => w_clob);
113 return w_clob;
114 end;
115
116 --
117 -- PROCEDURE/Function
118 -- private EncodeClob
119 --
120 -- PURPOSE
121 -- overload previous procedure if clob is empty
122 --
123 function EncodeClob(p_title varchar2, p_message varchar2) return clob
124 is
125 w_clob CLOB;
126 w_len integer;
127 offset integer := 1;
128 buff varchar2(4000);
129 begin
130 DBMS_LOB.CREATETEMPORARY(lob_loc => w_clob,cache => FALSE,dur => DBMS_LOB.CALL);
131 DBMS_LOB.OPEN(lob_loc =>w_clob, open_mode => DBMS_LOB.LOB_READWRITE);
132 buff := '<html><head><title>"?" -> '||p_title||'</title></head><body><pre>';
133 DBMS_LOB.writeappend(lob_loc => w_clob,amount =>length(buff),buffer => buff);
134 buff := NULL;
135 if (p_title is NULL) then
136 DBMS_LOB.writeappend(lob_loc => w_clob,amount =>length(p_message),buffer =>p_message);
137 end if;
138 buff := '</pre></body></html>';
139 DBMS_LOB.writeappend(lob_loc => w_clob,amount => length(buff),buffer => buff);
140 DBMS_LOB.CLOSE(lob_loc =>w_clob);
141 return w_clob;
142 end;
143
144 --
145 -- PROCEDURE/Function
146 -- private profile_check_msg
147 --
148 -- PURPOSE
149 -- returns OKC_PROFILE_CHECK message
150 -- with user name of p_prof_name option value
151 --
152 function profile_check_msg(p_prof_name varchar2) return varchar2
153 is
154 l_message varchar2(2000);
155 l_token varchar2(240);
156 cursor profile_option_csr(p_profile_option varchar2) is
157 select USER_PROFILE_OPTION_NAME
158 from fnd_profile_options_vl
159 where application_id=510
160 and PROFILE_OPTION_NAME = p_profile_option;
161 begin
162 open profile_option_csr(p_prof_name);
163 fetch profile_option_csr into l_token;
164 close profile_option_csr;
165 fnd_message.clear;
166 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_PROFILE_CHECK');
167 fnd_message.set_token(TOKEN=>'PROFILE',VALUE=>'"'||l_token||'"');
168 return fnd_message.get;
169 end;
170
171 --
172 -- PROCEDURE/Function
173 -- private get_sql_id
174 --
175 -- PURPOSE
176 -- returns sql_id - id of the query
177 -- derives it from OKC_WEB_REPORT profile option
178 --
179 function get_sql_id return number
180 is
181 l_sql_id number;
182 cursor c_sql_id is
183 select SQL_ID from (
184 select SQL_ID, XST_ID
185 from OKC_REPORT_XST
186 connect by ID = prior XST_ID
187 start with ID = fnd_profile.value('OKC_WEB_REPORT')
188 ) where SQL_ID is not NULL;
189 begin
190 open c_sql_id;
191 fetch c_sql_id into l_sql_id;
192 close c_sql_id;
193 return l_sql_id;
194 end;
195
196 --
197 -- PROCEDURE/Function
198 -- private get_xsl_id
199 --
200 -- PURPOSE
201 -- returns xsl_id - stylesheet id
202 -- derives it from OKC_WEB_REPORT profile option
203 --
204 function get_xsl_id return number
205 is
206 l_xsl_id number;
207 cursor c_xsl_id is
208 select XSL_ID
209 from OKC_REPORT_XST
210 where ID = fnd_profile.value('OKC_WEB_REPORT');
211 begin
212 open c_xsl_id;
213 fetch c_xsl_id into l_xsl_id;
214 close c_xsl_id;
215 return l_xsl_id;
216 end;
217
218 --
219 -- PROCEDURE/Function
220 -- get_xsl_help
221 --
222 -- PURPOSE
223 -- returns help text for XSL designer in format
224 -- <html>
225 -- <head><title>
226 -- "?" -> okc_report_sql_v.name
227 -- </title></head>
228 -- <body><pre>
229 -- okc_report_sql_v.help_text
230 -- '&' and '<' replaced with '&'||'amp;' and '&'||'lt;'
231 -- </pre></body>
232 -- </html>
233 --
234 function get_xsl_help(
235 p_sql_id number -- if null derive from OKC_WEB_REPORT prof.
236 ) return clob
237 is
238 l_sql_id number;
239 l_name varchar2(150);
240 l_clob clob;
241 cursor c_clob(p_sql number) is
242 select name, help_text
243 from okc_report_sql_v
244 where id = p_sql;
245 l_message varchar2(2000);
246 begin
247 if (p_sql_id is NULL) then
248 l_sql_id := get_sql_id;
249 else
250 l_sql_id := p_sql_id;
251 end if;
252 open c_clob(l_sql_id);
253 fetch c_clob into l_name, l_clob;
254 close c_clob;
255 if (l_clob is NULL) then
256 return EncodeClob(l_name, profile_check_msg('OKC_WEB_REPORT'));
257 else
258 return EncodeClob(l_clob, l_name);
259 end if;
260 end;
261
262 --
263 -- PROCEDURE/Function
264 -- get_art_help
265 --
266 -- PURPOSE
267 -- returns help text for standard atricles designer
268 -- similar to previous method based on view okc_report_xsl_v
269 --
270 function get_art_help(
271 p_xsl_id number -- if null derive from OKC_WEB_REPORT prof.
272 ) return clob
273 is
274 l_xsl_id number;
275 l_name varchar2(150);
276 l_clob clob;
277 cursor c_clob(p_xsl number) is
278 select name, help_text
279 from okc_report_xsl_v
280 where id = p_xsl;
281 l_message varchar2(2000);
282 begin
283 if (p_xsl_id is NULL) then
284 l_xsl_id := get_xsl_id;
285 else
286 l_xsl_id := p_xsl_id;
287 end if;
288 open c_clob(l_xsl_id);
289 fetch c_clob into l_name, l_clob;
290 close c_clob;
291 if (l_clob is NULL) then
292 return EncodeClob(l_name, profile_check_msg('OKC_WEB_REPORT'));
293 else
294 return EncodeClob(l_clob, l_name);
295 end if;
296 end;
297
298 --
299 -- PROCEDURE/Function
300 -- private not_cached_trans_msg
301 --
302 -- PURPOSE
303 -- returns OKC_NOT_CACHED_TRANSF message
304 -- with procedure name as METHOD token
305 --
306 function not_cached_transf_msg(p_proc_name varchar2) return varchar2
307 is
308 l_message varchar2(2000);
309 l_token varchar2(240);
310 begin
311 fnd_message.clear;
312 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_NOT_CACHED_TRANSF');
313 fnd_message.set_token(TOKEN=>'METHOD',VALUE=>'"'||p_proc_name||'"');
314 return fnd_message.get;
315 end;
316
317 --
318 -- PROCEDURE/Function
319 -- private cache_locked_msg
320 --
321 -- PURPOSE
322 -- returns OKC_CACHE_LOCKED message
323 -- with cache name as CACHE token
324 --
325 function cache_locked_msg(p_cache varchar2) return varchar2
326 is
327 l_message varchar2(2000);
328 l_token varchar2(240);
329 begin
330 fnd_message.clear;
331 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_CACHE_LOCKED');
332 fnd_message.set_token(TOKEN=>'CACHE_TYPE',VALUE=>'"'||p_cache||'"');
333 return fnd_message.get;
334 end;
335
336 --
337 -- PROCEDURE/Function
338 -- public get_k_version
339 --
340 -- PURPOSE
341 -- output MAJOR (might be in as well), MINOR vesions
342 -- to get rid of default, that is last, Contract version
343 --
344 -- prior 1158 was procedure get_k_version(p_chr_id number,
345
346 procedure get_k_version(p_chr_id in out nocopy number,
347 x_major_version in out nocopy number,
348 x_minor_version out nocopy number) is
349 cursor k_version_csr(pp_chr_id number,pp_major_version number) is
350 select V.MAJOR_VERSION, V.MINOR_VERSION
351 from
352 okc_k_vers_numbers_v V
353 where V.CHR_ID = pp_chr_id
354 and (pp_major_version is NULL or pp_major_version = V.MAJOR_VERSION)
355 union all
356 select pp_major_version, max(H.MINOR_VERSION) MINOR_VERSION
357 from okc_k_vers_numbers_v V1,
358 OKC_K_VERS_NUMBERS_H H
359 where V1.CHR_ID = pp_chr_id
360 and H.CHR_ID = pp_chr_id
361 and H.MAJOR_VERSION = pp_major_version
362 and H.MAJOR_VERSION <> V1.MAJOR_VERSION
363 ;
364 begin
365 --1158
366 if (p_chr_id is NULL) then
367 p_chr_id := get_new_id;
368 x_major_version := -1;
369 x_minor_version := -1;
370 return;
371 end if;
372 --/1158
373 open k_version_csr(p_chr_id,x_major_version);
374 fetch k_version_csr into x_major_version, x_minor_version;
375 close k_version_csr;
376 end;
377
378 --
379 -- PROCEDURE/Function
380 -- get_xml
381 --
382 -- PURPOSE
383 -- should be called only for "cached" sql transformations
384 -- returns datagram from cache if it is still valid,
385 -- or updates/inserts clob to put there result on XSQL transformation
386 --
387 procedure get_xml(
388 -- standard parameters
389 p_api_version in NUMBER ,
390 p_init_msg_list in VARCHAR2 ,
391 x_return_status out NOCOPY VARCHAR2,
392 x_msg_count out NOCOPY NUMBER,
393 x_msg_data out NOCOPY VARCHAR2,
394 -- input parameters
395 --1158 p_chr_id in NUMBER,
396 --1158 p_major_version NUMBER ,
397 --1158 p_sql_id in NUMBER ,
398 p_run_anyway_yn in varchar2 ,
399 -- output parameters
400 x_xml_text out nocopy CLOB,
401 x_clob_status out nocopy VARCHAR2
402 --'I' - inserted empty, or old and obsolete; locked
403 --'V' - valid not locked
404 --'N' - not cached
405 ) is
406 l_sql_id number;
407 l_msg_data varchar2(2000);
408 l_cached varchar2(1);
409 l_sql_date DATE;
410 cursor sql_csr(p_xid number) is
411 select S.CACHE_YN, S.LAST_UPDATE_DATE, S.id SQL_ID
412 from OKC_REPORT_SQL_V S, OKC_REPORT_XST T
413 where T.id = p_xid and T.sql_id = S.id
414 and sysdate between S.start_date and nvl(S.end_date,sysdate);
415 l_dummy varchar2(1);
416 cursor xml_csr( pp_chr_id number,
417 pp_major_version number,
418 pp_minor_version number,
419 pp_sql_id number,
420 pp_sql_date date) is
421 select '!'
422 from
423 okc_report_xml_v M
424 where M.CHR_ID = pp_chr_id
425 and M.MAJOR_VERSION = pp_major_version
426 and M.MINOR_VERSION = pp_minor_version
427 and M.SQL_ID = pp_sql_id
428 and M.LAST_UPDATE_DATE > pp_sql_date
429 and M.XML_TEXT is not NULL
430 ;
431 cursor xml_csr1( pp_chr_id number,
432 pp_major_version number,
433 pp_minor_version number,
434 pp_sql_id number,
435 pp_sql_date date) is
436 select M.XML_TEXT
437 from
438 okc_report_xml_v M
439 where M.CHR_ID = pp_chr_id
440 and M.MAJOR_VERSION = pp_major_version
441 and M.MINOR_VERSION = pp_minor_version
442 and M.SQL_ID = pp_sql_id
443 and M.LAST_UPDATE_DATE > pp_sql_date
444 and M.XML_TEXT is not NULL
445 ;
446 begin
447 DBMS_TRANSACTION.SAVEPOINT('OKC_REPORT_PVT');
448
449 open sql_csr(G_xid);
450 fetch sql_csr into l_cached, l_sql_date, l_sql_id;
451 close sql_csr;
452 if (l_cached is null) then
453 x_return_status := 'E';
454 x_msg_count := 1;
455 l_msg_data := profile_check_msg('OKC_WEB_REPORT');
456 x_msg_data := l_msg_data;
457 x_xml_text := EncodeClob('Error: OKC_REPORT_PVT.get_xml', l_msg_data);
458 x_clob_status := 'I';
459 return;
460 elsif (l_cached = 'N' or G_ignore_cache = 'Y') then
461 x_return_status := 'S';
462 x_clob_status := 'N';
463 return;
464 end if;
465
466 l_dummy := '?';
467 open xml_csr( G_kid,
468 G_vid,
469 G_mid,
470 l_sql_id,
471 l_sql_date);
472 fetch xml_csr into l_dummy;
473 close xml_csr;
474 if (l_dummy = '!' and p_run_anyway_yn = 'N') then
475 open xml_csr1( G_kid,
476 G_vid,
477 G_mid,
478 l_sql_id,
479 l_sql_date);
480 fetch xml_csr1 into x_xml_text;
481 close xml_csr1;
482 x_return_status := 'S';
483 x_clob_status := 'V';
484 return;
485 end if;
486 --
487 -- obsolete? - lock cache, update everything but clob
488 --
489 begin
490 delete from okc_report_xml M
491 where M.CHR_ID = G_kid
492 and M.MAJOR_VERSION = G_vid
493 and M.LANGUAGE = userenv('LANG')
494 and M.SQL_ID = l_sql_id
495 ;
496 exception when others then NULL;
497 end;
498 insert into okc_report_xml
499 ( CHR_ID
500 ,MAJOR_VERSION
501 ,LANGUAGE
502 ,SQL_ID
503 ,MINOR_VERSION
504 ,XML_TEXT
505 ,CREATED_BY
506 ,CREATION_DATE
507 ,LAST_UPDATED_BY
508 ,LAST_UPDATE_DATE
509 ,LAST_UPDATE_LOGIN
510 ) values
511 ( G_kid,
512 G_vid,
513 userenv('LANG'),
514 l_sql_id,
515 G_mid,
516 empty_clob(),
517 fnd_global.user_id,
518 sysdate,
519 fnd_global.user_id,
520 sysdate,
521 fnd_global.login_id
522 );
523 select XML_TEXT into x_xml_text
524 from okc_report_xml
525 where CHR_ID = G_kid
526 and MAJOR_VERSION = G_vid
527 and LANGUAGE = userenv('LANG')
528 and SQL_ID = l_sql_id
529 for update of XML_TEXT nowait;
530 x_return_status := 'S';
531 x_clob_status := 'I';
532 return;
533 exception when others then
534 x_return_status := 'E';
535 x_msg_count := 1;
536 l_msg_data := cache_locked_msg('XML');
537 x_msg_data := l_msg_data;
538 x_xml_text := EncodeClob('Error: OKC_REPORT_PVT.get_xml', l_msg_data);
539 x_clob_status := 'I';
540 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('OKC_REPORT_PVT');
541 end;
542
543 --
544 -- PROCEDURE/Function
545 -- private get_xsl_sql_date
546 --
547 -- PURPOSE
548 -- returns last update date of the stylesheet
549 -- (including called stylesheets, if any)
550 --
551 function get_xsl_sql_date(p_xst_id number) return date
552 is
553 l_date date;
554 cursor last_update_csr(p_xst number) is
555 select greatest(
556 max(S.LAST_UPDATE_DATE),
557 max(Q.LAST_UPDATE_DATE),
558 max(T.LAST_UPDATE_DATE)) d
559 from
560 (select SQL_ID, XSL_ID, LAST_UPDATE_DATE
561 from OKC_REPORT_XST
562 connect by ID = prior XST_ID
563 start with ID = p_xst) T,
564 OKC_REPORT_XSL_V S,
565 OKC_REPORT_SQL_V Q
566 where T.XSL_ID = S.ID
567 and T.SQL_ID = Q.ID (+);
568 begin
569 open last_update_csr(p_xst_id);
570 fetch last_update_csr into l_date;
571 close last_update_csr;
572 return l_date;
573 end;
574
575 --
576 -- PROCEDURE/Function
577 -- get_htm
578 --
579 -- PURPOSE
580 -- should be called only for "cached" xsl transformations
581 -- returns datagram from cache if it is still valid,
582 -- or updates/inserts clob to put there result on XSL Transformation
583 --
584 procedure get_htm(
585 -- standard parameters
586 p_api_version in NUMBER ,
587 p_init_msg_list in VARCHAR2 ,
588 x_return_status out NOCOPY VARCHAR2,
589 x_msg_count out NOCOPY NUMBER,
590 x_msg_data out NOCOPY VARCHAR2,
591 -- input parameters
592 --1158 p_chr_id in NUMBER,
593 --1158 p_major_version NUMBER ,
594 --1158 p_xst_id in NUMBER ,
595 --1158 p_scn_id in NUMBER ,
596 p_run_anyway_yn in varchar2 ,
597 -- output parameters
598 x_htm_text out nocopy CLOB,
599 x_clob_status out nocopy VARCHAR2
600 --'I' - inserted empty, or old and obsolete; locked
601 --'V' - valid not locked
602 --'N' - not cached
603 ) is
604 l_msg_data varchar2(2000);
605 l_cached varchar2(1);
606 l_xst_date DATE;
607 cursor xst_csr(p_xst number) is
608 select T.CACHE_YN
609 from OKC_REPORT_XST_V T, OKC_REPORT_XSL_V L
610 where T.id = p_xst
611 and T.XSL_ID = L.ID
612 and sysdate between L.start_date and nvl(L.end_date,sysdate);
613 l_dummy varchar2(1);
614 cursor htm_cache_csr(pp_chr_id number,
615 pp_major_version number,
616 pp_minor_version number,
617 pp_xst_id number,
618 pp_scn_id number,
619 pp_xst_date date) is
620 select '!'
621 from
622 okc_report_htm_v H
623 where H.CHR_ID = pp_chr_id
624 and H.MAJOR_VERSION = pp_major_version
625 and H.MINOR_VERSION = pp_minor_version
626 and H.XST_ID = pp_xst_id
627 and H.SCN_ID = pp_scn_id
628 and H.LAST_UPDATE_DATE > pp_xst_date
629 and H.HTM_TEXT is not NULL
630 ;
631 cursor htm_cache_csr1( pp_chr_id number,
632 pp_major_version number,
633 pp_minor_version number,
634 pp_xst_id number,
635 pp_scn_id number,
636 pp_xst_date date) is
637 select H.HTM_TEXT
638 from
639 okc_report_htm_v H
640 where H.CHR_ID = pp_chr_id
641 and H.MAJOR_VERSION = pp_major_version
642 and H.MINOR_VERSION = pp_minor_version
643 and H.XST_ID = pp_xst_id
644 and H.SCN_ID = pp_scn_id
645 and H.LAST_UPDATE_DATE > pp_xst_date
646 and H.HTM_TEXT is not NULL
647 ;
648 begin
649 DBMS_TRANSACTION.SAVEPOINT('OKC_REPORT_PVT');
650
651 open xst_csr(G_xid);
652 fetch xst_csr into l_cached;
653 close xst_csr;
654 if (l_cached is null) then
655 x_return_status := 'E';
656 x_msg_count := 1;
657 l_msg_data := profile_check_msg('OKC_WEB_REPORT');
658 x_msg_data := l_msg_data;
659 x_htm_text := EncodeClob('Error: OKC_REPORT_PVT.get_htm', l_msg_data);
660 x_clob_status := 'I';
661 return;
662 end if;
663 --
664 -- find last update in definitions
665 --
666 l_xst_date := get_xsl_sql_date(G_xid);
667
668 l_dummy:='?';
669 open htm_cache_csr( G_kid,
670 G_vid,
671 G_mid,
672 G_xid,
673 0,
674 l_xst_date);
675 fetch htm_cache_csr into l_dummy;
676 close htm_cache_csr;
677 if (l_dummy = '!' and l_cached = 'Y' and p_run_anyway_yn = 'N' and G_ignore_cache = 'N') then
678 open htm_cache_csr1( G_kid,
679 G_vid,
680 G_mid,
681 G_xid,
682 0,
683 l_xst_date);
684 fetch htm_cache_csr1 into x_htm_text;
685 close htm_cache_csr1;
686 x_return_status := 'S';
687 x_clob_status := 'V';
688 return;
689 end if;
690 --
691 -- obsolete? - lock cache, update everything but clob
692 --
693 begin
694 delete from okc_report_htm H
695 where H.CHR_ID = G_kid
696 and H.MAJOR_VERSION = G_vid
697 and H.LANGUAGE = userenv('LANG')
698 and H.XST_ID = G_xid
699 and H.SCN_ID = 0
700 ;
701 exception when others then NULL;
702 end;
703 --
704 -- not found! - insert in cache and lock
705 --
706 insert into okc_report_htm
707 ( CHR_ID
708 ,MAJOR_VERSION
709 ,LANGUAGE
710 ,XST_ID
711 ,SCN_ID
712 ,MINOR_VERSION
713 ,HTM_TEXT
714 ,CREATED_BY
715 ,CREATION_DATE
716 ,LAST_UPDATED_BY
717 ,LAST_UPDATE_DATE
718 ,LAST_UPDATE_LOGIN
719 ) values
720 ( G_kid,
721 G_vid,
722 userenv('LANG'),
723 G_xid,
724 0,
725 G_mid,
726 empty_clob(),
727 fnd_global.user_id,
728 sysdate,
729 fnd_global.user_id,
730 sysdate,
731 fnd_global.login_id
732 );
733 select HTM_TEXT into x_htm_text
734 from okc_report_htm
735 where CHR_ID = G_kid
736 and MAJOR_VERSION = G_vid
737 and LANGUAGE = userenv('LANG')
738 and XST_ID = G_xid
739 and SCN_ID = 0
740 for update of HTM_TEXT nowait;
741 x_return_status := 'S';
742 if (G_ignore_cache = 'Y' or l_cached = 'N') then
743 x_clob_status := 'N';
744 else
745 x_clob_status := 'I';
746 end if;
747 return;
748 exception when others then
749 x_return_status := 'E';
750 x_msg_count := 1;
751 l_msg_data := cache_locked_msg('HTM');
752 x_msg_data := l_msg_data;
753 x_htm_text := EncodeClob('Error: OKC_REPORT_PVT.get_htm', l_msg_data);
754 x_clob_status := 'I';
755 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('OKC_REPORT_PVT');
756 end;
757
758
759 --
760 -- PROCEDURE/Function
761 -- private urlencode
762 --
763 -- PURPOSE
764 -- escapes parameter values
765 --
766 function urlencode (value varchar2) return varchar2 is
767 begin
768 return replace(replace(WF_MAIL.URLENCODE(value),'\','%5C'),':','%3A');
769 end;
770
771 --
772 -- PROCEDURE/Function
773 -- private failed_lock
774 --
775 -- PURPOSE
776 -- returns "lock failure" translated message
777 --
778 function failed_lock return varchar2
779 is
780 l_message varchar2(2000);
781 begin
782 fnd_message.clear;
783 fnd_message.set_name(APPLICATION=>OKC_API.G_FND_APP,NAME=>OKC_API.G_FORM_UNABLE_TO_RESERVE_REC);
784 return fnd_message.get;
785 end;
786
787 --
788 -- PROCEDURE/Function
789 -- private failed_session
790 --
791 -- PURPOSE
792 -- returns FND_SESSION_FAILED translated message
793 --
794 function failed_session return varchar2
795 is
796 l_message varchar2(2000);
797 begin
798 fnd_message.clear;
799 fnd_message.set_name(APPLICATION=>'FND',NAME=>'FND_SESSION_FAILED');
800 return fnd_message.get;
801 end;
802
803 --
804 -- PROCEDURE/Function
805 -- public set_env
806 --
807 -- PURPOSE
808 -- call only from JSP apllication (not from forms apps)
809 --
810 -- dinamicly executes <fnd_profile.value('OKC_WEB_ENVPROC')>
811 -- besides it sets okc_tree_index.set_root_id(p_kid):
812 -- SQL statements use okc_tree_index.get_root_id as Contact Id
813 --
814 procedure set_env(p_kid varchar2) is
815 cursor c1 is
816 select '!' from okc_k_headers_b
817 where id=p_kid;
818 l_dummy varchar2(1) := '?';
819 cursor c2 (p_xid number) is
820 select '!'
821 from
822 okc_report_xst X,
823 okc_report_prm_v P
824 where X.ID = p_xid
825 and P.sql_id = X.sql_id
826 and P.code not in ('xid', 'kid', 'vid', 'content_type');
827 l_value varchar2(200);
828 begin
829 okc_tree_index.set_root_id(p_kid);
830 G_xid := fnd_profile.value('OKC_WEB_REPORT');
831 G_kid := p_kid;
832 G_vid := OKC_PARAMETERS_PUB.Get('vid');
833 get_k_version(G_kid, G_vid, G_mid);
834 if (G_vid = -1) then
835 G_ignore_cache := 'Y';
836 else
837 open c2(G_xid);
838 fetch c2 into l_dummy;
839 close c2;
840 if (l_dummy = '!') then
841 G_ignore_cache := 'Y';
842 else
843 G_ignore_cache := 'N';
844 end if;
845 end if;
846 l_dummy := '?';
847 open c1;
848 fetch c1 into l_dummy;
849 close c1;
850 l_value := fnd_profile.value('OKC_WEB_ENVPROC');
851 if (l_value is NULL or upper(l_value) = 'OKC_CONTEXT.SET_OKC_ORG_CONTEXT') then
852 if (l_dummy = '!') then
853 okc_context.set_okc_org_context(p_chr_id => p_kid);
854 else
855 okc_context.set_okc_org_context(p_org_id => NULL, p_organization_id => NULL);
856 end if;
857 else
858 begin
859 if (l_dummy = '?') then
860 l_value := 'begin '||l_value||';end;';
861 execute immediate l_value;
862 else
863 l_value := 'begin '||l_value||'(:1); end;';
864 execute immediate l_value using in p_kid;
865 end if;
866 exception when others then
867 if (l_dummy = '?') then
868 l_value := 'begin '||l_value||'(p_chr_id => NULL);end;';
869 execute immediate l_value;
870 else
871 l_value := 'begin '||l_value||'(p_chr_id=> :1); end;';
872 execute immediate l_value using in p_kid;
873 end if;
874 end;
875 end if;
876 exception
877 when others then NULL;
878 end set_env;
879
880 --
881 -- PROCEDURE/Function
882 -- public set_env
883 --
884 -- PURPOSE - previous overload with extra in parameter
885 -- p_xid that is report id to set OKC_WEB_REPORT po
886 -- if not default report requested
887 --
888 -- call only from JSP apllication (not from forms apps)
889 --
890 -- dinamicly executes <fnd_profile.value('OKC_WEB_ENVPROC')>
891 -- besides it sets okc_tree_index.set_root_id(p_kid):
892 -- SQL statements use okc_tree_index.get_root_id as Contact Id
893 --
894 procedure set_env(
895 p_kid varchar2
896 ,p_xid varchar2
897 ) is
898 begin
899 if (p_xid is not NULL) then
900 fnd_profile.put('OKC_WEB_REPORT',p_xid);
901 end if;
902 set_env(p_kid);
903 exception
904 when others then NULL;
905 end set_env;
906
907 --1158
908 --
909 -- PROCEDURE/Function
910 -- public set_env
911 --
912 -- PURPOSE - previous overload with request parameter's set
913 --
914 -- dinamicly executes <fnd_profile.value('OKC_WEB_ENVPROC')>
915 -- besides it sets okc_tree_index.set_root_id(p_kid):
916 --
917 procedure set_env(p_array in JTF_VARCHAR2_TABLE_2000) is
918 cursor c1(p_kn varchar2) is
919 select to_char(id) from okc_k_headers_b
920 where contract_number = p_kn;
921 kid varchar2(40);
922 k_ind number;
923 k_ind1 number;
924 k_num varchar2(300);
925 begin
926 OKC_PARAMETERS_PUB.Set_Params(p_array);
927 k_ind := OKC_PARAMETERS_PUB.Get_Index('kid');
928 if (k_ind is not null) then
929 if (OKC_PARAMETERS_PUB.Get_Value(k_ind) is null) then
930 k_ind1 := OKC_PARAMETERS_PUB.Get_Index('_kid');
931 if (k_ind1 is not null) then
932 k_num := OKC_PARAMETERS_PUB.Get_Value(k_ind1);
933 if (k_num is not null) then
934 open c1(k_num);
935 fetch c1 into kid;
936 close c1;
937 if (kid is not null) then
938 OKC_PARAMETERS_PUB.Reset_Param(k_ind,kid);
939 end if;
940 end if;
941 end if;
942 else
943 kid := OKC_PARAMETERS_PUB.Get_Value(k_ind);
944 end if;
945 end if;
946 -- Changes for bugfix 3404142 start
947 -- set_env(kid, OKC_PARAMETERS_PUB.Get('xid'));
948 set_env(kid, fnd_profile.value('OKC_WEB_REPORT'));
949 -- Changes for bugfix 3404142 end
950 exception
951 when others then NULL;
952 end set_env;
953
954 function url_other_params(p_array in OKC_PARAMETERS_PUB.name_value_tbl_type) return varchar2 is
955 l_url varchar2(4000);
956 l_name varchar2(200);
957 l_value varchar2(4000);
958 i number;
959 c number;
960 begin
961 if (p_array is not null) then
962 c := p_array.COUNT;
963 if (c>0) then
964 i := p_array.FIRST;
965 while (i <= p_array.LAST) loop
966
967 l_name := p_array(i).NAME;
968 l_value := urlencode(p_array(i).VALUE);
969 if (length(l_url)+length(l_name)+length(l_value) <= 3990) then
970 l_url := l_url||l_name||'='||l_value||'&';
971 end if;
972 i := i+1;
973 end loop;
974 end if;
975 end if;
976 return l_url;
977 end;
978
979 --/1158
980
981 --
982 -- PROCEDURE/Function
983 -- public set_env
984 --
985 -- PURPOSE
986 -- should be called from FROMS application
987 --
988 -- 1. apps_initialize
989 -- 2. dbms_session.set_nls
990 -- 3. exec <fnd_profile.value('OKC_WEB_ENVPROC')>
991 -- 4. besides it sets okc_tree_index.set_root_id(p_kid)
992 -- SQL statements use okc_tree_index.get_root_id as Contact Id
993 --
994
995 procedure set_env( -- parameters names as in request
996 p_uid varchar2, -- User Id
997 p_rid varchar2, -- Resp Id
998 p_aid varchar2, -- Apps Id
999 p_gid varchar2, -- Group Id
1000 p_nlsl varchar2, -- NLS Language
1001 p_nlst varchar2, -- NLS Territory
1002 p_kid varchar2, -- Contract Id
1003 p_xid varchar2 -- Report Id
1004 ) is
1005 l_value varchar2(200);
1006 cursor nls_csr(param varchar2) is
1007 select value
1008 from NLS_SESSION_PARAMETERS
1009 where PARAMETER=param;
1010 begin
1011 fnd_global.apps_initialize(
1012 user_id => to_number(p_uid),
1013 resp_id => to_number(p_rid),
1014 resp_appl_id => to_number(p_aid),
1015 security_group_id => to_number(p_gid)
1016 );
1017 --
1018 -- set nls context if different
1019 --
1020 open nls_csr('NLS_LANGUAGE');
1021 fetch nls_csr into l_value;
1022 close nls_csr;
1023 if (l_value<>p_nlsl) then
1024 sys.dbms_session.set_nls('NLS_LANGUAGE',p_nlsl);
1025 end if;
1026 open nls_csr('NLS_TERRITORY');
1027 fetch nls_csr into l_value;
1028 close nls_csr;
1029 if (l_value<>p_nlst) then
1030 sys.dbms_session.set_nls('NLS_TERRITORY',p_nlst);
1031 end if;
1032 fnd_profile.put('OKC_WEB_REPORT',p_xid);
1033 set_env(p_kid);
1034 exception
1035 when others then NULL;
1036 end set_env;
1037
1038 --
1039 -- PROCEDURE/Function
1040 -- private report_url
1041 --
1042 -- PURPOSE
1043 -- returns url for forms apps to raise report
1044 --
1045 -- JSP:
1046 -- fnd_profile.value('APPS_SERVLET_AGENT')||'OA_HTML/okcFormsXmlPrint.jsp' (previously okcxmlkf.jsp)
1047 --
1048 -- parameters:
1049 --
1050 -- event=FRM
1051 -- dbc=v$instance.lower(host_name)||'_'||lower(instance_name)||'.dbc'
1052 -- kid=p_chr_id
1053 -- vid=p_major_version
1054 -- sid=p_scn_id
1055 -- nlsl=<NLS_LANGUAGE>
1056 -- nlst=<NLS_TERRITORY>
1057 -- uid=fnd_global.user_id
1058 -- rid=fnd_global.resp_id
1059 -- aid=fnd_global.resp_appl_id
1060 -- gid=fnd_global.security_group_id
1061 -- xid=xts_id
1062 --
1063
1064 function report_url(
1065 p_chr_id in NUMBER,
1066 p_major_version NUMBER,
1067 p_scn_id NUMBER )
1068 return varchar2 as
1069 l_path varchar2(200);
1070 l_template varchar2(200) := 'okcFormsXmlPrint.jsp';
1071 l_dbc varchar2(200);
1072 l_url varchar2(1000);
1073 l_value varchar2(200);
1074 begin
1075 l_path := fnd_profile.value('APPS_SERVLET_AGENT');
1076 select lower(host_name)||'_'||lower(instance_name)||'.dbc' into l_dbc
1077 from v$instance;
1078 l_url := l_path;
1079 if ((l_path is not NULL) and (substr(l_path,-1,1) <> '/')) then
1080 l_url := l_url||'/';
1081 end if;
1082 l_url := l_url||'OA_HTML/'||l_template||'?event=FRM';
1083 --
1084 l_url := l_url||'&'||'dbc='||l_dbc;
1085 l_url := l_url||'&'||'kid='||to_char(p_chr_id);
1086 l_url := l_url||'&'||'vid='||to_char(p_major_version);
1087 l_url := l_url||'&'||'sid='||to_char(p_scn_id);
1088 l_url := l_url||'&'||'xid='||fnd_profile.value('OKC_WEB_REPORT');
1089 --
1090 l_url := l_url||'&'||'nlsl=';
1091 select wf_mail.URLENCODE(value) into l_value
1092 from NLS_SESSION_PARAMETERS
1093 where PARAMETER='NLS_LANGUAGE';
1094 l_url := l_url||l_value;
1095 --
1096 l_url := l_url||'&'||'nlst=';
1097 select wf_mail.URLENCODE(value) into l_value
1098 from NLS_SESSION_PARAMETERS
1099 where PARAMETER='NLS_TERRITORY';
1100 l_url := l_url||l_value;
1101 --
1102 l_url := l_url||'&'||'uid='||to_char(fnd_global.user_id)
1103 ||'&'||'rid='||to_char(fnd_global.resp_id)
1104 ||'&'||'aid='||to_char(fnd_global.resp_appl_id)
1105 ||'&'||'gid='||to_char(fnd_global.security_group_id)
1106 ||'&'||'key='||substr(to_char(okc_p_util.raw_to_number(sys_guid())),-10)
1107 ;
1108 return l_url;
1109 end report_url;
1110
1111
1112 --
1113 -- PROCEDURE/Function
1114 -- public exec_OKC_WEB_PRERUN
1115 --
1116 -- PURPOSE
1117 --
1118 -- procedure executes <OKC_WEB_PRERUN> profile option
1119 -- site level value sample: 'OKC_REPORT_PVT.prerun'
1120 -- published only for jsp apps
1121 -- in forms apps call included in get_contract_url
1122 --
1123 -- signature of the procedure defined in <OKC_WEB_PRERUN> profile option
1124 -- should be the same as the sample OKC_REPORT_PVT.prerun
1125 --
1126 procedure exec_OKC_WEB_PRERUN(
1127 -- standard parameters
1128 p_api_version in NUMBER ,
1129 p_init_msg_list in VARCHAR2 ,
1130 x_return_status out NOCOPY VARCHAR2,
1131 x_msg_count out NOCOPY NUMBER,
1132 x_msg_data out NOCOPY VARCHAR2
1133 -- input parameters
1134 --1158 ,p_chr_id in NUMBER,
1135 --1158 p_major_version in NUMBER ,
1136 --1158 p_scn_id in NUMBER
1137 ) is
1138 l_value varchar2(2000);
1139 begin
1140 l_value := fnd_profile.value('OKC_WEB_PRERUN');
1141 if (l_value is NULL) then
1142 x_return_status := 'S';
1143 return;
1144 end if;
1145 if (upper(l_value) = 'OKC_REPORT_PVT.PRERUN') then
1146 OKC_REPORT_PVT.prerun(
1147 p_api_version => p_api_version,
1148 p_init_msg_list => p_init_msg_list,
1149 x_return_status => x_return_status,
1150 x_msg_count => x_msg_count,
1151 x_msg_data => x_msg_data,
1152 p_chr_id => G_kid,
1153 p_major_version => G_vid,
1154 p_scn_id => 0
1155 );
1156 else
1157 l_value := 'begin '||l_value||'('||
1158 'p_api_version => :p_api_version,'||
1159 'p_init_msg_list=> :p_init_msg_list,'||
1160 'x_return_status=> :x_return_status,'||
1161 'x_msg_count => :x_msg_count,'||
1162 'x_msg_data => :x_msg_data,'||
1163 'p_chr_id => :p_chr_id,'||
1164 'p_major_version=> :p_major_version,'||
1165 'p_scn_id => :p_scn_id'||
1166 ');end;';
1167 execute immediate l_value using
1168 in p_api_version,
1169 in p_init_msg_list,
1170 in out x_return_status,
1171 in out x_msg_count,
1172 in out x_msg_data,
1173 in G_kid,
1174 in G_vid,
1175 in 0;
1176 end if;
1177 exception
1178 when OTHERS then
1179 fnd_message.clear;
1180 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_UNEXPECTED_ERROR');
1181 fnd_message.set_token(TOKEN=>'ERROR_CODE',VALUE=>sqlcode);
1182 fnd_message.set_token(TOKEN=>'ERROR_MESSAGE',VALUE=>sqlerrm);
1183 x_return_status := 'U';
1184 x_msg_count := 1;
1185 x_msg_data := fnd_message.get;
1186 end;
1187
1188 --
1189 -- PROCEDURE/Function
1190 -- exec_OKC_WEB_LOG_RUN
1191 --
1192 -- PURPOSE
1193 --
1194 -- procedure executes <OKC_WEB_LOG_RUN> profile option
1195 -- site level value sample: 'OKC_REPORT_PVT.noop'
1196 -- noop means No Operation (knowhow belongs to WF team)
1197 -- called from bean both for FRM and JSP applications
1198 -- when real transformation happens, not just retrieve
1199 -- from cache
1200 --
1201 -- signature of the procedure defined in <OKC_WEB_LOG_RUN> profile option
1202 -- should be the same as the sample OKC_REPORT_PVT.noop
1203 --
1204 procedure exec_OKC_WEB_LOG_RUN(
1205 -- standard parameters
1206 p_api_version in NUMBER ,
1207 p_init_msg_list in VARCHAR2 ,
1208 x_return_status out NOCOPY VARCHAR2,
1209 x_msg_count out NOCOPY NUMBER,
1210 x_msg_data out NOCOPY VARCHAR2
1211 -- input parameters
1212 --1158 ,p_chr_id in NUMBER,
1213 --1158 p_major_version in NUMBER ,
1214 --1158 p_scn_id in NUMBER
1215 ) is
1216 l_value varchar2(2000);
1217 begin
1218 l_value := fnd_profile.value('OKC_WEB_LOG_RUN');
1219 if (l_value is NULL) then
1220 x_return_status := 'S';
1221 return;
1222 end if;
1223 if (upper(l_value) = 'OKC_REPORT_PVT.NOOP') then
1224 OKC_REPORT_PVT.noop(
1225 p_api_version => p_api_version,
1226 p_init_msg_list => p_init_msg_list,
1227 x_return_status => x_return_status,
1228 x_msg_count => x_msg_count,
1229 x_msg_data => x_msg_data,
1230 p_chr_id => G_kid,
1231 p_major_version => G_vid,
1232 p_scn_id => 0
1233 );
1234 else
1235 l_value := 'begin '||l_value||'('||
1236 'p_api_version => :p_api_version,'||
1237 'p_init_msg_list=> :p_init_msg_list,'||
1238 'x_return_status=> :x_return_status,'||
1239 'x_msg_count => :x_msg_count,'||
1240 'x_msg_data => :x_msg_data,'||
1241 'p_chr_id => :p_chr_id,'||
1242 'p_major_version=> :p_major_version,'||
1243 'p_scn_id => :p_scn_id'||
1244 ');end;';
1245 execute immediate l_value using
1246 in p_api_version,
1247 in p_init_msg_list,
1248 in out x_return_status,
1249 in out x_msg_count,
1250 in out x_msg_data,
1251 in G_kid,
1252 in G_vid,
1253 in 0;
1254 end if;
1255 exception
1256 when OTHERS then
1257 fnd_message.clear;
1258 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_UNEXPECTED_ERROR');
1259 fnd_message.set_token(TOKEN=>'ERROR_CODE',VALUE=>sqlcode);
1260 fnd_message.set_token(TOKEN=>'ERROR_MESSAGE',VALUE=>sqlerrm);
1261 x_return_status := 'U';
1262 x_msg_count := 1;
1263 x_msg_data := fnd_message.get;
1264 end;
1265
1266
1267 --
1268 -- PROCEDURE/Function
1269 -- public get_contract_url
1270 --
1271 -- PURPOSE
1272 --
1273 -- produces url to print contract from form:
1274 -- fnd_utilities.open_url(okc_report_pvt.report_url(:p_chr_id));
1275 -- takes care this url not reusable
1276 --
1277 procedure get_contract_url(
1278 -- standard parameters
1279 p_api_version in NUMBER ,
1280 p_init_msg_list in VARCHAR2 ,
1281 x_return_status out NOCOPY VARCHAR2,
1282 x_msg_count out NOCOPY NUMBER,
1283 x_msg_data out NOCOPY VARCHAR2,
1284 -- input parameters
1285 p_chr_id in NUMBER,
1286 p_major_version NUMBER ,
1287 p_scn_id NUMBER ,
1288 -- output parameters
1289 x_url out nocopy varchar2
1290 ) is
1291 l_chr_id number := p_chr_id;
1292 l_major_version number := p_major_version;
1293 l_minor_version number;
1294 l_scn_id number := NVL(p_scn_id,0);
1295 cursor k_header_csr is
1296 select '!'
1297 from OKC_K_HEADERS_TL
1298 where ID = p_chr_id
1299 and LANGUAGE = userenv('LANG')
1300 for update of last_update_date, last_updated_by
1301 nowait;
1302 l_dummy varchar2(1) := '?';
1303 begin
1304 DBMS_TRANSACTION.SAVEPOINT('OKC_REPORT_PVT');
1305
1306 exec_OKC_WEB_PRERUN(
1307 p_api_version,
1308 p_init_msg_list,
1309 x_return_status,
1310 x_msg_count,
1311 x_msg_data
1312 -- ,p_chr_id,
1313 -- p_major_version,
1314 -- p_scn_id
1315 );
1316 if (x_return_status<>'S') then
1317 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('OKC_REPORT_PVT');
1318 return;
1319 end if;
1320
1321 --
1322 -- get rid of default for major_version
1323 -- and retrieve minor version
1324 --
1325 get_k_version(l_chr_id, l_major_version, l_minor_version);
1326 open k_header_csr;
1327 fetch k_header_csr into l_dummy;
1328 close k_header_csr;
1329 if l_dummy = '?' then
1330 raise NO_DATA_FOUND;
1331 end if;
1332 update OKC_K_HEADERS_TL
1333 set last_updated_by = fnd_global.user_id,
1334 last_update_date = sysdate+1/144
1335 where ID = p_chr_id
1336 and LANGUAGE = userenv('LANG');
1337 x_url := report_url(p_chr_id,l_major_version, l_scn_id);
1338 commit;
1339 x_return_status := 'S';
1340 return;
1341 exception when others then
1342 x_return_status := 'E';
1343 x_msg_count := 1;
1344 x_msg_data := failed_lock;
1345 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('OKC_REPORT_PVT');
1346 end;
1347
1348 --
1349 -- PROCEDURE/Function
1350 -- check_access
1351 --
1352 -- PURPOSE
1353 --
1354 -- returns 'E' if request is obsolete;
1355 -- if not returns 'S';
1356 --
1357 procedure check_access(
1358 -- standard parameters
1359 p_api_version in NUMBER ,
1360 p_init_msg_list in VARCHAR2 ,
1361 x_return_status out NOCOPY VARCHAR2,
1362 x_msg_count out NOCOPY NUMBER,
1363 x_msg_data out NOCOPY VARCHAR2,
1364 -- input parameters
1365 p_chr_id in NUMBER
1366 ) is
1367 cursor k_header_csr is
1368 select '!'
1369 from OKC_K_HEADERS_TL
1370 where ID = p_chr_id
1371 and LANGUAGE = userenv('LANG')
1372 and last_updated_by = fnd_global.user_id
1373 and last_update_date > sysdate
1374 for update of last_update_date
1375 nowait;
1376 l_dummy varchar2(1) := '?';
1377 begin
1378 DBMS_TRANSACTION.SAVEPOINT('OKC_REPORT_PVT');
1379 open k_header_csr;
1380 fetch k_header_csr into l_dummy;
1381 close k_header_csr;
1382 if l_dummy = '?' then
1383 raise NO_DATA_FOUND;
1384 end if;
1385 -- for test commented out, later on place back
1386 -- update OKC_K_HEADERS_TL
1387 -- set last_update_date = sysdate
1388 -- where ID = p_chr_id and LANGUAGE = userenv('LANG');
1389 commit;
1390
1391 x_return_status := 'S';
1392 return;
1393 exception when others then
1394 x_return_status := 'E';
1395 x_msg_count := 1;
1396 x_msg_data := failed_session;
1397 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('OKC_REPORT_PVT');
1398 end;
1399
1400 --
1401 -- PROCEDURE/Function
1402 -- transformation_error
1403 --
1404 -- PURPOSE
1405 --
1406 -- returns translated message
1407 -- OKC_TRANSF_ERROR with tokens TRANSF_TYPE, TRANSF_NAME
1408 --
1409 function transformation_error(
1410 p_type in varchar2,
1411 p_id in number
1412 ) return varchar2 is
1413 l_message varchar2(2000);
1414 l_token varchar2(150);
1415 cursor c_sql(p_sql number) is
1416 select name from okc_report_sql_v
1417 where id=p_sql;
1418 cursor c_xsl(p_xsl number) is
1419 select name from okc_report_xsl_v
1420 where id=p_xsl;
1421 begin
1422 if (p_type='SQL') then
1423 open c_sql(p_id);
1424 fetch c_sql into l_token;
1425 close c_sql;
1426 else
1427 open c_xsl(p_id);
1428 fetch c_xsl into l_token;
1429 close c_xsl;
1430 end if;
1431 fnd_message.clear;
1432 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_TRANSF_ERROR');
1433 fnd_message.set_token(TOKEN=>'TRANSF_TYPE',VALUE=>p_type);
1434 fnd_message.set_token(TOKEN=>'TRANSF_NAME',VALUE=>'"'||l_token||'"');
1435 return fnd_message.get;
1436 end;
1437
1438 function return_mess(p_mess varchar2) return varchar2 is
1439 begin
1440 fnd_message.clear;
1441 fnd_message.set_name(APPLICATION => 'OKC',NAME => p_mess);
1442 return '<html><head><title>Error</title><head><body><ul><li><pre>'||fnd_message.get||'</pre></li></ul></body></html>';
1443 end;
1444
1445
1446 --
1447 -- PROCEDURE/Function
1448 -- check_transf_path
1449 --
1450 -- PURPOSE
1451 --
1452 -- returns 'S' if path valid
1453 -- else returns translated message
1454 -- OKC_INACTIVE_TRANSF
1455 -- tokens TRANSF_TYPE, TRANSF_NAME, PROFILE
1456 --
1457 function check_transf_path return varchar2 is
1458
1459 l_message varchar2(2000);
1460 l_token1 varchar2(240);
1461 l_token2 varchar2(240);
1462 l_token3 varchar2(240);
1463
1464 cursor template_csr is
1465 select
1466 'XSL' TEMPLATE_TYPE,
1467 S.NAME TEMPLATE_NAME
1468 from
1469 ( select XSL_ID,SQL_ID
1470 from OKC_REPORT_XST
1471 connect by ID = prior XST_ID
1472 start with ID = fnd_profile.value('OKC_WEB_REPORT')) T,
1473 OKC_REPORT_XSL_V S
1474 where T.XSL_ID = S.ID
1475 and not(sysdate between S.START_DATE and NVL(S.END_DATE,sysdate))
1476 union all
1477 select
1478 'SQL' TEMPLATE_TYPE,
1479 Q.NAME TEMPLATE_NAME
1480 from
1481 ( select XSL_ID,SQL_ID
1482 from OKC_REPORT_XST
1483 connect by ID = prior XST_ID
1484 start with ID = fnd_profile.value('OKC_WEB_REPORT')) T,
1485 OKC_REPORT_SQL_V Q
1486 where T.SQL_ID = Q.ID
1487 and not(sysdate between Q.START_DATE and NVL(Q.END_DATE,sysdate))
1488 ;
1489 cursor profile_options_csr is
1490 select '"'||USER_PROFILE_OPTION_NAME||'"'
1491 from fnd_profile_options_vl
1492 where application_id=510
1493 and PROFILE_OPTION_NAME = 'OKC_WEB_REPORT'
1494 ;
1495
1496 l_dummy varchar2(1):='?';
1497 cursor report_exists is
1498 select '!'
1499 from OKC_REPORT_XST T, OKC_REPORT_XSL_V L
1500 where T.ID = fnd_profile.value('OKC_WEB_REPORT')
1501 and T.XSL_ID = L.ID;
1502
1503 begin
1504 if (fnd_profile.value('OKC_WEB_REPORT') is NULL) then
1505 -- open profile_options_csr;
1506 -- fetch profile_options_csr into l_token3;
1507 -- close profile_options_csr;
1508 fnd_message.clear;
1509 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_INACTIVE_TRANSF');
1510 -- fnd_message.set_token(TOKEN=>'TRANSF_TYPE',VALUE=>l_token1);
1511 -- fnd_message.set_token(TOKEN=>'TRANSF_NAME',VALUE=>'"'||l_token2||'"');
1512 -- fnd_message.set_token(TOKEN=>'PROFILE',VALUE=>l_token3);
1513 return fnd_message.get;
1514 end if;
1515 open report_exists;
1516 fetch report_exists into l_dummy;
1517 close report_exists;
1518 if (l_dummy = '?') then
1519 -- open profile_options_csr;
1520 -- fetch profile_options_csr into l_token3;
1521 -- close profile_options_csr;
1522 fnd_message.clear;
1523 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_INACTIVE_TRANSF');
1524 -- fnd_message.set_token(TOKEN=>'TRANSF_TYPE',VALUE=>l_token1);
1525 -- fnd_message.set_token(TOKEN=>'TRANSF_NAME',VALUE=>'"'||l_token2||'"');
1526 -- fnd_message.set_token(TOKEN=>'PROFILE',VALUE=>l_token3);
1527 return fnd_message.get;
1528 end if;
1529
1530 open template_csr;
1531 fetch template_csr into l_token1, l_token2;
1532 close template_csr;
1533 if (l_token1 is not null) then
1534 open profile_options_csr;
1535 fetch profile_options_csr into l_token3;
1536 close profile_options_csr;
1537 fnd_message.clear;
1538 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_INACTIVE_TRANSF');
1539 -- fnd_message.set_token(TOKEN=>'TRANSF_TYPE',VALUE=>l_token1);
1540 -- fnd_message.set_token(TOKEN=>'TRANSF_NAME',VALUE=>'"'||l_token2||'"');
1541 -- fnd_message.set_token(TOKEN=>'PROFILE',VALUE=>l_token3);
1542 return fnd_message.get;
1543 else
1544 return 'S';
1545 end if;
1546 end;
1547
1548 --
1549 -- PROCEDURE/Function
1550 -- noop - mean No Operation - WF team knowhow
1551 --
1552 -- PURPOSE
1553 --
1554 -- sample dummy procedure for setting OKC_WEB_LOG_RUN
1555 -- profile option value on site level
1556 --
1557 -- what is important - signature
1558 --
1559 procedure noop(
1560 -- standard parameters
1561 p_api_version in NUMBER ,
1562 p_init_msg_list in VARCHAR2 ,
1563 x_return_status out NOCOPY VARCHAR2,
1564 x_msg_count out NOCOPY NUMBER,
1565 x_msg_data out NOCOPY VARCHAR2,
1566 -- input parameters
1567 p_chr_id in NUMBER,
1568 p_major_version NUMBER ,
1569 p_scn_id in NUMBER
1570 ) is
1571 begin
1572 x_return_status := 'S';
1573 end;
1574
1575 --
1576 -- PROCEDURE/Function
1577 -- private function data_required_msg
1578 -- called from prerun validation
1579 --
1580 -- PURPOSE
1581 --
1582 -- returns translated message OKC_DATA_REQUIRED
1583 -- Data required for some operation
1584 -- DATA_NAME data required for OPERATION
1585 --
1586 -- translatable token DATA_NAME
1587 -- OKC_SECTIONS Sections
1588 -- OKC_RULE_GROUPS Rule Groups
1589 -- translatable token OPERATION
1590 -- OKC_PRINT_CONTRACT Contract Printing
1591 --
1592
1593 function data_required_msg(p_data_name varchar2) return varchar2 is
1594 begin
1595 fnd_message.clear;
1596 FND_MESSAGE.SET_NAME(application => 'OKC',
1597 name => 'OKC_DATA_REQUIRED');
1598 FND_MESSAGE.SET_TOKEN(token => 'DATA_NAME',
1599 value => p_data_name,
1600 translate => TRUE);
1601 FND_MESSAGE.SET_TOKEN(token => 'OPERATION',
1602 value => 'OKC_PRINT_CONTRACT',
1603 translate => TRUE);
1604 return fnd_message.get;
1605 end;
1606
1607 --
1608 -- PROCEDURE/Function
1609 -- articles_warning
1610 --
1611 -- PURPOSE
1612 --
1613 -- included for use within Authoring Form only for PM demo
1614 --
1615 procedure articles_warning(
1616 -- standard parameters
1617 p_api_version in NUMBER ,
1618 p_init_msg_list in VARCHAR2 ,
1619 x_return_status out NOCOPY VARCHAR2,
1620 x_msg_count out NOCOPY NUMBER,
1621 x_msg_data out NOCOPY VARCHAR2,
1622 -- input parameters
1623 p_chr_id in NUMBER
1624 ) is
1625 l_dummy number;
1626 cursor articles_diff_csr(p_chr number) is
1627 select id
1628 from okc_k_articles_v
1629 where dnz_chr_id = p_chr
1630 and chr_id = p_chr
1631 minus
1632 SELECT SC.cat_id id
1633 from okc_section_contents_v SC
1634 WHERE SC.scn_id in
1635 (SELECT id
1636 from okc_sections_b
1637 connect by prior id = scn_id
1638 start with chr_id = p_chr
1639 and scn_id is NULL);
1640 begin
1641 open articles_diff_csr(p_chr_id);
1642 fetch articles_diff_csr into l_dummy;
1643 close articles_diff_csr;
1644 if (l_dummy is not NULL) then
1645 x_return_status := 'E';
1646 x_msg_count := 1;
1647 fnd_message.clear;
1648 FND_MESSAGE.SET_NAME(application => 'OKC', name => 'OKC_ARTICLES_WARNING');
1649 x_msg_data := fnd_message.get;
1650 return;
1651 end if;
1652 x_return_status := 'S';
1653 end;
1654
1655
1656 --
1657 -- PROCEDURE/Function
1658 -- prerun
1659 --
1660 -- PURPOSE
1661 --
1662 -- sample procedure, its name could be used
1663 -- to set profile option OKC_WEB_PRERUN
1664 -- performs sample validation tasks:
1665 -- checks if lines and sections are present in the contract
1666 --
1667 -- what is import - signature
1668 --
1669 procedure prerun(
1670 -- standard parameters
1671 p_api_version in NUMBER ,
1672 p_init_msg_list in VARCHAR2 ,
1673 x_return_status out NOCOPY VARCHAR2,
1674 x_msg_count out NOCOPY NUMBER,
1675 x_msg_data out NOCOPY VARCHAR2,
1676 -- input parameters
1677 p_chr_id in NUMBER,
1678 p_major_version in NUMBER ,
1679 p_scn_id in NUMBER
1680 ) is
1681 l_dummy varchar2(1);
1682 --
1683 -- sections required
1684 --
1685 cursor sections_csr(p_chr number) is
1686 select '!'
1687 from okc_sections_v
1688 where CHR_ID = p_chr
1689 ;
1690 --
1691 -- RG required at header level
1692 --
1693 cursor lines_csr(p_chr number) is
1694 select '!'
1695 from okc_k_lines_v
1696 where CHR_ID = p_chr
1697 ;
1698 begin
1699 l_dummy := '?';
1700 open sections_csr(p_chr_id);
1701 fetch sections_csr into l_dummy;
1702 close sections_csr;
1703 if (l_dummy = '?') then
1704 x_return_status := 'E';
1705 x_msg_count := 1;
1706 x_msg_data := data_required_msg('OKC_SECTIONS');
1707 return;
1708 end if;
1709 l_dummy := '?';
1710 open lines_csr(p_chr_id);
1711 fetch lines_csr into l_dummy;
1712 close lines_csr;
1713 if (l_dummy = '?') then
1714 x_return_status := 'E';
1715 x_msg_count := 1;
1716 x_msg_data := data_required_msg('OKC_K_LINES');
1717 return;
1718 end if;
1719 x_return_status := 'S';
1720 end;
1721
1722 --
1723 -- PROCEDURE/Function
1724 -- free_temp_clob
1725 --
1726 -- PURPOSE
1727 -- for internal use only
1728 -- just a wrapper of dbms_clob.freetemporary
1729 --
1730 procedure free_temp_clob(
1731 p_clob in out nocopy CLOB
1732 ) is
1733 begin
1734 if (dbms_lob.istemporary(lob_loc => p_clob) = 1) then
1735 dbms_lob.freetemporary(lob_loc => p_clob);
1736 end if;
1737 exception when others then NULL;
1738 end;
1739
1740 -- PROCEDURE/Function
1741 -- get_sql_name
1742 -- PURPOSE
1743 -- for internal use only
1744 --
1745 function get_sql_name(p_sql_id number) return varchar2 is
1746 cursor sql_csr is select name from okc_report_sql_v
1747 where id=p_sql_id;
1748 l_name varchar2(150);
1749 begin
1750 if p_sql_id is null then return NULL; end if;
1751 open sql_csr;
1752 fetch sql_csr into l_name;
1753 close sql_csr;
1754 return l_name;
1755 end;
1756
1757 -- PROCEDURE/Function
1758 -- public get_new_id
1759 -- PURPOSE
1760 -- for internal use only
1761 --
1762 function get_new_id return number is
1763 begin
1764 return okc_p_util.raw_to_number(sys_guid());
1765 end;
1766
1767 -- PROCEDURE/Function
1768 -- public get_new_id
1769 -- PURPOSE
1770 -- for internal use only
1771 --
1772 function get_new_id(p_entity varchar2) return number is
1773 l_user_id number := fnd_global.user_id;
1774 l_n number;
1775 begin
1776 if (l_user_id <> 1) then
1777 return okc_p_util.raw_to_number(sys_guid());
1778 else
1779 if (p_entity = 'SQL') then
1780 select NVL(max(id),10000)+1 into l_n
1781 from okc_report_sql_b
1782 where id between 10001 and 20000-1;
1783 elsif (p_entity = 'XSL') then
1784 select NVL(max(id),10000)+1 into l_n
1785 from okc_report_xsl_b
1786 where id between 10001 and 20000-1;
1787 elsif (p_entity = 'XST') then
1788 select NVL(max(id),10000)+1 into l_n
1789 from okc_report_xst
1790 where id between 10001 and 20000-1;
1791 end if;
1792 return l_n;
1793 end if;
1794 end;
1795
1796 -- Start of comments
1797 --
1798 -- Procedure Name : check_name_uk
1799 -- Description : checks if sql or xsl name is unique
1800 -- Business Rules :
1801 -- Parameters :
1802 -- Version : 1.0
1803 -- End of comments
1804
1805 function check_name_uk(p_entity varchar2) return varchar2 is
1806 cursor sql_csr is
1807 select name
1808 from okc_report_sql_v
1809 where nvl(end_date,sysdate)>=sysdate
1810 group by name
1811 having count(1)>1;
1812 cursor xsl_csr is
1813 select name
1814 from okc_report_xsl_v
1815 where nvl(end_date,sysdate)>=sysdate
1816 group by name
1817 having count(1)>1;
1818 l_name varchar2(150);
1819 begin
1820 if (p_entity = 'SQL') then
1821 open sql_csr;
1822 fetch sql_csr into l_name;
1823 close sql_csr;
1824 elsif (p_entity = 'XSL') then
1825 open xsl_csr;
1826 fetch xsl_csr into l_name;
1827 close xsl_csr;
1828 end if;
1829 if (l_name is not null) then
1830 fnd_message.clear;
1831 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_VALUE_NOT_UNIQUE');
1832 fnd_message.set_token(TOKEN=>'COL_NAME',VALUE=>l_name);
1833 return fnd_message.get;
1834 else
1835 return null;
1836 end if;
1837 end;
1838
1839 procedure set_message(p_message varchar2, p_code varchar2, p_prompt varchar2, p_value varchar2) is
1840 begin
1841 OKC_API.SET_MESSAGE(p_app_name => 'OKC'
1842 ,p_msg_name => p_message
1843 ,p_token1 => 'NAME'
1844 ,p_token1_value => p_prompt
1845 ,p_token2 => 'VALUE'
1846 ,p_token2_value => p_value
1847 );
1848 OKC_PARAMETERS_PUB.Reset_Param(OKC_PARAMETERS_PUB.Get_Index(p_code),NULL);
1849 end;
1850
1851 procedure set_sql_id is
1852 cursor c_sql_id is
1853 select T.SQL_ID
1854 from OKC_REPORT_XST T, OKC_REPORT_XSL_V L
1855 where T.ID = fnd_profile.value('OKC_WEB_REPORT')
1856 and T.XSL_ID = L.ID;
1857 begin
1858 G_sql_id := null;
1859 if (fnd_profile.value('OKC_WEB_REPORT') is NULL) then
1860 OKC_API.SET_MESSAGE(p_app_name => 'OKC'
1861 ,p_msg_name => 'OKC_INACTIVE_TRANSF'
1862 );
1863 return;
1864 end if;
1865 open c_sql_id;
1866 fetch c_sql_id into G_sql_id;
1867 close c_sql_id;
1868 if (G_sql_id is NULL) then
1869 OKC_API.SET_MESSAGE(p_app_name => 'OKC'
1870 ,p_msg_name => 'OKC_INACTIVE_TRANSF');
1871 end if;
1872
1873 end;
1874
1875 -- Start of comments
1876 --
1877 -- Procedure Name : validate parameters
1878 -- Description : returns errors #
1879 -- Business Rules :
1880 -- Parameters :
1881 -- Version : 1.0
1882 -- End of comments
1883
1884 function validate_params(p_required_yn varchar2 ) return number is
1885 cursor c1(p number) is
1886 select code,
1887 prompt,
1888 type,
1889 required_yn,
1890 lov_query,
1891 OKC_PARAMETERS_PUB.get(code) value
1892 from OKC_REPORT_PRM_V
1893 where sql_id = p
1894 order by sequence_num;
1895 r c1%ROWTYPE;
1896
1897 type ct is ref cursor;
1898 c2 ct;
1899 n number;
1900 ni number;
1901 m varchar2(4000);
1902 nv varchar2(4000);
1903
1904 begin
1905 fnd_msg_pub.initialize;
1906 --
1907 -- validate types, dependencies, spawn fnd_messages
1908 --
1909 set_sql_id;
1910 if (G_sql_id is NULL) then
1911 return -1;
1912 end if;
1913 for r in c1(G_sql_id) LOOP
1914 if ( (p_required_yn = 'Y') and (r.required_yn = 'Y') ) then
1915 nv := r.value;
1916 ni := OKC_PARAMETERS_PUB.get_index('_'||r.code);
1917 if (ni is not NULL) then
1918 nv := OKC_PARAMETERS_PUB.get_value(ni);
1919 end if;
1920 if (nv is NULL) then
1921 OKC_API.SET_MESSAGE(p_app_name => 'OKC'
1922 ,p_msg_name => 'OKC_REQUIRED_FIELD'
1923 ,p_token1 => 'FIELD_NAME'
1924 ,p_token1_value => '"'||r.prompt||'"'
1925 );
1926 end if;
1927 end if;
1928 if (r.type = 'VARCHAR2') then
1929 GOTO continue;
1930 end if;
1931 if ( (r.type = 'NUMBER') and (r.value is not NULL) ) then
1932 begin
1933 if (to_number(r.value) is not NULL) then
1934 GOTO continue;
1935 end if;
1936 exception when others then
1937 set_message('OKC_XML_PARAM_NUMBER', r.code, r.prompt, r.value);
1938 GOTO continue;
1939 end;
1940 end if;
1941 if ( (r.type = 'DATE') and (r.value is not NULL) ) then
1942 begin
1943 if (to_date(r.value,'YYYY/MM/DD') is not NULL) then
1944 GOTO continue;
1945 end if;
1946 exception when others then
1947 set_message('OKC_XML_PARAM_DATE', r.code, r.prompt, r.value);
1948 GOTO continue;
1949 end;
1950 end if;
1951
1952 if (r.type = 'LOV') then
1953 ni := OKC_PARAMETERS_PUB.get_index('_'||r.code);
1954 if (ni is NULL) then
1955 if (r.value is NULL) then
1956 GOTO continue;
1957 else
1958 begin
1959 n := NULL;
1960 open c2 for 'select 1 a from ('||r.lov_query||') where id = :1' using r.value;
1961 fetch c2 into n;
1962 close c2;
1963 if (n is NULL) then
1964 set_message('OKC_XML_PARAM_LOV', r.code, r.prompt, r.value);
1965 end if;
1966 GOTO continue;
1967 exception when others then
1968 if (c2%ISOPEN) then close c2; end if;
1969 set_message('OKC_XML_PARAM_LOV', r.code, r.prompt, r.value);
1970 GOTO continue;
1971 end;
1972 end if;
1973 else
1974 begin
1975 nv := OKC_PARAMETERS_PUB.get_value(ni);
1976
1977 if (nv is NULL) then
1978 OKC_PARAMETERS_PUB.Reset_Param(OKC_PARAMETERS_PUB.Get_Index(r.code),NULL);
1979 GOTO continue;
1980 end if;
1981
1982 n := NULL;
1983 open c2 for
1984 'select 1 a from ('||r.lov_query||') where id = :1 and name=:2'
1985 using r.value, nv;
1986 fetch c2 into n;
1987 close c2;
1988 if (n is not NULL) then
1989 GOTO continue;
1990 end if;
1991
1992 m := NULL;
1993 open c2 for
1994 'select id from ('||r.lov_query||') where name=:2'
1995 using nv;
1996 fetch c2 into m;
1997 close c2;
1998 if (m is not NULL) then
1999 OKC_PARAMETERS_PUB.Reset_Param(OKC_PARAMETERS_PUB.Get_Index(r.code),m);
2000 GOTO continue;
2001 end if;
2002 open c2 for
2003 'select id from ('||r.lov_query||') where name like :2'
2004 using nv;
2005 fetch c2 into m;
2006 close c2;
2007 if (m is not NULL) then
2008 OKC_PARAMETERS_PUB.Reset_Param(OKC_PARAMETERS_PUB.Get_Index(r.code),m);
2009 GOTO continue;
2010 end if;
2011 set_message('OKC_XML_PARAM_LOV', r.code, r.prompt, nv);
2012 GOTO continue;
2013 exception when others then
2014 if (c2%ISOPEN) then close c2;
2015 end if;
2016 set_message('OKC_XML_PARAM_LOV', r.code, r.prompt, nv);
2017 GOTO continue;
2018 end;
2019 end if;
2020 end if;
2021 <<continue>> NULL;
2022 end LOOP;
2023
2024 return fnd_msg_pub.Count_Msg;
2025 end;
2026
2027 function set_and_validate(p_array in JTF_VARCHAR2_TABLE_2000, p_required_yn varchar2 ) return number is
2028 begin
2029 set_env(p_array);
2030 return validate_params(p_required_yn);
2031 end;
2032
2033 function get_zero_mess return varchar2 is
2034 begin
2035 fnd_message.clear;
2036 fnd_message.set_name(APPLICATION=>'CN',NAME=>'CN_NO_RECS_FOUND');
2037 return replace(fnd_message.get,'<',fnd_global.local_chr(38)||'lt;');
2038 end;
2039
2040 function get_much_mess return varchar2 is
2041 begin
2042 fnd_message.clear;
2043 fnd_message.set_name(APPLICATION=>'PER',NAME=>'HR_WEB_RETRIEVE_LIMIT_EXCEEDED');
2044 return replace(fnd_message.get,'<',fnd_global.local_chr(38)||'lt;');
2045 end;
2046
2047 procedure get_labels(
2048 x_title out nocopy varchar2,
2049 x_prompt out nocopy varchar2,
2050 x_message out nocopy varchar2,
2051 x_name out nocopy varchar2,
2052 x_description out nocopy varchar2,
2053 x_sql out nocopy varchar2) is
2054 cursor c1 is
2055 select replace(prompt,'<',fnd_global.local_chr(38)||'lt;'), lov_query
2056 from okc_report_prm_v PRM, okc_report_xst_v XST
2057 where PRM.sql_id = XST.sql_id
2058 and XST.id = fnd_profile.value('OKC_WEB_REPORT')
2059 and PRM.code = OKC_PARAMETERS_PUB.get('__param_code');
2060 cursor c2 is
2061 select replace(MEANING,'<',fnd_global.local_chr(38)||'lt;') from fnd_lookups
2062 where LOOKUP_TYPE='OKS_ITEM_DISPLAY_PREFERENCE'
2063 and lookup_code='DISPLAY_NAME';
2064 cursor c3 is
2065 select replace(MEANING,'<',fnd_global.local_chr(38)||'lt;') from fnd_lookups
2066 where LOOKUP_TYPE='OKS_ITEM_DISPLAY_PREFERENCE'
2067 and lookup_code='DISPLAY_DESC';
2068 cursor c4 is
2069 select replace(description,'<',fnd_global.local_chr(38)||'lt;') from fnd_lookups
2070 where LOOKUP_TYPE='FLEX_VALIDATION_EVENTS'
2071 and LOOKUP_CODE='Q';
2072
2073 begin
2074 open c4;
2075 fetch c4 into x_title;
2076 close c4;
2077 open c2;
2078 fetch c2 into x_name;
2079 close c2;
2080 open c3;
2081 fetch c3 into x_description;
2082 close c3;
2083 if (fnd_profile.value('OKC_WEB_REPORT') is NULL) then
2084 fnd_message.clear;
2085 fnd_message.set_name(APPLICATION=>'CN',NAME=>'CN_NO_RECS_FOUND');
2086 x_message := fnd_message.get;
2087 return;
2088 else
2089 open c1;
2090 fetch c1 into x_prompt,x_sql;
2091 close c1;
2092 end if;
2093 end;
2094
2095 procedure get_actions(
2096 x_run1 out nocopy varchar2,
2097 x_run2 out nocopy varchar2,
2098 x_close out nocopy varchar2) is
2099 begin
2100 fnd_message.clear;
2101 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_XML_RUN1');
2102 x_run1 := replace(fnd_message.get,'<',fnd_global.local_chr(38)||'lt;');
2103 fnd_message.clear;
2104 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_XML_RUN2');
2105 x_run2 := replace(fnd_message.get,'<',fnd_global.local_chr(38)||'lt;');
2106 fnd_message.clear;
2107 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_XML_CLOSE');
2108 x_close := replace(fnd_message.get,'<',fnd_global.local_chr(38)||'lt;');
2109 end;
2110
2111 procedure document_link(
2112 p_kid in NUMBER,
2113 p_vid in NUMBER,
2114 p_xid in NUMBER,
2115 p_document in out NOCOPY CLOB) as
2116
2117 cursor get_htm_text(pp_kid number, pp_vid number, pp_xid number) is
2118 select HTM_TEXT
2119 from okc_report_htm_v
2120 where CHR_ID = pp_kid
2121 and MAJOR_VERSION = pp_vid
2122 and XST_ID = pp_xid;
2123 c1 clob;
2124
2125 begin
2126 open get_htm_text(p_kid,p_vid,p_xid);
2127 fetch get_htm_text into c1;
2128 -- fetch get_htm_text into p_document;
2129 close get_htm_text;
2130 DBMS_LOB.COPY(dest_lob => p_document,src_lob => c1, amount => dbms_lob.getlength(c1));
2131 end;
2132
2133 --
2134 -- sample post-report trigger to send message to
2135 -- the wf_role passed in P_RECIPIENT parameter
2136 --
2137 procedure post_message(
2138 p_api_version in NUMBER ,
2139 p_init_msg_list in VARCHAR2 ,
2140 x_return_status out NOCOPY VARCHAR2,
2141 x_msg_count out NOCOPY NUMBER,
2142 x_msg_data out NOCOPY VARCHAR2,
2143 p_chr_id in NUMBER,
2144 p_major_version NUMBER ,
2145 p_scn_id in NUMBER ) is
2146
2147 P_RECIPIENT varchar2(100) := OKC_PARAMETERS_PUB.get('P_RECIPIENT');
2148 P_SUBJECT varchar2(2000) := OKC_PARAMETERS_PUB.get('P_SUBJECT');
2149 P_BODY varchar2(2000) := OKC_PARAMETERS_PUB.get('P_BODY');
2150 P_XID varchar2(40) := fnd_profile.value('OKC_WEB_REPORT');
2151
2152 l_dummy varchar2(1) := '?';
2153
2154 cursor c1(k number) is
2155 select
2156 Contract_number||
2157 decode(Contract_number_modifier,NULL,NULL,' - ')||
2158 Contract_number_modifier
2159 from okc_k_headers_b
2160 where id = k;
2161
2162 cursor c2(x varchar2) is
2163 select
2164 S.NAME
2165 from okc_report_xst T,
2166 okc_report_sql_v Q,
2167 okc_report_xsl_v S
2168 where T.id = x
2169 and T.sql_id = Q.id
2170 and T.xsl_id = S.id;
2171 begin
2172 x_return_status := 'S';
2173 if (P_RECIPIENT is null) then
2174 return;
2175 end if;
2176 if (P_SUBJECT is NULL) then
2177 open c1(p_chr_id);
2178 fetch c1 into P_SUBJECT;
2179 close c1;
2180 end if;
2181 if (P_SUBJECT is NULL) then
2182 open c2(P_XID);
2183 fetch c2 into P_SUBJECT;
2184 close c2;
2185 end if;
2186
2187 OKC_ASYNC_PUB.send_doc(
2188 p_api_version => 1,
2189 p_init_msg_list => 'T',
2190 x_return_status => x_return_status,
2191 x_msg_count => x_msg_count,
2192 x_msg_data => x_msg_data,
2193 p_recipient => P_RECIPIENT,
2194 p_msg_subj => P_SUBJECT,
2195 p_msg_body => P_BODY,
2196 p_proc => 'begin OKC_REPORT_PVT.document_link('
2197 ||'p_kid => '||p_chr_id||','
2198 ||'p_vid => '||NVL(to_char(p_major_version),'NULL')||','
2199 ||'p_xid => '||P_XID||','
2200 ||'p_document => '||':1); end;');
2201
2202 end post_message;
2203
2204 --
2205 -- send document from cache
2206 --
2207 procedure send_document(
2208 p_recipient varchar2,
2209 p_subject varchar2,
2210 p_body varchar2 ,
2211 p_chr_id in NUMBER,
2212 p_major_version NUMBER,
2213 p_xst_id NUMBER) is
2214
2215 x_return_status VARCHAR2(1);
2216 x_msg_count NUMBER;
2217 x_msg_data VARCHAR2(2000);
2218
2219 begin
2220 if ((p_recipient is NULL) or (p_recipient = fnd_global.local_chr(0))) then
2221 return;
2222 end if;
2223 OKC_ASYNC_PUB.send_doc(
2224 p_api_version => 1,
2225 p_init_msg_list => 'T',
2226 x_return_status => x_return_status,
2227 x_msg_count => x_msg_count,
2228 x_msg_data => x_msg_data,
2229 p_recipient => p_recipient,
2230 p_msg_subj => p_subject,
2231 p_msg_body => p_body,
2232 p_proc => 'begin OKC_REPORT_PVT.document_link('
2233 ||'p_kid => '||p_chr_id||','
2234 ||'p_vid => '||NVL(to_char(p_major_version),'NULL')||','
2235 ||'p_xid => '||P_XST_ID||','
2236 ||'p_document => '||':1); end;');
2237 end send_document;
2238
2239 --
2240 -- send document from cache
2241 --
2242 procedure conc_send_document(
2243 errbuf out nocopy varchar2,
2244 retcode out nocopy varchar2,
2245 p_recipient varchar2,
2246 p_cc varchar2 ,
2247 p_bcc varchar2 ,
2248 p_subject varchar2,
2249 p_body varchar2 ,
2250 p_xst_id NUMBER,
2251 p_chr_id NUMBER,
2252 p_major_version NUMBER ) is
2253 v number := p_major_version;
2254 cursor c1(k number) is
2255 select
2256 MAJOR_VERSION
2257 from OKC_K_VERS_NUMBERS_V
2258 where CHR_ID = k;
2259 begin
2260 errbuf := '';
2261 retcode := '0';
2262 if (p_major_version is NULL) then
2263 open c1(p_chr_id);
2264 fetch c1 into v;
2265 close c1;
2266 end if;
2267 if (v is NULL) then
2268 v:= -1;
2269 end if;
2270 send_document(
2271 p_recipient,
2272 p_subject,
2273 p_body,
2274 p_chr_id,
2275 v,
2276 p_xst_id);
2277 send_document(
2278 p_cc,
2279 p_subject,
2280 p_body,
2281 p_chr_id,
2282 v,
2283 p_xst_id);
2284 send_document(
2285 p_bcc,
2286 p_subject,
2287 p_body,
2288 p_chr_id,
2289 v,
2290 p_xst_id);
2291 end;
2292
2293 procedure conc_send_error(
2294 errbuf out nocopy varchar2,
2295 retcode out nocopy varchar2,
2296 p_recipient varchar2,
2297 p_cc varchar2 ,
2298 p_xst_id NUMBER,
2299 p_chr_id in NUMBER) is
2300
2301 cursor c1(k number) is
2302 select
2303 Contract_number
2304 ,Contract_number_modifier
2305 from okc_k_headers_b
2306 where id = k;
2307
2308 l_n varchar2(200);
2309 l_m varchar2(200);
2310
2311 cursor c2(x varchar2) is
2312 select
2313 Q.NAME NAME,
2314 S.NAME DESCRIPTION
2315 from okc_report_xst T,
2316 okc_report_sql_tl Q,
2317 okc_report_xsl_tl S
2318 where T.id = x
2319 and T.sql_id = Q.id
2320 and T.xsl_id = S.id
2321 and Q.LANGUAGE = userenv('LANG')
2322 and S.LANGUAGE = userenv('LANG')
2323 ;
2324
2325 l_q varchar2(200);
2326 l_x varchar2(200);
2327
2328 l_s varchar2(2000);
2329 l_b varchar2(2000);
2330
2331 x_return_status varchar2(1);
2332 x_msg_count number;
2333 x_msg_data varchar2(2000);
2334
2335 begin
2336 errbuf := '';
2337 retcode := '0';
2338 if ((p_recipient is NULL) or (p_recipient = fnd_global.local_chr(0))) then
2339 return;
2340 end if;
2341 open c1(p_chr_id);
2342 fetch c1 into l_n,l_m;
2343 close c1;
2344 open c2(p_xst_id);
2345 fetch c2 into l_q,l_x;
2346 close c2;
2347
2348 fnd_message.clear;
2349 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_XML_ERROR_SUBJECT');
2350 fnd_message.set_token(TOKEN => 'NAME' ,VALUE => l_q);
2351 fnd_message.set_token(TOKEN => 'DESCRIPTION',VALUE => l_x);
2352 fnd_message.set_token(TOKEN => 'NUMBER' ,VALUE => l_n);
2353 fnd_message.set_token(TOKEN => 'MODIFIER' ,VALUE => l_m);
2354 l_s := fnd_message.get;
2355
2356 fnd_message.clear;
2357 fnd_message.set_name(APPLICATION=>'OKC',NAME=>'OKC_XML_ERROR');
2358 fnd_message.set_token(TOKEN => 'NAME' ,VALUE => l_q);
2359 fnd_message.set_token(TOKEN => 'DESCRIPTION',VALUE => l_x);
2360 fnd_message.set_token(TOKEN => 'NUMBER' ,VALUE => l_n);
2361 fnd_message.set_token(TOKEN => 'MODIFIER' ,VALUE => l_m);
2362 l_b := fnd_message.get;
2363
2364 OKC_ASYNC_PUB.msg_call(
2365 p_api_version => 1,
2366 x_return_status => x_return_status,
2367 x_msg_count => x_msg_count,
2368 x_msg_data => x_msg_data,
2369 p_recipient => p_recipient,
2370 p_msg_subj => l_s,
2371 p_msg_body => l_b,
2372 p_contract_id => p_chr_id);
2373 if ((p_cc is NULL) or (p_cc = fnd_global.local_chr(0))) then
2374 return;
2375 end if;
2376 OKC_ASYNC_PUB.msg_call(
2377 p_api_version => 1,
2378 x_return_status => x_return_status,
2379 x_msg_count => x_msg_count,
2380 x_msg_data => x_msg_data,
2381 p_recipient => p_cc,
2382 p_msg_subj => l_s,
2383 p_msg_body => l_b,
2384 p_contract_id => p_chr_id);
2385 end;
2386
2387 function run_report_and_send(
2388 p_recipient varchar2,
2389 p_cc varchar2 ,
2390 p_bcc varchar2 ,
2391 p_subject varchar2,
2392 p_body varchar2 ,
2393 p_xst_id NUMBER,
2394 p_chr_id NUMBER,
2395 p_major_version NUMBER ,
2396 p_err_recipient varchar2 ,
2397 p_err_cc varchar2
2398 ) return number is
2399 success boolean;
2400 begin
2401 success := fnd_submit.set_request_set('OKC','KXMLREPORT_SET');
2402 if (not(success)) then return NULL; end if;
2403 success := fnd_submit.submit_program('OKC','KXMLREPORT','KXMLREPORT',p_chr_id,p_xst_id);
2404 if (not(success)) then return NULL; end if;
2405 success := fnd_submit.submit_program('OKC','OKC_XML_MESS1','OKC_XML_MESS1',P_RECIPIENT,P_CC,P_BCC,P_SUBJECT,
2406 P_BODY, P_XST_ID,P_CHR_ID, P_MAJOR_VERSION);
2407 if (not(success)) then return NULL; end if;
2408 success := fnd_submit.submit_program('OKC','OKC_XML_MESS2','OKC_XML_MESS2',P_ERR_RECIPIENT,P_ERR_CC,P_XST_ID,
2409 P_CHR_ID);
2410 if (not(success)) then return NULL; end if;
2411 return fnd_submit.submit_set(null,FALSE);
2412 end;
2413
2414 end OKC_REPORT_PVT;