oracle ebs如何将报表挂到相应的职责下?,结合EBS并发做报表技术小结

对于结合EBS并发做报表的方式，到目前为止主要学习了三种方式，在此总结一下:

一.并发+Report Builder+Xmlp:

Step1.利用Report Builder创建获取数据的RDF文件，通常步骤如下：

1.1 添加用户参数P_CONC_REQUEST_ID ，这个参数是一定要有的；

1.2 根据实际的需求添加其他的用户参数，当然这一步你可以在写SQL查询的时候用‘:参数名’来使用参数，Report Builder会自动对应的参数并出现相应的提示，需要注意的是定义的参数的类型，一般情况下，数字类型的用number，字符类型的就用Character,而对于日期类型，因为在后面定义的并发请求的参数中，一般是使用值集FND_STANDARD_DATE，所以日期类型的参数一般也是用Character类型，然后在后面使用时进行转换，示例代码如下l_end_date := trunc(to_date( p_end_date,'YYYY/MM/DD HH24:MI:SS')) + 1 - 1/(24 * 3600 )，根据前人的经验，对于数据库中的日期字段，尤其是Transaction Date，不要直接用 trunc，这样会导致无法使用该日期的索引，一般是对参数进行必要的处理，如上述的加1天减1秒。

1.3 在BEFORE REPORT触发器里调用用户出口函数 FND SRWINIT 来初始化环境，一般形式如下：

srw.user_exit('FND SRWINIT');

复制代码

1.4 在AFTER REPORT触发器里调用用户出口函数FND SRWEXIT 来释放用户出口函数中使用的内存，一般形式如下：

srw.user_exit('FND SRWEXIT');

复制代码

1.5 创建SQL查询，这个比较简单，你可以直接在SQL查询的窗口里写select语句，或者在Package中准备好数据后再在SQL查询窗口中select出来，还涉及到后续的分组，合计和排序等等，这方面的文章比较多，技术实现起来也比较方便，就不多说了；

1.6 保存上面的步骤，生成rdf文件，用ftp工具将rdf文件上传到对应的应用top目录下的reports文件夹下对应的语言的文件夹下，要注意的问题是如果应用服务器为Linux系统，那么请确认上传的rdf文件的扩展名一定要是小写的；

Step2.定义可执行和并发程序,步骤如下:

2.1  定义可执行，选择与1.6步骤对应的应用，执行方法选择Oracle Reports，执行文件写的就是1.6步骤生成的rdf文件，不过不用写扩展名；

2.2  定义并发程序，选择对应的应用，选择2.1步骤的可执行，输出格式为xml；

2.3  如果1.6步骤的rdf里有用户参数(除P_CONC_REQUEST_ID外)，需要定义与rdf中对应的参数，其中变量的命名必须和rdf中的参数命名完全一致，需要注意的是，日期类型的参数一般推荐使用值集FND_STANDARD_DATE，这样可以直接验证输入的日期的格式，另外关于参数的值集之间的联动，可以在从值集的定义里加上条件形如where field_name = $FLEX$. value_set_name。

2.4  将并发程序挂到相应的职责的请求组里，然后就可以提交并发生成XML数据了。

Step3. 定义数据定义和模板

3.1  根据2.4步骤生成的XML数据文件，利用XMLP Desktop工具生成rtf模板，这里面的技巧很多，也有不少文章讲到，在此就不多费口舌了；

3.2  进入XML Publisher 管理员职责，进行数据定义和模板定义，数据定义中的代码要和并发程序的简称完全一致，模板定义中的上传文件的‘地区’推荐留空它！系统会默认第一个上传的rtf模板为默认的模板。

3.3  经过前面的步骤后，就可以直接提交请求，正常运行完后由查看输出就可以看到pdf格式的报表输出，XMLP默认为PDF输出，可以在提交并发前，在‘选项’里修改为其他的输出格式，后面会介绍一种直接修改后台表的方法来实现其他格式的默认输出。

二.并发+PL/SQL存储过程+Xmlp

Step1.创建获取数据，并生成XML数据源，最后输出CLOB对象到并发里的输出的PL/SQL存储过程，步骤如下：

1.1  根据实际需求，构造SQL查询语句,形式如 l_query :=’select * from fwk_tbx_po_headers where HEADER_ID =’||p_header_id||'  '||’Order by HEADER_ID’,更复杂的XML数据源结构在后面将会介绍；

1.2  利用dbms_xmlgen包和SQL查询生成XML形式的CLOB对象，在1.1步骤的基础上，创建Function如下：

FUNCTION get_xml_report_data(p_query VARCHAR2) RETURN CLOB AS

l_xml_result     CLOB;

l_xml_header     VARCHAR2(100);

