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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[select *from
(SELECT
ccv.project_number,
round(ccv.irr * 100, 2) || '%' irr,
ccv.dd_bank_account_name,
ccv.contract_number,
(SELECT bpv.bp_name
FROM hls_bp_master_v bpv
WHERE bpv.bp_id = ccv.bp_id_tenant) bpv_bp_name,
(select dbms_lob.substr(wmsys.wm_concat(h.bp_name))
from con_contract_bp b, hls_bp_master h
where b.enabled_flag = 'Y'
and b.bp_id = h.bp_id
and b.contract_id = ccv.contract_id
and b.bp_category = 'AGENT') bp_name,
cclil.product_plan_id_n,
ccv.invoice_price,
decode(cclil.purchase_tax_flag,'Y','是','N','否') purchase_tax_flag,
cclil.purchase_tax,
decode( cclil.insurance_flag,'Y','是','N','否') insurance_flag,
decode(cclil.insurance_flag,'N',null,cclil.insurance_amount) insurance_amount,
decode(cclil.travel_tax_flag,'Y','是','N','否') travel_tax_flag,
cclil.travel_tax,
decode( cclil.gps_flag,'Y','是','N','否') gps_flag,
cclil.gps_amount,
cclil.deposit,
cclil.down_payment,
cclil.lease_charge,
cclil.residual_value,
ccv.total_rental,
ccv.pmt,
ccv.finance_amount,
ccv.total_interest,
ccv.lease_times,
to_char(ccv.inception_of_lease,'yyyy-mm-dd') inception_of_lease,
-- to_date(to_char(ccv.inception_of_lease,'yyyy-mm-dd'),'yyyy-mm-dd') inception_of_lease,
-- ccv.inception_of_lease,
ccv.lease_end_date,
(SELECT g.gps_provider
FROM ast_car_gps g
WHERE g.contract_id = ccv.contract_id and rownum=1) gps_provider,
ccv.contract_status,
ccv.contract_status_n
FROM con_contract_lv ccv, con_contract_lease_item_lv cclil
WHERE ccv.contract_id = cclil.contract_id
and ccv.data_class = 'NORMAL') #WHERE_CLAUSE#
]]></bm:query-sql>
</bm:operation>
</bm:operations>
<bm:fields>
<bm:field name="project_number"/>
<bm:field name="irr"/>
<bm:field name="contract_number"/>
<bm:field name="bpv_bp_name"/>
<bm:field name="dd_bank_account_name"/>
<bm:field name="bp_name"/>
<bm:field name="product_plan_id_n"/>
<bm:field name="invoice_price" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="purchase_tax_flag"/>
<bm:field name="purchase_tax" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="insurance_flag"/>
<bm:field name="insurance_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="travel_tax_flag"/>
<bm:field name="travel_tax" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="gps_flag"/>
<bm:field name="gps_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="deposit" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="down_payment" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="lease_charge" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="residual_value" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="total_rental" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="pmt" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="finance_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="total_interest" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="lease_times"/>
<bm:field name="inception_of_lease"/>
<bm:field name="lease_end_date"/>
<bm:field name="gps_provider"/>
<bm:field name="contract_status"/>
<bm:field name="contract_status_n"/>
</bm:fields>
<bm:query-fields>
<bm:query-field name="bp_name" queryExpression="bp_name=${@bp_name}"/>
<bm:query-field name="project_number" queryExpression="upper(project_number) like '%' || upper(${@project_number}) || '%'"/>
<bm:query-field name="contract_number" queryExpression="upper(contract_number) like '%' || upper(${@contract_number}) || '%'"/>
<bm:query-field name="dd_bank_account_name" queryExpression="dd_bank_account_name=${@dd_bank_account_name}"/>
<bm:query-field name="gps_provider" queryExpression="gps_provider=${@gps_provider}"/>
<bm:query-field name="contract_status" queryExpression="contract_status=${@contract_status}"/>
<!-- <bm:query-field name="inception_of_lease" queryexpression="trunc(to_date(inception_of_lease,'yyyy-mm-dd')) = to_date(${@inception_of_lease},'yyyy-mm-dd')"/> -->
<bm:query-field name="inception_of_lease_from" queryexpression="trunc(to_date(inception_of_lease,'yyyy-mm-dd')) >= to_date(${@inception_of_lease_from},'yyyy-mm-dd')"/>
<bm:query-field name="inception_of_lease_to" queryexpression="trunc(to_date(inception_of_lease,'yyyy-mm-dd')) <= to_date(${@inception_of_lease_to},'yyyy-mm-dd')"/>
</bm:query-fields>
</bm:model>