[Home] [Help]
PACKAGE BODY: APPS.XDO_DGF_RPT_PKG
Source
1 PACKAGE BODY xdo_dgf_rpt_pkg as
2 /* $Header: XDODGFRPB.pls 120.0 2008/01/19 00:13:41 bgkim noship $ */
3
4
5 -- global private variables to store filtered lists
6 g_report_list RPT_TABLE_TYPE;
7 g_template_list TPLT_TABLE_TYPE;
8 g_parameter_list PARAM_TABLE_TYPE;
9 g_rule_list RULE_TABLE_TYPE;
10 g_tpl_rule_id_list TPL_RULE_ID_TABLE_TYPE;
11
12 g_current_runtime_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
13 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
14 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
15 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
16 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
17 g_error_buffer VARCHAR2(100);
18
19 -- procedure get_lang_territories
20 -- Description: fills attributes template_lang_territory_codes and template_lang_territory_desc
21 -- for each template in the table p_template_table
22 PROCEDURE get_lang_territories(
23 p_template_code IN VARCHAR2,
24 p_template_application IN VARCHAR2,
25 p_lang_terr_codes OUT NOCOPY VARCHAR2,
26 p_lang_terr_desc OUT NOCOPY VARCHAR2
27 )
28 IS
29 CURSOR c_lang_terr_codes is
30 SELECT l.language language_code,
31 l.territory territory_code
32 FROM xdo_lobs l
33 WHERE l.lob_type = 'TEMPLATE'
34 AND l.lob_code = p_template_code
35 AND l.application_short_name = p_template_application;
36
37 CURSOR c_default_lang is
38 SELECT tb.default_language default_language
39 FROM xdo_templates_b tb
40 WHERE tb.application_short_name = p_template_application
41 AND tb.template_code = p_template_code;
42 -- local vars
43 l_default_language VARCHAR2(10) := '00';
44 l_language_code VARCHAR2(10);
45 l_language_desc VARCHAR2(100);
46 l_territory_desc VARCHAR2(100);
47 l_lang_terr_codes VARCHAR2(1000) := '';
48 l_lang_terr_desc VARCHAR2(2000) := '';
49
50 BEGIN
51 FOR rec IN c_default_lang
52 LOOP
53 l_default_language := rec.default_language;
54 END LOOP;
55 --
56 FOR rec IN c_lang_terr_codes
57 LOOP
58 IF rec.language_code = '00' THEN
59 l_language_code := l_default_language;
60 ELSE
61 l_language_code := rec.language_code;
62 END IF;
63 l_lang_terr_codes := l_lang_terr_codes || l_language_code || ':' || rec.territory_code || '#';
64
65 -- get language description
66 SELECT il.name
67 INTO l_language_desc
68 FROM fnd_iso_languages_vl il
69 WHERE il.iso_language_2 = l_language_code;
70 -- get territory description
71 IF rec.territory_code <> '00' THEN
72 SELECT tr.territory_short_name
73 INTO l_territory_desc
74 FROM fnd_territories_vl tr
75 WHERE tr.territory_code = upper(rec.territory_code);
76
77 l_lang_terr_desc := l_lang_terr_desc || l_language_desc || ', ' || l_territory_desc || '#';
78 ELSE
79 l_lang_terr_desc := l_lang_terr_desc || l_language_desc || '#';
80 END IF;
81 END LOOP;
82 p_lang_terr_codes := l_lang_terr_codes;
83 p_lang_terr_desc := l_lang_terr_desc;
84 END;
85
86 PROCEDURE get_context_reports(
87 p_form_code IN VARCHAR2,
88 p_block_code IN VARCHAR2,
89 p_report_table OUT NOCOPY RPT_TABLE_TYPE,
90 p_template_table OUT NOCOPY TPLT_TABLE_TYPE,
91 p_tpl_rule_id_table OUT NOCOPY TPL_RULE_ID_TABLE_TYPE,
92 p_rule_table OUT NOCOPY RULE_TABLE_TYPE)
93 is
94 l_report_table RPT_TABLE_TYPE;
95 l_template_table TPLT_TABLE_TYPE;
96 l_tpl_rule_id_table TPL_RULE_ID_TABLE_TYPE;
97 l_rule_table RULE_TABLE_TYPE;
98 i INTEGER := 0; -- counter for reports
99 j INTEGER := 0; -- counter for templates
100 k INTEGER := 0; -- counter for tpl_rules
101 l_last_report_code VARCHAR2(30) := '$';
102 l_resp_id NUMBER;
103
104 CURSOR c_contexts(p_resp_id NUMBER) IS
105 SELECT rv.report_name report_name,
106 rv.report_code report_code,
107 rv.report_application report_application, -- added column 19.4.2006
108 rc.report_context_id rpt_context_id,
109 tc.template_context_id tpl_ctx_id,
110 tc.pdf_allowed pdf_format_allowed,
111 tc.rtf_allowed rtf_format_allowed,
112 tc.htm_allowed htm_format_allowed,
113 tc.xls_allowed xls_format_allowed,
114 tc.txt_allowed txt_format_allowed,
115 tv.template_name template_name,
116 tv.template_code template_code,
117 tv.template_application template_application -- added column 19.4.2006
118 FROM xdo_dgf_block_contexts bc,
119 xdo_dgf_rpt_contexts rc,
120 xdo_dgf_reports_v rv,
121 xdo_dgf_tpl_contexts tc,
122 xdo_dgf_templates_v tv,
123 fnd_request_group_units ru,
124 fnd_request_groups rg,
125 fnd_responsibility_vl fr,
126 fnd_concurrent_programs_vl fc
127 WHERE bc.form_code = p_form_code
128 AND bc.block_code = p_block_code
129 AND bc.block_context_id = rc.block_context_id
130 AND rv.report_code = rc.report_code
131 AND rv.report_application = rc.report_application
132 AND tc.report_context_id = rc.report_context_id
133 AND tv.template_code = tc.template_code
134 AND tv.template_application = tc.template_application
135 AND rv.report_code = fc.concurrent_program_name
136 AND ru.application_id = rg.application_id
137 AND ru.request_group_id = rg.request_group_id
138 AND rg.application_id = fr.group_application_id
139 AND rg.request_group_id = fr.request_group_id
140 AND ru.request_unit_id = fc.concurrent_program_id
141 AND ru.unit_application_id = fc.application_id
142 AND ru.request_unit_type = 'P'
143 AND fr.responsibility_id = p_resp_id
144 ORDER BY rv.report_code;
145
146
147 CURSOR c_context_tpl_rules(p_tpl_context_id IN INTEGER) is
148 SELECT tr.rule_catalog_id xrg_id,
149 tr.format_filter_type format_filter_type
150 FROM xdo_dgf_tpl_rules tr
151 WHERE tr.template_context_id = p_tpl_context_id;
152
153 CURSOR c_rule_catalog(p_xrg_id IN INTEGER) is
154 SELECT cat.rule_short_name rule_short_name,
155 cat.rule_type rule_type,
156 cat.rule_variable rule_variable,
157 cat.rule_operator rule_operator,
158 cat.rule_values rule_values,
159 cat.rule_values_datatype rule_values_datatype,
160 cat.db_function db_function,
161 cat.arg_number arg_number,
162 cat.arg01 arg01,
163 cat.arg02 arg02,
164 cat.arg03 arg03,
165 cat.arg04 arg04,
166 cat.arg05 arg05,
167 cat.arg06 arg06,
168 cat.arg07 arg07,
169 cat.arg08 arg08,
170 cat.arg09 arg09,
171 cat.arg10 arg10,
172 cat.arg01_type arg01_type,
173 cat.arg02_type arg02_type,
174 cat.arg03_type arg03_type,
175 cat.arg04_type arg04_type,
176 cat.arg05_type arg05_type,
177 cat.arg06_type arg06_type,
178 cat.arg07_type arg07_type,
179 cat.arg08_type arg08_type,
180 cat.arg09_type arg09_type,
181 cat.arg10_type arg10_type
182 FROM xdo_dgf_rule_catalog_vl cat
183 WHERE cat.rule_catalog_id = p_xrg_id;
184
185
186 -- cursor variables
187 l_tpl_rule_rec c_context_tpl_rules%rowtype;
188 l_rule_catalog_rec c_rule_catalog%rowtype;
189
190 -- local procedures
191 PROCEDURE write_rule(p_rule_id IN INTEGER)
192 IS
193 BEGIN
194
195 IF (g_level_statement >= g_current_runtime_level ) THEN
196 FND_LOG.STRING(g_level_statement,
197 'xdo_dgf_rpt_pkg.get_context_reports.write_rule',
198 'write_rule START: p_rule_id = ' || p_rule_id);
199 END IF;
200
201 -- find out whether the corresponding catalog rule is already recorded
202 IF NOT l_rule_table.exists(p_rule_id) THEN
203
204 IF (g_level_statement >= g_current_runtime_level ) THEN
205 FND_LOG.STRING(g_level_statement,
206 'xdo_dgf_rpt_pkg.get_context_reports.write_rule',
207 'go to fetch new RULE record; id = ' || p_rule_id);
208 END IF;
209
210 OPEN c_rule_catalog(p_rule_id);
211 FETCH c_rule_catalog INTO l_rule_catalog_rec;
212 l_rule_table(l_tpl_rule_rec.xrg_id).id := p_rule_id;
213 l_rule_table(l_tpl_rule_rec.xrg_id).rule_short_name := l_rule_catalog_rec.rule_short_name;
214 l_rule_table(l_tpl_rule_rec.xrg_id).rule_type := l_rule_catalog_rec.rule_type;
215 l_rule_table(l_tpl_rule_rec.xrg_id).rule_variable := l_rule_catalog_rec.rule_variable;
216 l_rule_table(l_tpl_rule_rec.xrg_id).rule_operator := l_rule_catalog_rec.rule_operator;
217 l_rule_table(l_tpl_rule_rec.xrg_id).rule_values := l_rule_catalog_rec.rule_values;
218 l_rule_table(l_tpl_rule_rec.xrg_id).rule_values_datatype := l_rule_catalog_rec.rule_values_datatype;
219 -- items for rule_type = 'D' (DB function call)
220 l_rule_table(l_tpl_rule_rec.xrg_id).db_function := l_rule_catalog_rec.db_function;
221 l_rule_table(l_tpl_rule_rec.xrg_id).arg_number := l_rule_catalog_rec.arg_number;
222 l_rule_table(l_tpl_rule_rec.xrg_id).arg01 := l_rule_catalog_rec.arg01;
223 l_rule_table(l_tpl_rule_rec.xrg_id).arg02 := l_rule_catalog_rec.arg02;
224 l_rule_table(l_tpl_rule_rec.xrg_id).arg03 := l_rule_catalog_rec.arg03;
225 l_rule_table(l_tpl_rule_rec.xrg_id).arg04 := l_rule_catalog_rec.arg04;
226 l_rule_table(l_tpl_rule_rec.xrg_id).arg05 := l_rule_catalog_rec.arg05;
227 l_rule_table(l_tpl_rule_rec.xrg_id).arg06 := l_rule_catalog_rec.arg06;
228 l_rule_table(l_tpl_rule_rec.xrg_id).arg07 := l_rule_catalog_rec.arg07;
229 l_rule_table(l_tpl_rule_rec.xrg_id).arg08 := l_rule_catalog_rec.arg08;
230 l_rule_table(l_tpl_rule_rec.xrg_id).arg09 := l_rule_catalog_rec.arg09;
231 l_rule_table(l_tpl_rule_rec.xrg_id).arg10 := l_rule_catalog_rec.arg10;
232 l_rule_table(l_tpl_rule_rec.xrg_id).arg01_type := l_rule_catalog_rec.arg01_type;
233 l_rule_table(l_tpl_rule_rec.xrg_id).arg02_type := l_rule_catalog_rec.arg02_type;
234 l_rule_table(l_tpl_rule_rec.xrg_id).arg03_type := l_rule_catalog_rec.arg03_type;
235 l_rule_table(l_tpl_rule_rec.xrg_id).arg04_type := l_rule_catalog_rec.arg04_type;
236 l_rule_table(l_tpl_rule_rec.xrg_id).arg05_type := l_rule_catalog_rec.arg05_type;
237 l_rule_table(l_tpl_rule_rec.xrg_id).arg06_type := l_rule_catalog_rec.arg06_type;
238 l_rule_table(l_tpl_rule_rec.xrg_id).arg07_type := l_rule_catalog_rec.arg07_type;
239 l_rule_table(l_tpl_rule_rec.xrg_id).arg08_type := l_rule_catalog_rec.arg08_type;
240 l_rule_table(l_tpl_rule_rec.xrg_id).arg09_type := l_rule_catalog_rec.arg09_type;
241 l_rule_table(l_tpl_rule_rec.xrg_id).arg10_type := l_rule_catalog_rec.arg10_type;
242 close c_rule_catalog;
243 END IF;
244 END;
245 -- end of local procedures
246
247 BEGIN
248 IF (g_level_statement >= g_current_runtime_level ) THEN
249 FND_LOG.STRING(g_level_statement,
250 'xdo_dgf_rpt_pkg.get_context_reports',
251 'START: pp_form_code = ' || p_form_code
252 || ' p_block_code = ' || p_block_code);
253 END IF;
254
255 fnd_profile.get('RESP_ID',l_resp_id);
256
257 IF (g_level_statement >= g_current_runtime_level ) THEN
258 FND_LOG.STRING(g_level_statement,
259 'xdo_dgf_rpt_pkg.get_context_reports',
260 'RESP_ID = ' || l_resp_id);
261 END IF;
262
263 FOR rec IN c_contexts(l_resp_id)
264 LOOP
265 IF l_last_report_code <> rec.report_code THEN
266 i := i + 1;
267 l_report_table(i).report_name := rec.report_name;
268 l_report_table(i).report_code := rec.report_application || ':' || rec.report_code; -- modified 27.4.2006
269 l_report_table(i).report_application := rec.report_application; -- added attribute 19.4.2006
270 l_report_table(i).rpt_context_id := rec.rpt_context_id;
271 l_last_report_code := rec.report_code;
272 END IF;
273 j:= j + 1;
274 l_template_table(j).template_name := rec.template_name;
275 l_template_table(j).template_code := rec.template_application || ':' || rec.template_code; -- modified 27.4.2006
276 l_template_table(j).template_application := rec.template_application; -- added column 19.4.2006
277 -- get template languages and territories -- added 28.4.2006
278 get_lang_territories(rec.template_code,
279 rec.template_application,
280 l_template_table(j).template_lang_territory_codes,
281 l_template_table(j).template_lang_territory_desc);
282
283 IF (g_level_statement >= g_current_runtime_level ) THEN
284 FND_LOG.STRING(g_level_statement,
285 'xdo_dgf_rpt_pkg.get_context_reports',
286 'l_template_table(' || j || ').template_lang_territory_codes=' || l_template_table(j).template_lang_territory_codes || ';' ||
287 'l_template_table(' || j || ').template_lang_territory_desc=' || l_template_table(j).template_lang_territory_desc);
288 END IF;
289
290 l_template_table(j).report_code := rec.report_application || ':' || rec.report_code; -- modified 27.4.2006
291 l_template_table(j).pdf_format_allowed := rec.pdf_format_allowed;
292 l_template_table(j).rtf_format_allowed := rec.rtf_format_allowed;
293 l_template_table(j).htm_format_allowed := rec.htm_format_allowed;
294 l_template_table(j).xls_format_allowed := rec.xls_format_allowed;
295 l_template_table(j).txt_format_allowed := rec.txt_format_allowed;
296
297 -- loop through XDO_DGF_TPL_RULES for a given template
298 -- and write id's into l_tpl_rule_id_table
299 OPEN c_context_tpl_rules(rec.tpl_ctx_id);
300 FETCH c_context_tpl_rules INTO l_tpl_rule_rec;
301 IF c_context_tpl_rules%found THEN
302 k := k + 1;
303 l_template_table(j).first_r_id := k;
304 l_tpl_rule_id_table(k).rule_id := l_tpl_rule_rec.xrg_id;
305 l_tpl_rule_id_table(k).template_code := rec.template_code;
306 l_tpl_rule_id_table(k).format_filter_type := l_tpl_rule_rec.format_filter_type;
307
308 write_rule(l_tpl_rule_rec.xrg_id);
309 END IF;
310 LOOP
311 FETCH c_context_tpl_rules INTO l_tpl_rule_rec;
312 EXIT WHEN c_context_tpl_rules%notfound;
313 k:= k + 1;
314 l_tpl_rule_id_table(k).rule_id := l_tpl_rule_rec.xrg_id;
315 l_tpl_rule_id_table(k).template_code := rec.template_code;
316 l_tpl_rule_id_table(k).format_filter_type := l_tpl_rule_rec.format_filter_type;
317
318 write_rule(l_tpl_rule_rec.xrg_id);
319 END LOOP;
320 IF l_template_table(j).first_r_id <> -1 THEN
321 l_template_table(j).last_r_id := k;
322 END IF;
323 CLOSE c_context_tpl_rules;
324
325 END LOOP;
326 -- fill the output variables
330 p_rule_table := l_rule_table;
327 p_report_table := l_report_table;
328 p_template_table := l_template_table;
329 p_tpl_rule_id_table := l_tpl_rule_id_table;
331 --xdo_dgf_logger.log('get_context_reports','p_report_table.count = ' || p_report_table.count,'D');
332 END;
333
334 PROCEDURE get_report_parameters(
335 p_rpt_contexts IN RPT_TABLE_TYPE,
336 p_parameters OUT NOCOPY PARAM_TABLE_TYPE
337 )
338 IS
339 l_sql VARCHAR2(32000);
340 l_rpt_context_id_list VARCHAR2(1000) := '';
341
342 l_report_code VARCHAR2(30);
343 l_report_application VARCHAR2(20);
344 l_parameter_type VARCHAR2(1);
345 l_parameter_value VARCHAR2(500);
346 l_parameter_name VARCHAR2(100); -- added 29.11.2006 - new column PARAMETER_NAME
347 i INTEGER := 0;
348 TYPE ref_cursor_type is REF CURSOR;
349 c_params ref_cursor_type;
350 BEGIN
351 IF (g_level_statement >= g_current_runtime_level ) THEN
352 FND_LOG.STRING(g_level_statement,
353 'xdo_dgf_rpt_pkg.get_report_parameters',
354 'START');
355 END IF;
356
357 FOR i IN 1..p_rpt_contexts.count LOOP
358 l_rpt_context_id_list := l_rpt_context_id_list || ''''
359 || p_rpt_contexts(i).rpt_context_id || '''';
360 IF i < p_rpt_contexts.count THEN
361 l_rpt_context_id_list := l_rpt_context_id_list || ',';
362 END IF;
363 END LOOP;
364 l_sql := 'SELECT c.report_code, c.report_application, p.parameter_type, p.parameter_value, p.parameter_name ' -- modified 29.11.2006 - new column PARAMETER_NAME
365 || 'FROM XDO_DGF_RPT_CONTEXT_PARAMETERS p, XDO_DGF_RPT_CONTEXTS c '
366 || 'WHERE p.xrc_id = c.id '
367 || ' AND c.id in ('
368 || l_rpt_context_id_list
369 || ')'
370 || ' order by c.report_code, p.parameter_sequence';
371
372 OPEN c_params FOR l_sql;
373 LOOP
374 FETCH c_params INTO l_report_code, l_report_application, l_parameter_type, l_parameter_value, l_parameter_name;
375 EXIT WHEN c_params%notfound;
376 i := i+1;
377 p_parameters(i).report_code := l_report_application || ':' || l_report_code; -- modified 27.4.2006
378 p_parameters(i).report_application := l_report_application;
379 p_parameters(i).parameter_type := l_parameter_type;
380 p_parameters(i).parameter_value := l_parameter_value;
381 p_parameters(i).parameter_name := l_parameter_name; -- added 29.11.2006 - new column PARAMETER_NAME
382 END LOOP;
383
384 IF (g_level_statement >= g_current_runtime_level ) THEN
385 FND_LOG.STRING(g_level_statement,
386 'xdo_dgf_rpt_pkg.get_report_parameters',
387 'END: p_parameters.count = ' || p_parameters.count);
388 END IF;
389
390 END;
391
392 /* get_context_lists*/
393 -- stores all lists to global private variables
394 -- returns only lists to be resolved on the application server layer
395 -- i.e. parameters list and rule list
396 PROCEDURE prepare_context_lists(p_form_code IN VARCHAR2,
397 p_block_code IN VARCHAR2
398 )
399 IS
400 BEGIN
401 get_context_reports(
402 p_form_code,
403 p_block_code,
404 g_report_list,
405 g_template_list,
406 g_tpl_rule_id_list,
407 g_rule_list);
408
409 -- xdo_dgf_logger.log('prepare_context_lists','reports count = ' || g_report_list.count,'D');
410 -- 20.10.2006 - fix: clear g_parameter_list
411 g_parameter_list.delete;
412 IF g_report_list.count > 0 THEN
413 get_report_parameters(
414 g_report_list,
415 g_parameter_list
416 );
417 END IF;
418 END;
419
420 -- procedure apply_tpl_rules()
421 -- applies evaluated rules in g_rule_list on g_template_list
422 FUNCTION apply_tpl_rules(p_template_list XDO_DGF_RPT_PKG.TPLT_TABLE_TYPE,
423 p_rule_list RULE_TABLE_TYPE,
424 p_tpl_rule_id_list TPL_RULE_ID_TABLE_TYPE)
425 RETURN XDO_DGF_RPT_PKG.TPLT_TABLE_TYPE
426 IS
427 l_template_list XDO_DGF_RPT_PKG.TPLT_TABLE_TYPE;
428 l_result_value boolean;
429 -- vars for filtering allowed output formats
430 l_result_value_PDF boolean;
431 l_result_value_RTF boolean;
432 l_result_value_HTM boolean;
433 l_result_value_XLS boolean;
434 l_result_value_TXT boolean;
435 k integer := 1;
436 j integer;
437 BEGIN
438 IF (g_level_statement >= g_current_runtime_level ) THEN
439 FND_LOG.STRING(g_level_statement,
440 'xdo_dgf_rpt_pkg.apply_tpl_rules',
441 'START ...p_template_list.count = ' || p_template_list.count);
442 END IF;
443
444 FOR i IN 1..p_template_list.count
445 LOOP
446 l_result_value := true;
447 l_result_value_PDF := p_template_list(i).pdf_format_allowed = 'Y';
448 l_result_value_RTF := p_template_list(i).rtf_format_allowed = 'Y';
449 l_result_value_HTM := p_template_list(i).htm_format_allowed = 'Y';
450 l_result_value_XLS := p_template_list(i).xls_format_allowed = 'Y';
451 l_result_value_TXT := p_template_list(i).txt_format_allowed = 'Y';
452
453 IF p_template_list(i).first_r_id <> -1 THEN
454 j := p_template_list(i).first_r_id;
455
459 'p_template_list(i).template_code = ' || p_template_list(i).template_code
456 IF (g_level_statement >= g_current_runtime_level ) THEN
457 FND_LOG.STRING(g_level_statement,
458 'xdo_dgf_rpt_pkg.apply_tpl_rules',
460 || ' p_template_list(i).first_r_id = '|| p_template_list(i).first_r_id);
461 END IF;
462
463 LOOP
464 IF p_tpl_rule_id_list(j).format_filter_type IS NULL THEN
465 l_result_value := l_result_value AND p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value;
466 ELSIF p_tpl_rule_id_list(j).format_filter_type = 'PDF' THEN
467 l_result_value_PDF := l_result_value_PDF AND p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value;
468 ELSIF p_tpl_rule_id_list(j).format_filter_type = 'RTF' THEN
469 l_result_value_RTF := l_result_value_RTF and p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value;
470 ELSIF p_tpl_rule_id_list(j).format_filter_type = 'HTM' THEN
471 l_result_value_HTM := l_result_value_HTM and p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value;
472 ELSIF p_tpl_rule_id_list(j).format_filter_type = 'XLS' THEN
473 l_result_value_XLS := l_result_value_XLS and p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value;
474 ELSIF p_tpl_rule_id_list(j).format_filter_type = 'TXT' THEN
475 l_result_value_TXT := l_result_value_TXT and p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value;
476 END IF;
477 -- logging
478 IF p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value THEN
479
480 IF (g_level_statement >= g_current_runtime_level ) THEN
481 FND_LOG.STRING(g_level_statement,
482 'xdo_dgf_rpt_pkg.apply_tpl_rules',
483 'j=' || j ||
484 ' p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value = TRUE');
485 END IF;
486
487 ELSE
488
489 IF (g_level_statement >= g_current_runtime_level ) THEN
490 FND_LOG.STRING(g_level_statement,
491 'xdo_dgf_rpt_pkg.apply_tpl_rules',
492 'j=' || j ||
493 ' p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value = FALSE');
494 END IF;
495
496 END IF; -- IF p_rule_list(p_tpl_rule_id_list(j).rule_id).return_value THEN
497
498 EXIT WHEN (j=p_template_list(i).last_r_id) OR (NOT l_result_value);
499 j := j + 1;
500 END LOOP;
501 END IF; -- IF p_template_list(i).first_r_id <> -1 THEN
502
503 IF l_result_value THEN
504 l_template_list(k) := p_template_list(i);
505
506 IF l_result_value_PDF THEN
507 l_template_list(k).pdf_format_allowed := 'Y';
508 ELSE
509 l_template_list(k).pdf_format_allowed := 'N';
510 END IF;
511
512 IF l_result_value_RTF THEN
513 l_template_list(k).rtf_format_allowed := 'Y';
514 ELSE
515 l_template_list(k).rtf_format_allowed := 'N';
516 END IF;
517
518 IF l_result_value_HTM THEN
519 l_template_list(k).htm_format_allowed := 'Y';
520 ELSE
521 l_template_list(k).htm_format_allowed := 'N';
522 END IF;
523
524 IF l_result_value_XLS THEN
525 l_template_list(k).xls_format_allowed := 'Y';
526 ELSE
527 l_template_list(k).xls_format_allowed := 'N';
528 END IF;
529
530 IF l_result_value_TXT THEN
531 l_template_list(k).txt_format_allowed := 'Y';
532 ELSE
533 l_template_list(k).txt_format_allowed := 'N';
534 END IF;
535 k := k + 1;
536 END IF; -- IF l_result_value THEN
537 END LOOP;
538 return l_template_list;
539 --log('xxobl_report_pkg.apply_tpl_rules - END ...');
540 IF (g_level_statement >= g_current_runtime_level ) THEN
541 FND_LOG.STRING(g_level_statement,
542 'xdo_dgf_rpt_pkg.apply_tpl_rules',
543 'END');
544 END IF;
545
546 end;
547
548 PROCEDURE filter_templates(p_resolved_rule_list IN RULE_TABLE_TYPE)
549 IS
550 l_report_list_final XDO_DGF_RPT_PKG.RPT_TABLE_TYPE;
551 BEGIN
552 g_template_list := apply_tpl_rules(g_template_list,
553 xdo_dgf_rule_pkg.evaluate_rules(p_resolved_rule_list),
554 g_tpl_rule_id_list);
555
556 -- remove reports with no templates
557 IF (g_level_statement >= g_current_runtime_level ) THEN
558 FND_LOG.STRING(g_level_statement,
559 'xdo_dgf_rpt_pkg.filter_templates',
560 'check for reports with no templates - start');
561 END IF;
562
563 IF g_template_list.count > 0 THEN
564
565 DECLARE
566 j INTEGER := 1; -- pointer do tabulky sablon
567 k INTEGER := 1; -- pointer na volnou bunku v l_report_list_final
568 l_no_more_templates BOOLEAN := false;
569 BEGIN
570 IF (g_level_statement >= g_current_runtime_level ) THEN
571 FND_LOG.STRING(g_level_statement,
572 'xdo_dgf_rpt_pkg.filter_templates',
573 'g_report_list.count = ' || g_report_list.count);
574 END IF;
575
576 l_report_list_final.delete;
577 <<OUTER>>
578 FOR i IN 1..g_report_list.count
582 k := k + 1;
579 LOOP
580 IF g_report_list(i).report_code = g_template_list(j).report_code THEN
581 l_report_list_final(k) := g_report_list(i);
583 LOOP -- posun j na dalsi report_code
584 j:= j+1;
585 IF j > g_template_list.last THEN
586 l_no_more_templates := TRUE;
587 EXIT OUTER;
588 END IF;
589 EXIT WHEN g_template_list(j).report_code <> g_template_list(j-1).report_code;
590 END LOOP;
591 END IF;
592 END LOOP OUTER;
593 -- copy the result back to the global variable
594 g_report_list := l_report_list_final;
595
596 IF (g_level_statement >= g_current_runtime_level ) THEN
597 FND_LOG.STRING(g_level_statement,
598 'xdo_dgf_rpt_pkg.filter_templates',
599 'l_report_list_final.count = '|| l_report_list_final.count);
600 END IF;
601 END;
602 ELSE -- IF g_template_list.count > 0 THEN
603 g_report_list.delete;
604 END IF;
605 END;
606
607 procedure filter_templates_o(p_resolved_rule_list_o IN XDO_DGF_RULE_TABLE_TYPE)
608 IS
609 l_resolved_rule_list RULE_TABLE_TYPE;
610 l_rule_id INTEGER;
611 BEGIN
612 FOR i IN 1..p_resolved_rule_list_o.count
613 LOOP
614 l_rule_id := p_resolved_rule_list_o(i).id;
615 l_resolved_rule_list(l_rule_id).id := l_rule_id;
616 l_resolved_rule_list(l_rule_id).rule_short_name := p_resolved_rule_list_o(i).rule_short_name;
617 l_resolved_rule_list(l_rule_id).rule_type := p_resolved_rule_list_o(i).rule_type;
618 l_resolved_rule_list(l_rule_id).rule_variable := p_resolved_rule_list_o(i).rule_variable;
619 l_resolved_rule_list(l_rule_id).rule_operator := p_resolved_rule_list_o(i).rule_operator;
620 l_resolved_rule_list(l_rule_id).rule_values := p_resolved_rule_list_o(i).rule_values;
621 l_resolved_rule_list(l_rule_id).rule_values_datatype := p_resolved_rule_list_o(i).rule_values_datatype;
622 l_resolved_rule_list(l_rule_id).db_function := p_resolved_rule_list_o(i).db_function;
623 l_resolved_rule_list(l_rule_id).arg_number := p_resolved_rule_list_o(i).arg_number;
624 l_resolved_rule_list(l_rule_id).arg01 := p_resolved_rule_list_o(i).arg01;
625 l_resolved_rule_list(l_rule_id).arg02 := p_resolved_rule_list_o(i).arg02;
626 l_resolved_rule_list(l_rule_id).arg03 := p_resolved_rule_list_o(i).arg03;
627 l_resolved_rule_list(l_rule_id).arg04 := p_resolved_rule_list_o(i).arg04;
628 l_resolved_rule_list(l_rule_id).arg05 := p_resolved_rule_list_o(i).arg05;
629 l_resolved_rule_list(l_rule_id).arg06 := p_resolved_rule_list_o(i).arg06;
630 l_resolved_rule_list(l_rule_id).arg07 := p_resolved_rule_list_o(i).arg07;
631 l_resolved_rule_list(l_rule_id).arg08 := p_resolved_rule_list_o(i).arg08;
632 l_resolved_rule_list(l_rule_id).arg09 := p_resolved_rule_list_o(i).arg09;
633 l_resolved_rule_list(l_rule_id).arg10 := p_resolved_rule_list_o(i).arg10;
634 l_resolved_rule_list(l_rule_id).arg01_type := p_resolved_rule_list_o(i).arg01_type;
635 l_resolved_rule_list(l_rule_id).arg02_type := p_resolved_rule_list_o(i).arg02_type;
636 l_resolved_rule_list(l_rule_id).arg03_type := p_resolved_rule_list_o(i).arg03_type;
637 l_resolved_rule_list(l_rule_id).arg04_type := p_resolved_rule_list_o(i).arg04_type;
638 l_resolved_rule_list(l_rule_id).arg05_type := p_resolved_rule_list_o(i).arg05_type;
639 l_resolved_rule_list(l_rule_id).arg06_type := p_resolved_rule_list_o(i).arg06_type;
640 l_resolved_rule_list(l_rule_id).arg07_type := p_resolved_rule_list_o(i).arg07_type;
641 l_resolved_rule_list(l_rule_id).arg08_type := p_resolved_rule_list_o(i).arg08_type;
642 l_resolved_rule_list(l_rule_id).arg09_type := p_resolved_rule_list_o(i).arg09_type;
643 l_resolved_rule_list(l_rule_id).arg10_type := p_resolved_rule_list_o(i).arg10_type;
644
645 IF p_resolved_rule_list_o(i).return_value = 'T' THEN
646 l_resolved_rule_list(l_rule_id).return_value := TRUE;
647 ELSE
648 l_resolved_rule_list(l_rule_id).return_value := FALSE;
649 END IF;
650 END LOOP;
651 filter_templates(l_resolved_rule_list);
652 END;
653
654 PROCEDURE store_report_list(p_report_list IN RPT_TABLE_TYPE)
655 IS
656 BEGIN
657 g_report_list := p_report_list;
658 END;
659
660 PROCEDURE store_template_list(p_template_list IN TPLT_TABLE_TYPE)
661 IS
662 BEGIN
663 g_template_list := p_template_list;
664 END;
665
666 PROCEDURE store_parameter_list(p_parameter_list IN PARAM_TABLE_TYPE)
667 IS
668 BEGIN
669
670 IF (g_level_statement >= g_current_runtime_level ) THEN
671 FND_LOG.STRING(g_level_statement,
672 'xdo_dgf_rpt_pkg.store_parameter_list',
673 'START: p_parameter_list.count = ' || p_parameter_list.count);
674 END IF;
675 g_parameter_list := p_parameter_list;
676 END;
677
678 FUNCTION get_report_list RETURN RPT_TABLE_TYPE
679 IS
680 BEGIN
681 IF (g_level_statement >= g_current_runtime_level ) THEN
682 FND_LOG.STRING(g_level_statement,
683 'xdo_dgf_rpt_pkg.get_report_list',
684 'START: g_report_list.count = ' || g_report_list.count);
685 END IF;
686
687 RETURN g_report_list;
688 END;
689
690 FUNCTION get_template_list RETURN TPLT_TABLE_TYPE
691 IS
692 BEGIN
693 RETURN g_template_list;
694 END;
695
696 FUNCTION get_parameter_list RETURN PARAM_TABLE_TYPE
697 IS
698 BEGIN
702 'xdo_dgf_rpt_pkg.get_parameter_list',
699
700 IF (g_level_statement >= g_current_runtime_level ) THEN
701 FND_LOG.STRING(g_level_statement,
703 'START2: g_parameter_list.count = ' || g_parameter_list.count);
704 END IF;
705
706 RETURN g_parameter_list;
707 END;
708
709 FUNCTION get_rule_list RETURN RULE_TABLE_TYPE
710 IS
711 BEGIN
712
713 IF (g_level_statement >= g_current_runtime_level ) THEN
714 FND_LOG.STRING(g_level_statement,
715 'xdo_dgf_rpt_pkg.get_rule_list',
716 'START2: g_rule_list.count = ' || g_rule_list.count);
717 END IF;
718
719 RETURN g_rule_list;
720 END;
721
722 FUNCTION get_rule_list_o RETURN XDO_DGF_RULE_TABLE_TYPE
723 IS
724 l_rule_list_o XDO_DGF_RULE_TABLE_TYPE := XDO_DGF_RULE_TABLE_TYPE();
725 l_return_value VARCHAR2(1);
726 i NUMBER;
727 j INTEGER := 0;
728 BEGIN
729 IF g_rule_list.count > 0 THEN
730 i := g_rule_list.first;
731 LOOP
732 l_rule_list_o.extend();
733 j := j + 1;
734 IF g_rule_list(i).return_value THEN
735 l_return_value := 'T';
736 ELSE
737 l_return_value := 'F';
738 END IF;
739 l_rule_list_o(j) := XDO_DGF_RULE_RECORD_TYPE(
740 g_rule_list(i).id,
741 g_rule_list(i).rule_short_name,
742 g_rule_list(i).rule_type,
743 g_rule_list(i).rule_variable,
744 g_rule_list(i).rule_operator,
745 g_rule_list(i).rule_values,
746 g_rule_list(i).rule_values_datatype,
747 g_rule_list(i).db_function,
748 g_rule_list(i).arg_number,
749 g_rule_list(i).arg01,
750 g_rule_list(i).arg02,
751 g_rule_list(i).arg03,
752 g_rule_list(i).arg04,
753 g_rule_list(i).arg05,
754 g_rule_list(i).arg06,
755 g_rule_list(i).arg07,
756 g_rule_list(i).arg08,
757 g_rule_list(i).arg09,
758 g_rule_list(i).arg10,
759 g_rule_list(i).arg01_type,
760 g_rule_list(i).arg02_type,
761 g_rule_list(i).arg03_type,
762 g_rule_list(i).arg04_type,
763 g_rule_list(i).arg05_type,
764 g_rule_list(i).arg06_type,
765 g_rule_list(i).arg07_type,
766 g_rule_list(i).arg08_type,
767 g_rule_list(i).arg09_type,
768 g_rule_list(i).arg10_type,
769 l_return_value
770 );
771 EXIT WHEN i = g_rule_list.last;
772 i := g_rule_list.next(i);
773 END LOOP;
774 END IF;
775 RETURN l_rule_list_o;
776 END;
777
778 FUNCTION get_parameter_list_o RETURN XDO_DGF_PARAM_TABLE_TYPE
779 IS
780 l_parameter_list_o XDO_DGF_PARAM_TABLE_TYPE := XDO_DGF_PARAM_TABLE_TYPE();
781 BEGIN
782 FOR i IN 1..g_parameter_list.count
783 LOOP
784 l_parameter_list_o.extend;
785 l_parameter_list_o(i) := XDO_DGF_PARAM_RECORD_TYPE(
786 g_parameter_list(i).report_code,
787 g_parameter_list(i).report_application,
788 g_parameter_list(i).parameter_type ,
789 g_parameter_list(i).parameter_name, -- added 29.11.2006 - new attribute
790 g_parameter_list(i).parameter_value
791 );
792 END LOOP;
793 RETURN l_parameter_list_o;
794 END;
795
796 FUNCTION get_template_list_o RETURN XDO_DGF_TPLT_TABLE_TYPE
797 IS
798 l_template_list_o XDO_DGF_TPLT_TABLE_TYPE := XDO_DGF_TPLT_TABLE_TYPE();
799 BEGIN
800 FOR i IN 1..g_template_list.count
801 LOOP
802 l_template_list_o.extend;
803 l_template_list_o(i) := XDO_DGF_TPLT_RECORD_TYPE(
804 g_template_list(i).template_name,
805 g_template_list(i).template_code,
806 g_template_list(i).template_application,
807 g_template_list(i).template_lang_territory_codes,
808 g_template_list(i).template_lang_territory_desc,
809 g_template_list(i).report_code,
810 g_template_list(i).pdf_format_allowed,
811 g_template_list(i).rtf_format_allowed,
812 g_template_list(i).htm_format_allowed,
813 g_template_list(i).xls_format_allowed,
814 g_template_list(i).txt_format_allowed,
815 g_template_list(i).printer_allowed,
816 g_template_list(i).first_r_id,
817 g_template_list(i).last_r_id
818 );
819 END LOOP;
820 RETURN l_template_list_o;
821 END;
822
823 FUNCTION get_report_list_o RETURN XDO_DGF_RPT_TABLE_TYPE
824 IS
825 l_report_list_o XDO_DGF_RPT_TABLE_TYPE := XDO_DGF_RPT_TABLE_TYPE();
826 BEGIN
827
828 IF (g_level_statement >= g_current_runtime_level ) THEN
829 FND_LOG.STRING(g_level_statement,
830 'xdo_dgf_rpt_pkg.get_report_list_o',
831 'g_report_list.count = '|| g_report_list.count);
832 END IF;
833
834 FOR i in 1..g_report_list.count
835 LOOP
836 l_report_list_o.extend;
837 l_report_list_o(i) := XDO_DGF_RPT_RECORD_TYPE(
838 g_report_list(i).report_name,
839 g_report_list(i).report_code,
840 g_report_list(i).report_application,
841 g_report_list(i).rpt_context_id
842 );
843 END LOOP;
844
845 RETURN l_report_list_o;
846 END;
847
848 END XDO_DGF_RPT_PKG;