l_xml_header_len NUMBER;

CONTEXT          dbms_xmlgen.ctxhandle;

l_start_pos      NUMBER;

l_temp_xml       CLOB;

l_encoding       VARCHAR2(50);

BEGIN

dbms_lob.createtemporary(l_xml_result, TRUE);

dbms_lob.createtemporary(l_temp_xml, TRUE);

CONTEXT := dbms_xmlgen.newcontext(p_query);

dbms_xmlgen.setrowsettag(CONTEXT, 'HEADERS');

dbms_xmlgen.setrowtag(CONTEXT, 'HEADER');

l_temp_xml := dbms_xmlgen.getxml(CONTEXT);

dbms_xmlgen.closecontext(CONTEXT);

l_encoding       := fnd_profile.VALUE('ICX_CLIENT_IANA_ENCODING');

l_xml_header     := '<?xml version="1.0" encoding="' || l_encoding || '"?>';

l_xml_header_len := length(l_xml_header);

dbms_lob.writeappend(l_xml_result, length(l_xml_header), l_xml_header);

IF dbms_lob.getlength(l_temp_xml) > 0 THEN

l_start_pos := dbms_lob.instr(l_temp_xml, '>', 1, 1);

dbms_lob.copy(dest_lob => l_xml_result, src_lob => l_temp_xml,

amount => dbms_lob.getlength(l_temp_xml) - l_start_pos,

dest_offset => dbms_lob.getlength(l_xml_result) + 1, src_offset => l_start_pos + 1);

END IF;

RETURN l_xml_result;

END;

复制代码

1.3 输出CLOB对象到并发的输出里，承接步骤1.2,创建过程如下：

PROCEDURE print_cloboutput(p_xml_data IN CLOB) IS

l_amount NUMBER;

l_offset NUMBER;

l_length NUMBER;

l_data   VARCHAR2(32767);

BEGIN

l_length := nvl(dbms_lob.getlength(p_xml_data), 0);

l_offset := 1;

l_amount := 16383;

LOOP

EXIT WHEN l_length <= 0;

dbms_lob.READ(p_xml_data, l_amount, l_offset, l_data);

fnd_file.put(fnd_file.output, l_data);

l_length := l_length - l_amount;

l_offset := l_offset + l_amount;

END LOOP;

END;

复制代码

1.4 将上面的三步结合起来，统一在一个PL/SQL存储过程里。

1.5 复杂的XML结构的生成,前面利用dbms_xmlgen生成的只是一层的数据结构，那么如果多层(>=2)的XML结构如何生成呢？下面介绍一种生成两层结构的方法,示例如下:

DECLARE

l_xml_result     CLOB;

l_xml_header     VARCHAR2(100);

l_xml_header_len NUMBER;

CONTEXT          dbms_xmlgen.ctxhandle;

l_start_pos      NUMBER;

l_header_xml     CLOB;

l_line_xml       CLOB;

l_encoding       VARCHAR2(50);

l_header_query   VARCHAR2(1000);

l_line_query     VARCHAR2(1000);

BEGIN

dbms_lob.createtemporary(l_xml_result, TRUE);

dbms_lob.createtemporary(l_header_xml, TRUE);

dbms_lob.createtemporary(l_line_xml, TRUE);

l_encoding       := fnd_profile.VALUE('ICX_CLIENT_IANA_ENCODING');

l_xml_header     := '<?xml version="1.0" encoding="' || l_encoding || '"?>';

l_xml_header_len := length(l_xml_header);

dbms_lob.writeappend(l_xml_result, length(l_xml_header), l_xml_header);

dbms_lob.writeappend(l_xml_result, length(''), '');

FOR header_rec IN (SELECT header_id FROM fwk_tbx_po_headers) LOOP

l_header_query := 'select header_id,description,status_code,currency_code from fwk_tbx_po_headers   where HEADER_ID  =  ' ||

header_rec.header_id;

CONTEXT        := dbms_xmlgen.newcontext(l_header_query);

dbms_xmlgen.setrowsettag(CONTEXT, '');

dbms_xmlgen.setrowtag(CONTEXT, 'HEADER');

l_header_xml := dbms_xmlgen.getxml(CONTEXT);

dbms_xmlgen.closecontext(CONTEXT);

IF dbms_lob.getlength(l_header_xml) > 0 THEN

l_start_pos := dbms_lob.instr(l_header_xml, '>', 1, 1);

dbms_lob.copy(dest_lob => l_xml_result, src_lob => l_header_xml,

amount => dbms_lob.getlength(l_header_xml) - l_start_pos - length('') - 1,

dest_offset => dbms_lob.getlength(l_xml_result) + 1, src_offset => l_start_pos + 1);

