即时打印的XML报表不需要创建PLSQL程序包,功能顾问良师益友,写个简单的XML报表还是可以的。
其步骤大致分为如下:
-
创建XML文档。
-
创建RTF模板。
-
创建数据源和上传RTF模板。
-
创建请求并添加到你需要的请求组。
以下具体说明:
创建XML文档,其包括如下部分:
分别是参数、触发器、数据查询、数据结构。
参数:顾名思义,报表参数,用于输入查询条件。
触发器:用于执行系列的FUNCTION,比如用于初始化,这个非必输。
数据查询:SQL主体。
数据结构:输出结构。
文档格式示例如下:
举例说明,我要建立一个银企直连付款记录清单(客户化数据)主要取客户化付款表和发票,没有复杂报表控制及输出:
先建立XML数据如下:
下面是项目上的源码,参考如下:
<?xml version = '1.0' encoding = 'UTF-8'?>
<!-- $Header: CUXGLJEPRT.xml 115.1 2015/11/23 14:15:06 xdouser noship $ -->
<!-- dbdrv: none -->
<dataTemplate name="CUXGLJEPRT" version="1.0">
<parameters>
<parameter name="P0" dataType="number"/><!-- :P0 是否批量打印 0/1 -->
<parameter name="P1" dataType="number"/><!-- :P1 应用产品 GL 101,AP 200,AR 222 -->
<parameter name="P2" dataType="number"/><!-- :P2 打印凭证 单据ID GL:je_header_id AP:INVOICE_ID/CASH_RECEIPT_ID-->
<parameter name="P3" dataType="character"/><!-- :P3 单据类型 AP_INVOICES/AP_PAYMENTS-->
<parameter name="P4" dataType="number"/><!-- :P4 汇总模板 -->
</parameters>
<dataQuery>
<sqlStatement name="Q_LIST">
<![CDATA[
SELECT to_number(gjh.doc_sequence_value) AS DOC_VALUE
,to_char(gjh.default_effective_date, 'YYYY-MM-DD') AS GL_DATE
,gcck.segment1 AS COMPANY_SEG
,gjh.batch_name AS BATCH_NAME
,gjh.header_name AS HEADER_NAME
,gjl.je_line_num AS LINE_NUM
,gjh.period_name AS PERIOD_NAME
,nvl(gjh.currency_conversion_rate, 0) AS RATE
,gjct.user_je_category_name AS je_category
,gjl.entered_dr AS entered_dr
,gjl.entered_cr AS entered_cr
,gjl.accounted_dr AS accounted_dr
,gjl.accounted_cr AS accounted_cr
,(CASE
WHEN gjh.reversed_je_header_id IS NOT NULL
AND gjh.je_source = 'Manual' THEN
'冲销:' || gjl.description
ELSE
gjl.description
END) AS LINE_DESC
,nvl(length(gjl.description),0) AS LINE_DESC_LEN
,TRIM(gjs.user_je_source_name) AS JE_SOURCE
,gcck.concatenated_segments AS CONC_SEGMENTS
,nvl(length(gcck.concatenated_segments),0) AS CONC_SEGMENTS_LEN
,cux_gl_journalprint_pkg.get_ccid_desc('SQLGL'
,'GL#'
,gcck.chart_of_accounts_id
,gjl.code_combination_id) AS CONC_SEGMENTS_DESC
,nvl(length(cux_gl_journalprint_pkg.get_ccid_desc('SQLGL'
,'GL#'
,gcck.chart_of_accounts_id
,gjl.code_combination_id)),0) AS SEGMENTS_DESC_LEN
,gjh.je_info.posted_by AS POSTED_PERSON
,gjh.je_info.created_by AS CREATE_PERSON
,gjh.je_info.approved_by AS APPROVAL_PERSON
,gjh.je_info.acct_manager as fin_manager
,gjh.je_info.attachment_num AS ATTACHMENT
,ledge.currency_code AS LEDGER_CURRENCY
,gjh.currency_code AS CURRENCY
,DECODE(ledge.currency_code,gjh.currency_code,'Y','N') base_currency_flag
,gl_flexfields_pkg.get_description_sql(x_coa_id => gcck.chart_of_accounts_id
,x_seg_num => 1
,x_seg_val => gcck.segment1) COMPANY_NAME
,gjh.je_header_id
,cux_gl_journalprint_pkg.money_to_chinese(sum(nvl(gjl.accounted_dr, 0)) OVER(PARTITION BY gjh.je_header_id)) total_desc
FROM gl_ledgers ledge
,(SELECT gjb.name batch_name
,gjh.name header_name
,gjh.*
,cux_gl_journalprint_pkg.get_je_info(p_je_header_id => gjh.je_header_id) je_info
FROM gl_je_batches gjb
,gl_je_headers gjh
where gjb.je_batch_id = gjh.je_batch_id) gjh
,gl_je_lines gjl
,gl_je_sources_vl gjs
,gl_je_categories_vl gjct
,gl_code_combinations_kfv gcck
WHERE 1 = 1
AND ledge.ledger_id = gjh.ledger_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcck.code_combination_id
AND gjh.je_source = gjs.je_source_name
AND gjh.je_category = gjct.je_category_name
AND ((nvl(gjl.accounted_dr,0) <> 0 OR nvl(gjl.accounted_cr,0) <> 0)
OR (nvl(gjl.entered_dr,0) <> 0 OR nvl(gjl.entered_cr,0) <> 0)
OR NOT EXISTS
(SELECT 1
FROM gl_je_lines gjl2
WHERE gjl2.je_header_id = gjh.je_header_id
AND (nvl(gjl2.entered_dr, 0) <> 0 OR nvl(gjl2.entered_dr, 0) <> 0) ))
AND :p0 = 0
AND :p1 = 101
AND gjh.je_header_id = :p2
UNION ALL
SELECT to_number(gjh.doc_sequence_value) AS DOC_VALUE
,to_char(gjh.default_effective_date, 'YYYY-MM-DD') AS GL_DATE
,gcck.segment1 AS COMPANY_SEG
,gjh.batch_name AS BATCH_NAME
,gjh.header_name AS HEADER_NA