1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: Hongquan.Dai
$Date: 2018-1-25 下午2:03:15
$Revision: 1.0
$Purpose: 保证金word打印
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
SELECT t1.invoice_hd_id,
t1.invoice_bp_name,--主承租人
t1.contract_number,--支付表编号
t1.total_amount,--发票金额
FND_CONVERT_TO_CHINESE_PKG.amount_convert_to_chinese(p_number => t1.total_amount) as chinese_amount,--发票金额大写
substr(to_char(sysdate,'yyyymmdd'),0,4) as year,--年
substr(to_char(sysdate,'yyyymmdd'),5,2) as month,--月
substr(to_char(sysdate,'yyyymmdd'),7,2) as day,--日
t1.project_number,
t1.project_name,
t1.invoice_bp_id,
t1.invoice_bp_code,
t1.invoice_title,
t1.invoice_number,
t1.invoice_status,
t1.invoice_status_desc,
t1.currency,
t1.business_type,
t1.business_type_desc,
t1.invoice_kind,
t1.invoice_kind_desc,
t1.document_type,
t1.created_by_name,
t1.created_by,
to_char(t1.accounting_date, 'yyyy-mm-dd') accounting_date,
t1.total_amount,
t1.tax_amount,
t1.billing_method,
t1.lease_organization,
t1.lease_channel,
t1.division,
t1.company_id,
t1.CREATE_JE_FLAG,
t1.spv_company_id
FROM acr_invoice_hd_v t1
WHERE
t1.invoice_hd_id = ${/parameter/@invoice_hd_id}
ORDER BY t1.invoice_date asc , t1.document_number asc
]]></bm:query-sql>
</bm:operation>
</bm:operations>
</bm:model>