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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: wangwei
$Date: 2015-1-26 上午11:32:01
$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
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
AND B.DOCUMENT_TYPE='CARLS') project_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('NEW','APPROVED_RETURN') AND B.DOCUMENT_TYPE='CARLS') NO_SUBMIT_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('APPROVING') AND B.DOCUMENT_TYPE='CARLS') APPROVING_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CLOSED','CANCEL') AND B.DOCUMENT_TYPE='CARLS') CLOSE_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('APPROVED','CONTRACT_CREATED','REJECT' ) AND B.DOCUMENT_TYPE='CARLS'
and not exists(select 1 from zj_wfl_approve_history_v his
where his.instance_id=b.wfl_instance_id and his.action_type='2')) return_APPROVED_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('APPROVED','CONTRACT_CREATED','REJECT' ) AND B.DOCUMENT_TYPE='CARLS'
and exists(select 1 from zj_wfl_approve_history_v wfl_his
where wfl_his.instance_id=b.wfl_instance_id and wfl_his.action_type='2')) APPROVED_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('APPROVED','CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and not exists(SELECT 1
FROM yonda_doc_status_history his
WHERE his.document_category = 'PROJECT'
AND his.document_id = b.project_id
AND his.status = '290'))no_CONDITIONAL_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('APPROVED','CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and exists(SELECT 1
FROM yonda_doc_status_history his
WHERE his.document_category = 'PROJECT'
AND his.document_id = b.project_id
AND his.status = '290')) CONDITIONAL_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('REJECT' ) AND B.DOCUMENT_TYPE='CARLS') REJECT_NUMBER ,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and exists (select 1 from con_contract con where con.project_id=b.project_id
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_create_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED','CANCEL' ) AND B.DOCUMENT_TYPE='CARLS'
and exists (select 1 from con_contract con
where con.project_id=b.project_id and con.contract_status='CANCEL'
)
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_cancel_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and exists (select 1 from con_contract con
where con.project_id=b.project_id and con.signing_date is not null
and con.contract_status!='CANCEL'
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_sign_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and exists (select 1 from con_contract con
where con.project_id=b.project_id and con.signing_date is null
and con.contract_status!='CANCEL'
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_unsign_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and exists (select 1 from con_contract con,YONDA_DOC_STATUS_HISTORY_V DOC
where con.project_id=b.project_id AND CON.CONTRACT_ID=DOC.document_id
AND DOC.document_category='CONTRACT' AND DOC.status='280'
and con.contract_status!='CANCEL'
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_INCEPT_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and NOT exists (select 1 from con_contract con,YONDA_DOC_STATUS_HISTORY_V DOC
where con.project_id=b.project_id AND CON.CONTRACT_ID=DOC.document_id
AND DOC.document_category='CONTRACT' AND DOC.status='280'
and con.contract_status!='CANCEL'
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_no_INCEPT_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and exists (select 1 from con_contract con,con_contract_lease_item item,
con_contract_item_detail detail,ast_car_gps gps
where con.project_id=b.project_id and con.contract_id=item.contract_id
and item.contract_lease_item_id=detail.item_detail_id
and detail.item_detail_id=gps.item_detail_id
and gps.enabled_flag='Y'
and con.contract_status!='CANCEL'
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_gps_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and not exists (select 1 from con_contract con,con_contract_lease_item item,
con_contract_item_detail detail,ast_car_gps gps
where con.project_id=b.project_id and con.contract_id=item.contract_id
and item.contract_lease_item_id=detail.item_detail_id
and detail.item_detail_id=gps.item_detail_id
and gps.enabled_flag='Y'
and con.contract_status!='CANCEL'
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_no_gps_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and exists (select 1 from con_contract con,con_contract_lease_item item,
con_contract_item_detail detail,ast_car_license license
where con.project_id=b.project_id and con.contract_id=item.contract_id
and item.contract_lease_item_id=detail.item_detail_id
and detail.item_detail_id=license.item_detail_id
and license.enabled_flag='Y'
and con.contract_status!='CANCEL'
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_licence_NUMBER,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and exists (select 1 from con_contract con,con_contract_lease_item item,
con_contract_item_detail detail,ast_car_insurance insurance
where con.project_id=b.project_id and con.contract_id=item.contract_id
and item.contract_lease_item_id=detail.item_detail_id
and detail.item_detail_id=insurance.item_detail_id
and con.contract_status!='CANCEL'
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_insurance_NUMBER ,
(select count(1) from prj_project b where b.creation_date>=trunc(nvl(to_date(${@start_date},'yyyy-mm-dd'),b.creation_date))
and trunc(b.creation_date)<=nvl(to_date(${@end_date},'yyyy-mm-dd'),b.creation_date)
and b.project_status IN ('CONTRACT_CREATED' ) AND B.DOCUMENT_TYPE='CARLS'
and exists (select 1 from con_contract con,YONDA_DOC_STATUS_HISTORY_V DOC
where con.project_id=b.project_id AND CON.CONTRACT_ID=DOC.document_id
AND DOC.document_category='CONTRACT' AND DOC.status='240'
and con.contract_status!='CANCEL'
AND CON.DATA_CLASS='NORMAL')
and exists(select 1 from con_contract con where con.project_id=b.project_id )) contract_GET_CAR_NUMBER
from dual
]]></bm:query-sql>
</bm:operation>
</bm:operations>
</bm:model>