-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathV2__crm_views.sql
65 lines (60 loc) · 2.63 KB
/
V2__crm_views.sql
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
create view bpartner_detail_v as
select B.*,
A.c_contactactivity_id as c_contactactivity_id,
A.ad_client_id as activity_ad_client_id,
A.ad_org_id as activity_ad_org_id,
A.created as activity_created,
A.createdby as activity_createdby,
A.description as activity_description,
A.isactive as activity_isactive,
A.updated as activity_updated,
A.updatedby as activity_updatedby,
A.startdate as activity_startdate,
A.enddate as activity_enddate,
A.comments as activity_comments,
A.salesrep_id as activity_salesrep_id,
A.ad_user_id as activity_ad_user_id,
A.contactactivitytype as activity_contactactivitytype,
A.iscomplete as activity_iscomplete,
A.c_opportunity_id as activity_c_opportunity_id,
A.c_contactactivity_uu as activity_c_contactactivity_uu,
C.name as category_name
from adempiere.c_bpartner B
left join c_opportunity O on B.c_bpartner_id = O.c_bpartner_id
left join c_contactactivity A on A.c_opportunity_id = O.c_opportunity_id
left join crm_customer_category CC on CC.c_bpartner_id = B.c_bpartner_id
left join crm_category C on C.crm_category_id = CC.crm_category_id;
create or replace view bpartner_v as
select B.*,
A.c_contactactivity_id as c_contactactivity_id,
A.ad_client_id as activity_ad_client_id,
A.ad_org_id as activity_ad_org_id,
A.created as activity_created,
A.createdby as activity_createdby,
A.description as activity_description,
A.isactive as activity_isactive,
A.updated as activity_updated,
A.updatedby as activity_updatedby,
A.startdate as activity_startdate,
A.enddate as activity_enddate,
A.comments as activity_comments,
A.salesrep_id as activity_salesrep_id,
A.ad_user_id as activity_ad_user_id,
A.contactactivitytype as activity_contactactivitytype,
A.iscomplete as activity_iscomplete,
A.c_opportunity_id as activity_c_opportunity_id,
A.c_contactactivity_uu as activity_c_contactactivity_uu,
C.name as category_name
from adempiere.c_bpartner B
left join c_opportunity O on B.c_bpartner_id = O.c_bpartner_id
left join c_contactactivity A on A.c_opportunity_id = O.c_opportunity_id
left join crm_customer_category CC on CC.c_bpartner_id = B.c_bpartner_id
left join crm_category C on C.crm_category_id = CC.crm_category_id
where (B.c_bpartner_id, coalesce(A.c_contactactivity_id,0)) IN
(
select B.c_bpartner_id, coalesce(max(A.c_contactactivity_id),0) as c_contactactivity_id from adempiere.c_bpartner B
left join c_opportunity O on B.c_bpartner_id = O.c_bpartner_id
left join c_contactactivity A on A.c_opportunity_id = O.c_opportunity_id
where coalesce(A.isactive,'Y') = 'Y'
group by B.c_bpartner_id
)