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;
最终输出结果: