[Home] [Help]
PACKAGE BODY: APPS.AS_GAR_LEADS_PUB
Source
1 PACKAGE BODY AS_GAR_LEADS_PUB AS
2 /* $Header: asxgrldb.pls 120.8 2006/02/02 21:24 amagupta noship $ */
3
4 ---------------------------------------------------------------------------
5 -- Start of Comments
6 ---------------------------------------------------------------------------
7 -- PACKAGE NAME: AS_GAR_LEADS_PUB
8 -- ---------------------------------------------------------------------
9 -- PURPOSE
10 -- --------
11 -- This package contains procedures to accomplish each of the following
12 -- tasks:
13 -- 1: Call the JTY API to process data from JTY trans tables and
14 -- populate JTY winners.
15 -- 2: Merge and insert records from winners into AS_ACCESSES_ALL_ALL
16 -- 3: Soft Delete unwanted records from AS_ACCESSES_ALL_ALL
17 -- 4: Lead Owner Assignment
18 --
19 ---------------------------------------------------------------------------
20 /*-------------------------------------------------------------------------+
21 | PRIVATE CONSTANTS
22 +-------------------------------------------------------------------------*/
23 G_BUSINESS_EVENT CONSTANT VARCHAR2(60) := 'oracle.apps.as.tap.batch_mode';
24 DEADLOCK_DETECTED EXCEPTION;
25 PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
26 G_ENTITY CONSTANT VARCHAR2(12) := 'GAR::LEADS::';
27 /*-------------------------------------------------------------------------*
28 | PRIVATE VARIABLES
29 *-------------------------------------------------------------------------*/
30 /*-------------------------------------------------------------------------*
31 | PRIVATE ROUTINES SPECIFICATION
32 *-------------------------------------------------------------------------*/
33 /*------------------------------------------------------------------------*
34 | PUBLIC ROUTINES
35 *------------------------------------------------------------------------*/
36
37 /************************** Start GAR Wrapper *****************************/
38 PROCEDURE GAR_WRAPPER(
39 errbuf OUT NOCOPY VARCHAR2,
40 retcode OUT NOCOPY VARCHAR2,
41 p_run_mode IN VARCHAR2,
42 p_debug_mode IN VARCHAR2,
43 p_trace_mode IN VARCHAR2,
44 p_worker_id IN VARCHAR2,
45 P_percent_analyzed IN NUMBER )
46 IS
47 l_terr_globals AS_GAR.TERR_GLOBALS;
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(2000);
50 l_errbuf VARCHAR2(4000);
51 l_retcode VARCHAR2(255);
52 l_sub_exist VARCHAR2(1);
53 l_return_status VARCHAR2(1);
54 l_target_type VARCHAR2(15);
55 l_status BOOLEAN;
56 l_proc VARCHAR2(30):= 'GAR_WRAPPER::';
57 BEGIN
58 AS_GAR.g_debug_flag := p_debug_mode;
59 IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
60 AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
61
62 IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
63 l_target_type := 'TOTAL';
64 ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
65 l_target_type := 'INCREMENTAL';
66 END If;
67
68 -- Set the Global variables
69 AS_GAR.INIT(
70 p_run_mode,
71 p_worker_id,
72 l_terr_globals);
73
74 /* This inserts into Lead winners */
75 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
76 JTY_ASSIGN_BULK_PUB.GET_WINNERS
77 ( p_api_version_number => 1.0,
78 p_init_msg_list => FND_API.G_TRUE,
79 p_source_id => -1001,
80 p_trans_id => -1003,
81 P_PROGRAM_NAME => 'SALES/LEAD PROGRAM',
82 P_mode => l_target_type,
83 P_percent_analyzed => NVL(P_percent_analyzed,20),
84 p_worker_id => p_worker_id,
85 x_return_status => l_return_status,
86 x_msg_count => l_msg_count,
87 x_msg_data => l_msg_data,
88 ERRBUF => l_errbuf,
89 RETCODE => l_retcode);
90
91
92 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
93 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
94
95 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
96 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 End If;
99
100 COMMIT;
101
102 -- Explode GROUPS if any inside winners
103 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
104 AS_GAR_LEADS_PUB.EXPLODE_GROUPS_LEADS(
105 x_errbuf => l_errbuf,
106 x_retcode => l_retcode,
107 p_terr_globals => l_terr_globals,
108 x_return_status => l_return_status);
109
110 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
111 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
112
113 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
114 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 End If;
117
118 COMMIT;
119
120 -- Explode TEAMS if any inside winners
121 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
122 AS_GAR_LEADS_PUB.EXPLODE_TEAMS_LEADS(
123 x_errbuf => l_errbuf,
124 x_retcode => l_retcode,
125 p_terr_globals => l_terr_globals,
126 x_return_status => l_return_status);
127
128 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
129 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
130
131 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
132 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 End If;
135
136 COMMIT;
137
138 -- Set team leader for Leads
139 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
140 AS_GAR_LEADS_PUB.SET_TEAM_LEAD_LEADS(
141 x_errbuf => l_errbuf,
142 x_retcode => l_retcode,
143 p_terr_globals => l_terr_globals,
144 x_return_status => l_return_status);
145
146 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
147 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
148
149 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
150 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
152 End If;
153
154 -- Insert into Lead Accesses from Winners
155 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
156 AS_GAR_LEADS_PUB.INSERT_ACCESSES_LEADS(
157 x_errbuf => l_errbuf,
158 x_retcode => l_retcode,
159 p_terr_globals => l_terr_globals,
160 x_return_status => l_return_status);
161
162 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
163 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
164
165 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
166 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 End If;
169
170 -- Insert into territory Accesses
171 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
172 AS_GAR_LEADS_PUB.INSERT_TERR_ACCESSES_LEADS(
173 x_errbuf => l_errbuf,
174 x_retcode => l_retcode,
175 p_terr_globals => l_terr_globals,
176 x_return_status => l_return_status);
177
178 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
179 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
180
181 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
182 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 End If;
185
186 -- Remove (soft delete) records in access table that are not qualified
187 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
188 AS_GAR_LEADS_PUB.PERFORM_LEAD_CLEANUP(
189 x_errbuf => l_errbuf,
190 x_retcode => l_retcode,
191 p_terr_globals => l_terr_globals,
192 x_return_status => l_return_status);
193
194 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
195 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
196
197 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
198 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
200 End If;
201
202 -- LEAD Owner assignment
203 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_START);
204 AS_GAR_LEADS_PUB.ASSIGN_LEAD_OWNER(
205 x_errbuf => l_errbuf,
206 x_retcode => l_retcode,
207 p_terr_globals => l_terr_globals,
208 x_return_status => l_return_status);
209
210 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_END);
211 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO || AS_GAR.G_RETURN_STATUS || l_return_status);
212
213 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
214 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CO, l_errbuf, l_retcode);
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 End If;
217
218 -- BES enhancement
219
220 l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
221 IF l_sub_exist = 'Y' THEN
222 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
223 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
224 AS_GAR.Raise_BE(l_terr_globals);
225 END If;
226
227 AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
228 EXCEPTION
229 WHEN OTHERS THEN
230 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
231 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
232 END GAR_WRAPPER;
233
234 /************************** End GAR Wrapper *****************************/
235
236 /************************** Start Explode Teams Leads ******************/
237 PROCEDURE EXPLODE_TEAMS_LEADS(
238 x_errbuf OUT NOCOPY VARCHAR2,
239 x_retcode OUT NOCOPY VARCHAR2,
240 p_terr_globals IN AS_GAR.TERR_GLOBALS,
241 x_return_status OUT NOCOPY VARCHAR2)
242 IS
243
244 /*-------------------------------------------------------------------------+
245 | LOGIC
246 |
247 | A RESOURCE team can be comprised OF resources who belong TO one OR more
248 | GROUPS OF resources.
249 | So get a LIST OF team members (OF TYPE employee OR partner OR parter contact
250 | AND play a ROLE OF salesrep ) AND get atleast one GROUP id that they belong TO
251 | WHERE they play a similar ROLE.
252 | UNION THE above WITH a LIST OF ALL members OF ALL GROUPS which BY themselves
253 | are a RESOURCE within a team.
254 | INSERT these members INTO winners IF they are NOT already IN winners.
255 +-------------------------------------------------------------------------*/
256
257 l_errbuf VARCHAR2(4000);
258 l_retcode VARCHAR2(255);
259 l_res_type_count NUMBER;
260 l_res_lead_count NUMBER;
261 l_resource_type VARCHAR2(10);
262 l_request_id NUMBER;
263 l_worker_id NUMBER;
264
265 CURSOR c_get_res_type_count(c_resource_type VARCHAR2, c_request_id NUMBER, c_worker_id NUMBER)
266 IS
267 SELECT count(*)
268 FROM JTF_TAE_1001_LEAD_WINNERS
269 WHERE request_id = c_request_id
270 AND resource_type = c_resource_type
271 AND worker_id = c_worker_id
272 AND ROWNUM < 2;
273
274
275 CURSOR count_res_lead
276 IS
277 SELECT count(*)
278 FROM JTF_TERR_RSC_ALL rsc,
279 JTF_TERR_DENORM_RULES_ALL rules,
280 JTF_TERR_RSC_ACCESS_ALL acc
281 WHERE rules.terr_id = rsc.terr_id
282 AND rsc.resource_type = 'RS_TEAM'
283 AND acc.access_type = 'LEAD'
284 AND rules.source_id = -1001
285 AND rsc.terr_rsc_id = acc.terr_rsc_id;
286
287 BEGIN
288 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
289 x_return_status := FND_API.G_RET_STS_SUCCESS;
290 l_request_id := p_terr_globals.request_id;
291 l_worker_id := p_terr_globals.worker_id;
292 l_resource_type := 'RS_TEAM';
293
294
295 OPEN count_res_lead;
296 FETCH count_res_lead INTO l_res_lead_count;
297 CLOSE count_res_lead;
298
299 IF l_res_lead_count > 0 THEN
300 OPEN c_get_res_type_count(l_resource_type, l_request_id, l_worker_id);
301 FETCH c_get_res_type_count INTO l_res_type_count;
302 CLOSE c_get_res_type_count;
303 END IF;
304
305 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
306 IF l_res_type_count > 0 THEN
307 /* Get resources within a resource team */
308 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
309 /** Note
310 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
311 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
312 **/
313 INSERT INTO JTF_TAE_1001_LEAD_WINNERS
314 (trans_object_id,
315 trans_detail_object_id,
316 terr_id,
317 resource_id,
318 resource_type,
319 group_id,
320 full_access_flag,
321 request_id,
322 program_application_id,
323 program_id,
324 program_update_date,
325 source_id,
326 trans_object_type_id,
327 last_update_date,
328 last_updated_by,
329 creation_date,
330 created_by,
331 last_update_login,
332 absolute_rank,
333 top_level_terr_id,
334 num_winners,
335 terr_rsc_id,
336 role,
337 primary_contact_flag,
338 person_id,
339 org_id,
340 worker_id)
341 SELECT T.trans_object_id,
342 T.trans_detail_object_id,
343 T.terr_id,
344 J.resource_id,
345 DECODE(J.resource_category,'PARTY','RS_PARTY',
346 'PARTNER','RS_PARTNER',
347 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
348 J.group_id,
349 T.full_access_flag,
350 T.request_id,
351 T.program_application_id,
352 T.program_id, T.program_update_date,
353 T.source_id,
354 T.trans_object_type_id,
355 SYSDATE,
356 T.last_updated_by,
357 SYSDATE,
358 T.created_by,
359 T.last_update_login,
360 T.absolute_rank,
361 T.top_level_terr_id,
362 T.num_winners,
363 T.terr_rsc_id,
364 T.role,
365 T.primary_contact_flag,
366 J.person_id,
367 T.org_id,
368 T.worker_id
369 FROM
370 JTF_TAE_1001_LEAD_WINNERS T,
371 (
372 SELECT TM.team_resource_id resource_id,
373 TM.person_id person_id2,
374 MIN(G.group_id)group_id,
375 MIN(T.team_id) team_id,
376 TRES.category resource_category,
377 MIN(TRES.source_id) person_id
378 FROM jtf_rs_team_members TM, jtf_rs_teams_b T,
379 jtf_rs_team_usages TU, jtf_rs_role_relations TRR,
380 jtf_rs_roles_b TR, jtf_rs_resource_extns TRES,
381 (
382 SELECT m.group_id group_id, m.resource_id resource_id
383 FROM jtf_rs_group_members m,
384 jtf_rs_groups_b g,
385 jtf_rs_group_usages u,
386 jtf_rs_role_relations rr,
387 jtf_rs_roles_b r,
388 jtf_rs_resource_extns res
389 WHERE m.group_id = g.group_id
390 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
391 AND NVL(g.end_date_active,SYSDATE)
392 AND u.group_id = g.group_id
393 AND u.usage IN ('SALES','PRM')
394 AND m.group_member_id = rr.role_resource_id
395 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
396 AND rr.delete_flag <> 'Y'
397 AND SYSDATE BETWEEN rr.start_date_active
398 AND NVL(rr.end_date_active,SYSDATE)
399 AND rr.role_id = r.role_id
400 AND r.role_type_code
401 IN ('SALES', 'TELESALES', 'FIELDSALES','PRM')
402 AND r.active_flag = 'Y'
403 AND res.resource_id = m.resource_id
404 AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
405 ) G
406 WHERE tm.team_id = t.team_id
407 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
408 AND NVL(t.end_date_active,SYSDATE)
409 AND tu.team_id = t.team_id
410 AND tu.usage IN ('SALES','PRM')
411 AND tm.team_member_id = trr.role_resource_id
412 AND tm.delete_flag <> 'Y'
413 AND tm.resource_type = 'INDIVIDUAL'
414 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
415 AND trr.delete_flag <> 'Y'
416 AND SYSDATE BETWEEN trr.start_date_active
417 AND NVL(trr.end_date_active,SYSDATE)
418 AND trr.role_id = tr.role_id
419 AND tr.role_type_code IN
420 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
421 AND tr.active_flag = 'Y'
422 AND tres.resource_id = tm.team_resource_id
423 AND tres.category IN ('EMPLOYEE','PARTY','PARTNER')
424 AND tm.team_resource_id = g.resource_id
425 GROUP BY tm.team_resource_id,
426 tm.person_id,
427 tres.category,
428 tres.source_id
429 UNION ALL
430 SELECT MIN(m.resource_id) resource_id,
431 MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
432 MIN(jtm.team_id) team_id, res.CATEGORY resource_category,
433 MIN(res.source_id) person_id
434 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
435 jtf_rs_group_usages u, jtf_rs_role_relations rr,
436 jtf_rs_roles_b r, jtf_rs_resource_extns res,
437 (
438 SELECT tm.team_resource_id group_id,
439 t.team_id team_id
440 FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
441 jtf_rs_team_usages tu,jtf_rs_role_relations trr,
442 jtf_rs_roles_b tr, jtf_rs_resource_extns tres
443 WHERE tm.team_id = t.team_id
444 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
445 AND NVL(t.end_date_active,SYSDATE)
446 AND tu.team_id = t.team_id
447 AND tu.usage IN ('SALES','PRM')
448 AND tm.team_member_id = trr.role_resource_id
449 AND tm.delete_flag <> 'Y'
450 AND tm.resource_type = 'GROUP'
451 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
452 AND trr.delete_flag <> 'Y'
453 AND SYSDATE BETWEEN trr.start_date_active
454 AND NVL(trr.end_date_active,SYSDATE)
455 AND trr.role_id = tr.role_id
456 AND tr.role_type_code IN
457 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
458 AND tr.active_flag = 'Y'
459 AND tres.resource_id = tm.team_resource_id
460 AND tres.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
461 ) jtm
462 WHERE m.group_id = g.group_id
463 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
464 AND NVL(g.end_date_active,SYSDATE)
465 AND u.group_id = g.group_id
466 AND u.usage IN ('SALES','PRM')
467 AND m.group_member_id = rr.role_resource_id
468 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
469 AND rr.delete_flag <> 'Y'
470 AND SYSDATE BETWEEN rr.start_date_active
471 AND NVL(rr.end_date_active,SYSDATE)
472 AND rr.role_id = r.role_id
473 AND r.role_type_code IN
474 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
475 AND r.active_flag = 'Y'
476 AND res.resource_id = m.resource_id
477 AND res.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
478 AND jtm.group_id = g.group_id
479 GROUP BY m.resource_id, m.person_id, jtm.team_id, res.CATEGORY) J
480 WHERE j.team_id = t.resource_id
481 AND t.request_id = l_request_id
482 AND t.worker_id = l_worker_id
483 AND t.resource_type = 'RS_TEAM'
484 AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_LEAD_WINNERS rt1
485 WHERE rt1.resource_id = j.resource_id
486 AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
487 AND rt1.request_id = t.request_id
488 AND rt1.worker_id = t.worker_id
489 AND rt1.trans_object_id = t.trans_object_id
490 AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
491
492 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
493 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
494
495
496 COMMIT;
497
498 END IF; /* if l_res_type_count > 0 */
499 EXCEPTION
500 WHEN others THEN
501 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
502 x_errbuf := SQLERRM;
503 x_retcode := SQLCODE;
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 RAISE;
506 END EXPLODE_TEAMS_LEADS;
507 /************************** End Explode Teams Leads ******************/
508
509 /************************** Start Explode Groups Leads ******************/
510 PROCEDURE EXPLODE_GROUPS_LEADS(
511 x_errbuf OUT NOCOPY VARCHAR2,
512 x_retcode OUT NOCOPY VARCHAR2,
513 p_terr_globals IN AS_GAR.TERR_GLOBALS,
514 x_return_status OUT NOCOPY VARCHAR2)
515 IS
516 -------------RS_GROUP---------
517 /*-------------------------------------------------------------------------+
518 | PROGRAM LOGIC
519 |
520 | FOR EACH GROUP listed AS a winner within winners, get THE members who play
521 | a sales ROLE AND are either an employee OR partner AND INSERT back INTO
522 | winners IF they are NOT already IN winners.
523 +-------------------------------------------------------------------------*/
524 l_errbuf VARCHAR2(4000);
525 l_retcode VARCHAR2(255);
526 l_res_type_count NUMBER;
527 l_res_lead_count NUMBER;
528 l_resource_type VARCHAR2(10);
529 l_request_id NUMBER;
530 l_worker_id NUMBER;
531
532 CURSOR c_get_res_type_count(c_resource_type VARCHAR2, c_request_id NUMBER, c_worker_id NUMBER)
533 IS
534 SELECT count(*)
535 FROM JTF_TAE_1001_LEAD_WINNERS
536 WHERE request_id = c_request_id
537 AND resource_type = c_resource_type
538 AND worker_id = c_worker_id
539 AND ROWNUM < 2;
540
541 CURSOR count_res_lead
542 IS
543 SELECT count(*)
544 FROM JTF_TERR_RSC_ALL rsc,
545 JTF_TERR_DENORM_RULES_ALL rules,
546 JTF_TERR_RSC_ACCESS_ALL acc
547 WHERE rules.terr_id = rsc.terr_id
548 AND rsc.resource_type = 'RS_GROUP'
549 AND acc.access_type = 'LEAD'
550 AND rules.source_id = -1001
551 AND rsc.terr_rsc_id = acc.terr_rsc_id ;
552
553 BEGIN
554 l_resource_type := 'RS_GROUP';
555 x_return_status := FND_API.G_RET_STS_SUCCESS;
556 l_request_id := p_terr_globals.request_id;
557 l_worker_id := p_terr_globals.worker_id;
558
559 OPEN count_res_lead;
560 FETCH count_res_lead INTO l_res_lead_count;
561 CLOSE count_res_lead;
562
563 IF l_res_lead_count > 0 THEN
564 OPEN c_get_res_type_count(l_resource_type, l_request_id, l_worker_id);
565 FETCH c_get_res_type_count INTO l_res_type_count;
566 CLOSE c_get_res_type_count;
567 END IF;
568
569 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
570 IF l_res_type_count > 0 THEN
571 /* Get resources within a resource group */
572 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
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
578 INSERT INTO JTF_TAE_1001_LEAD_WINNERS
579 (trans_object_id,
580 trans_detail_object_id,
581 terr_id,
582 resource_id,
583 resource_type,
584 group_id,
585 full_access_flag,
586 request_id,
587 program_application_id,
588 program_id,
589 program_update_date,
590 source_id,
591 trans_object_type_id,
592 last_update_date,
593 last_updated_by,
594 creation_date,
595 created_by,
596 last_update_login,
597 absolute_rank,
598 top_level_terr_id,
599 num_winners,
600 terr_rsc_id,
601 role,
602 primary_contact_flag,
603 person_id,
604 org_id,
605 worker_id)
606 SELECT T.trans_object_id,
607 T.trans_detail_object_id,
608 T.terr_id,
609 J.resource_id,
610 DECODE(J.resource_category,'PARTY','RS_PARTY',
611 'PARTNER','RS_PARTNER',
612 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
613 J.group_id,
614 T.full_access_flag,
615 T.request_id,
616 T.program_application_id,
617 T.program_id,
618 T.program_update_date,
619 T.source_id,
620 T.trans_object_type_id,
621 SYSDATE,
622 T.last_updated_by,
623 SYSDATE,
624 T.created_by,
625 T.last_update_login,
626 T.absolute_rank,
627 T.top_level_terr_id,
628 T.num_winners,
629 T.terr_rsc_id,
630 T.role,
631 T.primary_contact_flag,
632 J.person_id,
633 T.org_id,
634 T.worker_id
635 FROM
636 JTF_TAE_1001_LEAD_WINNERS t,
637 (
638 SELECT MIN(m.resource_id) resource_id,
639 res.category resource_category,
640 m.group_id group_id, MIN(res.source_id) person_id
641 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
642 jtf_rs_group_usages u, jtf_rs_role_relations rr,
643 jtf_rs_roles_b r, jtf_rs_resource_extns res
644 WHERE m.group_id = g.group_id
645 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
646 AND NVL(g.end_date_active,SYSDATE)
647 AND u.group_id = g.group_id
648 AND u.usage IN ('SALES','PRM')
649 AND m.group_member_id = rr.role_resource_id
650 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
651 AND rr.role_id = r.role_id
652 AND rr.delete_flag <> 'Y'
653 AND SYSDATE BETWEEN rr.start_date_active
654 AND NVL(rr.end_date_active,SYSDATE)
655 AND r.role_type_code IN
656 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
657 AND r.active_flag = 'Y'
658 AND res.resource_id = m.resource_id
659 AND res.category IN ('EMPLOYEE')--,'PARTY','PARTNER')
660 GROUP BY m.group_member_id, m.resource_id, m.person_id,
661 m.group_id, res.CATEGORY) j
662 WHERE j.group_id = t.resource_id
663 AND t.request_id = l_request_id
664 AND t.worker_id = l_worker_id
665 AND t.resource_type = 'RS_GROUP'
666 AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_LEAD_WINNERS rt1
667 WHERE rt1.resource_id = j.resource_id
668 AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
669 AND rt1.request_id = t.request_id
670 AND rt1.worker_id = t.worker_id
671 AND rt1.trans_object_id = t.trans_object_id
672 AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
673
674 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);
675 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
676
677 COMMIT;
678 END IF; /* if l_res_type_count > 0 */
679
680 EXCEPTION
681 WHEN OTHERS THEN
682 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS, SQLERRM, TO_CHAR(SQLCODE));
683 x_errbuf := SQLERRM;
684 x_retcode := SQLCODE;
685 x_return_status := FND_API.G_RET_STS_ERROR;
686 RAISE;
687 END EXPLODE_GROUPS_LEADS;
688 /************************** End Explode Groups Leads ******************/
689
690 /************************** Start Set Leads Team Leader *****************/
691
692 /*-------------------------------------------------------------------------+
693 | PROGRAM LOGIC
694 |
695 | Winners table records are striped by worker id.
696 | All the logic pertains to what happens within a single worker.
697 | Get a list of resources who are marked as full access in winners but are
698 | not marked as full access in accesses (CURSOR team_leader).
699 | Loop within the worker for sets of records within winners ---?
700 | Bulk collect from team_leader cursor into array.
701 | Break up the array into batches based on global var bulk_size.
702 | For each batch:
703 | Try 3 times to bulk update acesses
704 | if all 3 attempts fail because of deadlock:
705 | Update on record at a time.
706 +-------------------------------------------------------------------------*/
707
708 PROCEDURE SET_TEAM_LEAD_LEADS(
709 x_errbuf OUT NOCOPY VARCHAR2,
710 x_retcode OUT NOCOPY VARCHAR2,
711 p_terr_globals IN AS_GAR.TERR_GLOBALS,
712 x_return_status OUT NOCOPY VARCHAR2)
713 IS
714
715 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
716 TYPE faf_list is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
717
718 l_sales_lead_id num_list;
719 l_salesforce_id num_list;
720 l_sales_group_id num_list;
721 l_faf faf_list;
722 l_var NUMBER;
723 l_worker_id NUMBER;
724 l_limit_flag BOOLEAN := FALSE;
725 l_max_fetches NUMBER := 10000;
726 l_loop_count NUMBER := 0;
727 l_flag BOOLEAN;
728 l_first NUMBER;
729 l_last NUMBER;
730 l_attempts NUMBER := 0;
731
732 CURSOR team_leader(c_worker_id number) IS
733 SELECT /*+ LEADING(WIN) USE_NL(A WIN) INDEX(A) */ A.sales_lead_id,
734 A.salesforce_id,
735 A.sales_group_id,
736 NVL(WIN.full_access_flag,'N')
737 FROM AS_ACCESSES_ALL_ALL A,
738 JTF_TAE_1001_LEAD_WINNERS WIN
739 WHERE A.lead_id is NULL
740 AND A.sales_lead_id is NOT NULL
741 AND A.delete_flag is NULL
742 AND NVL(A.team_leader_flag,'N') <> NVL(WIN.full_access_flag,'N')
743 AND WIN.source_id = -1001
744 AND WIN.worker_id = c_worker_id
745 AND WIN.resource_type = 'RS_EMPLOYEE'
746 AND WIN.trans_object_id = A.sales_lead_id
747 AND WIN.resource_id = A.salesforce_id
748 AND WIN.group_id = A.sales_group_id
749 GROUP BY A.sales_lead_id,
750 A.salesforce_id,
751 A.sales_group_id,
752 WIN.full_access_flag;
753
754
755 BEGIN
756 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_START);
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758 l_worker_id:=p_terr_globals.worker_id;
759 l_var :=p_terr_globals.bulk_size;
760 l_max_fetches := p_terr_globals.cursor_limit;
761 LOOP -- For l_limit_flag
762 IF (l_limit_flag) THEN EXIT; END IF;
763
764 l_sales_lead_id.DELETE;
765 l_salesforce_id.DELETE;
766 l_sales_group_id.DELETE;
767 l_faf.DELETE;
768 l_loop_count := l_loop_count + 1;
769 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'LOOPCOUNT :- ' || l_loop_count);
770
771 --------------------------------
772 OPEN team_leader(l_worker_id);
773 FETCH team_leader BULK COLLECT INTO
774 l_sales_lead_id, l_salesforce_id, l_sales_group_id, l_faf
775 LIMIT l_max_fetches;
776 CLOSE team_leader;
777
778 -- Initialize variables
779 l_flag := TRUE;
780 l_first := 0;
781 l_last := 0;
782 l_attempts := 1;
783
784 IF l_sales_lead_id.COUNT < l_max_fetches THEN
785 l_limit_flag := TRUE;
786 END IF;
787 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
788
789 IF l_sales_lead_id.COUNT > 0 THEN
790 l_flag := TRUE;
791 l_first := l_sales_lead_id.FIRST;
792 l_last := l_first + l_var;
793 WHILE l_flag LOOP
794 IF l_last > l_sales_lead_id.LAST THEN
795 l_last := l_sales_lead_id.LAST;
796 END IF;
797 WHILE l_attempts < 3 LOOP
798 BEGIN
799 FORALL i IN l_first .. l_last
800 UPDATE AS_ACCESSES_ALL_ALL ACC
801 SET object_version_number = NVL(object_version_number,0) + 1,
802 ACC.last_update_date = SYSDATE,
803 ACC.last_updated_by = p_terr_globals.user_id,
804 ACC.last_update_login = p_terr_globals.last_update_login,
805 ACC.request_id = p_terr_globals.request_id,
806 ACC.program_application_id = p_terr_globals.prog_appl_id,
807 ACC.program_id = p_terr_globals.prog_id,
808 ACC.program_update_date = SYSDATE,
809 ACC.team_leader_flag = l_faf(i)
810 WHERE ACC.lead_id is NULL
811 AND ACC.sales_lead_id is NOT NULL
812 AND ACC.sales_lead_id = l_sales_lead_id(i)
813 AND ACC.salesforce_id = l_salesforce_id(i)
814 AND ACC.sales_group_id = l_sales_group_id(i);
815 COMMIT;
816 l_attempts := 3;
817 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS ||AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
818 EXCEPTION
819 WHEN DEADLOCK_DETECTED THEN
820 BEGIN
821 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_DEADLOCK ||l_attempts);
822 ROLLBACK;
823 l_attempts := l_attempts +1;
824 IF l_attempts = 3 THEN
825 FOR i IN l_first .. l_last
826 LOOP
827 BEGIN
828 UPDATE AS_ACCESSES_ALL_ALL ACC
829 SET object_version_number = NVL(object_version_number,0) + 1,
830 ACC.last_update_date = SYSDATE,
831 ACC.last_updated_by = p_terr_globals.user_id,
832 ACC.last_update_login = p_terr_globals.last_update_login,
833 ACC.request_id = p_terr_globals.request_id,
834 ACC.program_application_id = p_terr_globals.prog_appl_id,
835 ACC.program_id = p_terr_globals.prog_id,
836 ACC.program_update_date = SYSDATE,
837 ACC.team_leader_flag = l_faf(i)
838 WHERE ACC.lead_id is NULL
839 AND ACC.sales_lead_id is NOT NULL
840 AND ACC.sales_lead_id = l_sales_lead_id(i)
841 AND ACC.salesforce_id = l_salesforce_id(i)
842 AND ACC.sales_group_id = l_sales_group_id(i);
843 EXCEPTION
844 WHEN OTHERS THEN
845 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
846 AS_GAR.LOG('SALES_LEAD_ID/SALESFORCE_ID/SALESGROUP_ID/ORG_ID - ' || l_sales_lead_id(i) || '/' || l_salesforce_id(i) || '/' || l_sales_group_id(i) );
847 END;
848 END LOOP; -- for each record individually
849 COMMIT;
850 END IF;
851 END; -- end of deadlock exception
852 WHEN OTHERS THEN
853 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD, SQLERRM, TO_CHAR(SQLCODE));
854 x_errbuf := SQLERRM;
855 x_retcode := SQLCODE;
856 x_return_status := FND_API.G_RET_STS_ERROR;
857 RAISE;
858 END;
859 END LOOP; -- loop for 3 attempts
860 /* For the next batch of records by bulk_size */
861 l_first := l_last + 1;
862 l_last := l_first + l_var;
863 IF l_first > l_sales_lead_id.LAST THEN
864 l_flag := FALSE;
865 END IF;
866 END LOOP; -- loop for more records within the bulk_size
867 END IF; --l_sales_lead_id.count > 0
868 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
869 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
870 END LOOP; -- loop for more bulk_size fetches
871 l_sales_lead_id.DELETE;
872 l_faf.DELETE;
873 l_salesforce_id.DELETE;
874 l_sales_group_id.DELETE;
875 EXCEPTION
876 WHEN OTHERS THEN
877 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD, SQLERRM, TO_CHAR(SQLCODE));
878 x_errbuf := SQLERRM;
879 x_retcode := SQLCODE;
880 x_return_status := FND_API.G_RET_STS_ERROR;
881 RAISE;
882 END SET_TEAM_LEAD_LEADS;
883
884 /************************** End Set Leads Team Leader *****************/
885
886 /************************** Start Insert Into Entity Accesses*************/
887
888 PROCEDURE INSERT_ACCESSES_LEADS(
889 x_errbuf OUT NOCOPY VARCHAR2,
890 x_retcode OUT NOCOPY VARCHAR2,
891 p_terr_globals IN AS_GAR.TERR_GLOBALS,
892 x_return_status OUT NOCOPY VARCHAR2)
893 IS
894 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
895 TYPE faf_list is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
896
897
898
899 l_sales_lead_id num_list;
900 l_org_id num_list;
901 l_salesforce_id num_list;
902 l_sales_group_id num_list;
903 l_person_id num_list;
904 l_faf faf_list;
905
906
907 l_var NUMBER;
908 l_worker_id NUMBER;
909 l_limit_flag BOOLEAN := FALSE;
910 l_max_fetches NUMBER := 10000;
911 l_loop_count NUMBER := 0;
912 l_flag BOOLEAN;
913 l_first NUMBER;
914 l_last NUMBER;
915 l_attempts NUMBER := 0;
916 l_src_exists VARCHAR2(1);
917
918 CURSOR ins_acc(c_worker_id number) IS
919 SELECT W.resource_id,
920 W.group_id,
921 MIN(W.person_id) person_id,
922 W.trans_object_id sales_lead_id,
923 MAX(W.full_access_flag) FAF,
924 W.org_id
925 FROM JTF_TAE_1001_LEAD_WINNERS W
926 WHERE W.source_id = -1001
927 AND W.worker_id = c_worker_id
928 AND W.resource_type = 'RS_EMPLOYEE'
929 AND W.group_id IS NOT NULL --- Added to work around the JTY functionality which allows group_id to be NULL during setup of resources.
930 GROUP BY W.trans_object_id,
931 W.resource_id,
932 W.group_id,
933 W.org_id;
934
935 BEGIN
936 /*-------------------------------------------------------------------------+
937 | PROGRAM LOGIC
938 |
939 | Re-Initialize variables and null out if necessary.
940 | Try bulk inserting into accesses. If this fails, insert records one by one.
941 |
942 +-------------------------------------------------------------------------*/
943 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
944 x_return_status := FND_API.G_RET_STS_SUCCESS;
945 l_worker_id:=p_terr_globals.worker_id;
946 l_var :=p_terr_globals.bulk_size;
947 OPEN ins_acc(l_worker_id);
948 LOOP
949 IF (l_limit_flag) THEN EXIT; END IF;
950
951 l_sales_lead_id.DELETE;
952 l_org_id.DELETE;
953 l_salesforce_id.DELETE;
954 l_sales_group_id.DELETE;
955 l_person_id.DELETE;
956 l_faf.DELETE;
957
958 EXIT WHEN ins_acc%NOTFOUND;
959
960 l_loop_count := l_loop_count + 1;
961 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'LOOPCOUNT :- ' || l_loop_count);
962
963 FETCH ins_acc BULK COLLECT INTO
964 l_salesforce_id, l_sales_group_id, l_person_id,
965 l_sales_lead_id,l_faf,l_org_id
966 LIMIT l_max_fetches;
967
968 -- Initialize variables
969 l_flag := TRUE;
970 l_first := 0;
971 l_last := 0;
972
973 IF l_sales_lead_id.COUNT < l_max_fetches THEN
974 l_limit_flag := TRUE;
975 END IF;
976
977 IF l_sales_lead_id.COUNT > 0 THEN
978 l_flag := TRUE;
979 l_first := l_sales_lead_id.FIRST;
980 l_last := l_first + l_var;
981 WHILE l_flag LOOP
982 IF l_last > l_sales_lead_id.LAST THEN
983 l_last := l_sales_lead_id.LAST;
984 END IF;
985 BEGIN
986 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' to '||l_last);
987 FORALL i IN l_first .. l_last
988 INSERT INTO AS_ACCESSES_ALL_ALL
989 ( access_id
990 ,access_type
991 ,salesforce_id
992 ,sales_group_id
993 ,person_id
994 ,salesforce_role_code
995 ,customer_id
996 ,address_id
997 ,sales_lead_id
998 ,freeze_flag
999 ,reassign_flag
1000 ,team_leader_flag
1001 ,last_update_date
1002 ,last_updated_by
1003 ,creation_date
1004 ,created_by
1005 ,last_update_login
1006 ,request_id
1007 ,program_application_id
1008 ,program_id
1009 ,program_update_date
1010 ,created_by_tap_flag
1011 ,org_id
1012 ,open_flag
1013 ,lead_rank_score
1014 ,object_creation_date
1015 )
1016 (
1017 SELECT as_accesses_s.nextval
1018 ,'X'
1019 ,l_salesforce_id(i)
1020 ,l_sales_group_id(i)
1021 ,l_person_id(i)
1022 ,NULL
1023 ,L.customer_id
1024 ,L.address_id
1025 ,l_sales_lead_id(i)
1026 ,'N'
1027 ,'N'
1028 ,l_faf(i)
1029 ,SYSDATE
1030 ,p_terr_globals.user_id
1031 ,SYSDATE
1032 ,p_terr_globals.user_id
1033 ,p_terr_globals.last_update_login
1034 ,p_terr_globals.request_id
1035 ,p_terr_globals.prog_appl_id
1036 ,p_terr_globals.prog_id
1037 ,SYSDATE
1038 ,'Y'
1039 ,l_org_id(i)
1040 ,DECODE(STS.opp_open_status_flag, 'Y', 'Y', NULL)
1041 ,L.lead_rank_score
1042 ,L.creation_date
1043 FROM DUAL, AS_SALES_LEADS L, AS_STATUSES_B STS
1044 WHERE L.sales_lead_id = l_sales_lead_id(i)
1045 AND L.status_code = STS.status_code
1046 AND NOT EXISTS ( SELECT 'X'
1047 FROM AS_ACCESSES_ALL_ALL AA
1048 WHERE AA.sales_lead_id IS NOT NULL
1049 AND AA.lead_id IS NULL
1050 AND AA.delete_flag IS NULL
1051 AND AA.sales_lead_id = l_sales_lead_id(i)
1052 AND AA.salesforce_id = l_salesforce_id(i)
1053 AND AA.sales_group_id = l_sales_group_id(i)
1054 )
1055 );
1056 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1057 COMMIT;
1058 EXCEPTION
1059 WHEN DUP_VAL_ON_INDEX THEN
1060 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1061 FOR i IN l_first .. l_last LOOP
1062 BEGIN
1063 INSERT INTO AS_ACCESSES_ALL_ALL
1064 ( access_id
1065 ,access_type
1066 ,salesforce_id
1067 ,sales_group_id
1068 ,person_id
1069 ,salesforce_role_code
1070 ,customer_id
1071 ,address_id
1072 ,sales_lead_id
1073 ,freeze_flag
1074 ,reassign_flag
1075 ,team_leader_flag
1076 ,last_update_date
1077 ,last_updated_by
1078 ,creation_date
1079 ,created_by
1080 ,last_update_login
1081 ,request_id
1082 ,program_application_id
1083 ,program_id
1084 ,program_update_date
1085 ,created_by_tap_flag
1086 ,org_id
1087 ,open_flag
1088 ,lead_rank_score
1089 ,object_creation_date
1090 )
1091 (
1092 SELECT as_accesses_s.NEXTVAL
1093 ,'X'
1094 ,l_salesforce_id(i)
1095 ,l_sales_group_id(i)
1096 ,l_person_id(i)
1097 ,NULL
1098 ,L.customer_id
1099 ,L.address_id
1100 ,l_sales_lead_id(i)
1101 ,'N'
1102 ,'N'
1103 ,l_faf(i)
1104 ,SYSDATE
1105 ,p_terr_globals.user_id
1106 ,SYSDATE
1107 ,p_terr_globals.user_id
1108 ,p_terr_globals.last_update_login
1109 ,p_terr_globals.request_id
1110 ,p_terr_globals.prog_appl_id
1111 ,p_terr_globals.prog_id
1112 ,SYSDATE
1113 ,'Y'
1114 ,l_org_id(i)
1115 ,DECODE(STS.opp_open_status_flag, 'Y', 'Y', NULL)
1116 ,L.lead_rank_score
1117 ,L.creation_date
1118 FROM DUAL,AS_SALES_LEADS L, AS_STATUSES_B STS
1119 WHERE L.sales_lead_id = l_sales_lead_id(i)
1120 AND L.status_code = STS.status_code
1121 AND NOT EXISTS ( SELECT 'X'
1122 FROM AS_ACCESSES_ALL_ALL AA
1123 WHERE AA.sales_lead_id IS NOT NULL
1124 AND AA.lead_id IS NULL
1125 AND AA.delete_flag IS NULL
1126 AND AA.sales_lead_id = l_sales_lead_id(i)
1127 AND AA.salesforce_id = l_salesforce_id(i)
1128 AND AA.sales_group_id = l_sales_group_id(i)
1129 )
1130 );
1131 EXCEPTION
1132 WHEN OTHERS THEN
1133 NULL;
1134 END;
1135 END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
1136 COMMIT;
1137 WHEN OTHERS THEN
1138 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1139 x_errbuf := SQLERRM;
1140 x_retcode := SQLCODE;
1141 x_return_status := FND_API.G_RET_STS_ERROR;
1142 RAISE;
1143 END;
1144 l_first := l_last + 1;
1145 l_last := l_first + l_var;
1146 IF l_first > l_sales_lead_id.last THEN
1147 l_flag := FALSE;
1148 END IF;
1149 END LOOP; /* l_flag loop */
1150 END IF; --l_sales_lead_id.count > 0
1151 END LOOP; -- loop for more bulk_size fetches
1152 l_sales_lead_id.DELETE;
1153 l_org_id.DELETE;
1154 l_salesforce_id.DELETE;
1155 l_sales_group_id.DELETE;
1156 l_person_id.DELETE;
1157 l_faf.DELETE;
1158 IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1159
1160 EXCEPTION
1161 WHEN others THEN
1162 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC, SQLERRM, TO_CHAR(SQLCODE));
1163 x_errbuf := SQLERRM;
1164 x_retcode := SQLCODE;
1165 x_return_status := FND_API.G_RET_STS_ERROR;
1166 IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1167 RAISE;
1168 END INSERT_ACCESSES_LEADS;
1169
1170 /************************** End Insert Into Entity Accesses*************/
1171
1172 /************************** Start Insert Into Terr Accesses*************/
1173
1174 PROCEDURE INSERT_TERR_ACCESSES_LEADS(
1175 x_errbuf OUT NOCOPY VARCHAR2,
1176 x_retcode OUT NOCOPY VARCHAR2,
1177 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1178 x_return_status OUT NOCOPY VARCHAR2)
1179 IS
1180 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1181
1182 l_sales_lead_id num_list;
1183 l_salesforce_id num_list;
1184 l_sales_group_id num_list;
1185 l_terr_id num_list;
1186
1187 l_var NUMBER;
1188 l_limit_flag BOOLEAN := FALSE;
1189 l_worker_id NUMBER;
1190 l_max_fetches NUMBER := 10000;
1191 l_loop_count NUMBER := 0;
1192 l_flag BOOLEAN;
1193 l_first NUMBER;
1194 l_last NUMBER;
1195
1196
1197 CURSOR ins_tacc(c_worker_id number) IS
1198 SELECT w.terr_id
1199 ,w.trans_object_id
1200 ,w.resource_id
1201 ,w.group_id
1202 FROM JTF_TAE_1001_LEAD_WINNERS W
1203 WHERE W.source_id = -1001
1204 AND W.worker_id = c_worker_id
1205 AND W.resource_type = 'RS_EMPLOYEE'
1206 AND W.group_id IS NOT NULL --- Added to work around the JTY functionality which allows group_id to be NULL during setup of resources.
1207 GROUP BY W.terr_id,
1208 W.trans_object_id,
1209 W.resource_id,
1210 W.group_id;
1211
1212 BEGIN
1213 /*-------------------------------------------------------------------------+
1214 | PROGRAM LOGIC
1215 |
1216 | Re-Initialize variables and null out if necessary.
1217 | Almost the same as accesses, except the insertion is into as_territory_accesses
1218 | and there is no involvement of role.
1219 |
1220 +-------------------------------------------------------------------------*/
1221 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
1222 x_return_status := FND_API.G_RET_STS_SUCCESS;
1223 l_worker_id := p_terr_globals.worker_id;
1224 l_var := p_terr_globals.bulk_size;
1225 OPEN ins_tacc(l_worker_id);
1226 LOOP
1227 IF (l_limit_flag) THEN EXIT; END IF;
1228 EXIT WHEN ins_tacc%NOTFOUND;
1229 l_loop_count := l_loop_count + 1;
1230 l_sales_lead_id.DELETE;
1231 l_salesforce_id.DELETE;
1232 l_sales_group_id.DELETE;
1233 l_terr_id.DELETE;
1234 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'LOOPCOUNT :- ' || l_loop_count);
1235 BEGIN
1236
1237 FETCH ins_tacc BULK COLLECT INTO l_terr_id,
1238 l_sales_lead_id, l_salesforce_id, l_sales_group_id
1239 LIMIT l_max_fetches;
1240 -- Initialize variables
1241 l_flag := TRUE;
1242 l_first := 0;
1243 l_last := 0;
1244
1245 IF l_sales_lead_id.COUNT < l_max_fetches THEN l_limit_flag := TRUE; END IF;
1246 IF l_sales_lead_id.COUNT > 0 THEN
1247 l_flag := TRUE;
1248 l_first := l_sales_lead_id.first;
1249 l_last := l_first + l_var;
1250 WHILE l_flag LOOP
1251 IF l_last > l_sales_lead_id.last THEN
1252 l_last := l_sales_lead_id.last;
1253 END IF;
1254 BEGIN
1255 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1256 FORALL i IN l_first .. l_last
1257 INSERT INTO AS_TERRITORY_ACCESSES
1258 ( access_id,
1259 territory_id,
1260 user_territory_id,
1261 last_update_date,
1262 last_updated_by,
1263 creation_date,
1264 created_by,
1265 last_update_login,
1266 request_id,
1267 program_application_id,
1268 program_id,
1269 program_update_date
1270 )
1271 (
1272 SELECT
1273 V.acc_id,
1274 l_terr_id(i),
1275 l_terr_id(i),
1276 SYSDATE,
1277 p_terr_globals.user_id,
1278 SYSDATE,
1279 p_terr_globals.user_id,
1280 p_terr_globals.last_update_login,
1281 p_terr_globals.request_id,
1282 p_terr_globals.prog_appl_id,
1283 p_terr_globals.prog_id,
1284 SYSDATE
1285 FROM
1286 ( SELECT DISTINCT A.access_id acc_id
1287 FROM AS_ACCESSES_ALL_ALL A
1288 WHERE A.sales_lead_id=l_sales_lead_id(i)
1289 AND A.sales_group_id = l_sales_group_id(i)
1290 AND A.salesforce_id=l_salesforce_id(i)
1291 AND A.sales_lead_id is NOT NULL
1292 AND A.delete_flag IS NULL
1293 AND A.lead_id is NULL
1294 AND NOT EXISTS
1295 (SELECT 'X'
1296 FROM AS_TERRITORY_ACCESSES AST
1297 WHERE AST.access_id = A.access_id
1298 AND AST.territory_id = l_terr_id(i))
1299 ) V
1300 );
1301 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1302 COMMIT;
1303 EXCEPTION
1304 WHEN DUP_VAL_ON_INDEX THEN
1305 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1306 FOR i IN l_first .. l_last LOOP
1307 BEGIN
1308 INSERT INTO AS_TERRITORY_ACCESSES
1309 ( access_id,
1310 territory_id,
1311 user_territory_id,
1312 last_update_date,
1313 last_updated_by,
1314 creation_date,
1315 created_by,
1316 last_update_login,
1317 request_id,
1318 program_application_id,
1319 program_id,
1320 program_update_date
1321 )
1322 (
1323 SELECT
1324 v.acc_id,
1325 l_terr_id(i),
1326 l_terr_id(i),
1327 SYSDATE,
1328 p_terr_globals.user_id,
1329 SYSDATE,
1330 p_terr_globals.user_id,
1331 p_terr_globals.last_update_login,
1332 p_terr_globals.request_id,
1333 p_terr_globals.prog_appl_id,
1334 p_terr_globals.prog_id,
1335 SYSDATE
1336 FROM
1337 ( SELECT DISTINCT a.access_id acc_id
1338 FROM AS_ACCESSES_ALL_ALL A
1339 WHERE A.sales_lead_id=l_sales_lead_id(i)
1340 AND A.sales_group_id = l_sales_group_id(i)
1341 AND A.salesforce_id=l_salesforce_id(i)
1342 AND A.sales_lead_id is NOT NULL
1343 AND A.lead_id is NULL
1344 AND A.delete_flag IS NULL
1345 AND NOT EXISTS
1346 (SELECT 'X'
1347 FROM AS_TERRITORY_ACCESSES AST
1348 WHERE AST.access_id = A.access_id
1349 AND AST.territory_id = l_terr_id(i))
1350 ) V
1351 );
1352 EXCEPTION
1353 WHEN Others THEN
1354 NULL;
1355 END;
1356 END LOOP;
1357 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1358 COMMIT;
1359 WHEN Others THEN
1360 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1361 x_errbuf := SQLERRM;
1362 x_retcode := SQLCODE;
1363 x_return_status := FND_API.G_RET_STS_ERROR;
1364 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1365 RAISE;
1366 END;
1367 l_first := l_last + 1;
1368 l_last := l_first + l_var;
1369 IF l_first > l_sales_lead_id.last THEN
1370 l_flag := FALSE;
1371 END IF;
1372 END LOOP;
1373 END IF; --l_sales_lead_id.count > 0
1374 EXCEPTION
1375 WHEN Others THEN
1376 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC , SQLERRM, TO_CHAR(SQLCODE));
1377 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1378 x_errbuf := SQLERRM;
1379 x_retcode := SQLCODE;
1380 x_return_status := FND_API.G_RET_STS_ERROR;
1381 RAISE;
1382 END;
1383 END LOOP; -- end loop for insert into territory accesses
1384 l_sales_lead_id.DELETE;
1385 l_salesforce_id.DELETE;
1386 l_sales_group_id.DELETE;
1387 l_terr_id.DELETE;
1388 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1389 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
1390 EXCEPTION
1391 WHEN others THEN
1392 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
1393 x_errbuf := SQLERRM;
1394 x_retcode := SQLCODE;
1395 x_return_status := FND_API.G_RET_STS_ERROR;
1396 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1397 RAISE;
1398 END INSERT_TERR_ACCESSES_LEADS;
1399
1400 /************************** End Insert Into Terr Accesses*************/
1401
1402
1403 /************************** Start Lead Cleanup ***********************/
1404
1405 PROCEDURE Perform_Lead_Cleanup(
1406 x_errbuf OUT NOCOPY VARCHAR2,
1407 x_retcode OUT NOCOPY VARCHAR2,
1408 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1409 x_return_status OUT NOCOPY VARCHAR2)
1410 IS
1411
1412 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1413 l_sales_lead_id num_list;
1414 l_access_id num_list;
1415
1416
1417 l_flag BOOLEAN;
1418 l_first NUMBER;
1419 l_last NUMBER;
1420 l_var NUMBER;
1421 l_attempts NUMBER := 0;
1422
1423 l_worker_id NUMBER;
1424
1425 l_del_flag BOOLEAN:=FALSE;
1426 l_limit_flag BOOLEAN := FALSE;
1427 l_max_fetches NUMBER := 10000;
1428 l_loop_count NUMBER := 0;
1429 G_NUM_REC CONSTANT NUMBER:=10000;
1430 G_DEL_REC CONSTANT NUMBER:=10001;
1431
1432
1433
1434 CURSOR del_lead_totalmode(c_worker_id number) IS
1435 SELECT distinct trans_object_id
1436 FROM JTF_TAE_1001_LEAD_TRANS
1437 WHERE worker_id=c_worker_id;
1438
1439 CURSOR del_lead_newmode(c_worker_id number) IS
1440 SELECT distinct trans_object_id
1441 FROM JTF_TAE_1001_LEAD_NM_TRANS
1442 WHERE worker_id=c_worker_id;
1443
1444 BEGIN
1445 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_START);
1446 x_return_status := FND_API.G_RET_STS_SUCCESS;
1447 l_worker_id := p_terr_globals.worker_id;
1448 l_var := p_terr_globals.bulk_size;
1449 l_max_fetches := p_terr_globals.cursor_limit;
1450
1451 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1452 OPEN del_lead_totalmode(l_worker_id);
1453 ELSE
1454 OPEN del_lead_newmode(l_worker_id);
1455 END IF;
1456 LOOP --{L1
1457 IF (l_limit_flag) THEN EXIT; END IF;
1458
1459 l_loop_count := l_loop_count + 1;
1460 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'LOOPCOUNT :- ' ||l_loop_count);
1461 BEGIN
1462 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1463 EXIT WHEN del_lead_totalmode%NOTFOUND;
1464 FETCH del_lead_totalmode BULK COLLECT INTO l_sales_lead_id
1465 LIMIT l_max_fetches;
1466 ELSE
1467 EXIT WHEN del_lead_newmode%NOTFOUND;
1468 FETCH del_lead_newmode BULK COLLECT INTO l_sales_lead_id
1469 LIMIT l_max_fetches;
1470 END IF;
1471 -- Initialize variables (Ist Init)
1472 l_flag := TRUE;
1473 l_first := 0;
1474 l_last := 0;
1475 l_attempts := 1;
1476
1477 IF l_sales_lead_id.COUNT < l_max_fetches THEN
1478 l_limit_flag := TRUE;
1479 END IF;
1480
1481 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_START);
1482 IF l_sales_lead_id.count > 0 THEN --{I1
1483 l_flag := TRUE;
1484 l_first := l_sales_lead_id.first;
1485 l_last := l_first + l_var;
1486 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED ||
1487 l_sales_lead_id.FIRST || '-' ||
1488 l_sales_lead_id.LAST);
1489 WHILE l_flag LOOP --{L2 10K cust loop
1490 IF l_last > l_sales_lead_id.LAST THEN
1491 l_last := l_sales_lead_id.LAST;
1492 END IF;
1493 l_del_flag := FALSE;
1494 l_attempts := 1;
1495 LOOP --{L3 to update only 10k record at a time
1496 IF (l_del_flag) THEN EXIT; END IF;
1497 l_del_flag := FALSE;
1498 WHILE l_attempts < 3 LOOP --{L4
1499 BEGIN
1500 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
1501 FORALL i in l_first..l_last
1502 UPDATE AS_ACCESSES_ALL_ALL ACC
1503 SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1504 WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1505 AND ACC.freeze_flag = 'N'
1506 AND ACC.lead_id IS NULL
1507 AND ACC.sales_lead_id IS NOT NULL
1508 AND ACC.delete_flag IS NULL
1509 AND NOT EXISTS (SELECT 'X'
1510 FROM JTF_TAE_1001_LEAD_WINNERS W
1511 WHERE W.trans_object_id = ACC.sales_lead_id
1512 AND W.worker_id = l_worker_id
1513 AND W.resource_id = ACC.salesforce_id
1514 AND W.group_id = ACC.sales_group_id)
1515 AND ROWNUM < G_DEL_REC;
1516 COMMIT;
1517 l_attempts := 3;
1518 IF l_access_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
1519 EXCEPTION
1520 WHEN DUP_VAL_ON_INDEX THEN
1521 BEGIN
1522 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_DEL || AS_GAR.G_START);
1523 FORALL i in l_first..l_last
1524 DELETE FROM AS_ACCESSES_ALL_ALL ACC
1525 WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1526 AND ACC.freeze_flag = 'N'
1527 AND ACC.lead_id IS NULL
1528 AND ACC.sales_lead_id IS NOT NULL
1529 AND NOT EXISTS (SELECT 'X'
1530 FROM JTF_TAE_1001_LEAD_WINNERS W
1531 WHERE W.trans_object_id = ACC.sales_lead_id
1532 AND W.worker_id = l_worker_id
1533 AND W.resource_id = ACC.salesforce_id
1534 AND W.group_id = ACC.sales_group_id)
1535 AND ROWNUM < G_DEL_REC;
1536 COMMIT;
1537 l_attempts := 3;
1538 IF l_access_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
1539 EXCEPTION
1540 WHEN OTHERS THEN
1541 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_DEL, SQLERRM, TO_CHAR(SQLCODE));
1542 END;
1543 WHEN deadlock_detected THEN
1544 BEGIN --{I2
1545 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_UPD || AS_GAR.G_DEADLOCK || l_attempts);
1546 ROLLBACK;
1547 l_attempts := l_attempts +1;
1548 IF l_attempts = 3 THEN
1549 FOR i IN l_first .. l_last LOOP --{L5
1550 BEGIN
1551 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_UPD || AS_GAR.G_START);
1552 UPDATE AS_ACCESSES_ALL_ALL ACC
1553 SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
1554 WHERE ACC.sales_lead_id = l_sales_lead_id(i)
1555 AND ACC.freeze_flag = 'N'
1556 AND ACC.lead_id IS NULL
1557 AND ACC.sales_lead_id IS NOT NULL
1558 AND ACC.delete_flag IS NULL
1559 AND NOT EXISTS (SELECT 'X'
1560 FROM JTF_TAE_1001_LEAD_WINNERS W
1561 WHERE W.trans_object_id = ACC.sales_lead_id
1562 AND W.resource_id = ACC.salesforce_id
1563 AND W.worker_id = l_worker_id
1564 AND W.group_id = ACC.sales_group_id);
1565 COMMIT;
1566 EXCEPTION
1567 WHEN DUP_VAL_ON_INDEX THEN
1568 BEGIN
1569 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_DEL || AS_GAR.G_START);
1570 DELETE FROM AS_ACCESSES_ALL_ALL ACC
1571 WHERE ACC.sales_lead_id=l_sales_lead_id(i)
1572 AND ACC.freeze_flag = 'N'
1573 AND ACC.lead_id IS NULL
1574 AND ACC.sales_lead_id IS NOT NULL
1575 AND NOT EXISTS (SELECT 'X'
1576 FROM JTF_TAE_1001_LEAD_WINNERS W
1577 WHERE W.trans_object_id = ACC.sales_lead_id
1578 AND W.resource_id = ACC.salesforce_id
1579 AND W.worker_id = l_worker_id
1580 AND W.group_id = ACC.sales_group_id);
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_DEL || AS_GAR.G_GENERAL_EXCEPTION);
1584 AS_GAR.LOG('SALES_LEAD_ID - ' || l_sales_lead_id(i));
1585 END;
1586 END;
1587 END LOOP; --}L5
1588 COMMIT;
1589 l_del_flag := TRUE;
1590 END IF;
1591 END; --}I2 end of deadlock exception
1592 WHEN OTHERS THEN
1593 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1594 IF del_lead_totalmode%ISOPEN THEN CLOSE del_lead_totalmode; END IF;
1595 IF del_lead_newmode%ISOPEN THEN CLOSE del_lead_newmode; END IF;
1596 x_errbuf := SQLERRM;
1597 x_retcode := SQLCODE;
1598 x_return_status := FND_API.G_RET_STS_ERROR;
1599 RAISE;
1600 END;
1601 END LOOP; --}L4 l_attempts loop 3 trys
1602 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-' || l_last);
1603 END LOOP; --}L3 -- to update only 10k record at a time on accesses
1604 l_first := l_last + 1;
1605 l_last := l_first + l_var;
1606 IF l_first > l_sales_lead_id.LAST THEN
1607 l_flag := FALSE;
1608 END IF;
1609 END LOOP; --}L2 while l_flag loop (10K cust loop)
1610 END IF;--}I1
1611 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
1612 COMMIT;
1613 EXCEPTION
1614 WHEN Others THEN
1615 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1616 IF del_lead_totalmode%ISOPEN THEN CLOSE del_lead_totalmode; END IF;
1617 IF del_lead_newmode%ISOPEN THEN CLOSE del_lead_newmode; END IF;
1618 x_errbuf := SQLERRM;
1619 x_retcode := SQLCODE;
1620 x_return_status := FND_API.G_RET_STS_ERROR;
1621 RAISE;
1622 END;
1623 END LOOP;--}L1
1624 IF del_lead_totalmode%ISOPEN THEN CLOSE del_lead_totalmode; END IF;
1625 IF del_lead_newmode%ISOPEN THEN CLOSE del_lead_newmode; END IF;
1626 EXCEPTION
1627 WHEN OTHERS THEN
1628 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1629 x_errbuf := SQLERRM;
1630 x_retcode := SQLCODE;
1631 x_return_status := FND_API.G_RET_STS_ERROR;
1632 RAISE;
1633 END PERFORM_LEAD_CLEANUP;
1634
1635 /************************** End Lead Cleanup ***********************/
1636 /**************************** Start Assign Lead Owner ********************/
1637 PROCEDURE ASSIGN_LEAD_OWNER(
1638 x_errbuf OUT NOCOPY VARCHAR2,
1639 x_retcode OUT NOCOPY VARCHAR2,
1640 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1641 x_return_status OUT NOCOPY VARCHAR2)
1642 IS
1643 l_return_status VARCHAR2(1);
1644 l_msg_count NUMBER;
1645 l_msg_data VARCHAR2(2000);
1646
1647 CURSOR lead_owner_totalmode(c_worker_id number) IS
1648 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
1649 max(DECODE(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
1650 FROM AS_ACCESSES_ALL_ALL aaa,
1651 ( SELECT distinct trans_object_id
1652 FROM JTF_TAE_1001_LEAD_TRANS
1653 WHERE worker_id=c_worker_id ) w
1654 WHERE aaa.lead_id is NULL
1655 AND aaa.sales_lead_id is NOT NULL
1656 AND aaa.delete_flag is NULL
1657 AND aaa.sales_lead_id=w.trans_object_id
1658 AND aaa.sales_lead_id+0=w.trans_object_id
1659 GROUP BY aaa.sales_lead_id
1660 HAVING SUM(DECODE(aaa.CREATED_BY_TAP_FLAG,'Y',1,0)) > 0
1661 AND SUM(DECODE(aaa.owner_flag,'Y',1,0)) = 0
1662 UNION -- Union added for Bug#4035168
1663 SELECT trans_object_id ,0
1664 FROM JTF_TAE_1001_LEAD_TRANS w
1665 WHERE worker_id = c_worker_id
1666 AND NOT EXISTS
1667 (SELECT 'x'
1668 FROM AS_ACCESSES_ALL aaa
1669 WHERE aaa.sales_lead_id =w.trans_object_id
1670 AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
1671 OR aaa.owner_flag='Y'));
1672
1673
1674 CURSOR lead_owner_newmode(c_worker_id number) IS
1675 SELECT /*+ index(aaa as_accesses_n6) */ aaa.sales_lead_id ,
1676 max(DECODE(aaa.created_by_tap_flag,'Y',aaa.access_id,-999)) access_id
1677 FROM AS_ACCESSES_ALL_ALL aaa,
1678 ( SELECT distinct trans_object_id
1679 FROM JTF_TAE_1001_LEAD_NM_TRANS
1680 WHERE worker_id=c_worker_id ) w
1681 WHERE aaa.lead_id is null
1682 AND aaa.delete_flag is null
1683 AND aaa.sales_lead_id=w.trans_object_id
1684 AND aaa.sales_lead_id+0=w.trans_object_id
1685 GROUP BY aaa.sales_lead_id
1686 HAVING SUM(DECODE(aaa.CREATED_BY_TAP_FLAG,'Y',1,0)) > 0
1687 AND SUM(DECODE(aaa.owner_flag,'Y',1,0)) = 0
1688 UNION -- Union added for Bug#4035168
1689 SELECT trans_object_id ,0
1690 FROM JTF_TAE_1001_LEAD_NM_TRANS w
1691 WHERE worker_id = c_worker_id
1692 AND NOT EXISTS
1693 (SELECT 'x'
1694 FROM AS_ACCESSES_ALL aaa
1695 WHERE aaa.sales_lead_id =w.trans_object_id
1696 AND (aaa.CREATED_BY_TAP_FLAG = 'Y'
1697 OR aaa.owner_flag='Y'));
1698
1699
1700
1701 TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1702
1703 l_sales_lead_id num_list;
1704 l_access_id num_list;
1705
1706 l_limit_flag BOOLEAN := FALSE;
1707 l_max_fetches NUMBER := 10000;
1708 l_loop_count NUMBER := 0;
1709
1710 l_attempts NUMBER := 0;
1711 l_exceptions BOOLEAN := FALSE;
1712
1713 l_flag BOOLEAN;
1714 l_first NUMBER;
1715 l_last NUMBER;
1716 l_worker_id NUMBER;
1717 l_var NUMBER;
1718
1719
1720
1721 BEGIN
1722 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_START);
1723
1724 x_return_status := FND_API.G_RET_STS_SUCCESS;
1725 l_worker_id:=p_terr_globals.worker_id;
1726 l_var :=p_terr_globals.bulk_size;
1727 l_max_fetches := p_terr_globals.cursor_limit;
1728 IF fnd_profile.value('AS_LEAD_ASSIGNMENT_UHK') = 'Y'
1729 THEN
1730 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'CUSTOM AS PER PROFILE---AS_LEAD_ASSIGNMENT_UHK:- Y---');
1731 AS_CUSTOM_HOOKS_UHK.Lead_TOTTAP_Owner_Assignment(
1732 p_request_id => p_terr_globals.request_id,
1733 p_worker_id => p_terr_globals.worker_id,
1734 x_return_status => l_return_status,
1735 x_msg_count => l_msg_count,
1736 x_msg_data => l_msg_data);
1737 ELSE
1738 LOOP
1739 IF (l_limit_flag) THEN EXIT; END IF;
1740 l_loop_count := l_loop_count + 1;
1741 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'LOOPCOUNT:- '|| l_loop_count);
1742 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1743 OPEN lead_owner_totalmode(l_worker_id);
1744 FETCH lead_owner_totalmode BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
1745 CLOSE lead_owner_totalmode;
1746 ELSE
1747 OPEN lead_owner_newmode(l_worker_id);
1748 FETCH lead_owner_newmode BULK COLLECT INTO l_sales_lead_id,l_access_id LIMIT l_max_fetches;
1749 CLOSE lead_owner_newmode;
1750 END IF;
1751 l_flag := TRUE;
1752 l_first := 0;
1753 l_last := 0;
1754 l_attempts := 1;
1755
1756 IF l_sales_lead_id.COUNT < l_max_fetches THEN
1757 l_limit_flag := TRUE;
1758 END IF;
1759
1760 IF l_sales_lead_id.COUNT > 0 THEN
1761 l_flag := TRUE;
1762 l_first := l_sales_lead_id.FIRST;
1763 l_last := l_first + l_var;
1764 WHILE l_flag LOOP
1765 IF l_last > l_sales_lead_id.LAST THEN
1766 l_last := l_sales_lead_id.LAST;
1767 END IF;
1768 WHILE l_attempts < 3 LOOP
1769 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS');
1770 BEGIN
1771 FORALL i in l_first .. l_last
1772 UPDATE AS_SALES_LEADS sl
1773 SET sl.last_update_date = SYSDATE,
1774 sl.last_updated_by = p_terr_globals.user_id,
1775 sl.last_update_login = p_terr_globals.last_update_login,
1776 sl.request_id = p_terr_globals.request_id,
1777 sl.program_application_id = p_terr_globals.prog_appl_id,
1778 sl.program_id = p_terr_globals.prog_id,
1779 sl.program_update_date = SYSDATE,
1780 ( sl.assign_to_salesforce_id,
1781 sl.assign_sales_group_id,
1782 sl.assign_to_person_id
1783 ) =
1784 ( SELECT salesforce_id,sales_group_id,person_id
1785 FROM as_accesses_all_all
1786 WHERE access_id = l_access_id(i)
1787 )
1788 WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
1789 COMMIT;
1790 l_attempts := 3;
1791 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
1792 EXCEPTION
1793 WHEN deadlock_detected THEN
1794 BEGIN
1795 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_DEADLOCK ||l_attempts);
1796 ROLLBACK;
1797 l_attempts := l_attempts +1;
1798 IF l_attempts = 3 THEN
1799 FOR i IN l_first .. l_last LOOP
1800 BEGIN
1801 UPDATE AS_SALES_LEADS sl
1802 SET sl.last_update_date = SYSDATE,
1803 sl.last_updated_by = p_terr_globals.user_id,
1804 sl.last_update_login = p_terr_globals.last_update_login,
1805 sl.request_id = p_terr_globals.request_id,
1806 sl.program_application_id = p_terr_globals.prog_appl_id,
1807 sl.program_id = p_terr_globals.prog_id,
1808 sl.program_update_date = SYSDATE,
1809 ( sl.assign_to_salesforce_id,
1810 sl.assign_sales_group_id,
1811 sl.assign_to_person_id
1812 ) =
1813 ( SELECT salesforce_id,sales_group_id,person_id
1814 FROM as_accesses_all_all
1815 WHERE access_id = l_access_id(i)
1816 )
1817 WHERE sl.sales_lead_id = l_sales_lead_id(i) ;
1818 EXCEPTION
1819 WHEN OTHERS THEN
1820 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF SALES LEADS', SQLERRM, TO_CHAR(SQLCODE));
1821 END;
1822 END LOOP;
1823 COMMIT;
1824 END IF;
1825 END; -- end of deadlock exception
1826 WHEN OTHERS THEN
1827 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_SALES_LEADS', SQLERRM, TO_CHAR(SQLCODE));
1828 x_errbuf := SQLERRM;
1829 x_retcode := SQLCODE;
1830 x_return_status := FND_API.G_RET_STS_ERROR;
1831 RAISE;
1832 END;
1833 END LOOP;
1834
1835 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES');
1836 l_attempts := 1;
1837 WHILE l_attempts < 3 LOOP
1838 BEGIN
1839 FORALL i in l_first .. l_last
1840 UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = NVL(object_version_number,0) + 1,
1841 ACC.LAST_UPDATE_DATE = SYSDATE,
1842 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
1843 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
1844 ACC.REQUEST_ID = p_terr_globals.request_id,
1845 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
1846 ACC.PROGRAM_ID = p_terr_globals.prog_id,
1847 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
1848 ACC.owner_flag = 'Y'
1849 WHERE ACC.access_id = l_access_id(i);
1850 COMMIT;
1851 l_attempts := 3;
1852 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES::' || AS_GAR.G_N_ROWS_PROCESSED || l_first || ' - '|| l_last);
1853 EXCEPTION
1854 WHEN deadlock_detected THEN
1855 BEGIN
1856 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE AS_ACCESSES::' || AS_GAR.G_DEADLOCK ||l_attempts );
1857 l_attempts := l_attempts +1;
1858 ROLLBACK;
1859 IF l_attempts = 3 THEN
1860 FOR i IN l_first .. l_last LOOP
1861 BEGIN
1862 UPDATE AS_ACCESSES_ALL_ALL ACC SET object_version_number = NVL(object_version_number,0) + 1,
1863 ACC.LAST_UPDATE_DATE = SYSDATE,
1864 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
1865 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
1866 ACC.REQUEST_ID = p_terr_globals.request_id,
1867 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
1868 ACC.PROGRAM_ID = p_terr_globals.prog_id,
1869 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
1870 ACC.owner_flag = 'Y'
1871 WHERE ACC.access_id = l_access_id(i);
1872 EXCEPTION
1873 WHEN OTHERS THEN
1874 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'ROW-BY-ROW UPDATE OF SALES LEADS ACCESSES', SQLERRM, TO_CHAR(SQLCODE));
1875 END;
1876 END LOOP;
1877 COMMIT;
1878 END IF;
1879 END; -- end of deadlock exception
1880 WHEN OTHERS THEN
1881 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || 'UPDATE SALES_LEADS ACCESSES', SQLERRM, TO_CHAR(SQLCODE));
1882 x_errbuf := SQLERRM;
1883 x_retcode := SQLCODE;
1884 x_return_status := FND_API.G_RET_STS_ERROR;
1885 RAISE;
1886 END;
1887 END LOOP;
1888 l_first := l_last + 1;
1889 l_last := l_first + l_var;
1890 IF l_first > l_sales_lead_id.LAST THEN
1891 l_flag := FALSE;
1892 END IF;
1893 END LOOP;
1894 END IF; --l_sales_lead_id.count > 0
1895 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_N_ROWS_PROCESSED || l_sales_lead_id.COUNT);
1896 END LOOP;
1897 l_limit_flag := FALSE;
1898 l_loop_count := 0;
1899 l_access_id.delete;
1900 l_sales_lead_id.delete;
1901 l_attempts := 1;
1902 END IF; -- (Custom or Non Custom)
1903
1904 EXCEPTION
1905 WHEN OTHERS THEN
1906 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CO || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1907 x_errbuf := SQLERRM;
1908 x_retcode := SQLCODE;
1909 x_return_status := FND_API.G_RET_STS_ERROR;
1910 RAISE;
1911 END ASSIGN_LEAD_OWNER;
1912
1913 /**************************** End Assign Lead Owner ********************/
1914
1915
1916
1917 END AS_GAR_LEADS_PUB;
1918