1 PACKAGE BODY AS_JTF_RS_GROUP_MEM_TRG as
2 /* $Header: asxrstgb.pls 120.1 2005/12/22 22:54:09 subabu noship $ */
3 --
4 --
5 -- HISTORY
6 -- 11/17/00 ACNG Created
7 -- 03/19/01 ACNG Add logic to move sales group info
8 -- only if sales leads/opp is in open status
9 -- 03/20/01 ACNG Put profile options in order to move
10 -- opp/lead in open only/other status
11
12 -- NOTE
13 -- If a resource is moved from one group to another group
14 -- a record will be written into JTF_RS_GROUP_MEMBERS_AUD
15 -- The record will have new_resource_id, null old_resource_id,
16 -- new_group_id and old_group_id.
17 --
18 -- Only two options available for the profile
19 -- 1) AS_MOVE_OPPORTUNITIES : Open/All
20 -- 2) AS_MOVE_SALES_LEADS : Open/All
21 --
22
23 PROCEDURE Group_Mem_Trigger_Handler(
24 x_group_member_id NUMBER,
25 x_new_group_id NUMBER,
26 x_old_group_id NUMBER,
27 x_new_resource_id NUMBER,
28 x_old_resource_id NUMBER,
29 Trigger_Mode VARCHAR2 ) IS
30
31 l_opp_profile VARCHAR2(50);
32 l_lead_profile VARCHAR2(50);
33
34 l_forecast_id NUMBER := null;
35
36 CURSOR get_forecasts (sg_id number, sf_id number) IS
37 SELECT forecast_id
38 FROM as_internal_forecasts
39 WHERE sales_group_id = sg_id
40 AND salesforce_id = sf_id
41 AND status_code = 'SUBMITTED'
42 AND end_date_active is null;
43 BEGIN
44
45 l_opp_profile := upper(ltrim(rtrim(nvl(FND_PROFILE.Value('AS_MOVE_OPPORTUNITIES'),'OPEN'))));
46 l_lead_profile := upper(ltrim(rtrim(nvl(FND_PROFILE.Value('AS_MOVE_SALES_LEADS'),'OPEN'))));
47
48 IF(Trigger_Mode = 'ON-INSERT') THEN
49
50 IF(x_old_resource_id is null AND
51 x_new_group_id is not null AND
52 x_old_group_id is not null) THEN
53
54 -- Update Customer Access
55 update AS_ACCESSES_ALL acc
56 set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
57 where acc.salesforce_id = x_new_resource_id
58 and acc.sales_group_id = x_old_group_id
59 and acc.lead_id is null
60 and acc.sales_lead_id is null
61 and not exists
62 ( select 1
63 from AS_ACCESSES_ALL acc2
64 where acc2.sales_group_id = x_new_group_id
65 and acc2.salesforce_id = x_new_resource_id
66 and acc2.customer_id = acc.customer_id
67 and acc2.lead_id is null
68 and acc2.sales_lead_id is null
69 and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
70 and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) );
71
72 IF(l_opp_profile = 'OPEN') THEN
73
74 -- Update Open Opportunity's Access
75 update AS_ACCESSES_ALL acc
76 set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
77 where acc.salesforce_id = x_new_resource_id
78 and acc.sales_group_id = x_old_group_id
79 and acc.lead_id is not null
80 and acc.sales_lead_id is null
81 and not exists
82 ( select 1
83 from AS_ACCESSES_ALL acc2
84 where acc2.sales_group_id = x_new_group_id
85 and acc2.salesforce_id = x_new_resource_id
86 and acc2.customer_id = acc.customer_id
87 and acc2.lead_id = acc.lead_id
88 and acc2.sales_lead_id is null
89 and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
90 and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) )
91 and exists
92 ( select 1
93 from AS_LEADS_ALL ld, AS_STATUSES_B st
94 where acc.lead_id = ld.lead_id
95 and ld.status = st.status_code
96 and st.opp_open_status_flag = 'Y' );
97
98 -- Update Open Opportunities
99 update AS_SALES_CREDITS sc
100 set object_version_number = nvl(object_version_number,0) + 1, sc.salesgroup_id = x_new_group_id
101 where sc.salesforce_id = x_new_resource_id
102 and sc.salesgroup_id = x_old_group_id
103 and exists
104 ( select 1
105 from AS_LEADS_ALL ld, AS_STATUSES_B st
106 where ld.status = st.status_code
107 and st.opp_open_status_flag = 'Y'
108 and sc.lead_id = ld.lead_id );
109
110 update AS_LEADS_ALL ld
111 set object_version_number = nvl(object_version_number,0) + 1, ld.owner_sales_group_id = x_new_group_id
112 where ld.owner_salesforce_id = x_new_resource_id
113 and ld.owner_sales_group_id = x_old_group_id
114 and exists
115 ( select 1
116 from AS_STATUSES_B st
117 where ld.status = st.status_code
118 and st.opp_open_status_flag = 'Y');
119
120 ELSIF(l_opp_profile = 'ALL') THEN
121
122 update AS_ACCESSES_ALL acc
123 set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
124 where acc.salesforce_id = x_new_resource_id
125 and acc.sales_group_id = x_old_group_id
126 and acc.lead_id is not null
127 and acc.sales_lead_id is null
128 and not exists
129 ( select 1
130 from AS_ACCESSES_ALL acc2
131 where acc2.sales_group_id = x_new_group_id
132 and acc2.salesforce_id = x_new_resource_id
133 and acc2.customer_id = acc.customer_id
134 and acc2.lead_id = acc.lead_id
135 and acc2.sales_lead_id is null
136 and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
137 and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) );
138
139 update AS_SALES_CREDITS sc
140 set object_version_number = nvl(object_version_number,0) + 1, sc.salesgroup_id = x_new_group_id
141 where sc.salesforce_id = x_new_resource_id
142 and sc.salesgroup_id = x_old_group_id;
143
144 update AS_LEADS_ALL ld
145 set object_version_number = nvl(object_version_number,0) + 1, ld.owner_sales_group_id = x_new_group_id
146 where ld.owner_salesforce_id = x_new_resource_id
147 and ld.owner_sales_group_id = x_old_group_id;
148
149 END IF;
150
151 IF(l_lead_profile = 'OPEN') THEN
152
153 -- Update Open Sales Leads' Access
154 update AS_ACCESSES_ALL acc
155 set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
156 where acc.salesforce_id = x_new_resource_id
157 and acc.sales_group_id = x_old_group_id
158 and acc.sales_lead_id is not null
159 and acc.lead_id is null
160 and not exists
161 ( select 1
162 from AS_ACCESSES_ALL acc2
163 where acc2.sales_group_id = x_new_group_id
164 and acc2.salesforce_id = x_new_resource_id
165 and acc2.customer_id = acc.customer_id
166 and acc2.sales_lead_id = acc.sales_lead_id
167 and acc2.lead_id is null
168 and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
169 and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) )
170 and exists
171 ( select 1
172 from AS_SALES_LEADS sl, AS_STATUSES_B st
173 where acc.sales_lead_id = sl.sales_lead_id
174 and sl.status_code = st.status_code
175 and st.opp_open_status_flag = 'Y' );
176
177 -- Update Open Sales Leads
178 update AS_SALES_LEADS sl
179 set sl.assign_sales_group_id = x_new_group_id
180 where sl.assign_to_salesforce_id = x_new_resource_id
181 and sl.assign_sales_group_id = x_old_group_id
182 and exists
183 ( select 1
184 from AS_STATUSES_B st
185 where st.status_code = sl.status_code
186 and st.opp_open_status_flag = 'Y' );
187
188 ELSIF(l_lead_profile = 'ALL') THEN
189
190 -- Update Sales Leads' Access
191 update AS_ACCESSES_ALL acc
192 set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
193 where acc.salesforce_id = x_new_resource_id
194 and acc.sales_group_id = x_old_group_id
195 and acc.sales_lead_id is not null
196 and acc.lead_id is null
197 and not exists
198 ( select 1
199 from AS_ACCESSES_ALL acc2
200 where acc2.sales_group_id = x_new_group_id
201 and acc2.salesforce_id = x_new_resource_id
202 and acc2.customer_id = acc.customer_id
203 and acc2.sales_lead_id = acc.sales_lead_id
204 and acc2.lead_id is null
205 and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
206 and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) );
207
208 -- Update Sales Leads
209 update AS_SALES_LEADS sl
210 set sl.assign_sales_group_id = x_new_group_id
211 where sl.assign_to_salesforce_id = x_new_resource_id
212 and sl.assign_sales_group_id = x_old_group_id;
213
214 END IF;
215
216 update JTF_TERR_RSC_ALL jtr
217 set jtr.group_id = x_new_group_id
218 where jtr.resource_id = x_new_resource_id
219 and jtr.group_id = x_old_group_id;
220
221 END IF;
222
223 /* End date any active submitted forecasts, worksheets etc..*/
224
225 OPEN get_forecasts(x_old_group_id, x_new_resource_id);
226 LOOP
227 FETCH get_forecasts into l_forecast_id;
228 EXIT WHEN get_forecasts%NOTFOUND;
229
230 UPDATE as_prod_worksheet_lines
231 SET object_version_number = nvl(object_version_number,0) + 1, end_date_active = sysdate
232 WHERE forecast_id = l_forecast_id
233 AND end_date_active is null;
234
235 UPDATE as_forecast_worksheets
236 SET object_version_number = nvl(object_version_number,0) + 1, end_date_active = sysdate
237 WHERE forecast_id = l_forecast_id
238 AND end_date_active is null;
239
240 UPDATE as_internal_forecasts
241 SET object_version_number = nvl(object_version_number,0) + 1, end_date_active = sysdate
242 WHERE forecast_id = l_forecast_id
243 AND end_date_active is null;
244
245 END LOOP;
246 CLOSE get_forecasts;
247
248 /* End forecast related code */
249
250 END IF;
251
252 END Group_Mem_Trigger_Handler;
253
254 END AS_JTF_RS_GROUP_MEM_TRG;
[Home] [Help]