EBS 报表开发：RTF、Excel Template

1. Templete及XSL-FO

当上传RTF文件到XML模板管理器时，将被自动转换为XSL-FO。
以下SQL能查询模板文件（RTF、EXCEL等）和转换后的XSL-FO：

SELECT t2.application_short_name,
       t2.template_code,
       t1.LANGUAGE,
       t1.template_name,
       t3.file_name,
       t3.xdo_file_type,
       t3.file_data
 FROM xdo.xdo_templates_tl t1, 
      xdo.xdo_templates_b t2, 
      xdo.xdo_lobs t3
WHERE t1.template_code = t2.template_code
  AND t2.template_code = t3.lob_code
  AND t1.LANGUAGE =  'US'
  AND t1.template_code = '******'

2. Templete及XSL-FO下载

–创建目录用于存放下载的文件

create or replace directory FILE_DIR as '/tmp/test';

–创建Procedure读取BLOB文件

create or replace procedure read_blob_file(p_file_name IN varchar2, p_file_type IN varchar2)
IS
    l_file utl_file.file_type;
    l_lob blob;
    l_offset int:= 1;
    l_amount int:= 32767;
    l_len int;
    l_buffer raw(32767);
begin
  SELECT t3.file_data
    into l_lob
 FROM xdo.xdo_templates_tl t1, 
      xdo.xdo_templates_b t2, 
      xdo.xdo_lobs t3
WHERE t1.template_code = t2.template_code
  AND t2.template_code = t3.lob_code
  AND t1.LANGUAGE =  'US'
  AND t1.template_code = p_file_name
  and t3.xdo_file_type = p_file_type;

  l_file:= utl_file.fopen('FILE_DIR', p_file_name||'.'|| p_file_type, 'w', 32767);
  l_len:= dbms_lob.getlength(l_lob);
  
  while l_offset< l_len loop
      dbms_lob.read(l_lob, l_amount, l_offset, l_buffer);
      utl_file.put_raw(l_file, l_buffer, true);
      l_offset:= l_offset+ l_amount;
  end loop;
  
  utl_file.fclose(l_file);
end;

–测试

begin
    read_blob_file('Template名称','RTF');
end;

3. 修改Template及Data Definitions名称和代码
资料定义的代码保存之后，在网页上就不能更改了，

可以通过后台修改表：

UPDATE apps.XDO_DS_DEFINITIONS_B B
   SET data_source_code ='CUXTEST0515NEW'
WHERE data_source_code ='CUXTEST0515';

UPDATE APPS.XDO_DS_DEFINITIONS_TL TL
   SET data_source_code ='CUXTEST0515NEW'
WHERE data_source_code LIKE 'CUXTEST0515' ;

UPDATE XDO.XDO_LOBS
   SET LOB_CODE ='CUXTEST0515NEW'
 WHERE LOB_CODE ='CUXTEST0515'
 AND FILE_NAME LIKE 'CUXTEST0515.xml'