END IF;

dbms_lob.writeappend(l_xml_result, length(''), '');

FOR line_rec IN (SELECT line_id FROM fwk_tbx_po_lines WHERE header_id = header_rec.header_id) LOOP

l_line_query := 'select HEADER_ID, line_id,item_description,quantity from fwk_tbx_po_lines  where HEADER_ID  = ' ||

header_rec.header_id || ' and line_id=' || line_rec.line_id;

CONTEXT      := dbms_xmlgen.newcontext(l_line_query);

dbms_xmlgen.setrowsettag(CONTEXT, '');

dbms_xmlgen.setrowtag(CONTEXT, 'LINE');

l_line_xml := dbms_xmlgen.getxml(CONTEXT);

dbms_xmlgen.closecontext(CONTEXT);

IF dbms_lob.getlength(l_line_xml) > 0 THEN

l_start_pos := dbms_lob.instr(l_line_xml, '>', 1, 1);

dbms_lob.copy(dest_lob => l_xml_result, src_lob => l_line_xml,

amount => dbms_lob.getlength(l_line_xml) - l_start_pos,

dest_offset => dbms_lob.getlength(l_xml_result) + 1, src_offset => l_start_pos + 1);

END IF;

END LOOP;

dbms_lob.writeappend(l_xml_result, length(''), '');

dbms_lob.writeappend(l_xml_result, length(''), '');

END LOOP;

dbms_lob.writeappend(l_xml_result, length(''), '');

END;

复制代码

至于三层结构,可以在上面两层结构的基础上再加一层循环,特别注意以下标签对的输出方法，可参考标签对的输出方法来修改，另外在网上找到另外一种也是很好的方法，作者已经写得很明白了，主要是利用Cast和Multiset函数将子查询转化为子结果集，从而将从表的数据以数据集合的形式查询出来，详细请参考http://alvin.blog.51cto.com/210283/91259.

1.6 修改XMLP的默认输出，并发关联XMLP模板后，默认输出为PDF格式，但很多时候用户需要的是其他如Excel格式，如果让用户每次提交并发时自己去修改输出方式，就降低了用户的方便性，现介绍一种网上搜来的办法如下：

PROCEDURE update_xml_output_format IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

UPDATE fnd_conc_pp_actions

SET argument5 = 'EXCEL'

WHERE concurrent_request_id = fnd_profile.VALUE('CONC_REQUEST_ID')

AND action_type = 6;

COMMIT;

END;

复制代码

主要是利用了自治事务的特性，在并发执行开始时进行修改，其中argument5的值就是并发输出的文档格式，你可以修改为其他可用的格式。

Step2.定义可执行和并发程序,步骤如下:

2.1定义可执行,执行方法选择PL/SQL存储过程，执行文件名填写相应存储过程的名字，需要注意的是存储过程一定要有两个参数errbuf和retcode两个OUT类型参数，并且要作为第一和第二个参数可以都为varchar2类型的，errbuf是用来输出错误的信息，retcode输出错误号，一般为0表示无错误，1，2表示有错误。

2.2 定义并发程序，输出格式为XML，其中参数的定义需要注意的是，对应所调用的PL/SQL存储过程，errbuf和retcode这两个是不需要在并发里定义的，但是其他的参数要按照其在存储过程里的顺序和类型进行定义，名字倒是不用一致，系统会根据顺序来对应的，所以当可执行的执行方法为PL/SQL存储过程时，你可以看到参数定义里的变量是灰色不可修改的。

2.3 对于日期类型的参数，和上面的Report Builder中的一样，推荐使用值集FND_STANDARD_DATE，存储过程对应的日期参数使用varchar2类型，在后面使用时再转换。

Step3. 定义数据定义和模板，关联并发程序，这一步就不细说了。

三.并发+PL/SQL存储过程+Html

这种方法和上面的两种在获取数据和定义可执行与并发程序方面并没有任何区别，差别就在于这种方法是直接在PL/SQL存储过程生成html代码，然后利用形如fnd_file.put(fnd_file.output, l_html_data)的形式将html代码输出到并发的输出里，结合游标和html代码，举一例子如下：

PROCEDURE out_html_report

(

errbuf  OUT VARCHAR2,

retcode OUT VARCHAR2

) AS

BEGIN

fnd_file.put(fnd_file.output,'

');

fnd_file.put(fnd_file.output,'

头ID	描述	状态	币种

fnd_file.put(fnd_file.output,

'

' || rec.header_id ||'' || rec.description ||'' || rec.status_code ||'' || rec.currency_code ||'');

END LOOP;

fnd_file.put(fnd_file.output, '

');

fnd_file.put(fnd_file.output, '');

END;

复制代码