EBS - 增加并发请求输出类型

最新推荐文章于 2022-09-05 14:26:27 发布

AlanXu0618

最新推荐文章于 2022-09-05 14:26:27 发布

阅读量825

点赞数

分类专栏： EBS

本文链接：https://blog.csdn.net/verysevere/article/details/106897613

版权

EBS 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

概述

主要用于不通过BI Publisher，来生成EXCEL输出文件

Excel文件内容，需通过HTML语法画出

步骤

Step1.创建输出类型

应用管理员->应用产品->代码->应用程序对象库->快码值【CP_OUTPUT_FILE_TYPE】

因为该快码类型是系统级别，界面无法编辑，故从后台插入

注意快码（lookup_code）长度不大于4位

这一步骤定义后，即可修改对应请求的输出格式，如下为“XLS”

INSERT INTO fnd_lookup_values
  (lookup_type
  ,LANGUAGE
  ,lookup_code
  ,meaning
  ,description
  ,enabled_flag
  ,created_by
  ,creation_date
  ,last_updated_by
  ,last_update_login
  ,last_update_date
  ,source_lang
  ,security_group_id
  ,view_application_id)
VALUES
  ('CP_OUTPUT_FILE_TYPE'
  ,'ZHS'
  ,'XLS'
  ,'XLS'
  ,'EXCEL 输出'
  ,'Y'
  ,1
  ,SYSDATE
  ,1
  ,0
  ,SYSDATE
  ,'ZHS'
  ,0
  ,0);

INSERT INTO fnd_lookup_values
  (lookup_type
  ,LANGUAGE
  ,lookup_code
  ,meaning
  ,description
  ,enabled_flag
  ,created_by
  ,creation_date
  ,last_updated_by
  ,last_update_login
  ,last_update_date
  ,source_lang
  ,security_group_id
  ,view_application_id)
VALUES
  ('CP_OUTPUT_FILE_TYPE'
  ,'US'
  ,'XLS'
  ,'XLS'
  ,'EXCEL Output'
  ,'Y'
  ,1
  ,SYSDATE
  ,1
  ,0
  ,SYSDATE
  ,'US'
  ,0
  ,0);

Step2.创建浏览器选项

针对这类请求输出，设置对应的输出文件类型

系统管理员->安装->浏览器选项

文件格式	MIME类型	说明
XLS	application/vnd.ms-excel	Excel 输出

