EBS 报表开发：标准报表Text输出改为XML输出

很多EBS的标准报表，特别是EBS R11都只有text格式输出，输出结果不好整理数据，需修改为Excel格式输出。

下面介绍两种类型（Oracle Report, Spawned）的报表如何修改。

Oracle Report类型@ Cost Type Comparison Report
Step1. 复制text格式输出的请求，建立一个XML输出的新的请求。

Step2. 运行请求，得到XML文件

Step3: 根据XML文件编写rtf模块并上传（略）

Spawned Report类型@ Applied Receipts Register
Spawned类型的报表用例1的方式修改行不通，且此类报表是用Pro*C编译上传的，无法看到源码。

但可以通过Trace查看SQL源码，再根据SQL结果编写XML文件。

Step1: 定义请求可调试

Step2: 系统配置文件
如果仅仅是"Enable Trace"，收集到的Sql Trace并不包含binds and waits，可通过配置文件设置。（建议设置用户层）

Concurrent: Allow Debugging Yes

Step3: 运行请求
勾选SQL Trace，并选择"SQL Trace with Binds and Waits"

Step4: 查询Trace文件

select name, value
from v$parameter
where name like 'user_dump_dest';

Trace文件中的SQL语句。SQL语句很多，最好是用text输出中的已知字段进行搜索。

Step5: 开发新请求，执行方式为SQL*PLUS
Spawned类型的请求会触发另一个子请求，子请求查询数据存入表中，父请求再输出数据。

子请求参数： 3000, 82, , 50428, 415, 415, 2019/01/01 00:00:00, 2019/01/31 00:00:00, , , , , , , , , , , , , , ,

父请求参数：SUBMIT, AR, RXARARRG, 217, DEFAULT, TEXT, 3000, 82, , 50428, 415, 415, 2019/01/01 00:00:00, 2019/01/31 00:00:00, , , , , , , , , , , , , , ,

那我们可以用执行方式为SQL*PLUS的请求，语句中触发子请求查询数据，再将结果生成XML数据或者直接用HTML显示数据。
–SQL文件

declare
    retcode                 NUMBER;
    errbuf                  VARCHAR2(1000);

    --等待程序：
    v_request_id            number;
    l_phase                 varchar2(200); 
    l_status                varchar2(200);  
    l_dev_phase             varchar2(200); 
    l_dev_status            varchar2(200); 
    l_message               varchar2(200); 
    l_request_status        boolean;   
    
    CURSOR user_cursor IS
        SELECT 
            DEBIT_BALANCING
           ,RECEIPT_CURRENCY_CODE
           ,fnd_date.date_to_displaydate(APPLY_DATE)  APPLY_DATE
           ,CUSTOMER_NUMBER
           ,CUSTOMER_NAME
           ,BATCH_NAME
           ,TRX_NUMBER
           ,RELATED_CUSTOMER
           ,RECEIPT_NUMBER
           ,TRX_CURRENCY_CODE
           ,AMOUNT_APPLIED_TO
           ,AMOUNT_APPLIED_FROM
           ,ACCTD_EARNED_DISCOUNT_TAKEN
           ,ACCTD_UNEARNED_DISCOUNT_TAKEN
           ,ACCTD_AMOUNT_APPLIED_TO
           ,FUNCTIONAL_CURRENCY_CODE
        FROM AR_RECEIPTS_REP_ITF  --object_type: Table
        WHERE REQUEST_ID = v_request_id
        ORDER BY DEBIT_BALANCING,
                 RECEIPT_CURRENCY_CODE,
                 APPLY_DATE;
