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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: ZLF
$Date: 2015-1-15 下午8:46:58
$Revision: 1.0
$Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
SELECT
*
FROM
(SELECT
c.contract_id,
c.contract_number,
c.bp_id_tenant,
(SELECT h.bp_name FROM hls_bp_master h WHERE h.bp_id = c.bp_id_tenant
) bp_name,
cli.brand_id,
(SELECT
bv.description
FROM
hls_car_brands_vl bv
WHERE
bv.brand_id = cli.brand_id
) brand_des,
cli.series_id,
(SELECT
sv.description
FROM
hls_car_series_vl sv
WHERE
sv.series_id = cli.series_id
) series_des,
cli.model_id,
(SELECT
mv.description
FROM
hls_car_model_vl mv
WHERE
mv.model_id = cli.model_id
) model_des,
cid.item_frame_number,
c.contract_status,
(SELECT
v.code_value_name
FROM
sys_code_values_v v
WHERE
v.code ='CON500_CONTRACT_STATUS' AND
v.code_value=c.contract_status
) contract_status_des,
(NVL(
(SELECT
'已结清'
FROM
dual
WHERE
NOT EXISTS
(SELECT
*
FROM
con_contract_cashflow ccc
WHERE
ccc.contract_id =c.contract_id AND
ccc.write_off_flag IN ('NOT','PARTIAL')
)
),'未结清')) terminate_flag_des,
(NVL(
(SELECT
'过户已完成'
FROM
dual
WHERE
EXISTS
(SELECT
*
FROM
con_contract_item_detail cd
WHERE
cd.item_detail_id =cid.item_detail_id AND
NVL(cd.ownership_transfer_flag,'N')='Y' AND
cd.transfer_date IS NOT NULL
)
),'过户未完成')) transfer_flag_des,
(NVL(
(SELECT
'已解除抵押'
FROM
dual
WHERE
EXISTS
(SELECT
*
FROM
ast_car_license al
WHERE
al.item_detail_id =cid.item_detail_id AND
NVL(al.relieve_mortgage_flag,'N')='Y' AND
al.relieve_mortgage_date IS NOT NULL
)
),'未解除抵押')) relieve_mortgage_flag_des,
(SELECT
MAX(ai.insurance_date_to)
FROM
ast_car_insurance ai
WHERE
ai.item_detail_id=cid.item_detail_id AND
ai.insurance_type='10'
) compulsory_insurance_end_date,
(SELECT
MAX(ai.insurance_date_to)
FROM
ast_car_insurance ai
WHERE
ai.item_detail_id=cid.item_detail_id AND
ai.insurance_type='20'
) commercial_insurance_end_date
FROM
con_contract c,
con_contract_lease_item cli,
con_contract_item_detail cid
WHERE
c.contract_id = cli.contract_id AND
cli.contract_lease_item_id = cid.contract_lease_item_id AND
c.data_class = 'NORMAL' AND
c.contract_status in ('ET','ETING','TERMINATE')
) t1 #WHERE_CLAUSE#
]]></bm:query-sql>
</bm:operation>
</bm:operations>
<bm:query-fields>
<bm:query-field name="contract_number" queryExpression="t1.contract_number like ${@contract_number}"/>
<bm:query-field name="bp_name" queryExpression="t1.bp_name like ${@bp_name}"/>
</bm:query-fields>
</bm:model>