Step3.编写package,示例

 PROCEDURE prt_html_head(iv_seg1_out    IN VARCHAR2
                         ,iv_seg1_in     IN VARCHAR2
                         ,iv_period_name IN VARCHAR2
                         ,iv_direction   IN VARCHAR2) IS
    lv_report_title VARCHAR2(120);
    lv_charset CONSTANT VARCHAR2(30) := 'GB2312';
  BEGIN
    IF iv_direction = 'OUT' THEN
      lv_report_title := iv_seg1_out;
    ELSE
      lv_report_title := iv_seg1_in;
    END IF;
    lv_report_title := lv_report_title || ' ' || iv_period_name || ' 万能划拨凭证（自动）';
    -- output report header
    fnd_file.put_line(fnd_file.output
                     ,'<HTML xmlns:v="urn:schemas-microsoft-com:vml"
                         xmlns:o="urn:schemas-microsoft-com:office:office"
                         xmlns:x="urn:schemas-microsoft-com:office:excel">');
    fnd_file.put_line(fnd_file.output
                     ,'<head> <meta http-equiv="Content-Type" content="text/html; charset="' || lv_charset || '">');
    fnd_file.put_line(fnd_file.output, '<title>' || lv_report_title || '</title>');
    fnd_file.put_line(fnd_file.output, '<H3 align=center><B>' || lv_report_title || '</B></H3>');
    fnd_file.put_line(fnd_file.output
                     ,'<STYLE>
                         BODY{background-color:#FFFFFF;font-family:Verdana;font-size:10pt;color:Black;}
                         TR,TD{font-family:Verdana;font-size:10pt;color:Black;}
                         .numberx{mso-number-format:"\@";text-decoration:none;}
                         </STYLE>');
    fnd_file.put_line(fnd_file.output, '</head>');
    fnd_file.put_line(fnd_file.output, '<body>');

SELECT add_months(to_date(iv_period_name || '-01', 'yyyy-mm-dd'), 1) - 1
      INTO ld_acct_date
      FROM dual;
  
    SELECT gjs.user_je_source_name
      INTO lv_je_source_name
      FROM gl_je_sources gjs
     WHERE gjs.je_source_name = gv_je_source;
  
    SELECT gjc.user_je_category_name
      INTO lv_je_category_name
      FROM gl_je_categories gjc
     WHERE gjc.je_category_name = gv_je_category;
  
    SELECT '日记账导入 ' || lv_je_source_name || ' ' || '12345' || ' : '
      INTO lv_header_name
      FROM gl_je_sources_vl gjs
     WHERE gjs.je_source_name = gv_je_source;
    --凭证头信息
    fnd_file.put_line(fnd_file.output
                     ,'<table width="100%" border="0" cellspacing="0" cellpadding="2"
                       style="BORDER-COLLAPSE:collapse"
                       bordercolorlight="#000000" bordercolordark="#000000"
                       id="table_header">');
  
    fnd_file.put_line(fnd_file.output, '<tr>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right" ><B>日记账：</B></td>');
    fnd_file.put_line(fnd_file.output
                     ,'<td nowrap align="left" colspan="3">' || lv_header_name || lv_je_source_name || 'CNY' || '[模拟]' ||
                      '</td>');
    fnd_file.put_line(fnd_file.output, '<tr>');
  
    fnd_file.put_line(fnd_file.output, '<tr>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right" ><B>期间：</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left" >' || iv_period_name || '</td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right" ><B>有效日期：</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left" >' || to_char(ld_acct_date, 'dd-MON-yyyy') || '</td>');
    fnd_file.put_line(fnd_file.output, '</tr>');
  
    fnd_file.put_line(fnd_file.output, '<tr>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right" ><B>类别：</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left" >' || lv_je_category_name || '</td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right" ><B>来源：</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left" >' || lv_je_source_name || '</td>');
    fnd_file.put_line(fnd_file.output, '</tr>');
  
    fnd_file.put_line(fnd_file.output, '<tr>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right" ><B>余额类型：</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left" >' || '实际' || '</td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right" ><B>预算：</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left" >' || '' || '</td>');
    fnd_file.put_line(fnd_file.output, '</tr>');
  
    fnd_file.put_line(fnd_file.output, '<tr>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right" ><B>说明：</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left" colspan="3">' || lv_header_name || '</td>');
    fnd_file.put_line(fnd_file.output, '<tr>');
  
    fnd_file.put_line(fnd_file.output, '<tr>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right" ><B>参考：</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left" colspan="3">' || '万能划拨-12345' || '</td>');
    fnd_file.put_line(fnd_file.output, '<tr>');
  
    fnd_file.put_line(fnd_file.output, '</table>');
    fnd_file.put_line(fnd_file.output, '</br>');
  
    --凭证行信息-表头
    fnd_file.put_line(fnd_file.output
                     ,'<table width="100%" border="1" cellspacing="0" cellpadding="2"
                               style="BORDER-COLLAPSE:collapse"
                               bordercolorlight="#000000" bordercolordark="#000000"
                               id="table_line">');
    fnd_file.put_line(fnd_file.output, '<tr bgcolor="#3481B8">');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="center"><B>' || '行号' || '</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left"><B>' || '科目' || '</B></td>');
    --fnd_file.put_line(fnd_file.output, '<td nowrap align="left"><B>' || '科目说明' || '</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right"><B>' || '借方' || '</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="right"><B>' || '贷方' || '</B></td>');
    fnd_file.put_line(fnd_file.output, '<td nowrap align="left"><B>' || '摘要' || '</B></td>');
    fnd_file.put_line(fnd_file.output, '</tr>');

    fnd_file.put_line(fnd_file.output, '</table>');
    fnd_file.put_line(fnd_file.output, '</body></html>');
  END;