BEGIN
    fnd_file.put_line(fnd_file.log,'Start request:'||'ARRXARRG  '||CURRENT_TIMESTAMP);
    
  --submit request: RXARARRG
    v_request_id := FND_request.SUBMIT_request( 'AR',
                                                'RXARARRG',
                                                '', 
                                                '', 
                                                false, 
                                                '&7',
                                                '&8',
                                                '',
                                                '&10',
                                                '', 
                                                '',           
                                                '&13',
                                                '&14',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                '',
                                                CHR(0));

    Commit;
    fnd_file.put_line(fnd_file.log,'v_request_id:'||v_request_id);
  
  --等待请求
  fnd_file.put_line(fnd_file.log,'Wait request ARRXARRG,begin：'||CURRENT_TIMESTAMP);
    l_request_status :=  fnd_concurrent.wait_for_request(v_request_id,
                                                         5, 
                                                         120, 
                                                         l_phase, 
                                                         l_status, 
                                                         l_dev_phase, 
                                                         l_dev_status, 
                                                         l_message);
    fnd_file.put_line(fnd_file.log,'Wait request ARRXARRG,end: '||CURRENT_TIMESTAMP);
    IF l_request_status THEN 
      IF l_dev_status = 'NORMAL' THEN 
         fnd_file.put_line(fnd_file.log,'Request:'||v_request_id || ' run successfully.'); 
      ELSE 
        fnd_file.put_line(fnd_file.log,'Request '||v_request_id||' did not complete normally ,status ：' || l_dev_status); 
         RETURN; 
      END IF; 
    ELSE 
     fnd_file.put_line(fnd_file.log,'Request '||v_request_id||'not finished ,can not see its report!');
      RETURN; 
    END IF;
  
  --generate XML data
        fnd_file.put_line(fnd_file.output,'<?xml version="1.0" encoding="UTF-8"?>');
        fnd_file.put_line(fnd_file.output,'<G_XML>');
  
  FOR temp in user_cursor LOOP

    fnd_file.put_line(fnd_file.output,'<G_LINE>');
        fnd_file.put_line(fnd_file.output,'<DEBIT_BALANCING>' || temp.DEBIT_BALANCING || '</DEBIT_BALANCING>');
        fnd_file.put_line(fnd_file.output,'<RECEIPT_CURRENCY_CODE>' || temp.RECEIPT_CURRENCY_CODE || '</RECEIPT_CURRENCY_CODE>');
        fnd_file.put_line(fnd_file.output,'<APPLY_DATE>' || temp.APPLY_DATE || '</APPLY_DATE>');
        fnd_file.put_line(fnd_file.output,'<CUSTOMER_NUMBER>' || temp.CUSTOMER_NUMBER || '</CUSTOMER_NUMBER>');   
      -- "<" and "&" is illegal in xml, need to put the data in CDATA   
        fnd_file.put_line(fnd_file.output,'<CUSTOMER_NAME> <![CDATA[' || temp.CUSTOMER_NAME || ']]> </CUSTOMER_NAME>');
        fnd_file.put_line(fnd_file.output,'<BATCH_NAME>' || temp.BATCH_NAME || '</BATCH_NAME>');
        fnd_file.put_line(fnd_file.output,'<TRX_NUMBER>' || temp.TRX_NUMBER || '</TRX_NUMBER>');   
        fnd_file.put_line(fnd_file.output,'<RELATED_CUSTOMER>' || temp.RELATED_CUSTOMER || '</RELATED_CUSTOMER>');
        fnd_file.put_line(fnd_file.output,'<RECEIPT_NUMBER>' || temp.RECEIPT_NUMBER || '</RECEIPT_NUMBER>');
        fnd_file.put_line(fnd_file.output,'<TRX_CURRENCY_CODE>' || temp.TRX_CURRENCY_CODE || '</TRX_CURRENCY_CODE>');   
        fnd_file.put_line(fnd_file.output,'<AMOUNT_APPLIED_TO>' || temp.AMOUNT_APPLIED_TO || '</AMOUNT_APPLIED_TO>');    
        fnd_file.put_line(fnd_file.output,'<AMOUNT_APPLIED_FROM>' || temp.AMOUNT_APPLIED_FROM || '</AMOUNT_APPLIED_FROM>');   
        fnd_file.put_line(fnd_file.output,'<ACCTD_EARNED_DISCOUNT_TAKEN>' || temp.ACCTD_EARNED_DISCOUNT_TAKEN || '</ACCTD_EARNED_DISCOUNT_TAKEN>'); 
        fnd_file.put_line(fnd_file.output,'<ACCTD_UNEARNED_DISCOUNT_TAKEN>' || temp.ACCTD_UNEARNED_DISCOUNT_TAKEN || '</ACCTD_UNEARNED_DISCOUNT_TAKEN>');       
        fnd_file.put_line(fnd_file.output,'<ACCTD_AMOUNT_APPLIED_TO>' || temp.ACCTD_AMOUNT_APPLIED_TO || '</ACCTD_AMOUNT_APPLIED_TO>'); 
        fnd_file.put_line(fnd_file.output,'<FUNCTIONAL_CURRENCY_CODE>' || temp.FUNCTIONAL_CURRENCY_CODE || '</FUNCTIONAL_CURRENCY_CODE>');  
    fnd_file.put_line(fnd_file.output,'</G_LINE>');
    
  END LOOP;
  
      fnd_file.put_line(fnd_file.output,'</G_XML>');

END;
/

将SQL文件上传到FTP服务器上

定义执行档

复制text格式输出的请求，建立一个XML输出的新的请求，执行档改为SQL文件。

运行新请求：HUH Applied Receipts Register，点击输出发现能输出部分XML数据，但还是有问题。

是因为XML不支持解析带有“&”字符的数据，加上“<![CDATA[ 内容 ]]>”可解决问题

Step6: 根据XML文件编写rtf模块并上传（略）