EBS 报表开发：PLSQL Utils@SYLK_UTIL_PKG

1. SYLK_UTIL_PKG的用法

SYLK_UTIL_PKG包下载： https://github.com/mortenbra/alexandria-plsql-utils/tree/master/ora
SYLK_UTIL_PKG包DEMO： https://github.com/mortenbra/alexandria-plsql-utils/tree/master/demos

步骤1. 下载SYLK_UTIL_PKG包并部署到数据库

步骤2. 创建生成Excel的目录
create or replace directory EXCEL_CREATION_TEST as ‘/usr/tmp’

步骤3. 使用SYLK_UTIL_PKG生成Excel

declare
    output utl_file.file_type;
begin
    output := utl_file.fopen( 'EXCEL_CREATION_TEST', 'emp1.slk', 'w', 32000 );

    sylk_util_pkg.show(
        p_file => output,
        p_query => 'select empno id, ename employee, sal Salary, comm commission from scott.emp where job = :JOB and sal > :SAL',
        p_parm_names => sylk_util_pkg.owaSylkArray( 'JOB', 'SAL'),
        p_parm_values => sylk_util_pkg.owaSylkArray( 'MANAGER', '2000' ),
        p_sum_column => sylk_util_pkg.owaSylkArray( 'N', 'N', 'Y', 'Y'),
        p_show_grid => 'NO' );

    utl_file.fflush ( output );
    utl_file.fclose ( output );
end;

生成Excel成功。

2. 输出到EBS 请求输出

Reference： http://garethroberts.blogspot.com/2007/10/excel-file-output-from-oracle.html

步骤1.修改SYLK_UTIL_PKG包
• fnd_file.output instead of utl_file.put_line(g_file
• Remove parameters for p_file

步骤2. 建立请求执行档使用的PLSQL包

create or replace package XXXV8_USERS_SYLK_PKG AUTHID CURRENT_USER AS
  procedure main
  ( errbuf      out varchar2
   ,retcode     out varchar2
   ,p_job       in  varchar2
   ,p_salary    in  number
  );
end XXXV8_USERS_SYLK_PKG;


create or replace package body XXXV8_USERS_SYLK_PKG AS
  procedure main( errbuf      out varchar2
                 ,retcode     out varchar2
                 ,p_job       in  varchar2
                 ,p_salary    in  number ) as
  begin
    sylk_util_pkg.show(
          --p_file => output,
          p_query => 'select empno id, ename employee, sal Salary, comm commission from scott.emp where job = :JOB and sal > :SAL',
          p_parm_names => sylk_util_pkg.owaSylkArray( 'JOB', 'SAL'),
          --p_parm_values => sylk_util_pkg.owaSylkArray( 'MANAGER', '2000' ),
          p_parm_values => sylk_util_pkg.owaSylkArray( p_job, p_salary ),
          p_sum_column => sylk_util_pkg.owaSylkArray( 'N', 'N', 'Y', 'Y'),
          p_show_grid => 'NO',
          p_widths => sylk_util_pkg.owaSylkArray(20,20,20,20));          
  end main;
END XXXV8_USERS_SYLK_PKG;

步骤3. 建立请求执行档

步骤4. 建立请求和请求参数

步骤5. 运行请求，测试结果