EBS R12输出Excel报表的几种方式

Heathcliff_看海的人

已于 2022-09-27 08:56:03 修改

阅读量1.6k

点赞数 1

分类专栏： Oracle ERP Report 文章标签：数据库 sql

于 2022-09-05 14:26:27 首次发布

本文链接：https://blog.csdn.net/m0_67158898/article/details/126702154

版权

Oracle ERP Report 专栏收录该内容

1 篇文章

订阅专栏

1、xml publisher + Excel模板

这种方式需要提前定义好Excel模板，类似rtf模板一样，适合格式较为复杂表单。

详细参考：XML Publiser For Excel Template_weixin_30267691的博客-CSDN博客

2、plsql + xml直接输出

这种方式不需要定义模板，可在package中直接定义表的标题，然后输出行，适合比较简单的表单

参考程序：

 /* ===============================================
  *   PROGRAM NAME:
  *                cux_po_display_excel_rpt
  *   DESCRIPTION:
  *                CUX：PO台账报表
  *   HISTORY:
  *     1.00   2021/01/19     xxxxxx   Creation  RE20201209001
  * ==============================================*/
  g_report_title VARCHAR2(80) := 'PO台账报表';
  g_charset CONSTANT VARCHAR2(30) := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
  --g_pkg_name CONSTANT VARCHAR2(35) := 'CUX_PO_DISPLAY_EXCEL_RPT';
  g_data_format      VARCHAR2(30) := 'YYYY-MM-DD';
  g_data_format_time VARCHAR2(30) := 'YYYY-MM-DD hh24:mi:ss';
  PROCEDURE output(p_info IN VARCHAR2) IS
  BEGIN
    fnd_file.put_line(fnd_file.output,
                      p_info);
  END output;

  PROCEDURE log(p_message IN VARCHAR2) AS
  BEGIN
    fnd_file.put_line(fnd_file.log,
                      p_message);
  END;
  /* ==============================================
  *   PROCEDURE NAME
  *       output_html_head
  *   DESCRIPTION:
  *       output report title and header
  *   ARGUMENT:
  *       p_report_title    - report name
  *       p_report_css
  *   RETURN:
  *   HISTORY:
  *    1.00   2021/01/19     xxxxxx   Creation  RE20201209001
  * ==============================================*/
  PROCEDURE output_html_head(p_report_title IN VARCHAR2,
                             p_report_css   IN VARCHAR2) IS
    l_client_charset VARCHAR2(150);
  BEGIN
    BEGIN
      SELECT v.tag
        INTO l_client_charset
        FROM fnd_lookup_values_vl v
       WHERE v.lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
         AND v.lookup_code = fnd_profile.value('FND_NATIVE_CLIENT_ENCODING')
         AND v.enabled_flag = 'Y'
         AND trunc(SYSDATE) BETWEEN nvl(v.start_date_active,
                                        trunc(SYSDATE - 1)) AND nvl(v.end_date_active,
                                                                    trunc(SYSDATE + 1));
    EXCEPTION
      WHEN no_data_found THEN
        l_client_charset := 'UTF-8';
    END;
  
    output('<html>');
    output('<head>');
    output('<meta http-equiv="Content-Type" content="text/html; charset=' || l_client_charset || '">');
    output('<title>' || p_report_title || '</title>');
    output('<style type="text/css" media="all">');
    output('body,table{font-size:13px;font-family:"Book Antiqua","Segoe UI", Tahoma, "Trebuchet MS", verdana, helvetica, arial, sans-serif, Georgia;}
         #table_margin{margin-left:50px;}
         .default_text {mso-number-format:"\@";};
         .num
         {mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\\-\#\,\#\#0\.00\\ ";};
         .percent
         {mso-number-format:Percent;};
         .percent1
         {font-style:italic;
          text-decoration:underline;
          text-underline-style:single;
          mso-number-format:Percent;}');
    IF substr(p_report_css,
              1,
              1) = '.' THEN
      output(';' || p_report_css);
    END IF;
    output('</style>');
    output('</head>');
    output('</br>');
    output('<body>');
    output('<H3 align=center>' || p_report_title || '</H3>');
    output('</br>');
  END output_html_head;

  /* =================================================================
  *   PROCEDURE: print_header
  *
  *   DESCRIPTION: 打印报表列头
  *
  *   PARAMS:
  *
  *   HISTORY:
  *     1.00   2021/01/19     xxxxxx   Creation  RE20201209001
  * =====================================================================*/
  PROCEDURE print_header IS
  BEGIN
  
    output('<tr bgcolor="#BBBBBB">');
    --
    output('<td nowrap align=center><B>' || '业务实体' || '</B></td>');
    output('<td nowrap align=center><B>' || 'PO号' || '</B></td>');
    output('<td nowrap align=center><B>' || '供应商编码' || '</B></td>');
    output('<td nowrap align=center><B>' || '供应商地点' || '</B></td>');
    output('<td nowrap align=center><B>' || '生产国' || '</B></td>');
    --5
    output('<td nowrap align=center><B>' || 'PO创建日期' || '</B></td>');
    output('<td nowrap align=center><B>' || '采购员' || '</B></td>');
    output('<td nowrap align=center><B>' || '订单创建者' || '</B></td>');
    output('<td nowrap align=center><B>' || '物料编码' || '</B></td>');
    output('<td nowrap align=center><B>' || 'title_mini' || '</B></td>');
    --10
    output('<td nowrap align=center><B>' || 'PO行数量' || '</B></td>');
    output('<td nowrap align=center><B>' || 'PO号-行号-发运行号' || '</B></td>');
    output('<td nowrap align=center><B>' || 'PO发运行数量*' || '</B></td>');
    output('<td nowrap align=center><B>' || 'PR编码' || '</B></td>');
    output('<td nowrap align=center><B>' || 'PR类别' || '</B></td>');
    --15
    output('<td nowrap align=center><B>' || '来源需求类型' || '</B></td>');
    output('<td nowrap align=center><B>' || 'PL/SO编号' || '</B></td>');
    output('<td nowrap align=center><B>' || '母单号' || '</B></td>');
    output('<td nowrap align=center><B>' || 'PL/SO备注' || '</B></td>');
    output('<td nowrap align=center><B>' || '运输方式*' || '</B></td>');
    output('<td nowrap align=center><B>' || '区域/市场*' || '</B></td>');
    --20
    output('<td nowrap align=center><B>' || '区域仓库*' || '</B></td>');
    output('<td nowrap align=center><B>' || '接收子库*' || '</B></td>');
    output('<td nowrap align=center><B>' || '供应商可验货日期*' || '</B></td>');
    output('<td nowrap align=center><B>' || '供应商入仓日期*' || '</B></td>');
    output('<td nowrap align=center><B>' || '条码类型*' || '</B></td>');
    --25
    output('<td nowrap align=center><B>' || 'Asins/UPC*' || '</B></td>');
    output('<td nowrap align=center><B>' || 'MSKU*' || '</B></td>');
    output('<td nowrap align=center><B>' || '条码*' || '</B></td>');
    output('<td nowrap align=center><B>' || '条码编译语言' || '</B></td>');
    output('<td nowrap align=center><B>' || '是否贴防伪贴纸' || '</B></td>');
    --30
    output('<td nowrap align=center><B>' || '应交延迟原因*' || '</B></td>');
    output('<td nowrap align=center><B>' || '评审不准原因' || '</B></td>');
    output('<td nowrap align=center><B>' || '定制信息' || '</B></td>');
    output('<td nowrap align=center><B>' || '发运行备注*' || '</B></td>');
    output('<td nowrap align=center><B>' || '质检日期' || '</B></td>');
    --35
    output('<td nowrap align=center><B>' || '质检结果' || '</B></td>');
    output('<td nowrap align=center><B>' || '要求到接收子仓日期*' || '</B></td>');
    output('<td nowrap align=center><B>' || '要求到货日期*' || '</B></td>');
    output('<td nowrap align=center><B>' || '首次供应商回复日期' || '</B></td>');
    output('<td nowrap align=center><B>' || '接收入库数量' || '</B></td>');
    --40
    output('<td nowrap align=center><B>' || '是否已入库' || '</B></td>');
    output('<td nowrap align=center><B>' || 'LeadTime时间' || '</B></td>');
    output('<td nowrap align=center><B>' || '后处理期' || '</B></td>');
    output('<td nowrap align=center><B>' || '是否已对账' || '</B></td>');
    output('<td nowrap align=center><B>' || 'ERP入仓日期' || '</B></td>');
    --45
    output('<td nowrap align=center><B>' || '整箱数' || '</B></td>');
    output('<td nowrap align=center><B>' || '是否整箱数控制' || '</B></td>');
    output('<td nowrap align=center><B>' || '延迟原因备注' || '</B></td>');
    output('<td nowrap align=center><B>' || '拆分批次' || '</B></td>');
    output('<td nowrap align=center><B>' || '拆分日期' || '</B></td>');
    --50
    output('<td nowrap align=center><B>' || '是否取消' || '</B></td>');
    output('<td nowrap align=center><B>' || '是否MPS' || '</B></td>');
    output('<td nowrap align=center><B>' || '收货日期' || '</B></td>');
    output('<td nowrap align=center><B>' || '是否外销' || '</B></td>'); --add by wen 20211025 for RE20210906005
  
    --add by wen 20211207 for 109718 start --
    output('<td nowrap align=center><B>' || '彩盒条码标签' || '</B></td>');
    output('<td nowrap align=center><B>' || '彩盒特殊标签' || '</B></td>');
    output('<td nowrap align=center><B>' || '内外箱标签' || '</B></td>');
    --add by wen 20211207 for 109718 end --
  
    output('</tr>');
  END print_header;
  /* ====================================================================
  *   PROCEDURE: main
  *
  *   DESCRIPTION: 主程序
  *
  *   PARAMS:
  *              p_vendor_id             供应商
                 po_create_f             PO创建日期自,
                 po_create_t             PO创建日期至,
                 p_agent_id              采购员,
                 p_vendor_check_date_f   供应商可验货日期自,
                 p_vendor_check_date_t   供应商可验货日期至,
                 p_vendor_receive_date_f 供应商入仓日期自,
                 p_vendor_receive_date_t 供应商入仓日期至,
                 p_req_delivery_date_f   要求到接收子仓日期自,
                 p_req_delivery_date_t   要求到接收子仓日期至,
                 p_receive_date_f        收货日期自,
                 p_receive_date_t        收货日期至,
                 p_receive_status        是否已入库
  *   HISTORY:
  *     1.00   2021/01/19     xxxxxx   Creation  RE20201209001
  * =====================================================================*/
  PROCEDURE main(retcode                 OUT VARCHAR2,
                 errbuf                  OUT VARCHAR2,
                 p_vendor_id             IN NUMBER,
                 po_create_f             IN VARCHAR2,
                 po_create_t             IN VARCHAR2,
                 p_agent_id              IN NUMBER,
                 p_vendor_check_date_f   IN VARCHAR2,
                 p_vendor_check_date_t   IN VARCHAR2,
                 p_vendor_receive_date_f IN VARCHAR2,
                 p_vendor_receive_date_t IN VARCHAR2,
                 p_req_delivery_date_f   IN VARCHAR2,
                 p_req_delivery_date_t   IN VARCHAR2,
                 p_receive_date_f        IN VARCHAR2,
                 p_receive_date_t        IN VARCHAR2,
                 p_receive_status        IN VARCHAR2,
                 p_cancel_flag           IN VARCHAR2) IS
    CURSOR csr_main IS
      SELECT cdv.ou_name, --业务实体
             cdv.po_number, --采购订单
             cdv.shipment_display_num, --PO号-行号-发运行号
             cdv.creation_date, --PO创建日期
             cdv.agent_name, --采购员
             cdv.create_user_name, --订单创建者
             cdv.pinxian, --品线
             cdv.vendor_number, --供应商编号
             cdv.vendor_name, --供应商名称
             cdv.vendor_site_code, --供应商地点编号
             cdv.make_country, --生产国
             cdv.item_number, --物料编码
             cdv.item_desc, --物料描述
             cdv.category_desc, --物料类别
             cdv.line_quantity, --行数量
             cdv.quantity, --发运行数量
             cdv.currency_code, --币种
             cdv.item_mini, --item_mini
             cdv.req_type, --PR类别
             cdv.sr_order_type, --来源需求类型
             cdv.req_number, --PR编码
             cdv.sopl_number, --PL/SO编号
             cdv.primeval_order_number, --母单号
             cdv.sopl_comments, --PL/SO备注
             cdv.region_desc, --区域/市场*
             cdv.region_org_code, --区域仓库*
             cdv.shipment_method_desc, --运输方式*
             cdv.receive_subinventory_code, --接收子库*
             cdv.quantity_received, --接收入库数量
             cdv.qa_result_desc, --质检结果
             cdv.need_by_date, --要求到货日期*
             cdv.actual_qa_date, --质检日期
             cdv.vendor_available_check_date, --供应商可验货日期自
             cdv.vendor_receive_date, --供应商入仓日期自
             cdv.asins_msg, --Asins/UPC
             cdv.asins_br_code_type, --条码类型
             cdv.asins_msku, --MSKU
             cdv.asins_br_code, --条码
             cdv.barcode_to_supplier, --条码编译语言
             cdv.anti_fake_sticker_flag, --是否贴防伪贴纸
             cdv.lead_time, --LeadTime时间
             cdv.adjust_days, --后处理时间
             --cdv.air_ship_reason_name,
             cdv.delay_delivery_desc, --应交延迟原因
             cdv.review_delay_desc, --评审不准原因
             cdv.special_info, --定制信息
             cdv.shipment_comments, --发运行备注
             cdv.po_receive_status, --是否已入库
             --cdv.oms_status,
             --cdv.bind_status,
             cdv.marked_flag, --是否已对账
             --cdv.compare_status,
             --cdv.compare_status_desc,
             --cdv.po_status,
             --cdv.srm_approve_status,
             --cdv.srm_status_desc,
             --cdv.qc_doc_number,
             cdv.req_delivery_date, --要求到接收子仓日期
             cdv.promised_date, --要求到货日期
             cdv.erp_receive_date, --ERP入仓日期
             --cdv.product_code,
             cdv.fixed_lot_multiplier, --整箱数
             cdv.fixed_lot_flag_dsp, --是否整箱数控制
             --cdv.certification_comments,
             --cdv.shipment_available_check_date,
             --cdv.lt_delay_reason_desc,
             cdv.delay_comments, --延迟原因备注
             cdv.split_line_location_num, --拆分批次
             --cdv.split_line_location_id,
             cdv.split_date, --拆分日期
             --cdv.manufacturer_sku,
             --cdv.manufacturer,
             --cdv.assembly_item_number,
             --cdv.assembly_order_number,
             --cdv.assembly_name,
             --cdv.line_comments,
             --cdv.vendor_comments,
             -- cdv.vendor_submit_date,
             cdv.cancel_flag_desc, --是否取消
             --cdv.materials_need_by_date,
             cdv.po_mps_flag_desc, --是否MPS
             cdv.receive_date, --收货日期
             cdv.area_flag_dsp, --是否外销 add by wen 20211025 for RE20210906005
             cdv.color_barcode_lab, --add by wen 20211207 for 109718
             cdv.color_special_lab, --add by wen 20211207 for 109718
             cdv.inouter_box_lab --add by wen 20211207 for 109718
        FROM cux_po_display_v cdv
       WHERE EXISTS (SELECT 1
                FROM fnd_flex_value_sets ffvs,
                     fnd_flex_values_vl  ffvv,
                     per_all_people_f    ppf,
                     fnd_flex_value_sets ffvs_par,
                     fnd_flex_values_vl  ffvv_par,
                     fnd_user            fu
               WHERE ffvs.flex_value_set_id = ffvv.flex_value_set_id
                 AND ffvs.flex_value_set_name = 'OCE_PO_Buyer'
                 AND ffvv.enabled_flag = 'Y'
                 AND ffvs_par.flex_value_set_id = ffvv_par.flex_value_set_id
                 AND ffvs_par.flex_value_set_name = 'OCEANWING_Product'
                 AND ffvv_par.flex_value = ffvv.parent_flex_value_low
                 AND ffvv_par.description = ppf.last_name
                 AND trunc(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
                 AND ffvv.flex_value = fu.user_name
                 AND ppf.person_id = cdv.agent_id
                 AND fu.user_id = fnd_global.user_id)
            --供应商
         AND cdv.vendor_id = nvl(p_vendor_id,
                                 cdv.vendor_id)
            --PO创建日期
         AND (cdv.creation_date >= fnd_conc_date.string_to_date(po_create_f) OR po_create_f IS NULL)
         AND (cdv.creation_date <= fnd_conc_date.string_to_date(po_create_t) OR po_create_t IS NULL)
            --采购员
         AND cdv.agent_id = nvl(p_agent_id,
                                cdv.agent_id)
            --供应商可验货日期
         AND (cdv.vendor_available_check_date >= fnd_conc_date.string_to_date(p_vendor_check_date_f) OR p_vendor_check_date_f IS NULL)
         AND (cdv.vendor_available_check_date <= fnd_conc_date.string_to_date(p_vendor_check_date_t) OR p_vendor_check_date_t IS NULL)
            --供应商入仓日期
         AND (cdv.vendor_receive_date >= fnd_conc_date.string_to_date(p_vendor_receive_date_f) OR p_vendor_receive_date_f IS NULL)
         AND (cdv.vendor_receive_date <= fnd_conc_date.string_to_date(p_vendor_receive_date_t) OR p_vendor_receive_date_t IS NULL)
            --要求到接收子仓日期
         AND (cdv.req_delivery_date >= fnd_conc_date.string_to_date(p_req_delivery_date_f) OR p_req_delivery_date_f IS NULL)
         AND (cdv.req_delivery_date <= fnd_conc_date.string_to_date(p_req_delivery_date_t) OR p_req_delivery_date_t IS NULL)
            --收货日期
         AND (cdv.receive_date >= fnd_conc_date.string_to_date(p_receive_date_f) OR p_receive_date_f IS NULL)
         AND (cdv.receive_date <= fnd_conc_date.string_to_date(p_receive_date_t) OR p_receive_date_t IS NULL)
            --是否已入库
         AND cdv.po_receive_status = nvl(p_receive_status,
                                         cdv.po_receive_status)
         AND cdv.cancel_flag = nvl(p_cancel_flag,
                                   cdv.cancel_flag) --add by wen 20210713 for RE20210712001
       ORDER BY cdv.po_number;
  BEGIN
    retcode := 0;
  
    ---============================开始输出=============================--
    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">');
    output('<head> <meta http-equiv="Content-Type" content="text/html; charset="' || g_charset || '">');
    output('<title>' || g_report_title || '</title>');
    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;}
                         .amount{mso-number-format:"\#,##0.00";text-decoration:none;}
                          .date{mso-number-format:"YYYY-MM-DD HH:MM:SS";text-decoration:none;}
                         </STYLE>');
    output('</head>');
  
    output('<body>');
    output('<table width="100%">');
    output('<tr>');
    output('<td colspan=18 align="center"><font size="5">' || g_report_title || '</font></td>');
    output('</tr>');
    output('</table>');
    output('<table width="100%" border="1" cellspacing="0" cellpadding="2"
                         style="BORDER-COLLAPSE:collapse"
                         bordercolorlight="#000000" bordercolordark="#000000"
                         id="table1">');
  
    print_header;
    FOR rec IN csr_main
    LOOP
    
      output('<tr>');
      output('<td nowrap class="numberx">' || rec.ou_name || '</td>');
      output('<td nowrap>' || rec.po_number || '</td>');
      output('<td nowrap>' || rec.vendor_number || '</td>');
      output('<td nowrap>' || rec.vendor_site_code || '</td>');
      output('<td nowrap>' || rec.make_country || '</td>');
      --5
      output('<td nowrap>' || to_char(rec.creation_date,
                                      g_data_format_time) || '</td>');
      output('<td nowrap>' || rec.agent_name || '</td>');
      output('<td nowrap>' || rec.create_user_name || '</td>');
      output('<td nowrap class="numberx">' || rec.item_number || '</td>');
      output('<td nowrap>' || rec.item_mini || '</td>');
      --10
      output('<td nowrap>' || rec.line_quantity || '</td>');
      output('<td nowrap>' || rec.shipment_display_num || '</td>');
      output('<td nowrap>' || rec.quantity || '</td>');
      output('<td nowrap>' || rec.req_number || '</td>');
      output('<td nowrap>' || rec.req_type || '</td>');
      --15
      output('<td nowrap>' || rec.sr_order_type || '</td>');
      output('<td nowrap>' || rec.sopl_number || '</td>');
      output('<td nowrap>' || rec.primeval_order_number || '</td>');
      output('<td nowrap>' || rec.sopl_comments || '</td>');
      output('<td nowrap>' || rec.shipment_method_desc || '</td>');
      output('<td nowrap>' || rec.region_desc || '</td>');
      --20
      output('<td nowrap>' || rec.region_org_code || '</td>');
      output('<td nowrap>' || rec.receive_subinventory_code || '</td>');
      output('<td nowrap>' || to_char(rec.vendor_available_check_date,
                                      g_data_format) || '</td>');
      output('<td nowrap>' || to_char(rec.vendor_receive_date,
                                      g_data_format) || '</td>');
      output('<td nowrap>' || rec.asins_br_code_type || '</td>');
      --25
      output('<td nowrap>' || rec.asins_msg || '</td>');
      output('<td nowrap>' || rec.asins_msku || '</td>');
      output('<td nowrap>' || rec.asins_br_code || '</td>');
      output('<td nowrap>' || rec.barcode_to_supplier || '</td>');
      output('<td nowrap>' || rec.anti_fake_sticker_flag || '</td>');
      --30
      output('<td nowrap>' || rec.delay_delivery_desc || '</td>');
      output('<td nowrap>' || rec.review_delay_desc || '</td>');
      output('<td nowrap>' || rec.special_info || '</td>');
      output('<td nowrap>' || rec.shipment_comments || '</td>');
      output('<td nowrap>' || to_char(rec.actual_qa_date,
                                      g_data_format) || '</td>');
      --35
      output('<td nowrap>' || rec.qa_result_desc || '</td>');
      output('<td nowrap>' || to_char(rec.req_delivery_date,
                                      g_data_format) || '</td>');
      output('<td nowrap>' || to_char(rec.promised_date,
                                      g_data_format) || '</td>');
      output('<td nowrap>' || to_char(rec.need_by_date,
                                      g_data_format) || '</td>');
      output('<td nowrap>' || rec.quantity_received || '</td>');
      --40
      output('<td nowrap>' || rec.po_receive_status || '</td>');
      output('<td nowrap>' || rec.lead_time || '</td>');
      output('<td nowrap>' || rec.adjust_days || '</td>');
      output('<td nowrap>' || rec.marked_flag || '</td>');
      output('<td nowrap>' || to_char(rec.erp_receive_date,
                                      g_data_format) || '</td>');
      --45
      output('<td nowrap>' || rec.fixed_lot_multiplier || '</td>');
      output('<td nowrap>' || rec.fixed_lot_flag_dsp || '</td>');
      output('<td nowrap>' || rec.delay_comments || '</td>');
      output('<td nowrap>' || rec.split_line_location_num || '</td>');
      output('<td nowrap>' || to_char(rec.split_date,
                                      g_data_format) || '</td>');
      --50
      output('<td nowrap>' || rec.cancel_flag_desc || '</td>');
      output('<td nowrap>' || rec.po_mps_flag_desc || '</td>');
      output('<td nowrap>' || to_char(rec.receive_date,
                                      g_data_format) || '</td>');
      output('<td nowrap>' || rec.area_flag_dsp || '</td>'); --add by wen 20211025 for RE20210906005
    
      output('<td nowrap>' || rec.color_barcode_lab || '</td>');
      output('<td nowrap>' || rec.color_special_lab || '</td>');
      output('<td nowrap>' || rec.inouter_box_lab || '</td>');
    
      output('</tr>');
    
    END LOOP;
  
    output('</table>');
    output('</html>');
  EXCEPTION
    WHEN fnd_api.g_exc_error THEN
      retcode := '1';
      errbuf  := SQLERRM;
    WHEN fnd_api.g_exc_unexpected_error THEN
      retcode := '2';
      errbuf  := SQLERRM;
    WHEN OTHERS THEN
      retcode := '2';
      errbuf  := SQLERRM;
  END main;

3、plsql+html输出

这种方式不需要定义模板,可在package中直接定义表的标题，然后输出行,html方便做单元格合并，还可以自由的定义单元格的颜色，比较灵活。

参考package模板：https://download.csdn.net/download/m0_67158898/86509577

报表要求：

HTML单元格的请自行百度，下面贴一下部分代码案例：

PROCEDURE output_html_line_1(p_str In Varchar2) is
  
  Begin
    output('<td nowrap align=center colspan="16"><B>' || p_str ||
           '</B></td>');
  End output_html_line_1;

  PROCEDURE output_html_line_2 is
  
  Begin
    output('<td nowrap align=center rowspan="2"><B>初始工程量</B></td>');
    output('<td nowrap align=center rowspan="2"><B>初始投资额</B></td>');
    output('<td nowrap align=center rowspan="2"><B>调整工程量</B></td>');
    output('<td nowrap align=center rowspan="2"><B>调整投资额</B></td>');
    output('<td nowrap align=center rowspan="2"><B>最终工程量</B></td>');
    output('<td nowrap align=center rowspan="2"><B>最终投资额</B></td>');
  
    output('<td nowrap align=center><B>工作完成量</B></td>');
    output('<td nowrap align=center><B>完成投资额(万元)</B></td>');
    output('<td nowrap align=center><B>工作完成量</B></td>');
    output('<td nowrap align=center><B>完成投资额(万元)</B></td>');
  
    output('<td nowrap align=center rowspan="2"><B>XX成立以来累计的工程量（期初）</B></td>');
    output('<td nowrap align=center rowspan="2"><B>XX成立以来累计的投资额（期初）</B></td>');
    output('<td nowrap align=center rowspan="2"><B>XX成立以来累计的工程量</B></td>');
    output('<td nowrap align=center rowspan="2"><B>XX成立以来累计的投资额</B></td>');
    output('<td nowrap align=center colspan="2"><B>总计</B></td>');
  
  End output_html_line_2;

  PROCEDURE output_html_line_3 is
  
  Begin
    output('<td nowrap align=center colspan="2"><B>本期间</B></td>');
    output('<td nowrap align=center colspan="2"><B>本财年</B></td>');
    output('<td nowrap align=center><B>工程量</B></td>');
    output('<td nowrap align=center><B>完成投资额(万元)</B></td>');
  
  End output_html_line_3;

  PROCEDURE output_data_line(p_id IN NUMBER) is
    Cursor cur_data(P_ORG_CODE In Varchar2) Is
      select 100 qty1,
             100 qty2,
             100 qty3,
             100 qty4,
             100 qty5,
             100 qty6,
             100 qty7,
             100 qty8,
             100 qty9,
             100 qty10,
             100 qty11,
             100 qty12,
             100 qty13,
             100 qty14,
             100 qty15,
             100 qty16
        from dual;
  BEGIN
    FOR rec_org IN cur_org LOOP
      for rec_data in cur_data(rec_org.ORG_CODE) loop
        output('<td nowrap>' || rec_data.qty1 || '</td>');
        output('<td nowrap>' || rec_data.qty2 || '</td>');
        output('<td nowrap>' || rec_data.qty3 || '</td>');
        output('<td nowrap>' || rec_data.qty4 || '</td>');
        output('<td nowrap>' || rec_data.qty5 || '</td>');
        output('<td nowrap>' || rec_data.qty6 || '</td>');
        output('<td nowrap>' || rec_data.qty7 || '</td>');
        output('<td nowrap>' || rec_data.qty8 || '</td>');
        output('<td nowrap>' || rec_data.qty9 || '</td>');
        output('<td nowrap>' || rec_data.qty10 || '</td>');
        output('<td nowrap>' || rec_data.qty11 || '</td>');
        output('<td nowrap>' || rec_data.qty12 || '</td>');
        output('<td nowrap>' || rec_data.qty13 || '</td>');
        output('<td nowrap>' || rec_data.qty14 || '</td>');
        output('<td nowrap>' || rec_data.qty15 || '</td>');
        output('<td nowrap>' || rec_data.qty16 || '</td>');
      end loop;
    end loop;
  END output_data_line;

  PROCEDURE main(retcode OUT VARCHAR2, errbuf OUT VARCHAR2, p_id IN NUMBER) IS
  
    cursor cur_TYPE is
      Select FLV.MEANING type, Flv.TAG Uom
        from CUX_FND_LOOKUP_VALUES FLV
       WHERE FLV.LOOKUP_TYPE = 'CUX_PM_PRO_IMAG_PROGRESS_CLASS';
    l_line_num Number := 0;
  BEGIN
    retcode := 0;
  
    ---============================开始输出=============================--
    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">');
    output('<head> <meta http-equiv="Content-Type" content="text/html; charset="' ||
           g_charset || '">');
    output('<title>' || g_report_title || '</title>');
    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;}
                         .amount{mso-number-format:"\#,##0.00";text-decoration:none;}
                          .date{mso-number-format:"YYYY-MM-DD HH:MM:SS";text-decoration:none;}
                         </STYLE>');
    output('</head>');
  
    output('<body>');
    output('<table width="100%">');
    output('<tr>');
    output('<td colspan=18 align="center"><font size="5">' ||
           g_report_title || '</font></td>');
    output('</tr>');
    output('</table>');
    output('<table width="100%" border="1" cellspacing="0" cellpadding="2"
                         style="BORDER-COLLAPSE:collapse"
                         bordercolorlight="#000000" bordercolordark="#000000"
                         id="table1">');
    --第一行 
    output('<tr>');
    output('<td nowrap align=center rowspan="2" colspan="3"><B></B></td>');
    FOR REC IN cur_org LOOP
      output_html_line_1(p_str => REC.org_NAME);
    END LOOP;
    output('</tr>');
  
    --第二行  
    output('<tr>');
    FOR REC IN cur_org LOOP
      output_html_line_2;
    END LOOP;
    output('</tr>');
  
    --第三行  
    output('<tr>');
    output('<td nowrap align=center><B>行号</B></td>');
    output('<td nowrap align=center><B>类型</B></td>');
    output('<td nowrap align=center><B>单位</B></td>');
    FOR REC IN cur_org LOOP
      output_html_line_3;
    END LOOP;
    output('</tr>');
  
    --数据行
    FOR REC IN cur_TYPE LOOP
      l_line_num := l_line_num + 1;
      output('<tr>');
      output('<td nowrap>' || l_line_num || '</td>');
      output('<td nowrap>' || rec.type || '</td>');
      output('<td nowrap>' || rec.uom || '</td>');
      output_data_line(p_id => p_id);
      output('</tr>');
    END LOOP;
    output('</table>');
    output('</html>');
  EXCEPTION
    WHEN fnd_api.g_exc_error THEN
      retcode := '1';
      errbuf  := SQLERRM;
    WHEN fnd_api.g_exc_unexpected_error THEN
      retcode := '2';
      errbuf  := SQLERRM;
    WHEN OTHERS THEN
      retcode := '2';
      errbuf  := SQLERRM;
  END;

最终输出结果：