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
<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: chenlingfeng
$Date: 2017-7-14 上午10:10:50
$Revision: 1.0
$Purpose:
-->
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1">
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
select * from(
select t1.chance_bp_id chance_bp_id,
t1.chance_bp_name chance_bp_name,
t1.bp_class,
(select v.code_value_name
from sys_code_values_v v
where v.code = 'HLS211_BP_CLASS'
and v.code_value = t1.bp_class) bp_class_desc,
'CHANCE_BP' trx_category,
'潜在客户' trx_category_n,
t1.owner_user_id owner_user_id,
(select v.code_value_name
from sys_code_values_v v
where v.code = 'HLS211_BP_CLASS'
and v.code_value = t1.bp_class) as trx_number,
(select description
from sys_user su
where su.user_id = t1.owner_user_id) as owner_user_id_n,
trunc(t1.creation_date) as creation_date_desc,
(select un.unit_name
from exp_org_unit_v un
where un.unit_id = t1.unit_id) as unit_id_n
from hn_chance_bp t1
where t1.enabled_flag = 'Y'
and not exists
(select 1
from aut_trx_user_authorize a1, aut_owner_user_authorize a2
where a1.trx_category = 'CHANCE_BP'
and a1.trx_id = t1.chance_bp_id
and trunc(sysdate) between a1.start_date and
nvl(a1.end_date, trunc(sysdate))
and a1.user_id = a2.owner_user_id
and a1.trx_category = a2.trx_category
and a2.authorized_user_id = ${/session/@user_id}
and trunc(sysdate) between a2.start_date and
nvl(a2.end_date, trunc(sysdate)))
union all
select t2.bp_id chance_bp_id,
t2.bp_name chance_bp_name,
t2.bp_class,
(select v.code_value_name
from sys_code_values_v v
where v.code = 'HLS211_BP_CLASS'
and v.code_value = t2.bp_class) bp_class_desc,
'BP' trx_category,
'商业伙伴' trx_category_n,
t2.owner_user_id owner_user_id,
t2.bp_code trx_number,
(select description
from sys_user su
where su.user_id = t2.owner_user_id) as owner_user_id_n,
trunc(t2.creation_date) as creation_date_desc,
(select e1.unit_name
from exp_emp_assign_e_v e1
where e1.user_id = t2.owner_user_id
and e1.primary_position_flag = 'Y') as unit_id_n
from hls_bp_master t2
where t2.enabled_flag = 'Y'
and not exists
(select 1
from aut_trx_user_authorize a1, aut_owner_user_authorize a2
where a1.trx_category = 'BP'
and a1.trx_id = t2.bp_id
and trunc(sysdate) between a1.start_date and
nvl(a1.end_date, trunc(sysdate))
and a1.user_id = a2.owner_user_id
and a1.trx_category = a2.trx_category
and a2.authorized_user_id = ${/session/@user_id}
and trunc(sysdate) between a2.start_date and
nvl(a2.end_date, trunc(sysdate)))
)t1
#WHERE_CLAUSE#
]]></bm:query-sql>
</bm:operation>
</bm:operations>
<bm:fields>
<bm:field name="chance_bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="chance_bp_id" prompt="chance_bp_id"/>
<bm:field name="chance_bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="trx_name" prompt="trx_name"/>
<bm:field name="trx_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="trx_category" prompt="trx_category"/>
<bm:field name="trx_category_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="trx_category_n" prompt="trx_category_n"/>
<bm:field name="bp_class_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_class_n" prompt="bp_class_n"/>
<bm:field name="owner_user_id" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="document_owner_user_id" prompt="document_owner_user_id"/>
<bm:field name="trx_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="trx_number" prompt="trx_number"/>
<bm:field name="owner_user_id_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="owner_user_id_n" prompt="owner_user_id_n"/>
<bm:field name="creation_date_desc" databaseType="DATE" datatype="java.util.Date" expression="trunc(creation_date)" forInsert="false" forUpdate="false"/>
<bm:field name="unit_id_n" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false" physicalName="unit_id_n"/>
</bm:fields>
<bm:query-fields>
<bm:query-field name="chance_bp_name" queryExpression="t1.chance_bp_name like '%'||${@chance_bp_name}||'%'"/>
<bm:query-field name="bp_class_desc" queryExpression="t1.bp_class_desc =${@bp_class_desc}"/>
<bm:query-field name="owner_user_id_n" queryExpression="t1.owner_user_id_n like '%' || ${@owner_user_id_n}||'%'"/>
</bm:query-fields>
</bm:model>