[Home] [Help]
PACKAGE BODY: APPS.AS_RTTAP_OPPTY
Source
1 PACKAGE BODY AS_RTTAP_OPPTY as
2 /* $Header: asxrtopb.pls 120.11 2006/10/11 11:41:02 sariff noship $ */
3
4 G_ENTITY CONSTANT VARCHAR2(17) := 'OPPTYS::RT::';
5 G_LEAD_ID NUMBER;
6 G_PKG_NAME CONSTANT VARCHAR2(15) := 'AS_RTTAP_OPPTY';
7 PROCEDURE RTTAP_WRAPPER(
8 P_Api_Version_Number IN NUMBER,
9 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
10 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
11 p_LEAD_ID IN NUMBER,
12 X_Return_Status OUT NOCOPY VARCHAR2,
13 X_Msg_Count OUT NOCOPY NUMBER,
14 X_Msg_Data OUT NOCOPY VARCHAR2
15 )
16 IS
17 l_errbuf VARCHAR2(4000);
18 l_retcode VARCHAR2(255);
19 l_msg_count NUMBER;
20 l_msg_data VARCHAR2(1000);
21 l_trans_rec JTY_ASSIGN_REALTIME_PUB.bulk_trans_id_type;
22 l_WinningTerrMember_tbl JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
23 l_api_name CONSTANT VARCHAR2(30) := 'RTTAP_WRAPPER';
24 l_api_version_number CONSTANT NUMBER := 1.0;
25 L_RETURN_STATUS VARCHAR2(10);
26 BEGIN
27 G_LEAD_ID := p_lead_id;
28 SAVEPOINT RTTAP_WRAPPER_PVT;
29
30 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
31 AS_GAR.G_DEBUG_FLAG := 'Y';
32 END IF;
33 -- IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
34 AS_GAR.LOG(G_ENTITY || G_PKG_NAME || AS_GAR.G_START);
35
36 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
37 p_api_version_number,
38 l_api_name,
39 G_PKG_NAME)
40 THEN
41 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
42 END IF;
43
44 -- Initialize message list if p_init_msg_list is set to TRUE.
45 IF FND_API.to_Boolean( p_init_msg_list ) THEN
46 FND_MSG_PUB.initialize;
47 END IF;
48 -- Initialize API return status to SUCCESS
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50
51 IF NVL(FND_PROFILE.Value('AS_ENABLE_OPP_ONLINE_TAP'), 'N') <> 'Y' THEN
52 /*------------------------------------------------------+
53 | If REALTIME TAP profile is turned on there is NO need
54 | to insert into changed accounts since the oppty is
55 | processed immediately.
56 +-------------------------------------------------------*/
57 INSERT INTO AS_CHANGED_ACCOUNTS_ALL
58 ( customer_id,
59 address_id,
60 lead_id,
61 last_update_date,
62 last_updated_by,
63 creation_date,
64 created_by,
65 last_update_login,
66 change_type )
67 SELECT customer_id,
68 address_id,
69 lead_id,
70 SYSDATE,
71 0,
72 SYSDATE,
73 0,
74 0,
75 'OPPORTUNITY'
76 FROM AS_LEADS_ALL LDS
77 WHERE lead_id = G_LEAD_ID
78 AND NOT EXISTS
79 ( SELECT 'X'
80 FROM AS_CHANGED_ACCOUNTS_ALL ACC
81 WHERE LDS.customer_id = ACC.customer_id
82 --AND LDS.address_id = ACC.address_id -- fix for bug#5116019
83 AND LDS.lead_id = ACC.lead_id
84 AND ACC.request_id IS NULL );
85
86 ELSE
87 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
88 l_trans_rec.trans_object_id1 := jtf_terr_number_list(G_LEAD_ID);
89 l_trans_rec.trans_object_id2 := jtf_terr_number_list(null);
90 l_trans_rec.trans_object_id3 := jtf_terr_number_list(null);
91 l_trans_rec.trans_object_id4 := jtf_terr_number_list(null);
92 l_trans_rec.trans_object_id5 := jtf_terr_number_list(null);
93 l_trans_rec.txn_date := jtf_terr_date_list(null);
94 JTY_ASSIGN_REALTIME_PUB.get_winners(
95 p_api_version_number => 1.0,
96 p_init_msg_list => FND_API.G_FALSE,
97 p_source_id => -1001,
98 p_trans_id => -1004,
99 p_mode => 'REAL TIME:RESOURCE',
100 p_param_passing_mechanism => 'PBR',
101 p_program_name => 'SALES/OPPORTUNITY PROGRAM',
102 p_trans_rec => l_trans_rec,
103 p_name_value_pair => null,
104 p_role => null,
105 p_resource_type => null,
106 x_return_status => l_return_status,
107 x_msg_count => l_msg_count,
108 x_msg_data => l_msg_data,
109 x_winners_rec => l_WinningTerrMember_tbl);
110 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
111 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
112 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
113 FND_MSG_PUB.Count_And_Get
114 ( p_count => l_msg_count,
115 p_data => l_msg_data
116 );
117 AS_UTILITY_PVT.Get_Messages(l_msg_count, l_msg_data);
118 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_msg_data, 'ERROR');
119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
120 END IF;
121
122 IF (l_WinningTerrMember_tbl.resource_id.count > 0) THEN
123 FOR i IN l_WinningTerrMember_tbl.terr_id.FIRST .. l_WinningTerrMember_tbl.terr_id.LAST LOOP
124 AS_GAR.LOG(G_ENTITY || 'Trans Object ID : ' || l_WinningTerrMember_tbl.trans_object_id(i) ||
125 'Trans Detail Object ID : ' || l_WinningTerrMember_tbl.trans_detail_object_id(i) ||
126 'Terr ID : ' || l_WinningTerrMember_tbl.terr_id(i) || ' Terr Name : ' || l_WinningTerrMember_tbl.terr_name(i) ||
127 ' Resource ID : ' || l_WinningTerrMember_tbl.resource_id(i) ||
128 ' Resource TYPE : ' || l_WinningTerrMember_tbl.resource_type(i));
129 END LOOP;
130 -- Explode GROUPS if any inside winners
131 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
132 AS_RTTAP_OPPTY.EXPLODE_GROUPS_OPPTYS(
133 x_errbuf => l_errbuf,
134 x_retcode => l_retcode,
135 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
136 x_return_status => l_return_status);
137
138 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
139 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
140
141 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
142 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
144 End If;
145
146 -- Explode TEAMS if any inside winners
147 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
148 AS_RTTAP_OPPTY.EXPLODE_TEAMS_OPPTYS(
149 x_errbuf => l_errbuf,
150 x_retcode => l_retcode,
151 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
152 x_return_status => l_return_status);
153
154 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
155 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
156
157 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
158 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
160 End If;
161
162 -- Set team leader for Opptys
163 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
164 AS_RTTAP_OPPTY.SET_TEAM_LEAD_OPPTYS(
165 x_errbuf => l_errbuf,
166 x_retcode => l_retcode,
167 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
168 x_return_status => l_return_status);
169
170 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
171 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
172
173 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
174 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
176 End If;
177
178 -- Insert into Oppty Accesses from Winners
179 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
180 AS_RTTAP_OPPTY.INSERT_ACCESSES_OPPTYS(
181 x_errbuf => l_errbuf,
182 x_retcode => l_retcode,
183 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
184 x_return_status => l_return_status);
185
186 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
187 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
188
189 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
190 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
192 End If;
193
194 -- Insert into territory Accesses
195 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
196 AS_RTTAP_OPPTY.INSERT_TERR_ACCESSES_OPPTYS(
197 x_errbuf => l_errbuf,
198 x_retcode => l_retcode,
199 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
200 x_return_status => l_return_status);
201
202 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
203 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
204
205 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
206 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 End If;
209 END IF;
210 -- Remove (soft delete) records in access table that are not qualified
211 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
212 AS_RTTAP_OPPTY.PERFORM_OPPTY_CLEANUP(
213 x_errbuf => l_errbuf,
214 x_retcode => l_retcode,
215 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
216 x_return_status => l_return_status);
217
218 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
219 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
220
221 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
222 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 End If;
225
226 -- Opportunity Owner assignment
227 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_START);
228 /* ----------------------------------------------------------------------+
229 | G_TAP_FLAG is set to avoid calling the leads buid trigger .
230 +------------------------------------------------------------------------*/
231 AS_GAR.G_TAP_FLAG := 'Y';
232 AS_RTTAP_OPPTY.ASSIGN_OPPTY_OWNER(
233 x_errbuf => l_errbuf,
234 x_retcode => l_retcode,
235 p_WinningTerrMember_tbl => l_WinningTerrMember_tbl,
236 x_return_status => l_return_status);
237 AS_GAR.G_TAP_FLAG := 'N';
238 /* ----------------------------------------------------------------------+
239 | G_TAP_FLAG is reset.
240 +------------------------------------------------------------------------*/
241 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_END);
242 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_RETURN_STATUS || l_return_status);
243
244 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
245 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO, l_errbuf, l_retcode);
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 End If;
248
249 -- Opportunity Raising Business Event
250 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || 'Raising BE' || AS_GAR.G_START);
251
252 AS_RTTAP_OPPTY.RAISE_BUSINESS_EVENT(
253 x_errbuf => l_errbuf,
254 x_retcode => l_retcode,
255 x_return_status => l_return_status);
256
257 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
258 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || 'Raising BE' , l_errbuf, l_retcode);
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 End If;
261 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_END);
262 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_RETURN_STATUS || l_return_status);
263
264 END IF;
265
266 -- Reset AS_ACCESSES_ALL.open_flag added for bug 5592395
267
268 UPDATE AS_ACCESSES_ALL acc
269 SET object_version_number = nvl(object_version_number,0) + 1, acc.OPEN_FLAG = 'Y'
270 WHERE acc.LEAD_ID = p_lead_id
271 AND EXISTS
272 (select 1
273 from as_leads_all ld,
274 as_statuses_b st
275 where st.opp_open_status_flag = 'Y'
276 and st.status_code = ld.status
277 and ld.lead_id = p_lead_id )
278 AND nvl(acc.OPEN_FLAG, 'N') <> 'Y';
279
280 UPDATE AS_ACCESSES_ALL acc
281 SET object_version_number = nvl(object_version_number,0) + 1, acc.OPEN_FLAG = 'N'
282 WHERE acc.LEAD_ID = p_lead_id
283 AND NOT EXISTS
284 (select 1
285 from as_leads_all ld,
286 as_statuses_b st
287 where st.opp_open_status_flag = 'Y'
288 and st.status_code = ld.status
289 and ld.lead_id = p_lead_id )
290 AND acc.OPEN_FLAG IS NOT NULL;
291
292
293 -- Standard check for p_commit
294 IF FND_API.to_Boolean( p_commit ) THEN
295 COMMIT WORK;
296 END IF;
297 EXCEPTION
298 WHEN FND_API.G_EXC_ERROR THEN
299 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
300 P_MODULE => 'as.plsql.tap.realtime'
301 ,P_API_NAME => l_api_name
302 ,P_PKG_NAME => G_PKG_NAME
303 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
304 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
305 ,X_MSG_COUNT => l_msg_count
306 ,X_MSG_DATA => l_msg_data
307 ,X_RETURN_STATUS => l_return_status);
308 X_Return_Status := FND_API.G_RET_STS_ERROR;
309 X_Msg_Count := 1;
310 X_Msg_Data := 'ERROR IN OPPTY REALTIME TAP ASSIGNMENT';
311
312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
313 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
314 P_MODULE => 'as.plsql.tap.realtime'
315 ,P_API_NAME => l_api_name
316 ,P_PKG_NAME => G_PKG_NAME
317 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
318 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
319 ,X_MSG_COUNT => l_msg_count
320 ,X_MSG_DATA => l_msg_data
321 ,X_RETURN_STATUS => l_return_status);
322 X_Return_Status := FND_API.G_RET_STS_ERROR;
323 X_Msg_Count := 1;
324 X_Msg_Data := 'ERROR IN OPPTY REALTIME TAP ASSIGNMENT';
325
326 WHEN OTHERS THEN
327 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
328 P_MODULE => 'as.plsql.tap.realtime'
329 ,P_API_NAME => l_api_name
330 ,P_PKG_NAME => G_PKG_NAME
331 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
332 ,P_SQLCODE => SQLCODE
333 ,P_SQLERRM => SQLERRM
334 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
335 ,X_MSG_COUNT => l_msg_count
336 ,X_MSG_DATA => l_msg_data
337 ,X_RETURN_STATUS => l_return_status);
338 X_Return_Status := FND_API.G_RET_STS_ERROR;
339 X_Msg_Count := 1;
340 X_Msg_Data := 'ERROR IN OPPTY REALTIME TAP ASSIGNMENT';
341 END RTTAP_WRAPPER;
342
343
344 /************************** Start Explode Teams Opptys ******************/
345 PROCEDURE EXPLODE_TEAMS_OPPTYS(
346 x_errbuf OUT NOCOPY VARCHAR2,
347 x_retcode OUT NOCOPY VARCHAR2,
348 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
349 x_return_status OUT NOCOPY VARCHAR2)
350 IS
351
352 /*-------------------------------------------------------------------------+
353 | LOGIC
354 |
355 | A RESOURCE team can be comprised OF resources who belong TO one OR more
356 | GROUPS OF resources.
357 | So get a LIST OF team members (OF TYPE employee OR partner OR parter contact
358 | AND play a ROLE OF salesrep ) AND get atleast one GROUP id that they belong TO
359 | WHERE they play a similar ROLE.
360 | UNION THE above WITH a LIST OF ALL members OF ALL GROUPS which BY themselves
361 | are a RESOURCE within a team.
362 | INSERT these members INTO winners IF they are NOT already IN winners.
363 +-------------------------------------------------------------------------*/
364
365 l_errbuf VARCHAR2(4000);
366 l_retcode VARCHAR2(255);
367 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
368
369 l_resource_id num_list;
370 l_group_id num_list;
371 l_person_id num_list;
372
373
374 BEGIN
375 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
376 x_return_status := FND_API.G_RET_STS_SUCCESS;
377 -- l_resource_type := 'RS_TEAM';
378 /* Get resources within a resource team */
379 /** Note
380 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
381 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
382 **/
383 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
384 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
385 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
386 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_TEAM' THEN
387
388 SELECT resource_id, group_id , person_id
389 BULK COLLECT INTO l_resource_id, l_group_id,l_person_id
390 FROM
391 (
392 SELECT TM.team_resource_id resource_id,
393 TM.person_id person_id2,
394 MIN(G.group_id)group_id,
395 MIN(T.team_id) team_id,
396 TRES.CATEGORY resource_category,
397 MIN(TRES.source_id) person_id
398 FROM jtf_rs_team_members TM, jtf_rs_teams_b T,
399 jtf_rs_team_usages TU, jtf_rs_role_relations TRR,
400 jtf_rs_roles_b TR, jtf_rs_resource_extns TRES,
401 (
402 SELECT m.group_id group_id, m.resource_id resource_id
403 FROM jtf_rs_group_members m,
404 jtf_rs_groups_b g,
405 jtf_rs_group_usages u,
406 jtf_rs_role_relations rr,
407 jtf_rs_roles_b r,
408 jtf_rs_resource_extns res
409 WHERE m.group_id = g.group_id
410 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
411 AND NVL(g.end_date_active,SYSDATE)
412 AND u.group_id = g.group_id
413 AND u.usage IN ('SALES','PRM')
414 AND m.group_member_id = rr.role_resource_id
415 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
416 AND rr.delete_flag <> 'Y'
417 AND SYSDATE BETWEEN rr.start_date_active
418 AND NVL(rr.end_date_active,SYSDATE)
419 AND rr.role_id = r.role_id
420 AND r.role_type_code
421 IN ('SALES', 'TELESALES', 'FIELDSALES','PRM')
422 AND r.active_flag = 'Y'
423 AND res.resource_id = m.resource_id
424 AND res.CATEGORY IN ('EMPLOYEE')--,'PARTY','PARTNER')
425 ) G
426 WHERE tm.team_id = t.team_id
427 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
428 AND NVL(t.end_date_active,SYSDATE)
429 AND tu.team_id = t.team_id
430 AND tu.usage IN ('SALES','PRM')
431 AND tm.team_member_id = trr.role_resource_id
432 AND tm.delete_flag <> 'Y'
433 AND tm.resource_type = 'INDIVIDUAL'
434 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
435 AND trr.delete_flag <> 'Y'
436 AND SYSDATE BETWEEN trr.start_date_active
437 AND NVL(trr.end_date_active,SYSDATE)
438 AND trr.role_id = tr.role_id
439 AND tr.role_type_code IN
440 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
441 AND tr.active_flag = 'Y'
442 AND tres.resource_id = tm.team_resource_id
443 AND tres.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
444 AND tm.team_resource_id = g.resource_id
445 GROUP BY tm.team_resource_id,
446 tm.person_id,
447 tres.CATEGORY,
448 tres.source_id
449 UNION ALL
450 SELECT MIN(m.resource_id) resource_id,
451 MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
452 MIN(jtm.team_id) team_id, res.CATEGORY resource_category,
453 MIN(res.source_id) person_id
454 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
455 jtf_rs_group_usages u, jtf_rs_role_relations rr,
456 jtf_rs_roles_b r, jtf_rs_resource_extns res,
457 (
458 SELECT tm.team_resource_id group_id,
459 t.team_id team_id
460 FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
461 jtf_rs_team_usages tu,jtf_rs_role_relations trr,
462 jtf_rs_roles_b tr, jtf_rs_resource_extns tres
463 WHERE tm.team_id = t.team_id
464 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
465 AND NVL(t.end_date_active,SYSDATE)
466 AND tu.team_id = t.team_id
467 AND tu.usage IN ('SALES','PRM')
468 AND tm.team_member_id = trr.role_resource_id
469 AND tm.delete_flag <> 'Y'
470 AND tm.resource_type = 'GROUP'
471 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
472 AND trr.delete_flag <> 'Y'
473 AND SYSDATE BETWEEN trr.start_date_active
474 AND NVL(trr.end_date_active,SYSDATE)
475 AND trr.role_id = tr.role_id
476 AND tr.role_type_code IN
477 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
478 AND tr.active_flag = 'Y'
479 AND tres.resource_id = tm.team_resource_id
480 AND tres.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
481 ) jtm
482 WHERE m.group_id = g.group_id
483 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
484 AND NVL(g.end_date_active,SYSDATE)
485 AND u.group_id = g.group_id
486 AND u.usage IN ('SALES','PRM')
487 AND m.group_member_id = rr.role_resource_id
488 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
489 AND rr.delete_flag <> 'Y'
490 AND SYSDATE BETWEEN rr.start_date_active
491 AND NVL(rr.end_date_active,SYSDATE)
492 AND rr.role_id = r.role_id
493 AND r.role_type_code IN
494 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
495 AND r.active_flag = 'Y'
496 AND res.resource_id = m.resource_id
497 AND res.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
498 AND jtm.group_id = g.group_id
499 GROUP BY m.resource_id, m.person_id, jtm.team_id, res.CATEGORY) J
500
501 WHERE j.team_id = p_WinningTerrMember_tbl.resource_id(l_index);
502 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT
503 ||'FOR TEAM ' ||p_WinningTerrMember_tbl.resource_id(l_index));
504
505
506 IF l_resource_id.COUNT > 0 THEN
507 FOR i IN l_resource_id.FIRST .. l_resource_id.LAST LOOP
508 /* No need to Check to see if it is already part of
509 p_WinningTerrMember_tbl because this will be slow,
510 So we insert into p_WinningTerrMember_tbl directly*/
511 IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
512 p_WinningTerrMember_tbl.resource_id.EXTEND;
513 p_WinningTerrMember_tbl.group_id.EXTEND;
514 p_WinningTerrMember_tbl.person_id.EXTEND;
515 p_WinningTerrMember_tbl.resource_type.EXTEND;
516 p_WinningTerrMember_tbl.full_access_flag.EXTEND;
517 p_WinningTerrMember_tbl.terr_id.EXTEND;
518 p_WinningTerrMember_tbl.trans_object_id.EXTEND;
519 p_WinningTerrMember_tbl.org_id.EXTEND;
520 p_WinningTerrMember_tbl.resource_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_resource_id(i);
521 p_WinningTerrMember_tbl.group_id(p_WinningTerrMember_tbl.resource_id.COUNT) := l_group_id(i);
522 p_WinningTerrMember_tbl.person_id(p_WinningTerrMember_tbl.person_id.COUNT ) := l_person_id(i);
523 p_WinningTerrMember_tbl.resource_type(p_WinningTerrMember_tbl.resource_id.COUNT) := 'RS_EMPLOYEE';
524 p_WinningTerrMember_tbl.full_access_flag(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.full_access_flag(l_index);
525 p_WinningTerrMember_tbl.terr_id(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.terr_id(l_index);
526 p_WinningTerrMember_tbl.trans_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := G_LEAD_ID;
527 p_WinningTerrMember_tbl.org_id(p_WinningTerrMember_tbl.org_id.COUNT ) :=p_WinningTerrMember_tbl.org_id(l_index);
528 END IF;
529 END LOOP;
530 END IF;
531 END IF;
532 END LOOP;
533 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
534 END IF; /* if p_WinningTerrMember_tbl.resource_id.COUNT > 0 */
535 EXCEPTION
536 WHEN others THEN
537 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
538 x_errbuf := SQLERRM;
539 x_retcode := SQLCODE;
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 END EXPLODE_TEAMS_OPPTYS;
542 /************************** End Explode Teams Opptys ******************/
543
544 /************************** Start Explode Groups Opptys ******************/
545 PROCEDURE EXPLODE_GROUPS_OPPTYS(
546 x_errbuf OUT NOCOPY VARCHAR2,
547 x_retcode OUT NOCOPY VARCHAR2,
548 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
549 x_return_status OUT NOCOPY VARCHAR2)
550 IS
551 -------------RS_GROUP---------
552 /*-------------------------------------------------------------------------+
553 | PROGRAM LOGIC
554 |
555 | FOR EACH GROUP listed AS a winner within winners, get THE members who play
556 | a sales ROLE AND are either an employee OR partner AND INSERT back INTO
557 | winners IF they are NOT already IN winners.
558 +-------------------------------------------------------------------------*/
559 l_errbuf VARCHAR2(4000);
560 l_retcode VARCHAR2(255);
561
562 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
563
564 l_resource_id num_list;
565 l_group_id num_list;
566 l_person_id num_list;
567
568 BEGIN
569 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
570 x_return_status := FND_API.G_RET_STS_SUCCESS;
571 -- l_resource_type := 'RS_TEAM';
572 /* Get resources within a resource team */
573 /** Note
574 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
575 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
576 **/
577 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
578 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
579 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
580 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_GROUP' THEN
581 SELECT resource_id, group_id,person_id
582 BULK COLLECT INTO l_resource_id, l_group_id,l_person_id
583 FROM
584 (
585 SELECT min(m.resource_id) resource_id,
586 res.category resource_category,
587 m.group_id group_id, min(res.source_id) person_id
588 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
589 jtf_rs_group_usages u, jtf_rs_role_relations rr,
590 jtf_rs_roles_b r, jtf_rs_resource_extns res
591 WHERE m.group_id = g.group_id
592 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
593 AND NVL(g.end_date_active,SYSDATE)
594 AND u.group_id = g.group_id
595 AND u.usage IN ('SALES','PRM')
596 AND m.group_member_id = rr.role_resource_id
597 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
598 AND rr.role_id = r.role_id
599 AND rr.delete_flag <> 'Y'
600 AND SYSDATE BETWEEN rr.start_date_active
601 AND NVL(rr.end_date_active,SYSDATE)
602 AND r.role_type_code IN
603 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
604 AND r.active_flag = 'Y'
605 AND res.resource_id = m.resource_id
606 AND res.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
607 GROUP BY m.group_member_id, m.resource_id, m.person_id,
608 m.group_id, res.CATEGORY) j
609 WHERE j.group_id = p_WinningTerrMember_tbl.resource_id(l_index);
610 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT
611 ||'FOR GROUP ' ||p_WinningTerrMember_tbl.resource_id(l_index));
612 IF l_resource_id.COUNT > 0 THEN
613 FOR i IN l_resource_id.FIRST .. l_resource_id.LAST LOOP
614 /* No need to Check to see if it is already part of
615 p_WinningTerrMember_tbl because this will be slow,
616 So we insert into p_WinningTerrMember_tbl directly*/
617 IF l_group_id(i) IS NOT NULL THEN --- Resources without groups should NOT be added to the sales team
618 p_WinningTerrMember_tbl.resource_id.EXTEND;
619 p_WinningTerrMember_tbl.group_id.EXTEND;
620 p_WinningTerrMember_tbl.person_id.EXTEND;
621 p_WinningTerrMember_tbl.resource_type.EXTEND;
622 p_WinningTerrMember_tbl.full_access_flag.EXTEND;
623 p_WinningTerrMember_tbl.trans_object_id.EXTEND;
624 p_WinningTerrMember_tbl.terr_id.EXTEND;
625 p_WinningTerrMember_tbl.org_id.EXTEND;
626 p_WinningTerrMember_tbl.resource_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := l_resource_id(i);
627 p_WinningTerrMember_tbl.group_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := l_group_id(i);
628 p_WinningTerrMember_tbl.person_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := l_person_id(i);
629 p_WinningTerrMember_tbl.resource_type(p_WinningTerrMember_tbl.resource_id.COUNT ) := 'RS_EMPLOYEE';
630 p_WinningTerrMember_tbl.full_access_flag(p_WinningTerrMember_tbl.resource_id.COUNT ) := p_WinningTerrMember_tbl.full_access_flag(l_index);
631 p_WinningTerrMember_tbl.trans_object_id(p_WinningTerrMember_tbl.resource_id.COUNT ) := G_LEAD_ID;
632 p_WinningTerrMember_tbl.terr_id(p_WinningTerrMember_tbl.resource_id.COUNT) := p_WinningTerrMember_tbl.terr_id(l_index);
633 p_WinningTerrMember_tbl.org_id(p_WinningTerrMember_tbl.resource_id.COUNT ) :=p_WinningTerrMember_tbl.org_id(l_index);
634 END IF;
635 END LOOP;
636 END IF;
637 END IF;
638 END LOOP;
639 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
640 -- COMMIT;
641 END IF; /* if p_WinningTerrMember_tbl.resource_id.COUNT > 0 */
642 EXCEPTION
643 WHEN OTHERS THEN
644 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS, SQLERRM, TO_CHAR(SQLCODE));
645 x_errbuf := SQLERRM;
646 x_retcode := SQLCODE;
647 x_return_status := FND_API.G_RET_STS_ERROR;
648 END EXPLODE_GROUPS_OPPTYS;
649
650 PROCEDURE SET_TEAM_LEAD_OPPTYS(
651 x_errbuf OUT NOCOPY VARCHAR2,
652 x_retcode OUT NOCOPY VARCHAR2,
653 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
654 x_return_status OUT NOCOPY VARCHAR2) IS
655 BEGIN
656 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_START);
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
659 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
660 AS_GAR.LOG(G_ENTITY || G_LEAD_ID || '::' || 'RESOURCE/GROUP::' || p_WinningTerrMember_tbl.resource_id(l_index) || '/' || p_WinningTerrMember_tbl.group_id(l_index));
661 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' THEN
662 UPDATE AS_ACCESSES_ALL_ALL ACC
663 SET object_version_number = nvl(object_version_number,0) + 1,
664 ACC.last_update_date = SYSDATE,
665 ACC.last_updated_by = FND_GLOBAL.USER_ID,
666 ACC.last_update_login = FND_GLOBAL.USER_ID,
667 ACC.team_leader_flag = NVL(p_WinningTerrMember_tbl.full_access_flag(l_index),'N')
668 WHERE ACC.lead_id = G_LEAD_ID
669 AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
670 AND ACC.sales_group_id = p_WinningTerrMember_tbl.group_id(l_index)
671 AND NVL(ACC.team_leader_flag,'N') <> NVL(p_WinningTerrMember_tbl.full_access_flag(l_index),'N');
672 END IF;
673 END LOOP;
674 END IF;
675 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
676 EXCEPTION
677 WHEN OTHERS THEN
678 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD, SQLERRM, TO_CHAR(SQLCODE));
679 x_errbuf := SQLERRM;
680 x_retcode := SQLCODE;
681 x_return_status := FND_API.G_RET_STS_ERROR;
682 END SET_TEAM_LEAD_OPPTYS;
683
684
685 PROCEDURE INSERT_ACCESSES_OPPTYS(
686 x_errbuf OUT NOCOPY VARCHAR2,
687 x_retcode OUT NOCOPY VARCHAR2,
688 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
689 x_return_status OUT NOCOPY VARCHAR2) IS
690 BEGIN
691 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
692 x_return_status := FND_API.G_RET_STS_SUCCESS;
693 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
694 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
695 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' AND p_WinningTerrMember_tbl.group_id(l_index) IS NOT NULL THEN
696 AS_GAR.LOG(G_ENTITY || G_LEAD_ID || '::' || 'BEFORE INSERT INTO AS_ACCESSED_ALL RESOURCE/GROUP::' || p_WinningTerrMember_tbl.resource_id(l_index)
697 || '/' || p_WinningTerrMember_tbl.group_id(l_index));
698 INSERT INTO AS_ACCESSES_ALL
699 (access_id ,
700 last_update_date ,
701 last_updated_by,
702 creation_date ,
703 created_by ,
704 last_update_login,
705 access_type ,
706 freeze_flag,
707 reassign_flag,
708 team_leader_flag ,
709 customer_id ,
710 address_id ,
711 salesforce_id ,
712 person_id ,
713 sales_group_id,
714 lead_id,
715 created_by_tap_flag,
716 owner_flag,
717 open_flag,org_id)
718 SELECT AS_ACCESSES_S.NEXTVAL,
719 SYSDATE,
720 FND_GLOBAL.USER_ID,
721 SYSDATE,
722 FND_GLOBAL.USER_ID,
723 FND_GLOBAL.USER_ID,
724 'Online',
725 'N',
726 'N',
727 DECODE(p_WinningTerrMember_tbl.full_access_flag(l_index),'Y','Y','N'),
728 LDS.customer_id,
729 LDS.address_Id,
730 p_WinningTerrMember_tbl.resource_id(l_index),
731 p_WinningTerrMember_tbl.person_id(l_index),
732 p_WinningTerrMember_tbl.group_id(l_index),
733 LDS.lead_id,
734 'Y',
735 'N',
736 NVL(ST.opp_open_status_flag,'N'),
737 p_WinningTerrMember_tbl.org_id(l_index)
738 FROM AS_LEADS_ALL LDS, AS_STATUSES_B ST
739 WHERE LDS.status = ST.status_code
740 AND LDS.lead_id = G_LEAD_ID
741 AND NOT EXISTS
742 ( SELECT NULL FROM AS_ACCESSES_ALL ACC
743 WHERE ACC.lead_id = LDS.lead_id
744 AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
745 AND ACC.sales_group_id = p_WinningTerrMember_tbl.group_id(l_index) );
746 END IF;
747 END LOOP;
748 END IF;
749 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_END);
750 EXCEPTION
751 WHEN OTHERS THEN
752 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC, SQLERRM, TO_CHAR(SQLCODE));
753 x_errbuf := SQLERRM;
754 x_retcode := SQLCODE;
755 x_return_status := FND_API.G_RET_STS_ERROR;
756 END INSERT_ACCESSES_OPPTYS;
757
758 PROCEDURE INSERT_TERR_ACCESSES_OPPTYS(
759 x_errbuf OUT NOCOPY VARCHAR2,
760 x_retcode OUT NOCOPY VARCHAR2,
761 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
762 x_return_status OUT NOCOPY VARCHAR2) IS
763 BEGIN
764 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
765 /*------------------------------------------------------------------------------+
766 | we are deleting all rows for the entity from as_territory_accesses prior to
767 | inserting into it because the logic for removing only certain terr_id/access_id
768 | combinations is very complex and could be slow..
769 +-------------------------------------------------------------------------------*/
770 x_return_status := FND_API.G_RET_STS_SUCCESS;
771 DELETE FROM AS_TERRITORY_ACCESSES TACC
772 WHERE TACC.access_id IN
773 (SELECT ACC.access_id
774 FROM AS_ACCESSES_ALL ACC
775 WHERE lead_id = G_LEAD_ID);
776 IF p_WinningTerrMember_tbl.resource_id.COUNT > 0 THEN
777 FOR l_index IN p_WinningTerrMember_tbl.resource_id.FIRST..p_WinningTerrMember_tbl.resource_id.LAST LOOP
778 IF p_WinningTerrMember_tbl.resource_type(l_index) = 'RS_EMPLOYEE' THEN
779 INSERT INTO AS_TERRITORY_ACCESSES
780 ( access_id,
781 territory_id,
782 user_territory_id,
783 last_update_date,
784 last_updated_by,
785 creation_date,
786 created_by,
787 last_update_login )
788 SELECT
789 ACC.access_id,
790 p_WinningTerrMember_tbl.terr_id(l_index),
791 p_WinningTerrMember_tbl.terr_id(l_index),
792 SYSDATE,
793 FND_GLOBAL.USER_ID,
794 SYSDATE,
795 FND_GLOBAL.USER_ID,
796 FND_GLOBAL.USER_ID
797 FROM AS_ACCESSES_ALL ACC
798 WHERE ACC.lead_id = G_LEAD_ID
799 AND ACC.salesforce_id = p_WinningTerrMember_tbl.resource_id(l_index)
800 AND ACC.sales_group_id = p_WinningTerrMember_tbl.group_id(l_index)
801 AND NOT EXISTS ( SELECT 'Y'
802 FROM AS_TERRITORY_ACCESSES
803 WHERE ACCESS_ID = ACC.access_id
804 AND TERRITORY_ID = p_WinningTerrMember_tbl.terr_id(l_index)) ;
805 END IF;
806 END LOOP;
807 END IF;
808 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
809 EXCEPTION
810 WHEN OTHERS THEN
811 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
812 x_errbuf := SQLERRM;
813 x_retcode := SQLCODE;
814 x_return_status := FND_API.G_RET_STS_ERROR;
815 END INSERT_TERR_ACCESSES_OPPTYS;
816
817 PROCEDURE PERFORM_OPPTY_CLEANUP(
818 x_errbuf OUT NOCOPY VARCHAR2,
819 x_retcode OUT NOCOPY VARCHAR2,
820 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
821 x_return_status OUT NOCOPY VARCHAR2) IS
822
823 BEGIN
824 x_return_status := FND_API.G_RET_STS_SUCCESS;
825 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_START);
826 DELETE FROM AS_ACCESSES_ALL ACC
827 WHERE lead_id = G_LEAD_ID
828 AND NVL(freeze_flag, 'N') <> 'Y'
829 AND SALESFORCE_ID||SALES_GROUP_ID NOT IN (
830 SELECT RESTAB.RES||GRPTAB.GRP FROM
831 (SELECT rownum ROW_NUM,A.COLUMN_VALUE RES FROM TABLE(CAST(p_WinningTerrMember_tbl.resource_id AS jtf_terr_number_list)) a) RESTAB,
832 (SELECT rownum ROW_NUM,b.COLUMN_VALUE GRP FROM TABLE(CAST(p_WinningTerrMember_tbl.group_id AS jtf_terr_number_list)) b) GRPTAB
833 WHERE RESTAB.ROW_NUM = GRPTAB.ROW_NUM
834 )
835 AND NOT EXISTS (SELECT 'X'
836 FROM AS_SALES_CREDITS
837 WHERE salesforce_id = ACC.salesforce_id
838 AND salesgroup_id = ACC.sales_group_id
839 AND lead_id = G_LEAD_ID) ;
840
841 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
842 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
843 EXCEPTION
844 WHEN OTHERS THEN
845 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC, SQLERRM, TO_CHAR(SQLCODE));
846 x_errbuf := SQLERRM;
847 x_retcode := SQLCODE;
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 END PERFORM_OPPTY_CLEANUP;
850
851 ----------------------
852 PROCEDURE ASSIGN_OPPTY_OWNER(
853 x_errbuf OUT NOCOPY VARCHAR2,
854 x_retcode OUT NOCOPY VARCHAR2,
855 p_WinningTerrMember_tbl IN OUT NOCOPY JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type,
856 x_return_status OUT NOCOPY VARCHAR2) IS
857
858 CURSOR oppty_owner_rt IS
859 SELECT MAX(AAA.access_id) access_id -- /*+ index(aaa as_accesses_n3) */
860 FROM AS_ACCESSES_ALL AAA
861 WHERE AAA.lead_id = G_LEAD_ID
862 AND NVL(AAA.CREATED_BY_TAP_FLAG,'N') = 'Y' ;
863
864 CURSOR is_owner_set IS
865 SELECT 'X'
866 FROM AS_ACCESSES_ALL
867 WHERE lead_id = G_LEAD_ID
868 AND owner_flag = 'Y';
869
870
871 v_own_set VARCHAR2(1);
872 v_acc_id NUMBER;
873 v_srep_id NUMBER;
874 v_grp_id NUMBER;
875
876 BEGIN
877 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START) ;
878 /* ----------------------------------------------------------------------+
879 | Select MAX(access_id) from as_accesses for this lead where the created_by_tap
880 | flag is set and owner flag is not set..Is there anything else that we need to do ?
881 | Then update accesses,leads,and scd
882 +------------------------------------------------------------------------*/
883 x_return_status := FND_API.G_RET_STS_SUCCESS;
884 OPEN is_owner_set;
885 FETCH is_owner_set INTO v_own_set;
886 IF is_owner_set%NOTFOUND THEN
887 OPEN oppty_owner_rt;
888 FETCH oppty_owner_rt INTO v_acc_id;
889 IF oppty_owner_rt%NOTFOUND THEN
890 UPDATE AS_LEADS_ALL sl
891 SET SL.object_version_number = nvl(sl.object_version_number,0) + 1,
892 SL.last_update_date = SYSDATE,
893 SL.last_updated_by = FND_GLOBAL.USER_ID,
894 SL.last_update_login = FND_GLOBAL.USER_ID,
895 SL.owner_salesforce_id = NULL,
896 SL.owner_sales_group_id = NULL
897 WHERE SL.lead_id = G_LEAD_ID ;
898 UPDATE AS_SALES_CREDITS_DENORM SCD
899 SET SCD.object_version_number = nvl(scd.object_version_number,0) + 1,
900 SCD.last_update_date = SYSDATE,
901 SCD.last_updated_by = FND_GLOBAL.USER_ID,
902 SCD.last_update_login = FND_GLOBAL.USER_ID,
903 SCD.owner_salesforce_id = NULL,
904 SCD.owner_sales_group_id = NULL
905 WHERE SCD.lead_id = G_LEAD_ID ;
906 ELSE
907 UPDATE AS_ACCESSES_ALL AAA
908 SET AAA.owner_flag = 'Y',
909 AAA.object_version_number = nvl(AAA.object_version_number,0) + 1,
910 AAA.last_update_date = SYSDATE,
911 AAA.last_updated_by = FND_GLOBAL.USER_ID,
912 AAA.last_update_login = FND_GLOBAL.USER_ID
913 WHERE access_id = v_acc_id
914 RETURNING salesforce_id,sales_group_id INTO v_srep_id,v_grp_id;
915 UPDATE AS_LEADS_ALL sl
916 SET SL.object_version_number = nvl(sl.object_version_number,0) + 1,
917 SL.last_update_date = SYSDATE,
918 SL.last_updated_by = FND_GLOBAL.USER_ID,
919 SL.last_update_login = FND_GLOBAL.USER_ID,
920 SL.owner_salesforce_id = v_srep_id,
921 SL.owner_sales_group_id = v_grp_id
922 WHERE SL.lead_id = G_LEAD_ID ;
923 UPDATE AS_SALES_CREDITS_DENORM SCD
924 SET SCD.object_version_number = nvl(scd.object_version_number,0) + 1,
925 SCD.last_update_date = SYSDATE,
926 SCD.last_updated_by = FND_GLOBAL.USER_ID,
927 SCD.last_update_login = FND_GLOBAL.USER_ID,
928 SCD.owner_salesforce_id = v_srep_id,
929 SCD.owner_sales_group_id = v_grp_id
930 WHERE SCD.lead_id = G_LEAD_ID ;
931 END IF;
932 END IF;
933 CLOSE is_owner_set;
934 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_END);
935 EXCEPTION
936 WHEN OTHERS THEN
937 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO, SQLERRM, TO_CHAR(SQLCODE));
938 x_errbuf := SQLERRM;
939 x_retcode := SQLCODE;
940 x_return_status := FND_API.G_RET_STS_ERROR;
941 END ASSIGN_OPPTY_OWNER;
942
943 PROCEDURE RAISE_BUSINESS_EVENT(
944 x_errbuf OUT NOCOPY VARCHAR2,
945 x_retcode OUT NOCOPY VARCHAR2,
946 X_return_status OUT NOCOPY VARCHAR2) IS
947 l_list WF_PARAMETER_LIST_T;
948 l_param WF_PARAMETER_T;
949 x_event_key varchar2(1000);
950 l_event_name VARCHAR2(240) := 'oracle.apps.as.opportunity.tap.realtime.post';
951 BEGIN
952 X_return_status := FND_API.G_RET_STS_SUCCESS;
953 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'Raising BE' || AS_GAR.G_START) ;
954
955 -- initialization of object variables
956 l_list := WF_PARAMETER_LIST_T();
957
958 -- Add Context values to the list
959 l_param := WF_PARAMETER_T( NULL, NULL );
960
961 -- fill the parameters list
962 l_list.extend;
963 l_param.SetName( 'LEAD_ID' );
964 l_param.SetValue( G_LEAD_ID );
965 l_list(l_list.last) := l_param;
966
967 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'Before Calling BE Procedure ' || AS_GAR.G_START);
968
969 SELECT l_event_name || AS_BUSINESS_EVENT_S.nextval
970 INTO x_event_key
971 FROM DUAL;
972
973 AS_BUSINESS_EVENT_PVT.raise_event(
974 p_event_name => l_event_name,
975 p_event_key => x_event_key,
976 p_parameters => l_list );
977 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || 'After Calling BE Procedure ' || AS_GAR.G_END) ;
978 EXCEPTION
979 WHEN OTHERS THEN
980 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || 'RAISING BE' , SQLERRM, TO_CHAR(SQLCODE));
981 x_errbuf := SQLERRM;
982 x_retcode := SQLCODE;
983 x_return_status := FND_API.G_RET_STS_ERROR;
984
985 END RAISE_BUSINESS_EVENT;
986 END AS_RTTAP_OPPTY;