[Home] [Help]
PACKAGE: APPS.OKC_REPORT_PVT
Source
1 package OKC_REPORT_PVT as
2 /* $Header: OKCRXPKS.pls 120.0.12010000.2 2008/10/24 08:03:04 ssreekum ship $ */
3
4 --
5 -- Print Contract Process API methods
6 --
7 -- get_xsl_help
8 -- get_art_help
9 -- get_xml
10 -- get_htm
11 -- set_env
12 -- get_contract_url
13 -- check_access
14 -- transformation_error
15 -- check_transf_path
16 -- exec_OKC_WEB_PRERUN
17 -- prerun
18 -- exec_OKC_WEB_LOG_RUN
19 -- noop
20 -- free_temp_clob
21 -- get_k_version
22 --
23
24 --
25 -- Dependencies:
26 --
27 -- okc_tree_index (.set_root_id)
28 -- wf_mail (.urlencode)
29 --
30
31 --
32 -- Profile values used:
33 --
34 -- OKC_WEB_DBC
35 -- OKC_WEB_PATH
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 --
53 -- get_xsl_help
54 --
55 -- returns help text for XSL designer in format
56 -- <html>
57 -- <head><title>
58 -- "?" -> okc_report_sql_v.name
59 -- </title></head>
60 -- <body><pre>
61 -- okc_report_sql_v.help_text
62 -- '&' and '<' replaced with '&'||'amp;' and '&'||'lt;'
63 -- </pre></body>
64 -- </html>
65 --
66 function get_xsl_help(
67 p_sql_id number default NULL -- if null derive from OKC_WEB_REPORT prof.
68 ) return clob;
69
70 --
71 -- get_art_help
72 --
73 -- returns help text for standard atricles designer
74 -- similar to previous method based on view okc_report_xsl_v
75 --
76 function get_art_help(
77 p_xsl_id number default NULL -- if null derive from OKC_WEB_REPORT prof.
78 ) return clob;
79
80 --
81 -- get_xml
82 --
83 -- should be called only for "cached" sql transformations
84 -- returns datagram from cache if it is still valid,
85 -- or updates/inserts clob to put there result on XSQL transformation
86 --
87 procedure get_xml(
88 -- standard parameters
89 p_api_version in NUMBER default 1,
90 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
91 x_return_status out nocopy VARCHAR2,
92 x_msg_count out nocopy NUMBER,
93 x_msg_data out nocopy VARCHAR2,
94 -- input parameters
95 --1158 p_chr_id in NUMBER,
96 --1158 p_major_version NUMBER default NULL, -- NULL stands for the latest
97 --1158 p_sql_id in NUMBER default NULL, -- if null derive from OKC_WEB_REPORT prof.
98 p_run_anyway_yn in varchar2 default 'N',
99 -- output parameters
100 x_xml_text out nocopy CLOB,
101 x_clob_status out nocopy VARCHAR2
102 --'I' - inserted empty, or old and obsolete; locked
103 --'V' - valid not locked
104 );
105
106 --
107 -- get_htm
108 --
109 -- should be called only for "cached" xsl transformations
110 -- returns datagram from cache if it is still valid,
111 -- or updates/inserts clob to put there result on XSL transformation
112 --
113 procedure get_htm(
114 -- standard parameters
115 p_api_version in NUMBER default 1,
116 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
117 x_return_status out nocopy VARCHAR2,
118 x_msg_count out nocopy NUMBER,
119 x_msg_data out nocopy VARCHAR2,
120 -- input parameters
121 --1158 p_chr_id in NUMBER,
122 --1158 p_major_version NUMBER default NULL, -- NULL stands for the latest
123 --1158 p_xst_id in NUMBER default NULL, -- if null derive from OKC_WEB_REPORT prof.
124 --1158 p_scn_id in NUMBER default NULL, -- NULL for all top sections
125 p_run_anyway_yn in varchar2 default 'N',
126 -- output parameters
127 x_htm_text out nocopy CLOB,
128 x_clob_status out nocopy VARCHAR2
129 --'I' - inserted empty, or old and obsolete; locked
130 --'V' - valid not locked
131 );
132
133 --
134 -- public set_env
135 --
136 -- exec <fnd_profile.value('OKC_WEB_ENVPROC')>
137 -- besides it sets okc_tree_index.set_root_id(p_kid)
138 -- for SQL statement
139 -- should be called from JSP apllication
140 --
141 procedure set_env(
142 p_kid varchar2
143 );
144
145 --
146 -- public set_env
147 --
148 -- exec <fnd_profile.value('OKC_WEB_ENVPROC')>
149 -- besides it sets okc_tree_index.set_root_id(p_kid)
150 -- for SQL statement
151 -- should be called from JSP apllication
152 --
153 --1158 procedure set_env(
154 --1158 p_kid varchar2
155 --1158 ,p_xid varchar2
156 --1158 );
157
158 --1158
159
160 --
161 -- PROCEDURE/Function
162 -- public set_env
163 --
164 -- PURPOSE - previous overload with request parameter's set
165 --
166 -- dinamicly executes <fnd_profile.value('OKC_WEB_ENVPROC')>
167 --
168 -- for backward compatibility:
169 -- sets okc_tree_index.set_root_id(kid):
170 -- fnd_profile.put('OKC_WEB_REPORT',xid);
171 --
172 -- you should take care that xid and kid from context (if there)
173 -- should go to params list (if they are not there yet)
174 --
175 procedure set_env(p_array in JTF_VARCHAR2_TABLE_2000);
176
177 function url_other_params(p_array in OKC_PARAMETERS_PUB.name_value_tbl_type) return varchar2;
178
179
180 --
181 -- private report_url, published only for debug
182 --
183 -- returns url of
184 --
185 -- fnd_profile.value('OKC_WEB_PATH')/xmlK.jsp
186 --
187 -- with parameters:
188 --
189 -- event=FRM
190 -- dbc=fnd_profile.value('OKC_WEB_DBC')
191 -- kid=p_chr_id
192 -- vid=p_major_version
193 -- sid=p_scn_id
194 -- nlsl=<NLS_LANGUAGE>
195 -- nlst=<NLS_TERRITORY>
196 -- uid=fnd_global.user_id
197 -- rid=fnd_global.resp_id
198 -- aid=fnd_global.resp_appl_id
199 -- gid=fnd_global.security_group_id
200 -- xid=xst_id
201 --
202 function report_url(
203 p_chr_id in NUMBER,
204 p_major_version NUMBER,
205 p_scn_id NUMBER )
206 return varchar2;
207
208 --
209 -- get_contract_url
210 --
211 -- produces url to print contract from form:
212 -- fnd_utilities.open_url(okc_report_pvt.report_url(:p_chr_id));
213 -- takes care this url be for one shot
214 --
215 procedure get_contract_url(
216 -- standard parameters
217 p_api_version in NUMBER default 1,
218 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
219 x_return_status out nocopy VARCHAR2,
220 x_msg_count out nocopy NUMBER,
221 x_msg_data out nocopy VARCHAR2,
222 -- input parameters
223 p_chr_id in NUMBER,
224 p_major_version NUMBER default NULL, -- NULL stands for the latest
225 p_scn_id NUMBER default NULL, -- NULL for all top sections
226 -- output parameters
227 x_url out nocopy varchar2
228 );
229
230 --
231 -- check_access
232 --
233 -- returns 'E' if request is obsolete;
234 -- if not then marks it as obsolete and returns 'S';
235 -- takes care get_contract_url.x_url be for one shot
236 --
237 procedure check_access(
238 -- standard parameters
239 p_api_version in NUMBER default 1,
240 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
241 x_return_status out nocopy VARCHAR2,
242 x_msg_count out nocopy NUMBER,
243 x_msg_data out nocopy VARCHAR2,
244 -- input parameters
245 p_chr_id in NUMBER
246 );
247
248
249 --
250 -- transformation_error
251 --
252 -- returns message from message dictionary
253 -- OKC_TRANSF_ERROR tokens TEMPLATE, NAME
254 --
255 -- p_type, p_id -> 'SQL', SQL_ID
256 -- -> 'XSL', XSL_ID
257 --
258 function transformation_error(
259 p_type in varchar2,
260 p_id in number
261 ) return varchar2;
262
263
264 --
265 -- check_transf_path
266 --
267 -- returns 'S' if path valid
268 -- else returns message
269 -- OKC_INACTIVE_TRANSF
270 -- tokens TRANSF_TYPE, TRANSF_NAME, PROFILE
271 --
272 function check_transf_path return varchar2;
273
274
275
276 --
277 -- exec_OKC_WEB_PRERUN
278 --
279 -- procedure executes <OKC_WEB_PRERUN> profile option
280 -- site level value is 'OKC_REPORT_PVT.prerun'
281 -- published only for jsp apps
282 -- in forms apps call included in get_contract_url
283 --
284 -- signature of the procedure defined in <OKC_WEB_PRERUN> profile option
285 -- should be the same as OKC_REPORT_PVT.prerun
286 --
287 procedure exec_OKC_WEB_PRERUN(
288 -- standard parameters
289 p_api_version in NUMBER default 1,
290 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
291 x_return_status out nocopy VARCHAR2,
292 x_msg_count out nocopy NUMBER,
293 x_msg_data out nocopy VARCHAR2
294 -- input parameters
295 --1158 ,p_chr_id in NUMBER,
296 --1158 p_major_version in NUMBER default NULL,
297 --1158 p_scn_id in NUMBER default NULL
298 );
299
300 --
301 -- prerun
302 --
303 -- 'OKC_REPORT_PVT.prerun' used as default value
304 -- for profile option OKC_WEB_PRERUN
305 -- performs some validation tasks
306 -- like sections should be defined for the contract
307 -- let/not let to run report
308 --
309 procedure prerun(
310 -- standard parameters
311 p_api_version in NUMBER default 1,
312 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
313 x_return_status out nocopy VARCHAR2,
314 x_msg_count out nocopy NUMBER,
315 x_msg_data out nocopy VARCHAR2,
316 -- input parameters
317 p_chr_id in NUMBER,
318 p_major_version NUMBER default NULL,
319 p_scn_id in NUMBER default NULL
320 );
321
322 --
323 -- articles_warning
324 --
325 -- included for use with Authoring Form only
326 --
327 procedure articles_warning(
328 -- standard parameters
329 p_api_version in NUMBER default 1,
330 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
331 x_return_status out nocopy VARCHAR2,
332 x_msg_count out nocopy NUMBER,
333 x_msg_data out nocopy VARCHAR2,
334 -- input parameters
335 p_chr_id in NUMBER
336 );
337
338 --
339 -- exec_OKC_WEB_LOG_RUN
340 --
341 -- procedure executes <OKC_WEB_LOG_RUN> profile option
342 -- site level value is 'OKC_REPORT_PVT.noop'
343 -- called from bean both for FRM and JSP applications
344 -- when real transformation happens, not just retrieve
345 -- from cache
346 --
347 -- signature of the procedure defined in <OKC_WEB_LOG_RUN> profile option
348 -- should be the same as OKC_REPORT_PVT.noop
349 --
350 procedure exec_OKC_WEB_LOG_RUN(
351 -- standard parameters
352 p_api_version in NUMBER default 1,
353 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
354 x_return_status out nocopy VARCHAR2,
355 x_msg_count out nocopy NUMBER,
356 x_msg_data out nocopy VARCHAR2
357 -- input parameters
358 --1158 p_chr_id in NUMBER,
359 --1158 p_major_version in NUMBER default NULL,
360 --1158 p_scn_id in NUMBER default NULL
361 );
362
363 --
364 -- noop
365 --
366 -- dummy procedure for setting OKC_WEB_LOG_RUN
367 -- profile option value at site level
368 --
369 -- does nothing, returns 'S'
370 --
371 procedure noop(
372 -- standard parameters
373 p_api_version in NUMBER default 1,
374 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
375 x_return_status out nocopy VARCHAR2,
376 x_msg_count out nocopy NUMBER,
377 x_msg_data out nocopy VARCHAR2,
378 -- input parameters
379 p_chr_id in NUMBER,
380 p_major_version NUMBER default NULL,
381 p_scn_id in NUMBER default NULL
382 );
383
384 --
385 -- free_temp_clob
386 --
387 -- just a wrapper of dbms_clob.freetemporary
388 --
389 procedure free_temp_clob(
390 p_clob in out nocopy CLOB
391 );
392
393 -- public get_k_version
394 --
395 -- output MAJOR (might be in as well), MINOR vesions
396 -- to get rid of default last version
397 --
398 -- published for JSP apps to get rid of default versions
399 --
400 -- prior 1158 was procedure get_k_version(p_chr_id number,
401
402 procedure get_k_version(p_chr_id in out nocopy number,
403 x_major_version in out nocopy number,
404 x_minor_version out nocopy number);
405
406 -- public get_sql_name
407 --
408 function get_sql_name(p_sql_id number) return varchar2;
409
410 -- public get_new_id
411 --
412 function get_new_id return number;
413
414 function get_new_id(p_entity varchar2) return number;
415
416 -- Start of comments
417 --
418 -- Procedure Name : check_name_uk
419 -- Description :
420 -- Business Rules :
421 -- Parameters :
422 -- Version : 1.0
423 -- End of comments
424
425 function check_name_uk(p_entity varchar2) return varchar2;
426
427 -- Start of comments
428 --
429 -- Procedure Name : return_mess
430 -- Description : returns error
431 -- Business Rules :
432 -- Parameters :
433 -- Version : 1.0
434 -- End of comments
435
436 function return_mess(p_mess varchar2) return varchar2;
437
438 -- Start of comments
439 --
440 -- Procedure Name : validate parameters
441 -- Description : returns errors #, -1 if critical error, 0 - no errors
442 -- Business Rules :
443 -- Parameters :
444 -- Version : 1.0
445 -- End of comments
446
447 function validate_params(p_required_yn varchar2 default 'N') return number;
448 function set_and_validate(p_array in JTF_VARCHAR2_TABLE_2000,
449 p_required_yn varchar2 default 'N') return number;
450
451 function get_zero_mess return varchar2;
452 function get_much_mess return varchar2;
453 procedure get_labels(
454 x_title out nocopy varchar2,
455 x_prompt out nocopy varchar2,
456 x_message out nocopy varchar2,
457 x_name out nocopy varchar2,
458 x_description out nocopy varchar2,
459 x_sql out nocopy varchar2);
460 procedure get_actions(
461 x_run1 out nocopy varchar2,
462 x_run2 out nocopy varchar2,
463 x_close out nocopy varchar2);
464
465 procedure document_link(
466 p_kid in NUMBER,
467 p_vid in NUMBER,
468 p_xid in NUMBER,
469 p_document in out nocopy CLOB);
470
471 --
472 -- sample post report trigger to send message to
473 -- the wf_role passed in P_RECEPIENT parameter
474 --
475 procedure post_message(
476 p_api_version in NUMBER default 1,
477 p_init_msg_list in VARCHAR2 default OKC_API.G_TRUE,
478 x_return_status out nocopy VARCHAR2,
479 x_msg_count out nocopy NUMBER,
480 x_msg_data out nocopy VARCHAR2,
481 p_chr_id in NUMBER,
482 p_major_version NUMBER default NULL,
483 p_scn_id in NUMBER default NULL);
484
485 --
486 -- just send document from cache
487 --
488 procedure send_document(
489 p_recipient varchar2,
490 p_subject varchar2,
491 p_body varchar2 default NULL,
492 p_chr_id in NUMBER,
493 p_major_version NUMBER,
494 p_xst_id NUMBER);
495
496 procedure conc_send_document(
497 errbuf out nocopy varchar2,
498 retcode out nocopy varchar2,
499 p_recipient varchar2,
500 p_cc varchar2 default NULL,
501 p_bcc varchar2 default NULL,
502 p_subject varchar2,
503 p_body varchar2 default NULL,
504 p_xst_id NUMBER,
505 p_chr_id in NUMBER,
506 p_major_version NUMBER default NULL);
507
508 procedure conc_send_error(
509 errbuf out nocopy varchar2,
510 retcode out nocopy varchar2,
511 p_recipient varchar2,
512 p_cc varchar2 default NULL,
513 p_xst_id NUMBER,
514 p_chr_id in NUMBER);
515
516 function run_report_and_send(
517 p_recipient varchar2,
518 p_cc varchar2 default NULL,
519 p_bcc varchar2 default NULL,
520 p_subject varchar2,
521 p_body varchar2 default NULL,
522 p_xst_id NUMBER,
523 p_chr_id NUMBER,
524 p_major_version NUMBER default NULL,
525 p_err_recipient varchar2 default NULL,
526 p_err_cc varchar2 default NULL
527 ) return number;
528
529 end OKC_REPORT_PVT;